|
SELECT A.INSTITUTION ,A.ACAD_CAREER , A.EMPLID ,A.ACAD_PROG , B.ACAD_PLAN , C.SSR_SL_ACAD_YR_NLD , C.SCC_BRINCD_NLD ,A.ADM_APPL_NBR ,A.APPL_PROG_NBR , A.ACAD_LOAD_APPR , C.SCC_SUBBRINCD_NLD , C.SSR_FORM_STUDY_NLD , C.SSR_ACAD_LEVEL_NLD ,A.CAMPUS ,C.SSR_SL_STARTMO_NLD FROM PS_ADM_APPL_PROG A , PS_ADM_APPL_PLAN B , PS_SAD_STD_APP_NLD C , PS_EXTERNAL_SYSTEM D WHERE A.EMPLID =B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR =B.ADM_APPL_NBR AND A.APPL_PROG_NBR = B.APPL_PROG_NBR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EMPLID =C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND A.ADM_APPL_NBR =C.ADM_APPL_NBR AND A.APPL_PROG_NBR = C.APPL_PROG_NBR AND A.EFFDT = C.EFFDT AND A.EFFSEQ = C.EFFSEQ AND A.EMPLID = D.EMPLID AND D.EXTERNAL_SYSTEM ='SLN' AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_EXTERNAL_SYSTEM D1 WHERE D.EMPLID = D1.EMPLID AND D.EXTERNAL_SYSTEM = D1.EXTERNAL_SYSTEM AND D1.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ADM_APPL_PROG A1 WHERE A.EMPLID =A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.STDNT_CAR_NBR = A1.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A.APPL_PROG_NBR = A1.APPL_PROG_NBR AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_ADM_APPL_PROG A2 WHERE A.EMPLID =A2.EMPLID AND A.ACAD_CAREER = A2.ACAD_CAREER AND A.STDNT_CAR_NBR = A2.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A2.ADM_APPL_NBR AND A.APPL_PROG_NBR = A2.APPL_PROG_NBR AND A.EFFDT =A2.EFFDT) AND A.PROG_STATUS <> 'AC' AND EXISTS ( SELECT 'X' FROM PS_ADM_APPL_PROG AX , PS_ADM_APPL_PLAN BX , PS_SAD_STD_APP_NLD CX WHERE AX.EMPLID =BX.EMPLID AND AX.ACAD_CAREER = BX.ACAD_CAREER AND AX.STDNT_CAR_NBR = BX.STDNT_CAR_NBR AND AX.ADM_APPL_NBR =BX.ADM_APPL_NBR AND AX.APPL_PROG_NBR = BX.APPL_PROG_NBR AND AX.EFFDT = BX.EFFDT AND AX.EFFSEQ = BX.EFFSEQ AND AX.EMPLID =CX.EMPLID AND AX.ACAD_CAREER = CX.ACAD_CAREER AND AX.STDNT_CAR_NBR = CX.STDNT_CAR_NBR AND AX.ADM_APPL_NBR =CX.ADM_APPL_NBR AND AX.APPL_PROG_NBR = CX.APPL_PROG_NBR AND AX.EFFDT = CX.EFFDT AND AX.EFFSEQ = CX.EFFSEQ AND AX.EMPLID =A.EMPLID AND AX.ACAD_CAREER = A.ACAD_CAREER AND AX.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND AX.ADM_APPL_NBR =A.ADM_APPL_NBR AND AX.APPL_PROG_NBR = A.APPL_PROG_NBR AND (AX.EFFDT = A.EFFDT AND AX.EFFSEQ = A.EFFSEQ -1) AND ( AX.ACAD_LOAD_APPR <> A.ACAD_LOAD_APPR OR C.SCC_SUBBRINCD_NLD <> CX.SCC_SUBBRINCD_NLD OR C.SSR_FORM_STUDY_NLD <> CX.SSR_FORM_STUDY_NLD OR C.SSR_ACAD_LEVEL_NLD <> CX.SSR_ACAD_LEVEL_NLD OR C.SSR_SL_STARTMO_NLD <> CX.SSR_SL_STARTMO_NLD OR A.CAMPUS <> AX.CAMPUS))
|
INSTITUTION