[Home] [Help]
PACKAGE BODY: APPS.BEN_PURGE_BCKDT_VOIDED
Source
1 PACKAGE BODY ben_purge_bckdt_voided AS
2 /* $Header: benprbck.pkb 120.5.12010000.2 2008/08/05 14:49:30 ubhat ship $ */
3 --
4 g_package varchar2(80) := 'ben_purge_bckdt_voided';
5 g_max_person_err Number := 100;
6 g_persons_errored Number := 0;
7 g_persons_procd Number := 0;
8 g_cache_per_proc g_cache_person_process_rec;
9 g_elig_rows number := 0;
10 g_elig_per_rows number := 0;
11 g_enrt_rt_rows number := 0;
12 g_enrt_prem_rows number := 0;
13 g_enrt_bnft_rows number := 0;
14 g_elctbl_chc_ctfn_rows number := 0;
15 g_elig_per_elctbl_chc_rows number := 0;
16 g_pil_elctbl_chc_popl_rows number := 0;
17 g_elig_dpnt_rows number := 0;
18 g_prtt_rt_rows number := 0;
19 g_prtt_enrt_actn_rows number := 0;
20 g_prtt_prem_rows number := 0;
21 g_ctfn_prvdd_rows number := 0;
22 g_elig_cvrd_dpnt_rows number := 0;
23 g_prtt_enrt_rslt_rows number := 0;
24 g_pl_bnf_rows number := 0;
25 g_prmry_care_rows number := 0;
26 g_per_in_ler_rows number := 0;
27 g_ptnl_ler_rows number := 0;
28 g_le_clsn_rows number := 0;
29
30 --
31 -- ==================================================================================
32 -- << Procedure: person_selection_rule >>
33 -- Description:
34 -- this procedure is called from 'process'. It calls the person selection rule.
35 -- this has been added to report errors for a person while executing the selection rule
36 -- and prevent the conc process from failing .
37 -- ==================================================================================
38 procedure person_selection_rule
39 (p_person_id in Number
40 ,p_business_group_id in Number
41 ,p_person_selection_rule_id in Number
42 ,p_effective_date in Date
43 ,p_input1 in varchar2 default null -- Bug 5331889
44 ,p_input1_value in varchar2 default null
45 ,p_return in out nocopy varchar2
46 ,p_err_message in out nocopy varchar2 ) as
47
48 Cursor c1 is
49 Select assignment_id
50 From per_assignments_f paf
51 Where paf.person_id = p_person_id
52 and paf.assignment_type <> 'C'
53 And paf.primary_flag = 'Y'
54 And paf.business_group_id = p_business_group_id
55 And p_effective_date between paf.effective_start_date and paf.effective_end_date ;
56 --
57 l_proc varchar2(80) := g_package||'.person_selection_rule';
58 l_outputs ff_exec.outputs_t;
59 --l_return varchar2(30);
60 l_assignment_id number;
61 l_actn varchar2(80);
62 value_exception exception ;
63 Begin
64 hr_utility.set_location ('Entering '||l_proc,10);
65 --
66 -- Get assignment ID form per_assignments_f table.
67 --
68 l_actn := 'Opening C1 Assignment cursor...';
69 open c1;
70 fetch c1 into l_assignment_id;
71 If c1%notfound then
72 close c1;
73 raise ben_batch_utils.g_record_error;
74 End if;
75 close c1;
76 -- Call formula initialise routine
77 --
78 l_actn := 'Calling benutils.formula procedure...';
79
80 l_outputs := benutils.formula
81 (p_formula_id => p_person_selection_rule_id
82 ,p_effective_date => p_effective_date
83 ,p_business_group_id => p_business_group_id
84 ,p_assignment_id => l_assignment_id
85 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
86 ,p_param1_value => to_char(p_person_id)
87 ,p_param2 => p_input1
88 ,p_param2_value => p_input1_value);
89 --
90 p_return := l_outputs(l_outputs.first).value;
91 --
92 l_actn := 'Evaluating benutils.formula return...';
93 --
94 If upper(p_return) not in ('Y', 'N') then
95 Raise value_exception ;
96 End if;
97 hr_utility.set_location ('Leaving '||l_proc,10);
98 Exception
99 When ben_batch_utils.g_record_error then
100 p_return := 'N' ;
101 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
102 fnd_message.set_token('ID' ,to_char(p_person_id) );
103 fnd_message.set_token('PROC',l_proc ) ;
104 p_err_message := fnd_message.get ;
105
106 When value_exception then
107 p_return := 'N' ;
108 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
109 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
110 fnd_message.set_token('PROC',l_proc ) ;
111 p_err_message := fnd_message.get ;
112
113 when others then
114 p_return := 'N' ;
115 p_err_message := 'Unhandled exception while processing Person : '||to_char(p_person_id)
116 ||' in package : '|| l_proc ||'.' || substr(sqlerrm,1,170);
117
118 End person_selection_rule;
119 --
120 --
121
122 procedure print_parameters
123 (p_thread_id in number
124 ,p_validate in varchar2
125 ,p_benefit_action_id in number
126 ,p_effective_date in date
127 ,p_business_group_id in number
128 ,p_person_id in number default hr_api.g_number
129 ,p_ler_id in number default hr_api.g_number
130 ,p_organization_id in number default hr_api.g_number
131 ,p_benfts_grp_id in number default hr_api.g_number
132 ,p_location_id in number default hr_api.g_number
133 ,p_legal_entity_id in number default hr_api.g_number
134 ,p_payroll_id in number default hr_api.g_number
135 ,p_person_selection_rule_id in number default hr_api.g_number
136 ,p_audit_log in varchar2 default hr_api.g_varchar2
137 ,p_from_ocrd_date in date default null
138 ,p_to_ocrd_date in date default null
139 ,p_life_evt_typ_cd in varchar2 default null
140 ,p_bckt_stat_cd in varchar2 default 'VOIDD'
141 ,p_delete_life_evt in varchar2 default 'N'
142 ,p_delete_ptnl_life_evt in varchar2 default 'N'
143 ) is
144 l_proc varchar2(80) := g_package||'.print_parameters';
145 l_string varchar2(80);
146 l_actn varchar2(80);
147 begin
148 hr_utility.set_location ('Entering '||l_proc,10);
149 ben_batch_utils.write(p_text => 'Runtime Parameters');
150 ben_batch_utils.write(p_text => '------------------');
151 ben_batch_utils.write(p_text => 'Thread ID :'||to_char(p_thread_id));
152 ben_batch_utils.write(p_text => 'Validation Mode :' ||
153 hr_general.decode_lookup('YES_NO',p_validate));
154 ben_batch_utils.write(p_text => 'Benefit Action ID :' ||
155 to_char(p_benefit_action_id));
156 ben_batch_utils.write(p_text => 'Effective Date :' ||
157 to_char(p_effective_date,'DD/MM/YYYY'));
158 ben_batch_utils.write(p_text =>'Business Group ID :' || p_business_group_id);
159 --
160 If (nvl(p_person_selection_rule_id,-1) <> hr_api.g_number) then
161 ben_batch_utils.write(p_text => 'Person Selection Rule :'||
162 benutils.iftrue
163 (p_expression => p_person_selection_rule_id is null
164 ,p_true => 'None'
165 ,p_false => p_person_selection_rule_id));
166 End if;
167 --
168 l_actn := 'Printing p_person_id...';
169 If (nvl(p_person_id,-1) <> hr_api.g_number) then
170 ben_batch_utils.write(p_text => 'Person ID :'||
171 benutils.iftrue
172 (p_expression => p_person_id is null
173 ,p_true => 'None'
174 ,p_false => p_person_id));
175 End if;
176 --
177 --
178 l_actn := 'Printing p_ler_id...';
179 If (nvl(p_ler_id,-1) <> hr_api.g_number) then
180 ben_batch_utils.write(p_text => 'Ler ID :'||
181 benutils.iftrue
182 (p_expression => p_ler_id is null
183 ,p_true => 'None'
184 ,p_false => p_ler_id));
185 End if;
186 --
187 l_actn := 'Printing p_organization_id...';
188 If (nvl(p_organization_id,-1) <> hr_api.g_number) then
189 ben_batch_utils.write(p_text => 'Organization ID :'||
190 benutils.iftrue
191 (p_expression => p_organization_id is null
192 ,p_true => 'None'
193 ,p_false => p_organization_id));
194 End if;
195 --
196 l_actn := 'Printing p_benfts_grp_id...';
197 If (nvl(p_benfts_grp_id,-1) <> hr_api.g_number) then
198 ben_batch_utils.write(p_text => 'Benefits Group ID :'||
199 benutils.iftrue
200 (p_expression => p_benfts_grp_id is null
201 ,p_true => 'None'
202 ,p_false => p_benfts_grp_id));
203 End if;
204 --
205 l_actn := 'Printing p_location_id...';
206 If (nvl(p_location_id,-1) <> hr_api.g_number) then
207 ben_batch_utils.write(p_text => 'Location ID :'||
208 benutils.iftrue
209 (p_expression => p_location_id is null
210 ,p_true => 'None'
211 ,p_false => p_location_id));
212 End if;
213 --
214 l_actn := 'Printing p_legal_entity_id...';
215 If (nvl(p_legal_entity_id,-1) <> hr_api.g_number) then
216 ben_batch_utils.write(p_text => 'Legal Entity ID :'||
217 benutils.iftrue
218 (p_expression => p_legal_entity_id is null
219 ,p_true => 'None'
220 ,p_false => p_legal_entity_id));
221 End if;
222 --
223 l_actn := 'Printing p_payroll_id...';
224 If (nvl(p_payroll_id,-1) <> hr_api.g_number) then
225 ben_batch_utils.write(p_text => 'Payroll ID :'||
226 benutils.iftrue
227 (p_expression => p_payroll_id is null
228 ,p_true => 'None'
229 ,p_false => p_payroll_id));
230 End if;
231 --
232 --
233 If p_life_evt_typ_cd is not null then
234 ben_batch_utils.write(p_text => 'Life Event Type Code :' ||p_life_evt_typ_cd);
235 end if;
236 --
237 if p_from_ocrd_date is not null then
238 ben_batch_utils.write(p_text => 'From Occurred Date :' || to_char(p_from_ocrd_date, 'DD/MM/YYYY'));
239 end if;
240 --
241 ben_batch_utils.write(p_text => 'To Occurred Date :' || to_char(p_to_ocrd_date, 'DD/MM/YYYY'));
242 ben_batch_utils.write(p_text => 'Backed Out Status Code :'||p_bckt_stat_cd);
243 ben_batch_utils.write(p_text => 'Delete Life Events :'||
244 hr_general.decode_lookup('YES_NO',p_delete_life_evt));
245
246 If (nvl(p_audit_log,'xxxx') <> hr_api.g_varchar2) then
247 ben_batch_utils.write(p_text => 'Audit log flag :'||
248 hr_general.decode_lookup('YES_NO',p_audit_log));
249 End if;
250 hr_utility.set_location ('Leaving '||l_proc,10);
251 exception
252 when others then
253 ben_batch_utils.rpt_error(p_proc => l_proc
254 ,p_last_actn => l_actn );
255 raise;
256 end print_parameters;
257 --
258 procedure process
259 (errbuf out nocopy varchar2
260 ,retcode out nocopy number
261 ,p_benefit_action_id in number
262 ,p_effective_date in varchar2
263 ,p_business_group_id in number
264 ,p_Person_id in number default NULL
265 ,p_Person_selection_rl in number default NULL
266 ,p_life_event_id in number default null
267 ,p_from_ocrd_date in varchar2 default null
268 ,p_to_ocrd_date in varchar2
269 ,p_organization_id in number default null
270 ,p_location_id in number default null
271 ,p_benfts_grp_id in number default null
272 ,p_legal_entity_id in number default null
273 ,p_payroll_id in number default null
274 ,p_life_evt_typ_cd in varchar2 default null
275 ,p_bckt_stat_cd in varchar2 default 'VOIDD'
276 ,p_audit_log_flag in varchar2 default 'N'
277 ,p_delete_life_evt in varchar2
278 ,p_delete_ptnl_life_evt in varchar2
279 )
280 is
281 --
282 -- Local variable declaration.
283 --
284 l_effective_date date;
285 l_person_ok varchar2(30) := 'Y';
286 l_person_actn_cnt number(15) := 0;
287 l_start_person_actn_id number(15);
288 l_end_person_actn_id number(15);
289 l_object_version_number number(15);
290 l_actn varchar2(80);
291 l_request_id number(15);
292 l_benefit_action_id number(15);
293 l_person_id number(15);
294 l_person_action_id number(15);
295 l_range_id number(15);
296 l_chunk_size number := 20;
297 l_chunk_num number := 1;
298 l_threads number(5) := 1;
299 l_num_ranges number := 0;
300 l_from_ocrd_date date;
301 l_to_ocrd_date date;
302 --
303 cursor c_person is
304 select ppf.person_id from per_all_people_f ppf
305 where (ppf.person_id = p_person_id or p_person_id is null)
306 and ppf.business_group_id = p_business_group_id
307 and l_effective_date between ppf.effective_start_date
308 and ppf.effective_end_date
309 and (p_organization_id is null
310 or exists (select null
311 from per_all_assignments_f paa
312 where paa.person_id = ppf.person_id
313 and l_effective_date
314 between paa.effective_start_date
315 and paa.effective_end_date
316 and paa.business_group_id = ppf.business_group_id
317 and paa.primary_flag = 'Y'
318 and paa.organization_id = p_organization_id))
319 and (p_location_id is null
320 or exists (select null
321 from per_all_assignments_f paa
322 where paa.person_id = ppf.person_id
323 and l_effective_date
324 between paa.effective_start_date
325 and paa.effective_end_date
326 and paa.business_group_id = ppf.business_group_id
327 and paa.primary_flag = 'Y'
328 and paa.location_id = p_location_id))
329 and (p_benfts_grp_id is null
330 or exists (select null
331 from per_all_people_f pap
332 where pap.person_id = ppf.person_id
333 and pap.business_group_id = ppf.business_group_id
334 and l_effective_date
335 between pap.effective_start_date
336 and pap.effective_end_date
337 and pap.benefit_group_id = p_benfts_grp_id))
338 and (p_legal_entity_id is null
339 or exists (select null
340 from per_assignments_f paf,
341 hr_soft_coding_keyflex soft
342 where paf.person_id = ppf.person_id
343 and paf.assignment_type <> 'C'
344 and l_effective_date
345 between paf.effective_start_date
346 and paf.effective_end_date
347 and paf.business_group_id = ppf.business_group_id
348 and paf.primary_flag = 'Y'
349 and soft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
350 and soft.segment1 = to_char(p_legal_entity_id)))
351 and (p_payroll_id is null
352 or exists (select null
353 from per_all_assignments_f paf,
354 pay_payrolls_f pay
355 where paf.person_id = ppf.person_id
356 and pay.payroll_id = paf.payroll_id
357 and paf.payroll_id = p_payroll_id
358 and paf.assignment_type <> 'C'
359 and paf.primary_flag = 'Y'
360 and l_effective_date
361 between paf.effective_start_date
362 and paf.effective_end_date
363 and l_effective_date
364 between pay.effective_start_date
365 and pay.effective_end_date));
366 --
367 l_proc varchar2(80) := g_package||'.process';
368 l_err_message varchar2(2000);
369 l_commit number;
370 Begin
371 --
372 hr_utility.set_location('Entering '||l_proc, 1);
373 /*
374 l_effective_date := to_date(p_effective_date
375 ,'YYYY/MM/DD HH24:MI:SS');
376 --
377 l_effective_date := to_date(to_char(trunc(l_effective_date)
378 ,'DD/MM/RRRR'),'DD/MM/RRRR');
379 */
380 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
381 l_from_ocrd_date:=trunc(fnd_date.canonical_to_date(p_from_ocrd_date));
382 l_to_ocrd_date:=trunc(fnd_date.canonical_to_date(p_to_ocrd_date));
383
384 -- Put row in fnd_sessions
385 --
386 dt_fndate.change_ses_date
387 (p_ses_date => l_effective_date,
388 p_commit => l_commit);
389
390 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
391 --
392 -- Get the parameters defined for the batch process.
393 --
394 benutils.get_parameter
395 (p_business_group_id => p_business_group_id
396 ,p_batch_exe_cd => 'BENPRBCK'
397 ,p_threads => l_threads
398 ,p_chunk_size => l_chunk_size
399 ,p_max_errors => g_max_person_err);
400 if p_benefit_action_id is null then
401 --
402 -- Create a new benefit_action row.
403 --
404 ben_benefit_actions_api.create_benefit_actions
405 (p_validate => FALSE
406 ,p_benefit_action_id => l_benefit_action_id
407 ,p_process_date => l_effective_date
408 ,p_person_id => p_person_id
409 ,p_mode_cd => 'P'
410 ,p_business_group_id => p_business_group_id
411 ,p_person_selection_rl => p_person_selection_rl
412 ,p_no_programs_flag => 'N'
413 ,p_no_plans_flag => 'N'
414 ,p_derivable_factors_flag => 'N'
415 ,p_validate_flag => 'N'
416 ,p_audit_log_flag => p_audit_log_flag
417 ,p_ler_id => p_life_event_id
418 ,p_date_from => l_from_ocrd_date --reuse
419 ,p_lf_evt_ocrd_dt => l_to_ocrd_date -- reuse
420 ,p_organization_id => p_organization_id
421 ,p_location_id => p_location_id
422 ,p_benfts_grp_id => p_benfts_grp_id
423 ,p_legal_entity_id => p_legal_entity_id
424 ,p_payroll_id => p_payroll_id
425 ,p_ptnl_ler_for_per_stat_cd => p_life_evt_typ_cd --reuse
426 ,p_elig_enrol_cd => p_bckt_stat_cd --reuse
427 ,p_debug_messages_flag => p_delete_life_evt --reuse
428 ,p_object_version_number => l_object_version_number
429 ,p_effective_date => l_effective_date
430 ,p_request_id => fnd_global.conc_request_id
431 ,p_program_application_id => fnd_global.prog_appl_id
432 ,p_program_id => fnd_global.conc_program_id
433 ,p_program_update_date => sysdate);
434 --
435 benutils.g_benefit_action_id := l_benefit_action_id;
436 --
437 benutils.g_thread_id := 99;
438 --
439 l_actn := 'Removing batch ranges ';
440 --
441 delete from ben_batch_ranges
442 where benefit_action_id = l_benefit_action_id;
443
444 hr_utility.set_location ('Before c_person',11);
445 for l_rec in c_person
446 loop
447 --
448 hr_utility.set_location (' c_person',11);
449 -- set variables for this iteration
450 --
451 l_person_ok := 'Y';
452 --
453 -- Check the person selection rule.
454 --
455 if p_person_selection_rl is not null then
456 --
457 person_selection_rule
458 (p_person_id => l_rec.person_id
459 ,p_business_group_id => p_business_group_id
460 ,p_person_selection_rule_id => p_person_selection_rl
461 ,p_effective_date => l_effective_date
462 ,p_return => l_person_ok
463 ,p_err_message => l_err_message );
464
465 if l_err_message is not null
466 then
467 Ben_batch_utils.write(p_text =>
468 '<< Person id : '||to_char(l_rec.person_id)||' failed.'||
469 ' Reason : '|| l_err_message ||' >>' );
470 end if ;
471 --
472 end if;
473 --
474 if l_person_ok = 'Y' then
475 --
476 -- Either no person sel rule or person selection rule passed. Create a
477 -- person action row.
478 --
479 ben_person_actions_api.create_person_actions
480 (p_validate => FALSE
481 ,p_person_action_id => l_person_action_id
482 ,p_person_id => l_rec.person_id
483 ,p_benefit_action_id => l_benefit_action_id
484 ,p_action_status_cd => 'U'
485 ,p_chunk_number => l_chunk_num
486 ,p_object_version_number => l_object_version_number
487 ,p_effective_date => l_effective_date);
488 --
489 -- increment the person action count
490 --
491 l_person_actn_cnt := l_person_actn_cnt + 1;
492 -- Set the ending person action id to the last person action id that got
493 -- created
494 --
495 l_end_person_actn_id := l_person_action_id;
496 --
497 -- We have to create batch ranges based on the number of person actions
498 -- created and the chunk size defined for the batch process.
499 --
500 if mod(l_person_actn_cnt, l_chunk_size) = 1 or l_chunk_size = 1 then
501 --
502 -- This is the first person action id in a new range.
503 --
504 l_start_person_actn_id := l_person_action_id;
505 --
506 end if;
507 --
508 if mod(l_person_actn_cnt, l_chunk_size) = 0 or l_chunk_size = 1 then
509 --
510 -- The number of person actions that got created equals the chunk
511 -- size. Create a batch range for the person actions.
512 --
513 ben_batch_ranges_api.create_batch_ranges
514 (p_validate => FALSE
515 ,p_effective_date => l_effective_date
516 ,p_benefit_action_id => l_benefit_action_id
517 ,p_range_id => l_range_id
518 ,p_range_status_cd => 'U'
519 ,p_starting_person_action_id => l_start_person_actn_id
520 ,p_ending_person_action_id => l_end_person_actn_id
521 ,p_object_version_number => l_object_version_number);
522 --
523 l_num_ranges := l_num_ranges + 1;
524 l_chunk_num := l_chunk_num + 1;
525 --
526 end if;
527 --
528 end if;
529 --
530 end loop;
531 --
532 -- There may be a few person actions left over from the loop above that may
533 -- not have got inserted into a batch range because the number was less than
534 -- the chunk size. Create a range for the remaining person actions. This
535 -- also applies when only one person gets selected.
536 --
537 if l_person_actn_cnt > 0 and
538 mod(l_person_actn_cnt, l_chunk_size) <> 0 then
539 --
540 ben_batch_ranges_api.create_batch_ranges
541 (p_validate => FALSE
542 ,p_effective_date => l_effective_date
543 ,p_benefit_action_id => l_benefit_action_id
544 ,p_range_id => l_range_id
545 ,p_range_status_cd => 'U'
546 ,p_starting_person_action_id => l_start_person_actn_id
547 ,p_ending_person_action_id => l_end_person_actn_id
548 ,p_object_version_number => l_object_version_number);
549 --
550 l_num_ranges := l_num_ranges + 1;
551 --
552 end if;
553 --
554 Else
555 --
556 -- Benefit action id is not null i.e. the batch process is being restarted
557 -- for a certain benefit action id. Create batch ranges and person actions
558 -- for restarting.
559 --
560 l_benefit_action_id := p_benefit_action_id;
561 --
562 hr_utility.set_location('Restarting for benefit action id : ' ||
563 to_char(l_benefit_action_id), 10);
564 ben_batch_utils.create_restart_person_actions
565 (p_benefit_action_id => p_benefit_action_id
566 ,p_effective_date => l_effective_date
567 ,p_chunk_size => l_chunk_size
568 ,p_threads => l_threads
569 ,p_num_ranges => l_num_ranges
570 ,p_num_persons => l_person_actn_cnt);
571 --
572 end if;
573 --
574 commit;
575 --
576 -- Submit requests to the concurrent manager based on the number of ranges
577 -- that got created.
578 --
579 if l_num_ranges > 1 then
580 --
581 hr_utility.set_location('More than one range got created.', 10);
582 --
583 --
584 -- Set the number of threads to the lesser of the defined number of threads
585 -- and the number of ranges created above. There's no point in submitting
586 -- 5 threads for only two ranges.
587 --
588 l_threads := least(l_threads, l_num_ranges);
589 --
590 for l_count in 1..(l_threads - 1)
591 loop
592 --
593 -- We are subtracting one from the number of threads because the main
594 -- process will act as the last thread and will be able to keep track of
595 -- the child requests that get submitted.
596 --
597 hr_utility.set_location('Submitting request ' || l_count, 10);
598 --
599 l_request_id := fnd_request.submit_request
600 (application => 'BEN'
601 ,program => 'BENPRTRD'
602 ,description => NULL
603 ,sub_request => FALSE
604 ,argument1 => l_benefit_action_id
605 ,argument2 => l_count
606 ,argument3 => p_effective_date
607 ,argument4 => p_business_group_id
608 ,argument5 => p_life_event_id
609 ,argument6 => p_from_ocrd_date
610 ,argument7 => p_to_ocrd_date
611 ,argument8 => p_organization_id
612 ,argument9 => p_location_id
613 ,argument10 => p_benfts_grp_id
614 ,argument11 => p_legal_entity_id
615 ,argument12 => p_payroll_id
616 ,argument13 => p_life_evt_typ_cd
617 ,argument14 => p_bckt_stat_cd
618 ,argument15 => p_audit_log_flag
619 ,argument16 => p_delete_life_evt
620 );
621 --
622 -- Store the request id of the concurrent request
623 --
624 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
625 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
626 := l_request_id;
627 commit;
628 --
629 end loop;
630 --
631 elsif l_num_ranges = 0 then
632 --
633 -- No ranges got created. i.e. no people got selected. Error out.
634 --
635 print_parameters
636 (p_thread_id => 99
637 ,p_validate => 'false'
638 ,p_benefit_action_id => l_benefit_action_id
639 ,p_business_group_id => p_business_group_id
640 ,p_effective_date => l_effective_date
641 ,p_person_id => p_person_id
642 ,p_person_selection_rule_id => p_person_selection_rl
643 );
644 --
645 ben_batch_utils.write(p_text =>
646 'No person got selected with above selection criteria.');
647 --
648 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
649 fnd_message.set_token('PROC',l_proc);
650 fnd_message.raise_error;
651 --
652 end if;
653 --
654 -- Carry on with the master. This will ensure that the master finishes last.
655 --
656 hr_utility.set_location('Submitting the master process', 10);
657 --
658 do_multithread
659 (errbuf => errbuf
660 ,retcode => retcode
661 ,p_benefit_action_id => l_benefit_action_id
662 ,p_thread_id => l_threads
663 ,p_effective_date => p_effective_date
664 ,p_business_group_id => p_business_group_id
665 ,p_life_event_id => p_life_event_id
666 ,p_from_ocrd_date => p_from_ocrd_date
667 ,p_to_ocrd_date => p_to_ocrd_date
668 ,p_organization_id => p_organization_id
669 ,p_location_id => p_location_id
670 ,p_benfts_grp_id => p_benfts_grp_id
671 ,p_legal_entity_id => p_legal_entity_id
672 ,p_payroll_id => p_payroll_id
673 ,p_life_evt_typ_cd => p_life_evt_typ_cd
674 ,p_bckt_stat_cd => p_bckt_stat_cd
675 ,p_audit_log_flag => p_audit_log_flag
676 ,p_delete_life_evt => p_delete_life_evt
677 );
678 --
679 -- Check if all the slave processes are finished.
680 --
681 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
682 --
683 --
684 benutils.write(p_text => benutils.g_banner_minus);
685 if p_delete_ptnl_life_evt = 'Y' and p_delete_life_evt = 'Y'
686 then
687 --
688 delete from ben_ptnl_ler_for_per
689 where ptnl_ler_for_per_stat_cd = 'VOIDD'
690 and business_group_id = p_business_group_id
691 and (p_life_event_id is null or ler_id = p_life_event_id)
692 and (p_person_id is null or person_id = p_person_id);
693
694 -- end if;
695 --
696 ben_batch_utils.write(p_text => ' No. of Voided Potentials Deleted = ' || sql%rowcount );
697 elsif p_delete_ptnl_life_evt = 'Y' and p_delete_life_evt = 'N'
698 then
699 If p_person_id is not null then
700 delete from ben_ptnl_ler_for_per p
701 where p.ptnl_ler_for_per_stat_cd = 'VOIDD'
702 and p.business_group_id = p_business_group_id
703 and p.person_id = p_person_id
704 and not exists ( select 1
705 from ben_per_in_ler pil
706 where pil.PTNL_LER_FOR_PER_ID = p.PTNL_LER_FOR_PER_ID);
707 else
708 delete from ben_ptnl_ler_for_per p
709 where p.ptnl_ler_for_per_stat_cd = 'VOIDD'
710 and p.business_group_id = p_business_group_id
711 and not exists ( select 1
712 from ben_per_in_ler pil
713 where pil.PTNL_LER_FOR_PER_ID = p.PTNL_LER_FOR_PER_ID);
714 end if;
715 --
716 ben_batch_utils.write(p_text => ' No. of Voided Potentials Deleted = ' || sql%rowcount );
717 --
718 end if;
719 --
720 benutils.write(p_text => benutils.g_banner_minus);
721 --
722 -- Time to delete orphaned data in key transation tables as this causes
723 -- application errors and customers expect this process to delete all such data.
724 -- dont delete PEN as it has lot of other child tables.
725 -- May be we shud plug in the ben post process calll here
726 --
727 delete from ben_elig_cvrd_dpnt_f t
728 where business_group_id = p_business_group_id
729 and not exists ( select 1 from ben_per_in_ler p
730 where p.per_in_ler_id = t.per_in_ler_id ) ;
731
732 delete from ben_pl_bnf_f t
733 where business_group_id = p_business_group_id
734 and not exists ( select 1 from ben_per_in_ler p
735 where p.per_in_ler_id = t.per_in_ler_id );
736 --
737 -- End the process.
738 --
739 ben_batch_utils.end_process
740 (p_benefit_action_id => l_benefit_action_id
741 ,p_person_selected => l_person_actn_cnt
742 ,p_business_group_id => p_business_group_id);
743 --
744 hr_utility.set_location ('Leaving ' || l_proc, 10);
745 --
746 commit;
747 --
748 exception
749 --
750 when others then
751 --
752 ben_batch_utils.rpt_error(p_proc => l_proc
753 ,p_last_actn => l_actn
754 ,p_rpt_flag => TRUE);
755 --
756 benutils.write(p_text => fnd_message.get);
757 benutils.write(p_text => sqlerrm);
758 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
759 --
760 if l_num_ranges > 0 then
761 --
762 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
763 --
764 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
765 ,p_person_selected => l_person_actn_cnt
766 ,p_business_group_id => p_business_group_id);
767 --
768 --submit_all_reports(p_audit_log => 'N');
769 --
770 end if;
771 --
772 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
773 fnd_message.set_token('PROCEDURE', l_proc);
774 fnd_message.set_token('STEP', l_actn );
775 fnd_message.raise_error;
776 --
777 end process;
778 --
779
780 procedure do_multithread
781 (errbuf out nocopy varchar2
782 ,retcode out nocopy number
783 ,p_benefit_action_id in number
784 ,p_thread_id in number
785 ,p_effective_date in varchar2
786 ,p_business_group_id in number
787 ,p_life_event_id in number default null
788 ,p_from_ocrd_date in varchar2 default null
789 ,p_to_ocrd_date in varchar2
790 ,p_organization_id in number default null
791 ,p_location_id in number default null
792 ,p_benfts_grp_id in number default null
793 ,p_legal_entity_id in number default null
794 ,p_payroll_id in number default null
795 ,p_life_evt_typ_cd in varchar2 default null
796 ,p_bckt_stat_cd in varchar2 default 'VOIDD'
797 ,p_audit_log_flag in varchar2 default 'N'
798 ,p_delete_life_evt in varchar2
799 )
800 is
801 --
802 -- Local variable declaration
803 --
804 l_effective_date date;
805 l_proc varchar2(80) := g_package || '.do_multithread';
806 l_person_id ben_person_actions.person_id%type;
807 l_person_action_id ben_person_actions.person_action_id%type;
808 l_object_version_number ben_person_actions.object_version_number%type;
809 l_ler_id ben_person_actions.ler_id%type;
810 l_range_id ben_batch_ranges.range_id%type;
811 l_record_number number := 0;
812 l_start_person_action_id number := 0;
813 l_end_person_action_id number := 0;
814 l_actn varchar2(80);
815 l_cnt number(5):= 0;
816 l_validate Boolean := FALSE;
817 l_chunk_size number;
818 l_threads number;
819 --
820 -- Cursor declarations
821 --
822 cursor c_range_thread
823 is
824 select ran.range_id
825 ,ran.starting_person_action_id
826 ,ran.ending_person_action_id
827 from ben_batch_ranges ran
828 where ran.range_status_cd = 'U'
829 and ran.benefit_action_id = p_benefit_action_id
830 and rownum < 2
831 for update of ran.range_status_cd;
832 --
833 cursor c_person_thread
834 is
835 select ben.person_id
836 ,ben.person_action_id
837 ,ben.object_version_number
838 from ben_person_actions ben
839 where ben.benefit_action_id = p_benefit_action_id
840 and ben.action_status_cd <> 'P'
841 and ben.person_action_id between l_start_person_action_id
842 and l_end_person_action_id
843 order by ben.person_action_id;
844 --
845 cursor c_parameter
846 is
847 select *
848 from ben_benefit_actions ben
849 where ben.benefit_action_id = p_benefit_action_id;
850 --
851 l_parm c_parameter%rowtype;
852 l_commit number;
853 --
854 -- start bug 3079317
855 l_rec benutils.g_active_life_event;
856 l_env ben_env_object.g_global_env_rec_type;
857 l_per_rec per_all_people_f%rowtype;
858 l_encoded_message varchar2(2000);
859 l_app_short_name varchar2(2000);
860 l_message_name varchar2(2000);
861 g_rec ben_type.g_report_rec;
862 l_from_ocrd_date date;
863 l_to_ocrd_date date;
864 --
865 -- end bug 3079317
866
867 begin
868 --
869 hr_utility.set_location ('Entering '||l_proc,10);
870 --
871 /*
872 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
873 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
874 ,'DD/MM/RRRR');
875 */
876 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
877 l_from_ocrd_date :=trunc(fnd_date.canonical_to_date(p_from_ocrd_date));
878 l_to_ocrd_date :=trunc(fnd_date.canonical_to_date(p_to_ocrd_date));
879 --
880 -- Put row in fnd_sessions
881 --
882 dt_fndate.change_ses_date
883 (p_ses_date => l_effective_date,
884 p_commit => l_commit);
885 --
886 l_actn := 'Calling benutils.get_parameter...';
887 benutils.get_parameter(p_business_group_id => p_business_group_id
888 ,p_batch_exe_cd => 'BENPRBCK'
889 ,p_threads => l_threads
890 ,p_chunk_size => l_chunk_size
891 ,p_max_errors => g_max_person_err);
892 --
893 -- Set up benefits environment
894 --
895 ben_env_object.init(p_business_group_id => p_business_group_id,
896 p_effective_date => l_effective_date,
897 p_thread_id => p_thread_id,
898 p_chunk_size => l_chunk_size,
899 p_threads => l_threads,
900 p_max_errors => g_max_person_err,
901 p_benefit_action_id => p_benefit_action_id);
902 --
903 g_persons_procd := 0;
904 g_persons_errored := 0;
905 g_elig_rows := 0;
906 g_elig_per_rows := 0;
907 g_enrt_rt_rows := 0;
908 g_enrt_prem_rows := 0;
909 g_enrt_bnft_rows := 0;
910 g_elctbl_chc_ctfn_rows := 0;
911 g_elig_per_elctbl_chc_rows := 0;
912 g_pil_elctbl_chc_popl_rows := 0;
913 g_elig_dpnt_rows := 0;
914 g_prtt_rt_rows := 0;
915 g_prtt_enrt_actn_rows := 0;
916 g_prtt_prem_rows := 0;
917 g_ctfn_prvdd_rows := 0;
918 g_elig_cvrd_dpnt_rows := 0;
919 g_prtt_enrt_rslt_rows := 0;
920 g_pl_bnf_rows := 0;
921 g_prmry_care_rows := 0;
922 g_per_in_ler_rows := 0;
923 g_ptnl_ler_rows := 0;
924 g_le_clsn_rows := 0;
925 --
926 ben_batch_utils.ini;
927 --
928 benutils.g_benefit_action_id := p_benefit_action_id;
929 benutils.g_thread_id := p_thread_id;
930 --
931 -- Fetch the parameters defined for the batch process.
932 --
933 open c_parameter;
934 fetch c_parameter into l_parm;
935 close c_parameter;
936 --
937 -- Print the parameters to the log file.
938 --
939 print_parameters
940 (p_thread_id => p_thread_id
941 ,p_benefit_action_id => p_benefit_action_id
942 ,p_validate => 'N'
943 ,p_business_group_id => p_business_group_id
944 ,p_effective_date => l_effective_date
945 ,p_person_id => l_parm.person_id
946 ,p_person_selection_rule_id => l_parm.person_selection_rl
947 ,p_location_id => l_parm.location_id
948 ,p_ler_id => l_parm.ler_id
949 ,p_organization_id => l_parm.organization_id
950 ,p_benfts_grp_id => l_parm.benfts_grp_id
951 ,p_legal_entity_id => l_parm.legal_entity_id
952 ,p_payroll_id => l_parm.payroll_id
953 ,p_from_ocrd_date => l_parm.date_from
954 ,p_to_ocrd_date => l_parm.lf_evt_ocrd_dt
955 ,p_life_evt_typ_cd => l_parm.ptnl_ler_for_per_stat_cd
956 ,p_bckt_stat_cd => l_parm.elig_enrol_cd
957 ,p_delete_life_evt => l_parm.debug_messages_flag
958 ,p_audit_log => p_audit_log_flag);
959 --
960 loop
961 --
962 open c_range_thread;
963 fetch c_range_thread into l_range_id,
964 l_start_person_action_id,
965 l_end_person_action_id;
966 --
967 exit when c_range_thread%notfound;
968 --
969 close c_range_thread;
970 --
971 -- Update the range status code to processed 'P'
972 --
973 update ben_batch_ranges ran
974 set ran.range_status_cd = 'P'
975 where ran.range_id = l_range_id;
976 --
977 hr_utility.set_location('Updated range ' || to_char(l_range_id) ||
978 ' status code to P', 10);
979 --
980 commit;
981 --
982 -- Remove all records from cache
983 --
984 g_cache_per_proc.delete;
985 --
986 open c_person_thread;
987 --
988 l_record_number := 0;
989 --
990 hr_utility.set_location('Load person actions into the cache', 10);
991 --
992 loop
993 --
994 fetch c_person_thread into
995 g_cache_per_proc(l_record_number+1).person_id
996 ,g_cache_per_proc(l_record_number+1).person_action_id
997 ,g_cache_per_proc(l_record_number+1).object_version_number
998 ;
999 --
1000 exit when c_person_thread%notfound;
1001 --
1002 l_record_number := l_record_number + 1;
1003 --
1004 l_actn := 'Updating person_ations.';
1005 --
1006 update ben_person_actions
1007 set action_status_cd = 'T'
1008 where person_action_id = l_person_action_id;
1009 --
1010 end loop;
1011 --
1012 close c_person_thread;
1013 --
1014 commit;
1015 --
1016 if l_record_number > 0 then
1017 --
1018 for l_cnt in 1..l_record_number
1019 loop
1020 --
1021 hr_utility.set_location('Purge rows for ' ||
1022 to_char(g_cache_per_proc(l_cnt).person_id), 10);
1023 --
1024 begin
1025 --
1026 ben_purge_bckdt_voided.purge_single_person
1027 (p_effective_date => l_effective_date
1028 ,p_business_group_id => p_business_group_id
1029 ,p_person_id => g_cache_per_proc(l_cnt).person_id
1030 ,p_life_event_id => p_life_event_id
1031 ,p_from_ocrd_date => l_from_ocrd_date
1032 ,p_to_ocrd_date => l_to_ocrd_date
1033 ,p_life_evt_typ_cd => p_life_evt_typ_cd
1034 ,p_bckt_stat_cd => p_bckt_stat_cd
1035 ,p_audit_log_flag => p_audit_log_flag
1036 ,p_delete_life_evt => p_delete_life_evt
1037 );
1038 g_persons_procd := g_persons_procd + 1;
1039
1040 exception
1041 --
1042 when others then
1043 --
1044 ben_env_object.setenv(p_lf_evt_ocrd_dt => l_effective_date);
1045 ben_env_object.get(p_rec => l_env);
1046 ben_person_object.get_object(p_person_id => g_cache_per_proc(l_cnt).person_id,
1047 p_rec => l_per_rec);
1048 --
1049 l_encoded_message := fnd_message.get_encoded;
1050 fnd_message.parse_encoded(encoded_message => l_encoded_message,
1051 app_short_name => l_app_short_name,
1052 message_name => l_message_name);
1053
1054 fnd_message.set_encoded(encoded_message => l_encoded_message);
1055 --
1056 g_rec.text := fnd_message.get ;
1057 --
1058 g_rec.error_message_code := nvl(l_message_name , nvl(g_rec.error_message_code,sqlcode));
1059 g_rec.text := nvl(g_rec.text , nvl(g_rec.text,substr(sqlerrm,1,400)) );
1060 g_rec.rep_typ_cd := 'ERROR';
1061 g_rec.person_id := g_cache_per_proc(l_cnt).person_id;
1062 benutils.write(p_text => g_rec.text);
1063 benutils.write(p_rec => g_rec);
1064 --
1065 update ben_person_actions
1066 set action_status_cd = 'E'
1067 where person_action_id = g_cache_per_proc(l_cnt).person_action_id;
1068 --
1069 g_persons_errored := g_persons_errored + 1;
1070 --
1071 if g_persons_errored > g_max_person_err then
1072 fnd_message.raise_error;
1073 end if;
1074 --
1075 end;
1076 --
1077 end loop;
1078 --
1079 else
1080 --
1081 hr_utility.set_location('No records found. Erroring out.', 10);
1082 --
1083 l_actn := 'Reporting error since there is no record found';
1084 --
1085 fnd_message.set_name('BEN','BEN_91906_PER_NOT_FND_IN_RNG');
1086 fnd_message.set_token('PROC',l_proc);
1087 fnd_message.set_token('BENEFIT_ACTION_ID',to_char(p_benefit_action_id));
1088 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
1089 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1090 fnd_message.raise_error;
1091 --
1092 end if;
1093 --
1094 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1095 --
1096 commit;
1097 --
1098 end loop;
1099 --
1100 benutils.write(p_text => benutils.g_banner_minus);
1101 benutils.write(p_text => 'Table Name No. Of Rows Deleted');
1102 benutils.write(p_text => '---------- -------------------');
1103 benutils.write(p_text => 'Ben_elig_per_f '||to_char(g_elig_rows));
1104 benutils.write(p_text => 'Ben_elig_per_opt_f '||to_char(g_elig_per_rows));
1105 benutils.write(p_text => 'Ben_enrt_rt '||to_char(g_enrt_rt_rows));
1106 benutils.write(p_text => 'Ben_enrt_prem '||to_char(g_enrt_prem_rows));
1107 benutils.write(p_text => 'Ben_enrt_bnft '||to_char(g_enrt_bnft_rows));
1108 benutils.write(p_text => 'Ben_elctbl_chc_ctfn '||to_char(g_elctbl_chc_ctfn_rows));
1109 benutils.write(p_text => 'Ben_elig_per_elctbl_chc '||to_char(g_elig_per_elctbl_chc_rows));
1110 benutils.write(p_text => 'Ben_pil_elctbl_chc_popl '||to_char(g_pil_elctbl_chc_popl_rows));
1111 benutils.write(p_text => 'Ben_elig_dpnt '||to_char(g_elig_dpnt_rows));
1112 benutils.write(p_text => 'Ben_prtt_rt_val '||to_char(g_prtt_rt_rows));
1113 benutils.write(p_text => 'Ben_prtt_enrt_actn_f '||to_char(g_prtt_enrt_actn_rows));
1114 benutils.write(p_text => 'Ben_prtt_prem_f '||to_char(g_prtt_prem_rows));
1115 benutils.write(p_text => 'Ben_prtt_enrt_ctfn_prvdd_f '||to_char(g_ctfn_prvdd_rows));
1116 benutils.write(p_text => 'Ben_elig_cvrd_dpnt_f '||to_char(g_elig_cvrd_dpnt_rows));
1117 benutils.write(p_text => 'Ben_prtt_enrt_rslt_f '||to_char(g_prtt_enrt_rslt_rows));
1118 benutils.write(p_text => 'Ben_pl_bnf_f '||to_char(g_pl_bnf_rows));
1119 benutils.write(p_text => 'Ben_prmry_care_prvdr_f '||to_char(g_prmry_care_rows));
1120 benutils.write(p_text => 'Ben_per_in_ler '||to_char(g_per_in_ler_rows));
1121 benutils.write(p_text => 'Ben_ptnl_ler_for_per '||to_char(g_ptnl_ler_rows));
1122 benutils.write(p_text => 'Ben_le_clsn_n_rstr '||to_char(g_le_clsn_rows));
1123 benutils.write(p_text => benutils.g_banner_minus);
1124 --
1125 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1126 --
1127 commit;
1128 --
1129 --
1130 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1131 ,p_num_pers_errored => g_persons_errored);
1132 --
1133 hr_utility.set_location ('Leaving '||l_proc,70);
1134 --
1135 exception
1136 --
1137 when others then
1138 --
1139 rollback;
1140 benutils.write(p_text => sqlerrm);
1141 --
1142 hr_utility.set_location('BENPRBCK Super Error ' || l_proc, 10);
1143 --
1144 ben_batch_utils.rpt_error(p_proc => l_proc
1145 ,p_last_actn => l_actn
1146 ,p_rpt_flag => TRUE);
1147 --
1148 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1149 ,p_num_pers_errored => g_persons_errored);
1150 --
1151 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1152 --
1153 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1154 fnd_message.set_token('PROCEDURE', l_proc);
1155 fnd_message.set_token('STEP',l_actn );
1156 fnd_message.raise_error;
1157 --
1158 end do_multithread;
1159 --
1160 -- -----------------------------------------------------------------------------
1161 -- |----------------------------< restart >------------------------------------|
1162 -- -----------------------------------------------------------------------------
1163 --
1164 procedure restart
1165 (errbuf out nocopy varchar2
1166 ,retcode out nocopy number
1167 ,p_benefit_action_id in number)
1168 is
1169 --
1170 -- Cursor Declaration
1171 --
1172 cursor c_parameters
1173 is
1174 select --to_char(process_date, 'YYYY/MM/DD HH24:MI:SS') process_date
1175 fnd_date.date_to_canonical(process_date) process_date
1176 ,business_group_id
1177 ,person_id
1178 ,person_selection_rl
1179 --,life_event_id
1180 --,from_ocrd_date
1181 ,lf_evt_ocrd_dt -- mapped to t_ocrd_date
1182 ,organization_id
1183 ,location_id
1184 ,benfts_grp_id
1185 ,legal_entity_id
1186 ,payroll_id
1187 ,CM_TRGR_TYP_CD -- mapped to lf_evt_typ_cd
1188 ,PTNL_LER_FOR_PER_STAT_CD -- mapped to bck_stat_cd
1189 ,audit_log_flag
1190 From ben_benefit_actions ben
1191 Where ben.benefit_action_id = p_benefit_action_id;
1192 --
1193 -- Local Variable declaration.
1194 --
1195 l_parameters c_parameters%rowtype;
1196 l_errbuf varchar2(80);
1197 l_retcode number;
1198 l_actn varchar2(80);
1199 --
1200 l_proc varchar2(80) := g_package||'.restart';
1201 --
1202 begin
1203 --
1204 hr_utility.set_location ('Entering '||l_proc,10);
1205 --
1206 -- get the parameters for a previous run and do a restart
1207 --
1208 open c_parameters;
1209 fetch c_parameters into l_parameters;
1210 --
1211 if c_parameters%notfound then
1212 --
1213 close c_parameters;
1214 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
1215 fnd_message.set_token('PROC',l_proc);
1216 fnd_message.raise_error;
1217 --
1218 end if;
1219 --
1220 close c_parameters;
1221 --
1222 -- Call the process procedure with parameters for restart
1223 --
1224 Process
1225 (errbuf => l_errbuf
1226 ,retcode => l_retcode
1227 ,p_benefit_action_id => p_benefit_action_id
1228 ,p_effective_date => l_parameters.process_date
1229 ,p_business_group_id => l_parameters.business_group_id
1230 ,p_person_id => l_parameters.person_id
1231 ,p_Person_selection_rl => l_parameters.Person_selection_rl
1232 --,p_life_event_id => l_parameters.life_event_id
1233 --,p_from_ocrd_date => l_parameters.
1234 ,p_to_ocrd_date => l_parameters.lf_evt_ocrd_dt
1235 ,p_organization_id => l_parameters.organization_id
1236 ,p_location_id => l_parameters.location_id
1237 ,p_benfts_grp_id => l_parameters.benfts_grp_id
1238 ,p_legal_entity_id => l_parameters.legal_entity_id
1239 ,p_payroll_id => l_parameters.payroll_id
1240 ,p_life_evt_typ_cd => l_parameters.CM_TRGR_TYP_CD
1241 ,p_bckt_stat_cd => l_parameters.PTNL_LER_FOR_PER_STAT_CD
1242 ,p_audit_log_flag => l_parameters.audit_log_flag
1243 );
1244 --
1245 hr_utility.set_location ('Leaving '||l_proc,70);
1246 --
1247 end restart;
1248 --
1249 procedure purge_single_person
1250 (p_effective_date in date
1251 ,p_business_group_id in number
1252 ,p_person_id in Number default NULL
1253 ,p_life_event_id in number default null
1254 ,p_from_ocrd_date in date default null
1255 ,p_to_ocrd_date in date
1256 ,p_life_evt_typ_cd in varchar2 default null
1257 ,p_bckt_stat_cd in varchar2 default 'VOIDD'
1258 ,p_audit_log_flag in varchar2 default 'N'
1259 ,p_delete_life_evt in varchar2
1260 )
1261 is
1262
1263 --
1264 l_from_ocrd_date date;
1265 --
1266 cursor c_per_in_ler_1 is
1267 select per_in_ler_id
1268 from ben_per_in_ler pil
1269 where pil.person_id = p_person_id
1270 and pil.business_group_id = p_business_group_id
1271 and pil.per_in_ler_stat_cd in ('BCKDT','VOIDD')
1272 and (p_life_event_id is null or pil.ler_id = p_life_event_id)
1273 and pil.lf_evt_ocrd_dt between l_from_ocrd_date and p_to_ocrd_date
1274 and (p_life_evt_typ_cd is null or
1275 exists (select null
1276 from ben_ler_f ler
1277 where ler.ler_id = pil.ler_id
1278 and ler.typ_cd = p_life_evt_typ_cd
1279 and pil.lf_evt_ocrd_dt between ler.effective_start_date and
1280 ler.effective_end_date));
1281 --
1282 cursor c_per_in_ler_2 is
1283 select per_in_ler_id
1284 from ben_per_in_ler pil
1285 where pil.person_id = p_person_id
1286 and pil.business_group_id = p_business_group_id
1287 and pil.per_in_ler_stat_cd in ('VOIDD')
1288 and (p_life_event_id is null or pil.ler_id = p_life_event_id)
1289 and pil.lf_evt_ocrd_dt between l_from_ocrd_date and p_to_ocrd_date
1290 and (p_life_evt_typ_cd is null or
1291 exists (select null
1292 from ben_ler_f ler
1293 where ler.ler_id = pil.ler_id
1294 and ler.typ_cd = p_life_evt_typ_cd
1295 and pil.lf_evt_ocrd_dt between ler.effective_start_date and
1296 ler.effective_end_date));
1297 --
1298 cursor c_elctbl_chc (p_per_in_ler_id number) is
1299 select elig_per_elctbl_chc_id
1300 from ben_elig_per_elctbl_chc epe
1301 where epe.per_in_ler_id = p_per_in_ler_id;
1302 --
1303 cursor c_prtt_enrt_rslt (p_per_in_ler_id number) is
1304 select prtt_enrt_rslt_id
1305 from ben_prtt_enrt_rslt_f pen
1306 where pen.per_in_ler_id = p_per_in_ler_id
1307 and pen.prtt_enrt_rslt_stat_cd in ('BCKDT','VOIDD');
1308 --
1309 type per_in_tab is table of ben_per_in_ler.per_in_ler_id%type
1310 index by binary_integer;
1311 t_per_in_ler per_in_tab;
1312 t_per_in_ler2 per_in_tab;
1313 t_elctbl_chc per_in_tab;
1314 t_rslt per_in_tab;
1315
1316 Begin
1317 --
1318 --hr_utility.trace_on(null,'ORACLE');
1319 hr_utility.set_location('Entering purge_single_person',1);
1320 l_from_ocrd_date := nvl(p_from_ocrd_date, to_date('01/01/1900','dd/mm/yyyy'));
1321 --
1322 open c_per_in_ler_1;
1323 fetch c_per_in_ler_1 bulk collect into t_per_in_ler;
1324 close c_per_in_ler_1;
1325 --
1326 if p_bckt_stat_cd = 'VOIDD' then
1327 open c_per_in_ler_2;
1328 fetch c_per_in_ler_2 bulk collect into t_per_in_ler2;
1329 close c_per_in_ler_2;
1330 t_per_in_ler := t_per_in_ler2; -- Added while fixing 3670708
1331 else
1332 t_per_in_ler2 := t_per_in_ler;
1333 end if;
1334 --
1335 hr_utility.set_location('delete elig per',2);
1336 if t_per_in_ler.count > 0 then
1337 --
1338 forall i in 1..t_per_in_ler.last
1339 --
1340 delete from ben_elig_per_f pep
1341 where pep.per_in_ler_id = t_per_in_ler(i);
1342 g_elig_rows := g_elig_rows + sql%rowcount;
1343 --
1344 forall i in 1..t_per_in_ler.last
1345 delete from ben_elig_per_opt_f epo
1346 where epo.per_in_ler_id = t_per_in_ler(i);
1347 g_elig_per_rows := g_elig_per_rows + sql%rowcount;
1348 --
1349 end if;
1350 --
1351 hr_utility.set_location('delete elig per',3);
1352 if t_per_in_ler2.count > 0 then
1353 --
1354 -- added here during bug fix 3670708
1355 forall i in 1..t_per_in_ler2.last
1356 delete from ben_pil_elctbl_chc_popl pel
1357 where pel.per_in_ler_id = t_per_in_ler2(i);
1358 g_pil_elctbl_chc_popl_rows := g_pil_elctbl_chc_popl_rows + sql%rowcount;
1359 --
1360 -- End of bug fix 3670708
1361
1362 for i in 1..t_per_in_ler2.last loop
1363 --
1364 t_elctbl_chc.delete;
1365 t_rslt.delete; -- Added while fixing 3670708
1366 --
1367 open c_elctbl_chc (t_per_in_ler2(i));
1368 fetch c_elctbl_chc bulk collect into t_elctbl_chc;
1369 close c_elctbl_chc;
1370 --
1371 open c_prtt_enrt_rslt (t_per_in_ler2(i));
1372 fetch c_prtt_enrt_rslt bulk collect into t_rslt;
1373 close c_prtt_enrt_rslt;
1374
1375 -- delete all the rows related to elig per elctbl choice for the voided life event
1376 if t_elctbl_chc.count > 0 then
1377 --
1378 hr_utility.set_location('delete enrt rt',1);
1379 forall i in 1..t_elctbl_chc.last
1380 delete from ben_enrt_rt
1381 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1382 g_enrt_rt_rows := g_enrt_rt_rows + sql%rowcount;
1383
1384 forall i in 1..t_elctbl_chc.last
1385 delete from ben_enrt_rt
1386 where enrt_bnft_id in
1387 (select enrt_bnft_id
1388 from ben_enrt_bnft
1389 where elig_per_elctbl_chc_id = t_elctbl_chc(i));
1390 g_enrt_rt_rows := g_enrt_rt_rows + sql%rowcount;
1391
1392 forall i in 1..t_elctbl_chc.last
1393 delete from ben_enrt_prem
1394 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1395 g_enrt_prem_rows := g_enrt_prem_rows + sql%rowcount;
1396
1397 forall i in 1..t_elctbl_chc.last
1398 delete from ben_enrt_prem
1399 where enrt_bnft_id in
1400 (select enrt_bnft_id
1401 from ben_enrt_bnft
1402 where elig_per_elctbl_chc_id = t_elctbl_chc(i));
1403 g_enrt_prem_rows := g_enrt_prem_rows + sql%rowcount;
1404
1405 forall i in 1..t_elctbl_chc.last
1406 delete from ben_elctbl_chc_ctfn
1407 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1408 g_elctbl_chc_ctfn_rows := g_elctbl_chc_ctfn_rows + sql%rowcount;
1409
1410 forall i in 1..t_elctbl_chc.last
1411 delete from ben_elctbl_chc_ctfn
1412 where enrt_bnft_id in
1413 (select enrt_bnft_id
1414 from ben_enrt_bnft
1415 where elig_per_elctbl_chc_id = t_elctbl_chc(i));
1416 g_elctbl_chc_ctfn_rows := g_elctbl_chc_ctfn_rows + sql%rowcount;
1417
1418 forall i in 1..t_elctbl_chc.last
1419 delete from ben_enrt_bnft
1420 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1421 g_enrt_bnft_rows := g_enrt_bnft_rows + sql%rowcount;
1422
1423 --
1424 -- Commented it out and deleting this thru per_in_ler above Bug 3670708
1425 /*
1426 forall i in 1..t_elctbl_chc.last
1427 delete from ben_pil_elctbl_chc_popl pel
1428 where pel.pil_elctbl_chc_popl_id =
1429 (select pil_elctbl_chc_popl_id
1430 from ben_elig_per_elctbl_chc
1431 where elig_per_elctbl_chc_id = t_elctbl_chc(i));
1432 g_pil_elctbl_chc_popl_rows := g_pil_elctbl_chc_popl_rows + sql%rowcount;
1433 */
1434
1435 --
1436 forall i in 1..t_elctbl_chc.last
1437 delete from ben_elig_per_elctbl_chc
1438 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1439 g_elig_per_elctbl_chc_rows := g_elig_per_elctbl_chc_rows + sql%rowcount;
1440 --
1441 forall i in 1..t_elctbl_chc.last
1442 delete from ben_elig_dpnt
1443 where elig_per_elctbl_chc_id = t_elctbl_chc(i);
1444 g_elig_dpnt_rows := g_elig_dpnt_rows + sql%rowcount;
1445
1446 end if;
1447 -- delete all the rows related to prtt_enrt_rslt for the voided life events
1448 if t_rslt.count > 0 then
1449 --
1450 forall i in 1..t_rslt.last
1451 --
1452 delete from ben_prtt_rt_val
1453 where prtt_enrt_rslt_id = t_rslt(i);
1454 g_prtt_rt_rows := g_prtt_rt_rows + sql%rowcount;
1455 --
1456 forall i in 1..t_rslt.last
1457 --
1458 delete from ben_prtt_enrt_actn_f
1459 where prtt_enrt_rslt_id = t_rslt(i);
1460 g_prtt_enrt_actn_rows := g_prtt_enrt_actn_rows + sql%rowcount;
1461 --
1462 forall i in 1..t_rslt.last
1463 --
1464 delete from ben_prtt_prem_f
1465 where prtt_enrt_rslt_id = t_rslt(i);
1466 g_prtt_prem_rows := g_prtt_prem_rows + sql%rowcount;
1467 --
1468 forall i in 1..t_rslt.last
1469 --
1470 delete from ben_prtt_enrt_ctfn_prvdd_f
1471 where prtt_enrt_rslt_id = t_rslt(i);
1472 g_ctfn_prvdd_rows := g_ctfn_prvdd_rows + sql%rowcount;
1473
1474 forall i in 1..t_rslt.last
1475 --
1476 delete from ben_elig_cvrd_dpnt_f
1477 where prtt_enrt_rslt_id = t_rslt(i);
1478 g_elig_cvrd_dpnt_rows := g_elig_cvrd_dpnt_rows + sql%rowcount;
1479 --
1480 forall i in 1..t_rslt.last
1481 --
1482 delete from ben_prtt_enrt_rslt_f
1483 where prtt_enrt_rslt_id = t_rslt(i);
1484 g_prtt_enrt_rslt_rows := g_prtt_enrt_rslt_rows + sql%rowcount;
1485 --
1486 forall i in 1..t_rslt.last
1487 --
1488 delete from ben_pl_bnf_f
1489 where prtt_enrt_rslt_id = t_rslt(i);
1490 g_pl_bnf_rows := g_pl_bnf_rows + sql%rowcount;
1491 --
1492 forall i in 1..t_rslt.last
1493 --
1494 delete from ben_prmry_care_prvdr_f
1495 where prtt_enrt_rslt_id = t_rslt(i);
1496 g_prmry_care_rows := g_prmry_care_rows + sql%rowcount;
1497 --
1498 end if;
1499 end loop;
1500 --
1501 forall i in 1..t_per_in_ler2.last
1502 delete from ben_le_clsn_n_rstr
1503 where per_in_ler_id = t_per_in_ler2(i);
1504 g_le_clsn_rows := g_le_clsn_rows + sql%rowcount;
1505 --
1506 if p_delete_life_evt = 'Y' then
1507 forall i in 1..t_per_in_ler2.last
1508 delete from ben_ptnl_ler_for_per
1509 where ptnl_ler_for_per_id = (select ptnl_ler_for_per_id
1510 from ben_per_in_ler pil
1511 where per_in_ler_id = t_per_in_ler2(i))
1512 and ptnl_ler_for_per_stat_cd = 'VOIDD';
1513 --
1514 forall i in 1..t_per_in_ler2.last
1515 delete from ben_per_in_ler
1516 where per_in_ler_id = t_per_in_ler2(i);
1517 end if;
1518 --
1519 end if;
1520 --
1521 commit;
1522 --
1523 hr_utility.set_location('Leaving purge_single_person',100);
1524 end ;
1525
1526 end ben_purge_bckdt_voided;