Item Cross Reference/Customer Items/Customer Items Architecture/ Manufacturer Items/Item Relationships

Item Cross Reference

Item Cross-references:

Cross-reference types define relationships between items and entities such as old item numbers or supplier item numbers (not for customer and manufacturer parts). This helps in searching the inventory items based on such cross references.

Assign inventory items with various items of above cross reference type here.



Search Items for above cross reference type ie Venodr and for Item value Item1.We can get our inventory item Item1A , hence cross references can be used to search items effectively.


4.6.1 Item Cross-references Architecture
MTL_CROSS_REFERENCE_TYPES

CROSS_REFERENCE_TYPE  
DESCRIPTION           
DISABLE_DATE          
VALIDATE_FLAG                     
MTL_CROSS_REFERENCES

INVENTORY_ITEM_ID     
ORGANIZATION_ID       
CROSS_REFERENCE_TYPE 
CROSS_REFERENCE       
DESCRIPTION           
ORG_INDEPENDENT_FLAG 
UOM_CODE              
REVISION_ID                         
Query to find  item cross-references :

set lines 150
set pages 150
col item form a24
col reference_type form a16
col cross_reference form a24
col description form a28
select
msi.segment1 item,
mcr.cross_reference_type reference_type,
mcr.cross_reference,
mcr.description
from mtl_cross_references mcr,
     mtl_system_items msi
where mcr.cross_reference_type='Vendor'
and mcr.inventory_item_id=msi.inventory_item_id
and mcr.organization_id=msi.organization_id
order by 1,2
/


Customer Items 

A customer item defined at the Customer level is recognized across all address and address categories for that customer. If you ship an item to multiple customer ship-to sites that have been grouped as an address category, you can define the customer item for that address category. You would define a customer item at the address level if you ship the item to only one ship-to site for that customer.Assign the customer item to a Commodity Code (Comm. Codes Shown later).You can reference a  customer item as a Model by entering the inventory item number of an existing Model item. (Model Tab).Enter the default master and detail containers for this customer item as well as the minimum fill percent for the container. (Container Tab).Also check Required to indicate that items must be departure planned before they released and Before Build to indicate that ATO items must be departure planned before they are built.(Departure Tab).Use the Customer Item Cross References window to define and update cross references between your inventory items and the customer item numbers.

 Customer Items Architecture

 
MTL_CUSTOMER_ITEMS

CUSTOMER_ITEM_ID              
CUSTOMER_ID                   
CUSTOMER_CATEGORY_CODE        
ADDRESS_ID                    
CUSTOMER_ITEM_NUMBER          
ITEM_DEFINITION_LEVEL         
CUSTOMER_ITEM_DESC            
MODEL_CUSTOMER_ITEM_ID        
COMMODITY_CODE_ID             
MASTER_CONTAINER_ITEM_ID      
CONTAINER_ITEM_ORG_ID         
DETAIL_CONTAINER_ITEM_ID      
MIN_FILL_PERCENTAGE           
DEP_PLAN_REQUIRED_FLAG        
DEP_PLAN_PRIOR_BLD_FLAG       
INACTIVE_FLAG                 
DEMAND_TOLERANCE_POSITIVE     
DEMAND_TOLERANCE_NEGATIVE                   
MTL_CUSTOMER_ITEM_XREFS

CUSTOMER_ITEM_ID      
INVENTORY_ITEM_ID     
MASTER_ORGANIZATION_ID
PREFERENCE_NUMBER     
INACTIVE_FLAG         
Query to find Customer items :

set lines 150
set pages 150
col item form a24
col item_desc form a40
col customer_item_desc form a40
col customer_item_number form a18
col item_definition_level form a12
col customer form a24
select
   hp.party_name customer,
   ci.customer_item_number,
   ci.customer_item_desc,
   msi.segment1 item,
   msi.description item_desc,
   ci.customer_category_code,
   ci.item_definition_level,
   ci.commodity_code_id,
   ci.address_id
from
hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
where ci.customer_item_id=ix.customer_item_id
and ix.inventory_item_id=msi.inventory_item_id
and ix.master_organization_id=msi.organization_id
and ci.customer_id=hca.cust_account_id
and hca.party_id=hp.party_id
order by 1,2
/
  Manufacturer Items 


If u define manufacturer part numbers for items, you can use this information for reporting purposes and in catalog searches for such items.

MTL_MANUFACTURERS

 MANUFACTURER_ID       
 MANUFACTURER_NAME    
 DESCRIPTION           
MTL_MFG_PART_NUMBERS

MANUFACTURER_ID       
MFG_PART_NUM          
INVENTORY_ITEM_ID     
ORGANIZATION_ID       
MRP_PLANNING_CODE     
DESCRIPTION           
FIRST_ARTICLE_STATUS  
APPROVAL_STATUS       
Query to find Manufacturer items :
set lines 150
set pages 150
col manufacturer_name form a24
col description form a24
col mfg_part_num  form a24
col inv_item  form a24
col item_desc   form a50

select
mm.manufacturer_name,
   mp.mfg_part_num,
   mp.description,
   msi.segment1 inv_item,
   msi.description item_desc
from
 mtl_system_items msi,
 mtl_mfg_part_numbers mp,
 mtl_manufacturers mm
where mm.manufacturer_id=mp.manufacturer_id
and   mp.inventory_item_id=msi.inventory_item_id
and   mp.organization_id=msi.organization_id
order by 1,2
/


Item Relationships


You can define relationships between items. This allows you to search for items through these relationships. Within Oracle Purchasing you can define acceptable substitute items for receiving. You must define a list of substitutes before you receive a substitute item in place of an originally ordered item. In OM, u can see the related items while ordering and cross-sell the related items.


Item Relationships Architecture
MTL_RELATED_ITEMS

INVENTORY_ITEM_ID             
ORGANIZATION_ID               
RELATED_ITEM_ID               
RELATIONSHIP_TYPE_ID          
RECIPROCAL_FLAG               
PLANNING_ENABLED_FLAG        
START_DATE
END_DATE      
Query to find related items :
set lines 150
set pages 150
col item form a24
col description form a40
col related_item form a24
col relation form a24

select
ito.segment1 item,
ito.description,
itr.segment1 related_item,
itr.description,
ml.meaning relation,
ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
and ml.lookup_type(+)='MTL_RELATIONSHIP_TYPES'
order by 1,2
/

No comments:

Topics Covered

Purchasing Concurrent program INVENTORY MODULE Inventory Concepts Min-Max Puchasing Sales Order Supply Chain Management Xml Publisher dff Alert Creation Assembly Pull Assigning Approval Groups Assigning Item BOM Tables Backend Tables Base Tables Bills of Material Tables CATALOG TABLES CATEGORY TABLES Closed not Summarized Concurrent Programs Concurrent Request Consigned and VM Inventory Count (*) Customer Items Customer Items Architecture DDL DML Defining Line Type Defining Subinventories Drop Ship EBS EVEN NUMBERED Error: - APP-PER-50022 FND Tables FRM-40735 Fetch Item Information Flexfields Functional GENERIC DISPOSITIONS Hosting ITEM LOCATIONS TABLE Idle Session Timeout Important questions Introduction to Database Invalid Identifier Error Inventory Inventory Implementation Inventory Item master Inventory Transaction Item Attributes Item Cross Reference Item Defining Attributes Item Deletion Item Master Item Relationships Item Status Attributes Item Tables Java Setup for R12 Key Tables Location Creation Lot and Serial Manufacturer Items Move order Normalization ODD NUMBERED ORA-29273 ORDER MANAGEMENT Oracle Apps Oracle Instance Oracle Order Management PURCHASING MODULE Payable Table Payment Terms Procure to Pay Cycle Profile Categories Purchase - PO Revisions Purchase - Purchase Document Types Purchase Order Purchasing - Define Buyers Purchasing - Defining Approval Groups Purchasing - Invoice matching Purchasing Options Quotations Quote Analysis RFQ RICE Components Receipt Routings Receivables (AR) Tables Receiving Options Registration of a Report Requisition Templates Responsibility Query SALES ORDER TABLE SALES PERSON CREATION SCM Service Contract Sql Self Joins Subinventory Subinventory Transfer Supplier Lists Suppliers TEMP Tablespace Toad Value Sets Wildcard Workflow copy column find an PO details implementation inbound order to cash org_id out bound out of the box implementation vanilla implementation