INV Item Cross Reference Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Creates, updates and deletes item cross references - the Cross Reference Types form (Inventory > Setup > Items > Cross-Reference Types), also reached from the Item form via Tools > Cross References.

A cross reference is either organization independent (Applicable To All Organizations is set and Org is blank) or specific to one organization (Org is entered). The organization must be one the item is assigned to.

Revision and Unit of Measure only apply to the GTIN cross reference type named by the profile option INV: GTIN Cross Reference Type, and Revision additionally requires an organization. Entering them for any other type is rejected.

Descriptions are translatable and are downloaded in the session language.

To remove a cross reference, enter Yes in Delete Cross Reference on a downloaded row.

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

Upload Mode, Item, Cross Reference Type, Cross Reference, Organization Code

Oracle EBS Tables Used

mtl_cross_references_vl, mtl_system_items_vl, mtl_parameters, mtl_item_revisions_b, org_access_view

Report Categories

Enginatics, R12 only, Upload

Related Reports

CAC Receiving Value (Period-End), CAC Calculate ICP PII Item Costs by Where Used, CAC Where Used by Cost Type, INV Item Upload, INV Item User Defined Attribute Upload, CAC WIP Jobs With Complete Status But Not Ready for Close, CAC WIP Jobs With Complete Status Which Are Ready for Close, CAC Inventory to G/L Reconciliation (Restricted by Org Access)

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_Cross_Reference_Upload.xml
SQL Source	INV Item Cross Reference Upload.sql
Full Report on Enginatics	www.enginatics.com/reports/inv-item-cross-reference-upload/

Case Study & Technical Analysis: INV Item Cross Reference Upload

Executive Summary

INV Item Cross Reference Upload maintains item cross references in Oracle Inventory, the alternate identifiers that link an item to a customer item number, a supplier item number, a legacy part number or a GTIN barcode. It creates, updates and deletes cross references in bulk through the Cross Reference Types setup, which is also reached from the Item form. Inventory and product data teams use it when loading customer or supplier part catalogs, migrating legacy numbering, or maintaining the GTIN values that trading partners use on orders and labels.

Business Challenge

• Partner Catalogs: Customers and suppliers identify items by their own part numbers, and each mapping must be recorded against the correct item.
• Organization Scope: A cross reference can apply to all organizations or only to one, and an item can be assigned to only some organizations.
• GTIN Rules: Barcode references carry revision and unit of measure values that are valid only for the GTIN cross reference type, so incorrect entries must be prevented.
• Maintenance Effort: Thousands of mappings created by hand through the Item form are slow and error prone.

The Solution

• Item Based Rows: Each row names the item, its master organization, the cross reference type, the reference value and the description, so the mapping is complete on one line.
• Scope Control: The Applicable To All Organizations flag and the Organization Code column together set whether a reference is global or organization specific.
• Type Specific Attributes: Revision and unit of measure are accepted only for the GTIN type, which the description states is defined by the INV: GTIN Cross Reference Type profile option.
• Delete Option: A delete column removes a downloaded cross reference when set to Yes.

Technical Architecture (High Level)

Primary Tables

• MTL_CROSS_REFERENCES_VL: the cross references with their type, reference value, description, unit of measure and descriptive flexfield attributes.
• MTL_SYSTEM_ITEMS_VL: the items, with their concatenated segments and descriptions.
• MTL_PARAMETERS: the organizations, used to identify the item master organization and the cross reference organization.
• MTL_ITEM_REVISIONS_B: the item revisions referenced by a GTIN cross reference.
• ORG_ACCESS_VIEW: the organizations the current responsibility can access.

Logical Relationships

1. Each cross reference in MTL_CROSS_REFERENCES_VL is joined to its item in MTL_SYSTEM_ITEMS_VL on the inventory item identifier.
2. The item’s organization is joined to MTL_PARAMETERS and restricted to the master organization.
3. The cross reference’s own organization is outer joined to MTL_PARAMETERS, so organization independent references return no organization code.
4. The revision is outer joined to MTL_ITEM_REVISIONS_B on the revision identifier.
5. Rows are limited to items in organizations returned by ORG_ACCESS_VIEW for the current responsibility.

Parameters & Filtering

• Upload Mode: Selects Create, or Create and Update, which controls whether existing cross references are downloaded.
• Item: Restricts the cross references to one item.
• Cross Reference Type: Restricts the output to one cross reference type.
• Cross Reference: Matches the reference value itself, for example a customer part number.
• Organization Code: Restricts the output to references in one organization.

Performance & Optimization

• Item Driven Joins: Cross references join to the item view on a single identifier, so the item attributes are fetched once per reference.
• Responsibility Scope Filter: The organization restriction uses the access view, so the extract covers only the organizations the user is allowed to maintain.
• Outer Joins for Scope: Organization and revision joins are outer joins, so organization independent references and references without revisions are still returned.
• Flexfield Expansion: Descriptive flexfield values are expanded through the flexfield value function, which decodes segment values for display.

FAQ

Q: How do I create a cross reference that applies to all organizations? A: Leave the Org column blank and set the Applicable To All Organizations flag to Yes for the row.

Q: Why is my revision rejected for a customer cross reference? A: Revision and unit of measure apply only to the GTIN cross reference type. Entering them for any other type is rejected.

Q: How do I remove a cross reference? A: Download the reference, enter Yes in the Delete Cross Reference column for that row, and upload the template.

---

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