[Home] [Help]
PACKAGE BODY: APPS.BEN_GENERATE_COMMUNICATIONS
Source
1 package body ben_generate_communications as
2 /* $Header: bencommu.pkb 120.22.12020000.6 2012/07/03 12:17:35 amnaraya ship $ */
3 --
4 g_package varchar2(30) := 'ben_generate_communications.';
5 -- bwharton bug 1619271 added 5 globals below.
6 g_p_date_cd varchar2(30);
7 g_p_lf_evt_ocrd_dt date;
8 g_p_effective_date date;
9 g_p_formula_id number;
10 g_p_person_id number;
11
12 type g_ct_eval is record
13 (CM_TYP_ID NUMBER,
14 EVAL_RESULT VARCHAR2(1)
15 );
16 type g_ct_eval_list is table of g_ct_eval;
17 g_ct_eval_tab g_ct_eval_list := g_ct_eval_list();
18 --
19 function rule_passes(p_rule_id in number,
20 p_person_id in number,
21 p_assignment_id in number,
22 p_business_group_id in number,
23 p_organization_id in number,
24 p_communication_type_id in number,
25 p_ler_id in number default null,
26 p_pgm_id in number default null,
27 p_pl_id in number default null,
28 p_pl_typ_id in number default null,
29 p_per_cm_id in number default null,
30 p_effective_date in date) return boolean is
31 --
32 l_proc varchar2(80) := g_package||'rule_passes';
33 l_outputs ff_exec.outputs_t;
34 l_jurisdiction_code pay_state_rules.jurisdiction_code%type;
35 l_loc_rec hr_locations_all%rowtype;
36 l_ass_rec per_all_assignments_f%rowtype;
37 --
38 begin
39 --
40 hr_utility.set_location('Entering: '||l_proc,10);
41 --
42 -- Steps
43 -- 1. If no rule exists return true
44 -- 2. If a rule exists, evaluate rule
45 -- 3. If evaluated value = 'N' return false
46 -- If evaluated rule = 'Y' return true
47 --
48 -- Step 1.
49 --
50 if p_rule_id is null then
51 --
52 hr_utility.set_location('Leaving for rule id null : '||l_proc,10);
53 return true;
54 --
55 else
56 --
57 -- Evaluate l_jurisdiction_code
58 --
59 ben_person_object.get_object(p_person_id => p_person_id,
60 p_rec => l_ass_rec);
61 --
62 if l_ass_rec.assignment_id is null then
63 --
64 -- Grab the persons benefits assignment instead
65 --
66 ben_person_object.get_benass_object(p_person_id => p_person_id,
67 p_rec => l_ass_rec);
68 --
69 end if;
70 --
71 if l_ass_rec.location_id is not null then
72 --
73 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
74 p_rec => l_loc_rec);
75 --
76 --if l_loc_rec.region_2 is not null then
77 -- l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
78 -- (p_state => l_loc_rec.region_2);
79 --end if;
80 --
81 end if;
82 --
83 -- Evaluate rule
84 --
85 -- Step 2.
86 --
87 l_outputs := benutils.formula
88 (p_formula_id => p_rule_id,
89 p_effective_date => p_effective_date,
90 p_assignment_id => p_assignment_id,
91 p_business_group_id => p_business_group_id,
92 p_organization_id => p_organization_id,
93 p_communication_type_id => p_communication_type_id,
94 p_ler_id => p_ler_id,
95 p_pgm_id => p_pgm_id,
96 p_pl_id => p_pl_id,
97 p_pl_typ_id => p_pl_typ_id,
98 p_per_cm_id => p_per_cm_id,
99 p_jurisdiction_code => l_jurisdiction_code,
100 --RCHASE Bug Fix - Formula requires person_id as input value
101 --RCHASE for individuals without assignments
102 p_param1 =>'PERSON_ID',
103 p_param1_value =>to_char(p_person_id)
104 );
105 --
106 -- Step 3.
107 --
108 if l_outputs(l_outputs.first).value = 'Y' then
109 --
110 return true;
111 --
112 elsif l_outputs(l_outputs.first).value = 'N' then
113 --
114 return false;
115 --
116 elsif l_outputs(l_outputs.first).value <> 'N' then
117 --
118 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
119 fnd_message.set_token('RL','formula_id :'||p_rule_id);
120 fnd_message.set_token('PROC',l_proc);
121 raise ben_manage_life_events.g_record_error;
122 --
123 end if;
124 --
125 end if;
126 --
127 hr_utility.set_location('Leaving: '||l_proc,10);
128 --
129 end rule_passes;
130 --
131 procedure pop_ben_per_cm_f
132 (p_person_id in number
133 ,p_ler_id in number
134 ,p_per_in_ler_id in number
135 ,p_prtt_enrt_actn_id in number
136 ,p_bnf_person_id in number
137 ,p_dpnt_person_id in number
138 ,p_cm_typ_id in number
139 ,p_lf_evt_ocrd_dt in date
140 ,p_rqstbl_untl_dt in date
141 ,p_business_group_id in number
142 ,p_effective_date in date
143 ,p_date_cd in varchar2
144 ,p_formula_id in number
145 ,p_pgm_id in number
146 ,p_pl_id in number
147 ,p_per_cm_id out nocopy number
148 )
149 is
150 --
151 l_proc varchar2(80) := g_package||'pop_ben_per_cm_f';
152 --
153 -- Output variables
154 --
155 l_object_version_number number;
156 l_effective_start_date date;
157 l_effective_end_date date;
158 l_notfound boolean;
159
160 --
161 cursor c_per_cm
162 (c_ler_id number
163 ,c_pna_id number
164 ,c_per_id number
165 ,c_bnfper_id number
166 ,c_dpntper_id number
167 ,c_cm_typ_id number
168 ,c_pil_id number
169 ,c_leo_dt date
170 ,c_eff_dt date
171 ,c_bgp_id number
172 ,c_comm_sdt date
173 )
174 is
175 select pcm.per_cm_id
176 from ben_per_cm_f pcm, ben_per_in_ler pil
177 -- if commu table has no ler id dont compare , pil_id take care
178 -- of validation # 3296015
179 where (pcm.ler_id is null or pcm.ler_id = c_ler_id)
180 and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
181 and nvl(pcm.person_id,-1) = nvl(c_per_id,-1)
182 and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
183 and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
184 and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
185 and nvl(pcm.per_in_ler_id,-1) = nvl(c_pil_id,-1)
186 and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
187 nvl(c_leo_dt,c_eff_dt)
188 and pcm.business_group_id = c_bgp_id
189 and c_comm_sdt
190 between pcm.effective_start_date
191 and pcm.effective_end_date
192 and pil.per_in_ler_id(+) = pcm.per_in_ler_id
193 and nvl(pil.business_group_id,c_bgp_id) =
194 c_bgp_id
195 and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD', 'BCKDT');
196 --
197 -- Added performant version of c_per_cm to fire only when PIL ID is set.
198 --
199 cursor c_pil_per_cm
200 (c_ler_id number
201 ,c_pna_id number
202 ,c_per_id number
203 ,c_bnfper_id number
204 ,c_dpntper_id number
205 ,c_cm_typ_id number
206 ,c_pil_id number
207 ,c_leo_dt date
208 ,c_eff_dt date
209 ,c_comm_sdt date
210 )
211 is
212 select pcm.per_cm_id
213 from ben_per_cm_f pcm,ben_per_cm_prvdd_f pcpf
214 where pcm.per_in_ler_id = c_pil_id
215 -- if commu table has no ler id dont compare , pil_id take care
216 -- of validation # 3296015
217 and (pcm.ler_id is null or pcm.ler_id = c_ler_id)
218 and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
219 and nvl(pcm.person_id,-1) = nvl(c_per_id,-1)
220 and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
221 and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
222 and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
223 and pcm.per_cm_id = pcpf.per_cm_id
224 and pcpf.per_cm_prvdd_stat_cd <> 'VOID'
225 -- Bug :7629124, Check to see if Communication is VOIDed.If VOIDed, then create a new Communication for the same LE
226 -- Added ben_per_cm_prvdd_f table to the cursor for verification
227 and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
228 nvl(c_leo_dt,c_eff_dt)
229 and c_comm_sdt
230 between pcm.effective_start_date and pcm.effective_end_date;
231 --
232 -- Added performant version of c_per_cm to fire only when PER ID is set.
233 --
234 cursor c_perid_per_cm
235 (c_ler_id number
236 ,c_pna_id number
237 ,c_per_id number
238 ,c_bnfper_id number
239 ,c_dpntper_id number
240 ,c_cm_typ_id number
241 ,c_leo_dt date
242 ,c_eff_dt date
243 ,c_comm_sdt date
244 )
245 is
246 select pcm.per_cm_id
247 from ben_per_cm_f pcm
248 where pcm.person_id = c_per_id
249 -- if commu table has no ler id dont compare , pil_id take care
250 -- of validation # 3296015
251 and (pcm.ler_id is null or pcm.ler_id = c_ler_id)
252 and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
253 and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
254 and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
255 and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
256 and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
257 nvl(c_leo_dt,c_eff_dt)
258 and c_comm_sdt
259 between pcm.effective_start_date and pcm.effective_end_date;
260
261 CURSOR c_pea
262 IS
263 SELECT effective_start_date
264 FROM ben_prtt_enrt_actn_f
265 WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id;
266 l_pea_esd date;
267 -- added cursor for bug: 5499162
268 CURSOR c_oipl
269 IS
270 SELECT oipl_id
271 FROM ben_prtt_enrt_rslt_f
272 WHERE prtt_enrt_rslt_id IN (
273 SELECT prtt_enrt_rslt_id
274 FROM ben_prtt_enrt_actn_f
275 WHERE prtt_enrt_actn_id =
276 p_prtt_enrt_actn_id);
277 l_oipl_id ben_prtt_enrt_rslt_f.oipl_id%TYPE;
278 -- end addition
279
280 cursor c_elig_rec(p_eff_date date) is
281 select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
282 ben_elig_obj_elig_profl_f obe
283 where p_eff_date between obj.effective_start_date
284 and obj.effective_end_date
285 and p_eff_date between obe.effective_start_date
286 and obe.effective_end_date
287 and obj.table_name = 'BEN_CM_TYP_F'
288 and obj.column_name = 'CM_TYP_ID'
289 and obj.column_value = p_cm_typ_id
290 and obj.business_group_id = p_business_group_id
291 and obe.business_group_id = p_business_group_id
292 and obj.ELIG_OBJ_ID = obe.ELIG_OBJ_ID;
293
294 l_elig_rec c_elig_rec%rowtype;
295 l_dummy_val varchar2(1);
296 l_eval varchar2(1);
297 l_result varchar2(1);
298 l_score_tab ben_evaluate_elig_profiles.scoreTab;
299 l_elig_flag varchar2(1);
300 l_found_elig boolean;
301 l_elig_flag1 varchar2(1);
302 l_continue_eval varchar2(1);
303
304 procedure is_eval(p_cm_typ_id number,
305 p_eval out nocopy varchar2,
306 p_result out nocopy varchar2) is
307 l_found boolean;
308 begin
309 l_found := false;
310 hr_utility.set_location('p_cm_typ_id '||p_cm_typ_id,1001);
311 if g_ct_eval_tab.count is not NULL and g_ct_eval_tab.count > 0 then
312 for i in g_ct_eval_tab.first..g_ct_eval_tab.last loop
313 hr_utility.set_location('inside for ',1001);
314 if(g_ct_eval_tab(i).CM_TYP_ID = p_cm_typ_id) then
315 l_found := true;
316 p_eval := 'Y';
317 p_result := g_ct_eval_tab(i).EVAL_RESULT;
318 end if;
319 end loop;
320 end if;
321
322 if(not l_found) then
323 p_eval := 'N';
324 p_result := 'N';
325 else
326 return;
327 end if;
328 exception
329 when others then
330 hr_utility.set_location('Error: '||SQLCODE,1001);
331 p_eval := 'N';
332 p_result := 'N';
333 end is_eval;
334
335 procedure add_ctp_elig(p_cm_typ_id number,
336 p_result varchar2) is
337 begin
338 g_ct_eval_tab.extend;
339 g_ct_eval_tab(g_ct_eval_tab.last).CM_TYP_ID := p_cm_typ_id;
340 g_ct_eval_tab(g_ct_eval_tab.last).EVAL_RESULT := p_result;
341 end add_ctp_elig;
342
343 begin
344 --
345 hr_utility.set_location('Entering: '||l_proc,10);
346 --
347 -- Reset the globals for the communication.
348 --
349 g_comm_start_date := null;
350 g_to_be_sent_dt := null;
351 --
352 begin
353 --
354 -- bwharton Bug 1619171.
355 -- Initialize globals to pass to check_hipaa_ctfn
356 -- this will allow the correct info to be passed to
357 -- ben_determine_date.
358 g_p_date_cd := p_date_cd;
359 g_p_person_id := p_person_id;
360 g_p_formula_id := p_formula_id;
361 g_p_effective_date := p_effective_date;
362 g_p_lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
363 --
364 -- change here for bug: 5499162
365 OPEN c_oipl;
366 FETCH c_oipl
367 INTO l_oipl_id;
368 CLOSE c_oipl;
369
370 ben_determine_date.main
371 (p_date_cd => p_date_cd,
372 p_per_in_ler_id => p_per_in_ler_id,
373 p_person_id => p_person_id,
374 p_pgm_id => p_pgm_id,
375 p_pl_id => p_pl_id,
376 p_business_group_id => p_business_group_id,
377 p_formula_id => p_formula_id,
378 p_effective_date => p_effective_date,
379 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
380 p_returned_date => g_to_be_sent_dt,
381 p_oipl_id => l_oipl_id); -- added for bug: 5499162
382 -- change end
383 --
384 exception
385 --
386 when others then
387 --
388 g_to_be_sent_dt := p_effective_date;
389 --
390 end;
391 --
392 g_comm_start_date := p_effective_date;
393 --
394 if g_to_be_sent_dt < g_comm_start_date then
395 --
396 g_comm_start_date := g_to_be_sent_dt;
397 --
398 end if;
399 --start 5332579
400 /***
401 if any issue use Alternate fix : bendenrr.pkb ,store backed_out_date to g_backed_out_date.
402 if g_comm_start_date < g_backed_out_date then g_comm_start_date = g_backed_out_date
403 ****/
404 IF p_prtt_enrt_actn_id IS NOT NULL
405 THEN
406 OPEN c_pea;
407 FETCH c_pea INTO l_pea_esd;
408
409 IF c_pea%FOUND
410 THEN
411 hr_utility.set_location ('PEA strt date ' || l_pea_esd, 10);
412
413 IF l_pea_esd > g_comm_start_date
414 THEN
415 --
416 g_comm_start_date := l_pea_esd;
417 --
418 END IF;
419
420 hr_utility.set_location ( 'final g_comm_start_date '
421 || g_comm_start_date,
422 10
423 );
424 CLOSE c_pea;
425 ELSE
426 CLOSE c_pea;
427 END IF;
428 END IF;
429
430 -- end 5332579
431 --
432 -- Check if the PIL ID is set. If so then fire c_pil_per_cm
433 -- rather than c_per_cm
434 --
435 hr_utility.set_location ('p_lf_evt_ocrd_dt 1'||p_lf_evt_ocrd_dt,101);
436 hr_utility.set_location ('p_effective_date 1'||p_effective_date,101);
437 hr_utility.set_location ('Condition'||ben_generate_communications.g_comm_start_date,101);
438 if p_per_in_ler_id is not null then
439 --
440 hr_utility.set_location ('Condition 1',101);
441 open c_pil_per_cm
442 (c_ler_id => p_ler_id
443 ,c_pna_id => p_prtt_enrt_actn_id
444 ,c_per_id => p_person_id
445 ,c_bnfper_id => p_bnf_person_id
446 ,c_dpntper_id => p_dpnt_person_id
447 ,c_cm_typ_id => p_cm_typ_id
448 ,c_pil_id => p_per_in_ler_id
449 ,c_leo_dt => p_lf_evt_ocrd_dt
450 ,c_eff_dt => p_effective_date
451 ,c_comm_sdt => ben_generate_communications.g_comm_start_date
452 );
453 fetch c_pil_per_cm into p_per_cm_id;
454 if c_pil_per_cm%notfound then
455 l_notfound := TRUE;
456 else
457 l_notfound := FALSE;
458 end if;
459 close c_pil_per_cm;
460 --
461 elsif p_person_id is not null
462 then
463 --
464 hr_utility.set_location ('Condition 2',101);
465 open c_perid_per_cm
466 (c_ler_id => p_ler_id
467 ,c_pna_id => p_prtt_enrt_actn_id
468 ,c_per_id => p_person_id
469 ,c_bnfper_id => p_bnf_person_id
470 ,c_dpntper_id => p_dpnt_person_id
471 ,c_cm_typ_id => p_cm_typ_id
472 ,c_leo_dt => p_lf_evt_ocrd_dt
473 ,c_eff_dt => p_effective_date
474 ,c_comm_sdt => ben_generate_communications.g_comm_start_date
475 );
476 fetch c_perid_per_cm into p_per_cm_id;
477 if c_perid_per_cm%notfound then
478 l_notfound := TRUE;
479 else
480 l_notfound := FALSE;
481 end if;
482 close c_perid_per_cm;
483 --
484 else
485 --
486 hr_utility.set_location ('Condition 3',101);
487 open c_per_cm
488 (c_ler_id => p_ler_id
489 ,c_pna_id => p_prtt_enrt_actn_id
490 ,c_per_id => p_person_id
491 ,c_bnfper_id => p_bnf_person_id
492 ,c_dpntper_id => p_dpnt_person_id
493 ,c_cm_typ_id => p_cm_typ_id
494 ,c_pil_id => p_per_in_ler_id
495 ,c_leo_dt => p_lf_evt_ocrd_dt
496 ,c_eff_dt => p_effective_date
497 ,c_bgp_id => p_business_group_id
498 ,c_comm_sdt => ben_generate_communications.g_comm_start_date
499 );
500 --
501 fetch c_per_cm into p_per_cm_id;
502 if c_per_cm%notfound then
503 l_notfound := TRUE;
504 else
505 l_notfound := FALSE;
506 end if;
507 close c_per_cm;
508 --
509 end if;
510 --
511 if l_notfound then
512 --
513 open c_elig_rec(ben_generate_communications.g_comm_start_date);
514 fetch c_elig_rec into l_elig_rec;
515 if(c_elig_rec%found) then
516 close c_elig_rec;
517 l_dummy_val := 'Y';
518 else
519 l_dummy_val := 'N';
520 close c_elig_rec;
521 end if;
522 l_eval := 'N';
523 l_result := 'N';
524 l_continue_eval := 'N' ;
525 l_found_elig := false;
526
527 hr_utility.set_location( 'cm_typ_id '||p_cm_typ_id,1001);
528 if(l_dummy_val = 'N' ) then
529 l_continue_eval := 'Y';
530 else
531 is_eval(p_cm_typ_id,
532 l_eval,
533 l_result);
534 end if;
535 hr_utility.set_location( 'l_eval '||l_eval,1001);
536 hr_utility.set_location( 'l_result '||l_result,1001);
537
538 if(l_eval = 'Y' and l_result = 'N') then
539 l_continue_eval := 'N' ;
540 elsif(l_eval = 'Y' and l_result = 'Y') then
541 l_continue_eval := 'Y' ;
542 elsif (l_eval = 'N' and l_dummy_val = 'Y') then
543 ben_eval_comm_ler_elig_profl.check_eligible
544 (p_person_id => p_person_id
545 ,p_effective_date => nvl(ben_generate_communications.g_comm_start_date,p_effective_date)
546 --,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
547 ,p_business_group_id => p_business_group_id
548 ,p_per_in_ler_id => p_per_in_ler_id
549 --,p_ler_id => p_ler_id
550 ,p_source => 'C'
551 ,p_col_value => p_cm_typ_id
552 ,p_elig_flag => l_elig_flag1);
553
554 if(l_elig_flag1 = 'Y') then
555 hr_utility.set_location( 'l_triggers.cm_typ_id Elig'||p_cm_typ_id,1001);
556 l_continue_eval := 'Y' ;
557 add_ctp_elig(p_cm_typ_id,'Y');
558 else
559 hr_utility.set_location( 'l_triggers.cm_typ_id Not Elig'||p_cm_typ_id,1001);
560 l_continue_eval := 'N' ;
561 add_ctp_elig(p_cm_typ_id,'N');
562 end if;
563 end if;
564 --
565 if(l_continue_eval = 'Y' ) then
566 ben_per_cm_api.create_per_cm_perf
567 (p_validate => false
568 ,p_per_cm_id => p_per_cm_id
569 ,p_effective_start_date => l_effective_start_date
570 ,p_effective_end_date => l_effective_end_date
571 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
572 ,p_rqstbl_untl_dt => p_rqstbl_untl_dt
573 ,p_ler_id => p_ler_id
574 ,p_per_in_ler_id => p_per_in_ler_id
575 ,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
576 ,p_person_id => p_person_id
577 ,p_bnf_person_id => p_bnf_person_id
578 ,p_dpnt_person_id => p_dpnt_person_id
579 ,p_cm_typ_id => p_cm_typ_id
580 ,p_business_group_id => p_business_group_id
581 ,p_pcm_attribute_category => null
582 ,p_pcm_attribute1 => null
583 ,p_pcm_attribute2 => null
584 ,p_pcm_attribute3 => null
585 ,p_pcm_attribute4 => null
586 ,p_pcm_attribute5 => null
587 ,p_pcm_attribute6 => null
588 ,p_pcm_attribute7 => null
589 ,p_pcm_attribute8 => null
590 ,p_pcm_attribute9 => null
591 ,p_pcm_attribute10 => null
592 ,p_pcm_attribute11 => null
593 ,p_pcm_attribute12 => null
594 ,p_pcm_attribute13 => null
595 ,p_pcm_attribute14 => null
596 ,p_pcm_attribute15 => null
597 ,p_pcm_attribute16 => null
598 ,p_pcm_attribute17 => null
599 ,p_pcm_attribute18 => null
600 ,p_pcm_attribute19 => null
601 ,p_pcm_attribute20 => null
602 ,p_pcm_attribute21 => null
603 ,p_pcm_attribute22 => null
604 ,p_pcm_attribute23 => null
605 ,p_pcm_attribute24 => null
606 ,p_pcm_attribute25 => null
607 ,p_pcm_attribute26 => null
608 ,p_pcm_attribute27 => null
609 ,p_pcm_attribute28 => null
610 ,p_pcm_attribute29 => null
611 ,p_pcm_attribute30 => null
612 ,p_object_version_number => l_object_version_number
613 ,p_effective_date => ben_generate_communications.
614 g_comm_start_date
615 ,p_request_id => fnd_global.conc_request_id
616 ,p_program_application_id => fnd_global.prog_appl_id
617 ,p_program_id => fnd_global.conc_program_id
618 ,p_program_update_date => sysdate
619 );
620 else
621 p_per_cm_id := -1;
622 end if;
623 --
624 end if;
625 --
626 hr_utility.set_location('Leaving: '||l_proc,10);
627 --
628 end pop_ben_per_cm_f;
629 --
630 procedure pop_ben_per_cm_trgr_f(p_per_cm_id in number,
631 p_cm_trgr_id in number,
632 p_business_group_id in number,
633 p_effective_date in date,
634 p_per_cm_trgr_id out nocopy number) is
635 --
636 l_proc varchar2(80) := g_package||'pop_ben_per_cm_trgr_f';
637 --
638 -- Output variables
639 --
640 l_object_version_number number;
641 l_effective_start_date date;
642 l_effective_end_date date;
643 --
644 cursor c_per_cm_trgr is
645 select null
646 from ben_per_cm_trgr_f pcr
647 where pcr.cm_trgr_id = p_cm_trgr_id
648 and pcr.per_cm_id = p_per_cm_id
649 and pcr.business_group_id = p_business_group_id
650 and ben_generate_communications.g_comm_start_date
651 between pcr.effective_start_date
652 and pcr.effective_end_date;
653 --
654 begin
655 --
656 hr_utility.set_location('Entering: '||l_proc,10);
657 --
658 open c_per_cm_trgr;
659 --
660 fetch c_per_cm_trgr into p_per_cm_trgr_id;
661 if c_per_cm_trgr%notfound then
662 --
663 ben_per_cm_trgr_api.create_per_cm_trgr_perf
664 (p_validate => false
665 ,p_per_cm_trgr_id => p_per_cm_trgr_id
666 ,p_effective_start_date => l_effective_start_date
667 ,p_effective_end_date => l_effective_end_date
668 ,p_cm_trgr_id => p_cm_trgr_id
669 ,p_per_cm_id => p_per_cm_id
670 ,p_business_group_id => p_business_group_id
671 ,p_pcr_attribute_category => null
672 ,p_pcr_attribute1 => null
673 ,p_pcr_attribute2 => null
674 ,p_pcr_attribute3 => null
675 ,p_pcr_attribute4 => null
676 ,p_pcr_attribute5 => null
677 ,p_pcr_attribute6 => null
678 ,p_pcr_attribute7 => null
679 ,p_pcr_attribute8 => null
680 ,p_pcr_attribute9 => null
681 ,p_pcr_attribute10 => null
682 ,p_pcr_attribute11 => null
683 ,p_pcr_attribute12 => null
684 ,p_pcr_attribute13 => null
685 ,p_pcr_attribute14 => null
686 ,p_pcr_attribute15 => null
687 ,p_pcr_attribute16 => null
688 ,p_pcr_attribute17 => null
689 ,p_pcr_attribute18 => null
690 ,p_pcr_attribute19 => null
691 ,p_pcr_attribute20 => null
692 ,p_pcr_attribute21 => null
693 ,p_pcr_attribute22 => null
694 ,p_pcr_attribute23 => null
695 ,p_pcr_attribute24 => null
696 ,p_pcr_attribute25 => null
697 ,p_pcr_attribute26 => null
698 ,p_pcr_attribute27 => null
699 ,p_pcr_attribute28 => null
700 ,p_pcr_attribute29 => null
701 ,p_pcr_attribute30 => null
702 ,p_object_version_number => l_object_version_number
703 ,p_effective_date => ben_generate_communications.
704 g_comm_start_date);
705 --
706 end if;
707 --
708 close c_per_cm_trgr;
709 --
710 hr_utility.set_location('Leaving: '||l_proc,10);
711 --
712 end pop_ben_per_cm_trgr_f;
713 --
714 procedure pop_ben_per_cm_prvdd_f(p_per_cm_id in number,
715 p_effective_date in date,
716 p_rqstd_flag in varchar2,
717 p_inspn_rqd_flag in varchar2,
718 p_per_cm_prvdd_stat_cd in varchar2,
719 p_cm_dlvry_med_cd in varchar2,
720 p_cm_dlvry_mthd_cd in varchar2,
721 p_sent_dt in date,
722 p_mode in varchar2,
723 p_dlvry_instn_txt in varchar2,
724 p_address_id in number,
725 p_business_group_id in number,
726 p_per_cm_prvdd_id out nocopy number) is
727 --
728 l_proc varchar2(80) := g_package||'pop_ben_per_cm_prvdd_f';
729 --
730 -- Output variables
731 --
732 l_object_version_number number;
733 l_effective_start_date date;
734 l_effective_end_date date;
735 --
736 cursor c_per_cm_prvdd is
737 select pcd.*
738 from ben_per_cm_prvdd_f pcd
739 where pcd.per_cm_id = p_per_cm_id
740 and pcd.sent_dt is null
741 and pcd.business_group_id = p_business_group_id
742 and ben_generate_communications.g_comm_start_date
743 between pcd.effective_start_date
744 and pcd.effective_end_date;
745 --
746 cursor c_get_instnc_num is
747 select max(pcd.instnc_num)
748 from ben_per_cm_prvdd_f pcd
749 where pcd.per_cm_id = p_per_cm_id
750 and pcd.business_group_id = p_business_group_id
751 and ben_generate_communications.g_comm_start_date
752 between pcd.effective_start_date
753 and pcd.effective_end_date;
754 --
755 l_instnc_num number;
756 l_pcd_rec c_per_cm_prvdd%rowtype;
757 --
758 begin
759 --
760 hr_utility.set_location('Entering: '||l_proc,10);
761 --
762 open c_per_cm_prvdd;
763 --
764 fetch c_per_cm_prvdd into l_pcd_rec;
765 --
766 if c_per_cm_prvdd%notfound then
767 close c_per_cm_prvdd;
768 --
769 -- Get the instance number
770 --
771 open c_get_instnc_num;
772 fetch c_get_instnc_num into l_instnc_num;
773 close c_get_instnc_num;
774 if l_instnc_num is null then
775 --
776 -- New communication.
777 --
778 l_instnc_num := 1;
779 else
780 l_instnc_num := l_instnc_num + 1;
781 end if;
782 --
783 ben_per_cm_prvdd_api.create_per_cm_prvdd_perf
784 (p_validate => false
785 ,p_per_cm_prvdd_id => p_per_cm_prvdd_id
786 ,p_effective_start_date => l_effective_start_date
787 ,p_effective_end_date => l_effective_end_date
788 ,p_rqstd_flag => p_rqstd_flag
789 ,p_inspn_rqd_flag => p_inspn_rqd_flag
790 ,p_per_cm_prvdd_stat_cd => p_per_cm_prvdd_stat_cd
791 ,p_cm_dlvry_med_cd => p_cm_dlvry_med_cd
792 ,p_cm_dlvry_mthd_cd => p_cm_dlvry_mthd_cd
793 ,p_sent_dt => p_sent_dt
794 ,p_instnc_num => l_instnc_num
795 ,p_to_be_sent_dt => g_to_be_sent_dt
796 ,p_dlvry_instn_txt => p_dlvry_instn_txt
797 ,p_per_cm_id => p_per_cm_id
798 ,p_address_id => p_address_id
799 ,p_business_group_id => p_business_group_id
800 ,p_pcd_attribute_category => null
801 ,p_pcd_attribute1 => null
802 ,p_pcd_attribute2 => null
803 ,p_pcd_attribute3 => null
804 ,p_pcd_attribute4 => null
805 ,p_pcd_attribute5 => null
806 ,p_pcd_attribute6 => null
807 ,p_pcd_attribute7 => null
808 ,p_pcd_attribute8 => null
809 ,p_pcd_attribute9 => null
810 ,p_pcd_attribute10 => null
811 ,p_pcd_attribute11 => null
812 ,p_pcd_attribute12 => null
813 ,p_pcd_attribute13 => null
814 ,p_pcd_attribute14 => null
815 ,p_pcd_attribute15 => null
816 ,p_pcd_attribute16 => null
817 ,p_pcd_attribute17 => null
818 ,p_pcd_attribute18 => null
819 ,p_pcd_attribute19 => null
820 ,p_pcd_attribute20 => null
821 ,p_pcd_attribute21 => null
822 ,p_pcd_attribute22 => null
823 ,p_pcd_attribute23 => null
824 ,p_pcd_attribute24 => null
825 ,p_pcd_attribute25 => null
826 ,p_pcd_attribute26 => null
827 ,p_pcd_attribute27 => null
828 ,p_pcd_attribute28 => null
829 ,p_pcd_attribute29 => null
830 ,p_pcd_attribute30 => null
831 ,p_object_version_number => l_object_version_number
832 ,p_effective_date => ben_generate_communications.
833 g_comm_start_date);
834 --
835 else
836 close c_per_cm_prvdd;
837 end if;
838 --
839 hr_utility.set_location('Leaving: '||l_proc,10);
840 --
841 end pop_ben_per_cm_prvdd_f;
842 --
843 procedure pop_ben_per_cm_usg_f
844 (p_per_cm_id in number,
845 p_cm_typ_usg_id in number,
846 p_business_group_id in number,
847 p_effective_date in date,
848 p_per_cm_usg_id out nocopy number,
849 p_usage_created out nocopy boolean) is
850 --
851 l_proc varchar2(80) := g_package||'pop_ben_per_cm_usg_f';
852 --
853 -- Output variables
854 --
855 l_object_version_number number;
856 l_effective_start_date date;
857 l_effective_end_date date;
858 --
859 cursor c1 is
860 select pcu.per_cm_usg_id
861 from ben_per_cm_usg_f pcu
862 where pcu.per_cm_id = p_per_cm_id
863 and pcu.cm_typ_usg_id = p_cm_typ_usg_id
864 and pcu.business_group_id = p_business_group_id
865 and ben_generate_communications.g_comm_start_date between
866 pcu.effective_start_date and pcu.effective_end_date;
867 --
868 begin
869 --
870 hr_utility.set_location('Entering: '||l_proc,10);
871 --
872 p_usage_created := true;
873 --
874 open c1;
875 --
876 fetch c1 into p_per_cm_usg_id;
877 if c1%notfound then
878 --
879 --
880 ben_per_cm_usg_api.create_per_cm_usg_perf
881 (p_validate => false
882 ,p_per_cm_usg_id => p_per_cm_usg_id
883 ,p_effective_start_date => l_effective_start_date
884 ,p_effective_end_date => l_effective_end_date
885 ,p_per_cm_id => p_per_cm_id
886 ,p_cm_typ_usg_id => p_cm_typ_usg_id
887 ,p_business_group_id => p_business_group_id
888 ,p_pcu_attribute_category => null
889 ,p_pcu_attribute1 => null
890 ,p_pcu_attribute2 => null
891 ,p_pcu_attribute3 => null
892 ,p_pcu_attribute4 => null
893 ,p_pcu_attribute5 => null
894 ,p_pcu_attribute6 => null
895 ,p_pcu_attribute7 => null
896 ,p_pcu_attribute8 => null
897 ,p_pcu_attribute9 => null
898 ,p_pcu_attribute10 => null
899 ,p_pcu_attribute11 => null
900 ,p_pcu_attribute12 => null
901 ,p_pcu_attribute13 => null
902 ,p_pcu_attribute14 => null
903 ,p_pcu_attribute15 => null
904 ,p_pcu_attribute16 => null
905 ,p_pcu_attribute17 => null
906 ,p_pcu_attribute18 => null
907 ,p_pcu_attribute19 => null
908 ,p_pcu_attribute20 => null
909 ,p_pcu_attribute21 => null
910 ,p_pcu_attribute22 => null
911 ,p_pcu_attribute23 => null
912 ,p_pcu_attribute24 => null
913 ,p_pcu_attribute25 => null
914 ,p_pcu_attribute26 => null
915 ,p_pcu_attribute27 => null
916 ,p_pcu_attribute28 => null
917 ,p_pcu_attribute29 => null
918 ,p_pcu_attribute30 => null
919 ,p_object_version_number => l_object_version_number
920 ,p_effective_date => ben_generate_communications.
921 g_comm_start_date);
922 --
923 end if;
924 --
925 close c1;
926 --
927 hr_utility.set_location('Leaving: '||l_proc,10);
928 --
929 end pop_ben_per_cm_usg_f;
930 --
931 procedure populate_working_tables
932 (p_person_id in number,
933 p_cm_typ_id in number,
934 p_business_group_id in number,
935 p_effective_date in date,
936 p_cm_trgr_id in number,
937 p_inspn_rqd_flag in varchar2,
938 p_cm_dlvry_med_cd in varchar2,
939 p_cm_dlvry_mthd_cd in varchar2,
940 p_per_cm_id in number,
941 p_mode in varchar2 default 'I') is
942 --
943 l_proc varchar2(80) := g_package||'populate_working_tables';
944 l_per_cm_trgr_id number;
945 l_per_cm_prvdd_id number;
946 l_new_instnc boolean := TRUE;
947 --
948 begin
949 --
950 hr_utility.set_location('Entering: '||l_proc,10);
951 --
952 pop_ben_per_cm_trgr_f
953 (p_per_cm_id => p_per_cm_id,
954 p_cm_trgr_id => p_cm_trgr_id,
955 p_business_group_id => p_business_group_id,
956 p_effective_date => p_effective_date,
957 p_per_cm_trgr_id => l_per_cm_trgr_id);
958 --
959 pop_ben_per_cm_prvdd_f
960 (p_per_cm_id => p_per_cm_id,
961 p_rqstd_flag => 'N',
962 p_inspn_rqd_flag => p_inspn_rqd_flag,
963 p_per_cm_prvdd_stat_cd => 'ACTIVE',
964 p_cm_dlvry_med_cd => p_cm_dlvry_med_cd,
965 p_cm_dlvry_mthd_cd => p_cm_dlvry_mthd_cd,
966 p_sent_dt => null,
967 p_mode => p_mode,
968 p_dlvry_instn_txt => null,
969 p_address_id => null,
970 p_business_group_id => p_business_group_id,
971 p_effective_date => p_effective_date,
972 p_per_cm_prvdd_id => l_per_cm_prvdd_id);
973 --
974 g_commu_rec.person_id := p_person_id;
975 g_commu_rec.per_cm_id := p_per_cm_id;
976 g_commu_rec.cm_typ_id := p_cm_typ_id;
977 g_commu_rec.per_cm_prvdd_id := l_per_cm_prvdd_id;
978 g_commu_rec.to_be_sent_dt := g_to_be_sent_dt;
979 g_commu_rec.business_group_id := p_business_group_id;
980 --
981 hr_utility.set_location('Leaving: '||l_proc,10);
982 --
983 end populate_working_tables;
984 --
985 function get_cvg_strt_dt (p_elig_per_id number,
986 p_per_in_ler_id number )
987 return date is
988 --
989 cursor c_pep IS
990 select pgm_id,
991 pl_id
992 from ben_elig_per_f pep
993 where pep.per_in_ler_id = p_per_in_ler_id
994 and pep.elig_per_id = p_elig_per_id ;
995 --
996 cursor c_epe_pgm(p_pgm_id number,p_pl_id number) IS
997 select epe.fonm_cvg_strt_dt
998 from ben_pil_elctbl_chc_popl popl,
999 ben_elig_per_elctbl_chc epe
1000 where popl.per_in_ler_id = p_per_in_ler_id
1001 and popl.pgm_id = p_pgm_id
1002 and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id
1003 and NVL(epe.pl_id,-1) = nvl(p_pl_id, nvl(epe.pl_id,-1)) -- 5633934 : Added this condition
1004 and not exists (select 'x' from ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc epe1
1005 where popl1.per_in_ler_id = p_per_in_ler_id
1006 and popl1.pgm_id = p_pgm_id
1007 and epe1.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id
1008 and NVL(epe1.pl_id,-1) = nvl(p_pl_id, nvl(epe1.pl_id,-1))
1009 and epe.fonm_cvg_strt_dt > epe1.fonm_cvg_strt_dt); /*Added not exists clause for Bug 7268357*/
1010 --
1011 cursor c_epe_pl(p_pl_id number) IS
1012 select epe.fonm_cvg_strt_dt
1013 from ben_pil_elctbl_chc_popl popl,
1014 ben_elig_per_elctbl_chc epe
1015 where popl.per_in_ler_id = p_per_in_ler_id
1016 and popl.pl_id = p_pl_id
1017 and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id;
1018 --
1019 l_pl_id number;
1020 l_pgm_id number;
1021 l_cvg_date date;
1022 begin
1023 --
1024 open c_pep ;
1025 fetch c_pep into l_pgm_id,l_pl_id ;
1026 close c_pep;
1027 --
1028 IF l_pgm_id IS NOT NULL THEN
1029 open c_epe_pgm(l_pgm_id, l_pl_id);
1030 fetch c_epe_pgm into l_cvg_date ;
1031 close c_epe_pgm ;
1032 ELSIF l_pl_id IS NOT NULL THEN
1033 open c_epe_pl(l_pl_id);
1034 fetch c_epe_pl into l_cvg_date ;
1035 close c_epe_pl ;
1036 END IF;
1037 --
1038 /*
1039 open c_epe;
1040 fetch c_epe into l_cvg_date;
1041 close c_epe;
1042 */
1043 return l_cvg_date;
1044 --
1045 end get_cvg_strt_dt;
1046
1047 procedure check_first_time_elig_inelig
1048 (p_person_id in number,
1049 p_business_group_id in number,
1050 p_assignment_id in number,
1051 p_organization_id in number,
1052 -- PB : 5422 :
1053 -- p_enrt_perd_id in number,
1054 p_asnd_lf_evt_dt in date,
1055 p_actn_typ_id in number,
1056 p_per_cm_id in number,
1057 p_cm_typ_id in number,
1058 p_ler_id in number,
1059 p_pgm_id in number,
1060 p_pl_id in number,
1061 p_pl_typ_id in number,
1062 p_effective_date in date,
1063 p_lf_evt_ocrd_dt in date,
1064 p_eligible_flag in varchar2,
1065 p_whnvr_trgrd_flag in varchar2,
1066 p_usages_created out nocopy boolean) is
1067 --
1068 l_proc varchar2(80) := g_package||'check_first_time_elig_inelig';
1069 l_effective_date date;
1070 l_effective_date_1 date;
1071 --
1072 -- Bug 11654610
1073
1074 cursor c_prev_pil(c_per_in_ler_id number ) is
1075 select pil.per_in_ler_id
1076 from ben_per_in_ler pil,
1077 ben_ler_f ler
1078 where pil.per_in_ler_id not in (c_per_in_ler_id)
1079 and pil.person_id = p_person_id
1080 and pil.ler_id = ler.ler_id
1081 and p_effective_date between
1082 ler.effective_start_date and ler.effective_end_date
1083 and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1084 and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
1085 order by pil.lf_evt_ocrd_dt desc;
1086
1087 l_prev_pil_id number;
1088
1089 -- Bug 11654610
1090
1091
1092 /* Added for Bug 8227214 */
1093 cursor c_get_per_in_ler(c_lf_evt_ocrd_dt date) is
1094 select pil.per_in_ler_id from ben_per_in_ler pil,ben_ler_f le
1095 where pil.lf_evt_ocrd_dt=c_lf_evt_ocrd_dt
1096 and pil.ler_id=le.ler_id
1097 and pil.person_id=p_person_id
1098 and c_lf_evt_ocrd_dt between le.effective_start_date and le.effective_end_date
1099 and pil.per_in_ler_stat_cd NOT IN ( 'VOIDD', 'BCKDT')
1100 and le.typ_cd not in ('IREC','GSP','COMP','ABS','SCHEDDU','SCHEDDA');
1101
1102 l_per_in_ler_id number;
1103 /* End of Bug 8227214 */
1104
1105
1106 cursor c1(cv_effective_date date, cv_effective_date_1 date) is
1107 select ctu.cm_typ_usg_id,
1108 ctu.cm_usg_rl,
1109 ctu.pl_id, -- Bug 1555557
1110 ctu.pgm_id,
1111 ctu.ler_id,
1112 ctu.pl_typ_id,
1113 get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id) cvg_dt,
1114 pil.per_in_ler_id,
1115 pep.elig_per_id,
1116 pep.pgm_id ppgm_id,
1117 pep.pl_id ppl_id
1118 from ben_cm_typ_usg_f ctu,
1119 ben_elig_per_f pep,
1120 ben_per_in_ler pil
1121 where ctu.business_group_id = p_business_group_id
1122 and pep.business_group_id = ctu.business_group_id
1123 and pep.person_id = p_person_id
1124 and pil.per_in_ler_id = l_per_in_ler_id -- Bug 8227214
1125 and pep.per_in_ler_id = pil.per_in_ler_id -- Bug 8227214
1126 /*Commented condition for Bug 8227214 */
1127 /*and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)
1128 between pep.effective_start_date and pep.effective_end_date*/
1129 and nvl(ctu.ler_id,nvl(pil.ler_id,-1)) = nvl(pil.ler_id,-1)
1130 and nvl(ctu.pgm_id,nvl(pep.pgm_id,-1)) = nvl(pep.pgm_id,-1)
1131 /* Bug 8809596: Pick the correct eligibility record instead of looping through all the
1132 eligibility records when Plan Usage is Null or PlanType Usage is Null*/
1133 and ( (ctu.pl_typ_id is not null and nvl(ctu.pl_id,nvl(pep.pl_id,-1)) = nvl(pep.pl_id,-1) )
1134 or (ctu.pl_typ_id is null and nvl(ctu.pl_id,-1) = nvl(pep.pl_id,-1))
1135 )
1136 and (p_ler_id is null or
1137 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
1138 and (p_pl_typ_id is null or
1139 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
1140 /* Now join in enrollment period */
1141 and (p_asnd_lf_evt_dt is null or
1142 ctu.enrt_perd_id is null or
1143 exists (
1144 select null
1145 from ben_enrt_perd enp_c
1146 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
1147 enp_c.business_group_id=ctu.business_group_id and
1148 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
1149 )
1150 )
1151 -- if pl_typ is in usages , validte the pl against pl_type
1152 and (ctu.pl_typ_id is null or
1153 exists
1154 ( select 'x'
1155 from ben_pl_f pl
1156 where pl.pl_id = pep.pl_id
1157 and pl.pl_typ_id = ctu.pl_typ_id
1158 and cv_effective_date between
1159 pl.effective_start_date
1160 and pl.effective_end_date
1161
1162 )
1163 )
1164 /* Use nvl here as only pgm pl can be populated */
1165 and ben_generate_communications.g_comm_start_date
1166 between ctu.effective_start_date
1167 and ctu.effective_end_date
1168 and ctu.cm_typ_id = p_cm_typ_id
1169 and ctu.all_r_any_cd = 'ALL'
1170 /* Final test make sure eligible as of today */
1171 --
1172 -- Bugs : 1412882, part of bug 1412951
1173 --
1174 -- and (pep.effective_start_date = p_effective_date or
1175 -- pep.effective_start_date = p_lf_evt_ocrd_dt)
1176 -- and pep.effective_start_date = p_effective_date
1177 and pep.elig_flag = p_eligible_flag
1178 and pil.per_in_ler_id(+) = pep.per_in_ler_id
1179 and nvl(pil.business_group_id,p_business_group_id) =
1180 p_business_group_id
1181 and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT')
1182 ---look for previous eligble
1183 and (( p_eligible_flag = 'Y'
1184 and not exists ( SELECT 'x'
1185 FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1186 WHERE pep2.person_id = pep.person_id
1187 AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1188 AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1189 --- pep ler id is not updated so pil ler id is validated # 2784972
1190 /*Bug 9454579 : Uncommented the below condition*/
1191 AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
1192 AND (ctu.pl_typ_id is null or
1193 (exists
1194 ( select 'x'
1195 from ben_pl_f pl
1196 where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
1197 and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
1198 and cv_effective_date between
1199 pl.effective_start_date
1200 and pl.effective_end_date
1201 )
1202 ) )
1203 AND pep2.business_group_id = pep.business_group_id
1204 AND pep2.elig_flag = 'Y'
1205 AND pep.effective_start_date-1 -- Bug 8809596 : modified cond to pep.effective_start_date-1
1206 BETWEEN pep2.effective_start_date AND pep2.effective_end_date
1207 AND pil2.per_in_ler_id = pep2.per_in_ler_id
1208 AND pep2.per_in_ler_id = l_prev_pil_id /* bug 11654610 */
1209 /*Commented below condition for Bug 8809596*/
1210 --and pil2.per_in_ler_id <> l_per_in_ler_id -- Bug 8227214
1211 AND pil2.business_group_id = pep2.business_group_id
1212 AND ( pil2.per_in_ler_stat_cd NOT IN
1213 ( 'VOIDD', 'BCKDT')
1214 OR pil2.per_in_ler_stat_cd IS NULL) --
1215 )
1216 )
1217 ---Bug 11654610: For looking for ineligble, check for whether Participant is eligible
1218 --for previous life event and not eligible for the current LE. Added both exists clause for the bug fix
1219 OR
1220 ( p_eligible_flag = 'N'
1221 and exists ( SELECT 'x'
1222 FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1223 WHERE pep2.person_id = pep.person_id
1224 AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1225 AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1226 -- AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
1227 AND (ctu.pl_typ_id is null or
1228 (exists
1229 ( select 'x'
1230 from ben_pl_f pl
1231 where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
1232 and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
1233 and cv_effective_date between
1234 pl.effective_start_date
1235 and pl.effective_end_date
1236 )
1237 ) )
1238 AND pep2.business_group_id = pep.business_group_id
1239 AND pep2.elig_flag = 'Y'
1240 AND pep.effective_start_date-1
1241 BETWEEN pep2.effective_start_date AND pep2.effective_end_date
1242 AND pil2.per_in_ler_id (+) = pep2.per_in_ler_id
1243 AND pep2.per_in_ler_id = l_prev_pil_id
1244 AND pil2.business_group_id (+) = pep2.business_group_id
1245 AND ( pil2.per_in_ler_stat_cd NOT IN
1246 ( 'VOIDD', 'BCKDT')
1247 OR pil2.per_in_ler_stat_cd IS NULL) --
1248 )
1249 and exists ( SELECT 'x'
1250 FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1251 WHERE pep2.person_id = pep.person_id
1252 AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1253 AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1254 AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
1255 AND (ctu.pl_typ_id is null or
1256 (exists
1257 ( select 'x'
1258 from ben_pl_f pl
1259 where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
1260 and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
1261 and cv_effective_date between
1262 pl.effective_start_date
1263 and pl.effective_end_date
1264 )
1265 ) )
1266 AND pep2.business_group_id = pep.business_group_id
1267 AND pep2.elig_flag = 'N'
1268 AND pep.effective_start_date
1269 BETWEEN pep2.effective_start_date AND pep2.effective_end_date
1270 AND pil2.per_in_ler_id (+) = pep2.per_in_ler_id
1271 AND pep2.per_in_ler_id = l_per_in_ler_id
1272 AND pil2.business_group_id (+) = pep2.business_group_id
1273 AND ( pil2.per_in_ler_stat_cd NOT IN
1274 ( 'VOIDD', 'BCKDT')
1275 OR pil2.per_in_ler_stat_cd IS NULL) --
1276 )
1277 )
1278 ) ;
1279
1280 --
1281 cursor c2(cv_effective_date date, cv_lf_evt_ocrd_dt date) is
1282 select null
1283 from ben_elig_per_f pep,
1284 ben_per_in_ler pil
1285 where pep.business_group_id = p_business_group_id
1286 and pep.person_id = p_person_id
1287 and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)
1288 between pep.effective_start_date and pep.effective_end_date
1289 /* Final test make sure eligible as of today */
1290 --
1291 -- Bugs : 1412882, part of bug 1412951
1292 --
1293 and (pep.effective_start_date = cv_effective_date or
1294 pep.effective_start_date = cv_lf_evt_ocrd_dt or
1295 pep.effective_start_date = get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id))
1296 -- and pep.effective_start_date = p_effective_date
1297 and pep.elig_flag = p_eligible_flag
1298 and pil.per_in_ler_id(+) = pep.per_in_ler_id
1299 and nvl(pil.business_group_id,p_business_group_id) =
1300 p_business_group_id
1301 and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT');
1302 --
1303 -- Cursor fetch definition
1304 --
1305 l_c1 c1%rowtype;
1306 --
1307
1308 --- To make sure the person is not elibile as dpnt
1309 -- a dpnt elible for cobra whn prtt terminated and
1310 -- the same dpnt aged out and became eligble for cobra as prtt
1311 --- in the case the first time elibility to be validated in ben_dpnt_elig
1312 cursor c3 (c_pgm_id number,
1313 c_pl_id number,
1314 c_ler_id number,
1315 c_pl_typ_id number,
1316 cv_effective_date date) is
1317 select 'x'
1318 FROM ben_elig_per_f pep,
1319 ben_per_in_ler pil,
1320 ben_elig_dpnt egd
1321 WHERE egd.dpnt_person_id = p_person_id
1322 and egd.business_group_id = p_business_group_id
1323 and egd.elig_per_id = pep.elig_per_id
1324 and (c_pl_id is null or nvl(c_pl_id,-1) = nvl(pep.pl_id,-1) )
1325 and (c_pgm_id is null or nvl(c_pgm_id,-1) = nvl(pep.pgm_id,-1) )
1326 and (c_ler_id is null or nvl(c_ler_id,-1) = nvl(pep.ler_id,-1) )
1327 and (c_pl_typ_id is null or
1328 (exists
1329 ( select 'x'
1330 from ben_pl_f pl
1331 where pl.pl_id = pep.pl_id
1332 and pl.pl_typ_id = c_pl_typ_id
1333 and cv_effective_date between
1334 pl.effective_start_date
1335 and pl.effective_end_date
1336 )
1337 ) )
1338 and pep.business_group_id = pep.business_group_id
1339 and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date) -1
1340 BETWEEN pep.effective_start_date AND pep.effective_end_date
1341 AND pil.per_in_ler_id (+) = pep.per_in_ler_id
1342 and pil.business_group_id (+) = pep.business_group_id
1343 and ( pil.per_in_ler_stat_cd NOT IN
1344 ( 'VOIDD', 'BCKDT')
1345 OR pil.per_in_ler_stat_cd IS NULL) ;
1346
1347
1348 -- Local variables
1349 --
1350 l_usages_created boolean := false;
1351 l_created boolean := false;
1352 l_dummy varchar2(1);
1353 l_ass_rec per_all_assignments_f%rowtype;
1354 l_f_elg_nelg varchar2(1) ;
1355 --
1356 --
1357 -- Output variables
1358 --
1359 l_per_cm_usg_id number;
1360 --
1361
1362 begin
1363 --
1364 hr_utility.set_location('Entering: '||l_proc,10);
1365 --
1366 ben_person_object.get_object(p_person_id => p_person_id,
1367 p_rec => l_ass_rec);
1368 --
1369 -- FONM
1370 -- Based on fonm mode reset the date.
1371 --
1372 open c_get_per_in_ler(p_lf_evt_ocrd_dt);
1373 fetch c_get_per_in_ler into l_per_in_ler_id;
1374 close c_get_per_in_ler;
1375 hr_utility.set_location('Coomu per_in_ler_id : '||l_per_in_ler_id,10);
1376 hr_utility.set_location('Coomu le occrd dt : '||p_lf_evt_ocrd_dt,10);
1377
1378 l_effective_date_1 := least(p_effective_date,nvl(p_lf_evt_ocrd_dt,p_effective_date))-1 ;
1379 if l_ass_rec.assignment_id is null then
1380 --
1381 -- Grab the persons benefit assignment instead
1382 --
1383 ben_person_object.get_benass_object(p_person_id => p_person_id,
1384 p_rec => l_ass_rec);
1385 --
1386 end if;
1387 hr_utility.set_location(' flag ' || p_whnvr_trgrd_flag , 1999);
1388 hr_utility.set_location(' pl_typ_id ' || p_pl_typ_id , 1999);
1389 hr_utility.set_location(' p_asnd_lf_evt_dt ' || p_asnd_lf_evt_dt , 1999);
1390 hr_utility.set_location(' p_lf_evt_ocrd_dt ' || p_lf_evt_ocrd_dt , 1999);
1391 hr_utility.set_location(' p_effective_date ' || p_effective_date , 1999);
1392 hr_utility.set_location(' p_eligible_flag ' || p_eligible_flag , 1999);
1393 hr_utility.set_location(' p_person_id ' || p_person_id , 1999);
1394 hr_utility.set_location(' p_assignment_id ' || p_assignment_id , 1999);
1395 hr_utility.set_location(' p_per_cm_id ' || p_per_cm_id , 1999);
1396 hr_utility.set_location(' p_actn_typ_id ' || p_actn_typ_id , 1999);
1397 hr_utility.set_location(' p_cm_typ_id ' || p_cm_typ_id , 1999);
1398 hr_utility.set_location(' comm_start_date'||ben_generate_communications.g_comm_start_date , 1999);
1399 hr_utility.set_location(' p_ler_id ' || p_ler_id , 1999);
1400 hr_utility.set_location(' p_pgm_id ' || p_pgm_id , 1999);
1401 hr_utility.set_location(' p_pl_id ' || p_pl_id , 1999);
1402
1403 --
1404 if p_whnvr_trgrd_flag = 'N' then
1405 --
1406 -- We must determine a if a usage exists
1407 --
1408 -- Reset the based on fonm mode.
1409 --
1410 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
1411 --
1412 hr_utility.set_location('Coomu l_effective_date : '||l_effective_date,10);
1413
1414 open c_prev_pil(l_per_in_ler_id);
1415 fetch c_prev_pil into l_prev_pil_id;
1416 close c_prev_pil;
1417
1418 open c1(l_effective_date , l_effective_date_1);
1419 --
1420 loop
1421 --
1422 fetch c1 into l_c1;
1423 exit when c1%notfound;
1424 --
1425 hr_utility.set_location('Coomu cvg_date : '||l_c1.cvg_dt,10);
1426 hr_utility.set_location('Coomu cvg_date : '||l_c1.per_in_ler_id,10);
1427 hr_utility.set_location('Coomu cvg_date : '||l_c1.elig_per_id,10);
1428 hr_utility.set_location('Coomu cvg_date : '||l_c1.ler_id,10);
1429 hr_utility.set_location('Coomu cvg_date : '||l_c1.pl_typ_id,10);
1430 -- make sure he is eligible /noneligible as dpnt for the same object
1431 -- # 2754970
1432 l_f_elg_nelg := 'Y';
1433
1434 open c3 (l_c1.pgm_id ,
1435 l_c1.pl_id ,
1436 l_c1.ler_id ,
1437 l_c1.pl_typ_id,
1438 l_effective_date);
1439
1440 fetch c3 into l_dummy ;
1441 if c3%found then
1442 if p_eligible_flag = 'Y' then
1443 l_f_elg_nelg := 'N' ;
1444 hr_utility.set_location('l_f_elg_nelg = ' || l_f_elg_nelg , 5676);
1445 end if ;
1446 end if ;
1447 close c3 ;
1448 ----
1449 hr_utility.set_location('l_f_elg_nelg = ' || l_f_elg_nelg , 5678);
1450
1451 if l_f_elg_nelg = 'Y' then
1452
1453 hr_utility.set_location('p_pgm_id = ' || p_pgm_id || ' ler_id = ' || p_ler_id, 5678);
1454 hr_utility.set_location('p_pgm_id = ' || l_c1.pgm_id || ' pl_id = ' || l_c1.pl_id, 5678);
1455 if rule_passes
1456 (p_rule_id => l_c1.cm_usg_rl,
1457 p_person_id => p_person_id,
1458 p_assignment_id => p_assignment_id,
1459 p_business_group_id => p_business_group_id,
1460 p_organization_id => p_organization_id,
1461 p_communication_type_id => p_cm_typ_id,
1462 p_ler_id => p_ler_id,
1463 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
1464 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
1465 p_pl_typ_id => p_pl_typ_id,
1466 p_per_cm_id => p_per_cm_id,
1467 p_effective_date => l_effective_date) then
1468 --
1469 -- create usage
1470 --
1471 pop_ben_per_cm_usg_f
1472 (p_per_cm_id => p_per_cm_id,
1473 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
1474 p_business_group_id => p_business_group_id,
1475 p_effective_date => p_effective_date,
1476 p_per_cm_usg_id => l_per_cm_usg_id,
1477 p_usage_created => l_usages_created);
1478 --
1479 if l_usages_created then
1480 --
1481 -- Set boolean so we know that we created at least one usage
1482 --
1483 l_created := true;
1484 --
1485 end if;
1486 --
1487 end if;
1488 end if ;
1489 --
1490 end loop;
1491 --
1492 close c1;
1493 --
1494 elsif p_whnvr_trgrd_flag = 'Y' then
1495 --
1496 -- We just need to check whether an eligible record exists as of todays
1497 -- date.
1498 --
1499 open c2(l_effective_date, p_lf_evt_ocrd_dt);
1500 --
1501 fetch c2 into l_dummy;
1502 if c2%found then
1503 --
1504 l_created := true;
1505 --
1506 end if;
1507 --
1508 close c2;
1509 --
1510 end if;
1511 --
1512 hr_utility.set_location('Leaving: '||l_proc,10);
1513 --
1514 p_usages_created := l_created;
1515 --
1516 end check_first_time_elig_inelig;
1517 --
1518 procedure check_automatic_enrollment
1519 (p_person_id in number,
1520 p_per_in_ler_id in number,
1521 p_business_group_id in number,
1522 p_assignment_id in number,
1523 p_organization_id in number,
1524 -- PB : 5422 :
1525 -- p_enrt_perd_id in number,
1526 p_asnd_lf_evt_dt in date,
1527 p_pgm_id in number,
1528 p_pl_id in number,
1529 p_pl_typ_id in number,
1530 p_ler_id in number,
1531 p_actn_typ_id in number,
1532 p_per_cm_id in number,
1533 p_cm_typ_id in number,
1534 p_effective_date in date,
1535 p_lf_evt_ocrd_dt in date,
1536 p_whnvr_trgrd_flag in varchar2,
1537 p_usages_created out nocopy boolean) is
1538 --
1539 l_proc varchar2(80) := g_package||'check_automatic_enrollment';
1540 l_effective_date date;
1541 --
1542 cursor c1 is
1543 select ctu.cm_typ_usg_id,
1544 ctu.cm_usg_rl,
1545 ctu.pl_id, -- Bug 1555557
1546 ctu.pgm_id,
1547 ctu.pl_typ_id
1548 from ben_cm_typ_usg_f ctu,
1549 ben_prtt_enrt_rslt_f pen
1550 where ctu.business_group_id = p_business_group_id
1551 /* First join comp objects */
1552 and pen.business_group_id = ctu.business_group_id
1553 and pen.person_id = p_person_id
1554 and pen.per_in_ler_id = p_per_in_ler_id
1555 and pen.ler_id = nvl(p_ler_id,pen.ler_id)
1556 and p_effective_date
1557 between pen.effective_start_date
1558 and pen.effective_end_date
1559 /* Use nvl here as only pgm or pl can be populated */
1560 and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
1561 and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
1562 and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
1563 /* Now join in enrollment period */
1564 and (p_asnd_lf_evt_dt is null or
1565 ctu.enrt_perd_id is null or
1566 exists (
1567 select null
1568 from ben_enrt_perd enp_c
1569 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
1570 enp_c.business_group_id=ctu.business_group_id and
1571 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
1572 )
1573 /* PB : 5422 :
1574 select null
1575 from ben_enrt_perd enp_c,
1576 ben_enrt_perd enp_m
1577 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
1578 enp_c.business_group_id=ctu.business_group_id and
1579 enp_m.enrt_perd_id=p_enrt_perd_id and
1580 enp_m.business_group_id=ctu.business_group_id and
1581 enp_m.strt_dt=enp_c.strt_dt
1582 ) */
1583 )
1584 and (p_pl_typ_id is null or
1585 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
1586 and ben_generate_communications.g_comm_start_date
1587 between ctu.effective_start_date
1588 and ctu.effective_end_date
1589 and ctu.cm_typ_id = p_cm_typ_id
1590 and ctu.all_r_any_cd = 'ALL'
1591 /* Final test make sure created in the same run
1592 Checking using per_in_ler_id and conc_request_id */
1593 and pen.request_id = fnd_global.conc_request_id
1594 and pen.enrt_mthd_cd = 'A'
1595 and pen.prtt_enrt_rslt_stat_cd is null;
1596 --
1597 cursor c2 is
1598 select null
1599 from ben_prtt_enrt_rslt_f pen
1600 where pen.business_group_id = p_business_group_id
1601 and pen.person_id = p_person_id
1602 and pen.per_in_ler_id = p_per_in_ler_id
1603 and p_effective_date
1604 between pen.effective_start_date
1605 and pen.effective_end_date
1606 /* Final test make sure created in the same run
1607 Checking using per_in_ler_id and conc_request_id */
1608 and pen.request_id = fnd_global.conc_request_id
1609 and pen.enrt_mthd_cd = 'A'
1610 and pen.prtt_enrt_rslt_stat_cd is null;
1611 --
1612 -- Cursor fetch definition
1613 --
1614 l_c1 c1%rowtype;
1615 --
1616 -- Local variables
1617 --
1618 l_usages_created boolean := false;
1619 l_created boolean := false;
1620 l_dummy varchar2(1);
1621 --
1622 -- Output variables
1623 --
1624 l_per_cm_usg_id number;
1625 --
1626 begin
1627 --
1628 hr_utility.set_location('Entering: '||l_proc,10);
1629 --
1630 if p_whnvr_trgrd_flag = 'N' then
1631 --
1632 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
1633 --
1634 open c1;
1635 --
1636 loop
1637 --
1638 fetch c1 into l_c1;
1639 exit when c1%notfound;
1640 --
1641 if rule_passes
1642 (p_rule_id => l_c1.cm_usg_rl,
1643 p_person_id => p_person_id,
1644 p_assignment_id => p_assignment_id,
1645 p_business_group_id => p_business_group_id,
1646 p_organization_id => p_organization_id,
1647 p_communication_type_id => p_cm_typ_id,
1648 p_ler_id => p_ler_id,
1649 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
1650 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
1651 p_pl_typ_id => p_pl_typ_id,
1652 p_per_cm_id => p_per_cm_id,
1653 p_effective_date => l_effective_date) then
1654 --
1655 -- create usage
1656 --
1657 pop_ben_per_cm_usg_f
1658 (p_per_cm_id => p_per_cm_id,
1659 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
1660 p_business_group_id => p_business_group_id,
1661 p_effective_date => p_effective_date,
1662 p_per_cm_usg_id => l_per_cm_usg_id,
1663 p_usage_created => l_usages_created);
1664 --
1665 if l_usages_created then
1666 --
1667 -- Set boolean so we know that we created at least one usage
1668 --
1669 l_created := true;
1670 --
1671 end if;
1672 --
1673 end if;
1674 --
1675 end loop;
1676 --
1677 close c1;
1678 --
1679 else
1680 --
1681 open c2;
1682 --
1683 fetch c2 into l_dummy;
1684 if c2%found then
1685 --
1686 l_created := true;
1687 --
1688 end if;
1689 --
1690 close c2;
1691 --
1692 end if;
1693 --
1694 hr_utility.set_location('Leaving: '||l_proc,10);
1695 --
1696 p_usages_created := l_created;
1697 --
1698 end check_automatic_enrollment;
1699 --
1700 procedure check_electable_choice_popl
1701 (p_per_in_ler_id in number
1702 ,p_person_id in number
1703 ,p_business_group_id in number
1704 ,p_assignment_id in number
1705 ,p_organization_id in number
1706 ,p_asnd_lf_evt_dt in date
1707 ,p_pgm_id in number
1708 ,p_pl_id in number
1709 ,p_pl_typ_id in number
1710 ,p_ler_id in number
1711 ,p_actn_typ_id in number
1712 ,p_per_cm_id in number
1713 ,p_cm_typ_id in number
1714 ,p_effective_date in date
1715 ,p_lf_evt_ocrd_dt in date
1716 ,p_whnvr_trgrd_flag in varchar2
1717 ,p_usages_created out nocopy boolean
1718 )
1719 is
1720 --
1721 l_proc varchar2(80) := g_package||'check_electable_choice_popl';
1722 --
1723 l_ctu_cm_typ_usg_id_va benutils.g_number_table := benutils.g_number_table();
1724 l_ctu_cm_usg_rl_va benutils.g_number_table := benutils.g_number_table();
1725 l_ctu_pl_id_va benutils.g_number_table := benutils.g_number_table();
1726 l_ctu_pgm_id_va benutils.g_number_table := benutils.g_number_table();
1727 l_ctu_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
1728 --
1729 l_effective_date date;
1730 -- bug 5465081 : re-worked the c1 sql for performance
1731 CURSOR c1 (
1732 c_pil_id NUMBER,
1733 c_bgp_id NUMBER,
1734 c_pl_typ_id NUMBER,
1735 c_asnd_lf_evt_dt DATE,
1736 c_comm_st_date DATE,
1737 c_cm_typ_id NUMBER
1738 )
1739 IS
1740 SELECT ctu.cm_typ_usg_id, ctu.cm_usg_rl, ctu.pl_id, ctu.pgm_id,
1741 ctu.pl_typ_id
1742 FROM ben_cm_typ_usg_f ctu, ben_per_in_ler pil
1743 WHERE ctu.business_group_id = c_bgp_id
1744 AND ctu.cm_typ_id = c_cm_typ_id
1745 AND ctu.all_r_any_cd = 'ALL'
1746 AND ( c_asnd_lf_evt_dt IS NULL
1747 OR ctu.enrt_perd_id IS NULL
1748 OR EXISTS (
1749 SELECT NULL
1750 FROM ben_enrt_perd enp_c
1751 WHERE enp_c.enrt_perd_id = ctu.enrt_perd_id
1752 AND enp_c.business_group_id = ctu.business_group_id
1753 AND enp_c.asnd_lf_evt_dt = c_asnd_lf_evt_dt)
1754 )
1755 AND c_comm_st_date BETWEEN ctu.effective_start_date
1756 AND ctu.effective_end_date
1757 AND ( c_pl_typ_id IS NULL
1758 OR NVL (ctu.pl_typ_id, c_pl_typ_id) = c_pl_typ_id
1759 )
1760 AND pil.per_in_ler_id = c_pil_id
1761 AND ctu.business_group_id = pil.business_group_id
1762 AND NVL (ctu.ler_id, pil.ler_id) = pil.ler_id
1763 AND EXISTS (
1764 SELECT NULL
1765 FROM ben_elig_per_elctbl_chc epe
1766 WHERE epe.per_in_ler_id = pil.per_in_ler_id
1767 AND epe.business_group_id = pil.business_group_id
1768 AND epe.elctbl_flag = 'Y'
1769 AND NVL (ctu.pgm_id, NVL (epe.pgm_id, -1)) =
1770 NVL (epe.pgm_id,
1771 -1)
1772 AND NVL (ctu.pl_id, NVL (epe.pl_id, -1)) =
1773 NVL (epe.pl_id,
1774 -1)
1775 AND NVL (ctu.pl_typ_id, NVL (epe.pl_typ_id, -1)) =
1776 NVL (epe.pl_typ_id,
1777 -1)
1778 AND ROWNUM = 1);
1779 /* bug 5465081 : re-worked the sql for performance
1780 select ctu.cm_typ_usg_id,
1781 ctu.cm_usg_rl,
1782 ctu.pl_id,
1783 ctu.pgm_id,
1784 ctu.pl_typ_id
1785 from ben_cm_typ_usg_f ctu,
1786 ben_per_in_ler pil,
1787 ben_elig_per_elctbl_chc epe
1788 where pil.per_in_ler_id = c_pil_id
1789 and pil.business_group_id = c_bgp_id
1790 and ctu.business_group_id = pil.business_group_id
1791
1792 and epe.per_in_ler_id = pil.per_in_ler_id
1793 and epe.elctbl_flag = 'Y'
1794 and nvl(ctu.ler_id,pil.ler_id) = pil.ler_id
1795 and nvl(ctu.pgm_id,nvl(epe.pgm_id,-1)) = nvl(epe.pgm_id,-1)
1796 and nvl(ctu.pl_id,nvl(epe.pl_id,-1)) = nvl(epe.pl_id,-1)
1797 and (c_pl_typ_id is null or
1798 nvl(ctu.pl_typ_id,c_pl_typ_id) = c_pl_typ_id)
1799
1800 and (c_asnd_lf_evt_dt is null or
1801 ctu.enrt_perd_id is null or
1802 exists (
1803 select null
1804 from ben_enrt_perd enp_c
1805 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
1806 enp_c.business_group_id=ctu.business_group_id and
1807 enp_c.asnd_lf_evt_dt = c_asnd_lf_evt_dt
1808 )
1809 )
1810 and c_comm_st_date
1811 between ctu.effective_start_date and ctu.effective_end_date
1812 and ctu.cm_typ_id = c_cm_typ_id
1813 and ctu.all_r_any_cd = 'ALL'; */
1814 --
1815 cursor c2 is
1816 select null
1817 from ben_elig_per_elctbl_chc epe,
1818 ben_per_in_ler pil
1819 where pil.per_in_ler_id = p_per_in_ler_id
1820 and pil.business_group_id = p_business_group_id
1821 and epe.per_in_ler_id = pil.per_in_ler_id
1822 and epe.elctbl_flag = 'Y';
1823 --
1824 -- Cursor fetch definition
1825 --
1826 l_c1 c1%rowtype;
1827 --
1828 -- Local variables
1829 --
1830 l_usages_created boolean := false;
1831 l_created boolean := false;
1832 l_dummy varchar2(1);
1833 --
1834 -- Output variables
1835 --
1836 l_per_cm_usg_id number;
1837 --
1838 begin
1839 --
1840 hr_utility.set_location('Entering: '||l_proc,10);
1841 --
1842 if p_whnvr_trgrd_flag = 'N' then
1843 --
1844 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
1845 --
1846 open c1
1847 (c_pil_id => p_per_in_ler_id
1848 ,c_bgp_id => p_business_group_id
1849 ,c_pl_typ_id => p_pl_typ_id
1850 ,c_asnd_lf_evt_dt => p_asnd_lf_evt_dt
1851 ,c_comm_st_date => ben_generate_communications.g_comm_start_date
1852 ,c_cm_typ_id => p_cm_typ_id
1853 );
1854 fetch c1 BULK COLLECT INTO l_ctu_cm_typ_usg_id_va,
1855 l_ctu_cm_usg_rl_va,
1856 l_ctu_pl_id_va,
1857 l_ctu_pgm_id_va,
1858 l_ctu_pl_typ_id_va;
1859 close c1;
1860 --
1861 if l_ctu_cm_typ_usg_id_va.count > 0
1862 then
1863 --
1864 for ctuva in l_ctu_cm_typ_usg_id_va.first..l_ctu_cm_typ_usg_id_va.last
1865 loop
1866 --
1867 if rule_passes
1868 (p_rule_id => l_ctu_cm_usg_rl_va(ctuva)
1869 ,p_person_id => p_person_id
1870 ,p_assignment_id => p_assignment_id
1871 ,p_business_group_id => p_business_group_id
1872 ,p_organization_id => p_organization_id
1873 ,p_communication_type_id => p_cm_typ_id
1874 ,p_ler_id => p_ler_id
1875 ,p_pgm_id => nvl(p_pgm_id, l_ctu_pgm_id_va(ctuva))
1876 ,p_pl_id => nvl(p_pl_id, l_ctu_pl_id_va(ctuva))
1877 ,p_pl_typ_id => p_pl_typ_id
1878 ,p_per_cm_id => p_per_cm_id
1879 ,p_effective_date => l_effective_date
1880 )
1881 then
1882 --
1883 -- create usage
1884 --
1885 pop_ben_per_cm_usg_f
1886 (p_per_cm_id => p_per_cm_id
1887 ,p_cm_typ_usg_id => l_ctu_cm_typ_usg_id_va(ctuva)
1888 ,p_business_group_id => p_business_group_id
1889 ,p_effective_date => p_effective_date
1890 ,p_per_cm_usg_id => l_per_cm_usg_id
1891 ,p_usage_created => l_usages_created
1892 );
1893 --
1894 if l_usages_created then
1895 --
1896 -- Set boolean so we know that we created at least one usage
1897 --
1898 l_created := true;
1899 --
1900 end if;
1901 --
1902 end if;
1903 --
1904 end loop;
1905 --
1906 end if;
1907 --
1908 /*
1909 open c1;
1910 --
1911 loop
1912 --
1913 fetch c1 into l_c1;
1914 exit when c1%notfound;
1915 --
1916 if rule_passes
1917 (p_rule_id => l_c1.cm_usg_rl,
1918 p_person_id => p_person_id,
1919 p_assignment_id => p_assignment_id,
1920 p_business_group_id => p_business_group_id,
1921 p_organization_id => p_organization_id,
1922 p_communication_type_id => p_cm_typ_id,
1923 p_ler_id => p_ler_id,
1924 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
1925 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
1926 p_pl_typ_id => p_pl_typ_id,
1927 p_per_cm_id => p_per_cm_id,
1928 p_effective_date => l_effective_date) then
1929 --
1930 -- create usage
1931 --
1932 pop_ben_per_cm_usg_f
1933 (p_per_cm_id => p_per_cm_id,
1934 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
1935 p_business_group_id => p_business_group_id,
1936 p_effective_date => p_effective_date,
1937 p_per_cm_usg_id => l_per_cm_usg_id,
1938 p_usage_created => l_usages_created);
1939 --
1940 if l_usages_created then
1941 --
1942 -- Set boolean so we know that we created at least one usage
1943 --
1944 l_created := true;
1945 --
1946 end if;
1947 --
1948 end if;
1949 --
1950 end loop;
1951 --
1952 close c1;
1953 */
1954 --
1955 else
1956 --
1957 -- We just need to check whether an eligible record exists as of todays
1958 -- date.
1959 --
1960 open c2;
1961 --
1962 fetch c2 into l_dummy;
1963 if c2%found then
1964 --
1965 l_created := true;
1966 --
1967 end if;
1968 --
1969 close c2;
1970 --
1971 end if;
1972 --
1973 hr_utility.set_location('Leaving: '||l_proc,10);
1974 --
1975 p_usages_created := l_created;
1976 --
1977 end check_electable_choice_popl;
1978 --
1979 procedure check_no_impact_on_benefits
1980 (p_per_in_ler_id in number,
1981 p_person_id in number,
1982 p_business_group_id in number,
1983 p_assignment_id in number,
1984 p_organization_id in number,
1985 p_pgm_id in number,
1986 p_pl_id in number,
1987 p_pl_typ_id in number,
1988 p_ler_id in number,
1989 p_per_cm_id in number,
1990 p_cm_typ_id in number,
1991 p_effective_date in date,
1992 p_lf_evt_ocrd_dt in date,
1993 p_whnvr_trgrd_flag in varchar2,
1994 p_usages_created out nocopy boolean) is
1995 --
1996 l_proc varchar2(80) := g_package||'check_no_impact_on_benefits';
1997 l_effective_date date;
1998 --
1999 -- This check only really needs the ler_id as context
2000 --
2001 cursor c1 is
2002 select ctu.cm_typ_usg_id,
2003 ctu.cm_usg_rl
2004 from ben_cm_typ_usg_f ctu
2005 where ctu.business_group_id = p_business_group_id
2006 and ctu.cm_typ_id = p_cm_typ_id
2007 and ctu.ler_id = p_ler_id
2008 and ctu.pgm_id is null
2009 and ctu.pl_id is null
2010 and ctu.pl_typ_id is null
2011 and ctu.enrt_perd_id is null
2012 and ctu.actn_typ_id is null
2013 and not exists (select null
2014 from ben_elig_per_elctbl_chc epe
2015 where epe.business_group_id = p_business_group_id
2016 and epe.elctbl_flag = 'Y'
2017 and epe.per_in_ler_id = p_per_in_ler_id)
2018 and ben_generate_communications.g_comm_start_date
2019 between ctu.effective_start_date
2020 and ctu.effective_end_date
2021 /* This process code can only apply to ALL since one context only
2022 can be set, we code for both though just in case */
2023 and ctu.all_r_any_cd in ('ALL','ANY');
2024 --
2025 cursor c2 is
2026 select null
2027 from sys.dual
2028 where not exists (select null
2029 from ben_elig_per_elctbl_chc epe
2030 where epe.business_group_id = p_business_group_id
2031 and epe.elctbl_flag = 'Y'
2032 and epe.per_in_ler_id = p_per_in_ler_id);
2033 --
2034 -- Cursor fetch definition
2035 --
2036 l_c1 c1%rowtype;
2037 --
2038 -- Local variables
2039 --
2040 l_usages_created boolean := false;
2041 l_created boolean := false;
2042 l_dummy varchar2(1);
2043 --
2044 -- Output variables
2045 --
2046 l_per_cm_usg_id number;
2047 --
2048 begin
2049 --
2050 hr_utility.set_location('Entering: '||l_proc,10);
2051 --
2052 if p_whnvr_trgrd_flag = 'N' then
2053 --
2054 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2055 --
2056 open c1;
2057 --
2058 loop
2059 --
2060 fetch c1 into l_c1;
2061 exit when c1%notfound;
2062 --
2063 if rule_passes
2064 (p_rule_id => l_c1.cm_usg_rl,
2065 p_person_id => p_person_id,
2066 p_assignment_id => p_assignment_id,
2067 p_business_group_id => p_business_group_id,
2068 p_organization_id => p_organization_id,
2069 p_communication_type_id => p_cm_typ_id,
2070 p_ler_id => p_ler_id,
2071 p_pgm_id => p_pgm_id,
2072 p_pl_id => p_pl_id,
2073 p_pl_typ_id => p_pl_typ_id,
2074 p_per_cm_id => p_per_cm_id,
2075 p_effective_date => l_effective_date) then
2076 --
2077 -- create usage
2078 --
2079 pop_ben_per_cm_usg_f
2080 (p_per_cm_id => p_per_cm_id,
2081 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2082 p_business_group_id => p_business_group_id,
2083 p_effective_date => p_effective_date,
2084 p_per_cm_usg_id => l_per_cm_usg_id,
2085 p_usage_created => l_usages_created);
2086 --
2087 if l_usages_created then
2088 --
2089 -- Set boolean so we know that we created at least one usage
2090 --
2091 l_created := true;
2092 --
2093 end if;
2094 --
2095 end if;
2096 --
2097 end loop;
2098 --
2099 close c1;
2100 --
2101 else
2102 --
2103 open c2;
2104 --
2105 fetch c2 into l_dummy;
2106 if c2%found then
2107 --
2108 l_created := true;
2109 --
2110 end if;
2111 --
2112 close c2;
2113 --
2114 end if;
2115 --
2116 hr_utility.set_location('Leaving: '||l_proc,10);
2117 --
2118 p_usages_created := l_created;
2119 --
2120 end check_no_impact_on_benefits;
2121 --
2122 procedure check_inelig_deenroll
2123 (p_per_in_ler_id in number,
2124 p_person_id in number,
2125 p_business_group_id in number,
2126 p_assignment_id in number,
2127 p_organization_id in number,
2128 p_pgm_id in number,
2129 p_pl_id in number,
2130 p_pl_typ_id in number,
2131 -- PB : 5422 :
2132 p_asnd_lf_evt_dt in date,
2133 -- p_enrt_perd_id in number,
2134 p_ler_id in number,
2135 p_actn_typ_id in number,
2136 p_per_cm_id in number,
2137 p_cm_typ_id in number,
2138 p_effective_date in date,
2139 p_lf_evt_ocrd_dt in date,
2140 p_whnvr_trgrd_flag in varchar2,
2141 p_usages_created out nocopy boolean) is
2142 --
2143 l_proc varchar2(80) := g_package||'check_inelig_deenroll';
2144 l_effective_date date;
2145 --
2146 cursor c1 is
2147 select ctu.cm_typ_usg_id,
2148 ctu.cm_usg_rl,
2149 ctu.pl_id, -- Bug 1555557
2150 ctu.pgm_id,
2151 ctu.pl_typ_id
2152 from ben_cm_typ_usg_f ctu,
2153 ben_prtt_enrt_rslt_f pen,
2154 ben_per_in_ler pil
2155 where ctu.business_group_id = p_business_group_id
2156 /* First join comp objects */
2157 and pen.business_group_id = ctu.business_group_id
2158 and pen.per_in_ler_id = p_per_in_ler_id
2159 and pil.per_in_ler_id = pen.per_in_ler_id -- 5926672 new pil join
2160 and pen.ler_id = nvl(p_ler_id,pen.ler_id)
2161 and ( (p_effective_date
2162 between pen.effective_start_date -- 5926672 or condition As Enrollment window might have shifted to future dates
2163 and pen.effective_end_date)
2164 or pil.LF_EVT_OCRD_DT = p_effective_date
2165 or pil.STRTD_DT = p_effective_date
2166 )
2167 /* Use nvl here as only pgm or pl can be populated */
2168 and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
2169 and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
2170 and nvl(ctu.pl_id, pen.pl_id) = pen.pl_id
2171 and nvl(ctu.pl_typ_id,pen.pl_typ_id) = pen.pl_typ_id
2172 /* Now join in enrollment period */
2173 and (p_asnd_lf_evt_dt is null or
2174 ctu.enrt_perd_id is null or
2175 exists (
2176 select null
2177 from ben_enrt_perd enp_c
2178 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
2179 enp_c.business_group_id=ctu.business_group_id and
2180 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
2181 )
2182 /* PB : 5422 :
2183 select null
2184 from ben_enrt_perd enp_c,
2185 ben_enrt_perd enp_m
2186 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
2187 enp_c.business_group_id=ctu.business_group_id and
2188 enp_m.enrt_perd_id=p_enrt_perd_id and
2189 enp_m.business_group_id=ctu.business_group_id and
2190 enp_m.strt_dt=enp_c.strt_dt
2191 ) */
2192 )
2193 and ben_generate_communications.g_comm_start_date
2194 between ctu.effective_start_date
2195 and ctu.effective_end_date
2196 and ctu.cm_typ_id = p_cm_typ_id
2197 and ctu.all_r_any_cd = 'ALL'
2198 and pen.enrt_cvg_thru_dt < hr_api.g_eot
2199 and pen.prtt_enrt_rslt_stat_cd is null;
2200 --
2201 cursor c2 is
2202 select null
2203 from ben_prtt_enrt_rslt_f pen,
2204 ben_per_in_ler pil
2205 where pen.business_group_id = p_business_group_id
2206 and pen.per_in_ler_id = p_per_in_ler_id
2207 and pen.per_in_ler_id = pil.per_in_ler_id -- 5926672 join to pil
2208 and pen.enrt_cvg_thru_dt < hr_api.g_eot
2209 and (
2210 (l_effective_date
2211 between pen.effective_start_date -- 5926672 chnged to pil
2212 and pen.effective_end_date
2213 )
2214 or pil.LF_EVT_OCRD_DT = l_effective_date
2215 or pil.STRTD_DT = l_effective_date
2216 )
2217 and pen.prtt_enrt_rslt_stat_cd is null;
2218 --
2219 -- Cursor fetch definition
2220 --
2221 l_c1 c1%rowtype;
2222 --
2223 -- Local variables
2224 --
2225 l_usages_created boolean := false;
2226 l_created boolean := false;
2227 l_dummy varchar2(1);
2228 --
2229 -- Output variables
2230 --
2231 l_per_cm_usg_id number;
2232 --
2233 begin
2234 --
2235 hr_utility.set_location('Entering: '||l_proc,10);
2236 hr_utility.set_location('p_per_in_ler_id: '||p_per_in_ler_id,10);
2237 --
2238 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2239 hr_utility.set_location('l_effective_date: '||l_effective_date,10);
2240 --
2241 if p_whnvr_trgrd_flag = 'N' then
2242 --
2243 open c1;
2244 --
2245 loop
2246 --
2247 fetch c1 into l_c1;
2248 exit when c1%notfound;
2249 --
2250 if rule_passes
2251 (p_rule_id => l_c1.cm_usg_rl,
2252 p_person_id => p_person_id,
2253 p_assignment_id => p_assignment_id,
2254 p_business_group_id => p_business_group_id,
2255 p_organization_id => p_organization_id,
2256 p_communication_type_id => p_cm_typ_id,
2257 p_ler_id => p_ler_id,
2258 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
2259 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
2260 p_pl_typ_id => p_pl_typ_id,
2261 p_per_cm_id => p_per_cm_id,
2262 p_effective_date => l_effective_date) then
2263 --
2264 -- create usage
2265 --
2266 pop_ben_per_cm_usg_f
2267 (p_per_cm_id => p_per_cm_id,
2268 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2269 p_business_group_id => p_business_group_id,
2270 p_effective_date => p_effective_date,
2271 p_per_cm_usg_id => l_per_cm_usg_id,
2272 p_usage_created => l_usages_created);
2273 --
2274 if l_usages_created then
2275 --
2276 -- Set boolean so we know that we created at least one usage
2277 --
2278 l_created := true;
2279 --
2280 end if;
2281 --
2282 end if;
2283 --
2284 end loop;
2285 --
2286 close c1;
2287 --
2288 else
2289 --
2290 open c2;
2291 --
2292 fetch c2 into l_dummy;
2293 if c2%found then
2294 --
2295 l_created := true;
2296 --
2297 end if;
2298 --
2299 close c2;
2300 --
2301 end if;
2302 --
2303 hr_utility.set_location('Leaving: '||l_proc,10);
2304 --
2305 p_usages_created := l_created;
2306 --
2307 end check_inelig_deenroll;
2308 --
2309 procedure check_expl_dflt_enrollment
2310 (p_per_in_ler_id in number,
2311 p_person_id in number,
2312 p_business_group_id in number,
2313 p_assignment_id in number,
2314 p_organization_id in number,
2315 p_pgm_id in number,
2316 p_pl_id in number,
2317 p_pl_typ_id in number,
2318 -- PB : 5422 :
2319 p_asnd_lf_evt_dt in date,
2320 -- p_enrt_perd_id in number,
2321 p_ler_id in number,
2322 p_per_cm_id in number,
2323 p_cm_typ_id in number,
2324 p_enrt_mthd_cd in varchar2,
2325 p_effective_date in date,
2326 p_lf_evt_ocrd_dt in date,
2327 p_whnvr_trgrd_flag in varchar2,
2328 p_usages_created out nocopy boolean) is
2329 --
2330 l_proc varchar2(80) := g_package||'check_expl_dflt_enrollment';
2331 l_effective_date date;
2332 --
2333 cursor c1 is
2334 select ctu.cm_typ_usg_id,
2335 ctu.cm_usg_rl,
2336 ctu.pl_id, -- Bug 1555557
2337 ctu.pgm_id,
2338 ctu.pl_typ_id
2339 from ben_cm_typ_usg_f ctu,
2340 ben_prtt_enrt_rslt_f pen,
2341 ben_pil_elctbl_chc_popl pel
2342 where ctu.business_group_id = p_business_group_id
2343 /* First join comp objects */
2344 and pen.business_group_id = ctu.business_group_id
2345 and pen.per_in_ler_id = p_per_in_ler_id
2346 and pen.per_in_ler_id = pel.per_in_ler_id
2347 and pen.enrt_mthd_cd = p_enrt_mthd_cd
2348 and p_effective_date
2349 between pen.effective_start_date
2350 and pen.effective_end_date
2351 and pel.business_group_id = pen.business_group_id
2352 and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
2353 and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
2354 and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
2355 -- validate the incomming parameter to make sure comm triiger for right plan
2356 and ( ctu.pgm_id is null or p_pgm_id is null or p_pgm_id = ctu.pgm_id )
2357 and ( ctu.pl_id is null or p_pl_id is null or p_pl_id = ctu.pl_id )
2358 --if he pl_type_id is passed compare with pl_type_id or
2359 -- compare with pen.pl_type_id
2360 --and (p_pl_typ_id is null or
2361 -- nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2362 and ( (p_pl_typ_id is null and
2363 nvl(ctu.pl_typ_id,pen.pl_typ_id ) = pen.pl_typ_id)
2364 or (p_pl_typ_id is not null and
2365 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2366 )
2367 /* Now join in enrollment period */
2368 and ((ctu.enrt_perd_id = pel.enrt_perd_id
2369 and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2370 and pel.pgm_id is null)
2371 or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2372 or (ctu.enrt_perd_id is null))
2373 and ben_generate_communications.g_comm_start_date
2374 between ctu.effective_start_date
2375 and ctu.effective_end_date
2376 and ctu.cm_typ_id = p_cm_typ_id
2377 and ctu.all_r_any_cd = 'ALL'
2378 and pen.prtt_enrt_rslt_stat_cd is null;
2379 --
2380 -- Cursor fetch definition
2381 --
2382 l_c1 c1%rowtype;
2383 --
2384 cursor c2 is
2385 select null
2386 from ben_elig_per_elctbl_chc epe,
2387 ben_per_in_ler pil
2388 where pil.per_in_ler_id = p_per_in_ler_id
2389 and pil.business_group_id = p_business_group_id
2390 and epe.per_in_ler_id = pil.per_in_ler_id;
2391 --
2392 -- Local variables
2393 --
2394 l_usages_created boolean := false;
2395 l_created boolean := false;
2396 l_dummy varchar2(1);
2397 --
2398 -- Output variables
2399 --
2400 l_per_cm_usg_id number;
2401 --
2402 begin
2403 --
2404 hr_utility.set_location('Entering: '||l_proc,10);
2405
2406 hr_utility.set_location('befo loop pl type_id ' || p_pl_typ_id,77);
2407 --
2408 if p_whnvr_trgrd_flag = 'N' then
2409 --
2410 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2411 --
2412 open c1;
2413 --
2414 loop
2415 --
2416 fetch c1 into l_c1;
2417 exit when c1%notfound;
2418 hr_utility.set_location('befo loop pl type_id ' || l_c1.pl_id,77);
2419 --
2420 if rule_passes
2421 (p_rule_id => l_c1.cm_usg_rl,
2422 p_person_id => p_person_id,
2423 p_assignment_id => p_assignment_id,
2424 p_business_group_id => p_business_group_id,
2425 p_organization_id => p_organization_id,
2426 p_communication_type_id => p_cm_typ_id,
2427 p_ler_id => p_ler_id,
2428 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
2429 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
2430 p_pl_typ_id => p_pl_typ_id,
2431 p_per_cm_id => p_per_cm_id,
2432 p_effective_date => l_effective_date) then
2433 --
2434 -- create usage
2435 --
2436 pop_ben_per_cm_usg_f
2437 (p_per_cm_id => p_per_cm_id,
2438 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2439 p_business_group_id => p_business_group_id,
2440 p_effective_date => p_effective_date,
2441 p_per_cm_usg_id => l_per_cm_usg_id,
2442 p_usage_created => l_usages_created);
2443 --
2444 if l_usages_created then
2445 --
2446 -- Set boolean so we know that we created at least one usage
2447 --
2448 l_created := true;
2449 --
2450 end if;
2451 --
2452 end if;
2453 --
2454 end loop;
2455 --
2456 close c1;
2457 --
2458 else
2459 --
2460 -- we need to check if the person is eligible as of the effective_date
2461 --
2462 open c2;
2463 fetch c2 into l_dummy;
2464 --
2465 if c2%found then
2466 --
2467 l_created := true;
2468 --
2469 end if;
2470 --
2471 close c2;
2472 --
2473 end if;
2474 --
2475 hr_utility.set_location('Leaving: '||l_proc,10);
2476 --
2477 p_usages_created := l_created;
2478 --
2479 end check_expl_dflt_enrollment;
2480 --
2481 procedure check_close_enrollment
2482 (p_per_in_ler_id in number,
2483 p_person_id in number,
2484 p_business_group_id in number,
2485 p_assignment_id in number,
2486 p_organization_id in number,
2487 p_ler_id in number,
2488 p_per_cm_id in number,
2489 p_cm_typ_id in number,
2490 p_effective_date in date,
2491 p_lf_evt_ocrd_dt in date,
2492 p_whnvr_trgrd_flag in varchar2,
2493 p_usages_created out nocopy boolean) is
2494 --
2495 l_proc varchar2(80) := g_package||'check_close_enrollment';
2496 l_effective_date date;
2497 --
2498 -- Cursor fetch definition
2499 --
2500 cursor c1 is
2501 select ctu.cm_typ_usg_id,
2502 ctu.cm_usg_rl,
2503 ctu.pl_id, -- Bug 1555557
2504 ctu.pgm_id,
2505 ctu.pl_typ_id
2506 from ben_cm_typ_usg_f ctu,
2507 ben_prtt_enrt_rslt_f pen,
2508 ben_pil_elctbl_chc_popl pel
2509 where ctu.business_group_id = p_business_group_id
2510 /* First join comp objects */
2511 and pen.business_group_id = ctu.business_group_id
2512 and pen.per_in_ler_id = p_per_in_ler_id
2513 and pen.per_in_ler_id = pel.per_in_ler_id
2514 and p_effective_date
2515 between pen.effective_start_date
2516 and pen.effective_end_date
2517 and pel.business_group_id = pen.business_group_id
2518 and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
2519 and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
2520 and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
2521 and nvl(ctu.pl_typ_id,pen.pl_typ_id) = pen.pl_typ_id
2522 /* Now join in enrollment period */
2523 and ((ctu.enrt_perd_id = pel.enrt_perd_id
2524 and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
2525 and pel.pgm_id is null)
2526 or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
2527 or (ctu.enrt_perd_id is null))
2528 and ben_generate_communications.g_comm_start_date
2529 between ctu.effective_start_date
2530 and ctu.effective_end_date
2531 and ctu.cm_typ_id = p_cm_typ_id
2532 and ctu.all_r_any_cd = 'ALL'
2533 and pen.prtt_enrt_rslt_stat_cd is null;
2534
2535 --
2536 l_c1 c1%rowtype;
2537 --
2538 cursor c2 is
2539 select null
2540 from ben_elig_per_elctbl_chc epe,
2541 ben_per_in_ler pil
2542 where pil.per_in_ler_id = p_per_in_ler_id
2543 and pil.business_group_id = p_business_group_id
2544 and epe.per_in_ler_id = pil.per_in_ler_id;
2545 --
2546 -- Local variables
2547 --
2548 l_usages_created boolean := false;
2549 l_created boolean := false;
2550 l_dummy varchar2(1);
2551 --
2552 -- Output variables
2553 --
2554 l_per_cm_usg_id number;
2555 --
2556 begin
2557 --
2558 hr_utility.set_location('Entering: '||l_proc,10);
2559 --
2560 if p_whnvr_trgrd_flag = 'N' then
2561 --
2562 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2563 --
2564 open c1;
2565 --
2566 loop
2567 --
2568 fetch c1 into l_c1;
2569 exit when c1%notfound;
2570 --
2571 if rule_passes
2572 (p_rule_id => l_c1.cm_usg_rl,
2573 p_person_id => p_person_id,
2574 p_assignment_id => p_assignment_id,
2575 p_business_group_id => p_business_group_id,
2576 p_organization_id => p_organization_id,
2577 p_communication_type_id => p_cm_typ_id,
2578 p_ler_id => p_ler_id,
2579 p_pgm_id => l_c1.pgm_id, -- Bug 1555557
2580 p_pl_id => l_c1.pl_id, -- Bug 1555557
2581 p_pl_typ_id => null,
2582 p_per_cm_id => p_per_cm_id,
2583 p_effective_date => l_effective_date) then
2584 --
2585 -- create usage
2586 --
2587 pop_ben_per_cm_usg_f
2588 (p_per_cm_id => p_per_cm_id,
2589 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2590 p_business_group_id => p_business_group_id,
2591 p_effective_date => p_effective_date,
2592 p_per_cm_usg_id => l_per_cm_usg_id,
2593 p_usage_created => l_usages_created);
2594 --
2595 if l_usages_created then
2596 --
2597 -- Set boolean so we know that we created at least one usage
2598 --
2599 l_created := true;
2600 --
2601 end if;
2602 --
2603 end if;
2604 --
2605 end loop;
2606 --
2607 close c1;
2608 --
2609 else
2610 --
2611 -- we need to check if the person is eligible as of the effective_date
2612 --
2613 open c2;
2614 --
2615 fetch c2 into l_dummy;
2616 --
2617 if c2%found then
2618 --
2619 l_created := true;
2620 --
2621 end if;
2622 --
2623 close c2;
2624 --
2625 end if;
2626 --
2627 hr_utility.set_location('Leaving: '||l_proc,10);
2628 --
2629 p_usages_created := l_created;
2630 --
2631 end check_close_enrollment;
2632 --
2633 procedure check_actn_item
2634 (p_per_in_ler_id in number,
2635 p_person_id in number,
2636 p_business_group_id in number,
2637 p_assignment_id in number,
2638 p_organization_id in number,
2639 p_actn_typ_id in number,
2640 p_pgm_id in number,
2641 p_pl_id in number,
2642 p_pl_typ_id in number,
2643 p_ler_id in number,
2644 p_per_cm_id in number,
2645 p_cm_typ_id in number,
2646 p_effective_date in date,
2647 p_lf_evt_ocrd_dt in date,
2648 p_whnvr_trgrd_flag in varchar2,
2649 p_usages_created out nocopy boolean) is
2650 --
2651 l_proc varchar2(80) := g_package||'check_actn_item';
2652 l_effective_date date;
2653 --
2654 cursor c1 is
2655 select ctu.cm_typ_usg_id,
2656 ctu.cm_usg_rl
2657 from ben_cm_typ_usg_f ctu
2658 where ctu.business_group_id = p_business_group_id
2659 and (p_ler_id is null or
2660 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
2661 and (p_pgm_id is null or
2662 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
2663 and (p_pl_id is null or
2664 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2665 and (p_pl_typ_id is null or
2666 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2667 and (p_actn_typ_id is null or
2668 nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
2669 and ben_generate_communications.g_comm_start_date
2670 between ctu.effective_start_date
2671 and ctu.effective_end_date
2672 and ctu.cm_typ_id = p_cm_typ_id
2673 and ctu.all_r_any_cd = 'ALL';
2674 --
2675 -- Cursor fetch definition
2676 --
2677 l_c1 c1%rowtype;
2678 --
2679 -- Local variables
2680 --
2681 l_usages_created boolean := false;
2682 l_created boolean := false;
2683 l_dummy varchar2(1);
2684 --
2685 -- Output variables
2686 --
2687 l_per_cm_usg_id number;
2688 --
2689 begin
2690 --
2691 hr_utility.set_location('Entering: '||l_proc,10);
2692 --
2693 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2694 --
2695 if p_whnvr_trgrd_flag = 'N' then
2696 --
2697 open c1;
2698 --
2699 loop
2700 --
2701 fetch c1 into l_c1;
2702 exit when c1%notfound;
2703 --
2704 if rule_passes
2705 (p_rule_id => l_c1.cm_usg_rl,
2706 p_person_id => p_person_id,
2707 p_assignment_id => p_assignment_id,
2708 p_business_group_id => p_business_group_id,
2709 p_organization_id => p_organization_id,
2710 p_communication_type_id => p_cm_typ_id,
2711 p_ler_id => p_ler_id,
2712 p_pgm_id => p_pgm_id,
2713 p_pl_id => p_pl_id,
2714 p_pl_typ_id => p_pl_typ_id,
2715 p_per_cm_id => p_per_cm_id,
2716 p_effective_date => l_effective_date) then
2717 --
2718 -- create usage
2719 --
2720 pop_ben_per_cm_usg_f
2721 (p_per_cm_id => p_per_cm_id,
2722 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2723 p_business_group_id => p_business_group_id,
2724 p_effective_date => p_effective_date,
2725 p_per_cm_usg_id => l_per_cm_usg_id,
2726 p_usage_created => l_usages_created);
2727 --
2728 if l_usages_created then
2729 --
2730 -- Set boolean so we know that we created at least one usage
2731 --
2732 l_created := true;
2733 --
2734 end if;
2735 --
2736 end if;
2737 --
2738 end loop;
2739 --
2740 close c1;
2741 --
2742 else
2743 --
2744 l_created := true;
2745 --
2746 end if;
2747 --
2748 hr_utility.set_location('Leaving: '||l_proc,10);
2749 --
2750 p_usages_created := l_created;
2751 --
2752 end check_actn_item;
2753 --
2754 procedure check_reimbursement
2755 (p_person_id in number,
2756 p_business_group_id in number,
2757 p_assignment_id in number,
2758 p_organization_id in number,
2759 p_pgm_id in number,
2760 p_pl_id in number,
2761 p_pl_typ_id in number,
2762 p_ler_id in number,
2763 p_per_cm_id in number,
2764 p_cm_typ_id in number,
2765 p_effective_date in date,
2766 p_lf_evt_ocrd_dt in date,
2767 p_whnvr_trgrd_flag in varchar2,
2768 p_usages_created out nocopy boolean) is
2769 --
2770 l_proc varchar2(80) := g_package||'check_reimbursement';
2771 l_effective_date date;
2772 --
2773 cursor c1 is
2774 select ctu.cm_typ_usg_id,
2775 ctu.cm_usg_rl
2776 from ben_cm_typ_usg_f ctu
2777 where ctu.business_group_id = p_business_group_id
2778 and (p_ler_id is null or
2779 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
2780 and (p_pgm_id is null or
2781 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
2782 and (p_pl_id is null or
2783 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2784 and (p_pl_typ_id is null or
2785 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2786 and ben_generate_communications.g_comm_start_date
2787 between ctu.effective_start_date
2788 and ctu.effective_end_date
2789 and ctu.cm_typ_id = p_cm_typ_id
2790 and ctu.all_r_any_cd = 'ALL';
2791 --
2792 l_c1 c1%rowtype;
2793 --
2794 -- Local variables
2795 --
2796 l_usages_created boolean := false;
2797 l_created boolean := false;
2798 l_dummy varchar2(1);
2799 --
2800 -- Output variables
2801 --
2802 l_per_cm_usg_id number;
2803 --
2804 begin
2805 --
2806 hr_utility.set_location('Entering: '||l_proc,10);
2807 hr_utility.set_location('BG : '|| p_business_group_id,10);
2808 hr_utility.set_location('PL: '||p_pl_id,10);
2809 hr_utility.set_location('ler : '||p_ler_id,10);
2810 hr_utility.set_location('pgm: '|| p_pgm_id,10);
2811 hr_utility.set_location('pl_typ: '||p_pl_typ_id,10);
2812 hr_utility.set_location('comm date : '||ben_generate_communications.g_comm_start_date,10);
2813 hr_utility.set_location('comm type : '||p_cm_typ_id,10);
2814
2815 --
2816 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2817 --
2818 if p_whnvr_trgrd_flag = 'N' then
2819 --
2820 open c1;
2821 --
2822 loop
2823 --
2824 fetch c1 into l_c1;
2825 exit when c1%notfound;
2826 --
2827 if rule_passes
2828 (p_rule_id => l_c1.cm_usg_rl,
2829 p_person_id => p_person_id,
2830 p_assignment_id => p_assignment_id,
2831 p_business_group_id => p_business_group_id,
2832 p_organization_id => p_organization_id,
2833 p_communication_type_id => p_cm_typ_id,
2834 p_ler_id => p_ler_id,
2835 p_pgm_id => p_pgm_id,
2836 p_pl_id => p_pl_id,
2837 p_pl_typ_id => p_pl_typ_id,
2838 p_per_cm_id => p_per_cm_id,
2839 p_effective_date => l_effective_date) then
2840 --
2841 -- create usage
2842 --
2843 pop_ben_per_cm_usg_f
2844 (p_per_cm_id => p_per_cm_id,
2845 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2846 p_business_group_id => p_business_group_id,
2847 p_effective_date => p_effective_date,
2848 p_per_cm_usg_id => l_per_cm_usg_id,
2849 p_usage_created => l_usages_created);
2850 --
2851 if l_usages_created then
2852 --
2853 -- Set boolean so we know that we created at least one usage
2854 --
2855 l_created := true;
2856 --
2857 end if;
2858 --
2859 end if;
2860 --
2861 end loop;
2862 --
2863 close c1;
2864 --
2865 else
2866 --
2867 l_created := true;
2868 --
2869 end if;
2870 --
2871 hr_utility.set_location('Leaving: '||l_proc,10);
2872 --
2873 p_usages_created := l_created;
2874 --
2875 end check_reimbursement;
2876 --
2877 procedure check_dpnt_end_enrt
2878 (p_person_id in number,
2879 p_assignment_id in number,
2880 p_business_group_id in number,
2881 p_organization_id in number,
2882 p_pgm_id in number,
2883 p_pl_id in number,
2884 p_pl_typ_id in number,
2885 p_ler_id in number,
2886 p_per_cm_id in number,
2887 p_cm_typ_id in number,
2888 p_effective_date in date,
2889 p_lf_evt_ocrd_dt in date,
2890 p_whnvr_trgrd_flag in varchar2,
2891 p_usages_created out nocopy boolean) is
2892 --
2893 l_proc varchar2(80) := g_package || '.check_dpnt_end_enrt';
2894 l_effective_date date;
2895 --
2896 cursor c1 is
2897 select ctu.cm_typ_usg_id,
2898 ctu.cm_usg_rl
2899 from ben_cm_typ_usg_f ctu
2900 where ctu.business_group_id = p_business_group_id
2901 and (p_pgm_id is null or
2902 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
2903 and (p_pl_id is null or
2904 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
2905 and (p_pl_typ_id is null or
2906 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
2907 and ben_generate_communications.g_comm_start_date
2908 between ctu.effective_start_date
2909 and ctu.effective_end_date
2910 and ctu.cm_typ_id = p_cm_typ_id
2911 and ctu.all_r_any_cd = 'ALL';
2912 --
2913 l_c1 c1%rowtype;
2914 --
2915 -- Local variables
2916 --
2917 l_usages_created boolean := false;
2918 l_created boolean := false;
2919 l_dummy varchar2(1);
2920 --
2921 -- Output variables
2922 --
2923 l_per_cm_usg_id number;
2924 --
2925 begin
2926 --
2927 hr_utility.set_location('Entering : ' || l_proc, 10);
2928 --
2929 if p_whnvr_trgrd_flag = 'N' then
2930 --
2931 -- We have to check to see if usages exist.
2932 --
2933 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2934 --
2935 open c1;
2936 --
2937 loop
2938 --
2939 fetch c1 into l_c1;
2940 exit when c1%notfound;
2941 --
2942 hr_utility.set_location(' cursor got row ' , 110);
2943 if rule_passes
2944 (p_rule_id => l_c1.cm_usg_rl,
2945 p_person_id => p_person_id,
2946 p_assignment_id => p_assignment_id,
2947 p_business_group_id => p_business_group_id,
2948 p_organization_id => p_organization_id,
2949 p_communication_type_id => p_cm_typ_id,
2950 p_ler_id => p_ler_id,
2951 p_pgm_id => p_pgm_id,
2952 p_pl_id => p_pl_id,
2953 p_pl_typ_id => p_pl_typ_id,
2954 p_per_cm_id => p_per_cm_id,
2955 p_effective_date => l_effective_date) then
2956 --
2957 -- create usage
2958 --
2959 pop_ben_per_cm_usg_f
2960 (p_per_cm_id => p_per_cm_id,
2961 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
2962 p_business_group_id => p_business_group_id,
2963 p_effective_date => p_effective_date,
2964 p_per_cm_usg_id => l_per_cm_usg_id,
2965 p_usage_created => l_usages_created);
2966 --
2967 if l_usages_created then
2968 --
2969 -- Set boolean so we know that we created at least one usage
2970 --
2971 l_created := true;
2972 --
2973 end if;
2974 --
2975 end if;
2976 --
2977 end loop;
2978 --
2979 close c1;
2980 --
2981 else
2982 --
2983 l_created := true;
2984 --
2985 end if;
2986 --
2987 hr_utility.set_location('Leaving : ' || l_proc, 10);
2988 --
2989 end check_dpnt_end_enrt;
2990 --
2991 procedure check_mass_mail
2992 (p_per_in_ler_id in number,
2993 p_person_id in number,
2994 p_business_group_id in number,
2995 p_assignment_id in number,
2996 p_organization_id in number,
2997 p_pgm_id in number,
2998 p_pl_id in number,
2999 p_pl_typ_id in number,
3000 p_per_cm_id in number,
3001 p_cm_typ_id in number,
3002 p_effective_date in date,
3003 p_lf_evt_ocrd_dt in date,
3004 p_whnvr_trgrd_flag in varchar2,
3005 p_usages_created out nocopy boolean) is
3006 --
3007 l_proc varchar2(80) := g_package || '.check_mass_mail';
3008 l_effective_date date;
3009 --
3010 cursor c_mssmlg is
3011 select ctu.cm_typ_usg_id,
3012 ctu.cm_usg_rl
3013 from ben_cm_typ_usg_f ctu
3014 where ctu.business_group_id = p_business_group_id
3015 and (p_pgm_id is null or
3016 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3017 and (p_pl_id is null or
3018 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3019 and (p_pl_typ_id is null or
3020 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3021 and ben_generate_communications.g_comm_start_date
3022 between ctu.effective_start_date
3023 and ctu.effective_end_date
3024 and ctu.cm_typ_id = p_cm_typ_id
3025 and ctu.all_r_any_cd = 'ALL';
3026 --
3027 l_mssmlg c_mssmlg%rowtype;
3028 --
3029 -- Local variables
3030 --
3031 l_usages_created boolean := false;
3032 l_created boolean := false;
3033 l_dummy varchar2(1);
3034 --
3035 -- Output variables
3036 --
3037 l_per_cm_usg_id number;
3038 --
3039 begin
3040 --
3041 hr_utility.set_location('Entering : ' || l_proc, 10);
3042 --
3043 if p_whnvr_trgrd_flag = 'N' then
3044 --
3045 -- We have to check to see if usages exist.
3046 --
3047 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3048 --
3049 open c_mssmlg;
3050 --
3051 loop
3052 --
3053 fetch c_mssmlg into l_mssmlg;
3054 exit when c_mssmlg%notfound;
3055 --
3056 if rule_passes
3057 (p_rule_id => l_mssmlg.cm_usg_rl,
3058 p_person_id => p_person_id,
3059 p_assignment_id => p_assignment_id,
3060 p_business_group_id => p_business_group_id,
3061 p_organization_id => p_organization_id,
3062 p_communication_type_id => p_cm_typ_id,
3063 p_ler_id => null,
3064 p_pgm_id => p_pgm_id,
3065 p_pl_id => p_pl_id,
3066 p_pl_typ_id => p_pl_typ_id,
3067 p_per_cm_id => p_per_cm_id,
3068 p_effective_date => l_effective_date) then
3069 --
3070 -- create usage
3071 --
3072 pop_ben_per_cm_usg_f
3073 (p_per_cm_id => p_per_cm_id,
3074 p_cm_typ_usg_id => l_mssmlg.cm_typ_usg_id,
3075 p_business_group_id => p_business_group_id,
3076 p_effective_date => p_effective_date,
3077 p_per_cm_usg_id => l_per_cm_usg_id,
3078 p_usage_created => l_usages_created);
3079 --
3080 if l_usages_created then
3081 --
3082 -- Set boolean so we know that we created at least one usage
3083 --
3084 l_created := true;
3085 --
3086 end if;
3087 --
3088 end if;
3089 --
3090 end loop;
3091 --
3092 close c_mssmlg;
3093 --
3094 else
3095 --
3096 l_created := true;
3097 --
3098 end if;
3099 --
3100 hr_utility.set_location('Leaving : ' || l_proc, 10);
3101 --
3102 end check_mass_mail;
3103 --
3104 procedure check_enrt_rmdr
3105 (p_per_in_ler_id in number,
3106 p_person_id in number,
3107 p_business_group_id in number,
3108 p_assignment_id in number,
3109 p_organization_id in number,
3110 p_pgm_id in number,
3111 p_pl_id in number,
3112 p_pl_typ_id in number,
3113 -- PB : 5422 :
3114 p_asnd_lf_evt_dt in date,
3115 -- p_enrt_perd_id in number,
3116 p_ler_id in number,
3117 p_per_cm_id in number,
3118 p_cm_typ_id in number,
3119 p_enrt_mthd_cd in varchar2,
3120 p_lf_evt_ocrd_dt in date,
3121 p_effective_date in date,
3122 p_whnvr_trgrd_flag in varchar2,
3123 p_usages_created out nocopy boolean) is
3124 --
3125 l_proc varchar2(80) := g_package || 'check_enrt_rmdr';
3126 l_effective_date date;
3127 --
3128 cursor c1 is
3129 select ctu.cm_typ_usg_id,
3130 ctu.cm_usg_rl
3131 from ben_cm_typ_usg_f ctu,
3132 ben_pil_elctbl_chc_popl pel
3133 where ctu.business_group_id = p_business_group_id
3134 and pel.business_group_id = ctu.business_group_id
3135 and (p_ler_id is null or
3136 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
3137 and (p_pl_id is null or
3138 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3139 and (p_pgm_id is null or
3140 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3141 and (p_pl_typ_id is null or
3142 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3143 and (p_asnd_lf_evt_dt is null or
3144 ctu.enrt_perd_id is null or
3145 exists (
3146 select null
3147 from ben_enrt_perd enp_c
3148 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3149 enp_c.business_group_id=ctu.business_group_id and
3150 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
3151 )
3152 )
3153 /* Now join in enrollment period */
3154 and pel.per_in_ler_id = p_per_in_ler_id
3155 and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
3156 = nvl(pel.enrt_perd_id, -1)
3157 and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
3158 and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
3159 and ben_generate_communications.g_comm_start_date
3160 between ctu.effective_start_date
3161 and ctu.effective_end_date
3162 and ctu.cm_typ_id = p_cm_typ_id
3163 and ctu.all_r_any_cd = 'ALL'
3164 and exists
3165 (select null
3166 from ben_elig_per_elctbl_chc epe1
3167 where epe1.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
3168 and epe1.elctbl_flag = 'Y');
3169 --
3170 cursor c2 is
3171 select null
3172 from ben_elig_per_elctbl_chc epe
3173 where epe.per_in_ler_id = p_per_in_ler_id
3174 and epe.elctbl_flag = 'Y'
3175 and epe.business_group_id = p_business_group_id;
3176 --
3177 -- Cursor fetch definition
3178 --
3179 l_c1 c1%rowtype;
3180 --
3181 l_usages_created boolean := false;
3182 l_created boolean := false;
3183 l_dummy varchar2(1);
3184 --
3185 -- Output variables
3186 --
3187 l_per_cm_usg_id number;
3188 --
3189 begin
3190 --
3191 hr_utility.set_location('Entering: '||l_proc,10);
3192 --
3193 if p_whnvr_trgrd_flag = 'N' then
3194 --
3195 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3196 --
3197 open c1;
3198 --
3199 loop
3200 --
3201 fetch c1 into l_c1;
3202 exit when c1%notfound;
3203 --
3204 if rule_passes(
3205 p_rule_id => l_c1.cm_usg_rl,
3206 p_person_id => p_person_id,
3207 p_assignment_id => p_assignment_id,
3208 p_business_group_id => p_business_group_id,
3209 p_organization_id => p_organization_id,
3210 p_communication_type_id => p_cm_typ_id,
3211 p_ler_id => p_ler_id,
3212 p_pgm_id => p_pgm_id,
3213 p_pl_id => p_pl_id,
3214 p_pl_typ_id => p_pl_typ_id,
3215 p_per_cm_id => p_per_cm_id,
3216 p_effective_date => l_effective_date) then
3217 --
3218 -- create usage
3219 --
3220 pop_ben_per_cm_usg_f
3221 (p_per_cm_id => p_per_cm_id,
3222 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
3223 p_business_group_id => p_business_group_id,
3224 p_effective_date => p_effective_date,
3225 p_per_cm_usg_id => l_per_cm_usg_id,
3226 p_usage_created => l_usages_created);
3227 --
3228 if l_usages_created then
3229 --
3230 -- Set boolean so we know that we created at least one usage
3231 --
3232 l_created := true;
3233 --
3234 end if;
3235 --
3236 end if;
3237 --
3238 end loop;
3239 --
3240 close c1;
3241 --
3242 else
3243 --
3244 open c2;
3245 --
3246 fetch c2 into l_dummy;
3247 if c2%found then
3248 --
3249 l_created := true;
3250 --
3251 end if;
3252 --
3253 close c2;
3254 --
3255 end if;
3256 --
3257 hr_utility.set_location('Leaving: '||l_proc,10);
3258 --
3259 p_usages_created := l_created;
3260 --
3261 end check_enrt_rmdr;
3262 --
3263 procedure check_emrg_evt
3264 (p_per_in_ler_id in number,
3265 p_person_id in number,
3266 p_business_group_id in number,
3267 p_assignment_id in number,
3268 p_organization_id in number,
3269 p_per_cm_id in number,
3270 p_cm_typ_id in number,
3271 p_pgm_id in number,
3272 p_pl_id in number,
3273 p_pl_typ_id in number,
3274 p_enrt_mthd_cd in varchar2,
3275 p_lf_evt_ocrd_dt in date,
3276 p_effective_date in date,
3277 p_whnvr_trgrd_flag in varchar2,
3278 p_usages_created out nocopy boolean) is
3279 --
3280 l_proc varchar2(80) := g_package || 'check_emrg_evt';
3281 l_effective_date date;
3282 --
3283 cursor c1 is
3284 select ctu.cm_typ_usg_id,
3285 ctu.cm_usg_rl
3286 from ben_cm_typ_usg_f ctu
3287 where ctu.business_group_id = p_business_group_id
3288 and (p_pl_id is null or
3289 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3290 and (p_pgm_id is null or
3291 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3292 and (p_pl_typ_id is null or
3293 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3294 and ben_generate_communications.g_comm_start_date
3295 between ctu.effective_start_date
3296 and ctu.effective_end_date
3297 and ctu.cm_typ_id = p_cm_typ_id
3298 and ctu.all_r_any_cd = 'ALL';
3299 --
3300 -- Cursor fetch definition
3301 --
3302 l_c1 c1%rowtype;
3303 --
3304 -- Local variables
3305 --
3306 l_usages_created boolean := false;
3307 l_created boolean := false;
3308 l_dummy varchar2(1);
3309 --
3310 -- Output variables
3311 --
3312 l_per_cm_usg_id number;
3313 --
3314 begin
3315 --
3316 hr_utility.set_location('Entering: '||l_proc,10);
3317 --
3318 if p_whnvr_trgrd_flag = 'N' then
3319 --
3320 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3321 --
3322 open c1;
3323 --
3324 loop
3325 --
3326 fetch c1 into l_c1;
3327 exit when c1%notfound;
3328 --
3329 if rule_passes
3330 (p_rule_id => l_c1.cm_usg_rl,
3331 p_person_id => p_person_id,
3332 p_assignment_id => p_assignment_id,
3333 p_business_group_id => p_business_group_id,
3334 p_organization_id => p_organization_id,
3335 p_communication_type_id => p_cm_typ_id,
3336 p_ler_id => null,
3337 p_pgm_id => p_pgm_id,
3338 p_pl_id => p_pl_id,
3339 p_pl_typ_id => p_pl_typ_id,
3340 p_per_cm_id => p_per_cm_id,
3341 p_effective_date => l_effective_date) then
3342 --
3343 -- create usage
3344 --
3345 pop_ben_per_cm_usg_f
3346 (p_per_cm_id => p_per_cm_id,
3347 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
3348 p_business_group_id => p_business_group_id,
3349 p_effective_date => p_effective_date,
3350 p_per_cm_usg_id => l_per_cm_usg_id,
3351 p_usage_created => l_usages_created);
3352 --
3353 if l_usages_created then
3354 --
3355 -- Set boolean so we know that we created at least one usage
3356 --
3357 l_created := true;
3358 --
3359 end if;
3360 --
3361 end if;
3362 --
3363 end loop;
3364 --
3365 close c1;
3366 --
3367 else
3368 --
3369 l_created := true;
3370 --
3371 end if;
3372 --
3373 hr_utility.set_location('Leaving: '||l_proc,10);
3374 --
3375 p_usages_created := l_created;
3376 --
3377 end check_emrg_evt;
3378 --
3379 procedure check_rate_change
3380 (p_per_in_ler_id in number,
3381 p_person_id in number,
3382 p_business_group_id in number,
3383 p_organization_id in number,
3384 p_pgm_id in number,
3385 p_pl_id in number,
3386 p_pl_typ_id in number,
3387 p_assignment_id in number,
3388 -- PB : 5422 :
3389 p_asnd_lf_evt_dt in date,
3390 -- p_enrt_perd_id in number,
3391 p_ler_id in number,
3392 p_actn_typ_id in number,
3393 p_per_cm_id in number,
3394 p_cm_typ_id in number,
3395 p_effective_date in date,
3396 p_lf_evt_ocrd_dt in date,
3397 p_whnvr_trgrd_flag in varchar2,
3398 p_usages_created out nocopy boolean) is
3399 --
3400 l_proc varchar2(80) := g_package||'rate_change';
3401 l_effective_date date;
3402 --
3403 cursor c1 is
3404 select ctu.cm_typ_usg_id,
3405 ctu.cm_usg_rl,
3406 ctu.pl_id, -- Bug 1555557
3407 ctu.pgm_id,
3408 ctu.pl_typ_id
3409 from ben_cm_typ_usg_f ctu,
3410 ben_prtt_enrt_rslt_f pen,
3411 ben_prtt_rt_val prv
3412 where ctu.business_group_id = p_business_group_id
3413 /* First join comp objects */
3414 and prv.per_in_ler_id = p_per_in_ler_id
3415 and prv.elctns_made_dt = p_effective_date
3416 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3417 and pen.business_group_id = prv.business_group_id
3418 and pen.per_in_ler_id <> prv.per_in_ler_id
3419 and p_effective_date
3420 between pen.effective_start_date
3421 and pen.effective_end_date
3422 /* Use nvl here as only pgm or pl can be populated */
3423 and nvl(ctu.ler_id,nvl(p_ler_id,-1)) = nvl(p_ler_id,-1)
3424 and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
3425 and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
3426 /* Now join in enrollment period */
3427 and (p_asnd_lf_evt_dt is null or
3428 ctu.enrt_perd_id is null or
3429 exists (
3430 select null
3431 from ben_enrt_perd enp_c
3432 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3433 enp_c.business_group_id=ctu.business_group_id and
3434 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
3435 )
3436 /* PB : 5422 :
3437 select null
3438 from ben_enrt_perd enp_c,
3439 ben_enrt_perd enp_m
3440 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3441 enp_c.business_group_id=ctu.business_group_id and
3442 enp_m.enrt_perd_id=p_enrt_perd_id and
3443 enp_m.business_group_id=ctu.business_group_id and
3444 enp_m.strt_dt=enp_c.strt_dt
3445 ) */
3446 )
3447 and ben_generate_communications.g_comm_start_date
3448 between ctu.effective_start_date
3449 and ctu.effective_end_date
3450 and ctu.cm_typ_id = p_cm_typ_id
3451 and ctu.all_r_any_cd = 'ALL'
3452 and pen.prtt_enrt_rslt_stat_cd is null
3453 and prv.prtt_rt_val_stat_cd is null;
3454 --
3455 cursor c2 is
3456 select null
3457 from ben_prtt_rt_val prv,
3458 ben_prtt_enrt_rslt_f pen
3459 where prv.business_group_id = p_business_group_id
3460 and prv.per_in_ler_id = p_per_in_ler_id
3461 and prv.elctns_made_dt = p_effective_date
3462 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3463 and pen.business_group_id = prv.business_group_id
3464 and pen.prtt_enrt_rslt_stat_cd is null
3465 and prv.prtt_rt_val_stat_cd is null
3466 and pen.per_in_ler_id <> prv.per_in_ler_id
3467 and p_effective_date
3468 between pen.effective_start_date
3469 and pen.effective_end_date;
3470 --
3471 -- Cursor fetch definition
3472 --
3473 l_c1 c1%rowtype;
3474 --
3475 -- Local variables
3476 --
3477 l_usages_created boolean := false;
3478 l_created boolean := false;
3479 l_dummy varchar2(1);
3480 --
3481 -- Output variables
3482 --
3483 l_per_cm_usg_id number;
3484 --
3485 begin
3486 --
3487 hr_utility.set_location('Entering: '||l_proc,10);
3488 --
3489 if p_whnvr_trgrd_flag = 'N' then
3490 --
3491 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3492 --
3493 open c1;
3494 --
3495 loop
3496 --
3497 fetch c1 into l_c1;
3498 exit when c1%notfound;
3499 --
3500 if rule_passes
3501 (p_rule_id => l_c1.cm_usg_rl,
3502 p_person_id => p_person_id,
3503 p_assignment_id => p_assignment_id,
3504 p_business_group_id => p_business_group_id,
3505 p_organization_id => p_organization_id,
3506 p_communication_type_id => p_cm_typ_id,
3507 p_ler_id => p_ler_id,
3508 p_pgm_id => nvl(p_pgm_id, l_c1.pgm_id), -- Bug 1555557
3509 p_pl_id => nvl(p_pl_id, l_c1.pl_id), -- Bug 1555557
3510 p_pl_typ_id => p_pl_typ_id,
3511 p_per_cm_id => p_per_cm_id,
3512 p_effective_date => l_effective_date) then
3513 --
3514 -- create usage
3515 --
3516 pop_ben_per_cm_usg_f
3517 (p_per_cm_id => p_per_cm_id,
3518 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
3519 p_business_group_id => p_business_group_id,
3520 p_effective_date => p_effective_date,
3521 p_per_cm_usg_id => l_per_cm_usg_id,
3522 p_usage_created => l_usages_created);
3523 --
3524 if l_usages_created then
3525 --
3526 -- Set boolean so we know that we created at least one usage
3527 --
3528 l_created := true;
3529 --
3530 end if;
3531 --
3532 end if;
3533 --
3534 end loop;
3535 --
3536 close c1;
3537 --
3538 else
3539 --
3540 open c2;
3541 --
3542 fetch c2 into l_dummy;
3543 if c2%found then
3544 --
3545 l_created := true;
3546 --
3547 end if;
3548 --
3549 close c2;
3550 --
3551 end if;
3552 --
3553 hr_utility.set_location('Leaving: '||l_proc,10);
3554 --
3555 p_usages_created := l_created;
3556 --
3557 end check_rate_change;
3558 --
3559 procedure check_hipaa_usages
3560 (p_per_in_ler_id in number,
3561 p_person_id in number,
3562 p_business_group_id in number,
3563 p_assignment_id in number,
3564 p_organization_id in number,
3565 p_pgm_id in number,
3566 p_pl_id in number,
3567 p_pl_typ_id in number,
3568 p_ler_id in number,
3569 -- PB : 5422 :
3570 p_asnd_lf_evt_dt in date,
3571 -- p_enrt_perd_id in number,
3572 p_per_cm_id in number,
3573 p_cm_typ_id in number,
3574 p_effective_date in date,
3575 p_lf_evt_ocrd_dt in date,
3576 p_whnvr_trgrd_flag in varchar2,
3577 p_usages_created out nocopy boolean) is
3578 --
3579 l_proc varchar2(80) := g_package||'check_hipaa_usages';
3580 l_effective_date date;
3581 --
3582 cursor c1 is
3583 select ctu.cm_typ_usg_id,
3584 ctu.cm_usg_rl
3585 from ben_cm_typ_usg_f ctu
3586 where ctu.business_group_id = p_business_group_id
3587 and (p_ler_id is null or
3588 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
3589 and (p_pgm_id is null or
3590 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3591 and (p_pl_id is null or
3592 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3593 and (p_pl_typ_id is null or
3594 nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
3595 and (p_asnd_lf_evt_dt is null or
3596 ctu.enrt_perd_id is null or
3597 exists (
3598 select null
3599 from ben_enrt_perd enp_c
3600 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3601 enp_c.business_group_id=ctu.business_group_id and
3602 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
3603 )
3604 /* PB : 5422 :
3605 select null
3606 from ben_enrt_perd enp_c,
3607 ben_enrt_perd enp_m
3608 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3609 enp_c.business_group_id=ctu.business_group_id and
3610 enp_m.enrt_perd_id=p_enrt_perd_id and
3611 enp_m.business_group_id=ctu.business_group_id and
3612 enp_m.strt_dt=enp_c.strt_dt
3613 ) */
3614 )
3615 and ben_generate_communications.g_comm_start_date
3616 between ctu.effective_start_date
3617 and ctu.effective_end_date
3618 and ctu.cm_typ_id = p_cm_typ_id
3619 and ctu.all_r_any_cd = 'ALL';
3620 --
3621 -- Cursor fetch definition
3622 --
3623 l_c1 c1%rowtype;
3624 --
3625 -- Local variables
3626 --
3627 l_usages_created boolean := false;
3628 l_created boolean := false;
3629 --
3630 -- Output variables
3631 --
3632 l_per_cm_usg_id number;
3633 --
3634 begin
3635 --
3636 hr_utility.set_location('Entering: '||l_proc,10);
3637 --
3638 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3639 --
3640 if p_whnvr_trgrd_flag = 'N' then
3641 --
3642 open c1;
3643 --
3644 loop
3645 --
3646 fetch c1 into l_c1;
3647 exit when c1%notfound;
3648 --
3649 if rule_passes
3650 (p_rule_id => l_c1.cm_usg_rl,
3651 p_person_id => p_person_id,
3652 p_assignment_id => p_assignment_id,
3653 p_business_group_id => p_business_group_id,
3654 p_organization_id => p_organization_id,
3655 p_communication_type_id => p_cm_typ_id,
3656 p_ler_id => p_ler_id,
3657 p_pgm_id => p_pgm_id,
3658 p_pl_id => p_pl_id,
3659 p_pl_typ_id => p_pl_typ_id,
3660 p_per_cm_id => p_per_cm_id,
3661 p_effective_date => l_effective_date) then
3662 --
3663 -- create usage
3664 --
3665 pop_ben_per_cm_usg_f
3666 (p_per_cm_id => p_per_cm_id,
3667 p_cm_typ_usg_id => l_c1.cm_typ_usg_id,
3668 p_business_group_id => p_business_group_id,
3669 p_effective_date => p_effective_date,
3670 p_per_cm_usg_id => l_per_cm_usg_id,
3671 p_usage_created => l_usages_created);
3672 --
3673 if l_usages_created then
3674 --
3675 -- Set boolean so we know that we created at least one usage
3676 --
3677 l_created := true;
3678 --
3679 end if;
3680 --
3681 end if;
3682 --
3683 end loop;
3684 --
3685 close c1;
3686 --
3687 else
3688 --
3689 l_created := true;
3690 --
3691 end if;
3692 --
3693 hr_utility.set_location('Leaving: '||l_proc,10);
3694 --
3695 p_usages_created := l_created;
3696 --
3697 end check_hipaa_usages;
3698 --
3699 procedure check_hipaa_ctfn
3700 (p_per_in_ler_id in number,
3701 p_person_id in number,
3702 p_business_group_id in number,
3703 p_assignment_id in number,
3704 p_organization_id in number,
3705 p_pgm_id in number,
3706 p_pl_id in number,
3707 p_pl_typ_id in number,
3708 p_ler_id in number,
3709 -- PB : 5422 :
3710 p_asnd_lf_evt_dt in date,
3711 -- p_enrt_perd_id in number,
3712 p_per_cm_id in number,
3713 p_cm_typ_id in number,
3714 p_effective_date in date,
3715 p_lf_evt_ocrd_dt in date,
3716 p_whnvr_trgrd_flag in varchar2,
3717 p_usages_created out nocopy boolean) is
3718 --
3719 l_proc varchar2(80) := g_package||'check_hipaa_ctfn';
3720 l_epe_exists varchar2(30) := 'N';
3721 l_crntly_enrd varchar2(30) := 'N';
3722 l_created boolean := false;
3723 l_usages_created boolean := false;
3724 --
3725 l_per_rec per_all_people_f%rowtype;
3726 --
3727 -- bwharton bug 1619271 added 4 lines below.
3728 l_pl_id number;
3729 l_oipl_id number;
3730 l_business_group_id number;
3731 l_pgm_id number;
3732 l_effective_date date;
3733 --
3734 -- This cursor gets all the comp. objects that were de-enrolled
3735 -- due/during this life event and satisfies the HIPAA conditions
3736 -- and regulation.
3737 --
3738 cursor c_prev_enrt is
3739 select distinct pen.pgm_id, pen.pl_typ_id
3740 from ben_prtt_enrt_rslt_f pen,
3741 ben_pl_f pln,
3742 ben_pl_regn_f plrg,
3743 ben_regn_f regn,
3744 ben_oipl_f cop,
3745 ben_opt_f opt
3746 where pen.per_in_ler_id = p_per_in_ler_id
3747 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
3748 and pen.sspndd_flag = 'N'
3749 and pen.prtt_enrt_rslt_stat_cd is null
3750 --8818355
3751 --and p_effective_date between
3752 -- pen.effective_start_date and pen.effective_end_date
3753 and pen.pl_id = pln.pl_id
3754 and pln.invk_dcln_prtn_pl_flag = 'N'
3755 and p_effective_date between
3756 pln.effective_start_date and pln.effective_end_date
3757 and plrg.pl_id = pln.pl_id
3758 and plrg.regn_id = regn.regn_id
3759 and regn.sttry_citn_name = 'HIPAA'
3760 and p_effective_date between
3761 plrg.effective_start_date and plrg.effective_end_date
3762 and p_effective_date between
3763 regn.effective_start_date and regn.effective_end_date
3764 and pen.oipl_id = cop.oipl_id (+)
3765 and p_effective_date between
3766 nvl(cop.effective_start_date, p_effective_date) and
3767 nvl(cop.effective_end_date, p_effective_date)
3768 and nvl(cop.opt_id, -1) = opt.opt_id (+)
3769 and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
3770 and p_effective_date between
3771 nvl(opt.effective_start_date, p_effective_date) and
3772 nvl(opt.effective_end_date, p_effective_date);
3773 --
3774 -- This cursor checks existence of any electable choices for that
3775 -- plan type with a started per in ler.
3776 --
3777 cursor c_epe(v_pgm_id in number,
3778 v_pl_typ_id in number) is
3779 select 'Y'
3780 from ben_elig_per_elctbl_chc epe,
3781 ben_per_in_ler pil,
3782 ben_pl_f pln,
3783 ben_oipl_f cop,
3784 ben_opt_f opt
3785 where epe.per_in_ler_id = p_per_in_ler_id
3786 and nvl(epe.pgm_id,-1) = nvl(v_pgm_id,-1)
3787 and epe.pl_typ_id = v_pl_typ_id
3788 and epe.elctbl_flag = 'Y'
3789 and epe.per_in_ler_id = pil.per_in_ler_id
3790 and pil.per_in_ler_stat_cd = 'STRTD'
3791 and epe.pl_id = pln.pl_id
3792 and pln.invk_dcln_prtn_pl_flag = 'N'
3793 and p_effective_date between
3794 pln.effective_start_date and pln.effective_end_date
3795 and epe.oipl_id = cop.oipl_id (+)
3796 and p_effective_date between
3797 nvl(cop.effective_start_date, p_effective_date) and
3798 nvl(cop.effective_end_date, p_effective_date)
3799 and nvl(cop.opt_id, -1) = opt.opt_id (+)
3800 and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
3801 and p_effective_date between
3802 nvl(opt.effective_start_date, p_effective_date) and
3803 nvl(opt.effective_end_date, p_effective_date);
3804 --
3805 -- The cursor checks whether the participant is stil covered in the
3806 -- plan type.
3807 --
3808 cursor c_crntly_enrd(v_pgm_id in number,
3809 v_pl_typ_id in number) is
3810 select 'Y'
3811 from ben_prtt_enrt_rslt_f pen,
3812 ben_pl_f pln,
3813 ben_oipl_f cop,
3814 ben_opt_f opt
3815 where pen.person_id = p_person_id
3816 -- and nvl(pen.pgm_id,-1) = nvl(v_pgm_id,-1) maagrawa (02/11/00)
3817 and pen.pl_typ_id = v_pl_typ_id
3818 and pen.enrt_cvg_thru_dt = hr_api.g_eot
3819 --8818355
3820 and pen.effective_end_date = hr_api.g_eot
3821 and pen.sspndd_flag = 'N'
3822 and pen.prtt_enrt_rslt_stat_cd is null
3823 --8818355
3824 --and p_effective_date between
3825 -- pen.effective_start_date and pen.effective_end_date
3826 and pen.pl_id = pln.pl_id
3827 and pln.invk_dcln_prtn_pl_flag = 'N'
3828 and p_effective_date between
3829 pln.effective_start_date and pln.effective_end_date
3830 and pen.oipl_id = cop.oipl_id (+)
3831 and p_effective_date between
3832 nvl(cop.effective_start_date, p_effective_date) and
3833 nvl(cop.effective_end_date, p_effective_date)
3834 and nvl(cop.opt_id, -1) = opt.opt_id (+)
3835 and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
3836 and p_effective_date between
3837 nvl(opt.effective_start_date, p_effective_date) and
3838 nvl(opt.effective_end_date, p_effective_date);
3839 --
3840 -- bwharton bug 1619271 added cursor below.
3841 cursor c_revise_date (v_pgm_id number, v_pl_typ_id number,
3842 v_per_in_ler_id number, v_ler_id number) is
3843 select pgm_id, pl_id, oipl_id, business_group_id
3844 from ben_prtt_enrt_rslt_f
3845 where pl_typ_id = v_pl_typ_id
3846 and pgm_id = v_pgm_id
3847 and per_in_ler_id = v_per_in_ler_id
3848 and ler_id = v_ler_id
3849 order by prtt_enrt_rslt_id desc
3850 ;
3851 -- 3717297 the hipaa communication called from benmngle and close enrollment
3852 -- to avoid the dups
3853 cursor c_pcd is
3854 select 'x'
3855 from ben_per_cm_prvdd_f
3856 where per_cm_id = p_per_cm_id
3857 --
3858 -- Bug No: 3752029
3859 -- Commented out this condition since it was only allowing selecting records for which we
3860 -- have 'To_be_sent_code' as 'As of event date'. So to disallow duplicate HIPAA letter
3861 -- generation for other To_be_sent_code values, this condition is removed.
3862 --
3863 -- and to_be_sent_dt = g_to_be_sent_dt
3864 and p_effective_date between
3865 effective_start_date and effective_end_date
3866 ;
3867 l_tmp varchar2(1) ;
3868
3869 --
3870 --HIPAA Enh
3871 cursor c_cm_typ_pl_typ is
3872 select ctu.pl_typ_id,
3873 ctu.cm_typ_usg_id,
3874 ctu.cm_usg_rl
3875 from ben_cm_typ_usg_f ctu
3876 where ctu.business_group_id = p_business_group_id
3877 and (p_ler_id is null or
3878 nvl(ctu.ler_id,p_ler_id) = p_ler_id)
3879 and (p_pgm_id is null or
3880 nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
3881 and (p_pl_id is null or
3882 nvl(ctu.pl_id,p_pl_id) = p_pl_id)
3883 and (p_asnd_lf_evt_dt is null or
3884 ctu.enrt_perd_id is null or
3885 exists (
3886 select null
3887 from ben_enrt_perd enp_c
3888 where enp_c.enrt_perd_id=ctu.enrt_perd_id and
3889 enp_c.business_group_id=ctu.business_group_id and
3890 enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
3891 )
3892 )
3893 and ctu.pl_typ_id is not null
3894 and ben_generate_communications.g_comm_start_date
3895 between ctu.effective_start_date
3896 and ctu.effective_end_date
3897 and ctu.cm_typ_id = p_cm_typ_id
3898 and ctu.pl_typ_id is not null
3899 and ctu.all_r_any_cd = 'ALL';
3900 --HIPAA Enh
3901 --
3902 begin
3903 --
3904 -- We need to generate prtt. HIPAA comm. when a person de-enrolls
3905 -- from a plan which has HIPAA regulation attached. The communication
3906 -- needs to be generated only if he de-enrolls from all plans within
3907 -- the plan type (within that program).
3908 -- First we check for all the comp. objects which are getting de-enrolled.
3909 -- Then we check for whether the prtt. is currently enrolled in that
3910 -- plan type. If not, check for any enrollment opportunity available
3911 -- to enroll in that plan type. If there are no enrollment opportunity,
3912 -- then generate the neccessary HIPAA comm.
3913 --
3914 hr_utility.set_location('Entering: '||l_proc,10);
3915 --
3916 ben_person_object.get_object(p_person_id => p_person_id,
3917 p_rec => l_per_rec);
3918 --
3919 -- If the person is dead, no comm. for the prtt.
3920 --
3921 hr_utility.set_location('per_in_ler_id '||p_per_in_ler_id,20);
3922 if l_per_rec.date_of_death is not null then
3923 --
3924 p_usages_created := false;
3925 return;
3926 --
3927 end if;
3928 -- 3717297 the hipaa communication generated from benmngle , before close enrollment extract is executed
3929 -- which updates the send date. then the close enrollment create the same instance of the commu one more time
3930 -- fixed by validating whether same comm exists for the same pil id for the same to be send dt
3931
3932 open c_pcd ;
3933 fetch c_pcd into l_tmp ;
3934 if c_pcd%found then
3935 close c_pcd ;
3936 p_usages_created := false;
3937 hr_utility.set_location('communication exist for the same id '||l_proc,20);
3938 hr_utility.set_location(' same id '||p_per_cm_id,20);
3939 hr_utility.set_location(' same dt '||g_to_be_sent_dt,20);
3940 return;
3941 end if ;
3942 close c_pcd ;
3943
3944 --
3945 hr_utility.set_location('Before loop '||l_proc,20);
3946 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
3947 --
3948 for l_prev_enrt in c_prev_enrt loop
3949 --
3950 hr_utility.set_location('In loop: '||l_proc,30);
3951 --
3952 l_epe_exists := 'N';
3953 l_crntly_enrd := 'N';
3954 --
3955 open c_crntly_enrd(l_prev_enrt.pgm_id, l_prev_enrt.pl_typ_id);
3956 fetch c_crntly_enrd into l_crntly_enrd;
3957 close c_crntly_enrd;
3958 --
3959 --HIPAA Enh
3960 if l_crntly_enrd = 'N' then
3961 for l_cm_typ_pl_typ in c_cm_typ_pl_typ loop
3962 if rule_passes
3963 (p_rule_id => l_cm_typ_pl_typ.cm_usg_rl,
3964 p_person_id => p_person_id,
3965 p_assignment_id => p_assignment_id,
3966 p_business_group_id => p_business_group_id,
3967 p_organization_id => p_organization_id,
3968 p_communication_type_id => p_cm_typ_id,
3969 p_ler_id => p_ler_id,
3970 p_pgm_id => p_pgm_id,
3971 p_pl_id => p_pl_id,
3972 p_pl_typ_id => p_pl_typ_id,
3973 p_per_cm_id => p_per_cm_id,
3974 p_effective_date => l_effective_date) then
3975 open c_crntly_enrd(l_prev_enrt.pgm_id, l_cm_typ_pl_typ.pl_typ_id);
3976 fetch c_crntly_enrd into l_crntly_enrd;
3977 close c_crntly_enrd;
3978 end if;
3979 exit when l_crntly_enrd = 'Y';
3980 end loop;
3981 end if;
3982 --HIPAA Enh
3983 --
3984 if l_crntly_enrd = 'N' then
3985 --
3986 hr_utility.set_location('No Result: '||l_proc,40);
3987 --
3988 open c_epe(l_prev_enrt.pgm_id, l_prev_enrt.pl_typ_id);
3989 fetch c_epe into l_epe_exists;
3990 close c_epe;
3991 --
3992
3993 --HIPAA Enh
3994 if l_epe_exists = 'N' then
3995 for l_cm_typ_pl_typ in c_cm_typ_pl_typ loop
3996 if rule_passes
3997 (p_rule_id => l_cm_typ_pl_typ.cm_usg_rl,
3998 p_person_id => p_person_id,
3999 p_assignment_id => p_assignment_id,
4000 p_business_group_id => p_business_group_id,
4001 p_organization_id => p_organization_id,
4002 p_communication_type_id => p_cm_typ_id,
4003 p_ler_id => p_ler_id,
4004 p_pgm_id => p_pgm_id,
4005 p_pl_id => p_pl_id,
4006 p_pl_typ_id => p_pl_typ_id,
4007 p_per_cm_id => p_per_cm_id,
4008 p_effective_date => l_effective_date) then
4009 open c_epe(l_prev_enrt.pgm_id, l_cm_typ_pl_typ.pl_typ_id);
4010 fetch c_epe into l_epe_exists;
4011 close c_epe;
4012 end if;
4013 exit when l_epe_exists = 'Y';
4014 end loop;
4015 end if;
4016 --HIPAA Enh
4017
4018 --
4019 if l_epe_exists = 'N' then
4020 --
4021 -- hr_utility.set_location('No Choice : '||l_proc,50);
4022 --
4023 -- bwharton bug 1619271.
4024 -- When the to be sent date is null due to pl_id / oipl_id
4025 -- not available at the earlier call to determine date, try
4026 -- again as the pl_id / oipl_id can now be ascertained.
4027 -- the cursor is ordered by a descending prtt_enrt_rslt_id.
4028 --
4029 if g_to_be_sent_dt is null then
4030 for c_revise_date_rec in c_revise_date
4031 (
4032 l_prev_enrt.pgm_id,
4033 l_prev_enrt.pl_typ_id,
4034 p_per_in_ler_id,
4035 p_ler_id
4036 ) loop
4037 l_pgm_id := c_revise_date_rec.pgm_id;
4038 l_pl_id := c_revise_date_rec.pl_id;
4039 l_oipl_id := c_revise_date_rec.oipl_id;
4040 l_business_group_id := c_revise_date_rec.business_group_id;
4041 end loop;
4042 ben_determine_date.main
4043 (p_date_cd => g_p_date_cd,
4044 p_per_in_ler_id => p_per_in_ler_id,
4045 p_person_id => g_p_person_id,
4046 p_pgm_id => l_pgm_id,
4047 p_pl_id => l_pl_id,
4048 p_oipl_id => l_oipl_id,
4049 p_business_group_id => l_business_group_id,
4050 p_formula_id => g_p_formula_id,
4051 p_effective_date => g_p_effective_date,
4052 p_lf_evt_ocrd_dt => g_p_lf_evt_ocrd_dt,
4053 p_returned_date => g_to_be_sent_dt);
4054 hr_utility.set_location('BCW: revised g_to_be_sent_dt date : '
4055 ||g_to_be_sent_dt,1469);
4056 end if;
4057 --
4058 -- bwharton bug 1619271 end of changes above.
4059 check_hipaa_usages
4060 (p_per_in_ler_id => p_per_in_ler_id,
4061 p_person_id => p_person_id,
4062 p_business_group_id => p_business_group_id,
4063 p_assignment_id => p_assignment_id,
4064 p_organization_id => p_organization_id,
4065 p_pgm_id => l_prev_enrt.pgm_id,
4066 p_pl_id => p_pl_id,
4067 p_pl_typ_id => l_prev_enrt.pl_typ_id,
4068 p_ler_id => p_ler_id,
4069 -- PB : 5422 :
4070 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4071 -- p_enrt_perd_id => p_enrt_perd_id,
4072 p_per_cm_id => p_per_cm_id,
4073 p_cm_typ_id => p_cm_typ_id,
4074 p_effective_date => p_effective_date,
4075 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4076 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4077 p_usages_created => l_created);
4078 --
4079 if l_created then
4080 --
4081 l_usages_created := true;
4082 --
4083 end if;
4084 --
4085 end if;
4086 --
4087 end if;
4088 --
4089 end loop;
4090 --
4091 p_usages_created := l_usages_created;
4092 --
4093 hr_utility.set_location('Leaving: '||l_proc,10);
4094 --
4095 end check_hipaa_ctfn;
4096 --
4097 function usages_exist(p_proc_cd in varchar2,
4098 p_person_id in number,
4099 p_per_in_ler_id in number,
4100 p_organization_id in number,
4101 p_assignment_id in number,
4102 -- PB : 5422 :
4103 p_asnd_lf_evt_dt in date,
4104 -- p_enrt_perd_id in number,
4105 p_actn_typ_id in number,
4106 p_ler_id in number,
4107 p_enrt_mthd_cd in varchar2,
4108 p_pgm_id in number,
4109 p_pl_id in number,
4110 p_pl_typ_id in number,
4111 p_per_cm_id in number,
4112 p_cm_typ_id in number,
4113 p_business_group_id in number,
4114 p_effective_date in date,
4115 p_lf_evt_ocrd_dt in date,
4116 p_whnvr_trgrd_flag in varchar2) return boolean is
4117 --
4118 l_proc varchar2(80) := g_package||'usages_exist';
4119 --
4120 -- Output variable
4121 --
4122 l_usages_created boolean := false;
4123 --
4124 begin
4125 --
4126 hr_utility.set_location('Entering: '||l_proc,10);
4127 --
4128 hr_utility.set_location(' program ' || p_pgm_id , 1999);
4129 hr_utility.set_location(' ler_id ' || p_ler_id , 1999);
4130 hr_utility.set_location(' proc_cd '|| p_proc_cd , 1999);
4131
4132 -- The whenever triggered flag is only relevant to BENMNGLE calls due to
4133 -- the fact that we need to know what action took place and to make sure
4134 -- that an action took place.
4135 --
4136 -- Evaluate proc cd and then call relevant procedure to check usage
4137 --
4138 if p_proc_cd = 'MLEELIG' then
4139 --
4140 -- Do eligible case
4141 --
4142 check_first_time_elig_inelig
4143 (p_person_id => p_person_id,
4144 p_business_group_id => p_business_group_id,
4145 p_assignment_id => p_assignment_id,
4146 p_organization_id => p_organization_id,
4147 -- PB : 5422 :
4148 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4149 -- p_enrt_perd_id => p_enrt_perd_id,
4150 p_actn_typ_id => p_actn_typ_id,
4151 p_ler_id => p_ler_id,
4152 p_pgm_id => p_pgm_id,
4153 p_pl_id => p_pl_id,
4154 p_pl_typ_id => p_pl_typ_id,
4155 p_per_cm_id => p_per_cm_id,
4156 p_cm_typ_id => p_cm_typ_id,
4157 p_effective_date => p_effective_date,
4158 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4159 p_eligible_flag => 'Y',
4160 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4161 p_usages_created => l_usages_created);
4162 --
4163 elsif p_proc_cd = 'MLEINELIG' then
4164 --
4165 -- Do ineligible case
4166 --
4167 check_first_time_elig_inelig
4168 (p_person_id => p_person_id,
4169 p_business_group_id => p_business_group_id,
4170 p_assignment_id => p_assignment_id,
4171 p_organization_id => p_organization_id,
4172 -- PB : 5422 :
4173 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4174 -- p_enrt_perd_id => p_enrt_perd_id,
4175 p_actn_typ_id => p_actn_typ_id,
4176 p_ler_id => p_ler_id,
4177 p_pgm_id => p_pgm_id,
4178 p_pl_id => p_pl_id,
4179 p_pl_typ_id => p_pl_typ_id,
4180 p_per_cm_id => p_per_cm_id,
4181 p_cm_typ_id => p_cm_typ_id,
4182 p_effective_date => p_effective_date,
4183 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4184 p_eligible_flag => 'N',
4185 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4186 p_usages_created => l_usages_created);
4187 --
4188 elsif p_proc_cd = 'MLEAUTOENRT' then
4189 --
4190 check_automatic_enrollment
4191 (p_person_id => p_person_id,
4192 p_per_in_ler_id => p_per_in_ler_id,
4193 p_business_group_id => p_business_group_id,
4194 p_organization_id => p_organization_id,
4195 p_assignment_id => p_assignment_id,
4196 -- PB : 5422 :
4197 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4198 -- p_enrt_perd_id => p_enrt_perd_id,
4199 p_actn_typ_id => p_actn_typ_id,
4200 p_pgm_id => p_pgm_id,
4201 p_pl_id => p_pl_id,
4202 p_pl_typ_id => p_pl_typ_id,
4203 p_ler_id => p_ler_id,
4204 p_per_cm_id => p_per_cm_id,
4205 p_cm_typ_id => p_cm_typ_id,
4206 p_effective_date => p_effective_date,
4207 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4208 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4209 p_usages_created => l_usages_created);
4210 --
4211 elsif p_proc_cd = 'MLEPECP' then
4212 --
4213 check_electable_choice_popl
4214 (p_per_in_ler_id => p_per_in_ler_id,
4215 p_person_id => p_person_id,
4216 p_business_group_id => p_business_group_id,
4217 p_assignment_id => p_assignment_id,
4218 p_organization_id => p_organization_id,
4219 -- PB : 5422 :
4220 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4221 -- p_enrt_perd_id => p_enrt_perd_id,
4222 p_actn_typ_id => p_actn_typ_id,
4223 p_ler_id => p_ler_id,
4224 p_pgm_id => p_pgm_id,
4225 p_pl_id => p_pl_id,
4226 p_pl_typ_id => p_pl_typ_id,
4227 p_per_cm_id => p_per_cm_id,
4228 p_cm_typ_id => p_cm_typ_id,
4229 p_effective_date => p_effective_date,
4230 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4231 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4232 p_usages_created => l_usages_created);
4233 --
4234 elsif p_proc_cd = 'MLENOIMP' then
4235 --
4236 check_no_impact_on_benefits
4237 (p_per_in_ler_id => p_per_in_ler_id,
4238 p_person_id => p_person_id,
4239 p_business_group_id => p_business_group_id,
4240 p_assignment_id => p_assignment_id,
4241 p_organization_id => p_organization_id,
4242 p_ler_id => p_ler_id,
4243 p_pgm_id => p_pgm_id,
4244 p_pl_id => p_pl_id,
4245 p_pl_typ_id => p_pl_typ_id,
4246 p_per_cm_id => p_per_cm_id,
4247 p_cm_typ_id => p_cm_typ_id,
4248 p_effective_date => p_effective_date,
4249 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4250 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4251 p_usages_created => l_usages_created);
4252 --
4253 elsif p_proc_cd = 'MLEENDENRT' then
4254 --
4255 check_inelig_deenroll
4256 (p_per_in_ler_id => p_per_in_ler_id,
4257 p_person_id => p_person_id,
4258 p_business_group_id => p_business_group_id,
4259 p_assignment_id => p_assignment_id,
4260 p_organization_id => p_organization_id,
4261 -- PB : 5422 :
4262 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4263 -- p_enrt_perd_id => p_enrt_perd_id,
4264 p_ler_id => p_ler_id,
4265 p_pgm_id => p_pgm_id,
4266 p_pl_id => p_pl_id,
4267 p_pl_typ_id => p_pl_typ_id,
4268 p_actn_typ_id => p_actn_typ_id,
4269 p_per_cm_id => p_per_cm_id,
4270 p_cm_typ_id => p_cm_typ_id,
4271 p_effective_date => p_effective_date,
4272 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4273 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4274 p_usages_created => l_usages_created);
4275 --
4276 elsif p_proc_cd = 'MLERTCHG' then
4277 --
4278 check_rate_change
4279 (p_per_in_ler_id => p_per_in_ler_id,
4280 p_person_id => p_person_id,
4281 p_business_group_id => p_business_group_id,
4282 p_assignment_id => p_assignment_id,
4283 p_organization_id => p_organization_id,
4284 -- PB : 5422 :
4285 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4286 -- p_enrt_perd_id => p_enrt_perd_id,
4287 p_ler_id => p_ler_id,
4288 p_pgm_id => p_pgm_id,
4289 p_pl_id => p_pl_id,
4290 p_pl_typ_id => p_pl_typ_id,
4291 p_actn_typ_id => p_actn_typ_id,
4292 p_per_cm_id => p_per_cm_id,
4293 p_cm_typ_id => p_cm_typ_id,
4294 p_effective_date => p_effective_date,
4295 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4296 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4297 p_usages_created => l_usages_created);
4298 --
4299 elsif p_proc_cd in ('FORMENRT','WEBENRT','IVRENRT','DFLTENRT') then
4300 --
4301 check_expl_dflt_enrollment
4302 (p_per_in_ler_id => p_per_in_ler_id,
4303 p_person_id => p_person_id,
4304 p_business_group_id => p_business_group_id,
4305 p_assignment_id => p_assignment_id,
4306 p_organization_id => p_organization_id,
4307 -- PB : 5422 :
4308 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4309 -- p_enrt_perd_id => p_enrt_perd_id,
4310 p_ler_id => p_ler_id,
4311 p_pgm_id => p_pgm_id,
4312 p_pl_id => p_pl_id,
4313 p_pl_typ_id => p_pl_typ_id,
4314 p_per_cm_id => p_per_cm_id,
4315 p_cm_typ_id => p_cm_typ_id,
4316 p_enrt_mthd_cd => p_enrt_mthd_cd,
4317 p_effective_date => p_effective_date,
4318 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4319 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4320 p_usages_created => l_usages_created);
4321 --
4322 elsif p_proc_cd = 'CLSENRT' then
4323 --
4324 check_close_enrollment
4325 (p_per_in_ler_id => p_per_in_ler_id,
4326 p_person_id => p_person_id,
4327 p_business_group_id => p_business_group_id,
4328 p_assignment_id => p_assignment_id,
4329 p_organization_id => p_organization_id,
4330 p_ler_id => p_ler_id,
4331 p_per_cm_id => p_per_cm_id,
4332 p_cm_typ_id => p_cm_typ_id,
4333 p_effective_date => p_effective_date,
4334 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4335 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4336 p_usages_created => l_usages_created);
4337 --
4338 elsif p_proc_cd in ('ACTNCMPL', 'ACTNCREATED', 'MSSMLGAR') then
4339 --
4340 check_actn_item
4341 (p_per_in_ler_id => p_per_in_ler_id,
4342 p_person_id => p_person_id,
4343 p_business_group_id => p_business_group_id,
4344 p_assignment_id => p_assignment_id,
4345 p_organization_id => p_organization_id,
4346 p_actn_typ_id => p_actn_typ_id,
4347 p_pgm_id => p_pgm_id,
4348 p_pl_id => p_pl_id,
4349 p_pl_typ_id => p_pl_typ_id,
4350 p_ler_id => p_ler_id,
4351 p_per_cm_id => p_per_cm_id,
4352 p_cm_typ_id => p_cm_typ_id,
4353 p_effective_date => p_effective_date,
4354 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4355 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4356 p_usages_created => l_usages_created);
4357 --
4358 elsif p_proc_cd = 'MSSMLG' then
4359 --
4360 check_mass_mail
4361 (p_per_in_ler_id => p_per_in_ler_id,
4362 p_person_id => p_person_id,
4363 p_business_group_id => p_business_group_id,
4364 p_assignment_id => p_assignment_id,
4365 p_organization_id => p_organization_id,
4366 p_pgm_id => p_pgm_id,
4367 p_pl_id => p_pl_id,
4368 p_pl_typ_id => p_pl_typ_id,
4369 p_per_cm_id => p_per_cm_id,
4370 p_cm_typ_id => p_cm_typ_id,
4371 p_effective_date => p_effective_date,
4372 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4373 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4374 p_usages_created => l_usages_created);
4375 --
4376 elsif p_proc_cd = 'DPNTENDENRT' then
4377 --
4378 check_dpnt_end_enrt
4379 (p_person_id => p_person_id,
4380 p_assignment_id => p_assignment_id,
4381 p_business_group_id => p_business_group_id,
4382 p_organization_id => p_organization_id,
4383 p_pgm_id => p_pgm_id,
4384 p_pl_id => p_pl_id,
4385 p_pl_typ_id => p_pl_typ_id,
4386 p_ler_id => p_ler_id,
4387 p_per_cm_id => p_per_cm_id,
4388 p_cm_typ_id => p_cm_typ_id,
4389 p_effective_date => p_effective_date,
4390 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4391 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4392 p_usages_created => l_usages_created);
4393 --
4394 elsif p_proc_cd = 'MSSMLGER' then
4395 --
4396 check_enrt_rmdr
4397 (p_per_in_ler_id => p_per_in_ler_id,
4398 p_person_id => p_person_id,
4399 p_business_group_id => p_business_group_id,
4400 p_assignment_id => p_assignment_id,
4401 p_organization_id => p_organization_id,
4402 -- PB : 5422 :
4403 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4404 -- p_enrt_perd_id => p_enrt_perd_id,
4405 p_ler_id => p_ler_id,
4406 p_pgm_id => p_pgm_id,
4407 p_pl_id => p_pl_id,
4408 p_pl_typ_id => p_pl_typ_id,
4409 p_per_cm_id => p_per_cm_id,
4410 p_cm_typ_id => p_cm_typ_id,
4411 p_enrt_mthd_cd => p_enrt_mthd_cd,
4412 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4413 p_effective_date => p_effective_date,
4414 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4415 p_usages_created => l_usages_created);
4416 --
4417 elsif p_proc_cd = 'MSSMLGEE' then
4418 --
4419 check_emrg_evt
4420 (p_per_in_ler_id => p_per_in_ler_id,
4421 p_person_id => p_person_id,
4422 p_business_group_id => p_business_group_id,
4423 p_assignment_id => p_assignment_id,
4424 p_organization_id => p_organization_id,
4425 p_per_cm_id => p_per_cm_id,
4426 p_cm_typ_id => p_cm_typ_id,
4427 p_pl_id => p_pl_id,
4428 p_pgm_id => p_pgm_id,
4429 p_pl_typ_id => p_pl_typ_id,
4430 p_enrt_mthd_cd => p_enrt_mthd_cd,
4431 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4432 p_effective_date => p_effective_date,
4433 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4434 p_usages_created => l_usages_created);
4435 --
4436 elsif p_proc_cd = 'HPAPRTTDE' then
4437 --
4438 check_hipaa_ctfn
4439 (p_per_in_ler_id => p_per_in_ler_id,
4440 p_person_id => p_person_id,
4441 p_business_group_id => p_business_group_id,
4442 p_assignment_id => p_assignment_id,
4443 p_organization_id => p_organization_id,
4444 p_pgm_id => p_pgm_id,
4445 p_pl_id => p_pl_id,
4446 p_pl_typ_id => p_pl_typ_id,
4447 p_ler_id => p_ler_id,
4448 -- PB : 5422 :
4449 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4450 -- p_enrt_perd_id => p_enrt_perd_id,
4451 p_per_cm_id => p_per_cm_id,
4452 p_cm_typ_id => p_cm_typ_id,
4453 p_effective_date => p_effective_date,
4454 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4455 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4456 p_usages_created => l_usages_created);
4457 --
4458 elsif p_proc_cd in
4459 ('RMBPYMT', 'RMBRQST', 'RMBPRPY', 'RMBDND','RMBAPRVD' , 'RMBPNDG' , 'RMBNAPEL' , 'RMBVOID','RMBDPLCT')
4460 then
4461 --
4462 check_reimbursement
4463 (p_person_id => p_person_id,
4464 p_business_group_id => p_business_group_id,
4465 p_assignment_id => p_assignment_id,
4466 p_organization_id => p_organization_id,
4467 p_pgm_id => p_pgm_id,
4468 p_pl_id => p_pl_id,
4469 p_pl_typ_id => p_pl_typ_id,
4470 p_ler_id => p_ler_id,
4471 p_per_cm_id => p_per_cm_id,
4472 p_cm_typ_id => p_cm_typ_id,
4473 p_effective_date => p_effective_date,
4474 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4475 p_whnvr_trgrd_flag => p_whnvr_trgrd_flag,
4476 p_usages_created => l_usages_created);
4477 --
4478 else
4479 --
4480 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
4481 fnd_message.set_token('PROC',l_proc);
4482 fnd_message.set_token('CODE1',p_proc_cd);
4483 raise ben_manage_life_events.g_record_error;
4484 --
4485 end if;
4486 --
4487 hr_utility.set_location('Leaving: '||l_proc,10);
4488 --
4489 return l_usages_created;
4490 --
4491 end usages_exist;
4492 --
4493 procedure main(p_person_id in number,
4494 p_cm_trgr_typ_cd in varchar2 default null,
4495 p_cm_typ_id in number default null,
4496 p_ler_id in number default null,
4497 p_per_in_ler_id in number default null,
4498 p_prtt_enrt_actn_id in number default null,
4499 p_bnf_person_id in number default null,
4500 p_dpnt_person_id in number default null,
4501 -- PB : 5422 :
4502 p_asnd_lf_evt_dt in date default null,
4503 -- p_enrt_perd_id in number default null,
4504 p_actn_typ_id in number default null,
4505 p_enrt_mthd_cd in varchar2 default null,
4506 p_pgm_id in number default null,
4507 p_pl_id in number default null,
4508 p_pl_typ_id in number default null,
4509 p_rqstbl_untl_dt in date default null,
4510 p_business_group_id in number,
4511 p_proc_cd1 in varchar2 default null,
4512 p_proc_cd2 in varchar2 default null,
4513 p_proc_cd3 in varchar2 default null,
4514 p_proc_cd4 in varchar2 default null,
4515 p_proc_cd5 in varchar2 default null,
4516 p_proc_cd6 in varchar2 default null,
4517 p_proc_cd7 in varchar2 default null,
4518 p_proc_cd8 in varchar2 default null,
4519 p_proc_cd9 in varchar2 default null,
4520 p_proc_cd10 in varchar2 default null,
4521 p_effective_date in date,
4522 p_lf_evt_ocrd_dt in date default null,
4523 p_mode in varchar2 default 'I',
4524 p_source in varchar2 default null) is
4525 --
4526 l_proc varchar2(80) := g_package||'main';
4527 --
4528 l_ass_rec per_all_assignments_f%rowtype;
4529 l_pl_rec ben_pl_f%rowtype;
4530 l_pil_rec ben_per_in_ler%rowtype;
4531 l_loc_rec hr_locations_all%rowtype;
4532 l_effective_date date;
4533 --
4534 cursor c_triggers(p_eff_date date) is
4535 select ctr.cm_trgr_src_cd,
4536 ctr.cm_trgr_typ_cd,
4537 ctr.cm_trgr_id,
4538 ctt.cm_typ_trgr_rl,
4539 cct.whnvr_trgrd_flag,
4540 cmt.cm_dlvry_mthd_typ_cd,
4541 cmd.cm_dlvry_med_typ_cd,
4542 cct.inspn_rqd_flag,
4543 cct.cm_typ_id,
4544 cct.to_be_sent_dt_cd,
4545 cct.to_be_sent_dt_rl,
4546 cct.cm_typ_rl,
4547 cct.inspn_rqd_rl,
4548 ctr.proc_cd,
4549 cct.rcpent_cd,
4550 cct.name
4551 from ben_cm_trgr ctr,
4552 ben_cm_typ_trgr_f ctt,
4553 ben_cm_typ_f cct,
4554 ben_cm_dlvry_mthd_typ cmt,
4555 ben_cm_dlvry_med_typ cmd
4556 /* if p_cm_trgr_typ_cd is specified pick only those rows */
4557 where ctr.cm_trgr_typ_cd = nvl(p_cm_trgr_typ_cd, ctr.cm_trgr_typ_cd)
4558 and ctt.cm_trgr_id = ctr.cm_trgr_id
4559 and ctt.business_group_id = p_business_group_id
4560 and p_eff_date
4561 between ctt.effective_start_date
4562 and ctt.effective_end_date
4563 /* if p_cm_typ_id is specified, pick only those rows */
4564 and cct.cm_typ_id = nvl(p_cm_typ_id, cct.cm_typ_id)
4565 and cct.cm_typ_id = ctt.cm_typ_id
4566 and p_eff_date
4567 between cct.effective_start_date
4568 and cct.effective_end_date
4569 and cct.cm_typ_id = cmt.cm_typ_id(+)
4570 and nvl(cmt.dflt_flag,'Y') = 'Y'
4571 and nvl(cct.inactive_flag,'N') = 'N' -- Bug 12572670
4572 and cmt.cm_dlvry_mthd_typ_id = cmd.cm_dlvry_mthd_typ_id(+)
4573 and nvl(cmd.dflt_flag,'Y') = 'Y'
4574 and ctr.proc_cd in (p_proc_cd1,
4575 p_proc_cd2,
4576 p_proc_cd3,
4577 p_proc_cd4,
4578 p_proc_cd5,
4579 p_proc_cd6,
4580 p_proc_cd7,
4581 p_proc_cd8,
4582 p_proc_cd9,
4583 p_proc_cd10);
4584 --
4585 -- Cursor fetch definition
4586 --
4587 l_triggers c_triggers%rowtype;
4588 --
4589 cursor c_pil (p_per_in_ler_id number ) is
4590 select pil.lf_evt_ocrd_dt,
4591 ler.typ_cd
4592 from ben_per_in_ler pil ,
4593 ben_ler_f ler
4594 where pil.per_in_ler_id = p_per_in_ler_id
4595 and ler.ler_id = pil.ler_id
4596 and p_effective_date between
4597 ler.effective_start_date and ler.effective_end_date ;
4598 --
4599 -- Bug 6468678
4600 --
4601 CURSOR c_get_pil IS
4602 --
4603 SELECT pil.*
4604 FROM ben_per_in_ler pil, ben_ler_f ler
4605 WHERE pil.person_id = p_person_id
4606 AND pil.per_in_ler_stat_cd = 'STRTD'
4607 AND ler.ler_id = pil.ler_id
4608 AND ler.ler_id = NVL (p_ler_id, pil.ler_id)
4609 AND ler.typ_cd not in ('COMP', 'GSP', 'ABS')
4610 AND p_effective_date BETWEEN
4611 ler.effective_start_date AND ler.effective_end_date
4612 ORDER BY DECODE(ler.typ_cd,'SCHEDDU',1,2) desc ;
4613 --
4614 l_get_pil c_get_pil%ROWTYPE;
4615 --
4616 -- Bug 6468678
4617 -- Local variables
4618 --
4619 l_to_be_sent_dt date;
4620 l_pl_typ_id number := null;
4621 l_per_in_ler_id number := null;
4622 l_rqstbl_untl_dt date := null;
4623 --
4624 -- Out variables from procedure calls
4625 --
4626 l_per_cm_id number;
4627 l_per_cm_prvdd_id number;
4628 --
4629 l_lf_evt_ocrd_dt date ;
4630 -- 3296015
4631 l_pil_lf_evt_ocrd_dt date ;
4632 l_pil_typ_cd ben_ler_f.typ_cd%type ;
4633 --
4634 begin
4635 --
4636 hr_utility.set_location('Entering: '||l_proc,10);
4637 hr_utility.set_location(' proc cd 1' || p_proc_cd1,77);
4638 hr_utility.set_location(' proc cd 2' || p_proc_cd2,77);
4639 hr_utility.set_location(' proc cd 3' || p_proc_cd3,77);
4640 --
4641 l_effective_date := nvl(l_lf_evt_ocrd_dt,p_effective_date);
4642 --
4643 -- CWB Changes.
4644 --
4645 if p_per_in_ler_id is null then
4646 --
4647 -- Bug 64686780
4648 /* ben_person_object.get_object(p_person_id => p_person_id,
4649 p_rec => l_pil_rec); */
4650 /*Bug 8873512:Added If Else condition. If 'Mass Mailing',per_in_ler_id will be null.
4651 Do not get the per_in_ler_id*/
4652 if(nvl(p_proc_cd1,'MSSMLGAR') <> 'MSSMLG') then
4653 open c_get_pil;
4654 fetch c_get_pil into l_get_pil;
4655 close c_get_pil;
4656 end if;
4657 --
4658 -- Bug 6468678
4659 end if;
4660 --
4661 ben_person_object.get_object(p_person_id => p_person_id,
4662 p_rec => l_ass_rec);
4663 --
4664
4665 -- Bug 6468678
4666 -- l_per_in_ler_id := nvl(p_per_in_ler_id, l_pil_rec.per_in_ler_id);
4667 l_per_in_ler_id := nvl(p_per_in_ler_id, l_get_pil.per_in_ler_id);
4668 -- Bug 6468678
4669
4670 --- incase lf_evt_ocrd_dt is null, get it from pil
4671 l_lf_evt_ocrd_dt := p_lf_evt_ocrd_dt ;
4672
4673 --- 3296015 whne the maintenacne process executed , the current opne le is a
4674 --- picked for the communication , that may be age old so the fix
4675 --- getting the effective date if the ler type is unrestricred and ler is is null
4676
4677 if p_lf_evt_ocrd_dt is null and l_per_in_ler_id is not null then
4678 hr_utility.set_location('pil befo : '||l_lf_evt_ocrd_dt,10);
4679 open c_pil (l_per_in_ler_id) ;
4680 fetch c_pil into l_pil_lf_evt_ocrd_dt, l_pil_typ_cd ;
4681 close c_pil ;
4682
4683
4684 l_lf_evt_ocrd_dt := l_pil_lf_evt_ocrd_dt ;
4685 hr_utility.set_location('pil aftr : '||l_lf_evt_ocrd_dt,10);
4686
4687
4688 -- when the ler_id is null it is called from some process not from any event
4689 -- when the ler type is unrestricred the pil_id could be old
4690 -- the date validate whther pil id is old one # 3296015
4691 if p_ler_id is null and l_pil_typ_cd = 'SCHEDDU' and l_lf_evt_ocrd_dt < p_effective_date then
4692 l_lf_evt_ocrd_dt := p_effective_date ;
4693 hr_utility.set_location('ler aftr : '||l_lf_evt_ocrd_dt,10);
4694 end if ;
4695
4696 end if ;
4697
4698
4699 -- when the ler_id is null it is called from some process not from any event
4700 -- when the ler type is unrestricred the pil_id could be old
4701 -- the date validate whther pil id is old one # 3296015
4702 if p_ler_id is null and l_pil_typ_cd = 'SCHEDDU' and l_lf_evt_ocrd_dt < p_effective_date then
4703 l_lf_evt_ocrd_dt := p_effective_date ;
4704 hr_utility.set_location('ler aftr : '||l_lf_evt_ocrd_dt,10);
4705 end if ;
4706
4707 --
4708 if l_ass_rec.assignment_id is null then
4709 --
4710 -- Grab the persons benefit assignment
4711 --
4712 ben_person_object.get_benass_object(p_person_id => p_person_id,
4713 p_rec => l_ass_rec);
4714 --
4715 end if;
4716
4717 ----
4718 --
4719 hr_utility.set_location( 'plan and type '|| p_pl_id || ' / '||p_pl_typ_id, 77 );
4720 if p_pl_id is not null and
4721 p_pl_typ_id is null then
4722 --
4723 ben_comp_object.get_object(p_pl_id => p_pl_id,
4724 p_rec => l_pl_rec);
4725 --
4726 l_pl_typ_id := l_pl_rec.pl_typ_id;
4727 --
4728 else
4729 --
4730 l_pl_typ_id := p_pl_typ_id;
4731 --
4732 end if;
4733 --
4734 -- Steps to generate communications
4735 --
4736 -- 1. Get all communication triggers for BG that are valid as of effective
4737 -- date. Join to ben_cm_typ_trgr_f and ben_cm_typ (Cursor c_triggers).
4738 -- 2. Loop through records
4739 -- 3. Set Savepoint
4740 -- 4. If rule exists and fails then look at next record
4741 -- 5. If whnvr_trgrd_flag = 'Y' then do for all comp objects
4742 -- 4a. Populate ben_cm_trgr_f
4743 -- 4b. Populate ben_per_cm_f
4744 -- 4b. Populate ben_per_cm_prvdd_f
4745 -- 6. If whnvr_trgrd_flag = 'N' then join to cm_typ_usg_f
4746 -- 7. If rule exists or no rule and a usage exists
4747 -- 6a. Populate ben_per_cm_trgr_f
4748 -- 6b. Populate ben_per_cm_usg_f
4749 -- 6c. Populate ben_per_cm_f
4750 -- 6d. Populate ben_per_cm_prvdd_f
4751 -- 8. Go to 2.
4752 --
4753 -- Step 1.
4754 --
4755
4756 g_ct_eval_tab.delete;
4757
4758 open c_triggers(l_effective_date);
4759 --
4760 loop
4761 --
4762 -- Step 2.
4763 --
4764 fetch c_triggers into l_triggers;
4765 exit when c_triggers%notfound;
4766 --
4767 -- Step 3.
4768 --
4769 savepoint communications_savepoint;
4770 --
4771 hr_utility.set_location('Commu type id'||l_triggers.cm_typ_id||' ' ||l_triggers.proc_cd,10);
4772 -- Step 4.
4773 --
4774 if rule_passes
4775 (p_rule_id => l_triggers.cm_typ_trgr_rl,
4776 p_person_id => p_person_id,
4777 p_assignment_id => l_ass_rec.assignment_id,
4778 p_business_group_id => p_business_group_id,
4779 p_organization_id => l_ass_rec.organization_id,
4780 p_communication_type_id => l_triggers.cm_typ_id,
4781 p_ler_id => p_ler_id,
4782 p_pgm_id => p_pgm_id,
4783 p_pl_id => p_pl_id,
4784 p_pl_typ_id => l_pl_typ_id,
4785 p_effective_date => l_effective_date)
4786 and rule_passes
4787 (p_rule_id => l_triggers.cm_typ_rl,
4788 p_person_id => p_person_id,
4789 p_assignment_id => l_ass_rec.assignment_id,
4790 p_business_group_id => p_business_group_id,
4791 p_organization_id => l_ass_rec.organization_id,
4792 p_communication_type_id => l_triggers.cm_typ_id,
4793 p_ler_id => p_ler_id,
4794 p_pgm_id => p_pgm_id,
4795 p_pl_id => p_pl_id,
4796 p_pl_typ_id => l_pl_typ_id,
4797 p_effective_date => l_effective_date) then
4798 --
4799 -- OK rule is fine!
4800 --
4801 if l_triggers.inspn_rqd_rl is not null then
4802 if rule_passes
4803 (p_rule_id => l_triggers.inspn_rqd_rl,
4804 p_person_id => p_person_id,
4805 p_assignment_id => l_ass_rec.assignment_id,
4806 p_business_group_id => p_business_group_id,
4807 p_organization_id => l_ass_rec.organization_id,
4808 p_communication_type_id => l_triggers.cm_typ_id,
4809 p_ler_id => p_ler_id,
4810 p_pgm_id => p_pgm_id,
4811 p_pl_id => p_pl_id,
4812 p_pl_typ_id => l_pl_typ_id,
4813 p_effective_date => l_effective_date) then
4814 --
4815 l_triggers.inspn_rqd_flag := 'Y';
4816 --
4817 else
4818 --
4819 l_triggers.inspn_rqd_flag := 'N';
4820 --
4821 end if;
4822 --
4823 end if;
4824 --
4825 l_rqstbl_untl_dt := null;
4826 --
4827 if l_triggers.proc_cd in ('HPADPNTLC', 'HPAPRTTDE') then
4828 --
4829 l_rqstbl_untl_dt := nvl(p_rqstbl_untl_dt,
4830 add_months(l_effective_date,24));
4831 --
4832 end if;
4833 --
4834 -- If the receipient code is null, then generate comm for the
4835 -- participant. If the value is not null, then generate related
4836 -- person's communications and do not generate any prtt. comm.
4837 --
4838 -- Additionally, generate Participant HIPAA comm. for participants
4839 -- only; and generate dependent comm. for dependents.
4840 --
4841 if (l_triggers.rcpent_cd is null and
4842 l_triggers.proc_cd <> 'HPADPNTLC') or
4843 l_triggers.proc_cd = 'HPAPRTTDE' then
4844 --
4845 -- Step 5.
4846 --
4847 pop_ben_per_cm_f
4848 (p_person_id => p_person_id,
4849 p_ler_id => p_ler_id,
4850 p_per_in_ler_id => l_per_in_ler_id,
4851 p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
4852 p_bnf_person_id => p_bnf_person_id,
4853 p_dpnt_person_id => p_dpnt_person_id,
4854 p_cm_typ_id => l_triggers.cm_typ_id,
4855 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
4856 p_rqstbl_untl_dt => l_rqstbl_untl_dt,
4857 p_business_group_id => p_business_group_id,
4858 p_effective_date => p_effective_date,
4859 p_date_cd => l_triggers.to_be_sent_dt_cd,
4860 p_formula_id => l_triggers.to_be_sent_dt_rl,
4861 p_pgm_id => p_pgm_id,
4862 p_pl_id => p_pl_id,
4863 p_per_cm_id => l_per_cm_id);
4864 hr_utility.set_location('Cm type id'||l_per_cm_id,10);
4865 --
4866 --
4867 -- We have to work out the usages and this depends on the proc_cd
4868 -- If we have no usages then there is no need to populate the
4869 -- other communication tables.
4870 --
4871 -- Step 6 and 7.
4872 --
4873 if(l_per_cm_id <> -1) then
4874 if not usages_exist
4875 (p_proc_cd => l_triggers.proc_cd,
4876 p_person_id => p_person_id,
4877 p_per_in_ler_id => l_per_in_ler_id,
4878 p_ler_id => p_ler_id,
4879 p_business_group_id => p_business_group_id,
4880 p_organization_id => l_ass_rec.organization_id,
4881 -- PB : 5422 :
4882 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4883 -- p_enrt_perd_id => p_enrt_perd_id,
4884 p_actn_typ_id => p_actn_typ_id,
4885 p_enrt_mthd_cd => p_enrt_mthd_cd,
4886 p_pgm_id => p_pgm_id,
4887 p_pl_id => p_pl_id,
4888 p_pl_typ_id => l_pl_typ_id,
4889 p_assignment_id => l_ass_rec.assignment_id,
4890 p_per_cm_id => l_per_cm_id,
4891 p_cm_typ_id => l_triggers.cm_typ_id,
4892 p_effective_date => p_effective_date,
4893 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
4894 p_whnvr_trgrd_flag => l_triggers.whnvr_trgrd_flag) then
4895 --
4896 -- We have to rollback the transaction
4897 --
4898 hr_utility.set_location('rolling back' ,10);
4899 rollback to communications_savepoint;
4900 --
4901 else
4902 --
4903 hr_utility.set_location('Cm type id'||l_per_cm_id,10);
4904 populate_working_tables
4905 (p_person_id => p_person_id,
4906 p_cm_typ_id => l_triggers.cm_typ_id,
4907 p_business_group_id => p_business_group_id,
4908 p_effective_date => p_effective_date,
4909 p_cm_trgr_id => l_triggers.cm_trgr_id,
4910 p_inspn_rqd_flag => l_triggers.inspn_rqd_flag,
4911 p_cm_dlvry_med_cd => l_triggers.cm_dlvry_med_typ_cd,
4912 p_cm_dlvry_mthd_cd => l_triggers.cm_dlvry_mthd_typ_cd,
4913 p_per_cm_id => l_per_cm_id,
4914 p_mode => p_mode);
4915 --
4916 fnd_message.set_name('BEN','BEN_92089_CREATED_PER_COMM');
4917 fnd_message.set_token('COMMUNICATION',l_triggers.name);
4918 if fnd_global.conc_request_id <> -1 then
4919 benutils.write(fnd_message.get);
4920 benutils.write(p_rec => g_commu_rec);
4921 g_comm_generated := true;
4922 end if;
4923 end if;
4924 --
4925 end if;
4926 --
4927 else
4928 --
4929 -- Generate related persons/dependents comm.
4930 --
4931 hr_utility.set_location('Entering dpnt',10);
4932 ben_generate_dpnt_comm.main
4933 (p_proc_cd => l_triggers.proc_cd,
4934 p_name => l_triggers.name,
4935 p_rcpent_cd => l_triggers.rcpent_cd,
4936 p_person_id => p_person_id,
4937 p_per_in_ler_id => l_per_in_ler_id,
4938 p_business_group_id => p_business_group_id,
4939 p_assignment_id => l_ass_rec.assignment_id,
4940 p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
4941 -- PB : 5422 :
4942 p_asnd_lf_evt_dt => p_asnd_lf_evt_dt,
4943 -- p_enrt_perd_id => p_enrt_perd_id,
4944 p_enrt_mthd_cd => p_enrt_mthd_cd,
4945 p_actn_typ_id => p_actn_typ_id,
4946 p_per_cm_id => l_per_cm_id,
4947 p_pgm_id => p_pgm_id,
4948 p_pl_id => p_pl_id,
4949 p_pl_typ_id => l_pl_typ_id,
4950 p_cm_typ_id => l_triggers.cm_typ_id,
4951 p_cm_trgr_id => l_triggers.cm_trgr_id,
4952 p_ler_id => p_ler_id,
4953 p_date_cd => l_triggers.to_be_sent_dt_cd,
4954 p_inspn_rqd_flag => l_triggers.inspn_rqd_flag,
4955 p_formula_id => l_triggers.to_be_sent_dt_rl,
4956 p_effective_date => p_effective_date,
4957 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
4958 p_rqstbl_untl_dt => l_rqstbl_untl_dt,
4959 p_cm_dlvry_med_cd => l_triggers.cm_dlvry_med_typ_cd,
4960 p_cm_dlvry_mthd_cd => l_triggers.cm_dlvry_mthd_typ_cd,
4961 p_whnvr_trgrd_flag => l_triggers.whnvr_trgrd_flag,
4962 p_source => p_source);
4963 --
4964 end if;
4965 --
4966 end if;
4967 --
4968 end loop;
4969 --
4970 close c_triggers;
4971 --
4972 hr_utility.set_location('Leaving: '||l_proc,10);
4973 --
4974 hr_utility.set_location('Displaying stats ',10);
4975 end main;
4976 --
4977 end ben_generate_communications;
4978 --