SSR_DEEWR_AOVW(SQL View) |
Index Back |
---|
SELECT DISTINCT A.EMPLID ,A.ACAD_CAREER ,A.STDNT_CAR_NBR ,A.ADM_APPL_NBR ,B.APPL_PROG_NBR ,B.EFFDT ,B.EFFSEQ ,A.ADM_APPL_CTR ,A.ADM_APPL_METHOD ,A.ADMIT_TYPE ,A.ADM_APPL_DT ,B.ADMIT_TERM ,B.PROG_ACTION ,B.PROG_REASON ,B.ACAD_PROG ,B.CAMPUS ,C.SAD_ATTEND_MODE ,C.SSR_DEEWR_FUND_SRC ,PRG_AUS.SSR_PROG_TYPE_CD ,MAPCIT.SSR_ELEM_358 ,RNC.SSR_DEEWR_REFDATE ,RNC.OPRID ,RNC.RUN_CNTL_ID FROM PS_ADM_APPL_DATA A ,PS_ADM_APPL_PROG B ,PS_SAD_APL_PRG_AUS C ,PS_SSR_ACD_PRG_AUS PRG_AUS ,PS_SCC_CITIZ_HIST CIT ,PS_SSR_MAP_358 MAPCIT ,PS_SSR_DEEWR_AORNC RNC WHERE A.EMPLID = B.EMPLID AND A.ADM_APPL_NBR = B.ADM_APPL_NBR AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND B.EFFDT = ( SELECT MAX(B3.EFFDT) FROM PS_ADM_APPL_PROG B3 WHERE B3.EMPLID = B.EMPLID AND B3.ACAD_CAREER = B.ACAD_CAREER AND B3.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND B3.ADM_APPL_NBR = B.ADM_APPL_NBR AND B3.APPL_PROG_NBR = B.APPL_PROG_NBR AND B3.EFFDT <= RNC.SSR_DEEWR_REFDATE) AND B.EFFSEQ = ( SELECT MAX(B1.EFFSEQ) FROM PS_ADM_APPL_PROG B1 WHERE B1.EMPLID = B.EMPLID AND B1.ACAD_CAREER = B.ACAD_CAREER AND B1.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND B1.ADM_APPL_NBR = B.ADM_APPL_NBR AND B1.APPL_PROG_NBR = B.APPL_PROG_NBR AND B1.EFFDT = B.EFFDT) AND C.EMPLID = B.EMPLID AND C.ACAD_CAREER = B.ACAD_CAREER AND C.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND C.ADM_APPL_NBR = B.ADM_APPL_NBR AND C.APPL_PROG_NBR = B.APPL_PROG_NBR AND C.EFFDT = B.EFFDT AND C.EFFSEQ = B.EFFSEQ AND PRG_AUS.ACAD_PROG = B.ACAD_PROG AND PRG_AUS.INSTITUTION = B.INSTITUTION AND PRG_AUS.EFFDT = ( SELECT MAX(PRG_AUS1.EFFDT) FROM PS_SSR_ACD_PRG_AUS PRG_AUS1 WHERE PRG_AUS.ACAD_PROG = PRG_AUS1.ACAD_PROG AND PRG_AUS.INSTITUTION = PRG_AUS1.INSTITUTION AND PRG_AUS1.EFFDT <= RNC.SSR_DEEWR_REFDATE) AND CIT.EMPLID = B.EMPLID AND CIT.COUNTRY = 'AUS' AND CIT.EFFDT = ( SELECT MAX(CIT1.EFFDT) FROM PS_SCC_CITIZ_HIST CIT1 WHERE CIT.EMPLID = CIT1.EMPLID AND CIT1.COUNTRY = CIT.COUNTRY AND CIT1.EFFDT <= A.ADM_APPL_DT) AND MAPCIT.CITIZENSHIP_STATUS = CIT.CITIZENSHIP_STATUS AND MAPCIT.EFFDT = ( SELECT MAX(MAPCIT2.EFFDT) FROM PS_SSR_MAP_358 MAPCIT2 WHERE MAPCIT2.CITIZENSHIP_STATUS = MAPCIT.CITIZENSHIP_STATUS AND MAPCIT2.SSR_ELEM_358 = MAPCIT.SSR_ELEM_358 AND MAPCIT2.EFFDT <= %CurrentDateIn) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
2 | 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 |
3 | STDNT_CAR_NBR | Number(3,0) | SMALLINT NOT NULL | Student Career Nbr |
4 | ADM_APPL_NBR | Character(8) | VARCHAR2(8) NOT NULL | Application Nbr |
5 | APPL_PROG_NBR | Number(3,0) | SMALLINT NOT NULL | Application Program Nbr |
6 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
7 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
8 | ADM_APPL_CTR | Character(4) | VARCHAR2(4) NOT NULL | Application Center |
9 | ADM_APPL_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Application Method
DSK=Diskette EDI=EDI HRD=Hard Copy OUA=Ontario University Application SRV=Application Service WWW=Web Application |
10 | ADMIT_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Admit Type |
11 | ADM_APPL_DT | Date(10) | DATE | Application Date |
12 | ADMIT_TERM | Character(4) | VARCHAR2(4) NOT NULL | Admit Term |
13 | 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 |
14 | PROG_REASON | Character(4) | VARCHAR2(4) NOT NULL | Action Reason |
15 | ACAD_PROG | Character(5) | VARCHAR2(5) NOT NULL | Academic Program |
16 | CAMPUS | Character(5) | VARCHAR2(5) NOT NULL | Campus |
17 | SAD_ATTEND_MODE | Character(1) | VARCHAR2(1) NOT NULL |
Mode of Attendance
1=Internal Mode of Attendance 2=External Mode of Attendance 3=Multi-modal Mode of Attendance 4=Completed Course through OUA 5=Submission of Original Work 6=Employer based 7=Completed Course except in OUA |
18 | SSR_DEEWR_FUND_SRC | Character(3) | VARCHAR2(3) NOT NULL | Funding Source |
19 | SSR_PROG_TYPE_CD | Character(2) | VARCHAR2(2) NOT NULL | Program Type Code |
20 | SSR_ELEM_358 | Character(1) | VARCHAR2(1) NOT NULL | DEST Citizen/Resident Code |
21 | SSR_DEEWR_REFDATE | Date(10) | DATE | Reference Date |
22 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
23 | RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |