[Home] [Help]
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
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,
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