API for Finally clsoing the PO Internal Requisitions

Purchase Order -

A purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

Internal Requisitions-

Internal Requisitions provide the mechanism for requesting and transferring material from inventory to other inventory or expense locations.

When Purchasing, Order Entry / Shipping, and Inventory are installed, they combine to give you a flexible solution for your inter-organization and intra-organization requests.

 

In the code below- 

xx_po_requisition_close this is a custom table with the following fields -

1.       REQUISITION_NUMBER   VARCHAR2(200) 

2.       ORG_ID      NUMBER        

3.       PROCESS_FLAG      NUMBER  

Script to create table –

Create table xx_po_requisition_close(REQUISITION_NUMBER   VARCHAR2(200) 

ORG_ID      NUMBER        

PROCESS_FLAG      NUMBER  );

 

we need to insert the requisition numbers that we need to finally close in this table and then this script can be run -

the source code -

SET SERVEROUTPUT ON;

DECLARE

    x_req_control_error_rc  VARCHAR2(500);

    l_org_id                NUMBER := 98;

-- enter the Operating_Unit Here

    cnt                     NUMBER := 0;

 

    CURSOR c_req_no IS

    SELECT  *  FROM xx_po_requisition_close WHERE  process_flag = 0;

 

    CURSOR c_req_close (p_req_no VARCHAR2) IS

    SELECT

        prh.segment1 requisition_num,

        prh.requisition_header_id,

        prh.org_id,

        prl.requisition_line_id,

        prh.preparer_id,

        prh.type_lookup_code,

        pdt.document_type_code,

        prh.authorization_status,

        prh.closed_code

    FROM

        apps.po_requisition_headers_all    prh,

        apps.po_requisition_lines_all      prl,

        apps.po_document_types_all         pdt

    WHERE

            1 = 1

        AND prh.org_id = l_org_id

        AND pdt.document_type_code = 'REQUISITION'

        AND prh.authorization_status = 'APPROVED'

        AND prl.line_location_id IS NULL

        AND prh.requisition_header_id = prl.requisition_header_id

        AND prh.type_lookup_code = pdt.document_subtype

        AND prh.org_id = pdt.org_id

        AND prh.segment1 = p_req_no; -- Enter The Requisition Number

 

    l_resp_id               NUMBER;

    l_resp_appl_id          NUMBER;

BEGIN

    SELECT

        application_id,

        responsibility_id

    INTO

        l_resp_appl_id,

        l_resp_id

    FROM

        fnd_responsibility_vl

    WHERE

        responsibility_name LIKE ‘Purchasing SU';

 

    fnd_global.apps_initialize(user_id => 4483,  --USER ID

 

     resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);

 

    mo_global.init('PO');

    mo_global.set_policy_context('S', l_org_id);

    FOR j IN c_req_no LOOP

        FOR i IN c_req_close(j.requisition_number) LOOP

            dbms_output.put_line('Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>' || i.requisition_num);

            dbms_output.put_line('=======================================================');

            po_reqs_control_sv.update_reqs_status(x_req_header_id => i.requisition_header_id, x_req_line_id => i.requisition_line_id,

                                                 x_agent_id => i.preparer_id,

                                                 x_req_doc_type => i.document_type_code,

                                                 x_req_doc_subtype => i.type_lookup_code,

                                                 x_req_control_action => 'FINALLY CLOSE',

                                                 x_req_control_reason => 'FINALLY CLOSED BY API',

                                                 x_req_action_date => sysdate,

                                                 x_encumbrance_flag => 'N',

                                                 x_oe_installed_flag => 'Y',

                                                 x_req_control_error_rc => x_req_control_error_rc);

 

            dbms_output.put_line('Status Found: ' || x_req_control_error_rc);

            dbms_output.put_line('Requisition Number which is Finally Closed =>' || i.requisition_num);

            cnt := cnt + 1;

            IF x_req_control_error_rc IS NULL THEN

                dbms_output.put_line('Status : '

                                     || x_req_control_error_rc

                                     || sqlerrm);

                                     UPDATE xx_po_requisition_close

        SET

            process_flag = 1

        WHERE

            requisition_number = j.requisition_number;

                COMMIT;

            ELSE

                dbms_output.put_line('ERROR: '

                                     || x_req_control_error_rc

                                     || sqlerrm);

                                     UPDATE xx_po_requisition_close

        SET

            process_flag = 2

        WHERE

            requisition_number = j.requisition_number;

            END IF;

        END LOOP;        

    END LOOP;

    dbms_output.put_line('Count is : => ' || cnt);

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Requisition Number Exception Others =>' || sqlerrm);

END;