ORACLE 采购订单导入(API)

DECLARE
l_iface_rec po.po_headers_interface%ROWTYPE;
l_iface_lines_rec po.po_lines_interface%ROWTYPE;
l_iface_dis_rec po.po_distributions_interface%ROWTYPE;
--l_org_id NUMBER := 81; --OU ID l_user_id NUMBER := 0; --User ID?Sysadmin
l_batch_id NUMBER;
l_line_num NUMBER;
CURSOR headers IS
SELECT DISTINCT old_po_number,
ou_name,
org_id,
vendor_name,
vendor_id,
vendor_site_code,
vendor_site_id,
agent_name,
agent_id,
currency_code,
terms_name,
terms_id,
rate,
rate_type

FROM cux_po_inport;

CURSOR lines(p_old_po_number VARCHAR2,
p_org_id NUMBER,
p_vendor_id NUMBER,
p_vendor_site_id NUMBER,
p_agent_id NUMBER,
p_currency_code VARCHAR2,
p_terms_id NUMBER) IS
SELECT item_code,
item_id,
quantity,
uom,
unit_price,
promised_date,
need_by_date,
destination_subinventory
FROM cux_po_inport
WHERE old_po_number = p_old_po_number
AND org_id = p_org_id
AND vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id
AND agent_id = p_agent_id
AND currency_code = p_currency_code
AND terms_id = p_terms_id;
BEGIN
SELECT po_headers_interface_s.NEXTVAL INTO l_batch_id FROM dual;

FOR h IN headers LOOP
--l_iface_https://www.wendangku.net/doc/ea6676007.html,_id := l_org_id;
SELECT po_headers_interface_s.NEXTVAL
INTO l_iface_rec.interface_header_id
FROM dual;
l_iface_rec.process_code := 'PENDING';
l_iface_rec.action := 'ORIGINAL';
l_iface_rec.document_type_code := 'STANDARD';
l_iface_rec.document_subtype := NULL;
--l_iface_rec.quote_warning_delay := 10;
l_iface_rec.document_num := NULL;
l_iface_rec.approval_status := 'APPROVED';
l_iface_rec.agent_id := h.agent_id;
l_iface_rec.vendor_id := h.vendor_id;
l_iface_rec.vendor_site_id := h.vendor_site_id;
l_iface_rec.currency_code := h.currency_code;
l_iface_rec.terms_id := h.terms_id;
l_iface_rec.attribute5 := h.old_po_number;
IF h.rate_type IS NULL THEN
l_iface_rec.rate_date := NULL;
ELSE
l_iface_rec.rate_date := TO_DATE('2012-12-06', 'YYYY-MM-DD') /*TRUNC(SYSDATE)*/
;
END IF;

l_iface_rec.RATE_TYPE := h.rate_type;
l_iface_rec.rate := h.rate;

l_iface_rec.interface_source_code := 'CUX_PO_INPORT';
l_iface_rec.batch_id := l_batch_id;

INSERT INTO po.po_headers_interface VALUES l_iface_rec;
l_line_num := 0;
FOR l IN lines(h.old_p

o_number,
https://www.wendangku.net/doc/ea6676007.html,_id,
h.vendor_id,
h.vendor_site_id,
h.agent_id,
h.currency_code,
h.terms_id) LOOP
l_line_num := l_line_num + 1;
l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;
l_iface_lines_rec.process_code := 'PENDING';
l_iface_lines_rec.action := 'ORIGINAL';
l_iface_lines_rec.line_num := l_line_num;
l_iface_lines_rec.item_id := l.item_id;
--l_iface_lines_rec.promised_date := SYSDATE;
--Shipment 1
--l_iface_lines_rec.shipment_num := 1;
l_iface_lines_rec.quantity := l.quantity;
l_iface_lines_rec.unit_price := l.unit_price;
l_iface_lines_rec.promised_date := l.promised_date; --承诺日期
l_iface_lines_rec.need_by_date := l.need_by_date; --需求日期

SELECT po_lines_interface_s.NEXTVAL
INTO l_iface_lines_rec.interface_line_id
FROM dual;
INSERT INTO po_lines_interface VALUES l_iface_lines_rec;
l_iface_dis_rec.interface_header_id := l_iface_lines_rec.interface_header_id;
l_iface_dis_rec.distribution_num := 1;
--l_iface_dis_rec.quantity_delivered := 0;
--l_iface_dis_rec.quantity_billed := 0;
--l_iface_dis_rec.quantity_cancelled := 0; --Must Be 0
--l_iface_dis_rec.charge_account_id := NULL;
l_iface_dis_rec.interface_line_id := l_iface_lines_rec.interface_line_id;
l_iface_dis_rec.quantity_ordered := l_iface_lines_rec.quantity;
l_iface_dis_rec.destination_subinventory := l.destination_subinventory; --子库
SELECT po.po_distributions_interface_s.NEXTVAL
INTO l_iface_dis_rec.interface_distribution_id
FROM dual;
INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;
END LOOP;
END LOOP;
/* --Shipment 2
l_iface_lines_rec.shipment_num := 2;
l_iface_lines_rec.quantity := 300;
--l_iface_lines_rec.unit_price := 1;
SELECT po_lines_interface_s.NEXTVAL
INTO l_iface_lines_rec.interface_line_id
FROM dual;
INSERT INTO po_lines_interface VALUES l_iface_lines_rec;


l_iface_dis_rec.interface_line_id := l_iface_lines_rec.interface_line_id;
l_iface_dis_rec.quantity_ordered := l_iface_lines_rec.quantity;
l_iface_dis_rec.destination_subinventory := 'W009'; --子库
SELECT po.po_distributions_interface_s.NEXTVAL
INTO l_iface_dis_rec.interface_distribution_id
FROM dual;
INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;*/
--Should initialize before po_docs_interface_sv5.process_po_headers_interface
apps.fnd_global.apps_initialize(user_id => 0,
resp_id => 50819,
resp_appl_id => 201);
mo_global.set_

policy_context('S', 84 /*fnd_profile.VALUE('ORG_ID')*/);
po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id => l_iface_rec.batch_id,
x_buyer_id => NULL,
x_document_type => l_iface_rec.document_type_code,
x_document_subtype => l_iface_rec.document_subtype,
x_create_items => 'N',
x_create_sourcing_rules_flag => NULL,
x_rel_gen_method => NULL,
x_approved_status => l_iface_rec.approval_status,
x_commit_interval => 1,
x_process_code => 'PENDING',
x_interface_header_id => NULL,
x_org_id_param => NULL,
x_ga_flag => NULL);
END;