SSR_PRGGRD_SRCH

(SQL View)
 Index Back

Program Degree Search

This is a search record for the Graduation Tracking component. It returns the max effective dated academic program/plans where the program status is Active or Complete, the expected graduation term is not blank and the plan is degree seeking.

SELECT B.OPRCLASS , A.EMPLID , A.INSTITUTION , A.ACAD_CAREER , A.STDNT_CAR_NBR , A.ACAD_PROG , A.EXP_GRAD_TERM , D.DEGREE , C.ACAD_PLAN , ' ' , B.NAME , B.SEX , B.BIRTHDATE , B.BIRTHDATE_MSK , B.CAMPUS_ID , B.NATIONAL_ID , B.NATIONAL_ID_MSK , B.NID_COUNTRY , B.NATIONAL_ID_TYPE , B.NID_DESCRSHORT , B.LAST_NAME_SRCH , B.FIRST_NAME_SRCH FROM PS_ACAD_PROG A , PS_PEOPLE_SRCH B , PS_ACAD_PLAN C , PS_ACAD_PLAN_TBL D WHERE A.EMPLID = B.EMPLID AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_ACAD_PROG E WHERE E.EMPLID = A.EMPLID AND E.ACAD_CAREER = A.ACAD_CAREER AND E.STDNT_CAR_NBR = A.STDNT_CAR_NBR) AND A.EFFSEQ = ( SELECT MAX(F.EFFSEQ) FROM PS_ACAD_PROG F WHERE F.EMPLID = A.EMPLID AND F.ACAD_CAREER = A.ACAD_CAREER AND F.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND F.EFFDT = A.EFFDT) AND A.EXP_GRAD_TERM <> ' ' AND A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND A.EFFDT = C.EFFDT AND A.EFFSEQ = C.EFFSEQ AND D.INSTITUTION=A.INSTITUTION AND D.ACAD_PLAN = C.ACAD_PLAN AND D.DEGREE <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_STDNT_GRAD X WHERE X.EMPLID = A.EMPLID AND X.INSTITUTION = A.INSTITUTION AND X.ACAD_CAREER = A.ACAD_CAREER AND X.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND X.ACAD_PROG = A.ACAD_PROG AND X.EXP_GRAD_TERM = A.EXP_GRAD_TERM) UNION SELECT B.OPRCLASS , A.EMPLID , A.INSTITUTION , A.ACAD_CAREER , A.STDNT_CAR_NBR , A.ACAD_PROG , A.EXP_GRAD_TERM , A.DEGREE , ' ' , A.SSR_GRAD_STATUS , B.NAME , B.SEX , B.BIRTHDATE , B.BIRTHDATE_MSK , B.CAMPUS_ID , B.NATIONAL_ID , B.NATIONAL_ID_MSK , B.NID_COUNTRY , B.NATIONAL_ID_TYPE , B.NID_DESCRSHORT , B.LAST_NAME_SRCH , B.FIRST_NAME_SRCH FROM PS_SSR_STDNT_GRAD A , PS_PEOPLE_SRCH B WHERE A.EMPLID = B.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

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

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTN_SCRTY

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

Default Value: OPR_DEF_TBL_CS.ACAD_CAREER

Prompt Table: ACAD_CAR_SCRTY

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

Default Value: 0

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

Default Value: OPR_DEF_TBL_CS.ACAD_PROG

Prompt Table: ACAD_PROG_SCTY

7 EXP_GRAD_TERM Character(4) VARCHAR2(4) NOT NULL Expected Graduation Term

Prompt Table: TERM_TBL

8 DEGREE Character(8) VARCHAR2(8) NOT NULL Degree

Prompt Table: DEGREE_INTRN_VW

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

Prompt Table: ACAD_PLAN_SVW2

10 SSR_GRAD_STATUS Character(4) VARCHAR2(4) NOT NULL eLearning Status which is used to derive the INTERNET field on SDR Course Register File
11 NAME Character(50) VARCHAR2(50) NOT NULL Name
12 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
13 BIRTHDATE Date(10) DATE Date of Birth
14 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
15 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
16 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
17 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
18 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
19 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
20 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
21 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
22 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name