[Home] [Help]
PACKAGE BODY: APPS.BEN_CLS_UNRESOLVED_ACTN_ITEM
Source
1 package body ben_cls_unresolved_Actn_item as
2 /* $Header: benuneai.pkb 120.1 2005/06/06 11:12:35 ikasire noship $ */
3 --
4 -- Globle Type declaration
5 --
6 type g_actn_rec is record
7 (prtt_enrt_actn_id ben_prtt_enrt_actn_f.prtt_enrt_actn_id%type
8 ,cmpltd_dt ben_prtt_enrt_actn_f.cmpltd_dt%type
9 ,due_dt ben_prtt_enrt_actn_f.due_dt%type
10 ,rqd_flag ben_prtt_enrt_actn_f.rqd_flag%type
11 ,prtt_enrt_rslt_id ben_prtt_enrt_actn_f.prtt_enrt_rslt_id%type
12 ,actn_typ_id ben_prtt_enrt_actn_f.actn_typ_id%type
13 ,actn_cd varchar2(30)
14 ,effective_start_date date
15 ,effective_end_date date
16 );
17 type g_actn_table is table of g_actn_rec index by binary_integer;
18 --
19 type g_bnf_rec is record
20 (prtt_enrt_rslt_id number(15)
21 ,bnf_person_id number(15)
22 );
23 type g_bnf_table is table of g_bnf_rec index by binary_integer;
24 --
25 type g_cert_rec is record
26 (prtt_enrt_rslt_id number(15)
27 ,actn_typ_id number(15)
28 ,enrt_ctfn_recd_dt date
29 );
30 type g_cert_table is table of g_cert_rec index by binary_integer;
31 --
32 type g_dpnt_rec is record
33 (prtt_enrt_rslt_id number(15)
34 ,dpnt_person_id number(15)
35 ,cvg_strt_dt date
36 ,cvg_end_dt date
37 );
38 type g_dpnt_table is table of g_dpnt_rec index by binary_integer;
39 --
40 -- Globle variable declaration.
41 --
42 g_package varchar2(80) := 'ben_cls_unresolved_actn_item';
43 g_cache_per_proc g_cache_person_process_rec;
44 g_persons_procd integer := 0;
45 g_persons_errored integer := 0;
46 g_max_person_err integer := 100;
47 g_actn_cnt number := 0;
48 g_actn_tbl g_actn_table;
49 g_cert_cnt number := 0;
50 g_cert_tbl g_cert_table;
51 g_bnf_cnt number := 0;
52 g_bnf_tbl g_bnf_table;
53 g_dpnt_cnt binary_integer := 0;
54 g_dpnt_tbl g_dpnt_table;
55 g_person_actn_cnt number := 0;
56 --
57 -- ----------------------------------------------------------------------------
58 -- |---------------------< write_person_category >----------------------------|
59 -- ----------------------------------------------------------------------------
60 --
61 procedure write_person_category
62 (p_audit_log in varchar2 default 'N'
63 ,p_error in Boolean default FALSE
64 ,p_business_group_id in number
65 ,P_person_id in number
66 ,p_effective_date in date
67 )
68 is
69 --
70 l_proc varchar2(80) := g_package||'.write_person_category';
71 --
72 l_actn varchar2(80);
73 l_cache ben_batch_utils.g_comp_obj_table := ben_batch_utils.g_cache_comp;
74 l_cache_cnt binary_integer := ben_batch_utils.g_cache_comp_cnt;
75 l_category varchar2(30);
76 l_detail varchar2(132);
77 l_ovn number;
78 l_id number;
79 l_ovn1 varchar2(240);
80 l_actn_cd varchar2(30);
81 l_chg boolean := FALSE;
82 l_del boolean := FALSE;
83 --
84 Begin
85 --
86 hr_utility.set_location ('Entering '||l_proc,05);
87 --
88 If(p_error) then
89 --
90 If(p_audit_log = 'Y') then
91 --
92 l_category := 'ERROR_C';
93 l_detail := 'Error occur while Close action item';
94 --
95 l_actn := 'Calling ben_batch_utils.write_rec (ERROR_C)...';
96 Ben_batch_utils.write_rec(p_typ_cd => l_category
97 ,p_text => l_detail);
98 --
99 End if;
100 --
101 Else
102 --
103 l_actn := 'Determine person category...';
104 --
105 For i in 1..g_actn_cnt loop
106 --
107 If(g_actn_tbl(i).actn_cd in ('C','D')) then
108 l_chg := TRUE;
109 exit;
110 End if;
111 --
112 End loop;
113 --
114 If (not l_chg) then
115 --
116 l_category := 'ACTNNOACTN';
117 l_detail := 'Participants processed without action';
118 --
119 Else
120 --
121 For i in 1..l_cache_cnt loop
122 --
123 If(l_cache(i).actn_cd = 'D') then
124 l_del := TRUE;
125 exit;
126 End if;
127 --
128 End loop;
129 --
130 If (l_del) then
131 l_category := 'ACTNENRTDEL';
132 l_detail := 'Participant action closed(Enrollment deleted)';
133 Else
134 l_category := 'ACTNNOENRTDEL';
135 l_detail := 'Participant action closed(No enrollment deleted)';
136 End if;
137 --
138 End if;
139 --
140 l_actn := 'Calling ben_batch_utils.write_rec...';
141 Ben_batch_utils.write_rec(p_typ_cd => l_category
142 ,p_text => l_detail);
143 --
144 End if;
145 --
146 If (not p_error and p_audit_log = 'Y') then
147 --
148 For i in 1..l_cache_cnt loop
149 --
150 l_actn := 'Calling ben_batch_rate_info_api.create_batch_rate_info...';
151 --
152 ben_batch_rate_info_api.create_batch_rate_info
153 (p_batch_rt_id => l_id
154 ,p_benefit_action_id => benutils.g_benefit_action_id
155 ,p_person_id => p_person_id
156 ,p_pgm_id => l_cache(i).pgm_id
157 ,p_pl_id => l_cache(i).pl_id
158 ,p_oipl_id => l_cache(i).oipl_id
159 ,p_dflt_val => l_cache(i).bnft_amt
160 ,p_val => l_cache(i).prtt_enrt_rslt_id
161 ,p_enrt_cvg_strt_dt => l_cache(i).cvg_strt_dt -- Bug 4386646
162 ,p_enrt_cvg_thru_dt => l_cache(i).cvg_thru_dt -- Bug 4386646
163 ,p_actn_cd => l_actn_cd
164 ,p_dflt_flag => 'Y'
165 ,p_business_group_id => p_business_group_id
166 ,p_effective_date => p_effective_date
167 ,p_object_version_number => l_OVN
168 );
169 --
170 End loop;
171 --
172 For i in 1..g_dpnt_cnt loop
173 --
174 l_actn := 'Calling ben_batch_dpnt_info_api.create_batch_dpnt_info...';
175 --
176 ben_batch_dpnt_info_api.create_batch_dpnt_info
177 (p_batch_dpnt_id => l_id
178 ,p_person_id => p_person_id
179 ,p_benefit_action_id => benutils.g_benefit_action_id
180 ,p_business_group_id => p_business_group_id
181 ,p_enrt_cvg_strt_dt => g_dpnt_tbl(i).cvg_strt_dt
182 ,p_enrt_cvg_thru_dt => g_dpnt_tbl(i).cvg_end_dt
183 ,p_actn_cd => to_char(g_dpnt_tbl(i).prtt_enrt_rslt_id)
184 ,p_object_version_number => l_OVN1
185 ,p_dpnt_person_id => g_dpnt_tbl(i).dpnt_person_id
186 ,p_effective_date => p_effective_date
187 );
188 --
189 End loop;
190 --
191 For i in 1..g_actn_cnt loop
192 --
193 l_actn := 'Calling create_batch_actn_item_info...';
194 ben_batch_actn_item_info_api.create_batch_actn_item_info
195 (p_batch_actn_item_id => l_id
196 ,p_benefit_action_id => benutils.g_benefit_action_id
197 ,p_person_id => p_person_id
198 ,p_actn_typ_id => g_actn_tbl(i).actn_typ_id
199 ,p_cmpltd_dt => g_actn_tbl(i).cmpltd_dt
200 ,p_due_dt => g_actn_tbl(i).due_dt
201 ,p_rqd_flag => g_actn_tbl(i).rqd_flag
202 ,p_actn_cd => g_actn_tbl(i).actn_cd ||
203 to_char(g_actn_tbl(i).prtt_enrt_rslt_id)
204 ,p_business_group_id => p_business_group_id
205 ,p_object_version_number => l_ovn
206 ,p_effective_date => p_effective_date
207 );
208 --
209 End loop;
210 --
211 For i in 1..g_cert_cnt loop
212 --
213 l_actn := 'Calling create_batch_bnft_cert_info (Certification)...';
214 --
215 ben_batch_bnft_cert_info_api.create_batch_bnft_cert_info
216 (p_batch_benft_cert_id => l_id
217 ,p_benefit_action_id => benutils.g_benefit_action_id
218 ,p_person_id => p_person_id
219 ,p_actn_typ_id => g_cert_tbl(i).actn_typ_id
220 ,p_typ_cd => 'C' ||
221 to_char(g_cert_tbl(i).prtt_enrt_rslt_id)
222 ,p_enrt_ctfn_recd_dt => g_cert_tbl(i).enrt_ctfn_recd_dt
223 ,p_object_version_number => l_ovn
224 ,p_effective_date => p_effective_date
225 );
226 --
227 End loop;
228 --
229 For i in 1..g_bnf_cnt loop
230 --
231 l_actn := 'Calling create_batch_bnft_cert_info (Beneficiary)...';
232 --
233 ben_batch_bnft_cert_info_api.create_batch_bnft_cert_info
234 (p_batch_benft_cert_id => l_id
235 ,p_benefit_action_id => benutils.g_benefit_action_id
236 ,p_person_id => p_person_id
237 ,p_typ_cd => 'B' ||
238 to_char(g_bnf_tbl(i).prtt_enrt_rslt_id)
239 ,p_actn_typ_id => g_bnf_tbl(i).bnf_person_id
240 ,p_object_version_number => l_ovn
241 ,p_effective_date => p_effective_date
242 );
243 --
244 End loop;
245 --
246 End if;
247 --
248 hr_utility.set_location ('Leaving '||l_proc, 10);
249 --
250 Exception
251 --
252 When others then
253 ben_batch_utils.rpt_error(p_proc => l_proc
254 ,p_last_actn => l_actn
255 ,p_rpt_flag => TRUE
256 );
257 raise;
258 --
259 End write_person_category;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |-----------------------< submit_all_reports >-----------------------------|
263 -- ----------------------------------------------------------------------------
264 --
265 Procedure Submit_all_reports
266 (p_rpt_flag in Boolean default FALSE
267 ,p_audit_log in varchar2 default 'N')
268 is
269 --
270 l_proc varchar2(80) := g_package||'.submit_all_reports';
271 l_actn varchar2(80);
272 l_request_id number;
273 --
274 begin
275 --
276 hr_utility.set_location ('Entering '||l_proc,05);
277 --
278 l_actn := 'Calling ben_batch_utils.batch_report (BENUAAUD)...';
279 --
280 If fnd_global.conc_request_id <> -1 then
281 --
282 If(p_audit_log = 'Y') then
283 --
284 ben_batch_utils.batch_report
285 (p_concurrent_request_id => fnd_global.conc_request_id
286 ,p_program_name => 'BENUAAUD'
287 ,p_request_id => l_request_id);
288 --
289 End if;
290 --
291 l_actn := 'Calling ben_batch_utils.batch_report (BENUASUM)...';
292 --
293 ben_batch_utils.batch_report
294 (p_concurrent_request_id => fnd_global.conc_request_id
295 ,p_program_name => 'BENUASUM'
296 ,p_request_id => l_request_id
297 );
298 --
299 -- Submit the generic error by error type and error by person reports.
300 --
301 ben_batch_reporting.batch_reports
302 (p_concurrent_request_id => fnd_global.conc_request_id
303 ,p_report_type => 'ERROR_BY_ERROR_TYPE');
304 --
305 ben_batch_reporting.batch_reports
306 (p_concurrent_request_id => fnd_global.conc_request_id
307 ,p_report_type => 'ERROR_BY_PERSON');
308 --
309 End if;
310 --
311 hr_utility.set_location ('Leaving '||l_proc,10);
312 --
313 Exception
314 --
315 When others then
316 ben_batch_utils.rpt_error(p_proc => l_proc
317 ,p_last_actn => l_actn
318 ,p_rpt_flag => p_rpt_flag
319 );
320 raise;
321 End Submit_all_reports;
322 --
323 -- ----------------------------------------------------------------------------
324 -- |---------------------< remove_prtt_actn >----------------------------|
325 -- ----------------------------------------------------------------------------
326 --
327 procedure remove_prtt_actn
328 (p_prtt_enrt_actn_id in number
329 ,p_business_group_id in number
330 ,p_effective_date in date
331 ,p_datetrack_mode in varchar2
332 ,p_object_version_number in out nocopy number
333 ,p_prtt_enrt_rslt_id in number
334 ,p_rslt_object_version_number in out nocopy number
335 ,p_unsuspend_enrt_flag in varchar2
336 ,p_effective_start_date in out nocopy date
337 ,p_effective_end_date in out nocopy date
338 ,p_batch_flag in boolean
339 ,p_audit_log in varchar2 default 'N'
340 )
341 is
342 --
343 l_proc varchar2(80) := g_package||'.Remove_Prtt_Actn';
344 l_actn varchar2(80);
345
346 -- For nocopy changes
347 l_object_version_number number := p_object_version_number;
348 l_rslt_object_version_number number := p_object_version_number;
349 l_effective_start_date date := p_effective_start_date;
350 l_effective_end_date date := p_effective_end_date;
351 --
352 begin
353 --
354 hr_utility.set_location ('Entering '||l_proc,5);
355 --
356 --
357 l_actn := 'Calling delete_prtt_enrt_actn(' || to_char(p_prtt_enrt_actn_id)
358 || ')...' ;
359 --
360 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
361 (p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
362 ,p_business_group_id => p_business_group_id
363 ,p_effective_date => p_effective_date
364 ,p_datetrack_mode => p_datetrack_mode
365 ,p_object_version_number => p_object_version_number
366 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
367 ,p_rslt_object_version_number => p_rslt_object_version_number
368 ,p_unsuspend_enrt_flag => p_unsuspend_enrt_flag
369 ,p_effective_start_date => p_effective_start_date
370 ,p_effective_end_date => p_effective_end_date
371 );
372 --
373 hr_utility.set_location ('Leaving '||l_proc,10);
374 --
375 Exception
376 --
377 When others then
378 --
379 ben_batch_utils.rpt_error(p_proc => l_proc
380 ,p_last_actn => l_actn
381 ,p_rpt_flag => p_batch_flag
382 );
383 -- For nocopy changes
384 p_object_version_number := l_object_version_number;
385 p_rslt_object_version_number := l_object_version_number;
386 p_effective_start_date := l_effective_start_date;
387 p_effective_end_date := l_effective_end_date;
388
389 raise;
390 --
391 End;
392 --
393 -- ----------------------------------------------------------------------------
394 -- |----------------------------< ld_dpnt >-----------------------------------|
395 -- ----------------------------------------------------------------------------
396 --
397 procedure ld_dpnt
398 (p_business_group_id in number
399 ,p_effective_date in date
400 ,p_prtt_enrt_rslt_id in number)
401 is
402 --
403 cursor c1
404 is
405 select ecd.prtt_enrt_rslt_id,
406 ecd.dpnt_person_id,
407 ecd.cvg_strt_dt,
408 ecd.cvg_thru_dt
409 from ben_elig_cvrd_dpnt_f ecd,
410 ben_per_in_ler pil
411 where ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
412 and ecd.business_group_id = p_business_group_id
413 and p_effective_date between ecd.effective_start_date
414 and ecd.effective_end_date
415 and pil.per_in_ler_id=ecd.per_in_ler_id
416 and pil.business_group_id=ecd.business_group_id
417 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
418 --
419 l_proc varchar2(80) := g_package||'.ld_dpnt';
420 l_actn varchar2(80);
421 --
422 Begin
423 --
424 hr_utility.set_location ('Entering '||l_proc,05);
425 --
426 l_actn := 'Entering ld_dpnt...';
427 --
428 For rec in c1 loop
429 l_actn := 'Loading dpnt...';
430 g_dpnt_cnt := g_dpnt_cnt + 1;
431 g_dpnt_tbl(g_dpnt_cnt) := rec;
432 End loop;
433 --
434 hr_utility.set_location ('Leaving '||l_proc,10);
435 --
436 Exception
437 --
438 When others then
439 ben_batch_utils.rpt_error(p_proc => l_proc
440 ,p_last_actn => l_actn
441 ,p_rpt_flag => TRUE
442 );
443 raise;
444 --
445 End ld_dpnt;
446 --
447 -- ----------------------------------------------------------------------------
448 -- |----------------------------< ld_bnf >------------------------------------|
449 -- ----------------------------------------------------------------------------
450 --
451 Procedure ld_bnf
452 (p_business_group_id in number
453 ,p_effective_date in date
454 ,p_prtt_enrt_rslt_id in number)
455 is
456 --
457 cursor c1
458 is
459 select bnf.prtt_enrt_rslt_id, bnf.bnf_person_id
460 from ben_pl_bnf_f bnf,
461 ben_per_in_ler pil
462 where bnf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
463 and bnf.business_group_id= p_business_group_id
464 and p_effective_date between bnf.effective_start_date
465 and bnf.effective_end_date
466 and pil.per_in_ler_id=bnf.per_in_ler_id
467 and pil.business_group_id=bnf.business_group_id
468 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
469 --
470 l_proc varchar2(80) := g_package||'.ld_bnf';
471 l_actn varchar2(80);
472 --
473 Begin
474 hr_utility.set_location ('Entering '||l_proc,05);
475 l_actn := 'Entering ld_bnf...';
476 For rec in c1 loop
477 l_actn := 'Loading beneficiary...';
478 g_bnf_cnt := g_bnf_cnt + 1;
479 g_bnf_tbl(g_bnf_cnt) := rec;
480 End loop;
481 hr_utility.set_location ('Leaving '||l_proc,10);
482 Exception
483 When others then
484 ben_batch_utils.rpt_error(p_proc => l_proc
485 ,p_last_actn => l_actn
486 ,p_rpt_flag => TRUE
487 );
488 raise;
489 End ld_bnf;
490 --
491 -- ----------------------------------------------------------------------------
492 -- |----------------------------< ld_cert >-----------------------------------|
493 -- ----------------------------------------------------------------------------
494 --
495 Procedure ld_cert(p_business_group_id in number
496 ,p_effective_date in date
497 ,p_prtt_enrt_rslt_id in number
498 ) is
499 --
500 Cursor c1 is
501 Select a.prtt_enrt_rslt_id,
502 b.actn_typ_id,
503 a.enrt_ctfn_recd_dt
504 from ben_prtt_enrt_ctfn_prvdd_f a
505 ,ben_prtt_enrt_actn_f b
506 ,ben_per_in_ler pil
507 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
508 and a.business_group_id= p_business_group_Id
509 and p_effective_date between
510 a.effective_start_date and a.effective_start_date
511 and b.prtt_enrt_actn_id = a.prtt_enrt_actn_id
512 and p_effective_date between
513 b.effective_start_date and b.effective_start_date
514 and pil.per_in_ler_id=b.per_in_ler_id
515 and pil.business_group_id=b.business_group_id
516 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
517 ;
518 --
519 l_proc varchar2(80) := g_package||'.ld_cert';
520 l_actn varchar2(80);
521 Begin
522 hr_utility.set_location ('Entering '||l_proc,05);
523 l_actn := 'Entering ld_cert...';
524 For rec in c1 loop
525 l_actn := 'Loading Certicication...';
526 g_cert_cnt := g_cert_cnt + 1;
527 g_cert_tbl(g_cert_cnt) := rec;
528 End loop;
529 hr_utility.set_location ('Leaving '||l_proc,10);
530 Exception
531 When others then
532 ben_batch_utils.rpt_error(p_proc => l_proc
533 ,p_last_actn => l_actn
534 ,p_rpt_flag => TRUE
535 );
536 raise;
537 End ld_cert;
538 --
539 -- ----------------------------------------------------------------------------
540 -- |----------------------------< ld_actn >-----------------------------------|
541 -- ----------------------------------------------------------------------------
542 --
543 procedure ld_actn
544 (p_business_group_id in number
545 ,p_effective_date in date
546 ,p_prtt_enrt_rslt_id in number
547 ,p_before in Boolean
548 ,p_after in Boolean
549 ,p_idx_b in out nocopy binary_integer
550 ,p_idx_e in out nocopy binary_integer
551 )
552 is
553 --
554 cursor c1
555 is
556 select pea.prtt_enrt_actn_id
557 ,pea.cmpltd_dt
558 ,pea.due_dt
559 ,pea.rqd_flag
560 ,pea.prtt_enrt_rslt_id
561 ,pea.actn_typ_id
562 ,'N' actn_cd
563 ,pea.effective_start_date
564 ,pea.effective_end_date
565 from ben_prtt_enrt_actn_f pea,
566 ben_per_in_ler pil
567 where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
568 and pea.business_group_id = p_business_group_id
569 and p_effective_date between pea.effective_start_date
570 and pea.effective_end_date
571 and pil.per_in_ler_id=pea.per_in_ler_id
572 and pil.business_group_id=pea.business_group_id
573 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
574 order by pea.prtt_enrt_rslt_id, pea.prtt_enrt_actn_id
575 ;
576 l_proc varchar2(80) := g_package||'.ld_actn';
577 l_actn varchar2(80);
578 i binary_integer := 0;
579 l_fnd boolean;
580 l_idx_b binary_integer := p_idx_b ;
581 l_idx_e binary_integer:= p_idx_e;
582
583 Begin
584 hr_utility.set_location ('Entering '||l_proc,05);
585 If(p_before) then
586 --
587 l_actn := 'Load actn item (Before)...';
588 p_idx_b := g_actn_cnt;
589 For rec1 in c1 loop
590 g_actn_cnt := g_actn_cnt + 1;
591 g_actn_tbl(g_actn_cnt) := rec1;
592 End loop;
593 p_idx_e:= g_actn_cnt;
594 Elsif(p_after) then
595 --
596 l_actn := 'Compae actn item (After)...';
597 For rec2 in c1 loop
598 l_fnd := FALSE;
599 For i in p_idx_b+1..p_idx_e loop
600 If (rec2.prtt_enrt_actn_id = g_actn_tbl(i).prtt_enrt_actn_id) then
601 If(rec2.effective_end_date = p_effective_date) then
602 g_actn_tbl(i).actn_cd := 'D';
603 Elsif(rec2.cmpltd_dt is not null and g_actn_tbl(i).cmpltd_dt is NULL)
604 then
605 g_actn_tbl(i).actn_cd := 'C';
606 End if;
607 l_fnd := TRUE;
608 Exit;
609 End if;
610 End loop;
611 -- * Handle actn item get Zapped.
612 If (not l_fnd) then
613 g_actn_tbl(i).actn_cd := 'D';
614 End if;
615 End loop;
616 End if;
617 hr_utility.set_location ('Leaving '||l_proc,10);
618 Exception
619 When others then
620 ben_batch_utils.rpt_error(p_proc => l_proc
621 ,p_last_actn => l_actn
622 ,p_rpt_flag => TRUE
623 );
624 -- for nocopy changes
625 p_idx_b := l_idx_b ;
626 p_idx_e := l_idx_e;
627 raise;
628 End ld_actn;
629 --
630 -- ----------------------------------------------------------------------------
631 -- |-------------------< cls_per_unresolved_actn_item >-----------------------|
632 -- ----------------------------------------------------------------------------
633 --
634 -- This procedure is called to close any unresolved action items for a person.
635 --
636 procedure cls_per_unresolved_actn_item
637 (p_person_id in number
638 ,p_effective_date in date
639 ,p_business_group_id in number
640 ,p_overwrite_flag in boolean default FALSE
641 ,p_batch_flag in boolean default FALSE
642 ,p_validate in boolean default FALSE
643 ,p_person_action_id in Number default NULL
644 ,p_object_version_number in Number default NULL
645 ,p_audit_log in varchar2 default 'N'
646 )
647 is
648 --
649 -- Local Cursor/record variables
650 --
651 cursor c_pen
652 is
653 select pen.prtt_enrt_rslt_id
654 ,pen.effective_start_date
655 ,pen.effective_end_date
656 ,pen.business_group_id
657 ,pen.person_id
658 ,pen.rplcs_sspndd_rslt_id
659 ,pen.sspndd_flag
660 ,pen.object_version_number
661 ,pen.per_in_ler_id
662 ,'N' skip
663 from ben_prtt_enrt_rslt_f pen
664 where pen.person_id = p_person_id
665 and pen.business_group_id = p_business_group_id
666 and pen.prtt_enrt_rslt_stat_cd is null
667 and exists (select null
668 from ben_prtt_enrt_actn_f pea
669 where pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
670 and pea.cmpltd_dt is null
671 and p_effective_date between pea.effective_start_date
672 and pea.effective_end_date)
673 and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
674 and p_effective_date between pen.effective_start_date
675 and pen.effective_end_date -1
676 and ( pen.effective_end_date = hr_api.g_eot or --Bug 4398840
677 not exists (select 'x' from ben_prtt_enrt_rslt_f pen1 -- to exclude the ended result
678 where pen1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
679 and pen1.effective_end_date = hr_api.g_eot
680 and pen1.enrt_cvg_thru_dt <> hr_api.g_eot
681 )
682 )
683 and pen.effective_end_date >= pen.enrt_cvg_strt_dt;
684 --
685 type g_pen_record is table of c_pen%rowtype index by binary_integer;
686 --
687 -- Get all open actions for the prtt_enrt_rslt_id.
688 --
689 cursor c_actn (c_prtt_enrt_rslt_id number)
690 is
691 select b.prtt_enrt_actn_id
692 ,b.due_dt
693 ,b.cmpltd_dt
694 ,b.rqd_flag
695 ,b.prtt_enrt_rslt_id
696 ,b.actn_typ_id
697 ,b.effective_start_date
698 ,b.effective_end_date
699 ,b.object_version_number
700 from ben_prtt_enrt_actn_f b,
701 ben_per_in_ler pil
702 where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
703 and b.business_group_id = p_business_group_id
704 and b.cmpltd_dt is NULL
705 and p_effective_date between b.effective_start_date
706 and b.effective_end_date
707 and pil.per_in_ler_id=b.per_in_ler_id
708 and pil.business_group_id=b.business_group_id
709 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
710 ;
711 --
712 -- Get minimum due date of all open, required actions for a result
713 --
714 cursor c_actn_min (c_prtt_enrt_rslt_id number)
715 is
716 select min(nvl(b.due_dt,hr_api.g_eot)) due_dt
717 from ben_prtt_enrt_actn_f b,
718 ben_per_in_ler pil
719 where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
720 and b.business_group_id = p_business_group_id
721 and b.cmpltd_dt is NULL
722 and b.rqd_flag = 'Y'
723 and p_effective_date between b.effective_start_date
724 and b.effective_end_date
725 and pil.per_in_ler_id=b.per_in_ler_id
726 and pil.business_group_id=b.business_group_id
727 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
728 ;
729 --
730 -- Record structures for preliminary processing.
731 --
732 l_enrt_rec g_pen_record; -- Enrollment results
733 l_enrt_cnt number := 0;
734 l_intr_rec g_pen_record; -- Interim Enrollments.
735 l_intr_cnt number := 0;
736 l_process_rec g_pen_record; -- Enrt results minus interim results
737 l_process_cnt number := 0;
738 --
739 -- Local Variables.
740 --
741 l_proc Varchar2(80) := g_package||'.cls_per_unresolved_actn_item';
742 --
743 l_actn Varchar2(80);
744 l_actn_cd Varchar2(30) := 'N';
745 l_set Boolean := FALSE;
746 l_found Boolean := FALSE;
747 l_del_flag Boolean := FALSE;
748 l_dump_boolean Boolean := FALSE;
749 l_dump_number number := 0;
750 l_due_dt date;
751 l_suspend_flag varchar2(30) ;
752 l_output_string varchar2(132);
753 l_idx_b Binary_integer := 0;
754 l_idx_e Binary_integer := 0;
755 l_object_version_number Number := p_object_version_number;
756 --
757 begin
758 --
759 hr_utility.set_location ('Entering ' || l_proc, 10);
760 --
761 -- Issue a savepoint for validation mode.
762 --
763 savepoint validate_point;
764 --
765 If p_batch_flag then
766 --
767 l_actn := 'Calling Ben_batch_utils.person_header...';
768 --
769 ben_batch_utils.person_header
770 (p_person_id => p_person_id
771 ,p_business_group_id => p_business_group_id
772 ,p_effective_date => p_effective_date);
773 --
774 ben_batch_utils.ini('COMP_OBJ');
775 --
776 g_actn_tbl.delete;
777 g_actn_cnt := 0;
778 g_cert_tbl.delete;
779 g_cert_cnt := 0;
780 g_bnf_tbl.delete;
781 g_bnf_cnt := 0;
782 g_dpnt_tbl.delete;
783 g_dpnt_cnt := 0;
784 --
785 End if;
786 --
787 -- There is some preliminary processing that needs to be done before closing
788 -- action items.
789 -- 1. Load all the person's enrollment reslults into cache-A.
790 -- 2. If the enrollment result is suspended and has an interim result, then
791 -- load the interim enrollment results into cache-B.
792 -- 3. Load all the records from cache-A minus the ones in cache-B into
793 -- cache-C. This is the set of enrollment records that will be processed.
794 --
795 -- Step 1. Load all the enrollment results for the person into the cache.
796 --
797 l_actn := 'Loading cursor into cache';
798 --
799 for l_rec in c_pen loop
800 l_enrt_cnt := l_enrt_cnt + 1;
801 l_enrt_rec(l_enrt_cnt) := l_rec;
802 end loop;
803 --
804 hr_utility.set_location ('All enrollments l_enrt_cnt '||l_enrt_cnt ,20);
805 for i in 1..l_enrt_cnt
806 loop
807 --
808 -- If the enrollment result is suspended and has an interim enrollment
809 -- then load the interim result's id into the cache.
810 --
811 if l_enrt_rec(i).rplcs_sspndd_rslt_id is not null then
812 hr_utility.set_location('Loading interim id into cache', 10);
813 l_intr_cnt := l_intr_cnt + 1;
814 l_intr_rec(l_intr_cnt).prtt_enrt_rslt_id := l_enrt_rec(i).rplcs_sspndd_rslt_id;
815 end if;
816 --
817 end loop;
818 hr_utility.set_location ('Interim l_intr_cnt '||l_intr_cnt,30);
819 --
820 -- Load all of the person's interim enrt-rslt records into the l_intr_rec cache.
821 --
822 for i in 1..l_intr_cnt loop
823 --
824 for j in 1..l_enrt_cnt loop
825 --
826 if (l_enrt_rec(j).prtt_enrt_rslt_id = l_intr_rec(i).prtt_enrt_rslt_id )
827 then
828 l_intr_rec(i) := l_enrt_rec(j);
829 exit;
830 end if;
831 --
832 end loop;
833 --
834 end loop;
835 --
836 -- l_process_rec is all results for the person that are not interim results
837 --
838 l_actn := 'Removing interim from All enrollments';
839 --
840 if l_intr_cnt > 0 then
841 --
842 l_found := false;
843 --
844 for i in 1..l_enrt_cnt loop
845 --
846 for j in 1..l_intr_cnt loop
847 --
848 l_found := false; --Bug 2386000
849 --hr_utility.set_location(' INTR PEN '||l_intr_rec(j).prtt_enrt_rslt_id,111);
850 --hr_utility.set_location(' ENROLL PEN '||l_enrt_rec(i).prtt_enrt_rslt_id,111);
851 --
852 if l_intr_rec(j).prtt_enrt_rslt_id = l_enrt_rec(i).prtt_enrt_rslt_id
853 then
854 --
855 l_found := TRUE;
856 exit;
857 --
858 end if;
859 --
860 end loop;
861 --
862 if not l_found then
863 --
864 l_process_cnt := l_process_cnt + 1;
865 l_process_rec(l_process_cnt) := l_enrt_rec(i);
866 --
867 end if;
868 --
869 end loop;
870 --
871 else
872 l_process_cnt := l_enrt_cnt ;
873 l_process_rec := l_enrt_rec;
874 end if;
875 --
876 hr_utility.set_location(' All enrt minus Interim enrt l_process_cnt '||l_process_cnt, 40);
877 l_actn := 'Starting Enrollment loop';
878 --
879 for i in 1..l_process_cnt loop
880 --
881 l_actn_cd := 'N';
882 --
883 if (p_batch_flag) then
884 --
885 -- g_actn_tbl is all the action items for a single result
886 --
887 hr_utility.set_location(' Call ld_actn First Place ',50);
888 ld_actn(p_business_group_id => p_business_group_id
889 ,p_effective_date => p_effective_date
890 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
891 ,p_before => TRUE
892 ,p_after => FALSE
893 ,p_idx_b => l_idx_b
894 ,p_idx_e => l_idx_e);
895 --
896 end if;
897 --
898 -- Attempt to resolve actions items for this result.
899 --
900 hr_utility.set_location('Call determine_action_items ',60);
901 ben_enrollment_action_items.determine_action_items
902 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
903 ,p_effective_date => p_effective_date
904 ,p_business_group_id => p_business_group_id
905 ,p_suspend_flag => l_suspend_flag
906 ,p_post_rslt_flag => 'Y'
907 ,p_rslt_object_version_number => l_process_rec(i).object_version_number
908 ,p_dpnt_actn_warning => l_dump_boolean
909 ,p_bnf_actn_warning => l_dump_boolean
910 ,p_ctfn_actn_warning => l_dump_boolean);
911 --
912 hr_utility.set_location(' l_suspend_flag '||l_suspend_flag,70);
913 hr_utility.set_location(' l_process_rec(i).sspndd_flag '||l_process_rec(i).sspndd_flag , 70);
914 --
915 If (l_suspend_flag = 'Y' and l_process_rec(i).sspndd_flag = 'Y') then
916 --
917 -- The result is suspended now, and was suspended before this call...
918 --
919 -- Get minimum due date of all required open actn items for this result
920 --
921 hr_utility.set_location('Enrt Result still suspended after benactcm call.'
922 , 10);
923 --
924 open c_actn_min(l_process_rec(i).prtt_enrt_rslt_id);
925 Fetch c_actn_min into l_due_dt;
926 --
927 If c_actn_min%notfound then
928 close c_actn_min;
929 fnd_message.set_token('BEN', 'BEN_91909_REQ_ACTN_NO_FND_ENRT');
930 fnd_message.raise_error;
931 End if;
932 --
933 Close c_actn_min;
934 --
935 -- If the minimum due date has past, then there is no way for the prtt to
936 -- complete all the required action items for this suspended result. Go
937 -- ahead and delete the suspended result.
938 --
939 If p_effective_date >= l_due_dt or p_overwrite_flag = TRUE
940 then
941 --
942 hr_utility.set_location('Due date passed or overwrite flag TRUE', 10);
943 --
944 If (p_audit_log = 'Y') then
945 --
946 -- g_bnf_tbl is all the beneficiaries for a single result
947 --
948 l_actn := 'Calling ld_bnf...';
949 ld_bnf (p_business_group_id => p_business_group_id
950 ,p_effective_date => p_effective_date
951 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
952 --
953 -- g_cert_tbl is all the certifications for a single result
954 --
955 l_actn := 'Calling ld_cert...';
956 ld_cert(p_business_group_id => p_business_group_id
957 ,p_effective_date => p_effective_date
958 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
959 --
960 End if;
961 --
962 l_actn := 'Calling ben_prtt_enrt_result_api.delete_enrollment...' ;
963 --
964 ben_prtt_enrt_result_api.delete_enrollment
965 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
966 ,p_per_in_ler_id => l_process_rec(i).per_in_ler_id -- Bug 2386000
967 ,p_business_group_id => p_business_group_id
968 ,p_effective_start_date => l_process_rec(i).effective_start_date
969 ,p_effective_end_date => l_process_rec(i).effective_end_date
970 ,p_object_version_number => l_process_rec(i).object_version_number
971 ,p_effective_date => p_effective_date
972 ,p_datetrack_mode => hr_api.g_delete
973 ,p_multi_row_validate => TRUE
974 ,p_source => 'benuneai');
975 --
976 If (p_audit_log = 'Y')then
977 --
978 l_actn := 'Calling ld_dpnt...';
979 --
980 ld_dpnt(p_business_group_id => p_business_group_id
981 ,p_effective_date => p_effective_date
982 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
983 --
984 End if;
985 --
986 End if;
987 --
988 Elsif l_suspend_flag = 'N' and l_process_rec(i).sspndd_flag = 'Y'
989 then
990 --
991 -- If the enrollment result was unsuspended as a result of the call to
992 -- determine_action_items process, then its interim enrollment would have
993 -- been end-dated. So set the skip flag for the interim record so that
994 -- processing is skipped when it is picked up.
995 --
996 l_actn := 'Processing unsupended enrollment in loop E';
997 --
998 hr_utility.set_location('Result unsuspended after benactcm call', 10);
999 --
1000 If (p_batch_flag) then
1001 --
1002 ben_batch_utils.write(p_text => '> Enrollment ( ' ||
1003 to_char(l_process_rec(i).prtt_enrt_rslt_id) ||
1004 ') unsuspended ');
1005 --
1006 End if;
1007 --
1008 l_actn_cd := 'U';
1009 --
1010 For j in 1..l_intr_cnt loop
1011 --
1012 If l_intr_rec(j).prtt_enrt_rslt_id=l_process_rec(i).prtt_enrt_rslt_id
1013 then
1014 --
1015 hr_utility.set_location('Will not process interim id ' ||
1016 l_intr_rec(j).prtt_enrt_rslt_id, 10);
1017 --
1018 l_intr_rec(j).skip := 'Y';
1019 --
1020 If (p_batch_flag) then
1021 ben_batch_utils.write(p_text => '> Interim Enrollment ( ' ||
1022 to_char(l_process_rec(j).prtt_enrt_rslt_id) ||
1023 ') Ended ');
1024 End if;
1025 --
1026 exit;
1027 --
1028 End if;
1029 --
1030 End loop;
1031 --
1032 Elsif (l_suspend_flag = 'Y' and l_process_rec(i).sspndd_flag = 'N') then
1033 --
1034 -- If the result is suspended now and wasn't before then error out.
1035 --
1036 hr_utility.set_location('Result suspended after benactcm call.', 10);
1037 --
1038 If (p_batch_flag) then
1039 --
1040 ben_batch_utils.write
1041 ('Enrollment(' || to_char(l_process_rec(i).prtt_enrt_rslt_id)
1042 || ') can not be suspeneded since it is active before');
1043 End if;
1044 --
1045 Fnd_message.set_name('BEN','BEN_91908_ENRT_NOT_ALWD_SUSP');
1046 Fnd_message.raise_error;
1047 --
1048 End if;
1049 --
1050 hr_utility.set_location(' After four cases ',80);
1051 -- Clean up all unresolved action items for this enrt result.
1052 --
1053 For l_recA in c_actn(l_process_rec(i).prtt_enrt_rslt_id) loop
1054 --
1055 l_del_flag := FALSE;
1056 --
1057 If p_overwrite_flag then
1058 --
1059 l_del_flag := TRUE;
1060 --
1061 Elsif l_recA.due_dt < p_effective_date then --CFW
1062 --
1063 l_del_flag := TRUE;
1064 --
1065 Else
1066 --
1067 -- if the due date for the action items isn't past, do not delete it.
1068 --
1069 NULL;
1070 --
1071 End if;
1072 --
1073 If l_del_flag then
1074 --
1075 hr_utility.set_location(' l_del_flag TRUE ',80);
1076 remove_prtt_actn
1077 (p_prtt_enrt_actn_id => l_reca.prtt_enrt_actn_id
1078 ,p_business_group_id => p_business_group_id
1079 ,p_effective_date => p_effective_date
1080 ,p_datetrack_mode => hr_api.g_delete
1081 ,p_object_version_number => l_reca.object_version_number
1082 ,p_prtt_enrt_rslt_id => l_dump_number
1083 ,p_rslt_object_version_number => l_dump_number
1084 ,p_unsuspend_enrt_flag => 'N'
1085 ,p_effective_start_date => l_recA.effective_start_date
1086 ,p_effective_end_date => l_recA.effective_end_date
1087 ,p_batch_flag => p_batch_flag);
1088 --
1089 End if;
1090 --
1091 End loop; -- End of c_Actn
1092 --
1093 If p_batch_flag then
1094 --
1095 --hr_utility.set_location(' Second ld_actn Call ', 90);
1096 --hr_utility.set_location(' l_idx_b '||l_idx_b,90);
1097 --hr_utility.set_location('l_idx_e '||l_idx_e,90);
1098
1099 --
1100 ld_actn(p_business_group_id => p_business_group_id
1101 ,p_effective_date => p_effective_date
1102 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1103 ,p_before => FALSE
1104 ,p_after => TRUE
1105 ,p_idx_b => l_idx_b
1106 ,p_idx_e => l_idx_e);
1107 --
1108 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1109 --
1110 Ben_batch_utils.cache_comp_obj
1111 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1112 ,p_effective_date => p_effective_date
1113 ,p_actn_cd => l_actn_cd);
1114 --
1115 If (l_actn_cd = 'D') then
1116 --
1117 benutils.write
1118 (p_text => '> Enrollment(' ||
1119 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') Ended ');
1120 --
1121 Elsif (l_actn_cd = 'U') then
1122 --
1123 benutils.write
1124 (p_text => '> Enrollment(' ||
1125 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') unsuspended ');
1126 --
1127 End if;
1128 --
1129 End if;
1130 --
1131 End loop; -- End of Erec loop
1132 --
1133 -- Clean up interim enrollments.
1134 --
1135 For i in 1..l_intr_cnt loop
1136 --
1137 -- For each enrt result id call determine action items to try to unsuspend
1138 -- the result.
1139 --
1140 hr_utility.set_location('Cleaing up interim enrollment id : ' ||
1141 l_intr_rec(i).prtt_enrt_rslt_id, 10);
1142 --
1143 If (l_intr_rec(i).skip = 'N') then
1144 --
1145 l_actn_cd := 'N';
1146 --
1147 If (p_batch_flag) then
1148 --
1149 l_actn := 'Calling ld_actn...';
1150 --
1151 --hr_utility.set_location(' Second ld_actn Call ', 99);
1152 --hr_utility.set_location(' l_idx_b '||l_idx_b,99);
1153 --hr_utility.set_location('l_idx_e '||l_idx_e,99);
1154 --hr_utility.set_location('l_process_rec(i).prtt_enrt_rslt_id '
1155 -- ||l_process_rec(i).prtt_enrt_rslt_id,99);
1156 --hr_utility.set_location(' p_effective_date '||p_effective_date,99);
1157
1158 ld_actn(p_business_group_id => p_business_group_id
1159 ,p_effective_date => p_effective_date
1160 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1161 ,p_before => TRUE
1162 ,p_after => FALSE
1163 ,p_idx_b => l_idx_b
1164 ,p_idx_e => l_idx_e
1165 );
1166 End if;
1167 --
1168 ben_enrollment_action_items.determine_action_items
1169 (p_prtt_enrt_rslt_id => l_intr_rec(i).prtt_enrt_rslt_id
1170 ,p_effective_date => p_effective_date
1171 ,p_business_group_id => p_business_group_id
1172 ,p_suspend_flag => l_suspend_flag
1173 ,p_datetrack_mode => 'CORRECTION'
1174 ,p_post_rslt_flag => 'N'
1175 ,p_rslt_object_version_number => l_intr_rec(i).object_version_number
1176 ,p_dpnt_actn_warning => l_dump_boolean
1177 ,p_bnf_actn_warning => l_dump_boolean
1178 ,p_ctfn_actn_warning => l_dump_boolean
1179 );
1180 --
1181 If (l_suspend_flag = 'Y' ) then
1182 l_actn := 'Erroring out - Suspend_flag can not be "Y"(I) ';
1183 fnd_message.set_name('BEN','BEN_91908_ENRT_NOT_ALWD_SUSP');
1184 fnd_message.raise_error;
1185 End if;
1186 --
1187 l_actn := 'Cleaning out all unresolved actn items(I)';
1188 --
1189 For l_recA in c_actn
1190 (c_prtt_enrt_rslt_id => l_intr_rec(i).prtt_enrt_rslt_id)
1191 loop
1192 --
1193 l_del_flag := FALSE;
1194 --
1195 If (p_overwrite_flag) then
1196 l_del_flag := TRUE;
1197 Elsif (l_recA.DUE_dt <= p_effective_date) then
1198 -- if the due date for the action items isn't past, do not delete it.
1199 NULL;
1200 End if;
1201 --
1202 If (l_del_flag) then
1203 --
1204 l_actn := 'Calling REMOVE_PRTT_ACTN to clean actn(I)';
1205 --
1206 remove_prtt_actn
1207 (p_prtt_enrt_actn_id => l_reca.prtt_enrt_actn_id
1208 ,p_business_group_id => p_business_group_id
1209 ,p_effective_date => p_effective_date
1210 ,p_datetrack_mode => hr_api.g_delete
1211 ,p_object_version_number => l_reca.object_version_number
1212 ,p_prtt_enrt_rslt_id => l_dump_number
1213 ,p_rslt_object_version_number => l_dump_number
1214 ,p_unsuspend_enrt_flag => 'N'
1215 ,p_effective_start_date => l_recA.effective_start_date
1216 ,p_effective_end_date => l_recA.effective_end_date
1217 ,p_batch_flag => p_batch_flag
1218 );
1219 --
1220 If (p_batch_flag) then
1221 --
1222 l_actn := 'Calling ben_batch_utils.write...';
1223 --
1224 ben_batch_utils.write(p_text => '> Prtt_Enrt_Actn(' ||
1225 to_char(l_recA.prtt_enrt_actn_id) ||
1226 ') deleted');
1227 --
1228 End if;
1229 --
1230 End if;
1231 --
1232 End loop; -- End of c_Actn
1233 --
1234 If (p_batch_flag) then
1235 --
1236 l_actn := 'Calling ld_actn...';
1237 --
1238 ld_actn(p_business_group_id => p_business_group_id
1239 ,p_effective_date => p_effective_date
1240 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1241 ,p_before => FALSE
1242 ,p_after => TRUE
1243 ,p_idx_b => l_idx_b
1244 ,p_idx_e => l_idx_e
1245 );
1246 --
1247 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1248 --
1249 Ben_batch_utils.cache_comp_obj
1250 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1251 ,p_effective_date => p_effective_date
1252 ,p_actn_cd => l_actn_cd
1253 );
1254 --
1255 If (l_actn_cd = 'D') then
1256 --
1257 benutils.write
1258 (p_text => '> Enrollment(' ||
1259 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') Ended ');
1260 --
1261 Elsif (l_actn_cd = 'U') then
1262 --
1263 benutils.write
1264 (p_text => '> Enrollment(' ||
1265 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') unsuspended ');
1266 --
1267 End if;
1268 --
1269 End if;
1270 --
1271 End if;
1272 --
1273 End loop; -- End of Irec loop
1274 --
1275 If p_validate then
1276 rollback to validate_point;
1277 End if;
1278 --
1279 If (p_batch_flag) then
1280 --
1281 write_person_category
1282 (p_audit_log => p_audit_log
1283 ,p_business_group_id => p_business_group_id
1284 ,P_person_id => p_person_id
1285 ,p_effective_date => p_effective_date);
1286 --
1287 If p_person_action_id is not null then
1288 --
1289 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
1290 --
1291 ben_person_actions_api.update_person_actions
1292 (p_person_action_id => p_person_action_id
1293 ,p_action_status_cd => 'P'
1294 ,p_object_version_number => l_object_version_number
1295 ,p_effective_date => p_effective_date);
1296 --
1297 End if;
1298 --
1299 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
1300 g_persons_procd := g_persons_procd + 1;
1301 --
1302 End if;
1303 --
1304 hr_utility.set_location('Leaving:'||l_proc, 10);
1305 --
1306 Exception
1307 --
1308 When others then
1309 --
1310 rollback to validate_point;
1311 --
1312 hr_utility.set_location('Exception handled in cls_per...', 10);
1313 If (p_batch_flag) then
1314 --
1315 g_persons_errored := g_persons_errored + 1;
1316 ben_batch_utils.write_error_rec;
1317 --
1318 ben_batch_utils.rpt_error(p_proc => l_proc
1319 ,p_last_actn => l_actn
1320 ,p_rpt_flag => TRUE);
1321 --
1322 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1323 ,p_effective_date => p_effective_date
1324 );
1325 --
1326 Ben_batch_utils.write(p_text => ' << Transactions Rollbacked >> ');
1327 --
1328 If p_person_action_id is not null then
1329 --
1330 ben_person_actions_api.update_person_actions
1331 (p_person_action_id => p_person_action_id
1332 ,p_action_status_cd => 'E'
1333 ,p_object_version_number => l_object_version_number
1334 ,p_effective_date => p_effective_date);
1335 --
1336 End if;
1337 --
1338 write_person_category
1339 (p_audit_log => p_audit_log
1340 ,p_error => TRUE
1341 ,p_business_group_id => p_business_group_id
1342 ,p_person_id => p_person_id
1343 ,p_effective_date => p_effective_date);
1344 --
1345 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
1346 --
1347 End if;
1348 --
1349 fnd_message.raise_error;
1350 --
1351 end cls_per_unresolved_actn_item;
1352 --
1353 -- ----------------------------------------------------------------------------
1354 -- |--------------------------< do_multithread >------------------------------|
1355 -- ----------------------------------------------------------------------------
1356 --
1357 procedure do_multithread
1358 (errbuf out nocopy varchar2
1359 ,retcode out nocopy number
1360 ,p_validate in varchar2 default 'N'
1361 ,p_benefit_action_id in number
1362 ,p_thread_id in number
1363 ,p_effective_date in varchar2
1364 ,p_business_group_id in number
1365 ,p_audit_log in varchar2 default 'N'
1366 )
1367 is
1368 --
1369 -- Local variable declaration
1370 --
1371 l_proc varchar2(80) := g_package||'.do_multithread';
1372 --
1373 l_effective_date date;
1374 l_person_id ben_person_actions.person_id%type;
1375 l_person_action_id ben_person_actions.person_action_id%type;
1376 l_object_version_number ben_person_actions.object_version_number%type;
1377 l_ler_id ben_person_actions.ler_id%type;
1378 l_range_id ben_batch_ranges.range_id%type;
1379 l_record_number number := 0;
1380 l_start_person_action_id number := 0;
1381 l_end_person_action_id number := 0;
1382 l_actn varchar2(80);
1383 l_cnt number(5):= 0;
1384 l_validate boolean;
1385 l_threads number;
1386 l_chunk_size number;
1387 --
1388 -- Cursors declaration
1389 --
1390 cursor c_range_thread
1391 is
1392 select ran.range_id
1393 ,ran.starting_person_action_id
1394 ,ran.ending_person_action_id
1395 from ben_batch_ranges ran
1396 where ran.range_status_cd = 'U'
1397 and ran.benefit_action_id = p_benefit_action_id
1398 and rownum < 2
1399 for update of ran.range_status_cd;
1400 --
1401 cursor c_person_thread
1402 is
1403 select ben.person_id
1404 ,ben.person_action_id
1405 ,ben.object_version_number
1406 ,ben.ler_id
1407 from ben_person_actions ben
1408 where ben.benefit_action_id = p_benefit_action_id
1409 and ben.action_status_cd <> 'P'
1410 and ben.person_action_id between l_start_person_action_id
1411 and l_end_person_action_id
1412 order by ben.person_action_id;
1413 --
1414 cursor c_parameter
1415 is
1416 select *
1417 from ben_benefit_actions ben
1418 where ben.benefit_action_id = p_benefit_action_id;
1419 --
1420 cursor c_master is
1421 select 'Y'
1422 from ben_benefit_actions bft
1423 where bft.benefit_action_id = p_benefit_action_id
1424 and bft.request_id = fnd_global.conc_request_id;
1425 --
1426 l_parm c_parameter%rowtype;
1427 l_commit number;
1428 l_master varchar2(1) := 'N';
1429 --
1430 Begin
1431 --
1432 hr_utility.set_location ('Entering '||l_proc,5);
1433 --
1434 -- Convert varchar2 dates to real dates
1435 -- 1) First remove time component
1436 -- 2) Next convert format
1437 --
1438 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1439 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
1440 ,'DD/MM/RRRR');
1441 --
1442 -- Put row in fnd_sessions
1443 --
1444 dt_fndate.change_ses_date
1445 (p_ses_date => l_effective_date,
1446 p_commit => l_commit);
1447 --
1448 l_actn := 'Calling benutils.get_parameter...';
1449 benutils.get_parameter(p_business_group_id => p_business_group_Id
1450 ,p_batch_exe_cd => 'BENUNEAI'
1451 ,p_threads => l_threads
1452 ,p_chunk_size => l_chunk_size
1453 ,p_max_errors => g_max_person_err);
1454 --
1455 -- Set up benefits environment
1456 --
1457 ben_env_object.init(p_business_group_id => p_business_group_id,
1458 p_effective_date => l_effective_date,
1459 p_thread_id => p_thread_id,
1460 p_chunk_size => l_chunk_size,
1461 p_threads => l_threads,
1462 p_max_errors => g_max_person_err,
1463 p_benefit_action_id => p_benefit_action_id);
1464 --
1465 g_persons_procd := 0;
1466 g_persons_errored := 0;
1467 --
1468 ben_batch_utils.ini;
1469 --
1470 benutils.g_benefit_action_id := p_benefit_action_id;
1471 benutils.g_thread_id := p_thread_id;
1472 --
1473 open c_master;
1474 fetch c_master into l_master;
1475 close c_master;
1476 --
1477 if p_validate = 'Y'
1478 then
1479 l_validate := TRUE;
1480 else
1481 l_validate := FALSE;
1482 end if;
1483 --
1484 open c_parameter;
1485 fetch c_parameter into l_parm;
1486 close c_parameter;
1487 --
1488 if fnd_global.conc_request_id <> -1
1489 then
1490 --
1491 -- Print the batch parameters to the log file if this program was called by
1492 -- the concurrent manager.
1493 --
1494 Ben_batch_utils.print_parameters
1495 (p_thread_id => p_thread_id
1496 ,p_benefit_action_id => p_benefit_action_id
1497 ,p_validate => p_validate
1498 ,p_business_group_id => p_business_group_id
1499 ,p_effective_date => l_effective_date
1500 ,p_person_id => l_parm.person_id
1501 ,p_person_selection_rule_id => l_parm.person_selection_rl
1502 ,p_location_id => l_parm.location_id
1503 ,p_audit_log => p_audit_log);
1504 --
1505 end if;
1506 --
1507 -- The processing for this thread is as follows:
1508 -- 1) Lock the rows in ben_batch_ranges that are not processed.
1509 -- 2) Fetch the start and ending person action id for the range.
1510 -- 3) Loop through the person actions in the range and close unresolved
1511 -- action items for each.
1512 -- 4) Go to number 1 again and repeat until all ranges are processed.
1513 --
1514 loop
1515 --
1516 open c_range_thread;
1517 fetch c_range_thread
1518 into l_range_id,l_start_person_action_id,l_end_person_action_id;
1519 --
1520 if c_range_thread%notfound
1521 then
1522 close c_range_thread;
1523 exit;
1524 end if;
1525 --
1526 close c_range_thread;
1527 --
1528 update ben_batch_ranges ran
1529 set ran.range_status_cd = 'P'
1530 where ran.range_id = l_range_id;
1531 --
1532 commit;
1533 --
1534 g_cache_per_proc.delete;
1535 --
1536 l_record_number := 0;
1537 --
1538 -- Loop through all the person actions for the batch range being processed
1539 -- and close the unresolved action items.
1540 --
1541 for l_rec in c_person_thread
1542 loop
1543 --
1544 hr_utility.set_location('person_id : ' || l_rec.person_id, 10);
1545 --
1546 g_person_actn_cnt := g_person_actn_cnt + 1;
1547 --
1548 begin
1549 --
1550 ben_cls_unresolved_actn_item.cls_per_unresolved_actn_item
1551 (p_person_id => l_rec.person_id
1552 ,p_person_action_id => l_rec.person_action_id
1553 ,p_object_version_number => l_rec.object_version_number
1554 ,p_effective_date => l_effective_date
1555 ,p_business_group_id => p_business_group_id
1556 ,p_overwrite_flag => FALSE
1557 ,p_validate => l_validate
1558 ,p_batch_flag => TRUE
1559 ,p_audit_log => p_audit_log);
1560 --
1561 Exception
1562 When others then
1563 If (g_persons_errored > g_max_person_err) then
1564 fnd_message.raise_error;
1565 End if;
1566 End;
1567
1568 End loop;
1569 --
1570 --
1571 End loop;
1572 --
1573 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1574 ,p_num_pers_errored => g_persons_errored);
1575 --
1576 --
1577 -- Check if all the slave processes are finished.
1578 --
1579 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1580 --
1581 -- End the process.
1582 --
1583 ben_batch_utils.end_process
1584 (p_benefit_action_id => p_benefit_action_id
1585 ,p_person_selected => g_person_actn_cnt
1586 ,p_business_group_id => p_business_group_id);
1587 --
1588 -- Submit reports.
1589 --
1590 if l_master = 'Y' then
1591 --
1592 submit_all_reports(p_audit_log => p_audit_log);
1593 --
1594 end if;
1595 --
1596 hr_utility.set_location ('Leaving '||l_proc,70);
1597 --
1598 Exception
1599 --
1600 when others
1601 then
1602 --
1603 rollback;
1604 --
1605 ben_batch_utils.rpt_error(p_proc => l_proc
1606 ,p_last_actn => l_actn
1607 ,p_rpt_flag => TRUE);
1608 --
1609 benutils.write(p_text => fnd_message.get);
1610 benutils.write(p_text => sqlerrm);
1611 --
1612 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1613 --
1614 ben_batch_utils.end_process(p_benefit_action_id => p_benefit_action_id
1615 ,p_person_selected => g_person_actn_cnt
1616 ,p_business_group_id => p_business_group_id);
1617 --
1618 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1619 --
1620 commit;
1621 --
1622 fnd_message.raise_error;
1623 --
1624 end do_multithread;
1625 --
1626 -- ----------------------------------------------------------------------------
1627 -- |-----------------------------< restart >----------------------------------|
1628 -- ----------------------------------------------------------------------------
1629 --
1630 procedure restart
1631 (errbuf out nocopy varchar2
1632 ,retcode out nocopy number
1633 ,p_benefit_action_id in number)
1634 is
1635 --
1636 -- Cursor Declaration
1637 --
1638 cursor c_parameters
1639 is
1640 select to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
1641 ,business_group_id
1642 ,pgm_id
1643 ,pl_id
1644 ,location_id
1645 ,ler_id
1646 ,popl_enrt_typ_cycl_id
1647 ,person_id
1648 ,person_selection_rl
1649 ,validate_flag
1650 ,debug_messages_flag
1651 ,audit_log_flag
1652 from ben_benefit_actions ben
1653 where ben.benefit_action_id = p_benefit_action_id;
1654 --
1655 -- Local Variable declaration.
1656 --
1657 l_proc varchar2(80) := g_package||'.restart';
1658 l_parameters c_parameters%rowtype;
1659 l_errbuf varchar2(80);
1660 l_retcode number;
1661 l_actn varchar2(80);
1662 --
1663 Begin
1664 --
1665 hr_utility.set_location ('Entering ' || l_proc, 10);
1666 --
1667 -- get the parameters for a previous run and do a restart
1668 --
1669 open c_parameters;
1670 fetch c_parameters into l_parameters;
1671 --
1672 If c_parameters%notfound
1673 then
1674 close c_parameters;
1675 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
1676 fnd_message.raise_error;
1677 End if;
1678 --
1679 close c_parameters;
1680 --
1681 -- Call the "process" procedure with parameters for restart
1682 --
1683 process(errbuf => l_errbuf
1684 ,retcode => l_retcode
1685 ,p_benefit_action_id => p_benefit_action_id
1686 ,p_effective_date => l_parameters.process_date
1687 ,p_validate => l_parameters.validate_flag
1688 ,p_business_group_id => l_parameters.business_group_id
1689 ,p_pgm_id => l_parameters.pgm_id
1690 ,p_pl_nip_id => l_parameters.pl_id
1691 ,p_location_id => l_parameters.location_id
1692 ,p_person_id => l_parameters.person_id
1693 ,p_debug_messages => l_parameters.debug_messages_flag
1694 ,p_audit_log => l_parameters.audit_log_flag
1695 );
1696 --
1697 hr_utility.set_location ('Leaving ' || l_proc, 70);
1698 --
1699 End restart;
1700 --
1701 -- ----------------------------------------------------------------------------
1702 -- |------------------------------< process >---------------------------------|
1703 -- ----------------------------------------------------------------------------
1704 --
1705 -- This is the main procedure that is called from the concurrent manager.
1706 --
1707 procedure process
1708 (errbuf out nocopy varchar2
1709 ,retcode out nocopy number
1710 ,p_benefit_action_id in number
1711 ,p_effective_date in varchar2
1712 ,p_business_group_id in number
1713 ,p_pgm_id in number default NULL
1714 ,p_pl_nip_id in number default NULL
1715 ,p_location_id in number default NULL
1716 ,p_person_id in number default NULL
1717 ,p_person_selection_rl in number default NULL
1718 ,p_validate in varchar2 default 'N'
1719 ,p_debug_messages in varchar2 default 'N'
1720 ,p_audit_log in varchar2 default 'N'
1721 )
1722 is
1723 --
1724 l_effective_date date;
1725 --
1726 -- Cursor Declaration.
1727 --
1728 cursor c_pen
1729 is
1730 select distinct pen.person_id
1731 from ben_prtt_enrt_rslt_f pen
1732 ,ben_per_in_ler pil
1733 ,ben_prtt_enrt_actn_f actn
1734 where pen.business_group_id = p_business_group_id
1735 and pen.business_group_id = actn.business_group_id
1736 and pen.prtt_enrt_rslt_stat_cd is null
1737 and pen.prtt_enrt_rslt_id = actn.prtt_enrt_rslt_id
1738 and nvl(pen.effective_end_date,hr_api.g_eot) = hr_api.g_eot
1739 and nvl(actn.effective_end_date,hr_api.g_eot) = hr_api.g_eot
1740 and actn.cmpltd_dt is null
1741 and (p_person_id is null or
1742 pen.person_id = p_person_id)
1743 and (p_location_id is null or
1744 exists ( select null
1745 from per_assignments_f asg
1746 where asg.person_id = pen.person_id
1747 and asg.assignment_type <> 'C'
1748 and asg.location_id = p_location_id
1749 and asg.business_group_id = p_business_group_id
1750 and l_effective_date between asg.effective_start_date
1751 and asg.effective_end_date))
1752 and (p_pgm_id is null or
1753 pen.pgm_id = p_pgm_id)
1754 and (p_pl_nip_id is null or
1755 (pen.pl_id = p_pl_nip_id and pen.pgm_id is null))
1756 and pil.per_in_ler_id=actn.per_in_ler_id
1757 and pil.business_group_id=actn.business_group_id
1758 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1759 ;
1760 --
1761 -- Local variable declaration.
1762 --
1763 l_proc varchar2(80) := g_package||'.Process';
1764 --
1765 l_object_version_number Number(15);
1766 l_datetrack_mode varchar2(80);
1767 l_actn varchar2(80);
1768 l_request_id number;
1769 l_benefit_action_id number(15);
1770 l_person_action_id number(15);
1771 l_range_id number(15);
1772 l_chunk_size number := 20;
1773 l_num_ranges number := 0;
1774 l_threads number := 1;
1775 l_person_cnt number := 0;
1776 l_chunk_num number := 1;
1777 l_person_ok varchar2(1) := 'Y';
1778 l_person_actn_cnt number := 0;
1779 l_start_person_actn_id number(15);
1780 l_end_person_actn_id number(15);
1781 l_commit number;
1782 --
1783 Begin
1784 --
1785 hr_utility.set_location ('Entering '||l_proc,5);
1786 --
1787 -- Convert varchar2 dates to real dates
1788 -- 1) First remove time component
1789 -- 2) Next convert format
1790 --
1791 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1792 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
1793 ,'DD/MM/RRRR');
1794 --
1795 -- Put row in fnd_sessions
1796 --
1797 dt_fndate.change_ses_date
1798 (p_ses_date => l_effective_date,
1799 p_commit => l_commit);
1800 --
1801 -- Make sure all the mandatory input parameters are not null
1802 --
1803 hr_api.mandatory_arg_error(p_api_name => l_proc
1804 ,p_argument => 'p_effective_date'
1805 ,p_argument_value => l_effective_date
1806 );
1807 --
1808 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
1809 --
1810 -- Get the parameters that were defined for this batch process.
1811 --
1812 benutils.get_parameter(p_business_group_id => p_business_group_id
1813 ,p_batch_exe_cd => 'BENUNEAI'
1814 ,p_threads => l_threads
1815 ,p_chunk_size => l_chunk_size
1816 ,p_max_errors => g_max_person_err);
1817 --
1818 -- Create benefit actions parameters in the benefit action table.
1819 -- Do not create if a benefit action already exists, in other words
1820 -- we are doing a restart.
1821 --
1822 If p_benefit_action_id is null then
1823 --
1824 ben_benefit_actions_api.create_benefit_actions
1825 (p_validate => false
1826 ,p_benefit_action_id => l_benefit_action_id
1827 ,p_process_date => l_effective_date
1828 ,p_mode_cd => 'S'
1829 ,p_derivable_factors_flag => 'N'
1830 ,p_validate_flag => p_validate
1831 ,p_person_id => p_person_id
1832 ,p_person_type_id => NULL
1833 ,p_pgm_id => p_pgm_id
1834 ,p_business_group_id => p_business_group_id
1835 ,p_pl_id => p_pl_nip_id
1836 ,p_popl_enrt_typ_cycl_id => NULL
1837 ,p_no_programs_flag => 'N'
1838 ,p_no_plans_flag => 'N'
1839 ,p_comp_selection_rl => NULL
1840 ,p_person_selection_rl => p_person_selection_rl
1841 ,p_ler_id => NULL
1842 ,p_organization_id => NULL
1843 ,p_benfts_grp_id => NULL
1844 ,p_location_id => p_location_id
1845 ,p_pstl_zip_rng_id => NULL
1846 ,p_rptg_grp_id => NULL
1847 ,p_pl_typ_id => NULL
1848 ,p_opt_id => NULL
1849 ,p_eligy_prfl_id => NULL
1850 ,p_vrbl_rt_prfl_id => NULL
1851 ,p_legal_entity_id => NULL
1852 ,p_payroll_id => NULL
1853 ,p_debug_messages_flag => p_debug_messages
1854 ,p_audit_log_flag => p_audit_log
1855 ,p_object_version_number => l_object_version_number
1856 ,p_effective_date => l_effective_date
1857 ,p_request_id => fnd_global.conc_request_id
1858 ,p_program_application_id => fnd_global.prog_appl_id
1859 ,p_program_id => fnd_global.conc_program_id
1860 ,p_program_update_date => sysdate);
1861 --
1862 benutils.g_benefit_action_id := l_benefit_action_id;
1863 benutils.g_thread_id := 99;
1864 --
1865 -- Loop through rows in ben_per_in_ler_f based on the parameters passed and
1866 -- create person actions for the selected people.
1867 --
1868 for l_rec in c_pen
1869 loop
1870 --
1871 -- set variables for this iteration
1872 --
1873 l_person_ok := 'Y';
1874 --
1875 -- Check the person selection rule.
1876 --
1877 if p_person_selection_rl is not null
1878 then
1879 --
1880 l_person_ok := ben_batch_utils.person_selection_rule
1881 (p_person_id => l_rec.person_id
1882 ,p_business_group_id => p_business_group_id
1883 ,p_person_selection_rule_id => p_person_selection_rl
1884 ,p_effective_date => l_effective_date);
1885 --
1886 end if;
1887 --
1888 if l_person_ok = 'Y'
1889 then
1890 --
1891 -- Either no person sel rule or person selection rule passed. Create a
1892 -- person action row.
1893 --
1894 ben_person_actions_api.create_person_actions
1895 (p_validate => FALSE
1896 ,p_person_action_id => l_person_action_id
1897 ,p_person_id => l_rec.person_id
1898 ,p_benefit_action_id => l_benefit_action_id
1899 ,p_action_status_cd => 'U'
1900 ,p_chunk_number => l_chunk_num
1901 ,p_object_version_number => l_object_version_number
1902 ,p_effective_date => l_effective_date);
1903 --
1904 -- increment the person action count and Set the ending person action id
1905 -- to the last person action id that got created
1906 --
1907 l_person_actn_cnt := l_person_actn_cnt + 1;
1908 l_end_person_actn_id := l_person_action_id;
1909 --
1910 -- We have to create batch ranges based on the number of person actions
1911 -- created and the chunk size defined for the batch process.
1912 --
1913 if mod(l_person_actn_cnt, l_chunk_size) = 1 or l_chunk_size = 1
1914 then
1915 --
1916 -- This is the first person action id in a new range.
1917 --
1918 l_start_person_actn_id := l_person_action_id;
1919 --
1920 end if;
1921 --
1922 if mod(l_person_actn_cnt, l_chunk_size) = 0 or l_chunk_size = 1
1923 then
1924 --
1925 -- The number of person actions that got created equals the chunk
1926 -- size. Create a batch range for the person actions.
1927 --
1928 ben_batch_ranges_api.create_batch_ranges
1929 (p_validate => FALSE
1930 ,p_effective_date => l_effective_date
1931 ,p_benefit_action_id => l_benefit_action_id
1932 ,p_range_id => l_range_id
1933 ,p_range_status_cd => 'U'
1934 ,p_starting_person_action_id => l_start_person_actn_id
1935 ,p_ending_person_action_id => l_end_person_actn_id
1936 ,p_object_version_number => l_object_version_number);
1937 --
1938 l_num_ranges := l_num_ranges + 1;
1939 l_chunk_num := l_chunk_num + 1;
1940 --
1941 end if;
1942 --
1943 end if;
1944 --
1945 end loop;
1946 --
1947 -- There may be a few person actions left over from the loop above that may
1948 -- not have got inserted into a batch range because the number was less than
1949 -- the chunk size. Create a range for the remaining person actions. This
1950 -- also applies when only one person gets selected.
1951 --
1952 if l_person_actn_cnt > 0 and
1953 mod(l_person_actn_cnt, l_chunk_size) <> 0
1954 then
1955 --
1956 ben_batch_ranges_api.create_batch_ranges
1957 (p_validate => FALSE
1958 ,p_effective_date => l_effective_date
1959 ,p_benefit_action_id => l_benefit_action_id
1960 ,p_range_id => l_range_id
1961 ,p_range_status_cd => 'U'
1962 ,p_starting_person_action_id => l_start_person_actn_id
1963 ,p_ending_person_action_id => l_end_person_actn_id
1964 ,p_object_version_number => l_object_version_number);
1965 --
1966 l_num_ranges := l_num_ranges + 1;
1967 --
1968 end if;
1969 --
1970 Else
1971 --
1972 -- Benefit action id is not null i.e. the batch process is being restarted
1973 -- for a certain benefit action id. Create batch ranges and person actions
1974 -- for restarting.
1975 --
1976 l_benefit_action_id := p_benefit_action_id;
1977 --
1978 hr_utility.set_location('Restarting for benefit action id : ' ||
1979 to_char(l_benefit_action_id), 10);
1980 --
1981 ben_batch_utils.create_restart_person_actions
1982 (p_benefit_action_id => p_benefit_action_id
1983 ,p_effective_date => l_effective_date
1984 ,p_chunk_size => l_chunk_size
1985 ,p_threads => l_threads
1986 ,p_num_ranges => l_num_ranges
1987 ,p_num_persons => l_person_cnt);
1988 --
1989 end if;
1990 --
1991 commit;
1992 --
1993 -- Submit requests to the concurrent manager based on the number of ranges
1994 -- that got created.
1995 --
1996 if l_num_ranges > 1
1997 then
1998 --
1999 hr_utility.set_location('More than one range got created.', 10);
2000 --
2001 -- Set the number of threads to the lesser of the defined number of threads
2002 -- and the number of ranges created above. There's no point in submitting
2003 -- 5 threads for only two ranges.
2004 --
2005 l_threads := least(l_threads, l_num_ranges);
2006 --
2007 for l_count in 1..(l_threads - 1)
2008 loop
2009 --
2010 -- We are subtracting one from the number of threads because the main
2011 -- process will act as the last thread and will be able to keep track of
2012 -- the child requests that get submitted.
2013 --
2014 hr_utility.set_location('Submitting request ' || l_count, 10);
2015 --
2016 l_request_id := fnd_request.submit_request
2017 (application => 'BEN'
2018 ,program => 'BENUNEAIS'
2019 ,description => NULL
2020 ,sub_request => FALSE
2021 ,argument1 => p_validate
2022 ,argument2 => l_benefit_action_id
2023 ,argument3 => l_count
2024 ,argument4 => p_effective_date
2025 ,argument5 => p_business_group_id
2026 ,argument6 => p_audit_log);
2027 --
2028 -- Store the request id of the concurrent request
2029 --
2030 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
2031 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
2032 := l_request_id;
2033 --
2034 end loop;
2035 --
2036 elsif l_num_ranges = 0
2037 then
2038 --
2039 hr_utility.set_location('No people selected', 10);
2040 -- No ranges got created. i.e. no people got selected. Error out.
2041 --
2042 ben_batch_utils.print_parameters
2043 (p_thread_id => 99
2044 ,p_benefit_action_id => l_benefit_action_id
2045 ,p_validate => p_validate
2046 ,p_business_group_id => p_business_group_id
2047 ,p_effective_date => l_effective_date
2048 ,p_person_id => p_person_id
2049 ,p_person_selection_rule_id => p_person_selection_rl
2050 ,p_location_id => p_location_id
2051 );
2052 --
2053 ben_batch_utils.write(p_text =>
2054 'No person got selected with above selection criteria.');
2055 --
2056 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
2057 fnd_message.raise_error;
2058 --
2059 end if;
2060 --
2061 -- Carry on with the master. This will ensure that the master finishes last.
2062 --
2063 hr_utility.set_location('Submitting the master process', 10);
2064 --
2065 do_multithread
2066 (errbuf => errbuf
2067 ,retcode => retcode
2068 ,p_validate => p_validate
2069 ,p_benefit_action_id => l_benefit_action_id
2070 ,p_thread_id => l_threads
2071 ,p_effective_date => p_effective_date
2072 ,p_business_group_id => p_business_group_id
2073 ,p_audit_log => p_audit_log);
2074 --
2075 hr_utility.set_location ('Leaving ' || l_proc, 10);
2076 --
2077 End process;
2078 --
2079 End ben_cls_unresolved_Actn_item;