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;