[Home] [Help]
406:
407: END get_fund_desc;
408:
409:
410: FUNCTION per_in_fa ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
411: p_ci_cal_type VARCHAR2,
412: p_ci_sequence_number NUMBER,
413: p_base_id OUT NOCOPY NUMBER
414: )
421: -- Purpose : Returns person number for the person id passed
422: --
423: --------------------------------------------------------------------------------------------
424:
425: CURSOR cur_get_pers_num ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
426: IS
427: SELECT person_number
428: FROM igs_pe_person_base_v
429: WHERE
432: get_pers_num_rec cur_get_pers_num%ROWTYPE;
433:
434: CURSOR cur_get_base (p_cal_type igs_ca_inst_all.cal_type%TYPE,
435: p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
436: p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
437: IS
438: SELECT
439: base_id
440: FROM
437: IS
438: SELECT
439: base_id
440: FROM
441: igf_ap_fa_base_rec
442: WHERE
443: person_id = p_person_id AND
444: ci_cal_type = p_cal_type AND
445: ci_sequence_number = p_sequence_number;
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
1186: || Created On : 07-Jan-2002
1194: */
1195:
1196:
1197:
1198: CURSOR c_student_details( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1199: IS
1200: SELECT manual_disb_hold,
1201: fa_process_status
1202: FROM igf_ap_fa_base_rec
1198: CURSOR c_student_details( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1199: IS
1200: SELECT manual_disb_hold,
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;
1210:
1211: CURSOR c_chk_verif_status( cp_base_id igf_ap_fa_base_rec.base_id%TYPE)
1212: IS
1213: SELECT fed_verif_status
1214: FROM igf_ap_fa_base_rec fab
1215: WHERE fab.base_id = p_base_id
1210:
1211: CURSOR c_chk_verif_status( cp_base_id igf_ap_fa_base_rec.base_id%TYPE)
1212: IS
1213: SELECT fed_verif_status
1214: FROM igf_ap_fa_base_rec fab
1215: WHERE fab.base_id = p_base_id
1216: AND fab.fed_verif_status IN ('ACCURATE','CALCULATED','NOTVERIFIED','NOTSELECTED',
1217: 'REPROCESSED','TOLERANCE','WAIVED');
1218: CURSOR c_fnd_todo(
1215: WHERE fab.base_id = p_base_id
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
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
1244: WHERE fmast.fund_id = cp_fund_id
1245: AND fa.base_id = cp_base_id
1246: AND fa.person_id = tdinst.person_id
1247: AND fndtd.fund_id = fmast.fund_id
1339:
1340: END chk_todo_result;
1341:
1342:
1343: PROCEDURE insert_pays_prg_uts(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1344: p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
1345: p_acad_ci_seq_num igs_ca_inst_all.sequence_number%TYPE)
1346: AS
1347: --------------------------------------------------------------------------------------------
1376: get_acad_tp_rec cur_get_acad_tp%ROWTYPE;
1377:
1378: -- Get all the programs,unit attempts in which student has 'enrolled'
1379:
1380: CURSOR cur_get_att(p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
1381: p_acad_cal_type igs_ca_inst_all.cal_type%TYPE,
1382: p_tp_cal_type igs_ca_inst_all.cal_type%TYPE,
1383: p_tp_sequence_number igs_ca_inst_all.sequence_number%TYPE)
1384: IS
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: --------------------------------------------------------------------------------------------
1478: --
1487: -- common to temp table ( igf_db_pays_prg_t ) and fund setup for pays only program
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,
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: --------------------------------------------------------------------------------------------
1569: --
1578: -- common to temp table ( igf_db_pays_prg_t ) and fund setup for pays only units
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,
1651:
1652: END chk_pays_uts;
1653:
1654:
1655: FUNCTION chk_fed_elig( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1656: p_fund_type VARCHAR2)
1657:
1658: RETURN BOOLEAN
1659: AS
1666: --------------------------------------------------------------------------------------------
1667:
1668: --Get the eligibility status of the student for an active ISIR for the context Award Year
1669:
1670: CURSOR cur_fedl_elig (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1671: SELECT isir.nslds_match_flag,
1672: fabase.nslds_data_override_flg
1673: FROM igf_ap_isir_matched isir,
1674: igf_ap_fa_base_rec_all fabase
1670: CURSOR cur_fedl_elig (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1671: SELECT isir.nslds_match_flag,
1672: fabase.nslds_data_override_flg
1673: FROM igf_ap_isir_matched isir,
1674: igf_ap_fa_base_rec_all fabase
1675: WHERE isir.base_id = p_base_id
1676: AND isir.active_isir = 'Y'
1677: AND isir.base_id = fabase.base_id;
1678:
1716: app_exception.raise_exception;
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
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
1765: SELECT
1766: fee_class
1767: FROM
1837: app_exception.raise_exception;
1838:
1839: END chk_fclass_result;
1840:
1841: FUNCTION chk_att_result(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1842: p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
1843: p_ld_seq_number igs_ca_inst_all.sequence_number%TYPE,
1844: p_min_att_type igs_en_atd_type_all.attendance_type%TYPE,
1845: p_result OUT NOCOPY VARCHAR2
1929:
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,
1953:
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.* ,
2288: app_exception.raise_exception;
2289:
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
2316: --------------------------------------------------------------------------------------------
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
2419: get_disb_rec cur_get_disb%ROWTYPE;
2420:
2421: -- Cursor to get Verification Status of Student
2422:
2423: CURSOR cur_get_ver (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2424: IS
2425: SELECT
2426: NVL(fed_verif_status,'*') fed_verif_status
2427: FROM
2424: IS
2425: SELECT
2426: NVL(fed_verif_status,'*') fed_verif_status
2427: FROM
2428: igf_ap_fa_base_rec
2429: WHERE
2430: base_id = p_base_id;
2431:
2432: get_ver_rec cur_get_ver%ROWTYPE;
3592: retcode OUT NOCOPY NUMBER,
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