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 /