CAC Missing Receiving Accounting Transactions – 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 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. – +=============================================================================+*/

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, 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, CAC Manufacturing Variance, CAC Receiving Expense Value (Period-End), GL Account Distribution Analysis, CAC Receiving Account Summary

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 Missing Receiving Accounting Transactions 23-Jul-2022 171912.xlsx
Blitz Report™ XML Import CAC_Missing_Receiving_Accounting_Transactions.xml
SQL Source CAC Missing Receiving Accounting Transactions.sql
Full Report 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.

© Enginatics GmbH