[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_PELL_CALC
Source
1 PACKAGE BODY igf_gr_pell_calc AS
2 /* $Header: IGFGR11B.pls 120.25 2006/08/25 05:56:02 veramach ship $ */
3
4 /*===================================================================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +===================================================================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGF_GR_PELL_CALC |
11 | |
12 | NOTES |
13 | New process that recomputes pell based on the Student Term Enrl |
14 | details. |
15 | Package includes wrappers that calculate pell and will be called |
16 | From the following places |
17 | Student Awards Form / Student Disb Form / Packaging Process |
18 | Single Fund Process / RFMS Originations. |
19 | |
20 | HISTORY |
21 | museshad 31-May-2006 Bug 5254735. Following changes made in calc_pell and get_pell_attendance_type() - |
22 | 1. If the no. of terms in COA + DP combination is more than the no. of Payment Periods |
23 | setup in Pell Setup form, then an Err used to be thrown before, now it is a Warning |
24 | message. |
25 | 2. If the Pell award amount (cp_aid) is more than the Full time Pell amount, before an |
26 | error message used to be thrown. This scenario cannot occur now. Term level |
27 | amt is reduced to be within the Full time Pell amount. |
28 | 3. If get_pell_attendance_type() fails to derive the ACTUAL attendance type for any of |
29 | the terms in COA + DP combination, then calc_pell() would error out before.This has |
30 | been changed to skip that term and award Pell by processing the remaining terms. |
31 | museshad 06-Mar-2006 Bug 5006587. Build FA 162. Adjust (+ or -) Term Start Date with Term Offset. |
32 | Changes made to get_pell_setup() and get_pell_attendance_type(). |
33 | museshad 08-Nov-2005 Bug 4624366. Added exception handler in get_pell_attendance_type, for the |
34 | Enrollment wrapper call igs_en_prc_load.enrp_get_inst_latt. |
35 | museshad 12-Sep-2005 Build FA 157. Added the procedure round_term_disbursements to implement Pell |
36 | disbursement rounding. |
37 | museshad 13-Jul-2005 Build FA 157. Added the proocedure 'get_key_prog_ver' and other related changes |
38 | relating to deriving anticipated data. |
39 | rasahoo 28-May-2004 Bug# 4396459 Repalced the table refference igf_gr_pell_rng_amt with igf_gr_reg_amts and|
40 | Repalced the table refference igf_gr_alt_coa with igf_gr_alt_amts. |
41 | cdcruz 28-Oct-2004 FA152 BUILD Modified igf_aw_packng_subfns.get_fed_efc() as part of dependency |
42 | sjadhav 15-Oct-2004 Modified get_pell_setup for COD Entity ID |
43 | veramach 12-Dec-2003 Fixed issues with NVL |
44 | sjalasut 10 Dec, 2003 FA132 Changes.Removed get_current_enrl_term |
45 | and replaced with igf_ap_gen_001.get_key_program |
46 | cdcruz 06-Dec-2003 Creation of file |
47 | |
48 *===================================================================================================================*/
49
50 NO_SETUP EXCEPTION;
51
52 PROCEDURE get_pm_3_acad_term_wks(p_cal_type igs_ca_inst_all.cal_type%TYPE,
53 p_seq_number igs_ca_inst_all.sequence_number%TYPE,
54 p_course_cd igs_en_psv_term_it.course_cd%TYPE,
55 p_version_number igs_en_psv_term_it.version_number%TYPE,
56 p_term_weeks OUT NOCOPY NUMBER,
57 p_acad_weeks OUT NOCOPY NUMBER,
58 p_result OUT NOCOPY VARCHAR2,
59 p_message OUT NOCOPY VARCHAR2)
60 IS
61
62
63 CURSOR cur_get_anl_instr_time
64 IS
65 SELECT annual_instruction_time
66 FROM igs_ps_ver_all
67 WHERE course_cd = p_course_cd
68 AND version_number = p_version_number;
69
70 get_anl_instr_time_rec cur_get_anl_instr_time%ROWTYPE;
71
72 CURSOR cur_get_term_instr_time
73 IS
74 SELECT term_instruction_time
75 FROM igs_en_psv_term_it
76 WHERE cal_type = p_cal_type
77 AND sequence_number = p_seq_number
78 AND course_cd = p_course_cd
79 AND version_number = p_version_number;
80
81 get_term_instr_time_rec cur_get_term_instr_time%ROWTYPE;
82
83 CURSOR cur_get_term_time
84 IS
85 SELECT
86 term_instruction_time
87 FROM
88 igs_ca_inst_all
89 WHERE
90 cal_type = p_cal_type AND
91 sequence_number = p_seq_number;
92
93 get_term_time_rec cur_get_term_time%ROWTYPE;
94
95 BEGIN
96
97 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
98 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pm_3_acad_term_wks.debug',
99 'In Params - > p_cal_type -> p_seq_number -> p_course_cd -> p_course_cd '
100 || ' -> ' || p_cal_type
101 || ' -> ' || p_seq_number
102 || ' -> ' || p_course_cd
103 || ' -> ' || p_course_cd);
104 END IF;
105
106 p_acad_weeks := NULL;
107 p_term_weeks := NULL;
108
109 OPEN cur_get_anl_instr_time;
110 FETCH cur_get_anl_instr_time INTO get_anl_instr_time_rec;
111 CLOSE cur_get_anl_instr_time;
112
113 p_acad_weeks := get_anl_instr_time_rec.annual_instruction_time;
114
115 --
116 -- If acad weeks is NULL or ZERO then return with error
117 --
118 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
119 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pm_3_acad_term_wks.debug',
120 'Prog Level Weeks - > p_acad_weeks '
121 || ' -> ' || p_acad_weeks);
122 END IF;
123
124 IF p_acad_weeks IS NULL THEN
125 fnd_message.set_name( 'IGF','IGF_AW_NO_ANNL_INST_TIME');
126 fnd_message.set_token('PROGRAM_CODE',p_course_cd);
127 fnd_message.set_token('VERSION_NUM',p_version_number);
128 p_message := fnd_message.get ;
129 p_result := 'E' ;
130 RETURN;
131 ELSIF p_acad_weeks = 0 THEN
132 fnd_message.set_name( 'IGF','IGF_AW_ZERO_ANNL_TIME');
133 fnd_message.set_token('PROGRAM_CODE',p_course_cd);
134 fnd_message.set_token('VERSION_NUM',p_version_number);
135 p_message := fnd_message.get ;
136 p_result := 'E' ;
137 RETURN;
138 END IF;
139
140 --
141 -- Open the Program Level Override
142 --
143 OPEN cur_get_term_instr_time;
144 FETCH cur_get_term_instr_time INTO get_term_instr_time_rec;
145 CLOSE cur_get_term_instr_time;
146
147 p_term_weeks := get_term_instr_time_rec.term_instruction_time;
148
149 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
150 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pm_3_acad_term_wks.debug',
151 'Term Level Prog Overd Weeks - > p_term_weeks '
152 || ' -> ' || p_term_weeks);
153 END IF;
154
155 IF p_term_weeks = 0 THEN
156 fnd_message.set_name('IGF','IGF_AW_ZERO_TERM_ACAD_TIME');
157 fnd_message.set_token('PROGRAM_CODE',p_course_cd);
158 fnd_message.set_token('VERSION_NUM',p_version_number);
159 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_cal_type,p_seq_number));
160 p_message := fnd_message.get;
161 p_result := 'E';
162 RETURN;
163 END IF;
164
165 IF p_term_weeks IS NULL THEN
166 --
167 -- Open term level cursor
168 --
169 OPEN cur_get_term_time;
170 FETCH cur_get_term_time INTO get_term_time_rec;
171 CLOSE cur_get_term_time;
172
173 p_term_weeks := get_term_time_rec.term_instruction_time;
174
175 IF p_term_weeks IS NULL THEN
176 fnd_message.set_name('IGF','IGF_AW_NO_TERM_INST_TIME');
177 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_cal_type,p_seq_number));
178 p_message := fnd_message.get;
179 p_result := 'E';
180 ELSIF p_term_weeks = 0 THEN
181 fnd_message.set_name('IGF','IGF_AW_ZERO_TERM_TIME');
182 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(p_cal_type,p_seq_number));
183 p_message := fnd_message.get;
184 p_result := 'E';
185 END IF;
186 END IF;
187
188 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
189 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pm_3_acad_term_wks.debug',
190 'Term Level Weeks - > p_term_weeks '
191 || ' -> ' || p_term_weeks);
192 END IF;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
197 fnd_message.set_token('NAME','IGF_GR_PELL_CALC.GET_PM_3_ACAD_TERM_WKS '||SQLERRM);
198 igs_ge_msg_stack.add;
199 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
200 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.get_pm_3_acad_term_wks.exception',
201 'sql error message: '||SQLERRM);
202 END IF;
203 app_exception.raise_exception;
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
213 /*
214 || Created By : museshad
215 || Created On : 13-Jul-2005
216 || Purpose : This procedure is called, when actual (Enrollment) key program data
217 || is not available. Key program is got by looking in this order -
218 || 1) Enrollment data (Actual)
219 || 2) Admissions data
220 || 3) FA Anticipated data
221 || This procedure implements 2
222 || For Admissions, the Key program data is considered only if the
223 || Student has just one Admission application.
224 */
225 CURSOR c_get_prog_frm_adm
226 IS
227 SELECT
228 adm.course_cd key_prog,
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
238 s_adm_st.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
239 adm.course_cd IS NOT NULL AND
240 1 = (SELECT COUNT(person_id)
241 FROM igs_ad_ps_appl_inst_all adm1, igs_ad_ou_stat s_adm_st1
242 WHERE
243 adm1.person_id = adm.person_id AND
244 adm1.adm_outcome_status = s_adm_st1.adm_outcome_status AND
245 s_adm_st1.s_adm_outcome_status IN ('OFFER', 'COND-OFFER') AND
246 adm1.course_cd IS NOT NULL);
247
248 l_adm_rec c_get_prog_frm_adm%ROWTYPE;
249 BEGIN
250
251 p_key_prog_cd := NULL;
252 p_key_prog_ver := NULL;
253
254 OPEN c_get_prog_frm_adm;
255 FETCH c_get_prog_frm_adm INTO l_adm_rec;
256
257 IF (c_get_prog_frm_adm%FOUND) THEN
258 p_key_prog_cd := l_adm_rec.key_prog;
259 p_key_prog_ver := l_adm_rec.key_prog_ver;
260
261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
262 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.get_key_prog_ver_frm_adm.debug', 'Getting key program details from Admissions. Key program: ' ||p_key_prog_cd|| ', Version: ' ||p_key_prog_ver);
263 END IF;
264 END IF;
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,
274 cp_pell_setup_rec IN OUT NOCOPY igf_gr_pell_setup_all%ROWTYPE ,
275 cp_message OUT NOCOPY VARCHAR2,
276 cp_return_status OUT NOCOPY VARCHAR2
277 )
278 IS
279 /*
280 || Created By : CDCRUZ
281 || Created On : 19-NOV-2003
282 || Purpose : Procedure to get Pell Setup Details
283 || Known limitations, enhancements or remarks :
284 || This wrapper takes in parameters
285 || of Base id / primary program and calendar
286 || and returns the Pell Setup record.
287 ||
288 || Change History :
289 || Who When What
290 || (reverse chronological order - newest change first)
291 || museshad 05-Apr-2006 While deriving Pell default setup course_cd
292 || and version_number should be checked for NULL.
293 || Fixed this.
294 || museshad 06-Mar-2006 Bug 5006587. Build FA 162 - COD Reg Updates.
295 || Added override for the newly introduced column
296 || term_start_offset_num
297 || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
298 */
299
300 CURSOR c_get_rep_pell_id(
301 l_cal_type igs_ca_inst.cal_type%TYPE,
302 l_seq_num igs_ca_inst.sequence_number%TYPE,
303 l_att_pell_id igf_gr_pell_setup_all.rep_pell_id%TYPE
304 )
305 IS
306 SELECT rep.reporting_pell_cd
307 FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
308 WHERE gap.ci_cal_type = l_cal_type
309 AND gap.ci_sequence_number = l_seq_num
310 AND gap.attending_pell_cd = l_att_pell_id
311 AND gap.rcampus_id = rep.rcampus_id;
312
313 CURSOR c_pell_setup(
314 l_ci_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
315 l_ci_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE,
316 l_reporting_pell_id igf_gr_pell_setup.rep_pell_id%TYPE,
317 l_course_cd igf_gr_pell_setup.course_cd%TYPE,
318 l_version_number igf_gr_pell_setup.version_number%TYPE
319 )
320 IS
321 SELECT pell.*
322 FROM igf_gr_pell_setup_all pell
323 WHERE pell.ci_cal_type = l_ci_cal_type
324 AND pell.ci_sequence_number = l_ci_sequence_number
325 AND pell.rep_pell_id = l_reporting_pell_id
326 AND pell.course_cd = l_course_cd
327 AND pell.version_number = l_version_number;
328
329 CURSOR c_pell_def_setup(
330 l_ci_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
331 l_ci_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE,
332 l_reporting_pell_id igf_gr_pell_setup.rep_pell_id%TYPE
333 )
334 IS
335 SELECT pell.*
336 FROM igf_gr_pell_setup_all pell
337 WHERE pell.ci_cal_type = l_ci_cal_type
338 AND pell.ci_sequence_number = l_ci_sequence_number
339 AND pell.rep_pell_id = l_reporting_pell_id
340 AND pell.course_cd IS NULL
341 AND pell.version_number IS NULL;
342
343 l_attend_pell_id igf_gr_report_pell.reporting_pell_cd%TYPE;
344 l_rep_pell_id igf_gr_attend_pell.attending_pell_cd%TYPE;
345 l_ret_status VARCHAR2(30);
346 l_msg_data VARCHAR2(30);
347 l_stu_num VARCHAR2(30);
348 l_ovrd_setup_rec igf_gr_pell_setup_all%ROWTYPE;
349
350
351 -- Entity ID declarations
352 CURSOR c_get_rep_entity_id_txt(
353 l_cal_type igs_ca_inst.cal_type%TYPE,
354 l_seq_num igs_ca_inst.sequence_number%TYPE,
355 l_atd_entity_id igf_gr_attend_pell.atd_entity_id_txt%TYPE
356 )
357 IS
358 SELECT rep.rep_entity_id_txt
359 FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
360 WHERE gap.ci_cal_type = l_cal_type
361 AND gap.ci_sequence_number = l_seq_num
362 AND gap.atd_entity_id_txt = l_atd_entity_id
363 AND gap.rcampus_id = rep.rcampus_id;
364
365 CURSOR c_pell_def_setup_cod(
366 l_ci_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE,
367 l_ci_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE,
368 l_rep_entity_id igf_gr_pell_setup_all.rep_entity_id_txt%TYPE
369 )
370 IS
371 SELECT pell.*
372 FROM igf_gr_pell_setup_all pell
373 WHERE pell.ci_cal_type = l_ci_cal_type
374 AND pell.ci_sequence_number = l_ci_sequence_number
375 AND pell.rep_entity_id_txt = l_rep_entity_id
376 AND pell.course_cd IS NULL
377 AND pell.version_number IS NULL;
378
379 CURSOR c_pell_ovrd_setup_cod(
380 l_ci_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE,
381 l_ci_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE,
382 l_rep_entity_id igf_gr_pell_setup_all.rep_entity_id_txt%TYPE,
383 l_course_cd igf_gr_pell_setup_all.course_cd%TYPE,
384 l_version_number igf_gr_pell_setup_all.version_number%TYPE
385 )
386 IS
387 SELECT pell.*
388 FROM igf_gr_pell_setup_all pell
389 WHERE pell.ci_cal_type = l_ci_cal_type
390 AND pell.ci_sequence_number = l_ci_sequence_number
391 AND pell.rep_entity_id_txt = l_rep_entity_id
392 AND pell.course_cd = l_course_cd
393 AND pell.version_number = l_version_number;
394
395 l_atd_entity_id_txt igf_gr_report_pell.rep_entity_id_txt%TYPE;
396 l_rep_entity_id_txt igf_gr_attend_pell.atd_entity_id_txt%TYPE;
397 lb_cod_year BOOLEAN;
398
399 BEGIN
400
401 lb_cod_year := igf_sl_dl_validation.check_full_participant(
402 cp_cal_type,
403 cp_sequence_number,
404 'PELL'
405 );
406
407 IF lb_cod_year
408 THEN -- cod year
409 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
410 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',' COD Year');
411 END IF;
412
413 l_atd_entity_id_txt := NULL;
414 l_rep_entity_id_txt := NULL;
415
416 -- Get attending Pell Id from Org Setup.
417 igf_sl_gen.get_stu_fao_code(
418 p_base_id => cp_base_id,
419 p_office_type => 'ENTITY_ID',
420 x_office_cd => l_atd_entity_id_txt,
421 x_return_status => l_ret_status,
422 x_msg_data => l_msg_data
423 );
424
425 IF (l_ret_status = 'E')
426 THEN
427 --get attending pell id from anticipated data
428 igf_sl_gen.get_stu_ant_fao_code(
429 p_base_id => cp_base_id,
430 p_office_type => 'ENTITY_ID',
431 x_office_cd => l_atd_entity_id_txt,
432 x_return_status => l_ret_status,
433 x_msg_data => l_msg_data
434 );
435 IF l_ret_status = 'E' THEN
436 cp_return_status := l_ret_status;
437 fnd_message.set_name('IGF', 'IGF_GR_NO_ATTEND_ENTITY_ID');
438 cp_message := fnd_message.get;
439 RETURN;
440 END IF;
441 END IF;
442
443 IF ((l_ret_status = 'S') AND (l_atd_entity_id_txt IS NOT NULL))
444 THEN
445
446 -- Derive the report pell ID.
447 OPEN c_get_rep_entity_id_txt(
448 cp_cal_type,
449 cp_sequence_number,
450 l_atd_entity_id_txt
451 );
452 FETCH c_get_rep_entity_id_txt INTO l_rep_entity_id_txt;
453 CLOSE c_get_rep_entity_id_txt;
454 END IF;
455
456 IF (l_rep_entity_id_txt IS NULL)
457 THEN
458 l_stu_num := igf_gr_gen.get_per_num(cp_base_id);
459 fnd_message.set_name('IGF', 'IGF_GR_NOREP_ENTITY');
460 fnd_message.set_token('STU_NUMBER', l_stu_num);
461 cp_message := fnd_message.get;
462 cp_return_status := 'E';
463 RETURN;
464 END IF;
465
466 cp_pell_setup_rec := NULL;
467
468 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
469 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',' COD Year l_rep_entity_id_txt ' || l_rep_entity_id_txt);
470 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',' COD Year l_atd_entity_id_txt ' || l_atd_entity_id_txt);
471 END IF;
472
473 -- Retrieve the Default Pell Setup values
474 OPEN c_pell_def_setup_cod(
475 cp_cal_type,
476 cp_sequence_number,
477 l_rep_entity_id_txt
478 );
479 FETCH c_pell_def_setup_cod INTO cp_pell_setup_rec;
480
481 IF c_pell_def_setup_cod%NOTFOUND
482 THEN
483 CLOSE c_pell_def_setup_cod;
484 cp_return_status := 'E';
485 fnd_message.set_name('IGF', 'IGF_GR_NO_PELL_SETUP_COD');
486 fnd_message.set_token('REP_ENTITY_ID_TXT', l_rep_entity_id_txt);
487 cp_message := fnd_message.get;
488 RETURN;
489 END IF;
490
491 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
492 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',' COD Year cp_pell_setup_rec.pell_seq_id ' || cp_pell_setup_rec.pell_seq_id);
493 END IF;
494
495 CLOSE c_pell_def_setup_cod;
496
497 -- Retrieve Program Level Setup if Any
498 OPEN c_pell_ovrd_setup_cod(
499 cp_cal_type,
500 cp_sequence_number,
501 l_rep_entity_id_txt,
502 cp_course_cd,
503 cp_version_number
504 );
505 FETCH c_pell_ovrd_setup_cod INTO l_ovrd_setup_rec;
506 CLOSE c_pell_ovrd_setup_cod;
507
508 IF l_ovrd_setup_rec.rep_pell_id IS NOT NULL
509 THEN
510 -- Overridden setup exist hence use the Overidden fields
511 -- Populate the Non Default Values.
512 -- Not not all fields can be overridden , hence individually the columns are replaced
513 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
514 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',' COD Year l_ovrd_setup_rec.rep_pell_id ' || l_ovrd_setup_rec.rep_pell_id);
515 END IF;
516
517 cp_pell_setup_rec.academic_cal := l_ovrd_setup_rec.academic_cal;
518 cp_pell_setup_rec.payment_method := l_ovrd_setup_rec.payment_method;
519 cp_pell_setup_rec.wk_inst_time_calc_pymt := l_ovrd_setup_rec.wk_inst_time_calc_pymt;
520 cp_pell_setup_rec.wk_int_time_prg_def_yr := l_ovrd_setup_rec.wk_int_time_prg_def_yr;
521 cp_pell_setup_rec.cr_clk_hrs_prds_sch_yr := l_ovrd_setup_rec.cr_clk_hrs_prds_sch_yr;
522 cp_pell_setup_rec.cr_clk_hrs_acad_yr := l_ovrd_setup_rec.cr_clk_hrs_acad_yr;
523 cp_pell_setup_rec.payment_periods_num := l_ovrd_setup_rec.payment_periods_num;
524 cp_pell_setup_rec.enr_before_ts_code := l_ovrd_setup_rec.enr_before_ts_code;
525 cp_pell_setup_rec.enr_in_mt_code := l_ovrd_setup_rec.enr_in_mt_code;
526 cp_pell_setup_rec.enr_after_tc_code := l_ovrd_setup_rec.enr_after_tc_code;
527 cp_pell_setup_rec.pell_seq_id := l_ovrd_setup_rec.pell_seq_id;
528 cp_pell_setup_rec.term_start_offset_num := l_ovrd_setup_rec.term_start_offset_num; -- museshad (Bug 5006587 - FA 162)
529 END IF;
530 ELSE -- phase-in award year
531
532 l_attend_pell_id := NULL;
533 l_rep_pell_id := NULL;
534 -- Get attending Pell Id from Org Setup.
535 igf_sl_gen.get_stu_fao_code(
536 p_base_id => cp_base_id,
537 p_office_type => 'PELL_ID',
538 x_office_cd => l_attend_pell_id,
539 x_return_status => l_ret_status,
540 x_msg_data => l_msg_data
541 );
542
543 IF (l_ret_status = 'E')
544 THEN
545 --try to get anticipated pell id
546 igf_sl_gen.get_stu_ant_fao_code(
547 p_base_id => cp_base_id,
548 p_office_type => 'PELL_ID',
549 x_office_cd => l_attend_pell_id,
550 x_return_status => l_ret_status,
551 x_msg_data => l_msg_data
552 );
553 IF l_ret_status = 'E' THEN
554 cp_return_status := l_ret_status;
555 fnd_message.set_name('IGF', 'IGF_GR_NO_ATTEND_PELL');
556 cp_message := fnd_message.get;
557 RETURN;
558 END IF;
559 END IF;
560
561 IF ((l_ret_status = 'S') AND (l_attend_pell_id IS NOT NULL))
562 THEN
563 -- Derive the report pell ID.
564 OPEN c_get_rep_pell_id(
565 cp_cal_type,
566 cp_sequence_number,
567 l_attend_pell_id
568 );
569 FETCH c_get_rep_pell_id INTO l_rep_pell_id;
570 CLOSE c_get_rep_pell_id;
571 END IF;
572
573 IF (l_rep_pell_id IS NULL)
574 THEN
575 l_stu_num := igf_gr_gen.get_per_num(cp_base_id);
576 fnd_message.set_name('IGF', 'IGF_GR_NOREP_PELL');
577 fnd_message.set_token('STU_NUMBER', l_stu_num);
578 cp_message := fnd_message.get;
579 cp_return_status := 'E';
580 RETURN;
581 END IF;
582
583 cp_pell_setup_rec := NULL;
584 -- Retrieve the Default Pell Setup values
585 OPEN c_pell_def_setup(cp_cal_type, cp_sequence_number, l_rep_pell_id);
586 FETCH c_pell_def_setup INTO cp_pell_setup_rec;
587
588 IF c_pell_def_setup%NOTFOUND
589 THEN
590 CLOSE c_pell_def_setup;
591 cp_return_status := 'E';
592 fnd_message.set_name('IGF', 'IGF_GR_NO_PELL_SETUP');
593 fnd_message.set_token('REP_PELL_ID', l_rep_pell_id);
594 cp_message := fnd_message.get;
595 RETURN;
596 END IF;
597
598 CLOSE c_pell_def_setup;
599 -- Retrieve Program Level Setup if Any
600 OPEN c_pell_setup(
601 cp_cal_type,
602 cp_sequence_number,
603 l_rep_pell_id,
604 cp_course_cd,
605 cp_version_number
606 );
607 FETCH c_pell_setup INTO l_ovrd_setup_rec;
608 CLOSE c_pell_setup;
609
610 IF l_ovrd_setup_rec.rep_pell_id IS NOT NULL
611 THEN
612 -- Overridden setup exist hence use the Overidden fields
613 -- Populate the Non Default Values.
614 -- Not not all fields can be overridden , hence individually the columns are replaced
615
616 cp_pell_setup_rec.academic_cal := l_ovrd_setup_rec.academic_cal;
617 cp_pell_setup_rec.payment_method := l_ovrd_setup_rec.payment_method;
618 cp_pell_setup_rec.wk_inst_time_calc_pymt := l_ovrd_setup_rec.wk_inst_time_calc_pymt;
619 cp_pell_setup_rec.wk_int_time_prg_def_yr := l_ovrd_setup_rec.wk_int_time_prg_def_yr;
620 cp_pell_setup_rec.cr_clk_hrs_prds_sch_yr := l_ovrd_setup_rec.cr_clk_hrs_prds_sch_yr;
621 cp_pell_setup_rec.cr_clk_hrs_acad_yr := l_ovrd_setup_rec.cr_clk_hrs_acad_yr;
622 cp_pell_setup_rec.payment_periods_num := l_ovrd_setup_rec.payment_periods_num;
623 cp_pell_setup_rec.enr_before_ts_code := l_ovrd_setup_rec.enr_before_ts_code;
624 cp_pell_setup_rec.enr_in_mt_code := l_ovrd_setup_rec.enr_in_mt_code;
625 cp_pell_setup_rec.enr_after_tc_code := l_ovrd_setup_rec.enr_after_tc_code;
626 cp_pell_setup_rec.pell_seq_id := l_ovrd_setup_rec.pell_seq_id;
627 cp_pell_setup_rec.term_start_offset_num := l_ovrd_setup_rec.term_start_offset_num; -- museshad (Bug 5006587 - FA 162)
628 END IF;
629 END IF; -- cod year
630
631 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
632 THEN
633 fnd_log.STRING(
634 fnd_log.level_statement,
635 'igf.plsql.igf_gr_pell_calc.get_pell_setup.debug',
636 'Pell Setup -> Rep_pell_id->'
637 || cp_pell_setup_rec.rep_pell_id
638 || ' Base_id->'
639 || TO_CHAR(cp_base_id)
640 || ' ProgCode/Ver ->'
641 || cp_pell_setup_rec.course_cd
642 || '/'
643 || TO_CHAR(cp_pell_setup_rec.version_number)
644 );
645 END IF;
646
647 IF cp_pell_setup_rec.payment_method IN ('1', '2')
648 AND NVL(cp_pell_setup_rec.payment_periods_num, 0) = 0
649 THEN
650 cp_return_status := 'E';
651 fnd_message.set_name('IGF', 'IGF_GR_INVALID_PYMNT_PRD');
652 cp_message := fnd_message.get;
653 RETURN;
654 END IF;
655
656 cp_return_status := 'S';
657 cp_message := NULL;
658 RETURN;
659
660 EXCEPTION
661 WHEN OTHERS
662 THEN
663 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
664 fnd_message.set_token(
665 'NAME',
666 'igf_gr_pell_calc.get_pell_setup ' || SQLERRM
667 );
668 igs_ge_msg_stack.ADD;
669
670 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level
671 THEN
672 fnd_log.STRING(
673 fnd_log.level_exception,
674 'igf.plsql.igf_gr_pell_calc.get_pell_setup.exception',
675 'sql error message: ' || SQLERRM
676 );
677 END IF;
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,
687 cp_pell_schedule_code OUT NOCOPY VARCHAR2,
688 cp_message OUT NOCOPY VARCHAR2,
689 cp_return_status OUT NOCOPY VARCHAR2
690 )
691 IS
692 /*
693 || Created By : CDCRUZ
694 || Created On : 19-NOV-2003
695 || Purpose : Procedure to get the pell Cost of Attendance and EFC
696 || Known limitations, enhancements or remarks :
697 || This wrapper takes in parameters
698 || of Base id / Attendance type / Pell Setup Record
699 || and returns the Pell COA / EFC / Pell Schedule used.
700 ||
701 || Change History :
702 || Who When What
703 || (reverse chronological order - newest change first)
704 || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
705 */
706
707 CURSOR c_lt_ht_coa (l_base_id NUMBER)
708 IS
709 SELECT
710 NVL(SUM(aci.pell_coa_amount),0) reg_pell_coa,
711 NVL(SUM(aci.alt_pell_amount),0) alt_pell_coa
712 FROM igf_aw_coa_items aci,
713 igf_aw_item ai
714 WHERE aci.base_id = l_base_id
715 AND aci.item_code = ai.item_code
716 AND ai.item_category_code IN ('TUITION','BOOKS','FEES','TRANSPORTATION','SUPPLIES','DEPENDENT_CARE');
717
718 c_lt_ht_coa_rec c_lt_ht_coa%ROWTYPE;
719
720
721 CURSOR c_pell_coa (l_base_id NUMBER)
722 IS
723 SELECT
724 NVL(SUM(aci.pell_coa_amount),0) reg_pell_coa,
725 NVL(SUM(aci.alt_pell_amount),0) alt_pell_coa
726 FROM igf_aw_coa_items aci
727 WHERE aci.base_id = l_base_id;
728
729
730 c_pell_coa_rec c_pell_coa%ROWTYPE;
731
732 ln_reg_coa NUMBER;
733 ln_alt_coa NUMBER;
734 ln_efc NUMBER;
735 ln_pell_efc NUMBER;
736
737 BEGIN
738
739 -- Retrieve his COA.
740 IF cp_attendance_type = '4' THEN
741 --Less Than Half Time
742
743 OPEN c_lt_ht_coa(cp_base_id) ;
744 FETCH c_lt_ht_coa INTO c_lt_ht_coa_rec;
745 CLOSE c_lt_ht_coa;
746
747 ln_reg_coa := c_lt_ht_coa_rec.REG_PELL_COA ;
748 ln_alt_coa := c_lt_ht_coa_rec.ALT_PELL_COA ;
749
750 ELSE
751 -- Not less than half time
752
753 OPEN c_pell_coa(cp_base_id) ;
754 FETCH c_pell_coa INTO c_pell_coa_rec ;
755 CLOSE c_pell_coa ;
756
757 ln_reg_coa := c_pell_coa_rec.REG_PELL_COA ;
758 ln_alt_coa := c_pell_coa_rec.ALT_PELL_COA ;
759
760 END IF;
761
762 --- Get Pell EFC
763 igf_aw_packng_subfns.get_fed_efc(
764 l_base_id => cp_base_id,
765 l_awd_prd_code => NULL,
766 l_efc_f => ln_efc,
767 l_pell_efc => ln_pell_efc,
768 l_efc_ay => ln_efc
769 );
770
771 IF ln_alt_coa <= cp_pell_setup_rec.pell_alt_exp_max AND
772 ln_pell_efc <= cp_pell_setup_rec.efc_max AND
773 ln_reg_coa >= cp_pell_setup_rec.alt_coa_limit THEN
774 cp_pell_schedule_code := 'A';
775 cp_coa := ln_reg_coa ;
776 ELSE
777 cp_pell_schedule_code := 'R';
778 cp_coa := ln_reg_coa ;
779 END IF;
780
781 cp_efc := ln_pell_efc ;
782 cp_return_status := 'S' ;
783
784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
785 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_coa_efc.debug',
786 'Pell EFC -> Pell Reg COA -> Pell Alt COA -> Pell Matrix -> '
787 || cp_efc ||' -> '||ln_reg_coa||' -> '||ln_alt_coa||' -> '||cp_pell_schedule_code);
788 END IF;
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
793 fnd_message.set_token('NAME','igf_gr_pell_calc.get_pell_coa_efc '||SQLERRM);
794 igs_ge_msg_stack.add;
795 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
796 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.get_pell_coa_efc.exception','sql error message: '||SQLERRM);
797 END IF;
798 app_exception.raise_exception;
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,
808 cp_message OUT NOCOPY VARCHAR2,
809 cp_return_status OUT NOCOPY VARCHAR2
810 )
811 IS
812 /*
813 || Created By : CDCRUZ
814 || Created On : 19-NOV-2003
815 || Purpose : Procedure to get Pell attendance type
816 || Known limitations, enhancements or remarks :
817 || This wrapper takes in parameters
818 || of Base id / load calendar in context
819 || and returns the Pell attendance type based on the setup
820 ||
821 || Change History :
822 || Who When What
823 || (reverse chronological order - newest change first)
824 || museshad 06-Mar-2006 Bug 5006587. Build FA 162.
825 || Adjust (+ or -) Term Start Date with Term Offset
826 || museshad 08-Nov-2005 Bug 4624366
827 || The Enrollment wrapper igs_en_prc_load.enrp_get_inst_latt
828 || throws an App exception when there is no Key program.
829 || Handled this.
830 || bkkumar 23-Mar-2004 Bug 3512319 Removed the hard coded base_id in the c_nominated cursor.
831 || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
832 */
833
834 p_ld_start_dt DATE;
835 p_ld_end_dt DATE;
836 p_ld_start_dt_offset DATE;
837
838 CURSOR c_isir ( l_base_id NUMBER )
839 IS
840 SELECT
841 summ_enrl_status
842 FROM igf_ap_isir_matched isir
843 WHERE
844 isir.base_id = l_base_id AND
845 isir.active_isir = 'Y' ;
846
847 CURSOR c_nominated ( l_base_id NUMBER )
848 IS
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
858 en.key_program = 'Y' AND
859 pell.cal_type = fa.ci_cal_type AND
860 pell.sequence_number = fa.ci_sequence_number AND
861 en.attendance_type = pell.attendance_type ;
862
863 CURSOR c_fabase ( l_base_id NUMBER)
864 IS
865 SELECT
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;
875
876 l_program_cd igs_en_stdnt_ps_att.course_cd%TYPE;
877 l_program_version igs_en_stdnt_ps_att.version_number%TYPE;
878 l_attendance_type igs_en_atd_type_load.attendance_type%TYPE ;
879 l_credit_pts igs_en_su_attempt.override_achievable_cp%TYPE ;
880 l_fte igs_en_su_attempt.override_achievable_cp%TYPE ;
881
882 l_effctive_cens_dt DATE;
883 l_enrl_mode igf_gr_pell_setup.enr_in_mt_code%TYPE;
884
885
886 CURSOR c_get_pell_att(
887 l_att_type igf_ap_attend_map_all.attendance_type%TYPE,
888 l_cal_type igf_ap_attend_map_all.cal_type%TYPE,
889 l_sequence_number igf_ap_attend_map_all.sequence_number%TYPE)
890 IS
891 SELECT
892 pell.pell_att_code attendance_type
893 FROM
894 igf_ap_attend_map pell
895 WHERE
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;
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
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
920 AND pell.cal_type = fa.ci_cal_type
921 AND pell.sequence_number = fa.ci_sequence_number
922 AND fa.base_id = ant.base_id;
923
924 l_app VARCHAR2(50);
925 l_name VARCHAR2(30);
926 BEGIN
927
928 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
929 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Before call to IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE');
930 END IF;
931
932 -- Get the Effective census date
933 l_effctive_cens_dt := IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE (
934 P_LOAD_CAL_TYPE => cp_ld_cal_type,
935 P_LOAD_CAL_SEQ_NUMBER => cp_ld_sequence_number,
936 P_TEACH_CAL_TYPE => NULL,
937 P_TEACH_CAL_SEQ_NUMBER => NULL
938 );
939
940 IF l_effctive_cens_dt IS NULL THEN
941 cp_return_status := 'E';
942 fnd_message.set_name('IGF','IGF_AW_EN_LD_STDA');
943 fnd_message.set_token('LD_CI_ALT_CODE',igf_gr_gen.get_alt_code(cp_ld_cal_type,cp_ld_sequence_number));
944 cp_message := fnd_message.get ;
945
946 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
947 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Error deriving Effective Census Date');
948 END IF;
949
950 RETURN;
951 END IF;
952
953 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
954 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Effective Census Date:' ||l_effctive_cens_dt);
955 END IF;
956
957 p_ld_start_dt := NULL;
958 p_ld_end_dt := NULL;
959 p_ld_start_dt_offset := NULL;
960
961 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
962 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','deriving start/end dates for '||
963 'base_id:'||cp_base_id||
964 'ld_cal_type:'||cp_ld_cal_type||
965 'ld_sequence_number:'||cp_ld_sequence_number);
966 END IF;
967 igf_ap_gen_001.get_term_dates(
968 p_base_id => cp_base_id,
969 p_ld_cal_type => cp_ld_cal_type,
970 p_ld_sequence_number => cp_ld_sequence_number,
971 p_ld_start_date => p_ld_start_dt,
972 p_ld_end_date => p_ld_end_dt
973 );
974
975 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
976 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','p_ld_start_dt:'||p_ld_start_dt);
977 END IF;
978
979 IF p_ld_start_dt IS NULL THEN
980 cp_return_status := 'E';
981 fnd_message.set_name('IGF','IGF_AW_INV_START_DT');
982 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(cp_ld_cal_type,cp_ld_sequence_number));
983 cp_message := fnd_message.get ;
984 RETURN;
985 END IF;
986
987 -- museshad (Bug 5006587 - Build FA 162). Adjust (+ or -) term start date with term offset. Use the new term start date to determine l_enrl_mode.
988 p_ld_start_dt_offset := p_ld_start_dt + NVL(cp_pell_setup_rec.term_start_offset_num, 0);
989
990 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
991 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Term Offset: ' ||NVL(cp_pell_setup_rec.term_start_offset_num, 0));
992 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Term Start date with term offset: ' ||p_ld_start_dt_offset);
993 END IF;
994 -- museshad (Bug 5006587 - Build FA 162)
995
996 IF SYSDATE < p_ld_start_dt_offset THEN
997 -- Use Before Term Start Attendance Type
998 l_enrl_mode := cp_pell_setup_rec.enr_before_ts_code ;
999
1000 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1001 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Enrl Term Rule Used >Before Term Start'
1002 ||' for '||cp_ld_cal_type||'/'||cp_ld_sequence_number);
1003 END IF;
1004
1005 ELSIF SYSDATE BETWEEN p_ld_start_dt_offset AND l_effctive_cens_dt THEN
1006 -- Use Mid Term Attendance Type
1007 l_enrl_mode := cp_pell_setup_rec.enr_in_mt_code ;
1008
1009 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1010 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Enrl Term Rule Used >Term In Progress'
1011 ||' for '||cp_ld_cal_type||'/'||cp_ld_sequence_number);
1012 END IF;
1013
1014 ELSE
1015 -- Use After Census Attendance Type
1016 l_enrl_mode := cp_pell_setup_rec.enr_after_tc_code ;
1017
1018 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1019 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Enrl Term Rule Used >After Term Census'
1020 ||' for '||cp_ld_cal_type||'/'||cp_ld_sequence_number);
1021 END IF;
1022
1023 END IF;
1024
1025 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1026 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','value of l_enrl_mode >'|| l_enrl_mode
1027 ||' for '||cp_ld_cal_type||'/'||cp_ld_sequence_number);
1028 END IF;
1029
1030 -- Derive the Literal Value of the attendance Type
1031
1032 IF l_enrl_mode = 'FT' THEN
1033 cp_attendance_type := '1';
1034
1035 ELSIF l_enrl_mode = 'HT' THEN
1036 cp_attendance_type := '3';
1037
1038 ELSIF l_enrl_mode = 'TQT' THEN
1039 cp_attendance_type := '2';
1040
1041 ELSIF l_enrl_mode = 'LTHT' THEN
1042 cp_attendance_type := '4';
1043
1044 ELSIF l_enrl_mode = 'ANTICIPATED' THEN
1045 -- get the value from the Payment ISIR
1046 OPEN c_isir(cp_base_id);
1047 FETCH c_isir INTO cp_attendance_type ;
1048 IF c_isir%NOTFOUND THEN
1049 cp_return_status := 'E';
1050 fnd_message.set_name('IGF','IGF_SL_LI_NO_ACTIVE_ISIR');
1051 cp_message := fnd_message.get ;
1052 CLOSE c_isir;
1053 RETURN;
1054 ELSE
1055 CLOSE c_isir;
1056 END IF;
1057
1058 IF (cp_attendance_type IS NULL) THEN
1059 cp_return_status := 'E';
1060 fnd_message.set_name('IGF','IGF_GR_ANTICIP_ATT_NOT_AVAIL');
1061 cp_message := fnd_message.get ;
1062 RETURN;
1063 END IF;
1064
1065 ELSIF l_enrl_mode = 'PRG_NOMINATED' THEN
1066 -- get the primary programs Nominated Attendance Type
1067 cp_attendance_type := NULL;
1068 OPEN c_nominated ( cp_base_id);
1069 FETCH c_nominated INTO cp_attendance_type;
1070 CLOSE c_nominated;
1071
1072 IF cp_attendance_type IS NULL THEN
1073 IF igf_aw_coa_gen.canUseAnticipVal THEN
1074 -- Get attendance type from FA anticipated data
1075 cp_attendance_type := NULL;
1076 OPEN c_get_ant_atype(
1077 cp_base_id => cp_base_id,
1078 cp_ld_cal_type => cp_ld_cal_type,
1079 cp_ld_sequence_number => cp_ld_sequence_number
1080 );
1081 FETCH c_get_ant_atype INTO cp_attendance_type;
1082 CLOSE c_get_ant_atype;
1083 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1084 fnd_log.string( fnd_log.level_statement,
1085 'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug',
1086 'Attendance Type from FA anticipated data is '|| NVL(l_attendance_type, ''));
1087 END IF;
1088 IF cp_attendance_type IS NULL THEN
1089 cp_return_status := 'E';
1090 fnd_message.set_name('IGF','IGF_GR_NOM_ATT_NOT_EXIST');
1091 cp_message := fnd_message.get ;
1092 RETURN;
1093 END IF;
1094 ELSE
1095 cp_return_status := 'E';
1096 fnd_message.set_name('IGF','IGF_GR_NOM_ATT_NOT_EXIST');
1097 cp_message := fnd_message.get ;
1098 RETURN;
1099 END IF;
1100 END IF;
1101
1102 ELSIF l_enrl_mode = 'ACTUAL' THEN
1103 -- get the Derived Attendance Type
1104
1105 OPEN c_fabase ( cp_base_id);
1106 FETCH c_fabase INTO l_fabase_rec;
1107 CLOSE c_fabase;
1108
1109 l_attendance_type := NULL;
1110 l_credit_pts := NULL;
1111 l_fte := NULL;
1112
1113 OPEN c_chk_enr(cp_person_id => l_fabase_rec.person_id);
1114 FETCH c_chk_enr INTO l_chk_enr_rec;
1115
1116 IF (c_chk_enr%FOUND) THEN
1117 BEGIN
1118 -- Get the derived attendnce type for the Context Term
1119 igs_en_prc_load.enrp_get_inst_latt( p_person_id => l_fabase_rec.person_id,
1120 p_load_cal_type => cp_ld_cal_type,
1121 p_load_seq_number => cp_ld_sequence_number,
1122 p_attendance => l_attendance_type,
1123 p_credit_points => l_credit_pts,
1124 p_fte => l_fte);
1125 EXCEPTION
1126 /*The above Enrollment wrapper can return an App exception (or)
1127 any unhandled exception.*/
1128 WHEN OTHERS THEN
1129 cp_message := fnd_message.get;
1130 IF cp_message IS NOT NULL THEN
1131 -- App Exception from Enrollment wrapper.
1132 -- We know how this needs to be handled.
1133 cp_return_status := 'E';
1134 RETURN;
1135 ELSE
1136 -- Unhandled Exception from Enrollment wrapper.
1137 -- We do not know how this needs to be handles, so raise it again.
1138 RAISE;
1139 END IF;
1140 END;
1141 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1142 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug','Attendance Type from the igs_en_prc_load.enrp_get_inst_latt api is '||NVL(l_attendance_type, ''));
1143 END IF;
1144
1145 END IF;
1146 CLOSE c_chk_enr;
1147
1148 IF l_attendance_type IS NULL THEN
1149 fnd_message.set_name('IGF','IGF_GR_NO_OSS_ATTEND');
1150 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(cp_ld_cal_type,cp_ld_sequence_number));
1151 cp_message := fnd_message.get;
1152 cp_return_status := 'E';
1153 cp_attendance_type := 'XX';
1154 RETURN;
1155 END IF;
1156
1157 -- Get the Pell Attendance Type
1158 OPEN c_get_pell_att(l_attendance_type,
1159 l_fabase_rec.ci_cal_type,
1160 l_fabase_rec.ci_sequence_number);
1161
1162 FETCH c_get_pell_att INTO cp_attendance_type;
1163 IF c_get_pell_att%NOTFOUND THEN
1164 fnd_message.set_name('IGF','IGF_GR_PELL_ATT_NOT_EXIST');
1165 fnd_message.set_token('ATTEND_TYPE',NVL(l_attendance_type,'Null'));
1166 cp_message := fnd_message.get;
1167 cp_return_status := 'E' ;
1168 CLOSE c_get_pell_att;
1169
1170 RETURN;
1171 END IF;
1172 CLOSE c_get_pell_att;
1173
1174 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1175 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.debug',
1176 'cp_attendance_type -> l_enrl_mode -> l_effctive_cens_dt -> '
1177 || cp_attendance_type ||' -> '||l_enrl_mode||' -> '||l_effctive_cens_dt);
1178 END IF;
1179
1180 END IF;
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1184 fnd_message.set_token('NAME','igf_gr_pell_calc.get_pell_attendance_type '||SQLERRM);
1185 igs_ge_msg_stack.add;
1186 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1187 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.get_pell_attendance_type.exception','sql error message: '||SQLERRM);
1188 END IF;
1189 app_exception.raise_exception;
1190
1191 END get_pell_attendance_type;
1192
1193 PROCEDURE get_pell_matrix_amt(
1194 cp_cal_type IN igs_ca_inst.cal_type%TYPE,
1195 cp_sequence_num IN igs_ca_inst.sequence_number%TYPE,
1196 cp_efc IN NUMBER,
1197 cp_pell_schd IN VARCHAR2,
1198 cp_enrl_stat IN VARCHAR2,
1199 cp_pell_coa IN NUMBER,
1200 cp_pell_alt_exp IN NUMBER,
1201 cp_called_from IN VARCHAR2,
1202 cp_return_status IN OUT NOCOPY VARCHAR2,
1203 cp_message IN OUT NOCOPY VARCHAR2,
1204 cp_aid IN OUT NOCOPY NUMBER
1205 )
1206 IS
1207
1208 --
1209 -- Schdl Pell Award from Regular Pell Matrix
1210 --
1211
1212 CURSOR c_pell_rng(
1213 l_ci_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
1214 l_ci_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE,
1215 l_coa igf_gr_reg_amts.coa_range_start%TYPE,
1216 l_efc igf_gr_reg_amts.efc_range_start%TYPE,
1217 l_enrl_stat igf_gr_reg_amts.enrollment_stat_code%TYPE
1218 ) IS
1219 SELECT crngd.pell_amount amount
1220 FROM igf_gr_reg_amts crngd,
1221 igf_ap_batch_aw_map_all batch
1222 WHERE crngd.enrollment_stat_code = l_enrl_stat
1223 AND batch.ci_cal_type = l_ci_cal_type
1224 AND batch.ci_sequence_number = l_ci_sequence_number
1225 AND batch.sys_award_year = crngd.sys_awd_yr
1226 AND (l_coa BETWEEN crngd.coa_range_start AND crngd.coa_range_end)
1227 AND (l_efc BETWEEN crngd.efc_range_start AND crngd.efc_range_end);
1228
1229 l_pell_rng c_pell_rng%rowtype ;
1230
1231
1232 --
1233 -- Schdl Pell Award from Alternate Pell Matrix
1234 --
1235
1236 CURSOR c_alt_pell(
1237 l_ci_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
1238 l_ci_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE,
1239 l_coa igf_gr_alt_amts.coa_range_start%TYPE,
1240 l_altexp igf_gr_alt_amts.exp_range_start%TYPE,
1241 l_efc igf_gr_reg_amts.efc_range_start%TYPE,
1242 l_enrl_stat igf_gr_reg_amts.enrollment_stat_code%TYPE
1243 ) IS
1244 SELECT alt.pell_amount amount
1245 FROM igf_gr_alt_amts alt,
1246 igf_ap_batch_aw_map_all batch
1247 WHERE alt.enrollment_stat_code = l_enrl_stat
1248 AND alt.sys_awd_yr = batch.sys_award_year
1249 AND batch.ci_cal_type = l_ci_cal_type
1250 AND batch.ci_sequence_number = l_ci_sequence_number
1251 AND (l_coa BETWEEN alt.coa_range_start AND alt.coa_range_end)
1252 AND (l_altexp BETWEEN alt.exp_range_start AND alt.exp_range_end)
1253 AND (l_efc BETWEEN alt.efc_range_start AND alt.efc_range_end);
1254
1255 l_alt_pell c_alt_pell%ROWTYPE;
1256 l_aid_passed NUMBER;
1257
1258 BEGIN
1259
1260 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1261 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_matrix_amt.debug',
1262 'Parameters in get_pell_matrix -> caltype/seq/efc/attend/coa/cp_pell_schd ->' || cp_cal_type || '/' ||
1263 TO_CHAR(cp_sequence_num) || '/' || TO_CHAR(cp_efc) || '/' || cp_enrl_stat || '/' || TO_CHAR(cp_pell_coa) ||'/'||cp_pell_schd );
1264 END IF;
1265
1266 IF cp_pell_schd = 'R' THEN
1267
1268 -- Use Regular Pell Matrix
1269
1270 OPEN c_pell_rng (cp_cal_type,
1271 cp_sequence_num,
1272 cp_pell_coa,
1273 cp_efc,
1274 cp_enrl_stat);
1275 FETCH c_pell_rng into l_pell_rng ;
1276
1277 IF c_pell_rng%FOUND THEN
1278 CLOSE c_pell_rng;
1279 cp_aid := l_pell_rng.amount ;
1280 ELSE
1281 fnd_message.set_name('IGF','IGF_GR_PELL_RNG_ERR_FRM');
1282 IF cp_pell_coa IS NULL THEN
1283 fnd_message.set_token('COA','NULL');
1284 ELSE
1285 fnd_message.set_token('COA',TO_CHAR(cp_pell_coa));
1286 END IF;
1287 IF cp_efc IS NULL THEN
1288 fnd_message.set_token('EFC','NULL');
1289 ELSE
1290 fnd_message.set_token('EFC',TO_CHAR(cp_efc));
1291 END IF;
1292 fnd_message.set_token('ATT',NVL(igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT',cp_enrl_stat),'NULL'));
1293 CLOSE c_pell_rng;
1294 cp_return_status := 'E';
1295 cp_message := fnd_message.get ;
1296 RETURN;
1297 END IF;
1298
1299 ELSE
1300
1301 OPEN c_alt_pell ( cp_cal_type ,
1302 cp_sequence_num ,
1303 cp_pell_coa,
1304 cp_pell_alt_exp ,
1305 cp_efc ,
1306 cp_enrl_stat);
1307
1308 FETCH c_alt_pell INTO l_alt_pell;
1309
1310 IF c_alt_pell%FOUND THEN
1311 CLOSE c_alt_pell;
1312 cp_aid := l_alt_pell.amount ;
1313
1314 ELSE
1315 fnd_message.set_name('IGF','IGF_GR_PELL_RNG_ERR_FRM_ALT');
1316 IF cp_pell_coa IS NULL THEN
1317 fnd_message.set_token('COA','NULL');
1318 ELSE
1319 fnd_message.set_token('COA',TO_CHAR(cp_pell_coa));
1320 END IF;
1321 IF cp_efc IS NULL THEN
1322 fnd_message.set_token('EFC','NULL');
1323 ELSE
1324 fnd_message.set_token('EFC',TO_CHAR(cp_efc));
1325 END IF;
1326 fnd_message.set_token('ATT',NVL(igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT',cp_enrl_stat),'NULL'));
1327 IF cp_pell_alt_exp IS NULL THEN
1328 fnd_message.set_token('ALT_EXP','NULL');
1329 ELSE
1330 fnd_message.set_token('ALT_EXP',TO_CHAR(cp_pell_alt_exp));
1331 END IF;
1332
1333 cp_aid := 0 ;
1334 CLOSE c_alt_pell;
1335 cp_return_status := 'E';
1336 cp_message := fnd_message.get ;
1337 RETURN;
1338
1339 END IF;
1340
1341 END IF;
1342
1343 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1344 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.get_pell_matrix_amt.debug',
1345 'cp_aid -> '|| cp_aid);
1346 END IF;
1347
1348 RETURN;
1349
1350 EXCEPTION
1351 WHEN NO_SETUP THEN
1352 RAISE;
1353
1354 WHEN OTHERS THEN
1355 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1356 fnd_message.set_token('NAME','igf_gr_pell_calc.get_pell_matrix_amt '||SQLERRM);
1357 igs_ge_msg_stack.add;
1358 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1359 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.get_pell_matrix_amt.exception','sql error message: '||SQLERRM);
1360 END IF;
1361 app_exception.raise_exception;
1362
1363 END get_pell_matrix_amt ;
1364
1365 FUNCTION num_disb(
1366 p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1367 p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
1368 p_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE
1369 ) RETURN NUMBER AS
1370 ------------------------------------------------------------------
1371 --Created by : veramach, Oracle India
1372 --Date created: 24/August/2005
1373 --
1374 --Purpose:
1375 --
1376 --
1377 --Known limitations/enhancements and/or remarks:
1378 --
1379 --Change History:
1380 --Who When What
1381 -------------------------------------------------------------------
1382
1383 -- Get number of disbursements
1384 CURSOR c_num_disb(
1385 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1386 cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
1387 cp_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE
1388 ) IS
1389 SELECT COUNT (*) num_disb
1390 FROM igf_aw_dp_terms terms,
1391 igf_aw_dp_teach_prds teach_periods
1392 WHERE terms.adplans_id = cp_adplans_id
1393 AND terms.adterms_id = teach_periods.adterms_id
1394 AND terms.ld_cal_type = NVL(cp_ld_cal_type,terms.ld_cal_type)
1395 AND terms.ld_sequence_number = NVL(cp_ld_sequence_number,terms.ld_sequence_number);
1396 l_num_disb NUMBER;
1397
1398 BEGIN
1399 OPEN c_num_disb(p_adplans_id,p_ld_cal_type,p_ld_sequence_number);
1400 FETCH c_num_disb INTO l_num_disb;
1401 CLOSE c_num_disb;
1402
1403 RETURN l_num_disb;
1404 END num_disb;
1405
1406 PROCEDURE round_term_disbursements (
1407 p_pell_tab IN OUT NOCOPY pell_tab,
1408 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1409 p_dist_plan_code IN igf_aw_awd_dist_plans.dist_plan_method_code%TYPE,
1410 p_ld_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1411 p_ld_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
1412 p_term_amt IN NUMBER,
1413 p_tp_count IN NUMBER,
1414 p_pkg_awd_status IN igf_aw_fund_mast_all.pckg_awd_stat%TYPE,
1415 p_return_status OUT NOCOPY VARCHAR2
1416 )
1417 IS
1418 /*
1419 || Created By : museshad
1420 || Created On : 12-Sep-2005
1421 || Purpose : Round Pell disbursements based on the distribution method and rounding factor
1422 || Known limitations, enhancements or remarks :
1423 || Refer igf_aw_packaging.round_off_disbursements() for the explanation of the logic
1424 || Change History :
1425 || Who When What
1426 || (reverse chronological order - newest change first)
1427 */
1428
1429 l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
1430 l_trunc_factor NUMBER := 0;
1431 l_extra_factor NUMBER := 0;
1432 l_disb_no NUMBER := 0;
1433 l_special_disb_no NUMBER := 0;
1434 l_step NUMBER := 0;
1435 l_disb_amt NUMBER := 0;
1436 l_disb_prelim_amt NUMBER := 0;
1437 l_disb_amt_extra NUMBER := 0;
1438 l_disb_inter_sum_amt NUMBER := 0;
1439 l_disb_diff NUMBER := 0;
1440 l_term_disb_cnt NUMBER := 0;
1441 l_term_found BOOLEAN := FALSE;
1442
1443 BEGIN
1444 -- Get the disbursement rounding factor
1445 l_disb_round_factor := igf_aw_packaging.get_disb_round_factor(p_fund_id => p_fund_id);
1446 l_term_found := FALSE;
1447
1448 IF l_disb_round_factor NOT IN ('ONE_FIRST', 'DEC_FIRST', 'ONE_LAST', 'DEC_LAST') THEN
1449 -- Invalid disbursement round factor. Return with Err status
1450 p_return_status := 'E';
1451 RETURN;
1452 END IF;
1453
1454 -- Log useful values
1455 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1456 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Into round_term_disbursements. Parameters received ...');
1457 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Rounding disbursements for the term: '||p_ld_cal_type||', '||p_ld_seq_num);
1458 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Term level amount: '||p_term_amt);
1459 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Teaching Period count: '||p_tp_count);
1460 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Disbursement rounding factor: '||l_disb_round_factor);
1461 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'Distribution plan code: ' || p_dist_plan_code);
1462 END IF;
1463
1464 -- Set the attributes common to ONEs rounding factor
1465 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'ONE_LAST' THEN
1466 l_trunc_factor := 0;
1467 l_extra_factor := 1;
1468 -- Set the attributes common to DECIMALs rounding factor
1469 ELSIF l_disb_round_factor = 'DEC_FIRST' OR l_disb_round_factor = 'DEC_LAST' THEN
1470 l_trunc_factor := 2;
1471 l_extra_factor := 0.01;
1472 END IF;
1473
1474 -- Set the attributes common to FIRST rounding factor
1475 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'DEC_FIRST' THEN
1476 l_disb_no := 1;
1477 l_step := 1;
1478
1479 IF p_dist_plan_code IN ('C', 'M') THEN
1480 l_special_disb_no := 1; -- First disbursement in the term
1481 END IF;
1482
1483 -- Set the attributes common to LAST rounding factor
1484 ELSIF l_disb_round_factor = 'ONE_LAST' OR l_disb_round_factor = 'DEC_LAST' THEN
1485 l_disb_no := p_pell_tab.COUNT;
1486 l_step := -1;
1487
1488 IF p_dist_plan_code IN ('C', 'M') THEN
1489 l_special_disb_no := p_tp_count; -- Last disbursement in the term
1490 END IF;
1491 END IF;
1492
1493 -- Log values
1494 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1495 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_trunc_factor: ' ||l_trunc_factor);
1496 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_extra_factor: ' ||l_extra_factor);
1497 END IF;
1498
1499 ------------------------------------
1500 -- EVEN Distribution
1501 ------------------------------------
1502 IF p_dist_plan_code = 'E' THEN
1503
1504 -- Normal disbursement amount
1505 l_disb_amt := TRUNC(NVL((p_term_amt/p_tp_count), 0), l_trunc_factor);
1506
1507 -- Preliminary disbursement amount
1508 l_disb_prelim_amt := TRUNC(NVL((p_term_amt - (l_disb_amt * (p_tp_count-1))), 0), l_trunc_factor);
1509
1510 -- Difference in disbursement amount
1511 l_disb_diff := TRUNC(NVL((l_disb_prelim_amt - l_disb_amt), 0), l_trunc_factor);
1512
1513 -- Extra disbursement amount
1514 IF l_disb_diff > 0 THEN
1515 l_disb_amt_extra := TRUNC(NVL((l_disb_amt + l_extra_factor), 0), l_trunc_factor);
1516 ELSIF l_disb_diff < 0 THEN
1517 l_disb_amt_extra := TRUNC(NVL((l_disb_amt - l_extra_factor), 0), l_trunc_factor);
1518 ELSE
1519 l_disb_amt_extra := TRUNC(NVL(l_disb_amt, 0), l_trunc_factor);
1520 END IF;
1521
1522 -- Get the absolute difference value between preliminary and normal disbursement amount
1523 l_disb_diff := ABS(l_disb_diff);
1524
1525 -- Log values
1526 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1527 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_disb_diff: ' ||l_disb_diff);
1528 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_disb_prelim_amt: ' ||l_disb_prelim_amt);
1529 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_disb_amt_extra: ' ||l_disb_amt_extra);
1530 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_disb_amt: ' ||l_disb_amt);
1531 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug', 'l_step: ' ||l_step);
1532 END IF;
1533
1534 WHILE l_disb_no BETWEEN 1 AND p_pell_tab.COUNT
1535 LOOP
1536 -- Check if it is the current term's disbursement
1537 IF (p_pell_tab.EXISTS(l_disb_no)) AND
1538 (p_pell_tab(l_disb_no).ld_cal_type = p_ld_cal_type AND p_pell_tab(l_disb_no).ld_sequence_number = p_ld_seq_num) THEN
1539
1540 l_term_found := TRUE;
1541
1542 -- Give the extra amount to each disbursement
1543 IF l_disb_diff >= l_extra_factor THEN
1544 -- Log
1545 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1546 fnd_log.string(fnd_log.level_statement,
1547 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug',
1548 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt||' New rounded disb Amt= '||l_disb_amt_extra);
1549 END IF;
1550
1551 p_pell_tab(l_disb_no).offered_amt := l_disb_amt_extra;
1552 l_disb_diff := NVL((l_disb_diff - l_extra_factor), 0);
1553 ELSE
1554 -- Log
1555 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1556 fnd_log.string(fnd_log.level_statement,
1557 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug',
1558 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt||' New rounded disb Amt= '||l_disb_amt);
1559 END IF;
1560
1561 p_pell_tab(l_disb_no).offered_amt := l_disb_amt;
1562 END IF;
1563 END IF;
1564
1565 /* Update the Accepted amount */
1566 IF p_pkg_awd_status = 'ACCEPTED' THEN
1567 p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
1568 ELSE
1569 p_pell_tab(l_disb_no).accepted_amt := NULL;
1570 END IF;
1571
1572 l_disb_no := NVL(l_disb_no, 0) + l_step;
1573 END LOOP;
1574
1575 ------------------------------------
1576 -- MATCH COA/MANUAL Distribution
1577 ------------------------------------
1578 ELSIF p_dist_plan_code IN ('C', 'M') THEN
1579 l_term_disb_cnt := 0;
1580
1581 WHILE l_disb_no BETWEEN 1 AND p_pell_tab.COUNT
1582 LOOP
1583 IF (p_pell_tab.EXISTS(l_disb_no)) AND
1584 (p_pell_tab(l_disb_no).ld_cal_type = p_ld_cal_type AND p_pell_tab(l_disb_no).ld_sequence_number = p_ld_seq_num) THEN
1585
1586 l_term_found := TRUE;
1587
1588 l_term_disb_cnt := l_term_disb_cnt + 1;
1589
1590 IF l_term_disb_cnt <> l_special_disb_no THEN
1591 -- Other disbursements
1592 -- Log
1593 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1594 fnd_log.string(fnd_log.level_statement,
1595 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug',
1596 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt
1597 ||' New rounded disb Amt= '||(TRUNC(NVL(p_pell_tab(l_disb_no).offered_amt, 0), l_trunc_factor)));
1598 END IF;
1599
1600 p_pell_tab(l_disb_no).offered_amt := TRUNC(NVL(p_pell_tab(l_disb_no).offered_amt, 0), l_trunc_factor);
1601
1602 -- Calculate running total of other disbursements
1603 l_disb_inter_sum_amt := NVL((l_disb_inter_sum_amt + p_pell_tab(l_disb_no).offered_amt), 0);
1604 ELSE
1605 -- Special (First/Last) disbursement
1606 -- Log
1607 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1608 fnd_log.string(fnd_log.level_statement,
1609 'igf.plsql.igf_gr_pell_calc.round_term_disbursements.debug',
1610 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt
1611 ||' New rounded disb Amt= '||(TRUNC(NVL((p_term_amt - l_disb_inter_sum_amt), 0), l_trunc_factor)));
1612 END IF;
1613 p_pell_tab(l_disb_no).offered_amt := TRUNC(NVL((p_term_amt - l_disb_inter_sum_amt), 0), l_trunc_factor);
1614 END IF;
1615 END IF;
1616
1617 /* Update the Accepted amount */
1618 IF p_pkg_awd_status = 'ACCEPTED' THEN
1619 p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
1620 ELSE
1621 p_pell_tab(l_disb_no).accepted_amt := NULL;
1622 END IF;
1623
1624 l_disb_no := NVL(l_disb_no, 0) + l_step;
1625 END LOOP;
1626 END IF;
1627
1628 -- Check if the term passed was found in PL/SQL table.
1629 -- if no, then it is an error.
1630 IF NOT l_term_found THEN
1631 p_return_status := 'E';
1632 RETURN;
1633 END IF;
1634
1635 p_return_status := 'S';
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1639 fnd_message.set_token('NAME','igf_gr_pell_calc.round_term_disbursements '||SQLERRM);
1640 igs_ge_msg_stack.add;
1641
1642 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1643 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.round_term_disbursements.exception','sql error message: '||SQLERRM);
1644 END IF;
1645
1646 app_exception.raise_exception;
1647 END round_term_disbursements;
1648
1649
1650 PROCEDURE round_all_disbursements(
1651 p_pell_tab IN OUT NOCOPY pell_tab,
1652 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
1653 p_dist_plan_code IN igf_aw_awd_dist_plans.dist_plan_method_code%TYPE,
1654 p_aid IN NUMBER,
1655 p_disb_count IN NUMBER,
1656 p_pkg_awd_status IN igf_aw_fund_mast_all.pckg_awd_stat%TYPE,
1657 p_return_status OUT NOCOPY VARCHAR2
1658 ) AS
1659 ------------------------------------------------------------------
1660 --Created by : veramach, Oracle India
1661 --Date created:
1662 --
1663 --Purpose:
1664 --
1665 --
1666 --Known limitations/enhancements and/or remarks:
1667 --
1668 --Change History:
1669 --Who When What
1670 -------------------------------------------------------------------
1671 l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE := NULL;
1672 l_trunc_factor NUMBER := 0;
1673 l_extra_factor NUMBER := 0;
1674 l_disb_no NUMBER := 0;
1675 l_special_disb_no NUMBER := 0;
1676 l_step NUMBER := 0;
1677 l_disb_amt NUMBER := 0;
1678 l_disb_prelim_amt NUMBER := 0;
1679 l_disb_amt_extra NUMBER := 0;
1680 l_disb_inter_sum_amt NUMBER := 0;
1681 l_disb_diff NUMBER := 0;
1682
1683 BEGIN
1684 l_disb_round_factor := igf_aw_packaging.get_disb_round_factor(p_fund_id => p_fund_id);
1685
1686 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1687 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'Into round_all_disbursements. Parameters received ...');
1688 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'p_aid: '||p_aid);
1689 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'disb count: '||p_disb_count);
1690 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'Disbursement rounding factor: '||l_disb_round_factor);
1691 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'Distribution plan code: ' || p_dist_plan_code);
1692 END IF;
1693
1694 -- Set the attributes common to ONEs rounding factor
1695 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'ONE_LAST' THEN
1696 l_trunc_factor := 0;
1697 l_extra_factor := 1;
1698 -- Set the attributes common to DECIMALs rounding factor
1699 ELSIF l_disb_round_factor = 'DEC_FIRST' OR l_disb_round_factor = 'DEC_LAST' THEN
1700 l_trunc_factor := 2;
1701 l_extra_factor := 0.01;
1702 END IF;
1703
1704 -- Set the attributes common to FIRST rounding factor
1705 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'DEC_FIRST' THEN
1706 l_disb_no := 1;
1707 l_step := 1;
1708
1709 IF p_dist_plan_code IN ('C', 'M') THEN
1710 l_special_disb_no := 1; -- First disbursement
1711 END IF;
1712
1713 -- Set the attributes common to LAST rounding factor
1714 ELSIF l_disb_round_factor = 'ONE_LAST' OR l_disb_round_factor = 'DEC_LAST' THEN
1715 l_disb_no := p_pell_tab.COUNT;
1716 l_step := -1;
1717
1718 IF p_dist_plan_code IN ('C', 'M') THEN
1719 l_special_disb_no := p_disb_count; -- Last disbursement
1720 END IF;
1721 END IF;
1722
1723 -- Log values
1724 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1725 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_trunc_factor: ' ||l_trunc_factor);
1726 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_extra_factor: ' ||l_extra_factor);
1727 END IF;
1728
1729 IF p_dist_plan_code = 'E' THEN
1730
1731 -- Normal disbursement amount
1732 l_disb_amt := TRUNC(NVL((p_aid/p_disb_count), 0), l_trunc_factor);
1733
1734 -- Preliminary disbursement amount
1735 l_disb_prelim_amt := TRUNC(NVL((p_aid - (l_disb_amt * (p_disb_count-1))), 0), l_trunc_factor);
1736
1737 -- Difference in disbursement amount
1738 l_disb_diff := TRUNC(NVL((l_disb_prelim_amt - l_disb_amt), 0), l_trunc_factor);
1739
1740 -- Extra disbursement amount
1741 IF l_disb_diff > 0 THEN
1742 l_disb_amt_extra := TRUNC(NVL((l_disb_amt + l_extra_factor), 0), l_trunc_factor);
1743 ELSIF l_disb_diff < 0 THEN
1744 l_disb_amt_extra := TRUNC(NVL((l_disb_amt - l_extra_factor), 0), l_trunc_factor);
1745 ELSE
1746 l_disb_amt_extra := TRUNC(NVL(l_disb_amt, 0), l_trunc_factor);
1747 END IF;
1748
1749 -- Get the absolute difference value between preliminary and normal disbursement amount
1750 l_disb_diff := ABS(l_disb_diff);
1751
1752 -- Log values
1753 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1754 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_disb_diff: ' ||l_disb_diff);
1755 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_disb_prelim_amt: ' ||l_disb_prelim_amt);
1756 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_disb_amt_extra: ' ||l_disb_amt_extra);
1757 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_disb_amt: ' ||l_disb_amt);
1758 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug', 'l_step: ' ||l_step);
1759 END IF;
1760
1761 WHILE l_disb_no BETWEEN 1 AND p_pell_tab.COUNT
1762 LOOP
1763 -- Check if it is the current term's disbursement
1764 IF (p_pell_tab.EXISTS(l_disb_no)) THEN
1765
1766 -- Give the extra amount to each disbursement
1767 IF l_disb_diff >= l_extra_factor THEN
1768 -- Log
1769 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1770 fnd_log.string(fnd_log.level_statement,
1771 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug',
1772 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt||' New rounded disb Amt= '||l_disb_amt_extra);
1773 END IF;
1774
1775 p_pell_tab(l_disb_no).offered_amt := l_disb_amt_extra;
1776 l_disb_diff := NVL((l_disb_diff - l_extra_factor), 0);
1777 ELSE
1778 -- Log
1779 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1780 fnd_log.string(fnd_log.level_statement,
1781 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug',
1782 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt||' New rounded disb Amt= '||l_disb_amt);
1783 END IF;
1784
1785 p_pell_tab(l_disb_no).offered_amt := l_disb_amt;
1786 END IF;
1787 END IF;
1788
1789 /* Update the Accepted amount */
1790 IF p_pkg_awd_status = 'ACCEPTED' THEN
1791 p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
1792 ELSE
1793 p_pell_tab(l_disb_no).accepted_amt := NULL;
1794 END IF;
1795
1796 l_disb_no := NVL(l_disb_no, 0) + l_step;
1797 END LOOP;
1798
1799 ------------------------------------
1800 -- MATCH COA/MANUAL Distribution
1801 ------------------------------------
1802 ELSIF p_dist_plan_code IN ('C', 'M') THEN
1803
1804 WHILE l_disb_no BETWEEN 1 AND p_pell_tab.COUNT
1805 LOOP
1806 IF (p_pell_tab.EXISTS(l_disb_no)) THEN
1807
1808 IF l_disb_no <> l_special_disb_no THEN
1809 -- Other disbursements
1810 -- Log
1811 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1812 fnd_log.string(fnd_log.level_statement,
1813 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug',
1814 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt
1815 ||' New rounded disb Amt= '||(TRUNC(NVL(p_pell_tab(l_disb_no).offered_amt, 0), l_trunc_factor)));
1816 END IF;
1817
1818 p_pell_tab(l_disb_no).offered_amt := TRUNC(NVL(p_pell_tab(l_disb_no).offered_amt, 0), l_trunc_factor);
1819
1820 -- Calculate running total of other disbursements
1821 l_disb_inter_sum_amt := NVL((l_disb_inter_sum_amt + p_pell_tab(l_disb_no).offered_amt), 0);
1822 ELSE
1823 -- Special (First/Last) disbursement
1824 -- Log
1825 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1826 fnd_log.string(fnd_log.level_statement,
1827 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug',
1828 'Disb No= '||l_disb_no||' Old Disb Amt= '||p_pell_tab(l_disb_no).offered_amt
1829 ||' New rounded disb Amt= '||(TRUNC(NVL((p_aid - l_disb_inter_sum_amt), 0), l_trunc_factor)));
1830 END IF;
1831 p_pell_tab(l_disb_no).offered_amt := TRUNC(NVL((p_aid - l_disb_inter_sum_amt), 0), l_trunc_factor);
1832 END IF;
1833 END IF;
1834
1835 /* Update the Accepted amount */
1836 IF p_pkg_awd_status = 'ACCEPTED' THEN
1837 p_pell_tab(l_disb_no).accepted_amt := p_pell_tab(l_disb_no).offered_amt;
1838 ELSE
1839 p_pell_tab(l_disb_no).accepted_amt := NULL;
1840 END IF;
1841
1842 l_disb_no := NVL(l_disb_no, 0) + l_step;
1843 END LOOP;
1844 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1845 fnd_log.string(fnd_log.level_statement,
1846 'igf.plsql.igf_gr_pell_calc.round_all_disbursements.debug',
1847 'Disb No= '||l_special_disb_no||' Old Disb Amt= '||p_pell_tab(l_special_disb_no).offered_amt
1848 ||' New rounded disb Amt= '|| (TRUNC(NVL((p_aid - l_disb_inter_sum_amt), 0), l_trunc_factor)));
1849 END IF;
1850 p_pell_tab(l_special_disb_no).offered_amt := TRUNC(NVL((p_aid - l_disb_inter_sum_amt), 0), l_trunc_factor);
1851 IF p_pkg_awd_status = 'ACCEPTED' THEN
1852 p_pell_tab(l_special_disb_no).accepted_amt := p_pell_tab(l_special_disb_no).offered_amt;
1853 ELSE
1854 p_pell_tab(l_special_disb_no).accepted_amt := NULL;
1855 END IF;
1856 END IF;
1857 p_return_status := 'S';
1858
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1862 fnd_message.set_token('NAME','igf_gr_pell_calc.round_all_disbursements '||SQLERRM);
1863 igs_ge_msg_stack.add;
1864 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1865 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.round_all_disbursements.exception','sql error message: '||SQLERRM);
1866 END IF;
1867 app_exception.raise_exception;
1868 END round_all_disbursements;
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,
1878 cp_called_from IN VARCHAR2,
1879 cp_pell_seq_id OUT NOCOPY igf_gr_pell_setup_all.pell_seq_id%TYPE,
1880 cp_pell_schedule_code OUT NOCOPY VARCHAR2
1881 ) IS
1882 /*
1883 || Created By : CDCRUZ
1884 || Created On : 19-NOV-2003
1885 || Purpose : Procedure to calculate the Pell Award
1886 || Known limitations, enhancements or remarks :
1887 || This wrapper takes in parameters
1888 || of Base id / fund id/ plan id
1889 || and returns the Pell award / A Pl/Sql table with the disbursements
1890 || and a return status of S/E => Success/Error
1891 || Also returns the Pk to the Pell Setup record used for processing
1892 ||
1893 || Variable description:
1894 || l_aid Annual Pell amount
1895 || cp_aid Pell Award amount
1896 || l_term_amt Pell term-level award amount
1897 || l_tp_amt Teaching-period level award amount
1898 || cp_pell_tab PL/SQL table containing a record for each Teaching Period
1899 || c_terms_det Cursor to loop thru each disb in (COA + DP) terms
1900 ||
1901 || Change History :
1902 || Who When What
1903 || (reverse chronological order - newest change first)
1904 || museshad 12-Sep-2005 Build FA 157.
1905 || Implemented Pell disbursement rounding
1906 || museshad 20-Jun-2005 Build# FA157 - Bug# 4382371.
1907 || 1) Added another check to determine Pell eligibility.
1908 || Check the completed programs in the Student's
1909 || Program Attempts history and check if there are
1910 || any non 'PRE-BACHELORS' programs. If so,
1911 || the Student is not eligible for Pell.
1912 || Modified the cursor 'c_acad_hist' to avoid unnecessary
1913 || looping.
1914 || 2) Get key program data from Admissions/FA Anticipated data
1915 || when actual key program data is not available
1916 || bkkumar 21-July-2004 Bug# 3778277 This total amount check validation should happen
1917 || only if the attendance type is same for all terms.
1918 || Also added the validation to validate the amount to be awarded
1919 || against the full time pell amount.
1920 || veramach 01-Jul-2004 bug # 3729182 Added logic to check whether cp_pell_tab is null
1921 || bkkumar 01-Apr-04 Bug# 3409969 Added the logic to calculate the pell
1922 || award amount when the pell_formula = 3 , to derive the
1923 || term_instruction_time and annual instruction time
1924 || from the OSS.
1925 || Also the rounding off logic is implemented that in case the
1926 || attendance type is same for all the terms for the student and
1927 || if the number of terms enrolled is same as the payment periods
1928 || then add the balance pell amount if any to the last pell term amount.
1929 || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
1930 */
1931
1932 -- Get the Academic History of the Student to check if He is
1933 -- Eligible for Pell.
1934 CURSOR c_acad_hist(
1935 l_base_id NUMBER
1936 ) IS
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
1946 AND dc.program_type = ptype.course_type
1947 AND UPPER(ptype.fin_aid_program_type) IN ('BACHELORS','PROFESSIONAL');
1948 l_acad_hist c_acad_hist%ROWTYPE;
1949
1950 -- check whether the student is eligibile for PELL Grant per the context
1951 -- Payment ISIR.
1952
1953 CURSOR c_pell_elig ( l_base_id NUMBER ) IS
1954 SELECT NVL(pell_grant_elig_flag, 'N') pell_grant_elig_flag,
1955 transaction_num
1956 FROM igf_ap_isir_matched ism
1957 WHERE ism.base_id = l_base_id
1958 AND ism.active_isir = 'Y';
1959
1960 l_pell_elig c_pell_elig%ROWTYPE;
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
1970 terms.ld_cal_type,
1971 terms.ld_sequence_number
1972 FROM
1973 igf_aw_dp_terms terms,
1974 igf_aw_coa_itm_terms coa
1975 WHERE
1976 coa.base_id = l_base_id
1977 AND terms.adplans_id = l_adplans_id
1978 AND terms.ld_cal_type = coa.ld_cal_type
1979 AND terms.ld_sequence_number = coa.ld_sequence_number
1980 GROUP by terms.ld_cal_type,terms.ld_sequence_number
1981 );
1982
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 (
1992 SELECT
1993 terms.ld_cal_type,
1994 terms.ld_sequence_number
1995 FROM
1996 igf_aw_dp_terms terms
1997 WHERE
1998 terms.adplans_id = l_adplans_id
1999 GROUP by terms.ld_cal_type,terms.ld_sequence_number
2000 );
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,
2010 terms.ld_cal_type,
2011 terms.ld_sequence_number
2012 ),
2013 teach.start_date) disb_dt,
2014 terms.ld_cal_type,
2015 terms.ld_sequence_number,
2016 teach.tp_cal_type,
2017 teach.tp_sequence_number,
2018 teach.tp_perct_num,
2019 teach.attendance_type_code,
2020 teach.credit_points_num,
2021 teach.date_offset_cd
2022 FROM igf_aw_dp_terms terms,
2023 igf_aw_dp_teach_prds_v teach
2024 WHERE terms.adplans_id = l_adplans_id
2025 AND teach.adterms_id = terms.adterms_id
2026 AND (terms.ld_cal_type, terms.ld_sequence_number) IN (
2027 SELECT coa.ld_cal_type,
2028 coa.ld_sequence_number
2029 FROM igf_aw_coa_itm_terms coa
2030 WHERE coa.base_id = l_base_id)
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,
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
2047 -- Get The Fund Details
2048 CURSOR c_fund ( l_fund_id igf_aw_fund_mast_all.fund_id%TYPE
2049 ) IS
2050 SELECT
2051 fm.disb_exp_da,
2052 fm.disb_verf_da,
2053 fm.show_on_bill,
2054 fm.pckg_awd_stat
2055 FROM igf_aw_fund_mast fm
2056 WHERE
2057 fm.fund_id = l_fund_id ;
2058
2059 l_fund_rec c_fund%ROWTYPE;
2060
2061 CURSOR c_get_ofst( cp_ofst_da igs_ca_da.dt_alias%TYPE,
2062 cp_tp_Cal_type igs_ca_inst.cal_type%TYPE,
2063 cp_ci_sequence igs_ca_inst.sequence_number%TYPE,
2064 cp_cur_da igs_ca_da.dt_alias%TYPE) IS
2065 SELECT dai.absolute_val ofst_absolute_val,
2066 dai.derived_val ofst_derived_val
2067 FROM igs_ca_da_inst_ofst ofst,
2068 igs_ca_da_inst_v dai,
2069 igs_ca_da_inst rel
2070 WHERE rel.dt_alias = cp_ofst_da
2071 AND rel.cal_type = cp_tp_cal_type
2072 AND rel.ci_sequence_number = cp_ci_Sequence
2073 AND ofst.dt_alias = cp_cur_da
2074 AND ofst.offset_dt_alias = cp_ofst_da
2075 AND ofst.offset_dai_sequence_number = rel.sequence_number
2076 AND dai.dt_alias = ofst.dt_alias
2077 AND dai.sequence_number = ofst.dai_sequence_number;
2078
2079 l_ofst_rec c_get_ofst%ROWTYPE;
2080
2081 -- Get CP Plan details
2082 CURSOR c_dp_details(
2083 l_plan_id igf_aw_awd_dist_plans.adplans_id%TYPE) IS
2084 SELECT
2085 dp.adplans_id,
2086 dp.awd_dist_plan_cd,
2087 dp.awd_dist_plan_cd_desc,
2088 dp.dist_plan_method_code
2089 FROM
2090 igf_aw_awd_dist_plans dp
2091 WHERE
2092 dp.adplans_id = l_plan_id;
2093
2094 l_dp_details_rec c_dp_details%ROWTYPE;
2095
2096 l_pell_setup_rec igf_gr_pell_setup_all%ROWTYPE;
2097 l_pell_schedule VARCHAR2(30);
2098 l_pell_attend_type VARCHAR2(30);
2099 l_message fnd_new_messages.message_text%TYPE;
2100 l_return_status VARCHAR2(30);
2101 l_coa NUMBER;
2102 l_efc NUMBER;
2103 l_aid NUMBER;
2104 l_term_amt NUMBER;
2105 l_running_term NUMBER;
2106 l_cnt NUMBER;
2107 l_disb_num NUMBER;
2108 l_tp_amt NUMBER;
2109 l_actual_weeks NUMBER;
2110 l_disb_exp_dt DATE;
2111 l_verif_enfr_dt DATE;
2112 l_program_cd igs_en_stdnt_ps_att.course_cd%TYPE;
2113 l_program_version igs_en_stdnt_ps_att.version_number%TYPE;
2114 l_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
2115 pell_att_flag BOOLEAN := TRUE;
2116 l_term_cnt NUMBER;
2117 old_pell_att_type igf_ap_attend_map.attendance_type%TYPE;
2118 l_term_weeks NUMBER;
2119 l_total_term_weeks NUMBER;
2120 l_pell_amt NUMBER;
2121 l_ft_pell_amt NUMBER;
2122 l_return_mesg_text VARCHAR2(1000);
2123 next_disbursement EXCEPTION;
2124 l_term_exists BOOLEAN := FALSE;
2125 l_full_time_amount NUMBER := 0;
2126 l_ft_running_amount NUMBER := 0;
2127 l_pell_schedule_code VARCHAR2(1) := 'X';
2128
2129
2130 -- PL/SQL Table that returns the records
2131 l_pell_tab pell_tab := pell_tab();
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,
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
2146 en.person_id = fa.person_id AND
2147 en.course_cd = pver.course_cd AND
2148 en.version_number = pver.version_number AND
2149 pver.course_type = ptype.course_type AND
2150 UPPER(en.course_attempt_status) = 'COMPLETED' AND
2151 UPPER(ptype.fin_aid_program_type) <> 'PRE-BACHELORS';
2152
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
2162 ant_data.program_cd key_prog,
2163 prog.version_number key_prog_ver
2164 FROM
2165 igf_aw_dp_terms terms,
2166 igf_aw_dp_teach_prds_v teach,
2167 igf_ap_fa_ant_data ant_data,
2168 igs_ps_ver prog
2169 WHERE
2170 terms.adplans_id = cp_plan_id AND
2171 teach.adterms_id = terms.adterms_id AND
2172 ant_data.ld_cal_type = terms.ld_cal_type AND
2173 ant_data.ld_sequence_number = terms.ld_sequence_number AND
2174 ant_data.base_id = cp_base_id AND
2175 ant_data.program_cd = prog.course_cd AND
2176 prog.course_status = 'ACTIVE' AND
2177 ant_data.program_cd IS NOT NULL AND
2178 (terms.ld_cal_type,terms.ld_sequence_number) IN
2179 (SELECT coa.ld_cal_type, coa.ld_sequence_number
2180 FROM igf_aw_coa_itm_terms coa
2181 WHERE coa.base_id = cp_base_id)
2182 ORDER BY
2183 igf_aw_packaging.get_term_start_date(cp_base_id, terms.ld_cal_type, terms.ld_sequence_number) ASC,
2184 prog.version_number DESC;
2185
2186 l_get_ant_key_prog_ver_rec cur_get_ant_key_prog_ver%ROWTYPE;
2187
2188 -- museshad (Build# FA 157 Pell disbursement rounding)
2189 TYPE pell_term_rec IS RECORD(
2190 term_ld_cal_type igs_ca_inst.cal_type%TYPE,
2191 term_ld_seq_num igs_ca_inst.sequence_number%TYPE,
2192 term_amt NUMBER,
2193 tp_count NUMBER
2194 );
2195 TYPE pell_term_tab IS TABLE OF pell_term_rec;
2196 l_pell_term_tab_rec pell_term_tab;
2197
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,
2207 terms.ld_sequence_number,
2208 COUNT(terms.ld_cal_type) tp_count
2209 FROM
2210 igf_aw_dp_terms terms,
2211 igf_aw_dp_teach_prds teach_periods
2212 WHERE
2213 teach_periods.adterms_id = terms.adterms_id AND
2214 terms.adplans_id = cp_plan_id AND
2215 (terms.ld_cal_type,
2216 terms.ld_sequence_number) IN (
2217 SELECT coa.ld_cal_type,
2218 coa.ld_sequence_number
2219 FROM igf_aw_coa_itm_terms coa
2220 WHERE coa.base_id = cp_base_id
2221 )
2222 GROUP BY terms.ld_cal_type, terms.ld_sequence_number;
2223 -- museshad (Build# FA 157 Pell disbursement rounding)
2224
2225 -- Get roundoff_fact
2226 CURSOR c_roundoff_fact(
2227 cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
2228 ) IS
2229 SELECT roundoff_fact
2230 FROM igf_aw_fund_mast_all
2231 WHERE fund_id = cp_fund_id;
2232 l_roundoff_fact igf_aw_fund_mast_all.roundoff_fact%TYPE;
2233
2234 BEGIN
2235 l_pell_amt := 0;
2236 l_ft_pell_amt := 0;
2237 l_return_mesg_text := NULL;
2238
2239 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2240 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2241 'cp_plan_id -> '|| cp_plan_id);
2242 END IF;
2243 l_total_term_weeks := 0;
2244 -- If no plan ID is passed , Not possible to calculate Pell hence return
2245 IF cp_plan_id IS NULL THEN
2246 fnd_message.set_name('IGF','IGF_AW_DIST_CODE_FAIL');
2247 cp_message := fnd_message.get ;
2248 cp_return_status := 'E' ;
2249 return;
2250 ELSE
2251 -- Get the CP_Plan code to be displayed in the Log
2252 OPEN c_dp_details(cp_plan_id);
2253 FETCH c_dp_details INTO l_dp_details_rec;
2254 IF c_dp_details%NOTFOUND THEN
2255 CLOSE c_dp_details;
2256 fnd_message.set_name('IGF','IGF_AW_DIST_CODE_FAIL');
2257 cp_message := fnd_message.get ;
2258 cp_return_status := 'E' ;
2259 return;
2260 END IF;
2261 CLOSE c_dp_details;
2262
2263 END IF;
2264
2265 OPEN c_base(cp_base_id);
2266 FETCH c_base INTO l_base_rec;
2267 CLOSE c_base;
2268
2269 -- Check if student is a Graduate/Professional
2270 OPEN c_acad_hist(cp_base_id);
2271 FETCH c_acad_hist INTO l_acad_hist;
2272
2273 IF (c_acad_hist%FOUND) THEN
2274 l_aid := 0;
2275 fnd_message.set_name('IGF','IGF_AW_NO_PELL_HIGH_DEG');
2276 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
2277 cp_message := fnd_message.get ;
2278
2279 cp_return_status := 'E' ;
2280 CLOSE c_acad_hist;
2281 RETURN;
2282 END IF;
2283 CLOSE c_acad_hist;
2284
2285 -- Check if the student is eligible per the ISIR record.
2286
2287 IF cp_called_from NOT IN ('IGFAW016','IGFGR005') THEN
2288 OPEN c_pell_elig(cp_base_id);
2289 FETCH c_pell_elig INTO l_pell_elig;
2290 IF l_pell_elig.pell_grant_elig_flag = 'N' THEN
2291 l_aid := 0;
2292
2293 fnd_message.set_name('IGF','IGF_AP_NO_PELL_AWARD');
2294 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
2295 cp_message := fnd_message.get ;
2296 cp_return_status := 'E' ;
2297 CLOSE c_pell_elig;
2298 RETURN;
2299 END IF;
2300 CLOSE c_pell_elig;
2301 END IF;
2302
2303 IF cp_called_from NOT IN ('IGFAW016','IGFGR005') THEN
2304 -- Get all the non PRE-BACHELOR program types already completed by the Student from
2305 -- Enrollment's Program attempts table. If the student has already completed
2306 -- any non PRE-BACHELOR program type, then he is not eligible for Pell
2307 OPEN c_get_prog_type(cp_base_id);
2308 FETCH c_get_prog_type INTO l_get_prog_type;
2309
2310 IF (c_get_prog_type%FOUND) THEN
2311 -- Display error message
2312 fnd_message.set_name('IGF','IGF_AW_NO_PELL_HIGH_DEG');
2313 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
2314 cp_message := fnd_message.get ;
2315
2316 -- Log error
2317 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2318 fnd_log.string(fnd_log.level_statement,
2319 'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2320 'Person Number ' ||igf_gr_gen.get_per_num(cp_base_id)|| ' not eligible for Pell because this person has already completed a Bacheolor/Professional program'
2321 );
2322 END IF;
2323 -- Mark return status as Error
2324 cp_return_status := 'E' ;
2325
2326 CLOSE c_get_prog_type;
2327 RETURN;
2328 END IF;
2329 CLOSE c_get_prog_type;
2330 END IF;
2331
2332 -- Start processing Pell award based on the Common Terms.
2333 OPEN c_terms(cp_base_id,
2334 cp_plan_id );
2335 FETCH c_terms INTO l_terms_rec;
2336 CLOSE c_terms;
2337
2338 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2339 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','No terms found for base_id >' || TO_CHAR(cp_base_id) || ' plan_id >' || TO_CHAR(cp_plan_id) || ' = ' || TO_CHAR(NVL(l_terms_rec.total_terms,0)));
2340 END IF;
2341
2342 IF NVL(l_terms_rec.total_terms,0) = 0 THEN
2343
2344 cp_return_status := 'E';
2345 fnd_message.set_name('IGF','IGF_AW_COA_COMMON_TERMS_FAIL');
2346 fnd_message.set_token('PLAN_CD',l_dp_details_rec.awd_dist_plan_cd);
2347 cp_message := fnd_message.get ;
2348 RETURN;
2349 END IF;
2350
2351 -- Get the students key program details
2352 -- Based on these details the Pell Setup record is arrived at
2353 igf_ap_gen_001.get_key_program(cp_base_id => cp_base_id,
2354 cp_course_cd => l_program_cd,
2355 cp_version_number => l_program_version
2356 );
2357
2358 IF l_program_cd IS NULL THEN
2359 -- Actual (Enrollment) key program details not available.
2360 -- Get it from Admissions
2361 get_key_prog_ver_frm_adm(
2362 p_base_id => cp_base_id,
2363 p_key_prog_cd => l_program_cd,
2364 p_key_prog_ver => l_program_version
2365 );
2366
2367 IF l_program_cd IS NULL AND igf_aw_coa_gen.canUseAnticipVal THEN
2368 -- Admissions does not have key program details
2369 -- Get it from FA Anticipated data.
2370 OPEN cur_get_ant_key_prog_ver(cp_base_id, cp_plan_id);
2371 FETCH cur_get_ant_key_prog_ver INTO l_get_ant_key_prog_ver_rec;
2372 CLOSE cur_get_ant_key_prog_ver;
2373
2374 l_program_cd := l_get_ant_key_prog_ver_rec.key_prog;
2375 l_program_version := l_get_ant_key_prog_ver_rec.key_prog_ver;
2376
2377 IF l_program_cd IS NULL THEN
2378 -- FA Anticipated data does not have key program details. Error out
2379 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2380 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Cannot compute key program details.');
2381 END IF;
2382
2383 cp_return_status := 'E';
2384 fnd_message.set_name('IGS', 'IGS_EN_NO_KEY_PRG');
2385 fnd_message.set_token('PERSON', igf_gr_gen.get_per_num(cp_base_id));
2386 cp_message := fnd_message.get;
2387 RETURN;
2388 END IF;
2389 END IF;
2390 END IF;
2391
2392 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2393 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Key Program > Course cd>' || l_program_cd || ' Version >' || TO_CHAR(l_program_version) );
2394 END IF;
2395
2396 l_message := NULL;
2397
2398 -- Get the Pell Setup
2399 get_pell_setup( cp_base_id => cp_base_id,
2400 cp_course_cd => l_program_cd,
2401 cp_version_number => l_program_version,
2402 cp_cal_type => l_base_rec.ci_cal_type,
2403 cp_sequence_number => l_base_rec.ci_sequence_number ,
2404 cp_pell_setup_rec => l_pell_setup_rec ,
2405 cp_message => l_message ,
2406 cp_return_status => l_return_status );
2407
2408 IF l_return_status = 'E' THEN
2409 cp_message := l_message;
2410 cp_return_status := 'E' ;
2411 RETURN;
2412 END IF;
2413
2414 cp_pell_seq_id := l_pell_setup_rec.pell_seq_id;
2415
2416 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2417 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Pell Setup Retrival Succesful for Primary key PELL_SEQ_ID- > ' || TO_CHAR(l_pell_setup_rec.PELL_SEQ_ID));
2418 END IF;
2419
2420 -- Check if the Pell Setup Payment periods are greater than the total number of payment periods
2421 IF l_terms_rec.total_terms > l_pell_setup_rec.payment_periods_num THEN
2422 fnd_message.set_name('IGF','IGF_GR_INVALID_PAY_PERIODS');
2423 fnd_message.set_token('REPORT_PELL_ID', l_pell_setup_rec.rep_pell_id);
2424 fnd_file.put_line(fnd_file.log, fnd_message.get);
2425
2426 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2427 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Pell Setup pymnt prds - > ' || TO_CHAR(l_pell_setup_rec.payment_periods_num) || ' Actual periods ->' || TO_CHAR(l_terms_rec.total_terms));
2428 END IF;
2429 END IF;
2430
2431 -- Get fund attributes to set at Disbursement Level.
2432 OPEN c_fund(cp_fund_id);
2433 FETCH c_fund into l_fund_rec;
2434 IF c_fund%NOTFOUND THEN
2435 fnd_message.set_name('IGF','IGF_AW_NO_SUCH_FUND');
2436 fnd_message.set_token('FUND_ID',TO_CHAR(cp_fund_id));
2437 cp_message := fnd_message.get ;
2438 cp_return_status := 'E' ;
2439 CLOSE c_fund;
2440 RETURN;
2441 END IF;
2442 CLOSE c_fund;
2443
2444
2445 -- Initialize the running Term flag
2446 l_running_term := -1 ;
2447 l_term_cnt := 0;
2448 l_term_amt := 0;
2449 l_disb_num := 0;
2450 cp_aid := NULL;
2451 old_pell_att_type := NULL;
2452 l_ft_running_amount := 0;
2453
2454 /*
2455 Before starting pell calculation, find out what is the Full time pell amount
2456 that the student can get, using his own COA and EFC
2457 */
2458 get_pell_coa_efc(
2459 cp_base_id => cp_base_id,
2460 cp_attendance_type => '1',
2461 cp_pell_setup_rec => l_pell_setup_rec,
2462 cp_coa => l_coa,
2463 cp_efc => l_efc,
2464 cp_pell_schedule_code => l_pell_schedule_code,
2465 cp_message => l_message,
2466 cp_return_status => l_return_status
2467 );
2468
2469 -- Assumed that get_pell_coa_efc() will not return any error.
2470 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2471 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Done with get_pell_coa_efc: l_coa= '||l_coa||
2472 ' l_efc: '||l_efc||' l_pell_schedule_code: '||l_pell_schedule_code);
2473 END IF;
2474
2475 get_pell_matrix_amt(
2476 cp_cal_type => l_base_rec.ci_cal_type,
2477 cp_sequence_num => l_base_rec.ci_sequence_number,
2478 cp_efc => l_efc,
2479 cp_pell_schd => l_pell_schedule_code,
2480 cp_enrl_stat => '1',
2481 cp_pell_coa => l_coa,
2482 cp_pell_alt_exp => l_base_rec.pell_alt_expense,
2483 cp_called_from => 'PELLORIG',
2484 cp_return_status => l_return_status,
2485 cp_message => l_message,
2486 cp_aid => l_full_time_amount
2487 );
2488
2489 -- Assumed that get_pell_matrix_amt() will not return any error.
2490 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2491 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Done with get_pell_matrix_amt: l_full_time_amount= '|| l_full_time_amount);
2492 END IF;
2493
2494 -- Check the if running in DP only Mode or COA/DP common Terms Mode
2495 OPEN c_terms_det (
2496 cp_plan_id,
2497 cp_base_id
2498 );
2499
2500 LOOP -- << c_terms_det Start loop >>
2501 BEGIN -- << Start Block >>
2502 FETCH c_terms_det INTO l_terms_det_rec;
2503 EXIT WHEN c_terms_det%NOTFOUND;
2504
2505 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2506 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Processing Term->' ||
2507 l_terms_det_rec.ld_cal_type || '/' || TO_CHAR(l_terms_det_rec.ld_sequence_number) || ' Teach->' ||
2508 l_terms_det_rec.tp_cal_type || '/' || TO_CHAR(l_terms_det_rec.tp_sequence_number) );
2509 END IF;
2510
2511 IF l_dp_details_rec.dist_plan_method_code = 'E' THEN
2512 l_terms_det_rec.tp_perct_num := 100 / num_disb(cp_plan_id,l_terms_det_rec.ld_cal_type,l_terms_det_rec.ld_sequence_number);
2513 END IF;
2514
2515 IF l_running_term <> l_terms_det_rec.ld_sequence_number THEN
2516 -- Term has changed so Do a Term Level Pell computation .
2517
2518 -- Reset the Term variables
2519 l_running_term := l_terms_det_rec.ld_sequence_number ;
2520 l_term_amt := 0 ;
2521 l_term_cnt := l_term_cnt + 1;
2522
2523 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2524 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Term level call to get_pell_attendance_type ' );
2525 END IF;
2526
2527 -- Get the Attndance Type for the Person
2528 get_pell_attendance_type (
2529 cp_base_id => cp_base_id,
2530 cp_ld_cal_type => l_terms_det_rec.ld_cal_type ,
2531 cp_ld_sequence_number => l_terms_det_rec.ld_sequence_number ,
2532 cp_pell_setup_rec => l_pell_setup_rec ,
2533 cp_attendance_type => l_pell_attend_type ,
2534 cp_message => l_message ,
2535 cp_return_status => l_return_status );
2536
2537 IF (l_return_status='E') AND (l_pell_attend_type <> 'XX' OR l_pell_attend_type IS NULL) THEN
2538 -- IGF_GR_PELL_ATT_NOT_EXIST scenario - FA Attendance type mapping does not exist. Error out and return.
2539 cp_message := l_message ;
2540 cp_return_status := 'E' ;
2541 CLOSE c_terms_det ;
2542
2543 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2544 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','returning back - current term '||l_terms_det_rec.ld_cal_type||'/'||l_terms_det_rec.ld_sequence_number);
2545 END IF;
2546
2547 RETURN;
2548 ELSIF (l_return_status='E') AND l_pell_attend_type = 'XX' THEN
2549 -- IGF_GR_NO_OSS_ATTEND scenario- Not able to derive ACTUAL attendance type for the term.
2550 -- We can still continue by Skipping this disb and moving to next disb in the loop c_terms_det
2551 l_term_cnt := l_term_cnt - 1;
2552 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2553 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug', 'Not able to derive ACTUAL attendance type for the term '
2554 ||l_terms_det_rec.ld_cal_type ||'/'|| l_terms_det_rec.ld_sequence_number ||'. Raising next_disbursement to process next disbursement.');
2555 END IF;
2556
2557 RAISE next_disbursement;
2558 END IF;
2559
2560 -- FACR116
2561 -- here set the flag indicating that if the attendance type is different for the terms
2562 -- If pell_att_flag is FALSE then the pell attendance type is not the same for all the terms.
2563 IF pell_att_flag AND NVL(old_pell_att_type,'*') = l_pell_attend_type THEN
2564 pell_att_flag := TRUE;
2565 ELSE
2566 IF old_pell_att_type IS NOT NULL THEN
2567 pell_att_flag := FALSE;
2568 END IF;
2569 END IF;
2570 old_pell_att_type := l_pell_attend_type;
2571
2572 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2573 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Term level call to get_pell_coa_efc ' );
2574 END IF;
2575
2576 -- Get Pell COA and EFC
2577 get_pell_coa_efc (
2578 cp_base_id => cp_base_id,
2579 cp_attendance_type => l_pell_attend_type ,
2580 cp_pell_setup_rec => l_pell_setup_rec ,
2581 cp_coa => l_coa ,
2582 cp_efc => l_efc ,
2583 cp_pell_schedule_code => l_pell_schedule ,
2584 cp_message => l_message ,
2585 cp_return_status => l_return_status );
2586
2587 IF (l_return_status='E') THEN
2588 cp_message := l_message ;
2589 cp_return_status := 'E' ;
2590 CLOSE c_terms_det ;
2591 RETURN;
2592 ELSE
2593 cp_pell_schedule_code := l_pell_schedule ;
2594 END IF;
2595
2596 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2597 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','**** values passed to matrix efc>'|| l_efc || '/schedule>' || l_pell_schedule || '/coa>' || l_coa || '/alt_exp>'
2598 || l_base_rec.pell_alt_expense || '/attend_type>' || l_pell_attend_type);
2599 END IF;
2600
2601 -- Compute the Annual Pell Amount for the context attendance type
2602 get_pell_matrix_amt(
2603 cp_cal_type => l_base_rec.ci_cal_type,
2604 cp_sequence_num => l_base_rec.ci_sequence_number,
2605 cp_efc => l_efc,
2606 cp_pell_schd => l_pell_schedule,
2607 cp_enrl_stat => l_pell_attend_type,
2608 cp_pell_coa => l_coa,
2609 cp_pell_alt_exp => l_base_rec.pell_alt_expense,
2610 cp_called_from => cp_called_from,
2611 cp_message => l_message,
2612 cp_return_status => l_return_status,
2613 cp_aid => l_aid
2614 ) ;
2615
2616 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2617 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','Term level return amount from get_pell_matrix_amt **************->' || TO_CHAR(l_aid) );
2618 END IF;
2619
2620 IF l_return_status IS NOT NULL AND l_return_status <> 'E' AND l_aid = 0 THEN
2621 l_term_cnt := l_term_cnt - 1;
2622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2623 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','reduced l_term_cnt:'||l_term_cnt);
2624 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','raising next_disbursement for '||l_terms_det_rec.ld_cal_type||'/'||l_terms_det_rec.ld_sequence_number ||' as matrix amt is zero');
2625 END IF;
2626 RAISE next_disbursement;
2627 END IF;
2628
2629 IF (l_return_status='E') THEN
2630 cp_message := l_message ;
2631 cp_return_status := 'E' ;
2632 CLOSE c_terms_det ;
2633 RETURN;
2634 END IF;
2635 IF l_aid = 0 THEN
2636 fnd_message.set_name('IGF','IGF_AW_ZERO_PELL_AMT');
2637 cp_message := fnd_message.get;
2638 cp_return_status := 'E' ;
2639 RETURN;
2640 END IF;
2641 -- Get the Term Level Amount
2642 IF l_pell_setup_rec.payment_method = 3 THEN
2643
2644 l_term_weeks := NULL;
2645 l_actual_weeks := NULL;
2646 cp_return_status := NULL;
2647 cp_message := NULL;
2648
2649 get_pm_3_acad_term_wks(l_terms_det_rec.ld_cal_type,
2650 l_terms_det_rec.ld_sequence_number,
2651 l_program_cd,
2652 l_program_version,
2653 l_term_weeks,
2654 l_actual_weeks,
2655 cp_return_status,
2656 cp_message
2657 );
2658
2659 IF cp_return_status = 'E' THEN
2660 RETURN;
2661 END IF;
2662
2663 -- museshad Build# FA 157
2664 -- Changed ROUNDing to two decimal places
2665 l_term_amt := ROUND(((l_aid * l_term_weeks) / l_actual_weeks), 2);
2666 l_total_term_weeks := l_total_term_weeks + l_term_weeks;
2667
2668 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2669 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2670 'Payment Method = 3 - > l_actual_weeks - > l_term_weeks - > l_aid -> l_term_amt -> '
2671 || l_actual_weeks || ' -> ' || l_term_weeks
2672 || ' -> ' || l_aid
2673 || ' -> ' || l_term_amt);
2674 END IF;
2675
2676 ELSE -- the payment method is not equal to '3'
2677 l_term_amt := ROUND((l_aid/l_pell_setup_rec.payment_periods_num),2) ;
2678 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2679 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug', 'l_aid***payment periods -> ' ||l_aid|| '***' ||l_pell_setup_rec.payment_periods_num);
2680 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2681 'Payment Method is NOT 3 - > l_aid -> l_term_amt -> '
2682 || ' -> ' || l_aid
2683 || ' -> ' || l_term_amt);
2684 END IF;
2685 END IF;
2686
2687 /*
2688 For each term, add the aid amount to running total
2689 */
2690 IF l_ft_running_amount + l_term_amt > l_full_time_amount THEN
2691 l_term_amt := l_full_time_amount - l_ft_running_amount ;
2692 l_ft_running_amount := l_full_time_amount;
2693
2694 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2695 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug', 'Reduced l_term_amt to ' ||l_term_amt|| ' so that it is within the full time Pell amt');
2696 END IF;
2697 ELSE
2698 l_ft_running_amount := l_ft_running_amount + l_term_amt ;
2699 END IF;
2700
2701 END IF; -- Term Calendar Instance has Changed
2702
2703 IF NVL(l_term_amt, 0) > 0 THEN
2704 -- Retrieve the Verification Enforcement Date
2705 l_ofst_rec := NULL;
2706 OPEN c_get_ofst(
2707 l_terms_det_rec.date_offset_cd,
2708 l_terms_det_rec.ld_cal_type,
2709 l_terms_det_rec.ld_sequence_number,
2710 l_fund_rec.disb_verf_da
2711 );
2712 FETCH c_get_ofst INTO l_ofst_rec;
2713 CLOSE c_get_ofst;
2714
2715 IF l_ofst_rec.ofst_derived_val IS NOT NULL THEN
2716 l_verif_enfr_dt := l_ofst_rec.ofst_derived_val;
2717 ELSE
2718 l_verif_enfr_dt := l_ofst_rec.ofst_absolute_val;
2719 END IF;
2720
2721 -- Retrieve the Disbursement Expiration Date
2722 l_ofst_rec := NULL;
2723 OPEN c_get_ofst(
2724 l_terms_det_rec.date_offset_cd,
2725 l_terms_det_rec.ld_cal_type,
2726 l_terms_det_rec.ld_sequence_number,
2727 l_fund_rec.disb_exp_da
2728 );
2729 FETCH c_get_ofst INTO l_ofst_rec;
2730 CLOSE c_get_ofst;
2731
2732 IF l_ofst_rec.ofst_derived_val IS NOT NULL THEN
2733 l_disb_exp_dt := l_ofst_rec.ofst_derived_val;
2734 ELSE
2735 l_disb_exp_dt := l_ofst_rec.ofst_absolute_val;
2736 END IF;
2737
2738 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2739 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',' /PL SQL Table count Before ' ||l_cnt);
2740 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',' Teaching Period Percent: ' ||l_terms_det_rec.tp_perct_num);
2741 END IF;
2742
2743 -- Populate the PL/SQL Table with the Disbursement Details
2744 l_tp_amt := ROUND((l_term_amt * l_terms_det_rec.tp_perct_num/100),2) ;
2745
2746 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2747 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',' l_tp_amt: ' ||l_tp_amt);
2748 END IF;
2749
2750 IF cp_pell_tab IS NULL THEN
2751 cp_pell_tab := pell_tab();
2752 END IF;
2753 l_cnt := cp_pell_tab.COUNT;
2754 l_cnt := NVL(l_cnt,0) + 1 ;
2755 l_disb_num := l_disb_num + 1;
2756
2757
2758 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2759 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','### term_ld>' || l_terms_det_rec.ld_cal_type || '/tp_cal>' ||
2760 l_terms_det_rec.tp_cal_type || '/tp_seq>' || l_terms_det_rec.tp_sequence_number || '/term_amt>' || l_term_amt || '/tp_%>' || l_terms_det_rec.tp_perct_num
2761 || '/tp_amt>' || l_tp_amt ||' /PL SQL Table count After ' ||l_cnt);
2762 END IF;
2763
2764 cp_pell_tab.EXTEND(1);
2765
2766 cp_pell_tab(l_cnt).process_id := NULL;
2767 cp_pell_tab(l_cnt).sl_number := l_disb_num ;
2768 cp_pell_tab(l_cnt).disb_dt := l_terms_det_rec.disb_dt ;
2769 cp_pell_tab(l_cnt).fund_id := cp_fund_id ;
2770 cp_pell_tab(l_cnt).base_id := cp_base_id ;
2771 cp_pell_tab(l_cnt).offered_amt := l_tp_amt ;
2772 cp_pell_tab(l_cnt).term_amt := l_term_amt ; -- museshad (Build FA 157)
2773
2774 IF l_fund_rec.pckg_awd_stat = 'ACCEPTED' THEN
2775 cp_pell_tab(l_cnt).accepted_amt := l_tp_amt ;
2776 ELSE
2777 cp_pell_tab(l_cnt).accepted_amt := NULL ;
2778 END IF;
2779
2780 cp_pell_tab(l_cnt).paid_amt := null;
2781 cp_pell_tab(l_cnt).ld_cal_type := l_terms_det_rec.ld_cal_type ;
2782 cp_pell_tab(l_cnt).ld_sequence_number := l_terms_det_rec.ld_sequence_number;
2783 cp_pell_tab(l_cnt).tp_cal_type := l_terms_det_rec.tp_cal_type;
2784 cp_pell_tab(l_cnt).tp_sequence_number := l_terms_det_rec.tp_sequence_number;
2785 cp_pell_tab(l_cnt).app_trans_num_txt := l_pell_elig.transaction_num;
2786 cp_pell_tab(l_cnt).adplans_id := cp_plan_id;
2787 cp_pell_tab(l_cnt).attendance_type_code := l_terms_det_rec.attendance_type_code;
2788 cp_pell_tab(l_cnt).min_credit_pts := l_terms_det_rec.credit_points_num;
2789 cp_pell_tab(l_cnt).disb_exp_dt := l_disb_exp_dt;
2790 cp_pell_tab(l_cnt).verf_enfr_dt := l_verif_enfr_dt;
2791 cp_pell_tab(l_cnt).show_on_bill := l_fund_rec.show_on_bill;
2792 cp_pell_tab(l_cnt).base_attendance_type_code := l_pell_attend_type ;
2793
2794 cp_aid := NVL(cp_aid,0) + NVL(l_tp_amt,0) ;
2795
2796 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2797 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2798 'l_tp_amt , cp_aid - LOOP ' || l_tp_amt || ' , ' || cp_aid);
2799 END IF;
2800 ELSE
2801 -- l_term_amt is NULL or 0
2802 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2803 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug', 'Ignoring term bcoz l_term_amt is 0');
2804 END IF;
2805 END IF; -- << NVL(l_term_amt, 0) > 0 >>
2806
2807 EXCEPTION
2808 WHEN next_disbursement THEN
2809 l_term_amt := NULL;
2810 l_pell_attend_type := NULL;
2811 WHEN OTHERS THEN
2812 RAISE;
2813 END; -- << End Block >>
2814 END LOOP; -- << c_terms_det End loop >>
2815
2816 CLOSE c_terms_det;
2817
2818 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2819 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
2820 'l_aid , cp_aid , l_acad_wks , l_term_total_weeks '
2821 || l_aid || ' , '|| cp_aid || ' , '
2822 || l_actual_weeks || ' , '|| l_total_term_weeks);
2823 END IF;
2824
2825 -- Check if cp_pell_tab is NOT filled for some reason. If so, error out and return
2826 IF (cp_pell_tab IS NULL) OR (cp_pell_tab IS NOT NULL AND cp_pell_tab.COUNT = 0) THEN
2827 -- cp_pell_tab is not filled, so the Pell awd amount will be 0
2828 fnd_message.set_name('IGF','IGF_AW_ZERO_PELL_AMT');
2829 cp_message := fnd_message.get;
2830 cp_return_status := 'E';
2831 cp_aid := 0;
2832
2833 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2834 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug', 'cp_pell_tab is NOT filled. Cannot proceed further.');
2835 END IF;
2836
2837 RETURN;
2838 END IF;
2839
2840 OPEN c_roundoff_fact(cp_fund_id);
2841 FETCH c_roundoff_fact INTO l_roundoff_fact;
2842 CLOSE c_roundoff_fact;
2843
2844 -- museshad (Round Pell disbursements)
2845 -- Loop for each distinct term
2846 FOR l_term_rec IN cur_get_term_info(
2847 cp_plan_id => cp_plan_id,
2848 cp_base_id => cp_base_id
2849 )
2850 LOOP
2851 l_term_amt := 0;
2852 l_term_exists := FALSE;
2853
2854 -- Loop thru the main PL/SQL table and find the term amount
2855 FOR i in 1..cp_pell_tab.COUNT
2856 LOOP
2857 IF (cp_pell_tab.EXISTS(i) AND
2858 (
2859 ((l_term_rec.ld_cal_type IS NOT NULL) AND (cp_pell_tab(i).ld_cal_type = l_term_rec.ld_cal_type)) AND
2860 ((l_term_rec.ld_sequence_number IS NOT NULL) AND (cp_pell_tab(i).ld_sequence_number = l_term_rec.ld_sequence_number))
2861 )
2862 ) THEN
2863 l_term_amt := cp_pell_tab(i).term_amt;
2864 l_term_exists := TRUE;
2865 /*
2866 Apply fund master round-off factor to term level amount
2867 */
2868 IF l_roundoff_fact = '0.5' THEN
2869 l_term_amt := ROUND(l_term_amt, 2);
2870 ELSIF l_roundoff_fact = '1' THEN
2871 l_term_amt := ROUND(l_term_amt);
2872 END IF;
2873 EXIT;
2874 END IF;
2875 END LOOP;
2876
2877 -- Round all disbursements in the term
2878 l_return_status := 'S';
2879 -- Log
2880 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2881 fnd_log.string(fnd_log.level_statement,
2882 'igf.plsql.igf_gr_pell_calc.calc_pell.debug Full Time Amount Validation',
2883 'Calling round_term_disbursements for the term '||l_term_rec.ld_cal_type||', '||l_term_rec.ld_sequence_number);
2884 END IF;
2885 IF NOT pell_att_flag AND l_term_exists THEN
2886 --round off term amount
2887 round_term_disbursements (
2888 p_pell_tab => cp_pell_tab,
2889 p_fund_id => cp_fund_id,
2890 p_dist_plan_code => l_dp_details_rec.dist_plan_method_code,
2891 p_ld_cal_type => l_term_rec.ld_cal_type,
2892 p_ld_seq_num => l_term_rec.ld_sequence_number,
2893 p_term_amt => l_term_amt,
2894 p_tp_count => l_term_rec.tp_count,
2895 p_pkg_awd_status => l_fund_rec.pckg_awd_stat,
2896 p_return_status => l_return_status
2897 );
2898
2899 -- Check for err in Pell disbursement rounding for the term
2900 IF l_return_status = 'E' THEN
2901 cp_message := NULL;
2902 cp_return_status := 'E';
2903 RETURN;
2904 END IF;
2905 ELSE
2906 --round of entire award amount
2907 /*
2908 Apply fund master round-off factor to award amount
2909 */
2910 IF l_roundoff_fact = '0.5' THEN
2911 cp_aid := ROUND(cp_aid, 2);
2912 ELSIF l_roundoff_fact = '1' THEN
2913 cp_aid := ROUND(cp_aid);
2914 END IF;
2915 round_all_disbursements(
2916 p_pell_tab => cp_pell_tab,
2917 p_fund_id => cp_fund_id,
2918 p_dist_plan_code => l_dp_details_rec.dist_plan_method_code,
2919 p_aid => cp_aid,
2920 p_disb_count => cp_pell_tab.COUNT,
2921 p_pkg_awd_status => l_fund_rec.pckg_awd_stat,
2922 p_return_status => l_return_status
2923 );
2924 END IF;
2925 END LOOP;
2926 -- museshad (Round Pell disbursements)
2927
2928 --
2929 -- FACR116
2930 --
2931 IF (l_pell_setup_rec.payment_method <> 3)
2932 OR (l_pell_setup_rec.payment_method = 3 AND l_total_term_weeks = l_actual_weeks) THEN
2933 -- If the payment periods is equal to the terms enrolled for the student
2934 IF l_pell_setup_rec.payment_periods_num = l_term_cnt THEN
2935 -- if the attendance type is same in all the terms
2936 IF pell_att_flag THEN
2937 IF l_aid <> cp_aid THEN
2938 cp_pell_tab(l_cnt).offered_amt := l_tp_amt + l_aid - cp_aid ;
2939 IF l_fund_rec.pckg_awd_stat = 'ACCEPTED' THEN
2940 cp_pell_tab(l_cnt).accepted_amt := l_tp_amt + l_aid - cp_aid ;
2941 ELSE
2942 cp_pell_tab(l_cnt).accepted_amt := NULL ;
2943 END IF;
2944 cp_aid := l_aid;
2945 END IF;
2946 END IF;
2947 END IF;
2948 END IF;
2949 --
2950 -- Bug 3778277 This validation should happen only if the attendance type is same for all terms
2951 /*IF pell_att_flag AND cp_aid > l_aid THEN
2952 -- Total awarded amount is greater than the limit
2953 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2954 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug','raising IGF_AW_PELL_LMT_EXCEED with cp_aid/l_aid->'||cp_aid||'/'||l_aid);
2955 END IF;
2956 fnd_message.set_name('IGF','IGF_AW_PELL_LMT_EXCEED');
2957 cp_message := fnd_message.get;
2958 cp_return_status := 'E' ;
2959 RETURN;
2960 END IF;*/
2961 -- Check to compare the award amount with the full time pell amount that a student can get.
2962 l_return_status := NULL;
2963 calc_ft_max_pell(cp_base_id => cp_base_id,
2964 cp_cal_type => l_base_rec.ci_cal_type,
2965 cp_sequence_number => l_base_rec.ci_sequence_number,
2966 cp_flag => 'FULL_TIME',
2967 cp_aid => l_pell_amt,
2968 cp_ft_aid => l_ft_pell_amt,
2969 cp_return_status => l_return_status,
2970 cp_message => l_return_mesg_text
2971 );
2972
2973 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2974 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug Full Time Amount Validation',
2975 'l_ft_pell_amt , cp_aid , l_return_status , l_return_mesg_text '
2976 || l_ft_pell_amt || ' , '|| cp_aid || ' , '
2977 || l_return_status || ' , '|| l_return_mesg_text);
2978 END IF;
2979 IF (NVL(l_return_status,'*') = 'E') THEN
2980 cp_message := l_return_mesg_text;
2981 cp_return_status := 'E' ;
2982 RETURN;
2983 ELSE
2984 IF cp_aid > l_ft_pell_amt THEN
2985 fnd_message.set_name('IGF','IGF_AW_PELL_LMT_EXCEED');
2986 cp_message := fnd_message.get;
2987 cp_return_status := 'E' ;
2988 RETURN;
2989 END IF;
2990 END IF;
2991
2992 /* museshad (Build FA 157)
2993 Recalculate cp_aid after rounding */
2994 cp_aid := 0;
2995 FOR i in 1..cp_pell_tab.COUNT
2996 LOOP
2997 IF (cp_pell_tab.EXISTS(i)) THEN
2998 cp_aid := cp_aid + NVL(cp_pell_tab(i).offered_amt, 0);
2999 END IF;
3000 END LOOP;
3001
3002 cp_return_status := 'S';
3003
3004
3005 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3006 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_pell.debug',
3007 'Aid Amount ' || cp_aid);
3008 END IF;
3009
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3013 fnd_message.set_token('NAME','igf_gr_pell_calc.calc_pell '||SQLERRM);
3014 igs_ge_msg_stack.add;
3015 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3016 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.calc_pell.exception','sql error message: '||SQLERRM);
3017 END IF;
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 /*
3027 || Created By : CDCRUZ
3028 || Created On : 19-NOV-2003
3029 || Purpose : Procedure to calculate the Eligibilty Status of the Student
3030 || Known limitations, enhancements or remarks :
3031 || This wrapper takes in parameters
3032 || of Base id
3033 || and a return status of S/E => Success/Error
3034 || Change History :
3035 || Who When What
3036 || (reverse chronological order - newest change first)
3037 || museshad 01-Jun-2005 Build# FA157 - Bug# 4382371.
3038 || Added another check to determine Pell eligibility.
3039 || Check the completed programs in the Student's
3040 || Program Attempts history (apart from Admission Academic
3041 || History which is already there) and see if there are
3042 || any non 'PRE-BACHELORS' programs. If so,
3043 || mark as ineligible.
3044 || Modified the cursor 'c_acad_hist' to avoid unnecessary
3045 || looping
3046 || ugummall 17-DEC-2003 BUG# 3252832 FA-131 Cod updates
3047 || when c_pell_elig cursor not found used a message.
3048 || CDCRUZ 19-NOV-2003 BUG# 3252832 FA-131 Cod updates
3049 */
3050 CURSOR c_acad_hist(
3051 l_base_id NUMBER
3052 ) IS
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
3062 AND dc.program_type = ptype.course_type
3063 AND UPPER(ptype.fin_aid_program_type) IN ('BACHELORS','PROFESSIONAL');
3064
3065 l_acad_hist c_acad_hist%ROWTYPE;
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' ;
3075
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,
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
3090 en.person_id = fa.person_id AND
3091 en.course_cd = pver.course_cd AND
3092 en.version_number = pver.version_number AND
3093 pver.course_type = ptype.course_type AND
3094 UPPER(en.course_attempt_status) = 'COMPLETED' AND
3095 UPPER(ptype.fin_aid_program_type) <> 'PRE-BACHELORS';
3096
3097 l_get_prog_type c_get_prog_type%ROWTYPE;
3098
3099 BEGIN
3100
3101 cp_return_status := 'S' ;
3102
3103 OPEN c_acad_hist(cp_base_id);
3104 FETCH c_acad_hist INTO l_acad_hist;
3105
3106 IF (c_acad_hist%FOUND) THEN
3107 -- Not eligible for Pell
3108 fnd_message.set_name('IGF','IGF_AW_NO_PELL_HIGH_DEG');
3109 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
3110 igs_ge_msg_stack.add;
3111
3112 cp_return_status := 'E';
3113 CLOSE c_acad_hist;
3114 RETURN;
3115 END IF;
3116 CLOSE c_acad_hist;
3117
3118 OPEN c_pell_elig(cp_base_id);
3119 FETCH c_pell_elig INTO l_pell_elig;
3120 IF (c_pell_elig%NOTFOUND) THEN
3121 fnd_message.set_name('IGF','IGF_AP_ACT_ISIR_NOT_FOUND');
3122 igs_ge_msg_stack.add;
3123 cp_return_status := 'E' ;
3124 ELSE
3125 IF NVL(l_pell_elig.pell_grant_elig_flag,'N') = 'N' THEN
3126 fnd_message.set_name('IGF','IGF_AP_NO_PELL_AWARD');
3127 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
3128 igs_ge_msg_stack.add;
3129 cp_return_status := 'E' ;
3130 CLOSE c_pell_elig;
3131 RETURN;
3132 END IF;
3133 END IF;
3134 CLOSE c_pell_elig;
3135
3136 -- museshad Build# FA157 - Bug# 4382371
3137 -- Get all non PRE-BACHELOR Program types completed by the Student
3138 -- If the student has already completed any non PRE-BACHELOR program
3139 -- then he is not eligible for Pell
3140 OPEN c_get_prog_type(cp_base_id);
3141 FETCH c_get_prog_type INTO l_get_prog_type;
3142
3143 IF (c_get_prog_type%FOUND) THEN
3144 -- Not eligible for Pell
3145 fnd_message.set_name('IGF','IGF_AW_NO_PELL_HIGH_DEG');
3146 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(cp_base_id));
3147 igs_ge_msg_stack.add;
3148 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3149 fnd_log.string(fnd_log.level_statement,
3150 'igf.plsql.igf_gr_pell_calc.pell_elig.debug',
3151 'Person Number ' ||igf_gr_gen.get_per_num(cp_base_id)|| ' not eligible for Pell because this person has already completed a Bacheolor/Professional program'
3152 );
3153 END IF;
3154
3155 cp_return_status := 'E' ;
3156 CLOSE c_get_prog_type;
3157 RETURN;
3158 END IF;
3159 CLOSE c_get_prog_type;
3160
3161 EXCEPTION
3162 WHEN OTHERS THEN
3163 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3164 fnd_message.set_token('NAME','igf_gr_pell_calc.pell_elig '||SQLERRM);
3165 igs_ge_msg_stack.add;
3166 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3167 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.pell_elig.exception','sql error message: '||SQLERRM);
3168 END IF;
3169 app_exception.raise_exception;
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,
3179 cp_return_status IN OUT NOCOPY VARCHAR2,
3180 cp_message IN OUT NOCOPY VARCHAR2,
3181 cp_called_from IN VARCHAR2,
3182 cp_pell_schedule_code OUT NOCOPY VARCHAR2
3183 ) IS
3184 /*
3185 || Created By : CDCRUZ
3186 || Created On : 19-NOV-2003
3187 || Purpose : Procedure to calculate Pell amount for a given term
3188 || Known limitations, enhancements or remarks :
3189 || This wrapper takes in parameters
3190 || of Base id / Attendance Type/ Term Details
3191 || returns the Term Amount
3192 || and a return status of S/E => Success/Error
3193 || Change History :
3194 || Who When What
3195 || (reverse chronological order - newest change first)
3196 || museshad 14-Sep-2005 Build FA 157. Implemented term amount rounding.
3197 || bkkumar 01-Apr-04 Bug# 3409969 Added the logic to calculate the pell
3198 || award amount when the pell_formula = 3 , to derive the
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,
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
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
3222 SELECT
3223 ant_data.program_cd key_prog,
3224 prog.version_number key_prog_ver
3225 FROM
3226 igf_ap_fa_ant_data ant_data,
3227 igs_ps_ver prog
3228 WHERE
3229 ant_data.ld_cal_type = cp_ld_cal_type AND
3230 ant_data.ld_sequence_number = cp_ld_sequence_number AND
3231 ant_data.base_id = cp_base_id AND
3232 ant_data.program_cd = prog.course_cd AND
3233 prog.course_status = 'ACTIVE' AND
3234 ant_data.program_cd IS NOT NULL AND
3235 ROWNUM = 1
3236 ORDER BY prog.version_number DESC;
3237
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,
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
3252 UPPER(fmast.fund_code) = 'PELL';
3253
3254 l_awd_round_fact_rec cur_get_awd_round_fact%ROWTYPE;
3255
3256 l_pell_setup_rec igf_gr_pell_setup_all%ROWTYPE;
3257 l_pell_schedule VARCHAR2(30);
3258 l_message fnd_new_messages.message_text%TYPE;
3259 l_return_status VARCHAR2(30);
3260
3261 l_coa NUMBER;
3262 l_efc NUMBER;
3263 l_aid NUMBER;
3264 l_term_amt NUMBER;
3265 l_tp_amt NUMBER;
3266 l_actual_weeks NUMBER;
3267 l_program_cd igs_en_stdnt_ps_att.course_cd%TYPE;
3268 l_program_version igs_en_stdnt_ps_att.version_number%TYPE;
3269 l_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
3270 l_term_weeks NUMBER;
3271
3272
3273 BEGIN
3274
3275 OPEN c_base(cp_base_id);
3276 FETCH c_base INTO l_base_rec;
3277 CLOSE c_base;
3278
3279 -- Get the students OSS Details
3280 -- get the key program from the term or spa table of enrollments.
3281 igf_ap_gen_001.get_key_program(cp_base_id => cp_base_id,
3282 cp_course_cd => l_program_cd,
3283 cp_version_number => l_program_version
3284 );
3285
3286 IF l_program_cd IS NULL THEN
3287 -- Actual (Enrollment) key program details not available.
3288 -- Get it from Admissions
3289 get_key_prog_ver_frm_adm(
3290 p_base_id => cp_base_id,
3291 p_key_prog_cd => l_program_cd,
3292 p_key_prog_ver => l_program_version
3293 );
3294
3295 IF l_program_cd IS NULL AND igf_aw_coa_gen.canUseAnticipVal THEN
3296 -- Admissions does not have key program details
3297 -- Get it from FA Anticipated data.
3298 OPEN cur_get_ant_key_prog_ver(cp_base_id, cp_ld_cal_type, cp_ld_sequence_number);
3299 FETCH cur_get_ant_key_prog_ver INTO l_get_ant_key_prog_ver_rec;
3300 CLOSE cur_get_ant_key_prog_ver;
3301
3302 l_program_cd := l_get_ant_key_prog_ver_rec.key_prog;
3303 l_program_version := l_get_ant_key_prog_ver_rec.key_prog_ver;
3304
3305 IF l_program_cd IS NULL THEN
3306 -- FA Anticipated data does not have key program details. Error out
3307 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3308 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_term_pell.debug','Cannot compute key program details.');
3309 END IF;
3310
3311 cp_return_status := 'E' ;
3312 fnd_message.set_name('IGS', 'IGS_EN_NO_KEY_PRG');
3313 fnd_message.set_token('PERSON', igf_gr_gen.get_per_num(cp_base_id));
3314 cp_message := fnd_message.get;
3315 RETURN;
3316 END IF;
3317 END IF;
3318 END IF;
3319
3320 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3321 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_term_pell.debug','Key Program > Course cd>' || l_program_cd || ' Version >' || TO_CHAR(l_program_version) );
3322 END IF;
3323
3324 l_message := NULL;
3325
3326 -- Get the Pell Setup
3327 get_pell_setup( cp_base_id => cp_base_id,
3328 cp_course_cd => l_program_cd,
3329 cp_version_number => l_program_version,
3330 cp_cal_type => l_base_rec.ci_cal_type,
3331 cp_sequence_number => l_base_rec.ci_sequence_number ,
3332 cp_pell_setup_rec => l_pell_setup_rec ,
3333 cp_message => l_message ,
3334 cp_return_status => l_return_status );
3335
3336 IF l_return_status = 'E' THEN
3337 cp_message := l_message;
3338 cp_return_status := 'E' ;
3339 RETURN;
3340 END IF;
3341
3342 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3343 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_term_pell.debug','Pell Setup Retrival Succesful for Primary key PELL_SEQ_ID- > ' || TO_CHAR(l_pell_setup_rec.PELL_SEQ_ID));
3344 END IF;
3345
3346
3347 -- Get Pell COA and EFC
3348 get_pell_coa_efc (
3349 cp_base_id => cp_base_id,
3350 cp_attendance_type => cp_attendance_type,
3351 cp_pell_setup_rec => l_pell_setup_rec ,
3352 cp_coa => l_coa ,
3353 cp_efc => l_efc ,
3354 cp_pell_schedule_code => l_pell_schedule ,
3355 cp_message => l_message ,
3356 cp_return_status => l_return_status );
3357
3358
3359 IF (l_return_status='E') THEN
3360 cp_message := l_message ;
3361 cp_return_status := 'E' ;
3362 RETURN;
3363 END IF;
3364 l_coa := NVL(l_coa,0);
3365
3366 cp_pell_schedule_code := l_pell_schedule ;
3367
3368 -- Compute the Annual Pell Amount for the context attendance type
3369 get_pell_matrix_amt(
3370 cp_cal_type => l_base_rec.ci_cal_type,
3371 cp_sequence_num => l_base_rec.ci_sequence_number,
3372 cp_efc => l_efc,
3373 cp_pell_schd => l_pell_schedule,
3374 cp_enrl_stat => cp_attendance_type,
3375 cp_pell_coa => l_coa,
3376 cp_pell_alt_exp => l_base_rec.pell_alt_expense,
3377 cp_called_from => cp_called_from,
3378 cp_message => l_message,
3379 cp_return_status => l_return_status,
3380 cp_aid => l_aid
3381 ) ;
3382
3383 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3384 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_term_pell.debug','Term level return amount from get_pell_matrix_amt **************->' || TO_CHAR(l_aid) );
3385 END IF;
3386
3387 IF (l_return_status='E') THEN
3388 cp_message := l_message ;
3389 cp_return_status := 'E' ;
3390 RETURN;
3391 END IF;
3392 IF l_aid = 0 THEN
3393 fnd_message.set_name('IGF','IGF_AW_ZERO_TERM_PELL_AMT');
3394 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(cp_ld_cal_type,cp_ld_sequence_number));
3395 cp_message := fnd_message.get;
3396 cp_return_status := 'E' ;
3397 RETURN;
3398 END IF;
3399 -- Get the Term Level Amount
3400 IF l_pell_setup_rec.payment_method = 3 THEN
3401
3402 l_term_weeks := NULL;
3403 l_actual_weeks := NULL;
3404 cp_return_status := NULL;
3405 cp_message := NULL;
3406 get_pm_3_acad_term_wks(cp_ld_cal_type,
3407 cp_ld_sequence_number,
3408 l_program_cd,
3409 l_program_version,
3410 l_term_weeks,
3411 l_actual_weeks,
3412 cp_return_status,
3413 cp_message
3414 );
3415
3416 IF cp_return_status = 'E' THEN
3417 RETURN;
3418 END IF;
3419
3420 l_term_amt := ROUND((l_aid * l_term_weeks) / l_actual_weeks);
3421
3422 ELSE -- if the payment method is not '3'
3423 l_term_amt := ROUND((l_aid/l_pell_setup_rec.payment_periods_num),2) ;
3424 END IF;
3425
3426 IF UPPER(cp_called_from) = 'IGFGR005' THEN
3427 /* Round the term amount based on the award rounding factor setup in Fund Manager */
3428 OPEN cur_get_awd_round_fact(cp_base_id => cp_base_id);
3429 FETCH cur_get_awd_round_fact INTO l_awd_round_fact_rec;
3430
3431 IF (cur_get_awd_round_fact%FOUND) AND (l_awd_round_fact_rec.roundoff_fact IS NOT NULL) THEN
3432
3433 IF l_awd_round_fact_rec.roundoff_fact = '0.5' THEN
3434 l_term_amt := ROUND(l_term_amt, 2);
3435
3436 ELSIF l_awd_round_fact_rec.roundoff_fact = '1' THEN
3437 l_term_amt := ROUND(l_term_amt);
3438 END IF;
3439
3440 END IF;
3441 CLOSE cur_get_awd_round_fact;
3442 END IF;
3443 cp_term_aid := l_term_amt;
3444 cp_return_status := 'S';
3445
3446 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3447 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_term_pell.debug','cp_term_aid ' || cp_term_aid );
3448 END IF;
3449
3450 EXCEPTION
3451 WHEN OTHERS THEN
3452 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3453 fnd_message.set_token('NAME','igf_gr_pell_calc.calc_term_pell '||SQLERRM);
3454 igs_ge_msg_stack.add;
3455 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3456 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.calc_term_pell.exception','sql error message: '||SQLERRM);
3457 END IF;
3458 app_exception.raise_exception;
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,
3468 cp_ft_aid IN OUT NOCOPY NUMBER,
3469 cp_return_status IN OUT NOCOPY VARCHAR2,
3470 cp_message IN OUT NOCOPY VARCHAR2
3471 )
3472 IS
3473 ------------------------------------------------------------------
3474 --Created by : cdcruz, Oracle India
3475 --Date created: 01-Dec-2003
3476 --
3477 --Purpose:
3478 -- Note : value of the parameter cp_flag can have 2 values
3479 -- FULL_TIME -> Full Time - Calculate using Full-Time Attendance Type / Payment ISIR's EFC / 99999 COA
3480 -- MAX_PELL -> Max Pell - Calculate using Full-Time Attendance Type / 0 EFC / 99999 COA
3481 -- ACTUAL_PELL -> Actual amount
3482 --Known limitations/enhancements and/or remarks:
3483 --
3484 --Change History:
3485 --Who When What
3486 -- rasahoo 10-Mar-2004 Bug # 3491025 While Run mode (cp_flag) is Full time, the Pell COA will be calculated
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
3496 igf_gr_rfms rfms
3497 WHERE
3498 rfms.base_id = l_base_id;
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
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
3517 FROM
3518 igf_aw_awd_ld_cal_v awd_year_terms,
3519 igf_ap_fa_ant_data ant_data,
3520 igs_ps_ver prog
3521 WHERE
3522 ant_data.ld_cal_type = awd_year_terms.ld_cal_type AND
3523 ant_data.ld_sequence_number = awd_year_terms.ld_sequence_number AND
3524 ant_data.base_id = cp_base_id and
3525 ant_data.program_cd = prog.course_cd AND
3526 prog.course_status = 'ACTIVE' AND
3527 ant_data.program_cd IS NOT NULL
3528 ORDER BY
3529 igf_aw_packaging.get_term_start_date(cp_base_id, awd_year_terms.ld_cal_type, awd_year_terms.ld_sequence_number) ASC,
3530 prog.version_number DESC;
3531
3532 l_get_ant_key_prog_ver_rec cur_get_ant_key_prog_ver%ROWTYPE;
3533
3534 l_coa_rec c_coa%ROWTYPE;
3535 l_reg_efc NUMBER;
3536 l_att_type igs_en_stdnt_ps_att.attendance_type%TYPE;
3537 l_efc NUMBER;
3538 l_coa NUMBER;
3539 l_ft_coa NUMBER;
3540 l_ft_aid NUMBER;
3541 l_program_cd igs_en_stdnt_ps_att.course_cd%TYPE;
3542 l_program_version igs_en_stdnt_ps_att.version_number%TYPE;
3543 l_pell_setup_rec igf_gr_pell_setup_all%ROWTYPE;
3544 l_ft_efc NUMBER;
3545 l_pell_schedule_code VARCHAR2(30);
3546
3547
3548 BEGIN
3549
3550 l_att_type := '1' ;
3551 l_coa := 99999 ;
3552
3553 IF cp_flag IN ('FULL_TIME','ACTUAL_PELL') THEN
3554
3555 --- Get Pell EFC
3556 igf_aw_packng_subfns.get_fed_efc(
3557 l_base_id => cp_base_id,
3558 l_awd_prd_code => NULL,
3559 l_efc_f => l_reg_efc,
3560 l_pell_efc => l_efc,
3561 l_efc_ay => l_reg_efc
3562 );
3563
3564
3565 ELSIF cp_flag = 'MAX_PELL' THEN
3566 -- Originate with Max Pell Award
3567 l_efc := 0 ;
3568 END IF;
3569
3570 IF cp_flag = 'MAX_PELL' THEN
3571
3572 get_pell_matrix_amt(
3573 cp_cal_type => cp_cal_type,
3574 cp_sequence_num => cp_sequence_number,
3575 cp_efc => l_efc,
3576 cp_pell_schd => 'R',
3577 cp_enrl_stat => l_att_type,
3578 cp_pell_coa => l_coa,
3579 cp_pell_alt_exp => 0,
3580 cp_called_from => 'PELLORIG',
3581 cp_message => cp_message,
3582 cp_return_status => cp_return_status,
3583 cp_aid => cp_aid
3584 ) ;
3585
3586 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3587 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_ft_max_pell.debug','Term level return amount from get_pell_matrix_amt **************->' || TO_CHAR(cp_aid) );
3588 END IF;
3589 IF (cp_return_status='E') THEN
3590 RETURN;
3591 END IF;
3592
3593 ELSIF cp_flag = 'FULL_TIME' THEN -- Get the Full time Pell.
3594 -- Get the students key program details
3595 -- Based on these details the Pell Setup record is arrived at
3596 igf_ap_gen_001.get_key_program(cp_base_id => cp_base_id,
3597 cp_course_cd => l_program_cd,
3598 cp_version_number => l_program_version
3599 );
3600
3601 IF l_program_cd IS NULL THEN
3602 -- Actual (Enrollment) key program details not available.
3603 -- Get it from Admissions
3604 get_key_prog_ver_frm_adm(
3605 p_base_id => cp_base_id,
3606 p_key_prog_cd => l_program_cd,
3607 p_key_prog_ver => l_program_version
3608 );
3609
3610 IF l_program_cd IS NULL AND igf_aw_coa_gen.canUseAnticipVal THEN
3611 -- Admissions does not have key program details
3612 -- Get it from FA Anticipated data.
3613 OPEN cur_get_ant_key_prog_ver(cp_base_id);
3614 FETCH cur_get_ant_key_prog_ver INTO l_get_ant_key_prog_ver_rec;
3615 CLOSE cur_get_ant_key_prog_ver;
3616
3617 l_program_cd := l_get_ant_key_prog_ver_rec.key_prog;
3618 l_program_version := l_get_ant_key_prog_ver_rec.key_prog_ver;
3619
3620 IF l_program_cd IS NULL THEN
3621 -- FA Anticipated data does not have key program details. Error out
3622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3623 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_ft_max_pell.debug','Cannot compute key program details');
3624 END IF;
3625
3626 cp_return_status := 'E' ;
3627 fnd_message.set_name('IGS', 'IGS_EN_NO_KEY_PRG');
3628 fnd_message.set_token('PERSON', igf_gr_gen.get_per_num(cp_base_id));
3629 cp_message := fnd_message.get;
3630 RETURN;
3631 END IF;
3632 END IF;
3633 END IF;
3634
3635 -- Get the Pell Setup
3636 get_pell_setup( cp_base_id => cp_base_id,
3637 cp_course_cd => l_program_cd,
3638 cp_version_number => l_program_version,
3639 cp_cal_type => cp_cal_type,
3640 cp_sequence_number => cp_sequence_number,
3641 cp_pell_setup_rec => l_pell_setup_rec ,
3642 cp_message => cp_message ,
3643 cp_return_status => cp_return_status );
3644 -- Get Pell COA
3645 get_pell_coa_efc( cp_base_id => cp_base_id,
3646 cp_attendance_type => '1',
3647 cp_pell_setup_rec => l_pell_setup_rec,
3648 cp_coa => l_ft_coa ,
3649 cp_efc => l_ft_efc,
3650 cp_pell_schedule_code => l_pell_schedule_code,
3651 cp_message => cp_message,
3652 cp_return_status => cp_return_status
3653 ) ;
3654 -- Get pell amount from Pell matrix
3655 get_pell_matrix_amt(
3656 cp_cal_type => cp_cal_type,
3657 cp_sequence_num => cp_sequence_number,
3658 cp_efc => l_ft_efc,
3659 cp_pell_schd => l_pell_schedule_code , --'R',
3660 cp_enrl_stat => l_att_type,
3661 cp_pell_coa => l_ft_coa,
3662 cp_pell_alt_exp => 0,
3663 cp_called_from => 'PELLORIG',
3664 cp_message => cp_message,
3665 cp_return_status => cp_return_status,
3666 cp_aid => cp_aid
3667 ) ;
3668
3669 ELSE
3670 -- Actual Attendance Type
3671 -- This will be called only from Pell Originations process
3672 -- Hence RFMS record has to exist
3673
3674 OPEN c_rfms(cp_base_id);
3675 FETCH c_rfms INTO l_rfms_rec;
3676 IF c_rfms%NOTFOUND THEN
3677 CLOSE c_rfms;
3678 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
3679 cp_message := fnd_message.get;
3680 cp_return_status := 'E' ;
3681 RETURN;
3682 END IF;
3683
3684 cp_aid := ROUND(l_rfms_rec.pell_amount,2) ;
3685
3686 END IF;
3687
3688 IF cp_flag in ('FULL_TIME','MAX_PELL') THEN
3689
3690 cp_ft_aid := ROUND(cp_aid,2) ;
3691
3692 ELSE
3693 -- Compute the Full Time Amount for Actual EFC/COA
3694
3695 -- Fetch the Full time COA for the student
3696 OPEN c_coa(cp_base_id);
3697 FETCH c_coa INTO l_coa_rec;
3698 CLOSE c_coa;
3699
3700 -- No check for COA%notfound because per new rule
3701 -- You can create pell even if he has zero COA.
3702
3703 get_pell_matrix_amt(
3704 cp_cal_type => cp_cal_type,
3705 cp_sequence_num => cp_sequence_number,
3706 cp_efc => l_efc,
3707 cp_pell_schd => 'R',
3708 cp_enrl_stat => '1',
3709 cp_pell_coa => NVL(l_coa_rec.pell_coa,0),
3710 cp_pell_alt_exp => 0,
3711 cp_called_from => 'PELLORIG',
3712 cp_message => cp_message,
3713 cp_return_status => cp_return_status,
3714 cp_aid => l_ft_aid
3715 ) ;
3716
3717 cp_ft_aid := ROUND(l_ft_aid,2) ;
3718
3719 END IF;
3720
3721 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3722 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell_calc.calc_ft_max_pell.debug','cp_ft_aid ' || cp_ft_aid );
3723 END IF;
3724
3725 EXCEPTION
3726 WHEN OTHERS THEN
3727 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3728 fnd_message.set_token('NAME','igf_gr_pell_calc.calc_ft_max_pell '||SQLERRM);
3729 igs_ge_msg_stack.add;
3730 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3731 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_pell_calc.calc_ft_max_pell.exception','sql error message: '||SQLERRM);
3732 END IF;
3733 app_exception.raise_exception;
3734
3735
3736 END calc_ft_max_pell;
3737
3738 END igf_gr_pell_calc;