# Oracle EBS Pending Transactions SQL for Inventory Period Close

> Source: https://netray.co/tools/oracle-ebs-period-close-pending-transactions-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds SQL that counts and lists pending, errored and uncosted transactions in inventory and WIP before you close an Oracle inventory accounting period.

## Category

developer

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS consultant
- Cost accountant
- Oracle developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Organization ID

**Key:** org_id

**Type:** number

**Default:** 207

#### Period end date

**Key:** period_end

**Type:** date

**Default:** 2026-10-31

**Blocks:**

#### 1. Pending counts by source

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

#### 2. Errored inventory interface rows

**Body:** SELECT transaction_interface_id,
       inventory_item_id,
       transaction_date,
       process_flag,
       error_code,
       error_explanation
FROM   mtl_transactions_interface
WHERE  organization_id = {{org_id}}
AND    transaction_date < TO_DATE('{{period_end}}', 'YYYY-MM-DD') + 1
ORDER BY transaction_date;

#### 3. Uncosted material transactions

**Body:** 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 = {{org_id}}
AND    mmt.transaction_date < TO_DATE('{{period_end}}', 'YYYY-MM-DD') + 1
AND    mmt.costed_flag IN ('N', 'E')
ORDER BY mmt.transaction_date;

#### 4. Period status for the organization

**Body:** SELECT acct_period_id,
       period_name,
       open_flag,
       period_start_date,
       schedule_close_date,
       period_close_date
FROM   org_acct_periods
WHERE  organization_id = {{org_id}}
AND    period_start_date <= TO_DATE('{{period_end}}', 'YYYY-MM-DD')
ORDER BY period_start_date DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Why can't I close the Oracle inventory period?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

oracle ebs pending transactions inventory period close, mtl_material_transactions_temp query, mtl_transactions_interface errors sql, uncosted transactions oracle ebs, oracle inventory period close sql, wip_move_txn_interface pending, oracle cost management pending transactions

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json