Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS Pending Transactions SQL for Inventory Period Close

Inventory period close in Oracle EBS stops when pending or uncosted transactions remain before the period end. This generator gives you SQL to count them by source, list the errored rows, and check the period status, so you can clear them before close day.

Fill in

Organization ID

Period end date

Generated code

Download:

1. Pending counts by source

SELECT 'MTL_MATERIAL_TRANSACTIONS_TEMP' AS source, COUNT(*) AS pending_rows
FROM   mtl_material_transactions_temp
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
UNION ALL
SELECT 'MTL_TRANSACTIONS_INTERFACE', COUNT(*)
FROM   mtl_transactions_interface
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
UNION ALL
SELECT 'WIP_MOVE_TXN_INTERFACE', COUNT(*)
FROM   wip_move_txn_interface
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
UNION ALL
SELECT 'WIP_COST_TXN_INTERFACE', COUNT(*)
FROM   wip_cost_txn_interface
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
UNION ALL
SELECT 'MTL_MATERIAL_TRANSACTIONS (uncosted)', COUNT(*)
FROM   mtl_material_transactions
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
AND    costed_flag IN ('N', 'E');

2. Errored inventory interface rows

SELECT transaction_interface_id,
       inventory_item_id,
       transaction_date,
       process_flag,
       error_code,
       error_explanation
FROM   mtl_transactions_interface
WHERE  organization_id = 207
AND    transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
ORDER BY transaction_date;

3. Uncosted material transactions

SELECT mmt.transaction_id,
       mmt.transaction_date,
       mmt.inventory_item_id,
       mmt.transaction_type_id,
       mmt.transaction_quantity,
       mmt.costed_flag
FROM   mtl_material_transactions mmt
WHERE  mmt.organization_id = 207
AND    mmt.transaction_date < TO_DATE('2026-10-31', 'YYYY-MM-DD') + 1
AND    mmt.costed_flag IN ('N', 'E')
ORDER BY mmt.transaction_date;

4. Period status for the organization

SELECT acct_period_id,
       period_name,
       open_flag,
       period_start_date,
       schedule_close_date,
       period_close_date
FROM   org_acct_periods
WHERE  organization_id = 207
AND    period_start_date <= TO_DATE('2026-10-31', 'YYYY-MM-DD')
ORDER BY period_start_date DESC;

Oracle's own Pending Transactions form and the period close pending transactions report remain the system of record. Use this SQL to prepare and triage. Column names are for Release 12 style schemas, so verify them against the eTRM. Resolve rows through the standard forms or resubmit via supported processes, never by deleting rows blindly.

Month-end pending transaction watch

Optional: discuss read-only monitoring of pending and uncosted transactions across your EBS organizations.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter the organization id and the period end date. Run block one first to see which source has rows. Then use the detail blocks for errored interface rows and uncosted transactions. Work down the sources in order: resolve the interface errors, let the transaction manager and cost processor run, then re-run the counts until each source shows zero.

Block 1: counts by pending source
Block 2: errored inventory interface rows
Block 3: uncosted transactions
Block 4: period status

Why transactions block the close

The close process needs every transaction dated in the period to be fully processed and costed, so the period balances and general ledger transfer reflect reality. Pending rows may sit in the temp table awaiting the transaction manager, in an interface table with an error, or in the material transactions table with a costed flag that shows the cost worker has not completed. Each has a different fix.

Reading and resolving the results

Rows in the interface with an error code need the underlying cause fixed, such as a closed job, a missing subinventory or an invalid account, and then resubmission through the supported route. Uncosted rows usually point to a cost manager or cost worker that is not running, or to missing cost data. Rows in the temp table often mean a stuck transaction manager or a locked row.

Common mistakes

Teams check only the inventory side and forget WIP interfaces and receiving. Others delete interface rows to make a count go to zero, which loses a real business transaction. Also remember to run the cost processing and transfer programs after clearing rows, because the close checks are rerun at close time.

Frequently Asked Questions

Why can't I close the Oracle inventory period?

The usual reasons are pending transactions in the temp or interface tables, uncosted transactions, unprocessed receiving or WIP activity, or open items in other modules. The Pending Transactions form and the SQL here show which source is blocking.

What does costed_flag N or E mean?

N indicates the transaction is waiting to be costed, and E indicates a costing error. Both stop the period close until the cost processor completes or the error is resolved.

Can I delete rows from MTL_TRANSACTIONS_INTERFACE?

Technically possible, but it discards a real business event. Correct the cause and resubmit, or reverse the activity through a supported transaction. Delete only with documented approval, and keep the data you remove.

Does this cover receiving and purchasing?

Not fully. Receiving transactions and accrual items have their own pending checks, and the receiving interface is separate. Check the Pending Transactions form and receiving reports in addition to this SQL.

If month end triage consumes your team, we can discuss read-only monitoring of pending transactions over your EBS data, built on your own servers.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Developer Tools tools

See all

Back to All Tools