AV_ACAD_PROF_VW(SQL View) |
Index Back |
---|---|
Academic InformationAcademic Information, which is used in AV_REL_ACAD_VW |
SELECT A.EMPLID , A.AV_DEGREE_TYPE , A.AV_INST_ORG_ID , A.INSTITUTION_NAME , %DateNull , A.AV_YEAR , A.ACCOMPLISHMENT , A.AV_DEGREE_DESCR , AC.ACAD_CAREER , X.XLATLONGNAME , A.AV_MAJOR1_CD , A.AV_MAJOR1_NAME , A.AV_MAJOR2_CD , A.AV_MAJOR2_NAME , A.AV_MINOR1_CD , A.AV_MINOR1_NAME , A.AV_MINOR2_CD , A.AV_MINOR2_NAME FROM PS_AV_INT_DEG_VW A , PS_ACAD_DEGR AC , PSXLATITEM X WHERE A.EMPLID = AC.EMPLID AND A.STDNT_DEGR = AC.STDNT_DEGR AND X.FIELDNAME = 'ACAD_CAREER' AND X.FIELDVALUE = AC.ACAD_CAREER AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn) UNION SELECT B.EMPLID , B.AV_DEGREE_TYPE , B.AV_INST_ORG_ID , B.INSTITUTION_NAME , %DateNull , B.AV_YEAR , B.ACCOMPLISHMENT , B.AV_DEGREE_DESCR , '' , '' , B.AV_MAJOR1_CD , B.AV_MAJOR1_NAME , B.AV_MAJOR2_CD , B.AV_MAJOR2_NAME , B.AV_MINOR1_CD , B.AV_MINOR1_NAME , B.AV_MINOR2_CD , B.AV_MINOR2_NAME FROM PS_AV_LEG_DEG_VW B UNION SELECT EXT_DEG.EMPLID , 'E' , EXT_DEG.EXT_ORG_ID , EXT_ORG.DESCR , EXT_DEG.DEGREE_DT , '' , EXT_DEG.DEGREE , DEG.DESCR , EXT_DEG.EXT_CAREER , XLAT.XLATLONGNAME , EXT_DEG.EXT_SUBJ_AREA_1 , EXT_DEG.FIELD_OF_STUDY_1 , EXT_DEG.EXT_SUBJ_AREA_2 , EXT_DEG.FIELD_OF_STUDY_2 , '' , '' , '' , '' FROM PS_EXT_DEGREE EXT_DEG , PS_EXT_ORG_TBL EXT_ORG , PS_DEGREE_TBL DEG , PSXLATITEM XLAT WHERE EXT_ORG.EXT_ORG_ID = EXT_DEG.EXT_ORG_ID AND EXT_ORG.EFFDT = ( SELECT MAX(EXT_ORG1.EFFDT) FROM PS_EXT_ORG_TBL EXT_ORG1 WHERE EXT_ORG1.EXT_ORG_ID = EXT_ORG.EXT_ORG_ID AND EXT_ORG1.EFFDT <= %CurrentDateIn) AND EXT_ORG.EFF_STATUS = 'A' AND EXT_DEG.DEGREE = DEG.DEGREE AND DEG.EFFDT = ( SELECT MAX(DEG1.EFFDT) FROM PS_DEGREE_TBL DEG1 WHERE DEG1.DEGREE = DEG.DEGREE AND DEG1.EFFDT <= %CurrentDateIn) AND DEG.EFF_STATUS = 'A' AND XLAT.FIELDNAME = 'EXT_CAREER' AND XLAT.FIELDVALUE = EXT_DEG.EXT_CAREER AND XLAT.EFFDT = ( SELECT MAX(XLAT1.EFFDT) FROM PSXLATITEM XLAT1 WHERE XLAT1.FIELDNAME = XLAT.FIELDNAME AND XLAT1.FIELDVALUE = XLAT.FIELDVALUE AND XLAT1.EFFDT <= %CurrentDateIn) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
2 | AV_DEGREE_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
The degree type. Internal, External or legacy.
E=External Degree I=Internal Degree L=Legacy Degree |
3 | AV_INST_ORG_ID | Character(11) | VARCHAR2(11) NOT NULL | Institution or EXT_ORG_ID container |
4 | INSTITUTION_NAME | Character(40) | VARCHAR2(40) NOT NULL | Institution Name |
5 | DEGREE_DT | Date(10) | DATE | Admissions degree date. |
6 | AV_YEAR | Character(6) | VARCHAR2(6) NOT NULL | Year |
7 | DEGREE | Character(8) | VARCHAR2(8) NOT NULL | Degree |
8 | AV_DEGREE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description of a degree that was conferred to a person. |
9 | 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 |
10 | ACAD_CAREER_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Academic Career |
11 | AV_MAJOR1_CD | Character(10) | VARCHAR2(10) NOT NULL | Major |
12 | AV_MAJOR1_NAME | Character(50) | VARCHAR2(50) NOT NULL | Major1 description of a person |
13 | AV_MAJOR2_CD | Character(10) | VARCHAR2(10) NOT NULL | Major 2 |
14 | AV_MAJOR2_NAME | Character(50) | VARCHAR2(50) NOT NULL | Major2 description |
15 | AV_MINOR1_CD | Character(10) | VARCHAR2(10) NOT NULL | Minor |
16 | AV_MINOR1_NAME | Character(50) | VARCHAR2(50) NOT NULL | Minor1 description |
17 | AV_MINOR2_CD | Character(10) | VARCHAR2(10) NOT NULL | Minor 2 |
18 | AV_MINOR2_NAME | Character(50) | VARCHAR2(50) NOT NULL | Minor2 description |