Oracle Apps Inventory Management Scripts

Oracle Apps Inventory Management Scripts 

Oracle Apps Manufacturing standard codes and their meanings

PROMPT MANUFACTURING

PROMPT Find standard codes and their meanings

SELECT   lookup_type, lookup_code, SUBSTR (meaning, 1, 60) "Meaning"

  FROM mfg_lookups

 WHERE UPPER (lookup_type) LIKE UPPER ('%'||NVL ('&YourType', 'N/A') || '%')

      OR lookup_type IN ('SERIAL_NUM_STATUS',

                        'MTL_PRIMARY_COST',

                        'MTL_CC_ENTRY_STATUSES',

                        'MTL_TXN_REQUEST_STATUS',

                        'MOVE_ORDER_TYPE'

                       )

ORDER BY lookup_type, lookup_code;

 

 

Oracle Apps Inventory Transaction setup scripts

a. Transaction types

PROMPT MANUFACTURING: INVENTORY

PROMPT Transaction Type

SELECT   transaction_type_id, transaction_action_id, transaction_source_type_id,

         transaction_type_name

    FROM mtl_transaction_types

   WHERE TO_CHAR (transaction_type_id) LIKE ('%&YourTransactionTypeID%')

ORDER BY transaction_type_id;

b. Transaction sources:

PROMPT Transaction Source

SELECT transaction_source_type_id, transaction_source_type_name

  FROM mtl_txn_source_types

 WHERE transaction_source_type_id LIKE NVL ('&TransSourceID', '%');

 

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;