VIEW: APPS.IGS_AD_PS_APPL_INST_CDT_CTXT_V

Source

View Text - Preformatted

SELECT acai.row_id row_id, pe.api_person_id, aa.person_number, acai.person_id, acai.course_cd, acai.crv_version_number as version_number, acai.course_cd || ' ' || acai.crv_version_number as COURSE_VERSION, NULL as SCA_COURSE_CD, acai.admission_appl_number acai_admission_appl_number, acai.nominated_course_cd acai_nominated_course_cd, acai.sequence_number acai_sequence_number, acai.location_cd, acai.attendance_mode, acai.attendance_type, acai.created_by, acai.creation_date, acai.last_updated_by, acai.last_update_date, acai.last_update_login , aa.application_id application_id, aa.alt_appl_id alt_appl_id FROM IGS_AD_PS_APPL_INST_SV acai, IGS_AD_APPL_V aa, IGS_PE_PERSON_V pe WHERE pe.person_id = aa.person_id AND acai.person_id = aa.person_id AND acai.admission_appl_number = aa.admission_appl_number AND (EXISTS (SELECT 'x' FROM IGS_AD_PRCS_CAT_STEP apcs WHERE apcs.admission_cat = aa.admission_cat AND apcs.s_admission_process_type = aa.s_admission_process_type AND apcs.s_admission_step_type = 'RESEARCH' AND apcs.step_group_type <> 'TRACK' )) AND (NOT EXISTS (SELECT 'x' FROM IGS_EN_STDNT_PS_ATT_SV sca2 WHERE sca2.person_id = acai.person_id AND sca2.course_cd = acai.course_cd AND sca2.adm_admission_appl_number = acai.admission_appl_number AND sca2.adm_nominated_course_cd = acai.nominated_course_cd AND sca2.adm_sequence_number = acai.sequence_number)) UNION SELECT sca.row_id row_id, pe.api_person_id, pe.person_number, sca.person_id, sca.course_cd, sca.version_number, sca.course_cd || ' ' || sca.version_number course_version, sca.course_cd sca_course_cd, NVL(sca.adm_admission_appl_number,1) acai_admission_appl_number, NVL(sca.adm_nominated_course_cd,'NONE') acai_nominated_course_cd, NVL(sca.adm_sequence_number,1) acai_sequence_number, sca.location_cd, sca.attendance_mode, sca.attendance_type, sca.created_by, sca.creation_date, sca.last_updated_by, sca.last_update_date, sca.last_update_login, TO_NUMBER(NULL), TO_CHAR(NULL) FROM IGS_EN_STDNT_PS_ATT_SV sca, IGS_PE_PERSON_V pe WHERE pe.person_id = sca.person_id

View Text - HTML Formatted

SELECT ACAI.ROW_ID ROW_ID
, PE.API_PERSON_ID
, AA.PERSON_NUMBER
, ACAI.PERSON_ID
, ACAI.COURSE_CD
, ACAI.CRV_VERSION_NUMBER AS VERSION_NUMBER
, ACAI.COURSE_CD || ' ' || ACAI.CRV_VERSION_NUMBER AS COURSE_VERSION
, NULL AS SCA_COURSE_CD
, ACAI.ADMISSION_APPL_NUMBER ACAI_ADMISSION_APPL_NUMBER
, ACAI.NOMINATED_COURSE_CD ACAI_NOMINATED_COURSE_CD
, ACAI.SEQUENCE_NUMBER ACAI_SEQUENCE_NUMBER
, ACAI.LOCATION_CD
, ACAI.ATTENDANCE_MODE
, ACAI.ATTENDANCE_TYPE
, ACAI.CREATED_BY
, ACAI.CREATION_DATE
, ACAI.LAST_UPDATED_BY
, ACAI.LAST_UPDATE_DATE
, ACAI.LAST_UPDATE_LOGIN
, AA.APPLICATION_ID APPLICATION_ID
, AA.ALT_APPL_ID ALT_APPL_ID
FROM IGS_AD_PS_APPL_INST_SV ACAI
, IGS_AD_APPL_V AA
, IGS_PE_PERSON_V PE
WHERE PE.PERSON_ID = AA.PERSON_ID
AND ACAI.PERSON_ID = AA.PERSON_ID
AND ACAI.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND (EXISTS (SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP APCS
WHERE APCS.ADMISSION_CAT = AA.ADMISSION_CAT
AND APCS.S_ADMISSION_PROCESS_TYPE = AA.S_ADMISSION_PROCESS_TYPE
AND APCS.S_ADMISSION_STEP_TYPE = 'RESEARCH'
AND APCS.STEP_GROUP_TYPE <> 'TRACK' ))
AND (NOT EXISTS (SELECT 'X'
FROM IGS_EN_STDNT_PS_ATT_SV SCA2
WHERE SCA2.PERSON_ID = ACAI.PERSON_ID
AND SCA2.COURSE_CD = ACAI.COURSE_CD
AND SCA2.ADM_ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER
AND SCA2.ADM_NOMINATED_COURSE_CD = ACAI.NOMINATED_COURSE_CD
AND SCA2.ADM_SEQUENCE_NUMBER = ACAI.SEQUENCE_NUMBER)) UNION SELECT SCA.ROW_ID ROW_ID
, PE.API_PERSON_ID
, PE.PERSON_NUMBER
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, SCA.COURSE_CD || ' ' || SCA.VERSION_NUMBER COURSE_VERSION
, SCA.COURSE_CD SCA_COURSE_CD
, NVL(SCA.ADM_ADMISSION_APPL_NUMBER
, 1) ACAI_ADMISSION_APPL_NUMBER
, NVL(SCA.ADM_NOMINATED_COURSE_CD
, 'NONE') ACAI_NOMINATED_COURSE_CD
, NVL(SCA.ADM_SEQUENCE_NUMBER
, 1) ACAI_SEQUENCE_NUMBER
, SCA.LOCATION_CD
, SCA.ATTENDANCE_MODE
, SCA.ATTENDANCE_TYPE
, SCA.CREATED_BY
, SCA.CREATION_DATE
, SCA.LAST_UPDATED_BY
, SCA.LAST_UPDATE_DATE
, SCA.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
FROM IGS_EN_STDNT_PS_ATT_SV SCA
, IGS_PE_PERSON_V PE
WHERE PE.PERSON_ID = SCA.PERSON_ID