Account Alias as the name suggests is a Simplified Name for Account Combinations setup in GL.
Now normally in organizations, you need to throw materials away from the inventory in case material is unused or scrapped. So for this you use the Account Alias Issue Transactions.
Using Account Alias Issue transaction, you normally throw away materials and hence this reduces the stock.
The opposite of Account Alias issue is Miscellaneous receipt which you often use to increase material stock in Inventory.
Account Alias Setup is present in Inventory > Setup > Account Aliases
Our Requirement was the following :-
The business created a Specific subinventory = INVADJ to which they will first do a subinventory transfer from any specific inventory from which they want to reduce the stock or throw away material.
Hence they would first do a Subinventory Transfer. Actually to be particular, we needed e-Signatures to sign-off the scrap amounts to throw away from the stock. Hence this step. You may directly reduce stock from any subinventory you like to.
You can easily achieve the Account Alias Transaction from Application. For those who need the code, it is here. Now remember, the ultimate destination for the transaction is MTL_MATERIAL_TRANSACTIONS.
Account Alias Issue Code
-- Get the Interface Transaction ID
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id
FROM DUAL;
-- Get the Transaction type ID
SELECT transaction_type_id
INTO l_trx_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'Account alias issue';
-- Check if item is Lot Controlled / Serial Number Controlled
SELECT lot_control_code
,serial_number_control_code
INTO l_lot_control_code
,l_serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
INSERT INTO mtl_transactions_interface
(transaction_header_id
,transaction_interface_id
,transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,transaction_source_id
--,transaction_source_name
, lock_flag
,flow_schedule
,scheduled_flag
,inventory_item_id
,subinventory_code
,distribution_account_id
--,transaction_cost
, cost_type_id
,locator_id
)
VALUES (l_transaction_interface_id -- transaction_header_id
,l_transaction_interface_id -- transaction_interface_id
,l_trx_type_id -- transaction_type_id
,'EA' -- transaction_uom
,SYSDATE -- transaction_date
,225 -- organization_id
,- 4 /* -- transaction_quantity // Please Note this has to be -ve as you are reducing qty from stock */
,SYSDATE -- last_update_date
,1752 -- last_updated_by = Niladri Banerjee
,SYSDATE -- creation_date
,1752 -- created_by = Niladri Banerjee
,3 -- transaction_mode = BACKGROUND PROCESSING
,1 -- process_flag
,l_transaction_interface_id -- source_header_id
,l_transaction_interface_id -- source_line_id
,'SCRAP' -- source_code
,60 -- transaction_source_id
--,'SCRAP' -- transaction_source_name
, 2 -- Lock_flag
,'Y' -- flow_schedule
,2 -- scheduled_flag
,1701 -- inventory_item_id
,'STORES' -- subinventory_code
, 1 /*-- cost_type_id // For Standard Costs. Check cst_cost_types for your cost you want*/
,929 -- locator_id // Get the inventory_location_id from mtl_item_locations_kfv where concatenated_segements = << Your Specific Locator>>
);
-- If Item is Lot Controlled
IF
l_lot_control_code = 2
THEN
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_transaction_id
,primary_quantity
,lot_expiration_date
)
VALUES (
l_transaction_interface_id -- transaction_interface_id
,'L2345' /* lot_number // Get the Lot Number from MTL_LOT_NUMBERS as per your requirement */
,- (4) -- transaction_quantity // Remember this has to be -ve
,SYSDATE -- last_update_date
,1752 -- last_updated_by = Niladri Banerjee
,SYSDATE -- creation_date
,1752 -- created_by = Niladri Banerjee
,l_transaction_interface_id -- product_transaction_id
,- 4 -- primary_quantity
,l_lot_expiration_date /*-- lot_expiration_date // Get the Lot Expiry Date from MTL_LOT_NUMBERS as per your requirement*/
);
END IF;
-- Please note that an item can be both Lot and Serial Controlled together at the same time.
-- If item is Serial Controlled
END IF;
-- Please note that an item can be both Lot and Serial Controlled together at the same time.
-- If item is Serial Controlled
IF l_serial_number_control_code > 1
THEN
THEN
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (l_transaction_interface_id
, SYSDATE
, 1752
, SYSDATE
, 1752
, 'SERIAL-123'
, 'SERIAL-123'
);
END IF;
COMMIT;
Now remember the Material Transaction Processor Runs in the Background to pick the transaction. In case you want to Invoke it from backend for the specific Interface Transaction ID, here is the code.
Declare the following variables
l_return_status VARCHAR2 (10);
l_errorcode NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_trans_count NUMBER;
and use the following code to invoke transaction manager
l_return_value :=
inv_txn_manager_pub.process_transactions
(p_api_version => 1.0
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_trans_count => l_trans_count
,p_table => 1
,p_header_id => l_transaction_interface_id
);
If l_return_status = 'E'
THEN
-- Error
ELSE
-- Success !!!
END IF;
From Application the inventory transaction processor can run in the following way
Go to Inventory > Setup > Transactions > Interface Managers and then Highlight "Material Transaction"
In case you successfully entered the data into interface tables but the transaction has not appeared in MTL_MATERIAL_TRANSACTIONS, then check for errors
SELECT error_explanation FROM MTL_TRANSACTIONS_INTERFACE WHERE transaction_interface_id = << Your Transaction Interface ID >>
This Completes the Account Alias Issue Transaction !!! Hope this helps !! Please write to me for any queries in the same blog post.
