照Oracle的sample有error, 或許有些假設與前提不同.
PO interface error:
1.table: PO_HEADERS_INTERFACE
Error: PO_HEADER_ID There should be at least one line per document.
修正: 補vendor_site_id後OK
2.table: RCV_TRANSACTIONS_INTERFACE
Error: RCV_DELIVER_TO_LOC_INVALID
修正: 清空deliver_to_org_id
DECLARE
CURSOR c1 (
p_po_number VARCHAR2)IS
SELECT pha.vendor_id,
pha.vendor_site_id,
pha.po_header_id,
pla.po_line_id,
pla.item_id,
pll.line_location_id,
pll.quantity,
pll.quantity_received,
pll.ship_to_location_id,
pll.ship_to_organization_id,
msi.lot_control_code, -- '2'代表有control
msi.primary_unit_of_measure
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll,
mtl_system_items_b msi
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pla.item_id = msi.inventory_item_id
AND pll.ship_to_organization_id = msi.organization_id
AND pha.segment1 = p_po_number --'2200000242'
AND ROWNUM = 1;
CURSOR c2 (
p_po_number VARCHAR2)
IS
SELECT pha.vendor_id,
pha.vendor_site_id,
pha.po_header_id,
pla.po_line_id,
pla.item_id,
pll.line_location_id,
pll.quantity,
pll.quantity_received,
pll.ship_to_location_id,
pll.ship_to_organization_id,
msi.lot_control_code, -- '2'代表有control
msi.primary_unit_of_measure
FROM po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll,
mtl_system_items_b msi
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pla.item_id = msi.inventory_item_id
AND pll.ship_to_organization_id = msi.organization_id
AND pha.segment1 = p_po_number --'2200000242'
;
v_txn_header_id NUMBER;
v_group_id NUMBER;
v_txn_interface_id NUMBER;
v_po_number VARCHAR2 (20);
v_subinventory VARCHAR2 (10);
v_locator VARCHAR2 (20);
v_locator_id NUMBER;
v_lot_interface_id number;
v_txn_date date := to_date('20190707','yyyymmdd');
BEGIN
v_po_number := '2200000240';
v_subinventory := 'NFG';
v_locator := 'NFG.PG25';
select rcv_headers_interface_s.NEXTVAL
into v_txn_header_id
from dual;
select rcv_interface_groups_s.NEXTVAL
into v_group_id
from dual;
SELECT inventory_location_id
INTO v_locator_id
FROM mtl_item_locations
WHERE organization_id = 498
AND segment1 || '.' || segment2 = v_locator;
FOR r1 IN c1 (v_po_number)
LOOP
INSERT INTO rcv_headers_interface (header_interface_id,
GROUP_ID,
processing_status_code,
receipt_source_code,
transaction_type,
auto_transact_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
vendor_id,
vendor_site_id,
ship_to_organization_id,
expected_receipt_date,
validation_flag)
VALUES (v_txn_header_id, --rcv_headers_interface_s.nextval,
v_group_id, --rcv_interface_groups_s.nextval,
'PENDING',
'VENDOR',
'NEW',
'DELIVER',
SYSDATE,
1091,
SYSDATE,
1091,
0,
r1.vendor_id,
r1.vendor_site_id,
r1.ship_to_organization_id,
SYSDATE,
'Y');
END LOOP;
FOR r2 IN c2 (v_po_number)
LOOP
select rcv_transactions_interface_s.NEXTVAL
into v_txn_interface_id
from dual;
INSERT INTO rcv_transactions_interface (interface_transaction_id,
GROUP_ID,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
auto_transact_code,
ship_to_location_id,
receipt_source_code,
vendor_id,
vendor_site_id,
source_document_code,
po_header_id,
po_line_id,
po_line_location_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
subinventory,
locator_id,
header_interface_id,
document_num,
to_organization_id,
validation_flag)
VALUES (v_txn_interface_id, --rcv_trsactions_interface_s.nextval,
v_group_id,
SYSDATE,
1091,
SYSDATE,
1091,
0,
'RECEIVE',
v_txn_date,
'PENDING',
'BATCH',
'PENDING',
r2.quantity, --qty,
r2.primary_unit_of_measure,
--?uom,
r2.item_id,
81, --?employee_id,
'DELIVER',
r2.ship_to_location_id, --?ship_to_location_id
'VENDOR',
r2.vendor_id,
r2.vendor_site_id,
'PO',
r2.po_header_id,
r2.po_line_id,
r2.line_location_id,
'INVENTORY',
NULL, --?deliver_to_person_id,
498, --location_id,
null, --498, --deliver_to_location_id
v_subinventory,
v_locator_id, --Adingo新增
v_txn_header_id, --rcv_headers_interface_s.CURRVAL,
v_po_number,
498, --to_organization_id
'Y');
if r2.lot_control_code = '2' then
select mtl_material_transactions_s.NEXTVAL
into v_lot_interface_id
from dual;
INSERT INTO mtl_transaction_lots_interface (transaction_interface_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
lot_number,
transaction_quantity,
primary_quantity,
product_code,
product_transaction_id)
VALUES (v_lot_interface_id,
SYSDATE,
1091,
SYSDATE,
1091,
0,
'2018', --lot_number
r2.quantity,
--transaction_quantity
r2.quantity, --primary_quantity
'RCV',
v_txn_interface_id --rcv_transactions_interface_s.CURRVAL
);
end if;
END LOOP;
commit;
END;
Ref:
1.ROI - How To Perform Purchase Order Receipt For Lot and Serial Controlled Item ? (Doc ID 368811.1)
沒有留言:
張貼留言
注意：只有此網誌的成員可以留言。