SAD_UC_APDEC_VW(SQL View) |
Index Back |
|---|---|
Application Search View |
| SELECT a.institution , a.sad_uc_pers_id , sc.oprid , a.SAD_UC_SUPPORT_REF , a.emplid , a.sad_uc_timestamp , 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_inst , ch.sad_uc_course , ch.sad_uc_campus , ch.sad_uc_entryyear , ch.SAD_UC_APP_STATUS , chd.descr , STADC.descr , g.SAD_UC_EXP_STATUS , ch.ADM_APPL_NBR , ch.SAD_UC_STUDY_MODE, ch.SAD_UC_DEFER_CYCLE 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 LEFT OUTER 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.sad_uc_system = 'APPL' LEFT OUTER JOIN PS_SAD_UC_APST2_VW CHD ON CHD.SAD_UC_APP_STATUS = CH.SAD_UC_APP_STATUS LEFT OUTER JOIN PS_ADM_APPL_PROG D ON A.institution = d.institution AND A.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 A.institution = e.institution AND A.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_scrty_appl_ctr SC ON A.institution = sc.institution LEFT OUTER JOIN PS_SAD_UC_APDECISN g ON a.institution = g.institution AND a.sad_uc_appcode = g.sad_uc_appcode AND a.sad_uc_pers_id = g.sad_uc_pers_id LEFT OUTER JOIN PS_SAD_UC_APST2_VW STADC ON STADC.SAD_UC_APP_STATUS = g.SAD_UC_APP_STATUS WHERE a.sad_uc_system = 'APPL' AND ( ch.EFFDT IS NULL OR ch.EFFDT=( SELECT MAX(EFF1.EFFDT) FROM PS_SAD_UC_IVSTARG EFF1 WHERE EFF1.INSTITUTION=ch.INSTITUTION AND EFF1.SAD_UC_APPCODE=ch.SAD_UC_APPCODE AND EFF1.SAD_UC_PERS_ID=ch.SAD_UC_PERS_ID AND EFF1.SAD_UC_CHOICENO=ch.SAD_UC_CHOICENO AND eff1.sad_uc_system =ch.sad_uc_system AND EFF1.EFFDT<=%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 sc.access_cd = 'Y' AND (sc.adm_appl_ctr = E.adm_appl_ctr OR sc.adm_appl_ctr = 'ALL') ) ) AND a.sad_uc_timestamp = ( SELECT MAX(A1.sad_uc_timestamp) FROM PS_SAD_UC_APPLREC a1 WHERE a1.institution = a.institution AND a1.sad_uc_appcode = a.sad_uc_appcode AND a1.sad_uc_pers_id = a.sad_uc_pers_id AND a1.emplid = a.emplid AND a1.sad_uc_system = a.sad_uc_system) AND (g.EFFDT IS NULL OR g.EFFDT=( SELECT MAX(g1.EFFDT) FROM PS_SAD_UC_APDECISN g1 WHERE g1.INSTITUTION=ch.INSTITUTION AND g1.SAD_UC_APPCODE=ch.SAD_UC_APPCODE AND g1.SAD_UC_PERS_ID=ch.SAD_UC_PERS_ID)) |
| # | 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 | SAD_UC_SUPPORT_REF | Character(10) | VARCHAR2(10) NOT NULL | Field for DFE API |
| 5 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PEOPLE_SRCH |
| 6 | SAD_UC_TIMESTAMP |
DateTime(26) | TIMESTAMP | Timestamp |
| 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_APPCODE |
Character(10) | VARCHAR2(10) NOT NULL | Application ID |
| 14 | SAD_UC_INST | Character(3) | VARCHAR2(3) NOT NULL |
Provider Code
Default Value: SAD_UC_USR_DFLT.SAD_UC_INST Prompt Table: SAD_UC_PCODE_VW |
| 15 | SAD_UC_COURSE | Character(4) | VARCHAR2(4) NOT NULL |
Course
Default Value: SAD_UC_USR_DFLT.SAD_UC_AP_COURSE Prompt Table: SAD_UC_ACRC4_VW |
| 16 | SAD_UC_CAMPUS | Character(5) | VARCHAR2(5) NOT NULL | Campus |
| 17 | SAD_UC_ENTRYYEAR | Number(4,0) | SMALLINT NOT NULL |
Entry Year
Default Value: SAD_UC_USR_DFLT.SAD_UC_APENTRYYEAR |
| 18 | SAD_UC_APP_STATUS | Character(30) | VARCHAR2(30) NOT NULL |
Application Status
Prompt Table: SAD_UC_APST2_VW |
| 19 | SASD_UC_APST_DESC | Character(30) | VARCHAR2(30) NOT NULL |
Application Status Description
Prompt Table: SAD_UC_APST2_VW |
| 20 | SAD_UC_APP_STADEC | Character(30) | VARCHAR2(30) NOT NULL |
Application status for decision record search viewa
Prompt Table: SAD_UC_APST2_VW |
| 21 | SAD_UC_EXP_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Field for DFE Apply
C=Completed E=Error N=New R=Resend |
| 22 | ADM_APPL_NBR | Character(8) | VARCHAR2(8) NOT NULL | Application Nbr |
| 23 | SAD_UC_STUDY_MODE | Character(10) | VARCHAR2(10) NOT NULL | Study Mode |
| 24 | SAD_UC_DEFER_CYCLE | Number(4,0) | SMALLINT NOT NULL | Deferred To Cycle |