SAD_BR_M321_NLD

(SQL View)
 Index Back

321 mirror view

Added a check for effdt < term begin date with MATR and added a effseq to make sure we pick up the right rows from acad_prog when there are multiple rows on one day.

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.SAD_BR_EDU_SEC_NLD , ES.EMPLID , ES.SAD_BR_ENRL_SQ_NLD , AP.EFFDT , AP.SSR_FUNDING_CD_NLD , APT.HEGIS_CODE , CASE WHEN AP.SSR_FORM_STUDY_NLD = 'E' THEN ' ' ELSE (%Sql(SAD_BR_LOOKUP_CAR_LD_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP.ACAD_LOAD_APPR, AP.EFFDT)) END , CASE WHEN AP.SSR_FORM_STUDY_NLD = 'E' THEN 'EX' ELSE (%Sql(SAD_BR_LOAD_LOOKUP_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP.ACAD_LOAD_APPR, AP.EFFDT)) END , (%Sql(SAD_BR_ACAD_PROG_LVL_SQL, ES.INSTITUTION, ES.SCC_BRINCD_NLD, ES.SAD_BR_EDU_SEC_NLD, AP.ACAD_PROG, AP.EFFDT)) , (%Sql(SAD_BR_NHR_LOC_CD_SQL, ES.INSTITUTION, AP.CAMPUS)) , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SAD_BR_ENRSQNLD 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 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.SSR_PRG_ACTION_NLD NOT IN (%Sql(SAD_BR_SETUP_LNV_PRG_ACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, 'BO', AP1.EFFDT)) 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(SAD_BR_SETUP_LNV_PRG_ACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, 'BO', 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(SAD_BR_SETUP_LNV_PRG_ACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, 'BO', 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 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(SAD_BR_SETUP_PRG_ACT_HE_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, 'BO')) 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(SAD_BR_SETUP_PRG_ACT_HE_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, 'BO'))) 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.SAD_BR_EDU_SEC_NLD = 'BO' AND ES.EMPLID = AP.EMPLID AND ES.ACAD_CAREER = AP.ACAD_CAREER AND ES.STDNT_CAR_NBR = AP.STDNT_CAR_NBR JOIN PS_ACAD_PROG_TBL APT ON APT.ACAD_PROG = AP.ACAD_PROG AND %EffdtCheck(ACAD_PROG_TBL APT_ED, APT, AP.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
3 SAD_BR_EDU_SEC_NLD Character(2) VARCHAR2(2) NOT NULL Educational Sector
BO=Professional Education
ED=Education
VA=VAVO
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 SAD_BR_ENRL_SQ_NLD Character(3) VARCHAR2(3) NOT NULL Enrollment Sequence number
6 SAD_BR_ENR_ST_DT Date(10) DATE NOT NULL Eff Enrollment Start Date
7 SAD_BR_FUNDING_NLD Character(1) VARCHAR2(1) NOT NULL Funding Indicator

Y/N Table Edit

Default Value: N

8 HEGIS_CODE Character(8) VARCHAR2(8) NOT NULL HEGIS Code
9 SAD_BR_CAREER_NLD Character(3) VARCHAR2(3) NOT NULL BRON Career
BBL=BBL
BOL=BOL
CBL=CBL
COL=COL
10 SAD_BR_LOAD_NLD Character(2) VARCHAR2(2) NOT NULL BRON Intensity
DT=Parttime
EX=Exam Student
VT=Fulltime
11 SAD_BR_LEVEL_NLD Character(1) VARCHAR2(1) NOT NULL BRON Level
1=Level 1
2=Level 2
3=Level 3
4=Level 4
12 SAD_BR_LOC_CD_NLD Character(12) VARCHAR2(12) NOT NULL Location Code
13 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
14 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr