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