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)

PO_LINE_INTRFACE INTERFACE IN ORACLE APPS

AN  OTHER  EXAMPLE  FOR  INBOUND  INTERFACE (PO_LINES_INTRFACE):

IT IS NOTHING BUT A PROGRAM; IT WILL BE USED TO TRANSFER THE DATA FROM FLAT FILE TO DATA BASE TABLE OR FROM DATA BASE TABLE TO FLAT FILE. TWO TYPES OF INTERFACE GIVEN BELOW.

 1.INBOUND INTERFACE.

    2. OUTBOUND INTERFACE.

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 I WILL FOLLOW 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.

Step1: TEMPARORY TABLE CREATION:

SQL>CREATE TABLE XXX_PO_LINES_TEMP

(

 INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

   ,LIST_PRICE_PER_UNIT

 )

 SELECT

   INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

   ,LIST_PRICE_PER_UNIT

   FROM PO_LINES_INTERFACE

   WHERE 1=7;

2)STAGING TABLE CREATION:

SQL>CREATE TABLE XXX_PO_LINES_STAG

AS

SELECT * FROM PO_LINES_INTERFACE

WHERE 1=7;

ALTER THE STAGIN TABLE:

SQL>ALTER TABLE XXX_PO_LINES_STAG

ADD(STATUS VARCHAR2(2),

    ERR_MSG VARCHAR2(4000);

    );

Step3: Develop the control file (.ctl) and register the control file:

LOAD DATA

INFILE *

TRUNCATE INTO TABLE XXX_PO_LINES_TEMP

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

 

)

BEGINDATA

1,21,3,2,"Goods","SKYESS_SUZIKI","SKYESS MOTORS",209955,"Ea",10,120,"M1","Adelaide"

1,22,3,2,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",10,120,"M1","Adelaide"

1,23,3,2,"Goods","SKYESS_R12","SKYESS_R12",211956,"Ea",10,120,"M1","Adelaide"

1,24,3,2,"Goods","SKYESS_BULL","SKYESS BULLLS",211955,"Ea",10,120,"M1","Adelaide"

1,25,3,2,"Goods","SB10299","Cover Assembly",233,"Ea",10,120,"M1","Adelaide"

2,26,2,1,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",222,234,"M1","Adelaide"

2,27,2,1,"Goods","SKYESS_BULBS","SKYESS ORGANIZATION",208955,"Ea",222,234,"M1","Adelaide"

2,28,2,1,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",222,234,"M1","Adelaide"

2,29,2,1,"Goods","SB10450","Chassis - 450 Sentinel Standard",1781,"Ea",222,234,"M1","Adelaide"

2,30,2,1,"Goods","SB10460","Cover Assembly",676,"Ea",222,234,"M1","Adelaide"

3,31,2,1,"Goods","SKYESS_BULBS","SKYESS ORGANIZATION",208955,"Ea",222,234,"M1","Adelaide"

Note:”.ctl ” Registration is same as shown in before example.

Step4: Transfer  the data from temporary table to staging table:

CREATE OR REPLACE PROCEDURE PO_LINE_MOVE_TO_STAG(errbuf out varchar2,

                                      retcode out varchar2

                                         )

IS

I NUMBER;

BEGIN

  INSERT INTO XXX_PO_LINES_STAG

  (

   INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

  )

  SELECT INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION               

  FROM XXX_PO_LINES_TEMP;

DBMS_OUTPUT.PUT_LINE('TOTAL RECORD(S) INSERTED'||SQL%ROWCOUNT);

EXCEPTION

 WHEN OTHERS THEN

 DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);

END PO_LINE_MOVE_TO_STAG;

Note: PROCEDURE REGISTRATION ALSO SAME AS SHOWN AS PRIVIOUS EXAMPLE.

RESULT:

PROCEDURE CREATED SUCCESSFULLY.

SQL> SELECT * FROM XXX_PO_LINES_STAG;

IT WILL SHOW THE RESULT.

Step5: Validate the staging table data:

SQL> CREATE OR REPLACE PROCEDURE PO_LINE_VALDATE_SAGE(ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

AS

I_COUNT NUMBER;

I_ERR_FLAG NUMBER:=0;

I_ERR_MSG VARCHAR2(2000);

I_UOM_CODE VARCHAR2(20);

CURSOR C1

IS

SELECT

   A.INTERFACE_HEADER_ID

  ,A.INTERFACE_LINE_ID

  ,A.LINE_NUM                       

  ,A.SHIPMENT_NUM                   

  ,A.LINE_TYPE                      

  ,A.ITEM

  ,A.ITEM_DESCRIPTION

  ,A.ITEM_ID                          

  ,A.UOM_CODE                       

  ,A.QUANTITY                       

  ,A.UNIT_PRICE                     

  ,A.SHIP_TO_ORGANIZATION_CODE      

  ,A.SHIP_TO_LOCATION               

  FROM XXX_PO_LINES_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;

  -- VALIDATE ALL COLUMNS

  --VALIDATION HEDER_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 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;

  --VALIDATION OF LINE_NUM

  IF C_REC.LINE_NUM=0 THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID INTERFACE LINE_NUM';

  END IF;

  --VALIDATE THE SHIPMENT_NUM

  IF C_REC.SHIPMENT_NUM=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE ITEM

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT

         FROM MTL_SYSTEM_ITEMS_B

         WHERE SEGMENT1=C_REC.ITEM OR SEGMENT2=C_REC.ITEM;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID INVENTORY ITEM';

  END IF;

  --VALIDATE THE ITEM_DESCRIPTION

  IF C_REC.ITEM_DESCRIPTION=NULL THEN

  I_ERR_FLAG:=1;

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

  END IF;

 

  --VALIDATE THE ITEM_ID

   I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT

         FROM MTL_SYSTEM_ITEMS_B

         WHERE INVENTORY_ITEM_ID=C_REC.ITEM_ID;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**INVALID INVENTORY ITEM_ID';

  END IF;

 

  --VALIDATE THE QUANTITY

  IF C_REC.QUANTITY=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

 

  --VALIDATE THE UNIT_PRICE

  IF C_REC.UNIT_PRICE=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT

          FROM ORG_ORGANIZATION_DEFINITIONS

         WHERE ORGANIZATION_CODE=C_REC.SHIP_TO_ORGANIZATION_CODE;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

 

  -- VALIDATE THE SHIP_TO_LOCATION

  I_COUNT:=0;

  SELECT COUNT(LOCATION_ID) INTO I_COUNT

         FROM HR_LOCATIONS_ALL

        WHERE LOCATION_CODE=C_REC.SHIP_TO_LOCATION;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**SHIP_TO_LOCATION IS INVALID';

  END IF;

--UPDATING THE STAGING TABLE

  IF I_ERR_FLAG=1 THEN

  UPDATE XXX_PO_LINES_STAG SET STATUS='E',ERR_MSG=I_ERR_MSG

         WHERE INTERFACE_LINE_ID=C_REC.INTERFACE_LINE_ID;

  ELSE

  UPDATE XXX_PO_LINES_STAG SET STATUS='V'

         WHERE INTERFACE_LINE_ID=C_REC.INTERFACE_LINE_ID;

  END IF;

EXCEPTION

 WHEN OTHERS THEN

 NULL;

 END;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE('VALIDATION COMPLETED SUCCESSFULLY');

END PO_LINE_VALDATE_SAGE;

NOTE: Note: PROCEDURE REGISTRATION ALSO SAME AS SHOWN AS PRIVIOUS EXAMPLE.

RESULT:

PROCEDURE CREATED SUCCESSFULLY.

SQL> SELECT * FROM XXX_PO_LINES_STAG;

IT WILL SHOW THE RESULT.

NOTE:HERE WE CAN VERIFY THE TWO COLUMNS MAINLY 1)STATUS

2)ERR_MSG.OK.

Step6: Transfer the data from staging to interface table:

SQL> CREATE OR REPLACE PROCEDURE PO_LINE_MOVE_TO_INTERFACE(ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2)

IS

I NUMBER;

BEGIN

  INSERT INTO PO_LINES_INTERFACE

  (

   INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                   

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION

  )

  SELECT

  INTERFACE_HEADER_ID

  ,INTERFACE_LINE_ID

  ,LINE_NUM                       

  ,SHIPMENT_NUM                    

  ,LINE_TYPE                      

  ,ITEM

  ,ITEM_DESCRIPTION

  ,item_id                          

  ,UOM_CODE                       

  ,QUANTITY                       

  ,UNIT_PRICE                     

  ,SHIP_TO_ORGANIZATION_CODE      

  ,SHIP_TO_LOCATION

  FROM XXX_PO_LINES_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 PO_LINE_MOVE_TO_INTERFACE;

RESULT:

PROCEDURE CREATED SUCCESSFULLY.

SQL> SELECT * FROM PO_LINES_INTERFACE  WHERE INTERFACE_ HEADER_ID IN(1,2,3);

IT WILL SHOW THE RESULT.

Hi FRIENDS THIS IS VENKATANARESH.IF YOU HAVE ANY MORE DOUBTS PLEASE SEND A EMAIL: nvn.oracleapps@gmail.com

                                                                                  (Ur’s: Venkatanaresh)