INTRODUCTION
This blog explains how we cancel approved requisition in oracle apps
PROCEDURE CANCEL_REQUISITION (P_BATCH_RUN_ID IN NUMBER,P_REQUISITION_NO IN VARCHAR)
AS
X_REQ_CONTROL_ERROR_RC VARCHAR2(500);
LN_USER_ID NUMBER;
LN_RESP_ID NUMBER;
LN_APPL_ID NUMBER;
LN_ORG_ID NUMBER := 82;
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 PO_REQUISITION_HEADERS_ALL PRH
,PO_REQUISITION_LINES_ALL PRL
,PO_DOCUMENT_TYPES_ALL PDT
WHERE 1 = 1
AND PRH.ORG_ID = LN_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 = P_REQUISITION_NO; — ENTER THE REQUISITION NUMBER
BEGIN
BEGIN
SELECT USER_ID
INTO LN_USER_ID
FROM FND_USER
WHERE USER_NAME = ‘ARUN’;
EXCEPTION
WHEN OTHERS THEN
LN_USER_ID := NULL;
END;
BEGIN
SELECT RESPONSIBILITY_ID
,APPLICATION_ID
INTO LN_RESP_ID
,LN_APPL_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME = ‘India Local Purchasing’
AND NVL (TRUNC (END_DATE), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
EXCEPTION
WHEN OTHERS THEN
LN_RESP_ID := NULL;
LN_APPL_ID := NULL;
END;
FND_GLOBAL.APPS_INITIALIZE (LN_USER_ID, LN_RESP_ID, LN_APPL_ID);
MO_GLOBAL.SET_POLICY_CONTEXT (‘S’, LN_ORG_ID);
FOR I IN C_REQ_CANCEL LOOP
DBMS_OUTPUT.PUT_LINE(‘Cancellation process starts’||P_REQUISITION_NO ||’-‘||P_BATCH_RUN_ID );
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(‘Cancellation process ends ‘||X_REQ_CONTROL_ERROR_RC);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Cancellation process ends ‘||SQLERRM);
END ;
