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