SAD_PB_CASVW

(SQL View)
 Index Back

SELECT f.oprclass , A.emplid , B.last_name , B.first_name ,g.sex , f.birthdate_msk ,A.institution , A.acad_career ,a.stdnt_car_nbr ,a.sad_pb_rec_number ,CASE a.sad_pb_rec_type WHEN 'A' THEN ap_prg.acad_prog WHEN 'S' THEN ac_prg.acad_prog END ,CASE a.sad_pb_rec_type WHEN 'A' THEN ad_plan.acad_plan WHEN 'S' THEN ac_plan.acad_plan END ,CASE a.sad_pb_rec_type WHEN 'A' THEN ctr.adm_appl_ctr WHEN 'S' THEN ' ' END , A.adm_appl_nbr , A.appl_prog_nbr , A.sad_pb_rec_type , CASE a.sad_pb_rec_type WHEN 'A' THEN ap_prg.admit_term WHEN 'S' THEN ac_prg.admit_term END ,CASE a.sad_pb_rec_type WHEN 'A' THEN ap_prg.PROG_STATUS WHEN 'S' THEN ac_prg.PROG_STATUS END ,res.residency , B.last_name_srch , B.first_name_srch , A.sad_pb_cas_number , A.sad_pb_cas_status ,sad_pb_upload %Concat sad_pb_update %Concat sad_pb_report %Concat sad_pb_graduate FROM ps_sad_pb_cas A JOIN PS_PERSON_NAME B ON a.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON a.emplid = f.emplid LEFT OUTER JOIN PS_ADM_APPL_PROG ap_prg ON a.emplid = ap_prg.emplid AND a.acad_career = ap_prg.acad_career AND a.stdnt_car_nbr = ap_prg.stdnt_car_nbr AND a.adm_appl_nbr = ap_prg.adm_appl_nbr AND a.appl_prog_nbr = ap_prg.appl_prog_nbr LEFT OUTER JOIN PS_ACAD_PROG ac_prg ON a.emplid =ac_prg.emplid AND a.acad_career = ac_prg.acad_career AND a.stdnt_car_nbr = ac_prg.stdnt_car_nbr AND a.adm_appl_nbr = ac_prg.adm_appl_nbr AND a.appl_prog_nbr = ac_prg.appl_prog_nbr LEFT OUTER JOIN PS_ADM_APPL_PLAN ad_plan ON a.emplid = ad_plan.emplid AND a.acad_career = ad_plan.acad_career AND a.stdnt_car_nbr = ad_plan.stdnt_car_nbr AND a.adm_appl_nbr = ad_plan.adm_appl_nbr AND a.appl_prog_nbr = ad_plan.appl_prog_nbr LEFT OUTER JOIN PS_ACAD_PLAN ac_plan ON a.emplid = ac_plan.emplid AND a.acad_career = ac_plan.acad_career AND a.stdnt_car_nbr = ac_plan.stdnt_car_nbr LEFT OUTER JOIN ps_pers_data_effdt g ON a.emplid=g.emplid LEFT OUTER JOIN PS_ADM_APPL_DATA ctr ON a.emplid = ctr.emplid AND a.acad_career = ctr.acad_career AND a.stdnt_car_nbr = ctr.stdnt_car_nbr AND a.adm_appl_nbr = ctr.adm_appl_nbr LEFT OUTER JOIN ps_residency_off res ON a.emplid = res.emplid AND a.acad_career = res.acad_career AND a.institution =res.institution WHERE A.EFFDT=( SELECT MAX(EFF1.EFFDT) FROM PS_SAD_PB_CAS EFF1 WHERE EFF1.EMPLID=A.EMPLID AND EFF1.INSTITUTION=A.INSTITUTION AND EFF1.SAD_PB_REC_TYPE=A.SAD_PB_REC_TYPE AND EFF1.ACAD_CAREER=A.ACAD_CAREER AND EFF1.STDNT_CAR_NBR=A.STDNT_CAR_NBR AND EFF1.SAD_PB_REC_NUMBER=A.SAD_PB_REC_NUMBER AND EFF1.ADM_APPL_NBR=A.ADM_APPL_NBR AND EFF1.APPL_PROG_NBR=A.APPL_PROG_NBR) AND A.EFFSEQ=( SELECT MAX(EFF3.EFFSEQ) FROM PS_SAD_PB_CAS EFF3 WHERE EFF3.EMPLID=A.EMPLID AND EFF3.INSTITUTION=A.INSTITUTION AND EFF3.SAD_PB_REC_TYPE =A.SAD_PB_REC_TYPE AND EFF3.ACAD_CAREER=A.ACAD_CAREER AND EFF3.STDNT_CAR_NBR=A.STDNT_CAR_NBR AND EFF3.SAD_PB_REC_NUMBER =A.SAD_PB_REC_NUMBER AND EFF3.ADM_APPL_NBR=A.ADM_APPL_NBR AND EFF3.APPL_PROG_NBR=A.APPL_PROG_NBR AND EFF3.EFFDT=A.EFFDT ) AND ( AP_PRG.EFFDT IS NULL OR ( AP_PRG.EFFDT=( SELECT MAX(EFF4.EFFDT) FROM PS_ADM_APPL_PROG EFF4 WHERE EFF4.EMPLID=AP_PRG.EMPLID AND EFF4.ACAD_CAREER=AP_PRG.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=AP_PRG.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=AP_PRG.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=AP_PRG.APPL_PROG_NBR) AND AP_PRG.EFFSEQ=( SELECT MAX(EFF4.EFFSEQ) FROM PS_ADM_APPL_PROG EFF4 WHERE EFF4.EMPLID=AP_PRG.EMPLID AND EFF4.ACAD_CAREER=AP_PRG.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=AP_PRG.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=AP_PRG.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=AP_PRG.APPL_PROG_NBR AND EFF4.EFFDT=AP_PRG.EFFDT ) )) AND ( AC_PRG.EFFDT IS NULL OR ( AC_PRG.EFFDT=( SELECT MAX(EFF4.EFFDT) FROM PS_ACAD_PROG EFF4 WHERE EFF4.EMPLID=AC_PRG.EMPLID AND EFF4.ACAD_CAREER=AC_PRG.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=AC_PRG.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=AC_PRG.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=AC_PRG.APPL_PROG_NBR) AND AC_PRG.EFFSEQ=( SELECT MAX(EFF4.EFFSEQ) FROM PS_ACAD_PROG EFF4 WHERE EFF4.EMPLID=AC_PRG.EMPLID AND EFF4.ACAD_CAREER=AC_PRG.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=AC_PRG.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=AC_PRG.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=AC_PRG.APPL_PROG_NBR AND EFF4.EFFDT=AC_PRG.EFFDT ) )) AND ( ad_plan.EFFDT IS NULL OR ( ad_plan.EFFDT=( SELECT MAX(EFF4.EFFDT) FROM PS_ADM_APPL_PLAN EFF4 WHERE EFF4.EMPLID=ad_plan.EMPLID AND EFF4.ACAD_CAREER=ad_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ad_plan.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=ad_plan.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=ad_plan.APPL_PROG_NBR) AND ad_plan.EFFSEQ=( SELECT MAX(EFF4.EFFSEQ) FROM PS_ADM_APPL_PLAN EFF4 WHERE EFF4.EMPLID=ad_plan.EMPLID AND EFF4.ACAD_CAREER=ad_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ad_plan.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=ad_plan.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=ad_plan.APPL_PROG_NBR AND EFF4.EFFDT=ad_plan.EFFDT ) AND ad_plan.acad_plan = ( SELECT MIN(acad_plan) FROM PS_ADM_APPL_PLAN EFF4 WHERE EFF4.EMPLID=ad_plan.EMPLID AND EFF4.ACAD_CAREER=ad_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ad_plan.STDNT_CAR_NBR AND EFF4.ADM_APPL_NBR=ad_plan.ADM_APPL_NBR AND EFF4.APPL_PROG_NBR=ad_plan.APPL_PROG_NBR AND EFF4.EFFDT=ad_plan.EFFDT AND EFF4.EFFSEQ=ad_plan.EFFSEQ ) )) AND ( ac_plan.EFFDT IS NULL OR ( ac_plan.EFFDT=( SELECT MAX(EFF4.EFFDT) FROM PS_ACAD_PLAN EFF4 WHERE EFF4.EMPLID=ac_plan.EMPLID AND EFF4.ACAD_CAREER=ac_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ac_plan.STDNT_CAR_NBR) AND ac_plan.EFFSEQ=( SELECT MAX(EFF4.EFFSEQ) FROM PS_ACAD_PLAN EFF4 WHERE EFF4.EMPLID=ac_plan.EMPLID AND EFF4.ACAD_CAREER=ac_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ac_plan.STDNT_CAR_NBR AND EFF4.EFFDT=ac_plan.EFFDT ) AND ac_plan.plan_sequence = ( SELECT MIN(plan_sequence) FROM PS_ACAD_PLAN EFF4 WHERE EFF4.EMPLID=ac_plan.EMPLID AND EFF4.ACAD_CAREER=ac_plan.ACAD_CAREER AND EFF4.STDNT_CAR_NBR=ac_plan.STDNT_CAR_NBR AND EFF4.EFFDT=ac_plan.EFFDT AND EFF4.EFFSEQ=ac_plan.EFFSEQ ) )) AND g.effdt =( SELECT MAX(g1.EFFDT) FROM PS_pers_data_effdt g1 WHERE g1.EMPLID=a.EMPLID ) AND (( res.effective_term IS NULL ) OR res.effective_term = ( SELECT MAX(res1.effective_term ) FROM ps_residency_off res1 WHERE a.emplid = res1.emplid AND a.acad_career = res1.acad_career AND a.institution =res1.institution))

# 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 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
4 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
5 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
6 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
7 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

8 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_TBL

9 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
10 SAD_PB_REC_NUMBER Number(3,0) SMALLINT NOT NULL Record Number
11 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

Prompt Table: ACAD_PROG_TBLVW

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

Prompt Table: SAD_PB_PLANVW

13 ADM_APPL_CTR Character(4) VARCHAR2(4) NOT NULL Application Center
14 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr

Prompt Table: SAD_PB_ADMNBRVW

15 APPL_PROG_NBR Number(3,0) SMALLINT NOT NULL Application Program Nbr
16 SAD_PB_REC_TYPE Character(1) VARCHAR2(1) NOT NULL Record Type
A=Applicant
S=Student
17 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term

Default Value: OPR_DEF_TBL_CS.STRM

Prompt Table: SAD_PB_TERMVW

18 PROG_STATUS Character(4) VARCHAR2(4) NOT NULL 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
19 RESIDENCY Character(5) VARCHAR2(5) NOT NULL Residency
20 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
21 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
22 SAD_PB_CAS_NUMBER Character(14) VARCHAR2(14) NOT NULL CAS Number
23 SAD_PB_CAS_STATUS Character(4) VARCHAR2(4) NOT NULL CAS Status
ASG=ASSIGNED
CNC=CANCELLED
EXP=EXPIRED
OBS=OBSOLETE
USD=USED
WDR=WITHDRAWN
24 SAD_PB_PRC_STATUS Character(30) VARCHAR2(30) NOT NULL Processing Status

Prompt Table: SAD_PB_STATUSVW