通过PO接口表导入PO数据
我们通过PO接口表导入PO数据程序如下,可以导入成功,
但是用户要求,通过接口表导入后,PO直接提交到指定的审批路径,即导入后PO的状态为in process.
我有两个问题:
1. 提交PO导入请求时:Import Standard Purchase Orders 时有四个参数:1) Default Buyer 2)Create or Update Item 3) Approval status 4) Batch id
由于Approval status:只有三个状态:incomplete, approved, initiate incomplete, 请问各位大侠有没有办法,使通过接口表导入的PO变成in process状态.
2. 如果导入的PO中有某个ITEM,系统中不存在,请问系统怎样自动创建, 我发现在接交Import Standard Purchase Orders 时有一个参数:Create or Update Item 选Y但系统并不会自动创建ITEM.
导入的SQL如下:
/* Formatted on 2009/03/09 10:25 (Formatter Plus v4.8.8) */
/* 自动生成PO号的导入*/
DECLARE
header_seq NUMBER;
line_seq NUMBER;
dis_seq NUMBER;
/* Basic data*/
CURSOR c1
IS
SELECT DISTINCT a.batch_id, a.action, a.process_code, a.po_type,
a.REFERECNCE_NUM, a.currency_code, pv.vendor_id,
pvsa.vendor_site_id, hlat.location_id ship_to_location_id, a.org_id
FROM zhopl.oplk_po_input_temp a,
inv.mtl_system_items_b msib,
po.po_vendors pv,
po.po_vendor_sites_all pvsa,
hr_locations_all_tl hlat
WHERE a.item = msib.segment1
AND DECODE (a.org_id, 6, 8,25,26,2,4,67,68,87,88) = msib.organization_id
AND a.vendor = pv.vendor_name
AND a.site_code = pvsa.vendor_site_code
AND hlat.location_code=a.ship_to
ORDER BY a.REFERECNCE_NUM;
/* The data to import into po_line_temp table*/
CURSOR c2
IS
SELECT a.REFERECNCE_NUM, a.line_num, a.shipment_num, msib.inventory_item_id,
a.uom unit_of_measure, a.qty, a.unit_price, a.promised_date,
hlat.location_id ship_to_location_id, 'NA' line_attribute4
FROM zhopl.oplk_po_input_temp a, inv.mtl_system_items_b msib,hr_locations_all_tl hlat
WHERE a.item = msib.segment1 AND msib.organization_id = 8
AND hlat.location_code=a.ship_to
ORDER BY a.REFERECNCE_NUM, a.line_num;
/*The data to import into po_distributinn_temp table */
CURSOR c3
IS
SELECT a.REFERECNCE_NUM, a.line_num, a.shipment_num, a.qty,
1002 charge_account_id, 8 destination_organization_id
FROM zhopl.oplk_po_input_temp a
ORDER BY a.REFERECNCE_NUM, a.line_num, a.shipment_num;
/* The data to import po_headers_interface*/
CURSOR c4
IS
SELECT *
FROM zhopl.oplk_po_header_iface_temp;
/*The data to import po_lines_interface*/
CURSOR c5 (po_num VARCHAR2)
IS
SELECT *
FROM zhopl.oplk_po_line_iface_temp
WHERE REFERECNCE_NUM = po_num
ORDER BY REFERECNCE_NUM, line_num, shipment_num;
/*The data to import po_distributions_interface*/
CURSOR c6 (kpo_num VARCHAR2, kline_num NUMBER, kshipment_num NUMBER)
IS
SELECT *
FROM zhopl.oplk_po_distribution_temp
WHERE REFERECNCE_NUM = kpo_num
AND line_num = kline_num
AND shipment_num = kshipment_num
ORDER BY REFERECNCE_NUM, line_num, shipment_num, distribution_num;
BEGIN
DELETE FROM zhopl.oplk_po_header_iface_temp;
DELETE FROM zhopl.oplk_po_line_iface_temp;
DELETE FROM zhopl.oplk_po_distribution_temp;
DELETE FROM po_headers_interface;
DELETE FROM po_lines_interface;
DELETE FROM po_distributions_interface;
COMMIT;
/* BEGIN INSERT RECORDS INTO TEMPORARY PO HEADER IFACE TABLE*/
FOR r1 IN c1
LOOP
INSERT INTO zhopl.oplk_po_header_iface_temp
(batch_id, action, process_code, document_type_code,
REFERECNCE_NUM, currency_code, vendor_id,
vendor_site_id, ship_to_location_id, org_id
)
VALUES (r1.batch_id, r1.action, r1.process_code, r1.po_type,
r1.REFERECNCE_NUM, r1.currency_code, r1.vendor_id,
r1.vendor_site_id, r1.ship_to_location_id, r1.org_id
);
END LOOP;
/* BEGIN INTER RECORDS INTO TEMPORARY PO LINE IFACE TABLE*/
FOR r2 IN c2
LOOP
INSERT INTO zhopl.oplk_po_line_iface_temp
(REFERECNCE_NUM, line_num, shipment_num,
item_id, unit_of_measure, quantity,
unit_price, promised_date, ship_to_location_id,
line_attribute4
)
VALUES (r2.REFERECNCE_NUM, r2.line_num, r2.shipment_num,
r2.inventory_item_id, r2.unit_of_measure, r2.qty,
r2.unit_price, r2.promised_date, r2.ship_to_location_id,
r2.line_attribute4
);
END LOOP;
/*BEGIN INSERT RECORDS INTO TEMPORARY PO DISTRIBUTION TABLE*/
FOR r3 IN c3
LOOP
INSERT INTO zhopl.oplk_po_distribution_temp
(REFERECNCE_NUM, line_num, distribution_num,
quantity_ordered, charge_account_id,
destination_organization_id, shipment_num
)
VALUES (r3.REFERECNCE_NUM, r3.line_num, 1,
r3.qty, r3.charge_account_id,
r3.destination_organization_id, r3.shipment_num
);
END LOOP;
COMMIT;
/* BEGIN INPUT PO_HEADERS_INTERFACE TABLE */
FOR r4 IN c4
LOOP
SELECT po.po_headers_interface_s.NEXTVAL
INTO header_seq
FROM DUAL;
INSERT INTO po.po_headers_interface
(batch_id, interface_header_id, action, document_type_code,
process_code, reference_num, vendor_id,
vendor_site_id, currency_code,
ship_to_location_id, org_id
)
VALUES (r4.batch_id, header_seq, r4.action, r4.document_type_code,
r4.process_code, r4.REFERECNCE_NUM, r4.vendor_id,
r4.vendor_site_id, r4.currency_code,
r4.ship_to_location_id, r4.org_id
);
/*BEGIN INSERT RECORDS INTO PO_LINES_INTERFACE TABLE */
FOR r5 IN c5 (r4.REFERECNCE_NUM)
LOOP
SELECT po.po_lines_interface_s.NEXTVAL
INTO line_seq
FROM DUAL;
INSERT INTO po.po_lines_interface
(interface_line_id, interface_header_id, line_num,
item_id, unit_of_measure, quantity,
unit_price, ship_to_location_id, shipment_num,
promised_date, line_attribute4,line_attribute5,line_attribute6
,line_attribute7,line_attribute8,line_attribute9,line_attribute10
,line_attribute11
)
VALUES (line_seq, header_seq, r5.line_num,
r5.item_id, r5.unit_of_measure, r5.quantity,
r5.unit_price, r5.ship_to_location_id, r5.shipment_num,
r5.promised_date, r5.line_attribute4,r5.line_attribute4,
r5.line_attribute4,r5.line_attribute4,r5.line_attribute4
,r5.line_attribute4,r5.line_attribute4,r5.line_attribute4
);
/*BEGIN INSERT RECORDS INTO PO_DISTRIBUTIONS_INTERFACE TABLE */
FOR r6 IN c6 (r5.REFERECNCE_NUM, r5.line_num, r5.shipment_num)
LOOP
SELECT po.po_distributions_interface_s.NEXTVAL
INTO dis_seq
FROM DUAL;
INSERT INTO po_distributions_interface
(interface_header_id, interface_line_id,
interface_distribution_id, distribution_num,
quantity_ordered, charge_account_id,
destination_organization_id
)
VALUES (header_seq, line_seq,
dis_seq, r6.distribution_num,
r6.quantity_ordered, r6.charge_account_id,
r6.destination_organization_id
);
END LOOP;
END LOOP;
END LOOP;
COMMIT;
END;
但是用户要求,通过接口表导入后,PO直接提交到指定的审批路径,即导入后PO的状态为in process.
我有两个问题:
1. 提交PO导入请求时:Import Standard Purchase Orders 时有四个参数:1) Default Buyer 2)Create or Update Item 3) Approval status 4) Batch id
由于Approval status:只有三个状态:incomplete, approved, initiate incomplete, 请问各位大侠有没有办法,使通过接口表导入的PO变成in process状态.
2. 如果导入的PO中有某个ITEM,系统中不存在,请问系统怎样自动创建, 我发现在接交Import Standard Purchase Orders 时有一个参数:Create or Update Item 选Y但系统并不会自动创建ITEM.
导入的SQL如下:
/* Formatted on 2009/03/09 10:25 (Formatter Plus v4.8.8) */
/* 自动生成PO号的导入*/
DECLARE
header_seq NUMBER;
line_seq NUMBER;
dis_seq NUMBER;
/* Basic data*/
CURSOR c1
IS
SELECT DISTINCT a.batch_id, a.action, a.process_code, a.po_type,
a.REFERECNCE_NUM, a.currency_code, pv.vendor_id,
pvsa.vendor_site_id, hlat.location_id ship_to_location_id, a.org_id
FROM zhopl.oplk_po_input_temp a,
inv.mtl_system_items_b msib,
po.po_vendors pv,
po.po_vendor_sites_all pvsa,
hr_locations_all_tl hlat
WHERE a.item = msib.segment1
AND DECODE (a.org_id, 6, 8,25,26,2,4,67,68,87,88) = msib.organization_id
AND a.vendor = pv.vendor_name
AND a.site_code = pvsa.vendor_site_code
AND hlat.location_code=a.ship_to
ORDER BY a.REFERECNCE_NUM;
/* The data to import into po_line_temp table*/
CURSOR c2
IS
SELECT a.REFERECNCE_NUM, a.line_num, a.shipment_num, msib.inventory_item_id,
a.uom unit_of_measure, a.qty, a.unit_price, a.promised_date,
hlat.location_id ship_to_location_id, 'NA' line_attribute4
FROM zhopl.oplk_po_input_temp a, inv.mtl_system_items_b msib,hr_locations_all_tl hlat
WHERE a.item = msib.segment1 AND msib.organization_id = 8
AND hlat.location_code=a.ship_to
ORDER BY a.REFERECNCE_NUM, a.line_num;
/*The data to import into po_distributinn_temp table */
CURSOR c3
IS
SELECT a.REFERECNCE_NUM, a.line_num, a.shipment_num, a.qty,
1002 charge_account_id, 8 destination_organization_id
FROM zhopl.oplk_po_input_temp a
ORDER BY a.REFERECNCE_NUM, a.line_num, a.shipment_num;
/* The data to import po_headers_interface*/
CURSOR c4
IS
SELECT *
FROM zhopl.oplk_po_header_iface_temp;
/*The data to import po_lines_interface*/
CURSOR c5 (po_num VARCHAR2)
IS
SELECT *
FROM zhopl.oplk_po_line_iface_temp
WHERE REFERECNCE_NUM = po_num
ORDER BY REFERECNCE_NUM, line_num, shipment_num;
/*The data to import po_distributions_interface*/
CURSOR c6 (kpo_num VARCHAR2, kline_num NUMBER, kshipment_num NUMBER)
IS
SELECT *
FROM zhopl.oplk_po_distribution_temp
WHERE REFERECNCE_NUM = kpo_num
AND line_num = kline_num
AND shipment_num = kshipment_num
ORDER BY REFERECNCE_NUM, line_num, shipment_num, distribution_num;
BEGIN
DELETE FROM zhopl.oplk_po_header_iface_temp;
DELETE FROM zhopl.oplk_po_line_iface_temp;
DELETE FROM zhopl.oplk_po_distribution_temp;
DELETE FROM po_headers_interface;
DELETE FROM po_lines_interface;
DELETE FROM po_distributions_interface;
COMMIT;
/* BEGIN INSERT RECORDS INTO TEMPORARY PO HEADER IFACE TABLE*/
FOR r1 IN c1
LOOP
INSERT INTO zhopl.oplk_po_header_iface_temp
(batch_id, action, process_code, document_type_code,
REFERECNCE_NUM, currency_code, vendor_id,
vendor_site_id, ship_to_location_id, org_id
)
VALUES (r1.batch_id, r1.action, r1.process_code, r1.po_type,
r1.REFERECNCE_NUM, r1.currency_code, r1.vendor_id,
r1.vendor_site_id, r1.ship_to_location_id, r1.org_id
);
END LOOP;
/* BEGIN INTER RECORDS INTO TEMPORARY PO LINE IFACE TABLE*/
FOR r2 IN c2
LOOP
INSERT INTO zhopl.oplk_po_line_iface_temp
(REFERECNCE_NUM, line_num, shipment_num,
item_id, unit_of_measure, quantity,
unit_price, promised_date, ship_to_location_id,
line_attribute4
)
VALUES (r2.REFERECNCE_NUM, r2.line_num, r2.shipment_num,
r2.inventory_item_id, r2.unit_of_measure, r2.qty,
r2.unit_price, r2.promised_date, r2.ship_to_location_id,
r2.line_attribute4
);
END LOOP;
/*BEGIN INSERT RECORDS INTO TEMPORARY PO DISTRIBUTION TABLE*/
FOR r3 IN c3
LOOP
INSERT INTO zhopl.oplk_po_distribution_temp
(REFERECNCE_NUM, line_num, distribution_num,
quantity_ordered, charge_account_id,
destination_organization_id, shipment_num
)
VALUES (r3.REFERECNCE_NUM, r3.line_num, 1,
r3.qty, r3.charge_account_id,
r3.destination_organization_id, r3.shipment_num
);
END LOOP;
COMMIT;
/* BEGIN INPUT PO_HEADERS_INTERFACE TABLE */
FOR r4 IN c4
LOOP
SELECT po.po_headers_interface_s.NEXTVAL
INTO header_seq
FROM DUAL;
INSERT INTO po.po_headers_interface
(batch_id, interface_header_id, action, document_type_code,
process_code, reference_num, vendor_id,
vendor_site_id, currency_code,
ship_to_location_id, org_id
)
VALUES (r4.batch_id, header_seq, r4.action, r4.document_type_code,
r4.process_code, r4.REFERECNCE_NUM, r4.vendor_id,
r4.vendor_site_id, r4.currency_code,
r4.ship_to_location_id, r4.org_id
);
/*BEGIN INSERT RECORDS INTO PO_LINES_INTERFACE TABLE */
FOR r5 IN c5 (r4.REFERECNCE_NUM)
LOOP
SELECT po.po_lines_interface_s.NEXTVAL
INTO line_seq
FROM DUAL;
INSERT INTO po.po_lines_interface
(interface_line_id, interface_header_id, line_num,
item_id, unit_of_measure, quantity,
unit_price, ship_to_location_id, shipment_num,
promised_date, line_attribute4,line_attribute5,line_attribute6
,line_attribute7,line_attribute8,line_attribute9,line_attribute10
,line_attribute11
)
VALUES (line_seq, header_seq, r5.line_num,
r5.item_id, r5.unit_of_measure, r5.quantity,
r5.unit_price, r5.ship_to_location_id, r5.shipment_num,
r5.promised_date, r5.line_attribute4,r5.line_attribute4,
r5.line_attribute4,r5.line_attribute4,r5.line_attribute4
,r5.line_attribute4,r5.line_attribute4,r5.line_attribute4
);
/*BEGIN INSERT RECORDS INTO PO_DISTRIBUTIONS_INTERFACE TABLE */
FOR r6 IN c6 (r5.REFERECNCE_NUM, r5.line_num, r5.shipment_num)
LOOP
SELECT po.po_distributions_interface_s.NEXTVAL
INTO dis_seq
FROM DUAL;
INSERT INTO po_distributions_interface
(interface_header_id, interface_line_id,
interface_distribution_id, distribution_num,
quantity_ordered, charge_account_id,
destination_organization_id
)
VALUES (header_seq, line_seq,
dis_seq, r6.distribution_num,
r6.quantity_ordered, r6.charge_account_id,
r6.destination_organization_id
);
END LOOP;
END LOOP;
END LOOP;
COMMIT;
END;
