| layout | default |
|---|---|
| title | CAC Receiving Account Summary | Oracle EBS SQL Report |
| description | Report to get the receiving accounting distributions, in summary, by item, purchase order, purchase order line, release and project number. With the Show… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, Nidec changes, R12 only, CAC, Receiving, Account, Summary, mtl_units_of_measure_vl, org_acct_periods, gl_code_combinations |
| permalink | /CAC%20Receiving%20Account%20Summary/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Report to get the receiving accounting distributions, in summary, by item, purchase order, purchase order line, release and project number. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. With parameters to limit the report size, to display or not display purchase information (purchase order, line, release) and WIP outside processing information (WIP job and OSP resource code). And if you accrue expense receipts at time of receipt, for expense destinations when there is no item number, this report will get the expense category information and put it into the columns for the first category set.
(Note: this report has not been tested with encumbrance entries.)
Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries. Show Purchase Orders: display the purchase order, line and release information. Enter Yes or No, use to limit the report size (mandatory). Show Projects: display the project number and name. Enter Yes or No, use to limit the report size (mandatory). Show WIP Outside Processing: display the WIP job and outside processing resource. Enter Yes or No, use to limit the report size (mandatory). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Supplier Name: enter the specific supplier you wish to report (optional). PO Number: enter the specific purchase order number you wish to report (optional). Destination Code: enter the purchase order destination type you wish to report (optional). You can choose Inventory, Expense or Shop Floor (WIP outside processing). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).
/* +=============================================================================+ -- | Copyright 2009- 2022 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this -- | permission.
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- +=============================================================================+*/ |
Transaction Date From, Transaction Date To, Show SLA Accounting, Show Purchase Orders, Show Projects, Show WIP Outside Processing, Category Set 1, Category Set 2, Category Set 3, Supplier Name, PO Number, Destination Code, Item Number, Organization Code, Operating Unit, Ledger
mtl_units_of_measure_vl, org_acct_periods, gl_code_combinations, fnd_lookup_values, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, po_lookup_codes, &wip_osp_tables, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
Enginatics, Nidec changes, R12 only
CAC Receiving Account Detail, CAC Receiving Value (Period-End), CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory Pending Cost Adjustment - No Currencies, CAC ICP PII Material Account Detail, CAC ICP PII Material Account Summary, CAC Recost Cost of Goods Sold, CAC WIP Resource Efficiency
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.
| Resource | Link |
|---|---|
| Excel Example Output | CAC Receiving Account Summary 03-Sep-2022 111842.xlsx |
| Blitz Report™ XML Import | CAC_Receiving_Account_Summary.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/cac-receiving-account-summary/ |
The CAC Receiving Account Summary report is a specialized accounting tool for the "Receiving" subledger. While the "Material Account Summary" covers inventory movements, this report focuses specifically on the accrual and clearing activities that happen at the dock door. It bridges the gap between the Purchase Order (PO) and the General Ledger (GL), providing a summarized view of:
- Accruals: The liability recorded when goods are received but not yet invoiced (Receipt Accruals).
- Clearing: The reversal of that liability when the AP Invoice is matched.
- Inspection/Delivery: The movement of value from "Receiving Inspection" to "Inventory" or "Expense".
The "Receiving" stage is a financial limbo. Goods are physically present but often not yet legally owned or invoiced.
- The "Accrual" Black Hole: At month-end, Finance needs to know exactly what is sitting in the "Receiving Inventory" (or "Accrual") account. If this account doesn't reconcile, it usually means receipts haven't been delivered or invoices haven't been matched.
- Expense vs. Inventory: Receipts for expense items (like office supplies) often bypass inventory but still generate accounting entries. Tracking these "Expense Destination" receipts is crucial for departmental budget analysis.
- SLA Complexity: As with other subledgers, R12 Subledger Accounting (SLA) can transform the raw receiving accounts (e.g., changing a cost center based on the project code). Reporting on the raw data (
RCV_RECEIVING_SUB_LEDGER) might not match the GL.
This report provides a flexible, summarized view of receiving activity.
- Dual-Mode Architecture:
- SLA Mode: Joins to
XLA_DISTRIBUTION_LINKSto show the final, transformed accounting entries that hit the GL. - Legacy Mode: Queries
RCV_RECEIVING_SUB_LEDGERdirectly for a faster, operational view of the receiving transactions.
- SLA Mode: Joins to
- Expense Handling: It has special logic to handle "Description-based" POs (where there is no Item Number). In these cases, it pulls the "Expense Category" to categorize the spend, ensuring that even non-stock purchases are reportable.
- Granularity Control: Users can toggle "Show Purchase Orders," "Show Projects," and "Show WIP" to switch between a high-level GL summary and a detailed transaction register.
The query relies on a dynamic FROM clause (likely hidden in the &subledger_tab or similar variable in the full code, though the snippet shows the outer select) to switch between data sources.
- Core Data Source:
- Non-SLA:
RCV_RECEIVING_SUB_LEDGER(RRSL) is the primary source. It linksRCV_TRANSACTIONStoGL_CODE_COMBINATIONS. - SLA: The query likely joins
RCV_TRANSACTIONS->RCV_RECEIVING_SUB_LEDGER->XLA_DISTRIBUTION_LINKS->XLA_AE_LINES.
- Non-SLA:
- Dynamic Grouping: The
GROUP BYclause changes based on the user's parameters. IfShow Purchase Ordersis 'No', the query aggregates all receipts for an item/account into a single line, significantly reducing row count. - Lookup Decoding: Extensive use of
FND_LOOKUP_VALUESandPO_LOOKUP_CODESensures that cryptic codes like "DELIVER" or "RECEIVE" are translated into user-friendly terms like "Delivery to Inventory" or "Standard Receipt".
- Show SLA Accounting: The critical switch for GL reconciliation.
- Destination Code: Allows filtering for "Expense" (Direct to GL), "Inventory" (Stock), or "Shop Floor" (Outside Processing).
- Show Purchase Orders/Projects/WIP: Toggles for detail level.
- Transaction Date From/To: Defines the accounting period.
- Summary by Default: By defaulting to a summary view (grouping by Account/Item), the report is much faster than a standard "Receiving Transaction Register" which lists every single receipt line.
- Conditional Joins: The dynamic SQL ensures that tables like
PO_HEADERS_ALLorPA_PROJECTS_ALLare only joined if the user explicitly asks for that data.
Q: Why is the "Amount" column sometimes zero? A: In Receiving, you often have offsetting entries. For example, a "Delivery" transaction credits Receiving Inspection and debits Inventory. If you summarize by Item (and the accounts are the same, which is rare but possible), they might net out. More likely, you are seeing the net activity for a period.
Q: Can I use this to reconcile the "AP Accrual" account? A: Yes. Filter for the Accrual Account and look at the net activity. However, the "Accrual Reconciliation Report" is a more specialized tool for matching individual PO receipts to Invoices.
Q: What does "Shop Floor" destination mean? A: This refers to Outside Processing (OSP). When you receive an OSP item, you are receiving a service directly to a WIP Job, not a physical item into a warehouse. The accounting debits WIP Valuation, not Inventory.
- 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
© 2026 Enginatics