CAC Missing Receiving Accounting Transactions – Oracle EBS SQL Report

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

Overview

Report to find receiving transactions where the receiving accounting entries do not exist, for PO receipts into receiving and for returns from inventory and outside processing (work in process). To get all transactions which are missing receiving accounting entries, even for transactions where the transaction amounts are too small, set the Minimum Transaction Amount to zero (0). And note the quantities and purchase order unit prices are expressed in the item’s primary unit of measure.

Note: To find missing purchase order deliveries into inventory or work in process, use the CAC Missing Material Transactions and the CAC Missing WIP Transactions reports.

/* +=============================================================================+ – | Copyright 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. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: find_missing_receiving_accounting_entries.sql – | – | Parameters: – | p_trx_date_from – Starting transaction date, mandatory – | p_trx_date_to – Ending transaction date, mandatory – | p_minimum_amount – The absolute smallest transaction amount to be reported – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | p_item_number – Specific item number you wish to report (optional) – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | – | Description: – | Report to find receiving transactions where the receiving accounting entries do not – | exist. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 14 Jul 2022 Douglas Volz Initial Coding – | 1.1 15 Jul 2022 Douglas Volz Added PO number, PO Line, decode on receipt – | quantity. – | 1.2 18 Jul 2022 Douglas Volz Correct logic for CORRECT transaction types. – | 1.3 23 Jul 2022 Douglas Volz Added Parent Transaction Type to report. – | Additional exclusions for CORRECT transactions. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Minimum Transaction Amount, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

org_acct_periods, rcv_transactions, fnd_lookup_values, po_lines_all, po_headers_all, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_txn_source_types, mtl_parameters, fnd_common_lookups, fnd_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, rcv_receiving_sub_ledger, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Receiving Value (Period-End), GL Account Analysis, CAC Recost Cost of Goods Sold, CAC Purchase Price Variance, CAC Deferred COGS Out-of-Balance, GL Account Analysis (Distributions), CAC Manufacturing Variance, CAC Receiving Expense Value (Period-End), GL Account Distribution Analysis

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 CAC Missing Receiving Accounting Transactions 23-Jul-2022 171912.xlsx
Blitz Report™ XML Import CAC_Missing_Receiving_Accounting_Transactions.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-missing-receiving-accounting-transactions/

Case Study & Technical Analysis: CAC Missing Receiving Accounting Transactions

Executive Summary

The CAC Missing Receiving Accounting Transactions report is the Receiving equivalent of the Material Missing Accounting report. It identifies Receiving transactions (Receipts, Deliveries, Returns) that have been processed but failed to generate the necessary accounting entries in the Receiving Subledger (rcv_receiving_sub_ledger).

Business Challenge

Solution

This report identifies the orphans.

Technical Architecture

Parameters

Performance

FAQ

Q: Why does this happen? A: Often due to the “Receiving Transaction Processor” failing or the “Create Accounting - Receiving” program not picking up the event.

Q: Does it include “Correct” transactions? A: Yes, corrections should generate accounting (reversing the original entry), so they are checked as well.

© 2026 Enginatics