Results
SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ACAD_CAREER, A.ADMIT_TERM, SUBSTR( B.NATIONAL_ID,6,4), A.PROG_ACTION, A.ADMIT_TYPE, TO_CHAR(D.BIRTHDATE,'YYYY-MM-DD'), ' ' FROM PS_ADM_MC_VW A, PS_SCC_PERS_NI_QVW B, PS_CU_BIO_VW C, PS_PERSNL_FERPA_VW D WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ADM_MC_VW A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A_ED.ADM_APPL_NBR AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR AND A_ED.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ADM_MC_VW A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A_ES.ADM_APPL_NBR AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR AND A.EFFDT = A_ES.EFFDT) AND A.INSTITUTION = :1 AND A.ADMIT_TERM BETWEEN :2 AND :3 AND A.EMPLID = B.EMPLID AND B.NATIONAL_ID <> 'XXXXXXXXX' AND A.ACAD_CAREER = :4 AND A.EMPLID = C.EMPLID AND A.EMPLID = D.EMPLID (+) UNION SELECT F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, E.EMPLID, E.ACAD_CAREER, E.ADMIT_TERM, 'xxxx', E.PROG_ACTION, E.ADMIT_TYPE, TO_CHAR(G.BIRTHDATE,'YYYY-MM-DD'), J.EXTERNAL_SYSTEM_ID FROM PS_ADM_MC_VW E, PS_CU_BIO_VW F, PS_PERSNL_FERPA_VW G, PS_EXTERNAL_SYSTEM J WHERE E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ADM_MC_VW E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.ACAD_CAREER = E_ED.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ED.STDNT_CAR_NBR AND E.ADM_APPL_NBR = E_ED.ADM_APPL_NBR AND E.APPL_PROG_NBR = E_ED.APPL_PROG_NBR AND E_ED.EFFDT <= SYSDATE) AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_ADM_MC_VW E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.ACAD_CAREER = E_ES.ACAD_CAREER AND E.STDNT_CAR_NBR = E_ES.STDNT_CAR_NBR AND E.ADM_APPL_NBR = E_ES.ADM_APPL_NBR AND E.APPL_PROG_NBR = E_ES.APPL_PROG_NBR AND E.EFFDT = E_ES.EFFDT) AND E.INSTITUTION = :1 AND E.ADMIT_TERM BETWEEN :2 AND :3 AND E.ACAD_CAREER = :4 AND E.EMPLID = F.EMPLID AND E.EMPLID = G.EMPLID (+) AND ( NOT EXISTS (SELECT H.EMPLID FROM PS_SCC_PERS_NI_QVW H WHERE H.EMPLID = E.EMPLID) OR EXISTS (SELECT I.EMPLID FROM PS_SCC_PERS_NI_QVW I WHERE I.EMPLID = E.EMPLID AND I.NATIONAL_ID = 'XXXXXXXXX')) AND E.EMPLID = J.EMPLID (+) AND 'LID' = J.EXTERNAL_SYSTEM (+) ORDER BY 1, 2, 3 | |||||||||||||||||||||||