Page History

SELECT DISTINCT O.DESCR, A.DEPTID, ' ', C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPL_RCD, '1', A.EMPLID, A.EMPL_STATUS, A.JOBCODE, D.DESCR, '** Total Wrkld', sum ( L.WEEK_WORKLOAD_HRS), ' ', ' ', ' ', ' ', A.BUSINESS_UNIT

  FROM PS_EMPLOYMENT_HOME A, PS_NAME_CURR_VW C, PS_JOBCODE_TBL_VW D, PS_INSTR_TERM_DTL L, PS_DEPT_TBL O

  WHERE A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_EMPLOYMENT_HOME A_ED

        WHERE A.EMPLID = A_ED.EMPLID

          AND A.EMPL_RCD = A_ED.EMPL_RCD

          AND A_ED.EFFDT <= SYSDATE)

     AND EXISTS (SELECT B.EMPLID

  FROM PS_INSTR_TERM_DTL B

  WHERE B.EMPLID = A.EMPLID

     AND B.INSTITUTION = :1

     AND B.STRM = :2)

     AND A.EMPLID = C.EMPLID

     AND C.NAME_TYPE = 'PRI'

     AND A.JOBCODE =  D.JOBCODE (+)

     AND A.EMPLID = L.EMPLID

     AND A.EMPL_RCD = L.EMPL_RCD

     AND L.STRM = :2

     AND O.DEPTID = A.DEPTID

     AND O.EFFDT =

        (SELECT MAX(O_ED.EFFDT) FROM PS_DEPT_TBL O_ED

        WHERE O.SETID = O_ED.SETID

          AND O.DEPTID = O_ED.DEPTID

          AND O_ED.EFFDT <= SYSDATE)

     AND O.SETID = A.BUSINESS_UNIT

  GROUP BY  O.DESCR,  A.DEPTID,  ' ',  C.LAST_NAME,  C.FIRST_NAME,  C.MIDDLE_NAME,  A.EMPL_RCD,  '1',  A.EMPLID,  A.EMPL_STATUS,  A.JOBCODE,  D.DESCR,  '** Total Wrkld',  ' ',  ' ',  ' ',  ' ',  A.BUSINESS_UNIT

UNION

SELECT P.DESCR, E.DEPTID, H.ACAD_ORG, M.LAST_NAME, M.FIRST_NAME, M.MIDDLE_NAME, E.EMPL_RCD, '2', ' ', ' ', ' ', ' ',  G.INSTITUTION || '-' ||  G.STRM || '-' ||  G.SESSION_CODE, G.WEEK_WORKLOAD_HRS, to_char ( G.CLASS_NBR, '99999'), G.CRSE_ID,  H.SUBJECT || ' ' ||  H.CATALOG_NBR || ' ' ||  H.CLASS_SECTION || ' Enr: ' || to_char ( H.ENRL_TOT, '999'), G.DESCR, E.BUSINESS_UNIT

  FROM PS_EMPLOYMENT_HOME E, PS_INSTR_TERM_DTL G, PS_CLASS_TBL H, PS_NAME_CURR_VW M, PS_DEPT_TBL P

  WHERE E.EFFDT =

        (SELECT MAX(E_ED.EFFDT) FROM PS_EMPLOYMENT_HOME E_ED

        WHERE E.EMPLID = E_ED.EMPLID

          AND E.EMPL_RCD = E_ED.EMPL_RCD

          AND E_ED.EFFDT <= SYSDATE)

     AND E.EMPLID = G.EMPLID

     AND E.EMPL_RCD = G.EMPL_RCD

     AND G.INSTITUTION = :1

     AND G.STRM = :2

     AND E.EMPLID = M.EMPLID

     AND M.NAME_TYPE = 'PRI'

     AND G.INSTITUTION =  H.INSTITUTION (+)

     AND G.STRM =  H.STRM (+)

     AND G.CRSE_ID =  H.CRSE_ID (+)

     AND G.CRSE_OFFER_NBR =  H.CRSE_OFFER_NBR (+)

     AND G.SESSION_CODE =  H.SESSION_CODE (+)

     AND G.CLASS_NBR =  H.CLASS_NBR (+)

     AND P.DEPTID = E.DEPTID

     AND P.EFFDT =

        (SELECT MAX(P_ED.EFFDT) FROM PS_DEPT_TBL P_ED

        WHERE P.SETID = P_ED.SETID

          AND P.DEPTID = P_ED.DEPTID

          AND P_ED.EFFDT <= SYSDATE)

     AND P.SETID = E.BUSINESS_UNIT

UNION

SELECT Q.DESCR, I.DEPTID, K.ACAD_ORG, N.LAST_NAME, N.FIRST_NAME, N.MIDDLE_NAME, I.EMPL_RCD, '3', ' ', ' ', ' ', ' ',  F.INSTITUTION || '-' ||  F.STRM || '-' ||  F.SESSION_CODE, F.WEEK_WORKLOAD_HRS, to_char ( F.CLASS_NBR, '99999'), F.CRSE_ID,  K.SUBJECT || ' ' ||  K.CATALOG_NBR || ' ' ||  K.CLASS_SECTION || ' ' || 'Enr: ' || to_char ( K.ENRL_TOT, '999'), K.DESCR, I.BUSINESS_UNIT

  FROM PS_EMPLOYMENT_HOME I, PS_INSTR_TERM_DTL F, PS_CLASS_TBL K, PS_NAME_CURR_VW N, PS_DEPT_TBL Q

  WHERE I.EFFDT =

        (SELECT MAX(I_ED.EFFDT) FROM PS_EMPLOYMENT_HOME I_ED

        WHERE I.EMPLID = I_ED.EMPLID

          AND I.EMPL_RCD = I_ED.EMPL_RCD

          AND I_ED.EFFDT <= SYSDATE)

     AND EXISTS (SELECT J.EMPLID

  FROM PS_INSTR_TERM_DTL J

  WHERE J.EMPLID = I.EMPLID

     AND J.INSTITUTION = :1

     AND J.STRM = :2)

     AND I.EMPLID = F.EMPLID

     AND I.EMPL_RCD = F.EMPL_RCD

     AND F.INSTITUTION <> :1

     AND F.STRM = :2

     AND F.INSTITUTION = K.INSTITUTION

     AND F.STRM = K.STRM

     AND K.CRSE_ID = F.CRSE_ID

     AND K.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR

     AND K.SESSION_CODE = F.SESSION_CODE

     AND K.CLASS_NBR = F.CLASS_NBR

     AND I.EMPLID = N.EMPLID

     AND Q.DEPTID = I.DEPTID

     AND Q.EFFDT =

        (SELECT MAX(Q_ED.EFFDT) FROM PS_DEPT_TBL Q_ED

        WHERE Q.SETID = Q_ED.SETID

          AND Q.DEPTID = Q_ED.DEPTID

          AND Q_ED.EFFDT <= SYSDATE)

     AND Q.SETID = I.BUSINESS_UNIT

  ORDER BY 1, 4, 5, 6, 7, 8, 17