Query SQL:
SELECT D.DEPTID, E.DESCR, D.JOBCODE, F.DESCR, B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.SUBJECT, C.CATALOG_NBR, C.DESCR, C.ENRL_TOT, G.UNITS_ACAD_PROG, A.WEEK_WORKLOAD_HRS
FROM PS_INSTR_TERM_DTL A, PS_CU_BIO_VW B, PS_CLASS_TBL C, PS_JOB D, PS_EMPLMT_SRCH_QRY D1, PS_DEPT_TBL E, PS_JOBCODE_TBL F, PS_CRSE_CATALOG G
WHERE D.EMPLID = D1.EMPLID
AND D.EMPL_RCD = D1.EMPL_RCD
AND D1.OPRID = '11002650'
AND ( A.INSTITUTION = :1
AND A.STRM = :2
AND A.EMPLID = B.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND A.STRM = C.STRM
AND A.CRSE_ID = C.CRSE_ID
AND A.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR
AND A.SESSION_CODE = C.SESSION_CODE
AND A.CLASS_NBR = C.CLASS_NBR
AND A.EMPLID = D.EMPLID
AND D.EMPL_RCD = A.EMPL_RCD
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_JOB D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.EMPL_RCD = D_ED.EMPL_RCD
AND D_ED.EFFDT <= C.START_DT)
AND D.EFFSEQ =
(SELECT MAX(D_ES.EFFSEQ) FROM PS_JOB D_ES
WHERE D.EMPLID = D_ES.EMPLID
AND D.EMPL_RCD = D_ES.EMPL_RCD
AND D.EFFDT = D_ES.EFFDT)
AND E.DEPTID = D.DEPTID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED
WHERE E.SETID = E_ED.SETID
AND E.DEPTID = E_ED.DEPTID
AND E_ED.EFFDT <= C.START_DT)
AND E.SETID = D.SETID_DEPT
AND F.JOBCODE = D.JOBCODE
AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_JOBCODE_TBL F_ED
WHERE F.SETID = F_ED.SETID
AND F.JOBCODE = F_ED.JOBCODE
AND F_ED.EFFDT <= C.START_DT)
AND F.SETID = D.SETID_JOBCODE
AND C.CRSE_ID = G.CRSE_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_CRSE_CATALOG G_ED
WHERE G.CRSE_ID = G_ED.CRSE_ID
AND G_ED.EFFDT <= C.START_DT) )
UNION
SELECT J.DEPTID, L.DESCR, J.JOBCODE, K.DESCR, I.LAST_NAME, I.FIRST_NAME, I.MIDDLE_NAME, H.EMPLID, ' ', ' ', H.DESCR, 0, 0, H.WEEK_WORKLOAD_HRS
FROM PS_INSTR_TERM_DTL H, PS_CU_BIO_VW I, PS_JOB J, PS_EMPLMT_SRCH_QRY J1, PS_JOBCODE_TBL K, PS_DEPT_TBL L
WHERE J.EMPLID = J1.EMPLID
AND J.EMPL_RCD = J1.EMPL_RCD
AND J1.OPRID = '11002650'
AND ( H.INSTITUTION = :1
AND H.STRM = :2
AND H.EMPLID = I.EMPLID
AND H.EMPLID = J.EMPLID
AND J.EMPL_RCD = H.EMPL_RCD
AND J.EFFDT =
(SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
WHERE J.EMPLID = J_ED.EMPLID
AND J.EMPL_RCD = J_ED.EMPL_RCD
AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ =
(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
WHERE J.EMPLID = J_ES.EMPLID
AND J.EMPL_RCD = J_ES.EMPL_RCD
AND J.EFFDT = J_ES.EFFDT)
AND H.CLASS_NBR = 0
AND K.JOBCODE = J.JOBCODE
AND K.EFFDT =
(SELECT MAX(K_ED.EFFDT) FROM PS_JOBCODE_TBL K_ED
WHERE K.SETID = K_ED.SETID
AND K.JOBCODE = K_ED.JOBCODE
AND K_ED.EFFDT <= SYSDATE)
AND K.SETID = J.SETID_JOBCODE
AND L.DEPTID = J.DEPTID
AND L.EFFDT =
(SELECT MAX(L_ED.EFFDT) FROM PS_DEPT_TBL L_ED
WHERE L.SETID = L_ED.SETID
AND L.DEPTID = L_ED.DEPTID
AND L_ED.EFFDT <= SYSDATE)
AND L.SETID = J.SETID_DEPT )
ORDER BY 2, 4, 5, 6, 7