|
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 , RSLT.SSR_RSLT_OUTCOME , 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.PROG_ACTION = 'COMP' AND AP.ACAD_LOAD_APPR NOT IN (%Sql(SSR_BR_GET_ODT_LOAD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD)) 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_RS2E_NLD INST ON INST.INSTITUTION = ES.INSTITUTION AND INST.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND INST.EFFDT = ( SELECT MAX(INST_ED.EFFDT) FROM PS_SSR_BR_RS2E_NLD INST_ED WHERE INST.INSTITUTION = INST_ED.INSTITUTION AND INST.SCC_BRINCD_NLD = INST_ED.SCC_BRINCD_NLD AND INST_ED.EFFDT <= AP.EFFDT) JOIN PS_SSR_APT_RSLT RSLT ON RSLT.INSTITUTION = ES.INSTITUTION AND RSLT.EMPLID = ES.EMPLID JOIN PS_SSR_APT_ITEM ITM ON RSLT.INSTITUTION = ITM.INSTITUTION AND RSLT.EMPLID = ITM.EMPLID AND RSLT.SSR_APT_INSTANCE = AP.SSR_APT_INSTANCE AND RSLT.SSR_RSLT_TYPE = INST.SSR_RSLT_TYPE 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 REA.SCC_CAF_ATTRIB_NM FROM PS_SSR_BR_REA_NLD REA WHERE REA.INSTITUTION = INST.INSTITUTION AND REA.SCC_BRINCD_NLD = INST.SCC_BRINCD_NLD AND REA.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 AIET.SSR_ITEM_TYPE FROM PS_SSR_BR_AIET_NLD AIET WHERE AIET.INSTITUTION = ES.INSTITUTION AND AIET.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND AIET.EFFDT = ( SELECT MAX(AIET1.EFFDT) FROM PS_SSR_BR_AIET_NLD AIET1 WHERE AIET1.INSTITUTION = AIET.INSTITUTION AND AIET1.SSR_ITEM_TYPE= AIET.SSR_ITEM_TYPE AND AIET1.SCC_BRINCD_NLD = AIET.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 REA.SCC_CAF_ATTRIB_NM FROM PS_SSR_BR_REA_NLD REA WHERE REA.INSTITUTION = INST.INSTITUTION AND REA.SCC_BRINCD_NLD = INST.SCC_BRINCD_NLD AND REA.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 ATT.SSR_ATT_STAT >= '80') 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' ) AND RSLT.SSR_PE_RSN_CD NOT IN ( SELECT ERIC.SSR_BR_ERSN_CD_NLD FROM PS_SSR_BR_ERIC_NLD ERIC WHERE ERIC.INSTITUTION= ES.INSTITUTION AND ERIC.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND ERIC.EFFDT = ( SELECT MAX(EFFDT) FROM PS_SSR_BR_ERIC_NLD ERIC1 WHERE ERIC1.INSTITUTION= ERIC.INSTITUTION AND ERIC1.SCC_BRINCD_NLD = ERIC.SCC_BRINCD_NLD AND ERIC1.EFFDT <= INST.EFFDT))
|
INSTITUTION