VIEW: APPS.IGS_AD_PS_APL_INST_CNTX_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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