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