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