Procure to Pay Technical Cycle

P2P Technical Flow (Procure to Pay Cycle with Important Columns and Joins)

SELECT
PRHA.SEGMENT1 REQ_NUMBER,
PRHA.AUTHORIZATION_STATUS,
PRHA.TYPE_LOOKUP_CODE,
PRLA.ITEM_DESCRIPTION,
PRLA.UNIT_PRICE,
PRLA.QUANTITY,
PRLA.ITEM_ID,
PRLA.QUANTITY_DELIVERED,
PRLA.QUANTITY_CANCELLED,
PRLA.ORG_ID,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
MSIB.SEGMENT1 "ITEM",
PRDA.DISTRIBUTION_ID,
PDA.PO_DISTRIBUTION_ID,
PDA.REQ_DISTRIBUTION_ID,
PLA.PO_LINE_ID,
PHA.PO_HEADER_ID,
PHA.SHIP_TO_LOCATION_ID,
PHA.AUTHORIZATION_STATUS "PO STATUS",
ASS.VENDOR_NAME,
ASSA.ADDRESS_LINE1||','||ASSA.CITY||','||ASSA.STATE||','||ASSA.ZIP||','||ASSA.COUNTRY,
RT.TRANSACTION_ID,
RT.TRANSACTION_TYPE,
RT.UNIT_OF_MEASURE,
RSL.QUANTITY_SHIPPED,
RSL.QUANTITY_RECEIVED,
RSH.RECEIPT_NUM,
AILA.INVOICE_ID,
AILA.LINE_SOURCE,
AILA.LINE_TYPE_LOOKUP_CODE,
AILA.AMOUNT,
AIA.INVOICE_NUM,
AIA.INVOICE_AMOUNT,
AIA.AMOUNT_PAID,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_NUMBER,
ACA.CHECK_ID,
ACA.STATUS_LOOKUP_CODE,
ACA.CLEARED_AMOUNT,
XAH.DESCRIPTION "INVOICE DESCRIPTION",
GJB.NAME "BATCH NAME",
GJB.STATUS,
GJB.DESCRIPTION "BATCH DESCRIPTION",
GJH.NAME "JOURNAL NAME",
GJH.DESCRIPTION "JOURNAL DESCRIPTION"
from
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
MTL_SYSTEM_ITEMS_B MSIB,
PO_LINE_LOCATIONS_ALL PLLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS ASS,
AP_SUPPLIER_SITES_ALL ASSA,
--AP_SUPPLIER_CONTACTS ASCS
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
where
PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND
PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND
PLLA.SHIP_TO_ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND
PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND
PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND
PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLLA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND
PHA.VENDOR_ID=ASS.VENDOR_ID
AND
ASS.VENDOR_ID=ASSA.VENDOR_ID
--AND
--ASSA.VENDOR_SITE_ID=ASCS.VENDOR_SITE_ID
AND
PDA.PO_DISTRIBUTION_ID=RT.PO_DISTRIBUTION_ID
AND
RT.TRANSACTION_TYPE='RECEIVE'
AND
RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND
RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND
PDA.PO_DISTRIBUTION_ID=AILA.PO_DISTRIBUTION_ID
AND
AILA.INVOICE_ID=AIA.INVOICE_ID
AND
AIA.INVOICE_ID=AIDA.INVOICE_iD
AND
AIDA.INVOICE_ID=AIPA.INVOICE_ID
AND
AIPA.CHECK_ID=ACA.CHECK_ID
AND
XTE.TRANSACTION_NUMBER=AIA.INVOICE_NUM
AND
XTE.ENTITY_ID=XE.ENTITY_ID
AND
XE.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=AIDA.INVOICE_LINE_NUMBER
AND
XE.EVENT_ID=XDL.EVENT_ID
AND
XAH.AE_HEADER_ID=XDL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND
GIR.REFERENCE_5=XTE.ENTITY_ID
AND
GIR.REFERENCE_6=TO_CHAR(XE.EVENT_ID)
AND
GIR.REFERENCE_7=TO_CHAR(XAH.AE_HEADER_ID)
AND
GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND
GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND
GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND
GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND
GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND
PRHA.SEGMENT1=2259;

SQL Queries for Interview

Sql Queries

SELECT * FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'yyyy')<1982;
SELECT * FROM SCOTT.EMP WHERE COMM>SAL;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP,SAL/30 SAL FROM SCOTT.EMP ;--where sal/30>200;
SELECT ENAME,EMPNO,SAL,TO_CHAR(HIREDATE,'day') FROM SCOTT.EMP WHERE TO_CHAR(HIREDATE,'day')IN( 'monday','saturday');
SELECT * FROM SCOTT.EMP WHERE SAL<=1000;
SELECT ENAME,MONTHS_BETWEEN(SYSDATE,HIREDATE) EXP FROM  SCOTT.EMP;
SELECT DISTINCT * FROM SCOTT.EMP;
SELECT DISTINCT(DEPTNO) FROM SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE EMPNO IN(SELECT MGR FROM SCOTT.EMP WHERE SAL>2000);
SELECT * FROM SCOTT.EMP WHERE MGR IS NULL;
SELECT (SAL*12),EMPNO FROM SCOTT.EMP;
SELECT JOB,DEPTNO,ENAME FROM SCOTT.EMP;
SELECT ENAME,SAL,JOB FROM SCOTT.EMP WHERE JOB IN(SELECT JOB FROM SCOTT.EMP WHERE ENAME='FORD');
SELECT * FROM SCOTT.EMP WHERE HIREDATE>(SELECT HIREDATE FROM SCOTT.EMP WHERE ENAME='KING');
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP);
SELECT EMPNO,ENAME,SAL FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL=(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='CLERK');
SELECT AVG(SAL),MIN(SAL),MAX(SAL),ENAME,JOB FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='MANAGER' GROUP BY ENAME,JOB;
SELECT DEPTNO FROM SCOTT.EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*)>=2;
SELECT SUM(NVL(COMM,100)+SAL),DEPTNO FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT ENAME FROM SCOTT.EMP WHERE SAL >(SELECT SAL FROM SCOTT.EMP WHERE ENAME='JONES') AND SAL> (SELECT SAL FROM SCOTT.EMP WHERE ENAME='SCOTT');
SELECT * FROM SCOTT.EMP E WHERE SAL=(SELECT MAX(SAL) FROM SCOTT.EMP  WHERE DEPTNO=E.DEPTNO);
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL<(SELECT SAL FROM SCOTT.EMP WHERE ENAME='PRESIDENT');
SELECT ENAME,LOC FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
CREATE VIEW TEMP_VIEW AS SELECT  ENAME,EMPNO,JOB,SAL,MGR,HIREDATE,E.DEPTNO,LOC,DNAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO;
SELECT * FROM TEMP_VIEW;
SELECT COUNT(EMPNO) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND LOC='NEW YORK';
SELECT  DNAME,LOC,COUNT(EMPNO),AVG(SAL) FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=&DEPTNO GROUP BY DNAME, LOC;
SELECT E.ENAME MGR,F.ENAME  FROM SCOTT.EMP E,SCOTT.EMP F WHERE E.EMPNO=F.MGR;
SELECT JOB FROM SCOTT.EMP  HAVING SUM(SAL) >(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MGR');
SELECT SAL,(CASE WHEN SAL<=1500 THEN 'Low'
WHEN SAL>2000 THEN 'High'
ELSE 'Med'
END ) RANGE,
DECODE(SAL,5000,'King','ASDF') ASD
FROM
SCOTT.EMP;
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' OR JOB='SALESMAN'
OR JOB='ANALYST' AND SAL>3000;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'yyyy')-TO_CHAR(HIREDATE,'yyyy')>=27;
SELECT ENAME FROM SCOTT.EMP WHERE TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(HIREDATE,'YYYY')>=30;
SELECT USERNAME FROM ALL_USERS;
SELECT TNAME FROM TAB;
SHOW USER;
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO IN(10,20,40) OR JOB IN('CLERKS','SALESMAN','ANALYST');
SELECT ENAME,SAL*12 FROM SCOTT.EMP ORDER BY SAL DESC;
SELECT DEPTNO,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY DEPTNO;
SELECT JOB,COUNT(EMPNO)FROM SCOTT.EMP GROUP BY JOB;
SELECT ENAME,SAL FROM SCOTT.EMP WHERE SAL =(SELECT MAX(SAL) FROM SCOTT.EMP);
DISPLAY THE NAMES OF CLERKS WHO EARN A SALARY MORE THAN THE LOWEST SALARY OF ANY SALESMAN.
SELECT ENAME FROM SCOTT.EMP WHERE JOB='CLERK' AND SAL>(SELECT MIN(SAL) FROM SCOTT.EMP WHERE JOB='SALESMAN');
DISPLAY THE NAMES OF THE EMPLOYEES WHO EARN HIGHEST SALARY IN THEIR RESPECTIVE DEPARTMENTS
SELECT ENAME,DEPTNO,SAL FROM SCOTT.EMP WHERE SAL IN(SELECT MAX(SAL) FROM SCOTT.EMP GROUP BY DEPTNO);
SELECT ENAME FROM SCOTT.EMP WHERE DEPTNO=(SELECT DEPTNO FROM SCOTT.DEPT WHERE DNAME='ACCOUNTING');
OR
SELECT ENAME FROM SCOTT.EMP E,SCOTT.DEPT D WHERE E.DEPTNO=D.DEPTNO AND DNAME='ACCOUNTING';
DISPLAY THE JOB GROUPS HAVING TOTAL SALARY GREATER THAN THE MAXIMUM SALARY FOR MANAGERS.
SELECT JOB,SUM(SAL) TOTAL_SAL FROM SCOTT.EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM SCOTT.EMP WHERE JOB='MANAGER');
SELECT ENAME,LENGTH(ENAME) FROM SCOTT.EMP;
SELECT EMPNO,ENAME,DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') FROM SCOTT.EMP;
SELECT TO_DATE(SYSDATE)-TO_DATE('05-may-90')FROM DUAL;
SELECT TO_DATE(SYSDATE)-TO_DATE('17-dec-88')FROM DUAL;
SELECT TO_CHAR(SYSDATE,'ddth Month day year') FROM DUAL;
SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=10 AND JOB IN(SELECT JOB FROM SCOTT.EMP WHERE DEPTNO=20);
DISPLAY THE DETAILS OF THOSE WHO DO NOT HAVE ANY PERSON WORKING UNDER THEM.
SELECT E.ENAME FROM SCOTT.EMP,SCOTT.EMP E WHERE EMP.MGR=E.EMPNO GROUP BY E.ENAME HAVING COUNT(*)=1;
SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR;
SELECT ENAME FROM SCOTT.EMP WHERE ENAME NOT IN(SELECT DISTINCT(M.ENAME) FROM SCOTT.EMP E,SCOTT.EMP M WHERE M.EMPNO=E.MGR);
SELECT M.ENAME FROM SCOTT.EMP E,SCOTT.EMP M WHERE E.EMPNO=M.MGR AND E.ENAME='JONES';
DISPLAY THOSE EMPLOYEES WHOSE SALARY GRETER THAN HIS MANAGER SALARY.
SELECT P.ENAME FROM SCOTT.EMP E,SCOTT.EMP P WHERE E.EMPNO=P.MGR AND P.SAL>E.SAL;
DISPLAY THOSE EMPLOYEE WHOSE SALARY IS EQUAL TO AVERAGE OF MAXIMUM AND MINIMUM?
SELECT ENAME FROM SCOTT.EMP WHERE SAL=(SELECT MAX(SAL)+MIN(SAL)/2 FROM SCOTT.EMP);
SELECT COUNT(*) FROM SCOTT.EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>3
FIND OUT LAST 5(LEAST)EARNERS OF THE COMPANY.?;
SELECT DISTINCT SAL FROM SCOTT.EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM SCOTT.EMP A WHERE A.SAL<=E.SAL) ORDER BY SAL DESC;
SELECT C.ENAME FROM SCOTT.EMP C, SCOTT.EMP E WHERE C.SAL=E.DEPTNO;

order to cash cycle

Oracle-Order to Cash Cycle with Important Columns and Joins

SELECT DISTINCT
OOHA.ORDER_NUMBER,
OOLA.ORDERED_ITEM,
OOLA.ORDERED_QUANTITY,
OOLA.CUST_PO_NUMBER,
OOLA.UNIT_SELLING_PRICE,
HCA.ACCOUNT_NAME CUSTOMER_NAME,
OTTT.NAME ORDER_NAME,
OTTT.DESCRIPTION ORDER_DESC,
QLH.NAME PRICELIST_NAME,
(SELECT LAST_NAME||','||FIRST_NAME FROM PER_ALL_PEOPLE_F WHERE PERSON_ID=RSA.PERSON_ID) SALESREP,HCSUA.LOCATION,
HPS.PARTY_SITE_NAME,
HL.ADDRESS1||','||HL.CITY||','||HL.POSTAL_CODE||','||HL.STATE||','||HL.COUNTRY||','||HL.ADDRESS_KEY TOTAL_ADDRESS,
MSIB.SEGMENT1 ITEM,
MSIB.DESCRIPTION,
MSIB.INVENTORY_ITEM_STATUS_CODE,
MSIB.ITEM_TYPE,
WDD.REQUESTED_QUANTITY,
WDD.SHIPPED_QUANTITY,
WDD.DELIVERED_QUANTITY,
OOHA.FLOW_STATUS_CODE HEADERS_CODE,
OOLA.FLOW_STATUS_CODE LINES_CODE,
WDD.RELEASED_STATUS,
WND.DELIVERY_ID,
WND.NAME DELIVERY_NUMBER,
RCTA.TRX_NUMBER,
ACRA.AMOUNT,
ACRA.RECEIPT_NUMBER,
GJB.NAME BACTH_NAME,
GJB.DESCRIPTION  BATCH_DESCRIPTION,
GJH.NAME JOURNAL_NAME,
GJH.STATUS JOURNAL_STATUS,
GJH.DESCRIPTION JE_DESCRIPTION
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
HZ_CUST_ACCOUNTS HCA,
OE_TRANSACTION_TYPES_TL OTTT,
QP_LIST_HEADERS QLH,
RA_SALESREPS_ALL RSA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES  HPS,
HZ_LOCATIONS HL,
MTL_SYSTEM_ITEMS_B MSIB,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUSTOMER_TRX_ALL RCTA,
AR_PAYMENT_SCHEDULES_ALL APSA,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
AR_CASH_RECEIPTS_ALL ACRA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE
OOHA.HEADER_ID=OOLA.HEADER_ID
AND
OOHA.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND
OOHA.ORDER_TYPE_ID=OTTT.TRANSACTION_TYPE_ID
AND
OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND
OOHA.SALESREP_ID=RSA.SALESREP_ID
AND
OOHA.SHIP_TO_ORG_ID=HCSUA.SITE_USE_ID
AND
HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
AND
HCASA.PARTY_SITE_ID=HPS.PARTY_SITE_ID
AND
HPS.LOCATION_ID=HL.LOCATION_ID
AND
OOLA.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND
OOLA.SHIP_FROM_ORG_ID=MSIB.ORGANIZATION_ID
AND
OOLA.HEADER_ID=WDD.SOURCE_HEADER_ID
AND
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND
WDA.DELIVERY_ID=WND.DELIVERY_ID
AND
RCTLA.INTERFACE_LINE_ATTRIBUTE1=TO_CHAR(OOHA.ORDER_NUMBER)
AND
RCTLA.CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID
AND
RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND
APSA.CUSTOMER_TRX_ID=ARAA.APPLIED_CUSTOMER_TRX_ID
AND
ARAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND
XTE.TRANSACTION_NUMBER=RCTA.TRX_NUMBER
AND
XTE.ENTITY_ID=XE.ENTITY_ID
AND
XE.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND
GIR.GL_SL_LINK_TABLE='XLAJEL'
AND
GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND
GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND
GJH.JE_HEADER_ID=GJH.JE_HEADER_ID
AND
OOHA.ORDER_NUMBER=5854;

Outbound Interface In Oracle Apps R12 - Complete Process With Example

Outbound Interface:

Outbound Interface will be used to extract the data from oracle Database tables into the flat files.

This is One of the PL/SQL Package which will be used to transfer the data from table to files from files to tables

But when we are working for file to table we will use SQl *Loader to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

UTL_FILE Package:

1)Utl_File.fopen     = To open (or) Create the file

2)Utl_File.Put_line  = To Transfer the data into the File.

3)Utl_File.fclose    = To close the File after Data transfer.

Outbound Interface Process:

 To Find  UTL Path:

SELECT * FROM V$PARAMETER

WHERE UPPER(NAME) LIKE '%UTL%';

Select From List of UTL Paths:

/usr/tmp,

/oracle/DEV/db/tech_st/12.1.03/appsutil/outbound/DEV_seaerpdev, 

/oracle/DEV/inst/apps/DEV_seaerpdev/logs/appl/conc/log, 

/oracle/DEV/inst/apps/DEV_seaerpdev/logs/appl/conc/out,

/oracle/DEV/apps/apps_st/appl/SFDC/sfdc_ebs,

/oracle/DEV/apps/apps_st/appl/SFDC/in/CanceledSalesOrders/CSOL

Sample Table Query To Get Output  :

 

-------------------------------------------------------------------------------------------------------------

Out Bound Interface Program:

DECLARE

CURSOR C1 IS

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOHA.CREATION_DATE,OOLA.ORDERED_ITEM,OOLA.ORDERED_QUANTITY

FROM OE_ORDER_HEADERS_ALL OOHA,OE_ORDER_LINES_ALL OOLA

WHERE OOHA.HEADER_ID=OOLA.HEADER_ID

AND OOHA.SOLD_TO_ORG_ID=8428

AND OOHA.FLOW_STATUS_CODE<>'CLOSED';

V_FILE  UTL_FILE.FILE_TYPE;

V_COUNT NUMBER;

BEGIN

V_COUNT:=0;

V_FILE:=UTL_FILE.FOPEN('/usr/tmp','XXSEA_ORDER_EXTRACT.csv','W');

FOR I IN C1  

LOOP

V_COUNT:=V_COUNT+1;

UTL_FILE.PUT_LINE(V_FILE,I.ORDER_NUMBER||','||I.FLOW_STATUS_CODE||','||I.CREATION_DATE||','||I.ORDERED_ITEM||','||I.ORDERED_QUANTITY);

DBMS_OUTPUT.PUT_LINE(I.ORDER_NUMBER||','||I.FLOW_STATUS_CODE||','||I.CREATION_DATE||','||I.ORDERED_ITEM||','||I.ORDERED_QUANTITY);

END LOOP;

UTL_FILE.FCLOSE(V_FILE);

DBMS_OUTPUT.PUT_LINE('count :'||V_COUNT);

END;

------------------------------------------------------------------------------------------------------------------------------------------------------------

    Please Comment Your Suggestion

      Contact US :

      Maheshsuper7@gmail.com,

      Vanamsanthosh615@gmail.com

Oracle Apps Inbound Interface Process - Complete Process With Example

Inbound Interface :

Inbound Interface will be used to upload the data from legacy system (Flat files) into
Oracle Applications base tables.

While Developing the Inbound interface  we will use SQL * loader to import the data
into base tables.

Here one Example for Inbound Process

        1. Sample Flat File Creation:

Move Flat File(txt file) into Server through WinScp

Create Table Structure as txt File:

      2.Sample Control File:

        

       3.Move CTL To Server (WinScp):

     4.Register SQL * Loader  Concurrent Program:

      To Find

               ·         User id

               ·         User_resp_id

               ·         user_resp_appl_id

SELECT U.USER_ID USER_ID

,      R.RESPONSIBILITY_ID RESP_ID

,      R.APPLICATION_ID RESP_APPL_ID

,      R.RESPONSIBILITY_KEY RESP_KEY

FROM   FND_USER U

,      FND_USER_RESP_GROUPS UR

,      FND_RESPONSIBILITY R

WHERE  U.USER_NAME = 'USER_NAME'

AND    UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID

AND    UR.USER_ID = U.USER_ID;

   5.Interface Program :

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

CREATE OR REPLACE PROCEDURE  XXSEA_INTERFACES AS

CURSOR C1 IS

SELECT * FROM XXSEA_INTERFACE;

V_COUNT NUMBER;

V_ORG_COUNT NUMBER;

V_TEMP_COUNT NUMBER;

L_ITEM_TABLE EGO_ITEM_PUB.ITEM_TBL_TYPE;

X_ITEM_TABLE EGO_ITEM_PUB.ITEM_TBL_TYPE;

X_RETURN_STATUS VARCHAR2(1);

X_MSG_COUNT NUMBER(10);

X_MSG_DATA VARCHAR2(1000);

X_MESSAGE_LIST ERROR_HANDLER. ERROR_TBL_TYPE;

V_USER_ID NUMBER;

V_RESP_ID NUMBER;

V_RESP_APPL_ID NUMBER;

J NUMBER;

BEGIN

J:=0;

FND_GLOBAL.APPS_INITIALIZE(USER_ID => 3392,

RESP_ID => 21876,

RESP_APPL_ID  =>401);

FOR I IN C1

LOOP

J:=J+1;

BEGIN

SELECT COUNT(SEGMENT1) INTO V_COUNT FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1=I.ITEM_NUMBER AND ORGANIZATION_ID=I.ORGANIZATION_ID;

DBMS_OUTPUT.PUT_LINE('ITEM COUNT'|| V_COUNT);

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'-'||SQLERRM );

END;

BEGIN

SELECT COUNT(ORGANIZATION_ID) INTO V_ORG_COUNT FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID=I.ORGANIZATION_ID ;

DBMS_OUTPUT.PUT_LINE('ORG COUNT'||V_ORG_COUNT);

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE || '-' || SQLERRM);

END;

BEGIN

SELECT   COUNT (TEMPLATE_ID)

INTO   V_TEMP_COUNT

FROM   MTL_ITEM_TEMPLATES

WHERE   TEMPLATE_NAME = I.TEMPLATE_NAME;

DBMS_OUTPUT.PUT_LINE('TEMPLATE_COUNT'|| V_TEMP_COUNT);

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE ||'-'|| SQLERRM);

END;

IF V_COUNT=0 AND V_ORG_COUNT>0 AND V_TEMP_COUNT>0 THEN

L_ITEM_TABLE(J).TRANSACTION_TYPE:='CREATE';

L_ITEM_TABLE(J).SEGMENT1:=I.ITEM_NUMBER;

L_ITEM_TABLE(J).DESCRIPTION:=I.DESCRIPTION;

L_ITEM_TABLE(J).TEMPLATE_NAME:=I.TEMPLATE_NAME;

L_ITEM_TABLE(J).ORGANIZATION_ID:=I.ORGANIZATION_ID;

ELSE

DBMS_OUTPUT.PUT_LINE('RECORD ALREADY EXISTS');

END IF ;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('CALLING API TO CREATE ITEM');

EGO_ITEM_PUB.PROCESS_ITEMS (P_API_VERSION     => 1.0,

P_INIT_MSG_LIST   => FND_API.G_TRUE,

P_COMMIT          => FND_API.G_TRUE,

P_ITEM_TBL        => L_ITEM_TABLE,

X_ITEM_TBL        => X_ITEM_TABLE,

X_RETURN_STATUS   => X_RETURN_STATUS,

X_MSG_COUNT       => X_MSG_COUNT);

DBMS_OUTPUT.PUT_LINE ('RETURN STATUS ==>' || X_RETURN_STATUS);

IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS)

THEN

FOR I IN 1 .. X_ITEM_TABLE.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE('INVENTORY ITEM ID CREATED:'

|| TO_CHAR (

X_ITEM_TABLE (I).INVENTORY_ITEM_ID

));

DBMS_OUTPUT.PUT_LINE('ORGANIZATION ID :'

|| TO_CHAR (

X_ITEM_TABLE (I).ORGANIZATION_ID

));

END LOOP;

ELSE

DBMS_OUTPUT.PUT_LINE ('ERROR MESSAGES :');

ERROR_HANDLER.GET_MESSAGE_LIST (X_MESSAGE_LIST => X_MESSAGE_LIST);

FOR I IN 1 .. X_MESSAGE_LIST.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE (X_MESSAGE_LIST (I).MESSAGE_TEXT);

END LOOP;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE (

'ERROR HAS OCCURED AND ERROR IS ' || SUBSTR (SQLERRM, 1, 200)

);

END;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    Please Comment Your Suggestion


      Contact US :
      Maheshsuper7@gmail.com,
      Vanamsanthosh615@gmail.com