IRC_CAND_CONTACT_INFO

Details

Schema: FUSION
Object owner: IRC
Object type: VIEW

Columns

Name
PERSON_ID CANDIDATE_NUMBER SYSTEM_PERSON_TYPE INTERNAL_FLAG EMAIL_ADDRESS_ID EMAIL_VERIFIED_FLAG EMAIL_PREFERRED_FLAG EMAIL_ADDRESS EMAIL_TYPE PHONE_ID PHONE_VERIFIED_FLAG PHONE_PREFERRED_FLAG COUNTRY_CODE_NUMBER AREA_CODE PHONE_NUMBER SEARCH_PHONE_NUMBER EXTENSION DISPLAY_PHONE_NUMBER PHONE_TYPE

Name

PERSON_ID

CANDIDATE_NUMBER

SYSTEM_PERSON_TYPE

INTERNAL_FLAG

EMAIL_ADDRESS_ID

EMAIL_VERIFIED_FLAG

EMAIL_PREFERRED_FLAG

EMAIL_ADDRESS

EMAIL_TYPE

PHONE_ID

PHONE_VERIFIED_FLAG

PHONE_PREFERRED_FLAG

COUNTRY_CODE_NUMBER

AREA_CODE

PHONE_NUMBER

SEARCH_PHONE_NUMBER

EXTENSION

DISPLAY_PHONE_NUMBER

PHONE_TYPE

Query

SQL_Statement
SELECT cand.person_id, cand.candidate_number, cand.system_person_type, cand.internal_flag, cand.email_address_id, cand.email_verified_flag, cand.email_preferred_flag, email.email_address, email.email_type, cand.phone_id, cand.phone_verified_flag, cand.phone_preferred_flag, 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.email_verified_flag, c.email_preferred_flag, c.candidate_number, c.cand_phone_id, c.phone_verified_flag, c.phone_preferred_flag, cpt.SYSTEM_PERSON_TYPE, cpt.INTERNAL_FLAG, ( CASE WHEN c.cand_email_id IS NOT NULL AND EXISTS (SELECT 1 FROM per_email_addresses e WHERE c.cand_email_id = e.email_address_id) THEN c.cand_email_id ELSE ( select email_address_id from ( SELECT pea.email_address_id FROM per_email_addresses pea WHERE c.person_id = pea.person_id AND ((cpt.INTERNAL_FLAG = 'Y' AND pea.email_type = 'W1') OR (cpt.INTERNAL_FLAG = 'N' AND pea.email_type != 'W1')) ORDER BY pea.last_update_date DESC ) WHERE ROWNUM = 1 ) END ) AS email_address_id, ( 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, IRC_CAND_PERSON_TYPE_V cpt WHERE c.person_id = cpt.person_id) cand LEFT JOIN per_display_phones_v phone ON phone.phone_id = cand.phone_id LEFT JOIN per_email_addresses email ON email.email_address_id = cand.email_address_id

SQL_Statement

SELECT cand.person_id,

cand.candidate_number,

cand.system_person_type,

cand.internal_flag,

cand.email_address_id,

cand.email_verified_flag,

cand.email_preferred_flag,

email.email_address,

email.email_type,

cand.phone_id,

cand.phone_verified_flag,

cand.phone_preferred_flag,

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.email_verified_flag,

c.email_preferred_flag,

c.candidate_number,

c.cand_phone_id,

c.phone_verified_flag,

c.phone_preferred_flag,

cpt.SYSTEM_PERSON_TYPE,

cpt.INTERNAL_FLAG,

(

CASE

WHEN c.cand_email_id IS NOT NULL AND EXISTS (SELECT 1 FROM per_email_addresses e WHERE c.cand_email_id = e.email_address_id)

THEN c.cand_email_id

ELSE

(

select email_address_id from (

SELECT

pea.email_address_id

FROM

per_email_addresses pea

WHERE

c.person_id = pea.person_id

AND ((cpt.INTERNAL_FLAG = 'Y' AND pea.email_type = 'W1') OR (cpt.INTERNAL_FLAG = 'N' AND pea.email_type != 'W1'))

ORDER BY pea.last_update_date DESC

)

WHERE ROWNUM = 1

)

END

) AS email_address_id,

(

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, IRC_CAND_PERSON_TYPE_V cpt

WHERE c.person_id = cpt.person_id) cand

LEFT JOIN per_display_phones_v phone

ON phone.phone_id = cand.phone_id

LEFT JOIN per_email_addresses email

ON email.email_address_id = cand.email_address_id