[Home] [Help]
426: app_exception.raise_exception;
427:
428: END create_pell_disb;
429:
430: PROCEDURE updating_coa_in_fa_base (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
431: IS
432: --
433: ------------------------------------------------------------------
434: -- Created by : Amit Dhawan, Oracle India (adhawan)
446: -- Who When What
447: -------------------------------------------------------------------
448: -- rasahoo 01-Dec-2003 FA 128 Isir Update
449: -- Added new parameter award_fmly_contribution_type
450: -- to igf_ap_fa_base_rec_pkg.update_row
451: -------------------------------------------------------------------
452: --ugummall 13-OCT-2003 FA 126 Multiple FA Offices
453: -- added new parameter assoc_org_num to
454: -- igf_ap_fa_base_rec_pkg.update_row call.
450: -- to igf_ap_fa_base_rec_pkg.update_row
451: -------------------------------------------------------------------
452: --ugummall 13-OCT-2003 FA 126 Multiple FA Offices
453: -- added new parameter assoc_org_num to
454: -- igf_ap_fa_base_rec_pkg.update_row call.
455: -------------------------------------------------------------------
456: -- sjadhav 09-Apr-2003 Bug 2890177
457: -- Modified updating_coa_in_fa_base
458: -- If pell coa and alt exp for coa items
471: -- and updating in Fa base
472: -- Added pell_alt_exp in update
473: -- row of fabase
474: -- c_stud_det modified to
475: -- select from igf_ap_fa_base_rec
476: -- instead of fa_con_v
477: -- Removed p_coa , p_ci_cal_type ,
478: -- p_sequence_number passed as
479: -- paramters
486: -- adhawan 12-apr-2002 Updating the Fa Record
487: -------------------------------------------------------------------
488: --
489:
490: CURSOR c_stud_det (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
491: IS
492: SELECT fa_detail.*
493: FROM igf_ap_fa_base_rec fa_detail
494: WHERE fa_detail.base_id = p_base_id;
489:
490: CURSOR c_stud_det (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
491: IS
492: SELECT fa_detail.*
493: FROM igf_ap_fa_base_rec fa_detail
494: WHERE fa_detail.base_id = p_base_id;
495:
496: l_stud_det c_stud_det%ROWTYPE ;
497: --
496: l_stud_det c_stud_det%ROWTYPE ;
497: --
498: -- Modified for bug Id 2613546
499: --
500: CURSOR cur_tot_coa (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
501: IS
502: SELECT
503: SUM(NVL(citsn.amount,0)) coa_total,
504: SUM(NVL(citsn.pell_coa_amount,0)) pell_coa,
511:
512: --
513: -- Bug 2890177
514: --
515: CURSOR cur_tot_coa_null (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
516: IS
517: SELECT
518: SUM(NVL(citsn.pell_coa_amount,-1)) pell_coa,
519: SUM(NVL(citsn.alt_pell_amount,-1)) pell_alt_expense
521: WHERE citsn.base_id = p_base_id ;
522:
523: tot_coa_null_rec cur_tot_coa_null%ROWTYPE;
524:
525: CURSOR cur_tot_coa_cnt (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
526: IS
527: SELECT
528: COUNT(base_id) rec_cnt
529: FROM igf_aw_coa_items citsn
579: --
580: -- Bug 2890177
581: --
582:
583: igf_ap_fa_base_rec_pkg.update_row(
584: x_rowid => l_stud_det.row_id,
585: x_base_id => l_stud_det.base_id,
586: x_ci_cal_type => l_stud_det.ci_cal_type,
587: x_person_id => l_stud_det.person_id,
1058: -- ordering disbursement records by disbursement dates
1059: -- This is used only in case the distribution method is MANUAL
1060: --
1061: CURSOR c_auto_disb(
1062: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1063: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1064: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1065: ) IS
1066: 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,
1103: -- Awarding WITHOUT COA, Manual Method
1104: --
1105:
1106: CURSOR c_auto_disb_wcoa(
1107: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1108: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1109: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1110: ) IS
1111: 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,
1137: -- this cursor is used in case the distribution method is EQUAL
1138: --
1139:
1140: CURSOR c_auto_disb_equal(
1141: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1142: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1143: cp_num_terms NUMBER,
1144: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1145: ) IS
1179: --
1180: -- Adding UNION clause to take care of Awarding without COA
1181: --
1182: CURSOR c_auto_disb_equal_wcoa(
1183: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1184: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1185: cp_num_terms NUMBER,
1186: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1187: ) IS
1211:
1212: --cursor to create disbursment records
1213: --this cursor is used if distribution method is MATCH COA
1214: CURSOR c_auto_disb_coa_match(
1215: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1216: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1217: cp_total_coa_amount NUMBER,
1218: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1219: ) IS
1250: AND aprd.ld_sequence_number = coaterms.ld_sequence_number
1251: ORDER BY 1;
1252: -------------bug 4077735----------------------
1253: CURSOR c_auto_disb_pell(
1254: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1255: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1256: ) IS
1257: 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,
1258: terms.ld_cal_type ld_cal_type,
1284: -- Awarding WITHOUT COA, Manual Method
1285: --
1286:
1287: CURSOR c_auto_disb_wcoa_pell(
1288: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1289: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1290: ) IS
1291: 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,
1292: terms.ld_cal_type ld_cal_type,
1309: -- this cursor is used in case the distribution method is EQUAL
1310: --
1311:
1312: CURSOR c_auto_disb_equal_pell(
1313: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1314: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1315: cp_num_terms NUMBER
1316: ) IS
1317: 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,
1340: ORDER BY 1;
1341:
1342:
1343: CURSOR c_auto_disb_equal_wcoa_pell(
1344: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1345: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1346: cp_num_terms NUMBER
1347: ) IS
1348: 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,
1363:
1364: --cursor to create disbursment records
1365: --this cursor is used if distribution method is MATCH COA
1366: CURSOR c_auto_disb_coa_match_pell(
1367: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1368: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1369: cp_total_coa_amount NUMBER
1370: ) IS
1371: 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,
1393: AND coaterms.base_id = cp_base_id
1394: ORDER BY 1;
1395:
1396: CURSOR cur_terms_count_pell(
1397: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1398: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1399: ) IS
1400: SELECT COUNT(*)
1401: FROM igf_aw_dp_terms terms,
1417: FROM igf_aw_dp_terms terms
1418: WHERE terms.adplans_id = cp_adplans_id;
1419:
1420: CURSOR c_coa_pell(
1421: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1422: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1423: ) IS
1424: SELECT SUM(amount) coa
1425: FROM igf_aw_coa_itm_terms coa_terms,
1434:
1435: -------------bug 4077735----------------------
1436: -- Get COA
1437: CURSOR c_coa(
1438: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1439: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1440: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1441: ) IS
1442: SELECT SUM(amount) coa
1445: ld_sequence_number
1446: FROM igf_aw_dp_terms
1447: WHERE adplans_id = cp_adplans_id
1448: )dist_terms,
1449: igf_ap_fa_base_rec_all fa,
1450: igf_aw_awd_prd_term aprd
1451: WHERE dist_terms.ld_cal_type = coa_terms.ld_cal_type
1452: AND dist_terms.ld_sequence_number = coa_terms.ld_sequence_number
1453: AND coa_terms.base_id = cp_base_id
1457: AND aprd.award_prd_cd = cp_awd_prd_code
1458: AND aprd.ld_cal_type = coa_terms.ld_cal_type
1459: AND aprd.ld_sequence_number = coa_terms.ld_sequence_number;
1460:
1461: ln_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1462:
1463: --
1464: -- get terms count
1465: --
1463: --
1464: -- get terms count
1465: --
1466: CURSOR cur_terms_count(
1467: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1468: cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1469: cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1470: ) IS
1471: SELECT COUNT(*)
1476: FROM igf_aw_coa_itm_terms
1477: WHERE base_id = cp_base_id
1478: GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
1479: igf_aw_awd_prd_term aprd,
1480: igf_ap_fa_base_rec_all fa
1481: WHERE terms.adplans_id = cp_adplans_id
1482: AND coaterms.ld_cal_type = terms.ld_cal_type
1483: AND coaterms.ld_sequence_number = terms.ld_sequence_number
1484: AND coaterms.base_id = cp_base_id
1527: WHERE award_id = p_award_id;
1528:
1529: get_base_rec cur_get_base%ROWTYPE;
1530:
1531: CURSOR cur_nslds_hist ( p_base_id igf_ap_fa_base_rec.base_id%type ) IS
1532: SELECT 'x'
1533: FROM igf_ap_nslds_data nslds,
1534: igf_ap_fa_base_rec_all fabase
1535: WHERE fabase.person_id = (SELECT person_id from igf_ap_fa_base_rec_all WHERE base_id = p_base_id) AND
1530:
1531: CURSOR cur_nslds_hist ( p_base_id igf_ap_fa_base_rec.base_id%type ) IS
1532: SELECT 'x'
1533: FROM igf_ap_nslds_data nslds,
1534: igf_ap_fa_base_rec_all fabase
1535: WHERE fabase.person_id = (SELECT person_id from igf_ap_fa_base_rec_all WHERE base_id = p_base_id) AND
1536: fabase.base_id = nslds.base_id AND
1537: nslds.nslds_loan_prog_code_1 IS NOT NULL;
1538:
1531: CURSOR cur_nslds_hist ( p_base_id igf_ap_fa_base_rec.base_id%type ) IS
1532: SELECT 'x'
1533: FROM igf_ap_nslds_data nslds,
1534: igf_ap_fa_base_rec_all fabase
1535: WHERE fabase.person_id = (SELECT person_id from igf_ap_fa_base_rec_all WHERE base_id = p_base_id) AND
1536: fabase.base_id = nslds.base_id AND
1537: nslds.nslds_loan_prog_code_1 IS NOT NULL;
1538:
1539: x_nslds_hist cur_nslds_hist%ROWTYPE;
2807: -----------------------------------------------------------------------------*/
2808:
2809:
2810: CURSOR cur_active_isir(
2811: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
2812: ) IS
2813: SELECT transaction_num
2814: FROM igf_ap_isir_matched_all
2815: WHERE base_id = cp_base_id
3139: END check_disbdts;
3140:
3141:
3142: FUNCTION check_amounts ( p_calling_form IN OUT NOCOPY VARCHAR2,
3143: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
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,
3195: -- Gets the max amt + max terms the student got a fund in a lifetime
3196: --
3197:
3198: CURSOR cur_max_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
3199: cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3200: IS
3201: SELECT
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
3211: AND awd.fund_id = fmast.fund_id
3212: AND awd.base_id = fabase.base_id
3219: --
3220: -- Cursor to Aggregate Award and Count
3221: --
3222: CURSOR cur_agg_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
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,
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
3232: AND awd.fund_id = fmast.fund_id
3233: AND awd.base_id = fabase.base_id
4287: IS
4288: SELECT
4289: 'Y'
4290: FROM
4291: igf_ap_fa_base_rec fa,
4292: igf_aw_award awd,
4293: igf_aw_awd_frml_det fdet
4294: WHERE
4295: fa.target_group = c_grp AND
4304: IS
4305: SELECT
4306: 'Y'
4307: FROM
4308: igf_ap_fa_base_rec fa,
4309: igf_aw_award awd,
4310: igf_aw_awd_frml_det fdet
4311: WHERE
4312: fa.base_id = c_baseid AND
4356: END awd_group_freeze;
4357:
4358: PROCEDURE get_common_perct(
4359: p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
4360: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
4361: p_perct OUT NOCOPY NUMBER,
4362: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
4363: ) AS
4364: ------------------------------------------------------------------
4395: SELECT SUM((teach_periods.tp_perct_num * terms.ld_perct_num)/100) perct
4396: FROM igf_aw_dp_terms terms,
4397: igf_aw_dp_teach_prds teach_periods,
4398: igf_aw_awd_prd_term aprd,
4399: igf_ap_fa_base_rec_all fa,
4400: (SELECT base_id,
4401: ld_cal_type,
4402: ld_sequence_number
4403: FROM igf_aw_coa_itm_terms
4431: END get_common_perct;
4432:
4433: PROCEDURE check_common_terms(
4434: p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
4435: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
4436: p_result OUT NOCOPY NUMBER,
4437: p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
4438: ) AS
4439: ------------------------------------------------------------------
4477: ld_sequence_number
4478: FROM igf_aw_coa_itm_terms
4479: WHERE base_id = p_base_id
4480: GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
4481: igf_ap_fa_base_rec_all fa,
4482: igf_aw_awd_prd_term aprd
4483: WHERE terms.adplans_id = p_adplans_id
4484: AND terms.adterms_id = teach_periods.adterms_id
4485: AND coaterms.ld_cal_type = terms.ld_cal_type
4525: --Who When What
4526: -------------------------------------------------------------------
4527:
4528: CURSOR cur_active_isir(
4529: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
4530: ) IS
4531: SELECT transaction_num
4532: FROM igf_ap_isir_matched_all
4533: WHERE base_id = cp_base_id