|
SELECT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID , ES.SAD_BR_ENRL_SQ_NLD , APT.HEGIS_CODE , %Coalesce ( (SELECT TT.TERM_BEGIN_DT FROM PS_TERM_TBL TT WHERE TT.STRM = AP.ADMIT_TERM AND TT.ACAD_CAREER = AP.ACAD_CAREER AND TT.INSTITUTION = AP.INSTITUTION and AP.EFFDT < TT.TERM_BEGIN_DT) , AP.EFFDT) AS ENROLLMENT_DT , CASE WHEN (INST.SAD_BR_TRM_END_NLD) = ('O') THEN ( SELECT OWO.END_DATE FROM PS_SAD_STD_CON_NLD OWO WHERE OWO.INSTITUTION = AP.INSTITUTION AND OWO.ACAD_CAREER = AP.ACAD_CAREER AND OWO.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND OWO.EMPLID = AP.EMPLID AND OWO.STATUS = 'A' AND OWO.SAD_CONTRACTNR_NLD = (%Sql(SAD_BR_OWO_MAX_CONT_NLD, OWO.INSTITUTION, OWO.EMPLID, OWO.ACAD_CAREER, OWO.STDNT_CAR_NBR, OWO.STATUS))) ELSE CASE WHEN (INST.SAD_BR_TRM_END_NLD) = ('E') THEN ( SELECT T.term_end_dt FROM ps_term_tbl T WHERE T.institution = AP.INSTITUTION AND T.acad_career = AP.ACAD_CAREER AND T.strm = AP.EXP_GRAD_TERM ) END END AS SAD_BR_LEAVEDT_NLD, (%Sql(SAD_BR_VA_ENDDATE_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP2.PROG_ACTION,AP2.EFFDT)) AS SAD_BR_ENR_END_DT , (%Sql(SAD_BR_GET_HIGH_PRIO_SQL, AP.EMPLID)) AS SAD_BR_PRIO_ED_NLD , CASE WHEN ( SELECT ARR.STDNT_GROUP FROM PS_STDNT_GRPS_HIST ARR WHERE ARR.EMPLID = ES.EMPLID AND ARR.INSTITUTION = ES.INSTITUTION AND ARR.STDNT_GROUP = INST.SAD_BR_NEWVAL_NLD AND ARR.EFF_STATUS = 'A' AND ARR.EFFDT = ( SELECT MAX(ARR1.EFFDT) FROM PS_STDNT_GRPS_HIST ARR1 WHERE ARR.EMPLID = ARR1.EMPLID AND ARR.INSTITUTION = ARR1.INSTITUTION AND ARR.STDNT_GROUP = ARR1.STDNT_GROUP AND ARR1.EFF_STATUS = 'A' AND ARR1.EFFDT <= %CurrentDateIn)) IS NULL THEN 'N' ELSE 'Y' END AS SAD_BR_NEW_ARR_NLD , %Coalesce(( SELECT OWO_1.DURATION_HOURS FROM PS_SAD_STD_CON_NLD OWO_1 WHERE OWO_1.INSTITUTION = AP.INSTITUTION AND OWO_1.ACAD_CAREER = AP.ACAD_CAREER AND OWO_1.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND OWO_1.EMPLID = AP.EMPLID AND OWO_1.STATUS = 'A' AND OWO_1.SAD_CONTRACTNR_NLD = (%Sql(SAD_BR_OWO_MAX_CONT_NLD, OWO_1.INSTITUTION, OWO_1.EMPLID, OWO_1.ACAD_CAREER, OWO_1.STDNT_CAR_NBR, OWO_1.STATUS))), 0) AS DURATION_HOURS ,(%Sql(SAD_BR_LOAD_LOOKUP_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP.ACAD_LOAD_APPR, AP.EFFDT)) AS SAD_BR_LOAD_NLD , %Coalesce((%Sql(SAD_BR_VA_FND_BY_NLD, AP.EMPLID, AP.ACAD_CAREER, AP.STDNT_CAR_NBR, AP.EFFDT)), '') AS SAD_BR_FND_BY_NLD , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SAD_BR_ENRSQ_VA 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 IN (%Sql(SAD_BR_GET_PR_AC_ENR_VA_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP.EFFDT)) JOIN PS_ACAD_PROG_TBL APT ON AP.ACAD_PROG = APT.ACAD_PROG AND AP.INSTITUTION = APT.INSTITUTION JOIN PS_SSR_STD_PRG_NLD SP ON AP.EMPLID = SP.EMPLID AND AP.EFFDT = SP.EFFDT AND AP.EFFSEQ = SP.EFFSEQ AND AP.ACAD_CAREER = SP.ACAD_CAREER AND AP.STDNT_CAR_NBR = SP.STDNT_CAR_NBR AND SP.SAD_BR_EX_BRON_NLD <> 'Y' JOIN PS_SAD_BR_VA_NLD INST ON INST.INSTITUTION = ES.INSTITUTION AND INST.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD 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 ( SELECT DISTINCT ENRN.PROG_ACTION FROM PS_SAD_BR_ENREN_VA ENRN WHERE ENRN.INSTITUTION = ES.INSTITUTION AND ENRN.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND ENRN.SAD_BR_EDU_SEC_NLD = ES.SAD_BR_EDU_SEC_NLD AND ENRN.EFFDT = ( SELECT MAX(ENRN1.EFFDT) FROM PS_SAD_BR_ENREN_VA ENRN1 WHERE ENRN.INSTITUTION = ENRN1.INSTITUTION AND ENRN.SCC_BRINCD_NLD = ENRN1.SCC_BRINCD_NLD AND ENRN.SAD_BR_EDU_SEC_NLD = ENRN1.SAD_BR_EDU_SEC_NLD AND ENRN1.EFFDT <= AP2.EFFDT )) WHERE (%Sql(SAD_BR_VA_GET_NBR_ACPR_TOSEND, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, ES.EMPLID, ES.ACAD_CAREER, ES.STDNT_CAR_NBR)) > 0 AND APT.EFFDT = ( SELECT MAX(APT_ED.EFFDT) FROM PS_ACAD_PROG_TBL APT_ED WHERE APT_ED.ACAD_PROG = APT.ACAD_PROG AND APT_ED.EFFDT <= AP.EFFDT) AND INST.EFFDT = ( SELECT MAX(INST_ED.EFFDT) FROM PS_SAD_BR_VA_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)
|
INSTITUTION