CAC Receiving Account Detail – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Accounting · all Cost Accounting reports
Overview
Receiving accounting distributions in detail by item, purchase order, release, transaction date, creation date, created by and transaction id. Show SLA Accounting = Yes reports the Subledger Accounting (Create Accounting) account, and only distributions accounted in Subledger Accounting. No reports the receiving subledger account. Show Project Information and Show WIP Outside Processing add the project and task, and the WIP job and outside processing resource columns. Purchasing Category is the purchase order line category, which also categorizes expense receipts without an item. On instances with India Localization, GST columns (GST/PAN/TAN, tax, HSN/SAC, NR Tax) are also included.
/* +=============================================================================+ – | Copyright 2010 - 2022 Douglas Volz Consulting, Inc. – | Permission to use this code is granted provided the original author is – | acknowledged. No warranties, express or otherwise is included in this – | permission. – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_rcv_dist_xla_detail_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 05 Apr 2010 Douglas Volz Initial Coding based on XXX_RCV_DIST_XLA_SUM_REPT.sql – | 1.18 21 Aug 2022 Douglas Volz Streamline dynamic SQL code. – | 1.19 03 Sep 2022 Douglas Volz Add Accounting Line Type, Transaction Type and Minimum Amount parameters. – | And language translations for Accounting Line Types. – | 1.20 08 Oct 2022 Douglas Volz Add project task information. – +=============================================================================+*/
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
Transaction Date From, Transaction Date To, Show SLA Accounting, Show Project Information, Show WIP Outside Processing, Category Set 1, Category Set 2, Category Set 3, Transaction Type, Minimum Absolute Amount, Supplier Name, PO Number, Destination Code, Item Number, Organization Code, Operating Unit, Ledger
Oracle EBS Tables Used
po_releases_all, mtl_parameters, xxen_report_org_access
Report Categories
Related Reports
CAC Invoice Price Variance, CAC Open Purchase Orders, CAC PO Receipt History for Item Costing, PO Invoice Price Variance, PO Receiving Transactions, ONT Blanket Sales Agreements, INV Kanban Cards, CAC Receiving Expense Value (Period-End), PO Receipts/Deliveries Discrepancy
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 | CAC Receiving Account Detail 03-Sep-2022 132129.xlsx |
| Blitz Report™ XML Import | CAC_Receiving_Account_Detail.xml |
| SQL Source | CAC Receiving Account Detail.sql |
| Full Report on Enginatics | www.enginatics.com/reports/cac-receiving-account-detail/ |
Case Study & Technical Analysis: CAC Receiving Account Detail
Executive Summary
The CAC Receiving Account Detail report is a forensic accounting tool for the Receiving module. It lists the specific accounting entries (Debits and Credits) generated by receiving transactions. This is critical for reconciling the “Receiving Inspection” (Asset) and “PO Accrual” (Liability) accounts.
Business Challenge
- Reconciliation: The General Ledger shows a balance in “Receiving Inspection” that doesn’t match the subledger. Which transaction caused the drift?
- Accrual Auditing: Verifying that the liability was booked correctly for a specific high-value receipt.
- SLA Visibility: In R12, Subledger Accounting (SLA) rules can transform accounts. Users need to see the final account used, not just the default from the PO.
Solution
This report bridges the gap between Operations and Finance.
- Granularity: One row per accounting line (Debit or Credit).
- Context: Links the journal line back to the PO Number, Receipt Number, Item, and Project.
- SLA Integration: Can optionally pull data from
xla_ae_linesto show the final transformation rules applied.
Technical Architecture
- Tables:
rcv_receiving_sub_ledger(Pre-SLA),xla_ae_lines(Post-SLA),rcv_transactions. - Logic: The “Show SLA Accounting” parameter toggles between the raw subledger table and the XLA engine tables.
Parameters
- Show SLA Accounting: (Mandatory) “Yes” for accurate GL matching, “No” for faster operational checks.
- Transaction Date From/To: (Mandatory) Accounting period.
- Minimum Absolute Amount: (Optional) Filter out small rounding differences.
Performance
- Variable: Running with “Show SLA Accounting = Yes” is slower because it joins to the massive XLA tables.
FAQ
Q: Why are there two entries for one receipt? A: A standard receipt generates a Debit to Receiving Inspection and a Credit to the Accrual Account. Q: Does this show “Deliver” transactions? A: Yes, it shows the full flow: Receive (Dr Insp / Cr Accrual) -> Deliver (Dr Inv / Cr Insp).
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