Reports2017-11-18T12:27:27+00:00

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 Name SQL text Validation
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
By continuing to use the site, you agree to the use of cookies. Accept