VIEW: APPS.IGS_AD_PS_APLINSTUNT_UNITHST_V

Source

View Text - Preformatted

SELECT acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_start_dt, acaiuh1.hist_end_dt, acaiuh1.hist_who, NVL(acaiuh1.uv_version_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaiuh_col( 'UV_VERSION_NUMBER', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.uv_version_number)), NVL(acaiuh1.cal_type, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'CAL_TYPE', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.cal_type)), NVL(acaiuh1.ci_sequence_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaiuh_col( 'CI_SEQUENCE_NUMBER', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.ci_sequence_number)), NVL(acaiuh1.location_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'LOCATION_CD', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.location_cd)), NVL(acaiuh1.unit_class, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'UNIT_CLASS', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.unit_class)), NVL(acaiuh1.unit_mode, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'UNIT_MODE', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.unit_mode)), NVL(acaiuh1.adm_unit_outcome_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'ADM_UNIT_OUTCOME_STATUS', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.adm_unit_outcome_status)), NVL(acaiuh1.ass_tracking_id, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaiuh_col( 'ASS_TRACKING_ID', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.ass_tracking_id)), NVL(acaiuh1.rule_waived_dt, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaiuh_col( 'RULE_WAIVED_DT', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.rule_waived_dt)), NVL(acaiuh1.rule_waived_person_id, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaiuh_col( 'RULE_WAIVED_PERSON_ID', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.rule_waived_person_id)), NVL(acaiuh1.sup_unit_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaiuh_col( 'SUP_UNIT_CD', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt), 1, 10), acaiu1.sup_unit_cd)), NVL(acaiuh1.sup_uv_version_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaiuh_col( 'SUP_UV_VERSION_NUMBER', acaiuh1.adm_ps_appl_inst_unit_id, acaiuh1.person_id, acaiuh1.admission_appl_number, acaiuh1.nominated_course_cd, acaiuh1.acai_sequence_number, acaiuh1.unit_cd, acaiuh1.hist_end_dt)), acaiu1.sup_uv_version_number)), acaiuh1.created_by, acaiuh1.creation_date, acaiuh1.last_updated_by, acaiuh1.last_update_date, acaiuh1.last_update_login, acaiuh1.adm_ps_appl_inst_unit_id FROM IGS_AD_PS_APINTUNTHS acaiuh1, IGS_AD_PS_APLINSTUNT acaiu1 WHERE acaiuh1.adm_ps_appl_inst_unit_id = acaiu1.adm_ps_appl_inst_unit_id UNION ALL SELECT acaiu2.person_id, acaiu2.admission_appl_number, acaiu2.nominated_course_cd, acaiu2.acai_sequence_number, acaiu2.unit_cd, NVL( MAX(acaiuh2.hist_end_dt), acaiu2.LAST_UPDATE_DATE), IGS_GE_DATE.IGSDATE(NULL), acaiu2.LAST_UPDATED_BY, acaiu2.uv_version_number, acaiu2.cal_type, acaiu2.ci_sequence_number, acaiu2.location_cd, acaiu2.unit_class, acaiu2.unit_mode, acaiu2.adm_unit_outcome_status, acaiu2.ass_tracking_id, acaiu2.rule_waived_dt, acaiu2.rule_waived_person_id, acaiu2.sup_unit_cd, acaiu2.sup_uv_version_number, acaiu2.created_by, acaiu2.creation_date, acaiu2.last_updated_by, acaiu2.last_update_date, acaiu2.last_update_login, acaiu2.adm_ps_appl_inst_unit_id FROM IGS_AD_PS_APINTUNTHS acaiuh2, IGS_AD_PS_APLINSTUNT acaiu2 WHERE acaiuh2.adm_ps_appl_inst_unit_id (+) = acaiu2.adm_ps_appl_inst_unit_id GROUP BY acaiu2.person_id, acaiu2.admission_appl_number, acaiu2.nominated_course_cd, acaiu2.acai_sequence_number, acaiu2.unit_cd, acaiu2.uv_version_number, acaiu2.cal_type, acaiu2.ci_sequence_number, acaiu2.location_cd, acaiu2.unit_class, acaiu2.unit_mode, acaiu2.adm_unit_outcome_status, acaiu2.ass_tracking_id, acaiu2.rule_waived_dt, acaiu2.rule_waived_person_id, acaiu2.sup_unit_cd, acaiu2.sup_uv_version_number, acaiu2.created_by, acaiu2.creation_date, acaiu2.LAST_UPDATED_BY, acaiu2.LAST_UPDATE_DATE, acaiu2.last_update_login, acaiu2.adm_ps_appl_inst_unit_id

View Text - HTML Formatted

SELECT ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_START_DT
, ACAIUH1.HIST_END_DT
, ACAIUH1.HIST_WHO
, NVL(ACAIUH1.UV_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'UV_VERSION_NUMBER'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.UV_VERSION_NUMBER))
, NVL(ACAIUH1.CAL_TYPE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'CAL_TYPE'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.CAL_TYPE))
, NVL(ACAIUH1.CI_SEQUENCE_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'CI_SEQUENCE_NUMBER'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.CI_SEQUENCE_NUMBER))
, NVL(ACAIUH1.LOCATION_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'LOCATION_CD'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.LOCATION_CD))
, NVL(ACAIUH1.UNIT_CLASS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'UNIT_CLASS'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.UNIT_CLASS))
, NVL(ACAIUH1.UNIT_MODE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'UNIT_MODE'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.UNIT_MODE))
, NVL(ACAIUH1.ADM_UNIT_OUTCOME_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'ADM_UNIT_OUTCOME_STATUS'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.ADM_UNIT_OUTCOME_STATUS))
, NVL(ACAIUH1.ASS_TRACKING_ID
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'ASS_TRACKING_ID'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.ASS_TRACKING_ID))
, NVL(ACAIUH1.RULE_WAIVED_DT
, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'RULE_WAIVED_DT'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.RULE_WAIVED_DT))
, NVL(ACAIUH1.RULE_WAIVED_PERSON_ID
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'RULE_WAIVED_PERSON_ID'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.RULE_WAIVED_PERSON_ID))
, NVL(ACAIUH1.SUP_UNIT_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'SUP_UNIT_CD'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT)
, 1
, 10)
, ACAIU1.SUP_UNIT_CD))
, NVL(ACAIUH1.SUP_UV_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIUH_COL( 'SUP_UV_VERSION_NUMBER'
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
, ACAIUH1.PERSON_ID
, ACAIUH1.ADMISSION_APPL_NUMBER
, ACAIUH1.NOMINATED_COURSE_CD
, ACAIUH1.ACAI_SEQUENCE_NUMBER
, ACAIUH1.UNIT_CD
, ACAIUH1.HIST_END_DT))
, ACAIU1.SUP_UV_VERSION_NUMBER))
, ACAIUH1.CREATED_BY
, ACAIUH1.CREATION_DATE
, ACAIUH1.LAST_UPDATED_BY
, ACAIUH1.LAST_UPDATE_DATE
, ACAIUH1.LAST_UPDATE_LOGIN
, ACAIUH1.ADM_PS_APPL_INST_UNIT_ID
FROM IGS_AD_PS_APINTUNTHS ACAIUH1
, IGS_AD_PS_APLINSTUNT ACAIU1
WHERE ACAIUH1.ADM_PS_APPL_INST_UNIT_ID = ACAIU1.ADM_PS_APPL_INST_UNIT_ID UNION ALL SELECT ACAIU2.PERSON_ID
, ACAIU2.ADMISSION_APPL_NUMBER
, ACAIU2.NOMINATED_COURSE_CD
, ACAIU2.ACAI_SEQUENCE_NUMBER
, ACAIU2.UNIT_CD
, NVL( MAX(ACAIUH2.HIST_END_DT)
, ACAIU2.LAST_UPDATE_DATE)
, IGS_GE_DATE.IGSDATE(NULL)
, ACAIU2.LAST_UPDATED_BY
, ACAIU2.UV_VERSION_NUMBER
, ACAIU2.CAL_TYPE
, ACAIU2.CI_SEQUENCE_NUMBER
, ACAIU2.LOCATION_CD
, ACAIU2.UNIT_CLASS
, ACAIU2.UNIT_MODE
, ACAIU2.ADM_UNIT_OUTCOME_STATUS
, ACAIU2.ASS_TRACKING_ID
, ACAIU2.RULE_WAIVED_DT
, ACAIU2.RULE_WAIVED_PERSON_ID
, ACAIU2.SUP_UNIT_CD
, ACAIU2.SUP_UV_VERSION_NUMBER
, ACAIU2.CREATED_BY
, ACAIU2.CREATION_DATE
, ACAIU2.LAST_UPDATED_BY
, ACAIU2.LAST_UPDATE_DATE
, ACAIU2.LAST_UPDATE_LOGIN
, ACAIU2.ADM_PS_APPL_INST_UNIT_ID
FROM IGS_AD_PS_APINTUNTHS ACAIUH2
, IGS_AD_PS_APLINSTUNT ACAIU2
WHERE ACAIUH2.ADM_PS_APPL_INST_UNIT_ID (+) = ACAIU2.ADM_PS_APPL_INST_UNIT_ID GROUP BY ACAIU2.PERSON_ID
, ACAIU2.ADMISSION_APPL_NUMBER
, ACAIU2.NOMINATED_COURSE_CD
, ACAIU2.ACAI_SEQUENCE_NUMBER
, ACAIU2.UNIT_CD
, ACAIU2.UV_VERSION_NUMBER
, ACAIU2.CAL_TYPE
, ACAIU2.CI_SEQUENCE_NUMBER
, ACAIU2.LOCATION_CD
, ACAIU2.UNIT_CLASS
, ACAIU2.UNIT_MODE
, ACAIU2.ADM_UNIT_OUTCOME_STATUS
, ACAIU2.ASS_TRACKING_ID
, ACAIU2.RULE_WAIVED_DT
, ACAIU2.RULE_WAIVED_PERSON_ID
, ACAIU2.SUP_UNIT_CD
, ACAIU2.SUP_UV_VERSION_NUMBER
, ACAIU2.CREATED_BY
, ACAIU2.CREATION_DATE
, ACAIU2.LAST_UPDATED_BY
, ACAIU2.LAST_UPDATE_DATE
, ACAIU2.LAST_UPDATE_LOGIN
, ACAIU2.ADM_PS_APPL_INST_UNIT_ID