VIEW: APPS.IGS_AD_PS_APPL_INST_CONTEXT_V

Source

View Text - Preformatted

SELECT ACAIV.ROW_ID ROW_ID, acaiv.person_id, pdv.person_number, pdv.full_name context_block_name, pdv.gender sex, pdv.birth_date birth_dt, (SELECT DECODE(COUNT(ROWID),0,'N','Y') FROM igs_pe_pers_encumb WHERE person_id = pdv.person_id AND TRUNC(SYSDATE) BETWEEN start_dt AND (NVL(expiry_dt,TRUNC(SYSDATE)+1)-1)) encumbered_ind, DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_ind, aa.admission_appl_number, aa.admission_cat, aa.s_admission_process_type, aa.acad_cal_type, aa.acad_ci_sequence_number, ci1.alternate_code, aa.adm_cal_type, aa.adm_ci_sequence_number, ci2.alternate_code, ci1.alternate_code || '/' || ci2.alternate_code, acaiv.nominated_course_cd, acaiv.sequence_number, acaiv.course_cd, acaiv.crv_version_number, acaiv.location_cd, acaiv.attendance_mode, acaiv.attendance_type, acaiv.preference_number, acaiv.course_cd || ' ' || acaiv.crv_version_number, psv.title course_title_display, acaiv.created_by, acaiv.creation_date, acaiv.last_updated_by, acaiv.last_update_date, acaiv.last_update_login, aa.application_id application_id, aa.alt_appl_id alt_appl_id FROM IGS_AD_PS_APPL_INST acaiv, IGS_AD_APPL aa, IGS_PE_PERSON_BASE_V pdv, IGS_CA_INST ci1, IGS_CA_INST ci2, IGS_PS_VER psv, IGS_PE_HZ_PARTIES pd, HZ_PERSON_PROFILES pp WHERE acaiv.person_id = aa.person_id AND acaiv.admission_appl_number = aa.admission_appl_number AND acaiv.person_id = pdv.person_id AND ci1.cal_type = aa.acad_cal_type AND ci1.sequence_number = aa.acad_ci_sequence_number AND ci2.cal_type = aa.adm_cal_type AND ci2.sequence_number = aa.adm_ci_sequence_number AND acaiv.nominated_course_cd = psv.course_cd AND acaiv.crv_version_number = psv.version_number AND aa.person_id = pd.party_id AND aa.person_id = pp.party_id AND pp.content_source_type(+) = 'USER_ENTERED' AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date, SYSDATE)

View Text - HTML Formatted

SELECT ACAIV.ROW_ID ROW_ID
, ACAIV.PERSON_ID
, PDV.PERSON_NUMBER
, PDV.FULL_NAME CONTEXT_BLOCK_NAME
, PDV.GENDER SEX
, PDV.BIRTH_DATE BIRTH_DT
, (SELECT DECODE(COUNT(ROWID)
, 0
, 'N'
, 'Y')
FROM IGS_PE_PERS_ENCUMB
WHERE PERSON_ID = PDV.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN START_DT
AND (NVL(EXPIRY_DT
, TRUNC(SYSDATE)+1)-1)) ENCUMBERED_IND
, DECODE(PP.DATE_OF_DEATH
, NULL
, NVL(PD.DECEASED_IND
, 'N')
, 'Y') DECEASED_IND
, AA.ADMISSION_APPL_NUMBER
, AA.ADMISSION_CAT
, AA.S_ADMISSION_PROCESS_TYPE
, AA.ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER
, CI1.ALTERNATE_CODE
, AA.ADM_CAL_TYPE
, AA.ADM_CI_SEQUENCE_NUMBER
, CI2.ALTERNATE_CODE
, CI1.ALTERNATE_CODE || '/' || CI2.ALTERNATE_CODE
, ACAIV.NOMINATED_COURSE_CD
, ACAIV.SEQUENCE_NUMBER
, ACAIV.COURSE_CD
, ACAIV.CRV_VERSION_NUMBER
, ACAIV.LOCATION_CD
, ACAIV.ATTENDANCE_MODE
, ACAIV.ATTENDANCE_TYPE
, ACAIV.PREFERENCE_NUMBER
, ACAIV.COURSE_CD || ' ' || ACAIV.CRV_VERSION_NUMBER
, PSV.TITLE COURSE_TITLE_DISPLAY
, ACAIV.CREATED_BY
, ACAIV.CREATION_DATE
, ACAIV.LAST_UPDATED_BY
, ACAIV.LAST_UPDATE_DATE
, ACAIV.LAST_UPDATE_LOGIN
, AA.APPLICATION_ID APPLICATION_ID
, AA.ALT_APPL_ID ALT_APPL_ID
FROM IGS_AD_PS_APPL_INST ACAIV
, IGS_AD_APPL AA
, IGS_PE_PERSON_BASE_V PDV
, IGS_CA_INST CI1
, IGS_CA_INST CI2
, IGS_PS_VER PSV
, IGS_PE_HZ_PARTIES PD
, HZ_PERSON_PROFILES PP
WHERE ACAIV.PERSON_ID = AA.PERSON_ID
AND ACAIV.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND ACAIV.PERSON_ID = PDV.PERSON_ID
AND CI1.CAL_TYPE = AA.ACAD_CAL_TYPE
AND CI1.SEQUENCE_NUMBER = AA.ACAD_CI_SEQUENCE_NUMBER
AND CI2.CAL_TYPE = AA.ADM_CAL_TYPE
AND CI2.SEQUENCE_NUMBER = AA.ADM_CI_SEQUENCE_NUMBER
AND ACAIV.NOMINATED_COURSE_CD = PSV.COURSE_CD
AND ACAIV.CRV_VERSION_NUMBER = PSV.VERSION_NUMBER
AND AA.PERSON_ID = PD.PARTY_ID
AND AA.PERSON_ID = PP.PARTY_ID
AND PP.CONTENT_SOURCE_TYPE(+) = 'USER_ENTERED'
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)