View: AMS_PERSON_PHONE2_V

Product:	AMS - Marketing
Description:	Electronic methods of communicating with parties
Implementation/DBA Data:	APPS.AMS_PERSON_PHONE2_V

View Text

SELECT HCP.CONTACT_POINT_ID
, HCP.CONTACT_POINT_TYPE
, HCP.STATUS
, HCP.OWNER_TABLE_NAME
, HCP.OWNER_TABLE_ID
, HCP.CREATION_DATE
, HCP.CREATED_BY
, HCP.LAST_UPDATE_DATE
, HCP.LAST_UPDATED_BY
, HCP.LAST_UPDATE_LOGIN
, HCP.CONTENT_SOURCE_TYPE
, HCP.PRIMARY_FLAG
, HCP.PHONE_TOUCH_TONE_TYPE_FLAG
, HCP.PHONE_LINE_TYPE
, HCP.TIME_ZONE
, HCP.RAW_PHONE_NUMBER
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, HCP.TELEX_NUMBER
, HCP.TIMEZONE_ID
, HCP.CONTACT_POINT_PURPOSE
, HCP.PRIMARY_BY_PURPOSE
, HCP.TRANSPOSED_PHONE_NUMBER
, HCPREF.CONTACT_PREFERENCE_ID
, HCPREF.PREFERENCE_CODE
, HCPREF.PREFERENCE_TOPIC_TYPE
, HCPREF.PREFERENCE_TOPIC_TYPE_ID
, HCPREF.PREFERENCE_TOPIC_TYPE_CODE
, HCPREF.PREFERENCE_START_DATE
, HCPREF.PREFERENCE_START_TIME_HR
, HCPREF.PREFERENCE_START_TIME_MI
, HCPREF.PREFERENCE_END_DATE
, HCPREF.PREFERENCE_END_TIME_HR
, HCPREF.PREFERENCE_END_TIME_MI
, HCPREF.MAX_NO_OF_INTERACTIONS
, HCPREF.MAX_NO_OF_INTERACT_UOM_CODE
, HCPREF.REASON_CODE
FROM HZ_CONTACT_POINTS HCP
, HZ_PARTIES HP
, HZ_CONTACT_PREFERENCES HCPREF
WHERE HCPREF.CONTACT_TYPE (+) = 'CALL'
AND HCPREF.PREFERENCE_CODE (+) = 'DO'
AND HCP.STATUS = 'A'
AND HCP.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND HCP.CONTACT_POINT_TYPE = 'PHONE'
AND HCP.PHONE_LINE_TYPE='GEN'
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HCP.PRIMARY_FLAG != 'Y'
AND HCPREF.CONTACT_LEVEL_TABLE (+) = 'HZ_CONTACT_POINTS'
AND HCPREF.CONTACT_LEVEL_TABLE_ID (+) = HP.PARTY_ID
AND HCP.OWNER_TABLE_ID = HP.PARTY_ID
AND HP.STATUS = 'A'
AND HP.PARTY_TYPE = 'PERSON'
AND HCPREF.PREFERENCE_END_DATE (+) IS NULL
AND HCPREF.STATUS (+) = 'A'
AND AMS_PHONE_UTILS.GET_CREATION_DATE(HCP.OWNER_TABLE_ID
, 2) = HCP.CREATION_DATE

Columns