SELECT DISTINCT PFM.PRODUCT_FAMILY_ITEM_ID , PFM.PRODUCT_FAMILY , MSI.CONCATENATED_SEGMENTS||RHX_DP.GET_PARAMETER('RHX_DP_P_DELIMITER')|| MSI.ORGANIZATION_ID , MSI.INVENTORY_ITEM_ID , MSI.ORGANIZATION_ID FROM RHX_DP_L_PRODFAM_V PFM , MTL_SYSTEM_ITEMS_KFV MSI WHERE DECODE(MSI.PRODUCT_FAMILY_ITEM_ID , NULL , -999 , MSI.PRODUCT_FAMILY_ITEM_ID ) = PFM.PRODUCT_FAMILY_ITEM_ID AND MSI.BOM_ITEM_TYPE <> 5 AND MSI.ENABLED_FLAG = 'Y' AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV MSI2 WHERE MSI2.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID AND MSI.PRODUCT_FAMILY_ITEM_ID IS NULL AND MSI2.PRODUCT_FAMILY_ITEM_ID IS NOT NULL )