Cancelling PO Using API PO_Document_Control_PUB.control_document

• The PL/SQL procedure, PO_Document_Control_PUB.control_document , provides the ability to cancel Oracle Purchasing documents directly through an API.
• The API will perform all of the same processing that would be done if a cancellation was requested through the PO Summary Control Window.
• Prior to calling the API we should set our global context to reflect the application, user and responsibility used to perform the cancel action. If we do not set this context, the API will not be able to identify or update your data.

–R12 – PO – Sample Script to cancel PO using po_document_control_pub API.sql

DECLARE

l_return_status VARCHAR2 (10);

CURSOR C_PO_CANCEL is

SELECT pha.po_header_id,
pha.org_id,
pha.segment1 po_number,
pha.type_lookup_code,
pha.cancel_flag,
pha.closed_code
FROM po_headers_all pha
WHERE 1=1
AND pha.segment1 = ‘376729’ — Enter The Purchase Order Number
AND nvl(pha.closed_code,’OPEN’) = ‘OPEN’
AND nvl(pha.cancel_flag, ‘N’) = ‘N’
AND approved_flag = ‘Y’;

BEGIN

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

FOR i IN c_po_cancel

LOOP

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’,i.org_id );

DBMS_OUTPUT.PUT_LINE (‘Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents’);

po_document_control_pub.control_document
(p_api_version => 1.0, — p_api_version
p_init_msg_list => fnd_api.g_true, — p_init_msg_list
p_commit => fnd_api.g_true, — p_commit
x_return_status => l_return_status, — x_return_status
p_doc_type => ‘PO’, — p_doc_type
p_doc_subtype => ‘STANDARD’, — p_doc_subtype
p_doc_id => i.po_header_id, — p_doc_id
p_doc_num => NULL, — p_doc_num
p_release_id => NULL, — p_release_id
p_release_num => NULL, — p_release_num
p_doc_line_id => NULL, — p_doc_line_id
p_doc_line_num => NULL, — p_doc_line_num
p_doc_line_loc_id => NULL, — p_doc_line_loc_id
p_doc_shipment_num => NULL, — p_doc_shipment_num
p_action => ‘CANCEL’, — p_action
p_action_date => SYSDATE, — p_action_date
p_cancel_reason => NULL, — p_cancel_reason
p_cancel_reqs_flag => ‘N’, — p_cancel_reqs_flag
p_print_flag => NULL, — p_print_flag
p_note_to_vendor => NULL, — p_note_to_vendor
p_use_gldate =>NULL ,
p_org_id => i.org_id
);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘The Return Status of the API is => ‘ l_return_status);
If l_return_status = ‘S’ Then

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order Which is Cancelled Now => ‘ i.po_number);
Else

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order =>’ i.po_number ‘Failed to cancel Due To Following Reason’);

— Get any messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
DBMS_OUTPUT.put_line (fnd_msg_pub.get
(p_msg_index => j,
p_encoded => ‘F’));
END LOOP;
END IF;
END LOOP;
END;