INTERFACE TABLE:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
BASE TABLE:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
CONCURRENT PROGRAM NAME:
IMPORT STANDARD PURCHASE ORDER
ERROR TABLE
PO_INTERFACE_ERRORS
STAGGING TABLE:
HEADER TABLE:
Create table V_PO_HEADER(
INTERFACE_HEADER_ID NUMBER,
ORG_ID NUMBER,
DOCUMENT_TYPE_CODE VARCHAR2(25),
SHIP_TO_LOCATION_ID NUMBER,
BILL_TO_LOCATION_ID NUMBER,
AGENT_NAME VARCHAR2(240),
APPROVAL_STATUS VARCHAR2(25),
CURRENCY_CODE VARCHAR2(15),
PROCESSING_ID NUMBER,
BATCH_ID NUMBER,
ACTION VARCHAR2(25),
VENDOR_ID NUMBER,
VENDOR_SITE_ID NUMBER);
LINE TABLE:
create table V_PO_LINES(
INTERFACE_HEADER_ID NUMBER,
INTERFACE_LINE_ID NUMBER,
LINE_NUM NUMBER,
LINE_TYPE VARCHAR2(25),
ITEM VARCHAR2(1000),
CATEGORY VARCHAR2(2000),
ITEM_DESCRIPTION VARCHAR2(240),
UOM_CODE VARCHAR2(3),
QUANTITY NUMBER,
UNIT_PRICE NUMBER,
PROCESSING_ID NUMBER,
ACTION VARCHAR2(25));
DATA FILE FOR HEADER:
910001,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910002,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910003,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910004,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910005,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910006,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910007,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910008,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910009,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910010,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910011,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910012,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910013,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910014,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910015,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910016,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910017,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910018,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910019,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
910020,204,STANDARD,207,204,ChangSeKang,INCOMPLETE,USD,25,100,ORIGINAL,600,1414
CONTROL FILE FOR HEADER:
LOAD DATA
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/pur_heads.txt'
INSERT INTO TABLE V_PO_HEADER
FIELDS TERMINATED BY ","
(INTERFACE_HEADER_ID,
ORG_ID,
DOCUMENT_TYPE_CODE,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
AGENT_NAME,
APPROVAL_STATUS,
CURRENCY_CODE,
PROCESSING_ID,
BATCH_ID,
ACTION,
VENDOR_ID,
VENDOR_SITE_ID)
DATA FILE FOR LINE:
910001,55001,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910002,55002,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910003,55003,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910004,55004,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910005,55005,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910006,55006,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910007,55007,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910008,55008,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910009,55009,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910010,55010,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910011,55011,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910012,55012,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910013,55013,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910014,55014,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910015,55015,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910016,55016,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910017,55017,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910018,55018,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910019,55019,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
910020,55020,1,Goods,ISO002,MISC.MISC,ISO002,Ea,7,7,25,ORIGINAL
CONTROL FILE FOR LINE:
LOAD DATA
INFILE '/oracle2/VIS1211/apps/apps_st/appl/po/12.0.0/bin/pur_line.txt'
INSERT INTO TABLE V_PO_LINES
FIELDS TERMINATED BY ","
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
LINE_NUM,
LINE_TYPE,
ITEM,
CATEGORY,
ITEM_DESCRIPTION,
UOM_CODE,
QUANTITY,
UNIT_PRICE,
PROCESSING_ID,
ACTION)
After Finishing this move the Header and Line file to gftp.
Run the Header and Line Control File in SQL Loader.
PROCEDURE PROGRAM
CREATE OR REPLACE PROCEDURE pur_proce (errbuf out varchar2,retcode out varchar2) is
CURSOR c1 IS SELECT * FROM V_PO_HEADER;
CURSOR c2 IS SELECT * FROM V_PO_LINES;
BEGIN
FOR i IN c1 LOOP
INSERT INTO po_headers_interface
(INTERFACE_HEADER_ID,
ORG_ID,
DOCUMENT_TYPE_CODE,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
AGENT_NAME,
APPROVAL_STATUS,
CURRENCY_CODE,
PROCESSING_ID,
BATCH_ID,
ACTION,
VENDOR_ID,
vendor_site_id)
VALUES (i.INTERFACE_HEADER_ID,
i.ORG_ID,
i.DOCUMENT_TYPE_CODE,
i.SHIP_TO_LOCATION_ID,
i.BILL_TO_LOCATION_ID,
i.AGENT_NAME,
i.APPROVAL_STATUS,
i.CURRENCY_CODE,
i.PROCESSING_ID,
i.BATCH_ID,
i.ACTION,
i.VENDOR_ID,
i.VENDOR_SITE_ID);
END LOOP;
FOR j IN c2 LOOP
INSERT INTO po_lines_interface
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
LINE_NUM,
LINE_TYPE,
ITEM,
CATEGORY,
ITEM_DESCRIPTION,
UOM_CODE,
QUANTITY,
UNIT_PRICE,
PROCESSING_ID,
ACTION )
VALUES (j.INTERFACE_HEADER_ID,
j.INTERFACE_LINE_ID,
j.LINE_NUM,
j.LINE_TYPE,
j.ITEM,
j.CATEGORY,
j.ITEM_DESCRIPTION,
j.UOM_CODE,
j.QUANTITY,
j.UNIT_PRICE,
j.PROCESSING_ID,
j.ACTION );
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
Run the Procedure using PL/SQL Stored Preocedure.
Finally Run the Concurrent Program Name:IMPORT STANDARD PURCHASE ORDER.
QUERY:
select * from V_PO_HEADER
select * from V_PO_LINES
select * from po_headers_interface
select * from po_lines_interface
select * from po_interface_errors
select * from po_headers_all where request_id=5832984
select * from po_lines_all where request_id=5832984
No comments:
Post a Comment