CAC Material Account Alias with Lot Numbers – 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 display the material account alias transactions by lot number. Specify Yes for Show Lot Number to split out the transaction quantities and amounts by transaction lot number. And if processed by Create Accounting, the Create Accounting column shows “Yes”.

Parameters:

Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show Lot Number: enter Yes to see transactions by lot number, enter No to exclude lot information (mandatory). Account Alias: enter the account alias to report (optional). Category Set 1, 2, 3: any item category you wish (optional). Item Number: enter the item numbers 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-2025 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 06 Nov 2009 Douglas Volz Initial Coding – | 1.1 11 Nov 2009 Douglas Volz Added Org Code and transaction ID – | 1.2 12 Nov 2009 Douglas Volz Added item and description – | 1.3 06 Jan 2010 Douglas Volz Made dates a parameter – | 1.4 12 Jan 2010 Douglas Volz Added quantity and unit cost columns – | 1.5 12 Jan 2010 Douglas Volz Added account alias information – | 1.6 20 Jun 2010 Douglas Volz Added created by information and fixed sort – | 1.7 27 Jun 2010 Douglas Volz Fixed column label for user name, added Ledger parameter – | 1.8 16 Jul 2010 Douglas Volz Added primary unit of measure (UOM), reason – | code and transaction reference (comments) and added lot number – | 1.9 06 Feb 2012 Douglas Volz Rewrite SQL report to solve cross-joining problem – | with having multiple lot numbers per material transaction and multiple material overheads – | 1.10 22 Jun 2015 Douglas Volz Added back comments to this code, removed client-specific SLA rules – | 1.11 17 May 2017 Douglas Volz Added category sets – | 1.12 25 Mar 2025 Douglas Volz Cleaned up code for Blitz Report and added Create Accounting Y/N column. – | 1.13 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

Transaction Date From, Transaction Date To, Show Lot Number, Inventory Account Alias, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_transaction_reasons, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, mtl_material_transactions, mtl_generic_dispositions, mtl_transaction_lot_numbers, mtl_transaction_types, mtl_system_items_vl, org_acct_periods, mtl_transaction_accounts, xla_distribution_links, xla_ae_lines, cst_cost_elements, gl_code_combinations_kfv, xxen_report_org_access

Report Categories

Enginatics, R12 only

INV Material Transactions, CAC Recost Cost of Goods Sold, INV Material Account Distribution Detail, GL Account Analysis, CAC Purchase Price Variance, CAC Internal Order Shipment Margin, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, GL Account Analysis (Drilldown) (with inventory and WIP), 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_Material_Account_Alias_with_Lot_Numbers.xml
SQL Source CAC Material Account Alias with Lot Numbers.sql
Full Report on Enginatics www.enginatics.com/reports/cac-material-account-alias-with-lot-numbers/

Case Study & Technical Analysis: CAC Material Account Alias with Lot Numbers

Executive Summary

The CAC Material Account Alias with Lot Numbers report is a compliance and traceability tool. It focuses on “Account Alias” transactions—manual inventory adjustments where the user selects a GL account alias (e.g., “Scrap”, “R&D Issue”, “Inventory Adjustment”). Crucially, this report includes Lot Number details, which are often missing from standard GL reports.

Business Challenge

Solution

This report provides a detailed audit trail.

Technical Architecture

The report joins the transaction history to the lot transaction table:

Parameters

Performance

FAQ

Q: What if the item is not lot controlled? A: The report will still show the transaction, but the Lot Number column will be blank (or the row will not split, depending on the join type).

Q: Does this show “Account Alias Receipts” too? A: Yes, it shows both Issues (negative qty) and Receipts (positive qty) performed via the Account Alias screen.

Q: Can I see who performed the transaction? A: Yes, the report typically includes the “Created By” user, which is essential for auditing manual adjustments.

© Enginatics GmbH