[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_SUMMARY_PKG
Source
1 package body BEN_CWB_SUMMARY_PKG as
2 /* $Header: bencwbsm.pkb 120.15.12010000.2 2008/09/08 06:15:58 cakunuru ship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_summary_pkg.'; --Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 type g_summary_type is table of ben_cwb_summary%rowtype
12 index by binary_integer;
13 g_summary_rec g_summary_type;
14 --
15 -- Get the rate information for this person.
16 cursor csr_rates(v_per_in_ler_id number) is
17 select rt.group_pl_id group_pl_id
18 ,rt.group_oipl_id group_oipl_id
19 ,1 elig_count
20 ,decode(rt.ws_val,null,0,0,0,1) emp_recv_count
21 ,rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
22 ,xchg.xchg_rate,1) elig_sal_val
23 ,rt.ws_val/decode(pl.ws_nnmntry_uom,null
24 ,xchg.xchg_rate,1) ws_val
25 ,rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
26 ,xchg.xchg_rate,1) stat_sal_val
27 ,rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
28 ,xchg.xchg_rate,1) oth_comp_val
29 ,rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
30 ,xchg.xchg_rate,1) tot_comp_val
31 ,rt.rec_val/decode(pl.rec_nnmntry_uom,null
32 ,xchg.xchg_rate,1) rec_val
33 ,rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
34 ,xchg.xchg_rate,1) rec_mn_val
35 ,rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
36 ,xchg.xchg_rate,1) rec_mx_val
37 ,rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
38 ,xchg.xchg_rate,1) misc1_val
39 ,rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
40 ,xchg.xchg_rate,1) misc2_val
41 ,rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
42 ,xchg.xchg_rate,1) misc3_val
43 from ben_cwb_person_rates rt
44 ,ben_cwb_pl_dsgn pl
45 ,ben_cwb_xchg xchg
46 where rt.group_per_in_ler_id = v_per_in_ler_id
47 and rt.pl_id = pl.pl_id
48 and rt.oipl_id = pl.oipl_id
49 and rt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
50 and rt.elig_flag = 'Y'
51 and xchg.group_pl_id = rt.group_pl_id
52 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
53 and xchg.currency = rt.currency
54 order by rt.group_pl_id, rt.group_oipl_id;
55
56 -- Get the summary information for this person
57 cursor csr_summary(v_per_in_ler_id number) is
58 select group_pl_id group_pl_id
59 ,group_oipl_id group_oipl_id
60 ,sum(elig_count_all) elig_count
61 ,sum(emp_recv_count_all) emp_recv_count
62 ,sum(elig_sal_val_all) elig_sal_val_all
63 ,sum(ws_val_all) ws_val
64 ,sum(stat_sal_val_all) stat_sal_val
65 ,sum(oth_comp_val_all) oth_comp_val
66 ,sum(tot_comp_val_all) tot_comp_val
67 ,sum(rec_val_all) rec_val
68 ,sum(rec_mn_val_all) rec_mn_val
69 ,sum(rec_mx_val_all) rec_mx_val
70 ,sum(misc1_val_all) misc1_val
71 ,sum(misc2_val_all) misc2_val
72 ,sum(misc3_val_all) misc3_val
73 from ben_cwb_summary
74 where group_per_in_ler_id = v_per_in_ler_id
75 group by group_pl_id, group_oipl_id
76 order by group_pl_id, group_oipl_id;
77
78 -- While updating the old manager hierarchy, we need to go only until
79 -- the point where the new manager hierarchy meets. Similarly while
80 -- updating the new manager hierarchy, we just need to go up to the
81 -- point where the old mgr hierarchy meets.
82 cursor csr_mgr_ids(v_mgr_per_in_ler_id1 number
83 ,v_mgr_per_in_ler_id2 number) is
84 select mgr_per_in_ler_id
85 ,lvl_num
86 from ben_cwb_group_hrchy hrchy1
87 where emp_per_in_ler_id = v_mgr_per_in_ler_id1
88 and mgr_per_in_ler_id not in
89 (select mgr_per_in_ler_id
90 from ben_cwb_group_hrchy hrchy2
91 where emp_per_in_ler_id = v_mgr_per_in_ler_id2
92 and mgr_per_in_ler_id <> v_mgr_per_in_ler_id1
93 and lvl_num > 0)
94 order by lvl_num;
95 --
96 cursor csr_mgrs(v_per_in_ler_id in number) is
97 select mgr_per_in_ler_id
98 ,lvl_num
99 from ben_cwb_group_hrchy
100 where emp_per_in_ler_id = v_per_in_ler_id
101 and lvl_num > 0
102 order by lvl_num;
103
104 procedure check_refresh_jobs(p_group_pl_id in number
105 ,p_lf_evt_ocrd_dt in date
106 ,p_called_from_batch in varchar2 default 'N') is
107 cursor c_refresh is
108 select 'Y'
109 from ben_cwb_summary
110 where group_per_in_ler_id = -1
111 and group_pl_id = p_group_pl_id
112 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
113 and status = 'R'
114 and person_id = -1;
115 --
116 l_refresh varchar2(1);
117 --
118 begin
119 --
120 if fnd_global.conc_request_id = -1 then --7316806:check enforced only when the changes are done from worksheet (no conc. request is running)
121 open c_refresh;
122 fetch c_refresh into l_refresh;
123 close c_refresh;
124 --
125 if l_refresh = 'Y' then
126 fnd_message.set_name('BEN', 'BEN_94676_CWB_SUMM_RFRSH_RUNNG');
127 fnd_message.raise_error;
128 end if;
129 end if; -- conc-req check
130 --
131 end check_refresh_jobs;
132 --
133 procedure insert_refresh_job_marker(p_group_pl_id in number
134 ,p_lf_evt_ocrd_dt in date) is
135 pragma autonomous_transaction;
136 begin
137 --
138 insert into ben_cwb_summary
139 (summary_id
140 ,group_per_in_ler_id
141 ,group_pl_id
142 ,lf_evt_ocrd_dt
143 ,status
144 ,person_id)
145 values
146 (ben_cwb_summary_s.nextval
147 ,-1
148 ,p_group_pl_id
149 ,p_lf_evt_ocrd_dt
150 ,'R'
151 ,-1);
152 --
153 commit;
154 --
155 end insert_refresh_job_marker;
156 --
157 procedure delete_refresh_job_marker(p_group_pl_id in number
158 ,p_lf_evt_ocrd_dt in date) is
159 pragma autonomous_transaction;
160 begin
161 --
162 delete ben_cwb_summary
163 where group_per_in_ler_id = -1
164 and group_pl_id = p_group_pl_id
165 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
166 and status = 'R'
167 and person_id = -1;
168 --
169 commit;
170 --
171 end delete_refresh_job_marker;
172 --
173 -- --------------------------------------------------------------------------
174 -- |--------------------------< update_or_insert >---------------------------|
175 -- --------------------------------------------------------------------------
176 --
177 procedure update_or_insert (p_sum_rec in ben_cwb_summary%rowtype) is
178
179 -- select all the summary ids for this combination
180 cursor csr_summary_id is
181 select summary_id
182 from ben_cwb_summary
183 where group_per_in_ler_id = p_sum_rec.group_per_in_ler_id
184 and group_pl_id = p_sum_rec.group_pl_id
185 and group_oipl_id = p_sum_rec.group_oipl_id;
186 --
187 cursor c_pil is
188 select pil.person_id,
189 pil.lf_evt_ocrd_dt
190 from ben_per_in_ler pil
191 where pil.per_in_ler_id = p_sum_rec.group_per_in_ler_id;
192 --
193 l_dummy varchar2(1);
194 l_summary_id number;
195 l_found varchar2(1) := null;
196 l_status varchar2(30);
197 --
198 l_person_id number;
199 l_lf_evt_ocrd_dt date;
200 --
201 l_proc varchar2(72) := g_package||'update_or_insert';
202 --
203 begin
204 --
205 if g_debug then
206 hr_utility.set_location('Entering:'|| l_proc, 10);
207 end if;
208 --
209
210 --
211 l_person_id := p_sum_rec.person_id;
212 l_lf_evt_ocrd_dt := p_sum_rec.lf_evt_ocrd_dt;
213 --
214 -- If the pl/sql record does not have person_id or lf_evt_ocrd_dt,
215 -- then get it from the per_in_ler record.
216 --
217 if l_person_id is null or l_lf_evt_ocrd_dt is null then
218 --
219 open c_pil;
220 fetch c_pil into l_person_id, l_lf_evt_ocrd_dt;
221 close c_pil;
222 --
223 end if;
224 --
225
226 check_refresh_jobs(p_group_pl_id => p_sum_rec.group_pl_id
227 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt);
228 --
229 open csr_summary_id;
230 fetch csr_summary_id into l_summary_id;
231
232 if (csr_summary_id%notfound) then
233 --
234 if g_debug then
235 hr_utility.set_location(l_proc, 20);
236 end if;
237 --
238 -- No records are found. So set the flag to No records.
239 l_found := 'N'; -- no records;
240 else
241 --
242 if g_debug then
243 hr_utility.set_location(l_proc, 30);
244 end if;
245 -- try to get summary Id of one unlocked record
246 loop
247 begin
248 select null into l_dummy
249 from ben_cwb_summary
250 where summary_id = l_summary_id
251 for update nowait;
252
253 -- Found one unlocked record. So set the flag and exit from the loop
254 l_found :='F'; -- Found
255 exit;
256 exception
257 when hr_api.object_locked then
258 -- The present record is locked. So try for another
259 fetch csr_summary_id into l_summary_id;
260 if (csr_summary_id%notfound) then
261 -- All records are locked. So set the flag to Locked
262 -- exit from the loop
263 l_found := 'L'; -- Locked
264 exit;
265 end if;
266 end;
267 end loop;
268 --
269 end if;
270 close csr_summary_id;
271
272
273 if (l_found = 'F') then
274 --
275 if g_debug then
276 hr_utility.set_location(l_proc, 40);
277 end if;
278 --
279 -- Found one unlocked record. So update the summary.
280 update ben_cwb_summary summ
281 set elig_count_direct = nvl2(summ.elig_count_direct,
282 summ.elig_count_direct+
283 nvl(p_sum_rec.elig_count_direct,0),
284 p_sum_rec.elig_count_direct)
285 ,elig_count_all = nvl2(summ.elig_count_all,
286 summ.elig_count_all+
287 nvl(p_sum_rec.elig_count_all,0),
288 p_sum_rec.elig_count_all)
289 ,emp_recv_count_direct = nvl2(summ.emp_recv_count_direct,
290 summ.emp_recv_count_direct+
291 nvl(p_sum_rec.emp_recv_count_direct,0),
292 p_sum_rec.emp_recv_count_direct)
293 ,emp_recv_count_all = nvl2(summ.emp_recv_count_all,
294 summ.emp_recv_count_all+
295 nvl(p_sum_rec.emp_recv_count_all,0),
296 p_sum_rec.emp_recv_count_all)
297 ,elig_sal_val_direct = nvl2(summ.elig_sal_val_direct,
298 summ.elig_sal_val_direct+
299 nvl(p_sum_rec.elig_sal_val_direct,0),
300 p_sum_rec.elig_sal_val_direct)
301 ,elig_sal_val_all = nvl2(summ.elig_sal_val_all,
302 summ.elig_sal_val_all +
303 nvl(p_sum_rec.elig_sal_val_all,0),
304 p_sum_rec.elig_sal_val_all)
305 ,ws_val_direct = nvl2(summ.ws_val_direct,
306 summ.ws_val_direct +
307 nvl(p_sum_rec.ws_val_direct,0),
308 p_sum_rec.ws_val_direct)
309 ,ws_val_all = nvl2(summ.ws_val_all,
310 summ.ws_val_all +
311 nvl(p_sum_rec.ws_val_all,0),
312 p_sum_rec.ws_val_all)
313 ,ws_bdgt_val_direct = nvl2(summ.ws_bdgt_val_direct,
314 summ.ws_bdgt_val_direct +
315 nvl(p_sum_rec.ws_bdgt_val_direct,0),
316 p_sum_rec.ws_bdgt_val_direct)
317 ,ws_bdgt_val_all = nvl2(summ.ws_bdgt_val_all,
318 summ.ws_bdgt_val_all +
319 nvl(p_sum_rec.ws_bdgt_val_all,0),
320 p_sum_rec.ws_bdgt_val_all)
321 ,ws_bdgt_iss_val_direct = nvl2(summ.ws_bdgt_iss_val_direct,
322 summ.ws_bdgt_iss_val_direct +
323 nvl(p_sum_rec.ws_bdgt_iss_val_direct,0),
324 p_sum_rec.ws_bdgt_iss_val_direct)
325 ,ws_bdgt_iss_val_all = nvl2(summ.ws_bdgt_iss_val_all,
326 summ.ws_bdgt_iss_val_all +
327 nvl(p_sum_rec.ws_bdgt_iss_val_all,0),
328 p_sum_rec.ws_bdgt_iss_val_all)
329 ,bdgt_val_direct = nvl2(summ.bdgt_val_direct,
330 summ.bdgt_val_direct +
331 nvl(p_sum_rec.bdgt_val_direct,0),
332 p_sum_rec.bdgt_val_direct)
333 ,bdgt_iss_val_direct = nvl2(summ.bdgt_iss_val_direct,
334 summ.bdgt_iss_val_direct +
335 nvl(p_sum_rec.bdgt_iss_val_direct,0),
336 p_sum_rec.bdgt_iss_val_direct)
337 ,stat_sal_val_direct = nvl2(summ.stat_sal_val_direct,
338 summ.stat_sal_val_direct +
339 nvl(p_sum_rec.stat_sal_val_direct,0),
340 p_sum_rec.stat_sal_val_direct)
341 ,stat_sal_val_all = nvl2(summ.stat_sal_val_all,
342 summ.stat_sal_val_all +
343 nvl(p_sum_rec.stat_sal_val_all,0),
347 nvl(p_sum_rec.oth_comp_val_direct,0),
344 p_sum_rec.stat_sal_val_all)
345 ,oth_comp_val_direct = nvl2(summ.oth_comp_val_direct,
346 summ.oth_comp_val_direct +
348 p_sum_rec.oth_comp_val_direct)
349 ,oth_comp_val_all = nvl2(summ.oth_comp_val_all,
350 summ.oth_comp_val_all +
351 nvl(p_sum_rec.oth_comp_val_all,0),
352 p_sum_rec.oth_comp_val_all)
353 ,tot_comp_val_direct = nvl2(summ.tot_comp_val_direct,
354 summ.tot_comp_val_direct +
355 nvl(p_sum_rec.tot_comp_val_direct,0),
356 p_sum_rec.tot_comp_val_direct)
357 ,tot_comp_val_all = nvl2(summ.tot_comp_val_all,
358 summ.tot_comp_val_all +
359 nvl(p_sum_rec.tot_comp_val_all,0),
360 p_sum_rec.tot_comp_val_all)
361 ,rec_val_direct = nvl2(summ.rec_val_direct,
362 summ.rec_val_direct +
363 nvl(p_sum_rec.rec_val_direct,0),
364 p_sum_rec.rec_val_direct)
365 ,rec_val_all = nvl2(summ.rec_val_all,
366 summ.rec_val_all +
367 nvl(p_sum_rec.rec_val_all,0),
368 p_sum_rec.rec_val_all)
369 ,rec_mn_val_direct = nvl2(summ.rec_mn_val_direct,
370 summ.rec_mn_val_direct +
371 nvl(p_sum_rec.rec_mn_val_direct,0),
372 p_sum_rec.rec_mn_val_direct)
373 ,rec_mn_val_all = nvl2(summ.rec_mn_val_all,
374 summ.rec_mn_val_all +
375 nvl(p_sum_rec.rec_mn_val_all,0),
376 p_sum_rec.rec_mn_val_all)
377 ,rec_mx_val_direct = nvl2(summ.rec_mx_val_direct,
378 summ.rec_mx_val_direct +
379 nvl(p_sum_rec.rec_mx_val_direct,0),
380 p_sum_rec.rec_mx_val_direct)
381 ,rec_mx_val_all = nvl2(summ.rec_mx_val_all,
382 summ.rec_mx_val_all +
383 nvl(p_sum_rec.rec_mx_val_all,0),
384 p_sum_rec.rec_mx_val_all)
385 ,misc1_val_direct = nvl2(summ.misc1_val_direct,
386 summ.misc1_val_direct +
387 nvl(p_sum_rec.misc1_val_direct,0),
388 p_sum_rec.misc1_val_direct)
389 ,misc1_val_all = nvl2(summ.misc1_val_all,
390 summ.misc1_val_all +
391 nvl(p_sum_rec.misc1_val_all,0),
392 p_sum_rec.misc1_val_all)
393 ,misc2_val_direct = nvl2(summ.misc2_val_direct,
394 summ.misc2_val_direct +
395 nvl(p_sum_rec.misc2_val_direct,0),
396 p_sum_rec.misc2_val_direct)
397 ,misc2_val_all = nvl2(summ.misc2_val_all,
398 summ.misc2_val_all +
399 nvl(p_sum_rec.misc2_val_all,0),
400 p_sum_rec.misc2_val_all)
401 ,misc3_val_direct = nvl2(summ.misc3_val_direct,
402 summ.misc3_val_direct +
403 nvl(p_sum_rec.misc3_val_direct,0),
404 p_sum_rec.misc3_val_direct)
405 ,misc3_val_all = nvl2(summ.misc3_val_all,
406 summ.misc3_val_all +
407 nvl(p_sum_rec.misc3_val_all,0),
408 p_sum_rec.misc3_val_all)
409 where summ.summary_id = l_summary_id;
410 else
411 --
412 if g_debug then
413 hr_utility.set_location(l_proc, 50);
414 end if;
415 --
416 if (l_found = 'L') then
417 -- All the records are locked. So insert a 'P' record.
418 l_status := 'P';
419 else -- l_found = 'N'
420 -- No records are found. So this will be the first record.
421 l_status := null;
422 end if;
423
424 insert into ben_cwb_summary (
425 summary_id
426 ,group_per_in_ler_id
427 ,group_pl_id
428 ,group_oipl_id
429 ,status
430 ,elig_count_direct
431 ,elig_count_all
432 ,emp_recv_count_direct
433 ,emp_recv_count_all
434 ,elig_sal_val_direct
435 ,elig_sal_val_all
436 ,ws_val_direct
437 ,ws_val_all
438 ,ws_bdgt_val_direct
439 ,ws_bdgt_val_all
440 ,ws_bdgt_iss_val_direct
441 ,ws_bdgt_iss_val_all
442 ,bdgt_val_direct
443 ,bdgt_iss_val_direct
444 ,stat_sal_val_direct
445 ,stat_sal_val_all
446 ,oth_comp_val_direct
447 ,oth_comp_val_all
448 ,tot_comp_val_direct
449 ,tot_comp_val_all
450 ,rec_val_direct
451 ,rec_val_all
452 ,rec_mn_val_direct
453 ,rec_mn_val_all
457 ,misc1_val_all
454 ,rec_mx_val_direct
455 ,rec_mx_val_all
456 ,misc1_val_direct
458 ,misc2_val_direct
459 ,misc2_val_all
460 ,misc3_val_direct
461 ,misc3_val_all
462 ,person_id
463 ,lf_evt_ocrd_dt)
464 values (
465 ben_cwb_summary_s.nextval
466 ,p_sum_rec.group_per_in_ler_id
467 ,p_sum_rec.group_pl_id
468 ,p_sum_rec.group_oipl_id
469 ,l_status
470 ,p_sum_rec.elig_count_direct
471 ,p_sum_rec.elig_count_all
472 ,p_sum_rec.emp_recv_count_direct
473 ,p_sum_rec.emp_recv_count_all
474 ,p_sum_rec.elig_sal_val_direct
475 ,p_sum_rec.elig_sal_val_all
476 ,p_sum_rec.ws_val_direct
477 ,p_sum_rec.ws_val_all
478 ,p_sum_rec.ws_bdgt_val_direct
479 ,p_sum_rec.ws_bdgt_val_all
480 ,p_sum_rec.ws_bdgt_iss_val_direct
481 ,p_sum_rec.ws_bdgt_iss_val_all
482 ,p_sum_rec.bdgt_val_direct
483 ,p_sum_rec.bdgt_iss_val_direct
484 ,p_sum_rec.stat_sal_val_direct
485 ,p_sum_rec.stat_sal_val_all
486 ,p_sum_rec.oth_comp_val_direct
487 ,p_sum_rec.oth_comp_val_all
488 ,p_sum_rec.tot_comp_val_direct
489 ,p_sum_rec.tot_comp_val_all
490 ,p_sum_rec.rec_val_direct
491 ,p_sum_rec.rec_val_all
492 ,p_sum_rec.rec_mn_val_direct
493 ,p_sum_rec.rec_mn_val_all
494 ,p_sum_rec.rec_mx_val_direct
495 ,p_sum_rec.rec_mx_val_all
496 ,p_sum_rec.misc1_val_direct
497 ,p_sum_rec.misc1_val_all
498 ,p_sum_rec.misc2_val_direct
499 ,p_sum_rec.misc2_val_all
500 ,p_sum_rec.misc3_val_direct
501 ,p_sum_rec.misc3_val_all
502 ,l_person_id
503 ,l_lf_evt_ocrd_dt
504 );
505 end if;
506 --
507 if g_debug then
508 hr_utility.set_location(' Leaving:'|| l_proc, 99);
509 end if;
510 --
511 end; -- of procedure update_or_insert
512 --
513 -- --------------------------------------------------------------------------
514 -- |----------------------< consolidate_summary_rec >------------------------|
515 -- --------------------------------------------------------------------------
516 procedure consolidate_summary_rec(p_person_id in number) is
517
518 -- get the records with status P
519 cursor csr_pending_recs(p_person_id number) is
520 select summ.*
521 from ben_cwb_summary summ
522 where summ.person_id = p_person_id
523 and summ.status = 'P'
524 order by summ.group_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
525 --
526 l_sum_rec ben_cwb_summary%rowtype;
527 --
528 l_proc varchar2(72) := g_package||'consolidate_summary_rec';
529 --
530 begin
531 --
532 if g_debug then
533 hr_utility.set_location('Entering:'|| l_proc, 10);
534 end if;
535 --
536 -- for each pending record
537 for pending_rec in csr_pending_recs(p_person_id)
538 loop
539 --
540 if g_debug then
541 hr_utility.set_location(l_proc, 20);
542 end if;
543 --
544 -- These will be executed only for the first iteration of the loop
545 if l_sum_rec.group_per_in_ler_id is null then
546 --
547 if g_debug then
548 hr_utility.set_location(l_proc, 30);
549 end if;
550 --
551 l_sum_rec.group_per_in_ler_id := pending_rec.group_per_in_ler_id;
552 l_sum_rec.group_pl_id := pending_rec.group_pl_id;
553 l_sum_rec.group_oipl_id := pending_rec.group_oipl_id;
554 l_sum_rec.person_id := pending_rec.person_id;
555 end if;
556
557 -- check if group_per_in_ler_id, group_pl_id or group_oipl_id changes
558 if ( l_sum_rec.group_per_in_ler_id <> pending_rec.group_per_in_ler_id
559 or l_sum_rec.group_pl_id <> pending_rec.group_pl_id
560 or l_sum_rec.group_oipl_id <>
561 pending_rec.group_oipl_id) then
562 --
563 if g_debug then
564 hr_utility.set_location(l_proc, 40);
565 end if;
566 --
567 -- combination changed. So call update_or_insert
568 -- This procedure tries to update the main rec, if it is already
569 -- locked then it will insert a new record with status "P".
570 update_or_insert (l_sum_rec);
571
572 -- Now clear the l_sum_rec to store the next combination.
573 l_sum_rec.group_per_in_ler_id :=pending_rec.group_per_in_ler_id;
574 l_sum_rec.group_pl_id :=pending_rec.group_pl_id;
575 l_sum_rec.group_oipl_id :=pending_rec.group_oipl_id;
576 l_sum_rec.person_id := pending_rec.person_id;
577 l_sum_rec.status :=null;
578 l_sum_rec.elig_count_direct :=null;
579 l_sum_rec.elig_count_all :=null;
580 l_sum_rec.emp_recv_count_direct :=null;
581 l_sum_rec.emp_recv_count_all :=null;
582 l_sum_rec.elig_sal_val_direct :=null;
583 l_sum_rec.elig_sal_val_all :=null;
587 l_sum_rec.ws_bdgt_val_all :=null;
584 l_sum_rec.ws_val_direct :=null;
585 l_sum_rec.ws_val_all :=null;
586 l_sum_rec.ws_bdgt_val_direct :=null;
588 l_sum_rec.ws_bdgt_iss_val_direct :=null;
589 l_sum_rec.ws_bdgt_iss_val_all :=null;
590 l_sum_rec.bdgt_val_direct :=null;
591 l_sum_rec.bdgt_iss_val_direct :=null;
592 l_sum_rec.stat_sal_val_direct :=null;
593 l_sum_rec.stat_sal_val_all :=null;
594 l_sum_rec.oth_comp_val_direct :=null;
595 l_sum_rec.oth_comp_val_all :=null;
596 l_sum_rec.tot_comp_val_direct :=null;
597 l_sum_rec.tot_comp_val_all :=null;
598 l_sum_rec.rec_val_direct :=null;
599 l_sum_rec.rec_val_all :=null;
600 l_sum_rec.rec_mn_val_direct :=null;
601 l_sum_rec.rec_mn_val_all :=null;
602 l_sum_rec.rec_mx_val_direct :=null;
603 l_sum_rec.rec_mx_val_all :=null;
604 l_sum_rec.misc1_val_direct :=null;
605 l_sum_rec.misc1_val_all :=null;
606 l_sum_rec.misc2_val_direct :=null;
607 l_sum_rec.misc2_val_all :=null;
608 l_sum_rec.misc3_val_direct :=null;
609 l_sum_rec.misc3_val_all :=null;
610 end if; -- if change in combination
611 --
612 if g_debug then
613 hr_utility.set_location(l_proc, 50);
614 end if;
615 --
616 --Now, add the values
617 l_sum_rec.elig_count_direct := ben_cwb_utils.add_number_with_null_check
618 (l_sum_rec.elig_count_direct,pending_rec.elig_count_direct);
619 l_sum_rec.elig_count_all := ben_cwb_utils.add_number_with_null_check
620 (l_sum_rec.elig_count_all,pending_rec.elig_count_all);
621 l_sum_rec.emp_recv_count_direct := ben_cwb_utils.add_number_with_null_check
622 (l_sum_rec.emp_recv_count_direct,pending_rec.emp_recv_count_direct);
623 l_sum_rec.emp_recv_count_all :=ben_cwb_utils.add_number_with_null_check
624 (l_sum_rec.emp_recv_count_all,pending_rec.emp_recv_count_all);
625 l_sum_rec.elig_sal_val_direct :=ben_cwb_utils.add_number_with_null_check
626 (l_sum_rec.elig_sal_val_direct,pending_rec.elig_sal_val_direct);
627 l_sum_rec.elig_sal_val_all := ben_cwb_utils.add_number_with_null_check
628 (l_sum_rec.elig_sal_val_all,pending_rec.elig_sal_val_all);
629 l_sum_rec.ws_val_direct :=ben_cwb_utils.add_number_with_null_check
630 (l_sum_rec.ws_val_direct,pending_rec.ws_val_direct);
631 l_sum_rec.ws_val_all :=ben_cwb_utils.add_number_with_null_check
632 (l_sum_rec.ws_val_all,pending_rec.ws_val_all);
633 l_sum_rec.ws_bdgt_val_direct :=ben_cwb_utils.add_number_with_null_check
634 (l_sum_rec.ws_bdgt_val_direct,pending_rec.ws_bdgt_val_direct);
635 l_sum_rec.ws_bdgt_val_all :=ben_cwb_utils.add_number_with_null_check
636 (l_sum_rec.ws_bdgt_val_all,pending_rec.ws_bdgt_val_all);
637 l_sum_rec.ws_bdgt_iss_val_direct := ben_cwb_utils.add_number_with_null_check
638 (l_sum_rec.ws_bdgt_iss_val_direct,pending_rec.ws_bdgt_iss_val_direct);
639 l_sum_rec.ws_bdgt_iss_val_all :=ben_cwb_utils.add_number_with_null_check
640 (l_sum_rec.ws_bdgt_iss_val_all,pending_rec.ws_bdgt_iss_val_all);
641 l_sum_rec.bdgt_val_direct :=ben_cwb_utils.add_number_with_null_check
642 (l_sum_rec.bdgt_val_direct,pending_rec.bdgt_val_direct);
643 l_sum_rec.bdgt_iss_val_direct :=ben_cwb_utils.add_number_with_null_check
644 (l_sum_rec.bdgt_iss_val_direct,pending_rec.bdgt_iss_val_direct);
645 l_sum_rec.stat_sal_val_direct :=ben_cwb_utils.add_number_with_null_check
646 (l_sum_rec.stat_sal_val_direct,pending_rec.stat_sal_val_direct);
647 l_sum_rec.stat_sal_val_all :=ben_cwb_utils.add_number_with_null_check
648 (l_sum_rec.stat_sal_val_all,pending_rec.stat_sal_val_all);
649 l_sum_rec.oth_comp_val_direct :=ben_cwb_utils.add_number_with_null_check
650 (l_sum_rec.oth_comp_val_direct,pending_rec.oth_comp_val_direct);
651 l_sum_rec.oth_comp_val_all :=ben_cwb_utils.add_number_with_null_check
652 (l_sum_rec.oth_comp_val_all,pending_rec.oth_comp_val_all);
653 l_sum_rec.tot_comp_val_direct :=ben_cwb_utils.add_number_with_null_check
654 (l_sum_rec.tot_comp_val_direct,pending_rec.tot_comp_val_direct);
655 l_sum_rec.tot_comp_val_all :=ben_cwb_utils.add_number_with_null_check
656 (l_sum_rec.tot_comp_val_all,pending_rec.tot_comp_val_all);
657 l_sum_rec.rec_val_direct :=ben_cwb_utils.add_number_with_null_check
658 (l_sum_rec.rec_val_direct,pending_rec.rec_val_direct);
659 l_sum_rec.rec_val_all :=ben_cwb_utils.add_number_with_null_check
660 (l_sum_rec.rec_val_all,pending_rec.rec_val_all);
661 l_sum_rec.rec_mn_val_direct :=ben_cwb_utils.add_number_with_null_check
662 (l_sum_rec.rec_mn_val_direct,pending_rec.rec_mn_val_direct);
663 l_sum_rec.rec_mn_val_all :=ben_cwb_utils.add_number_with_null_check
664 (l_sum_rec.rec_mn_val_all,pending_rec.rec_mn_val_all);
665 l_sum_rec.rec_mx_val_direct :=ben_cwb_utils.add_number_with_null_check
666 (l_sum_rec.rec_mx_val_direct,pending_rec.rec_mx_val_direct);
667 l_sum_rec.rec_mx_val_all :=ben_cwb_utils.add_number_with_null_check
668 (l_sum_rec.rec_mx_val_all,pending_rec.rec_mx_val_all);
669 l_sum_rec.misc1_val_direct :=ben_cwb_utils.add_number_with_null_check
670 (l_sum_rec.misc1_val_direct,pending_rec.misc1_val_direct);
671 l_sum_rec.misc1_val_all :=ben_cwb_utils.add_number_with_null_check
675 l_sum_rec.misc2_val_all :=ben_cwb_utils.add_number_with_null_check
672 (l_sum_rec.misc1_val_all,pending_rec.misc1_val_all);
673 l_sum_rec.misc2_val_direct :=ben_cwb_utils.add_number_with_null_check
674 (l_sum_rec.misc2_val_direct,pending_rec.misc2_val_direct);
676 (l_sum_rec.misc2_val_all,pending_rec.misc2_val_all);
677 l_sum_rec.misc3_val_direct :=ben_cwb_utils.add_number_with_null_check
678 (l_sum_rec.misc3_val_direct,pending_rec.misc3_val_direct);
679 l_sum_rec.misc3_val_all :=ben_cwb_utils.add_number_with_null_check
680 (l_sum_rec.misc3_val_all,pending_rec.misc3_val_all);
681
682 -- Now delete the record from ben_cwb_summary as it is already
683 -- consolidated
684 delete from ben_cwb_summary
685 where summary_id = pending_rec.summary_id;
686 end loop; -- of get_pening_recs
687 --
688 if g_debug then
689 hr_utility.set_location(l_proc, 60);
690 end if;
691 --
692 -- if the for loop fetches atleast one record then the last summary record
693 -- values will not get updated in ben_cwb_summary. So call
694 -- update_or_insert again to update the values
695
696 if (l_sum_rec.group_per_in_ler_id is not null) then
697 --
698 if g_debug then
699 hr_utility.set_location(l_proc, 50);
700 end if;
701 --
702 update_or_insert(l_sum_rec);
703 end if;
704 --
705 if g_debug then
706 hr_utility.set_location(' Leaving:'|| l_proc, 99);
707 end if;
708 --
709 end; -- end of procedure consolidate_summary_rec
710 --
711 -- --------------------------------------------------------------------------
712 -- |--------------------< consolidate_summary_rec_all >----------------------|
713 -- --------------------------------------------------------------------------
714 --
715 procedure consolidate_summary_rec_all is
716 --
717 -- cursor to get the person ids who have split rows in
718 -- summary table
719 cursor csr_get_person_ids is
720 select distinct(person_id) person_id
721 from ben_cwb_summary
722 where status = 'P';
723 --
724 l_proc varchar2(72) := g_package||'consolidate_summary_rec_all';
725 --
726 begin
727 --
728 if g_debug then
729 hr_utility.set_location('Entering:'|| l_proc, 10);
730 end if;
731 --
732 -- for each person from csr_get_per_id
733 for person in csr_get_person_ids
734 loop
735 --
736 if g_debug then
737 hr_utility.set_location(l_proc, 20);
738 end if;
739 --
740 -- call the process that consolidates the summary records
741 -- for this person
742 consolidate_summary_rec(p_person_id => person.person_id);
743 end loop;
744 --
745 if g_debug then
746 hr_utility.set_location(' Leaving:'|| l_proc, 99);
747 end if;
748 --
749 end;
750 --
751 -- --------------------------------------------------------------------------
752 -- |------------------------< update_person_info >--------------------------|
753 -- --------------------------------------------------------------------------
754 --
755 -- Internal procedure to update the correct person_id in person_info record.
756 --
757 procedure update_person_info(p_group_pl_id in number
758 ,p_lf_evt_ocrd_dt in date) is
759 -- cursor to fetch the person_id from ben_per_in_ler for
760 -- group_per_in_ler_ids with -1 as person_id in ben_cwb_person_info
761 cursor csr_person_ids is
762 select pi.group_per_in_ler_id, pil.person_id
763 from ben_cwb_person_info pi
764 ,ben_per_in_ler pil
765 where pi.person_id = -1
766 and pi.group_pl_id = p_group_pl_id
767 and pi.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
768 and pi.group_per_in_ler_id = pil.per_in_ler_id
769 and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
770 --
771 type group_per_in_ler_id_type is table of
772 ben_cwb_person_info.group_per_in_ler_id%type;
773 type person_id_type is table of
774 ben_per_in_ler.person_id%type;
775 -- declare pl/sql tables
776 l_group_per_in_ler_id_tab group_per_in_ler_id_type;
777 l_person_id_tab person_id_type;
778 --
779 l_proc varchar2(72) :=g_package||'update_person_info';
780 --
781 begin
782 --
783 if g_debug then
784 hr_utility.set_location('Entering:'|| l_proc, 10);
785 end if;
786 --
787 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
788 'update_person_info';
789 ben_manage_cwb_life_events.g_error_log_rec.step_number := 77;
790 --
791 -- Set the person id in ben_cwb_person_info to correct values from -1.
792 open csr_person_ids;
793 fetch csr_person_ids bulk collect into l_group_per_in_ler_id_tab
794 ,l_person_id_tab;
795 close csr_person_ids;
796
797 if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
798 forall i in l_group_per_in_ler_id_tab.first..
799 l_group_per_in_ler_id_tab.last
800 update ben_cwb_person_info
801 set person_id = l_person_id_tab(i)
802 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i);
803
807 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
804 --
805 -- Run Dynamic Calculations.
806 --
808 'run_dynamic_calc';
809 ben_manage_cwb_life_events.g_error_log_rec.step_number := 78;
810 --
811 for i in l_group_per_in_ler_id_tab.first..
812 l_group_per_in_ler_id_tab.last loop
813 ben_cwb_dyn_calc_pkg.run_dynamic_calculations(
814 p_group_per_in_ler_id => l_group_per_in_ler_id_tab(i)
815 ,p_group_pl_id => p_group_pl_id
816 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
817 end loop;
818
819 end if;
820 --
821 l_group_per_in_ler_id_tab.delete;
822 l_person_id_tab.delete;
823 --
824 if g_debug then
825 hr_utility.set_location(' Leaving:'|| l_proc, 999);
826 end if;
827 --
828 end update_person_info;
829 --
830 -- --------------------------------------------------------------------------
831 -- |-----------------------< compute_bdgts_and_all >-------------------------|
832 -- --------------------------------------------------------------------------
833 --
834 -- Internal procedure for computing bdgts and _all inforation
835 --
836 procedure compute_bdgts_and_all(p_group_pl_id in number
837 ,p_lf_evt_ocrd_dt in date) is
838
839 -- cursor to get the ws bdgt info if it is stored in %
840 cursor csr_directs_ws_bdgt_in_percnt(p_group_pl_id number
841 ,p_lf_evt_ocrd_dt date) is
842 select hrchy.mgr_per_in_ler_id
843 ,grp.group_pl_id
844 ,grp.group_oipl_id,
845 sum(grp.ws_bdgt_val * summ.elig_sal_val_direct/100)
846 ws_bdgt_val_direct
847 ,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
848 ws_bdgt_iss_val_direct
849 from ben_cwb_group_hrchy hrchy
850 ,ben_cwb_person_groups grp
851 ,ben_cwb_person_info info
852 ,ben_cwb_summary summ
853 where info.group_pl_id = p_group_pl_id
854 and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
855 and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
856 and hrchy.lvl_num = 1
857 and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
858 and grp.group_per_in_ler_id = summ.group_per_in_ler_id
859 and grp.group_pl_id = summ.group_pl_id
860 and grp.group_oipl_id = summ.group_oipl_id
861 group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
862
863 -- cursor to get the db bdgt and ws bdgt info, if it is stored in amount
864 cursor csr_directs_bdgt_in_amt(p_group_pl_id number
865 ,p_lf_evt_ocrd_dt date) is
866 select hrchy.mgr_per_in_ler_id
867 ,grp.group_pl_id
868 ,grp.group_oipl_id
869 ,sum(grp.ws_bdgt_val) ws_bdgt_val_direct
870 ,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_direct
871 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
872 grp.dist_bdgt_val)) bdgt_val_direct
873 ,sum(decode(nvl(grp.dist_bdgt_val,0),0, grp.ws_bdgt_iss_val,
874 grp.dist_bdgt_iss_val)) bdgt_iss_val_direct
875 from ben_cwb_group_hrchy hrchy
876 ,ben_cwb_person_groups grp
877 ,ben_cwb_person_info info
878 where info.group_pl_id = p_group_pl_id
879 and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
880 and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
881 and hrchy.lvl_num = 1
882 and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
883 group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
884
885 -- cursor to get the _all info.
886 cursor csr_all_info(p_group_pl_id number
887 ,p_lf_evt_ocrd_dt date) is
888 select hrchy.mgr_per_in_ler_id
889 ,summ.group_pl_id, summ.group_oipl_id
890 ,sum(elig_count_direct) elig_count_all
891 ,sum(emp_recv_count_direct) emp_recv_count_all
892 ,sum(elig_sal_val_direct) elig_sal_val_all
893 ,sum(ws_val_direct) ws_val_all
894 ,sum(ws_bdgt_val_direct)ws_bdgt_val_all
895 ,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val_all
896 ,sum(stat_sal_val_direct) stat_sal_val_all
897 ,sum(oth_comp_val_direct) oth_comp_val_all
898 ,sum(tot_comp_val_direct) tot_comp_val_all
899 ,sum(rec_val_direct) rec_val_all
900 ,sum(rec_mn_val_direct) rec_mn_val_all
901 ,sum(rec_mx_val_direct) rec_mx_val_all
902 ,sum(misc1_val_direct) misc1_val_all
903 ,sum(misc2_val_direct) misc2_val_all
904 ,sum(misc3_val_direct) misc3_val_all
905 from ben_cwb_group_hrchy hrchy
906 ,ben_cwb_summary summ
907 ,ben_cwb_person_info info
908 where info.group_pl_id = p_group_pl_id
909 and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
910 and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
911 and hrchy.lvl_num >=0
912 and hrchy.emp_per_in_ler_id = summ.group_per_in_ler_id
913 group by hrchy.mgr_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
914
915 -- cursor to get the bdgt info if it stored in %
916 cursor csr_directs_bdgt_in_prcnt(p_group_pl_id number
917 ,p_lf_evt_ocrd_dt date) is
918 select hrchy.mgr_per_in_ler_id
919 ,grp.group_pl_id
920 ,grp.group_oipl_id
921 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
922 grp.dist_bdgt_val)*
923 decode(nvl(grp.dist_bdgt_val,0),0,summ.elig_sal_val_direct,
927 summ.elig_sal_val_direct,summ.elig_sal_val_all) /100)
924 summ.elig_sal_val_all) /100) bdgt_val_direct
925 ,sum(decode(nvl(grp.dist_bdgt_val,0),0, grp.ws_bdgt_iss_val,
926 grp.dist_bdgt_iss_val)* decode(nvl(grp.dist_bdgt_val,0),0,
928 bdgt_iss_val_direct
929 from ben_cwb_group_hrchy hrchy
930 ,ben_cwb_person_groups grp
931 ,ben_cwb_summary summ
932 ,ben_cwb_person_info info
933 where info.group_pl_id = p_group_pl_id
934 and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
935 and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
936 and hrchy.lvl_num = 1
937 and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
938 and grp.group_per_in_ler_id = summ.group_per_in_ler_id
939 and grp.group_pl_id = summ.group_pl_id
940 and grp.group_oipl_id = summ.group_oipl_id
941 group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
942
943 -- Type delcarations for pl/sql tables
944 type group_per_in_ler_id_type is table of
945 ben_cwb_summary.group_per_in_ler_id%type;
946 type group_pl_id_type is table of
947 ben_cwb_summary.group_pl_id%type;
948 type group_oipl_id_type is table of
949 ben_cwb_summary.group_oipl_id%type;
950 type elig_count_all_type is table of
951 ben_cwb_summary.elig_count_all%type;
952 type emp_recv_count_all_type is table of
953 ben_cwb_summary. emp_recv_count_all%type;
954 type elig_sal_val_all_type is table of
955 ben_cwb_summary.elig_sal_val_all%type;
956 type ws_val_all_type is table of
957 ben_cwb_summary.ws_val_all%type;
958 type ws_bdgt_val_direct_type is table of
959 ben_cwb_summary.ws_bdgt_val_direct%type;
960 type ws_bdgt_val_all_type is table of
961 ben_cwb_summary.ws_bdgt_val_all%type;
962 type ws_bdgt_iss_val_direct_type is table of
963 ben_cwb_summary.ws_bdgt_iss_val_direct%type;
964 type ws_bdgt_iss_val_all_type is table of
965 ben_cwb_summary.ws_bdgt_iss_val_all%type;
966 type bdgt_val_direct_type is table of
967 ben_cwb_summary.bdgt_val_direct%type;
968 type bdgt_iss_val_direct_type is table of
969 ben_cwb_summary.bdgt_iss_val_direct%type;
970 type stat_sal_val_all_type is table of
971 ben_cwb_summary.stat_sal_val_all%type;
972 type oth_comp_val_all_type is table of
973 ben_cwb_summary.oth_comp_val_all%type;
974 type tot_comp_val_all_type is table of
975 ben_cwb_summary.tot_comp_val_all%type;
976 type rec_val_all_type is table of
977 ben_cwb_summary.rec_val_all%type;
978 type rec_mn_val_all_type is table of
979 ben_cwb_summary.rec_mn_val_all%type;
980 type rec_mx_val_all_type is table of
981 ben_cwb_summary.rec_mx_val_all%type;
982 type misc1_val_all_type is table of
983 ben_cwb_summary.misc1_val_all%type;
984 type misc2_val_all_type is table of
985 ben_cwb_summary.misc2_val_all%type;
986 type misc3_val_all_type is table of
987 ben_cwb_summary.misc3_val_all%type;
988 type person_id_type is table of
989 ben_cwb_person_info.person_id%type;
990 --
991 -- declare pl/sql tables
992 l_group_per_in_ler_id_tab group_per_in_ler_id_type;
993 l_group_pl_id_tab group_pl_id_type;
994 l_group_oipl_id_tab group_oipl_id_type;
995 l_elig_count_all_tab elig_count_all_type;
996 l_emp_recv_count_all_tab emp_recv_count_all_type;
997 l_elig_sal_val_all_tab elig_sal_val_all_type;
998 l_ws_val_all_tab ws_val_all_type;
999 l_ws_bdgt_val_direct_tab ws_bdgt_val_direct_type;
1000 l_ws_bdgt_val_all_tab ws_bdgt_val_all_type;
1001 l_ws_bdgt_iss_val_direct_tab ws_bdgt_iss_val_direct_type;
1002 l_ws_bdgt_iss_val_all_tab ws_bdgt_iss_val_all_type;
1003 l_bdgt_val_direct_tab bdgt_val_direct_type;
1004 l_bdgt_iss_val_direct_tab bdgt_iss_val_direct_type;
1005 l_stat_sal_val_all_tab stat_sal_val_all_type;
1006 l_oth_comp_val_all_tab oth_comp_val_all_type;
1007 l_tot_comp_val_all_tab tot_comp_val_all_type;
1008 l_rec_val_all_tab rec_val_all_type;
1009 l_rec_mn_val_all_tab rec_mn_val_all_type;
1010 l_rec_mx_val_all_tab rec_mx_val_all_type;
1011 l_misc1_val_all_tab misc1_val_all_type;
1012 l_misc2_val_all_tab misc2_val_all_type;
1013 l_misc3_val_all_tab misc3_val_all_type;
1014 l_person_id_tab person_id_type;
1015 --
1016 l_prsrv_bdgt_cd varchar2(30);
1017 l_uses_bdgt_flag varchar2(30);
1018 --
1019 l_proc varchar2(72) :=g_package||'compute_bdgts_and_all';
1020 --
1021 begin
1022 --
1023 if g_debug then
1024 hr_utility.set_location('Entering:'|| l_proc, 10);
1025 end if;
1026 --
1027 -- get the prsrv_bdgt_cd from pl_dsgn
1028 select prsrv_bdgt_cd, uses_bdgt_flag
1029 into l_prsrv_bdgt_cd, l_uses_bdgt_flag
1030 from ben_cwb_pl_dsgn
1031 where pl_id = p_group_pl_id
1032 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1033 and oipl_id = -1;
1034 --
1035 if g_debug then
1036 hr_utility.set_location(l_proc, 20);
1037 end if;
1038 --
1039 if l_uses_bdgt_flag = 'Y' then
1040 -- process the budgets summary
1041 --
1042 -- If the Preserve Budget Code is 'P' then compute ws_bdgt_val_direct
1043 -- and ws_bdgt_iss_val_direct.
1044 if l_prsrv_bdgt_cd = 'P' then
1045 --
1046 if g_debug then
1047 hr_utility.set_location(l_proc, 30);
1048 end if;
1052 fetch csr_directs_ws_bdgt_in_percnt bulk collect into
1049 --
1050 -- bulk collect the data
1051 open csr_directs_ws_bdgt_in_percnt(p_group_pl_id, p_lf_evt_ocrd_dt);
1053 l_group_per_in_ler_id_tab
1054 ,l_group_pl_id_tab
1055 ,l_group_oipl_id_tab
1056 ,l_ws_bdgt_val_direct_tab
1057 ,l_ws_bdgt_iss_val_direct_tab;
1058 close csr_directs_ws_bdgt_in_percnt;
1059 --
1060 if g_debug then
1061 hr_utility.set_location(l_proc, 40);
1062 end if;
1063 --
1064 if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1065 -- bulk update the table
1066 forall i in l_group_per_in_ler_id_tab.first..
1067 l_group_per_in_ler_id_tab.last
1068 update ben_cwb_summary
1069 set ws_bdgt_val_direct = l_ws_bdgt_val_direct_tab(i)
1070 ,ws_bdgt_iss_val_direct = l_ws_bdgt_iss_val_direct_tab(i)
1071 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
1072 and group_pl_id = l_group_pl_id_tab(i)
1073 and group_oipl_id = l_group_oipl_id_tab(i);
1074 end if;
1075 --
1076 if g_debug then
1077 hr_utility.set_location(l_proc, 50);
1078 end if;
1079 --
1080 l_group_per_in_ler_id_tab.delete;
1081 l_group_pl_id_tab.delete;
1082 l_group_oipl_id_tab.delete;
1083 l_ws_bdgt_val_direct_tab.delete;
1084 l_ws_bdgt_iss_val_direct_tab.delete;
1085
1086 else
1087 --
1088 if g_debug then
1089 hr_utility.set_location(l_proc, 60);
1090 end if;
1091 --
1092 -- the code is 'A', compute bdgt_val_direct, bdgt_iss_va_direct
1093 -- ws_bdgt_val_direct, ws_bdgt_iss_val_direct
1094 open csr_directs_bdgt_in_amt(p_group_pl_id, p_lf_evt_ocrd_dt);
1095 fetch csr_directs_bdgt_in_amt bulk collect into
1096 l_group_per_in_ler_id_tab
1097 ,l_group_pl_id_tab
1098 ,l_group_oipl_id_tab
1099 ,l_ws_bdgt_val_direct_tab
1100 ,l_ws_bdgt_iss_val_direct_tab
1101 ,l_bdgt_val_direct_tab
1102 ,l_bdgt_iss_val_direct_tab;
1103 close csr_directs_bdgt_in_amt;
1104 --
1105 if g_debug then
1106 hr_utility.set_location(l_proc, 70);
1107 end if;
1108 --
1109 if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1110 -- bulk update the table
1111 forall i in l_group_per_in_ler_id_tab.first..
1112 l_group_per_in_ler_id_tab.last
1113 update ben_cwb_summary
1114 set ws_bdgt_val_direct = l_ws_bdgt_val_direct_tab(i)
1115 ,ws_bdgt_iss_val_direct = l_ws_bdgt_iss_val_direct_tab(i)
1116 ,bdgt_val_direct = l_bdgt_val_direct_tab(i)
1117 ,bdgt_iss_val_direct = l_bdgt_iss_val_direct_tab(i)
1118 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
1119 and group_pl_id = l_group_pl_id_tab(i)
1120 and group_oipl_id = l_group_oipl_id_tab(i);
1121 end if;
1122 --
1123 if g_debug then
1124 hr_utility.set_location(l_proc, 80);
1125 end if;
1126 --
1127 l_group_per_in_ler_id_tab.delete;
1128 l_group_pl_id_tab.delete;
1129 l_group_oipl_id_tab.delete;
1130 l_ws_bdgt_val_direct_tab.delete;
1131 l_ws_bdgt_iss_val_direct_tab.delete;
1132 l_bdgt_val_direct_tab.delete;
1133 l_bdgt_iss_val_direct_tab.delete;
1134 end if; -- of prsrv_bdgt_cd
1135 end if; -- of uses_bdgts_flag
1136 --
1137 if g_debug then
1138 hr_utility.set_location(l_proc, 90);
1139 end if;
1140
1141 -- Now compute the _all information
1142 open csr_all_info(p_group_pl_id
1143 ,p_lf_evt_ocrd_dt);
1144 fetch csr_all_info bulk collect into l_group_per_in_ler_id_tab
1145 ,l_group_pl_id_tab
1146 ,l_group_oipl_id_tab
1147 ,l_elig_count_all_tab
1148 ,l_emp_recv_count_all_tab
1149 ,l_elig_sal_val_all_tab
1150 ,l_ws_val_all_tab
1151 ,l_ws_bdgt_val_all_tab
1152 ,l_ws_bdgt_iss_val_all_tab
1153 ,l_stat_sal_val_all_tab
1154 ,l_oth_comp_val_all_tab
1155 ,l_tot_comp_val_all_tab
1156 ,l_rec_val_all_tab
1157 ,l_rec_mn_val_all_tab
1158 ,l_rec_mx_val_all_tab
1159 ,l_misc1_val_all_tab
1160 ,l_misc2_val_all_tab
1161 ,l_misc3_val_all_tab;
1162 close csr_all_info;
1163 --
1164 if g_debug then
1165 hr_utility.set_location(l_proc, 100);
1166 end if;
1167 --
1171 l_group_per_in_ler_id_tab.last
1168 if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1169 -- bulk update the data into ben_cwb_summary
1170 forall i in l_group_per_in_ler_id_tab.first..
1172 update ben_cwb_summary
1173 set elig_count_all = l_elig_count_all_tab(i)
1174 ,emp_recv_count_all = l_emp_recv_count_all_tab(i)
1175 ,elig_sal_val_all = l_elig_sal_val_all_tab(i)
1176 ,ws_val_all = l_ws_val_all_tab(i)
1177 ,ws_bdgt_val_all = l_ws_bdgt_val_all_tab(i)
1178 ,ws_bdgt_iss_val_all = l_ws_bdgt_iss_val_all_tab(i)
1179 ,stat_sal_val_all = l_stat_sal_val_all_tab(i)
1180 ,oth_comp_val_all = l_oth_comp_val_all_tab(i)
1181 ,tot_comp_val_all = l_tot_comp_val_all_tab(i)
1182 ,rec_val_all = l_rec_val_all_tab(i)
1183 ,rec_mn_val_all = l_rec_mn_val_all_tab(i)
1184 ,rec_mx_val_all = l_rec_mx_val_all_tab(i)
1185 ,misc1_val_all = l_misc1_val_all_tab(i)
1186 ,misc2_val_all = l_misc2_val_all_tab(i)
1187 ,misc3_val_all = l_misc3_val_all_tab(i)
1188 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
1189 and group_pl_id = l_group_pl_id_tab(i)
1190 and group_oipl_id = l_group_oipl_id_tab(i);
1191 end if;
1192 --
1193 if g_debug then
1194 hr_utility.set_location(l_proc, 110);
1195 end if;
1196 --
1197 l_group_per_in_ler_id_tab.delete;
1198 l_group_pl_id_tab.delete;
1199 l_group_oipl_id_tab.delete;
1200 l_elig_count_all_tab.delete;
1201 l_emp_recv_count_all_tab.delete;
1202 l_elig_sal_val_all_tab.delete;
1203 l_ws_val_all_tab.delete;
1204 l_ws_bdgt_val_all_tab.delete;
1205 l_ws_bdgt_iss_val_all_tab.delete;
1206 l_stat_sal_val_all_tab.delete;
1207 l_oth_comp_val_all_tab.delete;
1208 l_tot_comp_val_all_tab.delete;
1209 l_rec_val_all_tab.delete;
1210 l_rec_mn_val_all_tab.delete;
1211 l_rec_mx_val_all_tab.delete;
1212 l_misc1_val_all_tab.delete;
1213 l_misc2_val_all_tab.delete;
1214 l_misc3_val_all_tab.delete;
1215 --
1216 if g_debug then
1217 hr_utility.set_location(l_proc, 120);
1218 end if;
1219 --
1220 if l_uses_bdgt_flag = 'Y' then
1221 -- process the budgets info
1222 --
1223 if l_prsrv_bdgt_cd = 'P' then
1224 --
1225 if g_debug then
1226 hr_utility.set_location(l_proc, 130);
1227 end if;
1228 --
1229 -- bulk collect the data
1230 open csr_directs_bdgt_in_prcnt(p_group_pl_id, p_lf_evt_ocrd_dt);
1231 fetch csr_directs_bdgt_in_prcnt bulk collect into
1232 l_group_per_in_ler_id_tab
1233 ,l_group_pl_id_tab
1234 ,l_group_oipl_id_tab
1235 ,l_bdgt_val_direct_tab
1236 ,l_bdgt_iss_val_direct_tab;
1237 close csr_directs_bdgt_in_prcnt;
1238 --
1239 if g_debug then
1240 hr_utility.set_location(l_proc, 140);
1241 end if;
1242 --
1243 -- bulk update the table
1244 if nvl(l_group_per_in_ler_id_tab.count,0) > 0 then
1245 forall i in l_group_per_in_ler_id_tab.first..
1246 l_group_per_in_ler_id_tab.last
1247 update ben_cwb_summary
1248 set bdgt_val_direct = l_bdgt_val_direct_tab(i)
1249 ,bdgt_iss_val_direct = l_bdgt_iss_val_direct_tab(i)
1250 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
1251 and group_pl_id = l_group_pl_id_tab(i)
1252 and group_oipl_id = l_group_oipl_id_tab(i);
1253 end if;
1254 --
1255 if g_debug then
1256 hr_utility.set_location(l_proc, 150);
1257 end if;
1258 --
1259 l_group_per_in_ler_id_tab.delete;
1260 l_group_pl_id_tab.delete;
1261 l_group_oipl_id_tab.delete;
1262 l_bdgt_val_direct_tab.delete;
1263 l_bdgt_iss_val_direct_tab.delete;
1264 end if; -- of prsrv_bdgt_cd
1265 end if; -- of uses_bdgt_flag
1266 --
1267 if g_debug then
1268 hr_utility.set_location(l_proc, 160);
1269 end if;
1270 --
1271 if g_debug then
1272 hr_utility.set_location(' Leaving:'|| l_proc, 999);
1273 end if;
1274 --
1275 end; -- of compute_bdgts_and_all
1276 --
1277 -- --------------------------------------------------------------------------
1278 -- |----------------------< refresh_summary_group_pl >-----------------------|
1279 -- --------------------------------------------------------------------------
1280 procedure refresh_summary_group_pl(p_group_pl_id in number
1281 ,p_lf_evt_ocrd_dt in date) is
1282
1283 -- Cursor for retrieving directs info. No budget info
1284 cursor csr_directs_info is
1285 select hrchy.mgr_per_in_ler_id group_per_in_ler_id
1286 ,rt.group_pl_id group_pl_id
1287 ,rt.group_oipl_id group_oipl_id
1288 ,count(rt.group_per_in_ler_id) elig_count_direct
1289 ,sum(decode(rt.ws_val,null,0,0,0,1)) emp_recv_count_direct
1290 ,sum(rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
1291 ,xchg.xchg_rate,1)) elig_sal_val_direct
1292 ,sum(rt.ws_val/decode(pl.ws_nnmntry_uom,null
1293 ,xchg.xchg_rate,1)) ws_val_direct
1297 ,xchg.xchg_rate,1)) oth_comp_val_direct
1294 ,sum(rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
1295 ,xchg.xchg_rate,1)) stat_sal_val_direct
1296 ,sum(rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
1298 ,sum(rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
1299 ,xchg.xchg_rate,1)) tot_comp_val_direct
1300 ,sum(rt.rec_val/decode(pl.rec_nnmntry_uom,null
1301 ,xchg.xchg_rate,1)) rec_val_direct
1302 ,sum(rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
1303 ,xchg.xchg_rate,1)) rec_mn_val_direct
1304 ,sum(rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
1305 ,xchg.xchg_rate,1)) rec_mx_val_direct
1306 ,sum(rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
1307 ,xchg.xchg_rate,1)) misc1_val_direct
1308 ,sum(rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
1309 ,xchg.xchg_rate,1)) misc2_val_direct
1310 ,sum(rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
1311 ,xchg.xchg_rate,1)) misc3_val_direct
1312 from ben_cwb_group_hrchy hrchy
1313 ,ben_cwb_person_rates rt
1314 ,ben_cwb_pl_dsgn pl
1315 ,ben_cwb_xchg xchg
1316 where rt.group_pl_id = p_group_pl_id
1317 and rt.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1318 and rt.elig_flag = 'Y'
1319 and rt.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1320 and hrchy.lvl_num = 1
1321 and rt.pl_id = pl.pl_id
1322 and pl.oipl_id = rt.oipl_id
1323 and pl.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
1324 and xchg.group_pl_id = rt.group_pl_id
1325 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
1326 and xchg.currency = rt.currency
1327 group by hrchy.mgr_per_in_ler_id,rt.group_pl_id, rt.group_oipl_id;
1328 --
1329 type group_per_in_ler_id_type is table of
1330 ben_cwb_summary.group_per_in_ler_id%type;
1331 type group_pl_id_type is table of ben_cwb_summary.group_pl_id%type;
1332 type group_oipl_id_type is table of ben_cwb_summary.group_oipl_id%type;
1333 type elig_count_direct_type is table of
1334 ben_cwb_summary.elig_count_direct%type;
1335 type emp_recv_count_direct_type is table of
1336 ben_cwb_summary.emp_recv_count_direct%type;
1337 type elig_sal_val_direct_type is table of
1338 ben_cwb_summary.elig_sal_val_direct%type;
1339 type ws_val_direct_type is table of ben_cwb_summary.ws_val_direct%type;
1340 type stat_sal_val_direct_type is table of
1341 ben_cwb_summary.stat_sal_val_direct%type;
1342 type oth_comp_val_direct_type is table of
1343 ben_cwb_summary.oth_comp_val_direct%type;
1344 type tot_comp_val_direct_type is table of
1345 ben_cwb_summary.tot_comp_val_direct%type;
1346 type rec_val_direct_type is table of ben_cwb_summary.rec_val_direct%type;
1347 type rec_mn_val_direct_type is table of
1348 ben_cwb_summary.rec_mn_val_direct%type;
1349 type rec_mx_val_direct_type is table of
1350 ben_cwb_summary.rec_mx_val_direct%type;
1351 type misc1_val_direct_type is table of
1352 ben_cwb_summary.misc1_val_direct%type;
1353 type misc2_val_direct_type is table of
1354 ben_cwb_summary.misc2_val_direct%type;
1355 type misc3_val_direct_type is table of
1356 ben_cwb_summary.misc3_val_direct%type;
1357 --
1358 l_group_per_in_ler_id_tab group_per_in_ler_id_type;
1359 l_group_pl_id_tab group_pl_id_type;
1360 l_group_oipl_id_tab group_oipl_id_type;
1361 l_elig_count_direct_tab elig_count_direct_type;
1362 l_emp_recv_count_direct_tab emp_recv_count_direct_type;
1363 l_elig_sal_val_direct_tab elig_sal_val_direct_type;
1364 l_ws_val_direct_tab ws_val_direct_type;
1365 l_stat_sal_val_direct_tab stat_sal_val_direct_type;
1366 l_oth_comp_val_direct_tab oth_comp_val_direct_type;
1367 l_tot_comp_val_direct_tab tot_comp_val_direct_type;
1368 l_rec_val_direct_tab rec_val_direct_type;
1369 l_rec_mn_val_direct_tab rec_mn_val_direct_type;
1370 l_rec_mx_val_direct_tab rec_mx_val_direct_type;
1371 l_misc1_val_direct_tab misc1_val_direct_type;
1372 l_misc2_val_direct_tab misc2_val_direct_type;
1373 l_misc3_val_direct_tab misc3_val_direct_type;
1374 --
1375 l_proc varchar2(72) := g_package||'refresh_summary_group_pl';
1376 --
1377 begin
1378 --
1379 if g_debug then
1380 hr_utility.set_location('Entering:'|| l_proc, 10);
1381 end if;
1382 --
1383 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1384 'refresh_summary_group_pl';
1385 ben_manage_cwb_life_events.g_error_log_rec.step_number := 711;
1386 --
1387 check_refresh_jobs(p_group_pl_id => p_group_pl_id
1388 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1389 --
1390 begin
1391 --
1392 insert_refresh_job_marker(p_group_pl_id => p_group_pl_id
1393 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1394 --
1395 -- delete all the summary rows from ben_cwb_summary for
1396 -- this group_pl_id and lf_evt_ocrd_dt
1397 delete from ben_cwb_summary summ
1398 where group_pl_id = p_group_pl_id
1399 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1400 and group_per_in_ler_id <> -1;
1401 --
1402 if g_debug then
1403 hr_utility.set_location(l_proc, 20);
1404 end if;
1405 --
1406 -- Insert an empty summary row for each managers. This summary row
1407 -- will contain only the group_per_in_ler_id, group_pl_id, group_oipl_id,
1408 -- person_id and lf_evt_ocrd_dt
1409 insert into ben_cwb_summary
1410 (summary_id
1414 ,person_id
1411 ,group_per_in_ler_id
1412 ,group_pl_id
1413 ,group_oipl_id
1415 ,lf_evt_ocrd_dt)
1416 select ben_cwb_summary_s.nextval
1417 ,hrchy.emp_per_in_ler_id
1418 ,p_group_pl_id
1419 ,grp.group_oipl_id
1420 ,pil.person_id
1421 ,p_lf_evt_ocrd_dt
1422 from ben_cwb_group_hrchy hrchy
1423 ,ben_cwb_person_groups grp
1424 ,ben_per_in_ler pil
1425 where hrchy.lvl_num=0
1426 and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
1427 and pil.group_pl_id = p_group_pl_id
1428 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1429 and grp.group_per_in_ler_id = pil.per_in_ler_id
1430 and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
1431
1432 --
1433 if g_debug then
1434 hr_utility.set_location(l_proc, 30);
1435 end if;
1436 --
1437 -- get the directs info
1438 open csr_directs_info;
1439 fetch csr_directs_info bulk collect into l_group_per_in_ler_id_tab
1440 ,l_group_pl_id_tab
1441 ,l_group_oipl_id_tab
1442 ,l_elig_count_direct_tab
1443 ,l_emp_recv_count_direct_tab
1444 ,l_elig_sal_val_direct_tab
1445 ,l_ws_val_direct_tab
1446 ,l_stat_sal_val_direct_tab
1447 ,l_oth_comp_val_direct_tab
1448 ,l_tot_comp_val_direct_tab
1449 ,l_rec_val_direct_tab
1450 ,l_rec_mn_val_direct_tab
1451 ,l_rec_mx_val_direct_tab
1452 ,l_misc1_val_direct_tab
1453 ,l_misc2_val_direct_tab
1454 ,l_misc3_val_direct_tab;
1455 close csr_directs_info;
1456 --
1457 if g_debug then
1458 hr_utility.set_location(l_proc, 40);
1459 end if;
1460 --
1461 if (nvl(l_group_per_in_ler_id_tab.count,0) > 0)then
1462 forall i in l_group_per_in_ler_id_tab.first..
1463 l_group_per_in_ler_id_tab.last
1464 update ben_cwb_summary
1465 set elig_count_direct = l_elig_count_direct_tab(i)
1466 ,emp_recv_count_direct = l_emp_recv_count_direct_tab(i)
1467 ,elig_sal_val_direct = l_elig_sal_val_direct_tab(i)
1468 ,ws_val_direct = l_ws_val_direct_tab(i)
1469 ,stat_sal_val_direct = l_stat_sal_val_direct_tab(i)
1470 ,oth_comp_val_direct = l_oth_comp_val_direct_tab(i)
1471 ,tot_comp_val_direct = l_tot_comp_val_direct_tab(i)
1472 ,rec_val_direct = l_rec_val_direct_tab(i)
1473 ,rec_mn_val_direct = l_rec_mn_val_direct_tab(i)
1474 ,rec_mx_val_direct = l_rec_mx_val_direct_tab(i)
1475 ,misc1_val_direct = l_misc1_val_direct_tab(i)
1476 ,misc2_val_direct = l_misc2_val_direct_tab(i)
1477 ,misc3_val_direct = l_misc3_val_direct_tab(i)
1478 where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
1479 and group_pl_id = l_group_pl_id_tab(i)
1480 and group_oipl_id = l_group_oipl_id_tab(i);
1481
1482 --
1483 -- delete the pl_sql tables
1484 l_group_per_in_ler_id_tab.delete;
1485 l_group_pl_id_tab.delete;
1486 l_group_oipl_id_tab.delete;
1487 l_elig_count_direct_tab.delete;
1488 l_emp_recv_count_direct_tab.delete;
1489 l_elig_sal_val_direct_tab.delete;
1490 l_ws_val_direct_tab.delete;
1491 l_stat_sal_val_direct_tab.delete;
1492 l_oth_comp_val_direct_tab.delete;
1493 l_tot_comp_val_direct_tab.delete;
1494 l_rec_val_direct_tab.delete;
1495 l_rec_mn_val_direct_tab.delete;
1496 l_rec_mx_val_direct_tab.delete;
1497 l_misc1_val_direct_tab.delete;
1498 l_misc2_val_direct_tab.delete;
1499 l_misc3_val_direct_tab.delete;
1500
1501 -- Call the compute_bdgts_and_all procedure to compute the Bdgets and
1502 -- and _all information.
1503 compute_bdgts_and_all(p_group_pl_id
1504 ,p_lf_evt_ocrd_dt);
1505 end if;
1506 -- Now the summary is populated. Set the person id in ben_cwb_person_info
1507 -- to correct values from -1.
1508 update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
1509 --
1510 delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
1511 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1512 --
1513 if g_debug then
1514 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1515 end if;
1516 --
1517 exception
1518 when others then
1519 delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
1520 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
1521 raise;
1522 end;
1523 end refresh_summary_group_pl; -- of refresh_summary_group_pl
1524 --
1525 -- --------------------------------------------------------------------------
1526 -- |---------------------< update_budgets_summary >-------------------------|
1527 -- --------------------------------------------------------------------------
1528 -- This procedure is used by reassign_mgr and reassign_emp to compute the
1532 procedure update_budgets_summary(p_group_per_in_ler_id in number
1529 -- budgts of a given person. Before calling this procedure, save_pl_sql_tab
1530 -- should be called to transfer the data in pl/sql tables to the database.
1531 --
1533 ,p_prsrv_bdgt_cd in varchar2
1534 ,p_only_all in boolean default false) is
1535
1536 -- cursor to compute the prcnt bdgts direct
1537 cursor csr_prcnt_bdgts_direct(p_group_per_in_ler_id number) is
1538 select grp.group_pl_id group_pl_id
1539 ,grp.group_oipl_id group_oipl_id
1540 ,sum(grp.ws_bdgt_val * summ.elig_sal_val_direct / 100)
1541 ws_bdgt_val_direct
1542 ,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
1543 ws_bdgt_iss_val_direct
1544 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
1545 grp.dist_bdgt_val) * decode(nvl(grp.dist_bdgt_val,0),0,
1546 summ.elig_sal_val_direct, summ.elig_sal_val_all) / 100)
1547 bdgt_val_direct
1548 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_iss_val,
1549 grp.dist_bdgt_iss_val) * decode(nvl(grp.dist_bdgt_val,0),0,
1550 summ.elig_sal_val_direct, summ.elig_sal_val_all) / 100)
1551 bdgt_iss_val_direct
1552 from ben_cwb_group_hrchy hrchy
1553 ,ben_cwb_person_groups grp
1554 ,ben_cwb_summary summ
1555 where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
1556 and hrchy.lvl_num = 1
1557 and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1558 and summ.group_per_in_ler_id (+)= grp.group_per_in_ler_id
1559 and summ.group_pl_id (+)= grp.group_pl_id
1560 and summ.group_oipl_id (+)= grp.group_oipl_id
1561 group by grp.group_pl_id, grp.group_oipl_id
1562 order by grp.group_pl_id, grp.group_oipl_id;
1563
1564 -- cursor to compute the amount bdgts direct
1565 cursor csr_amt_bdgts_direct(p_group_per_in_ler_id number) is
1566 select grp.group_pl_id group_pl_id
1567 ,grp.group_oipl_id group_oipl_id
1568 ,sum(grp.ws_bdgt_val) ws_bdgt_val_direct
1569 ,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_direct
1570 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
1571 grp.dist_bdgt_val)) bdgt_val_direct
1572 ,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_iss_val,
1573 grp.dist_bdgt_iss_val)) bdgt_iss_val_direct
1574 from ben_cwb_group_hrchy hrchy
1575 ,ben_cwb_person_groups grp
1576 where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
1577 and hrchy.lvl_num = 1
1578 and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1579 group by grp.group_pl_id, grp.group_oipl_id
1580 order by grp.group_pl_id, grp.group_oipl_id;
1581
1582 -- cursor to compute the prcnt ws_bdgt_all
1583 cursor csr_prcnt_ws_bdgts_all(p_group_per_in_ler_id number) is
1584 select grp.group_pl_id group_pl_id
1585 ,grp.group_oipl_id group_oipl_id
1586 ,sum(grp.ws_bdgt_val * summ.elig_sal_val_direct / 100)
1587 ws_bdgt_val_all
1588 ,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
1589 ws_bdgt_iss_val_all
1590 from ben_cwb_group_hrchy hrchy
1591 ,ben_cwb_person_groups grp
1592 ,ben_cwb_summary summ
1593 where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
1594 and hrchy.lvl_num >= 1
1595 and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1596 and summ.group_per_in_ler_id (+)= grp.group_per_in_ler_id
1597 and summ.group_pl_id (+)= grp.group_pl_id
1598 and summ.group_oipl_id (+)= grp.group_oipl_id
1599 group by grp.group_pl_id, grp.group_oipl_id
1600 order by grp.group_pl_id, grp.group_oipl_id;
1601
1602 -- cursor to compute the amount ws_bdgt_all
1603 cursor csr_amt_ws_bdgts_all(p_group_per_in_ler_id number) is
1604 select grp.group_pl_id group_pl_id
1605 ,grp.group_oipl_id group_oipl_id
1606 ,sum(grp.ws_bdgt_val) ws_bdgt_val_all
1607 ,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_all
1608 from ben_cwb_group_hrchy hrchy
1609 ,ben_cwb_person_groups grp
1610 where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
1611 and hrchy.lvl_num >= 1
1612 and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1613 group by grp.group_pl_id, grp.group_oipl_id
1614 order by grp.group_pl_id, grp.group_oipl_id;
1615
1616 -- cursor to fetch the old summary
1617 cursor csr_old_bdgts_summary(p_group_per_in_ler_id number) is
1618 select sum(ws_bdgt_val_direct) ws_bdgt_val_direct
1619 ,sum(ws_bdgt_val_all) ws_bdgt_val_all
1620 ,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val_direct
1621 ,sum(ws_bdgt_iss_val_all) ws_bdgt_iss_val_all
1622 ,sum(bdgt_val_direct) bdgt_val_direct
1623 ,sum(bdgt_iss_val_direct) bdgt_iss_val
1624 from ben_cwb_summary
1625 where group_per_in_ler_id = p_group_per_in_ler_id
1626 group by group_pl_id, group_oipl_id
1627 order by group_pl_id, group_oipl_id;
1628 --
1629 -- Type delcarations for pl/sql tables
1630 type group_pl_id_type is table of
1631 ben_cwb_summary.group_pl_id%type;
1632 type group_oipl_id_type is table of
1633 ben_cwb_summary.group_oipl_id%type;
1634 type ws_bdgt_val_direct_type is table of
1635 ben_cwb_summary.ws_bdgt_val_direct%type;
1636 type ws_bdgt_val_all_type is table of
1637 ben_cwb_summary.ws_bdgt_val_all%type;
1638 type ws_bdgt_iss_val_direct_type is table of
1639 ben_cwb_summary.ws_bdgt_iss_val_direct%type;
1640 type ws_bdgt_iss_val_all_type is table of
1644 type bdgt_iss_val_direct_type is table of
1641 ben_cwb_summary.ws_bdgt_iss_val_all%type;
1642 type bdgt_val_direct_type is table of
1643 ben_cwb_summary.bdgt_val_direct%type;
1645 ben_cwb_summary.bdgt_iss_val_direct%type;
1646 --
1647 l_group_pl_id_tab group_pl_id_type;
1648 l_group_oipl_id_tab group_oipl_id_type;
1649 l_ws_bdgt_val_direct_tab ws_bdgt_val_direct_type;
1650 l_ws_bdgt_val_all_tab ws_bdgt_val_all_type;
1651 l_ws_bdgt_iss_val_direct_tab ws_bdgt_iss_val_direct_type;
1652 l_ws_bdgt_iss_val_all_tab ws_bdgt_iss_val_all_type;
1653 l_bdgt_val_direct_tab bdgt_val_direct_type;
1654 l_bdgt_iss_val_direct_tab bdgt_iss_val_direct_type;
1655 l_old_ws_bdgt_val_direct_tab ws_bdgt_val_direct_type;
1656 l_old_ws_bdgt_val_all_tab ws_bdgt_val_all_type;
1657 l_old_ws_bdgt_iss_val_dir_tab ws_bdgt_iss_val_direct_type;
1658 l_old_ws_bdgt_iss_val_all_tab ws_bdgt_iss_val_all_type;
1659 l_old_bdgt_val_direct_tab bdgt_val_direct_type;
1660 l_old_bdgt_iss_val_direct_tab bdgt_iss_val_direct_type;
1661 --
1662 l_proc varchar2(72) := g_package||'update_budgets_summary';
1663 --
1664 begin
1665 --
1666 if g_debug then
1667 hr_utility.set_location('Entering:'|| l_proc, 10);
1668 end if;
1669 --
1670 -- get the Ws Bdgt Direct and Dist Bdgt Direct
1671 if p_prsrv_bdgt_cd = 'P' then
1672 --
1673 if g_debug then
1674 hr_utility.set_location(l_proc, 20);
1675 end if;
1676 --
1677 if not (p_only_all) then
1678 open csr_prcnt_bdgts_direct(p_group_per_in_ler_id);
1679 fetch csr_prcnt_bdgts_direct bulk collect into
1680 l_group_pl_id_tab
1681 ,l_group_oipl_id_tab
1682 ,l_ws_bdgt_val_direct_tab
1683 ,l_ws_bdgt_iss_val_direct_tab
1684 ,l_bdgt_val_direct_tab
1685 ,l_bdgt_iss_val_direct_tab;
1686 close csr_prcnt_bdgts_direct;
1687 end if;
1688 --
1689 if g_debug then
1690 hr_utility.set_location(l_proc, 30);
1691 end if;
1692 --
1693 open csr_prcnt_ws_bdgts_all(p_group_per_in_ler_id);
1694 fetch csr_prcnt_ws_bdgts_all bulk collect into
1695 l_group_pl_id_tab
1696 ,l_group_oipl_id_tab
1697 ,l_ws_bdgt_val_all_tab
1698 ,l_ws_bdgt_iss_val_all_tab;
1699 close csr_prcnt_ws_bdgts_all;
1700 else
1701 --
1702 if g_debug then
1703 hr_utility.set_location(l_proc, 40);
1704 end if;
1705 --
1706 if not (p_only_all) then
1707 open csr_amt_bdgts_direct(p_group_per_in_ler_id);
1708 fetch csr_amt_bdgts_direct bulk collect into
1709 l_group_pl_id_tab
1710 ,l_group_oipl_id_tab
1711 ,l_ws_bdgt_val_direct_tab
1712 ,l_ws_bdgt_iss_val_direct_tab
1713 ,l_bdgt_val_direct_tab
1714 ,l_bdgt_iss_val_direct_tab;
1715 close csr_amt_bdgts_direct;
1716 end if;
1717 --
1718 if g_debug then
1719 hr_utility.set_location(l_proc, 50);
1720 end if;
1721 --
1722 open csr_amt_ws_bdgts_all(p_group_per_in_ler_id);
1723 fetch csr_amt_ws_bdgts_all bulk collect into
1724 l_group_pl_id_tab
1725 ,l_group_oipl_id_tab
1726 ,l_ws_bdgt_val_all_tab
1727 ,l_ws_bdgt_iss_val_all_tab;
1728 close csr_amt_ws_bdgts_all;
1729 end if;
1730 --
1731 if g_debug then
1732 hr_utility.set_location(l_proc, 60);
1733 end if;
1734 --
1735 -- get the old bdgts summary
1736 open csr_old_bdgts_summary(p_group_per_in_ler_id);
1737 fetch csr_old_bdgts_summary bulk collect into
1738 l_old_ws_bdgt_val_direct_tab
1739 ,l_old_ws_bdgt_val_all_tab
1740 ,l_old_ws_bdgt_iss_val_dir_tab
1741 ,l_old_ws_bdgt_iss_val_all_tab
1742 ,l_old_bdgt_val_direct_tab
1743 ,l_old_bdgt_iss_val_direct_tab;
1744 close csr_old_bdgts_summary;
1745 --
1746 if g_debug then
1747 hr_utility.set_location(l_proc, 70);
1748 end if;
1749 --
1750 if nvl(l_group_pl_id_tab.count,0) = 0 then
1751 --
1752 if g_debug then
1753 hr_utility.set_location(' Leaving:'|| l_proc, 79);
1754 end if;
1755 -- no records to process
1756 return;
1757 end if;
1758 --
1759 for i in l_group_pl_id_tab.first..l_group_pl_id_tab.last
1760 loop
1761 --
1762 if g_debug then
1763 hr_utility.set_location(l_proc, 80);
1764 hr_utility.set_location('grp pil :'||p_group_per_in_ler_id,85);
1765 hr_utility.set_location('Ws bdgt All :'||l_ws_bdgt_val_all_tab(i),85);
1766 hr_utility.set_location('Ws bdgt Iss All :'||l_ws_bdgt_iss_val_all_tab(i),85);
1767 end if;
1768 --
1769 -- call the update_or_insert_pl_sql_tab with the diff
1773 ,p_group_pl_id => l_group_pl_id_tab(i)
1770 if not (p_only_all) then
1771 update_or_insert_pl_sql_tab
1772 (p_group_per_in_ler_id => p_group_per_in_ler_id
1774 ,p_group_oipl_id => l_group_oipl_id_tab(i)
1775 ,p_ws_bdgt_val_direct => ben_cwb_utils.add_number_with_null_check
1776 (l_ws_bdgt_val_direct_tab(i),
1777 -l_old_ws_bdgt_val_direct_tab(i))
1778 ,p_ws_bdgt_val_all => ben_cwb_utils.add_number_with_null_check
1779 (l_ws_bdgt_val_all_tab(i),
1780 -l_old_ws_bdgt_val_all_tab(i))
1781 ,p_ws_bdgt_iss_val_direct => ben_cwb_utils.add_number_with_null_check
1782 (l_ws_bdgt_iss_val_direct_tab(i),
1783 -l_old_ws_bdgt_iss_val_dir_tab(i))
1784 ,p_ws_bdgt_iss_val_all => ben_cwb_utils.add_number_with_null_check
1785 (l_ws_bdgt_iss_val_all_tab(i),
1786 -l_old_ws_bdgt_iss_val_all_tab(i))
1787 ,p_bdgt_val_direct => ben_cwb_utils.add_number_with_null_check
1788 (l_bdgt_val_direct_tab(i),
1789 -l_old_bdgt_val_direct_tab(i))
1790 ,p_bdgt_iss_val_direct => ben_cwb_utils.add_number_with_null_check
1791 (l_bdgt_iss_val_direct_tab(i),
1792 -l_old_bdgt_iss_val_direct_tab(i)));
1793 else
1794 update_or_insert_pl_sql_tab
1795 (p_group_per_in_ler_id => p_group_per_in_ler_id
1796 ,p_group_pl_id => l_group_pl_id_tab(i)
1797 ,p_group_oipl_id => l_group_oipl_id_tab(i)
1798 ,p_ws_bdgt_val_all => ben_cwb_utils.add_number_with_null_check
1799 (l_ws_bdgt_val_all_tab(i),
1800 -l_old_ws_bdgt_val_all_tab(i))
1801 ,p_ws_bdgt_iss_val_all => ben_cwb_utils.add_number_with_null_check
1802 (l_ws_bdgt_iss_val_all_tab(i),
1803 -l_old_ws_bdgt_iss_val_all_tab(i)));
1804 end if;
1805 end loop;
1806 --
1807 -- call save_pl_sql_tab to transfer from pl/sql table to database
1808 save_pl_sql_tab;
1809 --
1810 --
1811 if g_debug then
1812 hr_utility.set_location(l_proc, 90);
1813 end if;
1814 --
1815 if g_debug then
1816 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1817 end if;
1818 --
1819 end; -- update_budgets_summary
1820 --
1821 -- --------------------------------------------------------------------------
1822 -- |----------------------< refresh_summary_persons >-----------------------|
1823 -- --------------------------------------------------------------------------
1824 --
1825 procedure refresh_summary_persons(p_group_pl_id in number
1826 ,p_lf_evt_ocrd_dt in date) is
1827
1828 -- cursor of employees which were processed in the current run
1829 cursor csr_emps is
1830 select per.group_per_in_ler_id
1831 ,hrchy.mgr_per_in_ler_id
1832 from ben_cwb_person_info per
1833 ,ben_cwb_group_hrchy hrchy
1834 where per.person_id = -1
1835 and per.group_pl_id = p_group_pl_id
1836 and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1837 and per.group_per_in_ler_id = hrchy.emp_per_in_ler_id (+)
1838 and per.group_per_in_ler_id = hrchy.mgr_per_in_ler_id (+);
1839
1840 -- cursor for computing the empty summary rows for managers
1841 cursor csr_empty_summary(p_group_pl_id number
1842 ,p_lf_evt_ocrd_dt date)is
1843 select hrchy.mgr_per_in_ler_id
1844 ,p_group_pl_id group_pl_id
1845 ,grp.group_oipl_id
1846 ,pil.person_id
1847 from ben_cwb_group_hrchy hrchy
1848 ,ben_cwb_person_info per
1849 ,ben_cwb_person_groups grp
1850 ,ben_per_in_ler pil
1851 where per.person_id = -1
1852 and per.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1853 and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
1854 and grp.group_pl_id = p_group_pl_id
1855 and grp.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1856 and grp.group_per_in_ler_id = pil.per_in_ler_id
1857 and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
1858 and not exists (select 'Y'
1859 from ben_cwb_summary sm
1860 where sm.group_per_in_ler_id = grp.group_per_in_ler_id
1861 and sm.group_pl_id = grp.group_pl_id
1862 and sm.group_oipl_id = grp.group_oipl_id)
1863 group by hrchy.mgr_per_in_ler_id, group_oipl_id, pil.person_id;
1864 -- Cursor for retrieving directs info. No budget info
1865 cursor csr_directs_info(v_group_per_in_ler_id in number) is
1866 select hrchy.mgr_per_in_ler_id group_per_in_ler_id
1867 ,rt.group_pl_id group_pl_id
1868 ,rt.group_oipl_id group_oipl_id
1869 ,count(rt.group_per_in_ler_id) elig_count
1870 ,sum(decode(rt.ws_val,null,0,0,0,1)) emp_recv_count
1871 ,sum(rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
1872 ,xchg.xchg_rate,1)) elig_sal_val
1873 ,sum(rt.ws_val/decode(pl.ws_nnmntry_uom,null
1874 ,xchg.xchg_rate,1)) ws_val
1875 ,sum(rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
1879 ,sum(rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
1876 ,xchg.xchg_rate,1)) stat_sal_val
1877 ,sum(rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
1878 ,xchg.xchg_rate,1)) oth_comp_val
1880 ,xchg.xchg_rate,1)) tot_comp_val
1881 ,sum(rt.rec_val/decode(pl.rec_nnmntry_uom,null
1882 ,xchg.xchg_rate,1)) rec_val
1883 ,sum(rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
1884 ,xchg.xchg_rate,1)) rec_mn_val
1885 ,sum(rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
1886 ,xchg.xchg_rate,1)) rec_mx_val
1887 ,sum(rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
1888 ,xchg.xchg_rate,1)) misc1_val
1889 ,sum(rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
1890 ,xchg.xchg_rate,1)) misc2_val
1891 ,sum(rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
1892 ,xchg.xchg_rate,1)) misc3_val
1893 from ben_cwb_group_hrchy hrchy
1894 ,ben_cwb_person_rates rt
1895 ,ben_cwb_pl_dsgn pl
1896 ,ben_cwb_xchg xchg
1897 where hrchy.mgr_per_in_ler_id = v_group_per_in_ler_id
1898 and rt.group_per_in_ler_id = hrchy.emp_per_in_ler_id
1899 and hrchy.lvl_num = 1
1900 and rt.elig_flag = 'Y'
1901 and rt.pl_id = pl.pl_id
1902 and pl.oipl_id = rt.oipl_id
1903 and pl.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
1904 and xchg.group_pl_id = rt.group_pl_id
1905 and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
1906 and xchg.currency = rt.currency
1907 group by hrchy.mgr_per_in_ler_id,rt.group_pl_id, rt.group_oipl_id;
1908 -- cursor to get the _all info.
1909 cursor csr_all_info(v_group_per_in_ler_id number) is
1910 select hrchy.mgr_per_in_ler_id
1911 ,summ.group_pl_id, summ.group_oipl_id
1912 ,sum(elig_count_direct) elig_count
1913 ,sum(emp_recv_count_direct) emp_recv_count
1914 ,sum(elig_sal_val_direct) elig_sal_val
1915 ,sum(ws_val_direct) ws_val
1916 ,sum(ws_bdgt_val_direct)ws_bdgt_val
1917 ,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val
1918 ,sum(stat_sal_val_direct) stat_sal_val
1919 ,sum(oth_comp_val_direct) oth_comp_val
1920 ,sum(tot_comp_val_direct) tot_comp_val
1921 ,sum(rec_val_direct) rec_val
1922 ,sum(rec_mn_val_direct) rec_mn_val
1923 ,sum(rec_mx_val_direct) rec_mx_val
1924 ,sum(misc1_val_direct) misc1_val
1925 ,sum(misc2_val_direct) misc2_val
1926 ,sum(misc3_val_direct) misc3_val
1927 from ben_cwb_group_hrchy hrchy
1928 ,ben_cwb_summary summ
1929 where hrchy.mgr_per_in_ler_id = v_group_per_in_ler_id
1930 and hrchy.lvl_num >=0
1931 and hrchy.emp_per_in_ler_id = summ.group_per_in_ler_id
1932 group by hrchy.mgr_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
1933 --
1934 cursor csr_mgrs_info(v_per_in_ler_id in number) is
1935 select h.mgr_per_in_ler_id
1936 ,h.lvl_num
1937 ,i.person_id
1938 from ben_cwb_group_hrchy h
1939 ,ben_cwb_person_info i
1940 where h.emp_per_in_ler_id = v_per_in_ler_id
1941 and h.lvl_num > 0
1942 and h.mgr_per_in_ler_id = i.group_per_in_ler_id
1943 order by h.lvl_num;
1944 --
1945 l_prsrv_bdgt_cd varchar2(30);
1946 l_uses_bdgt_flag varchar2(30);
1947 l_count number;
1948 l_immd_mgr number;
1949 --
1950 l_proc varchar2(72) := g_package||'refresh_summary_persons';
1951 --
1952 begin
1953 --
1954 if g_debug then
1955 hr_utility.set_location('Entering:'|| l_proc, 10);
1956 end if;
1957 --
1958 -- Need to execute this procedure only when the number of persons with
1959 -- person_id as -1 is less. If records are more, call the procedure
1960 -- refresh_summary_group_pl
1961
1962 select count(per.group_per_in_ler_id) into l_count
1963 from ben_cwb_person_info per
1964 where per.person_id = -1
1965 and per.group_pl_id = p_group_pl_id
1966 and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
1967
1968 if l_count = 0 then
1969 -- No new life event.
1970 return;
1971 elsif l_count > 2 then
1972 --
1973 if g_debug then
1974 hr_utility.set_location(l_proc, 15);
1975 end if;
1976 -- call the refresh_summary_group_pl to improve performance
1977 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1978 'refresh_summary_group_pl';
1979 ben_manage_cwb_life_events.g_error_log_rec.step_number := 71;
1980 --
1981 refresh_summary_group_pl(p_group_pl_id, p_lf_evt_ocrd_dt);
1982 --
1983 return;
1984 --
1985 end if;
1986 --
1987 if g_debug then
1988 hr_utility.set_location(l_proc, 20);
1989 end if;
1990 --
1991 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
1992 'refresh_summary_persons';
1993 ben_manage_cwb_life_events.g_error_log_rec.step_number := 72;
1994 --
1995 -- Insert an empty summary row for each managers. This summary row
1996 -- will contain only the group_per_in_ler_id, group_pl_id, group_oipl_id,
1997 -- person_id and lf_evt_ocrd_dt
1998 for l_empty_summ in csr_empty_summary(p_group_pl_id,p_lf_evt_ocrd_dt) loop
1999 --
2000 insert into ben_cwb_summary
2001 (summary_id
2002 ,group_per_in_ler_id
2003 ,group_pl_id
2007 values (ben_cwb_summary_s.nextval
2004 ,group_oipl_id
2005 ,person_id
2006 ,lf_evt_ocrd_dt)
2008 ,l_empty_summ.mgr_per_in_ler_id
2009 ,l_empty_summ.group_pl_id
2010 ,l_empty_summ.group_oipl_id
2011 ,l_empty_summ.person_id
2012 ,p_lf_evt_ocrd_dt);
2013 --
2014 end loop;
2015 --
2016 if g_debug then
2017 hr_utility.set_location(l_proc, 30);
2018 end if;
2019 --
2020 for l_emps in csr_emps loop
2021 --
2022 for l_rates in csr_rates(l_emps.group_per_in_ler_id) loop
2023 --
2024 l_immd_mgr := 1;
2025 --
2026 for l_mgrs in csr_mgrs_info(l_emps.group_per_in_ler_id) loop
2027 -- If the manager is also part of the current summary refresh,
2028 -- it will be taken care by manager's summary.
2029 if l_mgrs.person_id = -1 then
2030 exit;
2031 end if;
2032 --
2033 update_or_insert_pl_sql_tab
2034 (p_group_per_in_ler_id => l_mgrs.mgr_per_in_ler_id
2035 ,p_group_pl_id => l_rates.group_pl_id
2036 ,p_group_oipl_id => l_rates.group_oipl_id
2037 ,p_elig_count_direct => l_rates.elig_count*l_immd_mgr
2038 ,p_elig_count_all => l_rates.elig_count
2039 ,p_emp_recv_count_direct => l_rates.emp_recv_count*l_immd_mgr
2040 ,p_emp_recv_count_all => l_rates.emp_recv_count
2041 ,p_elig_sal_val_direct => l_rates.elig_sal_val*l_immd_mgr
2042 ,p_elig_sal_val_all => l_rates.elig_sal_val
2043 ,p_ws_val_direct => l_rates.ws_val*l_immd_mgr
2044 ,p_ws_val_all => l_rates.ws_val
2045 ,p_stat_sal_val_direct => l_rates.stat_sal_val*l_immd_mgr
2046 ,p_stat_sal_val_all => l_rates.stat_sal_val
2047 ,p_oth_comp_val_direct => l_rates.oth_comp_val*l_immd_mgr
2048 ,p_oth_comp_val_all => l_rates.oth_comp_val
2049 ,p_tot_comp_val_direct => l_rates.tot_comp_val*l_immd_mgr
2050 ,p_tot_comp_val_all => l_rates.tot_comp_val
2051 ,p_rec_val_direct => l_rates.rec_val*l_immd_mgr
2052 ,p_rec_val_all => l_rates.rec_val
2053 ,p_rec_mn_val_direct => l_rates.rec_mn_val*l_immd_mgr
2054 ,p_rec_mn_val_all => l_rates.rec_mn_val
2055 ,p_rec_mx_val_direct => l_rates.rec_mx_val*l_immd_mgr
2056 ,p_rec_mx_val_all => l_rates.rec_mx_val
2057 ,p_misc1_val_direct => l_rates.misc1_val*l_immd_mgr
2058 ,p_misc1_val_all => l_rates.misc1_val
2059 ,p_misc2_val_direct => l_rates.misc2_val*l_immd_mgr
2060 ,p_misc2_val_all => l_rates.misc2_val
2061 ,p_misc3_val_direct => l_rates.misc3_val*l_immd_mgr
2062 ,p_misc3_val_all => l_rates.misc3_val);
2063
2064 l_immd_mgr := 0;
2065 --
2066 end loop; --mgrs
2067 --
2068 end loop; -- rates
2069 --
2070 if l_emps.mgr_per_in_ler_id is not null then
2071 --
2072 -- The person is a manager, so take care of it's direct summary
2073 --
2074 for l_directs in csr_directs_info(l_emps.group_per_in_ler_id) loop
2075 update_or_insert_pl_sql_tab
2076 (p_group_per_in_ler_id => l_emps.group_per_in_ler_id
2077 ,p_group_pl_id => l_directs.group_pl_id
2078 ,p_group_oipl_id => l_directs.group_oipl_id
2079 ,p_elig_count_direct => l_directs.elig_count
2080 ,p_emp_recv_count_direct => l_directs.emp_recv_count
2081 ,p_elig_sal_val_direct => l_directs.elig_sal_val
2082 ,p_ws_val_direct => l_directs.ws_val
2083 ,p_stat_sal_val_direct => l_directs.stat_sal_val
2084 ,p_oth_comp_val_direct => l_directs.oth_comp_val
2085 ,p_tot_comp_val_direct => l_directs.tot_comp_val
2086 ,p_rec_val_direct => l_directs.rec_val
2087 ,p_rec_mn_val_direct => l_directs.rec_mn_val
2088 ,p_rec_mx_val_direct => l_directs.rec_mx_val
2089 ,p_misc1_val_direct => l_directs.misc1_val
2090 ,p_misc2_val_direct => l_directs.misc2_val
2091 ,p_misc3_val_direct => l_directs.misc3_val);
2092 end loop; --directs
2093 end if;
2094 end loop; -- emps
2095 --
2096 save_pl_sql_tab;
2097 --
2098 for l_emps in csr_emps loop
2099 if l_emps.mgr_per_in_ler_id is not null then
2100 --
2101 -- The person is a manager, so take care of it's all summary
2102 --
2103 for l_all in csr_all_info(l_emps.group_per_in_ler_id) loop
2104 update_or_insert_pl_sql_tab
2105 (p_group_per_in_ler_id => l_emps.group_per_in_ler_id
2106 ,p_group_pl_id => l_all.group_pl_id
2107 ,p_group_oipl_id => l_all.group_oipl_id
2108 ,p_elig_count_all => l_all.elig_count
2109 ,p_emp_recv_count_all => l_all.emp_recv_count
2110 ,p_elig_sal_val_all => l_all.elig_sal_val
2111 ,p_ws_val_all => l_all.ws_val
2112 ,p_stat_sal_val_all => l_all.stat_sal_val
2113 ,p_oth_comp_val_all => l_all.oth_comp_val
2114 ,p_tot_comp_val_all => l_all.tot_comp_val
2115 ,p_rec_val_all => l_all.rec_val
2116 ,p_rec_mn_val_all => l_all.rec_mn_val
2120 ,p_misc3_val_all => l_all.misc3_val);
2117 ,p_rec_mx_val_all => l_all.rec_mx_val
2118 ,p_misc1_val_all => l_all.misc1_val
2119 ,p_misc2_val_all => l_all.misc2_val
2121 --
2122 for l_mgrs in csr_mgrs_info(l_emps.group_per_in_ler_id) loop
2123 -- If the manager is also part of the current summary refresh,
2124 -- it will be taken care by manager's summary.
2125 if l_mgrs.person_id = -1 then
2126 exit;
2127 end if;
2128 --
2129 update_or_insert_pl_sql_tab
2130 (p_group_per_in_ler_id => l_mgrs.mgr_per_in_ler_id
2131 ,p_group_pl_id => l_all.group_pl_id
2132 ,p_group_oipl_id => l_all.group_oipl_id
2133 ,p_elig_count_all => l_all.elig_count
2134 ,p_emp_recv_count_all => l_all.emp_recv_count
2135 ,p_elig_sal_val_all => l_all.elig_sal_val
2136 ,p_ws_val_all => l_all.ws_val
2137 ,p_stat_sal_val_all => l_all.stat_sal_val
2138 ,p_oth_comp_val_all => l_all.oth_comp_val
2139 ,p_tot_comp_val_all => l_all.tot_comp_val
2140 ,p_rec_val_all => l_all.rec_val
2141 ,p_rec_mn_val_all => l_all.rec_mn_val
2142 ,p_rec_mx_val_all => l_all.rec_mx_val
2143 ,p_misc1_val_all => l_all.misc1_val
2144 ,p_misc2_val_all => l_all.misc2_val
2145 ,p_misc3_val_all => l_all.misc3_val);
2146 end loop; --mgrs
2147 end loop; -- all
2148 end if;
2149 --
2150 end loop; --emps
2151 --
2152 select prsrv_bdgt_cd, uses_bdgt_flag
2153 into l_prsrv_bdgt_cd, l_uses_bdgt_flag
2154 from ben_cwb_pl_dsgn
2155 where pl_id = p_group_pl_id
2156 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2157 and oipl_id = -1;
2158 --
2159 --
2160 if g_debug then
2161 hr_utility.set_location(l_proc, 90);
2162 end if;
2163
2164 -- check if the uses_bdgt_flag is set
2165 if l_uses_bdgt_flag = 'Y' then
2166 --
2167 save_pl_sql_tab;
2168 --
2169 for l_emps in csr_emps loop
2170 --
2171 for l_mgrs in csr_mgrs(l_emps.group_per_in_ler_id) loop
2172 --
2173 -- After 2 levels, only need to update all level budget.
2174 --
2175 update_budgets_summary(l_mgrs.mgr_per_in_ler_id,l_prsrv_bdgt_cd,
2176 (l_mgrs.lvl_num > 2));
2177 end loop;
2178 --
2179 end loop;
2180 end if; -- of uses_bdgt_flag
2181 --
2182 if g_debug then
2183 hr_utility.set_location(l_proc, 160);
2184 end if;
2185 --
2186 save_pl_sql_tab;
2187 --
2188 --
2189 -- Now the summary is populated. Set the person id in ben_cwb_person_info
2190 -- to correct values from -1.
2191 update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
2192 --
2193 if g_debug then
2194 hr_utility.set_location(' Leaving:'|| l_proc, 999);
2195 end if;
2196 --
2197 end; -- of refresh_summary_persons
2198 --
2199 -- --------------------------------------------------------------------------
2200 -- |--------------------< update_or_insert_pl_sql_tab >----------------------|
2201 -- --------------------------------------------------------------------------
2202 --
2203 procedure update_or_insert_pl_sql_tab
2204 (p_group_per_in_ler_id in number
2205 ,p_group_pl_id in number
2206 ,p_group_oipl_id in number
2207 ,p_elig_count_direct in number default null
2208 ,p_elig_count_all in number default null
2209 ,p_emp_recv_count_direct in number default null
2210 ,p_emp_recv_count_all in number default null
2211 ,p_elig_sal_val_direct in number default null
2212 ,p_elig_sal_val_all in number default null
2213 ,p_ws_val_direct in number default null
2214 ,p_ws_val_all in number default null
2215 ,p_ws_bdgt_val_direct in number default null
2216 ,p_ws_bdgt_val_all in number default null
2217 ,p_ws_bdgt_iss_val_direct in number default null
2218 ,p_ws_bdgt_iss_val_all in number default null
2219 ,p_bdgt_val_direct in number default null
2220 ,p_bdgt_iss_val_direct in number default null
2221 ,p_stat_sal_val_direct in number default null
2222 ,p_stat_sal_val_all in number default null
2223 ,p_oth_comp_val_direct in number default null
2224 ,p_oth_comp_val_all in number default null
2225 ,p_tot_comp_val_direct in number default null
2226 ,p_tot_comp_val_all in number default null
2227 ,p_rec_val_direct in number default null
2228 ,p_rec_val_all in number default null
2229 ,p_rec_mn_val_direct in number default null
2230 ,p_rec_mn_val_all in number default null
2231 ,p_rec_mx_val_direct in number default null
2232 ,p_rec_mx_val_all in number default null
2233 ,p_misc1_val_direct in number default null
2234 ,p_misc1_val_all in number default null
2235 ,p_misc2_val_direct in number default null
2239 ,p_person_id in number default null
2236 ,p_misc2_val_all in number default null
2237 ,p_misc3_val_direct in number default null
2238 ,p_misc3_val_all in number default null
2240 ,p_lf_evt_ocrd_dt in date default null) is
2241 --
2242 l_found boolean := false;
2243 j binary_integer;
2244 --
2245 l_proc varchar2(72) := g_package||'update_or_insert_pl_sql_tab';
2246 --
2247 begin
2248 --
2249 if g_debug then
2250 hr_utility.set_location('Entering:'|| l_proc, 10);
2251 end if;
2252 --
2253 hr_utility.set_location('Count :'||g_summary_rec.count,20);
2254 --
2255 if nvl(g_summary_rec.count,0) > 0 then
2256 for i in g_summary_rec.first..g_summary_rec.last
2257 loop
2258 if(g_summary_rec(i).group_per_in_ler_id = p_group_per_in_ler_id and
2259 g_summary_rec(i).group_pl_id = p_group_pl_id and
2260 g_summary_rec(i).group_oipl_id = p_group_oipl_id) then
2261 --
2262 l_found := true;
2263 j := i;
2264 exit;
2265 end if;
2266 end loop;
2267 end if;
2268 --
2269 if (l_found) then
2270 --
2271 if g_debug then
2272 hr_utility.set_location(l_proc, 30);
2273 end if;
2274 --
2275 -- record alredy exists. So update the summary
2276 g_summary_rec(j).elig_count_direct := ben_cwb_utils.add_number_with_null_check
2277 (g_summary_rec(j).elig_count_direct,p_elig_count_direct);
2278 g_summary_rec(j).elig_count_all := ben_cwb_utils.add_number_with_null_check
2279 (g_summary_rec(j).elig_count_all,p_elig_count_all);
2280 g_summary_rec(j).emp_recv_count_direct := ben_cwb_utils.add_number_with_null_check
2281 (g_summary_rec(j).emp_recv_count_direct,p_emp_recv_count_direct);
2282 g_summary_rec(j).emp_recv_count_all :=ben_cwb_utils.add_number_with_null_check
2283 (g_summary_rec(j).emp_recv_count_all,p_emp_recv_count_all);
2284 g_summary_rec(j).elig_sal_val_direct :=ben_cwb_utils.add_number_with_null_check
2285 (g_summary_rec(j).elig_sal_val_direct,p_elig_sal_val_direct);
2286 g_summary_rec(j).elig_sal_val_all := ben_cwb_utils.add_number_with_null_check
2287 (g_summary_rec(j).elig_sal_val_all,p_elig_sal_val_all);
2288 g_summary_rec(j).ws_val_direct :=ben_cwb_utils.add_number_with_null_check
2289 (g_summary_rec(j).ws_val_direct,p_ws_val_direct);
2290 g_summary_rec(j).ws_val_all :=ben_cwb_utils.add_number_with_null_check
2291 (g_summary_rec(j).ws_val_all,p_ws_val_all);
2292 g_summary_rec(j).ws_bdgt_val_direct :=ben_cwb_utils.add_number_with_null_check
2293 (g_summary_rec(j).ws_bdgt_val_direct,p_ws_bdgt_val_direct);
2294 g_summary_rec(j).ws_bdgt_val_all :=ben_cwb_utils.add_number_with_null_check
2295 (g_summary_rec(j).ws_bdgt_val_all,p_ws_bdgt_val_all);
2296 g_summary_rec(j).ws_bdgt_iss_val_direct := ben_cwb_utils.add_number_with_null_check
2297 (g_summary_rec(j).ws_bdgt_iss_val_direct,p_ws_bdgt_iss_val_direct);
2298 g_summary_rec(j).ws_bdgt_iss_val_all :=ben_cwb_utils.add_number_with_null_check
2299 (g_summary_rec(j).ws_bdgt_iss_val_all,p_ws_bdgt_iss_val_all);
2300 g_summary_rec(j).bdgt_val_direct :=ben_cwb_utils.add_number_with_null_check
2301 (g_summary_rec(j).bdgt_val_direct,p_bdgt_val_direct);
2302 g_summary_rec(j).bdgt_iss_val_direct :=ben_cwb_utils.add_number_with_null_check
2303 (g_summary_rec(j).bdgt_iss_val_direct,p_bdgt_iss_val_direct);
2304 g_summary_rec(j).stat_sal_val_direct :=ben_cwb_utils.add_number_with_null_check
2305 (g_summary_rec(j).stat_sal_val_direct,p_stat_sal_val_direct);
2306 g_summary_rec(j).stat_sal_val_all :=ben_cwb_utils.add_number_with_null_check
2307 (g_summary_rec(j).stat_sal_val_all,p_stat_sal_val_all);
2308 g_summary_rec(j).oth_comp_val_direct :=ben_cwb_utils.add_number_with_null_check
2309 (g_summary_rec(j).oth_comp_val_direct,p_oth_comp_val_direct);
2310 g_summary_rec(j).oth_comp_val_all :=ben_cwb_utils.add_number_with_null_check
2311 (g_summary_rec(j).oth_comp_val_all,p_oth_comp_val_all);
2312 g_summary_rec(j).tot_comp_val_direct :=ben_cwb_utils.add_number_with_null_check
2313 (g_summary_rec(j).tot_comp_val_direct,p_tot_comp_val_direct);
2314 g_summary_rec(j).tot_comp_val_all :=ben_cwb_utils.add_number_with_null_check
2315 (g_summary_rec(j).tot_comp_val_all,p_tot_comp_val_all);
2316 g_summary_rec(j).rec_val_direct :=ben_cwb_utils.add_number_with_null_check
2317 (g_summary_rec(j).rec_val_direct,p_rec_val_direct);
2318 g_summary_rec(j).rec_val_all :=ben_cwb_utils.add_number_with_null_check
2319 (g_summary_rec(j).rec_val_all,p_rec_val_all);
2320 g_summary_rec(j).rec_mn_val_direct :=ben_cwb_utils.add_number_with_null_check
2321 (g_summary_rec(j).rec_mn_val_direct,p_rec_mn_val_direct);
2322 g_summary_rec(j).rec_mn_val_all :=ben_cwb_utils.add_number_with_null_check
2323 (g_summary_rec(j).rec_mn_val_all,p_rec_mn_val_all);
2324 g_summary_rec(j).rec_mx_val_direct :=ben_cwb_utils.add_number_with_null_check
2325 (g_summary_rec(j).rec_mx_val_direct,p_rec_mx_val_direct);
2326 g_summary_rec(j).rec_mx_val_all :=ben_cwb_utils.add_number_with_null_check
2327 (g_summary_rec(j).rec_mx_val_all,p_rec_mx_val_all);
2328 g_summary_rec(j).misc1_val_direct :=ben_cwb_utils.add_number_with_null_check
2329 (g_summary_rec(j).misc1_val_direct,p_misc1_val_direct);
2333 (g_summary_rec(j).misc2_val_direct,p_misc2_val_direct);
2330 g_summary_rec(j).misc1_val_all :=ben_cwb_utils.add_number_with_null_check
2331 (g_summary_rec(j).misc1_val_all,p_misc1_val_all);
2332 g_summary_rec(j).misc2_val_direct :=ben_cwb_utils.add_number_with_null_check
2334 g_summary_rec(j).misc2_val_all :=ben_cwb_utils.add_number_with_null_check
2335 (g_summary_rec(j).misc2_val_all,p_misc2_val_all);
2336 g_summary_rec(j).misc3_val_direct :=ben_cwb_utils.add_number_with_null_check
2337 (g_summary_rec(j).misc3_val_direct,p_misc3_val_direct);
2338 g_summary_rec(j).misc3_val_all :=ben_cwb_utils.add_number_with_null_check
2339 (g_summary_rec(j).misc3_val_all,p_misc3_val_all);
2340 g_summary_rec(j).person_id:=nvl(p_person_id,g_summary_rec(j).person_id);
2341 g_summary_rec(j).lf_evt_ocrd_dt:=nvl(p_lf_evt_ocrd_dt,g_summary_rec(j).lf_evt_ocrd_dt);
2342 else
2343 --
2344 if g_debug then
2345 hr_utility.set_location(l_proc, 40);
2346 end if;
2347 --
2348 if nvl(g_summary_rec.count,0) > 0 then
2349 j := g_summary_rec.last + 1;
2350 else
2351 j :=1;
2352 end if;
2353 -- insert the new record.
2354 g_summary_rec(j).group_per_in_ler_id := p_group_per_in_ler_id;
2355 g_summary_rec(j).group_pl_id := p_group_pl_id;
2356 g_summary_rec(j).group_oipl_id := p_group_oipl_id;
2357 g_summary_rec(j).elig_count_direct := p_elig_count_direct;
2358 g_summary_rec(j).elig_count_all := p_elig_count_all;
2359 g_summary_rec(j).emp_recv_count_direct := p_emp_recv_count_direct;
2360 g_summary_rec(j).emp_recv_count_all := p_emp_recv_count_all;
2361 g_summary_rec(j).elig_sal_val_direct := p_elig_sal_val_direct;
2362 g_summary_rec(j).elig_sal_val_all := p_elig_sal_val_all;
2363 g_summary_rec(j).ws_val_direct := p_ws_val_direct;
2364 g_summary_rec(j).ws_val_all := p_ws_val_all;
2365 g_summary_rec(j).ws_bdgt_val_direct := p_ws_bdgt_val_direct;
2366 g_summary_rec(j).ws_bdgt_val_all := p_ws_bdgt_val_all;
2367 g_summary_rec(j).ws_bdgt_iss_val_direct := p_ws_bdgt_iss_val_direct;
2368 g_summary_rec(j).ws_bdgt_iss_val_all := p_ws_bdgt_iss_val_all;
2369 g_summary_rec(j).bdgt_val_direct := p_bdgt_val_direct;
2370 g_summary_rec(j).bdgt_iss_val_direct := p_bdgt_iss_val_direct;
2371 g_summary_rec(j).stat_sal_val_direct := p_stat_sal_val_direct;
2372 g_summary_rec(j).stat_sal_val_all := p_stat_sal_val_all;
2373 g_summary_rec(j).oth_comp_val_direct := p_oth_comp_val_direct;
2374 g_summary_rec(j).oth_comp_val_all := p_oth_comp_val_all;
2375 g_summary_rec(j).tot_comp_val_direct := p_tot_comp_val_direct;
2376 g_summary_rec(j).tot_comp_val_all := p_tot_comp_val_all;
2377 g_summary_rec(j).rec_val_direct := p_rec_val_direct;
2378 g_summary_rec(j).rec_val_all := p_rec_val_all;
2379 g_summary_rec(j).rec_mn_val_direct := p_rec_mn_val_direct;
2380 g_summary_rec(j).rec_mn_val_all := p_rec_mn_val_all;
2381 g_summary_rec(j).rec_mx_val_direct := p_rec_mx_val_direct;
2382 g_summary_rec(j).rec_mx_val_all := p_rec_mx_val_all;
2383 g_summary_rec(j).misc1_val_direct := p_misc1_val_direct;
2384 g_summary_rec(j).misc1_val_all := p_misc1_val_all;
2385 g_summary_rec(j).misc2_val_direct := p_misc2_val_direct;
2386 g_summary_rec(j).misc2_val_all := p_misc2_val_all;
2387 g_summary_rec(j).misc3_val_direct := p_misc3_val_direct;
2388 g_summary_rec(j).misc3_val_all := p_misc3_val_all;
2389 g_summary_rec(j).person_id := p_person_id;
2390 g_summary_rec(j).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
2391 end if; -- of l_found
2392 --
2393 if g_debug then
2394 hr_utility.set_location(' Leaving:'|| l_proc, 99);
2395 end if;
2396 --
2397 end; -- update_or_insert_pl_sql_tab
2398 --
2399 -- --------------------------------------------------------------------------
2400 -- |---------------------------< save_pl_sql_tab >---------------------------|
2401 -- --------------------------------------------------------------------------
2402 procedure save_pl_sql_tab is
2403 --
2404 l_proc varchar2(72) := g_package||'save_pl_sql_tab';
2405 --
2406 begin
2407 --
2408 if g_debug then
2409 hr_utility.set_location('Entering:'|| l_proc, 10);
2410 end if;
2411 --
2412 if nvl(g_summary_rec.count,0) = 0 then
2413 return;
2414 end if;
2415 --
2416 for i in g_summary_rec.first..g_summary_rec.last
2417 loop
2418 update_or_insert(g_summary_rec(i));
2419 end loop;
2420 --
2421 if g_debug then
2422 hr_utility.set_location(l_proc, 20);
2423 end if;
2424 --
2425 g_summary_rec.delete;
2426 --
2427 if g_debug then
2428 hr_utility.set_location(' Leaving:'|| l_proc, 99);
2429 end if;
2430 --
2431 end; -- save_pl_sql_tab
2432 --
2433 -- --------------------------------------------------------------------------
2434 -- |--------------------------< delete_pl_sql_tab >--------------------------|
2435 -- --------------------------------------------------------------------------
2436 procedure delete_pl_sql_tab is
2437 --
2438 l_proc varchar2(72) := g_package||'delete_pl_sql_tab';
2439 --
2440 begin
2441 --
2442 if g_debug then
2446 g_summary_rec.delete;
2443 hr_utility.set_location('Entering:'|| l_proc, 10);
2444 end if;
2445 --
2447 --
2448 if g_debug then
2449 hr_utility.set_location(' Leaving:'|| l_proc, 99);
2450 end if;
2451 --
2452 end; -- delete_pl_sql_tab
2453 --
2454 --
2455 -- --------------------------------------------------------------------------
2456 -- |--------------------------< clean_budget_data >-------------------------|
2457 -- --------------------------------------------------------------------------
2458 procedure clean_budget_data(p_per_in_ler_id in number
2459 ,p_lvl_up in number default null) is
2460 --
2461 l_proc varchar2(72) := g_package||'clean_budget_data';
2462 l_per_in_ler_id number := null;
2463 --
2464 cursor c_mgr_id is
2465 select hrchy.mgr_per_in_ler_id
2466 from ben_cwb_group_hrchy hrchy
2467 where hrchy.emp_per_in_ler_id = p_per_in_ler_id
2468 and hrchy.lvl_num = p_lvl_up;
2469 --
2470 cursor c_summary is
2471 select sum(sm.elig_count_all)-sum(sm.elig_count_direct) indirect_count
2472 ,sum(sm.elig_count_all) all_count
2473 ,max(grp.bdgt_pop_cd) bdgt_pop_cd
2474 from ben_cwb_summary sm
2475 ,ben_cwb_person_groups grp
2476 where sm.group_per_in_ler_id = l_per_in_ler_id
2477 and sm.group_oipl_id = -1
2478 and sm.group_per_in_ler_id = grp.group_per_in_ler_id
2479 and sm.group_pl_id = grp.group_pl_id
2480 and sm.group_oipl_id = grp.group_oipl_id;
2481 l_summary c_summary%rowtype;
2482 --
2483 cursor c_grps is
2484 select grp.group_pl_id
2485 ,grp.group_oipl_id
2486 ,grp.lf_evt_ocrd_dt
2487 ,grp.object_version_number
2488 ,grp.dist_bdgt_val
2489 ,grp.dist_bdgt_iss_val
2490 ,grp.ws_bdgt_val
2491 ,grp.ws_bdgt_iss_val
2492 ,grp.ws_bdgt_iss_date
2493 from ben_cwb_person_groups grp
2494 where grp.group_per_in_ler_id = l_per_in_ler_id;
2495 --
2496 cursor c_mgr_pop_cd is
2497 select grp.bdgt_pop_cd
2498 from ben_cwb_group_hrchy hrchy
2499 ,ben_cwb_person_groups grp
2500 where hrchy.emp_per_in_ler_id = l_per_in_ler_id
2501 and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
2502 and hrchy.lvl_num = 1
2503 and grp.group_oipl_id = -1;
2504 --
2505 l_is_hlm boolean := false;
2506 l_mgr_pop_cd varchar2(30) := null;
2507 --
2508 begin
2509 --
2510 if g_debug then
2511 hr_utility.set_location('Entering:'|| l_proc, 10);
2512 end if;
2513 --
2514
2515 if p_lvl_up is not null then
2516 open c_mgr_id;
2517 fetch c_mgr_id into l_per_in_ler_id;
2518 close c_mgr_id;
2519 else
2520 l_per_in_ler_id := p_per_in_ler_id;
2521 end if;
2522 --
2523 if l_per_in_ler_id is null then
2524 return;
2525 end if;
2526 --
2527 if g_debug then
2528 hr_utility.set_location(l_proc, 20);
2529 end if;
2530 --
2531 open c_summary;
2532 fetch c_summary into l_summary;
2533 close c_summary;
2534
2535 --
2536 if l_summary.indirect_count > 0 then
2537 l_is_hlm := true;
2538 end if;
2539 --
2540 if l_is_hlm and l_summary.bdgt_pop_cd is not null then
2541 -- Is now an HLM and has already done budgeting, so no status change.
2542 return;
2543 end if;
2544 --
2545 if g_debug then
2546 hr_utility.set_location(l_proc, 30);
2547 end if;
2548 --
2549 for l_grps in c_grps loop
2550
2551 if l_summary.all_count < 1 then
2552 -- No longer a manager.
2553 -- Remove the worksheet and dist bdgt.
2554 ben_cwb_person_groups_api.update_group_budget(
2555 p_group_per_in_ler_id => l_per_in_ler_id
2556 ,p_group_pl_id => l_grps.group_pl_id
2557 ,p_group_oipl_id => l_grps.group_oipl_id
2558 ,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
2559 ,p_bdgt_pop_cd => null
2560 ,p_dist_bdgt_val => null
2561 ,p_dist_bdgt_iss_val => null
2562 ,p_dist_bdgt_iss_date => null
2563 ,p_ws_bdgt_val => null
2564 ,p_ws_bdgt_iss_val => null
2565 ,p_ws_bdgt_iss_date => null
2566 ,p_object_version_number => l_grps.object_version_number
2567 ,p_perf_min_max_edit => 'N');
2568 --
2569 elsif not(l_is_hlm) and
2570 (l_grps.dist_bdgt_val is not null or
2571 l_grps.dist_bdgt_iss_val is not null) then
2572 -- Is LLM and has distribution budget.
2573 -- Null them.
2574 if g_debug then
2575 hr_utility.set_location(l_proc, 40);
2576 end if;
2577 --
2578 ben_cwb_person_groups_api.update_group_budget(
2579 p_group_per_in_ler_id => l_per_in_ler_id
2580 ,p_group_pl_id => l_grps.group_pl_id
2581 ,p_group_oipl_id => l_grps.group_oipl_id
2582 ,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
2583 ,p_bdgt_pop_cd => null
2584 ,p_dist_bdgt_val => null
2585 ,p_dist_bdgt_iss_val => null
2586 ,p_dist_bdgt_iss_date => null
2590 elsif l_is_hlm and l_summary.bdgt_pop_cd is null and
2587 ,p_object_version_number => l_grps.object_version_number
2588 ,p_perf_min_max_edit => 'N');
2589 --
2591 nvl(l_grps.dist_bdgt_val,0) = 0 and
2592 (nvl(l_grps.ws_bdgt_val,0) <> 0 or
2593 nvl(l_grps.ws_bdgt_iss_val,0) <> 0) then
2594 -- Is HLM and has Worksheet Budget and no distribution budget.
2595 -- Check if they are allowed to budget
2596 -- A person is allowed to Budget only when they are HLM and the
2597 -- manager above them have a budgeting population of "Direct Managers"
2598 if g_debug then
2599 hr_utility.set_location(l_proc, 50);
2600 end if;
2601 --
2602 open c_mgr_pop_cd;
2603 fetch c_mgr_pop_cd into l_mgr_pop_cd;
2604 close c_mgr_pop_cd;
2605
2606 if l_mgr_pop_cd = 'D' then
2607 -- Copy the worksheet budget to distribution budget.
2608 ben_cwb_person_groups_api.update_group_budget(
2609 p_group_per_in_ler_id => l_per_in_ler_id
2610 ,p_group_pl_id => l_grps.group_pl_id
2611 ,p_group_oipl_id => l_grps.group_oipl_id
2612 ,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
2613 ,p_bdgt_pop_cd => 'D'
2614 ,p_dist_bdgt_val => l_grps.ws_bdgt_val
2615 ,p_dist_bdgt_iss_val => l_grps.ws_bdgt_iss_val
2616 ,p_dist_bdgt_iss_date => l_grps.ws_bdgt_iss_date
2617 ,p_object_version_number => l_grps.object_version_number
2618 ,p_perf_min_max_edit => 'N');
2619 end if;
2620 end if;
2621
2622 end loop; --c_grps
2623
2624 if g_debug then
2625 hr_utility.set_location('Leaving:'|| l_proc, 999);
2626 end if;
2627 --
2628
2629 end clean_budget_data;
2630
2631 --
2632 -- --------------------------------------------------------------------------
2633 -- |----------------------------< reassign_mgr >-----------------------------|
2634 -- --------------------------------------------------------------------------
2635 procedure reassign_mgr(p_old_mgr_per_in_ler_id in number
2636 ,p_new_mgr_per_in_ler_id in number
2637 ,p_emp_per_in_ler_id in number) is
2638
2639
2640 -- Type delcarations for pl/sql tables
2641 type group_pl_id_type is table of
2642 ben_cwb_summary.group_pl_id%type index by binary_integer;
2643 type group_oipl_id_type is table of
2644 ben_cwb_summary.group_oipl_id%type index by binary_integer;
2645 type elig_count_direct_type is table of
2646 ben_cwb_summary.elig_count_direct%type index by binary_integer;
2647 type elig_count_all_type is table of
2648 ben_cwb_summary.elig_count_all%type index by binary_integer;
2649 type emp_recv_count_direct_type is table of
2650 ben_cwb_summary.emp_recv_count_direct%type index by binary_integer;
2651 type emp_recv_count_all_type is table of
2652 ben_cwb_summary. emp_recv_count_all%type index by binary_integer;
2653 type elig_sal_val_all_type is table of
2654 ben_cwb_summary.elig_sal_val_all%type index by binary_integer;
2655 type ws_val_direct_type is table of
2656 ben_cwb_summary.ws_val_direct%type index by binary_integer;
2657 type ws_val_all_type is table of
2658 ben_cwb_summary.ws_val_all%type index by binary_integer;
2659 type stat_sal_val_direct_type is table of
2660 ben_cwb_summary.stat_sal_val_direct%type index by binary_integer;
2661 type stat_sal_val_all_type is table of
2662 ben_cwb_summary.stat_sal_val_all%type index by binary_integer;
2663 type oth_comp_val_direct_type is table of
2664 ben_cwb_summary.oth_comp_val_direct%type index by binary_integer;
2665 type oth_comp_val_all_type is table of
2666 ben_cwb_summary.oth_comp_val_all%type index by binary_integer;
2667 type tot_comp_val_direct_type is table of
2668 ben_cwb_summary.tot_comp_val_direct%type index by binary_integer;
2669 type tot_comp_val_all_type is table of
2670 ben_cwb_summary.tot_comp_val_all%type index by binary_integer;
2671 type rec_val_direct_type is table of
2672 ben_cwb_summary.rec_val_direct%type index by binary_integer;
2673 type rec_val_all_type is table of
2674 ben_cwb_summary.rec_val_all%type index by binary_integer;
2675 type rec_mn_val_direct_type is table of
2676 ben_cwb_summary.rec_mn_val_direct%type index by binary_integer;
2677 type rec_mn_val_all_type is table of
2678 ben_cwb_summary.rec_mn_val_all%type index by binary_integer;
2679 type rec_mx_val_direct_type is table of
2680 ben_cwb_summary.rec_mx_val_direct%type index by binary_integer;
2681 type rec_mx_val_all_type is table of
2682 ben_cwb_summary.rec_mx_val_all%type index by binary_integer;
2683 type misc1_val_direct_type is table of
2684 ben_cwb_summary.misc1_val_direct%type index by binary_integer;
2685 type misc1_val_all_type is table of
2686 ben_cwb_summary.misc1_val_all%type index by binary_integer;
2687 type misc2_val_direct_type is table of
2688 ben_cwb_summary.misc2_val_direct%type index by binary_integer;
2689 type misc2_val_all_type is table of
2690 ben_cwb_summary.misc2_val_all%type index by binary_integer;
2691 type misc3_val_direct_type is table of
2692 ben_cwb_summary.misc3_val_direct%type index by binary_integer;
2693 type misc3_val_all_type is table of
2694 ben_cwb_summary.misc3_val_all%type index by binary_integer;
2698 -- declare pl/sql tables
2695 type person_id_type is table of
2696 ben_cwb_summary.person_id%type index by binary_integer;
2697 --
2699 l_rts_group_pl_id_tab group_pl_id_type;
2700 l_rts_group_oipl_id_tab group_oipl_id_type;
2701 l_all_group_pl_id_tab group_pl_id_type;
2702 l_all_group_oipl_id_tab group_oipl_id_type;
2703 --
2704 l_elig_count_tab elig_count_direct_type;
2705 l_elig_count_all_tab elig_count_all_type;
2706 l_emp_recv_count_tab emp_recv_count_direct_type;
2707 l_emp_recv_count_all_tab emp_recv_count_all_type;
2708 l_elig_sal_val_tab elig_sal_val_all_type;
2709 l_elig_sal_val_all_tab elig_sal_val_all_type;
2710 l_ws_val_tab ws_val_direct_type;
2711 l_ws_val_all_tab ws_val_all_type;
2712 l_stat_sal_val_tab stat_sal_val_direct_type;
2713 l_stat_sal_val_all_tab stat_sal_val_all_type;
2714 l_oth_comp_val_tab oth_comp_val_direct_type;
2715 l_oth_comp_val_all_tab oth_comp_val_all_type;
2716 l_tot_comp_val_tab tot_comp_val_direct_type;
2717 l_tot_comp_val_all_tab tot_comp_val_all_type;
2718 l_rec_val_tab rec_val_direct_type;
2719 l_rec_val_all_tab rec_val_all_type;
2720 l_rec_mn_val_tab rec_mn_val_direct_type;
2721 l_rec_mn_val_all_tab rec_mn_val_all_type;
2722 l_rec_mx_val_tab rec_mx_val_direct_type;
2723 l_rec_mx_val_all_tab rec_mx_val_all_type;
2724 l_misc1_val_tab misc1_val_direct_type;
2725 l_misc1_val_all_tab misc1_val_all_type;
2726 l_misc2_val_tab misc2_val_direct_type;
2727 l_misc2_val_all_tab misc2_val_all_type;
2728 l_misc3_val_tab misc3_val_direct_type;
2729 l_misc3_val_all_tab misc3_val_all_type;
2730 --
2731 l_immd_mgr number;
2732 l_last_mgr_id number;
2733 l_prsrv_bdgt_cd varchar2(1);
2734 l_uses_bdgt_flag varchar2(1);
2735 --
2736 l_proc varchar2(72) := g_package||'reassign_mgr';
2737 --
2738 begin
2739 --
2740 if g_debug then
2741 hr_utility.set_location('Entering:'|| l_proc, 10);
2742 end if;
2743 --
2744 open csr_rates(p_emp_per_in_ler_id);
2745 fetch csr_rates bulk collect into l_rts_group_pl_id_tab
2746 ,l_rts_group_oipl_id_tab
2747 ,l_elig_count_tab
2748 ,l_emp_recv_count_tab
2749 ,l_elig_sal_val_tab
2750 ,l_ws_val_tab
2751 ,l_stat_sal_val_tab
2752 ,l_oth_comp_val_tab
2753 ,l_tot_comp_val_tab
2754 ,l_rec_val_tab
2755 ,l_rec_mn_val_tab
2756 ,l_rec_mx_val_tab
2757 ,l_misc1_val_tab
2758 ,l_misc2_val_tab
2759 ,l_misc3_val_tab;
2760 close csr_rates;
2761
2762 open csr_summary(p_emp_per_in_ler_id);
2763 fetch csr_summary bulk collect into l_all_group_pl_id_tab
2764 ,l_all_group_oipl_id_tab
2765 ,l_elig_count_all_tab
2766 ,l_emp_recv_count_all_tab
2767 ,l_elig_sal_val_all_tab
2768 ,l_ws_val_all_tab
2769 ,l_stat_sal_val_all_tab
2770 ,l_oth_comp_val_all_tab
2771 ,l_tot_comp_val_all_tab
2772 ,l_rec_val_all_tab
2773 ,l_rec_mn_val_all_tab
2774 ,l_rec_mx_val_all_tab
2775 ,l_misc1_val_all_tab
2776 ,l_misc2_val_all_tab
2777 ,l_misc3_val_all_tab;
2778 close csr_summary;
2779 --
2780 if nvl(l_rts_group_pl_id_tab.count,0) = 0 then
2781 -- though the person is not having person_rates record, the person
2782 -- may be having eligible employees reporting to him. so insert 0
2783 for j in l_all_group_pl_id_tab.first .. l_all_group_pl_id_tab.last
2784 loop
2785 l_rts_group_pl_id_tab(j) := l_all_group_pl_id_tab(j);
2786 l_rts_group_oipl_id_tab(j) := l_all_group_oipl_id_tab(j);
2787 l_elig_count_tab(j) := 0;
2788 l_emp_recv_count_tab(j) := 0;
2789 l_elig_sal_val_tab(j) := null;
2790 l_ws_val_tab(j) := null;
2791 l_stat_sal_val_tab(j) := null;
2792 l_oth_comp_val_tab(j) := null;
2793 l_tot_comp_val_tab(j) := null;
2794 l_rec_val_tab(j) := null;
2795 l_rec_mn_val_tab(j) := null;
2796 l_rec_mx_val_tab(j) := null;
2797 l_misc1_val_tab(j) := null;
2798 l_misc2_val_tab(j) := null;
2799 l_misc3_val_tab(j) :=null;
2800 end loop;
2801 end if;
2802 --
2803 if nvl(l_rts_group_pl_id_tab.count,0) = 0 then
2804 --
2805 if g_debug then
2806 hr_utility.set_location(' Leaving:'|| l_proc, 99);
2807 end if;
2808 -- no records to process. return
2809 return;
2810 end if;
2811 --
2812 if g_debug then
2813 hr_utility.set_location(l_proc, 20);
2814 end if;
2815 --
2816 l_immd_mgr := 1;
2817 for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
2818 ,p_new_mgr_per_in_ler_id) loop
2819 --
2820 if g_debug then
2821 hr_utility.set_location(l_proc, 30);
2822 end if;
2823 --
2827 --
2824 if g_debug then
2825 hr_utility.set_location(l_proc, 40);
2826 end if;
2828 for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
2829 loop
2830 update_or_insert_pl_sql_tab
2831 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
2832 ,p_group_pl_id => l_rts_group_pl_id_tab(i)
2833 ,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
2834 ,p_elig_count_direct => -(l_elig_count_tab(i)) *
2835 l_immd_mgr
2836 ,p_elig_count_all => -(nvl(l_elig_count_tab(i),0)
2837 + nvl(l_elig_count_all_tab(i),0))
2838 ,p_emp_recv_count_direct => -(l_emp_recv_count_tab(i)) *
2839 l_immd_mgr
2840 ,p_emp_recv_count_all =>
2841 -(nvl(l_emp_recv_count_tab(i),0) +
2842 nvl(l_emp_recv_count_all_tab(i),0))
2843 ,p_elig_sal_val_direct => -(l_elig_sal_val_tab(i)) *
2844 l_immd_mgr
2845 ,p_elig_sal_val_all => - ben_cwb_utils.add_number_with_null_check
2846 (l_elig_sal_val_tab(i),
2847 l_elig_sal_val_all_tab(i))
2848 ,p_ws_val_direct =>-(l_ws_val_tab(i)) * l_immd_mgr
2849 ,p_ws_val_all => - ben_cwb_utils.add_number_with_null_check
2850 (l_ws_val_tab(i),
2851 l_ws_val_all_tab(i))
2852 ,p_stat_sal_val_direct => -(l_stat_sal_val_tab(i)) *
2853 l_immd_mgr
2854 ,p_stat_sal_val_all => - ben_cwb_utils.add_number_with_null_check
2855 (l_stat_sal_val_tab(i),
2856 l_stat_sal_val_all_tab(i))
2857 ,p_oth_comp_val_direct => -(l_oth_comp_val_tab(i)) *
2858 l_immd_mgr
2859 ,p_oth_comp_val_all => - ben_cwb_utils.add_number_with_null_check
2860 (l_oth_comp_val_tab(i),
2861 l_oth_comp_val_all_tab(i))
2862 ,p_tot_comp_val_direct => -(l_tot_comp_val_tab(i)) *
2863 l_immd_mgr
2864 ,p_tot_comp_val_all => - ben_cwb_utils.add_number_with_null_check
2865 (l_tot_comp_val_tab(i),
2866 l_tot_comp_val_all_tab(i))
2867 ,p_rec_val_direct => -(l_rec_val_tab(i)) *
2868 l_immd_mgr
2869 ,p_rec_val_all => - ben_cwb_utils.add_number_with_null_check
2870 (l_rec_val_tab(i),
2871 l_rec_val_all_tab(i))
2872 ,p_rec_mn_val_direct => -(l_rec_mn_val_tab(i)) *
2873 l_immd_mgr
2874 ,p_rec_mn_val_all => - ben_cwb_utils.add_number_with_null_check
2875 (l_rec_mn_val_tab(i),
2876 l_rec_mn_val_all_tab(i))
2877 ,p_rec_mx_val_direct => -(l_rec_mx_val_tab(i)) *
2878 l_immd_mgr
2879 ,p_rec_mx_val_all => - ben_cwb_utils.add_number_with_null_check
2880 (l_rec_mx_val_tab(i),
2881 l_rec_mx_val_all_tab(i))
2882 ,p_misc1_val_direct => -(l_misc1_val_tab(i)) *
2883 l_immd_mgr
2884 ,p_misc1_val_all => - ben_cwb_utils.add_number_with_null_check
2885 (l_misc1_val_tab(i),
2886 l_misc1_val_all_tab(i))
2887 ,p_misc2_val_direct => -(l_misc2_val_tab(i)) *
2888 l_immd_mgr
2889 ,p_misc2_val_all => - ben_cwb_utils.add_number_with_null_check
2890 (l_misc2_val_tab(i),
2891 l_misc2_val_all_tab(i))
2892 ,p_misc3_val_direct => -(l_misc3_val_all_tab(i)) *
2893 l_immd_mgr
2894 ,p_misc3_val_all => - ben_cwb_utils.add_number_with_null_check
2895 (l_misc3_val_tab(i),
2896 l_misc3_val_all_tab(i))
2897 );
2898 end loop;
2899 --
2900 if g_debug then
2901 hr_utility.set_location(l_proc, 50);
2902 end if;
2903 --
2904 -- Now change the l_immd_mgr value to false
2905 l_immd_mgr := 0;
2906 end loop; -- of csr_mgr_ids cursor
2907 --
2908 if g_debug then
2909 hr_utility.set_location(l_proc, 60);
2910 end if;
2911 --
2912
2913 -- Now add the values to new manager hierarchy
2914 l_immd_mgr := 1;
2915 for mgr in csr_mgr_ids(p_new_mgr_per_in_ler_id
2916 ,p_old_mgr_per_in_ler_id) loop
2917 --
2918 if g_debug then
2919 hr_utility.set_location(l_proc, 70);
2920 end if;
2921 for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
2922 loop
2926 ,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
2923 update_or_insert_pl_sql_tab
2924 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
2925 ,p_group_pl_id => l_rts_group_pl_id_tab(i)
2927 ,p_elig_count_direct => (l_elig_count_tab(i)) *
2928 l_immd_mgr
2929 ,p_elig_count_all => (nvl(l_elig_count_tab(i),0)
2930 + nvl(l_elig_count_all_tab(i),0))
2931 ,p_emp_recv_count_direct => (l_emp_recv_count_tab(i)) *
2932 l_immd_mgr
2933 ,p_emp_recv_count_all =>
2934 (nvl(l_emp_recv_count_tab(i),0) +
2935 nvl(l_emp_recv_count_all_tab(i),0))
2936 ,p_elig_sal_val_direct => (l_elig_sal_val_tab(i)) *
2937 l_immd_mgr
2938 ,p_elig_sal_val_all => ben_cwb_utils.add_number_with_null_check
2939 (l_elig_sal_val_tab(i),
2940 l_elig_sal_val_all_tab(i))
2941 ,p_ws_val_direct =>(l_ws_val_tab(i)) * l_immd_mgr
2942 ,p_ws_val_all => ben_cwb_utils.add_number_with_null_check
2943 (l_ws_val_tab(i),
2944 l_ws_val_all_tab(i))
2945 ,p_stat_sal_val_direct => (l_stat_sal_val_tab(i)) *
2946 l_immd_mgr
2947 ,p_stat_sal_val_all => ben_cwb_utils.add_number_with_null_check
2948 (l_stat_sal_val_tab(i),
2949 l_stat_sal_val_all_tab(i))
2950 ,p_oth_comp_val_direct => (l_oth_comp_val_tab(i)) *
2951 l_immd_mgr
2952 ,p_oth_comp_val_all => ben_cwb_utils.add_number_with_null_check
2953 (l_oth_comp_val_tab(i),
2954 l_oth_comp_val_all_tab(i))
2955 ,p_tot_comp_val_direct => (l_tot_comp_val_tab(i)) *
2956 l_immd_mgr
2957 ,p_tot_comp_val_all => ben_cwb_utils.add_number_with_null_check
2958 (l_tot_comp_val_tab(i),
2959 l_tot_comp_val_all_tab(i))
2960 ,p_rec_val_direct => (l_rec_val_tab(i)) *
2961 l_immd_mgr
2962 ,p_rec_val_all => ben_cwb_utils.add_number_with_null_check
2963 (l_rec_val_tab(i),
2964 l_rec_val_all_tab(i))
2965 ,p_rec_mn_val_direct => (l_rec_mn_val_tab(i)) *
2966 l_immd_mgr
2967 ,p_rec_mn_val_all => ben_cwb_utils.add_number_with_null_check
2968 (l_rec_mn_val_tab(i),
2969 l_rec_mn_val_all_tab(i))
2970 ,p_rec_mx_val_direct => (l_rec_mx_val_tab(i)) *
2971 l_immd_mgr
2972 ,p_rec_mx_val_all => ben_cwb_utils.add_number_with_null_check
2973 (l_rec_mx_val_tab(i),
2974 l_rec_mx_val_all_tab(i))
2975 ,p_misc1_val_direct => (l_misc1_val_tab(i)) *
2976 l_immd_mgr
2977 ,p_misc1_val_all => ben_cwb_utils.add_number_with_null_check
2978 (l_misc1_val_tab(i),
2979 l_misc1_val_all_tab(i))
2980 ,p_misc2_val_direct => (l_misc2_val_tab(i)) *
2981 l_immd_mgr
2982 ,p_misc2_val_all => ben_cwb_utils.add_number_with_null_check
2983 (l_misc2_val_tab(i),
2984 l_misc2_val_all_tab(i))
2985 ,p_misc3_val_direct => (l_misc3_val_all_tab(i)) *
2986 l_immd_mgr
2987 ,p_misc3_val_all => ben_cwb_utils.add_number_with_null_check
2988 (l_misc3_val_tab(i),
2989 l_misc3_val_all_tab(i))
2990 );
2991 end loop;
2992 --
2993 if g_debug then
2994 hr_utility.set_location(l_proc, 80);
2995 end if;
2996 --
2997 --
2998 -- Now change the l_immd_mgr value to false
2999 l_immd_mgr := 0;
3000 end loop; -- of csr_mgr_ids cursor
3001 --
3002 if g_debug then
3003 hr_utility.set_location(l_proc, 90);
3004 end if;
3005 --
3006 -- Bug 3812624. Added nvl to p_new_mgr_per_in_ler_id. Atleast one of them
3007 -- is always not null.
3008
3009 --
3010 -- Now update the budgets
3011 --
3012 select pl.prsrv_bdgt_cd
3013 ,pl.uses_bdgt_flag
3014 into l_prsrv_bdgt_cd
3015 ,l_uses_bdgt_flag
3016 from ben_cwb_pl_dsgn pl
3017 ,ben_cwb_person_groups grp
3018 where grp.group_per_in_ler_id = nvl(p_new_mgr_per_in_ler_id,p_old_mgr_per_in_ler_id)
3019 and grp.group_oipl_id = -1
3020 and pl.pl_id = grp.group_pl_id
3021 and pl.oipl_id = grp.group_oipl_id
3025 if g_debug then
3022 and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
3023 --
3024 --
3026 hr_utility.set_location(l_proc, 100);
3027 end if;
3028 --
3029 if l_uses_bdgt_flag = 'N' then
3030 --
3031 if g_debug then
3032 hr_utility.set_location(l_proc, 109);
3033 end if;
3034 --
3035 -- Budgets are not used. So no processing required.
3036 return;
3037 end if;
3038 --
3039 -- call the save_pl_sql_tab to transfer the data in the pl/sql tab
3040 -- to the database. This is required to for computing the budgets
3041 -- correctly.
3042 save_pl_sql_tab;
3043 --
3044 if g_debug then
3045 hr_utility.set_location(l_proc, 110);
3046 end if;
3047 --
3048 -- update the budgets in the old mgr hiearchy
3049 --
3050 for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
3051 ,p_new_mgr_per_in_ler_id)
3052 loop
3053 --
3054 if g_debug then
3055 hr_utility.set_location(l_proc, 120);
3056 end if;
3057 --
3058 -- If lvl_num = 0,1 , do everything
3059 -- For lvl_num > 1, only all.
3060 --
3061 -- call update_budgets_summary to update the budgets for this manager
3062 --
3063 update_budgets_summary(mgr.mgr_per_in_ler_id
3064 ,l_prsrv_bdgt_cd
3065 ,(mgr.lvl_num > 1));
3066 end loop;
3067 --
3068 if g_debug then
3069 hr_utility.set_location(l_proc, 130);
3070 end if;
3071 --
3072 -- update the budgets in the new mgr hiearchy
3073 --
3074 l_last_mgr_id :=null;
3075 for mgr in csr_mgr_ids(p_new_mgr_per_in_ler_id
3076 ,p_old_mgr_per_in_ler_id)
3077 loop
3078 --
3079 if g_debug then
3080 hr_utility.set_location(l_proc, 140);
3081 end if;
3082 --
3083 -- If lvl_num = 0,1 , do everything
3084 -- For lvl_num > 1, only all.
3085 --
3086 -- call update_budgets_summary to update the budgets for this manager
3087 --
3088 update_budgets_summary(mgr.mgr_per_in_ler_id
3089 ,l_prsrv_bdgt_cd
3090 ,(mgr.lvl_num > 1));
3091 -- take the mgr id in l_last_mgr_id
3092 l_last_mgr_id := mgr.mgr_per_in_ler_id;
3093 end loop;
3094 --
3095 if g_debug then
3096 hr_utility.set_location(l_proc, 150);
3097 end if;
3098 --
3099 -- when budgets are stored as % we need to update the ws_bdgt_val_all
3100 -- till the top
3101 if l_prsrv_bdgt_cd = 'P' then
3102 --
3103 if g_debug then
3104 hr_utility.set_location(l_proc, 160);
3105 end if;
3106 --
3107 for l_mgrs in csr_mgrs(l_last_mgr_id) loop
3108 --
3109 if g_debug then
3110 hr_utility.set_location(l_proc, 170);
3111 end if;
3112 --
3113 -- lvl_num = 1, do everything.
3114 -- when lvl_num > 1, then only update budgets for all.
3115 --
3116 update_budgets_summary(l_mgrs.mgr_per_in_ler_id
3117 ,l_prsrv_bdgt_cd
3118 ,(l_mgrs.lvl_num > 1));
3119 --
3120 end loop;
3121 end if; -- of prsrv_bdgt_cd
3122 --
3123 -- Clean Budget data for old and new managers. Their role may
3124 -- have changed from LLM to HLM or HLM to LLM.
3125 --
3126 if g_debug then
3127 hr_utility.set_location(l_proc, 180);
3128 end if;
3129 --
3130 clean_budget_data(p_per_in_ler_id => p_old_mgr_per_in_ler_id);
3131 clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id);
3132 save_pl_sql_tab;
3133 --
3134 if g_debug then
3135 hr_utility.set_location(' Leaving:'|| l_proc, 999);
3136 end if;
3137 --
3138 end; -- end of reassign_mgr
3139 --
3140 --
3141 -- --------------------------------------------------------------------------
3142 -- |----------------------------< reassign_emp >-----------------------------|
3143 -- --------------------------------------------------------------------------
3144 procedure reassign_emp(p_old_mgr_per_in_ler_id in number
3145 ,p_new_mgr_per_in_ler_id in number
3146 ,p_emp_per_in_ler_id in number) is
3147
3148 -- Type delcarations for pl/sql tables
3149 type group_pl_id_type is table of
3150 ben_cwb_summary.group_pl_id%type index by binary_integer;
3151 type group_oipl_id_type is table of
3152 ben_cwb_summary.group_oipl_id%type index by binary_integer;
3153 type elig_count_direct_type is table of
3154 ben_cwb_summary.elig_count_direct%type index by binary_integer;
3155 type emp_recv_count_direct_type is table of
3156 ben_cwb_summary.emp_recv_count_direct%type index by binary_integer;
3157 type elig_sal_val_direct_type is table of
3158 ben_cwb_summary.elig_sal_val_direct%type index by binary_integer;
3159 type ws_val_direct_type is table of
3160 ben_cwb_summary.ws_val_direct%type index by binary_integer;
3161 type stat_sal_val_direct_type is table of
3162 ben_cwb_summary.stat_sal_val_direct%type index by binary_integer;
3163 type oth_comp_val_direct_type is table of
3164 ben_cwb_summary.oth_comp_val_direct%type index by binary_integer;
3168 ben_cwb_summary.rec_val_direct%type index by binary_integer;
3165 type tot_comp_val_direct_type is table of
3166 ben_cwb_summary.tot_comp_val_direct%type index by binary_integer;
3167 type rec_val_direct_type is table of
3169 type rec_mn_val_direct_type is table of
3170 ben_cwb_summary.rec_mn_val_direct%type index by binary_integer;
3171 type rec_mx_val_direct_type is table of
3172 ben_cwb_summary.rec_mx_val_direct%type index by binary_integer;
3173 type misc1_val_direct_type is table of
3174 ben_cwb_summary.misc1_val_direct%type index by binary_integer;
3175 type misc2_val_direct_type is table of
3176 ben_cwb_summary.misc2_val_direct%type index by binary_integer;
3177 type misc3_val_direct_type is table of
3178 ben_cwb_summary.misc3_val_direct%type index by binary_integer;
3179 -- declare pl/sql tables
3180 l_rts_group_pl_id_tab group_pl_id_type;
3181 l_rts_group_oipl_id_tab group_oipl_id_type;
3182 l_grp_group_pl_id_tab group_pl_id_type;
3183 l_grp_group_oipl_id_tab group_oipl_id_type;
3184 --
3185 l_elig_count_tab elig_count_direct_type;
3186 l_emp_recv_count_tab emp_recv_count_direct_type;
3187 l_elig_sal_val_tab elig_sal_val_direct_type;
3188 l_ws_val_tab ws_val_direct_type;
3189 l_stat_sal_val_tab stat_sal_val_direct_type;
3190 l_oth_comp_val_tab oth_comp_val_direct_type;
3191 l_tot_comp_val_tab tot_comp_val_direct_type;
3192 l_rec_val_tab rec_val_direct_type;
3193 l_rec_mn_val_tab rec_mn_val_direct_type;
3194 l_rec_mx_val_tab rec_mx_val_direct_type;
3195 l_misc1_val_tab misc1_val_direct_type;
3196 l_misc2_val_tab misc2_val_direct_type;
3197 l_misc3_val_tab misc3_val_direct_type;
3198 --
3199 l_immd_mgr number;
3200 l_last_mgr_id number;
3201 l_prsrv_bdgt_cd varchar2(1);
3202 l_uses_bdgt_flag varchar2(1);
3203 --
3204 l_ws_bdgt_val number;
3205 l_ws_bdgt_iss_val number;
3206 l_bdgt_val number;
3207 l_bdgt_iss_val number;
3208 --
3209 l_proc varchar2(72) := g_package||'reassign_emp';
3210 --
3211 begin
3212 --
3213 if g_debug then
3214 hr_utility.set_location('Entering:'|| l_proc, 10);
3215 end if;
3216 --
3217 open csr_rates(p_emp_per_in_ler_id);
3218 fetch csr_rates bulk collect into l_rts_group_pl_id_tab
3219 ,l_rts_group_oipl_id_tab
3220 ,l_elig_count_tab
3221 ,l_emp_recv_count_tab
3222 ,l_elig_sal_val_tab
3223 ,l_ws_val_tab
3224 ,l_stat_sal_val_tab
3225 ,l_oth_comp_val_tab
3226 ,l_tot_comp_val_tab
3227 ,l_rec_val_tab
3228 ,l_rec_mn_val_tab
3229 ,l_rec_mx_val_tab
3230 ,l_misc1_val_tab
3231 ,l_misc2_val_tab
3232 ,l_misc3_val_tab;
3233 close csr_rates;
3234 --
3235 if nvl(l_rts_group_pl_id_tab.count,0) = 0 then
3236 --
3237 if g_debug then
3238 hr_utility.set_location(' Leaving:'|| l_proc, 99);
3239 end if;
3240 --
3241 return;
3242 end if;
3243 --
3244 if g_debug then
3245 hr_utility.set_location(l_proc, 20);
3246 end if;
3247 --
3248 l_immd_mgr := 1;
3249 for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
3250 ,p_new_mgr_per_in_ler_id)
3251 loop
3252 --
3253 if g_debug then
3254 hr_utility.set_location(l_proc, 30);
3255 end if;
3256 --
3257 --
3258 for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
3259 loop
3260 update_or_insert_pl_sql_tab
3261 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
3262 ,p_group_pl_id => l_rts_group_pl_id_tab(i)
3263 ,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
3264 ,p_elig_count_direct => -(l_elig_count_tab(i)) *
3265 l_immd_mgr
3266 ,p_elig_count_all => -(l_elig_count_tab(i))
3267 ,p_emp_recv_count_direct => -(l_emp_recv_count_tab(i))
3268 * l_immd_mgr
3269 ,p_emp_recv_count_all => -(l_emp_recv_count_tab(i))
3270 ,p_elig_sal_val_direct => -(l_elig_sal_val_tab(i)) *
3271 l_immd_mgr
3272 ,p_elig_sal_val_all => -(l_elig_sal_val_tab(i))
3273 ,p_ws_val_direct => -(l_ws_val_tab(i)) *
3274 l_immd_mgr
3275 ,p_ws_val_all => -(l_ws_val_tab(i))
3276 ,p_stat_sal_val_direct => -(l_stat_sal_val_tab(i)) *
3277 l_immd_mgr
3278 ,p_stat_sal_val_all => -(l_stat_sal_val_tab(i))
3279 ,p_oth_comp_val_direct => -(l_oth_comp_val_tab(i)) *
3280 l_immd_mgr
3281 ,p_oth_comp_val_all => -(l_oth_comp_val_tab(i))
3282 ,p_tot_comp_val_direct => -(l_tot_comp_val_tab(i)) *
3283 l_immd_mgr
3284 ,p_tot_comp_val_all => -(l_tot_comp_val_tab(i))
3285 ,p_rec_val_direct => -(l_rec_val_tab(i)) *
3286 l_immd_mgr
3290 ,p_rec_mn_val_all => -(l_rec_mn_val_tab(i))
3287 ,p_rec_val_all => -(l_rec_val_tab(i))
3288 ,p_rec_mn_val_direct => -(l_rec_mn_val_tab(i)) *
3289 l_immd_mgr
3291 ,p_rec_mx_val_direct => -(l_rec_mx_val_tab(i)) *
3292 l_immd_mgr
3293 ,p_rec_mx_val_all => -(l_rec_mx_val_tab(i))
3294 ,p_misc1_val_direct => -(l_misc1_val_tab(i)) *
3295 l_immd_mgr
3296 ,p_misc1_val_all => -(l_misc1_val_tab(i))
3297 ,p_misc2_val_direct => -(l_misc2_val_tab(i)) *
3298 l_immd_mgr
3299 ,p_misc2_val_all => -(l_misc2_val_tab(i))
3300 ,p_misc3_val_direct => -(l_misc3_val_tab(i)) *
3301 l_immd_mgr
3302 ,p_misc3_val_all => -(l_misc3_val_tab(i))
3303 );
3304 end loop;
3305 --
3306 if g_debug then
3307 hr_utility.set_location(l_proc, 40);
3308 end if;
3309 --
3310 -- Now change the l_immd_mgr value to false
3311 l_immd_mgr := 0;
3312 end loop; -- of csr_mgr_ids cursor
3313 --
3314 if g_debug then
3315 hr_utility.set_location(l_proc, 50);
3316 end if;
3317 --
3318
3319 -- Now add the values to new manager hierarchy
3320 l_immd_mgr := 1;
3321 for mgr in csr_mgr_ids(p_new_mgr_per_in_ler_id
3322 ,p_old_mgr_per_in_ler_id)
3323 loop
3324 --
3325 if g_debug then
3326 hr_utility.set_location(l_proc, 60);
3327 end if;
3328 --
3329 for i in l_rts_group_pl_id_tab.first..l_rts_group_pl_id_tab.last
3330 loop
3331 update_or_insert_pl_sql_tab
3332 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
3333 ,p_group_pl_id => l_rts_group_pl_id_tab(i)
3334 ,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
3335 ,p_elig_count_direct => (l_elig_count_tab(i)) *
3336 l_immd_mgr
3337 ,p_elig_count_all => (l_elig_count_tab(i))
3338 ,p_emp_recv_count_direct => (l_emp_recv_count_tab(i))
3339 * l_immd_mgr
3340 ,p_emp_recv_count_all => (l_emp_recv_count_tab(i))
3341 ,p_elig_sal_val_direct => (l_elig_sal_val_tab(i)) *
3342 l_immd_mgr
3343 ,p_elig_sal_val_all => (l_elig_sal_val_tab(i))
3344 ,p_ws_val_direct => (l_ws_val_tab(i)) *
3345 l_immd_mgr
3346 ,p_ws_val_all => (l_ws_val_tab(i))
3347 ,p_stat_sal_val_direct => (l_stat_sal_val_tab(i)) *
3348 l_immd_mgr
3349 ,p_stat_sal_val_all => (l_stat_sal_val_tab(i))
3350 ,p_oth_comp_val_direct => (l_oth_comp_val_tab(i)) *
3351 l_immd_mgr
3352 ,p_oth_comp_val_all => (l_oth_comp_val_tab(i))
3353 ,p_tot_comp_val_direct => (l_tot_comp_val_tab(i)) *
3354 l_immd_mgr
3355 ,p_tot_comp_val_all => (l_tot_comp_val_tab(i))
3356 ,p_rec_val_direct => (l_rec_val_tab(i)) *
3357 l_immd_mgr
3358 ,p_rec_val_all => (l_rec_val_tab(i))
3359 ,p_rec_mn_val_direct => (l_rec_mn_val_tab(i)) *
3360 l_immd_mgr
3361 ,p_rec_mn_val_all => (l_rec_mn_val_tab(i))
3362 ,p_rec_mx_val_direct => (l_rec_mx_val_tab(i)) *
3363 l_immd_mgr
3364 ,p_rec_mx_val_all => (l_rec_mx_val_tab(i))
3365 ,p_misc1_val_direct => (l_misc1_val_tab(i)) *
3366 l_immd_mgr
3367 ,p_misc1_val_all => (l_misc1_val_tab(i))
3368 ,p_misc2_val_direct => (l_misc2_val_tab(i)) *
3369 l_immd_mgr
3370 ,p_misc2_val_all => (l_misc2_val_tab(i))
3371 ,p_misc3_val_direct => (l_misc3_val_tab(i)) *
3372 l_immd_mgr
3373 ,p_misc3_val_all => (l_misc3_val_tab(i))
3374 );
3375 end loop;
3376 --
3377 if g_debug then
3378 hr_utility.set_location(l_proc, 70);
3379 end if;
3380 --
3381 --
3382 -- Now change the l_immd_mgr value to false
3383 l_immd_mgr := 0;
3384 end loop; -- of csr_mgr_ids cursor
3385 --
3386 if g_debug then
3387 hr_utility.set_location(l_proc, 80);
3388 end if;
3389 --
3390 -- if the Prsrv Bdgt Cd is P then, the managers budget also need to
3391 -- updated.
3392 select pl.prsrv_bdgt_cd
3393 ,pl.uses_bdgt_flag
3394 into l_prsrv_bdgt_cd
3395 ,l_uses_bdgt_flag
3396 from ben_cwb_pl_dsgn pl
3397 ,ben_cwb_person_groups grp
3398 where grp.group_per_in_ler_id = nvl(p_new_mgr_per_in_ler_id, p_old_mgr_per_in_ler_id)
3399 and grp.group_oipl_id = -1
3400 and pl.pl_id = grp.group_pl_id
3401 and pl.oipl_id = grp.group_oipl_id
3402 and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
3403 --
3404 if g_debug then
3408 if l_uses_bdgt_flag = 'N' then
3405 hr_utility.set_location(l_proc, 90);
3406 end if;
3407 --
3409 --
3410 if g_debug then
3411 hr_utility.set_location(l_proc, 99);
3412 end if;
3413 --
3414 -- Budgets are not used. So no process required.
3415 return;
3416 --
3417 end if;
3418 --
3419 -- call the save_pl_sql_tab to transfer the data in the pl/sql tab
3420 -- to the database. This is required to for computing the budgets
3421 -- correctly.
3422 save_pl_sql_tab;
3423 --
3424 if l_prsrv_bdgt_cd = 'P' then
3425 --
3426 if g_debug then
3427 hr_utility.set_location(l_proc, 100);
3428 end if;
3429 --
3430 for mgr in csr_mgr_ids(p_old_mgr_per_in_ler_id
3431 ,p_new_mgr_per_in_ler_id)
3432 loop
3433 --
3434 if g_debug then
3435 hr_utility.set_location(l_proc, 110);
3436 end if;
3437 --
3438 -- lvl_num = 0, ignore that row as emps do not have budget.
3439 -- lvl_num = 1, do everything.
3440 -- when lvl_num > 1, then only update budgets for all.
3441
3442 --
3443 -- call update_budgets_summary to update the budgets for this manager
3444 --
3445 if mgr.lvl_num > 0 then
3446 update_budgets_summary(mgr.mgr_per_in_ler_id
3447 ,l_prsrv_bdgt_cd
3448 ,(mgr.lvl_num>1));
3449 end if;
3450 end loop;
3451 --
3452 if g_debug then
3453 hr_utility.set_location(l_proc, 120);
3454 end if;
3455 --
3456 -- update the budgets in the new mgr hiearchy
3457 --
3458 l_last_mgr_id :=null;
3459 for mgr in csr_mgr_ids(p_new_mgr_per_in_ler_id
3460 ,p_old_mgr_per_in_ler_id)
3461 loop
3462 --
3463 if g_debug then
3464 hr_utility.set_location(l_proc, 130);
3465 end if;
3466 --
3467 -- lvl_num = 0, ignore that row as emps do not have budget.
3468 -- lvl_num = 1, do everything.
3469 -- when lvl_num > 1, then only update budgets for all.
3470
3471 --
3472 -- call update_budgets_summary to update the budgets for this manager
3473 --
3474 if mgr.lvl_num > 0 then
3475 update_budgets_summary(mgr.mgr_per_in_ler_id
3476 ,l_prsrv_bdgt_cd
3477 ,(mgr.lvl_num>1));
3478 end if;
3479 -- take the mgr id in l_last_mgr_id
3480 l_last_mgr_id := mgr.mgr_per_in_ler_id;
3481 end loop;
3482 --
3483 if g_debug then
3484 hr_utility.set_location(l_proc, 140);
3485 end if;
3486 --
3487 -- when budgets are stored as % the ws_bdgt_val_all will be affected
3488 -- till top
3489 for l_mgrs in csr_mgrs(l_last_mgr_id) loop
3490 --
3491 if g_debug then
3492 hr_utility.set_location(l_proc, 150);
3493 end if;
3494 --
3495 -- lvl_num = 1, do everything.
3496 -- when lvl_num > 1, then only update budgets for all.
3497 --
3498 update_budgets_summary(l_mgrs.mgr_per_in_ler_id
3499 ,l_prsrv_bdgt_cd
3500 ,(l_mgrs.lvl_num>1));
3501 --
3502 end loop;
3503 --
3504 end if; -- of if prsrv_bdgt_cd
3505 --
3506 -- If the reassigned employee is not a manager,
3507 -- then the budget cleanup is needed for old and
3508 -- new manager's manager
3509 --
3510 if g_debug then
3511 hr_utility.set_location(l_proc, 160);
3512 end if;
3513 --
3514 clean_budget_data(p_per_in_ler_id => p_old_mgr_per_in_ler_id);
3515 clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id);
3516 save_pl_sql_tab;
3517
3518 clean_budget_data(p_per_in_ler_id => p_old_mgr_per_in_ler_id
3519 ,p_lvl_up => 1);
3520 clean_budget_data(p_per_in_ler_id => p_new_mgr_per_in_ler_id
3521 ,p_lvl_up => 1);
3522 save_pl_sql_tab;
3523 --
3524 if g_debug then
3525 hr_utility.set_location(' Leaving:'|| l_proc, 999);
3526 end if;
3527 --
3528 end; -- end of reassign_emp
3529 --
3530 -- --------------------------------------------------------------------------
3531 -- |-------------------< update_summary_on_reassignment >--------------------|
3532 -- --------------------------------------------------------------------------
3533 procedure update_summary_on_reassignment(p_old_mgr_per_in_ler_id in number
3534 ,p_new_mgr_per_in_ler_id in number
3535 ,p_emp_per_in_ler_id in number) is
3536 --
3537 l_is_mgr varchar2(1);
3538 l_dummy varchar2(1);
3539 l_insert_old_mgr boolean;
3540 --
3541 l_proc varchar2(72) := g_package||'update_summary_on_reassignment';
3542 --
3543 begin
3544 --
3545 if g_debug then
3546 hr_utility.set_location('Entering:'|| l_proc, 10);
3547 end if;
3548 --
3549 --check if the emp was a manager earlier
3550 begin
3551 select null into l_dummy
3552 from ben_cwb_group_hrchy
3556 l_is_mgr := 'Y';
3553 where mgr_per_in_ler_id = p_emp_per_in_ler_id
3554 and lvl_num = 0;
3555 --
3557 exception
3558 when no_data_found then
3559 l_is_mgr := 'N';
3560 end;
3561 --
3562 if g_debug then
3563 hr_utility.set_location(l_proc, 20);
3564 end if;
3565
3566 --
3567 -- if the old_mgr_pil is already deleted from group_hrchy then insert
3568 -- it. The row will be used the reassign_mgr and reassign_emp. But if
3569 -- the old_mgr_pil is null(i.e., we are re-assigning the top level mgr
3570 -- of one hierarchy to another person) dont insert the rec.
3571 if (p_old_mgr_per_in_ler_id is not null) then
3572 begin
3573 select null into l_dummy
3574 from ben_cwb_group_hrchy hrchy
3575 where emp_per_in_ler_id = p_old_mgr_per_in_ler_id
3576 and lvl_num = 0;
3577 --
3578 if g_debug then
3579 hr_utility.set_location(l_proc, 30);
3580 end if;
3581 --
3582 exception
3583 when no_data_found then
3584 -- row is not present;
3585 insert into ben_cwb_group_hrchy(mgr_per_in_ler_id
3586 ,emp_per_in_ler_id
3587 ,lvl_num)
3588 values(p_old_mgr_per_in_ler_id
3589 ,p_old_mgr_per_in_ler_id
3590 ,0);
3591 -- set the flag
3592 l_insert_old_mgr :=true;
3593 --
3594 if g_debug then
3595 hr_utility.set_location(l_proc, 40);
3596 end if;
3597 --
3598 end;
3599 end if;
3600
3601 -- if the new_mgr_per_in_ler_id was not manager earlier then,
3602 -- add the summary row for the person.
3603
3604 insert into ben_cwb_summary
3605 (summary_id
3606 ,group_per_in_ler_id
3607 ,group_pl_id
3608 ,group_oipl_id
3609 ,person_id
3610 ,lf_evt_ocrd_dt)
3611 select ben_cwb_summary_s.nextval
3612 ,grp.group_per_in_ler_id
3613 ,grp.group_pl_id
3614 ,grp.group_oipl_id
3615 ,pil.person_id
3616 ,pil.lf_evt_ocrd_dt
3617 from ben_cwb_person_groups grp
3618 ,ben_per_in_ler pil
3619 where grp.group_per_in_ler_id = p_new_mgr_per_in_ler_id
3620 and grp.group_per_in_ler_id = pil.per_in_ler_id
3621 and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
3622 and not exists(select null
3623 from ben_cwb_summary
3624 where group_per_in_ler_id = p_new_mgr_per_in_ler_id);
3625
3626 if l_is_mgr = 'Y' then
3627 -- call reassign_mgr
3628 reassign_mgr(p_old_mgr_per_in_ler_id => p_old_mgr_per_in_ler_id
3629 ,p_new_mgr_per_in_ler_id => p_new_mgr_per_in_ler_id
3630 ,p_emp_per_in_ler_id => p_emp_per_in_ler_id);
3631 else
3632 -- call reassign_emp
3633 reassign_emp(p_old_mgr_per_in_ler_id => p_old_mgr_per_in_ler_id
3634 ,p_new_mgr_per_in_ler_id => p_new_mgr_per_in_ler_id
3635 ,p_emp_per_in_ler_id => p_emp_per_in_ler_id);
3636 end if;
3637 --
3638 save_pl_sql_tab;
3639 --
3640 -- before leaving, delete the row from group_hrchy, if it is
3641 -- inserted in this procedure
3642 if l_insert_old_mgr then
3643 --
3644 if g_debug then
3645 hr_utility.set_location(l_proc, 50);
3646 end if;
3647 --
3648 delete from ben_cwb_group_hrchy
3649 where mgr_per_in_ler_id = p_old_mgr_per_in_ler_id
3650 and lvl_num = 0;
3651 end if;
3652
3653 if g_debug then
3654 hr_utility.set_location(' Leaving:'|| l_proc, 99);
3655 end if;
3656 --
3657 end;
3658 --
3659 -- --------------------------------------------------------------------------
3660 -- |----------------------< delete_summary_group_pl >------------------------|
3661 -- --------------------------------------------------------------------------
3662 procedure delete_summary_group_pl(p_group_pl_id number
3663 ,p_lf_evt_ocrd_dt date)
3664 is
3665 --
3666 l_proc varchar2(72) := g_package||'delete_summary_group_pl';
3667 --
3668 begin
3669 --
3670 if g_debug then
3671 hr_utility.set_location('Entering:'|| l_proc, 10);
3672 end if;
3673 --
3674 delete from ben_cwb_summary
3675 where group_pl_id = p_group_pl_id
3676 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
3677 --
3678 if g_debug then
3679 hr_utility.set_location(l_proc, 170);
3680 end if;
3681 --
3682 end;
3683 --
3684 -- --------------------------------------------------------------------------
3685 -- |----------------< upd_summary_on_elig_sal_change >--------------------|
3686 -- --------------------------------------------------------------------------
3687 procedure upd_summary_on_elig_sal_change(p_group_per_in_ler_id in number
3688 ,p_elig_sal_change in number) is
3689 -- get the managers' ids and change in Ws Bdgts
3690 cursor csr_ws_bdgts(p_group_per_in_ler_id number
3691 ,p_sal number) is
3692 select grp.group_pl_id
3693 ,grp.group_oipl_id
3697 ,ben_cwb_person_groups grp
3694 ,grp.ws_bdgt_val * p_sal / 100 ws_bdgt_val
3695 ,grp.ws_bdgt_iss_val * p_sal / 100 ws_bdgt_iss_val
3696 from ben_cwb_group_hrchy hrchy
3698 where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
3699 and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
3700 and hrchy.lvl_num = 1
3701 order by grp.group_pl_id, grp.group_oipl_id;
3702
3703 -- get the mgr ids of the person.
3704 cursor mgr_ids(p_group_per_in_ler_id number) is
3705 select hrchy.mgr_per_in_ler_id per_in_ler_id
3706 ,mgr.mgr_per_in_ler_id next_mgr_id
3707 from ben_cwb_group_hrchy hrchy
3708 ,ben_cwb_group_hrchy mgr
3709 where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
3710 and hrchy.lvl_num > 0
3711 and mgr.emp_per_in_ler_id (+) = hrchy.mgr_per_in_ler_id
3712 and mgr.lvl_num (+) = 1
3713 order by hrchy.lvl_num;
3714
3715
3716 -- Type delcarations for pl/sql tables
3717 type group_per_in_ler_id_type is table of
3718 ben_cwb_person_groups.group_per_in_ler_id%type;
3719 type group_pl_id_type is table of
3720 ben_cwb_person_groups.group_pl_id%type;
3721 type group_oipl_id_type is table of
3722 ben_cwb_person_groups.group_oipl_id%type;
3723 type ws_bdgt_val_type is table of
3724 ben_cwb_person_groups.ws_bdgt_val%type;
3725 type ws_bdgt_iss_val_type is table of
3726 ben_cwb_person_groups.ws_bdgt_iss_val%type;
3727
3728 -- declare pl/sql tables
3729 l_group_pl_id_tab group_pl_id_type;
3730 l_group_oipl_id_tab group_oipl_id_type;
3731 l_ws_bdgt_val_tab ws_bdgt_val_type;
3732 l_ws_bdgt_iss_val_tab ws_bdgt_iss_val_type;
3733 --
3734 l_immd_mgr number;
3735 --
3736 l_bdgt_val number;
3737 l_bdgt_iss_val number;
3738 l_prsrv_bdgt_cd varchar2(1);
3739 l_uses_bdgt_flag varchar2(1);
3740 --
3741 l_proc varchar2(72) := g_package||'upd_summary_on_elig_sal_change';
3742 --
3743 begin
3744 --
3745 if g_debug then
3746 hr_utility.set_location('Entering:'|| l_proc, 10);
3747 end if;
3748 --
3749 select pl.prsrv_bdgt_cd
3750 ,pl.uses_bdgt_flag
3751 into l_prsrv_bdgt_cd
3752 ,l_uses_bdgt_flag
3753 from ben_cwb_pl_dsgn pl
3754 ,ben_cwb_person_groups grp
3755 where grp.group_per_in_ler_id = p_group_per_in_ler_id
3756 and grp.group_oipl_id = -1
3757 and pl.pl_id = grp.group_pl_id
3758 and pl.oipl_id = grp.group_oipl_id
3759 and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
3760
3761 if l_prsrv_bdgt_cd = 'A' or l_uses_bdgt_flag = 'N'
3762 or p_elig_sal_change = 0 then
3763 --
3764 if g_debug then
3765 hr_utility.set_location(' Leaving:'|| l_proc, 19);
3766 end if;
3767 -- no changes are required
3768 return;
3769 end if;
3770 --
3771 open csr_ws_bdgts(p_group_per_in_ler_id, p_elig_sal_change);
3772 fetch csr_ws_bdgts bulk collect into l_group_pl_id_tab
3773 ,l_group_oipl_id_tab
3774 ,l_ws_bdgt_val_tab
3775 ,l_ws_bdgt_iss_val_tab;
3776 close csr_ws_bdgts;
3777
3778 --
3779 if g_debug then
3780 hr_utility.set_location(l_proc, 20);
3781 end if;
3782 --
3783 --
3784 l_immd_mgr := 1;
3785 --
3786 for mgr in mgr_ids(p_group_per_in_ler_id)
3787 loop
3788 --
3789 if g_debug then
3790 hr_utility.set_location(l_proc, 30);
3791 end if;
3792 --
3793 for i in l_group_pl_id_tab.first..l_group_pl_id_tab.last
3794 loop
3795 --
3796 if g_debug then
3797 hr_utility.set_location(l_proc, 40);
3798 end if;
3799
3800 -- for Dist Bdgts, get the current mgr bdgt
3801 select decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
3802 grp.dist_bdgt_val) * p_elig_sal_change /100
3803 ,decode(nvl(grp.dist_bdgt_iss_val,0),0,grp.ws_bdgt_iss_val,
3804 grp.dist_bdgt_iss_val) * p_elig_sal_change /100
3805 into l_bdgt_val
3806 ,l_bdgt_iss_val
3807 from ben_cwb_person_groups grp
3808 where grp.group_per_in_ler_id = mgr.per_in_ler_id
3809 and grp.group_pl_id = l_group_pl_id_tab(i)
3810 and grp.group_oipl_id = l_group_oipl_id_tab(i);
3811 --
3812 if g_debug then
3813 hr_utility.set_location(l_proc, 50);
3814 end if;
3815
3816 -- this is the change in bdgts of current mgr. so his/her immd
3817 -- mgr summary will need to be updated.
3818 if mgr.next_mgr_id is not null then
3819 --
3820 if g_debug then
3821 hr_utility.set_location(l_proc, 60);
3822 end if;
3823 --
3824 update_or_insert_pl_sql_tab
3825 (p_group_per_in_ler_id => mgr.next_mgr_id
3826 ,p_group_pl_id => l_group_pl_id_tab(i)
3827 ,p_group_oipl_id => l_group_oipl_id_tab(i)
3828 ,p_ws_bdgt_val_direct => l_ws_bdgt_val_tab(i) * l_immd_mgr
3829 ,p_ws_bdgt_val_all => l_ws_bdgt_val_tab(i)
3830 ,p_ws_bdgt_iss_val_direct => l_ws_bdgt_iss_val_tab(i) * l_immd_mgr
3831 ,p_ws_bdgt_iss_val_all => l_ws_bdgt_iss_val_tab(i)
3832 ,p_bdgt_val_direct => l_bdgt_val
3833 ,p_bdgt_iss_val_direct => l_bdgt_iss_val
3834 );
3835 end if; -- if mgr.next_mgr_id
3836 end loop;
3837 --
3838 l_immd_mgr := 0;
3839 --
3840 if g_debug then
3841 hr_utility.set_location(l_proc, 70);
3842 end if;
3843 --
3844 end loop; -- of mgr_ids cursor
3845 --
3846 if g_debug then
3847 hr_utility.set_location(' Leaving:'|| l_proc, 99);
3848 end if;
3849 --
3850 end; -- end of update_summary_on_elig_sal_change
3851 --
3852 -- --------------------------------------------------------------------------
3853 -- |-------------------< refresh_summary_all_plans >-----------------------|
3854 -- --------------------------------------------------------------------------
3855 procedure refresh_summary_all_plans
3856 is
3857 --
3858 --
3859 cursor csr_plans_to_refresh
3860 is
3861 select distinct group_pl_id
3862 ,lf_evt_ocrd_dt
3863 from ben_cwb_person_info cpi
3864 where cpi.person_id = -1;
3865 --
3866 --
3867 begin
3868 --
3869 --
3870 for l_rec in csr_plans_to_refresh loop
3871 refresh_summary_persons(l_rec.group_pl_id, l_rec.lf_evt_ocrd_dt);
3872 commit;
3873 end loop;
3874 --
3875 --
3876 end;
3877 --
3878 end ben_cwb_summary_pkg; -- end of package
3879