Queryies for Inventory Transactions Pending

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;