INV Period Close Pending Transactions – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Inventory · all Inventory reports
Overview
Summary report to display the pending transaction counts as found in the Inventory Account Periods Close form, checking open receipts, pending shipments, failed inventory, WIP, etc
The Period Count shows the pending transactions occurring within the specified period.
The Period Close Count shows the count of pending transactions as at the scheduled period close date of the specified period.
The Period Close Count are the counts displayed in the Pending Transactions Inventory Account Periods Close Form.
This is the accumulated count of pending transactions upto the shceduled close date of the specified period, except for the count of ‘Unprocessed Shipping Transactions’ which are not carried over to the next period.
Period count queries are sourced from procedure CST_AccountingPeriod_PUB.Get_PendingTcount (CSTPAPEB.pls 120.18.12020000.8)
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Organization Code, Period From, Period To
Oracle EBS Tables Used
org_organization_definitions, org_acct_periods, mtl_material_transactions_temp, xxen_report_org_access, mtl_material_transactions, wip_cost_txn_interface, wsm_split_merge_txn_interface, wsm_lot_move_txn_interface, wsm_lot_split_merges_interface, mtl_parameters, cst_lc_adj_interface, rcv_transactions_interface, mtl_transactions_interface, wip_move_txn_interface, wip_discrete_jobs, wip_entities, wsh_delivery_details, wsh_delivery_assignments_v, wsh_new_deliveries, wsh_delivery_legs, wsh_trip_stops, wsh_delivery_assignments, oe_order_lines_all, po_requisition_lines_all, mtl_interorg_parameters
Report Categories
Related Reports
CAC Interface Error Summary, CAC WIP Jobs With Complete Status But Not Ready for Close, CAC WIP Jobs With Complete Status Which Are Ready for Close, WSH Shipping/Delivery Transactions, GL Account Distribution Analysis
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | INV Period Close Pending Transactions 03-Dec-2024 195754.xlsx |
| Blitz Report™ XML Import | INV_Period_Close_Pending_Transactions.xml |
| SQL Source | INV Period Close Pending Transactions.sql |
| Full Report on Enginatics | www.enginatics.com/reports/inv-period-close-pending-transactions/ |
INV Period Close Pending Transactions - Case Study & Technical Analysis
Executive Summary
The INV Period Close Pending Transactions report is the “Pre-Flight Check” for the inventory month-end close. Before an accounting period can be closed, Oracle requires all transactions to be fully processed. This report identifies any “stuck” transactions (Unprocessed Material, Pending WIP, Uncosted items) that are blocking the close.
Business Challenge
The “Period Close” is often a stressful time. The system prevents closing if anything is pending, but finding what is pending can be like finding a needle in a haystack. Users struggle with:
- Vague Errors: The system says “Pending Transactions exist” but doesn’t say where.
- Interface Tables: Transactions might be stuck in an interface table (Open Interface, WIP Interface) that users can’t easily see.
- Uncosted Items: Transactions that processed physically but failed financially (e.g., missing cost).
Solution
The INV Period Close Pending Transactions report aggregates counts from all the various “holding areas” in the system. It replicates the logic of the “Pending Transactions” form but provides a printable, shareable format.
Key Features:
- Comprehensive Check: Checks Material Transactions, WIP Move Transactions, Uncosted Transactions, Pending Shipping, and Receiving Interfaces.
- Count Summary: Shows the count of stuck records in each category.
- Drill-Down: (In some versions) Provides details on the specific IDs that are stuck.
Technical Architecture
The report queries a series of interface and temporary tables that act as queues for the inventory processors.
Key Tables and Views
MTL_MATERIAL_TRANSACTIONS_TEMP: The primary queue for on-line processing.MTL_TRANSACTIONS_INTERFACE: The queue for open interface / background processing.WIP_COST_TXN_INTERFACE: Pending resource transactions.RCV_TRANSACTIONS_INTERFACE: Pending receipts.WSH_DELIVERY_DETAILS: Pending shipments (not yet interfaced).
Core Logic
- Union Query: The report runs a series of
SELECT COUNT(*)queries against each interface table. - Date Filtering: It only counts transactions that fall within the period being closed.
- Resolution: If the count > 0, the period cannot close.
Business Impact
- Faster Close: Pinpoints the exact blockers so they can be fixed immediately.
- Data Integrity: Ensures that all activity for the month is fully accounted for before the books are closed.
- Stress Reduction: Eliminates the “mystery” of why the period won’t close.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH