1 package body ben_comp_object as
2 /* $Header: bencompo.pkb 120.0 2005/05/28 03:51:44 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Comp Object Caching Routine
13 Purpose
14 This package is used to return comp object information.
15 History
16 Date Who Version What?
17 ---- --- ------- -----
18 05 May 99 G Perry 115.0 Created
19 27 May 99 G Perry 115.1 Added extra cache for cobra.
20 25 Jun 99 G Perry 115.2 Made objects cache on demand.
21 08 Jul 99 mhoyes 115.3 - Modified trace messages.
22 - Removed + 0s from all cursors.
23 12 Jul 99 jcarpent 115.4 - Added checks for backed out pil.
24 04 Aug 99 G Perry 115.5 Added last record got cache.
25 12 Aug 99 mhoyes 115.6 - Removed frequently executed trace
26 locations.
27 12 Aug 99 G Perry 115.7 Added BUSINESS_GROUP and
28 EFFECTIVE_DATE as extra contexts
29 for error message.
30 31 Aug 99 mhoyes 115.8 - Removed frequently executed trace
31 locations.
32 31 Mar 99 gperry 115.9 Added oiplip support.
33 06 May 00 rchase 115.10 Performance modifications
34 29 Dec 00 tmathers 115.11 fixed check_sql errors.
35 03 Jun 04 rpgupta 115.13 3662774
36 changed cursor c1 for performance
37 */
38 --------------------------------------------------------------------------------
39 --
40 g_package varchar2(30) := 'ben_comp_object.';
41 g_hash_key number := ben_hash_utility.get_hash_key;
42 g_hash_jump number := ben_hash_utility.get_hash_jump;
43 --
44 -- Set object routines
45 --
46 procedure set_object(p_rec in out NOCOPY ben_pgm_f%rowtype) is
47 --
48 l_proc varchar2(80) := g_package||'set_object pgm';
49 l_index pls_integer;
50 --
51 begin
52 --
53 -- hr_utility.set_location('Entering '||l_proc,10);
54 --
55 -- 1) get hash index
56 -- 2) If hash index is not used use hash index
57 -- 3) If hash index is used and correct then do nothing
58 -- 4) If hash index is used and not correct then try next hash index
59 --
60 -- Get hashed index value
61 --
62 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.pgm_id);
63 --115.10 replaced original exists checking with equality check and exception
64 if g_cache_pgm_rec(l_index).pgm_id = p_rec.pgm_id then
65 -- do nothing, cache entry already exists
66 null;
67 else
68 --
69 -- Loop through the hash using the jump routine to check further
70 -- indexes
71 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
72 --
73 l_index := l_index+g_hash_jump;
74 while g_cache_pgm_rec(l_index).pgm_id <> p_rec.pgm_id loop
75 --
76 l_index := l_index+g_hash_jump;
77
78 end loop;
79 --
80 end if;
81 --
82 -- hr_utility.set_location('Leaving '||l_proc,10);
83 --
84 exception when NO_DATA_FOUND then
85 -- set cache entry at current index location
86 g_cache_pgm_rec(l_index):=p_rec;
87 --
88 end set_object;
89 --
90 procedure set_object(p_rec in out nocopy ben_pl_f%rowtype) is
91 --
92 l_proc varchar2(80) := g_package||'set_object pln ';
93 l_index pls_integer;
94 --
95 begin
96 --
97 -- 1) get hash index
98 -- 2) If hash index is not used use hash index
99 -- 3) If hash index is used and correct then do nothing
100 -- 4) If hash index is used and not correct then try next hash index
101 --
102 -- Get hashed index value
103 --
104 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.pl_id);
105 --115.10 replaced original exists checking with equality check and exception
106 if g_cache_pl_rec(l_index).pl_id = p_rec.pl_id then
107 -- do nothing, cache entry already exists
108 null;
109 else
110 --
111 -- Loop through the hash using the jump routine to check further
112 -- indexes
113 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
114 --
115 l_index := l_index+g_hash_jump;
116 while g_cache_pl_rec(l_index).pl_id <> p_rec.pl_id loop
117 --
118 l_index := l_index+g_hash_jump;
119
120 end loop;
121 --
122 end if;
123 --
124 -- hr_utility.set_location('Leaving '||l_proc,10);
125 --
126 exception when NO_DATA_FOUND then
127 -- set cache entry at current index location
128 g_cache_pl_rec(l_index):=p_rec;
129 --
130 end set_object;
131 --
132 procedure set_object(p_rec in out nocopy ben_oipl_f%rowtype) is
133 --
134 l_proc varchar2(80) := g_package||'set_object oipl ';
135 l_index pls_integer;
136 --l_not_hash_found boolean;
137 --
138 begin
139 --
140 -- 1) get hash index
141 -- 2) If hash index is not used use hash index
142 -- 3) If hash index is used and correct then do nothing
143 -- 4) If hash index is used and not correct then try next hash index
144 --
145 -- Get hashed index value
146 --
147 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.oipl_id);
148 --115.10 replaced original exists checking with equality check and exception
149 if g_cache_oipl_rec(l_index).oipl_id = p_rec.oipl_id then
150 -- do nothing, cache entry already exists
151 null;
152 else
153 --
154 -- Loop through the hash using the jump routine to check further
155 -- indexes
156 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
157 --
158 l_index := l_index+g_hash_jump;
159 while g_cache_oipl_rec(l_index).oipl_id <> p_rec.oipl_id loop
160 --
161 l_index := l_index+g_hash_jump;
162
163 end loop;
164 --
165 end if;
166 --
167 -- hr_utility.set_location('Leaving '||l_proc,10);
168 --
169 exception when NO_DATA_FOUND then
170 -- set cache entry at current index location
171 g_cache_oipl_rec(l_index):=p_rec;
172 --
173 end set_object;
174 --
175 procedure set_object(p_rec in out nocopy ben_plip_f%rowtype) is
176 --
177 l_proc varchar2(80) := g_package||'set_object plip ';
178 l_index pls_integer;
179 l_not_hash_found boolean;
180 --
181 begin
182 --
183 -- 1) get hash index
184 -- 2) If hash index is not used use hash index
185 -- 3) If hash index is used and correct then do nothing
186 -- 4) If hash index is used and not correct then try next hash index
187 --
188 -- Get hashed index value
189 --
190 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.plip_id);
191 --115.10 replaced original exists checking with equality check and exception
192 if g_cache_plip_rec(l_index).plip_id = p_rec.plip_id then
193 -- do nothing, cache entry already exists
194 null;
195 else
196 --
197 -- Loop through the hash using the jump routine to check further
198 -- indexes
199 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
200 --
201 l_index := l_index+g_hash_jump;
202 while g_cache_plip_rec(l_index).plip_id <> p_rec.plip_id loop
203 --
204 l_index := l_index+g_hash_jump;
205
206 end loop;
207 --
208 end if;
209 --
210 -- hr_utility.set_location('Leaving '||l_proc,10);
211 --
212 exception when NO_DATA_FOUND then
213 -- set cache entry at current index location
214 g_cache_plip_rec(l_index):=p_rec;
215 --
216 end set_object;
217 --
218 procedure set_object(p_rec in out nocopy ben_ptip_f%rowtype) is
219 --
220 l_proc varchar2(80) := g_package||'set_object ptip';
221 l_index pls_integer;
222 --l_not_hash_found boolean;
223 --
224 begin
225 --
226 -- hr_utility.set_location('Entering '||l_proc,10);
227 --
228 -- 1) get hash index
229 -- 2) If hash index is not used use hash index
230 -- 3) If hash index is used and correct then do nothing
231 -- 4) If hash index is used and not correct then try next hash index
232 --
233 -- Get hashed index value
234 --
235 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.ptip_id);
236 --115.10 replaced original exists checking with equality check and exception
237 if g_cache_ptip_rec(l_index).ptip_id = p_rec.ptip_id then
238 -- do nothing, cache entry already exists
239 null;
240 else
241 --
242 -- Loop through the hash using the jump routine to check further
243 -- indexes
244 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
245 --
246 l_index := l_index+g_hash_jump;
247 while g_cache_ptip_rec(l_index).ptip_id <> p_rec.ptip_id loop
248 --
249 l_index := l_index+g_hash_jump;
250
251 end loop;
252 --
253 end if;
254 --
255 -- hr_utility.set_location('Leaving '||l_proc,10);
256 --
257 exception when NO_DATA_FOUND then
258 -- set cache entry at current index location
259 g_cache_ptip_rec(l_index):=p_rec;
260 --
261 end set_object;
262 --
263 procedure set_object(p_rec in out nocopy ben_opt_f%rowtype) is
264 --
265 l_proc varchar2(80) := g_package||'set_object opt ';
266 l_index pls_integer;
267 --l_not_hash_found boolean;
268 --
269 begin
270 --
271 -- 1) get hash index
272 -- 2) If hash index is not used use hash index
273 -- 3) If hash index is used and correct then do nothing
274 -- 4) If hash index is used and not correct then try next hash index
275 --
276 -- Get hashed index value
277 --
278 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.opt_id);
279 --
280 --115.10 replaced original exists checking with equality check and exception
281 if g_cache_opt_rec(l_index).opt_id = p_rec.opt_id then
282 -- do nothing, cache entry already exists
283 null;
284 else
285 --
286 -- Loop through the hash using the jump routine to check further
287 -- indexes
288 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
289 --
290 l_index := l_index+g_hash_jump;
291 while g_cache_opt_rec(l_index).opt_id <> p_rec.opt_id loop
292 --
293 l_index := l_index+g_hash_jump;
294
295 end loop;
296 --
297 end if;
298 --
299 -- hr_utility.set_location('Leaving '||l_proc,10);
300 --
301 exception when NO_DATA_FOUND then
302 -- set cache entry at current index location
303 g_cache_opt_rec(l_index):=p_rec;
304 --
305 end set_object;
306 --
307 procedure set_object(p_rec in out nocopy ben_oiplip_f%rowtype) is
308 --
309 l_proc varchar2(80) := g_package||'set_object oiplip ';
310 l_index pls_integer;
311 --l_not_hash_found boolean;
312 --
313 begin
314 --
315 -- 1) get hash index
316 -- 2) If hash index is not used use hash index
317 -- 3) If hash index is used and correct then do nothing
318 -- 4) If hash index is used and not correct then try next hash index
319 --
320 -- Get hashed index value
321 --
322 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.oiplip_id);
323 --115.10 replaced original exists checking with equality check and exception
324 if g_cache_oiplip_rec(l_index).oiplip_id = p_rec.oiplip_id then
325 -- do nothing, cache entry already exists
326 null;
327 else
328 --
329 -- Loop through the hash using the jump routine to check further
330 -- indexes
331 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
332 --
333 l_index := l_index+g_hash_jump;
334 while g_cache_oiplip_rec(l_index).oiplip_id <> p_rec.oiplip_id loop
335 --
336 l_index := l_index+g_hash_jump;
337
338 end loop;
339 --
340 end if;
341 --
342 -- hr_utility.set_location('Leaving '||l_proc,10);
343 --
344 exception when NO_DATA_FOUND then
345 -- set cache entry at current index location
346 g_cache_oiplip_rec(l_index):=p_rec;
347 --
348 end set_object;
349 --
350 -- Set object alternate route routines
351 --
352 procedure set_object(p_pl_id in number,
353 p_business_group_id in number,
354 p_effective_date in date,
355 p_rec in out NOCOPY ben_pl_f%rowtype) is
356 --
357 l_proc varchar2(80) := g_package||'set_object alt pln';
358 --
359 cursor c1 is
360 select pln.*
361 from ben_pl_f pln
362 where pln.pl_id = p_pl_id
363 and pln.business_group_id = p_business_group_id
364 and p_effective_date
365 between pln.effective_start_date
366 and pln.effective_end_date;
367 --
368 --l_rec ben_pl_f%rowtype;
369 --
370 begin
371 --
372 -- hr_utility.set_location('Entering '||l_proc,10);
373 --
374 -- 1) Get record from database.
375 -- 2) If record not found then raise error.
376 -- 3) Pass record to set_object routine.
377 --
378 open c1;
379 --
380 fetch c1 into p_rec;
381 if c1%notfound then
382 --
383 close c1;
384 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
385 fnd_message.set_token('PROC',l_proc);
386 fnd_message.set_token('PGM',null);
387 fnd_message.set_token('PLN',p_pl_id);
388 fnd_message.set_token('OIPL',null);
389 fnd_message.set_token('PLIP',null);
390 fnd_message.set_token('PTIP',null);
391 fnd_message.set_token('OPT',null);
392 fnd_message.set_token('OIPLIP',null);
393 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
394 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
395 fnd_message.raise_error;
396 --
397 end if;
398 --
399 close c1;
400 --
401 set_object(p_rec => p_rec);
402 --
403 -- p_rec := l_rec;
404 --
405 -- hr_utility.set_location('Leaving '||l_proc,10);
406 --
407 end set_object;
408 --
409 procedure set_object(p_pgm_id in number,
410 p_business_group_id in number,
411 p_effective_date in date,
412 p_rec in out nocopy ben_pgm_f%rowtype) is
413 --
417 select pgm.*
414 l_proc varchar2(80) := g_package||'set_object alt pgm';
415 --
416 cursor c1 is
418 from ben_pgm_f pgm
419 where pgm.pgm_id = p_pgm_id
420 and pgm.business_group_id = p_business_group_id
421 and p_effective_date
422 between pgm.effective_start_date
423 and pgm.effective_end_date;
424 --
425 --l_rec ben_pgm_f%rowtype;
426 --
427 begin
428 --
429 -- hr_utility.set_location('Entering '||l_proc,10);
430 --
431 -- 1) Get record from database.
432 -- 2) If record not found then raise error.
433 -- 3) Pass record to set_object routine.
434 --
435 open c1;
436 --
437 fetch c1 into p_rec;
438 if c1%notfound then
439 --
440 close c1;
441 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
442 fnd_message.set_token('PROC',l_proc);
443 fnd_message.set_token('PGM',p_pgm_id);
444 fnd_message.set_token('PLN',null);
445 fnd_message.set_token('OIPL',null);
446 fnd_message.set_token('PLIP',null);
447 fnd_message.set_token('PTIP',null);
448 fnd_message.set_token('OPT',null);
449 fnd_message.set_token('OIPLIP',null);
450 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
451 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
452 fnd_message.raise_error;
453 --
454 end if;
455 --
456 close c1;
457 --
458 set_object(p_rec => p_rec);
459 --
460 --p_rec := l_rec;
461 --
462 -- hr_utility.set_location('Leaving '||l_proc,10);
463 --
464 end set_object;
465 --
466 procedure set_object(p_oipl_id in number,
467 p_business_group_id in number,
468 p_effective_date in date,
469 p_rec in out NOCOPY ben_oipl_f%rowtype) is
470 --
471 l_proc varchar2(80) := g_package||'set_object alt oipl';
472 --
473 cursor c1 is
474 select cop.*
475 from ben_oipl_f cop
476 where cop.oipl_id = p_oipl_id
477 and cop.business_group_id = p_business_group_id
478 and p_effective_date
479 between cop.effective_start_date
480 and cop.effective_end_date;
481 --
482 --l_rec ben_oipl_f%rowtype;
483 --
484 begin
485 --
486 -- hr_utility.set_location('Entering '||l_proc,10);
487 --
488 -- 1) Get record from database.
489 -- 2) If record not found then raise error.
490 -- 3) Pass record to set_object routine.
491 --
492 open c1;
493 --
494 fetch c1 into p_rec;
495 if c1%notfound then
496 --
497 close c1;
498 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
499 fnd_message.set_token('PROC',l_proc);
500 fnd_message.set_token('PGM',null);
501 fnd_message.set_token('PLN',null);
502 fnd_message.set_token('OIPL',p_oipl_id);
503 fnd_message.set_token('PLIP',null);
504 fnd_message.set_token('PTIP',null);
505 fnd_message.set_token('OPT',null);
506 fnd_message.set_token('OIPLIP',null);
507 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
508 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
509 fnd_message.raise_error;
510 --
511 end if;
512 --
513 close c1;
514 --
515 set_object(p_rec => p_rec);
516 --
517 --p_rec := l_rec;
518 --
519 -- hr_utility.set_location('Leaving '||l_proc,10);
520 --
521 end set_object;
522 --
523 procedure set_object(p_plip_id in number,
524 p_business_group_id in number,
525 p_effective_date in date,
526 p_rec in out NOCOPY ben_plip_f%rowtype) is
527 --
528 l_proc varchar2(80) := g_package||'set_object alt plip ';
529 --
530 cursor c1 is
531 select cpp.*
532 from ben_plip_f cpp
533 where cpp.plip_id = p_plip_id
534 and cpp.business_group_id = p_business_group_id
535 and p_effective_date
536 between cpp.effective_start_date
537 and cpp.effective_end_date;
538 --
539 --l_rec ben_plip_f%rowtype;
540 --
541 begin
542 --
543 -- hr_utility.set_location('Entering '||l_proc,10);
544 --
545 -- 1) Get record from database.
546 -- 2) If record not found then raise error.
547 -- 3) Pass record to set_object routine.
548 --
549 open c1;
550 --
551 fetch c1 into p_rec;
552 if c1%notfound then
553 --
554 close c1;
555 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
556 fnd_message.set_token('PROC',l_proc);
557 fnd_message.set_token('PGM',null);
558 fnd_message.set_token('PLN',null);
559 fnd_message.set_token('OIPL',null);
560 fnd_message.set_token('PLIP',p_plip_id);
561 fnd_message.set_token('PTIP',null);
562 fnd_message.set_token('OPT',null);
563 fnd_message.set_token('OIPLIP',null);
567 --
564 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
565 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
566 fnd_message.raise_error;
568 end if;
569 --
570 close c1;
571 --
572 set_object(p_rec => p_rec);
573 --
574 --p_rec := l_rec;
575 --
576 -- hr_utility.set_location('Leaving '||l_proc,10);
577 --
578 end set_object;
579 --
580 procedure set_object(p_ptip_id in number,
581 p_business_group_id in number,
582 p_effective_date in date,
583 p_rec in out NOCOPY ben_ptip_f%rowtype) is
584 --
585 l_proc varchar2(80) := g_package||'set_object ptip';
586 --
587 cursor c1 is
588 select ctp.*
589 from ben_ptip_f ctp
590 where ctp.ptip_id = p_ptip_id
591 and ctp.business_group_id = p_business_group_id
592 and p_effective_date
593 between ctp.effective_start_date
594 and ctp.effective_end_date;
595 --
596 l_rec ben_ptip_f%rowtype;
597 --
598 begin
599 --
600 -- hr_utility.set_location('Entering '||l_proc,10);
601 --
602 -- 1) Get record from database.
603 -- 2) If record not found then raise error.
604 -- 3) Pass record to set_object routine.
605 --
606 open c1;
607 --
608 fetch c1 into p_rec;
609 if c1%notfound then
610 --
611 close c1;
612 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
613 fnd_message.set_token('PROC',l_proc);
614 fnd_message.set_token('PGM',null);
615 fnd_message.set_token('PLN',null);
616 fnd_message.set_token('OIPL',null);
617 fnd_message.set_token('PLIP',null);
618 fnd_message.set_token('PTIP',p_ptip_id);
619 fnd_message.set_token('OPT',null);
620 fnd_message.set_token('OIPLIP',null);
621 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
622 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
623 fnd_message.raise_error;
624 --
625 end if;
626 --
627 close c1;
628 --
629 set_object(p_rec => p_rec);
630 --
631 --p_rec := l_rec;
632 --
633 -- hr_utility.set_location('Leaving '||l_proc,10);
634 --
635 end set_object;
636 --
637 procedure set_object(p_opt_id in number,
638 p_business_group_id in number,
639 p_effective_date in date,
640 p_rec in out nocopy ben_opt_f%rowtype) is
641 --
642 l_proc varchar2(80) := g_package||'set_object alt opt';
643 --
644 cursor c1 is
645 select opt.*
646 from ben_opt_f opt
647 where opt.opt_id = p_opt_id
648 and opt.business_group_id = p_business_group_id
649 and p_effective_date
650 between opt.effective_start_date
651 and opt.effective_end_date;
652 --
653 --l_rec ben_opt_f%rowtype;
654 --
655 begin
656 --
657 -- 1) Get record from database.
658 -- 2) If record not found then raise error.
659 -- 3) Pass record to set_object routine.
660 --
661 open c1;
662 --
663 fetch c1 into p_rec;
664 if c1%notfound then
665 --
666 close c1;
667 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
668 fnd_message.set_token('PROC',l_proc);
669 fnd_message.set_token('PGM',null);
670 fnd_message.set_token('PLN',null);
671 fnd_message.set_token('OIPL',null);
672 fnd_message.set_token('PLIP',null);
673 fnd_message.set_token('PTIP',null);
674 fnd_message.set_token('OPT',p_opt_id);
675 fnd_message.set_token('OIPLIP',null);
676 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
677 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
678 fnd_message.raise_error;
679 --
680 end if;
681 --
682 close c1;
683 --
684 set_object(p_rec => p_rec);
685 --
686 --p_rec := l_rec;
687 --
688 end set_object;
689 --
690 procedure set_object(p_oiplip_id in number,
691 p_business_group_id in number,
692 p_effective_date in date,
693 p_rec in out NOCOPY ben_oiplip_f%rowtype) is
694 --
695 l_proc varchar2(80) := g_package||'set_object alt oiplip';
696 --
697 cursor c1 is
698 select opp.*
699 from ben_oiplip_f opp
700 where opp.oiplip_id = p_oiplip_id
701 and opp.business_group_id = p_business_group_id
702 and p_effective_date
703 between opp.effective_start_date
704 and opp.effective_end_date;
705 --
706 --l_rec ben_oiplip_f%rowtype;
707 --
708 begin
709 --
710 -- 1) Get record from database.
711 -- 2) If record not found then raise error.
712 -- 3) Pass record to set_object routine.
713 --
714 open c1;
715 --
716 fetch c1 into p_rec;
720 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
717 if c1%notfound then
718 --
719 close c1;
721 fnd_message.set_token('PROC',l_proc);
722 fnd_message.set_token('PGM',null);
723 fnd_message.set_token('PLN',null);
724 fnd_message.set_token('OIPL',null);
725 fnd_message.set_token('PLIP',null);
726 fnd_message.set_token('PTIP',null);
727 fnd_message.set_token('OPT',null);
728 fnd_message.set_token('OIPLIP',p_oiplip_id);
729 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
730 fnd_message.set_token('BUSINESS_GROUP',p_business_group_id);
731 fnd_message.raise_error;
732 --
733 end if;
734 --
735 close c1;
736 --
737 set_object(p_rec => p_rec);
738 --
739 --p_rec := l_rec;
740 --
741 end set_object;
742 --
743 -- Get object routines
744 --
745 procedure get_object(p_pgm_id in number,
746 p_rec in out NOCOPY ben_pgm_f%rowtype) is
747 --
748 l_proc varchar2(80) := g_package||'get_object pgm';
749 l_index pls_integer;
750 --l_not_hash_found boolean;
751 l_env ben_env_object.g_global_env_rec_type;
752 --l_rec ben_pgm_f%rowtype;
753 --
754 begin
755 --
756 -- hr_utility.set_location('Entering '||l_proc,10);
757 --
758 if g_cache_last_pgm_rec.pgm_id = p_pgm_id then
759 --
760 p_rec := g_cache_last_pgm_rec;
761 return;
762 --
763 end if;
764 --
765 -- 1) Get hashed index
766 -- 2) If hashed index is correct pgm then return program
767 -- 3) If hashed index is not correct program then check next index
768 -- 4) Repest 3 until correct program found, if not found raise error.
769 --
770 -- Get hashed index value
771 --
772 l_index := ben_hash_utility.get_hashed_index(p_id => p_pgm_id);
773 --
774 if g_cache_pgm_rec(l_index).pgm_id = p_pgm_id then
775 --
776 g_cache_last_pgm_rec := g_cache_pgm_rec(l_index);
777 p_rec := g_cache_last_pgm_rec;
778 --
779 else
780 --
781 -- We need to loop through all the hashed indexes
782 -- if none exists at current index the NO_DATA_FOUND expection will fire
783 --
784 l_index := l_index+g_hash_jump;
785 while g_cache_pgm_rec(l_index).pgm_id <> p_pgm_id loop
786 --
787 l_index := l_index+g_hash_jump;
788 --
789 end loop;
790 --
791 g_cache_last_pgm_rec := g_cache_pgm_rec(l_index);
792 p_rec := g_cache_last_pgm_rec;
793 --
794 end if;
795 --
796 exception
797 --
798 when no_data_found then
799 --
800 ben_env_object.get(p_rec => l_env);
801 --
802 set_object(p_pgm_id => p_pgm_id,
803 p_business_group_id => l_env.business_group_id,
804 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
805 l_env.effective_date),
806 p_rec => p_rec);
807 --
808 g_cache_last_pgm_rec := p_rec;
809 --p_rec := l_rec;
810 --
811 end get_object;
812 --
813 procedure get_object(p_pl_id in number,
814 p_rec in out NOCOPY ben_pl_f%rowtype) is
815 --
816 l_proc varchar2(80) := g_package||'get_object pln';
817 l_index pls_integer;
818 --l_not_hash_found boolean;
819 l_env ben_env_object.g_global_env_rec_type;
820 --l_rec ben_pl_f%rowtype;
821 --
822 begin
823 --
824 -- hr_utility.set_location('Entering '||l_proc,10);
825 --
826 if g_cache_last_pl_rec.pl_id = p_pl_id then
827 --
828 p_rec := g_cache_last_pl_rec;
829 return;
830 --
831 end if;
832 --
833 -- 1) Get hashed index
834 -- 2) If hashed index is correct pgm then return program
835 -- 3) If hashed index is not correct program then check next index
836 -- 4) Repest 3 until correct program found, if not found raise error.
837 --
838 -- Get hashed index value
839 --
840 l_index := ben_hash_utility.get_hashed_index(p_id => p_pl_id);
841 --
842 if g_cache_pl_rec(l_index).pl_id = p_pl_id then
843 --
844 g_cache_last_pl_rec := g_cache_pl_rec(l_index);
845 p_rec := g_cache_last_pl_rec;
846 --
847 else
848 --
849 -- We need to loop through all the hashed indexes
850 -- if none exists at current index the NO_DATA_FOUND expection will fire
851 --
852 l_index := l_index+g_hash_jump;
853 while g_cache_pl_rec(l_index).pl_id <> p_pl_id loop
854 --
855 l_index := l_index+g_hash_jump;
856 --
857 end loop;
858 --
859 g_cache_last_pl_rec := g_cache_pl_rec(l_index);
860 p_rec := g_cache_last_pl_rec;
861 --
862 end if;
863 --
864 exception
865 --
866 when no_data_found then
867 --
868 ben_env_object.get(p_rec => l_env);
869 --
870 set_object(p_pl_id => p_pl_id ,
874 p_rec => p_rec);
871 p_business_group_id => l_env.business_group_id,
872 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
873 l_env.effective_date),
875 --
876 g_cache_last_pl_rec := p_rec;
877 --p_rec := l_rec;
878 --
879 -- hr_utility.set_location('NDF Leaving '||l_proc,10);
880 --
881 end get_object;
882 --
883 procedure get_object(p_oipl_id in number,
884 p_rec in out NOCOPY ben_oipl_f%rowtype) is
885 --
886 l_proc varchar2(80) := g_package||'get_object oipl';
887 l_index pls_integer;
888 --l_not_hash_found boolean;
889 l_env ben_env_object.g_global_env_rec_type;
890 --l_rec ben_oipl_f%rowtype;
891 --
892 begin
893 --
894 -- hr_utility.set_location('Entering '||l_proc,10);
895 --
896 if g_cache_last_oipl_rec.oipl_id = p_oipl_id then
897 --
898 p_rec := g_cache_last_oipl_rec;
899 return;
900 --
901 end if;
902 --
903 -- 1) Get hashed index
904 -- 2) If hashed index is correct pgm then return program
905 -- 3) If hashed index is not correct program then check next index
906 -- 4) Repest 3 until correct program found, if not found raise error.
907 --
908 -- Get hashed index value
909 --
910 l_index := ben_hash_utility.get_hashed_index(p_id => p_oipl_id);
911 --
912 if g_cache_oipl_rec(l_index).oipl_id = p_oipl_id then
913 --
914 g_cache_last_oipl_rec := g_cache_oipl_rec(l_index);
915 p_rec := g_cache_last_oipl_rec;
916 --
917 else
918 --
919 -- We need to loop through all the hashed indexes
920 -- if none exists at current index the NO_DATA_FOUND expection will fire
921 --
922 l_index := l_index+g_hash_jump;
923 while g_cache_oipl_rec(l_index).oipl_id <> p_oipl_id loop
924 --
925 l_index := l_index+g_hash_jump;
926 --
927 end loop;
928 --
929 g_cache_last_oipl_rec := g_cache_oipl_rec(l_index);
930 p_rec := g_cache_last_oipl_rec;
931 --
932 end if;
933 --
934 exception
935 --
936 when no_data_found then
937 --
938 ben_env_object.get(p_rec => l_env);
939 --
940 set_object(p_oipl_id => p_oipl_id,
941 p_business_group_id => l_env.business_group_id,
942 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
943 l_env.effective_date),
944 p_rec => p_rec);
945 --
946 g_cache_last_oipl_rec := p_rec;
947 --p_rec := l_rec;
948 --
949 end get_object;
950 --
951 procedure get_object(p_plip_id in number,
952 p_rec in out NOCOPY ben_plip_f%rowtype) is
953 --
954 l_proc varchar2(80) := g_package||'get_object plip';
955 l_index pls_integer;
956 --l_not_hash_found boolean;
957 l_env ben_env_object.g_global_env_rec_type;
958 --l_rec ben_plip_f%rowtype;
959 --
960 begin
961 --
962 -- hr_utility.set_location('Entering '||l_proc,10);
963 --
964 if g_cache_last_plip_rec.plip_id = p_plip_id then
965 --
966 p_rec := g_cache_last_plip_rec;
967 return;
968 --
969 end if;
970 --
971 -- 1) Get hashed index
972 -- 2) If hashed index is correct pgm then return program
973 -- 3) If hashed index is not correct program then check next index
974 -- 4) Repest 3 until correct program found, if not found raise error.
975 --
976 -- Get hashed index value
977 --
978 l_index := ben_hash_utility.get_hashed_index(p_id => p_plip_id);
979 --
980 if g_cache_plip_rec(l_index).plip_id = p_plip_id then
981 --
982 g_cache_last_plip_rec := g_cache_plip_rec(l_index);
983 p_rec := g_cache_last_plip_rec;
984 --
985 else
986 --
987 -- We need to loop through all the hashed indexes
988 -- if none exists at current index the NO_DATA_FOUND expection will fire
989 --
990 l_index := l_index+g_hash_jump;
991 while g_cache_plip_rec(l_index).plip_id <> p_plip_id loop
992 --
993 l_index := l_index+g_hash_jump;
994 --
995 end loop;
996 --
997 g_cache_last_plip_rec := g_cache_plip_rec(l_index);
998 p_rec := g_cache_last_plip_rec;
999 --
1000 end if;
1001 --
1002 exception
1003 --
1004 when no_data_found then
1005 --
1006 ben_env_object.get(p_rec => l_env);
1007 --
1008 set_object(p_plip_id => p_plip_id,
1009 p_business_group_id => l_env.business_group_id,
1010 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
1011 l_env.effective_date),
1012 p_rec => p_rec);
1013 --
1014 g_cache_last_plip_rec := p_rec;
1015 --p_rec := l_rec;
1016 --
1017 end get_object;
1018 --
1019 procedure get_object(p_ptip_id in number,
1023 l_index pls_integer;
1020 p_rec in out nocopy ben_ptip_f%rowtype) is
1021 --
1022 l_proc varchar2(80) := g_package||'get_object ptip';
1024 --l_not_hash_found boolean;
1025 l_env ben_env_object.g_global_env_rec_type;
1026 --l_rec ben_ptip_f%rowtype;
1027 --
1028 begin
1029 --
1030 -- hr_utility.set_location('Entering '||l_proc,10);
1031 --
1032 if g_cache_last_ptip_rec.ptip_id = p_ptip_id then
1033 --
1034 p_rec := g_cache_last_ptip_rec;
1035 return;
1036 --
1037 end if;
1038 --
1039 -- 1) Get hashed index
1040 -- 2) If hashed index is correct pgm then return program
1041 -- 3) If hashed index is not correct program then check next index
1042 -- 4) Repest 3 until correct program found, if not found raise error.
1043 --
1044 -- Get hashed index value
1045 --
1046 l_index := ben_hash_utility.get_hashed_index(p_id => p_ptip_id);
1047 --
1048 if g_cache_ptip_rec(l_index).ptip_id = p_ptip_id then
1049 --
1050 g_cache_last_ptip_rec := g_cache_ptip_rec(l_index);
1051 p_rec := g_cache_last_ptip_rec;
1052 --
1053 else
1054 --
1055 -- We need to loop through all the hashed indexes
1056 -- if none exists at current index the NO_DATA_FOUND expection will fire
1057 --
1058 l_index := l_index+g_hash_jump;
1059 while g_cache_ptip_rec(l_index).ptip_id <> p_ptip_id loop
1060 --
1061 l_index := l_index+g_hash_jump;
1062 --
1063 end loop;
1064 --
1065 g_cache_last_ptip_rec := g_cache_ptip_rec(l_index);
1066 p_rec := g_cache_last_ptip_rec;
1067 --
1068 end if;
1069 --
1070 exception
1071 --
1072 when no_data_found then
1073 --
1074 ben_env_object.get(p_rec => l_env);
1075 --
1076 set_object(p_ptip_id => p_ptip_id,
1077 p_business_group_id => l_env.business_group_id,
1078 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
1079 l_env.effective_date),
1080 p_rec => p_rec);
1081 --
1082 g_cache_last_ptip_rec := p_rec;
1083 --p_rec := l_rec;
1084 --
1085 end get_object;
1086 --
1087 procedure get_object(p_opt_id in number,
1088 p_rec in out NOCOPY ben_opt_f%rowtype) is
1089 --
1090 l_proc varchar2(80) := g_package||'get_object opt';
1091 l_index pls_integer;
1092 --l_not_hash_found boolean;
1093 l_env ben_env_object.g_global_env_rec_type;
1094 --l_rec ben_opt_f%rowtype;
1095 --
1096 begin
1097 --
1098 -- hr_utility.set_location('Entering '||l_proc,10);
1099 --
1100 if g_cache_last_opt_rec.opt_id = p_opt_id then
1101 --
1102 p_rec := g_cache_last_opt_rec;
1103 return;
1104 --
1105 end if;
1106 --
1107 -- 1) Get hashed index
1108 -- 2) If hashed index is correct pgm then return program
1109 -- 3) If hashed index is not correct program then check next index
1110 -- 4) Repest 3 until correct program found, if not found raise error.
1111 --
1112 -- Get hashed index value
1113 --
1114 l_index := ben_hash_utility.get_hashed_index(p_id => p_opt_id);
1115 --
1116 if g_cache_opt_rec(l_index).opt_id = p_opt_id then
1117 --
1118 g_cache_last_opt_rec := g_cache_opt_rec(l_index);
1119 p_rec := g_cache_last_opt_rec;
1120 --
1121 else
1122 --
1123 -- We need to loop through all the hashed indexes
1124 -- if none exists at current index the NO_DATA_FOUND expection will fire
1125 --
1126 l_index := l_index+g_hash_jump;
1127 while g_cache_opt_rec(l_index).opt_id <> p_opt_id loop
1128 --
1129 l_index := l_index+g_hash_jump;
1130 --
1131 end loop;
1132 --
1133 g_cache_last_opt_rec := g_cache_opt_rec(l_index);
1134 p_rec := g_cache_last_opt_rec;
1135 --
1136 end if;
1137 --
1138 exception
1139 --
1140 when no_data_found then
1141 --
1142 ben_env_object.get(p_rec => l_env);
1143 --
1144 set_object(p_opt_id => p_opt_id,
1145 p_business_group_id => l_env.business_group_id,
1146 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
1147 l_env.effective_date),
1148 p_rec => p_rec);
1149 --
1150 --115.10 g_cache_last_opt_rec := g_cache_opt_rec(l_index); should read as follows
1151 g_cache_last_opt_rec := p_rec;
1152 --p_rec := l_rec;
1153 --
1154 end get_object;
1155 --
1156 procedure get_object(p_oiplip_id in number,
1157 p_rec in out NOCOPY ben_oiplip_f%rowtype) is
1158 --
1159 l_proc varchar2(80) := g_package||'get_object oiplip';
1160 l_index pls_integer;
1161 --l_not_hash_found boolean;
1162 l_env ben_env_object.g_global_env_rec_type;
1163 --l_rec ben_oiplip_f%rowtype;
1164 --
1165 begin
1166 --
1167 -- hr_utility.set_location('Entering '||l_proc,10);
1168 --
1172 return;
1169 if g_cache_last_oiplip_rec.oiplip_id = p_oiplip_id then
1170 --
1171 p_rec := g_cache_last_oiplip_rec;
1173 --
1174 end if;
1175 --
1176 -- 1) Get hashed index
1177 -- 2) If hashed index is correct pgm then return program
1178 -- 3) If hashed index is not correct program then check next index
1179 -- 4) Repest 3 until correct program found, if not found raise error.
1180 --
1181 -- Get hashed index value
1182 --
1183 l_index := ben_hash_utility.get_hashed_index(p_id => p_oiplip_id);
1184 --
1185 if g_cache_oiplip_rec(l_index).oiplip_id = p_oiplip_id then
1186 --
1187 g_cache_last_oiplip_rec := g_cache_oiplip_rec(l_index);
1188 p_rec := g_cache_last_oiplip_rec;
1189 --
1190 else
1191 --
1192 -- We need to loop through all the hashed indexes
1193 -- if none exists at current index the NO_DATA_FOUND expection will fire
1194 --
1195 l_index := l_index+g_hash_jump;
1196 while g_cache_oiplip_rec(l_index).oiplip_id <> p_oiplip_id loop
1197 --
1198 l_index := l_index+g_hash_jump;
1199 --
1200 end loop;
1201 --
1202 g_cache_last_oiplip_rec := g_cache_oiplip_rec(l_index);
1203 p_rec := g_cache_last_oiplip_rec;
1204 --
1205 end if;
1206 --
1207 exception
1208 --
1209 when no_data_found then
1210 --
1211 ben_env_object.get(p_rec => l_env);
1212 --
1213 set_object(p_oiplip_id => p_oiplip_id,
1214 p_business_group_id => l_env.business_group_id,
1215 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
1216 l_env.effective_date),
1217 p_rec => p_rec);
1218 --
1219 --115.10 g_cache_last_oiplip_rec := g_cache_oiplip_rec(l_index); should read as follows
1220 g_cache_last_oiplip_rec := p_rec;
1221 --p_rec := l_rec;
1222 --
1223 end get_object;
1224 --
1225 procedure get_object_set_cobra
1226 (p_pgm_id in number,
1227 p_only_pls_subj_cobra_flag in varchar2,
1228 p_rec in out NOCOPY g_cache_pl_rec_table) is
1229 --
1230 l_proc varchar2(80) := g_package||'get_object_set_cobra';
1231 l_index pls_integer;
1232 --l_not_hash_found boolean;
1233 l_results_table g_cache_pl_rec_table;
1234 l_rec ben_cache.g_cache_lookup;
1235 l_num_recs number;
1236 l_env ben_env_object.g_global_env_rec_type;
1237 l_business_group_id number;
1238 l_lf_evt_ocrd_dt date;
1239 --
1240 cursor c1 is -- changed the sql for performance bug 3662774
1241 select distinct pln.*
1242 from ben_pl_f pln,
1243 ben_plip_f cpp,
1244 ben_pl_regn_f prg,
1245 ben_regn_f reg
1246 where pln.pl_id = cpp.pl_id
1247 and pln.business_group_id = l_env.business_group_id
1248 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1249 between pln.effective_start_date
1250 and pln.effective_end_date
1251 and cpp.business_group_id = pln.business_group_id
1252 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1253 between cpp.effective_start_date
1254 and cpp.effective_end_date
1255 and cpp.pgm_id = p_pgm_id
1256 and prg.pl_id = pln.pl_id
1257 and prg.business_group_id = pln.business_group_id
1258 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1259 between prg.effective_start_date
1260 and prg.effective_end_date
1261 and reg.regn_id = prg.regn_id
1262 and reg.business_group_id = prg.business_group_id
1263 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1264 between reg.effective_start_date
1265 and reg.effective_end_date
1266 and reg.name = decode(p_only_pls_subj_cobra_flag,
1267 'Y',
1268 'COBRA',
1269 reg.name)
1270 and exists
1271 ( select 1
1272 from ben_elig_per_f epo
1273 ,ben_per_in_ler pil
1274 where epo.pgm_id = p_pgm_id
1275 and epo.pl_id = l_env.pl_id
1276 and epo.business_group_id = l_env.business_group_id
1277 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1278 between epo.effective_start_date
1279 and epo.effective_end_date
1280 and epo.elig_flag = 'Y'
1281 and pil.per_in_ler_id(+)=epo.per_in_ler_id
1282 and pil.business_group_id(+)=epo.business_group_id+0
1283 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
1284 or pil.per_in_ler_stat_cd is null -- outer join condition
1285 )
1286 )
1287 ;
1288
1289
1290
1291 /* select pln.*
1292 from ben_pl_f pln,
1293 ben_plip_f cpp,
1294 ben_pl_regn_f prg,
1295 ben_regn_f reg,
1296 ben_elig_per_f epo
1297 , ben_per_in_ler pil
1298 where pln.pl_id = cpp.pl_id
1302 and pln.effective_end_date
1299 and pln.business_group_id = l_env.business_group_id
1300 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1301 between pln.effective_start_date
1303 and cpp.business_group_id = pln.business_group_id
1304 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1305 between cpp.effective_start_date
1306 and cpp.effective_end_date
1307 and cpp.pgm_id = p_pgm_id
1308 and prg.pl_id = pln.pl_id
1309 and prg.business_group_id = pln.business_group_id
1310 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1311 between prg.effective_start_date
1312 and prg.effective_end_date
1313 and reg.regn_id = prg.regn_id
1314 and reg.business_group_id = prg.business_group_id
1315 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1316 between reg.effective_start_date
1317 and reg.effective_end_date
1318 and epo.pgm_id = p_pgm_id
1319 and epo.pl_id = l_env.pl_id
1320 and epo.business_group_id = l_env.business_group_id
1321 and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
1322 between epo.effective_start_date
1323 and epo.effective_end_date
1324 and epo.elig_flag = 'Y'
1325 and reg.name = decode(p_only_pls_subj_cobra_flag,
1326 'Y',
1327 'COBRA',
1328 reg.name)
1329 and pil.per_in_ler_id(+)=epo.per_in_ler_id
1330 and pil.business_group_id(+)=epo.business_group_id+0
1331 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
1332 or pil.per_in_ler_stat_cd is null -- outer join condition
1333 )
1334 ;
1335 */
1336 --
1337 --l_c1 c1%rowtype;
1338 --
1339 begin
1340 --
1341 -- hr_utility.set_location('Entering '||l_proc,10);
1342 --
1343 -- Grab environment information
1344 --
1345 ben_env_object.get(p_rec => l_env);
1346 --
1347 -- This is a special case as we are attempting to get all the plans in
1348 -- program for a COBRA program . We need to only hash the PGM_ID as the
1349 -- plans will be stored in an consecutive indexed table.
1350 -- The PGM lookup table merely stores the start and stop locations of
1351 -- the detail table and also whether any details records actualy exist.
1352 --
1353 -- 1) Get hashed index
1354 -- 2) If hashed index is correct program then return detail records
1355 -- 3) If hashed index is not correct program then check next index
1356 -- 4) Repeat 3 until correct program found or empty cell found.
1357 --
1358 -- Get hashed index value
1359 --
1360 l_index := ben_hash_utility.get_hashed_index(p_id => p_pgm_id);
1361 --
1362 if g_cache_pgm_cobra_lookup(l_index).id = p_pgm_id then
1363 --
1364 l_rec := g_cache_pgm_cobra_lookup(l_index);
1365 --
1366 else
1367 --
1368 -- We need to loop through all the hashed indexes
1369 -- if none exists at current index the NO_DATA_FOUND expection will fire
1370 --
1371 l_index := l_index+g_hash_jump;
1372 while g_cache_pgm_cobra_lookup(l_index).id <> p_pgm_id loop
1373 --
1374 l_index := l_index+g_hash_jump;
1375 --
1376 end loop;
1377 --
1378 l_rec := g_cache_pgm_cobra_lookup(l_index);
1379 --
1380 end if;
1381 --
1382 --
1383 -- Now fill the details table with the required consecutive rows as
1384 -- stated by the lookup table
1385 --
1386 if l_rec.v2value_1 = 'N' then
1387 --
1388 -- There are no records in the detail table so therefore
1389 -- return an empty table of records.
1390 --
1391 p_rec := l_results_table;
1392 --
1393 else
1394 --
1395 -- We have to loop through all the required records of the
1396 -- detail structure and return the records.
1397 --
1398 for l_count in l_rec.starttorele_num..l_rec.endtorele_num loop
1399 --
1400 -- Load records into l_results_table cache
1401 --
1402 p_rec(p_rec.count+1) := g_cache_pgm_cobra_rec(l_count);
1403 --
1404 end loop;
1405 --
1406 --
1407 end if;
1408 --
1409 -- hr_utility.set_location('Leaving '||l_proc,10);
1410 --
1411 exception
1412 --
1413 when no_data_found then
1414 --
1415 g_cache_pgm_cobra_lookup(l_index).id := p_pgm_id;
1416 g_cache_pgm_cobra_lookup(l_index).fk_id := l_business_group_id;
1417 g_cache_pgm_cobra_lookup(l_index).v2value_1 := 'N';
1418 --
1419 -- We need to force the cache of the cobra plans records
1420 --
1421 open c1;
1422 --
1423 loop
1424 --
1425 fetch c1 into p_rec(p_rec.count+1);
1426 exit when c1%notfound;
1427 --
1428 -- Set up the cobra lookup table
1429 --
1430 if g_cache_pgm_cobra_lookup(l_index).v2value_1 = 'N' then
1431 --
1432 g_cache_pgm_cobra_lookup(l_index).v2value_1 := 'Y';
1433 g_cache_pgm_cobra_lookup(l_index).starttorele_num :=
1434 g_cache_pgm_cobra_rec.count+1;
1435 --
1436 end if;
1437 --
1441 g_cache_pgm_cobra_rec(g_cache_pgm_cobra_rec.count+1) := p_rec(p_rec.count);
1438 -- Read the cursor values into the cobra details table
1439 -- additionally copy them to the results table
1440 --
1442 --
1443 end loop;
1444 --
1445 close c1;
1446 --
1447 if g_cache_pgm_cobra_lookup(l_index).starttorele_num is not null then
1448 --
1449 g_cache_pgm_cobra_lookup(l_index).endtorele_num :=
1450 g_cache_pgm_cobra_rec.count;
1451 --
1452 end if;
1453 --
1454 --
1455 end get_object_set_cobra;
1456 --
1457 procedure clear_down_cache is
1458 --
1459 l_opt_rec ben_opt_f%rowtype;
1460 l_oiplip_rec ben_oiplip_f%rowtype;
1461 l_pgm_rec ben_pgm_f%rowtype;
1462 l_pl_rec ben_pl_f%rowtype;
1463 l_plip_rec ben_plip_f%rowtype;
1464 l_ptip_rec ben_ptip_f%rowtype;
1465 l_oipl_rec ben_oipl_f%rowtype;
1466 --
1467 begin
1468 --
1469 g_cache_pgm_rec.delete;
1470 g_cache_pl_rec.delete;
1471 g_cache_oipl_rec.delete;
1472 g_cache_plip_rec.delete;
1473 g_cache_ptip_rec.delete;
1474 g_cache_opt_rec.delete;
1475 g_cache_oiplip_rec.delete;
1476 g_cache_last_pgm_rec := l_pgm_rec;
1477 g_cache_last_pl_rec := l_pl_rec;
1478 g_cache_last_oipl_rec := l_oipl_rec;
1479 g_cache_last_plip_rec := l_plip_rec;
1480 g_cache_last_ptip_rec := l_ptip_rec;
1481 g_cache_last_opt_rec := l_opt_rec;
1482 g_cache_last_oiplip_rec := l_oiplip_rec;
1483 g_cache_pgm_cobra_lookup.delete;
1484 g_cache_pgm_cobra_rec.delete;
1485 --
1486 end clear_down_cache;
1487 --
1488 end ben_comp_object;
[Home] [Help]