Page History

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