CST Uninvoiced Receipts – 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

Application: Bills of Material Description: Uninvoiced Receipts Report Templates: Select a template to show Accruals summarized by

Each template includes a Pivot summarizing the accruals by Accrual Account and the Pivot/Sort Parameter Value (Vendor, Category, PO/Release Number)

Source: Uninvoiced Receipts Report Short Name: CSTACREP DB Package: XXEN_CST

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, Accrued Receipts, Include Online Accruals, Include Closed POs, Categories From, Categories To, Minimum Accrual Amount, Period Name, Vendors From, Vendors To, Sort/Pivot By, Dynamic Precision Option, DFF Display

Oracle EBS Tables Used

po_vendor_sites_all, mtl_parameters, rcv_transactions, rcv_shipment_headers, cst_per_end_accruals_temp, po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all, po_vendors, po_line_types, po_releases_all, mtl_system_items_b_kfv, gl_sets_of_books, fnd_currencies, mtl_categories_kfv, gl_code_combinations_kfv

Report Categories

Enginatics, R12 only

AP Invoices and Lines, CST AP and PO Accrual Reconciliation, PO Headers and Lines, GL Account Distribution Analysis, GL Account Analysis, CST Miscellaneous Accrual Reconciliation, GL Account Analysis (Drilldown) (with inventory and WIP)

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 Uninvoiced Receipts - Accruals by PO Release Distributions 27-Dec-2024 100620.xlsx
Blitz Report™ XML Import CST_Uninvoiced_Receipts.xml
SQL Source CST Uninvoiced Receipts.sql
Full Report on Enginatics www.enginatics.com/reports/cst-uninvoiced-receipts/

Case Study & Technical Analysis: CST Uninvoiced Receipts

1. Executive Summary

The CST Uninvoiced Receipts report is a standard Oracle Cost Management report used to support the Period End Accrual process. It provides a detailed listing of all Purchase Order receipts that have been accrued (received) but not yet fully invoiced as of a specific period end.

Unlike the “Accrual Reconciliation” reports which are based on a snapshot table (CST_RECONCILIATION_SUMMARY) populated by a load program, this report is often run directly against the transactional tables (or a temporary table CST_PER_END_ACCRUALS_TEMP depending on the version/mode) to validate the General Ledger accrual balance. It is the primary audit report for the “Received Not Invoiced” liability.

2. Business Context & Usage

2.1. Purpose

2.2. Key Stakeholders

2.3. Process Flow

  1. Receipt: Goods are received.
    • On-Receipt Accrual (Inventory): Liability is booked immediately.
    • Period-End Accrual (Expense): Liability is not booked until period end.
  2. Period Close: The “Uninvoiced Receipts Report” is run.
  3. GL Posting:
    • For Expense items, the report output is used to create a manual or automated reversing journal entry for the liability.
    • For Inventory items, the report serves as the subledger detail for the existing GL balance.
  4. Reconciliation: The report total is compared to the GL Accrual Account balance.

3. Technical Analysis

3.1. Data Sources & Tables

The report logic depends on whether it’s running for “On-Receipt” or “Period-End” accruals, but generally involves:

3.2. Key Logic & Calculations

3.3. Parameters

4. Common Issues & Troubleshooting

5. SQL Query Structure

The provided SQL is a direct extraction query, likely designed to replace the standard Oracle RDF report with a Blitz Report Excel extract.

  1. Inner Query (Aggregation):
    • Selects from CST_PER_END_ACCRUALS_TEMP (aliased as x or implied in the source).
    • Joins to PO and RCV tables.
    • Calculates line_qty_accrued, ship_qty_accrued using window functions (SUM OVER PARTITION) to allow drilling down from Summary to Detail.
  2. Outer Query (Formatting):
    • Formats the unit prices and amounts based on the precision parameters.
    • Handles the Pivot Sort Value logic to allow dynamic grouping in the Excel output.
    • Constructs user-friendly columns like PO (Release) Number.

This structure allows the report to serve multiple purposes: a high-level summary for the Controller and a detailed transaction list for the AP Clerk.

© Enginatics GmbH