|
SELECT DISTINCT A.INSTITUTION ,B.SSR_RIO_PRG_UUID ,E.STRM || '-' || C.SSR_COHORT_ID AS SSR_RI_COH_COD_NLD ,C.SSR_COH_STAT_NLD ,CASE WHEN C.SSR_TERMSES_NLD = 'T' THEN (E.TERM_BEGIN_DT - C.FROM_DAY) ELSE CASE WHEN C.SSR_TERMSES_NLD = 'S' THEN (F.SESS_BEGIN_DT - C.FROM_DAY) END END AS SSR_RI_ADMS_DT_NLD , CASE WHEN ( F.SESS_BEGIN_DT = H.END_DT OR H.TIME_PERIOD IS NULL) THEN F.SESS_BEGIN_DT END AS SSR_RI_COH_ST_DT ,CASE WHEN C.SSR_TERMSES_NLD = 'T' THEN (E.TERM_BEGIN_DT + C.TO_DAY) ELSE CASE WHEN C.SSR_TERMSES_NLD = 'S' THEN (F.SESS_BEGIN_DT + C.TO_DAY) END END AS SSR_RI_ADME_DT_NLD ,CASE WHEN (F.SESS_BEGIN_DT <> H.END_DT) THEN (F.SESS_BEGIN_DT) END AS SSR_RI_ENT_ST_NLD ,CASE WHEN (F.SESS_BEGIN_DT <> H.END_DT) THEN (H.END_DT) END AS SSR_RI_ENT_END_NLD ,C.SSR_COH_ACC_CD_NLD AS SSR_RI_ACC_CD_NLD ,'N' AS SSR_RI_PERMREQ_NLD ,'' AS SSR_RI_PERMEXP_NLD , C.SSR_RIO_SC_NLD , C.SSR_RIO_OC_NLD , CASE WHEN ( F.SESS_BEGIN_DT = H.END_DT OR H.TIME_PERIOD IS NULL) THEN CASE WHEN C1.SSR_PROG_NORM_NLD = 'Y' THEN %DateAdd(F.SESS_BEGIN_DT,365*D.PROG_NORM_COMPLTN) WHEN C1.SSR_PROG_NORM_NLD = 'M' THEN %DateAdd(F.SESS_BEGIN_DT,%Truncate(365/12*D.PROG_NORM_COMPLTN,0)) WHEN C1.SSR_PROG_NORM_NLD = 'W' THEN %DateAdd(F.SESS_BEGIN_DT,7*D.PROG_NORM_COMPLTN) ELSE %DateNull END ELSE %DateNull END , E.STRM , E.ACAD_CAREER FROM PS_SSR_RI_EPS_NLD A , PS_SSR_RIO_PRG_NLD B , PS_SCC_CMPCHRT_NLD C JOIN PS_SSR_PRG_NRM_NLD C1 ON C.INSTITUTION = C1.INSTITUTION AND C.ACAD_PROG = C1.ACAD_PROG AND C.EFFDT = C1.EFFDT , PS_ACAD_PROG_TBL D , PS_TERM_TBL E , (PS_SESSION_TBL F LEFT OUTER JOIN PS_SESS_TIME_PEROD H ON F.INSTITUTION = H.INSTITUTION AND F.ACAD_CAREER = H.ACAD_CAREER AND F.STRM = H.STRM AND F.SESSION_CODE = H.SESSION_CODE AND H.TIME_PERIOD = '100') ,PS_SCC_HM_CAMP_NLD I ,PS_SCC_CMPLOAD_NLD J WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_SSR_RI_EPS_NLD A_ED WHERE A.INSTITUTION = A_ED.INSTITUTION AND A_ED.EFFDT <= %CurrentDateIn) AND A.INSTITUTION = B.INSTITUTION AND B.INSTITUTION = I.INSTITUTION AND B.ACAD_PROG = I.ACAD_PROG AND B.CAMPUS = I.CAMPUS AND I.EFFDT = D.EFFDT AND B.INSTITUTION = J.INSTITUTION AND B.ACAD_PROG = J.ACAD_PROG AND B.CAMPUS = J.CAMPUS AND B.ACAD_LOAD_APPR = J.ACAD_LOAD_APPR AND J.EFFDT = D.EFFDT AND B.INSTITUTION = C.INSTITUTION AND B.ACAD_PROG = C.ACAD_PROG AND B.CAMPUS = C.CAMPUS AND B.ACAD_LOAD_APPR = C.ACAD_LOAD_APPR AND B.ACAD_ORG = I.ACAD_ORG AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_ACAD_PROG_TBL D_ED WHERE D.INSTITUTION = D_ED.INSTITUTION AND D.ACAD_PROG = D_ED.ACAD_PROG AND D_ED.EFFDT <= %CurrentDateIn) AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_SCC_CMPCHRT_NLD C_ED WHERE C_ED.INSTITUTION = C.INSTITUTION AND C_ED.ACAD_PROG = C.ACAD_PROG AND C_ED.CAMPUS = C.CAMPUS AND C_ED.ACAD_LOAD_APPR = C.ACAD_LOAD_APPR AND C_ED.SSR_COHORT_ID = C.SSR_COHORT_ID AND C_ED.EFFDT <= F.SESS_BEGIN_DT) AND C.INSTITUTION = D.INSTITUTION AND C.ACAD_PROG = D.ACAD_PROG AND D.INSTITUTION = E.INSTITUTION AND E.ACAD_CAREER = D.ACAD_CAREER AND E.INSTITUTION = F.INSTITUTION AND E.ACAD_CAREER = F.ACAD_CAREER AND E.STRM = F.STRM AND F.SESSION_CODE = C.SESSION_CODE AND E.ACAD_CAREER IN ( SELECT DISTINCT XLSE.ACAD_CAREER FROM PS_SSR_BR_XLSE_NLD XLSE WHERE XLSE.INSTITUTION = A.INSTITUTION AND %EffdtCheck(SSR_BR_XLSE_NLD XLSE_ED, XLSE, %CURRENTDATEIN) ) AND E.STRM <= CASE WHEN C.STRM2 = ' ' THEN ( SELECT DISTINCT (G.STRM) FROM PS_TERM_TBL G WHERE (%DateAdd(%CurrentDateIn,365) BETWEEN G.TERM_BEGIN_DT AND G.TERM_END_DT) AND G.ACAD_CAREER = E.ACAD_CAREER AND G.INSTITUTION = E.INSTITUTION) ELSE C.STRM2 END AND E.STRM >= C.STRM1 AND C.SSR_RIO_YN_NLD = 'Y'
|
INSTITUTION