View: IGS_GR_HIST_V

View Text

SELECT GRH1.PERSON_ID
, GRH1.CREATE_DT
, GRH1.HIST_START_DT
, GRH1.HIST_END_DT
, GRH1.HIST_WHO
, NVL(GRH1.GRD_CAL_TYPE
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'GRD_CAL_TYPE'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.GRD_CAL_TYPE)) GRD_CAL_TYPE
, NVL(GRH1.GRD_CI_SEQUENCE_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'GRD_CI_SEQUENCE_NUMBER'
, GRH1.HIST_END_DT))
, GR1.GRD_CI_SEQUENCE_NUMBER)) GRD_CI_SEQUENCE_NUMBER
, NVL(GRH1.COURSE_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'COURSE_CD'
, GRH1.HIST_END_DT)
, 1
, 6)
, GR1.COURSE_CD)) COURSE_CD
, NVL(GRH1.AWARD_COURSE_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'AWARD_COURSE_CD'
, GRH1.HIST_END_DT)
, 1
, 6)
, GR1.AWARD_COURSE_CD)) AWARD_COURSE_CD
, NVL(GRH1.AWARD_CRS_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'AWARD_CRS_VERSION_NUMBER'
, GRH1.HIST_END_DT))
, GR1.AWARD_CRS_VERSION_NUMBER)) AWARD_CRS_VERSION_NUMBER
, NVL(GRH1.AWARD_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'AWARD_CD'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.AWARD_CD)) AWARD_CD
, NVL(GRH1.HONOURS_LEVEL
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'HONOURS_LEVEL'
, GRH1.HIST_END_DT)
, 1
, 3)
, GR1.HONOURS_LEVEL)) HONOURS_LEVEL
, NVL(GRH1.CONFERRAL_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'CONFERRAL_DT'
, GRH1.HIST_END_DT))
, GR1.CONFERRAL_DT)) CONFERRAL_DT
, NVL(GRH1.GRADUAND_STATUS
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'GRADUAND_STATUS'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.GRADUAND_STATUS)) GRADUAND_STATUS
, NVL(GRH1.GRADUAND_APPR_STATUS
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'IGS_GR_APRV_STAT'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.GRADUAND_APPR_STATUS)) GRADUAND_APPR_STATUS
, NVL(GRH1.S_GRADUAND_TYPE
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'S_GRADUAND_TYPE'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.S_GRADUAND_TYPE)) S_GRADUAND_TYPE
, NVL(GRH1.GRADUATION_NAME
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'GRADUATION_NAME'
, GRH1.HIST_END_DT)
, 1
, 80)
, GR1.GRADUATION_NAME)) GRADUATION_NAME
, NVL(GRH1.PROXY_AWARD_IND
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'PROXY_AWARD_IND'
, GRH1.HIST_END_DT)
, 1
, 1)
, GR1.PROXY_AWARD_IND)) PROXY_AWARD_IND
, NVL(GRH1.PROXY_AWARD_PERSON_ID
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'PROXY_AWARD_PERSON_ID'
, GRH1.HIST_END_DT))
, GR1.PROXY_AWARD_PERSON_ID)) PROXY_AWARD_PERSON_ID
, NVL(GRH1.PREVIOUS_QUALIFICATIONS
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'PREVIOUS_QUALIFICATIONS'
, GRH1.HIST_END_DT)
, 1
, 2000)
, GR1.PREVIOUS_QUALIFICATIONS)) PREVIOUS_QUALIFICATIONS
, NVL(GRH1.CONVOCATION_MEMBERSHIP_IND
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'CONVOCATION_MEMBERSHIP_IND'
, GRH1.HIST_END_DT)
, 1
, 1)
, GR1.CONVOCATION_MEMBERSHIP_IND)) CONVOCATION_MEMBERSHIP_IND
, NVL(GRH1.SUR_FOR_COURSE_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'SUR_FOR_COURSE_CD'
, GRH1.HIST_END_DT)
, 1
, 6)
, GR1.SUR_FOR_COURSE_CD)) SUR_FOR_COURSE_CD
, NVL(GRH1.SUR_FOR_CRS_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'SUR_FOR_CRS_VERSION_NUMBER'
, GRH1.HIST_END_DT))
, GR1.SUR_FOR_CRS_VERSION_NUMBER)) SUR_FOR_CRS_VERSION_NUMBER
, NVL(GRH1.SUR_FOR_AWARD_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'SUR_FOR_AWARD_CD'
, GRH1.HIST_END_DT)
, 1
, 10)
, GR1.SUR_FOR_AWARD_CD)) SUR_FOR_AWARD_CD
, GRH1.CREATED_BY
, GRH1.CREATION_DATE
, GRH1.LAST_UPDATED_BY
, GRH1.LAST_UPDATE_DATE
, GRH1.LAST_UPDATE_LOGIN
, NVL(GRH1.COMMENTS
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_GRH_COL( GRH1.PERSON_ID
, GRH1.CREATE_DT
, 'COOMMENTS'
, GRH1.HIST_END_DT)
, 1
, 2000)
, GR1.COMMENTS)) COMMENTS FROM IGS_GR_GRADUAND GR1
, IGS_GR_GRADUAND_HIST GRH1
WHERE GR1.PERSON_ID = GRH1.PERSON_ID
AND GR1.CREATE_DT = GRH1.CREATE_DT UNION ALL SELECT GR2.PERSON_ID
, GR2.CREATE_DT
, NVL(MAX(GRH2.HIST_END_DT)
, GR2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, GR2.LAST_UPDATED_BY
, GR2.GRD_CAL_TYPE
, GR2.GRD_CI_SEQUENCE_NUMBER
, GR2.COURSE_CD
, GR2.AWARD_COURSE_CD
, GR2.AWARD_CRS_VERSION_NUMBER
, GR2.AWARD_CD
, GR2.HONOURS_LEVEL
, GR2.CONFERRAL_DT
, GR2.GRADUAND_STATUS
, GR2.GRADUAND_APPR_STATUS
, GR2.S_GRADUAND_TYPE
, GR2.GRADUATION_NAME
, GR2.PROXY_AWARD_IND
, GR2.PROXY_AWARD_PERSON_ID
, GR2.PREVIOUS_QUALIFICATIONS
, GR2.CONVOCATION_MEMBERSHIP_IND
, GR2.SUR_FOR_COURSE_CD
, GR2.SUR_FOR_CRS_VERSION_NUMBER
, GR2.SUR_FOR_AWARD_CD
, GR2.CREATED_BY
, GR2.CREATION_DATE
, GR2.LAST_UPDATED_BY
, GR2.LAST_UPDATE_DATE
, GR2.LAST_UPDATE_LOGIN
, GR2.COMMENTS FROM IGS_GR_GRADUAND GR2
, IGS_GR_GRADUAND_HIST GRH2 WHERE GR2.PERSON_ID = GRH2.PERSON_ID (+)
AND GR2.CREATE_DT = GRH2.CREATE_DT (+) GROUP BY GR2.PERSON_ID
, GR2.CREATE_DT
, GR2.GRD_CAL_TYPE
, GR2.GRD_CI_SEQUENCE_NUMBER
, GR2.COURSE_CD
, GR2.AWARD_COURSE_CD
, GR2.AWARD_CRS_VERSION_NUMBER
, GR2.AWARD_CD
, GR2.HONOURS_LEVEL
, GR2.CONFERRAL_DT
, GR2.GRADUAND_STATUS
, GR2.GRADUAND_APPR_STATUS
, GR2.S_GRADUAND_TYPE
, GR2.GRADUATION_NAME
, GR2.PROXY_AWARD_IND
, GR2.PROXY_AWARD_PERSON_ID
, GR2.PREVIOUS_QUALIFICATIONS
, GR2.CONVOCATION_MEMBERSHIP_IND
, GR2.SUR_FOR_COURSE_CD
, GR2.SUR_FOR_CRS_VERSION_NUMBER
, GR2.SUR_FOR_AWARD_CD
, GR2.CREATED_BY
, GR2.CREATION_DATE
, GR2.LAST_UPDATED_BY
, GR2.LAST_UPDATE_DATE
, GR2.LAST_UPDATE_LOGIN
, GR2.COMMENTS

Columns