VIEW: APPS.IGS_AD_APPL_HIST_APHSTRY_V

Source

View Text - Preformatted

SELECT aah1.row_id row_id, aah1.person_id, pe.person_number, aah1.admission_appl_number, aah1.hist_start_dt, aah1.hist_end_dt, aah1.hist_who, NVL( aah1.appl_dt, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_001.audp_get_aah_col( 'APPL_DT', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt)), aa1.appl_dt)), ci1.alternate_code, ci1.cal_type, ci1.sequence_number, ci2.alternate_code, ci2.cal_type, ci2.sequence_number, NVL( aah1.admission_cat, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'ADMISSION_CAT', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.admission_cat)), NVL( aah1.s_admission_process_type, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'S_ADMISSION_PROCESS_TYPE', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.s_admission_process_type)), NVL( aah1.ADM_APPL_STATUS, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'ADM_APPL_STATUS', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.ADM_APPL_STATUS)), NVL( aah1.ADM_FEE_STATUS, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'ADM_FEE_STATUS', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.ADM_FEE_STATUS)), NVL( aah1.tac_appl_ind, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'TAC_APPL_IND', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.tac_appl_ind)), aah1.created_by, aah1.creation_date, aah1.last_updated_by, aah1.last_update_date, aah1.last_update_login FROM IGS_AD_APPL_HIST aah1, IGS_AD_APPL aa1, IGS_CA_INST ci1, IGS_CA_INST ci2, IGS_PE_PERSON_BASE_V pe WHERE aa1.person_id = aah1.person_id AND aah1.person_id = pe.person_id AND aa1.admission_appl_number = aah1.admission_appl_number AND ci1.cal_type = NVL( aah1.acad_cal_type, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'ACAD_CAL_TYPE', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.acad_cal_type)) AND ci1.sequence_number = NVL( aah1.acad_ci_sequence_number, NVL( TO_NUMBER( IGS_AU_GEN_001.audp_get_aah_col( 'ACAD_CI_SEQUENCE_NUMBER', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt)), aa1.acad_ci_sequence_number)) AND ci2.cal_type = NVL( aah1.adm_cal_type, NVL( IGS_AU_GEN_001.audp_get_aah_col( 'ADM_CAL_TYPE', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt), aa1.adm_cal_type)) AND ci2.sequence_number = NVL( aah1.adm_ci_sequence_number, NVL( TO_NUMBER( IGS_AU_GEN_001.audp_get_aah_col( 'ADM_CI_SEQUENCE_NUMBER', aah1.person_id, aah1.admission_appl_number, aah1.hist_end_dt)), aa1.adm_ci_sequence_number)) UNION SELECT aa2.row_id, aa2.person_id, p1.person_number, aa2.admission_appl_number, aa2.last_update_date, IGS_GE_DATE.IGSDATE(NULL), aa2.last_updated_by, aa2.appl_dt, ci3.alternate_code, aa2.acad_cal_type, aa2.acad_ci_sequence_number, ci4.alternate_code, aa2.adm_cal_type, aa2.adm_ci_sequence_number, aa2.admission_cat, aa2.s_admission_process_type, aa2.ADM_APPL_STATUS, aa2.ADM_FEE_STATUS, aa2.tac_appl_ind, aa2.created_by, aa2.creation_date, aa2.last_updated_by, aa2.last_update_date, aa2.last_update_login FROM IGS_AD_APPL aa2, IGS_CA_INST ci3, IGS_CA_INST ci4, IGS_PE_PERSON_BASE_V p1 WHERE ci3.cal_type = aa2.acad_cal_type AND ci3.sequence_number = aa2.acad_ci_sequence_number AND ci4.cal_type = aa2.adm_cal_type AND ci4.sequence_number = aa2.adm_ci_sequence_number AND p1.person_id = aa2.person_id

View Text - HTML Formatted

SELECT AAH1.ROW_ID ROW_ID
, AAH1.PERSON_ID
, PE.PERSON_NUMBER
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_START_DT
, AAH1.HIST_END_DT
, AAH1.HIST_WHO
, NVL( AAH1.APPL_DT
, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'APPL_DT'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT))
, AA1.APPL_DT))
, CI1.ALTERNATE_CODE
, CI1.CAL_TYPE
, CI1.SEQUENCE_NUMBER
, CI2.ALTERNATE_CODE
, CI2.CAL_TYPE
, CI2.SEQUENCE_NUMBER
, NVL( AAH1.ADMISSION_CAT
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ADMISSION_CAT'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.ADMISSION_CAT))
, NVL( AAH1.S_ADMISSION_PROCESS_TYPE
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'S_ADMISSION_PROCESS_TYPE'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.S_ADMISSION_PROCESS_TYPE))
, NVL( AAH1.ADM_APPL_STATUS
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ADM_APPL_STATUS'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.ADM_APPL_STATUS))
, NVL( AAH1.ADM_FEE_STATUS
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ADM_FEE_STATUS'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.ADM_FEE_STATUS))
, NVL( AAH1.TAC_APPL_IND
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'TAC_APPL_IND'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.TAC_APPL_IND))
, AAH1.CREATED_BY
, AAH1.CREATION_DATE
, AAH1.LAST_UPDATED_BY
, AAH1.LAST_UPDATE_DATE
, AAH1.LAST_UPDATE_LOGIN
FROM IGS_AD_APPL_HIST AAH1
, IGS_AD_APPL AA1
, IGS_CA_INST CI1
, IGS_CA_INST CI2
, IGS_PE_PERSON_BASE_V PE
WHERE AA1.PERSON_ID = AAH1.PERSON_ID
AND AAH1.PERSON_ID = PE.PERSON_ID
AND AA1.ADMISSION_APPL_NUMBER = AAH1.ADMISSION_APPL_NUMBER
AND CI1.CAL_TYPE = NVL( AAH1.ACAD_CAL_TYPE
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ACAD_CAL_TYPE'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.ACAD_CAL_TYPE))
AND CI1.SEQUENCE_NUMBER = NVL( AAH1.ACAD_CI_SEQUENCE_NUMBER
, NVL( TO_NUMBER( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ACAD_CI_SEQUENCE_NUMBER'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT))
, AA1.ACAD_CI_SEQUENCE_NUMBER))
AND CI2.CAL_TYPE = NVL( AAH1.ADM_CAL_TYPE
, NVL( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ADM_CAL_TYPE'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT)
, AA1.ADM_CAL_TYPE))
AND CI2.SEQUENCE_NUMBER = NVL( AAH1.ADM_CI_SEQUENCE_NUMBER
, NVL( TO_NUMBER( IGS_AU_GEN_001.AUDP_GET_AAH_COL( 'ADM_CI_SEQUENCE_NUMBER'
, AAH1.PERSON_ID
, AAH1.ADMISSION_APPL_NUMBER
, AAH1.HIST_END_DT))
, AA1.ADM_CI_SEQUENCE_NUMBER)) UNION SELECT AA2.ROW_ID
, AA2.PERSON_ID
, P1.PERSON_NUMBER
, AA2.ADMISSION_APPL_NUMBER
, AA2.LAST_UPDATE_DATE
, IGS_GE_DATE.IGSDATE(NULL)
, AA2.LAST_UPDATED_BY
, AA2.APPL_DT
, CI3.ALTERNATE_CODE
, AA2.ACAD_CAL_TYPE
, AA2.ACAD_CI_SEQUENCE_NUMBER
, CI4.ALTERNATE_CODE
, AA2.ADM_CAL_TYPE
, AA2.ADM_CI_SEQUENCE_NUMBER
, AA2.ADMISSION_CAT
, AA2.S_ADMISSION_PROCESS_TYPE
, AA2.ADM_APPL_STATUS
, AA2.ADM_FEE_STATUS
, AA2.TAC_APPL_IND
, AA2.CREATED_BY
, AA2.CREATION_DATE
, AA2.LAST_UPDATED_BY
, AA2.LAST_UPDATE_DATE
, AA2.LAST_UPDATE_LOGIN
FROM IGS_AD_APPL AA2
, IGS_CA_INST CI3
, IGS_CA_INST CI4
, IGS_PE_PERSON_BASE_V P1
WHERE CI3.CAL_TYPE = AA2.ACAD_CAL_TYPE
AND CI3.SEQUENCE_NUMBER = AA2.ACAD_CI_SEQUENCE_NUMBER
AND CI4.CAL_TYPE = AA2.ADM_CAL_TYPE
AND CI4.SEQUENCE_NUMBER = AA2.ADM_CI_SEQUENCE_NUMBER
AND P1.PERSON_ID = AA2.PERSON_ID