SELECT pav.vacancy_id VACANCY_ID,
pav.name VACANCY_NAME,
loc.location_code LOCATION,
ipc.job_title JOB_TITLE,
ipc.org_name ORG_NAME,
to_char(pav.date_from,'DD-MON-YYYY') DATE_FROM,
to_char(pav.date_to,'DD-MON-YYYY') DATE_TO,
ADS_STRIPALLHTMLTAGS(ipc.org_description) ORG_DESCRIPTION,
ADS_STRIPALLHTMLTAGS(ipc.brief_description) BRIEF_DESCRIPTION,
ADS_STRIPALLHTMLTAGS(IPc.DETAILED_DESCRIPTION) DETAILED_DESCRIPTION,
ADS_STRIPALLHTMLTAGS(IPc.JOB_REQUIREMENTS) JOB_REQUIREMENTS,
ADS_STRIPALLHTMLTAGS(IPc.ADDITIONAL_DETAILS) ADDITIONAL_DETAILS,
ADS_STRIPALLHTMLTAGS(IPc.HOW_TO_APPLY) HOW_TO,
ias.EXTERNAL EXTERNAL
FROM per_all_vacancies pav,
per_recruitment_activities pra,
per_recruitment_activity_for prf,
irc_posting_contents_vl ipc,
irc_all_recruiting_sites_vl ias,
hr_locations_all_vl loc,
hr_all_organization_units ou
WHERE pav.vacancy_id = prf.vacancy_id
AND prf.recruitment_activity_id = pra.recruitment_activity_id
AND pra.posting_content_id = ipc.posting_content_id
AND pra.recruiting_site_id = ias.recruiting_site_id
AND loc.location_id(+) = pav.location_id
AND NVL(:In_Date,SYSDATE) BETWEEN pav.date_from AND nvl(pav.date_to, '31-DEC-2099')
and pav.business_group_id=ou.business_group_id
and pav.business_group_id = 1668
and pav.ORGANIZATION_ID=ou.ORGANIZATION_ID
AND ias.EXTERNAL = 'Y' |
