PO_DISTRIBUTIONS_INTERFACE INTERFACE PROCESS

Inbound Interface:

It will be used to upload the data from legacy

system flat file to oracle apps base table. For this we will use SQL LOADER program and  UTL_FILE( PL/SQL program).

Here we will follow  below six steps

1)Develop the temporary table.

2)Develop the staging table and alter the staging table.

3)Develop the control file (.ctl) and register the control file.

4)Transfer the data from Temporary table to staging table .

5)validate the staging table data.

6)Transfer the data staging to interface table.

NOTE:

To load the data from flat file to oracle  apps data base tables . I will prefer sql  loader, why because sql loader is simple to identifying the flat file errors and database errors. It is very simple compare than UTL_FILE and more familiar to developer.

Here I am going to work on PO Module under  purchasing, vision operations(USA).OK.

Step1:  TEMPARARY TABLE CREATION:

CREATE TABLE XXX_PO_DISTRIBUTIONS_TEMP

(INTERFACE_HEADER_ID,

INTERFACE_LINE_ID,

INTERFACE_DISTRIBUTION_ID,

SET_OF_BOOKS_ID,

DESTINATION_ORGANIZATION_ID,

ORG_ID,

QUANTITY_ORDERED 

)

AS 

SELECT 

INTERFACE_HEADER_ID,

INTERFACE_LINE_ID,

INTERFACE_DISTRIBUTION_ID,

SET_OF_BOOKS_ID,

DESTINATION_ORGANIZATION_ID,

ORG_ID,

QUANTITY_ORDERED

FROM  PO_DISTRIBUTIONS_INTERFACE

WHERE 1=7;

Step2:  STAGING TABLE CREATION:

SQL>CREATE TABLE XXX_PO_DISTRIBUTIONS_STAG

AS 

SELECT * FROM PO_DISTRIBUTIONS_INTERFACE 

WHERE 1=7; 

ALTER THE STAGING TABLE:

SQL>ALTER TABLE XXX_PO_DISTRIBUTIONS_STAG ADD

(STATUS VARCHAR2(2),

 ERR_MSG VARCHAR2(4000)

);

SQL>DESC XXX_PO_DISTRIBUTIONS_STAG;

Step3: DEVELOP  THE CONTROL FILE (.CTL):

LOAD DATA 
INFILE *
TRUNCATE INTO TABLE XXX_PO_DISTRIBUTIONS_TEMP
FIELDS TERMINATED BY','
OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(
INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
DESTINATION_ORGANIZATION_ID,
ORG_ID,
QUANTITY_ORDERED
)
BEGINDATA
1,21,1,1,207,204,10
1,22,2,1,207,204,10
1,23,3,1,207,204,10
1,24,4,1,207,204,10
1,25,5,1,207,204,10
2,26,6,1,207,204,222
2,27,7,1,207,204,222
2,28,8,1,207,204,222
2,29,9,1,207,204,222
2,30,10,1,207,204,222
3,31,11,1,207,204,222

IMPORTANT POINTS WHILE CREATION OF SQL LOADER:

1)SAVE THE FILE .ctl EXTENTIONS

 EX:  XXX_PO_DISTRIBUTIONS.ctl.

2) “.ctl”  SHOULD BE IN SMALL  LETTERS ONLY OTHER WISE IT WILL GIVE ERROR.

3)IN LINUX ENVIRONMENT  WE SHOULD CHECK THE FILE PERMISSIONS FROM OPERATION SYSTEM.OK.

4) TO TRANSFER THE  “.ctl” FILE FROM CLIENT TO SERVER (XP OR SERVER 2003 TO LINUX ENVIRONMENT) THROUGH WINSCP OR PUTTY.

 5) THE LINUX  PURCHASE ORDER CORRESPONDING FOLDER PATH IS

 “/oraAS/oracle/VIS/apps/apps_st/appl/inv/12.0.0/bin”

6)REGISTER THE “.ctl” FILE BY USING SQL* LOADER

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILTY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> EXECUTABLE

CONCURRENT PROGRAM CREATION:

SYSTEM ADMINISTRATOR RESPONSIBILTY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> DEFINE

 ATTACH TO REQUEST GROUP:

GOTO

SYSTEM ADMINISTRATOR RESPONSIBILTY

----SECURITY--->RESPONSIBILTY--->REQUEST.

RUN THE CONCURRENT PORGRAM: UNDER THE Inventory,Visionoperations(USA).

   GO TO VIEW MENU

   ---->SELECT RQUEST:

Step4: TRANSFOR THE DATA FROM TEMPRARY TABLE TO STAGING TABLE: (register the pl/sql procedure also):

CREATE OR REPLACE PROCEDURE PO_DIST_MOVE_TO_STAG(errbuf out varchar2,
retcode out varchar2
)
IS 
I NUMBER;
BEGIN
  INSERT INTO XXX_PO_DISTRIBUTIONS_STAG
  (
   INTERFACE_HEADER_ID,
   INTERFACE_LINE_ID,
   INTERFACE_DISTRIBUTION_ID,
   SET_OF_BOOKS_ID,
   DESTINATION_ORGANIZATION_ID,
   ORG_ID,
   QUANTITY_ORDERED     
  )
  SELECT
   INTERFACE_HEADER_ID,
   INTERFACE_LINE_ID,
   INTERFACE_DISTRIBUTION_ID,
   SET_OF_BOOKS_ID,
   DESTINATION_ORGANIZATION_ID,
   ORG_ID,
   QUANTITY_ORDERED              
  FROM XXX_PO_DISTRIBUTIONS_TEMP;
DBMS_OUTPUT.PUT_LINE('TOTAL RECORD(S) INSERTED'||SQL%ROWCOUNT);
EXCEPTION
 WHEN OTHERS THEN 
 DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);
END PO_DIST_MOVE_TO_STAG;

SQL>SELECT  * FROM XXX_PO_DISTRIBUTIONS_STAG;

 REGISTER  THE PROCEDURE:

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> EXECUTABLE

CONCURRENT PROGRAM CREATION:

SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> DEFINE

ATTACH TO REQUEST GROUP:

GOTO

SYSTEM ADMINISTRATOR RESPONSIBILITY

     ----SECURITY

          --->RESPONSIBILITY

                --->REQUEST

RUN THE CONCURRENT PORGRAM: UNDER THE  Inventory,Vision Operations(USA).

   GO TO VIEW MENU 
         ---->SELECT RQUEST.

SQL>SELECT * FROM XXX_PO_DISTRIBUTIONS_STAG;

Step5: VALIDATE THE STAGING TABLE DATA (REGISTER THE PL/SQL PROCEDURE ALOS):

CREATE OR REPLACE PROCEDURE XXX_PO_DIST_STAG_VALIDATION(errbuf out varchar2,

      retcode out varchar2

)

IS 

I_COUNT NUMBER;

I_ERR_FLAG NUMBER:=0;

I_ERR_MSG VARCHAR2(2000);

CURSOR C1 

IS 

SELECT 

     A.INTERFACE_HEADER_ID,

   A.INTERFACE_LINE_ID,

   A.INTERFACE_DISTRIBUTION_ID,

   A.SET_OF_BOOKS_ID,

   A.DESTINATION_ORGANIZATION_ID,

   A.ORG_ID,

   A.QUANTITY_ORDERED

FROM XXX_PO_DISTRIBUTIONS_STAG A

WHERE NVL(STATUS,'T') IN ('T','E');

  BEGIN

  FOR C_REC IN C1

  LOOP

  BEGIN

  I_ERR_FLAG:=0;

  I_ERR_MSG:=NULL;

  I_COUNT:=0;

  --VALIDATION INTERFACE_HEADER_ID

  SELECT COUNT(*) INTO I_COUNT 

         FROM PO_HEADERS_INTERFACE

         WHERE INTERFACE_HEADER_ID=C_REC.INTERFACE_HEADER_ID;

  IF I_COUNT=0 THEN 

  I_ERR_FLAG:=1;

  I_ERR_MSG:='**INVALID INTERFACE HEADER ID';

  END IF;

  

  --VALIDATION INTERFACE_LINE_ID

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT 

         FROM PO_LINES_INTERFACE

         WHERE INTERFACE_LINE_ID=C_REC.INTERFACE_LINE_ID;

  IF I_COUNT=0 THEN 

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID INTERFACE LINE ID';

  END IF;

  --VALIDATE THE DISTRIBUTION_ID

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT 

         FROM PO_DISTRIBUTIONS_INTERFACE

         WHERE          INTERFACE_DISTRIBUTION_ID=C_REC.INTERFACE_DISTRIBUTION_ID;

  IF I_COUNT>0 THEN 

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID INTERFACE DISTRIBUTION ID';

  END IF;

   --VALIDATE THE SET_OF_BOOKS_ID

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT 

         FROM Gl_sets_of_books WHERE           SET_OF_BOOKS_ID=C_REC.SET_OF_BOOKS_ID;

  IF I_COUNT=0 THEN 

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID SET OF BOOKS ID';

  END IF;

  -- VALIDATE THE DESTINATION_ORGANIZATION_ID

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT 

         FROM ORG_ORGANIZATION_DEFINITIONS 

WHERE ORGANIZATION_ID=C_REC.DESTINATION_ORGANIZATION_ID;

  IF I_COUNT=0 THEN 

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID DESTINATION_ORGANIZATION_ID ID';

  END IF;

  --VALIDATE THE QUANTITY

  IF C_REC.QUANTITY_ORDERED=0 THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID QUANTITY_ORDERED';

  END IF;

--update the staging table 

  IF I_ERR_FLAG=1 THEN 

  UPDATE XXX_PO_DISTRIBUTIONS_STAG 

  SET STATUS='E',ERR_MSG=I_ERR_MSG

  WHERE INTERFACE_DISTRIBUTION_ID=C_REC.INTERFACE_DISTRIBUTION_ID;

  ELSE

  UPDATE XXX_PO_DISTRIBUTIONS_STAG

  SET STATUS='V'

  WHERE INTERFACE_DISTRIBUTION_ID=C_REC.INTERFACE_DISTRIBUTION_ID;

  END IF;

EXCEPTION

 WHEN OTHERS THEN 

 NULL;

 END;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE('VALIDATION COMPLETED SUCCESSFULLY');

END XXX_PO_DIST_STAG_VALIDATION;

 RESULT: EXECUTED SUCCESSFULLY.

NOW WE ARE GOING TO REGISTER THE PROCEDURE.

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> EXECUTABLE

CONCURRENT PROGRAM CREATION:

SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> DEFINE

ATTACH TO REQUEST GROUP:

GOTO

SYSTEM ADMINISTRATOR RESPONSIBILITY

    ----SECURITY

         --->RESPONSIBILITY

                --->REQUEST.

RUN THE CONCURRENT PORGRAM: UNDER THE  Inventory,Vision Operations(USA).

GO TO VIEW MENU 

      ---->SELECT RQUEST:

SQL>SELECT * FROM XXX_PO_DISTRIBUTIONS_STAG;

STEP 6: TRANSFOR THE DATA STAGING TO INTERFACE TABLE REGISTER THE PL/SQL PROCEDURE ALOS):

CREATE OR REPLACE PROCEDURE XXX_PO_DISTR_STAG_INT(ERRBUF OUT VARCHAR2,
 RETCODE OUT VARCHAR2
)
IS
I NUMBER;
BEGIN
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(
INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
DESTINATION_ORGANIZATION_ID,
ORG_ID,
QUANTITY_ORDERED
)
SELECT 
INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
SET_OF_BOOKS_ID,
DESTINATION_ORGANIZATION_ID,
ORG_ID,
QUANTITY_ORDERED
FROM XXX_PO_DISTRIBUTIONS_STAG
WHERE STATUS='V';
DBMS_OUTPUT.PUT_LINE('TOTAL RECORD(S) INSERTED'||SQL%ROWCOUNT);
EXCEPTION
 WHEN OTHERS THEN 
 DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);

END XXX_PO_DISTR_STAG_INT;

RESULT: PROCEDURE EXECUTED SUCCESSFULLY.

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> EXECUTABLE

CONCURRENT PROGRAM CREATION:

SYSTEM ADMINISTRATOR RESPONSIBILTY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> DEFINE

     ATTACH TO REQUEST GROUP:

   GOTO

   SYSTEM ADMINISTRATOR RESPONSIBILITY

     ----SECURITY

             --->RESPONSIBILITY

                    --->REQUEST.

RUN THE CONCURRENT PORGRAM: UNDER THE  Inventory,Vision Operations(USA).

GO TO VIEW MENU 

          ---->SELECT RQUEST:

 SQL>SELECT * FROM PO_DISTRIBUTIONS_INTERFACE WHERE  INTERFACE_DISTRIBUTION_ID IN(1,2,3,4,5,6,7,8,9,10,11);

HI FRIEND'S  YOU HAVE ANY DOUBTS PLEASE SEND A MAIL,MY e-MAIL ID IS : nvn.oracleapps@gmail.com 

THANKING YOU FRIENDS

                                                                     (Ur’s:VenkataNaresh)