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™.

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. – +=============================================================================+*/

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_transaction_types, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, org_acct_periods, cst_cost_elements, gl_code_combinations_kfv, fnd_user, mfg_lookups, fnd_common_lookups, fnd_lookups, mtl_material_transactions, mtl_transaction_lot_numbers, mtl_generic_dispositions, inv_organizations, mtl_transaction_accounts, xla_distribution_links, xla_ae_headers, xla_ae_lines

Report Categories

Enginatics

CAC ICP PII Material Account Detail, CAC Material Account Detail, INV Material Account Distribution Detail, CAC Manufacturing Variance, CAC WIP Account Detail, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Deferred COGS Out-of-Balance, CAC ICP PII Inventory Pending Cost Adjustment

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 None
Blitz Report™ XML Import CAC_Material_Account_Alias_with_Lot_Numbers.xml
Full SQL 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.

© 2026 Enginatics