PER ADS Current Vacancy Details

 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'