|
SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.EMPLID , ES.SSR_BR_ENRL_SQ_NLD , AP.SSR_BR_ENR_ST_DT , AP.SSR_FUNDING_CD_NLD , CASE WHEN H.SCC_HEGIS_TYPE_NLD = 'P' THEN APT.HEGIS_CODE ELSE %Coalesce(APC.SCC_CAF_ATTR_VAL ,APT.HEGIS_CODE) END , CASE WHEN AP.SSR_FORM_STUDY_NLD = 'E' THEN 'EX' ELSE (%Sql(SSR_BR_LOOKUP_CAR_LD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, AP.ACAD_LOAD_APPR, AP.SSR_BR_ENR_ST_DT)) END , (%Sql(SSR_BR_ACAD_PROG_LVL_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, AP.ACAD_PROG, AP.SSR_BR_ENR_ST_DT)) , CASE WHEN RIO.SSR_RI_BRON_NLD = 'N' THEN (%Sql(SSR_BR_NHR_LOC_CD_SQL, ES.INSTITUTION, AP.CAMPUS)) END , CASE WHEN RIO.SSR_RI_BRON_NLD = 'Y' THEN CASE WHEN AP.SSR_BR_ENR_ST_DT >= SETUP.SSR_BR_RIO_BDT_NLD OR ( AP2.EFFDT IS NULL AND SP2.EFFDT IS NULL AND ENVW.SSR_BR_ENR_END_DT IS NULL ) THEN (%Sql(SSR_BR_NHR_LOC_CD_SQL, ES.INSTITUTION, AP.CAMPUS)) END END AS SSR_RI_OLO_CD_NLD , CASE WHEN RIO.SSR_RI_BRON_NLD = 'Y' THEN CASE WHEN AP.SSR_BR_ENR_ST_DT >= SETUP.SSR_BR_RIO_BDT_NLD OR ( AP2.EFFDT IS NULL AND SP2.EFFDT IS NULL AND ENVW.SSR_BR_ENR_END_DT IS NULL ) THEN CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'HM' THEN (%Sql(SSR_BR_RIO_EPV_ACADPROG_NLD, AP.ACAD_ORG)) ELSE CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'AP' THEN (%Sql(SSR_BR_RIO_EPV_ACADPROG_NLD, APT.ACAD_ORG)) ELSE CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'AC' THEN (%Sql(SSR_BR_RIO_EPV_ACADPROG_NLD, SETUP.ACAD_ORG)) END END END END END AS SSR_RI_EPV_COD_NLD , ES.ACAD_CAREER , ES.STDNT_CAR_NBR , AP.ACAD_PROG FROM PS_SSR_BR_ENRVWNLD ES JOIN PS_SSR_BR_EPVW2NLD AP ON ES.INSTITUTION = AP.INSTITUTION AND ES.SCC_BRINCD_NLD = AP.SCC_BRINCD_NLD AND ES.EMPLID = AP.EMPLID AND ES.ACAD_CAREER = AP.ACAD_CAREER AND ES.STDNT_CAR_NBR = AP.STDNT_CAR_NBR JOIN PS_SSR_BR_ENRS_NLD SETUP ON SETUP.INSTITUTION = ES.INSTITUTION AND SETUP.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND %EffdtCheck(SSR_BR_ENRS_NLD SETUP_ED, SETUP, AP.SSR_BR_ENR_ST_DT) LEFT OUTER JOIN PS_ACAD_PROG AP2 ON AP2.EMPLID = AP.EMPLID AND AP2.ACAD_CAREER = AP.ACAD_CAREER AND AP2.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP2.PROG_ACTION IN ('MATR' , 'ACTV' , 'DATA' , 'PRGC' , 'PLNC') AND AP2.EFFDT > AP.SSR_BR_ENR_ST_DT AND AP2.EFFDT <= SETUP.SSR_BR_RIO_BDT_NLD LEFT OUTER JOIN PS_SSR_STD_PRG_NLD SP2 ON SP2.EMPLID = AP.EMPLID AND SP2.ACAD_CAREER = AP.ACAD_CAREER AND SP2.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND SP2.SSR_PRG_ACTION_NLD IN (%Sql(SSR_BR_SETUP_PRG_ACT_HE_SQL, AP.INSTITUTION, ES.SCC_BRINCD_NLD)) AND SP2.EFFDT > AP.SSR_BR_ENR_ST_DT AND SP2.EFFDT <= SETUP.SSR_BR_RIO_BDT_NLD LEFT OUTER JOIN PS_SSR_BR_ENVW_NLD ENVW ON ENVW.INSTITUTION = ES.INSTITUTION AND ENVW.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND ENVW.EMPLID = AP.EMPLID AND ENVW.SSR_BR_ENRL_SQ_NLD = ES.SSR_BR_ENRL_SQ_NLD AND ENVW.SSR_BR_ENR_END_DT BETWEEN AP.SSR_BR_ENR_ST_DT AND SETUP.SSR_BR_RIO_BDT_NLD JOIN PS_ACAD_PROG_TBL APT ON APT.ACAD_PROG = AP.ACAD_PROG AND %EffdtCheck(ACAD_PROG_TBL APT_ED, APT, AP.SSR_BR_ENR_ST_DT) LEFT OUTER JOIN PS_ACAD_PROG_CAF APC ON APT.ACAD_PROG = APC.ACAD_PROG AND APT.EFFDT = APC.EFFDT AND APC.SCC_CAF_ATTRIB_NM = SETUP.SCC_CAF_ATTRIB_NM JOIN PS_SSR_HEGISCD_NLD H ON APT.HEGIS_CODE = H.HEGIS_CODE AND %EffdtCheck(SSR_HEGISCD_NLD H_ED,H,%CurrentDateIn) LEFT OUTER JOIN PS_SSR_RI_EPS_NLD RIO ON RIO.INSTITUTION = ES.INSTITUTION WHERE (RIO.EFFDT = ( SELECT MAX(RIO_ED.EFFDT) FROM PS_SSR_RI_EPS_NLD RIO_ED WHERE RIO.INSTITUTION = RIO_ED.INSTITUTION AND RIO_ED.EFFDT <= %CurrentDateIn) OR RIO.EFFDT IS NULL)
|
INSTITUTION