INV Item Category Assignment – 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
Imported from BI Publisher Description: Item categories report Application: Inventory Source: Item categories report (XML) Short Name: INVIRCAT_XML DB package: INV_INVIRCAT_XMLP_PKG
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
Category Set, Organization Code, Category From, Category To, Item From, Item To, Cat. Set Control Level Violated
Oracle EBS Tables Used
mtl_categories_kfv, mtl_category_set_valid_cats, mtl_categories_b, mtl_parameters, mtl_item_categories, mtl_system_items_b, mtl_system_items_vl, mtl_category_sets_v, mtl_categories_v
Report Categories
Related Reports
AP Invoice Upload, INV Item Upload 11i, MRP Sourcing Rules and Bills of Distribution, CAC New Standard Item Costs, CAC Last Standard Item Cost
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 | INV_Item_Category_Assignment.xml |
| SQL Source | INV Item Category Assignment.sql |
| Full Report on Enginatics | www.enginatics.com/reports/inv-item-category-assignment/ |
INV Item Category Assignment - Case Study & Technical Analysis
Executive Summary
The INV Item Category Assignment report provides a detailed listing of which items are assigned to which categories within a specific Category Set. Since an item can belong to multiple category sets (e.g., one for Sales, one for Purchasing, one for Planning), this report allows users to view the specific assignments for a targeted purpose.
Business Use Cases
- Catalog Management: Used to review and clean up item classifications (e.g., “Show me all items in the ‘Electronics’ category”).
- New Item Setup Verification: Ensures that new items have been assigned to the correct categories, which often drives account derivation and reporting.
- Sourcing Analysis: Procurement teams use this to group items by purchasing category to analyze spend.
Technical Analysis
Core Tables
MTL_ITEM_CATEGORIES: The intersection table linking Items to Categories.MTL_CATEGORIES_B: The category definitions.MTL_CATEGORY_SETS_B: The category set definitions.MTL_SYSTEM_ITEMS_B: The item master.
Key Joins & Logic
- Category Set Context: The query MUST filter by
CATEGORY_SET_ID. Without this, the results would be a meaningless mix of different classification schemes. - Organization Context: Category assignments can be Master Level (same for all orgs) or Org Level (specific to an org). The report respects this control level.
Key Parameters
- Category Set: The specific classification scheme to report on (Mandatory).
- Category From/To: Range of categories to include.
- Item From/To: Range of items.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH