View: AMS_CONTACT_PRODUCTS_V

Product:	AMS - Marketing
Description:	This view is used by Contact Preferences. This view store the interest Types and interet type codes
Implementation/DBA Data:	APPS.AMS_CONTACT_PRODUCTS_V

View Text

SELECT /*+ FIRST_ROWS USE_NL(BI TI BP TP) */ BI.INTEREST_TYPE_ID ID
, TO_CHAR(BI.INTEREST_TYPE_ID) INTEREST_TYPE_ID
, -1 INTEREST_CODE_ID
, TI.INTEREST_TYPE
, ''
, 'AS_INTEREST_TYPES_B'
FROM AS_INTEREST_TYPES_B BI
, AS_INTEREST_TYPES_TL TI
WHERE BI.ENABLED_FLAG = 'Y'
AND BI.EXPECTED_PURCHASE_FLAG = 'Y'
AND TI.INTEREST_TYPE_ID = BI.INTEREST_TYPE_ID
AND TI.LANGUAGE=USERENV('LANG') UNION SELECT /*+ FIRST_ROWS USE_NL(BI TI BP TP) */ BP.INTEREST_CODE_ID ID
, TO_CHAR(BI.INTEREST_TYPE_ID)
, BP.INTEREST_CODE_ID
, TI.INTEREST_TYPE
, TP.CODE
, 'AS_INTEREST_CODES_B'
FROM AS_INTEREST_TYPES_B BI
, AS_INTEREST_TYPES_TL TI
, AS_INTEREST_CODES_B BP
, AS_INTEREST_CODES_TL TP
WHERE BI.INTEREST_TYPE_ID = BP.INTEREST_TYPE_ID
AND BP.PARENT_INTEREST_CODE_ID IS NULL
AND BI.ENABLED_FLAG = 'Y'
AND BI.EXPECTED_PURCHASE_FLAG = 'Y'
AND BP.ENABLED_FLAG = 'Y'
AND TI.INTEREST_TYPE_ID = BI.INTEREST_TYPE_ID
AND TP.INTEREST_CODE_ID = BP.INTEREST_CODE_ID
AND TP.LANGUAGE=USERENV('LANG')
AND TI.LANGUAGE=USERENV('LANG')

Columns