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