SAD_SL_ADCRR_VW

(SQL View)
 Index Back

Studielink Admissions Current


SELECT A.EMPLID ,D.EXTERNAL_SYSTEM_ID ,A.ACAD_CAREER ,A.STDNT_CAR_NBR ,A.ADM_APPL_NBR ,A.APPL_PROG_NBR ,A.EFFDT ,A.EFFSEQ ,A.PROG_STATUS ,A.INSTITUTION ,A.ACAD_PROG ,A.ACAD_LOAD_APPR ,C.SSR_SL_PROG_CD_NLD ,C.SCC_BRINCD_NLD ,B.ACAD_PLAN ,C.SSR_SL_ACAD_YR_NLD ,C.SCC_SUBBRINCD_NLD ,C.SSR_FORM_STUDY_NLD ,C.SSR_ACAD_LEVEL_NLD ,A.CAMPUS ,C.SSR_SL_STATUS_NLD ,C.SAD_SL_ENRLSQ_NLD FROM PS_ADM_APPL_PROG A ,PS_ADM_APPL_PLAN B ,PS_SAD_STD_APP_NLD C ,PS_EXTERNAL_SYSTEM D WHERE A.EMPLID =B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR =B.ADM_APPL_NBR AND A.APPL_PROG_NBR = B.APPL_PROG_NBR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EMPLID =C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND A.ADM_APPL_NBR =C.ADM_APPL_NBR AND A.APPL_PROG_NBR = C.APPL_PROG_NBR AND A.EFFDT = C.EFFDT AND A.EFFSEQ = C.EFFSEQ AND A.EMPLID = D.EMPLID AND D.EXTERNAL_SYSTEM ='SLN' AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_EXTERNAL_SYSTEM D1 WHERE D.EMPLID = D1.EMPLID AND D.EXTERNAL_SYSTEM = D1.EXTERNAL_SYSTEM AND D1.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ADM_APPL_PROG A1 WHERE A.EMPLID =A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.STDNT_CAR_NBR = A1.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A.APPL_PROG_NBR = A1.APPL_PROG_NBR AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_ADM_APPL_PROG A2 WHERE A.EMPLID =A2.EMPLID AND A.ACAD_CAREER = A2.ACAD_CAREER AND A.STDNT_CAR_NBR = A2.STDNT_CAR_NBR AND A.ADM_APPL_NBR = A2.ADM_APPL_NBR AND A.APPL_PROG_NBR = A2.APPL_PROG_NBR AND A.EFFDT =A2.EFFDT) AND A.PROG_STATUS <> 'AC' UNION SELECT A.EMPLID ,D.EXTERNAL_SYSTEM_ID ,A.ACAD_CAREER ,A.STDNT_CAR_NBR ,A.ADM_APPL_NBR ,A.APPL_PROG_NBR ,A.EFFDT ,A.EFFSEQ ,A.PROG_STATUS ,A.INSTITUTION ,A.ACAD_PROG ,A.ACAD_LOAD_APPR ,C.SSR_SL_PROG_CD_NLD ,C.SCC_BRINCD_NLD ,B.ACAD_PLAN ,C.ACAD_YEAR ,C.SCC_SUBBRINCD_NLD ,C.SSR_FORM_STUDY_NLD ,C.SSR_ACAD_LEVEL_NLD ,A.CAMPUS ,C.SSR_SL_STATUS_NLD ,C.SAD_SL_ENRLSQ_NLD FROM PS_ACAD_PROG A , PS_ACAD_PLAN B , PS_SSR_STD_PRG_NLD C , PS_EXTERNAL_SYSTEM D , PS_TERM_TBL E WHERE A.EMPLID =B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EMPLID =C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND A.EMPLID = D.EMPLID AND A.INSTITUTION = E.INSTITUTION AND A.ACAD_CAREER = E.ACAD_CAREER AND C.ACAD_YEAR = E.ACAD_YEAR AND D.EXTERNAL_SYSTEM ='SLN' AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_EXTERNAL_SYSTEM D1 WHERE D.EMPLID = D1.EMPLID AND D.EXTERNAL_SYSTEM = D1.EXTERNAL_SYSTEM AND D1.EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ACAD_PROG A1 WHERE A.EMPLID =A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.STDNT_CAR_NBR = A1.STDNT_CAR_NBR AND A1.EFFDT <= E.TERM_END_DT) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_ACAD_PROG A2 WHERE A.EMPLID =A2.EMPLID AND A.ACAD_CAREER = A2.ACAD_CAREER AND A.STDNT_CAR_NBR = A2.STDNT_CAR_NBR AND A.EFFDT =A2.EFFDT) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_SSR_STD_PRG_NLD C1 WHERE C.EMPLID =C1.EMPLID AND C.ACAD_CAREER = C1.ACAD_CAREER AND C.STDNT_CAR_NBR = C1.STDNT_CAR_NBR AND C1.EFFDT <= E.TERM_END_DT) AND C.EFFSEQ = ( SELECT MAX(C2.EFFSEQ) FROM PS_SSR_STD_PRG_NLD C2 WHERE C.EMPLID =C2.EMPLID AND C.ACAD_CAREER = C2.ACAD_CAREER AND C.STDNT_CAR_NBR = C2.STDNT_CAR_NBR AND C.EFFDT =C2.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EXTERNAL_SYSTEM_ID Character(20) VARCHAR2(20) NOT NULL External System ID
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 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
6 APPL_PROG_NBR Number(3,0) SMALLINT NOT NULL Application Program Nbr
7 EFFDT Date(10) DATE Effective Date

Default Value: %date

8 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
9 PROG_STATUS Character(4) VARCHAR2(4) NOT NULL Academic Program Status
AC=Active in Program
AD=Admitted
AP=Applicant
CM=Completed Program
CN=Cancelled
DC=Discontinued
DE=Deceased
DM=Dismissed
LA=Leave of Absence
PM=Prematriculant
SP=Suspended
WT=Waitlisted
10 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
11 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
12 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
13 SSR_SL_PROG_CD_NLD Character(5) VARCHAR2(5) NOT NULL Studielink Program Code
14 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
15 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan
16 SSR_SL_ACAD_YR_NLD Character(4) VARCHAR2(4) NOT NULL Academic Year
17 SCC_SUBBRINCD_NLD Character(2) VARCHAR2(2) NOT NULL Sub Institute
18 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
19 SSR_ACAD_LEVEL_NLD Character(4) VARCHAR2(4) NOT NULL Academ. Level Higher Education
A=Associate Degree
B=Bachelor
D=Propaedeutic Bachelor
H=Main
I=Initial Phase
K=Candidate
L=University teacher education
M=Master
P=Propaedeutic Phase
Q=Post-Initial
T=Interim Doctoral Exam
V=Continuation Phase
W=Continuing study
20 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus
21 SSR_SL_STATUS_NLD Character(1) VARCHAR2(1) NOT NULL Studielink Status
B=Continuing Application
G=Cancelled/Withdrawn
I=Registered/Enrolled
N=No Re-Enrollment
R=Rejected/Denied
S=Study Suspended
U=Departed
V=Enrollment Requested
X=Not a Studielink Participant
22 SAD_SL_ENRLSQ_NLD Character(20) VARCHAR2(20) NOT NULL Studielink Enroll Seq Nbr