[Home] [Help]
98: RETURN get_fund_rec.fed_fund_code;
99:
100: END get_fed_fund_code;
101:
102: FUNCTION isRepackaging(p_award_id IN igf_aw_award_all.award_id%TYPE)
103: RETURN BOOLEAN
104: IS
105: ------------------------------------------------------------------
106: -- Created by : museshad
114: -- Change History:
115: -------------------------------------------------------------------
116: -- Who When What
117: ------------------------------------------------------------------
118: CURSOR c_chk_repkg(cp_award_id IN igf_aw_award_all.award_id%TYPE)
119: IS
120: SELECT 'X'
121: FROM igf_aw_awd_disb_all
122: WHERE award_id = cp_award_id;
153: */
154:
155: -- Get all disbursements more than cp_disb_num in the award
156: CURSOR c_disb_cancel(
157: cp_award_id igf_aw_award_all.award_id%TYPE,
158: cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
159: )
160: IS
161: SELECT *
256: -------------------------------------------------------------------
257: --
258:
259: CURSOR cur_get_fed_fund_code(
260: cp_award_id igf_aw_award_all.award_id%TYPE
261: ) IS
262: SELECT fed_fund_code,ci_cal_type,ci_sequence_number
263: FROM igf_aw_award_v
264: WHERE award_id = cp_award_id;
264: WHERE award_id = cp_award_id;
265: l_get_fed_fund_code cur_get_fed_fund_code%ROWTYPE;
266:
267: CURSOR c_disb(
268: cp_award_id igf_aw_award_all.award_id%TYPE,
269: cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
270: ) IS
271: SELECT rowid row_id,
272: disb.*
708: PROCEDURE round_off_disbursements(
709: p_fund_id IN igf_aw_award_t_all.fund_id%TYPE,
710: p_award_id IN igf_aw_award_t_all.award_id%TYPE,
711: p_offered_amt IN igf_aw_award_t_all.offered_amt%TYPE,
712: p_award_status IN igf_aw_award_all.award_status%TYPE,
713: p_dist_plan_code IN igf_aw_awd_dist_plans.dist_plan_method_code%TYPE,
714: p_disb_count IN NUMBER
715: )
716: IS
729: */
730:
731: -- Returns all the disbursements for an award
732: -- The ORDER BY clause ensures that the disbursements are returned in the order of their creation
733: CURSOR cur_get_all_disb (p_award_id igf_aw_award_all.award_id%TYPE)
734: IS
735: SELECT disb.rowid, disb.*
736: FROM igf_aw_awd_disb disb
737: WHERE
1509: AND aprd.ld_sequence_number = terms.ld_sequence_number;
1510:
1511: -- Get a specific disbursment for an award
1512: CURSOR c_disb(
1513: cp_award_id igf_aw_award_all.award_id%TYPE,
1514: cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
1515: ) IS
1516: SELECT rowid row_id,
1517: disb.*
1520: AND disb_num = cp_disb_num;
1521:
1522: l_disb c_disb%ROWTYPE;
1523:
1524: CURSOR cur_get_base ( p_award_id igf_aw_award_all.award_id%TYPE) IS
1525: SELECT base_id
1526: FROM igf_aw_award_all
1527: WHERE award_id = p_award_id;
1528:
1522: l_disb c_disb%ROWTYPE;
1523:
1524: CURSOR cur_get_base ( p_award_id igf_aw_award_all.award_id%TYPE) IS
1525: SELECT base_id
1526: FROM igf_aw_award_all
1527: WHERE award_id = p_award_id;
1528:
1529: get_base_rec cur_get_base%ROWTYPE;
1530:
2826: disb.ld_sequence_number,
2827: disb.base_attendance_type_code,
2828: SUM(disb.disb_gross_amt) term_total
2829: FROM igf_aw_awd_disb_all disb,
2830: igf_aw_award_all awd
2831: WHERE awd.award_id = disb.award_id
2832: AND awd.award_id = p_award_id
2833: GROUP BY disb.ld_cal_type,disb.ld_sequence_number,disb.base_attendance_type_code;
2834:
3019:
3020: END update_awd_cancell_to_offer;
3021:
3022:
3023: FUNCTION check_disbdts ( p_award_id IN igf_aw_award_all.award_id%TYPE,
3024: p_ld_seq_number IN NUMBER)
3025: RETURN VARCHAR2
3026: IS
3027:
3041: -- sjadhav
3042: -- Bug 2387496
3043: -- Added new function to check dates
3044:
3045: CURSOR cur_disb_num ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3046: SELECT disb_num,disb_date,trans_type
3047: FROM
3048: igf_aw_awd_disb_all
3049: WHERE
3054: disb_num;
3055:
3056: disb_num_rec cur_disb_num%ROWTYPE;
3057:
3058: CURSOR cur_disb_dat ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3059: SELECT disb_num,disb_date
3060: FROM
3061: igf_aw_awd_disb
3062: WHERE
3144: p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3145: p_fund_code IN igf_aw_fund_mast_all.fund_code%TYPE,
3146: p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
3147: p_person_number IN igf_aw_award_v.person_number%TYPE,
3148: p_award_id IN igf_aw_award_all.award_id%TYPE,
3149: p_act_isir IN VARCHAR2,
3150: p_ld_seq_number IN NUMBER,
3151: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE,
3152: p_chk_holds OUT NOCOPY VARCHAR2)
3202: NVL(SUM(NVL(disb.disb_gross_amt,0)),0) lf_total,
3203: COUNT(DISTINCT awd.award_id) lf_count
3204: FROM
3205: igf_aw_awd_disb_all disb,
3206: igf_aw_award_all awd,
3207: igf_aw_fund_mast_all fmast,
3208: igf_ap_fa_base_rec_all fabase
3209: WHERE fmast.fund_code = cp_fund_code
3210: AND disb.award_id = awd.award_id
3223: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3224: IS
3225: SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
3226: COUNT(awd.award_id) lf_count
3227: FROM igf_aw_award_all awd,
3228: igf_aw_fund_mast_all fmast,
3229: igf_ap_fa_base_rec fabase,
3230: igf_ap_batch_aw_map_all bam
3231: WHERE fmast.fund_code = cp_fund_code
3258: total_fund_awd_rec cur_total_fund_awd%ROWTYPE;
3259: --
3260: -- This cursor retrives Total Amount for a Award -
3261: --
3262: CURSOR cur_total_award_amt (p_award_id igf_aw_award_all.award_id%TYPE)
3263: IS
3264: SELECT
3265: SUM(disb.disb_gross_amt) total_award_amt
3266: FROM
3317:
3318: --
3319: -- Cursor to get total disbursements for award
3320: --
3321: CURSOR cur_get_count ( p_award_id igf_aw_award_all.award_id%TYPE)
3322: IS
3323: SELECT
3324: COUNT(ld_cal_type) disb_count
3325: FROM igf_aw_awd_disb
3326: WHERE
3327: award_id = p_award_id;
3328:
3329:
3330: CURSOR cur_disb_num ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3331: SELECT disb_num,disb_date,trans_type,disb_accepted_amt accepted_amt
3332: FROM
3333: igf_aw_awd_disb_all
3334: WHERE
3358: disb.ld_sequence_number,
3359: disb.base_attendance_type_code,
3360: SUM(disb.disb_gross_amt) term_total
3361: FROM igf_aw_awd_disb_all disb,
3362: igf_aw_award_all awd
3363: WHERE disb.trans_type <> 'C'
3364: AND awd.award_id = disb.award_id
3365: AND awd.award_id = p_award_id
3366: AND disb.ld_sequence_number = p_ld_seq_number
3873: -- Bug 2306310
3874: --
3875: -- Procedure to update show_on_bill flag based on the fund manager value
3876: --
3877: PROCEDURE update_bill_flag ( p_fund_id IN igf_aw_award_all.fund_id%TYPE,
3878: p_new_val IN igf_aw_fund_mast_all.show_on_bill%TYPE)
3879: IS
3880:
3881: --
3880:
3881: --
3882: -- Cursor to get awards of the fund
3883: --
3884: CURSOR cur_get_awd ( p_fund_id IN igf_aw_award_all.fund_id%TYPE )
3885: IS
3886: SELECT award_id
3887: FROM
3888: igf_aw_award
3892:
3893: --
3894: -- Cursor to get Planned Disbursements for the award
3895: --
3896: CURSOR cur_get_adisb ( p_award_id IN igf_aw_award_all.award_id%TYPE,
3897: p_new_val IN igf_aw_fund_mast_all.show_on_bill%TYPE)
3898: IS
3899: SELECT *
3900: FROM
3983:
3984: END update_bill_flag;
3985:
3986:
3987: FUNCTION delete_awd_disb ( p_award_id IN igf_aw_award_all.award_id%TYPE ,
3988: p_ld_seq_num IN igf_aw_awd_disb_all.ld_sequence_number%TYPE ,
3989: p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE )
3990: RETURN VARCHAR2
3991: IS
4005: --------------------------------------------------------------------------------------------
4006: --
4007: -- check if award has pell or loan record
4008: --
4009: CURSOR cur_pell_awd (p_award_id igf_aw_award_all.award_id%TYPE)
4010: IS
4011: SELECT
4012: COUNT(origination_id) awd_count
4013: FROM
4016: award_id = p_award_id;
4017:
4018: pell_awd_rec cur_pell_awd%ROWTYPE;
4019:
4020: CURSOR cur_loan_awd (p_award_id igf_aw_award_all.award_id%TYPE)
4021: IS
4022: SELECT
4023: COUNT(loan_id) awd_count
4024: FROM
4031:
4032: --
4033: -- check if award has pell or loan record
4034: --
4035: CURSOR cur_fws_awd (p_award_id igf_aw_award_all.award_id%TYPE) IS
4036: SELECT COUNT(auth_id) awd_count
4037: FROM igf_se_auth
4038: WHERE award_id = p_award_id
4039: AND flag = 'A';
4044: --
4045: -- Cursor to get disbursements
4046: --
4047:
4048: CURSOR cur_get_adisb ( p_award_id igf_aw_award_all.award_id%TYPE,
4049: p_ld_seq_num igf_aw_awd_disb_all.ld_sequence_number%TYPE,
4050: p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
4051: IS
4052: SELECT
4065: --
4066: -- Cursor to get Holds
4067: --
4068:
4069: CURSOR cur_get_holds ( p_award_id igf_aw_award_all.award_id%TYPE,
4070: p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
4071: IS
4072: SELECT
4073: row_id
4082: --
4083: -- Cursor to get Holds
4084: --
4085:
4086: CURSOR cur_get_awds ( p_award_id igf_aw_award_all.award_id%TYPE)
4087: IS
4088: SELECT
4089: row_id,base_id
4090: FROM
4099: ) IS
4100: SELECT fcat.fed_fund_code
4101: FROM igf_aw_fund_cat fcat,
4102: igf_aw_fund_mast fmast,
4103: igf_aw_award_all awd
4104: WHERE fcat.fund_code = fmast.fund_code
4105: AND fmast.fund_id = awd.fund_id
4106: AND awd.award_id = p_award_id;
4107:
4108: get_fund_rec cur_fed_fund_code%ROWTYPE;
4109:
4110:
4111: CURSOR cur_chg_dtls(
4112: cp_award_id igf_aw_award_all.award_id%TYPE,
4113: cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
4114: ) IS
4115: SELECT ROWID row_id,
4116: disb_status
4224:
4225: END delete_awd_disb;
4226:
4227:
4228: FUNCTION get_total_disb ( p_award_id IN igf_aw_award_all.award_id%TYPE,
4229: p_ld_seq_num IN igf_aw_awd_disb_all.ld_sequence_number%TYPE )
4230: RETURN NUMBER
4231: IS
4232: --
4234: -- Bug 2306310
4235: -- Function to return number of disbursements
4236: --
4237:
4238: CURSOR cur_disb_nums ( p_award_id igf_aw_award_all.award_id%TYPE,
4239: p_ld_seq_num igf_aw_awd_disb_all.ld_sequence_number%TYPE)
4240: IS
4241: SELECT COUNT(disb_num) tot_num
4242: FROM
4535:
4536: active_isir_rec cur_active_isir%ROWTYPE;
4537:
4538: CURSOR cur_award_app_num(
4539: cp_award_id igf_aw_award_all.award_id%TYPE
4540: ) IS
4541: SELECT *
4542: FROM igf_aw_award
4543: WHERE award_id = p_award_id;