CAC Receiving Expense Value (Period-End) – 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

Report to show receiving value for expense locations and expense destination types, for expenses accrued at time of receipt. You may run this report for open or closed accounting periods. If run for a prior period the report automatically rolls back the quantities and values to the prior period’s period end date. if run for a current period, the report shows the real-time quantities and values. This report displays both the receiving valuation account as well as the offset (expense or CapEx) account.

Parameters: Period Name: the accounting period you wish to report (mandatory). Category Sets 1 - 3: any item category you wish (optional) Supplier Name: enter a supplier name to report (optional) Item Number: enter an item number to report (optional) Organization Code: specific inventory organization to report (optional) Operating Unit: specific operating unit (optional) Ledger: specific ledger (optional)

/* +=============================================================================+ – | Copyright 2010-25 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. – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 5 Jul 2010 Douglas Volz Created initial Report for Celgene – | based on XXX_IPV_RCV_VALUE_REPT.sql – | 1.1 5 Jul 2010 Douglas Volz Added requisition, requestor and requestor – | email address – | 1.2 15 Sep 2010 Douglas Volz Added unit of measure, supplier, buyer columns. – | Fix for parameter changes for BO Freehand SQL requirements, – | changing the code to accept null or % or value GL Ledger names. – | 1.3 27 Sep 2010 Douglas Volz Changed the report sort to include the offset accounts – | 1.4 04 Jan 2012 Douglas Volz Fixed quantities to sum up rcv_receiving_sub_ledger, – | as the view rcv_receiving_value_view and – | rcv_transactions does not split out quantities by – | po distributions. For expenses you can have multiple expense – | accounts or distributions for each scheduled receipt – | in po_line_locations – | 1.5 08 Feb 2012 Douglas Volz Rewrite code to fix quantity and amounts, mtl_supply does – | not handle split rcv_transations, split by multiple – | po_distribution_ids. It only stores one of the PODs for – | expenses. – | 1.6 06 Jan 2020 Douglas Volz Added item categories, Org Code and Operating Unit parameters. – | 1.7 29 May 2025 Douglas Volz Removed tabs, added Blitz G/L and OU security.. – | 1.8 15 Oct 2025 Douglas Volz Changed inventory periods to PO financial periods. – | Added non-recoverable tax amounts into PO prices. – | And changed Period Name parameter to use INV Periods – | which also include the master inventory org. – | 1.9 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+*/

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

Period Name, Category Set 1, Category Set 2, Category Set 3, Supplier Name, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_period_statuses, xxen_report_org_access, hr_employees, po_requisition_lines_all, po_requisition_headers_all, rcv_parameters, mtl_supply, rcv_transactions, rcv_shipment_headers, rcv_shipment_lines, po_distributions_all, po_line_locations_all, po_lines_all, po_headers_all, po_releases_all, rcv_receiving_sub_ledger, po_system_parameters_all, rcv_accounting_events, ap_suppliers, mtl_system_items_vl, pa_projects_all, gl_code_combinations

Report Categories

Enginatics

CAC Purchase Price Variance, CAC Receiving Value (Period-End), CAC Interface Error Summary, CAC Recost Cost of Goods Sold, GL Account Analysis, CAC PO Receipt History for Item Costing, INV Material Transactions, PO Internal Requisitions and Sales Orders, GL Account Distribution Analysis

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 Example
Blitz Report™ XML Import CAC_Receiving_Expense_Value_Period_End.xml
SQL Source CAC Receiving Expense Value (Period-End).sql
Full Report on Enginatics www.enginatics.com/reports/cac-receiving-expense-value-period-end/

Case Study & Technical Analysis: CAC Receiving Expense Value (Period-End)

Executive Summary

The CAC Receiving Expense Value (Period-End) report focuses on the valuation of “Expense” destination items. In Oracle EBS, you can configure expense items (like Office Supplies) to “Accrue on Receipt”. This means they hit the Balance Sheet (Receiving Inspection) upon receipt and only move to the Expense account upon Delivery. This report values that specific slice of inventory.

Business Challenge

Solution

This report provides “As Of” valuation.

Technical Architecture

Parameters

Performance

FAQ

Q: Why not just use the standard Receiving Value report? A: The standard report often excludes Expense destinations or mixes them in. This report isolates them for specific accrual reconciliation.

© Enginatics GmbH