Skip to the content.

CST AP and PO Accrual Reconciliation – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Management · all Cost Management reports

Overview

Imported from Concurrent Program Application: Bills of Material Source: AP and PO Accrual Reconciliation Report Short Name: CSTACRAP

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

Operating Unit, Show Transaction Details, Transaction Date From, Transaction Date To, 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

Oracle EBS Tables Used

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

Report Categories

Enginatics, R12 only

GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), CST Miscellaneous Accrual Reconciliation, GL Account Distribution Analysis, PO Headers and Lines, CAC AP Accrual IR ISO Match Analysis, CAC Receiving Activity Summary, PO Purchase Price Variance, AP Invoices and Lines

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 CST AP and PO Accrual Reconciliation 14-Jul-2021 041641.xlsx
Blitz Report™ XML Import CST_AP_and_PO_Accrual_Reconciliation.xml
SQL Source CST AP and PO Accrual Reconciliation.sql
Full Report on Enginatics www.enginatics.com/reports/cst-ap-and-po-accrual-reconciliation/

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

2.2. Key Stakeholders

2.3. Process Flow

  1. PO Receipt: Goods are received. The system debits Inventory/Expense and credits the Accrual Account (Estimated Liability).
  2. AP Invoice: Invoice is entered and matched to the PO/Receipt. The system debits the Accrual Account and credits Liability (AP).
  3. Accrual Load Run: The “Accrual Reconciliation Load Run” program populates the reconciliation tables (CST_RECONCILIATION_SUMMARY, CST_AP_PO_RECONCILIATION).
  4. Report Generation: This report is run to view the data populated by the load program.
  5. Analysis & Action: Users analyze the report to identify unmatched items, price variances, or “stuck” transactions.
  6. 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.

3.2. Key Logic & Calculations

3.3. Parameters

4. Common Issues & Troubleshooting

5. SQL Query Structure

The query uses a Common Table Expression (CTE) named capr to pre-calculate and format the main dataset.

  1. CTE capr: Joins CST_RECONCILIATION_SUMMARY and CST_AP_PO_RECONCILIATION with PO, AP, and GL tables.
    • Derives ledger, operating_unit, and GL segments.
    • Calculates age_in_days and aging_period.
    • Formats PO and Invoice references.
  2. Main Select: Selects from the capr CTE.
    • 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.


© Enginatics GmbH