Query SQL:
SELECT A.EMPLID, A.STRM, A.UNT_TAKEN_PRGRSS, B.FIRST_NAME, B.MIDDLE_NAME, B.LAST_NAME, A.ACAD_CAREER, L.ACAD_PLAN, C.NATIONAL_ID
FROM PS_STDNT_CAR_TERM A, PS_CU_BIO_VW B, PS_SCC_PERS_NI_QVW C, PS_ACAD_PROG I, PS_ACAD_PLAN L
WHERE ( A.INSTITUTION = :1
AND A.STRM = :2
AND A.EMPLID = B.EMPLID
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_EXTERNAL_SYSTEM D
WHERE ( D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.EXTERNAL_SYSTEM = D_ED.EXTERNAL_SYSTEM
AND D_ED.EFFDT <= SYSDATE)
AND D.EMPLID = A.EMPLID
AND D.EXTERNAL_SYSTEM = 'LID' ))
AND A.EMPLID = C.EMPLID
AND 'XXXXXXXXX' = C.NATIONAL_ID
AND SUBSTR ( B.LAST_NAME, 1, 5) <> 'ZZZZZ'
AND A.EMPLID = I.EMPLID
AND A.ACAD_CAREER = I.ACAD_CAREER
AND A.INSTITUTION = I.INSTITUTION
AND I.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_ACAD_PROG I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.ACAD_CAREER = I_ED.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR
AND I_ED.EFFDT <= SYSDATE)
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_ACAD_PROG I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.ACAD_CAREER = I_ES.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR
AND I.EFFDT = I_ES.EFFDT)
AND I.PROG_STATUS = 'AC'
AND I.EMPLID = L.EMPLID
AND I.ACAD_CAREER = L.ACAD_CAREER
AND I.STDNT_CAR_NBR = L.STDNT_CAR_NBR
AND I.EFFSEQ = L.EFFSEQ
AND I.EFFDT = L.EFFDT
AND L.ACAD_PLAN NOT LIKE '%-MIN' )
UNION
SELECT E.EMPLID, E.STRM, E.UNT_TAKEN_PRGRSS, F.FIRST_NAME, F.MIDDLE_NAME, F.LAST_NAME, E.ACAD_CAREER, K.ACAD_PLAN, ' '
FROM PS_STDNT_CAR_TERM E, PS_CU_BIO_VW F, PS_ACAD_PROG J, PS_ACAD_PLAN K
WHERE ( E.INSTITUTION = :1
AND E.STRM = :2
AND E.EMPLID = F.EMPLID
AND NOT EXISTS (SELECT G.EMPLID
FROM PS_SCC_PERS_NI_QVW G
WHERE ( G.EMPLID = E.EMPLID
AND G.COUNTRY = 'USA' ))
AND NOT EXISTS (SELECT H.EMPLID
FROM PS_EXTERNAL_SYSTEM H
WHERE ( 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.EMPLID = E.EMPLID
AND H.EXTERNAL_SYSTEM = 'LID' ))
AND E.EMPLID = J.EMPLID
AND E.ACAD_CAREER = J.ACAD_CAREER
AND E.INSTITUTION = J.INSTITUTION
AND J.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND J.EFFDT =
(SELECT MAX(J_ED.EFFDT) FROM PS_ACAD_PROG J_ED
WHERE J.EMPLID = J_ED.EMPLID
AND J.ACAD_CAREER = J_ED.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ED.STDNT_CAR_NBR
AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ =
(SELECT MAX(J_ES.EFFSEQ) FROM PS_ACAD_PROG J_ES
WHERE J.EMPLID = J_ES.EMPLID
AND J.ACAD_CAREER = J_ES.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ES.STDNT_CAR_NBR
AND J.EFFDT = J_ES.EFFDT)
AND J.PROG_STATUS = 'AC'
AND J.EMPLID = K.EMPLID
AND J.ACAD_CAREER = K.ACAD_CAREER
AND J.STDNT_CAR_NBR = K.STDNT_CAR_NBR
AND J.EFFSEQ = K.EFFSEQ
AND J.EFFDT = K.EFFDT
AND K.ACAD_PLAN NOT LIKE '%-MIN' )
FROM PS_STDNT_CAR_TERM A, PS_CU_BIO_VW B, PS_SCC_PERS_NI_QVW C, PS_ACAD_PROG I, PS_ACAD_PLAN L
WHERE ( A.INSTITUTION = :1
AND A.STRM = :2
AND A.EMPLID = B.EMPLID
AND NOT EXISTS (SELECT D.EMPLID
FROM PS_EXTERNAL_SYSTEM D
WHERE ( D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM D_ED
WHERE D.EMPLID = D_ED.EMPLID
AND D.EXTERNAL_SYSTEM = D_ED.EXTERNAL_SYSTEM
AND D_ED.EFFDT <= SYSDATE)
AND D.EMPLID = A.EMPLID
AND D.EXTERNAL_SYSTEM = 'LID' ))
AND A.EMPLID = C.EMPLID
AND 'XXXXXXXXX' = C.NATIONAL_ID
AND SUBSTR ( B.LAST_NAME, 1, 5) <> 'ZZZZZ'
AND A.EMPLID = I.EMPLID
AND A.ACAD_CAREER = I.ACAD_CAREER
AND A.INSTITUTION = I.INSTITUTION
AND I.STDNT_CAR_NBR = A.STDNT_CAR_NBR
AND I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_ACAD_PROG I_ED
WHERE I.EMPLID = I_ED.EMPLID
AND I.ACAD_CAREER = I_ED.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ED.STDNT_CAR_NBR
AND I_ED.EFFDT <= SYSDATE)
AND I.EFFSEQ =
(SELECT MAX(I_ES.EFFSEQ) FROM PS_ACAD_PROG I_ES
WHERE I.EMPLID = I_ES.EMPLID
AND I.ACAD_CAREER = I_ES.ACAD_CAREER
AND I.STDNT_CAR_NBR = I_ES.STDNT_CAR_NBR
AND I.EFFDT = I_ES.EFFDT)
AND I.PROG_STATUS = 'AC'
AND I.EMPLID = L.EMPLID
AND I.ACAD_CAREER = L.ACAD_CAREER
AND I.STDNT_CAR_NBR = L.STDNT_CAR_NBR
AND I.EFFSEQ = L.EFFSEQ
AND I.EFFDT = L.EFFDT
AND L.ACAD_PLAN NOT LIKE '%-MIN' )
UNION
SELECT E.EMPLID, E.STRM, E.UNT_TAKEN_PRGRSS, F.FIRST_NAME, F.MIDDLE_NAME, F.LAST_NAME, E.ACAD_CAREER, K.ACAD_PLAN, ' '
FROM PS_STDNT_CAR_TERM E, PS_CU_BIO_VW F, PS_ACAD_PROG J, PS_ACAD_PLAN K
WHERE ( E.INSTITUTION = :1
AND E.STRM = :2
AND E.EMPLID = F.EMPLID
AND NOT EXISTS (SELECT G.EMPLID
FROM PS_SCC_PERS_NI_QVW G
WHERE ( G.EMPLID = E.EMPLID
AND G.COUNTRY = 'USA' ))
AND NOT EXISTS (SELECT H.EMPLID
FROM PS_EXTERNAL_SYSTEM H
WHERE ( 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.EMPLID = E.EMPLID
AND H.EXTERNAL_SYSTEM = 'LID' ))
AND E.EMPLID = J.EMPLID
AND E.ACAD_CAREER = J.ACAD_CAREER
AND E.INSTITUTION = J.INSTITUTION
AND J.STDNT_CAR_NBR = E.STDNT_CAR_NBR
AND J.EFFDT =
(SELECT MAX(J_ED.EFFDT) FROM PS_ACAD_PROG J_ED
WHERE J.EMPLID = J_ED.EMPLID
AND J.ACAD_CAREER = J_ED.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ED.STDNT_CAR_NBR
AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ =
(SELECT MAX(J_ES.EFFSEQ) FROM PS_ACAD_PROG J_ES
WHERE J.EMPLID = J_ES.EMPLID
AND J.ACAD_CAREER = J_ES.ACAD_CAREER
AND J.STDNT_CAR_NBR = J_ES.STDNT_CAR_NBR
AND J.EFFDT = J_ES.EFFDT)
AND J.PROG_STATUS = 'AC'
AND J.EMPLID = K.EMPLID
AND J.ACAD_CAREER = K.ACAD_CAREER
AND J.STDNT_CAR_NBR = K.STDNT_CAR_NBR
AND J.EFFSEQ = K.EFFSEQ
AND J.EFFDT = K.EFFDT
AND K.ACAD_PLAN NOT LIKE '%-MIN' )