Saturday, July 3, 2010

PO Cancel API Code

Your Ad Here



CREATE OR REPLACE PROCEDURE APPS.XXPO_CANCEL_PO
(ERRBUFF VARCHAR2, RETCODE NUMBER, P_DOC_NUM VARCHAR2)
IS
CURSOR FETCH_PO_CUR IS
SELECT * FROM PO_HEADERS_ALL
WHERE SEGMENT1 = P_DOC_NUM
AND ORG_ID = FND_PROFILE.VALUE('ORG_ID');

V_USER_ID NUMBER ;
V_RESPONSIBILITY_ID NUMBER ;
V_RESPONSIBILITY_APPL_ID NUMBER ;
V_ORG_ID VARCHAR2(20) ;
L_RETURN_STATUS VARCHAR2(1) := NULL;
L_MSG_DATA VARCHAR2(2000);
V_SUCCESS_COUNT NUMBER;
V_FAILURE_COUNT NUMBER;

BEGIN
V_SUCCESS_COUNT := 0;
V_FAILURE_COUNT := 0;

FOR FETCH_PO_REC IN FETCH_PO_CUR
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG , 'START OF CANCEL PO PROCESS API'SYSDATE );
BEGIN
V_USER_ID := FND_GLOBAL.USER_ID ;
V_RESPONSIBILITY_ID := FND_GLOBAL.RESP_ID ;
V_RESPONSIBILITY_APPL_ID := FND_GLOBAL.RESP_APPL_ID;
FND_GLOBAL.APPS_INITIALIZE( V_USER_ID ,V_RESPONSIBILITY_ID,V_RESPONSIBILITY_APPL_ID);
V_ORG_ID:=FND_PROFILE.VALUE('ORG_ID');
MO_GLOBAL.INIT('PO');
MO_GLOBAL.SET_POLICY_CONTEXT('S',V_ORG_ID);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG , 'ERROR CODE FOR INITIALIZATION IS :'TO_NUMBER( SQLCODE )' ' 'ERROR MSG FOR INITIALIZATION IS :'SUBSTR( SQLERRM , 1 , 50) ) ;
END ;

FND_MSG_PUB.INITIALIZE ;
FND_FILE.PUT_LINE(FND_FILE.LOG , 'PO NUMBER TO CANCEL IS :'FETCH_PO_REC.SEGMENT1 ) ;

IF FETCH_PO_REC.AUTHORIZATION_STATUS <> 'APPROVED'
THEN
UPDATE PO_HEADERS_ALL
SET AUTHORIZATION_STATUS = 'APPROVED'
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;
END IF;

PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT
(1.0, -- P_API_VERSION
FND_API.G_TRUE, -- P_INIT_MSG_LIST
FND_API.G_TRUE, -- P_COMMIT
L_RETURN_STATUS,-- X_RETURN_STATUS
'PO', -- P_DOC_TYPE
'STANDARD', -- P_DOC_SUBTYPE
NULL, -- P_DOC_ID
FETCH_PO_REC.SEGMENT1, -- P_DOC_NUM
NULL, -- P_RELEASE_ID
NULL, -- P_RELEASE_NUM
NULL, -- P_DOC_LINE_ID
NULL, -- P_DOC_LINE_NUM
NULL, -- P_DOC_LINE_LOC_ID
NULL, -- P_DOC_SHIPMENT_NUM
'CANCEL', -- P_ACTION
SYSDATE, -- P_ACTION_DATE
NULL, -- P_CANCEL_REASON
'N', -- P_CANCEL_REQS_FLAG
NULL, -- P_PRINT_FLAG
NULL, -- P_NOTE_TO_VENDOR
'N' ); --P_USE_GLDATE

FND_FILE.PUT_LINE(FND_FILE.LOG,'API RETURN STATUS IS-' L_RETURN_STATUS);

IF L_RETURN_STATUS = 'U' THEN --( FND_API.G_RET_STS_UNEXP_ERROR)
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UNEXPECTED ERROR IN CANCEL PO PROCESS API');
FND_MSG_PUB.ADD_EXC_MSG('QP_PRICE_LIST_PUB',' UNEXPECTED ERROR IN CANCEL PO PROCESS API');
ELSIF
L_RETURN_STATUS ='E' THEN --- ( FND_API.G_RET_STS_ERROR )
FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CANCEL PO PROCESS API');
FND_MSG_PUB.ADD_EXC_MSG('QP_PRICE_LIST_PUB','ERROR IN CANCEL PO PROCESS API');
END IF;

IF L_RETURN_STATUS != 'S' THEN
V_FAILURE_COUNT := V_FAILURE_COUNT + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL FAIL RECORD COUNT'V_FAILURE_COUNT);
FOR ERR_CODE IN 1..FND_MSG_PUB.COUNT_MSGLOOP
L_MSG_DATA := SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => 1,P_ENCODED=>'F'),1,200);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CANCEL PO PROCESS API FAILED FOR REASON-'L_MSG_DATA ) ;
END LOOP;
END IF;

IF L_RETURN_STATUS = 'S' THEN --- ( FND_API.G_RET_STS_SUCESS)
FND_FILE.PUT_LINE (FND_FILE.LOG, 'CANCEL PO PROCESS API PASSED' ) ;
V_SUCCESS_COUNT := V_SUCCESS_COUNT + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL SUCCESS RECORD COUNT-'V_SUCCESS_COUNT);

-- RENAME PO NUMBER AFTER CANCELLATION
UPDATE PO_HEADERS_ALL
SET SEGMENT1 = 'XX-'FETCH_PO_REC.SEGMENT1
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;

UPDATE PO_HEADERS_ARCHIVE_ALL
SET SEGMENT1 = 'XX-'FETCH_PO_REC.SEGMENT1
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;

END IF ;
END LOOP ;

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR CODE IN THE EXECUTION OF CANCEL API IS :'TO_NUMBER( SQLCODE )' 'SUBSTR( SQLERRM , 1 , 50) ) ;
END XXPO_CANCEL_PO ;
/
Your Ad Here

2 comments:

  1. getting error as::
    Document control failed due to error in [DOCMGR-CANCEL] with a return code of [OTHER].

    ReplyDelete
  2. Hi Lokesh,
    This code works fine for me in R12...
    Please initialize the session inside code using fnd_client_info in EBS or mo_global, if you are using R12, and then try..
    If still doesnt work, enable the debug and check the log file for exact error and post here

    Thanks
    Amit

    ReplyDelete