PO Receipt Interface in Oracle Apps

Below is the code for PO Receipt Interface. The below script is based on Oracle Document ID  DOC ID 1520449.1.

In the Below script has the functionalities as given below

1. Insertion of RCV_TRANSACTIONS_INTERFACE

2.Insertion of RCV_HEADERS_INTERFACE

3. Automating Concurrent Receiving Transaction Processor and using Wait to complete the concurrent

4. Once done giving the Receipt Number

 

declare

L_USER_ID NUMBER :=5685;
L_RESP_ID NUMBER :=52081;
L_APPL_ID NUMBER :=201;
L_ORG_ID NUMBER :=405;
L_LOGIN_ID NUMBER :=5664513;–PROFILE VALUE
L_RCV_HEADER_ID NUMBER;
L_RCV_GROUP_ID NUMBER;
L_RCV_TRANSACTION_ID NUMBER;
L_CHR_LOT_NUMBER VARCHAR2 (50);
LC_RECEIPT_NUMBER NUMBER:= 0;
LN_REQ_ID NUMBER;
LN_REQUEST_ID NUMBER;
LN_TEMP NUMBER;
LB_RESULT1 BOOLEAN;
LV_PHASE VARCHAR2(20);
LV_STATUS1 VARCHAR2(20);
LV_DEV_PHASE VARCHAR2(20);
LV_DEV_STATUS VARCHAR2(20);
LV_MESSAGE VARCHAR2(20);

CURSOR C_PO_INFO(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT PHA.PO_HEADER_ID, PLA.PO_LINE_ID, PHA.AGENT_ID, PHA.ORG_ID,
PLLA.LINE_LOCATION_ID,
PDA.PO_DISTRIBUTION_ID,
PHA.VENDOR_ID, PHA.VENDOR_SITE_ID, PLLA.SHIP_TO_ORGANIZATION_ID, PHA.SEGMENT1 DOCUMENT_NUM,PLA.LINE_NUM DOCUMENT_LINE_NUM, PLA.QUANTITY,
DESTINATION_SUBINVENTORY SUBINVENTORY
FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PHA.PO_HEADER_ID = NVL(P_PO_HEADER_ID, PHA.PO_HEADER_ID);

CURSOR C_PO_HDR(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT PHA.PO_HEADER_ID, PHA.AGENT_ID, PHA.ORG_ID,

PHA.VENDOR_ID, PHA.VENDOR_SITE_ID, PLLA.SHIP_TO_ORGANIZATION_ID, PHA.SEGMENT1 DOCUMENT_NUM

FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PHA.PO_HEADER_ID = NVL(P_PO_HEADER_ID, PHA.PO_HEADER_ID);

CURSOR GET_RECEIPT_NUMBER(P_PO_HEADER_ID IN NUMBER) IS
SELECT DISTINCT RECEIPT_NUM FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL
WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSL.PO_HEADER_ID = P_PO_HEADER_ID;

BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT(405);–INITIALIZING ORG_ID
FND_GLOBAL.APPS_INITIALIZE(L_USER_ID, L_RESP_ID, L_APPL_ID,L_LOGIN_ID);
SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO L_RCV_HEADER_ID
FROM DUAL;
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO L_RCV_GROUP_ID
FROM DUAL;
–SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
–INTO L_RCV_TRANSACTION_ID
–FROM DUAL;

DBMS_OUTPUT.PUT_LINE(‘BEFORE CALLING RCV_HEADERS_INTERFACE’);
FOR J IN C_PO_HDR(P_PO_HEADER_ID) LOOP

INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
VENDOR_ID,
VENDOR_SITE_ID,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
EMPLOYEE_ID,
VALIDATION_FLAG,
ORG_ID
)
VALUES
(L_RCV_HEADER_ID , –HEADER_INTERFACE_ID
L_RCV_GROUP_ID, –GROUP_ID
‘PENDING’, –PROCESSING_STATUS_CODE
‘VENDOR’, –RECEIPT_SOURCE_CODE
‘NEW’, –TRANSACTION_TYPE
‘RECEIVE’, –AUTO_TRANSACT_CODE
SYSDATE, –LAST_UPDATE_DATE
0, –LAST_UPDATED_BY
0, –LAST_UPDATE_LOGIN
SYSDATE, –CREATION_DATE
5685, –CREATED_BY
J.VENDOR_ID, –VENDOR_ID
J.VENDOR_SITE_ID, –VENDOR_SITE_ID
J.SHIP_TO_ORGANIZATION_ID, –SHIP_TO_ORGANIZATION_ID,
SYSDATE, –EXPECTED_RECEIPT_DATE
J.AGENT_ID, –EMPLOYEE_ID
‘Y’, –VALIDATION_FLAG
J.ORG_ID –OPERATING_UNIT
);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘BEFORE CALLING RCV_TRANSACTIONS_INTERFACE’);
FOR I IN C_PO_INFO(P_PO_HEADER_ID) LOOP

INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
INTERFACE_SOURCE_CODE,
AUTO_TRANSACT_CODE,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
HEADER_INTERFACE_ID,
DOCUMENT_NUM,
DOCUMENT_LINE_NUM,
VALIDATION_FLAG,
QUANTITY,
— AMOUNT,
SUBINVENTORY
)
SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, –INTERFACE_TRANSACTION_ID
L_RCV_GROUP_ID, –GROUP_ID
SYSDATE, –LAST_UPDATE_DATE
0, –LAST_UPDATED_BY
SYSDATE, –CREATION_DATE
5685, –CREATED_BY
0, –LAST_UPDATE_LOGIN
‘RECEIVE’, –TRANSACTION_TYPE
SYSDATE, –TRANSACTION_DATE
‘PENDING’, –PROCESSING_STATUS_CODE
‘BATCH’, –PROCESSING_MODE_CODE
‘PENDING’, –TRANSACTION_STATUS_CODE
‘FLO’, –INTERFACE_SOURCE_CODE
‘DELIVER’, –AUTO_TRANSACT_CODE
‘VENDOR’, –RECEIPT_SOURCE_CODE
I.SHIP_TO_ORGANIZATION_ID, –TO_ORGANIZATION_ID
‘PO’, –SOURCE_DOCUMENT_CODE
I.PO_HEADER_ID, –PO_HEADER_ID
I.PO_LINE_ID, –PO_LINE_ID
L_RCV_HEADER_ID, –HEADER_INTERFACE_ID
I.DOCUMENT_NUM, –DOCUMENT_NUM
I.DOCUMENT_LINE_NUM, –DOCUMENT_LINE_NUM
‘Y’, –VALIDATION_FLAG
I.QUANTITY, –QUANTITY
— 10, –AMOUNT
NVL(I.SUBINVENTORY,’NF-Staging’ )
FROM DUAL;

END LOOP;
DBMS_OUTPUT.PUT_LINE(‘AFTER CALLING RCV_TRANSACTIONS_INTERFACE’);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘CALLING CONCURRENT’);
RECEIPT_CONCURRENT( NULL ,L_RCV_GROUP_ID);

DBMS_OUTPUT.PUT_LINE(‘**** EXECUTED SUCCESSFULLY *****’);

OPEN GET_RECEIPT_NUMBER(P_PO_HEADER_ID);
FETCH GET_RECEIPT_NUMBER INTO LC_RECEIPT_NUMBER;
CLOSE GET_RECEIPT_NUMBER;

DBMS_OUTPUT.PUT_LINE(‘Receipt Number is ‘||LC_RECEIPT_NUMBER);

— FND_GLOBAL.APPS_INITIALIZE (USER_ID => FND_GLOBAL.USER_ID,
— RESP_ID => FND_GLOBAL.RESP_ID,
— RESP_APPL_ID => FND_GLOBAL.RESP_APPL_ID);
—
— L_USER_ID NUMBER :=5685;
–L_RESP_ID NUMBER :=52081;
–L_APPL_ID NUMBER :=201;

FND_GLOBAL.APPS_INITIALIZE (USER_ID => 5685,
RESP_ID => 52081,
RESP_APPL_ID =>201);

LN_REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( ‘PO’ –APPLICATIONI SHORT NAME;
,’RVCTP’ –PROGRAM SHORT NAME;
,
,
,FALSE
,’BATCH’
,P_GROUP_ID
,P_ORG_ID
);

COMMIT;
DBMS_OUTPUT.PUT_LINE(LN_REQ_ID);

LB_RESULT1 := FND_CONCURRENT.WAIT_FOR_REQUEST (REQUEST_ID => LN_REQ_ID , INTERVAL => 5, PHASE => LV_PHASE, STATUS => LV_STATUS1, DEV_PHASE => LV_DEV_PHASE, DEV_STATUS => LV_DEV_STATUS, MESSAGE => LV_MESSAGE );
COMMIT;
IF LV_DEV_PHASE = ‘COMPLETE’ AND LV_DEV_STATUS = ‘NORMAL’ THEN
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘SQLLOADER PROGRAM COMPLETED SUCCESSFULLY’ || LN_REQUEST_ID );
DBMS_OUTPUT.PUT_LINE(‘Concurrent Request completed successfully’);
— PO_RETURNS(ERRBUF , RETCODE , ‘1’ );
ELSE
DBMS_OUTPUT.PUT_LINE(‘Error while running Concurrent Request’);
ROLLBACK;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROE’);
DBMS_OUTPUT.PUT_LINE(‘ERROR’||SQLERRM);

END;

 

Thank You