摘自：http://blog.youkuaiyun.com/cai_xingyun/article/details/17533505
除了可以用
详解EBS接口开发之库存事务处理采购接收的方法还可以用一下方法，不同之处在于带有批次和序列控制的时候实现方式不同
The script will load records into the tables
RCV_HEADERS_INTERFACE,
RCV_TRANSACTIONS_INTERFACE,
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
If the item is only a standard item, only the records into RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables have to be created.
SETUP
0) Ensure to apply the patches listed in Document 367396.1 Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J) and For Release 12 see Document 423541.1
1) Purchasing / Purchase Orders / Purchase Orders
Standard Purchase Order number 10084 is entered
Vendor F-Supplier
One line / shipment line/ distribution line for
Item=F-Lot-Serial-Controlled-4
Quantity=10
Destination Org=M2
Ship To=M2-Boston
Receipt Routing=Direct Delivery
2) Item 'F-Lot-Serial-Controlled-4' is a lot and serial controlled Item in Destination Organization
- SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code,
- msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),
- '2','Y',
- '1','N') LOT_CONTROL,
- decode(to_char(msi.serial_number_control_code),
- '1','None',
- '2','Predefined',
- '5','Dynamic at INV receipt',
- '6','Dynamic at SO issue') SERIAL_CONTROL
- from mtl_system_items_b msi,mtl_parameters mp
- where msi.segment1 like '&item' and msi.organization_id=mp.organization_id;
For item=F-Lot-Serial-Controlled-4 , inventory_item_id=378856 and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'
3) In Purchasing Responsibility
Receiving/Receipts
In org M2 Boston
Find expected receipts
Source Type =Supplier
Purchase Order = 10084
-> Ensure 1 line is available to be receipted
Order type = Standard
Order = 10084
Routing = Direct Delivery
4) Run the following scripts so to find the necessary information to insert into the RCV_TRANSACTIONS_INTERFACE table:
- Select * from PO_HEADERS_ALL where SEGMENT1 = '&po_number'
- PO_HEADER_ID=44274
- TYPE_LOOKUP_CODE=STANDARD
- SEGMENT1=10084
- VENDOR_ID=7927
- VENDOR_SITE_ID=4724
- APPROVED_FLAG=Y
- ORG_ID=204
-
- Select * from PO_LINES_ALL where PO_HEADER_ID=&Po_header_id;
- PO_LINE_ID=50364
- PO_HEADER_ID=44274
- LINE_NUM=1
- ITEM_ID=378856
- CATEGORY_ID=1
- ITEM_DESCRIPTION=F-Lot Serial Controlled Item for ROI/Internal SO
- UNIT_MEAS_LOOKUP_CODE=Each
- QUANTITY=10
- ORG_ID=204
-
- Select * from PO_LINE_LOCATIONS_ALL where PO_HEADER_ID=&Po_header_id;
- LINE_LOCATION_ID=85112
- PO_HEADER_ID=44274
- PO_LINE_ID=50364
- QUANTITY=10,00
- QUANTITY_RECEIVED=0,00
- SHIP_TO_LOCATION_ID=209
- NEED_BY_DATE=01/05/2006
- PROMISED_DATE=01/05/2006
- SHIP_TO_ORGANIZATION_ID=209
- SHIPMENT_NUM=1
- RECEIVING_ROUTING_ID=3
- ORG_ID=204
-
- Select * from PO_DISTRIBUTIONS_ALL where PO_HEADER_ID=&Po_header_id;
- PO_DISTRIBUTION_ID=86928
- PO_LINE_LOCATION_ID=85112
- PO_HEADER_ID=44274
- PO_LINE_ID=50364
- QUANTITY_DELIVERED=0,00
- DELIVER_TO_LOCATION_ID=209
- DELIVER_TO_PERSON_ID=13706
- DESTINATION_TYPE_CODE=INVENTORY
- DESTINATION_ORGANIZATION_ID=209
- DESTINATION_SUBINVENTORY=Stores
- DISTRIBUTION_NUM=1
- ORG_ID=204
-
- Select * from MTL_SUPPLY where PO_HEADER_ID=&Po_header_id;
- SUPPLY_TYPE_CODE=PO
- SUPPLY_SOURCE_ID=86928
- PO_HEADER_ID=44274
- PO_LINE_ID=50364
- PO_LINE_LOCATION_ID=85112
- PO_DISTRIBUTION_ID=86928
- ITEM_ID=378856
- QUANTITY=10
- UNIT_OF_MEASURE=Each
- DESTINATION_TYPE_CODE=INVENTORY
- TO_ORGANIZATION_ID=209
RECEIVE/ DELIVER to INVENTORY Transaction for STANDARD PURCHASE ORDER Example
1) Insert via ROI a Direct DELIVER Receipt for Standard Purchase Order Number 10084 (PO_HEADER_ID=44274) on vendor F-Supplier of 2 items in destination organization M2 with LOT_NUM=B00406 and serial numbers SN_00001 to SN_00002
Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information with RECEIPT_SOURCE_CODE='VENDOR' and VALIDATION_FLAG='Y'
. 1 record in RCV_TRANSACTIONS_INTERFACE table for PO_HEADER_ID=44274
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='VENDOR' and SOURCE_DOCUMENT_CODE='PO'
VALIDATION_FLAG='Y'
. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2, primary_quantity =2
on lot number B00406
(After Bug 9399287, there is not need to populate MTL_TRANSACTION_LOTS_INTERFACE.primary_quantity anymore)
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating
FM_SERIAL_NUMBER='SN_00001'and TO_SERIAL_NUMBER='SN_00002'
- 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
- )
- VALUES
- (rcv_headers_interface_s.nextval ,
- rcv_interface_groups_s.nextval,
- 'PENDING',
- 'VENDOR',
- 'NEW',
- 'DELIVER',
- SYSDATE,
- 0,
- 0,
- SYSDATE,
- 0,
- 7927,
- 209,
- SYSDATE,
- 'Y'
- );
-
- 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
- )
- SELECT
- rcv_transactions_interface_s.nextval,
- rcv_interface_groups_s.currval,
- SYSDATE,
- 0,
- SYSDATE,
- 0,
- 0,
- 'RECEIVE',
- SYSDATE,
- 'PENDING',
- 'BATCH',
- 'PENDING',
- 2,
- 'Each',
- 378856,
- 13706,
- 'DELIVER',
- 209,
- 'VENDOR',
- 7927,
- 'PO',
- 44274,
- 50364,
- 85112,
- 'INVENTORY',
- 13706,
- 209,
- 209,
- 'Stores',
- rcv_headers_interface_s.currval,
- 10084,
- 209,
- 'Y'
- FROM 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,
- PRIMARY_QUANTITY,
- SERIAL_TRANSACTION_TEMP_ID,
- PRODUCT_CODE,
- PRODUCT_TRANSACTION_ID
- )
- VALUES
- ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
- SYSDATE,
- 0,
- SYSDATE,
- 0,
- 0,
- 'B00406',
- 2,
- 2,
- 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,
- 'SN_00001',
- 'SN_00002',
- 'RCV',
- RCV_TRANSACTIONS_INTERFACE_S.CURRVAL
- );
- Commit;
Note: On R12 instance, RHI.org_id or RTI.org_id should also be populated.
2) Run the following scripts to check data have been correctly inserted
- SQL> Select * from RCV_TRANSACTIONS_INTERFACE where PO_HEADER_ID=&Po_header_id;
- INTERFACE_TRANSACTION_ID=576924
- GROUP_ID=32140
- HEADER_INTERFACE_ID=180194
- TRANSACTION_TYPE=RECEIVE
- AUTO_TRANSACT_CODE=DELIVER
- RECEIPT_SOURCE_CODE=VENDOR
- TO_ORGANIZATION_ID=209
- SOURCE_DOCUMENT_CODE=PO
- PO_HEADER_ID=44274
- DESTINATION_TYPE_CODE='INVENTORY'
- VALIDATION_FLAG=Y
-
- SQL> Select * from RCV_HEADERS_INTERFACE where HEADER_INTERFACE_ID=&Header_Interface_ID
- GROUP_ID=32140
- HEADER_INTERFACE_ID=180194
- VENDOR_ID=7927
- VALIDATION_FLAG=Y
-
- SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
- where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
- TRANSACTION_INTERFACE_ID=11599958
- LOT_NUMBER=B00406
- SERIAL_TRANSACTION_TEMP_ID=11599958
- PRODUCT_CODE=RCV
- PRODUCT_TRANSACTION_ID=576924
-
- SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
- where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
- TRANSACTION_INTERFACE_ID=11599958
- FM_SERIAL_NUMBER=SN_00001
- TO_SERIAL_NUMBER=SN_00002
- PRODUCT_TRANSACTION_ID=576924
3) In Purchasing Responsibility, Change to receiving organization and run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=32140)
4) Navigate to Receiving / Receiving Transactions Summary form For PO Number 10084, Receipt Number 5034 has Receive and Deliver transactions.
5) Check how the following application tables have been populated/updated
- SQL> Select * from RCV_HEADERS_INTERFACE where HEADER_INTERFACE_ID=&Header_Interface_ID
- GROUP_ID=32140
- HEADER_INTERFACE_ID=180194
- PROCESSING_STATUS_CODE=SUCCESS
- RECEIPT_HEADER_ID=594567
- VENDOR_ID=7927
- VALIDATION_FLAG=Y
- PROCESSING_REQUEST_ID=3053037
-
- SQL> Select * from RCV_TRANSACTIONS where PO_HEADER_ID=&Po_header_id;
- It returns 2 records
For TRANSACTION_TYPE=RECEIVE
TRANSACTION_ID=638052
REQUEST_ID=3053037
SHIPMENT_HEADER_ID=594567
SHIPMENT_LINE_ID=600529
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=-1
QUANTITY=2
For TRANSACTION_TYPE=DELIVER
TRANSACTION_ID=638053
REQUEST_ID=3053037
SHIPMENT_HEADER_ID=594567
SHIPMENT_LINE_ID=600529
DESTINATION_TYPE_CODE=INVENTORY
PARENT_TRANSACTION_ID=638052
QUANTITY=2
- SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_HEADER_ID=&Shipment_header_id;
- 1 record has been created
- SHIPMENT_HEADER_ID=594567
- RECEIPT_SOURCE_CODE=VENDOR
- RECEIPT_NUM=5034
-
- SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_header_id;
- SHIPMENT_LINE_ID=600529
- SHIPMENT_HEADER_ID=594567
- QUANTITY_SHIPPED=2
- QUANTITY_RECEIVED=2
- SHIPMENT_LINE_STATUS_CODE=FULLY RECEIVED
-
- SQL> Select * from MTL_SERIAL_NUMBERS where INVENTORY_ITEM_ID=&Item_id;
- 2 records have been inserted with
- INVENTORY_ITEM_ID=378856
- SERIAL_NUMBER = SN_00001 / SN_00002
- LOT_NUMBER=B00406
- CURRENT_STATUS=3
- CURRENT_ORGANIZATION_ID=209
-
- SQL> Select * from MTL_LOT_NUMBERS where INVENTORY_ITEM_ID=&Item_id;
- INVENTORY_ITEM_ID=378856
- ORGANIZATION_ID=209
- LOT_NUMBER=B00406
-
- SQL> Select * from MTL_SUPPLY where PO_HEADER_ID=&Po_header_id;
- SUPPLY_TYPE_CODE=PO
- SUPPLY_SOURCE_ID=86928
- PO_HEADER_ID=44274
- PO_LINE_ID=50364
- PO_LINE_LOCATION_ID=85112
- PO_DISTRIBUTION_ID=86928
- ITEM_ID=378856
- QUANTITY=8
- UNIT_OF_MEASURE=Each
- DESTINATION_TYPE_CODE=INVENTORY
- TO_ORGANIZATION_ID=209
扫一扫
730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
