Oracle Apps Inventory Management Scripts

Oracle Apps Inventory Management Scripts

Inventory Transactions

a. Stuck interface transactions (Group By)

      PROMPT Stuck Transactions - GroupBy MTI

      SELECT   transaction_type_id,organization_id,SUBSTR(ERROR_CODE, 1, 30),

               SUBSTR (error_explanation, 1, 50),

               TO_CHAR (transaction_date, 'YYYY-MM'), COUNT (*)

          FROM mtl_transactions_interface

      GROUP BY transaction_type_id,

               organization_id,

               TO_CHAR (transaction_date, 'YYYY-MM'),

               SUBSTR (ERROR_CODE, 1, 30),

               SUBSTR (error_explanation, 1, 50);

b. Stuck pending transactions (Group By)

      PROMPT Stuck Transactions - GroupBy MMTT

      SELECT   transaction_type_id,organization_id,SUBSTR(ERROR_CODE, 1, 30),

               SUBSTR (error_explanation, 1, 50),

               TO_CHAR (transaction_date, 'YYYY-MM'), COUNT (*)

          FROM mtl_material_transactions_temp

      GROUP BY transaction_type_id,

               organization_id,

               TO_CHAR (transaction_date, 'YYYY-MM'),

               SUBSTR (ERROR_CODE, 1, 30),

               SUBSTR (error_explanation, 1, 50);

             

c. Stuck move order transactions (Group By)

      PROMPT Stuck Transactions - GroupBy Move Order

      SELECT   transaction_type_id, TO_CHAR (transaction_date, 'YYYY-MON'),

               DECODE (

                  transaction_status,

                  2,

                  'Untransacted Move order',

                  transaction_status

               ),

               ERROR_CODE, error_explanation, COUNT (*)

          FROM mtl_material_transactions_temp

         WHERE organization_id = &org_id

      GROUP BY transaction_type_id,

               TO_CHAR (transaction_date, 'YYYY-MON'),

               DECODE (

                  transaction_status,

                  2,

                  'Untransacted Move order',

                  transaction_status

               ),

               ERROR_CODE,

               error_explanation;

d. Uncosted transactions (Group By)

      PROMPT Uncosted Transactions - GroupBy MMT

      SELECT   transaction_type_id, organization_id, costed_flag,

               TO_CHAR (transaction_date, 'YYYY-MM'), ERROR_CODE,

               SUBSTR (error_explanation, 1, 50), COUNT (*)

          FROM mtl_material_transactions

         WHERE costed_flag IN ('N', 'E')

      GROUP BY transaction_type_id,

               organization_id,

               costed_flag,

               TO_CHAR (transaction_date, 'YYYY-MM'),

               ERROR_CODE,

               SUBSTR (error_explanation, 1, 50);

e. Dump information about transaction tables

      PROMPT Stuck Transactions Dump - MTI

      SELECT   transaction_interface_id, inventory_item_id, organization_id,

               subinventory_code, locator_id, revision, transaction_quantity,

               transaction_date, transaction_type_id, transaction_source_id,

               transfer_subinventory, transfer_locator, trx_source_line_id,

               cost_group_id, process_flag, lock_flag, transaction_mode,

               error_explanation, ERROR_CODE

          FROM mtl_transactions_interface

      ORDER BY transaction_source_id, trx_source_line_id;

     

      PROMPT Stuck Transactions Dump - MMTT

      SELECT   transaction_temp_id, inventory_item_id, organization_id,

               subinventory_code, locator_id, revision, transaction_quantity,

               transaction_date, transaction_type_id, transaction_source_id,

   transfer_subinventory, transfer_to_location, trx_source_line_id,cost_group_id, process_flag, lock_flag, transaction_mode,error_explanation, ERROR_CODE

          FROM mtl_material_transactions_temp

      ORDER BY transaction_source_id, trx_source_line_id;

           

      PROMPT Stuck Transactions Dump - MMT

  SELECT transaction_id, inventory_item_id, organization_id, subinventory_code,locator_id, revision, transaction_quantity, transaction_date,transaction_type_id, transaction_source_id, transfer_subinventory,transfer_locator_id, trx_source_line_id, cost_group_id,error_explanation, ERROR_CODE

          FROM mtl_material_transactions

         WHERE costed_flag IN ('N', 'E')

      ORDER BY transaction_source_id, trx_source_line_id;

How to find the Quantity on hand.

How to find the Quantity on hand.

Abstract
This article is how to find the Quantity on hand.

The quantities considered in calculating the
Available quantities are
On hand Quantity --> quantity from mtl_onhand_quantities_details
Allocated Quantity --> mtl_material_transactions_temp
Reserved Quantity --> mtl_reservations

But these table reflects the total of quantity available in the sub inventory without
considering the provision for reserve or allocated quantity.

For Example - Total on hand quantity: 100,
Allocated quantity : 10
and reserved quantity: 20.
let suppose out of 20 reserved quantity, 5 is reserved against the demand source for
which we are performing the transaction right now.
Then the available quantity while transacting the above source is 100-10-20+5 =75.

But in the above tables we will get it as 100 only as not a single unit is went of of the sub inventory.



The relevant form in application is INVMATWB.fmb.
NAVIGATION:
Inventory > On Hand Availability>On-hand quantity --- query the item.



The API, that calculates the available quantities is
inv_quantity_tree_pvt.create_tree.

Inventory Transfer to GL

As a part of reconciliation with GL the inventory details are transferred to General Ledger. Oracle has provided the users with a standard program: Transfer Transactions to GL which can be used to transfer the inventory information to general ledger.

On running this program all the accounting information in the INV module is transferred to General Ledger. The Program picks up all the records from the table mtl_transaction_accounts having gl_batch_id = -1 for the entered program parameters and transfers them to gl interface.

The Program is generally run as a part of period end process when account reconciliations need to be done.

Like the other modules when the request is run all the accounting information is transferred to GL Interface. Users can subsequently run the Journal Import and Journal Posting Programs to create journals and post them into Ledger.

When submitting the Inventory Transfer to GL Program the user has an option of transferring the information at a summary level or detailed level

Summary: The accounting information of the relevant transactions are summarized and                summary information is transferred, as summary information is being transferred the process is faster and takes less time.

Detail: In case of detail a detailed information is transferred. Detailed accounting records are created for each transaction of Inventory. As the mode since , since detailed transactions are created for individual Inventory transactions the process is more time consuming and thus processing times are higher.

If none is selected, no transfer of accounting information to GL is done for this organization.

The Program is generally run as a part of period closing , however we can perform the general ledger transfer at any time during an open period--not just at period close. The transfer loads summary or detail accounting activity for any open period into the general ledger interface, including both inventory and work in process entries. When more than one period is open, the transfer selects transactions from the first open period up to the entered transfer date, and passes the correct accounting date and financial information into the general ledger interface. 

As we have mentioned the Program picks up the relevant records from the table mtl_transaction accounts having a gl_batch_id = -1, When the transactions are passed to the gl_interface it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.