View: IGS_AD_PS_APL_INST_CNTX_V

View Text

SELECT AAV.ROWID ROW_ID
, P.PARTY_ID PERSON_ID
, P.PARTY_NUMBER PERSON_NUMBER
, ACAI.NOMINATED_COURSE_CD
, ACAI.ADMISSION_APPL_NUMBER
, ACAI.SEQUENCE_NUMBER
, P.PERSON_LAST_NAME || '
, ' || P.PERSON_TITLE || ' ' || NVL (P.KNOWN_AS
, P.PERSON_FIRST_NAME) DSP_CONTEXT_BLOCK_NAME
, AAV.ADMISSION_CAT ADMISSION_CAT
, AAV.S_ADMISSION_PROCESS_TYPE S_ADMISSION_PROCESS_TYPE
, LKUP.MEANING ADM_PRC_TYPE_DESC
, AAV.APPL_DT APPL_DT
, ACAI.COURSE_CD COURSE_CD
, ACAI.LOCATION_CD LOCATION_CD
, ACAI.ATTENDANCE_MODE ATTENDANCE_MODE
, ACAI.ATTENDANCE_TYPE ATTENDANCE_TYPE
, CRV.TITLE CGNBT_COURSE_CD
, AAV.ACAD_CAL_TYPE ACAD_CAL_TYPE
, AAV.ACAD_CI_SEQUENCE_NUMBER ACAD_CI_SEQUENCE_NUMBER
, CI1.ALTERNATE_CODE ACAD_ALTERNATE_CODE
, NVL(ACAI.ADM_CAL_TYPE
, AAV.ADM_CAL_TYPE) ADM_CAL_TYPE
, NVL(ACAI.ADM_CI_SEQUENCE_NUMBER
, AAV.ADM_CI_SEQUENCE_NUMBER ) ADM_CI_SEQUENCE_NUMBER
, CI2.ALTERNATE_CODE ALTERNATE_CODE
, CI1.ALTERNATE_CODE || '/' || CI2.ALTERNATE_CODE COMMENCEMENT_PERIOD
, AAV.ADMISSION_CAT || '/' || LKUP.MEANING ADMISSION_PROCESS_CAT
, ACAI.UNIT_SET_CD UNIT_SET_CD
, DECODE(ACAI.UNIT_SET_CD
, NULL
, NULL
, (SELECT ESU.TITLE
FROM IGS_EN_UNIT_SET ESU
WHERE ACAI.UNIT_SET_CD = ESU.UNIT_SET_CD
AND ACAI.US_VERSION_NUMBER = ESU.VERSION_NUMBER))
, ACAI.US_VERSION_NUMBER US_VERSION_NUMBER
, ACAI.EDU_GOAL_PRIOR_ENROLL_ID EDU_GOAL_PRIOR_ENROLL_ID
, DECODE(ACAI.EDU_GOAL_PRIOR_ENROLL_ID
, NULL
, NULL
, (SELECT CC.NAME
FROM IGS_AD_CODE_CLASSES CC
WHERE ACAI.EDU_GOAL_PRIOR_ENROLL_ID = CC.CODE_ID
AND CC.CLASS = 'EDU_GOALS' )) EDU_GOAL_PRIOR_ENROLL
, DECODE(ACAI.EDU_GOAL_PRIOR_ENROLL_ID
, NULL
, NULL
, (SELECT CC.DESCRIPTION
FROM IGS_AD_CODE_CLASSES CC
WHERE ACAI.EDU_GOAL_PRIOR_ENROLL_ID = CC.CODE_ID
AND CC.CLASS = 'EDU_GOALS' )) EDU_GOAL_PRIOR_ENROLL_DESC
, ACAI.CREATED_BY
, ACAI.CREATION_DATE
, ACAI.LAST_UPDATED_BY
, ACAI.LAST_UPDATE_DATE
, ACAI.LAST_UPDATE_LOGIN
, CI1.START_DT ACAD_CI_START_DT
, CI1.END_DT ACAD_CI_END_DT
, CI2.START_DT ADM_CI_START_DT
, CI2.END_DT ADM_CI_END_DT
, AAV.ADM_APPL_STATUS
, AAV.APPLICATION_ID APPLICATION_ID
, AAV.ALT_APPL_ID ALT_APPL_ID
FROM HZ_PARTIES P
, IGS_AD_APPL AAV
, IGS_AD_PS_APPL_INST ACAI
, IGS_PS_VER CRV
, IGS_CA_INST CI1
, IGS_CA_INST CI2
, IGS_LOOKUP_VALUES LKUP
WHERE P.PARTY_ID = AAV.PERSON_ID
AND AAV.PERSON_ID = ACAI.PERSON_ID
AND AAV.ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER
AND CRV.COURSE_CD = ACAI.COURSE_CD
AND CRV.VERSION_NUMBER = ACAI.CRV_VERSION_NUMBER
AND CI1.CAL_TYPE = AAV.ACAD_CAL_TYPE
AND CI1.SEQUENCE_NUMBER = AAV.ACAD_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = NVL(ACAI.ADM_CAL_TYPE
, AAV.ADM_CAL_TYPE)
AND CI2.SEQUENCE_NUMBER = NVL(ACAI.ADM_CI_SEQUENCE_NUMBER
, AAV.ADM_CI_SEQUENCE_NUMBER)
AND LKUP.LOOKUP_TYPE = 'ADMISSION_PROCESS_TYPE'
AND AAV.S_ADMISSION_PROCESS_TYPE = LKUP.LOOKUP_CODE

Columns