Validations and Data Sample its your wish
1.Lines:
a. CREATE TABLE XXPO_LINES_STG
(
INTERFACE_HEADER_ID NUMBER
,INTERFACE_LINE_ID NUMBER
,LINE_NUM NUMBER
,LIST_PRICE_PER_UNIT NUMBER
,SHIPMENT_NUM NUMBER
,LINE_TYPE VARCHAR2(100)
,ITEM VARCHAR2(240)
,ITEM_DESCRIPTION VARCHAR2(240)
,item_id NUMBER
,UOM_CODE VARCHAR2(30)
,QUANTITY NUMBER
,UNIT_PRICE NUMBER
,SHIP_TO_ORGANIZATION_CODE VARCHAR2(40)
,SHIP_TO_LOCATION VARCHAR2(60)
);
b. load data
infile '--------------------------------------------------'
truncate into table XXPO_LINES_STG
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,LINE_NUM
,LIST_PRICE_PER_UNIT
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,ITEM_ID
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_LOCATION
)
c. CREATE OR REPLACE PROCEDURE xxpo_lines_conv(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxpo_lines_stg%ROWTYPE INDEX BY BINARY_INTEGER;
v_vendor_id VARCHAR2(100);
v_interface_header_id VARCHAR2(100);
v_item VARCHAR2(150);
v_vendors test_array ;
BEGIN
SELECT * BULK COLLECT INTO v_vendors FROM xxpo_lines_stg;
FOR i IN 1..v_vendors.COUNT
LOOP
INSERT INTO po_lines_interface
(
interface_line_id
,interface_header_id
,ship_to_location_id
,line_num
,shipment_num
,line_type
,item
,item_description
,item_id
,uom_code
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_code
--,ship_to_location
,need_by_date
,promised_date
,list_price_per_unit
)
VALUES
( v_vendors(i).interface_line_id --po_lines_interface_s.nextval
, v_vendors(i).interface_header_id --po_headers_interface_s.CURRVAL--v_interface_header_id
,144
,v_vendors(i).line_num
,v_vendors(i).shipment_num
,v_vendors(i).line_type
,v_vendors(i).item
,v_vendors(i).item_description
,v_vendors(i).item_id
,v_vendors(i).uom_code
,'Each'
,v_vendors(i).quantity
,v_vendors(i).unit_price
,v_vendors(i).ship_to_organization_code
--,v_vendors(i).SHIP_TO_LOCATION
,SYSDATE --done
,SYSDATE --done
,v_vendors(i).list_price_per_unit
);
END LOOP;
COMMIT;
END;
/
2. Headers:
a. CREATE TABLE XXPO_HEADERS_STG
(
INTERFACE_HEADER_ID 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)
,FREIGHT_CARRIER VARCHAR2(25)
,FOB VARCHAR2(25)
,FREIGHT_TERMS VARCHAR2(25)
);
b. load data
infile '------------------------------------'
insert into table XXPO_HEADERS_STG
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)
c. CREATE OR REPLACE PROCEDURE xxpo_header_conv(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxpo_headers_stg%ROWTYPE INDEX BY BINARY_INTEGER;
v_vendor_id VARCHAR2(100);
v_vendor_int_id VARCHAR2(100);
v_vendors test_array ;
BEGIN
SELECT * BULK COLLECT INTO v_vendors FROM xxpo_headers_stg;
FOR i IN 1..v_vendors.COUNT
LOOP
INSERT INTO po_headers_interface
(
interface_header_id
,batch_id
,action
,org_id
,document_type_code
,currency_code
,agent_id
,agent_name
,vendor_name
,vendor_site_code
,vendor_site_id
,ship_to_location
,bill_to_location
,creation_date
,approval_status
,approved_date
,freight_carrier
,fob
,freight_terms
)
VALUES
(
v_vendors(i).interface_header_id--po_headers_interface_s.NEXTVAL
,v_vendors(i).batch_id --po_headers_interface_s.CURRVAL
,v_vendors(i).action
,v_vendors(i).org_id --"Puffer-Sweiven"
,v_vendors(i).document_type_code --done
,v_vendors(i).currency_code
,61 --done
,v_vendors(i).agent_name --done
,v_vendors(i).vendor_name
,v_vendors(i).vendor_site_code
,1120
,v_vendors(i).ship_to_location
,v_vendors(i).bill_to_location
,SYSDATE-10 --done
,v_vendors(i).approval_status
,SYSDATE --done
,v_vendors(i).freight_carrier
,v_vendors(i).fob
,v_vendors(i).freight_terms
);
END LOOP;
COMMIT;
END;
/
3. Distributions:
a. CREATE TABLE XXPO_DISTRIBUTION_STG
(
INTERFACE_HEADER_ID NUMBER,
INTERFACE_LINE_ID NUMBER,
INTERFACE_DISTRIBUTION_ID NUMBER,
DISTRIBUTION_NUM NUMBER,
QUANTITY_ORDERED NUMBER,
QTY_DELIVERED NUMBER,
QTY_BILLED NUMBER,
QTY_CANCELLED NUMBER,
DELIVER_TO_LOCATION_ID NUMBER,
DELIVER_TO_PERSON_ID NUMBER,
SET_OF_BOOKS VARCHAR2(50),
CHARGE_ACCT VARCHAR2(2000),
AMOUNT_BILLED NUMBER
);
b. load data
infile '------------------------------------------'
truncate into table XXPO_DISTRIBUTION_STG
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,INTERFACE_DISTRIBUTION_ID
,DISTRIBUTION_NUM
,QUANTITY_ORDERED
,QTY_DELIVERED
,QTY_BILLED
,QTY_CANCELLED
,DELIVER_TO_LOCATION_ID
,DELIVER_TO_PERSON_ID
,SET_OF_BOOKS
,CHARGE_ACCT
,AMOUNT_BILLED)
c. CREATE OR REPLACE PROCEDURE xxpo_distribution_conv(x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER)
IS
TYPE test_array IS TABLE OF xxpo_distribution_stg%ROWTYPE INDEX BY BINARY_INTEGER;
v_interface_header_id VARCHAR2(100);
v_interface_line_id VARCHAR2(150);
v_item VARCHAR2(150);
v_vendors test_array ;
BEGIN
SELECT * BULK COLLECT INTO v_vendors FROM xxpo_distribution_stg;
FOR i IN 1..v_vendors.COUNT
LOOP
INSERT INTO po_distributions_interface
(
interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account,
charge_account_id,
variance_account_id,
accrual_account_id,
quantity_delivered,
quantity_billed,
quantity_cancelled,
deliver_to_location_id,
deliver_to_person_id,
org_id
)
VALUES
(
v_vendors(i).interface_header_id ,--po_headers_interface_s.currval,--v_interface_header_id,
v_vendors(i).interface_line_id ,--po_lines_interface_s.currval,--v_interface_line_id, --nvl(v_interface_line_id,1),
v_vendors(i).interface_distribution_id, --distributions_interface_s.nextval,
v_vendors(i).distribution_num,
v_vendors(i).quantity_ordered,
v_vendors(i).charge_acct,
1001,
1000,
1006,
v_vendors(i).qty_delivered,
v_vendors(i).qty_billed,
v_vendors(i).qty_cancelled,
v_vendors(i).deliver_to_location_id,
v_vendors(i).deliver_to_person_id,
104
);
END LOOP;
COMMIT;
END;
/
No comments:
Post a Comment