[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
rfms.*
FROM
igf_gr_rfms rfms
WHERE
rfms.orig_action_code = 'R' AND
rfms.base_id = NVL(l_base_id,rfms.base_id) AND
rfms.ci_cal_type = l_ci_cal_type AND
rfms.ci_sequence_number = l_ci_sequence_number AND
rfms.rep_pell_id = cp_reporting_pell AND
rfms.attending_campus_id = NVL(cp_attending_pell, rfms.attending_campus_id)
FOR UPDATE OF orig_action_code NOWAIT;
PROCEDURE update_orig_rec(p_rfms_rec cur_rfms%ROWTYPE,
p_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE) IS
--
-- This procedure updates the rfms origination records
-- which are being sent to the external processor
-- orig_action_code is updated to 'S' to indicate that
-- the record has been processed and sent
-- Update the RFMB_ID field in IGFGR004
--
BEGIN
igf_gr_rfms_pkg.update_row(
x_rowid => p_rfms_rec.row_id,
x_origination_id => p_rfms_rec.origination_id,
x_ci_cal_type => p_rfms_rec.ci_cal_type,
x_ci_sequence_number => p_rfms_rec.ci_sequence_number,
x_base_id => p_rfms_rec.base_id,
x_award_id => p_rfms_rec.award_id,
x_rfmb_id => p_rfmb_id,
x_sys_orig_ssn => p_rfms_rec.sys_orig_ssn,
x_sys_orig_name_cd => p_rfms_rec.sys_orig_name_cd,
x_transaction_num => p_rfms_rec.transaction_num,
x_efc => p_rfms_rec.efc,
x_ver_status_code => p_rfms_rec.ver_status_code,
x_secondary_efc => p_rfms_rec.secondary_efc,
x_secondary_efc_cd => p_rfms_rec.secondary_efc_cd,
x_pell_amount => p_rfms_rec.pell_amount,
x_pell_profile => p_rfms_rec.pell_profile,
x_enrollment_status => p_rfms_rec.enrollment_status,
x_enrollment_dt => p_rfms_rec.enrollment_dt,
x_coa_amount => p_rfms_rec.coa_amount,
x_academic_calendar => p_rfms_rec.academic_calendar,
x_payment_method => p_rfms_rec.payment_method,
x_total_pymt_prds => p_rfms_rec.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => p_rfms_rec.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => p_rfms_rec.attending_campus_id,
x_est_disb_dt1 => p_rfms_rec.est_disb_dt1,
x_orig_action_code => 'S',
x_orig_status_dt => p_rfms_rec.orig_status_dt,
x_orig_ed_use_flags => p_rfms_rec.orig_ed_use_flags,
x_ft_pell_amount => p_rfms_rec.ft_pell_amount,
x_prev_accpt_efc => p_rfms_rec.prev_accpt_efc,
x_prev_accpt_tran_no => p_rfms_rec.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => p_rfms_rec.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => p_rfms_rec.prev_accpt_coa,
x_orig_reject_code => p_rfms_rec.orig_reject_code,
x_wk_inst_time_calc_pymt => p_rfms_rec.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => p_rfms_rec.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => p_rfms_rec.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => p_rfms_rec.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => p_rfms_rec.inst_cross_ref_cd,
x_low_tution_fee => p_rfms_rec.low_tution_fee,
x_rec_source => p_rfms_rec.rec_source,
x_pending_amount => p_rfms_rec.pending_amount,
x_mode => 'R',
x_birth_dt => p_rfms_rec.birth_dt,
x_last_name => p_rfms_rec.last_name,
x_first_name => p_rfms_rec.first_name,
x_middle_name => p_rfms_rec.middle_name,
x_current_ssn => p_rfms_rec.current_ssn,
x_legacy_record_flag => NULL,
x_reporting_pell_cd => p_rfms_rec.rep_pell_id,
x_rep_entity_id_txt => p_rfms_rec.rep_entity_id_txt,
x_atd_entity_id_txt => p_rfms_rec.atd_entity_id_txt,
x_note_message => p_rfms_rec.note_message,
x_full_resp_code => p_rfms_rec.full_resp_code,
x_document_id_txt => p_rfms_rec.document_id_txt );
fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.UPDATE_ORIG_REC');
END update_orig_rec;
SELECT disb_dt
FROM igf_gr_rfms_disb
WHERE origination_id = l_orig_id
ORDER BY disb_ref_num;
update_orig_rec(p_rfms_rec,p_rfmb_id);
fnd_message.set_name('IGF','IGF_GR_RECORDS_UPDATED');
SELECT isir.transaction_num
FROM igf_ap_isir_matched isir
WHERE isir.base_id = cp_base_id
AND isir.payment_isir = 'Y' ;
SELECT 'Y'
FROM igf_gr_report_pell rep,
igf_gr_attend_pell att
WHERE rep.rcampus_id = att.rcampus_id
AND rep.ci_cal_type = cp_ci_cal_type
AND rep.ci_sequence_number = cp_ci_sequence_number
AND rep.reporting_pell_cd = cp_reporting_pell
AND att.attending_pell_cd = cp_attending_pell;
SELECT base_attendance_type_code
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id
GROUP BY base_attendance_type_code;
SELECT sum(disb_amt) disb_amt_tot
FROM igf_gr_rfms_disb
WHERE origination_id = cp_origination_id;
SELECT DECODE( accepted_amt,0,offered_amt,NULL,offered_amt,accepted_amt) pell_award
FROM igf_aw_award awd
WHERE awd.award_id = cp_award_id
AND awd.award_status IN ('OFFERED','ACCEPTED');
OPEN refcur_persid_check FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING rfms_rec.base_id,p_persid_grp;
OPEN refcur_persid_check FOR 'SELECT 1
FROM igf_ap_fa_base_rec fabase
WHERE fabase.base_id = :base_id
AND fabase.person_id in ( '||lv_sql_stmt||') ' USING rfms_rec.base_id;
SELECT
rfmb_id
FROM
igf_gr_rfms_batch
WHERE batch_id = p_batch_id;
SELECT rfms.*
FROM
igf_gr_rfms rfms
WHERE
rfms.origination_id = l_origination_id
FOR UPDATE OF origination_id NOWAIT;
SELECT
record_data
FROM
igf_gr_load_file_t
WHERE
gldr_id BETWEEN 2 AND (l_last_gldr_id - 1) AND
file_type = 'GR_RFMS_ORIG';
igf_gr_gen.insert_sys_holds(get_pell_rec.award_id,NULL,'PELL');
igf_gr_rfms_pkg.update_row (
x_rowid => get_pell_rec.row_id,
x_origination_id => l_rfms.origination_id,
x_ci_cal_type => l_rfms.ci_cal_type,
x_ci_sequence_number => l_rfms.ci_sequence_number,
x_base_id => l_rfms.base_id,
x_award_id => l_rfms.award_id,
x_rfmb_id => l_rfms.rfmb_id,
x_sys_orig_ssn => l_rfms.sys_orig_ssn,
x_sys_orig_name_cd => l_rfms.sys_orig_name_cd,
x_transaction_num => l_rfms.transaction_num,
x_efc => l_rfms.efc,
x_ver_status_code => l_rfms.ver_status_code,
x_secondary_efc => l_rfms.secondary_efc,
x_secondary_efc_cd => l_rfms.secondary_efc_cd,
x_pell_amount => l_rfms.pell_amount,
x_pell_profile => l_rfms.pell_profile,
x_enrollment_status => l_rfms.enrollment_status,
x_enrollment_dt => l_rfms.enrollment_dt,
x_coa_amount => l_rfms.coa_amount,
x_academic_calendar => l_rfms.academic_calendar,
x_payment_method => l_rfms.payment_method,
x_total_pymt_prds => l_rfms.total_pymt_prds,
x_incrcd_fed_pell_rcp_cd => l_rfms.incrcd_fed_pell_rcp_cd,
x_attending_campus_id => l_rfms.attending_campus_id,
x_est_disb_dt1 => l_rfms.est_disb_dt1,
x_orig_action_code => l_rfms.orig_action_code,
x_orig_status_dt => l_rfms.orig_status_dt,
x_orig_ed_use_flags => l_rfms.orig_ed_use_flags,
x_ft_pell_amount => l_rfms.ft_pell_amount,
x_prev_accpt_efc => l_rfms.prev_accpt_efc,
x_prev_accpt_tran_no => l_rfms.prev_accpt_tran_no,
x_prev_accpt_sec_efc_cd => l_rfms.prev_accpt_sec_efc_cd,
x_prev_accpt_coa => l_rfms.prev_accpt_coa,
x_orig_reject_code => l_rfms.orig_reject_code,
x_wk_inst_time_calc_pymt => l_rfms.wk_inst_time_calc_pymt,
x_wk_int_time_prg_def_yr => l_rfms.wk_int_time_prg_def_yr,
x_cr_clk_hrs_prds_sch_yr => l_rfms.cr_clk_hrs_prds_sch_yr,
x_cr_clk_hrs_acad_yr => l_rfms.cr_clk_hrs_acad_yr,
x_inst_cross_ref_cd => l_rfms.inst_cross_ref_cd,
x_low_tution_fee => l_rfms.low_tution_fee,
x_rec_source => l_rfms.rec_source,
x_pending_amount => l_rfms.pending_amount,
x_mode => 'R',
x_birth_dt => l_rfms.birth_dt,
x_last_name => l_rfms.last_name,
x_first_name => l_rfms.first_name,
x_middle_name => l_rfms.middle_name,
x_current_ssn => l_rfms.current_ssn,
x_legacy_record_flag => NULL,
x_reporting_pell_cd => l_rfms.rep_pell_id,
x_rep_entity_id_txt => get_pell_rec.rep_entity_id_txt,
x_atd_entity_id_txt => get_pell_rec.atd_entity_id_txt,
x_note_message => get_pell_rec.note_message,
x_full_resp_code => get_pell_rec.full_resp_code,
x_document_id_txt => get_pell_rec.document_id_txt );
SELECT group_cd group_name
FROM igs_pe_persid_group_all
WHERE group_id = cp_persid_grp;