VIEW: APPS.IGS_GR_HIST_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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