|
SELECT Ap.institution , Ap.sad_uc_pers_id ,sc.oprid ,f.oprclass , Ap.emplid , b.last_name_srch , b.first_name_srch , b.last_name , b.first_name , f.birthdate_msk , Ap.sad_uc_appcode , ch.sad_uc_choiceno , Ap.sad_uc_appyear , ch.sad_uc_course , ch.sad_uc_campus , ch.sad_uc_entryyear , ch.adm_appl_nbr , ( SELECT MIN(ch1.effdt) FROM PS_SAD_UC_IVSTARC ch1 WHERE ch.institution = ch1.institution AND ch.sad_uc_appcode = ch1.sad_uc_appcode AND ch.sad_uc_pers_id = ch1.sad_uc_pers_id AND ch.sad_uc_choiceno = ch1.sad_uc_choiceno) sad_uc_date , d.acad_career , d.acad_prog , d.admit_term , E.adm_appl_ctr ,decode(ch.sad_uc_decision ,' ' ,'N' , ch.sad_uc_decision) sad_uc_decision , ch.sad_uc_reply FROM PS_SAD_UC_APPLREC Ap JOIN PS_PERSON_NAME B ON Ap.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON Ap.emplid = f.emplid LEFT OUTER JOIN PS_SAD_UC_IVSTARC ch ON Ap.institution = ch.institution AND Ap.sad_uc_appcode = ch.sad_uc_appcode AND Ap.sad_uc_pers_id = ch.sad_uc_pers_id LEFT OUTER JOIN PS_ADM_APPL_PROG D ON Ap.institution = d.institution AND Ap.emplid = d.emplid AND ch.acad_career = d.acad_career AND ch.stdnt_car_nbr = d.stdnt_car_nbr AND ch.adm_appl_nbr = d.adm_appl_nbr LEFT OUTER JOIN PS_ADM_APPL_DATA E ON Ap.institution = e.institution AND Ap.emplid = e.emplid AND ch.acad_career = e.acad_career AND ch.stdnt_car_nbr = e.stdnt_car_nbr AND ch.adm_appl_nbr = e.adm_appl_nbr LEFT OUTER JOIN ps_scrty_appl_ctr SC ON Ap.institution = sc.institution AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr OR sc.adm_appl_ctr = 'ALL') WHERE Ap.sad_uc_system = 'UCAS' AND ( ch.EFFDT IS NULL OR ch.EFFDT=( SELECT MAX(EFF3.EFFDT) FROM PS_SAD_UC_IVSTARC EFF3 WHERE EFF3.INSTITUTION=ch.INSTITUTION AND EFF3.SAD_UC_APPCODE=ch.SAD_UC_APPCODE AND EFF3.SAD_UC_PERS_ID=ch.SAD_UC_PERS_ID AND EFF3.SAD_UC_CHOICENO=ch.SAD_UC_CHOICENO AND EFF3.EFFDT<= %CurrentDateIn)) AND ( D.EFFDT IS NULL OR (D.EFFDT = ( SELECT MAX(EF1.EFFDT) FROM PS_ADM_APPL_PROG EF1 WHERE EF1.EMPLID = D.EMPLID AND EF1.ACAD_CAREER = D.ACAD_CAREER AND EF1.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND EF1.ADM_APPL_NBR = D.ADM_APPL_NBR AND EF1.APPL_PROG_NBR = D.APPL_PROG_NBR AND EF1.EFFDT <= %CurrentDateIn) AND D.EFFSEQ=( SELECT MAX(EF2.EFFSEQ) FROM PS_ADM_APPL_PROG EF2 WHERE EF2.EMPLID=D.EMPLID AND EF2.ACAD_CAREER=D.ACAD_CAREER AND EF2.STDNT_CAR_NBR=D.STDNT_CAR_NBR AND EF2.ADM_APPL_NBR=D.ADM_APPL_NBR AND EF2.APPL_PROG_NBR=D.APPL_PROG_NBR AND EF2.EFFDT=D.EFFDT ) ))
|
INSTITUTION