VIEW: APPS.IGS_AD_PS_APLINSTHST_APLHIST_V

Source

View Text - Preformatted

SELECT acaih.person_id, acaih.admission_appl_number, acaih.nominated_course_cd, acaih.sequence_number, acaih.hist_start_dt, acaih.hist_end_dt, acaih.hist_who, acaih.hist_offer_round_number, acaih.adm_cal_type, acaih.adm_ci_sequence_number, acaih.course_cd, acaih.crv_version_number, acaih.location_cd, acaih.attendance_mode, acaih.attendance_type, acaih.unit_set_cd, acaih.us_version_number, acaih.preference_number, acaih.adm_doc_status, acaih.adm_entry_qual_status, acaih.late_adm_fee_status, acaih.adm_outcome_status, acaih.adm_otcm_status_auth_person_id, acaih.adm_outcome_status_auth_dt, acaih.adm_outcome_status_reason, acaih.offer_dt, acaih.offer_response_dt, acaih.prpsd_commencement_dt, acaih.adm_cndtnl_offer_status, acaih.cndtnl_offer_satisfied_dt, acaih.cndtnl_offer_must_be_stsfd_ind, acaih.adm_offer_resp_status, acaih.actual_response_dt, acaih.adm_offer_dfrmnt_status, acaih.deferred_adm_cal_type, acaih.deferred_adm_ci_sequence_num, acaih.deferred_tracking_id, acaih.ass_rank, acaih.secondary_ass_rank, acaih.intrntnl_acceptance_advice_num, acaih.ass_tracking_id, acaih.fee_cat, acaih.hecs_payment_option, acaih.expected_completion_yr, acaih.expected_completion_perd, acaih.correspondence_cat, acaih.enrolment_cat, acaih.funding_source, acaih.applicant_acptnce_cndtn, acaih.cndtnl_offer_cndtn, acah.transfer_course_cd, acah.basis_for_admission_type, acah.admission_cd, acah.course_rank_set, acah.course_rank_schedule, acah.req_for_reconsideration_ind, acah.req_for_adv_standing_ind, acaih.created_by, acaih.creation_date, acaih.last_updated_by, acaih.last_update_date, acaih.last_update_login, acaih.appl_inst_status FROM IGS_AD_PS_APLINSTHST acaih, IGS_AD_PS_APPL_HIST acah WHERE acah.person_id = acaih.person_id AND acah.admission_appl_number = acaih.admission_appl_number AND acah.nominated_course_cd = acaih.nominated_course_cd AND acah.hist_start_dt = acaih.hist_start_dt UNION SELECT acaih.person_id, acaih.admission_appl_number, acaih.nominated_course_cd, acaih.sequence_number, acaih.hist_start_dt, acaih.hist_end_dt, acaih.hist_who, acaih.hist_offer_round_number, acaih.adm_cal_type, acaih.adm_ci_sequence_number, acaih.course_cd, acaih.crv_version_number, acaih.location_cd, acaih.attendance_mode, acaih.attendance_type, acaih.unit_set_cd, acaih.us_version_number, acaih.preference_number, acaih.adm_doc_status, acaih.adm_entry_qual_status, acaih.late_adm_fee_status, acaih.adm_outcome_status, acaih.adm_otcm_status_auth_person_id, acaih.adm_outcome_status_auth_dt, acaih.adm_outcome_status_reason, acaih.offer_dt, acaih.offer_response_dt, acaih.prpsd_commencement_dt, acaih.adm_cndtnl_offer_status, acaih.cndtnl_offer_satisfied_dt, acaih.cndtnl_offer_must_be_stsfd_ind, acaih.adm_offer_resp_status, acaih.actual_response_dt, acaih.adm_offer_dfrmnt_status, acaih.deferred_adm_cal_type, acaih.deferred_adm_ci_sequence_num, acaih.deferred_tracking_id, acaih.ass_rank, acaih.secondary_ass_rank, acaih.intrntnl_acceptance_advice_num, acaih.ass_tracking_id, acaih.fee_cat, acaih.hecs_payment_option, acaih.expected_completion_yr, acaih.expected_completion_perd, acaih.correspondence_cat, acaih.enrolment_cat, acaih.funding_source, acaih.applicant_acptnce_cndtn, acaih.cndtnl_offer_cndtn, NULL, NULL, NULL, NULL, NULL, NULL, NULL, acaih.created_by, acaih.creation_date, acaih.last_updated_by, acaih.last_update_date, acaih.last_update_login, acaih.appl_inst_status FROM IGS_AD_PS_APLINSTHST acaih WHERE NOT EXISTS ( SELECT 'X' FROM IGS_AD_PS_APPL_HIST acah WHERE acah.person_id = acaih.person_id AND acah.admission_appl_number = acaih.admission_appl_number AND acah.nominated_course_cd = acaih.nominated_course_cd AND acah.hist_start_dt = acaih.hist_start_dt) UNION SELECT acah.person_id, acah.admission_appl_number, acah.nominated_course_cd, TO_NUMBER(NULL), acah.hist_start_dt, acah.hist_end_dt, acah.hist_who, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, TO_NUMBER(NULL), NULL, NULL, NULL, NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, NULL, NULL, NULL, TO_NUMBER(NULL), IGS_GE_DATE.IGSDATE(NULL), NULL, IGS_GE_DATE.IGSDATE(NULL), IGS_GE_DATE.IGSDATE(NULL), IGS_GE_DATE.IGSDATE(NULL), NULL, IGS_GE_DATE.IGSDATE(NULL), NULL, NULL, IGS_GE_DATE.IGSDATE(NULL), NULL, NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, NULL, TO_NUMBER(NULL), NULL, NULL, NULL, NULL, NULL, NULL, acah.transfer_course_cd, acah.basis_for_admission_type, acah.admission_cd, acah.course_rank_set, acah.course_rank_schedule, acah.req_for_reconsideration_ind, acah.req_for_adv_standing_ind, acah.created_by, acah.creation_date, acah.last_updated_by, acah.last_update_date, acah.last_update_login, NULL FROM IGS_AD_PS_APPL_HIST acah WHERE NOT EXISTS ( SELECT 'X' FROM IGS_AD_PS_APLINSTHST acaih WHERE acaih.person_id = acah.person_id AND acaih.admission_appl_number = acah.admission_appl_number AND acaih.nominated_course_cd = acah.nominated_course_cd AND acaih.hist_start_dt = acah.hist_start_dt)

View Text - HTML Formatted

SELECT ACAIH.PERSON_ID
, ACAIH.ADMISSION_APPL_NUMBER
, ACAIH.NOMINATED_COURSE_CD
, ACAIH.SEQUENCE_NUMBER
, ACAIH.HIST_START_DT
, ACAIH.HIST_END_DT
, ACAIH.HIST_WHO
, ACAIH.HIST_OFFER_ROUND_NUMBER
, ACAIH.ADM_CAL_TYPE
, ACAIH.ADM_CI_SEQUENCE_NUMBER
, ACAIH.COURSE_CD
, ACAIH.CRV_VERSION_NUMBER
, ACAIH.LOCATION_CD
, ACAIH.ATTENDANCE_MODE
, ACAIH.ATTENDANCE_TYPE
, ACAIH.UNIT_SET_CD
, ACAIH.US_VERSION_NUMBER
, ACAIH.PREFERENCE_NUMBER
, ACAIH.ADM_DOC_STATUS
, ACAIH.ADM_ENTRY_QUAL_STATUS
, ACAIH.LATE_ADM_FEE_STATUS
, ACAIH.ADM_OUTCOME_STATUS
, ACAIH.ADM_OTCM_STATUS_AUTH_PERSON_ID
, ACAIH.ADM_OUTCOME_STATUS_AUTH_DT
, ACAIH.ADM_OUTCOME_STATUS_REASON
, ACAIH.OFFER_DT
, ACAIH.OFFER_RESPONSE_DT
, ACAIH.PRPSD_COMMENCEMENT_DT
, ACAIH.ADM_CNDTNL_OFFER_STATUS
, ACAIH.CNDTNL_OFFER_SATISFIED_DT
, ACAIH.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAIH.ADM_OFFER_RESP_STATUS
, ACAIH.ACTUAL_RESPONSE_DT
, ACAIH.ADM_OFFER_DFRMNT_STATUS
, ACAIH.DEFERRED_ADM_CAL_TYPE
, ACAIH.DEFERRED_ADM_CI_SEQUENCE_NUM
, ACAIH.DEFERRED_TRACKING_ID
, ACAIH.ASS_RANK
, ACAIH.SECONDARY_ASS_RANK
, ACAIH.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ACAIH.ASS_TRACKING_ID
, ACAIH.FEE_CAT
, ACAIH.HECS_PAYMENT_OPTION
, ACAIH.EXPECTED_COMPLETION_YR
, ACAIH.EXPECTED_COMPLETION_PERD
, ACAIH.CORRESPONDENCE_CAT
, ACAIH.ENROLMENT_CAT
, ACAIH.FUNDING_SOURCE
, ACAIH.APPLICANT_ACPTNCE_CNDTN
, ACAIH.CNDTNL_OFFER_CNDTN
, ACAH.TRANSFER_COURSE_CD
, ACAH.BASIS_FOR_ADMISSION_TYPE
, ACAH.ADMISSION_CD
, ACAH.COURSE_RANK_SET
, ACAH.COURSE_RANK_SCHEDULE
, ACAH.REQ_FOR_RECONSIDERATION_IND
, ACAH.REQ_FOR_ADV_STANDING_IND
, ACAIH.CREATED_BY
, ACAIH.CREATION_DATE
, ACAIH.LAST_UPDATED_BY
, ACAIH.LAST_UPDATE_DATE
, ACAIH.LAST_UPDATE_LOGIN
, ACAIH.APPL_INST_STATUS
FROM IGS_AD_PS_APLINSTHST ACAIH
, IGS_AD_PS_APPL_HIST ACAH
WHERE ACAH.PERSON_ID = ACAIH.PERSON_ID
AND ACAH.ADMISSION_APPL_NUMBER = ACAIH.ADMISSION_APPL_NUMBER
AND ACAH.NOMINATED_COURSE_CD = ACAIH.NOMINATED_COURSE_CD
AND ACAH.HIST_START_DT = ACAIH.HIST_START_DT UNION SELECT ACAIH.PERSON_ID
, ACAIH.ADMISSION_APPL_NUMBER
, ACAIH.NOMINATED_COURSE_CD
, ACAIH.SEQUENCE_NUMBER
, ACAIH.HIST_START_DT
, ACAIH.HIST_END_DT
, ACAIH.HIST_WHO
, ACAIH.HIST_OFFER_ROUND_NUMBER
, ACAIH.ADM_CAL_TYPE
, ACAIH.ADM_CI_SEQUENCE_NUMBER
, ACAIH.COURSE_CD
, ACAIH.CRV_VERSION_NUMBER
, ACAIH.LOCATION_CD
, ACAIH.ATTENDANCE_MODE
, ACAIH.ATTENDANCE_TYPE
, ACAIH.UNIT_SET_CD
, ACAIH.US_VERSION_NUMBER
, ACAIH.PREFERENCE_NUMBER
, ACAIH.ADM_DOC_STATUS
, ACAIH.ADM_ENTRY_QUAL_STATUS
, ACAIH.LATE_ADM_FEE_STATUS
, ACAIH.ADM_OUTCOME_STATUS
, ACAIH.ADM_OTCM_STATUS_AUTH_PERSON_ID
, ACAIH.ADM_OUTCOME_STATUS_AUTH_DT
, ACAIH.ADM_OUTCOME_STATUS_REASON
, ACAIH.OFFER_DT
, ACAIH.OFFER_RESPONSE_DT
, ACAIH.PRPSD_COMMENCEMENT_DT
, ACAIH.ADM_CNDTNL_OFFER_STATUS
, ACAIH.CNDTNL_OFFER_SATISFIED_DT
, ACAIH.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAIH.ADM_OFFER_RESP_STATUS
, ACAIH.ACTUAL_RESPONSE_DT
, ACAIH.ADM_OFFER_DFRMNT_STATUS
, ACAIH.DEFERRED_ADM_CAL_TYPE
, ACAIH.DEFERRED_ADM_CI_SEQUENCE_NUM
, ACAIH.DEFERRED_TRACKING_ID
, ACAIH.ASS_RANK
, ACAIH.SECONDARY_ASS_RANK
, ACAIH.INTRNTNL_ACCEPTANCE_ADVICE_NUM
, ACAIH.ASS_TRACKING_ID
, ACAIH.FEE_CAT
, ACAIH.HECS_PAYMENT_OPTION
, ACAIH.EXPECTED_COMPLETION_YR
, ACAIH.EXPECTED_COMPLETION_PERD
, ACAIH.CORRESPONDENCE_CAT
, ACAIH.ENROLMENT_CAT
, ACAIH.FUNDING_SOURCE
, ACAIH.APPLICANT_ACPTNCE_CNDTN
, ACAIH.CNDTNL_OFFER_CNDTN
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ACAIH.CREATED_BY
, ACAIH.CREATION_DATE
, ACAIH.LAST_UPDATED_BY
, ACAIH.LAST_UPDATE_DATE
, ACAIH.LAST_UPDATE_LOGIN
, ACAIH.APPL_INST_STATUS
FROM IGS_AD_PS_APLINSTHST ACAIH
WHERE NOT EXISTS ( SELECT 'X'
FROM IGS_AD_PS_APPL_HIST ACAH
WHERE ACAH.PERSON_ID = ACAIH.PERSON_ID
AND ACAH.ADMISSION_APPL_NUMBER = ACAIH.ADMISSION_APPL_NUMBER
AND ACAH.NOMINATED_COURSE_CD = ACAIH.NOMINATED_COURSE_CD
AND ACAH.HIST_START_DT = ACAIH.HIST_START_DT) UNION SELECT ACAH.PERSON_ID
, ACAH.ADMISSION_APPL_NUMBER
, ACAH.NOMINATED_COURSE_CD
, TO_NUMBER(NULL)
, ACAH.HIST_START_DT
, ACAH.HIST_END_DT
, ACAH.HIST_WHO
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, IGS_GE_DATE.IGSDATE(NULL)
, NULL
, IGS_GE_DATE.IGSDATE(NULL)
, IGS_GE_DATE.IGSDATE(NULL)
, IGS_GE_DATE.IGSDATE(NULL)
, NULL
, IGS_GE_DATE.IGSDATE(NULL)
, NULL
, NULL
, IGS_GE_DATE.IGSDATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ACAH.TRANSFER_COURSE_CD
, ACAH.BASIS_FOR_ADMISSION_TYPE
, ACAH.ADMISSION_CD
, ACAH.COURSE_RANK_SET
, ACAH.COURSE_RANK_SCHEDULE
, ACAH.REQ_FOR_RECONSIDERATION_IND
, ACAH.REQ_FOR_ADV_STANDING_IND
, ACAH.CREATED_BY
, ACAH.CREATION_DATE
, ACAH.LAST_UPDATED_BY
, ACAH.LAST_UPDATE_DATE
, ACAH.LAST_UPDATE_LOGIN
, NULL
FROM IGS_AD_PS_APPL_HIST ACAH
WHERE NOT EXISTS ( SELECT 'X'
FROM IGS_AD_PS_APLINSTHST ACAIH
WHERE ACAIH.PERSON_ID = ACAH.PERSON_ID
AND ACAIH.ADMISSION_APPL_NUMBER = ACAH.ADMISSION_APPL_NUMBER
AND ACAIH.NOMINATED_COURSE_CD = ACAH.NOMINATED_COURSE_CD
AND ACAIH.HIST_START_DT = ACAH.HIST_START_DT)