SAD_UC_TRANVW

(SQL View)
 Index Back

SELECT sc.oprid , ch.institution , ch.sad_uc_pers_id , ch.emplid , b.last_name_srch , b.first_name_srch , b.last_name , b.first_name , f.oprclass , f.birthdate_msk , ch.sad_uc_appcode , ch.sad_uc_choiceno , a.sad_uc_appyear , ch.sad_uc_course , ch.sad_uc_campus , ch.sad_uc_entryyear , ch.adm_appl_nbr , %DatePart(trn.sad_uc_creation_dt ) sad_uc_date ,COALESCE((CASE WHEN trn.sad_uc_hold_flag='Y' THEN 'Y' ELSE 'N' END) ,'N') sad_uc_hold_flag ,COALESCE((CASE WHEN trn.sad_uc_error_code = '-2' THEN 'N' WHEN trn.sad_uc_error_code = '-1' THEN 'N' WHEN trn.sad_uc_error_code = '0' THEN 'N' WHEN trn.sad_uc_error_code = ' ' THEN 'N' WHEN trn.sad_uc_error_code IS NULL THEN 'N' ELSE 'Y' END) ,'N') sad_uc_trn_inerror ,COALESCE((CASE WHEN trn.sad_uc_sent_flag='N' THEN 'Y' ELSE 'N' END) ,'N') sad_uc_unsent_flag , ( SELECT sad_uc_tran_type FROM PS_SAD_UC_TRANIN t4 WHERE ch.institution = t4.institution AND ch.sad_uc_pers_id = t4.sad_uc_pers_id AND ch.sad_uc_appcode = t4.sad_uc_appcode AND ch.sad_uc_choiceno = t4.sad_uc_choiceno AND t4.sad_uc_creation_dt = ( SELECT MAX(trn1.sad_uc_creation_dt) FROM PS_SAD_UC_TRANIN trn1 WHERE trn1.institution = t4.institution AND trn1.sad_uc_pers_id = t4.sad_uc_pers_id AND trn1.sad_uc_appcode = t4.sad_uc_appcode AND trn1.sad_uc_choiceno = t4.sad_uc_choiceno) ) sad_uc_tran_type 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_APPLREC A ON ch.institution = a.institution AND ch.sad_uc_pers_id =A.sad_uc_pers_id AND ch.sad_uc_appcode =a.sad_uc_appcode LEFT OUTER JOIN PS_ADM_APPL_DATA E ON ch.institution = E.institution AND ch.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 E.institution = sc.institution LEFT OUTER JOIN PS_SAD_UC_TRANIN trn ON ch.institution = trn.institution AND ch.sad_uc_pers_id = trn.sad_uc_pers_id AND ch.sad_uc_appcode = trn.sad_uc_appcode AND ch.saD_uc_choiceno = trn.sad_uc_choiceno AND trn.sad_uc_obsolete <> 'Y' WHERE %EffdtCheck(SAD_UC_IVSTARC EFF2, Ch,%CurrentDateIn) AND ( E.emplid = ' ' OR ( 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 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 UCAS Application Code
12 SAD_UC_CHOICENO Number(3,0) SMALLINT NOT NULL UCAS Choice Number
13 SAD_UC_APPYEAR Character(4) VARCHAR2(4) NOT NULL UCAS Admissions Cycle
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 SAD_UC_DATE Date(10) DATE Created Date
19 SAD_UC_HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Transaction On Hold

Y/N Table Edit

Default Value: N

20 SAD_UC_TRN_INERROR Character(1) VARCHAR2(1) NOT NULL Transaction In Error

Y/N Table Edit

Default Value: N

21 SAD_UC_UNSENT_FLAG Character(1) VARCHAR2(1) NOT NULL Unsent Transaction

Y/N Table Edit

Default Value: N

22 SAD_UC_TRAN_TYPE Character(4) VARCHAR2(4) NOT NULL Transaction Type
AD=Adjustment Offer
AD=Adjustment Offer
LA=Amended Decision
LC=Course Correction
LD=Decision
PE=Enquiry
RA=UF Amendment
RD=Confirmation Decision
RQ=Clearing Form Request
RR=Release Into Clearing
RW=UF Withdrawal
RX=Clearing Decision