1. Tables and Views for HCM
  2. IRC_CAND_EMAIL_ADDRESS_V

IRC_CAND_EMAIL_ADDRESS_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

PERSON_ID

EMAIL_VERIFIED_FLAG

EMAIL_PREFERRED_FLAG

CANDIDATE_NUMBER

EMAIL_ADDRESS_ID

EMAIL_ADDRESS

EMAIL_TYPE

Query

SQL_Statement

SELECT cand.person_id,

cand.email_verified_flag,

cand.email_preferred_flag,

cand.candidate_number,

cand.email_address_id,

email.email_address,

email.email_type

from

(SELECT

c.person_id,

c.email_verified_flag,

c.email_preferred_flag,

c.candidate_number,

(

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

WHERE

c.person_id = pea.person_id

and pea.person_id = CPT.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

FROM

irc_candidates c) cand, per_email_addresses email where email.email_address_id = cand.email_address_id