[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.5.12020000.3 2012/07/03 12:59:37 amnaraya ship $ */
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(c_effective_date date)
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 /* Bug 10145083: Check for c_effective_date and not p_effective_date*/
570 and c_effective_date between pea.effective_start_date
571 and pea.effective_end_date
572 and pil.per_in_ler_id=pea.per_in_ler_id
573 and pil.business_group_id=pea.business_group_id
574 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
575 order by pea.prtt_enrt_rslt_id, pea.prtt_enrt_actn_id
576 ;
577 l_proc varchar2(80) := g_package||'.ld_actn';
578 l_actn varchar2(80);
579 i binary_integer := 0;
580 l_fnd boolean;
581 l_idx_b binary_integer := p_idx_b ;
582 l_idx_e binary_integer:= p_idx_e;
583
584 Begin
585 hr_utility.set_location ('Entering '||l_proc,05);
586 If(p_before) then
587 --
588 l_actn := 'Load actn item (Before)...';
589 p_idx_b := g_actn_cnt;
590 For rec1 in c1(p_effective_date) loop
591 g_actn_cnt := g_actn_cnt + 1;
592 g_actn_tbl(g_actn_cnt) := rec1;
593 End loop;
594 p_idx_e:= g_actn_cnt;
595 Elsif(p_after) then
596 --
597 l_actn := 'Compae actn item (After)...';
598 /*Bug 9735870: When comparing the action items after the action items are updated,
599 action items will be end dated 1 day before the p_effective_date. Checking the
600 actions as of p_effective_date will not fetch any records. Changed the cursor
601 condition to check the action item as of p_effective_date -1 */
602 For rec2 in c1(p_effective_date-1) loop
603 l_fnd := FALSE;
604 For i in p_idx_b+1..p_idx_e loop
605 If (rec2.prtt_enrt_actn_id = g_actn_tbl(i).prtt_enrt_actn_id) then
606 If(rec2.effective_end_date = (p_effective_date-1) ) then
607 g_actn_tbl(i).actn_cd := 'D';
608 Elsif(rec2.cmpltd_dt is not null and g_actn_tbl(i).cmpltd_dt is NULL)
609 then
610 g_actn_tbl(i).actn_cd := 'C';
611 End if;
612 l_fnd := TRUE;
613 Exit;
614 End if;
615 End loop;
616 -- * Handle actn item get Zapped.
617 If (not l_fnd) then
618 g_actn_tbl(i).actn_cd := 'D';
619 End if;
620 End loop;
621 End if;
622 hr_utility.set_location ('Leaving '||l_proc,10);
623 Exception
624 When others then
625 ben_batch_utils.rpt_error(p_proc => l_proc
626 ,p_last_actn => l_actn
627 ,p_rpt_flag => TRUE
628 );
629 -- for nocopy changes
630 p_idx_b := l_idx_b ;
631 p_idx_e := l_idx_e;
632 raise;
633 End ld_actn;
634 --
635 -- ----------------------------------------------------------------------------
636 -- |-------------------< cls_per_unresolved_actn_item >-----------------------|
637 -- ----------------------------------------------------------------------------
638 --
639 -- This procedure is called to close any unresolved action items for a person.
640 --
641 procedure cls_per_unresolved_actn_item
642 (p_person_id in number
643 ,p_effective_date in date
644 ,p_business_group_id in number
645 ,p_overwrite_flag in boolean default FALSE
646 ,p_batch_flag in boolean default FALSE
647 ,p_validate in boolean default FALSE
648 ,p_person_action_id in Number default NULL
649 ,p_object_version_number in Number default NULL
650 ,p_audit_log in varchar2 default 'N'
651 )
652 is
653 --
654 -- Local Cursor/record variables
655 --
656 cursor c_pen
657 is
658 select pen.prtt_enrt_rslt_id
659 ,pen.effective_start_date
660 ,pen.effective_end_date
661 ,pen.business_group_id
662 ,pen.person_id
663 ,pen.rplcs_sspndd_rslt_id
664 ,pen.sspndd_flag
665 ,pen.object_version_number
666 ,pen.per_in_ler_id
667 ,'N' skip
668 from ben_prtt_enrt_rslt_f pen
669 where pen.person_id = p_person_id
670 and pen.business_group_id = p_business_group_id
671 and pen.prtt_enrt_rslt_stat_cd is null
672 and exists (select null
673 from ben_prtt_enrt_actn_f pea
674 where pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
675 and pea.cmpltd_dt is null
676 and p_effective_date between pea.effective_start_date
677 and pea.effective_end_date)
678 and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
679 and p_effective_date between pen.effective_start_date
680 and pen.effective_end_date -1
681 and ( pen.effective_end_date = hr_api.g_eot or --Bug 4398840
682 not exists (select 'x' from ben_prtt_enrt_rslt_f pen1 -- to exclude the ended result
683 where pen1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
684 and pen1.effective_end_date = hr_api.g_eot
685 and pen1.enrt_cvg_thru_dt <> hr_api.g_eot
686 )
687 )
688 and pen.effective_end_date >= pen.enrt_cvg_strt_dt;
689 --
690 type g_pen_record is table of c_pen%rowtype index by binary_integer;
691 --
692 -- Get all open actions for the prtt_enrt_rslt_id.
693 --
694 cursor c_actn (c_prtt_enrt_rslt_id number)
695 is
696 select b.prtt_enrt_actn_id
697 ,b.due_dt
698 ,b.cmpltd_dt
699 ,b.rqd_flag
700 ,b.prtt_enrt_rslt_id
701 ,b.actn_typ_id
702 ,b.effective_start_date
703 ,b.effective_end_date
704 ,b.object_version_number
705 from ben_prtt_enrt_actn_f b,
706 ben_per_in_ler pil
707 where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
708 and b.business_group_id = p_business_group_id
709 and b.cmpltd_dt is NULL
710 and p_effective_date between b.effective_start_date
711 and b.effective_end_date
712 and pil.per_in_ler_id=b.per_in_ler_id
713 and pil.business_group_id=b.business_group_id
714 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
715 AND EXISTS (SELECT NULL ------Bug 8620516
716 FROM ben_prtt_enrt_rslt_f pen
717 WHERE pen.prtt_enrt_rslt_id = b.prtt_enrt_rslt_id
718 AND pen.per_in_ler_id = pil.per_in_ler_id
719 AND pen.business_group_id = pil.business_group_id
720 AND pen.prtt_enrt_rslt_stat_cd IS NULL)
721 ;
722 --
723 -- Get minimum due date of all open, required actions for a result
724 --------------------------------------------------------------------------------
725 --Bug 14013461 start
726 cursor c_cert(c_prtt_enrt_actn_id number)
727 is
728 select cvrd_dpnt_ctfn_prvdd_id id,
729 object_version_number,
730 effective_start_date,
731 effective_end_date,
732 hrl.MEANING,
733 'Dependent certification: ' type
734 from ben_cvrd_dpnt_ctfn_prvdd_f dpf,
735 hr_lookups hrl
736 where dpf.prtt_enrt_actn_id = c_prtt_enrt_actn_id
737 and dpf.business_group_id = p_business_group_id
738 and p_effective_date between dpf.effective_start_date
739 and dpf.effective_end_date
740 and hrl.LOOKUP_TYPE = 'BEN_DPNT_CVG_CTFN_TYP'
741 and hrl.LOOKUP_CODE = dpf.DPNT_DSGN_CTFN_TYP_CD
742 union
743 select prtt_enrt_ctfn_prvdd_id id,
744 object_version_number,
745 effective_start_date,
746 effective_end_date,
747 hrl.MEANING,
748 'Participant certification: ' type
749 from ben_prtt_enrt_ctfn_prvdd_f pcf,
750 hr_lookups hrl
751 where pcf.prtt_enrt_actn_id = c_prtt_enrt_actn_id
752 and pcf.business_group_id = p_business_group_id
753 and p_effective_date between pcf.effective_start_date
754 and pcf.effective_end_date
755 and hrl.LOOKUP_TYPE = 'BEN_ENRT_CTFN_TYP'
756 and hrl.LOOKUP_CODE = pcf.ENRT_CTFN_TYP_CD;
757
758 cursor c_pen_info(c_prtt_enrt_rslt_id number)
759 is
760 select pgm_id,
761 pl_id,
762 oipl_id
763 from ben_prtt_enrt_rslt_f
764 where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
765 and business_group_id = p_business_group_id
766 and p_effective_date between effective_start_date
767 and effective_end_date
768 and prtt_enrt_rslt_stat_cd is null;
769
770 cursor c_pgm_info(c_pgm_id number)
771 is
772 select pgm.name||'('||to_char(pgm.pgm_id)||')'||':' name1
773 from ben_pgm_f pgm
774 where pgm.pgm_id = c_pgm_id
775 and p_effective_date between pgm.effective_start_date
776 and pgm.effective_end_date;
777 cursor c_pl_info(c_pl_id number)
778 is
779 select pl.name||'('||to_char(pl.pl_id)||')' name1
780 from ben_pl_f pl
781 where pl.pl_id = c_pl_id
782 and p_effective_date between pl.effective_start_date
783 and pl.effective_end_date;
784
785 cursor c_opt_info(c_oipl_id number)
786 is
787 select ':'||opt.name||'('||to_char(opt.opt_id )||')' name1
788 from ben_oipl_f oipl,
789 ben_opt_f opt
790 where oipl.oipl_id = c_oipl_id
791 and p_effective_date between oipl.effective_start_date
792 and oipl.effective_end_date
793 and opt.opt_id = oipl.opt_id
794 and p_effective_date between opt.effective_start_date
795 and opt.effective_end_date;
796
797 l_pgm_info c_pgm_info%rowtype;
798 l_pl_info c_pl_info%rowtype;
799 l_opt_info c_opt_info%rowtype;
800
801 l_varible number:=0;
802
803 l_pen_info c_pen_info%rowtype;
804 -- Bug 14013461 end
805 -------------------------------------------------------------------------------
806 --
807 cursor c_actn_min (c_prtt_enrt_rslt_id number)
808 is
809 select min(nvl(b.due_dt,hr_api.g_eot)) due_dt
810 from ben_prtt_enrt_actn_f b,
811 ben_per_in_ler pil
812 where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
813 and b.business_group_id = p_business_group_id
814 and b.cmpltd_dt is NULL
815 and b.rqd_flag = 'Y'
816 and p_effective_date between b.effective_start_date
817 and b.effective_end_date
818 and pil.per_in_ler_id=b.per_in_ler_id
819 and pil.business_group_id=b.business_group_id
820 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
821 ;
822 --
823 -- Record structures for preliminary processing.
824 --
825 l_enrt_rec g_pen_record; -- Enrollment results
826 l_enrt_cnt number := 0;
827 l_intr_rec g_pen_record; -- Interim Enrollments.
828 l_intr_cnt number := 0;
829 l_process_rec g_pen_record; -- Enrt results minus interim results
830 l_process_cnt number := 0;
831 --
832 -- Local Variables.
833 --
834 l_proc Varchar2(80) := g_package||'.cls_per_unresolved_actn_item';
835 --
836 l_actn Varchar2(80);
837 l_actn_cd Varchar2(30) := 'N';
838 l_set Boolean := FALSE;
839 l_found Boolean := FALSE;
840 l_del_flag Boolean := FALSE;
841 l_dump_boolean Boolean := FALSE;
842 l_dump_number number := 0;
843 l_due_dt date;
844 l_suspend_flag varchar2(30) ;
845 l_output_string varchar2(132);
846 l_idx_b Binary_integer := 0;
847 l_idx_e Binary_integer := 0;
848 l_object_version_number Number := p_object_version_number;
849 --
850 begin
851 --
852 hr_utility.set_location ('Entering ' || l_proc, 10);
853 --
854 -- Issue a savepoint for validation mode.
855 --
856 savepoint validate_point;
857 --
858 If p_batch_flag then
859 --
860 l_actn := 'Calling Ben_batch_utils.person_header...';
861 --
862 ben_batch_utils.person_header
863 (p_person_id => p_person_id
864 ,p_business_group_id => p_business_group_id
865 ,p_effective_date => p_effective_date);
866 --
867 ben_batch_utils.ini('COMP_OBJ');
868 --
869 g_actn_tbl.delete;
870 g_actn_cnt := 0;
871 g_cert_tbl.delete;
872 g_cert_cnt := 0;
873 g_bnf_tbl.delete;
874 g_bnf_cnt := 0;
875 g_dpnt_tbl.delete;
876 g_dpnt_cnt := 0;
877 --
878 End if;
879 --
880 -- There is some preliminary processing that needs to be done before closing
881 -- action items.
882 -- 1. Load all the person's enrollment reslults into cache-A.
883 -- 2. If the enrollment result is suspended and has an interim result, then
884 -- load the interim enrollment results into cache-B.
885 -- 3. Load all the records from cache-A minus the ones in cache-B into
886 -- cache-C. This is the set of enrollment records that will be processed.
887 --
888 -- Step 1. Load all the enrollment results for the person into the cache.
889 --
890 l_actn := 'Loading cursor into cache';
891 --
892 for l_rec in c_pen loop
893 l_enrt_cnt := l_enrt_cnt + 1;
894 l_enrt_rec(l_enrt_cnt) := l_rec;
895 end loop;
896 --
897 hr_utility.set_location ('All enrollments l_enrt_cnt '||l_enrt_cnt ,20);
898 for i in 1..l_enrt_cnt
899 loop
900 --
901 -- If the enrollment result is suspended and has an interim enrollment
902 -- then load the interim result's id into the cache.
903 --
904 if l_enrt_rec(i).rplcs_sspndd_rslt_id is not null then
905 hr_utility.set_location('Loading interim id into cache', 10);
906 l_intr_cnt := l_intr_cnt + 1;
907 l_intr_rec(l_intr_cnt).prtt_enrt_rslt_id := l_enrt_rec(i).rplcs_sspndd_rslt_id;
908 end if;
909 --
910 end loop;
911 hr_utility.set_location ('Interim l_intr_cnt '||l_intr_cnt,30);
912 --
913 -- Load all of the person's interim enrt-rslt records into the l_intr_rec cache.
914 --
915 for i in 1..l_intr_cnt loop
916 --
917 for j in 1..l_enrt_cnt loop
918 --
919 if (l_enrt_rec(j).prtt_enrt_rslt_id = l_intr_rec(i).prtt_enrt_rslt_id )
920 then
921 l_intr_rec(i) := l_enrt_rec(j);
922 exit;
923 end if;
924 --
925 end loop;
926 --
927 end loop;
928 --
929 -- l_process_rec is all results for the person that are not interim results
930 --
931 l_actn := 'Removing interim from All enrollments';
932 --
933 if l_intr_cnt > 0 then
934 --
935 l_found := false;
936 --
937 for i in 1..l_enrt_cnt loop
938 --
939 for j in 1..l_intr_cnt loop
940 --
941 l_found := false; --Bug 2386000
942 --hr_utility.set_location(' INTR PEN '||l_intr_rec(j).prtt_enrt_rslt_id,111);
943 --hr_utility.set_location(' ENROLL PEN '||l_enrt_rec(i).prtt_enrt_rslt_id,111);
944 --
945 if l_intr_rec(j).prtt_enrt_rslt_id = l_enrt_rec(i).prtt_enrt_rslt_id
946 then
947 --
948 l_found := TRUE;
949 exit;
950 --
951 end if;
952 --
953 end loop;
954 --
955 if not l_found then
956 --
957 l_process_cnt := l_process_cnt + 1;
958 l_process_rec(l_process_cnt) := l_enrt_rec(i);
959 --
960 end if;
961 --
962 end loop;
963 --
964 else
965 l_process_cnt := l_enrt_cnt ;
966 l_process_rec := l_enrt_rec;
967 end if;
968 --
969 hr_utility.set_location(' All enrt minus Interim enrt l_process_cnt '||l_process_cnt, 40);
970 l_actn := 'Starting Enrollment loop';
971 --
972 for i in 1..l_process_cnt loop
973 --
974 l_actn_cd := 'N';
975 --
976 if (p_batch_flag) then
977 --
978 -- g_actn_tbl is all the action items for a single result
979 --
980 hr_utility.set_location(' Call ld_actn First Place ',50);
981 ld_actn(p_business_group_id => p_business_group_id
982 ,p_effective_date => p_effective_date
983 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
984 ,p_before => TRUE
985 ,p_after => FALSE
986 ,p_idx_b => l_idx_b
987 ,p_idx_e => l_idx_e);
988 --
989 end if;
990 --
991 -- Attempt to resolve actions items for this result.
992 --
993 hr_utility.set_location('Call determine_action_items ',60);
994 ben_enrollment_action_items.determine_action_items
995 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
996 ,p_effective_date => p_effective_date
997 ,p_business_group_id => p_business_group_id
998 ,p_suspend_flag => l_suspend_flag
999 ,p_post_rslt_flag => 'Y'
1000 ,p_rslt_object_version_number => l_process_rec(i).object_version_number
1001 ,p_dpnt_actn_warning => l_dump_boolean
1002 ,p_bnf_actn_warning => l_dump_boolean
1003 ,p_ctfn_actn_warning => l_dump_boolean);
1004 --
1005 hr_utility.set_location(' l_suspend_flag '||l_suspend_flag,70);
1006 hr_utility.set_location(' l_process_rec(i).sspndd_flag '||l_process_rec(i).sspndd_flag , 70);
1007 --
1008 If (l_suspend_flag = 'Y' and l_process_rec(i).sspndd_flag = 'Y') then
1009 --
1010 -- The result is suspended now, and was suspended before this call...
1011 --
1012 -- Get minimum due date of all required open actn items for this result
1013 --
1014 hr_utility.set_location('Enrt Result still suspended after benactcm call.'
1015 , 10);
1016 --
1017 open c_actn_min(l_process_rec(i).prtt_enrt_rslt_id);
1018 Fetch c_actn_min into l_due_dt;
1019 --
1020 If c_actn_min%notfound then
1021 close c_actn_min;
1022 fnd_message.set_token('BEN', 'BEN_91909_REQ_ACTN_NO_FND_ENRT');
1023 fnd_message.raise_error;
1024 End if;
1025 --
1026 Close c_actn_min;
1027 --
1028 -- If the minimum due date has past, then there is no way for the prtt to
1029 -- complete all the required action items for this suspended result. Go
1030 -- ahead and delete the suspended result.
1031 --
1032 If p_effective_date >= l_due_dt or p_overwrite_flag = TRUE
1033 then
1034 --
1035 hr_utility.set_location('Due date passed or overwrite flag TRUE', 10);
1036 --
1037 If (p_audit_log = 'Y') then
1038 --
1039 -- g_bnf_tbl is all the beneficiaries for a single result
1040 --
1041 l_actn := 'Calling ld_bnf...';
1042 ld_bnf (p_business_group_id => p_business_group_id
1043 ,p_effective_date => p_effective_date
1044 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
1045 --
1046 -- g_cert_tbl is all the certifications for a single result
1047 --
1048 l_actn := 'Calling ld_cert...';
1049 ld_cert(p_business_group_id => p_business_group_id
1050 ,p_effective_date => p_effective_date
1051 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
1052 --
1053 End if;
1054 --
1055 l_actn := 'Calling ben_prtt_enrt_result_api.delete_enrollment...' ;
1056 --
1057 --Bug 14013461 start
1058 if l_varible = 0 then
1059 ben_batch_utils.write(p_text => '');
1060 ben_batch_utils.write(p_text => 'Deleting suspended enrollments for person_id('||to_char(p_person_id)||')');
1061 l_varible := l_varible+1;
1062 end if;
1063 open c_pen_info(l_process_rec(i).prtt_enrt_rslt_id);
1064 fetch c_pen_info into l_pen_info;
1065 --Bug 14162855
1066 l_pgm_info := null;
1067 l_pl_info := null;
1068 l_opt_info := null;
1069 --Bug 14162855
1070 If l_pen_info.pgm_id is not null
1071 then
1072 open c_pgm_info(l_pen_info.pgm_id);
1073 fetch c_pgm_info into l_pgm_info;
1074 close c_pgm_info;
1075 end if;
1076
1077 If l_pen_info.pl_id is not null
1078 then
1079 open c_pl_info(l_pen_info.pl_id);
1080 fetch c_pl_info into l_pl_info;
1081 close c_pl_info;
1082 end if;
1083
1084 If l_pen_info.oipl_id is not null
1085 then
1086 open c_opt_info(l_pen_info.oipl_id);
1087 fetch c_opt_info into l_opt_info;
1088 close c_opt_info;
1089 end if;
1090
1091 -- ben_batch_utils.write(p_text => ' pgm_id:'||to_char(l_pgm_id)||'|pl_id:'||to_char(l_pl_id)||'|oipl_id:'||to_char(l_oipl_id));
1092 ben_batch_utils.write(p_text => ' '||l_pgm_info.name1||l_pl_info.name1||l_opt_info.name1);
1093 close c_pen_info;
1094 For l_actn in c_actn(l_process_rec(i).prtt_enrt_rslt_id) loop
1095 for l_cert in c_cert(l_actn.prtt_enrt_actn_id) loop
1096 ben_batch_utils.write(p_text => ' Closing '||l_cert.type||l_cert.meaning||'('||to_char(l_cert.id)||')');
1097 end loop;
1098 end loop;
1099 --Bug 14013461 end
1100
1101 ben_prtt_enrt_result_api.delete_enrollment
1102 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1103 ,p_per_in_ler_id => l_process_rec(i).per_in_ler_id -- Bug 2386000
1104 ,p_business_group_id => p_business_group_id
1105 ,p_effective_start_date => l_process_rec(i).effective_start_date
1106 ,p_effective_end_date => l_process_rec(i).effective_end_date
1107 ,p_object_version_number => l_process_rec(i).object_version_number
1108 ,p_effective_date => p_effective_date
1109 ,p_datetrack_mode => hr_api.g_delete
1110 ,p_multi_row_validate => TRUE
1111 ,p_source => 'benuneai');
1112 --
1113 /*Bug 10145083: If the Suspended result is deleted, set the action code to 'D'*/
1114 l_actn_cd := 'D';
1115
1116 If (p_audit_log = 'Y')then
1117 --
1118 l_actn := 'Calling ld_dpnt...';
1119 --
1120 ld_dpnt(p_business_group_id => p_business_group_id
1121 ,p_effective_date => p_effective_date
1122 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id);
1123 --
1124 End if;
1125 --
1126 End if;
1127 --
1128 Elsif l_suspend_flag = 'N' and l_process_rec(i).sspndd_flag = 'Y'
1129 then
1130 --
1131 -- If the enrollment result was unsuspended as a result of the call to
1132 -- determine_action_items process, then its interim enrollment would have
1133 -- been end-dated. So set the skip flag for the interim record so that
1134 -- processing is skipped when it is picked up.
1135 --
1136 l_actn := 'Processing unsupended enrollment in loop E';
1137 --
1138 hr_utility.set_location('Result unsuspended after benactcm call', 10);
1139 --
1140 If (p_batch_flag) then
1141 --
1142 ben_batch_utils.write(p_text => '> Enrollment ( ' ||
1143 to_char(l_process_rec(i).prtt_enrt_rslt_id) ||
1144 ') unsuspended ');
1145 --
1146 End if;
1147 --
1148 l_actn_cd := 'U';
1149 --
1150 For j in 1..l_intr_cnt loop
1151 --
1152 If l_intr_rec(j).prtt_enrt_rslt_id=l_process_rec(i).prtt_enrt_rslt_id
1153 then
1154 --
1155 hr_utility.set_location('Will not process interim id ' ||
1156 l_intr_rec(j).prtt_enrt_rslt_id, 10);
1157 --
1158 l_intr_rec(j).skip := 'Y';
1159 --
1160 If (p_batch_flag) then
1161 ben_batch_utils.write(p_text => '> Interim Enrollment ( ' ||
1162 to_char(l_process_rec(j).prtt_enrt_rslt_id) ||
1163 ') Ended ');
1164 End if;
1165 --
1166 exit;
1167 --
1168 End if;
1169 --
1170 End loop;
1171 --
1172 Elsif (l_suspend_flag = 'Y' and l_process_rec(i).sspndd_flag = 'N') then
1173 --
1174 -- If the result is suspended now and wasn't before then error out.
1175 --
1176 hr_utility.set_location('Result suspended after benactcm call.', 10);
1177 --
1178 If (p_batch_flag) then
1179 --
1180 ben_batch_utils.write
1181 ('Enrollment(' || to_char(l_process_rec(i).prtt_enrt_rslt_id)
1182 || ') can not be suspeneded since it is active before');
1183 End if;
1184 --
1185 Fnd_message.set_name('BEN','BEN_91908_ENRT_NOT_ALWD_SUSP');
1186 Fnd_message.raise_error;
1187 --
1188 End if;
1189 --
1190 hr_utility.set_location(' After four cases ',80);
1191 -- Clean up all unresolved action items for this enrt result.
1192 --
1193 For l_recA in c_actn(l_process_rec(i).prtt_enrt_rslt_id) loop
1194 --
1195 l_del_flag := FALSE;
1196 --
1197 If p_overwrite_flag then
1198 --
1199 l_del_flag := TRUE;
1200 --
1201 Elsif l_recA.due_dt < p_effective_date then --CFW
1202 --
1203 l_del_flag := TRUE;
1204 --
1205 Else
1206 --
1207 -- if the due date for the action items isn't past, do not delete it.
1208 --
1209 NULL;
1210 --
1211 End if;
1212 --
1213 If l_del_flag then
1214 --
1215 hr_utility.set_location(' l_del_flag TRUE ',80);
1216 remove_prtt_actn
1217 (p_prtt_enrt_actn_id => l_reca.prtt_enrt_actn_id
1218 ,p_business_group_id => p_business_group_id
1219 ,p_effective_date => p_effective_date
1220 ,p_datetrack_mode => hr_api.g_delete
1221 ,p_object_version_number => l_reca.object_version_number
1222 ,p_prtt_enrt_rslt_id => l_dump_number
1223 ,p_rslt_object_version_number => l_dump_number
1224 ,p_unsuspend_enrt_flag => 'N'
1225 ,p_effective_start_date => l_recA.effective_start_date
1226 ,p_effective_end_date => l_recA.effective_end_date
1227 ,p_batch_flag => p_batch_flag);
1228 --
1229 End if;
1230 --
1231 End loop; -- End of c_Actn
1232 --
1233 If p_batch_flag then
1234 --
1235 --hr_utility.set_location(' Second ld_actn Call ', 90);
1236 --hr_utility.set_location(' l_idx_b '||l_idx_b,90);
1237 --hr_utility.set_location('l_idx_e '||l_idx_e,90);
1238
1239 --
1240 ld_actn(p_business_group_id => p_business_group_id
1241 ,p_effective_date => p_effective_date
1242 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1243 ,p_before => FALSE
1244 ,p_after => TRUE
1245 ,p_idx_b => l_idx_b
1246 ,p_idx_e => l_idx_e);
1247 --
1248 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1249 --
1250 Ben_batch_utils.cache_comp_obj
1251 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1252 ,p_effective_date => p_effective_date
1253 ,p_actn_cd => l_actn_cd);
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 loop; -- End of Erec loop
1272 --
1273 -- Clean up interim enrollments.
1274 --
1275 For i in 1..l_intr_cnt loop
1276 --
1277 -- For each enrt result id call determine action items to try to unsuspend
1278 -- the result.
1279 --
1280 hr_utility.set_location('Cleaing up interim enrollment id : ' ||
1281 l_intr_rec(i).prtt_enrt_rslt_id, 10);
1282 --
1283 If (l_intr_rec(i).skip = 'N') then
1284 --
1285 l_actn_cd := 'N';
1286 --
1287 If (p_batch_flag) then
1288 --
1289 l_actn := 'Calling ld_actn...';
1290 --
1291 --hr_utility.set_location(' Second ld_actn Call ', 99);
1292 --hr_utility.set_location(' l_idx_b '||l_idx_b,99);
1293 --hr_utility.set_location('l_idx_e '||l_idx_e,99);
1294 --hr_utility.set_location('l_process_rec(i).prtt_enrt_rslt_id '
1295 -- ||l_process_rec(i).prtt_enrt_rslt_id,99);
1296 --hr_utility.set_location(' p_effective_date '||p_effective_date,99);
1297
1298 ld_actn(p_business_group_id => p_business_group_id
1299 ,p_effective_date => p_effective_date
1300 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1301 ,p_before => TRUE
1302 ,p_after => FALSE
1303 ,p_idx_b => l_idx_b
1304 ,p_idx_e => l_idx_e
1305 );
1306 End if;
1307 --
1308 ben_enrollment_action_items.determine_action_items
1309 (p_prtt_enrt_rslt_id => l_intr_rec(i).prtt_enrt_rslt_id
1310 ,p_effective_date => p_effective_date
1311 ,p_business_group_id => p_business_group_id
1312 ,p_suspend_flag => l_suspend_flag
1313 ,p_datetrack_mode => 'CORRECTION'
1314 ,p_post_rslt_flag => 'N'
1315 ,p_rslt_object_version_number => l_intr_rec(i).object_version_number
1316 ,p_dpnt_actn_warning => l_dump_boolean
1317 ,p_bnf_actn_warning => l_dump_boolean
1318 ,p_ctfn_actn_warning => l_dump_boolean
1319 );
1320 --
1321 If (l_suspend_flag = 'Y' ) then
1322 l_actn := 'Erroring out - Suspend_flag can not be "Y"(I) ';
1323 fnd_message.set_name('BEN','BEN_91908_ENRT_NOT_ALWD_SUSP');
1324 fnd_message.raise_error;
1325 End if;
1326 --
1327 l_actn := 'Cleaning out all unresolved actn items(I)';
1328 --
1329 For l_recA in c_actn
1330 (c_prtt_enrt_rslt_id => l_intr_rec(i).prtt_enrt_rslt_id)
1331 loop
1332 --
1333 l_del_flag := FALSE;
1334 --
1335 If (p_overwrite_flag) then
1336 l_del_flag := TRUE;
1337 Elsif (l_recA.DUE_dt <= p_effective_date) then
1338 -- if the due date for the action items isn't past, do not delete it.
1339 NULL;
1340 End if;
1341 --
1342 If (l_del_flag) then
1343 --
1344 l_actn := 'Calling REMOVE_PRTT_ACTN to clean actn(I)';
1345 --
1346 remove_prtt_actn
1347 (p_prtt_enrt_actn_id => l_reca.prtt_enrt_actn_id
1348 ,p_business_group_id => p_business_group_id
1349 ,p_effective_date => p_effective_date
1350 ,p_datetrack_mode => hr_api.g_delete
1351 ,p_object_version_number => l_reca.object_version_number
1352 ,p_prtt_enrt_rslt_id => l_dump_number
1353 ,p_rslt_object_version_number => l_dump_number
1354 ,p_unsuspend_enrt_flag => 'N'
1355 ,p_effective_start_date => l_recA.effective_start_date
1356 ,p_effective_end_date => l_recA.effective_end_date
1357 ,p_batch_flag => p_batch_flag
1358 );
1359 --
1360 If (p_batch_flag) then
1361 --
1362 l_actn := 'Calling ben_batch_utils.write...';
1363 --
1364 ben_batch_utils.write(p_text => '> Prtt_Enrt_Actn(' ||
1365 to_char(l_recA.prtt_enrt_actn_id) ||
1366 ') deleted');
1367 --
1368 End if;
1369 --
1370 End if;
1371 --
1372 End loop; -- End of c_Actn
1373 --
1374 If (p_batch_flag) then
1375 --
1376 l_actn := 'Calling ld_actn...';
1377 --
1378 ld_actn(p_business_group_id => p_business_group_id
1379 ,p_effective_date => p_effective_date
1380 ,p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1381 ,p_before => FALSE
1382 ,p_after => TRUE
1383 ,p_idx_b => l_idx_b
1384 ,p_idx_e => l_idx_e
1385 );
1386 --
1387 l_actn := 'Calling Ben_batch_utils.cache_comp_obj...';
1388 --
1389 Ben_batch_utils.cache_comp_obj
1390 (p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
1391 ,p_effective_date => p_effective_date
1392 ,p_actn_cd => l_actn_cd
1393 );
1394 --
1395 If (l_actn_cd = 'D') then
1396 --
1397 benutils.write
1398 (p_text => '> Enrollment(' ||
1399 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') Ended ');
1400 --
1401 Elsif (l_actn_cd = 'U') then
1402 --
1403 benutils.write
1404 (p_text => '> Enrollment(' ||
1405 to_char(l_process_rec(i).prtt_enrt_rslt_id) || ') unsuspended ');
1406 --
1407 End if;
1408 --
1409 End if;
1410 --
1411 End if;
1412 --
1413 End loop; -- End of Irec loop
1414 --
1415 If p_validate then
1416 rollback to validate_point;
1417 End if;
1418 --
1419 If (p_batch_flag) then
1420 --
1421 write_person_category
1422 (p_audit_log => p_audit_log
1423 ,p_business_group_id => p_business_group_id
1424 ,P_person_id => p_person_id
1425 ,p_effective_date => p_effective_date);
1426 --
1427 If p_person_action_id is not null then
1428 --
1429 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
1430 --
1431 ben_person_actions_api.update_person_actions
1432 (p_person_action_id => p_person_action_id
1433 ,p_action_status_cd => 'P'
1434 ,p_object_version_number => l_object_version_number
1435 ,p_effective_date => p_effective_date);
1436 --
1437 End if;
1438 --
1439 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
1440 g_persons_procd := g_persons_procd + 1;
1441 --
1442 End if;
1443 --
1444 hr_utility.set_location('Leaving:'||l_proc, 10);
1445 --
1446 Exception
1447 --
1448 When others then
1449 --
1450 rollback to validate_point;
1451 --
1452 hr_utility.set_location('Exception handled in cls_per...', 10);
1453 If (p_batch_flag) then
1454 --
1455 g_persons_errored := g_persons_errored + 1;
1456 ben_batch_utils.write_error_rec;
1457 --
1458 ben_batch_utils.rpt_error(p_proc => l_proc
1459 ,p_last_actn => l_actn
1460 ,p_rpt_flag => TRUE);
1461 --
1462 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1463 ,p_effective_date => p_effective_date
1464 );
1465 --
1466 Ben_batch_utils.write(p_text => ' << Transactions Rollbacked >> ');
1467 --
1468 If p_person_action_id is not null then
1469 --
1470 ben_person_actions_api.update_person_actions
1471 (p_person_action_id => p_person_action_id
1472 ,p_action_status_cd => 'E'
1473 ,p_object_version_number => l_object_version_number
1474 ,p_effective_date => p_effective_date);
1475 --
1476 End if;
1477 --
1478 write_person_category
1479 (p_audit_log => p_audit_log
1480 ,p_error => TRUE
1481 ,p_business_group_id => p_business_group_id
1482 ,p_person_id => p_person_id
1483 ,p_effective_date => p_effective_date);
1484 --
1485 benutils.write_table_and_file(p_table => TRUE, p_file => FALSE);
1486 --
1487 End if;
1488 --
1489 fnd_message.raise_error;
1490 --
1491 end cls_per_unresolved_actn_item;
1492 --
1493 -- ----------------------------------------------------------------------------
1494 -- |--------------------------< do_multithread >------------------------------|
1495 -- ----------------------------------------------------------------------------
1496 --
1497 procedure do_multithread
1498 (errbuf out nocopy varchar2
1499 ,retcode out nocopy number
1500 ,p_validate in varchar2 default 'N'
1501 ,p_benefit_action_id in number
1502 ,p_thread_id in number
1503 ,p_effective_date in varchar2
1504 ,p_business_group_id in number
1505 ,p_audit_log in varchar2 default 'N'
1506 )
1507 is
1508 --
1509 -- Local variable declaration
1510 --
1511 l_proc varchar2(80) := g_package||'.do_multithread';
1512 --
1513 l_effective_date date;
1514 l_person_id ben_person_actions.person_id%type;
1515 l_person_action_id ben_person_actions.person_action_id%type;
1516 l_object_version_number ben_person_actions.object_version_number%type;
1517 l_ler_id ben_person_actions.ler_id%type;
1518 l_range_id ben_batch_ranges.range_id%type;
1519 l_record_number number := 0;
1520 l_start_person_action_id number := 0;
1521 l_end_person_action_id number := 0;
1522 l_actn varchar2(80);
1523 l_cnt number(5):= 0;
1524 l_validate boolean;
1525 l_threads number;
1526 l_chunk_size number;
1527 --
1528 -- Cursors declaration
1529 --
1530 cursor c_range_thread
1531 is
1532 select ran.range_id
1533 ,ran.starting_person_action_id
1534 ,ran.ending_person_action_id
1535 from ben_batch_ranges ran
1536 where ran.range_status_cd = 'U'
1537 and ran.benefit_action_id = p_benefit_action_id
1538 and rownum < 2
1539 for update of ran.range_status_cd;
1540 --
1541 cursor c_person_thread
1542 is
1543 select ben.person_id
1544 ,ben.person_action_id
1545 ,ben.object_version_number
1546 ,ben.ler_id
1547 from ben_person_actions ben
1548 where ben.benefit_action_id = p_benefit_action_id
1549 and ben.action_status_cd <> 'P'
1550 and ben.person_action_id between l_start_person_action_id
1551 and l_end_person_action_id
1552 order by ben.person_action_id;
1553 --
1554 cursor c_parameter
1555 is
1556 select *
1557 from ben_benefit_actions ben
1558 where ben.benefit_action_id = p_benefit_action_id;
1559 --
1560 cursor c_master is
1561 select 'Y'
1562 from ben_benefit_actions bft
1563 where bft.benefit_action_id = p_benefit_action_id
1564 and bft.request_id = fnd_global.conc_request_id;
1565 --
1566 l_parm c_parameter%rowtype;
1567 l_commit number;
1568 l_master varchar2(1) := 'N';
1569 --
1570 Begin
1571 --
1572 hr_utility.set_location ('Entering '||l_proc,5);
1573 --
1574 -- Convert varchar2 dates to real dates
1575 -- 1) First remove time component
1576 -- 2) Next convert format
1577 --
1578 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1579 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
1580 ,'DD/MM/RRRR');
1581 --
1582 -- Put row in fnd_sessions
1583 --
1584 dt_fndate.change_ses_date
1585 (p_ses_date => l_effective_date,
1586 p_commit => l_commit);
1587 --
1588 l_actn := 'Calling benutils.get_parameter...';
1589 benutils.get_parameter(p_business_group_id => p_business_group_Id
1590 ,p_batch_exe_cd => 'BENUNEAI'
1591 ,p_threads => l_threads
1592 ,p_chunk_size => l_chunk_size
1593 ,p_max_errors => g_max_person_err);
1594 --
1595 -- Set up benefits environment
1596 --
1597 ben_env_object.init(p_business_group_id => p_business_group_id,
1598 p_effective_date => l_effective_date,
1599 p_thread_id => p_thread_id,
1600 p_chunk_size => l_chunk_size,
1601 p_threads => l_threads,
1602 p_max_errors => g_max_person_err,
1603 p_benefit_action_id => p_benefit_action_id);
1604 --
1605 g_persons_procd := 0;
1606 g_persons_errored := 0;
1607 --
1608 ben_batch_utils.ini;
1609 --
1610 benutils.g_benefit_action_id := p_benefit_action_id;
1611 benutils.g_thread_id := p_thread_id;
1612 --
1613 open c_master;
1614 fetch c_master into l_master;
1615 close c_master;
1616 --
1617 if p_validate = 'Y'
1618 then
1619 l_validate := TRUE;
1620 else
1621 l_validate := FALSE;
1622 end if;
1623 --
1624 open c_parameter;
1625 fetch c_parameter into l_parm;
1626 close c_parameter;
1627 --
1628 if fnd_global.conc_request_id <> -1
1629 then
1630 --
1631 -- Print the batch parameters to the log file if this program was called by
1632 -- the concurrent manager.
1633 --
1634 Ben_batch_utils.print_parameters
1635 (p_thread_id => p_thread_id
1636 ,p_benefit_action_id => p_benefit_action_id
1637 ,p_validate => p_validate
1638 ,p_business_group_id => p_business_group_id
1639 ,p_effective_date => l_effective_date
1640 ,p_person_id => l_parm.person_id
1641 ,p_person_selection_rule_id => l_parm.person_selection_rl
1642 ,p_location_id => l_parm.location_id
1643 ,p_audit_log => p_audit_log);
1644 --
1645 end if;
1646 --
1647 -- The processing for this thread is as follows:
1648 -- 1) Lock the rows in ben_batch_ranges that are not processed.
1649 -- 2) Fetch the start and ending person action id for the range.
1650 -- 3) Loop through the person actions in the range and close unresolved
1651 -- action items for each.
1652 -- 4) Go to number 1 again and repeat until all ranges are processed.
1653 --
1654 loop
1655 --
1656 open c_range_thread;
1657 fetch c_range_thread
1658 into l_range_id,l_start_person_action_id,l_end_person_action_id;
1659 --
1660 if c_range_thread%notfound
1661 then
1662 close c_range_thread;
1663 exit;
1664 end if;
1665 --
1666 close c_range_thread;
1667 --
1668 update ben_batch_ranges ran
1669 set ran.range_status_cd = 'P'
1670 where ran.range_id = l_range_id;
1671 --
1672 commit;
1673 --
1674 g_cache_per_proc.delete;
1675 --
1676 l_record_number := 0;
1677 --
1678 -- Loop through all the person actions for the batch range being processed
1679 -- and close the unresolved action items.
1680 --
1681 for l_rec in c_person_thread
1682 loop
1683 --
1684 hr_utility.set_location('person_id : ' || l_rec.person_id, 10);
1685 --
1686 g_person_actn_cnt := g_person_actn_cnt + 1;
1687 --
1688 begin
1689 --
1690 ben_cls_unresolved_actn_item.cls_per_unresolved_actn_item
1691 (p_person_id => l_rec.person_id
1692 ,p_person_action_id => l_rec.person_action_id
1693 ,p_object_version_number => l_rec.object_version_number
1694 ,p_effective_date => l_effective_date
1695 ,p_business_group_id => p_business_group_id
1696 ,p_overwrite_flag => FALSE
1697 ,p_validate => l_validate
1698 ,p_batch_flag => TRUE
1699 ,p_audit_log => p_audit_log);
1700 --
1701 Exception
1702 When others then
1703 If (g_persons_errored > g_max_person_err) then
1704 fnd_message.raise_error;
1705 End if;
1706 End;
1707
1708 End loop;
1709 --
1710 --
1711 End loop;
1712 --
1713 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1714 ,p_num_pers_errored => g_persons_errored);
1715 --
1716 --
1717 -- Check if all the slave processes are finished.
1718 --
1719 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1720 --
1721 -- End the process.
1722 --
1723 ben_batch_utils.end_process
1724 (p_benefit_action_id => p_benefit_action_id
1725 ,p_person_selected => g_person_actn_cnt
1726 ,p_business_group_id => p_business_group_id);
1727 --
1728 -- Submit reports.
1729 --
1730 if l_master = 'Y' then
1731 --
1732 submit_all_reports(p_audit_log => p_audit_log);
1733 --
1734 end if;
1735 --
1736 hr_utility.set_location ('Leaving '||l_proc,70);
1737 --
1738 Exception
1739 --
1740 when others
1741 then
1742 --
1743 rollback;
1744 --
1745 ben_batch_utils.rpt_error(p_proc => l_proc
1746 ,p_last_actn => l_actn
1747 ,p_rpt_flag => TRUE);
1748 --
1749 benutils.write(p_text => fnd_message.get);
1750 benutils.write(p_text => sqlerrm);
1751 --
1752 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1753 --
1754 ben_batch_utils.end_process(p_benefit_action_id => p_benefit_action_id
1755 ,p_person_selected => g_person_actn_cnt
1756 ,p_business_group_id => p_business_group_id);
1757 --
1758 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1759 --
1760 commit;
1761 --
1762 fnd_message.raise_error;
1763 --
1764 end do_multithread;
1765 --
1766 -- ----------------------------------------------------------------------------
1767 -- |-----------------------------< restart >----------------------------------|
1768 -- ----------------------------------------------------------------------------
1769 --
1770 procedure restart
1771 (errbuf out nocopy varchar2
1772 ,retcode out nocopy number
1773 ,p_benefit_action_id in number)
1774 is
1775 --
1776 -- Cursor Declaration
1777 --
1778 cursor c_parameters
1779 is
1780 select to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
1781 ,business_group_id
1782 ,pgm_id
1783 ,pl_id
1784 ,location_id
1785 ,ler_id
1786 ,popl_enrt_typ_cycl_id
1787 ,person_id
1788 ,person_selection_rl
1789 ,validate_flag
1790 ,debug_messages_flag
1791 ,audit_log_flag
1792 from ben_benefit_actions ben
1793 where ben.benefit_action_id = p_benefit_action_id;
1794 --
1795 -- Local Variable declaration.
1796 --
1797 l_proc varchar2(80) := g_package||'.restart';
1798 l_parameters c_parameters%rowtype;
1799 l_errbuf varchar2(80);
1800 l_retcode number;
1801 l_actn varchar2(80);
1802 --
1803 Begin
1804 --
1805 hr_utility.set_location ('Entering ' || l_proc, 10);
1806 --
1807 -- get the parameters for a previous run and do a restart
1808 --
1809 open c_parameters;
1810 fetch c_parameters into l_parameters;
1811 --
1812 If c_parameters%notfound
1813 then
1814 close c_parameters;
1815 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
1816 fnd_message.raise_error;
1817 End if;
1818 --
1819 close c_parameters;
1820 --
1821 -- Call the "process" procedure with parameters for restart
1822 --
1823 process(errbuf => l_errbuf
1824 ,retcode => l_retcode
1825 ,p_benefit_action_id => p_benefit_action_id
1826 ,p_effective_date => l_parameters.process_date
1827 ,p_validate => l_parameters.validate_flag
1828 ,p_business_group_id => l_parameters.business_group_id
1829 ,p_pgm_id => l_parameters.pgm_id
1830 ,p_pl_nip_id => l_parameters.pl_id
1831 ,p_location_id => l_parameters.location_id
1832 ,p_person_id => l_parameters.person_id
1833 ,p_debug_messages => l_parameters.debug_messages_flag
1834 ,p_audit_log => l_parameters.audit_log_flag
1835 );
1836 --
1837 hr_utility.set_location ('Leaving ' || l_proc, 70);
1838 --
1839 End restart;
1840 --
1841 -- ----------------------------------------------------------------------------
1842 -- |------------------------------< process >---------------------------------|
1843 -- ----------------------------------------------------------------------------
1844 --
1845 -- This is the main procedure that is called from the concurrent manager.
1846 --
1847 procedure process
1848 (errbuf out nocopy varchar2
1849 ,retcode out nocopy number
1850 ,p_benefit_action_id in number
1851 ,p_effective_date in varchar2
1852 ,p_business_group_id in number
1853 ,p_pgm_id in number default NULL
1854 ,p_pl_nip_id in number default NULL
1855 ,p_location_id in number default NULL
1856 ,p_person_id in number default NULL
1857 ,p_person_selection_rl in number default NULL
1858 ,p_validate in varchar2 default 'N'
1859 ,p_debug_messages in varchar2 default 'N'
1860 ,p_audit_log in varchar2 default 'N'
1861 )
1862 is
1863 --
1864 l_effective_date date;
1865 --
1866 -- Cursor Declaration.
1867 --
1868 cursor c_pen
1869 is
1870 select distinct pen.person_id
1871 from ben_prtt_enrt_rslt_f pen
1872 ,ben_per_in_ler pil
1873 ,ben_prtt_enrt_actn_f actn
1874 where pen.business_group_id = p_business_group_id
1875 and pen.business_group_id = actn.business_group_id
1876 and pen.prtt_enrt_rslt_stat_cd is null
1877 and pen.prtt_enrt_rslt_id = actn.prtt_enrt_rslt_id
1878 and nvl(pen.effective_end_date,hr_api.g_eot) = hr_api.g_eot
1879 and nvl(actn.effective_end_date,hr_api.g_eot) = hr_api.g_eot
1880 and actn.cmpltd_dt is null
1881 and (p_person_id is null or
1882 pen.person_id = p_person_id)
1883 and (p_location_id is null or
1884 exists ( select null
1885 from per_assignments_f asg
1886 where asg.person_id = pen.person_id
1887 and asg.assignment_type <> 'C'
1888 and asg.location_id = p_location_id
1889 and asg.business_group_id = p_business_group_id
1890 and l_effective_date between asg.effective_start_date
1891 and asg.effective_end_date))
1892 and (p_pgm_id is null or
1893 pen.pgm_id = p_pgm_id)
1894 and (p_pl_nip_id is null or
1895 (pen.pl_id = p_pl_nip_id and pen.pgm_id is null))
1896 and pil.per_in_ler_id=actn.per_in_ler_id
1897 and pil.business_group_id=actn.business_group_id
1898 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1899 ;
1900 --
1901 -- Local variable declaration.
1902 --
1903 l_proc varchar2(80) := g_package||'.Process';
1904 --
1905 l_object_version_number Number(15);
1906 l_datetrack_mode varchar2(80);
1907 l_actn varchar2(80);
1908 l_request_id number;
1909 l_benefit_action_id number(15);
1910 l_person_action_id number(15);
1911 l_range_id number(15);
1912 l_chunk_size number := 20;
1913 l_num_ranges number := 0;
1914 l_threads number := 1;
1915 l_person_cnt number := 0;
1916 l_chunk_num number := 1;
1917 l_person_ok varchar2(1) := 'Y';
1918 l_person_actn_cnt number := 0;
1919 l_start_person_actn_id number(15);
1920 l_end_person_actn_id number(15);
1921 l_commit number;
1922 --
1923 Begin
1924 --
1925 hr_utility.set_location ('Entering '||l_proc,5);
1926 --
1927 -- Convert varchar2 dates to real dates
1928 -- 1) First remove time component
1929 -- 2) Next convert format
1930 --
1931 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1932 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
1933 ,'DD/MM/RRRR');
1934 --
1935 -- Put row in fnd_sessions
1936 --
1937 dt_fndate.change_ses_date
1938 (p_ses_date => l_effective_date,
1939 p_commit => l_commit);
1940 --
1941 -- Make sure all the mandatory input parameters are not null
1942 --
1943 hr_api.mandatory_arg_error(p_api_name => l_proc
1944 ,p_argument => 'p_effective_date'
1945 ,p_argument_value => l_effective_date
1946 );
1947 --
1948 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
1949 --
1950 -- Get the parameters that were defined for this batch process.
1951 --
1952 benutils.get_parameter(p_business_group_id => p_business_group_id
1953 ,p_batch_exe_cd => 'BENUNEAI'
1954 ,p_threads => l_threads
1955 ,p_chunk_size => l_chunk_size
1956 ,p_max_errors => g_max_person_err);
1957 --
1958 -- Create benefit actions parameters in the benefit action table.
1959 -- Do not create if a benefit action already exists, in other words
1960 -- we are doing a restart.
1961 --
1962 If p_benefit_action_id is null then
1963 --
1964 ben_benefit_actions_api.create_benefit_actions
1965 (p_validate => false
1966 ,p_benefit_action_id => l_benefit_action_id
1967 ,p_process_date => l_effective_date
1968 ,p_mode_cd => 'S'
1969 ,p_derivable_factors_flag => 'N'
1970 ,p_validate_flag => p_validate
1971 ,p_person_id => p_person_id
1972 ,p_person_type_id => NULL
1973 ,p_pgm_id => p_pgm_id
1974 ,p_business_group_id => p_business_group_id
1975 ,p_pl_id => p_pl_nip_id
1976 ,p_popl_enrt_typ_cycl_id => NULL
1977 ,p_no_programs_flag => 'N'
1978 ,p_no_plans_flag => 'N'
1979 ,p_comp_selection_rl => NULL
1980 ,p_person_selection_rl => p_person_selection_rl
1981 ,p_ler_id => NULL
1982 ,p_organization_id => NULL
1983 ,p_benfts_grp_id => NULL
1984 ,p_location_id => p_location_id
1985 ,p_pstl_zip_rng_id => NULL
1986 ,p_rptg_grp_id => NULL
1987 ,p_pl_typ_id => NULL
1988 ,p_opt_id => NULL
1989 ,p_eligy_prfl_id => NULL
1990 ,p_vrbl_rt_prfl_id => NULL
1991 ,p_legal_entity_id => NULL
1992 ,p_payroll_id => NULL
1993 ,p_debug_messages_flag => p_debug_messages
1994 ,p_audit_log_flag => p_audit_log
1995 ,p_object_version_number => l_object_version_number
1996 ,p_effective_date => l_effective_date
1997 ,p_request_id => fnd_global.conc_request_id
1998 ,p_program_application_id => fnd_global.prog_appl_id
1999 ,p_program_id => fnd_global.conc_program_id
2000 ,p_program_update_date => sysdate);
2001 --
2002 benutils.g_benefit_action_id := l_benefit_action_id;
2003 benutils.g_thread_id := 99;
2004 --
2005 -- Loop through rows in ben_per_in_ler_f based on the parameters passed and
2006 -- create person actions for the selected people.
2007 --
2008 for l_rec in c_pen
2009 loop
2010 --
2011 -- set variables for this iteration
2012 --
2013 l_person_ok := 'Y';
2014 --
2015 -- Check the person selection rule.
2016 --
2017 if p_person_selection_rl is not null
2018 then
2019 --
2020 l_person_ok := ben_batch_utils.person_selection_rule
2021 (p_person_id => l_rec.person_id
2022 ,p_business_group_id => p_business_group_id
2023 ,p_person_selection_rule_id => p_person_selection_rl
2024 ,p_effective_date => l_effective_date);
2025 --
2026 end if;
2027 --
2028 if l_person_ok = 'Y'
2029 then
2030 --
2031 -- Either no person sel rule or person selection rule passed. Create a
2032 -- person action row.
2033 --
2034 ben_person_actions_api.create_person_actions
2035 (p_validate => FALSE
2036 ,p_person_action_id => l_person_action_id
2037 ,p_person_id => l_rec.person_id
2038 ,p_benefit_action_id => l_benefit_action_id
2039 ,p_action_status_cd => 'U'
2040 ,p_chunk_number => l_chunk_num
2041 ,p_object_version_number => l_object_version_number
2042 ,p_effective_date => l_effective_date);
2043 --
2044 -- increment the person action count and Set the ending person action id
2045 -- to the last person action id that got created
2046 --
2047 l_person_actn_cnt := l_person_actn_cnt + 1;
2048 l_end_person_actn_id := l_person_action_id;
2049 --
2050 -- We have to create batch ranges based on the number of person actions
2051 -- created and the chunk size defined for the batch process.
2052 --
2053 if mod(l_person_actn_cnt, l_chunk_size) = 1 or l_chunk_size = 1
2054 then
2055 --
2056 -- This is the first person action id in a new range.
2057 --
2058 l_start_person_actn_id := l_person_action_id;
2059 --
2060 end if;
2061 --
2062 if mod(l_person_actn_cnt, l_chunk_size) = 0 or l_chunk_size = 1
2063 then
2064 --
2065 -- The number of person actions that got created equals the chunk
2066 -- size. Create a batch range for the person actions.
2067 --
2068 ben_batch_ranges_api.create_batch_ranges
2069 (p_validate => FALSE
2070 ,p_effective_date => l_effective_date
2071 ,p_benefit_action_id => l_benefit_action_id
2072 ,p_range_id => l_range_id
2073 ,p_range_status_cd => 'U'
2074 ,p_starting_person_action_id => l_start_person_actn_id
2075 ,p_ending_person_action_id => l_end_person_actn_id
2076 ,p_object_version_number => l_object_version_number);
2077 --
2078 l_num_ranges := l_num_ranges + 1;
2079 l_chunk_num := l_chunk_num + 1;
2080 --
2081 end if;
2082 --
2083 end if;
2084 --
2085 end loop;
2086 --
2087 -- There may be a few person actions left over from the loop above that may
2088 -- not have got inserted into a batch range because the number was less than
2089 -- the chunk size. Create a range for the remaining person actions. This
2090 -- also applies when only one person gets selected.
2091 --
2092 if l_person_actn_cnt > 0 and
2093 mod(l_person_actn_cnt, l_chunk_size) <> 0
2094 then
2095 --
2096 ben_batch_ranges_api.create_batch_ranges
2097 (p_validate => FALSE
2098 ,p_effective_date => l_effective_date
2099 ,p_benefit_action_id => l_benefit_action_id
2100 ,p_range_id => l_range_id
2101 ,p_range_status_cd => 'U'
2102 ,p_starting_person_action_id => l_start_person_actn_id
2103 ,p_ending_person_action_id => l_end_person_actn_id
2104 ,p_object_version_number => l_object_version_number);
2105 --
2106 l_num_ranges := l_num_ranges + 1;
2107 --
2108 end if;
2109 --
2110 Else
2111 --
2112 -- Benefit action id is not null i.e. the batch process is being restarted
2113 -- for a certain benefit action id. Create batch ranges and person actions
2114 -- for restarting.
2115 --
2116 l_benefit_action_id := p_benefit_action_id;
2117 --
2118 hr_utility.set_location('Restarting for benefit action id : ' ||
2119 to_char(l_benefit_action_id), 10);
2120 --
2121 ben_batch_utils.create_restart_person_actions
2122 (p_benefit_action_id => p_benefit_action_id
2123 ,p_effective_date => l_effective_date
2124 ,p_chunk_size => l_chunk_size
2125 ,p_threads => l_threads
2126 ,p_num_ranges => l_num_ranges
2127 ,p_num_persons => l_person_cnt);
2128 --
2129 end if;
2130 --
2131 commit;
2132 --
2133 -- Submit requests to the concurrent manager based on the number of ranges
2134 -- that got created.
2135 --
2136 if l_num_ranges > 1
2137 then
2138 --
2139 hr_utility.set_location('More than one range got created.', 10);
2140 --
2141 -- Set the number of threads to the lesser of the defined number of threads
2142 -- and the number of ranges created above. There's no point in submitting
2143 -- 5 threads for only two ranges.
2144 --
2145 l_threads := least(l_threads, l_num_ranges);
2146 --
2147 for l_count in 1..(l_threads - 1)
2148 loop
2149 --
2150 -- We are subtracting one from the number of threads because the main
2151 -- process will act as the last thread and will be able to keep track of
2152 -- the child requests that get submitted.
2153 --
2154 hr_utility.set_location('Submitting request ' || l_count, 10);
2155 --
2156 l_request_id := fnd_request.submit_request
2157 (application => 'BEN'
2158 ,program => 'BENUNEAIS'
2159 ,description => NULL
2160 ,sub_request => FALSE
2161 ,argument1 => p_validate
2162 ,argument2 => l_benefit_action_id
2163 ,argument3 => l_count
2164 ,argument4 => p_effective_date
2165 ,argument5 => p_business_group_id
2166 ,argument6 => p_audit_log);
2167 --
2168 -- Store the request id of the concurrent request
2169 --
2170 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
2171 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
2172 := l_request_id;
2173 --
2174 end loop;
2175 --
2176 elsif l_num_ranges = 0
2177 then
2178 --
2179 hr_utility.set_location('No people selected', 10);
2180 -- No ranges got created. i.e. no people got selected. Error out.
2181 --
2182 ben_batch_utils.print_parameters
2183 (p_thread_id => 99
2184 ,p_benefit_action_id => l_benefit_action_id
2185 ,p_validate => p_validate
2186 ,p_business_group_id => p_business_group_id
2187 ,p_effective_date => l_effective_date
2188 ,p_person_id => p_person_id
2189 ,p_person_selection_rule_id => p_person_selection_rl
2190 ,p_location_id => p_location_id
2191 );
2192 --
2193 ben_batch_utils.write(p_text =>
2194 'No person got selected with above selection criteria.');
2195 --
2196 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
2197 fnd_message.raise_error;
2198 --
2199 end if;
2200 --
2201 -- Carry on with the master. This will ensure that the master finishes last.
2202 --
2203 hr_utility.set_location('Submitting the master process', 10);
2204 --
2205 do_multithread
2206 (errbuf => errbuf
2207 ,retcode => retcode
2208 ,p_validate => p_validate
2209 ,p_benefit_action_id => l_benefit_action_id
2210 ,p_thread_id => l_threads
2211 ,p_effective_date => p_effective_date
2212 ,p_business_group_id => p_business_group_id
2213 ,p_audit_log => p_audit_log);
2214 --
2215 hr_utility.set_location ('Leaving ' || l_proc, 10);
2216 --
2217 End process;
2218 --
2219 End ben_cls_unresolved_Actn_item;