Account Alias Issue Code

What is Account Alias Issue?

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

IF l_serial_number_control_code > 1
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.