[Home] [Help]
204:
205: END get_pm_3_acad_term_wks;
206:
207: PROCEDURE get_key_prog_ver_frm_adm(
208: p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
209: p_key_prog_cd OUT NOCOPY igs_ps_ver.course_cd%TYPE,
210: p_key_prog_ver OUT NOCOPY igs_ps_ver.version_number%TYPE
211: )
212: IS
229: adm.crv_version_number key_prog_ver
230: FROM
231: igs_ad_ps_appl_inst_all adm,
232: igs_ad_ou_stat s_adm_st,
233: igf_ap_fa_base_rec_all fabase
234: WHERE
235: adm.person_id = fabase.person_id AND
236: fabase.base_id = p_base_id AND
237: adm.adm_outcome_status = s_adm_st.adm_outcome_status AND
265:
266: CLOSE c_get_prog_frm_adm;
267: END get_key_prog_ver_frm_adm;
268:
269: PROCEDURE get_pell_setup ( cp_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
270: cp_course_cd IN igf_gr_pell_setup_all.course_cd%TYPE,
271: cp_version_number IN igf_gr_pell_setup_all.version_number%TYPE,
272: cp_cal_type IN igs_ca_inst.cal_type%TYPE,
273: cp_sequence_number IN igs_ca_inst.sequence_number%TYPE,
678: app_exception.raise_exception;
679: END get_pell_setup;
680:
681:
682: PROCEDURE get_pell_coa_efc (cp_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
683: cp_attendance_type IN igf_ap_attend_map.attendance_type%TYPE,
684: cp_pell_setup_rec IN igf_gr_pell_setup_all%ROWTYPE ,
685: cp_coa OUT NOCOPY NUMBER,
686: cp_efc OUT NOCOPY NUMBER,
799:
800: END get_pell_coa_efc;
801:
802: PROCEDURE get_pell_attendance_type (
803: cp_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
804: cp_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
805: cp_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
806: cp_pell_setup_rec IN igf_gr_pell_setup_all%ROWTYPE ,
807: cp_attendance_type IN OUT NOCOPY igf_ap_attend_map.attendance_type%TYPE,
849: SELECT
850: pell.pell_att_code attendance_type
851: FROM
852: igs_en_stdnt_ps_att en,
853: igf_ap_fa_base_rec fa,
854: igf_ap_attend_map pell
855: WHERE
856: fa.base_id = l_base_id AND
857: fa.person_id = en.person_id AND
866: person_id ,
867: ci_cal_type,
868: ci_sequence_number
869: FROM
870: igf_ap_fa_base_rec_all fa
871: WHERE
872: fa.base_id = l_base_id;
873:
874: l_fabase_rec c_fabase%ROWTYPE;
896: pell.cal_type = l_cal_type AND
897: pell.sequence_number = l_sequence_number AND
898: pell.attendance_type = l_att_type;
899:
900: CURSOR c_chk_enr(cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
901: IS
902: SELECT 'X'
903: FROM igs_en_stdnt_ps_att
904: WHERE person_id = cp_person_id;
904: WHERE person_id = cp_person_id;
905: l_chk_enr_rec c_chk_enr%ROWTYPE;
906:
907: CURSOR c_get_ant_atype(
908: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
909: cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
910: cp_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE
911: ) IS
912: SELECT pell.pell_att_code attendance_type
911: ) IS
912: SELECT pell.pell_att_code attendance_type
913: FROM igf_ap_fa_ant_data ant,
914: igf_ap_attend_map pell,
915: igf_ap_fa_base_rec_all fa
916: WHERE fa.base_id = cp_base_id
917: AND ant.ld_cal_type = cp_ld_cal_type
918: AND ant.ld_sequence_number = cp_ld_sequence_number
919: AND pell.attendance_type = ant.attendance_type
1869:
1870: PROCEDURE calc_pell(
1871: cp_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1872: cp_plan_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
1873: cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1874: cp_aid IN OUT NOCOPY NUMBER,
1875: cp_pell_tab IN OUT NOCOPY pell_tab,
1876: cp_return_status IN OUT NOCOPY VARCHAR2,
1877: cp_message IN OUT NOCOPY VARCHAR2,
1937: SELECT acad.degree_earned,
1938: ptype.fin_aid_program_type
1939: FROM igs_ad_acad_history_v acad,
1940: igs_ps_type_all ptype,
1941: igf_ap_fa_base_rec fa,
1942: igs_ps_degrees dc
1943: WHERE fa.base_id = l_base_id
1944: AND acad.person_id = fa.person_id
1945: AND acad.degree_earned = dc.degree_cd
1961:
1962:
1963: -- Get the Count of the Common Terms for the Student
1964: CURSOR c_terms(
1965: l_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1966: l_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1967: ) IS
1968: SELECT COUNT(*) Total_terms FROM (
1969: SELECT
1983: l_terms_rec c_terms%ROWTYPE;
1984:
1985: -- Get the Count of the Common Terms for the Student
1986: CURSOR c_dp_terms(
1987: l_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1988: l_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1989: ) IS
1990:
1991: SELECT COUNT(*) Total_terms FROM (
2001:
2002: -- Get the Terms for the Student, Common between COA / DPlan
2003: CURSOR c_terms_det(
2004: l_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
2005: l_base_id igf_ap_fa_base_rec_all.base_id%TYPE
2006: ) IS
2007: SELECT NVL (igf_aw_packaging.get_date_instance (
2008: l_base_id,
2009: teach.date_offset_cd,
2031: ORDER BY 1;
2032:
2033: l_terms_det_rec c_terms_det%ROWTYPE;
2034:
2035: CURSOR c_base( l_base_id igf_ap_fa_base_rec.base_id%TYPE
2036: ) IS
2037: SELECT
2038: fa.person_id,
2039: fa.ci_cal_type,
2038: fa.person_id,
2039: fa.ci_cal_type,
2040: fa.ci_sequence_number,
2041: fa.pell_alt_expense
2042: FROM igf_ap_fa_base_rec fa
2043: WHERE fa.base_id = l_base_id ;
2044:
2045: l_base_rec c_base%ROWTYPE;
2046:
2132:
2133: -- Gets all Programs (non pre-bachelor type) completed by the student from
2134: -- the Enrollment Program attempts table. If there is any record of this
2135: -- type, then the student is not eligible for Pell
2136: CURSOR c_get_prog_type (l_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2137: IS
2138: SELECT ptype.fin_aid_program_type prog_type
2139: FROM
2140: igs_en_stdnt_ps_att en,
2137: IS
2138: SELECT ptype.fin_aid_program_type prog_type
2139: FROM
2140: igs_en_stdnt_ps_att en,
2141: igf_ap_fa_base_rec fa,
2142: igs_ps_ver pver,
2143: igs_ps_type_v ptype
2144: WHERE
2145: fa.base_id = l_base_id AND
2153: l_get_prog_type c_get_prog_type%ROWTYPE;
2154:
2155: -- Gets anticipated Key Program details
2156: CURSOR cur_get_ant_key_prog_ver(
2157: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
2158: cp_plan_id igf_aw_awd_dist_plans.adplans_id%TYPE
2159: )
2160: IS
2161: SELECT
2198: -- Returns the terms in the (distribution plan + COA) and the number of teaching periods
2199: -- in each term
2200: CURSOR cur_get_term_info (
2201: cp_plan_id igf_aw_awd_dist_plans.adplans_id%TYPE,
2202: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
2203: )
2204: IS
2205: SELECT
2206: terms.ld_cal_type,
3018: app_exception.raise_exception;
3019:
3020: END calc_pell;
3021:
3022: PROCEDURE pell_elig( cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
3023: cp_return_status IN OUT NOCOPY VARCHAR2
3024: )
3025: IS
3026: /*
3053: SELECT acad.degree_earned,
3054: ptype.fin_aid_program_type
3055: FROM igs_ad_acad_history_v acad,
3056: igs_ps_type_all ptype,
3057: igf_ap_fa_base_rec fa,
3058: igs_ps_degrees dc
3059: WHERE fa.base_id = l_base_id
3060: AND acad.person_id = fa.person_id
3061: AND acad.degree_earned = dc.degree_cd
3066:
3067: -- check whether the student is eligibile for PELL Grant per the context
3068: -- Payment ISIR.
3069:
3070: CURSOR c_pell_elig ( l_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
3071: SELECT NVL(ism.pell_grant_elig_flag, 'N') pell_grant_elig_flag
3072: FROM igf_ap_isir_matched ism
3073: WHERE ism.base_id = l_base_id
3074: AND ism.active_isir = 'Y' ;
3076: l_pell_elig c_pell_elig%ROWTYPE;
3077:
3078: -- Get non Pre-Bachelor program types completed by the student
3079: -- from the Enrollment's table
3080: CURSOR c_get_prog_type (l_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
3081: IS
3082: SELECT ptype.FIN_AID_PROGRAM_TYPE prog_type
3083: FROM
3084: igs_en_stdnt_ps_att en,
3081: IS
3082: SELECT ptype.FIN_AID_PROGRAM_TYPE prog_type
3083: FROM
3084: igs_en_stdnt_ps_att en,
3085: igf_ap_fa_base_rec fa,
3086: igs_ps_ver pver,
3087: igs_ps_type_v ptype
3088: WHERE
3089: fa.base_id = l_base_id AND
3170:
3171: END pell_elig;
3172:
3173: PROCEDURE calc_term_pell(
3174: cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
3175: cp_attendance_type IN igf_ap_attend_map.attendance_type%TYPE,
3176: cp_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
3177: cp_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE,
3178: cp_term_aid IN OUT NOCOPY NUMBER,
3199: || term_instruction_time and annual instruction time
3200: || from the OSS.
3201: || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
3202: */
3203: CURSOR c_base( l_base_id igf_ap_fa_base_rec.base_id%TYPE
3204: ) IS
3205: SELECT
3206: fa.person_id,
3207: fa.ci_cal_type,
3206: fa.person_id,
3207: fa.ci_cal_type,
3208: fa.ci_sequence_number,
3209: fa.pell_alt_expense
3210: FROM igf_ap_fa_base_rec fa
3211: WHERE fa.base_id = l_base_id ;
3212:
3213: l_base_rec c_base%ROWTYPE;
3214:
3213: l_base_rec c_base%ROWTYPE;
3214:
3215: -- Gets FA Anticipated key program details
3216: CURSOR cur_get_ant_key_prog_ver(
3217: cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
3218: cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
3219: cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
3220: )
3221: IS
3238: l_get_ant_key_prog_ver_rec cur_get_ant_key_prog_ver%ROWTYPE;
3239:
3240: -- museshad (Build FA 157)
3241: -- Returns the award rounding factor setup for the fund in Fund Manager
3242: CURSOR cur_get_awd_round_fact(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
3243: IS
3244: SELECT LTRIM(RTRIM(roundoff_fact)) roundoff_fact
3245: FROM
3246: igf_aw_fund_mast_all fmast,
3243: IS
3244: SELECT LTRIM(RTRIM(roundoff_fact)) roundoff_fact
3245: FROM
3246: igf_aw_fund_mast_all fmast,
3247: igf_ap_fa_base_rec_all fabase
3248: WHERE
3249: fabase.base_id = cp_base_id AND
3250: fabase.ci_cal_type = fmast.ci_cal_type AND
3251: fabase.ci_sequence_number = fmast.ci_sequence_number AND
3459:
3460: END calc_term_pell;
3461:
3462: PROCEDURE calc_ft_max_pell(
3463: cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
3464: cp_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
3465: cp_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE,
3466: cp_flag IN VARCHAR2,
3467: cp_aid IN OUT NOCOPY NUMBER,
3460: END calc_term_pell;
3461:
3462: PROCEDURE calc_ft_max_pell(
3463: cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
3464: cp_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
3465: cp_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE,
3466: cp_flag IN VARCHAR2,
3467: cp_aid IN OUT NOCOPY NUMBER,
3468: cp_ft_aid IN OUT NOCOPY NUMBER,
3461:
3462: PROCEDURE calc_ft_max_pell(
3463: cp_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
3464: cp_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
3465: cp_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE,
3466: cp_flag IN VARCHAR2,
3467: cp_aid IN OUT NOCOPY NUMBER,
3468: cp_ft_aid IN OUT NOCOPY NUMBER,
3469: cp_return_status IN OUT NOCOPY VARCHAR2,
3487: -- instead of taking it as 99999 which is the max value.
3488: -------------------------------------------------------------------
3489:
3490: -- Get RFMS Pell Details
3491: CURSOR c_rfms( l_base_id igf_ap_fa_base_rec.base_id%TYPE
3492: ) IS
3493: SELECT
3494: RFMS.PELL_AMOUNT
3495: FROM
3499:
3500: l_rfms_rec c_rfms%ROWTYPE;
3501:
3502: -- Get
3503: CURSOR c_coa( l_base_id igf_ap_fa_base_rec.base_id%TYPE) IS
3504: SELECT
3505: fa.coa_pell pell_coa
3506: FROM
3507: igf_ap_fa_base_rec fa
3503: CURSOR c_coa( l_base_id igf_ap_fa_base_rec.base_id%TYPE) IS
3504: SELECT
3505: fa.coa_pell pell_coa
3506: FROM
3507: igf_ap_fa_base_rec fa
3508: WHERE
3509: fa.base_id = l_base_id;
3510:
3511: -- Get FA Anticipated Key Prog details
3508: WHERE
3509: fa.base_id = l_base_id;
3510:
3511: -- Get FA Anticipated Key Prog details
3512: CURSOR cur_get_ant_key_prog_ver(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
3513: IS
3514: SELECT
3515: ant_data.program_cd key_prog,
3516: prog.version_number key_prog_ver