VIEW: APPS.IGS_AS_STD_UNT_ATMPT_OTCM_H_V

Source

View Text - Preformatted

SELECT suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.hist_end_dt, suaoh1.hist_who, NVL(suaoh1.grading_schema_cd, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'GRADING_SCHEMA_CD', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 10), suao1.grading_schema_cd)), NVL(suaoh1.version_number, NVL(TO_NUMBER(IGS_AU_GEN_003.audp_get_suaoh_col( 'VERSION_NUMBER', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id)), suao1.version_number)), NVL(suaoh1.grade, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'GRADE', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 5), suao1.grade)), NVL(suaoh1.s_grade_creation_method_type, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'S_GRADE_CREATION_METHOD_TYPE', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 10), suao1.s_grade_creation_method_type)), NVL(suaoh1.finalised_outcome_ind, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'FINALISED_OUTCOME_IND', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 1), suao1.finalised_outcome_ind)), NVL(suaoh1.mark, NVL(TO_NUMBER(IGS_AU_GEN_003.audp_get_suaoh_col( 'MARK', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id)), suao1.mark)), NVL(suaoh1.number_times_keyed, NVL(TO_NUMBER(IGS_AU_GEN_003.audp_get_suaoh_col( 'NUMBER_TIMES_KEYED', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id)), suao1.number_times_keyed)), NVL(suaoh1.translated_grading_schema_cd, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'TRANSLATED_GRADING_SCHEMA_CD', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 10), suao1.translated_grading_schema_cd)), NVL(suaoh1.translated_version_number, NVL(TO_NUMBER(IGS_AU_GEN_003.audp_get_suaoh_col( 'TRANSLATED_VERSION_NUMBER', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id)), suao1.translated_version_number)), NVL(suaoh1.translated_grade, NVL(SUBSTR(IGS_AU_GEN_003.audp_get_suaoh_col( 'TRANSLATED_GRADE', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id),1, 5), suao1.translated_grade)), NVL(suaoh1.translated_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_suaoh_col( 'TRANSLATED_DT', suaoh1.person_id, suaoh1.course_cd, suaoh1.unit_cd, suaoh1.cal_type, suaoh1.ci_sequence_number, suaoh1.outcome_dt, suaoh1.hist_start_dt, suaoh1.uoo_id)), suao1.translated_dt)), suaoh1.created_by, suaoh1.creation_date, suaoh1.last_updated_by, suaoh1.last_update_date, suaoh1.last_update_login, suaoh1.uoo_id FROM IGS_AS_SU_ATMPTOUT_H suaoh1, IGS_AS_SU_STMPTOUT suao1 WHERE suao1.person_id = suaoh1.person_id AND suao1.course_cd = suaoh1.course_cd AND suao1.uoo_id = suaoh1.uoo_id AND suao1.outcome_dt = suaoh1.outcome_dt UNION ALL SELECT suao2.person_id, suao2.course_cd, suao2.unit_cd, suao2.cal_type, suao2.ci_sequence_number, suao2.outcome_dt, NVL(MAX(suaoh2.hist_end_dt),suao2.last_update_date), TO_DATE(NULL), suao2.last_updated_by, suao2.grading_schema_cd, suao2.version_number, suao2.grade, suao2.s_grade_creation_method_type, suao2.finalised_outcome_ind, suao2.mark, suao2.number_times_keyed, suao2.translated_grading_schema_cd, suao2.translated_version_number, suao2.translated_grade, suao2.translated_dt, suao2.created_by, suao2.creation_date, suao2.last_updated_by, suao2.last_update_date, suao2.last_update_login , suao2.uoo_id FROM IGS_AS_SU_ATMPTOUT_H suaoh2, IGS_AS_SU_STMPTOUT suao2 WHERE suao2.person_id = suaoh2.person_id(+) AND suao2.course_cd = suaoh2.course_cd(+) AND suao2.uoo_id = suaoh2.uoo_id(+) AND suao2.outcome_dt = suaoh2.outcome_dt(+) GROUP BY suao2.person_id, suao2.course_cd, suao2.unit_cd, suao2.cal_type, suao2.ci_sequence_number, suao2.outcome_dt, suao2.ci_start_dt, suao2.ci_end_dt, suao2.outcome_dt, suao2.grading_schema_cd, suao2.version_number, suao2.grade, suao2.s_grade_creation_method_type, suao2.finalised_outcome_ind, suao2.mark, suao2.number_times_keyed, suao2.translated_grading_schema_cd, suao2.translated_version_number, suao2.translated_grade, suao2.translated_dt, suao2.created_by, suao2.creation_date, suao2.last_updated_by, suao2.last_update_date, suao2.last_update_login, suao2.uoo_id

View Text - HTML Formatted

SELECT SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.HIST_END_DT
, SUAOH1.HIST_WHO
, NVL(SUAOH1.GRADING_SCHEMA_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'GRADING_SCHEMA_CD'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 10)
, SUAO1.GRADING_SCHEMA_CD))
, NVL(SUAOH1.VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'VERSION_NUMBER'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID))
, SUAO1.VERSION_NUMBER))
, NVL(SUAOH1.GRADE
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'GRADE'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 5)
, SUAO1.GRADE))
, NVL(SUAOH1.S_GRADE_CREATION_METHOD_TYPE
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'S_GRADE_CREATION_METHOD_TYPE'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 10)
, SUAO1.S_GRADE_CREATION_METHOD_TYPE))
, NVL(SUAOH1.FINALISED_OUTCOME_IND
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'FINALISED_OUTCOME_IND'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 1)
, SUAO1.FINALISED_OUTCOME_IND))
, NVL(SUAOH1.MARK
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'MARK'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID))
, SUAO1.MARK))
, NVL(SUAOH1.NUMBER_TIMES_KEYED
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'NUMBER_TIMES_KEYED'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID))
, SUAO1.NUMBER_TIMES_KEYED))
, NVL(SUAOH1.TRANSLATED_GRADING_SCHEMA_CD
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'TRANSLATED_GRADING_SCHEMA_CD'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 10)
, SUAO1.TRANSLATED_GRADING_SCHEMA_CD))
, NVL(SUAOH1.TRANSLATED_VERSION_NUMBER
, NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'TRANSLATED_VERSION_NUMBER'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID))
, SUAO1.TRANSLATED_VERSION_NUMBER))
, NVL(SUAOH1.TRANSLATED_GRADE
, NVL(SUBSTR(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'TRANSLATED_GRADE'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID)
, 1
, 5)
, SUAO1.TRANSLATED_GRADE))
, NVL(SUAOH1.TRANSLATED_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SUAOH_COL( 'TRANSLATED_DT'
, SUAOH1.PERSON_ID
, SUAOH1.COURSE_CD
, SUAOH1.UNIT_CD
, SUAOH1.CAL_TYPE
, SUAOH1.CI_SEQUENCE_NUMBER
, SUAOH1.OUTCOME_DT
, SUAOH1.HIST_START_DT
, SUAOH1.UOO_ID))
, SUAO1.TRANSLATED_DT))
, SUAOH1.CREATED_BY
, SUAOH1.CREATION_DATE
, SUAOH1.LAST_UPDATED_BY
, SUAOH1.LAST_UPDATE_DATE
, SUAOH1.LAST_UPDATE_LOGIN
, SUAOH1.UOO_ID
FROM IGS_AS_SU_ATMPTOUT_H SUAOH1
, IGS_AS_SU_STMPTOUT SUAO1
WHERE SUAO1.PERSON_ID = SUAOH1.PERSON_ID
AND SUAO1.COURSE_CD = SUAOH1.COURSE_CD
AND SUAO1.UOO_ID = SUAOH1.UOO_ID
AND SUAO1.OUTCOME_DT = SUAOH1.OUTCOME_DT UNION ALL SELECT SUAO2.PERSON_ID
, SUAO2.COURSE_CD
, SUAO2.UNIT_CD
, SUAO2.CAL_TYPE
, SUAO2.CI_SEQUENCE_NUMBER
, SUAO2.OUTCOME_DT
, NVL(MAX(SUAOH2.HIST_END_DT)
, SUAO2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, SUAO2.LAST_UPDATED_BY
, SUAO2.GRADING_SCHEMA_CD
, SUAO2.VERSION_NUMBER
, SUAO2.GRADE
, SUAO2.S_GRADE_CREATION_METHOD_TYPE
, SUAO2.FINALISED_OUTCOME_IND
, SUAO2.MARK
, SUAO2.NUMBER_TIMES_KEYED
, SUAO2.TRANSLATED_GRADING_SCHEMA_CD
, SUAO2.TRANSLATED_VERSION_NUMBER
, SUAO2.TRANSLATED_GRADE
, SUAO2.TRANSLATED_DT
, SUAO2.CREATED_BY
, SUAO2.CREATION_DATE
, SUAO2.LAST_UPDATED_BY
, SUAO2.LAST_UPDATE_DATE
, SUAO2.LAST_UPDATE_LOGIN
, SUAO2.UOO_ID
FROM IGS_AS_SU_ATMPTOUT_H SUAOH2
, IGS_AS_SU_STMPTOUT SUAO2
WHERE SUAO2.PERSON_ID = SUAOH2.PERSON_ID(+)
AND SUAO2.COURSE_CD = SUAOH2.COURSE_CD(+)
AND SUAO2.UOO_ID = SUAOH2.UOO_ID(+)
AND SUAO2.OUTCOME_DT = SUAOH2.OUTCOME_DT(+) GROUP BY SUAO2.PERSON_ID
, SUAO2.COURSE_CD
, SUAO2.UNIT_CD
, SUAO2.CAL_TYPE
, SUAO2.CI_SEQUENCE_NUMBER
, SUAO2.OUTCOME_DT
, SUAO2.CI_START_DT
, SUAO2.CI_END_DT
, SUAO2.OUTCOME_DT
, SUAO2.GRADING_SCHEMA_CD
, SUAO2.VERSION_NUMBER
, SUAO2.GRADE
, SUAO2.S_GRADE_CREATION_METHOD_TYPE
, SUAO2.FINALISED_OUTCOME_IND
, SUAO2.MARK
, SUAO2.NUMBER_TIMES_KEYED
, SUAO2.TRANSLATED_GRADING_SCHEMA_CD
, SUAO2.TRANSLATED_VERSION_NUMBER
, SUAO2.TRANSLATED_GRADE
, SUAO2.TRANSLATED_DT
, SUAO2.CREATED_BY
, SUAO2.CREATION_DATE
, SUAO2.LAST_UPDATED_BY
, SUAO2.LAST_UPDATE_DATE
, SUAO2.LAST_UPDATE_LOGIN
, SUAO2.UOO_ID