[Home] [Help]
163: CURSOR cur_get_fund_code (p_fund_id NUMBER)
164: IS
165: SELECT fund_code
166: FROM
167: igf_aw_fund_mast
168: WHERE
169: fund_id = p_fund_id;
170:
171: get_fund_code_rec cur_get_fund_code%ROWTYPE;
361:
362:
363: END get_cut_off_dt;
364:
365: FUNCTION get_fund_desc ( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
366: RETURN VARCHAR2
367: IS
368: --------------------------------------------------------------------------------------------
369: --
372: -- Purpose : Returns fund code + description of the fund id passed
373: --
374: --------------------------------------------------------------------------------------------
375:
376: CURSOR cur_fund_des ( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
377: IS
378: SELECT fund_code fdesc
379: FROM igf_aw_fund_mast
380: WHERE
375:
376: CURSOR cur_fund_des ( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
377: IS
378: SELECT fund_code fdesc
379: FROM igf_aw_fund_mast
380: WHERE
381: fund_id = p_fund_id;
382:
383: fund_des_rec cur_fund_des%ROWTYPE;
474:
475: PROCEDURE create_actual ( p_row_id ROWID,
476: p_lb_flag BOOLEAN,
477: p_lb_force BOOLEAN,
478: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
479: AS
480: --------------------------------------------------------------------------------------------
481: --
482: -- Created By : sjadhav
511:
512: disb_dtl_rec igf_db_awd_disb_dtl%ROWTYPE;
513:
514:
515: CURSOR cur_update_fund (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
516: IS
517: SELECT *
518: FROM
519: igf_aw_fund_mast
515: CURSOR cur_update_fund (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
516: IS
517: SELECT *
518: FROM
519: igf_aw_fund_mast
520: WHERE
521: fund_id = p_fund_id
522: FOR UPDATE OF total_disbursed NOWAIT;
523:
847:
848: CURSOR c_get_awd_det (cp_awd_id igf_aw_award_all.award_id%TYPE)
849: IS
850: SELECT fmast.ci_cal_type, fmast.ci_sequence_number
851: FROM igf_aw_fund_mast_all fmast,
852: igf_aw_award_all awd
853: WHERE fmast.fund_id = awd.fund_id AND
854: awd.award_id = cp_awd_id;
855:
1177: END delete_pays;
1178:
1179:
1180: FUNCTION chk_todo_result(p_message_name OUT NOCOPY VARCHAR2,
1181: p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1182: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1183: ) RETURN BOOLEAN AS
1184: /*
1185: || Created By : sjadhav
1201: fa_process_status
1202: FROM igf_ap_fa_base_rec
1203: WHERE base_id = cp_base_id;
1204:
1205: CURSOR c_fund_details( cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1206: IS
1207: SELECT ver_app_stat_override
1208: FROM igf_aw_fund_mast
1209: WHERE fund_id = cp_fund_id;
1204:
1205: CURSOR c_fund_details( cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1206: IS
1207: SELECT ver_app_stat_override
1208: FROM igf_aw_fund_mast
1209: WHERE fund_id = cp_fund_id;
1210:
1211: CURSOR c_chk_verif_status( cp_base_id igf_ap_fa_base_rec.base_id%TYPE)
1212: IS
1216: AND fab.fed_verif_status IN ('ACCURATE','CALCULATED','NOTVERIFIED','NOTSELECTED',
1217: 'REPROCESSED','TOLERANCE','WAIVED');
1218: CURSOR c_fnd_todo(
1219: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1220: cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1221: cp_status igf_ap_td_item_inst_all.status%TYPE,
1222: cp_inactive igf_ap_td_item_inst_all.inactive_flag%TYPE
1223: ) IS
1224: SELECT 'x'
1221: cp_status igf_ap_td_item_inst_all.status%TYPE,
1222: cp_inactive igf_ap_td_item_inst_all.inactive_flag%TYPE
1223: ) IS
1224: SELECT 'x'
1225: FROM igf_aw_fund_mast_all fmast,
1226: igf_aw_fund_td_map_all fndtd,
1227: igf_ap_td_item_inst_all tdinst,
1228: igf_ap_td_item_mst_all tdmst
1229: WHERE fmast.fund_id = cp_fund_id
1235: AND tdinst.status <> cp_status
1236: AND tdinst.inactive_flag <> cp_inactive
1237: UNION
1238: SELECT 'x'
1239: FROM igf_aw_fund_mast_all fmast,
1240: igf_aw_fund_td_map_all fndtd,
1241: igf_ap_td_item_inst_v tdinst,
1242: igf_ap_td_item_mst_all tdmst,
1243: igf_ap_fa_base_rec_all fa
1469:
1470: END insert_pays_prg_uts;
1471:
1472:
1473: FUNCTION chk_pays_prg( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1474: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1475: RETURN BOOLEAN
1476: AS
1477: --------------------------------------------------------------------------------------------
1488: -- If there are no records, the check is failed else passed
1489: --
1490:
1491: CURSOR cur_std_pays(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1492: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1493: IS
1494: SELECT
1495: program_cd,
1496: prg_ver_num
1514: --
1515: -- This cursor will retreive records from fund setup for pays only program
1516: -- If there are no records, then the pays only prog check is passed
1517: --
1518: CURSOR cur_fund_pprg (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1519: IS
1520: SELECT
1521: course_cd,
1522: version_number
1560: app_exception.raise_exception;
1561:
1562: END chk_pays_prg;
1563:
1564: FUNCTION chk_pays_uts( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1565: p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1566: RETURN BOOLEAN
1567: AS
1568: --------------------------------------------------------------------------------------------
1579: -- If there are no records, the check is failed else passed
1580: --
1581:
1582: CURSOR cur_std_pays(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1583: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1584: IS
1585: SELECT
1586: unit_cd,
1587: unit_ver_num
1604: --
1605: -- This cursor will retreive records from fund setup for pays only program
1606: -- If there are no records, then the pays only prog check is passed
1607: --
1608: CURSOR cur_fund_unit (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1609: IS
1610: SELECT
1611: unit_cd,
1612: version_number
1717:
1718: END chk_fed_elig;
1719:
1720: FUNCTION chk_fclass_result(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1721: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1722: p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
1723: p_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE)
1724: RETURN BOOLEAN
1725:
1740: --
1741: -- This cursor will retreive records from fund setup for pays only Fee Class
1742: -- If there are no records, then the pays only fee class check is passed
1743: --
1744: CURSOR cur_fund_fcls (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1745: IS
1746: SELECT
1747: fee_class
1748: FROM
1756: -- This cursor will return common fee classes from
1757: -- fund setup and persons charges
1758: --
1759:
1760: CURSOR cur_fee_cls ( p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1761: p_fee_cal_type igs_ca_inst.cal_type%TYPE,
1762: p_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
1763: p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
1764: IS
1930: END chk_att_result;
1931:
1932:
1933: PROCEDURE disb_verf_enf(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1934: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
1935: p_key_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
1936: p_key_ver_num igs_en_stdnt_ps_att.version_number%TYPE,
1937: p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
1938: p_acad_seq_num igs_ca_inst_all.sequence_number%TYPE)
1954: -- Cursor to retreive Actual Disbursemenet records for which the
1955: -- disbursement verification enforcement date is passed
1956:
1957: CURSOR cur_awd_disb ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1958: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
1959: IS
1960: SELECT
1961: adisb.* ,
1962: awd.base_id,
1965: fmast.fund_id,
1966: cat.fed_fund_code
1967: FROM
1968: igf_aw_awd_disb adisb,
1969: igf_aw_fund_mast fmast,
1970: igf_aw_award awd,
1971: igf_aw_fund_cat cat
1972: WHERE
1973: adisb.award_id = awd.award_id AND
2015:
2016: -- following variables are used to make sure that the fund level,award level and term level
2017: -- checks are not repeated for each disbursement belonging to the same fund/award/term
2018:
2019: ln_old_fund igf_aw_fund_mast_all.fund_id%TYPE;
2020: ln_new_fund igf_aw_fund_mast_all.fund_id%TYPE;
2021: lv_old_ld_c igf_aw_fund_tp_all.tp_cal_type%TYPE;
2022: ln_old_ld_n igf_aw_fund_tp_all.tp_sequence_number%TYPE;
2023: lv_new_ld_c igf_aw_fund_tp_all.tp_cal_type%TYPE;
2016: -- following variables are used to make sure that the fund level,award level and term level
2017: -- checks are not repeated for each disbursement belonging to the same fund/award/term
2018:
2019: ln_old_fund igf_aw_fund_mast_all.fund_id%TYPE;
2020: ln_new_fund igf_aw_fund_mast_all.fund_id%TYPE;
2021: lv_old_ld_c igf_aw_fund_tp_all.tp_cal_type%TYPE;
2022: ln_old_ld_n igf_aw_fund_tp_all.tp_sequence_number%TYPE;
2023: lv_new_ld_c igf_aw_fund_tp_all.tp_cal_type%TYPE;
2024: ln_new_ld_n igf_aw_fund_tp_all.tp_sequence_number%TYPE;
2290: END disb_verf_enf;
2291:
2292: PROCEDURE process_student( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
2293: p_result IN OUT NOCOPY VARCHAR2,
2294: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
2295: p_award_id igf_aw_award_all.award_id%TYPE,
2296: p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
2297: )
2298: AS
2317:
2318: SKIP_RECORD EXCEPTION;
2319:
2320: CURSOR cur_awd_disb(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
2321: p_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
2322: p_award_id igf_aw_award_all.award_id%TYPE,
2323: p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
2324: IS
2325: SELECT
2340: fmast.fee_type fee_type,
2341: cat.fed_fund_code
2342: FROM
2343: igf_aw_awd_disb_all adisb,
2344: igf_aw_fund_mast_all fmast,
2345: igf_aw_award_all awd,
2346: igf_aw_fund_cat_all cat
2347: WHERE
2348: adisb.award_id = awd.award_id AND
2480:
2481: -- following variables are used to make sure that the fund level,award level and term level
2482: -- checks are not repeated for each disbursement belonging to the same fund/award/term
2483:
2484: ln_old_fund igf_aw_fund_mast_all.fund_id%TYPE;
2485: ln_new_fund igf_aw_fund_mast_all.fund_id%TYPE;
2486:
2487: ln_old_awd igf_aw_award_all.award_id%TYPE;
2488: ln_new_awd igf_aw_award_all.award_id%TYPE;
2481: -- following variables are used to make sure that the fund level,award level and term level
2482: -- checks are not repeated for each disbursement belonging to the same fund/award/term
2483:
2484: ln_old_fund igf_aw_fund_mast_all.fund_id%TYPE;
2485: ln_new_fund igf_aw_fund_mast_all.fund_id%TYPE;
2486:
2487: ln_old_awd igf_aw_award_all.award_id%TYPE;
2488: ln_new_awd igf_aw_award_all.award_id%TYPE;
2489:
3593: p_award_year IN VARCHAR2,
3594: p_run_for IN VARCHAR2,
3595: p_per_grp_id IN NUMBER,
3596: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
3597: p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3598: p_log_det IN VARCHAR2,
3599: p_org_id IN NUMBER )
3600: AS
3601:
3644: WHERE group_id = p_per_grp_id;
3645: per_grp_rec cur_per_grp_name%ROWTYPE;
3646:
3647: -- Cursor to retreive Student having awards for a given fund
3648: CURSOR cur_award_std(p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
3649: IS
3650: SELECT
3651: DISTINCT
3652: awd.base_id,
3652: awd.base_id,
3653: fcat.fed_fund_code
3654: FROM
3655: igf_aw_award awd,
3656: igf_aw_fund_mast fmast,
3657: igf_aw_fund_cat fcat
3658: WHERE
3659: awd.fund_id = p_fund_id AND
3660: awd.fund_id = fmast.fund_id AND