1 package body ben_person_object as
2 /* $Header: benperde.pkb 120.0.12000000.2 2007/09/14 14:30:08 rtagarra noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Person Object Caching Routine
13 Purpose
14 This package is used to return person object information.
15 History
16 Version Date Who What?
17 --------- --------- ---------- --------------------------------------------
18 115.0 11-Jun-99 gperry Created(arcsed in by bbulusu)
19 115.1 16-Jun-99 gperry Added use of person date info structure so
20 we can cache a persons minimum effective
21 start date and assignment minimum effective
22 start date.
23 Added use of a person fte info structure so
24 we can cache FTE data.
25 Added cache structure to store active
26 life event.
27 Added cache structure to store persons
28 benefits balance.
29 115.2 23-Jun-99 gperry Added assignment extra info cache.
30 115.3 24-Jun-99 gperry Added contact cache.
31 115.4 24-Jun-99 gperry Added new caches to clear_down_cache proc.
32 115.5 12-Jul-99 mhoyes - Removed + 0s from all cursors.
33 - Modified overloaded trace messages.
34 115.6 05-Aug-99 gperry Added last cached record logic.
35 115.7 09-Aug-99 gperry Added new cache routines.
36 assignment_status_types
37 soft_coding_keyflex
38 person_type_info
39 115.8 12-Aug-99 gperry Fixed error messages.
40 115.9 16-Aug-99 gperry Used nocopy compiler directive.
41 115.10 17-Aug-99 gperry Removed business group id check from
42 set_ast_object.
43 115.11 18-Aug-99 gperry Added new cache structures for
44 ben_bnfts_bal_f.
45 115.12 23-Aug-99 gperry Cache full time info using assignment id.
46 Removed trace messages.
47 Hashing done locally now.
48 115.13 26-Aug-99 gperry Added benefits assignment cache.
49 Added applicants assignment cache.
50 Made assignment cache use employee
51 assignment.
52 115.14 26-Aug-99 gperry Made applicants assignment cache multirow.
53 115.15 01-Sep-99 gperry Applicant rows return in date order.
54 115.16 15-Sep-99 gperry Fixed bug 3045. Person type returns a null
55 row for terminated employees.
56 115.17 16-Sep-99 gperry Backport of 115.7 with 115.16 fix.
57 115.18 16-Sep-99 gperry Leapfrog of 115.16.
58 115.19 08-Oct-99 gperry Backport of 115.17 with 115.10 fix
59 Works with version 115.8 header.
60 115.20 19-Oct-99 Tmathers Leapfrog of 115.18.
61 115.21 30-Dec-99 gperry Fixed bug 1133284 so that when you get
62 period of service you get the latest info
63 whether the service is active or inactive.
64 115.22 08-Feb-00 lmcdonal add g_cache_last_typ_rec to clear_down_cache
65 bug 1167264.
66 115.23 01-May-00 rchase Performance enhancements, implemented
67 exception capturing instead of exists clauses
68 added "out NOCOPY" to all set procs and
69 removed extra record assignment statements
70 115.24 10-Jul-00 gperry Added firstass cache for WWBUG 1350997.
71 115.25 20-Jul-00 gperry Removed firstass cache and added allass
72 cache for WWBUG 1350997.
73 115.26 26-Jul-00 bbulusu Selecting all contacts for g_cache_con_rec.
74 Fix for WW Bug #1325440. Leapfrog based
75 on 115.22 - for Aera Production.
76 115.27 26-Jul-00 jcarpent Leapfrog based on 115.25 with change from
77 above.
78 115.28 28-Aug-00 stee Select all contacts where the effective
79 date is >= date_start instead of
80 > date_start.
81 115.29 14-Sep-00 mhoyes - Added delete calls to all asg and applicant
82 assignment caches.
83 115.30 03-Oct-00 mhoyes - Fixed semi-colon compliance violation.
84 115.31 05-Oct-00 gperry Added ord_id to person type cache.
85 This way we can get person type from person.
86 115.32 07-Nov-00 kmahendr - Fixed cache delete in the procedure set_allass_objecT
87 g_cache_last_allass_rec.delete in place of g_cache_last_appass_rec.
88 delete - WWWBug#1492522
89 115.33 05-Jan-01 kmahendr - Added parameter per_in_ler_id
90 115.34 24-Jan-02 kmahendr - Bug#2179708- Added cursor C2 to get full_time equivalent
91 of all the assignments
92 115.35 16-Mar-02 kmahendr - added dbdrv lines
93 115.36 29-Apr-02 pabodla - Bug 1631182 : support user created
94 person type. Added person_type_id
95 parameter.
96
97 115.37 03-May-02 pabodla - In set_typ_object for person_types do not get
98 person_type_id from per_all_peole_f
99 115.38 08-Jun-02 pabodla Do not select the contingent worker
100 assignment when assignment data is
101 fetched.
102 115.40 10-Oct-02 tmathers bug 2620818 set_typ_object(): cursor c1
103 mmudigon changed order by clause from col 4 to 5
104 115.41 13 Feb 03 mhoyes - Fixed PGA memory growth bug 2800680.
105 115.42 17 Feb 03 pabodla Added debug code
106 115.43 20 Apr 03 pbodla - FONM : where ever l_env dates are used
107 use fonm dates first.
108 115.44 14-Sep-07 rtagarra - Bug 6399423 changed cursor c1 in set_ass_object
109 -----------------------------------------------------------------------------
110 */
111 --
112 g_package varchar2(30) := 'ben_person_object.';
113 g_hash_key number := ben_hash_utility.get_hash_key;
114 g_hash_jump number := ben_hash_utility.get_hash_jump;
115 g_debug boolean := hr_utility.debug_enabled;
116 --
117 -- Set object routines
118 --
119 procedure set_object(p_rec in out NOCOPY per_all_people_f%rowtype) is
120 --
121 l_proc varchar2(80) := g_package||'set_object per';
122 l_index pls_integer;
123 --
124 begin
125 --
126 -- hr_utility.set_location('Entering '||l_proc,10);
127 --
128 -- 1) get hash index
129 -- 2) If hash index is not used use hash index
130 -- 3) If hash index is used and correct then do nothing
131 -- 4) If hash index is used and not correct then try next hash index
132 --
133 -- Get hashed index value
134 --
135 l_index := mod(p_rec.person_id,g_hash_key);
136 --
137 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
138 if g_cache_per_rec(l_index).person_id = p_rec.person_id then
139 -- do nothing, cache entry already exists
140 null;
141 else
142 --
143 -- Loop through the hash using the jump routine to check further
144 -- indexes
145 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
146 --
147 l_index := l_index+g_hash_jump;
148 while g_cache_per_rec(l_index).person_id <> p_rec.person_id loop
149 --
150 l_index := l_index+g_hash_jump;
151
152 end loop;
153 --
154 end if;
155 --
156 -- hr_utility.set_location('Leaving '||l_proc,10);
157 --
158 exception when NO_DATA_FOUND then
159 -- set cache entry at current index location
160 g_cache_per_rec(l_index):=p_rec;
161 --
162 end set_object;
163 --
164 procedure set_object(p_rec in out NOCOPY ben_bnfts_bal_f%rowtype) is
165 --
166 l_proc varchar2(80) := g_package||'set_object bnb';
167 l_index pls_integer;
168 --
169 begin
170 --
171 -- hr_utility.set_location('Entering '||l_proc,10);
172 --
173 -- 1) get hash index
174 -- 2) If hash index is not used use hash index
175 -- 3) If hash index is used and correct then do nothing
176 -- 4) If hash index is used and not correct then try next hash index
177 --
178 -- Get hashed index value
179 --
180 l_index := mod(p_rec.bnfts_bal_id,g_hash_key);
181 --
182 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
183 if g_cache_bnb_rec(l_index).bnfts_bal_id = p_rec.bnfts_bal_id then
184 -- do nothing, cache entry already exists
185 null;
186 else
187 --
188 -- Loop through the hash using the jump routine to check further
189 -- indexes
190 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
191 --
192 l_index := l_index+g_hash_jump;
193 while g_cache_bnb_rec(l_index).bnfts_bal_id <> p_rec.bnfts_bal_id loop
194 --
195 l_index := l_index+g_hash_jump;
196
197 end loop;
198 --
199 end if;
200 --
201 -- hr_utility.set_location('Leaving '||l_proc,10);
202 --
203 exception when NO_DATA_FOUND then
204 -- set cache entry at current index location
205 g_cache_bnb_rec(l_index):=p_rec;
206 end set_object;
207 --
208 procedure set_object(p_rec in out NOCOPY hr_soft_coding_keyflex%rowtype) is
209 --
210 l_proc varchar2(80) := g_package||'set_object hsc';
211 l_index pls_integer;
212 --
213 begin
214 --
215 -- hr_utility.set_location('Entering '||l_proc,10);
216 --
217 -- 1) get hash index
218 -- 2) If hash index is not used use hash index
219 -- 3) If hash index is used and correct then do nothing
220 -- 4) If hash index is used and not correct then try next hash index
221 --
222 -- Get hashed index value
223 --
224 l_index := mod(p_rec.soft_coding_keyflex_id,g_hash_key);
225 --
226 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
227 if g_cache_hsc_rec(l_index).soft_coding_keyflex_id = p_rec.soft_coding_keyflex_id then
228 -- do nothing, cache entry already exists
229 null;
230 else
231 --
232 -- Loop through the hash using the jump routine to check further
233 -- indexes
234 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
235 --
236 l_index := l_index+g_hash_jump;
237 while g_cache_hsc_rec(l_index).soft_coding_keyflex_id <> p_rec.soft_coding_keyflex_id loop
238 --
239 l_index := l_index+g_hash_jump;
240
241 end loop;
242 --
243 end if;
244 --
245 -- hr_utility.set_location('Leaving '||l_proc,10);
246 --
247 exception when NO_DATA_FOUND then
248 -- 115.23 set cache entry at current index location
249 g_cache_hsc_rec(l_index):=p_rec;
250 end set_object;
251 --
252 procedure set_object(p_rec in out NOCOPY per_all_assignments_f%rowtype) is
253 --
254 l_proc varchar2(80) := g_package||'set_object asg';
255 l_index pls_integer;
256 --
257 begin
258 --
259 -- hr_utility.set_location('Entering '||l_proc,10);
260 --
261 -- 1) get hash index
262 -- 2) If hash index is not used use hash index
263 -- 3) If hash index is used and correct then do nothing
264 -- 4) If hash index is used and not correct then try next hash index
265 --
266 -- Get hashed index value
267 --
268 l_index := mod(p_rec.person_id,g_hash_key);
269 --
270 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
271 if g_cache_ass_rec(l_index).person_id = p_rec.person_id then
272 -- do nothing, cache entry already exists
273 null;
274 else
275 --
276 -- Loop through the hash using the jump routine to check further
277 -- indexes
278 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
279 --
280 l_index := l_index+g_hash_jump;
281 while g_cache_ass_rec(l_index).person_id <> p_rec.person_id loop
282 --
283 l_index := l_index+g_hash_jump;
284
285 end loop;
286 --
287 end if;
288 --
289 -- hr_utility.set_location('Leaving '||l_proc,10);
290 --
291 exception when NO_DATA_FOUND then
292 -- 115.23 set cache entry at current index location
293 g_cache_ass_rec(l_index):=p_rec;
294 end set_object;
295 --
296 procedure set_benass_object(p_rec in out NOCOPY per_all_assignments_f%rowtype) is
297 --
298 l_proc varchar2(80) := g_package||'set_object benass';
299 l_index pls_integer;
300 begin
301 --
302 -- hr_utility.set_location('Entering '||l_proc,10);
303 --
304 -- 1) get hash index
305 -- 2) If hash index is not used use hash index
306 -- 3) If hash index is used and correct then do nothing
307 -- 4) If hash index is used and not correct then try next hash index
308 --
309 -- Get hashed index value
310 --
311 l_index := mod(p_rec.person_id,g_hash_key);
312 --
313 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
314 if g_cache_benass_rec(l_index).person_id = p_rec.person_id then
315 -- do nothing, cache entry already exists
316 null;
317 else
318 --
319 -- Loop through the hash using the jump routine to check further
320 -- indexes
321 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
322 --
323 l_index := l_index+g_hash_jump;
324 while g_cache_benass_rec(l_index).person_id <> p_rec.person_id loop
325 --
326 l_index := l_index+g_hash_jump;
327
328 end loop;
329 --
330 end if;
331 --
332 -- hr_utility.set_location('Leaving '||l_proc,10);
333 --
334 exception when NO_DATA_FOUND then
335 -- 115.23 set cache entry at current index location
336 g_cache_benass_rec(l_index):=p_rec;
337 end set_benass_object;
338 --
339 procedure set_object(p_rec in out NOCOPY per_assignment_status_types%rowtype) is
340 --
341 l_proc varchar2(80) := g_package||'set_object ast';
342 l_index pls_integer;
343 --
344 begin
345 --
346 -- hr_utility.set_location('Entering '||l_proc,10);
347 --
348 -- 1) get hash index
349 -- 2) If hash index is not used use hash index
350 -- 3) If hash index is used and correct then do nothing
351 -- 4) If hash index is used and not correct then try next hash index
352 --
353 -- Get hashed index value
354 --
358 if g_cache_ast_rec(l_index).assignment_status_type_id = p_rec.assignment_status_type_id then
355 l_index := mod(p_rec.assignment_status_type_id,g_hash_key);
356 --
357 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
359 -- do nothing, cache entry already exists
360 null;
361 else
362 --
363 -- Loop through the hash using the jump routine to check further
364 -- indexes
365 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
366 --
367 l_index := l_index+g_hash_jump;
368 while g_cache_ast_rec(l_index).assignment_status_type_id <> p_rec.assignment_status_type_id loop
369 --
370 l_index := l_index+g_hash_jump;
371
372 end loop;
373 --
374 end if;
375 --
376 -- hr_utility.set_location('Leaving '||l_proc,10);
377 --
378 exception when NO_DATA_FOUND then
379 -- 115.23 set cache entry at current index location
380 g_cache_ast_rec(l_index):=p_rec;
381 end set_object;
382 --
383 procedure set_object(p_rec in out NOCOPY per_assignment_extra_info%rowtype) is
384 --
385 l_proc varchar2(80) := g_package||'set_object aei';
386 l_index pls_integer;
387 --
388 begin
389 --
390 -- hr_utility.set_location('Entering '||l_proc,10);
391 --
392 -- 1) get hash index
393 -- 2) If hash index is not used use hash index
394 -- 3) If hash index is used and correct then do nothing
395 -- 4) If hash index is used and not correct then try next hash index
396 --
397 -- Get hashed index value
398 --
399 l_index := mod(p_rec.assignment_id,g_hash_key);
400 --
401 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
402 if g_cache_aei_rec(l_index).assignment_id = p_rec.assignment_id then
403 -- do nothing, cache entry already exists
404 null;
405 else
406 --
407 -- Loop through the hash using the jump routine to check further
408 -- indexes
409 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
410 --
411 l_index := l_index+g_hash_jump;
412 while g_cache_aei_rec(l_index).assignment_id <> p_rec.assignment_id loop
413 --
414 l_index := l_index+g_hash_jump;
415
416 end loop;
417 --
418 end if;
419 --
420 -- hr_utility.set_location('Leaving '||l_proc,10);
421 --
422 exception when NO_DATA_FOUND then
423 -- 115.23 set cache entry at current index location
424 g_cache_aei_rec(l_index):=p_rec;
425 end set_object;
426 --
427 procedure set_object(p_rec in out NOCOPY ben_per_in_ler%rowtype) is
428 --
429 l_proc varchar2(80) := g_package||'set_object pil';
430 l_index pls_integer;
431 --
432 begin
433 --
434 -- hr_utility.set_location('Entering '||l_proc,10);
435 --
436 -- 1) get hash index
437 -- 2) If hash index is not used use hash index
438 -- 3) If hash index is used and correct then do nothing
439 -- 4) If hash index is used and not correct then try next hash index
440 --
441 -- Get hashed index value
442 --
443 l_index := mod(p_rec.person_id,g_hash_key);
444 --
445 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
446 if g_cache_pil_rec(l_index).person_id = p_rec.person_id then
447 -- do nothing, cache entry already exists
448 null;
449 else
450 --
451 -- Loop through the hash using the jump routine to check further
452 -- indexes
453 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
454 --
455 l_index := l_index+g_hash_jump;
456 while g_cache_pil_rec(l_index).person_id <> p_rec.person_id loop
457 --
458 l_index := l_index+g_hash_jump;
459
460 end loop;
461 --
462 end if;
463 --
464 -- hr_utility.set_location('Leaving '||l_proc,10);
465 --
466 exception when NO_DATA_FOUND then
467 -- 115.23 set cache entry at current index location
468 g_cache_pil_rec(l_index):=p_rec;
469 end set_object;
470 --
471 procedure set_object(p_rec in out NOCOPY ben_per_bnfts_bal_f%rowtype) is
472 --
473 l_proc varchar2(80) := g_package||'set_object bbb';
474 l_index pls_integer;
475 --
476 begin
477 --
478 -- hr_utility.set_location('Entering '||l_proc,10);
479 --
480 l_index := nvl(g_cache_bal_rec.count,0)+1;
481 --
482 g_cache_bal_rec(l_index) := p_rec;
483 --
484 -- hr_utility.set_location('Leaving '||l_proc,10);
485 --
486 end set_object;
487 --
488 procedure set_object(p_rec in out NOCOPY per_contact_relationships%rowtype) is
489 --
490 l_proc varchar2(80) := g_package||'set_object ctr';
491 l_index pls_integer;
492 --
493 begin
494 --
495 -- hr_utility.set_location('Entering '||l_proc,10);
496 --
497 l_index := nvl(g_cache_con_rec.count,0)+1;
498 --
502 --
499 g_cache_con_rec(l_index) := p_rec;
500 --
501 -- hr_utility.set_location('Leaving '||l_proc,10);
503 end set_object;
504 --
505 procedure set_appass_object(p_rec in out NOCOPY per_all_assignments_f%rowtype) is
506 --
507 l_proc varchar2(80) := g_package||'set_object appass';
508 l_index pls_integer;
509 --
510 begin
511 --
512 -- hr_utility.set_location('Entering '||l_proc,10);
513 --
514 l_index := nvl(g_cache_appass_rec.count,0)+1;
515 --
516 g_cache_appass_rec(l_index) := p_rec;
517 --
518 -- hr_utility.set_location('Leaving '||l_proc,10);
519 --
520 end set_appass_object;
521 --
522 procedure set_allass_object(p_rec in out NOCOPY per_all_assignments_f%rowtype) is
523 --
524 l_proc varchar2(80) := g_package||'set_object allass';
525 l_index pls_integer;
526 --
527 begin
528 --
529 -- hr_utility.set_location('Entering '||l_proc,10);
530 --
531 l_index := nvl(g_cache_allass_rec.count,0)+1;
532 --
533 g_cache_allass_rec(l_index) := p_rec;
534 --
535 -- hr_utility.set_location('Leaving '||l_proc,10);
536 --
537 end set_allass_object;
538 --
539 procedure set_object(p_rec in out NOCOPY g_person_typ_info_rec) is
540 --
541 l_proc varchar2(80) := g_package||'set_object ctr';
542 l_index pls_integer;
543 --
544 begin
545 --
546 -- hr_utility.set_location('Entering '||l_proc,10);
547 --
548 l_index := nvl(g_cache_typ_rec.count,0)+1;
549 --
550 g_cache_typ_rec(l_index) := p_rec;
551 --
552 -- hr_utility.set_location('Leaving '||l_proc,10);
553 --
554 end set_object;
555 --
556 procedure set_object(p_rec in out NOCOPY per_periods_of_service%rowtype) is
557 --
558 l_proc varchar2(80) := g_package||'set_object pds';
559 l_index pls_integer;
560 --
561 begin
562 --
563 -- hr_utility.set_location('Entering '||l_proc,10);
564 --
565 -- 1) get hash index
566 -- 2) If hash index is not used use hash index
567 -- 3) If hash index is used and correct then do nothing
568 -- 4) If hash index is used and not correct then try next hash index
569 --
570 -- Get hashed index value
571 --
572 l_index := mod(p_rec.person_id,g_hash_key);
573 --
574 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
575 if g_cache_pps_rec(l_index).person_id = p_rec.person_id then
576 -- do nothing, cache entry already exists
577 null;
578 else
579 --
580 -- Loop through the hash using the jump routine to check further
581 -- indexes
582 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
583 --
584 l_index := l_index+g_hash_jump;
585 while g_cache_pps_rec(l_index).person_id <> p_rec.person_id loop
586 --
587 l_index := l_index+g_hash_jump;
588
589 end loop;
590 --
591 end if;
592 --
593 -- hr_utility.set_location('Leaving '||l_proc,10);
594 --
595 exception when NO_DATA_FOUND then
596 -- 115.23 set cache entry at current index location
597 g_cache_pps_rec(l_index):=p_rec;
598 end set_object;
599 --
600 procedure set_object(p_rec in out NOCOPY per_addresses%rowtype) is
601 --
602 l_proc varchar2(80) := g_package||'set_object adr';
603 l_index pls_integer;
604 --
605 begin
606 --
607 -- hr_utility.set_location('Entering '||l_proc,10);
608 --
609 -- 1) get hash index
610 -- 2) If hash index is not used use hash index
611 -- 3) If hash index is used and correct then do nothing
612 -- 4) If hash index is used and not correct then try next hash index
613 --
614 -- Get hashed index value
615 --
616 l_index := mod(p_rec.person_id,g_hash_key);
617 --
618 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
619 if g_cache_pad_rec(l_index).person_id = p_rec.person_id then
620 -- do nothing, cache entry already exists
621 null;
622 else
623 --
624 -- Loop through the hash using the jump routine to check further
625 -- indexes
626 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
627 --
628 l_index := l_index+g_hash_jump;
629 while g_cache_pad_rec(l_index).person_id <> p_rec.person_id loop
630 --
631 l_index := l_index+g_hash_jump;
632
633 end loop;
634 --
635 end if;
636 --
637 -- hr_utility.set_location('Leaving '||l_proc,10);
638 --
639 exception when NO_DATA_FOUND then
640 -- 115.23 set cache entry at current index location
641 g_cache_pad_rec(l_index):=p_rec;
642 end set_object;
643 --
644 procedure set_object(p_rec in out NOCOPY g_person_date_info_rec) is
645 --
646 l_proc varchar2(80) := g_package||'set_object pdi';
647 l_index pls_integer;
648 --
649 begin
650 --
654 -- 2) If hash index is not used use hash index
651 -- hr_utility.set_location('Entering '||l_proc,10);
652 --
653 -- 1) get hash index
655 -- 3) If hash index is used and correct then do nothing
656 -- 4) If hash index is used and not correct then try next hash index
657 --
658 -- Get hashed index value
659 --
660 l_index := mod(p_rec.person_id,g_hash_key);
661 --
662 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
663 if g_cache_date_rec(l_index).person_id = p_rec.person_id then
664 -- do nothing, cache entry already exists
665 null;
666 else
667 --
668 -- Loop through the hash using the jump routine to check further
669 -- indexes
670 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
671 --
672 l_index := l_index+g_hash_jump;
673 while g_cache_date_rec(l_index).person_id <> p_rec.person_id loop
674 --
675 l_index := l_index+g_hash_jump;
676
677 end loop;
678 --
679 end if;
680 --
681 -- hr_utility.set_location('Leaving '||l_proc,10);
682 --
683 exception when NO_DATA_FOUND then
684 -- 115.23 set cache entry at current index location
685 g_cache_date_rec(l_index):=p_rec;
686 end set_object;
687 --
688 procedure set_bal_per_object(p_rec in out NOCOPY ben_cache.g_cache_lookup) is
689 --
690 l_proc varchar2(80) := g_package||'set_bal_per_object';
691 l_index pls_integer;
692 --
693 begin
694 --
695 -- hr_utility.set_location('Entering '||l_proc,10);
696 --
697 -- 1) get hash index
698 -- 2) If hash index is not used use hash index
699 -- 3) If hash index is used and correct then do nothing
700 -- 4) If hash index is used and not correct then try next hash index
701 --
702 -- Get hashed index value
703 --
704 l_index := mod(p_rec.id,g_hash_key);
705 --
706 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
707 if g_cache_bal_per_rec(l_index).id = p_rec.id then
708 -- do nothing, cache entry already exists
709 null;
710 else
711 --
712 -- Loop through the hash using the jump routine to check further
713 -- indexes
714 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
715 --
716 l_index := l_index+g_hash_jump;
717 while g_cache_bal_per_rec(l_index).id <> p_rec.id loop
718 --
719 l_index := l_index+g_hash_jump;
720
721 end loop;
722 --
723 end if;
724 --
725 -- hr_utility.set_location('Leaving '||l_proc,10);
726 --
727 exception when NO_DATA_FOUND then
728 -- 115.23 set cache entry at current index location
729 g_cache_bal_per_rec(l_index):=p_rec;
730 end set_bal_per_object;
731 --
732 procedure set_con_per_object(p_rec in out NOCOPY ben_cache.g_cache_lookup) is
733 --
734 l_proc varchar2(80) := g_package||'set_con_per_object';
735 l_index pls_integer;
736 --
737 begin
738 --
739 -- hr_utility.set_location('Entering '||l_proc,10);
740 --
741 -- 1) get hash index
742 -- 2) If hash index is not used use hash index
743 -- 3) If hash index is used and correct then do nothing
744 -- 4) If hash index is used and not correct then try next hash index
745 --
746 -- Get hashed index value
747 --
748 l_index := mod(p_rec.id,g_hash_key);
749 --
750 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
751 if g_cache_con_per_rec(l_index).id = p_rec.id then
752 -- do nothing, cache entry already exists
753 null;
754 else
755 --
756 -- Loop through the hash using the jump routine to check further
757 -- indexes
758 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
759 --
760 l_index := l_index+g_hash_jump;
761 while g_cache_con_per_rec(l_index).id <> p_rec.id loop
762 --
763 l_index := l_index+g_hash_jump;
764
765 end loop;
766 --
767 end if;
768 --
769 -- hr_utility.set_location('Leaving '||l_proc,10);
770 --
771 exception when NO_DATA_FOUND then
772 -- 115.23 set cache entry at current index location
773 g_cache_con_per_rec(l_index):=p_rec;
774 end set_con_per_object;
775 --
776 procedure set_app_ass_object(p_rec in out NOCOPY ben_cache.g_cache_lookup) is
777 --
778 l_proc varchar2(80) := g_package||'set_appass_object';
779 l_index pls_integer;
780 --
781 begin
782 --
783 -- hr_utility.set_location('Entering '||l_proc,10);
784 --
785 -- 1) get hash index
786 -- 2) If hash index is not used use hash index
787 -- 3) If hash index is used and correct then do nothing
788 -- 4) If hash index is used and not correct then try next hash index
789 --
790 -- Get hashed index value
791 --
792 l_index := mod(p_rec.id,g_hash_key);
793 --
797 null;
794 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
795 if g_cache_app_ass_rec(l_index).id = p_rec.id then
796 -- do nothing, cache entry already exists
798 else
799 --
800 -- Loop through the hash using the jump routine to check further
801 -- indexes
802 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
803 --
804 l_index := l_index+g_hash_jump;
805 while g_cache_app_ass_rec(l_index).id <> p_rec.id loop
806 --
807 l_index := l_index+g_hash_jump;
808
809 end loop;
810 --
811 end if;
812 --
813 -- hr_utility.set_location('Leaving '||l_proc,10);
814 --
815 exception when NO_DATA_FOUND then
816 -- 115.23 set cache entry at current index location
817 g_cache_app_ass_rec(l_index):=p_rec;
818 end set_app_ass_object;
819 --
820 procedure set_all_ass_object(p_rec in out NOCOPY ben_cache.g_cache_lookup) is
821 --
822 l_proc varchar2(80) := g_package||'set_allass_object';
823 l_index pls_integer;
824 --
825 begin
826 --
827 -- hr_utility.set_location('Entering '||l_proc,10);
828 --
829 -- 1) get hash index
830 -- 2) If hash index is not used use hash index
831 -- 3) If hash index is used and correct then do nothing
832 -- 4) If hash index is used and not correct then try next hash index
833 --
834 -- Get hashed index value
835 --
836 l_index := mod(p_rec.id,g_hash_key);
837 --
838 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
839 if g_cache_all_ass_rec(l_index).id = p_rec.id then
840 -- do nothing, cache entry already exists
841 null;
842 else
843 --
844 -- Loop through the hash using the jump routine to check further
845 -- indexes
846 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
847 --
848 l_index := l_index+g_hash_jump;
849 while g_cache_all_ass_rec(l_index).id <> p_rec.id loop
850 --
851 l_index := l_index+g_hash_jump;
852
853 end loop;
854 --
855 end if;
856 --
857 -- hr_utility.set_location('Leaving '||l_proc,10);
858 --
859 exception when NO_DATA_FOUND then
860 -- 115.23 set cache entry at current index location
861 g_cache_all_ass_rec(l_index):=p_rec;
862 end set_all_ass_object;
863 --
864 procedure set_typ_per_object(p_rec in out NOCOPY ben_cache.g_cache_lookup) is
865 --
866 l_proc varchar2(80) := g_package||'set_typ_per_object';
867 l_index pls_integer;
868 --
869 begin
870 --
871 -- hr_utility.set_location('Entering '||l_proc,10);
872 --
873 -- 1) get hash index
874 -- 2) If hash index is not used use hash index
875 -- 3) If hash index is used and correct then do nothing
876 -- 4) If hash index is used and not correct then try next hash index
877 --
878 -- Get hashed index value
879 --
880 l_index := mod(p_rec.id,g_hash_key);
881 --
882 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
883 if g_cache_typ_per_rec(l_index).id = p_rec.id then
884 -- do nothing, cache entry already exists
885 null;
886 else
887 --
888 -- Loop through the hash using the jump routine to check further
889 -- indexes
890 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
891 --
892 l_index := l_index+g_hash_jump;
893 while g_cache_typ_per_rec(l_index).id <> p_rec.id loop
894 --
895 l_index := l_index+g_hash_jump;
896
897 end loop;
898 --
899 end if;
900 --
901 -- hr_utility.set_location('Leaving '||l_proc,10);
902 --
903 exception when NO_DATA_FOUND then
904 -- 115.23 set cache entry at current index location
905 g_cache_typ_per_rec(l_index):=p_rec;
906 end set_typ_per_object;
907 --
908 procedure set_object(p_rec in out NOCOPY g_person_fte_info_rec) is
909 --
910 l_proc varchar2(80) := g_package||'set_object pfte';
911 l_index pls_integer;
912 --
913 begin
914 --
915 -- hr_utility.set_location('Entering '||l_proc,10);
916 --
917 -- 1) get hash index
918 -- 2) If hash index is not used use hash index
919 -- 3) If hash index is used and correct then do nothing
920 -- 4) If hash index is used and not correct then try next hash index
921 --
922 -- Get hashed index value
923 --
924 l_index := mod(p_rec.assignment_id,g_hash_key);
925 --
926 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
927 if g_cache_fte_rec(l_index).assignment_id = p_rec.assignment_id then
928 -- do nothing, cache entry already exists
929 null;
930 else
931 --
932 -- Loop through the hash using the jump routine to check further
933 -- indexes
934 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
935 --
939 l_index := l_index+g_hash_jump;
936 l_index := l_index+g_hash_jump;
937 while g_cache_fte_rec(l_index).assignment_id <> p_rec.assignment_id loop
938 --
940
941 end loop;
942 --
943 end if;
944 --
945 -- hr_utility.set_location('Leaving '||l_proc,10);
946 --
947 exception when NO_DATA_FOUND then
948 -- 115.23 set cache entry at current index location
949 g_cache_fte_rec(l_index):=p_rec;
950 end set_object;
951 --
952 -- Set object alternate route routines
953 --
954 procedure set_ass_object
955 (p_person_id in number,
956 p_business_group_id in number,
957 p_effective_date in date,
958 p_rec in out nocopy per_all_assignments_f%rowtype) is
959 --
960 l_proc varchar2(80) := g_package||'set_ass_object';
961 --
962 -- Bug 6399423
963 /*
964 cursor c1 is
965 select paf.*
966 from per_all_assignments_f paf
967 where paf.person_id = p_person_id
968 and paf.business_group_id = p_business_group_id
969 and paf.primary_flag = 'Y'
970 and paf.assignment_type = 'E'
971 and p_effective_date
972 between paf.effective_start_date
973 and paf.effective_end_date;
974 */
975 cursor c1 is
976 select paf.*
977 from per_all_assignments_f paf
978 ,per_assignment_status_types ast
979 where paf.person_id = p_person_id
980 and paf.business_group_id = p_business_group_id
981 and paf.primary_flag = 'Y'
982 and paf.assignment_type = 'E'
983 and paf.assignment_status_type_id = ast.assignment_status_type_id
984 and ast.per_system_status <> 'TERM_ASSIGN'
985 and p_effective_date
986 between paf.effective_start_date
987 and paf.effective_end_date;
988
989 -- Bug 6399423
990 --l_rec per_all_assignments_f%rowtype;
991 --115.23 - comment out unneeded record
992 --
993 begin
994 --
995 -- hr_utility.set_location('Entering '||l_proc,10);
996 --
997 -- 1) Get record from database.
998 -- 2) If record not found then raise error.
999 -- 3) Pass record to set_object routine.
1000 --
1001 open c1;
1002 --
1003 --115.23 - altered to use NOCOPY record
1004 fetch c1 into p_rec;
1005 if c1%notfound then
1006 --
1007 --115.23 - altered to use NOCOPY record
1008 --l_rec.person_id := p_person_id;
1009 p_rec.person_id := p_person_id;
1010 --
1011 end if;
1012 --
1013 close c1;
1014 --
1015 --115.23 - altered to use NOCOPY record
1016 --set_object(p_rec => l_rec);
1017 set_object(p_rec => p_rec);
1018 --
1019 -- 115.23 - unnecessary, using NOCOPY record
1020 --p_rec := l_rec;
1021 --
1022 -- hr_utility.set_location('Leaving '||l_proc,10);
1023 --
1024 end set_ass_object;
1025 --
1026 procedure set_benass_object
1027 (p_person_id in number,
1028 p_business_group_id in number,
1029 p_effective_date in date,
1030 p_rec in out nocopy per_all_assignments_f%rowtype) is
1031 --
1032 l_proc varchar2(80) := g_package||'set_benass_object';
1033 --
1034 cursor c1 is
1035 select paf.*
1036 from per_all_assignments_f paf
1037 where paf.person_id = p_person_id
1038 and paf.business_group_id = p_business_group_id
1039 and paf.primary_flag = 'Y'
1040 and paf.assignment_type = 'B'
1041 and p_effective_date
1042 between paf.effective_start_date
1043 and paf.effective_end_date;
1044 --
1045 --l_rec per_all_assignments_f%rowtype;
1046 --115.23 - comment out unneeded record
1047 --
1048 begin
1049 --
1050 -- hr_utility.set_location('Entering '||l_proc,10);
1051 --
1052 -- 1) Get record from database.
1053 -- 2) If record not found then raise error.
1054 -- 3) Pass record to set_object routine.
1055 --
1056 open c1;
1057 --
1058 --115.23 - altered to use NOCOPY record
1059 fetch c1 into p_rec;
1060 if c1%notfound then
1061 --
1062 --115.23 - altered to use NOCOPY record
1063 --l_rec.person_id := p_person_id;
1064 p_rec.person_id := p_person_id;
1065 --
1066 end if;
1067 --
1068 close c1;
1069 --
1070 --115.23 - altered to use NOCOPY record
1071 --set_object(p_rec => l_rec);
1072 set_object(p_rec => p_rec);
1073 --
1074 -- 115.23 - unnecessary, using NOCOPY record
1075 --p_rec := l_rec;
1076 --
1077 -- hr_utility.set_location('Leaving '||l_proc,10);
1078 --
1079 end set_benass_object;
1080 --
1081 procedure set_bnb_object
1082 (p_bnfts_bal_id in number,
1083 p_business_group_id in number,
1084 p_effective_date in date,
1085 p_rec in out nocopy ben_bnfts_bal_f%rowtype) is
1086 --
1087 l_proc varchar2(80) := g_package||'set_bnb_object';
1088 --
1089 -- FONM
1090 l_effective_date date ;
1091 --
1092 cursor c1 is
1093 select bnb.*
1094 from ben_bnfts_bal_f bnb
1098 between bnb.effective_start_date
1095 where bnb.bnfts_bal_id = p_bnfts_bal_id
1096 and bnb.business_group_id = p_business_group_id
1097 and l_effective_date
1099 and bnb.effective_end_date;
1100 --
1101 --l_rec ben_bnfts_bal_f%rowtype;
1102 --115.23 - comment out unneeded record
1103 --
1104 begin
1105 --
1106 -- hr_utility.set_location('Entering '||l_proc,10);
1107 --
1108 -- 1) Get record from database.
1109 -- 2) If record not found then raise error.
1110 -- 3) Pass record to set_object routine.
1111 --
1112 -- FONM
1113 --
1114 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
1115 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
1116 p_effective_date)
1117 );
1118 open c1;
1119 --
1120 --115.23 - altered to use NOCOPY record
1121 fetch c1 into p_rec;
1122 if c1%notfound then
1123 --
1124 --115.23 - altered to use NOCOPY record
1125 --l_rec.person_id := p_person_id;
1126 p_rec.bnfts_bal_id := p_bnfts_bal_id;
1127 --
1128 end if;
1129 --
1130 close c1;
1131 --
1132 --115.23 - altered to use NOCOPY record
1133 --set_object(p_rec => l_rec);
1134 set_object(p_rec => p_rec);
1135 --
1136 -- 115.23 - unnecessary, using NOCOPY record
1137 --p_rec := l_rec;
1138 --
1139 -- hr_utility.set_location('Leaving '||l_proc,10);
1140 --
1141 end set_bnb_object;
1142 --
1143 procedure set_ast_object
1144 (p_assignment_status_type_id in number,
1145 p_rec in out nocopy per_assignment_status_types%rowtype) is
1146 --
1147 l_proc varchar2(80) := g_package||'set_ast_object';
1148 --
1149 cursor c1 is
1150 select pas.*
1151 from per_assignment_status_types pas
1152 where pas.assignment_status_type_id = p_assignment_status_type_id
1153 and pas.active_flag = 'Y';
1154 --
1155 -- l_rec per_assignment_status_types%rowtype;
1156 --115.23 - comment out unneeded record
1157 --
1158 begin
1159 --
1160 -- hr_utility.set_location('Entering '||l_proc,10);
1161 --
1162 -- 1) Get record from database.
1163 -- 2) If record not found then raise error.
1164 -- 3) Pass record to set_object routine.
1165 --
1166 open c1;
1167 --
1168 --115.23 - altered to use NOCOPY record
1169 fetch c1 into p_rec;
1170 if c1%notfound then
1171 --
1172 --115.23 - altered to use NOCOPY record
1173 --l_rec.person_id := p_person_id;
1174 p_rec.assignment_status_type_id := p_assignment_status_type_id;
1175 --
1176 end if;
1177 --
1178 close c1;
1179 --
1180 --115.23 - altered to use NOCOPY record
1181 --set_object(p_rec => l_rec);
1182 set_object(p_rec => p_rec);
1183 --
1184 -- 115.23 - unnecessary, using NOCOPY record
1185 --p_rec := l_rec;
1186 --
1187 -- hr_utility.set_location('Leaving '||l_proc,10);
1188 --
1189 end set_ast_object;
1190 --
1191 procedure set_ass_object
1192 (p_assignment_id in number,
1193 p_rec in out nocopy per_assignment_extra_info%rowtype) is
1194 --
1195 l_proc varchar2(80) := g_package||'set_ass_object';
1196 --
1197 cursor c1 is
1198 select pei.*
1199 from per_assignment_extra_info pei
1200 where pei.assignment_id = p_assignment_id
1201 and pei.information_type = 'BEN_DERIVED';
1202 --
1203 --l_rec per_assignment_extra_info%rowtype;
1204 --115.23 - comment out unneeded record
1205 --
1206 begin
1207 --
1208 -- hr_utility.set_location('Entering '||l_proc,10);
1209 --
1210 -- 1) Get record from database.
1211 -- 2) If record not found then raise error.
1212 -- 3) Pass record to set_object routine.
1213 --
1214 open c1;
1215 --
1216 --115.23 - altered to use NOCOPY record
1217 fetch c1 into p_rec;
1218 if c1%notfound then
1219 --
1220 --115.23 - altered to use NOCOPY record
1221 --l_rec.person_id := p_person_id;
1222 p_rec.assignment_id := p_assignment_id;
1223 --
1224 end if;
1225 --
1226 close c1;
1227 --
1228 --115.23 - altered to use NOCOPY record
1229 --set_object(p_rec => l_rec);
1230 set_object(p_rec => p_rec);
1231 --
1232 -- 115.23 - unnecessary, using NOCOPY record
1233 --p_rec := l_rec;
1234 --
1235 -- hr_utility.set_location('Leaving '||l_proc,10);
1236 --
1237 end set_ass_object;
1238 --
1239 procedure set_pil_object
1240 (p_person_id in number,
1241 p_business_group_id in number,
1242 p_effective_date in date,
1243 -- added a per_in_ler_id for unrestricted enhancement
1244 p_per_in_ler_id in number default null,
1245 p_rec in out nocopy ben_per_in_ler%rowtype) is
1246 --
1247 l_proc varchar2(80) := g_package||'set_pil_object';
1248 --
1249 cursor c1 is
1250 select pil.*
1251 from ben_per_in_ler pil
1252 where pil.person_id = p_person_id
1256 and pil.per_in_ler_id = nvl(p_per_in_ler_id,pil.per_in_ler_id);
1253 and pil.business_group_id = p_business_group_id
1254 and pil.per_in_ler_stat_cd = 'STRTD'
1255 -- added per_in_ler_id for unrestricted enhancement
1257 --
1258 --l_rec ben_per_in_ler%rowtype;
1259 --115.23 - comment out unneeded record
1260 --
1261 begin
1262 --
1263 -- hr_utility.set_location('Entering '||l_proc,10);
1264 --
1265 -- 1) Get record from database.
1266 -- 2) If record not found then raise error.
1267 -- 3) Pass record to set_object routine.
1268 --
1269 open c1;
1270 --
1271 --115.23 - altered to use NOCOPY record
1272 fetch c1 into p_rec;
1273 if c1%notfound then
1274 --
1275 --115.23 - altered to use NOCOPY record
1276 --l_rec.person_id := p_person_id;
1277 p_rec.person_id := p_person_id;
1278 --
1279 end if;
1280 --
1281 close c1;
1282 --
1283 --115.23 - altered to use NOCOPY record
1284 --set_object(p_rec => l_rec);
1285 set_object(p_rec => p_rec);
1286 --
1287 -- 115.23 - unnecessary, using NOCOPY record
1288 --p_rec := l_rec;
1289 --
1290 -- hr_utility.set_location('Leaving '||l_proc,10);
1291 --
1292 end set_pil_object;
1293 --
1294 procedure set_con_object
1295 (p_person_id in number,
1296 p_business_group_id in number,
1297 p_effective_date in date,
1298 p_rec in out nocopy g_cache_con_table) is
1299 --
1300 l_proc varchar2(80) := g_package||'set_con_object';
1301 --
1302 -- As fix for WW Bug #1325440 this cursor now picks up all contacts for
1303 -- the participant and does not ignore end-dated relationships.
1304 --
1305 cursor c1 is
1306 select con.*
1307 from per_contact_relationships con,
1308 per_all_people_f ppf
1309 where con.person_id = p_person_id
1310 and con.business_group_id = p_business_group_id
1311 and nvl(con.date_start,hr_api.g_sot) <= p_effective_date
1312 and ppf.person_id = con.contact_person_id
1313 and ppf.business_group_id = con.business_group_id
1314 and p_effective_date
1315 between ppf.effective_start_date
1316 and ppf.effective_end_date;
1317 --
1318 l_rec g_cache_con_table;
1319 l_con_per_rec ben_cache.g_cache_lookup;
1320 l_num_recs number := 0;
1321 --
1322 begin
1323 --
1324 -- hr_utility.set_location('Entering '||l_proc,10);
1325 --
1326 -- 1) Get record from database.
1327 -- 2) If record not found then raise error.
1328 -- 3) Pass record to set_object routine.
1329 --
1330 g_cache_last_con_rec.delete;
1331 --
1332 open c1;
1333 --
1334 loop
1335 --
1336 l_num_recs := l_num_recs + 1;
1337 fetch c1 into l_rec(l_num_recs);
1338 exit when c1%notfound;
1339 --
1340 set_object(p_rec => l_rec(l_num_recs));
1341 g_cache_last_con_rec(l_num_recs) := l_rec(l_num_recs);
1342 --
1343 if l_num_recs = 1 then
1344 --
1345 l_con_per_rec.starttorele_num := g_cache_con_rec.count;
1346 --
1347 end if;
1348 --
1349 end loop;
1350 --
1351 close c1;
1352 --
1353 -- We need to set the con_per object.
1354 --
1355 l_con_per_rec.id := p_person_id;
1356 --
1357 if l_con_per_rec.starttorele_num is not null then
1358 --
1359 l_con_per_rec.endtorele_num := g_cache_con_rec.count;
1360 --
1361 end if;
1362 --
1363 set_con_per_object(p_rec => l_con_per_rec);
1364 --
1365 p_rec := l_rec;
1366 --
1367 -- hr_utility.set_location('Leaving '||l_proc,10);
1368 --
1369 end set_con_object;
1370 --
1371 procedure set_appass_object
1372 (p_person_id in number,
1373 p_business_group_id in number,
1374 p_effective_date in date,
1375 p_rec in out nocopy g_cache_ass_table) is
1376 --
1377 l_proc varchar2(80) ;
1378 --
1379 cursor c1 is
1380 select paf.*
1381 from per_all_assignments_f paf
1382 where paf.person_id = p_person_id
1383 and paf.business_group_id = p_business_group_id
1384 and paf.assignment_type = 'A'
1385 and p_effective_date
1386 between paf.effective_start_date
1387 and paf.effective_end_date
1388 order by paf.effective_start_date;
1389 --
1390 l_rec g_cache_ass_table;
1391 l_app_ass_rec ben_cache.g_cache_lookup;
1392 l_num_recs number := 0;
1393 --
1394 begin
1395 --
1396 g_debug := hr_utility.debug_enabled;
1397 if g_debug then
1398 l_proc := g_package||'set_app_ass_object';
1399 hr_utility.set_location('Entering '||l_proc,10);
1400 end if;
1401 --
1402 -- 1) Get record from database.
1403 -- 2) If record not found then raise error.
1404 -- 3) Pass record to set_object routine.
1405 --
1406 g_cache_last_appass_rec.delete;
1407 --
1408 open c1;
1409 --
1410 loop
1411 --
1412 l_num_recs := l_num_recs + 1;
1413 fetch c1 into l_rec(l_num_recs);
1417 g_cache_last_appass_rec(l_num_recs) := l_rec(l_num_recs);
1414 exit when c1%notfound;
1415 --
1416 set_appass_object(p_rec => l_rec(l_num_recs));
1418 --
1419 if l_num_recs = 1 then
1420 --
1421 l_app_ass_rec.starttorele_num := g_cache_app_ass_rec.count;
1422 --
1423 end if;
1424 --
1425 end loop;
1426 --
1427 close c1;
1428 --
1429 -- We need to set the con_per object.
1430 --
1431 l_app_ass_rec.id := p_person_id;
1432 --
1433 if l_app_ass_rec.starttorele_num is not null then
1434 --
1435 l_app_ass_rec.endtorele_num := g_cache_app_ass_rec.count;
1436 --
1437 end if;
1438 --
1439 set_app_ass_object(p_rec => l_app_ass_rec);
1440 --
1441 p_rec := l_rec;
1442 --
1443 if g_debug then
1444 hr_utility.set_location('Leaving '||l_proc,10);
1445 end if;
1446 --
1447 end set_appass_object;
1448 --
1449 procedure set_allass_object
1450 (p_person_id in number,
1451 p_business_group_id in number,
1452 p_effective_date in date,
1453 p_rec in out nocopy g_cache_ass_table) is
1454 --
1455 l_proc varchar2(80) ;
1456 --
1457 cursor c1 is
1458 select paf.*
1459 from per_all_assignments_f paf
1460 where paf.person_id = p_person_id
1461 and paf.assignment_type <> 'C'
1462 and paf.business_group_id = p_business_group_id
1463 and p_effective_date
1464 between paf.effective_start_date
1465 and paf.effective_end_date
1466 order by paf.effective_start_date;
1467 --
1468 l_rec g_cache_ass_table;
1469 l_all_ass_rec ben_cache.g_cache_lookup;
1470 l_num_recs number := 0;
1471 --
1472 begin
1473 --
1474 g_debug := hr_utility.debug_enabled;
1475 if g_debug then
1476 l_proc := g_package||'set_all_ass_object';
1477 hr_utility.set_location('Entering '||l_proc,10);
1478 end if;
1479 --
1480 -- 1) Get record from database.
1481 -- 2) If record not found then raise error.
1482 -- 3) Pass record to set_object routine.
1483 --
1484 g_cache_last_allass_rec.delete;
1485 --
1486 open c1;
1487 --
1488 loop
1489 --
1490 l_num_recs := l_num_recs + 1;
1491 fetch c1 into l_rec(l_num_recs);
1492 exit when c1%notfound;
1493 --
1494 set_allass_object(p_rec => l_rec(l_num_recs));
1495 g_cache_last_allass_rec(l_num_recs) := l_rec(l_num_recs);
1496 --
1497 if l_num_recs = 1 then
1498 --
1499 l_all_ass_rec.starttorele_num := g_cache_all_ass_rec.count;
1500 --
1501 end if;
1502 --
1503 end loop;
1504 --
1505 close c1;
1506 --
1507 -- We need to set the con_per object.
1508 --
1509 l_all_ass_rec.id := p_person_id;
1510 --
1511 if l_all_ass_rec.starttorele_num is not null then
1512 --
1513 l_all_ass_rec.endtorele_num := g_cache_all_ass_rec.count;
1514 --
1515 end if;
1516 --
1517 set_all_ass_object(p_rec => l_all_ass_rec);
1518 --
1519 p_rec := l_rec;
1520 --
1521 if g_debug then
1522 hr_utility.set_location('Leaving '||l_proc,10);
1523 end if;
1524 --
1525 end set_allass_object;
1526 --
1527 procedure set_typ_object
1528 (p_person_id in number,
1529 p_business_group_id in number,
1530 p_effective_date in date,
1531 p_rec in out nocopy g_cache_typ_table) is
1532 --
1533 l_proc varchar2(80) ;
1534 --
1535 cursor c1 is
1536 select per.person_id,
1537 ppt.person_type_id,
1538 ppt.user_person_type,
1539 ppt.system_person_type,
1540 decode(ppt.system_person_type,'EMP',1,2)
1541 from per_person_type_usages_f per,
1542 per_person_types ppt
1543 where per.person_id = p_person_id
1544 and p_effective_date
1545 between per.effective_start_date
1546 and per.effective_end_date
1547 and per.person_type_id = ppt.person_type_id
1548 --
1549 -- This person_type_id is the original person type id. It is possible that
1550 -- this person type id may not exist in per_person_type_usages_f. So this
1551 -- data should not be used for profiles evaluation.
1552 --
1553 /* union
1554 select ppf.person_id,
1555 ppt.person_type_id,
1556 ppt.user_person_type,
1557 ppt.system_person_type,
1558 decode(ppt.system_person_type,'EMP',1,2)
1559 from per_all_people_f ppf,
1560 per_person_types ppt
1561 where ppf.person_id = p_person_id
1562 and p_effective_date
1563 between ppf.effective_start_date
1564 and ppf.effective_end_date
1565 and ppf.person_type_id = ppt.person_type_id */
1566 order by 5;
1567 --
1568 l_rec g_cache_typ_table;
1569 l_typ_per_rec ben_cache.g_cache_lookup;
1570 l_num_recs number := 0;
1571 --
1572 begin
1573 --
1574 -- hr_utility.set_location('Entering '||l_proc,10);
1575 --
1579 --
1576 -- 1) Get record from database.
1577 -- 2) If record not found then raise error.
1578 -- 3) Pass record to set_object routine.
1580 g_cache_last_typ_rec.delete;
1581 --
1582 open c1;
1583 --
1584 loop
1585 --
1586 l_num_recs := l_num_recs + 1;
1587 fetch c1 into l_rec(l_num_recs);
1588 --
1589 if c1%notfound and l_num_recs = 1 then
1590 --
1591 l_rec(l_num_recs).person_id := p_person_id;
1592 set_object(p_rec => l_rec(l_num_recs));
1593 g_cache_last_typ_rec(l_num_recs) := l_rec(l_num_recs);
1594 --
1595 if l_num_recs = 1 then
1596 --
1597 l_typ_per_rec.starttorele_num := g_cache_typ_rec.count;
1598 --
1599 end if;
1600 --
1601 end if;
1602 --
1603 exit when c1%notfound;
1604 --
1605 set_object(p_rec => l_rec(l_num_recs));
1606 g_cache_last_typ_rec(l_num_recs) := l_rec(l_num_recs);
1607 --
1608 if l_num_recs = 1 then
1609 --
1610 l_typ_per_rec.starttorele_num := g_cache_typ_rec.count;
1611 --
1612 end if;
1613 --
1614 end loop;
1615 --
1616 close c1;
1617 --
1618 -- We need to set the typ_per object.
1619 --
1620 l_typ_per_rec.id := p_person_id;
1621 --
1622 if l_typ_per_rec.starttorele_num is not null then
1623 --
1624 l_typ_per_rec.endtorele_num := g_cache_typ_rec.count;
1625 --
1626 end if;
1627 --
1628 set_typ_per_object(p_rec => l_typ_per_rec);
1629 --
1630 p_rec := l_rec;
1631 --
1632 -- hr_utility.set_location('Leaving '||l_proc,10);
1633 --
1634 end set_typ_object;
1635 --
1636 procedure set_bal_object
1637 (p_person_id in number,
1638 p_business_group_id in number,
1639 p_effective_date in date) is
1640 --
1641 l_proc varchar2(80) := g_package||'set_bal_object';
1642 --
1643 cursor c1 is
1644 select bal.*
1645 from ben_per_bnfts_bal_f bal
1646 where bal.person_id = p_person_id
1647 and bal.business_group_id = p_business_group_id;
1648 --
1649 l_rec ben_per_bnfts_bal_f%rowtype;
1650 l_bal_per_rec ben_cache.g_cache_lookup;
1651 l_num_recs number := 0;
1652 --
1653 begin
1654 --
1655 -- hr_utility.set_location('Entering '||l_proc,10);
1656 --
1657 -- 1) Get record from database.
1658 -- 2) If record not found then raise error.
1659 -- 3) Pass record to set_object routine.
1660 --
1661 open c1;
1662 --
1663 loop
1664 --
1665 fetch c1 into l_rec;
1666 exit when c1%notfound;
1667 --
1668 set_object(p_rec => l_rec);
1669 --
1670 l_num_recs := l_num_recs +1;
1671 --
1672 if l_num_recs = 1 then
1673 --
1674 l_bal_per_rec.starttorele_num := g_cache_bal_rec.count;
1675 --
1676 end if;
1677 --
1678 end loop;
1679 --
1680 close c1;
1681 --
1682 -- We need to set the bal_per object.
1683 --
1684 l_bal_per_rec.id := p_person_id;
1685 --
1686 if l_bal_per_rec.starttorele_num is not null then
1687 --
1688 l_bal_per_rec.endtorele_num := g_cache_bal_rec.count;
1689 --
1690 end if;
1691 --
1692 set_bal_per_object(p_rec => l_bal_per_rec);
1693 --
1694 -- hr_utility.set_location('Leaving '||l_proc,10);
1695 --
1696 end set_bal_object;
1697 --
1698 procedure set_pad_object
1699 (p_person_id in number,
1700 p_business_group_id in number,
1701 p_effective_date in date,
1702 p_rec in out nocopy per_addresses%rowtype) is
1703 --
1704 l_proc varchar2(80) := g_package||'set_pad_object';
1705 --
1706 cursor c1 is
1707 select pad.*
1708 from per_addresses pad
1709 where pad.person_id = p_person_id
1710 and pad.business_group_id = p_business_group_id
1711 and pad.primary_flag = 'Y'
1712 and p_effective_date
1713 between pad.date_from
1714 and nvl(pad.date_to,p_effective_date);
1715 --
1716 --l_rec per_addresses%rowtype;
1717 --115.23 - comment out unneeded record
1718 --
1719 begin
1720 --
1721 -- hr_utility.set_location('Entering '||l_proc,10);
1722 --
1723 -- 1) Get record from database.
1724 -- 2) If record not found then raise error.
1725 -- 3) Pass record to set_object routine.
1726 --
1727 open c1;
1728 --
1729 --115.23 - altered to use NOCOPY record
1730 fetch c1 into p_rec;
1731 if c1%notfound then
1732 --
1733 --115.23 - altered to use NOCOPY record
1734 --l_rec.person_id := p_person_id;
1735 p_rec.person_id := p_person_id;
1736 --
1737 end if;
1738 --
1739 close c1;
1740 --
1741 --115.23 - altered to use NOCOPY record
1742 --set_object(p_rec => l_rec);
1743 set_object(p_rec => p_rec);
1744 --
1745 -- 115.23 - unnecessary, using NOCOPY record
1746 --p_rec := l_rec;
1747 --
1751 --
1748 -- hr_utility.set_location('Leaving '||l_proc,10);
1749 --
1750 end set_pad_object;
1752 procedure set_hsc_object
1753 (p_soft_coding_keyflex_id in number,
1754 p_rec in out nocopy hr_soft_coding_keyflex%rowtype) is
1755 --
1756 l_proc varchar2(80) := g_package||'set_hsc_object';
1757 --
1758 cursor c1 is
1759 select hsc.*
1760 from hr_soft_coding_keyflex hsc
1761 where hsc.soft_coding_keyflex_id = p_soft_coding_keyflex_id;
1762 --
1763 l_rec hr_soft_coding_keyflex%rowtype;
1764 --115.23 - comment out unneeded record
1765 --
1766 begin
1767 --
1768 -- hr_utility.set_location('Entering '||l_proc,10);
1769 --
1770 -- 1) Get record from database.
1771 -- 2) If record not found then raise error.
1772 -- 3) Pass record to set_object routine.
1773 --
1774 open c1;
1775 --
1776 --115.23 - altered to use NOCOPY record
1777 fetch c1 into p_rec;
1778 if c1%notfound then
1779 --
1780 --115.23 - altered to use NOCOPY record
1781 --l_rec.person_id := p_person_id;
1782 p_rec.soft_coding_keyflex_id := p_soft_coding_keyflex_id;
1783 --
1784 end if;
1785 --
1786 close c1;
1787 --
1788 --115.23 - altered to use NOCOPY record
1789 --set_object(p_rec => l_rec);
1790 set_object(p_rec => p_rec);
1791 --
1792 -- 115.23 - unnecessary, using NOCOPY record
1793 --p_rec := l_rec;
1794 --
1795 -- hr_utility.set_location('Leaving '||l_proc,10);
1796 --
1797 end set_hsc_object;
1798 --
1799 procedure set_per_object(p_person_id in number,
1800 p_business_group_id in number,
1801 p_effective_date in date,
1802 p_rec in out nocopy per_all_people_f%rowtype) is
1803 --
1804 l_proc varchar2(80) := g_package||'set_per_object';
1805 l_index pls_integer;
1806 --
1807 cursor c1 is
1808 select ppf.*
1809 from per_all_people_f ppf
1810 where ppf.person_id = p_person_id
1811 and ppf.business_group_id = p_business_group_id
1812 and p_effective_date
1813 between ppf.effective_start_date
1814 and ppf.effective_end_date;
1815 --
1816 --l_rec per_all_people_f%rowtype;
1817 --115.23 altered to use NOCOPY record
1818 begin
1819 --
1820 -- hr_utility.set_location('Entering '||l_proc,10);
1821 --
1822 -- 1) Get record from database.
1823 -- 2) If record not found then raise error.
1824 -- 3) Pass record to set_object routine.
1825 --
1826 open c1;
1827 --
1828 -- 115.23 altered to use NOCOPY record
1829 fetch c1 into p_rec;
1830 if c1%notfound then
1831 --
1832 close c1;
1833 fnd_message.set_name('BEN','BEN_92308_OBJECT_NOT_FOUND');
1834 fnd_message.set_token('PROC',l_proc);
1835 fnd_message.set_token('PERSON_ID',p_person_id);
1836 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
1837 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1838 fnd_message.raise_error;
1839 --
1840 end if;
1841 --
1842 close c1;
1843 --115.23 - removed additional call
1844 --set_object(p_rec => p_rec);
1845 l_index := mod(p_person_id,g_hash_key);
1846 --
1847 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
1848 if g_cache_per_rec(l_index).person_id = p_person_id then
1849 -- do nothing, cache entry already exists
1850 null;
1851 else
1852 --
1853 -- Loop through the hash using the jump routine to check further
1854 -- indexes
1855 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
1856 --
1857 l_index := l_index+g_hash_jump;
1858 while g_cache_per_rec(l_index).person_id <> p_person_id loop
1859 --
1860 l_index := l_index+g_hash_jump;
1861
1862 end loop;
1863 --
1864 end if;
1865 --
1866 -- hr_utility.set_location('Leaving '||l_proc,10);
1867 --
1868 --
1869 --115.23 - altered to use NOCOPY record
1870 --p_rec := l_rec;
1871 --
1872 -- hr_utility.set_location('Leaving '||l_proc,10);
1873 --
1874 exception when NO_DATA_FOUND then
1875 -- set cache entry at current index location
1876 g_cache_per_rec(l_index):=p_rec;
1877 end set_per_object;
1878 --
1879 procedure set_pps_object
1880 (p_person_id in number,
1881 p_business_group_id in number,
1882 p_effective_date in date,
1883 p_rec in out nocopy per_periods_of_service%rowtype) is
1884 --
1885 l_proc varchar2(80) := g_package||'set_pps_object';
1886 l_index pls_integer;
1887 --
1888 cursor c1 is
1889 select pps.*
1890 from per_periods_of_service pps
1891 where pps.person_id = p_person_id
1892 and pps.business_group_id = p_business_group_id
1893 and p_effective_date >= pps.date_start
1894 order by pps.date_start desc;
1895 --
1896 --l_rec per_periods_of_service%rowtype;
1897 --115.23 - comment out unneeded record
1898 --
1902 --
1899 begin
1900 --
1901 -- hr_utility.set_location('Entering '||l_proc,10);
1903 -- 1) Get record from database.
1904 -- 2) If record not found then raise error.
1905 -- 3) Pass record to set_object routine.
1906 --
1907 open c1;
1908 --
1909 --115.23 - altered to use NOCOPY record
1910 fetch c1 into p_rec;
1911 if c1%notfound then
1912 --
1913 --115.23 - altered to use NOCOPY record
1914 p_rec.person_id := p_person_id;
1915 --
1916 end if;
1917 --
1918 close c1;
1919 --115.23 - removed additional call
1920 --set_object(p_rec => p_rec);
1921 l_index := mod(p_person_id,g_hash_key);
1922 --
1923 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
1924 if g_cache_pps_rec(l_index).person_id = p_person_id then
1925 -- do nothing, cache entry already exists
1926 null;
1927 else
1928 --
1929 -- Loop through the hash using the jump routine to check further
1930 -- indexes
1931 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
1932 --
1933 l_index := l_index+g_hash_jump;
1934 while g_cache_pps_rec(l_index).person_id <> p_person_id loop
1935 --
1936 l_index := l_index+g_hash_jump;
1937
1938 end loop;
1939 --
1940 end if;
1941 --
1942 -- hr_utility.set_location('Leaving '||l_proc,10);
1943 --
1944 --
1945 --115.23 - altered to use NOCOPY record
1946 --p_rec := l_rec;
1947 --
1948 -- hr_utility.set_location('Leaving '||l_proc,10);
1949 --
1950 exception when NO_DATA_FOUND then
1951 -- set cache entry at current index location
1952 g_cache_pps_rec(l_index):=p_rec;
1953 end set_pps_object;
1954 --
1955 procedure set_per_dates_object
1956 (p_person_id in number,
1957 p_business_group_id in number,
1958 p_effective_date in date,
1959 p_rec in out nocopy g_person_date_info_rec) is
1960 --
1961 l_proc varchar2(80) := g_package||'set_per_dates_object';
1962 l_index pls_integer;
1963 --
1964 cursor c1 is
1965 select min(ppf.effective_start_date)
1966 from per_all_people_f ppf
1967 where ppf.person_id = p_person_id
1968 and ppf.business_group_id = p_business_group_id;
1969 --
1970 cursor c2 is
1971 select min(paf.effective_start_date)
1972 from per_all_assignments_f paf
1973 where paf.person_id = p_person_id
1974 and paf.assignment_type <> 'C'
1975 and paf.business_group_id = p_business_group_id
1976 and paf.primary_flag = 'Y';
1977 --
1978 --l_rec g_person_date_info_rec;
1979 --
1980 begin
1981 --
1982 -- hr_utility.set_location('Entering '||l_proc,10);
1983 --
1984 -- 1) Get record from database.
1985 -- 2) If record not found then raise error.
1986 -- 3) Pass record to set_object routine.
1987 --
1988 --115.23 - altered to use NOCOPY record
1989 p_rec.person_id := p_person_id;
1990 --
1991 open c1;
1992 -- 115.23 - altered to use NOCOPY record
1993 fetch c1 into p_rec.min_per_effective_start_date;
1994 if c1%notfound then
1995 --
1996 close c1;
1997 fnd_message.set_name('BEN','BEN_92308_OBJECT_NOT_FOUND');
1998 fnd_message.set_token('PROC',l_proc);
1999 fnd_message.set_token('PERSON_ID',p_person_id);
2000 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
2001 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
2002 fnd_message.raise_error;
2003 --
2004 end if;
2005 --
2006 close c1;
2007 --
2008 open c2;
2009 --115.23 - altered to use NOCOPY record
2010 fetch c2 into p_rec.min_ass_effective_start_date;
2011 --
2012 close c2;
2013 l_index := mod(p_person_id,g_hash_key);
2014 --
2015 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
2016 if g_cache_date_rec(l_index).person_id = p_person_id then
2017 -- do nothing, cache entry already exists
2018 null;
2019 else
2020 --
2021 -- Loop through the hash using the jump routine to check further
2022 -- indexes
2023 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
2024 --
2025 l_index := l_index+g_hash_jump;
2026 while g_cache_date_rec(l_index).person_id <> p_person_id loop
2027 --
2028 l_index := l_index+g_hash_jump;
2029
2030 end loop;
2031 --
2032 end if;
2033 --
2034 -- hr_utility.set_location('Leaving '||l_proc,10);
2035 --
2036 exception when NO_DATA_FOUND then
2037 -- 115.23 set cache entry at current index location
2038 g_cache_date_rec(l_index):=p_rec;
2039 end set_per_dates_object;
2040 --
2041 procedure set_per_fte_object
2042 (p_assignment_id in number,
2043 p_business_group_id in number,
2044 p_effective_date in date,
2045 p_rec in out nocopy g_person_fte_info_rec) is
2046 --
2050 cursor c1(p_primary_flag varchar2) is
2047 l_proc varchar2(80) := g_package||'set_per_fte_object';
2048 l_index pls_integer;
2049 --
2051 select sum(pab.value)
2052 from per_assignments_f paf,
2053 per_assignment_budget_values_f pab
2054 where paf.assignment_id = p_assignment_id
2055 and paf.business_group_id = p_business_group_id
2056 and paf.primary_flag = nvl(p_primary_flag,paf.primary_flag)
2057 and p_effective_date
2058 between paf.effective_start_date
2059 and paf.effective_end_date
2060 and pab.business_group_id = paf.business_group_id
2061 and pab.assignment_id = paf.assignment_id
2062 and pab.unit = 'FTE'
2063 and p_effective_date
2064 between pab.effective_start_date
2065 and pab.effective_end_date;
2066 --
2067 -- Bug#2179708 - to sum for all the assignments, person_id needs to be joined
2068 cursor c2 is
2069 select sum(pab.value)
2070 from per_assignments_f paf,
2071 per_assignment_budget_values_f pab,
2072 per_assignments_f paf2
2073 where paf.assignment_id = p_assignment_id
2074 and paf.business_group_id = p_business_group_id
2075 and p_effective_date
2076 between paf.effective_start_date
2077 and paf.effective_end_date
2078 and paf.person_id = paf2.person_id
2079 and pab.business_group_id = paf2.business_group_id
2080 and pab.assignment_id = paf2.assignment_id
2081 and pab.unit = 'FTE'
2082 and p_effective_date
2083 between pab.effective_start_date
2084 and pab.effective_end_date
2085 and p_effective_date
2086 between paf2.effective_start_date
2087 and paf2.effective_end_date;
2088
2089 --l_rec g_person_fte_info_rec;
2090 --
2091 begin
2092 --
2093 -- hr_utility.set_location('Entering '||l_proc,10);
2094 --
2095 -- 1) Get record from database.
2096 -- 2) If record not found then raise error.
2097 -- 3) Pass record to set_object routine.
2098 --115.23 - altered to use NOCOPY record
2099 --l_rec.assignment_id := p_assignment_id;
2100 p_rec.assignment_id := p_assignment_id;
2101 --
2102 open c1('Y');
2103 --115.23 - altered to use NOCOPY record
2104 fetch c1 into p_rec.fte;
2105 --
2106 close c1;
2107 --
2108 open c2;
2109 --115.23 - altered to use NOCOPY record
2110 fetch c2 into p_rec.total_fte;
2111 --
2112 close c2;
2113 --115.23 - altered to use NOCOPY record
2114 --p_rec := l_rec;
2115 --
2116 -- hr_utility.set_location('Leaving '||l_proc,10);
2117 --
2118 l_index := mod(p_assignment_id,g_hash_key);
2119 --
2120 -- 115.23 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
2121 if g_cache_fte_rec(l_index).assignment_id = p_assignment_id then
2122 -- do nothing, cache entry already exists
2123 null;
2124 else
2125 --
2126 -- Loop through the hash using the jump routine to check further
2127 -- indexes
2128 -- 115.23 if none exists at current index the NO_DATA_FOUND expection will fire
2129 --
2130 l_index := l_index+g_hash_jump;
2131 while g_cache_fte_rec(l_index).assignment_id <> p_assignment_id loop
2132 --
2133 l_index := l_index+g_hash_jump;
2134
2135 end loop;
2136 --
2137 end if;
2138 --
2139 -- hr_utility.set_location('Leaving '||l_proc,10);
2140 --
2141 exception when NO_DATA_FOUND then
2142 -- 115.23 set cache entry at current index location
2143 g_cache_fte_rec(l_index):=p_rec;
2144 end set_per_fte_object;
2145 --
2146 -- Get object routines
2147 --
2148 procedure get_object(p_person_id in number,
2149 p_rec in out nocopy per_all_people_f%rowtype) is
2150 l_proc varchar2(80) := g_package||'get_object per';
2151 l_index pls_integer;
2152 l_env ben_env_object.g_global_env_rec_type;
2153 --l_rec per_all_people_f%rowtype;
2154 --
2155 -- FONM
2156 l_effective_date date;
2157 --
2158 begin
2159 --
2160 -- hr_utility.set_location('Entering '||l_proc,10);
2161 --
2162 if g_cache_last_per_rec.person_id = p_person_id then
2163 --
2164 p_rec := g_cache_last_per_rec;
2165 return;
2166 --
2167 end if;
2168 --
2169 -- 1) Get hashed index
2170 -- 2) If hashed index is correct person_id then return person_id
2171 -- 3) If hashed index is not correct person_id then check next index
2172 -- 4) Repest 3 until correct person_id found, if not found raise error.
2173 --
2174 -- Get hashed index value
2175 --
2176 l_index := mod(p_person_id,g_hash_key);
2177 --
2178 if g_cache_per_rec(l_index).person_id = p_person_id then
2179 --
2180 g_cache_last_per_rec := g_cache_per_rec(l_index);
2181 p_rec := g_cache_last_per_rec;
2182 --
2183 else
2184 --
2185 -- We need to loop through all the hashed indexes
2186 -- if none exists at current index the NO_DATA_FOUND expection will fire
2187 --
2191 l_index := l_index+g_hash_jump;
2188 l_index := l_index+g_hash_jump;
2189 while g_cache_per_rec(l_index).person_id <> p_person_id loop
2190 --
2192 --
2193 end loop;
2194 --
2195 g_cache_last_per_rec := g_cache_per_rec(l_index);
2196 p_rec := g_cache_last_per_rec;
2197 --
2198 end if;
2199 --
2200 -- hr_utility.set_location('Leaving '||l_proc,10);
2201 --
2202 exception
2203 --
2204 when no_data_found then
2205 --
2206 -- Defrag all person caches to grab back PGA memory
2207 --
2208 ben_person_object.defrag_caches;
2209 --
2210 ben_env_object.get(p_rec => l_env);
2211 --
2212 -- FONM
2213 --
2214 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2215 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2216 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2217 );
2218 --
2219 set_per_object(p_person_id => p_person_id,
2220 p_business_group_id => l_env.business_group_id,
2221 p_effective_date => l_effective_date,
2222 p_rec => p_rec);
2223 --
2224 g_cache_last_per_rec := p_rec;
2225 --
2226 --
2227 end get_object;
2228 --
2229 procedure get_object(p_bnfts_bal_id in number,
2230 p_rec in out nocopy ben_bnfts_bal_f%rowtype) is
2231 --
2232 l_proc varchar2(80) := g_package||'get_object bnb';
2233 l_index pls_integer;
2234 --l_not_hash_found boolean;
2235 l_env ben_env_object.g_global_env_rec_type;
2236 --l_rec ben_bnfts_bal_f%rowtype;
2237 -- FONM
2238 l_effective_date date;
2239 --
2240 --
2241 begin
2242 --
2243 -- hr_utility.set_location('Entering '||l_proc,10);
2244 --
2245 if g_cache_last_bnb_rec.bnfts_bal_id = p_bnfts_bal_id then
2246 --
2247 p_rec := g_cache_last_bnb_rec;
2248 return;
2249 --
2250 end if;
2251 --
2252 -- 1) Get hashed index
2253 -- 2) If hashed index is correct person_id then return person_id
2254 -- 3) If hashed index is not correct person_id then check next index
2255 -- 4) Repest 3 until correct person_id found, if not found raise error.
2256 --
2257 -- Get hashed index value
2258 --
2259 l_index := mod(p_bnfts_bal_id,g_hash_key);
2260 --
2261 if g_cache_bnb_rec(l_index).bnfts_bal_id = p_bnfts_bal_id then
2262 --
2263 g_cache_last_bnb_rec := g_cache_bnb_rec(l_index);
2264 p_rec := g_cache_last_bnb_rec;
2265 --
2266 else
2267 --
2268 -- We need to loop through all the hashed indexes
2269 -- if none exists at current index the NO_DATA_FOUND expection will fire
2270 --
2271 l_index := l_index+g_hash_jump;
2272 while g_cache_bnb_rec(l_index).bnfts_bal_id <> p_bnfts_bal_id loop
2273 --
2274 l_index := l_index+g_hash_jump;
2275 --
2276 end loop;
2277 --
2278 g_cache_last_bnb_rec := g_cache_bnb_rec(l_index);
2279 p_rec := g_cache_last_bnb_rec;
2280 --
2281 end if;
2282 --
2283 -- hr_utility.set_location('Leaving '||l_proc,10);
2284 --
2285 exception
2286 --
2287 when no_data_found then
2288 --
2289 ben_env_object.get(p_rec => l_env);
2290 --
2291 -- FONM
2292 --
2293 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2294 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2295 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2296 );
2297 --
2298 set_bnb_object(p_bnfts_bal_id => p_bnfts_bal_id,
2299 p_business_group_id => l_env.business_group_id,
2300 p_effective_date => l_effective_date, /* FONM
2301 nvl(l_env.lf_evt_ocrd_dt,
2302 l_env.effective_date),*/
2303 p_rec => p_rec);
2304 --
2305 g_cache_last_bnb_rec := p_rec;
2306 --p_rec := l_rec;
2307 --
2308 end get_object;
2309 --
2310 procedure get_object(p_soft_coding_keyflex_id in number,
2311 p_rec in out nocopy hr_soft_coding_keyflex%rowtype) is
2312 --
2313 l_proc varchar2(80) := g_package||'get_object hsc';
2314 l_index pls_integer;
2315 --l_not_hash_found boolean;
2316 --l_rec hr_soft_coding_keyflex%rowtype;
2317 --
2318 begin
2319 --
2320 -- hr_utility.set_location('Entering '||l_proc,10);
2321 --
2322 if g_cache_last_hsc_rec.soft_coding_keyflex_id = p_soft_coding_keyflex_id then
2323 --
2324 p_rec := g_cache_last_hsc_rec;
2325 return;
2326 --
2327 end if;
2328 --
2329 -- 1) Get hashed index
2330 -- 2) If hashed index is correct person_id then return person_id
2331 -- 3) If hashed index is not correct person_id then check next index
2335 --
2332 -- 4) Repest 3 until correct person_id found, if not found raise error.
2333 --
2334 -- Get hashed index value
2336 l_index := mod(p_soft_coding_keyflex_id,g_hash_key);
2337 --
2338 if g_cache_hsc_rec(l_index).soft_coding_keyflex_id = p_soft_coding_keyflex_id then
2339 --
2340 g_cache_last_hsc_rec := g_cache_hsc_rec(l_index);
2341 p_rec := g_cache_last_hsc_rec;
2342 --
2343 else
2344 --
2345 -- We need to loop through all the hashed indexes
2346 -- if none exists at current index the NO_DATA_FOUND expection will fire
2347 --
2348 l_index := l_index+g_hash_jump;
2349 while g_cache_hsc_rec(l_index).soft_coding_keyflex_id <> p_soft_coding_keyflex_id loop
2350 --
2351 l_index := l_index+g_hash_jump;
2352 --
2353 end loop;
2354 --
2355 g_cache_last_hsc_rec := g_cache_hsc_rec(l_index);
2356 p_rec := g_cache_last_hsc_rec;
2357 --
2358 end if;
2359 exception
2360 --
2361 when no_data_found then
2362 --
2363 set_hsc_object(p_soft_coding_keyflex_id => p_soft_coding_keyflex_id,
2364 p_rec => p_rec);
2365 --
2366 g_cache_last_hsc_rec := p_rec;
2367 --p_rec := l_rec;
2368 --
2369 end get_object;
2370 --
2371 procedure get_object(p_person_id in number,
2372 p_rec in out nocopy per_all_assignments_f%rowtype) is
2373 --
2374 l_proc varchar2(80) := g_package||'get_object asg';
2375 l_index pls_integer;
2376 --l_not_hash_found boolean;
2377 l_env ben_env_object.g_global_env_rec_type;
2378 --l_rec per_all_assignments_f%rowtype;
2379 -- FONM
2380 l_effective_date date;
2381 --
2382 --
2383 begin
2384 --
2385 -- hr_utility.set_location('Entering '||l_proc,10);
2386 --
2387 if g_cache_last_ass_rec.person_id = p_person_id then
2388 --
2389 p_rec := g_cache_last_ass_rec;
2390 return;
2391 --
2392 end if;
2393 --
2394 -- 1) Get hashed index
2395 -- 2) If hashed index is correct person_id then return assignment
2396 -- 3) If hashed index is not correct person_id then check next index
2397 -- 4) Repest 3 until correct person_id found, if not found raise error.
2398 --
2399 -- Get hashed index value
2400 --
2401 l_index := mod(p_person_id,g_hash_key);
2402 --
2403 if g_cache_ass_rec(l_index).person_id = p_person_id then
2404 --
2405 g_cache_last_ass_rec := g_cache_ass_rec(l_index);
2406 p_rec := g_cache_last_ass_rec;
2407 --
2408 else
2409 --
2410 -- We need to loop through all the hashed indexes
2411 -- if none exists at current index the NO_DATA_FOUND expection will fire
2412 --
2413 l_index := l_index+g_hash_jump;
2414 while g_cache_ass_rec(l_index).person_id <> p_person_id loop
2415 --
2416 l_index := l_index+g_hash_jump;
2417 --
2418 end loop;
2419 --
2420 g_cache_last_ass_rec := g_cache_ass_rec(l_index);
2421 p_rec := g_cache_last_ass_rec;
2422 --
2423 end if;
2424 exception
2425 --
2426 when no_data_found then
2427 --
2428 ben_env_object.get(p_rec => l_env);
2429 --
2430 -- FONM
2431 --
2432 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2433 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2434 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2435 );
2436 --
2437 set_ass_object(p_person_id => p_person_id,
2438 p_business_group_id => l_env.business_group_id,
2439 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
2440 l_env.effective_date),*/
2441 p_rec => p_rec);
2442 --
2443 g_cache_last_ass_rec := p_rec;
2444 --p_rec := l_rec;
2445 --
2446 end get_object;
2447 --
2448 procedure get_benass_object
2449 (p_person_id in number,
2450 p_rec in out nocopy per_all_assignments_f%rowtype) is
2451 --
2452 l_proc varchar2(80) := g_package||'get_object benass';
2453 l_index pls_integer;
2454 --l_not_hash_found boolean;
2455 l_env ben_env_object.g_global_env_rec_type;
2456 --l_rec per_all_assignments_f%rowtype;
2457 --
2458 -- FONM
2459 l_effective_date date;
2460 --
2461 begin
2462 --
2463 -- hr_utility.set_location('Entering '||l_proc,10);
2464 --
2465 if g_cache_last_benass_rec.person_id = p_person_id then
2466 --
2467 p_rec := g_cache_last_benass_rec;
2468 return;
2469 --
2470 end if;
2471 --
2472 -- 1) Get hashed index
2473 -- 2) If hashed index is correct person_id then return assignment
2474 -- 3) If hashed index is not correct person_id then check next index
2475 -- 4) Repest 3 until correct person_id found, if not found raise error.
2476 --
2477 -- Get hashed index value
2478 --
2482 --
2479 l_index := mod(p_person_id,g_hash_key);
2480 --
2481 if g_cache_benass_rec(l_index).person_id = p_person_id then
2483 g_cache_last_benass_rec := g_cache_benass_rec(l_index);
2484 p_rec := g_cache_last_benass_rec;
2485 --
2486 else
2487 --
2488 -- We need to loop through all the hashed indexes
2489 -- if none exists at current index the NO_DATA_FOUND expection will fire
2490 --
2491 l_index := l_index+g_hash_jump;
2492 while g_cache_benass_rec(l_index).person_id <> p_person_id loop
2493 --
2494 l_index := l_index+g_hash_jump;
2495 --
2496 end loop;
2497 --
2498 g_cache_last_benass_rec := g_cache_benass_rec(l_index);
2499 p_rec := g_cache_last_benass_rec;
2500 --
2501 end if;
2502 exception
2503 --
2504 when no_data_found then
2505 --
2506 ben_env_object.get(p_rec => l_env);
2507 --
2508 -- FONM
2509 --
2510 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2511 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2512 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2513 );
2514 --
2515 set_benass_object(p_person_id => p_person_id,
2516 p_business_group_id => l_env.business_group_id,
2517 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
2518 l_env.effective_date),*/
2519 p_rec => p_rec);
2520 --
2521 g_cache_last_benass_rec := p_rec;
2522 --p_rec := l_rec;
2523 --
2524 end get_benass_object;
2525 --
2526 procedure get_object(p_assignment_status_type_id in number,
2527 p_rec in out nocopy per_assignment_status_types%rowtype) is
2528 --
2529 l_proc varchar2(80) := g_package||'get_object ast';
2530 l_index pls_integer;
2531 l_not_hash_found boolean;
2532 l_rec per_assignment_status_types%rowtype;
2533 --
2534 begin
2535 --
2536 -- hr_utility.set_location('Entering '||l_proc,10);
2537 --
2538 if g_cache_last_ast_rec.assignment_status_type_id = p_assignment_status_type_id then
2539 --
2540 p_rec := g_cache_last_ast_rec;
2541 return;
2542 --
2543 end if;
2544 --
2545 -- 1) Get hashed index
2546 -- 2) If hashed index is correct person_id then return assignment
2547 -- 3) If hashed index is not correct person_id then check next index
2548 -- 4) Repest 3 until correct person_id found, if not found raise error.
2549 --
2550 -- Get hashed index value
2551 --
2552 l_index := mod(p_assignment_status_type_id,g_hash_key);
2553 --
2554 if g_cache_ast_rec(l_index).assignment_status_type_id = p_assignment_status_type_id then
2555 --
2556 g_cache_last_ast_rec := g_cache_ast_rec(l_index);
2557 p_rec := g_cache_last_ast_rec;
2558 --
2559 else
2560 --
2561 -- We need to loop through all the hashed indexes
2562 -- if none exists at current index the NO_DATA_FOUND expection will fire
2563 --
2564 l_index := l_index+g_hash_jump;
2565 while g_cache_ast_rec(l_index).assignment_status_type_id <> p_assignment_status_type_id loop
2566 --
2567 l_index := l_index+g_hash_jump;
2568 --
2569 end loop;
2570 --
2571 g_cache_last_ast_rec := g_cache_ast_rec(l_index);
2572 p_rec := g_cache_last_ast_rec;
2573 --
2574 end if;
2575 exception
2576 --
2577 when no_data_found then
2578 --
2579 set_ast_object(p_assignment_status_type_id => p_assignment_status_type_id,
2580 p_rec => p_rec);
2581 --
2582 g_cache_last_ast_rec := p_rec;
2583 --p_rec := l_rec;
2584 --
2585 end get_object;
2586 --
2587 procedure get_object(p_assignment_id in number,
2588 p_rec in out nocopy per_assignment_extra_info%rowtype) is
2589 --
2590 l_proc varchar2(80) := g_package||'get_object aei';
2591 l_index pls_integer;
2592 --l_not_hash_found boolean;
2593 --l_rec per_assignment_extra_info%rowtype;
2594 --
2595 begin
2596 --
2597 -- hr_utility.set_location('Entering '||l_proc,10);
2598 --
2599 if g_cache_last_aei_rec.assignment_id = p_assignment_id then
2600 --
2601 p_rec := g_cache_last_aei_rec;
2602 return;
2603 --
2604 end if;
2605 -- 1) Get hashed index
2606 -- 2) If hashed index is correct person_id then return assignment
2607 -- 3) If hashed index is not correct person_id then check next index
2608 -- 4) Repest 3 until correct person_id found, if not found raise error.
2609 --
2610 -- Get hashed index value
2611 --
2612 l_index := mod(p_assignment_id,g_hash_key);
2613 --
2614 if g_cache_aei_rec(l_index).assignment_id = p_assignment_id then
2615 --
2616 g_cache_last_aei_rec := g_cache_aei_rec(l_index);
2617 p_rec := g_cache_last_aei_rec;
2618 --
2619 else
2620 --
2624 l_index := l_index+g_hash_jump;
2621 -- We need to loop through all the hashed indexes
2622 -- if none exists at current index the NO_DATA_FOUND expection will fire
2623 --
2625 while g_cache_aei_rec(l_index).assignment_id <> p_assignment_id loop
2626 --
2627 l_index := l_index+g_hash_jump;
2628 --
2629 end loop;
2630 --
2631 g_cache_last_aei_rec := g_cache_aei_rec(l_index);
2632 p_rec := g_cache_last_aei_rec;
2633 --
2634 end if;
2635 exception
2636 --
2637 when no_data_found then
2638 --
2639 set_ass_object(p_assignment_id => p_assignment_id,
2640 p_rec => p_rec);
2641 --
2642 g_cache_last_aei_rec := p_rec;
2643 --p_rec := l_rec;
2644 --
2645 end get_object;
2646 --
2647 procedure get_object(p_person_id in number,
2648 -- added per_in_ler_id for unrestricted enhancement
2649 p_per_in_ler_id in number default null,
2650 p_rec in out nocopy ben_per_in_ler%rowtype) is
2651 --
2652 l_proc varchar2(80) := g_package||'get_object pil';
2653 l_index pls_integer;
2654 --l_not_hash_found boolean;
2655 l_env ben_env_object.g_global_env_rec_type;
2656 --l_rec ben_per_in_ler%rowtype;
2657 -- FONM
2658 l_effective_date date;
2659 --
2660 --
2661 begin
2662 --
2663 -- hr_utility.set_location('Entering '||l_proc,10);
2664 --
2665 if g_cache_last_pil_rec.person_id = p_person_id then
2666 --
2667 p_rec := g_cache_last_pil_rec;
2668 return;
2669 --
2670 end if;
2671 -- 1) Get hashed index
2672 -- 2) If hashed index is correct person_id then return assignment
2673 -- 3) If hashed index is not correct person_id then check next index
2674 -- 4) Repest 3 until correct person_id found, if not found raise error.
2675 --
2676 -- Get hashed index value
2677 --
2678 l_index := mod(p_person_id,g_hash_key);
2679 --
2680 if g_cache_pil_rec(l_index).person_id = p_person_id then
2681 --
2682 g_cache_last_pil_rec := g_cache_pil_rec(l_index);
2683 p_rec := g_cache_last_pil_rec;
2684 --
2685 else
2686 --
2687 -- We need to loop through all the hashed indexes
2688 -- if none exists at current index the NO_DATA_FOUND expection will fire
2689 --
2690 l_index := l_index+g_hash_jump;
2691 while g_cache_pil_rec(l_index).person_id <> p_person_id loop
2692 --
2693 l_index := l_index+g_hash_jump;
2694 --
2695 end loop;
2696 --
2697 g_cache_last_pil_rec := g_cache_pil_rec(l_index);
2698 p_rec := g_cache_last_pil_rec;
2699 --
2700 end if;
2701 exception
2702 --
2703 when no_data_found then
2704 --
2705 ben_env_object.get(p_rec => l_env);
2706 --
2707 -- FONM
2708 --
2709 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2710 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2711 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2712 );
2713 --
2714 set_pil_object(p_person_id => p_person_id,
2715 p_business_group_id => l_env.business_group_id,
2716 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
2717 l_env.effective_date),*/
2718 -- added per_in_ler_id for unrestricted enhancement
2719 p_per_in_ler_id => p_per_in_ler_id,
2720 p_rec => p_rec);
2721 --
2722 g_cache_last_pil_rec := p_rec;
2723 --p_rec := l_rec;
2724 --
2725 end get_object;
2726 --
2727 procedure get_object(p_person_id in number,
2728 p_rec in out nocopy per_addresses%rowtype) is
2729 --
2730 l_proc varchar2(80) := g_package||'get_object adr';
2731 l_index pls_integer;
2732 --l_not_hash_found boolean;
2733 l_env ben_env_object.g_global_env_rec_type;
2734 --l_rec per_addresses%rowtype;
2735 -- FONM
2736 l_effective_date date;
2737 --
2738 --
2739 begin
2740 --
2741 -- hr_utility.set_location('Entering '||l_proc,10);
2742 --
2743 if g_cache_last_pad_rec.person_id = p_person_id then
2744 --
2745 p_rec := g_cache_last_pad_rec;
2746 return;
2747 --
2748 end if;
2749 -- 1) Get hashed index
2750 -- 2) If hashed index is correct person_id then return assignment
2751 -- 3) If hashed index is not correct person_id then check next index
2752 -- 4) Repest 3 until correct person_id found, if not found raise error.
2753 --
2754 -- Get hashed index value
2755 --
2756 l_index := mod(p_person_id,g_hash_key);
2757 --
2758 if g_cache_pad_rec(l_index).person_id = p_person_id then
2759 --
2760 g_cache_last_pad_rec := g_cache_pad_rec(l_index);
2761 p_rec := g_cache_last_pad_rec;
2762 --
2763 else
2764 --
2765 -- We need to loop through all the hashed indexes
2769 while g_cache_pad_rec(l_index).person_id <> p_person_id loop
2766 -- if none exists at current index the NO_DATA_FOUND expection will fire
2767 --
2768 l_index := l_index+g_hash_jump;
2770 --
2771 l_index := l_index+g_hash_jump;
2772 --
2773 end loop;
2774 --
2775 g_cache_last_pad_rec := g_cache_pad_rec(l_index);
2776 p_rec := g_cache_last_pad_rec;
2777 --
2778 end if;
2779 exception
2780 --
2781 when no_data_found then
2782 --
2783 ben_env_object.get(p_rec => l_env);
2784 --
2785 -- FONM
2786 --
2787 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2788 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2789 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2790 );
2791 --
2792 set_pad_object(p_person_id => p_person_id,
2793 p_business_group_id => l_env.business_group_id,
2794 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
2795 l_env.effective_date), */
2796 p_rec => p_rec);
2797 --
2798 g_cache_last_pad_rec := p_rec;
2799 --p_rec := l_rec;
2800 --
2801 end get_object;
2802 --
2803 procedure get_object(p_person_id in number,
2804 p_rec in out nocopy per_periods_of_service%rowtype) is
2805 --
2806 l_proc varchar2(80) := g_package||'get_object pds';
2807 l_index pls_integer;
2808 --l_not_hash_found boolean;
2809 l_env ben_env_object.g_global_env_rec_type;
2810 --l_rec per_periods_of_service%rowtype;
2811 -- FONM
2812 l_effective_date date;
2813 --
2814 --
2815 begin
2816 --
2817 -- hr_utility.set_location('Entering '||l_proc,10);
2818 --
2819 if g_cache_last_pps_rec.person_id = p_person_id then
2820 --
2821 p_rec := g_cache_last_pps_rec;
2822 return;
2823 --
2824 end if;
2825 -- 1) Get hashed index
2826 -- 2) If hashed index is correct person_id then return pps
2827 -- 3) If hashed index is not correct person_id then check next index
2828 -- 4) Repest 3 until correct person_id found, if not found raise error.
2829 --
2830 -- Get hashed index value
2831 --
2832 l_index := mod(p_person_id,g_hash_key);
2833 --
2834 if g_cache_pps_rec(l_index).person_id = p_person_id then
2835 --
2836 g_cache_last_pps_rec := g_cache_pps_rec(l_index);
2837 p_rec := g_cache_last_pps_rec;
2838 --
2839 else
2840 --
2841 -- We need to loop through all the hashed indexes
2842 -- if none exists at current index the NO_DATA_FOUND expection will fire
2843 --
2844 l_index := l_index+g_hash_jump;
2845 while g_cache_pps_rec(l_index).person_id <> p_person_id loop
2846 --
2847 l_index := l_index+g_hash_jump;
2848 --
2849 end loop;
2850 --
2851 g_cache_last_pps_rec := g_cache_pps_rec(l_index);
2852 p_rec := g_cache_last_pps_rec;
2853 --
2854 end if;
2855 exception
2856 --
2857 when no_data_found then
2858 --
2859 ben_env_object.get(p_rec => l_env);
2860 --
2861 -- FONM
2862 --
2863 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2864 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2865 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2866 );
2867 --
2868 set_pps_object(p_person_id => p_person_id,
2869 p_business_group_id => l_env.business_group_id,
2870 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
2871 l_env.effective_date), */
2872 p_rec => p_rec);
2873 --
2874 g_cache_last_pps_rec := p_rec;
2875 --p_rec := l_rec;
2876 --
2877 end get_object;
2878 --
2879 procedure get_object(p_person_id in number,
2880 p_bnfts_bal_id in number,
2881 p_effective_date in date,
2882 p_rec in out nocopy ben_per_bnfts_bal_f%rowtype) is
2883 --
2884 l_proc varchar2(80) := g_package||'get_object bbb';
2885 l_index pls_integer;
2886 --l_not_hash_found boolean;
2887 l_env ben_env_object.g_global_env_rec_type;
2888 --l_rec ben_per_bnfts_bal_f%rowtype;
2889 -- FONM
2890 l_effective_date date;
2891 --
2892 --
2893 begin
2894 --
2895 -- hr_utility.set_location('Entering '||l_proc,10);
2896 --
2897 if g_cache_last_bal_rec.person_id = p_person_id and
2898 g_cache_last_bal_rec.bnfts_bal_id = p_bnfts_bal_id and
2899 nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2900 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt, p_effective_date))
2901 between g_cache_last_bal_rec.effective_start_date
2902 and g_cache_last_bal_rec.effective_end_date then
2903 --
2904 p_rec := g_cache_last_bal_rec;
2905 hr_utility.set_location('g_cache_last_bal_rec '||l_proc,10);
2909 -- 1) Get hashed index
2906 return;
2907 --
2908 end if;
2910 -- 2) If hashed index is correct person_id then return pps
2911 -- 3) If hashed index is not correct person_id then check next index
2912 -- 4) Repest 3 until correct person_id found, if not found raise error.
2913 --
2914 -- Get hashed index value
2915 --
2916 l_index := mod(p_person_id,g_hash_key);
2917 --
2918 if g_cache_bal_per_rec(l_index).id = p_person_id then
2919 --
2920 null;
2921 --
2922 else
2923 --
2924 -- We need to loop through all the hashed indexes
2925 -- if none exists at current index the NO_DATA_FOUND expection will fire
2926 --
2927 l_index := l_index+g_hash_jump;
2928 while g_cache_bal_per_rec(l_index).id <> p_person_id loop
2929 --
2930 l_index := l_index+g_hash_jump;
2931 --
2932 end loop;
2933 --
2934 --
2935 end if;
2936 --
2937 -- FONM
2938 --
2939 ben_env_object.get(p_rec => l_env);
2940 --
2941 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2942 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2943 p_effective_date)
2944 );
2945 --
2946 hr_utility.set_location('Before get_bal_per_object '||l_proc,10);
2947 get_bal_per_object
2948 (p_person_id => p_person_id,
2949 p_bnfts_bal_id => p_bnfts_bal_id,
2950 p_effective_date => l_effective_date,
2951 p_rec => p_rec);
2952 --
2953 g_cache_last_bal_rec := p_rec;
2954 --
2955 -- hr_utility.set_location('Leaving '||l_proc,10);
2956 --
2957 exception
2958 --
2959 when no_data_found then
2960 --
2961 ben_env_object.get(p_rec => l_env);
2962 --
2963 -- FONM
2964 --
2965 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
2966 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
2967 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
2968 );
2969 --
2970 set_bal_object(p_person_id => p_person_id,
2971 p_business_group_id => l_env.business_group_id,
2972 p_effective_date => l_effective_date); /* FONM nvl(l_env.lf_evt_ocrd_dt,
2973 l_env.effective_date)); */
2974 --
2975 get_bal_per_object
2976 (p_person_id => p_person_id,
2977 p_bnfts_bal_id => p_bnfts_bal_id,
2978 p_effective_date => l_effective_date,
2979 p_rec => p_rec);
2980 --
2981 g_cache_last_bal_rec := p_rec;
2982 --
2983 end get_object;
2984 --
2985 procedure get_object(p_person_id in number,
2986 p_rec in out nocopy g_cache_con_table) is
2987 --
2988 l_proc varchar2(80) := g_package||'get_object con';
2989 l_index pls_integer;
2990 --l_not_hash_found boolean;
2991 l_env ben_env_object.g_global_env_rec_type;
2992 --l_rec g_cache_con_table;
2993 -- FONM
2994 l_effective_date date;
2995 --
2996 --
2997 begin
2998 --
2999 -- hr_utility.set_location('Entering '||l_proc,10);
3000 --
3001 BEGIN
3002 if g_cache_last_con_rec(1).person_id = p_person_id then
3003 --115.23 no need for for loop, just make the assignment
3004 -- for l_count in g_cache_last_con_rec.first..
3005 -- g_cache_last_con_rec.last loop
3006 --
3007 -- p_rec(l_count) := g_cache_last_con_rec(l_count);
3008 p_rec := g_cache_last_con_rec;
3009 --
3010 -- end loop;
3011 --
3012 return;
3013 --
3014 end if;
3015 EXCEPTION WHEN OTHERS THEN
3016 NULL;
3017 END;
3018 --
3019 -- 1) Get hashed index
3020 -- 2) If hashed index is correct person_id then return pps
3021 -- 3) If hashed index is not correct person_id then check next index
3022 -- 4) Repest 3 until correct person_id found, if not found raise error.
3023 --
3024 -- Get hashed index value
3025 --
3026 l_index := mod(p_person_id,g_hash_key);
3027 --
3028 if g_cache_con_per_rec(l_index).id = p_person_id then
3029 --
3030 null;
3031 --
3032 else
3033 --
3034 -- We need to loop through all the hashed indexes
3035 -- if none exists at current index the NO_DATA_FOUND expection will fire
3036 --
3037 l_index := l_index+g_hash_jump;
3038 while g_cache_con_per_rec(l_index).id <> p_person_id loop
3039 --
3040 l_index := l_index+g_hash_jump;
3041 --
3042 end loop;
3043 --
3044 end if;
3045 --
3046 if g_cache_con_per_rec(l_index).starttorele_num is not null then
3047 --115.23 no need for delete, just assign later
3048 --g_cache_last_con_rec.delete;
3049 --
3050 for l_count in g_cache_con_per_rec(l_index).starttorele_num..
3051 g_cache_con_per_rec(l_index).endtorele_num loop
3052 --
3053 p_rec(p_rec.count+1) := g_cache_con_rec(l_count);
3054 --
3058 --
3055 end loop;
3056 --115.23 move assignment outside of loop, faster
3057 g_cache_last_con_rec:=p_rec;
3059 end if;
3060 --
3061 --p_rec := l_rec;
3062 --
3063 -- hr_utility.set_location('Leaving '||l_proc,10);
3064 --
3065 exception
3066 --
3067 when no_data_found then
3068 --
3069 ben_env_object.get(p_rec => l_env);
3070 --
3071 -- FONM
3072 --
3073 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3074 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3075 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3076 );
3077 --
3078 set_con_object(p_person_id => p_person_id,
3079 p_business_group_id => l_env.business_group_id,
3080 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
3081 l_env.effective_date), */
3082 p_rec => p_rec);
3083 --
3084 end get_object;
3085 --
3086 procedure get_object(p_person_id in number,
3087 p_rec in out nocopy g_cache_ass_table) is
3088 --
3089 l_proc varchar2(80) := g_package||'get_object ass';
3090 l_index pls_integer;
3091 --l_not_hash_found boolean;
3092 l_env ben_env_object.g_global_env_rec_type;
3093 --l_rec g_cache_ass_table;
3094 -- FONM
3095 l_effective_date date;
3096 --
3097 --
3098 begin
3099 --
3100 g_debug := hr_utility.debug_enabled;
3101 if g_debug then
3102 l_proc := g_package||'set_typ_object';
3103 hr_utility.set_location('Entering '||l_proc,10);
3104 end if;
3105 --
3106 begin
3107 if g_cache_last_appass_rec(1).person_id = p_person_id then
3108 --115.23 take out loop
3109 --for l_count in g_cache_last_appass_rec.first..
3110 --g_cache_last_appass_rec.last loop
3111 --
3112 p_rec := g_cache_last_appass_rec;
3113 --
3114 --end loop;
3115 --
3116 return;
3117 --
3118 end if;
3119 --
3120 exception when others then
3121 null;
3122 end;
3123 --
3124 -- 1) Get hashed index
3125 -- 2) If hashed index is correct person_id then return pps
3126 -- 3) If hashed index is not correct person_id then check next index
3127 -- 4) Repest 3 until correct person_id found, if not found raise error.
3128 --
3129 -- Get hashed index value
3130 --
3131 l_index := mod(p_person_id,g_hash_key);
3132 --
3133 if g_cache_app_ass_rec(l_index).id = p_person_id then
3134 --
3135 null;
3136 --
3137 else
3138 --
3139 -- We need to loop through all the hashed indexes
3140 -- if none exists at current index the NO_DATA_FOUND expection will fire
3141 --
3142 l_index := l_index+g_hash_jump;
3143 while g_cache_app_ass_rec(l_index).id <> p_person_id loop
3144 --
3145 l_index := l_index+g_hash_jump;
3146 --
3147 end loop;
3148 --
3149 end if;
3150 --
3151 if g_cache_app_ass_rec(l_index).starttorele_num is not null then
3152 --
3153 for l_count in g_cache_app_ass_rec(l_index).starttorele_num..
3154 g_cache_app_ass_rec(l_index).endtorele_num loop
3155 --
3156 p_rec(p_rec.count+1) := g_cache_appass_rec(l_count);
3157 --
3158 end loop;
3159 --115.23 move out of loop, faster
3160 g_cache_last_appass_rec:=p_rec;
3161 --
3162 end if;
3163 --115.23 no need
3164 --p_rec := l_rec;
3165 --
3166 if g_debug then
3167 hr_utility.set_location('Leaving '||l_proc,10);
3168 end if;
3169 --
3170 exception
3171 --
3172 when no_data_found then
3173 --
3174 ben_env_object.get(p_rec => l_env);
3175 --
3176 -- FONM
3177 --
3178 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3179 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3180 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3181 );
3182 --
3183 set_appass_object(p_person_id => p_person_id,
3184 p_business_group_id => l_env.business_group_id,
3185 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
3186 l_env.effective_date), */
3187 p_rec => p_rec);
3188 --
3189 end get_object;
3190 --
3191 procedure get_allass_object(p_person_id in number,
3192 p_rec in out nocopy g_cache_ass_table) is
3193 --
3194 l_proc varchar2(80) ;
3195 l_index pls_integer;
3196 --l_not_hash_found boolean;
3197 l_env ben_env_object.g_global_env_rec_type;
3198 --l_rec g_cache_ass_table;
3199 -- FONM
3200 l_effective_date date;
3201 --
3202 --
3203 begin
3204 --
3205 g_debug := hr_utility.debug_enabled;
3206 if g_debug then
3210 --
3207 l_proc := g_package||'get_allass_object ass';
3208 hr_utility.set_location('Entering '||l_proc,10);
3209 end if;
3211 begin
3212 if g_cache_last_allass_rec(1).person_id = p_person_id then
3213 --115.23 take out loop
3214 --for l_count in g_cache_last_appass_rec.first..
3215 --g_cache_last_appass_rec.last loop
3216 --
3217 p_rec := g_cache_last_allass_rec;
3218 --
3219 --end loop;
3220 --
3221 return;
3222 --
3223 end if;
3224 --
3225 exception when others then
3226 null;
3227 end;
3228 --
3229 -- 1) Get hashed index
3230 -- 2) If hashed index is correct person_id then return pps
3231 -- 3) If hashed index is not correct person_id then check next index
3232 -- 4) Repest 3 until correct person_id found, if not found raise error.
3233 --
3234 -- Get hashed index value
3235 --
3236 l_index := mod(p_person_id,g_hash_key);
3237 --
3238 if g_cache_all_ass_rec(l_index).id = p_person_id then
3239 --
3240 null;
3241 --
3242 else
3243 --
3244 -- We need to loop through all the hashed indexes
3245 -- if none exists at current index the NO_DATA_FOUND expection will fire
3246 --
3247 l_index := l_index+g_hash_jump;
3248 while g_cache_all_ass_rec(l_index).id <> p_person_id loop
3249 --
3250 l_index := l_index+g_hash_jump;
3251 --
3252 end loop;
3253 --
3254 end if;
3255 --
3256 if g_cache_all_ass_rec(l_index).starttorele_num is not null then
3257 --
3258 for l_count in g_cache_all_ass_rec(l_index).starttorele_num..
3259 g_cache_all_ass_rec(l_index).endtorele_num loop
3260 --
3261 p_rec(p_rec.count+1) := g_cache_allass_rec(l_count);
3262 --
3263 end loop;
3264 --115.23 move out of loop, faster
3265 g_cache_last_allass_rec:=p_rec;
3266 --
3267 end if;
3268 --115.23 no need
3269 --p_rec := l_rec;
3270 --
3271 if g_debug then
3272 hr_utility.set_location('Leaving '||l_proc,10);
3273 end if;
3274 --
3275 exception
3276 --
3277 when no_data_found then
3278 --
3279 ben_env_object.get(p_rec => l_env);
3280 --
3281 -- FONM
3282 --
3283 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3284 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3285 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3286 );
3287 --
3288 set_allass_object(p_person_id => p_person_id,
3289 p_business_group_id => l_env.business_group_id,
3290 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
3291 l_env.effective_date), */
3292 p_rec => p_rec);
3293 --
3294 end get_allass_object;
3295 --
3296 procedure get_object(p_person_id in number,
3297 p_rec in out nocopy g_cache_typ_table) is
3298 --
3299 l_proc varchar2(80) := g_package||'get_object typ';
3300 l_index pls_integer;
3301 --l_not_hash_found boolean;
3302 l_env ben_env_object.g_global_env_rec_type;
3303 --l_rec g_cache_typ_table;
3304 -- FONM
3305 l_effective_date date;
3306 --
3307 --
3308 begin
3309 --
3310 -- hr_utility.set_location('Entering '||l_proc,10);
3311 --
3312 begin
3313 --
3314 if g_cache_last_typ_rec(1).person_id = p_person_id then
3315 --115.23 remove loop, do straight assignment
3316 --for l_count in g_cache_last_typ_rec.first..
3317 --g_cache_last_typ_rec.last loop
3318 --
3319 p_rec := g_cache_last_typ_rec;
3320 --
3321 --end loop;
3322 --
3323 return;
3324 --
3325 end if;
3326 --
3327 exception when others then
3328 null;
3329 end;
3330 --
3331 -- 1) Get hashed index
3332 -- 2) If hashed index is correct person_id then return pps
3333 -- 3) If hashed index is not correct person_id then check next index
3334 -- 4) Repest 3 until correct person_id found, if not found raise error.
3335 --
3336 -- Get hashed index value
3337 --
3338 l_index := mod(p_person_id,g_hash_key);
3339 --
3340 if g_cache_typ_per_rec(l_index).id = p_person_id then
3341 --
3342 null;
3343 --
3344 else
3345 --
3346 -- We need to loop through all the hashed indexes
3347 -- if none exists at current index the NO_DATA_FOUND expection will fire
3348 --
3349 l_index := l_index+g_hash_jump;
3350 while g_cache_typ_per_rec(l_index).id <> p_person_id loop
3351 --
3352 l_index := l_index+g_hash_jump;
3353 --
3354 end loop;
3355 --
3356 end if;
3357 --
3358 if g_cache_typ_per_rec(l_index).starttorele_num is not null then
3359 --115.23 no need for delete, assign later
3360 --g_cache_last_typ_rec.delete;
3361 --
3365 p_rec(p_rec.count+1) := g_cache_typ_rec(l_count);
3362 for l_count in g_cache_typ_per_rec(l_index).starttorele_num..
3363 g_cache_typ_per_rec(l_index).endtorele_num loop
3364 --
3366 --
3367 end loop;
3368 --115.23 removed from loop, faster to directly assign
3369 g_cache_last_typ_rec:=p_rec;
3370 end if;
3371 --115.23 unnecessary
3372 --p_rec := l_rec;
3373 --
3374 -- hr_utility.set_location('Leaving '||l_proc,10);
3375 --
3376 exception
3377 --
3378 when no_data_found then
3379 --
3380 ben_env_object.get(p_rec => l_env);
3381 --
3382 -- FONM
3383 --
3384 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3385 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3386 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3387 );
3388 --
3389 set_typ_object(p_person_id => p_person_id,
3390 p_business_group_id => l_env.business_group_id,
3391 p_effective_date => l_effective_date, /* FONM nvl(l_env.lf_evt_ocrd_dt,
3392 l_env.effective_date), */
3393 p_rec => p_rec);
3394 --
3395 end get_object;
3396 --
3397 procedure get_bal_per_object
3398 (p_person_id in number,
3399 p_bnfts_bal_id in number,
3400 p_effective_date in date,
3401 p_rec in out nocopy ben_per_bnfts_bal_f%rowtype) is
3402 --
3403 l_proc varchar2(80) := g_package||'get_bal_per_object';
3404 l_index pls_integer;
3405 l_count pls_integer;
3406 --l_not_hash_found boolean;
3407 l_env ben_env_object.g_global_env_rec_type;
3408 l_rec ben_per_bnfts_bal_f%rowtype;
3409 l_start_index pls_integer;
3410 l_end_index pls_integer;
3411 --
3412 begin
3413 --
3414 -- hr_utility.set_location('Entering '||l_proc,10);
3415 --
3416 -- 1) Get hashed index
3417 -- 2) If hashed index is correct person_id then return pps
3418 -- 3) If hashed index is not correct person_id then check next index
3419 -- 4) Repest 3 until correct person_id found, if not found raise error.
3420 --
3421 -- Get hashed index value
3422 --
3423 l_index := mod(p_person_id,g_hash_key);
3424 --
3425 if g_cache_bal_per_rec(l_index).id = p_person_id then
3426 --
3427 l_start_index := g_cache_bal_per_rec(l_index).starttorele_num;
3428 l_end_index := g_cache_bal_per_rec(l_index).endtorele_num;
3429 --
3430 else
3431 --
3432 -- We need to loop through all the hashed indexes
3433 -- if none exists at current index the NO_DATA_FOUND expection will fire
3434 --
3435 l_index := l_index+g_hash_jump;
3436 while g_cache_bal_per_rec(l_index).id <> p_person_id loop
3437 --
3438 l_index := l_index+g_hash_jump;
3439 --
3440 end loop;
3441 --
3442 l_start_index := g_cache_bal_per_rec(l_index).starttorele_num;
3443 l_end_index := g_cache_bal_per_rec(l_index).endtorele_num;
3444 --
3445 end if;
3446 --
3447 --
3448 if l_start_index is null then
3449 --
3450 p_rec:=l_rec;
3451 --
3452 else
3453 --
3454 -- Lets loop through the rows and try and find a match
3455 --
3456 for l_count in l_start_index..l_end_index loop
3457 --
3458 if g_cache_bal_rec(l_count).bnfts_bal_id = p_bnfts_bal_id and
3459 p_effective_date
3460 between g_cache_bal_rec(l_count).effective_start_date
3461 and g_cache_bal_rec(l_count).effective_end_date then
3462 --
3463 p_rec := g_cache_bal_rec(l_count);
3464 exit;
3465 --
3466 end if;
3467 --
3468 end loop;
3469 --
3470 end if;
3471 --
3472 -- hr_utility.set_location('Leaving '||l_proc,10);
3473 --
3474 exception
3475 --
3476 when no_data_found then
3477 --
3478 fnd_message.set_name('BEN','BEN_92309_OBJECT_NOT_FOUND');
3479 fnd_message.set_token('PROC',l_proc);
3480 fnd_message.set_token('PERSON_ID',p_person_id);
3481 fnd_message.set_token('BNFTS_BAL_ID',p_bnfts_bal_id);
3482 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
3483 fnd_message.raise_error;
3484 --
3485 end get_bal_per_object;
3486 --
3487 procedure get_object(p_person_id in number,
3488 p_rec in out nocopy g_person_date_info_rec) is
3489 --
3490 l_proc varchar2(80) := g_package||'get_object pdi';
3491 l_index pls_integer;
3492 --l_not_hash_found boolean;
3493 l_env ben_env_object.g_global_env_rec_type;
3494 --l_rec g_person_date_info_rec;
3495 -- FONM
3496 l_effective_date date;
3497 --
3498 --
3499 begin
3500 --
3501 -- hr_utility.set_location('Entering '||l_proc,10);
3502 --
3503 if g_cache_last_date_rec.person_id = p_person_id then
3504 --
3505 p_rec := g_cache_last_date_rec;
3506 return;
3507 --
3508 end if;
3509 -- 1) Get hashed index
3513 --
3510 -- 2) If hashed index is correct person_id then return assignment
3511 -- 3) If hashed index is not correct person_id then check next index
3512 -- 4) Repest 3 until correct person_id found, if not found raise error.
3514 -- Get hashed index value
3515 --
3516 l_index := mod(p_person_id,g_hash_key);
3517 --
3518 if g_cache_date_rec(l_index).person_id = p_person_id then
3519 --
3520 g_cache_last_date_rec := g_cache_date_rec(l_index);
3521 p_rec := g_cache_last_date_rec;
3522 --
3523 else
3524 --
3525 -- We need to loop through all the hashed indexes
3526 -- if none exists at current index the NO_DATA_FOUND expection will fire
3527 --
3528 l_index := l_index+g_hash_jump;
3529 while g_cache_date_rec(l_index).person_id <> p_person_id loop
3530 --
3531 l_index := l_index+g_hash_jump;
3532 --
3533 end loop;
3534 --
3535 g_cache_last_date_rec := g_cache_date_rec(l_index);
3536 p_rec := g_cache_last_date_rec;
3537 --
3538 end if;
3539 --
3540 exception
3541 --
3542 when no_data_found then
3543 --
3544 ben_env_object.get(p_rec => l_env);
3545 --
3546 -- FONM
3547 --
3548 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3549 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3550 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3551 );
3552 --
3553 set_per_dates_object(p_person_id => p_person_id,
3554 p_business_group_id => l_env.business_group_id,
3555 p_effective_date => l_effective_date, /* FONMnvl(l_env.lf_evt_ocrd_dt,
3556 l_env.effective_date), */
3557 p_rec => p_rec);
3558 --
3559 g_cache_last_date_rec := p_rec;
3560 --p_rec := l_rec;
3561 --
3562 end get_object;
3563 --
3564 procedure get_object(p_assignment_id in number,
3565 p_rec in out nocopy g_person_fte_info_rec) is
3566 --
3567 l_proc varchar2(80) := g_package||'get_object pfte';
3568 l_index pls_integer;
3569 --l_not_hash_found boolean;
3570 l_env ben_env_object.g_global_env_rec_type;
3571 --l_rec g_person_fte_info_rec;
3572 -- FONM
3573 l_effective_date date;
3574 --
3575 --
3576 begin
3577 --
3578 -- hr_utility.set_location('Entering '||l_proc,10);
3579 --
3580 if g_cache_last_fte_rec.assignment_id = p_assignment_id then
3581 --
3582 p_rec := g_cache_last_fte_rec;
3583 return;
3584 --
3585 end if;
3586 -- 1) Get hashed index
3587 -- 2) If hashed index is correct person_id then return assignment
3588 -- 3) If hashed index is not correct person_id then check next index
3589 -- 4) Repest 3 until correct person_id found, if not found raise error.
3590 --
3591 -- Get hashed index value
3592 --
3593 l_index := mod(p_assignment_id,g_hash_key);
3594 --
3595 if g_cache_fte_rec(l_index).assignment_id = p_assignment_id then
3596 --
3597 g_cache_last_fte_rec := g_cache_fte_rec(l_index);
3598 p_rec := g_cache_last_fte_rec;
3599 --
3600 else
3601 --
3602 -- We need to loop through all the hashed indexes
3603 -- if none exists at current index the NO_DATA_FOUND expection will fire
3604 --
3605 l_index := l_index+g_hash_jump;
3606 while g_cache_fte_rec(l_index).assignment_id <> p_assignment_id loop
3607 --
3608 l_index := l_index+g_hash_jump;
3609 --
3610 end loop;
3611 --
3612 g_cache_last_fte_rec := g_cache_fte_rec(l_index);
3613 p_rec := g_cache_last_fte_rec;
3614 --
3615 end if;
3616 --
3617 exception
3618 --
3619 when no_data_found then
3620 --
3621 ben_env_object.get(p_rec => l_env);
3622 --
3623 -- FONM
3624 --
3625 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
3626 nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,
3627 nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date))
3628 );
3629 --
3630 set_per_fte_object(p_assignment_id => p_assignment_id,
3631 p_business_group_id => l_env.business_group_id,
3632 p_effective_date => l_effective_date, /* FONMnvl(l_env.lf_evt_ocrd_dt,
3633 l_env.effective_date), */
3634 p_rec => p_rec);
3635 --
3636 g_cache_last_fte_rec := p_rec;
3637 --p_rec := l_rec;
3638 --
3639 end get_object;
3640 --
3641 procedure clear_down_cache is
3642 --
3643 l_cache_last_per_rec per_all_people_f%rowtype;
3644 l_cache_last_ass_rec per_all_assignments_f%rowtype;
3645 l_cache_last_benass_rec per_all_assignments_f%rowtype;
3646 l_cache_last_ast_rec per_assignment_status_types%rowtype;
3647 l_cache_last_pps_rec per_periods_of_service%rowtype;
3648 l_cache_last_pad_rec per_addresses%rowtype;
3649 l_cache_last_pil_rec ben_per_in_ler%rowtype;
3650 l_cache_last_date_rec g_person_date_info_rec;
3651 l_cache_last_fte_rec g_person_fte_info_rec;
3652 l_cache_last_bal_rec ben_per_bnfts_bal_f%rowtype;
3653 l_cache_last_bnb_rec ben_bnfts_bal_f%rowtype;
3654 l_cache_last_hsc_rec hr_soft_coding_keyflex%rowtype;
3655 --
3656 begin
3657 --
3658 g_cache_per_rec.delete;
3659 g_cache_ass_rec.delete;
3660 g_cache_benass_rec.delete;
3661 g_cache_app_ass_rec.delete;
3662 --RCHASE
3663 g_cache_appass_rec.delete;
3664 g_cache_allass_rec.delete;
3665 --RCHASE
3666 g_cache_all_ass_rec.delete;
3667 g_cache_ast_rec.delete;
3668 g_cache_aei_rec.delete;
3669 g_cache_pps_rec.delete;
3670 g_cache_pad_rec.delete;
3671 g_cache_pil_rec.delete;
3672 g_cache_bal_rec.delete;
3673 g_cache_bnb_rec.delete;
3674 g_cache_hsc_rec.delete;
3675 g_cache_bal_per_rec.delete;
3676 g_cache_con_rec.delete;
3677 g_cache_con_per_rec.delete;
3678 g_cache_typ_rec.delete;
3679 g_cache_typ_per_rec.delete;
3680 g_cache_date_rec.delete;
3681 g_cache_fte_rec.delete;
3682 --
3683 -- Clear last cache records
3684 --
3685 g_cache_last_con_rec.delete;
3686 g_cache_last_per_rec := l_cache_last_per_rec;
3687 g_cache_last_ass_rec := l_cache_last_ass_rec;
3688 g_cache_last_appass_rec.delete;
3689 g_cache_last_allass_rec.delete;
3690 g_cache_last_benass_rec := l_cache_last_benass_rec;
3691 g_cache_last_ast_rec := l_cache_last_ast_rec;
3692 g_cache_last_pps_rec := l_cache_last_pps_rec;
3693 g_cache_last_pad_rec := l_cache_last_pad_rec;
3694 g_cache_last_bnb_rec := l_cache_last_bnb_rec;
3695 g_cache_last_pil_rec := l_cache_last_pil_rec;
3696 g_cache_last_hsc_rec := l_cache_last_hsc_rec;
3697 g_cache_last_date_rec := l_cache_last_date_rec;
3698 g_cache_last_fte_rec := l_cache_last_fte_rec;
3699 g_cache_last_typ_rec.delete;
3700 --
3701 end clear_down_cache;
3702 --
3703 procedure defrag_caches
3704 is
3705 --
3706 l_cache_last_per_rec per_all_people_f%rowtype;
3707 l_cache_last_ass_rec per_all_assignments_f%rowtype;
3708 l_cache_last_benass_rec per_all_assignments_f%rowtype;
3709 l_cache_last_ast_rec per_assignment_status_types%rowtype;
3710 l_cache_last_pps_rec per_periods_of_service%rowtype;
3711 l_cache_last_pad_rec per_addresses%rowtype;
3712 l_cache_last_pil_rec ben_per_in_ler%rowtype;
3713 l_cache_last_date_rec g_person_date_info_rec;
3714 l_cache_last_fte_rec g_person_fte_info_rec;
3715 l_cache_last_bal_rec ben_per_bnfts_bal_f%rowtype;
3716 l_cache_last_bnb_rec ben_bnfts_bal_f%rowtype;
3717 l_cache_last_hsc_rec hr_soft_coding_keyflex%rowtype;
3718 --
3719 begin
3720 --
3721 if g_cache_per_rec.count > 10
3722 then
3723 --
3724 clear_down_cache;
3725 --
3726 end if;
3727 --
3728 end defrag_caches;
3729 --
3730 end ben_person_object;
[Home] [Help]