SAD_UC_RAPRVW

(SQL View)
 Index Back

Release Approval Search View

Search view for Release to clearing approval page

SELECT sc.oprid , ch.institution , ch.sad_uc_pers_id , ch.sad_uc_appcode , ch.sad_uc_choiceno , ch.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 , prg.acad_prog , prg.admit_term , E.adm_appl_ctr FROM PS_SAD_UC_IVSTARC Ch JOIN PS_PERSON_NAME B ON ch.emplid = b.emplid JOIN PS_PERSON_MSK_VW F ON ch.emplid = f.emplid JOIN PS_SAD_UC_DECISN DEC ON ch.institution = dec.institution AND ch.sad_uc_pers_id = dec.sad_uc_pers_id AND ch.sad_uc_appcode = dec.sad_uc_appcode AND ch.sad_uc_choiceno = dec.sad_uc_choiceno LEFT OUTER JOIN PS_ADM_APPL_PROG prg ON ch.institution = prg.institution AND ch.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 prg.institution = e.institution AND prg.emplid = e.emplid AND prg.acad_career = e.acad_career AND prg.stdnt_car_nbr = e.stdnt_car_nbr AND prg.adm_appl_nbr = e.adm_appl_nbr LEFT OUTER JOIN ps_scrty_appl_ctr SC ON E.institution = sc.institution WHERE dec.sad_uc_appr_status='P' AND dec.sad_uc_rel_clr='Y' AND %EffdtCheck(SAD_UC_IVSTARC EFF3, Ch,%CurrentDateIn) AND ( 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 ) AND sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr 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_PERS_ID Character(10) VARCHAR2(10) NOT NULL Personalid - Unique personal identifier
4 SAD_UC_APPCODE Character(10) VARCHAR2(10) NOT NULL UCAS Application Code
5 SAD_UC_CHOICENO Number(3,0) SMALLINT NOT NULL UCAS Choice Number
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
8 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
10 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
11 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
12 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
13 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

14 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

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

Default Value: SAD_UC_USR_DFLT.SAD_UC_ENTRYYEAR

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 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term

Prompt Table: TERM_TBL

20 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