[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_REPACKAGE
Source
1 PACKAGE BODY igf_gr_repackage AS
2 /* $Header: IGFGR07B.pls 120.3 2006/08/10 15:59:33 museshad noship $ */
3 ----------------------------------------------------------------------
4 --museshad 10-Aug-2006 Bug 5337555. Build FA 163. TBH Impact.
5 ----------------------------------------------------------------------
6 --sjadhav 06-Nov-2004 FA 149 build, do not insert into igf_gr_rfms_disb for cod year
7 ----------------------------------------------------------------------
8 --ayedubat 13-OCT-04 FA 149 COD-XML Standards build bug # 3416863
9 -- Changed the TBH calls of the packages: igf_aw_awd_disb_pkg and
10 -- igf_gr_rfms_pkg
11 ----------------------------------------------------------------------
12 -- veramach July 2004 FA 151 HR integration (bug # 3709292)
13 -- Impact of obsoleting columns from igf_aw_awd_disb_pkg
14 ----------------------------------------------------------------------
15 -- veramach 12-Mar-2004 Bug 3490915
16 -- in call to igf_gr_rfms_pkg.update_row, changed the
17 -- value of pell_amout to l_offered_amt
18 -- Also, ft_pell_amt is calculated and updated in
19 -- the RFMS record.
20 ----------------------------------------------------------------------
21 -- cdcruz 05-Dec-2003 FA 131 Cod Updates
22 -- Reviewed the Code
23 -- Re-vamped the repackage procedure to reduced multiple checks for each mode
24 ----------------------------------------------------------------------
25 -- veramach 02-Dec-2003 FA 131 Cod Updates
26 -- Adds new procedures log_parameters,cancel_invalid_awards,repackage
27 -- Removes insert_award_t,delete_awd_disb
28 -- Logic existing in repackage_pell moved to repackage
29 ----------------------------------------------------------------------
30 --
31 -- sjadhav 01-Aug-2003 Bug 3062062
32 -- Corrected re-package routine for post_award
33 -- and disbursement deletions
34 ----------------------------------------------------------------------
35 --
36 -- sjadhav, May.29.2002 Bug 2381898
37 -- Added Get Latest Oss Detail Call Before
38 -- Calculating Pell Award
39 ----------------------------------------------------------------------
40 --
41
42 PROCEDURE log_parameters(
43 p_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
44 p_sequence_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
45 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
46 p_persid_grp IN igs_pe_persid_group_all.group_id%TYPE,
47 p_test_run IN VARCHAR2,
48 p_cancel_invalid_award IN VARCHAR2
49 ) AS
50 ------------------------------------------------------------------
51 --Created by : veramach, Oracle India
52 --Date created: 02-DEC-2003
53 --
54 --Purpose:
55 --
56 --
57 --Known limitations/enhancements and/or remarks:
58 --
59 --Change History:
60 --Who When What
61 -------------------------------------------------------------------
62
63 l_param_pass_log igf_lookups_view.meaning%TYPE ;
64 l_awd_yr_log igf_lookups_view.meaning%TYPE ;
65 l_pers_number_log igf_lookups_view.meaning%TYPE ;
66 l_pers_id_grp_log igf_lookups_view.meaning%TYPE ;
67 l_test_run_log igf_lookups_view.meaning%TYPE ;
68 l_cancel_inv_awd_log igf_lookups_view.meaning%TYPE ;
69
70 -- Get person number
71 CURSOR c_person_number(
72 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
73 ) IS
74 SELECT party_number
75 FROM hz_parties parties,
76 igf_ap_fa_base_rec_all fabase
77 WHERE fabase.person_id = parties.party_id
78 AND fabase.base_id = cp_base_id;
79
80 l_person_number hz_parties.party_number%TYPE;
81
82 -- Get alternate code
83 CURSOR c_alternate_code(
84 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
85 cp_seq_number igs_ca_inst_all.sequence_number%TYPE
86 ) IS
87 SELECT alternate_code
88 FROM igs_ca_inst_all
89 WHERE cal_type = cp_cal_type
90 AND sequence_number = cp_seq_number;
91
92 l_alternate_code igs_ca_inst_all.alternate_code%TYPE;
93
94 -- Get get group description for group_id
95 CURSOR c_person_group(
96 cp_persid_grp igs_pe_persid_group_all.group_id%TYPE
97 ) IS
98 SELECT group_cd group_name
99 FROM igs_pe_persid_group_all
100 WHERE group_id = cp_persid_grp;
101
102 l_persid_grp_name c_person_group%ROWTYPE;
103
104 BEGIN
105
106 l_param_pass_log := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS');
107 l_awd_yr_log := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YEAR');
108 l_pers_number_log := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_NUMBER');
109 l_pers_id_grp_log := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_ID_GROUP');
110 l_test_run_log := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','TEST_MODE');
111 l_cancel_inv_awd_log := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','CANCEL_INVALID_AWD');
112
113 fnd_file.new_line(fnd_file.log,2);
114 fnd_file.put_line(fnd_file.log,l_param_pass_log);
115
116 OPEN c_alternate_code(p_cal_type,p_sequence_number);
117 FETCH c_alternate_code INTO l_alternate_code;
118 CLOSE c_alternate_code;
119
120 fnd_file.put_line(fnd_file.log,RPAD(l_awd_yr_log,40) || ' : ' || l_alternate_code);
121
122 OPEN c_person_number(p_base_id);
123 FETCH c_person_number INTO l_person_number;
124 CLOSE c_person_number;
125
126 OPEN c_person_group(p_persid_grp);
127 FETCH c_person_group INTO l_persid_grp_name;
128 CLOSE c_person_group;
129
130 fnd_file.put_line(fnd_file.log,RPAD(l_pers_number_log,40) || ' : ' || l_person_number);
131 fnd_file.put_line(fnd_file.log,RPAD(l_pers_id_grp_log,40) || ' : ' || l_persid_grp_name.group_name);
132 fnd_file.put_line(fnd_file.log,RPAD(l_test_run_log,40) || ' : ' || p_test_run);
133 fnd_file.put_line(fnd_file.log,RPAD(l_cancel_inv_awd_log,40) || ' : ' || p_cancel_invalid_award);
134 fnd_file.put_line(fnd_file.log,RPAD('-',55,'-'));
135 fnd_file.new_line(fnd_file.log,2);
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
140 fnd_message.set_token('NAME','IGF_GE_REPACKAGE.LOG_PARAMETERS '||SQLERRM);
141 igs_ge_msg_stack.add;
142
143 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
144 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_repackage.log_parameters.exception','sql error message:'||SQLERRM);
145 END IF;
146
147 app_exception.raise_exception;
148 END log_parameters;
149
150 PROCEDURE cancel_invalid_award(
151 p_award_id igf_aw_award_all.award_id%TYPE
152 ) AS
153 ------------------------------------------------------------------
154 --Created by : veramach, Oracle India
155 --Date created: 01-DEC-2003
156 --
157 --Purpose: to cancel an existing award and its disbursements
158 --
159 --
160 --Known limitations/enhancements and/or remarks:
161 --
162 --Change History:
163 --Who When What
164 -------------------------------------------------------------------
165
166 -- Get an award
167 CURSOR c_award(
168 cp_award_id igf_aw_award_all.award_id%TYPE
169 ) IS
170 SELECT awd.rowid row_id,
171 awd.*
172 FROM igf_aw_award_all awd
173 WHERE award_id = cp_award_id;
174
175 -- Get disbursements for an award
176 CURSOR c_disb(
177 cp_award_id igf_aw_award_all.award_id%TYPE
178 ) IS
179 SELECT disb.rowid row_id,
180 disb.*
181 FROM igf_aw_awd_disb_all disb
182 WHERE award_id = cp_award_id;
183
184
185
186 BEGIN
187
188 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
189 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.cancel_invalid_award.debug','starting cancel_invalid_award with award_id:'||p_award_id);
190 END IF;
191
192 FOR awd_rec IN c_award(p_award_id) LOOP
193
194 FOR disb_rec IN c_disb(p_award_id) LOOP
195
196 igf_aw_awd_disb_pkg.update_row(
197 x_rowid => disb_rec.row_id,
198 x_award_id => disb_rec.award_id,
199 x_disb_num => disb_rec.disb_num,
200 x_tp_cal_type => disb_rec.tp_cal_type,
201 x_tp_sequence_number => disb_rec.tp_sequence_number,
202 x_disb_gross_amt => disb_rec.disb_gross_amt,
203 x_fee_1 => disb_rec.fee_1,
204 x_fee_2 => disb_rec.fee_2,
205 x_disb_net_amt => disb_rec.disb_net_amt,
206 x_disb_date => disb_rec.disb_date,
207 x_trans_type => 'C',
208 x_elig_status => disb_rec.elig_status,
209 x_elig_status_date => disb_rec.elig_status_date,
210 x_affirm_flag => disb_rec.affirm_flag,
211 x_hold_rel_ind => disb_rec.hold_rel_ind,
212 x_manual_hold_ind => disb_rec.manual_hold_ind,
213 x_disb_status => disb_rec.disb_status,
214 x_disb_status_date => disb_rec.disb_status_date,
215 x_late_disb_ind => disb_rec.late_disb_ind,
216 x_fund_dist_mthd => disb_rec.fund_dist_mthd,
217 x_prev_reported_ind => disb_rec.prev_reported_ind,
218 x_fund_release_date => disb_rec.fund_release_date,
219 x_fund_status => disb_rec.fund_status,
220 x_fund_status_date => disb_rec.fund_status_date,
221 x_fee_paid_1 => disb_rec.fee_paid_1,
222 x_fee_paid_2 => disb_rec.fee_paid_2,
223 x_cheque_number => disb_rec.cheque_number,
224 x_ld_cal_type => disb_rec.ld_cal_type,
225 x_ld_sequence_number => disb_rec.ld_sequence_number,
226 x_disb_accepted_amt => 0,
227 x_disb_paid_amt => 0,
228 x_rvsn_id => disb_rec.rvsn_id,
229 x_int_rebate_amt => disb_rec.int_rebate_amt,
230 x_force_disb => disb_rec.force_disb,
231 x_min_credit_pts => disb_rec.min_credit_pts,
232 x_disb_exp_dt => disb_rec.disb_exp_dt,
233 x_verf_enfr_dt => disb_rec.verf_enfr_dt,
234 x_fee_class => disb_rec.fee_class,
235 x_show_on_bill => disb_rec.show_on_bill,
236 x_mode => 'R',
237 x_attendance_type_code => disb_rec.attendance_type_code,
238 x_base_attendance_type_code => disb_rec.base_attendance_type_code,
239 x_payment_prd_st_date => disb_rec.payment_prd_st_date,
240 x_change_type_code => disb_rec.change_type_code,
241 x_fund_return_mthd_code => disb_rec.fund_return_mthd_code,
242 x_direct_to_borr_flag => disb_rec.direct_to_borr_flag
243 );
244
245 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
246 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.cancel_invalid_award.debug','updated disbursement number '||disb_rec.disb_num);
247 END IF;
248
249 END LOOP;
250
251 igf_aw_award_pkg.update_row(
252 x_rowid => awd_rec.row_id,
253 x_award_id => awd_rec.award_id,
254 x_fund_id => awd_rec.fund_id,
255 x_base_id => awd_rec.base_id,
256 x_offered_amt => awd_rec.offered_amt,
257 x_accepted_amt => 0,
258 x_paid_amt => 0,
259 x_packaging_type => awd_rec.packaging_type,
260 x_batch_id => awd_rec.batch_id,
261 x_manual_update => awd_rec.manual_update,
262 x_rules_override => awd_rec.rules_override,
263 x_award_date => awd_rec.award_date,
264 x_award_status => 'CANCELLED',
265 x_attribute_category => awd_rec.attribute_category,
266 x_attribute1 => awd_rec.attribute1,
267 x_attribute2 => awd_rec.attribute2,
268 x_attribute3 => awd_rec.attribute3,
269 x_attribute4 => awd_rec.attribute4,
270 x_attribute5 => awd_rec.attribute5,
271 x_attribute6 => awd_rec.attribute6,
272 x_attribute7 => awd_rec.attribute7,
273 x_attribute8 => awd_rec.attribute8,
274 x_attribute9 => awd_rec.attribute9,
275 x_attribute10 => awd_rec.attribute10,
276 x_attribute11 => awd_rec.attribute11,
277 x_attribute12 => awd_rec.attribute12,
278 x_attribute13 => awd_rec.attribute13,
279 x_attribute14 => awd_rec.attribute14,
280 x_attribute15 => awd_rec.attribute15,
281 x_attribute16 => awd_rec.attribute16,
282 x_attribute17 => awd_rec.attribute17,
283 x_attribute18 => awd_rec.attribute18,
284 x_attribute19 => awd_rec.attribute19,
285 x_attribute20 => awd_rec.attribute20,
286 x_rvsn_id => awd_rec.rvsn_id,
287 x_alt_pell_schedule => awd_rec.alt_pell_schedule,
288 x_mode => 'R',
289 x_award_number_txt => awd_rec.award_number_txt,
290 x_legacy_record_flag => awd_rec.legacy_record_flag,
291 x_adplans_id => awd_rec.adplans_id,
292 x_lock_award_flag => awd_rec.lock_award_flag,
293 x_app_trans_num_txt => awd_rec.app_trans_num_txt,
294 x_awd_proc_status_code => awd_rec.awd_proc_status_code,
295 x_notification_status_code => awd_rec.notification_status_code,
296 x_notification_status_date => awd_rec.notification_status_date,
297 x_publish_in_ss_flag => awd_rec.publish_in_ss_flag
298 );
299 END LOOP;
300
301 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
302 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.cancel_invalid_award.debug','finsihed cancel_invalid_award for award_id:'||p_award_id);
303 END IF;
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
308 fnd_message.set_token('NAME','IGF_GR_REPACKAGE.CANCEL_INVALID_AWARD ' || SQLERRM);
309 igs_ge_msg_stack.add;
310
311 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
312 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_repackage.cancel_invalid_award.exception','sql error message:'||SQLERRM);
313 END IF;
314
315 app_exception.raise_exception;
316
317 END cancel_invalid_award;
318
319
320 PROCEDURE repackage(
321 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
322 ) AS
323 ------------------------------------------------------------------
324 --Created by : veramach, Oracle India
325 --Date created:
326 --
327 --Purpose:
328 --
329 --
330 --Known limitations/enhancements and/or remarks:
331 --
332 --Change History:
333 --Who When What
334 -------------------------------------------------------------------
335
336 CURSOR c_stud_det(
337 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
338 ) IS
339 SELECT awd.base_id,
340 awd.award_id,
341 awd.fund_id,
342 awd.award_status,
343 fmast.fund_code
344 FROM igf_aw_fund_cat_all fcat,
345 igf_aw_fund_mast_all fmast,
346 igf_aw_award_all awd
347 WHERE fcat.fed_fund_code = 'PELL'
348 AND fcat.fund_code = fmast.fund_code
349 AND fmast.fund_id = awd.fund_id
350 AND awd.base_id = cp_base_id
351 AND NVL(awd.lock_award_flag,'N') = 'N';
352
353 -- Cursor to get the RFMS records for the particular award id
354 CURSOR c_rfms(
355 cp_award_id igf_aw_award_all.award_id%TYPE
356 ) IS
357 SELECT rfms.*
358 FROM igf_gr_rfms rfms
359 WHERE award_id = cp_award_id;
360
361 -- Cursor to get the RFMS Disbursement records for the RFMS Records
362 CURSOR c_rfms_disb(
363 cp_origination_id igf_gr_rfms_disb_v.origination_id%TYPE
364 ) IS
365 SELECT row_id,
366 disb_ack_act_status
367 FROM igf_gr_rfms_disb rfmd
368 WHERE origination_id = cp_origination_id;
369
370 -- Cursor to get the awards
371 --modified in FA131 to choose adplans_id,offered_amt also
372 CURSOR c_award(
373 cp_award_id igf_aw_award_all.award_id%TYPE
374 ) IS
375 SELECT awd.rowid row_id,
376 awd.*
377 FROM igf_aw_award_all awd
378 WHERE awd.award_id = cp_award_id
379 AND awd.award_status IN ('OFFERED','ACCEPTED');
380
381 lc_stud_det c_stud_det%ROWTYPE;
382 lc_rfms c_rfms%ROWTYPE;
383 lc_rfms_disb c_rfms_disb%ROWTYPE;
384 lc_award c_award%ROWTYPE;
385
386 l_offered_amt igf_aw_fund_mast_all.offered_amt%TYPE;
387 l_process igf_aw_award_t.process_id%TYPE;
388
389 e_next_record EXCEPTION;
390
391
392 -- Get active ISIR
393 CURSOR c_active_isir(
394 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
395 ) IS
396 SELECT isir_id
397 FROM igf_ap_isir_matched_all
398 WHERE base_id = cp_base_id
399 AND active_isir = 'Y';
400 l_active_isir c_active_isir%ROWTYPE;
401
402 -- Get payment isir
403 CURSOR c_payment_isir(
404 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
405 ) IS
406 SELECT isir_id
407 FROM igf_ap_isir_matched_all
408 WHERE base_id = cp_base_id
409 AND payment_isir = 'Y';
410 l_payment_isir c_payment_isir%ROWTYPE;
411
412 lb_valid_pell BOOLEAN := FALSE;
413
414 l_pell_tab igf_gr_pell_calc.pell_tab := igf_gr_pell_calc.pell_tab();
415 lv_message fnd_new_messages.message_text%TYPE := NULL;
416 lv_return_status VARCHAR2(1) := NULL;
417
418 lv_message_ft fnd_new_messages.message_text%TYPE := NULL;
419 lv_return_status_ft VARCHAR2(1) := NULL;
420 l_pell_ft_aid igf_gr_rfms_all.pell_amount%TYPE := NULL;
421 l_pell_aid igf_gr_rfms_all.ft_pell_amount%TYPE := NULL;
422
423
424 l_log_message VARCHAR2(4000);
425
426 -- Get alternate code
427 CURSOR c_alternate_code(
428 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
429 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE
430 ) IS
431 SELECT alternate_code
432 FROM igs_ca_inst_all
433 WHERE cal_type = cp_cal_type
434 AND sequence_number = cp_sequence_number;
435 l_alternate_code c_alternate_code%ROWTYPE;
436
437 -- Get pell setup values
438 CURSOR c_pell_setup(
439 cp_pell_seq_id igf_gr_pell_setup.pell_seq_id%TYPE
440 ) IS
441 SELECT *
442 FROM igf_gr_pell_setup
443 WHERE pell_seq_id = cp_pell_seq_id;
444 l_pell_setup c_pell_setup%ROWTYPE;
445
446
447 l_rfmsd_rec igf_gr_rfms_disb%ROWTYPE;
448
449 lv_row_id VARCHAR2(25);
450 lv_rfmd_id NUMBER;
451
452 -- Get disbursements which should be cancelled
453 CURSOR c_disb_cancel(
454 cp_award_id igf_aw_award_all.award_id%TYPE,
455 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
456 ) IS
457 SELECT *
458 FROM igf_aw_awd_disb
459 WHERE award_id = cp_award_id
460 AND disb_num > cp_disb_num;
461
462 -- Get context disbursement record
463 CURSOR c_disb(
464 cp_award_id igf_aw_award_all.award_id%TYPE,
465 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
466 ) IS
467 SELECT disb.rowid row_id,
468 disb.*
469 FROM igf_aw_awd_disb_all disb
470 WHERE
471 award_id = cp_award_id AND
472 disb_num = cp_disb_num ;
473
474 l_disb_rec c_disb%ROWTYPE;
475 l_last_disb_num NUMBER;
476
477 CURSOR c_rfmb_disb(
478 cp_origination_id igf_gr_rfms_disb_v.origination_id%TYPE,
479 cp_disb_num igf_gr_rfms_disb_all.disb_ref_num%TYPE
480 ) IS
481 SELECT rfmd.rowid row_id,
482 rfmd.*
483 FROM igf_gr_rfms_disb_all rfmd
484 WHERE
485 rfmd.origination_id = cp_origination_id AND
486 rfmd.disb_ref_num = cp_disb_num ;
487
488
489 CURSOR c_rfmd_extra(
490 cp_origination_id igf_gr_rfms_disb_v.origination_id%TYPE,
491 cp_disb_num igf_gr_rfms_disb_all.disb_ref_num%TYPE
492 ) IS
493 SELECT rfmd.rowid row_id,
494 rfmd.*
495 FROM igf_gr_rfms_disb_all rfmd
496 WHERE
497 rfmd.origination_id = cp_origination_id AND
498 rfmd.disb_ref_num > cp_disb_num ;
499
500 l_rfmb_disb_rec c_rfmb_disb%ROWTYPE;
501
502 lv_rfms_exits_flag BOOLEAN ;
503
504 l_pell_disb_cnt NUMBER;
505 lv_rowid VARCHAR2(25);
506 l_pell_seq_id igf_gr_pell_setup_all.pell_seq_id%TYPE;
507 l_pell_schedule_code igf_aw_award_all.alt_pell_schedule%TYPE;
508
509 -- Get person number
510 CURSOR c_person_number(
511 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
512 ) IS
513 SELECT party_number
514 FROM hz_parties parties,
515 igf_ap_fa_base_rec_all fabase
516 WHERE fabase.person_id = parties.party_id
517 AND fabase.base_id = cp_base_id;
518
519 l_person_number hz_parties.party_number%TYPE;
520
521 lv_msg_text VARCHAR2(2000);
522 ln_msg_index NUMBER;
523
524 BEGIN
525
526 fnd_file.new_line(fnd_file.log,1);
527 fnd_message.set_name('IGF', 'IGF_AW_PROCESS_STUD');
528 OPEN c_person_number(p_base_id);
529 FETCH c_person_number INTO l_person_number;
530 fnd_message.set_token('STUD',l_person_number);
531 fnd_file.put_line(fnd_file.log, fnd_message.get);
532
533 OPEN c_stud_det(
534 p_base_id
535 );
536 FETCH c_stud_det INTO lc_stud_det;
537 IF c_stud_det%NOTFOUND THEN
538 CLOSE c_stud_det;
539
540 fnd_message.set_name('IGF', 'IGF_AW_NO_PELL_STUDENT');
541 fnd_file.put_line(fnd_file.log, fnd_message.get);
542 RETURN;
543 END IF;
544
545 --
546 -- Logic : For each student detail records fetch the record from interface
547 -- and rfms and rfms disbursement tables. If records doesnot exists
548 -- raise a proper exception and skip the record. Also check
549 -- if the origination action code is
550 -- sent. If both the case then
551 -- raise the exception and skip the record. If the record exists in
552 -- RFMS AND RFMS Disbursement tables then delete from it.
553 --
554
555
556 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
557 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','***********person_number:'||l_person_number||'********');
558 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','award_id:'||lc_stud_det.award_id);
559 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','fund_id:'||lc_stud_det.fund_id);
560 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','award_status:'||lc_stud_det.award_status);
561 END IF;
562
563 SAVEPOINT sv_student;
564 FOR lc_rfms IN c_rfms(lc_stud_det.award_id) LOOP
565
566 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lc_rfms.orig_action_code:'||lc_rfms.orig_action_code);
568 END IF;
569
570
571 FOR lc_rfms_disb IN c_rfms_disb(lc_rfms.origination_id) LOOP
572
573
574 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
575 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lc_rfms_disb.disb_ack_act_status:'||lc_rfms_disb.disb_ack_act_status);
576 END IF;
577
578 IF lc_rfms_disb.disb_ack_act_status NOT IN ('R','N') THEN
579 fnd_message.set_name('IGF', 'IGF_GR_DISB_SENT_NO_RECALC');
580 fnd_file.put_line( fnd_file.log, fnd_message.get);
581
582 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
583 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','pell orig sent...erroring out');
584 END IF;
585
586 RAISE e_next_record;
587
588 END IF;
589
590 END LOOP;
591
592 END LOOP;
593
594 IF c_award%ISOPEN THEN
595 CLOSE c_award;
596 END IF;
597
598 OPEN c_award(
599 lc_stud_det.award_id
600 );
601 FETCH c_award INTO lc_award;
602
603 IF c_award%NOTFOUND THEN
604 fnd_message.set_name('IGF', 'IGF_AW_NO_AWARD_REC');
605 fnd_file.put_line( fnd_file.log, fnd_message.get);
606 CLOSE c_award;
607
608 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
609 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','no PELL award for student');
610 END IF;
611
612 RAISE e_next_record;
613 END IF;
614
615 CLOSE c_award;
616
617 --if student does not have a valid ISIR or does not have COA defined, we have to error out
618 lv_return_status := NULL;
619 igs_ge_msg_stack.initialize;
620 igf_gr_pell_calc.pell_elig(p_base_id,lv_return_status);
621
622 IF NVL(lv_return_status,'S') = 'E' THEN
623 lb_valid_pell := FALSE;
624 ELSE
625 lb_valid_pell := TRUE;
626 END IF;
627
628 IF NOT lb_valid_pell THEN
629
630 --log messages
631 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
632 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','pell elig failed for :'||l_person_number);
633 END IF;
634
635 IF igs_ge_msg_stack.count_msg > 0 THEN
636 lv_message := NULL;
637 FOR i IN 1..igs_ge_msg_stack.count_msg
638 LOOP
639 igs_ge_msg_stack.get(i,'F',lv_msg_text, ln_msg_index);
640 lv_message := lv_message ||
641 fnd_global.newline ||
642 lv_msg_text;
643 END LOOP;
644 fnd_file.put_line(fnd_file.log, lv_message);
645 END IF;
646
647
648 IF g_test_run = 'Y' THEN
649
650 IF g_cancel_invalid_awds = 'Y' THEN
651 --cancel the award only if the user selects to cancel invalid awards
652
653 fnd_message.set_name('IGF','IGF_AW_AWARD_CANCELLED');
654 fnd_message.set_token('AWD',lc_stud_det.award_id);
655 fnd_message.set_token('FUND',lc_stud_det.fund_code);
656 fnd_file.put_line(fnd_file.log,fnd_message.get);
657
658 ELSE -- Test run but not in Cancel Mode
659 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
660 fnd_file.put_line(fnd_file.log,fnd_message.get);
661 fnd_file.new_line(fnd_file.log,1);
662
663 END IF;
664
665
666 ELSE -- Running in Actual Mode
667
668 IF g_cancel_invalid_awds = 'Y' THEN
669 --cancel the award only if the user selects to cancel invalid awards
670
671 fnd_message.set_name('IGF','IGF_AW_AWARD_CANCELLED');
672 fnd_message.set_token('AWD',lc_stud_det.award_id);
673 fnd_message.set_token('FUND',lc_stud_det.fund_code);
674 fnd_file.put_line(fnd_file.log,fnd_message.get);
675
676 cancel_invalid_award(lc_stud_det.award_id);
677 COMMIT;
678 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
679 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','cancelled invalid award '||lc_stud_det.award_id);
680 END IF;
681
682 ELSE -- Actual Mode but Cancel Awards not set to Yes
683
684 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
685 fnd_file.put_line(fnd_file.log,fnd_message.get);
686 fnd_file.new_line(fnd_file.log,1);
687
688 END IF;
689
690 END IF;
691
692 RETURN;
693 END IF;
694
695 --check is the active isir is the payment isir.if not error out
696 OPEN c_payment_isir(p_base_id);
697 FETCH c_payment_isir INTO l_payment_isir;
698 CLOSE c_payment_isir;
699
700 OPEN c_active_isir(p_base_id);
701 FETCH c_active_isir INTO l_active_isir;
702 CLOSE c_active_isir;
703
704 IF NVL(l_payment_isir.isir_id, -1) <> NVL(l_active_isir.isir_id, -2) THEN
705
706 --log messages
707 fnd_message.set_name('IGF','IGF_AP_PELL_ISIR_CHK');
708 fnd_file.put_line(fnd_file.log,fnd_message.get);
709 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
710 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','payment isir is not active isir..error');
711 END IF;
712
713 IF g_test_run <> 'Y' AND g_cancel_invalid_awds = 'Y' THEN
714 --cancel award and disbursements
715 fnd_message.set_name('IGF','IGF_AP_PELL_ISIR_CHK');
716 fnd_file.put_line(fnd_file.log,fnd_message.get);
717
718 fnd_message.set_name('IGF','IGF_AW_AWARD_CANCELLED');
719 fnd_message.set_token('AWD',lc_stud_det.award_id);
720 fnd_message.set_token('FUND',lc_stud_det.fund_code);
721 fnd_file.put_line(fnd_file.log,fnd_message.get);
722
723 cancel_invalid_award(lc_stud_det.award_id);
724 COMMIT;
725 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
726 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','cancelled invalid award '||lc_stud_det.award_id);
727 END IF;
728
729 ELSE
730 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
731 fnd_file.put_line(fnd_file.log,fnd_message.get);
732 fnd_file.new_line(fnd_file.log,1);
733 END IF;
734
735 RETURN ;
736
737 END IF; -- Payment ISIR not equal to Active ISIR
738
739 l_offered_amt := NULL;
740 -- Call the procedure to get the offered amount
741 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
742 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lc_stud_det.fund_id:'||lc_stud_det.fund_id);
743 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lc_award.adplans_id:'||lc_award.adplans_id);
744 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lc_stud_det.base_id:'||lc_stud_det.base_id);
745 END IF;
746
747 lv_message := NULL;
748 l_pell_seq_id := NULL;
749
750 l_pell_tab := igf_gr_pell_calc.pell_tab();
751 igf_gr_pell_calc.calc_pell(
752 cp_fund_id => lc_stud_det.fund_id,
753 cp_plan_id => lc_award.adplans_id,
754 cp_base_id => lc_stud_det.base_id,
755 cp_aid => l_offered_amt,
756 cp_pell_tab => l_pell_tab,
757 cp_return_status => lv_return_status,
758 cp_message => lv_message,
759 cp_called_from => 'PACKAGING',
760 cp_pell_seq_id => l_pell_seq_id,
761 cp_pell_schedule_code => l_pell_schedule_code
762 );
763
764 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
765 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lv_return_status:'||lv_return_status);
766 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lv_message:'||lv_message);
767 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_pell_seq_id:'||l_pell_seq_id);
768 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_offered_amt returned by the wrapper:'||l_offered_amt);
769 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_pell_schedule_code:'||l_pell_schedule_code);
770 END IF;
771
772 -- ========== Calculation Complete at this Point of Time ===========---
773 -- ========== Now do Table Insert ===========---
774
775 -- Check for Failure
776 IF lv_return_status <> 'S' THEN
777 fnd_file.put_line(fnd_file.log,lv_message);
778 RAISE e_next_record;
779 END IF;
780
781 -- Check for new pall amount match in which case abort processing
782 IF l_offered_amt = lc_award.offered_amt THEN
783 fnd_message.set_name('IGF','IGF_GR_PELL_VALID_PELL_AWARD');
784 fnd_file.put_line(fnd_file.log,fnd_message.get);
785 RAISE e_next_record;
786 END IF;
787
788 -- Check if Origination Exists
789 OPEN c_rfms(lc_stud_det.award_id);
790 FETCH c_rfms INTO lc_rfms;
791 IF c_rfms%FOUND THEN
792
793 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
794 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','Found Pell Origination.Finding FT_PELL_AMT');
795 END IF;
796
797 igf_gr_pell_calc.calc_ft_max_pell(
798 cp_base_id => lc_stud_det.base_id,
799 cp_cal_type => lc_rfms.ci_cal_type,
800 cp_sequence_number => lc_rfms.ci_sequence_number,
801 cp_flag => 'FULL_TIME',
802 cp_aid => l_pell_aid,
803 cp_ft_aid => l_pell_ft_aid,
804 cp_return_status => lv_return_status_ft,
805 cp_message => lv_message_ft
806 );
807
808 -- Check for Failure
809 IF lv_return_status_ft <> 'S' THEN
810 fnd_file.put_line(fnd_file.log,lv_message_ft);
811 RAISE e_next_record;
812 END IF;
813
814 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
815 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','Full time PELL Amount:'||l_pell_ft_aid);
816 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','updating igf_gr_rfms with pell_amount:'||l_offered_amt);
817 END IF;
818
819 -- Update the Origination Record
820 igf_gr_rfms_pkg.update_row(
821 x_rowid => lc_rfms.row_id,
822 x_origination_id => lc_rfms.origination_id,
823 x_ci_cal_type => lc_rfms.ci_cal_type,
824 x_ci_sequence_number => lc_rfms.ci_sequence_number,
825 x_base_id => lc_rfms.base_id,
826 x_award_id => lc_rfms.award_id,
827 x_rfmb_id => lc_rfms.rfmb_id,
828 x_sys_orig_ssn => lc_rfms.sys_orig_ssn,
829 x_sys_orig_name_cd => lc_rfms.sys_orig_name_cd,
830 x_transaction_num => lc_rfms.transaction_num,
831 x_efc => lc_rfms.efc,
832 x_ver_status_code => lc_rfms.ver_status_code,
833 x_secondary_efc => lc_rfms.secondary_efc,
834 x_secondary_efc_cd => lc_rfms.secondary_efc_cd,
835 x_pell_amount => l_offered_amt,
836 x_pell_profile => lc_rfms.pell_profile,
837 x_enrollment_status => lc_rfms.enrollment_status,
838 x_enrollment_dt => lc_rfms.enrollment_dt,
839 x_coa_amount => lc_rfms.coa_amount,
840 x_academic_calendar => lc_rfms.academic_calendar,
841 x_payment_method => lc_rfms.payment_method,
842 x_total_pymt_prds => lc_rfms.total_pymt_prds,
843 x_incrcd_fed_pell_rcp_cd => lc_rfms.incrcd_fed_pell_rcp_cd,
844 x_attending_campus_id => lc_rfms.attending_campus_id,
845 x_est_disb_dt1 => lc_rfms.est_disb_dt1,
846 x_orig_action_code => lc_rfms.orig_action_code,
847 x_orig_status_dt => lc_rfms.orig_status_dt,
848 x_orig_ed_use_flags => lc_rfms.orig_ed_use_flags,
849 x_ft_pell_amount => l_pell_ft_aid,
850 x_prev_accpt_efc => lc_rfms.prev_accpt_efc,
851 x_prev_accpt_tran_no => lc_rfms.prev_accpt_tran_no,
852 x_prev_accpt_sec_efc_cd => lc_rfms.prev_accpt_sec_efc_cd,
853 x_prev_accpt_coa => lc_rfms.prev_accpt_coa,
854 x_orig_reject_code => lc_rfms.orig_reject_code,
855 x_wk_inst_time_calc_pymt => lc_rfms.wk_inst_time_calc_pymt,
856 x_wk_int_time_prg_def_yr => lc_rfms.wk_int_time_prg_def_yr,
857 x_cr_clk_hrs_prds_sch_yr => lc_rfms.cr_clk_hrs_prds_sch_yr,
858 x_cr_clk_hrs_acad_yr => lc_rfms.cr_clk_hrs_acad_yr,
859 x_inst_cross_ref_cd => lc_rfms.inst_cross_ref_cd,
860 x_low_tution_fee => lc_rfms.low_tution_fee,
861 x_rec_source => lc_rfms.rec_source,
862 x_pending_amount => lc_rfms.pending_amount,
863 x_mode => 'R',
864 x_birth_dt => lc_rfms.birth_dt,
865 x_last_name => lc_rfms.last_name,
866 x_first_name => lc_rfms.first_name,
867 x_middle_name => lc_rfms.middle_name,
868 x_current_ssn => lc_rfms.current_ssn,
869 x_legacy_record_flag => lc_rfms.legacy_record_flag,
870 x_reporting_pell_cd => lc_rfms.rep_pell_id,
871 x_rep_entity_id_txt => lc_rfms.rep_entity_id_txt,
872 x_atd_entity_id_txt => lc_rfms.atd_entity_id_txt,
873 x_note_message => lc_rfms.note_message,
874 x_full_resp_code => lc_rfms.full_resp_code,
875 x_document_id_txt => lc_rfms.document_id_txt
876 );
877
878 lv_rfms_exits_flag := TRUE ;
879 ELSE
880 lv_rfms_exits_flag := FALSE ;
881 END IF;
882 CLOSE c_rfms;
883
884 IF igf_aw_packng_subfns.is_over_award_occured(lc_stud_det.base_id) THEN
885 /*
886 Since PELL is an entitlement, we should not insert overaward holds on the award.
887 we show a message to the user saying that this award will result in an overaward, but we are not
888 inserting overaward holds as the fund is an entitlement
889 */
890 fnd_message.set_name('IGF','IGF_AW_ENTITLE_OVAWD');
891 fnd_message.set_token('FUND_CODE',lc_stud_det.fund_code);
892 fnd_message.set_token('AWD',TO_CHAR(l_offered_amt));
893 fnd_file.put_line(fnd_file.log,fnd_message.get);
894 END IF;
895
896 -- Loop thru the disbursements and post data
897 fnd_file.put_line(fnd_file.log, RPAD('-',210,'-'));
898 -- fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','DISBURSEMENT_NUMBER'), 18, ' '));
899 fnd_file.put(fnd_file.log, RPAD('#', 3, ' '));
900 fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','DISBURSEMENT_DATE'), 30, ' '));
901 fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAD_CALENDAR'), 30, ' '));
902 fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','TEACHING_CALENDAR'), 30, ' '));
903 fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','OFFERED_AMT'), 30, ' '));
904 fnd_file.put(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','ACCEPTED_AMT'), 30, ' '));
905 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','BASE_ATTEND_TYPE'), 30, ' '));
906 fnd_file.put_line(fnd_file.log, RPAD('-',210,'-'));
907
908 FOR i IN 1..l_pell_tab.COUNT
909 LOOP
910 l_log_message := RPAD(TO_CHAR(l_pell_tab(i).sl_number) ,3,' ');
911 l_log_message := l_log_message || RPAD(TO_CHAR(l_pell_tab(i).disb_dt),30,' ');
912
913 OPEN c_alternate_code(l_pell_tab(i).ld_cal_type,l_pell_tab(i).ld_sequence_number);
914 FETCH c_alternate_code INTO l_alternate_code;
915 CLOSE c_alternate_code;
916
917 l_log_message := l_log_message || RPAD(l_alternate_code.alternate_code,30,' ');
918
919 OPEN c_alternate_code(l_pell_tab(i).tp_cal_type,l_pell_tab(i).tp_sequence_number);
920 FETCH c_alternate_code INTO l_alternate_code;
921 CLOSE c_alternate_code;
922
923 l_log_message := l_log_message || RPAD(l_alternate_code.alternate_code,30,' ');
924 l_log_message := l_log_message || RPAD(TO_CHAR(l_pell_tab(i).offered_amt) ,30,' ');
925 l_log_message := l_log_message || RPAD(TO_CHAR(l_pell_tab(i).accepted_amt) ,30,' ');
926 l_log_message := l_log_message || RPAD(igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT',l_pell_tab(i).base_attendance_type_code) ,30,' ');
927
928 fnd_file.put_line(fnd_file.log, l_log_message);
929 --update the disbursement here, if it exists
930 --else insert the disbursement
931
932 -- Update only if run in the the Non Test Mode
933 IF NVL(g_test_run,'N') <> 'Y' THEN
934
935 -- Check if a Disbursement Exits for the context Number
936 OPEN c_disb(lc_stud_det.award_id,l_pell_tab(i).sl_number);
937 FETCH c_disb INTO l_disb_rec;
938 IF c_disb%FOUND THEN
939
940
941 -- Update the Award Disbursement Table
942 igf_aw_awd_disb_pkg.update_row(
943 x_rowid => l_disb_rec.row_id,
944 x_award_id => lc_stud_det.award_id,
945 x_disb_num => l_pell_tab(i).sl_number,
946 x_tp_cal_type => l_pell_tab(i).tp_cal_type,
947 x_tp_sequence_number => l_pell_tab(i).tp_sequence_number,
948 x_disb_gross_amt => l_pell_tab(i).offered_amt,
949 x_fee_1 => l_disb_rec.fee_1,
950 x_fee_2 => l_disb_rec.fee_2,
951 x_disb_net_amt => l_pell_tab(i).accepted_amt,
952 x_disb_date => l_pell_tab(i).disb_dt,
953 x_trans_type => l_disb_rec.trans_type,
954 x_elig_status => l_disb_rec.elig_status,
955 x_elig_status_date => l_disb_rec.elig_status_date,
956 x_affirm_flag => l_disb_rec.affirm_flag,
957 x_hold_rel_ind => l_disb_rec.hold_rel_ind,
958 x_manual_hold_ind => l_disb_rec.manual_hold_ind,
959 x_disb_status => l_disb_rec.disb_status,
960 x_disb_status_date => l_disb_rec.disb_status_date,
961 x_late_disb_ind => l_disb_rec.late_disb_ind,
962 x_fund_dist_mthd => l_disb_rec.fund_dist_mthd,
963 x_prev_reported_ind => l_disb_rec.prev_reported_ind,
964 x_fund_release_date => l_disb_rec.fund_release_date,
965 x_fund_status => l_disb_rec.fund_status,
966 x_fund_status_date => l_disb_rec.fund_status_date,
967 x_fee_paid_1 => l_disb_rec.fee_paid_1,
968 x_fee_paid_2 => l_disb_rec.fee_paid_2,
969 x_cheque_number => l_disb_rec.cheque_number,
970 x_ld_cal_type => l_pell_tab(i).ld_cal_type,
971 x_ld_sequence_number => l_pell_tab(i).ld_sequence_number,
972 x_disb_accepted_amt => l_pell_tab(i).accepted_amt,
973 x_disb_paid_amt => l_disb_rec.disb_paid_amt,
974 x_rvsn_id => l_disb_rec.rvsn_id,
975 x_int_rebate_amt => l_disb_rec.int_rebate_amt,
976 x_force_disb => l_disb_rec.force_disb,
977 x_min_credit_pts => l_pell_tab(i).min_credit_pts,
978 x_disb_exp_dt => l_pell_tab(i).disb_exp_dt,
979 x_verf_enfr_dt => l_pell_tab(i).verf_enfr_dt,
980 x_fee_class => l_disb_rec.fee_class,
981 x_show_on_bill => l_pell_tab(i).show_on_bill,
982 x_mode => 'R',
983 x_attendance_type_code => l_pell_tab(i).attendance_type_code,
984 x_base_attendance_type_code => l_pell_tab(i).base_attendance_type_code,
985 x_payment_prd_st_date => l_disb_rec.payment_prd_st_date,
986 x_change_type_code => l_disb_rec.change_type_code,
987 x_fund_return_mthd_code => l_disb_rec.fund_return_mthd_code,
988 x_direct_to_borr_flag => l_disb_rec.direct_to_borr_flag
989 );
990
991
992 ELSE -- Disbursement Does not Exist ..So Insert it
993 --
994
995 lv_row_id := NULL;
996
997 igf_aw_awd_disb_pkg.insert_row(
998 x_rowid => lv_row_id,
999 x_award_id => lc_stud_det.award_id,
1000 x_disb_num => l_pell_tab(i).sl_number,
1001 x_tp_cal_type => l_pell_tab(i).tp_cal_type,
1002 x_tp_sequence_number => l_pell_tab(i).tp_sequence_number,
1003 x_disb_gross_amt => l_pell_tab(i).offered_amt,
1004 x_fee_1 => NULL,
1005 x_fee_2 => NULL,
1006 x_disb_net_amt => l_pell_tab(i).accepted_amt,
1007 x_disb_date => l_pell_tab(i).disb_dt,
1008 x_trans_type => 'P',
1009 x_elig_status => 'N',
1010 x_elig_status_date => NULL,
1011 x_affirm_flag => 'N',
1012 x_hold_rel_ind => NULL,
1013 x_manual_hold_ind => 'N',
1014 x_disb_status => NULL,
1015 x_disb_status_date => NULL,
1016 x_late_disb_ind => NULL,
1017 x_fund_dist_mthd => 'E',
1018 x_prev_reported_ind => 'N',
1019 x_fund_release_date => NULL,
1020 x_fund_status => NULL,
1021 x_fund_status_date => NULL,
1022 x_fee_paid_1 => 0,
1023 x_fee_paid_2 => 0,
1024 x_cheque_number => NULL,
1025 x_ld_cal_type => l_pell_tab(i).ld_cal_type,
1026 x_ld_sequence_number => l_pell_tab(i).ld_sequence_number,
1027 x_disb_accepted_amt => l_pell_tab(i).accepted_amt,
1028 x_disb_paid_amt => 0,
1029 x_rvsn_id => NULL,
1030 x_int_rebate_amt => 0,
1031 x_force_disb => NULL,
1032 x_min_credit_pts => l_pell_tab(i).min_credit_pts,
1033 x_disb_exp_dt => l_pell_tab(i).disb_exp_dt,
1034 x_verf_enfr_dt => l_pell_tab(i).verf_enfr_dt,
1035 x_fee_class => NULL,
1036 x_show_on_bill => l_pell_tab(i).show_on_bill,
1037 x_mode => 'R',
1038 x_attendance_type_code => l_pell_tab(i).attendance_type_code,
1039 x_base_attendance_type_code => l_pell_tab(i).base_attendance_type_code,
1040 x_payment_prd_st_date => NULL,
1041 x_change_type_code => NULL,
1042 x_fund_return_mthd_code => NULL,
1043 x_direct_to_borr_flag => 'N'
1044 );
1045
1046 END IF; -- Disbursement Record Insert
1047 CLOSE c_disb;
1048 END IF; -- Test Mode Flag Not Set
1049 -- Check if a RFMS Disbursement Exits for the context Number
1050 IF lv_rfms_exits_flag THEN
1051
1052 -- Update only if run in the the Non Test Mode
1053 IF NVL(g_test_run,'N') <> 'Y' THEN
1054 IF NOT igf_sl_dl_validation.check_full_participant (lc_rfms.ci_cal_type,lc_rfms.ci_sequence_number,'PELL') THEN
1055 OPEN c_rfmb_disb(lc_rfms.origination_id,i);
1056 FETCH c_rfmb_disb INTO l_rfmb_disb_rec;
1057 IF c_rfmb_disb%FOUND THEN
1058
1059 l_rfmb_disb_rec.disb_ref_num := i;
1060 l_rfmb_disb_rec.disb_dt := l_pell_tab(i).disb_dt ;
1061 l_rfmb_disb_rec.disb_amt := l_pell_tab(i).accepted_amt ;
1062
1063 IF l_rfmb_disb_rec.disb_amt >= 0 THEN
1064 l_rfmb_disb_rec.db_cr_flag := 'P' ;
1065 ELSE
1066 l_rfmb_disb_rec.db_cr_flag := 'N' ;
1067 END IF;
1068
1069 l_rfmb_disb_rec.disb_ack_act_status := 'R' ;
1070 l_rfmb_disb_rec.disb_status_dt := TRUNC(SYSDATE);
1071 l_rfmb_disb_rec.disb_accpt_amt := NULL ;
1072 l_rfmb_disb_rec.accpt_db_cr_flag := NULL ;
1073 l_rfmb_disb_rec.disb_ytd_amt := NULL ;
1074 l_rfmb_disb_rec.pymt_prd_start_dt := NULL ;
1075 l_rfmb_disb_rec.accpt_pymt_prd_start_dt := NULL ;
1076 l_rfmb_disb_rec.edit_code := NULL ;
1077 l_rfmb_disb_rec.rfmb_id := NULL ;
1078
1079
1080 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1081 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.disb_ref_num:'||l_rfmb_disb_rec.disb_ref_num);
1082 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.disb_dt:'||l_rfmb_disb_rec.disb_dt);
1083 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.disb_amt:'||l_rfmb_disb_rec.disb_amt);
1084 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.db_cr_flag:'||l_rfmb_disb_rec.db_cr_flag);
1085 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.disb_ack_act_status:'||l_rfmb_disb_rec.disb_ack_act_status);
1086 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','l_rfmb_disb_rec.disb_status_dt:'||l_rfmb_disb_rec.disb_status_dt);
1087 END IF;
1088
1089
1090 igf_gr_rfms_disb_pkg.update_row(
1091 x_mode => 'R',
1092 x_rowid => l_rfmb_disb_rec.row_id,
1093 x_rfmd_id => l_rfmb_disb_rec.rfmd_id,
1094 x_origination_id => l_rfmb_disb_rec.origination_id,
1095 x_disb_ref_num => l_rfmb_disb_rec.disb_ref_num,
1096 x_disb_dt => l_rfmb_disb_rec.disb_dt,
1097 x_disb_amt => l_rfmb_disb_rec.disb_amt,
1098 x_db_cr_flag => l_rfmb_disb_rec.db_cr_flag,
1099 x_disb_ack_act_status => l_rfmb_disb_rec.disb_ack_act_status ,
1100 x_disb_status_dt => l_rfmb_disb_rec.disb_status_dt ,
1101 x_accpt_disb_dt => l_rfmb_disb_rec.accpt_disb_dt ,
1102 x_disb_accpt_amt => l_rfmb_disb_rec.disb_accpt_amt ,
1103 x_accpt_db_cr_flag => l_rfmb_disb_rec.accpt_db_cr_flag ,
1104 x_disb_ytd_amt => l_rfmb_disb_rec.disb_ytd_amt ,
1105 x_pymt_prd_start_dt => l_rfmb_disb_rec.pymt_prd_start_dt ,
1106 x_accpt_pymt_prd_start_dt => l_rfmb_disb_rec.accpt_pymt_prd_start_dt ,
1107 x_edit_code => l_rfmb_disb_rec.edit_code ,
1108 x_rfmb_id => l_rfmb_disb_rec.rfmb_id,
1109 x_ed_use_flags => l_rfmb_disb_rec.ed_use_flags
1110 );
1111 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','lv_rfmd_id:'||lv_rfmd_id);
1113 END IF;
1114
1115 ELSE -- RFMS Disbursement does not exist so add
1116
1117 lv_row_id := NULL;
1118 lv_rfmd_id := NULL;
1119
1120 igf_gr_rfms_disb_pkg.insert_row(
1121 x_mode => 'R',
1122 x_rowid => lv_row_id,
1123 x_rfmd_id => lv_rfmd_id,
1124 x_origination_id => lc_rfms.origination_id,
1125 x_disb_ref_num => i,
1126 x_disb_dt => l_pell_tab(i).disb_dt ,
1127 x_disb_amt => l_pell_tab(i).accepted_amt ,
1128 x_db_cr_flag => 'P',
1129 x_disb_ack_act_status => 'R',
1130 x_disb_status_dt => TRUNC(SYSDATE) ,
1131 x_accpt_disb_dt => NULL ,
1132 x_disb_accpt_amt => NULL ,
1133 x_accpt_db_cr_flag => NULL ,
1134 x_disb_ytd_amt => NULL ,
1135 x_pymt_prd_start_dt => NULL ,
1136 x_accpt_pymt_prd_start_dt => NULL ,
1137 x_edit_code => NULL ,
1138 x_rfmb_id => NULL ,
1139 x_ed_use_flags => NULL
1140 );
1141
1142
1143 END IF;
1144 CLOSE c_rfmb_disb;
1145 END IF; -- only for phase-in award year
1146
1147 END IF; -- Test Mode Flag Not Set
1148
1149 END IF; -- RFMS Exists Flag
1150
1151 l_last_disb_num := i;
1152
1153 END LOOP; -- Main loop for all the Disbursements from the Pl/SQL table
1154
1155 -- At this point all New Disbursments are adjusted.
1156 -- Check if the old Award Had Extra Disbursements in which case they need to be Cancelled
1157
1158 IF (NVL(g_test_run,'N') <> 'Y') THEN
1159
1160 -- Delete Additional Disbursement records
1161
1162 FOR disb_cancel_rec IN c_disb_cancel(lc_stud_det.award_id,l_last_disb_num) LOOP
1163
1164 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1165 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage.debug','cancelling awd_disb for award_id:'||disb_cancel_rec.award_id||' disb_cancel_rec.disb_num:'||disb_cancel_rec.disb_num);
1166 END IF;
1167
1168 -- cancel the disbursement
1169 igf_aw_awd_disb_pkg.update_row(
1170 x_rowid => disb_cancel_rec.row_id,
1171 x_award_id => disb_cancel_rec.award_id,
1172 x_disb_num => disb_cancel_rec.disb_num,
1173 x_tp_cal_type => disb_cancel_rec.tp_cal_type,
1174 x_tp_sequence_number => disb_cancel_rec.tp_sequence_number,
1175 x_disb_gross_amt => disb_cancel_rec.disb_gross_amt,
1176 x_fee_1 => disb_cancel_rec.fee_1,
1177 x_fee_2 => disb_cancel_rec.fee_2,
1178 x_disb_net_amt => disb_cancel_rec.disb_net_amt,
1179 x_disb_date => disb_cancel_rec.disb_date,
1180 x_trans_type => 'C',
1181 x_elig_status => disb_cancel_rec.elig_status,
1182 x_elig_status_date => disb_cancel_rec.elig_status_date,
1183 x_affirm_flag => disb_cancel_rec.affirm_flag,
1184 x_hold_rel_ind => disb_cancel_rec.hold_rel_ind,
1185 x_manual_hold_ind => disb_cancel_rec.manual_hold_ind,
1186 x_disb_status => disb_cancel_rec.disb_status,
1187 x_disb_status_date => disb_cancel_rec.disb_status_date,
1188 x_late_disb_ind => disb_cancel_rec.late_disb_ind,
1189 x_fund_dist_mthd => disb_cancel_rec.fund_dist_mthd,
1190 x_prev_reported_ind => disb_cancel_rec.prev_reported_ind,
1191 x_fund_release_date => disb_cancel_rec.fund_release_date,
1192 x_fund_status => disb_cancel_rec.fund_status,
1193 x_fund_status_date => disb_cancel_rec.fund_status_date,
1194 x_fee_paid_1 => disb_cancel_rec.fee_paid_1,
1195 x_fee_paid_2 => disb_cancel_rec.fee_paid_2,
1196 x_cheque_number => disb_cancel_rec.cheque_number,
1197 x_ld_cal_type => disb_cancel_rec.ld_cal_type,
1198 x_ld_sequence_number => disb_cancel_rec.ld_sequence_number,
1199 x_disb_accepted_amt => 0,
1200 x_disb_paid_amt => 0,
1201 x_rvsn_id => disb_cancel_rec.rvsn_id,
1202 x_int_rebate_amt => disb_cancel_rec.int_rebate_amt,
1203 x_force_disb => disb_cancel_rec.force_disb,
1204 x_min_credit_pts => disb_cancel_rec.min_credit_pts,
1205 x_disb_exp_dt => disb_cancel_rec.disb_exp_dt,
1206 x_verf_enfr_dt => disb_cancel_rec.verf_enfr_dt,
1207 x_fee_class => disb_cancel_rec.fee_class,
1208 x_show_on_bill => disb_cancel_rec.show_on_bill,
1209 x_mode => 'R',
1210 x_attendance_type_code => disb_cancel_rec.attendance_type_code,
1211 x_base_attendance_type_code => disb_cancel_rec.base_attendance_type_code,
1212 x_payment_prd_st_date => disb_cancel_rec.payment_prd_st_date,
1213 x_change_type_code => disb_cancel_rec.change_type_code,
1214 x_fund_return_mthd_code => disb_cancel_rec.fund_return_mthd_code,
1215 x_direct_to_borr_flag => disb_cancel_rec.direct_to_borr_flag
1216 );
1217
1218 END LOOP;
1219
1220 END IF; -- Cancel the Extra Disbursement Records
1221
1222
1223 -- Check if the old RFMS Disbursement records had Extra Disbursements in which case they need to be Deleted
1224
1225 IF (NVL(g_test_run,'N') <> 'Y') AND (lv_rfms_exits_flag) THEN
1226
1227 -- Clear Additional RFMS Entries if any.
1228
1229 FOR rfms_disb_rec IN c_rfmd_extra(lc_rfms.origination_id,l_last_disb_num) LOOP
1230 igf_gr_rfms_disb_pkg.delete_row(rfms_disb_rec.row_id);
1231 END LOOP;
1232
1233 END IF; -- Delete of Extra RFMS Disbursement Records
1234
1235 IF NVL(g_test_run,'N') <> 'Y' THEN
1236 IF NVL(l_pell_schedule_code,'*') <> NVL(lc_award.alt_pell_schedule,'*') THEN
1237 igf_aw_award_pkg.update_row(
1238 x_rowid => lc_award.row_id,
1239 x_award_id => lc_award.award_id,
1240 x_fund_id => lc_award.fund_id,
1241 x_base_id => lc_award.base_id,
1242 x_offered_amt => lc_award.offered_amt,
1243 x_accepted_amt => lc_award.accepted_amt,
1244 x_paid_amt => lc_award.paid_amt,
1245 x_packaging_type => lc_award.packaging_type,
1246 x_batch_id => lc_award.batch_id,
1247 x_manual_update => lc_award.manual_update,
1248 x_rules_override => lc_award.rules_override,
1249 x_award_date => lc_award.award_date,
1250 x_award_status => lc_award.award_status,
1251 x_attribute_category => lc_award.attribute_category,
1252 x_attribute1 => lc_award.attribute1,
1253 x_attribute2 => lc_award.attribute2,
1254 x_attribute3 => lc_award.attribute3,
1255 x_attribute4 => lc_award.attribute4,
1256 x_attribute5 => lc_award.attribute5,
1257 x_attribute6 => lc_award.attribute6,
1258 x_attribute7 => lc_award.attribute7,
1259 x_attribute8 => lc_award.attribute8,
1260 x_attribute9 => lc_award.attribute9,
1261 x_attribute10 => lc_award.attribute10,
1262 x_attribute11 => lc_award.attribute11,
1263 x_attribute12 => lc_award.attribute12,
1264 x_attribute13 => lc_award.attribute13,
1265 x_attribute14 => lc_award.attribute14,
1266 x_attribute15 => lc_award.attribute15,
1267 x_attribute16 => lc_award.attribute16,
1268 x_attribute17 => lc_award.attribute17,
1269 x_attribute18 => lc_award.attribute18,
1270 x_attribute19 => lc_award.attribute19,
1271 x_attribute20 => lc_award.attribute20,
1272 x_rvsn_id => lc_award.rvsn_id,
1273 x_alt_pell_schedule => l_pell_schedule_code,--update the pell schedule
1274 x_mode => 'R',
1275 x_award_number_txt => lc_award.award_number_txt,
1276 x_legacy_record_flag => lc_award.legacy_record_flag,
1277 x_adplans_id => lc_award.adplans_id,
1278 x_lock_award_flag => lc_award.lock_award_flag,
1279 x_app_trans_num_txt => lc_award.app_trans_num_txt,
1280 x_awd_proc_status_code => lc_award.awd_proc_status_code,
1281 x_notification_status_code => lc_award.notification_status_code,
1282 x_notification_status_date => lc_award.notification_status_date,
1283 x_publish_in_ss_flag => lc_award.publish_in_ss_flag
1284 );
1285 END IF;
1286 END IF;
1287
1288 fnd_file.put_line(fnd_file.log, RPAD('-',210,'-'));
1289
1290
1291 --issue a commit
1292 COMMIT;
1293
1294
1295 fnd_message.set_name('IGF','IGF_AW_PROCESS_COMPLETE');
1296 fnd_file.put_line( fnd_file.log, fnd_message.get);
1297 fnd_file.new_line(fnd_file.log,2);
1298
1299 EXCEPTION
1300
1301 WHEN e_next_record THEN
1302 ROLLBACK to sv_student;
1303 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
1304 fnd_file.put_line(fnd_file.log,fnd_message.get);
1305 fnd_file.new_line(fnd_file.log,1);
1306
1307 WHEN others THEN
1308 ROLLBACK to sv_student;
1309 fnd_file.put_line(fnd_file.log,SQLERRM);
1310 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
1311 fnd_file.new_line(fnd_file.log,1);
1312
1313 END repackage;
1314
1315
1316
1317 PROCEDURE repackage_pell(
1318 errbuf OUT NOCOPY VARCHAR2,
1319 retcode OUT NOCOPY NUMBER,
1320 p_award_year IN VARCHAR2,
1321 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1322 p_org_id IN igf_aw_award_all.org_id%TYPE,
1323 p_persid_grp IN igs_pe_persid_group_all.group_id%TYPE,
1324 p_test_run IN VARCHAR2,
1325 p_cancel_invalid_awds IN VARCHAR2
1326 ) AS
1327 -----------------------------------------------------------------------------------------------
1328 -- Change History :
1329 -- Who When What
1330 -- (reverse chronological order - newest change first)
1331 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1332 -- veramach 02-Dec-2003 FA 131 COD Updates
1333 -- Adds p_persid_grp,p_test_run,p_cancel_invalid_awds as parameters
1334 -- rasahoo 27-aug-2003 Removed the call to IGF_AP_OSS_PROCESS.GET_OSS_DETAILS
1335 -- as part of obsoletion of FA base record history
1336 -----------------------------------------------------------------------------------------------
1337 -- rasahoo 23-Apl-2003 Bug # 2860836
1338 -- locking problem created by fund manager is resolved
1339 -----------------------------------------------------------------------------------------------
1340 -- brajendr 24-Oct-2002 FA105 / FA108 Builds
1341 -- Modified the call PELL calc procedure to add one more parameter
1342 -----------------------------------------------------------------------------------------------
1343 --
1344
1345 -- Get all base ids in a award year
1346 CURSOR c_all_base_id(
1347 cp_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
1348 cp_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE
1349 ) IS
1350 SELECT awd.base_id
1351 FROM igf_aw_award_all awd,
1352 igf_aw_fund_mast_all fmast,
1353 igf_aw_fund_cat_all fcat
1354 WHERE fmast.ci_sequence_number = cp_seq_number
1355 AND fmast.ci_cal_type = cp_cal_type
1356 AND awd.fund_id = fmast.fund_id
1357 AND fmast.fund_code = fcat.fund_code
1358 AND fcat.fed_fund_code = 'PELL';
1359
1360 l_ci_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE;
1361 l_ci_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE;
1362 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
1363
1364 TYPE baseidRefCur IS REF CURSOR;
1365 cur_base_id baseidRefCur;
1366
1367 lv_status VARCHAR2(1);
1368 l_list VARCHAR2(32767);
1369 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1370
1371 BEGIN
1372
1373 retcode := 0;
1374 errbuf := NULL;
1375 -- Need to set the Org Id for this Responsibility
1376 igf_aw_gen.set_org_id(p_org_id);
1377
1378
1379
1380 IF p_award_year IS NULL THEN
1381 fnd_message.set_name('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
1382 fnd_file.put_line(fnd_file.log,fnd_message.get);
1383 RETURN;
1384 END IF;
1385
1386 -- Process only the given fund in the given Award Year.
1387 l_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
1388
1389 l_ci_sequence_number := TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_award_year,11))));
1390
1391 l_base_id := p_base_id;
1392
1393 g_test_run := p_test_run;
1394 g_cancel_invalid_awds := p_cancel_invalid_awds;
1395
1396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1397 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','l_ci_cal_type:'||l_ci_cal_type);
1398 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','l_ci_sequence_number:'||l_ci_sequence_number);
1399 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','l_base_id:'||l_base_id);
1400 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','g_test_run:'||g_test_run);
1401 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','g_cancel_invalid_awds:'||g_cancel_invalid_awds);
1402 END IF;
1403
1404 log_parameters(l_ci_cal_type,l_ci_sequence_number,l_base_id,p_persid_grp,p_test_run,p_cancel_invalid_awds);
1405
1406 IF p_base_id IS NOT NULL AND p_persid_grp IS NOT NULL THEN
1407 --Cannot pass both base_id and person_id group.
1408 fnd_message.set_name('IGF', 'IGF_AW_COA_PARAM_EX');
1409 fnd_file.put_line( fnd_file.log, fnd_message.get);
1410 RETURN;
1411 END IF;
1412
1413 --
1414 -- Open the Student Details and check if the records exists else
1415 -- raise a message
1416 --
1417 IF p_persid_grp IS NOT NULL THEN
1418 --get all base_id in the group and call the main routine
1419
1420 --get al person_id in the person _id group
1421 --Bug #5021084
1422 l_list := igf_ap_ss_pkg.get_pid(p_persid_grp,lv_status,lv_group_type);
1423
1424 --Bug #5021084. Passing Group ID if the group type is STATIC.
1425 IF lv_group_type = 'STATIC' THEN
1426 --open ref cursor for fetching the base_id of the person
1427 OPEN cur_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_ci_cal_type, l_ci_sequence_number,p_persid_grp;
1428 ELSIF lv_group_type = 'DYNAMIC' THEN
1429 --open ref cursor for fetching the base_id of the person
1430 OPEN cur_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_ci_cal_type, l_ci_sequence_number;
1431 END IF;
1432
1433
1434 FETCH cur_base_id INTO l_base_id;
1435 IF cur_base_id%FOUND THEN
1436 WHILE cur_base_id%FOUND
1437 LOOP
1438 --call the main routine here
1439 repackage(l_base_id);
1440
1441 FETCH cur_base_id INTO l_base_id;
1442 END LOOP;
1443 ELSE
1444 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
1445 fnd_file.put_line(fnd_file.log,fnd_message.get);
1446 END IF;
1447
1448 ELSIF p_base_id IS NOT NULL THEN
1449 --call the routine for the single base_id
1450 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1451 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_repackage.repackage_pell.debug','calling repackage with base_id:'||p_base_id);
1452 END IF;
1453 repackage(p_base_id);
1454
1455 ELSIF p_base_id IS NULL THEN
1456 --get all base_id in the award year and call the main routine for all base_id
1457
1458 FOR lc_all_base_id IN c_all_base_id(l_ci_sequence_number,l_ci_cal_type) LOOP
1459 --call the main routine here
1460 repackage(lc_all_base_id.base_id);
1461 END LOOP;
1462
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN app_exception.record_lock_exception THEN
1467 ROLLBACK;
1468 retcode := 2;
1469 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1470 igs_ge_msg_stack.conc_exception_hndl;
1471 WHEN OTHERS THEN
1472 ROLLBACK ;
1473 retcode := 2;
1474 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1475 fnd_message.set_token('NAME','IGF_GR_REPACKAGE.REPACKAGE_PELL '||SQLERRM);
1476 errbuf := fnd_message.get;
1477 igs_ge_msg_stack.conc_exception_hndl;
1478
1479 END repackage_pell;
1480
1481 END igf_gr_repackage;