Library of Oracle EBS SQLs for operational Excel and ECC reporting, Discoverer replacement, Oracle EBS performance tuning and rapid report development.

View the Project on GitHub Enginatics/Oracle-EBS-SQL

CAC Receiving Value (Period-End)


Report to show receiving value for all locations, as of the end of an accounting period. You may run this report for open or closed accounting periods. With parameters to display or not display WIP outside processing information (WIP job, status, close date and OSP resource code). If run for a prior period the report automatically rolls back the quantities and values to the prior period’s period end date. In addition, for any receipt with an item number, this report displays Inventory, Shop Floor (Outside Processing) and Expense destination types.

Reconciliation Notes to Oracle Reports: The Oracle All Inventory Values Report does not display Shop Floor (Outside Processing) or Expense destination types.
If the PO line is closed, the Oracle Receiving Value Report does not display Expense destination types, even if the receipt is still in receiving and has not been delivered.

Parameters: Period Name: the accounting period you wish to report (mandatory). Show WIP Outside Processing: display WIP job details for outside processing, enter Yes or No (mandatory). Category Set 1: any item category you wish (optional). Category Set 2: any item category you wish (optional). Item Number: specific item you wish to report (optional) Organization Code: specific inventory organization to report (optional) Operating Unit: specific operating unit (optional) Ledger: specific ledger (optional) Product Line Accounting: use this parameter to tell the report if you have set up product line accounting information in your item master accounts. Enter Yes or No. Product Line Segment: use this parameter to tell the report which segment to use to find the product line information. If the Product Line Accounting parameter is set to No this parameter is grayed out and not required. Item Master Account Type: use this parameter to determine if you store your product line information in your item master Expense, Cost of Sales or Sales Accounts. If the Product Line Accounting parameter is set to No this parameter is grayed out and not required.

/* +=============================================================================+ – | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. – | All rights reserved. – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 25 Nov 2009 Douglas Volz Created initial Report for client – | based on xxx_inv_value.sql – | 1.32 15 Jan 2022 Douglas Volz Modify joins to rcv_accounting_events, was – | cross-joining giving poor results. Added parameter – | conditions for p_org_code. – | 1.33 24 Jul 2022 Douglas Volz Multi-language changes for column headings, item – | status and uom code. Remove organization name. – | 1.34 26 Jul 2022 Douglas Volz Post Close Section, add Clearing accounting line type – | to queries and fix cross joining to po distributions. – | 1.35 02 Aug 2022 Douglas Volz Replace hard-code tree-walk with connect by statements and – | add dynamic product line accounts from the item master. – | 1.36 10 Aug 2022 Douglas Volz Performance improvements for main query. Allow expense – | receipts with items, add PO line status column. – | 1.37 12 Aug 2022 Douglas Volz Add parameter to Show WIP OSP columns and remove extra – | join to the item master table. – | 1.38 16 Aug 2022 Douglas Volz Fixes for quantities for Correction transactions and – | for when the PO unit price is zero. – +=============================================================================+*/


Period Name, Show WIP Outside Processing, Category Set 1, Category Set 2, Item Number, Organization Code, Operating Unit, Ledger, Product Line Accounting, Item Master Account Type, Product Line Segment

Used tables

po_requisition_headers_all, po_requisition_lines_all, hr_employees, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, rcv_parameters, mtl_system_items_vl, org_access_view, gl_code_combinations, fnd_lookups, po_headers_all, org_acct_periods, mfg_lookups, fnd_common_lookups, po_lookup_codes, po_lines_all, po_line_locations_all, po_releases_all, po_vendors, pa_projects_all, hr_organization_information, hr_all_organization_units, gl_ledgers, rcv_transactions, rcv_shipment_headers, mtl_supply, rcv_accounting_events, rcv_shipment_lines, po_distributions_all, rcv_receiving_sub_ledger




If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.

Example Report

CAC Receiving Value Period End - Pivot by Org 11-Aug-2022 151851.xlsx

Report SQL

Blitz Report™ import options


Oracle E-Business Suite Reporting Library

We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.

Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.

Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.

The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.

Blitz Report Pivots

The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.

Blitz Report Dashboard

You can download and use Blitz Report free of charge for your first 30 reports.

The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.

If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.

CRM FoundationContracts Core
Lease and Finance ManagementPayments
Process ManufacturingProcess Manufacturing Inventory
Public Sector FinancialsShipping Execution
XML PublisherAdvanced Pricing
CapacityLegal Entity Configurator
ProjectsService Contracts
Subledger AccountingApplications DBA
Enterprise Asset ManagementInstalled Base
E-Business TaxEnterprise Command Center
Advanced Supply Chain PlanningProperty Manager
Bills of MaterialMaster Scheduling/MRP
Work in ProcessHuman Resources
Order ManagementCash Management
Cost ManagementDiscoverer
PayablesGeneral Ledger
Blitz ReportDatabase Administration
Application Object LibraryCost Accounting

Useful Links

Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Oracle Discoverer replacement – importing worksheets into Blitz Report™

Blitz Report™ Toolkits

Supply Chain Hub by Blitz Report™

Blitz Report™ customers

Oracle EBS Reporting Blog

Oracle EBS Reporting

© 2024 Enginatics