-- l_iface_rec po_headers_interface%ROWTYPE;
校验头相关信息
SELECT COUNT(1)
INTO l_po_count
FROM po_headers_all ph
WHERE 1 = 1
AND ph.segment1 = rec.po_number;
IF (l_po_count > 0) THEN
l_message := l_message || ' Po_number: ' || rec.po_number || ' Is Error';
log('Po_number: ' || rec.po_number || ' Is Error');
END IF; BEGIN
SELECT hou.organization_id
INTO l_org_id
FROM hr_operating_units hou
WHERE 1 = 1
AND hou.name = rec.ou;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' OU: ' || rec.ou || ' Is Error';
log('OU: ' || rec.ou || ' Is Error');
END; BEGIN
SELECT podb.document_subtype
INTO l_type_code
FROM po_document_types_all_b podb,
po_document_types_all_tl podt
WHERE 1 = 1
AND podb.document_type_code = podt.document_type_code
AND podb.document_subtype = podt.document_subtype
AND podb.org_id = podt.org_id
AND podt.language = 'US'
AND podb.org_id = l_org_id
AND podb.document_type_code IN ('PO', 'PA')
AND podt.type_name = rec.type;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Type: ' || rec.type || ' Is Error';
log('Type: ' || rec.type || ' Is Error');
END; BEGIN
SELECT pv.vendor_id
INTO l_vendor_id
FROM po_vendors pv
WHERE 1 = 1
AND pv.vendor_name = rec.suppiler;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Suppiler: ' || rec.suppiler || ' Is Error';
log('Suppiler: ' || rec.suppiler || ' Is Error');
END; BEGIN
SELECT vs.vendor_site_id
INTO l_vendor_site_id
FROM po_vendor_sites_all vs
WHERE 1 = 1
AND vs.vendor_id = l_vendor_id
AND vs.vendor_site_code = rec.site;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Site: ' || rec.site || ' Is Error';
log('Site: ' || rec.site || ' Is Error');
END; BEGIN
SELECT hl.location_id
INTO l_ship_to_location_id
FROM hr_locations_all_tl hl
WHERE 1 = 1
AND hl.location_code = rec.ship_to
AND hl.language = userenv('LANG');
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Ship_to: ' || rec.ship_to || ' Is Error';
log('Ship_to: ' || rec.ship_to || ' Is Error');
END; BEGIN
SELECT hl.location_id
INTO l_bill_to_location_id
FROM hr_locations_all_tl hl
WHERE 1 = 1
AND hl.location_code = rec.bill_to
AND hl.language = userenv('LANG');
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Bill_to: ' || rec.bill_to || ' Is Error';
log('Bill_to: ' || rec.bill_to || ' Is Error');
END; BEGIN
SELECT p.person_id
INTO l_agent_id
FROM per_people_f p
WHERE 1 = 1
AND p.full_name = rec.buyer;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Buyer: ' || rec.buyer || ' Is Error';
log('Buyer: ' || rec.buyer || ' Is Error');
END; IF (l_message IS NOT NULL) THEN
dbms_output.put_line('Error l_message: ' || l_message);
RAISE fnd_api.g_exc_error;
END IF;
--插入头接口表
l_iface_rec.interface_header_id := po_headers_interface_s.nextval;
l_iface_rec.interface_source_code := 'XXPO_ONHAND_HEADER';
l_iface_rec.batch_id := g_session_id;
l_iface_rec.process_code := 'PENDING'; --PENDING, ACCEPTED, REJECTED, or NOTIFIED
l_iface_rec.action := 'ORIGINAL'; --ORIGINAL,REPLACE, or UPDATE
l_iface_rec.org_id := l_org_id;
l_iface_rec.document_num := rec.po_number;
l_iface_rec.document_type_code := l_type_code; --STANDARD, BLANKET, or QUOTATION
l_iface_rec.vendor_id := l_vendor_id;
l_iface_rec.vendor_site_id := l_vendor_site_id;
l_iface_rec.ship_to_location_id := l_ship_to_location_id;
l_iface_rec.bill_to_location_id := l_bill_to_location_id;
l_iface_rec.agent_id := l_agent_id;
l_iface_rec.approval_status := rec.status; --'INCOMPLETE'
l_iface_rec.currency_code := rec.currency;
--l_iface_rec.rate_date := to_date('22-MAR-2017', 'DD-MON-YYYY');
--l_iface_rec.rate_type := 'Corporate';
l_iface_rec.last_update_date := g_last_update_date;
l_iface_rec.last_updated_by := g_last_updated_by;
l_iface_rec.creation_date := rec.created_date;
l_iface_rec.created_by := g_created_by;
l_iface_rec.last_update_login := g_last_update_login;
l_iface_rec.program_application_id := g_program_appl_id;
l_iface_rec.program_id := g_program_id;
l_iface_rec.program_update_date := g_program_upd_date;
INSERT INTO po_headers_interface
VALUES l_iface_rec;
--l_iface_lines_rec po_lines_interface%ROWTYPE;
---校验行相关信息
BEGIN
SELECT msi.inventory_item_id,
msi.description
INTO l_item_id,
l_item_description
FROM mtl_system_items_b msi,
financials_system_params_all fsp
WHERE 1 = 1
AND nvl(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
AND fsp.org_id = l_org_id
AND msi.segment1 = rec_po_line.item_number;
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Item_number: ' || rec_po_line.item_number || ' Is Error';
log('Item_number: ' || rec_po_line.item_number || ' Is Error');
END; BEGIN
SELECT t.category_id
INTO l_category_id
FROM mtl_categories_kfv t
WHERE t.concatenated_segments = 'DEFAULT|DEFAULT';
EXCEPTION
WHEN OTHERS THEN
l_message := l_message || ' Category Is Error';
log('Category Is Error');
END; IF (l_message IS NOT NULL) THEN
dbms_output.put_line('Error l_message: ' || l_message);
RAISE fnd_api.g_exc_error;
END IF;
--插入行接口表
l_iface_lines_rec := NULL;
l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;
l_iface_lines_rec.interface_line_id := po.po_lines_interface_s.nextval;
l_iface_lines_rec.process_code := 'PENDING';
l_iface_lines_rec.action := 'ORIGINAL';
l_iface_lines_rec.line_num := rec_po_line.line_num;
l_iface_lines_rec.shipment_num := 1;
l_iface_lines_rec.line_type_id := 1;
l_iface_lines_rec.line_type := rec_po_line.type;
l_iface_lines_rec.item_id := l_item_id;
l_iface_lines_rec.category_id := l_category_id;
l_iface_lines_rec.item_description := l_item_description;
l_iface_lines_rec.uom_code := rec_po_line.uom;
l_iface_lines_rec.quantity := rec_po_line.qty;
l_iface_lines_rec.unit_price := rec_po_line.price;
l_iface_lines_rec.need_by_date := rec_po_line.need_by;
l_iface_lines_rec.last_update_date := g_last_update_date;
l_iface_lines_rec.last_updated_by := g_last_updated_by;
l_iface_lines_rec.creation_date := g_creation_date;
l_iface_lines_rec.created_by := g_created_by;
l_iface_lines_rec.last_update_login := g_last_update_login;
l_iface_lines_rec.program_application_id := g_program_appl_id;
l_iface_lines_rec.program_id := g_program_id;
l_iface_lines_rec.program_update_date := g_program_upd_date;
INSERT INTO po_lines_interface
VALUES l_iface_lines_rec;
--l_iface_dis_rec po_distributions_interface%ROWTYPE;
l_iface_dis_rec.interface_header_id := po_headers_interface_s.currval; --;
l_iface_dis_rec.interface_line_id := po_lines_interface_s.currval;
l_iface_dis_rec.interface_distribution_id := po.po_distributions_interface_s.nextval; -- NULL;
l_iface_dis_rec.distribution_num := 1;
l_iface_dis_rec.org_id := l_org_id;
l_iface_dis_rec.quantity_ordered := rec_po_line.qty;
l_iface_dis_rec.destination_type_code := 'INVENTORY'; --类型（费用/库存）
l_iface_dis_rec.rate_date := SYSDATE;
l_iface_dis_rec.last_update_date := g_last_update_date;
l_iface_dis_rec.last_updated_by := g_last_updated_by;
l_iface_dis_rec.creation_date := g_creation_date;
l_iface_dis_rec.created_by := g_created_by;
l_iface_dis_rec.last_update_login := g_last_update_login;
l_iface_dis_rec.program_application_id := g_program_appl_id;
l_iface_dis_rec.program_id := g_program_id;
l_iface_dis_rec.program_update_date := g_program_upd_date;
INSERT INTO po_distributions_interface
VALUES l_iface_dis_rec;
--导入PO
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 => NULL,
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)
--查看错误信息
ln_err_count := 0;
FOR c IN (SELECT t.document_num,
l.line_num,
d.distribution_num,
a.error_message,
l.interface_line_id,
d.interface_distribution_id,
a.interface_header_id
FROM po_interface_errors a,
po_headers_interface t,
po_lines_interface l,
po_distributions_interface d
WHERE a.interface_header_id = t.interface_header_id
AND l.interface_line_id(+) = a.interface_line_id
AND d.interface_distribution_id(+) = a.interface_distribution_id
AND t.batch_id = l_iface_rec.batch_id)
LOOP
ln_err_count := ln_err_count + 1;
log('Error: ' || c.error_message);
dbms_output.put_line('Error: ' || c.error_message); DELETE po_headers_interface ph
WHERE ph.interface_header_id = l_iface_rec.interface_header_id; DELETE po_lines_interface pl
WHERE pl.interface_header_id = l_iface_rec.interface_header_id; DELETE po_distributions_interface pd
WHERE pd.interface_header_id = l_iface_rec.interface_header_id; END LOOP;
IF (ln_err_count > 0) THEN
RAISE fnd_api.g_exc_error;
END IF;