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)
No comments:
Post a Comment