Sunday, December 18, 2011

How to programmatically close PO using PO_ACTIONS API

PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass P_ACTION parameter value as 'CLOSE'/'FINALLY CLOSE' depending on the requirement to close a Purchase Order
--Sample wrapper procedure  to close  purchase order
CREATE OR REPLACE 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
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.closed_code,
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 if one PO needs to be Closed/finally 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;

3 comments:

  1. Hi, Thanks for the post. It was really helpful.

    Currently we are using the PO_ACTIONS.CLOSE_PO to close blanket releases.

    l_close_status :=
    PO_ACTIONS.CLOSE_PO (p_docid => po_rec.po_header_id,
    p_doctyp => 'PA',
    p_docsubtyp => 'BLANKET',
    p_lineid => NULL,
    p_shipid => NULL,
    p_action => 'FINALLY CLOSE',
    p_return_code => l_return_code,
    p_auto_close => 'N',
    p_action_date => SYSDATE);

    The above program final closes the Blanket PO (‘Finally Closed’) at the header level and all the releases are in ‘Closed’ status. Is it possible to have the release status to ‘Finally Closed’ instead of ‘Closed’ status?

    ReplyDelete
  2. HI Sireesh,
    Does this script work for 11.5.10?
    I tried it and I am not able to close the PO.
    I don't get any error message from the API and the l_return_code is NULL.
    Any help is appreciated.

    Thanks!

    ReplyDelete
  3. This API does not work for 11.5.10.
    Please see the below note from Metalink.
    821765.1 - How Can Purchase Orders Be Mass Cancelled Using An API?
    Bug 4711021 - CANNOT CLOSE PO USING PO_ACTIONS.CLOSE_PO

    Thanks,
    Prakash

    ReplyDelete