Saturday, August 8, 2009

API for Closing/Finally Closing PO Using po_actions.close_po

Do we have any API for Finally Closing PO??

Yes, we are having an API for closing or finally closing the POs. In the API, there is a parameter "p_action" which we need to set as either CLOSE (if we want to close the PO) or FINALLY CLOSE (If we want to Finally Close) the PO. Another Parameter which needs to set properly is "p_auto_close". This parameter should be set to 'N'.

-- R12 - PO - Script to Close / Finally Close PO using PO_ACTIONS CLOSE_PO API.sql

DECLARE

x_action constant varchar2(20) := 'FINALLY CLOSE'; -- Change this parameter as per requirement
x_calling_mode constant varchar2(2) := 'PO';
x_conc_flag constant varchar2(1) := 'N';
x_return_code_h varchar2(100);
x_auto_close constant varchar2(1) := 'N';
x_origin_doc_id number;
x_returned boolean;

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 pdt.document_type_code = 'PO'
AND authorization_status = 'APPROVED'
AND pha.closed_code <> 'FINALLY CLOSED'
AND segment1 = '379329'; -- Enter the PO Number if one PO needs to be finally closed/Closed

begin

fnd_global.apps_initialize (user_id => 1805,
resp_id => 20707,
resp_appl_id => 201);

for po_head in c_po_details

LOOP

mo_global.init (po_head.document_type_code);
mo_global.set_policy_context ('S', po_head.org_id);

DBMS_OUTPUT.PUT_LINE ('Calling PO_Actions.close_po for Closing/Finally Closing PO =>' po_head.segment1);

x_returned :=
po_actions.close_po(
p_docid => po_head.po_header_id,
p_doctyp => po_head.document_type_code,
p_docsubtyp => po_head.document_subtype,
p_lineid => NULL,
p_shipid => NULL,
p_action => x_action,
p_reason => NULL,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL);

IF x_returned = TRUE THEN

DBMS_OUTPUT.PUT_LINE ('Purchase Order which just got Closed/Finally Closed is ' po_head.segment1);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE ('API Failed to Close/Finally Close the Purchase Order');

END IF;

END LOOP;

END;

25 comments:

  1. Hi jyoti
    Actaully i am trying to close purchase orders.Your program is working fine but, i want to close blanket purchase release lines..
    Can you suggest me how to achive this?...

    thanks
    raju
    09699312812

    ReplyDelete
  2. Hi Raju,

    Please Change the cursor query so that it can fetch the BPA. In the where clause add the following pdt.document_type_code = 'PA'. Please let me know if this helps or not.

    Regards,
    Jyoti

    ReplyDelete
  3. Dear jyothi
    Thanks for ur reply and
    if i use document type is 'PA' then it is closing BPA completely,
    ideally for me , BPA need to open for ever because its releases will be posible.

    So in that case, i want to close the lines of releases

    in technical aspect
    suppose BPA no-937 and release no-115
    i need to develop some program which need to act like
    headers status='OPEN'
    line status='OPEN'
    release status='CLOSED' (for particuleer
    line location status='CLOSED'(for particuler item)

    if I try to close the PO release from front end
    it is giving the above mentioned technical details

    Point no2 is, I heard that the api/function which we are using to not suggestable to use and not supported by oracle
    In short it is not public API’s

    Please suggest me

    Thanks
    Raju
    09699312812

    ReplyDelete
  4. Hello All,

    I am working on the same issue, I have read in one of the docs on metalink that there's no API to close PO's. Is that true, any insight is highly appreciated.

    Thanks
    Usha

    ReplyDelete
  5. Hi Usha and Jyothi
    we developed one program similer to jyothi logic and it is able to close PO's (standard, Blanket)....

    i feel this program is working in the same way ,when we try to close PO from front end..

    i suggest any one who are in the same way, if the received quantity is zero, instead of closing the PO, we can cancel that line by using Cancel public API.
    and if the received quantity is not zero then we can close The PO..

    ofcourse , for this PO close there is no API and ER is going on..

    jyothi ur recomandation are very good.

    we are testing the PO close program


    thanks
    raju
    09699312812

    ReplyDelete
  6. Hi Usha,

    This is not a public API, but it works fine.

    Rgds,
    Jyoti

    ReplyDelete
  7. Hi Raju,

    Thanks... Glad to hear that your program is working fine.

    Rgds,
    Jyoti

    ReplyDelete
  8. hi
    I applied the script on a PO and it is executed successfully. but the PO remains in its original status (not closed. Any suggestions?

    ReplyDelete
  9. Is this script applicable to release 11i as well?

    ReplyDelete
  10. hi abuhayyan
    can u send the query..i will check the same n let u know the problem?...

    ReplyDelete
  11. Hi abuhayyan,

    You need to submit this program as a Concurrent progam. Though I have not tested the program in 11.5.10.2, but I feel the program should over there as well.

    Rgds,
    Jyoti

    ReplyDelete
    Replies
    1. Hi Jyoti,

      I have used the below API through conc prog for cancel Standard PO in 11i. But not able to close that PO...

      The code is
      lv_result := PO_ACTIONS.CLOSE_PO(P_DOCID => po_details_rec.po_header_id,
      P_DOCTYP => 'PO',
      P_DOCSUBTYP => 'STANDARD',
      P_LINEID => NULL, P_SHIPID => NULL,
      P_ACTION => 'FINALLY CLOSE',
      P_REASON => 'Close Purchase Order ',
      P_CALLING_MODE => 'PO',
      P_CONC_FLAG => 'N',
      P_RETURN_CODE => lv_return_code,
      P_AUTO_CLOSE => 'N',
      P_ACTION_DATE => sysdate );


      Please help on this....

      Delete
  12. Hi Jyoti,

    I have used the above anoynomous block for closing a PO but the x_returned boolean is not returnig any value and so im getting 'API Failed to Close/Finally Close the Purchase Order'. I run in 11.5.10.
    below is the code,


    /* Formatted on 2010/07/21 12:36 (Formatter Plus v4.8.8) */
    DECLARE
    x_action CONSTANT VARCHAR2 (20) := 'FINALLY CLOSE';
    -- Change this parameter as per requirement
    x_calling_mode CONSTANT VARCHAR2 (2) := 'PO';
    x_conc_flag CONSTANT VARCHAR2 (1) := 'N';
    x_return_code_h VARCHAR2 (100);
    x_auto_close CONSTANT VARCHAR2 (1) := 'N';
    x_origin_doc_id NUMBER;
    x_returned BOOLEAN;

    l_user_id NUMBER;
    l_resp_id NUMBER;
    l_resp_appl_id NUMBER;
    l_ou_unit NUMBER;

    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 pdt.document_type_code = 'PO'
    AND authorization_status = 'APPROVED'
    AND pha.closed_code <> 'FINALLY CLOSED'
    AND segment1 = '100010';
    -- Enter the PO Number if one PO needs to be finally closed/Closed
    BEGIN


    -- l_user_id := fnd_global.user_id;
    -- l_resp_id := fnd_global.resp_id;
    -- l_resp_appl_id := fnd_global.resp_appl_id;
    -- fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);

    -- l_ou_unit := fnd_profile.VALUE ('MO_OPERATING_UNIT');

    fnd_client_info.set_org_context (NVL (l_ou_unit, 85));

    fnd_global.apps_initialize (user_id => 5930,
    resp_id => 50295,
    resp_appl_id => 201
    );

    FOR po_head IN c_po_details
    LOOP

    --mo_global.init(po_head.document_type_code);
    -- mo_global.set_policy_context('S', po_head.org_id);
    x_returned :=
    po_actions.close_po (p_docid => po_head.po_header_id,
    p_doctyp => po_head.document_type_code,
    p_docsubtyp => po_head.document_subtype,
    p_lineid => NULL,
    p_shipid => NULL,
    p_action => x_action,
    p_reason => NULL,
    p_calling_mode => x_calling_mode,
    p_conc_flag => x_conc_flag,
    p_return_code => x_return_code_h,
    p_auto_close => x_auto_close,
    p_action_date => SYSDATE,
    p_origin_doc_id => NULL
    );

    IF x_returned = TRUE
    THEN
    DBMS_OUTPUT.PUT_LINE ('Success');
    COMMIT;
    ELSE
    DBMS_OUTPUT.put_line('API Failed to Close/Finally Close the Purchase Order');
    --DBMS_OUTPUT.put_line('x_return_code_h'||x_return_code_h);
    --DBMS_OUTPUT.put_line('x_returned'||x_returned);
    END IF;
    END LOOP;
    END;

    ReplyDelete
  13. Hi Pradeep,

    you need to run this code from conc program or alternatively you need to use fnd_global.initialize instead of fnd_global.apps_initialize (mainly to pass some value to login_id parameter)

    with above change it worked for me.

    Rgds,
    -Kamal

    ReplyDelete
  14. Hi,

    Can somebody help me in the following issue.

    I need to a add a new shipping line to an existing item in an existing po.

    I tried thru the interface tables where using UPDATE action and mentioning the line number as the same line number of the item and shipping number as the next available shipping no.

    It gives an error line already exists. Is there a workaround for this.

    Regards
    Aroop

    ReplyDelete
  15. I am trying to cancel a po order as user A from schema B. when i cancel the po order as apps user from schema B, the order is being cancelled but I cannot cancel the po order as user A in schema B.

    I running this as a concurrent program.

    here is my script:


    procedure stp_Cancel_Po(p_user_id in number,
    p_ponumber in GSI_SPECIAL_ORDER_SUMMARY.Po_Number%TYPE,
    p_po_line_id in GSI_SPECIAL_ORDER_SUMMARY.Po_Line_Id%TYPE) as


    x_action constant varchar2(20) := 'CLOSE';
    x_calling_mode constant varchar2(2) := 'PO';
    x_conc_flag constant varchar2(1) := 'N';
    x_return_code_h varchar2(100);
    x_auto_close constant varchar2(1) := 'N';
    x_origin_doc_id number;
    x_returned boolean;

    v_po_user_id constant VARCHAR2(30) := apps.fnd_profile.VALUE('GSI_XOE_RETURNS_PO_USER_ID');
    v_po_resp_id constant VARCHAR2(30) := apps.fnd_profile.VALUE('GSI_XOE_RETURNS_PO_RESP_ID');
    v_po_resp_appl_id constant VARCHAR2(30) := apps.fnd_profile.VALUE('GSI_XOE_RETURNS_PO_RESP_APPL_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
    , pha.revision_num
    , pla.closed_code line_closed_code
    , pll.closed_code ship_closed_code
    FROM apps.po_headers_all pha, apps.po_document_types_all pdt
    , apps.po_lines_all pla
    , apps.po_line_locations_all pll
    WHERE pha.type_lookup_code = pdt.document_subtype
    AND pha.org_id = pdt.org_id
    AND pdt.document_type_code = 'PO'
    AND pha.segment1 = p_ponumber
    and pha.po_header_id = pla.po_header_id
    and pla.po_header_id = pll.po_header_id
    and pla.po_line_id = pll.po_line_id
    and pla.po_line_id = p_po_line_id;

    begin

    apps.fnd_global.apps_initialize (user_id => v_po_user_id,
    resp_id => v_po_resp_id,
    resp_appl_id => v_po_resp_appl_id);

    for po_head in c_po_details

    LOOP


    apps.mo_global.init (po_head.document_type_code);
    apps.mo_global.set_policy_context ('S', po_head.org_id);

    DBMS_OUTPUT.PUT_LINE ('Calling PO_Actions.close_po for Closing PO =>' ||po_head.segment1);

    x_returned :=
    apps.po_actions.close_po(
    p_docid => po_head.po_header_id,
    p_doctyp => po_head.document_type_code,
    p_docsubtyp => po_head.document_subtype,
    p_lineid => p_po_line_id,
    p_shipid => NULL,
    p_action => x_action,
    p_reason => NULL,
    p_calling_mode => x_calling_mode,
    p_conc_flag => x_conc_flag,
    p_return_code => x_return_code_h,
    p_auto_close => x_auto_close,
    p_action_date => SYSDATE,
    p_origin_doc_id => NULL);

    IF x_returned = TRUE THEN

    DBMS_OUTPUT.PUT_LINE ('Purchase Order which just got Closed is ' || po_head.segment1);

    COMMIT;

    ELSE

    DBMS_OUTPUT.PUT_LINE ('API Failed to Close the Purchase Order');

    END IF;

    ReplyDelete
  16. also, when i run try to cancel the po order as user B from schema B, the order is not being cancelled either. I have created all the synonyms and grants for user A and user B in the respective schemas.

    ReplyDelete
  17. This API is not applicable to 11i. (take a look at ID 821765.1)

    Any other approach for closing POs in 11.5.10.2 ?

    Regards,
    Soma

    ReplyDelete
  18. hi jyoti,

    what is the x_conc_flag will do?

    Thanks,
    Kavya

    ReplyDelete
  19. Hi Jyoti,

    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?

    Thank you,

    ReplyDelete
  20. Free Webinar : Oracle E-Business Suite - Salesforce.com Integration via Oracle's API on Aug 26th, 2015 at 3PM EST.

    Visit:  http://blog.optiosys.com

    ReplyDelete
  21. Hi,
    Currently i am working on 11i. This API is not working and I have gone through oracle doc's, there is no api to update po status in 11i. Can you suggest me is there any way to update status.

    Regards,
    Rajesh S

    ReplyDelete
  22. Hi,

    I'm using this API to finally close PO, PO is not getting finally closed but the API is returning TRUE. Can anyone help me with this?

    ReplyDelete
  23. Hi,

    I have an issue here. I have a PO line with an order quantity of 600.

    In distribution, I have split the line to two lines of:

    Line 1 = 100 (Have received this line fully)
    Line 2 = 500 (Need to cancel/close this line as we don't have the demand.

    Do you have an API to cancel/close the distribution line#2 so that it will not show up in MRP results?

    Thank you & Best regards.
    Shashi

    ReplyDelete
  24. API does not close PO and gives p_return_code value as SUBMISSION_FAILED and the lv_result value as TRUE. how do we interpret these values

    ReplyDelete