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.1
3. ASN
4. LCM
5. RMA