PO Cancel API Code

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 ;

/