SSR_APT_PRG_SCH

(SQL View)
 Index Back

Student Academic Program

This is the search record for the Student Academic Program/Academic Plan panels.

SELECT b.oprclass , a.emplid , a.institution , a.ssr_apt_instance , 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_apt_hdr a , PS_PEOPLE_SRCH b , ps_acad_prog c WHERE a.emplid = b.emplid AND a.emplid = c.emplid AND a.institution = c.institution AND a.ssr_apt_instance = c.ssr_apt_instance AND a.stdnt_car_nbr = c.stdnt_car_nbr AND c.prog_status = 'AC' AND c.effdt = ( SELECT MAX(c1.effdt) FROM ps_acad_prog c1 WHERE a.emplid = c1.emplid AND a.acad_career = c1.acad_career AND a.stdnt_car_nbr = c1.stdnt_car_nbr AND c1.effdt <= %CurrentDateIn) AND c.effseq = ( SELECT MAX(c2.effseq) FROM ps_acad_prog c2 WHERE c.emplid = c2.emplid AND c.acad_career = c2.acad_career AND a.stdnt_car_nbr = c2.stdnt_car_nbr AND a.ssr_apt_instance = c2.ssr_apt_instance AND c.effdt = c2.effdt) AND EXISTS ( SELECT 'x' FROM ps_acad_prog c2 WHERE c.emplid = c2.emplid AND c.institution = c2.institution AND c2.ssr_apt_instance <> 0 AND c2.ssr_apt_instance <> c.ssr_apt_instance) UNION SELECT b.oprclass , a.emplid , a.institution , a.ssr_apt_instance , 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_apt_hdr a , PS_PEOPLE_SRCH b , ps_acad_prog c WHERE a.emplid = b.emplid AND a.emplid = c.emplid AND a.institution = c.institution AND a.ssr_apt_instance = c.ssr_apt_instance AND a.stdnt_car_nbr = c.stdnt_car_nbr AND c.prog_status = 'AC' AND c.effdt > %CurrentDateIn AND C.EFFDT < ( SELECT MAX(c3.effdt) FROM ps_acad_prog c3 WHERE c.emplid = c3.emplid AND c.institution = c3.institution AND c.stdnt_car_nbr = c3.stdnt_car_nbr AND c.acad_career = c3.acad_career AND c3.effdt > %CurrentDateIn) AND C.EFFSEQ = ( SELECT MAX(c4.effseq) FROM ps_acad_prog c4 WHERE c.emplid = c4.emplid AND c.institution = c4.institution AND c.stdnt_car_nbr = c4.stdnt_car_nbr AND c.acad_career = c4.acad_career AND c.ssr_apt_instance = c4.ssr_apt_instance AND c.effdt = c4.effdt) AND EXISTS ( SELECT 'x' FROM ps_acad_prog c2 WHERE c.emplid = c2.emplid AND c.institution = c2.institution AND c2.ssr_apt_instance <> 0 AND c2.ssr_apt_instance <> a.ssr_apt_instance)

# 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

Prompt Table: INSTITUTION_TBL

4 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
7 BIRTHDATE Date(10) DATE Date of Birth
8 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
9 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
10 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
11 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
12 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
13 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
14 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
15 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
16 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name