Job Family

SELECT pjf.job_family_name
      ,pj.job_family_code CODE
      ,pj.active_status
      ,TO_CHAR (pj.effective_start_date
               ,'DD-Mon-YYYY'
               ,'NLS_DATE_LANGUAGE=AMERICAN')
          effective_start_date
FROM   per_job_family_f_tl pjf
      ,per_job_family_f pj
WHERE  pjf.job_family_id = pj.job_family_id
       AND TRUNC (SYSDATE) BETWEEN pj.effective_start_date
                               AND pj.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN pjf.effective_start_date
                               AND pjf.effective_end_date