WITH

IDEN AS
(SELECT SPRIDEN.SPRIDEN_PIDM AS PIDM, SPRIDEN.SPRIDEN_ID AS UAID,
       CASE WHEN SPRIDEN.SPRIDEN_FIRST_NAME IS NULL THEN SPRIDEN.SPRIDEN_LAST_NAME
          WHEN SPRIDEN.SPRIDEN_MI IS NULL THEN (SPRIDEN.SPRIDEN_LAST_NAME || ', ') || SPRIDEN.SPRIDEN_FIRST_NAME
          ELSE SPRIDEN.SPRIDEN_LAST_NAME || ', ' || SPRIDEN.SPRIDEN_FIRST_NAME || ' ' || SPRIDEN.SPRIDEN_MI
       END AS NAME,
    E.PEBEMPL_ORGN_CODE_HOME AS HOME_DEPT, E.PEBEMPL_ORGN_CODE_DIST AS HOME_TKL, O.TITLE1 AS HOME_MAU, O.TITLE3 AS HOME_UNIT,
    O2.TITLE AS HOME_TKL_TITLE, E.PEBEMPL_ECLS_CODE AS PRIME_ECLS
FROM SATURN.SPRIDEN SPRIDEN INNER JOIN PEBEMPL E ON (SPRIDEN.SPRIDEN_PIDM = E.PEBEMPL_PIDM)
        INNER JOIN FTVORGN_LEVELS O ON (E.PEBEMPL_ORGN_CODE_HOME = O.ORGN_CODE)
        INNER JOIN FTVORGN_LEVELS O2 ON (E.PEBEMPL_ORGN_CODE_DIST = O2.ORGN_CODE)
WHERE SPRIDEN.SPRIDEN_CHANGE_IND IS NULL
),

ADDR AS
(SELECT A.SPRADDR_PIDM PIDM, A.SPRADDR_STREET_LINE1 STREET1, A.SPRADDR_STREET_LINE2 STREET2, A.SPRADDR_STREET_LINE3 STREET3,
    A.SPRADDR_CITY CITY, A.SPRADDR_STAT_CODE STATE, A.SPRADDR_ZIP ZIP
FROM SPRADDR_CURRENT A
WHERE A.SPRADDR_ATYP_CODE = 'HR'),

JOBS_CHG_MAX AS
(SELECT MAX (z.NBRJOBS_PERS_CHG_DATE) AS CHG_DATE, z.NBRJOBS_PIDM AS PIDM, z.NBRJOBS_POSN AS POSN, z.NBRJOBS_SUFF AS SUFF
 FROM NBRJOBS z
 WHERE (z.NBRJOBS_PERS_CHG_DATE <= '30-JUN-' || :FiscalYear)
 GROUP BY z.NBRJOBS_PIDM, z.NBRJOBS_POSN, z.NBRJOBS_SUFF),

JOBS_MAX AS
(SELECT MAX(JOBS.NBRJOBS_EFFECTIVE_DATE) AS EFF_DATE,
 JOBS.NBRJOBS_PIDM AS PIDM, JOBS.NBRJOBS_POSN AS POSN, JOBS.NBRJOBS_SUFF AS SUFF
 FROM "PUBLIC".NBRJOBS JOBS INNER JOIN JOBS_CHG_MAX J ON (JOBS.NBRJOBS_PERS_CHG_DATE = J.CHG_DATE AND JOBS.NBRJOBS_PIDM = J.PIDM
                            AND JOBS.NBRJOBS_POSN = J.POSN AND JOBS.NBRJOBS_SUFF = J.SUFF)
 GROUP BY JOBS.NBRJOBS_PIDM, JOBS.NBRJOBS_POSN, JOBS.NBRJOBS_SUFF),

ACTIVE_JOBS AS
(SELECT DISTINCT
    JOBS.NBRJOBS_PIDM AS PIDM,
    DECODE (BJOB.NBRBJOB_CONTRACT_TYPE, 'P', 'PRIMARY', 'S', 'SECONDARY', 'O', 'OVERLOAD', 'OTHER') AS CONTRACT_TYPE,
    BJOB.NBRBJOB_POSN AS POSN,
    BJOB.NBRBJOB_BEGIN_DATE AS JOB_BEGIN,
    BJOB.NBRBJOB_END_DATE AS JOB_END,
    JOBS.NBRJOBS_EFFECTIVE_DATE AS JOB_EFF_DATE,
    JOBS.NBRJOBS_STATUS AS JOB_STATUS,
    JOBS.NBRJOBS_DESC AS JOB_DESCRIPTION,
    JOBS.NBRJOBS_ECLS_CODE AS JOB_ECLS,
    JOBS.NBRJOBS_ORGN_CODE_TS AS JOB_TKL,
    O.TITLE AS JOB_TKL_TITLE,
    JOBS.NBRJOBS_SAL_GRADE AS GRADE,
    JOBS.NBRJOBS_SAL_STEP AS STEP,
    JOBS.NBRJOBS_FTE AS FTE,
    JOBS.NBRJOBS_HRS_DAY AS HRS_DAY,
    JOBS.NBRJOBS_HRS_PAY AS HRS_PAY,
    JOBS.NBRJOBS_REG_RATE AS RATE,
    JOBS.NBRJOBS_ASSGN_SALARY AS BW_SALARY,
    JOBS.NBRJOBS_FACTOR AS FACTOR,
    JOBS.NBRJOBS_ANN_SALARY AS ANNUAL_SALARY
    FROM NBRBJOB BJOB INNER JOIN JOBS_MAX ON (BJOB.NBRBJOB_PIDM = JOBS_MAX.PIDM AND BJOB.NBRBJOB_POSN = JOBS_MAX.POSN AND BJOB.NBRBJOB_SUFF = JOBS_MAX.SUFF)
         INNER JOIN NBRJOBS JOBS ON (JOBS.NBRJOBS_PIDM = JOBS_MAX.PIDM AND JOBS.NBRJOBS_POSN = JOBS_MAX.POSN AND JOBS.NBRJOBS_SUFF = JOBS_MAX.SUFF 
            AND JOBS.NBRJOBS_EFFECTIVE_DATE = JOBS_MAX.EFF_DATE)
         INNER JOIN FTVORGN_LEVELS O ON ( JOBS.NBRJOBS_ORGN_CODE_TS = O.ORGN_CODE)
    WHERE 
    JOBS.NBRJOBS_ORGN_CODE_TS LIKE (:TKL || '%') /*Enter Time-Keeping Location like 'T190'*/
    AND BJOB.NBRBJOB_BEGIN_DATE <= '30-JUN-' || :FiscalYear AND (BJOB.NBRBJOB_END_DATE >= '30-JUN-' || :FiscalYear OR BJOB.NBRBJOB_END_DATE IS NULL) /*Could use something like:  '30-JUN-' || :FiscalYear*/),
    
POSN AS
(SELECT DISTINCT P.NBBPOSN_POSN POSN, P.NBBPOSN_BARG_CODE BC, DECODE(P.NBBPOSN_BARG_CODE, 'AC', 'UNAC - Rep Faculty', 'AD','UNAD - Rep Adjuncts',
        'AG','AGWA - Rep Grad Workers','CS','CAUSE - Rep Staff','FF','IAFF - Rep Firefighters', 'L6','L6070 - Rep Crafts & Trades','NB','Non-Bargaining Unit / Non-Rep','Not Defined') BARGAIN_UNIT
 FROM NBBPOSN P)



-------------------------------MAIN QUERY BELOW THIS LINE--------------------------
SELECT DISTINCT
    :FiscalYear AS FY, I.UAID, I.NAME, I.HOME_TKL, I.HOME_TKL_TITLE, I.HOME_DEPT,
    I.HOME_MAU, I.HOME_UNIT, I.PRIME_ECLS, P.BARGAIN_UNIT,
    J.CONTRACT_TYPE, J.POSN, J.JOB_BEGIN, J.JOB_END, J.JOB_EFF_DATE,
    J.JOB_STATUS, J.JOB_DESCRIPTION, J.JOB_ECLS, J.JOB_TKL, J.JOB_TKL_TITLE,
    J.GRADE, J.STEP, J.FTE, J.HRS_DAY, J.HRS_PAY, J.RATE, J.BW_SALARY, J.FACTOR, J.ANNUAL_SALARY, 
    A.STREET1, A.STREET2, A.STREET3, A.CITY, A.STATE, A.ZIP
FROM ACTIVE_JOBS J INNER JOIN IDEN I ON (J.PIDM = I.PIDM)
    INNER JOIN POSN P ON (J.POSN = P.POSN)
    LEFT OUTER JOIN ADDR A ON (A.PIDM = I.PIDM)
ORDER BY UAID ASC, NAME ASC