View: IGS_AD_PS_APLINSTHST_HISTORY_V

View Text

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

Columns