Identify Active Job Codes using SQL Query

SELECT

JOBS.JOB_ID,

JOBS.EFFECTIVE_START_DATE,

JOBS.EFFECTIVE_END_DATE,

JOBS.JOB_CODE,

JOBS_TL.NAME,

JOBS.BUSINESS_GROUP_ID,

JOBS.SET_ID,

JOBS.JOB_FAMILY_ID,

JOBS.BENCHMARK_JOB_ID,

JOBS.ACTION_OCCURRENCE_ID,

JOBS.ACTIVE_STATUS,

JOBS.BENCHMARK_JOB_FLAG,

JOBS.APPROVAL_AUTHORITY,

JOBS.MANAGER_LEVEL,

JOBS.MED_CHECKUP_REQ,

JOBS.REGULAR_TEMPORARY,

JOBS.FULL_PART_TIME,

JOBS.ATTRIBUTE4,

case
when  jobs.manager_level =    '0'    then    'Chief Operating Officer'

end as Career_Stream
FROM

PER_JOBS_F JOBS,

PER_JOBS_F_TL JOBS_TL

WHERE

JOBS.JOB_ID = JOBS_TL.JOB_ID AND JOBS.EFFECTIVE_START_DATE = JOBS_TL.EFFECTIVE_START_DATE AND JOBS.EFFECTIVE_END_DATE = JOBS_TL.EFFECTIVE_END_DATE AND JOBS_TL.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG') AND TRUNC(SYSDATE) BETWEEN JOBS.EFFECTIVE_START_DATE AND JOBS.EFFECTIVE_END_DATE
AND JOBS.ACTIVE_STATUS = 'A'

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.