VIEW: APPS.IGS_AD_PS_APLINSTHST_HISTORY_V

Source

View Text - Preformatted

SELECT aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, NVL(aaav1.sequence_number, acaiv1.sequence_number), aaav1.hist_start_dt, aaav1.hist_end_dt, aaav1.hist_who, NVL(aaav1.adm_cal_type, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_CAL_TYPE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_cal_type)), NVL(aaav1.adm_ci_sequence_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_CI_SEQUENCE_NUMBER', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.adm_ci_sequence_number)), NVL(aaav1.course_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'COURSE_CD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 6), acaiv1.course_cd)), NVL(aaav1.crv_version_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'CRV_VERSION_NUMBER', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.crv_version_number)), NVL(aaav1.location_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'LOCATION_CD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.location_cd)), NVL(aaav1.attendance_mode, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'attendance_mode', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 2), acaiv1.attendance_mode)), NVL(aaav1.attendance_type, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ATTENDANCE_TYPE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 2), acaiv1.attendance_type)), NVL(aaav1.unit_set_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'UNIT_SET_CD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.unit_set_cd)), NVL(aaav1.us_version_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'US_VERSION_NUMBER', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.us_version_number)), NVL(aaav1.preference_number, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'PREFERENCE_NUMBER', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.preference_number)), NVL(aaav1.adm_doc_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'adm_doc_status', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_doc_status)), NVL(aaav1.adm_entry_qual_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_ENTRY_QUAL_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_entry_qual_status)), NVL(aaav1.late_adm_fee_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'LATE_ADM_FEE_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.late_adm_fee_status)), NVL(aaav1.adm_outcome_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OUTCOME_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_outcome_status)), NVL(aaav1.adm_otcm_status_auth_person_id, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OTCM_STATUS_AUTH_PERSON_ID', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.adm_otcm_status_auth_person_id)), NVL(aaav1.adm_outcome_status_auth_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OUTCOME_STATUS_AUTH_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.adm_outcome_status_auth_dt)), NVL(aaav1.adm_outcome_status_reason, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OUTCOME_STATUS_REASON', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 60), acaiv1.adm_outcome_status_reason)), NVL(aaav1.offer_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'OFFER_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.offer_dt)), NVL(aaav1.offer_response_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'OFFER_RESPONSE_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.offer_response_dt)), NVL(aaav1.prpsd_commencement_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'PRPSD_COMMENCEMENT_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.prpsd_commencement_dt)), NVL(aaav1.adm_cndtnl_offer_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_CNDTNL_OFFER_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_cndtnl_offer_status)), NVL(aaav1.cndtnl_offer_satisfied_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'CNDTNL_OFFER_SATISFIED_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.cndtnl_offer_satisfied_dt)), NVL(aaav1.cndtnl_offer_must_be_stsfd_ind, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'CNDTNL_OFFER_MUST_BE_STSFD_IND', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 1), acaiv1.cndtnl_offer_must_be_stsfd_ind)), NVL(aaav1.adm_offer_resp_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OFFER_RESP_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_offer_resp_status)), NVL(aaav1.actual_response_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.audp_get_acaih_col( 'ACTUAL_RESPONSE_DT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.actual_response_dt)), NVL(aaav1.adm_offer_dfrmnt_status, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ADM_OFFER_DFRMNT_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.adm_offer_dfrmnt_status)), NVL(aaav1.deferred_adm_cal_type, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'DEFERRED_ADM_CAL_TYPE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.deferred_adm_cal_type)), NVL(aaav1.deferred_adm_ci_sequence_num, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'DEFERRED_ADM_CI_SEQUENCE_NUM', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.deferred_adm_ci_sequence_num)), NVL(aaav1.deferred_tracking_id, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'DEFERRED_TRACKING_ID', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.deferred_tracking_id)), NVL(aaav1.ass_rank, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'ASS_RANK', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.ass_rank)), NVL(aaav1.secondary_ass_rank, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'SECONDARY_ASS_RANK', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.secondary_ass_rank)), NVL(aaav1.intrntnl_acceptance_advice_num, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'INTRNTNL_ACCEPTANCE_ADVICE_NUM', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.intrntnl_acceptance_advice_num)), NVL(aaav1.ass_tracking_id, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'ASS_TRACKING_ID', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.ass_tracking_id)), NVL(aaav1.fee_cat, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'FEE_CAT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.fee_cat)), NVL(aaav1.hecs_payment_option, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'HECS_PAYMENT_OPTION', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.hecs_payment_option)), NVL(aaav1.expected_completion_yr, NVL(TO_NUMBER(IGS_AU_GEN_001.audp_get_acaih_col( 'EXPECTED_COMPLETION_YR', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt)), acaiv1.expected_completion_yr)), NVL(aaav1.expected_completion_perd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'EXPECTED_COMPLETION_PERD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 1), acaiv1.expected_completion_perd)), NVL(aaav1.correspondence_cat, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'CORRESPONDENCE_CAT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.correspondence_cat)), NVL(aaav1.enrolment_cat, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'ENROLMENT_CAT', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.enrolment_cat)), NVL(aaav1.funding_source, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'FUNDING_SOURCE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.funding_source)), NVL(aaav1.applicant_acptnce_cndtn, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'APPLICANT_ACPTNCE_CNDTN', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 2000), acaiv1.applicant_acptnce_cndtn)), NVL(aaav1.cndtnl_offer_cndtn, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'CNDTNL_OFFER_CNDTN', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 2000), acaiv1.cndtnl_offer_cndtn)), NVL(aaav1.transfer_course_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'TRANSFER_COURSE_CD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 6), acaiv1.transfer_course_cd)), NVL(aaav1.basis_for_admission_type, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'BASIS_FOR_ADMISSION_TYPE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 10), acaiv1.basis_for_admission_type)), NVL(aaav1.admission_cd, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'ADMISSION_CD', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 10), acaiv1.admission_cd)), NVL(aaav1.course_rank_set, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'COURSE_RANK_SET', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 30), acaiv1.course_rank_set)), NVL(aaav1.course_rank_schedule, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'COURSE_RANK_SCHEDULE', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 10), acaiv1.course_rank_schedule)), NVL(aaav1.req_for_reconsideration_ind, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'REQ_FOR_RECONSIDERATION_IND', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 1), acaiv1.req_for_reconsideration_ind)), NVL(aaav1.req_for_adv_standing_ind, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acah_col( 'REQ_FOR_ADV_STANDING_IND', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.hist_end_dt), 1, 1), acaiv1.req_for_adv_standing_ind)), aaav1.CREATED_BY, aaav1.CREATION_DATE, aaav1.LAST_UPDATED_BY, aaav1.LAST_UPDATE_DATE, aaav1.LAST_UPDATE_LOGIN, NVL(aaav1.APPL_INST_STATUS, NVL(SUBSTR(IGS_AU_GEN_001.audp_get_acaih_col( 'APPL_INST_STATUS', aaav1.person_id, aaav1.admission_appl_number, aaav1.nominated_course_cd, aaav1.sequence_number, aaav1.hist_end_dt), 1, 10), acaiv1.APPL_INST_STATUS)) FROM IGS_AD_PS_APLINSTHST_APLHIST_V aaav1, IGS_AD_PS_APPL_INST_APLINST_V acaiv1 WHERE acaiv1.person_id = aaav1.person_id AND acaiv1.admission_appl_number = aaav1.admission_appl_number AND acaiv1.nominated_course_cd = aaav1.nominated_course_cd AND (aaav1.sequence_number IS NULL OR acaiv1.sequence_number = aaav1.sequence_number) UNION SELECT acaiv2.person_id, acaiv2.admission_appl_number, acaiv2.nominated_course_cd, acaiv2.sequence_number, NVL( MAX(aaav2.hist_end_dt), GREATEST(acaiv2.LAST_UPDATE_DATE, aca.LAST_UPDATE_DATE)), IGS_GE_DATE.IGSDATE(NULL), acaiv2.LAST_UPDATED_BY, acaiv2.adm_cal_type, acaiv2.adm_ci_sequence_number, acaiv2.course_cd, acaiv2.crv_version_number, acaiv2.location_cd, acaiv2.attendance_mode, acaiv2.attendance_type, acaiv2.unit_set_cd, acaiv2.us_version_number, acaiv2.preference_number, acaiv2.adm_doc_status, acaiv2.adm_entry_qual_status, acaiv2.late_adm_fee_status, acaiv2.adm_outcome_status, acaiv2.adm_otcm_status_auth_person_id, acaiv2.adm_outcome_status_auth_dt, acaiv2.adm_outcome_status_reason, acaiv2.offer_dt, acaiv2.offer_response_dt, acaiv2.prpsd_commencement_dt, acaiv2.adm_cndtnl_offer_status, acaiv2.cndtnl_offer_satisfied_dt, acaiv2.cndtnl_offer_must_be_stsfd_ind, acaiv2.adm_offer_resp_status, acaiv2.actual_response_dt, acaiv2.adm_offer_dfrmnt_status, acaiv2.deferred_adm_cal_type, acaiv2.deferred_adm_ci_sequence_num, acaiv2.deferred_tracking_id, acaiv2.ass_rank, acaiv2.secondary_ass_rank, acaiv2.intrntnl_acceptance_advice_num, acaiv2.ass_tracking_id, acaiv2.fee_cat, acaiv2.hecs_payment_option, acaiv2.expected_completion_yr, acaiv2.expected_completion_perd, acaiv2.correspondence_cat, acaiv2.enrolment_cat, acaiv2.funding_source, acaiv2.applicant_acptnce_cndtn, acaiv2.cndtnl_offer_cndtn, acaiv2.transfer_course_cd, acaiv2.basis_for_admission_type, acaiv2.admission_cd, acaiv2.course_rank_set, acaiv2.course_rank_schedule, acaiv2.req_for_reconsideration_ind, acaiv2.req_for_adv_standing_ind, acaiv2.CREATED_BY, acaiv2.CREATION_DATE, acaiv2.LAST_UPDATED_BY, acaiv2.LAST_UPDATE_DATE, acaiv2.LAST_UPDATE_LOGIN, acaiv2.APPL_INST_STATUS FROM IGS_AD_PS_APLINSTHST_APLHIST_V aaav2, IGS_AD_PS_APPL_INST_APLINST_V acaiv2, IGS_AD_PS_APPL aca WHERE acaiv2.person_id = aaav2.person_id (+) AND acaiv2.admission_appl_number = aaav2.admission_appl_number (+) AND acaiv2.nominated_course_cd = aaav2.nominated_course_cd (+) AND acaiv2.sequence_number = aaav2.sequence_number (+) AND aca.person_id = acaiv2.person_id AND aca.admission_appl_number = acaiv2.admission_appl_number AND aca.nominated_course_cd = acaiv2.nominated_course_cd GROUP BY acaiv2.person_id, acaiv2.admission_appl_number, acaiv2.nominated_course_cd, acaiv2.sequence_number, aca.LAST_UPDATE_DATE, acaiv2.adm_cal_type, acaiv2.adm_ci_sequence_number, acaiv2.course_cd, acaiv2.crv_version_number, acaiv2.location_cd, acaiv2.attendance_mode, acaiv2.attendance_type, acaiv2.unit_set_cd, acaiv2.us_version_number, acaiv2.preference_number, acaiv2.adm_doc_status, acaiv2.adm_entry_qual_status, acaiv2.late_adm_fee_status, acaiv2.adm_outcome_status, acaiv2.adm_otcm_status_auth_person_id, acaiv2.adm_outcome_status_auth_dt, acaiv2.adm_outcome_status_reason, acaiv2.offer_dt, acaiv2.offer_response_dt, acaiv2.prpsd_commencement_dt, acaiv2.adm_cndtnl_offer_status, acaiv2.cndtnl_offer_satisfied_dt, acaiv2.cndtnl_offer_must_be_stsfd_ind, acaiv2.adm_offer_resp_status, acaiv2.actual_response_dt, acaiv2.adm_offer_dfrmnt_status, acaiv2.deferred_adm_cal_type, acaiv2.deferred_adm_ci_sequence_num, acaiv2.deferred_tracking_id, acaiv2.ass_rank, acaiv2.secondary_ass_rank, acaiv2.intrntnl_acceptance_advice_num, acaiv2.ass_tracking_id, acaiv2.fee_cat, acaiv2.hecs_payment_option, acaiv2.expected_completion_yr, acaiv2.expected_completion_perd, acaiv2.correspondence_cat, acaiv2.enrolment_cat, acaiv2.funding_source, acaiv2.applicant_acptnce_cndtn, acaiv2.cndtnl_offer_cndtn, acaiv2.transfer_course_cd, acaiv2.basis_for_admission_type, acaiv2.admission_cd, acaiv2.course_rank_set, acaiv2.course_rank_schedule, acaiv2.req_for_reconsideration_ind, acaiv2.req_for_adv_standing_ind, acaiv2.CREATED_BY, acaiv2.CREATION_DATE, acaiv2.LAST_UPDATED_BY, acaiv2.LAST_UPDATE_DATE, acaiv2.LAST_UPDATE_LOGIN, acaiv2.appl_inst_status

View Text - HTML Formatted

SELECT AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, NVL(AAAV1.SEQUENCE_NUMBER
, ACAIV1.SEQUENCE_NUMBER)
, AAAV1.HIST_START_DT
, AAAV1.HIST_END_DT
, AAAV1.HIST_WHO
, NVL(AAAV1.ADM_CAL_TYPE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_CAL_TYPE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_CAL_TYPE))
, NVL(AAAV1.ADM_CI_SEQUENCE_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_CI_SEQUENCE_NUMBER'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ADM_CI_SEQUENCE_NUMBER))
, NVL(AAAV1.COURSE_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'COURSE_CD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 6)
, ACAIV1.COURSE_CD))
, NVL(AAAV1.CRV_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'CRV_VERSION_NUMBER'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.CRV_VERSION_NUMBER))
, NVL(AAAV1.LOCATION_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'LOCATION_CD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.LOCATION_CD))
, NVL(AAAV1.ATTENDANCE_MODE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ATTENDANCE_MODE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 2)
, ACAIV1.ATTENDANCE_MODE))
, NVL(AAAV1.ATTENDANCE_TYPE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ATTENDANCE_TYPE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 2)
, ACAIV1.ATTENDANCE_TYPE))
, NVL(AAAV1.UNIT_SET_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'UNIT_SET_CD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.UNIT_SET_CD))
, NVL(AAAV1.US_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'US_VERSION_NUMBER'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.US_VERSION_NUMBER))
, NVL(AAAV1.PREFERENCE_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'PREFERENCE_NUMBER'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.PREFERENCE_NUMBER))
, NVL(AAAV1.ADM_DOC_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_DOC_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_DOC_STATUS))
, NVL(AAAV1.ADM_ENTRY_QUAL_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_ENTRY_QUAL_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_ENTRY_QUAL_STATUS))
, NVL(AAAV1.LATE_ADM_FEE_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'LATE_ADM_FEE_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.LATE_ADM_FEE_STATUS))
, NVL(AAAV1.ADM_OUTCOME_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OUTCOME_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_OUTCOME_STATUS))
, NVL(AAAV1.ADM_OTCM_STATUS_AUTH_PERSON_ID
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OTCM_STATUS_AUTH_PERSON_ID'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ADM_OTCM_STATUS_AUTH_PERSON_ID))
, NVL(AAAV1.ADM_OUTCOME_STATUS_AUTH_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OUTCOME_STATUS_AUTH_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ADM_OUTCOME_STATUS_AUTH_DT))
, NVL(AAAV1.ADM_OUTCOME_STATUS_REASON
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OUTCOME_STATUS_REASON'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 60)
, ACAIV1.ADM_OUTCOME_STATUS_REASON))
, NVL(AAAV1.OFFER_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'OFFER_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.OFFER_DT))
, NVL(AAAV1.OFFER_RESPONSE_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'OFFER_RESPONSE_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.OFFER_RESPONSE_DT))
, NVL(AAAV1.PRPSD_COMMENCEMENT_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'PRPSD_COMMENCEMENT_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.PRPSD_COMMENCEMENT_DT))
, NVL(AAAV1.ADM_CNDTNL_OFFER_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_CNDTNL_OFFER_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_CNDTNL_OFFER_STATUS))
, NVL(AAAV1.CNDTNL_OFFER_SATISFIED_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'CNDTNL_OFFER_SATISFIED_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.CNDTNL_OFFER_SATISFIED_DT))
, NVL(AAAV1.CNDTNL_OFFER_MUST_BE_STSFD_IND
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'CNDTNL_OFFER_MUST_BE_STSFD_IND'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 1)
, ACAIV1.CNDTNL_OFFER_MUST_BE_STSFD_IND))
, NVL(AAAV1.ADM_OFFER_RESP_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OFFER_RESP_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_OFFER_RESP_STATUS))
, NVL(AAAV1.ACTUAL_RESPONSE_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ACTUAL_RESPONSE_DT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ACTUAL_RESPONSE_DT))
, NVL(AAAV1.ADM_OFFER_DFRMNT_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ADM_OFFER_DFRMNT_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADM_OFFER_DFRMNT_STATUS))
, NVL(AAAV1.DEFERRED_ADM_CAL_TYPE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'DEFERRED_ADM_CAL_TYPE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.DEFERRED_ADM_CAL_TYPE))
, NVL(AAAV1.DEFERRED_ADM_CI_SEQUENCE_NUM
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'DEFERRED_ADM_CI_SEQUENCE_NUM'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.DEFERRED_ADM_CI_SEQUENCE_NUM))
, NVL(AAAV1.DEFERRED_TRACKING_ID
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'DEFERRED_TRACKING_ID'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.DEFERRED_TRACKING_ID))
, NVL(AAAV1.ASS_RANK
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ASS_RANK'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ASS_RANK))
, NVL(AAAV1.SECONDARY_ASS_RANK
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'SECONDARY_ASS_RANK'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.SECONDARY_ASS_RANK))
, NVL(AAAV1.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'INTRNTNL_ACCEPTANCE_ADVICE_NUM'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.INTRNTNL_ACCEPTANCE_ADVICE_NUM))
, NVL(AAAV1.ASS_TRACKING_ID
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ASS_TRACKING_ID'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.ASS_TRACKING_ID))
, NVL(AAAV1.FEE_CAT
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'FEE_CAT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.FEE_CAT))
, NVL(AAAV1.HECS_PAYMENT_OPTION
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'HECS_PAYMENT_OPTION'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.HECS_PAYMENT_OPTION))
, NVL(AAAV1.EXPECTED_COMPLETION_YR
, NVL(TO_NUMBER(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'EXPECTED_COMPLETION_YR'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT))
, ACAIV1.EXPECTED_COMPLETION_YR))
, NVL(AAAV1.EXPECTED_COMPLETION_PERD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'EXPECTED_COMPLETION_PERD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 1)
, ACAIV1.EXPECTED_COMPLETION_PERD))
, NVL(AAAV1.CORRESPONDENCE_CAT
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'CORRESPONDENCE_CAT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.CORRESPONDENCE_CAT))
, NVL(AAAV1.ENROLMENT_CAT
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'ENROLMENT_CAT'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ENROLMENT_CAT))
, NVL(AAAV1.FUNDING_SOURCE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'FUNDING_SOURCE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.FUNDING_SOURCE))
, NVL(AAAV1.APPLICANT_ACPTNCE_CNDTN
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'APPLICANT_ACPTNCE_CNDTN'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 2000)
, ACAIV1.APPLICANT_ACPTNCE_CNDTN))
, NVL(AAAV1.CNDTNL_OFFER_CNDTN
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'CNDTNL_OFFER_CNDTN'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 2000)
, ACAIV1.CNDTNL_OFFER_CNDTN))
, NVL(AAAV1.TRANSFER_COURSE_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'TRANSFER_COURSE_CD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 6)
, ACAIV1.TRANSFER_COURSE_CD))
, NVL(AAAV1.BASIS_FOR_ADMISSION_TYPE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'BASIS_FOR_ADMISSION_TYPE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.BASIS_FOR_ADMISSION_TYPE))
, NVL(AAAV1.ADMISSION_CD
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'ADMISSION_CD'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.ADMISSION_CD))
, NVL(AAAV1.COURSE_RANK_SET
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'COURSE_RANK_SET'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 30)
, ACAIV1.COURSE_RANK_SET))
, NVL(AAAV1.COURSE_RANK_SCHEDULE
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'COURSE_RANK_SCHEDULE'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.COURSE_RANK_SCHEDULE))
, NVL(AAAV1.REQ_FOR_RECONSIDERATION_IND
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'REQ_FOR_RECONSIDERATION_IND'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 1)
, ACAIV1.REQ_FOR_RECONSIDERATION_IND))
, NVL(AAAV1.REQ_FOR_ADV_STANDING_IND
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAH_COL( 'REQ_FOR_ADV_STANDING_IND'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.HIST_END_DT)
, 1
, 1)
, ACAIV1.REQ_FOR_ADV_STANDING_IND))
, AAAV1.CREATED_BY
, AAAV1.CREATION_DATE
, AAAV1.LAST_UPDATED_BY
, AAAV1.LAST_UPDATE_DATE
, AAAV1.LAST_UPDATE_LOGIN
, NVL(AAAV1.APPL_INST_STATUS
, NVL(SUBSTR(IGS_AU_GEN_001.AUDP_GET_ACAIH_COL( 'APPL_INST_STATUS'
, AAAV1.PERSON_ID
, AAAV1.ADMISSION_APPL_NUMBER
, AAAV1.NOMINATED_COURSE_CD
, AAAV1.SEQUENCE_NUMBER
, AAAV1.HIST_END_DT)
, 1
, 10)
, ACAIV1.APPL_INST_STATUS))
FROM IGS_AD_PS_APLINSTHST_APLHIST_V AAAV1
, IGS_AD_PS_APPL_INST_APLINST_V ACAIV1
WHERE ACAIV1.PERSON_ID = AAAV1.PERSON_ID
AND ACAIV1.ADMISSION_APPL_NUMBER = AAAV1.ADMISSION_APPL_NUMBER
AND ACAIV1.NOMINATED_COURSE_CD = AAAV1.NOMINATED_COURSE_CD
AND (AAAV1.SEQUENCE_NUMBER IS NULL OR ACAIV1.SEQUENCE_NUMBER = AAAV1.SEQUENCE_NUMBER) UNION SELECT ACAIV2.PERSON_ID
, ACAIV2.ADMISSION_APPL_NUMBER
, ACAIV2.NOMINATED_COURSE_CD
, ACAIV2.SEQUENCE_NUMBER
, NVL( MAX(AAAV2.HIST_END_DT)
, GREATEST(ACAIV2.LAST_UPDATE_DATE
, ACA.LAST_UPDATE_DATE))
, IGS_GE_DATE.IGSDATE(NULL)
, ACAIV2.LAST_UPDATED_BY
, ACAIV2.ADM_CAL_TYPE
, ACAIV2.ADM_CI_SEQUENCE_NUMBER
, ACAIV2.COURSE_CD
, ACAIV2.CRV_VERSION_NUMBER
, ACAIV2.LOCATION_CD
, ACAIV2.ATTENDANCE_MODE
, ACAIV2.ATTENDANCE_TYPE
, ACAIV2.UNIT_SET_CD
, ACAIV2.US_VERSION_NUMBER
, ACAIV2.PREFERENCE_NUMBER
, ACAIV2.ADM_DOC_STATUS
, ACAIV2.ADM_ENTRY_QUAL_STATUS
, ACAIV2.LATE_ADM_FEE_STATUS
, ACAIV2.ADM_OUTCOME_STATUS
, ACAIV2.ADM_OTCM_STATUS_AUTH_PERSON_ID
, ACAIV2.ADM_OUTCOME_STATUS_AUTH_DT
, ACAIV2.ADM_OUTCOME_STATUS_REASON
, ACAIV2.OFFER_DT
, ACAIV2.OFFER_RESPONSE_DT
, ACAIV2.PRPSD_COMMENCEMENT_DT
, ACAIV2.ADM_CNDTNL_OFFER_STATUS
, ACAIV2.CNDTNL_OFFER_SATISFIED_DT
, ACAIV2.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAIV2.ADM_OFFER_RESP_STATUS
, ACAIV2.ACTUAL_RESPONSE_DT
, ACAIV2.ADM_OFFER_DFRMNT_STATUS
, ACAIV2.DEFERRED_ADM_CAL_TYPE
, ACAIV2.DEFERRED_ADM_CI_SEQUENCE_NUM
, ACAIV2.DEFERRED_TRACKING_ID
, ACAIV2.ASS_RANK
, ACAIV2.SECONDARY_ASS_RANK
, ACAIV2.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ACAIV2.ASS_TRACKING_ID
, ACAIV2.FEE_CAT
, ACAIV2.HECS_PAYMENT_OPTION
, ACAIV2.EXPECTED_COMPLETION_YR
, ACAIV2.EXPECTED_COMPLETION_PERD
, ACAIV2.CORRESPONDENCE_CAT
, ACAIV2.ENROLMENT_CAT
, ACAIV2.FUNDING_SOURCE
, ACAIV2.APPLICANT_ACPTNCE_CNDTN
, ACAIV2.CNDTNL_OFFER_CNDTN
, ACAIV2.TRANSFER_COURSE_CD
, ACAIV2.BASIS_FOR_ADMISSION_TYPE
, ACAIV2.ADMISSION_CD
, ACAIV2.COURSE_RANK_SET
, ACAIV2.COURSE_RANK_SCHEDULE
, ACAIV2.REQ_FOR_RECONSIDERATION_IND
, ACAIV2.REQ_FOR_ADV_STANDING_IND
, ACAIV2.CREATED_BY
, ACAIV2.CREATION_DATE
, ACAIV2.LAST_UPDATED_BY
, ACAIV2.LAST_UPDATE_DATE
, ACAIV2.LAST_UPDATE_LOGIN
, ACAIV2.APPL_INST_STATUS
FROM IGS_AD_PS_APLINSTHST_APLHIST_V AAAV2
, IGS_AD_PS_APPL_INST_APLINST_V ACAIV2
, IGS_AD_PS_APPL ACA
WHERE ACAIV2.PERSON_ID = AAAV2.PERSON_ID (+)
AND ACAIV2.ADMISSION_APPL_NUMBER = AAAV2.ADMISSION_APPL_NUMBER (+)
AND ACAIV2.NOMINATED_COURSE_CD = AAAV2.NOMINATED_COURSE_CD (+)
AND ACAIV2.SEQUENCE_NUMBER = AAAV2.SEQUENCE_NUMBER (+)
AND ACA.PERSON_ID = ACAIV2.PERSON_ID
AND ACA.ADMISSION_APPL_NUMBER = ACAIV2.ADMISSION_APPL_NUMBER
AND ACA.NOMINATED_COURSE_CD = ACAIV2.NOMINATED_COURSE_CD GROUP BY ACAIV2.PERSON_ID
, ACAIV2.ADMISSION_APPL_NUMBER
, ACAIV2.NOMINATED_COURSE_CD
, ACAIV2.SEQUENCE_NUMBER
, ACA.LAST_UPDATE_DATE
, ACAIV2.ADM_CAL_TYPE
, ACAIV2.ADM_CI_SEQUENCE_NUMBER
, ACAIV2.COURSE_CD
, ACAIV2.CRV_VERSION_NUMBER
, ACAIV2.LOCATION_CD
, ACAIV2.ATTENDANCE_MODE
, ACAIV2.ATTENDANCE_TYPE
, ACAIV2.UNIT_SET_CD
, ACAIV2.US_VERSION_NUMBER
, ACAIV2.PREFERENCE_NUMBER
, ACAIV2.ADM_DOC_STATUS
, ACAIV2.ADM_ENTRY_QUAL_STATUS
, ACAIV2.LATE_ADM_FEE_STATUS
, ACAIV2.ADM_OUTCOME_STATUS
, ACAIV2.ADM_OTCM_STATUS_AUTH_PERSON_ID
, ACAIV2.ADM_OUTCOME_STATUS_AUTH_DT
, ACAIV2.ADM_OUTCOME_STATUS_REASON
, ACAIV2.OFFER_DT
, ACAIV2.OFFER_RESPONSE_DT
, ACAIV2.PRPSD_COMMENCEMENT_DT
, ACAIV2.ADM_CNDTNL_OFFER_STATUS
, ACAIV2.CNDTNL_OFFER_SATISFIED_DT
, ACAIV2.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAIV2.ADM_OFFER_RESP_STATUS
, ACAIV2.ACTUAL_RESPONSE_DT
, ACAIV2.ADM_OFFER_DFRMNT_STATUS
, ACAIV2.DEFERRED_ADM_CAL_TYPE
, ACAIV2.DEFERRED_ADM_CI_SEQUENCE_NUM
, ACAIV2.DEFERRED_TRACKING_ID
, ACAIV2.ASS_RANK
, ACAIV2.SECONDARY_ASS_RANK
, ACAIV2.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ACAIV2.ASS_TRACKING_ID
, ACAIV2.FEE_CAT
, ACAIV2.HECS_PAYMENT_OPTION
, ACAIV2.EXPECTED_COMPLETION_YR
, ACAIV2.EXPECTED_COMPLETION_PERD
, ACAIV2.CORRESPONDENCE_CAT
, ACAIV2.ENROLMENT_CAT
, ACAIV2.FUNDING_SOURCE
, ACAIV2.APPLICANT_ACPTNCE_CNDTN
, ACAIV2.CNDTNL_OFFER_CNDTN
, ACAIV2.TRANSFER_COURSE_CD
, ACAIV2.BASIS_FOR_ADMISSION_TYPE
, ACAIV2.ADMISSION_CD
, ACAIV2.COURSE_RANK_SET
, ACAIV2.COURSE_RANK_SCHEDULE
, ACAIV2.REQ_FOR_RECONSIDERATION_IND
, ACAIV2.REQ_FOR_ADV_STANDING_IND
, ACAIV2.CREATED_BY
, ACAIV2.CREATION_DATE
, ACAIV2.LAST_UPDATED_BY
, ACAIV2.LAST_UPDATE_DATE
, ACAIV2.LAST_UPDATE_LOGIN
, ACAIV2.APPL_INST_STATUS