1 Reply Latest reply: Feb 4, 2014 4:23 AM by HrishikeshJ RSS

How To Perform Lot Split Transactions Using Transaction Open Interface (MTI)

c69dd726-067b-4a93-8b7a-4fad0ac456ec Newbie
Currently Being Moderated

Can anyone give me some guidance on how to perform lot split transaction using MTI?

 

I am using the following code:

DECLARE
l_transaction_type_id NUMBER := 83;
l_transaction_action_id NUMBER := 41;
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 :=287996;
l_user_id NUMBER;
l_distribution_account_id NUMBER;
l_exp_date DATE;

BEGIN

--For Lot Merge, 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 resultant 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 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_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
'EACH', --subinventory_code
1198, --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
100000, --transaction_quantity
'EA', --transaction_uom
100000, --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
NULL, --lpn_id (for source MTI)
NULL --transfer_lpn_id (for resultant MTIs)
);

--Insert MTLI corresponding to the resultant 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
, 'Q0000.1' --Lot_Number
, l_exp_date --Lot_Expiration_Date
, 100000 --transaction_quantity
, 100000 --primary_quantity
);


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
'EACH', --subinventory_code
1198, --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
100000, --transaction_quantity
'EA', --transaction_uom
100000, --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
3, --transaction_batch_seq
NULL, --lpn_id (for source MTI)
NULL --transfer_lpn_id (for resultant MTIs)
);

--Insert MTLI corresponding to the resultant 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
, 'Q0000.1' --Lot_Number
, l_exp_date --Lot_Expiration_Date
, 100000 --transaction_quantity
, 100000 --primary_quantity
);


--Get transaction_interface_id of Source record-1

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

--Populate the MTI record for Source record-1

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
'EACH', --subinventory_code
1198, --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
-200000, --transaction_quantity
'EA', --transaction_uom
-200000, --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
NULL, --lpn_id (for source MTI)
NULL --transfer_lpn_id (for resultant MTIs)
);

--Insert MTLI corresponding to the Source record-1

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
, 'Q0000' --Lot_Number
, l_exp_date --Lot_Expiration_Date
, -200000 --transaction_quantity
, -200000 --primary_quantity
);
END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points