CST AP and PO Accrual Reconciliation
Description:
Imported from Concurrent Program Application: Bills of Material Source: AP and PO Accrual Reconciliation Report Short Name: CSTACRAP
Parameters
Operating Unit, Show Transaction Details, Balancing Segment From, Balancing Segment To, Accrual Account, Aging Period Days, Minimum Age in Days, Maximum Age in Days, Minimum Outstanding Balance, Maximum Outstanding Balance, Item, Vendor, PO Number, PO Release, PO Line, PO Shipment, Destination Type, Sort By
Used tables
per_all_people_f, mtl_system_items_vl, cst_reconciliation_codes, cst_ap_po_reconciliation, ap_invoices_all, ap_invoice_distributions_all, mtl_parameters, rcv_transactions, rcv_shipment_headers, cst_reconciliation_summary, po_distributions_all, po_line_locations_all, po_releases_all, po_lines_all, po_headers_all, po_vendors, po_destination_types_all_v, gl_code_combinations_kfv, cst_accrual_accounts, hr_all_organization_units, hr_organization_information, gl_sets_of_books, capr
Categories
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
CST AP and PO Accrual Reconciliation 14-Jul-2021 041641.xlsx
Report SQL
www.enginatics.com/reports/cst-ap-and-po-accrual-reconciliation/
Blitz Report™ import options
CST_AP_and_PO_Accrual_Reconciliation.xml
Case Study & Technical Analysis: CST AP and PO Accrual Reconciliation
1. Executive Summary
The CST AP and PO Accrual Reconciliation report is a critical financial tool used to reconcile the Accrual Accounts (typically the “Inventory AP Accrual” or “Expense AP Accrual” accounts) between the General Ledger (GL), Purchasing (PO), and Payables (AP) subledgers. It identifies discrepancies between the estimated liability recorded upon receipt of goods/services (PO Receipt) and the actual liability recorded upon invoicing (AP Invoice).
This report is essential for the Period End Accrual Process, ensuring that the accrual account balance in the GL accurately reflects the “Received but Not Invoiced” liability. It helps organizations identify write-off candidates, fix process gaps (e.g., un-invoiced receipts, price variances), and support audit requirements.
2. Business Context & Usage
2.1. Purpose
- Reconciliation: Matches PO receipts (accruals) with AP invoices to ensure the accrual account clears to zero for fully processed transactions.
- Liability Tracking: Provides a detailed breakdown of the outstanding accrual balance (Received Not Invoiced).
- Write-Off Identification: Highlights old or mismatched transactions that will never be invoiced or matched, allowing users to write them off to clear the balance.
- Audit Support: Serves as the subledger detail supporting the GL Accrual Account balance.
2.2. Key Stakeholders
- Accounts Payable (AP) Department: To resolve invoice matching holds and discrepancies.
- Purchasing Department: To close old POs and resolve receipt issues.
- Cost Accounting / Inventory Team: To monitor inventory accruals and write-offs.
- General Ledger (GL) Team: To reconcile the GL balance for period close.
2.3. Process Flow
- PO Receipt: Goods are received. The system debits Inventory/Expense and credits the Accrual Account (Estimated Liability).
- AP Invoice: Invoice is entered and matched to the PO/Receipt. The system debits the Accrual Account and credits Liability (AP).
- Accrual Load Run: The “Accrual Reconciliation Load Run” program populates the reconciliation tables (
CST_RECONCILIATION_SUMMARY,CST_AP_PO_RECONCILIATION). - Report Generation: This report is run to view the data populated by the load program.
- Analysis & Action: Users analyze the report to identify unmatched items, price variances, or “stuck” transactions.
- Write-Off: Irreconcilable differences are written off using the “Accrual Write-Off” form.
3. Technical Analysis
3.1. Data Sources & Tables
The report relies on a set of specialized reconciliation tables populated by the Accrual Load Run program, joined with standard PO and AP tables for details.
- Core Reconciliation Tables:
CST_RECONCILIATION_SUMMARY(CRS): Stores the summarized balance for each PO Distribution. This is the primary driver for the report’s logic regarding balances.CST_AP_PO_RECONCILIATION(CAPR): Contains the detailed transaction history (Receipts, Invoices, Write-offs) for each PO Distribution.CST_RECONCILIATION_CODES(CRC): Lookup table for transaction types (e.g., ‘Invoice’, ‘Receive’, ‘Match’).CST_ACCRUAL_ACCOUNTS: Stores the specific accrual accounts being reconciled.
- Standard Subledger Tables:
- Purchasing:
PO_HEADERS_ALL,PO_LINES_ALL,PO_LINE_LOCATIONS_ALL,PO_DISTRIBUTIONS_ALL,PO_RELEASES_ALL,RCV_TRANSACTIONS,RCV_SHIPMENT_HEADERS. - Payables:
AP_INVOICES_ALL,AP_INVOICE_DISTRIBUTIONS_ALL. - Inventory/Items:
MTL_SYSTEM_ITEMS_VL,MTL_PARAMETERS. - GL/Org:
GL_CODE_COMBINATIONS,HR_ALL_ORGANIZATION_UNITS.
- Purchasing:
3.2. Key Logic & Calculations
- Balances: The report calculates three key balances from
CST_RECONCILIATION_SUMMARY:PO_BALANCE: Value of receipts (Quantity Received * PO Price).AP_BALANCE: Value of invoices matched (Quantity Billed * Invoice Price).WRITE_OFF_BALANCE: Value of any write-offs performed.TOTAL_BALANCE: The net outstanding balance (PO_BALANCE+AP_BALANCE+WRITE_OFF_BALANCE). Ideally, this should be zero for closed transactions.
- Aging: The report calculates the age of the accrual entry to help prioritize old items.
- Logic:
sysdate - greatest(last_receipt_date, last_invoice_date). - Profile Option:
CST_ACCRUAL_AGE_IN_DAYSdetermines if aging is based on the last activity date or the initial transaction date.
- Logic:
- Transaction Source:
- Determined by the presence of
INVOICE_DISTRIBUTION_ID(AP) orWRITE_OFF_ID(WO). If neither, it is a PO Receipt.
- Determined by the presence of
3.3. Parameters
- Operating Unit: Filters by the relevant financial entity.
- Balancing Segment (From/To): Allows filtering by specific GL balancing segments (e.g., Company Code).
- Accrual Account: Filters for a specific GL account being reconciled.
- Aging Parameters (Days, Min/Max Balance): Filters to focus on high-value or old items.
- Show Transaction Details: Toggle to show every receipt/invoice line or just the summary per PO distribution.
4. Common Issues & Troubleshooting
- Data Not Appearing: The “Accrual Reconciliation Load Run” program must be run before this report. If the load program hasn’t run or failed, the report will be empty or stale.
- Balances Don’t Match GL:
- Manual journal entries to the accrual account in GL are not captured by this subledger report.
- Transactions occurring after the last “Load Run” will not be reflected.
- Performance: The report can be slow if the
CST_AP_PO_RECONCILIATIONtable is very large. Regular purging (via “Accrual Write-Off”) and archiving is recommended. - “Stuck” Balances: Often caused by:
- Receipts not invoiced (RNI).
- Invoices entered but not matched to the PO.
- Currency exchange rate variances.
- Rounding differences.
5. SQL Query Structure
The query uses a Common Table Expression (CTE) named capr to pre-calculate and format the main dataset.
- CTE
capr: JoinsCST_RECONCILIATION_SUMMARYandCST_AP_PO_RECONCILIATIONwith PO, AP, and GL tables.- Derives
ledger,operating_unit, and GL segments. - Calculates
age_in_daysandaging_period. - Formats PO and Invoice references.
- Derives
- Main Select: Selects from the
caprCTE.- Applies filtering based on the parameters (Aging, Balances, Account, etc.).
- Handles the “Show Transaction Details” logic (though the provided SQL seems to pull details by default, the standard report often groups them).
This structure allows for a flexible, high-performance extract that can be easily pivoted in Excel (via Blitz Report) to analyze the accrual reconciliation status.
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics