INV Item relationships listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Item relationships listing (XML)
Short Name: INVIRRIT_XML
DB package: INV_INVIRRIT_XMLP_PKG
SELECT 
    &P_ITEM_FLEXDATA1               ITEM_FLEXDATA1, 
  MRI.INVENTORY_ITEM_ID         ITEM_ID1, 
  MSI1.DESCRIPTION              ITEM_SHORT_DESC1,
  MSI1.UNIT_OF_ISSUE            ITEM_UOI1,
    &P_CATG_FLEXDATA1            CATG_FLEXDATA1,
  MCAT1.CATEGORY_ID                CATG_ID1,
  MSI1.ITEM_CATALOG_GROUP_ID    ITEM_ICG1,
    &P_ITEM_FLEXDATA2             ITEM_FLEXDATA2,   
  MRI.RELATED_ITEM_ID           ITEM_ID2,
  MSI2.DESCRIPTION              ITEM_SHORT_DESC2,
  MSI2.UNIT_OF_ISSUE            ITEM_UOI2,
  MSI2.ITEM_CATALOG_GROUP_ID    ITEM_ICG2,
  MCATS2.STRUCTURE_ID            CATG_STRUCT_ID2,
    &P_CATG_FLEXDATA2                CATG_FLEXDATA2,
  MCAT2.CATEGORY_ID              CATG_ID2,
  MRI.RECIPROCAL_FLAG          RECIPROCAL_FLAG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value1', 'INV', 'MSTK', 101, MSI1.ORGANIZATION_ID, MSI1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value1', 'INV', 'MCAT', MCAT1.STRUCTURE_ID, NULL, MCAT1.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_CATG_VALUE1, 
	INV_INVIRRIT_XMLP_PKG.c_icg_desc_1formula(MRI.INVENTORY_ITEM_ID) C_ICG_DESC_1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value2', 'INV', 'MSTK', 101, MSI2.ORGANIZATION_ID, MSI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE2, 
	INV_INVIRRIT_XMLP_PKG.c_reciprocal_valueformula(MRI.RECIPROCAL_FLAG) C_RECIPROCAL_VALUE, 
	INV_INVIRRIT_XMLP_PKG.c_icg_desc_2formula(MRI.RELATED_ITEM_ID) C_ICG_DESC_2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value2', 'INV', 'MCAT', MCAT1.STRUCTURE_ID, NULL, MCAT1.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_CATG_VALUE2
FROM 
  MTL_RELATED_ITEMS_VIEW  MRI,  
  MTL_SYSTEM_ITEMS              MSI1,
  MTL_SYSTEM_ITEMS              MSI2,
  MTL_ITEM_CATEGORIES           MICAT1,
  MTL_CATEGORIES                MCAT1,
  MTL_CATEGORY_SETS        MCATS1,
  MTL_ITEM_CATEGORIES           MICAT2, 
  MTL_CATEGORY_SETS             MCATS2,
  MTL_CATEGORIES                MCAT2
WHERE 
 MCAT2.CATEGORY_ID = MICAT2.CATEGORY_ID 
 AND 
 MCATS2.CATEGORY_SET_ID =  MICAT1.CATEGORY_SET_ID 
 AND 
 MICAT2.CATEGORY_SET_ID = MCATS2.CATEGORY_SET_ID 
  AND 
 MICAT2.INVENTORY_ITEM_ID = MRI.RELATED_ITEM_ID 
 AND 
 MICAT2.ORGANIZATION_ID =  MRI.ORGANIZATION_ID 
 AND 
 &P_ITEM_WHERE 
 AND 
 &P_CATG_WHERE  
 AND 
 MCAT1.CATEGORY_ID         = MICAT1.CATEGORY_ID 
 AND 
 MSI2.INVENTORY_ITEM_ID   = MRI.RELATED_ITEM_ID 
 AND 
 MSI2.ORGANIZATION_ID     = MRI.ORGANIZATION_ID 
 AND 
 MSI1.INVENTORY_ITEM_ID   = MRI.INVENTORY_ITEM_ID
 AND 
 MSI1.ORGANIZATION_ID     =   MRI.ORGANIZATION_ID 
 AND 
  MCATS1.CATEGORY_SET_ID  =  :P_CATG_SET_ID 
AND 
 MICAT1.CATEGORY_SET_ID =  :P_CATG_SET_ID 
 AND
 MICAT1.INVENTORY_ITEM_ID  = MRI.INVENTORY_ITEM_ID 
 AND 
 MICAT1.ORGANIZATION_ID    = MRI.ORGANIZATION_ID 
AND 
 MRI.RELATIONSHIP_TYPE_ID = :P_RELATION_TYPE
 AND 
 MRI.ORGANIZATION_ID      =  :C_MASTER_ORG_ID
 --added
 order by  &P_ITEM_FLEXDATA1
Parameter NameSQL textValidation
Category Structure
 
Number
Delimiter
 
Organization
 
Number
To
 
Categories From
 
Category Set
 
LOV Oracle
To
 
Items From
 
Item Description Type
 
LOV Oracle
Report Relationship Type
 
LOV Oracle