Query SQL:
SELECT C.ACAD_GROUP, D.DEPTID, E.DESCR, D.JOBCODE, F.DESCR, sum ( G.UNITS_ACAD_PROG * C.ENRL_TOT), sum ( A.WEEK_WORKLOAD_HRS),E.SETID,E.DEPTID,TO_CHAR(E.EFFDT,'YYYY-MM-DD'),F.SETID,F.JOBCODE,TO_CHAR(F.EFFDT,'YYYY-MM-DD')
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)
GROUP BY C.ACAD_GROUP, D.DEPTID, E.DESCR, D.JOBCODE, F.DESCR,E.SETID,E.DEPTID,TO_CHAR(E.EFFDT,'YYYY-MM-DD'),F.SETID,F.JOBCODE,TO_CHAR(F.EFFDT,'YYYY-MM-DD')
ORDER BY 1, 3, 5