View: IGS_AD_APPL_HIST_APHSTRY_V

View Text

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

Columns