SSR_BR_EPVW2NLD (SQL View)	Index Back
Enrollment Period view select Used in Enrollment Period current views /07-04-2021: Fix for Bug 32714219 - Incorrect period data rows in ssr_br_epvw_nld view due to home campus tables acad org change/ /* 2021-01-07: Fix for Bug 32187682 - ENH: RIO/ BRON issue for delivery of changed educ provider on offered prog/enrol / / PVP (CY2) 2021-03-16: Fix to select home campus acad org change rows if the change is within the enrolment period / / 2021-10-06: fix for Bug-ID: 32929597 - RIOv3 */

SSR_BR_EPVW2NLD

(SQL View)

Enrollment Period view select

Used in Enrollment Period current views /*07-04-2021: Fix for Bug 32714219 - Incorrect period data rows in ssr_br_epvw_nld view due to home campus tables acad org change*/ /* 2021-01-07: Fix for Bug 32187682 - ENH: RIO/ BRON issue for delivery of changed educ provider on offered prog/enrol */ /* PVP (CY2) 2021-03-16: Fix to select home campus acad org change rows if the change is within the enrolment period */ /* 2021-10-06: fix for Bug-ID: 32929597 - RIOv3 */

( SELECT AP1.INSTITUTION , HC1.SCC_BRINCD_NLD , AP1.EMPLID , 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.EFFSEQ , AP1.ACAD_PROG , AP1.ACAD_LOAD_APPR , AP1.CAMPUS , SP.SSR_FORM_STUDY_NLD , SP.SSR_FUNDING_CD_NLD , HC1.ACAD_ORG 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_ACTNRSN_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_PRGACT_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_PRGACT_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_PRGACT_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 AP1.INSTITUTION , HC1.SCC_BRINCD_NLD , AP1.EMPLID , AP1.ACAD_CAREER , AP1.STDNT_CAR_NBR , SP.EFFDT , AP1.EFFSEQ , AP1.ACAD_PROG , AP1.ACAD_LOAD_APPR , AP1.CAMPUS , SP.SSR_FORM_STUDY_NLD , SP.SSR_FUNDING_CD_NLD , HC1.ACAD_ORG 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, SP.EFFDT) WHERE SP.SSR_PRG_ACTION_NLD IN (%Sql(SSR_BR_SETUP_PRG_ACT_HE_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD)) AND SP.EMPLID = (%Sql(SSR_BR_PRG_ACTNRSN_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_HE_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') ) UNION SELECT AP1.INSTITUTION , HC1.SCC_BRINCD_NLD , AP1.EMPLID , AP1.ACAD_CAREER , AP1.STDNT_CAR_NBR , HC1.EFFDT AS EFFDT , AP1.EFFSEQ , AP1.ACAD_PROG , AP1.ACAD_LOAD_APPR , AP1.CAMPUS , SP.SSR_FORM_STUDY_NLD , SP.SSR_FUNDING_CD_NLD ,HC1.ACAD_ORG FROM PS_ACAD_PROG AP1 JOIN PS_SSR_BR_HMCMP_VW HC1 ON AP1.INSTITUTION = HC1.INSTITUTION AND AP1.ACAD_PROG = HC1.ACAD_PROG AND AP1.CAMPUS = HC1.CAMPUS 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_ACTNRSN_EXISTS, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD,SP.EMPLID, SP.ACAD_CAREER, SP.STDNT_CAR_NBR, HC1.EFFDT)) AND SP.SSR_PRG_ACTION_NLD NOT IN (%Sql(SSR_BR_SETUP_DIPL_PRGACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, HC1.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 <= HC1.EFFDT AND SP_ED.SSR_PRG_ACTION_NLD NOT IN (%Sql(SSR_BR_SETUP_DIPL_PRGACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, HC1.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_PRGACT_SQL, AP1.INSTITUTION, HC1.SCC_BRINCD_NLD, HC1.EFFDT)) ) WHERE AP1.PROG_ACTION IN ('MATR', 'ACTV', 'DATA', 'PRGC', 'PLNC') 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 <= HC1.EFFDT AND AP1_ED.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')) AND HC1.EFFDT >= AP1.EFFDT AND (HC1.EFFDT < ( SELECT MIN(AP3.EFFDT) FROM PS_ACAD_PROG AP3 WHERE AP3.EMPLID = AP1.EMPLID AND AP3.ACAD_CAREER = AP1.ACAD_CAREER AND AP3.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP3.PROG_ACTION IN ( 'PRGC','COMP','WADM') AND AP3.EFFDT > AP1.EFFDT) OR NOT EXISTS ( SELECT 'X' FROM PS_ACAD_PROG AP3 WHERE AP3.EMPLID = AP1.EMPLID AND AP3.ACAD_CAREER = AP1.ACAD_CAREER AND AP3.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP3.PROG_ACTION IN ( 'PRGC','COMP','WADM') AND AP3.EFFDT > AP1.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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

4 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

5 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr

6 SSR_BR_ENR_ST_DT Date(10) DATE NOT NULL Start Date

7 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

8 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

9 ACAD_LOAD_APPR Character(1) VARCHAR2(1) NOT NULL Approved Academic Load
B=Vocational Coaching (NLD)
D=Dual (NLD)
E=Part-time Vocational Tr (NLD)
F=Full-Time
O=Vocational Training (NLD)
P=Part-Time

10 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

11 SSR_FORM_STUDY_NLD Character(4) VARCHAR2(4) NOT NULL Form of Study Higher Education
A=Auditor
D=Participant
E=Extraneus
S=Student
T=Coop/Dual

12 SSR_FUNDING_CD_NLD Character(1) VARCHAR2(1) NOT NULL Funding Applies

13 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

#	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	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	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
5	STDNT_CAR_NBR	Number(3,0)	SMALLINT NOT NULL	Student Career Nbr
6	SSR_BR_ENR_ST_DT	Date(10)	DATE NOT NULL	Start Date
7	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
8	ACAD_PROG	Character(5)	VARCHAR2(5) NOT NULL	Academic Program
9	ACAD_LOAD_APPR	Character(1)	VARCHAR2(1) NOT NULL	Approved Academic Load B=Vocational Coaching (NLD) D=Dual (NLD) E=Part-time Vocational Tr (NLD) F=Full-Time O=Vocational Training (NLD) P=Part-Time
10	CAMPUS	Character(5)	VARCHAR2(5) NOT NULL	Campus
11	SSR_FORM_STUDY_NLD	Character(4)	VARCHAR2(4) NOT NULL	Form of Study Higher Education A=Auditor D=Participant E=Extraneus S=Student T=Coop/Dual
12	SSR_FUNDING_CD_NLD	Character(1)	VARCHAR2(1) NOT NULL	Funding Applies
13	ACAD_ORG	Character(10)	VARCHAR2(10) NOT NULL	Academic Organization