[Home] [Help]
PACKAGE BODY: APPS.BEN_MANAGE_CWB_LIFE_EVENTS
Source
1 package body ben_manage_cwb_life_events as
2 /* $Header: bencwbcm.pkb 120.31.12010000.3 2008/09/24 09:18:31 sgnanama ship $ */
3 --
4 /*
5 +========================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +========================================================================+
10 --
11 Name
12 Manage Life Events
13 Purpose
14 This package is used to handle cwb pre-benmngle process and post
15 benmngle cwb relevent actions like populating the cross business group
16 and other hierarchy data.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 19 Dec 03 pbodla/
21 Indrasen 115.0 Created.
22 22 Dec 03 Indrasen 115.1 removed 1 from package name
23 29 Dec 03 Indrasen 115.3 Added more procedures
24 05 Jan 03 Indrasen 115.4 Adding rebuild Hierarchy
25 06 Jan 03 pbodla 115.4 Added code to populate cwb_tasks
26 and populate_cwb_rates
27 07 Jan 03 pbodla 115.5 Added code to populate cwb_rates
28 08 Jan 03 pbodla/ 115.6 Added code to populate cwb_rates
29 ikasire as suggested by cwb team.
30 08 Jan 03 pbodla 115.7 Added code to for auto budget issue
31 17 Jan 03 pbodla/ 115.8 Added code to populate missing pils
32 ikasire Added code for global logging.
33 Added calls for missing group pils
34 and biuild Hierarchy
35 21 Jan 03 pbodla 115.8 Integrated with CWB procedures.
36 21 Jan 03 pbodla 115.9 Fixed popu_missing_person_pil to
37 populate data properly.
38 28 Jan 03 pbodla 115.12 Added code populate group pil id on
39 ben_cwb_person_rates if group pil
40 is null
41 02 Feb 04 pbodla 115.13 Added c_benfts_grp cursor to fetch
42 the benefit group based on name.
43 Plans business group id have to
44 be passed when cwb_process is
45 called.
46 04 Feb 04 pbodla/ 115.14 Modified get_gpil_id cursor.
47 ikasire
48
49 04 Feb 04 pbodla 115.15 Added code to get the group oipl id
50 05 Feb 04 ikasire 115.14/15 fixed slaves issue
51 05 Feb 04 ikasire 115.16 Added new procedure and changes for the
52 online call
53 06 Feb 04 pbodla 115.17 Added code to populate WS_RT_START_DAT
54 25 Feb 04 pbodla 115.18 Added params p_no_person_rates,
55 p_no_person_groups for the
56 populate_cwb_rates procedure.
57 These parameters will be used later
58 for reducing the data inserts.
59 27 Feb 04 ikasire 115.19 Added cursor to generate and pass
60 person selection rule in different
61 business groups
62 03 Mar 04 ikasire 115.20 Bug 3482033 fixes
63 22 Mar 04 pbodla 115.21 Bug 3517726 : Do not consider any
64 data which is not deleted by backout.
65 06 Apr 04 pbodla 115.22 Added the code to submit the reports
66 28 Apr 04 pbodla 115.23 Added the code to copy budget
67 columns from cwb_person_rates to
68 cwb_person_groups.
69 06 May 04 pbodla 115.24 Added procedure del_all_cwb_pils
70 to delete all data if person is
71 ineligible and track ineligible
72 flag is N.
73 26 May 04 pbodla 115.25 Added cursor get_per_info
74 to be used for online calls.
75 10 Jun 04 maagrawa 115.26 Pass null as effective_date to
76 ben_cwb_pl_dsgn_pkg and
77 ben_cwb_person_info_pkg
78 12 Jul 04 pbodla 115.27 Bug 3748539: This situation indicates
79 it is a recursive supervisory heirarchy.
80 13 Jul 04 pbodla 115.28 Bug 3748539: Added MGRPERSONID token.
81 20 Jul 04 pbodla 115.29 Added logic for single person run.
82 p_single_per_clone_all_data
83 21 Jul 04 pbodla 115.30 Added p_use_eff_dt_flag to
84 global_online_process_w and other
85 procedures, if front end
86 takes the decision to take full control
87 to clone data then this flag is passed
88 as Y.
89 08 oct 04 pbodla 115.31 Added extra parameters to
90 global_online_process_w, to handle
91 backout.
92 11 oct 04 pbodla 115.32 passed correct date parameters to
93 global_online_process_w
94 01 Nov 04 pbodla 115.33 Added procedure
95 sum_oipl_rates_and_upd_pl_rate
96 Bug : 3968065
97 03 Nov 04 pbodla 115.34 modified procedure to handle nulls
98 sum_oipl_rates_and_upd_pl_rate
99 17 Nov 04 pbodla 115.35 Bug 3510081 : data model changes
100 and code changes for auto allocation
101 of budgets.
102 01 Dec 04 pbodla 115.36 ACCESS_CD in ben_cwb_person_tasks
103 is populated with UP
104 06-Dec-04 bmanyam 115.37 Bug: 3979082. Use AME Hierarchy to
105 fetch manager hierarchy.
106 06-Dec-04 bmanyam 115.38 Bug: 3979082. Use RULE to
107 fetch manager hierarchy.
108 10-Dec-04 pbodla 115.39 Modified p_single_per_clone_all_data
109 commented the code which nullifies l_ws_mgr_id
110 if mgr per_in_ler not found.
111 14-Dec-04 pbodla 115.40 bug 4040013 - Modified cursor c_oipl_exists
112 to check ws rate at plan level also.
113 23-Dec-04 pbodla 115.41 bug 4052530 - initialisation of
114 g_group_per_in_ler is moved
115 within p_single_per_clone_all proc.
116 27-Dec-04 pbodla 115.42 Added initialization of globals to
117 global_online_process_w procedure
118 06-jan-05 pbodla 115.43 Pop cd is populated only for
119 auto allocation of budgets and at
120 plan level only.
121 24-jan-05 nhunur 115.44 4128034: Pass correct assignment id to the RL
122 21-Feb-05 pbodla 115.45 bug 4198404 - Added record structure
123 g_error_log_rec_type to log proper
124 error messages.
125
126 Commit data after refresh_pl_dsgn
127 only if the call is from conc manager
128
129 17-May-05 pbodla 115.46 Added performance hint for cursor
130 c_person_rates
131
132 27-May-05 pbodla 115.47 Bug 4399281 : Assume recursive heirarchy
133 : A reports to B, B reports to
134 C, C Reports to B. This scenario is not caught by error
135 BEN_94020_RECURSIVE_EMP_HEIRAR if person A is picked up
136 in heirarchy build first.
137 By adding check l_level > 75 infinite loop is broken and
138 when person B is picked up to build heirarchy above error
139 is raised.
140 14-Jun-05 kmahendr 115.48 Bug#4258200 - l_copy_person_bdgt_count initialised
141 to 0
142 20-Jun-05 pbodla 115.49 Bug#4258200 - Data from copy_ attributes are
143 not copied over for cross business group data.
144 17-aug-05 pbodla 115.50 Bug#4547916 - Even if manager is processed still
145 do not re create the group pil data.
146 Also allow creating heirarchy data based on processed pils.
147 28-Sep-05 tjesumic 115.51 audit_log_flag support 4 values
148 Y Log Yes Report YES
149 N Log NO Report YES
150 NN Log NO Report NO
151 YN Log Yes Report No
152 17-aug-05 pbodla 115.52 Bug#4720746 - typing error corrected.
153 28-Nov-05 maagrawa 115.53 4766589. Default person task's access
154 to task definition's hidden_cd.
155 29-dec-05 pbodla 115.54 PERF 4587770 : Added performance related
156 changes. Some of the potential code chages are
157 in comments as, we are waiting for GSI to
158 validate the fixes. Once GSI gets back
159 with results, this code can be merged.
160 03-Jan-06 nhunur 115.55 cwb - changes for person type param.
161 29-dec-05 pbodla 115.56 Enhancement to handle the recursive
162 heirarchy properly, so that string containing
163 personid's will be printed.
164 Process will not error out, it will
165 continue building the heirarchy.
166 Users have to use admin page and reassign
167 one employee who is in recursive relationship.
168 23-jan-06 pbodla 115.57 Enhancement to handle the recursive
169 heirarchy in online run. Added error
170 message - BEN_94537_REC_REPORTING
171 08-Feb-06 abparekh 115.58 Bug 4875181 - Added p_run_rollup_only to process
172 115.59 only Rollup Processes
173 07-Feb-06 mmudigon 115.59 CWB Multi currency support. Added
174 procs() exec_element_det_rl and
175 get_abr_ele_inp
176 determine_curr_code
177 17-Feb-06 pbodla/stee115.59 CWB Multi currency support. Added
178 determine_curr_code
179 21-Feb-06 pbodla 115.60 currency col populated in ben_cwb_person_rates.
180 28-Feb-06 pbodla 115.62 Fix currency_det_cd for salary basis and standard
181 rates.
182 04-Mar-06 maagrawa 115.63 Include call for exchange rate creation.
183 24-Mar-06 maagrawa 115.64 GSCC nocopy error.
184 27-Mar-06 stee 115.65 Populate currency when cloning
185 person data - Bug 5104388.
186 07-Apr-06 swjain 115.67 Bug 5141153: Updated procedure exec_element_det_rl
187 21-Apr-06 ikasired 115.68 5148387 handling for benefit assignment
188 26-Apr-06 maagrawa 115.70 4636102:Error getting killed in
189 online mode also.
190 12-May-06 bmanyam 115.71 Text for BEN_94537_REC_REPORTING changed by anadi.
191 So, the corresponding log-file buff is also
192 changed.
193 12-May-06 bmanyam 115.72 -- do --
194 22-May-06 pbodla 115.73 Bug 5232223 - Added code to handle the trk inelig flag
195 If trk inelig flag is set to N at group plan level
196 then do not create cwb per in ler and all associated data.
197
198 22-Jun-06 rbingi 115.74 Bug 5232223 - Calling del_all_cwb_pils when elpros attacthed to
199 local plan.
200 26-Jun-06 rbingi 115.75 Contd.5232223.
201 18-Oct-06 maagrawa 115.76 4587770.Tuned c_no_0_hrchy
202 01-dec-06 ssarkar 115.77 5124534 : modified popu_missing_person_pil
203 13-dec-06 ssarkar 115.78 5124534/5702794 : populate cwb_group_persons with pl_id/oipl_id
204 20-Feb-07 maagrawa 115.79 Further tuned c_no_0_hrchy. Use
205 ben_cwb_person_info instead of
206 ben_per_in_ler.
207 04-Jun-07 maagrawa 115.80 Further tuned c_no_0_hrchy. Check only
208 level 1 hierarchy.
209 24-Sep-08 sgnanama 115.81 7393142: process for terminated employee
210 */
211 --------------------------------------------------------------------------------
212 --
213 g_package varchar2(80) := 'ben_manage_cwb_life_events';
214 --
215 g_debug boolean := hr_utility.debug_enabled;
216 g_rebuild_pl_id number := null;
217 g_rebuild_lf_evt_ocrd_dt date := null;
218 g_rebuild_business_group_id number := null;
219 g_opt_exists boolean;
220 -- RECUR
221 TYPE g_hrchy_rec_type Is RECORD(
222 hrchy_cat_string varchar2(80)
223 );
224 --
225 g_hrchy_rec g_hrchy_rec_type ;
226 TYPE hrchy_table is table of g_hrchy_rec_type index by binary_integer ;
227 g_hrchy_tbl hrchy_table ;
228 -- END RECUR
229 --
230 -- Globals needed to copy copy_<budget> columns from rates table to group
231 -- table.
232 --
233 type g_cache_copy_person_bdgt_rt is record
234 (person_id ben_cwb_person_rates.person_id%type,
235 group_pl_id ben_pl_f.pl_id%type,
236 group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237 group_lf_evt_ocrd_dt date,
238 copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239 copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240 copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241 copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242 copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243 copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244 copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245 copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246 copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247 copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248 copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249 copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250 copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251 copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252 copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253 copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
254 );
255 type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
256 by binary_integer;
257 g_cache_copy_person_bdgt_tbl g_cache_copy_person_bdgt_typ;
258 g_cache_copy_person_bdgt_tbl1 g_cache_copy_person_bdgt_typ;
259
260 --
261 procedure check_slaves_status
262 (p_num_cwb_processes in number
263 ,p_cwb_processes_rec in ben_manage_cwb_life_events.g_cwb_processes_table
264 -- ,p_master in varchar2
265 ,p_slave_errored out nocopy boolean
266 )
267 is
268 --
269 l_package varchar2(80) := g_package||'.check_slaves_status';
270 --
271 l_no_slaves boolean := true;
272 l_poll_loops pls_integer;
273 l_slave_errored boolean;
274 --
275 cursor c_slaves
276 (c_request_id number
277 )
278 is
279 select phase_code,
280 status_code
281 from fnd_concurrent_requests fnd
282 where fnd.request_id = c_request_id;
283 --
284 l_slaves c_slaves%rowtype;
285 --
286 begin
287 --
288 if g_debug then
289 hr_utility.set_location ('Entering '||l_package,10);
290 end if;
291 --
292 if p_num_cwb_processes <> 0 -- and p_master = 'Y'
293 then
294 --
295 while l_no_slaves loop
296 --
297 l_no_slaves := false;
298 --
299 for elenum in 1..p_num_cwb_processes
300 loop
301 --
302 open c_slaves
303 (p_cwb_processes_rec(elenum)
304 );
305 fetch c_slaves into l_slaves;
306 if l_slaves.phase_code <> 'C'
307 then
308 --
309 l_no_slaves := true;
310 --
311 end if;
312 --
313 if l_slaves.status_code = 'E' then
314 --
315 l_slave_errored := true;
316 --
317 end if;
318 --
319 close c_slaves;
320 --
321 -- Loop to avoid over polling of fnd_concurrent_requests
322 --
323 l_poll_loops := 100000;
324 --
325 for i in 1..l_poll_loops
326 loop
327 --
328 null;
329 --
330 end loop;
331 --
332 end loop;
333 --
334 end loop;
335 --
336 end if;
337 --
338 if g_debug then
339 hr_utility.set_location ('Leaving '||l_package,10);
340 end if;
341 --
342 commit;
343 --
344 end check_slaves_status;
345 --
346 procedure check_all_slaves_finished
347 (p_benefit_action_id in number
348 ,p_business_group_id in number
349 ,p_slave_errored out nocopy boolean
350 )
351 is
352 --
353 l_package varchar2(80) := g_package||'.check_all_slaves_finished';
354 l_no_slaves boolean := true;
355 l_dummy varchar2(1);
356 l_master varchar2(1) := 'N';
357 l_param_rec benutils.g_batch_param_rec;
358 l_slave_errored boolean := false;
359 --
360 /*
361 cursor c_master is
362 select 'Y'
363 from ben_benefit_actions bft
364 where bft.benefit_action_id = p_benefit_action_id
365 and bft.request_id = fnd_global.conc_request_id;
366 */
367 --
368 begin
369 --
370 if g_debug then
371 hr_utility.set_location ('Entering '||l_package,10);
372 end if;
373 --
374 /*
375 -- Work out if process is master
376 --
377 open c_master;
378 --
379 fetch c_master into l_master;
380 --
381 close c_master;
382 --
383 */
384 -- Check slave status
385 --
386 check_slaves_status
387 (p_num_cwb_processes => ben_manage_cwb_life_events.g_num_cwb_processes
388 ,p_cwb_processes_rec => ben_manage_cwb_life_events.g_cwb_processes_rec
389 -- ,p_master => l_master
390 --
391 ,p_slave_errored => l_slave_errored
392 );
393 --
394 if g_debug then
395 hr_utility.set_location (l_package||' OUT NOCOPY slave loop ',20);
396 end if;
397 --
398 /*
399 -- Log process information
400 -- This is master specific only
401 --
402 if l_master = 'Y' then
403 --
404 ben_manage_life_events.write_bft_statistics
405 (p_business_group_id => p_business_group_id
406 ,p_benefit_action_id => p_benefit_action_id
407 );
408 --
409 end if;
410 hr_utility.set_location (l_package||' Write to file ',35);
411 --
412 benutils.write_table_and_file(p_table => true,
413 p_file => false);
414 */
415 --
416 commit;
417 --
418 p_slave_errored := l_slave_errored;
419 --
420 hr_utility.set_location ('Leaving '||l_package,50);
421 --
422 end check_all_slaves_finished;
423 --
424 -- Evaluates element determination rule
425 --
426 procedure exec_element_det_rl
427 (p_element_det_rl number default null,
428 p_acty_base_rt_id number default null,
429 p_effective_date date,
430 p_assignment_id number default null,
431 p_organization_id number default null,
432 p_business_group_id number default null,
433 p_pl_id number default null,
434 p_ler_id number default null,
435 p_element_type_id out nocopy number,
436 p_input_value_id out nocopy number,
437 p_currency_cd out nocopy varchar2) is
438
439 l_proc varchar2(80) := g_package||'.exec_element_det_rl' ;
440 l_element_det_rl number;
441 l_outputs ff_exec.outputs_t;
442
443 cursor c_abr is
444 select element_det_rl
445 from ben_acty_base_rt_f
446 where acty_base_rt_id = p_acty_base_rt_id
447 and p_effective_date between effective_start_date
448 and effective_end_date;
449
450 begin
451 --
452 if g_debug then
453 hr_utility.set_location('Entering: '||l_proc,10);
454 end if;
455 --
456 if p_element_det_rl is null then
457 --
458 if p_acty_base_rt_id is null then
459 --
460 hr_utility.set_location('Incorrect args '||l_proc,15);
461 --
462 hr_api.mandatory_arg_error
463 (p_api_name => l_proc,
464 p_argument => 'p_element_det_rl,p_acty_base_rt_id',
465 p_argument_value => p_element_det_rl);
466 --
467 else
468 --
469 open c_abr;
470 fetch c_abr into l_element_det_rl;
471 if c_abr%NotFound then /* Bug 5141153 : Added if condition */
472 close c_abr;
473 --
474 if g_debug then
475 hr_utility.set_location('No RL found '||l_proc,15);
476 hr_utility.set_location('Leaving: '||l_proc,15);
477 end if;
478 --
479 return;
480 --
481 end if; /* End Bug 5141153 */
482 close c_abr;
483 --
484 end if;
485 else
486 l_element_det_rl := p_element_det_rl;
487 end if;
488
489 if g_debug then
490 hr_utility.set_location('element_det_rl: '||l_element_det_rl,25);
491 end if;
492
493 l_outputs := benutils.formula
494 (p_formula_id => l_element_det_rl,
495 p_effective_date => p_effective_date,
496 p_assignment_id => p_assignment_id,
497 p_acty_base_rt_id => p_acty_base_rt_id,
498 p_organization_id => p_organization_id,
499 p_business_group_id => p_business_group_id,
500 p_pl_id => p_pl_id,
501 p_ler_id => p_ler_id);
502
503 for l_count in l_outputs.first..l_outputs.last
504 loop
505 --
506 if l_outputs(l_count).name = 'ELEMENT_TYPE_ID' then
507 p_element_type_id := to_number(l_outputs(l_count).value);
508 elsif l_outputs(l_count).name = 'INPUT_VALUE_ID' then
509 p_input_value_id := to_number(l_outputs(l_count).value);
510 elsif l_outputs(l_count).name = 'CURRENCY_CODE' then
511 p_currency_cd := l_outputs(l_count).value;
512 else
513 -- error
514 null;
515 end if;
516 --
517 end loop;
518 --
519 if g_debug then
520 hr_utility.set_location('Leaving: '||l_proc,10);
521 end if;
522 --
523 exception
524 when others then
525 if g_debug then
526 hr_utility.set_location('In exception block '||l_proc,10);
527 end if;
528 --
529 p_element_type_id := null;
530 p_input_value_id := null;
531 p_currency_cd := null;
532 raise;
533
534 end exec_element_det_rl;
535 --
536 -- call this proc to determine which ele and inp value to send to benelmen
537 --
538 procedure get_abr_ele_inp
539 (p_person_id number,
540 p_acty_base_rt_id number,
541 p_effective_date date,
542 p_element_type_id_in number default null,
543 p_input_value_id_in number default null,
544 p_pl_id number default null,
545 p_element_type_id_out out nocopy number,
546 p_input_value_id_out out nocopy number ) is
547
548 l_proc varchar2(80) := g_package||'.get_abr_ele_inp' ;
549 l_payroll_id number;
550 l_organization_id number;
551 l_assignment_id number;
552 l_element_type_id number;
553 l_input_value_id number;
554 l_dummy_varchar2 varchar2(255);
555
556 begin
557 --
558 if g_debug then
559 hr_utility.set_location('Entering: '||l_proc,10);
560 end if;
561 --
562 ben_element_entry.get_abr_assignment
563 (p_person_id => p_person_id
564 ,p_effective_date => p_effective_date
565 ,p_acty_base_rt_id => p_acty_base_rt_id
566 ,p_organization_id => l_organization_id
567 ,p_payroll_id => l_dummy_varchar2
568 ,p_assignment_id => l_assignment_id);
569 --
570 exec_element_det_rl
571 (p_element_det_rl => null,
572 p_acty_base_rt_id => p_acty_base_rt_id,
573 p_effective_date => p_effective_date,
574 p_assignment_id => l_assignment_id,
575 p_organization_id => l_organization_id,
576 p_pl_id => p_pl_id,
577 p_element_type_id => l_element_type_id,
578 p_input_value_id => l_input_value_id,
579 p_currency_cd => l_dummy_varchar2);
580 --
581 if l_element_type_id is not null and
582 l_input_value_id is not null then
583 --
584 p_element_type_id_out := l_element_type_id;
585 p_input_value_id_out := l_input_value_id;
586 --
587 else
588 --
589 p_element_type_id_out := p_element_type_id_in;
590 p_input_value_id_out := p_input_value_id_in;
591 --
592 end if;
593 --
594 if g_debug then
595 hr_utility.set_location('elt: '||p_element_type_id_out,10);
596 hr_utility.set_location('inp: '||p_input_value_id_out,10);
597 hr_utility.set_location('Leaving: '||l_proc,10);
598 end if;
599 --
600 exception
601 when others then
602 if g_debug then
603 hr_utility.set_location('In exception block '||l_proc,10);
604 end if;
605 --
606 p_element_type_id_out := null;
607 p_input_value_id_out := null;
608 raise;
609
610 end get_abr_ele_inp;
611 --
612 -- Determine curency code
613 --
614 procedure determine_curr_code
615 (p_element_det_rl number default null,
616 p_acty_base_rt_id number default null,
617 p_currency_det_cd varchar2 default null,
618 p_base_element_type_id number default null,
619 p_effective_date date,
620 p_assignment_id number default null,
621 p_organization_id number default null,
622 p_business_group_id number default null,
623 p_pl_id number default null,
624 p_opt_id number default null,
625 p_ler_id number default null,
626 p_element_type_id out nocopy number,
627 p_input_value_id out nocopy number,
628 p_currency_cd out nocopy varchar2) is
629
630 l_proc varchar2(80) := g_package||'.determine_curr_code' ;
631
632 l_element_type_id number;
633 l_input_value_id number;
634 l_currency_cd varchar2(200);
635 --
636 cursor c_get_et_currency_cd (l_element_type_id in number)
637 is
638 select input_currency_code
639 from pay_element_types_f
640 where element_type_id = l_element_type_id
641 and p_effective_date between effective_start_date
642 and effective_end_date;
643 --
644 cursor c_get_sb_currency_cd
645 is
646 select et.input_currency_code
647 from pay_element_types_f et
648 ,per_pay_bases pb
649 ,pay_input_values_f iv
650 ,per_all_assignments_f asg
651 where asg.pay_basis_id = pb.pay_basis_id
652 and pb.input_value_id = iv.input_value_id
653 and iv.element_type_id = et.element_type_id
654 and asg.assignment_id = p_assignment_id
655 and p_effective_date between
656 asg.effective_start_date and asg.effective_end_date
657 and p_effective_date between
658 iv.effective_start_date and iv.effective_end_date
659 and p_effective_date between
660 et.effective_start_date and et.effective_end_date;
661 --
662 cursor c_get_pl_currency_cd
663 is
664 select pln.nip_pl_uom
665 from ben_pl_f pln
666 where pln.pl_id = p_pl_id
667 and p_effective_date between
668 pln.effective_start_date and pln.effective_end_date;
669 begin
670 --
671 if g_debug then
672 hr_utility.set_location('Entering: '||l_proc,10);
673 hr_utility.set_location('p_currency_det_cd: '||p_currency_det_cd,10);
674 end if;
675 --
676 if nvl(p_currency_det_cd, 'AUTO') in ('STDRTEL', 'AUTO') then
677 --
678 if p_element_det_rl is not null then
679 --
680 exec_element_det_rl
681 (p_element_det_rl => p_element_det_rl,
682 p_acty_base_rt_id => p_acty_base_rt_id,
683 p_effective_date => p_effective_date,
684 p_assignment_id => p_assignment_id,
685 p_organization_id => p_organization_id,
686 p_business_group_id => p_business_group_id,
687 p_pl_id => p_pl_id,
688 -- p_opt_id => p_opt_id,
689 p_ler_id => p_ler_id,
690 p_element_type_id => l_element_type_id,
691 p_input_value_id => l_input_value_id,
692 p_currency_cd => l_currency_cd
693 );
694 --
695 end if;
696 --
697 if l_currency_cd is null and l_element_type_id is not null then
698 --
699 -- get it from the l_element_type_id
700 --
701 open c_get_et_currency_cd(l_element_type_id);
702 fetch c_get_et_currency_cd into l_currency_cd;
703 close c_get_et_currency_cd;
704 --
705 end if;
706 --
707 if l_currency_cd is null and p_base_element_type_id is not null then
708 --
709 -- get it from the p_base_element_type_id
710 --
711 open c_get_et_currency_cd(p_base_element_type_id);
712 fetch c_get_et_currency_cd into l_currency_cd;
713 close c_get_et_currency_cd;
714 --
715 end if;
716 --
717 if l_currency_cd is null and nvl(p_currency_det_cd, 'AUTO') = 'AUTO' then
718 --
719 -- get it from salary basis element.
720 --
721 open c_get_sb_currency_cd;
722 fetch c_get_sb_currency_cd into l_currency_cd;
723 close c_get_sb_currency_cd;
724 --
725 end if;
726 --
727 end if;
728 --
729 if l_currency_cd is null and p_currency_det_cd = 'SALBEL' then
730 --
731 -- Get it from salary basis element.
732 --
733 open c_get_sb_currency_cd;
734 fetch c_get_sb_currency_cd into l_currency_cd;
735 close c_get_sb_currency_cd;
736 --
737 end if;
738 --
739 -- Either currency_cd is PLAN or can't be determined earlier.
740 --
741 if l_currency_cd is null then
742 --
743 -- Get it from plan level.
744 --
745 open c_get_pl_currency_cd;
746 fetch c_get_pl_currency_cd into l_currency_cd;
747 close c_get_pl_currency_cd;
748 --
749 end if;
750 --
751 p_currency_cd := l_currency_cd;
752 p_input_value_id := l_input_value_id;
753 p_element_type_id := l_element_type_id;
754 --
755 if g_debug then
756 hr_utility.set_location('Leaving: '||l_proc,10);
757 end if;
758 --
759 end determine_curr_code;
760
761 --
762 -- RECUR
763 --
764 procedure p_add_to_recur_hrchy(p_hrchy_string varchar2, p_hrchy_search varchar2) is
765 l_proc varchar2(80) ;
766 l_found boolean := false;
767 l_counter number;
768 begin
769 --
770 if g_debug then
771 l_proc := g_package|| '.p_add_to_recur_hrchy';
772 hr_utility.set_location('Entering: '||l_proc,10);
773 end if;
774 --
775 l_counter := nvl(g_hrchy_tbl.LAST, 0);
776 if l_counter > 0 and p_hrchy_search is not null then
777 --
778 for i in 1..l_counter loop
779 if instr(g_hrchy_tbl(i).hrchy_cat_string, p_hrchy_search) > 0
780 then
781 l_found := true;
782 exit;
783 end if;
784 end loop;
785 --
786 end if;
787 --
788 if not l_found then
789 --
790 g_hrchy_tbl(l_counter+1).hrchy_cat_string := p_hrchy_string;
791 --
792 end if;
793 --
794 if g_debug then
795 hr_utility.set_location('Leaving: '||l_proc,10);
796 end if;
797 --
798 end p_add_to_recur_hrchy;
799 --
800 -- CWB Procedure for population of the CWB Hierarchy table
801 --
802 procedure popu_group_pil_heir(p_group_pl_id in number,
803 p_group_lf_evt_ocrd_dt in date,
804 p_group_business_group_id in number,
805 p_group_ler_id in number) is
806 --
807 l_proc VARCHAR2(80);
808 l_level number := 1 ;
809 l_emp_pil number ;
810 l_mgr_pil number ;
811 l_mgr_person_id number ;
812 l_mgr_person_id_out number ;
813 l_business_group_id number ;
814 l_pl_id number;
815 l_person_id number;
816 l_lf_evt_ocrd_dt date;
817 l_ler_id number;
818 l_rec benutils.g_batch_param_rec;
819 lv_pl_id number;
820 lv_business_group_id number;
821 lv_ler_id number;
822 lv_lf_evt_ocrd_dt date;
823 l_recursive_found boolean := false;
824 l_heirarchy_string varchar2(2000);
825 l_mgr_per_id_pos number;
826 --
827 -- Bug 2288042 : Create 0 level heirarchy data if manager is
828 -- is processed first and employee is processed later.
829 --
830 cursor c_no_0_hrchy(p_pl_id number,
831 p_lf_evt_ocrd_dt date) is
832 select unique hrh_0.mgr_per_in_ler_id
833 from ben_cwb_group_hrchy hrh_0,
834 ben_cwb_person_info mgr_info_0
835 where mgr_info_0.group_per_in_ler_id = hrh_0.emp_per_in_ler_id
836 and mgr_info_0.group_pl_id = p_pl_id
837 and mgr_info_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
838 and hrh_0.lvl_num = 1
839 and not exists
840 ( select 'Y'
841 from ben_cwb_group_hrchy hrh
842 where hrh.mgr_per_in_ler_id = hrh_0.mgr_per_in_ler_id
843 and hrh.emp_per_in_ler_id = hrh_0.mgr_per_in_ler_id
844 and hrh.lvl_num = 0
845 );
846 --
847 -- Cursor to select the pil records for emp
848 -- These are the records created initially with mgr_per_in_ler_id and
849 -- lvl_num as '-1'
850 --
851 cursor c_pil(cv_pl_id number, cv_lf_evt_ocrd_dt date) is
852 select
853 cwb.emp_per_in_ler_id,
854 pil.ws_mgr_id,
855 pil.person_id
856 from
857 ben_cwb_group_hrchy cwb,
858 ben_per_in_ler pil
859 where
860 cwb.mgr_per_in_ler_id = -1
861 and pil.per_in_ler_id = cwb.emp_per_in_ler_id
862 and pil.per_in_ler_stat_cd = 'STRTD'
863 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
864 and pil.group_pl_id = cv_pl_id
865 and cwb.lvl_num = -1;
866 --
867 cursor c_per_name(cv_person_id number, cv_lf_evt_ocrd_dt date) is
868 select full_name
869 from per_all_people_f per
870 where person_id = cv_person_id
871 and cv_lf_evt_ocrd_dt between effective_start_date
872 and effective_end_date;
873 --
874 -- This private procedure determines the Manager pil
875 -- This will get the manager pel record for a given emp - cascading
876 --
877 procedure mgr( p_person_id number,
878 p_business_group_id number,
879 p_pl_id number,
880 p_lf_evt_ocrd_dt date,
881 p_ler_id number,
882 p_ws_mgr_id out nocopy number,
883 p_per_in_ler_id out nocopy number ) is
884 --
885 cursor c_mgr(p_person_id number,
886 p_pl_id number,
887 p_lf_evt_ocrd_dt date,
888 p_ler_id number,
889 p_business_group_id number) is
890 select pil.ws_mgr_id,
891 pil.per_in_ler_id
892 from ben_per_in_ler pil
893 where pil.group_pl_id = p_pl_id
894 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
895 and pil.ler_id = p_ler_id
896 and pil.person_id = p_person_id
897 and pil.business_group_id = p_business_group_id
898 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD'); -- gsi also consider processed pils
899 --
900 l_ws_mgr_id number := null ;
901 l_per_in_ler_id number := null ;
902 begin
903 --
904 g_debug := hr_utility.debug_enabled;
905 if g_debug then
906 hr_utility.set_location('MGR p_person_id '||p_person_id,22);
907 hr_utility.set_location('MGR p_business_group_id '||p_business_group_id,23);
908 end if;
909 --
910 open c_mgr (p_person_id,p_pl_id,p_lf_evt_ocrd_dt,p_ler_id,p_business_group_id);
911 fetch c_mgr into l_ws_mgr_id,l_per_in_ler_id ;
912 close c_mgr ;
913 --
914 if g_debug then
915 hr_utility.set_location('MGR OUT l_per_in_ler_id '||l_per_in_ler_id,30);
916 hr_utility.set_location('MGR OUT l_ws_mgr_id '||l_ws_mgr_id,40);
917 end if;
918 --
919 p_per_in_ler_id := l_per_in_ler_id ;
920 p_ws_mgr_id := l_ws_mgr_id ;
921 end;
922 --
923 -- This procedure inserts records into hierarchy table
924 --
925 procedure insert_mgr_hrchy ( p_emp_per_in_ler_id number,
926 p_mgr_per_in_ler_id number,
927 p_lvl_num number ) is
928 begin
929 --
930 if g_debug then
931 hr_utility.set_location('insert_mgr_hrchy p_emp_per_in_ler_id '
932 ||p_emp_per_in_ler_id,10);
933 hr_utility.set_location('insert_mgr_hrchy p_mgr_per_in_ler_id '
934 ||p_mgr_per_in_ler_id || ' lvl = '
935 || p_lvl_num, 20);
936 end if;
937 insert into ben_cwb_group_hrchy (
938 emp_per_in_ler_id,
939 mgr_per_in_ler_id,
940 lvl_num )
941 values (
942 p_emp_per_in_ler_id,
943 p_mgr_per_in_ler_id,
944 p_lvl_num );
945 --
946 exception when others then
947 --
948 null; -- For Bug 2712602
949 --
950 end insert_mgr_hrchy;
951 --
952 procedure update_init_pil(cv_pl_id number, cv_lf_evt_ocrd_dt date) is
953 --
954 -- CWB bug : 2712602
955 --
956 cursor c_cwh is
957 select rowid
958 from ben_cwb_group_hrchy cwh
959 where cwh.lvl_num = -1 and
960 cwh.mgr_per_in_ler_id = -1
961 --
962 -- Bug 2541072 : Do not consider all per in ler's.
963 --
964 and exists
965 (select null
966 from ben_per_in_ler pil
967 where pil.per_in_ler_id = cwh.emp_per_in_ler_id
968 and pil.per_in_ler_stat_cd = 'STRTD'
969 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
970 and pil.group_pl_id = cv_pl_id
971 ) ;
972 --
973 begin
974 --
975 -- Also delete the rows for employees who do not have
976 -- subordinates and with level -1 .
977 -- And also the last subordinate is now reporting to another manager
978 -- we need to delete the pil,0,0 row of the employee.
979 --
980 delete
981 from ben_cwb_group_hrchy cwh
982 where (( cwh.lvl_num = -1
983 and cwh.mgr_per_in_ler_id = -1) OR
984 ( cwh.lvl_num = 0 and
985 cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
986 and not exists
987 (select null
988 from ben_cwb_group_hrchy cwh1
989 where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
990 and cwh1.lvl_num <> 0
991 )
992 --
993 -- Bug 2541072 : Do not consider all per in ler's.
994 --
995 and exists
996 (select null
997 from ben_per_in_ler pil
998 where pil.per_in_ler_id = cwh.emp_per_in_ler_id
999 and pil.per_in_ler_stat_cd = 'STRTD'
1000 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1001 and pil.group_pl_id = cv_pl_id
1002 ) ;
1003 /*
1004 --
1005 -- For performance this query can be used instead of above
1006 -- delete, but waiting for GSI to validate it scales better.
1007 -- For bug 4587770
1008 --
1009 select cwh.rowid
1010 from ben_per_in_ler pil, ben_cwb_group_hrchy cwh
1011 where pil.per_in_ler_id = cwh.emp_per_in_ler_id
1012 and pil.per_in_ler_stat_cd = 'STRTD'
1013 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1014 and pil.group_pl_id = cv_pl_id
1015 and (( cwh.lvl_num = -1
1016 and cwh.mgr_per_in_ler_id = -1) OR
1017 ( cwh.lvl_num = 0 and
1018 cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
1019 and not exists
1020 (select null
1021 from ben_cwb_group_hrchy cwh1
1022 where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
1023 and cwh1.lvl_num <> 0
1024 );
1025 */
1026 --
1027 -- Bug 2712602
1028 --
1029 for l_cwh in c_cwh loop
1030 --
1031 begin
1032 --
1033 update ben_cwb_group_hrchy cwh
1034 set cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id,
1035 cwh.lvl_num = 0
1036 where cwh.lvl_num = -1
1037 and cwh.mgr_per_in_ler_id = -1
1038 and cwh.rowid = l_cwh.rowid;
1039 exception
1040 when others then
1041 delete from ben_cwb_group_hrchy where rowid = l_cwh.rowid;null;
1042 end;
1043 --
1044 end loop;
1045 --
1046 exception when others then
1047 raise ;
1048 end ;
1049 --
1050 begin
1051 --
1052 if g_debug then
1053 l_proc := g_package|| '.popu_group_pil_heir';
1054 hr_utility.set_location('Entering: '||l_proc,10);
1055 end if;
1056 --
1057 lv_pl_id := p_group_pl_id;
1058 lv_business_group_id := p_group_business_group_id;
1059 lv_lf_evt_ocrd_dt := p_group_lf_evt_ocrd_dt;
1060 lv_ler_id := p_group_ler_id;
1061 --
1062 if g_debug then
1063 hr_utility.set_location(l_proc || ' lv_pl_id = ' || lv_pl_id, 9876);
1064 hr_utility.set_location(l_proc || ' lv_lf_evt_ocrd_dt = '
1065 || lv_lf_evt_ocrd_dt, 9876);
1066 hr_utility.set_location(l_proc || ' lv_business_group_id = '
1067 || lv_business_group_id, 9876);
1068 hr_utility.set_location(l_proc || ' lv_ler_id = ' || lv_ler_id, 9876);
1069 end if;
1070 open c_pil(lv_pl_id, lv_lf_evt_ocrd_dt);
1071 fetch c_pil into l_emp_pil,l_mgr_person_id,l_person_id ;
1072 --
1073 -- RECUR
1074 --
1075 g_hrchy_tbl.delete;
1076 l_recursive_found := false;
1077 l_heirarchy_string := '~'||to_char(l_person_id)||'~'||to_char(l_mgr_person_id)||'~';
1078 --
1079 -- RECUR END
1080 --
1081 --
1082 if g_debug then
1083 hr_utility.set_location(' l_emp_pil '||l_emp_pil,99);
1084 hr_utility.set_location(' l_mgr_person_id '||l_mgr_person_id,99);
1085 end if;
1086 --
1087 <<pil>>
1088 loop
1089 --
1090 exit pil when c_pil%notfound ;
1091 l_level := 1 ;
1092 --
1093 <<mgr_loop>>
1094 loop
1095 --
1096 if g_debug then
1097 hr_utility.set_location('Before mgr l_mgr_person_id '
1098 ||l_mgr_person_id,10);
1099 end if;
1100 --
1101 mgr(l_mgr_person_id,
1102 lv_business_group_id,
1103 lv_pl_id,
1104 lv_lf_evt_ocrd_dt,
1105 lv_ler_id,
1106 l_mgr_person_id_out,
1107 l_mgr_pil);
1108 --
1109 if g_debug then
1110 hr_utility.set_location('After Mgr l_mgr_person_id '
1111 ||l_mgr_person_id,20);
1112 hr_utility.set_location('After Mgr l_mgr_person_id_out '
1113 ||l_mgr_person_id_out,20);
1114 hr_utility.set_location('After Mgr l_mgr_pil '|| l_mgr_pil,30);
1115 end if;
1116 --
1117 --
1118 -- RECUR
1119 --
1120 if l_mgr_person_id_out is not null then
1121 l_mgr_per_id_pos := instr (l_heirarchy_string, '~'|| to_char(l_mgr_person_id_out) || '~');
1122 if l_mgr_per_id_pos > 0 then
1123 --
1124 l_recursive_found := true;
1125 -- hr_utility.set_location('level = ' || l_level, 99);
1126 -- hr_utility.set_location('l_heirarchy_string = ' || l_heirarchy_string, 99);
1127 -- Now go ahead and store in a pl/sql table so that we print each occurance only once
1128 p_add_to_recur_hrchy(l_heirarchy_string|| to_char(l_mgr_person_id_out) || '~',
1129 '~'||to_char(l_mgr_person_id) || '~' || to_char(l_mgr_person_id_out) || '~');
1130 --
1131 end if;
1132 l_heirarchy_string := l_heirarchy_string || to_char(l_mgr_person_id_out) || '~';
1133 end if;
1134 --
1135 -- RECUR END
1136 --
1137 if l_mgr_pil is not null then
1138 --
1139 if l_emp_pil = l_mgr_pil and l_level > 0 then
1140 --
1141 -- Bug 3748539
1142 -- This situation indicates it is a recursive supervisory heirarchy.
1143 -- Raise error and rollback the heirarchy rebuild.
1144 --
1145 hr_utility.set_location('BEN_94020_RECURSIVE_EMP_HEIRAR', 999);
1146 /* RECUR
1147 fnd_message.set_name('BEN', 'BEN_94020_RECURSIVE_EMP_HEIRAR');
1148 fnd_message.set_token('PERSONID',to_char(l_person_id));
1149 fnd_message.set_token('MGRPERSONID',to_char(l_mgr_person_id));
1150 fnd_message.raise_error;
1151 */ -- RECUR
1152 --
1153 end if;
1154 --
1155 insert_mgr_hrchy(l_emp_pil,l_mgr_pil,l_level);
1156 --
1157 end if;
1158 --
1159 -- Bug 4399281 : Assume recursive heirarchy : A reports to B, B reports to
1160 -- C, C Reports to B. This scenario is not caught by error
1161 -- BEN_94020_RECURSIVE_EMP_HEIRAR if person A is picked up in
1162 -- heirarchy build first.
1163 -- By adding check l_level > 75 infinite loop is broken and
1164 -- when person B is picked up to build heirarchy above error
1165 -- is raised.
1166 --
1167 exit mgr_loop when (l_mgr_person_id = l_mgr_person_id_out
1168 OR l_mgr_person_id_out is null or l_recursive_found or
1169 l_level > 75) ;
1170 --call to insert routne
1171 if g_debug then
1172 hr_utility.set_location('Emp EPE '||l_emp_pil , 20);
1173 hr_utility.set_location('Mgr EPE '||l_mgr_pil , 30);
1174 hr_utility.set_location('Level '||l_level , 40);
1175 end if;
1176 --
1177 --
1178 --after call to insert routine
1179 --
1180 l_mgr_person_id := l_mgr_person_id_out ;
1181 l_level := l_level + 1 ;
1182 l_mgr_pil := null ;
1183 --
1184 end loop mgr_loop;
1185 --
1186 fetch c_pil into l_emp_pil,l_mgr_person_id, l_person_id ;
1187 l_recursive_found := false;
1188 l_heirarchy_string := '~'||to_char(l_person_id)||'~'||to_char(l_mgr_person_id)||'~';
1189 --
1190 if g_debug then
1191 hr_utility.set_location(' End of mgr_loop ',99);
1192 end if;
1193 end loop pil ;
1194 --
1195 close c_pil ;
1196 --
1197 --call to delete the intial pil records
1198 if g_debug then
1199 hr_utility.set_location('Before call to delete_init_pil',10);
1200 end if;
1201 update_init_pil(lv_pl_id, lv_lf_evt_ocrd_dt) ;
1202 if g_debug then
1203 hr_utility.set_location('After call to delete_init_pil',10);
1204 end if;
1205
1206 -- Bug 4587770
1207 -- Backout already deletes the data, so no need to call again here,
1208 -- but keeping it for now and should be called only called from the
1209 -- concurrent programs.
1210 --
1211 if fnd_global.conc_request_id not in ( 0,-1) then
1212 --
1213 -- CWB 2712602 : Delete all the hrchy data linked to backed out per in ler.
1214 --
1215 delete from ben_cwb_group_hrchy
1216 where emp_per_in_ler_id in (
1217 select pil.per_in_ler_id
1218 from ben_per_in_ler pil
1219 where pil.group_pl_id = lv_pl_id
1220 and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
1221 and pil.per_in_ler_stat_cd = 'BCKDT');
1222 --
1223 delete from ben_cwb_group_hrchy
1224 where mgr_per_in_ler_id in (
1225 select pil.per_in_ler_id
1226 from ben_per_in_ler pil
1227 where pil.group_pl_id = lv_pl_id
1228 and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
1229 and pil.per_in_ler_stat_cd = 'BCKDT');
1230 --
1231 end if;
1232 --
1233 -- Bug 2288042
1234 -- Create 0 level heirarchy data for managers for whom this data
1235 -- is missing.
1236 --
1237 for l_no_0_hrchy in
1238 c_no_0_hrchy(lv_pl_id, lv_lf_evt_ocrd_dt) loop
1239 --
1240 begin
1241 --
1242 insert into ben_cwb_group_hrchy (
1243 emp_per_in_ler_id,
1244 mgr_per_in_ler_id,
1245 lvl_num )
1246 values (
1247 l_no_0_hrchy.mgr_per_in_ler_id,
1248 l_no_0_hrchy.mgr_per_in_ler_id,
1249 0 );
1250 --
1251 exception when others then
1252 null;
1253 end;
1254 --
1255 end loop;
1256 if g_debug then
1257 hr_utility.set_location('Leaving: '||l_proc,10);
1258 end if;
1259 --
1260 end popu_group_pil_heir;
1261 --
1262 procedure get_group_plan_info(p_pl_id in number,
1263 p_lf_evt_ocrd_dt in date,
1264 p_business_group_id in number default null,
1265 -- 9999IK Not required if we only run for group pl
1266 p_group_pl_id in number default null
1267 ) is
1268 --
1269 l_proc VARCHAR2(80);
1270 --
1271 cursor get_group_pl_fr_act_pl_info(cv_pl_id number,
1272 cv_lf_evt_ocrd_dt date) is
1273 select pet.pl_id, enp.ASND_LF_EVT_DT,
1274 pet.business_group_id, enp.ler_id,
1275 enp.hrchy_to_use_cd,
1276 enp.pos_structure_version_id,
1277 enp.dflt_ws_acc_cd,
1278 enp.end_dt,
1279 enp.auto_distr_flag,
1280 enp.ws_upd_strt_dt,
1281 enp.ws_upd_end_dt,
1282 enp.uses_bdgt_flag,
1283 enp.hrchy_ame_trn_cd,
1284 enp.hrchy_rl,
1285 -- Bug 5232223
1286 group_pln.trk_inelig_per_flag
1287 from ben_popl_enrt_typ_cycl_f pet,
1288 ben_enrt_perd enp,
1289 ben_ler_f ler,
1290 ben_pl_f pln,
1291 ben_pl_f group_pln
1292 where enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
1293 and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
1294 and pet.business_group_id = enp.business_group_id
1295 and cv_lf_evt_ocrd_dt
1296 between pet.effective_start_date
1297 and pet.effective_end_date
1298 and ler.typ_cd = 'COMP'
1299 and ler.business_group_id = pet.business_group_id
1300 and cv_lf_evt_ocrd_dt
1301 between ler.effective_start_date
1302 and ler.effective_end_date
1303 and ler.ler_id = enp.ler_id
1304 and pet.pl_id = group_pln.pl_id
1305 and cv_lf_evt_ocrd_dt
1306 between group_pln.effective_start_date
1307 and group_pln.effective_end_date
1308 and pln.group_pl_id = group_pln.pl_id
1309 and pln.pl_id = cv_pl_id
1310 and cv_lf_evt_ocrd_dt
1311 between pln.effective_start_date
1312 and pln.effective_end_date;
1313 --
1314 cursor get_group_pl_info(cv_pl_id number,
1315 cv_lf_evt_ocrd_dt date) is
1316 select pet.pl_id,
1317 enp.ASND_LF_EVT_DT,
1318 pet.business_group_id, enp.ler_id,
1319 enp.hrchy_to_use_cd,
1320 enp.pos_structure_version_id,
1321 enp.dflt_ws_acc_cd,
1322 enp.end_dt,
1323 enp.auto_distr_flag,
1324 enp.ws_upd_strt_dt,
1325 enp.ws_upd_end_dt,
1326 enp.uses_bdgt_flag,
1327 enp.hrchy_ame_trn_cd,
1328 enp.hrchy_rl,
1329 -- Bug 5232223
1330 group_pln.trk_inelig_per_flag
1331 from ben_popl_enrt_typ_cycl_f pet,
1332 ben_enrt_perd enp,
1333 ben_ler_f ler,
1334 ben_pl_f group_pln
1335 where enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
1336 and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
1337 -- and pet.business_group_id = enp.business_group_id
1338 and cv_lf_evt_ocrd_dt
1339 between pet.effective_start_date
1340 and pet.effective_end_date
1341 and ler.typ_cd = 'COMP'
1342 -- and ler.business_group_id = pet.business_group_id
1343 and cv_lf_evt_ocrd_dt
1344 between ler.effective_start_date
1345 and ler.effective_end_date
1346 and ler.ler_id = enp.ler_id
1347 and pet.pl_id = group_pln.pl_id
1348 and cv_lf_evt_ocrd_dt
1349 between group_pln.effective_start_date
1350 and group_pln.effective_end_date
1351 and group_pln.pl_id = cv_pl_id
1352 and cv_lf_evt_ocrd_dt
1353 between group_pln.effective_start_date
1354 and group_pln.effective_end_date;
1355 --
1356 cursor get_pl_wthn_group_pl(cv_group_pl_id number,
1357 cv_lf_evt_ocrd_dt date) is
1358 select count(*)
1359 from ben_pl_f
1360 where group_pl_id = cv_group_pl_id
1361 and cv_lf_evt_ocrd_dt between effective_start_date and
1362 effective_end_date;
1363 --
1364 begin
1365 --
1366 if g_debug then
1367 l_proc := g_package|| '.get_group_plan_info';
1368 hr_utility.set_location('Entering: '||l_proc,10);
1369 end if;
1370 --
1371 if g_cache_group_plan_rec.group_pl_id is null then
1372 --
1373 if p_group_pl_id is not null then
1374 --
1375 open get_group_pl_info(p_group_pl_id, p_lf_evt_ocrd_dt);
1376 fetch get_group_pl_info into g_cache_group_plan_rec.group_pl_id,
1377 g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
1378 g_cache_group_plan_rec.group_business_group_id,
1379 g_cache_group_plan_rec.group_ler_id,
1380 g_cache_group_plan_rec.hrchy_to_use_cd,
1381 g_cache_group_plan_rec.pos_structure_version_id,
1382 g_cache_group_plan_rec.access_cd,
1383 g_cache_group_plan_rec.end_dt,
1384 g_cache_group_plan_rec.auto_distr_flag,
1385 g_cache_group_plan_rec.ws_upd_strt_dt,
1386 g_cache_group_plan_rec.ws_upd_end_dt,
1387 g_cache_group_plan_rec.uses_bdgt_flag,
1388 g_cache_group_plan_rec.hrchy_ame_trn_cd,
1389 g_cache_group_plan_rec.hrchy_rl,
1390 -- Bug 5232223
1391 g_cache_group_plan_rec.trk_inelig_per_flag;
1392 close get_group_pl_info;
1393 --
1394 else
1395 --
1396 open get_group_pl_fr_act_pl_info(p_pl_id, p_lf_evt_ocrd_dt);
1397 fetch get_group_pl_fr_act_pl_info into g_cache_group_plan_rec.group_pl_id,
1398 g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
1399 g_cache_group_plan_rec.group_business_group_id,
1400 g_cache_group_plan_rec.group_ler_id,
1401 g_cache_group_plan_rec.hrchy_to_use_cd,
1402 g_cache_group_plan_rec.pos_structure_version_id,
1403 g_cache_group_plan_rec.access_cd,
1404 g_cache_group_plan_rec.end_dt,
1405 g_cache_group_plan_rec.auto_distr_flag,
1406 g_cache_group_plan_rec.ws_upd_strt_dt,
1407 g_cache_group_plan_rec.ws_upd_end_dt,
1408 g_cache_group_plan_rec.uses_bdgt_flag,
1409 g_cache_group_plan_rec.hrchy_ame_trn_cd,
1410 g_cache_group_plan_rec.hrchy_rl,
1411 -- Bug 5232223
1412 g_cache_group_plan_rec.trk_inelig_per_flag;
1413 close get_group_pl_fr_act_pl_info;
1414 --
1415 end if;
1416 --
1417 open get_pl_wthn_group_pl(g_cache_group_plan_rec.group_pl_id,
1418 g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
1419 fetch get_pl_wthn_group_pl into g_cache_group_plan_rec.plans_wthn_group_pl;
1420 close get_pl_wthn_group_pl;
1421 --
1422 end if;
1423 --
1424 if g_debug then
1425 hr_utility.set_location('Leaving: '||l_proc,100);
1426 end if;
1427 --
1428 end get_group_plan_info;
1429 --
1430 procedure popu_cwb_tables(
1431 p_group_per_in_ler_id in number,
1432 p_group_pl_id in number,
1433 p_group_lf_evt_ocrd_dt in date,
1434 p_group_business_group_id in number,
1435 p_group_ler_id in number,
1436 p_use_eff_dt_flag in varchar2 default 'N',
1437 p_effective_date in date default null
1438 ) is
1439 --
1440 l_proc varchar2(80);
1441 --
1442 l_emp_pil_id number;
1443 l_effective_date date := p_effective_date;
1444 --
1445 cursor c_cwb_hrchy(cv_emp_pil_id in number) is
1446 select emp_per_in_ler_id
1447 from ben_cwb_group_hrchy
1448 where emp_per_in_ler_id = cv_emp_pil_id;
1449 --
1450 cursor c_cwb_tasks(cv_pl_id in number) is
1451 select *
1452 from ben_cwb_wksht_grp
1453 where PL_ID = cv_PL_ID
1454 and STATUS_CD = 'A';
1455 --
1456 begin
1457 --
1458 --
1459 if g_debug then
1460 l_proc := g_package|| '.popu_cwb_tables';
1461 hr_utility.set_location('Entering: '||l_proc,100);
1462 end if;
1463 --
1464 g_cache_group_plan_rec.group_per_in_ler_id := p_group_per_in_ler_id;
1465 --
1466 -- Populate ben_cwb_group_hrchy
1467 --
1468 open c_cwb_hrchy(p_group_per_in_ler_id);
1469 fetch c_cwb_hrchy into l_emp_pil_id;
1470 --
1471 hr_utility.set_location('p_group_per_in_ler_id: '||p_group_per_in_ler_id,111);
1472 if c_cwb_hrchy%notfound then
1473 --
1474 insert into ben_cwb_group_hrchy (
1475 emp_per_in_ler_id,
1476 mgr_per_in_ler_id,
1477 lvl_num,
1478 OBJECT_VERSION_NUMBER )
1479 values(
1480 p_group_per_in_ler_id,
1481 -1,
1482 -1,
1483 1);
1484 --
1485 end if;
1486 --
1487 close c_cwb_hrchy;
1488 --
1489 -- Populate ben_cwb_person tasks
1490 --
1491 for l_cwb_tasks in c_cwb_tasks(p_group_pl_id) loop
1492 --
1493 insert into ben_cwb_person_tasks
1494 (GROUP_PER_IN_LER_ID
1495 ,TASK_ID
1496 ,GROUP_PL_ID
1497 ,LF_EVT_OCRD_DT
1498 ,STATUS_CD
1499 ,ACCESS_CD
1500 ,OBJECT_VERSION_NUMBER)
1501 values
1502 (p_group_per_in_ler_id,
1503 l_cwb_tasks.CWB_WKSHT_GRP_ID,
1504 p_group_pl_id,
1505 p_group_lf_evt_ocrd_dt,
1506 'NS',
1507 nvl(l_cwb_tasks.hidden_cd, 'UP'),
1508 1
1509 );
1510 --
1511 end loop;
1512 --
1513 if p_use_eff_dt_flag = 'N' then
1514 l_effective_date := null;
1515 end if;
1516 --
1517 BEN_CWB_PERSON_INFO_PKG.refresh_person_info
1518 (p_group_per_in_ler_id => p_group_per_in_ler_id,
1519 p_effective_date => l_effective_date,
1520 p_called_from_benmngle => true);
1521 --
1522 if g_debug then
1523 hr_utility.set_location('Leaving: '||l_proc,100);
1524 end if;
1525 --
1526 end popu_cwb_tables;
1527 --
1528 procedure get_cwb_manager_and_assignment
1529 (p_person_id in number,
1530 p_hrchy_to_use_cd in varchar2,
1531 p_pos_structure_version_id in number,
1532 p_effective_date in date,
1533 p_manager_id out nocopy number,
1534 p_assignment_id out nocopy number )
1535 is
1536 --Bug 2827121 Manager can be a contingent worker also.
1537
1538 CURSOR c_get_assignment IS
1539 SELECT assignment_id, position_id, organization_id, supervisor_id, business_group_id
1540 FROM per_all_assignments_f
1541 WHERE person_id = p_person_id
1542 AND primary_flag = 'Y'
1543 AND assignment_type IN ('E', 'C','B') -- Bug 2827121 --Bug 5148387
1544 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1545 order by decode(assignment_type,'E',1,'C',2,3);
1546
1547 --
1548 l_get_assignment c_get_assignment%ROWTYPE;
1549
1550 --
1551 CURSOR c_parent_position_id (p_position_id NUMBER) IS
1552 SELECT parent_position_id
1553 FROM per_pos_structure_elements
1554 WHERE subordinate_position_id = p_position_id
1555 AND pos_structure_version_id = p_pos_structure_version_id;
1556
1557 --
1558 CURSOR c_manager_id (p_position_id NUMBER) IS
1559 SELECT person_id
1560 FROM per_all_assignments_f ass,
1561 per_assignment_status_types ast
1562 WHERE ass.position_id = p_position_id
1563 AND ass.primary_flag = 'Y'
1564 AND ass.assignment_type IN ('E', 'C') -- Bug 2827121
1565 AND p_effective_date BETWEEN ass.effective_start_date
1566 AND ass.effective_end_date
1567 --Bug 3044311 -- Need to verify what other system types should be considered.
1568 AND ass.assignment_status_type_id = ast.assignment_status_type_id
1569 -- and ast.active_flag = 'Y'
1570 AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
1571
1572 --
1573 /*
1574 CURSOR c_sched_enrol_period_for_plan IS
1575 SELECT enrtp.enrt_perd_id,
1576 enrtp.strt_dt,
1577 enrtp.end_dt,
1578 enrtp.procg_end_dt,
1579 enrtp.dflt_enrt_dt,
1580 petc.enrt_typ_cycl_cd,
1581 enrtp.cls_enrt_dt_to_use_cd,
1582 enrtp.hrchy_to_use_cd,
1583 enrtp.pos_structure_version_id,
1584 enrtp.enrt_perd_det_ovrlp_bckdt_cd
1585 FROM ben_popl_enrt_typ_cycl_f petc,
1586 ben_enrt_perd enrtp,
1587 ben_ler_f ler
1588 WHERE petc.pl_id = l_pl_id
1589 AND petc.business_group_id = p_business_group_id
1590 AND l_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
1591 AND petc.effective_end_date
1592 AND petc.enrt_typ_cycl_cd <> 'L'
1593 AND enrtp.business_group_id = p_business_group_id
1594 AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
1595 AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
1596 and ler.ler_id (+) = enrtp.ler_id
1597 and ler.ler_id (+) = p_ler_id
1598 and l_lf_evt_ocrd_dt between ler.effective_start_date (+)
1599 and ler.effective_end_date (+);
1600 */
1601 --
1602 l_proc varchar2(80);
1603
1604 l_parent_position_id NUMBER (15);
1605 l_manager_id NUMBER (15);
1606 l_assignment_id NUMBER (15);
1607 l_position_id NUMBER (15);
1608 l_transaction_type_id VARCHAR2 (50);
1609 l_application_id_out NUMBER;
1610 l_application_id NUMBER;
1611 l_ame_approver ame_util.approverrecord;
1612 l_outputs ff_exec.outputs_t;
1613 l_loc_rec hr_locations_all%ROWTYPE;
1614 -- l_ass_rec per_all_assignments_f%ROWTYPE;
1615 l_jurisdiction_code VARCHAR2 (30);
1616 --
1617
1618 BEGIN
1619 --
1620 if g_debug then
1621 l_proc := g_package|| '.get_cwb_manager_and_assignment';
1622 hr_utility.set_location('Entering: '||l_proc,100);
1623 end if;
1624 --
1625 hr_utility.set_location('p_hrchy_to_use_cd '|| p_hrchy_to_use_cd,100);
1626 --
1627 OPEN c_get_assignment;
1628 FETCH c_get_assignment INTO l_get_assignment;
1629 CLOSE c_get_assignment;
1630 --
1631 l_assignment_id := l_get_assignment.assignment_id;
1632
1633 --
1634 IF p_hrchy_to_use_cd = 'S' THEN
1635 l_manager_id := l_get_assignment.supervisor_id;
1636 ELSIF p_hrchy_to_use_cd = 'P' THEN
1637 -- Start Bug 2684227
1638 -- Upon a vacancy, continue to climb the position hierarchy
1639 -- until a person is found
1640 l_position_id := l_get_assignment.position_id;
1641
1642 --
1643 LOOP
1644 OPEN c_parent_position_id (l_position_id);
1645 FETCH c_parent_position_id INTO l_parent_position_id;
1646 EXIT WHEN c_parent_position_id%NOTFOUND;
1647 CLOSE c_parent_position_id;
1648
1649 IF l_parent_position_id IS NOT NULL THEN
1650 OPEN c_manager_id (l_parent_position_id);
1651 FETCH c_manager_id INTO l_manager_id;
1652 CLOSE c_manager_id;
1653
1654 IF l_manager_id IS NOT NULL THEN
1655 EXIT;
1656 END IF;
1657 END IF;
1658
1659 l_position_id := l_parent_position_id;
1660 END LOOP;
1661
1662 -- End Bug 2684227
1663 -- Bug 2230922 : If manager id not found then default to supervisor.
1664 IF l_manager_id IS NULL THEN
1665 --
1666 l_manager_id := l_get_assignment.supervisor_id;
1667 --
1668 END IF;
1669 --
1670 -- Bug: 3979082: Changes start here
1671 ELSIF p_hrchy_to_use_cd = 'AME' THEN
1672 -- Use AME Hierarchy to fetch manager hierarchy.
1673 l_application_id := 805; -- Default it to 805
1674 -- Fetch the next approver
1675 hr_utility.set_location(' g_cache_group_plan_rec.hrchy_ame_trn_cd '
1676 || g_cache_group_plan_rec.hrchy_ame_trn_cd, 20);
1677 hr_utility.set_location(' p_person_id '|| p_person_id, 20);
1678 --
1679 BEGIN
1680 ame_api.getnextapprover (applicationidin => l_application_id,
1681 transactionidin => p_person_id,
1682 transactiontypein => g_cache_group_plan_rec.hrchy_ame_trn_cd,
1683 nextapproverout => l_ame_approver
1684 );
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 fnd_message.set_name('BEN','BEN_94119_AME_APPL_ERR');
1688 fnd_message.set_token('AME_ERROR', SQLERRM);
1689 fnd_message.raise_error;
1690 END;
1691 hr_utility.set_location(' l_ame_approver.person_id '|| l_ame_approver.person_id, 20);
1692 --
1693 l_manager_id := l_ame_approver.person_id;
1694 --
1695 ELSIF p_hrchy_to_use_cd = 'RL' THEN
1696 -- Use Rule to fetch Manager Id.
1697 hr_utility.set_location(' process RULE hrchy_rl '||g_cache_group_plan_rec.hrchy_rl, 20);
1698 --
1699 l_outputs :=
1700 benutils.formula (p_formula_id => g_cache_group_plan_rec.hrchy_rl
1701 ,p_effective_date => NVL(g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
1702 p_effective_date)
1703 ,p_assignment_id => l_get_assignment.assignment_id
1704 ,p_organization_id => l_get_assignment.organization_id
1705 ,p_business_group_id => l_get_assignment.business_group_id
1706 ,p_pl_id => g_cache_group_plan_rec.group_pl_id
1707 ,p_ler_id => g_cache_group_plan_rec.group_ler_id
1708 -- ENTER INPUT VALUES HERE
1709 ,p_param1 => 'BEN_IV_PERSON_ID'
1710 ,p_param1_value => p_person_id
1711 ,p_param2 => 'BEN_IV_ACCESS_CD'
1712 ,p_param2_value => g_cache_group_plan_rec.access_cd
1713 ,p_param3 => 'BEN_IV_END_DT'
1714 ,p_param3_value => fnd_date.date_to_canonical(g_cache_group_plan_rec.end_dt)
1715 ,p_param4 => 'BEN_IV_AUTO_DISTR_FLAG'
1716 ,p_param4_value => g_cache_group_plan_rec.auto_distr_flag
1717 ,p_param5 => 'BEN_IV_WS_UPD_STRT_DT'
1718 ,p_param5_value => fnd_date.date_to_canonical(g_cache_group_plan_rec.ws_upd_strt_dt)
1719 ,p_param6 => 'BEN_IV_WS_UPD_END_DT'
1720 ,p_param6_value => fnd_date.date_to_canonical(g_cache_group_plan_rec.ws_upd_end_dt)
1721 ,p_param7 => 'BEN_IV_USES_BDGT_FLAG'
1722 ,p_param7_value => g_cache_group_plan_rec.uses_bdgt_flag
1723 );
1724 --
1725 l_manager_id := TO_NUMBER(l_outputs(l_outputs.FIRST).VALUE);
1726 --
1727 hr_utility.set_location(' Rule ret MGR_ID '|| l_manager_id, 20);
1728 --
1729 -- Bug: 3979082: Changes end here
1730 END IF;
1731 --
1732 p_manager_id := l_manager_id;
1733 p_assignment_id := l_assignment_id;
1734 --
1735 if g_debug then
1736 l_proc := g_package|| '.get_cwb_manager_and_assignment';
1737 hr_utility.set_location('Leaving: '||l_proc,100);
1738 end if;
1739 --
1740 EXCEPTION -- nocopy changes
1741 --
1742 WHEN OTHERS THEN
1743 --
1744 p_manager_id := NULL;
1745 p_assignment_id := NULL;
1746 RAISE;
1747 end get_cwb_manager_and_assignment;
1748 --
1749 -- NOTE : THIS PROCEDURE SHOULD NOT BE CALLED/USED WITHOUT CONTACTING
1750 -- TY HAYDEN OR PRASAD BODLA
1751 -- TRACK INELIG FLAG IS NO LONGER USED.
1752 -- deletes per in lers and associated data for people with no elctble chc
1753 -- for actual plans, if trk_inelig_per_flag is set to N
1754 --
1755 procedure del_all_cwb_pils
1756 (p_person_id in number default null,
1757 p_group_pl_id in number,
1758 p_group_ler_id in number,
1759 p_group_lf_evt_ocrd_dt in date) is
1760 --
1761 cursor c_popl(cv_per_in_ler_id number) is
1762 select pel.pil_elctbl_chc_popl_id,
1763 pel.object_version_number pel_ovn
1764 from ben_pil_elctbl_chc_popl pel
1765 where pel.per_in_ler_id = cv_per_in_ler_id;
1766 --
1767 l_popl_rec c_popl%rowtype;
1768 --
1769 cursor c_epe(cv_per_in_ler_id number,
1770 cv_pil_elctbl_chc_popl_id number) is
1771 select epe.elig_per_elctbl_chc_id,
1772 epe.object_version_number epe_ovn
1773 from ben_elig_per_elctbl_chc epe
1774 where epe.per_in_ler_id = cv_per_in_ler_id
1775 and cv_pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
1776 --
1777 cursor c_pln(cv_effective_date date) is
1778 select pl.trk_inelig_per_flag
1779 from ben_pl_f pl
1780 where pl.pl_id = p_group_pl_id
1781 and cv_effective_date between pl.effective_start_date and
1782 pl.effective_end_date;
1783 --
1784 l_trk_inelig_flag varchar2(30);
1785 --
1786 -- Can we identify seeing group_per_in_ler_id is -1 and
1787 -- all rows for people with non -1 group_per_in_ler_id
1788 -- and not in ws_mgr_id
1789 --
1790 -- Bug 5232223 : modified cursor
1791 cursor c_del_inelg_per(cv_group_pl_id number, cv_lf_evt_ocrd_dt date)
1792 is
1793 select pil.per_in_ler_id,
1794 pil.object_version_number pil_ovn,
1795 pil.business_group_id,
1796 ptnl.ptnl_ler_for_per_id,
1797 ptnl.object_version_number ptnl_ovn
1798 from ben_per_in_ler pil,
1799 ben_ptnl_ler_for_per ptnl,
1800 ben_cwb_person_info cpi
1801 where pil.group_pl_id = cv_group_pl_id
1802 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1803 and pil.per_in_ler_stat_cd = 'STRTD'
1804 and cpi.group_per_in_ler_id = pil.per_in_ler_id
1805 and cpi.person_id = -1
1806 and ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
1807 and ptnl.ptnl_ler_for_per_stat_cd = 'PROCD'
1808 and not exists
1809 (select 'Y'
1810 from ben_cwb_group_hrchy hrh
1811 where hrh.mgr_per_in_ler_id = pil.per_in_ler_id
1812 and hrh.lvl_num > 0)
1813 and not exists
1814 (select 'Y'
1815 from ben_cwb_person_rates
1816 where group_pl_id = cv_group_pl_id
1817 and person_id = pil.person_id
1818 and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1819 and elig_flag = 'Y');
1820 --
1821 l_package varchar2(80);
1822 --
1823 begin
1824 --
1825 /* If track inelig flag is N for group plan then
1826 *
1827 * For given GROUP_PL_ID, GROUP_OIPL_ID, LF_EVT_OCRD_DT
1828 *
1829 * if there are no rows in ben_cwb_person_rates with
1830 * ELIG_FLAG = Y and only consider uncloned data
1831 * then
1832 *
1833 * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,
1834 * ben_per_in_ler, ben_ptnl_ler_for_per, ben_pil_elctbl_chc_popl,
1835 * ben_elig_per_elctbl_chc, ben_group_heirarchy,
1836 * ben_cwb_person_tasks.
1837 */
1838 if g_debug then
1839 l_package := g_package||'.del_all_cwb_pils';
1840 hr_utility.set_location ('Entering '||l_package,10);
1841 end if;
1842 --
1843 -- This cursor can be avoided if it is fetched in main process.
1844 --
1845 open c_pln(p_group_lf_evt_ocrd_dt);
1846 fetch c_pln into g_trk_inelig_flag;
1847 if g_trk_inelig_flag = 'N' then
1848 --
1849 for del_inelg_per_rec in c_del_inelg_per(p_group_pl_id,
1850 p_group_lf_evt_ocrd_dt)
1851 loop
1852 --
1853 for l_popl_rec in c_popl(del_inelg_per_rec.per_in_ler_id)
1854 loop
1855 --
1856 -- First delete epe data.
1857 --
1858 for l_epe_rec in c_epe(del_inelg_per_rec.per_in_ler_id,
1859 l_popl_rec.pil_elctbl_chc_popl_id)
1860 loop
1861 --
1862 ben_elig_per_elc_chc_api.delete_ELIG_PER_ELC_CHC
1863 (p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id,
1864 p_object_version_number => l_epe_rec.epe_ovn,
1865 p_effective_date => p_group_lf_evt_ocrd_dt);
1866 --
1867 end loop;
1868 --
1869 ben_Pil_Elctbl_chc_Popl_api.delete_Pil_Elctbl_chc_Popl
1870 (p_pil_elctbl_chc_popl_id => l_popl_rec.pil_elctbl_chc_popl_id,
1871 p_object_version_number => l_popl_rec.pel_ovn,
1872 p_effective_date => p_group_lf_evt_ocrd_dt);
1873 --
1874 end loop;
1875 --
1876 --
1877 -- Now delete CWB data.
1878 --
1879 ben_cwb_back_out_conc.delete_cwb_data(
1880 p_per_in_ler_id => del_inelg_per_rec.per_in_ler_id
1881 ,p_business_group_id => del_inelg_per_rec.business_group_id
1882 );
1883 --
1884 ben_Person_Life_Event_api.delete_Person_Life_Event
1885 (p_per_in_ler_id => del_inelg_per_rec.per_in_ler_id,
1886 p_object_version_number => del_inelg_per_rec.pil_ovn,
1887 p_effective_date => p_group_lf_evt_ocrd_dt);
1888
1889 ben_ptnl_ler_for_per_api.delete_ptnl_ler_for_per
1890 (p_ptnl_ler_for_per_id => del_inelg_per_rec.ptnl_ler_for_per_id,
1891 p_object_version_number => del_inelg_per_rec.ptnl_ovn,
1892 p_effective_date => p_group_lf_evt_ocrd_dt);
1893 --
1894 end loop;
1895 --
1896 end if;
1897 --
1898 if g_debug then
1899 hr_utility.set_location ('Leaving '||l_package,10);
1900 end if;
1901 --
1902 end del_all_cwb_pils;
1903 --
1904 --
1905 -- ----------------------------------------------------------------------------
1906 -- |------------------------< p_single_per_clone_all_data >----------------------|
1907 -- ----------------------------------------------------------------------------
1908 --
1909 procedure p_single_per_clone_all_data(
1910 p_person_id in number
1911 ,p_business_group_id in number
1912 ,p_ler_id in number default null
1913 ,p_effective_date in date
1914 ,p_lf_evt_ocrd_dt in date
1915 ,p_pl_id in number
1916 ,p_clone_only_cpg in varchar2 default 'N'
1917 ) is
1918 --
1919 l_ptnl_ler_for_per_id BEN_PTNL_LER_FOR_PER.PTNL_LER_FOR_PER_ID%TYPE;
1920 l_curr_per_in_ler_id number;
1921 l_object_version_number BEN_PTNL_LER_FOR_PER.OBJECT_VERSION_NUMBER%TYPE;
1922 l_pil_object_version_number BEN_PTNL_LER_FOR_PER.OBJECT_VERSION_NUMBER%TYPE;
1923 l_ws_mgr_id number;
1924 l_assignment_id number;
1925 l_ler_id number;
1926 l_procd_dt date;
1927 l_strtd_dt date;
1928 l_voidd_dt date;
1929 l_proc varchar2(72) := g_package||'.p_single_per_clone_all_data';
1930 --
1931 cursor c_popl_enrt_typ_cycl(cv_lf_evt_ocrd_dt date,
1932 cv_business_group_id number,
1933 cv_effective_date date,
1934 cv_pl_id number) is
1935 select ler.ler_id
1936 from ben_popl_enrt_typ_cycl_f pet,
1937 ben_enrt_perd enp,
1938 ben_ler_f ler
1939 where enp.business_group_id = cv_business_group_id
1940 and enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
1941 and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
1942 and pet.business_group_id = enp.business_group_id
1943 and cv_effective_date between pet.effective_start_date
1944 and pet.effective_end_date
1945 and ler.typ_cd = 'COMP'
1946 and ler.business_group_id = pet.business_group_id
1947 and cv_effective_date between ler.effective_start_date
1948 and ler.effective_end_date
1949 and ler.ler_id = enp.ler_id
1950 and pet.pl_id = cv_pl_id;
1951 --
1952 cursor c_cpg(c_group_pl_id number, c_group_lf_evt_ocrd_dt date) is
1953 select cpg.*
1954 from ben_cwb_person_groups cpg
1955 where cpg.group_pl_id = c_group_pl_id
1956 and cpg.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1957 and cpg.group_per_in_ler_id =
1958 (select cpg1.group_per_in_ler_id
1959 from ben_cwb_person_groups cpg1
1960 where cpg1.group_pl_id = c_group_pl_id
1961 and cpg1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1962 and rownum = 1);
1963 --
1964 CURSOR c_person_rates
1965 (c_group_lf_evt_ocrd_dt IN DATE
1966 ,c_group_pl_id IN NUMBER
1967 ,c_pl_id IN NUMBER
1968 )
1969 is
1970 select cpr.rowid, cpr.*
1971 from ben_cwb_person_rates cpr
1972 where cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1973 and cpr.group_pl_id = c_group_pl_id
1974 and cpr.pl_id = c_pl_id
1975 and cpr.person_id =
1976 (select cpr1.person_id
1977 from ben_cwb_person_rates cpr1
1978 where cpr1.group_pl_id = c_group_pl_id
1979 and cpr1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1980 and cpr1.pl_id = c_pl_id
1981 and rownum = 1);
1982 --
1983 cursor c_asg is
1984 select asg.assignment_id
1985 from per_all_assignments_f asg
1986 where asg.person_id = p_person_id
1987 and asg.primary_flag = 'Y'
1988 and p_effective_date
1989 between asg.effective_start_date
1990 and asg.effective_end_date
1991 order by asg.assignment_type desc;
1992 --
1993 cursor get_mgr_pil_id (cv_person_id in number,
1994 cv_lf_evt_ocrd_dt in date,
1995 cv_group_ler_id in number) is
1996 select per_in_ler_id
1997 from ben_per_in_ler
1998 where person_id = cv_person_id
1999 and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
2000 and ler_id = cv_group_ler_id
2001 and per_in_ler_stat_cd = 'STRTD';
2002 --
2003 l_mgr_per_in_ler_id number;
2004 --
2005 begin
2006 --
2007 -- Clone the data for current plan only.
2008 -- Group plan cloning should happen at the end of the loop.
2009 --
2010 hr_utility.set_location ('Entering '||l_proc,10);
2011 open c_popl_enrt_typ_cycl(p_lf_evt_ocrd_dt,
2012 p_business_group_id,
2013 p_effective_date,
2014 p_pl_id);
2015 --
2016 fetch c_popl_enrt_typ_cycl into l_ler_id;
2017 if c_popl_enrt_typ_cycl%notfound then
2018 --
2019 close c_popl_enrt_typ_cycl;
2020 fnd_message.set_name('BEN','BEN_91668_NO_FIND_POPL_ENRT');
2021 fnd_message.raise_error;
2022 end if;
2023 close c_popl_enrt_typ_cycl;
2024 hr_utility.set_location('p_lf_evt_ocrd_dt = ' || p_lf_evt_ocrd_dt, 1234);
2025 hr_utility.set_location('l_ler_id = ' || l_ler_id, 1234);
2026 hr_utility.set_location('p_pl_id = ' || p_pl_id, 1234);
2027 hr_utility.set_location('p_business_group_id = ' || p_business_group_id, 1234);
2028 --
2029 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
2030 (p_validate => false,
2031 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
2032 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2033 p_ptnl_ler_for_per_stat_cd => 'PROCD',
2034 p_ler_id => l_ler_id,
2035 p_person_id => p_person_id,
2036 -- p_ntfn_dt => l_ntfn_dt,
2037 -- p_unprocd_dt => l_unprocd_dt,
2038 -- p_dtctd_dt => l_dtctd_dt,
2039 p_business_group_id => p_business_group_id,
2040 p_object_version_number => l_object_version_number,
2041 p_effective_date => p_effective_date,
2042 p_program_application_id => fnd_global.prog_appl_id,
2043 p_program_id => fnd_global.conc_program_id,
2044 p_request_id => fnd_global.conc_request_id,
2045 p_program_update_date => sysdate);
2046 --
2047 ben_manage_cwb_life_events.get_group_plan_info(
2048 p_pl_id => p_pl_id,
2049 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2050 p_business_group_id => p_business_group_id);
2051 --
2052 hr_utility.set_location('group_pl_id ' ||
2053 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id ,20);
2054 if p_pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id
2055 then
2056 --
2057 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_per_in_ler_id := null;
2058 --
2059 ben_manage_cwb_life_events.get_cwb_manager_and_assignment
2060 (p_person_id => p_person_id,
2061 p_hrchy_to_use_cd => ben_manage_cwb_life_events.g_cache_group_plan_rec.hrchy_to_use_cd,
2062 p_pos_structure_version_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.pos_structure_version_id,
2063 p_effective_date => p_effective_date,
2064 p_manager_id => l_ws_mgr_id,
2065 p_assignment_id => l_assignment_id ) ;
2066 --
2067 --
2068 -- If manager is not processed previously then system should
2069 -- not attempt to clone for the manager later, so make the
2070 -- current person's ws_mgr_id as null
2071 --
2072 l_mgr_per_in_ler_id := null;
2073 open get_mgr_pil_id (l_ws_mgr_id,
2074 g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
2075 g_cache_group_plan_rec.group_ler_id);
2076 fetch get_mgr_pil_id into l_mgr_per_in_ler_id;
2077 close get_mgr_pil_id;
2078 --
2079 /* Following condition is causing data not cloned for manager
2080 so commenting. Need to identify why this was put. Currnetly commented
2081 as following case is not working.
2082 Case 1 :
2083
2084 Manager Name : SPP PRocess 16
2085 Hire Date : 30-Nov-2000
2086 Employee Name : SPP PRocess 17 (SPP Process 16 is the supervisor for
2087 this person)
2088 Hire Date : 30-Nov-2004
2089
2090 Plan Name : Single Run Bonus
2091 lv_evt_ocrd_dt : 01-Jan-2004
2092
2093 Ran the particiption process for this person with effective date :
2094 01-Dec-2004, SPP Process 16 was not processed as place-holder person.
2095 SPP PRocess 16 have to be processed as place holder
2096 ***************
2097 if l_mgr_per_in_ler_id is null then
2098 --
2099 l_ws_mgr_id := null;
2100 --
2101 end if;
2102 --
2103 */
2104 end if;
2105 --
2106 hr_utility.set_location('group_pl_id = ' ||
2107 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id, 30);
2108 --
2109 hr_utility.set_location('l_ws_mgr_id = ' || l_ws_mgr_id, 1234);
2110 hr_utility.set_location('l_assignment_id = ' || l_assignment_id, 1234);
2111 ben_Person_Life_Event_api.create_Person_Life_Event_perf
2112 (p_validate => false
2113 ,p_per_in_ler_id => l_curr_per_in_ler_id
2114 ,p_ler_id => l_ler_id
2115 ,p_person_id => p_person_id
2116 ,p_per_in_ler_stat_cd => 'STRTD'
2117 ,p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id
2118 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2119 ,p_business_group_id => p_business_group_id
2120 ,p_ntfn_dt => trunc(sysdate)
2121 ,p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id
2122 ,p_ws_mgr_id => l_ws_mgr_id
2123 ,p_assignment_id => l_assignment_id
2124 ,p_object_version_number => l_pil_object_version_number
2125 ,p_effective_date => p_effective_date
2126 ,p_program_application_id => fnd_global.prog_appl_id
2127 ,p_program_id => fnd_global.conc_program_id
2128 ,p_request_id => fnd_global.conc_request_id
2129 ,p_program_update_date => sysdate
2130 ,p_procd_dt => l_procd_dt
2131 ,p_strtd_dt => l_strtd_dt
2132 ,p_voidd_dt => l_voidd_dt);
2133 --
2134 -- Now clone the ben_cwb_person_rates, ben_cwb_group_rates.
2135 --
2136 if p_pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
2137 --
2138 -- Per in ler created is a group per in ler so populate other
2139 -- plan design tables.
2140 --
2141 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_per_in_ler_id := l_curr_per_in_ler_id;
2142 --
2143 hr_utility.set_location('Call ben_manage_cwb_life_events.popu_cwb_tables', 40);
2144 ben_manage_cwb_life_events.popu_cwb_tables(
2145 p_group_per_in_ler_id => l_curr_per_in_ler_id,
2146 p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id,
2147 p_group_lf_evt_ocrd_dt => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
2148 p_group_business_group_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_business_group_id,
2149 p_group_ler_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_ler_id,
2150 p_effective_date => p_effective_date,
2151 p_use_eff_dt_flag => 'Y');
2152 --
2153 -- For each of the group rates rows for a sample person
2154 -- copy data to current person.
2155 --
2156 for l_cpg_rec in c_cpg(ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id,
2157 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt)
2158 loop
2159 --
2160 -- create row in ben_cwb_person_groups
2161 --
2162 hr_utility.set_location('Creating missing ben_cwb_person_groups', 50);
2163 insert into ben_cwb_person_groups
2164 (group_per_in_ler_id,
2165 group_pl_id ,
2166 group_oipl_id ,
2167 lf_evt_ocrd_dt ,
2168 bdgt_pop_cd ,
2169 due_dt ,
2170 access_cd ,
2171 approval_cd ,
2172 approval_date ,
2173 approval_comments ,
2174 submit_cd ,
2175 submit_date ,
2176 submit_comments ,
2177 dist_bdgt_val ,
2178 ws_bdgt_val ,
2179 rsrv_val ,
2180 dist_bdgt_mn_val ,
2181 dist_bdgt_mx_val ,
2182 dist_bdgt_incr_val ,
2183 ws_bdgt_mn_val ,
2184 ws_bdgt_mx_val ,
2185 ws_bdgt_incr_val ,
2186 rsrv_mn_val ,
2187 rsrv_mx_val ,
2188 rsrv_incr_val ,
2189 dist_bdgt_iss_val ,
2190 ws_bdgt_iss_val ,
2191 dist_bdgt_iss_date ,
2192 ws_bdgt_iss_date ,
2193 ws_bdgt_val_last_upd_date ,
2194 dist_bdgt_val_last_upd_date ,
2195 rsrv_val_last_upd_date ,
2196 ws_bdgt_val_last_upd_by ,
2197 dist_bdgt_val_last_upd_by ,
2198 rsrv_val_last_upd_by ,
2199 object_version_number /* ,
2200 last_update_date ,
2201 last_updated_by ,
2202 last_update_login ,
2203 created_by ,
2204 creation_date */
2205 ) values (
2206 l_curr_per_in_ler_id,
2207 l_cpg_rec.group_pl_id ,
2208 nvl(l_cpg_rec.group_oipl_id, -1) ,
2209 l_cpg_rec.lf_evt_ocrd_dt ,
2210 null, -- bdgt_pop_cd
2211 null, -- l_cpg_rec.due_dt,
2212 g_cache_group_plan_rec.access_cd, -- l_cpg_rec.access_cd,
2213 null, -- approval_cd
2214 null, -- approval_date
2215 null, -- approval_comments
2216 'NS', -- submit_cd
2217 null, -- submit_date
2218 null, -- submit_comments
2219 null, -- l_copy_dist_bdgt_val,
2220 null, -- l_copy_ws_bdgt_val,
2221 null, -- l_copy_rsrv_val,
2222 null, -- l_copy_dist_bdgt_mn_val,
2223 null, -- l_copy_dist_bdgt_mx_val,
2224 null, -- l_copy_dist_bdgt_incr_val,
2225 null, -- l_copy_ws_bdgt_mn_val,
2226 null, -- l_copy_ws_bdgt_mx_val,
2227 null, -- l_copy_ws_bdgt_incr_val,
2228 null, -- l_copy_rsrv_mn_val,
2229 null, -- l_copy_rsrv_mx_val,
2230 null, -- l_copy_rsrv_incr_val,
2231 null, -- l_copy_dist_bdgt_iss_val,
2232 null, -- l_copy_ws_bdgt_iss_val,
2233 null, -- l_copy_dist_bdgt_iss_date,
2234 null, -- l_copy_ws_bdgt_iss_date,
2235 null, -- l_cpg_rec.ws_bdgt_val_last_upd_date ,
2236 null, -- l_cpg_rec.dist_bdgt_val_last_upd_date ,
2237 null, -- l_cpg_rec.rsrv_val_last_upd_date ,
2238 null, -- l_cpg_rec.ws_bdgt_val_last_upd_by ,
2239 null, -- l_cpg_rec.dist_bdgt_val_last_upd_by ,
2240 null, -- l_cpg_rec.rsrv_val_last_upd_by ,
2241 1-- , -- object_version_number
2242 /*
2243 l_cpg_rec.last_update_date ,
2244 l_cpg_rec.last_updated_by ,
2245 l_cpg_rec.last_update_login ,
2246 l_cpg_rec.created_by ,
2247 l_cpg_rec.creation_date
2248 */
2249 ) ;
2250 --
2251 end loop;
2252 --
2253 end if;
2254 --
2255 if p_clone_only_cpg = 'N' then
2256 --
2257 -- Copy relevant content from populate_cwb_rates
2258 --
2259 -- For each of the person rates rows for a sample person
2260 -- copy data to current person.
2261 --
2262 -- Populate
2263 -- BEN_CWB_PERSON_RATES
2264 -- Primary Key: PERSON_RATE_ID
2265 --
2266 hr_utility.set_location ('p_pl_id ' || p_pl_id,60);
2267 hr_utility.set_location ('g_cache_group_plan_rec.group_pl_id '||
2268 g_cache_group_plan_rec.group_pl_id ,60);
2269 hr_utility.set_location ('g_cache_group_plan_rec.plans_wthn_group_pl '
2270 || g_cache_group_plan_rec.plans_wthn_group_pl ,60);
2271 if ((p_pl_id = g_cache_group_plan_rec.group_pl_id and
2272 g_cache_group_plan_rec.plans_wthn_group_pl = 1
2273 ) OR
2274 (p_pl_id <> g_cache_group_plan_rec.group_pl_id)
2275 )
2276 then
2277 --
2278 if g_debug then
2279 hr_utility.set_location ('Person rate ' ,70);
2280 end if;
2281 --
2282 for l_cpr_rec in c_person_rates(
2283 c_group_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2284 ,c_group_pl_id => g_cache_group_plan_rec.group_pl_id
2285 ,c_pl_id => p_pl_id)
2286 loop
2287 --
2288 if l_assignment_id is null then
2289 --
2290 open c_asg;
2291 fetch c_asg into l_assignment_id;
2292 close c_asg;
2293 --
2294 end if;
2295 --
2296 insert into ben_cwb_person_rates
2297 (person_rate_id ,
2298 group_per_in_ler_id ,
2299 pl_id ,
2300 oipl_id ,
2301 group_pl_id ,
2302 group_oipl_id ,
2303 lf_evt_ocrd_dt ,
2304 person_id ,
2305 assignment_id ,
2306 elig_flag ,
2307 ws_val ,
2308 ws_mn_val ,
2309 ws_mx_val ,
2310 ws_incr_val ,
2311 elig_sal_val ,
2312 stat_sal_val ,
2313 oth_comp_val ,
2314 tot_comp_val ,
2315 misc1_val ,
2316 misc2_val ,
2317 misc3_val ,
2318 rec_val ,
2319 rec_mn_val ,
2320 rec_mx_val ,
2321 rec_incr_val ,
2322 ws_val_last_upd_date ,
2323 ws_val_last_upd_by ,
2324 pay_proposal_id ,
2325 element_entry_value_id ,
2326 inelig_rsn_cd ,
2327 elig_ovrid_dt ,
2328 elig_ovrid_person_id ,
2329 copy_dist_bdgt_val ,
2330 copy_ws_bdgt_val ,
2331 copy_rsrv_val ,
2332 copy_dist_bdgt_mn_val ,
2333 copy_dist_bdgt_mx_val ,
2334 copy_dist_bdgt_incr_val ,
2335 copy_ws_bdgt_mn_val ,
2336 copy_ws_bdgt_mx_val ,
2337 copy_ws_bdgt_incr_val ,
2338 copy_rsrv_mn_val ,
2339 copy_rsrv_mx_val ,
2340 copy_rsrv_incr_val ,
2341 copy_dist_bdgt_iss_val ,
2342 copy_ws_bdgt_iss_val ,
2343 copy_dist_bdgt_iss_date ,
2344 copy_ws_bdgt_iss_date ,
2345 COMP_POSTING_DATE ,
2346 WS_RT_START_DATE ,
2347 currency ,
2348 object_version_number /*,
2349 last_update_date ,
2350 last_updated_by ,
2351 last_update_login ,
2352 created_by ,
2353 creation_date */
2354 ) values
2355 (ben_cwb_person_rates_s.nextval,
2356 nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1),
2357 l_cpr_rec.pl_id,
2358 nvl(l_cpr_rec.oipl_id, -1),
2359 l_cpr_rec.group_pl_id,
2360 nvl(l_cpr_rec.group_oipl_id, -1), -- group_oipl_id ,
2361 l_cpr_rec.lf_evt_ocrd_dt,
2362 p_person_id,
2363 l_assignment_id ,
2364 'Y', -- l_elig_flag ,
2365 null, -- l_cpr_rec.ws_val ,
2366 null, -- l_cpr_rec.ws_mn_val ,
2367 null, -- l_cpr_rec.ws_mx_val ,
2368 null, -- l_cpr_rec.ws_incr_val ,
2369 null, -- l_cpr_rec.elig_sal_val ,
2370 null, -- l_cpr_rec.stat_sal_val ,
2371 null, -- l_cpr_rec.oth_comp_val ,
2372 null, -- l_cpr_rec.tot_comp_val ,
2373 null, -- l_cpr_rec.misc1_val ,
2374 null, -- l_cpr_rec.misc2_val ,
2375 null, -- l_cpr_rec.misc3_val ,
2376 null, -- l_cpr_rec.rec_val ,
2377 null, -- l_cpr_rec.rec_mn_val ,
2378 null, -- l_cpr_rec.rec_mx_val ,
2379 null, -- l_cpr_rec.rec_incr_val ,
2380 null, -- l_cpr_rec.ws_val_last_upd_date ,
2381 null, -- l_cpr_rec.ws_val_last_upd_by ,
2382 null, -- g_cwb_person_rates_rec.pay_proposal_id ,
2383 null, -- g_cwb_person_rates_rec.element_entry_value_id ,
2384 null, -- l_inelig_rsn_cd ,
2385 null, -- l_cpr_rec.elig_ovrid_dt ,
2386 null, -- l_cpr_rec.elig_ovrid_person_id ,
2387 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_val ,
2388 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_val ,
2389 null, -- g_cwb_person_rates_rec.copy_rsrv_val ,
2390 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_mn_val ,
2391 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_mx_val ,
2392 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_incr_val ,
2393 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_mn_val ,
2394 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_mx_val ,
2395 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_incr_val ,
2396 null, -- g_cwb_person_rates_rec.copy_rsrv_mn_val ,
2397 null, -- g_cwb_person_rates_rec.copy_rsrv_mx_val ,
2398 null, -- g_cwb_person_rates_rec.copy_rsrv_incr_val ,
2399 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_iss_val ,
2400 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_iss_val ,
2401 null, -- g_cwb_person_rates_rec.copy_dist_bdgt_iss_date ,
2402 null, -- g_cwb_person_rates_rec.copy_ws_bdgt_iss_date ,
2403 null, -- l_cpr_rec.COMP_POSTING_DATE,
2404 null, -- l_cpr_rec.WS_RT_START_DATE,
2405 l_cpr_rec.currency, -- Bug 5104388
2406 1 -- object_version_number ,
2407 /* l_cpr_rec.last_update_date ,
2408 l_cpr_rec.last_updated_by ,
2409 l_cpr_rec.last_update_login ,
2410 l_cpr_rec.created_by ,
2411 l_cpr_rec.creation_date
2412 */
2413 );
2414 --
2415 end loop;
2416 end if;
2417 end if;
2418 --
2419 end p_single_per_clone_all_data;
2420 --
2421 procedure global_process
2422 (errbuf out nocopy varchar2
2423 ,retcode out nocopy number
2424 ,p_benefit_action_id in number default null
2425 ,p_effective_date in varchar2
2426 ,p_mode in varchar2 default 'W'
2427 ,p_derivable_factors in varchar2 default 'ASC'
2428 ,p_validate in varchar2 default 'N'
2429 ,p_person_id in number default null
2430 ,p_pgm_id in number default null
2431 ,p_business_group_id in number
2432 ,p_pl_id in number default null
2433 ,p_popl_enrt_typ_cycl_id in number default null
2434 ,p_lf_evt_ocrd_dt in varchar2 default null
2435 ,p_person_type_id in number default null
2436 ,p_no_programs in varchar2 default 'N'
2437 ,p_no_plans in varchar2 default 'N'
2438 ,p_comp_selection_rule_id in number default null
2439 ,p_person_selection_rule_id in number default null
2440 ,p_ler_id in number default null
2441 ,p_organization_id in number default null
2442 ,p_benfts_grp_id in number default null
2443 ,p_location_id in number default null
2444 ,p_pstl_zip_rng_id in number default null
2445 ,p_rptg_grp_id in number default null
2446 ,p_pl_typ_id in number default null
2447 ,p_opt_id in number default null
2448 ,p_eligy_prfl_id in number default null
2449 ,p_vrbl_rt_prfl_id in number default null
2450 ,p_legal_entity_id in number default null
2451 ,p_payroll_id in number default null
2452 ,p_commit_data in varchar2 default 'Y'
2453 ,p_audit_log_flag in varchar2 default 'N'
2454 ,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
2455 ,p_cbr_tmprl_evt_flag in varchar2 default 'N'
2456 ,p_trace_plans_flag in varchar2 default 'N'
2457 ,p_online_call_flag in varchar2 default 'N'
2458 ,p_clone_all_data_flag in varchar2 default 'N'
2459 ,p_cwb_person_type in varchar2 default NULL
2460 ,p_run_rollup_only in varchar2 default 'N' /* Bug 4875181 */
2461 ) is
2462 --
2463 l_package varchar2(80) := g_package||'.global_process';
2464 --
2465 l_retcode number;
2466 l_errbuf varchar2(1000);
2467 l_encoded_message varchar2(2000);
2468 l_app_short_name varchar2(2000);
2469 l_message_name varchar2(2000);
2470 --
2471 l_prog_count number;
2472 l_plan_count number;
2473 l_oipl_count number;
2474 l_person_count number;
2475 l_plan_nip_count number;
2476 l_oipl_nip_count number;
2477
2478 l_request_id number;
2479 l_slave_errored boolean ;
2480 l_lf_evt_ocrd_dt date := fnd_date.canonical_to_date(p_lf_evt_ocrd_dt);
2481 l_effective_date date := fnd_date.canonical_to_date(p_effective_date);
2482 --Build Hierarchy
2483 l_pl_id number := p_pl_id ;
2484 l_business_group_id number;
2485 l_ler_id number;
2486 L_USE_EFF_DT_FLAG varchar2(1) := 'N';
2487 --
2488 -- Bug 3482033 fixes
2489 --
2490 cursor c_pln(p_pl_id number) is
2491 select '1' pln_order,
2492 pln.pl_id,
2493 pln.business_group_id,
2494 pln.name
2495 from ben_pl_f pln
2496 where pln.group_pl_id = p_pl_id
2497 and l_lf_evt_ocrd_dt between pln.effective_start_date
2498 and pln.effective_end_date
2499 and pln.pl_id =pln.group_pl_id
2500 and pln.pl_stat_cd = 'A'
2501 union
2502 select '2' pln_order,
2503 pln.pl_id,
2504 pln.business_group_id,
2505 pln.name
2506 from ben_pl_f pln
2507 where pln.group_pl_id = p_pl_id
2508 and l_lf_evt_ocrd_dt between pln.effective_start_date
2509 and pln.effective_end_date
2510 and pln.pl_id <> pln.group_pl_id
2511 and pln.pl_stat_cd = 'A'
2512 order by pln_order ;
2513 --
2514 cursor c_benfts_grp(cv_benfts_grp_id number, cv_business_group_id number) is
2515 select bnb.benfts_grp_id
2516 from ben_benfts_grp bnb,
2517 ben_benfts_grp bnb1
2518 where bnb.business_group_id = cv_business_group_id
2519 and bnb.name = bnb1.name
2520 and bnb1.benfts_grp_id = cv_benfts_grp_id;
2521 --
2522 l_benfts_grp_id number;
2523 --
2524 cursor c_person_selection_rl(cv_formula_id number, cv_business_group_id number,
2525 cv_effective_date date ) is
2526 select fff.formula_id
2527 from ff_formulas_f fff,
2528 ff_formulas_f fff1
2529 where fff.business_group_id = cv_business_group_id
2530 and cv_effective_date between fff.effective_start_date
2531 and fff.effective_end_date
2532 and fff.formula_name = fff1.formula_name
2533 and cv_effective_date between fff1.effective_start_date
2534 and fff1.effective_end_date
2535 and fff1.formula_id = cv_formula_id;
2536 --
2537 cursor get_per_info (p_person_id number, p_effective_date date) is
2538 Select ppf.person_id person_id
2539 ,paf.assignment_id assignment_id
2540 , ppf.original_date_of_hire original_start_date
2541 , ppf.start_date latest_start_date
2542 , ppp.date_start latest_placement_start_date
2543 , ppp.projected_termination_date
2544 /* Changed for bug#7393142
2545 , DECODE(Ppf.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,
2546 DECODE(Ppf.CURRENT_NPW_FLAG,
2547 'Y',PPP.DATE_START,
2548 NULL)
2549 ) Hire_Date
2550 */
2551 ,(CASE WHEN ppf.employee_number IS NOT NULL THEN
2552 pps.date_start
2553 WHEN ppf.npw_number IS NOT NULL THEN
2554 ppp.date_start
2555 END) HIRE_DATE
2556 ,pps.actual_termination_date actual_termination_date
2557 from per_all_people_f ppf
2558 ,per_all_assignments_f paf
2559 ,PER_PERIODS_OF_PLACEMENT PPP
2560 ,PER_PERIODS_OF_SERVICE PPS
2561 where ppf.person_id = paf.person_id
2562 and paf. assignment_type in ('E','B') -- Need to consider Ex-Employee too
2563 and p_effective_date between
2564 ppf.effective_start_date and ppf.effective_end_date
2565 and p_effective_date between
2566 paf.effective_start_date and paf.effective_end_date
2567 and ppp.person_id (+) = ppf.person_id
2568 and ((ppf.employee_number is null)
2569 or
2570 (Ppf.EMPLOYEE_NUMBER IS NOT NULL
2571 AND PPS.DATE_START =
2572 (SELECT MAX(PPS1.DATE_START)
2573 FROM PER_PERIODS_OF_SERVICE PPS1
2574 WHERE PPS1.PERSON_ID = Ppf.PERSON_ID
2575 AND PPS1.DATE_START <= Ppf.EFFECTIVE_END_DATE)
2576 )
2577 )
2578 AND ((Ppf.NPW_NUMBER IS NULL)
2579 OR
2580 (Ppf.NPW_NUMBER IS NOT NULL AND
2581 PPP.DATE_START =
2582 (SELECT MAX(PPP1.DATE_START)
2583 FROM PER_PERIODS_OF_PLACEMENT PPP1
2584 WHERE PPP1.PERSON_ID = Ppf.PERSON_ID
2585 AND PPP1.DATE_START <= Ppf.EFFECTIVE_END_DATE
2586 )
2587 )
2588 )
2589 AND PPS.PERSON_ID (+) = Ppf.PERSON_ID
2590 and ppf.person_id = p_person_id;
2591 --
2592 cursor c_get_ler(cv_business_group_id number,
2593 cv_lf_evt_ocrd_dt in date,
2594 cv_effective_date in date,
2595 cv_pl_id in number) is
2596 select ler.ler_id
2597 from ben_popl_enrt_typ_cycl_f pet,
2598 ben_enrt_perd enp,
2599 ben_ler_f ler
2600 where enp.business_group_id = cv_business_group_id
2601 and enp.asnd_lf_evt_dt = cv_lf_evt_ocrd_dt
2602 and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
2603 and pet.business_group_id = enp.business_group_id
2604 and cv_effective_date
2605 between pet.effective_start_date
2606 and pet.effective_end_date
2607 and ler.typ_cd = 'COMP'
2608 and ler.business_group_id = pet.business_group_id
2609 and cv_effective_date
2610 between ler.effective_start_date
2611 and ler.effective_end_date
2612 and ler.ler_id = enp.ler_id
2613 and pet.pl_id = cv_pl_id;
2614 --
2615 cursor c_get_pil(cv_person_id number,
2616 cv_pl_id number,
2617 cv_lf_evt_ocrd_dt date,
2618 cv_ler_id number,
2619 cv_business_group_id number) is
2620 select pil.per_in_ler_id
2621 from ben_per_in_ler pil
2622 where pil.group_pl_id = cv_pl_id
2623 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
2624 and pil.ler_id = cv_ler_id
2625 and pil.person_id = cv_person_id
2626 and pil.business_group_id = cv_business_group_id
2627 and pil.per_in_ler_stat_cd = 'STRTD';
2628 --
2629 -- 5232223
2630 cursor c_elpros_attchd_grp(cv_pl_id number,
2631 cv_eff_dt date) is
2632 select null
2633 from BEN_PRTN_ELIG_F epa,
2634 BEN_PRTN_ELIG_PRFL_f cep
2635 where epa.pl_id = cv_pl_id
2636 and epa.prtn_elig_id = cep.prtn_elig_id
2637 and cv_eff_dt between epa.effective_start_date
2638 and epa.effective_end_date
2639 and cv_eff_dt between cep.effective_start_date
2640 and cep.effective_end_date;
2641 -- 5232223
2642 --
2643 cursor c_elpros_attchd_loc(cv_pl_id number,
2644 cv_eff_dt date) is
2645 select null
2646 from BEN_PRTN_ELIG_F epa,
2647 BEN_PRTN_ELIG_PRFL_f cep,
2648 ben_pl_f pln
2649 where pln.group_pl_id = cv_pl_id
2650 and pln.group_pl_id <> pln.pl_id
2651 and epa.pl_id = pln.pl_id
2652 and epa.prtn_elig_id = cep.prtn_elig_id
2653 and cv_eff_dt between epa.effective_start_date
2654 and epa.effective_end_date
2655 and cv_eff_dt between cep.effective_start_date
2656 and cep.effective_end_date;
2657 -- 5232223
2658 --
2659 l_local_ler_id number;
2660 l_local_pil_id number;
2661 l_per_rec get_per_info%rowtype;
2662 l_person_selection_rule_id number;
2663 l_audit_log_flag varchar2(1) ;
2664 l_supress_report varchar2(1) ;
2665 l_run_rollup_only varchar2(30);
2666 l_dummy number; -- 5232223
2667 l_elpro_attcd_grp_pln boolean := false; -- 5232223
2668 l_elpro_attcd_act_pln boolean := false; -- 5232223
2669 --
2670 begin
2671 --
2672 g_debug := hr_utility.debug_enabled;
2673 -- decide the sudit log and supress report flag
2674 l_audit_log_flag := substr(P_audit_log_flag,1,1) ;
2675 l_supress_report := nvl(substr(P_audit_log_flag,2,1),'Y') ;
2676 l_run_rollup_only := nvl(p_run_rollup_only, 'N');
2677
2678 --- Value Log report
2679 --- N N Y
2680 --- Y Y Y
2681 --- NN N N
2682 --- YN Y N
2683 --
2684 hr_utility.set_location ('Entering '||l_package,10);
2685 hr_utility.set_location ('audit log '||l_audit_log_flag ,10);
2686 hr_utility.set_location ('supress log '|| l_supress_report ,10);
2687 hr_utility.set_location ('process rollup ' || p_run_rollup_only, 10);
2688 --
2689 if p_online_call_flag = 'N' then
2690 --
2691 fnd_file.put_line(which => fnd_file.log,
2692 buff => 'GLOBAL COMPENSATION PROCESS - SUMMARY lOG');
2693 fnd_file.put_line(which => fnd_file.log,
2694 buff => '------------------------------------------');
2695 --
2696 end if;
2697 --
2698 -- Populate ben_cwb_plan_design, it should be committed as 9999 autonmous trxn.
2699 --
2700 -- refresh plan design will be smart enough to refresh or not.
2701 --
2702 BEN_CWB_PL_DSGN_PKG.refresh_pl_dsgn(p_group_pl_id => l_pl_id
2703 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2704 ,p_effective_date => null);
2705 --
2706 if nvl(p_online_call_flag, 'N') = 'N' then
2707 commit;
2708 end if;
2709 --
2710 -- Bug 4875181 - Submit individual concurrent requests only if Run Rollup Process Only = No
2711 --
2712 if l_run_rollup_only = 'N'
2713 then
2714 --
2715 hr_utility.set_location('Entire process', 8888);
2716 --
2717 for l_count in c_pln(p_pl_id) loop
2718 --
2719 if p_benfts_grp_id is not null then
2720 --
2721 open c_benfts_grp(p_benfts_grp_id, l_count.business_group_id);
2722 fetch c_benfts_grp into l_benfts_grp_id;
2723 close c_benfts_grp;
2724 hr_utility.set_location('l_benfts_grp_id = ' || l_benfts_grp_id, 1234);
2725 --
2726 end if;
2727 --
2728 if p_person_selection_rule_id is not null then
2729 --
2730 open c_person_selection_rl(p_person_selection_rule_id,l_count.business_group_id,l_lf_evt_ocrd_dt);
2731 fetch c_person_selection_rl into l_person_selection_rule_id ;
2732 close c_person_selection_rl ;
2733 hr_utility.set_location('l_person_selection_rule_id = '||l_person_selection_rule_id,4321);
2734 --
2735 end if;
2736 --
2737 if p_trace_plans_flag = 'N' then
2738 --
2739 l_request_id := fnd_request.submit_request
2740 (application => 'BEN',
2741 program => 'BENCOMOD',
2742 description => NULL,
2743 sub_request => FALSE,
2744 argument1 => p_benefit_action_id,
2745 argument2 => p_effective_date,
2746 argument3 => p_mode,
2747 argument4 => p_derivable_factors,
2748 argument5 => p_validate,
2749 argument6 => p_person_id,
2750 argument7 => p_pgm_id,
2751 argument8 => l_count.business_group_id,
2752 argument9 => l_count.pl_id,
2753 argument10 => null,
2754 argument11 => p_lf_evt_ocrd_dt,
2755 argument12 => p_person_type_id,
2756 argument13 => p_no_programs,
2757 argument14 => p_no_plans,
2758 argument15 => p_comp_selection_rule_id,
2759 argument16 => l_person_selection_rule_id,
2760 argument17 => p_ler_id,
2761 argument18 => p_organization_id,
2762 argument19 => l_benfts_grp_id,
2763 argument20 => p_location_id,
2764 argument21 => p_pstl_zip_rng_id,
2765 argument22 => p_rptg_grp_id,
2766 argument23 => p_pl_typ_id,
2767 argument24 => p_opt_id,
2768 argument25 => p_eligy_prfl_id,
2769 argument26 => p_vrbl_rt_prfl_id,
2770 argument27 => p_legal_entity_id,
2771 argument28 => p_payroll_id,
2772 argument29 => p_commit_data,
2773 argument30 => l_audit_log_flag,
2774 argument31 => p_lmt_prpnip_by_org_flag,
2775 argument32 => p_cbr_tmprl_evt_flag,
2776 argument33 => p_cwb_person_type
2777 );
2778 --
2779 commit ;
2780 --
2781 ben_manage_cwb_life_events.g_num_cwb_processes :=
2782 ben_manage_cwb_life_events.g_num_cwb_processes + 1;
2783 ben_manage_cwb_life_events.g_cwb_processes_rec(g_num_cwb_processes) := l_request_id;
2784 --
2785 --
2786 fnd_file.put_line(which => fnd_file.log,
2787 buff => 'Submitted the concurrent request id '||l_request_id||
2788 ' for the plan :'||substr(l_count.name,1,100) );
2789 --
2790 else
2791 if p_online_call_flag = 'N' then
2792 --
2793 ben_manage_life_events.cwb_process
2794 (Errbuf =>l_errbuf,
2795 retcode =>l_retcode,
2796 p_benefit_action_id =>p_benefit_action_id ,
2797 p_effective_date =>p_effective_date,
2798 p_mode =>p_mode,
2799 p_derivable_factors =>p_derivable_factors,
2800 p_validate =>p_validate,
2801 p_person_id =>p_person_id,
2802 p_person_type_id =>p_person_type_id,
2803 p_pgm_id =>p_pgm_id,
2804 p_business_group_id =>l_count.business_group_id,
2805 p_pl_id =>l_count.pl_id,
2806 p_popl_enrt_typ_cycl_id =>p_popl_enrt_typ_cycl_id,
2807 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
2808 p_no_programs =>p_no_programs,
2809 p_no_plans =>p_no_plans,
2810 p_comp_selection_rule_id =>p_comp_selection_rule_id,
2811 p_person_selection_rule_id =>p_person_selection_rule_id,
2812 p_ler_id =>p_ler_id,
2813 p_organization_id =>p_organization_id,
2814 p_benfts_grp_id =>l_benfts_grp_id,
2815 p_location_id =>p_location_id,
2816 p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
2817 p_rptg_grp_id =>p_rptg_grp_id,
2818 p_pl_typ_id =>p_pl_typ_id,
2819 p_opt_id =>p_opt_id,
2820 p_eligy_prfl_id =>p_eligy_prfl_id,
2821 p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
2822 p_legal_entity_id =>p_legal_entity_id,
2823 p_payroll_id =>p_payroll_id,
2824 p_commit_data =>p_commit_data,
2825 p_audit_log_flag =>l_audit_log_flag,
2826 p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
2827 p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag,
2828 p_cwb_person_type => p_cwb_person_type);
2829 --
2830 elsif p_online_call_flag = 'Y' then
2831 --
2832 -- In online mode only process the plans within the business group.
2833 --
2834 if l_count.business_group_id = p_business_group_id then
2835 --
2836 -- If person exists before lf_evt_ocrd_dt then
2837 -- To check this can we make use of ORIGINAL_DATE_OF_HIRE, ADJUSTED_SVC_DATE
2838 -- OR see the cursor get_per_info above.
2839 -- call the following procedure, otherwise
2840 -- * Person id must be passed,
2841 -- * Single person run should not refresh the plan design.
2842 -- * Clone all the data and clear the rates.
2843 -- * Check for started pil exists, data to clone exists otherwise do not run person.
2844 -- * min(per.esd) should be used for all the data determination like manager etc
2845 -- * Person should be marked as cloned
2846 -- * If manager is not found what to do?
2847 -- * If manager and Employee both joined after the lf_evt_ocrd_dt?
2848 -- * Check all the places in post processes where lf_evt_ocrd_dt is used, it
2849 -- * should overloaded with persons min(ESD).
2850 -- *
2851 if l_per_rec.hire_date is null then
2852 --
2853 open get_per_info (p_person_id , l_effective_date);
2854 fetch get_per_info into l_per_rec;
2855 hr_utility.set_location('hire dt = ' || l_per_rec.hire_date, 999);
2856 hr_utility.set_location('leod dt = ' || l_lf_evt_ocrd_dt, 999);
2857 close get_per_info;
2858 --
2859 end if;
2860 --
2861 if l_per_rec.hire_date > l_lf_evt_ocrd_dt or
2862 -- l_per_rec.actual_termination_date < l_lf_evt_ocrd_dt or -- Changed for bug#7393142
2863 p_clone_all_data_flag = 'Y'
2864 then
2865 --
2866 hr_utility.set_location('Before call to p_single_per_clone_all_data', 999);
2867 -- Clone the data for current plan only.
2868 -- Group plan cloning should happen at the end of the loop.
2869 --
2870 l_USE_EFF_DT_FLAG := 'Y';
2871 p_single_per_clone_all_data(
2872 p_person_id
2873 ,l_count.business_group_id
2874 ,l_ler_id
2875 ,l_effective_date
2876 ,l_lf_evt_ocrd_dt
2877 ,l_count.pl_id
2878 );
2879 --
2880 else
2881 --
2882 -- If person is already processed for a plan do not process him.
2883 -- just process the remaining plans within this bg.
2884 --
2885 l_local_ler_id := null;
2886 open c_get_ler(l_count.business_group_id,
2887 l_lf_evt_ocrd_dt ,
2888 l_effective_date ,
2889 l_count.pl_id);
2890 fetch c_get_ler into l_local_ler_id;
2891 close c_get_ler;
2892 --
2893 -- Find the per in ler id for local plan.
2894 -- If not found then run the process.
2895 --
2896 l_local_pil_id := null;
2897 open c_get_pil(p_person_id ,
2898 l_count.pl_id ,
2899 l_lf_evt_ocrd_dt ,
2900 l_local_ler_id ,
2901 l_count.business_group_id );
2902 fetch c_get_pil into l_local_pil_id;
2903 close c_get_pil;
2904 --
2905 if l_local_pil_id is null then
2906 --
2907 ben_on_line_lf_evt.p_manage_life_events
2908 (p_person_id => p_person_id
2909 ,p_effective_date => l_effective_date
2910 ,p_business_group_id => l_count.business_group_id
2911 ,p_pgm_id => null
2912 ,p_pl_id => l_count.pl_id
2913 ,p_mode => p_mode
2914 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2915 ,p_prog_count => l_prog_count
2916 ,p_plan_count => l_plan_count
2917 ,p_oipl_count => l_oipl_count
2918 ,p_person_count => l_person_count
2919 ,p_plan_nip_count => l_plan_nip_count
2920 ,p_oipl_nip_count => l_oipl_nip_count
2921 ,p_ler_id => l_ler_id
2922 ,p_errbuf => l_errbuf
2923 ,p_retcode => l_retcode
2924 );
2925 --
2926 end if;
2927 --
2928 end if;
2929 --
2930 end if;
2931 --
2932 end if;
2933 --
2934 end if ;
2935 --
2936 end loop ; -- for each plan linked to group plan.
2937 --
2938 if p_trace_plans_flag = 'N' and nvl(p_online_call_flag, 'N') = 'N' then
2939 --
2940 check_all_slaves_finished
2941 (p_benefit_action_id => p_benefit_action_id
2942 ,p_business_group_id => p_business_group_id
2943 ,p_slave_errored => l_slave_errored
2944 );
2945 --
2946 end if;
2947 --
2948 end if; /* IF l_run_rollup_only = 'N' */
2949 --
2950 savepoint cwb_global_process; /* Bug 4875181 */
2951 --
2952 begin
2953 --
2954 -- Populate group plan cache.
2955 --
2956 g_error_log_rec.calling_proc := 'get_group_plan_info';
2957 g_error_log_rec.step_number := 1;
2958 --
2959 ben_manage_cwb_life_events.get_group_plan_info(
2960 p_group_pl_id => l_pl_id
2961 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2962 ,p_pl_id => null
2963 );
2964 --
2965 --
2966 if g_cache_group_plan_rec.group_ler_id is not null then
2967 --
2968 --Populate Missing Group Plan Information
2969 --
2970 fnd_file.put_line(which => fnd_file.log,
2971 buff => 'Started : Populating group plan data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
2972 hr_utility.set_location('global_process - before call to cwb_group_pil', 1234);
2973 if l_use_eff_dt_flag = 'N' then
2974 --
2975 g_error_log_rec.calling_proc := 'popu_cwb_group_pil_data';
2976 g_error_log_rec.step_number := 2;
2977 --
2978 ben_manage_cwb_life_events.popu_cwb_group_pil_data (
2979 p_group_per_in_ler_id => -9999
2980 ,p_group_pl_id => g_cache_group_plan_rec.group_pl_id
2981 ,p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt
2982 ,p_group_business_group_id => g_cache_group_plan_rec.group_business_group_id
2983 ,p_group_ler_id => g_cache_group_plan_rec.group_ler_id );
2984 else
2985 --
2986 -- In case of single person run and clone all the data
2987 -- clone ben_cwb_person_group data out side of
2988 -- popu_cwb_group_pil_data for persons outside the group plan's
2989 -- business group. This is to avoid cloning of data for managers.
2990 --
2991 if ben_manage_cwb_life_events.g_cache_group_plan_rec.group_per_in_ler_id is null
2992 then
2993 --
2994 g_error_log_rec.calling_proc := 'p_single_per_clone_all_data';
2995 g_error_log_rec.step_number := 3;
2996 p_single_per_clone_all_data(
2997 p_person_id
2998 ,g_cache_group_plan_rec.group_business_group_id
2999 ,g_cache_group_plan_rec.group_ler_id
3000 ,l_effective_date
3001 ,g_cache_group_plan_rec.group_lf_evt_ocrd_dt
3002 ,g_cache_group_plan_rec.group_pl_id
3003 ,'Y' -- for p_clone_only_cpg
3004 );
3005 --
3006 end if;
3007 --
3008 g_error_log_rec.calling_proc := 'popu_cwb_group_pil_data';
3009 g_error_log_rec.step_number := 4;
3010 --
3011 ben_manage_cwb_life_events.popu_cwb_group_pil_data (
3012 p_group_per_in_ler_id => -9999
3013 ,p_group_pl_id => g_cache_group_plan_rec.group_pl_id
3014 ,p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt
3015 ,p_group_business_group_id => g_cache_group_plan_rec.group_business_group_id
3016 ,p_group_ler_id => g_cache_group_plan_rec.group_ler_id
3017 ,p_use_eff_dt_flag => l_use_eff_dt_flag
3018 ,p_effective_date => l_effective_date
3019 );
3020 end if;
3021 --
3022 fnd_file.put_line(which => fnd_file.log,
3023 buff => 'Completed : Populating group plan data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
3024 --
3025 --Populate Group Hierarchy table data
3026 --
3027 g_error_log_rec.calling_proc := 'popu_group_pil_heir';
3028 g_error_log_rec.step_number := 4;
3029 --
3030 ben_manage_cwb_life_events.popu_group_pil_heir(
3031 p_group_pl_id => g_cache_group_plan_rec.group_pl_id
3032 ,p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt
3033 ,p_group_business_group_id => g_cache_group_plan_rec.group_business_group_id
3034 ,p_group_ler_id => g_cache_group_plan_rec.group_ler_id ) ;
3035 --
3036 fnd_file.put_line(which => fnd_file.log,
3037 -- buff => 'Completed : Populating heirarchy data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
3038 buff => 'Completed : Populating hierarchy data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
3039 -- changed for bug: 5134561
3040 --
3041 -- Add del_all_cwb_pils here to handle if the elpros are not attached
3042 -- to group plan but attached to actual plan and track inelig flag
3043 -- on group plan set to N.
3044 --
3045 -- 5232223
3046 Open c_elpros_attchd_grp(g_cache_group_plan_rec.group_pl_id,
3047 g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3048 fetch c_elpros_attchd_grp into l_dummy;
3049 if c_elpros_attchd_grp%FOUND then
3050 l_elpro_attcd_grp_pln := TRUE;
3051 else
3052 l_elpro_attcd_grp_pln := FALSE;
3053 end if;
3054 Close c_elpros_attchd_grp;
3055 --
3056 Open c_elpros_attchd_loc(g_cache_group_plan_rec.group_pl_id,
3057 g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3058 fetch c_elpros_attchd_loc into l_dummy;
3059 if c_elpros_attchd_loc%FOUND then
3060 l_elpro_attcd_act_pln := TRUE;
3061 else
3062 l_elpro_attcd_act_pln := FALSE;
3063 end if;
3064 close c_elpros_attchd_loc;
3065 --
3066 hr_utility.set_location('trk_inelg ->'|| g_cache_group_plan_rec.trk_inelig_per_flag,99);
3067 --
3068 if NOT l_elpro_attcd_grp_pln
3069 and l_elpro_attcd_act_pln
3070 and g_cache_group_plan_rec.trk_inelig_per_flag = 'N' then
3071 --
3072 del_all_cwb_pils(
3073 p_group_pl_id => g_cache_group_plan_rec.group_pl_id,
3074 p_group_ler_id => g_cache_group_plan_rec.group_ler_id,
3075 p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3076 --
3077 end if;
3078 -- 5232223
3079
3080 if g_cache_group_plan_rec.uses_bdgt_flag = 'Y' then
3081 --
3082 g_error_log_rec.calling_proc := 'auto_allocate_budgets';
3083 g_error_log_rec.step_number := 5;
3084 --
3085 ben_manage_cwb_life_events.auto_allocate_budgets (
3086 p_group_pl_id => g_cache_group_plan_rec.group_pl_id
3087 ,p_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3088 --
3089 end if;
3090 --
3091 -- Populate ben_cwb_xchg table.
3092 --
3093 g_error_log_rec.calling_proc := 'insert_into_ben_cwb_xchg';
3094 g_error_log_rec.step_number := 6;
3095 --
3096 ben_cwb_xchg_pkg.insert_into_ben_cwb_xchg(
3097 p_group_pl_id => g_cache_group_plan_rec.group_pl_id
3098 ,p_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt
3099 ,p_effective_date => null
3100 ,p_refresh_always => 'N');
3101 --
3102 -- Populate ben_cwb_summary table.
3103 --
3104 g_error_log_rec.calling_proc := 'refresh_summary_persons';
3105 g_error_log_rec.step_number := 7;
3106 --
3107 BEN_CWB_SUMMARY_PKG.refresh_summary_persons(
3108 p_group_pl_id => g_cache_group_plan_rec.group_pl_id
3109 ,p_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3110 --
3111 fnd_file.put_line(which => fnd_file.log,
3112 -- buff => 'Completed : Populating heirarchy data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
3113 buff => 'Completed : Populating hierarchy data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
3114 -- changed for bug: 5134561
3115 --
3116 end if;
3117 --
3118 --
3119 -- RECUR
3120 --
3121 -- Now try to log the information about the recursive heirarchy.
3122 --
3123 declare
3124 l_hrchy_counter number := 0;
3125 l_loop_counter number;
3126 begin
3127 l_hrchy_counter := nvl(g_hrchy_tbl.LAST, 0);
3128 if l_hrchy_counter > 0 then
3129 --
3130 fnd_file.put_line(which => fnd_file.log,
3131 buff => 'A recursive relationship was detected in the manager hierarchy. '||
3132 'You can correct the hierarchy data by updating the worksheet manager '||
3133 'on the employee administration page for each person ID listed. '||
3134 'You should also fix the problem at its HR source and refresh the '||
3135 'summary using Refresh Job. The list of person ID''s are reported below. ');
3136 -- Text for BEN_94537_REC_REPORTING changed, hence this text is also changed.
3137 /*
3138 'Employee hierarchy has recursive reporting. You can correct the hierarchy data '||
3139 'by using employee reassignment or correct the HR data. Then rerun the Single '||
3140 'Person process and refresh the summary using Refresh Job. '||
3141 'List of person id''s reported below.');
3142 */
3143 l_loop_counter := least(l_hrchy_counter, 100);
3144 if l_hrchy_counter > 100 then
3145 --
3146 fnd_file.put_line(which => fnd_file.log,
3147 buff => 'Also query the ben_trasactions table to see complete list.');
3148 --
3149 for i in 1..l_hrchy_counter loop
3150
3151 INSERT INTO ben_transaction
3152 (transaction_id
3153 ,transaction_type
3154 ,attribute1
3155 ,ATTRIBUTE40)
3156 VALUES (ben_transaction_s.NEXTVAL
3157 ,'CWBRECURHIER'
3158 ,to_char(fnd_global.conc_request_id)
3159 ,g_hrchy_tbl(i).hrchy_cat_string);
3160
3161 end loop;
3162 end if;
3163 --
3164 for i in 1..l_loop_counter loop
3165 fnd_file.put_line(which => fnd_file.log,
3166 buff => ' ' ||g_hrchy_tbl(i).hrchy_cat_string);
3167 end loop;
3168 --
3169 end if;
3170 exception when others then
3171 fnd_file.put_line(which => fnd_file.log,
3172 buff =>'A recursive relationship was detected in the manager hierarchy. '||
3173 'You can correct the hierarchy data by updating the worksheet manager '||
3174 'on the employee administration page for each person ID listed. '||
3175 'You should also fix the problem at its HR source and refresh the '||
3176 'summary using Refresh Job. The list of person ID''s are reported below. ');
3177 -- Text for BEN_94537_REC_REPORTING changed, hence this text is also changed.
3178 /* 'Employee hierarchy has recursive reporting. You can correct the hierarchy data '||
3179 'by using employee reassignment or correct the HR data. Then rerun the Single '||
3180 'Person process and refresh the summary using Refresh Job. '||
3181 'List of person id''s reported below.');
3182 */
3183
3184 null;
3185 end;
3186 -- RECUR
3187 --
3188 exception when others then
3189 --
3190 --
3191 -- Spawn the reports at the end.
3192 --
3193 if nvl(ben_manage_cwb_life_events.g_num_cwb_processes, 0) <> 0
3194 then
3195 --
3196 fnd_file.put_line(which => fnd_file.log,
3197 buff => 'An error occurred in the rollup routine. After fixing the ' ||
3198 'errors, please submit the process again with Run Rollup processes ' ||
3199 'only set to Yes.'
3200 );
3201 --
3202 if l_supress_report = 'Y'
3203 then
3204 --
3205 rollback to cwb_global_process;
3206 --
3207 for elenum in 1..ben_manage_cwb_life_events.g_num_cwb_processes
3208 loop
3209 --
3210 ben_batch_reporting.batch_reports
3211 (p_concurrent_request_id => ben_manage_cwb_life_events.g_cwb_processes_rec(elenum),
3212 p_mode => 'W',
3213 p_report_type => 'ACTIVITY_SUMMARY');
3214 --
3215 ben_batch_reporting.batch_reports
3216 (p_concurrent_request_id => ben_manage_cwb_life_events.g_cwb_processes_rec(elenum),
3217 p_report_type => 'ERROR_BY_PERSON');
3218 --
3219 end loop;
3220 --
3221 commit; /* Bug 4875181 - This was required otherwise the error and summary report
3222 concurrent requests dont get committed
3223 */
3224 --
3225 end if;
3226 end if ;
3227 --
3228 raise ;
3229 --
3230 end;
3231 --
3232 -- Spawn the reports at the end.
3233 --
3234 g_error_log_rec.calling_proc := 'Before Reports';
3235 g_error_log_rec.step_number := 8;
3236 --
3237 if nvl(ben_manage_cwb_life_events.g_num_cwb_processes, 0) <> 0
3238 then
3239 --
3240 if l_supress_report = 'Y' then
3241 for elenum in 1..ben_manage_cwb_life_events.g_num_cwb_processes
3242 loop
3243 --
3244 ben_batch_reporting.batch_reports
3245 (p_concurrent_request_id => ben_manage_cwb_life_events.g_cwb_processes_rec(elenum),
3246 p_mode => 'W',
3247 p_report_type => 'ACTIVITY_SUMMARY');
3248 --
3249 ben_batch_reporting.batch_reports
3250 (p_concurrent_request_id => ben_manage_cwb_life_events.g_cwb_processes_rec(elenum),
3251 p_report_type => 'ERROR_BY_PERSON');
3252 --
3253 end loop;
3254 end if;
3255 end if ;
3256 --
3257 exception when others then
3258 --
3259 if nvl(p_online_call_flag, 'N') = 'N' then
3260 --
3261 -- Write into log only if not a online process.
3262 -- Bug 4636102. The fnd_message.get was killing
3263 -- the error even for online mode.
3264 --
3265 fnd_file.put_line(which => fnd_file.log,
3266 buff => 'Error occured after calling: ' ||
3267 g_error_log_rec.calling_proc);
3268 fnd_file.put_line(which => fnd_file.log,
3269 buff => ' Step Number: ' ||
3270 to_number(g_error_log_rec.step_number));
3271 fnd_file.put_line(which => fnd_file.log,
3272 buff => ' Error : ' ||
3273 nvl(fnd_message.get,sqlerrm));
3274 end if;
3275 --
3276 fnd_message.raise_error;
3277 --
3278 end global_process;
3279 --
3280 -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.
3281 --
3282 procedure populate_cwb_rates(
3283 --
3284 -- Columns needed for ben_cwb_person_rates
3285 --
3286 p_person_id in number
3287 ,p_assignment_id in number default null
3288 ,p_organization_id in number default null
3289 ,p_pl_id in number
3290 ,p_oipl_id in number default null
3291 ,p_opt_id in number default null
3292 ,p_ler_id in number default null
3293 ,p_business_group_id in number default null
3294 ,p_acty_base_rt_id in number default null
3295 ,p_elig_flag in varchar2 default 'Y'
3296 ,p_inelig_rsn_cd in varchar2 default null
3297 --
3298 -- Columns needed by BEN_CWB_PERSON_GROUPS
3299 --
3300 ,p_due_dt in date default null
3301 ,p_access_cd in varchar2 default null
3302 ,p_elig_per_elctbl_chc_id in number default null
3303 ,p_no_person_rates in varchar2 default null
3304 ,p_no_person_groups in varchar2 default null
3305 ,p_currency_det_cd in varchar2 default null
3306 ,p_element_det_rl in number default null
3307 ,p_base_element_type_id in number default null
3308 ) is
3309 --
3310 -- 9999 cache this data and use it.
3311 --
3312 cursor c_group_oipl(cv_lf_evt_ocrd_dt in date) is
3313 select group_oipl.oipl_id
3314 from ben_oipl_f oipl
3315 ,ben_oipl_f group_oipl
3316 ,ben_pl_f pl
3317 ,ben_opt_f opt
3318 where oipl.oipl_id = p_oipl_id
3319 and oipl.opt_id = opt.opt_id
3320 and opt.group_opt_id = group_oipl.opt_id
3321 and group_oipl.pl_id = pl.group_pl_id
3322 and pl.pl_id = oipl.pl_id
3323 and cv_lf_evt_ocrd_dt between oipl.effective_start_date
3324 and oipl.effective_end_date
3325 and cv_lf_evt_ocrd_dt between group_oipl.effective_start_date
3326 and group_oipl.effective_end_date
3327 and cv_lf_evt_ocrd_dt between pl.effective_start_date
3328 and pl.effective_end_date
3329 and cv_lf_evt_ocrd_dt between opt.effective_start_date
3330 and opt.effective_end_date;
3331 --
3332 cursor c_epe(cv_elig_per_elctbl_chc_id in number) is
3333 select elig_flag, inelig_rsn_cd
3334 from ben_elig_per_elctbl_chc
3335 where elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id;
3336 --
3337 l_oipl_id number;
3338 l_group_oipl_id number;
3339 l_inelig_rsn_cd varchar2(80);
3340 l_elig_flag varchar2(80);
3341 l_element_type_id number;
3342 l_input_value_id number;
3343 l_currency_cd varchar2(80);
3344 l_package varchar2(80) := g_package||'.populate_cwb_rates' ;
3345 --
3346 begin
3347 --
3348 if g_debug then
3349 hr_utility.set_location ('Entering :' || l_package,10);
3350 end if;
3351 --
3352 if p_pl_id = g_cache_group_plan_rec.group_pl_id then
3353 --
3354 -- Populate BEN_CWB_PERSON_GROUPS
3355 -- Primary Key: GROUP_PER_IN_LER_ID, GROUP_PL_ID, GROUP_OIPL_ID
3356 --
3357 if g_debug then
3358 hr_utility.set_location ('Group rate ' ,15);
3359 end if;
3360 --
3361 /* Bug 3510081
3362 if g_cwb_person_groups_rec.ws_bdgt_val is not null
3363 and g_cache_group_plan_rec.auto_distr_flag = 'Y'
3364 then
3365 g_cwb_person_groups_rec.ws_bdgt_iss_val :=
3366 g_cwb_person_groups_rec.ws_bdgt_val;
3367 g_cwb_person_groups_rec.ws_bdgt_iss_date :=
3368 g_cache_group_plan_rec.group_lf_evt_ocrd_dt;
3369 end if;
3370 */
3371 --
3372 -- Bug 3510081 : Populate dflt values irrespective of auto_distr_flag.
3373 --
3374 g_cwb_person_groups_rec.dflt_ws_bdgt_val :=
3375 g_cwb_person_groups_rec.ws_bdgt_val;
3376 g_cwb_person_groups_rec.dflt_dist_bdgt_val :=
3377 g_cwb_person_groups_rec.dist_bdgt_val;
3378 --
3379 --
3380 -- Find the group_oipl_id
3381 --
3382 open c_group_oipl(g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3383 fetch c_group_oipl into l_group_oipl_id;
3384 close c_group_oipl;
3385 --
3386 insert into ben_cwb_person_groups
3387 (group_per_in_ler_id,
3388 group_pl_id ,
3389 group_oipl_id ,
3390 lf_evt_ocrd_dt ,
3391 bdgt_pop_cd ,
3392 due_dt ,
3393 access_cd ,
3394 approval_cd ,
3395 approval_date ,
3396 approval_comments ,
3397 submit_cd ,
3398 submit_date ,
3399 submit_comments ,
3400 dist_bdgt_val ,
3401 ws_bdgt_val ,
3402 -- Bug 3510081 New columns need to be populated.
3403 dflt_dist_bdgt_val ,
3404 dflt_ws_bdgt_val ,
3405 rsrv_val ,
3406 dist_bdgt_mn_val ,
3407 dist_bdgt_mx_val ,
3408 dist_bdgt_incr_val ,
3409 ws_bdgt_mn_val ,
3410 ws_bdgt_mx_val ,
3411 ws_bdgt_incr_val ,
3412 rsrv_mn_val ,
3413 rsrv_mx_val ,
3414 rsrv_incr_val ,
3415 dist_bdgt_iss_val ,
3416 ws_bdgt_iss_val ,
3417 dist_bdgt_iss_date ,
3418 ws_bdgt_iss_date ,
3419 ws_bdgt_val_last_upd_date ,
3420 dist_bdgt_val_last_upd_date ,
3421 rsrv_val_last_upd_date ,
3422 ws_bdgt_val_last_upd_by ,
3423 dist_bdgt_val_last_upd_by ,
3424 rsrv_val_last_upd_by ,
3425 object_version_number ,
3426 last_update_date ,
3427 last_updated_by ,
3428 last_update_login ,
3429 created_by ,
3430 creation_date
3431 ) values (
3432 g_cache_group_plan_rec.group_per_in_ler_id,
3433 g_cache_group_plan_rec.group_pl_id ,
3434 nvl(l_group_oipl_id, -1) ,
3435 g_cache_group_plan_rec.group_lf_evt_ocrd_dt ,
3436 null, -- bdgt_pop_cd
3437 nvl(p_due_dt,g_cache_group_plan_rec.ws_upd_end_dt), -- this can go as null
3438 g_cache_group_plan_rec.access_cd,
3439 null, -- approval_cd
3440 null, -- approval_date
3441 null, -- approval_comments
3442 'NS', -- submit_cd
3443 null, -- submit_date
3444 null, -- submit_comments
3445 --
3446 -- Bug 3510081 : No need to populate the budget values here.
3447 -- Need to verify whether other values need to be populated or not.
3448 --
3449 null, -- g_cwb_person_groups_rec.dist_bdgt_val ,
3450 null, -- g_cwb_person_groups_rec.ws_bdgt_val ,
3451 g_cwb_person_groups_rec.dflt_dist_bdgt_val ,
3452 g_cwb_person_groups_rec.dflt_ws_bdgt_val ,
3453 g_cwb_person_groups_rec.rsrv_val ,
3454 g_cwb_person_groups_rec.dist_bdgt_mn_val ,
3455 g_cwb_person_groups_rec.dist_bdgt_mx_val ,
3456 g_cwb_person_groups_rec.dist_bdgt_incr_val ,
3457 g_cwb_person_groups_rec.ws_bdgt_mn_val ,
3458 g_cwb_person_groups_rec.ws_bdgt_mx_val ,
3459 g_cwb_person_groups_rec.ws_bdgt_incr_val ,
3460 g_cwb_person_groups_rec.rsrv_mn_val ,
3461 g_cwb_person_groups_rec.rsrv_mx_val ,
3462 g_cwb_person_groups_rec.rsrv_incr_val ,
3463 --
3464 -- Bug 3510081 : No need to populate the budget values here.
3465 -- Need to verify whether other values need to be populated or not.
3466 --
3467 null, -- g_cwb_person_groups_rec.dist_bdgt_iss_val ,
3468 null, -- g_cwb_person_groups_rec.ws_bdgt_iss_val ,
3469 null, -- g_cwb_person_groups_rec.dist_bdgt_iss_date ,
3470 null, -- g_cwb_person_groups_rec.ws_bdgt_iss_date ,
3471 g_cwb_person_groups_rec.ws_bdgt_val_last_upd_date ,
3472 g_cwb_person_groups_rec.dist_bdgt_val_last_upd_date ,
3473 g_cwb_person_groups_rec.rsrv_val_last_upd_date ,
3474 g_cwb_person_groups_rec.ws_bdgt_val_last_upd_by ,
3475 g_cwb_person_groups_rec.dist_bdgt_val_last_upd_by ,
3476 g_cwb_person_groups_rec.rsrv_val_last_upd_by ,
3477 1, -- object_version_number
3478 -- Check all the column values.
3479 g_cwb_person_groups_rec.last_update_date ,
3480 g_cwb_person_groups_rec.last_updated_by ,
3481 g_cwb_person_groups_rec.last_update_login ,
3482 g_cwb_person_groups_rec.created_by ,
3483 g_cwb_person_groups_rec.creation_date
3484 ) ;
3485
3486 end if;
3487 --
3488 -- Populate
3489 -- BEN_CWB_PERSON_RATES
3490 -- Primary Key: PERSON_RATE_ID
3491 --
3492 if ((p_pl_id = g_cache_group_plan_rec.group_pl_id and
3493 g_cache_group_plan_rec.plans_wthn_group_pl = 1
3494 ) OR
3495 (p_pl_id <> g_cache_group_plan_rec.group_pl_id)
3496 ) -- and (nvl(p_no_person_rates, 'N') = 'N')
3497 then
3498 --
3499 if g_debug then
3500 hr_utility.set_location ('Person rate ' ,15);
3501 end if;
3502 --
3503 -- Find the group_oipl_id
3504 --
3505 open c_group_oipl(g_cache_group_plan_rec.group_lf_evt_ocrd_dt);
3506 fetch c_group_oipl into l_group_oipl_id;
3507 close c_group_oipl;
3508 --
3509 -- get the elig_flag and inelg_rsn_cd
3510 --
3511 -- 9999 these columns can be put into epe cache later.
3512 open c_epe(p_elig_per_elctbl_chc_id);
3513 fetch c_epe into l_elig_flag, l_inelig_rsn_cd;
3514 close c_epe;
3515 --
3516 -- Multi currency support
3517 --
3518 determine_curr_code
3519 (p_element_det_rl => p_element_det_rl,
3520 p_acty_base_rt_id => p_acty_base_rt_id,
3521 p_currency_det_cd => p_currency_det_cd,
3522 p_base_element_type_id => p_base_element_type_id,
3523 p_effective_date => g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
3524 p_assignment_id => p_assignment_id,
3525 p_organization_id => p_organization_id,
3526 p_business_group_id => p_business_group_id,
3527 p_pl_id => p_pl_id,
3528 p_opt_id => p_opt_id,
3529 p_ler_id => p_ler_id,
3530 p_element_type_id => l_element_type_id,
3531 p_input_value_id => l_input_value_id,
3532 p_currency_cd => l_currency_cd);
3533 --
3534 if g_debug then
3535 hr_utility.set_location ('Person rate GROUP_PER_IN_LER_ID ' || g_cache_group_plan_rec.group_per_in_ler_id,15);
3536 hr_utility.set_location ('Person rate PERSON_ID ' || p_person_id ,15);
3537 hr_utility.set_location ('Person rate PL_ID ' || p_pl_id,15);
3538 hr_utility.set_location ('Person rate GROUP_PL_ID ' || g_cache_group_plan_rec.group_pl_id,15);
3539 hr_utility.set_location ('Person rate LF_EVT_OCRD_DT ' || g_cache_group_plan_rec.group_lf_evt_ocrd_dt,15);
3540 end if;
3541
3542 insert into ben_cwb_person_rates
3543 (person_rate_id ,
3544 group_per_in_ler_id ,
3545 pl_id ,
3546 oipl_id ,
3547 group_pl_id ,
3548 group_oipl_id ,
3549 lf_evt_ocrd_dt ,
3550 person_id ,
3551 assignment_id ,
3552 elig_flag ,
3553 ws_val ,
3554 ws_mn_val ,
3555 ws_mx_val ,
3556 ws_incr_val ,
3557 elig_sal_val ,
3558 stat_sal_val ,
3559 oth_comp_val ,
3560 tot_comp_val ,
3561 misc1_val ,
3562 misc2_val ,
3563 misc3_val ,
3564 rec_val ,
3565 rec_mn_val ,
3566 rec_mx_val ,
3567 rec_incr_val ,
3568 ws_val_last_upd_date ,
3569 ws_val_last_upd_by ,
3570 pay_proposal_id ,
3571 element_entry_value_id ,
3572 inelig_rsn_cd ,
3573 elig_ovrid_dt ,
3574 elig_ovrid_person_id ,
3575 copy_dist_bdgt_val ,
3576 copy_ws_bdgt_val ,
3577 copy_rsrv_val ,
3578 copy_dist_bdgt_mn_val ,
3579 copy_dist_bdgt_mx_val ,
3580 copy_dist_bdgt_incr_val ,
3581 copy_ws_bdgt_mn_val ,
3582 copy_ws_bdgt_mx_val ,
3583 copy_ws_bdgt_incr_val ,
3584 copy_rsrv_mn_val ,
3585 copy_rsrv_mx_val ,
3586 copy_rsrv_incr_val ,
3587 copy_dist_bdgt_iss_val ,
3588 copy_ws_bdgt_iss_val ,
3589 copy_dist_bdgt_iss_date ,
3590 copy_ws_bdgt_iss_date ,
3591 COMP_POSTING_DATE ,
3592 WS_RT_START_DATE ,
3593 currency ,
3594 object_version_number ,
3595 last_update_date ,
3596 last_updated_by ,
3597 last_update_login ,
3598 created_by ,
3599 creation_date
3600 ) values
3601 (ben_cwb_person_rates_s.nextval ,
3602 nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1) ,
3603 p_pl_id ,
3604 nvl(p_oipl_id, -1),
3605 g_cache_group_plan_rec.group_pl_id ,
3606 nvl(l_group_oipl_id, -1), -- group_oipl_id ,
3607 g_cache_group_plan_rec.group_lf_evt_ocrd_dt ,
3608 p_person_id ,
3609 p_assignment_id ,
3610 l_elig_flag ,
3611 g_cwb_person_rates_rec.ws_val ,
3612 g_cwb_person_rates_rec.ws_mn_val ,
3613 g_cwb_person_rates_rec.ws_mx_val ,
3614 g_cwb_person_rates_rec.ws_incr_val ,
3615 g_cwb_person_rates_rec.elig_sal_val ,
3616 g_cwb_person_rates_rec.stat_sal_val ,
3617 g_cwb_person_rates_rec.oth_comp_val ,
3618 g_cwb_person_rates_rec.tot_comp_val ,
3619 g_cwb_person_rates_rec.misc1_val ,
3620 g_cwb_person_rates_rec.misc2_val ,
3621 g_cwb_person_rates_rec.misc3_val ,
3622 g_cwb_person_rates_rec.rec_val ,
3623 g_cwb_person_rates_rec.rec_mn_val ,
3624 g_cwb_person_rates_rec.rec_mx_val ,
3625 g_cwb_person_rates_rec.rec_incr_val ,
3626 g_cwb_person_rates_rec.ws_val_last_upd_date ,
3627 g_cwb_person_rates_rec.ws_val_last_upd_by ,
3628 g_cwb_person_rates_rec.pay_proposal_id ,
3629 g_cwb_person_rates_rec.element_entry_value_id ,
3630 l_inelig_rsn_cd ,
3631 g_cwb_person_rates_rec.elig_ovrid_dt ,
3632 g_cwb_person_rates_rec.elig_ovrid_person_id ,
3633 g_cwb_person_rates_rec.copy_dist_bdgt_val ,
3634 g_cwb_person_rates_rec.copy_ws_bdgt_val ,
3635 g_cwb_person_rates_rec.copy_rsrv_val ,
3636 g_cwb_person_rates_rec.copy_dist_bdgt_mn_val ,
3637 g_cwb_person_rates_rec.copy_dist_bdgt_mx_val ,
3638 g_cwb_person_rates_rec.copy_dist_bdgt_incr_val ,
3639 g_cwb_person_rates_rec.copy_ws_bdgt_mn_val ,
3640 g_cwb_person_rates_rec.copy_ws_bdgt_mx_val ,
3641 g_cwb_person_rates_rec.copy_ws_bdgt_incr_val ,
3642 g_cwb_person_rates_rec.copy_rsrv_mn_val ,
3643 g_cwb_person_rates_rec.copy_rsrv_mx_val ,
3644 g_cwb_person_rates_rec.copy_rsrv_incr_val ,
3645 g_cwb_person_rates_rec.copy_dist_bdgt_iss_val ,
3646 g_cwb_person_rates_rec.copy_ws_bdgt_iss_val ,
3647 g_cwb_person_rates_rec.copy_dist_bdgt_iss_date ,
3648 g_cwb_person_rates_rec.copy_ws_bdgt_iss_date ,
3649 g_cwb_person_rates_rec.COMP_POSTING_DATE,
3650 g_cwb_person_rates_rec.WS_RT_START_DATE,
3651 l_currency_cd ,
3652 1, -- object_version_number ,
3653 g_cwb_person_rates_rec.last_update_date ,
3654 g_cwb_person_rates_rec.last_updated_by ,
3655 g_cwb_person_rates_rec.last_update_login ,
3656 g_cwb_person_rates_rec.created_by ,
3657 g_cwb_person_rates_rec.creation_date );
3658 --
3659 end if;
3660 --
3661 if g_debug then
3662 hr_utility.set_location ('Leaving :' || l_package,10);
3663 end if;
3664 --
3665 end populate_cwb_rates;
3666 --
3667 procedure rebuild_heirarchy
3668 (p_group_per_in_ler_id in number ) is
3669 --
3670 l_package varchar2(80) := g_package||'.rebuild_heirarchy' ;
3671 -- Bug 2574791
3672 cursor c_pil is
3673 select pil.group_pl_id,
3674 pil.lf_evt_ocrd_dt,
3675 pil.business_group_id,
3676 pil.ler_id
3677 from ben_per_in_ler pil
3678 where pil.per_in_ler_id = p_group_per_in_ler_id ;
3679 --
3680 l_ler_id number ;
3681 begin
3682 --
3683 g_debug := hr_utility.debug_enabled;
3684 if g_debug then
3685 hr_utility.set_location ('Entering :' || l_package,10);
3686 end if;
3687 --
3688 -- Steps
3689 --
3690 -- 1. create pil,-1,-1 record for the p_per_in_ler_id.
3691 -- 2. create pil,-1,-1 records for
3692 -- empoyees whose mgr_pil is = to per_in_ler_id.
3693 -- 3. Delete all records from hierarchy table of the managers of the
3694 -- p_per_in_ler_id and the employees reporting to this
3695 -- p_per_in_ler_id.
3696 -- 4. call the popu_pil_heir to rebuild the table.
3697 --
3698 -- 5. When the first direct reportee is added to a new Manager
3699 -- we need two insert a mgr_pil,0,0 with level 1
3700 --
3701 if p_group_per_in_ler_id is not null then
3702 --
3703 open c_pil;
3704 fetch c_pil into g_rebuild_pl_id,
3705 g_rebuild_lf_evt_ocrd_dt,
3706 g_rebuild_business_group_id,
3707 l_ler_id ;
3708 close c_pil;
3709
3710 if g_debug then
3711 hr_utility.set_location ('Before Step 1',20);
3712 end if;
3713 --
3714 -- Step 1
3715 begin
3716 --
3717 insert into ben_cwb_group_hrchy (
3718 emp_per_in_ler_id,
3719 mgr_per_in_ler_id,
3720 lvl_num )
3721 values (
3722 p_group_per_in_ler_id,
3723 -1,
3724 -1 );
3725 --
3726 exception when no_data_found then
3727 --
3728 null;
3729 --
3730 when others then
3731 --
3732 null; -- raise ;
3733 --
3734 end;
3735 --
3736 if g_debug then
3737 hr_utility.set_location ('After Step 1',20);
3738 --
3739 hr_utility.set_location ('Before Step 2 ',20);
3740 end if;
3741 --
3742 --Step 2
3743 -- Don't insert for the pil,0,0 record since it is
3744 -- Already handled in Step 1 above.
3745 --
3746 declare
3747
3748 cursor c_emp_repo is
3749 select emp_per_in_ler_id
3750 from ben_cwb_group_hrchy
3751 where mgr_per_in_ler_id = p_group_per_in_ler_id
3752 and lvl_num > 0 ;
3753 --
3754 begin
3755 --
3756 for r_emp_repo in c_emp_repo loop
3757 --
3758 begin
3759
3760 insert into ben_cwb_group_hrchy (
3761 emp_per_in_ler_id,
3762 mgr_per_in_ler_id,
3763 lvl_num ) values (r_emp_repo.emp_per_in_ler_id, -1, -1);
3764 --
3765 exception when others then
3766 null;
3767 end;
3768 end loop;
3769 --
3770 exception when no_data_found then
3771 --
3772 null;
3773 --
3774 when others then
3775 --
3776 raise ;
3777 --
3778 end;
3779 --
3780 if g_debug then
3781 hr_utility.set_location ('After Step 2 ',20);
3782 --
3783 hr_utility.set_location ('Before Step 3 ',20);
3784 end if;
3785 --
3786 begin
3787 --
3788 --First Delete the Manager's own Hierarchy
3789 --
3790 delete from ben_cwb_group_hrchy
3791 where emp_per_in_ler_id = p_group_per_in_ler_id
3792 and lvl_num >= 0;
3793 --
3794 -- Now delete the Employees reporting to this manager(if he is a manager).
3795 --
3796 delete from ben_cwb_group_hrchy
3797 where emp_per_in_ler_id in (
3798 select emp_per_in_ler_id
3799 from ben_cwb_group_hrchy
3800 where mgr_per_in_ler_id = p_group_per_in_ler_id )
3801 and lvl_num >= 0;
3802 --
3803 exception when others then
3804 --
3805 raise ;
3806 --
3807 end;
3808 --
3809 if g_debug then
3810 hr_utility.set_location ('After Step 3 ',20);
3811 --
3812 hr_utility.set_location ('Before Calling popu_pel_heir',20);
3813 end if;
3814 --
3815 begin
3816 --
3817 ben_manage_cwb_life_events.popu_group_pil_heir(
3818 g_rebuild_pl_id,
3819 g_rebuild_lf_evt_ocrd_dt,
3820 g_rebuild_business_group_id,
3821 l_ler_id ) ;
3822 --
3823 exception when others then
3824 --
3825 raise ;
3826 --
3827 end;
3828 --
3829 if g_debug then
3830 hr_utility.set_location ('Before Step 5',20);
3831 end if;
3832 --
3833 begin
3834 --
3835 insert into ben_cwb_group_hrchy(
3836 emp_per_in_ler_id,
3837 mgr_per_in_ler_id,
3838 lvl_num )
3839 select
3840 distinct emp_per_in_ler_id,
3841 emp_per_in_ler_id,
3842 0
3843 from ben_cwb_group_hrchy cwb1
3844 where emp_per_in_ler_id =
3845 ( select mgr_per_in_ler_id from ben_cwb_group_hrchy
3846 where emp_per_in_ler_id = p_group_per_in_ler_id
3847 and lvl_num = 1 )
3848 and not exists ( select null from ben_cwb_group_hrchy cwb2
3849 where cwb1.emp_per_in_ler_id = cwb2.emp_per_in_ler_id
3850 and lvl_num = 0 ) ;
3851 --
3852 exception when no_data_found then
3853 --
3854 null;
3855 --
3856 when others then
3857 --
3858 raise ;
3859 --
3860 end;
3861 --
3862 /* PERF 4587770 can get rid of not exists by suppressing when others,
3863 -- move the sub query into main query.
3864 -- Waiting for GSI to validate. Once validated at gsi db, following code
3865 -- can replace above insert.
3866 --
3867 insert into ben_cwb_group_hrchy(
3868 emp_per_in_ler_id,
3869 mgr_per_in_ler_id,
3870 lvl_num )
3871 select
3872 h.mgr_per_in_ler_id
3873 ,h.mgr_per_in_ler_id
3874 ,0
3875 from ben_cwb_group_hrchy h
3876 where h.emp_per_in_ler_id = p_group_per_in_ler_id
3877 and h.lvl_num = 1;
3878 --
3879 exception when others then
3880 --
3881 null ;
3882 --
3883 end;
3884 */
3885 --
3886 -- Bug 2574791
3887 g_rebuild_pl_id := null;
3888 g_rebuild_lf_evt_ocrd_dt := null;
3889 g_rebuild_business_group_id := null;
3890 --
3891 end if;
3892 --
3893 if g_debug then
3894 hr_utility.set_location ('Afert Step 5',20);
3895 hr_utility.set_location ('After Calling popu_pil_heir',20);
3896 hr_utility.set_location ('Leaving : rebuild_heirarchy',30);
3897 end if;
3898 --
3899 --
3900 end rebuild_heirarchy ;
3901 --
3902 procedure popu_missing_person_pil (
3903 p_mode in varchar2,
3904 p_person_id in number,
3905 p_group_per_in_ler_id in number,
3906 p_group_pl_id in number,
3907 p_group_lf_evt_ocrd_dt in date,
3908 p_group_business_group_id in number,
3909 p_group_ler_id in number,
3910 p_use_eff_dt_flag in varchar2 default 'N',
3911 p_effective_date in date default null) is
3912 --
3913 l_proc varchar2(72) := g_package||'.popu_missing_person_pil';
3914 --
3915 -- 9999 check whether person id is populated in ben_cwb_person_rates.
3916 --
3917 CURSOR c_person_rates
3918 (c_person_id IN NUMBER
3919 ,c_group_lf_evt_ocrd_dt IN DATE
3920 ,c_group_pl_id IN NUMBER
3921 )
3922 is
3923 select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
3924 from ben_cwb_person_rates cpr
3925 where cpr.person_id = c_person_id
3926 and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3927 and cpr.group_pl_id = c_group_pl_id
3928 -- Bug 3517726 : Do not consider any data which is not deleted
3929 -- by backoout.
3930 and cpr.group_per_in_ler_id = -1
3931 for update of cpr.group_per_in_ler_id;
3932 --
3933 l_mnl_dt date;
3934 l_dtctd_dt date;
3935 l_procd_dt date;
3936 l_unprocd_dt date;
3937 l_voidd_dt date;
3938 l_strtd_dt date;
3939 l_effective_date date;
3940 --
3941 l_ws_mgr_id number;
3942 l_assignment_id number(15);
3943 l_curr_per_in_ler_id number;
3944 l_ptnl_ler_for_per_id number;
3945 l_object_version_number NUMBER;
3946 i binary_integer := 1;
3947 l_copy_person_bdgt_count binary_integer;
3948 --
3949 cursor c_cpg(c_group_pl_id number, c_group_lf_evt_ocrd_dt date) is
3950 select cpg.*
3951 from ben_cwb_person_groups cpg
3952 where cpg.group_pl_id = c_group_pl_id
3953 and cpg.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3954 and cpg.group_per_in_ler_id =
3955 (select cpg1.group_per_in_ler_id
3956 from ben_cwb_person_groups cpg1
3957 where cpg1.group_pl_id = c_group_pl_id
3958 and cpg1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3959 and rownum = 1);
3960 --
3961 l_copy_dist_bdgt_val ben_cwb_person_groups.dist_bdgt_val%type;
3962 l_copy_dist_bdgt_mn_val ben_cwb_person_groups.dist_bdgt_mn_val%type;
3963 l_copy_dist_bdgt_mx_val ben_cwb_person_groups.dist_bdgt_mx_val%type;
3964 l_copy_dist_bdgt_incr_val ben_cwb_person_groups.dist_bdgt_incr_val%type;
3965 l_copy_dist_bdgt_iss_val ben_cwb_person_groups.dist_bdgt_iss_val%type;
3966 l_copy_dist_bdgt_iss_date ben_cwb_person_groups.dist_bdgt_iss_date%type;
3967 l_copy_ws_bdgt_val ben_cwb_person_groups.ws_bdgt_val%type;
3968 l_copy_ws_bdgt_mn_val ben_cwb_person_groups.ws_bdgt_mn_val%type;
3969 l_copy_ws_bdgt_mx_val ben_cwb_person_groups.ws_bdgt_mx_val%type;
3970 l_copy_ws_bdgt_incr_val ben_cwb_person_groups.ws_bdgt_incr_val%type;
3971 l_copy_ws_bdgt_iss_val ben_cwb_person_groups.ws_bdgt_iss_val%type;
3972 l_copy_ws_bdgt_iss_date ben_cwb_person_groups.ws_bdgt_iss_date%type;
3973 l_copy_rsrv_val ben_cwb_person_groups.rsrv_val%type;
3974 l_copy_rsrv_mn_val ben_cwb_person_groups.rsrv_mn_val%type;
3975 l_copy_rsrv_mx_val ben_cwb_person_groups.rsrv_mx_val%type;
3976 l_copy_rsrv_incr_val ben_cwb_person_groups.rsrv_incr_val%type;
3977 --
3978 CURSOR c_pl_dsgn (c_group_pl_id NUMBER, c_group_lf_evt_ocrd_dt DATE)
3979 IS
3980 SELECT group_oipl_id
3981 FROM ben_cwb_pl_dsgn
3982 WHERE group_pl_id = c_group_pl_id
3983 AND pl_id = group_pl_id
3984 AND lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt;
3985
3986 --
3987 begin
3988 --
3989 if g_debug then
3990 hr_utility.set_location ('Entering : ' || l_proc,30);
3991 end if;
3992 --
3993 -- Create the potential and per in ler in group plan.
3994 --
3995 if p_use_eff_dt_flag = 'N' then
3996 l_effective_date := p_group_lf_evt_ocrd_dt;
3997 else
3998 l_effective_date := p_effective_date;
3999 end if;
4000 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
4001 (p_validate => false,
4002 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
4003 p_lf_evt_ocrd_dt => p_group_lf_evt_ocrd_dt,
4004 p_ptnl_ler_for_per_stat_cd => 'PROCD' ,
4005 --l_ptnl_ler_for_per_stat_cd_use,
4006 p_ler_id => p_group_ler_id,
4007 p_person_id => p_person_id,
4008 p_ntfn_dt => trunc(sysdate), -- l_ntfn_dt,
4009 p_procd_dt => trunc(sysdate),
4010 p_dtctd_dt => trunc(sysdate),
4011 p_business_group_id => p_group_business_group_id,
4012 p_object_version_number => l_object_version_number,
4013 p_effective_date => p_group_lf_evt_ocrd_dt,
4014 p_program_application_id => fnd_global.prog_appl_id,
4015 p_program_id => fnd_global.conc_program_id,
4016 p_request_id => fnd_global.conc_request_id,
4017 p_program_update_date => trunc(sysdate));
4018 --
4019 -- Get the manager information.
4020 --
4021 ben_manage_cwb_life_events.get_cwb_manager_and_assignment
4022 (p_person_id => p_person_id,
4023 p_hrchy_to_use_cd => ben_manage_cwb_life_events.g_cache_group_plan_rec.hrchy_to_use_cd,
4024 p_pos_structure_version_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.pos_structure_version_id,
4025 p_effective_date => l_effective_date, -- ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4026 p_manager_id => l_ws_mgr_id,
4027 p_assignment_id => l_assignment_id ) ;
4028 --
4029 hr_utility.set_location('l_ws_mgr_id = ' || l_ws_mgr_id, 1234);
4030 hr_utility.set_location('l_assignment_id = ' || l_assignment_id, 1234);
4031 --
4032 -- Create the group person life event
4033 --
4034 hr_utility.set_location('group_pl_id = ' || ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id, 1234);
4035 ben_Person_Life_Event_api.create_Person_Life_Event_perf
4036 (p_validate => false
4037 ,p_per_in_ler_id => l_curr_per_in_ler_id
4038 ,p_ler_id => p_group_ler_id
4039 ,p_person_id => p_person_id
4040 ,p_per_in_ler_stat_cd => 'STRTD'
4041 ,p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id -- 99999
4042 ,p_lf_evt_ocrd_dt => p_group_lf_evt_ocrd_dt
4043 ,p_business_group_id => p_group_business_group_id
4044 ,p_ntfn_dt => trunc(sysdate)
4045 ,p_group_pl_id => p_group_pl_id
4046 ,p_ws_mgr_id => l_ws_mgr_id
4047 ,p_assignment_id => l_assignment_id
4048 ,p_object_version_number => l_object_version_number
4049 ,p_effective_date => l_effective_date -- p_group_lf_evt_ocrd_dt
4050 ,p_program_application_id => fnd_global.prog_appl_id
4051 ,p_program_id => fnd_global.conc_program_id
4052 ,p_request_id => fnd_global.conc_request_id
4053 ,p_program_update_date => sysdate
4054 ,p_procd_dt => l_procd_dt
4055 ,p_strtd_dt => l_strtd_dt
4056 ,p_voidd_dt => l_voidd_dt);
4057 --
4058 -- Per in ler created is a group per in ler so populate other
4059 -- plan design tables.
4060 --
4061 hr_utility.set_location('Call ben_manage_cwb_life_events.popu_cwb_tables', 1234);
4062 if p_use_eff_dt_flag = 'Y' then
4063 --
4064 ben_manage_cwb_life_events.popu_cwb_tables(
4065 p_group_per_in_ler_id => l_curr_per_in_ler_id,
4066 p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id,
4067 p_group_lf_evt_ocrd_dt => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4068 p_group_business_group_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_business_group_id,
4069 p_group_ler_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_ler_id,
4070 p_use_eff_dt_flag => 'Y',
4071 p_effective_date => p_effective_date);
4072 --
4073 else
4074 --
4075 ben_manage_cwb_life_events.popu_cwb_tables(
4076 p_group_per_in_ler_id => l_curr_per_in_ler_id,
4077 p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id,
4078 p_group_lf_evt_ocrd_dt => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4079 p_group_business_group_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_business_group_id,
4080 p_group_ler_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_ler_id);
4081 end if;
4082 --
4083 --
4084 -- Cache the cwb_person_data if not cached already
4085 --
4086 if nvl(g_cache_cpg_rec.count, 0) = 0 then
4087 --
4088 -- Cache the cwb_person_groups data.
4089 --
4090 hr_utility.set_location('popu_cwb_tables before for loop', 1234);
4091 i := 1;
4092 for l_cpg_rec in c_cpg(p_group_pl_id, p_group_lf_evt_ocrd_dt) loop
4093 --
4094 hr_utility.set_location('popu_cwb_tables for loop i = ' || to_char(i), 1234);
4095 g_cache_cpg_rec(i) := l_cpg_rec;
4096 i := i+1;
4097 --
4098 end loop;
4099 end if;
4100
4101 /* 5124534 :
4102 if g_cache_cpg_rec.count is still zero means
4103 there is no record in ben_cwb_person_groups .
4104 populate group_pl_id,group_oipl_id,budget values
4105 into g_cache_cpg_rec
4106
4107 */
4108
4109 IF NVL (g_cache_cpg_rec.COUNT, 0) = 0
4110 THEN
4111 hr_utility.set_location (' populate g_cache_cpg_rec', 1234);
4112 i := 1;
4113
4114 FOR l_pl_dsgn IN c_pl_dsgn (g_cache_group_plan_rec.group_pl_id,
4115 g_cache_group_plan_rec.group_lf_evt_ocrd_dt
4116 )
4117 LOOP
4118
4119 g_cache_cpg_rec (i).group_pl_id := g_cache_group_plan_rec.group_pl_id;
4120 g_cache_cpg_rec (i).group_oipl_id := NVL (l_pl_dsgn.group_oipl_id,
4121 -1);
4122 g_cache_cpg_rec (i).lf_evt_ocrd_dt :=
4123 g_cache_group_plan_rec.group_lf_evt_ocrd_dt;
4124 g_cache_cpg_rec (i).due_dt := g_cache_group_plan_rec.ws_upd_end_dt;
4125 g_cache_cpg_rec (i).access_cd := g_cache_group_plan_rec.access_cd;
4126
4127 i := i + 1;
4128 END LOOP;
4129
4130 END IF; /* 5124534 end */
4131
4132
4133 --
4134 -- Initialise the copy budget structures.
4135 --
4136 g_cache_copy_person_bdgt_tbl := g_cache_copy_person_bdgt_tbl1;
4137 --
4138 -- Now update the group per in ler id on ben_cwb_person_rates
4139 --
4140 l_copy_person_bdgt_count := 0;
4141 for l_per_rt_rec in c_person_rates(p_person_id,
4142 p_group_lf_evt_ocrd_dt,
4143 p_group_pl_id
4144 ) loop
4145 --
4146 update ben_cwb_person_rates cpr
4147 set cpr.group_per_in_ler_id = l_curr_per_in_ler_id
4148 where cpr.rowid = l_per_rt_rec.rowid;
4149 --
4150 -- Write copy budget values into table structures
4151 --
4152 if l_per_rt_rec.copy_dist_bdgt_val is not null or
4153 l_per_rt_rec.copy_dist_bdgt_mn_val is not null or
4154 l_per_rt_rec.copy_dist_bdgt_mx_val is not null or
4155 l_per_rt_rec.copy_dist_bdgt_incr_val is not null or
4156 l_per_rt_rec.copy_dist_bdgt_iss_val is not null or
4157 l_per_rt_rec.copy_dist_bdgt_iss_date is not null or
4158 l_per_rt_rec.copy_ws_bdgt_val is not null or
4159 l_per_rt_rec.copy_ws_bdgt_mn_val is not null or
4160 l_per_rt_rec.copy_ws_bdgt_mx_val is not null or
4161 l_per_rt_rec.copy_ws_bdgt_incr_val is not null or
4162 l_per_rt_rec.copy_ws_bdgt_iss_val is not null or
4163 l_per_rt_rec.copy_ws_bdgt_iss_date is not null or
4164 l_per_rt_rec.copy_rsrv_val is not null or
4165 l_per_rt_rec.copy_rsrv_mn_val is not null or
4166 l_per_rt_rec.copy_rsrv_mx_val is not null or
4167 l_per_rt_rec.copy_rsrv_incr_val is not null
4168 then
4169 --
4170 l_copy_person_bdgt_count := l_copy_person_bdgt_count + 1;
4171 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_val :=
4172 l_per_rt_rec.copy_dist_bdgt_val;
4173 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_mn_val :=
4174 l_per_rt_rec.copy_dist_bdgt_mn_val;
4175 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_mx_val :=
4176 l_per_rt_rec.copy_dist_bdgt_mx_val;
4177 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_incr_val :=
4178 l_per_rt_rec.copy_dist_bdgt_incr_val;
4179 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_iss_val :=
4180 l_per_rt_rec.copy_dist_bdgt_iss_val;
4181 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_dist_bdgt_iss_date :=
4182 l_per_rt_rec.copy_dist_bdgt_iss_date;
4183 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_val :=
4184 l_per_rt_rec.copy_ws_bdgt_val;
4185 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_mn_val :=
4186 l_per_rt_rec.copy_ws_bdgt_mn_val;
4187 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_mx_val :=
4188 l_per_rt_rec.copy_ws_bdgt_mx_val;
4189 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_incr_val :=
4190 l_per_rt_rec.copy_ws_bdgt_incr_val;
4191 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_iss_val :=
4192 l_per_rt_rec.copy_ws_bdgt_iss_val;
4193 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_ws_bdgt_iss_date :=
4194 l_per_rt_rec.copy_ws_bdgt_iss_date;
4195 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_rsrv_val :=
4196 l_per_rt_rec.copy_rsrv_val;
4197 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_rsrv_mn_val :=
4198 l_per_rt_rec.copy_rsrv_mn_val;
4199 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_rsrv_mx_val :=
4200 l_per_rt_rec.copy_rsrv_mx_val;
4201 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).copy_rsrv_incr_val :=
4202 l_per_rt_rec.copy_rsrv_incr_val;
4203 --
4204 -- Added for bug 4258200
4205 --
4206 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).group_pl_id :=
4207 l_per_rt_rec.group_pl_id;
4208 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).group_oipl_id :=
4209 l_per_rt_rec.group_oipl_id;
4210 g_cache_copy_person_bdgt_tbl(l_copy_person_bdgt_count).group_lf_evt_ocrd_dt :=
4211 l_per_rt_rec.lf_evt_ocrd_dt;
4212 --
4213 end if;
4214 --
4215 end loop;
4216 --
4217 -- Create the ben_cwb_person_groups data.
4218 --
4219 if nvl(g_cache_cpg_rec.count, 0) > 0 then
4220 --
4221 for l_cpg_count in 1..g_cache_cpg_rec.count loop
4222 --
4223 -- update ben_cwb_person_groups with budget copy_ columns
4224 --
4225 if nvl(l_copy_person_bdgt_count, 0) > 0 then
4226 for l_bdgt_count in 1..l_copy_person_bdgt_count loop
4227 --
4228 if g_cache_cpg_rec(l_cpg_count).group_pl_id =
4229 g_cache_copy_person_bdgt_tbl(l_bdgt_count).group_pl_id
4230 and nvl(g_cache_cpg_rec(l_cpg_count).group_oipl_id, -1) =
4231 nvl(g_cache_copy_person_bdgt_tbl(l_bdgt_count).group_oipl_id, -1)
4232 and g_cache_cpg_rec(l_cpg_count).lf_evt_ocrd_dt =
4233 g_cache_copy_person_bdgt_tbl(l_bdgt_count).group_lf_evt_ocrd_dt
4234 then
4235 --
4236 -- Assign budget data from cache structure to person group structure.
4237 --
4238 l_copy_dist_bdgt_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_val;
4239 l_copy_dist_bdgt_mn_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_mn_val;
4240 l_copy_dist_bdgt_mx_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_mx_val;
4241 l_copy_dist_bdgt_incr_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_incr_val;
4242 l_copy_dist_bdgt_iss_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_iss_val;
4243 l_copy_dist_bdgt_iss_date := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_dist_bdgt_iss_date;
4244 l_copy_ws_bdgt_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_val;
4245 l_copy_ws_bdgt_mn_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_mn_val;
4246 l_copy_ws_bdgt_mx_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_mx_val;
4247 l_copy_ws_bdgt_incr_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_incr_val;
4248 l_copy_ws_bdgt_iss_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_iss_val;
4249 l_copy_ws_bdgt_iss_date := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_ws_bdgt_iss_date;
4250 l_copy_rsrv_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_rsrv_val;
4251 l_copy_rsrv_mn_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_rsrv_mn_val;
4252 l_copy_rsrv_mx_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_rsrv_mx_val;
4253 l_copy_rsrv_incr_val := g_cache_copy_person_bdgt_tbl(l_bdgt_count).copy_rsrv_incr_val;
4254 --
4255 else
4256 --
4257 l_copy_dist_bdgt_val := g_cache_cpg_rec(l_cpg_count).dist_bdgt_val;
4258 l_copy_dist_bdgt_mn_val := g_cache_cpg_rec(l_cpg_count).dist_bdgt_mn_val;
4259 l_copy_dist_bdgt_mx_val := g_cache_cpg_rec(l_cpg_count).dist_bdgt_mx_val;
4260 l_copy_dist_bdgt_incr_val := g_cache_cpg_rec(l_cpg_count).dist_bdgt_incr_val;
4261 l_copy_dist_bdgt_iss_val := g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_val;
4262 l_copy_dist_bdgt_iss_date := g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_date;
4263 l_copy_ws_bdgt_val := g_cache_cpg_rec(l_cpg_count).ws_bdgt_val;
4264 l_copy_ws_bdgt_mn_val := g_cache_cpg_rec(l_cpg_count).ws_bdgt_mn_val;
4265 l_copy_ws_bdgt_mx_val := g_cache_cpg_rec(l_cpg_count).ws_bdgt_mx_val;
4266 l_copy_ws_bdgt_incr_val := g_cache_cpg_rec(l_cpg_count).ws_bdgt_incr_val;
4267 l_copy_ws_bdgt_iss_val := g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_val;
4268 l_copy_ws_bdgt_iss_date := g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_date;
4269 l_copy_rsrv_val := g_cache_cpg_rec(l_cpg_count).rsrv_val;
4270 l_copy_rsrv_mn_val := g_cache_cpg_rec(l_cpg_count).rsrv_mn_val;
4271 l_copy_rsrv_mx_val := g_cache_cpg_rec(l_cpg_count).rsrv_mx_val;
4272 l_copy_rsrv_incr_val := g_cache_cpg_rec(l_cpg_count).rsrv_incr_val;
4273 --
4274 end if;
4275 --
4276 end loop;
4277 --
4278 end if;
4279 --
4280 -- create row in ben_cwb_person_groups
4281 --
4282 hr_utility.set_location('Creating missing ben_cwb_person_groups', 1234);
4283 insert into ben_cwb_person_groups
4284 (group_per_in_ler_id,
4285 group_pl_id ,
4286 group_oipl_id ,
4287 lf_evt_ocrd_dt ,
4288 bdgt_pop_cd ,
4289 due_dt ,
4290 access_cd ,
4291 approval_cd ,
4292 approval_date ,
4293 approval_comments ,
4294 submit_cd ,
4295 submit_date ,
4296 submit_comments ,
4297 dist_bdgt_val ,
4298 ws_bdgt_val ,
4299 -- Bug 3510081 New columns need to be populated.
4300 dflt_dist_bdgt_val ,
4301 dflt_ws_bdgt_val ,
4302 rsrv_val ,
4303 dist_bdgt_mn_val ,
4304 dist_bdgt_mx_val ,
4305 dist_bdgt_incr_val ,
4306 ws_bdgt_mn_val ,
4307 ws_bdgt_mx_val ,
4308 ws_bdgt_incr_val ,
4309 rsrv_mn_val ,
4310 rsrv_mx_val ,
4311 rsrv_incr_val ,
4312 dist_bdgt_iss_val ,
4313 ws_bdgt_iss_val ,
4314 dist_bdgt_iss_date ,
4315 ws_bdgt_iss_date ,
4316 ws_bdgt_val_last_upd_date ,
4317 dist_bdgt_val_last_upd_date ,
4318 rsrv_val_last_upd_date ,
4319 ws_bdgt_val_last_upd_by ,
4320 dist_bdgt_val_last_upd_by ,
4321 rsrv_val_last_upd_by ,
4322 object_version_number ,
4323 last_update_date ,
4324 last_updated_by ,
4325 last_update_login ,
4326 created_by ,
4327 creation_date
4328 ) values (
4329 l_curr_per_in_ler_id,
4330 g_cache_cpg_rec(l_cpg_count).group_pl_id ,
4331 nvl(g_cache_cpg_rec(l_cpg_count).group_oipl_id, -1) ,
4332 g_cache_cpg_rec(l_cpg_count).lf_evt_ocrd_dt ,
4333 null, -- bdgt_pop_cd
4334 g_cache_cpg_rec(l_cpg_count).due_dt,
4335 g_cache_cpg_rec(l_cpg_count).access_cd,
4336 null, -- approval_cd
4337 null, -- approval_date
4338 null, -- approval_comments
4339 'NS', -- submit_cd
4340 null, -- submit_date
4341 null, -- submit_comments
4342 /*
4343 g_cache_cpg_rec(l_cpg_count).dist_bdgt_val,
4344 g_cache_cpg_rec(l_cpg_count).ws_bdgt_val,
4345 g_cache_cpg_rec(l_cpg_count).rsrv_val,
4346 g_cache_cpg_rec(l_cpg_count).dist_bdgt_mn_val,
4347 g_cache_cpg_rec(l_cpg_count).dist_bdgt_mx_val,
4348 g_cache_cpg_rec(l_cpg_count).dist_bdgt_incr_val,
4349 g_cache_cpg_rec(l_cpg_count).ws_bdgt_mn_val,
4350 g_cache_cpg_rec(l_cpg_count).ws_bdgt_mx_val,
4351 g_cache_cpg_rec(l_cpg_count).ws_bdgt_incr_val,
4352 g_cache_cpg_rec(l_cpg_count).rsrv_mn_val,
4353 g_cache_cpg_rec(l_cpg_count).rsrv_mx_val,
4354 g_cache_cpg_rec(l_cpg_count).rsrv_incr_val,
4355 g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_val,
4356 g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_val,
4357 g_cache_cpg_rec(l_cpg_count).dist_bdgt_iss_date,
4358 g_cache_cpg_rec(l_cpg_count).ws_bdgt_iss_date,
4359 */
4360 -- Bug 3510081 put null values.
4361 null, -- l_copy_dist_bdgt_val,
4362 null, -- l_copy_ws_bdgt_val,
4363 l_copy_dist_bdgt_val,
4364 l_copy_ws_bdgt_val,
4365 l_copy_rsrv_val,
4366 l_copy_dist_bdgt_mn_val,
4367 l_copy_dist_bdgt_mx_val,
4368 l_copy_dist_bdgt_incr_val,
4369 l_copy_ws_bdgt_mn_val,
4370 l_copy_ws_bdgt_mx_val,
4371 l_copy_ws_bdgt_incr_val,
4372 l_copy_rsrv_mn_val,
4373 l_copy_rsrv_mx_val,
4374 l_copy_rsrv_incr_val,
4375 --
4376 -- Bug 3510081 : No need to populate the budget values here.
4377 -- Need to verify whether other values need to be populated or not.
4378 --
4379 null, -- l_copy_dist_bdgt_iss_val,
4380 null, -- l_copy_ws_bdgt_iss_val,
4381 null, -- l_copy_dist_bdgt_iss_date,
4382 null, -- l_copy_ws_bdgt_iss_date,
4383 g_cache_cpg_rec(l_cpg_count).ws_bdgt_val_last_upd_date ,
4384 g_cache_cpg_rec(l_cpg_count).dist_bdgt_val_last_upd_date ,
4385 g_cache_cpg_rec(l_cpg_count).rsrv_val_last_upd_date ,
4386 g_cache_cpg_rec(l_cpg_count).ws_bdgt_val_last_upd_by ,
4387 g_cache_cpg_rec(l_cpg_count).dist_bdgt_val_last_upd_by ,
4388 g_cache_cpg_rec(l_cpg_count).rsrv_val_last_upd_by ,
4389 1, -- object_version_number
4390 -- Check all the column values.
4391 g_cache_cpg_rec(l_cpg_count).last_update_date ,
4392 g_cache_cpg_rec(l_cpg_count).last_updated_by ,
4393 g_cache_cpg_rec(l_cpg_count).last_update_login ,
4394 g_cache_cpg_rec(l_cpg_count).created_by ,
4395 g_cache_cpg_rec(l_cpg_count).creation_date
4396 ) ;
4397 end loop;
4398 end if;
4399 --
4400 if g_debug then
4401 hr_utility.set_location ('Leaving : ' || l_proc,30);
4402 end if;
4403 end popu_missing_person_pil;
4404 --
4405 procedure popu_cwb_group_pil_data (
4406 p_group_per_in_ler_id in number, --9999 We will remove it later
4407 p_group_pl_id in number,
4408 p_group_lf_evt_ocrd_dt in date,
4409 p_group_business_group_id in number,
4410 p_group_ler_id in number,
4411 p_use_eff_dt_flag in varchar2 default 'N',
4412 p_effective_date in date default null) is
4413 --
4414 cursor c_missing_group_pils is
4415 select person_id
4416 from ben_per_in_ler all_pils
4417 where group_pl_id = p_group_pl_id
4418 and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
4419 and per_in_ler_stat_cd = 'STRTD'
4420 union
4421 select ws_mgr_id
4422 from ben_per_in_ler all_pils
4423 where group_pl_id = p_group_pl_id
4424 and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
4425 and per_in_ler_stat_cd = 'STRTD'
4426 and ws_mgr_id is not null
4427 minus
4428 select person_id
4429 from ben_per_in_ler all_pils
4430 where group_pl_id = p_group_pl_id
4431 and lf_evt_ocrd_dt = p_group_lf_evt_ocrd_dt
4432 and per_in_ler_stat_cd in ('STRTD', 'PROCD') -- GSI If a person is already processed but his reportee
4433 -- is processed then data should not be created for manager.
4434 and ler_id = p_group_ler_id;
4435 --
4436 l_exit_loop boolean;
4437 --
4438 l_group_per_in_ler_id number null;
4439 cursor c_null_gpil_cpr(cv_group_pl_id in number,
4440 cv_lf_evt_ocrd_dt in date) is
4441 select cpr.*, cpr.rowid
4442 from ben_cwb_person_rates cpr
4443 where GROUP_PER_IN_LER_ID = -1
4444 and GROUP_PL_ID = cv_group_pl_id
4445 and LF_EVT_OCRD_DT = cv_lf_evt_ocrd_dt;
4446 --
4447 cursor get_gpil_id (cv_person_id in number,
4448 cv_lf_evt_ocrd_dt in date,
4449 cv_group_ler_id in number) is
4450 select per_in_ler_id
4451 from ben_per_in_ler
4452 where person_id = cv_person_id
4453 and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
4454 and ler_id = cv_group_ler_id
4455 and per_in_ler_stat_cd = 'STRTD';
4456 -- and ws_mgr_id is not null;
4457 --
4458 begin
4459 --
4460 if g_debug then
4461 hr_utility.set_location('Entering popu_cwb_group_pil_data', 10);
4462 hr_utility.set_location('= ' ||p_group_per_in_ler_id , 1234);
4463 hr_utility.set_location('= ' ||p_group_pl_id , 1234);
4464 hr_utility.set_location('= ' ||p_group_lf_evt_ocrd_dt , 1234);
4465 hr_utility.set_location('= ' ||p_group_business_group_id , 1234);
4466 hr_utility.set_location('= ' ||p_group_ler_id , 1234);
4467 end if;
4468 /*
4469 popu_missing_pil(p_mode, p_group_pl_business_group_id, p_group_pl_id,
4470 p_group_ler_id, p_effective_date, p_lf_evt_ocrd_dt)
4471 Loop
4472 L_exit_loop = TRUE;
4473 For each person in( ((select person_id from ben_per_in_ler all_pils)
4474 union
4475 (select ws_mgr_id from ben_per_in_ler all_pils)
4476 ) minus
4477 (select person_id from ben_per_in_ler group_plan_pils)
4478 )
4479 Loop
4480 popu_missing_person_pil(p_mode, p_person_id,
4481 p_group_pl_business_group_id,
4482 p_group_pl_id, p_effective_date,
4483 p_lf_evt_ocrd_dt);
4484 L_exit_loop = FALSE;
4485 end for loop;
4486 If L_exit_loop = TRUE then exit;
4487 End Loop;
4488
4489 Logic for procedure which populate the missing data at person level:
4490 popu_missing_person_pil(p_mode, p_person_id, p_group_pl_business_group_id,
4491 p_group_pl_id, p_group_ler_id , p_effective_date,
4492 p_lf_evt_ocrd_dt)
4493 Also need to copy the budget rate from actual plan or option to group plan
4494 or option.
4495 If cached data for one group plan is not available,
4496 cache group plan per in ler data.
4497
4498 Create potential, per in ler for the group_ler_id for the person.
4499 Insert row into ben_cwb_group_hrchy if row already not exists.
4500
4501 Update the group_per_in_ler_id on ben_cwb_person_rates
4502 where group_per_in_ler_id is null and
4503 group_pl_id = p_group_pl_id and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt and
4504 assignment_id = p_assignment_id;
4505
4506 For each of the cached group plan epe data
4507 Create elig_per_elctbl_chc
4508 For each rate attached to group plan epe
4509 If rate is a budget rate if one is defined at actual plan level then val, min, max have to
4510 be moved to cloned rate.
4511 Create enrt_rt if needed or populate the denormalised tables.
4512
4513
4514 */
4515 --
4516 --
4517 -- loop for each person to whom the group per in ler is missing.
4518 --
4519 loop
4520 --
4521 l_exit_loop := TRUE;
4522 --
4523 g_error_log_rec.calling_proc := 'popu_missing_person_pil';
4524 g_error_log_rec.step_number := 21;
4525 --
4526 for l_rec in c_missing_group_pils loop
4527 --
4528 -- Populate group pil and associated data for this person.
4529 --
4530 hr_utility.set_location(' popu_cwb_group_pil_data : per id = ' || l_rec.person_id, 10);
4531 if p_use_eff_dt_flag = 'N' then
4532 --
4533 popu_missing_person_pil(
4534 p_mode => 'ABCD',
4535 p_person_id => l_rec.person_id ,
4536 p_group_per_in_ler_id => 9999, --9999IK Not required confirm Prasad
4537 p_group_pl_id => g_cache_group_plan_rec.group_pl_id,
4538 p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4539 p_group_business_group_id => g_cache_group_plan_rec.group_business_group_id,
4540 p_group_ler_id => g_cache_group_plan_rec.group_ler_id );
4541 --
4542 else
4543 --
4544 popu_missing_person_pil(
4545 p_mode => 'ABCD',
4546 p_person_id => l_rec.person_id ,
4547 p_group_per_in_ler_id => 9999, --9999IK Not required confirm Prasad
4548 p_group_pl_id => g_cache_group_plan_rec.group_pl_id,
4549 p_group_lf_evt_ocrd_dt => g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4550 p_group_business_group_id => g_cache_group_plan_rec.group_business_group_id,
4551 p_group_ler_id => g_cache_group_plan_rec.group_ler_id ,
4552 p_use_eff_dt_flag => p_use_eff_dt_flag,
4553 p_effective_date => p_effective_date
4554 );
4555 --
4556 end if;
4557 --
4558 l_exit_loop := FALSE;
4559 --
4560 end loop;
4561 --
4562 if l_exit_loop = TRUE then
4563 exit;
4564 end if;
4565 --
4566 end loop;
4567 --
4568 -- Now populate the group per in ler id on ben_cwb_person_rates table
4569 -- if it's missing.
4570 --
4571 g_error_log_rec.calling_proc := 'ben_cwb_person_rates';
4572 g_error_log_rec.step_number := 22;
4573 --
4574 l_group_per_in_ler_id := null;
4575 for l_null_gpil_rec in c_null_gpil_cpr(g_cache_group_plan_rec.group_pl_id,
4576 g_cache_group_plan_rec.group_lf_evt_ocrd_dt)
4577 loop
4578 --
4579 open get_gpil_id (l_null_gpil_rec.person_id,
4580 g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
4581 g_cache_group_plan_rec.group_ler_id);
4582 fetch get_gpil_id into l_group_per_in_ler_id;
4583 close get_gpil_id;
4584 --
4585 update ben_cwb_person_rates
4586 set group_per_in_ler_id = l_group_per_in_ler_id
4587 where rowid = l_null_gpil_rec.rowid;
4588 --
4589 end loop;
4590 --
4591 if g_debug then
4592 hr_utility.set_location('Leaving popu_cwb_group_pil_data', 10);
4593 end if;
4594 end popu_cwb_group_pil_data;
4595 --
4596 procedure global_online_process_w
4597 (
4598 p_effective_date in date
4599 ,p_validate in varchar2 default 'N'
4600 ,p_person_id in number default null
4601 -- Business group passed must be person business group.
4602 ,p_business_group_id in number
4603 -- This is group plan id.
4604 ,p_pl_id in number default null
4605 ,p_lf_evt_ocrd_dt in date default null
4606 ,p_clone_all_data_flag in varchar2 default 'N'
4607 ,p_backout_and_process_flag in varchar2 default 'N'
4608 ) is
4609 --
4610 l_proc varchar2(72) := g_package||'global_online_process_w';
4611 --
4612 l_retcode number;
4613 l_errbuf varchar2(1000);
4614 l_encoded_message varchar2(2000);
4615 l_app_short_name varchar2(2000);
4616 l_message_name varchar2(2000);
4617 l_effective_date varchar2(30);
4618 l_lf_evt_ocrd_dt varchar2(30);
4619 l_cache_group_plan_rec_temp g_cache_group_plan_type;
4620 --
4621 begin
4622 --
4623 hr_utility.set_location ('Entering '||l_proc,10);
4624 l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
4625 l_lf_evt_ocrd_dt := to_char(nvl(p_lf_evt_ocrd_dt,p_effective_date),'YYYY/MM/DD HH24:MI:SS');
4626 --
4627 fnd_msg_pub.initialize;
4628 --
4629 -- If backout is requested then first back out and call online.
4630 --
4631 if p_backout_and_process_flag = 'Y' then
4632 --
4633 hr_utility.set_location ('Before : p_backout_global_cwb_event ',10);
4634 ben_cwb_back_out_conc.p_backout_global_cwb_event(
4635 p_effective_date => p_lf_evt_ocrd_dt
4636 ,p_validate => p_validate
4637 ,p_business_group_id => p_business_group_id
4638 ,p_group_pl_id => p_pl_id
4639 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
4640 ,p_person_id => p_person_id
4641 );
4642 --
4643 end if;
4644 --
4645 -- Initilise the globals as this process could be called from
4646 -- the sscwb pages for different person or plans or different dates.
4647 --
4648 g_trk_inelig_flag := null;
4649 g_options_exists := null;
4650 g_cache_group_plan_rec := l_cache_group_plan_rec_temp;
4651 g_cache_cpg_rec.delete;
4652 g_cache_copy_person_bdgt_tbl := g_cache_copy_person_bdgt_tbl1;
4653 ben_manage_cwb_life_events.g_cwb_person_groups_rec := ben_manage_cwb_life_events.g_cwb_person_groups_rec_temp;
4654 ben_manage_cwb_life_events.g_cwb_person_rates_rec := ben_manage_cwb_life_events.g_cwb_person_rates_rec_temp;
4655 --
4656 ben_manage_cwb_life_events.global_process
4657 (Errbuf =>l_errbuf,
4658 retcode =>l_retcode,
4659 p_effective_date =>l_effective_date,
4660 p_validate =>p_validate,
4661 p_person_id =>p_person_id,
4662 p_business_group_id =>p_business_group_id,
4663 p_pl_id =>p_pl_id,
4664 p_lf_evt_ocrd_dt =>l_lf_evt_ocrd_dt,
4665 p_trace_plans_flag =>'Y',
4666 p_online_call_flag =>'Y',
4667 p_clone_all_data_flag => p_clone_all_data_flag);
4668 --
4669 -- If recursive hiearchy is found raise the error.
4670 --
4671 if nvl(g_hrchy_tbl.LAST, 0) > 0 then
4672 --
4673 fnd_message.set_name('BEN', 'BEN_94537_REC_REPORTING');
4674 fnd_message.set_token('TOKEN1', g_hrchy_tbl(1).hrchy_cat_string);
4675 fnd_message.raise_error;
4676 --
4677 end if;
4678 fnd_msg_pub.initialize;
4679 --
4680 exception when app_exception.application_exception then
4681 --
4682 ben_on_line_lf_evt.get_ser_message(
4683 p_encoded_message => l_encoded_message,
4684 p_app_short_name => l_app_short_name,
4685 p_message_name => l_message_name);
4686 --
4687 if (l_message_name like '%BEN_91769_NOONE_TO_PROCESS%') then
4688 fnd_message.set_name('BEN', 'BEN_92540_NOONE_TO_PROCESS_CM');
4689 end if;
4690 --
4691 if (l_message_name = 'BEN_91664_BENMNGLE_NO_OBJECTS') then
4692 l_encoded_message := fnd_message.get_encoded;
4693 else
4694 fnd_msg_pub.add;
4695 end if;
4696 --
4697 when others then
4698 --
4699 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
4700 fnd_message.set_token('2',substr(sqlerrm,1,200));
4701 fnd_msg_pub.add;
4702 --
4703 end global_online_process_w;
4704 --
4705 procedure sum_oipl_rates_and_upd_pl_rate (
4706 p_pl_id in number,
4707 p_group_pl_id in number,
4708 p_lf_evt_ocrd_dt in date,
4709 p_person_id in number,
4710 p_assignment_id in number
4711 ) is
4712 --
4713 cursor c_oipl_exists(cv_lf_evt_ocrd_dt in date,
4714 cv_pl_id in number) is
4715 select oipl.oipl_id
4716 from ben_oipl_f oipl
4717 where oipl.pl_id = cv_pl_id
4718 and oipl.OIPL_STAT_CD = 'A'
4719 and cv_lf_evt_ocrd_dt between oipl.effective_start_date
4720 and oipl.effective_end_date
4721 and exists
4722 (select null
4723 from ben_acty_base_rt_f abr
4724 where abr.pl_id = cv_pl_id
4725 and cv_lf_evt_ocrd_dt between abr.effective_start_date
4726 and abr.effective_end_date
4727 and abr.acty_typ_cd = 'CWBWS' -- 9999
4728 );
4729 --
4730 cursor c_cpr(cv_group_pl_id in number,
4731 cv_pl_id in number,
4732 cv_lf_evt_ocrd_dt in date,
4733 cv_person_id in number) is
4734 select *
4735 from ben_cwb_person_rates
4736 where group_pl_id = cv_group_pl_id
4737 and pl_id = cv_pl_id
4738 and nvl(oipl_id, -1) = -1
4739 and person_id = cv_person_id
4740 and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt;
4741 --
4742 cursor c_cpr_oipl(cv_group_pl_id in number,
4743 cv_pl_id in number,
4744 cv_lf_evt_ocrd_dt in date,
4745 cv_person_id in number) is
4746 select sum(ws_val) ws_val, sum(nvl(ws_val, 0)) ws_val_0_if_null
4747 from ben_cwb_person_rates
4748 where group_pl_id = cv_group_pl_id
4749 and pl_id = cv_pl_id
4750 and nvl(oipl_id, -1) <> -1
4751 and person_id = cv_person_id
4752 and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
4753 and ws_val is not null;
4754 --
4755 l_oipl_ws_sum number;
4756 l_ws_val_0_if_null number;
4757 l_oipl_id number;
4758 --
4759 begin
4760 --
4761 if g_debug then
4762 hr_utility.set_location ('Entering :sum_oipl_rates_and_upd_pl_rate ' ,10);
4763 end if;
4764 --
4765 -- Fetch all ben_cwb_person_rates for the person
4766 -- for the plan, for lf_evt_ocrd_dt if there exists oipl for this plan
4767 --
4768 if g_options_exists is null then
4769 --
4770 open c_oipl_exists(p_lf_evt_ocrd_dt, p_pl_id);
4771 fetch c_oipl_exists into l_oipl_id;
4772 if c_oipl_exists%found then
4773 g_options_exists := true;
4774 else
4775 g_options_exists := false;
4776 end if;
4777 close c_oipl_exists;
4778 --
4779 end if;
4780 --
4781 if g_options_exists then
4782 --
4783 for l_cpr_rec in c_cpr(p_group_pl_id, p_pl_id, p_lf_evt_ocrd_dt,
4784 p_person_id)
4785 loop
4786 --
4787 open c_cpr_oipl(p_group_pl_id, p_pl_id, p_lf_evt_ocrd_dt,
4788 p_person_id);
4789 fetch c_cpr_oipl into l_oipl_ws_sum, l_ws_val_0_if_null;
4790 if c_cpr_oipl%found then
4791 --
4792 if (l_oipl_ws_sum is not null and l_ws_val_0_if_null is not null)
4793 OR (l_oipl_ws_sum is null and l_ws_val_0_if_null <> 0)
4794 then
4795 --
4796 -- Update plan level cpr.
4797 --
4798 update ben_cwb_person_rates
4799 set ws_val = l_oipl_ws_sum
4800 where person_rate_id = l_cpr_rec.person_rate_id;
4801 --
4802 end if;
4803 end if;
4804 close c_cpr_oipl;
4805 --
4806 end loop;
4807 --
4808 end if;
4809 if g_debug then
4810 hr_utility.set_location ('Leaving :sum_oipl_rates_and_upd_pl_rate ' ,10);
4811 end if;
4812 --
4813 end sum_oipl_rates_and_upd_pl_rate;
4814 --
4815 --
4816 -- Procedure to populate the auto allocation of budgets.
4817 --
4818 procedure auto_allocate_budgets (
4819 p_pl_id in number default null,
4820 p_group_pl_id in number,
4821 p_lf_evt_ocrd_dt in date,
4822 p_person_id in number default null,
4823 p_assignment_id in number default null
4824 ) is
4825 --
4826 cursor c_high_mgr_cpg(c_group_pl_id number, c_lf_evt_ocrd_dt date) is
4827 select cpg.rowid, cpg.*
4828 from ben_cwb_person_groups cpg
4829 where cpg.group_pl_id = c_group_pl_id
4830 and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4831 and exists
4832 (select null
4833 from ben_cwb_person_rates cpr,
4834 ben_cwb_group_hrchy cgh
4835 where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
4836 and cpr.ELIG_FLAG = 'Y'
4837 and cgh.LVL_NUM > 1
4838 and cgh.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
4839 and cpg.dist_bdgt_val_last_upd_date is null
4840 and cpg.ws_bdgt_val_last_upd_date is null
4841 for update;
4842 --
4843 cursor c_leaf_mgr_cpg(c_group_pl_id number, c_lf_evt_ocrd_dt date) is
4844 select cpg.rowid, cpg.*
4845 from ben_cwb_person_groups cpg
4846 where cpg.group_pl_id = c_group_pl_id
4847 and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4848 and not exists
4849 (select null
4850 from ben_cwb_person_rates cpr1,
4851 ben_cwb_group_hrchy cgh1
4852 where cgh1.emp_per_in_ler_id = cpr1.GROUP_PER_IN_LER_ID
4853 and cpr1.ELIG_FLAG = 'Y'
4854 and cgh1.LVL_NUM > 1
4855 and cgh1.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
4856 and exists
4857 (select null
4858 from ben_cwb_person_rates cpr,
4859 ben_cwb_group_hrchy cgh
4860 where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
4861 and cpr.ELIG_FLAG = 'Y'
4862 and cgh.LVL_NUM = 1
4863 and cgh.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
4864 and cpg.ws_bdgt_val_last_upd_date is null
4865 and cpg.dist_bdgt_val_last_upd_date is null
4866 for update ;
4867 --
4868 l_dflt_dist_bdgt_val number;
4869 l_dflt_ws_bdgt_val number;
4870 l_iss_dt date;
4871 l_pop_cd varchar2(30);
4872 --
4873 begin
4874 if g_debug then
4875 hr_utility.set_location ('Entering :auto_allocate_budgets ' ,10);
4876 end if;
4877 --
4878 for l_high_mgr_cpg_rec in c_high_mgr_cpg(p_group_pl_id , p_lf_evt_ocrd_dt )
4879 loop
4880 --
4881 -- if auto alloc flag is Y update dist_bdgt_iss_val, ws_bdgt_iss_val
4882 -- dist_bdgt_iss_date, ws_bdgt_iss_date
4883 --
4884 --
4885 l_pop_cd := null;
4886 --
4887 if g_cache_group_plan_rec.auto_distr_flag = 'Y' then
4888 --
4889 l_dflt_dist_bdgt_val := l_high_mgr_cpg_rec.dflt_dist_bdgt_val;
4890 l_dflt_ws_bdgt_val := l_high_mgr_cpg_rec.dflt_ws_bdgt_val;
4891 --
4892 if nvl(l_high_mgr_cpg_rec.group_oipl_id , -1) = -1 then
4893 l_iss_dt := p_lf_evt_ocrd_dt;
4894 -- If components are configured then populate pop_cd at plan level.
4895 l_pop_cd := 'D';
4896 else
4897 l_iss_dt := null;
4898 end if;
4899 else
4900 --
4901 l_dflt_dist_bdgt_val := null;
4902 l_dflt_ws_bdgt_val := null;
4903 l_iss_dt := null;
4904 --
4905 end if;
4906 --
4907 update ben_cwb_person_groups
4908 set dist_bdgt_val = dflt_dist_bdgt_val,
4909 ws_bdgt_val = dflt_ws_bdgt_val,
4910 ws_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
4911 dist_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
4912 bdgt_pop_cd = l_pop_cd,
4913 dist_bdgt_iss_val = l_dflt_dist_bdgt_val,
4914 ws_bdgt_iss_val = l_dflt_ws_bdgt_val,
4915 dist_bdgt_iss_date = l_iss_dt,
4916 ws_bdgt_iss_date = l_iss_dt
4917 where rowid = l_high_mgr_cpg_rec.rowid;
4918 --
4919 end loop;
4920 --
4921 for l_leaf_mgr_cpg_rec in c_leaf_mgr_cpg(p_group_pl_id , p_lf_evt_ocrd_dt ) loop
4922 --
4923 --
4924 -- if auto alloc flag is Y update dist_bdgt_iss_val, ws_bdgt_iss_val
4925 -- dist_bdgt_iss_date, ws_bdgt_iss_date
4926 --
4927 -- If components are configured then populate pop_cd at plan level.
4928 -- Leaf managers do not need pop cd at all.
4929 --
4930 l_pop_cd := null;
4931 --
4932 if g_cache_group_plan_rec.auto_distr_flag = 'Y' then
4933 --
4934 l_dflt_ws_bdgt_val := l_leaf_mgr_cpg_rec.dflt_ws_bdgt_val;
4935 --
4936 if nvl(l_leaf_mgr_cpg_rec.group_oipl_id, -1) = -1 then
4937 -- It's a plan
4938 l_iss_dt := p_lf_evt_ocrd_dt;
4939 else
4940 l_iss_dt := null;
4941 end if;
4942 else
4943 --
4944 l_dflt_ws_bdgt_val := null;
4945 l_iss_dt := null;
4946 --
4947 end if;
4948 --
4949 update ben_cwb_person_groups
4950 set ws_bdgt_val = dflt_ws_bdgt_val,
4951 ws_bdgt_val_last_upd_date = p_lf_evt_ocrd_dt,
4952 ws_bdgt_iss_val = l_dflt_ws_bdgt_val,
4953 ws_bdgt_iss_date = l_iss_dt,
4954 bdgt_pop_cd = l_pop_cd
4955 where rowid = l_leaf_mgr_cpg_rec.rowid;
4956 --
4957 end loop;
4958 --
4959 if g_debug then
4960 hr_utility.set_location ('Leaving :auto_allocate_budgets ' ,10);
4961 end if;
4962 --
4963 end auto_allocate_budgets;
4964 --
4965 end BEN_MANAGE_CWB_LIFE_EVENTS;