ADM_PROG_PLN_VW (SQL View)	Index Back
UNION of Adm and Prog stack This view is used to join to various security views in order to select student's plan inforamtion that live in either the admissions stack (ADM_APPL_PLAN) or the records/program stack (ACAD_PLAN). NOTE: Unions were removed for Informix, Sybase and DB2/MVS. These platform specific SQL only grabs from ACAD_PLAN.

ADM_PROG_PLN_VW

(SQL View)

UNION of Adm and Prog stack

This view is used to join to various security views in order to select student's plan inforamtion that live in either the admissions stack (ADM_APPL_PLAN) or the records/program stack (ACAD_PLAN). NOTE: Unions were removed for Informix, Sybase and DB2/MVS. These platform specific SQL only grabs from ACAD_PLAN.

SELECT PRG.EMPLID , PRG.ACAD_CAREER , PRG.STDNT_CAR_NBR , PRG.EFFDT , PRG.EFFSEQ , PRG.ACAD_PLAN FROM PS_ACAD_PLAN PRG WHERE PRG.EFFDT = ( SELECT MAX(PRG1.EFFDT) FROM PS_ACAD_PLAN PRG1 WHERE PRG1.EMPLID = PRG.EMPLID AND PRG1.ACAD_CAREER = PRG.ACAD_CAREER AND PRG1.STDNT_CAR_NBR = PRG.STDNT_CAR_NBR AND PRG1.ACAD_PLAN = PRG.ACAD_PLAN AND PRG1.EFFDT <= %CurrentDateIn) AND PRG.EFFSEQ = ( SELECT MAX(PRG2.EFFSEQ) FROM PS_ACAD_PLAN PRG2 WHERE PRG2.EMPLID = PRG.EMPLID AND PRG2.ACAD_CAREER = PRG.ACAD_CAREER AND PRG2.STDNT_CAR_NBR = PRG.STDNT_CAR_NBR AND PRG2.EFFDT = PRG.EFFDT AND PRG2.ACAD_PLAN = PRG.ACAD_PLAN) UNION SELECT ADM.EMPLID , ADM.ACAD_CAREER , ADM.STDNT_CAR_NBR , ADM.EFFDT , ADM.EFFSEQ , ADM.ACAD_PLAN FROM PS_ADM_APPL_PLAN ADM WHERE ADM.EFFDT = ( SELECT MAX(ADM1.EFFDT) FROM PS_ADM_APPL_PLAN ADM1 WHERE ADM1.EMPLID = ADM.EMPLID AND ADM1.ACAD_CAREER = ADM.ACAD_CAREER AND ADM1.STDNT_CAR_NBR = ADM.STDNT_CAR_NBR AND ADM1.ADM_APPL_NBR = ADM.ADM_APPL_NBR AND ADM1.APPL_PROG_NBR = ADM.APPL_PROG_NBR AND ADM1.EFFDT <= %CurrentDateIn AND ADM1.ACAD_PLAN = ADM.ACAD_PLAN) AND ADM.EFFSEQ = ( SELECT MAX(ADM2.EFFSEQ) FROM PS_ADM_APPL_PLAN ADM2 WHERE ADM2.EMPLID = ADM.EMPLID AND ADM2.ACAD_CAREER = ADM.ACAD_CAREER AND ADM2.STDNT_CAR_NBR = ADM.STDNT_CAR_NBR AND ADM2.ADM_APPL_NBR = ADM.ADM_APPL_NBR AND ADM2.APPL_PROG_NBR = ADM.APPL_PROG_NBR AND ADM2.EFFDT = ADM.EFFDT AND ADM2.ACAD_PLAN = ADM.ACAD_PLAN)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 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

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

4 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

6 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	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
3	STDNT_CAR_NBR	Number(3,0)	SMALLINT NOT NULL	Student Career Nbr
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
6	ACAD_PLAN	Character(10)	VARCHAR2(10) NOT NULL	Academic Plan