[Home] [Help]
91:
92:
93: g_batch_dt VARCHAR2(20);
94:
95: FUNCTION chk_orig_isir_exists( p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
96: p_transaction_num IN igf_ap_ISIR_matched.transaction_num%TYPE)
97: RETURN BOOLEAN
98: AS
99: --------------------------------------------------------------------------------------------------------
105: --Who When What
106: --
107: ----------------------------------------------------------------------------------------------------------
108: -- retrieves records for which RFMS Originations exists in status Accepted or Sent, with a different Transaction Number
109: CURSOR chk_ex_orig_rec (cp_base_id igf_ap_fa_base_rec.base_id%TYPE,
110: cp_transaction_num igf_ap_ISIR_matched.transaction_num%TYPE)
111: IS
112: SELECT 'X'
113: FROM igf_gr_rfms rfms
299:
300:
301: END get_calendar_desc;
302:
303: FUNCTION get_per_num ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE)
304: RETURN VARCHAR2
305: IS
306: --------------------------------------------------------------------------------------------
307: --
310: -- Purpose : Returns person number for the Base id passed
311: --
312: --------------------------------------------------------------------------------------------
313:
314: CURSOR cur_fa_pers ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
315: IS
316: SELECT person_number
317: FROM igs_pe_person_base_v
318: WHERE person_id =
318: WHERE person_id =
319: (
320: SELECT person_id
321: FROM
322: igf_ap_fa_base_rec
323: WHERE
324: base_id = p_base_id
325: );
326:
349: END get_per_num;
350:
351:
352:
353: FUNCTION get_per_num ( p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
354: p_person_number OUT NOCOPY igf_ap_person_v.person_number%TYPE )
355: RETURN BOOLEAN
356: IS
357: --------------------------------------------------------------------------------------------
362: -- financial aid
363: --
364: --------------------------------------------------------------------------------------------
365:
366: CURSOR cur_fa_pers ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
367: IS
368: SELECT person_number
369: FROM igf_ap_person_v
370: WHERE
395:
396: END get_per_num;
397:
398:
399: FUNCTION get_person_id ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
400: RETURN VARCHAR2
401: IS
402: --------------------------------------------------------------------------------------------
403: --
406: -- Purpose : Returns Person ID for the Base id passed
407: --
408: --------------------------------------------------------------------------------------------
409:
410: CURSOR cur_fa_pers ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
411: IS
412: SELECT person_id
413: FROM igf_ap_fa_base_rec
414: WHERE
409:
410: CURSOR cur_fa_pers ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
411: IS
412: SELECT person_id
413: FROM igf_ap_fa_base_rec
414: WHERE
415: base_id = p_base_id;
416:
417: fa_pers_rec cur_fa_pers%ROWTYPE;
438:
439: END get_person_id;
440:
441:
442: FUNCTION get_per_num_oss ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
443: RETURN VARCHAR2
444: IS
445: --------------------------------------------------------------------------------------------
446: --
449: -- Purpose : Returns person number for the Base id passed
450: --
451: --------------------------------------------------------------------------------------------
452:
453: CURSOR cur_fa_pers ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
454: IS
455: SELECT person_number
456: FROM igs_pe_person_base_v
457: WHERE
1311:
1312: CURSOR get_awd_fmly_contrib_type ( p_base_id igf_aw_award_all.base_id%TYPE)
1313: IS
1314: SELECT award_fmly_contribution_type
1315: FROM igf_ap_fa_base_rec
1316: WHERE base_id = p_base_id;
1317:
1318:
1319: c_awd_fmly_contrib_type get_awd_fmly_contrib_type%ROWTYPE;
2089: IS
2090: SELECT
2091: pell_alt_expense
2092: FROM
2093: igf_ap_fa_base_rec
2094: WHERE
2095: base_id = p_base_id;
2096:
2097: get_alt_rec cur_get_alt%ROWTYPE;
2095: base_id = p_base_id;
2096:
2097: get_alt_rec cur_get_alt%ROWTYPE;
2098:
2099: CURSOR cur_tufees_code (p_exp igf_ap_fa_base_rec_all.pell_alt_expense%TYPE,
2100: p_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
2101: p_sequence_number igf_gr_rfms_all.ci_sequence_number%TYPE)
2102: IS
2103: SELECT ltfees.lt_fees_code
2619:
2620:
2621:
2622: FUNCTION get_pell_code(p_att_code IN igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2623: p_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
2624: p_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE)
2625: RETURN VARCHAR2
2626: /*
2627: || Created By :
2620:
2621:
2622: FUNCTION get_pell_code(p_att_code IN igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2623: p_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
2624: p_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE)
2625: RETURN VARCHAR2
2626: /*
2627: || Created By :
2628: || Created On :
2637: */
2638: IS
2639:
2640: CURSOR cur_get_pell (p_att_code igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2641: p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2642: p_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2643: )
2644: IS
2645: SELECT pell_att_code
2638: IS
2639:
2640: CURSOR cur_get_pell (p_att_code igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2641: p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2642: p_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2643: )
2644: IS
2645: SELECT pell_att_code
2646: FROM igf_ap_attend_map
2674: --
2675:
2676: PROCEDURE get_base_id(p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2677: p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2678: p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
2679: p_base_id OUT NOCOPY igf_ap_fa_base_rec_all.base_id%TYPE,
2680: p_err_msg OUT NOCOPY VARCHAR2
2681: )
2682: IS
2675:
2676: PROCEDURE get_base_id(p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2677: p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2678: p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
2679: p_base_id OUT NOCOPY igf_ap_fa_base_rec_all.base_id%TYPE,
2680: p_err_msg OUT NOCOPY VARCHAR2
2681: )
2682: IS
2683:
2687: --
2688:
2689: CURSOR cur_get_base (p_cal_type igs_ca_inst_all.cal_type%TYPE,
2690: p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2691: p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
2692: IS
2693: SELECT
2694: base_id
2695: FROM
2692: IS
2693: SELECT
2694: base_id
2695: FROM
2696: igf_ap_fa_base_rec
2697: WHERE
2698: person_id = p_person_id AND
2699: ci_cal_type = p_cal_type AND
2700: ci_sequence_number = p_sequence_number;
2732: app_exception.raise_exception;
2733:
2734: END get_base_id;
2735:
2736: PROCEDURE update_current_ssn (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
2737: p_cur_ssn IN igf_ap_isir_matched_all.current_ssn%TYPE,
2738: p_message OUT NOCOPY fnd_new_messages.message_name%TYPE)
2739: IS
2740: --
2743: -- update_current_ssn
2744: -- updates current ssn with the new value from active isir
2745: --
2746:
2747: CURSOR cur_get_pell (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2748: IS
2749: SELECT pell.*
2750: FROM
2751: igf_gr_rfms pell