[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hesa_st_spa_id,
course_cd,
version_number,
person_id,
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,
hesa_return_name,
hesa_return_id,
hesa_submission_name,
associate_ucas_number,
associate_scott_cand,
associate_teach_ref_num,
associate_nhs_reg_num,
itt_prog_outcome,
nhs_funding_source,
ufi_place,
postcode,
social_class_ind,
destination,
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,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM igs_he_st_spa
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_crv_version_number;
SELECT a.hesa_sequence_id hesa_sequence_id,
a.person_id person_id,
a.admission_appl_number admission_appl_number,
a.nominated_course_cd nominated_course_cd,
a.sequence_number sequence_number,
a.occupation_cd occupation_cd,
a.domicile_cd domicile_cd,
a.social_class_cd social_class_cd,
a.special_student_cd special_student_cd,
a.creation_date creation_date,
a.created_by created_by,
a.last_update_date last_update_date,
a.last_updated_by last_updated_by,
a.last_update_login last_update_login
FROM igs_he_ad_dtl a,
igs_ad_ps_appl_inst b,
igs_en_stdnt_ps_att c
WHERE a.person_id = b.person_id
AND a.admission_appl_number = b.admission_appl_number
AND a.nominated_course_cd = b.nominated_course_cd
AND a.sequence_number = b.sequence_number
AND b.admission_appl_number = c.adm_admission_appl_number
AND b.person_id = c.person_id
AND b.nominated_course_cd = c.adm_nominated_course_cd
AND b.sequence_number = c.adm_sequence_number
AND c.person_id = p_person_id
AND c.course_cd = p_course_cd
AND c.version_number = p_crv_version_number;
SELECT a.postal_code postal_code
FROM igs_pe_addr_v a
WHERE a.person_id = p_person_id
AND (a.status = 'A' AND SYSDATE BETWEEN NVL(a.start_dt,SYSDATE) AND NVL(a.end_dt,SYSDATE))
AND (EXISTS(SELECT 'X'
FROM igs_pe_partysiteuse_v b
WHERE a.party_site_id = b.party_site_id
AND b.site_use_type = 'HOME'
AND b.active='Y')
OR a.correspondence = 'Y');
SELECT ROWID
FROM igs_he_st_spa
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND version_number = p_crv_version_number;
SELECT student_inst_number
FROM igs_he_st_spa
WHERE person_id = cp_person_id;
SELECT igs_he_st_spa_all_s.NEXTVAL
FROM dual;
igs_he_st_spa_all_pkg.insert_row(
x_rowid =>x_rowid,
x_org_id =>l_org_id,
x_hesa_st_spa_id =>v_stat_seq_num,
x_person_id =>p_person_id,
x_course_cd =>p_course_cd,
x_version_number =>p_crv_version_number,
x_fe_student_marker =>NULL,
x_student_inst_number =>l_std_inst_num ,
x_domicile_cd =>gv_adm_rec.domicile_cd,
x_inst_last_attended =>NULL,
x_year_left_last_inst =>NULL,
x_highest_qual_on_entry =>NULL,
x_date_qual_on_entry_calc =>NULL,
x_a_level_point_score =>NULL,
x_highers_points_scores =>NULL,
x_occupation_code =>gv_adm_rec.occupation_cd,
x_commencement_dt =>NULL,
x_social_class_ind =>gv_adm_rec.social_class_cd,
x_special_student =>gv_adm_rec.special_student_cd,
x_student_qual_aim =>NULL,
x_student_fe_qual_aim =>NULL,
x_teacher_train_prog_id =>NULL,
x_itt_phase =>NULL,
x_bilingual_itt_marker =>NULL,
x_teaching_qual_gain_sector=>NULL,
x_teaching_qual_gain_subj1 =>NULL,
x_teaching_qual_gain_subj2 =>NULL,
x_teaching_qual_gain_subj3 =>NULL,
x_destination =>NULL,
x_itt_prog_outcome =>NULL,
x_hesa_return_name =>NULL,
x_hesa_return_id =>NULL,
x_hesa_submission_name =>NULL,
x_associate_ucas_number =>NULL,
x_associate_scott_cand =>NULL,
x_associate_teach_ref_num =>NULL,
x_associate_nhs_reg_num =>NULL,
x_nhs_funding_source =>NULL,
x_ufi_place =>NULL,
x_postcode =>gv_addr_rec,
x_occcode =>NULL,
x_total_ucas_tariff =>NULL,
x_nhs_employer =>NULL,
x_return_type =>NULL,
x_qual_aim_subj1 =>NULL,
x_qual_aim_subj2 =>NULL,
x_qual_aim_subj3 =>NULL,
x_qual_aim_proportion =>NULL,
x_mode =>'R',
x_dependants_cd =>NULL,
x_implied_fund_rate =>NULL,
x_gov_initiatives_cd =>NULL,
x_units_for_qual =>NULL,
x_disadv_uplift_elig_cd =>NULL,
x_franch_partner_cd =>NULL,
x_units_completed =>NULL,
x_franch_out_arr_cd =>NULL,
x_employer_role_cd =>NULL,
x_disadv_uplift_factor =>NULL,
x_enh_fund_elig_cd =>NULL,
x_exclude_flag =>NULL);
SELECT hesa_en_susa_id,
person_id,
course_cd,
unit_set_cd,
us_version_number,
sequence_number,
new_he_entrant_cd,
term_time_accom,
disability_allow,
additional_sup_band,
sldd_discrete_prov,
study_mode,
study_location,
fte_perc_override,
franchising_activity,
completion_status,
good_stand_marker,
complete_pyr_study_cd,
credit_value_yop1,
credit_value_yop2,
credit_level1,
credit_level2,
grad_sch_grade,
mark,
teaching_inst1,
teaching_inst2,
pro_not_taught,
fundability_code,
fee_eligibility,
fee_band,
non_payment_reason,
student_fee,
fte_intensity,
fte_calc_type,
calculated_fte,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login ,
type_of_year
FROM igs_he_en_susa
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_set_cd = p_old_unit_set_cd
AND us_version_number = p_old_us_version_number
-- smaddali modified p_sequence_number to p_old_sequence_number for bug#2350629
AND sequence_number = p_old_sequence_number;
SELECT hesa_en_susa_id,
person_id,
course_cd,
unit_set_cd,
us_version_number,
sequence_number,
new_he_entrant_cd,
term_time_accom,
disability_allow,
additional_sup_band,
sldd_discrete_prov,
study_mode,
study_location,
fte_perc_override,
franchising_activity,
completion_status,
good_stand_marker,
complete_pyr_study_cd,
credit_value_yop1,
credit_value_yop2,
credit_level1,
credit_level2,
grad_sch_grade,
mark,
teaching_inst1,
teaching_inst2,
pro_not_taught,
fundability_code,
fee_eligibility,
fee_band,
non_payment_reason,
student_fee,
fte_intensity,
calculated_fte,
fte_calc_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login ,
type_of_year
FROM igs_he_en_susa
WHERE person_id = p_person_id
AND course_cd = p_course_cd
AND unit_set_cd = p_new_unit_set_cd
AND us_version_number = p_new_us_version_number
-- smaddali modified p_sequence_number to p_new_sequence_number for bug#2350629
AND sequence_number = p_new_sequence_number;
SELECT a.fte_intensity fte_intensity,
a.fte_calc_type fte_calc_type,
a.franchising_activity franchising_activity,
a.fee_band fee_band,
a.type_of_year,
a.fundability_cd
FROM igs_he_poous a,
igs_en_stdnt_ps_att b
WHERE a.course_cd = p_course_cd
AND a.crv_version_number = p_crv_version_number
AND a.unit_set_cd = p_old_unit_set_cd
AND a.us_version_number = p_old_us_version_number
AND b.person_id = p_person_id
AND b.course_cd = a.course_cd
AND b.version_number = a.crv_version_number
AND a.cal_type = b.cal_type
AND a.location_cd = b.location_cd
AND a.attendance_mode = b.attendance_mode
AND a.attendance_type = b.attendance_type;
SELECT a.fte_intensity fte_intensity,
a.fte_calc_type fte_calc_type,
a.franchising_activity franchising_activity,
a.fee_band fee_band,
a.type_of_year,
a.fundability_cd
FROM igs_he_poous a,
igs_en_stdnt_ps_att b
WHERE a.course_cd = p_course_cd
AND a.crv_version_number = p_crv_version_number
AND a.unit_set_cd = p_new_unit_set_cd
AND a.us_version_number = p_new_us_version_number
AND b.person_id = p_person_id
AND b.course_cd = a.course_cd
AND b.version_number = a.crv_version_number
AND a.cal_type = b.cal_type
AND a.location_cd = b.location_cd
AND a.attendance_mode = b.attendance_mode
AND a.attendance_type = b.attendance_type;
SELECT igs_he_en_susa_s.NEXTVAL
FROM dual;
igs_he_en_susa_pkg.insert_row(
x_rowid => v_rowid,
x_hesa_en_susa_id => v_susa_seq_num,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_set_cd => p_new_unit_set_cd,
x_us_version_number => p_new_us_version_number,
x_sequence_number => p_new_sequence_number,
x_new_he_entrant_cd => NULL,
x_term_time_accom => NULL,
x_disability_allow => NULL,
x_additional_sup_band => NULL,
x_sldd_discrete_prov => NULL,
x_study_mode => NULL,
x_study_location => NULL,
x_fte_perc_override => NULL,
x_franchising_activity => NULL,
x_completion_status => NULL,
x_good_stand_marker => NULL,
x_complete_pyr_study_cd => NULL,
x_credit_value_yop1 => NULL,
x_credit_value_yop2 => NULL,
x_credit_level_achieved1 => NULL,
x_credit_level_achieved2 => NULL,
x_credit_pt_achieved1 => NULL,
x_credit_pt_achieved2 => NULL,
x_credit_level1 => NULL,
x_credit_level2 => NULL,
x_grad_sch_grade => NULL,
x_mark => NULL,
x_teaching_inst1 => NULL,
x_teaching_inst2 => NULL,
x_pro_not_taught => NULL,
x_fundability_code => NULL,
x_fee_eligibility => NULL,
x_fee_band => NULL,
x_non_payment_reason => NULL,
x_student_fee => NULL,
x_fte_intensity => NULL,
x_fte_calc_type => NULL,
x_calculated_fte => NULL,
x_type_of_year => NULL,
x_mode => 'R',
x_credit_value_yop3 => NULL,
x_credit_value_yop4 => NULL,
x_credit_level_achieved3 => NULL,
x_credit_level_achieved4 => NULL,
x_credit_pt_achieved3 => NULL,
x_credit_pt_achieved4 => NULL,
x_credit_level3 => NULL,
x_credit_level4 => NULL,
x_additional_sup_cost => NULL,
x_enh_fund_elig_cd => NULL,
x_disadv_uplift_factor => NULL,
x_year_stu => NULL);
SELECT igs_he_en_susa_s.NEXTVAL
FROM dual;
igs_he_en_susa_pkg.insert_row(
x_rowid => x_rowid,
x_hesa_en_susa_id => v_susa_seq_num,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_set_cd => p_new_unit_set_cd,
x_us_version_number => p_new_us_version_number,
x_sequence_number => p_new_sequence_number,
x_new_he_entrant_cd => NULL,
x_term_time_accom => NULL,
x_disability_allow => gv_old_susa_rec.disability_allow,
x_additional_sup_band => gv_old_susa_rec.additional_sup_band,
x_sldd_discrete_prov => gv_old_susa_rec.sldd_discrete_prov,
x_study_mode => NULL,
x_study_location => NULL,
x_fte_perc_override => NULL,
x_franchising_activity => v_franchising,
x_completion_status => NULL,
x_good_stand_marker => NULL,
x_complete_pyr_study_cd => NULL,
x_grad_sch_grade => NULL,
x_mark => NULL,
x_credit_value_yop1 => NULL,
x_credit_value_yop2 => NULL,
x_credit_level_achieved1 => NULL,
x_credit_level_achieved2 => NULL,
x_credit_pt_achieved1 => NULL,
x_credit_pt_achieved2 => NULL,
x_credit_level1 => NULL,
x_credit_level2 => NULL,
x_teaching_inst1 => NULL,
x_teaching_inst2 => NULL,
x_pro_not_taught => NULL,
x_fundability_code => l_fundability_code,
x_fee_eligibility => gv_old_susa_rec.fee_eligibility,
x_fee_band => v_fee_band,
x_non_payment_reason => NULL,
x_student_fee => gv_old_susa_rec.student_fee,
x_fte_intensity => v_fte,
x_calculated_fte => NULL,
x_fte_calc_type => v_fte_type,
x_type_of_year => l_type_of_year,
x_mode => 'R',
x_credit_value_yop3 => NULL,
x_credit_value_yop4 => NULL,
x_credit_level_achieved3 => NULL,
x_credit_level_achieved4 => NULL,
x_credit_pt_achieved3 => NULL,
x_credit_pt_achieved4 => NULL,
x_credit_level3 => NULL,
x_credit_level4 => NULL,
x_additional_sup_cost => NULL,
x_enh_fund_elig_cd => NULL,
x_disadv_uplift_factor => NULL,
x_year_stu => NULL);
SELECT 'x'
FROM igs_ps_awd
WHERE s_award_type = 'COURSE' AND
award_cd = p_award_cd AND
closed_ind = 'N';
SELECT 'x'
FROM igs_as_grd_sch_grade gsg,
igs_he_code_values hcv
WHERE gsg.grading_schema_cd = hcv.value AND
hcv.code_type = 'HESA_HIGH_QUAL_ON_ENT' AND
gsg.grade = p_highest_qual_on_entry AND
NVL(hcv.closed_ind,'N') = 'N' ;
SELECT usc.s_unit_set_cat
FROM igs_en_unit_set us,
igs_en_unit_set_cat usc
WHERE us.unit_set_cd = p_unit_set_cd AND
us.version_number = p_us_version_number AND
us.unit_set_cat = usc.unit_set_cat;
SELECT 'x'
FROM igs_or_inst_outer_v
WHERE institution_cd = p_teaching_inst AND
system_inst_type='POST-SECONDARY';
SELECT 'x'
FROM igs_as_grd_sch_grade gsg,
igs_he_poous_all poous,
igs_en_stdnt_ps_att sca,
igs_as_su_setatmpt susa
WHERE gsg.grading_schema_cd = poous.grading_schema_cd AND
gsg.version_number = poous.gs_version_number AND
gsg.grade = p_grad_sch_grade AND
poous.course_cd = sca.course_cd AND
poous.crv_version_number = sca.version_number AND
poous.cal_type = sca.cal_type AND
poous.location_cd = sca.location_cd AND
poous.attendance_mode = sca.attendance_mode AND
poous.attendance_type = sca.attendance_type AND
poous.unit_set_cd = susa.unit_set_cd AND
poous.us_version_number = susa.us_version_number AND
sca.person_id = p_person_id AND
sca.course_cd = p_program_cd AND
susa.person_id= sca.person_id AND
susa.course_cd = sca.course_cd AND
susa.unit_set_cd = p_unit_set_cd;