|
SELECT a.institution , a.sad_uc_pers_id ,sc.oprid , a.emplid , b.last_name_srch , b.first_name_srch , b.last_name , b.first_name ,f.oprclass , f.birthdate_msk , a.sad_uc_appcode , ch.sad_uc_choiceno , a.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_IVSTARG 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_gt_decision ,' ' ,'N' , ch.sad_uc_gt_decision) sad_uc_gt_decision , ch.sad_uc_gt_reply FROM PS_SAD_UC_APPLREC A JOIN PS_PERSON_NAME B ON a.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON a.emplid = f.emplid LEFT OUTER JOIN PS_SAD_UC_IVSTARG ch ON a.institution = ch.institution AND a.sad_uc_appcode = ch.sad_uc_appcode AND a.sad_uc_pers_id = ch.sad_uc_pers_id LEFT OUTER JOIN PS_ADM_APPL_PROG D ON A.institution = d.institution AND A.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 A.institution = e.institution AND A.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 A.institution = sc.institution AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr OR sc.adm_appl_ctr = 'ALL') WHERE a.sad_uc_system = 'GTTR' AND ( Ch.EFFDT IS NULL OR Ch.EFFDT=( SELECT MAX(EFF3.EFFDT) FROM PS_SAD_UC_IVSTARG 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