[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | Selects the UCAS Application Choice records as well as the OSS Application Instances for the UCAS Applicants. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT UCAPCH.SYSTEM_CODE
, UCADM.NAME
, UCAP.PERSONAL_ID
, UCAPCH.APP_NO
, UCAPCH.CHOICE_NO
, UCAPCH.UCAS_CYCLE
, UCAP.OSS_PERSON_ID
, UCAPCH.INSTITUTE_CODE
, UCAPCH.UCAS_PROGRAM_CODE
, UCAPCH.CAMPUS
, NULL
, UCAPCH.OSS_PROGRAM_CODE
, UCAPCH.OSS_PROGRAM_VERSION
, UCAPCH.OSS_ATTENDANCE_TYPE
, UCAPCH.OSS_ATTENDANCE_MODE
, UCAPCH.OSS_LOCATION
, PSVER.TITLE
, UCAP.ROUTE_B
, UCAPCH.DECISION
, LKDCSN.MEANING
, UCAPCH.REPLY
, LKRPLY.MEANING
, UCAPCH.CHOICE_CANCELLED
, UCAPCH.CREATED_BY
, UCAPCH.CREATION_DATE
, UCAPCH.LAST_UPDATED_BY
, UCAPCH.LAST_UPDATE_DATE
, UCAPCH.LAST_UPDATE_LOGIN
FROM IGS_UC_APPLICANTS UCAP
, IGS_UC_APP_CHOICES UCAPCH
, IGS_UC_DEFAULTS UCADM
, IGS_PS_VER PSVER
, IGS_LOOKUP_VALUES LKDCSN
, IGS_LOOKUP_VALUES LKRPLY
WHERE UCAP.APP_NO = UCAPCH.APP_NO
AND UCAPCH.SYSTEM_CODE = UCADM.SYSTEM_CODE
AND UCAPCH.OSS_PROGRAM_CODE = PSVER.COURSE_CD (+)
AND UCAPCH.OSS_PROGRAM_VERSION = PSVER.VERSION_NUMBER (+)
AND UCAPCH.DECISION = LKDCSN.LOOKUP_CODE (+)
AND LKDCSN.LOOKUP_TYPE(+) = 'IGS_UC_APP_DECISION'
AND UCAPCH.REPLY = LKRPLY.LOOKUP_CODE (+)
AND LKRPLY.LOOKUP_TYPE (+)='IGS_UC_APP_REPLY' UNION ALL SELECT 'OSS'
, 'OSS'
, NULL
, APPLINST.ADMISSION_APPL_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, APPLINST.PERSON_ID
, NULL
, NULL
, NULL
, NULL
, APPLINST.NOMINATED_COURSE_CD
, APPLINST.CRV_VERSION_NUMBER
, APPLINST.ATTENDANCE_TYPE
, APPLINST.ATTENDANCE_MODE
, APPLINST.LOCATION_CD
, PSVER.TITLE
, NULL
, APPLINST.ADM_OUTCOME_STATUS
, OUSTAT.DESCRIPTION
, APPLINST.ADM_OFFER_RESP_STATUS
, OFFRESP.DESCRIPTION
, NULL
, APPLINST.CREATED_BY
, APPLINST.CREATION_DATE
, APPLINST.LAST_UPDATED_BY
, APPLINST.LAST_UPDATE_DATE
, APPLINST.LAST_UPDATE_LOGIN
FROM IGS_AD_PS_APPL_INST_ALL APPLINST
, IGS_AD_CODE_CLASSES ACC
, IGS_PS_VER PSVER
, IGS_AD_OU_STAT OUSTAT
, IGS_AD_OFR_RESP_STAT OFFRESP
WHERE NVL(APPLINST.APP_SOURCE_ID
, -1) <> ACC.CODE_ID
AND ACC.CLASS = 'SYS_APPL_SOURCE'
AND ACC.CLASS_TYPE_CODE = 'ADM_CODE_CLASSES'
AND ACC.NAME = 'UCAS'
AND APPLINST.PERSON_ID IN (SELECT DISTINCT OSS_PERSON_ID
FROM IGS_UC_APPLICANTS)
AND APPLINST.NOMINATED_COURSE_CD = PSVER.COURSE_CD (+)
AND APPLINST.CRV_VERSION_NUMBER = PSVER.VERSION_NUMBER (+)
AND APPLINST.ADM_OUTCOME_STATUS = OUSTAT.ADM_OUTCOME_STATUS (+)
AND APPLINST.ADM_OFFER_RESP_STATUS = OFFRESP.ADM_OFFER_RESP_STATUS (+)