View: IGS_AS_SUSAH_V

Product:	IGS - Student System
Description:	This view is used to merge student unit set attempt history with the current student unit set attempt details enabling all details to be accessed over time, up till the current day. Each entry represents all column values over defined time
Implementation/DBA Data:	APPS.IGS_AS_SUSAH_V

View Text

SELECT SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_START_DT
, SUSAH1.HIST_END_DT
, SUSAH1.HIST_WHO
, NVL( SUSAH1.US_VERSION_NUMBER
, NVL( TO_NUMBER( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'US_VERSION_NUMBER'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT) )
, SUSA1.US_VERSION_NUMBER) )
, NVL( SUSAH1.SELECTION_DT
, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'SELECTION_DT'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT))
, SUSA1.SELECTION_DT ))
, NVL( SUSAH1.STUDENT_CONFIRMED_IND
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'STUDENT_CONFIRMED_IND'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 1)
, SUSA1.STUDENT_CONFIRMED_IND) )
, NVL( SUSAH1.END_DT
, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'END_DT'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT))
, SUSA1.END_DT) )
, NVL( SUSAH1.PARENT_UNIT_SET_CD
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'PARENT_UNIT_SET_CD'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 10)
, SUSA1.PARENT_UNIT_SET_CD) )
, NVL( SUSAH1.PARENT_SEQUENCE_NUMBER
, NVL( TO_NUMBER( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'PARENT_SEQUENCE_NUMBER'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT) )
, SUSA1.PARENT_SEQUENCE_NUMBER) )
, NVL( SUSAH1.PRIMARY_SET_IND
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'PRIMARY_SET_IND'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 1)
, SUSA1.PRIMARY_SET_IND) )
, NVL( SUSAH1.VOLUNTARY_END_IND
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'VOLUNTARY_END_IND'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 1)
, SUSA1.VOLUNTARY_END_IND) )
, NVL( SUSAH1.AUTHORISED_PERSON_ID
, NVL( TO_NUMBER( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'AUTHORISED_PERSON_ID'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT ) )
, SUSA1.AUTHORISED_PERSON_ID) )
, NVL( SUSAH1.AUTHORISED_ON
, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'AUTHORISED_ON'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT) )
, SUSA1.AUTHORISED_ON) )
, NVL( SUSAH1.OVERRIDE_TITLE
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'OVERRIDE_TITLE'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 90)
, SUSA1.OVERRIDE_TITLE) )
, NVL( SUSAH1.RQRMNTS_COMPLETE_IND
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'RQRMNTS_COMPLETE_IND'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 1)
, SUSA1.RQRMNTS_COMPLETE_IND) )
, NVL( SUSAH1.RQRMNTS_COMPLETE_DT
, NVL( IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'RQRMNTS_COMPLETE_DT'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT) )
, SUSA1.RQRMNTS_COMPLETE_DT) )
, NVL( SUSAH1.S_COMPLETED_SOURCE_TYPE
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'S_COMPLETED_SOURCE_TYPE'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 10)
, SUSA1.S_COMPLETED_SOURCE_TYPE) )
, NVL( SUSAH1.CATALOG_CAL_TYPE
, NVL( SUBSTR( IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'CATALOG_CAL_TYPE '
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT)
, 1
, 10)
, SUSA1.CATALOG_CAL_TYPE) )
, NVL( SUSAH1.CATALOG_SEQ_NUM
, NVL( TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUSAH_COL( 'CATALOG_SEQ_NUM'
, SUSAH1.PERSON_ID
, SUSAH1.COURSE_CD
, SUSAH1.UNIT_SET_CD
, SUSAH1.SEQUENCE_NUMBER
, SUSAH1.HIST_END_DT))
, SUSA1.CATALOG_SEQ_NUM) )
, SUSAH1.CREATED_BY
, SUSAH1.CREATION_DATE
, SUSAH1.LAST_UPDATED_BY
, SUSAH1.LAST_UPDATE_DATE
, SUSAH1.LAST_UPDATE_LOGIN FROM IGS_AS_SU_SETATMPT_H SUSAH1
, IGS_AS_SU_SETATMPT SUSA1
WHERE SUSA1.PERSON_ID = SUSAH1.PERSON_ID
AND SUSA1.COURSE_CD = SUSAH1.COURSE_CD
AND SUSA1.UNIT_SET_CD = SUSAH1.UNIT_SET_CD
AND SUSA1.SEQUENCE_NUMBER = SUSAH1.SEQUENCE_NUMBER UNION ALL SELECT SUSA2.PERSON_ID
, SUSA2.COURSE_CD
, SUSA2.UNIT_SET_CD
, SUSA2.SEQUENCE_NUMBER
, NVL( MAX(SUSAH2.HIST_END_DT)
, SUSA2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, SUSA2.LAST_UPDATED_BY
, SUSA2.US_VERSION_NUMBER
, SUSA2.SELECTION_DT
, SUSA2.STUDENT_CONFIRMED_IND
, SUSA2.END_DT
, SUSA2.PARENT_UNIT_SET_CD
, SUSA2.PARENT_SEQUENCE_NUMBER
, SUSA2.PRIMARY_SET_IND
, SUSA2.VOLUNTARY_END_IND
, SUSA2.AUTHORISED_PERSON_ID
, SUSA2.AUTHORISED_ON
, SUSA2.OVERRIDE_TITLE
, SUSA2.RQRMNTS_COMPLETE_IND
, SUSA2.RQRMNTS_COMPLETE_DT
, SUSA2.S_COMPLETED_SOURCE_TYPE
, SUSA2.CATALOG_CAL_TYPE
, SUSA2.CATALOG_SEQ_NUM
, SUSA2.CREATED_BY
, SUSA2.CREATION_DATE
, SUSA2.LAST_UPDATED_BY
, SUSA2.LAST_UPDATE_DATE
, SUSA2.LAST_UPDATE_LOGIN FROM IGS_AS_SU_SETATMPT_H SUSAH2
, IGS_AS_SU_SETATMPT SUSA2 WHERE SUSA2.PERSON_ID = SUSAH2.PERSON_ID (+)
AND SUSA2.COURSE_CD = SUSAH2.COURSE_CD (+)
AND SUSA2.UNIT_SET_CD = SUSAH2.UNIT_SET_CD (+)
AND SUSA2.SEQUENCE_NUMBER = SUSAH2.SEQUENCE_NUMBER (+) GROUP BY SUSA2.PERSON_ID
, SUSA2.COURSE_CD
, SUSA2.UNIT_SET_CD
, SUSA2.SEQUENCE_NUMBER
, SUSA2.US_VERSION_NUMBER
, SUSA2.SELECTION_DT
, SUSA2.STUDENT_CONFIRMED_IND
, SUSA2.END_DT
, SUSA2.PARENT_UNIT_SET_CD
, SUSA2.PARENT_SEQUENCE_NUMBER
, SUSA2.PRIMARY_SET_IND
, SUSA2.VOLUNTARY_END_IND
, SUSA2.AUTHORISED_PERSON_ID
, SUSA2.AUTHORISED_ON
, SUSA2.OVERRIDE_TITLE
, SUSA2.RQRMNTS_COMPLETE_IND
, SUSA2.RQRMNTS_COMPLETE_DT
, SUSA2.S_COMPLETED_SOURCE_TYPE
, SUSA2.CATALOG_CAL_TYPE
, SUSA2.CATALOG_SEQ_NUM
, SUSA2.CREATED_BY
, SUSA2.CREATION_DATE
, SUSA2.LAST_UPDATED_BY
, SUSA2.LAST_UPDATE_DATE
, SUSA2.LAST_UPDATE_LOGIN

Columns