[Home] [Help]
PACKAGE BODY: APPS.BEN_RT_PRFL_CACHE
Source
1 package body ben_rt_prfl_cache AS
2 /* $Header: bertprch.pkb 120.0.12000000.2 2007/09/14 09:50:37 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 Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 24-Jun-99 bbulusu Created.
15 115.1 01-Jul-99 lmcdonal added ttl_prtt and ttl_cvg
16 115.2 16-Jul-99 lmcdonal Added set locations for debugging. Fixed
17 l_inst_query for rules and ttl_cvg.
18 115.3 16-Aug-99 stee Fixed loa_rsn to use
19 absence_attendance_type_id and
20 abs_attendance_reason_id.
21 115.4 27-SEP-99 GPerry Added missing no min value flags as these
22 are needed as part of the business rule
23 checks.
24 115.5 04-OCT-99 GPerry Backport of 115.2 with 115.4 fix.
25 115.6 04-OCT-99 GPerry Leapfrog of 115.4.
26 115.7 06-OCT-99 STee Added period of enrollment and disabled
27 criteria.
28 115.8 12 Nov 99 tguy added los_fctr_id and age/comp id for
29 factors criteria.
30 115.9 10 Feb 00 GPerry Flad was missing from cache.
31 WWBUG 1189087.
32 115.10 11-May-00 dcollins Performance enhancements, implemented
33 exception capturing instead of exists clauses
34 added "in out NOCOPY" to all set procs and
35 removed extra record assignment statements
36 115.11 29-May-00 mhoyes - Upgraded various get procedures to latest
37 cache on demand.
38 115.13 31-May-00 mhoyes - Fixed age and los problems.
39 115.14 15-Dec-00 Tmathers - Change calls for
40 ben_hash_utility.write_mastDet_Cache to
41 call ben_cache.write_mastDet_Cache.
42 WWBUG 1545633.
43 115.15 29-Dec-00 Tmathers - fixed check_sql errors
44 115.16 20-Mar-02 vsethi added dbdrv lines
45 115.17 29-Apr-02 pabodla Bug 1631182 : support user created
46 person type. Added person_type_id
47 parameter.
48 115.9 05-Jun-02 vsethi Added code to handle the new rates flags
49 115.10 12-Jun-02 vsethi Added code to handle the quartile and
50 performance rating
51 115.11 10-Sep-02 bmanyam changed the caching query for scheduled hrs as part of
52 'Range of Scheduled Hrs' Enhancement
53 115.21 10-feb-03 hnarayan Added NOCOPY Changes
54 115.22 ll-Apr-03 pbodla FONM : cache is built using the fonm dates.
55 115.23 l7-Apr-03 pbodla get_eff_date : removed date_to_canonical call
56 kmahendr
57 115.24 13-Sep-07 rgajula Bug 6412287 Additional global tables g_poe_lookup, g_poe_instance cleared in procedure clear_down_cache
58 -----------------------------------------------------------------------------
59 */
60 --
61 -- Globals.
62 --
63 g_package varchar2(50) := 'ben_rt_prfl_cache.';
64 --
65 -- ----------------------------------------------------------------------------
66 -- |-----------------------< get_cached_data >--------------------------------|
67 -- ----------------------------------------------------------------------------
68 --
69 -- This procedure returns data that is already in the cache.
70 --
71 -- Parameter Name Comments
72 -- -------------- ------------------------------
73 -- p_vrbl_rt_prfl_id The variable rate profile id for the fetch.
74 -- p_lookup_name The name of type of master cache.
75 -- p_inst_name Name of the type of detail cache.
76 -- p_inst_set_type The data type of the detail cache structure.
77 -- p_out_inst_name Name of the global to which the anynymous dynamic SQL
78 -- block below will write the output data.
79 --
80 procedure get_cached_data
81 (p_vrbl_rt_prfl_id in number
82 ,p_lookup_name in varchar2
83 ,p_inst_name in varchar2
84 ,p_inst_set_type in varchar2
85 ,p_out_inst_name in varchar2
86 )
87 is
88 l_plsql_str long;
89 l_proc varchar2(80) := g_package || '.get_cached_data';
90
91 begin
92 --
93 g_inst_count := 0;
94 --
95 -- Build the plsql string for dynamic SQL
96 --
97 l_plsql_str :=
98 'DECLARE ' ||
99 'l_torrwnum binary_integer; ' ||
100 'l_insttorrw_num binary_integer; ' ||
101 'l_master_hv binary_integer; ' ||
102 'l_hash_found boolean; ' ||
103 'l_entry_exists boolean; ' ||
104 'l_inst_set ' || p_inst_set_type || '; ' ||
105 'BEGIN ' ||
106 --
107 -- Hash the master id
108 --
109 'l_master_hv := mod(' || to_char(p_vrbl_rt_prfl_id) ||
110 ', ben_hash_utility.get_hash_key);' ||
111 --
112 -- Check if hashed value is already cached
113 --
114 'begin ' ||
115 --
116 -- Poke cache for existence of hash value
117 --
118 'l_entry_exists := true; ' ||
119 'if (' || p_lookup_name || '(l_master_hv).starttorele_num = 0) then ' ||
120 'null; ' ||
121 'end if; ' ||
122 'if (l_entry_exists = true) then ' ||
123 --
124 -- If it does exist make sure it corresponds to the master id
125 --
126 'if ' || p_lookup_name || '(l_master_hv).id <> ' ||
127 to_char(p_vrbl_rt_prfl_id) || ' then ' ||
128 --
129 'l_hash_found := FALSE; ' ||
130 --
131 -- Loop until un-allocated has value is derived
132 --
133 'while l_hash_found = FALSE loop ' ||
134 --
135 'l_master_hv := l_master_hv + ben_hash_utility.get_hash_jump; ' ||
136 --
137 -- Check if the hash index exists, if not we can use it
138 --
139 'l_entry_exists := true; ' ||
140 'if (' || p_lookup_name || '(l_master_hv).starttorele_num = 0) then ' ||
141 'null; ' ||
142 'end if; ' ||
143 'if (l_entry_exists = false) then ' ||
144 --
145 -- Lets store the hash value in the index
146 --
147 'l_hash_found := TRUE; ' ||
148 'exit;' ||
149 --
150 'else ' ||
151 --
152 'l_hash_found := FALSE; ' ||
153 --
154 'end if; ' ||
155 --
156 'end loop; ' ||
157 --
158 'end if; ' ||
159 --
160 'end if; ' ||
161 'exception when NO_DATA_FOUND then ' ||
162 'l_entry_exists := false; ' ||
163 'end; ' ||
164 --
165 -- Get the instance details
166 --
167 -- Populate the detail instances based on the range for the master id
168 -- hashed value in the lookup cache i.e. between starttorele_num and
169 -- endtorele_num
170 --
171 'l_torrwnum := 0; ' ||
172 --
173 'for l_insttorrw_num in ' ||
174 p_lookup_name || '(l_master_hv).starttorele_num .. ' ||
175 p_lookup_name || '(l_master_hv).endtorele_num loop ' ||
176 --
177 p_out_inst_name || '(l_torrwnum) := ' ||
178 p_inst_name || '(l_insttorrw_num); ' ||
179 --
180 'l_torrwnum := l_torrwnum + 1; ' ||
181 --
182 'end loop; ' ||
183 --
184 'ben_rt_prfl_cache.g_inst_count := l_torrwnum; ' ||
185 --
186 'END;';
187
188 EXECUTE IMMEDIATE l_plsql_str;
189
190 end get_cached_data;
191 --
192 -- ----------------------------------------------------------------------------
193 -- |---------------------------< get_eff_date >-----------------------------------|
194 -- ----------------------------------------------------------------------------
195 --
196 -- This function returns the date string to use.
197 --
198 function get_eff_date
199 (p_lf_evt_ocrd_dt in date
200 ,p_effective_date in date)
201 return date
202 is
203 l_todate date;
204 begin
205 --
206 -- FONM
207 if ben_manage_life_events.fonm = 'Y' then
208 --
209 if ben_manage_life_events.g_fonm_rt_strt_dt is not null then
210 --
211 l_todate := ben_manage_life_events.g_fonm_rt_strt_dt;
212 --
213 elsif ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
214 --
215 l_todate := ben_manage_life_events.g_fonm_cvg_strt_dt;
216 --
217 elsif p_lf_evt_ocrd_dt is not null then
218 --
219 l_todate := p_lf_evt_ocrd_dt;
220 --
221 else
222 --
223 l_todate := p_effective_date ;
224 --
225 end if;
226 --
227 else
228 if p_lf_evt_ocrd_dt is not null then
229 --
230 l_todate := p_lf_evt_ocrd_dt;
231 --
232 else
233 --
234 l_todate := p_effective_date;
235 --
236 end if;
237 end if;
238 --
239 return l_todate;
240 --
241 end get_eff_date;
242 --
243 -- ----------------------------------------------------------------------------
244 -- |---------------------------< date_str >-----------------------------------|
245 -- ----------------------------------------------------------------------------
246 --
247 -- This function returns the date string to use.
248 --
249 function date_str
250 (p_lf_evt_ocrd_dt in date
251 ,p_effective_date in date)
252 return varchar2
253 is
254 l_todate_str varchar2(1000);
255 begin
256 --
257 -- FONM
258 if ben_manage_life_events.fonm = 'Y' then
259 --
260 if ben_manage_life_events.g_fonm_rt_strt_dt is not null then
261 --
262 l_todate_str :=
263 ' to_date(''' || fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt) ||
264 ''', ''' || fnd_date.canonical_dt_mask || ''')';
265 --
266 elsif ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
267 --
268 l_todate_str :=
269 ' to_date(''' || fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt) ||
270 ''', ''' || fnd_date.canonical_dt_mask || ''')';
271 --
272 elsif p_lf_evt_ocrd_dt is not null then
273 --
274 l_todate_str :=
275 ' to_date(''' || fnd_date.date_to_canonical(p_lf_evt_ocrd_dt) ||
276 ''', ''' || fnd_date.canonical_dt_mask || ''')';
277 --
278 else
279 --
280 l_todate_str :=
281 ' to_date(''' || fnd_date.date_to_canonical(p_effective_date) ||
282 ''', ''' || fnd_date.canonical_dt_mask || ''')';
283
284 end if;
285 --
286 else
287 if p_lf_evt_ocrd_dt is not null then
288 --
289 l_todate_str :=
290 ' to_date(''' || fnd_date.date_to_canonical(p_lf_evt_ocrd_dt) ||
291 ''', ''' || fnd_date.canonical_dt_mask || ''')';
292 --
293 else
294 --
295 l_todate_str :=
296 ' to_date(''' || fnd_date.date_to_canonical(p_effective_date) ||
297 ''', ''' || fnd_date.canonical_dt_mask || ''')';
298 --
299 end if;
300 end if;
301 --
302 return l_todate_str;
303 --
304 end date_str;
305 --
306 -- ----------------------------------------------------------------------------
307 -- |-----------------------< get_rt_prfl_cache >------------------------------|
308 -- ----------------------------------------------------------------------------
309 --
310 -- Following are overloaded procedures that get the appropriate caches.
311 --
312 -- PEOPLE GROUP
313 --
314 procedure get_rt_prfl_cache
315 (p_vrbl_rt_prfl_id in number
316 ,p_effective_date in date
317 ,p_lf_evt_ocrd_dt in date
318 ,p_business_group_id in number
319 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pg_inst_tbl
320 ,p_inst_count out nocopy number)
321 is
322 --
323 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache people group';
324 l_lookup_query long;
325 l_inst_query long;
326 l_instcolnm_set ben_cache.instcolnmtype;
327 --
328 begin
329 --
330 g_pg_out.delete;
331 --
332 if g_pg_lookup.count = 0 then
333 --
334 -- Cache not populated yet. So populate it now.
335 --
336 l_lookup_query :=
337 'select vrbl_rt_prfl_id, business_group_id' ||
338 ' from ben_vrbl_rt_prfl_f vpf' ||
339 ' where business_group_id = ' || to_char(p_business_group_id) ||
340 ' and exists (select null' ||
341 ' from ben_ppl_grp_rt_f pgr' ||
342 ' where pgr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
343 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
344 ' between pgr.effective_start_date' ||
345 ' and pgr.effective_end_date) ' ||
346 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
347 ' between vpf.effective_start_date and vpf.effective_end_date;';
348 --
349 l_inst_query :=
350 'select pgr.vrbl_rt_prfl_id, pgr.people_group_id, pgr.excld_flag ,' ||
351 ' ppg.segment1 ,ppg.segment2 ,ppg.segment3 ,ppg.segment4 ,ppg.segment5 ,' ||
352 ' ppg.segment6 ,ppg.segment7 ,ppg.segment8 ,ppg.segment9, ppg.segment10 ,ppg.segment11 ,' ||
353 ' ppg.segment12 ,ppg.segment13 ,ppg.segment14 ,ppg.segment15 ,ppg.segment16 ,'||
354 ' ppg.segment17 ,ppg.segment18 ,ppg.segment19 ,ppg.segment20 ,ppg.segment21 ,'||
355 ' ppg.segment22 ,ppg.segment23 ,ppg.segment24 ,ppg.segment25 ,ppg.segment26 ,'||
356 ' ppg.segment27 ,ppg.segment28 ,ppg.segment29 ,ppg.segment30 ' ||
357 ' from ben_ppl_grp_rt_f pgr , pay_people_groups ppg ' ||
358 ' where pgr.business_group_id = ' || to_char(p_business_group_id) ||
359 ' and pgr.people_group_id = ppg.people_group_id ' ||
360 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
361 ' between pgr.effective_start_date' ||
362 ' and pgr.effective_end_date' ||
363 ' order by pgr.vrbl_rt_prfl_id;';
364 --
365 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
366 l_instcolnm_set(0).caccol_name := 'id';
367 l_instcolnm_set(1).col_name := 'people_group_id';
368 l_instcolnm_set(1).caccol_name := 'people_group_id';
369 l_instcolnm_set(2).col_name := 'excld_flag';
370 l_instcolnm_set(2).caccol_name := 'excld_flag';
371 l_instcolnm_set(3).col_name := 'SEGMENT1';
372 l_instcolnm_set(3).caccol_name := 'SEGMENT1';
373 l_instcolnm_set(4).col_name := 'SEGMENT2';
374 l_instcolnm_set(4).caccol_name := 'SEGMENT2';
375
376 l_instcolnm_set(5).col_name := 'SEGMENT3';
377 l_instcolnm_set(5).caccol_name := 'SEGMENT3';
378 l_instcolnm_set(6).col_name := 'SEGMENT4';
379 l_instcolnm_set(6).caccol_name := 'SEGMENT4';
380 l_instcolnm_set(7).col_name := 'SEGMENT5';
381 l_instcolnm_set(7).caccol_name := 'SEGMENT5';
382 l_instcolnm_set(8).col_name := 'SEGMENT6';
383 l_instcolnm_set(8).caccol_name := 'SEGMENT6';
384 l_instcolnm_set(9).col_name := 'SEGMENT7';
385 l_instcolnm_set(9).caccol_name := 'SEGMENT7';
386
387 l_instcolnm_set(10).col_name := 'SEGMENT8';
388 l_instcolnm_set(10).caccol_name := 'SEGMENT8';
389 l_instcolnm_set(11).col_name := 'SEGMENT9';
390 l_instcolnm_set(11).caccol_name := 'SEGMENT9';
391 l_instcolnm_set(12).col_name := 'SEGMENT10';
392 l_instcolnm_set(12).caccol_name := 'SEGMENT10';
393 l_instcolnm_set(13).col_name := 'SEGMENT11';
394 l_instcolnm_set(13).caccol_name := 'SEGMENT11';
395 l_instcolnm_set(14).col_name := 'SEGMENT12';
396 l_instcolnm_set(14).caccol_name := 'SEGMENT12';
397 l_instcolnm_set(15).col_name := 'SEGMENT13';
398 l_instcolnm_set(15).caccol_name := 'SEGMENT13';
399
400 l_instcolnm_set(16).caccol_name := 'SEGMENT14';
401 l_instcolnm_set(16).col_name := 'SEGMENT14';
402
403 l_instcolnm_set(17).caccol_name := 'SEGMENT15';
404 l_instcolnm_set(17).col_name := 'SEGMENT15';
405
406 l_instcolnm_set(18).caccol_name := 'SEGMENT16';
407 l_instcolnm_set(18).col_name := 'SEGMENT16';
408
409 l_instcolnm_set(19).caccol_name := 'SEGMENT17';
410 l_instcolnm_set(19).col_name := 'SEGMENT17';
411
412 l_instcolnm_set(20).caccol_name := 'SEGMENT18';
413 l_instcolnm_set(20).col_name := 'SEGMENT18';
414
415 l_instcolnm_set(21).caccol_name := 'SEGMENT19';
416 l_instcolnm_set(21).col_name := 'SEGMENT19';
417
418 l_instcolnm_set(22).caccol_name := 'SEGMENT20';
419 l_instcolnm_set(22).col_name := 'SEGMENT20';
420
421 l_instcolnm_set(23).caccol_name := 'SEGMENT21';
422 l_instcolnm_set(23).col_name := 'SEGMENT21';
423
424 l_instcolnm_set(24).caccol_name := 'SEGMENT22';
425 l_instcolnm_set(24).col_name := 'SEGMENT22';
426
427 l_instcolnm_set(25).caccol_name := 'SEGMENT23';
428 l_instcolnm_set(25).col_name := 'SEGMENT23';
429
430 l_instcolnm_set(26).caccol_name := 'SEGMENT24';
431 l_instcolnm_set(26).col_name := 'SEGMENT24';
432
433 l_instcolnm_set(27).caccol_name := 'SEGMENT25';
434 l_instcolnm_set(27).col_name := 'SEGMENT25';
435
436 l_instcolnm_set(28).caccol_name := 'SEGMENT26';
437 l_instcolnm_set(28).col_name := 'SEGMENT26';
438
439 l_instcolnm_set(29).caccol_name := 'SEGMENT27';
440 l_instcolnm_set(29).col_name := 'SEGMENT27';
441
442 l_instcolnm_set(30).caccol_name := 'SEGMENT28';
443 l_instcolnm_set(30).col_name := 'SEGMENT28';
444
445 l_instcolnm_set(31).caccol_name := 'SEGMENT29';
446 l_instcolnm_set(31).col_name := 'SEGMENT29';
447
448 l_instcolnm_set(32).caccol_name := 'SEGMENT30';
449 l_instcolnm_set(32).col_name := 'SEGMENT30';
450
451 --
452 ben_cache.write_mastDet_Cache
453 (p_mastercol_name => 'vrbl_rt_prfl_id'
454 ,p_detailcol_name => 'vrbl_rt_prfl_id'
455 ,p_lkup_name => 'ben_rt_prfl_cache.g_pg_lookup'
456 ,p_inst_name => 'ben_rt_prfl_cache.g_pg_instance'
457 ,p_lkup_query => l_lookup_query
458 ,p_inst_query => l_inst_query
459 ,p_instcolnm_set => l_instcolnm_set
460 );
461 --
462 end if;
463 --
464 -- Cache already populated. Get record set.
465 --
466 get_cached_data
467 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
468 ,p_lookup_name => 'ben_rt_prfl_cache.g_pg_lookup'
469 ,p_inst_name => 'ben_rt_prfl_cache.g_pg_instance'
470 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pg_inst_tbl'
471 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pg_out'
472 );
473 --
474 p_inst_set := g_pg_out;
475 p_inst_count := g_inst_count;
476 --
477 exception
478 --
479 when no_data_found then
480 --
481 p_inst_count := 0;
482 hr_utility.set_location('No Person Groups found', 90);
483 hr_utility.set_location('Leaving : ' || l_proc, 99);
484 --
485 end;
486 --
487 /*procedure get_rt_prfl_cache
488 (p_vrbl_rt_prfl_id in number
489 ,p_effective_date in date
490 ,p_lf_evt_ocrd_dt in date
491 ,p_business_group_id in number
492 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pg_inst_tbl
493 ,p_inst_count out nocopy number)
494 is
495 --
496 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache people group';
497 l_lookup_query long;
498 l_inst_query long;
499 l_instcolnm_set ben_cache.instcolnmtype;
500 --
501 begin
502 --
503 g_pg_out.delete;
504 --
505 if g_pg_lookup.count = 0 then
506 --
507 -- Cache not populated yet. So populate it now.
508 --
509 l_lookup_query :=
510 'select vrbl_rt_prfl_id, business_group_id' ||
511 ' from ben_vrbl_rt_prfl_f vpf' ||
512 ' where business_group_id = ' || to_char(p_business_group_id) ||
513 ' and exists (select null' ||
514 ' from ben_ppl_grp_rt_f pgr' ||
515 ' where pgr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
516 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
517 ' between pgr.effective_start_date' ||
518 ' and pgr.effective_end_date) ' ||
519 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
520 ' between vpf.effective_start_date and vpf.effective_end_date;';
521 --
522 l_inst_query :=
523 'select pgr.vrbl_rt_prfl_id, pgr.people_group_id, pgr.excld_flag' ||
524 ' from ben_ppl_grp_rt_f pgr ' ||
525 ' where pgr.business_group_id = ' || to_char(p_business_group_id) ||
526 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
527 ' between pgr.effective_start_date' ||
528 ' and pgr.effective_end_date' ||
529 ' order by pgr.vrbl_rt_prfl_id;';
530 --
531 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
532 l_instcolnm_set(0).caccol_name := 'id';
533 l_instcolnm_set(1).col_name := 'people_group_id';
534 l_instcolnm_set(1).caccol_name := 'people_group_id';
535 l_instcolnm_set(2).col_name := 'excld_flag';
536 l_instcolnm_set(2).caccol_name := 'excld_flag';
537 --
538 ben_cache.write_mastDet_Cache
539 (p_mastercol_name => 'vrbl_rt_prfl_id'
540 ,p_detailcol_name => 'vrbl_rt_prfl_id'
541 ,p_lkup_name => 'ben_rt_prfl_cache.g_pg_lookup'
542 ,p_inst_name => 'ben_rt_prfl_cache.g_pg_instance'
543 ,p_lkup_query => l_lookup_query
544 ,p_inst_query => l_inst_query
545 ,p_instcolnm_set => l_instcolnm_set
546 );
547 --
548 end if;
549 --
550 -- Cache already populated. Get record set.
551 --
552 get_cached_data
553 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
554 ,p_lookup_name => 'ben_rt_prfl_cache.g_pg_lookup'
555 ,p_inst_name => 'ben_rt_prfl_cache.g_pg_instance'
556 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pg_inst_tbl'
557 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pg_out'
558 );
559 --
560 p_inst_set := g_pg_out;
561 p_inst_count := g_inst_count;
562 --
563 exception
564 --
565 when no_data_found then
566 --
567 p_inst_count := 0;
568 hr_utility.set_location('No Person Groups found', 90);
569 hr_utility.set_location('Leaving : ' || l_proc, 99);
570 --
571 end;
572 */
573 --
574 -- RULES
575 --
576 procedure get_rt_prfl_cache
577 (p_vrbl_rt_prfl_id in number
578 ,p_effective_date in date
579 ,p_lf_evt_ocrd_dt in date
580 ,p_business_group_id in number
581 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_rl_inst_tbl
582 ,p_inst_count out nocopy number)
583 is
584 --
585 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache RULES';
586 l_lookup_query long;
587 l_inst_query long;
588 l_instcolnm_set ben_cache.instcolnmtype;
589 --
590 begin
591 g_rl_out.delete;
592 --
593 if g_rl_lookup.count = 0 then
594 --
595 -- Cache not populated yet. So populate it now.
596 --
597 l_lookup_query :=
598 'select vrbl_rt_prfl_id, business_group_id' ||
599 ' from ben_vrbl_rt_prfl_f vpf' ||
600 ' where business_group_id = ' || to_char(p_business_group_id) ||
601 ' and exists (select null' ||
602 ' from ben_vrbl_rt_prfl_rl_f rpr' ||
603 ' where rpr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
604 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
605 ' between rpr.effective_start_date' ||
606 ' and rpr.effective_end_date) ' ||
607 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
608 ' between vpf.effective_start_date and vpf.effective_end_date;';
609 --
610 l_inst_query :=
611 'select rpr.vrbl_rt_prfl_id, rpr.formula_id' ||
612 ' from ben_vrbl_rt_prfl_rl_f rpr' ||
613 ' where rpr.business_group_id = ' || to_char(p_business_group_id) ||
614 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
615 ' between rpr.effective_start_date' ||
616 ' and rpr.effective_end_date' ||
617 ' order by rpr.vrbl_rt_prfl_id;';
618 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
619 l_instcolnm_set(0).caccol_name := 'id';
620 l_instcolnm_set(1).col_name := 'formula_id';
621 l_instcolnm_set(1).caccol_name := 'formula_id';
622 ben_cache.write_mastDet_Cache
623 (p_mastercol_name => 'vrbl_rt_prfl_id'
624 ,p_detailcol_name => 'vrbl_rt_prfl_id'
625 ,p_lkup_name => 'ben_rt_prfl_cache.g_rl_lookup'
626 ,p_inst_name => 'ben_rt_prfl_cache.g_rl_instance'
627 ,p_lkup_query => l_lookup_query
628 ,p_inst_query => l_inst_query
629 ,p_instcolnm_set => l_instcolnm_set
630 );
631 end if;
632 --
633 -- Cache already populated. Get record set.
634 --
635 get_cached_data
636 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
637 ,p_lookup_name => 'ben_rt_prfl_cache.g_rl_lookup'
638 ,p_inst_name => 'ben_rt_prfl_cache.g_rl_instance'
639 ,p_inst_set_type => 'ben_rt_prfl_cache.g_rl_inst_tbl'
640 ,p_out_inst_name => 'ben_rt_prfl_cache.g_rl_out'
641 );
642 --
643 p_inst_set := g_rl_out;
644 p_inst_count := g_inst_count;
645 exception
646 --
647 when no_data_found then
648 --
649 p_inst_count := 0;
650 hr_utility.set_location('No Rules found', 90);
651 hr_utility.set_location('Leaving : ' || l_proc, 99);
652 --
653 end;
654 --
655 -- TOBACCO
656 --
657 procedure get_rt_prfl_cache
658 (p_vrbl_rt_prfl_id in number
659 ,p_effective_date in date
660 ,p_lf_evt_ocrd_dt in date
661 ,p_business_group_id in number
662 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_tbco_inst_tbl
663 ,p_inst_count out nocopy number)
664 is
665 --
666 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
667 l_lookup_query long;
668 l_inst_query long;
669 l_instcolnm_set ben_cache.instcolnmtype;
670 --
671 begin
672 --
673 g_tbco_out.delete;
674 --
675 if g_tbco_lookup.count = 0 then
676 --
677 -- Cache not populated yet. So populate it now.
678 --
679 l_lookup_query :=
680 'select vrbl_rt_prfl_id, business_group_id' ||
681 ' from ben_vrbl_rt_prfl_f vpf' ||
682 ' where business_group_id = ' || to_char(p_business_group_id) ||
683 ' and exists (select null' ||
684 ' from ben_tbco_use_rt_f btu' ||
685 ' where btu.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
686 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
687 ' between btu.effective_start_date' ||
688 ' and btu.effective_end_date) ' ||
689 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
690 ' between vpf.effective_start_date and vpf.effective_end_date;';
691 --
692 l_inst_query :=
693 'select btu.vrbl_rt_prfl_id, btu.uses_tbco_flag, btu.excld_flag' ||
694 ' from ben_tbco_use_rt_f btu' ||
695 ' where btu.business_group_id = ' || to_char(p_business_group_id) ||
696 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
697 ' between btu.effective_start_date' ||
698 ' and btu.effective_end_date' ||
699 ' order by btu.vrbl_rt_prfl_id;';
700 --
701 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
702 l_instcolnm_set(0).caccol_name := 'id';
703 l_instcolnm_set(1).col_name := 'uses_tbco_flag';
704 l_instcolnm_set(1).caccol_name := 'uses_tbco_flag';
705 l_instcolnm_set(2).col_name := 'excld_flag';
706 l_instcolnm_set(2).caccol_name := 'excld_flag';
707 --
708 ben_cache.write_mastDet_Cache
709 (p_mastercol_name => 'vrbl_rt_prfl_id'
710 ,p_detailcol_name => 'vrbl_rt_prfl_id'
711 ,p_lkup_name => 'ben_rt_prfl_cache.g_tbco_lookup'
712 ,p_inst_name => 'ben_rt_prfl_cache.g_tbco_instance'
713 ,p_lkup_query => l_lookup_query
714 ,p_inst_query => l_inst_query
715 ,p_instcolnm_set => l_instcolnm_set
716 );
717 --
718 end if;
719 --
720 -- Cache already populated. Get record set.
721 --
722 get_cached_data
723 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
724 ,p_lookup_name => 'ben_rt_prfl_cache.g_tbco_lookup'
725 ,p_inst_name => 'ben_rt_prfl_cache.g_tbco_instance'
726 ,p_inst_set_type => 'ben_rt_prfl_cache.g_tbco_inst_tbl'
727 ,p_out_inst_name => 'ben_rt_prfl_cache.g_tbco_out'
728 );
729 --
730 p_inst_set := g_tbco_out;
731 p_inst_count := g_inst_count;
732 --
733 exception
734 --
735 when no_data_found then
736 --
737 p_inst_count := 0;
738 hr_utility.set_location('No Tobacco Data found', 90);
739 hr_utility.set_location('Leaving : ' || l_proc, 99);
740 --
741 end;
742 --
743 -- GENDER
744 --
745 procedure get_rt_prfl_cache
746 (p_vrbl_rt_prfl_id in number
747 ,p_effective_date in date
748 ,p_lf_evt_ocrd_dt in date
749 ,p_business_group_id in number
750 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_gndr_inst_tbl
751 ,p_inst_count out nocopy number)
752 is
753 --
754 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
755 l_lookup_query long;
756 l_inst_query long;
757 l_instcolnm_set ben_cache.instcolnmtype;
758 --
759 begin
760 --
761 g_gndr_out.delete;
762 --
763 if g_gndr_lookup.count = 0 then
764 --
765 -- Cache not populated yet. So populate it now.
766 --
767 l_lookup_query :=
768 'select vrbl_rt_prfl_id, business_group_id' ||
769 ' from ben_vrbl_rt_prfl_f vpf' ||
770 ' where business_group_id = ' || to_char(p_business_group_id) ||
771 ' and exists (select null' ||
772 ' from ben_gndr_rt_f bgr' ||
773 ' where bgr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
774 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
775 ' between bgr.effective_start_date' ||
776 ' and bgr.effective_end_date) ' ||
777 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
778 ' between vpf.effective_start_date and vpf.effective_end_date;';
779 --
780 l_inst_query :=
781 'select bgr.vrbl_rt_prfl_id, bgr.gndr_cd, bgr.excld_flag' ||
782 ' from ben_gndr_rt_f bgr' ||
783 ' where bgr.business_group_id = ' || to_char(p_business_group_id) ||
784 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
785 ' between bgr.effective_start_date' ||
786 ' and bgr.effective_end_date' ||
787 ' order by bgr.vrbl_rt_prfl_id;';
788 --
789 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
790 l_instcolnm_set(0).caccol_name := 'id';
791 l_instcolnm_set(1).col_name := 'gndr_cd';
792 l_instcolnm_set(1).caccol_name := 'gndr_cd';
793 l_instcolnm_set(2).col_name := 'excld_flag';
794 l_instcolnm_set(2).caccol_name := 'excld_flag';
795 --
796 ben_cache.write_mastDet_Cache
797 (p_mastercol_name => 'vrbl_rt_prfl_id'
798 ,p_detailcol_name => 'vrbl_rt_prfl_id'
799 ,p_lkup_name => 'ben_rt_prfl_cache.g_gndr_lookup'
800 ,p_inst_name => 'ben_rt_prfl_cache.g_gndr_instance'
801 ,p_lkup_query => l_lookup_query
802 ,p_inst_query => l_inst_query
803 ,p_instcolnm_set => l_instcolnm_set
804 );
805 --
806 end if;
807 --
808 -- Cache already populated. Get record set.
809 --
810 get_cached_data
811 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
812 ,p_lookup_name => 'ben_rt_prfl_cache.g_gndr_lookup'
813 ,p_inst_name => 'ben_rt_prfl_cache.g_gndr_instance'
814 ,p_inst_set_type => 'ben_rt_prfl_cache.g_gndr_inst_tbl'
815 ,p_out_inst_name => 'ben_rt_prfl_cache.g_gndr_out'
816 );
817 --
818 p_inst_set := g_gndr_out;
819 p_inst_count := g_inst_count;
820 --
821 exception
822 --
823 when no_data_found then
824 --
825 p_inst_count := 0;
826 hr_utility.set_location('No Genders found', 90);
827 hr_utility.set_location('Leaving : ' || l_proc, 99);
828 --
829 end;
830 --
831 -- Disabled
832 --
833 procedure get_rt_prfl_cache
834 (p_vrbl_rt_prfl_id in number
835 ,p_effective_date in date
836 ,p_lf_evt_ocrd_dt in date
837 ,p_business_group_id in number
838 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dsbld_inst_tbl
839 ,p_inst_count out nocopy number)
840 is
841 --
842 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
843 l_lookup_query long;
844 l_inst_query long;
845 l_instcolnm_set ben_cache.instcolnmtype;
846 --
847 begin
848 --
849 g_dsbld_out.delete;
850 --
851 if g_dsbld_lookup.count = 0 then
852 --
853 -- Cache not populated yet. So populate it now.
854 --
855 l_lookup_query :=
856 'select vrbl_rt_prfl_id, business_group_id' ||
857 ' from ben_vrbl_rt_prfl_f vpf' ||
858 ' where business_group_id = ' || to_char(p_business_group_id) ||
859 ' and exists (select null' ||
860 ' from ben_dsbld_rt_f dbr' ||
861 ' where dbr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
862 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
863 ' between dbr.effective_start_date' ||
864 ' and dbr.effective_end_date) ' ||
865 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
866 ' between vpf.effective_start_date and vpf.effective_end_date;';
867 --
868 l_inst_query :=
869 'select dbr.vrbl_rt_prfl_id, dbr.dsbld_cd, dbr.excld_flag' ||
870 ' from ben_dsbld_rt_f dbr' ||
871 ' where dbr.business_group_id = ' || to_char(p_business_group_id) ||
872 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
873 ' between dbr.effective_start_date' ||
874 ' and dbr.effective_end_date' ||
875 ' order by dbr.vrbl_rt_prfl_id;';
876 --
877 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
878 l_instcolnm_set(0).caccol_name := 'id';
879 l_instcolnm_set(1).col_name := 'dsbld_cd';
880 l_instcolnm_set(1).caccol_name := 'dsbld_cd';
881 l_instcolnm_set(2).col_name := 'excld_flag';
882 l_instcolnm_set(2).caccol_name := 'excld_flag';
883 --
884 ben_cache.write_mastDet_Cache
885 (p_mastercol_name => 'vrbl_rt_prfl_id'
886 ,p_detailcol_name => 'vrbl_rt_prfl_id'
887 ,p_lkup_name => 'ben_rt_prfl_cache.g_dsbld_lookup'
888 ,p_inst_name => 'ben_rt_prfl_cache.g_dsbld_instance'
889 ,p_lkup_query => l_lookup_query
890 ,p_inst_query => l_inst_query
891 ,p_instcolnm_set => l_instcolnm_set
892 );
893 --
894 end if;
895 --
896 -- Cache already populated. Get record set.
897 --
898 get_cached_data
899 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
900 ,p_lookup_name => 'ben_rt_prfl_cache.g_dsbld_lookup'
901 ,p_inst_name => 'ben_rt_prfl_cache.g_dsbld_instance'
902 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dsbld_inst_tbl'
903 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dsbld_out'
904 );
905 --
906 p_inst_set := g_dsbld_out;
907 p_inst_count := g_inst_count;
908 --
909 exception
910 --
911 when no_data_found then
912 --
913 p_inst_count := 0;
914 hr_utility.set_location('No Disabled Code found', 90);
915 hr_utility.set_location('Leaving : ' || l_proc, 99);
916 --
917 end;
918 --
919 -- BARGAINING UNIT
920 --
921 procedure get_rt_prfl_cache
922 (p_vrbl_rt_prfl_id in number
923 ,p_effective_date in date
924 ,p_lf_evt_ocrd_dt in date
925 ,p_business_group_id in number
926 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_brgng_inst_tbl
927 ,p_inst_count out nocopy number)
928 is
929 --
930 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
931 l_lookup_query long;
932 l_inst_query long;
933 l_instcolnm_set ben_cache.instcolnmtype;
934 --
935 begin
936 --
937 g_brgng_out.delete;
938 --
939 if g_brgng_lookup.count = 0 then
940 --
941 -- Cache not populated yet. So populate it now.
942 --
943 l_lookup_query :=
944 'select vrbl_rt_prfl_id, business_group_id' ||
945 ' from ben_vrbl_rt_prfl_f vpf' ||
946 ' where business_group_id = ' || to_char(p_business_group_id) ||
947 ' and exists (select null' ||
948 ' from ben_brgng_unit_rt_f ebu' ||
949 ' where ebu.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
950 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
951 ' between ebu.effective_start_date' ||
952 ' and ebu.effective_end_date) ' ||
953 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
954 ' between vpf.effective_start_date and vpf.effective_end_date;';
955 --
956 l_inst_query :=
957 'select ebu.vrbl_rt_prfl_id, ebu.brgng_unit_cd, ebu.excld_flag' ||
958 ' from ben_brgng_unit_rt_f ebu' ||
959 ' where ebu.business_group_id = ' || to_char(p_business_group_id) ||
960 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
961 ' between ebu.effective_start_date' ||
962 ' and ebu.effective_end_date' ||
963 ' order by ebu.vrbl_rt_prfl_id;';
964 --
965 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
966 l_instcolnm_set(0).caccol_name := 'id';
967 l_instcolnm_set(1).col_name := 'brgng_unit_cd';
968 l_instcolnm_set(1).caccol_name := 'brgng_unit_cd';
969 l_instcolnm_set(2).col_name := 'excld_flag';
970 l_instcolnm_set(2).caccol_name := 'excld_flag';
971 --
972 ben_cache.write_mastDet_Cache
973 (p_mastercol_name => 'vrbl_rt_prfl_id'
974 ,p_detailcol_name => 'vrbl_rt_prfl_id'
975 ,p_lkup_name => 'ben_rt_prfl_cache.g_brgng_lookup'
976 ,p_inst_name => 'ben_rt_prfl_cache.g_brgng_instance'
977 ,p_lkup_query => l_lookup_query
978 ,p_inst_query => l_inst_query
979 ,p_instcolnm_set => l_instcolnm_set
980 );
981 --
982 end if;
983 --
984 -- Cache already populated. Get record set.
985 --
986 get_cached_data
987 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
988 ,p_lookup_name => 'ben_rt_prfl_cache.g_brgng_lookup'
989 ,p_inst_name => 'ben_rt_prfl_cache.g_brgng_instance'
990 ,p_inst_set_type => 'ben_rt_prfl_cache.g_brgng_inst_tbl'
991 ,p_out_inst_name => 'ben_rt_prfl_cache.g_brgng_out'
992 );
993 --
994 p_inst_set := g_brgng_out;
995 p_inst_count := g_inst_count;
996 --
997 exception
998 --
999 when no_data_found then
1000 --
1001 p_inst_count := 0;
1002 hr_utility.set_location('No Bargining found', 90);
1003 hr_utility.set_location('Leaving : ' || l_proc, 99);
1004 --
1005 end;
1006 --
1007 -- BENEFITS GROUP
1008 --
1009 procedure get_rt_prfl_cache
1010 (p_vrbl_rt_prfl_id in number
1011 ,p_effective_date in date
1012 ,p_lf_evt_ocrd_dt in date
1013 ,p_business_group_id in number
1014 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_bnfgrp_inst_tbl
1015 ,p_inst_count out nocopy number)
1016 is
1017 --
1018 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1019 l_lookup_query long;
1020 l_inst_query long;
1021 l_instcolnm_set ben_cache.instcolnmtype;
1022 --
1023 begin
1024 --
1025 g_bnfgrp_out.delete;
1026 --
1027 if g_bnfgrp_lookup.count = 0 then
1028 --
1029 -- Cache not populated yet. So populate it now.
1030 --
1031 l_lookup_query :=
1032 'select vrbl_rt_prfl_id, business_group_id' ||
1033 ' from ben_vrbl_rt_prfl_f vpf' ||
1034 ' where business_group_id = ' || to_char(p_business_group_id) ||
1035 ' and exists (select null' ||
1036 ' from ben_benfts_grp_rt_f bgr' ||
1037 ' where bgr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1038 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1039 ' between bgr.effective_start_date' ||
1040 ' and bgr.effective_end_date) ' ||
1041 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1042 ' between vpf.effective_start_date and vpf.effective_end_date;';
1043 --
1044 l_inst_query :=
1045 'select bgr.vrbl_rt_prfl_id, bgr.benfts_grp_id, bgr.excld_flag' ||
1046 ' from ben_benfts_grp_rt_f bgr' ||
1047 ' where bgr.business_group_id = ' || to_char(p_business_group_id) ||
1048 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1049 ' between bgr.effective_start_date' ||
1050 ' and bgr.effective_end_date' ||
1051 ' order by bgr.vrbl_rt_prfl_id;';
1052 --
1053 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1054 l_instcolnm_set(0).caccol_name := 'id';
1055 l_instcolnm_set(1).col_name := 'benfts_grp_id';
1056 l_instcolnm_set(1).caccol_name := 'benfts_grp_id';
1057 l_instcolnm_set(2).col_name := 'excld_flag';
1058 l_instcolnm_set(2).caccol_name := 'excld_flag';
1059 --
1060 ben_cache.write_mastDet_Cache
1061 (p_mastercol_name => 'vrbl_rt_prfl_id'
1062 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1063 ,p_lkup_name => 'ben_rt_prfl_cache.g_bnfgrp_lookup'
1064 ,p_inst_name => 'ben_rt_prfl_cache.g_bnfgrp_instance'
1065 ,p_lkup_query => l_lookup_query
1066 ,p_inst_query => l_inst_query
1067 ,p_instcolnm_set => l_instcolnm_set
1068 );
1069 --
1070 end if;
1071 --
1072 -- Cache already populated. Get record set.
1073 --
1074 get_cached_data
1075 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1076 ,p_lookup_name => 'ben_rt_prfl_cache.g_bnfgrp_lookup'
1077 ,p_inst_name => 'ben_rt_prfl_cache.g_bnfgrp_instance'
1078 ,p_inst_set_type => 'ben_rt_prfl_cache.g_bnfgrp_inst_tbl'
1079 ,p_out_inst_name => 'ben_rt_prfl_cache.g_bnfgrp_out'
1080 );
1081 --
1082 p_inst_set := g_bnfgrp_out;
1083 p_inst_count := g_inst_count;
1084 --
1085 exception
1086 --
1087 when no_data_found then
1088 --
1089 p_inst_count := 0;
1090 hr_utility.set_location('No data found', 90);
1091 hr_utility.set_location('Leaving : ' || l_proc, 99);
1092 --
1093 end;
1094 --
1095 -- EMPLOYEE STATUS
1096 --
1097 procedure get_rt_prfl_cache
1098 (p_vrbl_rt_prfl_id in number
1099 ,p_effective_date in date
1100 ,p_lf_evt_ocrd_dt in date
1101 ,p_business_group_id in number
1102 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_eestat_inst_tbl
1103 ,p_inst_count out nocopy number)
1104 is
1105 --
1106 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1107 l_lookup_query long;
1108 l_inst_query long;
1109 l_instcolnm_set ben_cache.instcolnmtype;
1110 --
1111 begin
1112 --
1113 g_eestat_out.delete;
1114 --
1115 if g_eestat_lookup.count = 0 then
1116 --
1117 -- Cache not populated yet. So populate it now.
1118 --
1119 l_lookup_query :=
1120 'select vrbl_rt_prfl_id, business_group_id' ||
1121 ' from ben_vrbl_rt_prfl_f vpf' ||
1122 ' where business_group_id = ' || to_char(p_business_group_id) ||
1123 ' and exists (select null' ||
1124 ' from ben_ee_stat_rt_f ees' ||
1125 ' where ees.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1126 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1127 ' between ees.effective_start_date' ||
1128 ' and ees.effective_end_date) ' ||
1129 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1130 ' between vpf.effective_start_date and vpf.effective_end_date;';
1131 --
1132 l_inst_query :=
1133 'select ees.vrbl_rt_prfl_id, ees.assignment_status_type_id,' ||
1134 ' ees.excld_flag' ||
1135 ' from ben_ee_stat_rt_f ees' ||
1136 ' where ees.business_group_id = ' || to_char(p_business_group_id) ||
1137 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1138 ' between ees.effective_start_date' ||
1139 ' and ees.effective_end_date' ||
1140 ' order by ees.vrbl_rt_prfl_id;';
1141 --
1142 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1143 l_instcolnm_set(0).caccol_name := 'id';
1144 l_instcolnm_set(1).col_name := 'assignment_status_type_id';
1145 l_instcolnm_set(1).caccol_name := 'assignment_status_type_id';
1146 l_instcolnm_set(2).col_name := 'excld_flag';
1147 l_instcolnm_set(2).caccol_name := 'excld_flag';
1148 --
1149 ben_cache.write_mastDet_Cache
1150 (p_mastercol_name => 'vrbl_rt_prfl_id'
1151 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1152 ,p_lkup_name => 'ben_rt_prfl_cache.g_eestat_lookup'
1153 ,p_inst_name => 'ben_rt_prfl_cache.g_eestat_instance'
1154 ,p_lkup_query => l_lookup_query
1155 ,p_inst_query => l_inst_query
1156 ,p_instcolnm_set => l_instcolnm_set
1157 );
1158 --
1159 end if;
1160 --
1161 -- Cache already populated. Get record set.
1162 --
1163 get_cached_data
1164 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1165 ,p_lookup_name => 'ben_rt_prfl_cache.g_eestat_lookup'
1166 ,p_inst_name => 'ben_rt_prfl_cache.g_eestat_instance'
1167 ,p_inst_set_type => 'ben_rt_prfl_cache.g_eestat_inst_tbl'
1168 ,p_out_inst_name => 'ben_rt_prfl_cache.g_eestat_out'
1169 );
1170 --
1171 p_inst_set := g_eestat_out;
1172 p_inst_count := g_inst_count;
1173 --
1174 exception
1175 --
1176 when no_data_found then
1177 --
1178 p_inst_count := 0;
1179 hr_utility.set_location('No Employee Status found', 90);
1180 hr_utility.set_location('Leaving : ' || l_proc, 99);
1181 --
1182 end;
1183 --
1184 -- FULL TIME PART TIME
1185 --
1186 procedure get_rt_prfl_cache
1187 (p_vrbl_rt_prfl_id in number
1188 ,p_effective_date in date
1189 ,p_lf_evt_ocrd_dt in date
1190 ,p_business_group_id in number
1191 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ftpt_inst_tbl
1192 ,p_inst_count out nocopy number) is
1193 --
1194 l_proc varchar2(72) := 'get_rt_prfl_cache';
1195 --
1196 l_instcolnm_set ben_cache.InstColNmType;
1197 --
1198 l_torrwnum pls_integer;
1199 l_insttorrw_num pls_integer;
1200 l_index pls_integer;
1201 l_instcolnm_num pls_integer;
1202 l_mastertab_name varchar2(100);
1203 l_masterpkcol_name varchar2(100);
1204 l_lkup_name varchar2(100);
1205 l_inst_name varchar2(100);
1206 --
1207 l_not_hash_found boolean;
1208 --
1209 begin
1210 --
1211 -- Populate the global cache
1212 --
1213 if g_ftpt_lookup.count = 0 then
1214 --
1215 -- Build the cache
1216 --
1217 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
1218 l_masterpkcol_name := 'vrbl_rt_prfl_id';
1219 l_lkup_name := 'ben_rt_prfl_cache.g_ftpt_lookup';
1220 l_inst_name := 'ben_rt_prfl_cache.g_ftpt_instance';
1221 --
1222 l_instcolnm_num := 0;
1223 --
1224 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
1225 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
1226 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1227 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
1228 l_instcolnm_num := l_instcolnm_num+1;
1229 --
1230 l_instcolnm_set(l_instcolnm_num).col_name := 'fl_tm_pt_tm_cd';
1231 l_instcolnm_set(l_instcolnm_num).caccol_name := 'fl_tm_pt_tm_cd';
1232 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1233 l_instcolnm_num := l_instcolnm_num+1;
1234 --
1235 l_instcolnm_set(l_instcolnm_num).col_name := 'excld_flag';
1236 l_instcolnm_set(l_instcolnm_num).caccol_name := 'excld_flag';
1237 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1238 l_instcolnm_num := l_instcolnm_num+1;
1239 --
1240 ben_cache.Write_BGP_Cache
1241 (p_mastertab_name => l_mastertab_name
1242 ,p_masterpkcol_name => l_masterpkcol_name
1243 ,p_table1_name => 'ben_fl_tm_pt_tm_rt_f'
1244 ,p_business_group_id => p_business_group_id
1245 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
1246 ,p_lkup_name => l_lkup_name
1247 ,p_inst_name => l_inst_name
1248 ,p_instcolnm_set => l_instcolnm_set
1249 );
1250 --
1251 end if;
1252 --
1253 -- Get the instance details
1254 --
1255 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
1256 --
1257 -- Check if hashed value is already allocated
1258 --
1259 if g_ftpt_lookup.exists(l_index) then
1260 --
1261 -- If it does exist make sure its the right one
1262 --
1263 if g_ftpt_lookup(l_index).id <> p_vrbl_rt_prfl_id then
1264 --
1265 l_not_hash_found := false;
1266 --
1267 -- Loop until un-allocated has value is derived
1268 --
1269 while not l_not_hash_found loop
1270 --
1271 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
1272 --
1273 -- Check if the hash index exists, if not we can use it
1274 --
1275 if not g_ftpt_lookup.exists(l_index) then
1276 --
1277 -- Lets store the hash value in the index
1278 --
1279 l_not_hash_found := true;
1280 exit;
1281 --
1282 else
1283 --
1284 l_not_hash_found := false;
1285 --
1286 end if;
1287 --
1288 end loop;
1289 --
1290 end if;
1291 --
1292 end if;
1293 --
1294 l_torrwnum := 0;
1295 for l_insttorrw_num in g_ftpt_lookup(l_index).starttorele_num ..
1296 g_ftpt_lookup(l_index).endtorele_num loop
1297 --
1298 p_inst_set(l_torrwnum) := g_ftpt_instance(l_insttorrw_num);
1299 l_torrwnum := l_torrwnum+1;
1300 --
1301 end loop;
1302 --
1303 p_inst_count := l_torrwnum;
1304 --
1305 exception
1306 --
1307 when no_data_found then
1308 --
1309 p_inst_count := 0;
1310 --
1311 end get_rt_prfl_cache;
1312 --
1313 -- GRADE
1314 --
1315 procedure get_rt_prfl_cache
1316 (p_vrbl_rt_prfl_id in number
1317 ,p_effective_date in date
1318 ,p_lf_evt_ocrd_dt in date
1319 ,p_business_group_id in number
1320 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_grd_inst_tbl
1321 ,p_inst_count out nocopy number)
1322 is
1323 --
1324 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1325 l_lookup_query long;
1326 l_inst_query long;
1327 l_instcolnm_set ben_cache.instcolnmtype;
1328 --
1329 begin
1330 --
1331 g_grd_out.delete;
1332 --
1333 if g_grd_lookup.count = 0 then
1334 --
1335 -- Cache not populated yet. So populate it now.
1336 --
1337 l_lookup_query :=
1338 'select vrbl_rt_prfl_id, business_group_id' ||
1339 ' from ben_vrbl_rt_prfl_f vpf' ||
1340 ' where business_group_id = ' || to_char(p_business_group_id) ||
1341 ' and exists (select null' ||
1342 ' from ben_grade_rt_f egr' ||
1343 ' where egr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1344 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1345 ' between egr.effective_start_date' ||
1346 ' and egr.effective_end_date) ' ||
1347 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1348 ' between vpf.effective_start_date and vpf.effective_end_date;';
1349 --
1350 l_inst_query :=
1351 'select egr.vrbl_rt_prfl_id, egr.grade_id, egr.excld_flag' ||
1352 ' from ben_grade_rt_f egr' ||
1353 ' where egr.business_group_id = ' || to_char(p_business_group_id) ||
1354 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1355 ' between egr.effective_start_date' ||
1356 ' and egr.effective_end_date' ||
1357 ' order by egr.vrbl_rt_prfl_id;';
1358 --
1359 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1360 l_instcolnm_set(0).caccol_name := 'id';
1361 l_instcolnm_set(1).col_name := 'grade_id';
1362 l_instcolnm_set(1).caccol_name := 'grade_id';
1363 l_instcolnm_set(2).col_name := 'excld_flag';
1364 l_instcolnm_set(2).caccol_name := 'excld_flag';
1365 --
1366 ben_cache.write_mastDet_Cache
1367 (p_mastercol_name => 'vrbl_rt_prfl_id'
1368 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1369 ,p_lkup_name => 'ben_rt_prfl_cache.g_grd_lookup'
1370 ,p_inst_name => 'ben_rt_prfl_cache.g_grd_instance'
1371 ,p_lkup_query => l_lookup_query
1372 ,p_inst_query => l_inst_query
1373 ,p_instcolnm_set => l_instcolnm_set
1374 );
1375 --
1376 end if;
1377 --
1378 -- Cache already populated. Get record set.
1379 --
1380 get_cached_data
1381 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1382 ,p_lookup_name => 'ben_rt_prfl_cache.g_grd_lookup'
1383 ,p_inst_name => 'ben_rt_prfl_cache.g_grd_instance'
1384 ,p_inst_set_type => 'ben_rt_prfl_cache.g_grd_inst_tbl'
1385 ,p_out_inst_name => 'ben_rt_prfl_cache.g_grd_out'
1386 );
1387 --
1388 p_inst_set := g_grd_out;
1389 p_inst_count := g_inst_count;
1390 --
1391 exception
1392 --
1393 when no_data_found then
1394 --
1395 p_inst_count := 0;
1396 hr_utility.set_location('No Grade found', 90);
1397 hr_utility.set_location('Leaving : ' || l_proc, 99);
1398 --
1399 end;
1400 --
1401 -- PERCENT FULL TIME
1402 --
1403 procedure get_rt_prfl_cache
1404 (p_vrbl_rt_prfl_id in number
1405 ,p_effective_date in date
1406 ,p_lf_evt_ocrd_dt in date
1407 ,p_business_group_id in number
1408 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pctft_inst_tbl
1409 ,p_inst_count out nocopy number)
1410 is
1411 --
1412 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1413 l_lookup_query long;
1414 l_inst_query long;
1415 l_instcolnm_set ben_cache.instcolnmtype;
1416 --
1417 begin
1418 --
1419 g_pctft_out.delete;
1420 --
1421 if g_pctft_lookup.count = 0 then
1422 --
1423 -- Cache not populated yet. So populate it now.
1424 --
1425 l_lookup_query :=
1426 'select vrbl_rt_prfl_id, business_group_id' ||
1427 ' from ben_vrbl_rt_prfl_f vpf' ||
1428 ' where business_group_id = ' || to_char(p_business_group_id) ||
1429 ' and exists (select null' ||
1430 ' from ben_pct_fl_tm_rt_f epf' ||
1431 ' where epf.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1432 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1433 ' between epf.effective_start_date' ||
1434 ' and epf.effective_end_date) ' ||
1435 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1436 ' between vpf.effective_start_date and vpf.effective_end_date;';
1437 --
1438 l_inst_query :=
1439 'select epf.vrbl_rt_prfl_id, pff.mn_pct_val, pff.mx_pct_val,' ||
1440 ' pff.no_mn_pct_val_flag, pff.no_mx_pct_val_flag, epf.excld_flag' ||
1441 ' from ben_pct_fl_tm_fctr pff, ben_pct_fl_tm_rt_f epf' ||
1442 ' where pff.pct_fl_tm_fctr_id = epf.pct_fl_tm_fctr_id' ||
1443 ' and epf.business_group_id = ' || to_char(p_business_group_id) ||
1444 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1445 ' between epf.effective_start_date' ||
1446 ' and epf.effective_end_date' ||
1447 ' order by epf.vrbl_rt_prfl_id;';
1448 --
1449 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1450 l_instcolnm_set(0).caccol_name := 'id';
1451 l_instcolnm_set(1).col_name := 'mn_pct_val';
1452 l_instcolnm_set(1).caccol_name := 'mn_pct_val';
1453 l_instcolnm_set(2).col_name := 'mx_pct_val';
1454 l_instcolnm_set(2).caccol_name := 'mx_pct_val';
1455 l_instcolnm_set(3).col_name := 'no_mn_pct_val_flag';
1456 l_instcolnm_set(3).caccol_name := 'no_mn_pct_val_flag';
1457 l_instcolnm_set(4).col_name := 'no_mx_pct_val_flag';
1458 l_instcolnm_set(4).caccol_name := 'no_mx_pct_val_flag';
1459 l_instcolnm_set(5).col_name := 'excld_flag';
1460 l_instcolnm_set(5).caccol_name := 'excld_flag';
1461 --
1462 ben_cache.write_mastDet_Cache
1463 (p_mastercol_name => 'vrbl_rt_prfl_id'
1464 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1465 ,p_lkup_name => 'ben_rt_prfl_cache.g_pctft_lookup'
1466 ,p_inst_name => 'ben_rt_prfl_cache.g_pctft_instance'
1467 ,p_lkup_query => l_lookup_query
1468 ,p_inst_query => l_inst_query
1469 ,p_instcolnm_set => l_instcolnm_set
1470 );
1471 --
1472 end if;
1473 --
1474 -- Cache already populated. Get record set.
1475 --
1476 get_cached_data
1477 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1478 ,p_lookup_name => 'ben_rt_prfl_cache.g_pctft_lookup'
1479 ,p_inst_name => 'ben_rt_prfl_cache.g_pctft_instance'
1480 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pctft_inst_tbl'
1481 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pctft_out'
1482 );
1483 --
1484 p_inst_set := g_pctft_out;
1485 p_inst_count := g_inst_count;
1486 --
1487 exception
1488 --
1489 when no_data_found then
1490 --
1491 p_inst_count := 0;
1492 hr_utility.set_location('No Percent Full Time found', 90);
1493 hr_utility.set_location('Leaving : ' || l_proc, 99);
1494 --
1495 end;
1496 --
1497 -- HOURS WORKED
1498 --
1499 procedure get_rt_prfl_cache
1500 (p_vrbl_rt_prfl_id in number
1501 ,p_effective_date in date
1502 ,p_lf_evt_ocrd_dt in date
1503 ,p_business_group_id in number
1504 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_hrswkd_inst_tbl
1505 ,p_inst_count out nocopy number)
1506 is
1507 --
1508 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1509 l_lookup_query long;
1510 l_inst_query long;
1511 l_instcolnm_set ben_cache.instcolnmtype;
1512 --
1513 begin
1514 --
1515 g_hrswkd_out.delete;
1516 --
1517 if g_hrswkd_lookup.count = 0 then
1518 --
1519 -- Cache not populated yet. So populate it now.
1520 --
1521 l_lookup_query :=
1522 'select vrbl_rt_prfl_id, business_group_id' ||
1523 ' from ben_vrbl_rt_prfl_f vpf' ||
1524 ' where business_group_id = ' || to_char(p_business_group_id) ||
1525 ' and exists (select null' ||
1526 ' from ben_hrs_wkd_in_perd_rt_f ehw' ||
1527 ' where ehw.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1528 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1529 ' between ehw.effective_start_date' ||
1530 ' and ehw.effective_end_date) ' ||
1531 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1532 ' between vpf.effective_start_date and vpf.effective_end_date;';
1533 --
1534 l_inst_query :=
1535 'select ehw.vrbl_rt_prfl_id, hwf.mn_hrs_num, hwf.mx_hrs_num,' ||
1536 ' hwf.no_mn_hrs_wkd_flag, hwf.no_mx_hrs_wkd_flag, ehw.excld_flag' ||
1537 ' from ben_hrs_wkd_in_perd_fctr hwf, ben_hrs_wkd_in_perd_rt_f ehw' ||
1538 ' where hwf.hrs_wkd_in_perd_fctr_id = ehw.hrs_wkd_in_perd_fctr_id' ||
1539 ' and ehw.business_group_id = ' || to_char(p_business_group_id) ||
1540 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1541 ' between ehw.effective_start_date' ||
1542 ' and ehw.effective_end_date' ||
1543 ' order by ehw.vrbl_rt_prfl_id;';
1544 --
1545 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1546 l_instcolnm_set(0).caccol_name := 'id';
1547 l_instcolnm_set(1).col_name := 'mn_hrs_num';
1548 l_instcolnm_set(1).caccol_name := 'mn_hrs_num';
1549 l_instcolnm_set(2).col_name := 'mx_hrs_num';
1550 l_instcolnm_set(2).caccol_name := 'mx_hrs_num';
1551 l_instcolnm_set(3).col_name := 'no_mn_hrs_wkd_flag';
1552 l_instcolnm_set(3).caccol_name := 'no_mn_hrs_wkd_flag';
1553 l_instcolnm_set(4).col_name := 'no_mx_hrs_wkd_flag';
1554 l_instcolnm_set(4).caccol_name := 'no_mx_hrs_wkd_flag';
1555 l_instcolnm_set(5).col_name := 'excld_flag';
1556 l_instcolnm_set(5).caccol_name := 'excld_flag';
1557 --
1558 ben_cache.write_mastDet_Cache
1559 (p_mastercol_name => 'vrbl_rt_prfl_id'
1560 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1561 ,p_lkup_name => 'ben_rt_prfl_cache.g_hrswkd_lookup'
1562 ,p_inst_name => 'ben_rt_prfl_cache.g_hrswkd_instance'
1563 ,p_lkup_query => l_lookup_query
1564 ,p_inst_query => l_inst_query
1565 ,p_instcolnm_set => l_instcolnm_set
1566 );
1567 --
1568 end if;
1569 --
1570 -- Cache already populated. Get record set.
1571 --
1572 get_cached_data
1573 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1574 ,p_lookup_name => 'ben_rt_prfl_cache.g_hrswkd_lookup'
1575 ,p_inst_name => 'ben_rt_prfl_cache.g_hrswkd_instance'
1576 ,p_inst_set_type => 'ben_rt_prfl_cache.g_hrswkd_inst_tbl'
1577 ,p_out_inst_name => 'ben_rt_prfl_cache.g_hrswkd_out'
1578 );
1579 --
1580 p_inst_set := g_hrswkd_out;
1581 p_inst_count := g_inst_count;
1582 --
1583 exception
1584 --
1585 when no_data_found then
1586 --
1587 p_inst_count := 0;
1588 hr_utility.set_location('No Hours Worked found', 90);
1589 hr_utility.set_location('Leaving : ' || l_proc, 99);
1590 --
1591 end;
1592 --
1593 -- HOURS WORKED
1594 --
1595 procedure get_rt_prfl_cache
1596 (p_vrbl_rt_prfl_id in number
1597 ,p_effective_date in date
1598 ,p_lf_evt_ocrd_dt in date
1599 ,p_business_group_id in number
1600 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_poe_inst_tbl
1601 ,p_inst_count out nocopy number)
1602 is
1603 --
1604 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1605 l_lookup_query long;
1606 l_inst_query long;
1607 l_instcolnm_set ben_cache.instcolnmtype;
1608 --
1609 begin
1610 --
1611 g_poe_out.delete;
1612 --
1613 if g_poe_lookup.count = 0 then
1614 --
1615 -- Cache not populated yet. So populate it now.
1616 --
1617 l_lookup_query :=
1618 'select vrbl_rt_prfl_id, business_group_id' ||
1619 ' from ben_vrbl_rt_prfl_f vpf' ||
1620 ' where business_group_id = ' || to_char(p_business_group_id) ||
1621 ' and exists (select null' ||
1622 ' from ben_poe_rt_f prt' ||
1623 ' where prt.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1624 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1625 ' between prt.effective_start_date' ||
1626 ' and prt.effective_end_date) ' ||
1627 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1628 ' between vpf.effective_start_date and vpf.effective_end_date;';
1629 --
1630 l_inst_query :=
1631 'select prt.vrbl_rt_prfl_id, prt.mn_poe_num, prt.mx_poe_num,' ||
1632 ' prt.no_mn_poe_flag, prt.no_mx_poe_flag, prt.rndg_cd,' ||
1633 ' prt.rndg_rl, prt.poe_nnmntry_uom, prt.cbr_dsblty_apls_flag' ||
1634 ' from ben_poe_rt_f prt' ||
1635 ' where prt.business_group_id = ' || to_char(p_business_group_id) ||
1636 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1637 ' between prt.effective_start_date' ||
1638 ' and prt.effective_end_date' ||
1639 ' order by prt.vrbl_rt_prfl_id;';
1640 --
1641 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1642 l_instcolnm_set(0).caccol_name := 'id';
1643 l_instcolnm_set(1).col_name := 'mn_poe_num';
1644 l_instcolnm_set(1).caccol_name := 'mn_poe_num';
1645 l_instcolnm_set(2).col_name := 'mx_poe_num';
1646 l_instcolnm_set(2).caccol_name := 'mx_poe_num';
1647 l_instcolnm_set(3).col_name := 'no_mn_poe_flag';
1648 l_instcolnm_set(3).caccol_name := 'no_mn_poe_flag';
1649 l_instcolnm_set(4).col_name := 'no_mx_poe_flag';
1650 l_instcolnm_set(4).caccol_name := 'no_mx_poe_flag';
1651 l_instcolnm_set(5).col_name := 'rndg_cd';
1652 l_instcolnm_set(5).caccol_name := 'rndg_cd';
1653 l_instcolnm_set(6).col_name := 'rndg_rl';
1654 l_instcolnm_set(6).caccol_name := 'rndg_rl';
1655 l_instcolnm_set(7).col_name := 'poe_nnmntry_uom';
1656 l_instcolnm_set(7).caccol_name := 'poe_nnmntry_uom';
1657 l_instcolnm_set(8).col_name := 'cbr_dsblty_apls_flag';
1658 l_instcolnm_set(8).caccol_name := 'cbr_dsblty_apls_flag';
1659 --
1660 ben_cache.write_mastDet_Cache
1661 (p_mastercol_name => 'vrbl_rt_prfl_id'
1662 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1663 ,p_lkup_name => 'ben_rt_prfl_cache.g_poe_lookup'
1664 ,p_inst_name => 'ben_rt_prfl_cache.g_poe_instance'
1665 ,p_lkup_query => l_lookup_query
1666 ,p_inst_query => l_inst_query
1667 ,p_instcolnm_set => l_instcolnm_set
1668 );
1669 --
1670 end if;
1671 --
1672 -- Cache already populated. Get record set.
1673 --
1674 get_cached_data
1675 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1676 ,p_lookup_name => 'ben_rt_prfl_cache.g_poe_lookup'
1677 ,p_inst_name => 'ben_rt_prfl_cache.g_poe_instance'
1678 ,p_inst_set_type => 'ben_rt_prfl_cache.g_poe_inst_tbl'
1679 ,p_out_inst_name => 'ben_rt_prfl_cache.g_poe_out'
1680 );
1681 --
1682 p_inst_set := g_poe_out;
1683 p_inst_count := g_inst_count;
1684 --
1685 exception
1686 --
1687 when no_data_found then
1688 --
1689 p_inst_count := 0;
1690 hr_utility.set_location('No Period of Enrollment found', 90);
1691 hr_utility.set_location('Leaving : ' || l_proc, 99);
1692 --
1693 end;
1694 --
1695 -- LABOR UNION MEMBERSHIP
1696 --
1697 procedure get_rt_prfl_cache
1698 (p_vrbl_rt_prfl_id in number
1699 ,p_effective_date in date
1700 ,p_lf_evt_ocrd_dt in date
1701 ,p_business_group_id in number
1702 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lbrmmbr_inst_tbl
1703 ,p_inst_count out nocopy number)
1704 is
1705 --
1706 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1707 l_lookup_query long;
1708 l_inst_query long;
1709 l_instcolnm_set ben_cache.instcolnmtype;
1710 --
1711 begin
1712 --
1713 g_lbrmmbr_out.delete;
1714 --
1715 if g_lbrmmbr_lookup.count = 0 then
1716 --
1717 -- Cache not populated yet. So populate it now.
1718 --
1719 l_lookup_query :=
1720 'select vrbl_rt_prfl_id, business_group_id' ||
1721 ' from ben_vrbl_rt_prfl_f vpf' ||
1722 ' where business_group_id = ' || to_char(p_business_group_id) ||
1723 ' and exists (select null' ||
1724 ' from ben_lbr_mmbr_rt_f elu' ||
1725 ' where elu.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1726 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1727 ' between elu.effective_start_date' ||
1728 ' and elu.effective_end_date) ' ||
1729 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1730 ' between vpf.effective_start_date and vpf.effective_end_date;';
1731 --
1732 l_inst_query :=
1733 'select elu.vrbl_rt_prfl_id, elu.lbr_mmbr_flag, elu.excld_flag' ||
1734 ' from ben_lbr_mmbr_rt_f elu' ||
1735 ' where elu.business_group_id = ' || to_char(p_business_group_id) ||
1736 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1737 ' between elu.effective_start_date' ||
1738 ' and elu.effective_end_date' ||
1739 ' order by elu.vrbl_rt_prfl_id;';
1740 --
1741 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1742 l_instcolnm_set(0).caccol_name := 'id';
1743 l_instcolnm_set(1).col_name := 'lbr_mmbr_flag';
1744 l_instcolnm_set(1).caccol_name := 'lbr_mmbr_flag';
1745 l_instcolnm_set(2).col_name := 'excld_flag';
1746 l_instcolnm_set(2).caccol_name := 'excld_flag';
1747 --
1748 ben_cache.write_mastDet_Cache
1749 (p_mastercol_name => 'vrbl_rt_prfl_id'
1750 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1751 ,p_lkup_name => 'ben_rt_prfl_cache.g_lbrmmbr_lookup'
1752 ,p_inst_name => 'ben_rt_prfl_cache.g_lbrmmbr_instance'
1753 ,p_lkup_query => l_lookup_query
1754 ,p_inst_query => l_inst_query
1755 ,p_instcolnm_set => l_instcolnm_set
1756 );
1757 --
1758 end if;
1759 --
1760 -- Cache already populated. Get record set.
1761 --
1762 get_cached_data
1763 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1764 ,p_lookup_name => 'ben_rt_prfl_cache.g_lbrmmbr_lookup'
1765 ,p_inst_name => 'ben_rt_prfl_cache.g_lbrmmbr_instance'
1766 ,p_inst_set_type => 'ben_rt_prfl_cache.g_lbrmmbr_inst_tbl'
1767 ,p_out_inst_name => 'ben_rt_prfl_cache.g_lbrmmbr_out'
1768 );
1769 --
1770 p_inst_set := g_lbrmmbr_out;
1771 p_inst_count := g_inst_count;
1772 --
1773 exception
1774 --
1775 when no_data_found then
1776 --
1777 p_inst_count := 0;
1778 hr_utility.set_location('No Labor Union found', 90);
1779 hr_utility.set_location('Leaving : ' || l_proc, 99);
1780 --
1781 end;
1782 --
1783 -- LEGAL ENTITY
1784 --
1785 procedure get_rt_prfl_cache
1786 (p_vrbl_rt_prfl_id in number
1787 ,p_effective_date in date
1788 ,p_lf_evt_ocrd_dt in date
1789 ,p_business_group_id in number
1790 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lglenty_inst_tbl
1791 ,p_inst_count out nocopy number)
1792 is
1793 --
1794 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
1795 l_lookup_query long;
1796 l_inst_query long;
1797 l_instcolnm_set ben_cache.instcolnmtype;
1798 --
1799 begin
1800 --
1801 g_lglenty_out.delete;
1802 --
1803 if g_lglenty_lookup.count = 0 then
1804 --
1805 -- Cache not populated yet. So populate it now.
1806 --
1807 l_lookup_query :=
1808 'select vrbl_rt_prfl_id, business_group_id' ||
1809 ' from ben_vrbl_rt_prfl_f vpf' ||
1810 ' where business_group_id = ' || to_char(p_business_group_id) ||
1811 ' and exists (select null' ||
1812 ' from ben_lgl_enty_rt_f eln' ||
1813 ' where eln.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
1814 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
1815 ' between eln.effective_start_date' ||
1816 ' and eln.effective_end_date) ' ||
1817 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1818 ' between vpf.effective_start_date and vpf.effective_end_date;';
1819 --
1820 l_inst_query :=
1821 'select eln.vrbl_rt_prfl_id, eln.organization_id, eln.excld_flag' ||
1822 ' from ben_lgl_enty_rt_f eln' ||
1823 ' where eln.business_group_id = ' || to_char(p_business_group_id) ||
1824 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
1825 ' between eln.effective_start_date' ||
1826 ' and eln.effective_end_date' ||
1827 ' order by eln.vrbl_rt_prfl_id;';
1828 --
1829 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
1830 l_instcolnm_set(0).caccol_name := 'id';
1831 l_instcolnm_set(1).col_name := 'organization_id';
1832 l_instcolnm_set(1).caccol_name := 'organization_id';
1833 l_instcolnm_set(2).col_name := 'excld_flag';
1834 l_instcolnm_set(2).caccol_name := 'excld_flag';
1835 --
1836 ben_cache.write_mastDet_Cache
1837 (p_mastercol_name => 'vrbl_rt_prfl_id'
1838 ,p_detailcol_name => 'vrbl_rt_prfl_id'
1839 ,p_lkup_name => 'ben_rt_prfl_cache.g_lglenty_lookup'
1840 ,p_inst_name => 'ben_rt_prfl_cache.g_lglenty_instance'
1841 ,p_lkup_query => l_lookup_query
1842 ,p_inst_query => l_inst_query
1843 ,p_instcolnm_set => l_instcolnm_set
1844 );
1845 --
1846 end if;
1847 --
1848 -- Cache already populated. Get record set.
1849 --
1850 get_cached_data
1851 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
1852 ,p_lookup_name => 'ben_rt_prfl_cache.g_lglenty_lookup'
1853 ,p_inst_name => 'ben_rt_prfl_cache.g_lglenty_instance'
1854 ,p_inst_set_type => 'ben_rt_prfl_cache.g_lglenty_inst_tbl'
1855 ,p_out_inst_name => 'ben_rt_prfl_cache.g_lglenty_out'
1856 );
1857 --
1858 p_inst_set := g_lglenty_out;
1859 p_inst_count := g_inst_count;
1860 --
1861 exception
1862 --
1863 when no_data_found then
1864 --
1865 p_inst_count := 0;
1866 hr_utility.set_location('No Legal Entity found', 90);
1867 hr_utility.set_location('Leaving : ' || l_proc, 99);
1868 --
1869 end;
1870 --
1871 -- LEAVE OF ABSENCE
1872 --
1873 procedure get_rt_prfl_cache
1874 (p_vrbl_rt_prfl_id in number
1875 ,p_effective_date in date
1876 ,p_lf_evt_ocrd_dt in date
1877 ,p_business_group_id in number
1878 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_loa_inst_tbl
1879 ,p_inst_count out nocopy number) is
1880 --
1881 l_proc varchar2(72) := 'get_rt_prfl_cache';
1882 --
1883 l_instcolnm_set ben_cache.InstColNmType;
1884 --
1885 l_torrwnum pls_integer;
1886 l_insttorrw_num pls_integer;
1887 l_index pls_integer;
1888 l_instcolnm_num pls_integer;
1889 l_mastertab_name varchar2(100);
1890 l_masterpkcol_name varchar2(100);
1891 l_lkup_name varchar2(100);
1892 l_inst_name varchar2(100);
1893 --
1894 l_not_hash_found boolean;
1895 --
1896 begin
1897 --
1898 -- Populate the global cache
1899 --
1900 if g_loa_lookup.count = 0 then
1901 --
1902 -- Build the cache
1903 --
1904 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
1905 l_masterpkcol_name := 'vrbl_rt_prfl_id';
1906 l_lkup_name := 'ben_rt_prfl_cache.g_loa_lookup';
1907 l_inst_name := 'ben_rt_prfl_cache.g_loa_instance';
1908 --
1909 l_instcolnm_num := 0;
1910 --
1911 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
1912 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
1913 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1914 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
1915 l_instcolnm_num := l_instcolnm_num+1;
1916 --
1917 l_instcolnm_set(l_instcolnm_num).col_name := 'absence_attendance_type_id';
1918 l_instcolnm_set(l_instcolnm_num).caccol_name := 'absence_attendance_type_id';
1919 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1920 l_instcolnm_num := l_instcolnm_num+1;
1921 --
1922 l_instcolnm_set(l_instcolnm_num).col_name := 'abs_attendance_reason_id';
1923 l_instcolnm_set(l_instcolnm_num).caccol_name := 'abs_attendance_reason_id';
1924 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1925 l_instcolnm_num := l_instcolnm_num+1;
1926 --
1927 l_instcolnm_set(l_instcolnm_num).col_name := 'excld_flag';
1928 l_instcolnm_set(l_instcolnm_num).caccol_name := 'excld_flag';
1929 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
1930 l_instcolnm_num := l_instcolnm_num+1;
1931 --
1932 ben_cache.Write_BGP_Cache
1933 (p_mastertab_name => l_mastertab_name
1934 ,p_masterpkcol_name => l_masterpkcol_name
1935 ,p_table1_name => 'ben_loa_rsn_rt_f'
1936 ,p_business_group_id => p_business_group_id
1937 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
1938 ,p_lkup_name => l_lkup_name
1939 ,p_inst_name => l_inst_name
1940 ,p_instcolnm_set => l_instcolnm_set
1941 );
1942 --
1943 end if;
1944 --
1945 -- Get the instance details
1946 --
1947 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
1948 --
1949 -- Check if hashed value is already allocated
1950 --
1951 if g_loa_lookup.exists(l_index) then
1952 --
1953 -- If it does exist make sure its the right one
1954 --
1955 if g_loa_lookup(l_index).id <> p_vrbl_rt_prfl_id then
1956 --
1957 l_not_hash_found := false;
1958 --
1959 -- Loop until un-allocated has value is derived
1960 --
1961 while not l_not_hash_found loop
1962 --
1963 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
1964 --
1965 -- Check if the hash index exists, if not we can use it
1966 --
1967 if not g_loa_lookup.exists(l_index) then
1968 --
1969 -- Lets store the hash value in the index
1970 --
1971 l_not_hash_found := true;
1972 exit;
1973 --
1974 else
1975 --
1976 l_not_hash_found := false;
1977 --
1978 end if;
1979 --
1980 end loop;
1981 --
1982 end if;
1983 --
1984 end if;
1985 --
1986 l_torrwnum := 0;
1987 for l_insttorrw_num in g_loa_lookup(l_index).starttorele_num ..
1988 g_loa_lookup(l_index).endtorele_num loop
1989 --
1990 p_inst_set(l_torrwnum) := g_loa_instance(l_insttorrw_num);
1991 l_torrwnum := l_torrwnum+1;
1992 --
1993 end loop;
1994 --
1995 p_inst_count := l_torrwnum;
1996 --
1997 exception
1998 --
1999 when no_data_found then
2000 --
2001 p_inst_count := 0;
2002 --
2003 end get_rt_prfl_cache;
2004 --
2005 -- ORGANIZATION UNIT
2006 --
2007 procedure get_rt_prfl_cache
2008 (p_vrbl_rt_prfl_id in number
2009 ,p_effective_date in date
2010 ,p_lf_evt_ocrd_dt in date
2011 ,p_business_group_id in number
2012 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_org_inst_tbl
2013 ,p_inst_count out nocopy number) is
2014 --
2015 l_proc varchar2(72) := 'get_rt_prfl_cache';
2016 --
2017 l_instcolnm_set ben_cache.InstColNmType;
2018 --
2019 l_torrwnum pls_integer;
2020 l_insttorrw_num pls_integer;
2021 l_index pls_integer;
2022 l_instcolnm_num pls_integer;
2023 l_mastertab_name varchar2(100);
2024 l_masterpkcol_name varchar2(100);
2025 l_lkup_name varchar2(100);
2026 l_inst_name varchar2(100);
2027 --
2028 l_not_hash_found boolean;
2029 --
2030 begin
2031 --
2032 -- Populate the global cache
2033 --
2034 if g_org_lookup.count = 0 then
2035 --
2036 -- Build the cache
2037 --
2038 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
2039 l_masterpkcol_name := 'vrbl_rt_prfl_id';
2040 l_lkup_name := 'ben_rt_prfl_cache.g_org_lookup';
2041 l_inst_name := 'ben_rt_prfl_cache.g_org_instance';
2042 --
2043 l_instcolnm_num := 0;
2044 --
2045 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
2046 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
2047 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2048 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
2049 l_instcolnm_num := l_instcolnm_num+1;
2050 --
2051 l_instcolnm_set(l_instcolnm_num).col_name := 'organization_id';
2052 l_instcolnm_set(l_instcolnm_num).caccol_name := 'organization_id';
2053 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2054 l_instcolnm_num := l_instcolnm_num+1;
2055 --
2056 l_instcolnm_set(l_instcolnm_num).col_name := 'excld_flag';
2057 l_instcolnm_set(l_instcolnm_num).caccol_name := 'excld_flag';
2058 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2059 l_instcolnm_num := l_instcolnm_num+1;
2060 --
2061 ben_cache.Write_BGP_Cache
2062 (p_mastertab_name => l_mastertab_name
2063 ,p_masterpkcol_name => l_masterpkcol_name
2064 ,p_table1_name => 'ben_org_unit_rt_f'
2065 ,p_business_group_id => p_business_group_id
2066 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
2067 ,p_lkup_name => l_lkup_name
2068 ,p_inst_name => l_inst_name
2069 ,p_instcolnm_set => l_instcolnm_set
2070 );
2071 --
2072 end if;
2073 --
2074 -- Get the instance details
2075 --
2076 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
2077 --
2078 -- Check if hashed value is already allocated
2079 --
2080 if g_org_lookup.exists(l_index) then
2081 --
2082 -- If it does exist make sure its the right one
2083 --
2084 if g_org_lookup(l_index).id <> p_vrbl_rt_prfl_id then
2085 --
2086 l_not_hash_found := false;
2087 --
2088 -- Loop until un-allocated has value is derived
2089 --
2090 while not l_not_hash_found loop
2091 --
2092 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
2093 --
2094 -- Check if the hash index exists, if not we can use it
2095 --
2096 if not g_org_lookup.exists(l_index) then
2097 --
2098 -- Lets store the hash value in the index
2099 --
2100 l_not_hash_found := true;
2101 exit;
2102 --
2103 else
2104 --
2105 l_not_hash_found := false;
2106 --
2107 end if;
2108 --
2109 end loop;
2110 --
2111 end if;
2112 --
2113 end if;
2114 --
2115 l_torrwnum := 0;
2116 for l_insttorrw_num in g_org_lookup(l_index).starttorele_num ..
2117 g_org_lookup(l_index).endtorele_num loop
2118 --
2119 p_inst_set(l_torrwnum) := g_org_instance(l_insttorrw_num);
2120 l_torrwnum := l_torrwnum+1;
2121 --
2122 end loop;
2123 --
2124 p_inst_count := l_torrwnum;
2125 --
2126 exception
2127 --
2128 when no_data_found then
2129 --
2130 p_inst_count := 0;
2131 --
2132 end get_rt_prfl_cache;
2133 --
2134 -- PERSON TYPE
2135 --
2136 procedure get_rt_prfl_cache
2137 (p_vrbl_rt_prfl_id in number
2138 ,p_effective_date in date
2139 ,p_lf_evt_ocrd_dt in date
2140 ,p_business_group_id in number
2141 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pertyp_inst_tbl
2142 ,p_inst_count out nocopy number)
2143 is
2144 --
2145 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2146 l_lookup_query long;
2147 l_inst_query long;
2148 l_instcolnm_set ben_cache.instcolnmtype;
2149 --
2150 begin
2151 --
2152 g_pertyp_out.delete;
2153 --
2154 if g_pertyp_lookup.count = 0 then
2155 --
2156 -- Cache not populated yet. So populate it now.
2157 --
2158 l_lookup_query :=
2159 'select vrbl_rt_prfl_id, business_group_id' ||
2160 ' from ben_vrbl_rt_prfl_f vpf' ||
2161 ' where business_group_id = ' || to_char(p_business_group_id) ||
2162 ' and exists (select null' ||
2163 ' from ben_per_typ_rt_f ptr' ||
2164 ' where ptr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2165 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2166 ' between ptr.effective_start_date' ||
2167 ' and ptr.effective_end_date) ' ||
2168 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2169 ' between vpf.effective_start_date and vpf.effective_end_date;';
2170 --
2171 l_inst_query :=
2172 --
2173 -- Bug 1631182 - To support user created person_type_id
2174 -- decided not to use per_typ_cd instead use person_type_id
2175 --
2176 --'select ptr.vrbl_rt_prfl_id, ptr.per_typ_cd, ptr.excld_flag' ||
2177 'select ptr.vrbl_rt_prfl_id, ptr.person_type_id, ptr.excld_flag' ||
2178 ' from ben_per_typ_rt_f ptr' ||
2179 ' where ptr.business_group_id = ' || to_char(p_business_group_id) ||
2180 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2181 ' between ptr.effective_start_date' ||
2182 ' and ptr.effective_end_date' ||
2183 ' order by ptr.vrbl_rt_prfl_id;';
2184 --
2185 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2186 l_instcolnm_set(0).caccol_name := 'id';
2187 -- l_instcolnm_set(1).col_name := 'per_typ_cd';
2188 l_instcolnm_set(1).col_name := 'person_type_id';
2189 --l_instcolnm_set(1).caccol_name := 'per_typ_cd';
2190 l_instcolnm_set(1).caccol_name := 'person_type_id';
2191 l_instcolnm_set(2).col_name := 'excld_flag';
2192 l_instcolnm_set(2).caccol_name := 'excld_flag';
2193 --
2194 ben_cache.write_mastDet_Cache
2195 (p_mastercol_name => 'vrbl_rt_prfl_id'
2196 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2197 ,p_lkup_name => 'ben_rt_prfl_cache.g_pertyp_lookup'
2198 ,p_inst_name => 'ben_rt_prfl_cache.g_pertyp_instance'
2199 ,p_lkup_query => l_lookup_query
2200 ,p_inst_query => l_inst_query
2201 ,p_instcolnm_set => l_instcolnm_set
2202 );
2203 --
2204 end if;
2205 --
2206 -- Cache already populated. Get record set.
2207 --
2208 get_cached_data
2209 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2210 ,p_lookup_name => 'ben_rt_prfl_cache.g_pertyp_lookup'
2211 ,p_inst_name => 'ben_rt_prfl_cache.g_pertyp_instance'
2212 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pertyp_inst_tbl'
2213 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pertyp_out'
2214 );
2215 --
2216 p_inst_set := g_pertyp_out;
2217 p_inst_count := g_inst_count;
2218 --
2219 exception
2220 --
2221 when no_data_found then
2222 --
2223 p_inst_count := 0;
2224 hr_utility.set_location('No Person Type found', 90);
2225 hr_utility.set_location('Leaving : ' || l_proc, 99);
2226 --
2227 end;
2228 --
2229 -- POSTAL ZIP RANGE
2230 --
2231 procedure get_rt_prfl_cache
2232 (p_vrbl_rt_prfl_id in number
2233 ,p_effective_date in date
2234 ,p_lf_evt_ocrd_dt in date
2235 ,p_business_group_id in number
2236 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ziprng_inst_tbl
2237 ,p_inst_count out nocopy number)
2238 is
2239 --
2240 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2241 l_lookup_query long;
2242 l_inst_query long;
2243 l_instcolnm_set ben_cache.instcolnmtype;
2244 --
2245 begin
2246 --
2247 g_ziprng_out.delete;
2248 --
2249 if g_ziprng_lookup.count = 0 then
2250 --
2251 -- Cache not populated yet. So populate it now.
2252 --
2253 l_lookup_query :=
2254 'select vrbl_rt_prfl_id, business_group_id' ||
2255 ' from ben_vrbl_rt_prfl_f vpf' ||
2256 ' where business_group_id = ' || to_char(p_business_group_id) ||
2257 ' and exists (select null' ||
2258 ' from ben_pstl_zip_rt_f epz' ||
2259 ' where epz.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2260 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2261 ' between epz.effective_start_date' ||
2262 ' and epz.effective_end_date) ' ||
2263 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2264 ' between vpf.effective_start_date and vpf.effective_end_date;';
2265 --
2266 l_inst_query :=
2267 'select epz.vrbl_rt_prfl_id, rzr.from_value,' ||
2268 ' rzr.to_value, epz.excld_flag' ||
2269 ' from ben_pstl_zip_rng_f rzr, ben_pstl_zip_rt_f epz' ||
2270 ' where epz.business_group_id = ' || to_char(p_business_group_id) ||
2271 ' and epz.pstl_zip_rng_id = rzr.pstl_zip_rng_id' ||
2272 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2273 ' between epz.effective_start_date' ||
2274 ' and epz.effective_end_date' ||
2275 ' order by epz.vrbl_rt_prfl_id;';
2276 --
2277 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2278 l_instcolnm_set(0).caccol_name := 'id';
2279 l_instcolnm_set(1).col_name := 'from_value';
2280 l_instcolnm_set(1).caccol_name := 'from_value';
2281 l_instcolnm_set(2).col_name := 'to_value';
2282 l_instcolnm_set(2).caccol_name := 'to_value';
2283 l_instcolnm_set(3).col_name := 'excld_flag';
2284 l_instcolnm_set(3).caccol_name := 'excld_flag';
2285 --
2286 ben_cache.write_mastDet_Cache
2287 (p_mastercol_name => 'vrbl_rt_prfl_id'
2288 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2289 ,p_lkup_name => 'ben_rt_prfl_cache.g_ziprng_lookup'
2290 ,p_inst_name => 'ben_rt_prfl_cache.g_ziprng_instance'
2291 ,p_lkup_query => l_lookup_query
2292 ,p_inst_query => l_inst_query
2293 ,p_instcolnm_set => l_instcolnm_set
2294 );
2295 --
2296 end if;
2297 --
2298 -- Cache already populated. Get record set.
2299 --
2300 get_cached_data
2301 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2302 ,p_lookup_name => 'ben_rt_prfl_cache.g_ziprng_lookup'
2303 ,p_inst_name => 'ben_rt_prfl_cache.g_ziprng_instance'
2304 ,p_inst_set_type => 'ben_rt_prfl_cache.g_ziprng_inst_tbl'
2305 ,p_out_inst_name => 'ben_rt_prfl_cache.g_ziprng_out'
2306 );
2307 --
2308 p_inst_set := g_ziprng_out;
2309 p_inst_count := g_inst_count;
2310 --
2311 exception
2312 --
2313 when no_data_found then
2314 --
2315 p_inst_count := 0;
2316 hr_utility.set_location('No Zip found', 90);
2317 hr_utility.set_location('Leaving : ' || l_proc, 99);
2318 --
2319 end;
2320 --
2321 -- PAYROLL
2322 --
2323 procedure get_rt_prfl_cache
2324 (p_vrbl_rt_prfl_id in number
2325 ,p_effective_date in date
2326 ,p_lf_evt_ocrd_dt in date
2327 ,p_business_group_id in number
2328 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pyrl_inst_tbl
2329 ,p_inst_count out nocopy number)
2330 is
2331 --
2332 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2333 l_lookup_query long;
2334 l_inst_query long;
2335 l_instcolnm_set ben_cache.instcolnmtype;
2336 --
2337 begin
2338 --
2339 g_pyrl_out.delete;
2340 --
2341 if g_pyrl_lookup.count = 0 then
2342 --
2343 -- Cache not populated yet. So populate it now.
2344 --
2345 l_lookup_query :=
2346 'select vrbl_rt_prfl_id, business_group_id' ||
2347 ' from ben_vrbl_rt_prfl_f vpf' ||
2348 ' where business_group_id = ' || to_char(p_business_group_id) ||
2349 ' and exists (select null' ||
2350 ' from ben_pyrl_rt_f pr' ||
2351 ' where pr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2352 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2353 ' between pr.effective_start_date' ||
2354 ' and pr.effective_end_date) ' ||
2355 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2356 ' between vpf.effective_start_date and vpf.effective_end_date;';
2357 --
2358 l_inst_query :=
2359 'select pr.vrbl_rt_prfl_id, pr.payroll_id, pr.excld_flag' ||
2360 ' from ben_pyrl_rt_f pr' ||
2361 ' where pr.business_group_id = ' || to_char(p_business_group_id) ||
2362 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2363 ' between pr.effective_start_date' ||
2364 ' and pr.effective_end_date' ||
2365 ' order by pr.vrbl_rt_prfl_id;';
2366 --
2367 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2368 l_instcolnm_set(0).caccol_name := 'id';
2369 l_instcolnm_set(1).col_name := 'payroll_id';
2370 l_instcolnm_set(1).caccol_name := 'payroll_id';
2371 l_instcolnm_set(2).col_name := 'excld_flag';
2372 l_instcolnm_set(2).caccol_name := 'excld_flag';
2373 --
2374 ben_cache.write_mastDet_Cache
2375 (p_mastercol_name => 'vrbl_rt_prfl_id'
2376 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2377 ,p_lkup_name => 'ben_rt_prfl_cache.g_pyrl_lookup'
2378 ,p_inst_name => 'ben_rt_prfl_cache.g_pyrl_instance'
2379 ,p_lkup_query => l_lookup_query
2380 ,p_inst_query => l_inst_query
2381 ,p_instcolnm_set => l_instcolnm_set
2382 );
2383 --
2384 end if;
2385 --
2386 -- Cache already populated. Get record set.
2387 --
2388 get_cached_data
2389 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2390 ,p_lookup_name => 'ben_rt_prfl_cache.g_pyrl_lookup'
2391 ,p_inst_name => 'ben_rt_prfl_cache.g_pyrl_instance'
2392 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pyrl_inst_tbl'
2393 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pyrl_out'
2394 );
2395 --
2396 p_inst_set := g_pyrl_out;
2397 p_inst_count := g_inst_count;
2398 --
2399 exception
2400 --
2401 when no_data_found then
2402 --
2403 p_inst_count := 0;
2404 hr_utility.set_location('No Payroll found', 90);
2405 hr_utility.set_location('Leaving : ' || l_proc, 99);
2406 --
2407 end;
2408 --
2409 -- PAY BASIS
2410 --
2411 procedure get_rt_prfl_cache
2412 (p_vrbl_rt_prfl_id in number
2413 ,p_effective_date in date
2414 ,p_lf_evt_ocrd_dt in date
2415 ,p_business_group_id in number
2416 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_py_bss_inst_tbl
2417 ,p_inst_count out nocopy number)
2418 is
2419 --
2420 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2421 l_lookup_query long;
2422 l_inst_query long;
2423 l_instcolnm_set ben_cache.instcolnmtype;
2424 --
2425 begin
2426 --
2427 g_py_bss_out.delete;
2428 --
2429 if g_py_bss_lookup.count = 0 then
2430 --
2431 -- Cache not populated yet. So populate it now.
2432 --
2433 l_lookup_query :=
2434 'select vrbl_rt_prfl_id, business_group_id' ||
2435 ' from ben_vrbl_rt_prfl_f vpf' ||
2436 ' where business_group_id = ' || to_char(p_business_group_id) ||
2437 ' and exists (select null' ||
2438 ' from ben_py_bss_rt_f pbr' ||
2439 ' where pbr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2440 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2441 ' between pbr.effective_start_date' ||
2442 ' and pbr.effective_end_date) ' ||
2443 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2444 ' between vpf.effective_start_date and vpf.effective_end_date;';
2445 --
2446 l_inst_query :=
2447 'select pbr.vrbl_rt_prfl_id, pbr.pay_basis_id, pbr.excld_flag' ||
2448 ' from ben_py_bss_rt_f pbr' ||
2449 ' where pbr.business_group_id = ' || to_char(p_business_group_id) ||
2450 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2451 ' between pbr.effective_start_date' ||
2452 ' and pbr.effective_end_date' ||
2453 ' order by pbr.vrbl_rt_prfl_id;';
2454 --
2455 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2456 l_instcolnm_set(0).caccol_name := 'id';
2457 l_instcolnm_set(1).col_name := 'pay_basis_id';
2458 l_instcolnm_set(1).caccol_name := 'pay_basis_id';
2459 l_instcolnm_set(2).col_name := 'excld_flag';
2460 l_instcolnm_set(2).caccol_name := 'excld_flag';
2461 --
2462 ben_cache.write_mastDet_Cache
2463 (p_mastercol_name => 'vrbl_rt_prfl_id'
2464 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2465 ,p_lkup_name => 'ben_rt_prfl_cache.g_py_bss_lookup'
2466 ,p_inst_name => 'ben_rt_prfl_cache.g_py_bss_instance'
2467 ,p_lkup_query => l_lookup_query
2468 ,p_inst_query => l_inst_query
2469 ,p_instcolnm_set => l_instcolnm_set
2470 );
2471 --
2472 end if;
2473 --
2474 -- Cache already populated. Get record set.
2475 --
2476 get_cached_data
2477 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2478 ,p_lookup_name => 'ben_rt_prfl_cache.g_py_bss_lookup'
2479 ,p_inst_name => 'ben_rt_prfl_cache.g_py_bss_instance'
2480 ,p_inst_set_type => 'ben_rt_prfl_cache.g_py_bss_inst_tbl'
2481 ,p_out_inst_name => 'ben_rt_prfl_cache.g_py_bss_out'
2482 );
2483 --
2484 p_inst_set := g_py_bss_out;
2485 p_inst_count := g_inst_count;
2486 --
2487 exception
2488 --
2489 when no_data_found then
2490 --
2491 p_inst_count := 0;
2492 hr_utility.set_location('No Pay Basis found', 90);
2493 hr_utility.set_location('Leaving : ' || l_proc, 99);
2494 --
2495 end;
2496 --
2497 -- SCHEDULED HOURS
2498 --
2499 procedure get_rt_prfl_cache
2500 (p_vrbl_rt_prfl_id in number
2501 ,p_effective_date in date
2502 ,p_lf_evt_ocrd_dt in date
2503 ,p_business_group_id in number
2504 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_scdhrs_inst_tbl
2505 ,p_inst_count out nocopy number)
2506 is
2507 --
2508 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2509 l_lookup_query long;
2510 l_inst_query long;
2511 l_instcolnm_set ben_cache.instcolnmtype;
2512 --
2513 begin
2514 --
2515 g_scdhrs_out.delete;
2516 --
2517 if g_scdhrs_lookup.count = 0 then
2518 --
2519 -- Cache not populated yet. So populate it now.
2520 --
2521 l_lookup_query :=
2522 'select vrbl_rt_prfl_id, business_group_id' ||
2523 ' from ben_vrbl_rt_prfl_f vpf' ||
2524 ' where business_group_id = ' || to_char(p_business_group_id) ||
2525 ' and exists (select null' ||
2526 ' from ben_schedd_hrs_rt_f shr' ||
2527 ' where shr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2528 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2529 ' between shr.effective_start_date' ||
2530 ' and shr.effective_end_date) ' ||
2531 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2532 ' between vpf.effective_start_date and vpf.effective_end_date;';
2533 --
2534 l_inst_query :=
2535 'select shr.vrbl_rt_prfl_id, shr.hrs_num, shr.freq_cd, '||
2536 ' shr.max_hrs_num, shr.schedd_hrs_rl, shr.determination_cd, shr.determination_rl, '||
2537 ' shr.rounding_cd, shr.rounding_rl, ' ||
2538 ' shr.excld_flag' ||
2539 ' from ben_schedd_hrs_rt_f shr' ||
2540 ' where shr.business_group_id = ' || to_char(p_business_group_id) ||
2541 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2542 ' between shr.effective_start_date' ||
2543 ' and shr.effective_end_date' ||
2544 ' order by shr.vrbl_rt_prfl_id;';
2545 --
2546 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2547 l_instcolnm_set(0).caccol_name := 'id';
2548 l_instcolnm_set(1).col_name := 'hrs_num';
2549 l_instcolnm_set(1).caccol_name := 'hrs_num';
2550 l_instcolnm_set(2).col_name := 'freq_cd';
2551 l_instcolnm_set(2).caccol_name := 'freq_cd';
2552 l_instcolnm_set(3).col_name := 'max_hrs_num';
2553 l_instcolnm_set(3).caccol_name := 'max_hrs_num';
2554 l_instcolnm_set(4).col_name := 'schedd_hrs_rl';
2555 l_instcolnm_set(4).caccol_name := 'schedd_hrs_rl';
2556 l_instcolnm_set(5).col_name := 'determination_cd';
2557 l_instcolnm_set(5).caccol_name := 'determination_cd';
2558 l_instcolnm_set(6).col_name := 'determination_rl';
2559 l_instcolnm_set(6).caccol_name := 'determination_rl';
2560 l_instcolnm_set(7).col_name := 'rounding_cd';
2561 l_instcolnm_set(7).caccol_name := 'rounding_cd';
2562 l_instcolnm_set(8).col_name := 'rounding_rl';
2563 l_instcolnm_set(8).caccol_name := 'rounding_rl';
2564 l_instcolnm_set(9).col_name := 'excld_flag';
2565 l_instcolnm_set(9).caccol_name := 'excld_flag';
2566
2567 --
2568 ben_cache.write_mastDet_Cache
2569 (p_mastercol_name => 'vrbl_rt_prfl_id'
2570 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2571 ,p_lkup_name => 'ben_rt_prfl_cache.g_scdhrs_lookup'
2572 ,p_inst_name => 'ben_rt_prfl_cache.g_scdhrs_instance'
2573 ,p_lkup_query => l_lookup_query
2574 ,p_inst_query => l_inst_query
2575 ,p_instcolnm_set => l_instcolnm_set
2576 );
2577 --
2578 end if;
2579 --
2580 -- Cache already populated. Get record set.
2581 --
2582 get_cached_data
2583 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2584 ,p_lookup_name => 'ben_rt_prfl_cache.g_scdhrs_lookup'
2585 ,p_inst_name => 'ben_rt_prfl_cache.g_scdhrs_instance'
2586 ,p_inst_set_type => 'ben_rt_prfl_cache.g_scdhrs_inst_tbl'
2587 ,p_out_inst_name => 'ben_rt_prfl_cache.g_scdhrs_out'
2588 );
2589 --
2590 p_inst_set := g_scdhrs_out;
2591 p_inst_count := g_inst_count;
2592
2593 --
2594 exception
2595 --
2596 when no_data_found then
2597 --
2598 p_inst_count := 0;
2599 hr_utility.set_location('No Scheduled Hours found', 90);
2600 hr_utility.set_location('Leaving : ' || l_proc, 99);
2601 --
2602 end;
2603 --
2604 -- WORK LOCATION
2605 --
2606 procedure get_rt_prfl_cache
2607 (p_vrbl_rt_prfl_id in number
2608 ,p_effective_date in date
2609 ,p_lf_evt_ocrd_dt in date
2610 ,p_business_group_id in number
2611 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_wkloc_inst_tbl
2612 ,p_inst_count out nocopy number)
2613 is
2614 --
2615 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2616 l_lookup_query long;
2617 l_inst_query long;
2618 l_instcolnm_set ben_cache.instcolnmtype;
2619 --
2620 begin
2621 --
2622 g_wkloc_out.delete;
2623 --
2624 if g_wkloc_lookup.count = 0 then
2625 --
2626 -- Cache not populated yet. So populate it now.
2627 --
2628 l_lookup_query :=
2629 'select vrbl_rt_prfl_id, business_group_id' ||
2630 ' from ben_vrbl_rt_prfl_f vpf' ||
2631 ' where business_group_id = ' || to_char(p_business_group_id) ||
2632 ' and exists (select null' ||
2633 ' from ben_wk_loc_rt_f wlr' ||
2634 ' where wlr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2635 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2636 ' between wlr.effective_start_date' ||
2637 ' and wlr.effective_end_date) ' ||
2638 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2639 ' between vpf.effective_start_date and vpf.effective_end_date;';
2640 --
2641 l_inst_query :=
2642 'select wlr.vrbl_rt_prfl_id, wlr.location_id , wlr.excld_flag' ||
2643 ' from ben_wk_loc_rt_f wlr' ||
2644 ' where wlr.business_group_id = ' || to_char(p_business_group_id) ||
2645 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2646 ' between wlr.effective_start_date' ||
2647 ' and wlr.effective_end_date' ||
2648 ' order by wlr.vrbl_rt_prfl_id;';
2649 --
2650 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2651 l_instcolnm_set(0).caccol_name := 'id';
2652 l_instcolnm_set(1).col_name := 'location_id';
2653 l_instcolnm_set(1).caccol_name := 'location_id';
2654 l_instcolnm_set(2).col_name := 'excld_flag';
2655 l_instcolnm_set(2).caccol_name := 'excld_flag';
2656 --
2657 ben_cache.write_mastDet_Cache
2658 (p_mastercol_name => 'vrbl_rt_prfl_id'
2659 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2660 ,p_lkup_name => 'ben_rt_prfl_cache.g_wkloc_lookup'
2661 ,p_inst_name => 'ben_rt_prfl_cache.g_wkloc_instance'
2662 ,p_lkup_query => l_lookup_query
2663 ,p_inst_query => l_inst_query
2664 ,p_instcolnm_set => l_instcolnm_set
2665 );
2666 --
2667 end if;
2668 --
2669 -- Cache already populated. Get record set.
2670 --
2671 get_cached_data
2672 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2673 ,p_lookup_name => 'ben_rt_prfl_cache.g_wkloc_lookup'
2674 ,p_inst_name => 'ben_rt_prfl_cache.g_wkloc_instance'
2675 ,p_inst_set_type => 'ben_rt_prfl_cache.g_wkloc_inst_tbl'
2676 ,p_out_inst_name => 'ben_rt_prfl_cache.g_wkloc_out'
2677 );
2678 --
2679 p_inst_set := g_wkloc_out;
2680 p_inst_count := g_inst_count;
2681 --
2682 exception
2683 --
2684 when no_data_found then
2685 --
2686 p_inst_count := 0;
2687 hr_utility.set_location('No Work Location found', 90);
2688 hr_utility.set_location('Leaving : ' || l_proc, 99);
2689 --
2690 end;
2691 --
2692 -- SERVICE AREA
2693 --
2694 procedure get_rt_prfl_cache
2695 (p_vrbl_rt_prfl_id in number
2696 ,p_effective_date in date
2697 ,p_lf_evt_ocrd_dt in date
2698 ,p_business_group_id in number
2699 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_svcarea_inst_tbl
2700 ,p_inst_count out nocopy number)
2701 is
2702 --
2703 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2704 l_lookup_query long;
2705 l_inst_query long;
2706 l_instcolnm_set ben_cache.instcolnmtype;
2707 --
2708 begin
2709 --
2710 g_svcarea_out.delete;
2711 --
2712 if g_svcarea_lookup.count = 0 then
2713 --
2714 -- Cache not populated yet. So populate it now.
2715 --
2716 l_lookup_query :=
2717 'select vrbl_rt_prfl_id, business_group_id' ||
2718 ' from ben_vrbl_rt_prfl_f vpf' ||
2719 ' where business_group_id = ' || to_char(p_business_group_id) ||
2720 ' and exists (select null' ||
2721 ' from ben_svc_area_rt_f sar' ||
2722 ' where sar.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2723 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2724 ' between sar.effective_start_date' ||
2725 ' and sar.effective_end_date) ' ||
2726 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2727 ' between vpf.effective_start_date and vpf.effective_end_date;';
2728 --
2729 l_inst_query :=
2730 'select sar.vrbl_rt_prfl_id, pzr.from_value,' ||
2731 ' pzr.to_value, sar.excld_flag' ||
2732 ' from ben_pstl_zip_rng_f pzr, ben_svc_area_pstl_zip_rng_f spz,' ||
2733 ' ben_svc_area_rt_f sar' ||
2734 ' where sar.business_group_id = ' || to_char(p_business_group_id) ||
2735 ' and sar.svc_area_id = spz.svc_area_id' ||
2736 ' and spz.pstl_zip_rng_id = pzr.pstl_zip_rng_id' ||
2737 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2738 ' between sar.effective_start_date' ||
2739 ' and sar.effective_end_date' ||
2740 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2741 ' between pzr.effective_start_date' ||
2742 ' and pzr.effective_end_date' ||
2743 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2744 ' between spz.effective_start_date' ||
2745 ' and spz.effective_end_date' ||
2746 ' order by sar.vrbl_rt_prfl_id;';
2747 --
2748 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2749 l_instcolnm_set(0).caccol_name := 'id';
2750 l_instcolnm_set(1).col_name := 'from_value';
2751 l_instcolnm_set(1).caccol_name := 'from_value';
2752 l_instcolnm_set(2).col_name := 'to_value';
2753 l_instcolnm_set(2).caccol_name := 'to_value';
2754 l_instcolnm_set(3).col_name := 'excld_flag';
2755 l_instcolnm_set(3).caccol_name := 'excld_flag';
2756 --
2757 ben_cache.write_mastDet_Cache
2758 (p_mastercol_name => 'vrbl_rt_prfl_id'
2759 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2760 ,p_lkup_name => 'ben_rt_prfl_cache.g_svcarea_lookup'
2761 ,p_inst_name => 'ben_rt_prfl_cache.g_svcarea_instance'
2762 ,p_lkup_query => l_lookup_query
2763 ,p_inst_query => l_inst_query
2764 ,p_instcolnm_set => l_instcolnm_set
2765 );
2766 --
2767 end if;
2768 --
2769 -- Cache already populated. Get record set.
2770 --
2771 get_cached_data
2772 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2773 ,p_lookup_name => 'ben_rt_prfl_cache.g_svcarea_lookup'
2774 ,p_inst_name => 'ben_rt_prfl_cache.g_svcarea_instance'
2775 ,p_inst_set_type => 'ben_rt_prfl_cache.g_svcarea_inst_tbl'
2776 ,p_out_inst_name => 'ben_rt_prfl_cache.g_svcarea_out'
2777 );
2778 --
2779 p_inst_set := g_svcarea_out;
2780 p_inst_count := g_inst_count;
2781 --
2782 exception
2783 --
2784 when no_data_found then
2785 --
2786 p_inst_count := 0;
2787 hr_utility.set_location('No Service Area found', 90);
2788 hr_utility.set_location('Leaving : ' || l_proc, 99);
2789 --
2790 end;
2791 --
2792 -- HOURLY OR SALARIED
2793 --
2794 procedure get_rt_prfl_cache
2795 (p_vrbl_rt_prfl_id in number
2796 ,p_effective_date in date
2797 ,p_lf_evt_ocrd_dt in date
2798 ,p_business_group_id in number
2799 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_hrlyslrd_inst_tbl
2800 ,p_inst_count out nocopy number)
2801 is
2802 --
2803 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
2804 l_lookup_query long;
2805 l_inst_query long;
2806 l_instcolnm_set ben_cache.instcolnmtype;
2807 --
2808 begin
2809 --
2810 g_hrlyslrd_out.delete;
2811 --
2812 if g_hrlyslrd_lookup.count = 0 then
2813 --
2814 -- Cache not populated yet. So populate it now.
2815 --
2816 l_lookup_query :=
2817 'select vrbl_rt_prfl_id, business_group_id' ||
2818 ' from ben_vrbl_rt_prfl_f vpf' ||
2819 ' where business_group_id = ' || to_char(p_business_group_id) ||
2820 ' and exists (select null' ||
2821 ' from ben_hrly_slrd_rt_f hsr' ||
2822 ' where hsr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
2823 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
2824 ' between hsr.effective_start_date' ||
2825 ' and hsr.effective_end_date) ' ||
2826 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2827 ' between vpf.effective_start_date and vpf.effective_end_date;';
2828 --
2829 l_inst_query :=
2830 'select hsr.vrbl_rt_prfl_id, hsr.hrly_slrd_cd, hsr.excld_flag' ||
2831 ' from ben_hrly_slrd_rt_f hsr' ||
2832 ' where hsr.business_group_id = ' || to_char(p_business_group_id) ||
2833 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
2834 ' between hsr.effective_start_date' ||
2835 ' and hsr.effective_end_date' ||
2836 ' order by hsr.vrbl_rt_prfl_id;';
2837 --
2838 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
2839 l_instcolnm_set(0).caccol_name := 'id';
2840 l_instcolnm_set(1).col_name := 'hrly_slrd_cd';
2841 l_instcolnm_set(1).caccol_name := 'hrly_slrd_cd';
2842 l_instcolnm_set(2).col_name := 'excld_flag';
2843 l_instcolnm_set(2).caccol_name := 'excld_flag';
2844 --
2845 ben_cache.write_mastDet_Cache
2846 (p_mastercol_name => 'vrbl_rt_prfl_id'
2847 ,p_detailcol_name => 'vrbl_rt_prfl_id'
2848 ,p_lkup_name => 'ben_rt_prfl_cache.g_hrlyslrd_lookup'
2849 ,p_inst_name => 'ben_rt_prfl_cache.g_hrlyslrd_instance'
2850 ,p_lkup_query => l_lookup_query
2851 ,p_inst_query => l_inst_query
2852 ,p_instcolnm_set => l_instcolnm_set
2853 );
2854 --
2855 end if;
2856 --
2857 -- Cache already populated. Get record set.
2858 --
2859 get_cached_data
2860 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2861 ,p_lookup_name => 'ben_rt_prfl_cache.g_hrlyslrd_lookup'
2862 ,p_inst_name => 'ben_rt_prfl_cache.g_hrlyslrd_instance'
2863 ,p_inst_set_type => 'ben_rt_prfl_cache.g_hrlyslrd_inst_tbl'
2864 ,p_out_inst_name => 'ben_rt_prfl_cache.g_hrlyslrd_out'
2865 );
2866 --
2867 p_inst_set := g_hrlyslrd_out;
2868 p_inst_count := g_inst_count;
2869 --
2870 exception
2871 --
2872 when no_data_found then
2873 --
2874 p_inst_count := 0;
2875 hr_utility.set_location('No Hourly - Salaried found', 90);
2876 hr_utility.set_location('Leaving : ' || l_proc, 99);
2877 --
2878 end;
2879 --
2880 -- AGE
2881 --
2882 procedure get_rt_prfl_cache
2883 (p_vrbl_rt_prfl_id in number
2884 ,p_effective_date in date
2885 ,p_lf_evt_ocrd_dt in date
2886 ,p_business_group_id in number
2887 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_age_inst_tbl
2888 ,p_inst_count out nocopy number) is
2889 --
2890 l_proc varchar2(72) := 'get_rt_prfl_cache';
2891 --
2892 l_instcolnm_set ben_cache.InstColNmType;
2893 l_tabdet_set ben_cache.TabDetType;
2894 --
2895 l_torrwnum pls_integer;
2896 l_insttorrw_num pls_integer;
2897 l_index pls_integer;
2898 l_instcolnm_num pls_integer;
2899 l_mastertab_name varchar2(100);
2900 l_masterpkcol_name varchar2(100);
2901 l_lkup_name varchar2(100);
2902 l_inst_name varchar2(100);
2903 --
2904 l_not_hash_found boolean;
2905 --
2906 begin
2907 --
2908 -- Populate the global cache
2909 --
2910 if g_age_lookup.count = 0 then
2911 --
2912 -- Build the cache
2913 --
2914 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
2915 l_masterpkcol_name := 'vrbl_rt_prfl_id';
2916 l_lkup_name := 'ben_rt_prfl_cache.g_age_lookup';
2917 l_inst_name := 'ben_rt_prfl_cache.g_age_instance';
2918 --
2919 l_tabdet_set(0).tab_name := 'ben_age_rt_f';
2920 l_tabdet_set(0).tab_jncolnm := 'age_fctr_id';
2921 l_tabdet_set(1).tab_name := 'ben_age_fctr';
2922 l_tabdet_set(1).tab_datetype := 'nondt';
2923 --
2924 l_instcolnm_num := 0;
2925 --
2926 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
2927 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
2928 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2929 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
2930 l_instcolnm_num := l_instcolnm_num+1;
2931 --
2932 l_instcolnm_set(l_instcolnm_num).col_name := 'excld_flag';
2933 l_instcolnm_set(l_instcolnm_num).caccol_name := 'excld_flag';
2934 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2935 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2936 l_instcolnm_num := l_instcolnm_num+1;
2937 --
2938 l_instcolnm_set(l_instcolnm_num).col_name := 'age_fctr_id';
2939 l_instcolnm_set(l_instcolnm_num).caccol_name := 'age_fctr_id';
2940 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
2941 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2942 l_instcolnm_num := l_instcolnm_num+1;
2943 --
2944 l_instcolnm_set(l_instcolnm_num).col_name := 'mn_age_num';
2945 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mn_age_num';
2946 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
2947 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2948 l_instcolnm_num := l_instcolnm_num+1;
2949 --
2950 l_instcolnm_set(l_instcolnm_num).col_name := 'mx_age_num';
2951 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mx_age_num';
2952 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
2953 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2954 l_instcolnm_num := l_instcolnm_num+1;
2955 --
2956 l_instcolnm_set(l_instcolnm_num).col_name := 'no_mn_age_flag';
2957 l_instcolnm_set(l_instcolnm_num).caccol_name := 'no_mn_age_flag';
2958 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
2959 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2960 l_instcolnm_num := l_instcolnm_num+1;
2961 --
2962 l_instcolnm_set(l_instcolnm_num).col_name := 'no_mx_age_flag';
2963 l_instcolnm_set(l_instcolnm_num).caccol_name := 'no_mx_age_flag';
2964 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
2965 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
2966 l_instcolnm_num := l_instcolnm_num+1;
2967 --
2968 ben_cache.Write_BGP_Cache
2969 (p_mastertab_name => l_mastertab_name
2970 ,p_masterpkcol_name => l_masterpkcol_name
2971 ,p_tabdet_set => l_tabdet_set
2972 ,p_table1_name => null
2973 ,p_business_group_id => p_business_group_id
2974 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
2975 ,p_lkup_name => l_lkup_name
2976 ,p_inst_name => l_inst_name
2977 ,p_instcolnm_set => l_instcolnm_set
2978 );
2979 --
2980 end if;
2981 --
2982 -- Get the instance details
2983 --
2984 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
2985 --
2986 -- Check if hashed value is already allocated
2987 --
2988 if g_age_lookup.exists(l_index) then
2989 --
2990 -- If it does exist make sure its the right one
2991 --
2992 if g_age_lookup(l_index).id <> p_vrbl_rt_prfl_id then
2993 --
2994 l_not_hash_found := false;
2995 --
2996 -- Loop until un-allocated has value is derived
2997 --
2998 while not l_not_hash_found loop
2999 --
3000 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
3001 --
3002 -- Check if the hash index exists, if not we can use it
3003 --
3004 if not g_age_lookup.exists(l_index) then
3005 --
3006 -- Lets store the hash value in the index
3007 --
3008 l_not_hash_found := true;
3009 exit;
3010 --
3011 else
3012 --
3013 l_not_hash_found := false;
3014 --
3015 end if;
3016 --
3017 end loop;
3018 --
3019 end if;
3020 --
3021 end if;
3022 --
3023 l_torrwnum := 0;
3024 for l_insttorrw_num in g_age_lookup(l_index).starttorele_num ..
3025 g_age_lookup(l_index).endtorele_num loop
3026 --
3027 p_inst_set(l_torrwnum) := g_age_instance(l_insttorrw_num);
3028 l_torrwnum := l_torrwnum+1;
3029 --
3030 end loop;
3031 --
3032 p_inst_count := l_torrwnum;
3033 --
3034 exception
3035 --
3036 when no_data_found then
3037 --
3038 p_inst_count := 0;
3039 --
3040 end get_rt_prfl_cache;
3041 --
3042 -- COMP LVL CODE
3043 --
3044 procedure get_rt_prfl_cache
3045 (p_vrbl_rt_prfl_id in number
3046 ,p_effective_date in date
3047 ,p_lf_evt_ocrd_dt in date
3048 ,p_business_group_id in number
3049 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_complvl_inst_tbl
3050 ,p_inst_count out nocopy number)
3051 is
3052 --
3053 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3054 l_lookup_query long;
3055 l_inst_query long;
3056 l_instcolnm_set ben_cache.instcolnmtype;
3057 --
3058 begin
3059 --
3060 g_complvl_out.delete;
3061 --
3062 if g_complvl_lookup.count = 0 then
3063 --
3064 -- Cache not populated yet. So populate it now. First build master and
3065 -- detail queries.
3066 --
3067 l_lookup_query :=
3068 'select vrbl_rt_prfl_id, business_group_id' ||
3069 ' from ben_vrbl_rt_prfl_f vpf' ||
3070 ' where business_group_id = ' || to_char(p_business_group_id) ||
3071 ' and exists (select null' ||
3072 ' from ben_comp_lvl_rt_f clr' ||
3073 ' where clr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3074 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3075 ' between clr.effective_start_date' ||
3076 ' and clr.effective_end_date) ' ||
3077 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3078 ' between vpf.effective_start_date and vpf.effective_end_date;';
3079 --
3080 l_inst_query :=
3081 'select clr.vrbl_rt_prfl_id, clf.mn_comp_val, clf.mx_comp_val,' ||
3082 ' clr.comp_lvl_fctr_id, clr.excld_flag, no_mn_comp_flag, no_mx_comp_flag' ||
3083 ' from ben_comp_lvl_fctr clf, ben_comp_lvl_rt_f clr' ||
3084 ' where clr.business_group_id = ' || to_char(p_business_group_id) ||
3085 ' and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id' ||
3086 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3087 ' between clr.effective_start_date' ||
3088 ' and clr.effective_end_date' ||
3089 ' order by clr.vrbl_rt_prfl_id;';
3090 --
3091 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3092 l_instcolnm_set(0).caccol_name := 'id';
3093 l_instcolnm_set(1).col_name := 'mn_comp_val';
3094 l_instcolnm_set(1).caccol_name := 'mn_comp_val';
3095 l_instcolnm_set(2).col_name := 'mx_comp_val';
3096 l_instcolnm_set(2).caccol_name := 'mx_comp_val';
3097 l_instcolnm_set(3).col_name := 'comp_lvl_fctr_id';
3098 l_instcolnm_set(3).caccol_name := 'comp_lvl_fctr_id';
3099 l_instcolnm_set(4).col_name := 'excld_flag';
3100 l_instcolnm_set(4).caccol_name := 'excld_flag';
3101 l_instcolnm_set(5).col_name := 'no_mn_comp_flag';
3102 l_instcolnm_set(5).caccol_name := 'no_mn_comp_flag';
3103 l_instcolnm_set(6).col_name := 'no_mx_comp_flag';
3104 l_instcolnm_set(6).caccol_name := 'no_mx_comp_flag';
3105 --
3106 ben_cache.write_mastDet_Cache
3107 (p_mastercol_name => 'vrbl_rt_prfl_id'
3108 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3109 ,p_lkup_name => 'ben_rt_prfl_cache.g_complvl_lookup'
3110 ,p_inst_name => 'ben_rt_prfl_cache.g_complvl_instance'
3111 ,p_lkup_query => l_lookup_query
3112 ,p_inst_query => l_inst_query
3113 ,p_instcolnm_set => l_instcolnm_set
3114 );
3115 --
3116 end if;
3117 --
3118 -- Cache already populated. Get record set.
3119 --
3120 get_cached_data
3121 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3122 ,p_lookup_name => 'ben_rt_prfl_cache.g_complvl_lookup'
3123 ,p_inst_name => 'ben_rt_prfl_cache.g_complvl_instance'
3124 ,p_inst_set_type => 'ben_rt_prfl_cache.g_complvl_inst_tbl'
3125 ,p_out_inst_name => 'ben_rt_prfl_cache.g_complvl_out'
3126 );
3127 --
3128 p_inst_set := g_complvl_out;
3129 p_inst_count := g_inst_count;
3130 --
3131 exception
3132 --
3133 when no_data_found then
3134 --
3135 p_inst_count := 0;
3136 hr_utility.set_location('No Comp Lvl Code found', 90);
3137 hr_utility.set_location('Leaving : ' || l_proc, 99);
3138 --
3139 end;
3140 --
3141 -- LOS
3142 --
3143 procedure get_rt_prfl_cache
3144 (p_vrbl_rt_prfl_id in number
3145 ,p_effective_date in date
3146 ,p_lf_evt_ocrd_dt in date
3147 ,p_business_group_id in number
3148 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_los_inst_tbl
3149 ,p_inst_count out nocopy number) is
3150 --
3151 l_proc varchar2(72) := 'get_rt_prfl_cache';
3152 --
3153 l_instcolnm_set ben_cache.InstColNmType;
3154 l_tabdet_set ben_cache.TabDetType;
3155 --
3156 l_torrwnum pls_integer;
3157 l_insttorrw_num pls_integer;
3158 l_index pls_integer;
3159 l_instcolnm_num pls_integer;
3160 l_mastertab_name varchar2(100);
3161 l_masterpkcol_name varchar2(100);
3162 l_lkup_name varchar2(100);
3163 l_inst_name varchar2(100);
3164 --
3165 l_not_hash_found boolean;
3166 --
3167 begin
3168 --
3169 -- Populate the global cache
3170 --
3171 if g_los_lookup.count = 0 then
3172 --
3173 -- Build the cache
3174 --
3175 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
3176 l_masterpkcol_name := 'vrbl_rt_prfl_id';
3177 l_lkup_name := 'ben_rt_prfl_cache.g_los_lookup';
3178 l_inst_name := 'ben_rt_prfl_cache.g_los_instance';
3179 --
3180 l_tabdet_set(0).tab_name := 'ben_los_rt_f';
3181 l_tabdet_set(0).tab_jncolnm := 'los_fctr_id';
3182 l_tabdet_set(1).tab_name := 'ben_los_fctr';
3183 l_tabdet_set(1).tab_datetype := 'nondt';
3184 --
3185 l_instcolnm_num := 0;
3186 --
3187 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
3188 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
3189 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3190 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
3191 l_instcolnm_num := l_instcolnm_num+1;
3192 --
3193 l_instcolnm_set(l_instcolnm_num).col_name := 'excld_flag';
3194 l_instcolnm_set(l_instcolnm_num).caccol_name := 'excld_flag';
3195 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3196 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3197 l_instcolnm_num := l_instcolnm_num+1;
3198 --
3199 l_instcolnm_set(l_instcolnm_num).col_name := 'los_fctr_id';
3200 l_instcolnm_set(l_instcolnm_num).caccol_name := 'los_fctr_id';
3201 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3202 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3203 l_instcolnm_num := l_instcolnm_num+1;
3204 --
3205 l_instcolnm_set(l_instcolnm_num).col_name := 'mn_los_num';
3206 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mn_los_num';
3207 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
3208 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3209 l_instcolnm_num := l_instcolnm_num+1;
3210 --
3211 l_instcolnm_set(l_instcolnm_num).col_name := 'mx_los_num';
3212 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mx_los_num';
3213 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
3214 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3215 l_instcolnm_num := l_instcolnm_num+1;
3216 --
3217 l_instcolnm_set(l_instcolnm_num).col_name := 'no_mn_los_num_apls_flag';
3218 l_instcolnm_set(l_instcolnm_num).caccol_name := 'no_mn_los_num_apls_flag';
3219 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
3220 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3221 l_instcolnm_num := l_instcolnm_num+1;
3222 --
3223 l_instcolnm_set(l_instcolnm_num).col_name := 'no_mx_los_num_apls_flag';
3224 l_instcolnm_set(l_instcolnm_num).caccol_name := 'no_mx_los_num_apls_flag';
3225 l_instcolnm_set(l_instcolnm_num).col_alias := 'table2';
3226 l_instcolnm_set(l_instcolnm_num).col_type := 'SELECT';
3227 l_instcolnm_num := l_instcolnm_num+1;
3228 --
3229 ben_cache.Write_BGP_Cache
3230 (p_mastertab_name => l_mastertab_name
3231 ,p_masterpkcol_name => l_masterpkcol_name
3232 ,p_tabdet_set => l_tabdet_set
3233 ,p_table1_name => null
3234 ,p_business_group_id => p_business_group_id
3235 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
3236 ,p_lkup_name => l_lkup_name
3237 ,p_inst_name => l_inst_name
3238 ,p_instcolnm_set => l_instcolnm_set
3239 );
3240 --
3241 end if;
3242 --
3243 -- Get the instance details
3244 --
3245 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
3246 --
3247 -- Check if hashed value is already allocated
3248 --
3249 if g_los_lookup.exists(l_index) then
3250 --
3251 -- If it does exist make sure its the right one
3252 --
3253 if g_los_lookup(l_index).id <> p_vrbl_rt_prfl_id then
3254 --
3255 l_not_hash_found := false;
3256 --
3257 -- Loop until un-allocated has value is derived
3258 --
3259 while not l_not_hash_found loop
3260 --
3261 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
3262 --
3263 -- Check if the hash index exists, if not we can use it
3264 --
3265 if not g_los_lookup.exists(l_index) then
3266 --
3267 -- Lets store the hash value in the index
3268 --
3269 l_not_hash_found := true;
3270 exit;
3271 --
3272 else
3273 --
3274 l_not_hash_found := false;
3275 --
3276 end if;
3277 --
3278 end loop;
3279 --
3280 end if;
3281 --
3282 end if;
3283 --
3284 l_torrwnum := 0;
3285 for l_insttorrw_num in g_los_lookup(l_index).starttorele_num ..
3286 g_los_lookup(l_index).endtorele_num loop
3287 --
3288 p_inst_set(l_torrwnum) := g_los_instance(l_insttorrw_num);
3289 l_torrwnum := l_torrwnum+1;
3290 --
3291 end loop;
3292 --
3293 p_inst_count := l_torrwnum;
3294 --
3295 exception
3296 --
3297 when no_data_found then
3298 --
3299 p_inst_count := 0;
3300 --
3301 end get_rt_prfl_cache;
3302 /*
3303 --
3304 -- LOS
3305 --
3306 procedure get_rt_prfl_cache
3307 (p_vrbl_rt_prfl_id in number
3308 ,p_effective_date in date
3309 ,p_lf_evt_ocrd_dt in date
3310 ,p_business_group_id in number
3311 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_los_inst_tbl
3312 ,p_inst_count out nocopy number)
3313 is
3314 --
3315 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3316 l_lookup_query long;
3317 l_inst_query long;
3318 l_instcolnm_set ben_cache.instcolnmtype;
3319 --
3320 begin
3321 --
3322 g_los_out.delete;
3323 --
3324 if g_los_lookup.count = 0 then
3325 --
3326 -- Cache not populated yet. So populate it now. First build master and
3327 -- detail queries.
3328 --
3329 l_lookup_query :=
3330 'select vrbl_rt_prfl_id, business_group_id' ||
3331 ' from ben_vrbl_rt_prfl_f vpf' ||
3332 ' where business_group_id = ' || to_char(p_business_group_id) ||
3333 ' and exists (select null' ||
3334 ' from ben_los_rt_f lsr' ||
3335 ' where lsr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3336 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3337 ' between lsr.effective_start_date' ||
3338 ' and lsr.effective_end_date) ' ||
3339 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3340 ' between vpf.effective_start_date and vpf.effective_end_date;';
3341 --
3342 l_inst_query :=
3343 'select lsr.vrbl_rt_prfl_id, lsf.mn_los_num, lsf.mx_los_num,' ||
3344 ' lsr.excld_flag, no_mn_los_num_apls_flag, no_mx_los_num_apls_flag, '||
3345 ' lsf.los_fctr_id' ||
3346 ' from ben_los_fctr lsf, ben_los_rt_f lsr' ||
3347 ' where lsr.business_group_id = ' || to_char(p_business_group_id) ||
3348 ' and lsr.los_fctr_id = lsf.los_fctr_id' ||
3349 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3350 ' between lsr.effective_start_date' ||
3351 ' and lsr.effective_end_date' ||
3352 ' order by lsr.vrbl_rt_prfl_id;';
3353 --
3354 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3355 l_instcolnm_set(0).caccol_name := 'id';
3356 l_instcolnm_set(1).col_name := 'mn_los_num';
3357 l_instcolnm_set(1).caccol_name := 'mn_los_num';
3358 l_instcolnm_set(2).col_name := 'mx_los_num';
3359 l_instcolnm_set(2).caccol_name := 'mx_los_num';
3360 l_instcolnm_set(3).col_name := 'excld_flag';
3361 l_instcolnm_set(3).caccol_name := 'excld_flag';
3362 l_instcolnm_set(4).col_name := 'no_mn_los_num_apls_flag';
3363 l_instcolnm_set(4).caccol_name := 'no_mn_los_num_apls_flag';
3364 l_instcolnm_set(5).col_name := 'no_mx_los_num_apls_flag';
3365 l_instcolnm_set(5).caccol_name := 'no_mx_los_num_apls_flag';
3366 l_instcolnm_set(6).col_name := 'los_fctr_id';
3367 l_instcolnm_set(6).caccol_name := 'los_fctr_id';
3368 --
3369 ben_cache.write_mastDet_Cache
3370 (p_mastercol_name => 'vrbl_rt_prfl_id'
3371 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3372 ,p_lkup_name => 'ben_rt_prfl_cache.g_los_lookup'
3373 ,p_inst_name => 'ben_rt_prfl_cache.g_los_instance'
3374 ,p_lkup_query => l_lookup_query
3375 ,p_inst_query => l_inst_query
3376 ,p_instcolnm_set => l_instcolnm_set
3377 );
3378 --
3379 end if;
3380 --
3381 -- Cache already populated. Get record set.
3382 --
3383 get_cached_data
3384 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3385 ,p_lookup_name => 'ben_rt_prfl_cache.g_los_lookup'
3386 ,p_inst_name => 'ben_rt_prfl_cache.g_los_instance'
3387 ,p_inst_set_type => 'ben_rt_prfl_cache.g_los_inst_tbl'
3388 ,p_out_inst_name => 'ben_rt_prfl_cache.g_los_out'
3389 );
3390 --
3391 p_inst_set := g_los_out;
3392 p_inst_count := g_inst_count;
3393 --
3394 exception
3395 --
3396 when no_data_found then
3397 --
3398 p_inst_count := 0;
3399 hr_utility.set_location('No Length of Service found', 90);
3400 hr_utility.set_location('Leaving : ' || l_proc, 99);
3401 --
3402 end;
3403 */
3404 --
3405 -- CMBN AGE LOS
3406 --
3407 procedure get_rt_prfl_cache
3408 (p_vrbl_rt_prfl_id in number
3409 ,p_effective_date in date
3410 ,p_lf_evt_ocrd_dt in date
3411 ,p_business_group_id in number
3412 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_age_los_inst_tbl
3413 ,p_inst_count out nocopy number)
3414 is
3415 --
3416 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3417 l_lookup_query long;
3418 l_inst_query long;
3419 l_instcolnm_set ben_cache.instcolnmtype;
3420 --
3421 begin
3422 --
3423 g_age_los_out.delete;
3424 --
3425 if g_age_los_lookup.count = 0 then
3426 --
3427 -- Cache not populated yet. So populate it now. First build master and
3428 -- detail queries.
3429 --
3430 l_lookup_query :=
3431 'select vrbl_rt_prfl_id, business_group_id' ||
3432 ' from ben_vrbl_rt_prfl_f vpf' ||
3433 ' where business_group_id = ' || to_char(p_business_group_id) ||
3434 ' and exists (select null' ||
3435 ' from ben_cmbn_age_los_rt_f cmr' ||
3436 ' where cmr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3437 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3438 ' between cmr.effective_start_date' ||
3439 ' and cmr.effective_end_date) ' ||
3440 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3441 ' between vpf.effective_start_date and vpf.effective_end_date;';
3442 --
3443 l_inst_query :=
3444 'select cmr.vrbl_rt_prfl_id, cla.cmbnd_min_val, cla.cmbnd_max_val,' ||
3445 ' cmr.excld_flag, cla.cmbn_age_los_fctr_id' ||
3446 ' from ben_cmbn_age_los_fctr cla, ben_cmbn_age_los_rt_f cmr' ||
3447 ' where cmr.business_group_id = ' || to_char(p_business_group_id) ||
3448 ' and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id' ||
3449 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3450 ' between cmr.effective_start_date' ||
3451 ' and cmr.effective_end_date' ||
3452 ' order by cmr.vrbl_rt_prfl_id;';
3453 --
3454 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3455 l_instcolnm_set(0).caccol_name := 'id';
3456 l_instcolnm_set(1).col_name := 'cmbnd_min_val';
3457 l_instcolnm_set(1).caccol_name := 'cmbnd_min_val';
3458 l_instcolnm_set(2).col_name := 'cmbnd_max_val';
3459 l_instcolnm_set(2).caccol_name := 'cmbnd_max_val';
3460 l_instcolnm_set(3).col_name := 'excld_flag';
3461 l_instcolnm_set(3).caccol_name := 'excld_flag';
3462 l_instcolnm_set(4).col_name := 'cmbn_age_los_fctr_id';
3463 l_instcolnm_set(4).caccol_name := 'cmbn_age_los_fctr_id';
3464 --
3465 ben_cache.write_mastDet_Cache
3466 (p_mastercol_name => 'vrbl_rt_prfl_id'
3467 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3468 ,p_lkup_name => 'ben_rt_prfl_cache.g_age_los_lookup'
3469 ,p_inst_name => 'ben_rt_prfl_cache.g_age_los_instance'
3470 ,p_lkup_query => l_lookup_query
3471 ,p_inst_query => l_inst_query
3472 ,p_instcolnm_set => l_instcolnm_set
3473 );
3474 --
3475 end if;
3476 --
3477 -- Cache already populated. Get record set.
3478 --
3479 get_cached_data
3480 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3481 ,p_lookup_name => 'ben_rt_prfl_cache.g_age_los_lookup'
3482 ,p_inst_name => 'ben_rt_prfl_cache.g_age_los_instance'
3483 ,p_inst_set_type => 'ben_rt_prfl_cache.g_age_los_inst_tbl'
3484 ,p_out_inst_name => 'ben_rt_prfl_cache.g_age_los_out'
3485 );
3486 --
3487 p_inst_set := g_age_los_out;
3488 p_inst_count := g_inst_count;
3489 --
3490 exception
3491 --
3492 when no_data_found then
3493 --
3494 p_inst_count := 0;
3495 hr_utility.set_location('No Cmbn Age Los found', 90);
3496 hr_utility.set_location('Leaving : ' || l_proc, 99);
3497 --
3498 end;
3499 ------------------------------------------------------------------------
3500 -- TOTAL PARTICIPANTS
3501 ------------------------------------------------------------------------
3502 procedure get_rt_prfl_cache
3503 (p_vrbl_rt_prfl_id in number
3504 ,p_effective_date in date
3505 ,p_lf_evt_ocrd_dt in date
3506 ,p_business_group_id in number
3507 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ttl_prtt_inst_tbl
3508 ,p_inst_count out nocopy number) is
3509 --
3510 l_proc varchar2(72) := 'get_rt_prfl_cache';
3511 --
3512 l_instcolnm_set ben_cache.InstColNmType;
3513 --
3514 l_torrwnum pls_integer;
3515 l_insttorrw_num pls_integer;
3516 l_index pls_integer;
3517 l_instcolnm_num pls_integer;
3518 l_mastertab_name varchar2(100);
3519 l_masterpkcol_name varchar2(100);
3520 l_lkup_name varchar2(100);
3521 l_inst_name varchar2(100);
3522 --
3523 l_not_hash_found boolean;
3524 --
3525 begin
3526 --
3527 -- Populate the global cache
3528 --
3529 if g_ttl_prtt_lookup.count = 0 then
3530 --
3531 -- Build the cache
3532 --
3533 l_mastertab_name := 'ben_vrbl_rt_prfl_f';
3534 l_masterpkcol_name := 'vrbl_rt_prfl_id';
3535 l_lkup_name := 'ben_rt_prfl_cache.g_ttl_prtt_lookup';
3536 l_inst_name := 'ben_rt_prfl_cache.g_ttl_prtt_instance';
3537 --
3538 l_instcolnm_num := 0;
3539 --
3540 l_instcolnm_set(l_instcolnm_num).col_name := l_masterpkcol_name;
3541 l_instcolnm_set(l_instcolnm_num).caccol_name := 'vrbl_rt_prfl_id';
3542 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3543 l_instcolnm_set(l_instcolnm_num).col_type := 'MASTER';
3544 l_instcolnm_num := l_instcolnm_num+1;
3545 --
3546 l_instcolnm_set(l_instcolnm_num).col_name := 'mn_prtt_num';
3547 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mn_prtt_num';
3548 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3549 l_instcolnm_num := l_instcolnm_num+1;
3550 --
3551 l_instcolnm_set(l_instcolnm_num).col_name := 'mx_prtt_num';
3552 l_instcolnm_set(l_instcolnm_num).caccol_name := 'mx_prtt_num';
3553 l_instcolnm_set(l_instcolnm_num).col_alias := 'table1';
3554 l_instcolnm_num := l_instcolnm_num+1;
3555 --
3556 ben_cache.Write_BGP_Cache
3557 (p_mastertab_name => l_mastertab_name
3558 ,p_masterpkcol_name => l_masterpkcol_name
3559 ,p_table1_name => 'ben_ttl_prtt_rt_f'
3560 ,p_business_group_id => p_business_group_id
3561 ,p_effective_date => get_eff_date(p_lf_evt_ocrd_dt, p_effective_date)
3562 ,p_lkup_name => l_lkup_name
3563 ,p_inst_name => l_inst_name
3564 ,p_instcolnm_set => l_instcolnm_set
3565 );
3566 --
3567 end if;
3568 --
3569 -- Get the instance details
3570 --
3571 l_index := ben_hash_utility.get_hashed_index(p_id => p_vrbl_rt_prfl_id);
3572 --
3573 -- Check if hashed value is already allocated
3574 --
3575 if g_ttl_prtt_lookup.exists(l_index) then
3576 --
3577 -- If it does exist make sure its the right one
3578 --
3579 if g_ttl_prtt_lookup(l_index).id <> p_vrbl_rt_prfl_id then
3580 --
3581 l_not_hash_found := false;
3582 --
3583 -- Loop until un-allocated has value is derived
3584 --
3585 while not l_not_hash_found loop
3586 --
3587 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
3588 --
3589 -- Check if the hash index exists, if not we can use it
3590 --
3591 if not g_ttl_prtt_lookup.exists(l_index) then
3592 --
3593 -- Lets store the hash value in the index
3594 --
3595 l_not_hash_found := true;
3596 exit;
3597 --
3598 else
3599 --
3600 l_not_hash_found := false;
3601 --
3602 end if;
3603 --
3604 end loop;
3605 --
3606 end if;
3607 --
3608 end if;
3609 --
3610 l_torrwnum := 0;
3611 for l_insttorrw_num in g_ttl_prtt_lookup(l_index).starttorele_num ..
3612 g_ttl_prtt_lookup(l_index).endtorele_num loop
3613 --
3614 p_inst_set(l_torrwnum) := g_ttl_prtt_instance(l_insttorrw_num);
3615 l_torrwnum := l_torrwnum+1;
3616 --
3617 end loop;
3618 --
3619 p_inst_count := l_torrwnum;
3620 --
3621 exception
3622 --
3623 when no_data_found then
3624 --
3625 p_inst_count := 0;
3626 --
3627 end get_rt_prfl_cache;
3628 ------------------------------------------------------------------------
3629 -- TOTAL COVERAGE
3630 ------------------------------------------------------------------------
3631 procedure get_rt_prfl_cache
3632 (p_vrbl_rt_prfl_id in number
3633 ,p_effective_date in date
3634 ,p_lf_evt_ocrd_dt in date
3635 ,p_business_group_id in number
3636 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_ttl_cvg_inst_tbl
3637 ,p_inst_count out nocopy number)
3638 is
3639 --
3640 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache, ttl cvg';
3641 l_lookup_query long;
3642 l_inst_query long;
3643 l_instcolnm_set ben_cache.instcolnmtype;
3644 --
3645 begin
3646 g_ttl_cvg_out.delete;
3647 --
3648 if g_ttl_cvg_lookup.count = 0 then
3649
3650 --
3651 -- Cache not populated yet. So populate it now.
3652 --
3653 l_lookup_query :=
3654 'select vrbl_rt_prfl_id, business_group_id' ||
3655 ' from ben_vrbl_rt_prfl_f vpf' ||
3656 ' where business_group_id = ' || to_char(p_business_group_id) ||
3657 ' and exists (select null' ||
3658 ' from ben_ttl_cvg_vol_rt_f bgr' ||
3659 ' where bgr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3660 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3661 ' between bgr.effective_start_date' ||
3662 ' and bgr.effective_end_date) ' ||
3663 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3664 ' between vpf.effective_start_date and vpf.effective_end_date;';
3665 --
3666 l_inst_query :=
3667 'select bgr.vrbl_rt_prfl_id, bgr.mn_cvg_vol_amt, bgr.mx_cvg_vol_amt' ||
3668 ' from ben_ttl_cvg_vol_rt_f bgr' ||
3669 ' where bgr.business_group_id = ' || to_char(p_business_group_id) ||
3670 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3671 ' between bgr.effective_start_date' ||
3672 ' and bgr.effective_end_date' ||
3673 ' order by bgr.vrbl_rt_prfl_id;';
3674 --
3675 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3676 l_instcolnm_set(0).caccol_name := 'id';
3677 l_instcolnm_set(1).col_name := 'mn_cvg_vol_amt';
3678 l_instcolnm_set(1).caccol_name := 'mn_cvg_vol_amt';
3679 l_instcolnm_set(2).col_name := 'mx_cvg_vol_amt';
3680 l_instcolnm_set(2).caccol_name := 'mx_cvg_vol_amt';
3681
3682 ben_cache.write_mastDet_Cache
3683 (p_mastercol_name => 'vrbl_rt_prfl_id'
3684 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3685 ,p_lkup_name => 'ben_rt_prfl_cache.g_ttl_cvg_lookup'
3686 ,p_inst_name => 'ben_rt_prfl_cache.g_ttl_cvg_instance'
3687 ,p_lkup_query => l_lookup_query
3688 ,p_inst_query => l_inst_query
3689 ,p_instcolnm_set => l_instcolnm_set
3690 );
3691 --
3692 end if;
3693 --
3694 -- Cache already populated. Get record set.
3695 --
3696 get_cached_data
3697 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3698 ,p_lookup_name => 'ben_rt_prfl_cache.g_ttl_cvg_lookup'
3699 ,p_inst_name => 'ben_rt_prfl_cache.g_ttl_cvg_instance'
3700 ,p_inst_set_type => 'ben_rt_prfl_cache.g_ttl_cvg_inst_tbl'
3701 ,p_out_inst_name => 'ben_rt_prfl_cache.g_ttl_cvg_out'
3702 );
3703 --
3704 p_inst_set := g_ttl_cvg_out;
3705 p_inst_count := g_inst_count;
3706
3707 --
3708 exception
3709 --
3710 when no_data_found then
3711 --
3712 p_inst_count := 0;
3713 hr_utility.set_location('No data found - Coverage', 10);
3714 hr_utility.set_location('Leaving : ' || l_proc, 99);
3715 --
3716 end;
3717
3718
3719 ------------------------------------------------------------------------
3720 -- JOB
3721 ------------------------------------------------------------------------
3722 procedure get_rt_prfl_cache
3723 (p_vrbl_rt_prfl_id in number
3724 ,p_effective_date in date
3725 ,p_lf_evt_ocrd_dt in date
3726 ,p_business_group_id in number
3727 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_job_inst_tbl
3728 ,p_inst_count out nocopy number)
3729 is
3730 --
3731 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3732 l_lookup_query long;
3733 l_inst_query long;
3734 l_instcolnm_set ben_cache.instcolnmtype;
3735 --
3736 begin
3737 --
3738 g_job_out.delete;
3739 --
3740 if g_job_lookup.count = 0 then
3741 --
3742 -- Cache not populated yet. So populate it now.
3743 --
3744 l_lookup_query :=
3745 'select vrbl_rt_prfl_id, business_group_id' ||
3746 ' from ben_vrbl_rt_prfl_f vpf' ||
3747 ' where business_group_id = ' || to_char(p_business_group_id) ||
3748 ' and exists (select null' ||
3749 ' from ben_job_rt_f jrt' ||
3750 ' where jrt.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3751 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3752 ' between jrt.effective_start_date' ||
3753 ' and jrt.effective_end_date) ' ||
3754 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3755 ' between vpf.effective_start_date and vpf.effective_end_date;';
3756 --
3757 l_inst_query :=
3758 'select jrt.vrbl_rt_prfl_id, jrt.job_id, jrt.excld_flag' ||
3759 ' from ben_job_rt_f jrt' ||
3760 ' where jrt.business_group_id = ' || to_char(p_business_group_id) ||
3761 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3762 ' between jrt.effective_start_date' ||
3763 ' and jrt.effective_end_date' ||
3764 ' order by jrt.vrbl_rt_prfl_id;';
3765 --
3766 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3767 l_instcolnm_set(0).caccol_name := 'id';
3768 l_instcolnm_set(1).col_name := 'job_id';
3769 l_instcolnm_set(1).caccol_name := 'job_id';
3770 l_instcolnm_set(2).col_name := 'excld_flag';
3771 l_instcolnm_set(2).caccol_name := 'excld_flag';
3772 --
3773 ben_cache.write_mastDet_Cache
3774 (p_mastercol_name => 'vrbl_rt_prfl_id'
3775 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3776 ,p_lkup_name => 'ben_rt_prfl_cache.g_job_lookup'
3777 ,p_inst_name => 'ben_rt_prfl_cache.g_job_instance'
3778 ,p_lkup_query => l_lookup_query
3779 ,p_inst_query => l_inst_query
3780 ,p_instcolnm_set => l_instcolnm_set
3781 );
3782 --
3783 end if;
3784 --
3785 -- Cache already populated. Get record set.
3786 --
3787 get_cached_data
3788 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3789 ,p_lookup_name => 'ben_rt_prfl_cache.g_job_lookup'
3790 ,p_inst_name => 'ben_rt_prfl_cache.g_job_instance'
3791 ,p_inst_set_type => 'ben_rt_prfl_cache.g_job_inst_tbl'
3792 ,p_out_inst_name => 'ben_rt_prfl_cache.g_job_out'
3793 );
3794 --
3795 p_inst_set := g_job_out;
3796 p_inst_count := g_inst_count;
3797 --
3798 exception
3799 --
3800 when no_data_found then
3801 --
3802 p_inst_count := 0;
3803 hr_utility.set_location('No Job found', 90);
3804 hr_utility.set_location('Leaving : ' || l_proc, 99);
3805 --
3806 end;
3807
3808 ------------------------------------------------------------------------
3809 -- Opted for Medicare
3810 ------------------------------------------------------------------------
3811 procedure get_rt_prfl_cache
3812 (p_vrbl_rt_prfl_id in number
3813 ,p_effective_date in date
3814 ,p_lf_evt_ocrd_dt in date
3815 ,p_business_group_id in number
3816 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_optd_mdcr_inst_tbl
3817 ,p_inst_count out nocopy number)
3818 is
3819 --
3820 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3821 l_lookup_query long;
3822 l_inst_query long;
3823 l_instcolnm_set ben_cache.instcolnmtype;
3824 --
3825 begin
3826 --
3827 g_optd_mdcr_out.delete;
3828 --
3829 if g_optd_mdcr_lookup.count = 0 then
3830 --
3831 -- Cache not populated yet. So populate it now.
3832 --
3833 l_lookup_query :=
3834 'select vrbl_rt_prfl_id, business_group_id' ||
3835 ' from ben_vrbl_rt_prfl_f vpf' ||
3836 ' where business_group_id = ' || to_char(p_business_group_id) ||
3837 ' and exists (select null' ||
3838 ' from ben_optd_mdcr_rt_f omr' ||
3839 ' where omr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3840 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3841 ' between omr.effective_start_date' ||
3842 ' and omr.effective_end_date) ' ||
3843 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3844 ' between vpf.effective_start_date and vpf.effective_end_date;';
3845 --
3846 l_inst_query :=
3847 'select omr.vrbl_rt_prfl_id, omr.optd_mdcr_flag' ||
3848 ' from ben_optd_mdcr_rt_f omr' ||
3849 ' where omr.business_group_id = ' || to_char(p_business_group_id) ||
3850 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3851 ' between omr.effective_start_date' ||
3852 ' and omr.effective_end_date' ||
3853 ' order by omr.vrbl_rt_prfl_id;';
3854 --
3855 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3856 l_instcolnm_set(0).caccol_name := 'id';
3857 l_instcolnm_set(1).col_name := 'optd_mdcr_flag';
3858 l_instcolnm_set(1).caccol_name := 'optd_mdcr_flag';
3859 --
3860 ben_cache.write_mastDet_Cache
3861 (p_mastercol_name => 'vrbl_rt_prfl_id'
3862 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3863 ,p_lkup_name => 'ben_rt_prfl_cache.g_optd_mdcr_lookup'
3864 ,p_inst_name => 'ben_rt_prfl_cache.g_optd_mdcr_instance'
3865 ,p_lkup_query => l_lookup_query
3866 ,p_inst_query => l_inst_query
3867 ,p_instcolnm_set => l_instcolnm_set
3868 );
3869 --
3870 end if;
3871 --
3872 -- Cache already populated. Get record set.
3873 --
3874 get_cached_data
3875 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3876 ,p_lookup_name => 'ben_rt_prfl_cache.g_optd_mdcr_lookup'
3877 ,p_inst_name => 'ben_rt_prfl_cache.g_optd_mdcr_instance'
3878 ,p_inst_set_type => 'ben_rt_prfl_cache.g_optd_mdcr_inst_tbl'
3879 ,p_out_inst_name => 'ben_rt_prfl_cache.g_optd_mdcr_out'
3880 );
3881 --
3882 p_inst_set := g_optd_mdcr_out;
3883 p_inst_count := g_inst_count;
3884 --
3885 exception
3886 --
3887 when no_data_found then
3888 --
3889 p_inst_count := 0;
3890 hr_utility.set_location('No Opted for Medicare found', 90);
3891 hr_utility.set_location('Leaving : ' || l_proc, 99);
3892 --
3893 end;
3894 ------------------------------------------------------------------------
3895 -- Leaving Reason
3896 ------------------------------------------------------------------------
3897 procedure get_rt_prfl_cache
3898 (p_vrbl_rt_prfl_id in number
3899 ,p_effective_date in date
3900 ,p_lf_evt_ocrd_dt in date
3901 ,p_business_group_id in number
3902 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_lvg_rsn_inst_tbl
3903 ,p_inst_count out nocopy number)
3904 is
3905 --
3906 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3907 l_lookup_query long;
3908 l_inst_query long;
3909 l_instcolnm_set ben_cache.instcolnmtype;
3910 --
3911 begin
3912 --
3913 g_lvg_rsn_out.delete;
3914 --
3915 if g_lvg_rsn_lookup.count = 0 then
3916 --
3917 -- Cache not populated yet. So populate it now.
3918 --
3919 l_lookup_query :=
3920 'select vrbl_rt_prfl_id, business_group_id' ||
3921 ' from ben_vrbl_rt_prfl_f vpf' ||
3922 ' where business_group_id = ' || to_char(p_business_group_id) ||
3923 ' and exists (select null' ||
3924 ' from ben_lvg_rsn_rt_f lrr' ||
3925 ' where lrr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
3926 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
3927 ' between lrr.effective_start_date' ||
3928 ' and lrr.effective_end_date) ' ||
3929 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3930 ' between vpf.effective_start_date and vpf.effective_end_date;';
3931 --
3932 l_inst_query :=
3933 'select lrr.vrbl_rt_prfl_id, lrr.lvg_rsn_cd, lrr.excld_flag' ||
3934 ' from ben_lvg_rsn_rt_f lrr' ||
3935 ' where lrr.business_group_id = ' || to_char(p_business_group_id) ||
3936 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
3937 ' between lrr.effective_start_date' ||
3938 ' and lrr.effective_end_date' ||
3939 ' order by lrr.vrbl_rt_prfl_id;';
3940 --
3941 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
3942 l_instcolnm_set(0).caccol_name := 'id';
3943 l_instcolnm_set(1).col_name := 'lvg_rsn_cd';
3944 l_instcolnm_set(1).caccol_name := 'lvg_rsn_cd';
3945 l_instcolnm_set(2).col_name := 'excld_flag';
3946 l_instcolnm_set(2).caccol_name := 'excld_flag';
3947 --
3948 ben_cache.write_mastDet_Cache
3949 (p_mastercol_name => 'vrbl_rt_prfl_id'
3950 ,p_detailcol_name => 'vrbl_rt_prfl_id'
3951 ,p_lkup_name => 'ben_rt_prfl_cache.g_lvg_rsn_lookup'
3952 ,p_inst_name => 'ben_rt_prfl_cache.g_lvg_rsn_instance'
3953 ,p_lkup_query => l_lookup_query
3954 ,p_inst_query => l_inst_query
3955 ,p_instcolnm_set => l_instcolnm_set
3956 );
3957 --
3958 end if;
3959 --
3960 -- Cache already populated. Get record set.
3961 --
3962 get_cached_data
3963 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
3964 ,p_lookup_name => 'ben_rt_prfl_cache.g_lvg_rsn_lookup'
3965 ,p_inst_name => 'ben_rt_prfl_cache.g_lvg_rsn_instance'
3966 ,p_inst_set_type => 'ben_rt_prfl_cache.g_lvg_rsn_inst_tbl'
3967 ,p_out_inst_name => 'ben_rt_prfl_cache.g_lvg_rsn_out'
3968 );
3969 --
3970 p_inst_set := g_lvg_rsn_out;
3971 p_inst_count := g_inst_count;
3972 --
3973 exception
3974 --
3975 when no_data_found then
3976 --
3977 p_inst_count := 0;
3978 hr_utility.set_location('No Leaving Reason found', 90);
3979 hr_utility.set_location('Leaving : ' || l_proc, 99);
3980 --
3981 end;
3982
3983 ------------------------------------------------------------------------
3984 -- Cobra Qualified Beneficiary
3985 ------------------------------------------------------------------------
3986 procedure get_rt_prfl_cache
3987 (p_vrbl_rt_prfl_id in number
3988 ,p_effective_date in date
3989 ,p_lf_evt_ocrd_dt in date
3990 ,p_business_group_id in number
3991 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_cbr_qual_bnf_inst_tbl
3992 ,p_inst_count out nocopy number)
3993 is
3994 --
3995 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
3996 l_lookup_query long;
3997 l_inst_query long;
3998 l_instcolnm_set ben_cache.instcolnmtype;
3999 --
4000 begin
4001 --
4002 g_cbr_qual_bnf_out.delete;
4003 --
4004 if g_cbr_qual_bnf_lookup.count = 0 then
4005 --
4006 -- Cache not populated yet. So populate it now.
4007 --
4008 l_lookup_query :=
4009 'select vrbl_rt_prfl_id, business_group_id' ||
4010 ' from ben_vrbl_rt_prfl_f vpf' ||
4011 ' where business_group_id = ' || to_char(p_business_group_id) ||
4012 ' and exists (select null' ||
4013 ' from ben_cbr_quald_bnf_rt_f cqr' ||
4014 ' where cqr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4015 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4016 ' between cqr.effective_start_date' ||
4017 ' and cqr.effective_end_date) ' ||
4018 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4019 ' between vpf.effective_start_date and vpf.effective_end_date;';
4020 --
4021 l_inst_query :=
4022 'select cqr.vrbl_rt_prfl_id, cqr.ptip_id, cqr.pgm_id,' ||
4023 ' cqr.quald_bnf_flag ' ||
4024 ' from ben_cbr_quald_bnf_rt_f cqr' ||
4025 ' where cqr.business_group_id = ' || to_char(p_business_group_id) ||
4026 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4027 ' between cqr.effective_start_date' ||
4028 ' and cqr.effective_end_date' ||
4029 ' order by cqr.vrbl_rt_prfl_id;';
4030 --
4031 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4032 l_instcolnm_set(0).caccol_name := 'id';
4033 l_instcolnm_set(1).col_name := 'ptip_id';
4034 l_instcolnm_set(1).caccol_name := 'ptip_id';
4035 l_instcolnm_set(2).col_name := 'pgm_id';
4036 l_instcolnm_set(2).caccol_name := 'pgm_id';
4037 l_instcolnm_set(3).col_name := 'quald_bnf_flag';
4038 l_instcolnm_set(3).caccol_name := 'quald_bnf_flag';
4039
4040 --
4041 ben_cache.write_mastDet_Cache
4042 (p_mastercol_name => 'vrbl_rt_prfl_id'
4043 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4044 ,p_lkup_name => 'ben_rt_prfl_cache.g_cbr_qual_bnf_lookup'
4045 ,p_inst_name => 'ben_rt_prfl_cache.g_cbr_qual_bnf_instance'
4046 ,p_lkup_query => l_lookup_query
4047 ,p_inst_query => l_inst_query
4048 ,p_instcolnm_set => l_instcolnm_set
4049 );
4050 --
4051 end if;
4052 --
4053 -- Cache already populated. Get record set.
4054 --
4055 get_cached_data
4056 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4057 ,p_lookup_name => 'ben_rt_prfl_cache.g_cbr_qual_bnf_lookup'
4058 ,p_inst_name => 'ben_rt_prfl_cache.g_cbr_qual_bnf_instance'
4059 ,p_inst_set_type => 'ben_rt_prfl_cache.g_cbr_qual_bnf_inst_tbl'
4060 ,p_out_inst_name => 'ben_rt_prfl_cache.g_cbr_qual_bnf_out'
4061 );
4062 --
4063 p_inst_set := g_cbr_qual_bnf_out;
4064 p_inst_count := g_inst_count;
4065 --
4066 exception
4067 --
4068 when no_data_found then
4069 --
4070 p_inst_count := 0;
4071 hr_utility.set_location('No Cobra Qualified Benificiary found', 90);
4072 hr_utility.set_location('Leaving : ' || l_proc, 99);
4073 --
4074 end;
4075
4076 ------------------------------------------------------------------------
4077 -- Continuing Participation Profile
4078 ------------------------------------------------------------------------
4079 procedure get_rt_prfl_cache
4080 (p_vrbl_rt_prfl_id in number
4081 ,p_effective_date in date
4082 ,p_lf_evt_ocrd_dt in date
4083 ,p_business_group_id in number
4084 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_cntng_prtn_prfl_inst_tbl
4085 ,p_inst_count out nocopy number)
4086 is
4087 --
4088 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4089 l_lookup_query long;
4090 l_inst_query long;
4091 l_instcolnm_set ben_cache.instcolnmtype;
4092 --
4093 begin
4094 --
4095 g_cntng_prtn_prfl_out.delete;
4096 --
4097 if g_cntng_prtn_prfl_lookup.count = 0 then
4098 --
4099 -- Cache not populated yet. So populate it now.
4100 --
4101 l_lookup_query :=
4102 'select vrbl_rt_prfl_id, business_group_id' ||
4103 ' from ben_vrbl_rt_prfl_f vpf' ||
4104 ' where business_group_id = ' || to_char(p_business_group_id) ||
4105 ' and exists (select null' ||
4106 ' from ben_cntng_prtn_prfl_rt_f cpp' ||
4107 ' where cpp.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4108 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4109 ' between cpp.effective_start_date' ||
4110 ' and cpp.effective_end_date ) ' ||
4111 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date ) ||
4112 ' between vpf.effective_start_date and vpf.effective_end_date;';
4113
4114 l_inst_query :=
4115 'select cpp.vrbl_rt_prfl_id, cpp.pymt_must_be_rcvd_uom, ' ||
4116 ' cpp.pymt_must_be_rcvd_num , cpp.pymt_must_be_rcvd_rl ' ||
4117 ' from ben_cntng_prtn_prfl_rt_f cpp' ||
4118 ' where cpp.business_group_id = ' || to_char(p_business_group_id) ||
4119 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4120 ' between cpp.effective_start_date' ||
4121 ' and cpp.effective_end_date' ||
4122 ' order by cpp.vrbl_rt_prfl_id;';
4123
4124
4125 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4126 l_instcolnm_set(0).caccol_name := 'id';
4127 l_instcolnm_set(1).col_name := 'pymt_must_be_rcvd_uom';
4128 l_instcolnm_set(1).caccol_name := 'pymt_must_be_rcvd_uom';
4129 l_instcolnm_set(2).col_name := 'pymt_must_be_rcvd_num';
4130 l_instcolnm_set(2).caccol_name := 'pymt_must_be_rcvd_num';
4131 l_instcolnm_set(3).col_name := 'pymt_must_be_rcvd_rl';
4132 l_instcolnm_set(4).caccol_name := 'pymt_must_be_rcvd_rl';
4133 --
4134 ben_cache.write_mastDet_Cache
4135 (p_mastercol_name => 'vrbl_rt_prfl_id'
4136 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4137 ,p_lkup_name => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_lookup'
4138 ,p_inst_name => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_instance'
4139 ,p_lkup_query => l_lookup_query
4140 ,p_inst_query => l_inst_query
4141 ,p_instcolnm_set => l_instcolnm_set
4142 );
4143 --
4144 end if;
4145 --
4146 -- Cache already populated. Get record set.
4147 --
4148 get_cached_data
4149 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4150 ,p_lookup_name => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_lookup'
4151 ,p_inst_name => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_instance'
4152 ,p_inst_set_type => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_inst_tbl'
4153 ,p_out_inst_name => 'ben_rt_prfl_cache.g_cntng_prtn_prfl_out'
4154 );
4155 --
4156 p_inst_set := g_cntng_prtn_prfl_out;
4157 p_inst_count := g_inst_count;
4158 --
4159 exception
4160 --
4161 when no_data_found then
4162 --
4163 p_inst_count := 0;
4164 hr_utility.set_location('No continuing participant profile found', 90);
4165 hr_utility.set_location('Leaving : ' || l_proc, 99);
4166 --
4167 end;
4168
4169
4170 ------------------------------------------------------------------------
4171 -- Position
4172 ------------------------------------------------------------------------
4173 procedure get_rt_prfl_cache
4174 (p_vrbl_rt_prfl_id in number
4175 ,p_effective_date in date
4176 ,p_lf_evt_ocrd_dt in date
4177 ,p_business_group_id in number
4178 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_pstn_inst_tbl
4179 ,p_inst_count out nocopy number)
4180 is
4181 --
4182 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4183 l_lookup_query long;
4184 l_inst_query long;
4185 l_instcolnm_set ben_cache.instcolnmtype;
4186 --
4187 begin
4188 --
4189 g_pstn_out.delete;
4190 --
4191 if g_pstn_lookup.count = 0 then
4192 --
4193 -- Cache not populated yet. So populate it now.
4194 --
4195 l_lookup_query :=
4196 'select vrbl_rt_prfl_id, business_group_id' ||
4197 ' from ben_vrbl_rt_prfl_f vpf' ||
4198 ' where business_group_id = ' || to_char(p_business_group_id) ||
4199 ' and exists (select null' ||
4200 ' from ben_pstn_rt_f psr' ||
4201 ' where psr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4202 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4203 ' between psr.effective_start_date' ||
4204 ' and psr.effective_end_date) ' ||
4205 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4206 ' between vpf.effective_start_date and vpf.effective_end_date;';
4207 --
4208 l_inst_query :=
4209 'select psr.vrbl_rt_prfl_id, psr.position_id, psr.excld_flag' ||
4210 ' from ben_pstn_rt_f psr' ||
4211 ' where psr.business_group_id = ' || to_char(p_business_group_id) ||
4212 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4213 ' between psr.effective_start_date' ||
4214 ' and psr.effective_end_date' ||
4215 ' order by psr.vrbl_rt_prfl_id;';
4216 --
4217 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4218 l_instcolnm_set(0).caccol_name := 'id';
4219 l_instcolnm_set(1).col_name := 'position_id';
4220 l_instcolnm_set(1).caccol_name := 'position_id';
4221 l_instcolnm_set(2).col_name := 'excld_flag';
4222 l_instcolnm_set(2).caccol_name := 'excld_flag';
4223 --
4224 ben_cache.write_mastDet_Cache
4225 (p_mastercol_name => 'vrbl_rt_prfl_id'
4226 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4227 ,p_lkup_name => 'ben_rt_prfl_cache.g_pstn_lookup'
4228 ,p_inst_name => 'ben_rt_prfl_cache.g_pstn_instance'
4229 ,p_lkup_query => l_lookup_query
4230 ,p_inst_query => l_inst_query
4231 ,p_instcolnm_set => l_instcolnm_set
4232 );
4233 --
4234 end if;
4235 --
4236 -- Cache already populated. Get record set.
4237 --
4238 get_cached_data
4239 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4240 ,p_lookup_name => 'ben_rt_prfl_cache.g_pstn_lookup'
4241 ,p_inst_name => 'ben_rt_prfl_cache.g_pstn_instance'
4242 ,p_inst_set_type => 'ben_rt_prfl_cache.g_pstn_inst_tbl'
4243 ,p_out_inst_name => 'ben_rt_prfl_cache.g_pstn_out'
4244 );
4245 --
4246 p_inst_set := g_pstn_out;
4247 p_inst_count := g_inst_count;
4248 --
4249 exception
4250 --
4251 when no_data_found then
4252 --
4253 p_inst_count := 0;
4254 hr_utility.set_location('No position found', 90);
4255 hr_utility.set_location('Leaving : ' || l_proc, 99);
4256 --
4257 end;
4258
4259
4260 ------------------------------------------------------------------------
4261 -- Competency
4262 ------------------------------------------------------------------------
4263 procedure get_rt_prfl_cache
4264 (p_vrbl_rt_prfl_id in number
4265 ,p_effective_date in date
4266 ,p_lf_evt_ocrd_dt in date
4267 ,p_business_group_id in number
4268 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_comptncy_inst_tbl
4269 ,p_inst_count out nocopy number)
4270 is
4271 --
4272 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4273 l_lookup_query long;
4274 l_inst_query long;
4275 l_instcolnm_set ben_cache.instcolnmtype;
4276 --
4277 begin
4278 --
4279 g_comptncy_out.delete;
4280 --
4281 if g_comptncy_lookup.count = 0 then
4282 --
4283 -- Cache not populated yet. So populate it now.
4284 --
4285 l_lookup_query :=
4286 'select vrbl_rt_prfl_id, business_group_id' ||
4287 ' from ben_vrbl_rt_prfl_f vpf' ||
4288 ' where business_group_id = ' || to_char(p_business_group_id) ||
4289 ' and exists (select null' ||
4290 ' from ben_comptncy_rt_f cty' ||
4291 ' where cty.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4292 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4293 ' between cty.effective_start_date' ||
4294 ' and cty.effective_end_date) ' ||
4295 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4296 ' between vpf.effective_start_date and vpf.effective_end_date;';
4297 --
4298 l_inst_query :=
4299 'select cty.vrbl_rt_prfl_id, cty.competence_id,' ||
4300 ' cty.rating_level_id, cty.excld_flag' ||
4301 ' from ben_comptncy_rt_f cty' ||
4302 ' where cty.business_group_id = ' || to_char(p_business_group_id) ||
4303 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4304 ' between cty.effective_start_date' ||
4305 ' and cty.effective_end_date' ||
4306 ' order by cty.vrbl_rt_prfl_id;';
4307 --
4308 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4309 l_instcolnm_set(0).caccol_name := 'id';
4310 l_instcolnm_set(1).col_name := 'competence_id';
4311 l_instcolnm_set(1).caccol_name := 'competence_id';
4312 l_instcolnm_set(2).col_name := 'rating_level_id';
4313 l_instcolnm_set(2).caccol_name := 'rating_level_id';
4314 l_instcolnm_set(3).col_name := 'excld_flag';
4315 l_instcolnm_set(3).caccol_name := 'excld_flag';
4316 --
4317 ben_cache.write_mastDet_Cache
4318 (p_mastercol_name => 'vrbl_rt_prfl_id'
4319 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4320 ,p_lkup_name => 'ben_rt_prfl_cache.g_comptncy_lookup'
4321 ,p_inst_name => 'ben_rt_prfl_cache.g_comptncy_instance'
4322 ,p_lkup_query => l_lookup_query
4323 ,p_inst_query => l_inst_query
4324 ,p_instcolnm_set => l_instcolnm_set
4325 );
4326 --
4327 end if;
4328 --
4329 -- Cache already populated. Get record set.
4330 --
4331 get_cached_data
4332 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4333 ,p_lookup_name => 'ben_rt_prfl_cache.g_comptncy_lookup'
4334 ,p_inst_name => 'ben_rt_prfl_cache.g_comptncy_instance'
4335 ,p_inst_set_type => 'ben_rt_prfl_cache.g_comptncy_inst_tbl'
4336 ,p_out_inst_name => 'ben_rt_prfl_cache.g_comptncy_out'
4337 );
4338 --
4339 p_inst_set := g_comptncy_out;
4340 p_inst_count := g_inst_count;
4341 --
4342 exception
4343 --
4344 when no_data_found then
4345 --
4346 p_inst_count := 0;
4347 hr_utility.set_location('No compentency found', 90);
4348 hr_utility.set_location('Leaving : ' || l_proc, 99);
4349 --
4350 end;
4351
4352
4353 ------------------------------------------------------------------------
4354 -- Qualification Title
4355 ------------------------------------------------------------------------
4356 procedure get_rt_prfl_cache
4357 (p_vrbl_rt_prfl_id in number
4358 ,p_effective_date in date
4359 ,p_lf_evt_ocrd_dt in date
4360 ,p_business_group_id in number
4361 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_qual_titl_inst_tbl
4362 ,p_inst_count out nocopy number)
4363 is
4364 --
4365 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4366 l_lookup_query long;
4367 l_inst_query long;
4368 l_instcolnm_set ben_cache.instcolnmtype;
4369 --
4370 begin
4371 --
4372 g_qual_titl_out.delete;
4373 --
4374 if g_qual_titl_lookup.count = 0 then
4375 --
4376 -- Cache not populated yet. So populate it now.
4377 --
4378 l_lookup_query :=
4379 'select vrbl_rt_prfl_id, business_group_id' ||
4380 ' from ben_vrbl_rt_prfl_f vpf' ||
4381 ' where business_group_id = ' || to_char(p_business_group_id) ||
4382 ' and exists (select null' ||
4383 ' from ben_qual_titl_rt_f qtr' ||
4384 ' where qtr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4385 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4386 ' between qtr.effective_start_date' ||
4387 ' and qtr.effective_end_date) ' ||
4388 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4389 ' between vpf.effective_start_date and vpf.effective_end_date;';
4390 --
4391 l_inst_query :=
4392 'select qtr.vrbl_rt_prfl_id, qtr.qualification_type_id,' ||
4393 ' qtr.title, qtr.excld_flag' ||
4394 ' from ben_qual_titl_rt_f qtr' ||
4395 ' where qtr.business_group_id = ' || to_char(p_business_group_id) ||
4396 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4397 ' between qtr.effective_start_date' ||
4398 ' and qtr.effective_end_date' ||
4399 ' order by qtr.vrbl_rt_prfl_id;';
4400 --
4401 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4402 l_instcolnm_set(0).caccol_name := 'id';
4403 l_instcolnm_set(1).col_name := 'qualification_type_id';
4404 l_instcolnm_set(1).caccol_name := 'qualification_type_id';
4405 l_instcolnm_set(2).col_name := 'title';
4406 l_instcolnm_set(2).caccol_name := 'title';
4407 l_instcolnm_set(3).col_name := 'excld_flag';
4408 l_instcolnm_set(3).caccol_name := 'excld_flag';
4409 --
4410 ben_cache.write_mastDet_Cache
4411 (p_mastercol_name => 'vrbl_rt_prfl_id'
4412 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4413 ,p_lkup_name => 'ben_rt_prfl_cache.g_qual_titl_lookup'
4414 ,p_inst_name => 'ben_rt_prfl_cache.g_qual_titl_instance'
4415 ,p_lkup_query => l_lookup_query
4416 ,p_inst_query => l_inst_query
4417 ,p_instcolnm_set => l_instcolnm_set
4418 );
4419 --
4420 end if;
4421 --
4422 -- Cache already populated. Get record set.
4423 --
4424 get_cached_data
4425 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4426 ,p_lookup_name => 'ben_rt_prfl_cache.g_qual_titl_lookup'
4427 ,p_inst_name => 'ben_rt_prfl_cache.g_qual_titl_instance'
4428 ,p_inst_set_type => 'ben_rt_prfl_cache.g_qual_titl_inst_tbl'
4429 ,p_out_inst_name => 'ben_rt_prfl_cache.g_qual_titl_out'
4430 );
4431 --
4432 p_inst_set := g_qual_titl_out;
4433 p_inst_count := g_inst_count;
4434 --
4435 exception
4436 --
4437 when no_data_found then
4438 --
4439 p_inst_count := 0;
4440 hr_utility.set_location('No qualification title found', 90);
4441 hr_utility.set_location('Leaving : ' || l_proc, 99);
4442 --
4443 end;
4444
4445
4446 ------------------------------------------------------------------------
4447 -- DCR Covered by Other Plan
4448 ------------------------------------------------------------------------
4449
4450 procedure get_rt_prfl_cache
4451 (p_vrbl_rt_prfl_id in number
4452 ,p_effective_date in date
4453 ,p_lf_evt_ocrd_dt in date
4454 ,p_business_group_id in number
4455 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_inst_tbl
4456 ,p_inst_count out nocopy number) as
4457
4458 --
4459 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4460 l_lookup_query long;
4461 l_inst_query long;
4462 l_instcolnm_set ben_cache.instcolnmtype;
4463 --
4464 begin
4465 --
4466 g_dpnt_cvrd_othr_pl_out.delete;
4467 --
4468 if g_dpnt_cvrd_othr_pl_lookup.count = 0 then
4469 --
4470 -- Cache not populated yet. So populate it now.
4471 --
4472 l_lookup_query :=
4473 'select vrbl_rt_prfl_id, business_group_id' ||
4474 ' from ben_vrbl_rt_prfl_f vpf' ||
4475 ' where business_group_id = ' || to_char(p_business_group_id) ||
4476 ' and exists (select null' ||
4477 ' from BEN_DPNT_CVRD_OTHR_PL_RT_F dcr' ||
4478 ' where dcr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4479 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4480 ' between dcr.effective_start_date' ||
4481 ' and dcr.effective_end_date) ' ||
4482 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4483 ' between vpf.effective_start_date and vpf.effective_end_date;';
4484 --
4485 l_inst_query :=
4486 'select dcr.vrbl_rt_prfl_id, dcr.pl_id,' ||
4487 ' dcr.cvg_det_dt_cd , dcr.excld_flag' ||
4488 ' from BEN_DPNT_CVRD_OTHR_PL_RT_F dcr' ||
4489 ' where dcr.business_group_id = ' || to_char(p_business_group_id) ||
4490 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4491 ' between dcr.effective_start_date' ||
4492 ' and dcr.effective_end_date' ||
4493 ' order by dcr.vrbl_rt_prfl_id;';
4494 --
4495 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4496 l_instcolnm_set(0).caccol_name := 'id';
4497 l_instcolnm_set(1).col_name := 'pl_id';
4498 l_instcolnm_set(1).caccol_name := 'pl_id';
4499 l_instcolnm_set(2).col_name := 'cvg_det_dt_cd';
4500 l_instcolnm_set(2).caccol_name := 'cvg_det_dt_cd';
4501 l_instcolnm_set(3).col_name := 'excld_flag';
4502 l_instcolnm_set(3).caccol_name := 'excld_flag';
4503 --
4504 ben_cache.write_mastDet_Cache
4505 (p_mastercol_name => 'vrbl_rt_prfl_id'
4506 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4507 ,p_lkup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_lookup'
4508 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_instance'
4509 ,p_lkup_query => l_lookup_query
4510 ,p_inst_query => l_inst_query
4511 ,p_instcolnm_set => l_instcolnm_set
4512 );
4513 --
4514 end if;
4515 --
4516 -- Cache already populated. Get record set.
4517 --
4518 get_cached_data
4519 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4520 ,p_lookup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_lookup'
4521 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_instance'
4522 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_inst_tbl'
4523 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pl_out'
4524 );
4525 --
4526 p_inst_set := g_dpnt_cvrd_othr_pl_out;
4527 p_inst_count := g_inst_count;
4528 --
4529 exception
4530 --
4531 when no_data_found then
4532 --
4533 p_inst_count := 0;
4534 hr_utility.set_location('Covered by Other Plan found', 90);
4535 hr_utility.set_location('Leaving : ' || l_proc, 99);
4536 --
4537 end ;
4538 ------------------------------------------------------------------------
4539 -- DCP Covered by Other Plan in Program
4540 ------------------------------------------------------------------------
4541 --
4542 procedure get_rt_prfl_cache
4543 (p_vrbl_rt_prfl_id in number
4544 ,p_effective_date in date
4545 ,p_lf_evt_ocrd_dt in date
4546 ,p_business_group_id in number
4547 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_inst_tbl
4548 ,p_inst_count out nocopy number) as
4549 --
4550 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4551 l_lookup_query long;
4552 l_inst_query long;
4553 l_instcolnm_set ben_cache.instcolnmtype;
4554 --
4555 begin
4556 --
4557 g_dpnt_cvrd_othr_plip_out.delete;
4558 --
4559 if g_dpnt_cvrd_othr_plip_lookup.count = 0 then
4560 --
4561 -- Cache not populated yet. So populate it now.
4562 --
4563 l_lookup_query :=
4564 'select vrbl_rt_prfl_id, business_group_id' ||
4565 ' from ben_vrbl_rt_prfl_f vpf' ||
4566 ' where business_group_id = ' || to_char(p_business_group_id) ||
4567 ' and exists (select null' ||
4568 ' from BEN_DPNT_CVRD_PLIP_RT_F dcp' ||
4569 ' where dcp.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4570 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4571 ' between dcp.effective_start_date' ||
4572 ' and dcp.effective_end_date) ' ||
4573 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4574 ' between vpf.effective_start_date and vpf.effective_end_date;';
4575 --
4576 l_inst_query :=
4577 'select dcp.vrbl_rt_prfl_id, dcp.plip_id,' ||
4578 ' dcp.enrl_det_dt_cd , dcp.excld_flag' ||
4579 ' from BEN_DPNT_CVRD_PLIP_RT_F dcp' ||
4580 ' where dcp.business_group_id = ' || to_char(p_business_group_id) ||
4581 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4582 ' between dcp.effective_start_date' ||
4583 ' and dcp.effective_end_date' ||
4584 ' order by dcp.vrbl_rt_prfl_id;';
4585 --
4586 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4587 l_instcolnm_set(0).caccol_name := 'id';
4588 l_instcolnm_set(1).col_name := 'plip_id';
4589 l_instcolnm_set(1).caccol_name := 'plip_id';
4590 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
4591 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
4592 l_instcolnm_set(3).col_name := 'excld_flag';
4593 l_instcolnm_set(3).caccol_name := 'excld_flag';
4594 --
4595 ben_cache.write_mastDet_Cache
4596 (p_mastercol_name => 'vrbl_rt_prfl_id'
4597 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4598 ,p_lkup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_lookup'
4599 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_instance'
4600 ,p_lkup_query => l_lookup_query
4601 ,p_inst_query => l_inst_query
4602 ,p_instcolnm_set => l_instcolnm_set
4603 );
4604 --
4605 end if;
4606 --
4607 -- Cache already populated. Get record set.
4608 --
4609 get_cached_data
4610 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4611 ,p_lookup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_lookup'
4612 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_instance'
4613 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_inst_tbl'
4614 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_plip_out'
4615 );
4616 --
4617 p_inst_set := g_dpnt_cvrd_othr_plip_out;
4618 p_inst_count := g_inst_count;
4619 --
4620 exception
4621 --
4622 when no_data_found then
4623 --
4624 p_inst_count := 0;
4625 hr_utility.set_location('Covered by Other Plan in Program', 90);
4626 hr_utility.set_location('Leaving : ' || l_proc, 99);
4627 --
4628 end ;
4629 ------------------------------------------------------------------------
4630 -- DCO Covered by Other Plan Type in Program
4631 ------------------------------------------------------------------------
4632 --
4633 procedure get_rt_prfl_cache
4634 (p_vrbl_rt_prfl_id in number
4635 ,p_effective_date in date
4636 ,p_lf_evt_ocrd_dt in date
4637 ,p_business_group_id in number
4638 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_inst_tbl
4639 ,p_inst_count out nocopy number) as
4640
4641 --
4642 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4643 l_lookup_query long;
4644 l_inst_query long;
4645 l_instcolnm_set ben_cache.instcolnmtype;
4646 --
4647 begin
4648 --
4649 g_dpnt_cvrd_othr_ptip_out.delete;
4650 --
4651 if g_dpnt_cvrd_othr_ptip_lookup.count = 0 then
4652 --
4653 -- Cache not populated yet. So populate it now.
4654 --
4655 l_lookup_query :=
4656 'select vrbl_rt_prfl_id, business_group_id' ||
4657 ' from ben_vrbl_rt_prfl_f vpf' ||
4658 ' where business_group_id = ' || to_char(p_business_group_id) ||
4659 ' and exists (select null' ||
4660 ' from BEN_DPNT_CVRD_OTHR_PTIP_RT_F dco' ||
4661 ' where dco.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4662 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4663 ' between dco.effective_start_date' ||
4664 ' and dco.effective_end_date) ' ||
4665 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4666 ' between vpf.effective_start_date and vpf.effective_end_date;';
4667 --
4668 l_inst_query :=
4669 'select dco.vrbl_rt_prfl_id, dco.ptip_id,' ||
4670 ' dco.enrl_det_dt_cd , dco.excld_flag ,dco.only_pls_subj_cobra_flag ' ||
4671 ' from BEN_DPNT_CVRD_OTHR_PTIP_RT_F dco' ||
4672 ' where dco.business_group_id = ' || to_char(p_business_group_id) ||
4673 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4674 ' between dco.effective_start_date' ||
4675 ' and dco.effective_end_date' ||
4676 ' order by dco.vrbl_rt_prfl_id;';
4677 --
4678 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4679 l_instcolnm_set(0).caccol_name := 'id';
4680 l_instcolnm_set(1).col_name := 'ptip_id';
4681 l_instcolnm_set(1).caccol_name := 'ptip_id';
4682 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
4683 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
4684 l_instcolnm_set(3).col_name := 'excld_flag';
4685 l_instcolnm_set(3).caccol_name := 'excld_flag';
4686 l_instcolnm_set(4).col_name := 'only_pls_subj_cobra_flag';
4687 l_instcolnm_set(4).caccol_name := 'only_pls_subj_cobra_flag';
4688
4689 --
4690 ben_cache.write_mastDet_Cache
4691 (p_mastercol_name => 'vrbl_rt_prfl_id'
4692 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4693 ,p_lkup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_lookup'
4694 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_instance'
4695 ,p_lkup_query => l_lookup_query
4696 ,p_inst_query => l_inst_query
4697 ,p_instcolnm_set => l_instcolnm_set
4698 );
4699 --
4700 end if;
4701 --
4702 -- Cache already populated. Get record set.
4703 --
4704 get_cached_data
4705 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4706 ,p_lookup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_lookup'
4707 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_instance'
4708 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_inst_tbl'
4709 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_ptip_out'
4710 );
4711 --
4712 p_inst_set := g_dpnt_cvrd_othr_ptip_out;
4713 p_inst_count := g_inst_count;
4714 --
4715 exception
4716 --
4717 when no_data_found then
4718 --
4719 p_inst_count := 0;
4720 hr_utility.set_location('Covered by Other Plan Type in Program', 90);
4721 hr_utility.set_location('Leaving : ' || l_proc, 99);
4722 --
4723 end ;
4724 --
4725 ------------------------------------------------------------------------
4726 -- DOP Covered by Other Program
4727 ------------------------------------------------------------------------
4728 --
4729 procedure get_rt_prfl_cache
4730 (p_vrbl_rt_prfl_id in number
4731 ,p_effective_date in date
4732 ,p_lf_evt_ocrd_dt in date
4733 ,p_business_group_id in number
4734 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_inst_tbl
4735 ,p_inst_count out nocopy number) as
4736 --
4737 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4738 l_lookup_query long;
4739 l_inst_query long;
4740 l_instcolnm_set ben_cache.instcolnmtype;
4741 --
4742 begin
4743 --
4744 g_dpnt_cvrd_othr_pgm_out.delete;
4745 --
4746 if g_dpnt_cvrd_othr_pgm_lookup.count = 0 then
4747 --
4748 -- Cache not populated yet. So populate it now.
4749 --
4750 l_lookup_query :=
4751 'select vrbl_rt_prfl_id, business_group_id' ||
4752 ' from ben_vrbl_rt_prfl_f vpf' ||
4753 ' where business_group_id = ' || to_char(p_business_group_id) ||
4754 ' and exists (select null' ||
4755 ' from BEN_DPNT_CVRD_OTHR_PGM_RT_F dop' ||
4756 ' where dop.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4757 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4758 ' between dop.effective_start_date' ||
4759 ' and dop.effective_end_date) ' ||
4760 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4761 ' between vpf.effective_start_date and vpf.effective_end_date;';
4762 --
4763 l_inst_query :=
4764 'select dop.vrbl_rt_prfl_id, dop.pgm_id,' ||
4765 ' dop.enrl_det_dt_cd , dop.excld_flag , dop.only_pls_subj_cobra_flag' ||
4766 ' from BEN_DPNT_CVRD_OTHR_PGM_RT_F dop' ||
4767 ' where dop.business_group_id = ' || to_char(p_business_group_id) ||
4768 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4769 ' between dop.effective_start_date' ||
4770 ' and dop.effective_end_date' ||
4771 ' order by dop.vrbl_rt_prfl_id;';
4772 --
4773 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4774 l_instcolnm_set(0).caccol_name := 'id';
4775 l_instcolnm_set(1).col_name := 'pgm_id';
4776 l_instcolnm_set(1).caccol_name := 'pgm_id';
4777 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
4778 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
4779 l_instcolnm_set(3).col_name := 'excld_flag';
4780 l_instcolnm_set(3).caccol_name := 'excld_flag';
4781 l_instcolnm_set(4).col_name := 'only_pls_subj_cobra_flag';
4782 l_instcolnm_set(4).caccol_name := 'only_pls_subj_cobra_flag';
4783 --
4784 --
4785 ben_cache.write_mastDet_Cache
4786 (p_mastercol_name => 'vrbl_rt_prfl_id'
4787 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4788 ,p_lkup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_lookup'
4789 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_instance'
4790 ,p_lkup_query => l_lookup_query
4791 ,p_inst_query => l_inst_query
4792 ,p_instcolnm_set => l_instcolnm_set
4793 );
4794 --
4795 end if;
4796 --
4797 -- Cache already populated. Get record set.
4798 --
4799 get_cached_data
4800 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4801 ,p_lookup_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_lookup'
4802 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_instance'
4803 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_inst_tbl'
4804 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dpnt_cvrd_othr_pgm_out'
4805 );
4806 --
4807 p_inst_set := g_dpnt_cvrd_othr_pgm_out;
4808 p_inst_count := g_inst_count;
4809 --
4810 exception
4811 --
4812 when no_data_found then
4813 --
4814 p_inst_count := 0;
4815 hr_utility.set_location('Covered by Other Program', 90);
4816 hr_utility.set_location('Leaving : ' || l_proc, 99);
4817 --
4818 end ;
4819 --
4820 ------------------------------------------------------------------------
4821 -- PAP Eligible for Another Plan
4822 ------------------------------------------------------------------------
4823 --
4824 procedure get_rt_prfl_cache
4825 (p_vrbl_rt_prfl_id in number
4826 ,p_effective_date in date
4827 ,p_lf_evt_ocrd_dt in date
4828 ,p_business_group_id in number
4829 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_prtt_anthr_pl_inst_tbl
4830 ,p_inst_count out nocopy number) as
4831 --
4832 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4833 l_lookup_query long;
4834 l_inst_query long;
4835 l_instcolnm_set ben_cache.instcolnmtype;
4836 --
4837 begin
4838 --
4839 g_prtt_anthr_pl_out.delete;
4840 --
4841 if g_prtt_anthr_pl_lookup.count = 0 then
4842 --
4843 -- Cache not populated yet. So populate it now.
4844 --
4845 l_lookup_query :=
4846 'select vrbl_rt_prfl_id, business_group_id' ||
4847 ' from ben_vrbl_rt_prfl_f vpf' ||
4848 ' where business_group_id = ' || to_char(p_business_group_id) ||
4849 ' and exists (select null' ||
4850 ' from BEN_PRTT_ANTHR_PL_RT_F pap' ||
4851 ' where pap.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4852 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4853 ' between pap.effective_start_date' ||
4854 ' and pap.effective_end_date) ' ||
4855 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4856 ' between vpf.effective_start_date and vpf.effective_end_date;';
4857 --
4858 l_inst_query :=
4859 'select pap.vrbl_rt_prfl_id, pap.pl_id,' ||
4860 ' pap.excld_flag ' ||
4861 ' from BEN_PRTT_ANTHR_PL_RT_F pap' ||
4862 ' where pap.business_group_id = ' || to_char(p_business_group_id) ||
4863 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4864 ' between pap.effective_start_date' ||
4865 ' and pap.effective_end_date' ||
4866 ' order by pap.vrbl_rt_prfl_id;';
4867 --
4868 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4869 l_instcolnm_set(0).caccol_name := 'id';
4870 l_instcolnm_set(1).col_name := 'pl_id';
4871 l_instcolnm_set(1).caccol_name := 'pl_id';
4872 l_instcolnm_set(2).col_name := 'excld_flag';
4873 l_instcolnm_set(2).caccol_name := 'excld_flag';
4874 --
4875 --
4876 ben_cache.write_mastDet_Cache
4877 (p_mastercol_name => 'vrbl_rt_prfl_id'
4878 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4879 ,p_lkup_name => 'ben_rt_prfl_cache.g_prtt_anthr_pl_lookup'
4880 ,p_inst_name => 'ben_rt_prfl_cache.g_prtt_anthr_pl_instance'
4881 ,p_lkup_query => l_lookup_query
4882 ,p_inst_query => l_inst_query
4883 ,p_instcolnm_set => l_instcolnm_set
4884 );
4885 --
4886 end if;
4887 --
4888 -- Cache already populated. Get record set.
4889 --
4890 get_cached_data
4891 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4892 ,p_lookup_name => 'ben_rt_prfl_cache.g_prtt_anthr_pl_lookup'
4893 ,p_inst_name => 'ben_rt_prfl_cache.g_prtt_anthr_pl_instance'
4894 ,p_inst_set_type => 'ben_rt_prfl_cache.g_prtt_anthr_pl_inst_tbl'
4895 ,p_out_inst_name => 'ben_rt_prfl_cache.g_prtt_anthr_pl_out'
4896 );
4897 --
4898 p_inst_set := g_prtt_anthr_pl_out;
4899 p_inst_count := g_inst_count;
4900 --
4901 exception
4902 --
4903 when no_data_found then
4904 --
4905 p_inst_count := 0;
4906 hr_utility.set_location('Eligible for Another Plan', 90);
4907 hr_utility.set_location('Leaving : ' || l_proc, 99);
4908 --
4909 end ;
4910 --
4911 ------------------------------------------------------------------------
4912 -- OPR Eligible for Another Plan Type in Program
4913 ------------------------------------------------------------------------
4914 --
4915 procedure get_rt_prfl_cache
4916 (p_vrbl_rt_prfl_id in number
4917 ,p_effective_date in date
4918 ,p_lf_evt_ocrd_dt in date
4919 ,p_business_group_id in number
4920 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_othr_ptip_inst_tbl
4921 ,p_inst_count out nocopy number) as
4922 --
4923 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
4924 l_lookup_query long;
4925 l_inst_query long;
4926 l_instcolnm_set ben_cache.instcolnmtype;
4927 --
4928 begin
4929 --
4930 g_othr_ptip_out.delete;
4931 --
4932 if g_othr_ptip_lookup.count = 0 then
4933 --
4934 -- Cache not populated yet. So populate it now.
4935 --
4936 l_lookup_query :=
4937 'select vrbl_rt_prfl_id, business_group_id' ||
4938 ' from ben_vrbl_rt_prfl_f vpf' ||
4939 ' where business_group_id = ' || to_char(p_business_group_id) ||
4940 ' and exists (select null' ||
4941 ' from BEN_OTHR_PTIP_RT_F opr' ||
4942 ' where opr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
4943 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
4944 ' between opr.effective_start_date' ||
4945 ' and opr.effective_end_date) ' ||
4946 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4947 ' between vpf.effective_start_date and vpf.effective_end_date;';
4948 --
4949 l_inst_query :=
4950 'select opr.vrbl_rt_prfl_id, opr.ptip_id,' ||
4951 ' opr.excld_flag ,opr.only_pls_subj_cobra_flag ' ||
4952 ' from BEN_OTHR_PTIP_RT_F opr' ||
4953 ' where opr.business_group_id = ' || to_char(p_business_group_id) ||
4954 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
4955 ' between opr.effective_start_date' ||
4956 ' and opr.effective_end_date' ||
4957 ' order by opr.vrbl_rt_prfl_id;';
4958 --
4959 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
4960 l_instcolnm_set(0).caccol_name := 'id';
4961 l_instcolnm_set(1).col_name := 'ptip_id';
4962 l_instcolnm_set(1).caccol_name := 'ptip_id';
4963 l_instcolnm_set(2).col_name := 'excld_flag';
4964 l_instcolnm_set(2).caccol_name := 'excld_flag';
4965 l_instcolnm_set(3).col_name := 'only_pls_subj_cobra_flag';
4966 l_instcolnm_set(3).caccol_name := 'only_pls_subj_cobra_flag';
4967
4968 --
4969 --
4970 ben_cache.write_mastDet_Cache
4971 (p_mastercol_name => 'vrbl_rt_prfl_id'
4972 ,p_detailcol_name => 'vrbl_rt_prfl_id'
4973 ,p_lkup_name => 'ben_rt_prfl_cache.g_othr_ptip_lookup'
4974 ,p_inst_name => 'ben_rt_prfl_cache.g_othr_ptip_instance'
4975 ,p_lkup_query => l_lookup_query
4976 ,p_inst_query => l_inst_query
4977 ,p_instcolnm_set => l_instcolnm_set
4978 );
4979 --
4980 end if;
4981 --
4982 -- Cache already populated. Get record set.
4983 --
4984 get_cached_data
4985 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
4986 ,p_lookup_name => 'ben_rt_prfl_cache.g_othr_ptip_lookup'
4987 ,p_inst_name => 'ben_rt_prfl_cache.g_othr_ptip_instance'
4988 ,p_inst_set_type => 'ben_rt_prfl_cache.g_othr_ptip_inst_tbl'
4989 ,p_out_inst_name => 'ben_rt_prfl_cache.g_othr_ptip_out'
4990 );
4991 --
4992 p_inst_set := g_othr_ptip_out;
4993 p_inst_count := g_inst_count;
4994 --
4995 exception
4996 --
4997 when no_data_found then
4998 --
4999 p_inst_count := 0;
5000 hr_utility.set_location('Eligible for Another Plan Type in Program', 90);
5001 hr_utility.set_location('Leaving : ' || l_proc, 99);
5002 --
5003 end ;
5004 --
5005 ------------------------------------------------------------------------
5006 -- ENL Enrolled Another Plan
5007 ------------------------------------------------------------------------
5008 --
5009 procedure get_rt_prfl_cache
5010 (p_vrbl_rt_prfl_id in number
5011 ,p_effective_date in date
5012 ,p_lf_evt_ocrd_dt in date
5013 ,p_business_group_id in number
5014 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_pl_inst_tbl
5015 ,p_inst_count out nocopy number) as
5016 --
5017 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5018 l_lookup_query long;
5019 l_inst_query long;
5020 l_instcolnm_set ben_cache.instcolnmtype;
5021 --
5022 begin
5023 --
5024 g_enrld_anthr_pl_out.delete;
5025 --
5026 if g_enrld_anthr_pl_lookup.count = 0 then
5027 --
5028 -- Cache not populated yet. So populate it now.
5029 --
5030 l_lookup_query :=
5031 'select vrbl_rt_prfl_id, business_group_id' ||
5032 ' from ben_vrbl_rt_prfl_f vpf' ||
5033 ' where business_group_id = ' || to_char(p_business_group_id) ||
5034 ' and exists (select null' ||
5035 ' from BEN_ENRLD_ANTHR_PL_RT_F enl' ||
5036 ' where enl.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5037 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5038 ' between enl.effective_start_date' ||
5039 ' and enl.effective_end_date) ' ||
5040 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5041 ' between vpf.effective_start_date and vpf.effective_end_date;';
5042 --
5043 l_inst_query :=
5044 'select enl.vrbl_rt_prfl_id, enl.pl_id,' ||
5045 ' enl.enrl_det_dt_cd , enl.excld_flag ' ||
5046 ' from BEN_ENRLD_ANTHR_PL_RT_F enl' ||
5047 ' where enl.business_group_id = ' || to_char(p_business_group_id) ||
5048 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5049 ' between enl.effective_start_date' ||
5050 ' and enl.effective_end_date' ||
5051 ' order by enl.vrbl_rt_prfl_id;';
5052 --
5053 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5054 l_instcolnm_set(0).caccol_name := 'id';
5055 l_instcolnm_set(1).col_name := 'pl_id';
5056 l_instcolnm_set(1).caccol_name := 'pl_id';
5057 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
5058 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
5059 l_instcolnm_set(3).col_name := 'excld_flag';
5060 l_instcolnm_set(3).caccol_name := 'excld_flag';
5061 --
5062 --
5063 ben_cache.write_mastDet_Cache
5064 (p_mastercol_name => 'vrbl_rt_prfl_id'
5065 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5066 ,p_lkup_name => 'ben_rt_prfl_cache.g_enrld_anthr_pl_lookup'
5067 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pl_instance'
5068 ,p_lkup_query => l_lookup_query
5069 ,p_inst_query => l_inst_query
5070 ,p_instcolnm_set => l_instcolnm_set
5071 );
5072 --
5073 end if;
5074 --
5075 -- Cache already populated. Get record set.
5076 --
5077 get_cached_data
5078 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5079 ,p_lookup_name => 'ben_rt_prfl_cache.g_enrld_anthr_pl_lookup'
5080 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pl_instance'
5081 ,p_inst_set_type => 'ben_rt_prfl_cache.g_enrld_anthr_pl_inst_tbl'
5082 ,p_out_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pl_out'
5083 );
5084 --
5085 p_inst_set := g_enrld_anthr_pl_out;
5086 p_inst_count := g_inst_count;
5087 --
5088 exception
5089 --
5090 when no_data_found then
5091 --
5092 p_inst_count := 0;
5093 hr_utility.set_location('Enrolled Another Plan', 90);
5094 hr_utility.set_location('Leaving : ' || l_proc, 99);
5095 --
5096 end ;
5097 --
5098 ------------------------------------------------------------------------
5099 -- EAO Enrolled Another Option in Plan
5100 ------------------------------------------------------------------------
5101 --
5102 procedure get_rt_prfl_cache
5103 (p_vrbl_rt_prfl_id in number
5104 ,p_effective_date in date
5105 ,p_lf_evt_ocrd_dt in date
5106 ,p_business_group_id in number
5107 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_oipl_inst_tbl
5108 ,p_inst_count out nocopy number) as
5109 --
5110 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5111 l_lookup_query long;
5112 l_inst_query long;
5113 l_instcolnm_set ben_cache.instcolnmtype;
5114 --
5115 begin
5116 --
5117 g_enrld_anthr_oipl_out.delete;
5118 --
5119 if g_enrld_anthr_oipl_lookup.count = 0 then
5120 --
5121 -- Cache not populated yet. So populate it now.
5122 --
5123 l_lookup_query :=
5124 'select vrbl_rt_prfl_id, business_group_id' ||
5125 ' from ben_vrbl_rt_prfl_f vpf' ||
5126 ' where business_group_id = ' || to_char(p_business_group_id) ||
5127 ' and exists (select null' ||
5128 ' from BEN_ENRLD_ANTHR_OIPL_RT_F eao' ||
5129 ' where eao.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5130 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5131 ' between eao.effective_start_date' ||
5132 ' and eao.effective_end_date) ' ||
5133 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5134 ' between vpf.effective_start_date and vpf.effective_end_date;';
5135 --
5136 l_inst_query :=
5137 'select eao.vrbl_rt_prfl_id, eao.oipl_id,' ||
5138 ' eao.enrl_det_dt_cd , eao.excld_flag ' ||
5139 ' from BEN_ENRLD_ANTHR_OIPL_RT_F eao' ||
5140 ' where eao.business_group_id = ' || to_char(p_business_group_id) ||
5141 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5142 ' between eao.effective_start_date' ||
5143 ' and eao.effective_end_date' ||
5144 ' order by eao.vrbl_rt_prfl_id;';
5145 --
5146 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5147 l_instcolnm_set(0).caccol_name := 'id';
5148 l_instcolnm_set(1).col_name := 'oipl_id';
5149 l_instcolnm_set(1).caccol_name := 'oipl_id';
5150 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
5151 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
5152 l_instcolnm_set(3).col_name := 'excld_flag';
5153 l_instcolnm_set(3).caccol_name := 'excld_flag';
5154 --
5155 --
5156 ben_cache.write_mastDet_Cache
5157 (p_mastercol_name => 'vrbl_rt_prfl_id'
5158 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5159 ,p_lkup_name => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_lookup'
5160 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_instance'
5161 ,p_lkup_query => l_lookup_query
5162 ,p_inst_query => l_inst_query
5163 ,p_instcolnm_set => l_instcolnm_set
5164 );
5165 --
5166 end if;
5167 --
5168 -- Cache already populated. Get record set.
5169 --
5170 get_cached_data
5171 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5172 ,p_lookup_name => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_lookup'
5173 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_instance'
5174 ,p_inst_set_type => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_inst_tbl'
5175 ,p_out_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_oipl_out'
5176 );
5177 --
5178 p_inst_set := g_enrld_anthr_oipl_out;
5179 p_inst_count := g_inst_count;
5180 --
5181 exception
5182 --
5183 when no_data_found then
5184 --
5185 p_inst_count := 0;
5186 hr_utility.set_location('Enrolled Another Option in Plan', 90);
5187 hr_utility.set_location('Leaving : ' || l_proc, 99);
5188 --
5189 end ;
5190 --
5191 ------------------------------------------------------------------------
5192 -- EAR Enrolled Another Plan in Program
5193 ------------------------------------------------------------------------
5194 --
5195 procedure get_rt_prfl_cache
5196 (p_vrbl_rt_prfl_id in number
5197 ,p_effective_date in date
5198 ,p_lf_evt_ocrd_dt in date
5199 ,p_business_group_id in number
5200 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_plip_inst_tbl
5201 ,p_inst_count out nocopy number) as
5202 --
5203 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5204 l_lookup_query long;
5205 l_inst_query long;
5206 l_instcolnm_set ben_cache.instcolnmtype;
5207 --
5208 begin
5209 --
5210 g_enrld_anthr_plip_out.delete;
5211 --
5212 if g_enrld_anthr_plip_lookup.count = 0 then
5213 --
5214 -- Cache not populated yet. So populate it now.
5215 --
5216 l_lookup_query :=
5217 'select vrbl_rt_prfl_id, business_group_id' ||
5218 ' from ben_vrbl_rt_prfl_f vpf' ||
5219 ' where business_group_id = ' || to_char(p_business_group_id) ||
5220 ' and exists (select null' ||
5221 ' from BEN_ENRLD_ANTHR_PLIP_RT_F ear' ||
5222 ' where ear.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5223 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5224 ' between ear.effective_start_date' ||
5225 ' and ear.effective_end_date) ' ||
5226 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5227 ' between vpf.effective_start_date and vpf.effective_end_date;';
5228 --
5229 l_inst_query :=
5230 'select ear.vrbl_rt_prfl_id, ear.plip_id,' ||
5231 ' ear.enrl_det_dt_cd , ear.excld_flag ' ||
5232 ' from BEN_ENRLD_ANTHR_PLIP_RT_F ear' ||
5233 ' where ear.business_group_id = ' || to_char(p_business_group_id) ||
5234 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5235 ' between ear.effective_start_date' ||
5236 ' and ear.effective_end_date' ||
5237 ' order by ear.vrbl_rt_prfl_id;';
5238 --
5239 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5240 l_instcolnm_set(0).caccol_name := 'id';
5241 l_instcolnm_set(1).col_name := 'plip_id';
5242 l_instcolnm_set(1).caccol_name := 'plip_id';
5243 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
5244 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
5245 l_instcolnm_set(3).col_name := 'excld_flag';
5246 l_instcolnm_set(3).caccol_name := 'excld_flag';
5247 --
5248 --
5249 ben_cache.write_mastDet_Cache
5250 (p_mastercol_name => 'vrbl_rt_prfl_id'
5251 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5252 ,p_lkup_name => 'ben_rt_prfl_cache.g_enrld_anthr_plip_lookup'
5253 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_plip_instance'
5254 ,p_lkup_query => l_lookup_query
5255 ,p_inst_query => l_inst_query
5256 ,p_instcolnm_set => l_instcolnm_set
5257 );
5258 --
5259 end if;
5260 --
5261 -- Cache already populated. Get record set.
5262 --
5263 get_cached_data
5264 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5265 ,p_lookup_name => 'ben_rt_prfl_cache.g_enrld_anthr_plip_lookup'
5266 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_plip_instance'
5267 ,p_inst_set_type => 'ben_rt_prfl_cache.g_enrld_anthr_plip_inst_tbl'
5268 ,p_out_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_plip_out'
5269 );
5270 --
5271 p_inst_set := g_enrld_anthr_plip_out;
5272 p_inst_count := g_inst_count;
5273 --
5274 exception
5275 --
5276 when no_data_found then
5277 --
5278 p_inst_count := 0;
5279 hr_utility.set_location('Enrolled Another Plan in Program', 90);
5280 hr_utility.set_location('Leaving : ' || l_proc, 99);
5281 --
5282 end ;
5283 --
5284 ------------------------------------------------------------------------
5285 -- ENT Enrolled Another Plan Type in Program
5286 ------------------------------------------------------------------------
5287 --
5288 procedure get_rt_prfl_cache
5289 (p_vrbl_rt_prfl_id in number
5290 ,p_effective_date in date
5291 ,p_lf_evt_ocrd_dt in date
5292 ,p_business_group_id in number
5293 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_ptip_inst_tbl
5294 ,p_inst_count out nocopy number) as
5295 --
5296 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5297 l_lookup_query long;
5298 l_inst_query long;
5299 l_instcolnm_set ben_cache.instcolnmtype;
5300 --
5301 begin
5302 --
5303 g_enrld_anthr_ptip_out.delete;
5304 --
5305 if g_enrld_anthr_ptip_lookup.count = 0 then
5306 --
5307 -- Cache not populated yet. So populate it now.
5308 --
5309 l_lookup_query :=
5310 'select vrbl_rt_prfl_id, business_group_id' ||
5311 ' from ben_vrbl_rt_prfl_f vpf' ||
5312 ' where business_group_id = ' || to_char(p_business_group_id) ||
5313 ' and exists (select null' ||
5314 ' from BEN_ENRLD_ANTHR_PTIP_RT_F ent' ||
5315 ' where ent.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5316 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5317 ' between ent.effective_start_date' ||
5318 ' and ent.effective_end_date) ' ||
5319 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5320 ' between vpf.effective_start_date and vpf.effective_end_date;';
5321 --
5322 l_inst_query :=
5323 'select ent.vrbl_rt_prfl_id, ent.ptip_id,' ||
5324 ' ent.enrl_det_dt_cd , ent.excld_flag , ent.only_pls_subj_cobra_flag ' ||
5325 ' from BEN_ENRLD_ANTHR_PTIP_RT_F ent' ||
5326 ' where ent.business_group_id = ' || to_char(p_business_group_id) ||
5327 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5328 ' between ent.effective_start_date' ||
5329 ' and ent.effective_end_date' ||
5330 ' order by ent.vrbl_rt_prfl_id;';
5331 --
5332 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5333 l_instcolnm_set(0).caccol_name := 'id';
5334 l_instcolnm_set(1).col_name := 'ptip_id';
5335 l_instcolnm_set(1).caccol_name := 'ptip_id';
5336 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
5337 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
5338 l_instcolnm_set(3).col_name := 'excld_flag';
5339 l_instcolnm_set(3).caccol_name := 'excld_flag';
5340 l_instcolnm_set(4).col_name := 'only_pls_subj_cobra_flag';
5341 l_instcolnm_set(4).caccol_name := 'only_pls_subj_cobra_flag';
5342
5343 --
5344 --
5345 ben_cache.write_mastDet_Cache
5346 (p_mastercol_name => 'vrbl_rt_prfl_id'
5347 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5348 ,p_lkup_name => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_lookup'
5349 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_instance'
5350 ,p_lkup_query => l_lookup_query
5351 ,p_inst_query => l_inst_query
5352 ,p_instcolnm_set => l_instcolnm_set
5353 );
5354 --
5355 end if;
5356 --
5357 -- Cache already populated. Get record set.
5358 --
5359 get_cached_data
5360 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5361 ,p_lookup_name => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_lookup'
5362 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_instance'
5363 ,p_inst_set_type => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_inst_tbl'
5364 ,p_out_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_ptip_out'
5365 );
5366 --
5367 p_inst_set := g_enrld_anthr_ptip_out;
5368 p_inst_count := g_inst_count;
5369 --
5370 exception
5371 --
5372 when no_data_found then
5373 --
5374 p_inst_count := 0;
5375 hr_utility.set_location('Enrolled Another Plan Type in Program', 90);
5376 hr_utility.set_location('Leaving : ' || l_proc, 99);
5377 --
5378 end ;
5379 --
5380 ------------------------------------------------------------------------
5381 -- EAG Enrolled Another Program
5382 ------------------------------------------------------------------------
5383 --
5384 procedure get_rt_prfl_cache
5385 (p_vrbl_rt_prfl_id in number
5386 ,p_effective_date in date
5387 ,p_lf_evt_ocrd_dt in date
5388 ,p_business_group_id in number
5389 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_enrld_anthr_pgm_inst_tbl
5390 ,p_inst_count out nocopy number) as
5391 --
5392 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5393 l_lookup_query long;
5394 l_inst_query long;
5395 l_instcolnm_set ben_cache.instcolnmtype;
5396 --
5397 begin
5398 --
5399 g_enrld_anthr_pgm_out.delete;
5400 --
5401 if g_enrld_anthr_pgm_lookup.count = 0 then
5402 --
5403 -- Cache not populated yet. So populate it now.
5404 --
5405 l_lookup_query :=
5406 'select vrbl_rt_prfl_id, business_group_id' ||
5407 ' from ben_vrbl_rt_prfl_f vpf' ||
5408 ' where business_group_id = ' || to_char(p_business_group_id) ||
5409 ' and exists (select null' ||
5410 ' from BEN_ENRLD_ANTHR_PGM_RT_F eag' ||
5411 ' where eag.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5412 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5413 ' between eag.effective_start_date' ||
5414 ' and eag.effective_end_date) ' ||
5415 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5416 ' between vpf.effective_start_date and vpf.effective_end_date;';
5417 --
5418 l_inst_query :=
5419 'select eag.vrbl_rt_prfl_id, eag.pgm_id,' ||
5420 ' eag.enrl_det_dt_cd , eag.excld_flag ' ||
5421 ' from BEN_ENRLD_ANTHR_PGM_RT_F eag' ||
5422 ' where eag.business_group_id = ' || to_char(p_business_group_id) ||
5423 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5424 ' between eag.effective_start_date' ||
5425 ' and eag.effective_end_date' ||
5426 ' order by eag.vrbl_rt_prfl_id;';
5427 --
5428 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5429 l_instcolnm_set(0).caccol_name := 'id';
5430 l_instcolnm_set(1).col_name := 'pgm_id';
5431 l_instcolnm_set(1).caccol_name := 'pgm_id';
5432 l_instcolnm_set(2).col_name := 'enrl_det_dt_cd';
5433 l_instcolnm_set(2).caccol_name := 'enrl_det_dt_cd';
5434 l_instcolnm_set(3).col_name := 'excld_flag';
5435 l_instcolnm_set(3).caccol_name := 'excld_flag';
5436
5437 --
5438 --
5439 ben_cache.write_mastDet_Cache
5440 (p_mastercol_name => 'vrbl_rt_prfl_id'
5441 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5442 ,p_lkup_name => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_lookup'
5443 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_instance'
5444 ,p_lkup_query => l_lookup_query
5445 ,p_inst_query => l_inst_query
5446 ,p_instcolnm_set => l_instcolnm_set
5447 );
5448 --
5449 end if;
5450 --
5451 -- Cache already populated. Get record set.
5452 --
5453 get_cached_data
5454 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5455 ,p_lookup_name => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_lookup'
5456 ,p_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_instance'
5457 ,p_inst_set_type => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_inst_tbl'
5458 ,p_out_inst_name => 'ben_rt_prfl_cache.g_enrld_anthr_pgm_out'
5459 );
5460 --
5461 p_inst_set := g_enrld_anthr_pgm_out;
5462 p_inst_count := g_inst_count;
5463 --
5464 exception
5465 --
5466 when no_data_found then
5467 --
5468 p_inst_count := 0;
5469 hr_utility.set_location('Enrolled Another Program', 90);
5470 hr_utility.set_location('Leaving : ' || l_proc, 99);
5471 --
5472 end ;
5473
5474 ------------------------------------------------------------------------
5475 -- DOT Dependent Eligible for another plan type in program
5476 ------------------------------------------------------------------------
5477 --
5478 procedure get_rt_prfl_cache
5479 (p_vrbl_rt_prfl_id in number
5480 ,p_effective_date in date
5481 ,p_lf_evt_ocrd_dt in date
5482 ,p_business_group_id in number
5483 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_dpnt_othr_ptip_inst_tbl
5484 ,p_inst_count out nocopy number) as
5485 --
5486 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5487 l_lookup_query long;
5488 l_inst_query long;
5489 l_instcolnm_set ben_cache.instcolnmtype;
5490 --
5491 begin
5492 --
5493 g_dpnt_othr_ptip_out.delete;
5494 --
5495 if g_dpnt_othr_ptip_lookup.count = 0 then
5496 --
5497 -- Cache not populated yet. So populate it now.
5498 --
5499 l_lookup_query :=
5500 'select vrbl_rt_prfl_id, business_group_id' ||
5501 ' from ben_vrbl_rt_prfl_f vpf' ||
5502 ' where business_group_id = ' || to_char(p_business_group_id) ||
5503 ' and exists (select null' ||
5504 ' from BEN_DPNT_OTHR_PTIP_RT_F dot' ||
5505 ' where dot.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5506 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5507 ' between dot.effective_start_date' ||
5508 ' and dot.effective_end_date) ' ||
5509 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5510 ' between vpf.effective_start_date and vpf.effective_end_date;';
5511 --
5512 l_inst_query :=
5513 'select dot.vrbl_rt_prfl_id, dot.ptip_id,' ||
5514 ' dot.excld_flag ' ||
5515 ' from BEN_DPNT_OTHR_PTIP_RT_F dot' ||
5516 ' where dot.business_group_id = ' || to_char(p_business_group_id) ||
5517 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5518 ' between dot.effective_start_date' ||
5519 ' and dot.effective_end_date' ||
5520 ' order by dot.vrbl_rt_prfl_id;';
5521 --
5522 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5523 l_instcolnm_set(0).caccol_name := 'id';
5524 l_instcolnm_set(1).col_name := 'ptip_id';
5525 l_instcolnm_set(1).caccol_name := 'ptip_id';
5526 l_instcolnm_set(2).col_name := 'excld_flag';
5527 l_instcolnm_set(2).caccol_name := 'excld_flag';
5528
5529 --
5530 --
5531 ben_cache.write_mastDet_Cache
5532 (p_mastercol_name => 'vrbl_rt_prfl_id'
5533 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5534 ,p_lkup_name => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_lookup'
5535 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_instance'
5536 ,p_lkup_query => l_lookup_query
5537 ,p_inst_query => l_inst_query
5538 ,p_instcolnm_set => l_instcolnm_set
5539 );
5540 --
5541 end if;
5542 --
5543 -- Cache already populated. Get record set.
5544 --
5545 get_cached_data
5546 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5547 ,p_lookup_name => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_lookup'
5548 ,p_inst_name => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_instance'
5549 ,p_inst_set_type => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_inst_tbl'
5550 ,p_out_inst_name => 'ben_rt_prfl_cache.g_dpnt_othr_ptip_out'
5551 );
5552 --
5553 p_inst_set := g_dpnt_othr_ptip_out;
5554 p_inst_count := g_inst_count;
5555 --
5556 exception
5557 --
5558 when no_data_found then
5559 --
5560 p_inst_count := 0;
5561 hr_utility.set_location('Dependent covered in other plan type in program', 90);
5562 hr_utility.set_location('Leaving : ' || l_proc, 99);
5563 --
5564 end ;
5565 ------------------------------------------------------------------------
5566 -- NOC No Other Coverage
5567 ------------------------------------------------------------------------
5568 --
5569 procedure get_rt_prfl_cache
5570 (p_vrbl_rt_prfl_id in number
5571 ,p_effective_date in date
5572 ,p_lf_evt_ocrd_dt in date
5573 ,p_business_group_id in number
5574 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_no_othr_cvg_inst_tbl
5575 ,p_inst_count out nocopy number) as
5576 --
5577 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5578 l_lookup_query long;
5579 l_inst_query long;
5580 l_instcolnm_set ben_cache.instcolnmtype;
5581 --
5582 begin
5583 --
5584 g_no_othr_cvg_out.delete;
5585 --
5586 if g_no_othr_cvg_lookup.count = 0 then
5587 --
5588 -- Cache not populated yet. So populate it now.
5589 --
5590 l_lookup_query :=
5591 'select vrbl_rt_prfl_id, business_group_id' ||
5592 ' from ben_vrbl_rt_prfl_f vpf' ||
5593 ' where business_group_id = ' || to_char(p_business_group_id) ||
5594 ' and exists (select null' ||
5595 ' from BEN_NO_OTHR_CVG_RT_F noc' ||
5596 ' where noc.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5597 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5598 ' between noc.effective_start_date' ||
5599 ' and noc.effective_end_date) ' ||
5600 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5601 ' between vpf.effective_start_date and vpf.effective_end_date;';
5602 --
5603 l_inst_query :=
5604 'select noc.vrbl_rt_prfl_id, noc.coord_ben_no_cvg_flag ' ||
5605 ' from BEN_NO_OTHR_CVG_RT_F noc' ||
5606 ' where noc.business_group_id = ' || to_char(p_business_group_id) ||
5607 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5608 ' between noc.effective_start_date' ||
5609 ' and noc.effective_end_date' ||
5610 ' order by noc.vrbl_rt_prfl_id;';
5611 --
5612 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5613 l_instcolnm_set(0).caccol_name := 'id';
5614 l_instcolnm_set(1).col_name := 'coord_ben_no_cvg_flag';
5615 l_instcolnm_set(1).caccol_name := 'coord_ben_no_cvg_flag';
5616 --
5617 --
5618 ben_cache.write_mastDet_Cache
5619 (p_mastercol_name => 'vrbl_rt_prfl_id'
5620 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5621 ,p_lkup_name => 'ben_rt_prfl_cache.g_no_othr_cvg_lookup'
5622 ,p_inst_name => 'ben_rt_prfl_cache.g_no_othr_cvg_instance'
5623 ,p_lkup_query => l_lookup_query
5624 ,p_inst_query => l_inst_query
5625 ,p_instcolnm_set => l_instcolnm_set
5626 );
5627 --
5628 end if;
5629 --
5630 -- Cache already populated. Get record set.
5631 --
5632 get_cached_data
5633 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5634 ,p_lookup_name => 'ben_rt_prfl_cache.g_no_othr_cvg_lookup'
5635 ,p_inst_name => 'ben_rt_prfl_cache.g_no_othr_cvg_instance'
5636 ,p_inst_set_type => 'ben_rt_prfl_cache.g_no_othr_cvg_inst_tbl'
5637 ,p_out_inst_name => 'ben_rt_prfl_cache.g_no_othr_cvg_out'
5638 );
5639 --
5640 p_inst_set := g_no_othr_cvg_out;
5641 p_inst_count := g_inst_count;
5642 --
5643 exception
5644 --
5645 when no_data_found then
5646 --
5647 p_inst_count := 0;
5648 hr_utility.set_location('No Other Coverage', 90);
5649 hr_utility.set_location('Leaving : ' || l_proc, 99);
5650 --
5651 end ;
5652
5653 ------------------------------------------------------------------------
5654 -- Quartile in Grade
5655 ------------------------------------------------------------------------
5656 procedure get_rt_prfl_cache
5657 (p_vrbl_rt_prfl_id in number
5658 ,p_effective_date in date
5659 ,p_lf_evt_ocrd_dt in date
5660 ,p_business_group_id in number
5661 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_qua_in_gr_inst_tbl
5662 ,p_inst_count out nocopy number)
5663 is
5664 --
5665 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5666 l_lookup_query long;
5667 l_inst_query long;
5668 l_instcolnm_set ben_cache.instcolnmtype;
5669 --
5670 begin
5671 --
5672 g_qua_in_gr_out.delete;
5673 --
5674 if g_qua_in_gr_lookup.count = 0 then
5675 --
5676 -- Cache not populated yet. So populate it now.
5677 --
5678 l_lookup_query :=
5679 'select vrbl_rt_prfl_id, business_group_id' ||
5680 ' from ben_vrbl_rt_prfl_f vpf' ||
5681 ' where business_group_id = ' || to_char(p_business_group_id) ||
5682 ' and exists (select null' ||
5683 ' from ben_qua_in_gr_rt_f qig' ||
5684 ' where qig.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5685 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5686 ' between qig.effective_start_date' ||
5687 ' and qig.effective_end_date) ' ||
5688 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5689 ' between vpf.effective_start_date and vpf.effective_end_date;';
5690 --
5691 l_inst_query :=
5692 'select qig.vrbl_rt_prfl_id, qig.quar_in_grade_cd, qig.excld_flag' ||
5693 ' from ben_qua_in_gr_rt_f qig' ||
5694 ' where qig.business_group_id = ' || to_char(p_business_group_id) ||
5695 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5696 ' between qig.effective_start_date' ||
5697 ' and qig.effective_end_date' ||
5698 ' order by qig.vrbl_rt_prfl_id;';
5699 --
5700 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5701 l_instcolnm_set(0).caccol_name := 'id';
5702 l_instcolnm_set(1).col_name := 'quar_in_grade_cd';
5703 l_instcolnm_set(1).caccol_name := 'quar_in_grade_cd';
5704 l_instcolnm_set(2).col_name := 'excld_flag';
5705 l_instcolnm_set(2).caccol_name := 'excld_flag';
5706 --
5707 ben_cache.write_mastDet_Cache
5708 (p_mastercol_name => 'vrbl_rt_prfl_id'
5709 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5710 ,p_lkup_name => 'ben_rt_prfl_cache.g_qua_in_gr_lookup'
5711 ,p_inst_name => 'ben_rt_prfl_cache.g_qua_in_gr_instance'
5712 ,p_lkup_query => l_lookup_query
5713 ,p_inst_query => l_inst_query
5714 ,p_instcolnm_set => l_instcolnm_set
5715 );
5716 --
5717 end if;
5718 --
5719 -- Cache already populated. Get record set.
5720 --
5721 get_cached_data
5722 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5723 ,p_lookup_name => 'ben_rt_prfl_cache.g_qua_in_gr_lookup'
5724 ,p_inst_name => 'ben_rt_prfl_cache.g_qua_in_gr_instance'
5725 ,p_inst_set_type => 'ben_rt_prfl_cache.g_qua_in_gr_inst_tbl'
5726 ,p_out_inst_name => 'ben_rt_prfl_cache.g_qua_in_gr_out'
5727 );
5728 --
5729 p_inst_set := g_qua_in_gr_out;
5730 p_inst_count := g_inst_count;
5731 --
5732 exception
5733 --
5734 when no_data_found then
5735 --
5736 p_inst_count := 0;
5737 hr_utility.set_location('No Quartile in Grade found', 90);
5738 hr_utility.set_location('Leaving : ' || l_proc, 99);
5739 --
5740 end;
5741
5742 ------------------------------------------------------------------------
5743 -- Performance Rating
5744 ------------------------------------------------------------------------
5745 procedure get_rt_prfl_cache
5746 (p_vrbl_rt_prfl_id in number
5747 ,p_effective_date in date
5748 ,p_lf_evt_ocrd_dt in date
5749 ,p_business_group_id in number
5750 ,p_inst_set in out NOCOPY ben_rt_prfl_cache.g_perf_rtng_inst_tbl
5751 ,p_inst_count out nocopy number)
5752 is
5753 --
5754 l_proc varchar2(80) := g_package || '.get_rt_prfl_cache';
5755 l_lookup_query long;
5756 l_inst_query long;
5757 l_instcolnm_set ben_cache.instcolnmtype;
5758 --
5759 begin
5760 --
5761 g_perf_rtng_out.delete;
5762 --
5763 if g_perf_rtng_lookup.count = 0 then
5764 --
5765 -- Cache not populated yet. So populate it now.
5766 --
5767 l_lookup_query :=
5768 'select vrbl_rt_prfl_id, business_group_id' ||
5769 ' from ben_vrbl_rt_prfl_f vpf' ||
5770 ' where business_group_id = ' || to_char(p_business_group_id) ||
5771 ' and exists (select null' ||
5772 ' from ben_perf_rtng_rt_f prr' ||
5773 ' where prr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id' ||
5774 ' and ' || date_str(p_lf_evt_ocrd_dt,p_effective_date) ||
5775 ' between prr.effective_start_date' ||
5776 ' and prr.effective_end_date) ' ||
5777 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5778 ' between vpf.effective_start_date and vpf.effective_end_date;';
5779 --
5780 l_inst_query :=
5781 'select prr.vrbl_rt_prfl_id, prr.perf_rtng_cd,' ||
5782 ' prr.event_type, prr.excld_flag' ||
5783 ' from ben_perf_rtng_rt_f prr' ||
5784 ' where prr.business_group_id = ' || to_char(p_business_group_id) ||
5785 ' and ' || date_str(p_lf_evt_ocrd_dt, p_effective_date) ||
5786 ' between prr.effective_start_date' ||
5787 ' and prr.effective_end_date' ||
5788 ' order by prr.vrbl_rt_prfl_id;';
5789 --
5790 l_instcolnm_set(0).col_name := 'vrbl_rt_prfl_id';
5791 l_instcolnm_set(0).caccol_name := 'id';
5792 l_instcolnm_set(1).col_name := 'perf_rtng_cd';
5793 l_instcolnm_set(1).caccol_name := 'perf_rtng_cd';
5794 l_instcolnm_set(2).col_name := 'event_type';
5795 l_instcolnm_set(2).caccol_name := 'event_type';
5796 l_instcolnm_set(3).col_name := 'excld_flag';
5797 l_instcolnm_set(3).caccol_name := 'excld_flag';
5798 --
5799 ben_cache.write_mastDet_Cache
5800 (p_mastercol_name => 'vrbl_rt_prfl_id'
5801 ,p_detailcol_name => 'vrbl_rt_prfl_id'
5802 ,p_lkup_name => 'ben_rt_prfl_cache.g_perf_rtng_lookup'
5803 ,p_inst_name => 'ben_rt_prfl_cache.g_perf_rtng_instance'
5804 ,p_lkup_query => l_lookup_query
5805 ,p_inst_query => l_inst_query
5806 ,p_instcolnm_set => l_instcolnm_set
5807 );
5808 --
5809 end if;
5810 --
5811 -- Cache already populated. Get record set.
5812 --
5813 get_cached_data
5814 (p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
5815 ,p_lookup_name => 'ben_rt_prfl_cache.g_perf_rtng_lookup'
5816 ,p_inst_name => 'ben_rt_prfl_cache.g_perf_rtng_instance'
5817 ,p_inst_set_type => 'ben_rt_prfl_cache.g_perf_rtng_inst_tbl'
5818 ,p_out_inst_name => 'ben_rt_prfl_cache.g_perf_rtng_out'
5819 );
5820 --
5821 p_inst_set := g_perf_rtng_out;
5822 p_inst_count := g_inst_count;
5823 --
5824 exception
5825 --
5826 when no_data_found then
5827 --
5828 p_inst_count := 0;
5829 hr_utility.set_location('No performance rating found', 90);
5830 hr_utility.set_location('Leaving : ' || l_proc, 99);
5831 --
5832 end;
5833
5834 ------------------------------------------------------------------------
5835 -- DELETE ALL CACHED DATA
5836 ------------------------------------------------------------------------
5837 procedure clear_down_cache
5838 is
5839 begin
5840 --
5841 g_pg_lookup.delete;
5842 g_pg_instance.delete;
5843 g_rl_lookup.delete;
5844 g_rl_instance.delete;
5845 g_tbco_lookup.delete;
5846 g_tbco_instance.delete;
5847 g_gndr_lookup.delete;
5848 g_gndr_instance.delete;
5849 g_brgng_lookup.delete;
5850 g_brgng_instance.delete;
5851 g_bnfgrp_lookup.delete;
5852 g_bnfgrp_instance.delete;
5853 g_eestat_lookup.delete;
5854 g_eestat_instance.delete;
5855 g_ftpt_lookup.delete;
5856 g_ftpt_instance.delete;
5857 g_grd_lookup.delete;
5858 g_grd_instance.delete;
5859 g_pctft_lookup.delete;
5860 g_pctft_instance.delete;
5861 g_hrswkd_lookup.delete;
5862 g_hrswkd_instance.delete;
5863 g_lbrmmbr_lookup.delete;
5864 g_lbrmmbr_instance.delete;
5865 g_lglenty_lookup.delete;
5866 g_lglenty_instance.delete;
5867 g_loa_lookup.delete;
5868 g_loa_instance.delete;
5869 g_org_lookup.delete;
5870 g_org_instance.delete;
5871 g_pertyp_lookup.delete;
5872 g_pertyp_instance.delete;
5873 g_ziprng_lookup.delete;
5874 g_ziprng_instance.delete;
5875 g_pyrl_lookup.delete;
5876 g_pyrl_instance.delete;
5877 g_py_bss_lookup.delete;
5878 g_py_bss_instance.delete;
5879 g_scdhrs_lookup.delete;
5880 g_scdhrs_instance.delete;
5881 g_wkloc_lookup.delete;
5882 g_wkloc_instance.delete;
5883 g_svcarea_lookup.delete;
5884 g_svcarea_instance.delete;
5885 g_hrlyslrd_lookup.delete;
5886 g_hrlyslrd_instance.delete;
5887 g_age_lookup.delete;
5888 g_age_instance.delete;
5889 g_complvl_lookup.delete;
5890 g_complvl_instance.delete;
5891 g_los_lookup.delete;
5892 g_los_instance.delete;
5893 g_age_los_lookup.delete;
5894 g_age_los_instance.delete;
5895
5896 g_job_lookup.delete;
5897 g_job_instance.delete;
5898
5899 g_optd_mdcr_lookup.delete;
5900 g_optd_mdcr_instance.delete;
5901
5902 g_lvg_rsn_lookup.delete;
5903 g_lvg_rsn_instance.delete;
5904
5905 g_cbr_qual_bnf_lookup.delete;
5906 g_cbr_qual_bnf_instance.delete;
5907
5908 g_qual_titl_lookup.delete;
5909 g_qual_titl_instance.delete;
5910
5911 g_cntng_prtn_prfl_lookup.delete;
5912 g_cntng_prtn_prfl_instance.delete;
5913
5914 g_pstn_lookup.delete;
5915 g_pstn_instance.delete;
5916
5917 g_comptncy_lookup.delete;
5918 g_comptncy_instance.delete;
5919 --
5920 g_no_othr_cvg_lookup.delete;
5921 g_no_othr_cvg_instance.delete;
5922
5923 g_dpnt_othr_ptip_lookup.delete;
5924 g_dpnt_othr_ptip_instance.delete;
5925
5926 g_enrld_anthr_pgm_lookup.delete;
5927 g_enrld_anthr_pgm_instance.delete;
5928
5929
5930 g_enrld_anthr_ptip_lookup.delete;
5931 g_enrld_anthr_ptip_instance.delete;
5932
5933 g_enrld_anthr_plip_lookup.delete;
5934 g_enrld_anthr_plip_instance.delete;
5935
5936 g_enrld_anthr_oipl_lookup.delete;
5937 g_enrld_anthr_oipl_instance.delete;
5938
5939 g_enrld_anthr_pl_lookup.delete;
5940 g_enrld_anthr_pl_instance.delete;
5941
5942 g_othr_ptip_lookup.delete;
5943 g_othr_ptip_instance.delete;
5944
5945 g_prtt_anthr_pl_lookup.delete;
5946 g_prtt_anthr_pl_instance.delete;
5947
5948 g_dpnt_cvrd_othr_pgm_lookup.delete;
5949 g_dpnt_cvrd_othr_pgm_instance.delete;
5950
5951 g_dpnt_cvrd_othr_ptip_lookup.delete;
5952 g_dpnt_cvrd_othr_ptip_instance.delete;
5953
5954 g_dpnt_cvrd_othr_plip_lookup.delete;
5955 g_dpnt_cvrd_othr_plip_instance.delete;
5956
5957 g_dpnt_cvrd_othr_pl_lookup.delete;
5958 g_dpnt_cvrd_othr_pl_instance.delete;
5959 --
5960
5961 g_qua_in_gr_lookup.delete;
5962 g_qua_in_gr_instance.delete;
5963
5964 g_perf_rtng_lookup.delete;
5965 g_perf_rtng_instance.delete;
5966
5967 --Bug 6412287
5968 g_poe_lookup.delete;
5969 g_poe_instance.delete;
5970 --End Bug 6412287
5971
5972 end clear_down_cache;
5973 --
5974 end ben_rt_prfl_cache;