[Home] [Help]
57: || veramach 30-Jun-2004 bug 3709109 - Added call to function check_disb to enforce the rule that FWS funds can
58: || have only one disbursement per term
59: || veramach 11-Jun-2004 bug # 3684031 Added a check so that packaging does not put holds on any award-packaging, as per
60: || current logic, does not overaward. Single-fund packaging does impose overaward holds if it overawards.
61: || veramach 04-Mar-2004 bug # 3484438 - Changed cursor cur_new_awards to properly join on igf_aw_fund_mast_all
62: || veramach 16-Feb-2004 bug # 3446214 - removed code from process_single_fund which uses award group.
63: || cdcruz 04-Dec-2003 FA 131 COD Updates
64: || Modified the pell wrapper used to calculate pell amount. one more return parameter added
65: || Pell Schedule Code
260: ||
261: || Pre-requisites
262: || The following tables have to be populated before calling this process
263: || igf_fa_base_rec
264: || igf_aw_fund_mast
265: || igf_aw_ssn_tp
266: ||
267: || The Cost of Attendance Process must be run before Running Packaging
268:
295: g_verif_stat VARCHAR2(10);
296: -- g_sf_packaging VARCHAR2(1) := 'F';
297: g_sf_packaging VARCHAR2(1);
298: g_allow_to_exceed igf_lookups_view.lookup_code%TYPE;
299: g_fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE;
300: g_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
301: g_ci_sequence igs_ca_inst_all.sequence_number%TYPE;
302: g_sf_fund igf_aw_fund_mast_all.fund_id%TYPE;
303:
298: g_allow_to_exceed igf_lookups_view.lookup_code%TYPE;
299: g_fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE;
300: g_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
301: g_ci_sequence igs_ca_inst_all.sequence_number%TYPE;
302: g_sf_fund igf_aw_fund_mast_all.fund_id%TYPE;
303:
304: g_pell_tab igf_gr_pell_calc.pell_tab := igf_gr_pell_calc.pell_tab();
305:
306: PARAM_ERR EXCEPTION;
312: TYPE std_awards IS RECORD(
313: award_id NUMBER,
314: award NUMBER,
315: fund_id NUMBER,
316: replace_fc igf_aw_fund_mast_all.replace_fc%TYPE,
317: update_need igf_aw_fund_mast_all.update_need%TYPE,
318: entitlement igf_aw_fund_mast_all.entitlement%TYPE,
319: fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
320: fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE
313: award_id NUMBER,
314: award NUMBER,
315: fund_id NUMBER,
316: replace_fc igf_aw_fund_mast_all.replace_fc%TYPE,
317: update_need igf_aw_fund_mast_all.update_need%TYPE,
318: entitlement igf_aw_fund_mast_all.entitlement%TYPE,
319: fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
320: fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE
321: );
314: award NUMBER,
315: fund_id NUMBER,
316: replace_fc igf_aw_fund_mast_all.replace_fc%TYPE,
317: update_need igf_aw_fund_mast_all.update_need%TYPE,
318: entitlement igf_aw_fund_mast_all.entitlement%TYPE,
319: fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
320: fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE
321: );
322:
316: replace_fc igf_aw_fund_mast_all.replace_fc%TYPE,
317: update_need igf_aw_fund_mast_all.update_need%TYPE,
318: entitlement igf_aw_fund_mast_all.entitlement%TYPE,
319: fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE,
320: fm_fc_methd igf_aw_fund_mast_all.fm_fc_methd%TYPE
321: );
322:
323:
324: TYPE std_aid IS RECORD(
330: TYPE std_aid_tab IS TABLE OF std_aid;
331:
332: TYPE fund_awd IS RECORD(
333: base_id igf_ap_fa_base_rec_all.base_id%TYPE,
334: fund_id igf_aw_fund_mast_all.fund_id%TYPE,
335: awd_prct NUMBER
336: );
337:
338: TYPE std_fund_awd IS TABLE OF fund_awd;
432: CURSOR cur_get_fund (p_fund_id NUMBER)
433: IS
434: SELECT fcat.fed_fund_code
435: FROM igf_aw_fund_cat fcat,
436: igf_aw_fund_mast fmast
437: WHERE fcat.fund_code = fmast.fund_code
438: AND fmast.fund_id = p_fund_id;
439:
440: get_fund_rec cur_get_fund%ROWTYPE;
455: p_fund_id NUMBER
456: ) IS
457: SELECT fcat.sys_fund_type
458: FROM igf_aw_fund_cat fcat,
459: igf_aw_fund_mast fmast
460: WHERE fcat.fund_code = fmast.fund_code
461: AND fmast.fund_id = p_fund_id;
462:
463: get_fund_rec cur_get_fund_type%ROWTYPE;
469: RETURN get_fund_rec.sys_fund_type;
470: END get_sys_fund_type;
471:
472: FUNCTION get_disb_round_factor(
473: p_fund_id IN igf_aw_fund_mast.fund_id%TYPE
474: )
475: RETURN VARCHAR2
476: IS
477: /*
484: || Change History :
485: || Who WHEN What
486: || (reverse chronological order - newest change first)
487: */
488: CURSOR c_get_disb_round_factor(cp_fund_id igf_aw_fund_mast.fund_id%TYPE)
489: IS
490: SELECT disb_rounding_code
491: FROM igf_aw_fund_mast
492: WHERE fund_id = cp_fund_id;
487: */
488: CURSOR c_get_disb_round_factor(cp_fund_id igf_aw_fund_mast.fund_id%TYPE)
489: IS
490: SELECT disb_rounding_code
491: FROM igf_aw_fund_mast
492: WHERE fund_id = cp_fund_id;
493:
494: l_get_disb_round_factor c_get_disb_round_factor%ROWTYPE;
495: l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
491: FROM igf_aw_fund_mast
492: WHERE fund_id = cp_fund_id;
493:
494: l_get_disb_round_factor c_get_disb_round_factor%ROWTYPE;
495: l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
496: BEGIN
497: OPEN c_get_disb_round_factor(p_fund_id);
498: FETCH c_get_disb_round_factor INTO l_get_disb_round_factor;
499:
730: CURSOR c_awards IS
731: SELECT awd.ROWID row_id,
732: awd.*
733: FROM igf_aw_award_all awd,
734: igf_aw_fund_mast_all fmast
735: WHERE fmast.ci_cal_type = l_ci_cal_type
736: AND fmast.ci_sequence_number = l_ci_sequence_number
737: AND awd.fund_id = fmast.fund_id
738: AND awd.base_id = p_base_id
1423: WHERE award_id = cp_award_id;
1424:
1425: -- Get Fund Code from fund_id
1426: CURSOR c_fund_code(
1427: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
1428: ) IS
1429: SELECT fund_code
1430: FROM igf_aw_fund_mast_all
1431: WHERE fund_id = cp_fund_id;
1426: CURSOR c_fund_code(
1427: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
1428: ) IS
1429: SELECT fund_code
1430: FROM igf_aw_fund_mast_all
1431: WHERE fund_id = cp_fund_id;
1432: l_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
1433:
1434: lv_rowid ROWID;
1428: ) IS
1429: SELECT fund_code
1430: FROM igf_aw_fund_mast_all
1431: WHERE fund_id = cp_fund_id;
1432: l_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
1433:
1434: lv_rowid ROWID;
1435: l_amount igf_aw_award_all.offered_amt%TYPE;
1436:
1796: l_special_disb_no NUMBER := 0;
1797: l_disb_limit1 NUMBER := 0;
1798: l_disb_limit2 NUMBER := 0;
1799: l_step NUMBER := 0;
1800: l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
1801:
1802: TYPE l_disb_structure IS RECORD(
1803: fund_id igf_aw_fund_mast.fund_id%TYPE,
1804: disb_num NUMBER,
1799: l_step NUMBER := 0;
1800: l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
1801:
1802: TYPE l_disb_structure IS RECORD(
1803: fund_id igf_aw_fund_mast.fund_id%TYPE,
1804: disb_num NUMBER,
1805: disb_amt NUMBER
1806: );
1807: TYPE l_disb_structure_tab IS TABLE OF l_disb_structure INDEX BY BINARY_INTEGER;
2148:
2149: l_awd_tot c_awd_tot%ROWTYPE;
2150:
2151: -- Get the details of the Fund which are necessary while creating Awards and Disbursements
2152: CURSOR c_fmast ( x_fund_id igf_aw_fund_mast.fund_id%TYPE ) IS
2153: SELECT fmast.pckg_awd_stat,
2154: fmast.ci_cal_type,
2155: fmast.ci_sequence_number,
2156: fmast.nslds_disb_da,
2162: fmast.entitlement,
2163: fcat.fund_source,
2164: fcat.fed_fund_code,
2165: fcat.sys_fund_type
2166: FROM igf_aw_fund_mast_all fmast,
2167: igf_aw_fund_cat_all fcat
2168: WHERE fund_id = x_fund_id
2169: AND fcat.fund_code = fmast.fund_code;
2170:
4359: FUNCTION awardsExist(
4360: p_ci_cal_type igs_ca_inst.cal_type%TYPE,
4361: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
4362: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4363: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4364: p_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
4365: ) RETURN BOOLEAN AS
4366: ------------------------------------------------------------------
4367: --Created by : veramach, Oracle India
4430: END IF;
4431: END awardsExist;
4432:
4433: FUNCTION isOriginated(
4434: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4435: p_award_id igf_aw_award_all.award_id%TYPE
4436: ) RETURN BOOLEAN AS
4437: ------------------------------------------------------------------
4438: --Created by : veramach, Oracle India
4759: ORDER BY prty.fund_order_num, awd.award_id;
4760:
4761: -- Get fund_code
4762: CURSOR c_fund(
4763: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
4764: ) IS
4765: SELECT fund_code
4766: FROM igf_aw_fund_mast_all
4767: WHERE fund_id = cp_fund_id;
4762: CURSOR c_fund(
4763: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
4764: ) IS
4765: SELECT fund_code
4766: FROM igf_aw_fund_mast_all
4767: WHERE fund_id = cp_fund_id;
4768: l_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
4769:
4770: BEGIN
4764: ) IS
4765: SELECT fund_code
4766: FROM igf_aw_fund_mast_all
4767: WHERE fund_id = cp_fund_id;
4768: l_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
4769:
4770: BEGIN
4771:
4772: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
4857: p_ci_cal_type igs_ca_inst.cal_type%TYPE,
4858: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
4859: p_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
4860: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4861: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4862: p_lock_award_flag igf_aw_fund_mast_all.lock_award_flag%TYPE,
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4858: p_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
4859: p_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
4860: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4861: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4862: p_lock_award_flag igf_aw_fund_mast_all.lock_award_flag%TYPE,
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4866: p_max_award_amt igf_aw_fund_mast_all.max_award_amt%TYPE,
4859: p_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
4860: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4861: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4862: p_lock_award_flag igf_aw_fund_mast_all.lock_award_flag%TYPE,
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4866: p_max_award_amt igf_aw_fund_mast_all.max_award_amt%TYPE,
4867: p_min_award_amt igf_aw_fund_mast_all.min_award_amt%TYPE,
4860: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4861: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4862: p_lock_award_flag igf_aw_fund_mast_all.lock_award_flag%TYPE,
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4866: p_max_award_amt igf_aw_fund_mast_all.max_award_amt%TYPE,
4867: p_min_award_amt igf_aw_fund_mast_all.min_award_amt%TYPE,
4868: p_seq_no NUMBER
4862: p_lock_award_flag igf_aw_fund_mast_all.lock_award_flag%TYPE,
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4866: p_max_award_amt igf_aw_fund_mast_all.max_award_amt%TYPE,
4867: p_min_award_amt igf_aw_fund_mast_all.min_award_amt%TYPE,
4868: p_seq_no NUMBER
4869: ) AS
4870: ------------------------------------------------------------------
4863: p_re_pkg_verif_flag igf_aw_fund_mast_all.re_pkg_verif_flag%TYPE,
4864: p_donot_repkg_if_code igf_aw_fund_mast_all.donot_repkg_if_code%TYPE,
4865: p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
4866: p_max_award_amt igf_aw_fund_mast_all.max_award_amt%TYPE,
4867: p_min_award_amt igf_aw_fund_mast_all.min_award_amt%TYPE,
4868: p_seq_no NUMBER
4869: ) AS
4870: ------------------------------------------------------------------
4871: --Created by : veramach, Oracle India
4886: cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
4887: cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
4888: cp_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
4889: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
4890: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
4891: ) IS
4892: SELECT awd.award_id,
4893: awd.offered_amt,
4894: awd.paid_amt,
4901: fmast.over_award_amt,
4902: fmast.over_award_perct,
4903: fmast.available_amt,
4904: fmast.remaining_amt
4905: FROM igf_aw_award_all awd, igf_aw_fund_mast_all fmast
4906: WHERE awd.base_id = cp_base_id
4907: AND awd.fund_id = cp_fund_id
4908: AND awd.award_status IN('OFFERED', 'ACCEPTED', 'CANCELLED')
4909: AND fmast.fund_id = awd.fund_id
4934: fmast.over_award_amt,
4935: fmast.over_award_perct,
4936: fmast.available_amt,
4937: fmast.remaining_amt
4938: FROM igf_aw_award_all awd, igf_aw_fund_mast_all fmast
4939: WHERE awd.base_id = cp_base_id
4940: AND awd.fund_id = cp_fund_id
4941: AND awd.award_status IN('OFFERED', 'ACCEPTED', 'CANCELLED')
4942: AND fmast.fund_id = awd.fund_id
4980: l_sl_number NUMBER(15);
4981:
4982: -- Check for locked awards for the Student in the current fund
4983: CURSOR c_chk_locked_award(
4984: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
4985: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
4986: )
4987: IS
4988: SELECT 'x'
4995: l_chk_locked_award c_chk_locked_award%ROWTYPE;
4996:
4997: -- Returns all existing awards which are not locked
4998: CURSOR c_get_unlocked_award (
4999: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5000: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
5001: )
5002: IS
5003: SELECT awd_t.rowid, awd_t.*
5009: base_id = cp_base_id;
5010:
5011:
5012: -- Get the Over Award records from the temporary table.
5013: CURSOR c_ov_fund( x_fund_id igf_aw_fund_mast.fund_id%TYPE ) IS
5014: SELECT row_id
5015: FROM igf_aw_award_t awdt
5016: WHERE fund_id = x_fund_id
5017: AND flag = 'OV'
5020: l_ov_fund c_ov_fund%ROWTYPE;
5021: l_overaward NUMBER;
5022:
5023: CURSOR c_pell_cnt(
5024: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5025: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
5026: ) IS
5027: SELECT SUM(DECODE(awd.award_status,'CANCELLED',1,0)) cancelled_awd,
5028: SUM(DECODE(awd.award_status,'OFFERED',1,'ACCEPTED',1,0)) off_acc_awd
5035: AND awdt.award_id = awd.award_id;
5036: l_pell_cnt c_pell_cnt%ROWTYPE;
5037:
5038: CURSOR c_lock_pell_awd(
5039: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5040: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
5041: ) IS
5042: SELECT awdt.ROWID row_id,
5043: awdt.*
5050: AND awd.award_status = 'CANCELLED';
5051: l_lock_pell_awd c_lock_pell_awd%ROWTYPE;
5052:
5053: CURSOR c_lock_pell_awd1(
5054: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5055: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
5056: cp_award_id igf_aw_award_all.award_id%TYPE
5057: ) IS
5058: SELECT awdt.ROWID row_id,
5063: AND awdt.fund_id = cp_fund_id
5064: AND awdt.process_id = l_process_id;
5065:
5066: CURSOR c_latest_awd(
5067: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5068: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
5069: ) IS
5070: SELECT awd.award_id
5071: FROM igf_aw_award_t_all awdt,
5630: fmdet.adplans_id adplans_id,
5631: fmast.re_pkg_verif_flag re_pkg_verif_flag,
5632: NVL(fmdet.lock_award_flag,fmast.lock_award_flag) lock_award_flag
5633: FROM igf_aw_awd_frml_det fmdet,
5634: igf_aw_fund_mast_all fmast
5635: WHERE fmdet.formula_code = x_group_code
5636: AND fmdet.ci_cal_type = x_ci_cal_type
5637: AND fmdet.ci_sequence_number = x_ci_sequence_number
5638: AND fmdet.fund_id = fmast.fund_id
5656: 0 pe_group_id,
5657: g_plan_id adplans_id,
5658: fmast.re_pkg_verif_flag re_pkg_verif_flag,
5659: NVL(g_lock_award,fmast.lock_award_flag) lock_award_flag
5660: FROM igf_aw_fund_mast_all fmast
5661: WHERE fmast.discontinue_fund <> 'Y'
5662: AND g_sf_packaging = 'T'
5663: AND fund_id = g_sf_fund
5664: ORDER BY 3;
5665:
5666: l_fund_ld c_fund_ld%ROWTYPE;
5667:
5668: -- Get the Over Award records from the temporary table.
5669: CURSOR c_ovr_awd( x_fund_id igf_aw_fund_mast.fund_id%TYPE ) IS
5670: SELECT row_id
5671: FROM igf_aw_award_t awdt
5672: WHERE fund_id = x_fund_id
5673: AND flag = 'OV'
5681: -- Remove the funds from the students list who does not belong to the Person ID group defined at the Traget group funds.
5682:
5683: CURSOR c_rmv_funds(
5684: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
5685: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5686: cp_seq_no igf_aw_awd_frml_det_all.seq_no%TYPE,
5687: cp_formula_code igf_aw_awd_frml_det_all.formula_code%TYPE,
5688: cp_ci_cal_type igf_aw_awd_frml_det_all.ci_cal_type%TYPE,
5689: cp_ci_sequence_number igf_aw_awd_frml_det_all.ci_sequence_number%TYPE
5689: cp_ci_sequence_number igf_aw_awd_frml_det_all.ci_sequence_number%TYPE
5690: ) IS
5691: SELECT fmdet.seq_no, fmdet.pe_group_id, grp.group_cd
5692: FROM igf_aw_awd_frml_det fmdet,
5693: igf_aw_fund_mast fmast,
5694: igs_pe_all_persid_group_v grp
5695: WHERE fmast.fund_id = cp_fund_id
5696: AND fmdet.fund_id = fmast.fund_id
5697: AND fmdet.seq_no = cp_seq_no
5763: fmast.lock_award_flag,
5764: fmast.re_pkg_verif_flag,
5765: fmast.donot_repkg_if_code
5766: FROM igf_aw_award_all awd,
5767: igf_aw_fund_mast_all fmast,
5768: igf_aw_awd_disb_all disb,
5769: igf_aw_awd_prd_term aprd
5770: WHERE fmast.fund_id = awd.fund_id
5771: AND awd.base_id = cp_base_id
5832: l_ant_prog_rec c_get_ant_prog%ROWTYPE;
5833:
5834: -- Gets all the existing awards for a particular fund for a particular student
5835: CURSOR c_get_cancel_awds(
5836: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
5837: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
5838: cp_process_id igf_aw_award_t_all.process_id%TYPE
5839: )
5840: IS
6590: || Known limitations, enhancements or remarks :
6591: || Change History :
6592: || Who WHEN What
6593: || (reverse chronological order - newest change first)
6594: || veramach 04-Mar-2004 bug # 3484438 - Changed cursor cur_new_awards to properly join on igf_aw_fund_mast_all
6595: || veramach 20-NOV-2003 FA 125 - Modified cur_new_awards to use adplans_id instead of fund_id
6596: */
6597:
6598: -- Get all the funds which were rejected earlier for reconsidering
6647: x_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
6648: ) IS
6649: SELECT COUNT(awdt.fund_id) ftotal
6650: FROM igf_aw_award_t awdt,
6651: igf_aw_fund_mast_all fmast,
6652: igf_aw_dp_terms adterms,
6653: igf_aw_awd_prd_term aprd
6654: WHERE fmast.fund_code = x_fund_code
6655: AND awdt.base_id = x_base_id
6674: ) IS
6675: SELECT COUNT(fmast.fund_code) ftotal
6676: FROM igf_aw_award_all awd,
6677: igf_aw_awd_disb_all disb,
6678: igf_aw_fund_mast_all fmast,
6679: igf_aw_awd_prd_term aprd
6680: WHERE fmast.fund_code = x_fund_code
6681: AND awd.base_id = x_base_id
6682: AND awd.fund_id = fmast.fund_id
6690: AND disb.trans_type <> 'C';
6691:
6692: prior_awards_rec cur_prior_awards%ROWTYPE;
6693:
6694: CURSOR c_fund_cd(cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
6695: SELECT fund_code
6696: FROM igf_aw_fund_mast
6697: WHERE fund_id = cp_fund_id;
6698:
6692: prior_awards_rec cur_prior_awards%ROWTYPE;
6693:
6694: CURSOR c_fund_cd(cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
6695: SELECT fund_code
6696: FROM igf_aw_fund_mast
6697: WHERE fund_id = cp_fund_id;
6698:
6699: l_fund_cd c_fund_cd%ROWTYPE;
6700:
7568: 'REVIEW', 3,
7569: 'DO_NOT_REPKG', 4
7570: ) status_order
7571: FROM igf_aw_award_all awd,
7572: igf_aw_fund_mast_all fmast
7573: WHERE fmast.ci_cal_type = p_ci_cal_type
7574: AND fmast.ci_sequence_number = p_ci_sequence_number
7575: AND awd.fund_id = fmast.fund_id
7576: AND awd.base_id = p_base_id
7649: NVL(fm.entitlement,'N') entitlement,
7650: fcat.fed_fund_code fed_fund_code,
7651: fm.fm_fc_methd fm_fc_methd
7652: FROM igf_aw_award_all awd,
7653: igf_aw_fund_mast_all fm,
7654: igf_aw_fund_cat_all fcat
7655: WHERE base_id = cp_base_id
7656: AND awd.fund_id = fm.fund_id
7657: AND fm.fund_code = fcat.fund_code
7707: l_rem_cnt NUMBER;
7708: l_coa_months NUMBER;
7709: l_skip_fund BOOLEAN;
7710: l_fund_fail BOOLEAN;
7711: l_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
7712: l_aid NUMBER(12,2);
7713: l_accepted_amt NUMBER(12,2);
7714: l_seq_no NUMBER;
7715: l_pers_num igf_ap_fa_con_v.person_number%TYPE;
8357: fcat.fund_source fund_source,
8358: awt.award_id,
8359: awt.offered_amt
8360: FROM igf_aw_award_t awt,
8361: igf_aw_fund_mast_all fmast,
8362: igf_aw_fund_cat_all fcat
8363: WHERE awt.fund_id = fmast.fund_id
8364: AND fmast.fund_code = fcat.fund_code
8365: AND awt.base_id = x_base_id
8379: fmast.fm_fc_methd,
8380: fcat.fund_source fund_source,
8381: awt1.award_id,
8382: awt1.offered_amt
8383: FROM igf_aw_fund_mast_all fmast ,
8384: igf_aw_award_t awt1,
8385: igf_aw_fund_cat_all fcat
8386: WHERE g_sf_packaging = 'T'
8387: AND fmast.fund_id = g_sf_fund
8394:
8395: l_fund c_fund%ROWTYPE;
8396:
8397: -- Retrieves fund Properties
8398: CURSOR c_fmast ( cp_fund_id igf_aw_fund_mast.fund_id%TYPE ) IS
8399: SELECT fmast.*,
8400: fcat.fund_type,
8401: fcat.fund_source,
8402: fcat.fed_fund_code,
8402: fcat.fed_fund_code,
8403: fcat.sys_fund_type,
8404: 'Y' emulate_fed,
8405: DECODE(fmast.fm_fc_methd,'INSTITUTIONAL','Y','N') inst_method
8406: FROM igf_aw_fund_mast_all fmast ,
8407: igf_aw_fund_cat fcat
8408: WHERE fmast.fund_id = cp_fund_id
8409: AND fcat.fund_code = fmast.fund_code;
8410:
8425:
8426: l_temp c_temp%ROWTYPE;
8427:
8428: -- Check whether the fund is resulted in over award or not
8429: CURSOR c_cur_ovr_awd ( x_fund_id igf_aw_fund_mast.fund_id%TYPE ) IS
8430: SELECT awdt.*
8431: FROM igf_aw_award_t awdt
8432: WHERE awdt.fund_id = x_fund_id
8433: AND flag = 'OV'
8453: ) IS
8454: SELECT SUM(disb.disb_gross_amt) yr_total
8455: FROM igf_aw_awd_disb_all disb,
8456: igf_aw_award_all awd,
8457: igf_aw_fund_mast_all fmast
8458: WHERE disb.award_id = awd.award_id
8459: AND fmast.fund_id = awd.fund_id
8460: AND fmast.fund_id = cp_fund_id
8461: AND awd.base_id = cp_base_id
8477: --
8478: -- Gets the max amt + max terms the student got a fund in a lifetime
8479: --
8480: -- ???? Common Terms
8481: CURSOR cur_max_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
8482: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
8483: IS
8484: SELECT
8485: NVL(SUM(NVL(disb.disb_gross_amt,0)),0) lf_total,
8486: COUNT(DISTINCT awd.award_id) lf_count
8487: FROM
8488: igf_aw_awd_disb_all disb,
8489: igf_aw_award_all awd,
8490: igf_aw_fund_mast_all fmast,
8491: igf_ap_fa_base_rec_all fabase
8492: WHERE fmast.fund_code = cp_fund_code
8493: AND disb.award_id = awd.award_id
8494: AND awd.fund_id = fmast.fund_id
8501:
8502: --
8503: -- Cursor to Aggregate Award and Count
8504: --
8505: CURSOR cur_agg_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
8506: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
8507: IS
8508: SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
8509: COUNT(awd.award_id) lf_count
8507: IS
8508: SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
8509: COUNT(awd.award_id) lf_count
8510: FROM igf_aw_award_all awd,
8511: igf_aw_fund_mast_all fmast,
8512: igf_ap_fa_base_rec fabase,
8513: igf_ap_batch_aw_map_all bam
8514: WHERE fmast.fund_code = cp_fund_code
8515: AND awd.fund_id = fmast.fund_id
8552: cp_base_id igf_aw_award_t.base_id%TYPE ,
8553: cp_process_id igf_aw_award_t.process_id%TYPE
8554: ) IS
8555: SELECT COUNT(*) cnt, fm.fund_id
8556: FROM igf_aw_fund_mast_all fm,
8557: igf_aw_fund_cat_all ca
8558: WHERE fm.fund_code = ca.fund_code
8559: AND ca.fed_fund_code ='FSEOG'
8560: AND fm.fund_id IN ( SELECT awdt.fund_id
8613: -- Get the total number of NON Simulated Awards of a student for a fund
8614: -- This cursor is used to know if the person is having awards from the
8615: -- fund, if there are awards then do not re-package
8616: -- -- ???? Common Terms
8617: CURSOR cur_fund_awd_exist (cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
8618: cp_base_id igf_aw_award_all.base_id%TYPE)
8619: IS
8620: SELECT COUNT(award_id) cnt
8621: FROM igf_aw_award_all
8634: fed_fund_code,
8635: NVL(awd.offered_amt,0) offered_amt ,
8636: award_date
8637: FROM igf_aw_award_all awd,
8638: igf_aw_fund_mast_all fm,
8639: igf_aw_fund_cat_all fcat
8640: WHERE awd.award_status IN ('OFFERED','ACCEPTED')
8641: AND awd.base_id = cp_base_id
8642: AND awd.fund_id = fm.fund_id
8654: fed_fund_code,
8655: NVL(awdt.offered_amt,0),
8656: SYSDATE award_date
8657: FROM igf_aw_Award_t awdt,
8658: igf_aw_fund_mast_all fmt,
8659: igf_aw_fund_cat_all fcat
8660: WHERE awdt.base_id = cp_base_id
8661: AND awdt.process_id = cp_process_id
8662: AND awdt.fund_id = fmt.fund_id
8687: lc_get_term_prsnt c_get_term_prsnt%ROWTYPE;
8688:
8689:
8690: TYPE awd_grp_rec IS RECORD(
8691: fund_id igf_aw_fund_mast.fund_id%TYPE,
8692: fund_Code igf_aw_fund_mast_v.fund_code%TYPE,
8693: offered_amt igf_aw_award_t.offered_amt%TYPE,
8694: accepted_amt igf_aw_award_t.accepted_Amt%TYPE,
8695: total NUMBER,
8688:
8689:
8690: TYPE awd_grp_rec IS RECORD(
8691: fund_id igf_aw_fund_mast.fund_id%TYPE,
8692: fund_Code igf_aw_fund_mast_v.fund_code%TYPE,
8693: offered_amt igf_aw_award_t.offered_amt%TYPE,
8694: accepted_amt igf_aw_award_t.accepted_Amt%TYPE,
8695: total NUMBER,
8696: seq_no igf_aw_awd_frml_det.seq_no%TYPE,
8757: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
8758: cp_process_id igf_aw_award_t.process_id%TYPE
8759: ) IS
8760: SELECT fmast.fm_fc_methd fm_fc_methd
8761: FROM igf_aw_fund_mast_all fmast,
8762: igf_aw_award_all awd
8763: WHERE awd.fund_id = fmast.fund_id
8764: AND awd.base_id=cp_base_id
8765: AND fmast.fm_fc_methd = 'FEDERAL'
11233: awdt.adplans_id,
11234: awdt.award_id,
11235: awdt.lock_award_flag
11236: FROM igf_aw_award_t awdt ,
11237: igf_aw_fund_mast fmast,
11238: (SELECT MIN(awdt1.temp_char_val1) seq_no,
11239: awdt1.fund_id fund_id
11240: FROM igf_aw_award_t awdt1
11241: WHERE awdt1.process_id = l_process_id
11269: awdt.adplans_id,
11270: awdt.award_id,
11271: awdt.lock_award_flag
11272: FROM igf_aw_award_t awdt,
11273: igf_aw_fund_mast fmast
11274: WHERE awdt.process_id = l_process_id
11275: AND awdt.base_id = l_fabase.base_id
11276: AND awdt.fund_id = fmast.fund_id
11277: AND awdt.flag = 'AW'
11685: || (reverse chronological order - newest change first)
11686: */
11687:
11688: CURSOR c_awd_det(
11689: x_fund_id igf_aw_fund_mast.fund_id%TYPE,
11690: x_process_id NUMBER,
11691: x_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
11692: x_seq_no NUMBER,
11693: x_award_id igf_aw_award_all.award_id%TYPE
12654: app_exception.raise_exception;
12655: END get_disbursements;
12656:
12657: PROCEDURE add_todo(
12658: p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
12659: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
12660: ) IS
12661: /*
12662: || Created By : skoppula
12673: -- Get all the To Do Items which needs to be assigned to the student
12674: -- Fetch all To Do Items defined at the fund level and remove all To Do which are already assigned to the student
12675: CURSOR c_fnd_todo(
12676: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
12677: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
12678: ) IS
12679: SELECT tdmst.todo_number,
12680: tdmst.required_for_application,
12681: tdmst.max_attempt,
13073: l_ap igf_aw_award_prd.award_prd_desc%TYPE;
13074:
13075: -- Get fund code
13076: CURSOR c_fund(
13077: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
13078: ) IS
13079: SELECT fund_code
13080: FROM igf_aw_fund_mast_all
13081: WHERE fund_id = cp_fund_id;
13076: CURSOR c_fund(
13077: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
13078: ) IS
13079: SELECT fund_code
13080: FROM igf_aw_fund_mast_all
13081: WHERE fund_id = cp_fund_id;
13082: l_fund igf_aw_fund_mast_all.fund_code%TYPE;
13083:
13084: -- Get person number
13078: ) IS
13079: SELECT fund_code
13080: FROM igf_aw_fund_mast_all
13081: WHERE fund_id = cp_fund_id;
13082: l_fund igf_aw_fund_mast_all.fund_code%TYPE;
13083:
13084: -- Get person number
13085: CURSOR c_person_number(
13086: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
13297: SUM(awt.offered_amt * awt.temp_num_val1/100) offered_amt,
13298: fund_code,
13299: adplans_id
13300: FROM igf_aw_award_t awt,
13301: igf_aw_fund_mast_all fm
13302: WHERE fm.fund_id=awt.fund_id
13303: AND awt.flag = 'FL'
13304: AND awt.process_id = l_process_id
13305: GROUP BY fund_code,
13307:
13308: l_summ_rec c_summary_awd%ROWTYPE;
13309:
13310: -- Get the details of
13311: CURSOR c_fund_dtls( cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE) IS
13312: SELECT fm.auto_pkg,
13313: fm.fund_code,
13314: fcat.fed_fund_code,
13315: fcat.fund_source,
13315: fcat.fund_source,
13316: fm.entitlement,
13317: fm.min_award_amt,
13318: fm.max_award_amt
13319: FROM igf_aw_fund_mast fm,
13320: igf_aw_fund_cat fcat
13321: WHERE fm.fund_id = cp_fund_id
13322: AND fm.fund_code = fcat.fund_code;
13323: