VIEW: APPS.IGS_AS_SCA_H_V

Source

View Text - Preformatted

SELECT SCAH1.person_id, SCAH1.course_cd, SCAH1.hist_start_dt, SCAH1.hist_end_dt, SCAH1.hist_who, NVL(SCAH1.version_number, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('VERSION_NUMBER',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.version_number)), SUBSTR(NVL(SCAH1.cal_type, NVL(IGS_AU_GEN_003.audp_get_scah_col('cal_type',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.cal_type)),1,10), SUBSTR(NVL(SCAH1.location_cd, NVL(IGS_AU_GEN_003.audp_get_scah_col('LOCATION_CD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.location_cd)),1,10), SUBSTR(NVL(SCAH1.attendance_mode, NVL(IGS_AU_GEN_003.audp_get_scah_col('ATTENDANCE_MODE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.attendance_mode)),1,10), SUBSTR(NVL(SCAH1.attendance_type, NVL(IGS_AU_GEN_003.audp_get_scah_col('ATTENDANCE_TYPE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.attendance_type)),1,10), SUBSTR(NVL(SCAH1.student_confirmed_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('STUDENT_CONFIRMED_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.student_confirmed_ind)),1,1), NVL(SCAH1.commencement_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('COMMENCEMENT_DT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.commencement_dt)), SUBSTR(NVL(SCAH1.MANUAL_OVR_CMPL_DT_IND, NVL(IGS_AU_GEN_003.audp_get_scah_col('MANUAL_OVR_CMPL_DT_IND', SCAH1.person_id, SCAH1.course_cd, SCAH1.hist_end_dt), SCA1.MANUAL_OVR_CMPL_DT_IND)),1,1), NVL(SCAH1.OVERRIDE_CMPL_DT, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('OVERRIDE_CMPL_DT', SCAH1.person_id, SCAH1.course_cd, SCAH1.hist_end_dt)), SCA1.OVERRIDE_CMPL_DT)), SUBSTR(NVL(SCAH1.course_attempt_status, NVL(IGS_AU_GEN_003.audp_get_scah_col('COURSE_ATTEMPT_STATUS',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.course_attempt_status)),1,10), SUBSTR(NVL(SCAH1.progression_status, NVL(IGS_AU_GEN_003.audp_get_scah_col('PROGRESSION_STATUS',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.progression_status)),1,10), SUBSTR(NVL(SCAH1.derived_att_type, NVL(IGS_AU_GEN_003.audp_get_scah_col('DERIVED_ATT_TYPE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.derived_att_type)),1,10), SUBSTR(NVL(SCAH1.derived_att_mode, NVL(IGS_AU_GEN_003.audp_get_scah_col('DERIVED_ATT_MODE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.derived_att_mode)),1,10), SUBSTR(NVL(SCAH1.provisional_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('PROVISIONAL_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.provisional_ind)),1,1), NVL(SCAH1.discontinued_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('DISCONTINUED_DT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.discontinued_dt)), SUBSTR(NVL(SCAH1.discontinuation_reason_cd, NVL(IGS_AU_GEN_003.audp_get_scah_col('DISCONTINUED_REASON_CD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.discontinuation_reason_cd)),1,10), NVL(SCAH1.lapsed_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('LAPSED_DT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.lapsed_dt)), SUBSTR(NVL(SCAH1.funding_source, NVL(IGS_AU_GEN_003.audp_get_scah_col('FUNDING_SOURCE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.funding_source)),1,10), SUBSTR(NVL(SCAH1.exam_location_cd, NVL(IGS_AU_GEN_003.audp_get_scah_col('EXAM_LOCATION_CD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.exam_location_cd)),1,10), NVL(SCAH1.derived_completion_yr, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('DERIVED_COMPLETION_YR',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.derived_completion_yr)), SUBSTR(NVL(SCAH1.derived_completion_perd, NVL(IGS_AU_GEN_003.audp_get_scah_col('DERIVED_COMPLETION_PERD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.derived_completion_perd)),1,1), NVL(SCAH1.nominated_completion_yr, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('NOMINATED_COMPLETION_YR',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.nominated_completion_yr)), SUBSTR(NVL(SCAH1.nominated_completion_perd, NVL(IGS_AU_GEN_003.audp_get_scah_col('NOMINATED_COMPLETION_PERD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.nominated_completion_perd)),1,30), SUBSTR(NVL(SCAH1.rule_check_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('RULE_CHECK_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.rule_check_ind)),1,1), SUBSTR(NVL(SCAH1.waive_option_check_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('WAIVE_OPTION_CHECK_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.waive_option_check_ind)),1,1), NVL(SCAH1.last_rule_check_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('LAST_RUL_CHECK_DT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.last_rule_check_dt)), SUBSTR(NVL(SCAH1.publish_outcomes_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('PUBLISH_OUTCOMES_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.publish_outcomes_ind)),1,1), SUBSTR(NVL(SCAH1.course_rqrmnt_complete_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('COURSE_RQRMNT_COMPLETE_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.course_rqrmnt_complete_ind)),1,1), NVL(SCAH1.course_rqrmnts_complete_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.audp_get_scah_col('COURSE_RQRMNTS_COMPLETE_DT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.course_rqrmnts_complete_dt)), SUBSTR(NVL(SCAH1.s_completed_source_type, NVL(IGS_AU_GEN_003.audp_get_scah_col('S_COMPLETED_SOURCE_TYPE',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.s_completed_source_type)),1,10), NVL(SCAH1.override_time_limitation, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('OVERRIDE_TIME_LIMITATION',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.override_time_limitation)), SUBSTR(NVL(SCAH1.advanced_standing_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('ADVANCED_STANDING_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.advanced_standing_ind)),1,1), SUBSTR(NVL(SCAH1.fee_cat, NVL(IGS_AU_GEN_003.audp_get_scah_col('FEE_CAT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.fee_cat)),1,10), SUBSTR(NVL(SCAH1.correspondence_cat, NVL(IGS_AU_GEN_003.audp_get_scah_col('CORRESPONDENCE_CAT',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.correspondence_cat)),1,10), SUBSTR(NVL(SCAH1.self_help_group_ind, NVL(IGS_AU_GEN_003.audp_get_scah_col('SELF_HELP_GROUP_IND',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.self_help_group_ind)),1,1), NVL(SCAH1.adm_admission_appl_number, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('ADM_ADMISSION_APPL_NUMBER',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.adm_admission_appl_number)), SUBSTR(NVL(SCAH1.adm_nominated_course_cd, NVL(IGS_AU_GEN_003.audp_get_scah_col('ADM_NOMINATED_COURSE_CD',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt), SCA1.adm_nominated_course_cd)),1,6), NVL(SCAH1.adm_sequence_number, NVL( igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('ADM_SEQUENCE_NUMBER',SCAH1.person_id,SCAH1.course_cd,SCAH1.hist_end_dt)), SCA1.adm_sequence_number)), NVL(SCAH1.LAST_DATE_OF_ATTENDANCE,NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAST_DATE_OF_ATTENDANCE',SCAH1.PERSON_ID,SCAH1.COURSE_CD,SCAH1.HIST_END_DT)), SCA1.LAST_DATE_OF_ATTENDANCE)), SUBSTR(NVL(SCAH1.DROPPED_BY, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('DROPPED_BY',SCAH1.PERSON_ID,SCAH1.COURSE_CD,SCAH1.HIST_END_DT), SCA1.DROPPED_BY)),1,30), SUBSTR(NVL(SCAH1.PRIMARY_PROGRAM_TYPE, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('PRIMARY_PROGRAM_TYPE', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT), SCA1.PRIMARY_PROGRAM_TYPE)), 1, 10), SUBSTR(NVL(SCAH1.PRIMARY_PROG_TYPE_SOURCE, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('PRIMARY_PROG_TYPE_SOURCE', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT), SCA1.PRIMARY_PROG_TYPE_SOURCE)), 1, 10), SUBSTR(NVL(SCAH1.CATALOG_CAL_TYPE, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('CATALOG_CAL_TYPE', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT), SCA1.CATALOG_CAL_TYPE)), 1, 10), NVL(SCAH1.CATALOG_SEQ_NUM, NVL(igs_ge_number.to_num(IGS_AU_GEN_003.audp_get_scah_col('CATALOG_SEQ_NUM', SCAH1.person_id, SCAH1.course_cd, SCAH1.hist_end_dt)), SCA1.CATALOG_SEQ_NUM)), SUBSTR(NVL(SCAH1.KEY_PROGRAM, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('KEY_PROGRAM', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT), SCA1.KEY_PROGRAM)), 1, 1), SCAH1.created_by, SCAH1.creation_date, SCAH1.last_updated_by, SCAH1.last_update_date, SCAH1.last_update_login, NVL(SCAH1.COO_ID, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COO_ID', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT)), SCA1.COO_ID)), NVL(SCAH1.IGS_PR_CLASS_STD_ID, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('IGS_PR_CLASS_STD_ID', SCAH1.PERSON_ID, SCAH1.COURSE_CD, SCAH1.HIST_END_DT)), SCA1.IGS_PR_CLASS_STD_ID)) FROM IGS_AS_SC_ATTEMPT_H scah1, IGS_EN_STDNT_PS_ATT_ALL sca1 WHERE SCA1.person_id = SCAH1.person_id AND SCA1.course_cd = SCAH1.course_cd UNION ALL SELECT SCA2.person_id, SCA2.course_cd, NVL(MAX(SCAH2.hist_end_dt), SCA2.last_update_date), TO_DATE(NULL), SCA2.last_updated_by, SCA2.version_number, SCA2.cal_type, SCA2.location_cd, SCA2.attendance_mode, SCA2.attendance_type, SCA2.student_confirmed_ind, SCA2.commencement_dt, SCA2.manual_ovr_cmpl_dt_ind, SCA2.override_cmpl_dt, SCA2.course_attempt_status, SCA2.progression_status, SCA2.derived_att_type, SCA2.derived_att_mode, SCA2.provisional_ind, SCA2.discontinued_dt, SCA2.discontinuation_reason_cd, SCA2.lapsed_dt, SCA2.funding_source, SCA2.exam_location_cd, SCA2.derived_completion_yr, SCA2.derived_completion_perd, SCA2.nominated_completion_yr, SCA2.nominated_completion_perd, SCA2.rule_check_ind, SCA2.waive_option_check_ind, SCA2.last_rule_check_dt, SCA2.publish_outcomes_ind, SCA2.course_rqrmnt_complete_ind, SCA2.course_rqrmnts_complete_dt, SCA2.s_completed_source_type, SCA2.override_time_limitation, SCA2.advanced_standing_ind, SCA2.fee_cat, SCA2.correspondence_cat, SCA2.self_help_group_ind, SCA2.adm_admission_appl_number, SCA2.adm_nominated_course_cd, SCA2.adm_sequence_number, SCA2.last_date_of_attendance, SCA2.dropped_by, SCA2.PRIMARY_PROGRAM_TYPE, SCA2.PRIMARY_PROG_TYPE_SOURCE, SCA2.CATALOG_CAL_TYPE, SCA2.CATALOG_SEQ_NUM, SCA2.KEY_PROGRAM, SCA2.created_by, SCA2.creation_date, SCA2.last_updated_by, SCA2.last_update_date, SCA2.last_update_login, SCA2.COO_ID, SCA2.IGS_PR_CLASS_STD_ID FROM IGS_EN_STDNT_PS_ATT_ALL SCA2, IGS_AS_SC_ATTEMPT_H SCAH2 WHERE SCA2.person_id = SCAH2.person_id(+) AND SCA2.course_cd = SCAH2.course_cd(+) GROUP BY SCA2.person_id, SCA2.course_cd, SCA2.version_number, SCA2.cal_type, SCA2.location_cd, SCA2.attendance_mode, SCA2.attendance_type, SCA2.student_confirmed_ind, SCA2.commencement_dt, SCA2.manual_ovr_cmpl_dt_ind, SCA2.override_cmpl_dt, SCA2.course_attempt_status, SCA2.progression_status, SCA2.derived_att_type, SCA2.derived_att_mode, SCA2.provisional_ind, SCA2.discontinued_dt, SCA2.discontinuation_reason_cd, SCA2.lapsed_dt, SCA2.funding_source, SCA2.exam_location_cd, SCA2.derived_completion_yr, SCA2.derived_completion_perd, SCA2.nominated_completion_yr, SCA2.nominated_completion_perd, SCA2.rule_check_ind, SCA2.waive_option_check_ind, SCA2.last_rule_check_dt, SCA2.publish_outcomes_ind, SCA2.course_rqrmnt_complete_ind, SCA2.course_rqrmnts_complete_dt, SCA2.s_completed_source_type, SCA2.override_time_limitation, SCA2.advanced_standing_ind, SCA2.fee_cat, SCA2.correspondence_cat, SCA2.self_help_group_ind, SCA2.adm_admission_appl_number, SCA2.adm_nominated_course_cd, SCA2.adm_sequence_number, SCA2.LAST_DATE_OF_ATTENDANCE, SCA2.DROPPED_BY, SCA2.PRIMARY_PROGRAM_TYPE, SCA2.PRIMARY_PROG_TYPE_SOURCE, SCA2.CATALOG_CAL_TYPE, SCA2.CATALOG_SEQ_NUM, SCA2.KEY_PROGRAM, SCA2.created_by, SCA2.creation_date, SCA2.last_updated_by, SCA2.last_update_date, SCA2.last_update_login, SCA2.COO_ID, SCA2.IGS_PR_CLASS_STD_ID

View Text - HTML Formatted

SELECT SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_START_DT
, SCAH1.HIST_END_DT
, SCAH1.HIST_WHO
, NVL(SCAH1.VERSION_NUMBER
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('VERSION_NUMBER'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.VERSION_NUMBER))
, SUBSTR(NVL(SCAH1.CAL_TYPE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('CAL_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.CAL_TYPE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.LOCATION_CD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LOCATION_CD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.LOCATION_CD))
, 1
, 10)
, SUBSTR(NVL(SCAH1.ATTENDANCE_MODE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ATTENDANCE_MODE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.ATTENDANCE_MODE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.ATTENDANCE_TYPE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ATTENDANCE_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.ATTENDANCE_TYPE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.STUDENT_CONFIRMED_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('STUDENT_CONFIRMED_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.STUDENT_CONFIRMED_IND))
, 1
, 1)
, NVL(SCAH1.COMMENCEMENT_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COMMENCEMENT_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.COMMENCEMENT_DT))
, SUBSTR(NVL(SCAH1.MANUAL_OVR_CMPL_DT_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('MANUAL_OVR_CMPL_DT_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.MANUAL_OVR_CMPL_DT_IND))
, 1
, 1)
, NVL(SCAH1.OVERRIDE_CMPL_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('OVERRIDE_CMPL_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.OVERRIDE_CMPL_DT))
, SUBSTR(NVL(SCAH1.COURSE_ATTEMPT_STATUS
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_ATTEMPT_STATUS'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.COURSE_ATTEMPT_STATUS))
, 1
, 10)
, SUBSTR(NVL(SCAH1.PROGRESSION_STATUS
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PROGRESSION_STATUS'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.PROGRESSION_STATUS))
, 1
, 10)
, SUBSTR(NVL(SCAH1.DERIVED_ATT_TYPE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_ATT_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.DERIVED_ATT_TYPE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.DERIVED_ATT_MODE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_ATT_MODE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.DERIVED_ATT_MODE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.PROVISIONAL_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PROVISIONAL_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.PROVISIONAL_IND))
, 1
, 1)
, NVL(SCAH1.DISCONTINUED_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DISCONTINUED_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.DISCONTINUED_DT))
, SUBSTR(NVL(SCAH1.DISCONTINUATION_REASON_CD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DISCONTINUED_REASON_CD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.DISCONTINUATION_REASON_CD))
, 1
, 10)
, NVL(SCAH1.LAPSED_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAPSED_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.LAPSED_DT))
, SUBSTR(NVL(SCAH1.FUNDING_SOURCE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('FUNDING_SOURCE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.FUNDING_SOURCE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.EXAM_LOCATION_CD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('EXAM_LOCATION_CD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.EXAM_LOCATION_CD))
, 1
, 10)
, NVL(SCAH1.DERIVED_COMPLETION_YR
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_COMPLETION_YR'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.DERIVED_COMPLETION_YR))
, SUBSTR(NVL(SCAH1.DERIVED_COMPLETION_PERD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_COMPLETION_PERD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.DERIVED_COMPLETION_PERD))
, 1
, 1)
, NVL(SCAH1.NOMINATED_COMPLETION_YR
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('NOMINATED_COMPLETION_YR'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.NOMINATED_COMPLETION_YR))
, SUBSTR(NVL(SCAH1.NOMINATED_COMPLETION_PERD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('NOMINATED_COMPLETION_PERD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.NOMINATED_COMPLETION_PERD))
, 1
, 30)
, SUBSTR(NVL(SCAH1.RULE_CHECK_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('RULE_CHECK_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.RULE_CHECK_IND))
, 1
, 1)
, SUBSTR(NVL(SCAH1.WAIVE_OPTION_CHECK_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('WAIVE_OPTION_CHECK_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.WAIVE_OPTION_CHECK_IND))
, 1
, 1)
, NVL(SCAH1.LAST_RULE_CHECK_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAST_RUL_CHECK_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.LAST_RULE_CHECK_DT))
, SUBSTR(NVL(SCAH1.PUBLISH_OUTCOMES_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PUBLISH_OUTCOMES_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.PUBLISH_OUTCOMES_IND))
, 1
, 1)
, SUBSTR(NVL(SCAH1.COURSE_RQRMNT_COMPLETE_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_RQRMNT_COMPLETE_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.COURSE_RQRMNT_COMPLETE_IND))
, 1
, 1)
, NVL(SCAH1.COURSE_RQRMNTS_COMPLETE_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_RQRMNTS_COMPLETE_DT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.COURSE_RQRMNTS_COMPLETE_DT))
, SUBSTR(NVL(SCAH1.S_COMPLETED_SOURCE_TYPE
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('S_COMPLETED_SOURCE_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.S_COMPLETED_SOURCE_TYPE))
, 1
, 10)
, NVL(SCAH1.OVERRIDE_TIME_LIMITATION
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('OVERRIDE_TIME_LIMITATION'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.OVERRIDE_TIME_LIMITATION))
, SUBSTR(NVL(SCAH1.ADVANCED_STANDING_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADVANCED_STANDING_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.ADVANCED_STANDING_IND))
, 1
, 1)
, SUBSTR(NVL(SCAH1.FEE_CAT
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('FEE_CAT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.FEE_CAT))
, 1
, 10)
, SUBSTR(NVL(SCAH1.CORRESPONDENCE_CAT
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('CORRESPONDENCE_CAT'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.CORRESPONDENCE_CAT))
, 1
, 10)
, SUBSTR(NVL(SCAH1.SELF_HELP_GROUP_IND
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('SELF_HELP_GROUP_IND'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.SELF_HELP_GROUP_IND))
, 1
, 1)
, NVL(SCAH1.ADM_ADMISSION_APPL_NUMBER
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_ADMISSION_APPL_NUMBER'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.ADM_ADMISSION_APPL_NUMBER))
, SUBSTR(NVL(SCAH1.ADM_NOMINATED_COURSE_CD
, NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_NOMINATED_COURSE_CD'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.ADM_NOMINATED_COURSE_CD))
, 1
, 6)
, NVL(SCAH1.ADM_SEQUENCE_NUMBER
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_SEQUENCE_NUMBER'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.ADM_SEQUENCE_NUMBER))
, NVL(SCAH1.LAST_DATE_OF_ATTENDANCE
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAST_DATE_OF_ATTENDANCE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.LAST_DATE_OF_ATTENDANCE))
, SUBSTR(NVL(SCAH1.DROPPED_BY
, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('DROPPED_BY'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.DROPPED_BY))
, 1
, 30)
, SUBSTR(NVL(SCAH1.PRIMARY_PROGRAM_TYPE
, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('PRIMARY_PROGRAM_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.PRIMARY_PROGRAM_TYPE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.PRIMARY_PROG_TYPE_SOURCE
, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('PRIMARY_PROG_TYPE_SOURCE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.PRIMARY_PROG_TYPE_SOURCE))
, 1
, 10)
, SUBSTR(NVL(SCAH1.CATALOG_CAL_TYPE
, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('CATALOG_CAL_TYPE'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.CATALOG_CAL_TYPE))
, 1
, 10)
, NVL(SCAH1.CATALOG_SEQ_NUM
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('CATALOG_SEQ_NUM'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.CATALOG_SEQ_NUM))
, SUBSTR(NVL(SCAH1.KEY_PROGRAM
, NVL( IGS_AU_GEN_003.AUDP_GET_SCAH_COL ('KEY_PROGRAM'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT)
, SCA1.KEY_PROGRAM))
, 1
, 1)
, SCAH1.CREATED_BY
, SCAH1.CREATION_DATE
, SCAH1.LAST_UPDATED_BY
, SCAH1.LAST_UPDATE_DATE
, SCAH1.LAST_UPDATE_LOGIN
, NVL(SCAH1.COO_ID
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COO_ID'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.COO_ID))
, NVL(SCAH1.IGS_PR_CLASS_STD_ID
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('IGS_PR_CLASS_STD_ID'
, SCAH1.PERSON_ID
, SCAH1.COURSE_CD
, SCAH1.HIST_END_DT))
, SCA1.IGS_PR_CLASS_STD_ID))
FROM IGS_AS_SC_ATTEMPT_H SCAH1
, IGS_EN_STDNT_PS_ATT_ALL SCA1
WHERE SCA1.PERSON_ID = SCAH1.PERSON_ID
AND SCA1.COURSE_CD = SCAH1.COURSE_CD UNION ALL SELECT SCA2.PERSON_ID
, SCA2.COURSE_CD
, NVL(MAX(SCAH2.HIST_END_DT)
, SCA2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, SCA2.LAST_UPDATED_BY
, SCA2.VERSION_NUMBER
, SCA2.CAL_TYPE
, SCA2.LOCATION_CD
, SCA2.ATTENDANCE_MODE
, SCA2.ATTENDANCE_TYPE
, SCA2.STUDENT_CONFIRMED_IND
, SCA2.COMMENCEMENT_DT
, SCA2.MANUAL_OVR_CMPL_DT_IND
, SCA2.OVERRIDE_CMPL_DT
, SCA2.COURSE_ATTEMPT_STATUS
, SCA2.PROGRESSION_STATUS
, SCA2.DERIVED_ATT_TYPE
, SCA2.DERIVED_ATT_MODE
, SCA2.PROVISIONAL_IND
, SCA2.DISCONTINUED_DT
, SCA2.DISCONTINUATION_REASON_CD
, SCA2.LAPSED_DT
, SCA2.FUNDING_SOURCE
, SCA2.EXAM_LOCATION_CD
, SCA2.DERIVED_COMPLETION_YR
, SCA2.DERIVED_COMPLETION_PERD
, SCA2.NOMINATED_COMPLETION_YR
, SCA2.NOMINATED_COMPLETION_PERD
, SCA2.RULE_CHECK_IND
, SCA2.WAIVE_OPTION_CHECK_IND
, SCA2.LAST_RULE_CHECK_DT
, SCA2.PUBLISH_OUTCOMES_IND
, SCA2.COURSE_RQRMNT_COMPLETE_IND
, SCA2.COURSE_RQRMNTS_COMPLETE_DT
, SCA2.S_COMPLETED_SOURCE_TYPE
, SCA2.OVERRIDE_TIME_LIMITATION
, SCA2.ADVANCED_STANDING_IND
, SCA2.FEE_CAT
, SCA2.CORRESPONDENCE_CAT
, SCA2.SELF_HELP_GROUP_IND
, SCA2.ADM_ADMISSION_APPL_NUMBER
, SCA2.ADM_NOMINATED_COURSE_CD
, SCA2.ADM_SEQUENCE_NUMBER
, SCA2.LAST_DATE_OF_ATTENDANCE
, SCA2.DROPPED_BY
, SCA2.PRIMARY_PROGRAM_TYPE
, SCA2.PRIMARY_PROG_TYPE_SOURCE
, SCA2.CATALOG_CAL_TYPE
, SCA2.CATALOG_SEQ_NUM
, SCA2.KEY_PROGRAM
, SCA2.CREATED_BY
, SCA2.CREATION_DATE
, SCA2.LAST_UPDATED_BY
, SCA2.LAST_UPDATE_DATE
, SCA2.LAST_UPDATE_LOGIN
, SCA2.COO_ID
, SCA2.IGS_PR_CLASS_STD_ID
FROM IGS_EN_STDNT_PS_ATT_ALL SCA2
, IGS_AS_SC_ATTEMPT_H SCAH2
WHERE SCA2.PERSON_ID = SCAH2.PERSON_ID(+)
AND SCA2.COURSE_CD = SCAH2.COURSE_CD(+) GROUP BY SCA2.PERSON_ID
, SCA2.COURSE_CD
, SCA2.VERSION_NUMBER
, SCA2.CAL_TYPE
, SCA2.LOCATION_CD
, SCA2.ATTENDANCE_MODE
, SCA2.ATTENDANCE_TYPE
, SCA2.STUDENT_CONFIRMED_IND
, SCA2.COMMENCEMENT_DT
, SCA2.MANUAL_OVR_CMPL_DT_IND
, SCA2.OVERRIDE_CMPL_DT
, SCA2.COURSE_ATTEMPT_STATUS
, SCA2.PROGRESSION_STATUS
, SCA2.DERIVED_ATT_TYPE
, SCA2.DERIVED_ATT_MODE
, SCA2.PROVISIONAL_IND
, SCA2.DISCONTINUED_DT
, SCA2.DISCONTINUATION_REASON_CD
, SCA2.LAPSED_DT
, SCA2.FUNDING_SOURCE
, SCA2.EXAM_LOCATION_CD
, SCA2.DERIVED_COMPLETION_YR
, SCA2.DERIVED_COMPLETION_PERD
, SCA2.NOMINATED_COMPLETION_YR
, SCA2.NOMINATED_COMPLETION_PERD
, SCA2.RULE_CHECK_IND
, SCA2.WAIVE_OPTION_CHECK_IND
, SCA2.LAST_RULE_CHECK_DT
, SCA2.PUBLISH_OUTCOMES_IND
, SCA2.COURSE_RQRMNT_COMPLETE_IND
, SCA2.COURSE_RQRMNTS_COMPLETE_DT
, SCA2.S_COMPLETED_SOURCE_TYPE
, SCA2.OVERRIDE_TIME_LIMITATION
, SCA2.ADVANCED_STANDING_IND
, SCA2.FEE_CAT
, SCA2.CORRESPONDENCE_CAT
, SCA2.SELF_HELP_GROUP_IND
, SCA2.ADM_ADMISSION_APPL_NUMBER
, SCA2.ADM_NOMINATED_COURSE_CD
, SCA2.ADM_SEQUENCE_NUMBER
, SCA2.LAST_DATE_OF_ATTENDANCE
, SCA2.DROPPED_BY
, SCA2.PRIMARY_PROGRAM_TYPE
, SCA2.PRIMARY_PROG_TYPE_SOURCE
, SCA2.CATALOG_CAL_TYPE
, SCA2.CATALOG_SEQ_NUM
, SCA2.KEY_PROGRAM
, SCA2.CREATED_BY
, SCA2.CREATION_DATE
, SCA2.LAST_UPDATED_BY
, SCA2.LAST_UPDATE_DATE
, SCA2.LAST_UPDATE_LOGIN
, SCA2.COO_ID
, SCA2.IGS_PR_CLASS_STD_ID