SAD_UC_GTAPNVW

(SQL View)
 Index Back

GTTR Application Notes Srch Vw

GTTR Application Notes Search View

SELECT A.institution , A.sad_uc_pers_id , sc.oprid , A.emplid , b.last_name_srch , b.first_name_srch , b.last_name , b.first_name ,f.oprclass , f.birthdate_msk , A.sad_uc_appcode , ch.sad_uc_choiceno , ch.sad_uc_course , ch.sad_uc_campus , ch.sad_uc_entryyear , ch.adm_appl_nbr , ch.acad_career , prg.acad_prog , ( SELECT MIN(ch1.effdt) FROM PS_SAD_UC_IVSTARG ch1 WHERE ch.institution = ch1.institution AND ch.sad_uc_appcode = ch1.sad_uc_appcode AND ch.sad_uc_pers_id = ch1.sad_uc_pers_id AND ch.sad_uc_choiceno = ch1.sad_uc_choiceno) sad_uc_date , prg.admit_term , E.adm_appl_ctr ,A.saD_uc_system,decode(ch.sad_uc_gt_decision ,' ' ,'N' , ch.sad_uc_gt_decision) sad_uc_gt_decision ,ch.sad_uc_gt_reply FROM PS_SAD_UC_APPLREC A JOIN PS_PERSON_NAME B ON A.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON A.emplid = f.emplid JOIN PS_SAD_UC_IVSTARG Ch ON A.institution=ch.institution AND A.sad_uc_appcode=ch.sad_uc_appcode AND A.sad_uc_pers_id = ch.sad_uc_pers_id AND Ch.EFFDT=( SELECT MAX(EFF3.EFFDT) FROM PS_SAD_UC_IVSTARG EFF3 WHERE EFF3.INSTITUTION=Ch.INSTITUTION AND EFF3.SAD_UC_APPCODE=Ch.SAD_UC_APPCODE AND EFF3.SAD_UC_PERS_ID=Ch.SAD_UC_PERS_ID AND EFF3.SAD_UC_CHOICENO=Ch.SAD_UC_CHOICENO AND EFF3.EFFDT<= %CurrentDateIn) LEFT OUTER JOIN PS_ADM_APPL_PROG prg ON A.institution = prg.institution AND A.emplid = prg.emplid AND ch.acad_career = prg.acad_career AND ch.stdnt_car_nbr = prg.stdnt_car_nbr AND ch.adm_appl_nbr = prg.adm_appl_nbr LEFT OUTER JOIN PS_ADM_APPL_DATA E ON A.institution = e.institution AND A.emplid = e.emplid AND ch.acad_career = e.acad_career AND ch.stdnt_car_nbr = e.stdnt_car_nbr AND ch.adm_appl_nbr = e.adm_appl_nbr LEFT OUTER JOIN ps_scrty_appl_ctr SC ON A.institution = sc.institution AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr OR sc.adm_appl_ctr = 'ALL') WHERE ( prg.EFFDT IS NULL OR (prg.EFFDT = ( SELECT MAX(EF1.EFFDT) FROM PS_ADM_APPL_PROG EF1 WHERE EF1.EMPLID = prg.EMPLID AND EF1.ACAD_CAREER = prg.ACAD_CAREER AND EF1.STDNT_CAR_NBR = prg.STDNT_CAR_NBR AND EF1.ADM_APPL_NBR = prg.ADM_APPL_NBR AND EF1.APPL_PROG_NBR = prg.APPL_PROG_NBR AND EF1.EFFDT <= %CurrentDateIn) AND prg.EFFSEQ=( SELECT MAX(EF2.EFFSEQ) FROM PS_ADM_APPL_PROG EF2 WHERE EF2.EMPLID=prg.EMPLID AND EF2.ACAD_CAREER=prg.ACAD_CAREER AND EF2.STDNT_CAR_NBR=prg.STDNT_CAR_NBR AND EF2.ADM_APPL_NBR=prg.ADM_APPL_NBR AND EF2.APPL_PROG_NBR=prg.APPL_PROG_NBR AND EF2.EFFDT=prg.EFFDT ) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

2 SAD_UC_PERS_ID Character(10) VARCHAR2(10) NOT NULL Personalid - Unique personal identifier
3 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

5 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
6 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
9 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
10 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
11 SAD_UC_APPCODE Character(10) VARCHAR2(10) NOT NULL UTT Application Code
12 SAD_UC_CHOICENO Number(3,0) SMALLINT NOT NULL Round / Choice
13 SAD_UC_COURSE Character(4) VARCHAR2(4) NOT NULL UTT Course

Default Value: SAD_UC_USR_DFLT.SAD_UC_GT_COURSE

Prompt Table: SAD_UC_GTCRSEVW

14 SAD_UC_CAMPUS Character(5) VARCHAR2(5) NOT NULL UTT Campus

Default Value: SAD_UC_USR_DFLT.SAD_UC_GT_CAMPUS

Prompt Table: SAD_UC_GCAMPVW

15 SAD_UC_ENTRYYEAR Number(4,0) SMALLINT NOT NULL UTT Entry Year

Default Value: SAD_UC_USR_DFLT.SAD_UC_GTENTRYYEAR

16 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Number
17 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
18 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

Prompt Table: ACAD_PROG_TBL

19 SAD_UC_DATE Date(10) DATE Created Date
20 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term

Prompt Table: TERM_TBL

21 ADM_APPL_CTR Character(4) VARCHAR2(4) NOT NULL Application Center

Default Value: OPR_DEF_TBL_CS.ADM_APPL_CTR

Prompt Table: ADM_APPCTR_SCTY

22 SAD_UC_SYSTEM Character(4) VARCHAR2(4) NOT NULL UCAS System
APPL=Apply
GTTR=UTT
GTTR=UTT
UCAS=UCAS
23 SAD_UC_GT_DECISION Character(1) VARCHAR2(1) NOT NULL Decision
A=Accept
C=Conditional Offer
D=Delayed Conditional
E=Not qualified in English
G=Course Closed
I=Interview
M=Not qualified in Mathematics
N=None
R=Rejection
S=Not qualified in Science
U=Unconditional Offer
W=Withdrawal

Prompt Table: SAD_UC_GIDECVW

24 SAD_UC_GT_REPLY Character(1) VARCHAR2(1) NOT NULL Reply
D=Declined
F=Firm Acceptance