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