VIEW: APPS.IGS_UC_UCAPCH_OSSAPPL_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)