VIEW: APPS.AMS_ORG_PHONE3_V

Source

View Text - Preformatted

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 = 'PARTY_RELATIONSHIP' AND hcpref.PREFERENCE_END_DATE (+) is null AND hcpref.STATUS (+) = 'A' AND ams_phone_utils.get_creation_date(hcp.owner_table_id,3) = hcp.creation_date

View Text - HTML Formatted

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 = 'PARTY_RELATIONSHIP'
AND HCPREF.PREFERENCE_END_DATE (+) IS NULL
AND HCPREF.STATUS (+) = 'A'
AND AMS_PHONE_UTILS.GET_CREATION_DATE(HCP.OWNER_TABLE_ID
, 3) = HCP.CREATION_DATE