API To Cancel or Finally Close Requisition

Do we have any API to cancel or finally close a Purchase Requisition:

Yes, we do have one API & that is po_reqs_control_sv.update_reqs_status which can be used for finally closing or cancelling the requisition. We need to pass the parameters like requisition_header_id, requisition_line_id, Preparer_id, document_type_code,
type_lookup_code, req_control_action, Req_control_reason and the other default parameter to the API.

-- R12 - PO - Sample Script to Cancel PR Using API


DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; -- Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CANCEL 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,
prl.line_location_id
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 = '21170000909'; -- Enter The Requisition Number

BEGIN

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

mo_global.init ('PO');
mo_global.set_policy_context ('S', l_org_id);

FOR i IN C_REQ_CANCEL

LOOP

dbms_output.put_line (' Calling po_reqs_control_sv.update_reqs_status to cancel the 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 => 'CANCEL'
, X_req_control_reason => 'CANCELLED 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 cancelled is :=>' i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Count is :=>' cnt);

END;

-- R12 - PO - Script to Finally Close PR Using API.sql


DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; -- Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CLOSE 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 = '21170002264'; -- Enter The Requisition Number

BEGIN

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

mo_global.init ('PO');
mo_global.set_policy_context ('S', l_org_id);

FOR i IN C_REQ_CLOSE

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;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Count is :=>' cnt);

END;