How to perform Lot Translate Transactions using Transaction Open Interface (MTI) (Doc ID 838305.1)

In this Document

Applies to:

Oracle Inventory Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
This document includes all versions of 11i and R12 and later

Goal

How to perform Lot Translate Transactions using Transaction Open Interface (MTL_TRANSACTIONS_INTERFACE)?

Solution

Lot Translate Transaction will translate a Lot Number to an another Lot number. This can be performed using Transaction Open Interface Feature (MTL_TRANSACTIONS_INTERFACE).

Lot Translate Transactions
Lot A ----------------------------------------------------> Lot B
(Starting Lot Number) (Resultant Lot Number)

To Perform the Lot Translate Transactions, Two Set of records (Start record and Resultant record) has to be inserted in to the interface tables.
For Lot Translate Transactions, There will be only one Resultant Lot Number.
Lot translate can translate a single lot to another lot not multiple lots to a lot. If there is a requirement to perform lot translate for multiple lots they will have to perform the translation multiple times.

Specific Instructions for Lot Translate Tranactions to populate the below Mandatory Columns

PARENT_ID

This should be populated for both records (Start and Resultant records)
This must be equal to TRANSACTION_INTERFACE_ID of the start (parent) transaction (the one with negative quantity). This is vital for lot transactions.

TRANSACTION_BATCH_ID

Batch identifier. Should be the same for all the records.
It Should be a numeric value.
For convenience, this can be same as transaction_header_id.

TRANSACTION_BATCH_SEQ

Sequence number of the current record.
The Start Record (the one with negative quantity) should have the lowest value
The Resultant records should be higher than the Start Record.

TRANSACTION_QUANTITY

The transaction_quantity populated for the start and resultant MTI records should be equal
The transaction_quantity should be equal to the entire available to transact quantity of that lot in the org/item/sub/locator/LPN.

Example

Let us say there is a Lot "AA0000" with 10 Ea.
If we want to translate this Lot in to a new Lot say "AA0001"

Then MTL_TRANSACTIONS_INTERFACE (MTI) has to be populated as below

INTERFACE_ID	HEADER_ID	TXN_QUANTITY	TXN_BATCH_ID	TXN_BATCH_SEQ	PARENT_ID
100	100	-10	100	1	100
101	100	10	100	2	100

MTL_TRANSACTION_LOTS_INTERFACE (MTLI) would be:

INTERFACE_ID	LOT_NUMBER	TXN_QUANTITY
100	AA0000	-10
101	AA0001	10

Note: This note is applicable only to WMS Organizations in 11i and both WMS and non-WMS organizations for R12.

/* Sample Insert Script For Lot Translation */

DECLARE
l_transaction_type_id NUMBER := 84;
l_transaction_action_id NUMBER := 42;
l_transaction_source_type_id NUMBER := 13;
l_org_id NUMBER := 1884;
l_txn_header_id NUMBER;
l_txn_if_id1 NUMBER;
l_txn_if_id2 NUMBER;
l_txn_if_id3 NUMBER;
l_parent_id NUMBER;
l_sysdate DATE;
l_item_id NUMBER :=727226;
l_user_id NUMBER;
l_distribution_account_id NUMBER;
l_exp_date DATE;

BEGIN

--For Lot Translate, there should be only one resultant lot.
--The transaction_quantity populated in MTI/MTLI should be the entire
--quantity that is available to transact for the org/sub/item/locator/LPN in
--that particular lot number.

--Get transaction_header_id for all the MTIs

SELECT APPS.mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM sys.dual;

--Get transaction_interface_id of the start record

SELECT APPS.mtl_material_transactions_s.NEXTVAL
INTO l_txn_if_id1
FROM sys.dual;

l_parent_id := l_txn_if_id1;
l_sysdate := SYSDATE;
l_user_id := -1;                    --substitute with a valid user_id
l_distribution_account_id := NULL;  --needed for lot translate
l_exp_date := NULL;                 --set if required

--Populate the MTI record for start (parent) record

INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
transaction_interface_id,
transaction_header_id,
Source_Code,
Source_Line_Id,
Source_Header_Id,
Process_flag,
Transaction_Mode,
Lock_Flag,
Inventory_Item_Id,
revision,
Organization_id,
Subinventory_Code,
Locator_Id,
Transaction_Type_Id,
Transaction_Source_Type_Id,
Transaction_Action_Id,
Transaction_Quantity,
Transaction_UOM,
Primary_Quantity,
Transaction_Date,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
distribution_account_id,
parent_id,
transaction_batch_id,
transaction_batch_seq,
lpn_id,
transfer_lpn_id
)
VALUES
(
l_txn_if_id1,                 --transaction_header_id
l_txn_header_id,              --transaction_interface_id
'INV',                        --source_code
-1,                           --source_header_id
-1,                           --source_line_id
1,                            --process_flag
3,                            --transaction_mode
2,                            --lock_flag
l_item_id,                    --inventory_item_id
null,                         --revision
l_org_id,                     --organization_id
'BULK',                       --subinventory_code
1181,                         --locator_id
l_transaction_type_id,        --transaction_type_id
l_transaction_source_type_id, --transaction_source_type_id
l_transaction_action_Id,      --l_transaction_action_id
-10,                          --transaction_quantity
'EA',                         --transaction_uom
-10,                          --primary_quantity
l_sysdate,                    --Transaction_Date
l_sysdate,                    --Last_Update_Date
l_user_id,                    --Last_Updated_by
l_sysdate,                    --Creation_Date
l_user_id,                    --Created_by
l_distribution_account_id,    --distribution_account_id
l_parent_id,                  --parent_id
l_txn_header_id,              --transaction_batch_id
1,                            --transaction_batch_seq (1 for parent the lowest)
NULL,                         --lpn_id (for source MTI)
NULL                          --transfer_lpn_id (for resultant MTIs)
);

--Insert MTLI corresponding to the start MTI record

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE(
transaction_interface_id
, Source_Code
, Source_Line_Id
, Process_Flag
, Last_Update_Date
, Last_Updated_By
, Creation_Date
, Created_By
, Lot_Number
, lot_expiration_date
, Transaction_Quantity
, Primary_Quantity
)
VALUES (
l_txn_if_id1                   --transaction_interface_id
, 'INV'                        --Source_Code
, -1                           --Source_Line_Id
, 'Y'                          --Process_Flag
, l_sysdate                    --Last_Update_Date
, l_user_id                    --Last_Updated_by
, l_sysdate                    --Creation_date
, l_user_id                    --Created_By
, 'AA0000'                     --Lot_Number
, l_exp_date                   --Lot_Expiration_Date
, -10                          --transaction_quantity
, -10                          --primary_quantity
);

--Get transaction_interface_id of resultant record

SELECT APPS.mtl_material_transactions_s.NEXTVAL
INTO l_txn_if_id2
FROM sys.dual;

--Populate the MTI record for resultant record

INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
transaction_interface_id,
transaction_header_id,
Source_Code,
Source_Line_Id,
Source_Header_Id,
Process_flag,
Transaction_Mode,
Lock_Flag,
Inventory_Item_Id,
revision,
Organization_id,
Subinventory_Code,
Locator_Id,
Transaction_Type_Id,
Transaction_Source_Type_Id,
Transaction_Action_Id,
Transaction_Quantity,
Transaction_UOM,
Primary_Quantity,
Transaction_Date,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
distribution_account_id,
parent_id,
transaction_batch_id,
transaction_batch_seq,
lpn_id,
transfer_lpn_id
)
VALUES
(
l_txn_if_id2,                    --transaction_header_id
l_txn_header_id,                 --transaction_interface_id
'INV',                           --source_code
-1,                              --source_header_id
-1,                              --source_line_id
1,                               --process_flag
3,                               --transaction_mode
2,                               --lock_flag
l_item_id,                       --inventory_item_id
null,                            --revision
l_org_id,                        --organization_id
'BULK',                          --subinventory_code
1181,                            --locator_id
l_transaction_type_id,           --transaction_type_id
l_transaction_source_type_id,    --transaction_source_type_id
l_transaction_action_Id,         --transaction_action_id
10,                              --transaction_quantity
'EA',                            --transaction_uom
10,                              --primary_quantity
l_sysdate,                       --Transaction_Date
l_sysdate,                       --Last_Update_Date
l_user_id,                       --Last_Updated_by
l_sysdate,                       --Creation_Date
l_user_id,                       --Created_by
l_distribution_account_id,       --distribution_account_id
l_parent_id,                     --parent_id
l_txn_header_id,                 --transaction_batch_id
2,                               --transaction_batch_seq (2 for child )
NULL,                            --lpn_id (for source MTI)
NULL                             --transfer_lpn_id (for resultant MTIs)
);

--Insert MTLI corresponding to the resulstant record

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE(
transaction_interface_id
, Source_Code
, Source_Line_Id
, Process_Flag
, Last_Update_Date
, Last_Updated_By
, Creation_Date
, Created_By
, Lot_Number
, lot_expiration_date
, Transaction_Quantity
, Primary_Quantity
)
VALUES (
l_txn_if_id2                     --transaction_interface_id
, 'INV'                          --Source_Code
, -1                             --Source_Line_Id
, 'Y'                            --Process_Flag
, l_sysdate                      --Last_Update_Date
, l_user_id                      --Last_Updated_by
, l_sysdate                      --Creation_date
, l_user_id                      --Created_By
, 'AA0001'                       --Lot_Number
, l_exp_date                     --Lot_Expiration_Date
, 10                             --transaction_quantity
, 10                             --primary_quantity
);
END;

/

References

BUG:5213502 - WANT TO INSERT LOT SPLIT TXNS VIA OPEN TRANSACTION INTERFACE
BUG:4494207 - GETTING ERROR ON A LOT TRANSLATE USING THE MTL_TRANSACTIONS_INTERFACE TABLE
BUG:7364573 - LOT TRANSLATE TRANSACTION VIA MTL_TRANSACTIONS_INTERFACE ERRORS