[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode (p_column_name,
'UNIT_LEVEL', culh.unit_level,
'WAM_WEIGHTING', TO_CHAR(culh.wam_weighting))
FROM igs_ps_unit_lvl_hist culh
WHERE culh.unit_cd = p_unit_cd AND
culh.version_number = p_version_number AND
culh.course_cd = p_course_cd AND
culh.course_version_number = p_course_version_number AND
culh.hist_start_dt >= p_hist_date AND
decode (p_column_name,
'UNIT_LEVEL', culh.unit_level,
'WAM_WEIGHTING', TO_CHAR(culh.wam_weighting)) IS NOT NULL
ORDER BY culh.hist_start_dt;
sarakshi 23-Jan-2004 Enh#3345205, added column annual_instruction_time in the select statement
vvutukur 19_oct-2002 Enh#2608227.removed references to std_ft_completion_time,std_pt_completion_time
ayedubat 25-MAY-2001 modified the procedure to add new columns
(reverse chronological order - newest change first)
***************************************************************/
p_course_cd igs_ps_ver_hist_all.course_cd%TYPE ,
p_version_number igs_ps_ver_hist_all.version_number%TYPE ,
p_column_name user_tab_columns.column_name%TYPE ,
p_hist_date igs_ps_ver_hist_all.hist_start_dt%TYPE )
RETURN VARCHAR2 AS
BEGIN -- audp_get_cvh_col
-- Get the oldest column value (after a given date) for a given
-- column name, course_cd and version_number.
DECLARE
cst_start_dt VARCHAR2(30) := 'START_DT';
SELECT decode (p_column_name,
cst_start_dt, igs_ge_date.igschardt(cvh.start_dt),
cst_review_dt, igs_ge_date.igschardt(cvh.review_dt),
cst_expiry_dt, igs_ge_date.igschardt(cvh.expiry_dt),
cst_end_dt, igs_ge_date.igschardt(cvh.end_dt),
cst_course_status, cvh.course_status,
cst_title, cvh.title,
cst_short_title, cvh.short_title,
cst_abbreviation, cvh.abbreviation,
cst_supp_exam_permitted_ind, cvh.supp_exam_permitted_ind,
cst_generic_course_ind, cvh.generic_course_ind,
cst_graduate_students_ind, cvh.graduate_students_ind,
cst_count_intrmsn_in_time_ind, cvh.count_intrmsn_in_time_ind,
cst_intrmsn_allowed_ind, cvh.intrmsn_allowed_ind,
cst_course_type, cvh.course_type,
cst_ct_description, cvh.ct_description,
cst_responsible_org_unit_cd, cvh.responsible_org_unit_cd,
cst_responsible_ou_start_dt, igs_ge_date.igschardt(cvh.responsible_ou_start_dt),
cst_ou_description, cvh.ou_description,
cst_govt_special_course_type, cvh.govt_special_course_type,
cst_gsct_description, cvh.gsct_description,
cst_qualification_recency, TO_CHAR(cvh.qualification_recency),
cst_external_adv_stnd_limit, TO_CHAR(cvh.external_adv_stnd_limit),
cst_internal_adv_stnd_limit, TO_CHAR(cvh.internal_adv_stnd_limit),
cst_contact_hours, TO_CHAR(cvh.contact_hours),
cst_credit_points_required, TO_CHAR(cvh.credit_points_required),
cst_govt_course_load, TO_CHAR(cvh.govt_course_load),
cst_std_annual_load, TO_CHAR(cvh.std_annual_load),
cst_course_total_eftsu, TO_CHAR(cvh.course_total_eftsu),
cst_max_intrmsn_duration, TO_CHAR(cvh.max_intrmsn_duration),
cst_num_of_unts_bfr_intrmsn, TO_CHAR(cvh.num_of_units_before_intrmsn),
cst_min_sbmsn_percentage, TO_CHAR(cvh.min_sbmsn_percentage),
cst_min_cp_per_calendar, TO_CHAR(cvh.min_cp_per_calendar),
cst_approval_date, igs_ge_date.igschardt(cvh.approval_date),
cst_external_approval_date, igs_ge_date.igschardt(cvh.external_approval_date),
cst_federal_financial_aid, cvh.federal_financial_aid,
c_institutional_financial_aid, cvh.institutional_financial_aid,
cst_max_cp_per_teaching_period, TO_CHAR(cvh.max_cp_per_teaching_period),
cst_residency_cp_required, TO_CHAR(residency_cp_required),
cst_state_financial_aid, state_financial_aid,
cst_primary_program_rank, TO_CHAR(primary_program_rank),
l_c_max_wlst_per_stud, TO_CHAR(max_wlst_per_stud),
l_c_annual_instruction_time, TO_CHAR(annual_instruction_time))
FROM igs_ps_ver_hist cvh
WHERE cvh.course_cd = p_course_cd AND
cvh.version_number = p_version_number AND
cvh.hist_start_dt >= p_hist_date AND
decode (p_column_name,
cst_start_dt, igs_ge_date.igschardt(cvh.start_dt),
cst_review_dt, igs_ge_date.igschardt(cvh.review_dt),
cst_expiry_dt, igs_ge_date.igschardt(cvh.expiry_dt),
cst_end_dt, igs_ge_date.igschardt(cvh.end_dt),
cst_course_status, cvh.course_status,
cst_title, cvh.title,
cst_short_title, cvh.short_title,
cst_abbreviation, cvh.abbreviation,
cst_supp_exam_permitted_ind, cvh.supp_exam_permitted_ind,
cst_generic_course_ind, cvh.generic_course_ind,
cst_graduate_students_ind, cvh.graduate_students_ind,
cst_count_intrmsn_in_time_ind, cvh.count_intrmsn_in_time_ind,
cst_intrmsn_allowed_ind, cvh.intrmsn_allowed_ind,
cst_course_type, cvh.course_type,
cst_ct_description, cvh.ct_description,
cst_responsible_org_unit_cd, cvh.responsible_org_unit_cd,
cst_responsible_ou_start_dt, igs_ge_date.igschardt(cvh.responsible_ou_start_dt),
cst_ou_description, cvh.ou_description,
cst_govt_special_course_type, cvh.govt_special_course_type,
cst_gsct_description, cvh.gsct_description,
cst_qualification_recency, TO_CHAR(cvh.qualification_recency),
cst_external_adv_stnd_limit, TO_CHAR(cvh.external_adv_stnd_limit),
cst_internal_adv_stnd_limit, TO_CHAR(cvh.internal_adv_stnd_limit),
cst_contact_hours, TO_CHAR(cvh.contact_hours),
cst_credit_points_required, TO_CHAR(cvh.credit_points_required),
cst_govt_course_load, TO_CHAR(cvh.govt_course_load),
cst_std_annual_load, TO_CHAR(cvh.std_annual_load),
cst_course_total_eftsu, TO_CHAR(cvh.course_total_eftsu),
cst_max_intrmsn_duration, TO_CHAR(cvh.max_intrmsn_duration),
cst_num_of_unts_bfr_intrmsn, TO_CHAR(cvh.num_of_units_before_intrmsn),
cst_min_sbmsn_percentage, TO_CHAR(cvh.min_sbmsn_percentage),
cst_min_cp_per_calendar, TO_CHAR(cvh.min_cp_per_calendar),
cst_approval_date, igs_ge_date.igschardt(cvh.approval_date),
cst_external_approval_date, igs_ge_date.igschardt(cvh.external_approval_date),
cst_federal_financial_aid, cvh.federal_financial_aid,
c_institutional_financial_aid, cvh.institutional_financial_aid,
cst_max_cp_per_teaching_period, TO_CHAR(cvh.max_cp_per_teaching_period),
cst_residency_cp_required, TO_CHAR(residency_cp_required),
cst_state_financial_aid, state_financial_aid,
cst_primary_program_rank, TO_CHAR(primary_program_rank),
l_c_max_wlst_per_stud, TO_CHAR(max_wlst_per_stud),
l_c_annual_instruction_time, TO_CHAR(annual_instruction_time)) IS NOT NULL
ORDER BY cvh.hist_start_dt;
SELECT decode (p_column_name,
cst_description, dh.description,
cst_funding_index_1, TO_CHAR(dh.funding_index_1),
cst_funding_index_2, TO_CHAR(dh.funding_index_2),
cst_funding_index_3, TO_CHAR(dh.funding_index_3),
cst_gvt_dscplne_grp_cd, dh.govt_discipline_group_cd,
cst_closed_ind, dh.closed_ind)
FROM igs_ps_dscp_hist dh
WHERE dh.discipline_group_cd = p_dscplne_grp_cd AND
dh.hist_start_dt >= p_hist_end_dt AND
decode (p_column_name,
cst_description, dh.description,
cst_funding_index_1, TO_CHAR(dh.funding_index_1),
cst_funding_index_2, TO_CHAR(dh.funding_index_2),
cst_funding_index_3, TO_CHAR(dh.funding_index_3),
cst_gvt_dscplne_grp_cd, dh.govt_discipline_group_cd,
cst_closed_ind, dh.closed_ind) IS NOT NULL
ORDER BY
dh.hist_start_dt;
SELECT decode (p_column_name,
cst_fee_liability_status, fcflh.fee_liability_status,
cst_start_dt_alias, fcflh.start_dt_alias,
cst_start_dai_sequence_number, TO_CHAR(fcflh.start_dai_sequence_number),
cst_s_chg_method_type, fcflh.s_chg_method_type,
cst_rul_sequence_number, TO_CHAR(fcflh.rul_sequence_number))
FROM igs_fi_f_cat_f_lbl_h fcflh
WHERE fcflh.fee_cat = p_fee_cat AND
fcflh.fee_cal_type = p_fee_cal_type AND
fcflh.fee_ci_sequence_number = p_fee_ci_sequence_number AND
fcflh.fee_type = p_fee_type AND
fcflh.hist_start_dt >= p_hist_end_dt AND
decode (p_column_name,
cst_start_dt_alias, fcflh.start_dt_alias,
cst_start_dai_sequence_number, TO_CHAR(fcflh.start_dai_sequence_number),
cst_s_chg_method_type, fcflh.s_chg_method_type,
cst_rul_sequence_number, TO_CHAR(fcflh.rul_sequence_number)
) IS NOT NULL
ORDER BY
fcflh.hist_start_dt;
SELECT
decode (p_column_name,
'DESCRIPTION', fosh.description,
'GOVT_FIELD_OF_STUDY', fosh.govt_field_of_study,
'CLOSED_IND', fosh.closed_ind)
FROM igs_ps_fld_stdy_hist fosh
WHERE fosh.field_of_study = p_field_of_study AND
fosh.hist_start_dt >= p_hist_end_dt AND
decode (p_column_name,
'DESCRIPTION', fosh.description,
'GOVT_FIELD_OF_STUDY', fosh.govt_field_of_study,
'CLOSED_IND', fosh.closed_ind) IS NOT NULL
ORDER BY
fosh.hist_start_dt;
SELECT
decode (p_column_name,
'DESCRIPTION', fsh.description,
'GOVT_FUNDING_SOURCE', TO_CHAR(fsh.govt_funding_source),
'CLOSED_IND', fsh.closed_ind)
FROM igs_fi_fund_src_hist fsh
WHERE fsh.funding_source = p_funding_source AND
fsh.hist_start_dt >= p_hist_end_dt AND
decode (p_column_name,
'DESCRIPTION', fsh.description,
'GOVT_FUNDING_SOURCE', TO_CHAR(fsh.govt_funding_source),
'CLOSED_IND', fsh.closed_ind)IS NOT NULL
ORDER BY
fsh.hist_start_dt;
SELECT fsrh.dflt_ind
FROM igs_fi_fd_src_rstn_h fsrh
WHERE fsrh.course_cd = p_course_cd AND
fsrh.version_number = p_version_number AND
fsrh.funding_source = p_funding_source AND
fsrh.hist_start_dt >= p_hist_date AND
fsrh.dflt_ind IS NOT NULL
ORDER BY fsrh.hist_start_dt;
SELECT DECODE (p_column_name,
cst_fee_type_ci_status, ftcih.fee_type_ci_status,
cst_start_dt_alias, ftcih.start_dt_alias,
cst_start_dai_sequence_number, TO_CHAR(ftcih.start_dai_sequence_number),
cst_end_dt_alias, ftcih.end_dt_alias,
cst_end_dai_sequence_number, TO_CHAR(ftcih.end_dai_sequence_number),
cst_retro_dt_alias, ftcih.retro_dt_alias,
cst_retro_dai_sequence_number, TO_CHAR(ftcih.retro_dai_sequence_number),
cst_s_chg_method_type, ftcih.s_chg_method_type,
cst_rul_sequence_number, TO_CHAR(ftcih.rul_sequence_number))
FROM igs_fi_fee_type_ci_h ftcih
WHERE ftcih.fee_type = p_fee_type AND
ftcih.fee_cal_type = p_fee_cal_type AND
ftcih.fee_ci_sequence_number = p_fee_ci_sequence_number AND
ftcih.hist_start_dt >= p_hist_end_dt AND
decode (p_column_name,
cst_start_dt_alias, ftcih.start_dt_alias,
cst_start_dai_sequence_number, ftcih.start_dai_sequence_number,
cst_end_dt_alias, ftcih.end_dt_alias,
cst_end_dai_sequence_number, ftcih.end_dai_sequence_number,
cst_retro_dt_alias, ftcih.retro_dt_alias,
cst_retro_dai_sequence_number, ftcih.retro_dai_sequence_number,
cst_s_chg_method_type, ftcih.s_chg_method_type,
cst_rul_sequence_number, ftcih.rul_sequence_number) IS NOT NULL
ORDER BY ftcih.hist_start_dt;