The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM igs_he_st_prog_all
WHERE course_cd = p_old_course_cd
AND version_number = p_old_version_number;
SELECT hesa_st_prog_id
FROM igs_he_st_prog_all
WHERE course_cd = p_new_course_cd
AND version_number = p_new_version_number;
SELECT *
FROM igs_he_poous_all
WHERE course_cd = p_old_course_cd
AND crv_version_number = p_old_version_number;
SELECT hesa_poous_id
FROM igs_he_poous_all
WHERE course_cd = p_new_course_cd
AND crv_version_number = p_new_version_number
AND cal_type = p_cal_type
AND location_cd = p_location_cd
AND attendance_mode = p_attendance_mode
AND attendance_type = p_attendance_type
AND unit_set_cd = p_unit_set_cd
AND us_version_number = p_us_version_number;
IS SELECT *
FROM igs_he_poous_ou_all
WHERE course_cd = p_old_course_cd
AND crv_version_number = p_old_version_number ;
IS SELECT hesa_poous_ou_id
FROM igs_he_poous_ou_all
WHERE course_cd = p_new_course_cd
AND crv_version_number = p_new_version_number
AND cal_type = p_cal_type
AND location_cd = p_location_cd
AND attendance_mode = p_attendance_mode
AND attendance_type = p_attendance_type
AND unit_set_cd = p_unit_set_cd
AND us_version_number = p_us_version_number
AND organization_unit = p_organization_unit;
SELECT igs_he_st_prog_all_s.NEXTVAL
FROM dual;
igs_he_st_prog_all_pkg.insert_row(
X_ROWID => x_rowid,
X_HESA_ST_PROG_ID => v_hesa_seq_num,
X_ORG_ID => l_org_id,
X_COURSE_CD => p_new_course_cd,
X_VERSION_NUMBER => p_new_version_number,
X_TEACHER_TRAIN_PROG_ID => gv_hsp_old_rec.teacher_train_prog_id,
X_ITT_PHASE => gv_hsp_old_rec.itt_phase,
X_BILINGUAL_ITT_MARKER => gv_hsp_old_rec.bilingual_itt_marker,
X_TEACHING_QUAL_SOUGHT_SECTOR => gv_hsp_old_rec.teaching_qual_sought_sector,
X_TEACHING_QUAL_SOUGHT_SUBJ1 => gv_hsp_old_rec.teaching_qual_sought_subj1,
X_TEACHING_QUAL_SOUGHT_SUBJ2 => gv_hsp_old_rec.teaching_qual_sought_subj2,
X_TEACHING_QUAL_SOUGHT_SUBJ3 => gv_hsp_old_rec.teaching_qual_sought_subj3,
X_LOCATION_OF_STUDY => gv_hsp_old_rec.location_of_study,
X_OTHER_INST_PROV_TEACHING1 => gv_hsp_old_rec.other_inst_prov_teaching1,
X_OTHER_INST_PROV_TEACHING2 => gv_hsp_old_rec.other_inst_prov_teaching2,
X_PROP_TEACHING_IN_WELSH => gv_hsp_old_rec.prop_teaching_in_welsh,
X_PROP_NOT_TAUGHT => gv_hsp_old_rec.prop_not_taught,
X_CREDIT_TRANSFER_SCHEME => gv_hsp_old_rec.credit_transfer_scheme,
X_RETURN_TYPE => gv_hsp_old_rec.return_type,
X_DEFAULT_AWARD => gv_hsp_old_rec.default_award,
X_PROGRAM_CALC => gv_hsp_old_rec.program_calc,
X_LEVEL_APPLICABLE_TO_FUNDING => gv_hsp_old_rec.level_applicable_to_funding,
X_FRANCHISING_ACTIVITY => gv_hsp_old_rec.franchising_activity,
X_NHS_FUNDING_SOURCE => gv_hsp_old_rec.nhs_funding_source,
X_FE_PROGRAM_MARKER => gv_hsp_old_rec.fe_program_marker,
X_FEE_BAND => gv_hsp_old_rec.fee_band,
X_FUNDABILITY => gv_hsp_old_rec.fundability,
X_FTE_INTENSITY => gv_hsp_old_rec.fte_intensity,
X_TEACH_PERIOD_START_DT => gv_hsp_old_rec.teach_period_start_dt,
X_TEACH_PERIOD_END_DT => gv_hsp_old_rec.teach_period_end_dt,
X_MODE => 'R',
X_IMPLIED_FUND_RATE => gv_hsp_old_rec.implied_fund_rate,
X_GOV_INITIATIVES_CD => gv_hsp_old_rec.gov_initiatives_cd,
X_UNITS_FOR_QUAL => gv_hsp_old_rec.units_for_qual,
X_DISADV_UPLIFT_ELIG_CD => gv_hsp_old_rec.disadv_uplift_elig_cd,
X_FRANCH_PARTNER_CD => gv_hsp_old_rec.franch_partner_cd,
X_FRANCH_OUT_ARR_CD => gv_hsp_old_rec.franch_out_arr_cd,
X_EXCLUDE_FLAG => gv_hsp_old_rec.exclude_flag );
SELECT spc.*
FROM IGS_HE_PROG_OU_CC spc
WHERE spc.course_cd = cp_course_cd
AND spc.version_number = cp_version_number;
SELECT 'X'
FROM IGS_HE_PROG_OU_CC spc
WHERE spc.course_cd = cp_course_cd
AND spc.version_number = cp_version_number
AND spc.org_unit_cd = cp_org_unit_cd
AND spc.cost_centre = cp_cost_centre
AND spc.subject = cp_subject;
igs_he_prog_ou_cc_pkg.insert_row (
x_rowid => l_rowid,
x_hesa_prog_cc_id => l_hesa_prog_cc_id,
x_course_cd => p_new_course_cd,
x_version_number => p_new_version_number,
x_org_unit_cd => old_prg_cc_dtls_rec.org_unit_cd,
x_cost_centre => old_prg_cc_dtls_rec.cost_centre,
x_subject => old_prg_cc_dtls_rec.subject,
x_proportion => old_prg_cc_dtls_rec.proportion,
x_mode => 'R' );
SELECT igs_he_poous_all_s.NEXTVAL
FROM dual;
igs_he_poous_all_pkg.insert_row(
X_ROWID => x_rowid,
X_ORG_ID => l_org_id,
X_HESA_POOUS_ID => v_poous_seq_num,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER => p_new_version_number,
X_CAL_TYPE => gv_hpus_old_rec.cal_type,
X_LOCATION_CD => gv_hpus_old_rec.location_cd,
X_ATTENDANCE_MODE => gv_hpus_old_rec.attendance_mode,
X_ATTENDANCE_TYPE => gv_hpus_old_rec.attendance_type,
X_UNIT_SET_CD => gv_hpus_old_rec.unit_set_cd,
X_US_VERSION_NUMBER => gv_hpus_old_rec.us_version_number,
X_LOCATION_OF_STUDY => gv_hpus_old_rec.location_of_study,
X_MODE_OF_STUDY => gv_hpus_old_rec.mode_of_study,
X_UFI_PLACE => gv_hpus_old_rec.ufi_place,
X_FRANCHISING_ACTIVITY => gv_hpus_old_rec.franchising_activity,
X_TYPE_OF_YEAR => gv_hpus_old_rec.type_of_year,
X_LENG_CURRENT_YEAR => gv_hpus_old_rec.leng_current_year,
X_GRADING_SCHEMA_CD => gv_hpus_old_rec.grading_schema_cd,
X_GS_VERSION_NUMBER => gv_hpus_old_rec.gs_version_number,
X_CREDIT_VALUE_YOP1 => gv_hpus_old_rec.credit_value_yop1,
X_LEVEL_CREDIT1 => gv_hpus_old_rec.level_credit1,
X_CREDIT_VALUE_YOP2 => gv_hpus_old_rec.credit_value_yop2,
X_LEVEL_CREDIT2 => gv_hpus_old_rec.level_credit2,
X_CREDIT_VALUE_YOP3 => gv_hpus_old_rec.credit_value_yop3,
X_LEVEL_CREDIT3 => gv_hpus_old_rec.level_credit3,
X_CREDIT_VALUE_YOP4 => gv_hpus_old_rec.credit_value_yop4,
X_LEVEL_CREDIT4 => gv_hpus_old_rec.level_credit4,
X_FTE_INTENSITY => gv_hpus_old_rec.fte_intensity,
X_FTE_CALC_TYPE => gv_hpus_old_rec.fte_calc_type,
X_TEACH_PERIOD_START_DT => gv_hpus_old_rec.teach_period_start_dt,
X_TEACH_PERIOD_END_DT => gv_hpus_old_rec.teach_period_end_dt,
X_OTHER_INSTIT_TEACH1 => gv_hpus_old_rec.other_instit_teach1,
X_OTHER_INSTIT_TEACH2 => gv_hpus_old_rec.other_instit_teach2,
X_PROP_NOT_TAUGHT => gv_hpus_old_rec.prop_not_taught,
X_FUNDABILITY_CD => gv_hpus_old_rec.fundability_cd,
X_FEE_BAND => gv_hpus_old_rec.fee_band,
X_LEVEL_APPLICABLE_TO_FUNDING => gv_hpus_old_rec.level_applicable_to_funding,
X_MODE => 'R',
X_FUNDING_SOURCE => gv_hpus_old_rec.funding_source);
SELECT
poo.cal_type,
poo.location_cd,
poo.attendance_mode,
poo.attendance_type,
poo.unit_set_cd,
poo.us_version_number,
poo.organization_unit,
pcc.cost_centre,
pcc.subject,
pcc.proportion
FROM IGS_HE_POOUS_OU_CC pcc,
IGS_HE_POOUS_OU_ALL poo
WHERE
pcc.hesa_poous_ou_id = poo.hesa_poous_ou_id
AND poo.course_cd = p_old_course_cd
AND poo.crv_version_number = p_old_version_number;
SELECT hesa_poous_ou_id
FROM IGS_HE_POOUS_OU_ALL
WHERE
course_cd = p_new_course_cd
AND crv_version_number = p_new_version_number
AND cal_type = cp_cal_type
AND location_cd = cp_location_cd
AND attendance_mode = cp_attendance_mode
AND attendance_type = cp_attendance_type
AND unit_set_cd = cp_unit_set_cd
AND us_version_number = cp_us_version_number
AND organization_unit = cp_organization_unit;
SELECT 'X'
FROM IGS_HE_POOUS_OU_CC
WHERE
hesa_poous_ou_id = cp_poous_ou_id
AND cost_centre = cp_cost_centre
AND subject = cp_subject;
igs_he_poous_ou_cc_pkg.insert_row(
X_ROWID => x_rowid,
X_HESA_POOUS_CC_ID => x_hesa_poous_cc_id,
X_HESA_POOUS_OU_ID => l_new_poous_ou_id,
X_COST_CENTRE => l_old_poous_cc_dtls_rec.cost_centre,
X_SUBJECT => l_old_poous_cc_dtls_rec.subject,
X_PROPORTION => l_old_poous_cc_dtls_rec.proportion,
X_MODE => 'R');
SELECT igs_he_poous_ou_all_s.NEXTVAL
FROM dual;
igs_he_poous_ou_all_pkg.insert_row(
X_ROWID => x_rowid,
X_HESA_POOUS_OU_ID => v_pooou_seq_num,
X_ORG_ID => l_org_id,
X_COURSE_CD => p_new_course_cd,
X_CRV_VERSION_NUMBER=> p_new_version_number,
X_CAL_TYPE => gv_hpo_old_rec.cal_type,
X_LOCATION_CD => gv_hpo_old_rec.location_cd,
X_ATTENDANCE_MODE => gv_hpo_old_rec.attendance_mode,
X_ATTENDANCE_TYPE => gv_hpo_old_rec.attendance_type,
X_UNIT_SET_CD => gv_hpo_old_rec.unit_set_cd,
X_US_VERSION_NUMBER => gv_hpo_old_rec.us_version_number,
X_ORGANIZATION_UNIT => gv_hpo_old_rec.organization_unit,
X_PROPORTION => gv_hpo_old_rec.proportion,
X_MODE => 'R');
-- must be called AFTER POOUS_OU records have been updated!
create_poous_cc_rec(p_old_course_cd, p_old_version_number, p_new_course_cd, p_new_version_number );