Product: | IGS - Student System (Obsolete) |
---|---|
Description: | This view is used to merge student unit attempt outcome history with the current student unit attempt outcome details enabling all details to be accessed over time, up till the current day. Each entry represents all column values over defin |
Implementation/DBA Data: | Not implemented in this database |
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