0 Replies Latest reply on Aug 25, 2015 11:28 AM by User13049696-Oracle

    How to close Unapproved POs in Incomplete, In Process, Requires Reapproval and Rejected Status

    User13049696-Oracle

      Customers ERP data base down stream is getting an OPEN PO report which has all APPROVED and UNAPPROVED OPEN POs. While trying to close POs that are appearing in OPEN PO report, they are able to close only approved POs. But now business wants to close old unapproved OPEN. However they do not want to approve them from front end and then close them. So need assistance in closing them from back end.

      They have both uninvoiced and invoiced POs in status Incomplete, In Process, Rejected and Requires Reapproval status. They need help in updating the status to close and want to know the tables and fields that we need to consider while closing these POs.

      Support provided them with the below PO Close API script but it did not close the unapproved POs.

      --Sample wrapper procedure to close  purchase order

      CREATE OR REPLACE PROCEDURE xx_po_close_prc(p_header_id NUMBER)

      IS

       

      lv_result BOOLEAN;

      lv_return_code VARCHAR2(20);

      lv_header_id NUMBER := p_header_id;

       

      CURSOR c_po_details IS

      SELECT

      1. pha.po_header_id,
      2. pha.org_id,
      3. pha.segment1,
      4. pha.agent_id,
      5. pdt.document_subtype,
      6. pdt.document_type_code,
      7. pha.closed_code,
      8. pha.closed_date

      FROM apps.po_headers_all pha, apps.po_document_types_all pdt

      WHERE pha.type_lookup_code = pdt.document_subtype

      AND pha.org_id = pdt.org_id

      -- AND authorization_status = 'APPROVED'

      AND pha.closed_code <> 'FINALLY CLOSED'

      AND pha.po_header_id = p_header_id; -- Enter the PO_HEADER_ID of one PO which needs to be Closed

       

      BEGIN

       

      -- Parameters :

       

        -- p_docid : Header ID for Document

        -- Table: PO_HEADERS_ALL.PO_HEADER_ID

        -- p_doctyp : Document Type

        -- Table: PO_HEADERS_ALL.TYPE_LOOKUP_CODE

        -- Values : STANDARD, BLANKET, RELEASE

        -- p_lineid : Line ID for Document

        -- Table: PO_LINES_ALL.PO_LINE_ID

        -- p_shipid : Ship ID for Document

        -- Table: PO_LINE_LOCATIONS.LINE_LOCATION_ID

        -- p_action : Action to be performed

        -- Table: PO_ACTION_HISTORY.ACTION_CODE

        -- Values: 'CLOSE' -- Close for Receiving

        --         'INVOICE CLOSE'

        --         'FINALLY CLOSE'

        -- p_reason : Reason for Closing. This must be entered for Manual Closing

       

        -- p_calling_mode : Whether being invoked from 'PO', 'RCV' or 'AP'. This

        --                  determines which of the Closed States needs to be

        --                  checked (receiving, invoicing or both). This must be

        --                  entered for Auto Closing

       

        -- p_conc_flag : Whether invoked from a Concurrent Process. This must be

        --               entered for Manual Closing and is used by the Funds Checker

       

        -- p_return_code : Return Status of PO Closing

       

        -- p_auto_close : Whether to invoke Auto Closing or Manual Closing

       

        -- p_action_date is added to function close_po()

       

      FOR po_details_rec IN c_po_details

      LOOP

        BEGIN

        lv_result :=    PO_ACTIONS.CLOSE_PO(

                P_DOCID => po_details_rec.po_header_id,

                P_DOCTYP => 'PO',

                P_DOCSUBTYP => 'STANDARD', -- Can be STANDARD, BLANKET, RELEASE

                P_LINEID => NULL, -- If want to close Line

                P_SHIPID => NULL,-- If want to close Shipment

                P_ACTION => 'CLOSE',

                P_REASON => 'Close Purchase Order ',

                P_CALLING_MODE => po_details_rec.document_type_code,

                P_CONC_FLAG => 'N',

                P_RETURN_CODE => lv_return_code,

                P_AUTO_CLOSE => 'N',

                P_ACTION_DATE => sysdate,

      P_ORIGIN_DOC_ID => NULL );

          IF lv_result = TRUE THEN

      DBMS_OUTPUT.PUT_LINE('Successfully closed PO#'||po_details_rec.segment1);

          ELSE

      DBMS_OUTPUT.PUT_LINE('Cannot close PO#'||po_details_rec.segment1);

          END IF;

         END;

      END LOOP;

      EXCEPTION

         WHEN OTHERS THEN

           dbms_output.put_line(' Exception closing PO using PO_ACTIONS.CLOSE_PO'||SQLERRM);

      END xx_po_close_prc;

       

      Tested this code in test instance and it is working only for approved POs. Tried to update the approval status from backend for an unapproved PO using the below scripts:

       

      update po_headers_all set authorization_status = 'APPROVED', approved_flag = 'Y', approved_date = '25-AUG-15', last_update_date = '25-AUG-15'

      ,approval_required_flag = 'N'

      where segment1 = 1513 and type_lookup_code = 'STANDARD'

      and org_id = 204;

       

      update po_line_locations_all set approved_flag = 'Y', approved_date = '25-AUG-15', last_update_date = '25-AUG-15' where po_header_id = '8176';

       

      Insert into po_action_history (OBJECT_ID,OBJECT_TYPE_CODE,OBJECT_SUB_TYPE_CODE,SEQUENCE_NUM,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ACTION_CODE,ACTION_DATE,EMPLOYEE_ID,APPROVAL_PATH_ID,NOTE,OBJECT_REVISION_NUM,OFFLINE_CODE,LAST_UPDATE_LOGIN,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,PROGRAM_DATE,APPROVAL_GROUP_ID) values (8176,'PO','STANDARD',1,to_date('25-AUG-15','DD-MON-RR'),1068,to_date('25-AUG-15','DD-MON-RR'),1068,'SUBMIT',to_date('25-AUG-15','DD-MON-RR'),57,null,null,0,null,-1,0,0,0,null,null,null);

      Insert into po_action_history (OBJECT_ID,OBJECT_TYPE_CODE,OBJECT_SUB_TYPE_CODE,SEQUENCE_NUM,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ACTION_CODE,ACTION_DATE,EMPLOYEE_ID,APPROVAL_PATH_ID,NOTE,OBJECT_REVISION_NUM,OFFLINE_CODE,LAST_UPDATE_LOGIN,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,PROGRAM_DATE,APPROVAL_GROUP_ID) values (8176,'PO','STANDARD',2,to_date('25-AUG-15','DD-MON-RR'),1068,to_date('25-AUG-15','DD-MON-RR'),1068,'APPROVE',to_date('25-AUG-15','DD-MON-RR'),57,null,null,0,null,-1,0,0,0,null,null,null);

      commit;

      After that ran the Po Close API but still it is not closing the PO.

       

      Need help in updating the status to close and want to know the tables and fields that we need to consider while closing these POs.