[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) common_terms
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id;
SELECT COUNT(*) common_terms
FROM igf_aw_awd_dist_plans adplans,
igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND terms.ld_cal_type = aprd.ld_cal_type
AND terms.ld_sequence_number = terms.ld_sequence_number
AND aprd.award_prd_cd = p_awd_prd_code
AND adplans.adplans_id = terms.adplans_id
AND adplans.cal_type = aprd.ci_cal_type
AND adplans.sequence_number = aprd.ci_sequence_number;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast
WHERE fcat.fund_code = fmast.fund_code
AND fmast.fund_id = p_fund_id;
SELECT 'X'
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id;
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = cp_award_id AND
trans_type <> 'C' AND
disb_num > cp_disb_num;
igf_aw_awd_disb_pkg.update_row(
x_rowid => disb_cancel_rec.row_id,
x_award_id => disb_cancel_rec.award_id,
x_disb_num => disb_cancel_rec.disb_num,
x_tp_cal_type => disb_cancel_rec.tp_cal_type,
x_tp_sequence_number => disb_cancel_rec.tp_sequence_number,
x_disb_gross_amt => 0,
x_fee_1 => disb_cancel_rec.fee_1,
x_fee_2 => disb_cancel_rec.fee_2,
x_disb_net_amt => 0,
x_disb_date => disb_cancel_rec.disb_date,
x_trans_type => 'C',
x_elig_status => disb_cancel_rec.elig_status,
x_elig_status_date => disb_cancel_rec.elig_status_date,
x_affirm_flag => disb_cancel_rec.affirm_flag,
x_hold_rel_ind => disb_cancel_rec.hold_rel_ind,
x_manual_hold_ind => disb_cancel_rec.manual_hold_ind,
x_disb_status => disb_cancel_rec.disb_status,
x_disb_status_date => disb_cancel_rec.disb_status_date,
x_late_disb_ind => disb_cancel_rec.late_disb_ind,
x_fund_dist_mthd => disb_cancel_rec.fund_dist_mthd,
x_prev_reported_ind => disb_cancel_rec.prev_reported_ind,
x_fund_release_date => disb_cancel_rec.fund_release_date,
x_fund_status => disb_cancel_rec.fund_status,
x_fund_status_date => disb_cancel_rec.fund_status_date,
x_fee_paid_1 => disb_cancel_rec.fee_paid_1,
x_fee_paid_2 => disb_cancel_rec.fee_paid_2,
x_cheque_number => disb_cancel_rec.cheque_number,
x_ld_cal_type => disb_cancel_rec.ld_cal_type,
x_ld_sequence_number => disb_cancel_rec.ld_sequence_number,
x_disb_accepted_amt => 0,
x_disb_paid_amt => 0,
x_rvsn_id => disb_cancel_rec.rvsn_id,
x_int_rebate_amt => disb_cancel_rec.int_rebate_amt,
x_force_disb => disb_cancel_rec.force_disb,
x_min_credit_pts => disb_cancel_rec.min_credit_pts,
x_disb_exp_dt => disb_cancel_rec.disb_exp_dt,
x_verf_enfr_dt => disb_cancel_rec.verf_enfr_dt,
x_fee_class => disb_cancel_rec.fee_class,
x_show_on_bill => disb_cancel_rec.show_on_bill,
x_mode => 'R',
x_attendance_type_code => disb_cancel_rec.attendance_type_code,
x_base_attendance_type_code => disb_cancel_rec.base_attendance_type_code,
x_payment_prd_st_date => disb_cancel_rec.payment_prd_st_date,
x_change_type_code => disb_cancel_rec.change_type_code,
x_fund_return_mthd_code => disb_cancel_rec.fund_return_mthd_code,
x_direct_to_borr_flag => disb_cancel_rec.direct_to_borr_flag
);
SELECT fed_fund_code,ci_cal_type,ci_sequence_number
FROM igf_aw_award_v
WHERE award_id = cp_award_id;
SELECT rowid row_id,
disb.*
FROM igf_aw_awd_disb_all disb
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num;
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => p_award_id,
x_disb_num => i,
x_tp_cal_type => p_pell_tab(i).tp_cal_type,
x_tp_sequence_number => p_pell_tab(i).tp_sequence_number,
x_disb_gross_amt => p_pell_tab(i).offered_amt,
x_fee_1 => 0,
x_fee_2 => 0,
x_disb_net_amt => p_pell_tab(i).offered_amt,
x_disb_date => p_pell_tab(i).disb_dt,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_hold_ind,
x_manual_hold_ind => 'N',
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_disb.late_disb_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => 0,
x_fee_paid_2 => 0,
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => p_pell_tab(i).ld_cal_type,
x_ld_sequence_number => p_pell_tab(i).ld_sequence_number,
x_disb_accepted_amt => p_pell_tab(i).accepted_amt,
x_disb_paid_amt => 0,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => 0,
x_force_disb => 'N',
x_min_credit_pts => p_pell_tab(i).min_credit_pts,
x_disb_exp_dt => p_pell_tab(i).disb_exp_dt,
x_verf_enfr_dt => p_pell_tab(i).verf_enfr_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => p_pell_tab(i).show_on_bill,
x_attendance_type_code => p_pell_tab(i).attendance_type_code,
x_base_attendance_type_code => p_pell_tab(i).base_attendance_type_code,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
igf_aw_awd_disb_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_award_id => p_award_id,
x_disb_num => i,
x_tp_cal_type => p_pell_tab(i).tp_cal_type,
x_tp_sequence_number => p_pell_tab(i).tp_sequence_number,
x_disb_gross_amt => p_pell_tab(i).offered_amt,
x_fee_1 => 0,
x_fee_2 => 0,
x_disb_net_amt => p_pell_tab(i).offered_amt,
x_disb_date => p_pell_tab(i).disb_dt,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => NULL,
x_hold_rel_ind => l_hold_ind,
x_manual_hold_ind => 'N',
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 => 0,
x_fee_paid_2 => 0,
x_cheque_number => NULL,
x_ld_cal_type => p_pell_tab(i).ld_cal_type,
x_ld_sequence_number => p_pell_tab(i).ld_sequence_number,
x_disb_accepted_amt => p_pell_tab(i).accepted_amt,
x_disb_paid_amt => 0,
x_rvsn_id => NULL,
x_int_rebate_amt => 0,
x_force_disb => 'N',
x_min_credit_pts => p_pell_tab(i).min_credit_pts,
x_disb_exp_dt => p_pell_tab(i).disb_exp_dt,
x_verf_enfr_dt => p_pell_tab(i).verf_enfr_dt,
x_fee_class => NULL,
x_show_on_bill => p_pell_tab(i).show_on_bill,
x_attendance_type_code => p_pell_tab(i).attendance_type_code,
x_base_attendance_type_code => p_pell_tab(i).base_attendance_type_code,
x_payment_prd_st_date => NULL,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
SELECT fa_detail.*
FROM igf_ap_fa_base_rec fa_detail
WHERE fa_detail.base_id = p_base_id;
SELECT
SUM(NVL(citsn.amount,0)) coa_total,
SUM(NVL(citsn.pell_coa_amount,0)) pell_coa,
SUM(NVL(citsn.alt_pell_amount,0)) pell_alt_expense,
SUM(DECODE(citsn.fixed_cost,'Y',NVL(citsn.amount,0),0) ) fixed_coa
FROM igf_aw_coa_items citsn
WHERE citsn.base_id = p_base_id ;
SELECT
SUM(NVL(citsn.pell_coa_amount,-1)) pell_coa,
SUM(NVL(citsn.alt_pell_amount,-1)) pell_alt_expense
FROM igf_aw_coa_items citsn
WHERE citsn.base_id = p_base_id ;
SELECT
COUNT(base_id) rec_cnt
FROM igf_aw_coa_items citsn
WHERE citsn.base_id = p_base_id ;
igf_ap_fa_base_rec_pkg.update_row(
x_rowid => l_stud_det.row_id,
x_base_id => l_stud_det.base_id,
x_ci_cal_type => l_stud_det.ci_cal_type,
x_person_id => l_stud_det.person_id,
x_ci_sequence_number => l_stud_det.ci_sequence_number,
x_org_id => l_stud_det.org_id,
x_coa_pending => l_stud_det.coa_pending,
x_verification_process_run => l_stud_det.verification_process_run,
x_inst_verif_status_date => l_stud_det.inst_verif_status_date,
x_manual_verif_flag => l_stud_det.manual_verif_flag,
x_fed_verif_status => l_stud_det.fed_verif_status,
x_fed_verif_status_date => l_stud_det.fed_verif_status_date,
x_inst_verif_status => l_stud_det.inst_verif_status,
x_nslds_eligible => l_stud_det.nslds_eligible,
x_ede_correction_batch_id => l_stud_det.ede_correction_batch_id,
x_fa_process_status_date => l_stud_det.fa_process_status_date,
x_isir_corr_status => l_stud_det.isir_corr_status,
x_isir_corr_status_date => l_stud_det.isir_corr_status_date,
x_isir_status => l_stud_det.isir_status,
x_isir_status_date => l_stud_det.isir_status_date,
x_coa_code_f => NULL,
x_coa_code_i => NULL,
x_coa_f => tot_coa_rec.coa_total,
x_coa_i => tot_coa_rec.coa_total,
x_disbursement_hold => l_stud_det.disbursement_hold,
x_fa_process_status => l_stud_det.fa_process_status,
x_notification_status => l_stud_det.notification_status,
x_notification_status_date => l_stud_det.notification_status_date,
x_packaging_status => l_stud_det.packaging_status,
x_packaging_status_date => l_stud_det.packaging_status_date,
x_total_package_accepted => l_stud_det.total_package_accepted,
x_total_package_offered => l_stud_det.total_package_offered,
x_admstruct_id => l_stud_det.admstruct_id,
x_admsegment_1 => l_stud_det.admsegment_1,
x_admsegment_2 => l_stud_det.admsegment_2,
x_admsegment_3 => l_stud_det.admsegment_3,
x_admsegment_4 => l_stud_det.admsegment_4,
x_admsegment_5 => l_stud_det.admsegment_5,
x_admsegment_6 => l_stud_det.admsegment_6,
x_admsegment_7 => l_stud_det.admsegment_7,
x_admsegment_8 => l_stud_det.admsegment_8,
x_admsegment_9 => l_stud_det.admsegment_9,
x_admsegment_10 => l_stud_det.admsegment_10,
x_admsegment_11 => l_stud_det.admsegment_11,
x_admsegment_12 => l_stud_det.admsegment_12,
x_admsegment_13 => l_stud_det.admsegment_13,
x_admsegment_14 => l_stud_det.admsegment_14,
x_admsegment_15 => l_stud_det.admsegment_15,
x_admsegment_16 => l_stud_det.admsegment_16,
x_admsegment_17 => l_stud_det.admsegment_17,
x_admsegment_18 => l_stud_det.admsegment_18,
x_admsegment_19 => l_stud_det.admsegment_19,
x_admsegment_20 => l_stud_det.admsegment_20,
x_packstruct_id => l_stud_det.packstruct_id,
x_packsegment_1 => l_stud_det.packsegment_1,
x_packsegment_2 => l_stud_det.packsegment_2,
x_packsegment_3 => l_stud_det.packsegment_3,
x_packsegment_4 => l_stud_det.packsegment_4,
x_packsegment_5 => l_stud_det.packsegment_5,
x_packsegment_6 => l_stud_det.packsegment_6,
x_packsegment_7 => l_stud_det.packsegment_7,
x_packsegment_8 => l_stud_det.packsegment_8,
x_packsegment_9 => l_stud_det.packsegment_9,
x_packsegment_10 => l_stud_det.packsegment_10,
x_packsegment_11 => l_stud_det.packsegment_11,
x_packsegment_12 => l_stud_det.packsegment_12,
x_packsegment_13 => l_stud_det.packsegment_13,
x_packsegment_14 => l_stud_det.packsegment_14,
x_packsegment_15 => l_stud_det.packsegment_15,
x_packsegment_16 => l_stud_det.packsegment_16,
x_packsegment_17 => l_stud_det.packsegment_17,
x_packsegment_18 => l_stud_det.packsegment_18,
x_packsegment_19 => l_stud_det.packsegment_19,
x_packsegment_20 => l_stud_det.packsegment_20,
x_miscstruct_id => l_stud_det.miscstruct_id,
x_miscsegment_1 => l_stud_det.miscsegment_1,
x_miscsegment_2 => l_stud_det.miscsegment_2,
x_miscsegment_3 => l_stud_det.miscsegment_3,
x_miscsegment_4 => l_stud_det.miscsegment_4,
x_miscsegment_5 => l_stud_det.miscsegment_5,
x_miscsegment_6 => l_stud_det.miscsegment_6,
x_miscsegment_7 => l_stud_det.miscsegment_7,
x_miscsegment_8 => l_stud_det.miscsegment_8,
x_miscsegment_9 => l_stud_det.miscsegment_9,
x_miscsegment_10 => l_stud_det.miscsegment_10,
x_miscsegment_11 => l_stud_det.miscsegment_11,
x_miscsegment_12 => l_stud_det.miscsegment_12,
x_miscsegment_13 => l_stud_det.miscsegment_13,
x_miscsegment_14 => l_stud_det.miscsegment_14,
x_miscsegment_15 => l_stud_det.miscsegment_15,
x_miscsegment_16 => l_stud_det.miscsegment_16,
x_miscsegment_17 => l_stud_det.miscsegment_17,
x_miscsegment_18 => l_stud_det.miscsegment_18,
x_miscsegment_19 => l_stud_det.miscsegment_19,
x_miscsegment_20 => l_stud_det.miscsegment_20,
x_prof_judgement_flg => l_stud_det.prof_judgement_flg,
x_nslds_data_override_flg => l_stud_det.nslds_data_override_flg ,
x_target_group => l_stud_det.target_group,
x_coa_fixed => tot_coa_rec.fixed_coa,
x_coa_pell => tot_coa_rec.pell_coa,
x_profile_status => l_stud_det.profile_status,
x_profile_status_date => l_stud_det.profile_status_date,
x_profile_fc => l_stud_det.profile_fc,
x_tolerance_amount => l_stud_det.tolerance_amount,
x_pell_alt_expense => tot_coa_rec.pell_alt_expense,
x_manual_disb_hold => l_stud_det.manual_disb_hold,
x_mode => 'R',
x_assoc_org_num => l_stud_det.assoc_org_num,
x_award_fmly_contribution_type => l_stud_det.award_fmly_contribution_type,
x_isir_locked_by => l_stud_det.isir_locked_by,
x_adnl_unsub_loan_elig_flag => l_stud_det.adnl_unsub_loan_elig_flag,
x_lock_coa_flag => l_stud_det.lock_coa_flag,
x_lock_awd_flag => l_stud_det.lock_awd_flag
);
SELECT disb.rowid, disb.*
FROM igf_aw_awd_disb disb
WHERE
award_id = p_award_id AND
trans_type <> 'C'
ORDER BY disb_num ;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast
WHERE fcat.fund_code = fmast.fund_code
AND fmast.fund_id = p_fund_id;
igf_aw_awd_disb_pkg.update_row(
x_rowid => l_disb_rec.rowid ,
x_award_id => l_disb_rec.award_id ,
x_disb_num => l_disb_rec.disb_num ,
x_tp_cal_type => l_disb_rec.tp_cal_type,
x_tp_sequence_number => l_disb_rec.tp_sequence_number ,
x_disb_gross_amt => NVL(l_disb_structure_rec(l_disb_no).disb_amt,0),
x_fee_1 => l_disb_rec.fee_1 ,
x_fee_2 => l_disb_rec.fee_2 ,
x_disb_net_amt => l_disb_net_amt ,
x_disb_date => l_disb_rec.disb_date ,
x_trans_type => l_disb_rec.trans_type ,
x_elig_status => l_disb_rec.elig_status ,
x_elig_status_date => l_disb_rec.elig_status_date ,
x_affirm_flag => l_disb_rec.affirm_flag ,
x_hold_rel_ind => l_disb_rec.hold_rel_ind ,
x_manual_hold_ind => l_disb_rec.manual_hold_ind ,
x_disb_status => l_disb_rec.disb_status ,
x_disb_status_date => l_disb_rec.disb_status_date ,
x_late_disb_ind => l_disb_rec.late_disb_ind ,
x_fund_dist_mthd => l_disb_rec.fund_dist_mthd ,
x_prev_reported_ind => l_disb_rec.prev_reported_ind ,
x_fund_release_date => l_disb_rec.fund_release_date ,
x_fund_status => l_disb_rec.fund_status ,
x_fund_status_date => l_disb_rec.fund_status_date ,
x_fee_paid_1 => l_disb_rec.fee_paid_1 ,
x_fee_paid_2 => l_disb_rec.fee_paid_2 ,
x_cheque_number => l_disb_rec.cheque_number ,
x_ld_cal_type => l_disb_rec.ld_cal_type ,
x_ld_sequence_number => l_disb_rec.ld_sequence_number ,
x_disb_accepted_amt => l_accepted_amt ,
x_disb_paid_amt => l_disb_rec.disb_paid_amt ,
x_rvsn_id => l_disb_rec.rvsn_id ,
x_int_rebate_amt => l_disb_rec.int_rebate_amt ,
x_force_disb => l_disb_rec.force_disb ,
x_min_credit_pts => l_disb_rec.min_credit_pts ,
x_disb_exp_dt => l_disb_rec.disb_exp_dt ,
x_verf_enfr_dt => l_disb_rec.verf_enfr_dt ,
x_fee_class => l_disb_rec.fee_class ,
x_show_on_bill => l_disb_rec.show_on_bill ,
x_mode => 'R' ,
x_attendance_type_code => l_disb_rec.attendance_type_code ,
x_base_attendance_type_code=> l_disb_rec.base_attendance_type_code ,
x_payment_prd_st_date => l_disb_rec.payment_prd_st_date ,
x_change_type_code => l_disb_rec.change_type_code ,
x_fund_return_mthd_code => l_disb_rec.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb_rec.direct_to_borr_flag
);
SELECT fcat.fund_code,
fcat.fed_fund_code,
fund.disb_exp_da,
fund.ci_cal_type awd_cal_type,
fund.ci_sequence_number awd_sequence_number,
fund.disb_verf_da,
fund.show_on_bill,
fund.nslds_disb_da
FROM igf_aw_fund_mast fund,
igf_aw_fund_cat fcat
WHERE fund_id = cp_fund_id
AND fund.fund_code = fcat.fund_code;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type, ld_sequence_number) coaterms,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND terms.adplans_id = dp.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND coaterms.ld_cal_type = aprd.ld_cal_type
AND coaterms.ld_sequence_number = aprd.ld_sequence_number
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
igf_aw_awd_prd_term aprd,
igf_aw_awd_dist_plans dp
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND terms.ld_cal_type = aprd.ld_cal_type
AND terms.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND dp.adplans_id = terms.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
teach_periods.tp_perct_num/cp_num_terms perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND terms.adplans_id = dp.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND coaterms.ld_cal_type = aprd.ld_cal_type
AND coaterms.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
teach_periods.tp_perct_num/cp_num_terms perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND terms.adplans_id = dp.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(coa_term_amount/cp_total_coa_amount) * teach_periods.tp_perct_num perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number,
amount coa_term_amount
FROM igf_aw_coa_term_tot_v
WHERE base_id = cp_base_id) coaterms,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND dp.adplans_id = terms.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ld_cal_type = coaterms.ld_cal_type
AND aprd.ld_sequence_number = coaterms.ld_sequence_number
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type, ld_sequence_number) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
teach_periods.tp_perct_num/cp_num_terms perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
teach_periods.tp_perct_num/cp_num_terms perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
ORDER BY 1;
SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
terms.ld_cal_type ld_cal_type,
terms.ld_sequence_number ld_sequence_number,
teach_periods.tp_cal_type tp_cal_type,
teach_periods.tp_sequence_number tp_sequence_number,
(coa_term_amount/cp_total_coa_amount) * teach_periods.tp_perct_num perct,
teach_periods.start_date start_dt,
teach_periods.date_offset_cd tp_offset_da,
teach_periods.credit_points_num min_credit_points,
teach_periods.attendance_type_code attendance_type_code
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds_v teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number,
amount coa_term_amount
FROM igf_aw_coa_term_tot_v
WHERE base_id = cp_base_id) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
ORDER BY 1;
SELECT COUNT(*)
FROM igf_aw_dp_terms terms,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = cp_adplans_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id;
SELECT COUNT(*)
FROM igf_aw_dp_terms terms
WHERE terms.adplans_id = cp_adplans_id;
SELECT SUM(amount) coa
FROM igf_aw_coa_itm_terms coa_terms,
(SELECT ld_cal_type,
ld_sequence_number
FROM igf_aw_dp_terms
WHERE adplans_id = cp_adplans_id
)dist_terms
WHERE dist_terms.ld_cal_type = coa_terms.ld_cal_type
AND dist_terms.ld_sequence_number = coa_terms.ld_sequence_number
AND coa_terms.base_id = cp_base_id;
SELECT SUM(amount) coa
FROM igf_aw_coa_itm_terms coa_terms,
(SELECT ld_cal_type,
ld_sequence_number
FROM igf_aw_dp_terms
WHERE adplans_id = cp_adplans_id
)dist_terms,
igf_ap_fa_base_rec_all fa,
igf_aw_awd_prd_term aprd
WHERE dist_terms.ld_cal_type = coa_terms.ld_cal_type
AND dist_terms.ld_sequence_number = coa_terms.ld_sequence_number
AND coa_terms.base_id = cp_base_id
AND coa_terms.base_id = fa.base_id
AND fa.ci_cal_type = aprd.ci_cal_type
AND fa.ci_sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ld_cal_type = coa_terms.ld_cal_type
AND aprd.ld_sequence_number = coa_terms.ld_sequence_number;
SELECT COUNT(*)
FROM igf_aw_dp_terms terms,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = cp_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
igf_aw_awd_prd_term aprd,
igf_ap_fa_base_rec_all fa
WHERE terms.adplans_id = cp_adplans_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = cp_base_id
AND coaterms.base_id = fa.base_id
AND fa.ci_cal_type = aprd.ci_cal_type
AND fa.ci_sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ld_cal_type = coaterms.ld_cal_type
AND aprd.ld_sequence_number = coaterms.ld_sequence_number;
SELECT COUNT(*)
FROM igf_aw_dp_terms terms,
igf_aw_awd_dist_plans dp,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = cp_adplans_id
AND terms.adplans_id = dp.adplans_id
AND dp.cal_type = aprd.ci_cal_type
AND dp.sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = cp_awd_prd_code
AND aprd.ld_cal_type = terms.ld_cal_type
AND aprd.ld_sequence_number = terms.ld_sequence_number;
SELECT rowid row_id,
disb.*
FROM igf_aw_awd_disb_all disb
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num;
SELECT base_id
FROM igf_aw_award_all
WHERE award_id = p_award_id;
SELECT 'x'
FROM igf_ap_nslds_data nslds,
igf_ap_fa_base_rec_all fabase
WHERE fabase.person_id = (SELECT person_id from igf_ap_fa_base_rec_all WHERE base_id = p_base_id) AND
fabase.base_id = nslds.base_id AND
nslds.nslds_loan_prog_code_1 IS NOT NULL;
SELECT awd_dist_plan_cd_desc
FROM igf_aw_awd_dist_plans
WHERE adplans_id = cp_adplans_id;
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => p_award_id,
x_disb_num => ln_count,
x_tp_cal_type => lc_auto_disb.tp_cal_type,
x_tp_sequence_number => lc_auto_disb.tp_sequence_number,
x_disb_gross_amt => ln_disb_gross_amt,
x_fee_1 => ln_fee_1,
x_fee_2 => ln_fee_2,
x_disb_net_amt => ln_disb_net_amt,
x_disb_date => lc_auto_disb.disb_dt,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_hold_ind,
x_manual_hold_ind => 'N',
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_auto_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => 0,
x_fee_paid_2 => 0,
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => lc_auto_disb.ld_cal_type,
x_ld_sequence_number => lc_auto_disb.ld_sequence_number,
x_disb_accepted_amt => ln_disb_accepted_amt,
x_disb_paid_amt => 0,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => ln_int_rebate_amt,
x_force_disb => 'N',
x_min_credit_pts => lc_auto_disb.min_credit_points,
x_disb_exp_dt => ld_disb_exp_dt,
x_verf_enfr_dt => ld_verf_enfr_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => l_get_fund_dtls.show_on_bill,
x_attendance_type_code => lc_auto_disb.attendance_type_code,
x_base_attendance_type_code => lv_base_att_type,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
igf_aw_awd_disb_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_award_id => p_award_id,
x_disb_num => ln_count,
x_tp_cal_type => lc_auto_disb.tp_cal_type,
x_tp_sequence_number => lc_auto_disb.tp_sequence_number,
x_disb_gross_amt => ln_disb_gross_amt,
x_fee_1 => ln_fee_1,
x_fee_2 => ln_fee_2,
x_disb_net_amt => ln_disb_net_amt,
x_disb_date => lc_auto_disb.disb_dt,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => NULL,
x_hold_rel_ind => l_hold_ind,
x_manual_hold_ind => 'N',
x_disb_status => NULL,
x_disb_status_date => NULL,
x_late_disb_ind => l_auto_ind,
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 => 0,
x_fee_paid_2 => 0,
x_cheque_number => NULL,
x_ld_cal_type => lc_auto_disb.ld_cal_type,
x_ld_sequence_number => lc_auto_disb.ld_sequence_number,
x_disb_accepted_amt => ln_disb_accepted_amt,
x_disb_paid_amt => 0,
x_rvsn_id => NULL,
x_int_rebate_amt => ln_int_rebate_amt,
x_force_disb => 'N',
x_min_credit_pts => lc_auto_disb.min_credit_points,
x_disb_exp_dt => ld_disb_exp_dt,
x_verf_enfr_dt => ld_verf_enfr_dt,
x_fee_class => NULL,
x_show_on_bill => l_get_fund_dtls.show_on_bill,
x_attendance_type_code => lc_auto_disb.attendance_type_code,
x_base_attendance_type_code => lv_base_att_type,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => NULL,
x_fund_return_mthd_code => NULL,
x_direct_to_borr_flag => 'N'
);
igf_aw_awd_disb_pkg.update_row(
x_mode => 'R',
x_rowid => l_disb.row_id,
x_award_id => l_disb.award_id,
x_disb_num => l_disb.disb_num,
x_tp_cal_type => l_disb.tp_cal_type,
x_tp_sequence_number => l_disb.tp_sequence_number,
x_disb_gross_amt => l_disb.disb_gross_amt,
x_fee_1 => l_disb.fee_1,
x_fee_2 => l_disb.fee_2,
x_disb_net_amt => l_disb.disb_net_amt,
x_disb_date => ld_disb_date1,
x_trans_type => l_disb.trans_type,
x_elig_status => l_disb.elig_status,
x_elig_status_date => l_disb.elig_status_date,
x_affirm_flag => l_disb.affirm_flag,
x_hold_rel_ind => l_disb.hold_rel_ind,
x_manual_hold_ind => l_disb.manual_hold_ind,
x_disb_status => l_disb.disb_status,
x_disb_status_date => l_disb.disb_status_date,
x_late_disb_ind => l_disb.late_disb_ind,
x_fund_dist_mthd => l_disb.fund_dist_mthd,
x_prev_reported_ind => l_disb.prev_reported_ind ,
x_fund_release_date => l_disb.fund_release_date,
x_fund_status => l_disb.fund_status,
x_fund_status_date => l_disb.fund_status_date,
x_fee_paid_1 => l_disb.fee_paid_1,
x_fee_paid_2 => l_disb.fee_paid_2,
x_cheque_number => l_disb.cheque_number,
x_ld_cal_type => l_disb.ld_cal_type,
x_ld_sequence_number => l_disb.ld_sequence_number,
x_disb_accepted_amt => l_disb.disb_accepted_amt,
x_disb_paid_amt => l_disb.disb_paid_amt,
x_rvsn_id => l_disb.rvsn_id,
x_int_rebate_amt => l_disb.int_rebate_amt,
x_force_disb => l_disb.force_disb,
x_min_credit_pts => l_disb.min_credit_pts,
x_disb_exp_dt => l_disb.disb_exp_dt,
x_verf_enfr_dt => l_disb.verf_enfr_dt,
x_fee_class => l_disb.fee_class,
x_show_on_bill => l_disb.show_on_bill,
x_attendance_type_code => l_disb.attendance_type_code,
x_base_attendance_type_code => l_disb.base_attendance_type_code,
x_payment_prd_st_date => l_disb.payment_prd_st_date,
x_change_type_code => l_disb.change_type_code,
x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
x_direct_to_borr_flag => l_disb.direct_to_borr_flag
);
|| holds on the disbursements then updates the rules override to 'N' for award.
|| Parameter : Award_Id - Incates the Award ID for which override rules need to be removed.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
--
-- Get the details of Disbursement Holds for a given award if any holds exists
--
CURSOR c_chk_sys_disb_holds(p_award_id igf_aw_award.award_id%TYPE)
IS
SELECT 'x'
FROM igf_db_disb_holds dh
WHERE dh.award_id = p_award_id
AND dh.hold_type = 'SYSTEM'
AND dh.hold = 'OVERAWARD'
AND dh.release_flag = 'N';
SELECT awd.*
FROM igf_aw_award awd
WHERE awd.award_id = p_award_id;
igf_aw_award_pkg.update_row(
x_mode => 'R',
x_rowid => lc_get_awd_dtls.row_id,
x_award_id => lc_get_awd_dtls.award_id,
x_fund_id => lc_get_awd_dtls.fund_id,
x_base_id => lc_get_awd_dtls.base_id,
x_offered_amt => lc_get_awd_dtls.offered_amt,
x_accepted_amt => lc_get_awd_dtls.accepted_amt,
x_paid_amt => lc_get_awd_dtls.paid_amt,
x_packaging_type => lc_get_awd_dtls.packaging_type,
x_batch_id => lc_get_awd_dtls.batch_id,
x_manual_update => lc_get_awd_dtls.manual_update,
x_rules_override => 'N',
x_award_date => lc_get_awd_dtls.award_date,
x_award_status => lc_get_awd_dtls.award_status,
x_attribute_category => lc_get_awd_dtls.attribute_category,
x_attribute1 => lc_get_awd_dtls.attribute1,
x_attribute2 => lc_get_awd_dtls.attribute2,
x_attribute3 => lc_get_awd_dtls.attribute3,
x_attribute4 => lc_get_awd_dtls.attribute4,
x_attribute5 => lc_get_awd_dtls.attribute5,
x_attribute6 => lc_get_awd_dtls.attribute6,
x_attribute7 => lc_get_awd_dtls.attribute7,
x_attribute8 => lc_get_awd_dtls.attribute8,
x_attribute9 => lc_get_awd_dtls.attribute9,
x_attribute10 => lc_get_awd_dtls.attribute10,
x_attribute11 => lc_get_awd_dtls.attribute11,
x_attribute12 => lc_get_awd_dtls.attribute12,
x_attribute13 => lc_get_awd_dtls.attribute13,
x_attribute14 => lc_get_awd_dtls.attribute14,
x_attribute15 => lc_get_awd_dtls.attribute15,
x_attribute16 => lc_get_awd_dtls.attribute16,
x_attribute17 => lc_get_awd_dtls.attribute17,
x_attribute18 => lc_get_awd_dtls.attribute18,
x_attribute19 => lc_get_awd_dtls.attribute19,
x_attribute20 => lc_get_awd_dtls.attribute20,
x_rvsn_id => lc_get_awd_dtls.rvsn_id,
x_award_number_txt => lc_get_awd_dtls.award_number_txt,
x_legacy_record_flag => NULL,
x_adplans_id => lc_get_awd_dtls.adplans_id,
x_lock_award_flag => lc_get_awd_dtls.lock_award_flag,
x_app_trans_num_txt => lc_get_awd_dtls.app_trans_num_txt,
x_awd_proc_status_code => lc_get_awd_dtls.awd_proc_status_code,
x_notification_status_code => lc_get_awd_dtls.notification_status_code,
x_notification_status_date => lc_get_awd_dtls.notification_status_date,
x_publish_in_ss_flag => lc_get_awd_dtls.publish_in_ss_flag
);
SELECT dh.release_flag
FROM igf_db_disb_holds dh
WHERE dh.award_id = p_award_id
AND dh.disb_num = p_disb_num
AND dh.hold_type = 'SYSTEM'
AND dh.hold = 'OVERAWARD';
SELECT disb.award_id, disb.disb_num
FROM igf_aw_awd_disb disb
WHERE disb.award_id = p_award_id
AND disb.trans_type = 'P';
igf_db_disb_holds_pkg.insert_row(
x_mode => 'R',
x_rowid => lc_row_id,
x_hold_id => ln_hold_id,
x_award_id => rec_c_get_planned_awd_disb.award_id,
x_disb_num => rec_c_get_planned_awd_disb.disb_num,
x_hold => 'OVERAWARD',
x_hold_date => TRUNC(sysdate),
x_hold_type => 'SYSTEM',
x_release_date => NULL,
x_release_flag => 'N',
x_release_reason => NULL
);
PROCEDURE update_accept_amount (p_award_id IN igf_aw_award.award_id%TYPE )
IS
/*
-----------------------------------------------------------------------------
--
-- adhawan, May 12th 2002
--This procedure ensures that whenever the Award Status is changed to Accepted from Offered
--and the Accepted amount is null or Zero then updation of the accepted amounts should take
-- place with the offered amounts to the Disbursement table.
-- who when what
-- adhawan 12-May-2002 Added this procedure
-- Bug ID : 2332588
-----------------------------------------------------------------------------
*/
CURSOR c_accept_null IS
SELECT disb.*,disb.rowid row_id
FROM igf_aw_awd_disb_all disb
WHERE disb.award_id = p_award_id
AND disb.trans_type <> 'C'
AND NVL(disb.disb_accepted_amt,0) = 0
FOR UPDATE OF disb.disb_gross_amt NOWAIT;
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => c_null_rec.row_id,
x_award_id => c_null_rec.award_id,
x_disb_num => c_null_rec.disb_num,
x_tp_cal_type => c_null_rec.tp_cal_type,
x_tp_sequence_number => c_null_rec.tp_sequence_number,
x_disb_gross_amt => c_null_rec.disb_gross_amt,
x_fee_1 => c_null_rec.fee_1,
x_fee_2 => c_null_rec.fee_2,
x_disb_net_amt => c_null_rec.disb_net_amt,
x_disb_date => c_null_rec.disb_date,
x_trans_type => c_null_rec.trans_type,
x_elig_status => c_null_rec.elig_status,
x_elig_status_date => c_null_rec.elig_status_date,
x_affirm_flag => c_null_rec.affirm_flag,
x_hold_rel_ind => c_null_rec.hold_rel_ind,
x_manual_hold_ind => c_null_rec.manual_hold_ind,
x_disb_status => c_null_rec.disb_status,
x_disb_status_date => c_null_rec.disb_status_date,
x_late_disb_ind => c_null_rec.late_disb_ind,
x_fund_dist_mthd => c_null_rec.fund_dist_mthd,
x_prev_reported_ind => c_null_rec.prev_reported_ind,
x_fund_release_date => c_null_rec.fund_release_date,
x_fund_status => c_null_rec.fund_status,
x_fund_status_date => c_null_rec.fund_status_date,
x_fee_paid_1 => c_null_rec.fee_paid_1,
x_fee_paid_2 => c_null_rec.fee_paid_2,
x_cheque_number => c_null_rec.cheque_number,
x_ld_cal_type => c_null_rec.ld_cal_type,
x_ld_sequence_number => c_null_rec.ld_sequence_number,
x_disb_accepted_amt => c_null_rec.disb_gross_amt,--Accepted amount made equal to Offered amount
x_disb_paid_amt => c_null_rec.disb_paid_amt,
x_rvsn_id => c_null_rec.rvsn_id,
x_int_rebate_amt => c_null_rec.int_rebate_amt,
x_force_disb => c_null_rec.force_disb,
x_min_credit_pts => c_null_rec.min_credit_pts,
x_disb_exp_dt => c_null_rec.disb_exp_dt,
x_verf_enfr_dt => c_null_rec.verf_enfr_dt,
x_fee_class => c_null_rec.fee_class,
x_show_on_bill => c_null_rec.show_on_bill,
x_attendance_type_code => c_null_rec.attendance_type_code,
x_base_attendance_type_code => c_null_rec.base_attendance_type_code,
x_payment_prd_st_date => c_null_rec.payment_prd_st_date,
x_change_type_code => c_null_rec.change_type_code,
x_fund_return_mthd_code => c_null_rec.fund_return_mthd_code,
x_direct_to_borr_flag => c_null_rec.direct_to_borr_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_ACCEPT_AMOUNT'||' ' ||SQLERRM);
END update_accept_amount;
PROCEDURE update_awd_cancell_to_offer(p_award_id IN igf_aw_award.award_id%TYPE,
p_award_stat IN VARCHAR2,
p_fed_fund_code IN VARCHAR2,
p_base_id IN NUMBER,
p_message OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------------
--
-- adhawan, May 12th 2002
--This procedure ensures that whenever the Award Status is changed to Accepted OR Offered
-- from Cancelled or Declined the Transaction type , eligibility status , elig date should get updated
-- who when what
--smadathi 24-NOV-2004 Enh. Bug 3416936. Modified the update_row call to
-- igf_aw_awd_disb table
--adhawan 24-May-2002 Added this procedure
--Bug ID : 2375571
-----------------------------------------------------------------------------*/
CURSOR cur_active_isir(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
) IS
SELECT transaction_num
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND NVL(active_isir,'N') = 'Y';
SELECT disb.ld_cal_type,
disb.ld_sequence_number,
disb.base_attendance_type_code,
SUM(disb.disb_gross_amt) term_total
FROM igf_aw_awd_disb_all disb,
igf_aw_award_all awd
WHERE awd.award_id = disb.award_id
AND awd.award_id = p_award_id
GROUP BY disb.ld_cal_type,disb.ld_sequence_number,disb.base_attendance_type_code;
SELECT disb.*,disb.rowid row_id
FROM igf_aw_awd_disb_all disb
WHERE disb.award_id = p_award_id
AND disb.trans_type = 'C'
FOR UPDATE OF disb.disb_gross_amt NOWAIT;
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => c_change_trans_rec.row_id,
x_award_id => c_change_trans_rec.award_id,
x_disb_num => c_change_trans_rec.disb_num,
x_tp_cal_type => c_change_trans_rec.tp_cal_type,
x_tp_sequence_number => c_change_trans_rec.tp_sequence_number,
x_disb_gross_amt => c_change_trans_rec.disb_gross_amt,
x_fee_1 => c_change_trans_rec.fee_1,
x_fee_2 => c_change_trans_rec.fee_2,
x_disb_net_amt => c_change_trans_rec.disb_net_amt,
x_disb_date => c_change_trans_rec.disb_date,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => c_change_trans_rec.affirm_flag,
x_hold_rel_ind => c_change_trans_rec.hold_rel_ind,
x_manual_hold_ind => c_change_trans_rec.manual_hold_ind,
x_disb_status => c_change_trans_rec.disb_status,
x_disb_status_date => c_change_trans_rec.disb_status_date,
x_late_disb_ind => c_change_trans_rec.late_disb_ind,
x_fund_dist_mthd => c_change_trans_rec.fund_dist_mthd,
x_prev_reported_ind => c_change_trans_rec.prev_reported_ind,
x_fund_release_date => c_change_trans_rec.fund_release_date,
x_fund_status => c_change_trans_rec.fund_status,
x_fund_status_date => c_change_trans_rec.fund_status_date,
x_fee_paid_1 => c_change_trans_rec.fee_paid_1,
x_fee_paid_2 => c_change_trans_rec.fee_paid_2,
x_cheque_number => c_change_trans_rec.cheque_number,
x_ld_cal_type => c_change_trans_rec.ld_cal_type,
x_ld_sequence_number => c_change_trans_rec.ld_sequence_number,
x_disb_accepted_amt => c_change_trans_rec.disb_gross_amt,--Accepted amount made equal to Offered amount
x_disb_paid_amt => c_change_trans_rec.disb_paid_amt,
x_rvsn_id => c_change_trans_rec.rvsn_id,
x_int_rebate_amt => c_change_trans_rec.int_rebate_amt,
x_force_disb => c_change_trans_rec.force_disb,
x_min_credit_pts => c_change_trans_rec.min_credit_pts,
x_disb_exp_dt => c_change_trans_rec.disb_exp_dt,
x_verf_enfr_dt => c_change_trans_rec.verf_enfr_dt,
x_fee_class => c_change_trans_rec.fee_class,
x_show_on_bill => c_change_trans_rec.show_on_bill,
x_attendance_type_code => c_change_trans_rec.attendance_type_code,
x_base_attendance_type_code => c_change_trans_rec.base_attendance_type_code,
x_payment_prd_st_date => c_change_trans_rec.payment_prd_st_date,
x_change_type_code => c_change_trans_rec.change_type_code,
x_fund_return_mthd_code => c_change_trans_rec.fund_return_mthd_code,
x_called_from => 'IGFAW016',
x_direct_to_borr_flag => c_change_trans_rec.direct_to_borr_flag
);
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => c_change_trans_rec.row_id,
x_award_id => c_change_trans_rec.award_id,
x_disb_num => c_change_trans_rec.disb_num,
x_tp_cal_type => c_change_trans_rec.tp_cal_type,
x_tp_sequence_number => c_change_trans_rec.tp_sequence_number,
x_disb_gross_amt => c_change_trans_rec.disb_gross_amt,
x_fee_1 => c_change_trans_rec.fee_1,
x_fee_2 => c_change_trans_rec.fee_2,
x_disb_net_amt => c_change_trans_rec.disb_net_amt,
x_disb_date => c_change_trans_rec.disb_date,
x_trans_type => 'P',
x_elig_status => 'N',
x_elig_status_date => TRUNC(SYSDATE),
x_affirm_flag => c_change_trans_rec.affirm_flag,
x_hold_rel_ind => c_change_trans_rec.hold_rel_ind,
x_manual_hold_ind => c_change_trans_rec.manual_hold_ind,
x_disb_status => c_change_trans_rec.disb_status,
x_disb_status_date => c_change_trans_rec.disb_status_date,
x_late_disb_ind => c_change_trans_rec.late_disb_ind,
x_fund_dist_mthd => c_change_trans_rec.fund_dist_mthd,
x_prev_reported_ind => c_change_trans_rec.prev_reported_ind,
x_fund_release_date => c_change_trans_rec.fund_release_date,
x_fund_status => c_change_trans_rec.fund_status,
x_fund_status_date => c_change_trans_rec.fund_status_date,
x_fee_paid_1 => c_change_trans_rec.fee_paid_1,
x_fee_paid_2 => c_change_trans_rec.fee_paid_2,
x_cheque_number => c_change_trans_rec.cheque_number,
x_ld_cal_type => c_change_trans_rec.ld_cal_type,
x_ld_sequence_number => c_change_trans_rec.ld_sequence_number,
x_disb_accepted_amt => c_change_trans_rec.disb_accepted_amt,
x_disb_paid_amt => c_change_trans_rec.disb_paid_amt,
x_rvsn_id => c_change_trans_rec.rvsn_id,
x_int_rebate_amt => c_change_trans_rec.int_rebate_amt,
x_force_disb => c_change_trans_rec.force_disb,
x_min_credit_pts => c_change_trans_rec.min_credit_pts,
x_disb_exp_dt => c_change_trans_rec.disb_exp_dt,
x_verf_enfr_dt => c_change_trans_rec.verf_enfr_dt,
x_fee_class => c_change_trans_rec.fee_class,
x_show_on_bill => c_change_trans_rec.show_on_bill,
x_attendance_type_code => c_change_trans_rec.attendance_type_code,
x_base_attendance_type_code => c_change_trans_rec.base_attendance_type_code,
x_payment_prd_st_date => c_change_trans_rec.payment_prd_st_date,
x_change_type_code => c_change_trans_rec.change_type_code,
x_fund_return_mthd_code => c_change_trans_rec.fund_return_mthd_code,
x_direct_to_borr_flag => c_change_trans_rec.direct_to_borr_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_AWD_CANCELL_TO_OFFER'||' ' || SQLERRM);
END update_awd_cancell_to_offer;
SELECT disb_num,disb_date,trans_type
FROM
igf_aw_awd_disb_all
WHERE
award_id = p_award_id
AND
trans_type IN ('P','A')
ORDER BY
disb_num;
SELECT disb_num,disb_date
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id
AND
trans_type IN ('P','A')
ORDER BY
disb_date;
SELECT
NVL(SUM(NVL(disb.disb_gross_amt,0)),0) lf_total,
COUNT(DISTINCT awd.award_id) lf_count
FROM
igf_aw_awd_disb_all disb,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec_all fabase
WHERE fmast.fund_code = cp_fund_code
AND disb.award_id = awd.award_id
AND awd.fund_id = fmast.fund_id
AND awd.base_id = fabase.base_id
AND fabase.person_id = cp_person_id
AND disb.trans_type <> 'C'
AND awd.award_status IN ('OFFERED', 'ACCEPTED');
SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
COUNT(awd.award_id) lf_count
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_ap_fa_base_rec fabase,
igf_ap_batch_aw_map_all bam
WHERE fmast.fund_code = cp_fund_code
AND awd.fund_id = fmast.fund_id
AND awd.base_id = fabase.base_id
AND fabase.person_id = cp_person_id
AND fabase.ci_cal_type = bam.ci_cal_type
AND fabase.ci_sequence_number = bam.ci_sequence_number
AND awd.award_status IN ('OFFERED', 'ACCEPTED')
AND bam.award_year_status_code IN ('LA','LE');
SELECT SUM(disb.disb_gross_amt) total_fund_amt
FROM
igf_aw_award awd,
igf_aw_awd_disb disb
WHERE awd.base_id = p_base_id
AND awd.fund_id = p_fund_id
AND awd.award_id = disb.award_id
AND awd.award_status IN ('OFFERED', 'ACCEPTED')
AND disb.trans_type <> 'C';
SELECT
SUM(disb.disb_gross_amt) total_award_amt
FROM
igf_aw_awd_disb disb
WHERE
disb.award_id = p_award_id AND
disb.trans_type <> 'C';
SELECT
fm.replace_fc
FROM
igf_aw_fund_mast fm
WHERE
fm.fund_id = p_fund_id;
SELECT
DECODE(
NVL(allow_overaward,'N'),'N',
NVL(fmast.remaining_amt,0),
NVL(fmast.remaining_amt,0) +
(
DECODE (
NVL(over_award_amt,0),0,
NVL(over_award_perct,0) * NVL(fmast.available_amt,0) / 100,
NVL(over_award_amt,0)
)
)
)remaining_amt,
NVL(fmast.max_yearly_amt,0) max_yearly_amt,
NVL(fmast.max_award_amt,0) max_award_amt,
NVL(fmast.max_life_amt,0) max_life_amt,
NVL(fmast.max_life_term,0) max_life_term,
fmast.min_award_amt,
fmast.max_num_disb,
fmast.min_num_disb
FROM
igf_aw_fund_mast_all fmast
WHERE
fmast.fund_id = p_fund_id;
SELECT
COUNT(ld_cal_type) disb_count
FROM igf_aw_awd_disb
WHERE
award_id = p_award_id;
SELECT disb_num,disb_date,trans_type,disb_accepted_amt accepted_amt
FROM
igf_aw_awd_disb_all
WHERE
award_id = p_award_id;
SELECT
*
FROM
igf_aw_award
WHERE
award_id = p_award_id ;
SELECT disb.ld_cal_type,
disb.ld_sequence_number,
disb.base_attendance_type_code,
SUM(disb.disb_gross_amt) term_total
FROM igf_aw_awd_disb_all disb,
igf_aw_award_all awd
WHERE disb.trans_type <> 'C'
AND awd.award_id = disb.award_id
AND awd.award_id = p_award_id
AND disb.ld_sequence_number = p_ld_seq_number
GROUP BY disb.ld_cal_type,disb.ld_sequence_number,disb.base_attendance_type_code;
igf_aw_award_pkg.update_row(x_rowid => c_award_status_rec.row_id,
x_award_id => c_award_status_rec.award_id,
x_fund_id => c_award_status_rec.fund_id,
x_base_id => c_award_status_rec.base_id,
x_offered_amt => c_award_status_rec.offered_amt,
x_accepted_amt => c_award_status_rec.accepted_amt,
x_paid_amt => c_award_status_rec.paid_amt,
x_packaging_type => c_award_status_rec.packaging_type,
x_batch_id => c_award_status_rec.batch_id,
x_manual_update => c_award_status_rec.manual_update,
x_rules_override => c_award_status_rec.rules_override,
x_award_date => c_award_status_rec.award_date,
x_award_status => c_award_status_rec.award_status,
x_attribute_category => c_award_status_rec.attribute_category,
x_attribute1 => c_award_status_rec.attribute1,
x_attribute2 => c_award_status_rec.attribute2,
x_attribute3 => c_award_status_rec.attribute3,
x_attribute4 => c_award_status_rec.attribute4,
x_attribute5 => c_award_status_rec.attribute5,
x_attribute6 => c_award_status_rec.attribute6,
x_attribute7 => c_award_status_rec.attribute7,
x_attribute8 => c_award_status_rec.attribute8,
x_attribute9 => c_award_status_rec.attribute9,
x_attribute10 => c_award_status_rec.attribute10,
x_attribute11 => c_award_status_rec.attribute11,
x_attribute12 => c_award_status_rec.attribute12,
x_attribute13 => c_award_status_rec.attribute13,
x_attribute14 => c_award_status_rec.attribute14,
x_attribute15 => c_award_status_rec.attribute15,
x_attribute16 => c_award_status_rec.attribute16,
x_attribute17 => c_award_status_rec.attribute17,
x_attribute18 => c_award_status_rec.attribute18,
x_attribute19 => c_award_status_rec.attribute19,
x_attribute20 => c_award_status_rec.attribute20,
x_rvsn_id => c_award_status_rec.rvsn_id,
x_alt_pell_schedule => c_award_status_rec.alt_pell_schedule,
x_mode => 'R',
x_award_number_txt => c_award_status_rec.award_number_txt,
x_legacy_record_flag => NULL,
x_adplans_id => c_award_status_rec.adplans_id,
x_lock_award_flag => c_award_status_rec.lock_award_flag,
x_app_trans_num_txt => c_award_status_rec.app_trans_num_txt,
x_awd_proc_status_code => c_award_status_rec.awd_proc_status_code,
x_notification_status_code => c_award_status_rec.notification_status_code,
x_notification_status_date => c_award_status_rec.notification_status_date,
x_publish_in_ss_flag => c_award_status_rec.publish_in_ss_flag
);
PROCEDURE update_bill_flag ( p_fund_id IN igf_aw_award_all.fund_id%TYPE,
p_new_val IN igf_aw_fund_mast_all.show_on_bill%TYPE)
IS
--
-- Cursor to get awards of the fund
--
CURSOR cur_get_awd ( p_fund_id IN igf_aw_award_all.fund_id%TYPE )
IS
SELECT award_id
FROM
igf_aw_award
WHERE
fund_id = p_fund_id;
SELECT *
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id AND
trans_type = 'P' AND
NVL(show_on_bill,'*') <> p_new_val
FOR UPDATE OF
show_on_bill NOWAIT;
igf_aw_awd_disb_pkg.update_row( x_rowid => get_adisb_rec.row_id,
x_award_id => get_adisb_rec.award_id,
x_disb_num => get_adisb_rec.disb_num,
x_tp_cal_type => get_adisb_rec.tp_cal_type,
x_tp_sequence_number => get_adisb_rec.tp_sequence_number,
x_disb_gross_amt => get_adisb_rec.disb_gross_amt,
x_fee_1 => get_adisb_rec.fee_1,
x_fee_2 => get_adisb_rec.fee_2,
x_disb_net_amt => get_adisb_rec.disb_net_amt,
x_disb_date => get_adisb_rec.disb_date,
x_trans_type => get_adisb_rec.trans_type,
x_elig_status => get_adisb_rec.elig_status,
x_elig_status_date => get_adisb_rec.elig_status_date,
x_affirm_flag => get_adisb_rec.affirm_flag,
x_hold_rel_ind => get_adisb_rec.hold_rel_ind,
x_manual_hold_ind => get_adisb_rec.manual_hold_ind,
x_disb_status => get_adisb_rec.disb_status,
x_disb_status_date => get_adisb_rec.disb_status_date,
x_late_disb_ind => get_adisb_rec.late_disb_ind,
x_fund_dist_mthd => get_adisb_rec.fund_dist_mthd,
x_prev_reported_ind => get_adisb_rec.prev_reported_ind,
x_fund_release_date => get_adisb_rec.fund_release_date,
x_fund_status => get_adisb_rec.fund_status,
x_fund_status_date => get_adisb_rec.fund_status_date,
x_fee_paid_1 => get_adisb_rec.fee_paid_1,
x_fee_paid_2 => get_adisb_rec.fee_paid_2,
x_cheque_number => get_adisb_rec.cheque_number,
x_ld_cal_type => get_adisb_rec.ld_cal_type,
x_ld_sequence_number => get_adisb_rec.ld_sequence_number,
x_disb_accepted_amt => get_adisb_rec.disb_accepted_amt,
x_disb_paid_amt => get_adisb_rec.disb_paid_amt,
x_rvsn_id => get_adisb_rec.rvsn_id,
x_int_rebate_amt => get_adisb_rec.int_rebate_amt,
x_force_disb => get_adisb_rec.force_disb,
x_min_credit_pts => get_adisb_rec.min_credit_pts,
x_disb_exp_dt => get_adisb_rec.disb_exp_dt,
x_verf_enfr_dt => get_adisb_rec.verf_enfr_dt,
x_fee_class => get_adisb_rec.fee_class,
x_show_on_bill => p_new_val,
x_mode => 'R',
x_attendance_type_code => get_adisb_rec.attendance_type_code,
x_payment_prd_st_date => get_adisb_rec.payment_prd_st_date,
x_change_type_code => get_adisb_rec.change_type_code,
x_fund_return_mthd_code => get_adisb_rec.fund_return_mthd_code,
x_direct_to_borr_flag => get_adisb_rec.direct_to_borr_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_BILL_FLAG' );
fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_BILL_FLAG'|| ' ' || SQLERRM);
END update_bill_flag;
FUNCTION delete_awd_disb ( p_award_id IN igf_aw_award_all.award_id%TYPE ,
p_ld_seq_num IN igf_aw_awd_disb_all.ld_sequence_number%TYPE ,
p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE )
RETURN VARCHAR2
IS
--
--------------------------------------------------------------------------------------------
-- Who when what
--------------------------------------------------------------------------------------------
-- Brajendr 14-Jun-2002 Bug 2415009
-- Added a check for not deleting of award
-- and disbursement if auth id is generated.
--------------------------------------------------------------------------------------------
-- mesriniv 29-may-2002 Added this line of code
-- igf_aw_gen.update_fabase_awds(get_awds_rec.base_id,'REVISED');
SELECT
COUNT(origination_id) awd_count
FROM
igf_gr_rfms
WHERE
award_id = p_award_id;
SELECT
COUNT(loan_id) awd_count
FROM
igf_sl_loans
WHERE
award_id = p_award_id;
SELECT COUNT(auth_id) awd_count
FROM igf_se_auth
WHERE award_id = p_award_id
AND flag = 'A';
SELECT
row_id,
disb_num
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id AND
disb_num = NVL(p_disb_num,disb_num) AND
ld_sequence_number = NVL(p_ld_seq_num,ld_sequence_number);
SELECT
row_id
FROM
igf_db_disb_holds
WHERE
award_id = p_award_id AND
disb_num = NVL(p_disb_num,disb_num);
SELECT
row_id,base_id
FROM
igf_aw_award
WHERE
award_id = p_award_id ;
SELECT fcat.fed_fund_code
FROM igf_aw_fund_cat fcat,
igf_aw_fund_mast fmast,
igf_aw_award_all awd
WHERE fcat.fund_code = fmast.fund_code
AND fmast.fund_id = awd.fund_id
AND awd.award_id = p_award_id;
SELECT ROWID row_id,
disb_status
FROM igf_aw_db_chg_dtls
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num;
igf_db_disb_holds_pkg.delete_row(get_holds_rec.row_id);
igf_aw_db_chg_dtls_pkg.delete_row(get_chg_dtls.row_id);
igf_aw_awd_disb_pkg.delete_row(get_adisb_rec.row_id);
fnd_message.set_token('NAME','IGF_AW_GEN_003.DELETE_AWD_DISB'||' '||SQLERRM);
END delete_awd_disb;
SELECT COUNT(disb_num) tot_num
FROM
igf_aw_awd_disb
WHERE
award_id = p_award_id AND
ld_sequence_number = NVL(p_ld_seq_num,ld_sequence_number);
SELECT
'Y'
FROM
igf_ap_fa_base_rec fa,
igf_aw_award awd,
igf_aw_awd_frml_det fdet
WHERE
fa.target_group = c_grp AND
awd.base_id = fa.base_id AND
fa.packaging_status IN ('AUTO_PACKAGED','REVISED') AND
fdet.formula_code = fa.target_group AND
awd.request_id IS NOT NULL AND
ROWNUM = 1;
SELECT
'Y'
FROM
igf_ap_fa_base_rec fa,
igf_aw_award awd,
igf_aw_awd_frml_det fdet
WHERE
fa.base_id = c_baseid AND
awd.base_id = fa.base_id AND
fa.packaging_status IN ('AUTO_PACKAGED','REVISED') AND
fdet.formula_code = fa.target_group AND
awd.request_id IS NOT NULL AND
ROWNUM = 1;
SELECT SUM((teach_periods.tp_perct_num * terms.ld_perct_num)/100) perct
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = p_base_id;
SELECT SUM((teach_periods.tp_perct_num * terms.ld_perct_num)/100) perct
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods,
igf_aw_awd_prd_term aprd,
igf_ap_fa_base_rec_all fa,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
GROUP BY base_id, ld_cal_type, ld_sequence_number) coaterms
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = p_base_id
AND coaterms.base_id = fa.base_id
AND fa.ci_cal_type = aprd.ci_cal_type
AND fa.ci_sequence_number = aprd.ci_sequence_number
AND coaterms.ld_cal_type = aprd.ld_cal_type
AND coaterms.ld_sequence_number = aprd.ld_sequence_number
AND aprd.award_prd_cd = p_awd_prd_code;
SELECT COUNT(*) common_terms
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = p_base_id;
SELECT COUNT(*) common_terms
FROM igf_aw_dp_terms terms,
igf_aw_dp_teach_prds teach_periods,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
igf_ap_fa_base_rec_all fa,
igf_aw_awd_prd_term aprd
WHERE terms.adplans_id = p_adplans_id
AND terms.adterms_id = teach_periods.adterms_id
AND coaterms.ld_cal_type = terms.ld_cal_type
AND coaterms.ld_sequence_number = terms.ld_sequence_number
AND coaterms.base_id = p_base_id
AND coaterms.base_id = fa.base_id
AND fa.ci_cal_type = aprd.ci_cal_type
AND fa.ci_sequence_number = aprd.ci_sequence_number
AND aprd.award_prd_cd = p_awd_prd_code
AND coaterms.ld_cal_type = aprd.ld_cal_type
AND coaterms.ld_sequence_number = aprd.ld_sequence_number;
PROCEDURE update_award_app_trans( p_award_id IN NUMBER,
p_base_id IN NUMBER)
IS
------------------------------------------------------------------
--Created by : sjadhav, Oracle India
--Date created: 4-Dec-2003
--
--Purpose: Update Application Transaction Number in AWARD table
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
-------------------------------------------------------------------
CURSOR cur_active_isir(
cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
) IS
SELECT transaction_num
FROM igf_ap_isir_matched_all
WHERE base_id = cp_base_id
AND NVL(active_isir,'N') = 'Y';
SELECT *
FROM igf_aw_award
WHERE award_id = p_award_id;
igf_aw_award_pkg.update_row(
x_mode => 'R',
x_rowid => rec.row_id,
x_award_id => rec.award_id,
x_fund_id => rec.fund_id,
x_base_id => rec.base_id,
x_offered_amt => rec.offered_amt,
x_accepted_amt => rec.accepted_amt,
x_paid_amt => rec.paid_amt,
x_packaging_type => rec.packaging_type,
x_batch_id => rec.batch_id,
x_manual_update => rec.manual_update,
x_rules_override => 'N',
x_award_date => rec.award_date,
x_award_status => rec.award_status,
x_attribute_category => rec.attribute_category,
x_attribute1 => rec.attribute1,
x_attribute2 => rec.attribute2,
x_attribute3 => rec.attribute3,
x_attribute4 => rec.attribute4,
x_attribute5 => rec.attribute5,
x_attribute6 => rec.attribute6,
x_attribute7 => rec.attribute7,
x_attribute8 => rec.attribute8,
x_attribute9 => rec.attribute9,
x_attribute10 => rec.attribute10,
x_attribute11 => rec.attribute11,
x_attribute12 => rec.attribute12,
x_attribute13 => rec.attribute13,
x_attribute14 => rec.attribute14,
x_attribute15 => rec.attribute15,
x_attribute16 => rec.attribute16,
x_attribute17 => rec.attribute17,
x_attribute18 => rec.attribute18,
x_attribute19 => rec.attribute19,
x_attribute20 => rec.attribute20,
x_rvsn_id => rec.rvsn_id,
x_award_number_txt => rec.award_number_txt,
x_legacy_record_flag => NULL,
x_adplans_id => rec.adplans_id,
x_lock_award_flag => rec.lock_award_flag,
x_app_trans_num_txt => rec.app_trans_num_txt,
x_awd_proc_status_code => rec.awd_proc_status_code,
x_notification_status_code => rec.notification_status_code,
x_notification_status_date => rec.notification_status_date,
x_publish_in_ss_flag => rec.publish_in_ss_flag
);
fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_AWARD_APP_TRANS '||SQLERRM);
END update_award_app_trans;