VIEW: APPS.IGS_AS_SUSAH_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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