VIEW: APPS.IGS_PS_VER_HIST_V

Source

View Text - Preformatted

SELECT cvh1.course_cd, cvh1.version_number, cvh1.hist_start_dt, cvh1.hist_end_dt, cvh1.hist_who, NVL( cvh1.start_dt, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'START_DT', cvh1.hist_end_dt)), cv1.start_dt)), NVL( cvh1.review_dt, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'REVIEW_DT', cvh1.hist_end_dt)), cv1.review_dt)), NVL( cvh1.expiry_dt, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'EXPIRY_DT', cvh1.hist_end_dt)),cv1.expiry_dt)), NVL( cvh1.end_dt, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'END_DT', cvh1.hist_end_dt)), cv1.end_dt)), NVL( cvh1.COURSE_STATUS, NVL( SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'COURSE_STATUS', cvh1.hist_end_dt),1,10), cv1.COURSE_STATUS)), NVL( cvh1.title, NVL( SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'TITLE', cvh1.hist_end_dt), 1, 90), cv1.title)), NVL( cvh1.short_title, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'SHORT_TITLE', cvh1.hist_end_dt), 1, 40), cv1.short_title)), NVL( cvh1.abbreviation, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'ABBREVIATION', cvh1.hist_end_dt), 1, 20), cv1.abbreviation)), NVL( cvh1.supp_exam_permitted_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'SUPP_EXAM_PERMITTED_IND', cvh1.hist_end_dt), 1, 1),cv1.supp_exam_permitted_ind)), NVL( cvh1.generic_course_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'GENERIC_COURSE_IND', cvh1.hist_end_dt), 1, 1), cv1.generic_course_ind)), NVL( cvh1.graduate_students_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'GRADUATE_STUDENTS_IND', cvh1.hist_end_dt), 1, 1), cv1.graduate_students_ind)), NVL( cvh1.count_intrmsn_in_time_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'COUNT_INTRMSN_IN_TIME_IND', cvh1.hist_end_dt), 1, 1), cv1.count_intrmsn_in_time_ind)), NVL( cvh1.intrmsn_allowed_ind, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'INTRMSN_ALLOWED_IND', cvh1.hist_end_dt), 1, 1), cv1.intrmsn_allowed_ind)), NVL( cvh1.COURSE_TYPE, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'COURSE_TYPE', cvh1.hist_end_dt), 1, 10),cv1.COURSE_TYPE)), NVL( cvh1.ct_description, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'CT_DESCRIPTION', cvh1.hist_end_dt), 1, 60), ct1.description)), NVL( cvh1.responsible_org_unit_cd, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'RESPONSIBLE_ORG_UNIT_CD', cvh1.hist_end_dt), 1, 10), cv1.responsible_org_unit_cd)), NVL( cvh1.responsible_ou_start_dt, NVL( igs_ge_date.igsdATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'RESPONSIBLE_OU_START_DT', cvh1.hist_end_dt)), cv1.responsible_ou_start_dt)), NVL( cvh1.ou_description, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'OU_DESCRIPTION', cvh1.hist_end_dt), 1, 60), ou1.party_name)), NVL( cvh1.GOVT_SPECIAL_COURSE_TYPE, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'GOVT_SPECIAL_COURSE_TYPE', cvh1.hist_end_dt), 1, 2), cv1.GOVT_SPECIAL_COURSE_TYPE)), NVL( cvh1.gsct_description, NVL( SUBSTR( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'GSCT_DESCRIPTION', cvh1.hist_end_dt), 1, 60), gsct1.description)), NVL( cvh1.qualification_recency, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'QUALIFICATION_RECENCY', cvh1.hist_end_dt)), cv1.qualification_recency)), NVL( cvh1.external_adv_stnd_limit, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'EXTERNAL_ADV_STND_LIMIT', cvh1.hist_end_dt)), cv1.external_adv_stnd_limit)), NVL( cvh1.internal_adv_stnd_limit, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'INTERNAL_ADV_STND_LIMIT', cvh1.hist_end_dt)), cv1.external_adv_stnd_limit)), NVL( cvh1.contact_hours, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'CONTACT_HOURS', cvh1.hist_end_dt)), cv1.contact_hours)), NVL( cvh1.credit_points_required, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'CREDIT_POINTS_REQUIRED', cvh1.hist_end_dt)), cv1.credit_points_required)), NVL( cvh1.govt_course_load, NVL( IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'GOVT_COURSE_LOAD', cvh1.hist_end_dt)), cv1.govt_course_load)), NVL( cvh1.std_annual_load, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'STD_ANNUAL_LOAD', cvh1.hist_end_dt)), cv1.std_annual_load)), NVL( cvh1.annual_instruction_time, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'ANNUAL_INSTRUCTION_TIME', cvh1.hist_end_dt)), cv1.annual_instruction_time)), NVL( cvh1.course_total_eftsu, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'COURSE_TOTAL_EFTSU', cvh1.hist_end_dt)), cv1.course_total_eftsu)), NVL( CVH1.STD_FT_COMPLETION_TIME, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD, CVH1.VERSION_NUMBER, 'STD_FT_COMPLETION_TIME', CVH1.HIST_END_DT)), CV1.STD_FT_COMPLETION_TIME)), NVL( CVH1.STD_PT_COMPLETION_TIME, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD, CVH1.VERSION_NUMBER, 'STD_PT_COMPLETION_TIME', CVH1.HIST_END_DT)), CV1.STD_PT_COMPLETION_TIME)), NVL( cvh1.max_intrmsn_duration, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'MAX_INTRMSN_DURATION', cvh1.hist_end_dt)), cv1.max_intrmsn_duration)), NVL( cvh1.num_of_units_before_intrmsn, NVL(IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'NUM_OF_UNITS_BEFORE_INTRMSN', cvh1.hist_end_dt)), cv1.num_of_units_before_intrmsn)), NVL( cvh1.min_sbmsn_percentage, NVL(IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'MIN_SBMSN_PERCENTAGE', cvh1.hist_end_dt)), cv1.min_sbmsn_percentage)), NVL( cvh1.min_cp_per_calendar, NVL(IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'MIN_CP_PER_CALENDAR', cvh1.hist_end_dt)), cv1.min_cp_per_calendar)), NVL( cvh1.approval_date, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'APPROVAL_DATE', cvh1.hist_end_dt)), cv1.approval_date)), NVL( cvh1.external_approval_date, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'EXTERNAL_APPROVAL_DATE', cvh1.hist_end_dt)), cv1.external_approval_date)), NVL( cvh1.federal_financial_aid, NVL(SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'FEDERAL_FINANCIAL_AID', cvh1.hist_end_dt),1,1), cv1.federal_financial_aid)), NVL( cvh1.institutional_financial_aid, NVL(SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'INSTITUTIONAL_FINANCIAL_AID', cvh1.hist_end_dt),1,1), cv1.institutional_financial_aid)), NVL( cvh1.max_cp_per_teaching_period, NVL(IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'MAX_CP_PER_TEACHING_PERIOD', cvh1.hist_end_dt)), cv1.max_cp_per_teaching_period)), NVL( cvh1.residency_cp_required, NVL(IGS_GE_NUMBER.to_num(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'RESIDENCY_CP_REQUIRED', cvh1.hist_end_dt)), cv1.residency_cp_required)), NVL( cvh1.state_financial_aid, NVL(SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'STATE_FINAINCIAL_AID', cvh1.hist_end_dt),1,1), cv1.state_financial_aid)), NVL( cvh1.primary_program_rank, NVL(SUBSTR(IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'PRIMARY_PROGRAM_RANK', cvh1.hist_end_dt),1,1), cv1.primary_program_rank)), cvh1.created_by, cvh1.creation_date, cvh1.last_updated_by, cvh1.last_update_date, cvh1.last_update_login, NVL( cvh1.MAX_WLST_PER_STUD, NVL( IGS_GE_NUMBER.to_num( IGS_AU_GEN_002.audp_get_cvh_col( cvh1.course_cd, cvh1.version_number, 'MAX_WLST_PER_STUD', cvh1.hist_end_dt)), cv1.MAX_WLST_PER_STUD)) FROM IGS_PS_VER_HIST cvh1, IGS_PS_VER cv1, IGS_PS_TYPE ct1, IGS_PS_GOVT_SPL_TYPE gsct1, IGS_OR_INST_ORG_BASE_V ou1 WHERE cv1.course_cd = cvh1.course_cd AND cv1.version_number = cvh1.version_number AND cv1.COURSE_TYPE = ct1.COURSE_TYPE AND cv1.GOVT_SPECIAL_COURSE_TYPE = gsct1.GOVT_SPECIAL_COURSE_TYPE AND cv1.responsible_org_unit_cd = ou1.party_number AND cv1.responsible_ou_start_dt = ou1.start_dt UNION ALL SELECT cv2.course_cd, cv2.version_number, NVL( MAX(cvh2.hist_end_dt), cv2.last_update_date), TO_DATE(NULL), cv2.last_updated_by, cv2.start_dt, cv2.review_dt, cv2.expiry_dt, cv2.end_dt, cv2.COURSE_STATUS, cv2.title, cv2.short_title, cv2.abbreviation, cv2.supp_exam_permitted_ind, cv2.generic_course_ind, cv2.graduate_students_ind, cv2.count_intrmsn_in_time_ind, cv2.intrmsn_allowed_ind, cv2.COURSE_TYPE, ct2.description, cv2.responsible_org_unit_cd, cv2.responsible_ou_start_dt, ou2.party_name description, cv2.GOVT_SPECIAL_COURSE_TYPE, gsct2.description, cv2.qualification_recency, cv2.external_adv_stnd_limit, cv2.internal_adv_stnd_limit, cv2.contact_hours, cv2.credit_points_required, cv2.govt_course_load, cv2.std_annual_load, cv2.annual_instruction_time, cv2.course_total_eftsu, CV2.STD_FT_COMPLETION_TIME, CV2.STD_PT_COMPLETION_TIME, cv2.max_intrmsn_duration, cv2.num_of_units_before_intrmsn, cv2.min_sbmsn_percentage, cv2.min_cp_per_calendar, cv2.approval_date, cv2.external_approval_date, cv2.federal_financial_aid, cv2.institutional_financial_aid, cv2.max_cp_per_teaching_period, cv2.residency_cp_required, cv2.state_financial_aid, cv2.primary_program_rank, cv2.created_by, cv2.creation_date, cv2.last_updated_by, cv2.last_update_date, cv2.last_update_login, cv2.MAX_WLST_PER_STUD FROM IGS_PS_VER_HIST cvh2, IGS_PS_VER cv2, IGS_PS_TYPE ct2, IGS_PS_GOVT_SPL_TYPE gsct2, IGS_OR_INST_ORG_BASE_V ou2 WHERE cv2.course_cd = cvh2.course_cd (+) AND cv2.version_number = cvh2.version_number (+) AND cv2.COURSE_TYPE = ct2.COURSE_TYPE AND cv2.GOVT_SPECIAL_COURSE_TYPE = gsct2.GOVT_SPECIAL_COURSE_TYPE AND cv2.responsible_org_unit_cd = ou2.party_number AND cv2.responsible_ou_start_dt = ou2.start_dt GROUP BY cv2.course_cd, cv2.version_number, cv2.start_dt, cv2.review_dt, cv2.expiry_dt, cv2.end_dt, cv2.COURSE_STATUS, cv2.title, cv2.short_title, cv2.abbreviation, cv2.supp_exam_permitted_ind, cv2.generic_course_ind, cv2.graduate_students_ind, cv2.count_intrmsn_in_time_ind, cv2.intrmsn_allowed_ind, cv2.COURSE_TYPE, ct2.description, cv2.responsible_org_unit_cd, cv2.responsible_ou_start_dt, ou2.party_name , cv2.GOVT_SPECIAL_COURSE_TYPE, gsct2.description, cv2.qualification_recency, cv2.external_adv_stnd_limit, cv2.internal_adv_stnd_limit, cv2.contact_hours, cv2.credit_points_required, cv2.govt_course_load, cv2.std_annual_load, cv2.annual_instruction_time, cv2.course_total_eftsu, CV2.STD_FT_COMPLETION_TIME, CV2.STD_PT_COMPLETION_TIME, cv2.max_intrmsn_duration, cv2.num_of_units_before_intrmsn, cv2.min_sbmsn_percentage, cv2.min_cp_per_calendar, cv2.approval_date, cv2.external_approval_date, cv2.federal_financial_aid, cv2.institutional_financial_aid, cv2.max_cp_per_teaching_period, cv2.residency_cp_required, cv2.state_financial_aid, cv2.primary_program_rank, cv2.created_by, cv2.creation_date, cv2.last_updated_by, cv2.last_update_date, cv2.last_update_login, cv2.MAX_WLST_PER_STUD

View Text - HTML Formatted

SELECT CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, CVH1.HIST_START_DT
, CVH1.HIST_END_DT
, CVH1.HIST_WHO
, NVL( CVH1.START_DT
, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'START_DT'
, CVH1.HIST_END_DT))
, CV1.START_DT))
, NVL( CVH1.REVIEW_DT
, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'REVIEW_DT'
, CVH1.HIST_END_DT))
, CV1.REVIEW_DT))
, NVL( CVH1.EXPIRY_DT
, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'EXPIRY_DT'
, CVH1.HIST_END_DT))
, CV1.EXPIRY_DT))
, NVL( CVH1.END_DT
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'END_DT'
, CVH1.HIST_END_DT))
, CV1.END_DT))
, NVL( CVH1.COURSE_STATUS
, NVL( SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'COURSE_STATUS'
, CVH1.HIST_END_DT)
, 1
, 10)
, CV1.COURSE_STATUS))
, NVL( CVH1.TITLE
, NVL( SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'TITLE'
, CVH1.HIST_END_DT)
, 1
, 90)
, CV1.TITLE))
, NVL( CVH1.SHORT_TITLE
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'SHORT_TITLE'
, CVH1.HIST_END_DT)
, 1
, 40)
, CV1.SHORT_TITLE))
, NVL( CVH1.ABBREVIATION
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'ABBREVIATION'
, CVH1.HIST_END_DT)
, 1
, 20)
, CV1.ABBREVIATION))
, NVL( CVH1.SUPP_EXAM_PERMITTED_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'SUPP_EXAM_PERMITTED_IND'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.SUPP_EXAM_PERMITTED_IND))
, NVL( CVH1.GENERIC_COURSE_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'GENERIC_COURSE_IND'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.GENERIC_COURSE_IND))
, NVL( CVH1.GRADUATE_STUDENTS_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'GRADUATE_STUDENTS_IND'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.GRADUATE_STUDENTS_IND))
, NVL( CVH1.COUNT_INTRMSN_IN_TIME_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'COUNT_INTRMSN_IN_TIME_IND'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.COUNT_INTRMSN_IN_TIME_IND))
, NVL( CVH1.INTRMSN_ALLOWED_IND
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'INTRMSN_ALLOWED_IND'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.INTRMSN_ALLOWED_IND))
, NVL( CVH1.COURSE_TYPE
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'COURSE_TYPE'
, CVH1.HIST_END_DT)
, 1
, 10)
, CV1.COURSE_TYPE))
, NVL( CVH1.CT_DESCRIPTION
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'CT_DESCRIPTION'
, CVH1.HIST_END_DT)
, 1
, 60)
, CT1.DESCRIPTION))
, NVL( CVH1.RESPONSIBLE_ORG_UNIT_CD
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'RESPONSIBLE_ORG_UNIT_CD'
, CVH1.HIST_END_DT)
, 1
, 10)
, CV1.RESPONSIBLE_ORG_UNIT_CD))
, NVL( CVH1.RESPONSIBLE_OU_START_DT
, NVL( IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'RESPONSIBLE_OU_START_DT'
, CVH1.HIST_END_DT))
, CV1.RESPONSIBLE_OU_START_DT))
, NVL( CVH1.OU_DESCRIPTION
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'OU_DESCRIPTION'
, CVH1.HIST_END_DT)
, 1
, 60)
, OU1.PARTY_NAME))
, NVL( CVH1.GOVT_SPECIAL_COURSE_TYPE
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'GOVT_SPECIAL_COURSE_TYPE'
, CVH1.HIST_END_DT)
, 1
, 2)
, CV1.GOVT_SPECIAL_COURSE_TYPE))
, NVL( CVH1.GSCT_DESCRIPTION
, NVL( SUBSTR( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'GSCT_DESCRIPTION'
, CVH1.HIST_END_DT)
, 1
, 60)
, GSCT1.DESCRIPTION))
, NVL( CVH1.QUALIFICATION_RECENCY
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'QUALIFICATION_RECENCY'
, CVH1.HIST_END_DT))
, CV1.QUALIFICATION_RECENCY))
, NVL( CVH1.EXTERNAL_ADV_STND_LIMIT
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'EXTERNAL_ADV_STND_LIMIT'
, CVH1.HIST_END_DT))
, CV1.EXTERNAL_ADV_STND_LIMIT))
, NVL( CVH1.INTERNAL_ADV_STND_LIMIT
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'INTERNAL_ADV_STND_LIMIT'
, CVH1.HIST_END_DT))
, CV1.EXTERNAL_ADV_STND_LIMIT))
, NVL( CVH1.CONTACT_HOURS
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'CONTACT_HOURS'
, CVH1.HIST_END_DT))
, CV1.CONTACT_HOURS))
, NVL( CVH1.CREDIT_POINTS_REQUIRED
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'CREDIT_POINTS_REQUIRED'
, CVH1.HIST_END_DT))
, CV1.CREDIT_POINTS_REQUIRED))
, NVL( CVH1.GOVT_COURSE_LOAD
, NVL( IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'GOVT_COURSE_LOAD'
, CVH1.HIST_END_DT))
, CV1.GOVT_COURSE_LOAD))
, NVL( CVH1.STD_ANNUAL_LOAD
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'STD_ANNUAL_LOAD'
, CVH1.HIST_END_DT))
, CV1.STD_ANNUAL_LOAD))
, NVL( CVH1.ANNUAL_INSTRUCTION_TIME
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'ANNUAL_INSTRUCTION_TIME'
, CVH1.HIST_END_DT))
, CV1.ANNUAL_INSTRUCTION_TIME))
, NVL( CVH1.COURSE_TOTAL_EFTSU
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'COURSE_TOTAL_EFTSU'
, CVH1.HIST_END_DT))
, CV1.COURSE_TOTAL_EFTSU))
, NVL( CVH1.STD_FT_COMPLETION_TIME
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'STD_FT_COMPLETION_TIME'
, CVH1.HIST_END_DT))
, CV1.STD_FT_COMPLETION_TIME))
, NVL( CVH1.STD_PT_COMPLETION_TIME
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'STD_PT_COMPLETION_TIME'
, CVH1.HIST_END_DT))
, CV1.STD_PT_COMPLETION_TIME))
, NVL( CVH1.MAX_INTRMSN_DURATION
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'MAX_INTRMSN_DURATION'
, CVH1.HIST_END_DT))
, CV1.MAX_INTRMSN_DURATION))
, NVL( CVH1.NUM_OF_UNITS_BEFORE_INTRMSN
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'NUM_OF_UNITS_BEFORE_INTRMSN'
, CVH1.HIST_END_DT))
, CV1.NUM_OF_UNITS_BEFORE_INTRMSN))
, NVL( CVH1.MIN_SBMSN_PERCENTAGE
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'MIN_SBMSN_PERCENTAGE'
, CVH1.HIST_END_DT))
, CV1.MIN_SBMSN_PERCENTAGE))
, NVL( CVH1.MIN_CP_PER_CALENDAR
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'MIN_CP_PER_CALENDAR'
, CVH1.HIST_END_DT))
, CV1.MIN_CP_PER_CALENDAR))
, NVL( CVH1.APPROVAL_DATE
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'APPROVAL_DATE'
, CVH1.HIST_END_DT))
, CV1.APPROVAL_DATE))
, NVL( CVH1.EXTERNAL_APPROVAL_DATE
, NVL(IGS_GE_DATE.IGSDATE(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'EXTERNAL_APPROVAL_DATE'
, CVH1.HIST_END_DT))
, CV1.EXTERNAL_APPROVAL_DATE))
, NVL( CVH1.FEDERAL_FINANCIAL_AID
, NVL(SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'FEDERAL_FINANCIAL_AID'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.FEDERAL_FINANCIAL_AID))
, NVL( CVH1.INSTITUTIONAL_FINANCIAL_AID
, NVL(SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'INSTITUTIONAL_FINANCIAL_AID'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.INSTITUTIONAL_FINANCIAL_AID))
, NVL( CVH1.MAX_CP_PER_TEACHING_PERIOD
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'MAX_CP_PER_TEACHING_PERIOD'
, CVH1.HIST_END_DT))
, CV1.MAX_CP_PER_TEACHING_PERIOD))
, NVL( CVH1.RESIDENCY_CP_REQUIRED
, NVL(IGS_GE_NUMBER.TO_NUM(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'RESIDENCY_CP_REQUIRED'
, CVH1.HIST_END_DT))
, CV1.RESIDENCY_CP_REQUIRED))
, NVL( CVH1.STATE_FINANCIAL_AID
, NVL(SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'STATE_FINAINCIAL_AID'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.STATE_FINANCIAL_AID))
, NVL( CVH1.PRIMARY_PROGRAM_RANK
, NVL(SUBSTR(IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'PRIMARY_PROGRAM_RANK'
, CVH1.HIST_END_DT)
, 1
, 1)
, CV1.PRIMARY_PROGRAM_RANK))
, CVH1.CREATED_BY
, CVH1.CREATION_DATE
, CVH1.LAST_UPDATED_BY
, CVH1.LAST_UPDATE_DATE
, CVH1.LAST_UPDATE_LOGIN
, NVL( CVH1.MAX_WLST_PER_STUD
, NVL( IGS_GE_NUMBER.TO_NUM( IGS_AU_GEN_002.AUDP_GET_CVH_COL( CVH1.COURSE_CD
, CVH1.VERSION_NUMBER
, 'MAX_WLST_PER_STUD'
, CVH1.HIST_END_DT))
, CV1.MAX_WLST_PER_STUD))
FROM IGS_PS_VER_HIST CVH1
, IGS_PS_VER CV1
, IGS_PS_TYPE CT1
, IGS_PS_GOVT_SPL_TYPE GSCT1
, IGS_OR_INST_ORG_BASE_V OU1
WHERE CV1.COURSE_CD = CVH1.COURSE_CD
AND CV1.VERSION_NUMBER = CVH1.VERSION_NUMBER
AND CV1.COURSE_TYPE = CT1.COURSE_TYPE
AND CV1.GOVT_SPECIAL_COURSE_TYPE = GSCT1.GOVT_SPECIAL_COURSE_TYPE
AND CV1.RESPONSIBLE_ORG_UNIT_CD = OU1.PARTY_NUMBER
AND CV1.RESPONSIBLE_OU_START_DT = OU1.START_DT UNION ALL SELECT CV2.COURSE_CD
, CV2.VERSION_NUMBER
, NVL( MAX(CVH2.HIST_END_DT)
, CV2.LAST_UPDATE_DATE)
, TO_DATE(NULL)
, CV2.LAST_UPDATED_BY
, CV2.START_DT
, CV2.REVIEW_DT
, CV2.EXPIRY_DT
, CV2.END_DT
, CV2.COURSE_STATUS
, CV2.TITLE
, CV2.SHORT_TITLE
, CV2.ABBREVIATION
, CV2.SUPP_EXAM_PERMITTED_IND
, CV2.GENERIC_COURSE_IND
, CV2.GRADUATE_STUDENTS_IND
, CV2.COUNT_INTRMSN_IN_TIME_IND
, CV2.INTRMSN_ALLOWED_IND
, CV2.COURSE_TYPE
, CT2.DESCRIPTION
, CV2.RESPONSIBLE_ORG_UNIT_CD
, CV2.RESPONSIBLE_OU_START_DT
, OU2.PARTY_NAME DESCRIPTION
, CV2.GOVT_SPECIAL_COURSE_TYPE
, GSCT2.DESCRIPTION
, CV2.QUALIFICATION_RECENCY
, CV2.EXTERNAL_ADV_STND_LIMIT
, CV2.INTERNAL_ADV_STND_LIMIT
, CV2.CONTACT_HOURS
, CV2.CREDIT_POINTS_REQUIRED
, CV2.GOVT_COURSE_LOAD
, CV2.STD_ANNUAL_LOAD
, CV2.ANNUAL_INSTRUCTION_TIME
, CV2.COURSE_TOTAL_EFTSU
, CV2.STD_FT_COMPLETION_TIME
, CV2.STD_PT_COMPLETION_TIME
, CV2.MAX_INTRMSN_DURATION
, CV2.NUM_OF_UNITS_BEFORE_INTRMSN
, CV2.MIN_SBMSN_PERCENTAGE
, CV2.MIN_CP_PER_CALENDAR
, CV2.APPROVAL_DATE
, CV2.EXTERNAL_APPROVAL_DATE
, CV2.FEDERAL_FINANCIAL_AID
, CV2.INSTITUTIONAL_FINANCIAL_AID
, CV2.MAX_CP_PER_TEACHING_PERIOD
, CV2.RESIDENCY_CP_REQUIRED
, CV2.STATE_FINANCIAL_AID
, CV2.PRIMARY_PROGRAM_RANK
, CV2.CREATED_BY
, CV2.CREATION_DATE
, CV2.LAST_UPDATED_BY
, CV2.LAST_UPDATE_DATE
, CV2.LAST_UPDATE_LOGIN
, CV2.MAX_WLST_PER_STUD
FROM IGS_PS_VER_HIST CVH2
, IGS_PS_VER CV2
, IGS_PS_TYPE CT2
, IGS_PS_GOVT_SPL_TYPE GSCT2
, IGS_OR_INST_ORG_BASE_V OU2
WHERE CV2.COURSE_CD = CVH2.COURSE_CD (+)
AND CV2.VERSION_NUMBER = CVH2.VERSION_NUMBER (+)
AND CV2.COURSE_TYPE = CT2.COURSE_TYPE
AND CV2.GOVT_SPECIAL_COURSE_TYPE = GSCT2.GOVT_SPECIAL_COURSE_TYPE
AND CV2.RESPONSIBLE_ORG_UNIT_CD = OU2.PARTY_NUMBER
AND CV2.RESPONSIBLE_OU_START_DT = OU2.START_DT GROUP BY CV2.COURSE_CD
, CV2.VERSION_NUMBER
, CV2.START_DT
, CV2.REVIEW_DT
, CV2.EXPIRY_DT
, CV2.END_DT
, CV2.COURSE_STATUS
, CV2.TITLE
, CV2.SHORT_TITLE
, CV2.ABBREVIATION
, CV2.SUPP_EXAM_PERMITTED_IND
, CV2.GENERIC_COURSE_IND
, CV2.GRADUATE_STUDENTS_IND
, CV2.COUNT_INTRMSN_IN_TIME_IND
, CV2.INTRMSN_ALLOWED_IND
, CV2.COURSE_TYPE
, CT2.DESCRIPTION
, CV2.RESPONSIBLE_ORG_UNIT_CD
, CV2.RESPONSIBLE_OU_START_DT
, OU2.PARTY_NAME
, CV2.GOVT_SPECIAL_COURSE_TYPE
, GSCT2.DESCRIPTION
, CV2.QUALIFICATION_RECENCY
, CV2.EXTERNAL_ADV_STND_LIMIT
, CV2.INTERNAL_ADV_STND_LIMIT
, CV2.CONTACT_HOURS
, CV2.CREDIT_POINTS_REQUIRED
, CV2.GOVT_COURSE_LOAD
, CV2.STD_ANNUAL_LOAD
, CV2.ANNUAL_INSTRUCTION_TIME
, CV2.COURSE_TOTAL_EFTSU
, CV2.STD_FT_COMPLETION_TIME
, CV2.STD_PT_COMPLETION_TIME
, CV2.MAX_INTRMSN_DURATION
, CV2.NUM_OF_UNITS_BEFORE_INTRMSN
, CV2.MIN_SBMSN_PERCENTAGE
, CV2.MIN_CP_PER_CALENDAR
, CV2.APPROVAL_DATE
, CV2.EXTERNAL_APPROVAL_DATE
, CV2.FEDERAL_FINANCIAL_AID
, CV2.INSTITUTIONAL_FINANCIAL_AID
, CV2.MAX_CP_PER_TEACHING_PERIOD
, CV2.RESIDENCY_CP_REQUIRED
, CV2.STATE_FINANCIAL_AID
, CV2.PRIMARY_PROGRAM_RANK
, CV2.CREATED_BY
, CV2.CREATION_DATE
, CV2.LAST_UPDATED_BY
, CV2.LAST_UPDATE_DATE
, CV2.LAST_UPDATE_LOGIN
, CV2.MAX_WLST_PER_STUD