VIEW: APPS.IRCFV_CANDIDATES

Source

View Text - Preformatted

SELECT par.person_name candidate_name ,usr.email_address email_address ,par.person_first_name first_name ,par.person_last_name last_name /* notification preferences */ ,hr_bis.bis_decode_lookup('YES_NO',inp.matching_jobs) matching_jobs ,hr_bis.bis_decode_lookup('YES_NO',inp.receive_info_mail) receive_info_mail ,hr_bis.bis_decode_lookup('IRC_MESSAGE_FREQ',inp.matching_job_freq) frequency_of_emails ,hr_bis.bis_decode_lookup('YES_NO', DECODE(fup.preference_value, 'MAILHTML', 'Y','N')) email_in_html_format /* system codes */ ,inp.matching_jobs matching_jobs_code ,inp.receive_info_mail receive_info_mail_code ,inp.matching_job_freq matching_job_freq_code /* descriptive flexfields */ ,'_DF:PER:IRC_NOTIFICATION_PREFERENCES:inp' ,'_DF:AR:HZ_PERSON_PROFILES:par' /* id columns */ ,par.party_id party_id ,usr.user_id user_id ,usr.customer_id customer_id from hz_person_profiles par ,fnd_user usr ,fnd_user_preferences fup ,irc_notification_preferences inp where par.party_id = usr.person_party_id (+) and inp.party_id = par.party_id and UPPER(usr.email_address) = fup.user_name (+) and fup.module_name (+) = 'WF' and inp.allow_access = 'Y' and trunc(sysdate) between NVL(par.effective_start_date, hr_general.start_of_time) and NVL(par.effective_end_date , hr_general.end_of_time) WITH READ ONLY

View Text - HTML Formatted

SELECT PAR.PERSON_NAME CANDIDATE_NAME
, USR.EMAIL_ADDRESS EMAIL_ADDRESS
, PAR.PERSON_FIRST_NAME FIRST_NAME
, PAR.PERSON_LAST_NAME LAST_NAME /* NOTIFICATION PREFERENCES */
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, INP.MATCHING_JOBS) MATCHING_JOBS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, INP.RECEIVE_INFO_MAIL) RECEIVE_INFO_MAIL
, HR_BIS.BIS_DECODE_LOOKUP('IRC_MESSAGE_FREQ'
, INP.MATCHING_JOB_FREQ) FREQUENCY_OF_EMAILS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(FUP.PREFERENCE_VALUE
, 'MAILHTML'
, 'Y'
, 'N')) EMAIL_IN_HTML_FORMAT /* SYSTEM CODES */
, INP.MATCHING_JOBS MATCHING_JOBS_CODE
, INP.RECEIVE_INFO_MAIL RECEIVE_INFO_MAIL_CODE
, INP.MATCHING_JOB_FREQ MATCHING_JOB_FREQ_CODE /* DESCRIPTIVE FLEXFIELDS */
, '_DF:PER:IRC_NOTIFICATION_PREFERENCES:INP'
, '_DF:AR:HZ_PERSON_PROFILES:PAR' /* ID COLUMNS */
, PAR.PARTY_ID PARTY_ID
, USR.USER_ID USER_ID
, USR.CUSTOMER_ID CUSTOMER_ID
FROM HZ_PERSON_PROFILES PAR
, FND_USER USR
, FND_USER_PREFERENCES FUP
, IRC_NOTIFICATION_PREFERENCES INP
WHERE PAR.PARTY_ID = USR.PERSON_PARTY_ID (+)
AND INP.PARTY_ID = PAR.PARTY_ID
AND UPPER(USR.EMAIL_ADDRESS) = FUP.USER_NAME (+)
AND FUP.MODULE_NAME (+) = 'WF'
AND INP.ALLOW_ACCESS = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(PAR.EFFECTIVE_START_DATE
, HR_GENERAL.START_OF_TIME)
AND NVL(PAR.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME) WITH READ ONLY