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