PO INTERFACE

INTERFACES

          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 BACE 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 (PO_HEADERS_ALL ORPO_HEADES_INTERFACE), Purchasing,Vision operations(USA).OK.

CREATE TABLE   XXX_PO_HEADERS_TEMP

(INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME               

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

)

AS SELECT

INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME               

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

FROM PO_HEADERS_INTERFACE 

WHERE 1=7;

Note: Here the above select statement have one “where clause”. If the where condition  is true Table will be created along with data else

Table have only structure.(you will give only satisfied condition only. Other wise you have to face big problem).

CHECK IT ONCE.

SQL>DESC  XXX_PO_HEADERS_TEMP;

Name                                          Null?           Type

 ----------------------------------------- --------           -------------

INTERFACE_HEADER_ID        NOT NULL   NUMBER

 BATCH_ID                                                     NUMBER

 ACTION                                                         VARCHAR2(25)

 ORG_ID                                                         NUMBER

 DOCUMENT_TYPE_CODE                          VARCHAR2(25)

 CURRENCY_CODE                                      VARCHAR2(15)

 AGENT_NAME                                              VARCHAR2(240)

 VENDOR_NAME                                           VARCHAR2(240)

 VENDOR_SITE_CODE                                 VARCHAR2(15)

 SHIP_TO_LOCATION                                   VARCHAR2(60)

 BILL_TO_LOCATION                                    VARCHAR2(60)

 APPROVAL_STATUS                                   VARCHAR2(25)

 FREIGHT_CARRIER                                     VARCHAR2(25)

 FOB                                                                VARCHAR2(25)

 FREIGHT_TERMS                                         VARCHAR2(25)

SQL>SELECT  *  FROM  XXX_PO_HEADERS_TEMP;

RESULT: NO ROWS SELECTED.

Step2:  STAGING FILE CREATION:

CREATE TABLE XXX_PO_HEADERS_STAG

AS

SELECT * FROM PO_HEADERS_INTERFACE WHER 1=7;

ALTER STAGING TABLE:

ALTER TABLE XXX_PO_HEADERS_STAG ADD (STATUS VARCHAR2(2),

                                           ERR_MSG VARCHAR2(4000)

                                           );

Step3: DEVELOP  THE CONTROL FILE (.CTL) (register the control file alos)

LOAD DATA

INFILE *

TRUNCATE  INTO  TABLE  XXX_PO_HEADERS_TEMP

FIELDS  TERMINATED   BY "," 

OPTIONALLY  ENCLOSED BY '"'

TRAILING  NULLCOLS

( INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME               

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

)

BEGINDATA

1,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","3M Health Care","PARIS","S1- Chicago","V1- New York City","APPROVED","UPS","Origin","Due"

2,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","Dell Computers","DELL","S1- Chicago","V1- New York City","APPROVED","UPS","Origin","Due"

3,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","Abbott Laboratories, Inc.","VHS ABBOTT","S1- Chicago","V1- New York City","APPROVED","UPS","Origin","Due"

IMPORTANT POINTS WHILE CREATION OF SQL LOADER:

1)SAVE THE FILE .ctl  EXTENSIONS

 EX: " XXX_PO_HEADERS.ctl."

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

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

4) TO TRANSFOR 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/po/12.0.0/bin”

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

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 PROGRAM: UNDER THE PURCHASING,VISION OPERATIONS(USA).

GO TO VIEW MENU 
        ---->SELECT RQUESTS

   HERE ANY ERRORS ARE THERE PLEASE FIND ON  View Log    BUTTON.

  --->GOTO TOA

  SQL>SELECT * FROM XXX_PO_HEADERS_TEMP;

IT WILL SHOW THE ALL DATA. WHAT EVER WE ARE TRANSFORING FROM FLAT FILE.OK.

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

Here we will follow two ways. One is individual  procedure creation. other one is package creation. Both the ways working is same but while creation of  Executable small changes is there.

Her e I will show you individual procedure creation for every step.ok.

àgo to sql prompt àconnect to apps/apps@vis

    Execute the below procedure .ok.

SQL>CREATE OR REPLACE PROCEDURE

XXX_PO_HED_TEMP_STG(errbuf out  varchar2,

                                                 retcode out  varchar2

                                               )

IS

I NUMBER;

BEGIN

INSERT INTO XXX_PO_HEADERS_STAG(

   INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME               

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

  )

  SELECT

  INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME                

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

   FROM XXX_PO_HEADERS_TEMP;

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

EXCEPTION

 WHEN OTHERS THEN

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

END XXX_PO_HED_TEMP_STG;

RE:

PROCEDURE IS CREATED SUCCESSFULLY.

àTHEN REGISTER THE  ABOVE PROCEDURE

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 PROGRAM: UNDER THE PURCHASING,VISION OPERATIONS(USA).

GO TO  VIEW MENU
               ---->SELECT RQUESTS:

HERE ANY ERRORS ARE THERE PLEASE FIND ON  View Log  BUTTON.

--->GOTO TOA

SQL>SELECT * FROM XXX_PO_HEADERS_STAG;

IT WILL SHOW THE ALL DATA. WHAT EVER WE ARE TRANSFORING FROM FLAT FILE.OK.

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

SQL> CREATE OR REPLACE PROCEDURE XXX_PO_HED_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.BATCH_ID          

  ,A.ACTION              

  ,A.ORG_ID                   

  ,A.DOCUMENT_TYPE_CODE       

  ,A.CURRENCY_CODE            

  ,A.AGENT_NAME               

  ,A.VENDOR_NAME              

  ,A.VENDOR_SITE_CODE         

  ,A.SHIP_TO_LOCATION         

  ,A.BILL_TO_LOCATION

  ,A.APPROVAL_STATUS

  ,A.FREIGHT_CARRIER

  ,A.FOB

  ,A.FREIGHT_TERMS

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

  --VALIDATE THE ORGANIZATION ID

  I_COUNT:=0;

  SELECT COUNT(*) INTO I_COUNT

          FROM ORG_ORGANIZATION_DEFINITIONS

            WHERE ORGANIZATION_ID=C_REC.ORG_ID;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE BATCH_ID

  I_COUNT:=0;

  IF C_REC.BATCH_ID=NULL THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**BATCH ID IS NULL';

  END IF;

  -- VALIDATE THE DOCUMENT_TYPE_CODE

  IF C_REC.DOCUMENT_TYPE_CODE NOT IN('STANDARD','BLANKET','PLANNED','CONTRACT') THEN

  I_ERR_FLAG:=1;

  I_ERR_MSG:=I_ERR_MSG||'**DOCUMENT ID IS INVALID';

  END IF;

  --VALIDATE THE AGENT_NAME

  I_COUNT:=0;

  SELECT COUNT(BUYER_ID) INTO I_COUNT

           FROM PO_AGENTS_NAME_V

           WHERE FULL_NAME=C_REC.AGENT_NAME;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE VENDOR_NAME

  I_COUNT:=0;

  SELECT COUNT(VENDOR_ID) INTO I_COUNT

          FROM PO_VENDORS

          WHERE VENDOR_NAME=C_REC.VENDOR_NAME;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE VENDOR_SITE_CODE

  I_COUNT:=0;

  SELECT COUNT(VENDOR_SITE_ID) INTO I_COUNT

         FROM PO_VENDOR_SITES_ALL

         WHERE VENDOR_SITE_CODE=C_REC.VENDOR_SITE_CODE;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  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;

  -- VALIDATE THE BILL_TO_LOCATION

  I_COUNT:=0;

  SELECT COUNT(LOCATION_ID) INTO I_COUNT

         FROM HR_LOCATIONS_ALL

           WHERE LOCATION_CODE=C_REC.BILL_TO_LOCATION;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

  --VALIDATE THE CURRENCY_CODE

  I_COUNT:=0;

  SELECT COUNT(CURRENCY_CODE) INTO I_COUNT

         FROM FND_CURRENCIES

         WHERE CURRENCY_CODE=C_REC.CURRENCY_CODE;

  IF I_COUNT=0 THEN

  I_ERR_FLAG:=1;

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

  END IF;

--UPDATING THE STAGING TABLE

  IF I_ERR_FLAG=1 THEN

  UPDATE XXX_PO_HEADERS_STAG SET STATUS='E',ERR_MSG=I_ERR_MSG WHERE INTERFACE_HEADER_ID=C_REC.INTERFACE_HEADER_ID;

ELSE

  UPDATE XXX_PO_HEADERS_STAG SET STATUS='V'

         WHERE INTERFACE_HEADER_ID=C_REC.INTERFACE_HEADER_ID;

  END IF;

EXCEPTION

 WHEN OTHERS THEN

 NULL;

 END;

 END LOOP;

 DBMS_OUTPUT.PUT_LINE('VALIDATION COMPLETED SUCCESSFULLY');

END XXX_PO_HED_STAG_VALIDATION;

RESULT:

PROCEDURE CREATED SUCESSFULLY.

---> REGISTER THE  ABOVE PROCEDURE THROUGH CONCURRENT PROGRAM.

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILITY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> EXECUTABLE

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILTY.

      ---> CONCURRENT

           ---> PROGRAM

                   ---> DEFINE

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILTY.

      ---> SECURITY

           ---->RESPONSIBILITY

                   --->REQUEST

RUN THE CONCURRENT PORGRAM: UNDER THE PURCHASING,VISION OPERATIONS(USA).

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

HERE ANY ERRORS ARE THERE PLEASE FIND ON  View Log  BUTTON.

--->GOTO TOA

   SQL>SELECT * FROM XXX_PO_HEADERS_STAG;

IT WILL SHOW THE ALL DATA AND UPDATE THE ERR_MSG,STATUS COLUMN . IF THERE IS ANY ERROR THE ERROR STATU IS “E” ELSE IT  “V” (V  MEANS NO ERRORS).OK.

Step 6:Transfer the data staging to interface table register the pl/sql procedure alos)

SQL> CREATE OR REPLACE PROCEDURE XXX_PO_HED_STG_INTERFACE(errbuf out varchar2,

                                                               retcode out varchar2

                                                               )

IS

I NUMBER;

BEGIN

INSERT INTO PO_HEADERS_INTERFACE(

   INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION              

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME                

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

  )

  SELECT

  INTERFACE_HEADER_ID

  ,BATCH_ID          

  ,ACTION               

  ,ORG_ID                   

  ,DOCUMENT_TYPE_CODE       

  ,CURRENCY_CODE            

  ,AGENT_NAME               

  ,VENDOR_NAME              

  ,VENDOR_SITE_CODE         

  ,SHIP_TO_LOCATION         

  ,BILL_TO_LOCATION

  ,APPROVAL_STATUS

  ,FREIGHT_CARRIER

  ,FOB

  ,FREIGHT_TERMS

   FROM XXX_PO_HEADERS_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_HED_STG_INTERFACE;

RESULT:

PROCEDURE  CREATED SUCCESSFULLY.

--->THEN REGISTER THE  ABOVE PROCEDURE

GOTO 

--->SYSTEM ADMINISTRATOR RESPONSIBILTY.

      --->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 PROGRAM: UNDER THE  PURCHASING,VISION OPERATIONS(USA).

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

    HERE ANY ERRORS ARE THERE PLEASE FIND ON  View Log  BUTTON.

--->GOTO TOAD

SQL>SELECT * FROM PO_HEADERS_INTERFACE WHERE INTERFACE_HEADER_ID IN(1,2,3);

SQL>FINALLLY TAHE DATA WILL BE TRANSFOR INTO INTERFACE TABLE.

   It Will Show The All Data. What Ever We Are Transforing From Flat    File.Ok.

Hi FRIENDS THIS IS VENKATANARESH.IF YOU HAVE ANY MORE DOUBTS PLEASE SEND A EMAIL: nvn.oracleapps@gmail.com. or POST ON COMMENTS.
                                                                                                                            (Ur's:Venkatanaresh)