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