View: AS_ALL_INTEREST_TYPES_V

View Text

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

Columns