VIEW: APPS.IGS_AD_PS_APPL_INST_APLSUMR_V

Source

View Text - Preformatted

SELECT aav.person_id, aav.admission_appl_number, acai.nominated_course_cd, acai.sequence_number, ci_orig_acad.alternate_code || '/' || ci.alternate_code, ci_orig_acad.alternate_code || '/' || ci_orig_adm.alternate_code, ci_orig_acad.alternate_code || '/' || DECODE( acai.adm_cal_type, NULL, NULL,(SELECT ci_ovrd_adm.alternate_code FROM igs_ca_inst ci_ovrd_adm WHERE ci_ovrd_adm.cal_type = acai.adm_cal_type AND ci_ovrd_adm.sequence_number = acai.adm_ci_sequence_number )), aav.acad_cal_type, aav.acad_ci_sequence_number, CI_ORIG_ACAD.START_DT ACAD_CI_START_DT, CI_ORIG_ACAD.END_DT ACAD_CI_END_DT, nvl(acai.adm_cal_type,aav.adm_cal_type), nvl(acai.adm_ci_sequence_number,aav.adm_ci_sequence_number), ci.start_dt, ci.end_dt, AAV.ADMISSION_CAT || '/' || LKUP.MEANING ADMISSION_PROCESS_CAT, aav.admission_cat, aav.s_admission_process_type, aav.appl_dt, acai.course_cd, acai.crv_version_number, DECODE(acai.course_cd, NULL, NULL, (SELECT crv.title FROM igs_ps_ver crv WHERE crv.course_cd = acai.course_cd AND crv.version_number = acai.crv_version_number )), acai.preference_number, aav.adm_appl_status, acai.adm_outcome_status, acai.adm_offer_resp_status, acai.created_by, acai.creation_date, acai.last_updated_by, acai.last_update_date, acai.last_update_login, aav.choice_number, acai.future_acad_cal_type, acai.future_acad_ci_sequence_number, DECODE (acai.future_acad_cal_type, NULL, NULL, (SELECT ci_fut_acad.alternate_code FROM igs_ca_inst ci_fut_acad WHERE ci_fut_acad.cal_type = acai.future_acad_cal_type AND ci_fut_acad.sequence_number = acai.future_acad_ci_sequence_number )) future_acad_alternate_code, acai.future_adm_cal_type, acai.future_adm_ci_sequence_number, DECODE (acai.future_adm_cal_type, NULL, NULL, (SELECT ci_fut_adm.alternate_code FROM igs_ca_inst ci_fut_adm WHERE ci_fut_adm.cal_type = acai.future_adm_cal_type AND ci_fut_adm.sequence_number = acai.future_adm_ci_sequence_number )) future_adm_alternate_code, acai.previous_term_adm_appl_number, acai.previous_term_sequence_number, acai.future_term_adm_appl_number, acai.future_term_sequence_number, ACAI.DEF_ACAD_CAL_TYPE, ACAI.DEF_ACAD_CI_SEQUENCE_NUM, ACAI.DEF_PREV_TERM_ADM_APPL_NUM, ACAI.DEF_PREV_APPL_SEQUENCE_NUM, ACAI.DEF_TERM_ADM_APPL_NUM, ACAI.DEF_APPL_SEQUENCE_NUM, DECODE(acai.adm_cal_type , NULL, NULL, (SELECT ci_def_adm.alternate_code FROM igs_ca_inst ci_def_adm WHERE ci_def_adm.cal_type = acai.adm_cal_type AND ci_def_adm.sequence_number =acai.adm_ci_sequence_number)) , DECODE (acai.def_acad_cal_type , NULL, NULL, (SELECT ci_def_acad.alternate_code FROM igs_ca_inst ci_def_acad WHERE ci_def_acad.cal_type = acai.def_acad_cal_type AND ci_def_acad.sequence_number = acai.def_acad_ci_sequence_num)), AAV.APPLICATION_TYPE, AAV.APPLICATION_ID, AAV.ALT_APPL_ID, ACAI.APPL_INST_STATUS FROM IGS_AD_PS_APPL_INST acai, IGS_AD_APPL AAV, IGS_CA_INST ci, IGS_CA_INST ci_orig_acad, IGS_CA_INST ci_orig_adm, IGS_LOOKUP_VALUES LKUP WHERE aav.person_id = acai.person_id AND aav.admission_appl_number = acai.admission_appl_number AND ci.cal_type = NVL(acai.adm_cal_type, aav.adm_cal_type) AND ci.sequence_number = NVL(acai.adm_ci_sequence_number,aav.adm_ci_sequence_number) AND ci_orig_acad.cal_type = aav.acad_cal_type AND ci_orig_acad.sequence_number = aav.acad_ci_sequence_number AND ci_orig_adm.cal_type = aav.adm_cal_type AND ci_orig_adm.sequence_number = aav.adm_ci_sequence_number AND LKUP.LOOKUP_TYPE = 'ADMISSION_PROCESS_TYPE' AND AAV.S_ADMISSION_PROCESS_TYPE = LKUP.LOOKUP_CODE

View Text - HTML Formatted

SELECT AAV.PERSON_ID
, AAV.ADMISSION_APPL_NUMBER
, ACAI.NOMINATED_COURSE_CD
, ACAI.SEQUENCE_NUMBER
, CI_ORIG_ACAD.ALTERNATE_CODE || '/' || CI.ALTERNATE_CODE
, CI_ORIG_ACAD.ALTERNATE_CODE || '/' || CI_ORIG_ADM.ALTERNATE_CODE
, CI_ORIG_ACAD.ALTERNATE_CODE || '/' || DECODE( ACAI.ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI_OVRD_ADM.ALTERNATE_CODE
FROM IGS_CA_INST CI_OVRD_ADM
WHERE CI_OVRD_ADM.CAL_TYPE = ACAI.ADM_CAL_TYPE
AND CI_OVRD_ADM.SEQUENCE_NUMBER = ACAI.ADM_CI_SEQUENCE_NUMBER ))
, AAV.ACAD_CAL_TYPE
, AAV.ACAD_CI_SEQUENCE_NUMBER
, CI_ORIG_ACAD.START_DT ACAD_CI_START_DT
, CI_ORIG_ACAD.END_DT ACAD_CI_END_DT
, NVL(ACAI.ADM_CAL_TYPE
, AAV.ADM_CAL_TYPE)
, NVL(ACAI.ADM_CI_SEQUENCE_NUMBER
, AAV.ADM_CI_SEQUENCE_NUMBER)
, CI.START_DT
, CI.END_DT
, AAV.ADMISSION_CAT || '/' || LKUP.MEANING ADMISSION_PROCESS_CAT
, AAV.ADMISSION_CAT
, AAV.S_ADMISSION_PROCESS_TYPE
, AAV.APPL_DT
, ACAI.COURSE_CD
, ACAI.CRV_VERSION_NUMBER
, DECODE(ACAI.COURSE_CD
, NULL
, NULL
, (SELECT CRV.TITLE
FROM IGS_PS_VER CRV
WHERE CRV.COURSE_CD = ACAI.COURSE_CD
AND CRV.VERSION_NUMBER = ACAI.CRV_VERSION_NUMBER ))
, ACAI.PREFERENCE_NUMBER
, AAV.ADM_APPL_STATUS
, ACAI.ADM_OUTCOME_STATUS
, ACAI.ADM_OFFER_RESP_STATUS
, ACAI.CREATED_BY
, ACAI.CREATION_DATE
, ACAI.LAST_UPDATED_BY
, ACAI.LAST_UPDATE_DATE
, ACAI.LAST_UPDATE_LOGIN
, AAV.CHOICE_NUMBER
, ACAI.FUTURE_ACAD_CAL_TYPE
, ACAI.FUTURE_ACAD_CI_SEQUENCE_NUMBER
, DECODE (ACAI.FUTURE_ACAD_CAL_TYPE
, NULL
, NULL
, (SELECT CI_FUT_ACAD.ALTERNATE_CODE
FROM IGS_CA_INST CI_FUT_ACAD
WHERE CI_FUT_ACAD.CAL_TYPE = ACAI.FUTURE_ACAD_CAL_TYPE
AND CI_FUT_ACAD.SEQUENCE_NUMBER = ACAI.FUTURE_ACAD_CI_SEQUENCE_NUMBER )) FUTURE_ACAD_ALTERNATE_CODE
, ACAI.FUTURE_ADM_CAL_TYPE
, ACAI.FUTURE_ADM_CI_SEQUENCE_NUMBER
, DECODE (ACAI.FUTURE_ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI_FUT_ADM.ALTERNATE_CODE
FROM IGS_CA_INST CI_FUT_ADM
WHERE CI_FUT_ADM.CAL_TYPE = ACAI.FUTURE_ADM_CAL_TYPE
AND CI_FUT_ADM.SEQUENCE_NUMBER = ACAI.FUTURE_ADM_CI_SEQUENCE_NUMBER )) FUTURE_ADM_ALTERNATE_CODE
, ACAI.PREVIOUS_TERM_ADM_APPL_NUMBER
, ACAI.PREVIOUS_TERM_SEQUENCE_NUMBER
, ACAI.FUTURE_TERM_ADM_APPL_NUMBER
, ACAI.FUTURE_TERM_SEQUENCE_NUMBER
, ACAI.DEF_ACAD_CAL_TYPE
, ACAI.DEF_ACAD_CI_SEQUENCE_NUM
, ACAI.DEF_PREV_TERM_ADM_APPL_NUM
, ACAI.DEF_PREV_APPL_SEQUENCE_NUM
, ACAI.DEF_TERM_ADM_APPL_NUM
, ACAI.DEF_APPL_SEQUENCE_NUM
, DECODE(ACAI.ADM_CAL_TYPE
, NULL
, NULL
, (SELECT CI_DEF_ADM.ALTERNATE_CODE
FROM IGS_CA_INST CI_DEF_ADM
WHERE CI_DEF_ADM.CAL_TYPE = ACAI.ADM_CAL_TYPE
AND CI_DEF_ADM.SEQUENCE_NUMBER =ACAI.ADM_CI_SEQUENCE_NUMBER))
, DECODE (ACAI.DEF_ACAD_CAL_TYPE
, NULL
, NULL
, (SELECT CI_DEF_ACAD.ALTERNATE_CODE
FROM IGS_CA_INST CI_DEF_ACAD
WHERE CI_DEF_ACAD.CAL_TYPE = ACAI.DEF_ACAD_CAL_TYPE
AND CI_DEF_ACAD.SEQUENCE_NUMBER = ACAI.DEF_ACAD_CI_SEQUENCE_NUM))
, AAV.APPLICATION_TYPE
, AAV.APPLICATION_ID
, AAV.ALT_APPL_ID
, ACAI.APPL_INST_STATUS
FROM IGS_AD_PS_APPL_INST ACAI
, IGS_AD_APPL AAV
, IGS_CA_INST CI
, IGS_CA_INST CI_ORIG_ACAD
, IGS_CA_INST CI_ORIG_ADM
, IGS_LOOKUP_VALUES LKUP
WHERE AAV.PERSON_ID = ACAI.PERSON_ID
AND AAV.ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER
AND CI.CAL_TYPE = NVL(ACAI.ADM_CAL_TYPE
, AAV.ADM_CAL_TYPE)
AND CI.SEQUENCE_NUMBER = NVL(ACAI.ADM_CI_SEQUENCE_NUMBER
, AAV.ADM_CI_SEQUENCE_NUMBER)
AND CI_ORIG_ACAD.CAL_TYPE = AAV.ACAD_CAL_TYPE
AND CI_ORIG_ACAD.SEQUENCE_NUMBER = AAV.ACAD_CI_SEQUENCE_NUMBER
AND CI_ORIG_ADM.CAL_TYPE = AAV.ADM_CAL_TYPE
AND CI_ORIG_ADM.SEQUENCE_NUMBER = AAV.ADM_CI_SEQUENCE_NUMBER
AND LKUP.LOOKUP_TYPE = 'ADMISSION_PROCESS_TYPE'
AND AAV.S_ADMISSION_PROCESS_TYPE = LKUP.LOOKUP_CODE