[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_BACK_OUT_CONC
Source
1 package body ben_cwb_back_out_conc as
2 /* $Header: bencwbbo.pkb 120.7.12000000.3 2007/02/09 00:37:05 maagrawa noship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Back-out Compensation Life Events Concurrent Manager Processes
7 *
8 * Purpose
9 * This is a new package added to backout data created by the CWB global
10 * budget.
11 * This package houses the procedure which would be called from
12 * the concurrent manager.
13 *
14 * History
15 * Date Who Version What?
16 * --------- --------- ------- --------------------------------------
17 * 16-Jan-04 rpgupta 115.0 Created
18 09-Feb-04 nhunur 115.1 commented business_group_id clause
19 16-Feb-04 nhunur 115.2 commented business_group_id,ler_id clause
20 20-feb-04 nhunur 115.3 removed latest check call
21 03-Mar-04 rpgupta 115.4 1. Commented delete dmls and added calls to api's
22 19-Mar-04 pbodla 115.5 Bug 3517726 : CWB data is not getting deleted.
23 23-Mar-04 nhunur 115.6 removed if clause before cwb delete,added distinct
24 clause for person rates cursor.
25 25-Mar-04 rpgupta 115.7 Changed logic for person selection to work like
26 participation process.
27 26-Mar-04 pbodla 115.8 Added code to delete
28 BEN_CWB_PERSON_INFO,
29 BEN_CWB_SUMMARY,
30 ben_cwb_pl_dsgn
31 26-Mar-04 pbodla 115.9 l_ocrd_date need to be passed to
32 BEN_CWB_PL_DSGN_PKG.delete_pl_dsgn
33 27-Apr-04 rpgupta 115.13 bug 3517726 - elete records in ben_cwb_person_rates
34 with the given group_pl_id, ler_id and life event
35 ocrd date. Sometimes when one thread of benmngle
36 fails, theres a possibility that a few records in
37 person_rates exist with group_per_in_ler_id as -1
38 27-Apr-04 rpgupta 115.14/15 Added online backout procedure/trace calls.
39 01-Feb-2005 steotia 115.16 cwb_delete_routine and ben_cwb_person_info API
40 used to delete person_info record
41 11-Feb-2005 pbodla 115.17 Bug 4021004 : when group per in ler
42 is backed out it is not backing out the
43 heirarchy data and not resetting
44 heirarchy data for reporting employees
45 linked to this PIL.
46 23-Feb-2005 pbodla 115.18 4109090 : Removed the coun(*) statements which
47 are causing performance problems.
48 28-Feb-2005 nhunur 115.19 added code to close cursors in all conditions
49 13-apr-2005 nhunur 115.20 bug 4300599 - added code to handle person sel rule exceptions.
50 23-sep-2005 pbodla 115.21 bug 4598824 - Romove element entry if
51 life event backed out.
52 Added get_ele_dt_del_mode, backout_cwb_element
53 12-oct-2005 pbodla 115.22 bug 4653929 - Backout is not deleting
54 cwb plan design data as some life events
55 are not getting deleted : some cases
56 are - person hired and processed after
57 the life event occured date.
58 - As heirarchy data is in contention
59 it should be moved out of multi thread
60 30-Nov-2005 pbodla 115.23 Bug 4758468 : join condition is missing
61 in c_group_pils.
62 08-Mar-2006 stee 115.24 Bug 5060080 : Fix cursor to not delete
63 all the person rates for a plan and
64 life event occurred date. If there
65 is more than 1 life event for the
66 person, the person rates are also
67 deleted and the element entries are
68 not backed out.
69 06-Apr-2006 abparekh 115.25 Bug 5130397 : When CWB plan has options attached,
70 then while backing out BEN_CWB_PERSON_RATES rows
71 delete pay proposal only once
72 25-May-2006 ikasired 115.26 Bug 5240208 fix for heirarchy issue for
73 reassign, backout and reprocess issue
74 26-May-2006 maagrawa 115.27 Always run summary refresh at end
75 when running backout in batch.
76 In online mode, call delete apis
77 with update_summary ON.
78 21-aug-06 nhunur 115.28 Report any broken hierarchies, people who do not have
79 a level 1 manager but have a worksheet manager id
80 20-sep-06 nhunur 115.29 Reformat the list of people and change message.
81 06-Feb-07 maagrawa 115.30 When more than 1 person_rates record
82 exists for a group_per_in_ler_id,
83 pl_id, oipl_id combination, you get
84 a error when calling delete api for
85 2nd record.
86 * -----------------------------------------------------------------------------
87 */
88
89 /* global variables */
90 g_package varchar2(80) := 'ben_cwb_back_out_conc';
91 g_persons_processed number(9) := 0;
92 g_persons_ended number(9) := 0;
93 g_persons_passed number(9) := 0;
94 g_persons_errored number(9) := 0;
95 g_max_errors_allowed number(9) := 200;
96 g_rec ben_type.g_report_rec;
97 g_debug boolean := hr_utility.debug_enabled;
98 --
99 /*
100 procedure backout_heirarchy_data
101 (p_per_in_ler_id in number
102 ) is
103 cursor c_chr is
104 select rowid
105 from ben_cwb_group_hrchy
106 where mgr_per_in_ler_id = p_per_in_ler_id;
107 --
108 l_proc varchar2(50) := g_package||'.cwb_delete_routine';
109 begin
110 --
111 hr_utility.set_location( 'Entering '|| l_proc, 5);
112 --
113 -- Delete heirarchy data : bug 4021004
114 --
115 delete from ben_cwb_group_hrchy hrc
116 where emp_per_in_ler_id = p_per_in_ler_id;
117 --
118 -- Now delete all the data where this per in ler id is manager
119 --
120 for l_chr in c_chr loop
121 begin
122 update ben_cwb_group_hrchy set
123 mgr_per_in_ler_id = -1 ,
124 LVL_NUM = -1
125 where rowid = l_chr.rowid;
126 exception
127 when others then
128 null;
129 end;
130 end loop;
131 --
132 hr_utility.set_location( 'Leaving '||l_proc, 50);
133 --
134 end backout_heirarchy_data;
135 */
136 --
137 --
138 procedure backout_heirarchy_data
139 (p_per_in_ler_id in number
140 ) is
141 cursor c_chr is
142 select rowid, emp_per_in_ler_id
143 from ben_cwb_group_hrchy
144 where mgr_per_in_ler_id = p_per_in_ler_id;
145 --
146 l_proc varchar2(50) := g_package||'.cwb_delete_routine';
147 begin
148 --
149 hr_utility.set_location( 'Entering '|| l_proc, 5);
150 --
151 -- Delete heirarchy data : bug 4021004
152 --
153 delete from ben_cwb_group_hrchy hrc
154 where emp_per_in_ler_id = p_per_in_ler_id;
155 --
156 -- Now delete all the data where this per in ler id is manager
157 --
158 for l_chr in c_chr loop
159 begin
160 update ben_cwb_group_hrchy set
161 mgr_per_in_ler_id = -1 ,
162 LVL_NUM = -1
163 where rowid = l_chr.rowid;
164 --
165 delete from ben_cwb_group_hrchy
166 where emp_per_in_ler_id = l_chr.emp_per_in_ler_id
167 and LVL_NUM > -1;
168 --
169 exception
170 when others then
171 null;
172 end;
173 end loop;
174 --
175 hr_utility.set_location( 'Leaving '||l_proc, 50);
176 --
177 end backout_heirarchy_data;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |-------------------------< get_ele_dt_del_mode>---------------------------|
181 -- ----------------------------------------------------------------------------
182 --
183 function get_ele_dt_del_mode
184 (p_effective_date in date,
185 p_base_key_value in number)
186 return varchar2 is
187
188 l_zap_mode boolean;
189 l_delete_mode boolean;
190 l_future_change_mode boolean;
191 l_delete_next_change_mode boolean;
192 l_del_mode varchar2(30);
193 l_zap_start_date date;
194 l_zap_end_date date;
195 l_delete_start_date date;
196 l_delete_end_date date;
197 l_del_future_start_date date;
198 l_del_future_end_date date;
199 l_del_next_start_date date;
200 l_del_next_end_date date;
201 --
202 begin
203
204 dt_api.find_dt_del_modes -- _and_dates
205 (p_effective_date => p_effective_date,
206 p_base_table_name => 'PAY_ELEMENT_ENTRIES_F',
207 p_base_key_column => 'ELEMENT_ENTRY_ID',
208 p_base_key_value => p_base_key_value,
209 p_zap => l_zap_mode,
210 p_delete => l_delete_mode,
211 p_future_change => l_future_change_mode,
212 p_delete_next_change => l_delete_next_change_mode); /*,
213 p_zap_start_date => l_zap_start_date,
214 p_zap_end_date => l_zap_end_date,
215 p_delete_start_date => l_delete_start_date,
216 p_delete_end_date => l_delete_end_date,
217 p_del_future_start_date => l_del_future_start_date,
218 p_del_future_end_date => l_del_future_end_date,
219 p_del_next_start_date => l_del_next_start_date,
220 p_del_next_end_date => l_del_next_end_date);*/
221 --
222 hr_utility.set_location('l_zap_start_date = ' || l_zap_start_date, 12);
223 hr_utility.set_location('l_zap_end_date = ' || l_zap_end_date, 12);
224 hr_utility.set_location('l_delete_start_date = ' || l_delete_start_date, 12);
225 hr_utility.set_location('l_delete_end_date = ' || l_delete_end_date, 12);
226 hr_utility.set_location('l_del_future_start_date = ' || l_del_future_start_date, 12);
227 hr_utility.set_location('l_del_future_end_date = ' || l_del_future_end_date, 12);
228 hr_utility.set_location('l_del_next_start_date = ' || l_del_next_start_date, 12);
229 hr_utility.set_location('l_del_next_end_date = ' || l_del_next_end_date, 12);
230
231 if l_zap_mode then
232 hr_utility.set_location('l_zap true', 13);
233 end if;
234 if l_delete_mode then
235 hr_utility.set_location('l_delete_mode true', 13);
236 end if;
237 if l_future_change_mode then
238 hr_utility.set_location('l_future_change_mode true', 13);
239 end if;
240 if l_delete_next_change_mode then
241 hr_utility.set_location('l_delete_next_change_mode true', 13);
242 end if;
243 if l_delete_next_change_mode = true or l_future_change_mode = true then
244 l_del_mode := hr_api.g_future_change;
245 else
246 l_del_mode := hr_api.g_zap;
247 end if;
248 --
249 return l_del_mode;
250
251 end get_ele_dt_del_mode;
252
253 -- ----------------------------------------------------------------------------
254 -- |----------------------< backout_cwb_element >--------------------------|
255 -- ----------------------------------------------------------------------------
256 --
257 procedure backout_cwb_element(
258 p_validate IN BOOLEAN
259 ,p_element_entry_value_id in number
260 ,p_business_group_id IN NUMBER
261 ,p_person_id IN NUMBER
262 ,p_acty_ref_perd in varchar2 default null
263 ,p_acty_base_rt_id in number default null
264 ,p_element_link_id IN NUMBER default null
265 ,p_rt_end_date IN DATE default null
266 ,p_effective_date IN DATE default null
267 ,p_dt_delete_mode IN VARCHAR2 default null
268 ,p_amt in number default null ) is
269 --
270 l_proc VARCHAR2(72) := 'backout_cwb_element';
271 l_element_link_id number;
272 l_element_type_id number;
273 l_input_value_id NUMBER;
274 l_element_name varchar2(80);
275 l_processing_type varchar2(30);
276 l_assignment_id NUMBER;
277 l_payroll_id NUMBER;
278 l_element_entry_id NUMBER;
279 l_element_entry_start_date date;
280 l_element_entry_end_date date;
281 l_object_version_number NUMBER;
282 l_original_entry_id number;
283 l_entry_type varchar2(30);
284 l_curr_val_char varchar2 (60);
285 l_delete_warning BOOLEAN;
286 l_dt_delete_mode varchar2(80);
287 l_effective_start_date DATE;
288 l_effective_end_date DATE;
289 l_effective_date date;
290 l_string varchar2(4000);
291 L_ELEMENT_ENTRY_VALUE_ID NUMBER;
292 --
293 cursor c_min_max_dt(p_element_entry_id number) is
294 select min(effective_start_date),
295 max(effective_end_date)
296 from pay_element_entries_f
297 where element_entry_id = p_element_entry_id;
298 --
299 l_min_start_date date;
300 l_max_end_date date;
301 --
302 cursor c_ele_info(p_element_entry_value_id number) is
303 select pel.element_link_id,
304 pel.element_type_id,
305 pev.input_value_id,
306 pet.element_name,
307 pet.processing_type
308 from pay_element_types_f pet,
309 pay_element_links_f pel,
310 pay_element_entries_f pee,
311 pay_element_entry_values_f pev
312 where pev.element_entry_value_id = p_element_entry_value_id
313 and pee.element_entry_id = pev.element_entry_id
314 and pev.effective_start_date between pee.effective_start_date
315 and pee.effective_end_date
316 and pel.element_link_id = pee.element_link_id
317 and pee.effective_start_date between pel.effective_start_date
318 and pel.effective_end_date
319 and pet.element_type_id = pel.element_type_id
320 and pel.effective_start_date between pet.effective_start_date
321 and pet.effective_end_date;
322 --
323 cursor get_element_entry_id (p_element_type_id in number
324 ,p_input_value_id in number
325 ,p_element_entry_value_id in number
326 ,p_effective_date in date) is
327 select asg.assignment_id,
328 asg.payroll_id,
329 pee.element_entry_id,
330 pee.effective_start_date,
331 pee.effective_end_date,
332 pee.object_version_number,
333 pee.original_entry_id,
334 pee.entry_type,
335 pee.element_link_id,
336 pev.screen_entry_value
337 from per_all_assignments_f asg,
338 pay_element_links_f pel,
339 pay_element_entries_f pee,
340 pay_element_entry_values_f pev
341 where asg.person_id = p_person_id
342 and pee.assignment_id = asg.assignment_id
343 and p_effective_date between asg.effective_start_date
344 and asg.effective_end_date
345 and pee.creator_type = 'F'
346 and pee.entry_type = 'E'
347 and p_effective_date <= pee.effective_end_date
348 and pel.element_link_id = pee.element_link_id
349 and pee.effective_start_date between pel.effective_start_date
350 and pel.effective_end_date
351 and pel.element_type_id = p_element_type_id
352 and pev.element_entry_id = pee.element_entry_id
353 and pev.input_value_id = p_input_value_id
354 and (p_element_entry_value_id is null or
355 pev.element_entry_value_id = p_element_entry_value_id)
356 and pev.effective_start_date between pee.effective_start_date
357 and pee.effective_end_date
358 order by pee.effective_start_date ;
359 --
360 begin
361 --
362 g_debug := hr_utility.debug_enabled;
363 if g_debug then
364 hr_utility.set_location('Entering :'||l_proc,5);
365 hr_utility.set_location('Element_link_id='||to_char(p_element_link_id),6);
366 hr_utility.set_location('p_element_entry_value_id='||to_char(p_element_entry_value_id),6);
367 hr_utility.set_location('Effective_date='||to_char(p_effective_date),6);
368 hr_utility.set_location('p_rt_end_date='||to_char(p_rt_end_date),6);
369 end if;
370 --
371 -- After discussing with CWB team decide to go with simple approach.
372 -- Issues to consider
373 -- Can elements be recurring. CWB team - not
374 -- if element type is attached in cwb pl design table, can backout work ? YES
375 -- Ignore proration etc., YES
376 -- Element entry is shared by mutliple cwb entries ? NO
377 --
378 -- Assumptions.
379 -- ben_cwb_person_rates.ELEMENT_ENTRY_VALUE_ID will be passed this routine.
380 -- Element type from ben_cwb_plan_design overrides from abr.
381 -- Abr information have to be fetched similar to cwb post process.
382 --
383 --
384 -- if no element entry was created to start with, return
385 --
386 if p_element_entry_value_id is null then
387 hr_utility.set_location('no element entry '||l_proc,7);
388 hr_utility.set_location('Leaving: '||l_proc,7);
389 return;
390 end if;
391 --
392 -- find the element type and input value based on element_entry_value_id
393 -- attached to prtt rt.
394 --
395 open c_ele_info(p_element_entry_value_id);
396 fetch c_ele_info into
397 l_element_link_id,
398 l_element_type_id,
399 l_input_value_id,
400 l_element_name,
401 l_processing_type;
402 --
403 if c_ele_info%notfound then
404 close c_ele_info;
405 if g_debug then
406 --
407 -- entry_value_id attached to prtt rt does not exist. This is possible
408 -- prior to FP C when ct. could delete the entries
409 --
410 hr_utility.set_location('Leaving: '||l_proc,7);
411 end if;
412 return;
413 end if;
414 close c_ele_info;
415 --
416 l_effective_date := p_effective_date;
417 --
418 if g_debug then
419 hr_utility.set_location('ele type='||l_element_type_id,7);
420 hr_utility.set_location('inp val='||l_input_value_id,7);
421 hr_utility.set_location('l_effective_date='||l_effective_date,7);
422 end if;
423 --
424 -- find the element entry that needs to be deleted.
425 --
426 open get_element_entry_id(-- p_enrt_rslt_id
427 l_element_type_id
428 ,l_input_value_id
429 ,l_element_entry_value_id
430 ,l_effective_date);
431 fetch get_element_entry_id into
432 l_assignment_id,
433 l_payroll_id,
434 l_element_entry_id,
435 l_element_entry_start_date,
436 l_element_entry_end_date,
437 l_object_version_number,
438 l_original_entry_id,
439 l_entry_type,
440 l_element_link_id,
441 l_curr_val_char;
442 --
443 if get_element_entry_id%notfound then
444 close get_element_entry_id;
445 if g_debug then
446 -- element entry already ended.
447 hr_utility.set_location('element entry already ended',8);
448 hr_utility.set_location('Leaving: '||l_proc,7);
449 end if;
450
451 -- 9999 is it needed.
452 ben_warnings.load_warning
453 (p_application_short_name => 'BEN',
454 p_message_name => 'BEN_93455_ELE_ALREADY_ENDED',
455 p_parma => l_element_name,
456 p_parmb => to_char(l_effective_date),
457 p_person_id => p_person_id);
458 --
459 if fnd_global.conc_request_id in ( 0,-1) then
460 --
461 fnd_message.set_name('BEN','BEN_93455_ELE_ALREADY_ENDED');
462 fnd_message.set_token('PARMA',l_element_name);
463 fnd_message.set_token('PARMB',to_char(l_effective_date));
464 l_string := fnd_message.get;
465 benutils.write(p_text => l_string);
466 --
467 end if;
468 --
469 if g_debug then
470 --
471 -- Could delete the entries
472 --
473 hr_utility.set_location('Leaving: '||l_proc,8);
474 --
475 end if;
476 --
477 return;
478 --
479 end if;
480 --
481 -- Check if element is already processed in payroll, then make a
482 -- quickpay entries. -- 9999
483 --
484 -- Add the function 9999
485 l_dt_delete_mode := get_ele_dt_del_mode(p_effective_date, l_element_entry_id);
486 --
487 -- get the min effective_start date also.
488 --
489 open c_min_max_dt(l_element_entry_id);
490 fetch c_min_max_dt into l_min_start_date,l_max_end_date;
491 close c_min_max_dt;
492 --
493 if l_processing_type <> 'R' or p_effective_date < l_min_start_date then
494 l_dt_delete_mode := hr_api.g_zap;
495 else
496 if p_effective_date = l_min_start_date then
497 l_dt_delete_mode := hr_api.g_zap;
498 else
499 l_effective_date := p_effective_date -1;
500 end if;
501 end if;
502 --
503
504 hr_utility.set_location('l_dt_delete_mode = ' || l_dt_delete_mode, 9);
505 hr_utility.set_location('l_element_entry_id = ' || l_element_entry_id, 9);
506 hr_utility.set_location('l_processing_type = ' || l_processing_type, 9);
507 --
508 -- If procesing type id Non Recussring then zap the element entry.
509 -- If it is recurring then check whether the min effective_start date
510 -- less than the p_effective date, if so then do a future change, otherwise
511 -- zap it.
512 --
513 py_element_entry_api.delete_element_entry
514 (p_validate => p_validate
515 ,p_datetrack_delete_mode => l_dt_delete_mode
516 ,p_effective_date => l_effective_date
517 ,p_element_entry_id => l_element_entry_id
518 ,p_object_version_number => l_object_version_number
519 ,p_effective_start_date => l_effective_start_date
520 ,p_effective_end_date => l_effective_end_date
521 ,p_delete_warning => l_delete_warning);
522 --
523 if g_debug then
524 hr_utility.set_location('Leaving :'||l_proc,5);
525 end if;
526 --
527 end backout_cwb_element;
528 --
529 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
530 -- << Procedure: Restart >>
531 -- *****************************************************************
532 --
533 procedure restart (errbuf out nocopy varchar2
534 ,retcode out nocopy number
535 ,p_benefit_action_id in number) is
536 --
537 -- Cursor Declaration
538 --
539 cursor c_parameters is
540 Select process_date
541 ,mode_cd
542 ,validate_flag
543 ,business_group_id
544 ,person_selection_rl
545 ,ler_id
546 ,debug_messages_flag
547 ,date_from
548 ,ptnl_ler_for_per_stat_cd
549 ,pl_id
550 From ben_benefit_actions ben
551 Where ben.benefit_action_id = p_benefit_action_id;
552 --
553 -- Local Variable declaration.
554 --
555 l_proc varchar2(80) := g_package||'.restart';
556 l_parameters c_parameters%rowtype;
557 l_errbuf varchar2(80);
558 l_retcode number;
559 --
560 Begin
561 --
562 hr_utility.set_location ('Entering '||l_proc,10);
563 --
564 -- get the parameters for a previous run and do a restart
565 --
566 open c_parameters;
567 --
568 fetch c_parameters into l_parameters;
569 If c_parameters%notfound then
570 --
571 close c_parameters;
572 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
573 fnd_message.raise_error;
574 --
575 End if;
576 --
577 close c_parameters;
578 --
579 -- Call process procedure with parameters for restart
580 --
581 process(errbuf => l_errbuf
582 ,retcode => l_retcode
583 ,p_benefit_action_id => p_benefit_action_id
584 ,p_effective_date => fnd_date.date_to_canonical
585 (l_parameters.process_date)
586 ,p_validate => l_parameters.validate_flag
587 ,p_business_group_id => l_parameters.business_group_id
588 ,p_life_event_id => l_parameters.ler_id
589 ,p_ocrd_date => fnd_date.date_to_canonical
590 (l_parameters.date_from)
591 ,p_group_pl_id => l_parameters.pl_id
592 ,p_person_selection_rule_id => l_parameters.person_selection_rl
593 ,p_debug_messages => l_parameters.debug_messages_flag);
594 --
595 hr_utility.set_location ('Leaving '||l_proc,70);
596 --
597 end restart;
598 --
599
600
601 -- ============================================================================
602 -- << Procedure: Do_Multithread >>
603 -- Description:
604 -- this procedure is called from 'process'. It calls the back-out routine.
605 -- ============================================================================
606 procedure do_multithread
607 (errbuf out nocopy varchar2
608 ,retcode out nocopy number
609 ,p_validate in varchar2 default 'N'
610 ,p_benefit_action_id in number
611 ,p_thread_id in number
612 ,p_effective_date in varchar2
613 ,p_business_group_id in number
614 ,p_ocrd_date in varchar2
615 ,p_group_pl_id in number
616 ,p_life_event_id in number
617 ,p_bckt_stat_cd in varchar2
618 ) is
619 -- Local variable declaration
620 --
621 l_proc varchar2(80) := g_package||'.do_multithread';
622 l_person_id ben_person_actions.person_id%type;
623 l_person_action_id ben_person_actions.person_action_id%type;
624 l_object_version_number ben_person_actions.object_version_number%type;
625 l_ler_id ben_person_actions.ler_id%type;
626 l_range_id ben_batch_ranges.range_id%type;
627 l_record_number number := 0;
628 l_start_person_action_id number := 0;
629 l_end_person_action_id number := 0;
630 l_actn varchar2(80);
631 l_cnt number(5):= 0;
632 l_chunk_size number(15);
633 l_threads number(15);
634 l_effective_date date;
635 l_ocrd_date date;
636 l_commit number;
637 l_per_rec per_all_people_f%rowtype;
638 l_dummy2 number;
639 -- l_per_dummy_rec per_all_people_f%rowtype;
640
641 -- Cursors declaration
642 --
643 Cursor c_range_thread is
644 Select ran.range_id
645 ,ran.starting_person_action_id
646 ,ran.ending_person_action_id
647 From ben_batch_ranges ran
648 Where ran.range_status_cd = 'U'
649 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
650 And rownum < 2
651 For update of ran.range_status_cd;
652 --
653 cursor c_person_thread is
654 select ben.person_id,
655 ben.person_action_id
656 from ben_person_actions ben
657 where ben.benefit_action_id = p_benefit_action_id
658 and ben.action_status_cd not in ('P','E')
659 and ben.person_action_id
660 between l_start_person_action_id
661 and l_end_person_action_id
662 order by ben.person_action_id;
663 --
664 cursor c_ler_thread is
665 select pil.per_in_ler_id,
666 pil.person_id,
667 pil.per_in_ler_stat_cd,
668 pil.lf_evt_ocrd_dt,
669 pil.business_group_id,
670 ler.typ_cd,
671 ler.ler_id
672 from ben_per_in_ler pil,
673 ben_ler_f ler
674 where pil.person_id = l_person_id
675 and pil.lf_evt_ocrd_dt = l_ocrd_date
676 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
677 and pil.group_pl_id = p_group_pl_id -- CWBGLOBAL
678 and ler.ler_id = pil.ler_id
679 and ler.typ_cd = 'COMP' -- CWBGLOBAL
680 and nvl(l_effective_date,trunc(sysdate))
681 between ler.effective_start_date
682 and ler.effective_end_date
683 order by pil.person_id desc;
684 --
685 l_ler_thread c_ler_thread%rowtype;
686 --
687 Cursor c_parameter is
688 Select *
689 From ben_benefit_actions ben
690 Where ben.benefit_action_id = p_benefit_action_id;
691 --
692 l_parm c_parameter%rowtype;
693 --
694 --
695 cursor c_latest_ler_cwb is
696 select pil.per_in_ler_id,
697 ler.name
698 from ben_per_in_ler pil,
699 ben_ler_f ler
700 where pil.person_id = l_person_id
701 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
702 and pil.ler_id = ler.ler_id
703 and ler.typ_cd = 'COMP'
704 and nvl(l_effective_date,trunc(sysdate))
705 between ler.effective_start_date
706 and ler.effective_end_date
707 order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
708 --
709 l_latest_ler_cwb c_latest_ler_cwb%rowtype;
710 --
711 cursor c_person is
712 select ppf.*
713 from per_all_people_f ppf
714 where ppf.person_id = l_person_id
715 and nvl(l_effective_date,trunc(sysdate))
716 between ppf.effective_start_date
717 and ppf.effective_end_date;
718 --
719 cursor c_person_last is
720 select ppf.*
721 from per_all_people_f ppf
722 where ppf.person_id = l_person_id
723 order by effective_start_date desc;
724 --
725 Begin
726 --
727 hr_utility.set_location ('Entering '||l_proc,10);
728 --
729 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
730 fnd_message.set_token('PROC','dt_fndate.change_ses_date');
731 dt_fndate.change_ses_date
732 (p_ses_date => l_effective_date,
733 p_commit => l_commit);
734 --
735 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
736 l_ocrd_date := trunc(fnd_date.canonical_to_date(p_ocrd_date));
737 --
738 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
739 fnd_message.set_token('PROC','benutils.get_parameter');
740 benutils.get_parameter(p_business_group_id => p_business_group_id
741 ,p_batch_exe_cd => 'BENBOCON'
742 ,p_threads => l_threads
743 ,p_chunk_size => l_chunk_size
744 ,p_max_errors => g_max_errors_allowed);
745 --
746 hr_utility.set_location ('l_threads '||l_threads,10);
747 hr_utility.set_location ('l_chunk_size '||l_chunk_size,10);
748 --
749 --
750 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
751 fnd_message.set_token('PROC','ben_env_object.init');
752 ben_env_object.init(p_business_group_id => p_business_group_id,
753 p_effective_date => l_effective_date,
754 p_thread_id => p_thread_id,
755 p_chunk_size => l_chunk_size,
756 p_threads => l_threads,
757 p_max_errors => g_max_errors_allowed,
758 p_benefit_action_id => p_benefit_action_id);
759 --
760 -- Copy benefit action id to global in benutils package
761 --
762 benutils.g_benefit_action_id := p_benefit_action_id;
763 benutils.g_thread_id := p_thread_id;
764 g_persons_errored := 0;
765 g_persons_processed := 0;
766 --
767 open c_parameter;
768 --
769 fetch c_parameter into l_parm;
770 --
771 close c_parameter;
772 --
773 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
774 fnd_message.set_token('PROC','ben_batch_utils.print_parameters');
775 ben_batch_utils.print_parameters
776 (p_thread_id => p_thread_id
777 ,p_benefit_action_id => p_benefit_action_id
778 ,p_validate => p_validate
779 ,p_business_group_id => p_business_group_id
780 ,p_effective_date => l_effective_date
781 ,p_person_selection_rule_id => l_parm.person_selection_rl
782 ,p_organization_id => l_parm.organization_id
783 ,p_benfts_grp_id => l_parm.benfts_grp_id
784 ,p_location_id => l_parm.location_id
785 ,p_legal_entity_id => l_parm.legal_entity_id);
786
787
788 --
789 -- While loop to only try and fetch records while they exist
790 -- we always try and fetch the size of the chunk, if we get less
791 -- then we know that the process is finished so we end the while loop.
792 -- The process is as follows :
793 -- 1) Lock the rows that are not processed
794 -- 2) Grab as many rows as we can upto the chunk size
795 -- 3) Put each row into the person cache.
796 -- 4) Process the person cache
797 -- 5) Go to number 1 again.
798 --
799 hr_utility.set_location('getting range',10);
800 --
801 Loop
802 --
803 open c_range_thread;
804 --
805 fetch c_range_thread into l_range_id
806 ,l_start_person_action_id
807 ,l_end_person_action_id;
808 hr_utility.set_location('doing range fetch',10);
809 --
810 if c_range_thread%notfound then
811 --
812 hr_utility.set_location('range not Found',10);
813 --
814 close c_range_thread;
815 exit;
816 --
817 end if;
818 --
819 hr_utility.set_location('range Found',10);
820 --
821 close c_range_thread;
822 --
823 update ben_batch_ranges ran
824 set ran.range_status_cd = 'P'
825 where ran.range_id = l_range_id;
826 --
827 commit;
828 --
829 -- Get person who are in the range
830 --
831 open c_person_thread;
832 --
833 loop
834 --
835 fetch c_person_thread into l_person_id,
836 l_person_action_id;
837 hr_utility.set_location('person id'||l_person_id,10);
838 --
839 exit when c_person_thread%notfound;
840 --
841 savepoint last_place;
842 benutils.set_cache_record_position;
843
844 --
845 -- CWB - Added to avoid calling ben_person_object.get_object
846 --
847
848 open c_person;
849 fetch c_person into l_per_rec;
850 --
851 -- if l_per_rec is null get the data mased on first entry found,
852 -- order by based on effective_end_date.
853 --
854 if c_person%notfound then
855 --
856 open c_person_last;
857 fetch c_person_last into l_per_rec;
858 close c_person_last;
859 --
860 end if;
861 close c_person;
862 --
863 begin
864 --
865 hr_utility.set_location('Before open',10);
866 open c_ler_thread;
867 --
868 Loop
869 --
870 fetch c_ler_thread into l_ler_thread;
871 exit when c_ler_thread%notfound;
872 --
873 hr_utility.set_location ('per_in_ler_id '||l_ler_thread.per_in_ler_id,10);
874 hr_utility.set_location ('typ_cd '||l_ler_thread.typ_cd,10);
875 hr_utility.set_location ('bg id '||l_ler_thread.business_group_id,10);
876 --
877 --
878 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
879 fnd_message.set_token('PROC','ben_back_out_life_event');
880 hr_utility.set_location ('calling bolfe ',10);
881 ben_back_out_life_event.g_enrt_made_flag := Null;
882 ben_back_out_life_event.back_out_life_events
883 (p_per_in_ler_id => l_ler_thread.per_in_ler_id
884 ,p_business_group_id => l_ler_thread.business_group_id
885 ,p_bckt_stat_cd => p_bckt_stat_cd
886 ,p_effective_date => l_effective_date);
887 --
888 -- 9999 for some reason above proc errors just make the pil backed out.
889 --
890 -- CWBGLOBAL -- Call procedure to delete CWB de normalised data
891 --
892 -- Check if the current pil is the group pil. If so, call
893 -- delete_cwb_data.
894 --
895 hr_utility.set_location ('this ler is '||l_ler_thread.per_in_ler_id||
896 'group pil is '||p_life_event_id,777);
897 --
898 -- Bug 3517726 : CWB data is not getting deleted.
899 hr_utility.set_location ('calling delete_cwb_data',10);
900 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
901 fnd_message.set_token('PROC','delete_cwb_data');
902 delete_cwb_data
903 (p_per_in_ler_id => l_ler_thread.per_in_ler_id
904 , p_business_group_id => l_ler_thread.business_group_id
905 , p_update_summary => false
906 ) ;
907
908 /* bug 3517726
909 */
910
911 delete from ben_cwb_person_rates
912 where group_pl_id = p_group_pl_id
913 and lf_evt_ocrd_dt = l_ocrd_date
914 and group_per_in_ler_id = -1 -- Bug 5060080
915 and person_id = l_person_id;
916 --
917 g_rec.ler_id := l_ler_thread.ler_id;
918 g_rec.rep_typ_cd := 'LFBO';
919 g_rec.person_id := l_person_id;
920 --
921 -- This is to assign the global variable which contains information about
922 -- the closed or in process life events with or without election,
923 -- that were backed out.
924 --
925 g_rec.text := l_ler_thread.per_in_ler_stat_cd ||
926 ben_back_out_life_event.g_enrt_made_flag;
927 --
928 -- This is to assign the per_in_ler_id in the record to extract the
929 -- the electable choices later.
930 g_rec.temporal_ler_id := l_ler_thread.per_in_ler_id;
931
932 benutils.write(p_rec => g_rec);
933 --
934 End loop;
935 --
936 close c_ler_thread;
937 --
938 -- If we get here it was successful.
939 --
940 update ben_person_actions
941 set action_status_cd = 'P'
942 where person_id = l_person_id
943 and benefit_action_id = p_benefit_action_id;
944 --
945 benutils.write(l_per_rec.full_name||' processed successfully');
946 g_persons_processed := g_persons_processed + 1;
947 --
948 exception
949 --
950 when others then
951 --
952 hr_utility.set_location('Super Error exception level',10);
953 hr_utility.set_location(sqlerrm,10);
954
955 if c_latest_ler_cwb%isopen then
956
957 close c_latest_ler_cwb;
958 --
959 end if;
960
961 --
962 if c_ler_thread%isopen then
963
964 close c_ler_thread;
965 --
966 end if;
967 --
968 rollback to last_place;
969 benutils.rollback_cache;
970 --
971 update ben_person_actions
972 set action_status_cd = 'E'
973 where person_id = l_person_id
974 and benefit_action_id = p_benefit_action_id;
975 --
976 commit;
977 --
978 g_persons_errored := g_persons_errored + 1;
979 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
980 g_rec.rep_typ_cd := 'ERROR_LF';
981 -- g_rec.text := fnd_message.get;
982 g_rec.person_id := l_person_id;
983
984 g_rec.national_identifier := l_per_rec.national_identifier;
985 g_rec.error_message_code := benutils.get_message_name;
986 g_rec.text := fnd_message.get;
987
988 hr_utility.set_location('Error Message '||g_rec.text,10);
989 benutils.write(l_per_rec.full_name||' processed unsuccessfully');
990 benutils.write(g_rec.text);
991 benutils.write(p_rec => g_rec);
992 --
993 hr_utility.set_location('Max Errors = '||g_max_errors_allowed,10);
994 hr_utility.set_location('Num Errors = '||g_persons_errored,10);
995 if g_persons_errored > g_max_errors_allowed then
996 --
997 fnd_message.set_name('BEN','BEN_92431_BENBOCON_ERROR_LIMIT');
998 benutils.write(p_text => fnd_message.get);
999 --
1000 raise;
1001 --
1002 end if;
1003 --
1004 end;
1005 --
1006 hr_utility.set_location('Closing c_person_thread',10);
1007 --
1008 end loop;
1009 --
1010 close c_person_thread;
1011 --
1012 -- Commit chunk
1013 --
1014 if p_validate = 'Y' then
1015 --
1016 hr_utility.set_location('Rolling back transaction ',10);
1017 --
1018 rollback;
1019 --
1020 end if;
1021 --
1022 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1023 fnd_message.set_token('PROC','benutils.write_table_and_file');
1024 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1025 commit;
1026 --
1027 end loop;
1028 --
1029 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1030 fnd_message.set_token('PROC','benbatch_utils.write_logfile');
1031 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
1032 ,p_num_pers_errored => g_persons_errored);
1033 --
1034 commit;
1035 --
1036 hr_utility.set_location ('Leaving '||l_proc,70);
1037 --
1038 Exception
1039 --
1040 When others then
1041 --
1042 hr_utility.set_location('Super Error',10);
1043 hr_utility.set_location(sqlerrm,10);
1044 hr_utility.set_location('Super Error',10);
1045 rollback;
1046 benutils.rollback_cache;
1047 --
1048 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
1049 g_rec.rep_typ_cd := 'FATAL';
1050 g_rec.text := fnd_message.get;
1051 g_rec.person_id := l_person_id;
1052 --
1053 benutils.write(p_text => g_rec.text);
1054 benutils.write(p_rec => g_rec);
1055 --
1056 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
1057 ,p_num_pers_errored => g_persons_errored);
1058 --
1059 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1060 --
1061 commit;
1062 --
1063 fnd_message.raise_error;
1064 --
1065 End do_multithread;
1066
1067
1068
1069
1070 -- *************************************************************************
1071 -- * << Procedure: Process >>
1072 -- *************************************************************************
1073 -- This is called from the concurrent manager
1074
1075
1076 procedure process
1077 (errbuf out nocopy varchar2
1078 ,retcode out nocopy number
1079 ,p_benefit_action_id in number default null
1080 ,p_effective_date in varchar2
1081 ,p_validate in varchar2 default 'N'
1082 ,p_business_group_id in number
1083 ,p_group_pl_id in number
1084 ,p_life_event_id in number
1085 ,p_ocrd_date in varchar2
1086 ,p_person_selection_rule_id in number default null
1087 ,p_debug_messages in varchar2 default 'N'
1088 ,p_bckt_stat_cd in varchar2 default 'UNPROCD'
1089 ) is
1090
1091 /* local variable defintions */
1092 l_proc varchar2(80) := g_package||'.process';
1093 l_request_id number;
1094 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
1095 l_object_version_number ben_benefit_actions.object_version_number%type;
1096 l_person_id per_people_f.person_id%type;
1097 l_person_action_id ben_person_actions.person_action_id%type;
1098 l_ler_id ben_ler_f.ler_id%type;
1099 l_range_id ben_batch_ranges.range_id%type;
1100 l_chunk_size number := 20;
1101 l_threads number := 1;
1102 l_start_person_action_id number := 0;
1103 l_end_person_action_id number := 0;
1104 l_prev_person_id number := 0;
1105 rl_ret char(1);
1106 skip boolean;
1107 l_person_cnt number := 0;
1108 l_cnt number := 0;
1109 l_num_range number := 0;
1110 l_chunk_num number := 1;
1111 l_num_row number := 0;
1112 l_commit number;
1113 --
1114 l_effective_date date;
1115 l_ocrd_date date;
1116 l_no_one_to_process exception;
1117 l_business_group_id number;
1118 --
1119 l_person_selection number;
1120
1121 /* cursor definitions*/
1122
1123
1124 cursor c_person is
1125 select distinct ppf.person_id, ppf.business_group_id
1126 from per_all_people_f ppf
1127 where -- l_effective_date between ppf.effective_start_date and ppf.effective_end_date and
1128 exists (select null
1129 from ben_per_in_ler pil
1130 , ben_ler_f ler
1131 where pil.lf_evt_ocrd_dt = l_ocrd_date
1132 and pil.ler_id = ler.ler_id
1133 and l_effective_date between ler.effective_start_date
1134 and ler.effective_end_date
1135 /* and ler.business_group_id = p_business_group_id */
1136 -- Looks like p_life_event_id is not passed in
1137 and ler.typ_cd = 'COMP'
1138 and pil.ler_id = nvl(p_life_event_id, pil.ler_id)
1139 /* life event id made non mandatory parameter*/
1140 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
1141 and pil.person_id = ppf.person_id
1142 and pil.group_pl_id = p_group_pl_id
1143 ) ;
1144 /*
1145 cursor c_person is
1146 select distinct pil.person_id, pil.business_group_id
1147 from ben_per_in_ler pil
1148 where pil.lf_evt_ocrd_dt = l_ocrd_date
1149 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
1150 and pil.group_pl_id = p_group_pl_id;
1151
1152 */
1153 cursor c_person_selection (cv_formula_id number
1154 , cv_business_group_id number
1155 , cv_effective_date date
1156 ) is
1157 select fff.formula_id
1158 from ff_formulas_f fff,
1159 ff_formulas_f fff1
1160 where fff.business_group_id = cv_business_group_id
1161 and cv_effective_date between fff.effective_start_date
1162 and fff.effective_end_date
1163 and fff.formula_name = fff1.formula_name
1164 and cv_effective_date between fff1.effective_start_date
1165 and fff1.effective_end_date
1166 and fff1.formula_id = cv_formula_id;
1167
1168 -- Bug 4758468 : join condition is missing in c_group_pils.
1169
1170 cursor c_group_pils(cv_group_bg_id in number) is
1171 Select pil.per_in_ler_id
1172 from ben_person_actions act,
1173 ben_per_in_ler pil
1174 where act.benefit_action_id = l_benefit_action_id
1175 and act.action_status_cd = 'P'
1176 and act.person_id = pil.person_id
1177 and pil.lf_evt_ocrd_dt = l_ocrd_date
1178 and pil.per_in_ler_stat_cd = 'BCKDT'
1179 and pil.business_group_id = cv_group_bg_id
1180 and pil.group_pl_id = p_group_pl_id;
1181 --
1182 cursor c_group_pl_bg is
1183 Select pln.business_group_id
1184 from ben_pl_f pln
1185 where pln.pl_id = p_group_pl_id
1186 and l_ocrd_date between pln.effective_start_date
1187 and pln.effective_end_date;
1188 --
1189 cursor c_broke_hier (cv_group_pl_id in number,
1190 cv_ocrd_date in date) is
1191 select inf.full_name, inf.person_id
1192 from ben_cwb_person_info inf
1193 ,ben_per_in_ler pil
1194 where pil.group_pl_id = cv_group_pl_id
1195 and pil.lf_evt_ocrd_dt = cv_ocrd_date
1196 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
1197 and pil.ws_mgr_id is not null
1198 and pil.per_in_ler_id = inf.group_per_in_ler_id
1199 and not exists (select 'Y'
1200 from ben_cwb_group_hrchy hrchy
1201 where hrchy.emp_per_in_ler_id = pil.per_in_ler_id
1202 and hrchy.lvl_num = 1) ;
1203 --
1204 l_group_business_group_id number;
1205 l_person_ok varchar2(1) := 'Y';
1206 l_err_message varchar2(2000);
1207 l_head number := 0 ;
1208
1209 begin
1210 --
1211 hr_utility.set_location ('Entering '||l_proc,10);
1212 --
1213
1214 hr_utility.set_location ('p_business_group_id '||p_business_group_id,10);
1215 hr_utility.set_location ('p_life_event_id '||p_life_event_id,10);
1216 hr_utility.set_location ('p_ocrd_date '||p_ocrd_date,10);
1217 hr_utility.set_location ('p_group_pl_id '||p_group_pl_id,10);
1218 hr_utility.set_location ('p_person_selection_rule_id '||p_person_selection_rule_id,10);
1219 --
1220
1221 --
1222 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
1223 l_ocrd_date:=trunc(fnd_date.canonical_to_date(p_ocrd_date));
1224
1225 --
1226 dt_fndate.change_ses_date
1227 (p_ses_date => l_effective_date,
1228 p_commit => l_commit);
1229 --
1230 -- Get chunk_size and Thread values for multi-thread process.
1231 --
1232 ben_batch_utils.ini;
1233 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
1234 --
1235 benutils.get_parameter(p_business_group_id => p_business_group_id
1236 ,p_batch_exe_cd => 'BENBOCON'
1237 ,p_threads => l_threads
1238 ,p_chunk_size => l_chunk_size
1239 ,p_max_errors => g_max_errors_allowed);
1240 --
1241 -- Create benefit actions parameters in the benefit action table.
1242 -- Do not create if a benefit action already exists, in other words
1243 -- we are doing a restart.
1244 --
1245 If p_benefit_action_id is null then
1246 --
1247 ben_benefit_actions_api.create_benefit_actions
1248 (p_validate => false
1249 ,p_benefit_action_id => l_benefit_action_id
1250 ,p_process_date => l_effective_date
1251 ,p_mode_cd => 'S'
1252 ,p_derivable_factors_flag => 'N'
1253 ,p_validate_flag => p_validate
1254 ,p_person_id => null
1255 ,p_person_type_id => null
1256 ,p_pgm_id => null
1257 ,p_business_group_id => p_business_group_id
1258 ,p_pl_typ_id => null
1259 ,p_pl_id => p_group_pl_id -- CWBGLOBAL
1260 ,p_popl_enrt_typ_cycl_id => null
1261 ,p_no_programs_flag => 'N'
1262 ,p_no_plans_flag => 'N'
1263 ,p_comp_selection_rl => null
1264 ,p_person_selection_rl => p_person_selection_rule_id
1265 ,p_ler_id => p_life_event_id
1266 ,p_organization_id => null
1267 ,p_benfts_grp_id => null
1268 ,p_location_id => null
1269 ,p_pstl_zip_rng_id => null
1270 ,p_rptg_grp_id => null
1271 ,p_opt_id => null
1272 ,p_eligy_prfl_id => null
1273 ,p_vrbl_rt_prfl_id => null
1274 ,p_legal_entity_id => null
1275 ,p_payroll_id => null
1276 ,p_debug_messages_flag => p_debug_messages
1277 ,p_object_version_number => l_object_version_number
1278 ,p_effective_date => l_effective_date
1279 ,p_request_id => fnd_global.conc_request_id
1280 ,p_program_application_id => fnd_global.prog_appl_id
1281 ,p_program_id => fnd_global.conc_program_id
1282 ,p_program_update_date => sysdate
1283 ,p_date_from => l_ocrd_date
1284 ,p_uneai_effective_date => null);
1285 --
1286 benutils.g_benefit_action_id := l_benefit_action_id;
1287 --
1288 hr_utility.set_location ('l_benefit_action_id created is '||l_benefit_action_id,30);
1289 -- Delete/clear ranges from ben_batch_ranges table
1290 --
1291 Delete from ben_batch_ranges
1292 Where benefit_action_id = l_benefit_action_id;
1293 --
1294 -- Now lets create person actions for all the people we are going to
1295 -- process in the Back-out life event run
1296 --
1297 open c_person;
1298 --
1299 l_person_cnt := 0;
1300 l_cnt := 0;
1301 --
1302 loop
1303 --
1304 l_person_selection := null;
1305 fetch c_person into l_person_id, l_business_group_id;
1306 hr_utility.set_location ('next person selected is '||l_person_id,30);
1307 exit when c_person%notfound;
1308 --
1309 l_cnt := l_cnt + 1;
1310 --
1311 l_person_ok := 'Y';
1312 --
1313 If p_person_selection_rule_id is not NULL then
1314 --
1315 open c_person_selection (p_person_selection_rule_id,
1316 l_business_group_id, l_ocrd_date);
1317 fetch c_person_selection into l_person_selection;
1318 close c_person_selection;
1319 --
1320 if l_person_selection is not null then
1321 --
1322 ben_batch_utils.person_selection_rule
1323 (p_person_id => l_person_id
1324 ,p_business_group_id => l_business_group_id
1325 ,p_person_selection_rule_id=> l_person_selection
1326 ,p_effective_date => l_effective_date
1327 ,p_return => l_person_ok
1328 ,p_err_message => l_err_message );
1329 --
1330 if l_err_message is not null
1331 then
1332 --
1333 -- 9999 if the error message corresponds to
1334 -- BEN_91698_NO_ASSIGNMENT_FND then try running the formula again
1335 -- with different effective date.
1336 -- get the effective date from person record and use it.
1337 -- select effective_start_date, effective_end_date
1338 -- from per_all_people_f where person_id = l_person_id
1339 -- order by effective_start_date desc;
1340 --
1341 Ben_batch_utils.write(p_text =>
1342 '<< Person id : '||to_char(l_person_id)||' failed.'||
1343 ' Reason : '|| l_err_message ||' >>' );
1344 l_err_message := NULL ;
1345 --
1346 end if ;
1347 --
1348 end if;
1349
1350 End if;
1351 --
1352 -- Store person_id into person actions table.
1353 --
1354 If l_person_ok = 'Y' then
1355 --
1356 hr_utility.set_location ('person passed selection rule '||l_person_id,35);
1357 Ben_person_actions_api.create_person_actions
1358 (p_validate => false
1359 ,p_person_action_id => l_person_action_id
1360 ,p_person_id => l_person_id
1361 ,p_ler_id => l_ler_id
1362 ,p_benefit_action_id => l_benefit_action_id
1363 ,p_action_status_cd => 'U'
1364 ,p_chunk_number => l_chunk_num
1365 ,p_object_version_number => l_object_version_number
1366 ,p_effective_date => l_effective_date);
1367 --
1368 hr_utility.set_location ('person action created is '||l_person_action_id,40);
1369 --
1370 l_num_row := l_num_row + 1;
1371 l_person_cnt := l_person_cnt + 1;
1372 l_end_person_action_id := l_person_action_id;
1373 --
1374 If l_num_row = 1 then
1375 --
1376 l_start_person_action_id := l_person_action_id;
1377 --
1378 End if;
1379 ----
1380 If l_num_row = l_chunk_size then
1381 --
1382 -- Create a range of data to be multithreaded.
1383 --
1384 Ben_batch_ranges_api.create_batch_ranges
1385 (p_validate => false
1386 ,p_benefit_action_id => l_benefit_action_id
1387 ,p_range_id => l_range_id
1388 ,p_range_status_cd => 'U'
1389 ,p_starting_person_action_id => l_start_person_action_id
1390 ,p_ending_person_action_id => l_end_person_action_id
1391 ,p_object_version_number => l_object_version_number
1392 ,p_effective_date => l_effective_date);
1393 --
1394 hr_utility.set_location ('person action range created is '||l_range_id,45);
1395 --
1396 l_start_person_action_id := 0;
1397 l_end_person_action_id := 0;
1398 l_num_row := 0;
1399 l_num_range := l_num_range + 1;
1400 --
1401 End if;
1402 --
1403 End if;
1404 --
1405 End loop;
1406 --
1407 close c_person;
1408 --
1409 --
1410 hr_utility.set_location('l_num_row='||to_char(l_num_row),48);
1411 --
1412 If l_num_row <> 0 then
1413 --
1414 Ben_batch_ranges_api.create_batch_ranges
1415 (p_validate => false
1416 ,p_benefit_action_id => l_benefit_action_id
1417 ,p_range_id => l_range_id
1418 ,p_range_status_cd => 'U'
1419 ,p_starting_person_action_id => l_start_person_action_id
1420 ,p_ending_person_action_id => l_end_person_action_id
1421 ,p_object_version_number => l_object_version_number
1422 ,p_effective_date => l_effective_date);
1423 --
1424 l_num_range := l_num_range + 1;
1425 --
1426 hr_utility.set_location('l_num_row='||to_char(l_num_row),50);
1427 hr_utility.set_location ('person action range created is '||l_range_id,55);
1428 --
1429 End if;
1430 --
1431 Else
1432 --
1433 l_benefit_action_id := p_benefit_action_id;
1434 --
1435 Ben_batch_utils.create_restart_person_actions
1436 (p_benefit_action_id => p_benefit_action_id
1437 ,p_effective_date => l_effective_date
1438 ,p_chunk_size => l_chunk_size
1439 ,p_threads => l_threads
1440 ,p_num_ranges => l_num_range
1441 ,p_num_persons => l_person_cnt);
1442 --
1443 End if;
1444 --
1445 If l_num_range > 1 then
1446 --
1447 For l_count in 1..least(l_threads,l_num_range)-1 loop
1448 --
1449 hr_utility.set_location('spawning thread #'||l_count,60);
1450 --
1451 l_request_id := fnd_request.submit_request
1452 (application => 'BEN'
1453 ,program => 'BENCWBBT'
1454 ,description => NULL
1455 ,sub_request => FALSE
1456 ,argument1 => p_validate
1457 ,argument2 => l_benefit_action_id
1458 ,argument3 => l_count
1459 ,argument4 => p_effective_date
1460 ,argument5 => p_business_group_id
1461 ,argument6 => p_ocrd_date
1462 ,argument7 => p_group_pl_id
1463 ,argument8 => p_life_event_id
1464 ,argument9 => p_bckt_stat_cd
1465 );
1466 --
1467 -- Store the request id of the concurrent request
1468 --
1469 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1470 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1471 := l_request_id;
1472 --
1473 End loop;
1474 --
1475 commit;
1476 --
1477 Elsif (l_num_range = 0 ) then
1478 --
1479 Ben_batch_utils.print_parameters
1480 (p_thread_id => 99
1481 ,p_benefit_action_id => l_benefit_action_id
1482 ,p_validate => p_validate
1483 ,p_business_group_id => p_business_group_id
1484 ,p_effective_date => l_effective_date
1485 ,p_person_selection_rule_id => p_person_selection_rule_id
1486 ,p_ler_id => p_life_event_id
1487 ,p_organization_id => null
1488 ,p_benfts_grp_id => null
1489 ,p_location_id => null
1490 ,p_legal_entity_id => null);
1491 --
1492 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1493 fnd_message.set_token('PROC' , l_proc);
1494 raise l_no_one_to_process;
1495 --
1496 End if;
1497 --
1498 do_multithread(errbuf => errbuf
1499 ,retcode => retcode
1500 ,p_validate => p_validate
1501 ,p_benefit_action_id => l_benefit_action_id
1502 ,p_thread_id => l_threads+1
1503 ,p_effective_date => p_effective_date
1504 ,p_business_group_id => p_business_group_id
1505 ,p_ocrd_date => p_ocrd_date
1506 ,p_group_pl_id => p_group_pl_id
1507 --,p_to_ocrd_date => p_ocrd_date
1508 ,p_life_event_id => p_life_event_id
1509 ,p_bckt_stat_cd => p_bckt_stat_cd
1510 );
1511 --
1512 hr_utility.set_location('waiting for slaves',65);
1513 --
1514 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1515 hr_utility.set_location('hurray my slaves are done',70);
1516 --
1517 -- Once all slaves are done go and delete heirarchy data.
1518 --
1519 if p_validate = 'N' then
1520 --
1521 open c_group_pl_bg;
1522 fetch c_group_pl_bg into l_group_business_group_id;
1523 close c_group_pl_bg;
1524 --
1525 for l_group_pil_rec in c_group_pils(l_group_business_group_id) loop
1526 backout_heirarchy_data
1527 (p_per_in_ler_id => l_group_pil_rec.per_in_ler_id);
1528 end loop;
1529 --
1530 hr_utility.set_location('Deleting data from ben_cwb_pl_dsgn ',10);
1531 BEN_CWB_PL_DSGN_PKG.delete_pl_dsgn
1532 (p_group_pl_id => p_group_pl_id
1533 ,p_lf_evt_ocrd_dt => l_ocrd_date);
1534 --
1535 hr_utility.set_location('Refreshing Summary ',20);
1536 ben_cwb_summary_pkg.refresh_summary_group_pl
1537 (p_group_pl_id => p_group_pl_id
1538 ,p_lf_evt_ocrd_dt => l_ocrd_date);
1539 --
1540 commit;
1541 --
1542 hr_utility.set_location('Refreshing Summary Complete',30);
1543
1544 end if;
1545 --
1546 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1547 ,p_person_selected => l_person_cnt
1548 ,p_business_group_id => p_business_group_id);
1549 --
1550 -- Report any broken hierarchies, people who do not have a level 1 manager
1551 -- but have a worksheet manager id
1552 --
1553 l_head := 0;
1554 --
1555 for i in c_broke_hier (p_group_pl_id,l_ocrd_date )
1556 loop
1557 if l_head = 0
1558 then
1559 fnd_file.put_line(which => fnd_file.log,
1560 buff => 'Note: List of persons who do not have a level 1 manager but have a worksheet manager id.');
1561 fnd_file.put_line(which => fnd_file.log,
1562 buff => ' Please re-assign these employees to a new manager.');
1563 fnd_file.put_line(which => fnd_file.log,
1564 buff => '----------------------------------------------------------------------------------------');
1565 l_head := 1 ;
1566 end if;
1567 fnd_file.put_line(which => fnd_file.log,
1568 buff => i.full_name ||' ('||'person_id = ' || i.person_id||')' );
1569 end loop;
1570 --
1571 hr_utility.set_location('Submitting reports',72);
1572 --
1573 -- submit summary report here
1574 --
1575 l_request_id := fnd_request.submit_request
1576 (application => 'BEN',
1577 program => 'BENBOSUM',
1578 description => null,
1579 sub_request => false,
1580 argument1 => fnd_global.conc_request_id);
1581 --
1582 -- submit Error reports here
1583 --
1584 l_request_id := fnd_request.submit_request
1585 (application => 'BEN',
1586 program => 'BENERTYP',
1587 description => null,
1588 sub_request => false,
1589 argument1 => fnd_global.conc_request_id);
1590 --
1591 l_request_id := fnd_request.submit_request
1592 (application => 'BEN',
1593 program => 'BENERPER',
1594 description => null,
1595 sub_request => false,
1596 argument1 => fnd_global.conc_request_id);
1597 --
1598 hr_utility.set_location ('Leaving '||l_proc,75);
1599 --
1600 -- hr_utility.trace_off;
1601 Exception
1602
1603 when l_no_one_to_process then
1604 if c_person%isopen then
1605 close c_person;
1606 end if;
1607 benutils.write(p_text => fnd_message.get);
1608 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1609
1610 when others then
1611 --
1612 if c_person%isopen then
1613 close c_person;
1614 end if;
1615 hr_utility.set_location('Super Error',10);
1616 rollback;
1617 benutils.write(p_text => fnd_message.get);
1618 benutils.write(p_text => sqlerrm);
1619 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1620 If (l_num_range > 0) then
1621 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1622 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1623 ,p_person_selected => l_person_cnt
1624 ,p_business_group_id => p_business_group_id
1625 ) ;
1626 End if;
1627 fnd_message.raise_error;
1628 End process;
1629 --
1630 -- *************************************************************************
1631 -- * << Procedure: cwb_delete_routine >>
1632 -- *************************************************************************
1633 -- Procedure to delete data from the table passed as parameter ' p_routine'
1634
1635 procedure cwb_delete_routine
1636 (p_routine in varchar2
1637 ,p_per_in_ler_id in number
1638 ,p_update_summary in boolean
1639 ) is
1640 -- CWBGLOBAL
1641 cursor c_cwb_person_task is
1642 select task_id, object_version_number
1643 from ben_cwb_person_tasks
1644 where group_per_in_ler_id = p_per_in_ler_id;
1645 --
1646 cursor c_cwb_person_group is
1647 select group_pl_id, group_oipl_id, object_version_number
1648 from ben_cwb_person_groups
1649 where group_per_in_ler_id = p_per_in_ler_id;
1650 --
1651 cursor c_cwb_person_rates is
1652 select distinct pl_id, oipl_id, pay_proposal_id
1653 from ben_cwb_person_rates
1654 where group_per_in_ler_id = p_per_in_ler_id
1655 order by pay_proposal_id;
1656 --
1657 l_pl_id number;
1658 l_oipl_id number;
1659 --
1660 cursor c_chk_rts_exists is
1661 select ELEMENT_ENTRY_VALUE_ID,
1662 COMP_POSTING_DATE,
1663 person_id,
1664 object_version_number
1665 from ben_cwb_person_rates
1666 where group_per_in_ler_id = p_per_in_ler_id
1667 and pl_id = l_pl_id
1668 and oipl_id = l_oipl_id ;
1669 --
1670 l_pay_proposal_id number;
1671 l_pay_proposal_id_prev number := -1;
1672 --
1673 cursor c_pay_proposals is
1674 select object_version_number, business_group_id
1675 from per_pay_proposals
1676 where pay_proposal_id = l_pay_proposal_id;
1677 --
1678 --***************audit changes***************--
1679 --
1680 cursor c_cwb_person_info is
1681 select object_version_number
1682 from ben_cwb_person_info
1683 where group_per_in_ler_id = p_per_in_ler_id;
1684
1685 --
1686 l_proc varchar2(50) := g_package||'.cwb_delete_routine';
1687 l_task_id number;
1688 l_group_pl_id number;
1689 l_person_rate_id number;
1690 l_group_oipl_id number;
1691 l_object_version_number number;
1692 l_dum number;
1693 l_salary_warning boolean;
1694
1695 l_object_version_number_prop number;
1696 l_business_group_id_prop number;
1697 l_dummy number;
1698 l_dummy1 number;
1699 l_dummy2 number;
1700 l_ELEMENT_ENTRY_VALUE_ID number;
1701 l_COMP_POSTING_DATE date;
1702 l_person_id number;
1703 --
1704 begin
1705
1706 hr_utility.set_location( 'Entering '|| l_proc, 5);
1707 --
1708 -- CWBGLOBAL
1709 --
1710 if p_routine = 'BEN_CWB_PERSON_TASKS' then
1711 --
1712 open c_cwb_person_task;
1713 loop
1714 fetch c_cwb_person_task into l_task_id, l_object_version_number;
1715 exit when c_cwb_person_task%NOTFOUND ;
1716
1717 ben_cwb_person_tasks_api.delete_person_task
1718 ( p_validate => false,
1719 p_group_per_in_ler_id => p_per_in_ler_id,
1720 p_task_id => l_task_id,
1721 p_object_version_number => l_object_version_number
1722 );
1723
1724 end loop;
1725 --
1726 close c_cwb_person_task;
1727 --
1728 elsif p_routine = 'BEN_CWB_PERSON_GROUPS' then
1729 --
1730 open c_cwb_person_group;
1731 loop
1732 fetch c_cwb_person_group into l_group_pl_id,
1733 l_group_oipl_id, l_object_version_number;
1734 exit when c_cwb_person_group%NOTFOUND ;
1735
1736 BEN_CWB_PERSON_GROUPS_API.delete_group_budget
1737 ( p_validate => false,
1738 p_group_per_in_ler_id => p_per_in_ler_id,
1739 p_group_pl_id => l_group_pl_id,
1740 p_group_oipl_id => l_group_oipl_id,
1741 p_object_version_number => l_object_version_number,
1742 p_update_summary => p_update_summary
1743 );
1744
1745 end loop;
1746 close c_cwb_person_group;
1747 --
1748 elsif p_routine = 'BEN_CWB_PERSON_RATES' then
1749 --
1750 l_pay_proposal_id_prev := -1;
1751 --
1752 open c_cwb_person_rates;
1753 loop
1754
1755 l_ELEMENT_ENTRY_VALUE_ID := null;
1756 l_COMP_POSTING_DATE := null;
1757 l_object_version_number := null;
1758 --
1759 fetch c_cwb_person_rates into l_pl_id, l_oipl_id,
1760 l_pay_proposal_id;
1761 exit when c_cwb_person_rates%NOTFOUND ;
1762
1763 open c_chk_rts_exists;
1764 fetch c_chk_rts_exists into l_ELEMENT_ENTRY_VALUE_ID,
1765 l_COMP_POSTING_DATE ,
1766 l_person_id,
1767 l_object_version_number;
1768 close c_chk_rts_exists;
1769
1770 if l_pay_proposal_id is not null then
1771 --
1772 open c_pay_proposals;
1773 fetch c_pay_proposals
1774 into l_object_version_number_prop, l_business_group_id_prop;
1775 close c_pay_proposals;
1776 --
1777 end if;
1778 --
1779 -- Delete element entry if attached to rate row.
1780 --
1781 hr_utility.set_location('l_ELEMENT_ENTRY_VALUE_ID = '
1782 || l_ELEMENT_ENTRY_VALUE_ID, 88);
1783 hr_utility.set_location('l_business_group_id_prop = '
1784 || l_business_group_id_prop, 88);
1785 hr_utility.set_location('l_person_id = ' || l_person_id, 88);
1786 hr_utility.set_location('l_COMP_POSTING_DATE = '
1787 || l_COMP_POSTING_DATE, 88);
1788 if l_ELEMENT_ENTRY_VALUE_ID is not null and
1789 l_COMP_POSTING_DATE is not null then
1790
1791 backout_cwb_element(
1792 p_element_entry_value_id => l_ELEMENT_ENTRY_VALUE_ID
1793 ,p_validate => false
1794 ,p_business_group_id => l_business_group_id_prop
1795 ,p_person_id => l_person_id
1796 ,p_effective_date => l_COMP_POSTING_DATE
1797 );
1798
1799 end if;
1800 --
1801 if l_object_version_number is not null then
1802 ben_cwb_person_rates_api.delete_person_rate
1803 (p_validate => false
1804 ,p_group_per_in_ler_id => p_per_in_ler_id
1805 ,p_pl_id => l_pl_id
1806 ,p_oipl_id => l_oipl_id
1807 ,p_object_version_number => l_object_version_number
1808 ,p_update_summary => p_update_summary) ;
1809 end if;
1810 --
1811 -- Bug 5130397 : When CWB plan has options attached, then all corresponding rows in BEN_CWB_PERSON_RATES
1812 -- has pay_proposal_id populated and this being same ID, we should not call delete API
1813 -- more than once. Hence added following check : l_pay_proposal_id <> l_pay_proposal_id_prev
1814 --
1815 if l_pay_proposal_id is not null AND
1816 l_pay_proposal_id <> l_pay_proposal_id_prev
1817 then
1818 --
1819 hr_maintain_proposal_api.delete_salary_proposal
1820 ( p_pay_proposal_id => l_pay_proposal_id
1821 ,p_business_group_id => l_business_group_id_prop
1822 ,p_object_version_number => l_object_version_number_prop
1823 ,p_validate => false
1824 ,p_salary_warning => l_salary_warning ) ;
1825 --
1826 l_pay_proposal_id_prev := l_pay_proposal_id;
1827 --
1828 end if;
1829 --
1830 end loop;
1831 close c_cwb_person_rates;
1832
1833 elsif p_routine = 'BEN_CWB_PERSON_INFO' then
1834
1835 hr_utility.set_location( 'in audit changes BEN_CWB_PERSON_INFO'
1836 || l_proc, 500);
1837
1838 open c_cwb_person_info;
1839 loop
1840 fetch c_cwb_person_info into l_object_version_number;
1841 exit when c_cwb_person_info%NOTFOUND ;
1842
1843 BEN_CWB_PERSON_INFO_API.delete_person_info
1844 ( p_validate => false,
1845 p_group_per_in_ler_id => p_per_in_ler_id,
1846 p_object_version_number => l_object_version_number
1847 );
1848
1849 end loop;
1850 close c_cwb_person_info;
1851 hr_utility.set_location( 'LEAVING audit changes BEN_CWB_PERSON_INFO'
1852 || l_proc, 600);
1853
1854 end if;
1855 hr_utility.set_location( 'Leaving '||l_proc, 50);
1856
1857 exception
1858 --
1859 when others then
1860 --
1861 hr_utility.set_location('Super Error exception level',10);
1862 hr_utility.set_location(sqlerrm,10);
1863 --
1864 if c_cwb_person_info%isopen then
1865 close c_cwb_person_info;
1866 end if;
1867 if c_cwb_person_rates%isopen then
1868 close c_cwb_person_rates;
1869 end if;
1870 if c_cwb_person_group%isopen then
1871 close c_cwb_person_group;
1872 end if;
1873 if c_cwb_person_group%isopen then
1874 close c_cwb_person_group;
1875 end if;
1876 if c_cwb_person_task%isopen then
1877 close c_cwb_person_task;
1878 end if;
1879 raise;
1880 --
1881 end cwb_delete_routine;
1882
1883
1884 -- *************************************************************************
1885 -- * << Procedure: delete_cwb_data >>
1886 -- *************************************************************************
1887 -- Procedure to delete data from CWB de normalised tables
1888
1889 procedure delete_cwb_data
1890 (p_per_in_ler_id in number
1891 ,p_business_group_id in number
1892 ,p_update_summary in boolean default false
1893 ) is
1894
1895 l_proc varchar2(50) := g_package||'.delete_cwb_data';
1896 p_object_version_number ben_cwb_person_info.object_version_number%type;
1897 begin
1898
1899 hr_utility.set_location( 'Entering '||l_proc, 5);
1900
1901 --1. BEN_CWB_PERSON_TASKS
1902 hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_TASKS', 10);
1903 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1904 fnd_message.set_token('PROC','delete_person_tasks');
1905 cwb_delete_routine
1906 (p_routine => 'BEN_CWB_PERSON_TASKS'
1907 ,p_per_in_ler_id => p_per_in_ler_id
1908 ,p_update_summary => p_update_summary
1909 );
1910
1911 --2. BEN_CWB_PERSON_RATES
1912 hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_RATES', 15);
1913 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1914 fnd_message.set_token('PROC','delete_person_rates');
1915 cwb_delete_routine
1916 (p_routine => 'BEN_CWB_PERSON_RATES'
1917 ,p_per_in_ler_id => p_per_in_ler_id
1918 ,p_update_summary => p_update_summary
1919 );
1920
1921 --3. BEN_CWB_PERSON_GROUPS
1922 hr_utility.set_location( 'BEN_CWB_PERSON_GROUPS', 20);
1923 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1924 fnd_message.set_token('PROC','delete_person_groups');
1925 cwb_delete_routine
1926 (p_routine => 'BEN_CWB_PERSON_GROUPS'
1927 ,p_per_in_ler_id => p_per_in_ler_id
1928 ,p_update_summary => p_update_summary
1929 );
1930
1931 --4. BEN_CWB_PERSON_INFO
1932 hr_utility.set_location( 'Calling delete for BEN_CWB_PERSON_INFO', 25);
1933 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1934 fnd_message.set_token('PROC','delete_person_info');
1935 cwb_delete_routine
1936 (p_routine => 'BEN_CWB_PERSON_INFO'
1937 ,p_per_in_ler_id => p_per_in_ler_id
1938 ,p_update_summary => p_update_summary
1939 );
1940 --************************************************************ --
1941
1942 hr_utility.set_location( 'Leaving '||l_proc, 50);
1943 --
1944 end delete_cwb_data;
1945 --
1946 procedure delete_summary(p_group_per_in_ler_id in number) is
1947 --
1948 cursor csr_summary is
1949 select rowid, s.*
1950 from ben_cwb_summary s
1951 where s.group_per_in_ler_id = p_group_per_in_ler_id;
1952 --
1953 cursor csr_mgr_pil_ids is
1954 select mgr_per_in_ler_id
1955 from ben_cwb_group_hrchy
1956 where emp_per_in_ler_id = p_group_per_in_ler_id
1957 and lvl_num > 0;
1958 --
1959 begin
1960 --
1961 ben_cwb_summary_pkg.save_pl_sql_tab;
1962 for summs in csr_summary loop
1963 for mgr in csr_mgr_pil_ids loop
1964 ben_cwb_summary_pkg.update_or_insert_pl_sql_tab
1965 (p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
1966 ,p_group_pl_id => summs.group_pl_id
1967 ,p_group_oipl_id => summs.group_oipl_id
1968 ,p_elig_count_all => -summs.elig_count_all
1969 ,p_emp_recv_count_all => -summs.emp_recv_count_all
1970 ,p_elig_sal_val_all => -summs.elig_sal_val_all
1971 ,p_ws_bdgt_val_all => -summs.ws_bdgt_val_all
1972 ,p_ws_bdgt_iss_val_all => -summs.ws_bdgt_iss_val_all
1973 ,p_ws_val_all => -summs.ws_val_all
1974 ,p_stat_sal_val_all => -summs.stat_sal_val_all
1975 ,p_oth_comp_val_all => -summs.oth_comp_val_all
1976 ,p_tot_comp_val_all => -summs.tot_comp_val_all
1977 ,p_rec_val_all => -summs.rec_val_all
1978 ,p_rec_mn_val_all => -summs.rec_mn_val_all
1979 ,p_rec_mx_val_all => -summs.rec_mx_val_all
1980 ,p_misc1_val_all => -summs.misc1_val_all
1981 ,p_misc2_val_all => -summs.misc2_val_all
1982 ,p_misc3_val_all => -summs.misc3_val_all);
1983 end loop;
1984 delete ben_cwb_summary
1985 where rowid = summs.rowid;
1986 end loop;
1987
1988 ben_cwb_summary_pkg.save_pl_sql_tab;
1989
1990 end delete_summary;
1991 --
1992 procedure p_backout_global_cwb_event
1993 (p_effective_date in date
1994 ,p_validate in varchar2 default 'N'
1995 ,p_business_group_id in number
1996 ,p_group_pl_id in number
1997 ,p_life_event_id in number default null
1998 ,p_lf_evt_ocrd_dt in date
1999 ,p_person_id in number default null
2000 ,p_bckt_stat_cd in varchar2 default 'UNPROCD'
2001 ) is
2002 --
2003 cursor c_pil(cv_person_id number,
2004 cv_lf_evt_ocrd_dt date,
2005 cv_group_pl_id number,
2006 cv_effective_date date) is
2007 select pil.per_in_ler_id,
2008 pil.person_id,
2009 pil.per_in_ler_stat_cd,
2010 pil.lf_evt_ocrd_dt,
2011 pil.business_group_id,
2012 ler.typ_cd,
2013 ler.ler_id
2014 from ben_per_in_ler pil,
2015 ben_ler_f ler
2016 where pil.person_id = cv_person_id
2017 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
2018 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
2019 and pil.group_pl_id = cv_group_pl_id
2020 and ler.ler_id = pil.ler_id
2021 and ler.typ_cd = 'COMP'
2022 and nvl(cv_effective_date,trunc(sysdate))
2023 between ler.effective_start_date
2024 and ler.effective_end_date;
2025 --
2026 l_pil_rec c_pil%rowtype;
2027 l_proc varchar2(50) := g_package||'.p_backout_global_cwb_event';
2028 --
2029 begin
2030 --
2031 hr_utility.set_location( 'Entering '||l_proc, 10);
2032 open c_pil(p_person_id,
2033 p_lf_evt_ocrd_dt,
2034 p_group_pl_id,
2035 p_lf_evt_ocrd_dt);
2036 --
2037 Loop
2038 --
2039 fetch c_pil into l_pil_rec;
2040 exit when c_pil%notfound;
2041 --
2042 hr_utility.set_location ('per_in_ler_id '||l_pil_rec.per_in_ler_id,10);
2043 hr_utility.set_location ('typ_cd '||l_pil_rec.typ_cd,10);
2044 hr_utility.set_location ('bg id '||l_pil_rec.business_group_id,10);
2045 --
2046 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
2047 fnd_message.set_token('PROC','ben_back_out_life_event');
2048 hr_utility.set_location ('calling bolfe ',10);
2049 --
2050 ben_back_out_life_event.g_enrt_made_flag := Null;
2051 --
2052 ben_back_out_life_event.back_out_life_events
2053 (p_per_in_ler_id => l_pil_rec.per_in_ler_id
2054 ,p_business_group_id => l_pil_rec.business_group_id
2055 ,p_bckt_stat_cd => p_bckt_stat_cd
2056 ,p_effective_date => l_pil_rec.lf_evt_ocrd_dt);
2057 --
2058 delete_cwb_data
2059 (p_per_in_ler_id => l_pil_rec.per_in_ler_id
2060 ,p_business_group_id => l_pil_rec.business_group_id
2061 ,p_update_summary => true);
2062 --
2063 delete_summary(p_group_per_in_ler_id => l_pil_rec.per_in_ler_id);
2064 --
2065 backout_heirarchy_data
2066 (p_per_in_ler_id => l_pil_rec.per_in_ler_id);
2067 --
2068 delete from ben_cwb_person_rates
2069 where group_pl_id = p_group_pl_id
2070 and lf_evt_ocrd_dt = l_pil_rec.lf_evt_ocrd_dt
2071 and group_per_in_ler_id = -1 -- Bug 5060080
2072 and person_id = p_person_id;
2073 --
2074 End loop;
2075 --
2076 close c_pil;
2077 --
2078 hr_utility.set_location('Leaving '||l_proc, 50);
2079 exception
2080 --
2081 when others then
2082 --
2083 hr_utility.set_location('Super Error exception level',10);
2084 hr_utility.set_location(sqlerrm,10);
2085 --
2086 if c_pil%isopen then
2087 --
2088 close c_pil;
2089 --
2090 end if;
2091 --
2092 raise;
2093 --
2094 end p_backout_global_cwb_event;
2095 --
2096 end ben_cwb_back_out_conc;
2097 --