[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_GEN_003
Source
1 PACKAGE BODY igf_aw_gen_003 AS
2 /* $Header: IGFAW12B.pls 120.23 2006/08/04 07:40:11 veramach ship $ */
3
4
5 FUNCTION get_plan_disb_count(p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
6 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
7 ) RETURN NUMBER IS
8 --
9 ------------------------------------------------------------------
10 -- Created by :
11 -- Date created:
12 --
13 -- Purpose:
14 -- Insert disbursement records
15 --
16 -- Known limitations/enhancements and/or remarks:
17 --
18 -- Change History:
19 -------------------------------------------------------------------
20 -- Who When What
21 -------------------------------------------------------------------
22 -- veramach 12-Oct-2004 FA 152 - added p_awd_prd_code in the signature
23 -------------------------------------------------------------------
24 --
25 CURSOR cur_check_terms IS
26 SELECT COUNT(*) common_terms
27 FROM igf_aw_dp_terms terms,
28 igf_aw_dp_teach_prds teach_periods
29 WHERE terms.adplans_id = p_adplans_id
30 AND terms.adterms_id = teach_periods.adterms_id;
31 ln_result NUMBER;
32
33 CURSOR cur_check_terms_awd IS
34 SELECT COUNT(*) common_terms
35 FROM igf_aw_awd_dist_plans adplans,
36 igf_aw_dp_terms terms,
37 igf_aw_dp_teach_prds teach_periods,
38 igf_aw_awd_prd_term aprd
39 WHERE terms.adplans_id = p_adplans_id
40 AND terms.adterms_id = teach_periods.adterms_id
41 AND terms.ld_cal_type = aprd.ld_cal_type
42 AND terms.ld_sequence_number = terms.ld_sequence_number
43 AND aprd.award_prd_cd = p_awd_prd_code
44 AND adplans.adplans_id = terms.adplans_id
45 AND adplans.cal_type = aprd.ci_cal_type
46 AND adplans.sequence_number = aprd.ci_sequence_number;
47
48 BEGIN
49
50 ln_result := 0;
51 IF p_awd_prd_code IS NULL THEN
52 OPEN cur_check_terms;
53 FETCH cur_check_terms INTO ln_result;
54 CLOSE cur_check_terms;
55 ELSE
56 OPEN cur_check_terms_awd;
57 FETCH cur_check_terms_awd INTO ln_result;
58 CLOSE cur_check_terms_awd;
59 END IF;
60 ln_result := NVL(ln_result,0);
61
62 RETURN ln_result;
63
64 END get_plan_disb_count;
65
66 FUNCTION get_fed_fund_code(
67 p_fund_id NUMBER
68 ) RETURN VARCHAR2
69 IS
70 ------------------------------------------------------------------
71 -- Created by : museshad
72 -- Date created: 12-Sep-2005
73 --
74 -- Purpose: Returns fed_fund_code for the passed fund_id
75 -- Insert disbursement records
76 --
77 -- Known limitations/enhancements and/or remarks:
78 --
79 -- Change History:
80 -------------------------------------------------------------------
81 -- Who When What
82
83 CURSOR cur_get_fund (p_fund_id NUMBER)
84 IS
85 SELECT fcat.fed_fund_code
86 FROM igf_aw_fund_cat fcat,
87 igf_aw_fund_mast fmast
88 WHERE fcat.fund_code = fmast.fund_code
89 AND fmast.fund_id = p_fund_id;
90
91 get_fund_rec cur_get_fund%ROWTYPE;
92
93 BEGIN
94 OPEN cur_get_fund(p_fund_id);
95 FETCH cur_get_fund INTO get_fund_rec;
96 CLOSE cur_get_fund;
97
98 RETURN get_fund_rec.fed_fund_code;
99
100 END get_fed_fund_code;
101
102 FUNCTION isRepackaging(p_award_id IN igf_aw_award_all.award_id%TYPE)
103 RETURN BOOLEAN
104 IS
105 ------------------------------------------------------------------
106 -- Created by : museshad
107 -- Date created: 26-Sep-2005
108 --
109 -- Purpose: Returns FALSE if the award is being created newly (Packaging)
110 -- Returns TRUE if an existing award is being Repackaged
111 --
112 -- Known limitations/enhancements and/or remarks:
113 --
114 -- Change History:
115 -------------------------------------------------------------------
116 -- Who When What
117 ------------------------------------------------------------------
118 CURSOR c_chk_repkg(cp_award_id IN igf_aw_award_all.award_id%TYPE)
119 IS
120 SELECT 'X'
121 FROM igf_aw_awd_disb_all
122 WHERE award_id = cp_award_id;
123 l_repkg_rec c_chk_repkg%ROWTYPE;
124
125 BEGIN
126 OPEN c_chk_repkg(cp_award_id => p_award_id);
127 FETCH c_chk_repkg INTO l_repkg_rec;
128
129 IF (c_chk_repkg%FOUND) THEN
130 CLOSE c_chk_repkg;
131 RETURN TRUE;
132 ELSE
133 CLOSE c_chk_repkg;
134 RETURN FALSE;
135 END IF;
136 END isRepackaging;
137
138 PROCEDURE cancel_extra_disb (
139 p_award_id IN igf_aw_award.award_id%TYPE,
140 p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE
141 )
142 IS
143 /*
144 || Created By : museshad
145 || Created On : 26-Sep-2005
146 || Purpose : Cancels those disbursements in the award that exceed the
147 || disb num passed as parameter
148 ||
149 || Known limitations, enhancements or remarks :
150 ||
151 || Change History :
152 || Who When What
153 */
154
155 -- Get all disbursements more than cp_disb_num in the award
156 CURSOR c_disb_cancel(
157 cp_award_id igf_aw_award_all.award_id%TYPE,
158 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
159 )
160 IS
161 SELECT *
162 FROM igf_aw_awd_disb
163 WHERE award_id = cp_award_id AND
164 trans_type <> 'C' AND
165 disb_num > cp_disb_num;
166
167 BEGIN
168
169 FOR disb_cancel_rec IN c_disb_cancel(cp_award_id => p_award_id,
170 cp_disb_num => p_disb_num)
171 LOOP
172
173 -- Log
174 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
175 fnd_log.string(fnd_log.level_statement,
176 'igf.plsql.igf_aw_gen_003.cancel_extra_disb',
177 'Cancelling disb num ' ||disb_cancel_rec.disb_num|| ' in award_id:' ||p_award_id);
178 END IF;
179
180 -- cancel the disbursement
181 igf_aw_awd_disb_pkg.update_row(
182 x_rowid => disb_cancel_rec.row_id,
183 x_award_id => disb_cancel_rec.award_id,
184 x_disb_num => disb_cancel_rec.disb_num,
185 x_tp_cal_type => disb_cancel_rec.tp_cal_type,
186 x_tp_sequence_number => disb_cancel_rec.tp_sequence_number,
187 x_disb_gross_amt => 0,
188 x_fee_1 => disb_cancel_rec.fee_1,
189 x_fee_2 => disb_cancel_rec.fee_2,
190 x_disb_net_amt => 0,
191 x_disb_date => disb_cancel_rec.disb_date,
192 x_trans_type => 'C',
193 x_elig_status => disb_cancel_rec.elig_status,
194 x_elig_status_date => disb_cancel_rec.elig_status_date,
195 x_affirm_flag => disb_cancel_rec.affirm_flag,
196 x_hold_rel_ind => disb_cancel_rec.hold_rel_ind,
197 x_manual_hold_ind => disb_cancel_rec.manual_hold_ind,
198 x_disb_status => disb_cancel_rec.disb_status,
199 x_disb_status_date => disb_cancel_rec.disb_status_date,
200 x_late_disb_ind => disb_cancel_rec.late_disb_ind,
201 x_fund_dist_mthd => disb_cancel_rec.fund_dist_mthd,
202 x_prev_reported_ind => disb_cancel_rec.prev_reported_ind,
203 x_fund_release_date => disb_cancel_rec.fund_release_date,
204 x_fund_status => disb_cancel_rec.fund_status,
205 x_fund_status_date => disb_cancel_rec.fund_status_date,
206 x_fee_paid_1 => disb_cancel_rec.fee_paid_1,
207 x_fee_paid_2 => disb_cancel_rec.fee_paid_2,
208 x_cheque_number => disb_cancel_rec.cheque_number,
209 x_ld_cal_type => disb_cancel_rec.ld_cal_type,
210 x_ld_sequence_number => disb_cancel_rec.ld_sequence_number,
211 x_disb_accepted_amt => 0,
212 x_disb_paid_amt => 0,
213 x_rvsn_id => disb_cancel_rec.rvsn_id,
214 x_int_rebate_amt => disb_cancel_rec.int_rebate_amt,
215 x_force_disb => disb_cancel_rec.force_disb,
216 x_min_credit_pts => disb_cancel_rec.min_credit_pts,
217 x_disb_exp_dt => disb_cancel_rec.disb_exp_dt,
218 x_verf_enfr_dt => disb_cancel_rec.verf_enfr_dt,
219 x_fee_class => disb_cancel_rec.fee_class,
220 x_show_on_bill => disb_cancel_rec.show_on_bill,
221 x_mode => 'R',
222 x_attendance_type_code => disb_cancel_rec.attendance_type_code,
223 x_base_attendance_type_code => disb_cancel_rec.base_attendance_type_code,
224 x_payment_prd_st_date => disb_cancel_rec.payment_prd_st_date,
225 x_change_type_code => disb_cancel_rec.change_type_code,
226 x_fund_return_mthd_code => disb_cancel_rec.fund_return_mthd_code,
227 x_direct_to_borr_flag => disb_cancel_rec.direct_to_borr_flag
228 );
229
230 END LOOP;
231
232 END cancel_extra_disb;
233
234 PROCEDURE create_pell_disb( p_award_id IN NUMBER,
235 p_pell_tab IN igf_gr_pell_calc.pell_tab )
236 IS
237 --
238 ------------------------------------------------------------------
239 -- Created by : sjadhav, Oracle India
240 -- Date created: 1-Dec-2003
241 --
242 -- Purpose:
243 -- Insert disbursement records
244 --
245 -- Known limitations/enhancements and/or remarks:
246 --
247 -- Change History:
248 -------------------------------------------------------------------
249 -- Who When What
250 -------------------------------------------------------------------
251 -- museshad 17-Oct-2005 Bug# 4608591. Reinstating cancelled
252 -- Pell award.
253 -- sjadhav 1-Dec-2003 FA 131 Build
254 -- pssahni 7-Dec-2004 Default value of DRI is set to false
255 -- for full participant
256 -------------------------------------------------------------------
257 --
258
259 CURSOR cur_get_fed_fund_code(
260 cp_award_id igf_aw_award_all.award_id%TYPE
261 ) IS
262 SELECT fed_fund_code,ci_cal_type,ci_sequence_number
263 FROM igf_aw_award_v
264 WHERE award_id = cp_award_id;
265 l_get_fed_fund_code cur_get_fed_fund_code%ROWTYPE;
266
267 CURSOR c_disb(
268 cp_award_id igf_aw_award_all.award_id%TYPE,
269 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
270 ) IS
271 SELECT rowid row_id,
272 disb.*
273 FROM igf_aw_awd_disb_all disb
274 WHERE award_id = cp_award_id
275 AND disb_num = cp_disb_num;
276 l_disb c_disb%ROWTYPE;
277
278 lv_row_id ROWID;
279 l_hold_ind VARCHAR2(6);
280 ln_count NUMBER := 0;
281 BEGIN
282
283 l_hold_ind := NULL;
284 IF p_award_id IS NOT NULL AND
285 p_pell_tab.COUNT > 0 THEN
286
287 OPEN cur_get_fed_fund_code(p_award_id);
288 FETCH cur_get_fed_fund_code INTO l_get_fed_fund_code;
289 CLOSE cur_get_fed_fund_code;
290 IF (l_get_fed_fund_code.fed_fund_code = 'PELL' AND
291 igf_sl_dl_validation.check_full_participant (l_get_fed_fund_code.ci_cal_type,l_get_fed_fund_code.ci_sequence_number,'PELL'))
292 THEN
293 l_hold_ind := 'FALSE';
294 END IF;
295
296 FOR i IN 1..p_pell_tab.COUNT LOOP
297
298 lv_row_id := NULL;
299 ln_count := i;
300
301 OPEN c_disb(cp_award_id => p_award_id, cp_disb_num => i);
302 FETCH c_disb INTO l_disb;
303
304 IF isRepackaging(p_award_id => p_award_id) AND (c_disb%FOUND) THEN
305 l_hold_ind := NVL(l_disb.hold_rel_ind, 'FALSE');
306
307 igf_aw_awd_disb_pkg.update_row (
308 x_mode => 'R',
309 x_rowid => l_disb.row_id,
310 x_award_id => p_award_id,
311 x_disb_num => i,
312 x_tp_cal_type => p_pell_tab(i).tp_cal_type,
313 x_tp_sequence_number => p_pell_tab(i).tp_sequence_number,
314 x_disb_gross_amt => p_pell_tab(i).offered_amt,
315 x_fee_1 => 0,
316 x_fee_2 => 0,
317 x_disb_net_amt => p_pell_tab(i).offered_amt,
318 x_disb_date => p_pell_tab(i).disb_dt,
319 x_trans_type => 'P',
320 x_elig_status => 'N',
321 x_elig_status_date => TRUNC(SYSDATE),
322 x_affirm_flag => l_disb.affirm_flag,
323 x_hold_rel_ind => l_hold_ind,
324 x_manual_hold_ind => 'N',
325 x_disb_status => l_disb.disb_status,
326 x_disb_status_date => l_disb.disb_status_date,
327 x_late_disb_ind => l_disb.late_disb_ind,
328 x_fund_dist_mthd => l_disb.fund_dist_mthd,
329 x_prev_reported_ind => l_disb.prev_reported_ind,
330 x_fund_release_date => l_disb.fund_release_date,
331 x_fund_status => l_disb.fund_status,
332 x_fund_status_date => l_disb.fund_status_date,
333 x_fee_paid_1 => 0,
334 x_fee_paid_2 => 0,
335 x_cheque_number => l_disb.cheque_number,
336 x_ld_cal_type => p_pell_tab(i).ld_cal_type,
337 x_ld_sequence_number => p_pell_tab(i).ld_sequence_number,
338 x_disb_accepted_amt => p_pell_tab(i).accepted_amt,
339 x_disb_paid_amt => 0,
340 x_rvsn_id => l_disb.rvsn_id,
341 x_int_rebate_amt => 0,
342 x_force_disb => 'N',
343 x_min_credit_pts => p_pell_tab(i).min_credit_pts,
344 x_disb_exp_dt => p_pell_tab(i).disb_exp_dt,
345 x_verf_enfr_dt => p_pell_tab(i).verf_enfr_dt,
346 x_fee_class => l_disb.fee_class,
347 x_show_on_bill => p_pell_tab(i).show_on_bill,
348 x_attendance_type_code => p_pell_tab(i).attendance_type_code,
349 x_base_attendance_type_code => p_pell_tab(i).base_attendance_type_code,
350 x_payment_prd_st_date => l_disb.payment_prd_st_date,
351 x_change_type_code => l_disb.change_type_code,
352 x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
353 x_direct_to_borr_flag => l_disb.direct_to_borr_flag
354 );
355 ELSE
356 igf_aw_awd_disb_pkg.insert_row (
357 x_mode => 'R',
358 x_rowid => lv_row_id,
359 x_award_id => p_award_id,
360 x_disb_num => i,
361 x_tp_cal_type => p_pell_tab(i).tp_cal_type,
362 x_tp_sequence_number => p_pell_tab(i).tp_sequence_number,
363 x_disb_gross_amt => p_pell_tab(i).offered_amt,
364 x_fee_1 => 0,
365 x_fee_2 => 0,
366 x_disb_net_amt => p_pell_tab(i).offered_amt,
367 x_disb_date => p_pell_tab(i).disb_dt,
368 x_trans_type => 'P',
369 x_elig_status => 'N',
370 x_elig_status_date => TRUNC(SYSDATE),
371 x_affirm_flag => NULL,
372 x_hold_rel_ind => l_hold_ind,
373 x_manual_hold_ind => 'N',
374 x_disb_status => NULL,
375 x_disb_status_date => NULL,
376 x_late_disb_ind => NULL,
377 x_fund_dist_mthd => NULL,
378 x_prev_reported_ind => NULL,
379 x_fund_release_date => NULL,
380 x_fund_status => NULL,
381 x_fund_status_date => NULL,
382 x_fee_paid_1 => 0,
383 x_fee_paid_2 => 0,
384 x_cheque_number => NULL,
385 x_ld_cal_type => p_pell_tab(i).ld_cal_type,
386 x_ld_sequence_number => p_pell_tab(i).ld_sequence_number,
387 x_disb_accepted_amt => p_pell_tab(i).accepted_amt,
388 x_disb_paid_amt => 0,
389 x_rvsn_id => NULL,
390 x_int_rebate_amt => 0,
391 x_force_disb => 'N',
392 x_min_credit_pts => p_pell_tab(i).min_credit_pts,
393 x_disb_exp_dt => p_pell_tab(i).disb_exp_dt,
394 x_verf_enfr_dt => p_pell_tab(i).verf_enfr_dt,
395 x_fee_class => NULL,
396 x_show_on_bill => p_pell_tab(i).show_on_bill,
397 x_attendance_type_code => p_pell_tab(i).attendance_type_code,
398 x_base_attendance_type_code => p_pell_tab(i).base_attendance_type_code,
399 x_payment_prd_st_date => NULL,
400 x_change_type_code => NULL,
401 x_fund_return_mthd_code => NULL,
402 x_direct_to_borr_flag => 'N'
403 );
404 END IF;
405 CLOSE c_disb;
406
407 END LOOP;
408
409 -- museshad (Bug# 4608591)
410 -- While repackaging any extra disbursements present in the
411 -- award needs to be cancelled
412 IF isRepackaging(p_award_id => p_award_id) THEN
413 cancel_extra_disb (
414 p_award_id => p_award_id,
415 p_disb_num => ln_count
416 );
417 END IF;
418 -- museshad (Bug# 4608591)
419 END IF;
420
421 EXCEPTION
422
423 WHEN OTHERS THEN
424 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
425 fnd_message.set_token('NAME','IGF_AW_GEN_003.CREATE_PELL_DISB'||' ' ||SQLERRM);
426 app_exception.raise_exception;
427
428 END create_pell_disb;
429
430 PROCEDURE updating_coa_in_fa_base (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
431 IS
432 --
433 ------------------------------------------------------------------
434 -- Created by : Amit Dhawan, Oracle India (adhawan)
435 -- Date created: 10-apr-2002
436 --
437 -- Purpose:This is used to update the Financial Aid base record with
438 -- The cost of Attendance (Fixed Coa , Pell Coa , COA for Federal
439 -- COA for Institutional)
440 --
441 --
442 -- Known limitations/enhancements and/or remarks:
443 --
444 -- Change History:
445 -------------------------------------------------------------------
446 -- Who When What
447 -------------------------------------------------------------------
448 -- rasahoo 01-Dec-2003 FA 128 Isir Update
449 -- Added new parameter award_fmly_contribution_type
450 -- to igf_ap_fa_base_rec_pkg.update_row
451 -------------------------------------------------------------------
452 --ugummall 13-OCT-2003 FA 126 Multiple FA Offices
453 -- added new parameter assoc_org_num to
454 -- igf_ap_fa_base_rec_pkg.update_row call.
455 -------------------------------------------------------------------
456 -- sjadhav 09-Apr-2003 Bug 2890177
457 -- Modified updating_coa_in_fa_base
458 -- If pell coa and alt exp for coa items
459 -- have not been defined then update
460 -- fabase record with null values for
461 -- these
462 -------------------------------------------------------------------
463 -- masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
464 -- Removed packaging hold
465 -------------------------------------------------------------------
466 -- adhawan 25-oct-2002 Bug 2613546
467 -- Obsoletion of igf_aw_cit_ssn ,
468 -- using igf_aw_coa_items instead
469 -- Getting pell_coa_amount,
470 -- pell_alt_expense
471 -- and updating in Fa base
472 -- Added pell_alt_exp in update
473 -- row of fabase
474 -- c_stud_det modified to
475 -- select from igf_ap_fa_base_rec
476 -- instead of fa_con_v
477 -- Removed p_coa , p_ci_cal_type ,
478 -- p_sequence_number passed as
479 -- paramters
480 -------------------------------------------------------------------
481 -- masehgal 25-Sep-2002 Bug 2315112
482 -- FA 104 - To Do Enhancements
483 -- Added manual_disb_hold in FA
484 -- Base update
485 -------------------------------------------------------------------
486 -- adhawan 12-apr-2002 Updating the Fa Record
487 -------------------------------------------------------------------
488 --
489
490 CURSOR c_stud_det (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
491 IS
492 SELECT fa_detail.*
493 FROM igf_ap_fa_base_rec fa_detail
494 WHERE fa_detail.base_id = p_base_id;
495
496 l_stud_det c_stud_det%ROWTYPE ;
497 --
498 -- Modified for bug Id 2613546
499 --
500 CURSOR cur_tot_coa (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
501 IS
502 SELECT
503 SUM(NVL(citsn.amount,0)) coa_total,
504 SUM(NVL(citsn.pell_coa_amount,0)) pell_coa,
505 SUM(NVL(citsn.alt_pell_amount,0)) pell_alt_expense,
506 SUM(DECODE(citsn.fixed_cost,'Y',NVL(citsn.amount,0),0) ) fixed_coa
507 FROM igf_aw_coa_items citsn
508 WHERE citsn.base_id = p_base_id ;
509
510 tot_coa_rec cur_tot_coa%ROWTYPE;
511
512 --
513 -- Bug 2890177
514 --
515 CURSOR cur_tot_coa_null (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
516 IS
517 SELECT
518 SUM(NVL(citsn.pell_coa_amount,-1)) pell_coa,
519 SUM(NVL(citsn.alt_pell_amount,-1)) pell_alt_expense
520 FROM igf_aw_coa_items citsn
521 WHERE citsn.base_id = p_base_id ;
522
523 tot_coa_null_rec cur_tot_coa_null%ROWTYPE;
524
525 CURSOR cur_tot_coa_cnt (p_base_id igf_ap_fa_base_rec.base_id%TYPE)
526 IS
527 SELECT
528 COUNT(base_id) rec_cnt
529 FROM igf_aw_coa_items citsn
530 WHERE citsn.base_id = p_base_id ;
531
532 tot_coa_cnt_rec cur_tot_coa_cnt%ROWTYPE;
533
534 --
535 -- Bug 2890177
536 --
537
538
539 BEGIN
540
541 --
542 -- 1.open the student record.
543 -- 2.get the cost of attendance code assigned to the student
544 --
545
546 OPEN c_stud_det(p_base_id);
547 FETCH c_stud_det INTO l_stud_det;
548 IF c_stud_det%NOTFOUND THEN
549 CLOSE c_stud_det;
550 RETURN;
551 END IF;
552
553 OPEN cur_tot_coa(p_base_id);
554 FETCH cur_tot_coa INTO tot_coa_rec;
555 CLOSE cur_tot_coa;
556
557 --
558 -- Bug 2890177
559 --
560
561 OPEN cur_tot_coa_cnt(p_base_id);
562 FETCH cur_tot_coa_cnt INTO tot_coa_cnt_rec;
563 CLOSE cur_tot_coa_cnt;
564
565 OPEN cur_tot_coa_null(p_base_id);
566 FETCH cur_tot_coa_null INTO tot_coa_null_rec;
567 CLOSE cur_tot_coa_null;
568
569 IF tot_coa_null_rec.pell_coa <> tot_coa_cnt_rec.rec_cnt AND
570 tot_coa_cnt_rec.rec_cnt = ABS(tot_coa_null_rec.pell_coa) THEN
571 tot_coa_rec.pell_coa := NULL;
572 END IF;
573
574 IF tot_coa_null_rec.pell_alt_expense <> tot_coa_cnt_rec.rec_cnt AND
575 tot_coa_cnt_rec.rec_cnt = ABS(tot_coa_null_rec.pell_alt_expense) THEN
576 tot_coa_rec.pell_alt_expense := NULL;
577 END IF;
578
579 --
580 -- Bug 2890177
581 --
582
583 igf_ap_fa_base_rec_pkg.update_row(
584 x_rowid => l_stud_det.row_id,
585 x_base_id => l_stud_det.base_id,
586 x_ci_cal_type => l_stud_det.ci_cal_type,
587 x_person_id => l_stud_det.person_id,
588 x_ci_sequence_number => l_stud_det.ci_sequence_number,
589 x_org_id => l_stud_det.org_id,
590 x_coa_pending => l_stud_det.coa_pending,
591 x_verification_process_run => l_stud_det.verification_process_run,
592 x_inst_verif_status_date => l_stud_det.inst_verif_status_date,
593 x_manual_verif_flag => l_stud_det.manual_verif_flag,
594 x_fed_verif_status => l_stud_det.fed_verif_status,
595 x_fed_verif_status_date => l_stud_det.fed_verif_status_date,
596 x_inst_verif_status => l_stud_det.inst_verif_status,
597 x_nslds_eligible => l_stud_det.nslds_eligible,
598 x_ede_correction_batch_id => l_stud_det.ede_correction_batch_id,
599 x_fa_process_status_date => l_stud_det.fa_process_status_date,
600 x_isir_corr_status => l_stud_det.isir_corr_status,
601 x_isir_corr_status_date => l_stud_det.isir_corr_status_date,
602 x_isir_status => l_stud_det.isir_status,
603 x_isir_status_date => l_stud_det.isir_status_date,
604 x_coa_code_f => NULL,
605 x_coa_code_i => NULL,
606 x_coa_f => tot_coa_rec.coa_total,
607 x_coa_i => tot_coa_rec.coa_total,
608 x_disbursement_hold => l_stud_det.disbursement_hold,
609 x_fa_process_status => l_stud_det.fa_process_status,
610 x_notification_status => l_stud_det.notification_status,
611 x_notification_status_date => l_stud_det.notification_status_date,
612 x_packaging_status => l_stud_det.packaging_status,
613 x_packaging_status_date => l_stud_det.packaging_status_date,
614 x_total_package_accepted => l_stud_det.total_package_accepted,
615 x_total_package_offered => l_stud_det.total_package_offered,
616 x_admstruct_id => l_stud_det.admstruct_id,
617 x_admsegment_1 => l_stud_det.admsegment_1,
618 x_admsegment_2 => l_stud_det.admsegment_2,
619 x_admsegment_3 => l_stud_det.admsegment_3,
620 x_admsegment_4 => l_stud_det.admsegment_4,
621 x_admsegment_5 => l_stud_det.admsegment_5,
622 x_admsegment_6 => l_stud_det.admsegment_6,
623 x_admsegment_7 => l_stud_det.admsegment_7,
624 x_admsegment_8 => l_stud_det.admsegment_8,
625 x_admsegment_9 => l_stud_det.admsegment_9,
626 x_admsegment_10 => l_stud_det.admsegment_10,
627 x_admsegment_11 => l_stud_det.admsegment_11,
628 x_admsegment_12 => l_stud_det.admsegment_12,
629 x_admsegment_13 => l_stud_det.admsegment_13,
630 x_admsegment_14 => l_stud_det.admsegment_14,
631 x_admsegment_15 => l_stud_det.admsegment_15,
632 x_admsegment_16 => l_stud_det.admsegment_16,
633 x_admsegment_17 => l_stud_det.admsegment_17,
634 x_admsegment_18 => l_stud_det.admsegment_18,
635 x_admsegment_19 => l_stud_det.admsegment_19,
636 x_admsegment_20 => l_stud_det.admsegment_20,
637 x_packstruct_id => l_stud_det.packstruct_id,
638 x_packsegment_1 => l_stud_det.packsegment_1,
639 x_packsegment_2 => l_stud_det.packsegment_2,
640 x_packsegment_3 => l_stud_det.packsegment_3,
641 x_packsegment_4 => l_stud_det.packsegment_4,
642 x_packsegment_5 => l_stud_det.packsegment_5,
643 x_packsegment_6 => l_stud_det.packsegment_6,
644 x_packsegment_7 => l_stud_det.packsegment_7,
645 x_packsegment_8 => l_stud_det.packsegment_8,
646 x_packsegment_9 => l_stud_det.packsegment_9,
647 x_packsegment_10 => l_stud_det.packsegment_10,
648 x_packsegment_11 => l_stud_det.packsegment_11,
649 x_packsegment_12 => l_stud_det.packsegment_12,
650 x_packsegment_13 => l_stud_det.packsegment_13,
651 x_packsegment_14 => l_stud_det.packsegment_14,
652 x_packsegment_15 => l_stud_det.packsegment_15,
653 x_packsegment_16 => l_stud_det.packsegment_16,
654 x_packsegment_17 => l_stud_det.packsegment_17,
655 x_packsegment_18 => l_stud_det.packsegment_18,
656 x_packsegment_19 => l_stud_det.packsegment_19,
657 x_packsegment_20 => l_stud_det.packsegment_20,
658 x_miscstruct_id => l_stud_det.miscstruct_id,
659 x_miscsegment_1 => l_stud_det.miscsegment_1,
660 x_miscsegment_2 => l_stud_det.miscsegment_2,
661 x_miscsegment_3 => l_stud_det.miscsegment_3,
662 x_miscsegment_4 => l_stud_det.miscsegment_4,
663 x_miscsegment_5 => l_stud_det.miscsegment_5,
664 x_miscsegment_6 => l_stud_det.miscsegment_6,
665 x_miscsegment_7 => l_stud_det.miscsegment_7,
666 x_miscsegment_8 => l_stud_det.miscsegment_8,
667 x_miscsegment_9 => l_stud_det.miscsegment_9,
668 x_miscsegment_10 => l_stud_det.miscsegment_10,
669 x_miscsegment_11 => l_stud_det.miscsegment_11,
670 x_miscsegment_12 => l_stud_det.miscsegment_12,
671 x_miscsegment_13 => l_stud_det.miscsegment_13,
672 x_miscsegment_14 => l_stud_det.miscsegment_14,
673 x_miscsegment_15 => l_stud_det.miscsegment_15,
674 x_miscsegment_16 => l_stud_det.miscsegment_16,
675 x_miscsegment_17 => l_stud_det.miscsegment_17,
676 x_miscsegment_18 => l_stud_det.miscsegment_18,
677 x_miscsegment_19 => l_stud_det.miscsegment_19,
678 x_miscsegment_20 => l_stud_det.miscsegment_20,
679 x_prof_judgement_flg => l_stud_det.prof_judgement_flg,
680 x_nslds_data_override_flg => l_stud_det.nslds_data_override_flg ,
681 x_target_group => l_stud_det.target_group,
682 x_coa_fixed => tot_coa_rec.fixed_coa,
683 x_coa_pell => tot_coa_rec.pell_coa,
684 x_profile_status => l_stud_det.profile_status,
685 x_profile_status_date => l_stud_det.profile_status_date,
686 x_profile_fc => l_stud_det.profile_fc,
687 x_tolerance_amount => l_stud_det.tolerance_amount,
688 x_pell_alt_expense => tot_coa_rec.pell_alt_expense,
689 x_manual_disb_hold => l_stud_det.manual_disb_hold,
690 x_mode => 'R',
691 x_assoc_org_num => l_stud_det.assoc_org_num,
692 x_award_fmly_contribution_type => l_stud_det.award_fmly_contribution_type,
693 x_isir_locked_by => l_stud_det.isir_locked_by,
694 x_adnl_unsub_loan_elig_flag => l_stud_det.adnl_unsub_loan_elig_flag,
695 x_lock_coa_flag => l_stud_det.lock_coa_flag,
696 x_lock_awd_flag => l_stud_det.lock_awd_flag
697 );
698
699 EXCEPTION
700
701 WHEN OTHERS THEN
702 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
703 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATING_COA_IN_FA_BASE'||' ' ||SQLERRM);
704 app_exception.raise_exception;
705
706 END updating_coa_in_fa_base;
707
708 PROCEDURE round_off_disbursements(
709 p_fund_id IN igf_aw_award_t_all.fund_id%TYPE,
710 p_award_id IN igf_aw_award_t_all.award_id%TYPE,
711 p_offered_amt IN igf_aw_award_t_all.offered_amt%TYPE,
712 p_award_status IN igf_aw_award_all.award_status%TYPE,
713 p_dist_plan_code IN igf_aw_awd_dist_plans.dist_plan_method_code%TYPE,
714 p_disb_count IN NUMBER
715 )
716 IS
717 /*
718 || Created By : bvisvana
719 || Created On : 01-July-2005
720 || Purpose :
721 ||
722 || Known limitations, enhancements or remarks :
723 || Change History :
724 || Who WHEN What
725 || museshad 27-Sep-2005 Bug 4608591.
726 || Modified the cursor cur_get_all_disb
727 || so that it ignores cancelled disb.
728 || (reverse chronological order - newest change first)
729 */
730
731 -- Returns all the disbursements for an award
732 -- The ORDER BY clause ensures that the disbursements are returned in the order of their creation
733 CURSOR cur_get_all_disb (p_award_id igf_aw_award_all.award_id%TYPE)
734 IS
735 SELECT disb.rowid, disb.*
736 FROM igf_aw_awd_disb disb
737 WHERE
738 award_id = p_award_id AND
739 trans_type <> 'C'
740 ORDER BY disb_num ;
741
742 l_disb_amt NUMBER(12,3) := 0;
743 l_disb_prelim_amt NUMBER(12,3) := 0;
744 l_disb_amt_extra NUMBER(12,3) := 0;
745 l_disb_inter_sum_amt NUMBER(12,3) := 0;
746 l_disb_diff NUMBER := 0;
747 l_trunc_factor NUMBER := 0;
748 l_extra_factor NUMBER := 0;
749 l_disb_no NUMBER := 0;
750 l_special_disb_no NUMBER := 0;
751 l_disb_limit1 NUMBER := 0;
752 l_disb_limit2 NUMBER := 0;
753 l_step NUMBER := 0;
754 l_accepted_amt NUMBER(12,3) := 0;
755 l_disb_round_factor igf_aw_fund_mast.disb_rounding_code%TYPE;
756
757 TYPE l_disb_structure IS RECORD(
758 fund_id igf_aw_fund_mast.fund_id%TYPE,
759 disb_num NUMBER,
760 disb_amt NUMBER
761 );
762 TYPE l_disb_structure_tab IS TABLE OF l_disb_structure INDEX BY BINARY_INTEGER;
763 l_disb_structure_rec l_disb_structure_tab;
764
765 -- Get fed fund code
766 CURSOR cur_get_fund (p_fund_id NUMBER)
767 IS
768 SELECT fcat.fed_fund_code
769 FROM igf_aw_fund_cat fcat,
770 igf_aw_fund_mast fmast
771 WHERE fcat.fund_code = fmast.fund_code
772 AND fmast.fund_id = p_fund_id;
773
774 get_fund_rec cur_get_fund%ROWTYPE;
775 l_disb_net_amt NUMBER(12,3) := 0;
776
777 BEGIN
778 l_disb_round_factor := igf_aw_packaging.get_disb_round_factor(p_fund_id);
779
780 OPEN cur_get_fund(p_fund_id => p_fund_id);
781 FETCH cur_get_fund INTO get_fund_rec;
782 CLOSE cur_get_fund;
783
784 IF l_disb_round_factor IN ('ONE_FIRST','DEC_FIRST','ONE_LAST','DEC_LAST') THEN -- disb_round_factor
785
786 -- Log useful values
787 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
788 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.round_off_disbursements.debug ','ROUND FACTOR = '||l_disb_round_factor);
789 END IF;
790
791 -- Set the attributes common to ONEs rounding factor
792 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'ONE_LAST' THEN
793 l_trunc_factor := 0;
794 l_extra_factor := 1;
795 -- Set the attributes common to DECIMALs rounding factor
796 ELSIF l_disb_round_factor = 'DEC_FIRST' OR l_disb_round_factor = 'DEC_LAST' THEN
797 l_trunc_factor := 2;
798 l_extra_factor := 0.01;
799 END IF;
800
801 -- Set the attributes common to FIRST rounding factor
802 IF l_disb_round_factor = 'ONE_FIRST' OR l_disb_round_factor = 'DEC_FIRST' THEN
803 IF UPPER(p_dist_plan_code) = 'E' THEN
804 l_disb_limit1 := 1;
805 l_disb_limit2 := p_disb_count;
806 l_step := 1;
807 l_disb_no := l_disb_limit1;
808 ELSIF UPPER(p_dist_plan_code) IN ('C', 'M') THEN
809 l_special_disb_no := 1;
810 END IF;
811
812 -- Set the attributes common to LAST rounding factor
813 ELSIF l_disb_round_factor = 'ONE_LAST' OR l_disb_round_factor = 'DEC_LAST' THEN
814 IF UPPER(p_dist_plan_code) = 'E' THEN
815 l_disb_limit1 := 1;
816 l_disb_limit2 := p_disb_count;
817 l_step := -1;
818 l_disb_no := l_disb_limit2;
819 ELSIF UPPER(p_dist_plan_code) IN ('C', 'M') THEN
820 l_special_disb_no := p_disb_count;
821 END IF;
822 END IF;
823
824 -- Equal Distribution
825 IF UPPER(p_dist_plan_code) = 'E' THEN -- p_dist_plan_code
826
827 IF get_fund_rec.fed_fund_code <> 'PELL' THEN
828 -- Normal disbursement amount
829 l_disb_amt := TRUNC(NVL((p_offered_amt/p_disb_count), 0), l_trunc_factor);
830 -- Preliminary disbursement amount
831 l_disb_prelim_amt := TRUNC(NVL((p_offered_amt - (l_disb_amt * (p_disb_count-1))), 0), l_trunc_factor);
832 -- Difference in disbursement amount
833 l_disb_diff := TRUNC(NVL((l_disb_prelim_amt - l_disb_amt), 0), l_trunc_factor);
834 -- Extra disbursement amount
835 IF l_disb_diff > 0 THEN
836 l_disb_amt_extra := TRUNC(NVL((l_disb_amt + l_extra_factor), 0), l_trunc_factor);
837 ELSIF l_disb_diff < 0 THEN
838 l_disb_amt_extra := TRUNC(NVL((l_disb_amt - l_extra_factor), 0), l_trunc_factor);
839 ELSE
840 l_disb_amt_extra := TRUNC(NVL(l_disb_amt, 0), l_trunc_factor);
841 END IF;
842
843 -- Get the absolute difference value between preliminary and normal disbursement amount
844 l_disb_diff := ABS(l_disb_diff);
845
846 -- Calculate each disbursement and distribute the extra
847 -- amount starting from the first/last disbursement
848 WHILE l_disb_no BETWEEN l_disb_limit1 AND l_disb_limit2
849 LOOP
850 l_disb_structure_rec(l_disb_no).disb_num := l_disb_no;
851
852 IF l_disb_diff >= l_extra_factor THEN
853 l_disb_structure_rec(l_disb_no).disb_amt := l_disb_amt_extra;
854 l_disb_diff := NVL((l_disb_diff - l_extra_factor), 0);
855 ELSE
856 l_disb_structure_rec(l_disb_no).disb_amt := l_disb_amt;
857 END IF;
858
859 l_disb_no := NVL(l_disb_no, 0) + l_step;
860
861 -- Log useful values
862 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
863 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.round_off_disbursements.debug ','l_disb_no = '||l_disb_no);
864 END IF;
865 END LOOP;
866 END IF;
867
868 -- Match COA/Manual Distribution
869 ELSIF UPPER(p_dist_plan_code) IN ('C', 'M') THEN
870 -- Initialize disbursement counter
871 l_disb_no := 1;
872 -- Loop thru all the disbursement records and round the disbursement amount
873 FOR l_disb_rec_all IN cur_get_all_disb(p_award_id)
874 LOOP
875 -- Skip the first/last disbursement
876 IF l_disb_no <> l_special_disb_no THEN
877 -- Calculate disbursement amount truncated to correct decimal place
878 l_disb_amt := TRUNC(NVL(l_disb_rec_all.disb_gross_amt, 0), l_trunc_factor);
879 -- Add the disbursement to PL/SQL table
880 l_disb_structure_rec(l_disb_no).disb_num := l_disb_no;
881 l_disb_structure_rec(l_disb_no).disb_amt := l_disb_amt;
882
883 l_disb_inter_sum_amt := NVL((l_disb_inter_sum_amt + l_disb_amt), 0);
884
885 -- Log useful values
886 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
887 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_aw_gen_003.round_off_disbursements.debug ',
888 'Disbursement number: '||l_disb_structure_rec(l_disb_no).disb_num ||
889 'Disbursement amount: ' || to_char(l_disb_structure_rec(l_disb_no).disb_amt));
890 END IF;
891 END IF;
892 l_disb_no := NVL(l_disb_no, 0) + 1;
893 END LOOP;
894
895 -- Calculate first/last disbursement. Unlike other disbursements,
896 l_disb_amt := TRUNC(NVL((p_offered_amt - l_disb_inter_sum_amt), 0), l_trunc_factor);
897
898 -- Add the first/last disbursement to PL/SQL table
899 l_disb_structure_rec(l_special_disb_no).disb_num := l_special_disb_no;
900 l_disb_structure_rec(l_special_disb_no).disb_amt := l_disb_amt;
901
902 -- Log useful values
903 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
904 fnd_log.string(fnd_log.level_statement,
905 ' igf.plsql.igf_aw_gen_003.round_off_disbursements.debug ',
906 ' Disbursement number: '|| l_disb_structure_rec(l_special_disb_no).disb_num ||
907 ' Disbursement amount: ' || to_char(l_disb_structure_rec(l_special_disb_no).disb_amt));
908 END IF;
909
910 END IF; -- End of p_dist_plan_code
911 END IF; -- End of disb_round_factor
912
913 -- All the rounded disbursement amounts are now available in the RECORD
914 -- Update these to the disbursement table
915 l_disb_no := 0;
916 FOR l_disb_rec IN cur_get_all_disb(p_award_id)
917 LOOP
918 -- Get all disbursements
919 l_disb_no := NVL(l_disb_no, 0) + 1;
920
921 -- Check if the PL/SQL table has got a valid value for that disbursement number
922 IF l_disb_structure_rec.EXISTS(l_disb_no) THEN -- Disbursement existence check
923
924 -- If the Status is accepted then disb_accepted_amt = the new disb amt after rounding
925 IF p_award_status = 'ACCEPTED' THEN
926 l_accepted_amt := NVL(l_disb_structure_rec(l_disb_no).disb_amt,0);
927 END IF;
928
929 l_disb_net_amt := NVL(l_disb_structure_rec(l_disb_no).disb_amt,0) -
930 NVL(l_disb_rec.fee_1,0) -
931 NVL(l_disb_rec.fee_2,0) +
932 NVL(l_disb_rec.fee_paid_1,0) +
933 NVL(l_disb_rec.fee_paid_2,0) +
934 NVL(l_disb_rec.int_rebate_amt,0);
935
936 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
937 fnd_log.string(fnd_log.level_statement,
938 ' igf.plsql.igf_aw_gen_003.round_off_disbursements.debug ',
939 'Disbursement amounts before and after applying rounding logic');
940
941 fnd_log.string(fnd_log.level_statement,
942 'igf.plsql.igf_aw_packaging.round_off_disbursements.debug ',
943 ' Disbursement number: ' ||l_disb_no||
944 ' Old Disbursement amount: ' ||l_disb_rec.disb_gross_amt||
945 ' New disbursement amount after applying rounding logic: ' ||NVL(l_disb_structure_rec(l_disb_no).disb_amt, 0));
946 END IF;
947
948 igf_aw_awd_disb_pkg.update_row(
949 x_rowid => l_disb_rec.rowid ,
950 x_award_id => l_disb_rec.award_id ,
951 x_disb_num => l_disb_rec.disb_num ,
952 x_tp_cal_type => l_disb_rec.tp_cal_type,
953 x_tp_sequence_number => l_disb_rec.tp_sequence_number ,
954 x_disb_gross_amt => NVL(l_disb_structure_rec(l_disb_no).disb_amt,0),
955 x_fee_1 => l_disb_rec.fee_1 ,
956 x_fee_2 => l_disb_rec.fee_2 ,
957 x_disb_net_amt => l_disb_net_amt ,
958 x_disb_date => l_disb_rec.disb_date ,
959 x_trans_type => l_disb_rec.trans_type ,
960 x_elig_status => l_disb_rec.elig_status ,
961 x_elig_status_date => l_disb_rec.elig_status_date ,
962 x_affirm_flag => l_disb_rec.affirm_flag ,
963 x_hold_rel_ind => l_disb_rec.hold_rel_ind ,
964 x_manual_hold_ind => l_disb_rec.manual_hold_ind ,
965 x_disb_status => l_disb_rec.disb_status ,
966 x_disb_status_date => l_disb_rec.disb_status_date ,
967 x_late_disb_ind => l_disb_rec.late_disb_ind ,
968 x_fund_dist_mthd => l_disb_rec.fund_dist_mthd ,
969 x_prev_reported_ind => l_disb_rec.prev_reported_ind ,
970 x_fund_release_date => l_disb_rec.fund_release_date ,
971 x_fund_status => l_disb_rec.fund_status ,
972 x_fund_status_date => l_disb_rec.fund_status_date ,
973 x_fee_paid_1 => l_disb_rec.fee_paid_1 ,
974 x_fee_paid_2 => l_disb_rec.fee_paid_2 ,
975 x_cheque_number => l_disb_rec.cheque_number ,
976 x_ld_cal_type => l_disb_rec.ld_cal_type ,
977 x_ld_sequence_number => l_disb_rec.ld_sequence_number ,
978 x_disb_accepted_amt => l_accepted_amt ,
979 x_disb_paid_amt => l_disb_rec.disb_paid_amt ,
980 x_rvsn_id => l_disb_rec.rvsn_id ,
981 x_int_rebate_amt => l_disb_rec.int_rebate_amt ,
982 x_force_disb => l_disb_rec.force_disb ,
983 x_min_credit_pts => l_disb_rec.min_credit_pts ,
984 x_disb_exp_dt => l_disb_rec.disb_exp_dt ,
985 x_verf_enfr_dt => l_disb_rec.verf_enfr_dt ,
986 x_fee_class => l_disb_rec.fee_class ,
987 x_show_on_bill => l_disb_rec.show_on_bill ,
988 x_mode => 'R' ,
989 x_attendance_type_code => l_disb_rec.attendance_type_code ,
990 x_base_attendance_type_code=> l_disb_rec.base_attendance_type_code ,
991 x_payment_prd_st_date => l_disb_rec.payment_prd_st_date ,
992 x_change_type_code => l_disb_rec.change_type_code ,
993 x_fund_return_mthd_code => l_disb_rec.fund_return_mthd_code,
994 x_direct_to_borr_flag => l_disb_rec.direct_to_borr_flag
995 );
996 END IF; -- End of Disbursement existence check
997 END LOOP; -- End of Get all disbursements loop
998 END round_off_disbursements;
999
1000 PROCEDURE create_auto_disb( p_fund_id IN igf_aw_award.fund_id%TYPE,
1001 p_award_id IN igf_aw_award.award_id%TYPE,
1002 p_offered_amt IN igf_aw_award.offered_amt%TYPE,
1003 p_award_status IN igf_aw_award.award_status%TYPE,
1004 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
1005 p_method_code IN igf_aw_awd_dist_plans.dist_plan_method_code%TYPE,
1006 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
1007 )
1008 AS
1009 /*
1010 || Created By :
1011 || Created On :
1012 || Purpose :
1013 || Known limitations, enhancements or remarks :
1014 || Change History :
1015 || Who When What
1016 || museshad 24-Apr-2006 Bug 5116534.
1017 || 1. Modified cursor cur_nslds_hist to chk for
1018 || valid nslds_loan_prog_code_1 data.
1019 || 2. Modified the logic used to derive lb_nslds_ind
1020 || museshad 27-Sep-2005 Bug 4608591.
1021 || Implemented repackaging of awards.
1022 || museshad 26-Aug-2005 Join condition was incorrect in the cursor
1023 || 'cur_terms_count'. Corrected this.
1024 || veramach 22-Dec-2004 bug 4077735 - Added a check to see if there are common terms
1025 || when awarding without COA
1026 || veramach 12-Oct-2004 FA 152 - changed signature to include p_awd_prd_code
1027 || bkkumar 02-04-04 FACR116 - Added the new paramter p_alt_rel_code to the
1028 || get_loan_fee1 , get_loan_fee2 , get_cl_hold_rel_ind
1029 || , get_cl_auto_late_ind
1030 || veramach 17-NOV-2003 FA 125 added 2 new parameters - adplans_id,dist_plan_method_code
1031 || bkkumar 30-sep-03 Added base_id to the get_loan_fee1 and
1032 || get_loan_fee2 and added call to get_cl_hold_rel_ind
1033 || , get_cl_auto_late_ind
1034 || (reverse chronological order - newest change first)
1035 */
1036
1037 --cursor to get fund details
1038 CURSOR cur_get_fund_dtls(
1039 cp_fund_id igf_aw_award.fund_id%TYPE
1040 ) IS
1041 SELECT fcat.fund_code,
1042 fcat.fed_fund_code,
1043 fund.disb_exp_da,
1044 fund.ci_cal_type awd_cal_type,
1045 fund.ci_sequence_number awd_sequence_number,
1046 fund.disb_verf_da,
1047 fund.show_on_bill,
1048 fund.nslds_disb_da
1049 FROM igf_aw_fund_mast fund,
1050 igf_aw_fund_cat fcat
1051 WHERE fund_id = cp_fund_id
1052 AND fund.fund_code = fcat.fund_code;
1053 l_get_fund_dtls cur_get_fund_dtls%ROWTYPE;
1054
1055 --
1056 -- Cursor to Create Disbursement Records
1057 -- Please note that disb_dt, the first field is needed for
1058 -- ordering disbursement records by disbursement dates
1059 -- This is used only in case the distribution method is MANUAL
1060 --
1061 CURSOR c_auto_disb(
1062 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1063 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1064 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1065 ) IS
1066 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1067 terms.ld_cal_type ld_cal_type,
1068 terms.ld_sequence_number ld_sequence_number,
1069 teach_periods.tp_cal_type tp_cal_type,
1070 teach_periods.tp_sequence_number tp_sequence_number,
1071 (teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
1072 teach_periods.start_date start_dt,
1073 teach_periods.date_offset_cd tp_offset_da,
1074 teach_periods.credit_points_num min_credit_points,
1075 teach_periods.attendance_type_code attendance_type_code
1076 FROM igf_aw_dp_terms terms,
1077 igf_aw_dp_teach_prds_v teach_periods,
1078 (SELECT base_id,
1079 ld_cal_type,
1080 ld_sequence_number
1081 FROM igf_aw_coa_itm_terms
1082 WHERE base_id = cp_base_id
1083 GROUP BY base_id,ld_cal_type, ld_sequence_number) coaterms,
1084 igf_aw_awd_dist_plans dp,
1085 igf_aw_awd_prd_term aprd
1086 WHERE terms.adplans_id = cp_adplans_id
1087 AND terms.adterms_id = teach_periods.adterms_id
1088 AND coaterms.ld_cal_type = terms.ld_cal_type
1089 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1090 AND coaterms.base_id = cp_base_id
1091 AND terms.adplans_id = dp.adplans_id
1092 AND dp.cal_type = aprd.ci_cal_type
1093 AND dp.sequence_number = aprd.ci_sequence_number
1094 AND aprd.award_prd_cd = cp_awd_prd_code
1095 AND coaterms.ld_cal_type = aprd.ld_cal_type
1096 AND coaterms.ld_sequence_number = aprd.ld_sequence_number
1097 ORDER BY 1;
1098
1099 lc_auto_disb c_auto_disb%ROWTYPE;
1100
1101
1102 --
1103 -- Awarding WITHOUT COA, Manual Method
1104 --
1105
1106 CURSOR c_auto_disb_wcoa(
1107 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1108 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1109 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1110 ) IS
1111 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1112 terms.ld_cal_type ld_cal_type,
1113 terms.ld_sequence_number ld_sequence_number,
1114 teach_periods.tp_cal_type tp_cal_type,
1115 teach_periods.tp_sequence_number tp_sequence_number,
1116 (teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
1117 teach_periods.start_date start_dt,
1118 teach_periods.date_offset_cd tp_offset_da,
1119 teach_periods.credit_points_num min_credit_points,
1120 teach_periods.attendance_type_code attendance_type_code
1121 FROM igf_aw_dp_terms terms,
1122 igf_aw_dp_teach_prds_v teach_periods,
1123 igf_aw_awd_prd_term aprd,
1124 igf_aw_awd_dist_plans dp
1125 WHERE terms.adplans_id = cp_adplans_id
1126 AND terms.adterms_id = teach_periods.adterms_id
1127 AND terms.ld_cal_type = aprd.ld_cal_type
1128 AND terms.ld_sequence_number = aprd.ld_sequence_number
1129 AND aprd.award_prd_cd = cp_awd_prd_code
1130 AND dp.adplans_id = terms.adplans_id
1131 AND dp.cal_type = aprd.ci_cal_type
1132 AND dp.sequence_number = aprd.ci_sequence_number
1133 ORDER BY 1;
1134
1135 --
1136 -- cursor to create disbursment records
1137 -- this cursor is used in case the distribution method is EQUAL
1138 --
1139
1140 CURSOR c_auto_disb_equal(
1141 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1142 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1143 cp_num_terms NUMBER,
1144 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1145 ) IS
1146 SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1147 terms.ld_cal_type ld_cal_type,
1148 terms.ld_sequence_number ld_sequence_number,
1149 teach_periods.tp_cal_type tp_cal_type,
1150 teach_periods.tp_sequence_number tp_sequence_number,
1151 teach_periods.tp_perct_num/cp_num_terms perct,
1152 teach_periods.start_date start_dt,
1153 teach_periods.date_offset_cd tp_offset_da,
1154 teach_periods.credit_points_num min_credit_points,
1155 teach_periods.attendance_type_code attendance_type_code
1156 FROM igf_aw_dp_terms terms,
1157 igf_aw_dp_teach_prds_v teach_periods,
1158 (SELECT base_id,
1159 ld_cal_type,
1160 ld_sequence_number
1161 FROM igf_aw_coa_itm_terms
1162 WHERE base_id = cp_base_id
1163 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
1164 igf_aw_awd_dist_plans dp,
1165 igf_aw_awd_prd_term aprd
1166 WHERE terms.adplans_id = cp_adplans_id
1167 AND terms.adterms_id = teach_periods.adterms_id
1168 AND coaterms.ld_cal_type = terms.ld_cal_type
1169 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1170 AND coaterms.base_id = cp_base_id
1171 AND terms.adplans_id = dp.adplans_id
1172 AND dp.cal_type = aprd.ci_cal_type
1173 AND dp.sequence_number = aprd.ci_sequence_number
1174 AND coaterms.ld_cal_type = aprd.ld_cal_type
1175 AND coaterms.ld_sequence_number = aprd.ld_sequence_number
1176 AND aprd.award_prd_cd = cp_awd_prd_code
1177 ORDER BY 1;
1178
1179 --
1180 -- Adding UNION clause to take care of Awarding without COA
1181 --
1182 CURSOR c_auto_disb_equal_wcoa(
1183 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1184 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1185 cp_num_terms NUMBER,
1186 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1187 ) IS
1188 SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1189 terms.ld_cal_type ld_cal_type,
1190 terms.ld_sequence_number ld_sequence_number,
1191 teach_periods.tp_cal_type tp_cal_type,
1192 teach_periods.tp_sequence_number tp_sequence_number,
1193 teach_periods.tp_perct_num/cp_num_terms perct,
1194 teach_periods.start_date start_dt,
1195 teach_periods.date_offset_cd tp_offset_da,
1196 teach_periods.credit_points_num min_credit_points,
1197 teach_periods.attendance_type_code attendance_type_code
1198 FROM igf_aw_dp_terms terms,
1199 igf_aw_dp_teach_prds_v teach_periods,
1200 igf_aw_awd_dist_plans dp,
1201 igf_aw_awd_prd_term aprd
1202 WHERE terms.adplans_id = cp_adplans_id
1203 AND terms.adterms_id = teach_periods.adterms_id
1204 AND terms.adplans_id = dp.adplans_id
1205 AND dp.cal_type = aprd.ci_cal_type
1206 AND dp.sequence_number = aprd.ci_sequence_number
1207 AND aprd.award_prd_cd = cp_awd_prd_code
1208 AND aprd.ld_cal_type = terms.ld_cal_type
1209 AND aprd.ld_sequence_number = terms.ld_sequence_number
1210 ORDER BY 1;
1211
1212 --cursor to create disbursment records
1213 --this cursor is used if distribution method is MATCH COA
1214 CURSOR c_auto_disb_coa_match(
1215 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1216 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1217 cp_total_coa_amount NUMBER,
1218 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1219 ) IS
1220 SELECT NVL(igf_aw_packaging.get_date_instance (cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1221 terms.ld_cal_type ld_cal_type,
1222 terms.ld_sequence_number ld_sequence_number,
1223 teach_periods.tp_cal_type tp_cal_type,
1224 teach_periods.tp_sequence_number tp_sequence_number,
1225 (coa_term_amount/cp_total_coa_amount) * teach_periods.tp_perct_num perct,
1226 teach_periods.start_date start_dt,
1227 teach_periods.date_offset_cd tp_offset_da,
1228 teach_periods.credit_points_num min_credit_points,
1229 teach_periods.attendance_type_code attendance_type_code
1230 FROM igf_aw_dp_terms terms,
1231 igf_aw_dp_teach_prds_v teach_periods,
1232 (SELECT base_id,
1233 ld_cal_type,
1234 ld_sequence_number,
1235 amount coa_term_amount
1236 FROM igf_aw_coa_term_tot_v
1237 WHERE base_id = cp_base_id) coaterms,
1238 igf_aw_awd_dist_plans dp,
1239 igf_aw_awd_prd_term aprd
1240 WHERE terms.adplans_id = cp_adplans_id
1241 AND terms.adterms_id = teach_periods.adterms_id
1242 AND coaterms.ld_cal_type = terms.ld_cal_type
1243 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1244 AND coaterms.base_id = cp_base_id
1245 AND dp.adplans_id = terms.adplans_id
1246 AND dp.cal_type = aprd.ci_cal_type
1247 AND dp.sequence_number = aprd.ci_sequence_number
1248 AND aprd.award_prd_cd = cp_awd_prd_code
1249 AND aprd.ld_cal_type = coaterms.ld_cal_type
1250 AND aprd.ld_sequence_number = coaterms.ld_sequence_number
1251 ORDER BY 1;
1252 -------------bug 4077735----------------------
1253 CURSOR c_auto_disb_pell(
1254 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1255 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1256 ) IS
1257 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1258 terms.ld_cal_type ld_cal_type,
1259 terms.ld_sequence_number ld_sequence_number,
1260 teach_periods.tp_cal_type tp_cal_type,
1261 teach_periods.tp_sequence_number tp_sequence_number,
1262 (teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
1263 teach_periods.start_date start_dt,
1264 teach_periods.date_offset_cd tp_offset_da,
1265 teach_periods.credit_points_num min_credit_points,
1266 teach_periods.attendance_type_code attendance_type_code
1267 FROM igf_aw_dp_terms terms,
1268 igf_aw_dp_teach_prds_v teach_periods,
1269 (SELECT base_id,
1270 ld_cal_type,
1271 ld_sequence_number
1272 FROM igf_aw_coa_itm_terms
1273 WHERE base_id = cp_base_id
1274 GROUP BY base_id,ld_cal_type, ld_sequence_number) coaterms
1275 WHERE terms.adplans_id = cp_adplans_id
1276 AND terms.adterms_id = teach_periods.adterms_id
1277 AND coaterms.ld_cal_type = terms.ld_cal_type
1278 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1279 AND coaterms.base_id = cp_base_id
1280 ORDER BY 1;
1281
1282
1283 --
1284 -- Awarding WITHOUT COA, Manual Method
1285 --
1286
1287 CURSOR c_auto_disb_wcoa_pell(
1288 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1289 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1290 ) IS
1291 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1292 terms.ld_cal_type ld_cal_type,
1293 terms.ld_sequence_number ld_sequence_number,
1294 teach_periods.tp_cal_type tp_cal_type,
1295 teach_periods.tp_sequence_number tp_sequence_number,
1296 (teach_periods.tp_perct_num * terms.ld_perct_num)/100 perct,
1297 teach_periods.start_date start_dt,
1298 teach_periods.date_offset_cd tp_offset_da,
1299 teach_periods.credit_points_num min_credit_points,
1300 teach_periods.attendance_type_code attendance_type_code
1301 FROM igf_aw_dp_terms terms,
1302 igf_aw_dp_teach_prds_v teach_periods
1303 WHERE terms.adplans_id = cp_adplans_id
1304 AND terms.adterms_id = teach_periods.adterms_id
1305 ORDER BY 1;
1306
1307 --
1308 -- cursor to create disbursment records
1309 -- this cursor is used in case the distribution method is EQUAL
1310 --
1311
1312 CURSOR c_auto_disb_equal_pell(
1313 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1314 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1315 cp_num_terms NUMBER
1316 ) IS
1317 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1318 terms.ld_cal_type ld_cal_type,
1319 terms.ld_sequence_number ld_sequence_number,
1320 teach_periods.tp_cal_type tp_cal_type,
1321 teach_periods.tp_sequence_number tp_sequence_number,
1322 teach_periods.tp_perct_num/cp_num_terms perct,
1323 teach_periods.start_date start_dt,
1324 teach_periods.date_offset_cd tp_offset_da,
1325 teach_periods.credit_points_num min_credit_points,
1326 teach_periods.attendance_type_code attendance_type_code
1327 FROM igf_aw_dp_terms terms,
1328 igf_aw_dp_teach_prds_v teach_periods,
1329 (SELECT base_id,
1330 ld_cal_type,
1331 ld_sequence_number
1332 FROM igf_aw_coa_itm_terms
1333 WHERE base_id = cp_base_id
1334 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
1335 WHERE terms.adplans_id = cp_adplans_id
1336 AND terms.adterms_id = teach_periods.adterms_id
1337 AND coaterms.ld_cal_type = terms.ld_cal_type
1338 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1339 AND coaterms.base_id = cp_base_id
1340 ORDER BY 1;
1341
1342
1343 CURSOR c_auto_disb_equal_wcoa_pell(
1344 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1345 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1346 cp_num_terms NUMBER
1347 ) IS
1348 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1349 terms.ld_cal_type ld_cal_type,
1350 terms.ld_sequence_number ld_sequence_number,
1351 teach_periods.tp_cal_type tp_cal_type,
1352 teach_periods.tp_sequence_number tp_sequence_number,
1353 teach_periods.tp_perct_num/cp_num_terms perct,
1354 teach_periods.start_date start_dt,
1355 teach_periods.date_offset_cd tp_offset_da,
1356 teach_periods.credit_points_num min_credit_points,
1357 teach_periods.attendance_type_code attendance_type_code
1358 FROM igf_aw_dp_terms terms,
1359 igf_aw_dp_teach_prds_v teach_periods
1360 WHERE terms.adplans_id = cp_adplans_id
1361 AND terms.adterms_id = teach_periods.adterms_id
1362 ORDER BY 1;
1363
1364 --cursor to create disbursment records
1365 --this cursor is used if distribution method is MATCH COA
1366 CURSOR c_auto_disb_coa_match_pell(
1367 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1368 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1369 cp_total_coa_amount NUMBER
1370 ) IS
1371 SELECT NVL(igf_aw_packaging.get_date_instance(cp_base_id,teach_periods.date_offset_cd,terms.ld_cal_type,terms.ld_sequence_number),teach_periods.start_date) disb_dt,
1372 terms.ld_cal_type ld_cal_type,
1373 terms.ld_sequence_number ld_sequence_number,
1374 teach_periods.tp_cal_type tp_cal_type,
1375 teach_periods.tp_sequence_number tp_sequence_number,
1376 (coa_term_amount/cp_total_coa_amount) * teach_periods.tp_perct_num perct,
1377 teach_periods.start_date start_dt,
1378 teach_periods.date_offset_cd tp_offset_da,
1379 teach_periods.credit_points_num min_credit_points,
1380 teach_periods.attendance_type_code attendance_type_code
1381 FROM igf_aw_dp_terms terms,
1382 igf_aw_dp_teach_prds_v teach_periods,
1383 (SELECT base_id,
1384 ld_cal_type,
1385 ld_sequence_number,
1386 amount coa_term_amount
1387 FROM igf_aw_coa_term_tot_v
1388 WHERE base_id = cp_base_id) coaterms
1389 WHERE terms.adplans_id = cp_adplans_id
1390 AND terms.adterms_id = teach_periods.adterms_id
1391 AND coaterms.ld_cal_type = terms.ld_cal_type
1392 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1393 AND coaterms.base_id = cp_base_id
1394 ORDER BY 1;
1395
1396 CURSOR cur_terms_count_pell(
1397 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1398 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1399 ) IS
1400 SELECT COUNT(*)
1401 FROM igf_aw_dp_terms terms,
1402 (SELECT base_id,
1403 ld_cal_type,
1404 ld_sequence_number
1405 FROM igf_aw_coa_itm_terms
1406 WHERE base_id = cp_base_id
1407 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
1408 WHERE terms.adplans_id = cp_adplans_id
1409 AND coaterms.ld_cal_type = terms.ld_cal_type
1410 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1411 AND coaterms.base_id = cp_base_id;
1412
1413 CURSOR cur_terms_count_wcoa_pell(
1414 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1415 ) IS
1416 SELECT COUNT(*)
1417 FROM igf_aw_dp_terms terms
1418 WHERE terms.adplans_id = cp_adplans_id;
1419
1420 CURSOR c_coa_pell(
1421 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1422 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1423 ) IS
1424 SELECT SUM(amount) coa
1425 FROM igf_aw_coa_itm_terms coa_terms,
1426 (SELECT ld_cal_type,
1427 ld_sequence_number
1428 FROM igf_aw_dp_terms
1429 WHERE adplans_id = cp_adplans_id
1430 )dist_terms
1431 WHERE dist_terms.ld_cal_type = coa_terms.ld_cal_type
1432 AND dist_terms.ld_sequence_number = coa_terms.ld_sequence_number
1433 AND coa_terms.base_id = cp_base_id;
1434
1435 -------------bug 4077735----------------------
1436 -- Get COA
1437 CURSOR c_coa(
1438 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1439 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1440 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1441 ) IS
1442 SELECT SUM(amount) coa
1443 FROM igf_aw_coa_itm_terms coa_terms,
1444 (SELECT ld_cal_type,
1445 ld_sequence_number
1446 FROM igf_aw_dp_terms
1447 WHERE adplans_id = cp_adplans_id
1448 )dist_terms,
1449 igf_ap_fa_base_rec_all fa,
1450 igf_aw_awd_prd_term aprd
1451 WHERE dist_terms.ld_cal_type = coa_terms.ld_cal_type
1452 AND dist_terms.ld_sequence_number = coa_terms.ld_sequence_number
1453 AND coa_terms.base_id = cp_base_id
1454 AND coa_terms.base_id = fa.base_id
1455 AND fa.ci_cal_type = aprd.ci_cal_type
1456 AND fa.ci_sequence_number = aprd.ci_sequence_number
1457 AND aprd.award_prd_cd = cp_awd_prd_code
1458 AND aprd.ld_cal_type = coa_terms.ld_cal_type
1459 AND aprd.ld_sequence_number = coa_terms.ld_sequence_number;
1460
1461 ln_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
1462
1463 --
1464 -- get terms count
1465 --
1466 CURSOR cur_terms_count(
1467 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1468 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1469 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1470 ) IS
1471 SELECT COUNT(*)
1472 FROM igf_aw_dp_terms terms,
1473 (SELECT base_id,
1474 ld_cal_type,
1475 ld_sequence_number
1476 FROM igf_aw_coa_itm_terms
1477 WHERE base_id = cp_base_id
1478 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
1479 igf_aw_awd_prd_term aprd,
1480 igf_ap_fa_base_rec_all fa
1481 WHERE terms.adplans_id = cp_adplans_id
1482 AND coaterms.ld_cal_type = terms.ld_cal_type
1483 AND coaterms.ld_sequence_number = terms.ld_sequence_number
1484 AND coaterms.base_id = cp_base_id
1485 AND coaterms.base_id = fa.base_id
1486 AND fa.ci_cal_type = aprd.ci_cal_type
1487 AND fa.ci_sequence_number = aprd.ci_sequence_number
1488 AND aprd.award_prd_cd = cp_awd_prd_code
1489 AND aprd.ld_cal_type = coaterms.ld_cal_type
1490 AND aprd.ld_sequence_number = coaterms.ld_sequence_number;
1491
1492 l_terms_count NUMBER := 0;
1493
1494 CURSOR cur_terms_count_wcoa(
1495 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1496 cp_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1497 )
1498 IS
1499 SELECT COUNT(*)
1500 FROM igf_aw_dp_terms terms,
1501 igf_aw_awd_dist_plans dp,
1502 igf_aw_awd_prd_term aprd
1503 WHERE terms.adplans_id = cp_adplans_id
1504 AND terms.adplans_id = dp.adplans_id
1505 AND dp.cal_type = aprd.ci_cal_type
1506 AND dp.sequence_number = aprd.ci_sequence_number
1507 AND aprd.award_prd_cd = cp_awd_prd_code
1508 AND aprd.ld_cal_type = terms.ld_cal_type
1509 AND aprd.ld_sequence_number = terms.ld_sequence_number;
1510
1511 -- Get a specific disbursment for an award
1512 CURSOR c_disb(
1513 cp_award_id igf_aw_award_all.award_id%TYPE,
1514 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
1515 ) IS
1516 SELECT rowid row_id,
1517 disb.*
1518 FROM igf_aw_awd_disb_all disb
1519 WHERE award_id = cp_award_id
1520 AND disb_num = cp_disb_num;
1521
1522 l_disb c_disb%ROWTYPE;
1523
1524 CURSOR cur_get_base ( p_award_id igf_aw_award_all.award_id%TYPE) IS
1525 SELECT base_id
1526 FROM igf_aw_award_all
1527 WHERE award_id = p_award_id;
1528
1529 get_base_rec cur_get_base%ROWTYPE;
1530
1531 CURSOR cur_nslds_hist ( p_base_id igf_ap_fa_base_rec.base_id%type ) IS
1532 SELECT 'x'
1533 FROM igf_ap_nslds_data nslds,
1534 igf_ap_fa_base_rec_all fabase
1535 WHERE fabase.person_id = (SELECT person_id from igf_ap_fa_base_rec_all WHERE base_id = p_base_id) AND
1536 fabase.base_id = nslds.base_id AND
1537 nslds.nslds_loan_prog_code_1 IS NOT NULL;
1538
1539 x_nslds_hist cur_nslds_hist%ROWTYPE;
1540
1541 -- Get distribution plan name
1542 CURSOR c_adplans_dtls(
1543 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1544 ) IS
1545 SELECT awd_dist_plan_cd_desc
1546 FROM igf_aw_awd_dist_plans
1547 WHERE adplans_id = cp_adplans_id;
1548
1549 l_adplans_dtls c_adplans_dtls%ROWTYPE;
1550
1551 NO_COMMON_TERMS EXCEPTION;
1552 NO_AP_DP_COMM_TERMS EXCEPTION;
1553 lb_nslds_ind BOOLEAN := TRUE;
1554 lb_coa_exist BOOLEAN := TRUE;
1555
1556 lv_row_id ROWID;
1557 ln_total_disbs NUMBER := 0;
1558 ln_count NUMBER := 0;
1559 ln_disb_accepted_amt NUMBER(12,3) := 0;
1560 ln_disb_gross_amt NUMBER(12,3) := 0;
1561 ln_disb_net_amt NUMBER(12,3) := 0;
1562 ln_run_disb_gross_amt NUMBER(12,3) := 0;
1563 ln_fee_1 NUMBER(12,2) := 0;
1564 ln_fee_2 NUMBER(12,2) := 0;
1565 ln_int_rebate_amt NUMBER(12,2) := 0;
1566 ln_dummy_net_amt NUMBER(12,2) := 0;
1567 ln_dummy_fee_1 NUMBER(12,2) := 0;
1568 lv_base_att_type VARCHAR2(1);
1569 ld_verf_enfr_dt igf_aw_awd_disb_all.verf_enfr_dt%TYPE;
1570 ld_disb_date igf_aw_awd_disb_all.disb_date%TYPE;
1571 ld_disb_date1 igf_aw_awd_disb_all.disb_date%TYPE;
1572 ld_disb_exp_dt igf_aw_awd_disb_all.disb_exp_dt%TYPE;
1573 l_hold_ind igf_sl_cl_setup_all.hold_rel_ind%TYPE;
1574 l_auto_ind igf_sl_cl_setup_all.auto_late_disb_ind%TYPE;
1575 l_adplans_name igf_aw_awd_dist_plans.awd_dist_plan_cd_desc%TYPE;
1576
1577 BEGIN
1578
1579 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1580 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_fund_id:'||p_fund_id);
1581 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_award_id:'||p_award_id);
1582 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_adplans_id:'||p_adplans_id);
1583 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_method_code:'||p_method_code);
1584 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_offered_amt:'||p_offered_amt);
1585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_awd_prd_code:'||p_awd_prd_code);
1586 END IF;
1587
1588 OPEN cur_get_base(p_award_id);
1589 FETCH cur_get_base INTO get_base_rec;
1590 CLOSE cur_get_base;
1591
1592 OPEN c_adplans_dtls(p_adplans_id);
1593 FETCH c_adplans_dtls INTO l_adplans_dtls;
1594 CLOSE c_adplans_dtls;
1595
1596 ln_run_disb_gross_amt := 0;
1597 l_adplans_name := l_adplans_dtls.awd_dist_plan_cd_desc;
1598
1599 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1600 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','base_id:'||get_base_rec.base_id);
1601 END IF;
1602
1603 --
1604 -- Check for common terms only if COA is present
1605 --
1606 lb_coa_exist := TRUE;
1607 lb_coa_exist := check_coa(get_base_rec.base_id,p_awd_prd_code);
1608
1609 IF lb_coa_exist THEN
1610 check_common_terms(p_adplans_id,get_base_rec.base_id,ln_total_disbs,p_awd_prd_code);
1611 IF ln_total_disbs = 0 THEN
1612 RAISE NO_COMMON_TERMS;
1613 END IF;
1614 ELSE
1615 ln_total_disbs := get_plan_disb_count(p_adplans_id,p_awd_prd_code);
1616 END IF;
1617
1618 OPEN cur_get_fund_dtls(p_fund_id);
1619 FETCH cur_get_fund_dtls INTO l_get_fund_dtls;
1620 CLOSE cur_get_fund_dtls;
1621
1622 lc_auto_disb := NULL;
1623
1624 /* method check */
1625 IF p_method_code = 'M' THEN -- Manual distribution
1626
1627 IF lb_coa_exist THEN
1628 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1629 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb');
1630 END IF;
1631 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
1632 OPEN c_auto_disb_pell(get_base_rec.base_id,p_adplans_id);
1633 IF c_auto_disb_pell%NOTFOUND THEN
1634 CLOSE c_auto_disb_pell;
1635 ELSE
1636 FETCH c_auto_disb_pell INTO lc_auto_disb;
1637 END IF;
1638 ELSE
1639 OPEN c_auto_disb(get_base_rec.base_id,p_adplans_id,p_awd_prd_code);
1640 IF c_auto_disb%NOTFOUND THEN
1641 CLOSE c_auto_disb;
1642 ELSE
1643 FETCH c_auto_disb INTO lc_auto_disb;
1644 END IF;
1645 END IF;
1646 ELSE
1647 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1648 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_wcoa');
1649 END IF;
1650 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
1651 OPEN c_auto_disb_wcoa_pell(get_base_rec.base_id,p_adplans_id);
1652 IF c_auto_disb_wcoa_pell%NOTFOUND THEN
1653 CLOSE c_auto_disb_wcoa_pell;
1654 ELSE
1655 FETCH c_auto_disb_wcoa_pell INTO lc_auto_disb;
1656 END IF;
1657 ELSE
1658 OPEN c_auto_disb_wcoa(get_base_rec.base_id,p_adplans_id,p_awd_prd_code);
1659 IF c_auto_disb_wcoa%NOTFOUND THEN
1660 CLOSE c_auto_disb_wcoa;
1661 ELSE
1662 FETCH c_auto_disb_wcoa INTO lc_auto_disb;
1663 END IF;
1664 END IF;
1665 END IF;
1666
1667 ELSIF p_method_code = 'E' THEN -- Equal Distribution
1668 --Find the number of terms
1669
1670 IF lb_coa_exist THEN
1671 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
1672 OPEN cur_terms_count_pell(get_base_rec.base_id,p_adplans_id);
1673 FETCH cur_terms_count_pell INTO l_terms_count;
1674 CLOSE cur_terms_count_pell;
1675
1676 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1677 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_equal_pell with l_terms_count:'||l_terms_count);
1678 END IF;
1679 OPEN c_auto_disb_equal_pell(get_base_rec.base_id,p_adplans_id,l_terms_count);
1680 IF c_auto_disb_equal_pell%NOTFOUND THEN
1681 CLOSE c_auto_disb_equal_pell;
1682 ELSE
1683 FETCH c_auto_disb_equal_pell INTO lc_auto_disb;
1684 END IF;
1685 ELSE
1686 OPEN cur_terms_count(get_base_rec.base_id,p_adplans_id,p_awd_prd_code);
1687 FETCH cur_terms_count INTO l_terms_count;
1688 CLOSE cur_terms_count;
1689
1690 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1691 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_equal with l_terms_count:'||l_terms_count);
1692 END IF;
1693 OPEN c_auto_disb_equal(get_base_rec.base_id,p_adplans_id,l_terms_count,p_awd_prd_code);
1694 IF c_auto_disb_equal%NOTFOUND THEN
1695 CLOSE c_auto_disb_equal;
1696 ELSE
1697 FETCH c_auto_disb_equal INTO lc_auto_disb;
1698 END IF;
1699 END IF;
1700 ELSE
1701 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
1702 OPEN cur_terms_count_wcoa_pell(p_adplans_id);
1703 FETCH cur_terms_count_wcoa_pell INTO l_terms_count;
1704 CLOSE cur_terms_count_wcoa_pell;
1705
1706 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1707 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_equal_wcoa_pell with l_terms_count:'||l_terms_count);
1708 END IF;
1709 IF l_terms_count = 0 THEN
1710 RAISE NO_AP_DP_COMM_TERMS;
1711 END IF;
1712 OPEN c_auto_disb_equal_wcoa_pell(get_base_rec.base_id,p_adplans_id,l_terms_count);
1713 IF c_auto_disb_equal_wcoa_pell%NOTFOUND THEN
1714 CLOSE c_auto_disb_equal_wcoa_pell;
1715 ELSE
1716 FETCH c_auto_disb_equal_wcoa_pell INTO lc_auto_disb;
1717 END IF;
1718 ELSE
1719 OPEN cur_terms_count_wcoa(p_adplans_id,p_awd_prd_code);
1720 FETCH cur_terms_count_wcoa INTO l_terms_count;
1721 CLOSE cur_terms_count_wcoa;
1722
1723 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1724 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_equal_wcoa with l_terms_count:'||l_terms_count);
1725 END IF;
1726 IF l_terms_count = 0 THEN
1727 RAISE NO_AP_DP_COMM_TERMS;
1728 END IF;
1729 OPEN c_auto_disb_equal_wcoa(get_base_rec.base_id,p_adplans_id,l_terms_count,p_awd_prd_code);
1730 IF c_auto_disb_equal_wcoa%NOTFOUND THEN
1731 CLOSE c_auto_disb_equal_wcoa;
1732 ELSE
1733 FETCH c_auto_disb_equal_wcoa INTO lc_auto_disb;
1734 END IF;
1735 END IF;
1736 END IF;
1737
1738 ELSIF p_method_code = 'C' THEN -- Match COA distribution
1739
1740 --
1741 -- For Match COA method cannot award if
1742 -- there is no COA
1743 --
1744
1745 IF lb_coa_exist THEN
1746 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
1747 OPEN c_coa_pell(get_base_rec.base_id,p_adplans_id);
1748 FETCH c_coa_pell INTO ln_coa;
1749 CLOSE c_coa_pell;
1750
1751 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1752 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_coa_match_pell with ln_coa:'||ln_coa);
1753 END IF;
1754 OPEN c_auto_disb_coa_match_pell(get_base_rec.base_id,p_adplans_id,NVL(ln_coa,0));
1755 IF c_auto_disb_coa_match_pell%NOTFOUND THEN
1756 CLOSE c_auto_disb_coa_match_pell;
1757 ELSE
1758 FETCH c_auto_disb_coa_match_pell INTO lc_auto_disb;
1759 END IF;
1760 ELSE
1761 OPEN c_coa(get_base_rec.base_id,p_adplans_id,p_awd_prd_code);
1762 FETCH c_coa INTO ln_coa;
1763 CLOSE c_coa;
1764
1765 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1766 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','opening c_auto_disb_coa_match with ln_coa:'||ln_coa);
1767 END IF;
1768 OPEN c_auto_disb_coa_match(get_base_rec.base_id,p_adplans_id,NVL(ln_coa,0),p_awd_prd_code);
1769 IF c_auto_disb_coa_match%NOTFOUND THEN
1770 CLOSE c_auto_disb_coa_match;
1771
1772 ELSE
1773 FETCH c_auto_disb_coa_match INTO lc_auto_disb;
1774 END IF;
1775 END IF;
1776 ELSE
1777 RAISE NO_COMMON_TERMS;
1778 END IF;
1779
1780 END IF;/* end method check*/
1781
1782
1783 LOOP
1784
1785 -- Initialize all the variables.
1786 ln_disb_gross_amt := 0;
1787 ln_disb_net_amt := 0;
1788 ln_dummy_net_amt := 0;
1789 ln_dummy_fee_1 := 0;
1790 ln_fee_1 := 0;
1791 ln_fee_2 := 0;
1792 ln_int_rebate_amt := 0;
1793 ld_verf_enfr_dt := NULL;
1794 ld_disb_date := NULL;
1795 ld_disb_exp_dt := NULL;
1796
1797 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1798 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','disb_dt:'||lc_auto_disb.disb_dt);
1799 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ld_cal_type:'||lc_auto_disb.ld_cal_type);
1800 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ld_sequence_number:'||lc_auto_disb.ld_sequence_number);
1801 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','tp_cal_type:'||lc_auto_disb.tp_cal_type);
1802 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','tp_sequence_number:'||lc_auto_disb.tp_sequence_number);
1803 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','perct:'||lc_auto_disb.perct);
1804 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','start_dt:'||lc_auto_disb.start_dt);
1805 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','tp_offset_da:'||lc_auto_disb.tp_offset_da);
1806 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','min_credit_points:'||lc_auto_disb.min_credit_points);
1807 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','attendance_type_code:'||lc_auto_disb.attendance_type_code);
1808 END IF;
1809
1810 IF p_method_code = 'E' THEN
1811 IF l_get_fund_dtls.fed_fund_code = 'PELL' AND lb_coa_exist THEN
1812 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1813 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','calling num_disb with plan/cal_type/seq_num->'||p_adplans_id||'/'||lc_auto_disb.ld_cal_type||'/'||lc_auto_disb.ld_sequence_number);
1814 END IF;
1815 lc_auto_disb.perct := 100 / igf_gr_pell_calc.num_disb(p_adplans_id,lc_auto_disb.ld_cal_type,lc_auto_disb.ld_sequence_number);
1816 ELSIF l_get_fund_dtls.fed_fund_code = 'PELL' AND NOT lb_coa_exist THEN
1817 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1818 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','calling num_disb with plan/cal_type/seq_num->'||p_adplans_id||'/NULL/NULL');
1819 END IF;
1820 lc_auto_disb.perct := 100 / igf_gr_pell_calc.num_disb(p_adplans_id,NULL,NULL);
1821 ELSE
1822 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1823 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','calling num_disb with plan/cal_type/seq_num->'||p_adplans_id||'/NULL/NULL');
1824 END IF;
1825 lc_auto_disb.perct := 100 / igf_gr_pell_calc.num_disb(p_adplans_id,NULL,NULL);
1826 END IF;
1827 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1828 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','reset lc_auto_disb.perct to:'||lc_auto_disb.perct);
1829 END IF;
1830 END IF;
1831
1832 -- Calculate the Disbursement Gross Amount.
1833 ln_disb_gross_amt := NVL(((p_offered_amt) * lc_auto_disb.perct)/100, 0);
1834
1835 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1836 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ln_disb_gross_amt:'||ln_disb_gross_amt);
1837 END IF;
1838
1839 ln_count := ln_count + 1;
1840 -- Calculate the running totals
1841 ln_run_disb_gross_amt := ln_run_disb_gross_amt + ln_disb_gross_amt;
1842
1843 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1844 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','all others ln_count: '||ln_count||' ln_disb_gross_amt: '||ln_disb_gross_amt);
1845 END IF;
1846 -- Calculate the Fee_1, Rebate Amount and the net amount for Direct Loans
1847 IF igf_sl_gen.chk_dl_fed_fund_code(l_get_fund_dtls.fed_fund_code) = 'TRUE' THEN
1848 -- we are passing dummys here for Net and Fee amount as they will
1849 -- be calculated later on
1850 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1851 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','before round off call chk_dl_fed_fund_code = TRUE!ln_disb_net_amt:'||ln_disb_net_amt);
1852 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','before round off call ln_disb_gross_amt:'||ln_disb_gross_amt);
1853 END IF;
1854
1855 igf_sl_roundoff_digits_pkg.gross_fees_roundoff (
1856 p_last_disb_num => ln_total_disbs,
1857 p_offered_amt => p_offered_amt,
1858 p_fee_perct => igf_sl_award.get_loan_fee1(l_get_fund_dtls.fed_fund_code,
1859 l_get_fund_dtls.awd_cal_type,
1860 l_get_fund_dtls.awd_sequence_number,
1861 get_base_rec.base_id,
1862 igf_sl_award.get_alt_rel_code(l_get_fund_dtls.fund_code)),
1863 p_disb_gross_amt => ln_disb_gross_amt,
1864 p_disb_net_amt => ln_dummy_net_amt,
1865 p_fee => ln_dummy_fee_1
1866 ) ;
1867
1868 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1869 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','after round off call ln_disb_net_amt:'||ln_disb_net_amt);
1870 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','after round off call ln_disb_gross_amt:'||ln_disb_gross_amt);
1871 END IF;
1872
1873 -- This routine will return Net Amount/ Fee Amounts / Interest Rebate Amount
1874 igf_sl_award.get_loan_amts( l_get_fund_dtls.awd_cal_type,
1875 l_get_fund_dtls.awd_sequence_number,
1876 l_get_fund_dtls.fed_fund_code,
1877 ln_disb_gross_amt,
1878 ln_int_rebate_amt,
1879 ln_fee_1,
1880 ln_disb_net_amt);
1881
1882 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1883 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','After get loan amts ln_disb_net_amt:'||ln_disb_net_amt);
1884 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','After get loan amts ln_disb_gross_amt:'||ln_disb_gross_amt);
1885 END IF;
1886
1887 ELSIF igf_sl_gen.chk_cl_fed_fund_code(l_get_fund_dtls.fed_fund_code) = 'TRUE' THEN
1888
1889 -- FA 122 Loan Enhancemnts Add base_id to the call of get_loan_fee1 , get_loan_fee2
1890 -- added call to get_cl_hold_rel_ind , get_cl_auto_late_ind
1891
1892 l_hold_ind := igf_sl_award.get_cl_hold_rel_ind(l_get_fund_dtls.fed_fund_code,l_get_fund_dtls.awd_cal_type,l_get_fund_dtls.awd_sequence_number,get_base_rec.base_id,igf_sl_award.get_alt_rel_code(l_get_fund_dtls.fund_code));
1893 l_auto_ind := igf_sl_award.get_cl_auto_late_ind(l_get_fund_dtls.fed_fund_code,l_get_fund_dtls.awd_cal_type,l_get_fund_dtls.awd_sequence_number,get_base_rec.base_id,igf_sl_award.get_alt_rel_code(l_get_fund_dtls.fund_code));
1894
1895 -- Calculate Origination Fee
1896 ln_fee_1 := igf_sl_award.get_loan_fee1( l_get_fund_dtls.fed_fund_code,
1897 l_get_fund_dtls.awd_cal_type,
1898 l_get_fund_dtls.awd_sequence_number,
1899 get_base_rec.base_id,
1900 igf_sl_award.get_alt_rel_code(l_get_fund_dtls.fund_code));
1901 ln_fee_1 := ln_fee_1 * ln_disb_gross_amt/100 ;
1902 ln_fee_1 := TRUNC(ln_fee_1);
1903
1904 -- Calculate the Guaratee Fee
1905 ln_fee_2 := igf_sl_award.get_loan_fee2( l_get_fund_dtls.fed_fund_code,
1906 l_get_fund_dtls.awd_cal_type,
1907 l_get_fund_dtls.awd_sequence_number,
1908 get_base_rec.base_id,
1909 igf_sl_award.get_alt_rel_code(l_get_fund_dtls.fund_code));
1910 ln_fee_2 := ln_fee_2 * ln_disb_gross_amt/100;
1911 ln_fee_2 := TRUNC(ln_fee_2);
1912
1913 -- get the rounded off gross amount by using this routine
1914 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1915 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','before round off call chk_cl_fed_fund_code = TRUE!ln_disb_net_amt:'||ln_disb_net_amt);
1916 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','before round off call ln_disb_gross_amt:'||ln_disb_gross_amt);
1917 END IF;
1918
1919 igf_sl_roundoff_digits_pkg.cl_gross_fees_roundoff ( p_last_disb_num => ln_total_disbs,
1920 p_offered_amt => p_offered_amt,
1921 p_disb_gross_amt => ln_disb_gross_amt );
1922
1923 ln_disb_net_amt := NVL(ln_disb_gross_amt,0) - NVL(ln_fee_1,0) - NVL(ln_fee_2,0);
1924 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1925 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','after round off call chk_cl_fed_fund_code = TRUE!ln_disb_net_amt:'||ln_disb_net_amt);
1926 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','after round off call ln_disb_gross_amt:'||ln_disb_gross_amt);
1927 END IF;
1928
1929 ELSE
1930 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1931 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','chk_dl_fed_fund_code,chk_cl_fed_fund_code = FALSE!ln_disb_net_amt:'||ln_disb_net_amt);
1932 END IF;
1933
1934 ln_disb_net_amt := NVL(ln_disb_gross_amt,0);
1935 END IF;
1936
1937 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1938 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ln_count:'||ln_count);
1939 END IF;
1940
1941 --
1942 -- For all types of loans and it it is a first disbursement disbursement date
1943 -- should be NSLDS date else its an offset date or start date of term.
1944 --
1945
1946 IF (ln_count = 1)
1947 AND ( igf_sl_gen.chk_dl_fed_fund_code (l_get_fund_dtls.fed_fund_code) = 'TRUE'
1948 OR igf_sl_gen.chk_cl_fed_fund_code (l_get_fund_dtls.fed_fund_code) = 'TRUE' )
1949 AND (l_get_fund_dtls.nslds_disb_da IS NOT NULL) THEN
1950
1951 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1952 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','inside fed_fund_code checks');
1953 END IF;
1954
1955 ld_disb_date1 := NULL;
1956
1957 OPEN cur_nslds_hist (get_base_rec.base_id);
1958 FETCH cur_nslds_hist INTO x_nslds_hist;
1959
1960 IF cur_nslds_hist%NOTFOUND THEN
1961 --
1962 -- No NSLDS History exists for current student , so delay the disbursement
1963 --
1964 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1965 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','student has no NSLDS history, so applying NSLDS date offset.');
1966 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','lc_auto_disb.tp_offset_da:'||lc_auto_disb.tp_offset_da);
1967 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','lc_auto_disb.ld_cal_type:'||lc_auto_disb.ld_cal_type);
1968 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','lc_auto_disb.ld_sequence_number:'||lc_auto_disb.ld_sequence_number);
1969 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','l_get_fund_dtls.nslds_disb_da:'||l_get_fund_dtls.nslds_disb_da);
1970 END IF;
1971
1972 ld_disb_date1 := igf_ap_gen_001.get_date_alias_val(
1973 get_base_rec.base_id,
1974 lc_auto_disb.ld_cal_type,
1975 lc_auto_disb.ld_sequence_number,
1976 l_get_fund_dtls.nslds_disb_da
1977 );
1978
1979 IF ld_disb_date1 IS NOT NULL THEN
1980 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1981 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','NSLDS Disb Offset date, ld_disb_date1:' ||ld_disb_date1);
1982 END IF;
1983 ELSE
1984 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1985 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug',
1986 'Cannot compute NSLDS offset date. Some error in computing NSLDS offset date. So using the actual disb date.');
1987 END IF;
1988 END IF;
1989 ELSE
1990 -- NSLDS history exists. Do not delay disb date.
1991 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1992 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','Student has NSLDS history, so NOT applying NSLDS date offset.');
1993 END IF;
1994 END IF;
1995
1996 CLOSE cur_nslds_hist;
1997 END IF;
1998 --
1999 -- Calculate the actual Disbursement expiration date and
2000 -- verification enforcement date from the offset dates.
2001 --
2002
2003 ld_disb_exp_dt := igf_ap_gen_001.get_date_alias_val(
2004 get_base_rec.base_id,
2005 lc_auto_disb.ld_cal_type,
2006 lc_auto_disb.ld_sequence_number,
2007 l_get_fund_dtls.disb_exp_da
2008 );
2009
2010 ld_verf_enfr_dt := igf_ap_gen_001.get_date_alias_val(
2011 get_base_rec.base_id,
2012 lc_auto_disb.ld_cal_type,
2013 lc_auto_disb.ld_sequence_number,
2014 l_get_fund_dtls.disb_verf_da
2015 );
2016
2017 IF ld_disb_date1 IS NOT NULL AND ln_count = 1 THEN
2018 -- Student does NOT have NSLDS history, apply NSLDS date offset to delay disb date
2019 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2020 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','Setting lb_nslds_ind to FALSE, so that NSLDS date offset will be applied.');
2021 END IF;
2022
2023 lb_nslds_ind := FALSE;
2024 END IF;
2025
2026 IF p_award_status = 'ACCEPTED' THEN
2027 ln_disb_accepted_amt := ln_disb_gross_amt;
2028 ELSE
2029 ln_disb_accepted_amt := 0;
2030 END IF;
2031
2032 lv_base_att_type := NULL;
2033 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2034 lv_base_att_type := '1';
2035 END IF;
2036
2037 IF l_get_fund_dtls.fed_fund_code IN ('FWS','SPNSR') THEN
2038 lc_auto_disb.attendance_type_code := NULL;
2039 END IF;
2040
2041 IF (l_get_fund_dtls.fed_fund_code = 'PELL' AND
2042 igf_sl_dl_validation.check_full_participant (l_get_fund_dtls.awd_cal_type,l_get_fund_dtls.awd_sequence_number,'PELL'))
2043 OR (l_get_fund_dtls.fed_fund_code IN ('DLP','DLS','DLU') AND
2044 igf_sl_dl_validation.check_full_participant (l_get_fund_dtls.awd_cal_type,l_get_fund_dtls.awd_sequence_number,'DL'))
2045 THEN
2046 l_hold_ind := 'FALSE';
2047 END IF;
2048
2049 -- museshad (Bug# 4608591)
2050 OPEN c_disb(cp_award_id => p_award_id, cp_disb_num => ln_count);
2051 FETCH c_disb INTO l_disb;
2052
2053 IF isRepackaging(p_award_id => p_award_id) AND (c_disb%FOUND) THEN
2054 -- If you reach here, it means this existing award already has this
2055 -- disbursement. So, update it.
2056
2057 /*
2058 This Repackaging holds good (and gets executed) only when an
2059 existing CANCELLED award is reinstated to OFFERED/ACCEPTED status.
2060 */
2061
2062 -- Log
2063 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2064 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','Updating existing disbursement (Repackaging)');
2065 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','award_id:'||p_award_id);
2066 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','disb_num:'||ln_count);
2067 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','disb_gross_amt:'||ln_disb_gross_amt);
2068 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ln_disb_net_amt:'||ln_disb_net_amt);
2069 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_award_status: ' || p_award_status);
2070 END IF;
2071
2072 l_hold_ind := NVL(l_disb.hold_rel_ind, 'FALSE');
2073
2074 igf_aw_awd_disb_pkg.update_row (
2075 x_mode => 'R',
2076 x_rowid => l_disb.row_id,
2077 x_award_id => p_award_id,
2078 x_disb_num => ln_count,
2079 x_tp_cal_type => lc_auto_disb.tp_cal_type,
2080 x_tp_sequence_number => lc_auto_disb.tp_sequence_number,
2081 x_disb_gross_amt => ln_disb_gross_amt,
2082 x_fee_1 => ln_fee_1,
2083 x_fee_2 => ln_fee_2,
2084 x_disb_net_amt => ln_disb_net_amt,
2085 x_disb_date => lc_auto_disb.disb_dt,
2086 x_trans_type => 'P',
2087 x_elig_status => 'N',
2088 x_elig_status_date => TRUNC(SYSDATE),
2089 x_affirm_flag => l_disb.affirm_flag,
2090 x_hold_rel_ind => l_hold_ind,
2091 x_manual_hold_ind => 'N',
2092 x_disb_status => l_disb.disb_status,
2093 x_disb_status_date => l_disb.disb_status_date,
2094 x_late_disb_ind => l_auto_ind,
2095 x_fund_dist_mthd => l_disb.fund_dist_mthd,
2096 x_prev_reported_ind => l_disb.prev_reported_ind,
2097 x_fund_release_date => l_disb.fund_release_date,
2098 x_fund_status => l_disb.fund_status,
2099 x_fund_status_date => l_disb.fund_status_date,
2100 x_fee_paid_1 => 0,
2101 x_fee_paid_2 => 0,
2102 x_cheque_number => l_disb.cheque_number,
2103 x_ld_cal_type => lc_auto_disb.ld_cal_type,
2104 x_ld_sequence_number => lc_auto_disb.ld_sequence_number,
2105 x_disb_accepted_amt => ln_disb_accepted_amt,
2106 x_disb_paid_amt => 0,
2107 x_rvsn_id => l_disb.rvsn_id,
2108 x_int_rebate_amt => ln_int_rebate_amt,
2109 x_force_disb => 'N',
2110 x_min_credit_pts => lc_auto_disb.min_credit_points,
2111 x_disb_exp_dt => ld_disb_exp_dt,
2112 x_verf_enfr_dt => ld_verf_enfr_dt,
2113 x_fee_class => l_disb.fee_class,
2114 x_show_on_bill => l_get_fund_dtls.show_on_bill,
2115 x_attendance_type_code => lc_auto_disb.attendance_type_code,
2116 x_base_attendance_type_code => lv_base_att_type,
2117 x_payment_prd_st_date => l_disb.payment_prd_st_date,
2118 x_change_type_code => l_disb.change_type_code,
2119 x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
2120 x_direct_to_borr_flag => l_disb.direct_to_borr_flag
2121 );
2122 ELSE
2123 -- If you reach here, it means this is either - an existing award without this
2124 -- disbursement (or) it is a new award whose disbursement needs to be
2125 -- created. So, create the disbursement
2126
2127 -- Log
2128 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2129 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','Creating new disbursement (Packaging)');
2130 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','award_id:'||p_award_id);
2131 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','disb_num:'||ln_count);
2132 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','disb_gross_amt:'||ln_disb_gross_amt);
2133 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','ln_disb_net_amt:'||ln_disb_net_amt);
2134 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','p_award_status: ' || p_award_status);
2135 END IF;
2136
2137 igf_aw_awd_disb_pkg.insert_row (
2138 x_mode => 'R',
2139 x_rowid => lv_row_id,
2140 x_award_id => p_award_id,
2141 x_disb_num => ln_count,
2142 x_tp_cal_type => lc_auto_disb.tp_cal_type,
2143 x_tp_sequence_number => lc_auto_disb.tp_sequence_number,
2144 x_disb_gross_amt => ln_disb_gross_amt,
2145 x_fee_1 => ln_fee_1,
2146 x_fee_2 => ln_fee_2,
2147 x_disb_net_amt => ln_disb_net_amt,
2148 x_disb_date => lc_auto_disb.disb_dt,
2149 x_trans_type => 'P',
2150 x_elig_status => 'N',
2151 x_elig_status_date => TRUNC(SYSDATE),
2152 x_affirm_flag => NULL,
2153 x_hold_rel_ind => l_hold_ind,
2154 x_manual_hold_ind => 'N',
2155 x_disb_status => NULL,
2156 x_disb_status_date => NULL,
2157 x_late_disb_ind => l_auto_ind,
2158 x_fund_dist_mthd => NULL,
2159 x_prev_reported_ind => NULL,
2160 x_fund_release_date => NULL,
2161 x_fund_status => NULL,
2162 x_fund_status_date => NULL,
2163 x_fee_paid_1 => 0,
2164 x_fee_paid_2 => 0,
2165 x_cheque_number => NULL,
2166 x_ld_cal_type => lc_auto_disb.ld_cal_type,
2167 x_ld_sequence_number => lc_auto_disb.ld_sequence_number,
2168 x_disb_accepted_amt => ln_disb_accepted_amt,
2169 x_disb_paid_amt => 0,
2170 x_rvsn_id => NULL,
2171 x_int_rebate_amt => ln_int_rebate_amt,
2172 x_force_disb => 'N',
2173 x_min_credit_pts => lc_auto_disb.min_credit_points,
2174 x_disb_exp_dt => ld_disb_exp_dt,
2175 x_verf_enfr_dt => ld_verf_enfr_dt,
2176 x_fee_class => NULL,
2177 x_show_on_bill => l_get_fund_dtls.show_on_bill,
2178 x_attendance_type_code => lc_auto_disb.attendance_type_code,
2179 x_base_attendance_type_code => lv_base_att_type,
2180 x_payment_prd_st_date => l_disb.payment_prd_st_date,
2181 x_change_type_code => NULL,
2182 x_fund_return_mthd_code => NULL,
2183 x_direct_to_borr_flag => 'N'
2184 );
2185 END IF;
2186 CLOSE c_disb;
2187
2188 IF p_method_code = 'M' THEN /*Manual distribution */
2189 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2190 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','-----fetching next disbursment-----');
2191 END IF;
2192 IF lb_coa_exist THEN
2193 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2194 FETCH c_auto_disb_pell INTO lc_auto_disb;
2195 EXIT WHEN c_auto_disb_pell%NOTFOUND;
2196 ELSE
2197 FETCH c_auto_disb INTO lc_auto_disb;
2198 EXIT WHEN c_auto_disb%NOTFOUND;
2199 END IF;
2200 ELSE
2201 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2202 FETCH c_auto_disb_wcoa_pell INTO lc_auto_disb;
2203 EXIT WHEN c_auto_disb_wcoa_pell%NOTFOUND;
2204 ELSE
2205 FETCH c_auto_disb_wcoa INTO lc_auto_disb;
2206 EXIT WHEN c_auto_disb_wcoa%NOTFOUND;
2207 END IF;
2208 END IF;
2209
2210 ELSIF p_method_code = 'E' THEN /* Equal Distribution */
2211 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2212 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','-----fetching next disbursment-----');
2213 END IF;
2214 IF lb_coa_exist THEN
2215 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2216 FETCH c_auto_disb_equal_pell INTO lc_auto_disb;
2217 EXIT WHEN c_auto_disb_equal_pell%NOTFOUND;
2218 ELSE
2219 FETCH c_auto_disb_equal INTO lc_auto_disb;
2220 EXIT WHEN c_auto_disb_equal%NOTFOUND;
2221 END IF;
2222 ELSE
2223 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2224 FETCH c_auto_disb_equal_wcoa_pell INTO lc_auto_disb;
2225 EXIT WHEN c_auto_disb_equal_wcoa_pell%NOTFOUND;
2226 ELSE
2227 FETCH c_auto_disb_equal_wcoa INTO lc_auto_disb;
2228 EXIT WHEN c_auto_disb_equal_wcoa%NOTFOUND;
2229 END IF;
2230 END IF;
2231
2232 ELSIF p_method_code = 'C' THEN /* Match COA distribution */
2233 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2234 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','-----fetching next disbursment-----');
2235 END IF;
2236 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2237 FETCH c_auto_disb_coa_match_pell INTO lc_auto_disb;
2238 EXIT WHEN c_auto_disb_coa_match_pell%NOTFOUND;
2239 ELSE
2240 FETCH c_auto_disb_coa_match INTO lc_auto_disb;
2241 EXIT WHEN c_auto_disb_coa_match%NOTFOUND;
2242 END IF;
2243
2244 END IF;/* end method check*/
2245
2246 END LOOP;
2247
2248 IF p_method_code = 'M' THEN /*Manual distribution */
2249 IF lb_coa_exist THEN
2250 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2251 CLOSE c_auto_disb_pell;
2252 ELSE
2253 CLOSE c_auto_disb;
2254 END IF;
2255 ELSE
2256 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2257 CLOSE c_auto_disb_wcoa_pell;
2258 ELSE
2259 CLOSE c_auto_disb_wcoa;
2260 END IF;
2261 END IF;
2262
2263 ELSIF p_method_code = 'E' THEN /* Equal Distribution */
2264 IF lb_coa_exist THEN
2265 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2266 CLOSE c_auto_disb_equal_pell;
2267 ELSE
2268 CLOSE c_auto_disb_equal;
2269 END IF;
2270 ELSE
2271 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2272 CLOSE c_auto_disb_equal_wcoa_pell;
2273 ELSE
2274 CLOSE c_auto_disb_equal_wcoa;
2275 END IF;
2276 END IF;
2277
2278 ELSIF p_method_code = 'C' THEN /* Match COA distribution */
2279 IF l_get_fund_dtls.fed_fund_code = 'PELL' THEN
2280 CLOSE c_auto_disb_coa_match_pell;
2281 ELSE
2282 CLOSE c_auto_disb_coa_match;
2283 END IF;
2284 END IF;/* end method check*/
2285
2286 --
2287 -- Add To Do Items to student which are defined at the fund level
2288 --
2289
2290 igf_aw_packaging.add_todo(p_fund_id,get_base_rec.base_id);
2291
2292 -- Apply NSLDS offset to the first disbursment
2293 IF NOT lb_nslds_ind THEN
2294 OPEN c_disb(p_award_id,1);
2295 FETCH c_disb INTO l_disb;
2296 CLOSE c_disb;
2297
2298 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2299 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.create_auto_disb.debug','Flag set - updating disb num 1 with NSLDS disb offset date: ' ||ld_disb_date1);
2300 END IF;
2301
2302 igf_aw_awd_disb_pkg.update_row(
2303 x_mode => 'R',
2304 x_rowid => l_disb.row_id,
2305 x_award_id => l_disb.award_id,
2306 x_disb_num => l_disb.disb_num,
2307 x_tp_cal_type => l_disb.tp_cal_type,
2308 x_tp_sequence_number => l_disb.tp_sequence_number,
2309 x_disb_gross_amt => l_disb.disb_gross_amt,
2310 x_fee_1 => l_disb.fee_1,
2311 x_fee_2 => l_disb.fee_2,
2312 x_disb_net_amt => l_disb.disb_net_amt,
2313 x_disb_date => ld_disb_date1,
2314 x_trans_type => l_disb.trans_type,
2315 x_elig_status => l_disb.elig_status,
2316 x_elig_status_date => l_disb.elig_status_date,
2317 x_affirm_flag => l_disb.affirm_flag,
2318 x_hold_rel_ind => l_disb.hold_rel_ind,
2319 x_manual_hold_ind => l_disb.manual_hold_ind,
2320 x_disb_status => l_disb.disb_status,
2321 x_disb_status_date => l_disb.disb_status_date,
2322 x_late_disb_ind => l_disb.late_disb_ind,
2323 x_fund_dist_mthd => l_disb.fund_dist_mthd,
2324 x_prev_reported_ind => l_disb.prev_reported_ind ,
2325 x_fund_release_date => l_disb.fund_release_date,
2326 x_fund_status => l_disb.fund_status,
2327 x_fund_status_date => l_disb.fund_status_date,
2328 x_fee_paid_1 => l_disb.fee_paid_1,
2329 x_fee_paid_2 => l_disb.fee_paid_2,
2330 x_cheque_number => l_disb.cheque_number,
2331 x_ld_cal_type => l_disb.ld_cal_type,
2332 x_ld_sequence_number => l_disb.ld_sequence_number,
2333 x_disb_accepted_amt => l_disb.disb_accepted_amt,
2334 x_disb_paid_amt => l_disb.disb_paid_amt,
2335 x_rvsn_id => l_disb.rvsn_id,
2336 x_int_rebate_amt => l_disb.int_rebate_amt,
2337 x_force_disb => l_disb.force_disb,
2338 x_min_credit_pts => l_disb.min_credit_pts,
2339 x_disb_exp_dt => l_disb.disb_exp_dt,
2340 x_verf_enfr_dt => l_disb.verf_enfr_dt,
2341 x_fee_class => l_disb.fee_class,
2342 x_show_on_bill => l_disb.show_on_bill,
2343 x_attendance_type_code => l_disb.attendance_type_code,
2344 x_base_attendance_type_code => l_disb.base_attendance_type_code,
2345 x_payment_prd_st_date => l_disb.payment_prd_st_date,
2346 x_change_type_code => l_disb.change_type_code,
2347 x_fund_return_mthd_code => l_disb.fund_return_mthd_code,
2348 x_direct_to_borr_flag => l_disb.direct_to_borr_flag
2349 );
2350
2351 END IF;
2352
2353 -- museshad (Bug# 4608591)
2354 -- While repackaging any extra disbursements present in the
2355 -- award needs to be cancelled
2356 IF isRepackaging(p_award_id => p_award_id) THEN
2357 cancel_extra_disb (
2358 p_award_id => p_award_id,
2359 p_disb_num => ln_count
2360 );
2361 END IF;
2362 -- museshad (Bug# 4608591)
2363
2364 -- bvisvana - FA 157 - To enable rounding options at the time of award creation
2365 -- museshad (12-Sep-2005) - Pell disbursement rounding is handled separately in IGFGR11B
2366 IF get_fed_fund_code(p_fund_id => p_fund_id) <> 'PELL' THEN
2367 round_off_disbursements ( p_fund_id => p_fund_id,
2368 p_award_id => p_award_id,
2369 p_offered_amt => p_offered_amt,
2370 p_award_status => p_award_status,
2371 p_dist_plan_code => p_method_code ,
2372 p_disb_count => ln_count
2373 );
2374 END IF;
2375
2376 EXCEPTION
2377
2378 WHEN NO_COMMON_TERMS THEN
2379 fnd_message.set_name('IGF','IGF_AW_COA_COMMON_TERMS_FAIL');
2380 fnd_message.set_token('PLAN_CD',l_adplans_name);
2381 fnd_file.put_line(fnd_file.log,fnd_message.get);
2382 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2383 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_gen_003.create_auto_disb.exception','no common terms between COA and distribution plan');
2384 END IF;
2385
2386 WHEN NO_AP_DP_COMM_TERMS THEN
2387 fnd_message.set_name('IGF','IGF_AW_NO_APDP_COM_TERM');
2388 app_exception.raise_exception;
2389
2390 WHEN others THEN
2391 IF c_auto_disb%ISOPEN THEN
2392 CLOSE c_auto_disb;
2393 END IF;
2394 IF c_auto_disb_wcoa%ISOPEN THEN
2395 CLOSE c_auto_disb_wcoa;
2396 END IF;
2397 IF c_auto_disb_equal%ISOPEN THEN
2398 CLOSE c_auto_disb_equal;
2399 END IF;
2400 IF c_auto_disb_equal_wcoa%ISOPEN THEN
2401 CLOSE c_auto_disb_equal_wcoa;
2402 END IF;
2403 IF c_auto_disb_coa_match%ISOPEN THEN
2404 CLOSE c_auto_disb_coa_match;
2405 END IF;
2406
2407 IF c_auto_disb_pell%ISOPEN THEN
2408 CLOSE c_auto_disb_pell;
2409 END IF;
2410 IF c_auto_disb_wcoa_pell%ISOPEN THEN
2411 CLOSE c_auto_disb_wcoa_pell;
2412 END IF;
2413 IF c_auto_disb_equal_pell%ISOPEN THEN
2414 CLOSE c_auto_disb_equal_pell;
2415 END IF;
2416 IF c_auto_disb_equal_wcoa_pell%ISOPEN THEN
2417 CLOSE c_auto_disb_equal_wcoa_pell;
2418 END IF;
2419 IF c_auto_disb_coa_match_pell%ISOPEN THEN
2420 CLOSE c_auto_disb_coa_match_pell;
2421 END IF;
2422
2423 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2424 fnd_message.set_token('NAME','IGF_AW_GEN_003.CREATE_AUTO_DISB' || ' ' || SQLERRM);
2425 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2426 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_gen_003.create_auto_disb.exception','sql error message:'|| SQLERRM);
2427 END IF;
2428 app_exception.raise_exception;
2429
2430 END create_auto_disb;
2431
2432
2433 PROCEDURE remove_awd_rules_override(p_award_id IN igf_aw_award.award_id%TYPE)
2434 AS
2435 /*
2436 || Created By : brajendr
2437 || Created On :
2438 || Purpose : Fuction to remove the rules override check on the award
2439 || This function checks for over awaqrd holds on all the disbursements, if no
2440 || holds on the disbursements then updates the rules override to 'N' for award.
2441 || Parameter : Award_Id - Incates the Award ID for which override rules need to be removed.
2442 || Known limitations, enhancements or remarks :
2443 || Change History :
2444 || Who When What
2445 || (reverse chronological order - newest change first)
2446 */
2447
2448 --
2449 -- Get the details of Disbursement Holds for a given award if any holds exists
2450 --
2451 CURSOR c_chk_sys_disb_holds(p_award_id igf_aw_award.award_id%TYPE)
2452 IS
2453 SELECT 'x'
2454 FROM igf_db_disb_holds dh
2455 WHERE dh.award_id = p_award_id
2456 AND dh.hold_type = 'SYSTEM'
2457 AND dh.hold = 'OVERAWARD'
2458 AND dh.release_flag = 'N';
2459
2460 --
2461 -- Get the details of the award for updating the override rules.
2462 --
2463 CURSOR c_get_awd_dtls(p_award_id igf_aw_award.award_id%TYPE)
2464 IS
2465 SELECT awd.*
2466 FROM igf_aw_award awd
2467 WHERE awd.award_id = p_award_id;
2468
2469 lc_get_awd_dtls c_get_awd_dtls%ROWTYPE;
2470 lc_chk_sys_disb_holds c_chk_sys_disb_holds%ROWTYPE;
2471
2472 BEGIN
2473
2474 OPEN c_chk_sys_disb_holds( p_award_id);
2475 FETCH c_chk_sys_disb_holds INTO lc_chk_sys_disb_holds;
2476
2477 -- Check whether the Holds are present if not present then remove the rules override at award level
2478 IF c_chk_sys_disb_holds%NOTFOUND THEN
2479
2480 -- Fetch the details for the award and update the rules override with 'N'
2481 OPEN c_get_awd_dtls( p_award_id);
2482 FETCH c_get_awd_dtls INTO lc_get_awd_dtls;
2483 CLOSE c_get_awd_dtls;
2484
2485 igf_aw_award_pkg.update_row(
2486 x_mode => 'R',
2487 x_rowid => lc_get_awd_dtls.row_id,
2488 x_award_id => lc_get_awd_dtls.award_id,
2489 x_fund_id => lc_get_awd_dtls.fund_id,
2490 x_base_id => lc_get_awd_dtls.base_id,
2491 x_offered_amt => lc_get_awd_dtls.offered_amt,
2492 x_accepted_amt => lc_get_awd_dtls.accepted_amt,
2493 x_paid_amt => lc_get_awd_dtls.paid_amt,
2494 x_packaging_type => lc_get_awd_dtls.packaging_type,
2495 x_batch_id => lc_get_awd_dtls.batch_id,
2496 x_manual_update => lc_get_awd_dtls.manual_update,
2497 x_rules_override => 'N',
2498 x_award_date => lc_get_awd_dtls.award_date,
2499 x_award_status => lc_get_awd_dtls.award_status,
2500 x_attribute_category => lc_get_awd_dtls.attribute_category,
2501 x_attribute1 => lc_get_awd_dtls.attribute1,
2502 x_attribute2 => lc_get_awd_dtls.attribute2,
2503 x_attribute3 => lc_get_awd_dtls.attribute3,
2504 x_attribute4 => lc_get_awd_dtls.attribute4,
2505 x_attribute5 => lc_get_awd_dtls.attribute5,
2506 x_attribute6 => lc_get_awd_dtls.attribute6,
2507 x_attribute7 => lc_get_awd_dtls.attribute7,
2508 x_attribute8 => lc_get_awd_dtls.attribute8,
2509 x_attribute9 => lc_get_awd_dtls.attribute9,
2510 x_attribute10 => lc_get_awd_dtls.attribute10,
2511 x_attribute11 => lc_get_awd_dtls.attribute11,
2512 x_attribute12 => lc_get_awd_dtls.attribute12,
2513 x_attribute13 => lc_get_awd_dtls.attribute13,
2514 x_attribute14 => lc_get_awd_dtls.attribute14,
2515 x_attribute15 => lc_get_awd_dtls.attribute15,
2516 x_attribute16 => lc_get_awd_dtls.attribute16,
2517 x_attribute17 => lc_get_awd_dtls.attribute17,
2518 x_attribute18 => lc_get_awd_dtls.attribute18,
2519 x_attribute19 => lc_get_awd_dtls.attribute19,
2520 x_attribute20 => lc_get_awd_dtls.attribute20,
2521 x_rvsn_id => lc_get_awd_dtls.rvsn_id,
2522 x_award_number_txt => lc_get_awd_dtls.award_number_txt,
2523 x_legacy_record_flag => NULL,
2524 x_adplans_id => lc_get_awd_dtls.adplans_id,
2525 x_lock_award_flag => lc_get_awd_dtls.lock_award_flag,
2526 x_app_trans_num_txt => lc_get_awd_dtls.app_trans_num_txt,
2527 x_awd_proc_status_code => lc_get_awd_dtls.awd_proc_status_code,
2528 x_notification_status_code => lc_get_awd_dtls.notification_status_code,
2529 x_notification_status_date => lc_get_awd_dtls.notification_status_date,
2530 x_publish_in_ss_flag => lc_get_awd_dtls.publish_in_ss_flag
2531 );
2532
2533 END IF;
2534 CLOSE c_chk_sys_disb_holds;
2535
2536 EXCEPTION
2537 WHEN OTHERS THEN
2538 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2539 fnd_message.set_token('NAME','IGF_AW_GEN_003.REMOVE_AWD_RULES_OVERRIDE'||' ' ||SQLERRM);
2540 app_exception.raise_exception;
2541
2542 END remove_awd_rules_override;
2543
2544 FUNCTION place_ovawd_holds (p_award_id igf_aw_award.award_id%TYPE,
2545 p_disb_num igf_aw_awd_disb.disb_num%TYPE)
2546
2547 RETURN BOOLEAN
2548 IS
2549
2550 --
2551 -- Get the details of disbursement hold for the given disbursement if present
2552 -- If this function returns TRUE, it means new overaward hold
2553 -- can be placed
2554 --
2555 CURSOR cur_chk_holds( p_award_id igf_aw_award.award_id%TYPE,
2556 p_disb_num igf_aw_awd_disb.disb_num%TYPE) IS
2557 SELECT dh.release_flag
2558 FROM igf_db_disb_holds dh
2559 WHERE dh.award_id = p_award_id
2560 AND dh.disb_num = p_disb_num
2561 AND dh.hold_type = 'SYSTEM'
2562 AND dh.hold = 'OVERAWARD';
2563
2564 rec_chk_holds cur_chk_holds%ROWTYPE;
2565
2566 BEGIN
2567
2568 OPEN cur_chk_holds(p_award_id,p_disb_num);
2569 FETCH cur_chk_holds INTO rec_chk_holds;
2570
2571 IF cur_chk_holds%NOTFOUND THEN
2572 CLOSE cur_chk_holds;
2573 RETURN TRUE;
2574 ELSIF cur_chk_holds%FOUND THEN
2575 IF NVL(rec_chk_holds.release_flag,'N') = 'Y' THEN
2576 CLOSE cur_chk_holds;
2577 RETURN TRUE;
2578 ELSE
2579 CLOSE cur_chk_holds;
2580 RETURN FALSE;
2581 END IF;
2582 ELSE
2583 RETURN FALSE;
2584 END IF;
2585
2586 EXCEPTION
2587
2588 WHEN OTHERS THEN
2589 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2590 fnd_message.set_token('NAME','IGF_AW_GEN_003.PLACE_OVAWD_HOLDS'||' ' ||SQLERRM);
2591 app_exception.raise_exception;
2592
2593 END place_ovawd_holds;
2594
2595
2596 PROCEDURE create_over_awd_holds(
2597 p_award_id IN igf_aw_award.award_id%TYPE
2598 ) AS
2599 /*
2600 || Created By : brajendr
2601 || Created On :
2602 || Purpose : This function creates the disbursement holds if any over award amount.
2603 || For over awards, function checks for existing holds, and then create
2604 || disbursement holds if holds are not present.
2605 || Parameter : Award_Id - Incates the Award ID for which all the disbursements should be put on hold
2606 || Known limitations, enhancements or remarks :
2607 || Change History :
2608 || Who When What
2609 || (reverse chronological order - newest change first)
2610 */
2611
2612 -- Get the details of all planned disbursements of the given award.
2613 CURSOR c_get_planned_awd_disb(
2614 p_award_id igf_aw_award.award_id%TYPE
2615 ) IS
2616 SELECT disb.award_id, disb.disb_num
2617 FROM igf_aw_awd_disb disb
2618 WHERE disb.award_id = p_award_id
2619 AND disb.trans_type = 'P';
2620
2621 lc_row_id VARCHAR2(30);
2622 ln_hold_id igf_db_disb_holds.hold_id%TYPE;
2623
2624
2625 l_app VARCHAR2(50);
2626 l_name VARCHAR2(30);
2627
2628 BEGIN
2629
2630 -- Get all the Planned disbursements for the given award.
2631 FOR rec_c_get_planned_awd_disb IN c_get_planned_awd_disb( p_award_id) LOOP
2632
2633 BEGIN
2634
2635 -- Check whether the over award system is already pleased for the each disbursement.
2636
2637 IF place_ovawd_holds (p_award_id,rec_c_get_planned_awd_disb.disb_num) THEN
2638
2639 -- If Hold is not present, then create the hold on the disbursement.
2640 igf_db_disb_holds_pkg.insert_row(
2641 x_mode => 'R',
2642 x_rowid => lc_row_id,
2643 x_hold_id => ln_hold_id,
2644 x_award_id => rec_c_get_planned_awd_disb.award_id,
2645 x_disb_num => rec_c_get_planned_awd_disb.disb_num,
2646 x_hold => 'OVERAWARD',
2647 x_hold_date => TRUNC(sysdate),
2648 x_hold_type => 'SYSTEM',
2649 x_release_date => NULL,
2650 x_release_flag => 'N',
2651 x_release_reason => NULL
2652 );
2653 END IF;
2654
2655
2656 EXCEPTION
2657
2658 WHEN others THEN
2659 --
2660 -- This will ensure exception raised from the insert hold tbh
2661 -- are is not thrown in the form
2662 --
2663 fnd_message.parse_encoded(fnd_message.get_encoded, l_app, l_name);
2664 IF l_name = 'IGF_DB_HOLD_EXISTS' THEN
2665 NULL;
2666 ELSE
2667 RAISE;
2668 END IF;
2669 END;
2670 END LOOP; -- Planned disbursements loop
2671
2672 COMMIT;
2673
2674 EXCEPTION
2675
2676 WHEN OTHERS THEN
2677 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2678 fnd_message.set_token('NAME','IGF_AW_GEN_003.CREATE_OVER_AWD_HOLDS'||' ' ||SQLERRM);
2679 app_exception.raise_exception;
2680
2681 END create_over_awd_holds;
2682
2683 PROCEDURE update_accept_amount (p_award_id IN igf_aw_award.award_id%TYPE )
2684 IS
2685
2686 /*
2687 -----------------------------------------------------------------------------
2688 --
2689 -- adhawan, May 12th 2002
2690 --This procedure ensures that whenever the Award Status is changed to Accepted from Offered
2691 --and the Accepted amount is null or Zero then updation of the accepted amounts should take
2692 -- place with the offered amounts to the Disbursement table.
2693 -- who when what
2694 -- adhawan 12-May-2002 Added this procedure
2695 -- Bug ID : 2332588
2696 -----------------------------------------------------------------------------
2697 */
2698
2699
2700 CURSOR c_accept_null IS
2701 SELECT disb.*,disb.rowid row_id
2702 FROM igf_aw_awd_disb_all disb
2703 WHERE disb.award_id = p_award_id
2704 AND disb.trans_type <> 'C'
2705 AND NVL(disb.disb_accepted_amt,0) = 0
2706 FOR UPDATE OF disb.disb_gross_amt NOWAIT;
2707
2708 --
2709 -- This would select all the disb records which have accepted amount as null or zero
2710 --
2711
2712 BEGIN
2713
2714 FOR c_null_rec IN c_accept_null LOOP
2715
2716 --
2717 -- As accepted amount made equal to Offered amount,
2718 -- net amount is based on accepted amt
2719 --
2720 c_null_rec.disb_net_amt := c_null_rec.disb_gross_amt -
2721 NVL(c_null_rec.fee_1,0) -
2722 NVL(c_null_rec.fee_2,0) +
2723 NVL(c_null_rec.fee_paid_1,0) +
2724 NVL(c_null_rec.fee_paid_2,0) +
2725 NVL(c_null_rec.int_rebate_amt,0);
2726
2727 igf_aw_awd_disb_pkg.update_row (
2728 x_mode => 'R',
2729 x_rowid => c_null_rec.row_id,
2730 x_award_id => c_null_rec.award_id,
2731 x_disb_num => c_null_rec.disb_num,
2732 x_tp_cal_type => c_null_rec.tp_cal_type,
2733 x_tp_sequence_number => c_null_rec.tp_sequence_number,
2734 x_disb_gross_amt => c_null_rec.disb_gross_amt,
2735 x_fee_1 => c_null_rec.fee_1,
2736 x_fee_2 => c_null_rec.fee_2,
2737 x_disb_net_amt => c_null_rec.disb_net_amt,
2738 x_disb_date => c_null_rec.disb_date,
2739 x_trans_type => c_null_rec.trans_type,
2740 x_elig_status => c_null_rec.elig_status,
2741 x_elig_status_date => c_null_rec.elig_status_date,
2742 x_affirm_flag => c_null_rec.affirm_flag,
2743 x_hold_rel_ind => c_null_rec.hold_rel_ind,
2744 x_manual_hold_ind => c_null_rec.manual_hold_ind,
2745 x_disb_status => c_null_rec.disb_status,
2746 x_disb_status_date => c_null_rec.disb_status_date,
2747 x_late_disb_ind => c_null_rec.late_disb_ind,
2748 x_fund_dist_mthd => c_null_rec.fund_dist_mthd,
2749 x_prev_reported_ind => c_null_rec.prev_reported_ind,
2750 x_fund_release_date => c_null_rec.fund_release_date,
2751 x_fund_status => c_null_rec.fund_status,
2752 x_fund_status_date => c_null_rec.fund_status_date,
2753 x_fee_paid_1 => c_null_rec.fee_paid_1,
2754 x_fee_paid_2 => c_null_rec.fee_paid_2,
2755 x_cheque_number => c_null_rec.cheque_number,
2756 x_ld_cal_type => c_null_rec.ld_cal_type,
2757 x_ld_sequence_number => c_null_rec.ld_sequence_number,
2758 x_disb_accepted_amt => c_null_rec.disb_gross_amt,--Accepted amount made equal to Offered amount
2759 x_disb_paid_amt => c_null_rec.disb_paid_amt,
2760 x_rvsn_id => c_null_rec.rvsn_id,
2761 x_int_rebate_amt => c_null_rec.int_rebate_amt,
2762 x_force_disb => c_null_rec.force_disb,
2763 x_min_credit_pts => c_null_rec.min_credit_pts,
2764 x_disb_exp_dt => c_null_rec.disb_exp_dt,
2765 x_verf_enfr_dt => c_null_rec.verf_enfr_dt,
2766 x_fee_class => c_null_rec.fee_class,
2767 x_show_on_bill => c_null_rec.show_on_bill,
2768 x_attendance_type_code => c_null_rec.attendance_type_code,
2769 x_base_attendance_type_code => c_null_rec.base_attendance_type_code,
2770 x_payment_prd_st_date => c_null_rec.payment_prd_st_date,
2771 x_change_type_code => c_null_rec.change_type_code,
2772 x_fund_return_mthd_code => c_null_rec.fund_return_mthd_code,
2773 x_direct_to_borr_flag => c_null_rec.direct_to_borr_flag
2774 );
2775
2776
2777 END LOOP;
2778
2779 EXCEPTION
2780
2781 WHEN app_exception.record_lock_exception THEN
2782 ROLLBACK;
2783
2784 WHEN OTHERS THEN
2785 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2786 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_ACCEPT_AMOUNT'||' ' ||SQLERRM);
2787 app_exception.raise_exception;
2788
2789 END update_accept_amount;
2790
2791 PROCEDURE update_awd_cancell_to_offer(p_award_id IN igf_aw_award.award_id%TYPE,
2792 p_award_stat IN VARCHAR2,
2793 p_fed_fund_code IN VARCHAR2,
2794 p_base_id IN NUMBER,
2795 p_message OUT NOCOPY VARCHAR2)
2796 IS
2797 /*-----------------------------------------------------------------------------
2798 --
2799 -- adhawan, May 12th 2002
2800 --This procedure ensures that whenever the Award Status is changed to Accepted OR Offered
2801 -- from Cancelled or Declined the Transaction type , eligibility status , elig date should get updated
2802 -- who when what
2803 --smadathi 24-NOV-2004 Enh. Bug 3416936. Modified the update_row call to
2804 -- igf_aw_awd_disb table
2805 --adhawan 24-May-2002 Added this procedure
2806 --Bug ID : 2375571
2807 -----------------------------------------------------------------------------*/
2808
2809
2810 CURSOR cur_active_isir(
2811 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
2812 ) IS
2813 SELECT transaction_num
2814 FROM igf_ap_isir_matched_all
2815 WHERE base_id = cp_base_id
2816 AND NVL(active_isir,'N') = 'Y';
2817
2818 active_isir_rec cur_active_isir%ROWTYPE;
2819
2820 --
2821 -- Cursor to get term totals for a disbursement
2822 --
2823 CURSOR cur_term_amounts (p_award_id NUMBER)
2824 IS
2825 SELECT disb.ld_cal_type,
2826 disb.ld_sequence_number,
2827 disb.base_attendance_type_code,
2828 SUM(disb.disb_gross_amt) term_total
2829 FROM igf_aw_awd_disb_all disb,
2830 igf_aw_award_all awd
2831 WHERE awd.award_id = disb.award_id
2832 AND awd.award_id = p_award_id
2833 GROUP BY disb.ld_cal_type,disb.ld_sequence_number,disb.base_attendance_type_code;
2834
2835 term_amounts_rec cur_term_amounts%ROWTYPE;
2836
2837 CURSOR c_change_trans IS
2838 SELECT disb.*,disb.rowid row_id
2839 FROM igf_aw_awd_disb_all disb
2840 WHERE disb.award_id = p_award_id
2841 AND disb.trans_type = 'C'
2842 FOR UPDATE OF disb.disb_gross_amt NOWAIT;
2843
2844 p_term_aid NUMBER;
2845 p_return_status VARCHAR2(30);
2846 lv_pell_mat VARCHAR2(30);
2847
2848 BEGIN
2849
2850 IF p_fed_fund_code = 'PELL' THEN
2851 IF igf_aw_gen_003.check_coa(p_base_id) THEN
2852
2853 OPEN cur_active_isir(p_base_id);
2854 FETCH cur_active_isir INTO active_isir_rec;
2855 IF cur_active_isir%FOUND THEN
2856 CLOSE cur_active_isir;
2857 --
2858 -- FA 131 Check
2859 -- Check if the amount is less, raise error
2860 --
2861 FOR term_amounts_rec IN cur_term_amounts(p_award_id)
2862 LOOP
2863 p_message := NULL;
2864 igf_gr_pell_calc.calc_term_pell(p_base_id,
2865 term_amounts_rec.base_attendance_type_code,
2866 term_amounts_rec.ld_cal_type,term_amounts_rec.ld_sequence_number,
2867 p_term_aid,
2868 p_return_status,
2869 p_message,
2870 'IGFGR005',
2871 lv_pell_mat);
2872
2873 IF NVL(p_return_status,'N') = 'E' THEN
2874 RETURN;
2875 ELSIF NVL(p_term_aid,0) < term_amounts_rec.term_total THEN
2876 fnd_message.set_name('IGF','IGF_AW_PELL_DISB_ERR');
2877 fnd_message.set_token('LD_ALT_CODE',igf_gr_gen.get_alt_code(term_amounts_rec.ld_cal_type,term_amounts_rec.ld_sequence_number));
2878 fnd_message.set_token('ATT_TYPE',igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT',term_amounts_rec.base_attendance_type_code));
2879 fnd_message.set_token('TERM_TOTAL',term_amounts_rec.term_total);
2880 fnd_message.set_token('CALC_AMT',p_term_aid);
2881 p_message := fnd_message.get;
2882 RETURN;
2883 END IF;
2884 END LOOP;
2885
2886 ELSE
2887 CLOSE cur_active_isir;
2888 END IF;
2889 END IF;
2890 END IF;
2891
2892 IF p_award_stat ='CDA' THEN
2893 FOR c_change_trans_rec IN c_change_trans LOOP
2894
2895 --
2896 -- As accepted amount made equal to Offered amount,
2897 -- net amount is based on accepted amt
2898 --
2899 c_change_trans_rec.disb_net_amt := c_change_trans_rec.disb_gross_amt -
2900 NVL(c_change_trans_rec.fee_1,0) -
2901 NVL(c_change_trans_rec.fee_2,0) +
2902 NVL(c_change_trans_rec.fee_paid_1,0) +
2903 NVL(c_change_trans_rec.fee_paid_2,0) +
2904 NVL(c_change_trans_rec.int_rebate_amt,0);
2905 -- x_called_from passed to igf_aw_awd_disb_pkg.update_row is hard coded
2906 -- as IGFAW016 as this procedural update_awd_cancell_to_offer call out
2907 -- happens only through IGFAW016 - Student Awards form
2908
2909 igf_aw_awd_disb_pkg.update_row (
2910 x_mode => 'R',
2911 x_rowid => c_change_trans_rec.row_id,
2912 x_award_id => c_change_trans_rec.award_id,
2913 x_disb_num => c_change_trans_rec.disb_num,
2914 x_tp_cal_type => c_change_trans_rec.tp_cal_type,
2915 x_tp_sequence_number => c_change_trans_rec.tp_sequence_number,
2916 x_disb_gross_amt => c_change_trans_rec.disb_gross_amt,
2917 x_fee_1 => c_change_trans_rec.fee_1,
2918 x_fee_2 => c_change_trans_rec.fee_2,
2919 x_disb_net_amt => c_change_trans_rec.disb_net_amt,
2920 x_disb_date => c_change_trans_rec.disb_date,
2921 x_trans_type => 'P',
2922 x_elig_status => 'N',
2923 x_elig_status_date => TRUNC(SYSDATE),
2924 x_affirm_flag => c_change_trans_rec.affirm_flag,
2925 x_hold_rel_ind => c_change_trans_rec.hold_rel_ind,
2926 x_manual_hold_ind => c_change_trans_rec.manual_hold_ind,
2927 x_disb_status => c_change_trans_rec.disb_status,
2928 x_disb_status_date => c_change_trans_rec.disb_status_date,
2929 x_late_disb_ind => c_change_trans_rec.late_disb_ind,
2930 x_fund_dist_mthd => c_change_trans_rec.fund_dist_mthd,
2931 x_prev_reported_ind => c_change_trans_rec.prev_reported_ind,
2932 x_fund_release_date => c_change_trans_rec.fund_release_date,
2933 x_fund_status => c_change_trans_rec.fund_status,
2934 x_fund_status_date => c_change_trans_rec.fund_status_date,
2935 x_fee_paid_1 => c_change_trans_rec.fee_paid_1,
2936 x_fee_paid_2 => c_change_trans_rec.fee_paid_2,
2937 x_cheque_number => c_change_trans_rec.cheque_number,
2938 x_ld_cal_type => c_change_trans_rec.ld_cal_type,
2939 x_ld_sequence_number => c_change_trans_rec.ld_sequence_number,
2940 x_disb_accepted_amt => c_change_trans_rec.disb_gross_amt,--Accepted amount made equal to Offered amount
2941 x_disb_paid_amt => c_change_trans_rec.disb_paid_amt,
2942 x_rvsn_id => c_change_trans_rec.rvsn_id,
2943 x_int_rebate_amt => c_change_trans_rec.int_rebate_amt,
2944 x_force_disb => c_change_trans_rec.force_disb,
2945 x_min_credit_pts => c_change_trans_rec.min_credit_pts,
2946 x_disb_exp_dt => c_change_trans_rec.disb_exp_dt,
2947 x_verf_enfr_dt => c_change_trans_rec.verf_enfr_dt,
2948 x_fee_class => c_change_trans_rec.fee_class,
2949 x_show_on_bill => c_change_trans_rec.show_on_bill,
2950 x_attendance_type_code => c_change_trans_rec.attendance_type_code,
2951 x_base_attendance_type_code => c_change_trans_rec.base_attendance_type_code,
2952 x_payment_prd_st_date => c_change_trans_rec.payment_prd_st_date,
2953 x_change_type_code => c_change_trans_rec.change_type_code,
2954 x_fund_return_mthd_code => c_change_trans_rec.fund_return_mthd_code,
2955 x_called_from => 'IGFAW016',
2956 x_direct_to_borr_flag => c_change_trans_rec.direct_to_borr_flag
2957 );
2958 END LOOP;
2959 ELSIF p_award_stat ='CDO' THEN
2960 FOR c_change_trans_rec IN c_change_trans LOOP
2961 igf_aw_awd_disb_pkg.update_row (
2962 x_mode => 'R',
2963 x_rowid => c_change_trans_rec.row_id,
2964 x_award_id => c_change_trans_rec.award_id,
2965 x_disb_num => c_change_trans_rec.disb_num,
2966 x_tp_cal_type => c_change_trans_rec.tp_cal_type,
2967 x_tp_sequence_number => c_change_trans_rec.tp_sequence_number,
2968 x_disb_gross_amt => c_change_trans_rec.disb_gross_amt,
2969 x_fee_1 => c_change_trans_rec.fee_1,
2970 x_fee_2 => c_change_trans_rec.fee_2,
2971 x_disb_net_amt => c_change_trans_rec.disb_net_amt,
2972 x_disb_date => c_change_trans_rec.disb_date,
2973 x_trans_type => 'P',
2974 x_elig_status => 'N',
2975 x_elig_status_date => TRUNC(SYSDATE),
2976 x_affirm_flag => c_change_trans_rec.affirm_flag,
2977 x_hold_rel_ind => c_change_trans_rec.hold_rel_ind,
2978 x_manual_hold_ind => c_change_trans_rec.manual_hold_ind,
2979 x_disb_status => c_change_trans_rec.disb_status,
2980 x_disb_status_date => c_change_trans_rec.disb_status_date,
2981 x_late_disb_ind => c_change_trans_rec.late_disb_ind,
2982 x_fund_dist_mthd => c_change_trans_rec.fund_dist_mthd,
2983 x_prev_reported_ind => c_change_trans_rec.prev_reported_ind,
2984 x_fund_release_date => c_change_trans_rec.fund_release_date,
2985 x_fund_status => c_change_trans_rec.fund_status,
2986 x_fund_status_date => c_change_trans_rec.fund_status_date,
2987 x_fee_paid_1 => c_change_trans_rec.fee_paid_1,
2988 x_fee_paid_2 => c_change_trans_rec.fee_paid_2,
2989 x_cheque_number => c_change_trans_rec.cheque_number,
2990 x_ld_cal_type => c_change_trans_rec.ld_cal_type,
2991 x_ld_sequence_number => c_change_trans_rec.ld_sequence_number,
2992 x_disb_accepted_amt => c_change_trans_rec.disb_accepted_amt,
2993 x_disb_paid_amt => c_change_trans_rec.disb_paid_amt,
2994 x_rvsn_id => c_change_trans_rec.rvsn_id,
2995 x_int_rebate_amt => c_change_trans_rec.int_rebate_amt,
2996 x_force_disb => c_change_trans_rec.force_disb,
2997 x_min_credit_pts => c_change_trans_rec.min_credit_pts,
2998 x_disb_exp_dt => c_change_trans_rec.disb_exp_dt,
2999 x_verf_enfr_dt => c_change_trans_rec.verf_enfr_dt,
3000 x_fee_class => c_change_trans_rec.fee_class,
3001 x_show_on_bill => c_change_trans_rec.show_on_bill,
3002 x_attendance_type_code => c_change_trans_rec.attendance_type_code,
3003 x_base_attendance_type_code => c_change_trans_rec.base_attendance_type_code,
3004 x_payment_prd_st_date => c_change_trans_rec.payment_prd_st_date,
3005 x_change_type_code => c_change_trans_rec.change_type_code,
3006 x_fund_return_mthd_code => c_change_trans_rec.fund_return_mthd_code,
3007 x_direct_to_borr_flag => c_change_trans_rec.direct_to_borr_flag
3008 );
3009 END LOOP;
3010 END IF;
3011 EXCEPTION
3012 WHEN app_exception.record_lock_exception THEN
3013 ROLLBACK;
3014
3015 WHEN OTHERS THEN
3016 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3017 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_AWD_CANCELL_TO_OFFER'||' ' || SQLERRM);
3018 app_exception.raise_exception;
3019
3020 END update_awd_cancell_to_offer;
3021
3022
3023 FUNCTION check_disbdts ( p_award_id IN igf_aw_award_all.award_id%TYPE,
3024 p_ld_seq_number IN NUMBER)
3025 RETURN VARCHAR2
3026 IS
3027
3028
3029 --------------------------------------------------------------------------------------
3030 -- sjadhav 18-Feb-2003 Bug 2758823
3031 -- check if disbursement dates are in order with
3032 -- disbursement numbers for Planned and Actual
3033 -- Disbursements
3034 --------------------------------------------------------------------------------------
3035
3036 --mesriniv
3037 --Bug 2394012
3038 --Disbursement message to be modified
3039 --Removed TOKENS for message IGF_DB_DISB_ORDER
3040
3041 -- sjadhav
3042 -- Bug 2387496
3043 -- Added new function to check dates
3044
3045 CURSOR cur_disb_num ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3046 SELECT disb_num,disb_date,trans_type
3047 FROM
3048 igf_aw_awd_disb_all
3049 WHERE
3050 award_id = p_award_id
3051 AND
3052 trans_type IN ('P','A')
3053 ORDER BY
3054 disb_num;
3055
3056 disb_num_rec cur_disb_num%ROWTYPE;
3057
3058 CURSOR cur_disb_dat ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3059 SELECT disb_num,disb_date
3060 FROM
3061 igf_aw_awd_disb
3062 WHERE
3063 award_id = p_award_id
3064 AND
3065 trans_type IN ('P','A')
3066 ORDER BY
3067 disb_date;
3068
3069 disb_dat_rec cur_disb_dat%ROWTYPE;
3070
3071 TYPE disb_record IS RECORD
3072 ( disb_num igf_aw_awd_disb.disb_num%TYPE,
3073 disb_date igf_aw_awd_disb.disb_date%TYPE
3074 );
3075
3076 TYPE disb_num_list IS TABLE OF disb_record INDEX BY BINARY_INTEGER;
3077 disb_num_ele disb_num_list;
3078
3079 TYPE disb_dat_list IS TABLE OF disb_record INDEX BY BINARY_INTEGER;
3080 disb_dat_ele disb_dat_list;
3081
3082 ln_count_i BINARY_INTEGER := 0;
3083 ln_tot_rec NUMBER := 0;
3084 lv_message fnd_new_messages.message_text%TYPE;
3085
3086 BEGIN
3087
3088 lv_message := 'NULL';
3089
3090 IF p_ld_seq_number IS NOT NULL THEN
3091
3092 FOR disb_num_rec IN cur_disb_num (p_award_id)
3093 LOOP
3094 ln_count_i := ln_count_i + 1;
3095 disb_num_ele(ln_count_i).disb_num := disb_num_rec.disb_num;
3096 disb_num_ele(ln_count_i).disb_date := disb_num_rec.disb_date;
3097
3098 END LOOP;
3099
3100 ln_count_i := 0;
3101
3102 FOR disb_dat_rec IN cur_disb_dat ( p_award_id)
3103 LOOP
3104 ln_count_i := ln_count_i + 1;
3105 disb_dat_ele(ln_count_i).disb_num := disb_dat_rec.disb_num;
3106 disb_dat_ele(ln_count_i).disb_date := disb_dat_rec.disb_date;
3107 END LOOP;
3108
3109 ln_tot_rec := ln_count_i;
3110 ln_count_i := 0;
3111
3112 IF ln_tot_rec > 0 THEN
3113
3114 LOOP
3115 ln_count_i := ln_count_i + 1;
3116 EXIT WHEN ln_count_i > ln_tot_rec;
3117 IF disb_num_ele(ln_count_i).disb_num <> disb_dat_ele(ln_count_i).disb_num THEN
3118 fnd_message.set_name('IGF','IGF_DB_DISB_ORDER');
3119
3120 lv_message := fnd_message.get;
3121 RETURN lv_message;
3122 END IF;
3123
3124 END LOOP;
3125
3126 END IF;
3127
3128 END IF;
3129
3130 RETURN lv_message;
3131
3132 EXCEPTION
3133
3134 WHEN OTHERS THEN
3135 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3136 fnd_message.set_token('NAME','IGF_AW_GEN_003.CHECK_DISBDTS'||' ' || SQLERRM);
3137 app_exception.raise_exception;
3138
3139 END check_disbdts;
3140
3141
3142 FUNCTION check_amounts ( p_calling_form IN OUT NOCOPY VARCHAR2,
3143 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
3144 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
3145 p_fund_code IN igf_aw_fund_mast_all.fund_code%TYPE,
3146 p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
3147 p_person_number IN igf_aw_award_v.person_number%TYPE,
3148 p_award_id IN igf_aw_award_all.award_id%TYPE,
3149 p_act_isir IN VARCHAR2,
3150 p_ld_seq_number IN NUMBER,
3151 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE,
3152 p_chk_holds OUT NOCOPY VARCHAR2)
3153 RETURN VARCHAR2
3154 IS
3155
3156 --------------------------------------------------------------------------------------------
3157 -- rajagupt 16-Sep-2005 Bug # 2425618. Changed the if condition to check whether
3158 -- the award amount is exceeding the remaining amount in the fund
3159 -- Added an if condition for over awards for FWS
3160
3161 ---------------------------------------------------------------------------------------------
3162 -- cdcruz 28-Oct-2004 Bug # 3021287
3163 -- p_chk_holds parameter declared as varchar2(1) in the pld
3164 ---------------------------------------------------------------------------------------------
3165 -- cdcruz 28-Oct-2004 Bug # 3021287
3166 -- p_chk_holds parameter changed from boolean to varchar2
3167 -- will return the following values
3168 -- Null -> No Overawd situation
3169 -- 'A' -> Overawd situation at Awd Period
3170 -- 'Y' -> Overawd situation at Awd Yr Level
3171 ---------------------------------------------------------------------------------------------
3172 -- veramach 14-Apr-2004 Bug # 3547237
3173 -- Obsoleted igf_aw_gen_002.get_fed_efc and replaced references
3174 -- with igf_aw_packng_subfns.get_fed_efc
3175 ---------------------------------------------------------------------------------------------
3176 -- bkkumar 14-Jan-04 Bug# 3360702
3177 -- Passed the ln_corrected_amt paramter as 0 to the check_loan_limits and also displayed the
3178 -- error message correctly.
3179 ---------------------------------------------------------------------------------------------
3180 --
3181 -- sjadhav Jan-30-2003 Bug 2776704. Removed emulate_fed check
3182 -- as we are having all funds with fed method
3183 -- this check is removed from check amounts
3184 -- added a cursor to sum up all awards for student
3185 -- to check for overaward
3186 --
3187 ---------------------------------------------------------------------------------------------
3188 --
3189 -- sjadhav
3190 -- Bug 2255279
3191 -- Added procedure check_amounts
3192 ---------------------------------------------------------------------------------------------
3193
3194 --
3195 -- Gets the max amt + max terms the student got a fund in a lifetime
3196 --
3197
3198 CURSOR cur_max_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
3199 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3200 IS
3201 SELECT
3202 NVL(SUM(NVL(disb.disb_gross_amt,0)),0) lf_total,
3203 COUNT(DISTINCT awd.award_id) lf_count
3204 FROM
3205 igf_aw_awd_disb_all disb,
3206 igf_aw_award_all awd,
3207 igf_aw_fund_mast_all fmast,
3208 igf_ap_fa_base_rec_all fabase
3209 WHERE fmast.fund_code = cp_fund_code
3210 AND disb.award_id = awd.award_id
3211 AND awd.fund_id = fmast.fund_id
3212 AND awd.base_id = fabase.base_id
3213 AND fabase.person_id = cp_person_id
3214 AND disb.trans_type <> 'C'
3215 AND awd.award_status IN ('OFFERED', 'ACCEPTED');
3216
3217 max_lf_count_rec cur_max_lf_count%ROWTYPE;
3218
3219 --
3220 -- Cursor to Aggregate Award and Count
3221 --
3222 CURSOR cur_agg_lf_count ( cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE ,
3223 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3224 IS
3225 SELECT NVL(SUM(NVL(awd.offered_amt,0)),0) lf_total,
3226 COUNT(awd.award_id) lf_count
3227 FROM igf_aw_award_all awd,
3228 igf_aw_fund_mast_all fmast,
3229 igf_ap_fa_base_rec fabase,
3230 igf_ap_batch_aw_map_all bam
3231 WHERE fmast.fund_code = cp_fund_code
3232 AND awd.fund_id = fmast.fund_id
3233 AND awd.base_id = fabase.base_id
3234 AND fabase.person_id = cp_person_id
3235 AND fabase.ci_cal_type = bam.ci_cal_type
3236 AND fabase.ci_sequence_number = bam.ci_sequence_number
3237 AND awd.award_status IN ('OFFERED', 'ACCEPTED')
3238 AND bam.award_year_status_code IN ('LA','LE');
3239
3240 agg_lf_count_rec cur_agg_lf_count%ROWTYPE;
3241 --
3242 -- This cursor retrives Total Award for a Fund - This is Yearly amount
3243 --
3244
3245 CURSOR cur_total_fund_awd (p_base_id igf_aw_award.base_id%TYPE,
3246 p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
3247 IS
3248 SELECT SUM(disb.disb_gross_amt) total_fund_amt
3249 FROM
3250 igf_aw_award awd,
3251 igf_aw_awd_disb disb
3252 WHERE awd.base_id = p_base_id
3253 AND awd.fund_id = p_fund_id
3254 AND awd.award_id = disb.award_id
3255 AND awd.award_status IN ('OFFERED', 'ACCEPTED')
3256 AND disb.trans_type <> 'C';
3257
3258 total_fund_awd_rec cur_total_fund_awd%ROWTYPE;
3259 --
3260 -- This cursor retrives Total Amount for a Award -
3261 --
3262 CURSOR cur_total_award_amt (p_award_id igf_aw_award_all.award_id%TYPE)
3263 IS
3264 SELECT
3265 SUM(disb.disb_gross_amt) total_award_amt
3266 FROM
3267 igf_aw_awd_disb disb
3268 WHERE
3269 disb.award_id = p_award_id AND
3270 disb.trans_type <> 'C';
3271
3272 total_award_amt_rec cur_total_award_amt%ROWTYPE;
3273
3274 --
3275 -- Cursor to determine if the Fund uses federal methodology or not
3276 --
3277 CURSOR cur_chk_fdl_fund (p_fund_id igf_aw_fund_mast_all.fund_id%TYPE)
3278 IS
3279 SELECT
3280 fm.replace_fc
3281 FROM
3282 igf_aw_fund_mast fm
3283 WHERE
3284 fm.fund_id = p_fund_id;
3285
3286 chk_fdl_fund_rec cur_chk_fdl_fund%ROWTYPE;
3287
3288 CURSOR cur_fund_details( p_fund_id igf_aw_fund_mast.fund_id%TYPE)
3289 IS
3290 SELECT
3291 DECODE(
3292 NVL(allow_overaward,'N'),'N',
3293 NVL(fmast.remaining_amt,0),
3294 NVL(fmast.remaining_amt,0) +
3295 (
3296 DECODE (
3297 NVL(over_award_amt,0),0,
3298 NVL(over_award_perct,0) * NVL(fmast.available_amt,0) / 100,
3299 NVL(over_award_amt,0)
3300 )
3301 )
3302 )remaining_amt,
3303 NVL(fmast.max_yearly_amt,0) max_yearly_amt,
3304 NVL(fmast.max_award_amt,0) max_award_amt,
3305 NVL(fmast.max_life_amt,0) max_life_amt,
3306 NVL(fmast.max_life_term,0) max_life_term,
3307 fmast.min_award_amt,
3308 fmast.max_num_disb,
3309 fmast.min_num_disb
3310 FROM
3311 igf_aw_fund_mast_all fmast
3312 WHERE
3313 fmast.fund_id = p_fund_id;
3314
3315 fund_details_rec cur_fund_details%ROWTYPE;
3316
3317
3318 --
3319 -- Cursor to get total disbursements for award
3320 --
3321 CURSOR cur_get_count ( p_award_id igf_aw_award_all.award_id%TYPE)
3322 IS
3323 SELECT
3324 COUNT(ld_cal_type) disb_count
3325 FROM igf_aw_awd_disb
3326 WHERE
3327 award_id = p_award_id;
3328
3329
3330 CURSOR cur_disb_num ( p_award_id igf_aw_award_all.award_id%TYPE) IS
3331 SELECT disb_num,disb_date,trans_type,disb_accepted_amt accepted_amt
3332 FROM
3333 igf_aw_awd_disb_all
3334 WHERE
3335 award_id = p_award_id;
3336
3337 disb_num_rec cur_disb_num%ROWTYPE;
3338
3339
3340 CURSOR c_award_status
3341 IS
3342 SELECT
3343 *
3344 FROM
3345 igf_aw_award
3346 WHERE
3347 award_id = p_award_id ;
3348
3349 c_award_status_rec c_award_status%ROWTYPE;
3350
3351 --
3352 -- Cursor to get term totals for a disbursement
3353 --
3354 CURSOR cur_term_amounts (p_award_id NUMBER,
3355 p_ld_seq_number NUMBER)
3356 IS
3357 SELECT disb.ld_cal_type,
3358 disb.ld_sequence_number,
3359 disb.base_attendance_type_code,
3360 SUM(disb.disb_gross_amt) term_total
3361 FROM igf_aw_awd_disb_all disb,
3362 igf_aw_award_all awd
3363 WHERE disb.trans_type <> 'C'
3364 AND awd.award_id = disb.award_id
3365 AND awd.award_id = p_award_id
3366 AND disb.ld_sequence_number = p_ld_seq_number
3367 GROUP BY disb.ld_cal_type,disb.ld_sequence_number,disb.base_attendance_type_code;
3368
3369 term_amounts_rec cur_term_amounts%ROWTYPE;
3370
3371 ln_count_i BINARY_INTEGER := 0;
3372 ln_tot_rec NUMBER := 0;
3373 ln_aid NUMBER;
3374 lnf_resource NUMBER;
3375 lni_resource NUMBER;
3376 ln_unmet_need_f NUMBER;
3377 ln_unmet_need_i NUMBER;
3378 ln_resource_f_fc NUMBER;
3379 ln_resource_i_fc NUMBER;
3380 lv_pell_mat VARCHAR2(60) ;
3381 ln_corrected_amt NUMBER;
3382 ln_count_rec NUMBER := 0;
3383 p_term_aid NUMBER;
3384 p_return_status VARCHAR2(30);
3385 p_message VARCHAR2(4000);
3386 lv_message fnd_new_messages.message_text%TYPE;
3387 l_std_loan_tab igf_aw_packng_subfns.std_loan_tab := igf_aw_packng_subfns.std_loan_tab();
3388 l_msg_name fnd_new_messages.message_name%TYPE;
3389 l_efc NUMBER;
3390 l_dummy_pell_efc NUMBER;
3391 l_no_of_months NUMBER;
3392 l_subz_loan VARCHAR2(1);
3393 l_efc_ay NUMBER;
3394
3395 BEGIN
3396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3397 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','Parameter List - START');
3398 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_calling_form: ' ||p_calling_form);
3399 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_base_id: ' ||p_base_id);
3400 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_fund_id: ' ||p_fund_id);
3401 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_fund_code: ' ||p_fund_code);
3402 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_fed_fund_code: ' ||p_fed_fund_code);
3403 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_person_number: ' ||p_person_number);
3404 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_award_id: ' ||p_award_id);
3405 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_act_isir: ' ||p_act_isir);
3406 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_ld_seq_number: ' ||p_ld_seq_number);
3407 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','p_awd_prd_code: ' ||p_awd_prd_code);
3408 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','Parameter List - END');
3409 END IF;
3410
3411 lv_message := 'NULL';
3412 p_chk_holds := '*';
3413
3414 OPEN cur_fund_details( p_fund_id);
3415 FETCH cur_fund_details INTO fund_details_rec;
3416 CLOSE cur_fund_details;
3417
3418 OPEN cur_total_fund_awd (p_base_id,p_fund_id);
3419 FETCH cur_total_fund_awd INTO total_fund_awd_rec;
3420 CLOSE cur_total_fund_awd;
3421
3422 OPEN cur_total_award_amt(p_award_id);
3423 FETCH cur_total_award_amt INTO total_award_amt_rec;
3424 CLOSE cur_total_award_amt;
3425
3426 OPEN cur_max_lf_count( p_fund_code,igf_gr_gen.get_person_id(p_base_id));
3427 FETCH cur_max_lf_count INTO max_lf_count_rec;
3428 CLOSE cur_max_lf_count;
3429
3430 OPEN cur_agg_lf_count( p_fund_code,igf_gr_gen.get_person_id(p_base_id));
3431 FETCH cur_agg_lf_count INTO agg_lf_count_rec;
3432 CLOSE cur_agg_lf_count;
3433
3434 max_lf_count_rec.lf_total := max_lf_count_rec.lf_total + agg_lf_count_rec.lf_total;
3435 max_lf_count_rec.lf_count := max_lf_count_rec.lf_count + agg_lf_count_rec.lf_count;
3436
3437 OPEN cur_get_count( p_award_id);
3438 FETCH cur_get_count INTO ln_count_rec;
3439 CLOSE cur_get_count;
3440
3441 -- Getting the award status for the award
3442 OPEN c_award_status ;
3443 FETCH c_award_status INTO c_award_status_rec;
3444 CLOSE c_award_status;
3445
3446
3447 IF ln_count_rec = 0 THEN
3448 fnd_message.set_name('IGF','IGF_DB_NO_DISB_AWD');
3449 lv_message := fnd_message.get;
3450 RETURN lv_message;
3451 END IF;
3452
3453 --
3454 -- Check Whether the award amount is exceeding the remaining amount in the fund
3455 --
3456 IF fund_details_rec.remaining_amt < 0 THEN
3457 fnd_message.set_name('IGF','IGF_AW_NO_ENUGH_FNDS');
3458 lv_message := fnd_message.get;
3459 RETURN lv_message;
3460 END IF;
3461
3462 -- l_subz_loan is set to 'Y' for Subsidized loans. This is needed bcoz for Subsidized
3463 -- loans, VA30 and AMERICORPS awards are not considered as a resource. This flag is used
3464 -- down the line in the call to get_resource_need()
3465 IF p_fed_fund_code IN ('DLS','FLS') THEN
3466 l_subz_loan := 'Y';
3467
3468 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3469 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','Fund is DLS/FLS, so set l_subz_loan to Y');
3470 END IF;
3471 ELSE
3472 l_subz_loan := 'N';
3473
3474 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3475 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','Fund is NOT DLS/FLS, so set l_subz_loan to N');
3476 END IF;
3477 END IF;
3478
3479 -- This would ensure that if the Award Status is accepted and the disb number's transaction type
3480 -- is Actual or Planned then the Accepted amount must be entered;
3481 -- Bug id :
3482 FOR disb_num_rec IN cur_disb_num (p_award_id) LOOP
3483 IF disb_num_rec.trans_type IN ('P','A')AND c_award_status_rec.award_status ='ACCEPTED' THEN
3484 IF disb_num_rec.accepted_amt IS NULL THEN
3485 fnd_message.set_name('IGF','IGF_DB_ENTER_ACCEPT_AMT');
3486 fnd_message.set_token('DISB_NUM',disb_num_rec.disb_num);
3487 lv_message := fnd_message.get;
3488 RETURN lv_message;
3489 END IF;
3490 END IF;
3491 END LOOP;
3492
3493 IF p_ld_seq_number IS NOT NULL THEN
3494
3495 --
3496 -- Min / Max Number of Disbursement Check
3497 --
3498
3499
3500 IF fund_details_rec.min_num_disb IS NOT NULL THEN
3501 IF ln_count_rec < fund_details_rec.min_num_disb THEN
3502 fnd_message.set_name('IGF','IGF_AW_MIN_NUM_DISB_NOT_EXCEED');
3503 lv_message := fnd_message.get;
3504 RETURN lv_message;
3505 END IF;
3506 END IF;
3507
3508 IF fund_details_rec.max_num_disb IS NOT NULL THEN
3509 IF ln_count_rec > fund_details_rec.max_num_disb THEN
3510 fnd_message.set_name('IGF','IGF_AW_MAX_NUM_DISB_EXCEEDED');
3511 lv_message := fnd_message.get;
3512 RETURN lv_message;
3513 END IF;
3514 ELSE
3515
3516 IF p_calling_form IN ('IGFAW038','IGFAW039') THEN
3517 --
3518 -- If the maximum disb num is not specified, for PLUS it is 4/ For Sub/unsub 20
3519 --
3520 IF p_fed_fund_code IN ('DLP','FLP') THEN -- PLUS
3521 IF ln_count_rec > 4 THEN
3522 fnd_message.set_name('IGF','IGF_AW_PLUS_DISB');
3523 lv_message := fnd_message.get;
3524 RETURN lv_message;
3525 END IF;
3526 ELSIF p_fed_fund_code IN ('DLS','FLS','DLU','FLU') THEN -- S.UNS.
3527 IF ln_count_rec > 20 THEN
3528 fnd_message.set_name('IGF','IGF_AW_SUNS_DISB');
3529 lv_message := fnd_message.get;
3530 RETURN lv_message;
3531 END IF;
3532 END IF;
3533
3534 ELSIF p_calling_form = 'IGFGR005' THEN
3535
3536 --
3537 -- If the maximum disb num is not specified, for Pell it can be 90
3538 --
3539 IF ln_count_rec > 90 THEN
3540 fnd_message.set_name('IGF','IGF_AW_PELL_DISB');
3541 lv_message := fnd_message.get;
3542 RETURN lv_message;
3543 END IF;
3544 END IF;
3545
3546 END IF; -- max num disb check
3547
3548 END IF; -- term seq no is not null
3549
3550
3551 IF UPPER(p_calling_form) = 'IGFGR005' THEN
3552
3553 --
3554 -- FA 131 Check
3555 -- Check if the amount is less, add as warning message
3556 -- if calc_term_pell does not error out
3557 --
3558 FOR term_amounts_rec IN cur_term_amounts(p_award_id,p_ld_seq_number)
3559 LOOP
3560
3561 p_message := NULL;
3562 igf_gr_pell_calc.calc_term_pell(p_base_id,
3563 term_amounts_rec.base_attendance_type_code,
3564 term_amounts_rec.ld_cal_type,term_amounts_rec.ld_sequence_number,
3565 p_term_aid,
3566 p_return_status,
3567 p_message,
3568 'IGFGR005',
3569 lv_pell_mat);
3570
3571 IF NVL(p_return_status,'N') = 'E' THEN
3572 lv_message := p_message;
3573 RETURN lv_message;
3574 ELSIF NVL(p_term_aid,0) < term_amounts_rec.term_total THEN
3575 fnd_message.set_name('IGF','IGF_AW_PELL_DISB_WARN');
3576 fnd_message.set_token('LD_ALT_CODE',igf_gr_gen.get_alt_code(term_amounts_rec.ld_cal_type,term_amounts_rec.ld_sequence_number));
3577 fnd_message.set_token('ATT_TYPE',igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT',term_amounts_rec.base_attendance_type_code));
3578 fnd_message.set_token('TERM_TOTAL',term_amounts_rec.term_total);
3579 fnd_message.set_token('CALC_AMT',p_term_aid);
3580 fnd_msg_pub.add;
3581 END IF;
3582 END LOOP;
3583 --
3584 -- if the pell matrix changes for calculation
3585 -- we need to update igf_aw_award with the new value for lv_pell_mat
3586 --
3587 IF lv_pell_mat <> c_award_status_rec.alt_pell_schedule THEN
3588
3589 c_award_status_rec.alt_pell_schedule := lv_pell_mat;
3590
3591 igf_aw_award_pkg.update_row(x_rowid => c_award_status_rec.row_id,
3592 x_award_id => c_award_status_rec.award_id,
3593 x_fund_id => c_award_status_rec.fund_id,
3594 x_base_id => c_award_status_rec.base_id,
3595 x_offered_amt => c_award_status_rec.offered_amt,
3596 x_accepted_amt => c_award_status_rec.accepted_amt,
3597 x_paid_amt => c_award_status_rec.paid_amt,
3598 x_packaging_type => c_award_status_rec.packaging_type,
3599 x_batch_id => c_award_status_rec.batch_id,
3600 x_manual_update => c_award_status_rec.manual_update,
3601 x_rules_override => c_award_status_rec.rules_override,
3602 x_award_date => c_award_status_rec.award_date,
3603 x_award_status => c_award_status_rec.award_status,
3604 x_attribute_category => c_award_status_rec.attribute_category,
3605 x_attribute1 => c_award_status_rec.attribute1,
3606 x_attribute2 => c_award_status_rec.attribute2,
3607 x_attribute3 => c_award_status_rec.attribute3,
3608 x_attribute4 => c_award_status_rec.attribute4,
3609 x_attribute5 => c_award_status_rec.attribute5,
3610 x_attribute6 => c_award_status_rec.attribute6,
3611 x_attribute7 => c_award_status_rec.attribute7,
3612 x_attribute8 => c_award_status_rec.attribute8,
3613 x_attribute9 => c_award_status_rec.attribute9,
3614 x_attribute10 => c_award_status_rec.attribute10,
3615 x_attribute11 => c_award_status_rec.attribute11,
3616 x_attribute12 => c_award_status_rec.attribute12,
3617 x_attribute13 => c_award_status_rec.attribute13,
3618 x_attribute14 => c_award_status_rec.attribute14,
3619 x_attribute15 => c_award_status_rec.attribute15,
3620 x_attribute16 => c_award_status_rec.attribute16,
3621 x_attribute17 => c_award_status_rec.attribute17,
3622 x_attribute18 => c_award_status_rec.attribute18,
3623 x_attribute19 => c_award_status_rec.attribute19,
3624 x_attribute20 => c_award_status_rec.attribute20,
3625 x_rvsn_id => c_award_status_rec.rvsn_id,
3626 x_alt_pell_schedule => c_award_status_rec.alt_pell_schedule,
3627 x_mode => 'R',
3628 x_award_number_txt => c_award_status_rec.award_number_txt,
3629 x_legacy_record_flag => NULL,
3630 x_adplans_id => c_award_status_rec.adplans_id,
3631 x_lock_award_flag => c_award_status_rec.lock_award_flag,
3632 x_app_trans_num_txt => c_award_status_rec.app_trans_num_txt,
3633 x_awd_proc_status_code => c_award_status_rec.awd_proc_status_code,
3634 x_notification_status_code => c_award_status_rec.notification_status_code,
3635 x_notification_status_date => c_award_status_rec.notification_status_date,
3636 x_publish_in_ss_flag => c_award_status_rec.publish_in_ss_flag
3637 );
3638
3639
3640 END IF; -- pell schdl has changed
3641 END IF; -- calling form is igfgr005
3642
3643 --
3644 -- Start Bug 2431276
3645 -- These five validatons changed to warnings from errors
3646 --
3647 -- start of warnings
3648
3649 --
3650 -- Check Whether the award amount is exceeding the Min Limit Amounts in the fund
3651 --
3652
3653 IF NVL(total_award_amt_rec.total_award_amt,0) < fund_details_rec.min_award_amt THEN
3654 fnd_message.set_name('IGF','IGF_AW_MIN_AMT_FAILED');
3655 fnd_message.set_token('AMOUNT',fund_details_rec.min_award_amt);
3656 fnd_message.set_token('FUND',p_fund_code);
3657 fnd_msg_pub.add;
3658 END IF;
3659
3660 --
3661 -- Check if the Award Amount is exceeding the Max Award Amount in the fund
3662 --
3663 IF NVL(total_award_amt_rec.total_award_amt,0) > fund_details_rec.max_award_amt THEN
3664 fnd_message.set_name('IGF','IGF_AW_MAX_AMT_EXCEED');
3665 fnd_message.set_token('AMOUNT',fund_details_rec.max_award_amt);
3666 fnd_message.set_token('FUND',p_fund_code);
3667 fnd_msg_pub.add;
3668 END IF;
3669
3670 --
3671 -- Check if the Award Amount is exceeding the Max Yearly Amounts in the fund
3672 --
3673 IF fund_details_rec.max_yearly_amt > 0 THEN
3674 IF NVL(total_fund_awd_rec.total_fund_amt,0) > fund_details_rec.max_yearly_amt THEN
3675 fnd_message.set_name('IGF','IGF_AW_STD_EXCED_MAX_YR_AMT');
3676 fnd_message.set_token('AMOUNT',fund_details_rec.max_yearly_amt);
3677 fnd_message.set_token('FUND',p_fund_code);
3678 fnd_msg_pub.add;
3679 END IF;
3680 END IF;
3681 --
3682 -- Check whether the Award Amount is exceeding the LifeTime Amount in the fund
3683 --
3684 IF fund_details_rec.max_life_amt >0 THEN
3685 IF NVL(max_lf_count_rec.lf_total,0) > fund_details_rec.max_life_amt THEN
3686 fnd_message.set_name('IGF','IGF_AW_STD_EXCED_MAX_LF_AMT');
3687 fnd_message.set_token('AMOUNT',fund_details_rec.max_life_amt);
3688 fnd_message.set_token('FUND',p_fund_code);
3689 fnd_msg_pub.add;
3690 END IF;
3691 END IF;
3692 --
3693 -- Check whether the Award count is exceeding the LifeTime count in the fund
3694 --
3695
3696 IF fund_details_rec.max_life_term >0 THEN
3697 IF NVL(max_lf_count_rec.lf_count,0) > fund_details_rec.max_life_term THEN
3698 fnd_message.set_name('IGF','IGF_DB_MAX_LIFE_TERM_EXCEED');
3699 fnd_message.set_token('TERM',fund_details_rec.max_life_term );
3700 fnd_message.set_token('FUND',p_fund_code);
3701 fnd_msg_pub.add;
3702 END IF;
3703 END IF;
3704
3705 --
3706 -- end of warnings
3707 -- End Bug 2431276
3708 --
3709
3710 --
3711 -- Check for the Federal Loan Limits
3712 --
3713 --
3714 -- The check for active isir will not be needed once igfaw016 comes up with
3715 -- the validation which will not allow addition of awards if there is no isir present
3716 --
3717
3718 IF p_act_isir IS NOT NULL AND
3719 UPPER(p_calling_form) IN ('IGFAW038','IGFAW039')THEN
3720 IF p_fed_fund_code IN ('DLS','DLU','FLS','FLU') THEN
3721 ln_corrected_amt := 0;
3722 l_msg_name := NULL;
3723 -- since the fund amount is already awarded to the student then ln_corrected_amt is passed as 0.
3724 igf_aw_packng_subfns.check_loan_limits(l_base_id => p_base_id,
3725 fund_type => p_fed_fund_code,
3726 l_award_id => p_award_id,
3727 l_adplans_id => NULL,
3728 l_aid => ln_corrected_amt,
3729 l_std_loan_tab => l_std_loan_tab,
3730 p_msg_name => l_msg_name,
3731 l_awd_period => p_awd_prd_code,
3732 l_called_from => 'PACKAGING'
3733 );
3734 -- If the returned ln_corrected_amt is 0 with no message returned or ln_corrected_amt is greater than 0 then
3735 -- the set up is fine ,so no warning message.
3736 IF ln_corrected_amt = 0 AND l_msg_name IS NOT NULL THEN
3737 IF l_msg_name = 'IGF_AW_CLS_STD_NOT_FND' THEN
3738 l_msg_name := 'IGF_AW_CLS_STD_NOT_FND_WNG';
3739 ELSIF l_msg_name = 'IGF_AW_CLSSTD_MAP_NOT_FND' THEN
3740 l_msg_name := 'IGF_AW_CLSSTD_MAP_NOT_FND_WNG';
3741 ELSIF l_msg_name = 'IGF_AW_DEP_STAT_NOT_FND' THEN
3742 l_msg_name := 'IGF_AW_DEP_STAT_NOT_FND';
3743 ELSIF l_msg_name = 'IGF_AW_LOAN_LMT_NOT_FND' THEN
3744 l_msg_name := 'IGF_AW_LOAN_LMT_NOT_FND_WNG';
3745 END IF;
3746 fnd_message.set_name('IGF',l_msg_name);
3747 fnd_msg_pub.add;
3748 ELSIF ln_corrected_amt < 0 THEN
3749 -- if the ln_corrected_amt is less than 0 then some of the Stafford loan limit check has failed so
3750 -- we are displaying the appropriate warning message since the user can override the message.
3751 -- add yes no message to the stack
3752 --
3753 IF l_msg_name = 'IGF_AW_AGGR_LMT_ERR' THEN
3754 l_msg_name := 'IGF_AW_AGGR_LMT_WNG';
3755 ELSIF l_msg_name = 'IGF_AW_ANNUAL_LMT_ERR' THEN
3756 l_msg_name := 'IGF_AW_ANNUAL_LMT_WNG';
3757 ELSIF l_msg_name = 'IGF_AW_SUB_AGGR_LMT_ERR' THEN
3758 l_msg_name := 'IGF_AW_SUB_AGGR_LMT_WNG';
3759 ELSIF l_msg_name = 'IGF_AW_SUB_LMT_ERR' THEN
3760 l_msg_name := 'IGF_AW_SUB_LMT_ERR_WNG';
3761 ELSIF l_msg_name = 'IGF_AW_UNSUB_AGGR_LMT_ERR' THEN
3762 l_msg_name := 'IGF_AW_UNSUB_AGGR_LMT_WNG';
3763 ELSIF l_msg_name = 'IGF_AW_UNSUB_LMT_ERR' THEN
3764 l_msg_name := 'IGF_AW_UNSUB_LMT_WNG';
3765 END IF;
3766 fnd_message.set_name('IGF',l_msg_name);
3767 fnd_message.set_token('FUND_CODE',p_fund_code);
3768 fnd_msg_pub.add;
3769 END IF;
3770 END IF;
3771 END IF;
3772
3773 --
3774 -- Over Award is created only for Federal Funds
3775 -- Check If the Fund Uses Feferal Methodology to calculate Need
3776 -- Check IF the award is not of FWS type
3777
3778 IF p_calling_form <> 'IGFSE003' THEN
3779 OPEN cur_chk_fdl_fund(p_fund_id);
3780 FETCH cur_chk_fdl_fund INTO chk_fdl_fund_rec;
3781 CLOSE cur_chk_fdl_fund;
3782
3783 -- Get the EFC months for the Award Period and for the Award Yr
3784 igf_aw_packng_subfns.get_fed_efc(
3785 l_base_id => p_base_id,
3786 l_awd_prd_code => p_awd_prd_code,
3787 l_efc_f => l_efc,
3788 l_pell_efc => l_dummy_pell_efc,
3789 l_efc_ay => l_efc_ay
3790 );
3791
3792 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3793 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','l_efc:'||l_efc);
3794 END IF;
3795
3796 IF l_efc IS NOT NULL THEN
3797
3798 -- Check for Overaward within the Awardin Period first
3799
3800 IF p_awd_prd_code IS NOT NULL THEN
3801
3802 igf_aw_gen_002.get_resource_need(
3803 p_base_id => p_base_id,
3804 p_resource_f => lnf_resource,
3805 p_resource_i => lni_resource,
3806 p_unmet_need_f => ln_unmet_need_f,
3807 p_unmet_need_i => ln_unmet_need_i,
3808 p_resource_f_fc => ln_resource_f_fc,
3809 p_resource_i_fc => ln_resource_i_fc,
3810 p_awd_prd_code => p_awd_prd_code,
3811 p_calc_for_subz_loan => l_subz_loan
3812 );
3813
3814
3815 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3816 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','P_AWD_PRD_CODE>ln_unmet_need_f:'|| P_AWD_PRD_CODE || ' >' || ln_unmet_need_f);
3817 END IF;
3818
3819 IF NVL(ln_unmet_need_f,0) < 0 THEN
3820
3821 -- Overawad at Awarding Period itself
3822 p_chk_holds := 'A';
3823
3824 -- No need to proceed further for Award Year level validation
3825 RETURN lv_message;
3826
3827 END IF;
3828
3829 END IF;
3830
3831 -- Now check for OverAwd scenario for the Entire Award Year
3832
3833 igf_aw_gen_002.get_resource_need(
3834 p_base_id => p_base_id,
3835 p_resource_f => lnf_resource,
3836 p_resource_i => lni_resource,
3837 p_unmet_need_f => ln_unmet_need_f,
3838 p_unmet_need_i => ln_unmet_need_i,
3839 p_resource_f_fc => ln_resource_f_fc,
3840 p_resource_i_fc => ln_resource_i_fc,
3841 p_awd_prd_code => NULL,
3842 p_calc_for_subz_loan => l_subz_loan
3843 );
3844
3845
3846 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3847 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_003.check_amounts.debug','Award Year >ln_unmet_need_f: > '|| ln_unmet_need_f);
3848 END IF;
3849
3850 IF NVL(ln_unmet_need_f,0) < 0 THEN
3851
3852 -- Overawad at Awarding Period itself
3853 p_chk_holds := 'Y';
3854
3855 END IF;
3856
3857 END IF; -- EFC not null
3858
3859 END IF;
3860 RETURN lv_message;
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3865 fnd_message.set_token('NAME','IGF_AW_GEN_003.CHECK_AMOUNTS'|| ' ' ||SQLERRM );
3866 app_exception.raise_exception;
3867
3868 END check_amounts;
3869
3870
3871 --
3872 -- sjadhav
3873 -- Bug 2306310
3874 --
3875 -- Procedure to update show_on_bill flag based on the fund manager value
3876 --
3877 PROCEDURE update_bill_flag ( p_fund_id IN igf_aw_award_all.fund_id%TYPE,
3878 p_new_val IN igf_aw_fund_mast_all.show_on_bill%TYPE)
3879 IS
3880
3881 --
3882 -- Cursor to get awards of the fund
3883 --
3884 CURSOR cur_get_awd ( p_fund_id IN igf_aw_award_all.fund_id%TYPE )
3885 IS
3886 SELECT award_id
3887 FROM
3888 igf_aw_award
3889 WHERE
3890 fund_id = p_fund_id;
3891
3892
3893 --
3894 -- Cursor to get Planned Disbursements for the award
3895 --
3896 CURSOR cur_get_adisb ( p_award_id IN igf_aw_award_all.award_id%TYPE,
3897 p_new_val IN igf_aw_fund_mast_all.show_on_bill%TYPE)
3898 IS
3899 SELECT *
3900 FROM
3901 igf_aw_awd_disb
3902 WHERE
3903 award_id = p_award_id AND
3904 trans_type = 'P' AND
3905 NVL(show_on_bill,'*') <> p_new_val
3906 FOR UPDATE OF
3907 show_on_bill NOWAIT;
3908
3909
3910 BEGIN
3911
3912 SAVEPOINT bill_upd_sp;
3913
3914 FOR get_awd_rec IN cur_get_awd ( p_fund_id )
3915 LOOP
3916 FOR get_adisb_rec IN cur_get_adisb( get_awd_rec.award_id,p_new_val)
3917 LOOP
3918
3919 igf_aw_awd_disb_pkg.update_row( x_rowid => get_adisb_rec.row_id,
3920 x_award_id => get_adisb_rec.award_id,
3921 x_disb_num => get_adisb_rec.disb_num,
3922 x_tp_cal_type => get_adisb_rec.tp_cal_type,
3923 x_tp_sequence_number => get_adisb_rec.tp_sequence_number,
3924 x_disb_gross_amt => get_adisb_rec.disb_gross_amt,
3925 x_fee_1 => get_adisb_rec.fee_1,
3926 x_fee_2 => get_adisb_rec.fee_2,
3927 x_disb_net_amt => get_adisb_rec.disb_net_amt,
3928 x_disb_date => get_adisb_rec.disb_date,
3929 x_trans_type => get_adisb_rec.trans_type,
3930 x_elig_status => get_adisb_rec.elig_status,
3931 x_elig_status_date => get_adisb_rec.elig_status_date,
3932 x_affirm_flag => get_adisb_rec.affirm_flag,
3933 x_hold_rel_ind => get_adisb_rec.hold_rel_ind,
3934 x_manual_hold_ind => get_adisb_rec.manual_hold_ind,
3935 x_disb_status => get_adisb_rec.disb_status,
3936 x_disb_status_date => get_adisb_rec.disb_status_date,
3937 x_late_disb_ind => get_adisb_rec.late_disb_ind,
3938 x_fund_dist_mthd => get_adisb_rec.fund_dist_mthd,
3939 x_prev_reported_ind => get_adisb_rec.prev_reported_ind,
3940 x_fund_release_date => get_adisb_rec.fund_release_date,
3941 x_fund_status => get_adisb_rec.fund_status,
3942 x_fund_status_date => get_adisb_rec.fund_status_date,
3943 x_fee_paid_1 => get_adisb_rec.fee_paid_1,
3944 x_fee_paid_2 => get_adisb_rec.fee_paid_2,
3945 x_cheque_number => get_adisb_rec.cheque_number,
3946 x_ld_cal_type => get_adisb_rec.ld_cal_type,
3947 x_ld_sequence_number => get_adisb_rec.ld_sequence_number,
3948 x_disb_accepted_amt => get_adisb_rec.disb_accepted_amt,
3949 x_disb_paid_amt => get_adisb_rec.disb_paid_amt,
3950 x_rvsn_id => get_adisb_rec.rvsn_id,
3951 x_int_rebate_amt => get_adisb_rec.int_rebate_amt,
3952 x_force_disb => get_adisb_rec.force_disb,
3953 x_min_credit_pts => get_adisb_rec.min_credit_pts,
3954 x_disb_exp_dt => get_adisb_rec.disb_exp_dt,
3955 x_verf_enfr_dt => get_adisb_rec.verf_enfr_dt,
3956 x_fee_class => get_adisb_rec.fee_class,
3957 x_show_on_bill => p_new_val,
3958 x_mode => 'R',
3959 x_attendance_type_code => get_adisb_rec.attendance_type_code,
3960 x_payment_prd_st_date => get_adisb_rec.payment_prd_st_date,
3961 x_change_type_code => get_adisb_rec.change_type_code,
3962 x_fund_return_mthd_code => get_adisb_rec.fund_return_mthd_code,
3963 x_direct_to_borr_flag => get_adisb_rec.direct_to_borr_flag
3964 );
3965
3966 END LOOP;
3967 END LOOP;
3968
3969
3970 EXCEPTION
3971
3972 WHEN app_exception.record_lock_exception THEN
3973 ROLLBACK to bill_upd_sp;
3974 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
3975 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_BILL_FLAG' );
3976 app_exception.raise_exception;
3977
3978 WHEN others THEN
3979 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3980 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_BILL_FLAG'|| ' ' || SQLERRM);
3981 app_exception.raise_exception;
3982
3983
3984 END update_bill_flag;
3985
3986
3987 FUNCTION delete_awd_disb ( p_award_id IN igf_aw_award_all.award_id%TYPE ,
3988 p_ld_seq_num IN igf_aw_awd_disb_all.ld_sequence_number%TYPE ,
3989 p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE )
3990 RETURN VARCHAR2
3991 IS
3992
3993 --
3994 --------------------------------------------------------------------------------------------
3995 -- Who when what
3996 --------------------------------------------------------------------------------------------
3997 -- Brajendr 14-Jun-2002 Bug 2415009
3998 -- Added a check for not deleting of award
3999 -- and disbursement if auth id is generated.
4000 --------------------------------------------------------------------------------------------
4001 -- mesriniv 29-may-2002 Added this line of code
4002 -- igf_aw_gen.update_fabase_awds(get_awds_rec.base_id,'REVISED');
4003 --------------------------------------------------------------------------------------------
4004 -- sjadhav Bug 2306310 Function to delete disbursements
4005 --------------------------------------------------------------------------------------------
4006 --
4007 -- check if award has pell or loan record
4008 --
4009 CURSOR cur_pell_awd (p_award_id igf_aw_award_all.award_id%TYPE)
4010 IS
4011 SELECT
4012 COUNT(origination_id) awd_count
4013 FROM
4014 igf_gr_rfms
4015 WHERE
4016 award_id = p_award_id;
4017
4018 pell_awd_rec cur_pell_awd%ROWTYPE;
4019
4020 CURSOR cur_loan_awd (p_award_id igf_aw_award_all.award_id%TYPE)
4021 IS
4022 SELECT
4023 COUNT(loan_id) awd_count
4024 FROM
4025 igf_sl_loans
4026 WHERE
4027 award_id = p_award_id;
4028
4029 loan_awd_rec cur_loan_awd%ROWTYPE;
4030
4031
4032 --
4033 -- check if award has pell or loan record
4034 --
4035 CURSOR cur_fws_awd (p_award_id igf_aw_award_all.award_id%TYPE) IS
4036 SELECT COUNT(auth_id) awd_count
4037 FROM igf_se_auth
4038 WHERE award_id = p_award_id
4039 AND flag = 'A';
4040
4041 fws_awd_rec cur_fws_awd%ROWTYPE;
4042
4043
4044 --
4045 -- Cursor to get disbursements
4046 --
4047
4048 CURSOR cur_get_adisb ( p_award_id igf_aw_award_all.award_id%TYPE,
4049 p_ld_seq_num igf_aw_awd_disb_all.ld_sequence_number%TYPE,
4050 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
4051 IS
4052 SELECT
4053 row_id,
4054 disb_num
4055 FROM
4056 igf_aw_awd_disb
4057 WHERE
4058 award_id = p_award_id AND
4059 disb_num = NVL(p_disb_num,disb_num) AND
4060 ld_sequence_number = NVL(p_ld_seq_num,ld_sequence_number);
4061
4062 get_adisb_rec cur_get_adisb%ROWTYPE;
4063
4064
4065 --
4066 -- Cursor to get Holds
4067 --
4068
4069 CURSOR cur_get_holds ( p_award_id igf_aw_award_all.award_id%TYPE,
4070 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
4071 IS
4072 SELECT
4073 row_id
4074 FROM
4075 igf_db_disb_holds
4076 WHERE
4077 award_id = p_award_id AND
4078 disb_num = NVL(p_disb_num,disb_num);
4079
4080 get_holds_rec cur_get_holds%ROWTYPE;
4081
4082 --
4083 -- Cursor to get Holds
4084 --
4085
4086 CURSOR cur_get_awds ( p_award_id igf_aw_award_all.award_id%TYPE)
4087 IS
4088 SELECT
4089 row_id,base_id
4090 FROM
4091 igf_aw_award
4092 WHERE
4093 award_id = p_award_id ;
4094
4095 get_awds_rec cur_get_awds%ROWTYPE;
4096
4097 CURSOR cur_fed_fund_code(
4098 p_award_id NUMBER
4099 ) IS
4100 SELECT fcat.fed_fund_code
4101 FROM igf_aw_fund_cat fcat,
4102 igf_aw_fund_mast fmast,
4103 igf_aw_award_all awd
4104 WHERE fcat.fund_code = fmast.fund_code
4105 AND fmast.fund_id = awd.fund_id
4106 AND awd.award_id = p_award_id;
4107
4108 get_fund_rec cur_fed_fund_code%ROWTYPE;
4109
4110
4111 CURSOR cur_chg_dtls(
4112 cp_award_id igf_aw_award_all.award_id%TYPE,
4113 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
4114 ) IS
4115 SELECT ROWID row_id,
4116 disb_status
4117 FROM igf_aw_db_chg_dtls
4118 WHERE award_id = cp_award_id
4119 AND disb_num = cp_disb_num;
4120
4121 lv_message fnd_new_messages.message_text%TYPE;
4122 l_app VARCHAR2(50);
4123 l_name VARCHAR2(30);
4124
4125 --
4126 -- check if the award has any adjustments
4127 --
4128
4129 BEGIN
4130
4131 lv_message := 'NULL';
4132 --
4133 -- Do this check only when it is called from igfaw016
4134 --
4135 IF p_ld_seq_num IS NULL THEN
4136 OPEN cur_pell_awd(p_award_id);
4137 FETCH cur_pell_awd INTO pell_awd_rec;
4138 CLOSE cur_pell_awd;
4139
4140 IF pell_awd_rec.awd_count > 0 THEN
4141 fnd_message.set_name('IGF','IGF_AW_NO_DEL_PELL');
4142 lv_message := fnd_message.get;
4143 RETURN lv_message;
4144 END IF;
4145
4146
4147 OPEN cur_loan_awd(p_award_id);
4148 FETCH cur_loan_awd INTO loan_awd_rec;
4149 CLOSE cur_loan_awd;
4150
4151 IF loan_awd_rec.awd_count > 0 THEN
4152 fnd_message.set_name('IGF','IGF_AW_NO_DEL_LOAN');
4153 lv_message := fnd_message.get;
4154 RETURN lv_message;
4155 END IF;
4156
4157 -- Check for the FWS fund before detion of the Awards and its Disbursements
4158 -- If Auth Id is present then the data is already sent to 3rd party system,
4159 -- so should not allow to delete the Award and its disbursement.
4160 OPEN cur_fws_awd(p_award_id);
4161 FETCH cur_fws_awd INTO fws_awd_rec;
4162 CLOSE cur_fws_awd;
4163
4164 IF fws_awd_rec.awd_count > 0 THEN
4165 fnd_message.set_name('IGF','IGF_SE_AUTH_OR_PAID_PRSNT');
4166 lv_message := fnd_message.get;
4167 RETURN lv_message;
4168 END IF;
4169
4170 END IF;
4171
4172 IF p_ld_seq_num IS NOT NULL THEN
4173 FOR get_adisb_rec IN cur_get_adisb (p_award_id,p_ld_seq_num,p_disb_num) LOOP
4174 FOR get_holds_rec IN cur_get_holds (p_award_id,get_adisb_rec.disb_num) LOOP
4175 igf_db_disb_holds_pkg.delete_row(get_holds_rec.row_id);
4176 END LOOP;
4177
4178 OPEN cur_fed_fund_code(p_award_id);
4179 FETCH cur_fed_fund_code INTO get_fund_rec;
4180 CLOSE cur_fed_fund_code;
4181
4182 IF get_fund_rec.fed_fund_code IN ('DLS','DLP','DLU') THEN
4183 FOR get_chg_dtls IN cur_chg_dtls(p_award_id,get_adisb_rec.disb_num) LOOP
4184 IF get_chg_dtls.disb_status IN ('G') THEN
4185 igf_aw_db_chg_dtls_pkg.delete_row(get_chg_dtls.row_id);
4186 ELSE
4187 fnd_message.set_name('IGF','IGF_DB_DISB_DBCHG_FK');
4188 lv_message := fnd_message.get;
4189 RETURN lv_message;
4190 END IF;
4191 END LOOP;
4192 END IF;
4193 igf_aw_awd_disb_pkg.delete_row(get_adisb_rec.row_id);
4194
4195 END LOOP;
4196 END IF;
4197
4198
4199 RETURN lv_message;
4200
4201 EXCEPTION
4202
4203 WHEN others THEN
4204 --
4205 -- Return Adjustment fk message
4206 --
4207 ROLLBACK;
4208 fnd_message.parse_encoded(fnd_message.get_encoded, l_app, l_name);
4209 IF l_name = 'IGF_DB_DDTL_AWDD_FK' THEN
4210 fnd_message.set_name('IGF','IGF_DB_DDTL_AWDD_FK');
4211 lv_message := fnd_message.get;
4212 RETURN lv_message;
4213 ELSIF
4214 l_name = 'IGS_FI_FIPC_ADISB_FK' THEN
4215 fnd_message.set_name('IGS','IGS_FI_FIPC_ADISB_FK');
4216 lv_message := fnd_message.get;
4217 RETURN lv_message;
4218 ELSE
4219 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP' );
4220 fnd_message.set_token('NAME','IGF_AW_GEN_003.DELETE_AWD_DISB'||' '||SQLERRM);
4221 app_exception.raise_exception;
4222 END IF;
4223
4224
4225 END delete_awd_disb;
4226
4227
4228 FUNCTION get_total_disb ( p_award_id IN igf_aw_award_all.award_id%TYPE,
4229 p_ld_seq_num IN igf_aw_awd_disb_all.ld_sequence_number%TYPE )
4230 RETURN NUMBER
4231 IS
4232 --
4233 -- sjadhav
4234 -- Bug 2306310
4235 -- Function to return number of disbursements
4236 --
4237
4238 CURSOR cur_disb_nums ( p_award_id igf_aw_award_all.award_id%TYPE,
4239 p_ld_seq_num igf_aw_awd_disb_all.ld_sequence_number%TYPE)
4240 IS
4241 SELECT COUNT(disb_num) tot_num
4242 FROM
4243 igf_aw_awd_disb
4244 WHERE
4245 award_id = p_award_id AND
4246 ld_sequence_number = NVL(p_ld_seq_num,ld_sequence_number);
4247
4248 disb_nums_rec cur_disb_nums%ROWTYPE;
4249
4250 BEGIN
4251
4252 OPEN cur_disb_nums (p_award_id,p_ld_seq_num);
4253 FETCH cur_disb_nums INTO disb_nums_rec;
4254 CLOSE cur_disb_nums;
4255
4256 RETURN disb_nums_rec.tot_num;
4257
4258 EXCEPTION
4259
4260 WHEN OTHERS THEN
4261 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
4262 fnd_message.set_token('NAME','IGF_AW_GEN_003.GET_TOTAL_DISB' || ' ' || SQLERRM);
4263 app_exception.raise_exception;
4264
4265 END get_total_disb;
4266
4267 PROCEDURE awd_group_freeze(p_award_grp IN VARCHAR2,
4268 p_base_id IN NUMBER,
4269 p_out OUT NOCOPY VARCHAR2 )
4270 IS
4271 ---------------------------------------------------------------------
4272 --Created by : gmuralid
4273 --Date created: 08-04-2003
4274 --Purpose:
4275
4276 --Known limitations/enhancements and/or remarks:
4277
4278 --Change History:
4279
4280 --Who When What
4281 --bkkumar 6-Aug-2003 Bug# 3085852 Changed the cursors to
4282 -- remove the check for existence of awards
4283 -- and award status in 'accepted' or 'offered'
4284 --------------------------------------------------------------------
4285
4286 CURSOR c_chk_awd_grp(c_grp igf_aw_target_grp.group_cd%TYPE)
4287 IS
4288 SELECT
4289 'Y'
4290 FROM
4291 igf_ap_fa_base_rec fa,
4292 igf_aw_award awd,
4293 igf_aw_awd_frml_det fdet
4294 WHERE
4295 fa.target_group = c_grp AND
4296 awd.base_id = fa.base_id AND
4297 fa.packaging_status IN ('AUTO_PACKAGED','REVISED') AND
4298 fdet.formula_code = fa.target_group AND
4299 awd.request_id IS NOT NULL AND
4300 ROWNUM = 1;
4301
4302
4303 CURSOR c_chk_awd_grp_per(c_baseid NUMBER)
4304 IS
4305 SELECT
4306 'Y'
4307 FROM
4308 igf_ap_fa_base_rec fa,
4309 igf_aw_award awd,
4310 igf_aw_awd_frml_det fdet
4311 WHERE
4312 fa.base_id = c_baseid AND
4313 awd.base_id = fa.base_id AND
4314 fa.packaging_status IN ('AUTO_PACKAGED','REVISED') AND
4315 fdet.formula_code = fa.target_group AND
4316 awd.request_id IS NOT NULL AND
4317 ROWNUM = 1;
4318
4319 l_val VARCHAR2(1);
4320
4321 BEGIN
4322
4323 p_out := 'N';
4324 IF (p_base_id IS NULL AND p_award_grp IS NOT NULL) THEN
4325
4326 OPEN c_chk_awd_grp(p_award_grp);
4327 FETCH c_chk_awd_grp INTO l_val;
4328 CLOSE c_chk_awd_grp;
4329 p_out := NVL(l_val,'N');
4330
4331 ELSIF (p_base_id IS NOT NULL AND p_award_grp IS NULL) THEN
4332
4333 OPEN c_chk_awd_grp_per(p_base_id);
4334 FETCH c_chk_awd_grp_per INTO l_val;
4335 CLOSE c_chk_awd_grp_per;
4336 p_out := NVL(l_val,'N');
4337
4338 END IF;
4339
4340 EXCEPTION
4341
4342 WHEN OTHERS THEN
4343
4344 IF (c_chk_awd_grp%ISOPEN)THEN
4345 CLOSE c_chk_awd_grp;
4346 END IF;
4347
4348 IF (c_chk_awd_grp_per%ISOPEN) THEN
4349 CLOSE c_chk_awd_grp_per;
4350 END IF;
4351
4352 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP' );
4353 fnd_message.set_token('NAME','IGF_AW_GEN_003.AWD_GROUP_FREEZE'||' '|| SQLERRM);
4354 app_exception.raise_exception;
4355
4356 END awd_group_freeze;
4357
4358 PROCEDURE get_common_perct(
4359 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
4360 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
4361 p_perct OUT NOCOPY NUMBER,
4362 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
4363 ) AS
4364 ------------------------------------------------------------------
4365 --Created by : veramach, Oracle India
4366 --Date created: 11-NOV-2003
4367 --
4368 --Purpose:
4369 --
4370 --
4371 --Known limitations/enhancements and/or remarks:
4372 --
4373 --Change History:
4374 --Who When What
4375 -------------------------------------------------------------------
4376
4377 --Get common COA terms %
4378 CURSOR cur_get_perct IS
4379 SELECT SUM((teach_periods.tp_perct_num * terms.ld_perct_num)/100) perct
4380 FROM igf_aw_dp_terms terms,
4381 igf_aw_dp_teach_prds teach_periods,
4382 (SELECT base_id,
4383 ld_cal_type,
4384 ld_sequence_number
4385 FROM igf_aw_coa_itm_terms
4386 WHERE base_id = p_base_id
4387 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
4388 WHERE terms.adplans_id = p_adplans_id
4389 AND terms.adterms_id = teach_periods.adterms_id
4390 AND coaterms.ld_cal_type = terms.ld_cal_type
4391 AND coaterms.ld_sequence_number = terms.ld_sequence_number
4392 AND coaterms.base_id = p_base_id;
4393
4394 CURSOR cur_get_perct_awd IS
4395 SELECT SUM((teach_periods.tp_perct_num * terms.ld_perct_num)/100) perct
4396 FROM igf_aw_dp_terms terms,
4397 igf_aw_dp_teach_prds teach_periods,
4398 igf_aw_awd_prd_term aprd,
4399 igf_ap_fa_base_rec_all fa,
4400 (SELECT base_id,
4401 ld_cal_type,
4402 ld_sequence_number
4403 FROM igf_aw_coa_itm_terms
4404 WHERE base_id = p_base_id
4405 GROUP BY base_id, ld_cal_type, ld_sequence_number) coaterms
4406 WHERE terms.adplans_id = p_adplans_id
4407 AND terms.adterms_id = teach_periods.adterms_id
4408 AND coaterms.ld_cal_type = terms.ld_cal_type
4409 AND coaterms.ld_sequence_number = terms.ld_sequence_number
4410 AND coaterms.base_id = p_base_id
4411 AND coaterms.base_id = fa.base_id
4412 AND fa.ci_cal_type = aprd.ci_cal_type
4413 AND fa.ci_sequence_number = aprd.ci_sequence_number
4414 AND coaterms.ld_cal_type = aprd.ld_cal_type
4415 AND coaterms.ld_sequence_number = aprd.ld_sequence_number
4416 AND aprd.award_prd_cd = p_awd_prd_code;
4417
4418
4419 BEGIN
4420
4421 p_perct := 0;
4422 IF p_awd_prd_code IS NULL THEN
4423 OPEN cur_get_perct;
4424 FETCH cur_get_perct INTO p_perct;
4425 CLOSE cur_get_perct;
4426 ELSE
4427 OPEN cur_get_perct_awd;
4428 FETCH cur_get_perct_awd INTO p_perct;
4429 CLOSE cur_get_perct_awd;
4430 END IF;
4431 END get_common_perct;
4432
4433 PROCEDURE check_common_terms(
4434 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
4435 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
4436 p_result OUT NOCOPY NUMBER,
4437 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE
4438 ) AS
4439 ------------------------------------------------------------------
4440 --Created by : veramach, Oracle India
4441 --Date created: 11-NOV-2003
4442 --
4443 --Purpose: Checks if the distribution plan's terms and COA terms of the base_id
4444 -- have atleast one common term. If p_awd_prd_code is also passed, the procedure checks
4445 -- if there is atleast one common term between the base_id's COA terms,DP's terms and
4446 -- terms attached to the award period
4447 --
4448 --
4449 --Known limitations/enhancements and/or remarks:
4450 --
4451 --Change History:
4452 --Who When What
4453 -------------------------------------------------------------------
4454
4455 CURSOR cur_check_terms IS
4456 SELECT COUNT(*) common_terms
4457 FROM igf_aw_dp_terms terms,
4458 igf_aw_dp_teach_prds teach_periods,
4459 (SELECT base_id,
4460 ld_cal_type,
4461 ld_sequence_number
4462 FROM igf_aw_coa_itm_terms
4463 WHERE base_id = p_base_id
4464 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms
4465 WHERE terms.adplans_id = p_adplans_id
4466 AND terms.adterms_id = teach_periods.adterms_id
4467 AND coaterms.ld_cal_type = terms.ld_cal_type
4468 AND coaterms.ld_sequence_number = terms.ld_sequence_number
4469 AND coaterms.base_id = p_base_id;
4470
4471 CURSOR cur_check_terms_awd IS
4472 SELECT COUNT(*) common_terms
4473 FROM igf_aw_dp_terms terms,
4474 igf_aw_dp_teach_prds teach_periods,
4475 (SELECT base_id,
4476 ld_cal_type,
4477 ld_sequence_number
4478 FROM igf_aw_coa_itm_terms
4479 WHERE base_id = p_base_id
4480 GROUP BY base_id,ld_cal_type,ld_sequence_number) coaterms,
4481 igf_ap_fa_base_rec_all fa,
4482 igf_aw_awd_prd_term aprd
4483 WHERE terms.adplans_id = p_adplans_id
4484 AND terms.adterms_id = teach_periods.adterms_id
4485 AND coaterms.ld_cal_type = terms.ld_cal_type
4486 AND coaterms.ld_sequence_number = terms.ld_sequence_number
4487 AND coaterms.base_id = p_base_id
4488 AND coaterms.base_id = fa.base_id
4489 AND fa.ci_cal_type = aprd.ci_cal_type
4490 AND fa.ci_sequence_number = aprd.ci_sequence_number
4491 AND aprd.award_prd_cd = p_awd_prd_code
4492 AND coaterms.ld_cal_type = aprd.ld_cal_type
4493 AND coaterms.ld_sequence_number = aprd.ld_sequence_number;
4494
4495 BEGIN
4496 p_result := 0;
4497
4498 IF p_awd_prd_code IS NULL THEN
4499 OPEN cur_check_terms;
4500 FETCH cur_check_terms INTO p_result;
4501 CLOSE cur_check_terms;
4502 ELSE
4503 OPEN cur_check_terms_awd;
4504 FETCH cur_check_terms_awd INTO p_result;
4505 CLOSE cur_check_terms_awd;
4506 END IF;
4507
4508 p_result := NVL(p_result,0);
4509
4510 END check_common_terms;
4511
4512 PROCEDURE update_award_app_trans( p_award_id IN NUMBER,
4513 p_base_id IN NUMBER)
4514 IS
4515 ------------------------------------------------------------------
4516 --Created by : sjadhav, Oracle India
4517 --Date created: 4-Dec-2003
4518 --
4519 --Purpose: Update Application Transaction Number in AWARD table
4520 --
4521 --
4522 --Known limitations/enhancements and/or remarks:
4523 --
4524 --Change History:
4525 --Who When What
4526 -------------------------------------------------------------------
4527
4528 CURSOR cur_active_isir(
4529 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
4530 ) IS
4531 SELECT transaction_num
4532 FROM igf_ap_isir_matched_all
4533 WHERE base_id = cp_base_id
4534 AND NVL(active_isir,'N') = 'Y';
4535
4536 active_isir_rec cur_active_isir%ROWTYPE;
4537
4538 CURSOR cur_award_app_num(
4539 cp_award_id igf_aw_award_all.award_id%TYPE
4540 ) IS
4541 SELECT *
4542 FROM igf_aw_award
4543 WHERE award_id = p_award_id;
4544
4545 BEGIN
4546
4547 OPEN cur_active_isir(p_base_id);
4548 FETCH cur_active_isir INTO active_isir_rec;
4549 CLOSE cur_active_isir;
4550
4551 IF active_isir_rec.transaction_num IS NOT NULL THEN
4552 FOR rec IN cur_award_app_num(p_award_id)
4553 LOOP
4554 IF rec.app_trans_num_txt <> active_isir_rec.transaction_num THEN
4555 rec.app_trans_num_txt := active_isir_rec.transaction_num;
4556 igf_aw_award_pkg.update_row(
4557 x_mode => 'R',
4558 x_rowid => rec.row_id,
4559 x_award_id => rec.award_id,
4560 x_fund_id => rec.fund_id,
4561 x_base_id => rec.base_id,
4562 x_offered_amt => rec.offered_amt,
4563 x_accepted_amt => rec.accepted_amt,
4564 x_paid_amt => rec.paid_amt,
4565 x_packaging_type => rec.packaging_type,
4566 x_batch_id => rec.batch_id,
4567 x_manual_update => rec.manual_update,
4568 x_rules_override => 'N',
4569 x_award_date => rec.award_date,
4570 x_award_status => rec.award_status,
4571 x_attribute_category => rec.attribute_category,
4572 x_attribute1 => rec.attribute1,
4573 x_attribute2 => rec.attribute2,
4574 x_attribute3 => rec.attribute3,
4575 x_attribute4 => rec.attribute4,
4576 x_attribute5 => rec.attribute5,
4577 x_attribute6 => rec.attribute6,
4578 x_attribute7 => rec.attribute7,
4579 x_attribute8 => rec.attribute8,
4580 x_attribute9 => rec.attribute9,
4581 x_attribute10 => rec.attribute10,
4582 x_attribute11 => rec.attribute11,
4583 x_attribute12 => rec.attribute12,
4584 x_attribute13 => rec.attribute13,
4585 x_attribute14 => rec.attribute14,
4586 x_attribute15 => rec.attribute15,
4587 x_attribute16 => rec.attribute16,
4588 x_attribute17 => rec.attribute17,
4589 x_attribute18 => rec.attribute18,
4590 x_attribute19 => rec.attribute19,
4591 x_attribute20 => rec.attribute20,
4592 x_rvsn_id => rec.rvsn_id,
4593 x_award_number_txt => rec.award_number_txt,
4594 x_legacy_record_flag => NULL,
4595 x_adplans_id => rec.adplans_id,
4596 x_lock_award_flag => rec.lock_award_flag,
4597 x_app_trans_num_txt => rec.app_trans_num_txt,
4598 x_awd_proc_status_code => rec.awd_proc_status_code,
4599 x_notification_status_code => rec.notification_status_code,
4600 x_notification_status_date => rec.notification_status_date,
4601 x_publish_in_ss_flag => rec.publish_in_ss_flag
4602 );
4603 END IF;
4604 END LOOP;
4605 END IF;
4606
4607 EXCEPTION
4608 WHEN OTHERS THEN
4609 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
4610 fnd_message.set_token('NAME','IGF_AW_GEN_003.UPDATE_AWARD_APP_TRANS '||SQLERRM);
4611 igs_ge_msg_stack.add;
4612 app_exception.raise_exception;
4613
4614 END update_award_app_trans;
4615
4616 FUNCTION check_coa(
4617 p_base_id IN NUMBER,
4618 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL
4619 ) RETURN BOOLEAN IS
4620 ------------------------------------------------------------------
4621 -- Created by : sjadhav, Oracle India
4622 -- Date created: 4-Dec-2003
4623 --
4624 -- Purpose: Checks if Person has COA
4625 --
4626 --
4627 -- Known limitations/enhancements and/or remarks:
4628 --
4629 -- Change History:
4630 -- Who When What
4631 -------------------------------------------------------------------
4632
4633 BEGIN
4634 IF igf_aw_coa_gen.coa_amount(p_base_id,p_awd_prd_code) IS NULL THEN
4635 RETURN FALSE;
4636 ELSE
4637 RETURN TRUE;
4638 END IF;
4639 EXCEPTION
4640 WHEN OTHERS THEN
4641 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
4642 fnd_message.set_token('NAME','IGF_AW_GEN_003.CHECK_COA '||SQLERRM);
4643 igs_ge_msg_stack.add;
4644 app_exception.raise_exception;
4645
4646 END check_coa;
4647 END igf_aw_gen_003;