SSR_BR_ESD_SRCH

(SQL View)
 Index Back

Student EXS Search View

Common view used when searching for/selecting a student or applicant for display/update of extra support. /* bug 35484122 */

SELECT a.OPRCLASS , a.EMPLID , b.ACAD_CAREER , b.STDNT_CAR_NBR , b.ADM_APPL_NBR , aap.ACAD_PROG , aap.ADMIT_TERM , aap.PROG_STATUS , APT.DESCRSHORT FROM PS_PEOPLE_SRCH a , PS_SAD_STD_APP_NLD b JOIN PS_ADM_APPL_PROG aap ON b.EMPLID = aap.EMPLID AND b.ACAD_CAREER = aap.ACAD_CAREER AND b.STDNT_CAR_NBR = aap.STDNT_CAR_NBR AND b.ADM_APPL_NBR = aap.ADM_APPL_NBR AND b.APPL_PROG_NBR = aap.APPL_PROG_NBR AND b.EFFDT = aap.EFFDT AND b.EFFSEQ = aap.EFFSEQ JOIN PS_ACAD_PROG_TBL APT ON APT.INSTITUTION = AAP.INSTITUTION AND APT.ACAD_PROG = AAP.ACAD_PROG WHERE a.EMPLID = b.EMPLID AND aap.EFFDT = ( SELECT MAX(aap_ed.EFFDT) FROM PS_ADM_APPL_PROG aap_ed WHERE aap_ed.EMPLID = aap.EMPLID AND aap_ed.ACAD_CAREER = aap.ACAD_CAREER AND aap_ed.STDNT_CAR_NBR = aap.STDNT_CAR_NBR AND aap_ed.ADM_APPL_NBR = aap.ADM_APPL_NBR AND aap_ed.APPL_PROG_NBR = aap.APPL_PROG_NBR AND aap_ed.EFFDT <= %CurrentDateIn) AND aap.EFFSEQ = ( SELECT MAX(aap_es.EFFSEQ) FROM PS_ADM_APPL_PROG aap_es WHERE aap_es.EMPLID = aap.EMPLID AND aap_es.ACAD_CAREER = aap.ACAD_CAREER AND aap_es.STDNT_CAR_NBR = aap.STDNT_CAR_NBR AND aap_es.ADM_APPL_NBR = aap.ADM_APPL_NBR AND aap_es.APPL_PROG_NBR = aap.APPL_PROG_NBR AND aap_es.EFFDT = aap.EFFDT) AND %EffdtCheck( ACAD_PROG_TBL APT_ED, APT, %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_ACAD_PROG ap WHERE aap.EMPLID = ap.EMPLID AND aap.ACAD_CAREER = ap.ACAD_CAREER AND ap.ADM_APPL_NBR = aap.ADM_APPL_NBR ) UNION SELECT a.OPRCLASS , a.EMPLID , ap.ACAD_CAREER , ap.STDNT_CAR_NBR , ap.ADM_APPL_NBR , ap.ACAD_PROG , ap.ADMIT_TERM , ap.PROG_STATUS , APT.DESCRSHORT FROM PS_PEOPLE_SRCH a , PS_ACAD_PROG ap JOIN PS_ACAD_PROG_TBL APT ON APT.INSTITUTION = ap.INSTITUTION AND APT.ACAD_PROG = ap.ACAD_PROG WHERE a.EMPLID = ap.EMPLID AND ap.EFFDT = ( SELECT MAX(ap_ed.EFFDT) FROM PS_ACAD_PROG ap_ed WHERE ap_ed.EMPLID = ap.EMPLID AND ap_ed.ACAD_CAREER = ap.ACAD_CAREER AND ap_ed.STDNT_CAR_NBR = ap.STDNT_CAR_NBR AND ap_ed.EFFDT <= %CurrentDateIn) AND ap.EFFSEQ = ( SELECT MAX(ap_es.EFFSEQ) FROM PS_ACAD_PROG ap_es WHERE ap_es.EMPLID = ap.EMPLID AND ap_es.ACAD_CAREER = ap.ACAD_CAREER AND ap_es.STDNT_CAR_NBR = ap.STDNT_CAR_NBR AND ap_es.EFFDT = ap.EFFDT) AND %EffdtCheck( ACAD_PROG_TBL APT_ED, APT, %CurrentDateIn)

# 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
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 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
7 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term
8 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
9 PROG_SHORT_DESCR Character(10) VARCHAR2(10) NOT NULL Program Short Descr