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;
