IRC_CAND_PREFERRED_PHONE_V

Details

• Schema: FUSION

• Object owner: IRC

• Object type: VIEW

Columns

Name
PERSON_ID PHONE_VERIFIED_FLAG PHONE_PREFERRED_FLAG PHONE_CHANNEL_TYPE CANDIDATE_NUMBER PHONE_ID COUNTRY_CODE_NUMBER AREA_CODE PHONE_NUMBER SEARCH_PHONE_NUMBER EXTENSION DISPLAY_PHONE_NUMBER PHONE_TYPE

Query

SQL_Statement

SELECT cand.person_id, cand.phone_verified_flag, cand.phone_preferred_flag, cand.phone_channel_type, cand.candidate_number, cand.phone_id, phone.country_code_number, phone.area_code, phone.phone_number, phone.search_phone_number, phone.extension, phone.display_phone_number, phone.phone_type from (SELECT c.person_id, c.phone_verified_flag, c.phone_preferred_flag, c.phone_channel_type, c.candidate_number, ( CASE WHEN c.cand_phone_id IS NOT NULL AND EXISTS (SELECT 1 FROM per_phones p WHERE c.cand_phone_id = p.phone_id) THEN c.cand_phone_id ELSE ( SELECT PHONE_ID FROM (SELECT PP.phone_id AS PHONE_ID FROM PER_PHONES PP, IRC_CAND_PERSON_TYPE_V cpt WHERE c.PERSON_ID = PP.person_id AND c.PERSON_ID = cpt.person_id AND ((cpt.internal_flag = 'Y' AND PP.PHONE_TYPE = 'W1') OR (cpt.internal_flag = 'N' AND PP.PHONE_TYPE = 'HM')) ORDER BY PP.LAST_UPDATE_DATE DESC) WHERE ROWNUM = 1 ) END ) AS phone_id FROM irc_candidates c) cand, per_display_phones_v phone where phone.phone_id = cand.phone_id