ADM_PROG_STK_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 students that live in either the admissions stack (ADMAPPL_PROG) or the records/program stack (ACAD_PROG). NOTE: Unions were removed for Informix, Sybase and DB2/MVS. These platform specific SQL only grabs from ACAD_PROG.

ADM_PROG_STK_VW

(SQL View)

UNION of Adm and Prog stack

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

SELECT PRG.EMPLID ,PRG.CAMPUS , PRG.ACAD_CAREER , PRG.STDNT_CAR_NBR , PRG.EFFDT , PRG.EFFSEQ , PRG.INSTITUTION , PRG.ACAD_PROG FROM PS_ACAD_PROG PRG WHERE PRG.EFFDT = ( SELECT MAX(PRG1.EFFDT) FROM PS_ACAD_PROG PRG1 WHERE PRG1.INSTITUTION = PRG.INSTITUTION AND PRG1.EMPLID = PRG.EMPLID AND PRG1.ACAD_CAREER = PRG.ACAD_CAREER AND PRG1.STDNT_CAR_NBR = PRG.STDNT_CAR_NBR AND PRG1.EFFDT <= %CurrentDateIn) AND PRG.EFFSEQ = ( SELECT MAX(PRG2.EFFSEQ) FROM PS_ACAD_PROG PRG2 WHERE PRG2.INSTITUTION = PRG.INSTITUTION AND 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) UNION SELECT ADM.EMPLID ,ADM.CAMPUS , ADM.ACAD_CAREER , ADM.STDNT_CAR_NBR , ADM.EFFDT , ADM.EFFSEQ , ADM.INSTITUTION , ADM.ACAD_PROG FROM PS_ADM_APPL_PROG ADM WHERE ADM.EFFDT = ( SELECT MAX(ADM1.EFFDT) FROM PS_ADM_APPL_PROG ADM1 WHERE ADM1.INSTITUTION = ADM.INSTITUTION AND 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 ADM.EFFSEQ = ( SELECT MAX(ADM2.EFFSEQ) FROM PS_ADM_APPL_PROG ADM2 WHERE ADM2.INSTITUTION = ADM.INSTITUTION AND 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)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

3 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

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

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

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

7 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

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

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