roi: receiving open interface, 是提供给客户的接口, 通过 roi 客户可以不通过ebs form 界面做receiving 的动作, 而是通过脚本插入相关的接口表 ( rhi, rti 等), 再手动调用 concurrent request: rtp 来处理接口表的数据. 下面的脚本总结了几个常用业务的roi 脚本, 以便平时使用.
1. po receipt for lot & serial controlled item -- note: 368811.1脚本适用于: 标准 po, direct deliver routine, lot & serial item
select * from mtl_system_items_b where segment1 = 'yulotserial';select * from po_headers_all where segment1 = '7615';select * from po_lines_all where po_header_id = 883313;select * from po_line_locations_all where po_header_id = 883313;select * from po_distributions_all where po_header_id = 883313;select * from mtl_supply where po_header_id = 883313;
insert into rcv_headers_interface (header_interface_id, group_id, processing_status_code, receipt_source_code, transaction_type, auto_transact_code, last_update_date, last_updated_by, last_update_login, creation_date, created_by, vendor_id, ship_to_organization_id, expected_receipt_date, validation_flag, org_id ) values (rcv_headers_interface_s.nextval , rcv_interface_groups_s.nextval, 'pending', 'vendor', 'new', 'deliver', sysdate, 0, 0, sysdate, 0, 147, --po_headers_all.vendor_id 207, --po_line_locations_all.ship_to_organization_id sysdate, 'y', 204 --po_headers_all.org_id ); insert into rcv_transactions_interface (interface_transaction_id, group_id, last_update_date, last_updated_by, creation_date, created_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, 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, header_interface_id, document_num, to_organization_id, validation_flag, org_id ) select rcv_transactions_interface_s.nextval, rcv_interface_groups_s.currval, sysdate, 0, sysdate, 0, 0, 'receive', sysdate, 'pending', 'batch', 'pending', 50, --quantity 'each', --po_lines_all.unit_of_measure 736956, --po_lines_all.item_id 0, 'deliver', 207, --po_line_locations_all.ship_to_location_id 'vendor', 147, --po_headers_all.vendor_id 'po', 883313, --mtl_supply.po_header_id 954242, --mtl_supply.po_line_id 1051390, --mtl_supply.po_line_location_id 'inventory', null, 207, --location_id 207, --deliver_to_location_id 'fgi', --subinventory rcv_headers_interface_s.currval, 7615, --po number 207, --to_organization_id 'y', --validation_flag 204 --org_id_operating_unit_idfrom dual; insert into mtl_transaction_lots_interface ( transaction_interface_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, lot_number, transaction_quantity, serial_transaction_temp_id, product_code, product_transaction_id ) values ( mtl_material_transactions_s.nextval, sysdate, 0, sysdate, 0, 0, 'l-1000', --lot_number 50, --transaction_quantity mtl_material_transactions_s.nextval, 'rcv', rcv_transactions_interface_s.currval );insert into mtl_serial_numbers_interface ( transaction_interface_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, fm_serial_number, to_serial_number, product_code, product_transaction_id) values (mtl_material_transactions_s.currval, sysdate, 0, sysdate, 0, 0, 's-1000', --fm_serial_number 's-1049', --to_serial_number 'rcv', rcv_transactions_interface_s.currval ); commit;
查找 group_id
select * from rcv_transactions_interface where po_header_id=883313;select * from rcv_headers_interface where header_interface_id=4145843;
2. correction for lot & serial controlled item --note: 335699.13. asn
4. lcm
5. rma