View: IGS_AD_PS_APPL_INST_APLSUMR_V

View Text

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

Columns