Product: | IGS - Student System |
---|---|
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: | APPS.IGS_AS_STD_UNT_ATMPT_OTCM_H_V |
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