[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_ROLLOVER
Source
1 PACKAGE BODY igf_aw_rollover AS
2 /* $Header: IGFAW08B.pls 120.11 2006/02/01 02:58:55 ridas ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGF_AW_ROLLOVER |
11 | |
12 | NOTES |
13 | |
14 | |
15 | HISTORY |
16 | Who When What |
17 | ridas 16-MAY-2005 Build #4382389 |
18 | Rolling over all the award based setups |
19 | |
20 *=======================================================================*/
21
22 --Create fund todo items for the target award year
23 FUNCTION create_fund_todo (p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
24 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
25 p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
26 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
27 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
28 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
29 p_todo_item OUT NOCOPY igf_ap_td_item_mst_all.item_code%TYPE
30 )
31 RETURN BOOLEAN IS
32 --------------------------------------------------------------------------------
33 -- Created by : ridas, Oracle India
34 -- Date created: 16-MAY-2005
35
36 -- Change History:
37 -- Who When What
38 --
39 --------------------------------------------------------------------------------
40
41 --Cursor to fetch the todo details attached to the fund id
42 CURSOR c_get_fund_todo ( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
43 ) IS
44 SELECT DISTINCT mst.*
45 FROM igf_ap_td_item_mst_all mst,
46 igf_aw_fund_td_map_all map
47 WHERE map.fund_id = cp_ref_fund_id
48 AND map.item_sequence_number = mst.todo_number;
49
50 l_get_fund_todo c_get_fund_todo%ROWTYPE;
51
52
53 --Cursor to fetch the todo items attached to the fund id
54 CURSOR c_fund_td ( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
55 ) IS
56 SELECT tdm.item_code,
57 tdm.career_item
58 FROM igf_aw_fund_td_map_all ftodo,
59 igf_ap_td_item_mst_all tdm
60 WHERE tdm.todo_number = ftodo.item_sequence_number
61 AND ftodo.fund_id = cp_ref_fund_id;
62
63 l_fund_td c_fund_td%ROWTYPE;
64
65
66 --Cursor to fetch the todo number of a career item code
67 CURSOR c_get_cr_td_number( cp_item_code igf_ap_td_item_mst_all.item_code%TYPE
68 ) IS
69 SELECT todo_number
70 FROM igf_ap_td_item_mst_all
71 WHERE ci_cal_type IS NULL
72 AND ci_sequence_number IS NULL
73 AND item_code = cp_item_code
74 AND NVL(career_item,'N') = 'Y';
75
76
77 --Cursor to fetch the todo number of an item code for the To Award Year
78 CURSOR c_get_td_number( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
79 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
80 cp_item_code igf_ap_td_item_mst_all.item_code%TYPE
81 ) IS
82 SELECT todo_number
83 FROM igf_ap_td_item_mst_all
84 WHERE item_code = cp_item_code
85 AND ci_cal_type = cp_to_cal_type
86 AND ci_sequence_number = cp_to_sequence_number
87 AND NVL(career_item,'N') = 'N';
88
89
90 l_rowid VARCHAR2(25);
91 l_todo_number igf_ap_td_item_mst.todo_number%TYPE;
92 l_ftodo_id igf_aw_fund_td_map.ftodo_id%TYPE;
93 l_item_sequence_number igf_aw_fund_td_map_all.item_sequence_number%TYPE;
94 l_todo_item igf_ap_td_item_mst_all.item_code%TYPE := NULL;
95 lv_return_flg VARCHAR2(1);
96
97 BEGIN
98
99 FOR l_get_fund_todo IN c_get_fund_todo(p_ref_fund_id)
100 LOOP
101 IF (l_get_fund_todo.ci_cal_type IS NOT NULL AND l_get_fund_todo.ci_sequence_number IS NOT NULL AND NVL(l_get_fund_todo.career_item,'N')='N') THEN
102 OPEN c_get_td_number(p_to_cal_type, p_to_sequence_number, l_get_fund_todo.item_code);
103 FETCH c_get_td_number INTO l_item_sequence_number;
104 l_todo_item := l_get_fund_todo.item_code;
105
106 IF c_get_td_number%NOTFOUND THEN
107 l_rowid := NULL;
108 l_todo_number := NULL;
109
110 BEGIN
111 igf_ap_td_item_mst_pkg.insert_row(
112 x_rowid => l_rowid,
113 x_todo_number => l_todo_number,
114 x_item_code => l_get_fund_todo.item_code,
115 x_ci_cal_type => p_to_cal_type,
116 x_ci_sequence_number => p_to_sequence_number,
117 x_description => l_get_fund_todo.description,
118 x_corsp_mesg => l_get_fund_todo.corsp_mesg,
119 x_career_item => l_get_fund_todo.career_item,
120 x_required_for_application => l_get_fund_todo.required_for_application,
121 x_freq_attempt => l_get_fund_todo.freq_attempt,
122 x_max_attempt => l_get_fund_todo.max_attempt,
123 x_mode => 'R',
124 x_system_todo_type_code => l_get_fund_todo.system_todo_type_code,
125 x_application_code => l_get_fund_todo.application_code,
126 x_display_in_ss_flag => l_get_fund_todo.display_in_ss_flag,
127 x_ss_instruction_txt => l_get_fund_todo.ss_instruction_txt,
128 x_allow_attachment_flag => l_get_fund_todo.allow_attachment_flag,
129 x_document_url_txt => l_get_fund_todo.document_url_txt
130 );
131
132 IF l_get_fund_todo.system_todo_type_code = 'INSTAPP' THEN
133 lv_return_flg := rollover_inst_attch_todo ( p_frm_cal_type => p_frm_cal_type,
134 p_frm_sequence_number => p_frm_sequence_number,
135 p_to_cal_type => p_to_cal_type,
136 p_to_sequence_number => p_to_sequence_number,
137 p_application_code => l_get_fund_todo.application_code
138 );
139
140 IF lv_return_flg = 'Y' THEN
141 p_todo_item := l_todo_item;
142
143 fnd_message.set_name('IGF','IGF_AP_INST_ATTCH_TODO_ERR');
144 fnd_message.set_token('APPLICATION',l_get_fund_todo.application_code);
145 fnd_message.set_token('ITEM',l_todo_item);
146 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
147
148 RETURN FALSE;
149 END IF;
150 END IF;
151 EXCEPTION
152 WHEN OTHERS THEN
153 CLOSE c_get_td_number;
154 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
155 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_fund_todo.exception','Error while creating To Do Items');
156 END IF;
157
158 p_todo_item := l_todo_item;
159 RETURN FALSE;
160 END;
161 END IF;
162 CLOSE c_get_td_number;
163 END IF;
164 END LOOP;
165
166
167 FOR l_fund_td IN c_fund_td (p_ref_fund_id)
168 LOOP
169 l_item_sequence_number := NULL;
170
171 IF NVL(l_fund_td.career_item,'N') = 'Y' THEN
172 OPEN c_get_cr_td_number (l_fund_td.item_code);
173 FETCH c_get_cr_td_number INTO l_item_sequence_number;
174
175 l_todo_item := l_fund_td.item_code;
176 CLOSE c_get_cr_td_number;
177 ELSE
178 OPEN c_get_td_number (p_to_cal_type, p_to_sequence_number, l_fund_td.item_code);
179 FETCH c_get_td_number INTO l_item_sequence_number;
180
181 l_todo_item := l_fund_td.item_code;
182 CLOSE c_get_td_number;
183 END IF;
184
185 l_rowid := NULL;
186 l_ftodo_id := NULL;
187 igf_aw_fund_td_map_pkg.insert_row (
188 x_rowid => l_rowid,
189 x_ftodo_id => l_ftodo_id,
190 x_fund_id => p_new_fund_id,
191 x_item_sequence_number => l_item_sequence_number
192 );
193
194 END LOOP;
195
196 RETURN TRUE;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 p_todo_item := l_todo_item;
201 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
202 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_fund_todo.exception','Error while creating To Do Item mapping with fund');
203 END IF;
204 RETURN FALSE;
205
206 END create_fund_todo;
207
208
209 --Create pay feeclass for the target award year
210 FUNCTION create_pay_feeclass(p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
211 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
212 p_fee_class OUT NOCOPY igf_aw_fund_feeclas_all.fee_class%TYPE
213 )
214 RETURN BOOLEAN IS
215 --------------------------------------------------------------------------------
216 -- Created by : ridas, Oracle India
217 -- Date created: 16-MAY-2005
218
219 -- Change History:
220 -- Who When What
221 --
222 --------------------------------------------------------------------------------
223
224 -- Get the details of the feeclasses for a given Fund ID
225 CURSOR c_get_feeclass ( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
226 ) IS
227 SELECT fcls.fclass_id, fcls.fee_class
228 FROM igf_aw_fund_feeclas_all fcls
229 WHERE fund_id = cp_ref_fund_id;
230
231 l_rowid VARCHAR2(25);
232 l_fclass_id igf_aw_fund_feeclas_all.fclass_id%TYPE;
233
234 BEGIN
235 FOR l_get_feeclass IN c_get_feeclass( p_ref_fund_id ) LOOP
236 l_rowid := NULL;
237 l_fclass_id := NULL;
238 p_fee_class := l_get_feeclass.fee_class;
239
240
241 igf_aw_fund_feeclas_pkg.insert_row(
242 x_rowid => l_rowid,
243 x_fclass_id => l_fclass_id,
244 x_fund_id => p_new_fund_id,
245 x_fee_class => l_get_feeclass.fee_class
246 );
247 END LOOP;
248 RETURN TRUE;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
253 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_pay_feeclass.exception','Error while creating feeclass');
254 END IF;
255
256 RETURN FALSE;
257
258 END create_pay_feeclass;
259
260
261 --Create pay units for the target award year
262 FUNCTION create_pay_unit (p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
263 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
264 p_pay_unit OUT NOCOPY igf_aw_fund_unit_all.unit_cd%TYPE
265 )
266 RETURN BOOLEAN IS
267 --------------------------------------------------------------------------------
268 -- Created by : ridas, Oracle India
269 -- Date created: 16-MAY-2005
270
271 -- Change History:
272 -- Who When What
273 --
274 --------------------------------------------------------------------------------
275
276 -- Get the details of the pay units for a given Fund ID
277 CURSOR c_get_units ( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
278 ) IS
279 SELECT unt.unit_cd
280 FROM igf_aw_fund_unit_all unt
281 WHERE fund_id = cp_ref_fund_id;
282
283 -- Cursor to fetch all the existing versions for the UNIT
284 CURSOR c_get_unit_ver ( cp_unit_code igs_ps_unit_ver_all.unit_cd%TYPE
285 ) IS
286 SELECT version_number
287 FROM igs_ps_unit_ver_all
288 WHERE unit_cd = cp_unit_code
289 ORDER BY version_number asc;
290
291 l_rowid VARCHAR2(25);
292 l_funit_id igf_aw_fund_unit_all.funit_id%TYPE;
293
294 BEGIN
295 FOR l_get_units IN c_get_units( p_ref_fund_id ) LOOP
296 FOR l_get_unit_ver IN c_get_unit_ver (l_get_units.unit_cd)
297 LOOP
298 l_rowid := NULL;
299 l_funit_id := NULL;
300 p_pay_unit := l_get_units.unit_cd;
301
302 igf_aw_fund_unit_pkg.insert_row(
303 x_rowid => l_rowid,
304 x_funit_id => l_funit_id,
305 x_fund_id => p_new_fund_id,
306 x_unit_cd => l_get_units.unit_cd,
307 x_version_number => l_get_unit_ver.version_number
308 );
309 END LOOP;
310 END LOOP;
311 RETURN TRUE;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
316 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_pay_unit.exception','Error while creating pay units');
317 END IF;
318
319 RETURN FALSE;
320
321 END create_pay_unit;
322
323
324
325 --Create pay programs for the target award year
326 FUNCTION create_pay_program (p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
327 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
328 p_pay_program OUT NOCOPY igf_aw_fund_prg_all.course_cd%TYPE
329 )
330 RETURN BOOLEAN IS
331 --------------------------------------------------------------------------------
332 -- Created by : ridas, Oracle India
333 -- Date created: 16-MAY-2005
334
335 -- Change History:
336 -- Who When What
337 --
338 --------------------------------------------------------------------------------
339
340 -- Get the details of the pay programs for a given Fund ID
341 CURSOR c_get_programs ( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
342 ) IS
343 SELECT prg.course_cd
344 FROM igf_aw_fund_prg_all prg
345 WHERE fund_id = cp_ref_fund_id;
346
347 -- Cursor to fetch all the existing versions for the program type
348 CURSOR c_get_program_ver ( cp_program_code igs_ps_ver_all.course_cd%TYPE
349 ) IS
350 SELECT version_number
351 FROM igs_ps_ver_all
352 WHERE course_cd = cp_program_code
353 ORDER BY version_number asc;
354
355
356 l_rowid VARCHAR2(25);
357 l_fprg_id igf_aw_fund_prg_all.fprg_id%TYPE;
358
359 BEGIN
360 FOR l_get_programs IN c_get_programs( p_ref_fund_id ) LOOP
361 FOR l_get_program_ver IN c_get_program_ver (l_get_programs.course_cd)
362 LOOP
363 l_rowid := NULL;
364 l_fprg_id := NULL;
365 p_pay_program := l_get_programs.course_cd;
366
367 igf_aw_fund_prg_pkg.insert_row (
368 x_rowid => l_rowid,
369 x_fprg_id => l_fprg_id,
370 x_fund_id => p_new_fund_id,
371 x_course_cd => l_get_programs.course_cd,
372 x_version_number => l_get_program_ver.version_number
373 );
374 END LOOP;
375 END LOOP;
376 RETURN TRUE;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
381 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_pay_program.exception','Error while creating pay programs');
382 END IF;
383 RETURN FALSE;
384
385 END create_pay_program;
386
387
388
389 --Create exclusive fund for the target award year
390 FUNCTION create_exclusive_fund (p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
391 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
392 p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
393 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
394 p_exclusive_fund OUT NOCOPY igf_aw_fund_mast_all.fund_code%TYPE
395 )
396 RETURN BOOLEAN IS
397 --------------------------------------------------------------------------------
398 -- Created by : ridas, Oracle India
399 -- Date created: 16-MAY-2005
400
401 -- Change History:
402 -- Who When What
403 --
404 --------------------------------------------------------------------------------
405
406 -- Get the details of the existing Exclusive Funds for a given Fund ID
407 CURSOR c_fund_excl( cp_ref_fund_id igf_aw_fund_excl_all.fund_id%TYPE
408 ) IS
409 SELECT excl.fund_code
410 FROM igf_aw_fund_excl_all excl
411 WHERE excl.fund_id = cp_ref_fund_id;
412
413 -- check whether the fund is a discontinued fund or not
414 CURSOR c_chk_disc_fund( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
415 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
416 cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE
417 ) IS
418 SELECT discontinue_fund
419 FROM igf_aw_fund_mast_all fnd
420 WHERE fnd.ci_cal_type = cp_frm_cal_type
421 AND fnd.ci_sequence_number = cp_frm_sequence_number
422 AND fnd.fund_code = cp_fund_code;
423
424 l_discontinue_fund igf_aw_fund_mast_all.discontinue_fund%TYPE;
425 l_rowid VARCHAR2(25);
426
427 BEGIN
428 FOR l_fund_excl_rec IN c_fund_excl( p_ref_fund_id ) LOOP
429 l_discontinue_fund := NULL;
430 p_exclusive_fund := l_fund_excl_rec.fund_code;
431
432 OPEN c_chk_disc_fund(p_frm_cal_type, p_frm_sequence_number, l_fund_excl_rec.fund_code);
433 FETCH c_chk_disc_fund INTO l_discontinue_fund;
434 CLOSE c_chk_disc_fund;
435
436 IF l_discontinue_fund = 'Y' THEN
437 RETURN FALSE;
438 END IF;
439
440
441 l_rowid := NULL;
442 igf_aw_fund_excl_pkg.insert_row(
443 x_rowid => l_rowid,
444 x_fund_id => p_new_fund_id,
445 x_fund_code => l_fund_excl_rec.fund_code
446 );
447 END LOOP;
448 RETURN TRUE;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
453 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_exclusive_fund.exception','Error while creating exclusive fund');
454 END IF;
455 RETURN FALSE;
456
457 END create_exclusive_fund;
458
459
460 --Create inclusive fund for the target award year
461 FUNCTION create_inclusive_fund (p_ref_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
462 p_new_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
463 p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
464 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
465 p_inclusive_fund OUT NOCOPY igf_aw_fund_mast_all.fund_code%TYPE
466 )
467 RETURN BOOLEAN IS
468 --------------------------------------------------------------------------------
469 -- Created by : ridas, Oracle India
470 -- Date created: 16-MAY-2005
471
472 -- Change History:
473 -- Who When What
474 --
475 --------------------------------------------------------------------------------
476
477 -- Get the details of the existing Inclusive Funds for a given Fund ID
478 CURSOR c_fund_incl( cp_ref_fund_id igf_aw_fund_incl_all.fund_id%TYPE
479 ) IS
480 SELECT incl.fund_code
481 FROM igf_aw_fund_incl_all incl
482 WHERE incl.fund_id = cp_ref_fund_id;
483
484 -- check whether the fund is a discontinued fund or not
485 CURSOR c_chk_disc_fund( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
486 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
487 cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE
488 ) IS
489 SELECT discontinue_fund
490 FROM igf_aw_fund_mast_all fnd
491 WHERE fnd.ci_cal_type = cp_frm_cal_type
492 AND fnd.ci_sequence_number = cp_frm_sequence_number
493 AND fnd.fund_code = cp_fund_code;
494
495 l_discontinue_fund igf_aw_fund_mast_all.discontinue_fund%TYPE;
496 l_rowid VARCHAR2(25);
497
498 BEGIN
499 FOR l_fund_incl_rec IN c_fund_incl( p_ref_fund_id )
500 LOOP
501 l_discontinue_fund := NULL;
502 p_inclusive_fund := l_fund_incl_rec.fund_code;
503
504 OPEN c_chk_disc_fund(p_frm_cal_type, p_frm_sequence_number, l_fund_incl_rec.fund_code);
505 FETCH c_chk_disc_fund INTO l_discontinue_fund;
506 CLOSE c_chk_disc_fund;
507
508 IF l_discontinue_fund = 'Y' THEN
509 RETURN FALSE;
510 END IF;
511
512 l_rowid := NULL;
513 igf_aw_fund_incl_pkg.insert_row(
514 x_rowid => l_rowid,
515 x_fund_id => p_new_fund_id,
516 x_fund_code => l_fund_incl_rec.fund_code
517 );
518 END LOOP;
519 RETURN TRUE;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
524 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_inclusive_fund.exception','Error while creating inclusive fund');
525 END IF;
526 RETURN FALSE;
527
528 END create_inclusive_fund;
529
530
531 -- Function to check the existence of the fund in the target award year
532 -- IF exists return TRUE, else return FALSE
533 FUNCTION fund_exists ( p_fund_code IN igf_aw_fund_mast_all.fund_code%TYPE,
534 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
535 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
536 )
537 RETURN BOOLEAN IS
538 --------------------------------------------------------------------------------
539 -- Created by : ridas, Oracle India
540 -- Date created: 16-MAY-2005
541
542 -- Change History:
543 -- Who When What
544 --
545 --------------------------------------------------------------------------------
546
547 -- check whether the fund already present
548 CURSOR c_fund_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
549 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
550 cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE
551 ) IS
552 SELECT 'X' exist
553 FROM igf_aw_fund_mast_all fnd
554 WHERE fnd.ci_cal_type = cp_to_cal_type
555 AND fnd.ci_sequence_number = cp_to_sequence_number
556 AND fnd.fund_code = cp_fund_code;
557
558 l_fund_exists c_fund_exists%ROWTYPE;
559
560 BEGIN
561
562 OPEN c_fund_exists(p_to_cal_type, p_to_sequence_number, p_fund_code);
563 FETCH c_fund_exists INTO l_fund_exists;
564 IF c_fund_exists%NOTFOUND THEN
565 CLOSE c_fund_exists;
566 RETURN FALSE;
567 END IF;
568
569 CLOSE c_fund_exists;
570
571 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
572 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.fund_exists.debug','Fund already exists :'||p_fund_code);
573 END IF;
574
575 RETURN TRUE;
576
577 END fund_exists;
578
579
580 -- Procedure to create a new fund for the target award year
581 FUNCTION create_new_fund ( p_fund_rec IN igf_aw_fund_mast_all%ROWTYPE,
582 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
583 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
584 )
585 RETURN igf_aw_fund_mast_all.fund_id%TYPE IS
586 --------------------------------------------------------------------------------
587 -- Created by : ridas, Oracle India
588 -- Date created: 16-MAY-2005
589
590 -- Change History:
591 -- Who When What
592 -- museshad 14-Jul-2005 Build FA 140: Modified TBH call, since new columns
593 -- have got added to igf_aw_fund_mast_all.
594 --------------------------------------------------------------------------------
595
596 l_rowid VARCHAR2(25):= NULL;
597 l_fund_id igf_aw_fund_mast_all.fund_id%TYPE := NULL;
598
599 BEGIN
600 igf_aw_fund_mast_pkg.insert_row(
601 x_rowid => l_rowid,
602 x_fund_id => l_fund_id,
603 x_fund_code => p_fund_rec.fund_code,
604 x_ci_cal_type => p_to_cal_type,
605 x_ci_sequence_number => p_to_sequence_number,
606 x_description => p_fund_rec.description,
607 x_discontinue_fund => p_fund_rec.discontinue_fund,
608 x_entitlement => p_fund_rec.entitlement,
609 x_auto_pkg => p_fund_rec.auto_pkg,
610 x_self_help => p_fund_rec.self_help,
611 x_allow_man_pkg => p_fund_rec.allow_man_pkg,
612 x_update_need => p_fund_rec.update_need,
613 x_disburse_fund => p_fund_rec.disburse_fund,
614 x_available_amt => p_fund_rec.available_amt,
615 x_offered_amt => 0,
616 x_pending_amt => 0,
617 x_accepted_amt => 0,
618 x_declined_amt => 0,
619 x_cancelled_amt => 0,
620 x_remaining_amt => p_fund_rec.available_amt,
621 x_enrollment_status => p_fund_rec.enrollment_status,
622 x_prn_award_letter => p_fund_rec.prn_award_letter,
623 x_over_award_amt => p_fund_rec.over_award_amt,
624 x_over_award_perct => p_fund_rec.over_award_perct,
625 x_min_award_amt => p_fund_rec.min_award_amt,
626 x_max_award_amt => p_fund_rec.max_award_amt,
627 x_max_yearly_amt => p_fund_rec.max_yearly_amt,
628 x_max_life_amt => p_fund_rec.max_life_amt,
629 x_max_life_term => p_fund_rec.max_life_term,
630 x_fm_fc_methd => p_fund_rec.fm_fc_methd,
631 x_roundoff_fact => p_fund_rec.roundoff_fact,
632 x_replace_fc => p_fund_rec.replace_fc,
633 x_allow_overaward => p_fund_rec.allow_overaward,
634 x_pckg_awd_stat => p_fund_rec.pckg_awd_stat,
635 x_org_record_req => p_fund_rec.org_record_req,
636 x_disb_record_req => p_fund_rec.disb_record_req,
637 x_prom_note_req => p_fund_rec.prom_note_req,
638 x_min_num_disb => p_fund_rec.min_num_disb,
639 x_max_num_disb => p_fund_rec.max_num_disb,
640 x_fee_type => p_fund_rec.fee_type,
641 x_total_offered => 0,
642 x_total_accepted => 0,
643 x_total_declined => 0,
644 x_total_revoked => 0,
645 x_total_cancelled => 0,
646 x_total_disbursed => 0,
647 x_total_committed => 0,
648 x_committed_amt => 0,
649 x_disbursed_amt => 0,
650 x_awd_notice_txt => p_fund_rec.awd_notice_txt,
651 x_attribute_category => p_fund_rec.attribute_category,
652 x_attribute1 => p_fund_rec.attribute1,
653 x_attribute2 => p_fund_rec.attribute2,
654 x_attribute3 => p_fund_rec.attribute3,
655 x_attribute4 => p_fund_rec.attribute4,
656 x_attribute5 => p_fund_rec.attribute5,
657 x_attribute6 => p_fund_rec.attribute6,
658 x_attribute7 => p_fund_rec.attribute7,
659 x_attribute8 => p_fund_rec.attribute8,
660 x_attribute9 => p_fund_rec.attribute9,
661 x_attribute10 => p_fund_rec.attribute10,
662 x_attribute11 => p_fund_rec.attribute11,
663 x_attribute12 => p_fund_rec.attribute12,
664 x_attribute13 => p_fund_rec.attribute13,
665 x_attribute14 => p_fund_rec.attribute14,
666 x_attribute15 => p_fund_rec.attribute15,
667 x_attribute16 => p_fund_rec.attribute16,
668 x_attribute17 => p_fund_rec.attribute17,
669 x_attribute18 => p_fund_rec.attribute18,
670 x_attribute19 => p_fund_rec.attribute19,
671 x_attribute20 => p_fund_rec.attribute20,
672 x_disb_verf_da => p_fund_rec.disb_verf_da,
673 x_fund_exp_da => p_fund_rec.fund_exp_da,
674 x_nslds_disb_da => p_fund_rec.nslds_disb_da ,
675 x_disb_exp_da => p_fund_rec.disb_exp_da,
676 x_fund_recv_reqd => p_fund_rec.fund_recv_reqd,
677 x_show_on_bill => p_fund_rec.show_on_bill,
678 x_bill_desc => p_fund_rec.bill_desc,
679 x_credit_type_id => p_fund_rec.credit_type_id,
680 x_spnsr_ref_num => NULL,
681 x_party_id => p_fund_rec.party_id,
682 x_spnsr_fee_type => NULL,
683 x_min_credit_points => p_fund_rec.min_credit_points,
684 x_group_id => p_fund_rec.group_id ,
685 x_spnsr_attribute_category => NULL,
686 x_spnsr_attribute1 => NULL,
687 x_spnsr_attribute2 => NULL,
688 x_spnsr_attribute3 => NULL,
689 x_spnsr_attribute4 => NULL,
690 x_spnsr_attribute5 => NULL,
691 x_spnsr_attribute6 => NULL,
692 x_spnsr_attribute7 => NULL,
693 x_spnsr_attribute8 => NULL,
694 x_spnsr_attribute9 => NULL,
695 x_spnsr_attribute10 => NULL,
696 x_spnsr_attribute11 => NULL,
697 x_spnsr_attribute12 => NULL,
698 x_spnsr_attribute13 => NULL,
699 x_spnsr_attribute14 => NULL,
700 x_spnsr_attribute15 => NULL,
701 x_spnsr_attribute16 => NULL,
702 x_spnsr_attribute17 => NULL,
703 x_spnsr_attribute18 => NULL,
704 x_spnsr_attribute19 => NULL,
705 x_spnsr_attribute20 => NULL,
706 x_threshold_perct => p_fund_rec.threshold_perct,
707 x_threshold_value => p_fund_rec.threshold_value,
708 x_gift_aid => p_fund_rec.gift_aid,
709 x_send_without_doc => p_fund_rec.send_without_doc,
710 x_ver_app_stat_override => p_fund_rec.ver_app_stat_override,
711 x_re_pkg_verif_flag => p_fund_rec.re_pkg_verif_flag,
712 x_donot_repkg_if_code => p_fund_rec.donot_repkg_if_code,
713 x_lock_award_flag => p_fund_rec.lock_award_flag,
714 x_mode => 'R',
715 x_view_only_flag => p_fund_rec.view_only_flag,
716 x_accept_less_amt_flag => p_fund_rec.accept_less_amt_flag,
717 x_allow_inc_post_accept_flag => p_fund_rec.allow_inc_post_accept_flag,
718 x_min_increase_amt => p_fund_rec.min_increase_amt,
719 x_allow_dec_post_accept_flag => p_fund_rec.allow_dec_post_accept_flag,
720 x_min_decrease_amt => p_fund_rec.min_decrease_amt,
721 x_allow_decln_post_accept_flag => p_fund_rec.allow_decln_post_accept_flag,
722 x_status_after_decline => p_fund_rec.status_after_decline,
723 x_fund_information_txt => p_fund_rec.fund_information_txt,
724 x_disb_rounding_code => p_fund_rec.disb_rounding_code
725 );
726
727 RETURN l_fund_id;
728
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
732 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_new_fund.exception','Error while creating new fund :'||p_fund_rec.fund_code);
733 END IF;
734 RETURN NULL;
735
736 END create_new_fund;
737
738
739
740 -- Procedure to rollover fund attributes
741 PROCEDURE rollover_fund_attributes ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
742 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
743 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
744 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
745 )
746 IS
747 --------------------------------------------------------------------------------
748 -- Created by : ridas, Oracle India
749 -- Date created: 16-MAY-2005
750
751 -- Change History:
752 -- Who When What
753 --
754 --------------------------------------------------------------------------------
755
756 -- Get the funds for the source award year
757 CURSOR c_fund( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
758 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
759 ) IS
760 SELECT fnd.*
761 FROM igf_aw_fund_mast_all fnd,igf_aw_fund_cat_all fcat
762 WHERE fnd.ci_cal_type = cp_frm_cal_type
763 AND fnd.ci_sequence_number = cp_frm_sequence_number
764 AND fnd.fund_code = fcat.fund_code
765 AND fcat.sys_fund_type <> 'SPONSOR'
766 ORDER BY fnd.fund_id;
767
768 l_new_fund_id igf_aw_fund_mast_all.fund_id%TYPE := NULL;
769 E_SKIP_FUND EXCEPTION;
770 l_pay_program igf_aw_fund_prg_all.course_cd%TYPE;
771 l_pay_unit igf_aw_fund_unit_all.unit_cd%TYPE;
772 l_fee_class igf_aw_fund_feeclas_all.fee_class%TYPE;
773 l_todo_item igf_ap_td_item_mst_all.item_code%TYPE;
774 l_inclusive_fund igf_aw_fund_mast_all.fund_code%TYPE;
775 l_exclusive_fund igf_aw_fund_mast_all.fund_code%TYPE;
776 l_discontinued_flg VARCHAR2(1) := 'N';
777
778 BEGIN
779
780 fnd_file.new_line(fnd_file.log,1);
781 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FUND_ATTRIBUTE')||':' );
782
783 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
784 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.debug','Processing Fund Attributes');
785 END IF;
786
787 FOR l_fund IN c_fund(p_frm_cal_type, p_frm_sequence_number)
788 LOOP
789 BEGIN
790 fnd_message.set_name('IGF','IGF_AW_PROC_FUND');
791 fnd_message.set_token('FUND',l_fund.fund_code);
792 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
793
794 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
795 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.debug','Fund :'||l_fund.fund_code);
796 END IF;
797
798 --Check whether the fund already got rolled over
799 IF fund_exists(l_fund.fund_code, p_to_cal_type, p_to_sequence_number) THEN
800 fnd_message.set_name('IGF','IGF_AW_FND_ALRDY_PRSNT');
801 fnd_message.set_token('FUND',l_fund.fund_code);
802 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
803
804 RAISE E_SKIP_FUND;
805 END IF;
806
807 --Check for discontinued fund
808 IF l_fund.discontinue_fund = 'Y' THEN
809 fnd_message.set_name('IGF','IGF_AW_FND_RLOVR_DISCONT');
810 fnd_message.set_token('FUND',l_fund.fund_code);
811 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
812
813 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
814 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.debug','Discontinued Fund');
815 END IF;
816
817 l_discontinued_flg := 'Y';
818 RAISE E_SKIP_FUND;
819 END IF;
820
821 SAVEPOINT rollover_fund_attributes;
822
823 --Create new fund for the target award year
824 l_new_fund_id := create_new_fund( p_fund_rec => l_fund,
825 p_to_cal_type => p_to_cal_type,
826 p_to_sequence_number => p_to_sequence_number
827 );
828
829 IF l_new_fund_id IS NULL THEN
830 fnd_message.set_name('IGF','IGF_AW_INSERT_FUND_ERR');
831 fnd_message.set_token('FUND',l_fund.fund_code);
832 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
833
834 ROLLBACK TO rollover_fund_attributes;
835 RAISE E_SKIP_FUND;
836
837 ELSE
838 l_inclusive_fund := NULL;
839 --Skip the fund if creation of inclusive fund is not successful
840 IF NOT create_inclusive_fund(l_fund.fund_id, l_new_fund_id, p_frm_cal_type, p_frm_sequence_number, l_inclusive_fund) THEN
841 fnd_message.set_name('IGF','IGF_AW_FND_RLOVR_INCL_FLD');
842 fnd_message.set_token('FUND',l_inclusive_fund);
843 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
844
845 ROLLBACK TO rollover_fund_attributes;
846 RAISE E_SKIP_FUND;
847 END IF;
848
849 l_exclusive_fund := NULL;
850 --Skip the fund if creation of exclusive fund is not successful
851 IF NOT create_exclusive_fund(l_fund.fund_id, l_new_fund_id, p_frm_cal_type, p_frm_sequence_number, l_exclusive_fund) THEN
852 fnd_message.set_name('IGF','IGF_AW_FND_RLOVR_EXCL_FLD');
853 fnd_message.set_token('FUND',l_exclusive_fund);
854 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
855
856 ROLLBACK TO rollover_fund_attributes;
857 RAISE E_SKIP_FUND;
858 END IF;
859
860 l_pay_program := NULL;
861 --Skip the fund if creation of pay program is not successful
862 IF NOT create_pay_program(l_fund.fund_id, l_new_fund_id, l_pay_program) THEN
863 fnd_message.set_name('IGF','IGF_AW_FND_PRG_ROLL');
864 fnd_message.set_token('PROGRAM',l_pay_program);
865 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
866
867 ROLLBACK TO rollover_fund_attributes;
868 RAISE E_SKIP_FUND;
869 END IF;
870
871 l_pay_unit := NULL;
872 --Skip the fund if creation of pay unit is not successful
873 IF NOT create_pay_unit(l_fund.fund_id, l_new_fund_id, l_pay_unit) THEN
874 fnd_message.set_name('IGF','IGF_AW_FND_UNIT_ROLL');
875 fnd_message.set_token('UNIT',l_pay_unit);
876 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
877
878 ROLLBACK TO rollover_fund_attributes;
879 RAISE E_SKIP_FUND;
880 END IF;
881
882 l_fee_class := NULL;
883 --Skip the fund if creation of pay feeclass is not successful
884 IF NOT create_pay_feeclass(l_fund.fund_id, l_new_fund_id, l_fee_class) THEN
885 fnd_message.set_name('IGF','IGF_AW_FND_FEECLS_ROLL');
886 fnd_message.set_token('FEE_CLASS',l_fee_class);
887 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
888
889 ROLLBACK TO rollover_fund_attributes;
890 RAISE E_SKIP_FUND;
891 END IF;
892
893 l_todo_item := NULL;
894 --Skip the fund if creation of fund todo is not successful
895 IF NOT create_fund_todo(l_fund.fund_id,l_new_fund_id,p_frm_cal_type,p_frm_sequence_number,p_to_cal_type,p_to_sequence_number,l_todo_item) THEN
896 fnd_message.set_name('IGF','IGF_AW_FND_TODO_ROLL');
897 fnd_message.set_token('TODO_ITEM',l_todo_item);
898 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
899
900 ROLLBACK TO rollover_fund_attributes;
901 RAISE E_SKIP_FUND;
902 END IF;
903
904 END IF;
905
906 COMMIT;
907 fnd_message.set_name('IGF','IGF_AW_FND_RLOVR_FND_SUCCFL');
908 fnd_message.set_token('FUND',l_fund.fund_code);
909 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
910
911 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
912 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.debug','Successfully rolled over fund :'||l_fund.fund_code);
913 END IF;
914 EXCEPTION
915 WHEN E_SKIP_FUND THEN
916 IF l_discontinued_flg = 'N' THEN
917 fnd_message.set_name('IGF','IGF_AW_SKIPPING_FUND');
918 fnd_message.set_token('FUND_CODE',l_fund.fund_code);
919 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
920 END IF;
921
922 l_discontinued_flg := 'N';
923
924 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
925 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.exception','Skipping the fund :'||l_fund.fund_code);
926 END IF;
927
928 WHEN OTHERS THEN
929 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
930 fnd_message.set_token('NAME','igf_aw_rollover.rollover_fund_attributes :' || SQLERRM);
931 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
932 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_fund_attributes.exception','sql error:'||SQLERRM);
933 END IF;
934
935 app_exception.raise_exception;
936 END;
937 END LOOP;
938
939 END rollover_fund_attributes;
940
941
942 -- Function to check mapping (Award Year/ Term/ Teaching Period)
943 -- IF exists return TRUE, else return FALSE
944 FUNCTION chk_calendar_mapping ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
945 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
946 p_to_cal_type OUT NOCOPY igs_ca_inst_all.cal_type%TYPE,
947 p_to_sequence_number OUT NOCOPY igs_ca_inst_all.sequence_number%TYPE
948 )
949 RETURN BOOLEAN IS
950
951 CURSOR c_map_details( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
952 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
953 ) IS
954 SELECT cr_cal_type,
955 sc_sequence_number
956 FROM igf_aw_cal_rel_all
957 WHERE cr_cal_type = cp_frm_cal_type
958 AND cr_sequence_number = cp_frm_sequence_number
959 AND NVL(active,'N') = 'Y';
960
961
962 BEGIN
963 OPEN c_map_details(p_frm_cal_type, p_frm_sequence_number);
964 FETCH c_map_details INTO p_to_cal_type, p_to_sequence_number;
965 IF c_map_details%NOTFOUND THEN
966 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
967 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.chk_calendar_mapping.debug','Calendar Mapping does not exist');
968 END IF;
969
970 CLOSE c_map_details;
971 RETURN FALSE;
972 END IF;
973
974 CLOSE c_map_details;
975
976 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
977 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.chk_calendar_mapping.debug','Calendar Mapping exists');
978 END IF;
979
980 RETURN TRUE;
981
982 END chk_calendar_mapping;
983
984
985 -- Function to check the existence of the rate based setup in the target award year
986 -- IF exists return TRUE, else return FALSE
987 FUNCTION rate_setup_exists ( p_item_code IN igf_aw_coa_rate_det.item_code%TYPE,
988 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
989 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
990 )
991 RETURN BOOLEAN IS
992 --------------------------------------------------------------------------------
993 -- Created by : ridas, Oracle India
994 -- Date created: 17-MAY-2005
995
996 -- Change History:
997 -- Who When What
998 --
999 --------------------------------------------------------------------------------
1000
1001 -- check whether the item code is present or not
1002 CURSOR c_item_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
1003 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1004 cp_item_code igf_aw_coa_rate_det.item_code%TYPE
1005 ) IS
1006 SELECT 'X' exist
1007 FROM igf_aw_coa_rate_det item
1008 WHERE item.ci_cal_type = cp_to_cal_type
1009 AND item.ci_sequence_number = cp_to_sequence_number
1010 AND item.item_code = cp_item_code
1011 AND rownum = 1;
1012
1013 l_item_exists c_item_exists%ROWTYPE;
1014
1015 BEGIN
1016
1017 OPEN c_item_exists(p_to_cal_type, p_to_sequence_number, p_item_code);
1018 FETCH c_item_exists INTO l_item_exists;
1019 IF c_item_exists%NOTFOUND THEN
1020 CLOSE c_item_exists;
1021 RETURN FALSE;
1022 END IF;
1023
1024 CLOSE c_item_exists;
1025
1026 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1027 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rate_setup_exists.debug','Item already exists :'||p_item_code);
1028 END IF;
1029
1030 RETURN TRUE;
1031
1032 END rate_setup_exists;
1033
1034
1035 -- Procedure to rollover Cost of Attendance Rate Table Setup
1036 PROCEDURE rollover_rate_setups ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1037 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1038 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1039 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1040 )
1041 IS
1042 --------------------------------------------------------------------------------
1043 -- Created by : ridas, Oracle India
1044 -- Date created: 17-MAY-2005
1045
1046 -- Change History:
1047 -- Who When What
1048 --
1049 --------------------------------------------------------------------------------
1050
1051 -- Get distinct item code from the source award year
1052 CURSOR c_get_itm_code( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1053 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
1054 ) IS
1055 SELECT item_code
1056 FROM igf_aw_coa_rate_det
1057 WHERE ci_cal_type = cp_frm_cal_type
1058 AND ci_sequence_number = cp_frm_sequence_number
1059 GROUP BY item_code
1060 ORDER BY item_code;
1061
1062
1063 -- Get the rate table setup details for the source award year
1064 CURSOR c_rate_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1065 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1066 cp_item_code igf_aw_coa_rate_det.item_code%TYPE
1067 ) IS
1068 SELECT rate.*
1069 FROM igf_aw_coa_rate_det rate
1070 WHERE rate.ci_cal_type = cp_frm_cal_type
1071 AND rate.ci_sequence_number = cp_frm_sequence_number
1072 AND rate.item_code = cp_item_code;
1073
1074 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
1075 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1076 l_rowid VARCHAR2(25);
1077 E_SKIP_ITEM EXCEPTION;
1078 l_error_occurred VARCHAR2(1) := 'N';
1079
1080 BEGIN
1081
1082 fnd_file.new_line(fnd_file.log,1);
1083 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','COA_RATE_TABLE')||':' );
1084
1085 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1086 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_rate_setups.debug','Processing Rate Based Setups');
1087 END IF;
1088
1089 FOR l_get_itm_code IN c_get_itm_code(p_frm_cal_type, p_frm_sequence_number)
1090 LOOP
1091 BEGIN
1092 SAVEPOINT rollover_rate_setups;
1093
1094 fnd_message.set_name('IGF','IGF_AW_PROC_COA_ITEM');
1095 fnd_message.set_token('ITEM',l_get_itm_code.item_code);
1096 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
1097
1098 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1099 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_rate_setups.debug','COA Item :'||l_get_itm_code.item_code);
1100 END IF;
1101
1102 --Check whether the item already got rolled over
1103 IF rate_setup_exists(l_get_itm_code.item_code, p_to_cal_type, p_to_sequence_number) THEN
1104 fnd_message.set_name('IGF','IGF_AW_ITM_ALRDY_EXISTS');
1105 fnd_message.set_token('ITEM',l_get_itm_code.item_code);
1106 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1107
1108 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1109 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_rate_setups.debug','COA Item already ecists :'||l_get_itm_code.item_code);
1110 END IF;
1111 RAISE E_SKIP_ITEM;
1112 END IF;
1113
1114 l_error_occurred := 'N';
1115 FOR l_rate_setup IN c_rate_setup(p_frm_cal_type, p_frm_sequence_number, l_get_itm_code.item_code)
1116 LOOP
1117 l_to_ld_cal_type := NULL;
1118 l_to_ld_sequence_number := NULL;
1119
1120 IF l_rate_setup.ld_cal_type IS NOT NULL AND l_rate_setup.ld_sequence_number IS NOT NULL THEN
1121 IF NOT chk_calendar_mapping(l_rate_setup.ld_cal_type,l_rate_setup.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
1122 fnd_message.set_name('IGF','IGF_AW_TRM_NT_EXISTS');
1123 fnd_message.set_token('ITEM',l_rate_setup.item_code);
1124 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(l_rate_setup.ld_cal_type,l_rate_setup.ld_sequence_number));
1125
1126 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1127 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1128 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_rate_setups.debug','Calendar Mapping does not exist');
1129 END IF;
1130
1131 l_error_occurred := 'Y';
1132 END IF;
1133 END IF;
1134
1135 IF l_error_occurred = 'N' THEN
1136 l_rowid := NULL;
1137 igf_aw_coa_rate_det_pkg.insert_row (
1138 x_mode => 'R',
1139 x_rowid => l_rowid,
1140 x_ci_cal_type => p_to_cal_type,
1141 x_ci_sequence_number => p_to_sequence_number,
1142 x_item_code => l_rate_setup.item_code,
1143 x_rate_order_num => l_rate_setup.rate_order_num,
1144 x_pid_group_cd => l_rate_setup.pid_group_cd,
1145 x_org_unit_cd => l_rate_setup.org_unit_cd,
1146 x_program_type => l_rate_setup.program_type,
1147 x_program_location_cd => l_rate_setup.program_location_cd,
1148 x_program_cd => l_rate_setup.program_cd,
1149 x_class_standing => l_rate_setup.class_standing,
1150 x_residency_status_code => l_rate_setup.residency_status_code,
1151 x_housing_status_code => l_rate_setup.housing_status_code,
1152 x_attendance_type => l_rate_setup.attendance_type,
1153 x_attendance_mode => l_rate_setup.attendance_mode,
1154 x_ld_cal_type => l_to_ld_cal_type,
1155 x_ld_sequence_number => l_to_ld_sequence_number,
1156 x_mult_factor_code => l_rate_setup.mult_factor_code,
1157 x_mult_amount_num => l_rate_setup.mult_amount_num
1158 );
1159 END IF;
1160
1161 END LOOP;
1162
1163 IF l_error_occurred = 'N' THEN
1164 COMMIT;
1165
1166 fnd_message.set_name('IGF','IGF_AW_RT_RLOVR_SUCCFL');
1167 fnd_message.set_token('ITEM',l_get_itm_code.item_code);
1168 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1169
1170 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1171 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_rate_setups.debug','Successfully rolled over coa item :'||l_get_itm_code.item_code);
1172 END IF;
1173 ELSE
1174 RAISE E_SKIP_ITEM;
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN E_SKIP_ITEM THEN
1179 ROLLBACK TO rollover_rate_setups;
1180 fnd_message.set_name('IGF','IGF_AW_SKIPPING_ITEM');
1181 fnd_message.set_token('ITEM',l_get_itm_code.item_code);
1182 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1183
1184 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1185 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_rate_setups.exception','Skipping the item :'||l_get_itm_code.item_code);
1186 END IF;
1187
1188 WHEN OTHERS THEN
1189 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1190 fnd_message.set_token('NAME','igf_aw_rollover.rollover_rate_setups :' || SQLERRM);
1191 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1192 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_rate_setups.exception','sql error:'||SQLERRM);
1193 END IF;
1194
1195 app_exception.raise_exception;
1196 END;
1197 END LOOP;
1198 END rollover_rate_setups;
1199
1200
1201
1202 -- Procedure to rollover Institutional Application Setup
1203 PROCEDURE rollover_inst_applications ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1204 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1205 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1206 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1207 )
1208 IS
1209 --------------------------------------------------------------------------------
1210 -- Created by : ridas, Oracle India
1211 -- Date created: 19-MAY-2005
1212
1213 -- Change History:
1214 -- Who When What
1215 --
1216 --------------------------------------------------------------------------------
1217
1218 -- Get distinct Application Code from the source award year
1219 CURSOR c_get_appln_code( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1220 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
1221 ) IS
1222 SELECT appln.application_code
1223 FROM igf_ap_appl_setup_all appln
1224 WHERE appln.ci_cal_type = cp_frm_cal_type
1225 AND appln.ci_sequence_number = cp_frm_sequence_number
1226 AND NVL(appln.active_flag,'N')= 'Y'
1227 GROUP BY appln.application_code
1228 ORDER BY appln.application_code;
1229
1230
1231 -- Get the institutional application setup details for the source award year
1232 CURSOR c_inst_appln_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1233 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1234 cp_application_code igf_ap_appl_setup_all.application_code%TYPE
1235 ) IS
1236 SELECT appln.*
1237 FROM igf_ap_appl_setup_all appln
1238 WHERE appln.ci_cal_type = cp_frm_cal_type
1239 AND appln.ci_sequence_number = cp_frm_sequence_number
1240 AND appln.application_code = cp_application_code
1241 AND NVL(appln.active_flag,'N')= 'Y'
1242 ORDER BY appln.question_id;
1243
1244
1245 -- Check whether the application exists or not
1246 CURSOR c_appln_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
1247 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1248 cp_application_code igf_ap_appl_setup_all.application_code%TYPE
1249 ) IS
1250 SELECT 'X' exist
1251 FROM igf_ap_appl_setup_all appln
1252 WHERE appln.ci_cal_type = cp_to_cal_type
1253 AND appln.ci_sequence_number = cp_to_sequence_number
1254 AND appln.application_code = cp_application_code
1255 AND NVL(appln.active_flag,'N')= 'Y';
1256
1257 l_appln_exists c_appln_exists%ROWTYPE;
1258
1259 l_rowid VARCHAR2(25);
1260 E_SKIP_APPLICATION EXCEPTION;
1261 l_error_occurred VARCHAR2(1) := 'N';
1262 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
1263 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1264
1265 BEGIN
1266 fnd_file.new_line(fnd_file.log,1);
1267 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','INST_APPLICATION')||':' );
1268
1269 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1270 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_applications.debug','Processing Institutional Applications');
1271 END IF;
1272
1273 FOR l_get_appln_code IN c_get_appln_code(p_frm_cal_type, p_frm_sequence_number)
1274 LOOP
1275 BEGIN
1276 SAVEPOINT rollover_inst_applications;
1277
1278 fnd_message.set_name('IGF','IGF_AP_PROC_INST_APLN');
1279 fnd_message.set_token('APPLICATION',l_get_appln_code.application_code);
1280 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
1281
1282 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1283 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_applications.debug','Application :'||l_get_appln_code.application_code);
1284 END IF;
1285
1286 --Check whether the application already got rolled over
1287 OPEN c_appln_exists(p_to_cal_type, p_to_sequence_number, l_get_appln_code.application_code);
1288 FETCH c_appln_exists INTO l_appln_exists;
1289 IF c_appln_exists%FOUND THEN
1290 CLOSE c_appln_exists;
1291 fnd_message.set_name('IGF','IGF_AP_INST_APLN_ALRDY_EXT');
1292 fnd_message.set_token('APPLICATION',l_get_appln_code.application_code);
1293 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1294
1295 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1296 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_applications.debug','Application already exists');
1297 END IF;
1298 RAISE E_SKIP_APPLICATION;
1299 END IF;
1300 CLOSE c_appln_exists;
1301
1302 l_error_occurred := 'N';
1303
1304 FOR l_inst_appln_setup IN c_inst_appln_setup(p_frm_cal_type, p_frm_sequence_number, l_get_appln_code.application_code)
1305 LOOP
1306 BEGIN
1307 l_to_ld_cal_type := NULL;
1308 l_to_ld_sequence_number := NULL;
1309
1310 IF l_inst_appln_setup.ld_cal_type IS NOT NULL AND l_inst_appln_setup.ld_sequence_number IS NOT NULL THEN
1311 IF NOT chk_calendar_mapping(l_inst_appln_setup.ld_cal_type,l_inst_appln_setup.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
1312 fnd_message.set_name('IGF','IGF_AP_QUES_TRM_NT_EXISTS');
1313 fnd_message.set_token('QUESTION',l_inst_appln_setup.question);
1314 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(l_inst_appln_setup.ld_cal_type,l_inst_appln_setup.ld_sequence_number));
1315
1316 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1317 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1318 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_applications.debug','Calendar Mapping does not exist');
1319 END IF;
1320
1321 l_error_occurred := 'Y';
1322 END IF;
1323 END IF;
1324
1325 IF l_error_occurred = 'N' THEN
1326 l_rowid := NULL;
1327 l_inst_appln_setup.question_id := NULL;
1328
1329 --insert into the application setup table
1330 igf_ap_appl_setup_pkg.insert_row (
1331 x_mode => 'R',
1332 x_rowid => l_rowid,
1333 x_enabled => l_inst_appln_setup.enabled,
1334 x_org_id => l_inst_appln_setup.org_id,
1335 x_ci_cal_type => p_to_cal_type,
1336 x_ci_sequence_number => p_to_sequence_number,
1337 x_question_id => l_inst_appln_setup.question_id,
1338 x_question => l_inst_appln_setup.question,
1339 x_application_code => l_inst_appln_setup.application_code,
1340 x_application_name => l_inst_appln_setup.application_name,
1341 x_active_flag => l_inst_appln_setup.active_flag,
1342 x_answer_type_code => l_inst_appln_setup.answer_type_code,
1343 x_destination_txt => l_inst_appln_setup.destination_txt,
1344 x_ld_cal_type => l_to_ld_cal_type,
1345 x_ld_sequence_number => l_to_ld_sequence_number,
1346 x_all_terms_flag => l_inst_appln_setup.all_terms_flag,
1347 x_override_exist_ant_data_flag => l_inst_appln_setup.override_exist_ant_data_flag,
1348 x_required_flag => l_inst_appln_setup.required_flag,
1349 x_minimum_value_num => l_inst_appln_setup.minimum_value_num,
1350 x_maximum_value_num => l_inst_appln_setup.maximum_value_num,
1351 x_minimum_date => l_inst_appln_setup.minimum_date,
1352 x_maximium_date => l_inst_appln_setup.maximium_date,
1353 x_lookup_code => l_inst_appln_setup.lookup_code,
1354 x_hint_txt => l_inst_appln_setup.hint_txt
1355 );
1356 END IF;
1357
1358
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 l_error_occurred := 'Y';
1362
1363 fnd_message.set_name('IGF','IGF_AP_APLN_QUES_ROLL');
1364 fnd_message.set_token('QUESTION',l_inst_appln_setup.question);
1365 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1366
1367 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1368 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_applications.exception','Skipping Question ID :'||l_inst_appln_setup.question_id);
1369 END IF;
1370 END;
1371 END LOOP;
1372
1373 IF l_error_occurred = 'N' THEN
1374 COMMIT;
1375
1376 fnd_message.set_name('IGF','IGF_AP_APLN_RLOVR_SUCCFL');
1377 fnd_message.set_token('APPLICATION',l_get_appln_code.application_code);
1378 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1379
1380 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1381 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_applications.debug','Successfully rolled over Application :'||l_get_appln_code.application_code);
1382 END IF;
1383 ELSE
1384 RAISE E_SKIP_APPLICATION;
1385 END IF;
1386
1387 EXCEPTION
1388 WHEN E_SKIP_APPLICATION THEN
1389 ROLLBACK TO rollover_inst_applications;
1390
1391 fnd_message.set_name('IGF','IGF_AP_SKIPPING_INST_APLN');
1392 fnd_message.set_token('APPLICATION',l_get_appln_code.application_code);
1393 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1394
1395 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1396 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_applications.exception','Skipping the application :'||l_get_appln_code.application_code);
1397 END IF;
1398
1399 WHEN OTHERS THEN
1400 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1401 fnd_message.set_token('NAME','igf_aw_rollover.rollover_inst_applications :' || SQLERRM);
1402 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1403 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_applications.exception','sql error:'||SQLERRM);
1404 END IF;
1405
1406 app_exception.raise_exception;
1407 END;
1408 END LOOP;
1409 END rollover_inst_applications;
1410
1411
1412
1413 -- Procedure to create a new award distribution plan for the target award year
1414 FUNCTION create_new_plan ( p_plan_rec IN igf_aw_awd_dist_plans%ROWTYPE,
1415 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1416 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1417 )
1418 RETURN igf_aw_awd_dist_plans.adplans_id%TYPE IS
1419 --------------------------------------------------------------------------------
1420 -- Created by : ridas, Oracle India
1421 -- Date created: 20-MAY-2005
1422
1423 -- Change History:
1424 -- Who When What
1425 --
1426 --------------------------------------------------------------------------------
1427
1428 l_rowid VARCHAR2(25):= NULL;
1429 l_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE := NULL;
1430
1431 BEGIN
1432
1433 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1434 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_new_plan.debug','Insert an Award Distribution Plan');
1435 END IF;
1436
1437 igf_aw_awd_dist_plans_pkg.insert_row (
1438 x_mode => 'R',
1439 x_rowid => l_rowid,
1440 x_adplans_id => l_adplans_id,
1441 x_awd_dist_plan_cd => p_plan_rec.awd_dist_plan_cd,
1442 x_cal_type => p_to_cal_type,
1443 x_sequence_number => p_to_sequence_number,
1444 x_awd_dist_plan_cd_desc => p_plan_rec.awd_dist_plan_cd_desc,
1445 x_active_flag => p_plan_rec.active_flag,
1446 x_dist_plan_method_code => p_plan_rec.dist_plan_method_code
1447 );
1448
1449 RETURN l_adplans_id;
1450
1451 END create_new_plan;
1452
1453
1454 -- Procedure to create a new distribution plan term for the target award year
1455 FUNCTION create_new_plan_term ( p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
1456 p_plan_term_rec IN igf_aw_dp_terms%ROWTYPE,
1457 p_to_ld_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1458 p_to_ld_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1459 )
1460 RETURN igf_aw_dp_terms.adterms_id%TYPE IS
1461 --------------------------------------------------------------------------------
1462 -- Created by : ridas, Oracle India
1463 -- Date created: 20-MAY-2005
1464
1465 -- Change History:
1466 -- Who When What
1467 --
1468 --------------------------------------------------------------------------------
1469
1470 l_rowid VARCHAR2(25):= NULL;
1471 l_adterms_id igf_aw_dp_terms.adterms_id%TYPE := NULL;
1472
1473 BEGIN
1474
1475 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1476 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_new_plan_term.debug','Insert term attached to the plan');
1477 END IF;
1478
1479 igf_aw_dp_terms_pkg.insert_row (
1480 x_mode => 'R',
1481 x_rowid => l_rowid,
1482 x_adterms_id => l_adterms_id,
1483 x_adplans_id => p_adplans_id,
1484 x_ld_cal_type => p_to_ld_cal_type,
1485 x_ld_sequence_number => p_to_ld_sequence_number,
1486 x_ld_perct_num => p_plan_term_rec.ld_perct_num
1487 );
1488
1489 RETURN l_adterms_id;
1490
1491 END create_new_plan_term;
1492
1493
1494 -- Procedure to create a new distribution plan teaching period for the target award year
1495 FUNCTION create_new_plan_tp ( p_adterms_id IN igf_aw_dp_terms.adterms_id%TYPE,
1496 p_plan_tp_rec IN igf_aw_dp_teach_prds%ROWTYPE,
1497 p_to_tp_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1498 p_to_tp_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1499 )
1500 RETURN igf_aw_dp_teach_prds.adteach_id%TYPE IS
1501 --------------------------------------------------------------------------------
1502 -- Created by : ridas, Oracle India
1503 -- Date created: 20-MAY-2005
1504
1505 -- Change History:
1506 -- Who When What
1507 --
1508 --------------------------------------------------------------------------------
1509
1510 l_rowid VARCHAR2(25):= NULL;
1511 l_adteach_id igf_aw_dp_teach_prds.adteach_id%TYPE := NULL;
1512
1513 BEGIN
1514 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1515 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.create_new_plan_tp.debug','Insert teaching period attached to the term');
1516 END IF;
1517
1518 igf_aw_dp_teach_prds_pkg.insert_row (
1519 x_mode => 'R',
1520 x_rowid => l_rowid,
1521 x_adteach_id => l_adteach_id,
1522 x_adterms_id => p_adterms_id,
1523 x_tp_cal_type => p_to_tp_cal_type,
1524 x_tp_sequence_number => p_to_tp_sequence_number,
1525 x_tp_perct_num => p_plan_tp_rec.tp_perct_num,
1526 x_date_offset_cd => p_plan_tp_rec.date_offset_cd,
1527 x_attendance_type_code => p_plan_tp_rec.attendance_type_code,
1528 x_credit_points_num => p_plan_tp_rec.credit_points_num
1529 );
1530
1531 RETURN l_adteach_id;
1532
1533 END create_new_plan_tp;
1534
1535
1536 -- Procedure to rollover Award Distribution Plan Setup
1537 PROCEDURE rollover_distribution_plans ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1538 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1539 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1540 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1541 )
1542 IS
1543 --------------------------------------------------------------------------------
1544 -- Created by : ridas, Oracle India
1545 -- Date created: 19-MAY-2005
1546
1547 -- Change History:
1548 -- Who When What
1549 --
1550 --------------------------------------------------------------------------------
1551
1552 -- Get the Award Distribution Plans for the source award year
1553 CURSOR c_distb_plan_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1554 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
1555 ) IS
1556 SELECT plan.*
1557 FROM igf_aw_awd_dist_plans plan
1558 WHERE plan.cal_type = cp_frm_cal_type
1559 AND plan.sequence_number = cp_frm_sequence_number
1560 ORDER BY plan.awd_dist_plan_cd;
1561
1562 -- Check whether the fund already exists or not
1563 CURSOR c_plan_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
1564 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1565 cp_awd_dist_plan_cd igf_aw_awd_dist_plans.awd_dist_plan_cd%TYPE
1566 ) IS
1567 SELECT 'X' exist
1568 FROM igf_aw_awd_dist_plans plan
1569 WHERE plan.cal_type = cp_to_cal_type
1570 AND plan.sequence_number = cp_to_sequence_number
1571 AND plan.awd_dist_plan_cd = cp_awd_dist_plan_cd;
1572
1573 l_plan_exists c_plan_exists%ROWTYPE;
1574
1575 -- Get the Award Distribution Plan Terms attached to the Plan Id
1576 CURSOR c_distb_plan_term( cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1577 ) IS
1578 SELECT plan_term.*
1579 FROM igf_aw_dp_terms plan_term
1580 WHERE plan_term.adplans_id = cp_adplans_id
1581 ORDER BY plan_term.adterms_id;
1582
1583
1584 -- Get the Award Distribution Plan Teaching Periods attached to the Plan Term Id
1585 CURSOR c_distb_plan_tp( cp_adterms_id igf_aw_dp_terms.adterms_id%TYPE
1586 ) IS
1587 SELECT plan_tp.*
1588 FROM igf_aw_dp_teach_prds plan_tp
1589 WHERE plan_tp.adterms_id = cp_adterms_id
1590 ORDER BY plan_tp.adteach_id;
1591
1592
1593 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
1594 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1595 l_to_tp_cal_type igs_ca_inst_all.cal_type%TYPE;
1596 l_to_tp_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1597 l_rowid VARCHAR2(25);
1598 l_new_plan_id igf_aw_awd_dist_plans.adplans_id%TYPE := NULL;
1599 l_adterms_id igf_aw_dp_terms.adterms_id%TYPE := NULL;
1600 l_adteach_id igf_aw_dp_teach_prds.adteach_id%TYPE := NULL;
1601 E_SKIP_PLAN EXCEPTION;
1602 l_error_occurred VARCHAR2(1) := 'N';
1603
1604 BEGIN
1605
1606 fnd_file.new_line(fnd_file.log,1);
1607 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWD_DISTRIBUTION_PLAN')||':' );
1608
1609 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1610 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.debug','Processing Award Distribution Plans');
1611 END IF;
1612
1613
1614 FOR l_distb_plan_setup IN c_distb_plan_setup(p_frm_cal_type, p_frm_sequence_number)
1615 LOOP
1616 BEGIN
1617 SAVEPOINT rollover_distribution_plans;
1618
1619 fnd_message.set_name('IGF','IGF_AW_PROC_DIST_PLAN');
1620 fnd_message.set_token('PLAN',l_distb_plan_setup.awd_dist_plan_cd);
1621 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
1622
1623 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1624 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.debug','Distribution Plan :'||l_distb_plan_setup.awd_dist_plan_cd);
1625 END IF;
1626
1627 --Check whether the distribution plan already got rolled over or not
1628 OPEN c_plan_exists(p_to_cal_type, p_to_sequence_number, l_distb_plan_setup.awd_dist_plan_cd);
1629 FETCH c_plan_exists INTO l_plan_exists;
1630 IF c_plan_exists%FOUND THEN
1631 CLOSE c_plan_exists;
1632 fnd_message.set_name('IGF','IGF_AW_PLN_ALRDY_EXISTS');
1633 fnd_message.set_token('PLAN',l_distb_plan_setup.awd_dist_plan_cd);
1634 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1635
1636 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1637 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.debug','Distribution Plan already exists');
1638 END IF;
1639 RAISE E_SKIP_PLAN;
1640 END IF;
1641 CLOSE c_plan_exists;
1642
1643 l_error_occurred := 'N';
1644 l_new_plan_id := NULL;
1645 --Create new award distribution plan for the target award year
1646 l_new_plan_id := create_new_plan( p_plan_rec => l_distb_plan_setup,
1647 p_to_cal_type => p_to_cal_type,
1648 p_to_sequence_number => p_to_sequence_number
1649 );
1650
1651 IF l_new_plan_id IS NULL THEN
1652 RAISE E_SKIP_PLAN;
1653 ELSE
1654 --Loop for Plan Terms
1655 FOR l_distb_plan_term IN c_distb_plan_term(l_distb_plan_setup.adplans_id)
1656 LOOP
1657 l_to_ld_cal_type := NULL;
1658 l_to_ld_sequence_number := NULL;
1659
1660 --chech whether term mapping exists or not
1661 IF NOT chk_calendar_mapping(l_distb_plan_term.ld_cal_type,l_distb_plan_term.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
1662 fnd_message.set_name('IGF','IGF_AW_TRM_MAP_NT_FND');
1663 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(l_distb_plan_term.ld_cal_type,l_distb_plan_term.ld_sequence_number));
1664 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1665
1666 l_error_occurred := 'Y';
1667 END IF;
1668
1669 l_adterms_id := NULL;
1670
1671 IF l_error_occurred = 'N' THEN
1672 --Create new award distribution plan term for the target award year
1673 l_adterms_id := create_new_plan_term( p_adplans_id => l_new_plan_id,
1674 p_plan_term_rec => l_distb_plan_term,
1675 p_to_ld_cal_type => l_to_ld_cal_type,
1676 p_to_ld_sequence_number => l_to_ld_sequence_number
1677 );
1678 END IF;
1679
1680 IF l_adterms_id IS NULL AND l_error_occurred = 'N' THEN
1681 RAISE E_SKIP_PLAN;
1682 ELSE
1683 --Loop for Plan Teaching Periods
1684 FOR l_distb_plan_tp IN c_distb_plan_tp(l_distb_plan_term.adterms_id)
1685 LOOP
1686 l_to_tp_cal_type := NULL;
1687 l_to_tp_sequence_number := NULL;
1688
1689 --chech whether teaching period mapping exists or not
1690 IF NOT chk_calendar_mapping(l_distb_plan_tp.tp_cal_type,l_distb_plan_tp.tp_sequence_number,l_to_tp_cal_type,l_to_tp_sequence_number) THEN
1691 fnd_message.set_name('IGF','IGF_AW_TP_MAP_NT_FND');
1692 fnd_message.set_token('PERIOD',igf_gr_gen.get_alt_code(l_distb_plan_tp.tp_cal_type,l_distb_plan_tp.tp_sequence_number));
1693 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1694
1695 l_error_occurred := 'Y';
1696 END IF;
1697
1698 l_adteach_id := NULL;
1699
1700 IF l_error_occurred = 'N' THEN
1701 --Create new award distribution plan teaching period for the target award year
1702 l_adteach_id := create_new_plan_tp( p_adterms_id => l_adterms_id,
1703 p_plan_tp_rec => l_distb_plan_tp,
1704 p_to_tp_cal_type => l_to_tp_cal_type,
1705 p_to_tp_sequence_number => l_to_tp_sequence_number
1706 );
1707 END IF;
1708
1709 IF l_adteach_id IS NULL AND l_error_occurred = 'N' THEN
1710 RAISE E_SKIP_PLAN;
1711 END IF;
1712
1713 END LOOP; --End of FOR LOOP for Plan Teaching Periods
1714 END IF;
1715 END LOOP; --End of FOR LOOP for Plan Terms
1716 END IF;
1717
1718 IF l_error_occurred = 'N' THEN
1719 COMMIT;
1720
1721 fnd_message.set_name('IGF','IGF_AW_PLN_RLOVR_SUCCFL');
1722 fnd_message.set_token('PLAN',l_distb_plan_setup.awd_dist_plan_cd);
1723 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1724
1725 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1726 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.debug','Successfully rolled over distribution plan :'||l_distb_plan_setup.awd_dist_plan_cd);
1727 END IF;
1728 ELSE
1729 RAISE E_SKIP_PLAN;
1730 END IF;
1731
1732 EXCEPTION
1733 WHEN E_SKIP_PLAN THEN
1734 ROLLBACK TO rollover_distribution_plans;
1735 fnd_message.set_name('IGF','IGF_AW_SKIPPING_PLAN');
1736 fnd_message.set_token('PLAN',l_distb_plan_setup.awd_dist_plan_cd);
1737 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1738
1739 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1740 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.exception','Skipping the distribution plan :'||l_distb_plan_setup.awd_dist_plan_cd);
1741 END IF;
1742
1743 WHEN OTHERS THEN
1744 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1745 fnd_message.set_token('NAME','igf_aw_rollover.rollover_distribution_plans :' || SQLERRM);
1746 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1747 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_distribution_plans.exception','sql error:'||SQLERRM);
1748 END IF;
1749
1750 app_exception.raise_exception;
1751 END;
1752 END LOOP;
1753 END rollover_distribution_plans;
1754
1755
1756
1757 -- Procedure to rollover Cost of Attendance Group Setup
1758 PROCEDURE rollover_coa_groups ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1759 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1760 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1761 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
1762 )
1763 IS
1764 --------------------------------------------------------------------------------
1765 -- Created by : ridas, Oracle India
1766 -- Date created: 20-MAY-2005
1767
1768 -- Change History:
1769 -- Who When What
1770 --
1771 --------------------------------------------------------------------------------
1772
1773 -- Get the Cost of Attendance Group Setup for the source award year
1774 CURSOR c_coa_grp_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1775 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
1776 ) IS
1777 SELECT coa.*
1778 FROM igf_aw_coa_group_all coa
1779 WHERE coa.ci_cal_type = cp_frm_cal_type
1780 AND coa.ci_sequence_number = cp_frm_sequence_number
1781 ORDER BY coa.coa_code;
1782
1783 -- check the existence of the COA Group in the target award year
1784 CURSOR c_coa_grp_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
1785 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1786 cp_coa_code igf_aw_coa_group_all.coa_code%TYPE
1787 ) IS
1788 SELECT 'X' exist
1789 FROM igf_aw_coa_group_all coa
1790 WHERE coa.ci_cal_type = cp_to_cal_type
1791 AND coa.ci_sequence_number = cp_to_sequence_number
1792 AND coa.coa_code = cp_coa_code;
1793
1794 l_coa_grp_exists c_coa_grp_exists%ROWTYPE;
1795
1796
1797 -- Get the COA Items attached to the COA Group
1798 CURSOR c_coa_item( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1799 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1800 cp_coa_code igf_aw_coa_group_all.coa_code%TYPE
1801 ) IS
1802 SELECT coa_item.*
1803 FROM igf_aw_coa_grp_item_all coa_item
1804 WHERE coa_item.ci_cal_type = cp_frm_cal_type
1805 AND coa_item.ci_sequence_number = cp_frm_sequence_number
1806 AND coa_item.coa_code = cp_coa_code
1807 AND coa_item.active = 'Y'
1808 ORDER BY coa_item.item_code;
1809
1810
1811 --Check the existence of the COA Item in the rate table
1812 CURSOR c_coa_itm_exists (cp_cal_type igs_ca_inst_all.cal_type%TYPE,
1813 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1814 cp_item_code igf_aw_coa_rate_det.item_code%TYPE
1815 ) IS
1816 SELECT 'X' exist
1817 FROM igf_aw_coa_rate_det
1818 WHERE ci_cal_type = cp_cal_type
1819 AND ci_sequence_number = cp_sequence_number
1820 AND item_code = cp_item_code
1821 AND rownum = 1;
1822
1823 l_frm_coa_itm_exists c_coa_itm_exists%ROWTYPE;
1824 l_to_coa_itm_exists c_coa_itm_exists%ROWTYPE;
1825
1826
1827 -- Get the load percentage split up attached to the COA Group
1828 CURSOR c_ld_coa ( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1829 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1830 cp_coa_code igf_aw_coa_group_all.coa_code%TYPE
1831 ) IS
1832 SELECT ld_coa.*
1833 FROM igf_aw_coa_ld_all ld_coa
1834 WHERE ld_coa.ci_cal_type = cp_frm_cal_type
1835 AND ld_coa.ci_sequence_number = cp_frm_sequence_number
1836 AND ld_coa.coa_code = cp_coa_code;
1837
1838
1839 -- Get the overridden items attached to the COA Group
1840 CURSOR c_overridden_item ( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
1841 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1842 cp_coa_code igf_aw_coa_group_all.coa_code%TYPE
1843 ) IS
1844 SELECT over.*
1845 FROM igf_aw_cit_ld_ovrd_all over
1846 WHERE over.ci_cal_type = cp_frm_cal_type
1847 AND over.ci_sequence_number = cp_frm_sequence_number
1848 AND over.coa_code = cp_coa_code;
1849
1850
1851 l_rowid VARCHAR2(25):= NULL;
1852 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
1853 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1854 l_coald_id igf_aw_coa_ld_all.coald_id%TYPE := NULL;
1855 l_cldo_id igf_aw_cit_ld_ovrd_all.cldo_id%TYPE := NULL;
1856 E_SKIP_COA_GRP EXCEPTION;
1857 l_error_occurred VARCHAR2(1) := 'N';
1858
1859 BEGIN
1860
1861 fnd_file.new_line(fnd_file.log,1);
1862 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','COA_GROUP')||':' );
1863
1864 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1865 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','Processing COA Groups');
1866 END IF;
1867
1868
1869 FOR l_coa_grp_setup IN c_coa_grp_setup(p_frm_cal_type, p_frm_sequence_number)
1870 LOOP
1871 BEGIN
1872 SAVEPOINT rollover_coa_groups;
1873
1874 fnd_message.set_name('IGF','IGF_AW_PROC_COA_GROUP');
1875 fnd_message.set_token('COA_GROUP',l_coa_grp_setup.coa_code);
1876 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
1877
1878 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1879 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','COA Group :'||l_coa_grp_setup.coa_code);
1880 END IF;
1881
1882
1883 --Check whether the COA Group already got rolled over or not
1884 OPEN c_coa_grp_exists(p_to_cal_type, p_to_sequence_number, l_coa_grp_setup.coa_code);
1885 FETCH c_coa_grp_exists INTO l_coa_grp_exists;
1886 IF c_coa_grp_exists%FOUND THEN
1887 CLOSE c_coa_grp_exists;
1888 fnd_message.set_name('IGF','IGF_AW_COA_GRP_ALRDY_EXISTS');
1889 fnd_message.set_token('COA_GROUP',l_coa_grp_setup.coa_code);
1890 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1891
1892 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1893 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','COA Group already exists');
1894 END IF;
1895
1896 RAISE E_SKIP_COA_GRP;
1897 END IF;
1898 CLOSE c_coa_grp_exists;
1899
1900 l_rowid := NULL;
1901 --Create new COA Group for the target award year
1902 igf_aw_coa_group_pkg.insert_row (
1903 x_mode => 'R',
1904 x_rowid => l_rowid,
1905 x_coa_code => l_coa_grp_setup.coa_code,
1906 x_ci_cal_type => p_to_cal_type,
1907 x_ci_sequence_number => p_to_sequence_number,
1908 x_rule_order => NULL,
1909 x_s_rule_call_cd => NULL,
1910 x_rul_sequence_number => NULL,
1911 x_pell_coa => NULL,
1912 x_pell_alt_exp => NULL,
1913 x_coa_grp_desc => l_coa_grp_setup.coa_grp_desc
1914 );
1915
1916 l_error_occurred := 'N';
1917
1918 FOR l_coa_item IN c_coa_item(p_frm_cal_type, p_frm_sequence_number, l_coa_grp_setup.coa_code)
1919 LOOP
1920
1921 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1922 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','COA Item :'||l_coa_item.item_code);
1923 END IF;
1924
1925 --check the COA Item in the rate table for the source award year
1926 --OPEN c_coa_itm_exists(p_frm_cal_type, p_frm_sequence_number, l_coa_item.item_code);
1927 --FETCH c_coa_itm_exists INTO l_frm_coa_itm_exists;
1928 IF l_coa_item.default_value IS NULL THEN
1929 --CLOSE c_coa_itm_exists;
1930
1931 --If present, check in the rate table for the target award year
1932 OPEN c_coa_itm_exists(p_to_cal_type, p_to_sequence_number, l_coa_item.item_code);
1933 FETCH c_coa_itm_exists INTO l_to_coa_itm_exists;
1934
1935 IF c_coa_itm_exists%NOTFOUND THEN
1936 fnd_message.set_name('IGF','IGF_AW_RT_SETUP_NT_EXISTS');
1937 fnd_message.set_token('ITEM',l_coa_item.item_code);
1938 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_to_cal_type,p_to_sequence_number));
1939 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1940
1941 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1942 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','Rate Setup does not exist in the target award year');
1943 END IF;
1944
1945 --l_error_occurred := 'Y';
1946 END IF;
1947 CLOSE c_coa_itm_exists;
1948 --ELSE
1949 --CLOSE c_coa_itm_exists;
1950 END IF; --end of c_chk_coa_itm%FOUND
1951
1952 IF l_error_occurred = 'N' THEN
1953 l_rowid := NULL;
1954 -- create new COA group items for the target award year
1955 igf_aw_coa_grp_item_pkg.insert_row (
1956 x_mode => 'R',
1957 x_rowid => l_rowid,
1958 x_coa_code => l_coa_item.coa_code,
1959 x_ci_cal_type => p_to_cal_type,
1960 x_ci_sequence_number => p_to_sequence_number,
1961 x_item_code => l_coa_item.item_code,
1962 x_default_value => l_coa_item.default_value,
1963 x_fixed_cost => l_coa_item.fixed_cost,
1964 x_pell_coa => l_coa_item.pell_coa,
1965 x_active => l_coa_item.active,
1966 x_pell_amount => l_coa_item.pell_amount,
1967 x_pell_alternate_amt => l_coa_item.pell_alternate_amt,
1968 x_item_dist => l_coa_item.item_dist,
1969 x_lock_flag => l_coa_item.lock_flag
1970 );
1971 END IF;
1972
1973 END LOOP; -- end of FOR l_coa_item IN c_coa_item
1974
1975
1976 FOR l_ld_coa IN c_ld_coa(p_frm_cal_type, p_frm_sequence_number, l_coa_grp_setup.coa_code)
1977 LOOP
1978 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1979 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','Term attached to the Item');
1980 END IF;
1981
1982 l_to_ld_cal_type := NULL;
1983 l_to_ld_sequence_number := NULL;
1984
1985 --chech whether term mapping exists or not
1986 IF NOT chk_calendar_mapping(l_ld_coa.ld_cal_type,l_ld_coa.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
1987 fnd_message.set_name('IGF','IGF_AW_TRM_MAP_NT_FND');
1988 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(l_ld_coa.ld_cal_type,l_ld_coa.ld_sequence_number));
1989 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
1990
1991 l_error_occurred := 'Y';
1992 END IF;
1993
1994 IF l_error_occurred = 'N' THEN
1995 l_rowid := NULL;
1996 igf_aw_coa_ld_pkg.insert_row (
1997 x_mode => 'R',
1998 x_rowid => l_rowid,
1999 x_coald_id => l_coald_id,
2000 x_coa_code => l_ld_coa.coa_code,
2001 x_ci_cal_type => p_to_cal_type,
2002 x_ci_sequence_number => p_to_sequence_number,
2003 x_ld_cal_type => l_to_ld_cal_type,
2004 x_ld_sequence_number => l_to_ld_sequence_number,
2005 x_ld_perct => l_ld_coa.ld_perct
2006 );
2007 END IF;
2008 END LOOP; -- end of FOR l_ld_coa IN c_ld_coa
2009
2010 FOR l_overridden_item IN c_overridden_item(p_frm_cal_type, p_frm_sequence_number, l_coa_grp_setup.coa_code)
2011 LOOP
2012 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2013 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','Overridden COA Item :'||l_overridden_item.item_code);
2014 END IF;
2015
2016 l_to_ld_cal_type := NULL;
2017 l_to_ld_sequence_number := NULL;
2018
2019 --check whether term mapping exists or not
2020 IF NOT chk_calendar_mapping(l_overridden_item.ld_cal_type,l_overridden_item.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
2021 --fnd_message.set_name('IGF','IGF_AW_TRM_MAP_NT_FND');
2022 fnd_message.set_name('IGF','IGF_AW_TRM_NT_EXISTS');
2023 fnd_message.set_token('ITEM',l_overridden_item.item_code);
2024 fnd_message.set_token('TERM',igf_gr_gen.get_alt_code(l_overridden_item.ld_cal_type,l_overridden_item.ld_sequence_number));
2025 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2026
2027 l_error_occurred := 'Y';
2028 END IF;
2029
2030 IF l_error_occurred = 'N' THEN
2031 l_rowid := NULL;
2032 igf_aw_cit_ld_ovrd_pkg.insert_row (
2033 x_mode => 'R',
2034 x_rowid => l_rowid,
2035 x_cldo_id => l_cldo_id,
2036 x_coa_code => l_overridden_item.coa_code,
2037 x_ci_cal_type => p_to_cal_type,
2038 x_ci_sequence_number => p_to_sequence_number,
2039 x_item_code => l_overridden_item.item_code,
2040 x_ld_cal_type => l_to_ld_cal_type,
2041 x_ld_sequence_number => l_to_ld_sequence_number,
2042 x_ld_perct => l_overridden_item.ld_perct
2043 );
2044 END IF;
2045
2046 END LOOP; -- end of FOR l_overridden_item IN c_overridden_item
2047
2048 IF l_error_occurred = 'N' THEN
2049 COMMIT;
2050 fnd_message.set_name('IGF','IGF_AW_COA_GRP_RLOVR_SUCCFL');
2051 fnd_message.set_token('COA_GROUP',l_coa_grp_setup.coa_code);
2052 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2053
2054 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2055 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_coa_groups.debug','Successfully rolled over coa group :'||l_coa_grp_setup.coa_code);
2056 END IF;
2057 ELSE
2058 RAISE E_SKIP_COA_GRP;
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN E_SKIP_COA_GRP THEN
2063 ROLLBACK TO rollover_coa_groups;
2064 fnd_message.set_name('IGF','IGF_AW_SKIPPING_COA_GRP');
2065 fnd_message.set_token('COA_GROUP',l_coa_grp_setup.coa_code);
2066 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2067
2068 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2069 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_coa_groups.exception','Skipping the COA group :'||l_coa_grp_setup.coa_code);
2070 END IF;
2071
2072 WHEN OTHERS THEN
2073 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2074 fnd_message.set_token('NAME','igf_aw_rollover.rollover_coa_groups :' || SQLERRM);
2075 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2076 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_coa_groups.exception','sql error:'||SQLERRM);
2077 END IF;
2078
2079 app_exception.raise_exception;
2080 END;
2081 END LOOP;
2082 END rollover_coa_groups;
2083
2084
2085 -- Procedure to rollover To Do Item Setup
2086 PROCEDURE rollover_todo_items ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2087 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2088 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2089 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
2090 )
2091 IS
2092 --------------------------------------------------------------------------------
2093 -- Created by : ridas, Oracle India
2094 -- Date created: 23-MAY-2005
2095
2096 -- Change History:
2097 -- Who When What
2098 --
2099 --------------------------------------------------------------------------------
2100
2101 -- Get the To Do Item details for the source award year
2102 CURSOR c_todo_item_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2103 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
2104 ) IS
2105 SELECT todo.*
2106 FROM igf_ap_td_item_mst_all todo
2107 WHERE todo.ci_cal_type = cp_frm_cal_type
2108 AND todo.ci_sequence_number = cp_frm_sequence_number
2109 ORDER BY todo.item_code;
2110
2111 -- Check whether the todo item already got rolled over or not
2112 CURSOR c_todo_exists(cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
2113 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2114 cp_item_code igf_ap_td_item_mst_all.item_code%TYPE
2115 ) IS
2116 SELECT 'X' exist
2117 FROM igf_ap_td_item_mst_all todo
2118 WHERE todo.ci_cal_type = cp_to_cal_type
2119 AND todo.ci_sequence_number = cp_to_sequence_number
2120 AND todo.item_code = cp_item_code;
2121
2122 l_todo_exists c_todo_exists%ROWTYPE;
2123
2124 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
2125 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2126 l_rowid VARCHAR2(25):= NULL;
2127 l_todo_number igf_ap_td_item_mst_all.todo_number%TYPE;
2128 E_SKIP_TODO EXCEPTION;
2129 lv_return_flg VARCHAR2(1);
2130
2131 BEGIN
2132
2133 fnd_file.new_line(fnd_file.log,1);
2134 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','TODO_ITEM')||':' );
2135
2136 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2137 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_todo_items.debug','Processing To Do Items');
2138 END IF;
2139
2140
2141 FOR l_todo_item_setup IN c_todo_item_setup(p_frm_cal_type, p_frm_sequence_number)
2142 LOOP
2143 BEGIN
2144 SAVEPOINT rollover_todo_items;
2145
2146 fnd_message.set_name('IGF','IGF_AP_PROC_TODO_ITEM');
2147 fnd_message.set_token('ITEM',l_todo_item_setup.item_code);
2148 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
2149
2150 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2151 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_todo_items.debug','To Do Item :'||l_todo_item_setup.item_code);
2152 END IF;
2153
2154 --Check whether the todo item already got rolled over
2155 OPEN c_todo_exists(p_to_cal_type, p_to_sequence_number, l_todo_item_setup.item_code);
2156 FETCH c_todo_exists INTO l_todo_exists;
2157 IF c_todo_exists%FOUND THEN
2158 CLOSE c_todo_exists;
2159 fnd_message.set_name('IGF','IGF_AP_TODO_ALRDY_EXISTS');
2160 fnd_message.set_token('ITEM',l_todo_item_setup.item_code);
2161 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2162
2163 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2164 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_todo_items.debug','To Do Item already exists');
2165 END IF;
2166
2167 RAISE E_SKIP_TODO;
2168 END IF;
2169 CLOSE c_todo_exists;
2170
2171 l_rowid := NULL;
2172 l_todo_number := NULL;
2173 --insert into the To Do Items table
2174 igf_ap_td_item_mst_pkg.insert_row (
2175 x_rowid => l_rowid,
2176 x_todo_number => l_todo_number,
2177 x_item_code => l_todo_item_setup.item_code,
2178 x_ci_cal_type => p_to_cal_type,
2179 x_ci_sequence_number => p_to_sequence_number,
2180 x_description => l_todo_item_setup.description,
2181 x_corsp_mesg => l_todo_item_setup.corsp_mesg,
2182 x_career_item => l_todo_item_setup.career_item,
2183 x_required_for_application => l_todo_item_setup.required_for_application,
2184 x_freq_attempt => l_todo_item_setup.freq_attempt,
2185 x_max_attempt => l_todo_item_setup.max_attempt,
2186 x_mode => 'R',
2187 x_system_todo_type_code => l_todo_item_setup.system_todo_type_code,
2188 x_application_code => l_todo_item_setup.application_code,
2189 x_display_in_ss_flag => l_todo_item_setup.display_in_ss_flag,
2190 x_ss_instruction_txt => l_todo_item_setup.ss_instruction_txt,
2191 x_allow_attachment_flag => l_todo_item_setup.allow_attachment_flag,
2192 x_document_url_txt => l_todo_item_setup.document_url_txt
2193 );
2194
2195 IF l_todo_item_setup.system_todo_type_code = 'INSTAPP' THEN
2196 lv_return_flg := rollover_inst_attch_todo ( p_frm_cal_type => p_frm_cal_type,
2197 p_frm_sequence_number => p_frm_sequence_number,
2198 p_to_cal_type => p_to_cal_type,
2199 p_to_sequence_number => p_to_sequence_number,
2200 p_application_code => l_todo_item_setup.application_code
2201 );
2202
2203 IF lv_return_flg = 'Y' THEN
2204 fnd_message.set_name('IGF','IGF_AP_INST_ATTCH_TODO_ERR');
2205 fnd_message.set_token('APPLICATION',l_todo_item_setup.application_code);
2206 fnd_message.set_token('ITEM',l_todo_item_setup.item_code);
2207 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2208
2209 RAISE E_SKIP_TODO;
2210 END IF;
2211 END IF;
2212
2213 COMMIT;
2214
2215 fnd_message.set_name('IGF','IGF_AP_TODO_RLOVR_SUCCFL');
2216 fnd_message.set_token('ITEM',l_todo_item_setup.item_code);
2217 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2218
2219 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2220 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_todo_items.debug','Successfully rolled over To Do Item :'||l_todo_item_setup.item_code);
2221 END IF;
2222
2223 EXCEPTION
2224 WHEN E_SKIP_TODO THEN
2225 fnd_message.set_name('IGF','IGF_AP_SKIPPING_TODO');
2226 fnd_message.set_token('ITEM',l_todo_item_setup.item_code);
2227 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2228
2229 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2230 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_todo_items.exception','Skipping the To Do Item :'||l_todo_item_setup.item_code);
2231 END IF;
2232
2233 WHEN OTHERS THEN
2234 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2235 fnd_message.set_token('NAME','igf_aw_rollover.rollover_todo_items :' || SQLERRM);
2236 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2237 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_todo_items.exception','sql error:'||SQLERRM);
2238 END IF;
2239
2240 app_exception.raise_exception;
2241 END;
2242 END LOOP;
2243 END rollover_todo_items;
2244
2245
2246 --Function to return Award Distribution Plan Code
2247 FUNCTION get_plan_cd(
2248 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
2249 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2250 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
2251 )
2252 RETURN igf_aw_awd_dist_plans.awd_dist_plan_cd%TYPE AS
2253 ------------------------------------------------------------------
2254 --Created by : ridas, Oracle India
2255 --Date created: 24-MAY-2005
2256 --
2257 --Purpose:
2258 --
2259 --
2260 --Known limitations/enhancements and/or remarks:
2261 --
2262 --Change History:
2263 --Who When What
2264 -------------------------------------------------------------------
2265
2266 -- Get plan code
2267 CURSOR c_plan(
2268 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
2269 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
2270 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE
2271 ) IS
2272 SELECT awd_dist_plan_cd
2273 FROM igf_aw_awd_dist_plans
2274 WHERE adplans_id = cp_adplans_id
2275 AND cal_type = cp_cal_type
2276 AND sequence_number = cp_sequence_number;
2277
2278 l_plan c_plan%ROWTYPE;
2279
2280 BEGIN
2281 OPEN c_plan(p_adplans_id, p_cal_type, p_sequence_number);
2282 FETCH c_plan INTO l_plan;
2283 CLOSE c_plan;
2284
2285 RETURN l_plan.awd_dist_plan_cd;
2286 END get_plan_cd;
2287
2288
2289 --Function to return fund code
2290 FUNCTION get_fund_cd (
2291 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
2292 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2293 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
2294 )
2295 RETURN igf_aw_fund_mast_all.fund_code%TYPE AS
2296 ------------------------------------------------------------------
2297 --Created by : ridas, Oracle India
2298 --Date created: 24-MAY-2005
2299 --
2300 --Purpose:
2301 --
2302 --
2303 --Known limitations/enhancements and/or remarks:
2304 --
2305 --Change History:
2306 --Who When What
2307 -------------------------------------------------------------------
2308
2309 -- Get get fund code
2310 CURSOR c_fund(
2311 cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE,
2312 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
2313 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE
2314 ) IS
2315 SELECT fund_code
2316 FROM igf_aw_fund_mast_all
2317 WHERE fund_id = cp_fund_id
2318 AND ci_cal_type = cp_cal_type
2319 AND ci_sequence_number = cp_sequence_number;
2320
2321 l_fund c_fund%ROWTYPE;
2322
2323 BEGIN
2324 OPEN c_fund(p_fund_id, p_cal_type, p_sequence_number);
2325 FETCH c_fund INTO l_fund;
2326 CLOSE c_fund;
2327
2328 RETURN l_fund.fund_code;
2329 END get_fund_cd;
2330
2331
2332 -- Procedure to rollover Award Group Setup
2333 PROCEDURE rollover_award_groups ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2334 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2335 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2336 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
2337 )
2338 IS
2339 --------------------------------------------------------------------------------
2340 -- Created by : ridas, Oracle India
2341 -- Date created: 23-MAY-2005
2342
2343 -- Change History:
2344 -- Who When What
2345 --
2346 --------------------------------------------------------------------------------
2347
2348 -- Get the Award Group details for the source award year
2349 CURSOR c_award_grp_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2350 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
2351 ) IS
2352 SELECT grp.*
2353 FROM igf_aw_target_grp_all grp
2354 WHERE grp.cal_type = cp_frm_cal_type
2355 AND grp.sequence_number = cp_frm_sequence_number
2356 ORDER BY grp.group_cd;
2357
2358
2359 -- Check whether the award group already got rolled over or not
2360 CURSOR c_grp_exists (cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
2361 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2362 cp_group_cd igf_aw_target_grp_all.group_cd%TYPE
2363 ) IS
2364 SELECT 'X' exist
2365 FROM igf_aw_target_grp_all grp
2366 WHERE grp.cal_type = cp_to_cal_type
2367 AND grp.sequence_number = cp_to_sequence_number
2368 AND grp.group_cd = cp_group_cd;
2369
2370 l_grp_exists c_grp_exists%ROWTYPE;
2371
2372
2373 -- Get the sequence of funds attached to the formulas for the source award year
2374 CURSOR c_formula_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2375 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2376 cp_group_cd igf_aw_target_grp_all.group_cd%TYPE
2377 ) IS
2378 SELECT frm.*
2379 FROM igf_aw_awd_frml_det_all frm
2380 WHERE frm.ci_cal_type = cp_frm_cal_type
2381 AND frm.ci_sequence_number = cp_frm_sequence_number
2382 AND frm.formula_code = cp_group_cd
2383 ORDER BY frm.adplans_id;
2384
2385
2386 -- Check whether the Distribution Plan got rolled over
2387 CURSOR c_plan_exists (cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2388 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2389 cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
2390 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2391 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
2392 ) IS
2393 SELECT plan.adplans_id
2394 FROM igf_aw_awd_dist_plans plan
2395 WHERE plan.cal_type = cp_to_cal_type
2396 AND plan.sequence_number = cp_to_sequence_number
2397 AND plan.awd_dist_plan_cd IN
2398 ( SELECT plan_cd.awd_dist_plan_cd
2399 FROM igf_aw_awd_dist_plans plan_cd
2400 WHERE plan_cd.cal_type = cp_frm_cal_type
2401 AND plan_cd.sequence_number = cp_frm_sequence_number
2402 AND plan_cd.adplans_id = cp_adplans_id
2403 );
2404
2405 l_plan_exists c_plan_exists%ROWTYPE;
2406
2407
2408 -- Check whether the Funds got rolled over
2409 CURSOR c_fund_exists (cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2410 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2411 cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
2412 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2413 cp_fund_id igf_aw_fund_mast_all.fund_id%TYPE
2414 ) IS
2415 SELECT fnd.fund_id
2416 FROM igf_aw_fund_mast_all fnd
2417 WHERE fnd.ci_cal_type = cp_to_cal_type
2418 AND fnd.ci_sequence_number = cp_to_sequence_number
2419 AND fnd.fund_code IN
2420 ( SELECT fnd_cd.fund_code
2421 FROM igf_aw_fund_mast_all fnd_cd
2422 WHERE fnd_cd.ci_cal_type = cp_frm_cal_type
2423 AND fnd_cd.ci_sequence_number = cp_frm_sequence_number
2424 AND fnd_cd.fund_id = cp_fund_id
2425 );
2426
2427 l_fund_exists c_fund_exists%ROWTYPE;
2428
2429
2430 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
2431 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2432 l_rowid VARCHAR2(25):= NULL;
2433 l_tgrp_id igf_aw_target_grp_all.tgrp_id%TYPE;
2434 E_SKIP_AWARD_GRP EXCEPTION;
2435 l_error_occurred VARCHAR2(1) := 'N';
2436 l_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE := NULL;
2437
2438 BEGIN
2439
2440 fnd_file.new_line(fnd_file.log,1);
2441 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWD_GROUP')||':' );
2442
2443 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2444 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Processing Award Groups');
2445 END IF;
2446
2447 FOR l_award_grp_setup IN c_award_grp_setup(p_frm_cal_type, p_frm_sequence_number)
2448 LOOP
2449 BEGIN
2450 SAVEPOINT rollover_award_groups;
2451 l_error_occurred := 'N';
2452
2453 fnd_message.set_name('IGF','IGF_AW_PROC_AWD_GROUP');
2454 fnd_message.set_token('AWD_GROUP',l_award_grp_setup.group_cd);
2455 fnd_file.put_line(fnd_file.log, RPAD(' ',5)|| fnd_message.get);
2456
2457 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2458 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Award Group :'||l_award_grp_setup.group_cd);
2459 END IF;
2460
2461
2462 --Check whether the award group already got rolled over
2463 OPEN c_grp_exists(p_to_cal_type, p_to_sequence_number, l_award_grp_setup.group_cd);
2464 FETCH c_grp_exists INTO l_grp_exists;
2465 IF c_grp_exists%FOUND THEN
2466 CLOSE c_grp_exists;
2467 fnd_message.set_name('IGF','IGF_AW_AWD_GRP_ALRDY_EXISTS');
2468 fnd_message.set_token('AWD_GROUP',l_award_grp_setup.group_cd);
2469 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2470
2471 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2472 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Award Group already exists');
2473 END IF;
2474
2475 RAISE E_SKIP_AWARD_GRP;
2476 END IF;
2477 CLOSE c_grp_exists;
2478
2479 l_plan_exists := NULL;
2480
2481 IF l_award_grp_setup.adplans_id IS NOT NULL THEN
2482 --Check the existence of the distribution plan in the target award year
2483 OPEN c_plan_exists(p_frm_cal_type, p_frm_sequence_number, p_to_cal_type, p_to_sequence_number, l_award_grp_setup.adplans_id);
2484 FETCH c_plan_exists INTO l_plan_exists;
2485 IF c_plan_exists%NOTFOUND THEN
2486 fnd_message.set_name('IGF','IGF_AW_DIST_PLN_NT_EXISTS');
2487 fnd_message.set_token('PLAN',get_plan_cd(l_award_grp_setup.adplans_id, p_frm_cal_type, p_frm_sequence_number));
2488 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_to_cal_type,p_to_sequence_number));
2489 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2490
2491 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2492 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Distribution Plan does not exist');
2493 END IF;
2494
2495 l_error_occurred := 'Y';
2496 END IF;
2497 CLOSE c_plan_exists;
2498 END IF;
2499
2500 IF l_error_occurred = 'N' THEN
2501 l_rowid := NULL;
2502 l_tgrp_id := NULL;
2503
2504 --insert into the Award Groups table
2505 igf_aw_target_grp_pkg.insert_row (
2506 x_mode => 'R',
2507 x_rowid => l_rowid,
2508 x_group_cd => l_award_grp_setup.group_cd,
2509 x_description => l_award_grp_setup.description,
2510 x_active => l_award_grp_setup.active,
2511 x_max_grant_amt => l_award_grp_setup.max_grant_amt,
2512 x_max_grant_perct => l_award_grp_setup.max_grant_perct,
2513 x_max_grant_perct_fact => l_award_grp_setup.max_grant_perct_fact,
2514 x_max_loan_amt => l_award_grp_setup.max_loan_amt,
2515 x_max_loan_perct => l_award_grp_setup.max_loan_perct,
2516 x_max_loan_perct_fact => l_award_grp_setup.max_loan_perct_fact,
2517 x_max_work_amt => l_award_grp_setup.max_work_amt,
2518 x_max_work_perct => l_award_grp_setup.max_work_perct,
2519 x_max_work_perct_fact => l_award_grp_setup.max_work_perct_fact,
2520 x_max_shelp_amt => l_award_grp_setup.max_shelp_amt,
2521 x_max_shelp_perct => l_award_grp_setup.max_shelp_perct,
2522 x_max_shelp_perct_fact => l_award_grp_setup.max_shelp_perct_fact,
2523 x_max_gap_amt => l_award_grp_setup.max_gap_amt,
2524 x_max_gap_perct => l_award_grp_setup.max_gap_perct,
2525 x_max_gap_perct_fact => l_award_grp_setup.max_gap_perct_fact,
2526 x_use_fixed_costs => l_award_grp_setup.use_fixed_costs,
2527 x_max_aid_pkg => l_award_grp_setup.max_aid_pkg,
2528 x_max_gift_amt => l_award_grp_setup.max_gift_amt,
2529 x_max_gift_perct => l_award_grp_setup.max_gift_perct,
2530 x_max_gift_perct_fact => l_award_grp_setup.max_gift_perct_fact,
2531 x_max_schlrshp_amt => l_award_grp_setup.max_schlrshp_amt,
2532 x_max_schlrshp_perct => l_award_grp_setup.max_schlrshp_perct,
2533 x_max_schlrshp_perct_fact => l_award_grp_setup.max_schlrshp_perct_fact,
2534 x_cal_type => p_to_cal_type,
2535 x_sequence_number => p_to_sequence_number,
2536 x_rule_order => l_award_grp_setup.rule_order,
2537 x_s_rule_call_cd => l_award_grp_setup.s_rule_call_cd,
2538 x_rul_sequence_number => l_award_grp_setup.rul_sequence_number,
2539 x_tgrp_id => l_tgrp_id,
2540 x_adplans_id => l_plan_exists.adplans_id
2541 );
2542 END IF;
2543
2544 l_adplans_id := NULL;
2545
2546 -- Get the sequence of funds attached to the formula
2547 FOR l_formula_setup IN c_formula_setup(p_frm_cal_type, p_frm_sequence_number, l_award_grp_setup.group_cd)
2548 LOOP
2549 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2550 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Formula Code :'||l_formula_setup.formula_code);
2551 END IF;
2552
2553 --Check the existence of the FUND in the target award year
2554 OPEN c_fund_exists(p_frm_cal_type, p_frm_sequence_number, p_to_cal_type, p_to_sequence_number, l_formula_setup.fund_id);
2555 FETCH c_fund_exists INTO l_fund_exists;
2556 IF c_fund_exists%NOTFOUND THEN
2557 fnd_message.set_name('IGF','IGF_AW_FUND_NT_EXISTS');
2558 fnd_message.set_token('FUND',get_fund_cd(l_formula_setup.fund_id, p_frm_cal_type, p_frm_sequence_number));
2559 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_to_cal_type,p_to_sequence_number));
2560 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2561
2562 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2563 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Fund does not exist');
2564 END IF;
2565
2566 l_error_occurred := 'Y';
2567 END IF;
2568 CLOSE c_fund_exists;
2569
2570 l_plan_exists := NULL;
2571
2572 IF l_formula_setup.adplans_id IS NOT NULL AND NVL(l_adplans_id,0) <> NVL(l_formula_setup.adplans_id,0) THEN
2573 --Check the existence of the distribution plan in the target award year
2574 OPEN c_plan_exists(p_frm_cal_type, p_frm_sequence_number, p_to_cal_type, p_to_sequence_number, l_formula_setup.adplans_id);
2575 FETCH c_plan_exists INTO l_plan_exists;
2576 IF c_plan_exists%NOTFOUND THEN
2577 fnd_message.set_name('IGF','IGF_AW_DIST_PLN_OVR_NT_EXISTS');
2578 fnd_message.set_token('PLAN',get_plan_cd(l_formula_setup.adplans_id, p_frm_cal_type, p_frm_sequence_number));
2579 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_to_cal_type,p_to_sequence_number));
2580 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2581
2582 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2583 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Award Distribution Plan does not exist');
2584 END IF;
2585
2586 l_error_occurred := 'Y';
2587 END IF;
2588 CLOSE c_plan_exists;
2589 END IF;
2590
2591 l_adplans_id := l_formula_setup.adplans_id;
2592
2593 IF l_error_occurred = 'N' THEN
2594 l_rowid := NULL;
2595 --Insert the sequence of funds for the award group
2596 igf_aw_awd_frml_det_pkg.insert_row (
2597 x_mode => 'R',
2598 x_rowid => l_rowid,
2599 x_formula_code => l_formula_setup.formula_code,
2600 x_fund_id => l_fund_exists.fund_id,
2601 x_min_award_amt => l_formula_setup.min_award_amt,
2602 x_max_award_amt => l_formula_setup.max_award_amt,
2603 x_seq_no => l_formula_setup.seq_no,
2604 x_ci_cal_type => p_to_cal_type,
2605 x_ci_sequence_number => p_to_sequence_number,
2606 x_replace_fc => l_formula_setup.replace_fc,
2607 x_pe_group_id => l_formula_setup.pe_group_id,
2608 x_adplans_id => l_plan_exists.adplans_id,
2609 x_lock_award_flag => l_formula_setup.lock_award_flag
2610 );
2611
2612 END IF;
2613
2614 END LOOP;
2615
2616 IF l_error_occurred = 'N' THEN
2617 COMMIT;
2618
2619 fnd_message.set_name('IGF','IGF_AW_AWD_GRP_RLOVR_SUCCFL');
2620 fnd_message.set_token('AWD_GROUP',l_award_grp_setup.group_cd);
2621 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2622
2623 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2624 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_award_groups.debug','Successfully rolled over award group :'||l_award_grp_setup.group_cd);
2625 END IF;
2626 ELSE
2627 RAISE E_SKIP_AWARD_GRP;
2628 END IF;
2629
2630 EXCEPTION
2631 WHEN E_SKIP_AWARD_GRP THEN
2632 ROLLBACK TO rollover_award_groups;
2633 fnd_message.set_name('IGF','IGF_AW_SKIPPING_AWD_GRP');
2634 fnd_message.set_token('AWD_GROUP',l_award_grp_setup.group_cd);
2635 fnd_file.put_line(fnd_file.log, RPAD(' ',10)|| fnd_message.get);
2636
2637 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2638 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_award_groups.exception','Skipping the Award Group :'||l_award_grp_setup.group_cd);
2639 END IF;
2640
2641 WHEN OTHERS THEN
2642 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2643 fnd_message.set_token('NAME','igf_aw_rollover.rollover_award_groups :' || SQLERRM);
2644 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2645 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_award_groups.exception','sql error:'||SQLERRM);
2646 END IF;
2647
2648 app_exception.raise_exception;
2649 END;
2650 END LOOP;
2651 END rollover_award_groups;
2652
2653
2654 -- Procedure to rollover Institutional Application Setup attached to TO DO item
2655 FUNCTION rollover_inst_attch_todo ( p_frm_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2656 p_frm_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2657 p_to_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2658 p_to_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2659 p_application_code IN igf_ap_appl_setup_all.application_code%TYPE
2660 )
2661 RETURN VARCHAR IS
2662 --------------------------------------------------------------------------------
2663 -- Created by : ridas, Oracle India
2664 -- Date created: 20-OCT-2005
2665
2666 -- Change History:
2667 -- Who When What
2668 --
2669 --------------------------------------------------------------------------------
2670
2671 -- Get the institutional application setup details for the source award year
2672 CURSOR c_inst_appln_setup( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2673 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2674 cp_application_code igf_ap_appl_setup_all.application_code%TYPE
2675 ) IS
2676 SELECT appln.*
2677 FROM igf_ap_appl_setup_all appln
2678 WHERE appln.ci_cal_type = cp_frm_cal_type
2679 AND appln.ci_sequence_number = cp_frm_sequence_number
2680 AND appln.application_code = cp_application_code
2681 AND NVL(appln.active_flag,'N')= 'Y'
2682 ORDER BY appln.question_id;
2683
2684
2685 -- Check whether the application exists or not
2686 CURSOR c_appln_exists( cp_to_cal_type igs_ca_inst_all.cal_type%TYPE,
2687 cp_to_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2688 cp_application_code igf_ap_appl_setup_all.application_code%TYPE
2689 ) IS
2690 SELECT 'X' exist
2691 FROM igf_ap_appl_setup_all appln
2692 WHERE appln.ci_cal_type = cp_to_cal_type
2693 AND appln.ci_sequence_number = cp_to_sequence_number
2694 AND appln.application_code = cp_application_code
2695 AND NVL(appln.active_flag,'N')= 'Y';
2696
2697 l_appln_exists c_appln_exists%ROWTYPE;
2698
2699 l_rowid VARCHAR2(25);
2700 E_SKIP_APPLICATION EXCEPTION;
2701 l_error_occurred VARCHAR2(1) := 'N';
2702 l_to_ld_cal_type igs_ca_inst_all.cal_type%TYPE;
2703 l_to_ld_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2704
2705 BEGIN
2706
2707 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2708 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.debug','Processing Institutional Applications');
2709 END IF;
2710
2711 SAVEPOINT rollover_inst_attch_todo;
2712
2713 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2714 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.debug','Application :'||p_application_code);
2715 END IF;
2716
2717 --Check whether the application already got rolled over
2718 OPEN c_appln_exists(p_to_cal_type, p_to_sequence_number, p_application_code);
2719 FETCH c_appln_exists INTO l_appln_exists;
2720 IF c_appln_exists%FOUND THEN
2721 CLOSE c_appln_exists;
2722
2723 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2724 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.debug','Application already exists');
2725 END IF;
2726 RETURN 'N';
2727 END IF;
2728 CLOSE c_appln_exists;
2729
2730
2731 FOR l_inst_appln_setup IN c_inst_appln_setup(p_frm_cal_type, p_frm_sequence_number, p_application_code)
2732 LOOP
2733 BEGIN
2734 l_to_ld_cal_type := NULL;
2735 l_to_ld_sequence_number := NULL;
2736
2737 IF l_inst_appln_setup.ld_cal_type IS NOT NULL AND l_inst_appln_setup.ld_sequence_number IS NOT NULL THEN
2738 IF NOT chk_calendar_mapping(l_inst_appln_setup.ld_cal_type,l_inst_appln_setup.ld_sequence_number,l_to_ld_cal_type,l_to_ld_sequence_number) THEN
2739
2740 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2741 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.debug','Calendar Mapping does not exist');
2742 END IF;
2743
2744 l_error_occurred := 'Y';
2745 END IF;
2746 END IF;
2747
2748 IF l_error_occurred = 'N' THEN
2749 l_rowid := NULL;
2750 l_inst_appln_setup.question_id := NULL;
2751
2752 --insert into the application setup table
2753 igf_ap_appl_setup_pkg.insert_row (
2754 x_mode => 'R',
2755 x_rowid => l_rowid,
2756 x_enabled => l_inst_appln_setup.enabled,
2757 x_org_id => l_inst_appln_setup.org_id,
2758 x_ci_cal_type => p_to_cal_type,
2759 x_ci_sequence_number => p_to_sequence_number,
2760 x_question_id => l_inst_appln_setup.question_id,
2761 x_question => l_inst_appln_setup.question,
2762 x_application_code => l_inst_appln_setup.application_code,
2763 x_application_name => l_inst_appln_setup.application_name,
2764 x_active_flag => l_inst_appln_setup.active_flag,
2765 x_answer_type_code => l_inst_appln_setup.answer_type_code,
2766 x_destination_txt => l_inst_appln_setup.destination_txt,
2767 x_ld_cal_type => l_to_ld_cal_type,
2768 x_ld_sequence_number => l_to_ld_sequence_number,
2769 x_all_terms_flag => l_inst_appln_setup.all_terms_flag,
2770 x_override_exist_ant_data_flag => l_inst_appln_setup.override_exist_ant_data_flag,
2771 x_required_flag => l_inst_appln_setup.required_flag,
2772 x_minimum_value_num => l_inst_appln_setup.minimum_value_num,
2773 x_maximum_value_num => l_inst_appln_setup.maximum_value_num,
2774 x_minimum_date => l_inst_appln_setup.minimum_date,
2775 x_maximium_date => l_inst_appln_setup.maximium_date,
2776 x_lookup_code => l_inst_appln_setup.lookup_code,
2777 x_hint_txt => l_inst_appln_setup.hint_txt
2778 );
2779 END IF;
2780
2781
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 l_error_occurred := 'Y';
2785
2786 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2787 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.exception','Skipping Question ID :'||l_inst_appln_setup.question_id);
2788 END IF;
2789 END;
2790 END LOOP;
2791
2792 IF l_error_occurred = 'N' THEN
2793 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2794 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.debug','Successfully rolled over Application :'||p_application_code);
2795 END IF;
2796
2797 RETURN 'N';
2798 ELSE
2799 RAISE E_SKIP_APPLICATION;
2800 END IF;
2801
2802 EXCEPTION
2803 WHEN E_SKIP_APPLICATION THEN
2804 ROLLBACK TO rollover_inst_attch_todo;
2805
2806 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2807 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.exception','Skipping the application :'||p_application_code);
2808 END IF;
2809
2810 RETURN 'Y';
2811
2812 WHEN OTHERS THEN
2813 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2814 fnd_message.set_token('NAME','igf_aw_rollover.rollover_inst_attch_todo :' || SQLERRM);
2815 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2816 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_rollover.rollover_inst_attch_todo.exception','sql error:'||SQLERRM);
2817 END IF;
2818 app_exception.raise_exception;
2819
2820 END rollover_inst_attch_todo;
2821
2822
2823 PROCEDURE main(
2824 errbuf OUT NOCOPY VARCHAR2,
2825 retcode OUT NOCOPY NUMBER,
2826 p_frm_award_year IN VARCHAR2,
2827 p_fund_attribute IN VARCHAR2,
2828 p_org_id IN igf_aw_award_all.org_id%TYPE,
2829 p_rate_table IN VARCHAR2,
2830 p_inst_application IN VARCHAR2,
2831 p_distribution_plan IN VARCHAR2,
2832 p_coa_group IN VARCHAR2,
2833 p_todo IN VARCHAR2,
2834 p_award_grp IN VARCHAR2
2835 ) IS
2836 --------------------------------------------------------------------------------
2837 -- This is the main procedure which is called by the concurrent process
2838 -- 'Rollover Financial Aid Setups'.
2839 --
2840 -- Created by : ridas, Oracle India
2841 -- Date created: 12-MAY-2005
2842
2843 -- Change History:
2844 -- Who When What
2845 --
2846 --------------------------------------------------------------------------------
2847
2848 --Cursor to fetch To Award Year
2849 CURSOR c_get_to_awdyr( cp_frm_cal_type igs_ca_inst_all.cal_type%TYPE,
2850 cp_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE
2851 ) IS
2852 SELECT sc_cal_type,
2853 sc_sequence_number,
2854 sc_alternate_code
2855 FROM igf_aw_cal_rel_v
2856 WHERE cr_cal_type = cp_frm_cal_type
2857 AND cr_sequence_number = cp_frm_sequence_number
2858 AND NVL(active,'N') = 'Y';
2859
2860 l_get_to_awdyr c_get_to_awdyr%ROWTYPE;
2861
2862 lv_frm_cal_type igs_ca_inst_all.cal_type%TYPE;
2863 ln_frm_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2864 lv_to_cal_type igs_ca_inst_all.cal_type%TYPE;
2865 ln_to_sequence_number igs_ca_inst_all.sequence_number%TYPE;
2866 lv_to_award_year igs_ca_inst_all.alternate_code%TYPE;
2867 to_awdyr_exception EXCEPTION;
2868
2869 BEGIN
2870
2871 igf_aw_gen.set_org_id(NULL);
2872
2873 retcode := 0;
2874 errbuf := NULL;
2875 lv_frm_cal_type := LTRIM(RTRIM(SUBSTR(p_frm_award_year,1,10)));
2876 ln_frm_sequence_number := TO_NUMBER(SUBSTR(p_frm_award_year,11));
2877
2878 --get To Award year
2879 OPEN c_get_to_awdyr(lv_frm_cal_type, ln_frm_sequence_number);
2880 FETCH c_get_to_awdyr INTO l_get_to_awdyr;
2881
2882 IF c_get_to_awdyr%FOUND THEN
2883 lv_to_cal_type := l_get_to_awdyr.sc_cal_type;
2884 ln_to_sequence_number := l_get_to_awdyr.sc_sequence_number;
2885 lv_to_award_year := l_get_to_awdyr.sc_alternate_code;
2886 ELSE
2887 RAISE to_awdyr_exception;
2888 END IF;
2889 CLOSE c_get_to_awdyr;
2890
2891
2892 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2893 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_frm_award_year:'||p_frm_award_year);
2894 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_to_award_year:'||lv_to_award_year);
2895 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_fund_attribute:'||p_fund_attribute);
2896 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_rate_table:'||p_rate_table);
2897 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_inst_application:'||p_inst_application);
2898 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_distribution_plan:'||p_distribution_plan);
2899 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_coa_group:'||p_coa_group);
2900 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_todo:'||p_todo);
2901 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','p_award_grp:'||p_award_grp);
2902 END IF;
2903
2904 fnd_file.new_line(fnd_file.log,1);
2905
2906 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
2907 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FROM_AWD_YEAR'),60) || igf_gr_gen.get_alt_code(lv_frm_cal_type,ln_frm_sequence_number));
2908 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','TO_AWD_YEAR'),60) || igf_gr_gen.get_alt_code(lv_to_cal_type,ln_to_sequence_number));
2909
2910 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FUND_ATTRIBUTE'),60) ||p_fund_attribute );
2911 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','COA_RATE_TABLE'),60) ||p_rate_table );
2912 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','INST_APPLICATION'),60) ||p_inst_application );
2913 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWD_DISTRIBUTION_PLAN'),60) ||p_distribution_plan );
2914 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','COA_GROUP'),60) ||p_coa_group );
2915 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','TODO_ITEM'),60) ||p_todo );
2916 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWD_GROUP'),60) ||p_award_grp );
2917
2918 fnd_file.new_line(fnd_file.log,2);
2919
2920 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
2921
2922
2923 -- Rollover Fund Attribute Setup
2924 IF NVL(p_fund_attribute,'N') = 'Y' THEN
2925 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2926 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Fund rollover sub-process');
2927 END IF;
2928
2929 rollover_fund_attributes( p_frm_cal_type => lv_frm_cal_type,
2930 p_frm_sequence_number => ln_frm_sequence_number,
2931 p_to_cal_type => lv_to_cal_type,
2932 p_to_sequence_number => ln_to_sequence_number
2933 );
2934 END IF;
2935
2936 -- Rollover Cost of Attendance Rate Table Setup
2937 IF NVL(p_rate_table,'N') = 'Y' THEN
2938 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2939 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Cost of Attendance Rate Table rollover sub-process');
2940 END IF;
2941
2942 rollover_rate_setups( p_frm_cal_type => lv_frm_cal_type,
2943 p_frm_sequence_number => ln_frm_sequence_number,
2944 p_to_cal_type => lv_to_cal_type,
2945 p_to_sequence_number => ln_to_sequence_number
2946 );
2947 END IF;
2948
2949
2950 -- Rollover Institutional Application Setup
2951 IF NVL(p_inst_application,'N') = 'Y' THEN
2952 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2953 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Institutional Application rollover sub-process');
2954 END IF;
2955
2956
2957 rollover_inst_applications( p_frm_cal_type => lv_frm_cal_type,
2958 p_frm_sequence_number => ln_frm_sequence_number,
2959 p_to_cal_type => lv_to_cal_type,
2960 p_to_sequence_number => ln_to_sequence_number
2961 );
2962 END IF;
2963
2964
2965 -- Rollover Award Distribution Plan Setup
2966 IF NVL(p_distribution_plan,'N') = 'Y' THEN
2967 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2968 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Award Distribution Plan rollover sub-process');
2969 END IF;
2970
2971 rollover_distribution_plans(p_frm_cal_type => lv_frm_cal_type,
2972 p_frm_sequence_number => ln_frm_sequence_number,
2973 p_to_cal_type => lv_to_cal_type,
2974 p_to_sequence_number => ln_to_sequence_number
2975 );
2976 END IF;
2977
2978 -- Rollover Cost of Attendance Group Setup
2979 IF NVL(p_coa_group,'N') = 'Y' THEN
2980 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2981 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Cost of Attendance Group rollover sub-process');
2982 END IF;
2983
2984 rollover_coa_groups(p_frm_cal_type => lv_frm_cal_type,
2985 p_frm_sequence_number => ln_frm_sequence_number,
2986 p_to_cal_type => lv_to_cal_type,
2987 p_to_sequence_number => ln_to_sequence_number
2988 );
2989 END IF;
2990
2991 -- Rollover To Do Item Setup
2992 IF NVL(p_todo,'N') = 'Y' THEN
2993 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2994 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling To Do Item rollover sub-process');
2995 END IF;
2996
2997 rollover_todo_items(p_frm_cal_type => lv_frm_cal_type,
2998 p_frm_sequence_number => ln_frm_sequence_number,
2999 p_to_cal_type => lv_to_cal_type,
3000 p_to_sequence_number => ln_to_sequence_number
3001 );
3002 END IF;
3003
3004 -- Rollover Award Group Setup
3005 IF NVL(p_award_grp,'N') = 'Y' THEN
3006 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3007 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_rollover.main.debug','Calling Award Group rollover sub-process');
3008 END IF;
3009
3010 rollover_award_groups(p_frm_cal_type => lv_frm_cal_type,
3011 p_frm_sequence_number => ln_frm_sequence_number,
3012 p_to_cal_type => lv_to_cal_type,
3013 p_to_sequence_number => ln_to_sequence_number
3014 );
3015 END IF;
3016
3017
3018 fnd_file.new_line(fnd_file.log,1);
3019 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
3020
3021 COMMIT;
3022
3023 EXCEPTION
3024 WHEN to_awdyr_exception THEN
3025 retcode:=2;
3026 fnd_message.set_name('IGF','IGF_AW_AWD_NT_EXISTS');
3027 igs_ge_msg_stack.add;
3028 errbuf := fnd_message.get;
3029
3030 WHEN app_exception.record_lock_exception THEN
3031 ROLLBACK;
3032 retcode:=2;
3033 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
3034 igs_ge_msg_stack.add;
3035 errbuf := fnd_message.get;
3036
3037 WHEN OTHERS THEN
3038 ROLLBACK;
3039 retcode:=2;
3040 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
3041 igs_ge_msg_stack.add;
3042 errbuf := fnd_message.get || SQLERRM;
3043
3044 END main;
3045
3046 END igf_aw_rollover;