|
SELECT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.EMPLID , ES.SSR_BR_ENRL_SQ_NLD , ES.SSR_BR_ENRL_SQ_NLD , ES.SSR_BR_ENRL_SQ_NLD , APT.HEGIS_CODE , ITMVWS.SCC_CAF_ATTR_VAL AS SSR_BR_ELC_TST_NLD , AP.EFFDT , 'Y' , CASE WHEN RIO.SSR_RI_BRON_NLD = 'Y' THEN CASE WHEN AP.EFFDT >= SETUP.SSR_BR_RIO_BDT_NLD THEN CASE WHEN SETUP.SSR_BR_EDPVSEL_NLD = 'HM' THEN (%Sql(SSR_BR_RIO_EPV_HMCAMP_NLD, ES.INSTITUTION, AP.ACAD_PROG, AP.CAMPUS)) 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 FROM PS_SSR_BR_ENRVWNLD ES JOIN PS_ACAD_PROG AP ON ES.INSTITUTION = AP.INSTITUTION AND ES.EMPLID = AP.EMPLID AND ES.ACAD_CAREER =AP.ACAD_CAREER AND ES.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP.EFFDT = ( SELECT MAX(AP_ED.EFFDT) FROM PS_ACAD_PROG AP_ED WHERE AP_ED.INSTITUTION = AP.INSTITUTION AND AP_ED.EMPLID = AP.EMPLID AND AP_ED.ACAD_CAREER =AP.ACAD_CAREER AND AP_ED.STDNT_CAR_NBR = AP.STDNT_CAR_NBR) AND AP.EFFSEQ = ( SELECT MAX(AP_E.EFFSEQ) FROM PS_ACAD_PROG AP_E WHERE AP_E.INSTITUTION = AP.INSTITUTION AND AP_E.EMPLID = AP.EMPLID AND AP_E.ACAD_CAREER = AP.ACAD_CAREER AND AP_E.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP_E.EFFDT = AP.EFFDT) JOIN PS_ACAD_PROG_TBL APT ON ES.INSTITUTION = APT.INSTITUTION AND AP.ACAD_PROG = APT.ACAD_PROG AND APT.EFFDT = ( SELECT MAX(APT1.EFFDT) FROM PS_ACAD_PROG_TBL APT1 WHERE APT1.INSTITUTION = APT.INSTITUTION AND APT1.ACAD_PROG = APT.ACAD_PROG AND APT1.EFFDT <= AP.EFFDT) 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.EFFDT) JOIN PS_SSR_BR_RS2C_NLD INST ON INST.INSTITUTION = ES.INSTITUTION AND INST.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND INST.EFFDT = SETUP.EFFDT JOIN PS_SSR_APT_RSLT RSLT ON RSLT.INSTITUTION = ES.INSTITUTION AND RSLT.EMPLID = ES.EMPLID AND RSLT.SSR_RSLT_TYPE = INST.SSR_RSLT_TYPE AND RSLT.SSR_APT_INSTANCE = AP.SSR_APT_INSTANCE JOIN PS_SSR_APT_ITEM ITM ON RSLT.INSTITUTION = ITM.INSTITUTION AND RSLT.EMPLID = ITM.EMPLID AND ITM.SSR_APT_INSTANCE = RSLT.SSR_APT_INSTANCE AND ITM.SSR_APT_ITEM_SEQ = RSLT.SSR_APT_ITEM_SEQ JOIN PS_SSR_AIR_ADD ITMVWS ON RSLT.INSTITUTION = ITMVWS.INSTITUTION AND ITM.SSR_ITEM_ID = ITMVWS.SSR_ITEM_ID AND ITMVWS.SCC_CAF_ATTRIB_NM = ( SELECT RCA.SCC_CAF_ATTRIB_NM FROM PS_SSR_BR_RCA_NLD RCA WHERE RCA.INSTITUTION = INST.INSTITUTION AND RCA.SCC_BRINCD_NLD = INST.SCC_BRINCD_NLD AND RCA.EFFDT = INST.EFFDT) AND ITMVWS.EFFDT = ( SELECT MAX(ITMVWS1.EFFDT) FROM PS_SSR_AIR_HDR ITMVWS1 WHERE ITMVWS1.INSTITUTION = ITMVWS.INSTITUTION AND ITMVWS1.SSR_ITEM_ID = ITMVWS.SSR_ITEM_ID AND ITMVWS1.EFFDT <= AP.EFFDT) AND ITM.SSR_ITEM_TYPE IN ( SELECT AICT.SSR_ITEM_TYPE FROM PS_SSR_BR_AICT_NLD AICT WHERE AICT.INSTITUTION = ES.INSTITUTION AND AICT.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND AICT.EFFDT = ( SELECT MAX(AICT1.EFFDT) FROM PS_SSR_BR_AICT_NLD AICT1 WHERE AICT1.INSTITUTION = AICT.INSTITUTION AND AICT1.SSR_ITEM_TYPE = AICT.SSR_ITEM_TYPE AND AICT1.SCC_BRINCD_NLD = AICT.SCC_BRINCD_NLD )) AND RSLT.ATTEMPT_NBR = ( SELECT MAX(RSLT1.ATTEMPT_NBR) FROM PS_SSR_APT_RSLT RSLT1 , PS_SSR_APT_ATT ATT , PS_SSR_APT_ITEM ITM1 , PS_SSR_AIR_ADD ITMVWS1 WHERE RSLT.EMPLID = RSLT1.EMPLID AND RSLT.INSTITUTION = RSLT1.INSTITUTION AND RSLT.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND RSLT1.INSTITUTION = ITM1.INSTITUTION AND RSLT1.EMPLID = ITM1.EMPLID AND ITM1.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND ITM1.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ AND ITM1.SSR_ITEM_TYPE = ITM.SSR_ITEM_TYPE AND RSLT1.INSTITUTION = ATT.INSTITUTION AND RSLT1.EMPLID = ATT.EMPLID AND ATT.SSR_APT_INSTANCE = RSLT1.SSR_APT_INSTANCE AND ATT.SSR_APT_ITEM_SEQ = RSLT1.SSR_APT_ITEM_SEQ AND ATT.SSR_INCL_IN_CALC ='Y' AND RSLT1.ATTEMPT_NBR = ATT.ATTEMPT_NBR AND RSLT1.INSTITUTION = ITMVWS1.INSTITUTION AND ITM1.SSR_ITEM_ID = ITMVWS1.SSR_ITEM_ID AND ITMVWS1.SCC_CAF_ATTRIB_NM = ( SELECT RCA.SCC_CAF_ATTRIB_NM FROM PS_SSR_BR_RCA_NLD RCA WHERE RCA.INSTITUTION = INST.INSTITUTION AND RCA.SCC_BRINCD_NLD = INST.SCC_BRINCD_NLD AND RCA.EFFDT = INST.EFFDT) AND ITMVWS1.SCC_CAF_ATTR_VAL = ITMVWS.SCC_CAF_ATTR_VAL AND RSLT1.SSR_INCL_IN_CALC = 'Y' AND RSLT1.SSR_RSLT_TYPE_STAT >= '30' AND RSLT1.SSR_RSLT_OUTCOME = '40' AND ATT.SSR_ATT_STAT >= '80' AND AP.PROG_ACTION IN ( SELECT 'COMP' FROM PS_INSTALLATION WHERE AP.ACAD_LOAD_APPR IN (%Sql(SSR_BR_GET_ODT_LOAD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD)) UNION %Sql(SSR_BR_GET_ACTION_RSN_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD))) AND RSLT.SSR_RSLT_SEQ = ( SELECT MAX(RSLT2.SSR_RSLT_SEQ) FROM PS_SSR_APT_RSLT RSLT2 WHERE RSLT.EMPLID = RSLT2.EMPLID AND RSLT.INSTITUTION = RSLT2.INSTITUTION AND RSLT.SSR_APT_INSTANCE = RSLT2.SSR_APT_INSTANCE AND RSLT.SSR_APT_ITEM_SEQ = RSLT2.SSR_APT_ITEM_SEQ AND RSLT.ATTEMPT_NBR = RSLT2.ATTEMPT_NBR AND RSLT2.SSR_INCL_IN_CALC = 'Y' ) 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