Skip to content

CST Uninvoiced Receipts

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

CST Uninvoiced Receipts

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
CST Uninvoiced Receipts.sql
CST Uninvoiced Receipts.sql
 
 
DESCRIPTION.md
DESCRIPTION.md
 
 
README.md
README.md
 
 

README.md

layout default
title CST Uninvoiced Receipts | Oracle EBS SQL Report
description Application: Bills of Material Description: Uninvoiced Receipts Report Templates: Select a template to show Accruals summarized by - PO/Release…
keywords Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, CST, Uninvoiced, Receipts, rcv_transactions, rcv_shipment_headers, cst_per_end_accruals_temp
permalink /CST%20Uninvoiced%20Receipts/

CST Uninvoiced Receipts – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

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

  • PO/Release Distributions (Default)
  • PO/Release Shipments
  • PO/Release Lines

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

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, Show DFF Attributes

Oracle EBS Tables Used

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

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

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
Full SQL 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

  • Period End Validation: To substantiate the balance in the Accrual Account at the end of a financial period.
  • Liability Estimation: To determine the company's outstanding liability to suppliers for goods received.
  • Audit Compliance: Auditors use this report to verify that the liability recorded in the GL matches the detailed subledger transactions.
  • Aging Analysis: Helps identify old receipts that haven't been invoiced, which might indicate process failures or lost invoices.

2.2. Key Stakeholders

  • General Ledger (GL) Team: To book period-end accrual entries (if using On-Receipt Accrual) or to reverse/book entries (if using Period-End Accrual).
  • Accounts Payable (AP): To investigate why receipts haven't been invoiced.
  • Purchasing: To close out old POs that will never be invoiced.

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:

  • Core Transaction Tables:

    • RCV_TRANSACTIONS (RT): The source of truth for receipts.
    • RCV_SHIPMENT_HEADERS (RSH): Receipt header details (Receipt Number, Date).
    • CST_PER_END_ACCRUALS_TEMP (CPEA): A temporary table often populated by the "Accrual Load" or "Period End Accrual" concurrent program to store the calculated accrual values for the report.

  • Purchasing Tables:

    • PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL, PO_RELEASES_ALL.
    • PO_VENDORS.

  • GL/Org:

    • GL_CODE_COMBINATIONS, GL_SETS_OF_BOOKS.

3.2. Key Logic & Calculations

  • Accrual Amount:
    • Calculated as (Quantity Received - Quantity Billed) * PO Price.
    • If Quantity Billed > Quantity Received, the accrual is zero (or negative depending on specific logic/bugs, but typically this report focuses on positive liability).
  • Currency Conversion:
    • The report handles both PO currency and Functional currency.
    • Accrual Amount (PO Currency) vs Functional Accrual Amount (Ledger Currency).
  • Aging:
    • Age in Days = Report Date - Receipt Date.
  • Precision:
    • The SQL includes logic for Dynamic Precision Option to handle rounding differences between the PO definition and the currency definition.

3.3. Parameters

  • Accrued Receipts: Choose to report on "On Receipt" (Inventory) or "Period End" (Expense) accruals.
  • Include Online Accruals: Whether to include receipts that accrue immediately.
  • Include Closed POs: Critical for finding "stuck" accruals on closed orders.
  • Period Name: The target financial period for the snapshot.
  • Minimum Accrual Amount: Filters out negligible balances.
  • Category/Vendor: For targeted analysis.

4. Common Issues & Troubleshooting

  • Report vs. GL Mismatch:
    • Manual journal entries in GL.
    • Transactions occurring after the report snapshot was taken.
    • Exchange rate variances.
  • Negative Balances: Can occur if an invoice was matched to a receipt that was later returned (RTV) without a credit memo, or over-invoicing.
  • Performance: The report can be slow if CST_PER_END_ACCRUALS_TEMP is large or if the query runs directly against RCV_TRANSACTIONS without proper indexing.
  • "Ghost" Receipts: Receipts on cancelled lines or closed POs that still show up.

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.

Useful Links

© 2026 Enginatics