VIEW: APPS.IRCFV_VACANCY_CONSIDERATION

Source

View Text - Preformatted

SELECT bgT.name business_group_name ,par.person_name candidate_name ,usr.email_address email_address ,vac.name vacancy_name ,hr_bis.bis_decode_lookup('IRC_CONSIDERATION', ivc.consideration_status) consideration_status ,ivc.last_update_date consider_status_timestamp ,vac.date_from vacancy_date_from ,vac.date_to vacancy_date_to ,NVL(vac.date_to, hr_general.end_of_time) vacancy_date_to_nn ,vac.budget_measurement_type budget_measurement_type ,vac.budget_measurement_value budget_measurement_value /* system codes */ ,ivc.consideration_status consideration_status_code /* id columns */ ,vac.business_group_id business_group_id ,vac.organization_id organization_id ,vac.job_id job_id ,vac.grade_id grade_id ,vac.position_id position_id ,vac.location_id location_id ,vac.requisition_id requisition_id ,vac.people_group_id people_group_id ,vac.recruiter_id recruiter_id ,vac.vacancy_id vacancy_id ,vac.manager_id manager_id ,par.party_id party_id ,usr.user_id user_id ,usr.customer_id customer_id FROM hz_person_profiles par ,fnd_user usr ,irc_vacancy_considerations ivc ,per_vacancies vac ,hr_all_organization_units_tl bgT ,irc_notification_preferences inp WHERE par.party_id = usr.person_party_id (+) AND ivc.party_id = par.party_id AND par.party_id = inp.party_id AND inp.allow_access = 'Y' AND ivc.vacancy_id = vac.vacancy_id AND vac.business_group_id = bgT.organization_id AND bgT.language = USERENV('LANG') 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 BGT.NAME BUSINESS_GROUP_NAME
, PAR.PERSON_NAME CANDIDATE_NAME
, USR.EMAIL_ADDRESS EMAIL_ADDRESS
, VAC.NAME VACANCY_NAME
, HR_BIS.BIS_DECODE_LOOKUP('IRC_CONSIDERATION'
, IVC.CONSIDERATION_STATUS) CONSIDERATION_STATUS
, IVC.LAST_UPDATE_DATE CONSIDER_STATUS_TIMESTAMP
, VAC.DATE_FROM VACANCY_DATE_FROM
, VAC.DATE_TO VACANCY_DATE_TO
, NVL(VAC.DATE_TO
, HR_GENERAL.END_OF_TIME) VACANCY_DATE_TO_NN
, VAC.BUDGET_MEASUREMENT_TYPE BUDGET_MEASUREMENT_TYPE
, VAC.BUDGET_MEASUREMENT_VALUE BUDGET_MEASUREMENT_VALUE /* SYSTEM CODES */
, IVC.CONSIDERATION_STATUS CONSIDERATION_STATUS_CODE /* ID COLUMNS */
, VAC.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VAC.ORGANIZATION_ID ORGANIZATION_ID
, VAC.JOB_ID JOB_ID
, VAC.GRADE_ID GRADE_ID
, VAC.POSITION_ID POSITION_ID
, VAC.LOCATION_ID LOCATION_ID
, VAC.REQUISITION_ID REQUISITION_ID
, VAC.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, VAC.RECRUITER_ID RECRUITER_ID
, VAC.VACANCY_ID VACANCY_ID
, VAC.MANAGER_ID MANAGER_ID
, PAR.PARTY_ID PARTY_ID
, USR.USER_ID USER_ID
, USR.CUSTOMER_ID CUSTOMER_ID
FROM HZ_PERSON_PROFILES PAR
, FND_USER USR
, IRC_VACANCY_CONSIDERATIONS IVC
, PER_VACANCIES VAC
, HR_ALL_ORGANIZATION_UNITS_TL BGT
, IRC_NOTIFICATION_PREFERENCES INP
WHERE PAR.PARTY_ID = USR.PERSON_PARTY_ID (+)
AND IVC.PARTY_ID = PAR.PARTY_ID
AND PAR.PARTY_ID = INP.PARTY_ID
AND INP.ALLOW_ACCESS = 'Y'
AND IVC.VACANCY_ID = VAC.VACANCY_ID
AND VAC.BUSINESS_GROUP_ID = BGT.ORGANIZATION_ID
AND BGT.LANGUAGE = USERENV('LANG')
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