SELECT DISTINCT INTEREST_TYPE_ID || '/' || TO_NUMBER(NULL) INT_CODE_ID , INTEREST_TYPE PRODUCT_CATEGORY FROM AS_INTEREST_TYPES_VL WHERE NLS_UPPER(ENABLED_FLAG) = 'Y' UNION SELECT DISTINCT AIT.INTEREST_TYPE_ID || '/' || -999 , AIT.INTEREST_TYPE || '/ALL' FROM AS_INTEREST_TYPES_VL AIT WHERE NLS_UPPER(AIT.ENABLED_FLAG) = 'Y' AND EXISTS (SELECT 'Y' FROM AS_INTEREST_CODES AIC WHERE AIT.INTEREST_TYPE_ID = AIC.INTEREST_TYPE_ID AND AIC.PARENT_INTEREST_CODE_ID IS NULL AND NLS_UPPER(AIC.ENABLED_FLAG) = 'Y') UNION SELECT DISTINCT AIT.INTEREST_TYPE_ID || '/' || AIC.INTEREST_CODE_ID , AIT.INTEREST_TYPE || '/' || AIC.CODE FROM AS_INTEREST_TYPES_VL AIT , AS_INTEREST_CODES_VL AIC WHERE AIT.INTEREST_TYPE_ID = AIC.INTEREST_TYPE_ID AND AIC.PARENT_INTEREST_CODE_ID IS NULL AND NLS_UPPER(AIC.ENABLED_FLAG) = 'Y' AND NLS_UPPER(AIT.ENABLED_FLAG) = 'Y' UNION SELECT DISTINCT AIT.INTEREST_TYPE_ID || '/' || AIC.INTEREST_CODE_ID || '/' || -999 , AIT.INTEREST_TYPE || '/' || AIC.CODE || '/' || 'ALL' FROM AS_INTEREST_TYPES_VL AIT , AS_INTEREST_CODES_VL AIC WHERE AIT.INTEREST_TYPE_ID = AIC.INTEREST_TYPE_ID AND AIC.PARENT_INTEREST_CODE_ID IS NULL AND NLS_UPPER(AIC.ENABLED_FLAG) = 'Y' AND NLS_UPPER(AIT.ENABLED_FLAG) = 'Y' AND EXISTS (SELECT 'Y' FROM AS_INTEREST_CODES AIC WHERE AIT.INTEREST_TYPE_ID = AIC.INTEREST_TYPE_ID AND AIC.PARENT_INTEREST_CODE_ID IS NOT NULL AND NLS_UPPER(AIC.ENABLED_FLAG) = 'Y') UNION SELECT DISTINCT AIT.INTEREST_TYPE_ID || '/' || AIC.INTEREST_CODE_ID || '/' || AIC.INTEREST_CODE_ID , AIT.INTEREST_TYPE || '/' || AIC.CODE || '/' || AIC2.CODE FROM AS_INTEREST_TYPES_VL AIT , AS_INTEREST_CODES_VL AIC , AS_INTEREST_CODES_VL AIC2 WHERE AIT.INTEREST_TYPE_ID = AIC.INTEREST_TYPE_ID AND AIC.PARENT_INTEREST_CODE_ID IS NULL AND NLS_UPPER(AIC.ENABLED_FLAG) = 'Y' AND NLS_UPPER(AIT.ENABLED_FLAG) = 'Y' AND AIT.INTEREST_TYPE_ID = AIC2.INTEREST_TYPE_ID AND AIC2.PARENT_INTEREST_CODE_ID IS NOT NULL AND NLS_UPPER(AIC2.ENABLED_FLAG) = 'Y' UNION SELECT '0' , ' ALL' FROM DUAL