[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id ;
SELECT 'X'
FROM igs_ps_grp_mbr
WHERE course_cd = cp_course_cd
AND version_number = cp_version
AND course_group_cd = p_program_group;
SELECT map1
FROM igs_he_code_map_val
WHERE Association_code ='OSS_HESA_AWD_ASSOC'
AND Map2 = cp_Student_qual_aim ;
SELECT uqd.Exam_level,
uqd.Subject_code,
MAX(grd.rank) ucas_tariff
FROM igs_uc_qual_dets uqd,
igs_as_grd_sch_grade grd
WHERE uqd.Person_id = cp_person_id
AND uqd.Exam_level = cp_exam_level
AND grd.grade = uqd.approved_result
AND grd.grading_schema_cd = uqd.grading_schema_cd
AND grd.version_number = uqd.version_number
AND ( uqd.Year IS NULL OR uqd.Year <= TO_CHAR (cp_commencement_date, 'YYYY'))
AND ( subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
FROM igs_he_ut_excl_qals
WHERE tariff_calc_type_cd = cp_tariff_calc_type
AND (award_cd IS NULL OR award_cd = cp_exam_level)))
GROUP BY Exam_level, Subject_code ;
SELECT DISTINCT Exam_level
FROM Igs_uc_qual_dets
WHERE Person_id = l_person_id
AND exam_level IN (SELECT hula.award_cd
FROM igs_he_ut_lvl_award hula
WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
AND hula.closed_ind = 'N')
AND exam_level NOT IN (SELECT hueq.award_cd
FROM igs_he_ut_excl_qals hueq
WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
AND hueq.field_of_study IS NULL)
AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
FROM igs_he_ut_excl_qals
WHERE tariff_calc_type_cd = cp_tariff_calc_type
AND award_cd IS NULL))
AND (exam_level, subject_code) NOT IN (SELECT eqas.award_cd, eqas.field_of_study
FROM igs_he_ut_excl_qals eqas
WHERE eqas.tariff_calc_type_cd = cp_tariff_calc_type
AND eqas.award_cd IS NOT NULL
AND eqas.field_of_study IS NOT NULL)
AND (Year IS NULL OR Year <= TO_CHAR (l_commencement_date, 'YYYY') ) ;
CURSOR Cur_st_spa_for_update (l_person_id igs_he_st_spa.person_id%TYPE,
l_course_cd igs_he_st_spa.course_cd%TYPE) IS
SELECT ihss.row_id row_id,
ihss.hesa_st_spa_id hesa_st_spa_id,
ihss.course_cd course_cd,
ihss.version_number version_number,
ihss.person_id person_id,
ihss.fe_student_marker fe_student_marker,
ihss.domicile_cd domicile_cd,
ihss.inst_last_attended inst_last_attended,
ihss.year_left_last_inst year_left_last_inst,
ihss.highest_qual_on_entry highest_qual_on_entry,
ihss.date_qual_on_entry_calc date_qual_on_entry_calc,
ihss.a_level_point_score a_level_point_score,
ihss.highers_points_scores highers_points_scores,
ihss.occupation_code occupation_code,
ihss.commencement_dt commencement_dt,
ihss.special_student special_student,
ihss.student_qual_aim student_qual_aim,
ihss.student_fe_qual_aim student_fe_qual_aim,
ihss.teacher_train_prog_id teacher_train_prog_id,
ihss.itt_phase itt_phase,
ihss.bilingual_itt_marker bilingual_itt_marker,
ihss.teaching_qual_gain_sector teaching_qual_gain_sector,
ihss.teaching_qual_gain_subj1 teaching_qual_gain_subj1,
ihss.teaching_qual_gain_subj2 teaching_qual_gain_subj2,
ihss.teaching_qual_gain_subj3 teaching_qual_gain_subj3,
ihss.student_inst_number student_inst_number,
ihss.hesa_return_name hesa_return_name,
ihss.hesa_return_id hesa_return_id,
ihss.hesa_submission_name hesa_submission_name,
ihss.associate_ucas_number associate_ucas_number,
ihss.associate_scott_cand associate_scott_cand,
ihss.associate_teach_ref_num associate_teach_ref_num,
ihss.associate_nhs_reg_num associate_nhs_reg_num,
ihss.itt_prog_outcome itt_prog_outcome,
ihss.nhs_funding_source nhs_funding_source,
ihss.ufi_place ufi_place,
ihss.postcode postcode,
ihss.social_class_ind social_class_ind,
ihss.destination destination,
ihss.occcode occcode,
ihss.total_ucas_tariff total_ucas_tariff,
ihss.nhs_employer nhs_employer,
ihss.return_type,
ihss.qual_aim_subj1,
ihss.qual_aim_subj2,
ihss.qual_aim_subj3,
ihss.qual_aim_proportion,
ihss.dependants_cd,
ihss.implied_fund_rate,
ihss.gov_initiatives_cd,
ihss.units_for_qual,
ihss.disadv_uplift_elig_cd,
ihss.franch_partner_cd,
ihss.units_completed,
ihss.franch_out_arr_cd,
ihss.employer_role_cd,
ihss.disadv_uplift_factor,
ihss.enh_fund_elig_cd,
ihss.exclude_flag
FROM igs_he_st_spa ihss
WHERE ihss.person_id = l_person_id
AND ihss.course_cd = l_course_cd;
SELECT rowid
FROM Igs_he_st_spa_ut
WHERE Person_id = l_person_id
AND course_cd = l_course_cd;
SELECT Iagsta.grade,
Iagsta.To_grading_schema_cd,
Iagsta.to_version_number,
Iagsta.to_grade,
Iagsgv.full_grade_name, -- jchin 3484372 Added full grade name to output to log file
Iagsgv.rank
FROM Igs_uc_qual_dets Iuqd,
Igs_as_grd_sch_trn_all Iagsta,
Igs_as_grd_sch_grade_v Iagsgv,
Igs_as_grd_sch_grade grd
WHERE Iuqd.person_id = l_person_id
AND Iagsta.grading_schema_cd = Iuqd.grading_schema_cd
AND Iagsta.version_number = Iuqd.version_number
AND grd.grading_schema_cd = iuqd.grading_schema_cd
AND grd.version_number = Iuqd.version_number
AND ( iuqd.approved_result IS NULL
OR
(grd.grade = iuqd.approved_result AND grd.s_result_type = 'PASS' )
)
AND Iagsgv.grading_schema_cd = Iagsta.to_grading_schema_cd
AND Iagsgv.version_number = Iagsta.to_version_number
AND Iagsgv.grade = Iagsta.to_grade
AND (Iuqd.Year IS NULL OR Iuqd.Year <= TO_CHAR(p_commencement_dt,'YYYY'))
AND EXISTS (SELECT 'X'
FROM Igs_he_code_values
WHERE Code_type = 'HESA_HIGH_QUAL_ON_ENT'
AND Value = Iagsta.to_grading_schema_cd
AND NVL(closed_ind,'N') = 'N' )
ORDER BY Iagsgv.rank ASC ;
SELECT DISTINCT exam_level
FROM igs_uc_qual_dets
WHERE person_id = cp_person_id
AND exam_level <> cp_exam_level
AND subject_code = cp_subject_cd
AND exam_level IN (SELECT hula.award_cd
FROM igs_he_ut_lvl_award hula
WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
AND hula.closed_ind = 'N')
AND exam_level NOT IN (SELECT hueq.award_cd
FROM igs_he_ut_excl_qals hueq
WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
AND hueq.field_of_study IS NULL)
AND (year IS NULL OR Year <= TO_CHAR(cp_commencement_date, 'YYYY'));
SELECT parent_award_cd
FROM (SELECT *
FROM igs_he_ut_prt_award
WHERE tariff_calc_type_cd = cp_tariff_calc_type)
START WITH award_cd = cp_award_cd
CONNECT BY PRIOR parent_award_cd = award_cd
AND tariff_calc_type_cd = cp_tariff_calc_type;
l_last_update_date Igs_uc_qual_dets.Last_update_date%TYPE ;
l_record_inserted NUMBER := 0;
l_record_updated NUMBER := 0;
C_st_spa_for_update cur_st_spa_for_update%ROWTYPE;
l_recs_for_insert NUMBER; -- to bypass inserting into SPA_UT table if no qual recs found
fnd_dsql.add_text('SELECT ihss.student_qual_aim , iespa.commencement_dt, ihss.date_qual_on_entry_calc,');
fnd_dsql.add_text('AND EXISTS (SELECT person_id from igs_uc_qual_dets where person_id = ihss.person_id) ');
fnd_dsql.add_text(' (SELECT psv.course_cd, psv.version_number FROM igs_ps_ver psv WHERE psv.course_type = ');
Igs_he_st_spa_ut_all_pkg.delete_row(x_rowid => Cur_st_spa_ut_rec.rowid );
l_recs_for_insert := 0; -- initialize to zero
l_recs_for_insert := l_recs_for_insert + 1;
IF l_recs_for_insert > 0 THEN
-- Insert the Tariff scores for each Exam level of an applicant and
-- the number of subject of each Exam level
Igs_he_st_spa_ut_all_pkg.Insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_hesa_st_spau_id => l_hesa_st_spau_id,
x_Person_id => c_st_spa.person_id,
x_Course_cd => c_st_spa.course_cd,
x_Version_number => c_st_spa.version_number,
x_Qualification_level => c_exam_level.exam_level,
x_Number_of_qual => l_qual_count,
x_tariff_score => l_tariff_score,
x_org_id => igs_ge_gen_003.get_org_id );
l_record_inserted := l_record_inserted + 1 ;
/* picking up records to update the spa table */
OPEN Cur_st_spa_for_update (c_st_spa.person_id, c_st_spa.course_cd);
FETCH Cur_st_spa_for_update INTO c_st_spa_for_update;
CLOSE Cur_st_spa_for_update;
/* Getting the highest grade for the applicant to update the spa table */
l_grade := NULL ; --added by smaddali for bug2353094
Igs_he_st_spa_all_pkg.Update_row (
x_mode => 'R',
x_rowid => c_st_spa_for_update.row_id,
x_hesa_st_spa_id => c_st_spa_for_update.hesa_st_spa_id,
x_course_cd => c_st_spa_for_update.course_cd,
x_version_number => c_st_spa_for_update.version_number,
x_person_id => c_st_spa_for_update.person_id,
x_fe_student_marker => c_st_spa_for_update.fe_student_marker,
x_domicile_cd => c_st_spa_for_update.domicile_cd,
x_inst_last_attended => c_st_spa_for_update.inst_last_attended,
x_year_left_last_inst => c_st_spa_for_update.year_left_last_inst,
x_highest_qual_on_entry => l_grade.to_grade,
x_date_qual_on_entry_calc => SYSDATE,
x_a_level_point_score => c_st_spa_for_update.a_level_point_score,
x_highers_points_scores => c_st_spa_for_update.highers_points_scores,
x_occupation_code => c_st_spa_for_update.occupation_code,
x_commencement_dt => c_st_spa_for_update.commencement_dt,
x_special_student => c_st_spa_for_update.special_student,
x_student_qual_aim => c_st_spa_for_update.student_qual_aim,
x_student_fe_qual_aim => c_st_spa_for_update.student_fe_qual_aim,
x_teacher_train_prog_id => c_st_spa_for_update.teacher_train_prog_id,
x_itt_phase => c_st_spa_for_update.itt_phase,
x_bilingual_itt_marker => c_st_spa_for_update.bilingual_itt_marker,
x_teaching_qual_gain_sector => c_st_spa_for_update.teaching_qual_gain_sector,
x_teaching_qual_gain_subj1 => c_st_spa_for_update.teaching_qual_gain_subj1,
x_teaching_qual_gain_subj2 => c_st_spa_for_update.teaching_qual_gain_subj2,
x_teaching_qual_gain_subj3 => c_st_spa_for_update.teaching_qual_gain_subj3,
x_student_inst_number => c_st_spa_for_update.student_inst_number,
x_hesa_return_name => c_st_spa_for_update.hesa_return_name,
x_hesa_return_id => c_st_spa_for_update.hesa_return_id,
x_hesa_submission_name => c_st_spa_for_update.hesa_submission_name,
x_associate_ucas_number => c_st_spa_for_update.associate_ucas_number,
x_associate_scott_cand => c_st_spa_for_update.associate_scott_cand,
x_associate_teach_ref_num => c_st_spa_for_update.associate_teach_ref_num,
x_associate_nhs_reg_num => c_st_spa_for_update.associate_nhs_reg_num,
x_itt_prog_outcome => c_st_spa_for_update.itt_prog_outcome,
x_nhs_funding_source => c_st_spa_for_update.nhs_funding_source,
x_ufi_place => c_st_spa_for_update.ufi_place,
x_postcode => c_st_spa_for_update.postcode,
x_social_class_ind => c_st_spa_for_update.social_class_ind,
x_destination => c_st_spa_for_update.destination,
x_occcode => c_st_spa_for_update.occcode,
x_total_ucas_tariff => l_total_tariff_score,
x_nhs_employer => c_st_spa_for_update.nhs_employer,
x_return_type => c_st_spa_for_update.return_type,
x_qual_aim_subj1 => c_st_spa_for_update.qual_aim_subj1,
x_qual_aim_subj2 => c_st_spa_for_update.qual_aim_subj2,
x_qual_aim_subj3 => c_st_spa_for_update.qual_aim_subj3,
x_qual_aim_proportion => c_st_spa_for_update.qual_aim_proportion ,
x_org_id => igs_ge_gen_003.get_org_id,
x_dependants_cd => c_st_spa_for_update.dependants_cd ,
x_implied_fund_rate => c_st_spa_for_update.implied_fund_rate ,
x_gov_initiatives_cd => c_st_spa_for_update.gov_initiatives_cd ,
x_units_for_qual => c_st_spa_for_update.units_for_qual ,
x_disadv_uplift_elig_cd => c_st_spa_for_update.disadv_uplift_elig_cd ,
x_franch_partner_cd => c_st_spa_for_update.franch_partner_cd ,
x_units_completed => c_st_spa_for_update.units_completed ,
x_franch_out_arr_cd => c_st_spa_for_update.franch_out_arr_cd ,
x_employer_role_cd => c_st_spa_for_update.employer_role_cd ,
x_disadv_uplift_factor => c_st_spa_for_update.disadv_uplift_factor ,
x_enh_fund_elig_cd => c_st_spa_for_update.enh_fund_elig_cd,
x_exclude_flag => c_st_spa_for_update.exclude_flag);
l_record_updated := l_record_updated + 1 ;
fnd_message.set_token('REC_CNT',l_record_updated);
fnd_message.set_token('REC_CNT',l_record_inserted);
SELECT DISTINCT exam_level
FROM igs_uc_qual_dets
WHERE person_id = cp_person_id
AND exam_level IN (SELECT hula.award_cd
FROM igs_he_ut_lvl_award hula
WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
AND hula.closed_ind = 'N')
AND exam_level NOT IN (SELECT hueq.award_cd
FROM igs_he_ut_excl_qals hueq
WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
AND hueq.field_of_study IS NULL)
AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
FROM igs_he_ut_excl_qals
WHERE tariff_calc_type_cd = cp_tariff_calc_type
AND award_cd IS NULL))
AND (exam_level, subject_code) NOT IN (SELECT eqas.award_cd, eqas.field_of_study
FROM igs_he_ut_excl_qals eqas
WHERE eqas.tariff_calc_type_cd = cp_tariff_calc_type
AND eqas.award_cd IS NOT NULL
AND eqas.field_of_study IS NOT NULL);
SELECT uqd.Exam_level,
uqd.Subject_code,
MAX(grd.rank) ucas_tariff
FROM igs_uc_qual_dets uqd,
igs_as_grd_sch_grade grd
WHERE uqd.Person_id = cp_person_id
AND uqd.Exam_level = cp_exam_level
AND grd.grade = uqd.approved_result
AND grd.grading_schema_cd = uqd.grading_schema_cd
AND grd.version_number = uqd.version_number
AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
FROM igs_he_ut_excl_qals
WHERE tariff_calc_type_cd = cp_tariff_calc_type
AND (award_cd IS NULL OR award_cd = cp_exam_level)))
GROUP BY uqd.Exam_level, uqd.Subject_code ;
SELECT DISTINCT exam_level
FROM igs_uc_qual_dets
WHERE person_id = cp_person_id
AND exam_level <> cp_exam_level
AND subject_code = cp_subject_cd
AND exam_level IN (SELECT hula.award_cd
FROM igs_he_ut_lvl_award hula
WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
AND hula.closed_ind = 'N')
AND exam_level NOT IN (SELECT hueq.award_cd
FROM igs_he_ut_excl_qals hueq
WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
AND hueq.field_of_study IS NULL);
SELECT parent_award_cd
FROM ( SELECT *
FROM igs_he_ut_prt_award
WHERE tariff_calc_type_cd = cp_tariff_calc_type)
START WITH award_cd = cp_award_cd
CONNECT BY PRIOR parent_award_cd=award_cd
AND tariff_calc_type_cd = cp_tariff_calc_type;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT rowid
FROM igs_he_ut_prs_calcs
WHERE person_id = cp_person_id
AND tariff_calc_type_cd = cp_calc_type;
SELECT upd.rowid
FROM igs_he_ut_prs_dtls upd
WHERE person_id = cp_person_id
AND tariff_calc_type_cd = cp_calc_type;
l_record_inserted NUMBER := 0;
fnd_dsql.add_text('SELECT DISTINCT person_id FROM igs_uc_qual_dets ');
igs_he_ut_prs_calcs_pkg.insert_row(
x_rowid => check_prsn_tariff_exists_rec.rowid
,x_tariff_calc_type_cd => p_tariff_calc_type
,x_person_id => l_person_id
,x_calc_date => TRUNC(SYSDATE)
,x_mode => 'R');
igs_he_ut_prs_calcs_pkg.update_row(
x_rowid => check_prsn_tariff_exists_rec.rowid
,x_tariff_calc_type_cd => p_tariff_calc_type
,x_person_id => l_person_id
,x_calc_date => TRUNC(SYSDATE)
,x_mode => 'R');
igs_he_ut_prs_dtls_pkg.delete_row(del_dtls_rec.rowid);
igs_he_ut_prs_dtls_pkg.insert_row(
x_rowid => l_rowid
,x_tariff_calc_type_cd => p_tariff_calc_type
,x_person_id => l_person_id
,x_award_cd => prsn_exam_level_rec.exam_level
,x_number_of_qual => l_qual_count
,x_tariff_score => l_tariff_score
,x_mode => 'R' );
l_record_inserted := l_record_inserted + 1 ;
fnd_message.set_token('REC_CNT',l_record_inserted);
SELECT upd.award_cd
FROM igs_he_ut_prs_dtls upd
WHERE person_id = cp_person_id
AND tariff_calc_type_cd = cp_calc_type;
SELECT uqd.Exam_level,
uqd.Subject_code,
MAX(grd.rank) ucas_tariff
FROM igs_uc_qual_dets uqd,
igs_as_grd_sch_grade grd
WHERE uqd.Person_id = cp_person_id
AND uqd.Exam_level = cp_exam_level
AND grd.grade = uqd.approved_result
AND grd.grading_schema_cd = uqd.grading_schema_cd
AND grd.version_number = uqd.version_number
AND (subject_code IS NULL OR subject_code NOT IN (SELECT field_of_study
FROM igs_he_ut_excl_qals
WHERE tariff_calc_type_cd = cp_tariff_calc_type
AND (award_cd IS NULL OR award_cd = cp_exam_level)))
GROUP BY uqd.Exam_level, uqd.Subject_code ;
SELECT DISTINCT exam_level
FROM igs_uc_qual_dets
WHERE person_id = cp_person_id
AND exam_level <> cp_exam_level
AND subject_code = cp_subject_cd
AND exam_level IN (SELECT hula.award_cd
FROM igs_he_ut_lvl_award hula
WHERE hula.tariff_calc_type_cd = cp_tariff_calc_type
AND hula.closed_ind = 'N')
AND exam_level NOT IN (SELECT hueq.award_cd
FROM igs_he_ut_excl_qals hueq
WHERE hueq.tariff_calc_type_cd = cp_tariff_calc_type
AND hueq.field_of_study IS NULL);
SELECT parent_award_cd
FROM ( SELECT *
FROM igs_he_ut_prt_award
WHERE tariff_calc_type_cd = cp_tariff_calc_type)
START WITH award_cd = cp_award_cd
CONNECT BY PRIOR parent_award_cd=award_cd
AND tariff_calc_type_cd = cp_tariff_calc_type;
smvk 03-Jun-2003 Bug # 2858436.Modified the cursor c_prgawd to select open program awards only.
rbezawad 13-Feb-03 Modified w.r.t. HEFD202.1 build, Bug 2717744.
Introduced logic to aviod double counting of qualifications
pmarada 24-jul-2003 Before creating the ucas tariff scores for a student deleting old
ucas tariff score details. so removed the Igs_he_st_spa_ut_all_pkg update row call
and added delete row call. as per the bug 3064689
rgangara 29-Aug-03 Added 4 new parameters and created this as a separate procedure
for ease of understanding and maintenance
ayedubat 16-MAR-04 Added a new parameter, p_report_all_hierarchy_flag to the internal and
external tariff calculation procedure calls for Bug, 2956444
anwest 18-JAN-20 Bug# 4950285 R12 Disable OSS Mandate
-----------------------------------------------------------------------*/
CURSOR get_calc_type_cur (p_tariff_calc_type igs_he_ut_calc_type.tariff_calc_type_cd%TYPE) IS
SELECT tariff_calc_type_cd,
external_calc_ind,
report_all_hierarchy_flag
FROM igs_he_ut_calc_type
WHERE tariff_calc_type_cd = p_tariff_calc_type;