[Home] [Help]
PACKAGE BODY: APPS.BEN_COBRA_REQUIREMENTS
Source
1 package body ben_cobra_requirements as
2 /* $Header: bencobra.pkb 120.14.12020000.2 2012/09/25 17:08:37 stee ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1999 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name:
12 Determine COBRA requirements
13 Purpose
14 This package contains all the COBRA procedures that are
15 required by other processes to determine COBRA start and end
16 dates, qualified beneficiary status
17 History
18
19 Version Date Author Comments
20 -------+-----------+--------------+----------------------------------------
21 115.0 17-SEP-1999 stee Created
22 115.1 04-OCT-1999 stee Added pgm_typ_cd check to
23 end_prtt_cobra_eligibility.
24 115.2 04-OCT-1999 stee Fixed end_prtt_cobra_eligibility
25 to get the life event ocrd date
26 before checking if the person
27 is a quald bnf.
28 115.3 05-OCT-1999 stee For disability event, only extend the
29 eligibility end date if the calculated
30 date is great than the dependent
31 eligibility end date.
32 115.4 07-JAN-2000 pbodla - regn_id is added as context to mx_poe_rl
33 115.5 12-JAN-2000 stee - Fix update_cobra_info to write a qualified
34 beneficiary row when a dependent is
35 designated.
36 115.6 17-JAN-2000 stee - When checking for max cvg thru dt, limit it
37 to the COBRA program.
38 WWBUG# 1166171
39 115.7 30-JAN-2000 stee - If a person is does not need the
40 extension in a disability event as he/she
41 already has 36 months, update the
42 eligibility end date on pil_elctbl_chc_popl
43 in case they are allowed to make elections.
44 WWBUG# 11772229.
45 115.7 06-FEB-2000 stee - Check for backed out nocopy events.WWBUG#1178633.
46 115.8 01-MAR-2000 stee - COBRA by plan type.
47 115.10 06-MAR-2000 stee - Fix disability at time of life event dates.
48 115.11 30-MAR-2000 stee - Fix disability within 60 days of the life
49 event. Change update_dpnt_cobra_info to
50 not error out nocopy if the elig dates are null
51 as the dates are null for an open enrollment
52 event. WWBUG#: 1249902, 1147607, 1252082.
53 115.12 31-MAR-2000 stee - Fix to extend cobra eligibility end date
54 if it is greater than the current
55 eligibility end date.
56 Write cbr_per_in_ler for all qualified
57 beneficiary eligible for the disability
58 extension.
59 115.13 04-APR-2000 mmogel - Added tokens to messages to make them
60 more meaningful to the user
61 115.14 06-APR-2000 stee - When checking for min dates use null
62 instead of %notfound. Bug# 4956
63 115.15 28-APR-2000 stee - Update cobra ineligibility status when
64 prtt is no longer eligible or has waived
65 coverage. Also fix chk_enrld_or_cvrd
66 cursor to outer join to oipl table
67 if plan has no option.
68 115.16 17-May-2000 stee - Add pl_typ_id to the where clause when
69 selecting enrollment coverage end date
70 in determine_cobra_elig_dates for cobra
71 by plan type.
72 115.17 11-OCT-2000 rchase - added parameter pl typ id for
73 calls to formula as contexts.
74 115.18 20-OCT-2000 stee - When checking if a person is a cobra
75 qualified beneficiary, use elig_end_dt >
76 lf_evt_ocrd_dt as cobra start date can
77 be after the lf evt ocrd dt. WWBUG#1469388.
78 115.19 26-Oct-2000 rchase - fix wwbug 1480395 fetch pgm_id, if
79 necessary for formula context. Ensure the
80 proper rule is passed to the formula call
81 when determining mx poe date.
82 115.20 21-Mar-2001 ikasire bug 1566944 added ptip parameter and
83 edited c_get_max_poe cursor to see for
84 pgm_id or ptip_id
85 115.21 20-Aug-2001 stee Bug 1348235: Fix duplicate qual bnf row
86 when a dependent is added after the initial
87 qualifying event.
88 115.22 29-Aug-2001 pbodla bug:1949361 jurisdiction code is
89 derived inside benutils.formula
90 115.23 30-NOV-2001 stee Back out nocopy changes made in version 115.20.
91 The c_get_max_poe cursor may retrieve
92 the wrong period of enrollment if COBRA
93 by plan type is implemented. Also, for
94 subsequent events, it is not finding the
95 current qualified beneficiary row so a
96 duplicate one is created.
97 115.24 22-JAN-2002 stee If a person is no longer disabled, reduce
98 the max period of enrollment if applicable.
99 Bug# 2068332.
100 115.25 01-FEB-2002 stee Added dbdrv lines.
101 115.26 21-MAY-2002 stee Fix the cobra eligibility end date.
102 Bug# 2355218.
103 115.27 08-Jun-2002 pabodla - Do not select the contingent worker
104 assignment when assignment data is
105 fetched.
106 115.28 12-Aug-2002 stee Check if electable choices exist before
107 ending COBRA eligibility. Bug 1794808.
108 115.29 11-Sep-2002 stee Close c_get_ler_type cursor.
109 115.30 04-Nov-2002 stee For COBRA by plan type, check for enrollment
110 in the COBRA program instead of plan type
111 to determine eligibility. Bug 2626516.
112 115.31 23-DEC-2002 lakrish NOCOPY changes.
113 115.32 14-MAR-2003 stee When determining the enrollment coverage
114 start date for cobra eligibility start date,
115 use electable choices instead of enrollment
116 results. Bug 2821672 and 2815797.
117 115.33 13-Oct-2003 rpillay Bug 3097501 - Added procedures
118 allocate_payment, do_rounding and
119 get_amount_due
120 115.34 15-Oct-2003 rpillay Bug 3097501 - Added date check in
121 c_rates cursor (allocate_payment)
122 115.35 15-Oct-2003 rpillay Bug 3097501 - Changes to c_prev_pymts_latest
123 cursor (allocate_payment)
124 115.36 20-Oct-2003 rpillay Bug 3097501 - Changes to allocate_payments to
125 adjust payments against past and future rates
126 115.37 22-Oct-2003 rpillay Bug 3097501 - Changes to handle FSA balance
127 calculations when no change in amount
128 115.38 24-Oct-2003 rpillay Bug 3097501 - Changes to get_amount_due -
129 Using Rates in place of element balance
130 for FSA
131 115.39 18-Nov-2003 rpillay Bug 3097501 - Changes for not doing automatic
132 adjustments for plan year and element
133 changes
134 115.40 24-Nov-2003 rpillay Added nocopy to p_excess_amount
135 115.41 19-Dec-2003 rpillay Changes to ignore excess payments in
136 allocate_payments
137 115.42 22-Dec-2003 rpillay Changes to c_pen in allocate_payments to
138 check for coverage start and thru dates
139 115.43 05-Jan-2004 rpillay Bug 3338978 - added check for month_strt_dt
140 < rt_strt_dt in allocate_payments
141 115.44 13-Jan-2004 stee Remove ptip_id from the where clause in
142 c_get_enrt_cvg_thru_dt and
143 c_get_dpnt_cvg_thru_dt in the
144 get_max_cvg_thru_dt function. The eligibility
145 end date is the coverage end date of the
146 program. Bug 3368053.
147 115.45 15-Jan-2004 rpillay Moved code to fetch costing data to
148 get_costing_details procedure.
149 Pass cost_allocation_keyflex_id as NULL
150 while making payment adjustments to ensure
151 that costed values get assigned using the
152 Costing Hierarchy.
153 115.46 27-Sep-2004 tjesumic new param p_cvrd_today added in chk_enrld_or_cvrd # 3843549
154 coerage validation changes as per the param
155 115.47 04-jan-2005 ssarkar Bug# 3630753 : commented fnd_message.set_token('PROC',l_proc).
156 115.48 08-Sep-2005 stee If eligibility period end date is 01/01/0001, then
157 set the quald_bnf_flag = 'N' and leave the eligibility
158 period end date as is. Bug 4486609.
159 115.49 28-dec-2005 stee Only terminate cobra eligibility in chk_cobra_eligibility
160 if the person was previously enrolled in COBRA
161 benefits. Bug 4338471.
162 115.50 15-Feb-2005 bmanyam 4881917 PERF Fix: XBuild1 Drop
163 115.51 30-Jun-2006 swjain 5331889 Added person_id param to benutils.formula call in
164 115.52 08-Nov-2006 stee When creating quald_bnf for a dependent, get
165 the cvrd_emp_person_id with a person type
166 usage of 'PRTN'.
167 115.53 22-Feb-2008 rtagarra Bug 6840074
168 115.55 30-May-2008 velvanop Bug 7116537- Commented the p_effective_date condition of cursor c_get_enrt_cvg_thru_dt in
169 get_max_cvg_thru_dt function.
170 115.56 20-Feb-2009 velvanop Bug 8211414- Modified cursor c_get_quald_bnf. To determine QB, flag 'quald_bnf_flag' should be 'Y' .
171 Even though QB records exists, new QB records will be created for a LE only if the flag is set to 'N'.
172 115.57 28-Apr-2009 stee ARRA COBRA changes.
173 115.58 10-May-2009 stee ARRA COBRA changes - Create quald bnf
174 for dependent.
175 115.59 10-Feb-2009 stee Bug 9294528 - Check that a dependent does
176 not have a initial qualifying event
177 before adjusting their cobra end date.
178 115.60 09-Jun-2010 stee Bug 9690964 - If the covered employee id
179 is not found, get it from the prior
180 quald benefiaciary row.
181 115.61 02-May-2011 stee Bug 12386329 - Terminate COBRA eligibility for
182 covered dependents if the participant loses
183 eligibility.
184 115.62 28-Aug-2012 stee Bug 14332963 - Do not terminate the COBRA
185 eligibility for a covered dependent if the
186 there is a potential event for the dependent.
187 */
188 --------------------------------------------------------------------------------
189 g_package varchar2(80):='ben_cobra_requirements.';
190 --
191 --------------------------------------------------------------------------------
192 --
193 -- ----------------------------------------------------------------------------
194 -- |-------------------------< get_lf_evt_ocrd_dt >----------------------------
195 -- ----------------------------------------------------------------------------
196 --
197 function get_lf_evt_ocrd_dt
198 (p_per_in_ler_id in number
199 ,p_business_group_id in number) return date is
200 --
201 l_proc varchar2(80) := g_package||'.get_lf_evt_ocrd_dt';
202 l_lf_evt_ocrd_dt date;
203 l_exists varchar2(1);
204 --
205 cursor c_get_lf_evt_ocrd_dt
206 is
207 select pil.lf_evt_ocrd_dt
208 from ben_per_in_ler pil
209 where pil.per_in_ler_id = p_per_in_ler_id
210 and pil.business_group_id = p_business_group_id;
211 --
212 begin
213 --
214 hr_utility.set_location('Entering : ' || l_proc, 10);
215 --
216 open c_get_lf_evt_ocrd_dt;
217 fetch c_get_lf_evt_ocrd_dt into l_lf_evt_ocrd_dt;
218 close c_get_lf_evt_ocrd_dt;
219 --
220 return l_lf_evt_ocrd_dt;
221 --
222 end get_lf_evt_ocrd_dt;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |-------------------------< chk_init_evt >----------------------------------
226 -- ----------------------------------------------------------------------------
227 --
228 function chk_init_evt
229 (p_per_in_ler_id in number
230 ,p_business_group_id in number) return boolean is
231 --
232 l_proc varchar2(80) := g_package||'.chk_init_evt';
233 l_init_evt boolean := false;
234 l_exists varchar2(1);
235 --
236 cursor c_chk_init_evt
237 is
238 select null
239 from ben_cbr_per_in_ler crp
240 where crp.per_in_ler_id = p_per_in_ler_id
241 and crp.business_group_id = p_business_group_id
242 and crp.init_evt_flag = 'Y';
243 --
244 begin
245 --
246 hr_utility.set_location('Entering : ' || l_proc, 10);
247 --
248 open c_chk_init_evt;
249 fetch c_chk_init_evt into l_exists;
250 if c_chk_init_evt%found then
251 l_init_evt := true;
252 end if;
253 close c_chk_init_evt;
254 --
255 return l_init_evt;
256 --
257 end chk_init_evt;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |-------------------------< chk_dsbld>-------------------------------------
261 -- ----------------------------------------------------------------------------
262 --
263 function chk_dsbld
264 (p_person_id in number
265 ,p_lf_evt_ocrd_dt in date default null
266 ,p_effective_date in date
267 ,p_business_group_id in number) return boolean is
268 --
269 l_proc varchar2(80) := g_package||'.chk_dsbld';
270 l_dsbld boolean := false;
271 l_registered_disabled_flag per_all_people_f.registered_disabled_flag%type;
272 l_new_val ben_per_info_chg_cs_ler_f.new_val%type;
273 --
274 cursor c_chk_dsbld
275 is
276 select per.registered_disabled_flag
277 from per_all_people_f per
278 where per.person_id = p_person_id
279 and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
280 per.effective_start_date and per.effective_end_date
281 and per.business_group_id = p_business_group_id;
282 --
283 cursor c_chk_dsblty_criteria
284 is
285 select psl.new_val
286 from ben_per_info_chg_cs_ler_f psl
287 where psl.source_table = 'PER_ALL_PEOPLE_F'
288 and psl.source_column = 'REGISTERED_DISABLED_FLAG'
289 and nvl(p_lf_evt_ocrd_dt, p_effective_date)
290 between psl.effective_start_date and psl.effective_end_date
291 and psl.business_group_id = p_business_group_id;
292 --
293 begin
294 --
295 hr_utility.set_location('Entering : ' || l_proc, 10);
296 --
297 open c_chk_dsbld;
298 fetch c_chk_dsbld into l_registered_disabled_flag;
299 if c_chk_dsbld%found then
300 close c_chk_dsbld;
301 --
302 -- If person is disabled, check it meets the criteria
303 -- for the disability event.
304 --
305 if l_registered_disabled_flag is not null then
306 open c_chk_dsblty_criteria;
307 fetch c_chk_dsblty_criteria into l_new_val;
308 if c_chk_dsblty_criteria%found then
309 if l_new_val = 'OABANY' then
310 l_dsbld := true;
311 elsif l_new_val = l_registered_disabled_flag then
312 l_dsbld := true;
313 end if;
314 end if;
315 close c_chk_dsblty_criteria;
316 end if;
317 --
318 else
319 close c_chk_dsbld;
320 end if;
321 --
322 return l_dsbld;
323 --
324 end chk_dsbld;
325 -- ----------------------------------------------------------------------------
326 -- |-------------------------< get_dsblty_elig_perd_end_dt>--------------------
327 -- ----------------------------------------------------------------------------
328 --
329 function get_dsblty_elig_perd_end_dt
330 (p_person_id in number
331 ,p_pl_typ_id in number default null
332 ,p_lf_evt_ocrd_dt in date default null
333 ,p_cbr_elig_perd_strt_dt in date
334 ,p_pgm_id in number default null
335 ,p_ptip_id in number default null
336 ,p_effective_date in date
337 ,p_business_group_id in number) return date is
338 --
339 l_proc varchar2(80) := g_package||'.get_dsblty_elig_perd_end_dt';
340 l_dsblty_ler_id ben_per_in_ler.ler_id%type;
341 l_dsblty_elig_perd_end_dt date default null;
342 --
343 cursor c_get_dsblty_ler
344 is
345 select ler.ler_id
346 from ben_ler_f ler
347 where ler.typ_cd = 'DSBLTY'
348 and ler.business_group_id = p_business_group_id
349 and ler.qualg_evt_flag = 'Y'
350 and p_lf_evt_ocrd_dt
351 between ler.effective_start_date
352 and ler.effective_end_date;
353
354 --
355 cursor c_get_dsblty_max_poe is
356 select peo.*
357 from ben_elig_to_prte_rsn_f peo
358 where peo.ler_id = l_dsblty_ler_id
359 and peo.business_group_id = p_business_group_id
360 and nvl(p_lf_evt_ocrd_dt, p_effective_date)
361 between peo.effective_start_date and peo.effective_end_date
362 and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
363 and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
364 and (peo.mx_poe_val is not null or
365 peo.mx_poe_rl is not null);
366 --
367 l_poe_rec c_get_dsblty_max_poe%rowtype;
368 --
369 begin
370 --
371 hr_utility.set_location('Entering : ' || l_proc, 10);
372 --
373 open c_get_dsblty_ler;
374 fetch c_get_dsblty_ler into l_dsblty_ler_id;
375 if c_get_dsblty_ler%found then
376 hr_utility.set_location('Found disability event' , 10);
377 close c_get_dsblty_ler;
378 --
379 -- Get disability period of enrollment.
380 --
381 open c_get_dsblty_max_poe;
382 fetch c_get_dsblty_max_poe into l_poe_rec;
383 if c_get_dsblty_max_poe%found then
384 close c_get_dsblty_max_poe;
385 l_dsblty_elig_perd_end_dt
386 := get_cbr_elig_end_dt
387 (p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt
388 ,p_person_id => p_person_id
389 ,p_pl_typ_id => p_pl_typ_id
390 ,p_mx_poe_uom => l_poe_rec.mx_poe_uom
391 ,p_mx_poe_val => l_poe_rec.mx_poe_val
392 ,p_mx_poe_rl => l_poe_rec.mx_poe_rl
393 ,p_pgm_id => p_pgm_id
394 ,p_effective_date => p_lf_evt_ocrd_dt
395 ,p_business_group_id => p_business_group_id
396 ,p_ler_id => l_dsblty_ler_id
397 );
398 else
399 close c_get_dsblty_max_poe;
400 end if;
401 else
402 close c_get_dsblty_ler;
403 end if;
404 --
405 return l_dsblty_elig_perd_end_dt;
406 --
407 end get_dsblty_elig_perd_end_dt;
408 --
409 -- ----------------------------------------------------------------------------
410 -- |Determine Cobra eligibility start and end dates.
411 -- ----------------------------------------------------------------------------
412 --
413 procedure determine_cobra_elig_dates
414 (p_pgm_id in number default null
415 ,p_ptip_id in number default null
416 ,p_pl_typ_id in number default null
417 ,p_person_id in number
418 ,p_per_in_ler_id in number
419 ,p_lf_evt_ocrd_dt in date
420 ,p_business_group_id in number
421 ,p_effective_date in date
422 ,p_validate in boolean default false
423 ,p_cbr_elig_perd_strt_dt out nocopy date
424 ,p_cbr_elig_perd_end_dt out nocopy date
425 ,p_old_cbr_elig_perd_end_dt out nocopy date
426 ,p_cbr_quald_bnf_id out nocopy number
427 ,p_cqb_object_version_number out nocopy number
428 ,p_cvrd_emp_person_id out nocopy number
429 ,p_dsbld_apls out nocopy boolean
430 ,p_update out nocopy boolean
431 ) is
432 --
433 -- Declare cursors and local variables
434 --
435 l_det_end_date boolean := false;
436 l_enrt_cvg_strt_dt ben_elig_per_elctbl_chc.enrt_cvg_strt_dt%type;
437 l_dsblty_elig_perd_end_dt date;
438 l_cbr_elig_perd_end_dt date;
439 l_eligible boolean := false;
440 l_exists varchar2(1);
441 l_typ_cd ben_ler_f.typ_cd%type;
442 l_jurisdiction_code varchar2(30);
443 l_outputs ff_exec.outputs_t;
444 l_proc varchar2(72) := g_package||'determine_cobra_elig_dates';
445 --
446 cursor c_get_max_poe is
447 select peo.*
448 ,ler.typ_cd
449 ,pil.lf_evt_ocrd_dt
450 from ben_elig_to_prte_rsn_f peo
451 ,ben_per_in_ler pil
452 ,ben_ler_f ler
453 where pil.ler_id = peo.ler_id
454 and pil.per_in_ler_id = p_per_in_ler_id
455 and pil.business_group_id = p_business_group_id
456 and peo.business_group_id = pil.business_group_id
457 and nvl(p_lf_evt_ocrd_dt, p_effective_date)
458 between peo.effective_start_date and peo.effective_end_date
459 and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
460 and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
461 and (peo.mx_poe_val is not null or
462 peo.mx_poe_rl is not null)
463 and pil.ler_id = ler.ler_id
464 and nvl(p_lf_evt_ocrd_dt, p_effective_date)
465 between ler.effective_start_date and ler.effective_end_date
466 and ler.business_group_id = pil.business_group_id
467 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
468 --
469 cursor c_get_quald_bnf is
470 select cqb.*
471 from ben_cbr_quald_bnf cqb
472 ,ben_cbr_per_in_ler crp
473 ,ben_per_in_ler pil
474 where cqb.quald_bnf_person_id = p_person_id
475 and cqb.business_group_id = p_business_group_id
476 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
477 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
478 and crp.init_evt_flag = 'Y'
479 and cqb.quald_bnf_flag = 'Y' -- Bug 8211414
480 and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
481 and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
482 and crp.per_in_ler_id = pil.per_in_ler_id
483 and crp.business_group_id = cqb.business_group_id
484 and crp.business_group_id = pil.business_group_id
485 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
486 --
487 cursor c_get_enrt_cvg_strt_dt is
488 select min(epe.enrt_cvg_strt_dt)
489 from ben_elig_per_elctbl_chc epe
490 where epe.per_in_ler_id = p_per_in_ler_id
491 and epe.ptip_id = nvl(p_ptip_id, epe.ptip_id)
492 and epe.pgm_id = nvl(p_pgm_id, epe.pgm_id)
493 and epe.enrt_cvg_strt_dt is not null
494 and epe.elctbl_flag = 'Y'
495 and epe.business_group_id = p_business_group_id;
496 --
497 cursor c_regn is
498 select reg.regn_id
499 from ben_regn_f reg
500 where p_effective_date between
501 reg.effective_start_date and reg.effective_end_date
502 and reg.business_group_id = p_business_group_id
503 and reg.sttry_citn_name = 'COBRA';
504 --
505 l_regn_id number;
506 --
507 cursor c_state is
508 select loc.region_2,asg.assignment_id,asg.organization_id
509 from hr_locations_all loc,per_all_assignments_f asg
510 where loc.location_id(+) = asg.location_id
511 and asg.person_id = p_person_id
512 and asg.assignment_type <> 'C'
513 and asg.primary_flag = 'Y'
514 and p_effective_date
515 between asg.effective_start_date and asg.effective_end_date
516 and asg.business_group_id=p_business_group_id;
517 --
518 -- RCHASE wwbug 1480395 fetch pgm_id, if necessary for formula context
519 cursor c_pgm_ptip is
520 select pgm_id
521 from ben_ptip_f
522 where ptip_id = p_ptip_id
523 and p_effective_date between effective_start_date
524 and effective_end_date;
525 --
526 cursor c_get_enddsblty_ler is
527 select null
528 from ben_ler_f ler
529 ,ben_per_in_ler pil
530 where ler.ler_id = pil.ler_id
531 and pil.per_in_ler_id = p_per_in_ler_id
532 and ler.typ_cd = 'ENDDSBLTY'
533 and p_effective_date between
534 ler.effective_start_date and ler.effective_end_date;
535 --
536 cursor c_get_prvs_elig_end_dt(p_cbr_quald_bnf_id in number) is
537 select crp.prvs_elig_perd_end_dt
538 from ben_ler_f ler
539 ,ben_per_in_ler pil
540 ,ben_cbr_per_in_ler crp
541 where ler.ler_id = pil.ler_id
542 and pil.per_in_ler_id = crp.per_in_ler_id
543 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
544 and pil.business_group_id = p_business_group_id
545 and pil.business_group_id = ler.business_group_id
546 and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
547 and crp.business_group_id = ler.business_group_id
548 and ler.typ_cd = 'DSBLTY'
549 and crp.cnt_num = (select max(crp2.cnt_num)
550 from ben_cbr_per_in_ler crp2
551 ,ben_per_in_ler pil2
552 ,ben_ler_f ler2
553 where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
554 and crp2.business_group_id = pil2.business_group_id
555 and crp2.business_group_id = ler2.business_group_id
556 and crp2.business_group_id = p_business_group_id
557 and crp2.per_in_ler_id = pil2.per_in_ler_id
558 and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
559 and pil2.ler_id = ler2.ler_id
560 and ler2.qualg_evt_flag = 'Y')
561 and p_effective_date between
562 ler.effective_start_date and ler.effective_end_date;
563 --
564 cursor c_get_init_ler(p_cbr_quald_bnf_id in number) is
565 select peo.*
566 ,pil.lf_evt_ocrd_dt
567 from ben_ler_f ler
568 ,ben_per_in_ler pil
569 ,ben_cbr_per_in_ler crp
570 ,ben_elig_to_prte_rsn_f peo
571 where ler.ler_id = pil.ler_id
572 and pil.per_in_ler_id = crp.per_in_ler_id
573 and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
574 and pil.business_group_id = p_business_group_id
575 and crp.business_group_id = pil.business_group_id
576 and ler.business_group_id = pil.business_group_id
577 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
578 and crp.init_evt_flag = 'Y'
579 and pil.lf_evt_ocrd_dt
580 between peo.effective_start_date and peo.effective_end_date
581 and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
582 and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
583 and (peo.mx_poe_val is not null or
584 peo.mx_poe_rl is not null)
585 and pil.lf_evt_ocrd_dt
586 between ler.effective_start_date and ler.effective_end_date;
587 --
588 l_pgm_id number:=p_pgm_id;
589 -- RCHASE end
590 l_cqb_rec c_get_quald_bnf%rowtype;
591 l_poe_rec c_get_max_poe%rowtype;
592 l_poe2_rec c_get_init_ler%rowtype;
593 l_state_rec c_state%rowtype;
594 --
595 begin
596 --
597 hr_utility.set_location('Entering:'|| l_proc, 10);
598 hr_utility.set_location('p_pgm_id:'|| p_pgm_id, 10);
599 hr_utility.set_location('p_ptip_id:'|| p_ptip_id, 10);
600 p_dsbld_apls := false;
601 p_update := true;
602 --
603 -- Check if a maximum period of enrollment exists for the life event.
604 -- If there is no maximum period of enrollment, set the cobra eligibility
605 -- start and end dates to null.
606 --
607 open c_get_max_poe;
608 fetch c_get_max_poe into l_poe_rec;
609 if c_get_max_poe%found then
610 close c_get_max_poe;
611
612 --
613 -- Check if a qualified beneficiary exist for the person.
614 --
615 open c_get_quald_bnf;
616 fetch c_get_quald_bnf into l_cqb_rec;
617 --
618 if c_get_quald_bnf%notfound then
619 close c_get_quald_bnf;
620 --
621 -- Check if the max period of enrollment is relevant for the person.
622 -- i.e. do they have to be a dependent or spouse.
623 --
624 l_eligible := check_max_poe_eligibility
625 (p_person_id => p_person_id
626 ,p_mx_poe_apls_cd => l_poe_rec.mx_poe_apls_cd
627 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
628 ,p_business_group_id => p_business_group_id
629 );
630 if l_eligible then
631 --
632 -- Calculate start and end dates.
633 --
634 hr_utility.set_location('mx_poe_det_dt_cd:'|| l_poe_rec.mx_poe_det_dt_cd, 10);
635 if (l_poe_rec.mx_poe_det_dt_cd = 'CBRQED'
636 or l_poe_rec.mx_poe_det_dt_cd is null) then
637 --
638 hr_utility.set_location('p_lf_evt_ocrd_dt:'|| p_lf_evt_ocrd_dt, 10);
639 p_cbr_elig_perd_strt_dt := p_lf_evt_ocrd_dt;
640 --
641 elsif l_poe_rec.mx_poe_det_dt_cd = 'ECSD' then
642 --
643 open c_get_enrt_cvg_strt_dt;
644 fetch c_get_enrt_cvg_strt_dt into l_enrt_cvg_strt_dt;
645 if l_enrt_cvg_strt_dt is null then
646 close c_get_enrt_cvg_strt_dt;
647 --
648 hr_utility.set_location('Person ID:'|| p_person_id, 10);
649 hr_utility.set_location('per_in_ler :'|| p_per_in_ler_id, 10);
650 hr_utility.set_location('business_group_id :'|| p_business_group_id, 10);
651 --
652 -- Problem with eligibility setup. The person has to be
653 -- previously enrolled to be eligible for the COBRA program.
654 --
655 fnd_message.set_name('BEN','BEN_92426_CVG_THRU_DT_NOT_FND');
656 fnd_message.set_token('PROC',l_proc);
657 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
658 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
659 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
660 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
661 fnd_message.raise_error;
662 else
663 close c_get_enrt_cvg_strt_dt;
664 p_cbr_elig_perd_strt_dt := l_enrt_cvg_strt_dt;
665 hr_utility.set_location('l_enrt_cvg_strt_dt prtn:'|| l_enrt_cvg_strt_dt, 10);
666 end if;
667 elsif l_poe_rec.mx_poe_det_dt_cd = 'RL' then
668 --
669 -- Get the location info for rule context.
670 --
671 open c_state;
672 fetch c_state into l_state_rec;
673 close c_state;
674 /*
675 if l_state_rec.region_2 is not null then
676 l_jurisdiction_code :=
677 pay_mag_utils.lookup_jurisdiction_code
678 (p_state => l_state_rec.region_2);
679 end if;
680 */
681 --
682 open c_regn;
683 fetch c_regn into l_regn_id;
684 close c_regn;
685 --
686 -- RCHASE wwbug 1480395 fetch pgm_id, if necessary for formula context
687 if l_pgm_id is null then
688 open c_pgm_ptip;
689 fetch c_pgm_ptip into l_pgm_id;
690 close c_pgm_ptip;
691 end if;
692 --
693 l_outputs :=
694 benutils.formula
695 -- RCHASE wwbug 1480395 pass mx_poe_det_dt_rl
696 (p_formula_id => l_poe_rec.mx_poe_det_dt_rl, --l_poe_rec.mx_poe_rl,
697 p_effective_date => p_lf_evt_ocrd_dt,
698 p_assignment_id => l_state_rec.assignment_id,
699 p_organization_id => l_state_rec.organization_id,
700 p_business_group_id => p_business_group_id,
701 p_pgm_id => l_pgm_id,--p_pgm_id,
702 p_ler_id => l_poe_rec.ler_id,
703 p_regn_id => l_regn_id,
704 p_jurisdiction_code => l_jurisdiction_code,
705 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
706 p_param1_value => to_char(p_person_id));
707 --
708 -- RCHASE end
709 p_cbr_elig_perd_strt_dt :=
710 fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
711 --
712 end if;
713 --
714 -- Calculate the COBRA end date.
715 --
716 p_cbr_elig_perd_end_dt
717 := get_cbr_elig_end_dt
718 (p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt
719 ,p_person_id => p_person_id
720 ,p_pl_typ_id => p_pl_typ_id
721 ,p_mx_poe_uom => l_poe_rec.mx_poe_uom
722 ,p_mx_poe_val => l_poe_rec.mx_poe_val
723 ,p_mx_poe_rl => l_poe_rec.mx_poe_rl
724 ,p_pgm_id => p_pgm_id
725 ,p_effective_date => p_lf_evt_ocrd_dt
726 ,p_business_group_id => p_business_group_id
727 ,p_ler_id => l_poe_rec.ler_id
728 );
729 --
730 -- If person is disabled at the time of the qualifying event,
731 -- extend the eligibility end date if the disability extension
732 -- date is greater than the eligibility end date for the event.
733 -- For example: If life event is termination i.e. 18 months,
734 -- extend the cobra eligibility end date since disability is
735 -- typically 29 months. If life event is divorce i.e. 36 months
736 -- there is no change to the cobra eligibility end date.
737 --
738 if chk_dsbld(p_person_id => p_person_id
739 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
740 ,p_effective_date => p_effective_date
741 ,p_business_group_id => p_business_group_id) then
742 --
743 -- Get disability eligibility end date.
744 --
745 l_dsblty_elig_perd_end_dt
746 := get_dsblty_elig_perd_end_dt
747 (p_person_id => p_person_id
748 ,p_pl_typ_id => p_pl_typ_id
749 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
750 ,p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt
751 ,p_pgm_id => p_pgm_id
752 ,p_ptip_id => p_ptip_id
753 ,p_effective_date => p_effective_date
754 ,p_business_group_id => p_business_group_id);
755 --
756 if l_dsblty_elig_perd_end_dt > p_cbr_elig_perd_end_dt then
757 p_cbr_elig_perd_end_dt := l_dsblty_elig_perd_end_dt;
758 --
759 p_dsbld_apls := true;
760 end if;
761 end if;
762 else
763 p_cbr_elig_perd_strt_dt := null;
764 p_cbr_elig_perd_end_dt := null;
765 end if;
766 --
767 p_old_cbr_elig_perd_end_dt := null;
768 p_cbr_quald_bnf_id := null;
769 p_cqb_object_version_number := null;
770 p_cvrd_emp_person_id := null;
771 else
772 close c_get_quald_bnf;
773 p_cbr_elig_perd_strt_dt := l_cqb_rec.cbr_elig_perd_strt_dt;
774 p_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
775 p_old_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
776 p_cbr_quald_bnf_id := l_cqb_rec.cbr_quald_bnf_id;
777 p_cqb_object_version_number := l_cqb_rec.object_version_number;
778 p_cvrd_emp_person_id := l_cqb_rec.cvrd_emp_person_id;
779 --
780 -- If it is not a disability event or the person was disabled after 60
781 -- days then calculate the dates.
782 --
783 if (l_poe_rec.typ_cd <> 'DSBLTY' or
784 (l_poe_rec.typ_cd = 'DSBLTY' and
785 (p_lf_evt_ocrd_dt - l_cqb_rec.cbr_elig_perd_strt_dt) <= 60)) then
786 --
787 -- If person is a COBRA beneficiary, then check if the max poe
788 -- only applies to dependents of the covered employee.
789 --
790 --
791 -- Check if the max period of enrollment is relevant for the person.
792 -- i.e. do they have to be a dependent or spouse.
793 --
794 l_eligible := check_max_poe_eligibility
795 (p_person_id => p_person_id
796 ,p_mx_poe_apls_cd => l_poe_rec.mx_poe_apls_cd
797 ,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
798 ,p_quald_bnf_person_id => l_cqb_rec.quald_bnf_person_id
799 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
800 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
801 ,p_business_group_id => p_business_group_id
802 );
803 if l_eligible then
804 --
805 -- Redetermine COBRA end date.
806 --
807 p_cbr_elig_perd_end_dt
808 := get_cbr_elig_end_dt
809 (p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt
810 ,p_person_id => p_person_id
811 ,p_pl_typ_id => p_pl_typ_id
812 ,p_mx_poe_uom => l_poe_rec.mx_poe_uom
813 ,p_mx_poe_val => l_poe_rec.mx_poe_val
814 ,p_mx_poe_rl => l_poe_rec.mx_poe_rl
815 ,p_pgm_id => p_pgm_id
816 ,p_effective_date => p_lf_evt_ocrd_dt
817 ,p_business_group_id => p_business_group_id
818 ,p_ler_id => l_poe_rec.ler_id
819 );
820 --
821 -- If it is the initial event, check if person was disable
822 -- at the time of the qualifying event.
823 --
824 if chk_init_evt(p_per_in_ler_id => p_per_in_ler_id
825 ,p_business_group_id => p_business_group_id) then
826 --
827 if chk_dsbld(p_person_id => p_person_id
828 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
829 ,p_effective_date => p_effective_date
830 ,p_business_group_id => p_business_group_id) then
831 --
832 -- Get disability eligibility end date.
833 --
834 l_dsblty_elig_perd_end_dt
835 := get_dsblty_elig_perd_end_dt
836 (p_person_id => p_person_id
837 ,p_pl_typ_id => p_pl_typ_id
838 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
839 ,p_cbr_elig_perd_strt_dt => p_lf_evt_ocrd_dt
840 ,p_pgm_id => p_pgm_id
841 ,p_ptip_id => p_ptip_id
842 ,p_effective_date => p_effective_date
843 ,p_business_group_id => p_business_group_id);
844 --
845 if l_dsblty_elig_perd_end_dt > p_cbr_elig_perd_end_dt then
846 p_cbr_elig_perd_end_dt := l_dsblty_elig_perd_end_dt;
847 --
848 p_dsbld_apls := true;
849 end if;
850 end if;
851 else -- not initial event.
852 --
853 -- If the calculated date is greater that the current
854 -- eligibility end date.
855 --
856 if p_cbr_elig_perd_end_dt <> l_cqb_rec.cbr_elig_perd_end_dt then
857 --
858 -- Check if life event type is disability. If it is check
859 -- if the life event ocurred date is within the first 60 days
860 -- of the initial qualifying event. If it is within the first
861 -- 60 days, then extend the COBRA start and end for all person
862 -- with the same covered employee id.
863 --
864 if l_poe_rec.typ_cd = 'DSBLTY'then
865 if p_cbr_elig_perd_end_dt > l_cqb_rec.cbr_elig_perd_end_dt then
866 p_dsbld_apls := true;
867 else
868 --
869 -- Person's current eligibility date is greater than the
870 -- disability extension date.
871 --
872 p_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
873 end if;
874 end if;
875 --
876 end if;
877 end if;
878 else
879 p_update := false;
880 end if;
881 else
882 p_update := false;
883 end if;
884 end if;
885 else
886 close c_get_max_poe;
887 --
888 p_cbr_elig_perd_strt_dt := null;
889 p_cbr_elig_perd_end_dt := null;
890 p_old_cbr_elig_perd_end_dt := null;
891 p_cbr_quald_bnf_id := null;
892 p_cqb_object_version_number := null;
893 p_cvrd_emp_person_id := null;
894 --
895 -- If it is an end of disability event and a qualified beneficiary row
896 -- exists, re-instate the previous cobra eligibility end date
897 -- (the event prior to disability or the initial qualifying event)
898 -- as the person is no longer eligible for the COBRA extension.
899 -- If the life event occurred date is greater that the previous cobra
900 -- eligibility date, then set the eligibility end date to the life event
901 -- occurred date.
902 --
903 open c_get_enddsblty_ler;
904 fetch c_get_enddsblty_ler into l_exists;
905 if c_get_enddsblty_ler%found then
906 --
907 -- Get the qualified beneficiary row.
908 --
909 open c_get_quald_bnf;
910 fetch c_get_quald_bnf into l_cqb_rec;
911 if c_get_quald_bnf%found then
912 p_cbr_elig_perd_strt_dt := l_cqb_rec.cbr_elig_perd_strt_dt;
913 p_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
914 p_old_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
915 p_cbr_quald_bnf_id := l_cqb_rec.cbr_quald_bnf_id;
916 p_cqb_object_version_number := l_cqb_rec.object_version_number;
917 p_cvrd_emp_person_id := l_cqb_rec.cvrd_emp_person_id;
918 --
919 -- The last qualifying event has to be a disability event
920 -- or the person was disabled at the time of the initial
921 -- qualifying event.
922 --
923 open c_get_prvs_elig_end_dt(l_cqb_rec.cbr_quald_bnf_id);
924 fetch c_get_prvs_elig_end_dt into l_cbr_elig_perd_end_dt;
925 if c_get_prvs_elig_end_dt%found then
926 if p_lf_evt_ocrd_dt > l_cbr_elig_perd_end_dt then
927 p_cbr_elig_perd_end_dt := p_lf_evt_ocrd_dt;
928 else
929 p_cbr_elig_perd_end_dt := l_cbr_elig_perd_end_dt;
930 end if;
931 else
932 open c_get_init_ler(l_cqb_rec.cbr_quald_bnf_id);
933 fetch c_get_init_ler into l_poe2_rec;
934 if c_get_init_ler%found then
935 --
936 -- Check if person was disabled at the time of
937 -- the initial qualifying event.
938 --
939 if chk_dsbld
940 (p_person_id => p_person_id
941 ,p_lf_evt_ocrd_dt => l_poe2_rec.lf_evt_ocrd_dt
942 ,p_effective_date => p_effective_date
943 ,p_business_group_id => p_business_group_id) then
944 --
945 -- Calculate the initial qualifying event eligibility
946 -- end date.
947 --
948 p_cbr_elig_perd_end_dt
949 := get_cbr_elig_end_dt
950 (p_cbr_elig_perd_strt_dt => l_cqb_rec.cbr_elig_perd_strt_dt
951 ,p_person_id => p_person_id
952 ,p_pl_typ_id => p_pl_typ_id
953 ,p_mx_poe_uom => l_poe2_rec.mx_poe_uom
954 ,p_mx_poe_val => l_poe2_rec.mx_poe_val
955 ,p_mx_poe_rl => l_poe2_rec.mx_poe_rl
956 ,p_pgm_id => p_pgm_id
957 ,p_effective_date => l_poe2_rec.lf_evt_ocrd_dt
958 ,p_business_group_id => p_business_group_id
959 ,p_ler_id => l_poe2_rec.ler_id
960 );
961 if p_lf_evt_ocrd_dt > p_cbr_elig_perd_end_dt then
962 p_cbr_elig_perd_end_dt := p_lf_evt_ocrd_dt;
963 end if;
964 else
965 p_cbr_elig_perd_strt_dt := null;
966 p_cbr_elig_perd_end_dt := null;
967 p_old_cbr_elig_perd_end_dt := null;
968 p_cbr_quald_bnf_id := null;
969 p_cqb_object_version_number := null;
970 p_cvrd_emp_person_id := null;
971 --
972 -- Person was not disabled at the time of the qualifying
973 -- event so the end of disabity event is not valid.
974 --
975 fnd_message.set_name('BEN','BEN_92970_CBR_PER_NOT_DSBLD');
976 fnd_message.set_token('PROC',l_proc);
977 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
978 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
979 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
980 if fnd_global.conc_request_id <> -1 then
981 benutils.write(fnd_message.get);
982 end if;
983 end if;
984 --
985 else
986 p_cbr_elig_perd_strt_dt := null;
987 p_cbr_elig_perd_end_dt := null;
988 p_old_cbr_elig_perd_end_dt := null;
989 p_cbr_quald_bnf_id := null;
990 p_cqb_object_version_number := null;
991 p_cvrd_emp_person_id := null;
992 fnd_message.set_name('BEN','BEN_92970_CBR_PER_NOT_DSBLD');
993 fnd_message.set_token('PROC',l_proc);
994 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
995 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
996 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
997 if fnd_global.conc_request_id <> -1 then
998 benutils.write(fnd_message.get);
999 end if;
1000 end if;
1001 close c_get_init_ler;
1002 --
1003 end if;
1004 --
1005 close c_get_prvs_elig_end_dt;
1006 --
1007 end if;
1008 close c_get_quald_bnf;
1009 --
1010 end if;
1011 close c_get_enddsblty_ler;
1012 --
1013 end if;
1014 --
1015 hr_utility.set_location(' Leaving:'||l_proc, 70);
1016 --
1017 end determine_cobra_elig_dates;
1018 --
1019 -- ----------------------------------------------------------------------------
1020 -- |-------------------------< get_cbr_elig_end_dt >-- ------------------------
1021 -- ----------------------------------------------------------------------------
1022 --
1023 function get_cbr_elig_end_dt
1024 (p_cbr_elig_perd_strt_dt in date
1025 ,p_person_id in number
1026 ,p_pl_typ_id in number default null
1027 ,p_mx_poe_uom in varchar2
1028 ,p_mx_poe_val in number
1029 ,p_mx_poe_rl in number
1030 ,p_pgm_id in number
1031 ,p_effective_date in date
1032 ,p_business_group_id in number
1033 ,p_ler_id in number) return date is
1034 --
1035 l_proc varchar2(80) := g_package||'.get_cbr_elig_end_dt';
1036 l_outputs ff_exec.outputs_t;
1037 l_return_date date;
1038 l_jurisdiction_code varchar2(30);
1039 --
1040 cursor c_state is
1041 select loc.region_2,asg.assignment_id,asg.organization_id
1042 from hr_locations_all loc,per_all_assignments_f asg
1043 where loc.location_id(+) = asg.location_id
1044 and asg.assignment_type <> 'C'
1045 and asg.person_id = p_person_id
1046 and asg.primary_flag = 'Y'
1047 and p_effective_date
1048 between asg.effective_start_date and asg.effective_end_date
1049 and asg.business_group_id=p_business_group_id;
1050 --
1051 l_state_rec c_state%rowtype;
1052 --
1053 cursor c_regn is
1054 select reg.regn_id
1055 from ben_regn_f reg
1056 where p_effective_date between
1057 reg.effective_start_date and reg.effective_end_date
1058 and reg.business_group_id = p_business_group_id
1059 and reg.sttry_citn_name = 'COBRA';
1060 --
1061 l_regn_id number;
1062 --
1063 begin
1064 --
1065 hr_utility.set_location('Entering : ' || l_proc, 10);
1066 --
1067 if p_mx_poe_rl is not null then
1068 --
1069 -- Get the location info for rule context.
1070 --
1071 open c_state;
1072 fetch c_state into l_state_rec;
1073 close c_state;
1074 /*
1075 if l_state_rec.region_2 is not null then
1076 l_jurisdiction_code :=
1077 pay_mag_utils.lookup_jurisdiction_code
1078 (p_state => l_state_rec.region_2);
1079 end if;
1080 */
1081 --
1082 open c_regn;
1083 fetch c_regn into l_regn_id;
1084 close c_regn;
1085 --
1086 l_outputs :=
1087 benutils.formula
1088 (p_formula_id => p_mx_poe_rl,
1089 p_effective_date => p_effective_date,
1090 p_assignment_id => l_state_rec.assignment_id,
1091 p_organization_id => l_state_rec.organization_id,
1092 p_business_group_id => p_business_group_id,
1093 p_pgm_id => p_pgm_id,
1094 p_pl_typ_id => p_pl_typ_id,
1095 p_ler_id => p_ler_id,
1096 p_regn_id => l_regn_id,
1097 p_jurisdiction_code => l_jurisdiction_code,
1098 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
1099 p_param1_value => to_char(p_person_id));
1100 --
1101 l_return_date
1102 := fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
1103 else
1104 --
1105 hr_utility.set_location('p_cbr_elig_perd_strt_dt : ' || p_cbr_elig_perd_strt_dt, 10);
1106 hr_utility.set_location('p_mx_poe_uom : ' || p_mx_poe_uom, 10);
1107 hr_utility.set_location('p_mx_poe_val : ' || p_mx_poe_val, 10);
1108 l_return_date := benutils.derive_date
1109 (p_date => p_cbr_elig_perd_strt_dt
1110 ,p_uom => p_mx_poe_uom
1111 ,p_min => null
1112 ,p_max => p_mx_poe_val
1113 ,p_value => null
1114 ) - 1;
1115 end if;
1116 --
1117 hr_utility.set_location('l_return_date : ' || l_return_date, 10);
1118 hr_utility.set_location('Leaving : ' || l_proc, 10);
1119 return l_return_date;
1120 end get_cbr_elig_end_dt;
1121 --
1122 -- ----------------------------------------------------------------------------
1123 -- |-------------------------< chk_pgm_typ >-----------------------------------
1124 -- ----------------------------------------------------------------------------
1125 --
1126 function chk_pgm_typ
1127 (p_pgm_id in number
1128 ,p_effective_date in date
1129 ,p_business_group_id in number) return boolean is
1130 --
1131 l_proc varchar2(80) := g_package||'.chk_pgm_typ';
1132 l_exists varchar2(1);
1133 l_update boolean := false;
1134 --
1135 cursor c_chk_pgm_typ is
1136 select null
1137 from ben_pgm_f pgm
1138 where pgm.pgm_id = p_pgm_id
1139 and pgm.pgm_typ_cd like 'COBRA%'
1140 and p_effective_date
1141 between pgm.effective_start_date and pgm.effective_end_date
1142 and pgm.business_group_id=p_business_group_id;
1143 --
1144 begin
1145 --
1146 hr_utility.set_location('Entering : ' || l_proc, 10);
1147 --
1148 open c_chk_pgm_typ;
1149 fetch c_chk_pgm_typ into l_exists;
1150 if c_chk_pgm_typ%found then
1151 l_update := true;
1152 end if;
1153 close c_chk_pgm_typ;
1154 --
1155 return l_update;
1156 hr_utility.set_location('Leaving : ' || l_proc, 10);
1157 --
1158 end chk_pgm_typ;
1159 -- ----------------------------------------------------------------------------
1160 -- |-------------------------< chk_enrld_or_cvrd >-----------------------------
1161 -- ----------------------------------------------------------------------------
1162 --
1163 function chk_enrld_or_cvrd
1164 (p_pgm_id in number default null
1165 ,p_ptip_id in number default null
1166 ,p_person_id in number
1167 ,p_effective_date in date
1168 ,p_business_group_id in number
1169 ,p_cvrd_today in varchar2 default null)
1170 return boolean is
1171
1172 --
1173 l_proc varchar2(80) := g_package||'.chk_enrld_or_cvrd';
1174 l_exists varchar2(1);
1175 l_enrld_or_cvrd boolean := false;
1176 --
1177 cursor c_chk_enrld is
1178 select null
1179 from ben_prtt_enrt_rslt_f pen
1180 ,ben_pl_f pln
1181 ,ben_oipl_f cop
1182 ,ben_opt_f opt
1183 where pen.person_id = p_person_id
1184 and pen.prtt_enrt_rslt_stat_cd is null
1185 and pen.pgm_id = nvl(p_pgm_id, pen.pgm_id)
1186 -- and pen.ptip_id = nvl(p_ptip_id, pen.ptip_id)
1187 and pen.sspndd_flag = 'N'
1188 ---
1189 and
1190 ( (
1191 nvl(p_cvrd_today,'N') = 'N'
1192 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1193 and pen.effective_end_date = hr_api.g_eot
1194 and p_effective_date between pen.effective_start_date
1195 and pen.effective_end_date
1196 ) OR
1197 (
1198 nvl(p_cvrd_today,'N') = 'Y'
1199 AND pen.effective_end_date = hr_api.g_eot
1200 AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt
1201 AND pen.enrt_cvg_thru_dt
1202 AND pen.enrt_cvg_strt_dt < pen.effective_end_date
1203 )
1204 )
1205 and pen.business_group_id = p_business_group_id
1206 and pen.pl_id = pln.pl_id
1207 and pln.invk_dcln_prtn_pl_flag = 'N'
1208 and p_effective_date between pln.effective_start_date
1209 and pln.effective_end_date
1210 and pln.business_group_id = pen.business_group_id
1211 and pen.oipl_id = cop.oipl_id (+)
1212 and pen.business_group_id = cop.business_group_id (+)
1213 and p_effective_date between
1214 cop.effective_start_date (+)
1215 and cop.effective_end_date (+)
1216 and cop.opt_id = opt.opt_id (+)
1217 and nvl(opt.invk_wv_opt_flag,'N') = 'N'
1218 and cop.business_group_id = opt.business_group_id (+)
1219 and p_effective_date between
1220 opt.effective_start_date (+)
1221 and opt.effective_end_date (+);
1222 --
1223 cursor c_chk_cvrd is
1224 select null
1225 from ben_prtt_enrt_rslt_f pen
1226 ,ben_elig_cvrd_dpnt_f pdp
1227 where pdp.dpnt_person_id = p_person_id
1228 and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1229 and pen.prtt_enrt_rslt_stat_cd is null
1230 and pen.pgm_id = nvl(p_pgm_id, pen.pgm_id)
1231 -- and pen.ptip_id = nvl(p_ptip_id, pen.ptip_id)
1232 and pen.sspndd_flag = 'N'
1233 ---
1234 and
1235 ( (
1236 nvl(p_cvrd_today,'N') = 'N'
1237 and pdp.cvg_thru_dt = hr_api.g_eot
1238 and pdp.cvg_thru_dt <= pdp.effective_end_date
1239 and p_effective_date between pdp.effective_start_date
1240 and pdp.effective_end_date
1241 ) OR
1242 (
1243 nvl(p_cvrd_today,'N') = 'Y'
1244 AND pdp.effective_end_date = hr_api.g_eot
1245 AND p_effective_date BETWEEN pdp.cvg_strt_dt
1246 AND pdp.cvg_thru_dt
1247 AND pdp.cvg_strt_dt < pdp.effective_end_date
1248 )
1249 )
1250 --and pen.effective_end_date = hr_api.g_eot
1251 and p_effective_date between pen.effective_start_date
1252 and pen.effective_end_date
1253 and pen.business_group_id = p_business_group_id
1254 and pdp.business_group_id = pen.business_group_id;
1255
1256 --
1257 begin
1258 --
1259 hr_utility.set_location('Entering : ' || l_proc, 10);
1260 --
1261 -- Check if enrolled.
1262 --
1263 open c_chk_enrld;
1264 fetch c_chk_enrld into l_exists;
1265 if c_chk_enrld%found then
1266 l_enrld_or_cvrd := true;
1267 hr_utility.set_location('Enrolled', 10);
1268 else
1269 --
1270 -- Check if covered.
1271 --
1272 open c_chk_cvrd;
1273 fetch c_chk_cvrd into l_exists;
1274 if c_chk_cvrd%found then
1275 l_enrld_or_cvrd:= true;
1276 end if;
1277 close c_chk_cvrd;
1278 end if;
1279 close c_chk_enrld;
1280 --
1281 hr_utility.set_location('Leaving : ' || l_proc, 10);
1282 return l_enrld_or_cvrd;
1283 --
1284 end chk_enrld_or_cvrd;
1285 -- ----------------------------------------------------------------------------
1286 -- |-------------------------< get_max_cvg_thru_dt >------------------------------
1287 -- ----------------------------------------------------------------------------
1288 --
1289 function get_max_cvg_thru_dt
1290 (p_person_id in number
1291 ,p_lf_evt_ocrd_dt in date
1292 ,p_pgm_id in number default null
1293 ,p_ptip_id in number default null
1294 ,p_per_in_ler_id in number
1295 ,p_effective_date in date
1296 ,p_business_group_id in number) return date is
1297 --
1298 l_proc varchar2(80) := g_package||'.get_max_enrt_cvg_thru_dt';
1299 l_exists varchar2(1);
1300 l_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1301 l_enrt_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1302 l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
1303 --
1304 cursor c_get_enrt_cvg_thru_dt is
1305 select max(pen.enrt_cvg_thru_dt)
1306 from ben_prtt_enrt_rslt_f pen
1307 where pen.person_id = p_person_id
1308 and pen.prtt_enrt_rslt_stat_cd is null
1309 and pen.sspndd_flag = 'N'
1310 and pen.pgm_id = nvl(p_pgm_id,pen.pgm_id)
1311 --
1312 -- Bug 3368053: Remove ptip_id
1313 -- and pen.ptip_id = nvl(p_ptip_id,pen.ptip_id)
1314 and pen.prtt_enrt_rslt_stat_cd is null
1315 and nvl(pen.per_in_ler_id,-1) = nvl(p_per_in_ler_id, -1)
1316 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
1317 and pen.effective_end_date = hr_api.g_eot
1318 -- Bug 7116537, Commented the condition p_effective_date between effective_start_date and effective_end_date
1319 /*and p_effective_date between
1320 pen.effective_start_date and pen.effective_end_date*/
1321 and pen.business_group_id = p_business_group_id;
1322 --
1323 cursor c_get_dpnt_cvg_thru_dt is
1324 select max(pdp.cvg_thru_dt)
1325 from ben_elig_cvrd_dpnt_f pdp
1326 ,ben_prtt_enrt_rslt_f pen
1327 where pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1328 and pen.prtt_enrt_rslt_stat_cd is null
1329 and pdp.dpnt_person_id = p_person_id
1330 and pen.pgm_id = p_pgm_id
1331 --
1332 -- Bug 3368053: Remove ptip_id
1333 -- and pen.ptip_id = nvl(p_ptip_id,pen.ptip_id)
1334 and p_effective_date between pen.effective_start_date
1335 and pen.effective_end_date
1336 and pen.business_group_id = p_business_group_id
1337 and pdp.cvg_thru_dt <> hr_api.g_eot
1338 and p_lf_evt_ocrd_dt >= pdp.cvg_strt_dt
1339 and pdp.effective_end_date = hr_api.g_eot
1340 and p_effective_date between pdp.effective_start_date
1341 and pdp.effective_end_date
1342 and pdp.business_group_id = pen.business_group_id
1343 group by pdp.dpnt_person_id;
1344 --
1345 begin
1346 --
1347 hr_utility.set_location('Entering : ' || l_proc, 10);
1348 hr_utility.set_location('p_pgm_id : ' || p_pgm_id, 10);
1349 hr_utility.set_location('p_ptip_id : ' || p_ptip_id, 10);
1350 hr_utility.set_location('p_person_id : ' || p_person_id, 10);
1351 --
1352 open c_get_enrt_cvg_thru_dt;
1353 fetch c_get_enrt_cvg_thru_dt into l_enrt_cvg_thru_dt;
1354 close c_get_enrt_cvg_thru_dt;
1355 --
1356 -- Check if person is a covered dependent.
1357 --
1358 open c_get_dpnt_cvg_thru_dt;
1359 fetch c_get_dpnt_cvg_thru_dt into l_dpnt_cvg_thru_dt;
1360 close c_get_dpnt_cvg_thru_dt;
1361 --
1362 l_cvg_thru_dt := greatest(nvl(l_enrt_cvg_thru_dt, hr_api.g_sot)
1363 ,nvl(l_dpnt_cvg_thru_dt,hr_api.g_sot));
1364 return l_cvg_thru_dt;
1365 --
1366 end get_max_cvg_thru_dt;
1367 --
1368 -- ----------------------------------------------------------------------------
1369 -- |--------------------< update_cobra_elig_info >----------------------------
1370 -- ----------------------------------------------------------------------------
1371 --
1372 procedure update_cobra_elig_info
1373 (p_person_id in number
1374 ,p_per_in_ler_id in number
1375 ,p_lf_evt_ocrd_dt in date
1376 ,p_effective_date in date
1377 ,p_business_group_id in number
1378 ,p_validate in boolean default false) is
1379 --
1380 l_proc varchar2(80) := g_package||
1381 '.update_cobra_elig_info';
1382 l_ptip_id ben_ptip_f.ptip_id%type;
1383 l_pgm_id ben_pgm_f.pgm_id%type;
1384 l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1385 l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1386 l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1387 l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1388 l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1389 l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1390 l_dsbld_apls boolean;
1391 l_update boolean;
1392 l_arra_event boolean;
1393 --
1394 cursor c_get_epe is
1395 select epe.*, pgm.poe_lvl_cd
1396 from ben_elig_per_elctbl_chc epe
1397 ,ben_pgm_f pgm
1398 where epe.per_in_ler_id = p_per_in_ler_id
1399 and epe.business_group_id = p_business_group_id
1400 and epe.pgm_id = pgm.pgm_id
1401 and nvl(p_lf_evt_ocrd_dt, p_effective_date)
1402 between pgm.effective_start_date and pgm.effective_end_date
1403 and pgm.business_group_id = p_business_group_id
1404 and pgm.pgm_typ_cd like 'COBRA%'
1405 order by epe.pgm_id, epe.ptip_id;
1406 --
1407 cursor c_get_ler_info is
1408 select ler.*
1409 from ben_ler_f ler
1410 ,ben_per_in_ler pil
1411 where pil.per_in_ler_id = p_per_in_ler_id
1412 and pil.ler_id = ler.ler_id
1413 and ler.name = 'Assistance Eligible Individual'
1414 and p_effective_date
1415 between ler.effective_start_date
1416 and ler.effective_end_date;
1417 --
1418 l_ler_rec c_get_ler_info%rowtype;
1419 --
1420 cursor c_get_quald_bnf is
1421 select cqb.*
1422 from ben_cbr_quald_bnf cqb
1423 ,ben_cbr_per_in_ler crp
1424 ,ben_per_in_ler pil
1425 where cqb.quald_bnf_person_id = p_person_id
1426 and cqb.business_group_id = p_business_group_id
1427 and p_lf_evt_ocrd_dt
1428 between cqb.cbr_elig_perd_strt_dt
1429 and cqb.cbr_elig_perd_end_dt
1430 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
1431 and crp.init_evt_flag = 'Y'
1432 and cqb.quald_bnf_flag = 'N'
1433 and crp.per_in_ler_id = pil.per_in_ler_id
1434 and crp.business_group_id = cqb.business_group_id
1435 and crp.business_group_id = pil.business_group_id
1436 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1437 --
1438 begin
1439 --
1440 hr_utility.set_location('Entering : ' || l_proc, 10);
1441 --
1442 l_arra_event := false;
1443 --
1444 -- Check for Stimulus life event.
1445 --
1446 open c_get_ler_info;
1447 fetch c_get_ler_info into l_ler_rec;
1448 if c_get_ler_info%found then
1449 close c_get_ler_info;
1450 hr_utility.set_location('arra event found : ' || l_proc, 10);
1451 --
1452 -- Check if the person was previously eligible for
1453 -- for COBRA.
1454 --
1455 for l_cqb_rec in c_get_quald_bnf loop
1456 --
1457 -- Set the COBRA qualified beneficiary flag to 'Y' as the
1458 -- person is no longer a COBRA qualified beneficiary.
1459 --
1460 l_cqb_object_version_number := l_cqb_rec.object_version_number;
1461 --
1462 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
1463 (p_cbr_quald_bnf_id=> l_cqb_rec.cbr_quald_bnf_id
1464 ,p_quald_bnf_flag => 'Y'
1465 ,p_business_group_id => p_business_group_id
1466 ,p_object_version_number => l_cqb_object_version_number
1467 ,p_effective_date => p_effective_date);
1468 --
1469 l_arra_event := true;
1470 end loop;
1471 else
1472 close c_get_ler_info;
1473 end if;
1474 --
1475 if not l_arra_event then
1476 --
1477 for l_epe_rec in c_get_epe loop
1478 --
1479 -- A person can only be eligible in one COBRA program.
1480 --
1481 if (l_epe_rec.poe_lvl_cd = 'PGM' or
1482 l_epe_rec.poe_lvl_cd is null) then
1483 --
1484 if nvl(l_pgm_id,-1) <> l_epe_rec.pgm_id then
1485 --
1486 -- Determine cobra eligibility start and end dates.
1487 --
1488 determine_cobra_elig_dates
1489 (p_pgm_id => l_epe_rec.pgm_id
1490 ,p_pl_typ_id => l_epe_rec.pl_typ_id
1491 ,p_person_id => p_person_id
1492 ,p_per_in_ler_id => p_per_in_ler_id
1493 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1494 ,p_business_group_id => p_business_group_id
1495 ,p_effective_date => p_effective_date
1496 ,p_validate => p_validate
1497 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1498 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1499 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1500 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1501 ,p_cqb_object_version_number => l_cqb_object_version_number
1502 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1503 ,p_dsbld_apls => l_dsbld_apls
1504 ,p_update => l_update
1505 );
1506 --
1507 -- Only update the cobra information if the eligibility start
1508 -- and end dates are not null
1509 --
1510 if (l_cbr_elig_perd_strt_dt is not null and
1511 l_cbr_elig_perd_end_dt is not null and
1512 l_update)
1513 then
1514 update_cobra_info
1515 (p_per_in_ler_id => p_per_in_ler_id
1516 ,p_person_id => p_person_id
1517 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1518 ,p_cqb_object_version_number => l_cqb_object_version_number
1519 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1520 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1521 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1522 ,p_dsbld_apls => l_dsbld_apls
1523 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1524 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1525 ,p_business_group_id => p_business_group_id
1526 ,p_effective_date => p_effective_date
1527 ,p_pgm_id => l_epe_rec.pgm_id
1528 ,p_validate => p_validate
1529 );
1530 else
1531 --
1532 fnd_message.set_name('BEN','BEN_92428_CBR_DATES_NOT_FOUND');
1533 fnd_message.set_token('PROC',l_proc);
1534 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1535 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
1536 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
1537 if fnd_global.conc_request_id <> -1 then
1538 benutils.write(fnd_message.get);
1539 end if;
1540 --
1541 end if;
1542 l_pgm_id := l_epe_rec.pgm_id;
1543 --
1544 end if;
1545 --
1546 elsif l_epe_rec.poe_lvl_cd = 'PTIP' then
1547 --
1548 hr_utility.set_location('poe_lvl_cd : ' || l_epe_rec.poe_lvl_cd, 10);
1549 hr_utility.set_location('l_ptip_id : ' || l_ptip_id, 10);
1550 hr_utility.set_location('l_epe_rec.ptip_id : ' || l_epe_rec.ptip_id, 10);
1551 --
1552 if nvl(l_ptip_id,-1) <> l_epe_rec.ptip_id then
1553 --
1554 -- Determine cobra eligibility start and end dates.
1555 --
1556 determine_cobra_elig_dates
1557 (p_ptip_id => l_epe_rec.ptip_id
1558 ,p_pl_typ_id => l_epe_rec.pl_typ_id
1559 ,p_person_id => p_person_id
1560 ,p_per_in_ler_id => p_per_in_ler_id
1561 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1562 ,p_business_group_id => p_business_group_id
1563 ,p_effective_date => p_effective_date
1564 ,p_validate => p_validate
1565 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1566 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1567 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1568 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1569 ,p_cqb_object_version_number => l_cqb_object_version_number
1570 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1571 ,p_dsbld_apls => l_dsbld_apls
1572 ,p_update => l_update
1573 );
1574 --
1575 -- Only update the cobra information if the eligibility start
1576 -- and end dates are not null.
1577 --
1578 if (l_cbr_elig_perd_strt_dt is not null and
1579 l_cbr_elig_perd_end_dt is not null and
1580 l_update)
1581 then
1582 --
1583 update_cobra_info
1584 (p_per_in_ler_id => p_per_in_ler_id
1585 ,p_person_id => p_person_id
1586 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1587 ,p_cqb_object_version_number => l_cqb_object_version_number
1588 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1589 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1590 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1591 ,p_dsbld_apls => l_dsbld_apls
1592 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1593 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1594 ,p_business_group_id => p_business_group_id
1595 ,p_effective_date => p_effective_date
1596 ,p_pgm_id => l_epe_rec.pgm_id
1597 ,p_pl_typ_id => l_epe_rec.pl_typ_id
1598 ,p_ptip_id => l_epe_rec.ptip_id
1599 ,p_validate => p_validate
1600 );
1601 --
1602 else
1603 --
1604 fnd_message.set_name('BEN','BEN_92428_CBR_DATES_NOT_FOUND');
1605 fnd_message.set_token('PROC',l_proc);
1606 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1607 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
1608 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
1609 --
1610 if fnd_global.conc_request_id <> -1 then
1611 benutils.write(fnd_message.get);
1612 end if;
1613 end if;
1614 --
1615 l_ptip_id := l_epe_rec.ptip_id;
1616 --
1617 end if;
1618 --
1619 end if;
1620 end loop;
1621 end if; -- l_arra_event
1622 --
1623 hr_utility.set_location('Leaving : ' || l_proc, 10);
1624 --
1625 end update_cobra_elig_info;
1626 --
1627 -- ----------------------------------------------------------------------------
1628 -- |-------------------------< check_max_poe_eligibility >---------------------
1629 -- ---------------------------------------------------------------------------
1630 --
1631 function check_max_poe_eligibility
1632 (p_person_id in number
1633 ,p_mx_poe_apls_cd in varchar2
1634 ,p_cvrd_emp_person_id in number default null
1635 ,p_quald_bnf_person_id in number default null
1636 ,p_cbr_quald_bnf_id in number default null
1637 ,p_lf_evt_ocrd_dt in date
1638 ,p_business_group_id in number) return boolean is
1639 --
1640 --
1641 l_effective_date per_person_type_usages_f.effective_start_date%type;
1642 l_eligible boolean := false;
1643 l_exists varchar2(1);
1644 l_proc varchar2(80) := g_package||'.check_max_poe_eligibility';
1645 --
1646 cursor c_chk_cvrd_emp is
1647 select null
1648 from per_person_type_usages_f ptu
1649 ,per_person_types pet
1650 where ptu.person_type_id = pet.person_type_id
1651 and ptu.person_id = p_person_id
1652 and l_effective_date between
1653 ptu.effective_start_date and ptu.effective_end_date
1654 and pet.system_person_type = 'PRTN';
1655 --
1656 cursor c_get_contact_type is
1657 select null
1658 from per_contact_relationships ctr
1659 where ctr.person_id = p_person_id
1660 and ctr.contact_type = 'S'
1661 and p_lf_evt_ocrd_dt
1662 between nvl(ctr.date_start,hr_api.g_sot) and
1663 nvl(ctr.date_end,hr_api.g_eot)
1664 and ctr.business_group_id = p_business_group_id;
1665 --
1666 begin
1667 --
1668 hr_utility.set_location('Entering : ' || l_proc, 10);
1669 --
1670 l_effective_date := p_lf_evt_ocrd_dt - 1;
1671 --
1672 -- only applies to dependents of the covered employee.
1673 --
1674 if p_mx_poe_apls_cd = 'ACDPNT' then
1675 hr_utility.set_location('ACDPNT : ' || l_proc, 10);
1676 --
1677 -- If qualified beneficiary exists,
1678 --
1679 hr_utility.set_location('p_cbr_quald_bnf_id : ' || p_cbr_quald_bnf_id, 10);
1680 hr_utility.set_location('p_cvrd_emp_person_id : ' || p_cvrd_emp_person_id, 10);
1681 hr_utility.set_location('p_quald_bnf_person_id : ' || p_quald_bnf_person_id, 10);
1682 if p_cbr_quald_bnf_id is not null then
1683 --
1684 -- Check if the person is a covered dependent.
1685 --
1686 if p_cvrd_emp_person_id <> p_quald_bnf_person_id then
1687 l_eligible := true;
1688 end if;
1689 else
1690 --
1691 -- check if person is the covered employee.
1692 --
1693 open c_chk_cvrd_emp;
1694 fetch c_chk_cvrd_emp into l_exists;
1695 if c_chk_cvrd_emp%notfound then
1696 --
1697 -- Person is a covered dependent.
1698 --
1699 l_eligible := true;
1700 end if;
1701 close c_chk_cvrd_emp;
1702 end if;
1703 elsif p_mx_poe_apls_cd = 'CSPS' then
1704 --
1705 -- if max poe only applies to spouse, check if person is
1706 -- a spouse.
1707 --
1708 if p_cbr_quald_bnf_id is not null then
1709 if p_cvrd_emp_person_id <> p_quald_bnf_person_id then
1710 --
1711 open c_get_contact_type;
1712 fetch c_get_contact_type into l_exists;
1713 if c_get_contact_type%found then
1714 l_eligible := true;
1715 end if;
1716 close c_get_contact_type;
1717 end if;
1718 else
1719 --
1720 -- check if person is the covered employee.
1721 --
1722 open c_chk_cvrd_emp;
1723 fetch c_chk_cvrd_emp into l_exists;
1724 if c_chk_cvrd_emp%notfound then
1725 --
1726 -- Check if person is a spouse.
1727 --
1728 open c_get_contact_type;
1729 fetch c_get_contact_type into l_exists;
1730 if c_get_contact_type%found then
1731 l_eligible := true;
1732 end if;
1733 close c_get_contact_type;
1734 end if;
1735 close c_chk_cvrd_emp;
1736 end if;
1737 else
1738 l_eligible := true;
1739 end if;
1740 --
1741 hr_utility.set_location('Leaving : ' || l_proc, 10);
1742 return l_eligible;
1743 end check_max_poe_eligibility;
1744 -- ----------------------------------------------------------------------------
1745 -- |-------------------------< update_dpnt_cobra_info >-----------------------------
1746 -- ----------------------------------------------------------------------------
1747 --
1748 procedure update_dpnt_cobra_info
1749 (p_per_in_ler_id in number
1750 ,p_person_id in number
1751 ,p_business_group_id in number
1752 ,p_effective_date in date
1753 ,p_prtt_enrt_rslt_id in number
1754 ,p_validate in boolean default false) is
1755 --
1756 l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
1757 l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
1758 l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
1759 l_dsbld_apls boolean;
1760 l_update boolean;
1761 l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
1762 l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1763 l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
1764 l_old_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
1765 l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
1766 l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
1767 l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1768 l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
1769
1770 l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
1771 l_exists varchar2(1);
1772 l_init_evt boolean := false;
1773 l_pgm_id ben_pgm_f.pgm_id%type;
1774 l_ptip_id ben_ptip_f.ptip_id%type;
1775 l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
1776 l_cqb_ptip_id ben_ptip_f.ptip_id%type;
1777 l_poe_lvl_cd ben_pgm_f.poe_lvl_cd%type;
1778 l_enrld_person_id ben_prtt_enrt_rslt_f.person_id%type;
1779 l_proc varchar2(80) := g_package||'.update_dpnt_cobra_info';
1780 l_arra_event boolean;
1781 --
1782 cursor c_get_cbr_quald_bnf
1783 is
1784 select cqb.*
1785 from ben_cbr_quald_bnf cqb
1786 ,ben_cbr_per_in_ler crp
1787 ,ben_per_in_ler pil
1788 where cqb.quald_bnf_person_id = p_person_id
1789 and nvl(cqb.cbr_elig_perd_end_dt,l_lf_evt_ocrd_dt) >= l_lf_evt_ocrd_dt
1790 and cqb.pgm_id = l_pgm_id
1791 and nvl(cqb.ptip_id,l_ptip_id) = l_ptip_id
1792 and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
1793 and cqb.business_group_id = p_business_group_id
1794 and crp.per_in_ler_id = pil.per_in_ler_id
1795 and crp.business_group_id = cqb.business_group_id
1796 and pil.business_group_id = crp.business_group_id
1797 and crp.init_evt_flag = 'Y'
1798 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1799 --
1800 cursor c_get_pgm_id
1801 is
1802 select pen.*
1803 from ben_prtt_enrt_rslt_f pen
1804 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1805 and pen.prtt_enrt_rslt_stat_cd is null
1806 and p_effective_date
1807 between pen.effective_start_date and pen.effective_end_date
1808 and pen.business_group_id = p_business_group_id;
1809 --
1810 cursor c_get_cvrd_emp_person_id
1811 is
1812 select cqb.*, crp.per_in_ler_id
1813 from ben_cbr_quald_bnf cqb
1814 ,ben_cbr_per_in_ler crp
1815 ,ben_per_in_ler pil
1816 where cqb.quald_bnf_person_id = l_enrld_person_id
1817 and cqb.cbr_elig_perd_end_dt > l_lf_evt_ocrd_dt
1818 and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
1819 and cqb.business_group_id = p_business_group_id
1820 and crp.per_in_ler_id = pil.per_in_ler_id
1821 and crp.business_group_id = cqb.business_group_id
1822 and pil.business_group_id = crp.business_group_id
1823 and crp.init_evt_flag = 'Y'
1824 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1825 --
1826 cursor c_get_poe_lvl_cd
1827 is
1828 select pgm.poe_lvl_cd
1829 from ben_pgm_f pgm
1830 where pgm.pgm_id = l_pgm_id
1831 and p_effective_date
1832 between pgm.effective_start_date and pgm.effective_end_date
1833 and pgm.business_group_id = p_business_group_id;
1834 --
1835 -- arra event
1836 --
1837 cursor c_get_ler_info is
1838 select null
1839 from ben_ler_f ler
1840 ,ben_per_in_ler pil
1841 where pil.per_in_ler_id = p_per_in_ler_id
1842 and pil.ler_id = ler.ler_id
1843 and ler.name = 'Assistance Eligible Individual'
1844 and p_effective_date
1845 between ler.effective_start_date
1846 and ler.effective_end_date;
1847 --
1848 l_cqb_rec c_get_cbr_quald_bnf%rowtype;
1849 l_pen_rec c_get_pgm_id%rowtype;
1850 l_cvrd_emp_rec c_get_cvrd_emp_person_id%rowtype;
1851 --
1852 begin
1853 --
1854 hr_utility.set_location('Entering : ' || l_proc, 10);
1855 hr_utility.set_location('p_prtt_enrt_rslt_id : ' || p_prtt_enrt_rslt_id, 10);
1856 --
1857 -- Check for arra event
1858 --
1859 l_arra_event := false;
1860 --
1861 open c_get_ler_info;
1862 fetch c_get_ler_info into l_exists;
1863 if c_get_ler_info%found then
1864 l_arra_event := true;
1865 end if;
1866 --
1867 -- Get program id and enrolled person_id from enrollment result.
1868 --
1869 open c_get_pgm_id;
1870 fetch c_get_pgm_id into l_pen_rec;
1871 close c_get_pgm_id;
1872 --
1873 l_ptip_id := l_pen_rec.ptip_id;
1874 l_pgm_id := l_pen_rec.pgm_id;
1875 l_enrld_person_id := l_pen_rec.person_id;
1876 --
1877 hr_utility.set_location('pgm_id : ' || l_pen_rec.pgm_id, 10);
1878 hr_utility.set_location('ptip_id : ' || l_pen_rec.ptip_id, 10);
1879 --
1880 if chk_pgm_typ(p_pgm_id => l_pgm_id
1881 ,p_effective_date => p_effective_date
1882 ,p_business_group_id => p_business_group_id
1883 ) = false then
1884 hr_utility.set_location('Leaving - Not found : ' || l_proc, 10);
1885 return;
1886 end if;
1887 --
1888 -- Get the life event occurred date.
1889 --
1890 l_lf_evt_ocrd_dt := get_lf_evt_ocrd_dt
1891 (p_per_in_ler_id => p_per_in_ler_id
1892 ,p_business_group_id => p_business_group_id
1893 );
1894 --
1895 --
1896 -- Check if person is a qualified beneficiary.
1897 --
1898 open c_get_cbr_quald_bnf;
1899 fetch c_get_cbr_quald_bnf into l_cqb_rec;
1900 if c_get_cbr_quald_bnf%notfound then
1901 close c_get_cbr_quald_bnf;
1902 --
1903 open c_get_poe_lvl_cd;
1904 fetch c_get_poe_lvl_cd into l_poe_lvl_cd;
1905 close c_get_poe_lvl_cd;
1906 --
1907 --
1908 -- Check if event is the initial qualifying event. If it is
1909 -- not the initial qualifying event, set the quald_bnf_flag = No
1910 -- Person cannot be a qualified beneficiary unless it is the
1911 -- initial qualifying event. This is a dependent that is
1912 -- designated after the initial qualifying event event, during
1913 -- open enrollment, marriage or a gain of dependent event.
1914 --
1915 if (chk_init_evt(p_per_in_ler_id => p_per_in_ler_id
1916 ,p_business_group_id => p_business_group_id)) = false
1917 then
1918 l_quald_bnf_flag := 'N';
1919 l_cbr_elig_perd_strt_dt := null;
1920 l_cbr_elig_perd_end_dt := null;
1921 --
1922 if (l_poe_lvl_cd = 'PGM' or
1923 l_poe_lvl_cd is null) then
1924 l_cqb_ptip_id := null;
1925 l_pl_typ_id := null;
1926 elsif l_poe_lvl_cd = 'PTIP' then
1927 l_cqb_ptip_id := l_ptip_id;
1928 l_pl_typ_id := l_pen_rec.pl_typ_id;
1929 end if;
1930 --
1931 else
1932 hr_utility.set_location('Initial qualifying event', 10);
1933 l_quald_bnf_flag := 'Y';
1934 --
1935 -- Normally, a cobra qualified beneficiary row would exist
1936 -- if benmngle was run for the related life event. In the event
1937 -- that the related life event is run after the covered employee
1938 -- can make elections(per fidelity), we can go ahead a create
1939 -- a qualified beneficiary here and when benmngle processes
1940 -- the related life event, it would simply just update the row.
1941 --
1942 if (l_poe_lvl_cd = 'PGM' or
1943 l_poe_lvl_cd is null) then
1944 --
1945 determine_cobra_elig_dates
1946 (p_pgm_id => l_pgm_id
1947 ,p_pl_typ_id => l_pen_rec.pl_typ_id
1948 ,p_person_id => p_person_id
1949 ,p_per_in_ler_id => p_per_in_ler_id
1950 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1951 ,p_business_group_id => p_business_group_id
1952 ,p_effective_date => p_effective_date
1953 ,p_validate => p_validate
1954 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1955 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1956 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1957 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1958 ,p_cqb_object_version_number => l_cqb_object_version_number
1959 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1960 ,p_dsbld_apls => l_dsbld_apls
1961 ,p_update => l_update
1962 );
1963 --
1964 hr_utility.set_location('l_cbr_elig_perd_strt_dt '||l_cbr_elig_perd_strt_dt, 10);
1965 hr_utility.set_location('l_cbr_elig_perd_end_dt '||l_cbr_elig_perd_end_dt, 10);
1966 hr_utility.set_location('l_old_cbr_elig_perd_end_dt '||l_old_cbr_elig_perd_end_dt, 10);
1967 l_cqb_ptip_id := null;
1968 l_pl_typ_id := null;
1969 --
1970 elsif l_poe_lvl_cd = 'PTIP' then
1971 l_cqb_ptip_id := l_ptip_id;
1972 l_pl_typ_id := l_pen_rec.pl_typ_id;
1973 --
1974 -- Determine cobra eligibility start and end dates.
1975 --
1976 determine_cobra_elig_dates
1977 (p_ptip_id => l_ptip_id
1978 ,p_pl_typ_id => l_pl_typ_id
1979 ,p_person_id => p_person_id
1980 ,p_per_in_ler_id => p_per_in_ler_id
1981 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1982 ,p_business_group_id => p_business_group_id
1983 ,p_effective_date => p_effective_date
1984 ,p_validate => p_validate
1985 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
1986 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
1987 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
1988 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
1989 ,p_cqb_object_version_number => l_cqb_object_version_number
1990 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
1991 ,p_dsbld_apls => l_dsbld_apls
1992 ,p_update => l_update
1993 );
1994 end if;
1995 end if;
1996 --
1997 -- Get the cvrd employee person id.
1998 --
1999 open c_get_cvrd_emp_person_id;
2000 fetch c_get_cvrd_emp_person_id into l_cvrd_emp_rec;
2001 if c_get_cvrd_emp_person_id%notfound then
2002 --
2003 close c_get_cvrd_emp_person_id;
2004 --
2005 fnd_message.set_name('BEN','BEN_92429_CVRD_EMP_NOT_FOUND');
2006 --fnd_message.set_token('PROC',l_proc);
2007 fnd_message.raise_error;
2008 else
2009 close c_get_cvrd_emp_person_id;
2010 end if;
2011 --
2012 -- arra event
2013 --
2014 if (l_arra_event
2015 and l_quald_bnf_flag = 'N') then
2016 --
2017 -- Write a qualified beneficiary row for the dependent
2018 --
2019 ben_cbr_quald_bnf_api.create_cbr_quald_bnf
2020 (p_validate => p_validate
2021 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2022 ,p_quald_bnf_flag => l_cvrd_emp_rec.quald_bnf_flag
2023 ,p_cbr_elig_perd_strt_dt => l_cvrd_emp_rec.cbr_elig_perd_strt_dt
2024 ,p_cbr_elig_perd_end_dt => l_cvrd_emp_rec.cbr_elig_perd_end_dt
2025 ,p_quald_bnf_person_id => p_person_id
2026 ,p_cvrd_emp_person_id => l_cvrd_emp_rec.cvrd_emp_person_id
2027 ,p_pgm_id => l_pgm_id
2028 ,p_ptip_id => l_cqb_ptip_id
2029 ,p_pl_typ_id => l_pl_typ_id
2030 ,p_business_group_id => p_business_group_id
2031 ,p_object_version_number => l_object_version_number
2032 ,p_effective_date => p_effective_date
2033 );
2034 --
2035 -- Write a cbr per in ler row
2036 --
2037 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2038 (p_validate => p_validate
2039 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2040 ,p_init_evt_flag => 'Y'
2041 ,p_cnt_num => 1
2042 ,p_per_in_ler_id => l_cvrd_emp_rec.per_in_ler_id
2043 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2044 ,p_prvs_elig_perd_end_dt => null
2045 ,p_business_group_id => p_business_group_id
2046 ,p_object_version_number => l_object_version_number
2047 ,p_effective_date => p_effective_date
2048 );
2049
2050
2051 else
2052 --
2053 update_cobra_info
2054 (p_per_in_ler_id => p_per_in_ler_id
2055 ,p_person_id => p_person_id
2056 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
2057 ,p_cqb_object_version_number => l_cqb_rec.object_version_number
2058 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2059 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
2060 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2061 ,p_dsbld_apls => l_dsbld_apls
2062 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2063 ,p_quald_bnf_flag => l_quald_bnf_flag
2064 ,p_cvrd_emp_person_id => l_cvrd_emp_rec.cvrd_emp_person_id
2065 ,p_business_group_id => p_business_group_id
2066 ,p_pgm_id => l_pgm_id
2067 ,p_ptip_id => l_cqb_ptip_id
2068 ,p_pl_typ_id => l_pl_typ_id
2069 ,p_effective_date => p_effective_date
2070 ,p_validate => p_validate
2071 );
2072 end if;
2073 else -- quald bnf found.
2074 --
2075 hr_utility.set_location('Quald bnf exists', 10);
2076 --
2077 close c_get_cbr_quald_bnf;
2078 --
2079 if l_cqb_rec.quald_bnf_flag = 'Y' then
2080 --
2081 -- Get cobra eligibility dates. Check if we need to update the
2082 -- eligibility end dates.
2083 --
2084 open c_get_poe_lvl_cd;
2085 fetch c_get_poe_lvl_cd into l_poe_lvl_cd;
2086 close c_get_poe_lvl_cd;
2087 --
2088 hr_utility.set_location('POE LVL '||l_poe_lvl_cd, 10);
2089 if (l_poe_lvl_cd = 'PGM' or
2090 l_poe_lvl_cd is null) then
2091 --
2092 determine_cobra_elig_dates
2093 (p_pgm_id => l_pgm_id
2094 ,p_pl_typ_id => l_pen_rec.pl_typ_id
2095 ,p_person_id => p_person_id
2096 ,p_per_in_ler_id => p_per_in_ler_id
2097 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2098 ,p_business_group_id => p_business_group_id
2099 ,p_effective_date => p_effective_date
2100 ,p_validate => p_validate
2101 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2102 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2103 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
2104 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2105 ,p_cqb_object_version_number => l_cqb_object_version_number
2106 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
2107 ,p_dsbld_apls => l_dsbld_apls
2108 ,p_update => l_update
2109 );
2110 --
2111 hr_utility.set_location('elig_strt_dt '||l_cbr_elig_perd_strt_dt, 10);
2112 hr_utility.set_location('elig_end_dt '||l_cbr_elig_perd_end_dt, 10);
2113 hr_utility.set_location('old_elig_end_dt '||l_old_cbr_elig_perd_end_dt, 10);
2114 l_cqb_ptip_id := null;
2115 l_pl_typ_id := null;
2116 --
2117 elsif l_poe_lvl_cd = 'PTIP' then
2118 l_cqb_ptip_id := l_ptip_id;
2119 l_pl_typ_id := l_pen_rec.pl_typ_id;
2120 --
2121 -- Determine cobra eligibility start and end dates.
2122 --
2123 determine_cobra_elig_dates
2124 (p_ptip_id => l_ptip_id
2125 ,p_pl_typ_id => l_pl_typ_id
2126 ,p_person_id => p_person_id
2127 ,p_per_in_ler_id => p_per_in_ler_id
2128 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2129 ,p_business_group_id => p_business_group_id
2130 ,p_effective_date => p_effective_date
2131 ,p_validate => p_validate
2132 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2133 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2134 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
2135 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2136 ,p_cqb_object_version_number => l_cqb_object_version_number
2137 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
2138 ,p_dsbld_apls => l_dsbld_apls
2139 ,p_update => l_update
2140 );
2141 hr_utility.set_location('l_cbr_elig_perd_strt_dt '||l_cbr_elig_perd_strt_dt, 10);
2142 hr_utility.set_location('l_cbr_elig_perd_end_dt '||l_cbr_elig_perd_end_dt, 10);
2143 hr_utility.set_location('l_old_cbr_elig_perd_end_dt '||l_old_cbr_elig_perd_end_dt, 10);
2144 end if;
2145 --
2146 update_cobra_info
2147 (p_per_in_ler_id => p_per_in_ler_id
2148 ,p_person_id => p_person_id
2149 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
2150 ,p_cqb_object_version_number => l_cqb_object_version_number
2151 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2152 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
2153 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2154 ,p_dsbld_apls => l_dsbld_apls
2155 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2156 ,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
2157 ,p_business_group_id => p_business_group_id
2158 ,p_effective_date => p_effective_date
2159 ,p_validate => p_validate
2160 );
2161 else -- if qual bnf flag = 'N'
2162 --
2163 hr_utility.set_location('quald_bnf_flag N', 10);
2164 --
2165 -- If it is the initial event, person is now a qualified beneficiary.
2166 --
2167 if (chk_init_evt(p_per_in_ler_id => p_per_in_ler_id
2168 ,p_business_group_id => p_business_group_id))
2169 then
2170 --
2171 open c_get_poe_lvl_cd;
2172 fetch c_get_poe_lvl_cd into l_poe_lvl_cd;
2173 close c_get_poe_lvl_cd;
2174 --
2175 if (l_poe_lvl_cd = 'PGM' or
2176 l_poe_lvl_cd is null) then
2177 --
2178 determine_cobra_elig_dates
2179 (p_pgm_id => l_pgm_id
2180 ,p_pl_typ_id => l_pen_rec.pl_typ_id
2181 ,p_person_id => p_person_id
2182 ,p_per_in_ler_id => p_per_in_ler_id
2183 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2184 ,p_business_group_id => p_business_group_id
2185 ,p_effective_date => p_effective_date
2186 ,p_validate => p_validate
2187 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2188 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2189 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
2190 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2191 ,p_cqb_object_version_number => l_cqb_object_version_number
2192 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
2193 ,p_dsbld_apls => l_dsbld_apls
2194 ,p_update => l_update
2195 );
2196 l_cqb_ptip_id := null;
2197 l_pl_typ_id := null;
2198 --
2199 elsif l_poe_lvl_cd = 'PTIP' then
2200 l_cqb_ptip_id := l_ptip_id;
2201 l_pl_typ_id := l_pen_rec.pl_typ_id;
2202 --
2203 -- Determine cobra eligibility start and end dates.
2204 --
2205 determine_cobra_elig_dates
2206 (p_ptip_id => l_ptip_id
2207 ,p_pl_typ_id => l_pl_typ_id
2208 ,p_person_id => p_person_id
2209 ,p_per_in_ler_id => p_per_in_ler_id
2210 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2211 ,p_business_group_id => p_business_group_id
2212 ,p_effective_date => p_effective_date
2213 ,p_validate => p_validate
2214 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2215 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2216 ,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
2217 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2218 ,p_cqb_object_version_number => l_cqb_object_version_number
2219 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
2220 ,p_dsbld_apls => l_dsbld_apls
2221 ,p_update => l_update
2222 );
2223 end if;
2224 --
2225 -- Get the cvrd employee person id.
2226 --
2227 open c_get_cvrd_emp_person_id;
2228 fetch c_get_cvrd_emp_person_id into l_cvrd_emp_rec;
2229 if c_get_cvrd_emp_person_id%notfound then
2230 --
2231 close c_get_cvrd_emp_person_id;
2232 --
2233 fnd_message.set_name('BEN','BEN_92429_CVRD_EMP_NOT_FOUND');
2234 --fnd_message.set_token('PROC',l_proc);
2235 fnd_message.raise_error;
2236 else
2237 close c_get_cvrd_emp_person_id;
2238 end if;
2239 --
2240 update_cobra_info
2241 (p_per_in_ler_id => p_per_in_ler_id
2242 ,p_person_id => p_person_id
2243 ,p_cbr_quald_bnf_id => null
2244 ,p_cqb_object_version_number => null
2245 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
2246 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
2247 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2248 ,p_dsbld_apls => l_dsbld_apls
2249 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2250 ,p_quald_bnf_flag => 'Y'
2251 ,p_cvrd_emp_person_id => l_cvrd_emp_rec.cvrd_emp_person_id
2252 ,p_business_group_id => p_business_group_id
2253 ,p_pgm_id => l_pgm_id
2254 ,p_ptip_id => l_cqb_ptip_id
2255 ,p_pl_typ_id => l_pl_typ_id
2256 ,p_effective_date => p_effective_date
2257 ,p_validate => p_validate
2258 );
2259 end if; -- End check initial event.
2260 end if;
2261 end if;
2262 --
2263 hr_utility.set_location('Leaving : ' || l_proc, 10);
2264 end update_dpnt_cobra_info;
2265 -- ----------------------------------------------------------------------------
2266 -- |-------------------------< chk_cobra_eligibility >-------------------------
2267 -- ----------------------------------------------------------------------------
2268 --
2269 procedure chk_cobra_eligibility
2270 (p_per_in_ler_id in number
2271 ,p_person_id in number
2272 ,p_pgm_id in number
2273 ,p_lf_evt_ocrd_dt in date
2274 ,p_business_group_id in number
2275 ,p_effective_date in date
2276 ,p_validate in boolean default false) is
2277 --
2278 l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2279 l_update boolean := false;
2280 l_cbr_elig_perd_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%type;
2281 l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2282 l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2283 l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2284 l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2285 l_quald_bnf_person_id ben_cbr_quald_bnf.quald_bnf_person_id%type;
2286 l_cbr_elig_perd_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%type;
2287 l_enrld boolean := false;
2288 l_exists varchar2(1);
2289 l_pgm_id ben_pgm_f.pgm_id%type;
2290 l_proc varchar2(80) := g_package||'.chk_cobra_eligibility';
2291 --
2292 cursor c_get_cbr_quald_bnf(p_quald_bnf_person_id in number)
2293 is
2294 select cqb.*
2295 from ben_cbr_quald_bnf cqb
2296 ,ben_cbr_per_in_ler crp
2297 ,ben_per_in_ler pil
2298 where cqb.quald_bnf_person_id = p_quald_bnf_person_id
2299 and cqb.quald_bnf_flag = 'Y'
2300 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2301 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2302 and cqb.business_group_id = p_business_group_id
2303 and crp.per_in_ler_id = pil.per_in_ler_id
2304 and crp.business_group_id = cqb.business_group_id
2305 and pil.business_group_id = crp.business_group_id
2306 and crp.init_evt_flag = 'Y'
2307 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2308 --
2309 cursor c_get_cvrd_dpnt
2310 is
2311 select distinct pdp.dpnt_person_id
2312 from ben_elig_cvrd_dpnt_f pdp
2313 where pdp.per_in_ler_id = p_per_in_ler_id
2314 and p_lf_evt_ocrd_dt
2315 between pdp.effective_start_date and pdp.effective_end_date
2316 and pdp.cvg_thru_dt <> hr_api.g_eot
2317 and pdp.business_group_id = p_business_group_id;
2318 --
2319 cursor c_chk_init_evt is
2320 select crp.*
2321 from ben_cbr_per_in_ler crp
2322 where crp.per_in_ler_id = p_per_in_ler_id
2323 and crp.init_evt_flag = 'Y'
2324 and crp.business_group_id = p_business_group_id;
2325 --
2326 -- 14332963
2327 --
2328 cursor c_get_ptnl_evt(p_person_id in number) is
2329 select null
2330 from ben_ptnl_ler_for_per ppl
2331 where ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2332 and ppl.person_id = p_person_id
2333 and ppl.ptnl_ler_for_per_stat_cd in ('DTCTD', 'PROCD', 'UNPROCD')
2334 and ppl.business_group_id = p_business_group_id;
2335 -- end 14332963
2336 --
2337 l_cqb_rec c_get_cbr_quald_bnf%rowtype;
2338 l_crp_rec c_chk_init_evt%rowtype;
2339 --
2340 begin
2341 --
2342 hr_utility.set_location('Entering : ' || l_proc, 10);
2343 --
2344 -- Only update COBRA information if person is enrolling
2345 -- in a COBRA program.
2346 --
2347 if chk_pgm_typ(p_pgm_id => p_pgm_id
2348 ,p_effective_date => p_effective_date
2349 ,p_business_group_id => p_business_group_id
2350 ) = false then
2351 hr_utility.set_location('Leaving : ' || l_proc, 15);
2352 return;
2353 end if;
2354 --
2355 -- If it is the initial event, the Loss of Eligibility temporal
2356 -- event will set the qualified beneficiary flag to 'N' if the
2357 -- person waives coverage or does not elect during the enrollment
2358 -- period so we do not need to do anything here.
2359 --
2360 open c_chk_init_evt;
2361 fetch c_chk_init_evt into l_crp_rec;
2362 if c_chk_init_evt%notfound then
2363 close c_chk_init_evt;
2364 --
2365 -- Check if person is enrolled or covered in the pgm or plan type.
2366 --
2367 for l_cqb_rec in c_get_cbr_quald_bnf(p_person_id) loop
2368 --
2369 -- If person is no longer enrolled or covered in the COBRA program
2370 -- or plan type in program,then set the cobra eligibility end date
2371 -- to the maximum coverage end date of the enrollment results.
2372 --
2373 if (chk_enrld_or_cvrd
2374 (p_pgm_id => l_cqb_rec.pgm_id
2375 ,p_ptip_id => l_cqb_rec.ptip_id
2376 ,p_person_id => p_person_id
2377 ,p_effective_date => p_effective_date
2378 ,p_business_group_id => p_business_group_id)) = false then
2379 --
2380 -- The maximum enrollment coverage end date is the
2381 -- cobra eligibility end date for the person.
2382 --
2383 l_cbr_elig_perd_end_dt
2384 := get_max_cvg_thru_dt
2385 (p_person_id => p_person_id
2386 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2387 ,p_pgm_id => l_cqb_rec.pgm_id
2388 ,p_ptip_id => l_cqb_rec.ptip_id
2389 ,p_per_in_ler_id => p_per_in_ler_id
2390 ,p_business_group_id => p_business_group_id
2391 ,p_effective_date => p_effective_date
2392 );
2393 --
2394 -- Only set the COBRA eligibility end date if the person
2395 -- was previously covered.
2396 --
2397 if l_cbr_elig_perd_end_dt <> hr_api.g_sot then
2398 --
2399 update_cobra_info
2400 (p_per_in_ler_id => p_per_in_ler_id
2401 ,p_person_id => l_cqb_rec.quald_bnf_person_id
2402 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
2403 ,p_cqb_object_version_number => l_cqb_rec.object_version_number
2404 ,p_cbr_elig_perd_strt_dt => l_cqb_rec.cbr_elig_perd_strt_dt
2405 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
2406 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2407 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2408 ,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
2409 ,p_cbr_inelg_rsn_cd => 'VEC' -- Voluntary end of coverage.
2410 ,p_business_group_id => p_business_group_id
2411 ,p_effective_date => p_effective_date
2412 ,p_validate => p_validate
2413 );
2414 end if;
2415 end if; -- end enrld in pgm or ptip.
2416 end loop;
2417 --
2418 -- Also check if covered dependents are still covered.
2419 --
2420 -- Get all covered dependent.
2421 --
2422 for l_pdp_rec in c_get_cvrd_dpnt loop
2423 --
2424 -- Get the cobra qualified beneficiary row for the dependent
2425 --
2426 for l_cqb_rec in c_get_cbr_quald_bnf(l_pdp_rec.dpnt_person_id) loop
2427 --
2428 -- If person is no longer enrolled or covered in the COBRA program
2429 -- or plan type in program,then set the cobra eligibility end date
2430 -- to the maximum coverage end date of the enrollment results.
2431 --
2432 if (chk_enrld_or_cvrd
2433 (p_pgm_id => l_cqb_rec.pgm_id
2434 ,p_ptip_id => l_cqb_rec.ptip_id
2435 ,p_person_id => l_cqb_rec.quald_bnf_person_id
2436 ,p_effective_date => p_effective_date
2437 ,p_business_group_id => p_business_group_id)) = false then
2438 --
2439 -- bug 14332963. Do not update if there is a potential or
2440 -- started event for the dependent.
2441 --
2442 open c_get_ptnl_evt(l_pdp_rec.dpnt_person_id);
2443 fetch c_get_ptnl_evt into l_exists;
2444 if c_get_ptnl_evt%notfound then
2445 close c_get_ptnl_evt;
2446 hr_utility.set_location('l_pdp_rec.dpnt_person_id : ' || l_pdp_rec.dpnt_person_id, 10);
2447 --
2448 -- The maximum enrollment coverage end date is the
2449 -- cobra eligibility end date for the person.
2450 --
2451 l_cbr_elig_perd_end_dt
2452 := get_max_cvg_thru_dt
2453 (p_person_id => l_cqb_rec.quald_bnf_person_id
2454 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2455 ,p_pgm_id => l_cqb_rec.pgm_id
2456 ,p_ptip_id => l_cqb_rec.ptip_id
2457 ,p_per_in_ler_id => p_per_in_ler_id
2458 ,p_business_group_id => p_business_group_id
2459 ,p_effective_date => p_effective_date
2460 );
2461 --
2462 if l_cbr_elig_perd_end_dt <> hr_api.g_sot then
2463 update_cobra_info
2464 (p_per_in_ler_id => p_per_in_ler_id
2465 ,p_person_id => l_cqb_rec.quald_bnf_person_id
2466 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
2467 ,p_cqb_object_version_number => l_cqb_rec.object_version_number
2468 ,p_cbr_elig_perd_strt_dt => l_cqb_rec.cbr_elig_perd_strt_dt
2469 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
2470 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2471 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2472 ,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
2473 ,p_business_group_id => p_business_group_id
2474 ,p_effective_date => p_effective_date
2475 ,p_validate => p_validate
2476 );
2477 end if;
2478 else
2479 hr_utility.set_location('ptnl found ' , 10);
2480 close c_get_ptnl_evt;
2481 end if; -- c_get_curr_evt. 14332963
2482 end if; -- end enrld in pgm or ptip.
2483 end loop;
2484 end loop;
2485 else
2486 close c_chk_init_evt;
2487 end if;
2488 --
2489 hr_utility.set_location('Leaving : ' || l_proc, 10);
2490 end chk_cobra_eligibility;
2491 -- ----------------------------------------------------------------------------
2492 -- |-------------------------< update_cobra_info >-----------------------------
2493 -- ----------------------------------------------------------------------------
2494 --
2495 procedure update_cobra_info
2496 (p_per_in_ler_id in number
2497 ,p_person_id in number
2498 ,p_cbr_quald_bnf_id in number default null
2499 ,p_cqb_object_version_number in number default null
2500 ,p_cbr_elig_perd_strt_dt in date default null
2501 ,p_old_cbr_elig_perd_end_dt in date default null
2502 ,p_cbr_elig_perd_end_dt in date
2503 ,p_dsbld_apls in boolean default false
2504 ,p_lf_evt_ocrd_dt in date
2505 ,p_quald_bnf_flag in varchar2 default 'Y'
2506 ,p_cvrd_emp_person_id in number default null
2507 ,p_cbr_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
2508 ,p_business_group_id in number
2509 ,p_effective_date in date
2510 ,p_pgm_id in number default null
2511 ,p_ptip_id in number default null
2512 ,p_pl_typ_id in number default null
2513 ,p_validate in boolean default false) is
2514 --
2515 l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
2516 l_proc varchar2(80) := g_package||'.update_cobra_info';
2517 l_exists varchar2(1);
2518 l_init_evt boolean := false;
2519 l_cvrd_emp_person_id ben_cbr_quald_bnf.cvrd_emp_person_id%type;
2520 l_dsbld_apls boolean;
2521 l_update boolean;
2522 l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
2523 l_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
2524 l_cvrd_emp_end_date date;
2525 l_cbr_elig_perd_end_dt date;
2526 l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
2527 l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
2528 l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2529 l_cqb_object_version_number ben_cbr_per_in_ler.object_version_number%type;
2530 l_object_version_number ben_cbr_quald_bnf.object_version_number%type;
2531 --
2532 cursor c_chk_cvrd_emp is
2533 select null
2534 from per_person_type_usages_f ptu
2535 ,per_person_types pet
2536 where ptu.person_type_id = pet.person_type_id
2537 and ptu.person_id = p_person_id
2538 and l_effective_date between
2539 ptu.effective_start_date and ptu.effective_end_date
2540 and pet.system_person_type = 'PRTN';
2541 --
2542 cursor c_get_cvrd_emp is
2543 select ctr.person_id
2544 from per_contact_relationships ctr
2545 ,per_person_type_usages_f ptu
2546 ,per_person_types pet
2547 where ctr.contact_person_id = p_person_id
2548 and p_lf_evt_ocrd_dt
2549 between nvl(ctr.date_start,hr_api.g_sot) and
2550 nvl(ctr.date_end,hr_api.g_eot)
2551 and ctr.business_group_id = p_business_group_id
2552 and ctr.person_id = ptu.person_id
2553 and ptu.person_type_id = pet.person_type_id
2554 and l_effective_date between
2555 ptu.effective_start_date and ptu.effective_end_date
2556 and pet.system_person_type = 'PRTN';
2557 --
2558 cursor c_get_qualg_evt is
2559 select null
2560 from ben_ler_f ler
2561 ,ben_per_in_ler pil
2562 where ler.ler_id = pil.ler_id
2563 and pil.per_in_ler_id = p_per_in_ler_id
2564 and p_effective_date between
2565 ler.effective_start_date and ler.effective_end_date
2566 and pil.business_group_id = p_business_group_id
2567 and pil.business_group_id = ler.business_group_id
2568 and ler.qualg_evt_flag = 'Y';
2569 --
2570 cursor c_check_cbr_per_in_ler is
2571 select crp.*
2572 from ben_cbr_per_in_ler crp
2573 where (crp.per_in_ler_id = p_per_in_ler_id
2574 or (crp.per_in_ler_id
2575 in (select distinct crp2.per_in_ler_id
2576 from ben_cbr_per_in_ler crp2
2577 ,ben_cbr_quald_bnf cqb
2578 ,ben_per_in_ler pil
2579 where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2580 and cqb.cbr_quald_bnf_id = crp2.cbr_quald_bnf_id
2581 and crp2.per_in_ler_id = pil.per_in_ler_id
2582 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2583 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2584 and cqb.business_group_id = p_business_group_id
2585 and crp2.business_group_id = cqb.business_group_id
2586 and pil.business_group_id = crp2.business_group_id)))
2587 and crp.business_group_id = p_business_group_id
2588 and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id;
2589 --
2590 cursor c_get_cnt_num(p_cbr_quald_bnf_id in number) is
2591 select max(crp.cnt_num)
2592 from ben_cbr_per_in_ler crp
2593 ,ben_per_in_ler pil
2594 where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
2595 and crp.business_group_id = p_business_group_id
2596 and crp.per_in_ler_id = pil.per_in_ler_id
2597 and crp.business_group_id = pil.business_group_id
2598 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2599 --
2600 cursor c_get_all_quald_dpnts is
2601 select cqb.*
2602 from ben_cbr_quald_bnf cqb
2603 ,ben_cbr_per_in_ler crp
2604 ,ben_per_in_ler pil
2605 where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2606 and cqb.quald_bnf_person_id <> p_person_id
2607 and cqb.quald_bnf_flag = 'Y'
2608 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2609 and cqb.business_group_id = p_business_group_id
2610 and crp.business_group_id = cqb.business_group_id
2611 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2612 and crp.per_in_ler_id = pil.per_in_ler_id
2613 and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
2614 and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
2615 and crp.business_group_id = pil.business_group_id
2616 and crp.init_evt_flag = 'Y'
2617 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2618 --
2619 cursor c_get_cvrd_emp_end_date is
2620 select cbr_elig_perd_end_dt
2621 from ben_cbr_quald_bnf cqb
2622 where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
2623 and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
2624 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2625 and cqb.business_group_id = p_business_group_id
2626 and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
2627 and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1);
2628 --
2629 cursor c_get_cvrd_emp_pil(p_cvrd_emp_person_id in number) is
2630 select crp.*
2631 from ben_cbr_quald_bnf cqb
2632 ,ben_cbr_per_in_ler crp
2633 ,ben_per_in_ler pil
2634 where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
2635 and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
2636 and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
2637 and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
2638 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2639 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2640 and cqb.business_group_id = p_business_group_id
2641 and crp.init_evt_flag = 'Y'
2642 and crp.business_group_id = cqb.business_group_id
2643 and crp.per_in_ler_id = pil.per_in_ler_id
2644 and crp.business_group_id = pil.business_group_id
2645 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2646 --
2647 -- 9294528
2648 --
2649 cursor c_chk_init_evt is
2650 select null
2651 from ben_cbr_quald_bnf cqb
2652 ,ben_cbr_per_in_ler crp
2653 ,ben_per_in_ler pil
2654 where cqb.quald_bnf_person_id = p_person_id
2655 and cqb.quald_bnf_flag = 'Y'
2656 and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
2657 and cqb.business_group_id = p_business_group_id
2658 and crp.business_group_id = cqb.business_group_id
2659 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2660 and crp.per_in_ler_id = pil.per_in_ler_id
2661 and crp.per_in_ler_id <> p_per_in_ler_id
2662 and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
2663 and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
2664 and crp.business_group_id = pil.business_group_id
2665 and crp.init_evt_flag = 'Y'
2666 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2667 --
2668 -- 9690964
2669 --
2670 cursor c_get_quald_bnf is
2671 select cqb.*
2672 from ben_cbr_quald_bnf cqb
2673 ,ben_cbr_per_in_ler crp
2674 ,ben_per_in_ler pil
2675 where cqb.quald_bnf_person_id = p_person_id
2676 and cqb.business_group_id = p_business_group_id
2677 /* and p_lf_evt_ocrd_dt
2678 between cqb.cbr_elig_perd_strt_dt
2679 and cqb.cbr_elig_perd_end_dt */
2680 and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
2681 and crp.init_evt_flag = 'Y'
2682 -- and cqb.quald_bnf_flag = 'N'
2683 and crp.per_in_ler_id = pil.per_in_ler_id
2684 and crp.business_group_id = cqb.business_group_id
2685 and crp.business_group_id = pil.business_group_id
2686 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2687 order by cqb.cbr_elig_perd_strt_dt desc
2688 ;
2689 --
2690 cursor c_get_emp_person_id is
2691 select ctr.person_id
2692 from per_contact_relationships ctr
2693 where ctr.contact_person_id = p_person_id
2694 and p_lf_evt_ocrd_dt
2695 between nvl(ctr.date_start,hr_api.g_sot) and
2696 nvl(ctr.date_end,hr_api.g_eot)
2697 and ctr.business_group_id = p_business_group_id;
2698 --
2699 -- End 9690964
2700 --
2701 l_crp_rec c_check_cbr_per_in_ler%rowtype;
2702 l_crp2_rec c_get_cvrd_emp_pil%rowtype;
2703 l_cqb_rec c_get_quald_bnf%rowtype;
2704 begin
2705 --
2706 hr_utility.set_location('Entering : ' || l_proc, 10);
2707 --
2708 l_cbr_elig_perd_end_dt := p_cbr_elig_perd_end_dt;
2709 --
2710 if p_cbr_quald_bnf_id is null then
2711 --
2712 hr_utility.set_location('New Event : ' || l_proc, 10);
2713 --
2714 -- New qualifying event.
2715 --
2716 if p_cvrd_emp_person_id is null then
2717 l_effective_date := p_lf_evt_ocrd_dt -1;
2718 --
2719 -- Get covered employee id.
2720 --
2721 open c_chk_cvrd_emp;
2722 fetch c_chk_cvrd_emp into l_exists;
2723 if c_chk_cvrd_emp%found then
2724 l_cvrd_emp_person_id := p_person_id;
2725 --
2726 else
2727 -- Person may be a dependent. Get covered employee id
2728 --
2729 open c_get_cvrd_emp;
2730 fetch c_get_cvrd_emp into l_cvrd_emp_person_id;
2731 if c_get_cvrd_emp%notfound then
2732 --
2733 -- 9690964 - arra Check if person has
2734 -- a qualified beneficiary row from the
2735 -- initial qualifying event.
2736 --
2737 hr_utility.set_location('Covered Employee Not found : ' || l_proc, 10);
2738 open c_get_quald_bnf;
2739 fetch c_get_quald_bnf into l_cqb_rec;
2740 if c_get_quald_bnf%found then
2741 l_cvrd_emp_person_id := l_cqb_rec.cvrd_emp_person_id;
2742 else
2743 --
2744 -- If qualified beneficiary not found, then
2745 -- then person is a dependent.
2746 -- get the person_id for the contact.
2747 --
2748 open c_get_emp_person_id;
2749 fetch c_get_emp_person_id into l_cvrd_emp_person_id;
2750 close c_get_emp_person_id;
2751 end if;
2752 close c_get_quald_bnf;
2753 end if; -- c_get_cvrd_emp
2754 close c_get_cvrd_emp;
2755 end if;
2756 --
2757 close c_chk_cvrd_emp;
2758 else
2759 l_cvrd_emp_person_id := p_cvrd_emp_person_id;
2760 end if;
2761 hr_utility.set_location('Covered Employee : ' || l_cvrd_emp_person_id, 10);
2762 --
2763 if p_quald_bnf_flag = 'Y' then
2764 if p_person_id <> l_cvrd_emp_person_id then
2765 --
2766 -- If disability extension applies, check if the covered
2767 -- employee cobra eligibility end date is greater than the
2768 -- dependent end date. If it is, set the cobra eligibility
2769 -- end date to equal the dependent end date.
2770 --
2771 open c_get_cvrd_emp_end_date;
2772 fetch c_get_cvrd_emp_end_date into l_cvrd_emp_end_date;
2773 close c_get_cvrd_emp_end_date;
2774 if l_cvrd_emp_end_date > p_cbr_elig_perd_end_dt then
2775 l_cbr_elig_perd_end_dt := l_cvrd_emp_end_date;
2776 end if;
2777 end if;
2778 end if;
2779 --
2780 -- Write cobra qualified beneficiary row.
2781 --
2782 hr_utility.set_location('Inserting quald bnf: ' || l_proc, 10);
2783 hr_utility.set_location('l_cbr_elig_perd_end_dt: ' || l_cbr_elig_perd_end_dt, 10);
2784 --
2785 ben_cbr_quald_bnf_api.create_cbr_quald_bnf
2786 (p_validate => p_validate
2787 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2788 ,p_quald_bnf_flag => p_quald_bnf_flag
2789 ,p_cbr_elig_perd_strt_dt => p_cbr_elig_perd_strt_dt
2790 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2791 ,p_quald_bnf_person_id => p_person_id
2792 ,p_cvrd_emp_person_id => l_cvrd_emp_person_id
2793 ,p_pgm_id => p_pgm_id
2794 ,p_ptip_id => p_ptip_id
2795 ,p_pl_typ_id => p_pl_typ_id
2796 ,p_business_group_id => p_business_group_id
2797 ,p_object_version_number => l_object_version_number
2798 ,p_effective_date => p_effective_date
2799 );
2800 --
2801 if p_quald_bnf_flag = 'Y' then
2802 --
2803 -- Write COBRA per in ler row.
2804 --
2805 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2806 (p_validate => p_validate
2807 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2808 ,p_init_evt_flag => 'Y'
2809 ,p_cnt_num => 1
2810 ,p_per_in_ler_id => p_per_in_ler_id
2811 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2812 ,p_prvs_elig_perd_end_dt => null
2813 ,p_business_group_id => p_business_group_id
2814 ,p_object_version_number => l_object_version_number
2815 ,p_effective_date => p_effective_date
2816 );
2817 --
2818 if p_dsbld_apls then
2819 for l_dpnt_rec in c_get_all_quald_dpnts loop
2820 if l_cbr_elig_perd_end_dt > l_dpnt_rec.cbr_elig_perd_end_dt
2821 then
2822 --
2823 l_object_version_number := l_dpnt_rec.object_version_number;
2824 --
2825 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2826 (p_validate => p_validate
2827 ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2828 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2829 ,p_business_group_id => p_business_group_id
2830 ,p_object_version_number => l_object_version_number
2831 ,p_effective_date => p_effective_date
2832 );
2833 end if;
2834 end loop;
2835 end if;
2836 --
2837 else -- If person is not a qualified beneficiary.
2838 --
2839 hr_utility.set_location('Not a qualified beneficiary: '||l_cvrd_emp_person_id, 10);
2840 --
2841 -- Create a COBRA per in ler id using the covered employee initial
2842 -- life events. This is needed since the form now has to check
2843 -- if the per_in_ler is backed out before it displays the qualified
2844 -- beneficiary row.
2845 --
2846 open c_get_cvrd_emp_pil(l_cvrd_emp_person_id);
2847 fetch c_get_cvrd_emp_pil into l_crp2_rec;
2848 if c_get_cvrd_emp_pil%found then
2849 close c_get_cvrd_emp_pil;
2850 --
2851 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2852 (p_validate => p_validate
2853 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2854 ,p_init_evt_flag => 'Y'
2855 ,p_cnt_num => 1
2856 ,p_per_in_ler_id => l_crp2_rec.per_in_ler_id
2857 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
2858 ,p_prvs_elig_perd_end_dt => null
2859 ,p_business_group_id => p_business_group_id
2860 ,p_object_version_number => l_object_version_number
2861 ,p_effective_date => p_effective_date
2862 );
2863 else
2864 hr_utility.set_location('Did not find covered employee: '||l_cvrd_emp_person_id, 10);
2865 hr_utility.set_location('ptip_id : '||p_ptip_id, 10);
2866 hr_utility.set_location('pgm_id : '||p_pgm_id, 10);
2867 close c_get_cvrd_emp_pil;
2868 end if;
2869 --
2870 end if;
2871 else -- Qualified beneficiary found.
2872 l_cvrd_emp_person_id := p_cvrd_emp_person_id;
2873 --
2874 -- Only update the COBRA qualified beneficiary end date if it has
2875 -- changed.
2876 --
2877 if p_quald_bnf_flag = 'Y' then
2878 --
2879 l_init_evt := chk_init_evt(p_per_in_ler_id => p_per_in_ler_id
2880 ,p_business_group_id => p_business_group_id
2881 );
2882 if l_init_evt then
2883 --
2884 -- The qualified beneficiary row for each dependent is written at
2885 -- the time of the qualifying event. If the dependent is designated,
2886 -- the disability date calculated is not overridden.
2887 --
2888 if p_person_id <> l_cvrd_emp_person_id then
2889 --
2890 -- If person already has an initial qualifying event.
2891 -- 9294528.
2892 --
2893 open c_chk_init_evt;
2894 fetch c_chk_init_evt into l_exists;
2895 if c_chk_init_evt%notfound then
2896 --
2897 -- If disability extension applies, check if the covered
2898 -- employee cobra eligibility end date is greater than the
2899 -- dependent end date. If it is, set the cobra eligibility
2900 -- end date to equal the dependent end date.
2901 --
2902 open c_get_cvrd_emp_end_date;
2903 fetch c_get_cvrd_emp_end_date into l_cvrd_emp_end_date;
2904 close c_get_cvrd_emp_end_date;
2905 if l_cvrd_emp_end_date > l_cbr_elig_perd_end_dt then
2906 l_cbr_elig_perd_end_dt := l_cvrd_emp_end_date;
2907 end if;
2908 end if; -- chk_init evt
2909 end if;
2910 end if;
2911 --
2912 l_object_version_number := p_cqb_object_version_number;
2913 --
2914 hr_utility.set_location('l_cbr_elig_perd_end_dt : '||l_cbr_elig_perd_end_dt, 10);
2915 hr_utility.set_location('p_old_cbr_elig_perd_end_dt : '||p_old_cbr_elig_perd_end_dt, 10);
2916 if l_cbr_elig_perd_end_dt is not null then
2917 --
2918 if p_old_cbr_elig_perd_end_dt <> l_cbr_elig_perd_end_dt then
2919 --
2920 hr_utility.set_location('l_cbr_elig_perd_end_dt update: '||l_cbr_elig_perd_end_dt, 10);
2921 --
2922 --
2923 -- If the cobra eligibility start date is the start of time (01/01/0001), set the
2924 -- qualified beneficiary flag to 'N' instead of updating the eligiblity end date.
2925 -- Bug 4486609
2926 --
2927 if l_cbr_elig_perd_end_dt = hr_api.g_sot then
2928 l_quald_bnf_flag := 'N';
2929 l_cbr_elig_perd_end_dt := p_old_cbr_elig_perd_end_dt;
2930 else
2931 l_quald_bnf_flag := p_quald_bnf_flag;
2932 end if;
2933 --
2934 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2935 (p_validate => p_validate
2936 ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
2937 ,p_quald_bnf_flag => l_quald_bnf_flag
2938 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2939 ,p_cbr_inelg_rsn_cd => p_cbr_inelg_rsn_cd
2940 ,p_business_group_id => p_business_group_id
2941 ,p_object_version_number => l_object_version_number
2942 ,p_effective_date => p_effective_date
2943 );
2944 --
2945 if p_dsbld_apls = true then
2946 hr_utility.set_location('Disabled: ' || l_cbr_elig_perd_end_dt, 10);
2947 for l_dpnt_rec in c_get_all_quald_dpnts loop
2948 if l_cbr_elig_perd_end_dt > l_dpnt_rec.cbr_elig_perd_end_dt
2949 then
2950 hr_utility.set_location('Dpnt dsbld : ' || l_cbr_elig_perd_end_dt, 10);
2951 --
2952 l_object_version_number := l_dpnt_rec.object_version_number;
2953 --
2954 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2955 (p_validate => p_validate
2956 ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2957 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
2958 ,p_business_group_id => p_business_group_id
2959 ,p_object_version_number => l_object_version_number
2960 ,p_effective_date => p_effective_date
2961 );
2962 --
2963 -- If it is not the initial event, also write a cobra
2964 -- per_in_ler.
2965 --
2966 if l_init_evt = false then
2967 --
2968 -- Get the last count number from last event.
2969 --
2970 open c_get_cnt_num(l_dpnt_rec.cbr_quald_bnf_id);
2971 fetch c_get_cnt_num into l_cnt_num;
2972 close c_get_cnt_num;
2973 --
2974 hr_utility.set_location('l_cnt_num : ' || l_cnt_num, 10);
2975 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
2976 (p_validate => p_validate
2977 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
2978 ,p_init_evt_flag => 'N'
2979 ,p_cnt_num => l_cnt_num + 1
2980 ,p_per_in_ler_id => p_per_in_ler_id
2981 ,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
2982 ,p_prvs_elig_perd_end_dt => l_dpnt_rec.cbr_elig_perd_end_dt
2983 ,p_business_group_id => p_business_group_id
2984 ,p_object_version_number => l_object_version_number
2985 ,p_effective_date => p_effective_date
2986 );
2987 end if;
2988 end if;
2989 end loop;
2990 end if;
2991 end if;
2992 --
2993 -- If person is a qualified beneficiary, check if it is a new
2994 -- qualifying life event or an event where there is a change in
2995 -- the cobra eligibility end date. If it is write a new cobra
2996 -- per in ler for the person.
2997 --
2998 -- Check if the cobra per_in_ler exist for the person.
2999 --
3000 open c_check_cbr_per_in_ler;
3001 fetch c_check_cbr_per_in_ler into l_crp_rec;
3002 if c_check_cbr_per_in_ler%notfound then
3003 close c_check_cbr_per_in_ler;
3004 --
3005 -- Check if it is a cobra qualifying event.
3006 --
3007 -- open c_get_qualg_evt;
3008 -- fetch c_get_qualg_evt into l_exists;
3009 -- if (c_get_qualg_evt%found or
3010 if (p_old_cbr_elig_perd_end_dt <> p_cbr_elig_perd_end_dt) then
3011 -- close c_get_qualg_evt;
3012 --
3013 -- Get the last count number from last event.
3014 --
3015 open c_get_cnt_num(p_cbr_quald_bnf_id);
3016 fetch c_get_cnt_num into l_cnt_num;
3017 close c_get_cnt_num;
3018 --
3019 hr_utility.set_location('l_cnt_num : ' || l_cnt_num, 10);
3020 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
3021 (p_validate => p_validate
3022 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
3023 ,p_init_evt_flag => 'N'
3024 ,p_cnt_num => l_cnt_num + 1
3025 ,p_per_in_ler_id => p_per_in_ler_id
3026 ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
3027 ,p_prvs_elig_perd_end_dt => p_old_cbr_elig_perd_end_dt
3028 ,p_business_group_id => p_business_group_id
3029 ,p_object_version_number => l_object_version_number
3030 ,p_effective_date => p_effective_date
3031 );
3032 /* else
3033 close c_get_qualg_evt; */
3034 end if;
3035 else
3036 close c_check_cbr_per_in_ler;
3037 end if;
3038 --
3039 end if; -- end date is not null
3040 end if; -- qual_bnf flag = 'Y'
3041 end if;
3042 hr_utility.set_location('Leaving : ' || l_proc, 10);
3043 end update_cobra_info;
3044 -- ----------------------------------------------------------------------------
3045 -- |-------------------------< end_prtt_cobra_eligibility >-------------------------
3046 -- ----------------------------------------------------------------------------
3047 --
3048 procedure end_prtt_cobra_eligibility
3049 (p_per_in_ler_id in number
3050 ,p_person_id in number
3051 ,p_business_group_id in number
3052 ,p_effective_date in date
3053 ,p_validate in boolean default false) is
3054 --
3055 l_update boolean := false;
3056 l_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
3057 l_typ_cd ben_ler_f.typ_cd%type;
3058 l_cbr_inelg_rsn_cd ben_cbr_quald_bnf.cbr_inelg_rsn_cd%type;
3059 l_effective_date date;
3060 l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
3061 l_cbr_quald_bnf_id ben_cbr_quald_bnf.cbr_quald_bnf_id%type;
3062 l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3063 l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3064 l_cqb_quald_bnf_flag ben_cbr_quald_bnf.quald_bnf_flag%type;
3065 l_crp_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3066 l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%type;
3067 l_exists varchar2(1);
3068 l_proc varchar2(80) := g_package||'.end_prtt_cobra_eligibility';
3069 --
3070 cursor c_get_cbr_quald_bnf
3071 is
3072 select cqb.*
3073 from ben_cbr_quald_bnf cqb
3074 ,ben_cbr_per_in_ler crp
3075 ,ben_per_in_ler pil
3076 where cqb.quald_bnf_person_id = p_person_id
3077 and cqb.quald_bnf_flag = 'Y'
3078 and cqb.cbr_elig_perd_end_dt >= l_effective_date
3079 and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
3080 and cqb.business_group_id = p_business_group_id
3081 and crp.init_evt_flag = 'Y'
3082 and crp.per_in_ler_id = pil.per_in_ler_id
3083 and crp.business_group_id = cqb.business_group_id
3084 and pil.business_group_id = crp.business_group_id
3085 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3086 --
3087 -- Bug 12386329
3088 --
3089 cursor c_get_dpnt_cbr_quald_bnf(p_person_id in number)
3090 is
3091 select cqb.*
3092 from ben_cbr_quald_bnf cqb
3093 ,ben_cbr_per_in_ler crp
3094 ,ben_per_in_ler pil
3095 where cqb.quald_bnf_person_id = p_person_id
3096 and cqb.quald_bnf_flag = 'Y'
3097 and cqb.cbr_elig_perd_end_dt >= l_effective_date
3098 and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
3099 and cqb.business_group_id = p_business_group_id
3100 and crp.init_evt_flag = 'Y'
3101 and crp.per_in_ler_id = pil.per_in_ler_id
3102 and crp.business_group_id = cqb.business_group_id
3103 and pil.business_group_id = crp.business_group_id
3104 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3105 --
3106 -- end 12386329
3107 --
3108 cursor c_get_cnt_num is
3109 select max(crp.cnt_num)
3110 from ben_cbr_per_in_ler crp
3111 ,ben_per_in_ler pil
3112 where crp.cbr_quald_bnf_id = l_cbr_quald_bnf_id
3113 and crp.business_group_id = p_business_group_id
3114 and crp.per_in_ler_id = pil.per_in_ler_id
3115 and crp.business_group_id = pil.business_group_id
3116 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3117 --
3118 cursor c_get_ler_type is
3119 select ler.typ_cd
3120 from ben_ler_f ler
3121 ,ben_per_in_ler pil
3122 where ler.ler_id = pil.ler_id
3123 and pil.per_in_ler_id = p_per_in_ler_id
3124 and ler.business_group_id = p_business_group_id
3125 and ler.business_group_id = pil.business_group_id
3126 and p_effective_date
3127 between ler.effective_start_date and
3128 ler.effective_end_date;
3129 --
3130 cursor c_chk_elctbl_chc(p_pgm_id in number
3131 ,p_ptip_id in number
3132 ) is
3133 select null
3134 from ben_elig_per_elctbl_chc chc
3135 where chc.pgm_id = p_pgm_id
3136 and chc.ptip_id = nvl(p_ptip_id, chc.ptip_id)
3137 and chc.elctbl_flag = 'Y'
3138 and chc.per_in_ler_id = p_per_in_ler_id
3139 and chc.business_group_id = p_business_group_id;
3140 --
3141 -- Bug 12386329
3142 --
3143 cursor c_get_cvrd_dpnt is
3144 select distinct pdp.dpnt_person_id
3145 from ben_elig_cvrd_dpnt_f pdp
3146 where pdp.per_in_ler_id = p_per_in_ler_id
3147 and l_lf_evt_ocrd_dt
3148 between pdp.effective_start_date and pdp.effective_end_date
3149 and pdp.cvg_thru_dt <> hr_api.g_eot
3150 and pdp.business_group_id = p_business_group_id;
3151 --
3152 -- end 12386329
3153 --
3154 -- l_cqb_rec c_get_cbr_quald_bnf%rowtype;
3155 --
3156 begin
3157 g_cobra_enrollment_change := FALSE;
3158 --
3159 hr_utility.set_location('Entering : ' || l_proc, 10);
3160 --
3161 -- Get life event occurred date.
3162 --
3163 l_lf_evt_ocrd_dt := get_lf_evt_ocrd_dt
3164 (p_per_in_ler_id => p_per_in_ler_id
3165 ,p_business_group_id => p_business_group_id
3166 );
3167 --
3168 l_effective_date := (nvl(l_lf_evt_ocrd_dt,p_effective_date)) + 1;
3169 --
3170 for l_cqb_rec in c_get_cbr_quald_bnf loop
3171 l_cbr_quald_bnf_id := l_cqb_rec.cbr_quald_bnf_id;
3172 l_cqb_object_version_number := l_cqb_rec.object_version_number;
3173 l_cqb_quald_bnf_flag := l_cqb_rec.quald_bnf_flag;
3174 hr_utility.set_location('l_effective_date: ' || l_effective_date, 10);
3175 hr_utility.set_location('cqb_ptip_id: ' || l_cqb_rec.ptip_id, 10);
3176 hr_utility.set_location('cqb_pgm_id: ' || l_cqb_rec.pgm_id, 10);
3177 --
3178 -- If life event occurred date + 1 = to the cobra eligibility
3179 -- end date, we are processing a loss of eligibility event
3180 -- or the max period of enrollment is reached so the COBRA
3181 -- eligibility end date is correct. No need to proceed.
3182 --
3183 if l_cqb_rec.cbr_elig_perd_strt_dt = l_effective_date then
3184 return;
3185 end if;
3186 --
3187 -- If person is not enrolled or covered in the COBRA
3188 -- program then update his/her cobra eligibility end date.
3189 --
3190 if (chk_enrld_or_cvrd
3191 (p_pgm_id => l_cqb_rec.pgm_id
3192 ,p_ptip_id => l_cqb_rec.ptip_id
3193 ,p_person_id => p_person_id
3194 ,p_effective_date => p_effective_date
3195 ,p_business_group_id => p_business_group_id)) = false then
3196 hr_utility.set_location('Not enrolled: ', 10);
3197 --
3198 -- if person is not enroll then check if the person has no electable choices
3199 -- a COBRA program.
3200 --
3201 open c_chk_elctbl_chc(l_cqb_rec.pgm_id
3202 ,l_cqb_rec.ptip_id
3203 );
3204 fetch c_chk_elctbl_chc into l_exists;
3205 if c_chk_elctbl_chc%notfound then
3206 close c_chk_elctbl_chc;
3207 --
3208 hr_utility.set_location('No choices found: ', 10);
3209 --
3210 l_cbr_elig_perd_end_dt := get_max_cvg_thru_dt
3211 (p_person_id => p_person_id
3212 ,p_lf_evt_ocrd_dt => l_effective_date
3213 ,p_pgm_id => l_cqb_rec.pgm_id
3214 ,p_ptip_id => l_cqb_rec.ptip_id
3215 ,p_per_in_ler_id => p_per_in_ler_id
3216 ,p_effective_date => p_effective_date
3217 ,p_business_group_id => p_business_group_id
3218 );
3219 hr_utility.set_location('l_cbr_elig_perd_end_dt : ' || l_cbr_elig_perd_end_dt, 10);
3220
3221 --
3222 -- Write a cobra per_in_ler row for the person if the cobra eligibility
3223 -- period end date is not equal to the cobra eligibility end date
3224 -- on the qualified beneficiary row.
3225 --
3226 if l_cqb_rec.cbr_elig_perd_end_dt <> l_cbr_elig_perd_end_dt then
3227 if l_cbr_elig_perd_end_dt = hr_api.g_sot then
3228 l_cqb_quald_bnf_flag := 'N';
3229 l_cbr_elig_perd_end_dt := l_cqb_rec.cbr_elig_perd_end_dt;
3230 end if;
3231 --
3232 -- If life event is not a cobra temporal event, set the ineligibility
3233 -- reason to PLE i.e. preceding life event e.g. rehire so a max period
3234 -- of enrollment event is not triggered.
3235 --
3236 open c_get_ler_type;
3237 fetch c_get_ler_type into l_typ_cd;
3238 if c_get_ler_type%found then
3239 if l_typ_cd not in
3240 ('DRVDLSELG', 'DRVDNLP','DRVDPOEELG',
3241 'DRVDPOERT','DRVDVEC') then
3242 l_cbr_inelg_rsn_cd := 'PLE';
3243 else
3244 l_cbr_inelg_rsn_cd := hr_api.g_varchar2;
3245 end if;
3246 hr_utility.set_location('l_ler_typ: '||l_typ_cd, 10);
3247 end if;
3248 close c_get_ler_type;
3249 --
3250 end_cobra_eligibility
3251 (p_per_in_ler_id => p_per_in_ler_id
3252 ,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
3253 ,p_cqb_object_version_number => l_cqb_object_version_number
3254 ,p_quald_bnf_flag => l_cqb_quald_bnf_flag
3255 ,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
3256 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
3257 ,p_cbr_inelg_rsn_cd => l_cbr_inelg_rsn_cd
3258 ,p_business_group_id => p_business_group_id
3259 ,p_effective_date => p_effective_date
3260 );
3261 --
3262 -- Bug 12386329. End cobra eligibility for covered dependents.
3263 --
3264 for l_pdp_rec in c_get_cvrd_dpnt loop
3265 hr_utility.set_location('l_pdp_rec: '||l_pdp_rec.dpnt_person_id, 10);
3266 --
3267 -- Get the cobra qualified beneficiary row for the dependent
3268 --
3269 for l_dpnt_cqb_rec in c_get_dpnt_cbr_quald_bnf(l_pdp_rec.dpnt_person_id) loop
3270 hr_utility.set_location('l_cqb: '||l_dpnt_cqb_rec.quald_bnf_person_id, 10);
3271 --
3272 -- If person is no longer enrolled or covered in the COBRA program
3273 -- or plan type in program,then set the cobra eligibility end date
3274 -- to the maximum coverage end date of the enrollment results.
3275 --
3276 if (chk_enrld_or_cvrd
3277 (p_pgm_id => l_dpnt_cqb_rec.pgm_id
3278 ,p_ptip_id => l_dpnt_cqb_rec.ptip_id
3279 ,p_person_id => l_dpnt_cqb_rec.quald_bnf_person_id
3280 ,p_effective_date => p_effective_date
3281 ,p_business_group_id => p_business_group_id)) = false then
3282 --
3283 -- The maximum enrollment coverage end date is the
3284 -- cobra eligibility end date for the person.
3285 --
3286 l_cbr_elig_perd_end_dt
3287 := get_max_cvg_thru_dt
3288 (p_person_id => l_dpnt_cqb_rec.quald_bnf_person_id
3289 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
3290 ,p_pgm_id => l_dpnt_cqb_rec.pgm_id
3291 ,p_ptip_id => l_dpnt_cqb_rec.ptip_id
3292 ,p_per_in_ler_id => p_per_in_ler_id
3293 ,p_business_group_id => p_business_group_id
3294 ,p_effective_date => p_effective_date
3295 );
3296 --
3297 if l_cbr_elig_perd_end_dt <> hr_api.g_sot then
3298 update_cobra_info
3299 (p_per_in_ler_id => p_per_in_ler_id
3300 ,p_person_id => l_dpnt_cqb_rec.quald_bnf_person_id
3301 ,p_cbr_quald_bnf_id => l_dpnt_cqb_rec.cbr_quald_bnf_id
3302 ,p_cqb_object_version_number => l_dpnt_cqb_rec.object_version_number
3303 ,p_cbr_elig_perd_strt_dt => l_dpnt_cqb_rec.cbr_elig_perd_strt_dt
3304 ,p_old_cbr_elig_perd_end_dt => l_dpnt_cqb_rec.cbr_elig_perd_end_dt
3305 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
3306 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
3307 ,p_cvrd_emp_person_id => l_dpnt_cqb_rec.cvrd_emp_person_id
3308 ,p_business_group_id => p_business_group_id
3309 ,p_effective_date => p_effective_date
3310 ,p_validate => p_validate
3311 );
3312 end if;
3313 end if; -- end enrld in pgm or ptip.
3314 end loop; -- c_get_cvrd_dpnt
3315 end loop; -- c_get_dpnt_cbr_quald_bnf
3316 -- end 12386329
3317 end if;
3318 --
3319 else
3320 hr_utility.set_location('Choices found: ', 10);
3321 close c_chk_elctbl_chc;
3322 end if;
3323 end if;
3324 end loop;
3325 --
3326 hr_utility.set_location('Leaving : ' || l_proc, 10);
3327 end end_prtt_cobra_eligibility;
3328 --
3329 -- ----------------------------------------------------------------------------
3330 -- |-------------------------< end_cobra_eligibility >-------------------------
3331 -- ----------------------------------------------------------------------------
3332 --
3333 procedure end_cobra_eligibility
3334 (p_per_in_ler_id in number
3335 ,p_cbr_quald_bnf_id in number
3336 ,p_cqb_object_version_number in number
3337 ,p_quald_bnf_flag in varchar2 default 'Y'
3338 ,p_old_cbr_elig_perd_end_dt in date
3339 ,p_cbr_elig_perd_end_dt in date
3340 ,p_cbr_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
3341 ,p_business_group_id in number
3342 ,p_effective_date in date
3343 ,p_validate in boolean default false) is
3344 --
3345 l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
3346 l_cqb_object_version_number ben_cbr_quald_bnf.object_version_number%type;
3347 l_crp_object_version_number ben_cbr_per_in_ler.object_version_number%type;
3348 l_cbr_per_in_ler_id ben_cbr_per_in_ler.cbr_per_in_ler_id%type;
3349 l_cnt_num ben_cbr_per_in_ler.cnt_num%type;
3350 l_exists varchar2(1);
3351 l_proc varchar2(80) := g_package||'.end_cobra_eligibility';
3352 --
3353 cursor c_get_cnt_num is
3354 select max(crp.cnt_num)
3355 from ben_cbr_per_in_ler crp
3356 ,ben_per_in_ler pil
3357 where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3358 and crp.business_group_id = p_business_group_id
3359 and crp.per_in_ler_id = pil.per_in_ler_id
3360 and crp.business_group_id = pil.business_group_id
3361 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3362
3363 --
3364 cursor c_chk_cbr_per_in_ler is
3365 select null
3366 from ben_cbr_per_in_ler crp
3367 where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3368 and crp.per_in_ler_id = p_per_in_ler_id;
3369 --
3370 begin
3371 --
3372 hr_utility.set_location('Entering : ' || l_proc, 10);
3373 --
3374 -- If cobra per in ler exists, update the row.
3375 --
3376 open c_chk_cbr_per_in_ler;
3377 fetch c_chk_cbr_per_in_ler into l_exists;
3378 if c_chk_cbr_per_in_ler%notfound then
3379 close c_chk_cbr_per_in_ler;
3380 --
3381 --
3382 -- Get the last count number from last event.
3383 --
3384 open c_get_cnt_num;
3385 fetch c_get_cnt_num into l_cnt_num;
3386 close c_get_cnt_num;
3387 --
3388 ben_cbr_per_in_ler_api.create_cbr_per_in_ler
3389 (p_validate => p_validate
3390 ,p_cbr_per_in_ler_id => l_cbr_per_in_ler_id
3391 ,p_init_evt_flag => 'N'
3392 ,p_cnt_num => l_cnt_num + 1
3393 ,p_per_in_ler_id => p_per_in_ler_id
3394 ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
3395 ,p_prvs_elig_perd_end_dt => p_old_cbr_elig_perd_end_dt
3396 ,p_business_group_id => p_business_group_id
3397 ,p_object_version_number => l_crp_object_version_number
3398 ,p_effective_date => p_effective_date
3399 );
3400 else
3401 close c_chk_cbr_per_in_ler;
3402 end if;
3403 --
3404 l_cqb_object_version_number := p_cqb_object_version_number;
3405 --
3406 hr_utility.set_location('p_cbr_elig_perd_end_dt'||p_cbr_elig_perd_end_dt, 20);
3407 hr_utility.set_location('p_quald_bnf_flag'||p_quald_bnf_flag, 30);
3408 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
3409 (p_validate => p_validate
3410 ,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
3411 ,p_quald_bnf_flag => p_quald_bnf_flag
3412 ,p_cbr_elig_perd_end_dt => p_cbr_elig_perd_end_dt
3413 ,p_cbr_inelg_rsn_cd => p_cbr_inelg_rsn_cd
3414 ,p_business_group_id => p_business_group_id
3415 ,p_object_version_number => l_cqb_object_version_number
3416 ,p_effective_date => p_effective_date
3417 );
3418 --
3419 hr_utility.set_location('Leaving : ' || l_proc, 10);
3420 end end_cobra_eligibility;
3421 --
3422 --
3423 -- ----------------------------------------------------------------------------
3424 -- |-------------------------< do_rounding >-----------------------------------
3425 -- ----------------------------------------------------------------------------
3426 --
3427 function do_rounding(p_rndg_cd varchar2,
3428 p_rndg_rl number,
3429 p_amount number,
3430 p_effective_date date
3431 )
3432 return number is
3433 l_return_amt number;
3434 begin
3435 if (p_rndg_cd is not null or
3436 p_rndg_rl is not null) and
3437 p_amount is not null then
3438 --
3439 l_return_amt := benutils.do_rounding
3440 (p_rounding_cd => p_rndg_cd,
3441 p_rounding_rl => p_rndg_rl,
3442 p_value => p_amount,
3443 p_effective_date => p_effective_date);
3444 --
3445 elsif p_amount<>0 and
3446 p_amount is not null then
3447 --
3448 -- for now later do based on currency precision.
3449 --
3450 l_return_amt:=round(p_amount,2);
3451 else
3452 l_return_amt:=nvl(p_amount,0);
3453 end if;
3454
3455 return l_return_amt;
3456
3457 end do_rounding;
3458 --
3459 -- ----------------------------------------------------------------------------
3460 -- |-------------------------< compute_period_rate >---------------------------
3461 -- ----------------------------------------------------------------------------
3462 --
3463 function compute_period_rate(p_rt_strt_dt date
3464 ,p_rt_end_dt date
3465 ,p_ann_rt_val number
3466 ,p_rate_ytd number
3467 ,p_rndg_cd number
3468 ,p_rndg_rl number
3469 ,p_plan_year_end_dt date)
3470 return number is
3471 l_return_amt number;
3472 l_balance number;
3473 l_per_month_amt number;
3474 l_first_pp_adjustment number;
3475 l_months_remaining number;
3476 l_notional_months number;
3477
3478 l_proc varchar2(80) := g_package||'.compute_period_rate';
3479 begin
3480
3481 hr_utility.set_location('Entering : ' || l_proc, 10);
3482
3483 l_balance := p_ann_rt_val - p_rate_ytd;
3484
3485 l_notional_months := MONTHS_BETWEEN(LAST_DAY(p_plan_year_end_dt),
3486 ADD_MONTHS(LAST_DAY(p_rt_strt_dt),-1));
3487
3488 l_per_month_amt := l_balance/l_notional_months;
3489
3490 l_months_remaining := TRUNC(MONTHS_BETWEEN( LEAST(LAST_DAY(p_plan_year_end_dt),p_rt_end_dt),
3491 ADD_MONTHS(LAST_DAY(p_rt_strt_dt),-1)));
3492
3493 l_per_month_amt := do_rounding(p_rndg_cd => p_rndg_cd,
3494 p_rndg_rl => p_rndg_rl,
3495 p_amount => l_per_month_amt,
3496 p_effective_date => p_rt_strt_dt
3497 );
3498
3499 l_first_pp_adjustment:= l_balance - (l_per_month_amt * l_notional_months);
3500
3501 l_return_amt := l_first_pp_adjustment + (l_months_remaining * l_per_month_amt);
3502
3503 hr_utility.set_location('l_return_amt'|| l_return_amt, 10);
3504 hr_utility.set_location('Leaving : ' || l_proc, 10);
3505
3506 return l_return_amt;
3507
3508 end compute_period_rate;
3509 --
3510 -- ----------------------------------------------------------------------------
3511 -- |-------------------------< get_amount_due >--------------------------------
3512 -- ----------------------------------------------------------------------------
3513 --
3514 procedure get_amount_due
3515 (p_person_id in number
3516 ,p_business_group_id in number
3517 ,p_assignment_id in number
3518 ,p_payroll_id in number
3519 ,p_organization_id in number
3520 ,p_effective_date in date
3521 ,p_prtt_enrt_rslt_id in number
3522 ,p_acty_base_rt_id in number
3523 ,p_ann_rt_val in number
3524 ,p_mlt_cd in varchar2
3525 ,p_rt_strt_dt in date
3526 ,p_rt_end_dt in date
3527 ,p_first_month_amt out nocopy number
3528 ,p_per_month_amt out nocopy number
3529 ,p_last_month_amt out nocopy number
3530 )
3531 is
3532
3533 cursor c_current_result_info (c_prtt_enrt_rslt_id in number) is
3534 select pen.prtt_enrt_rslt_id,
3535 pen.pl_id,
3536 opt.opt_id,
3537 pen.pgm_id,
3538 pen.ler_id,
3539 pen.pl_typ_id,
3540 pen.person_id,
3541 pen.effective_start_date,
3542 pen.effective_end_date
3543 from ben_prtt_enrt_rslt_f pen,
3544 ben_oipl_f opt
3545 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3546 and opt.oipl_id(+)=pen.oipl_id
3547 and pen.prtt_enrt_rslt_stat_cd is null
3548 and pen.enrt_cvg_strt_dt between opt.effective_start_date(+)
3549 and opt.effective_end_date(+)
3550 order by pen.effective_start_date desc;
3551
3552 cursor get_abr_info (c_acty_base_rt_id in number
3553 ,c_effective_date in date) is
3554 select abr.prtl_mo_det_mthd_cd,
3555 abr.prtl_mo_det_mthd_rl,
3556 abr.wsh_rl_dy_mo_num,
3557 abr.prtl_mo_eff_dt_det_cd,
3558 abr.prtl_mo_eff_dt_det_rl,
3559 abr.rndg_cd,
3560 abr.rndg_rl,
3561 abr.ele_rqd_flag,
3562 abr.one_ann_pymt_cd,
3563 abr.entr_ann_val_flag,
3564 abr.use_calc_acty_bs_rt_flag,
3565 abr.acty_typ_cd,
3566 abr.input_va_calc_rl,
3567 abr.rt_typ_cd,
3568 abr.element_type_id,
3569 abr.input_value_id,
3570 abr.ele_entry_val_cd,
3571 abr.rt_mlt_cd,
3572 abr.parnt_chld_cd,
3573 abr.rcrrg_cd,
3574 abr.name
3575 from ben_acty_base_rt_f abr
3576 where abr.acty_base_rt_id=c_acty_base_rt_id
3577 and c_effective_date between abr.effective_start_date
3578 and abr.effective_end_date;
3579
3580 -- Parent rate information
3581 cursor c_abr2
3582 (c_effective_date in date,
3583 c_acty_base_rt_id in number
3584 )
3585 is
3586 select abr2.rt_mlt_cd,
3587 abr2.entr_ann_val_flag
3588 from ben_acty_base_rt_f abr,
3589 ben_acty_base_rt_f abr2
3590 where abr.acty_base_rt_id = c_acty_base_rt_id
3591 and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
3592 and abr2.parnt_chld_cd = 'PARNT'
3593 and c_effective_date
3594 between abr.effective_start_date
3595 and abr.effective_end_date
3596 and c_effective_date
3597 between abr2.effective_start_date
3598 and abr2.effective_end_date;
3599
3600 cursor c_plan_year_end_for_pen
3601 (c_prtt_enrt_rslt_id in number
3602 ,c_rate_start_or_end_dt in date
3603 ,c_effective_date in date
3604 )
3605 is
3606 select distinct
3607 yp.start_date,yp.end_date
3608 from ben_prtt_enrt_rslt_f pen,
3609 ben_popl_yr_perd pyp,
3610 ben_yr_perd yp
3611 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3612 and c_effective_date <= pen.effective_end_date
3613 and pyp.pl_id=pen.pl_id
3614 and yp.yr_perd_id=pyp.yr_perd_id
3615 and pen.prtt_enrt_rslt_stat_cd is null
3616 and c_rate_start_or_end_dt
3617 between yp.start_date and yp.end_date;
3618 --
3619
3620 CURSOR c_rates(c_person_id in number
3621 ,c_pgm_id in number
3622 ,c_acty_base_rt_id in number
3623 ,c_business_group_id in number
3624 ,c_cur_rt_strt_dt in date
3625 ,c_plan_year_strt_dt in date) is
3626 SELECT prv.rt_strt_dt
3627 ,prv.rt_end_dt
3628 ,prv.ann_rt_val
3629 FROM ben_prtt_enrt_rslt_f pen
3630 ,ben_prtt_rt_val prv
3631 WHERE pen.person_id = c_person_id
3632 AND pen.pgm_id = c_pgm_id
3633 AND pen.business_group_id = c_business_group_id
3634 AND pen.prtt_enrt_rslt_stat_cd IS NULL
3635 AND pen.sspndd_flag = 'N'
3636 AND pen.effective_end_date = hr_api.g_eot
3637 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3638 AND pen.business_group_id = prv.business_group_id
3639 AND prv.prtt_rt_val_stat_cd IS NULL
3640 AND prv.acty_base_rt_id = c_acty_base_rt_id
3641 AND prv.rt_strt_dt < c_cur_rt_strt_dt
3642 AND prv.rt_end_dt >= c_plan_year_strt_dt
3643 ORDER BY prv.rt_strt_dt;
3644
3645 l_mlt_cd ben_prtt_rt_val.mlt_cd%type;
3646
3647 l_per_month_amt number;
3648 l_prorated_first_mth_amt number;
3649 l_prorated_last_mth_amt number;
3650 l_result_rec c_current_result_info%rowtype;
3651 l_proration_flag varchar2(30):='N';
3652 l_jurisdiction_code varchar2(30);
3653 l_get_abr_info get_abr_info%rowtype;
3654
3655 l_prnt_abr c_abr2%rowtype ;
3656 l_months_remaining number;
3657 l_total_months number := 12;
3658 l_plan_year_end_dt date;
3659 l_plan_year_strt_dt date;
3660 l_prnt_ann_rt varchar2(30);
3661 l_first_pp_adjustment number;
3662 l_balance number;
3663
3664 l_first_rate boolean;
3665 l_rt_strt_dt date;
3666 l_rt_end_dt date;
3667 l_ann_rt_val number;
3668 l_rate_ytd number;
3669 l_proc varchar2(80) := g_package||'.get_amount_due';
3670 begin
3671
3672 hr_utility.set_location('Entering : ' || l_proc, 10);
3673
3674 open c_current_result_info(c_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id);
3675 fetch c_current_result_info into l_result_rec;
3676 close c_current_result_info;
3677
3678 open get_abr_info (c_acty_base_rt_id => p_acty_base_rt_id
3679 ,c_effective_date => p_effective_date);
3680 fetch get_abr_info into l_get_abr_info;
3681 close get_abr_info;
3682
3683 l_mlt_cd := nvl(p_mlt_cd,l_get_abr_info.rt_mlt_cd);
3684
3685 if l_get_abr_info.parnt_chld_cd = 'CHLD' then
3686 --
3687 open c_abr2 (p_effective_date, p_acty_base_rt_id);
3688 fetch c_abr2 into l_prnt_abr;
3689 if c_abr2%found then
3690 --
3691 if l_prnt_abr.rt_mlt_cd = 'SAREC' or
3692 l_prnt_abr.entr_ann_val_flag = 'Y' then
3693 l_prnt_ann_rt := 'Y';
3694 end if ;
3695 --
3696 end if;
3697 close c_abr2 ;
3698 end if;
3699
3700 open c_plan_year_end_for_pen
3701 (c_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3702 ,c_rate_start_or_end_dt => p_rt_strt_dt
3703 ,c_effective_date => p_effective_date
3704 );
3705 fetch c_plan_year_end_for_pen into l_plan_year_strt_dt, l_plan_year_end_dt;
3706 close c_plan_year_end_for_pen;
3707
3708 l_months_remaining := MONTHS_BETWEEN(LAST_DAY(l_plan_year_end_dt)
3709 ,ADD_MONTHS(LAST_DAY(p_rt_strt_dt),-1));
3710
3711 hr_utility.set_location('l_months_remaining ' || l_months_remaining, 10);
3712
3713 if l_get_abr_info.entr_ann_val_flag='Y' or
3714 l_prnt_ann_rt = 'Y' or
3715 l_mlt_cd = 'SAREC' then
3716
3717 l_first_rate := true;
3718 l_rate_ytd := 0;
3719 for r_rates in c_rates(c_person_id => p_person_id
3720 ,c_pgm_id => l_result_rec.pgm_id
3721 ,c_acty_base_rt_id => p_acty_base_rt_id
3722 ,c_business_group_id => p_business_group_id
3723 ,c_cur_rt_strt_dt => p_rt_strt_dt
3724 ,c_plan_year_strt_dt => l_plan_year_strt_dt)
3725 loop
3726 if l_first_rate = true then
3727 l_rt_strt_dt := GREATEST(r_rates.rt_strt_dt,l_plan_year_strt_dt);
3728 l_rt_end_dt := r_rates.rt_end_dt;
3729 l_ann_rt_val := r_rates.ann_rt_val;
3730
3731 l_first_rate := false;
3732 end if;
3733
3734 if l_ann_rt_val <> r_rates.ann_rt_val then -- Change in Amount
3735
3736 l_rate_ytd := l_rate_ytd + compute_period_rate(
3737 p_rt_strt_dt => l_rt_strt_dt
3738 ,p_rt_end_dt => l_rt_end_dt
3739 ,p_ann_rt_val => l_ann_rt_val
3740 ,p_rate_ytd => l_rate_ytd
3741 ,p_rndg_cd => l_get_abr_info.rndg_cd
3742 ,p_rndg_rl => l_get_abr_info.rndg_rl
3743 ,p_plan_year_end_dt => l_plan_year_end_dt);
3744
3745 hr_utility.set_location('l_rate_ytd '|| l_rate_ytd, 10);
3746
3747 l_rt_strt_dt := r_rates.rt_strt_dt;
3748 l_rt_end_dt := r_rates.rt_end_dt;
3749 l_ann_rt_val := r_rates.ann_rt_val;
3750
3751 else
3752 l_rt_end_dt := r_rates.rt_end_dt;
3753 end if;
3754 end loop;
3755
3756 if l_ann_rt_val is not null then
3757 l_rate_ytd := l_rate_ytd + compute_period_rate(
3758 p_rt_strt_dt => l_rt_strt_dt
3759 ,p_rt_end_dt => l_rt_end_dt
3760 ,p_ann_rt_val => l_ann_rt_val
3761 ,p_rate_ytd => l_rate_ytd
3762 ,p_rndg_cd => l_get_abr_info.rndg_cd
3763 ,p_rndg_rl => l_get_abr_info.rndg_rl
3764 ,p_plan_year_end_dt => l_plan_year_end_dt);
3765
3766 hr_utility.set_location('l_rate_ytd '|| l_rate_ytd, 20);
3767 end if;
3768
3769 l_balance := p_ann_rt_val - l_rate_ytd;
3770
3771 if l_months_remaining > 0 then
3772 l_per_month_amt := l_balance/l_months_remaining;
3773 else
3774 l_per_month_amt := 0;
3775 end if;
3776
3777 -- Round the per month amount
3778 l_per_month_amt := do_rounding(p_rndg_cd => l_get_abr_info.rndg_cd,
3779 p_rndg_rl => l_get_abr_info.rndg_rl,
3780 p_amount => l_per_month_amt,
3781 p_effective_date => p_effective_date
3782 );
3783
3784 l_first_pp_adjustment:= l_balance - (l_per_month_amt * l_months_remaining);
3785 --
3786 -- Proration is currently ignored for SAREC and Enter_Ann_Val_Flay = 'Y' cases
3787 --
3788 if ((l_ann_rt_val is null) OR (p_ann_rt_val <> l_ann_rt_val)) then
3789 -- Do adjustment for rounding only if Rate has changed
3790 l_prorated_first_mth_amt := l_per_month_amt + l_first_pp_adjustment;
3791 else
3792 l_prorated_first_mth_amt := l_per_month_amt;
3793 end if;
3794
3795 -- If Rate has ended in the middle of a month, then exclude that month
3796 if p_rt_end_dt = LAST_DAY(p_rt_end_dt) then
3797 l_prorated_last_mth_amt := l_per_month_amt;
3798 else
3799 l_prorated_last_mth_amt := 0;
3800 end if;
3801
3802 else -- Not SAREC or Enter_Ann_Val_Flay = 'Y'
3803
3804 l_per_month_amt := p_ann_rt_val/l_total_months;
3805
3806 l_prorated_first_mth_amt := ben_element_entry.prorate_amount
3807 (p_amt => l_per_month_amt
3808 ,p_acty_base_rt_id => p_acty_base_rt_id
3809 ,p_actl_prem_id => NULL
3810 ,p_cvg_amt_calc_mthd_id => NULL
3811 ,p_person_id => p_person_id
3812 ,p_rndg_cd => l_get_abr_info.rndg_cd
3813 ,p_rndg_rl => l_get_abr_info.rndg_rl
3814 ,p_pgm_id => l_result_rec.pgm_id
3815 ,p_pl_typ_id => l_result_rec.pl_typ_id
3816 ,p_pl_id => l_result_rec.pl_id
3817 ,p_opt_id => l_result_rec.opt_id
3818 ,p_ler_id => l_result_rec.ler_id
3819 ,p_prorate_flag => l_proration_flag
3820 ,p_effective_date => p_rt_strt_dt
3821 ,p_start_or_stop_cd => 'STRT'
3822 ,p_start_or_stop_date => p_rt_strt_dt
3823 ,p_business_group_id => p_business_group_id
3824 ,p_assignment_id => p_assignment_id
3825 ,p_organization_id => p_organization_id
3826 ,p_jurisdiction_code => l_jurisdiction_code
3827 ,p_wsh_rl_dy_mo_num => l_get_abr_info.wsh_rl_dy_mo_num
3828 ,p_prtl_mo_det_mthd_cd => l_get_abr_info.prtl_mo_det_mthd_cd
3829 ,p_prtl_mo_det_mthd_rl => l_get_abr_info.prtl_mo_det_mthd_rl);
3830
3831 l_prorated_last_mth_amt := ben_element_entry.prorate_amount
3832 (p_amt => l_per_month_amt
3833 ,p_acty_base_rt_id => p_acty_base_rt_id
3834 ,p_actl_prem_id => NULL
3835 ,p_cvg_amt_calc_mthd_id => NULL
3836 ,p_person_id => p_person_id
3837 ,p_rndg_cd => l_get_abr_info.rndg_cd
3838 ,p_rndg_rl => l_get_abr_info.rndg_rl
3839 ,p_pgm_id => l_result_rec.pgm_id
3840 ,p_pl_typ_id => l_result_rec.pl_typ_id
3841 ,p_pl_id => l_result_rec.pl_id
3842 ,p_opt_id => l_result_rec.opt_id
3843 ,p_ler_id => l_result_rec.ler_id
3844 ,p_prorate_flag => l_proration_flag
3845 ,p_effective_date => p_rt_end_dt
3846 ,p_start_or_stop_cd => 'STP'
3847 ,p_start_or_stop_date => p_rt_end_dt
3848 ,p_business_group_id => p_business_group_id
3849 ,p_assignment_id => p_assignment_id
3850 ,p_organization_id => p_organization_id
3851 ,p_jurisdiction_code => l_jurisdiction_code
3852 ,p_wsh_rl_dy_mo_num => l_get_abr_info.wsh_rl_dy_mo_num
3853 ,p_prtl_mo_det_mthd_cd => l_get_abr_info.prtl_mo_det_mthd_cd
3854 ,p_prtl_mo_det_mthd_rl => l_get_abr_info.prtl_mo_det_mthd_rl);
3855
3856
3857 l_per_month_amt := do_rounding(p_rndg_cd => l_get_abr_info.rndg_cd,
3858 p_rndg_rl => l_get_abr_info.rndg_rl,
3859 p_amount => l_per_month_amt,
3860 p_effective_date => p_effective_date
3861 );
3862
3863 l_prorated_first_mth_amt := do_rounding(p_rndg_cd => l_get_abr_info.rndg_cd,
3864 p_rndg_rl => l_get_abr_info.rndg_rl,
3865 p_amount => l_prorated_first_mth_amt,
3866 p_effective_date => p_effective_date
3867 );
3868
3869 l_prorated_last_mth_amt := do_rounding(p_rndg_cd => l_get_abr_info.rndg_cd,
3870 p_rndg_rl => l_get_abr_info.rndg_rl,
3871 p_amount => l_prorated_last_mth_amt,
3872 p_effective_date => p_effective_date
3873 );
3874
3875 l_first_pp_adjustment := (((p_ann_rt_val * l_months_remaining)/l_total_months) -
3876 (l_months_remaining * l_per_month_amt));
3877
3878
3879 l_first_pp_adjustment := do_rounding(p_rndg_cd => l_get_abr_info.rndg_cd,
3880 p_rndg_rl => l_get_abr_info.rndg_rl,
3881 p_amount => l_first_pp_adjustment,
3882 p_effective_date => p_effective_date
3883 );
3884
3885 l_prorated_first_mth_amt := l_prorated_first_mth_amt + l_first_pp_adjustment;
3886
3887 end if;
3888
3889 -- Set out variables
3890 p_per_month_amt := l_per_month_amt;
3891 p_first_month_amt := l_prorated_first_mth_amt;
3892 p_last_month_amt := l_prorated_last_mth_amt;
3893
3894 hr_utility.set_location('p_per_month_amt ' || p_per_month_amt, 10);
3895 hr_utility.set_location('p_first_month_amt ' || p_first_month_amt, 10);
3896 hr_utility.set_location('p_last_month_amt ' || p_last_month_amt, 10);
3897
3898 hr_utility.set_location('Leaving : ' || l_proc, 10);
3899
3900 end get_amount_due;
3901 --
3902 procedure get_due_and_payment_amt(p_person_id in number
3903 ,p_effective_date in date
3904 ,p_acty_base_rt_id in number
3905 ,p_business_group_id in number
3906 ,p_prtt_enrt_rslt_id in number
3907 ,p_rt_strt_dt in date
3908 ,p_rt_end_dt in date
3909 ,p_ann_rt_val in number
3910 ,p_mlt_cd in varchar2
3911 ,p_amt_due out nocopy number
3912 ,p_prev_pymts out nocopy number)
3913 is
3914
3915 cursor c_prev_pymts
3916 (c_assignment_id number
3917 ,c_acty_base_rt_id number
3918 ,c_business_group_id number
3919 ,c_effective_date date
3920 ,c_strt_dt date
3921 ,c_end_dt date) is
3922 SELECT NVL(sum(a.result_value),0) result_value
3923 FROM pay_run_result_values a
3924 ,pay_element_types_f b
3925 ,pay_assignment_actions d
3926 ,pay_payroll_actions e
3927 ,pay_run_results h
3928 ,ben_acty_base_rt_f i
3929 ,pay_input_values_f j
3930 WHERE d.assignment_id = c_assignment_id
3931 AND d.payroll_action_id = e.payroll_action_id
3932 AND i.input_value_id = j.input_value_id
3933 AND i.element_type_id = b.element_type_id
3934 AND i.acty_base_rt_id = c_acty_base_rt_id
3935 AND c_effective_date BETWEEN i.effective_start_date
3936 AND i.effective_end_date
3937 AND i.business_group_id = c_business_group_id
3938 AND b.element_type_id = h.element_type_id
3939 AND d.assignment_action_id = h.assignment_action_id
3940 AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
3941 AND a.input_value_id = j.input_value_id
3942 AND a.run_result_id = h.run_result_id
3943 AND j.element_type_id = b.element_type_id
3944 AND c_effective_date BETWEEN b.effective_start_date
3945 AND b.effective_end_date
3946 AND c_effective_date BETWEEN j.effective_start_date
3947 AND j.effective_end_date;
3948
3949 l_organization_id number;
3950 l_payroll_id number;
3951 l_assignment_id number;
3952
3953 l_first_month_amt number;
3954 l_per_month_amt number;
3955 l_last_month_amt number;
3956
3957 l_amt_due number;
3958 l_prev_mth_end_dt date;
3959 l_first_mth_end_dt date;
3960 l_months_between number;
3961
3962 l_prev_pymts number;
3963 l_proc varchar2(80) := g_package||'.get_due_and_payment_amt';
3964
3965 begin
3966
3967 hr_utility.set_location('Entering : ' || l_proc, 10);
3968
3969 ben_element_entry.get_abr_assignment
3970 (p_person_id => p_person_id
3971 ,p_effective_date => p_effective_date
3972 ,p_acty_base_rt_id => p_acty_base_rt_id
3973 ,p_organization_id => l_organization_id
3974 ,p_payroll_id => l_payroll_id
3975 ,p_assignment_id => l_assignment_id);
3976
3977 get_amount_due
3978 (p_person_id => p_person_id
3979 ,p_business_group_id => p_business_group_id
3980 ,p_assignment_id => l_assignment_id
3981 ,p_payroll_id => l_payroll_id
3982 ,p_organization_id => l_organization_id
3983 ,p_effective_date => p_effective_date
3984 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
3985 ,p_acty_base_rt_id => p_acty_base_rt_id
3986 ,p_ann_rt_val => p_ann_rt_val
3987 ,p_mlt_cd => p_mlt_cd
3988 ,p_rt_strt_dt => p_rt_strt_dt
3989 ,p_rt_end_dt => p_rt_end_dt
3990 ,p_first_month_amt => l_first_month_amt
3991 ,p_per_month_amt => l_per_month_amt
3992 ,p_last_month_amt => l_last_month_amt);
3993
3994
3995 open c_prev_pymts(c_assignment_id => l_assignment_id
3996 ,c_acty_base_rt_id => p_acty_base_rt_id
3997 ,c_business_group_id => p_business_group_id
3998 ,c_effective_date => p_effective_date
3999 ,c_strt_dt => p_rt_strt_dt
4000 ,c_end_dt => p_rt_end_dt);
4001
4002 fetch c_prev_pymts into l_prev_pymts;
4003 if c_prev_pymts%notfound then
4004 l_prev_pymts := 0;
4005 end if;
4006 close c_prev_pymts;
4007
4008 l_amt_due := l_first_month_amt + l_last_month_amt;
4009
4010 l_first_mth_end_dt := LAST_DAY(p_rt_strt_dt);
4011
4012 l_prev_mth_end_dt := LAST_DAY(ADD_MONTHS(p_rt_end_dt,-1));
4013 l_months_between := MONTHS_BETWEEN(l_prev_mth_end_dt,l_first_mth_end_dt);
4014
4015 l_amt_due := l_amt_due + (l_months_between * l_per_month_amt);
4016
4017 -- Set Out variables
4018 p_amt_due := l_amt_due;
4019 p_prev_pymts := l_prev_pymts;
4020
4021 hr_utility.set_location('Leaving : ' || l_proc, 10);
4022
4023 end get_due_and_payment_amt;
4024 --
4025 function get_comp_object_name(p_pl_id in number
4026 ,p_oipl_id in number
4027 ,p_effective_date in date)
4028 return varchar2
4029 is
4030
4031 cursor c_pln_name(c_pl_id in number
4032 ,c_effective_date in date )
4033 is
4034 select pln.name
4035 from ben_pl_f pln
4036 where pln.pl_id = c_pl_id
4037 and c_effective_date between pln.effective_start_date
4038 and pln.effective_end_date;
4039
4040 cursor c_opt_name(c_oipl_id in number
4041 ,c_effective_date in date )
4042 is
4043 select opt.name
4044 from ben_oipl_f cop,
4045 ben_opt_f opt
4046 where cop.oipl_id = c_oipl_id
4047 and cop.opt_id = opt.opt_id
4048 and c_effective_date between cop.effective_start_date
4049 and cop.effective_end_date
4050 and c_effective_date between opt.effective_start_date
4051 and opt.effective_end_date;
4052
4053 l_pln_name ben_pl_f.name%type;
4054 l_opt_name ben_opt_f.name%type;
4055 l_comp_object_name varchar2(500);
4056
4057 begin
4058 open c_pln_name(p_pl_id,p_effective_date);
4059 fetch c_pln_name into l_pln_name;
4060 close c_pln_name;
4061
4062 if p_oipl_id is not null then
4063 open c_opt_name(p_oipl_id,p_effective_date);
4064 fetch c_opt_name into l_opt_name;
4065 close c_opt_name;
4066 end if;
4067
4068 l_comp_object_name := l_pln_name;
4069 if l_opt_name is not null then
4070 l_comp_object_name := l_comp_object_name ||' - '||l_opt_name;
4071 end if;
4072
4073 return l_comp_object_name;
4074
4075 end get_comp_object_name;
4076 --
4077 procedure get_unpaid_rate(p_person_id in number
4078 ,p_pgm_id in number
4079 ,p_pl_typ_id in number
4080 ,p_business_group_id in number
4081 ,p_effective_date in date
4082 ,p_element_type_id in number
4083 ,p_input_value_id in number
4084 ,p_mode in varchar2
4085 ,p_prev_rt_strt_dt in date
4086 ,p_rt_strt_dt out nocopy date
4087 ,p_elm_chg_warning out nocopy varchar2)
4088
4089 is
4090
4091 CURSOR c_rates(c_person_id in number
4092 ,c_pgm_id in number
4093 ,c_pl_typ_id in number
4094 ,c_business_group_id in number
4095 ,c_effective_date in date
4096 ,c_element_type_id in number
4097 ,c_input_value_id in number) is
4098 SELECT pen.pl_id
4099 ,prv.element_entry_value_id
4100 ,prv.acty_base_rt_id
4101 ,prv.rt_strt_dt
4102 ,prv.rt_end_dt
4103 ,prv.ann_rt_val
4104 ,pen.prtt_enrt_rslt_id
4105 ,prv.mlt_cd
4106 FROM ben_prtt_enrt_rslt_f pen
4107 ,ben_prtt_rt_val prv
4108 ,ben_acty_base_rt_f abr
4109 WHERE pen.person_id = c_person_id
4110 AND pen.pgm_id = c_pgm_id
4111 AND pen.pl_typ_id = c_pl_typ_id
4112 AND pen.prtt_enrt_rslt_stat_cd IS NULL
4113 AND pen.sspndd_flag = 'N'
4114 AND pen.business_group_id = c_business_group_id
4115 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
4116 AND prv.business_group_id = pen.business_group_id
4117 AND prv.prtt_rt_val_stat_cd IS NULL
4118 AND prv.acty_typ_cd LIKE 'PBC%'
4119 AND pen.effective_end_date = hr_api.g_eot
4120 AND c_effective_date BETWEEN prv.rt_strt_dt and
4121 prv.rt_end_dt
4122 AND prv.acty_base_rt_id = abr.acty_base_rt_id
4123 AND abr.element_type_id +0 = c_element_type_id -- PERF FIX. Added +0
4124 AND abr.input_value_id +0= c_input_value_id -- PERF FIX. Added +0
4125 AND c_effective_date BETWEEN abr.effective_start_date
4126 and abr.effective_end_date;
4127
4128
4129 CURSOR c_rates_other(c_person_id in number
4130 ,c_pgm_id in number
4131 ,c_pl_typ_id in number
4132 ,c_business_group_id in number
4133 ,c_effective_date in date
4134 ,c_element_type_id in number
4135 ,c_input_value_id in number) is
4136 SELECT pen.pl_id
4137 ,pen.oipl_id
4138 ,prv.element_entry_value_id
4139 ,prv.acty_base_rt_id
4140 ,prv.rt_strt_dt
4141 ,prv.rt_end_dt
4142 ,prv.ann_rt_val
4143 ,pen.prtt_enrt_rslt_id
4144 ,prv.mlt_cd
4145 ,abr.name
4146 FROM ben_prtt_enrt_rslt_f pen
4147 ,ben_prtt_rt_val prv
4148 ,ben_acty_base_rt_f abr
4149 WHERE pen.person_id = c_person_id
4150 AND pen.pgm_id = c_pgm_id
4151 AND pen.pl_typ_id = c_pl_typ_id
4152 AND pen.prtt_enrt_rslt_stat_cd IS NULL
4153 AND pen.sspndd_flag = 'N'
4154 AND pen.business_group_id = c_business_group_id
4155 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
4156 AND prv.business_group_id = pen.business_group_id
4157 AND prv.prtt_rt_val_stat_cd IS NULL
4158 AND prv.acty_typ_cd LIKE 'PBC%'
4159 AND pen.effective_end_date = hr_api.g_eot
4160 AND c_effective_date BETWEEN prv.rt_strt_dt and
4161 prv.rt_end_dt
4162 AND prv.acty_base_rt_id = abr.acty_base_rt_id
4163 AND ((abr.element_type_id <> c_element_type_id) OR (abr.input_value_id <> c_input_value_id))
4164 AND c_effective_date BETWEEN abr.effective_start_date
4165 and abr.effective_end_date
4166 ORDER by pen.pl_id,pen.oipl_id;
4167
4168 l_rates c_rates%rowtype;
4169
4170 l_amt_due number;
4171 l_rt_strt_dt date;
4172 l_prev_pymts number;
4173 l_warning varchar2(2000);
4174 l_comp_object_name varchar2(500);
4175 l_pl_id number;
4176 l_oipl_id number;
4177 l_proc varchar2(80) := g_package||'.get_unpaid_rate';
4178
4179 begin
4180
4181 hr_utility.set_location('Entering : ' || l_proc, 10);
4182
4183 open c_rates(c_person_id => p_person_id
4184 ,c_pgm_id => p_pgm_id
4185 ,c_pl_typ_id => p_pl_typ_id
4186 ,c_business_group_id => p_business_group_id
4187 ,c_effective_date => p_effective_date
4188 ,c_element_type_id => p_element_type_id
4189 ,c_input_value_id => p_input_value_id);
4190 fetch c_rates into l_rates;
4191 if c_rates%notfound then
4192 close c_rates;
4193
4194 l_pl_id := null;
4195 l_oipl_id := null;
4196 for r_rates_other in c_rates_other
4197 (c_person_id => p_person_id
4198 ,c_pgm_id => p_pgm_id
4199 ,c_pl_typ_id => p_pl_typ_id
4200 ,c_business_group_id => p_business_group_id
4201 ,c_effective_date => p_effective_date
4202 ,c_element_type_id => p_element_type_id
4203 ,c_input_value_id => p_input_value_id)
4204 loop
4205
4206 get_due_and_payment_amt(p_person_id => p_person_id
4207 ,p_effective_date => p_effective_date
4208 ,p_acty_base_rt_id => r_rates_other.acty_base_rt_id
4209 ,p_business_group_id => p_business_group_id
4210 ,p_prtt_enrt_rslt_id => r_rates_other.prtt_enrt_rslt_id
4211 ,p_rt_strt_dt => r_rates_other.rt_strt_dt
4212 ,p_rt_end_dt => r_rates_other.rt_end_dt
4213 ,p_ann_rt_val => r_rates_other.ann_rt_val
4214 ,p_mlt_cd => r_rates_other.mlt_cd
4215 ,p_amt_due => l_amt_due
4216 ,p_prev_pymts => l_prev_pymts);
4217
4218
4219 if l_prev_pymts < l_amt_due then
4220
4221 if l_pl_id is null or
4222 ((l_pl_id <> r_rates_other.pl_id) or
4223 (NVL(l_oipl_id,hr_api.g_number) <> NVL(r_rates_other.oipl_id,hr_api.g_number))
4224 ) then
4225
4226
4227 l_pl_id := r_rates_other.pl_id;
4228 l_oipl_id := r_rates_other.oipl_id;
4229
4230 l_comp_object_name := get_comp_object_name
4231 (p_pl_id => r_rates_other.pl_id
4232 ,p_oipl_id => r_rates_other.oipl_id
4233 ,p_effective_date => r_rates_other.rt_strt_dt);
4234
4235 if l_warning is null then
4236 l_warning := l_warning || l_comp_object_name;
4237 else
4238 l_warning := l_warning || ', ' || l_comp_object_name;
4239 end if;
4240 end if;
4241
4242 end if;
4243 end loop;
4244
4245 if p_mode = 'PAST' then
4246 l_rt_strt_dt := p_prev_rt_strt_dt;
4247 elsif p_mode = 'FUTURE' then
4248 l_rt_strt_dt := NULL;
4249 end if;
4250
4251 else
4252 close c_rates;
4253
4254 get_due_and_payment_amt
4255 (p_person_id => p_person_id
4256 ,p_effective_date => p_effective_date
4257 ,p_acty_base_rt_id => l_rates.acty_base_rt_id
4258 ,p_business_group_id => p_business_group_id
4259 ,p_prtt_enrt_rslt_id => l_rates.prtt_enrt_rslt_id
4260 ,p_rt_strt_dt => l_rates.rt_strt_dt
4261 ,p_rt_end_dt => l_rates.rt_end_dt
4262 ,p_ann_rt_val => l_rates.ann_rt_val
4263 ,p_mlt_cd => l_rates.mlt_cd
4264 ,p_amt_due => l_amt_due
4265 ,p_prev_pymts => l_prev_pymts);
4266
4267 if l_prev_pymts = 0 then -- No payments
4268
4269 if p_mode = 'PAST' then
4270
4271 get_unpaid_rate
4272 (p_person_id => p_person_id
4273 ,p_pgm_id => p_pgm_id
4274 ,p_pl_typ_id => p_pl_typ_id
4275 ,p_business_group_id => p_business_group_id
4276 ,p_effective_date => (l_rates.rt_strt_dt - 1)
4277 ,p_element_type_id => p_element_type_id
4278 ,p_input_value_id => p_input_value_id
4279 ,p_mode => p_mode
4280 ,p_prev_rt_strt_dt => l_rates.rt_strt_dt
4281 ,p_rt_strt_dt => l_rt_strt_dt
4282 ,p_elm_chg_warning => l_warning);
4283
4284 elsif p_mode = 'FUTURE' then
4285 l_rt_strt_dt := l_rates.rt_strt_dt;
4286 end if;
4287
4288 elsif l_prev_pymts < l_amt_due then -- Incomplete payments
4289 l_rt_strt_dt := l_rates.rt_strt_dt;
4290
4291 else -- Full payment
4292
4293 if p_mode = 'PAST' then
4294 l_rt_strt_dt := p_prev_rt_strt_dt;
4295
4296 elsif p_mode = 'FUTURE' then
4297 get_unpaid_rate
4298 (p_person_id => p_person_id
4299 ,p_pgm_id => p_pgm_id
4300 ,p_pl_typ_id => p_pl_typ_id
4301 ,p_business_group_id => p_business_group_id
4302 ,p_effective_date => (l_rates.rt_end_dt + 1)
4303 ,p_element_type_id => p_element_type_id
4304 ,p_input_value_id => p_input_value_id
4305 ,p_mode => p_mode
4306 ,p_prev_rt_strt_dt => l_rates.rt_strt_dt
4307 ,p_rt_strt_dt => l_rt_strt_dt
4308 ,p_elm_chg_warning => l_warning);
4309 end if;
4310
4311 end if;
4312
4313 end if;
4314
4315 -- Set Out variables
4316 p_rt_strt_dt := l_rt_strt_dt;
4317 p_elm_chg_warning := l_warning;
4318
4319 hr_utility.set_location('p_rt_strt_dt ' ||p_rt_strt_dt, 10);
4320 hr_utility.set_location('Leaving : ' || l_proc, 10);
4321 end get_unpaid_rate;
4322 --
4323 -- ----------------------------------------------------------------------------
4324 -- |-------------------------< get_costing_details >---------------------------
4325 -- ----------------------------------------------------------------------------
4326 --
4327 procedure get_costing_details(p_person_id in number
4328 ,p_business_group_id in number
4329 ,p_assignment_id in number
4330 ,p_organization_id in number
4331 ,p_payroll_id in number
4332 ,p_rt_strt_dt in date
4333 ,p_acty_base_rt_id in number
4334 ,p_cos_set_id out nocopy number
4335 ,p_cost_alloc_kf_id out nocopy number
4336 ,p_costable_type out nocopy varchar2
4337 ,p_el_link out nocopy number
4338 ,p_input_value_id out nocopy number
4339 ,p_bal_adj_cost_flag out nocopy varchar2) is
4340
4341 cursor get_payroll(c_payroll_id in number
4342 ,c_effective_date in date
4343 ) is
4344 select pr.consolidation_set_id
4345 from pay_all_payrolls_f pr
4346 where pr.payroll_id = c_payroll_id
4347 and c_effective_date between pr.effective_start_date
4348 and pr.effective_end_date;
4349
4350 -- Balance_adj_cost_flag is now set based on the
4351 -- element's costable_type
4352 /*
4353 cursor input_val(c_acty_base_rt_id in number
4354 ,c_business_group_id in number
4355 ,c_effective_date in date
4356 ) is
4357 select entries.balance_adj_cost_flag
4358 from ben_acty_base_rt_f abr,
4359 pay_element_entry_values_f ee_values,
4360 pay_element_entries_f entries
4361 where abr.acty_base_rt_id = c_acty_base_rt_id
4362 and c_effective_date between abr.effective_start_date
4363 and abr.effective_end_date
4364 and abr.business_group_id = c_business_group_id
4365 and abr.input_value_id = ee_values.input_value_id
4366 and c_effective_date between ee_values.effective_start_date
4367 and ee_values.effective_end_date
4368 and ee_values.element_entry_id = entries.element_entry_id
4369 and c_effective_date between entries.effective_start_date
4370 and entries.effective_end_date;
4371 */
4372
4373 cursor get_el_link(c_acty_base_rt_id in number
4374 ,c_business_group_id in number
4375 ,c_effective_date in date
4376 ) is
4377 select elk.costable_type,
4378 link_inp_val.element_link_id,
4379 abr.input_value_id
4380 from ben_acty_base_rt_f abr,
4381 pay_input_values_f inp_val,
4382 pay_element_links_f elk,
4383 pay_link_input_values_f link_inp_val
4384 where acty_base_rt_id = c_acty_base_rt_id
4385 and abr.input_value_id = inp_val.input_value_id
4386 and c_effective_date between abr.effective_start_date
4387 and abr.effective_end_date
4388 and abr.business_group_id = c_business_group_id
4389 and c_effective_date between inp_val.effective_start_date
4390 and inp_val.effective_end_date
4391 and inp_val.business_group_id = c_business_group_id
4392 and link_inp_val.input_value_id = inp_val.input_value_id
4393 and elk.element_link_id = link_inp_val.element_link_id
4394 and c_effective_date between elk.effective_start_date
4395 and elk.effective_end_date
4396 and c_effective_date between link_inp_val.effective_start_date
4397 and link_inp_val.effective_end_date;
4398
4399 l_cos_set_id number;
4400 l_cost_alloc_kf_id number;
4401 l_el_link number;
4402 l_input_value_id number;
4403 l_bal_adj_cost_flag varchar2(30);
4404 l_costable_type pay_element_links_f.costable_type%TYPE;
4405
4406 begin
4407
4408 open get_payroll(c_payroll_id => p_payroll_id
4409 ,c_effective_date => p_rt_strt_dt);
4410 fetch get_payroll into l_cos_set_id;
4411 close get_payroll;
4412
4413 open get_el_link(c_acty_base_rt_id => p_acty_base_rt_id
4414 ,c_business_group_id => p_business_group_id
4415 ,c_effective_date => p_rt_strt_dt);
4416 fetch get_el_link into l_costable_type,l_el_link, l_input_value_id;
4417 close get_el_link;
4418
4419 --
4420 -- Pass cost_allocation_keyflex_id as NULL to pay_balance_adjustment_api
4421 -- This will ensure that costed values are assigned to the
4422 -- appropriate levels using the Costing Hierarchy
4423 --
4424 l_cost_alloc_kf_id := NULL;
4425
4426 -- Set bal_adj_cost_flag based on the element's costable_type
4427 if l_costable_type = 'N' then
4428 l_bal_adj_cost_flag := 'N';
4429 else
4430 l_bal_adj_cost_flag := 'Y';
4431 end if;
4432
4433 -- Set out variables
4434
4435 p_cos_set_id := l_cos_set_id;
4436 p_cost_alloc_kf_id := l_cost_alloc_kf_id;
4437 p_costable_type := l_costable_type;
4438 p_el_link := l_el_link;
4439 p_input_value_id := l_input_value_id;
4440 p_bal_adj_cost_flag := l_bal_adj_cost_flag;
4441
4442 end get_costing_details;
4443 --
4444 -- ----------------------------------------------------------------------------
4445 -- |-------------------------< allocate_payment >------------------------------
4446 -- ----------------------------------------------------------------------------
4447 --
4448 procedure allocate_payment(p_effective_date in date
4449 ,p_amount_paid in number
4450 ,p_acty_base_rt_id in number
4451 ,p_prtt_enrt_rslt_id in number
4452 ,p_business_group_id in number
4453 ,p_person_id in number
4454 ,p_rt_strt_dt in date
4455 ,p_month_strt_dt in date
4456 ,p_warning out nocopy boolean
4457 ,p_excess_amount out nocopy number) is
4458 --
4459
4460 CURSOR c_pen(c_prtt_enrt_rslt_id number
4461 ,c_effective_date date
4462 ,c_acty_base_rt_id number) IS
4463 SELECT pen.pgm_id
4464 ,pen.pl_typ_id
4465 ,abr.element_type_id
4466 ,abr.input_value_id
4467 FROM ben_prtt_enrt_rslt_f pen
4468 ,ben_prtt_rt_val prv
4469 ,ben_acty_base_rt_f abr
4470 WHERE pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
4471 AND c_effective_date BETWEEN pen.enrt_cvg_strt_dt
4472 AND pen.enrt_cvg_thru_dt
4473 AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
4474 AND pen.prtt_enrt_rslt_stat_cd IS NULL
4475 AND pen.sspndd_flag = 'N'
4476 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
4477 AND prv.acty_base_rt_id = c_acty_base_rt_id
4478 AND c_effective_date BETWEEN prv.rt_strt_dt and
4479 prv.rt_end_dt
4480 AND prv.acty_base_rt_id = abr.acty_base_rt_id
4481 AND c_effective_date BETWEEN abr.effective_start_date
4482 and abr.effective_end_date;
4483
4484 CURSOR c_get_cbr_due_day(c_pl_id number
4485 ,c_effective_date date) IS
4486 SELECT NVL(pln.cobra_pymt_due_dy_num,1)
4487 FROM ben_pl_f pln
4488 WHERE pln.pl_id = c_pl_id
4489 AND c_effective_date BETWEEN pln.effective_start_date
4490 AND pln.effective_end_date;
4491
4492 cursor c_prev_pymts
4493 (c_assignment_id number
4494 ,c_acty_base_rt_id number
4495 ,c_business_group_id number
4496 ,c_effective_date date
4497 ,c_strt_dt date
4498 ,c_end_dt date) is
4499 SELECT NVL(sum(a.result_value),0) result_value
4500 FROM pay_run_result_values a
4501 ,pay_element_types_f b
4502 ,pay_assignment_actions d
4503 ,pay_payroll_actions e
4504 ,pay_run_results h
4505 ,ben_acty_base_rt_f i
4506 ,pay_input_values_f j
4507 WHERE d.assignment_id = c_assignment_id
4508 AND d.payroll_action_id = e.payroll_action_id
4509 AND i.input_value_id = j.input_value_id
4510 AND i.element_type_id = b.element_type_id
4511 AND i.acty_base_rt_id = c_acty_base_rt_id
4512 AND c_effective_date BETWEEN i.effective_start_date
4513 AND i.effective_end_date
4514 AND i.business_group_id = c_business_group_id
4515 AND b.element_type_id = h.element_type_id
4516 AND d.assignment_action_id = h.assignment_action_id
4517 AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
4518 AND a.input_value_id = j.input_value_id
4519 AND a.run_result_id = h.run_result_id
4520 AND j.element_type_id = b.element_type_id
4521 AND c_effective_date BETWEEN b.effective_start_date
4522 AND b.effective_end_date
4523 AND c_effective_date BETWEEN j.effective_start_date
4524 AND j.effective_end_date;
4525
4526 cursor c_prev_pymts_all
4527 (c_assignment_id number
4528 ,c_acty_base_rt_id number
4529 ,c_business_group_id number
4530 ,c_effective_date date
4531 ,c_strt_dt date
4532 ,c_end_dt date) is
4533 SELECT NVL(sum(a.result_value),0) result_value
4534 ,LAST_DAY(e.date_earned) month_end_dt
4535 FROM pay_run_result_values a
4536 ,pay_element_types_f b
4537 ,pay_assignment_actions d
4538 ,pay_payroll_actions e
4539 ,pay_run_results h
4540 ,ben_acty_base_rt_f i
4541 ,pay_input_values_f j
4542 WHERE d.assignment_id = c_assignment_id
4543 AND d.payroll_action_id = e.payroll_action_id
4544 AND i.input_value_id = j.input_value_id
4545 AND i.element_type_id = b.element_type_id
4546 AND i.acty_base_rt_id = c_acty_base_rt_id
4547 AND c_effective_date BETWEEN i.effective_start_date
4548 AND i.effective_end_date
4549 AND i.business_group_id = c_business_group_id
4550 AND b.element_type_id = h.element_type_id
4551 AND d.assignment_action_id = h.assignment_action_id
4552 AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
4553 AND a.input_value_id = j.input_value_id
4554 AND a.run_result_id = h.run_result_id
4555 AND j.element_type_id = b.element_type_id
4556 AND c_effective_date BETWEEN b.effective_start_date
4557 AND b.effective_end_date
4558 AND c_effective_date BETWEEN j.effective_start_date
4559 AND j.effective_end_date
4560 group by LAST_DAY(e.date_earned)
4561 order by LAST_DAY(e.date_earned) desc;
4562
4563 cursor c_prev_pymts_latest
4564 (c_assignment_id number
4565 ,c_acty_base_rt_id number
4566 ,c_business_group_id number
4567 ,c_effective_date date
4568 ,c_per_month_amt number
4569 ,c_rt_strt_dt date
4570 ,c_rt_end_dt date) is
4571 SELECT NVL(sum(a.result_value),0) result_value
4572 ,LAST_DAY(e.date_earned) month_end_dt
4573 FROM pay_run_result_values a
4574 ,pay_element_types_f b
4575 ,pay_assignment_actions d
4576 ,pay_payroll_actions e
4577 ,pay_run_results h
4578 ,ben_acty_base_rt_f i
4579 ,pay_input_values_f j
4580 WHERE d.assignment_id = c_assignment_id
4581 AND d.payroll_action_id = e.payroll_action_id
4582 AND i.input_value_id = j.input_value_id
4583 AND i.element_type_id = b.element_type_id
4584 AND i.acty_base_rt_id = c_acty_base_rt_id
4585 AND c_effective_date BETWEEN i.effective_start_date
4586 AND i.effective_end_date
4587 AND i.business_group_id = c_business_group_id
4588 AND b.element_type_id = h.element_type_id
4589 AND d.assignment_action_id = h.assignment_action_id
4590 AND a.input_value_id = j.input_value_id
4591 AND a.run_result_id = h.run_result_id
4592 AND j.element_type_id = b.element_type_id
4593 AND c_effective_date BETWEEN b.effective_start_date
4594 AND b.effective_end_date
4595 AND c_effective_date BETWEEN j.effective_start_date
4596 AND j.effective_end_date
4597 AND e.date_earned between c_rt_strt_dt and c_rt_end_dt
4598 group by LAST_DAY(e.date_earned)
4599 having NVL(sum(a.result_value),0) = c_per_month_amt
4600 order by LAST_DAY(e.date_earned) desc;
4601
4602
4603 CURSOR c_rates(c_person_id in number
4604 ,c_pgm_id in number
4605 ,c_pl_typ_id in number
4606 ,c_business_group_id in number
4607 ,c_effective_date in date
4608 ,c_element_type_id in number
4609 ,c_input_value_id in number) is
4610 SELECT pen.pl_id
4611 ,prv.element_entry_value_id
4612 ,prv.acty_base_rt_id
4613 ,prv.rt_strt_dt
4614 ,prv.rt_end_dt
4615 ,prv.ann_rt_val
4616 ,pen.prtt_enrt_rslt_id
4617 ,prv.mlt_cd
4618 FROM ben_prtt_enrt_rslt_f pen
4619 ,ben_prtt_rt_val prv
4620 ,ben_acty_base_rt_f abr
4621 WHERE pen.person_id = c_person_id
4622 AND pen.pgm_id = c_pgm_id
4623 AND pen.pl_typ_id = c_pl_typ_id
4624 AND pen.prtt_enrt_rslt_stat_cd IS NULL
4625 AND pen.sspndd_flag = 'N'
4626 AND pen.business_group_id = c_business_group_id
4627 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
4628 AND prv.business_group_id = pen.business_group_id
4629 AND prv.prtt_rt_val_stat_cd IS NULL
4630 AND prv.acty_typ_cd LIKE 'PBC%'
4631 AND pen.effective_end_date = hr_api.g_eot
4632 AND prv.rt_strt_dt >= c_effective_date
4633 AND prv.acty_base_rt_id = abr.acty_base_rt_id
4634 AND abr.element_type_id +0= c_element_type_id -- PERF FIX. Added +0
4635 AND abr.input_value_id +0= c_input_value_id -- PERF FIX. Added +0
4636 AND c_effective_date BETWEEN abr.effective_start_date
4637 and abr.effective_end_date
4638 ORDER BY prv.rt_strt_dt;
4639
4640 CURSOR c_rates_desc(c_person_id in number
4641 ,c_pgm_id in number
4642 ,c_pl_typ_id in number
4643 ,c_business_group_id in number
4644 ,c_element_type_id in number
4645 ,c_input_value_id in number) is
4646 SELECT pen.pl_id
4647 ,prv.element_entry_value_id
4648 ,prv.acty_base_rt_id
4649 ,prv.rt_strt_dt
4650 ,prv.rt_end_dt
4651 ,prv.ann_rt_val
4652 ,pen.prtt_enrt_rslt_id
4653 ,prv.mlt_cd
4654 FROM ben_prtt_enrt_rslt_f pen
4655 ,ben_prtt_rt_val prv
4656 ,ben_acty_base_rt_f abr
4657 WHERE pen.person_id = c_person_id
4658 AND pen.pgm_id = c_pgm_id
4659 AND pen.pl_typ_id = c_pl_typ_id
4660 AND pen.prtt_enrt_rslt_stat_cd IS NULL
4661 AND pen.sspndd_flag = 'N'
4662 AND pen.business_group_id = c_business_group_id
4663 AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
4664 AND prv.business_group_id = pen.business_group_id
4665 AND prv.prtt_rt_val_stat_cd IS NULL
4666 AND prv.acty_typ_cd LIKE 'PBC%'
4667 AND pen.effective_end_date = hr_api.g_eot
4668 AND prv.acty_base_rt_id = abr.acty_base_rt_id
4669 AND abr.element_type_id = c_element_type_id
4670 AND abr.input_value_id = c_input_value_id
4671 AND prv.rt_strt_dt BETWEEN abr.effective_start_date
4672 and abr.effective_end_date
4673 ORDER BY prv.rt_strt_dt desc;
4674
4675 cursor c_plan_year_end_for_pen
4676 (c_prtt_enrt_rslt_id in number
4677 ,c_effective_date in date
4678 )
4679 is
4680 select distinct
4681 yp.start_date,yp.end_date
4682 from ben_prtt_enrt_rslt_f pen,
4683 ben_popl_yr_perd pyp,
4684 ben_yr_perd yp
4685 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
4686 and pen.prtt_enrt_rslt_stat_cd is null
4687 and c_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
4688 and pen.enrt_cvg_thru_dt <= pen.effective_end_date
4689 and pyp.pl_id=pen.pl_id
4690 and yp.yr_perd_id=pyp.yr_perd_id
4691 and c_effective_date between yp.start_date and yp.end_date;
4692
4693 l_plan_year_strt_dt date;
4694 l_plan_year_end_dt date;
4695
4696 l_ee_id number;
4697 l_effective_start_date date;
4698 l_effective_end_date date;
4699 l_ovn number;
4700 l_warning boolean;
4701
4702 l_amount_paid_bal number;
4703 l_pl_id ben_pl_f.pl_id%type;
4704 l_cbr_due_day ben_pl_f.cobra_pymt_due_dy_num%type;
4705
4706 l_amount_due number;
4707
4708 l_prev_pymts number;
4709 l_net_amount_due number;
4710 l_pymt_adj_amount number;
4711 l_pymt_dt date;
4712
4713 l_rt_strt_dt ben_prtt_rt_val.rt_strt_dt%type;
4714 l_rt_end_dt ben_prtt_rt_val.rt_end_dt%type;
4715 l_ann_rt_val ben_prtt_rt_val.ann_rt_val%type;
4716 l_mlt_cd ben_prtt_rt_val.mlt_cd%type;
4717
4718 l_last_month_amt number;
4719 l_first_month_amt number;
4720 l_per_month_amt number;
4721
4722 l_month_strt_dt date;
4723 l_month_end_dt date;
4724 l_last_full_pymt number;
4725 l_last_mth_end_dt date;
4726
4727 l_cos_set_id number;
4728 l_cost_alloc_kf_id number;
4729 l_el_link number;
4730 l_input_value_id number;
4731 l_bal_adj_cost_flag varchar2(30);
4732 l_costable_type pay_element_links_f.costable_type%TYPE;
4733
4734 l_organization_id number;
4735 l_payroll_id number;
4736 l_assignment_id number;
4737 l_pen c_pen%rowtype;
4738 l_month_last_day date;
4739
4740 l_proc varchar2(80) := g_package||'.allocate_payment';
4741 begin
4742
4743 hr_utility.set_location('Entering : ' || l_proc, 10);
4744
4745 l_amount_paid_bal := ABS(p_amount_paid);
4746
4747 open c_pen(p_prtt_enrt_rslt_id,p_effective_date,p_acty_base_rt_id);
4748 fetch c_pen into l_pen;
4749 close c_pen;
4750
4751 open c_plan_year_end_for_pen
4752 (c_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4753 ,c_effective_date => p_effective_date
4754 );
4755 fetch c_plan_year_end_for_pen into l_plan_year_strt_dt, l_plan_year_end_dt;
4756 close c_plan_year_end_for_pen;
4757
4758 if p_amount_paid > 0 then --Amount paid is positive
4759
4760 -- Find the latest month for which full payment was made
4761
4762 for r_rates in c_rates(c_person_id => p_person_id
4763 ,c_pgm_id => l_pen.pgm_id
4764 ,c_pl_typ_id => l_pen.pl_typ_id
4765 ,c_business_group_id => p_business_group_id
4766 ,c_effective_date => p_rt_strt_dt
4767 ,c_element_type_id => l_pen.element_type_id
4768 ,c_input_value_id => l_pen.input_value_id) loop
4769
4770 open c_get_cbr_due_day(r_rates.pl_id,r_rates.rt_strt_dt);
4771 fetch c_get_cbr_due_day into l_cbr_due_day;
4772 close c_get_cbr_due_day;
4773
4774 ben_element_entry.get_abr_assignment
4775 (p_person_id => p_person_id
4776 ,p_effective_date => r_rates.rt_strt_dt
4777 ,p_acty_base_rt_id => r_rates.acty_base_rt_id
4778 ,p_organization_id => l_organization_id
4779 ,p_payroll_id => l_payroll_id
4780 ,p_assignment_id => l_assignment_id);
4781
4782
4783 get_costing_details(p_person_id => p_person_id
4784 ,p_business_group_id => p_business_group_id
4785 ,p_assignment_id => l_assignment_id
4786 ,p_organization_id => l_organization_id
4787 ,p_payroll_id => l_payroll_id
4788 ,p_rt_strt_dt => r_rates.rt_strt_dt
4789 ,p_acty_base_rt_id => r_rates.acty_base_rt_id
4790 ,p_cos_set_id => l_cos_set_id
4791 ,p_cost_alloc_kf_id => l_cost_alloc_kf_id
4792 ,p_costable_type => l_costable_type
4793 ,p_el_link => l_el_link
4794 ,p_input_value_id => l_input_value_id
4795 ,p_bal_adj_cost_flag => l_bal_adj_cost_flag);
4796
4797 get_amount_due
4798 (p_person_id => p_person_id
4799 ,p_business_group_id => p_business_group_id
4800 ,p_assignment_id => l_assignment_id
4801 ,p_payroll_id => l_payroll_id
4802 ,p_organization_id => l_organization_id
4803 ,p_effective_date => r_rates.rt_strt_dt
4804 ,p_prtt_enrt_rslt_id => r_rates.prtt_enrt_rslt_id
4805 ,p_acty_base_rt_id => r_rates.acty_base_rt_id
4806 ,p_ann_rt_val => r_rates.ann_rt_val
4807 ,p_mlt_cd => r_rates.mlt_cd
4808 ,p_rt_strt_dt => r_rates.rt_strt_dt
4809 ,p_rt_end_dt => r_rates.rt_end_dt
4810 ,p_first_month_amt => l_first_month_amt
4811 ,p_per_month_amt => l_per_month_amt
4812 ,p_last_month_amt => l_last_month_amt);
4813
4814
4815 l_month_strt_dt := p_month_strt_dt;
4816
4817 -- Bug 3338978: If p_month_strt_dt is before
4818 -- Rate Start Date, start allocating payments based on
4819 -- Rate Start Date
4820 --
4821 if (l_month_strt_dt < r_rates.rt_strt_dt) then
4822 l_month_strt_dt := r_rates.rt_strt_dt;
4823 end if;
4824
4825 if l_per_month_amt > 0 then
4826 while (l_month_strt_dt <= r_rates.rt_end_dt
4827 and l_amount_paid_bal > 0
4828 and LAST_DAY(l_month_strt_dt) <= l_plan_year_end_dt
4829 and l_month_strt_dt >= l_plan_year_strt_dt)
4830 loop
4831
4832 l_month_end_dt := LAST_DAY(l_month_strt_dt);
4833
4834 if (l_month_end_dt > r_rates.rt_end_dt) then
4835 l_month_end_dt := r_rates.rt_end_dt;
4836 end if;
4837
4838 open c_prev_pymts(c_assignment_id => l_assignment_id
4839 ,c_acty_base_rt_id => r_rates.acty_base_rt_id
4840 ,c_business_group_id => p_business_group_id
4841 ,c_effective_date => r_rates.rt_strt_dt
4842 ,c_strt_dt => l_month_strt_dt
4843 ,c_end_dt => l_month_end_dt);
4844
4845 fetch c_prev_pymts into l_prev_pymts;
4846 if c_prev_pymts%notfound then
4847 l_prev_pymts := 0;
4848 end if;
4849 close c_prev_pymts;
4850
4851 --
4852 -- For first month, compare previous payments with
4853 -- amount due (l_first_mth_amt)
4854 -- For other months, compare previous payments with
4855 -- amount due (l_per_month_amt)
4856 -- For last month, compare previous payments with
4857 -- amount due (l_last_mth_amt)
4858
4859 if (l_month_strt_dt = r_rates.rt_strt_dt) then -- First month
4860 -- For first month, get prorated value
4861
4862 l_amount_due := l_first_month_amt;
4863
4864 elsif (l_month_end_dt = r_rates.rt_end_dt) then -- Last month
4865 -- For last month, get prorated value
4866
4867 l_amount_due := l_last_month_amt;
4868 else
4869 l_amount_due := l_per_month_amt;
4870 end if;
4871
4872 l_net_amount_due := l_amount_due - l_prev_pymts;
4873
4874 -- determine the payment adjustment amount
4875 -- if the net_amount_due is negative (excess payment
4876 -- made) then no payment adjustment is to be created
4877
4878 if l_net_amount_due > 0 then
4879 if l_amount_paid_bal <= l_net_amount_due then
4880 l_pymt_adj_amount := l_amount_paid_bal;
4881 else
4882 l_pymt_adj_amount := l_net_amount_due;
4883 end if;
4884 else
4885 l_pymt_adj_amount := 0;
4886 end if;
4887
4888 -- If the Effective Date falls in the Month
4889 -- for which the payment is being made, then
4890 -- Payment Date is set as Effective Date
4891
4892 -- If the Effective Date falls outside the Month
4893 -- for which the payment is being made, then
4894 -- Payment Date is set to the Cobra Payment Day
4895 -- for that month
4896
4897 if p_effective_date between l_month_strt_dt
4898 and l_month_end_dt then
4899 l_pymt_dt := p_effective_date;
4900 else
4901 l_pymt_dt := LAST_DAY(ADD_MONTHS(l_month_strt_dt,-1)) + l_cbr_due_day ;
4902 -- Bug 3208938
4903 -- If COBRA due day falls outside the month, use last day of month
4904
4905 l_month_last_day := LAST_DAY(l_month_strt_dt);
4906
4907 if l_pymt_dt > l_month_last_day then
4908 l_pymt_dt := l_month_last_day;
4909 end if;
4910
4911 -- Bug 3208938
4912 end if;
4913
4914 l_pymt_dt := GREATEST(r_rates.rt_strt_dt,l_pymt_dt);
4915
4916 l_pymt_dt := LEAST(r_rates.rt_end_dt,l_pymt_dt);
4917 if l_pymt_adj_amount > 0 then
4918
4919 pay_balance_adjustment_api.create_adjustment
4920 (p_validate => false,
4921 p_effective_date => l_pymt_dt,
4922 p_assignment_id => l_assignment_id,
4923 p_consolidation_set_id => l_cos_set_id,
4924 p_element_link_id => l_el_link,
4925 p_input_value_id1 => l_input_value_id,
4926 p_input_value_id2 => NULL,
4927 p_input_value_id3 => NULL,
4928 p_input_value_id4 => NULL,
4929 p_input_value_id5 => NULL,
4930 p_input_value_id6 => NULL,
4931 p_input_value_id7 => NULL,
4932 p_input_value_id8 => NULL,
4933 p_input_value_id9 => NULL,
4934 p_input_value_id10 => NULL,
4935 p_input_value_id11 => NULL,
4936 p_input_value_id12 => NULL,
4937 p_input_value_id13 => NULL,
4938 p_input_value_id14 => NULL,
4939 p_input_value_id15 => NULL,
4940 p_entry_value1 => l_pymt_adj_amount,
4941 p_entry_value2 => NULL,
4942 p_entry_value3 => NULL,
4943 p_entry_value4 => NULL,
4944 p_entry_value5 => NULL,
4945 p_entry_value6 => NULL,
4946 p_entry_value7 => NULL,
4947 p_entry_value8 => NULL,
4948 p_entry_value9 => NULL,
4949 p_entry_value10 => NULL,
4950 p_entry_value11 => NULL,
4951 p_entry_value12 => NULL,
4952 p_entry_value13 => NULL,
4953 p_entry_value14 => NULL,
4954 p_entry_value15 => NULL,
4955 p_balance_adj_cost_flag => l_bal_adj_cost_flag,
4956 p_cost_allocation_keyflex_id => l_cost_alloc_kf_id,
4957 p_attribute_category => NULL,
4958 p_attribute1 => NULL,
4959 p_attribute2 => NULL,
4960 p_attribute3 => NULL,
4961 p_attribute4 => NULL,
4962 p_attribute5 => NULL,
4963 p_attribute6 => NULL,
4964 p_attribute7 => NULL,
4965 p_attribute8 => NULL,
4966 p_attribute9 => NULL,
4967 p_attribute10 => NULL,
4968 p_attribute11 => NULL,
4969 p_attribute12 => NULL,
4970 p_attribute13 => NULL,
4971 p_attribute14 => NULL,
4972 p_attribute15 => NULL,
4973 p_attribute16 => NULL,
4974 p_attribute17 => NULL,
4975 p_attribute18 => NULL,
4976 p_attribute19 => NULL,
4977 p_attribute20 => NULL,
4978 p_element_entry_id => l_ee_id,
4979 p_effective_start_date => l_effective_start_date,
4980 p_effective_end_date => l_effective_end_date,
4981 p_object_version_number => l_ovn,
4982 p_create_warning => l_warning
4983 );
4984
4985 if l_warning then
4986 p_warning := l_warning;
4987 return;
4988 end if;
4989
4990 end if;
4991
4992 l_amount_paid_bal := l_amount_paid_bal - l_pymt_adj_amount;
4993 l_month_strt_dt := l_month_end_dt + 1;
4994
4995 end loop;
4996 end if; --l_per_month_amt > 0
4997
4998 if l_amount_paid_bal <= 0 then
4999 exit;
5000 end if;
5001
5002 end loop;
5003
5004 else --Payment reversal
5005
5006 for r_rates in c_rates_desc(c_person_id => p_person_id
5007 ,c_pgm_id => l_pen.pgm_id
5008 ,c_pl_typ_id => l_pen.pl_typ_id
5009 ,c_business_group_id => p_business_group_id
5010 ,c_element_type_id => l_pen.element_type_id
5011 ,c_input_value_id => l_pen.input_value_id)
5012 loop
5013
5014 open c_get_cbr_due_day(r_rates.pl_id,r_rates.rt_strt_dt);
5015 fetch c_get_cbr_due_day into l_cbr_due_day;
5016 close c_get_cbr_due_day;
5017
5018 ben_element_entry.get_abr_assignment
5019 (p_person_id => p_person_id
5020 ,p_effective_date => r_rates.rt_strt_dt
5021 ,p_acty_base_rt_id => r_rates.acty_base_rt_id
5022 ,p_organization_id => l_organization_id
5023 ,p_payroll_id => l_payroll_id
5024 ,p_assignment_id => l_assignment_id);
5025
5026
5027 get_costing_details(p_person_id => p_person_id
5028 ,p_business_group_id => p_business_group_id
5029 ,p_assignment_id => l_assignment_id
5030 ,p_organization_id => l_organization_id
5031 ,p_payroll_id => l_payroll_id
5032 ,p_rt_strt_dt => r_rates.rt_strt_dt
5033 ,p_acty_base_rt_id => r_rates.acty_base_rt_id
5034 ,p_cos_set_id => l_cos_set_id
5035 ,p_cost_alloc_kf_id => l_cost_alloc_kf_id
5036 ,p_costable_type => l_costable_type
5037 ,p_el_link => l_el_link
5038 ,p_input_value_id => l_input_value_id
5039 ,p_bal_adj_cost_flag => l_bal_adj_cost_flag);
5040
5041 for r_prev_pymts in c_prev_pymts_all(c_assignment_id => l_assignment_id
5042 ,c_acty_base_rt_id => r_rates.acty_base_rt_id
5043 ,c_business_group_id => p_business_group_id
5044 ,c_effective_date => r_rates.rt_strt_dt
5045 ,c_strt_dt => r_rates.rt_strt_dt
5046 ,c_end_dt => r_rates.rt_end_dt)
5047 loop
5048
5049 l_month_strt_dt := ADD_MONTHS(r_prev_pymts.month_end_dt,-1) + 1;
5050
5051 if ((r_prev_pymts.month_end_dt <= l_plan_year_end_dt)
5052 and (l_month_strt_dt >= l_plan_year_strt_dt))
5053 then
5054
5055 if l_amount_paid_bal <= r_prev_pymts.result_value then
5056 l_pymt_adj_amount := l_amount_paid_bal;
5057 else
5058 l_pymt_adj_amount := r_prev_pymts.result_value;
5059 end if;
5060
5061
5062 -- If the Effective Date falls in the Month
5063 -- for which the payment reversal is being made, then
5064 -- Payment Date is set as Effective Date
5065
5066 -- If the Effective Date falls outside the Month
5067 -- for which the payment reversal is being made, then
5068 -- Payment Date is set to the Cobra Payment Day
5069 -- for that month
5070
5071 if p_effective_date between l_month_strt_dt
5072 and r_prev_pymts.month_end_dt then
5073 l_pymt_dt := p_effective_date;
5074 else
5075 l_pymt_dt := l_month_strt_dt + (l_cbr_due_day -1);
5076
5077 -- Bug 3208938
5078 -- If COBRA due day falls outside the month, use last day of month
5079
5080 l_month_last_day := LAST_DAY(l_month_strt_dt);
5081
5082 if l_pymt_dt > l_month_last_day then
5083 l_pymt_dt := l_month_last_day;
5084 end if;
5085
5086 -- Bug 3208938
5087 end if;
5088
5089 l_pymt_dt := GREATEST(r_rates.rt_strt_dt,l_pymt_dt);
5090
5091 l_pymt_dt := LEAST(r_rates.rt_end_dt,l_pymt_dt);
5092
5093 if l_pymt_adj_amount > 0 then
5094
5095 pay_balance_adjustment_api.create_adjustment
5096 (p_validate => false,
5097 p_effective_date => l_pymt_dt,
5098 p_assignment_id => l_assignment_id,
5099 p_consolidation_set_id => l_cos_set_id,
5100 p_element_link_id => l_el_link,
5101 p_input_value_id1 => l_input_value_id,
5102 p_input_value_id2 => NULL,
5103 p_input_value_id3 => NULL,
5104 p_input_value_id4 => NULL,
5105 p_input_value_id5 => NULL,
5106 p_input_value_id6 => NULL,
5107 p_input_value_id7 => NULL,
5108 p_input_value_id8 => NULL,
5109 p_input_value_id9 => NULL,
5110 p_input_value_id10 => NULL,
5111 p_input_value_id11 => NULL,
5112 p_input_value_id12 => NULL,
5113 p_input_value_id13 => NULL,
5114 p_input_value_id14 => NULL,
5115 p_input_value_id15 => NULL,
5116 p_entry_value1 => -(l_pymt_adj_amount),
5117 p_entry_value2 => NULL,
5118 p_entry_value3 => NULL,
5119 p_entry_value4 => NULL,
5120 p_entry_value5 => NULL,
5121 p_entry_value6 => NULL,
5122 p_entry_value7 => NULL,
5123 p_entry_value8 => NULL,
5124 p_entry_value9 => NULL,
5125 p_entry_value10 => NULL,
5126 p_entry_value11 => NULL,
5127 p_entry_value12 => NULL,
5128 p_entry_value13 => NULL,
5129 p_entry_value14 => NULL,
5130 p_entry_value15 => NULL,
5131 p_balance_adj_cost_flag => l_bal_adj_cost_flag,
5132 p_cost_allocation_keyflex_id => l_cost_alloc_kf_id,
5133 p_attribute_category => NULL,
5134 p_attribute1 => NULL,
5135 p_attribute2 => NULL,
5136 p_attribute3 => NULL,
5137 p_attribute4 => NULL,
5138 p_attribute5 => NULL,
5139 p_attribute6 => NULL,
5140 p_attribute7 => NULL,
5141 p_attribute8 => NULL,
5142 p_attribute9 => NULL,
5143 p_attribute10 => NULL,
5144 p_attribute11 => NULL,
5145 p_attribute12 => NULL,
5146 p_attribute13 => NULL,
5147 p_attribute14 => NULL,
5148 p_attribute15 => NULL,
5149 p_attribute16 => NULL,
5150 p_attribute17 => NULL,
5151 p_attribute18 => NULL,
5152 p_attribute19 => NULL,
5153 p_attribute20 => NULL,
5154 p_element_entry_id => l_ee_id,
5155 p_effective_start_date => l_effective_start_date,
5156 p_effective_end_date => l_effective_end_date,
5157 p_object_version_number => l_ovn,
5158 p_create_warning => l_warning
5159 );
5160
5161 if l_warning then
5162 p_warning := l_warning;
5163 return;
5164 end if;
5165
5166 end if;
5167
5168 l_amount_paid_bal := l_amount_paid_bal - l_pymt_adj_amount;
5169
5170 if l_amount_paid_bal <= 0 then
5171 exit;
5172 end if;
5173
5174 end if;
5175
5176 end loop;
5177
5178 if l_amount_paid_bal <= 0 then
5179 exit;
5180 end if;
5181 end loop;
5182
5183 end if;
5184
5185 if p_amount_paid >= 0 then -- Positive payment
5186 p_excess_amount := l_amount_paid_bal;
5187
5188 elsif p_amount_paid < 0 then
5189 p_excess_amount := - l_amount_paid_bal;
5190 end if;
5191
5192 hr_utility.set_location('Leaving : ' || l_proc, 10);
5193 end allocate_payment;
5194
5195 END ben_cobra_requirements;