1. Tables and Views for HCM
  2. IRC_CAND_OPT_IN_PREF_V

IRC_CAND_OPT_IN_PREF_V

Details

  • Schema: FUSION

  • Object owner: IRC

  • Object type: VIEW

Columns

Name

PERSON_ID

CANDIDATE_NUMBER

OPT_IN_MKT_EMAILS_PREF

Query

SQL_Statement

SELECT C.PERSON_ID,

C.CANDIDATE_NUMBER,

(

CASE

WHEN EXISTS

(SELECT 1

FROM per_person_type_usages_m u

WHERE (( EXISTS (SELECT 1 FROM fnd_profile_options profileoptions, fnd_profile_option_values profileoptionvalues

WHERE profileoptions.profile_option_name = 'IRC_TREAT_CWK_AS_EXTERNAL'

AND profileoptions.profile_option_id = profileoptionvalues.profile_option_id

AND profileoptionvalues.profile_option_value = 'Y'

AND profileoptionvalues.LEVEL_VALUE='SITE')

AND u.system_person_type IN ( 'EMP' ))

OR (NOT EXISTS (SELECT 1 FROM fnd_profile_options profileoptions, fnd_profile_option_values profileoptionvalues

WHERE profileoptions.profile_option_name = 'IRC_TREAT_CWK_AS_EXTERNAL'

AND profileoptions.profile_option_id = profileoptionvalues.profile_option_id

AND profileoptionvalues.profile_option_value = 'Y'

AND profileoptionvalues.LEVEL_VALUE='SITE')

AND u.system_person_type IN ( 'EMP','CWK' ) ))

AND c.person_id = u.person_id

AND TRUNC(sysdate) BETWEEN u.effective_start_date AND u.effective_end_date

AND u.effective_latest_change = 'Y'

)

THEN 'ORA_YES'

ELSE DECODE(C.OPT_IN_MKT_EMAILS_FLAG, 'Y', 'ORA_YES', 'N', 'ORA_NO', 'ORA_UNSPECIFIED')

END ) AS OPT_IN_MKT_EMAILS_PREF

FROM IRC_CANDIDATES C