Goal
New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) supports for Inter-Org Transfers:
. RECEIVE transaction
. DELIVER to Inventory Transaction
How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
Solution
Following SAMPLE script is intended
To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done between 2 inventory organizations with Direct
Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)
In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.
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 Note 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 Note 423541.1
1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct
2) Item 'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and 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 both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'
3) Inventory / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1'
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010
4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:
Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_SOURCE_CODE=INVENTORY
RECEIPT_NUM=null
SHIP_TO_ORG_ID=210
Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=0
SHIPMENT_LINE_STATUS_CODE=EXPECTED
SOURCE_DOCUMENT_CODE=INVENTORY
ROUTING_HEADER_ID=3
FROM_ORGANIZATION=207
TO_ORGANIZATION_ID=210
TO_SUBINVENTORY=Staging1
5) Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534
Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
SHIPMENT_LINE_ID=246486
SUPPLY_TYPE_CODE=SHIPMENT
LOT_NUM=S00226
QUANTITY=10
Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
SHIPMENT_LINE_ID=246486
SUPPLY_TYPE_CODE=SHIPMENT
SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
LOT_NUM=S00226
RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example
1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002
Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'
. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'
. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_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,
SHIPMENT_NUM,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --Header_Interface_Id
rcv_interface_groups_s.nextval, --Group_Id
'PENDING', --Processing_Status_Code
'INVENTORY', --Receipt_Source_Code
'NEW', --Transaction_Type
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
0, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
0, --Created_By
'ROI-Lot-Serial-1', --Shipment_Num
210, --Ship_To_Organization_Id,
SYSDATE, --Expected_Receipt_Date
'Y' --Validation_Flag
);
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,
INTERFACE_SOURCE_CODE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
DESTINATION_TYPE_CODE,
SUBINVENTORY,
SHIPMENT_NUM,
EXPECTED_RECEIPT_DATE,
HEADER_INTERFACE_ID,
VALIDATION_FLAG
)
VALUES
( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.currval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Each', --UNIT_OF_MEASURE
'RCV', --INTERFACE_SOURCE_CODE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
233534, --SHIPMENT_HEADER_ID
246486, --SHIPMENT_LINE_ID
'INVENTORY', --RECEIPT_SOURCE_CODE
210, --TO_ORGANIZATION_ID
'INVENTORY', --SOURCE_DOCUMENT_CODE
'INVENTORY', --DESTINATION_TYPE_CODE
'Staging1', --SUBINVENTORY
'ROI-Lot-Serial-1', --SHIPMENT_NUM
SYSDATE, --EXPECTED_RECEIPT_DATE,
rcv_headers_interface_s.currval, --HEADER_INTERFACE_ID
'Y' --VALIDATION_FLAG
);
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,--TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S00226', --LOT_NUMBER
2, --TRANSACTION_QUANTITY
2, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
);
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,--TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SM1_00001', --FM_SERIAL_NUMBER
'SM1_00002', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
);
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_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839
SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839
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=25168)
4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 has Receive and Deliver transactions.
5) Check how the following application tables have been populated/updated
SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012
SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED
SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records
For TRANSACTION_TYPE=RECEIVE
TRANSACTION_ID=307969
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=-1
QUANTITY=2
For TRANSACTION_TYPE=DELIVER
TRANSACTION_ID=307970
DESTINATION_TYPE_CODE=INVENTORY
PARENT_TRANSACTION_ID=307969
QUANTITY=2
New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) supports for Inter-Org Transfers:
. RECEIVE transaction
. DELIVER to Inventory Transaction
How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
Solution
Following SAMPLE script is intended
To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done between 2 inventory organizations with Direct
Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)
In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.
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 Note 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 Note 423541.1
1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct
2) Item 'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and 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 both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'
3) Inventory / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1'
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010
4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:
Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_SOURCE_CODE=INVENTORY
RECEIPT_NUM=null
SHIP_TO_ORG_ID=210
Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=0
SHIPMENT_LINE_STATUS_CODE=EXPECTED
SOURCE_DOCUMENT_CODE=INVENTORY
ROUTING_HEADER_ID=3
FROM_ORGANIZATION=207
TO_ORGANIZATION_ID=210
TO_SUBINVENTORY=Staging1
5) Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534
Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
SHIPMENT_LINE_ID=246486
SUPPLY_TYPE_CODE=SHIPMENT
LOT_NUM=S00226
QUANTITY=10
Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
SHIPMENT_LINE_ID=246486
SUPPLY_TYPE_CODE=SHIPMENT
SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
LOT_NUM=S00226
RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example
1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002
Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'
. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'
. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_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,
SHIPMENT_NUM,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --Header_Interface_Id
rcv_interface_groups_s.nextval, --Group_Id
'PENDING', --Processing_Status_Code
'INVENTORY', --Receipt_Source_Code
'NEW', --Transaction_Type
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
0, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
0, --Created_By
'ROI-Lot-Serial-1', --Shipment_Num
210, --Ship_To_Organization_Id,
SYSDATE, --Expected_Receipt_Date
'Y' --Validation_Flag
);
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,
INTERFACE_SOURCE_CODE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
DESTINATION_TYPE_CODE,
SUBINVENTORY,
SHIPMENT_NUM,
EXPECTED_RECEIPT_DATE,
HEADER_INTERFACE_ID,
VALIDATION_FLAG
)
VALUES
( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.currval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Each', --UNIT_OF_MEASURE
'RCV', --INTERFACE_SOURCE_CODE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
233534, --SHIPMENT_HEADER_ID
246486, --SHIPMENT_LINE_ID
'INVENTORY', --RECEIPT_SOURCE_CODE
210, --TO_ORGANIZATION_ID
'INVENTORY', --SOURCE_DOCUMENT_CODE
'INVENTORY', --DESTINATION_TYPE_CODE
'Staging1', --SUBINVENTORY
'ROI-Lot-Serial-1', --SHIPMENT_NUM
SYSDATE, --EXPECTED_RECEIPT_DATE,
rcv_headers_interface_s.currval, --HEADER_INTERFACE_ID
'Y' --VALIDATION_FLAG
);
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,--TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S00226', --LOT_NUMBER
2, --TRANSACTION_QUANTITY
2, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
);
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,--TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SM1_00001', --FM_SERIAL_NUMBER
'SM1_00002', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
);
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_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839
SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839
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=25168)
4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 has Receive and Deliver transactions.
5) Check how the following application tables have been populated/updated
SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012
SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED
SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records
For TRANSACTION_TYPE=RECEIVE
TRANSACTION_ID=307969
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=-1
QUANTITY=2
For TRANSACTION_TYPE=DELIVER
TRANSACTION_ID=307970
DESTINATION_TYPE_CODE=INVENTORY
PARENT_TRANSACTION_ID=307969
QUANTITY=2
No comments:
Post a Comment