SSR_TSTPRGNL_VW

(SQL View)
 Index Back

Test Admin VAVO Search Record


SELECT DISTINCT C.OPRID , A.EMPLID , F.NAME , A.INSTITUTION , A.ACAD_CAREER , A.STDNT_CAR_NBR , A.ACAD_PROG , A.EFFDT , A.PROG_STATUS , A.CAMPUS , A.ACAD_LOAD_APPR , G.STRM FROM PS_ACAD_PROG A , PS_SCRTY_TBL_PROG C , PS_HCR_PERSON_NM_I F , PS_STDNT_CAR_TERM G WHERE C.ACCESS_CD = 'Y' AND A.EMPLID = F.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.ACAD_CAREER = C.ACAD_CAREER AND A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.ACAD_CAREER = G.ACAD_CAREER AND A.STDNT_CAR_NBR = G.STDNT_CAR_NBR AND A.EFFDT = ( SELECT MAX(A_EF.EFFDT) FROM PS_ACAD_PROG A_EF WHERE A.EMPLID = A_EF.EMPLID AND A.ACAD_CAREER = A_EF.ACAD_CAREER AND A.STDNT_CAR_NBR = A_EF.STDNT_CAR_NBR AND A.EFFDT <= %CurrentDateIn AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT)) AND (A.ACAD_PROG = C.ACAD_PROG OR C.ACAD_PROG = 'ALL') AND NOT EXISTS ( SELECT 1 FROM PS_ACAD_PROG X WHERE A.EMPLID = X.EMPLID AND A.ACAD_CAREER = X.ACAD_CAREER AND A.STDNT_CAR_NBR = X.STDNT_CAR_NBR AND X.PROG_STATUS = 'AC' AND EXISTS ( SELECT 1 FROM PS_ACAD_PROG Y WHERE X.EMPLID = Y.EMPLID AND X.ACAD_CAREER = Y.ACAD_CAREER AND X.STDNT_CAR_NBR = Y.STDNT_CAR_NBR AND Y.PROG_STATUS IN ('CN','DC') AND X.EFFDT = Y.EFFDT AND Y.SCC_ROW_UPD_DTTM > X.SCC_ROW_UPD_DTTM) ) UNION SELECT DISTINCT C.OPRID , A.EMPLID , F.NAME , A.INSTITUTION , A.ACAD_CAREER , A.STDNT_CAR_NBR , A.ACAD_PROG , A.EFFDT , A.PROG_STATUS , A.CAMPUS , A.ACAD_LOAD_APPR ,G.STRM FROM PS_ACAD_PROG A , PS_SCRTY_TBL_PROG C , PS_HCR_PERSON_NM_I F , PS_STDNT_CAR_TERM G WHERE C.ACCESS_CD = 'Y' AND A.EMPLID = F.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.ACAD_CAREER = C.ACAD_CAREER AND A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.ACAD_CAREER = G.ACAD_CAREER AND A.ADMIT_TERM = G.STRM AND A.EFFDT = ( SELECT MAX(A_EF.EFFDT) FROM PS_ACAD_PROG A_EF WHERE A.EMPLID = A_EF.EMPLID AND A.ACAD_CAREER = A_EF.ACAD_CAREER AND A.STDNT_CAR_NBR = A_EF.STDNT_CAR_NBR AND A.EFFDT <= %CurrentDateIn AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT)) AND (A.ACAD_PROG = C.ACAD_PROG OR C.ACAD_PROG = 'ALL') AND NOT EXISTS ( SELECT 1 FROM PS_ACAD_PROG X WHERE A.EMPLID = X.EMPLID AND A.ACAD_CAREER = X.ACAD_CAREER AND A.STDNT_CAR_NBR = X.STDNT_CAR_NBR AND X.PROG_STATUS = 'AC' AND EXISTS ( SELECT 1 FROM PS_ACAD_PROG Y WHERE X.EMPLID = Y.EMPLID AND X.ACAD_CAREER = Y.ACAD_CAREER AND X.STDNT_CAR_NBR = Y.STDNT_CAR_NBR AND Y.PROG_STATUS IN ('CN','DC') AND X.EFFDT = Y.EFFDT AND Y.SCC_ROW_UPD_DTTM > X.SCC_ROW_UPD_DTTM) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTN_SCRTY

5 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

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

Default Value: 0

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

Prompt Table: ACAD_PROG_SCTY

8 EFFDT Date(10) DATE Effective Date

Default Value: %date

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 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

Prompt Table: CAMPUS_TBL

11 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
12 STRM Character(4) VARCHAR2(4) NOT NULL Term

Prompt Table: TERM_TBL_VW