SAD_UC_CNFVW

(SQL View)
 Index Back

Application Search View

Common view used when searching for/selecting a person for display/update. This view should be used as the basis for all other views which go against people. The PeopleCode contained on this view is important for any other view which is based on this one - the searching/formatting for names and setting the carry id is done with this code.

SELECT sc.oprid , ch.institution ,de.sad_uc_system , ch.sad_uc_pers_id , ch.sad_uc_appcode , ch.sad_uc_choiceno , b.emplid , b.last_name_srch , b.first_name_srch , b.last_name , b.first_name ,f.oprclass , f.birthdate_msk , ch.sad_uc_course , ch.sad_uc_campus , ch.sad_uc_entryyear , ch.adm_appl_nbr , ch.acad_career , ( SELECT prg.acad_prog FROM PS_SAD_APL_PROG_VW prg WHERE ch.institution= prg.institution AND ch.emplid = prg.emplid AND ch.acad_career = prg.acad_career AND ch.adm_appl_nbr = prg.adm_appl_nbr ) AS acad_prog, ( SELECT prg.admit_term FROM PS_SAD_APL_PROG_VW prg WHERE ch.institution= prg.institution AND ch.emplid = prg.emplid AND ch.acad_career = prg.acad_career AND ch.adm_appl_nbr = prg.adm_appl_nbr ) AS admit_term, ( SELECT prg.adm_appl_ctr FROM PS_SAD_APL_PROG_VW prg WHERE ch.institution= prg.institution AND ch.emplid = prg.emplid AND ch.acad_career = prg.acad_career AND ch.adm_appl_nbr = prg.adm_appl_nbr ) AS appl_ctr , de.sad_uc_decision, ch.sad_uc_reply FROM PS_SAD_UC_IVSTARC Ch JOIN PS_SAD_UC_DECISN DE ON ch.institution = de.institution AND ch.sad_uc_pers_id = de.sad_uc_pers_id AND ch.sad_uc_appcode = de.sad_uc_appcode AND ch.sad_uc_choiceno = de.sad_uc_choiceno JOIN PS_PERSON_NAME B ON ch.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON ch.emplid = f.emplid LEFT OUTER JOIN ps_scrty_appl_ctr SC ON ch.institution = sc.institution WHERE ((((de.sad_uc_decision = 'C' AND ch.sad_uc_reply NOT IN ('D', ' ')) OR (de.sad_uc_decision = 'D' AND ch.sad_uc_reply = 'F')) AND de.sad_uc_cnfrm <> 'Y' AND de.sad_uc_appr_status IN ('A','N') ) OR de.sad_uc_cnfrm = 'Y' ) AND %EffdtCheck(SAD_UC_IVSTARC EFF3, Ch,%CurrentDateIn) AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = ( SELECT prg.adm_appl_ctr FROM PS_SAD_APL_PROG_VW prg WHERE ch.institution= prg.institution AND ch.emplid = prg.emplid AND ch.acad_career = prg.acad_career AND ch.adm_appl_nbr = prg.adm_appl_nbr) OR sc.adm_appl_ctr = 'ALL')

# 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 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

3 SAD_UC_SYSTEM Character(4) VARCHAR2(4) NOT NULL UCAS System
APPL=Apply
GTTR=UTT
GTTR=UTT
UCAS=UCAS

Default Value: UCAS

4 SAD_UC_PERS_ID Character(10) VARCHAR2(10) NOT NULL Personalid - Unique personal identifier
5 SAD_UC_APPCODE Character(10) VARCHAR2(10) NOT NULL UCAS Application Code
6 SAD_UC_CHOICENO Number(3,0) SMALLINT NOT NULL UCAS Choice Number
7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

8 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
9 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
10 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
12 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
13 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
14 SAD_UC_COURSE Character(4) VARCHAR2(4) NOT NULL UCAS Course Code

Default Value: SAD_UC_USR_DFLT.SAD_UC_COURSE

Prompt Table: SAD_UC_COURSEVW

15 SAD_UC_CAMPUS Character(5) VARCHAR2(5) NOT NULL UCAS Campus

Default Value: SAD_UC_USR_DFLT.SAD_UC_CAMPUS

Prompt Table: SAD_UC_CAMPUSVW

16 SAD_UC_ENTRYYEAR Number(4,0) SMALLINT NOT NULL UCAS Entry Year

Default Value: SAD_UC_USR_DFLT.SAD_UC_ENTRYYEAR

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

Prompt Table: ACAD_PROG_TBL

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_DECISION Character(1) VARCHAR2(1) NOT NULL Decision
A=Accept
C=Conditional Offer
D=Delayed Confirmation
F=Full Course
I=Interview
N=None
R=Rejection
U=Unconditional Offer
W=Withdrawal
23 SAD_UC_REPLY Character(1) VARCHAR2(1) NOT NULL Reply
D=Declined
F=Firm Acceptance
I=Insurance Acceptance

Prompt Table: SAD_UC_REPLYVW