|
SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.EMPLID , ES.SSR_BR_ENRL_SQ_NLD , AP.EFFDT ,'' /*Education provider for future use*/ ,'' /*BRON Location Code for future use*/ , APT.STUDY_FIELD , (%Sql(SSR_BR_GET_FND_BY_SQL, ES.ACAD_CAREER, AP.SAD_BR_FND_RSN_NLD)) END ,(%Sql(SSR_BR_LOAD_LOOKUPV_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, AP.ACAD_LOAD_APPR, AP.EFFDT)) END , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SSR_BR_ENVVWNLD ES JOIN ( SELECT HC1.SCC_BRINCD_NLD , AP1.EMPLID , AP1.INSTITUTION , AP1.ACAD_CAREER , AP1.STDNT_CAR_NBR , %Coalesce ( (SELECT TT.TERM_BEGIN_DT FROM PS_TERM_TBL TT WHERE TT.STRM = AP1.ADMIT_TERM AND TT.ACAD_CAREER =AP1.ACAD_CAREER AND TT.INSTITUTION = AP1.INSTITUTION and AP1.EFFDT < TT.TERM_BEGIN_DT and AP1.PROG_ACTION = 'MATR') , AP1.EFFDT) AS EFFDT , AP1.ACAD_PROG , AP1.ACAD_LOAD_APPR , AP1.CAMPUS , SP.SSR_FORM_STUDY_NLD , SP.SSR_FUNDING_CD_NLD , SP.SAD_BR_FND_RSN_NLD ,AP1.EFFSEQ FROM PS_ACAD_PROG AP1 JOIN PS_SCC_HM_CAMP_NLD HC1 ON AP1.ACAD_PROG = HC1.ACAD_PROG AND AP1.CAMPUS = HC1.CAMPUS AND %EffdtCheck(ACAD_PROG_TBL APT1, HC1, AP1.EFFDT) JOIN PS_SSR_STD_PRG_NLD SP ON SP.EMPLID = AP1.EMPLID AND SP.ACAD_CAREER = AP1.ACAD_CAREER AND SP.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND SP.EMPLID = (%Sql(SSR_BR_PRG_ACTNRSNV_EXISTS, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD,SP.EMPLID, SP.ACAD_CAREER, SP.STDNT_CAR_NBR, AP1.EFFDT)) AND SP.SSR_PRG_ACTION_NLD NOT IN (%Sql(SSR_BR_SETUP_DIPL_PRGACTV_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, AP1.EFFDT)) AND SP.EMPLID NOT IN (%Sql(SSR_BR_EXCLUDE_SEND, SP.EMPLID, SP.ACAD_CAREER, SP.STDNT_CAR_NBR)) AND SP.EFFDT = ( SELECT MAX(SP_ED.EFFDT) FROM PS_SSR_STD_PRG_NLD SP_ED WHERE SP_ED.EMPLID = SP.EMPLID AND SP_ED.ACAD_CAREER = SP.ACAD_CAREER AND SP_ED.STDNT_CAR_NBR = SP.STDNT_CAR_NBR AND SP_ED.EFFDT <= AP1.EFFDT AND SP_ED.SSR_PRG_ACTION_NLD NOT IN (%Sql(SSR_BR_SETUP_DIPL_PRGACTV_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, AP1.EFFDT)) ) AND SP.EFFSEQ = ( SELECT MAX(SP_ES.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP_ES WHERE SP_ES.EMPLID = SP.EMPLID AND SP_ES.ACAD_CAREER = SP.ACAD_CAREER AND SP_ES.STDNT_CAR_NBR = SP.STDNT_CAR_NBR AND SP_ES.EFFDT = SP.EFFDT AND SP_ES.SSR_PRG_ACTION_NLD NOT IN (%Sql(SSR_BR_SETUP_DIPL_PRGACTV_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, AP1.EFFDT)) ) WHERE AP1.PROG_ACTION IN ('MATR', 'ACTV', 'DATA', 'PRGC', 'PLNC') AND AP1.EFFSEQ=( SELECT MAX(AP1_ES.EFFSEQ) FROM PS_ACAD_PROG AP1_ES WHERE AP1.EMPLID=AP1_ES.EMPLID AND AP1.ACAD_CAREER=AP1_ES.ACAD_CAREER AND AP1.STDNT_CAR_NBR=AP1_ES.STDNT_CAR_NBR AND AP1.EFFDT=AP1_ES.EFFDT AND AP1_ES.PROG_ACTION IN ('MATR', 'ACTV', 'DATA', 'PRGC', 'PLNC')) UNION SELECT HC1.SCC_BRINCD_NLD , AP1.EMPLID , AP1.INSTITUTION , AP1.ACAD_CAREER , AP1.STDNT_CAR_NBR , SP.EFFDT , AP1.ACAD_PROG , AP1.ACAD_LOAD_APPR , AP1.CAMPUS , SP.SSR_FORM_STUDY_NLD , SP.SSR_FUNDING_CD_NLD , SP.SAD_BR_FND_RSN_NLD ,AP1.EFFSEQ FROM PS_SSR_STD_PRG_NLD SP JOIN PS_ACAD_PROG AP1 ON SP.EMPLID = AP1.EMPLID AND SP.ACAD_CAREER = AP1.ACAD_CAREER AND SP.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1.EFFDT = ( SELECT MAX(AP1_ED.EFFDT) FROM PS_ACAD_PROG AP1_ED WHERE AP1_ED.EMPLID = AP1.EMPLID AND AP1_ED.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ED.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ED.EFFDT <= SP.EFFDT) AND AP1.EFFSEQ = ( SELECT MAX(AP1_ES.EFFSEQ) FROM PS_ACAD_PROG AP1_ES WHERE AP1_ES.EMPLID = AP1.EMPLID AND AP1_ES.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ES.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ES.EFFDT = AP1.EFFDT) JOIN PS_SCC_HM_CAMP_NLD HC1 ON AP1.ACAD_PROG = HC1.ACAD_PROG AND AP1.CAMPUS = HC1.CAMPUS AND %EffdtCheck(ACAD_PROG_TBL APT1, HC1, AP1.EFFDT) WHERE SP.SSR_PRG_ACTION_NLD IN (%Sql(SSR_BR_SETUP_PRG_ACT_HEV_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD)) AND SP.EMPLID = (%Sql(SSR_BR_PRG_ACTNRSNV_EXISTS, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD,SP.EMPLID,SP.ACAD_CAREER, SP.STDNT_CAR_NBR, AP1.EFFDT)) AND SP.EMPLID NOT IN (%Sql(SSR_BR_EXCLUDE_SEND, SP.EMPLID, SP.ACAD_CAREER, SP.STDNT_CAR_NBR)) AND SP.EFFSEQ = ( SELECT MAX(SP_ES.EFFSEQ) FROM PS_SSR_STD_PRG_NLD SP_ES WHERE SP.EMPLID = SP_ES.EMPLID AND SP.ACAD_CAREER =SP_ES.ACAD_CAREER AND SP.STDNT_CAR_NBR = SP_ES.STDNT_CAR_NBR AND SP.EFFDT = SP_ES.EFFDT AND SP_ES.SSR_PRG_ACTION_NLD IN (%Sql(SSR_BR_SETUP_PRG_ACT_HEV_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD))) AND AP1.EFFSEQ=( SELECT MAX(AP1_ES.EFFSEQ) FROM PS_ACAD_PROG AP1_ES WHERE AP1.EMPLID=AP1_ES.EMPLID AND AP1.ACAD_CAREER=AP1_ES.ACAD_CAREER AND AP1.STDNT_CAR_NBR=AP1_ES.STDNT_CAR_NBR AND AP1.EFFDT=AP1_ES.EFFDT AND AP1_ES.PROG_ACTION IN ('MATR', 'ACTV', 'DATA', 'PRGC', 'PLNC') ) ) 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_ACAD_PLAN PL ON PL.ACAD_CAREER = AP.ACAD_CAREER AND PL.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND PL.EMPLID = AP.EMPLID AND PL.EFFDT = AP.EFFDT AND PL.EFFSEQ = AP.EFFSEQ AND PL.PLAN_SEQUENCE = ( SELECT MIN(PL1.PLAN_SEQUENCE) FROM PS_ACAD_PLAN PL1 WHERE PL1.ACAD_CAREER = AP.ACAD_CAREER AND PL1.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND PL1.EMPLID = AP.EMPLID AND PL1.EFFDT = AP.EFFDT AND PL1.EFFSEQ = AP.EFFSEQ ) JOIN PS_ACAD_PLAN_TBL APT ON APT.ACAD_PLAN = PL.ACAD_PLAN AND %EffdtCheck(ACAD_PLAN_TBL APT_ED, APT, AP.EFFDT)
|
INSTITUTION