[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_rows IN VARCHAR2 DEFAULT 'Y'
) IS
--
p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
SELECT *
FROM igs_pr_spo_interface
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND error_code IS NOT NULL
AND NVL (progression_outcome_type, '--') <> '-';
'p_delete_rows => ' || p_delete_rows
);
p_delete_rows
);
p_delete_rows
);
UPDATE igs_as_ug_interface
SET error_code = pr_error_rows.error_code
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND (alternate_code = pr_error_rows.progression_outcome_type
OR (alternate_code IS NULL
AND pr_error_rows.progression_outcome_type IS NULL)
)
AND (person_number = pr_error_rows.person_number
OR anonymous_id = pr_error_rows.anonymous_id)
AND course_cd = pr_error_rows.course_cd;
p_delete_rows IN VARCHAR2 DEFAULT 'Y'
) IS
--
p_batch_date DATE := TO_DATE (p_batch_datetime, 'YYYY/MM/DD HH24:MI:SS');
SELECT *
FROM igs_pr_spo_interface
WHERE user_id = p_user_id
AND TRUNC (batch_date) = TRUNC (p_batch_date)
AND ERROR_CODE IS NOT NULL
AND NVL (progression_outcome_type, '--') <> '-';
SELECT *
FROM igs_as_aio_interface
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND ERROR_CODE IS NOT NULL
AND ass_id IS NOT NULL;
'p_delete_rows => ' || p_delete_rows
);
p_delete_rows
);
p_delete_rows
);
p_delete_rows
);
UPDATE igs_as_ug_interface
SET error_code = pr_error_rows.error_code
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND (alternate_code = pr_error_rows.progression_outcome_type
OR (alternate_code IS NULL
AND pr_error_rows.progression_outcome_type IS NULL)
)
AND (person_number = pr_error_rows.person_number
OR anonymous_id = pr_error_rows.anonymous_id)
AND course_cd = pr_error_rows.course_cd
AND grading_period_cd = '-';
UPDATE igs_as_ug_interface
SET error_code = aio_error_rows.error_code
WHERE user_id = p_user_id
AND trunc(batch_date) = trunc(p_batch_date)
AND (person_number = aio_error_rows.person_number
OR anonymous_id = aio_error_rows.anonymous_id)
AND course_cd = aio_error_rows.course_cd
AND uoo_id = aio_error_rows.uoo_id
AND cal_type = aio_error_rows.cal_type
AND ci_sequence_number = aio_error_rows.ci_sequence_number
AND unit_class = aio_error_rows.unit_class
AND location_cd = aio_error_rows.location_cd
AND incomp_default_mark = aio_error_rows.ass_id
AND grading_period_cd = '-';
p_delete_rows IN VARCHAR2 DEFAULT 'Y'
) IS
--
v_person_id NUMBER (15);
SELECT ROWID,
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_level_achieved1,
credit_level_achieved2,
credit_pt_achieved1,
credit_pt_achieved2,
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,
type_of_year,
credit_value_yop3,
credit_value_yop4,
credit_level_achieved3,
credit_level_achieved4,
credit_pt_achieved3,
credit_pt_achieved4,
credit_level3,
credit_level4,
additional_sup_cost,
enh_fund_elig_cd,
disadv_uplift_factor,
year_stu
FROM igs_he_en_susa
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND unit_set_cd = cp_unit_set_cd
AND sequence_number = cp_sequence_number
AND us_version_number = cp_us_version_number;
SELECT spoi.user_id,
spoi.batch_date,
decode(spoi.person_number,'-',null,spoi.person_number) person_number,
decode(spoi.anonymous_id,'-',null,spoi.anonymous_id) anonymous_id,
spoi.course_cd,
spoi.progression_outcome_type,
spoi.comments,
spoi.error_code,
spoi.ROWID,
spoi.yop_grade,
spoi.yop_mark
FROM igs_pr_spo_interface spoi
WHERE spoi.user_id = p_user_id
AND trunc(spoi.batch_date) = trunc(p_batch_date)
AND NVL (spoi.progression_outcome_type, '--') <> '-';
SELECT 'X' spo_exists
FROM igs_pr_stdnt_pr_ou_all
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND prg_cal_type = cp_prg_cal_type
AND prg_ci_sequence_number = cp_prg_ci_sequence_number
AND progression_outcome_type = cp_progression_outcome_type;
UPDATE igs_pr_spo_interface
SET error_code = 'IGS_EN_PERSON_NO_RESP'
WHERE ROWID = v_spoi_rec.ROWID;
UPDATE igs_pr_spo_interface
SET error_code = v_error_code
WHERE ROWID = v_spoi_rec.ROWID;
SELECT igs_pr_spo_seq_num_s.NEXTVAL
INTO v_spo_sequence_number
FROM DUAL;
igs_pr_stdnt_pr_ou_pkg.insert_row (
x_rowid => v_rowid,
x_person_id => v_person_id,
x_course_cd => v_spoi_rec.course_cd,
x_sequence_number => v_spo_sequence_number,
x_prg_cal_type => v_prg_cal_type,
x_prg_ci_sequence_number => v_prg_ci_sequence_number,
x_rule_check_dt => NULL,
x_progression_rule_cat => NULL,
x_pra_sequence_number => NULL,
x_pro_sequence_number => NULL,
x_progression_outcome_type => v_spoi_rec.progression_outcome_type,
x_duration => NULL,
x_duration_type => NULL,
x_decision_status => 'PENDING',
x_decision_dt => NULL,
x_decision_org_unit_cd => NULL,
x_decision_ou_start_dt => NULL,
x_applied_dt => NULL,
x_show_cause_expiry_dt => NULL,
x_show_cause_dt => NULL,
x_show_cause_outcome_dt => NULL,
x_show_cause_outcome_type => NULL,
x_appeal_expiry_dt => NULL,
x_appeal_dt => NULL,
x_appeal_outcome_dt => NULL,
x_appeal_outcome_type => NULL,
x_encmb_course_group_cd => NULL,
x_restricted_enrolment_cp => NULL,
x_restricted_attendance_type => NULL,
x_comments => v_spoi_rec.comments,
x_show_cause_comments => NULL,
x_appeal_comments => NULL,
x_expiry_dt => NULL,
x_pro_pra_sequence_number => NULL,
x_mode => 'S',
x_org_id => v_org_id
);
UPDATE igs_pr_spo_interface
SET error_code = errbuf
WHERE rowid = v_spoi_rec.rowid;
igs_he_en_susa_pkg.insert_row (
x_mode => 'S',
x_rowid => v_he_rowid,
x_hesa_en_susa_id => v_hesa_en_susa_id,
x_person_id => v_person_id,
x_course_cd => v_spoi_rec.course_cd,
x_unit_set_cd => v_unit_set_cd,
x_us_version_number => v_us_version_number,
x_sequence_number => v_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 => v_spoi_rec.yop_grade,
x_mark => TO_NUMBER (v_spoi_rec.yop_mark),
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_calculated_fte => NULL,
x_fte_calc_type => NULL,
x_type_of_year => NULL,
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
);
UPDATE igs_pr_spo_interface
SET error_code = errbuf
WHERE rowid = v_spoi_rec.rowid;
igs_he_en_susa_pkg.update_row (
x_mode => 'S',
x_rowid => v_susa.ROWID,
x_hesa_en_susa_id => v_susa.hesa_en_susa_id,
x_person_id => v_susa.person_id,
x_course_cd => v_susa.course_cd,
x_unit_set_cd => v_susa.unit_set_cd,
x_us_version_number => v_susa.us_version_number,
x_sequence_number => v_susa.sequence_number,
x_new_he_entrant_cd => v_susa.new_he_entrant_cd,
x_term_time_accom => v_susa.term_time_accom,
x_disability_allow => v_susa.disability_allow,
x_additional_sup_band => v_susa.additional_sup_band,
x_sldd_discrete_prov => v_susa.sldd_discrete_prov,
x_study_mode => v_susa.study_mode,
x_study_location => v_susa.study_location,
x_fte_perc_override => v_susa.fte_perc_override,
x_franchising_activity => v_susa.franchising_activity,
x_completion_status => v_susa.completion_status,
x_good_stand_marker => v_susa.good_stand_marker,
x_complete_pyr_study_cd => v_susa.complete_pyr_study_cd,
x_credit_value_yop1 => v_susa.credit_value_yop1,
x_credit_value_yop2 => v_susa.credit_value_yop2,
x_credit_level_achieved1 => v_susa.credit_level_achieved1,
x_credit_level_achieved2 => v_susa.credit_level_achieved2,
x_credit_pt_achieved1 => v_susa.credit_pt_achieved1,
x_credit_pt_achieved2 => v_susa.credit_pt_achieved2,
x_credit_level1 => v_susa.credit_level1,
x_credit_level2 => v_susa.credit_level2,
x_grad_sch_grade => v_spoi_rec.yop_grade,
x_mark => TO_NUMBER (v_spoi_rec.yop_mark),
x_teaching_inst1 => v_susa.teaching_inst1,
x_teaching_inst2 => v_susa.teaching_inst2,
x_pro_not_taught => v_susa.pro_not_taught,
x_fundability_code => v_susa.fundability_code,
x_fee_eligibility => v_susa.fee_eligibility,
x_fee_band => v_susa.fee_band,
x_non_payment_reason => v_susa.non_payment_reason,
x_student_fee => v_susa.student_fee,
x_fte_intensity => v_susa.fte_intensity,
x_calculated_fte => v_susa.calculated_fte,
x_fte_calc_type => v_susa.fte_calc_type,
x_type_of_year => v_susa.type_of_year,
x_credit_value_yop3 => v_susa.credit_value_yop3,
x_credit_value_yop4 => v_susa.credit_value_yop4,
x_credit_level_achieved3 => v_susa.credit_level_achieved3,
x_credit_level_achieved4 => v_susa.credit_level_achieved4,
x_credit_pt_achieved3 => v_susa.credit_pt_achieved3,
x_credit_pt_achieved4 => v_susa.credit_pt_achieved4,
x_credit_level3 => v_susa.credit_level3,
x_credit_level4 => v_susa.credit_level4,
x_additional_sup_cost => v_susa.additional_sup_cost,
x_enh_fund_elig_cd => v_susa.enh_fund_elig_cd,
x_disadv_uplift_factor => v_susa.disadv_uplift_factor,
x_year_stu => v_susa.year_stu
);
UPDATE igs_pr_spo_interface
SET error_code = errbuf
WHERE rowid = v_spoi_rec.rowid;
fnd_request.submit_request ('IGS', 'IGSPRS04', NULL, NULL, FALSE, p_user_id, p_batch_datetime, p_delete_rows);
SELECT ec.upld_person_no_exist,
ec.upld_crs_not_enrolled
FROM igs_as_entry_conf ec
WHERE s_control_num = 1;
SELECT aip.person_id
FROM igs_as_anon_id_ps aip
WHERE aip.anonymous_id = p_anonymous_id
AND aip.course_cd = p_course_cd;
SELECT p.party_id
FROM hz_parties p
WHERE p.party_number = p_person_number;
SELECT spa.course_attempt_status,
spa.version_number
FROM igs_en_stdnt_ps_att_all spa
WHERE spa.person_id = cp_person_id
AND spa.course_cd = p_course_cd;
SELECT pot.s_progression_outcome_type
FROM igs_pr_ou_type pot
WHERE pot.progression_outcome_type = p_progression_outcome_type;
SELECT 'X'
FROM igs_pr_stdnt_pr_ou spo
WHERE spo.person_id = cp_person_id
AND spo.course_cd = p_course_cd
AND spo.progression_outcome_type = p_progression_outcome_type
AND spo.decision_status IN ('PENDING', 'APPROVED')
AND spo.prg_cal_type = cp_prg_cal_type
AND spo.prg_ci_sequence_number = cp_prg_ci_sequence_number;
SELECT 'X'
FROM igs_pr_s_crv_prg_cal scpc
WHERE scpc.course_cd = p_course_cd
AND scpc.version_number = cp_version_number;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_pr_s_crv_prg_cal scpc,
igs_ca_inst ci,
igs_ca_stat cs
WHERE scpc.course_cd = p_course_cd
AND scpc.version_number = cp_version_number
AND ci.cal_type = scpc.prg_cal_type
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci.start_dt < SYSDATE
--AND ci.end_dt = (SELECT MAX(ci.end_dt)
AND EXISTS ( SELECT 'X'
FROM igs_ca_inst_rel cir,
igs_en_su_attempt sua
WHERE cir.sup_cal_type = ci.cal_type
AND cir.sup_ci_sequence_number = ci.sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND sua.person_id = cp_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
ORDER BY ci.end_dt DESC;
SELECT 'X'
FROM igs_pr_s_ou_prg_cal sopc
WHERE igs_pr_gen_001.prgp_get_crv_cmt (p_course_cd, cp_version_number, sopc.org_unit_cd, sopc.ou_start_dt) = 'Y';
SELECT ci.cal_type,
ci.sequence_number
FROM igs_pr_s_ou_prg_cal sopc,
igs_ca_inst ci,
igs_ca_stat cs
WHERE igs_pr_gen_001.prgp_get_crv_cmt (p_course_cd, cp_version_number, sopc.org_unit_cd, sopc.ou_start_dt) = 'Y'
AND ci.cal_type = sopc.prg_cal_type
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci.start_dt < SYSDATE
--AND ci.end_dt = (SELECT MAX(ci.end_dt)
AND EXISTS ( SELECT 'X'
FROM igs_ca_inst_rel cir,
igs_en_su_attempt sua
WHERE cir.sup_cal_type = ci.cal_type
AND cir.sup_ci_sequence_number = ci.sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND sua.person_id = cp_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
ORDER BY ci.end_dt DESC;
SELECT ci.cal_type,
ci.sequence_number
FROM igs_pr_s_prg_cal spc,
igs_ca_inst ci,
igs_ca_stat cs
WHERE spc.s_control_num = 1
AND ci.cal_type = spc.prg_cal_type
AND ci.cal_status = cs.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci.start_dt < SYSDATE
--AND ci.end_dt = (SELECT MAX(ci.end_dt)
AND EXISTS ( SELECT 'X'
FROM igs_ca_inst_rel cir,
igs_en_su_attempt sua
WHERE cir.sup_cal_type = ci.cal_type
AND cir.sup_ci_sequence_number = ci.sequence_number
AND cir.sub_cal_type = sua.cal_type
AND cir.sub_ci_sequence_number = sua.ci_sequence_number
AND sua.person_id = cp_person_id
AND sua.course_cd = p_course_cd
AND sua.unit_attempt_status IN ('ENROLLED', 'COMPLETED'))
ORDER BY ci.end_dt DESC;
SELECT hpoous.grading_schema_cd,
hpoous.gs_version_number,
yop.unit_set_cd,
yop.us_version_number,
yop.sequence_number
FROM igs_en_susa_year_v yop,
igs_en_stdnt_ps_att_all spa,
igs_ps_ofr_opt_all coo,
igs_he_poous_all hpoous
WHERE yop.person_id = cp_person_id
AND yop.course_cd = cp_course_cd
AND yop.completion_dt IS NULL
AND yop.end_dt IS NULL
AND yop.person_id = spa.person_id
AND yop.course_cd = spa.course_cd
AND spa.coo_id = coo.coo_id
AND hpoous.unit_set_cd = yop.unit_set_cd
AND hpoous.us_version_number = yop.us_version_number
AND hpoous.course_cd = coo.course_cd
AND hpoous.crv_version_number = coo.version_number
AND hpoous.cal_type = coo.cal_type
AND hpoous.location_cd = coo.location_cd
AND hpoous.attendance_type = coo.attendance_type
AND hpoous.attendance_mode = coo.attendance_mode;
SELECT 'Y' grade_found
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND gsg.grade = cp_grade;
SELECT grade
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number
AND system_only_ind = 'N'
AND cp_marks BETWEEN gsg.lower_mark_range AND gsg.upper_mark_range;
SELECT MIN (gsg.lower_mark_range) min_lower_mark_range,
MAX (gsg.upper_mark_range) max_upper_mark_range
FROM igs_as_grd_sch_grade gsg
WHERE gsg.grading_schema_cd = cp_grading_schema_cd
AND gsg.version_number = cp_gs_version_number;