|
SELECT a.oprid , f.oprclass ,A.emplid , B.last_name , B.first_name , f.birthdate_msk ,A.institution , A.acad_career ,a.stdnt_car_nbr ,a.sad_pb_rec_number ,D.ACAD_PROG , A.adm_appl_nbr , A.appl_prog_nbr , A.sad_pb_rec_type , d.admit_term , B.last_name_srch , B.first_name_srch , A.sad_pb_cas_number , A.sad_pb_cas_status ,A.sad_pb_upload %Concat A.sad_pb_update %Concat A.sad_pb_report FROM PS_SAD_PB_CASOPRVW 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_ADM_APPL_PROG D ON a.emplid = d.emplid AND a.acad_career = d.acad_career AND a.stdnt_car_nbr = d.stdnt_car_nbr AND a.adm_appl_nbr = d.adm_appl_nbr AND A.appl_prog_nbr = d.appl_prog_nbr LEFT OUTER JOIN PS_ADM_APPL_DATA E ON E.emplid = d.emplid AND E.acad_career = d.acad_career AND E.stdnt_car_nbr = d.stdnt_car_nbr AND E.adm_appl_nbr = d.adm_appl_nbr LEFT OUTER JOIN ps_scrty_appl_ctr SC ON E.institution = sc.institution WHERE A.EFFDT=( SELECT MAX(EFF1.EFFDT) FROM PS_SAD_PB_CAS EFF1 WHERE EFF1.EMPLID=A.EMPLID AND EFF1.INSTITUTION=A.INSTITUTION AND EFF1.SAD_PB_REC_TYPE=A.SAD_PB_REC_TYPE AND EFF1.ACAD_CAREER=A.ACAD_CAREER AND EFF1.STDNT_CAR_NBR=A.STDNT_CAR_NBR AND EFF1.SAD_PB_REC_NUMBER=A.SAD_PB_REC_NUMBER AND EFF1.ADM_APPL_NBR=A.ADM_APPL_NBR AND EFF1.APPL_PROG_NBR=A.APPL_PROG_NBR AND EFF1.EFFDT<=%CurrentDateIn) AND A.EFFSEQ=( SELECT MAX(EFF3.EFFSEQ) FROM PS_SAD_PB_CAS EFF3 WHERE EFF3.EMPLID=A.EMPLID AND EFF3.INSTITUTION=A.INSTITUTION AND EFF3.SAD_PB_REC_TYPE =A.SAD_PB_REC_TYPE AND EFF3.ACAD_CAREER=A.ACAD_CAREER AND EFF3.STDNT_CAR_NBR=A.STDNT_CAR_NBR AND EFF3.SAD_PB_REC_NUMBER =A.SAD_PB_REC_NUMBER AND EFF3.adm_appl_nbr = a.adm_appl_nbr AND EFF3.appl_prog_nbr = a.appl_prog_nbr AND EFF3.EFFDT=A.EFFDT ) AND ( D.EFFDT IS NULL OR ( sc.oprid = a.oprid AND D.EFFDT=( SELECT MAX(EFF2.EFFDT) FROM PS_ADM_APPL_PROG EFF2 WHERE EFF2.EMPLID=D.EMPLID AND EFF2.ACAD_CAREER=D.ACAD_CAREER AND EFF2.STDNT_CAR_NBR=D.STDNT_CAR_NBR AND EFF2.ADM_APPL_NBR=D.ADM_APPL_NBR AND EFF2.APPL_PROG_NBR=D.APPL_PROG_NBR AND EFF2.EFFDT<= %CurrentDateIn) AND D.EFFSEQ=( SELECT MAX(EFF4.EFFSEQ) FROM PS_ADM_APPL_PROG EFF4 WHERE EFF4.EMPLID=D.EMPLID AND EFF4.ACAD_CAREER=D.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=D.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=D.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=D.APPL_PROG_NBR AND EFF4.EFFDT=D.EFFDT ) AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr OR sc.adm_appl_ctr = 'ALL') ))
|
OPRID