Blog Archives

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

Advertisements

Query to get Executable, Concurrent, Application in oracle apps AOL

Below query gives the Executable, Concurrent, Application info in oracle apps AOL
select fcpv.OUTPUT_FILE_TYPE, fcpv.CONCURRENT_PROGRAM_NAME, fcpv.USER_CONCURRENT_PROGRAM_NAME, substr(fat.application_name, 0,3), EXECUTION_FILE_NAME
from FND_CONCURRENT_PROGRAMS_VL fcpv , fnd_application_tl fat , FND_EXECUTABLES_FORM_V fefv
where
fcpv.application_id = fat.application_id
and fefv.executable_id = fcpv.EXECUTABLE_ID
and fcpv.USER_CONCURRENT_PROGRAM_NAME in (
‘HW Employee Contract Extension Letter’,
‘HW Employment Letter to Embassy’
,’HW Empoyee Education Certificate Letter’,
‘HW Probation Conformation Letter’,
‘HW Salary Certificate Letter’,
‘HW Salary Increment Letter’,
‘HW Salary Increment Or Bonus Letter Report’,
‘HW Sponsorship Visa Letter to Embassy’,
‘HW To Whom it May Concern Letter’,
‘HW Promotion Conformation Letter’,
‘HW Salary Alignment letter’,
‘HW Resignation Letter to Bank’,
‘HW Experience Certificate’,
‘HW Resignation Acceptance Letter’,
‘XXHW Employee Contract Non Renewable Report’,
‘HW Bonus Letter Only’
);

Getting ORG_ID in concurrent program

Sometimes we may get stuck to get the orgId in the concurrent to use so I have just given some screenshots by which we can have a overview of what profile option that we can use.

Using ORGID while passing in Concurrent Parameter so its directly we can select the Default type as Profile and then we can insert the Value as ORG_ID

Org ID in concurrent Parameter

Org ID in Value Set

 

For using OrgId in Value set we can use the FND_PROFILE.VALUE(‘ORG_ID’) for restricting values in value sets.

Org ID in Value Set

To have a full info of the available Profile options we can go to the respective Responsibility and search for the profile as shown in the below screenshot.

Available Profile options

Available Profiles

Thank you