SAD_UC_CHOICEVW

(SQL View)
 Index Back

Confirmation Enquiry context


SELECT ch.INSTITUTION , ch.SAD_UC_PERS_ID , ch.SAD_UC_APPCODE , ch.SAD_UC_CHOICENO , ch.EFFDT , ch.EMPLID , ch.sad_uc_inst , ch.SAD_UC_COURSE , ch.SAD_UC_CAMPUS , ch.SAD_UC_ENTRYYEAR , ch.SAD_UC_ENTRYMONTH , ch.SAD_UC_ENTRY_POINT , ch.SAD_UC_FACULTY , ch.SAD_UC_DECISION , ch.SAD_UC_DECISION_DT , ch.SAD_UC_REPLY , ch.SAD_UC_SUMMRY_COND , ch.sad_uc_prev_course , ch.sad_uc_prev_campus , ch.ADM_APPL_NBR , ch.ACAD_CAREER , E.ADM_APPL_CTR , D.PROG_ACTION , D.PROG_REASON , F.SCRIPT_TEXT , F.DESCR250 , G.TEL_CONTACT_1 , G.TEL_CONTACT_2 FROM %Table(SAD_UC_IVSTARC) ch LEFT OUTER JOIN PS_ADM_APPL_PROG D ON ch.INSTITUTION = d.INSTITUTION AND ch.emplid = d.emplid AND ch.acad_career = d.acad_career AND ch.stdnt_car_nbr = d.stdnt_car_nbr AND ch.adm_appl_nbr = d.adm_appl_nbr LEFT OUTER JOIN PS_ADM_APPL_DATA E ON D.INSTITUTION = e.INSTITUTION AND D.emplid = e.emplid AND D.acad_career = e.acad_career AND D.stdnt_car_nbr = e.stdnt_car_nbr AND D.adm_appl_nbr = e.adm_appl_nbr LEFT OUTER JOIN PS_PROG_RSN_TBL F ON D.INSTITUTION = F.SETID AND D.PROG_ACTION = F.PROG_ACTION AND D.PROG_REASON = F.PROG_REASON LEFT OUTER JOIN PS_ADM_APPLCTR_TBL G ON E.INSTITUTION = G.INSTITUTION AND E.ADM_APPL_CTR = G.ADM_APPL_CTR WHERE %EffdtCheck(SAD_UC_IVSTARC EFF1, ch,%CurrentDateIn) AND (D.EFFDT IS NULL OR ( D.EFFDT = ( SELECT MAX(EF1.EFFDT) FROM PS_ADM_APPL_PROG EF1 WHERE EF1.EMPLID = D.EMPLID AND EF1.ACAD_CAREER = D.ACAD_CAREER AND EF1.STDNT_CAR_NBR = D.STDNT_CAR_NBR AND EF1.ADM_APPL_NBR = D.ADM_APPL_NBR AND EF1.APPL_PROG_NBR = D.APPL_PROG_NBR AND EF1.EFFDT <= %CurrentDateIn) AND D.EFFSEQ=( SELECT MAX(EF2.EFFSEQ) FROM PS_ADM_APPL_PROG EF2 WHERE EF2.EMPLID=D.EMPLID AND EF2.ACAD_CAREER=D.ACAD_CAREER AND EF2.STDNT_CAR_NBR=D.STDNT_CAR_NBR AND EF2.ADM_APPL_NBR=D.ADM_APPL_NBR AND EF2.APPL_PROG_NBR=D.APPL_PROG_NBR AND EF2.EFFDT=D.EFFDT ) AND ( F.effdt IS NULL OR %EffdtCheck(PROG_RSN_TBL EFF2, F, %CurrentDateIn) ) AND %EffdtCheck(ADM_APPLCTR_TBL EFF3, G, %CurrentDateIn)) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SAD_UC_PERS_ID Character(10) VARCHAR2(10) NOT NULL Personalid - Unique personal identifier
3 SAD_UC_APPCODE Character(10) VARCHAR2(10) NOT NULL Application Code
4 SAD_UC_CHOICENO Number(3,0) SMALLINT NOT NULL Choice Number
5 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
7 SAD_UC_INST Character(3) VARCHAR2(3) NOT NULL UCAS Institution Code
8 SAD_UC_COURSE Character(4) VARCHAR2(4) NOT NULL UCAS Course Code
9 SAD_UC_CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus
10 SAD_UC_ENTRYYEAR Number(4,0) SMALLINT NOT NULL Entry Year
11 SAD_UC_ENTRYMONTH Number(2,0) SMALLINT NOT NULL Entry Month
12 SAD_UC_ENTRY_POINT Number(1,0) SMALLINT NOT NULL UCAS Entry Point
13 SAD_UC_FACULTY Character(20) VARCHAR2(20) NOT NULL Faculty
14 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
15 SAD_UC_DECISION_DT DateTime(26) TIMESTAMP Decision Date/Time
16 SAD_UC_REPLY Character(1) VARCHAR2(1) NOT NULL Reply
D=Declined
F=Firm Acceptance
I=Insurance Acceptance
17 SAD_UC_SUMMRY_COND Character(6) VARCHAR2(6) NOT NULL Summary Condition
18 SAD_UC_PREV_COURSE Character(4) VARCHAR2(4) NOT NULL Previous Course
19 SAD_UC_PREV_CAMPUS Character(3) VARCHAR2(3) NOT NULL Previous Campus
20 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
21 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
22 ADM_APPL_CTR Character(4) VARCHAR2(4) NOT NULL Application Center
23 PROG_ACTION Character(4) VARCHAR2(4) NOT NULL Program Action
ACTV=Activate
ADMT=Admit
ADRV=Admission Revocation
APPL=Application
COMP=Completion of Program
COND=Conditional Admit
DATA=Data Change
DDEF=Defer Decision
DEFR=Defer Enrollment
DEIN=Intention to Matriculate
DENY=Deny
DISC=Discontinuation
DISM=Dismissal
LEAV=Leave of Absence
MATR=Matriculation
PLNC=Plan Change
PRGC=Program Change
RADM=Readmit
RAPP=Readmit Application
RECN=Reconsideration
REVK=Revoke Degree
RLOA=Return from Leave of Absence
SPND=Suspension
TRAN=Transfer to Other Career
VDIS=Voluntarily Discontinued
WADM=Administrative Withdrawal
WAIT=Waitlist
WAOF=Waitlist Offer
WAPP=Applicant Withdrawal
24 PROG_REASON Character(4) VARCHAR2(4) NOT NULL Action Reason
25 SCRIPT_TEXT Long Character(2000) VARCHAR2(2000) Script Text
26 DESCR250 Character(250) VARCHAR2(250) NOT NULL Long Description
27 TEL_CONTACT_1 Character(40) VARCHAR2(40) NOT NULL Telephone Contact 1
28 TEL_CONTACT_2 Character(40) VARCHAR2(40) NOT NULL Telephone Contact 2