Interfaces in oracle apps(R12) technical: November 2014

Wednesday, 26 November 2014

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

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

)

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)

I_COUNT NUMBER;

I_ERR_FLAG NUMBER:=0;

I_ERR_MSG VARCHAR2(2000);

I_UOM_CODE VARCHAR2(20);

CURSOR C1

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)

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)

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

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

)

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

)

I_COUNT NUMBER;

I_ERR_FLAG NUMBER:=0;

I_ERR_MSG VARCHAR2(2000);

CURSOR C1

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

)

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)