1 package body ben_org_object as
2 /* $Header: benorgch.pkb 120.1 2005/05/31 04:10:12 rgajula noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Who What?
13 --------- --------- ---------- --------------------------------------------
14 115.0 09-Aug-99 GPERRY Created.
15 115.1 16-Aug-99 GPERRY Added nocopy compiler directive.
16 115.2 06 May 00 RChase Performance Modifications, replace exists,
17 add additional NOCOPY compiler directives,
18 remove additional record assignments.
19 115.3 29 Dec 00 Tmathers Fixed check_sql errors.
20 -----------------------------------------------------------------------------
21 */
22 --
23 g_package varchar2(30) := 'ben_org_object.';
24 g_hash_key number := ben_hash_utility.get_hash_key;
25 g_hash_jump number := ben_hash_utility.get_hash_jump;
26 --
27 -- Set object routines
28 --
29 procedure set_object(p_rec in out NOCOPY per_business_groups%rowtype) is
30 --
31 l_proc varchar2(80) := g_package||'set_object';
32 l_index pls_integer;
33 --l_not_hash_found boolean;
34 --
35 begin
36 --
37 --hr_utility.set_location('Entering '||l_proc,10);
38 --
39 -- 1) get hash index
40 -- 2) If hash index is not used use hash index
41 -- 3) If hash index is used and correct then do nothing
42 -- 4) If hash index is used and not correct then try next hash index
43 --
44 -- Get hashed index value
45 --
46 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.business_group_id);
47 -- 115.2 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
48 if g_cache_bus_rec(l_index).business_group_id = p_rec.business_group_id then
49 -- do nothing, cache entry already exists
50 null;
51 else
52 --
53 -- Loop through the hash using the jump routine to check further
54 -- indexes
55 -- 115.2 if none exists at current index the NO_DATA_FOUND expection will fire
56 --
57 l_index := l_index+g_hash_jump;
58 while g_cache_bus_rec(l_index).business_group_id <> p_rec.business_group_id loop
59 --
60 l_index := l_index+g_hash_jump;
61
62 end loop;
63 --
64 end if;
65 --
66 -- hr_utility.set_location('Leaving '||l_proc,10);
67 --
68 exception when NO_DATA_FOUND then
69 --115.2 set cache entry at current index location
70 g_cache_bus_rec(l_index):=p_rec;
71 --
72 end set_object;
73 --
74 procedure set_object(p_rec in out NOCOPY hr_all_organization_units%rowtype) is
75 --
76 l_proc varchar2(80) := g_package||'set_object';
77 l_index pls_integer;
78 --l_not_hash_found boolean;
79 --
80 begin
81 --
82 --hr_utility.set_location('Entering '||l_proc,10);
83 --
84 -- 1) get hash index
85 -- 2) If hash index is not used use hash index
86 -- 3) If hash index is used and correct then do nothing
87 -- 4) If hash index is used and not correct then try next hash index
88 --
89 -- Get hashed index value
90 --
91 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.organization_id);
92 -- 115.2 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
93 if g_cache_org_rec(l_index).organization_id = p_rec.organization_id then
94 -- do nothing, cache entry already exists
95 null;
96 else
97 --
98 -- Loop through the hash using the jump routine to check further
99 -- indexes
100 -- 115.2 if none exists at current index the NO_DATA_FOUND expection will fire
101 --
102 l_index := l_index+g_hash_jump;
103 while g_cache_org_rec(l_index).organization_id <> p_rec.organization_id loop
104 --
105 l_index := l_index+g_hash_jump;
106
107 end loop;
108 --
109 end if;
110 --
111 -- hr_utility.set_location('Leaving '||l_proc,10);
112 --
113 exception when NO_DATA_FOUND then
114 --115.2 set cache entry at current index location
115 g_cache_org_rec(l_index):=p_rec;
116 --
117 end set_object;
118 --
119 procedure set_object(p_rec in out NOCOPY pay_all_payrolls_f%rowtype) is
120 --
121 l_proc varchar2(80) := g_package||'set_object';
122 l_index pls_integer;
123 --l_not_hash_found boolean;
124 --
125 begin
126 --
127 --hr_utility.set_location('Entering '||l_proc,10);
128 --
129 -- 1) get hash index
130 -- 2) If hash index is not used use hash index
131 -- 3) If hash index is used and correct then do nothing
132 -- 4) If hash index is used and not correct then try next hash index
133 --
134 -- Get hashed index value
135 --
136 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.payroll_id);
137 -- 115.2 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
138 if g_cache_pay_rec(l_index).payroll_id = p_rec.payroll_id then
139 -- do nothing, cache entry already exists
140 null;
141 else
142 --
143 -- Loop through the hash using the jump routine to check further
144 -- indexes
145 -- 115.2 if none exists at current index the NO_DATA_FOUND expection will fire
146 --
147 l_index := l_index+g_hash_jump;
148 while g_cache_pay_rec(l_index).payroll_id <> p_rec.payroll_id loop
149 --
150 l_index := l_index+g_hash_jump;
151
152 end loop;
153 --
154 end if;
155 --
156 -- hr_utility.set_location('Leaving '||l_proc,10);
157 --
158 exception when NO_DATA_FOUND then
159 --115.2 set cache entry at current index location
160 g_cache_pay_rec(l_index):=p_rec;
161 --
162 end set_object;
163 --
164 procedure set_object(p_rec in out NOCOPY ben_benfts_grp%rowtype) is
165 --
166 l_proc varchar2(80) := g_package||'set_object';
167 l_index pls_integer;
168 --l_not_hash_found boolean;
169 --
170 begin
171 --
172 --hr_utility.set_location('Entering '||l_proc,10);
173 --
174 -- 1) get hash index
175 -- 2) If hash index is not used use hash index
176 -- 3) If hash index is used and correct then do nothing
177 -- 4) If hash index is used and not correct then try next hash index
178 --
179 -- Get hashed index value
180 --
181 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.benfts_grp_id);
182 -- 115.2 check for cache entry at current index. if none exists the NO_DATA_FOUND expection will fire
183 if g_cache_ben_rec(l_index).benfts_grp_id = p_rec.benfts_grp_id then
184 -- do nothing, cache entry already exists
185 null;
186 else
187 --
188 -- Loop through the hash using the jump routine to check further
189 -- indexes
190 -- 115.2 if none exists at current index the NO_DATA_FOUND expection will fire
191 --
192 l_index := l_index+g_hash_jump;
193 while g_cache_ben_rec(l_index).benfts_grp_id <> p_rec.benfts_grp_id loop
194 --
195 l_index := l_index+g_hash_jump;
196
197 end loop;
198 --
199 end if;
200 --
201 -- hr_utility.set_location('Leaving '||l_proc,10);
202 --
203 exception when NO_DATA_FOUND then
204 --115.2 set cache entry at current index location
205 g_cache_ben_rec(l_index):=p_rec;
206 --
207 end set_object;
208 --
209 -- Set object alternate route routines
210 --
211 procedure set_bus_object
212 (p_business_group_id in number,
213 p_effective_date in date,
214 p_rec in out nocopy per_business_groups%rowtype) is
215 --
216 l_proc varchar2(80) := g_package||'set_bus_object';
217 --
218 cursor c1 is
219 select bus.*
220 from per_business_groups bus
221 where bus.business_group_id = p_business_group_id
222 and p_effective_date
223 between bus.date_from
224 and nvl(bus.date_to,p_effective_date);
225 --115.2 remove additional declaration
226 --l_rec per_business_groups%rowtype;
227 --
228 begin
229 --
230 --hr_utility.set_location('Entering '||l_proc,10);
231 --
232 -- 1) Get record from database.
233 -- 2) If record not found then raise error.
234 -- 3) Pass record to set_object routine.
235 --
236 open c1;
237 --
238 fetch c1 into p_rec;
239 if c1%notfound then
240 --115.2 use NOCOPY parameter
241 --l_rec.business_group_id := p_business_group_id;
242 p_rec.business_group_id := p_business_group_id;
243 --
244 end if;
245 --
246 close c1;
247 --
248 set_object(p_rec => p_rec);
249 --115.2 remove additional assignment
250 --p_rec := l_rec;
251 --
252 --hr_utility.set_location('Leaving '||l_proc,10);
253 --
254 end set_bus_object;
255 --
256 procedure set_org_object
257 (p_organization_id in number,
258 p_effective_date in date,
259 p_rec in out nocopy hr_all_organization_units%rowtype) is
260 --
261 l_proc varchar2(80) := g_package||'set_org_object';
262 --
263 cursor c1 is
264 select org.*
265 from hr_all_organization_units org
266 where org.organization_id = p_organization_id
267 and p_effective_date
268 between org.date_from
269 and nvl(org.date_to,p_effective_date);
270 --115.2 remove additional declaration
271 --l_rec hr_all_organization_units%rowtype;
272 --
273 begin
274 --
275 --hr_utility.set_location('Entering '||l_proc,10);
276 --
277 -- 1) Get record from database.
278 -- 2) If record not found then raise error.
279 -- 3) Pass record to set_object routine.
280 --
281 open c1;
282 --
283 fetch c1 into p_rec;
284 if c1%notfound then
285 --115.2 use NOCOPY parameter
286 --l_rec.organization_id := p_organization_id;
287 p_rec.organization_id := p_organization_id;
288 --
289 end if;
290 --
291 close c1;
292 --
293 set_object(p_rec => p_rec);
294 --115.2 remove additional assignment
295 --p_rec := l_rec;
296 --
297 --hr_utility.set_location('Leaving '||l_proc,10);
298 --
299 end set_org_object;
300 --
301 procedure set_pay_object
302 (p_payroll_id in number,
303 p_business_group_id in number,
304 p_effective_date in date,
305 p_rec in out nocopy pay_all_payrolls_f%rowtype) is
306 --
307 l_proc varchar2(80) := g_package||'set_pay_object';
308 --
309 cursor c1 is
310 select pay.*
311 from pay_all_payrolls_f pay
312 where pay.payroll_id = p_payroll_id
313 and pay.business_group_id = p_business_group_id
314 and p_effective_date
315 between pay.effective_start_date
316 and pay.effective_end_date;
317 --115.2 remove additional declaration
318 --l_rec pay_all_payrolls_f%rowtype;
319 --
320 begin
321 --
322 --hr_utility.set_location('Entering '||l_proc,10);
323 --
324 -- 1) Get record from database.
325 -- 2) If record not found then raise error.
326 -- 3) Pass record to set_object routine.
327 --
328 open c1;
329 --
330 fetch c1 into p_rec;
331 if c1%notfound then
332 --115.2 use NOCOPY parameter
333 --l_rec.payroll_id := p_payroll_id;
334 p_rec.payroll_id := p_payroll_id;
335 --
336 end if;
337 --
338 close c1;
339 --
340 set_object(p_rec => p_rec);
341 --115.2 remove additional assignment
342 --p_rec := l_rec;
343 --
344 --hr_utility.set_location('Leaving '||l_proc,10);
345 --
346 end set_pay_object;
347 --
348 procedure set_ben_object
349 (p_benfts_grp_id in number,
350 p_business_group_id in number,
351 p_rec in out nocopy ben_benfts_grp%rowtype) is
352 --
353 l_proc varchar2(80) := g_package||'set_ben_object';
354 --
355 cursor c1 is
356 select ben.*
357 from ben_benfts_grp ben
358 where ben.benfts_grp_id = p_benfts_grp_id
359 and ben.business_group_id = p_business_group_id;
360 --115.2 remove additional declaration
361 --l_rec ben_benfts_grp%rowtype;
362 --
363 begin
364 --
365 --hr_utility.set_location('Entering '||l_proc,10);
366 --
367 -- 1) Get record from database.
368 -- 2) If record not found then raise error.
369 -- 3) Pass record to set_object routine.
370 --
371 open c1;
372 --
373 fetch c1 into p_rec;
374 if c1%notfound then
375 --115.2 use NOCOPY parameter
376 --l_rec.benfts_grp_id := p_benfts_grp_id;
377 p_rec.benfts_grp_id := p_benfts_grp_id;
378 --
379 end if;
380 --
381 close c1;
382 --
383 set_object(p_rec => p_rec);
384 --115.2 remove additional assignment
385 --p_rec := l_rec;
386 --
387 --hr_utility.set_location('Leaving '||l_proc,10);
388 --
389 end set_ben_object;
390 --
391 -- Get object routines
392 --
393 procedure get_object(p_business_group_id in number,
394 p_rec in out nocopy per_business_groups%rowtype) is
395 --
396 l_proc varchar2(80) := g_package||'get_object';
397 l_index pls_integer;
398 --l_not_hash_found boolean;
399 l_env ben_env_object.g_global_env_rec_type;
400 --l_rec per_business_groups%rowtype;
401 --
402 begin
403 --
404 --hr_utility.set_location('Entering '||l_proc,10);
405 --
406 if g_cache_last_bus_rec.business_group_id = p_business_group_id then
407 --
408 p_rec := g_cache_last_bus_rec;
409 return;
410 --
411 end if;
412 -- 1) Get hashed index
413 -- 2) If hashed index is correct pgm then return program
414 -- 3) If hashed index is not correct program then check next index
415 -- 4) Repest 3 until correct program found, if not found raise error.
416 --
417 -- Get hashed index value
418 --
419 l_index := ben_hash_utility.get_hashed_index(p_id => p_business_group_id);
420 --
421 if g_cache_bus_rec(l_index).business_group_id = p_business_group_id then
422 --
423 g_cache_last_bus_rec := g_cache_bus_rec(l_index);
424 p_rec := g_cache_last_bus_rec;
425 --
426 else
427 --
428 -- We need to loop through all the hashed indexes
429 -- if none exists at current index the NO_DATA_FOUND expection will fire
430 --
431 l_index := l_index+g_hash_jump;
432 while g_cache_bus_rec(l_index).business_group_id <> p_business_group_id loop
433 --
434 l_index := l_index+g_hash_jump;
435 --
436 end loop;
437 --
438 g_cache_last_bus_rec := g_cache_bus_rec(l_index);
439 p_rec := g_cache_last_bus_rec;
440 --
441 end if;
442 --
443 -- hr_utility.set_location('Leaving '||l_proc,10);
444 --
445 exception
446 --
447 when no_data_found then
448 --
449 ben_env_object.get(p_rec => l_env);
450 --
451 set_bus_object(p_business_group_id => p_business_group_id,
452 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
453 l_env.effective_date),
454 p_rec => p_rec);
455 --
456 g_cache_last_bus_rec := p_rec;
457 --p_rec := l_rec;
458 --
459 end get_object;
460 --
461 procedure get_object(p_organization_id in number,
462 p_rec in out nocopy hr_all_organization_units%rowtype) is
463 --
464 l_proc varchar2(80) := g_package||'get_object';
465 l_index pls_integer;
466 --l_not_hash_found boolean;
467 l_env ben_env_object.g_global_env_rec_type;
468 --l_rec hr_all_organization_units%rowtype;
469 --
470 begin
471 --
472 --hr_utility.set_location('Entering '||l_proc,10);
473 --
474 if g_cache_last_org_rec.organization_id = p_organization_id then
475 --
476 p_rec := g_cache_last_org_rec;
477 return;
478 --
479 end if;
480 -- 1) Get hashed index
481 -- 2) If hashed index is correct pgm then return program
482 -- 3) If hashed index is not correct program then check next index
483 -- 4) Repest 3 until correct program found, if not found raise error.
484 --
485 -- Get hashed index value
486 --
487 l_index := ben_hash_utility.get_hashed_index(p_id => p_organization_id);
488 --
489 if g_cache_org_rec(l_index).organization_id = p_organization_id then
490 --
491 g_cache_last_org_rec := g_cache_org_rec(l_index);
492 p_rec := g_cache_last_org_rec;
493 --
494 else
495 --
496 -- We need to loop through all the hashed indexes
497 -- if none exists at current index the NO_DATA_FOUND expection will fire
498 --
499 l_index := l_index+g_hash_jump;
500 while g_cache_org_rec(l_index).organization_id <> p_organization_id loop
501 --
502 l_index := l_index+g_hash_jump;
503 --
504 end loop;
505 --
506 g_cache_last_org_rec := g_cache_org_rec(l_index);
507 p_rec := g_cache_last_org_rec;
508 --
509 end if;
510 --
511 -- hr_utility.set_location('Leaving '||l_proc,10);
512 --
513 exception
514 --
515 when no_data_found then
516 --
517 ben_env_object.get(p_rec => l_env);
518 --
519 set_org_object(p_organization_id => p_organization_id,
520 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
521 l_env.effective_date),
522 p_rec => p_rec);
523 --
524 g_cache_last_org_rec := p_rec;
525 --p_rec := l_rec;
526 --
527 end get_object;
528 --
529 procedure get_object(p_payroll_id in number,
530 p_rec in out nocopy pay_all_payrolls_f%rowtype) is
531 --
532 l_proc varchar2(80) := g_package||'get_object';
533 l_index pls_integer;
534 --l_not_hash_found boolean;
535 l_env ben_env_object.g_global_env_rec_type;
536 --l_rec pay_all_payrolls_f%rowtype;
537 --
538 begin
539 --
540 --hr_utility.set_location('Entering '||l_proc,10);
541 --
542 if g_cache_last_pay_rec.payroll_id = p_payroll_id then
543 --
544 p_rec := g_cache_last_pay_rec;
545 return;
546 --
547 end if;
548 -- 1) Get hashed index
549 -- 2) If hashed index is correct pgm then return program
550 -- 3) If hashed index is not correct program then check next index
551 -- 4) Repest 3 until correct program found, if not found raise error.
552 --
553 -- Get hashed index value
554 --
555 l_index := ben_hash_utility.get_hashed_index(p_id => p_payroll_id);
556 --
557 if g_cache_pay_rec(l_index).payroll_id = p_payroll_id then
558 --
559 g_cache_last_pay_rec := g_cache_pay_rec(l_index);
560 p_rec := g_cache_last_pay_rec;
561 --
562 else
563 --
564 -- We need to loop through all the hashed indexes
565 -- if none exists at current index the NO_DATA_FOUND expection will fire
566 --
567 l_index := l_index+g_hash_jump;
568 while g_cache_pay_rec(l_index).payroll_id <> p_payroll_id loop
569 --
570 l_index := l_index+g_hash_jump;
571 --
572 end loop;
573 --
574 g_cache_last_pay_rec := g_cache_pay_rec(l_index);
575 p_rec := g_cache_last_pay_rec;
576 --
577 end if;
578 --
579 -- hr_utility.set_location('Leaving '||l_proc,10);
580 --
581 exception
582 --
583 when no_data_found then
584 --
585 ben_env_object.get(p_rec => l_env);
586 --
587 set_pay_object(p_payroll_id => p_payroll_id,
588 p_business_group_id => l_env.business_group_id,
589 p_effective_date => nvl(l_env.lf_evt_ocrd_dt,
590 l_env.effective_date),
591 p_rec => p_rec);
592 --
593 g_cache_last_pay_rec := p_rec;
594 --p_rec := l_rec;
595 --
596 end get_object;
597 --
598 procedure get_object(p_benfts_grp_id in number,
599 p_rec in out nocopy ben_benfts_grp%rowtype) is
600 --
601 l_proc varchar2(80) := g_package||'get_object';
602 l_index pls_integer;
603 --l_not_hash_found boolean;
604 l_env ben_env_object.g_global_env_rec_type;
605 --l_rec ben_benfts_grp%rowtype;
606 --
607 begin
608 --
609 --hr_utility.set_location('Entering '||l_proc,10);
610 --
611 if g_cache_last_ben_rec.benfts_grp_id = p_benfts_grp_id then
612 --
613 p_rec := g_cache_last_ben_rec;
614 return;
615 --
616 end if;
617 -- 1) Get hashed index
618 -- 2) If hashed index is correct pgm then return program
619 -- 3) If hashed index is not correct program then check next index
620 -- 4) Repest 3 until correct program found, if not found raise error.
621 --
622 -- Get hashed index value
623 --
624 l_index := ben_hash_utility.get_hashed_index(p_id => p_benfts_grp_id);
625 --
626 if g_cache_ben_rec(l_index).benfts_grp_id = p_benfts_grp_id then
627 --
628 g_cache_last_ben_rec := g_cache_ben_rec(l_index);
629 p_rec := g_cache_last_ben_rec;
630 --
631 else
632 --
633 -- We need to loop through all the hashed indexes
634 -- if none exists at current index the NO_DATA_FOUND expection will fire
635 --
636 l_index := l_index+g_hash_jump;
637 while g_cache_ben_rec(l_index).benfts_grp_id <> p_benfts_grp_id loop
638 --
639 l_index := l_index+g_hash_jump;
640 --
641 end loop;
642 --
643 g_cache_last_ben_rec := g_cache_ben_rec(l_index);
644 p_rec := g_cache_last_ben_rec;
645 --
646 end if;
647 --
648 -- hr_utility.set_location('Leaving '||l_proc,10);
649 --
650 exception
651 --
652 when no_data_found then
653 --
654 ben_env_object.get(p_rec => l_env);
655 --
656 set_ben_object(p_benfts_grp_id => p_benfts_grp_id,
657 p_business_group_id => l_env.business_group_id,
658 p_rec => p_rec);
659 --
660 g_cache_last_ben_rec := p_rec;
661 --p_rec := l_rec;
662 --
663 end get_object;
664 --
665 procedure clear_down_cache is
666 --
667 l_last_bus_rec per_business_groups%rowtype;
668 l_last_org_rec hr_all_organization_units%rowtype;
669 l_last_pay_rec pay_all_payrolls_f%rowtype;
670 l_last_ben_rec ben_benfts_grp%rowtype;
671 --
672 begin
673 --
674 g_cache_bus_rec.delete;
675 g_cache_last_bus_rec := l_last_bus_rec;
676 g_cache_org_rec.delete;
677 g_cache_last_org_rec := l_last_org_rec;
678 g_cache_pay_rec.delete;
679 g_cache_last_pay_rec := l_last_pay_rec;
680 g_cache_ben_rec.delete;
681 g_cache_last_ben_rec := l_last_ben_rec;
682 --
683 end clear_down_cache;
684 --
685 end ben_org_object;
[Home] [Help]