The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_b_award_updated BOOLEAN := FALSE;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = cp_type
AND lookup_code = cp_code;
SELECT show_on_bill
FROM igf_aw_fund_mast
WHERE fund_id = cp_fund_id;
SELECT alternate_code,
start_dt,
end_dt
FROM igs_ca_inst
WHERE cal_type = cp_c_cal_type
AND sequence_number = cp_n_sequence_number;
SELECT accepted_amt
FROM igf_aw_award
WHERE award_id = cp_n_award_id;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT NVL(disb_seq_num,0) disb_seq_num
FROM igf_db_awd_disb_dtl
WHERE award_id = cp_award_id AND
disb_num = cp_disb_num;
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = cp_award_id AND
disb_num = cp_disb_num;
igf_db_awd_disb_dtl_pkg.insert_row( x_rowid => g_rowid,
x_award_id => disb_dtl_rec.award_id ,
x_disb_num => disb_dtl_rec.disb_num ,
x_disb_seq_num => disb_dtl_rec.disb_seq_num ,
x_disb_gross_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_gross_amt) ,
x_fee_1 => disb_dtl_rec.fee_1 ,
x_fee_2 => disb_dtl_rec.fee_2 ,
x_disb_net_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_net_amt) ,
x_disb_adj_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_adj_amt) ,
x_disb_date => disb_dtl_rec.disb_date ,
x_fee_paid_1 => disb_dtl_rec.fee_paid_1 ,
x_fee_paid_2 => disb_dtl_rec.fee_paid_2 ,
x_disb_activity => disb_dtl_rec.disb_activity ,
x_disb_batch_id => NULL,
x_disb_ack_date => NULL,
x_booking_batch_id => NULL,
x_booked_date => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_sf_status => disb_dtl_rec.sf_status ,
x_sf_status_date => disb_dtl_rec.sf_status_date ,
x_sf_invoice_num => disb_dtl_rec.sf_invoice_num ,
x_spnsr_credit_id => disb_dtl_rec.spnsr_credit_id ,
x_spnsr_charge_id => disb_dtl_rec.spnsr_charge_id ,
x_sf_credit_id => disb_dtl_rec.sf_credit_id ,
x_error_desc => disb_dtl_rec.error_desc ,
x_mode => 'R',
x_notification_date => disb_dtl_rec.notification_date,
x_interest_rebate_amt => NULL,
x_ld_cal_type => get_adisb_rec.ld_cal_type,
x_ld_sequence_number => get_adisb_rec.ld_sequence_number
);
SELECT spstd.*, fmast.fund_code
FROM igf_sp_stdnt_rel_all spstd,
igf_aw_fund_mast_all fmast
WHERE spstd.spnsr_stdnt_id = cp_spnsr_stdnt_id
AND fmast.fund_id = spstd.fund_id;
SELECT *
FROM igf_sp_std_fc
WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id
AND fee_cls_id = NVL(cp_fee_cls_id,fee_cls_id);
SELECT *
FROM igf_sp_std_prg
WHERE fee_cls_id = cp_fee_cls_id;
SELECT *
FROM igf_sp_std_unit
WHERE fee_cls_prg_id = cp_fee_cls_prg_id;
SELECT 'x'
FROM igs_en_stdnt_ps_att psatt
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND version_number = cp_course_version_number
AND course_attempt_status IN ('ENROLLED','INACTIVE','DISCONTIN')
AND EXISTS ( SELECT 1
FROM igs_en_su_attempt_all sua
WHERE sua.person_id = psatt.person_id
AND sua.course_cd = psatt.course_cd
AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED')
AND (cal_type, ci_sequence_number) IN ( SELECT teach_cal_type, teach_ci_sequence_number
FROM igs_ca_load_to_teach_v
WHERE load_cal_type = cp_v_load_cal_type
AND load_ci_sequence_number = cp_n_load_seq_num)
);
SELECT NVL(max(disb_num),0) + 1
FROM igf_aw_awd_disb
WHERE award_id = cp_award_id;
SELECT *
FROM igf_sp_stdnt_rel
WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
SELECT *
FROM igf_sp_std_fc
WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
igf_aw_award_pkg.insert_row(x_rowid => g_rowid ,
x_award_id => l_award_id ,
x_fund_id => rec_stdnt_rel.fund_id,
x_base_id => rec_stdnt_rel.base_id,
x_offered_amt => 0 ,
x_accepted_amt => 0 ,
x_paid_amt => NULL,
x_packaging_type => NULL,
x_batch_id => NULL,
x_manual_update => 'N' ,
x_rules_override => NULL,
x_award_date => TRUNC(SYSDATE),
x_award_status => 'ACCEPTED' ,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_rvsn_id => NULL,
x_alt_pell_schedule =>NULL,
x_mode => 'R',
x_award_number_txt => NULL,
x_legacy_record_flag => NULL,
x_lock_award_flag => 'N',
x_app_trans_num_txt => NULL,
x_awd_proc_status_code => NULL,
x_notification_status_code => NULL,
x_notification_status_date => NULL,
x_publish_in_ss_flag => 'N'
);
igf_aw_awd_disb_pkg.insert_row(
x_rowid => g_rowid ,
x_award_id => l_award_id ,
x_disb_num => l_disb_num ,
x_tp_cal_type => NULL ,
x_tp_sequence_number => NULL ,
x_disb_gross_amt => l_disb_gross_amt ,
x_fee_1 => NULL ,
x_fee_2 => NULL ,
x_disb_net_amt => l_disb_gross_amt ,
x_disb_date => TRUNC(SYSDATE) ,
x_trans_type => p_award_type ,
x_elig_status => NULL ,
x_elig_status_date => NULL ,
x_affirm_flag => NULL ,
x_hold_rel_ind => NULL ,
x_manual_hold_ind => NULL ,
x_disb_status => NULL ,
x_disb_status_date => NULL ,
x_late_disb_ind => NULL ,
x_fund_dist_mthd => NULL ,
x_prev_reported_ind => NULL ,
x_fund_release_date => NULL ,
x_fund_status => NULL ,
x_fund_status_date => NULL ,
x_fee_paid_1 => NULL ,
x_fee_paid_2 => NULL ,
x_cheque_number => NULL ,
x_ld_cal_type => p_ld_cal_type ,
x_ld_sequence_number => p_ld_sequence_number ,
x_disb_accepted_amt => l_disb_gross_amt ,
x_disb_paid_amt => NULL ,
x_rvsn_id => NULL ,
x_int_rebate_amt => NULL ,
x_force_disb => NULL ,
x_min_credit_pts => NULL ,
x_disb_exp_dt => NULL ,
x_verf_enfr_dt => NULL ,
x_fee_class => rec_std_fc.fee_class ,
x_show_on_bill => l_include_as_plncrd , --for bug#2293676.
x_mode => 'R' ,
x_attendance_type_code => NULL ,
x_base_attendance_type_code => NULL,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
igf_aw_award_pkg.insert_row(
x_rowid => g_rowid ,
x_award_id => l_award_id ,
x_fund_id => rec_stdnt_rel.fund_id,
x_base_id => rec_stdnt_rel.base_id,
x_offered_amt => l_n_tot_spns_amt ,
x_accepted_amt => l_n_tot_spns_amt ,
x_paid_amt => NULL,
x_packaging_type => NULL,
x_batch_id => NULL,
x_manual_update => 'N' ,
x_rules_override => NULL,
x_award_date => TRUNC(SYSDATE),
x_award_status => 'ACCEPTED' ,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_rvsn_id => NULL,
x_alt_pell_schedule =>NULL,
x_mode => 'R',
x_award_number_txt => NULL,
x_legacy_record_flag => NULL,
x_lock_award_flag => 'N',
x_app_trans_num_txt => NULL,
x_awd_proc_status_code => NULL,
x_notification_status_code => NULL,
x_notification_status_date => NULL,
x_publish_in_ss_flag => 'N'
);
igf_aw_awd_disb_pkg.insert_row(
x_rowid => g_rowid ,
x_award_id => l_award_id ,
x_disb_num => l_disb_num ,
x_tp_cal_type => NULL ,
x_tp_sequence_number => NULL ,
x_disb_gross_amt => l_n_tot_spns_amt ,
x_fee_1 => NULL ,
x_fee_2 => NULL ,
x_disb_net_amt => l_n_tot_spns_amt ,
x_disb_date => TRUNC(SYSDATE) ,
x_trans_type => p_award_type ,
x_elig_status => NULL ,
x_elig_status_date => NULL ,
x_affirm_flag => NULL ,
x_hold_rel_ind => NULL ,
x_manual_hold_ind => NULL ,
x_disb_status => NULL ,
x_disb_status_date => NULL ,
x_late_disb_ind => NULL ,
x_fund_dist_mthd => NULL ,
x_prev_reported_ind => NULL ,
x_fund_release_date => NULL ,
x_fund_status => NULL ,
x_fund_status_date => NULL ,
x_fee_paid_1 => NULL ,
x_fee_paid_2 => NULL ,
x_cheque_number => NULL ,
x_ld_cal_type => p_ld_cal_type ,
x_ld_sequence_number => p_ld_sequence_number ,
x_disb_accepted_amt => l_n_tot_spns_amt ,
x_disb_paid_amt => NULL ,
x_rvsn_id => NULL ,
x_int_rebate_amt => NULL ,
x_force_disb => NULL ,
x_min_credit_pts => NULL ,
x_disb_exp_dt => NULL ,
x_verf_enfr_dt => NULL ,
x_fee_class => NULL ,
x_show_on_bill => l_include_as_plncrd , --for bug#2293676.
x_mode => 'R' ,
x_attendance_type_code => NULL ,
x_base_attendance_type_code => NULL,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
CURSOR c_manual_update (cp_base_id igf_sp_stdnt_rel.base_id%TYPE,
cp_fund_id igf_sp_stdnt_rel.fund_id%TYPE,
cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
)
IS
SELECT NVL(manual_update,'N'), award_id
FROM igf_aw_award awd
WHERE base_id = cp_base_id
AND fund_id = cp_fund_id
AND EXISTS (SELECT '1'
FROM igf_aw_awd_disb disb
WHERE disb.award_id = awd.award_id
AND disb.ld_cal_type = cp_ld_cal_type
AND disb.ld_sequence_number = cp_ld_sequence_number);
l_manual_update igf_aw_award.manual_update%TYPE;
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = cp_award_id
AND (
(fee_class = cp_v_fee_class AND cp_v_fee_class IS NOT NULL AND fee_class IS NOT NULL)
OR
(cp_v_fee_class IS NULL)
)
AND (
(cp_trans_type IS NOT NULL AND trans_type = cp_trans_type)
OR
(cp_trans_type IS NULL AND trans_type IN ('A','P'))
);
SELECT *
FROM igf_sp_std_fc
WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
OPEN c_manual_update (p_base_id,
p_fund_id,
p_ld_cal_type,
p_ld_sequence_number
);
FETCH c_manual_update INTO l_manual_update,l_award_id;
EXIT WHEN c_manual_update%NOTFOUND;
l_rec_count := c_manual_update%ROWCOUNT;
IF l_manual_update = 'Y' AND p_award_type = 'A' THEN
-- fetch record from igf_aw_awd_disb based on award id obtained and update only if
-- the award type is A
g_b_msg_logged := FALSE;
igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id ,
x_award_id => rec_aw_awd_disb.award_id ,
x_disb_num => rec_aw_awd_disb.disb_num ,
x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
x_disb_gross_amt => rec_aw_awd_disb.disb_gross_amt ,
x_fee_1 => rec_aw_awd_disb.fee_1 ,
x_fee_2 => rec_aw_awd_disb.fee_2 ,
x_disb_net_amt => rec_aw_awd_disb.disb_net_amt ,
x_disb_date => rec_aw_awd_disb.disb_date ,
x_trans_type => 'A' ,
x_elig_status => rec_aw_awd_disb.elig_status ,
x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
x_disb_status => rec_aw_awd_disb.disb_status ,
x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
x_fund_status => rec_aw_awd_disb.fund_status ,
x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
x_cheque_number => rec_aw_awd_disb.cheque_number ,
x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
x_disb_accepted_amt => rec_aw_awd_disb.disb_accepted_amt ,
x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
x_force_disb => rec_aw_awd_disb.force_disb ,
x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
x_fee_class => rec_aw_awd_disb.fee_class ,
x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
x_mode => 'R' ,
x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
x_change_type_code => rec_aw_awd_disb.change_type_code ,
x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
);
fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
ELSIF l_manual_update = 'N' AND p_n_total_spnsr_amt IS NOT NULL AND p_award_type = 'A' THEN
-- When invoked in the Actual Mode then get all the Planned Disbursement Records and check if the
-- eligibility satisfies. If satisfies then update the status to Actual.
IF NOT check_eligibility(p_person_id => p_person_id,
p_min_att_type => p_min_attendance_type,
p_min_credit_points => p_min_credit_points ,
p_ld_cal_type => p_ld_cal_type,
p_ld_sequence_number => p_ld_sequence_number ) THEN
log_parameters(g_v_disb_fee_class,NULL);
igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id ,
x_award_id => rec_aw_awd_disb.award_id ,
x_disb_num => rec_aw_awd_disb.disb_num ,
x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
x_disb_gross_amt => l_n_tot_spns_amt ,
x_fee_1 => rec_aw_awd_disb.fee_1 ,
x_fee_2 => rec_aw_awd_disb.fee_2 ,
x_disb_net_amt => l_n_tot_spns_amt ,
x_disb_date => rec_aw_awd_disb.disb_date ,
x_trans_type => 'A' ,
x_elig_status => rec_aw_awd_disb.elig_status ,
x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
x_disb_status => rec_aw_awd_disb.disb_status ,
x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
x_fund_status => rec_aw_awd_disb.fund_status ,
x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
x_cheque_number => rec_aw_awd_disb.cheque_number ,
x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
x_disb_accepted_amt => l_n_tot_spns_amt ,
x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
x_force_disb => rec_aw_awd_disb.force_disb ,
x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
x_fee_class => rec_aw_awd_disb.fee_class ,
x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
x_mode => 'R' ,
x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
x_change_type_code => rec_aw_awd_disb.change_type_code ,
x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
);
ELSIF l_manual_update = 'N' AND p_n_total_spnsr_amt IS NULL THEN
-- When the Award Type is provided as "A" then get all the disbursement records
-- that are even in the Planned State.
-- Otherwise get only the Planned Records.
IF p_award_type = 'A' THEN
l_v_award_type := NULL;
g_b_award_updated := FALSE;
fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
fnd_message.set_name('IGF','IGF_SP_UPDATE_AWARD');
fnd_message.set_name('IGF','IGF_SP_UPDATE_DISB');
igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id,
x_award_id => rec_aw_awd_disb.award_id ,
x_disb_num => rec_aw_awd_disb.disb_num ,
x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
x_disb_gross_amt => l_disb_gross_amt ,
x_fee_1 => rec_aw_awd_disb.fee_1 ,
x_fee_2 => rec_aw_awd_disb.fee_2 ,
x_disb_net_amt => l_disb_gross_amt ,
x_disb_date => rec_aw_awd_disb.disb_date,
x_trans_type => NVL(l_v_award_type,'A'),
x_elig_status => rec_aw_awd_disb.elig_status ,
x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
x_disb_status => rec_aw_awd_disb.disb_status ,
x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
x_fund_status => rec_aw_awd_disb.fund_status ,
x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
x_cheque_number => rec_aw_awd_disb.cheque_number ,
x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
x_disb_accepted_amt => l_disb_gross_amt ,
x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
x_force_disb => rec_aw_awd_disb.force_disb ,
x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
x_fee_class => rec_aw_awd_disb.fee_class ,
x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
x_mode => 'R' ,
x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
x_change_type_code => rec_aw_awd_disb.change_type_code ,
x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
);
igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id,
x_award_id => rec_aw_awd_disb.award_id ,
x_disb_num => rec_aw_awd_disb.disb_num ,
x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
x_disb_gross_amt => rec_aw_awd_disb.disb_gross_amt ,
x_fee_1 => rec_aw_awd_disb.fee_1 ,
x_fee_2 => rec_aw_awd_disb.fee_2 ,
x_disb_net_amt => rec_aw_awd_disb.disb_net_amt ,
x_disb_date => rec_aw_awd_disb.disb_date,
x_trans_type => 'A',
x_elig_status => rec_aw_awd_disb.elig_status ,
x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
x_disb_status => rec_aw_awd_disb.disb_status ,
x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
x_fund_status => rec_aw_awd_disb.fund_status ,
x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
x_cheque_number => rec_aw_awd_disb.cheque_number ,
x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
x_disb_accepted_amt => rec_aw_awd_disb.disb_accepted_amt ,
x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
x_force_disb => rec_aw_awd_disb.force_disb ,
x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
x_fee_class => rec_aw_awd_disb.fee_class ,
x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
x_mode => 'R' ,
x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
x_change_type_code => rec_aw_awd_disb.change_type_code ,
x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
);
fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
g_b_award_updated := TRUE;
fnd_message.set_name('IGF','IGF_SP_UPDATE_AWARD');
igf_aw_awd_disb_pkg.insert_row(x_rowid => g_rowid,
x_award_id => l_award_id,
x_disb_num => l_n_disb_num,
x_tp_cal_type => NULL,
x_tp_sequence_number => NULL,
x_disb_gross_amt => l_disb_gross_amt,
x_fee_1 => NULL,
x_fee_2 => NULL,
x_disb_net_amt => l_disb_gross_amt,
x_disb_date => TRUNC(SYSDATE),
x_trans_type => p_award_type,
x_elig_status => NULL,
x_elig_status_date => NULL,
x_affirm_flag => NULL,
x_hold_rel_ind => NULL,
x_manual_hold_ind => NULL,
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => NULL,
x_fund_dist_mthd => NULL,
x_prev_reported_ind => NULL,
x_fund_release_date => NULL,
x_fund_status => NULL,
x_fund_status_date => NULL,
x_fee_paid_1 => NULL,
x_fee_paid_2 => NULL,
x_cheque_number => NULL,
x_ld_cal_type => p_ld_cal_type,
x_ld_sequence_number => p_ld_sequence_number,
x_disb_accepted_amt => l_disb_gross_amt,
x_disb_paid_amt => NULL,
x_rvsn_id => NULL,
x_int_rebate_amt => NULL,
x_force_disb => NULL,
x_min_credit_pts => NULL,
x_disb_exp_dt => NULL,
x_verf_enfr_dt => NULL,
x_fee_class => rec_c_igf_sp_std_fc.fee_class,
x_show_on_bill => l_include_as_plncrd,
x_mode => 'R',
x_attendance_type_code => NULL,
x_base_attendance_type_code => NULL,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
IF (g_b_award_updated) THEN
i := i+1;
g_b_award_updated := FALSE;
fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
END IF;-- check for manual update
CLOSE c_manual_update;
IF c_manual_update%ISOPEN THEN
CLOSE c_manual_update;
SELECT rel.*,
fund.fund_code,
fund.description fund_desc,
fund.fee_type
FROM igf_sp_stdnt_rel rel,
igf_aw_fund_mast fund
WHERE rel.fund_id = fund.fund_id
AND rel.fund_id = cp_fund_id
AND fund.discontinue_fund <> cp_v_disc_fund
AND (
(p_ld_cal_type IS NOT NULL AND rel.ld_cal_type = p_ld_cal_type)
OR
(p_ld_cal_type IS NULL)
)
AND (
(p_ld_sequence_number IS NOT NULL AND rel.ld_sequence_number = p_ld_sequence_number)
OR
(p_ld_sequence_number IS NULL)
)
AND (
(cp_person_id IS NOT NULL AND person_id = cp_person_id)
OR
(cp_person_id IS NULL)
)
AND EXISTS ( SELECT '1'
FROM igf_ap_fa_base_rec
WHERE base_id = rel.base_id
AND person_id = rel.person_id
AND ci_cal_type = cp_awd_cal_type
AND ci_sequence_number = cp_awd_ci_sequence_number
)
ORDER BY fund.fund_code;
SELECT fmast.*
FROM igf_aw_fund_mast fmast ,
igf_aw_fund_cat fcat
WHERE fmast.fund_code = fcat.fund_code
AND (fmast.fund_id = cp_fund_id OR cp_fund_id IS NULL)
AND fmast.ci_cal_type = cp_cal_type
AND fmast.ci_sequence_number = cp_sequence_number
AND fcat.sys_fund_type = cp_v_sys_fund_type
AND fmast.discontinue_fund <> cp_v_disc_fund
ORDER BY fund_id;
SELECT group_cd, closed_ind
FROM igs_pe_all_persid_group_v
WHERE group_id = cp_n_pers_grp_id;
SELECT fund_code, discontinue_fund
FROM igf_aw_fund_mast
WHERE fund_id = cp_n_fund_id;