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