|
SELECT DISTINCT AP.INSTITUTION , SP1.EMPLID ,SP1.SSR_BRHO_RESEQ_NLD , %Substring(%Coalesce((%Sql(SSR_BR_NAT_ID_NLD,AP.EMPLID,'PR')) ,' ' ),1,9) , %Substring(%Coalesce((%Sql(SSR_BR_NAT_ID_NLD,AP.EMPLID,'OND')),' ') ,1,9) ,SP2.SCC_BRINCD_NLD ,SP2.SCC_SUBBRINCD_NLD ,SP2.SSR_SL_PROG_CD_NLD ,%Coalesce((SELECT ACLDX.SSR_BR_AC_LDAP_NLD FROM PS_SSR_BR_ACLD_NLD ACLDX WHERE ACLDX.ACAD_LOAD_APPR = AP.ACAD_LOAD_APPR),' ') ,%Coalesce((SELECT ACLVLX.SSR_BR_LEVEL_NLD FROM PS_SSR_BR_ALVL_NLD ACLVLX WHERE ACLVLX.SSR_ACAD_LEVEL_NLD = SP1.SSR_ACAD_LEVEL_NLD),' ') ,SP1.EFFDT ,%Coalesce((SELECT FNDX.SSR_BR_FNDCD_X_NLD FROM PS_SSR_BR_XFND_NLD FNDX WHERE FNDX.SSR_BR_FUND_CD_NLD = SP1.SSR_FUNDING_CD_NLD),' ') ,AP.ACAD_CAREER ,AP.STDNT_CAR_NBR FROM PS_SSR_STD_PRG_NLD SP1 , PS_SSR_STD_PRG_NLD SP2 , PS_ACAD_PROG AP ,PS_SSR_BR_INST_NLD INST WHERE AP.EMPLID = SP1.EMPLID AND AP.ACAD_CAREER = SP1.ACAD_CAREER AND AP.STDNT_CAR_NBR = SP1.STDNT_CAR_NBR AND SP1.SSR_BRHO_RESEQ_NLD = SP2.SSR_BRHO_RESEQ_NLD AND SP1.effdt = ( SELECT MAX(SP1_1.EFFDT) FROM PS_SSR_STD_PRG_NLD SP1_1 WHERE SP1.emplid = SP1_1.EMPLID AND SP1.SSR_BRHO_RESEQ_NLD = SP1_1.SSR_BRHO_RESEQ_NLD AND SP1.EFFSEQ = ( SELECT MAX(SP1_2.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP1_2 WHERE SP1.emplid = SP1_2.EMPLID AND SP1.SSR_BRHO_RESEQ_NLD = SP1_2.SSR_BRHO_RESEQ_NLD AND SP1_2.EFFDT = SP1.effdt) AND SP1_1.ssr_prg_action_nld IN ( SELECT RT.ssr_prg_action_nld FROM ps_SSR_BR_RTRG_NLD RT)) AND SP1.EMPLID = SP2.EMPLID AND SP1.ACAD_CAREER = SP2.ACAD_CAREER AND SP1.STDNT_CAR_NBR = SP2.STDNT_CAR_NBR AND SP2.effdt = ( SELECT MAX(SP2_1.EFFDT) FROM PS_SSR_STD_PRG_NLD SP2_1 WHERE SP1.emplid = SP2_1.EMPLID AND SP1.SSR_BRHO_RESEQ_NLD = SP2_1.SSR_BRHO_RESEQ_NLD AND SP2_1.EFFDT <= %CurrentDateIn) AND SP2.EFFSEQ = ( SELECT MAX(SP2_2.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP2_2 WHERE SP1.emplid = SP2_2.EMPLID AND SP1.SSR_BRHO_RESEQ_NLD = SP2_2.SSR_BRHO_RESEQ_NLD AND SP2_2.EFFDT<= SP2.effdt) AND AP.EFFDT = ( SELECT MAX(AP1.EFFDT) FROM PS_ACAD_PROG AP1 WHERE AP1.EMPLID = AP.EMPLID AND AP1.ACAD_CAREER = AP.ACAD_CAREER AND AP1.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP1.EFFDT <= ( SELECT T.TERM_END_DT FROM PS_TERM_TBL T WHERE T.INSTITUTION = AP.INSTITUTION AND T.TERM_CATEGORY = ( SELECT TERM_CATEGORY FROM PS_SSR_BR_PRCO_NLD) AND T.ACAD_CAREER = AP.ACAD_CAREER AND T.ACAD_YEAR = SP2.ACAD_YEAR) ) AND AP.EFFSEQ = ( SELECT MAX(AP2.EFFSEQ) FROM PS_ACAD_PROG AP2 WHERE AP.EMPLID = AP2.EMPLID AND AP.ACAD_CAREER = AP2.ACAD_CAREER AND AP.STDNT_CAR_NBR = AP2.STDNT_CAR_NBR AND AP2.EFFDT = AP.EFFDT) AND INST.INSTITUTION = AP.INSTITUTION AND ((AP.ACAD_CAREER IN ( SELECT SETUP.ACAD_CAREER FROM PS_SSR_BR_ACAR_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_CR_NLD = 'I' )) OR (AP.ACAD_CAREER IN ( SELECT PRMPT.ACAD_CAREER FROM PS_ACAD_CAR_TBL PRMPT WHERE INST.SSR_BR_EXIN_CR_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_CAR_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_CAREER NOT IN ( SELECT SETUP2.ACAD_CAREER FROM PS_SSR_BR_ACAR_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) )) ) AND ((AP.ACAD_PROG IN ( SELECT SETUP.ACAD_PROG FROM PS_SSR_BR_PROG_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_PR_NLD = 'I' ) ) OR (AP.ACAD_PROG IN ( SELECT PRMPT.ACAD_PROG FROM PS_ACAD_PROG_TBL PRMPT WHERE INST.SSR_BR_EXIN_PR_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_PROG_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_PROG NOT IN ( SELECT SETUP2.ACAD_PROG FROM PS_SSR_BR_PROG_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) )) ) AND ((SP1.SSR_ACAD_LEVEL_NLD IN ( SELECT SETUP.SSR_ACAD_LEVEL_NLD FROM PS_SSR_BR_LVLS_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_LV_NLD = 'I' )) OR (SP1.SSR_ACAD_LEVEL_NLD IN ( SELECT FIELDVALUE FROM psxlatitem WHERE INST.SSR_BR_EXIN_LV_NLD = 'E' AND fieldname = 'SSR_ACAD_LEVEL_NLD' AND fieldvalue NOT IN ( SELECT SETUP2.SSR_ACAD_LEVEL_NLD FROM PS_SSR_BR_LVLS_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION )) ) ) AND ((SP2.SSR_FORM_STUDY_NLD IN ( SELECT SETUP.SSR_FORM_STUDY_NLD FROM PS_SSR_BR_FOS_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_FS_NLD = 'I' ) ) OR (SP2.SSR_FORM_STUDY_NLD IN ( SELECT FIELDVALUE FROM psxlatitem WHERE INST.SSR_BR_EXIN_FS_NLD = 'E' AND fieldname = 'SSR_FORM_STUDY_NLD' AND fieldvalue NOT IN ( SELECT SETUP2.SSR_FORM_STUDY_NLD FROM PS_SSR_BR_FOS_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION )) ) ) AND EXISTS ( SELECT 'X' FROM PS_ACAD_PLAN PLANX , PS_SSR_STD_PRG_NLD C WHERE AP.EMPLID =PLANX.EMPLID AND AP.ACAD_CAREER = PLANX.ACAD_CAREER AND AP.STDNT_CAR_NBR = PLANX.STDNT_CAR_NBR AND AP.EFFDT = PLANX.EFFDT AND AP.EFFSEQ = PLANX.EFFSEQ AND AP.EMPLID =C.EMPLID AND AP.ACAD_CAREER = C.ACAD_CAREER AND AP.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND ((PLANX.ACAD_PLAN IN ( SELECT SETUP.ACAD_PLAN FROM PS_SSR_BR_PLAN_NLD SETUP WHERE SETUP.INSTITUTION = AP.INSTITUTION AND INST.SSR_BR_EXIN_PL_NLD = 'I' )) OR (PLANX.ACAD_PLAN IN ( SELECT PRMPT.ACAD_PLAN FROM PS_ACAD_PLAN_TBL PRMPT WHERE INST.SSR_BR_EXIN_PL_NLD = 'E' AND PRMPT.INSTITUTION = AP.INSTITUTION AND %EffdtCheck(ACAD_PLAN_TBL B2,PRMPT,%CurrentDateIn) AND PRMPT.ACAD_PLAN NOT IN ( SELECT SETUP2.ACAD_PLAN FROM PS_SSR_BR_PLAN_NLD SETUP2 WHERE SETUP2.INSTITUTION = AP.INSTITUTION) ) )))
|
INSTITUTION