[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_COA_CALC
Source
1 PACKAGE BODY igf_aw_coa_calc AS
2 /* $Header: IGFAW01B.pls 120.4 2006/02/08 23:36:46 ridas ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Who When What
6 -- veramach 21-Dec-2004 bug # 4081158 Resolved unhandled exception
7 -- cdcruz 12-Jan-2004 Bug # 3355361 FA CCR 118 COA Updates
8 -- Reviewed and review comments incorporated
9 -- veramach 08-Jan-2004 Bug # 3355361 FA CCR 118 COA Updates
10 -- Revamped the code. Added new parameters to run-
11 -- p_update_coa and p_update_method
12 -- added procedures overaward_amount,coa_needs_update,
13 -- does_term_mismatch,delete_coa
14 --
15 -- masehgal 10-Jun-2003 # 2858504 FA118.1 Added legacy_record_flag field
16 --
17 -- sjadhav 23-Dec-2002 Bug 2695347
18 -- 1. Implemented proper exception handling in all
19 -- inner routines and main exe
20 -- 2. Re-formatted log and output files
21 -- 3. Added proces_student routine to remove repeated
22 -- code
23 -- 4. modified add_coa_items routine so that output
24 -- file is created and fa base is updated only iff
25 -- there is change in coa for a student
26 -- 5. Cursor to pick up coa items is modified to pick
27 -- up only ACTIVE coa items
28 -- 6. Modified person group ref cursor.
29 --
30 ---------------------------------------------------------------------------------
31 -- gmuralid 23-Oct-2002 FA 105/FA108 Awarding enhancements COA calculation
32 -- changes
33 -- Completely modfied the Run Procedure
34 -- Added new procedures
35 -- 1) populate_setup_table
36 -- 2) add_coa_items
37 -- 3) print_output_file
38 ------------------------------------------------------------------------------
39 -- masehgal 25-Sep-2002 FA 104 - To Do Enhancements
40 -- Added manual_disb_hold in FA Base update
41 ------------------------------------------------------------------------------
42 -- Bug ID : 2331724 The Pell Amount should not split across
43 -- load Calendars
44 ------------------------------------------------------------------------------
45 -- adhawan 26-Apr-02 Cost of attendance groups are being set up with
46 -- Pell amounts on IGFAW005 Cost
47 -- of Attendance Assignment. After executing the job
48 -- Cost of Attendance--Compute Cost of Attendance, the
49 -- field labeled Pell is the Pell budget which should
50 -- never split up.The Pell amount should be the full
51 -- amount entered on the setup form.
52 --------------------------------------------------------------------------------
53 -- Bug ID : 2201787
54 -- who when what
55 -- brajendr 19-Jun-2001 1. Changed the cursor c_stud_det. removed the for
56 -- update clause from the cursor.
57 ------------------------------------------------------------------------------
58 -- Bug ID : 1606850
59 -- who when what
60 -- sjadhav 19-Jun-2001 1. enhancement for November Release.
61 ------------------------------------------------------------------------------
62 -- Bug ID : 1796006
63 -- who when what
64 -- sjadhav 23-May-2001 1. changed the sequence of
65 -- parameters to
66 -- Run Type / Award year /Base ID
67 ------------------------------------------------------------------------------
68 -- Bug ID : 1723272
69 -- who when what
70 -- mesriniv 20-Apr-2001 1.Change has been done in the cursor c_stud_det
71 -- to fetch the data from IGF_AP_FA_CON_V
72 -- instead of IGF_AP_FA_BASE_REC.
73 --------------------------------------------------------------------------------
74 -- Bug ID : 1731302 COA not calcuated for the whole award year
75 -- avenkatr 18-Apr-01 1. When the award year IS split and before
76 -- it is assigned to the cal_type it IS trimmed
77 --------------------------------------------------------------------------------
78
79 g_b_header BOOLEAN;
80 g_coa_updated VARCHAR2(1) ;
81 g_cal_type igs_ca_inst.cal_type%TYPE;
82 g_sequence_number igs_ca_inst.sequence_number%TYPE;
83
84 E_SKIP_STUDENT EXCEPTION;
85 E_SKIP_STD_NO_ITEMS EXCEPTION;
86 E_SKIP_STD_NO_TERMS EXCEPTION;
87
88 FUNCTION overaward_amount(
89 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
90 ) RETURN NUMBER AS
91 ------------------------------------------------------------------
92 --Created by : veramach, Oracle India
93 --Date created: 08-JAN-2004
94 --
95
96 --Purpose:
97 --
98 --
99 --Known limitations/enhancements and/or remarks:
100 --
101 --Change History:
102 --Who When What
103 -------------------------------------------------------------------
104
105 l_resource_f NUMBER;
106 l_resource_i NUMBER;
107 l_unmet_need_f NUMBER;
108 l_unmet_need_i NUMBER;
109 l_resource_f_fc NUMBER;
110 l_resource_i_fc NUMBER;
111
112 BEGIN
113
114 igf_aw_gen_002.get_resource_need(
115 p_base_id,
116 l_resource_f,
117 l_resource_i,
118 l_unmet_need_f,
119 l_unmet_need_i,
120 l_resource_f_fc,
121 l_resource_i_fc
122 );
123
124 RETURN -1 * l_unmet_need_f;
125
126 END overaward_amount;
127
128 FUNCTION coa_needs_update(
129 p_item_code igf_aw_coa_items.item_code%TYPE,
130 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
131 p_amount igf_aw_coa_items.amount%TYPE,
132 p_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
133 p_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
134 p_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
135 ) RETURN BOOLEAN AS
136 ------------------------------------------------------------------
137 --Created by : veramach, Oracle India
138 --Date created: 07-JAN-2004
139 --
140 --Purpose:
141 --
142 --
143 --Known limitations/enhancements and/or remarks:
144 --
145 --Change History:
146 --Who When What
147 --ridas 09-09-2005 Bug #4226096. Added a new CURSOR 'c_get_lock_flg'
148 -- to fetch lock flag.
149 -------------------------------------------------------------------
150
151 -- check if the student's COA item needs update or not
152 CURSOR c_coa_item(
153 cp_item_code igf_aw_coa_items.item_code%TYPE,
154 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
155 cp_amount igf_aw_coa_items.amount%TYPE,
156 cp_pell_coa_amount igf_aw_coa_items.pell_coa_amount%TYPE,
157 cp_alt_pell_amount igf_aw_coa_items.alt_pell_amount%TYPE,
158 cp_fixed_cost igf_aw_coa_items.fixed_cost%TYPE
159 ) IS
160 SELECT lock_flag
161 FROM igf_aw_coa_items
162 WHERE item_code = cp_item_code
163 AND base_id = cp_base_id
164 AND amount = cp_amount
165 AND NVL(pell_coa_amount,-1) = NVL(cp_pell_coa_amount,-1)
166 AND NVL(alt_pell_amount,-1) = NVL(cp_alt_pell_amount,-1)
167 AND NVL(fixed_cost,'*') = NVL(cp_fixed_cost,'*');
168
169 l_coa_item c_coa_item%ROWTYPE;
170
171
172 -- get lock flag
173 CURSOR c_get_lock_flg(
174 cp_item_code igf_aw_coa_items.item_code%TYPE,
175 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
176 ) IS
177 SELECT lock_flag
178 FROM igf_aw_coa_items
179 WHERE item_code = cp_item_code
180 AND base_id = cp_base_id;
181
182 l_get_lock_flg c_get_lock_flg%ROWTYPE;
183
184 BEGIN
185 OPEN c_coa_item(p_item_code,p_base_id,p_amount,p_pell_coa_amount,p_alt_pell_amount,p_fixed_cost);
186 FETCH c_coa_item INTO l_coa_item;
187
188 IF c_coa_item%FOUND THEN
189 CLOSE c_coa_item;
190 RETURN FALSE;
191 ELSE
192 CLOSE c_coa_item;
193
194 l_get_lock_flg := NULL;
195
196 OPEN c_get_lock_flg(p_item_code,p_base_id);
197 FETCH c_get_lock_flg INTO l_get_lock_flg;
198 CLOSE c_get_lock_flg;
199
200 IF l_get_lock_flg.lock_flag='N' OR l_get_lock_flg.lock_flag IS NULL THEN
201 RETURN TRUE;
202 ELSE
203 RETURN FALSE;
204 END IF;
205 END IF;
206 END coa_needs_update;
207
208 FUNCTION does_term_mismatch(
209 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
210 p_process_id NUMBER
211 ) RETURN BOOLEAN AS
212 ------------------------------------------------------------------
213 --Created by : veramach, Oracle India
214 --Date created: 29-DEC-2003
215 --
216 --Purpose:
217 --
218 --
219 --Known limitations/enhancements and/or remarks:
220 --
221 --Change History:
222 --Who When What
223 -------------------------------------------------------------------
224 --Cursor to check for load calandar match
225 CURSOR c_load_cal_chk(
226 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
227 c_process_id NUMBER
228 ) IS
229 SELECT DISTINCT ld_cal_type,ld_sequence_number
230 FROM igf_aw_coa_itm_terms
231 WHERE base_id = c_base_id
232 MINUS
233 SELECT DISTINCT ld_cal_type,ld_sequence_number
234 FROM igf_aw_award_t
235 WHERE process_id = c_process_id;
236
237 c_cal_chk_rec c_load_cal_chk%ROWTYPE;
238
239 -- New cursor Added on 20-DEC-02 for for terms inconsistency check
240 CURSOR c_load_cal_chk1(
241 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
242 c_process_id NUMBER
243 ) IS
244 SELECT DISTINCT ld_cal_type,ld_sequence_number
245 FROM igf_aw_award_t
246 WHERE process_id = c_process_id
247 MINUS
248 SELECT DISTINCT ld_cal_type,ld_sequence_number
249 FROM igf_aw_coa_itm_terms
250 WHERE base_id = c_base_id;
251
252 c_cal_chk_rec1 c_load_cal_chk1%ROWTYPE;
253
254 BEGIN
255
256 OPEN c_load_cal_chk(p_base_id,p_process_id);
257 FETCH c_load_cal_chk INTO c_cal_chk_rec;
258
259 IF c_load_cal_chk%FOUND THEN
260 CLOSE c_load_cal_chk;
261 RETURN TRUE;
262
263 ELSE
264 CLOSE c_load_cal_chk;
265
266 OPEN c_load_cal_chk1(p_base_id,p_process_id);
267 FETCH c_load_cal_chk1 INTO c_cal_chk_rec1;
268
269 IF c_load_cal_chk1%FOUND THEN
270 CLOSE c_load_cal_chk1;
271 RETURN TRUE;
272 ELSE
273 CLOSE c_load_cal_chk1;
274 RETURN FALSE;
275 END IF;
276 END IF;
277 END does_term_mismatch;
278
279
280 FUNCTION iscoalocked(
281 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
282 ) RETURN BOOLEAN AS
283 ------------------------------------------------------------------
284 --Created by : ridas, Oracle India
285 --Date created: 01-Nov-2004
286 --
287 --Purpose:
288 --
289 --Known limitations/enhancements and/or remarks:
290 --
291 --Change History:
292 --Who When What
293 -------------------------------------------------------------------
294
295 CURSOR c_coa(
296 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
297 ) IS
298 SELECT rowid row_id,
299 item_code,
300 lock_flag
301 FROM igf_aw_coa_items
302 WHERE base_id = cp_base_id;
303
304 CURSOR c_coa_terms(
305 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
306 cp_item_code igf_aw_coa_items.item_code%TYPE
307 ) IS
308 SELECT rowid row_id,
309 lock_flag
310 FROM igf_aw_coa_itm_terms
311 WHERE base_id = cp_base_id
312 AND item_code = cp_item_code;
313
314
315 BEGIN
316 FOR coa_rec IN c_coa(p_base_id) LOOP
317 IF coa_rec.lock_flag = 'Y' THEN
318 fnd_message.set_name('IGF','IGF_AW_COA_ITM_NOT_DEL');
319 fnd_message.set_token('ITEM_CODE',coa_rec.item_code);
320 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
321
322
323 RETURN FALSE;
324 END IF;
325
326 FOR coa_terms_rec IN c_coa_terms(p_base_id,coa_rec.item_code) LOOP
327 IF coa_terms_rec.lock_flag = 'Y' THEN
328 fnd_message.set_name('IGF','IGF_AW_COA_ITM_NOT_DEL');
329 fnd_message.set_token('ITEM_CODE',coa_rec.item_code);
330 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
331
332 RETURN FALSE;
333 END IF;
334 END LOOP;
335 END LOOP;
336
337 RETURN TRUE;
338 EXCEPTION
339 WHEN OTHERS THEN
340 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
341 fnd_message.set_token('NAME','IGF_AW_COA_CALC.ISCOALOCKED' || ' '|| SQLERRM);
342 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
343 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.iscoalocked.exception','sql error: '||SQLERRM);
344 END IF;
345 igs_ge_msg_stack.conc_exception_hndl;
346 app_exception.raise_exception;
347 END iscoalocked;
348
349
350
351
352 PROCEDURE delete_coa(
353 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
354 ) AS
355 ------------------------------------------------------------------
356 --Created by : veramach, Oracle India
357 --Date created: 26-Dec-2003
358 --
359 --Purpose: Delete COA item and terms associated with a base_id
360 --
361 --
362 --Known limitations/enhancements and/or remarks:
363 --
364 --Change History:
365 --Who When What
366 -------------------------------------------------------------------
367
368 CURSOR c_coa(
369 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
370 ) IS
371 SELECT rowid row_id,
372 item_code
373 FROM igf_aw_coa_items
374 WHERE base_id = cp_base_id;
375
376 CURSOR c_coa_terms(
377 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
378 cp_item_code igf_aw_coa_items.item_code%TYPE
379 ) IS
380 SELECT rowid row_id
381 FROM igf_aw_coa_itm_terms
382 WHERE base_id = cp_base_id
383 AND item_code = cp_item_code;
384
385
386 BEGIN
387 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
388 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','Starting delete_coa with base_id:'||p_base_id);
389 END IF;
390 FOR coa_rec IN c_coa(p_base_id) LOOP
391
392 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
393 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','deleting item '||coa_rec.item_code);
394 END IF;
395 FOR coa_terms_rec IN c_coa_terms(p_base_id,coa_rec.item_code) LOOP
396
397 igf_aw_coa_itm_terms_pkg.delete_row(
398 x_rowid => coa_terms_rec.row_id
399 );
400 END LOOP;
401
402 igf_aw_coa_items_pkg.delete_row(
403 x_rowid => coa_rec.row_id
404 );
405 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_DEL');
406 fnd_message.set_token('ITEM_CODE',coa_rec.item_code);
407 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
408
409 END LOOP;
410 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
411 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.delete_coa.debug','delete_coa done');
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
417 fnd_message.set_token('NAME','IGF_AW_COA_CALC.DELETE_COA' || ' '|| SQLERRM);
418 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
419 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.delete_coa.exception','sql error: '||SQLERRM);
420 END IF;
421 igs_ge_msg_stack.conc_exception_hndl;
422 app_exception.raise_exception;
423 END delete_coa;
424
425
426 ---------------------------------------------------------------------------------------
427 -- The procedure populate_setup_table populates the temporary table igf_aw_award_t
428 -- CREATED BY:gmuralid
429 --------------------------------------------------------------------------------------
430 PROCEDURE populate_setup_table(
431 p_grp_coa_code IN igf_aw_coa_grp_item.coa_code%TYPE,
432 p_ci_cal_type IN igf_aw_coa_grp_item.ci_cal_type%TYPE,
433 p_ci_sequence_number IN igf_aw_coa_grp_item.ci_sequence_number%TYPE,
434 l_process_id IN NUMBER
435 ) IS
436
437 -- Cursor retrieves all item and term information for a given group code and award year
438
439 CURSOR c_item_term_info(
440 c_coa_code igf_aw_coa_grp_item.coa_code%TYPE,
441 c_ci_cal_type igf_aw_coa_grp_item.ci_cal_type%TYPE,
442 c_ci_sequence_number igf_aw_coa_grp_item.ci_sequence_number%TYPE
443 ) IS
444 SELECT grp.item_code,
445 grp.fixed_cost,
446 grp.default_value item_amount,
447 grp.pell_amount,
448 grp.pell_alternate_amt,
449 grp.lock_flag,
450 def.ld_cal_type,
451 def.ld_sequence_number,
452 def.ld_perct
453 FROM igf_aw_coa_grp_item grp,
454 igf_aw_coa_ld def
455 WHERE grp.coa_code = c_coa_code
456 AND grp.ci_cal_type = c_ci_cal_type
457 AND grp.ci_sequence_number = c_ci_sequence_number
458 AND grp.coa_code = def.coa_code
459 AND grp.ci_cal_type = def.ci_cal_type
460 AND grp.ci_sequence_number = def.ci_sequence_number
461 AND grp.active = 'Y'
462 AND grp.item_dist = 'N'
463
464 UNION ALL
465
466 SELECT ovrd.item_code,
467 grp.fixed_cost,
468 grp.default_value item_amount,
469 grp.pell_amount,
470 grp.pell_alternate_amt,
471 grp.lock_flag,
472 ovrd.ld_cal_type,
473 ovrd.ld_sequence_number,
474 ovrd.ld_perct
475 FROM igf_aw_cit_ld_overide ovrd ,
476 igf_aw_coa_grp_item grp
477 WHERE grp.coa_code = c_coa_code
478 AND grp.ci_cal_type = c_ci_cal_type
479 AND grp.ci_sequence_number = c_ci_sequence_number
480 AND grp.coa_code = ovrd.coa_code
481 AND grp.ci_cal_type = ovrd.ci_cal_type
482 AND grp.ci_sequence_number = ovrd.ci_sequence_number
483 AND grp.item_code = ovrd.item_code
484 AND grp.active = 'Y'
485 AND grp.item_dist = 'Y' ;
486
487 items_rec c_item_term_info%ROWTYPE;
488 item_term_amount igf_aw_award_t.accepted_amt%TYPE;
489
490 l_rowid ROWID;
491 l_sl_number NUMBER(15);
492
493 BEGIN
494
495 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
496 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.populate_setup_table.debug','starting populate_setup_table with ' ||
497 'p_grp_coa_code->'||p_grp_coa_code||
498 'p_ci_cal_type->'||p_ci_cal_type||
499 'p_ci_sequence_number->'||p_ci_sequence_number);
500 END IF;
501 OPEN c_item_term_info(p_grp_coa_code,p_ci_cal_type,p_ci_sequence_number);
502 LOOP
503 FETCH c_item_term_info INTO items_rec;
504 EXIT WHEN c_item_term_info%NOTFOUND;
505
506 l_rowid := NULL;
507 l_sl_number := NULL;
508
509 IF items_rec.item_amount IS NOT NULL THEN
510 item_term_amount := items_rec.item_amount * (NVL(items_rec.ld_perct,0)/100);
511 ELSE
512 item_term_amount := NULL;
513 END IF;
514
515 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
516 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.populate_setup_table.debug','inserting item->'||items_rec.item_code||
517 'item_term_amount->'||item_term_amount||
518 'ld_cal->'||items_rec.ld_cal_type||
519 'ld_seq->'||items_rec.ld_sequence_number);
520 END IF;
521
522 igf_aw_award_t_pkg.insert_row(
523 x_rowid => l_rowid,
524 x_process_id => l_process_id,
525 x_sl_number => l_sl_number,
526 x_fund_id => NULL,
527 x_base_id => NULL,
528 x_offered_amt => items_rec.item_amount, --this is the item amount
529 x_accepted_amt => item_term_amount, --this is the term amount
530 x_paid_amt => items_rec.pell_amount, --this is the pell amount
531 x_need_reduction_amt => items_rec.pell_alternate_amt, --this is the pell alternate amount
532 x_flag => 'GR',
533 x_temp_num_val1 => NULL,
534 x_temp_num_val2 => NULL,
535 x_temp_char_val1 => items_rec.item_code, --item code
536 x_tp_cal_type => items_rec.fixed_cost,
537 x_tp_sequence_number => NULL,
538 x_ld_cal_type => items_rec.ld_cal_type,
539 x_ld_sequence_number => items_rec.ld_sequence_number,
540 x_mode => 'R',
541 x_adplans_id => NULL,
542 x_app_trans_num_txt => NULL,
543 x_award_id => NULL,
544 x_lock_award_flag => items_rec.lock_flag,
545 x_temp_val3_num => NULL,
546 x_temp_val4_num => NULL,
547 x_temp_char2_txt => NULL,
548 x_temp_char3_txt => NULL
549
550 );
551
552 END LOOP;
553 CLOSE c_item_term_info;
554
555 COMMIT;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
560 fnd_message.set_token('NAME','IGF_AW_COA_CALC.POPULATE_SETUP_TABLE' || ' '|| SQLERRM);
561 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
562 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.populate_setup_table.exception','sql error message: '||SQLERRM);
563 END IF;
564 igs_ge_msg_stack.conc_exception_hndl;
565 app_exception.raise_exception;
566 END populate_setup_table;
567
568
569 ------------------------------------------------------------------------------------------------
570 --Porcedure add_coa_items calculates the cost of attendance for a student
571 --CREATED BY :gmuralid
572 -------------------------------------------------------------------------------------------------
573 PROCEDURE add_coa_items(
574 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
575 p_grp_code IN igf_aw_coa_grp_item.coa_code%TYPE,
576 exeorder IN VARCHAR2,
577 l_process_id IN NUMBER,
578 result OUT NOCOPY VARCHAR2
579 ) IS
580
581 ------------------------------------------------------------------
582 --Created by : gmuralid
583 --Date created:
584 --
585 --Purpose:
586 --
587 --
588 --Known limitations/enhancements and/or remarks:
589 --
590 --Change History:
591 --Who When What
592 --veramach 08-Jan-2004 FA CCR 118 COA Updates
593 -- Added new logic based on p_update_coa and p_update_method values
594 -------------------------------------------------------------------
595
596 --
597 -- cursor retrieves inforamtion from temporary table to calcualte COA only for student who
598 -- does not have prior cost of attendance
599 --
600
601 CURSOR c_first_coa(
602 c_process_id NUMBER
603 ) IS
604 SELECT DISTINCT temp_char_val1 item_code,
605 offered_amt item_amount,
606 paid_amt pell_amount,
607 need_reduction_amt pell_alternate_amount,
608 tp_cal_type fixed_cost,
609 lock_award_flag
610 FROM igf_aw_award_t
611 WHERE process_id = c_process_id
612 ORDER BY temp_char_val1;
613 first_coa_rec c_first_coa%ROWTYPE;
614
615 --
616 --cursor retrieves inforamtion ( this includes the term information as well) from
617 --temporary table to calcualte COA only for student who
618 --does not have prior cost of attendance
619 --
620
621
622 CURSOR c_first_itm_term(
623 c_item_code igf_aw_award_t.temp_char_val1%TYPE,
624 c_process_id NUMBER
625 ) IS
626 SELECT temp_char_val1 item_code,
627 accepted_amt item_term_amount,
628 paid_amt pell_amount,
629 need_reduction_amt pell_alternate_amount,
630 tp_cal_type fixed_cost,
631 ld_cal_type,
632 ld_sequence_number,
633 lock_award_flag
634 FROM igf_aw_award_t
635 WHERE temp_char_val1 = c_item_code
636 AND process_id=c_process_id;
637
638 first_itm_term_rec c_first_itm_term%ROWTYPE;
639
640
641 --Cursor to retrieve item-term information for a student who already has some COA items assigned
642 CURSOR c_second_item_term(
643 c_item_code igf_aw_award_t.temp_char_val1%TYPE,
644 c_process_id NUMBER
645 ) IS
646 SELECT temp_char_val1 item_code,
647 accepted_amt item_term_amount,
648 paid_amt pell_amount,
649 need_reduction_amt pell_alternate_amount,
650 tp_cal_type fixed_cost,
651 ld_cal_type,
652 ld_sequence_number,
653 lock_award_flag
654 FROM igf_aw_award_t
655 WHERE temp_char_val1 = c_item_code
656 AND process_id = c_process_id;
657 sec_itm_term_rec c_second_item_term%ROWTYPE;
658
659 l_rowid ROWID;
660
661 -- Cursor to check for item match
662 CURSOR c_check_item_match(
663 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
664 c_process_id NUMBER
665 ) IS
666 SELECT DISTINCT coa_group_items.new_item,
667 coa_group_items.item_amount,
668 coa_group_items.pell_amt,
669 coa_group_items.pell_alt_amt,
670 coa_group_items.fixed_cost,
671 coa_group_items.lock_award_flag,
672 assigned_coa.existing_item,
673 assigned_coa.row_id
674 FROM
675 (
676 SELECT DISTINCT temp_char_val1 new_item,
677 offered_amt item_amount,
678 paid_amt pell_amt,
679 need_reduction_amt pell_alt_amt,
680 tp_cal_type fixed_cost,
681 lock_award_flag
682 FROM igf_aw_award_t
683 WHERE process_id = c_process_id
684 ) coa_group_items,
685 (
686 SELECT rowid row_id,
687 item_code existing_item
688 FROM igf_aw_coa_items
689 WHERE base_id = c_base_id
690 ) assigned_coa
691 WHERE coa_group_items.new_item = assigned_coa.existing_item(+);
692
693 chk_item_match_rec c_check_item_match%ROWTYPE;
694
695 -- select item's terms and term amount
696 CURSOR c_item_term(
697 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
698 cp_item_code igf_aw_coa_itm_terms.item_code%TYPE,
699 cp_ld_cal_type igf_aw_coa_itm_terms.ld_cal_type%TYPE,
700 cp_ld_sequence_number igf_aw_coa_itm_terms.ld_sequence_number%TYPE
701 ) IS
702 SELECT rowid row_id,
703 lock_flag
704 FROM igf_aw_coa_itm_terms
705 WHERE base_id = cp_base_id
706 AND item_code = cp_item_code
707 AND ld_cal_type = cp_ld_cal_type
708 AND ld_sequence_number = cp_ld_sequence_number;
709
710 l_item_term c_item_term%ROWTYPE;
711
712
713 --Cursor to fetch item details for the base id
714 CURSOR c_items(
715 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
716 c_item_code igf_aw_coa_itm_terms.item_code%TYPE
717 ) IS
718 SELECT item.rowid row_id,
719 item.*
720 FROM igf_aw_coa_items item
721 WHERE base_id = c_base_id
722 AND item_code = c_item_code;
723
724 l_items c_items%ROWTYPE;
725
726 --Cursor to fetch the sum amount of all the terms for the base id
727 CURSOR c_terms(
728 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
729 ) IS
730 SELECT item_code,
731 SUM(NVL(amount,0)) amount
732 FROM igf_aw_coa_itm_terms term
733 WHERE base_id = c_base_id
734 GROUP BY item_code;
735
736 l_terms c_terms%ROWTYPE;
737
738
739 l_base_details igf_aw_coa_gen.base_details;
740 lv_terms_updated VARCHAR2(1);
741 lv_item_assigned VARCHAR2(1);
742 lv_term_not_asgn VARCHAR2(1);
743 ln_amount NUMBER;
744 ln_rate_order NUMBER;
745
746
747 BEGIN
748
749 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
750 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','starting add_coa_items with '||
751 'p_base_id/p_grp_code/exeorder/l_process_id:'||
752 p_base_id || ' / ' || p_grp_code || ' / ' || exeorder || ' /' || l_process_id);
753 END IF;
754
755 result := 'N';
756 lv_terms_updated := 'N';
757 lv_term_not_asgn := 'Y';
758
759
760 IF (exeorder='FIRST') THEN
761 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
762 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','exeorder:'||exeorder);
763 END IF;
764 result:='Y';
765
766 FOR first_coa_rec IN c_first_coa(l_process_id) LOOP
767 l_rowid := NULL;
768 lv_item_assigned := 'N';
769
770 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
771 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling items.insert with base_id/item_code -> ' ||
772 p_base_id || ' / '|| first_coa_rec.item_code);
773 END IF;
774 g_coa_updated := 'Y' ;
775
776 igf_aw_coa_items_pkg.insert_row(
777 x_rowid => l_rowid,
778 x_base_id => p_base_id,
779 x_item_code => first_coa_rec.item_code,
780 x_amount => NVL(first_coa_rec.item_amount,0),
781 x_pell_coa_amount => first_coa_rec.pell_amount,
782 x_alt_pell_amount => first_coa_rec.pell_alternate_amount,
783 x_fixed_cost => first_coa_rec.fixed_cost,
784 x_legacy_record_flag => NULL,
785 x_mode => 'R',
786 x_lock_flag => first_coa_rec.lock_award_flag
787 );
788
789
790 FOR first_itm_term_rec IN c_first_itm_term(first_coa_rec.item_code,l_process_id) LOOP
791 l_rowid := NULL;
792 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
793 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling items.insert with base_id/item_code/ld_cal/ld_seq -> ' ||
794 p_base_id || ' / '|| first_itm_term_rec.item_code ||
795 ' / ' || first_itm_term_rec.ld_cal_type || ' / ' || first_itm_term_rec.ld_sequence_number);
796 END IF;
797
798 --if the amount is NULL Rate Order Setup is used
799 IF first_itm_term_rec.item_term_amount IS NULL THEN
800 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
801 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','first_itm_term_rec.item_term_amount IS NULL '||
802 'and calling igf_aw_coa_gen.getBaseDetails');
803 END IF;
804
805 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,first_itm_term_rec.ld_cal_type,first_itm_term_rec.ld_sequence_number);
806
807 --Rate Order found against the student attributes
808 IF igf_aw_coa_update.is_attrib_matching(
809 p_base_id => p_base_id,
810 p_base_details => l_base_details,
811 p_ci_cal_type => g_cal_type,
812 p_ci_sequence_number => g_sequence_number,
813 p_ld_cal_type => first_itm_term_rec.ld_cal_type,
814 p_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
815 p_item_code => first_itm_term_rec.item_code,
816 p_amount => ln_amount,
817 p_rate_order_num => ln_rate_order
818 ) THEN
819
820 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
821 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
822 END IF;
823
824 g_coa_updated := 'Y';
825 lv_terms_updated := 'Y';
826 lv_item_assigned := 'Y';
827
828 igf_aw_coa_itm_terms_pkg.insert_row(
829 x_rowid => l_rowid,
830 x_base_id => p_base_id,
831 x_item_code => first_itm_term_rec.item_code,
832 x_amount => ln_amount,
833 x_ld_cal_type => first_itm_term_rec.ld_cal_type,
834 x_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
835 x_mode => 'R',
836 x_lock_flag => first_itm_term_rec.lock_award_flag
837 );
838
839 --skip the term if Rate Order Setup is not available
840 ELSE
841 lv_term_not_asgn := 'N';
842
843 IF NVL(ln_rate_order,0) <> -1 THEN
844 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
845 fnd_message.set_token('ITEM_CODE',first_itm_term_rec.item_code);
846 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(first_itm_term_rec.ld_cal_type,first_itm_term_rec.ld_sequence_number));
847 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
848 END IF;
849 END IF;
850
851 --if the amount is NOT NULL COA Group Setup is used
852 ELSE
853 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
854 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','first_itm_term_rec.item_term_amount IS NOT NULL');
855 END IF;
856
857 g_coa_updated := 'Y';
858 lv_terms_updated := 'Y';
859 lv_item_assigned := 'Y';
860
861 igf_aw_coa_itm_terms_pkg.insert_row(
862 x_rowid => l_rowid,
863 x_base_id => p_base_id,
864 x_item_code => first_itm_term_rec.item_code,
865 x_amount => first_itm_term_rec.item_term_amount,
866 x_ld_cal_type => first_itm_term_rec.ld_cal_type,
867 x_ld_sequence_number => first_itm_term_rec.ld_sequence_number,
868 x_mode => 'R',
869 x_lock_flag => first_itm_term_rec.lock_award_flag
870 );
871 END IF;
872 END LOOP;
873
874 IF lv_item_assigned = 'Y' THEN
875 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_ADD');
876 fnd_message.set_token('ITEM_CODE',first_coa_rec.item_code);
877 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
878 ELSE
879 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_NTADD');
880 fnd_message.set_token('ITEM_CODE',first_coa_rec.item_code);
881 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
882 END IF;
883
884 IF lv_term_not_asgn = 'N' THEN
885 RAISE E_SKIP_STD_NO_TERMS;
886 END IF;
887 END LOOP;
888
889 IF lv_terms_updated = 'N' THEN
890 RAISE E_SKIP_STD_NO_ITEMS;
891 END IF;
892
893 ELSIF (exeorder='SECOND') THEN
894 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
895 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','exeorder:'||exeorder);
896 END IF;
897 result := 'N';
898
899 IF g_update_coa = 'N' THEN
900 IF does_term_mismatch(p_base_id,l_process_id) THEN
901 fnd_message.set_name('IGF','IGF_AW_COA_INCONSTENT_TERMS');
902 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
903
904 ELSE
905 result := 'N';
906 FOR chk_item_match_rec IN c_check_item_match(p_base_id,l_process_id) LOOP
907 IF chk_item_match_rec.existing_item IS NULL THEN
908 l_rowid:= NULL;
909 result := 'Y';
910 g_coa_updated := 'Y' ;
911 lv_item_assigned := 'N';
912
913 igf_aw_coa_items_pkg.insert_row(
914 x_rowid => l_rowid,
915 x_base_id => p_base_id,
916 x_item_code => chk_item_match_rec.new_item,
917 x_amount => NVL(chk_item_match_rec.item_amount,0),
918 x_pell_coa_amount => chk_item_match_rec.pell_amt,
919 x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
920 x_fixed_cost => chk_item_match_rec.fixed_cost,
921 x_mode => 'R',
922 x_lock_flag => chk_item_match_rec.lock_award_flag
923 );
924
925
926 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
927 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','added item '||chk_item_match_rec.new_item);
928 END IF;
929 FOR sec_itm_term_rec IN c_second_item_term( chk_item_match_rec.new_item,l_process_id) LOOP
930 l_rowid := NULL;
931
932 --if the amount is NULL Rate Order Setup is used
933 IF sec_itm_term_rec.item_term_amount IS NULL THEN
934 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
935 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NULL '||
936 'and calling igf_aw_coa_gen.getBaseDetails');
937 END IF;
938
939 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
940
941 --Rate Order found against the student attributes
942 IF igf_aw_coa_update.is_attrib_matching(
943 p_base_id => p_base_id,
944 p_base_details => l_base_details,
945 p_ci_cal_type => g_cal_type,
946 p_ci_sequence_number => g_sequence_number,
947 p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
948 p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
949 p_item_code => sec_itm_term_rec.item_code,
950 p_amount => ln_amount,
951 p_rate_order_num => ln_rate_order
952 ) THEN
953
954 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
955 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
956 END IF;
957
958 g_coa_updated := 'Y';
959 lv_terms_updated := 'Y';
960 lv_item_assigned := 'Y';
961
962 igf_aw_coa_itm_terms_pkg.insert_row(
963 x_rowid => l_rowid,
964 x_base_id => p_base_id,
965 x_item_code => sec_itm_term_rec.item_code,
966 x_amount => ln_amount,
967 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
968 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
969 x_mode => 'R',
970 x_lock_flag => sec_itm_term_rec.lock_award_flag
971 );
972
973 --skip the term if Rate Order Setup is not available
974 ELSE
975 lv_term_not_asgn := 'N';
976
977 IF NVL(ln_rate_order,0) <> -1 THEN
978 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
979 fnd_message.set_token('ITEM_CODE',sec_itm_term_rec.item_code);
980 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number));
981 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
982 END IF;
983 END IF;
984
985 --if the amount is NOT NULL COA Group Setup is used
986 ELSE
987 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
988 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NOT NULL');
989 END IF;
990
991 g_coa_updated := 'Y';
992 lv_terms_updated := 'Y';
993 lv_item_assigned := 'Y';
994
995 igf_aw_coa_itm_terms_pkg.insert_row(
996 x_rowid => l_rowid,
997 x_base_id => p_base_id,
998 x_item_code => sec_itm_term_rec.item_code,
999 x_amount => sec_itm_term_rec.item_term_amount,
1000 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1001 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1002 x_mode => 'R',
1003 x_lock_flag => sec_itm_term_rec.lock_award_flag
1004 );
1005 END IF;
1006
1007 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1008 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','ld_cal/ld_seq/amount->'||
1009 sec_itm_term_rec.ld_cal_type || ' / '||
1010 sec_itm_term_rec.ld_sequence_number || ' / ' ||
1011 sec_itm_term_rec.item_term_amount);
1012 END IF;
1013 END LOOP;
1014 END IF;
1015
1016 IF lv_item_assigned = 'Y' THEN
1017 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_ADD');
1018 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1019 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1020 ELSE
1021 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_NTADD');
1022 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1023 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1024 END IF;
1025
1026 IF lv_term_not_asgn = 'N' THEN
1027 RAISE E_SKIP_STD_NO_TERMS;
1028 END IF;
1029 END LOOP;
1030
1031 IF lv_terms_updated = 'N' THEN
1032 RAISE E_SKIP_STD_NO_ITEMS;
1033 END IF;
1034
1035 END IF;
1036 ELSIF g_update_coa = 'Y' THEN
1037 IF g_update_method = 'SKIP' THEN
1038 IF does_term_mismatch(p_base_id,l_process_id) THEN
1039 --log an error message
1040 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1041 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','g_override_inconsistent_terms = N!so erroring out');
1042 END IF;
1043 fnd_message.set_name('IGF','IGF_AW_COA_INCONSTENT_TERMS');
1044 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1045 result := 'N';
1046 ELSE
1047 FOR chk_item_match_rec IN c_check_item_match(p_base_id,l_process_id) LOOP
1048 l_rowid := NULL;
1049 IF chk_item_match_rec.existing_item IS NULL THEN
1050 --the COA item is not assigned
1051 --so insert the item
1052 g_coa_updated := 'Y' ;
1053 lv_item_assigned := 'N';
1054
1055 igf_aw_coa_items_pkg.insert_row(
1056 x_rowid => l_rowid,
1057 x_base_id => p_base_id,
1058 x_item_code => chk_item_match_rec.new_item,
1059 x_amount => NVL(chk_item_match_rec.item_amount,0),
1060 x_pell_coa_amount => chk_item_match_rec.pell_amt,
1061 x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
1062 x_fixed_cost => chk_item_match_rec.fixed_cost,
1063 x_mode => 'R',
1064 x_lock_flag => chk_item_match_rec.lock_award_flag
1065 );
1066
1067 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1068 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','added item '||chk_item_match_rec.new_item);
1069 END IF;
1070
1071 -- start adding terms and term amounts
1072 FOR sec_itm_term_rec IN c_second_item_term(chk_item_match_rec.new_item,l_process_id) LOOP
1073 l_rowid := NULL;
1074
1075 --if the amount is NULL Rate Order Setup is used
1076 IF sec_itm_term_rec.item_term_amount IS NULL THEN
1077 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1078 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NULL '||
1079 'and calling igf_aw_coa_gen.getBaseDetails');
1080 END IF;
1081
1082 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1083
1084 --Rate Order found against the student attributes
1085 IF igf_aw_coa_update.is_attrib_matching(
1086 p_base_id => p_base_id,
1087 p_base_details => l_base_details,
1088 p_ci_cal_type => g_cal_type,
1089 p_ci_sequence_number => g_sequence_number,
1090 p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1091 p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1092 p_item_code => sec_itm_term_rec.item_code,
1093 p_amount => ln_amount,
1094 p_rate_order_num => ln_rate_order
1095 ) THEN
1096
1097 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1098 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
1099 END IF;
1100
1101 g_coa_updated := 'Y';
1102 lv_terms_updated := 'Y';
1103 lv_item_assigned := 'Y';
1104
1105 igf_aw_coa_itm_terms_pkg.insert_row(
1106 x_rowid => l_rowid,
1107 x_base_id => p_base_id,
1108 x_item_code => sec_itm_term_rec.item_code,
1109 x_amount => ln_amount,
1110 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1111 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1112 x_mode => 'R',
1113 x_lock_flag => sec_itm_term_rec.lock_award_flag
1114 );
1115
1116 --skip the term if Rate Order Setup is not available
1117 ELSE
1118 lv_term_not_asgn := 'N';
1119
1120 IF NVL(ln_rate_order,0) <> -1 THEN
1121 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
1122 fnd_message.set_token('ITEM_CODE',sec_itm_term_rec.item_code);
1123 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number));
1124 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1125 END IF;
1126 END IF;
1127
1128 --if the amount is NOT NULL COA Group Setup is used
1129 ELSE
1130 g_coa_updated := 'Y';
1131 lv_terms_updated := 'Y';
1132 lv_item_assigned := 'Y';
1133
1134 igf_aw_coa_itm_terms_pkg.insert_row(
1135 x_rowid => l_rowid,
1136 x_base_id => p_base_id,
1137 x_item_code => sec_itm_term_rec.item_code,
1138 x_amount => sec_itm_term_rec.item_term_amount,
1139 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1140 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1141 x_mode => 'R',
1142 x_lock_flag => sec_itm_term_rec.lock_award_flag
1143 );
1144
1145 END IF;
1146
1147 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1148 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','ld_cal/ld_seq/amount->'||
1149 sec_itm_term_rec.ld_cal_type || ' / '||
1150 sec_itm_term_rec.ld_sequence_number || ' / ' ||
1151 sec_itm_term_rec.item_term_amount);
1152 END IF;
1153
1154 END LOOP;
1155
1156 IF lv_item_assigned = 'Y' THEN
1157 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_ADD');
1158 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1159 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1160 ELSE
1161 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_NTADD');
1162 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1163 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1164 END IF;
1165
1166 IF lv_term_not_asgn = 'N' THEN
1167 RAISE E_SKIP_STD_NO_TERMS;
1168 END IF;
1169
1170 ELSE
1171 --the student has the COA item assigned
1172 --so if it needs update, update it
1173 IF coa_needs_update(chk_item_match_rec.new_item,p_base_id,chk_item_match_rec.item_amount,chk_item_match_rec.pell_amt,chk_item_match_rec.pell_alt_amt,chk_item_match_rec.fixed_cost) THEN
1174 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1175 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','updating item '||chk_item_match_rec.new_item);
1176 END IF;
1177 igf_aw_coa_items_pkg.update_row(
1178 x_rowid => chk_item_match_rec.row_id,
1179 x_base_id => p_base_id,
1180 x_item_code => chk_item_match_rec.new_item,
1181 x_amount => NVL(chk_item_match_rec.item_amount,0),
1182 x_pell_coa_amount => chk_item_match_rec.pell_amt,
1183 x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
1184 x_fixed_cost => chk_item_match_rec.fixed_cost,
1185 x_mode => 'R',
1186 x_lock_flag => chk_item_match_rec.lock_award_flag
1187 );
1188 g_coa_updated := 'Y' ;
1189 lv_item_assigned := 'N';
1190
1191
1192 FOR sec_itm_term_rec IN c_second_item_term(chk_item_match_rec.new_item,l_process_id) LOOP
1193 l_rowid := NULL;
1194 OPEN c_item_term(p_base_id,sec_itm_term_rec.item_code,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1195 FETCH c_item_term INTO l_item_term;
1196 CLOSE c_item_term;
1197
1198 IF l_item_term.row_id is null THEN
1199 RAISE E_SKIP_STUDENT;
1200 END IF;
1201
1202 --skip the item if it is locked
1203 IF l_item_term.lock_flag = 'Y' THEN
1204 fnd_message.set_name('IGF','IGF_AW_SKP_LK_ITM');
1205 fnd_message.set_token('ITEM_CODE',l_items.item_code);
1206 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1207 ELSE
1208 --if the amount is NULL Rate Order Setup is used
1209 IF sec_itm_term_rec.item_term_amount IS NULL THEN
1210 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1211 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NULL '||
1212 'and calling igf_aw_coa_gen.getBaseDetails');
1213 END IF;
1214
1215 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1216
1217 --Rate Order found against the student attributes
1218 IF igf_aw_coa_update.is_attrib_matching(
1219 p_base_id => p_base_id,
1220 p_base_details => l_base_details,
1221 p_ci_cal_type => g_cal_type,
1222 p_ci_sequence_number => g_sequence_number,
1223 p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1224 p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1225 p_item_code => sec_itm_term_rec.item_code,
1226 p_amount => ln_amount,
1227 p_rate_order_num => ln_rate_order
1228 ) THEN
1229
1230 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1231 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
1232 END IF;
1233
1234 g_coa_updated := 'Y';
1235 lv_terms_updated := 'Y';
1236 lv_item_assigned := 'Y';
1237
1238 igf_aw_coa_itm_terms_pkg.update_row(
1239 x_rowid => l_item_term.row_id,
1240 x_base_id => p_base_id,
1241 x_item_code => sec_itm_term_rec.item_code,
1242 x_amount => ln_amount,
1243 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1244 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1245 x_mode => 'R',
1246 x_lock_flag => sec_itm_term_rec.lock_award_flag
1247 );
1248
1249 --skip the term if Rate Order Setup is not available
1250 ELSE
1251 lv_term_not_asgn := 'N';
1252
1253 IF NVL(ln_rate_order,0) <> -1 THEN
1254 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
1255 fnd_message.set_token('ITEM_CODE',sec_itm_term_rec.item_code);
1256 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number));
1257 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1258 END IF;
1259 END IF;
1260
1261 --if the amount is NOT NULL COA Group Setup is used
1262 ELSE
1263 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1264 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NOT NULL');
1265 END IF;
1266
1267 igf_aw_coa_itm_terms_pkg.update_row(
1268 x_rowid => l_item_term.row_id,
1269 x_base_id => p_base_id,
1270 x_item_code => sec_itm_term_rec.item_code,
1271 x_amount => sec_itm_term_rec.item_term_amount,
1272 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1273 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1274 x_mode => 'R',
1275 x_lock_flag => sec_itm_term_rec.lock_award_flag
1276 );
1277
1278
1279 g_coa_updated := 'Y';
1280 lv_terms_updated := 'Y';
1281 lv_item_assigned := 'Y';
1282 END IF;
1283
1284 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1285 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','ld_cal/ld_seq/amount->'||
1286 sec_itm_term_rec.ld_cal_type || ' / '||
1287 sec_itm_term_rec.ld_sequence_number || ' / ' ||
1288 sec_itm_term_rec.item_term_amount);
1289 END IF;
1290 END IF;
1291 END LOOP;
1292
1293 IF lv_item_assigned = 'Y' THEN
1294 fnd_message.set_name('IGF','IGF_AW_COA_ITM_ATTR_CHNG');
1295 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1296 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1297 ELSE
1298 fnd_message.set_name('IGF','IGF_AW_COA_ITM_ATTR_NTCHNG');
1299 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1300 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1301 END IF;
1302
1303 IF lv_term_not_asgn = 'N' THEN
1304 RAISE E_SKIP_STD_NO_TERMS;
1305 END IF;
1306
1307 END IF;
1308 END IF;
1309 END LOOP;
1310
1311 IF lv_terms_updated = 'N' THEN
1312 RAISE E_SKIP_STD_NO_ITEMS;
1313 END IF;
1314
1315 result := 'Y';
1316 END IF;
1317 ELSIF g_update_method = 'OVERWRITE' THEN
1318 IF does_term_mismatch(p_base_id,l_process_id) THEN
1319 --delete and recreate COA
1320 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1321 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling delete_coa');
1322 END IF;
1323
1324 --delete only if it is unlock
1325 IF NOT iscoalocked(p_base_id) THEN
1326 RAISE E_SKIP_STD_NO_ITEMS;
1327 END IF;
1328
1329 delete_coa(p_base_id);
1330
1331 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1332 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','calling add_coa_items with exeorder=FIRST');
1333 END IF;
1334
1335 add_coa_items(
1336 p_base_id,
1337 p_grp_code,
1338 'FIRST',
1339 l_process_id,
1340 result
1341 );
1342 ELSE
1343 FOR chk_item_match_rec IN c_check_item_match(p_base_id,l_process_id) LOOP
1344 l_rowid := NULL;
1345 IF chk_item_match_rec.existing_item IS NULL THEN
1346 --the COA item is not assigned
1347 --so insert the item
1348 g_coa_updated := 'Y' ;
1349 lv_item_assigned := 'N';
1350
1351 igf_aw_coa_items_pkg.insert_row(
1352 x_rowid => l_rowid,
1353 x_base_id => p_base_id,
1354 x_item_code => chk_item_match_rec.new_item,
1355 x_amount => NVL(chk_item_match_rec.item_amount,0),
1356 x_pell_coa_amount => chk_item_match_rec.pell_amt,
1357 x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
1358 x_fixed_cost => chk_item_match_rec.fixed_cost,
1359 x_mode => 'R',
1360 x_lock_flag => chk_item_match_rec.lock_award_flag
1361 );
1362
1363
1364 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1365 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','added item '||chk_item_match_rec.new_item);
1366 END IF;
1367
1368 -- start adding terms and term amounts
1369 FOR sec_itm_term_rec IN c_second_item_term(chk_item_match_rec.new_item,l_process_id) LOOP
1370 l_rowid := NULL;
1371
1372 --if the amount is NULL Rate Order Setup is used
1373 IF sec_itm_term_rec.item_term_amount IS NULL THEN
1374 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1375 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NULL '||
1376 'and calling igf_aw_coa_gen.getBaseDetails');
1377 END IF;
1378
1379 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1380
1381 --Rate Order found against the student attributes
1382 IF igf_aw_coa_update.is_attrib_matching(
1383 p_base_id => p_base_id,
1384 p_base_details => l_base_details,
1385 p_ci_cal_type => g_cal_type,
1386 p_ci_sequence_number => g_sequence_number,
1387 p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1388 p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1389 p_item_code => sec_itm_term_rec.item_code,
1390 p_amount => ln_amount,
1391 p_rate_order_num => ln_rate_order
1392 ) THEN
1393
1394 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1395 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
1396 END IF;
1397
1398 g_coa_updated := 'Y';
1399 lv_terms_updated := 'Y';
1400 lv_item_assigned := 'Y';
1401
1402 igf_aw_coa_itm_terms_pkg.insert_row(
1403 x_rowid => l_rowid,
1404 x_base_id => p_base_id,
1405 x_item_code => sec_itm_term_rec.item_code,
1406 x_amount => ln_amount,
1407 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1408 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1409 x_mode => 'R',
1410 x_lock_flag => sec_itm_term_rec.lock_award_flag
1411 );
1412
1413 --skip the term if Rate Order Setup is not available
1414 ELSE
1415 lv_term_not_asgn := 'N';
1416
1417 IF NVL(ln_rate_order,0) <> -1 THEN
1418 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
1419 fnd_message.set_token('ITEM_CODE',sec_itm_term_rec.item_code);
1420 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number));
1421 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1422 END IF;
1423 END IF;
1424
1425 --if the amount is NOT NULL COA Group Setup is used
1426 ELSE
1427 g_coa_updated := 'Y';
1428 lv_terms_updated := 'Y';
1429 lv_item_assigned := 'Y';
1430
1431 igf_aw_coa_itm_terms_pkg.insert_row(
1432 x_rowid => l_rowid,
1433 x_base_id => p_base_id,
1434 x_item_code => sec_itm_term_rec.item_code,
1435 x_amount => sec_itm_term_rec.item_term_amount,
1436 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1437 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1438 x_mode => 'R',
1439 x_lock_flag => sec_itm_term_rec.lock_award_flag
1440 );
1441 END IF;
1442
1443 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1444 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','ld_cal/ld_seq/amount->'||
1445 sec_itm_term_rec.ld_cal_type || ' / '||
1446 sec_itm_term_rec.ld_sequence_number || ' / ' ||
1447 sec_itm_term_rec.item_term_amount);
1448 END IF;
1449 END LOOP;
1450
1451 IF lv_item_assigned = 'Y' THEN
1452 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_ADD');
1453 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1454 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1455 ELSE
1456 fnd_message.set_name('IGF','IGF_AW_COA_ITEM_NTADD');
1457 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1458 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1459 END IF;
1460
1461 IF lv_term_not_asgn = 'N' THEN
1462 RAISE E_SKIP_STD_NO_TERMS;
1463 END IF;
1464
1465 ELSE
1466 --the student has the COA item assigned
1467 --so if it needs update, update it
1468 IF coa_needs_update(chk_item_match_rec.new_item,p_base_id,chk_item_match_rec.item_amount,chk_item_match_rec.pell_amt,chk_item_match_rec.pell_alt_amt,chk_item_match_rec.fixed_cost) THEN
1469 igf_aw_coa_items_pkg.update_row(
1470 x_rowid => chk_item_match_rec.row_id,
1471 x_base_id => p_base_id,
1472 x_item_code => chk_item_match_rec.new_item,
1473 x_amount => NVL(chk_item_match_rec.item_amount,0),
1474 x_pell_coa_amount => chk_item_match_rec.pell_amt,
1475 x_alt_pell_amount => chk_item_match_rec.pell_alt_amt,
1476 x_fixed_cost => chk_item_match_rec.fixed_cost,
1477 x_mode => 'R',
1478 x_lock_flag => chk_item_match_rec.lock_award_flag
1479 );
1480 g_coa_updated := 'Y' ;
1481 lv_item_assigned := 'N';
1482
1483
1484 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1485 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','changed item '||chk_item_match_rec.new_item);
1486 END IF;
1487
1488 FOR sec_itm_term_rec IN c_second_item_term(chk_item_match_rec.new_item,l_process_id) LOOP
1489 l_rowid := NULL;
1490 OPEN c_item_term(p_base_id,sec_itm_term_rec.item_code,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1491 FETCH c_item_term INTO l_item_term;
1492 CLOSE c_item_term;
1493
1494 IF l_item_term.row_id is null THEN
1495 RAISE E_SKIP_STUDENT;
1496 END IF;
1497
1498 --skip the item if it is locked
1499 IF l_item_term.lock_flag = 'Y' THEN
1500 fnd_message.set_name('IGF','IGF_AW_SKP_LK_ITM');
1501 fnd_message.set_token('ITEM_CODE',l_items.item_code);
1502 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1503 ELSE
1504
1505 --if the amount is NULL Rate Order Setup is used
1506 IF sec_itm_term_rec.item_term_amount IS NULL THEN
1507 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1508 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NULL '||
1509 'and calling igf_aw_coa_gen.getBaseDetails');
1510 END IF;
1511
1512 l_base_details := igf_aw_coa_gen.getBaseDetails(p_base_id,sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number);
1513
1514 --Rate Order found against the student attributes
1515 IF igf_aw_coa_update.is_attrib_matching(
1516 p_base_id => p_base_id,
1517 p_base_details => l_base_details,
1518 p_ci_cal_type => g_cal_type,
1519 p_ci_sequence_number => g_sequence_number,
1520 p_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1521 p_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1522 p_item_code => sec_itm_term_rec.item_code,
1523 p_amount => ln_amount,
1524 p_rate_order_num => ln_rate_order
1525 ) THEN
1526
1527 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1528 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','is_attrib_matching found');
1529 END IF;
1530
1531 g_coa_updated := 'Y';
1532 lv_terms_updated := 'Y';
1533 lv_item_assigned := 'Y';
1534
1535 igf_aw_coa_itm_terms_pkg.update_row(
1536 x_rowid => l_item_term.row_id,
1537 x_base_id => p_base_id,
1538 x_item_code => sec_itm_term_rec.item_code,
1539 x_amount => ln_amount,
1540 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1541 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1542 x_mode => 'R',
1543 x_lock_flag => sec_itm_term_rec.lock_award_flag
1544 );
1545
1546 --skip the term if Rate Order Setup is not available
1547 ELSE
1548 lv_term_not_asgn := 'N';
1549
1550 IF NVL(ln_rate_order,0) <> -1 THEN
1551 fnd_message.set_name('IGF','IGF_AW_ITEM_SKIP');
1552 fnd_message.set_token('ITEM_CODE',sec_itm_term_rec.item_code);
1553 fnd_message.set_token('TERM_CODE',igf_gr_gen.get_alt_code(sec_itm_term_rec.ld_cal_type,sec_itm_term_rec.ld_sequence_number));
1554 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1555 END IF;
1556 END IF;
1557
1558 --if the amount is NOT NULL COA Group Setup is used
1559 ELSE
1560 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1561 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.add_coa_items.debug','sec_itm_term_rec.item_term_amount IS NOT NULL');
1562 END IF;
1563
1564 igf_aw_coa_itm_terms_pkg.update_row(
1565 x_rowid => l_item_term.row_id,
1566 x_base_id => p_base_id,
1567 x_item_code => sec_itm_term_rec.item_code,
1568 x_amount => sec_itm_term_rec.item_term_amount,
1569 x_ld_cal_type => sec_itm_term_rec.ld_cal_type,
1570 x_ld_sequence_number => sec_itm_term_rec.ld_sequence_number,
1571 x_mode => 'R',
1572 x_lock_flag => sec_itm_term_rec.lock_award_flag
1573 );
1574 g_coa_updated := 'Y';
1575 lv_terms_updated := 'Y';
1576 lv_item_assigned := 'Y';
1577 END IF;
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_coa_calc.add_coa_items.debug','ld_cal/ld_seq/amount->'||
1581 sec_itm_term_rec.ld_cal_type || ' / '||
1582 sec_itm_term_rec.ld_sequence_number || ' / ' ||
1583 sec_itm_term_rec.item_term_amount);
1584 END IF;
1585 END IF;
1586 END LOOP;
1587
1588 IF lv_item_assigned = 'Y' THEN
1589 fnd_message.set_name('IGF','IGF_AW_COA_ITM_ATTR_CHNG');
1590 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1591 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1592 ELSE
1593 fnd_message.set_name('IGF','IGF_AW_COA_ITM_ATTR_NTCHNG');
1594 fnd_message.set_token('ITEM_CODE',chk_item_match_rec.new_item);
1595 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1596 END IF;
1597
1598 IF lv_term_not_asgn = 'N' THEN
1599 RAISE E_SKIP_STD_NO_TERMS;
1600 END IF;
1601
1602 END IF;
1603 END IF;
1604 END LOOP;
1605
1606 IF lv_terms_updated = 'N' THEN
1607 RAISE E_SKIP_STD_NO_ITEMS;
1608 END IF;
1609 result := 'Y';
1610 END IF;
1611 END IF;
1612 END IF;
1613 END IF;
1614
1615
1616 FOR l_terms IN c_terms(p_base_id)
1617 LOOP
1618
1619 --if the item code is NOT NULL insert
1620 IF l_terms.item_code IS NOT NULL THEN
1621 OPEN c_items(p_base_id,l_terms.item_code);
1622 FETCH c_items INTO l_items;
1623 CLOSE c_items;
1624
1625 igf_aw_coa_items_pkg.update_row(
1626 x_rowid => l_items.row_id,
1627 x_base_id => l_items.base_id,
1628 x_item_code => l_items.item_code,
1629 x_amount => l_terms.amount,
1630 x_pell_coa_amount => l_items.pell_coa_amount,
1631 x_alt_pell_amount => l_items.alt_pell_amount,
1632 x_fixed_cost => l_items.fixed_cost,
1633 x_legacy_record_flag => l_items.legacy_record_flag,
1634 x_mode => 'R',
1635 x_lock_flag => l_items.lock_flag
1636 );
1637
1638 END IF;
1639 END LOOP;
1640
1641 EXCEPTION
1642 WHEN E_SKIP_STUDENT THEN
1643 RAISE E_SKIP_STUDENT;
1644 WHEN E_SKIP_STD_NO_ITEMS THEN
1645 RAISE E_SKIP_STD_NO_ITEMS;
1646 WHEN E_SKIP_STD_NO_TERMS THEN
1647 RAISE E_SKIP_STD_NO_TERMS;
1648 WHEN OTHERS THEN
1649 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1650 fnd_message.set_token('NAME','IGF_AW_COA_CALC.ADD_COA_ITEMS' || ' '|| SQLERRM);
1651 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1652 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.add_coa_items.exception','sql error message: '||SQLERRM);
1653 END IF;
1654
1655 igs_ge_msg_stack.conc_exception_hndl;
1656 app_exception.raise_exception;
1657
1658 END add_coa_items;
1659
1660
1661
1662 ---------------------------------------------------------------------------------
1663 --Procedure to print output file
1664 --CREATED BY:gmuralid
1665 -------------------------------------------------------------------------------
1666 PROCEDURE print_output_file(
1667 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1668 ) IS
1669
1670 CURSOR c_out_file(
1671 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1672 ) IS
1673 SELECT ca.alternate_code term,
1674 SUM(NVL(terms.amount,0)) amount
1675 FROM igf_aw_coa_itm_terms terms,
1676 igs_ca_inst ca
1677 WHERE ca.cal_type = terms.ld_cal_type
1678 AND ca.sequence_number = terms.ld_sequence_number
1679 AND terms.base_id = c_base_id
1680 GROUP BY base_id,
1681 alternate_code
1682 ORDER BY 1;
1683 c_out_file_rec c_out_file%ROWTYPE;
1684
1685 CURSOR c_total_coa(
1686 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1687 ) IS
1688 SELECT coa_f total
1689 FROM igf_ap_fa_base_rec
1690 WHERE base_id = c_base_id;
1691 c_total_coa_rec c_total_coa%ROWTYPE;
1692
1693 BEGIN
1694
1695 IF g_b_header THEN
1696 fnd_message.set_name('IGF','IGF_AW_COA_PRINT_DTLS');
1697 fnd_file.put_line(fnd_file.output,fnd_message.get);
1698 fnd_file.put_line(fnd_file.output,RPAD('-',60,'-'));
1699 fnd_file.new_line(fnd_file.output,1);
1700 g_b_header := FALSE;
1701 END IF;
1702
1703 fnd_file.new_line(fnd_file.output,1);
1704 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER'),15)
1705 ||LPAD(igf_gr_gen.get_per_num(p_base_id),15));
1706 fnd_file.new_line(fnd_file.output,1);
1707
1708
1709 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','TERM'),30)
1710 || LPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','COA_TP_TOT'),30));
1711 FOR c_out_file_rec IN c_out_file(p_base_id) LOOP
1712 fnd_file.put_line(fnd_file.output,RPAD(c_out_file_rec.term,30)
1713 ||LPAD(TO_CHAR(c_out_file_rec.amount,'FM9999999990D90'),30));
1714 END LOOP;
1715
1716 OPEN c_total_coa(p_base_id);
1717 FETCH c_total_coa INTO c_total_coa_rec;
1718 fnd_file.new_line(fnd_file.output,1);
1719 fnd_file.put_line(fnd_file.output,RPAD('-',60,'-'));
1720 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','COA_TOT'),30)
1721 ||LPAD(TO_CHAR(c_total_coa_rec.total,'FM9999999990D90'),30));
1722 CLOSE c_total_coa;
1723
1724 fnd_file.put_line(fnd_file.output,RPAD('-',60,'-'));
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1728 fnd_message.set_token('NAME','IGF_AW_COA_CALC.PRINT_OUTPUT_FILE' || ' '|| SQLERRM);
1729 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1730 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.print_output_file.exception','sql error message: '||SQLERRM);
1731 END IF;
1732 igs_ge_msg_stack.conc_exception_hndl;
1733 app_exception.raise_exception;
1734 END print_output_file;
1735
1736
1737
1738 PROCEDURE process_student(
1739 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1740 p_grp_code igf_aw_coa_grp_item.coa_code%TYPE,
1741 p_process_id NUMBER
1742 ) IS
1743 ------------------------------------------------------------------
1744 --Created by :
1745 --Date created:
1746 --
1747 --Purpose:
1748 --
1749 --
1750 --Known limitations/enhancements and/or remarks:
1751 --
1752 --Change History:
1753 --Who When What
1754 --veramach 08-Jan-2004 FA CCR 118 COA Updates
1755 -- Added validations for overaward situation and PELL COA change
1756 -------------------------------------------------------------------
1757
1758 -- Cursor below retrieves all existing COA information for a student
1759 CURSOR cur_per_coa(
1760 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1761 ) IS
1762 SELECT 'x'
1763 FROM igf_aw_coa_itm_terms coa
1764 WHERE coa.base_id = p_base_id
1765 AND rownum = 1;
1766
1767 l_cur_per_coa cur_per_coa%ROWTYPE;
1768 lv_result VARCHAR2(5);
1769
1770 -- Get pell COA amounts
1771 CURSOR c_pell_coa(
1772 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1773 ) IS
1774 SELECT SUM(NVL(pell_coa_amount,0)) pell_coa,
1775 SUM(NVL(alt_pell_amount,0)) alt_pell_coa
1776 FROM igf_aw_coa_items
1777 WHERE base_id = cp_base_id;
1778
1779 l_old_coa c_pell_coa%ROWTYPE;
1780 l_new_coa c_pell_coa%ROWTYPE;
1781
1782 -- check whether the student has pell award
1783 CURSOR c_pell_award(
1784 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1785 ) IS
1786 SELECT 'x'
1787 FROM igf_aw_fund_cat_all fcat,
1788 igf_aw_fund_mast_all fmast,
1789 igf_aw_award_all awd
1790 WHERE fcat.fed_fund_code = 'PELL'
1791 AND fcat.fund_code = fmast.fund_code
1792 AND fmast.fund_id = awd.fund_id
1793 AND awd.award_status IN ('ACCEPTED','OFFERED')
1794 AND awd.base_id = cp_base_id;
1795
1796 l_pell_award c_pell_award%ROWTYPE;
1797
1798
1799 --This cursor is to fetch person details
1800 CURSOR c_base_rec (
1801 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1802 ) IS
1803 SELECT NVL(fab.lock_coa_flag,'N') lock_coa_flag
1804 FROM igf_ap_fa_base_rec fab
1805 WHERE fab.base_id = c_base_id;
1806
1807 l_base_rec c_base_rec%ROWTYPE;
1808
1809
1810 ln_overaward NUMBER := 0;
1811
1812 BEGIN
1813
1814 OPEN c_pell_coa(p_base_id);
1815 FETCH c_pell_coa INTO l_old_coa;
1816 CLOSE c_pell_coa;
1817
1818 SAVEPOINT start_coa_calc;
1819 g_coa_updated := 'N' ;
1820
1821 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1822 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','starting process_student with ' ||
1823 'base_id/group->' ||
1824 p_base_id || ' / ' ||
1825 p_grp_code);
1826 END IF;
1827
1828 fnd_file.put_line(fnd_file.log,' --------------------------------------------------------');
1829 fnd_message.set_name('IGF','IGF_AW_COA_PROCESS_STD');
1830 fnd_file.put_line(fnd_file.log,RPAD(' ',5) || RPAD(fnd_message.get,55) || igf_gr_gen.get_per_num(p_base_id));
1831 fnd_file.new_line(fnd_file.log,1);
1832
1833 OPEN c_base_rec(p_base_id);
1834 FETCH c_base_rec INTO l_base_rec;
1835 CLOSE c_base_rec;
1836
1837 IF l_base_rec.lock_coa_flag = 'Y' THEN
1838 fnd_message.set_name('IGF','IGF_AW_STUD_SKIP');
1839 fnd_file.put_line(fnd_file.log,RPAD(' ',10)||fnd_message.get);
1840 ELSE
1841 OPEN cur_per_coa(p_base_id);
1842 FETCH cur_per_coa INTO l_cur_per_coa ;
1843
1844 IF cur_per_coa%FOUND THEN
1845 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1846 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','calling add_coa_items with exeorder SECOND');
1847 END IF;
1848 add_coa_items(
1849 p_base_id,
1850 p_grp_code,
1851 'SECOND',
1852 p_process_id,
1853 lv_result
1854 );
1855 ELSIF cur_per_coa%NOTFOUND THEN
1856 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1857 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','calling add_coa_items with exeorder FIRST');
1858 END IF;
1859 add_coa_items(
1860 p_base_id,
1861 p_grp_code,
1862 'FIRST',
1863 p_process_id,
1864 lv_result
1865 );
1866 END IF;
1867 CLOSE cur_per_coa;
1868
1869 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1870 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','add_coa_items_returned lv_result:'||lv_result);
1871 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','calling igf_aw_gen_003.updating_coa_in_fa_base');
1872 END IF;
1873 igf_aw_gen_003.updating_coa_in_fa_base(p_base_id);
1874
1875 IF lv_result = 'Y' AND g_coa_updated = 'Y' THEN
1876 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1877 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','calling print_output_file, and checking overaward');
1878 END IF;
1879
1880 print_output_file(p_base_id);
1881
1882 IF igf_aw_packng_subfns.is_over_award_occured(p_base_id) THEN
1883 fnd_message.set_name('IGF','IGF_AW_COA_RSLT_OVERAWD');
1884 fnd_message.set_token('OVER_AWD_AMT',overaward_amount(p_base_id));
1885 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1886 END IF;
1887
1888 OPEN c_pell_award(p_base_id);
1889 FETCH c_pell_award INTO l_pell_award;
1890
1891 IF c_pell_award%FOUND THEN
1892
1893 OPEN c_pell_coa(p_base_id);
1894 FETCH c_pell_coa INTO l_new_coa;
1895 CLOSE c_pell_coa;
1896
1897 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1898 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','l_old_coa.pell_coa:'||l_old_coa.pell_coa);
1899 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','l_new_coa.pell_coa:'||l_new_coa.pell_coa);
1900 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','l_old_coa.alt_pell_coa:'||l_old_coa.alt_pell_coa);
1901 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.process_student.debug','l_new_coa.alt_pell_coa:'||l_new_coa.alt_pell_coa);
1902 END IF;
1903
1904 IF l_old_coa.pell_coa <> l_new_coa.pell_coa OR l_old_coa.alt_pell_coa <> l_new_coa.alt_pell_coa THEN
1905 fnd_message.set_name('IGF','IGF_AW_PELL_COA_CHNG');
1906 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1907 END IF;
1908
1909 END IF;
1910
1911 END IF;
1912
1913 IF g_coa_updated = 'N' THEN
1914 fnd_message.set_name('IGF','IGF_AW_NO_CHNG');
1915 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get);
1916 END IF;
1917
1918 fnd_message.set_name('IGF','IGF_AW_COA_ASSIGN_COMP');
1919 fnd_message.set_token('PERSON_NUMBER',igf_gr_gen.get_per_num(p_base_id));
1920 fnd_file.put_line(fnd_file.log,RPAD(' ',10) || fnd_message.get);
1921 fnd_file.new_line(fnd_file.log,1);
1922
1923 COMMIT;
1924
1925 END IF;
1926
1927
1928 EXCEPTION
1929 WHEN E_SKIP_STUDENT THEN
1930 ROLLBACK TO start_coa_calc;
1931 fnd_message.set_name('IGF','IGF_AW_INCON_ITM_TERMS');
1932 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1933
1934 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
1935 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1936
1937 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1938 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.process_student.exception','sql error message: '||SQLERRM);
1939 END IF;
1940
1941 WHEN E_SKIP_STD_NO_ITEMS THEN
1942 ROLLBACK TO start_coa_calc;
1943 fnd_message.set_name('IGF','IGF_AW_STD_SKIP_ASSGN');
1944 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1945
1946 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1947 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.process_student.exception','sql error message: '||SQLERRM);
1948 END IF;
1949
1950 WHEN E_SKIP_STD_NO_TERMS THEN
1951 ROLLBACK TO start_coa_calc;
1952 fnd_message.set_name('IGF','IGF_AW_COA_SKIP_STD');
1953 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1954
1955 fnd_message.set_name('IGF','IGF_AW_RATE_NOT_AVAIL');
1956 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1957
1958 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1959 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.process_student.exception','sql error message: '||SQLERRM);
1960 END IF;
1961
1962 WHEN OTHERS THEN
1963 ROLLBACK TO start_coa_calc;
1964
1965 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1966 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_coa_calc.process_student.exception','sql error message: '||SQLERRM);
1967 END IF;
1968
1969 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1970 fnd_message.set_token('NAME','IGF_AW_COA_CALC.PROCESS_STUDENT' || ' '||SQLERRM);
1971 fnd_file.put_line(fnd_file.log,RPAD(' ',10,' ') || fnd_message.get());
1972
1973 fnd_message.set_name('IGF','IGF_AW_RATE_NOT_AVAIL');
1974 fnd_file.put_line(fnd_file.log,RPAD(' ',10)|| fnd_message.get());
1975
1976 END process_student;
1977
1978
1979
1980 --
1981 -- This procedure is the callable from concurrent manager
1982 --
1983
1984 PROCEDURE run(
1985 errbuf OUT NOCOPY VARCHAR2,
1986 retcode OUT NOCOPY NUMBER,
1987 p_award_year IN VARCHAR2,
1988 p_grp_code IN igf_aw_coa_grp_item.coa_code%TYPE,
1989 p_update_coa IN VARCHAR2,
1990 p_update_method IN VARCHAR2,
1991 l_run_type IN VARCHAR2,
1992 p_pergrp_id IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
1993 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
1994 ) IS
1995 --------------------------------------------------------------------------------
1996 -- this procedure is called from concurrent manager.
1997 -- if the parameters passed are not correct then procedure exits
1998 -- giving reasons for errors.
1999 -- Created By : cdcruz
2000 -- Modified By : gmuralid
2001 --Change History:
2002 --Who When What
2003 --ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
2004 --tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
2005 --veramach 08-Jan-2004 FA CCR 118 COA Updates
2006 -- Added 2 new parameters p_update_coa and p_update_method
2007 -- Added validations on the 2 new parameters
2008 --------------------------------------------------------------------------------
2009
2010
2011 l_ci_cal_type VARCHAR2(10) ;
2012 l_ci_sequence_number NUMBER(15) ;
2013
2014 param_exception EXCEPTION;
2015
2016 --Cursor below retrieves all the person belonging to a person id group
2017
2018 -- Variables for the dynamic person id group
2019 lv_status VARCHAR2(1) := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
2020 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
2021 lv_sql_stmt VARCHAR(32767);
2022
2023
2024 TYPE CpregrpCurTyp IS REF CURSOR ;
2025 cur_per_grp CpregrpCurTyp ;
2026 TYPE CpergrpTyp IS RECORD(
2027 person_id igf_ap_fa_base_rec_all.person_id%TYPE,
2028 person_number igs_pe_person_base_v.person_number%TYPE
2029 );
2030 per_grp_rec CpergrpTyp ;
2031
2032 l_process_id NUMBER(15);
2033
2034 --Cursor below retrieves all the students belonging to a given AWARD YEAR
2035
2036 CURSOR c_per_awd_yr(
2037 c_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2038 c_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2039 ) IS
2040 SELECT fa.base_id
2041 FROM igf_ap_fa_base_rec_all fa
2042 WHERE fa.ci_cal_type = c_ci_cal_type
2043 AND fa.ci_sequence_number = c_ci_sequence_number;
2044 per_awd_rec c_per_awd_yr%ROWTYPE;
2045
2046 CURSOR c_temp_del(
2047 c_process_id NUMBER
2048 ) IS
2049 SELECT row_id rid
2050 FROM igf_aw_award_t
2051 WHERE process_id = c_process_id;
2052 temp_del_rec c_temp_del%ROWTYPE;
2053
2054 CURSOR c_group_code(
2055 c_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE
2056 ) IS
2057 SELECT group_cd
2058 FROM igs_pe_persid_group_all
2059 WHERE group_id = c_grp_id;
2060 c_grp_cd c_group_code%ROWTYPE;
2061
2062 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
2063 lv_err_msg fnd_new_messages.message_name%TYPE;
2064
2065 BEGIN
2066 igf_aw_gen.set_org_id(NULL);
2067 retcode := 0;
2068 errbuf := NULL;
2069 g_b_header := TRUE;
2070 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
2071 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
2072
2073 g_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
2074 g_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
2075 g_update_coa := p_update_coa;
2076 g_update_method := p_update_method;
2077
2078 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2079 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_award_year:'||p_award_year);
2080 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_grp_code:'||p_grp_code);
2081 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','l_run_type:'||l_run_type);
2082 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_pergrp_id:'||p_pergrp_id);
2083 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_base_id:'||p_base_id);
2084 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_update_coa:'||p_update_coa);
2085 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','p_update_method:'||p_update_method);
2086 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','l_ci_cal_type:'||l_ci_cal_type);
2087 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_coa_calc.run.debug','l_ci_sequence_number:'||l_ci_sequence_number);
2088 END IF;
2089
2090 fnd_file.new_line(fnd_file.log,1);
2091
2092 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
2093 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),60) || igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number));
2094 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_AP_RULE','GROUP_CODE'),60) || p_grp_code);
2095 OPEN c_group_code(p_pergrp_id);
2096 FETCH c_group_code INTO c_grp_cd;
2097 CLOSE c_group_code;
2098 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),60) || c_grp_cd.group_cd);
2099 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),60) || igf_gr_gen.get_per_num(p_base_id));
2100 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','UPDATE_COA'),60) || p_update_coa);
2101 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','UPDATE_METHOD'),60) || igf_aw_gen.lookup_desc('IGF_AW_COA_UPD_MTHD',p_update_method));
2102
2103 fnd_file.new_line(fnd_file.log,2);
2104 fnd_message.set_name('IGF','IGF_AW_PROCESS_COA_CAL');
2105 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get,60) || igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number));
2106
2107 fnd_file.new_line(fnd_file.log,1);
2108
2109 IF p_award_year IS NULL OR p_grp_code IS NULL THEN
2110 RAISE param_exception;
2111
2112 ELSIF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
2113 RAISE param_exception;
2114
2115 ELSIF (p_pergrp_id IS NOT NULL) AND (p_base_id IS NOT NULL) THEN
2116 RAISE param_exception;
2117
2118 ELSIF l_run_type = 'P' AND p_pergrp_id IS NULL THEN
2119 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_P');
2120 fnd_file.put_line(fnd_file.log,fnd_message.get);
2121 RAISE param_exception;
2122
2123 ELSIF l_run_type = 'S' AND p_base_id IS NULL THEN
2124 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_S');
2125 fnd_file.put_line(fnd_file.log,fnd_message.get);
2126 RAISE param_exception;
2127
2128 ELSIF p_update_coa = 'Y' AND p_update_method IS NULL THEN
2129 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_UPD');
2130 fnd_file.put_line(fnd_file.log,fnd_message.get);
2131 RAISE param_exception;
2132
2133 ELSIF l_run_type = 'P' AND (p_pergrp_id IS NOT NULL) THEN
2134 --Bug #5021084
2135 lv_sql_stmt := igf_ap_ss_pkg.get_pid(p_pergrp_id,lv_status,lv_group_type);
2136
2137 --Bug #5021084. Passing Group ID if the group type is STATIC.
2138 IF lv_group_type = 'STATIC' THEN
2139 OPEN cur_per_grp FOR
2140 'SELECT party_id person_id,
2141 party_number person_number
2142 FROM hz_parties
2143 WHERE party_id IN ('||lv_sql_stmt||') 'USING p_pergrp_id;
2144 ELSIF lv_group_type = 'DYNAMIC' THEN
2145 OPEN cur_per_grp FOR
2146 'SELECT party_id person_id,
2147 party_number person_number
2148 FROM hz_parties
2149 WHERE party_id IN ('||lv_sql_stmt||')';
2150 END IF;
2151
2152 FETCH cur_per_grp INTO per_grp_rec;
2153
2154 IF (cur_per_grp%NOTFOUND) THEN
2155 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
2156 fnd_file.put_line(fnd_file.log,fnd_message.get);
2157 ELSE
2158 SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
2159 populate_setup_table(p_grp_code,l_ci_cal_type,l_ci_sequence_number,l_process_id);
2160
2161 OPEN c_group_code(p_pergrp_id);
2162 FETCH c_group_code INTO c_grp_cd;
2163
2164 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
2165 fnd_message.set_token('P_PER_GRP',c_grp_cd.group_cd);
2166 fnd_file.put_line(fnd_file.log,fnd_message.get );
2167 fnd_file.new_line(fnd_file.log,1);
2168
2169 CLOSE c_group_code;
2170
2171 LOOP
2172 --
2173 -- check if person has a fa base record
2174 --
2175 ln_base_id := NULL;
2176 lv_err_msg := NULL;
2177
2178 igf_gr_gen.get_base_id(
2179 l_ci_cal_type,
2180 l_ci_sequence_number,
2181 per_grp_rec.person_id,
2182 ln_base_id,
2183 lv_err_msg
2184 );
2185
2186 IF lv_err_msg = 'NULL' THEN
2187 process_student(
2188 ln_base_id,
2189 p_grp_code,
2190 l_process_id
2191 );
2192 ELSE
2193 fnd_message.set_name('IGF','IGF_AW_NO_FA_BASE_EXISTS');
2194 fnd_message.set_token('PERS_NUM',per_grp_rec.person_number);
2195 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number));
2196 fnd_file.put_line(fnd_file.log,RPAD(' ',5) || fnd_message.get);
2197 fnd_file.new_line(fnd_file.log,1);
2198 END IF;
2199
2200 FETCH cur_per_grp INTO per_grp_rec;
2201 EXIT WHEN cur_per_grp%NOTFOUND;
2202 END LOOP;
2203 CLOSE cur_per_grp;
2204 END IF;
2205
2206 --COMPUTATION ONLY IF PERSON NUMBER IS PRESENT
2207
2208 ELSIF l_run_type = 'S' AND (p_pergrp_id is NULL) AND (p_base_id IS NOT NULL) THEN
2209 SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
2210 populate_setup_table(
2211 p_grp_code,
2212 l_ci_cal_type,
2213 l_ci_sequence_number,
2214 l_process_id
2215 );
2216 process_student(
2217 p_base_id,
2218 p_grp_code,
2219 l_process_id
2220 );
2221
2222 --COMPUTATION FOR AWARD YEAR ONLY
2223 ELSIF l_run_type = 'Y' AND (p_pergrp_id IS NULL) AND (p_base_id is NULL) THEN
2224 OPEN c_per_awd_yr(l_ci_cal_type,l_ci_sequence_number);
2225 FETCH c_per_awd_yr INTO per_awd_rec;
2226
2227 IF (c_per_awd_yr%NOTFOUND) THEN
2228 fnd_message.set_name('IGF','IGF_AW_COA_NO_STDS');
2229 fnd_file.put_line(fnd_file.log,fnd_message.get);
2230 fnd_file.new_line(fnd_file.log,1);
2231 ELSE
2232 SELECT igf_aw_process_s.nextval INTO l_process_id FROM dual ;
2233 populate_setup_table(
2234 p_grp_code,
2235 l_ci_cal_type,
2236 l_ci_sequence_number,
2237 l_process_id
2238 );
2239
2240 fnd_message.set_name('IGF','IGF_AW_PROC_AWD');
2241 fnd_message.set_token('AWD_YR',p_award_year);
2242 fnd_file.put_line(fnd_file.log,fnd_message.get );
2243 fnd_file.new_line(fnd_file.log,1);
2244
2245 LOOP
2246 IF per_awd_rec.base_id IS NOT NULL THEN
2247 process_student(
2248 per_awd_rec.base_id,
2249 p_grp_code,
2250 l_process_id
2251 );
2252 END IF;
2253 FETCH c_per_awd_yr INTO per_awd_rec;
2254 EXIT WHEN c_per_awd_yr%NOTFOUND;
2255 END LOOP;
2256
2257 CLOSE c_per_awd_yr;
2258 END IF;
2259 END IF;
2260
2261 FOR temp_del_rec IN c_temp_del(l_process_id) LOOP
2262 igf_aw_award_t_pkg.delete_row(temp_del_rec.rid);
2263 END LOOP;
2264
2265 COMMIT;
2266
2267 EXCEPTION
2268 WHEN param_exception THEN
2269 retcode:=2;
2270 fnd_message.set_name('IGF','IGF_AW_PARAM_ERR');
2271 igs_ge_msg_stack.add;
2272 errbuf := fnd_message.get;
2273
2274 WHEN app_exception.record_lock_exception THEN
2275 ROLLBACK;
2276 retcode:=2;
2277 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
2278 igs_ge_msg_stack.add;
2279 errbuf := fnd_message.get;
2280
2281 WHEN OTHERS THEN
2282 ROLLBACK;
2283 retcode:=2;
2284 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2285 igs_ge_msg_stack.add;
2286 errbuf := fnd_message.get || ' '|| SQLERRM;
2287 END run;
2288
2289 END igf_aw_coa_calc;