INV Item cross-references listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Item cross-references listing (XML)
Short Name: INVIRXRF_XML
DB package: INV_INVIRXRF_XMLP_PKG
SELECT
    &P_ITEM_FLEXDATA        ITEM_FLEXDATA2, 
  MSI.INVENTORY_ITEM_ID  ITEM_ID2,
  MSI.DESCRIPTION         ITEM_DESC2,
    &P_CATG_FLEXDATA   CATG_FLEXDATA2,
   MCAT.CATEGORY_ID          CATG_ID2,
  MCR.ORG_INDEPENDENT_FLAG  ORG_INDP,
  MCR.CROSS_REFERENCE_TYPE  XRF_TYPE2,
  MCR.CROSS_REFERENCE       XRF2,
  MCR.DESCRIPTION           XRF_DESC2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value2', 'INV', 'MCAT', MCAT.STRUCTURE_ID, NULL, MCAT.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_CATG_VALUE2, 
	INV_INVIRXRF_XMLP_PKG.c_org_indp1formula(MCR.ORG_INDEPENDENT_FLAG) C_org_indp1
FROM  
  MTL_ITEM_CATEGORIES  MICAT, 
  MTL_CATEGORY_SETS MCATS,
  MTL_CATEGORIES MCAT,
  MTL_SYSTEM_ITEMS_VL  MSI,   
  MTL_CROSS_REFERENCES MCR
WHERE 
&P_CATG_WHERE
AND MCATS.CATEGORY_SET_ID = :P_CATG_SET_ID
AND  MSI.INVENTORY_ITEM_ID = MICAT.INVENTORY_ITEM_ID 
AND  MICAT.CATEGORY_SET_ID = MCATS.CATEGORY_SET_ID 
AND  MICAT.CATEGORY_ID =  MCAT.CATEGORY_ID 
AND  MSI.ORGANIZATION_ID  = MICAT.ORGANIZATION_ID 
AND  &P_ITEM_WHERE 
AND MSI.ORGANIZATION_ID =  MICAT.ORGANIZATION_ID 
AND  MCR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID 
AND  MSI.ORGANIZATION_ID = :P_ORG 
AND MCATS.CATEGORY_SET_ID = :P_CATG_SET_ID 
AND (( MCR.ORG_INDEPENDENT_FLAG = 'N'  AND MCR.ORGANIZATION_ID = :P_ORG) 
             OR MCR.ORG_INDEPENDENT_FLAG = 'Y' )
AND   MCR.CROSS_REFERENCE_TYPE =
 NVL(:P_XREF_TYPE , MCR.CROSS_REFERENCE_TYPE)
 AND
  (
   ( :P_XREF_LO IS NULL AND :P_XREF_HI IS NULL )
   OR
   ( :P_XREF_LO IS NOT NULL AND :P_XREF_HI IS NULL
     AND
     :P_XREF_LO = MCR.CROSS_REFERENCE )
   OR
   ( :P_XREF_LO IS NOT NULL AND :P_XREF_HI IS NOT NULL
     AND
     MCR.CROSS_REFERENCE BETWEEN :P_XREF_LO AND             :P_XREF_HI )
  )
Order by 
&P_ITEM_ORDER_BY, 
mcr.cross_reference_type, 
mcr.cross_reference, 
mcr.description
Parameter Name SQL text Validation
Category Structure
 
Number
Organization
 
Number
To
 
Items From
 
To
 
LOV Oracle
Cross References From
 
LOV Oracle
Cross Reference Type
 
LOV Oracle
To
 
Categories From
 
Category Set
 
LOV Oracle
Report Type
 
LOV Oracle