BIP – Extract Job details with Job Family and Job Function details

SELECT pjf.job_id
     , pjf.job_code
     , pjft.name
     , pjffv.job_family_name
     , pjf.effective_start_date
     , pjf.job_function_code
     , hikm.source_system_id
     , hikm.source_system_owner
  FROM per_jobs_f pjf
     , per_jobs_f_tl pjft
     , per_job_family_f_vl pjffv
     , hrc_integration_key_map hikm
 WHERE pjf.job_id = hikm.surrogate_id
   AND pjft.job_id = pjf.job_id
   AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
   AND trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
   AND trunc(sysdate) between pjffv.effective_start_date and pjffv.effective_end_date
   AND pjft.language = 'US'
   AND pjffv.job_family_id = pjf.job_family_id

List of all job families not associated with a Job:

SELECT *
  FROM per_job_family_f_vl pjffv
 WHERE 1=1
   AND NOT EXISTS (SELECT 1
                     FROM per_jobs_f pjf
		    WHERE pjffv.job_family_id = pjf.job_family_id)