Legend of the Query:
Image of CU_BAR_CM_00006I
Query SQL:
SELECT A.DEPTID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPL_RCD, '1', A.EMPLID, A.EMPL_STATUS, A.JOBCODE, D.DESCR, A.BUSINESS_UNIT, F.DESCR, F.CRSE_ID, F.CRSE_OFFER_NBR, F.CLASS_NBR, F.CLASS_MTG_NBR, F.SESSION_CODE, F.INSTITUTION,D.SETID,D.JOBCODE,F.EMPLID,F.STRM,F.INST_TRM_DET_SEQ
FROM PS_EMPLOYMENT_HOME A, PS_NAME_CURR_VW C, PS_JOBCODE_TBL_VW D, PS_INSTR_TERM_DTL F
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.EMPL_RCD = A.EMPL_RCD
AND B.INSTITUTION = :1
AND B.STRM = :2 ))
AND A.EMPLID = C.EMPLID
AND C.NAME_TYPE = 'PRI'
AND A.JOBCODE = D.JOBCODE (+)
AND NOT EXISTS (SELECT E.DEPTID
FROM PS_DEPT_TBL E
WHERE ( 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 <= SYSDATE)
AND E.SETID = A.BUSINESS_UNIT
AND E.DEPTID = A.DEPTID
AND E.EFF_STATUS = 'A' ))
AND A.EMPLID = F.EMPLID
AND A.EMPL_RCD = F.EMPL_RCD
AND F.STRM = :2 )
ORDER BY 1, 2, 3, 4, 5, 6