Skip to the content.

INV Items – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Inventory · all Inventory reports

Overview

Master data report that lists item master attributes such as item type, UOM, status, serial control, account numbers and various other attributes.

Item Cost is the item’s cost in the cost type of the organization’s primary cost method. Item Cost and List Price are in the organization’s ledger Currency.

Sourcing Supplier is the supplier and site that Oracle’s sourcing routine selects from the Sourcing Assignment Set: the most specific assignment (item and organization, item, category, organization, global) first, then the highest allocation percent and the lowest rank, skipping suppliers the approved supplier list does not allow for sourcing. The site is the one the sourcing rule names, which can belong to a different operating unit from the organization’s.

Show Manufacturer Part Numbers lists an item once per manufacturer part number, and Show Item Cross References once per active cross reference that applies to its organization, i.e. one defined for all organizations or for that organization. With both, the item is listed once per combination.

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

Organization Code, Item, Item Description, Item Type, BOM Item Type, Exclude Item Type, Contract Item Type, Item Status, Buyer, Planner, Category Set 1, Category Set 2, Category Set 3, Category Set, Category, Last Update Date From, Last Update Date To, Last Updated By, Creation Date From, Creation Date To, Created By, Cross Reference Type, Cross Reference, Show Item Cross References, Manufacturer Part Number, Show Manufacturer Part Numbers, Master only, Sourcing Assignment Set, DFF Display

Oracle EBS Tables Used

mtl_item_catalog_groups_b_kfv, mtl_item_status_vl, mtl_units_of_measure_tl, mtl_actions, mtl_grades, mtl_material_statuses, mtl_system_items_vl, gl_code_combinations_kfv, cst_item_costs, per_people_x, mrp_item_sourcing_levels_v, ap_suppliers, mrp_assignment_sets, po_approved_supplier_list, po_asl_status_rules, ap_supplier_sites_all, org_organization_definitions, gl_ledgers, po_un_numbers, po_hazard_classes, fa_categories_b_kfv, rcv_routing_headers, mtl_planners, mtl_parameters, mtl_item_locations_kfv, mtl_atp_rules, wms_rules_vl, mtl_uom_conversions, ra_rules, ra_terms_vl, oks_coverage_templts_v, csi_item_instances, hr_all_organization_units_vl, cs_ctr_associations, cs_counter_groups, xxen_report_org_access

Report Categories

Enginatics

INV Item Upload, INV Item Upload 11i, GL Account Analysis, GL Account Distribution Analysis, INV Onhand Quantities, GL Account Analysis (Drilldown) (with inventory and WIP), PO Headers and Lines

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 INV Items 23-Jul-2025 025320.xlsx
Blitz Report™ XML Import INV_Items.xml
SQL Source INV Items.sql
Full Report on Enginatics www.enginatics.com/reports/inv-items/

Case Study & Technical Analysis: INV Items Report

Executive Summary

The INV Items report is a comprehensive master data extraction tool that provides a detailed, attribute-level view of the entire Oracle E-Business Suite item master. It serves as the primary report for auditing, analyzing, and exporting item information for business intelligence, reporting, and as a data source for mass update activities. With its extensive filtering capabilities, it empowers users to quickly access the specific item data they need in a clean, Excel-ready format.

Business Challenge

Accessing and analyzing item master data directly within Oracle EBS presents significant hurdles for business users and data analysts. The standard forms-based interface is not designed for mass data review or extraction. Key challenges include:

The Solution

The INV Items report provides a fast, flexible, and direct solution to these challenges, making item master data accessible to all users.

Technical Architecture (High Level)

The report is built on a direct, optimized SQL query that joins the core item master tables to provide a rich, detailed dataset.

Parameters & Filtering

The report’s strength lies in its wide array of parameters that allow for highly specific data extraction:

Performance & Optimization

The report is engineered for speed and efficiency, even when extracting large volumes of data.

FAQ

1. How does this report differ from the standard Oracle ‘Item Definition Detail’ report? While both reports show item details, this INV Items report is designed for mass data extraction and analysis with its extensive filtering and direct-to-Excel output. The standard Oracle report is typically run for a single item or a small range and is not optimized for exporting and analyzing thousands of records at once.

2. Can I add our company’s specific item attributes (Descriptive Flexfields) to the report? Yes. The report includes a parameter, “Show DFF Attributes,” which, when enabled, will dynamically include the Descriptive Flexfield columns in the output, making it easy to report on your company-specific data.

3. Is it possible to use the output of this report to perform a mass update? Absolutely. This report is the perfect companion to the INV Item Upload tool. You can use this report to filter and download the specific items you want to update, make your changes in the resulting Excel file, and then use that file as the input for the upload tool.


© Enginatics GmbH