Legend of the Query:
Image of CU_BAR_CM_00006
Query SQL:
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