The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER,
x_dependants_cd IN VARCHAR2,
x_implied_fund_rate IN NUMBER,
x_gov_initiatives_cd IN VARCHAR2,
x_units_for_qual IN NUMBER,
x_disadv_uplift_elig_cd IN VARCHAR2,
x_franch_partner_cd IN VARCHAR2,
x_units_completed IN NUMBER,
x_franch_out_arr_cd IN VARCHAR2,
x_employer_role_cd IN VARCHAR2,
x_disadv_uplift_factor IN NUMBER,
x_enh_fund_elig_cd IN VARCHAR2,
x_exclude_flag IN VARCHAR2
) AS
/*
|| Created By : [email protected]
|| Created On : 21-FEB-2002
|| Purpose : Initialises the Old and New references for the columns of the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_HE_ST_SPA_ALL
WHERE rowid = x_rowid;
IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
CLOSE cur_old_ref_values;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
IF (p_action = 'UPDATE' AND x_exclude_flag IS NULL) THEN
new_references.exclude_flag := old_references.exclude_flag;
IF (p_action = 'INSERT') THEN
new_references.associate_ucas_number := NVL(x_associate_ucas_number,'Y');
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
SELECT rowid
FROM igs_he_st_spa_all
WHERE hesa_st_spa_id = x_hesa_st_spa_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_he_st_spa_all
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_he_st_spa_all
WHERE ((course_cd = x_course_cd) AND
(person_id = x_person_id));
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER ,
x_dependants_cd IN VARCHAR2,
x_implied_fund_rate IN NUMBER ,
x_gov_initiatives_cd IN VARCHAR2,
x_units_for_qual IN NUMBER ,
x_disadv_uplift_elig_cd IN VARCHAR2,
x_franch_partner_cd IN VARCHAR2,
x_units_completed IN NUMBER ,
x_franch_out_arr_cd IN VARCHAR2,
x_employer_role_cd IN VARCHAR2,
x_disadv_uplift_factor IN NUMBER ,
x_enh_fund_elig_cd IN VARCHAR2,
x_exclude_flag IN VARCHAR2
) AS
/*
|| Created By : [email protected]
|| Created On : 21-FEB-2002
|| Purpose : Initialises the columns, Checks Constraints, Calls the
|| Trigger Handlers for the table, before any DML operation.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
set_column_values (
p_action,
x_rowid,
x_hesa_st_spa_id,
x_org_id,
x_person_id,
x_course_cd,
x_version_number,
x_fe_student_marker,
x_domicile_cd,
x_inst_last_attended,
x_year_left_last_inst,
x_highest_qual_on_entry,
x_date_qual_on_entry_calc,
x_a_level_point_score,
x_highers_points_scores,
x_occupation_code,
x_commencement_dt,
x_special_student,
x_student_qual_aim,
x_student_fe_qual_aim,
x_teacher_train_prog_id,
x_itt_phase,
x_bilingual_itt_marker,
x_teaching_qual_gain_sector,
x_teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3,
x_student_inst_number,
x_destination,
x_itt_prog_outcome,
x_hesa_return_name,
x_hesa_return_id,
x_hesa_submission_name,
x_associate_ucas_number,
x_associate_scott_cand,
x_associate_teach_ref_num,
x_associate_nhs_reg_num,
x_nhs_funding_source,
x_ufi_place,
x_postcode,
x_social_class_ind,
x_occcode,
x_total_ucas_tariff,
x_nhs_employer,
x_return_type,
x_qual_aim_subj1 ,
x_qual_aim_subj2,
x_qual_aim_subj3,
x_qual_aim_proportion,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_dependants_cd,
x_implied_fund_rate,
x_gov_initiatives_cd,
x_units_for_qual,
x_disadv_uplift_elig_cd,
x_franch_partner_cd,
x_units_completed,
x_franch_out_arr_cd,
x_employer_role_cd,
x_disadv_uplift_factor,
x_enh_fund_elig_cd,
x_exclude_flag
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation(
new_references.hesa_st_spa_id
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
check_uniqueness;
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
check_child_existance;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation (
new_references.hesa_st_spa_id
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
check_uniqueness;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
check_child_existance;
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_hesa_st_spa_id IN OUT NOCOPY NUMBER,
x_org_id IN NUMBER,
x_person_id IN NUMBER,
x_course_cd IN VARCHAR2,
x_version_number IN NUMBER,
x_fe_student_marker IN VARCHAR2,
x_domicile_cd IN VARCHAR2,
x_inst_last_attended IN VARCHAR2,
x_year_left_last_inst IN VARCHAR2,
x_highest_qual_on_entry IN VARCHAR2,
x_date_qual_on_entry_calc IN DATE,
x_a_level_point_score IN NUMBER,
x_highers_points_scores IN NUMBER,
x_occupation_code IN VARCHAR2,
x_commencement_dt IN DATE,
x_special_student IN VARCHAR2,
x_student_qual_aim IN VARCHAR2,
x_student_fe_qual_aim IN VARCHAR2,
x_teacher_train_prog_id IN VARCHAR2,
x_itt_phase IN VARCHAR2,
x_bilingual_itt_marker IN VARCHAR2,
x_teaching_qual_gain_sector IN VARCHAR2,
x_teaching_qual_gain_subj1 IN VARCHAR2,
x_teaching_qual_gain_subj2 IN VARCHAR2,
x_teaching_qual_gain_subj3 IN VARCHAR2,
x_student_inst_number IN VARCHAR2,
x_destination IN VARCHAR2,
x_itt_prog_outcome IN VARCHAR2,
x_hesa_return_name IN VARCHAR2,
x_hesa_return_id IN NUMBER,
x_hesa_submission_name IN VARCHAR2,
x_associate_ucas_number IN VARCHAR2,
x_associate_scott_cand IN VARCHAR2,
x_associate_teach_ref_num IN VARCHAR2,
x_associate_nhs_reg_num IN VARCHAR2,
x_nhs_funding_source IN VARCHAR2,
x_ufi_place IN VARCHAR2,
x_postcode IN VARCHAR2,
x_social_class_ind IN VARCHAR2,
x_occcode IN VARCHAR2,
x_total_ucas_tariff IN NUMBER,
x_nhs_employer IN VARCHAR2,
x_return_type IN VARCHAR2,
x_qual_aim_subj1 IN VARCHAR2,
x_qual_aim_subj2 IN VARCHAR2,
x_qual_aim_subj3 IN VARCHAR2,
x_qual_aim_proportion IN VARCHAR2,
x_mode IN VARCHAR2,
x_dependants_cd IN VARCHAR2 ,
x_implied_fund_rate IN NUMBER ,
x_gov_initiatives_cd IN VARCHAR2 ,
x_units_for_qual IN NUMBER ,
x_disadv_uplift_elig_cd IN VARCHAR2 ,
x_franch_partner_cd IN VARCHAR2 ,
x_units_completed IN NUMBER ,
x_franch_out_arr_cd IN VARCHAR2 ,
x_employer_role_cd IN VARCHAR2 ,
x_disadv_uplift_factor IN NUMBER ,
x_enh_fund_elig_cd IN VARCHAR2 ,
x_exclude_flag IN VARCHAR2
) AS
/*
|| Created By : [email protected]
|| Created On : 21-FEB-2002
|| Purpose : Handles the INSERT DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
|| w.r.t. SWCR006
|| (reverse chronological order - newest change first)
*/
CURSOR c IS
SELECT rowid
FROM igs_he_st_spa_all
WHERE hesa_st_spa_id = x_hesa_st_spa_id;
x_last_update_date DATE;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
x_last_update_date := SYSDATE;
x_last_updated_by := 1;
x_last_update_login := 0;
x_last_updated_by := fnd_global.user_id;
IF (x_last_updated_by IS NULL) THEN
x_last_updated_by := -1;
x_last_update_login := fnd_global.login_id;
IF (x_last_update_login IS NULL) THEN
x_last_update_login := -1;
SELECT igs_he_st_spa_all_s.NEXTVAL
INTO x_hesa_st_spa_id
FROM dual;
p_action => 'INSERT',
x_rowid => x_rowid,
x_hesa_st_spa_id => x_hesa_st_spa_id,
x_org_id => igs_ge_gen_003.get_org_id,
x_person_id => x_person_id,
x_course_cd => x_course_cd,
x_version_number => x_version_number,
x_fe_student_marker => x_fe_student_marker,
x_domicile_cd => x_domicile_cd,
x_inst_last_attended => x_inst_last_attended,
x_year_left_last_inst => x_year_left_last_inst,
x_highest_qual_on_entry => x_highest_qual_on_entry,
x_date_qual_on_entry_calc => x_date_qual_on_entry_calc,
x_a_level_point_score => x_a_level_point_score,
x_highers_points_scores => x_highers_points_scores,
x_occupation_code => x_occupation_code,
x_commencement_dt => x_commencement_dt,
x_special_student => x_special_student,
x_student_qual_aim => x_student_qual_aim,
x_student_fe_qual_aim => x_student_fe_qual_aim,
x_teacher_train_prog_id => x_teacher_train_prog_id,
x_itt_phase => x_itt_phase,
x_bilingual_itt_marker => x_bilingual_itt_marker,
x_teaching_qual_gain_sector => x_teaching_qual_gain_sector,
x_teaching_qual_gain_subj1 => x_teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2 => x_teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3 => x_teaching_qual_gain_subj3,
x_student_inst_number => x_student_inst_number,
x_destination => x_destination,
x_itt_prog_outcome => x_itt_prog_outcome,
x_hesa_return_name => x_hesa_return_name,
x_hesa_return_id => x_hesa_return_id,
x_hesa_submission_name => x_hesa_submission_name,
x_associate_ucas_number => x_associate_ucas_number,
x_associate_scott_cand => x_associate_scott_cand,
x_associate_teach_ref_num => x_associate_teach_ref_num,
x_associate_nhs_reg_num => x_associate_nhs_reg_num,
x_nhs_funding_source => x_nhs_funding_source,
x_ufi_place => x_ufi_place,
x_postcode => x_postcode,
x_social_class_ind => x_social_class_ind,
x_occcode => x_occcode,
x_total_ucas_tariff => x_total_ucas_tariff,
x_nhs_employer => x_nhs_employer,
x_return_type => x_return_type,
x_qual_aim_subj1 => x_qual_aim_subj1,
x_qual_aim_subj2 => x_qual_aim_subj2,
x_qual_aim_subj3 => x_qual_aim_subj3,
x_qual_aim_proportion => x_qual_aim_proportion ,
x_creation_date => x_last_update_date,
x_created_by => x_last_updated_by,
x_last_update_date => x_last_update_date,
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login,
x_dependants_cd => x_dependants_cd,
x_implied_fund_rate => x_implied_fund_rate,
x_gov_initiatives_cd => x_gov_initiatives_cd,
x_units_for_qual => x_units_for_qual,
x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
x_franch_partner_cd => x_franch_partner_cd,
x_units_completed => x_units_completed,
x_franch_out_arr_cd => x_franch_out_arr_cd,
x_employer_role_cd => x_employer_role_cd,
x_disadv_uplift_factor => x_disadv_uplift_factor,
x_enh_fund_elig_cd => x_enh_fund_elig_cd,
x_exclude_flag => x_exclude_flag
);
INSERT INTO igs_he_st_spa_all (
hesa_st_spa_id,
org_id,
person_id,
course_cd,
version_number,
fe_student_marker,
domicile_cd,
inst_last_attended,
year_left_last_inst,
highest_qual_on_entry,
date_qual_on_entry_calc,
a_level_point_score,
highers_points_scores,
occupation_code,
commencement_dt,
special_student,
student_qual_aim,
student_fe_qual_aim,
teacher_train_prog_id,
itt_phase,
bilingual_itt_marker,
teaching_qual_gain_sector,
teaching_qual_gain_subj1,
teaching_qual_gain_subj2,
teaching_qual_gain_subj3,
student_inst_number,
destination,
itt_prog_outcome,
hesa_return_name,
hesa_return_id,
hesa_submission_name,
associate_ucas_number,
associate_scott_cand,
associate_teach_ref_num,
associate_nhs_reg_num,
nhs_funding_source,
ufi_place,
postcode,
social_class_ind,
occcode,
total_ucas_tariff,
nhs_employer,
return_type,
qual_aim_subj1,
qual_aim_subj2,
qual_aim_subj3,
qual_aim_proportion,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
dependants_cd,
implied_fund_rate,
gov_initiatives_cd,
units_for_qual,
disadv_uplift_elig_cd,
franch_partner_cd,
units_completed,
franch_out_arr_cd,
employer_role_cd,
disadv_uplift_factor,
enh_fund_elig_cd,
exclude_flag
) VALUES (
new_references.hesa_st_spa_id,
new_references.org_id,
new_references.person_id,
new_references.course_cd,
new_references.version_number,
new_references.fe_student_marker,
new_references.domicile_cd,
new_references.inst_last_attended,
new_references.year_left_last_inst,
new_references.highest_qual_on_entry,
new_references.date_qual_on_entry_calc,
new_references.a_level_point_score,
new_references.highers_points_scores,
new_references.occupation_code,
new_references.commencement_dt,
new_references.special_student,
new_references.student_qual_aim,
new_references.student_fe_qual_aim,
new_references.teacher_train_prog_id,
new_references.itt_phase,
new_references.bilingual_itt_marker,
new_references.teaching_qual_gain_sector,
new_references.teaching_qual_gain_subj1,
new_references.teaching_qual_gain_subj2,
new_references.teaching_qual_gain_subj3,
new_references.student_inst_number,
new_references.destination,
new_references.itt_prog_outcome,
new_references.hesa_return_name,
new_references.hesa_return_id,
new_references.hesa_submission_name,
new_references.associate_ucas_number,
new_references.associate_scott_cand,
new_references.associate_teach_ref_num,
new_references.associate_nhs_reg_num,
new_references.nhs_funding_source,
new_references.ufi_place,
new_references.postcode,
new_references.social_class_ind,
new_references.occcode,
new_references.total_ucas_tariff,
new_references.nhs_employer,
new_references.return_type,
new_references.qual_aim_subj1,
new_references.qual_aim_subj2,
new_references.qual_aim_subj3,
new_references.qual_aim_proportion,
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
new_references.dependants_cd,
new_references.implied_fund_rate,
new_references.gov_initiatives_cd,
new_references.units_for_qual,
new_references.disadv_uplift_elig_cd,
new_references.franch_partner_cd,
new_references.units_completed,
new_references.franch_out_arr_cd,
new_references.employer_role_cd,
new_references.disadv_uplift_factor,
new_references.enh_fund_elig_cd,
new_references.exclude_flag
);
END insert_row;
SELECT
person_id,
course_cd,
version_number,
fe_student_marker,
domicile_cd,
inst_last_attended,
year_left_last_inst,
highest_qual_on_entry,
date_qual_on_entry_calc,
a_level_point_score,
highers_points_scores,
occupation_code,
commencement_dt,
special_student,
student_qual_aim,
student_fe_qual_aim,
teacher_train_prog_id,
itt_phase,
bilingual_itt_marker,
teaching_qual_gain_sector,
teaching_qual_gain_subj1,
teaching_qual_gain_subj2,
teaching_qual_gain_subj3,
student_inst_number,
destination,
itt_prog_outcome,
hesa_return_name,
hesa_return_id,
hesa_submission_name,
associate_ucas_number,
associate_scott_cand,
associate_teach_ref_num,
associate_nhs_reg_num,
nhs_funding_source,
ufi_place,
postcode,
social_class_ind,
occcode,
total_ucas_tariff,
nhs_employer,
return_type,
qual_aim_subj1,
qual_aim_subj2 ,
qual_aim_subj3 ,
qual_aim_proportion,
dependants_cd,
implied_fund_rate,
gov_initiatives_cd,
units_for_qual,
disadv_uplift_elig_cd,
franch_partner_cd,
units_completed,
franch_out_arr_cd,
employer_role_cd,
disadv_uplift_factor,
enh_fund_elig_cd,
exclude_flag
FROM igs_he_st_spa_all
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_hesa_st_spa_id IN NUMBER,
x_org_id IN NUMBER,
x_person_id IN NUMBER,
x_course_cd IN VARCHAR2,
x_version_number IN NUMBER,
x_fe_student_marker IN VARCHAR2,
x_domicile_cd IN VARCHAR2,
x_inst_last_attended IN VARCHAR2,
x_year_left_last_inst IN VARCHAR2,
x_highest_qual_on_entry IN VARCHAR2,
x_date_qual_on_entry_calc IN DATE,
x_a_level_point_score IN NUMBER,
x_highers_points_scores IN NUMBER,
x_occupation_code IN VARCHAR2,
x_commencement_dt IN DATE,
x_special_student IN VARCHAR2,
x_student_qual_aim IN VARCHAR2,
x_student_fe_qual_aim IN VARCHAR2,
x_teacher_train_prog_id IN VARCHAR2,
x_itt_phase IN VARCHAR2,
x_bilingual_itt_marker IN VARCHAR2,
x_teaching_qual_gain_sector IN VARCHAR2,
x_teaching_qual_gain_subj1 IN VARCHAR2,
x_teaching_qual_gain_subj2 IN VARCHAR2,
x_teaching_qual_gain_subj3 IN VARCHAR2,
x_student_inst_number IN VARCHAR2,
x_destination IN VARCHAR2,
x_itt_prog_outcome IN VARCHAR2,
x_hesa_return_name IN VARCHAR2,
x_hesa_return_id IN NUMBER,
x_hesa_submission_name IN VARCHAR2,
x_associate_ucas_number IN VARCHAR2,
x_associate_scott_cand IN VARCHAR2,
x_associate_teach_ref_num IN VARCHAR2,
x_associate_nhs_reg_num IN VARCHAR2,
x_nhs_funding_source IN VARCHAR2,
x_ufi_place IN VARCHAR2,
x_postcode IN VARCHAR2,
x_social_class_ind IN VARCHAR2,
x_occcode IN VARCHAR2,
x_total_ucas_tariff IN NUMBER,
x_nhs_employer IN VARCHAR2,
x_return_type IN VARCHAR2,
x_qual_aim_subj1 IN VARCHAR2,
x_qual_aim_subj2 IN VARCHAR2,
x_qual_aim_subj3 IN VARCHAR2,
x_qual_aim_proportion IN VARCHAR2,
x_mode IN VARCHAR2,
x_dependants_cd IN VARCHAR2 ,
x_implied_fund_rate IN NUMBER ,
x_gov_initiatives_cd IN VARCHAR2 ,
x_units_for_qual IN NUMBER ,
x_disadv_uplift_elig_cd IN VARCHAR2 ,
x_franch_partner_cd IN VARCHAR2 ,
x_units_completed IN NUMBER ,
x_franch_out_arr_cd IN VARCHAR2 ,
x_employer_role_cd IN VARCHAR2 ,
x_disadv_uplift_factor IN NUMBER ,
x_enh_fund_elig_cd IN VARCHAR2 ,
x_exclude_flag IN VARCHAR2
) AS
/*
|| Created By : [email protected]
|| Created On : 21-FEB-2002
|| Purpose : Handles the UPDATE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
||smvk 13-feb-2002 Call to igs_ge_gen_003.get_org_id
|| w.r.t. SWCR006
||
|| (reverse chronological order - newest change first)
*/
x_last_update_date DATE ;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
x_last_update_date := SYSDATE;
x_last_updated_by := 1;
x_last_update_login := 0;
x_last_updated_by := fnd_global.user_id;
IF x_last_updated_by IS NULL THEN
x_last_updated_by := -1;
x_last_update_login := fnd_global.login_id;
IF (x_last_update_login IS NULL) THEN
x_last_update_login := -1;
p_action => 'UPDATE',
x_rowid => x_rowid,
x_hesa_st_spa_id => x_hesa_st_spa_id,
x_org_id => igs_ge_gen_003.get_org_id,
x_person_id => x_person_id,
x_course_cd => x_course_cd,
x_version_number => x_version_number,
x_fe_student_marker => x_fe_student_marker,
x_domicile_cd => x_domicile_cd,
x_inst_last_attended => x_inst_last_attended,
x_year_left_last_inst => x_year_left_last_inst,
x_highest_qual_on_entry => x_highest_qual_on_entry,
x_date_qual_on_entry_calc => x_date_qual_on_entry_calc,
x_a_level_point_score => x_a_level_point_score,
x_highers_points_scores => x_highers_points_scores,
x_occupation_code => x_occupation_code,
x_commencement_dt => x_commencement_dt,
x_special_student => x_special_student,
x_student_qual_aim => x_student_qual_aim,
x_student_fe_qual_aim => x_student_fe_qual_aim,
x_teacher_train_prog_id => x_teacher_train_prog_id,
x_itt_phase => x_itt_phase,
x_bilingual_itt_marker => x_bilingual_itt_marker,
x_teaching_qual_gain_sector => x_teaching_qual_gain_sector,
x_teaching_qual_gain_subj1 => x_teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2 => x_teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3 => x_teaching_qual_gain_subj3,
x_student_inst_number => x_student_inst_number,
x_destination => x_destination,
x_itt_prog_outcome => x_itt_prog_outcome,
x_hesa_return_name => x_hesa_return_name,
x_hesa_return_id => x_hesa_return_id,
x_hesa_submission_name => x_hesa_submission_name,
x_associate_ucas_number => x_associate_ucas_number,
x_associate_scott_cand => x_associate_scott_cand,
x_associate_teach_ref_num => x_associate_teach_ref_num,
x_associate_nhs_reg_num => x_associate_nhs_reg_num,
x_nhs_funding_source => x_nhs_funding_source,
x_ufi_place => x_ufi_place,
x_postcode => x_postcode,
x_social_class_ind => x_social_class_ind,
x_occcode => x_occcode,
x_total_ucas_tariff => x_total_ucas_tariff,
x_nhs_employer => x_nhs_employer,
x_return_type => x_return_type,
x_qual_aim_subj1 => x_qual_aim_subj1,
x_qual_aim_subj2 => x_qual_aim_subj2,
x_qual_aim_subj3 => x_qual_aim_subj3,
x_qual_aim_proportion => x_qual_aim_proportion ,
x_creation_date => x_last_update_date,
x_created_by => x_last_updated_by,
x_last_update_date => x_last_update_date,
x_last_updated_by => x_last_updated_by,
x_last_update_login => x_last_update_login,
x_dependants_cd => x_dependants_cd,
x_implied_fund_rate => x_implied_fund_rate,
x_gov_initiatives_cd => x_gov_initiatives_cd,
x_units_for_qual => x_units_for_qual,
x_disadv_uplift_elig_cd => x_disadv_uplift_elig_cd,
x_franch_partner_cd => x_franch_partner_cd,
x_units_completed => x_units_completed,
x_franch_out_arr_cd => x_franch_out_arr_cd,
x_employer_role_cd => x_employer_role_cd,
x_disadv_uplift_factor => x_disadv_uplift_factor,
x_enh_fund_elig_cd => x_enh_fund_elig_cd,
x_exclude_flag => x_exclude_flag
);
UPDATE igs_he_st_spa_all
SET
org_id = new_references.org_id,
person_id = new_references.person_id,
course_cd = new_references.course_cd,
version_number = new_references.version_number,
fe_student_marker = new_references.fe_student_marker,
domicile_cd = new_references.domicile_cd,
inst_last_attended = new_references.inst_last_attended,
year_left_last_inst = new_references.year_left_last_inst,
highest_qual_on_entry = new_references.highest_qual_on_entry,
date_qual_on_entry_calc = new_references.date_qual_on_entry_calc,
a_level_point_score = new_references.a_level_point_score,
highers_points_scores = new_references.highers_points_scores,
occupation_code = new_references.occupation_code,
commencement_dt = new_references.commencement_dt,
special_student = new_references.special_student,
student_qual_aim = new_references.student_qual_aim,
student_fe_qual_aim = new_references.student_fe_qual_aim,
teacher_train_prog_id = new_references.teacher_train_prog_id,
itt_phase = new_references.itt_phase,
bilingual_itt_marker = new_references.bilingual_itt_marker,
teaching_qual_gain_sector = new_references.teaching_qual_gain_sector,
teaching_qual_gain_subj1 = new_references.teaching_qual_gain_subj1,
teaching_qual_gain_subj2 = new_references.teaching_qual_gain_subj2,
teaching_qual_gain_subj3 = new_references.teaching_qual_gain_subj3,
student_inst_number = new_references.student_inst_number,
destination = new_references.destination,
itt_prog_outcome = new_references.itt_prog_outcome,
hesa_return_name = new_references.hesa_return_name,
hesa_return_id = new_references.hesa_return_id,
hesa_submission_name = new_references.hesa_submission_name,
associate_ucas_number = new_references.associate_ucas_number,
associate_scott_cand = new_references.associate_scott_cand,
associate_teach_ref_num = new_references.associate_teach_ref_num,
associate_nhs_reg_num = new_references.associate_nhs_reg_num,
nhs_funding_source = new_references.nhs_funding_source,
ufi_place = new_references.ufi_place,
postcode = new_references.postcode,
social_class_ind = new_references.social_class_ind,
occcode = new_references.occcode,
total_ucas_tariff = new_references.total_ucas_tariff,
nhs_employer = new_references.nhs_employer,
return_type = new_references.return_type,
qual_aim_subj1 = new_references.qual_aim_subj1,
qual_aim_subj2 = new_references.qual_aim_subj2,
qual_aim_subj3 = new_references.qual_aim_subj3,
qual_aim_proportion = new_references.qual_aim_proportion ,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
dependants_cd = new_references.dependants_cd,
implied_fund_rate = new_references.implied_fund_rate,
gov_initiatives_cd = new_references.gov_initiatives_cd,
units_for_qual = new_references.units_for_qual,
disadv_uplift_elig_cd = new_references.disadv_uplift_elig_cd,
franch_partner_cd = new_references.franch_partner_cd,
units_completed = new_references.units_completed,
franch_out_arr_cd = new_references.franch_out_arr_cd,
employer_role_cd = new_references.employer_role_cd,
disadv_uplift_factor = new_references.disadv_uplift_factor,
enh_fund_elig_cd = new_references.enh_fund_elig_cd,
exclude_flag = new_references.exclude_flag
WHERE rowid = x_rowid;
END update_row;
|| Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c1 IS
SELECT rowid
FROM igs_he_st_spa_all
WHERE hesa_st_spa_id = x_hesa_st_spa_id;
insert_row (
x_rowid,
x_hesa_st_spa_id,
x_org_id,
x_person_id,
x_course_cd,
x_version_number,
x_fe_student_marker,
x_domicile_cd,
x_inst_last_attended,
x_year_left_last_inst,
x_highest_qual_on_entry,
x_date_qual_on_entry_calc,
x_a_level_point_score,
x_highers_points_scores,
x_occupation_code,
x_commencement_dt,
x_special_student,
x_student_qual_aim,
x_student_fe_qual_aim,
x_teacher_train_prog_id,
x_itt_phase,
x_bilingual_itt_marker,
x_teaching_qual_gain_sector,
x_teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3,
x_student_inst_number,
x_destination,
x_itt_prog_outcome,
x_hesa_return_name,
x_hesa_return_id,
x_hesa_submission_name,
x_associate_ucas_number,
x_associate_scott_cand,
x_associate_teach_ref_num,
x_associate_nhs_reg_num,
x_nhs_funding_source,
x_ufi_place,
x_postcode,
x_social_class_ind,
x_occcode,
x_total_ucas_tariff,
x_nhs_employer,
x_return_type,
x_qual_aim_subj1,
x_qual_aim_subj2 ,
x_qual_aim_subj3,
x_qual_aim_proportion,
l_mode,
x_dependants_cd,
x_implied_fund_rate,
x_gov_initiatives_cd,
x_units_for_qual,
x_disadv_uplift_elig_cd,
x_franch_partner_cd,
x_units_completed,
x_franch_out_arr_cd,
x_employer_role_cd,
x_disadv_uplift_factor,
x_enh_fund_elig_cd,
x_exclude_flag
);
update_row (
x_rowid,
x_hesa_st_spa_id,
x_org_id,
x_person_id,
x_course_cd,
x_version_number,
x_fe_student_marker,
x_domicile_cd,
x_inst_last_attended,
x_year_left_last_inst,
x_highest_qual_on_entry,
x_date_qual_on_entry_calc,
x_a_level_point_score,
x_highers_points_scores,
x_occupation_code,
x_commencement_dt,
x_special_student,
x_student_qual_aim,
x_student_fe_qual_aim,
x_teacher_train_prog_id,
x_itt_phase,
x_bilingual_itt_marker,
x_teaching_qual_gain_sector,
x_teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3,
x_student_inst_number,
x_destination,
x_itt_prog_outcome,
x_hesa_return_name,
x_hesa_return_id,
x_hesa_submission_name,
x_associate_ucas_number,
x_associate_scott_cand,
x_associate_teach_ref_num,
x_associate_nhs_reg_num,
x_nhs_funding_source,
x_ufi_place,
x_postcode,
x_social_class_ind,
x_occcode,
x_total_ucas_tariff,
x_nhs_employer,
x_return_type,
x_qual_aim_subj1 ,
x_qual_aim_subj2 ,
x_qual_aim_subj3 ,
x_qual_aim_proportion ,
l_mode ,
x_dependants_cd,
x_implied_fund_rate,
x_gov_initiatives_cd,
x_units_for_qual,
x_disadv_uplift_elig_cd,
x_franch_partner_cd,
x_units_completed,
x_franch_out_arr_cd,
x_employer_role_cd,
x_disadv_uplift_factor,
x_enh_fund_elig_cd,
x_exclude_flag
);
PROCEDURE delete_row (
x_rowid IN VARCHAR2,
x_mode IN VARCHAR2
) AS
/*
|| Created By : [email protected]
|| Created On : 21-FEB-2002
|| Purpose : Handles the DELETE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
before_dml (
p_action => 'DELETE',
x_rowid => x_rowid
);
DELETE FROM igs_he_st_spa_all
WHERE rowid = x_rowid;
END delete_row;