Page History

SELECT A.EMPLID, A.ACAD_CAREER, A.STRM, B.LAST_NAME, B.FIRST_NAME, C.NATIONAL_ID_TYPE, C.NATIONAL_ID, TO_CHAR(J.BIRTHDATE,'YYYY-MM-DD')

  FROM PS_STDNT_CAR_TERM A, PS_NAME_CURR_VW B, PS_SCC_PERS_NI_QVW C, PS_PERSNL_FERPA_VW J

  WHERE ( A.INSTITUTION = :1

     AND A.STRM BETWEEN :2 AND :3

     AND A.EMPLID = B.EMPLID

     AND B.NAME_TYPE = 'PRI'

     AND A.EMPLID =  C.EMPLID (+)

     AND A.STRM = (SELECT max ( G.STRM)

  FROM PS_STDNT_CAR_TERM G

  WHERE ( G.EMPLID = A.EMPLID

     AND G.ACAD_CAREER = A.ACAD_CAREER

     AND G.INSTITUTION = A.INSTITUTION

     AND G.STDNT_CAR_NBR = A.STDNT_CAR_NBR ))

     AND A.EMPLID =  J.EMPLID(+) )

UNION

SELECT E.EMPLID, E.ACAD_CAREER, E.STRM, F.LAST_NAME, F.FIRST_NAME, H.EXTERNAL_SYSTEM, H.EXTERNAL_SYSTEM_ID, TO_CHAR(K.BIRTHDATE,'YYYY-MM-DD')

  FROM PS_STDNT_CAR_TERM E, PS_NAME_CURR_VW F, PS_EXTERNAL_SYSTEM H, PS_PERSNL_FERPA_VW K

  WHERE ( E.EMPLID = F.EMPLID

     AND E.EMPLID = H.EMPLID

     AND H.EFFDT =

        (SELECT MAX(H_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM H_ED

        WHERE H.EMPLID = H_ED.EMPLID

          AND H.EXTERNAL_SYSTEM = H_ED.EXTERNAL_SYSTEM

          AND H_ED.EFFDT <= SYSDATE)

     AND H.EXTERNAL_SYSTEM = 'LID'

     AND E.INSTITUTION = :1

     AND E.STRM BETWEEN :2 AND :3

     AND EXISTS (SELECT I.EMPLID

  FROM PS_SCC_PERS_NI_QVW I

  WHERE ( I.EMPLID = E.EMPLID

     AND SUBSTR( I.NATIONAL_ID,1,1) = 'X' ))

     AND E.STRM = (SELECT max ( D.STRM)

  FROM PS_STDNT_CAR_TERM D

  WHERE ( D.EMPLID = E.EMPLID

     AND D.ACAD_CAREER = E.ACAD_CAREER

     AND D.INSTITUTION = E.INSTITUTION

     AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR ))

     AND E.EMPLID =  K.EMPLID(+)

     AND F.NAME_TYPE = 'PRI' )