Tuesday, July 14, 2015

iRecruitment Internal Vacancies/Jobs SQL Query


SELECT pav.NAME, ipc.org_name, ipc.job_title, pgt.NAME,
       ipc.posting_content_id, pav.status, pav.date_from, pav.date_to,
       hl.location_code, pav.attribute19,
       REPLACE (regexp_replace (ipc.brief_description, '<[^<>]*>', ''),
                ' ',
                ''
               ) brief_description,
       REPLACE
             (regexp_replace (ipc.detailed_description, '<[^<>]*>', ''),
              ' ',
              ''
             ) detailed_description,
       REPLACE (regexp_replace (ipc.job_requirements, '<[^<>]*>', ''),
                ' ',
                ''
               ) job_requirements,
       REPLACE (regexp_replace (ipc.additional_details, '<[^<>]*>', ''),
                ' ',
                ''
               ) additional_details,
       REPLACE (regexp_replace (ipc.how_to_apply, '<[^<>]*>', ''),
                ' ',
                ''
               ) how_to_apply
  FROM irc_posting_contents_tl ipc,
       per_all_vacancies pav,
       hr_locations_all hl,
       per_grades_tl pgt,
       per_recruitment_activities pra
 WHERE 1 = 1
   AND pav.primary_posting_id = ipc.posting_content_id
   AND ipc.posting_content_id = pra.posting_content_id
   AND ipc.LANGUAGE = 'US'
   AND pav.location_id = hl.location_id
   AND pav.grade_id = pgt.grade_id
   AND pgt.LANGUAGE = 'US'
   AND pra.recruiting_site_id = 1
   AND pav.status NOT IN ('CLOSED', 'CNCL', 'HOLD')
   AND job_title IS NOT NULL;

No comments: