[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_RATE_CHG
Source
1 Package Body ben_determine_rate_chg as
2 /* $Header: benrtchg.pkb 120.12.12010000.4 2008/08/05 14:53:34 ubhat ship $ */
3
4 --------------------------------------------------------------------------------
5 /*
6 +==============================================================================+
7 | Copyright (c) 1997 Oracle Corporation |
8 | Redwood Shores, California, USA |
9 | All rights reserved. |
10 +==============================================================================+
11 Name:
12 Determine rate/benefit changes.
13 Purpose:
14 This process determines what rate, benefit amount, or actual premiums have
15 changed and updates corresponding data
16
17 History:
18 Date Who Version What?
19 ---- --- ------- -----
20 25 Oct 98 T Guy 115.0 Created.
21 22 Dec 98 T Guy 115.1 Added enrt_mthd_cd to call for
22 ben_election_information.
23 election_rate_information
24 28 Dec 98 j lamoureux 115.2 Removed parameters
25 dflt_enrt_dt,
26 enrt_typ_cycl_cd,
27 enrt_perd_strt_dt,
28 enrt_perd_end_dt
29 in call to elig_per_elctbl_chc api
30 11 Jan 99 T Guy 115.3 added elctbl_chc_id to call for
31 ben_determine_rates.main
32 16 Feb 99 T Guy 115.4 added comp level cd check for
33 planfc and planimp
34 09 Mar 99 G Perry 115.6 IS to AS.
35 16 Apr 99 T Guy 115.7 Removed call to create new
36 elctbl_chc. Bendenrr now creates
37 a choice for currently enrolled
38 and sets elctbl_flag to N. Also
39 removed calls to determince coverage
40 and determine rates. These will
41 be already run during the normal
42 benmngle run.
43 29 Apr 99 lmcdonal 115.8 prtt_rt_val and prtt_enrt_rslt have
44 status codes now.
45 30 Jun 99 T Guy 115.9 Added total premium check
46 07 Jul 99 jcarpent 115.10 Added per_in_ler_id to call to
47 update_prtt_prem_f
48 09 Jul 99 jcarpent 115.11 Added checks for backed out nocopy pil
49 20-JUL-99 Gperry 115.12 genutils -> benutils package
50 rename.
51 14-SEP-99 shdas 115.13 added bnft_val to election_information
52 28-SEP-99 tguy 115.14 set global flag so that benmngle will
53 will close per_in_ler's with the others
54 being processed. This allows us to
55 avoid duplication and throughing off
56 counts for reporting purposes.
57 18-NOV-99 gperry 115.15 Corrected error messages.
58 30-Dec-99 maagrawa 115.16 Bug 3431 (1096820) fixed.
59 Major re-structuring of package.
60 Added parameter business_group_id.
61 20-Jan-00 thayden 115.17 Get any per_in_ler_id, not just STRTD.
62 02-Feb-00 lmcdonal 115.18 Re-compute certain premiums before
63 updating the prtt-prem row.
64 18-Feb-00 jcarpent 115.19 Remove 115.14 change to set the
65 g_electable_choice_created flag
66 this is already done in bendenrr.
67 Bug 4720 (no wwbug number)
68 01-Mar-00 jcarpent 115.20 Pass bnft_amt_changed flag to
69 ben_election_rate_info. Put in
70 payroll change checks.
71 31-Mar-00 mmogel 115.21 I changed the message number from
72 91382 to 91832 in the message name
73 BEN_91382_PACKAGE_PARAM_NULL
74 14-May-00 gperry 115.22 Fixed bug 1298556. Rates get created
75 if activity changes and rate does
76 not.
77 25-May-00 lmcdonal 115.23 Bug 1312906 leap-froged from 115.21
78 for aera.
79 call imputed income if benefit
80 changes on subj-to-imp plan.
81 25-May-00 lmcdonal 115.24 Bug 1312906 'real' version.
82 29-May-00 gperry 115.25 Corrected cursor for bug 1298556.
83 05-Jan-01 kmahendr 115.26 Added parameter per_in_ler_id
84 16-Jan-01 mhoyes 115.27 EFC stuff. Added new OUT NOCOPY parameter
85 to election_rate_information.
86 19-feb-01 tilak 115.28 flex credit amout changes is not affecting
87 on next enrollment. cursor c_flex and c_bpl
88 is creatd to update the ledger
89 15-Mar-01 kmahendr 115.29 Modified cursor c_flex and c_bpl and added call
90 total_pools to write prtt_rt_val for flex credit
91 Bug#1653733
92 26 Jun 01 ikasire 115.30 bug 1849019 added two new procedures
93 prv_delete and get_rate_codes to
94 handle ENTRBL rate start date codes.
95 17 Aug 01 kmahendr 115.31 Added parameter p_mode to prv_delete and modified
96 codes
97 22 Aug 01 kmahendr 115.32 Made changes to prv_delete procedure for future dated
98 rates
99 10 Sep 01 kmahendr 115.33 Bug#1969043 - Imputed Income compute procedure is
100 added
101 25 Sep 01 kmahendr 115.34 Added parameter p_mode to main and added condition
102 'R' to look for enrollment result
103 26 Nov 01 dschwart/ 115.35 Bug#1646442: fixed invalid date use (was using date
104 BBurns benmngle run, not event date).
105 19 Mar 02 kmahendr 115.36 Bug#2273129 - cursor c_bpl is changed to return
106 row only for prvdd_val and total_pools procedure
107 is called to write flex credit rate if bnft_amt
108 is changed.
109 19 Mar 02 kmahendr 115.37 Added dbdrv lines.
110 30 Apr 02 kmahendr 115.38 Added token to message 91832.
111 06 May 02 kmahendr 115.39 Bug#2359835 - fix made for bug#2273129 broke for
112 non-flex programs - before calling total_pools
113 program type is checked.
114 08-Jun-02 pabodla 115.40 Do not select the contingent worker
115 assignment when assignment data is
116 fetched.
117 08-Aug-02 kmahendr 115.41 Bug#2382651 - added pgm_id to total_pools call.
118 19-Sep-02 mmudigon 115.42 Bug#2505008 - pass l_eff_dt instead of
119 p_eff_dt while calling imputed_income
120 19-Sep-02 ikasire 115.43 Bug 2551834 we need use nvl for l_ecr.val
121 while calling election_information
122 to compute rate changes.
123 28-Oct-02 kmahendr 115.44 Bug#2648512 - Effective date is modified according to the
124 date of election in imputed income.
125 11-dec-2002 hmani 115.45 NoCopy changes
126 21-Feb-2003 kmahendr 115.46 Bug#2776740 - added call - end_prtt_rt_val
127 25-Feb-2003 ikasire 115.47 Bug 2789814 fixes for future enrollment results
128 from the previous life events
129 04-Apr-2003 pbodla 115.48 Bug 2841161 : Copy DFF segments if the
130 benefit amount changes and new enrollment is created.
131 23-Apr-2003 kmahendr 115.49 New function Determine_change_in_flex added - bug#290823
132 28-May-2003 kmahendr 115.50 Added codes for canon fix.
133 04-Jun-2003 kmahendr 115.51 Added a cursor c_entr_val and not to consider rates
134 with enter value at enrollment.Bug#2959410
135 07-Aug-2003 iaksire 115.52 Bug 3044116 Added code to handle the cases like
136 LE after Old LE date and before old le effective
137 date. We need to use enrt_perd_start_dt plus 1
138 to avoid the issue of correction.
139 13-Aug-2003 kmahendr 115.53 Added codes for Cvg_mlf_cd - ERL
140 01-Oct-2003 mmudigon 115.54 Bug 2775742. Update rates for rt chg
141 process when element/input attached
142 to abr is changed.
143 26-Oct-2003 mmudigon 115.55 Bug 2775742. changed <> to = line 937
144 30-Oct-2003 ikasire 115.56 Bug 3192923 Override Thru date needs to be
145 handled to coverage and rates.
146 11-nov-2003 nhunur 115.57 changed '= to in' in determine_change_in_flex
147 20-Jan-2004 kmahendr 115.58 Bug#3378865 - added ele_entry_val_cd check for
148 calling election_rate_information
149 22-Jan-2004 kmahendr 115.59 Bug#3395033 - added codes to call total_pools
150 to recompute flex credits
151 28-Jan-2004 ikasire 115.60 Bug 3394862 When rate is enter value at
152 enrollment, recalc is not being processed for
153 payroll changes. on fp.F it computes but with
154 null values for the rates.
155 16-Feb-2004 mmudigon 115.61 Bug 3437083. Logic to determine abr
156 assignment changes
157 05-Apr-2004 kmahendr 115.62 Bug#3554751 - cursor c_ppe modified to look
158 for per_in_ler_stat_cd
159 07-Apr-2004 tjesumic 115.63 fonm parameter added
160 14-Apr-2004 mmudigon 115.64 Additional FONM changes
161 08-Jul-2004 kmahendr 115.65 Bug#3739641 - modified c_ecr cursors
162 27-Jul-2004 mmudigon 115.66 Bug 3797946. Logic to determine
163 change in extra input values
164 11-jan-2005 kmahendr 115.67 Bug#4113295 - the change is not compared for SAREC rates
165 10-feb-2005 mmudigon 115.68 Bug 4157759. Modified cursor c_prv
166 and added a new cursor c_prv_min_dt
167 17-Mar-2005 kmahendr 115.69 Bug#3856424 - Modified cursor c_pen
168 30-Aug-2005 kmahendr 115.70 Bug#4481319 - effective date is
169 assigned enrt_perd_start_date
170 03-Nov-2005 abparekh 115.71 Bug 4715688 - Added cases to detect coverage change
171 10-Nov-2005 abparekh 115.72 Bug 4723828 - Added cases to detect coverage change
172 09-Nov-2005 ikasire 115.73 Bug 4715657 recalc doesnot work if rate start date code
173 is a rule
174 09-Nov-2005 kmahendr 115.74 Bug#4872115 - regression of fix made in version
175 71 and 72
176 05-Jan-2006 abparekh 115.75 Bug 4895872 - Nullify all member variables of L_ENB
177 if C_ENB not found
178 09-Jan-2006 kmahendr 115.76 Bug#4938930 - added mlt_cd condition
179 to cursor c_enb
180 05-Apr-2006 abparekh 115.77 Bug 5126800 : SAAEAR case added to detect change
181 in benefit amount during Recalculate Run
182 08-sep-2006 ssarkar 115.78 Bug 5507982 - initialise variable element_changed to false
183 for each epe .
184 24-oct-2006 ikasired 115.79 Bug 5617091 - need to exclude interim enrollments from
185 being pickedup for rate change processing
186 30-Jan-2007 rgajula 115.80 Bug 5768795 - Added call to PEN_API.chk_arcs_breach in main
187 for each of the pgm_id's in the enrollment results.
188 18-jun-2007 rtagarra 115.81 Bug 6133258 - Made fix 5768795 compatible for numeric overflow error
189
190 13-Feb-2008 rtagarra 115.82 Bug 6528302 - Fixed cursor c_element_info.
191
192 22-Feb-2008 rtagarra 115.83 Bug 6840074
193 23-Apr-08 sallumwa 115.84 Reverted back the changes made for the Bug : 6528302
194 */
195
196 ----------------------------------------------------------------------------------------------------
197 --
198 g_package varchar2(80) := 'ben_determine_rate_chg';
199 --
200 PROCEDURE main
201 (p_effective_date in date,
202 p_lf_evt_ocrd_dt in date,
203 p_business_group_id in number,
204 p_person_id in number,
205 -- added per_in_ler_id for unrestricted enhancement
206 p_per_in_ler_id in number,
207 p_mode in varchar2) IS
208 --
209 l_package varchar2(80) := g_package||'.main ';
210 l_object_version_number number;
211 l_prtt_rt_val_id number;
212 l_dummy_number number;
213 l_dummy_date date;
214 l_dummy_bool boolean;
215 l_dummy_char varchar(30);
216 l_prtt_enrt_rslt_id number;
217 l_period_type varchar2(30);
218 --
219 /* Start of Changes for WWBUG: 1646442: added following block of local variables */
220 --
221 l_ppe_dt_to_use date;
222 l_correction boolean;
223 l_update boolean;
224 l_update_override boolean;
225 l_update_change_insert boolean;
226 l_ppe_datetrack_mode varchar2(30);
227 l_enrt_cvg_strt_dt date;
228 l_enrt_cvg_strt_dt_cd varchar2(30);
229 l_enrt_cvg_strt_dt_rl number;
230 l_rt_strt_dt_cd varchar2(30);
231 l_rt_strt_dt_rl number;
232 l_enrt_cvg_end_dt date;
233 l_enrt_cvg_end_dt_cd varchar2(30);
234 l_enrt_cvg_end_dt_rl number;
235 l_rt_end_dt date;
236 l_rt_end_dt_cd varchar2(30);
237 l_rt_end_dt_rl number;
238 /* End of Changes for WWBUG: 1646442 */
239 --
240 l_pen_found boolean := false;
241 l_enb_found boolean := false;
242 l_prv_found boolean := false;
243 l_ppe_found boolean := false;
244 l_bnft_changed boolean := false;
245 l_imp_changed boolean := false;
246 --
247 l_effective_date date := least(p_effective_date,
248 nvl(p_lf_evt_ocrd_dt,p_effective_date));
249 l_new_assignment_id number;
250 l_new_organization_id number;
251 l_new_payroll_id number;
252 --
253 cursor c_pil is
254 select pil.per_in_ler_id, pil.lf_evt_ocrd_dt, pil.ler_id
255 from ben_per_in_ler pil
256 where pil.person_id = p_person_id
257 -- added per_in_ler_id for unrestricted enhancement
258 and pil.per_in_ler_id = p_per_in_ler_id
259 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
260 l_pil c_pil%rowtype;
261 --
262 cursor c_epe(l_per_in_ler_id number) is
263 select epe.elig_per_elctbl_chc_id,
264 epe.elctbl_flag,
265 epe.prtt_enrt_rslt_id,
266 pel.acty_ref_perd_cd,
267 epe.pl_id,
268 epe.oipl_id,
269 epe.pil_elctbl_chc_popl_id,
270 epe.fonm_cvg_strt_dt,
271 pel.enrt_perd_id,
272 pel.lee_rsn_id,
273 pel.enrt_perd_strt_dt
274 from ben_elig_per_elctbl_chc epe,
275 ben_pil_elctbl_chc_popl pel
276 where epe.per_in_ler_id = l_per_in_ler_id
277 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
278 and epe.per_in_ler_id = pel.per_in_ler_id
279 and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
280 --
281 cursor c_epe2(l_per_in_ler_id number) is
282 select epe.elig_per_elctbl_chc_id,
283 epe.elctbl_flag,
284 epe.prtt_enrt_rslt_id,
285 pel.acty_ref_perd_cd,
286 epe.pl_id,
287 epe.oipl_id,
288 epe.pil_elctbl_chc_popl_id,
289 epe.fonm_cvg_strt_dt,
290 pel.enrt_perd_id,
291 pel.lee_rsn_id,
292 pel.enrt_perd_strt_dt
293 from ben_elig_per_elctbl_chc epe,
294 ben_pil_elctbl_chc_popl pel
295 where epe.per_in_ler_id = l_per_in_ler_id
296 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
297 and epe.per_in_ler_id = pel.per_in_ler_id
298 and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
299 and (exists (select null from ben_enrt_rt ecr
300 where ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
301 and ecr.rt_mlt_cd = 'ERL') or exists (
302 select null from ben_enrt_rt ecr, ben_enrt_bnft enb
303 where enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
304 and ecr.enrt_bnft_id = enb.enrt_bnft_id
305 and ecr.rt_mlt_cd = 'ERL'));
306
307 cursor c_epe3(l_per_in_ler_id number) is
308 select epe.elig_per_elctbl_chc_id,
309 epe.elctbl_flag,
310 epe.prtt_enrt_rslt_id,
311 pel.acty_ref_perd_cd,
312 epe.pl_id,
313 epe.oipl_id,
314 epe.pil_elctbl_chc_popl_id,
315 epe.fonm_cvg_strt_dt,
316 pel.enrt_perd_id,
317 pel.lee_rsn_id,
318 pel.enrt_perd_strt_dt
319 from ben_elig_per_elctbl_chc epe,
320 ben_pil_elctbl_chc_popl pel
321 where epe.per_in_ler_id = l_per_in_ler_id
322 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
323 and epe.per_in_ler_id = pel.per_in_ler_id
324 and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
325 and (exists (select null from ben_enrt_bnft enb
326 where enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
327 and enb.cvg_mlt_cd = 'ERL')
328 );
329 --
330 l_epe c_epe%rowtype;
331 --BUG 3192923 If the life event passed the override thru date we
332 --need to process the enrollments and rates.
333 --If there is no override thru date we can never override the enrollment
334 --
335 cursor c_pen is
336 select pen.prtt_enrt_rslt_id,
337 pen.enrt_mthd_cd,
338 pen.pl_id,
339 pen.pgm_id,
340 pen.pl_typ_id,
341 pen.oipl_id,
342 pen.ler_id,
343 pen.enrt_cvg_strt_dt,
344 pen.bnft_amt,
345 pen.object_version_number,
346 pen.person_id,
347 pen.business_group_id,
348 pen.per_in_ler_id,
349 -- Added for bug 2841161
350 pen.pen_attribute_category,
351 pen.pen_attribute1,
352 pen.pen_attribute2,
353 pen.pen_attribute3,
354 pen.pen_attribute4,
355 pen.pen_attribute5,
356 pen.pen_attribute6,
357 pen.pen_attribute7,
358 pen.pen_attribute8,
359 pen.pen_attribute9,
360 pen.pen_attribute10,
361 pen.pen_attribute11,
362 pen.pen_attribute12,
363 pen.pen_attribute13,
364 pen.pen_attribute14,
365 pen.pen_attribute15,
366 pen.pen_attribute16,
367 pen.pen_attribute17,
368 pen.pen_attribute18,
369 pen.pen_attribute19,
370 pen.pen_attribute20,
371 pen.pen_attribute21,
372 pen.pen_attribute22,
373 pen.pen_attribute23,
374 pen.pen_attribute24,
375 pen.pen_attribute25,
376 pen.pen_attribute26,
377 pen.pen_attribute27,
378 pen.pen_attribute28,
379 pen.pen_attribute29,
380 pen.pen_attribute30
381 from ben_prtt_enrt_rslt_f pen
382 where pen.prtt_enrt_rslt_id = l_epe.prtt_enrt_rslt_id
383 and pen.person_id = p_person_id
384 and pen.sspndd_flag = 'N'
385 and ( (pen.enrt_ovridn_flag = 'N' ) OR
386 (pen.enrt_ovridn_flag = 'Y' and nvl(pen.enrt_ovrid_thru_dt,hr_api.g_eot) < l_effective_date ))
387 and pen.enrt_cvg_thru_dt = hr_api.g_eot
388 and pen.prtt_enrt_rslt_stat_cd is null
389 and l_effective_date -- Bug 3044116 p_effective_date
390 between pen.effective_start_date and pen.effective_end_date
391 --bug#3856424 - check for any deenrollment in future - defensive coding
392 and exists (select null from ben_prtt_enrt_rslt_f pen2
393 where pen2.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
394 and pen2.enrt_cvg_thru_dt = hr_api.g_eot
395 and pen2.effective_end_date = hr_api.g_eot)
396 and not exists ( select 'x' from ben_prtt_enrt_rslt_f pen3 --Bug 5617091 exclude interim results
397 where pen3.rplcs_sspndd_rslt_id= pen.prtt_enrt_rslt_id
398 and pen3.sspndd_flag = 'Y'
399 and pen3.prtt_enrt_rslt_stat_cd is null
400 and pen3.enrt_cvg_thru_dt = hr_api.g_eot
401 and pen3.effective_end_date = hr_api.g_eot)
402 ;
403 --
404 l_pen c_pen%rowtype;
405 --
406 -- Bug 4715688 : The cursor is used to get the coverage amount. Now even if the person is not enrolled
407 -- we need to fetch the coverage amount to ascertain if coverage exists. Hence added
408 -- clause enb.prtt_enrt_rslt_id is null in following cursor
409 --
410 cursor c_enb is
411 select enb.val,
412 enb.enrt_bnft_id,
413 enb.prtt_enrt_rslt_id,
414 enb.entr_val_at_enrt_flag,
415 enb.CVG_MLT_CD
416 from ben_enrt_bnft enb
417 where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
418 and ( enb.prtt_enrt_rslt_id = l_epe.prtt_enrt_rslt_id OR
419 (enb.prtt_enrt_rslt_id is null /* Bug 4715688 */
420 and enb.cvg_mlt_cd not like '%RNG')--bug#4938930
421 );
422 --
423 l_enb c_enb%rowtype;
424 --
425 cursor c_ecr(v_elig_per_elctbl_chc_id in number,
426 v_enrt_bnft_id in number) is
427 select nvl(ecr.val,ecr.dflt_val) val,
428 ecr.enrt_rt_id,
429 nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
430 ecr.acty_base_rt_id,
431 ecr.acty_typ_cd,
432 ecr.tx_typ_cd,
433 ecr.rt_strt_dt_cd,
434 ecr.rt_strt_dt,
435 ecr.ENTR_VAL_AT_ENRT_FLAG,
436 ecr.rt_mlt_cd
437 from ben_enrt_rt ecr
438 where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
439 and ecr.business_group_id = p_business_group_id
440 -- added for canon fix
441 and ecr.rt_mlt_cd <> 'ERL'
442 and ecr.ASN_ON_ENRT_FLAG = 'Y'
443 UNION
444 select nvl(ecr.val,ecr.dflt_val) val,
445 ecr.enrt_rt_id,
446 nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
447 ecr.acty_base_rt_id,
448 ecr.acty_typ_cd,
449 ecr.tx_typ_cd,
450 ecr.rt_strt_dt_cd,
451 ecr.rt_strt_dt,
452 ecr.ENTR_VAL_AT_ENRT_FLAG,
453 ecr.rt_mlt_cd
454 from ben_enrt_rt ecr
455 where ecr.enrt_bnft_id = v_enrt_bnft_id
456 and ecr.business_group_id = p_business_group_id
457 -- added for canon fix
458 and ecr.rt_mlt_cd <> 'ERL'
459 and ecr.ASN_ON_ENRT_FLAG = 'Y';
460 --
461 l_ecr_row c_ecr%rowtype;
462 --
463 cursor c_entr_val (p_acty_base_rt_id number
464 ,p_effective_date date) is
465 select 'Y'
466 from dual
467 where exists (select null
468 from ben_acty_base_rt_f abr
469 where abr.PARNT_ACTY_BASE_RT_ID = p_acty_base_rt_id
470 and abr.PARNT_CHLD_CD = 'CHLD'
471 and abr.entr_val_at_enrt_flag = 'Y'
472 and abr.ACTY_BASE_RT_STAT_CD = 'A'
473 and p_effective_date between
474 abr.effective_start_date and abr.effective_end_date)
475 or exists (select null
476 from ben_acty_base_rt_f abr1,
477 ben_acty_base_rt_f abr2
478 where abr1.acty_base_rt_id = p_acty_base_rt_id
479 and abr1.PARNT_CHLD_CD = 'CHLD'
480 and abr1.PARNT_ACTY_BASE_RT_ID = abr2.acty_base_rt_id
481 and abr2.entr_val_at_enrt_flag = 'Y'
482 and abr2.ACTY_BASE_RT_STAT_CD = 'A'
483 and p_effective_date between
484 abr1.effective_start_date and abr1.effective_end_date
485 and p_effective_date between
486 abr2.effective_start_date and abr2.effective_end_date);
487 -- added for canon fix
488 cursor c_ecr2(v_elig_per_elctbl_chc_id in number,
489 v_enrt_bnft_id in number) is
490 select nvl(ecr.val,ecr.dflt_val) val,
491 ecr.enrt_rt_id,
492 nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
493 ecr.acty_base_rt_id,
494 ecr.acty_typ_cd,
495 ecr.tx_typ_cd,
496 ecr.rt_strt_dt_cd,
497 ecr.rt_strt_dt
498 from ben_enrt_rt ecr
499 where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
500 and ecr.business_group_id = p_business_group_id
501 -- added for canon fix
502 and ecr.rt_mlt_cd = 'ERL'
503 and ecr.ASN_ON_ENRT_FLAG = 'Y'
504 UNION
505 select nvl(ecr.val,ecr.dflt_val) val,
506 ecr.enrt_rt_id,
507 nvl(ecr.ann_val,ecr.ann_dflt_val) val,
508 ecr.acty_base_rt_id,
509 ecr.acty_typ_cd,
510 ecr.tx_typ_cd,
511 ecr.rt_strt_dt_cd,
512 ecr.rt_strt_dt
513 from ben_enrt_rt ecr
514 where ecr.enrt_bnft_id = v_enrt_bnft_id
515 and ecr.business_group_id = p_business_group_id
516 -- added for canon fix
517 and ecr.rt_mlt_cd = 'ERL'
518 and ecr.ASN_ON_ENRT_FLAG = 'Y';
519 --
520 --BUG 3192923 If the life event passed the override thru date we
521 --need to process the enrollments and rates.
522 --If there is no override thru date we can never override the enrollment
523 --
524 --
525 cursor c_prv(v_acty_base_rt_id number,
526 v_rt_strt_dt date) is
527 select prv.prtt_rt_val_id,
528 prv.rt_val,
529 prv.rt_strt_dt,
530 prv.elctns_made_dt,
531 prv.acty_typ_cd,
532 prv.tx_typ_cd,
533 prv.element_entry_value_id,
534 prv.rt_ovridn_flag,
535 prv.rt_ovridn_thru_dt
536 from ben_prtt_rt_val prv
537 where prv.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
538 and prv.acty_base_rt_id = v_acty_base_rt_id
539 and ((v_rt_strt_dt is not null and
540 v_rt_strt_dt between prv.rt_strt_dt and prv.rt_end_dt) or
541 (v_rt_strt_dt is null and
542 prv.rt_end_dt = hr_api.g_eot))
543 and prv.prtt_rt_val_stat_cd is null;
544 --
545 l_prv c_prv%rowtype;
546 --
547 cursor c_prv_min_dt(p_pen_id number,
548 p_abr_id number) is
549 select min(prv.rt_strt_dt)
550 from ben_prtt_rt_val prv
551 where prv.prtt_enrt_rslt_id = p_pen_id
552 and prv.acty_base_rt_id = p_abr_id
553 and prv.prtt_rt_val_stat_cd is null;
554
555 cursor c_epr(v_elig_per_elctbl_chc_id in number,
556 v_enrt_bnft_id in number) is
557 select epr.val,
558 epr.enrt_prem_id,
559 epr.actl_prem_id
560 from ben_enrt_prem epr
561 where epr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
562 and epr.business_group_id = p_business_group_id
563 UNION
564 select epr.val,
565 epr.enrt_prem_id,
566 epr.actl_prem_id
567 from ben_enrt_prem epr
568 where epr.enrt_bnft_id = v_enrt_bnft_id
569 and epr.business_group_id = p_business_group_id;
570 --
571 cursor c_ppe(v_actl_prem_id number
572 /* Start of Changes for WWBUG: 1646442 added following line */
573 ,p_ppe_dt_to_use date)
574 /* End of Changes for WWBUG: 1646442 */
575 is
576 select ppe.prtt_prem_id,
577 ppe.std_prem_val,
578 ppe.std_prem_uom,
579 ppe.object_version_number
580 from ben_prtt_prem_f ppe,
581 ben_per_in_ler pil
582 where ppe.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
583 and ppe.actl_prem_id = v_actl_prem_id
584 and ppe.per_in_ler_id = pil.per_in_ler_id
585 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
586 and ppe.business_group_id = p_business_group_id
587 /*
588 CODE PRIOR TO WWBUG: 1646442
589 and p_effective_date between
590 */
591 /* Start of Changes for WWBUG: 1646442 */
592 and p_ppe_dt_to_use between
593 /* End of Changes for WWBUG: 1646442 */
594 ppe.effective_start_date and ppe.effective_end_date;
595
596 --
597 l_ppe c_ppe%rowtype;
598 --
599 cursor c_payroll_type_changed(
600 cp_person_id number,
601 cp_business_group_id number,
602 cp_effective_date date,
603 cp_orig_effective_date date
604 ) is
605 select pay.period_type
606 from per_all_assignments_f asg,
607 pay_payrolls_f pay,
608 per_all_assignments_f asg2,
609 pay_payrolls_f pay2
610 where asg.person_id = cp_person_id
611 and asg.assignment_type <> 'C'
612 and asg.business_group_id = cp_business_group_id
613 and asg.primary_flag = 'Y'
614 and cp_effective_date between
615 asg.effective_start_date and asg.effective_end_date
616 and pay.payroll_id=asg.payroll_id
617 and pay.business_group_id = asg.business_group_id
618 and cp_effective_date between
619 pay.effective_start_date and pay.effective_end_date
620 and asg2.person_id = cp_person_id
621 and asg2.assignment_type <> 'C'
622 and asg2.business_group_id = cp_business_group_id
623 and asg2.primary_flag = 'Y'
624 and cp_orig_effective_date between
625 asg2.effective_start_date and asg2.effective_end_date
626 and pay2.payroll_id=asg2.payroll_id
627 and pay2.business_group_id = asg2.business_group_id
628 and cp_orig_effective_date between
629 pay2.effective_start_date and pay2.effective_end_date
630 and pay2.period_type<>pay.period_type
631 and asg.assignment_type = asg2.assignment_type ;
632
633 cursor c_pl(p_pl_id number,p_eff_dt date) is
634 select 'x' from ben_pl_f pl
635 where pl.pl_id = p_pl_id
636 and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
637 and p_eff_dt between
638 pl.effective_start_date and pl.effective_end_date;
639 l_pl c_pl%rowtype;
640
641 ---
642 --- to find the flex credit place holder choice and rate
643 --- flex credit amount is changes is not effecting the ledger
644 --- this find the flex credit place holder plan prvdd amount
645 --- then the amount will be compared with bnft_prvdd_ldgr row
646 -- if a rate cahnge found update the bnft_prvdd_ldgr
647 cursor c_flex_choice (p_pgm_id number) is
648 select epe.bnft_prvdr_pool_id,
649 epe.elig_per_elctbl_chc_id,
650 epe.prtt_enrt_rslt_id,
651 epe.business_group_id,
652 epe.per_in_ler_id,
653 ecr.enrt_rt_id,
654 ecr.acty_base_rt_id,
655 nvl(ecr.dflt_val, ecr.val) val
656 from ben_elig_per_elctbl_chc epe1,
657 ben_elig_per_elctbl_chc epe,
658 ben_enrt_rt ecr
659 where epe1.elig_per_elctbl_chc_id=l_epe.elig_per_elctbl_chc_id and
660 epe1.business_group_id=p_business_group_id and
661 epe1.pgm_id = epe.pgm_id and
662 epe1.per_in_ler_id = epe.per_in_ler_id and
663 epe.bnft_prvdr_pool_id is not null and
664 epe.business_group_id=p_business_group_id and
665 epe.pgm_id = p_pgm_id and
666 ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id and
667 ecr.rt_usg_cd = 'FLXCR' and
668 ecr.business_group_id = p_business_group_id;
669
670 ---to get the current provdd value
671
672 cursor c_bpl ( c_acty_base_rt_id number ,
673 c_bnft_prvdr_pool_id number ,
674 c_prtt_enrt_rslt_id number) is
675 select prvdd_val
676 from ben_bnft_prvdd_ldgr_f bpl,
677 ben_per_in_ler pil
678 where bpl.acty_base_rt_id = c_acty_base_rt_id
679 and bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
680 and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
681 and bpl.prvdd_val is not null
682 and pil.per_in_ler_id = bpl.per_in_ler_id
683 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
684 and bpl.business_group_id = p_business_group_id
685 and p_effective_date between
686 bpl.effective_start_date and bpl.effective_end_date ;
687 --
688 /* Start of Changes for WWBUG: 1646442: added cursor and variable */
689 cursor c_pel (p_elig_pe_elctbl_chc_id number) is
690 select pel.enrt_perd_id,pel.lee_rsn_id
691 from ben_pil_elctbl_chc_popl pel
692 ,ben_elig_per_elctbl_chc epe
693 where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
694 and epe.elig_per_elctbl_chc_id = p_elig_pe_elctbl_chc_id;
695 --
696 l_pelrec c_pel%rowtype;
697 /* End of Changes for WWBUG: 1646442 */
698 --
699 --Bug 3044116
700 cursor c_prtt_enrt (p_pgm_id number) is
701 select epe.prtt_enrt_rslt_id,pel.enrt_perd_strt_dt
702 from ben_pil_elctbl_chc_popl pel,
703 ben_elig_per_elctbl_chc epe
704 where pel.per_in_ler_id = p_per_in_ler_id
705 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
706 and epe.comp_lvl_cd = 'PLANFC'
707 and epe.pgm_id = p_pgm_id
708 and epe.business_group_id = p_business_group_id;
709 --
710 cursor c_pen2 (p_prtt_enrt_rslt_id number) is
711 select enrt_mthd_cd
712 from ben_prtt_enrt_rslt_f
713 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
714 and prtt_enrt_rslt_stat_cd is null
715 and l_effective_date -- Bug 3044116 p_effective_date
716 between effective_start_date and effective_end_date;
717 --
718 cursor c_imputed_rslt is
719 select effective_start_date
720 from ben_prtt_enrt_rslt_f pen,
721 ben_elig_per_elctbl_chc epe
722 where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
723 and epe.per_in_ler_id = p_per_in_ler_id
724 and epe.comp_lvl_cd = 'PLANIMP'
725 and epe.business_group_id = p_business_group_id
726 and pen.prtt_enrt_rslt_stat_cd is null
727 order by effective_start_date ;
728 --
729 cursor c_element_info (p_element_entry_value_id number,
730 p_prtt_enrt_rslt_id number) is
731 select elk.element_type_id,
732 eev.input_value_id,
733 asg.payroll_id,
734 pee.element_entry_id,
735 pee.assignment_id,
736 pee.effective_end_date
737 from per_all_assignments_f asg,
738 pay_element_links_f elk,
739 pay_element_entries_f pee,
740 pay_element_entry_values_f eev
741 where eev.element_entry_value_id = p_element_entry_value_id
742 and eev.element_entry_id = pee.element_entry_id
743 and pee.element_link_id = elk.element_link_id
744 and pee.effective_start_date between elk.effective_start_date
745 and elk.effective_end_date
746 and eev.effective_start_date between pee.effective_start_date
747 and pee.effective_end_date
748 and pee.creator_type = 'F'
749 and pee.creator_id = p_prtt_enrt_rslt_id
750 and asg.assignment_id = pee.assignment_id
751 and pee.effective_start_date between asg.effective_start_date
752 and asg.effective_end_date
753 /*
754 and p_effective_date between asg.effective_start_date
755 and asg.effective_end_date
756 */
757 order by pee.effective_end_date desc ;
758 l_element_info c_element_info%rowtype;
759 --
760 cursor c_abr(p_acty_base_rt_id number,
761 p_eff_dt date) is
762 select ele_rqd_flag,
763 element_type_id,
764 input_value_id,
765 ele_entry_val_cd,
766 input_va_calc_rl,
767 effective_start_date,
768 effective_end_date
769 from ben_acty_base_rt_f
770 where acty_base_rt_id = p_acty_base_rt_id
771 and p_eff_dt between effective_start_date
772 and effective_end_date;
773 l_abr c_abr%rowtype;
774 l_abr2 c_abr%rowtype;
775
776
777 l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
778 l_prtt_enrt_rslt_id_shell number ;
779 l_acty_ref_perd_cd varchar2(80);
780 l_acty_base_rt_id number;
781 l_bpp_rt_strt_dt date;
782 l_rt_strt_dt date;
783 l_rt_val number;
784 l_element_type_id number;
785 l_enrt_mthd_cd varchar2(80) := null;
786 l_perform_imp varchar2(1) := 'N';
787 l_start_date date;
788 l_entr_val_at_enrt_flag varchar2(1) := 'N';
789 l_element_changed boolean := FALSE;
790 --
791 l_enrt_perd_strt_dt date ;
792 l_total_pools_eff_dt date ;
793 l_enb_valrow ben_determine_coverage.ENBValType;
794 l_flex_call boolean := false;
795 l_ext_inpval_tab ben_element_entry.ext_inpval_tab_typ;
796 l_inpval_tab ben_element_entry.inpval_tab_typ;
797 l_jurisdiction_code varchar2(30);
798 l_subpriority number;
799 l_ext_inp_changed boolean;
800 l_prv_min_strt_dt date;
801
802 --Bug 6133258
803 /*--Start Bug 5768795
804 TYPE pgm_idtype IS TABLE OF BEN_PGM_F.PGM_ID%TYPE INDEX BY BINARY_INTEGER;
805 pgm_id_table pgm_idtype;
806 pgm_id_table_index BINARY_INTEGER;
807 --End Bug 5768795*/
808 TYPE pgm_idtype IS TABLE OF BEN_PGM_F.PGM_ID%TYPE INDEX BY VARCHAR2(50);
809 pgm_id_table pgm_idtype;
810 pgm_id_table_index VARCHAR2(50);
811 --Bug 6133258
812 --
813 BEGIN
814 --
815 hr_utility.set_location ('Entering '||l_package,10);
816 hr_utility.set_location ('ler date '||l_effective_date,10);
817 --
818 -- Edit to ensure that the input p_effective_date has a value
819 --
820 If p_effective_date is null then
821 --
822 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
823 fnd_message.set_token('PACKAGE',l_package);
824 fnd_message.set_token('PROC','Rate Change Event');
825 fnd_message.set_token('PARAM','p_effective_date');
826 fnd_message.raise_error;
827 --
828 elsif p_person_id is null then
829 --
830 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
831 fnd_message.set_token('PACKAGE',l_package);
832 fnd_message.set_token('PROC','Rate Change Event');
833 fnd_message.set_token('PARAM','p_person_id');
834 fnd_message.raise_error;
835 --
836 end if;
837 --
838 hr_utility.set_location (l_package,20);
839 --
840 -- Get the active per in ler id.
841 --
842 open c_pil;
843 fetch c_pil into l_pil;
844 close c_pil;
845 --
846 -- Get all electbale choices for the per in ler.
847 --
848 hr_utility.set_location ('Before loop '||l_package,25);
849 --
850 open c_epe(l_pil.per_in_ler_id);
851 --
852 loop
853 --
854 hr_utility.set_location ('In loop '||l_package,30);
855 --
856 fetch c_epe into l_epe;
857 exit when c_epe%notfound;
858 hr_utility.set_location('Found an epe', 32);
859 hr_utility.set_location('pl_id='||l_epe.pl_id,1963);
860 hr_utility.set_location('GP oipl_id='||l_epe.oipl_id,1963);
861 hr_utility.set_location('epe_id='||l_epe.elig_per_elctbl_chc_id,1963);
862 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1963);
863 --
864 -- Initialize the variables.
865 --
866 l_pen_found := false;
867 l_enb_found := false;
868 l_prv_found := false;
869 l_ppe_found := false;
870 l_bnft_changed := false;
871 l_imp_changed := false;
872 l_enrt_cvg_strt_dt := null;
873 l_rt_strt_dt := null;
874 l_element_changed := false; -- bug 5507982
875 --
876 --Bug 2789814 to resolve the issue of running the rate change before the
877 --already started pen effective_start_date. Get the ENRT_PERD_STRT_DT fromn
878 --the popl and if this is in future use that date instead of l_effective_date
879 --for all computations.
880 if l_epe.enrt_perd_strt_dt is not null then
881 l_effective_date := l_epe.enrt_perd_strt_dt ;
882 end if ;
883 --
884 hr_utility.set_location('Max l_effective_date '||l_effective_date,123);
885 --
886 -- Look for rate, benefit, premium changes only if the choice is
887 -- not electable (electable flag is off).
888 --
889 hr_utility.set_location('elctbl_flag='||l_epe.elctbl_flag,1963);
890 if l_epe.elctbl_flag = 'N' or p_mode = 'R' then
891 --
892 hr_utility.set_location ('An epe is not electable'||
893 to_char(l_epe.elig_per_elctbl_chc_id),35);
894 --
895 open c_pen;
896 fetch c_pen into l_pen;
897 --
898 if c_pen%found then
899 --
900 l_pen_found := true;
901 hr_utility.set_location('pen found',1963);
902 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1963);
903 --
904 else
905 hr_utility.set_location('pen not found',1963);
906 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1963);
907 end if;
908 --
909 close c_pen;
910 --
911 if l_pen_found then
912 --
913 -- Check for benefit amount changes.
914 --
915 hr_utility.set_location (l_package,40);
916 --
917 open c_enb;
918 fetch c_enb into l_enb;
919 --
920 if c_enb%found then
921 --
922 hr_utility.set_location('c_enb found enrt_bnft_id = ' || l_enb.enrt_bnft_id, 1999);
923 hr_utility.set_location('c_enb found prtt_enrt_rslt_id = ' || l_enb.prtt_enrt_rslt_id, 1999);
924 l_enb_found := true;
925 --
926 open c_pl(l_epe.pl_id,nvl(l_epe.fonm_cvg_strt_dt,p_effective_date));
927 fetch c_pl into l_pl;
928 if c_pl%FOUND then
929 l_perform_imp := 'Y';
930 end if;
931 close c_pl;
932 --
933 else
934 --
935 -- Reset bnft id to null, if bnft record does not exist.
936 --
937 -- Bug 4895872 - Nullify all member variables of L_ENB
938 hr_utility.set_location('c_enb not found', 1999);
939 l_enb.enrt_bnft_id := null;
940 l_enb.val := null;
941 l_enb.prtt_enrt_rslt_id := null;
942 l_enb.entr_val_at_enrt_flag := NULL;
943 --
944 end if; --
945 close c_enb;
946 --
947 --
948 if l_enb.CVG_MLT_CD = 'SAAEAR'
949 then
950 --
951 open c_ecr(v_elig_per_elctbl_chc_id => null,
952 v_enrt_bnft_id => l_enb.enrt_bnft_id );
953 --
954 fetch c_ecr into l_ecr_row;
955 --
956 close c_ecr;
957 --
958 end if;
959 --
960 if ben_manage_life_events.fonm = 'Y' then
961 ben_manage_life_events.g_fonm_cvg_strt_dt :=
962 l_epe.fonm_cvg_strt_dt;
963 end if;
964
965 hr_utility.set_location('Old Amount '||l_pen.bnft_amt,10);
966 hr_utility.set_location('New Amount '||l_enb.val,10);
967 --
968 -- Case B added for bug 4715688
969 -- Case C added for bug 4723828
970 -- Case : Same as Annualized Elected Activity Rate (SAAEAR) added for bug 5126800 - For this case
971 -- ENB.VAL will always be null. Now if ECR.ENTR_VAL_AT_ENRT_FLAG = Y, then we should not
972 -- update the PEN record with BNFT_AMT
973 --
974 if ( ( l_enb_found AND /* Case A : If earlier coverage existed and new coverage is different from old */
975 nvl(l_pen.bnft_amt, -1) <> nvl(l_enb.val, -1) and
976 l_enb.entr_val_at_enrt_flag = 'N' AND
977 l_enb.CVG_MLT_CD <> 'SAAEAR'
978 )
979 OR
980 (
981 l_enb.CVG_MLT_CD = 'SAAEAR' AND
982 l_ecr_row.ENTR_VAL_AT_ENRT_FLAG = 'N'
983 )
984 )
985 OR
986 ( l_enb.prtt_enrt_rslt_id is null AND /* Case B : If coverage didnt exist earlier but now coverage exists */
987 l_enb.val is not null
988 )
989 OR
990 ( l_enb.enrt_bnft_id IS NULL AND /* Case C : If coverage existed, but new coverage is absent or null */
991 l_pen.bnft_amt IS NOT NULL
992 )
993 then
994 --
995 -- When benefit amount changes, new result is written. The
996 -- process also writes the new premium records. The new rates
997 -- do NOT get created as we are not passing any rates.
998 -- Also note that the new result id gets passed on to the
999 -- variable l_pen.prtt_enrt_rslt_id.
1000
1001 l_bnft_changed := true;
1002
1003 -- check if we need to re-compute imputed income:
1004 -- if any plan in loop meets this criteria, recompute out of loop.
1005 open c_pl(l_epe.pl_id,nvl(l_epe.fonm_cvg_strt_dt,p_effective_date));
1006 fetch c_pl into l_pl;
1007 if c_pl%FOUND then
1008 l_imp_changed := true;
1009 end if;
1010 close c_pl;
1011
1012 hr_utility.set_location (l_package,45);
1013
1014 ben_election_information.election_information
1015 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
1016 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1017 p_effective_date => l_effective_date,
1018 p_enrt_mthd_cd => l_pen.enrt_mthd_cd,
1019 p_enrt_bnft_id => l_enb.enrt_bnft_id,
1020 p_bnft_val => l_enb.val,
1021 p_prtt_rt_val_id1 => l_dummy_number,
1022 p_prtt_rt_val_id2 => l_dummy_number,
1023 p_prtt_rt_val_id3 => l_dummy_number,
1024 p_prtt_rt_val_id4 => l_dummy_number,
1025 p_prtt_rt_val_id5 => l_dummy_number,
1026 p_prtt_rt_val_id6 => l_dummy_number,
1027 p_prtt_rt_val_id7 => l_dummy_number,
1028 p_prtt_rt_val_id8 => l_dummy_number,
1029 p_prtt_rt_val_id9 => l_dummy_number,
1030 p_prtt_rt_val_id10 => l_dummy_number,
1031 -- Bug 2841161
1032 p_pen_attribute_category => l_pen.pen_attribute_category,
1033 p_pen_attribute1 => l_pen.pen_attribute1,
1034 p_pen_attribute2 => l_pen.pen_attribute2,
1035 p_pen_attribute3 => l_pen.pen_attribute3,
1036 p_pen_attribute4 => l_pen.pen_attribute4,
1037 p_pen_attribute5 => l_pen.pen_attribute5,
1038 p_pen_attribute6 => l_pen.pen_attribute6,
1039 p_pen_attribute7 => l_pen.pen_attribute7,
1040 p_pen_attribute8 => l_pen.pen_attribute8,
1041 p_pen_attribute9 => l_pen.pen_attribute9,
1042 p_pen_attribute10 => l_pen.pen_attribute10,
1043 p_pen_attribute11 => l_pen.pen_attribute11,
1044 p_pen_attribute12 => l_pen.pen_attribute12,
1045 p_pen_attribute13 => l_pen.pen_attribute13,
1046 p_pen_attribute14 => l_pen.pen_attribute14,
1047 p_pen_attribute15 => l_pen.pen_attribute15,
1048 p_pen_attribute16 => l_pen.pen_attribute16,
1049 p_pen_attribute17 => l_pen.pen_attribute17,
1050 p_pen_attribute18 => l_pen.pen_attribute18,
1051 p_pen_attribute19 => l_pen.pen_attribute19,
1052 p_pen_attribute20 => l_pen.pen_attribute20,
1053 p_pen_attribute21 => l_pen.pen_attribute21,
1054 p_pen_attribute22 => l_pen.pen_attribute22,
1055 p_pen_attribute23 => l_pen.pen_attribute23,
1056 p_pen_attribute24 => l_pen.pen_attribute24,
1057 p_pen_attribute25 => l_pen.pen_attribute25,
1058 p_pen_attribute26 => l_pen.pen_attribute26,
1059 p_pen_attribute27 => l_pen.pen_attribute27,
1060 p_pen_attribute28 => l_pen.pen_attribute28,
1061 p_pen_attribute29 => l_pen.pen_attribute29,
1062 p_pen_attribute30 => l_pen.pen_attribute30,
1063 p_datetrack_mode => hr_api.g_update,
1064 p_suspend_flag => l_dummy_char,
1065 p_effective_start_date => l_dummy_date,
1066 p_effective_end_date => l_dummy_date,
1067 p_object_version_number => l_pen.object_version_number,
1068 p_prtt_enrt_interim_id => l_dummy_number,
1069 p_business_group_id => p_business_group_id,
1070 p_dpnt_actn_warning => l_dummy_bool,
1071 p_bnf_actn_warning => l_dummy_bool,
1072 p_ctfn_actn_warning => l_dummy_bool);
1073 --
1074 --Start Bug 5768795
1075 if((not pgm_id_table.exists(l_pen.pgm_id)) and l_pen.pgm_id is not null)then
1076 pgm_id_table(l_pen.pgm_id):=l_pen.pgm_id;
1077 hr_utility.set_location('pgm_id_table(l_pen.pgm_id) ' ||pgm_id_table(l_pen.pgm_id),10);
1078 end if;
1079 --End Bug 5768795
1080 --
1081 end if;
1082 --
1083 -- Look for rate changes.
1084 --
1085 hr_utility.set_location ('Before Rates ',50);
1086 --
1087 for l_ecr in c_ecr(l_epe.elig_per_elctbl_chc_id,
1088 l_enb.enrt_bnft_id) loop
1089 --
1090 hr_utility.set_location ('In rates loop ',55);
1091 --
1092 if l_ecr.rt_strt_dt_cd is null then
1093 l_rt_strt_dt := l_ecr.rt_strt_dt;
1094 end if;
1095 --BUG 4715657 This needs to be called if l_rt_strt_dt is null before calling c_prv
1096 if l_rt_strt_dt is null then
1097 --
1098 -- derive new rt strt dt
1099 --
1100 ben_determine_date.rate_and_coverage_dates
1101 (p_which_dates_cd => 'R'
1102 ,p_date_mandatory_flag => 'Y'
1103 ,p_compute_dates_flag => 'Y'
1104 ,p_business_group_id => p_business_group_id
1105 ,p_per_in_ler_id => l_pil.per_in_ler_id
1106 ,p_person_id => p_person_id
1107 ,p_pgm_id => l_pen.pgm_id
1108 ,p_pl_id => l_pen.pl_id
1109 ,p_oipl_id => l_pen.oipl_id
1110 ,p_lee_rsn_id => l_epe.lee_rsn_id
1111 ,p_enrt_perd_id => l_epe.enrt_perd_id
1112 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt --out
1113 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd --out
1114 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl --out
1115 ,p_rt_strt_dt => l_rt_strt_dt --out
1116 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd --out
1117 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl --out
1118 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt --out
1119 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd --out
1120 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl --out
1121 ,p_rt_end_dt => l_rt_end_dt --out
1122 ,p_rt_end_dt_cd => l_rt_end_dt_cd --out
1123 ,p_rt_end_dt_rl => l_rt_end_dt_rl --out
1124 ,p_effective_date => l_effective_date
1125 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
1126 );
1127
1128 end if; --l_rt_strt_dt
1129 --
1130 open c_prv(l_ecr.acty_base_rt_id,l_rt_strt_dt);
1131 fetch c_prv into l_prv;
1132 --
1133 if c_prv%found then
1134 --
1135 l_prv_found := true;
1136 --
1137 else
1138 --
1139 l_prv_found := false;
1140 l_prv.prtt_rt_val_id := null;
1141 --
1142 end if;
1143 --
1144 close c_prv;
1145 --
1146 -- Update rates when the rate has changed.
1147 -- Also write rates when benefit amount changes, as the rates
1148 -- were not written while creating the new result.
1149 --
1150 -- Fix for WWBUG 1298556
1151 -- Check if activity changed.
1152 --
1153 hr_utility.set_location('OLD RT'||l_prv.rt_val,10);
1154 hr_utility.set_location('NEW RT'||l_ecr.val,10);
1155 hr_utility.set_location('OLD ACT'||l_prv.acty_typ_cd,10);
1156 hr_utility.set_location('NEW ACT'||l_ecr.acty_typ_cd,10);
1157 -- Bug 2551834 If the ecr.val is null it fails
1158 -- Bug#2959410 - don't look for changes if the rate is enter value
1159 -- at enrollment as the val will always be null
1160 l_entr_val_at_enrt_flag := l_ecr.entr_val_at_enrt_flag;
1161 --
1162 if l_ecr.entr_val_at_enrt_flag = 'N' then
1163 -- to check whether parent or child is enter value at enrollment
1164 open c_entr_val (l_ecr.acty_base_rt_id, l_effective_date);
1165 fetch c_entr_val into l_entr_val_at_enrt_flag;
1166 close c_entr_val;
1167 --
1168 end if;
1169 --
1170 -- if the amt changed, no need to determine if the element
1171 -- info changed
1172 --
1173 if l_prv_found and
1174 (nvl(l_prv.rt_val,0) = nvl(l_ecr.val,0) OR
1175 l_prv.rt_ovridn_flag = 'Y') then
1176 --
1177 if l_rt_strt_dt is null then
1178 --
1179 -- derive new rt strt dt
1180 --
1181 ben_determine_date.rate_and_coverage_dates
1182 (p_which_dates_cd => 'R'
1183 ,p_date_mandatory_flag => 'Y'
1184 ,p_compute_dates_flag => 'Y'
1185 ,p_business_group_id => p_business_group_id
1186 ,p_per_in_ler_id => l_pil.per_in_ler_id
1187 ,p_person_id => p_person_id
1188 ,p_pgm_id => l_pen.pgm_id
1189 ,p_pl_id => l_pen.pl_id
1190 ,p_oipl_id => l_pen.oipl_id
1191 ,p_lee_rsn_id => l_epe.lee_rsn_id
1192 ,p_enrt_perd_id => l_epe.enrt_perd_id
1193 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt --out
1194 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd --out
1195 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl --out
1196 ,p_rt_strt_dt => l_rt_strt_dt --out
1197 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd --out
1198 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl --out
1199 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt --out
1200 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd --out
1201 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl --out
1202 ,p_rt_end_dt => l_rt_end_dt --out
1203 ,p_rt_end_dt_cd => l_rt_end_dt_cd --out
1204 ,p_rt_end_dt_rl => l_rt_end_dt_rl --out
1205 ,p_effective_date => l_effective_date
1206 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
1207 );
1208
1209 end if; --l_rt_strt_dt
1210 --
1211 end if; -- rv.rt_ovridn_flag = 'Y'
1212
1213 if ben_manage_life_events.fonm = 'Y' then
1214 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
1215 end if;
1216 --
1217 --Bug 3192923 If the rate was overriden in the previous enrollment
1218 --and the rate start date is not after the override thru date we
1219 --don't need to recompute the rates.
1220 --
1221 if (( l_prv_found and nvl(l_prv.rt_val,0) = nvl(l_ecr.val,0)) AND
1222 (l_prv.rt_ovridn_flag = 'N' OR
1223 (l_prv.rt_ovridn_flag= 'Y' and nvl(l_prv.rt_ovridn_thru_dt,hr_api.g_eot)< l_rt_strt_dt ))) then
1224 --
1225 -- get old element info
1226 --
1227 l_element_info := null;
1228 if l_prv.element_entry_value_id is not null then
1229 open c_element_info (l_prv.element_entry_value_id,
1230 l_pen.prtt_enrt_rslt_id);
1231 fetch c_element_info into l_element_info;
1232 close c_element_info;
1233 end if;
1234
1235 hr_utility.set_location('elt :'||l_element_info.element_type_id,10);
1236 hr_utility.set_location('inp :'||l_element_info.input_value_id,10);
1237 hr_utility.set_location('ee end:'||l_element_info.effective_end_date,10);
1238 hr_utility.set_location('rt strt :'||l_rt_strt_dt,10);
1239 --
1240 -- get current element info
1241 --
1242 open c_abr(l_ecr.acty_base_rt_id,l_rt_strt_dt);
1243 fetch c_abr into l_abr;
1244 close c_abr;
1245 --
1246 -- get old values on standard rate
1247 --
1248 if not (l_prv.rt_strt_dt between l_abr.effective_start_date and
1249 l_abr.effective_end_date) then
1250 open c_abr(l_ecr.acty_base_rt_id,l_prv.rt_strt_dt);
1251 fetch c_abr into l_abr2;
1252 close c_abr;
1253 else
1254 l_abr2 := l_abr;
1255 end if;
1256 --
1257 --get the new abr assignment and payroll
1258 --
1259 l_new_assignment_id := null;
1260 l_new_organization_id := null;
1261 l_new_payroll_id := null;
1262
1263 ben_element_entry.get_abr_assignment
1264 (p_person_id => p_person_id,
1265 p_effective_date => l_rt_strt_dt,
1266 p_acty_base_rt_id => l_ecr.acty_base_rt_id,
1267 p_assignment_id => l_new_assignment_id,
1268 p_payroll_id => l_new_payroll_id,
1269 p_organization_id => l_new_organization_id);
1270
1271 --
1272 -- determine if element info changed
1273 --
1274 l_element_changed :=
1275 (l_prv.element_entry_value_id is not null and
1276 l_abr.ele_rqd_flag = 'N') or
1277 (l_abr.ele_rqd_flag = 'Y' and
1278 ((nvl(l_element_info.input_value_id,-1) <>
1279 l_abr.input_value_id) or
1280 (nvl(l_element_info.element_type_id,-1) <>
1281 l_abr.element_type_id) or
1282 (l_element_info.effective_end_date < l_rt_strt_dt) or
1283 (l_element_info.assignment_id <>
1284 nvl(l_new_assignment_id,-1)) or
1285 (nvl(l_element_info.payroll_id,-1) <>
1286 nvl(l_new_payroll_id,-1)) or
1287 (nvl(l_abr.ele_entry_val_cd,'PP') <>
1288 nvl(l_abr2.ele_entry_val_cd,'PP')))
1289 );
1290 --
1291 -- determine if extra input values changed
1292 --
1293 if not l_element_changed then
1294
1295 l_ext_inpval_tab.delete;
1296 ben_element_entry.get_extra_ele_inputs
1297 (p_effective_date => l_rt_strt_dt
1298 ,p_person_id => p_person_id
1299 ,p_business_group_id => p_business_group_id
1300 ,p_assignment_id => l_new_assignment_id
1301 ,p_element_link_id => null
1302 ,p_entry_type => 'E'
1303 ,p_input_value_id1 => null
1304 ,p_entry_value1 => null
1305 ,p_element_entry_id => null
1306 ,p_acty_base_rt_id => l_ecr.acty_base_rt_id
1307 ,p_input_va_calc_rl => l_abr.input_va_calc_rl
1308 ,p_abs_ler => null
1309 ,p_organization_id => l_new_organization_id
1310 ,p_payroll_id => l_new_payroll_id
1311 ,p_pgm_id => l_pen.pgm_id
1312 ,p_pl_id => l_pen.pl_id
1313 ,p_pl_typ_id => l_pen.pl_typ_id
1314 ,p_opt_id => null
1315 ,p_ler_id => l_pen.ler_id
1316 ,p_dml_typ => 'C'
1317 ,p_jurisdiction_code => l_jurisdiction_code
1318 ,p_ext_inpval_tab => l_ext_inpval_tab
1319 ,p_subpriority => l_subpriority
1320 );
1321
1322 ben_element_entry.get_inpval_tab
1323 (p_element_entry_id => l_element_info.element_entry_id
1324 ,p_effective_date => l_rt_strt_dt
1325 ,p_inpval_tab => l_inpval_tab);
1326
1327 l_ext_inp_changed := false;
1328 for i in 1..l_ext_inpval_tab.count
1329 loop
1330 for j in 1..l_inpval_tab.count
1331 loop
1332 if (l_ext_inpval_tab(i).input_value_id =
1333 l_inpval_tab(j).input_value_id) and
1334 (nvl(l_ext_inpval_tab(i).return_value,'-1') <>
1335 nvl(l_inpval_tab(j).value,'-1')) then
1336 l_ext_inp_changed := true;
1337 exit;
1338 end if;
1339 end loop;
1340 if l_ext_inp_changed then
1341 exit;
1342 end if;
1343 end loop;
1344
1345 l_element_changed := l_ext_inp_changed;
1346
1347 end if;
1348
1349 end if;
1350 --
1351 open c_prv_min_dt(l_pen.prtt_enrt_rslt_id,l_ecr.acty_base_rt_id);
1352 fetch c_prv_min_dt into l_prv_min_strt_dt;
1353 close c_prv_min_dt;
1354
1355 if ((l_rt_strt_dt >= nvl(l_prv_min_strt_dt,l_rt_strt_dt)) and
1356 ((NOT l_prv_found) OR
1357 (l_prv.rt_ovridn_flag = 'N' OR
1358 (l_prv.rt_ovridn_flag= 'Y' and
1359 nvl(l_prv.rt_ovridn_thru_dt,hr_api.g_eot)< l_rt_strt_dt))))
1360 then
1361 --
1362 /* Bug 3394862 We need to modify this if clause such that
1363 l_entr_val_at_enrt_flag is checked only
1364 with nvl(l_prv.rt_val,0) <> nvl(l_ecr.val,0) condition.
1365 --
1366 if (l_entr_val_at_enrt_flag = 'N' and
1367 (nvl(l_prv.rt_val,0) <> nvl(l_ecr.val,0) or
1368 nvl(l_prv.acty_typ_cd,'-1') <> nvl(l_ecr.acty_typ_cd,'-1') or
1369 nvl(l_prv.tx_typ_cd,'-1') <> nvl(l_ecr.tx_typ_cd,'-1') or
1370 l_element_changed)) or
1371 l_bnft_changed then
1372 */
1373 /* bug#4113295 - SAREC condition added as there is no need to create rate
1374 if there is no change in benefit amount * will anyway create rates
1375 */
1376
1377 if (l_entr_val_at_enrt_flag = 'N' and
1378 nvl(l_prv.rt_val,0) <> nvl(l_ecr.val,0)
1379 and l_ecr.rt_mlt_cd <> 'SAREC') or
1380 nvl(l_prv.acty_typ_cd,'-1') <> nvl(l_ecr.acty_typ_cd,'-1') or
1381 nvl(l_prv.tx_typ_cd,'-1') <> nvl(l_ecr.tx_typ_cd,'-1') or
1382 l_element_changed or
1383 l_bnft_changed then
1384 --
1385 hr_utility.set_location ('Calling rate_info ',60);
1386 hr_utility.set_location('rate change',13);
1387 --
1388 l_flex_call := true;
1389 ben_election_information.election_rate_information
1390 (p_enrt_mthd_cd => l_pen.enrt_mthd_cd
1391 ,p_effective_date => l_effective_date
1392 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1393 ,p_per_in_ler_id => l_pil.per_in_ler_id
1394 ,p_person_id => p_person_id
1395 ,p_pgm_id => l_pen.pgm_id
1396 ,p_pl_id => l_pen.pl_id
1397 ,p_oipl_id => l_pen.oipl_id
1398 ,p_enrt_rt_id => l_ecr.enrt_rt_id
1399 ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
1400 ,p_rt_val => l_ecr.val
1401 ,p_ann_rt_val => l_ecr.ann_val
1402 ,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
1403 ,p_acty_ref_perd_cd => l_epe.acty_ref_perd_cd
1404 ,p_datetrack_mode => hr_api.g_update
1405 ,p_business_group_id => p_business_group_id
1406 ,p_bnft_amt_changed => l_bnft_changed
1407 ,p_ele_changed => l_element_changed
1408 ,p_prv_rt_val => l_dummy_number
1409 ,p_prv_ann_rt_val => l_dummy_number
1410 );
1411 --
1412 end if;
1413 --
1414 end if; --Override if
1415 --
1416 end loop;
1417
1418 hr_utility.set_location ('Before imputed and premium process ',65);
1419
1420 if l_bnft_changed then
1421 -- if benefit amount changed, check to see if imputed
1422 -- income should also change.
1423 if l_imp_changed then
1424 ben_det_imputed_income.p_comp_imputed_income
1425 (p_person_id => p_person_id
1426 ,p_enrt_mthd_cd => 'A'
1427 ,p_per_in_ler_id => l_pil.per_in_ler_id
1428 ,p_effective_date => l_effective_date
1429 ,p_business_group_id => p_business_group_id
1430 ,p_ctrlm_fido_call => false
1431 ,p_validate => false);
1432 --
1433 l_perform_imp := 'N';
1434 end if;
1435 --
1436 -- call total-pools to compute flex credit rate - bug#2273129
1437 open c_prtt_enrt (l_pen.pgm_id);
1438 fetch c_prtt_enrt into l_prtt_enrt_rslt_id_shell,l_enrt_perd_strt_dt ;
1439 close c_prtt_enrt;
1440 --Bug 3044116
1441 l_total_pools_eff_dt := l_effective_date ;
1442 --
1443 /* l_effective_date is already assigned the enrt_perd_strt_dt
1444 if l_enrt_perd_strt_dt > l_effective_date then
1445 l_total_pools_eff_dt := l_enrt_perd_strt_dt + 1 ;
1446 end if ;
1447 */
1448 --
1449 if l_prtt_enrt_rslt_id_shell is not null then
1450 ben_provider_pools.total_pools(
1451 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_shell,
1452 p_prtt_rt_val_id => l_prtt_rt_val_id,
1453 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1454 p_acty_base_rt_id => l_acty_base_rt_id,
1455 p_rt_strt_dt => l_bpp_rt_strt_dt,
1456 p_rt_val => l_rt_val,
1457 p_element_type_id => l_element_type_id,
1458 p_person_id => p_person_id,
1459 p_per_in_ler_id => p_per_in_ler_id,
1460 p_enrt_mthd_cd => 'E' ,
1461 p_effective_date => l_total_pools_eff_dt, -- l_pil.lf_evt_ocrd_dt,
1462 p_business_group_id => p_business_group_id,
1463 p_pgm_id => l_pen.pgm_id
1464 );
1465 --
1466 end if;
1467 --
1468
1469 else
1470 open c_prtt_enrt (l_pen.pgm_id);
1471 fetch c_prtt_enrt into l_prtt_enrt_rslt_id_shell,l_enrt_perd_strt_dt ;
1472 close c_prtt_enrt;
1473 if l_prtt_enrt_rslt_id_shell is null then
1474 --call flex credit recompute even if there is no change in provided
1475 --value as the net amount might have been changed because of used value
1476 l_flex_call := false;
1477 else
1478 --
1479 open c_pen2(l_prtt_enrt_rslt_id_shell);
1480 fetch c_pen2 into l_enrt_mthd_cd;
1481 close c_pen2;
1482 --
1483 end if;
1484 --Bug 3044116
1485 l_total_pools_eff_dt := l_effective_date ;
1486 --
1487 /*
1488 if l_enrt_perd_strt_dt > l_effective_date then
1489 l_total_pools_eff_dt := l_enrt_perd_strt_dt + 1 ;
1490 end if ;
1491 */
1492 -- cal for the flex credit changes
1493 for i in c_flex_choice (l_pen.pgm_id) Loop
1494 l_prvdd_val := null;
1495 open c_bpl ( i.acty_base_rt_id ,
1496 i.bnft_prvdr_pool_id,
1497 l_prtt_enrt_rslt_id_shell ) ;
1498 fetch c_bpl into l_prvdd_val ;
1499 close c_bpl ;
1500 --if there is amount change call the updating
1501 hr_utility.set_location( 'prvdd val ' || l_prvdd_val , 1001);
1502 hr_utility.set_location( 'changed val ' || i.val , 1001);
1503 hr_utility.set_location('Result id'||l_prtt_enrt_rslt_id_shell,1002);
1504
1505
1506 if l_prvdd_val is not null and l_prvdd_val <> i.val then
1507 ben_provider_pools.create_credit_ledger_entry
1508 ( p_person_id => p_person_id ,
1509 p_elig_per_elctbl_chc_id => i.elig_per_elctbl_chc_id ,
1510 p_per_in_ler_id => i.per_in_ler_id,
1511 p_business_group_id => p_business_group_id ,
1512 p_bnft_prvdr_pool_id => i.bnft_prvdr_pool_id,
1513 p_enrt_mthd_cd => l_pen.enrt_mthd_cd,
1514 p_effective_date => l_effective_date
1515 --l_pil.lf_evt_ocrd_dt
1516 );
1517 --
1518 /*
1519 open c_pen2(l_prtt_enrt_rslt_id_shell);
1520 fetch c_pen2 into l_enrt_mthd_cd;
1521 close c_pen2;
1522 */
1523 --
1524 l_flex_call := false;
1525 ben_provider_pools.total_pools(
1526 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_shell,
1527 p_prtt_rt_val_id => l_prtt_rt_val_id,
1528 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1529 p_acty_base_rt_id => l_acty_base_rt_id,
1530 p_rt_strt_dt => l_bpp_rt_strt_dt,
1531 p_rt_val => l_rt_val,
1532 p_element_type_id => l_element_type_id,
1533 p_person_id => p_person_id,
1534 p_per_in_ler_id => p_per_in_ler_id,
1535 p_enrt_mthd_cd => l_enrt_mthd_cd ,
1536 p_effective_date => l_total_pools_eff_dt , --l_pil.lf_evt_ocrd_dt,
1537 p_business_group_id => p_business_group_id,
1538 p_pgm_id => l_pen.pgm_id
1539 );
1540
1541 end if;
1542 end loop ;
1543 --UK select bug
1544 if l_flex_call then
1545 -- call total_pools to recompute the net credit
1546 l_flex_call := false;
1547 ben_provider_pools.total_pools(
1548 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_shell,
1549 p_prtt_rt_val_id => l_prtt_rt_val_id,
1550 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
1551 p_acty_base_rt_id => l_acty_base_rt_id,
1552 p_rt_strt_dt => l_bpp_rt_strt_dt,
1553 p_rt_val => l_rt_val,
1554 p_element_type_id => l_element_type_id,
1555 p_person_id => p_person_id,
1556 p_per_in_ler_id => p_per_in_ler_id,
1557 p_enrt_mthd_cd => l_enrt_mthd_cd ,
1558 p_effective_date => l_total_pools_eff_dt ,
1559 p_business_group_id => p_business_group_id,
1560 p_pgm_id => l_pen.pgm_id
1561 );
1562 end if;
1563
1564 -- Look for premium changes.
1565 -- If the benefit amount has changed, the premium would
1566 -- already have changed by the election_information call.
1567 for l_epr in c_epr(l_epe.elig_per_elctbl_chc_id,
1568 l_enb.enrt_bnft_id) loop
1569 hr_utility.set_location ('In Prem. loop ',70);
1570 --
1571 -- derive new rt strt dt
1572 --
1573 if l_rt_strt_dt is null then
1574 ben_determine_date.rate_and_coverage_dates
1575 (p_which_dates_cd => 'R'
1576 ,p_date_mandatory_flag => 'Y'
1577 ,p_compute_dates_flag => 'Y'
1578 ,p_business_group_id => p_business_group_id
1579 ,p_per_in_ler_id => l_pil.per_in_ler_id
1580 ,p_person_id => p_person_id
1581 ,p_pgm_id => l_pen.pgm_id
1582 ,p_pl_id => l_pen.pl_id
1583 ,p_oipl_id => l_pen.oipl_id
1584 ,p_lee_rsn_id => l_epe.lee_rsn_id
1585 ,p_enrt_perd_id => l_epe.enrt_perd_id
1586 ,p_enrt_cvg_strt_dt => l_dummy_date
1587 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
1588 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
1589 ,p_rt_strt_dt => l_rt_strt_dt
1590 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
1591 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
1592 ,p_enrt_cvg_end_dt => l_dummy_date
1593 ,p_enrt_cvg_end_dt_cd => l_dummy_char
1594 ,p_enrt_cvg_end_dt_rl => l_dummy_number
1595 ,p_rt_end_dt => l_dummy_date
1596 ,p_rt_end_dt_cd => l_dummy_char
1597 ,p_rt_end_dt_rl => l_dummy_number
1598 ,p_effective_date => p_effective_date
1599 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
1600 );
1601 end if;
1602
1603 if l_enrt_cvg_strt_dt is null then
1604 ben_determine_date.rate_and_coverage_dates
1605 (p_which_dates_cd => 'C'
1606 ,p_date_mandatory_flag => 'Y'
1607 ,p_compute_dates_flag => 'Y'
1608 ,p_business_group_id => p_business_group_id
1609 ,p_per_in_ler_id => l_pil.per_in_ler_id
1610 ,p_person_id => p_person_id
1611 ,p_pgm_id => l_pen.pgm_id
1612 ,p_pl_id => l_pen.pl_id
1613 ,p_oipl_id => l_pen.oipl_id
1614 ,p_lee_rsn_id => l_epe.lee_rsn_id
1615 ,p_enrt_perd_id => l_epe.enrt_perd_id
1616 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt --out
1617 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
1618 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
1619 ,p_rt_strt_dt => l_dummy_date
1620 ,p_rt_strt_dt_cd => l_dummy_char
1621 ,p_rt_strt_dt_rl => l_dummy_number
1622 ,p_enrt_cvg_end_dt => l_dummy_date
1623 ,p_enrt_cvg_end_dt_cd => l_dummy_char
1624 ,p_enrt_cvg_end_dt_rl => l_dummy_number
1625 ,p_rt_end_dt => l_dummy_date
1626 ,p_rt_end_dt_cd => l_dummy_char
1627 ,p_rt_end_dt_rl => l_dummy_number
1628 ,p_effective_date => p_effective_date
1629 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
1630 );
1631 end if;
1632
1633 if ben_manage_life_events.fonm = 'Y' then
1634 l_ppe_dt_to_use := l_enrt_cvg_strt_dt;
1635 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
1636 ben_manage_life_events.g_fonm_cvg_strt_dt := l_enrt_cvg_strt_dt;
1637
1638 else
1639 l_ppe_dt_to_use := greatest(l_pen.enrt_cvg_strt_dt,l_rt_strt_dt);
1640 end if;
1641
1642 open c_ppe(l_epr.actl_prem_id,l_ppe_dt_to_use);
1643 fetch c_ppe into l_ppe;
1644
1645 if c_ppe%found then
1646 l_ppe_found := true;
1647 else
1648 l_ppe_found := false;
1649 end if;
1650 close c_ppe;
1651
1652 if l_ppe_found then
1653 -- Because the benefit amount could have changed, and the premiums
1654 -- can be based on the benefit amount, re-calc it. It does a recalc
1655 -- if the benefit amount is entered at enrollment.
1656 ben_PRTT_PREM_api.recalc_PRTT_PREM
1657 (p_prtt_prem_id => l_ppe.prtt_prem_id
1658 ,p_std_prem_uom => l_ppe.std_prem_uom
1659 ,p_std_prem_val => l_epr.val -- in/out
1660 ,p_actl_prem_id => l_epr.actl_prem_id
1661 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1662 ,p_per_in_ler_id => l_pil.per_in_ler_id
1663 ,p_ler_id => l_pil.ler_id
1664 ,p_lf_evt_ocrd_dt => l_pil.lf_evt_ocrd_dt
1665 ,p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
1666 ,p_enrt_bnft_id => l_enb.enrt_bnft_id
1667 ,p_business_group_id => p_business_group_id
1668 ,p_effective_date => l_effective_date --9999p_effective_date
1669 -- bof FONM
1670 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1671 ,p_rt_strt_dt => l_rt_strt_dt
1672 -- eof FONM
1673 );
1674 --
1675 if l_ppe.std_prem_val <> l_epr.val then
1676 hr_utility.set_location ('Updating prem. ',75);
1677 /* Start of Changes for WWBUG: 1646442 */
1678 --
1679 --Find the valid datetrack modes.
1680 --
1681 dt_api.find_dt_upd_modes
1682 (p_effective_date => l_ppe_dt_to_use,
1683 p_base_table_name => 'BEN_PRTT_PREM_F',
1684 p_base_key_column => 'prtt_prem_id',
1685 p_base_key_value => l_ppe.prtt_prem_id,
1686 p_correction => l_correction,
1687 p_update => l_update,
1688 p_update_override => l_update_override,
1689 p_update_change_insert => l_update_change_insert);
1690
1691 if l_update_override then
1692 l_ppe_datetrack_mode := hr_api.g_update_override;
1693 elsif l_update then
1694 l_ppe_datetrack_mode := hr_api.g_update;
1695 else
1696 l_ppe_datetrack_mode := hr_api.g_correction;
1697 end if;
1698 /* End of Changes for WWBUG: 1646442 */
1699
1700 ben_prtt_prem_api.update_prtt_prem
1701 (p_prtt_prem_id => l_ppe.prtt_prem_id,
1702 p_object_version_number => l_ppe.object_version_number,
1703 p_std_prem_val => l_epr.val,
1704 p_per_in_ler_id => l_pil.per_in_ler_id,
1705 /*
1706 CODE PRIOR TO WWBUG: 1646442
1707 p_effective_date => l_effective_date, --9999 p_effective_date,
1708 p_datetrack_mode => hr_api.g_update,
1709 */
1710 /* Start of Changes for WWBUG: 1646442 */
1711 p_effective_date => l_ppe_dt_to_use,
1712 p_datetrack_mode => l_ppe_datetrack_mode,
1713 /* End of Changes for WWBUG: 1646442 */
1714 p_effective_start_date => l_dummy_date,
1715 p_effective_end_date => l_dummy_date);
1716 end if;
1717 end if;
1718 end loop; -- c_epr
1719
1720 end if; -- l_bnft_changed
1721 --
1722 -- Bug#2776740 - if the standard rate already enrolled is not valid in this life event
1723 -- then end the participant rate
1724 ben_det_enrt_rates.end_prtt_rt_val
1725 (p_person_id => l_pen.person_id
1726 ,p_per_in_ler_id => p_per_in_ler_id
1727 ,p_business_group_id => l_pen.business_group_id
1728 ,p_effective_date => l_effective_date -- 9999 p_effective_date
1729 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1730 );
1731 --
1732 end if; -- l_pen_found
1733 --
1734 end if; -- elctbl_flag = 'N'
1735 --
1736 end loop; -- c_epe
1737 --
1738 hr_utility.set_location ('Out of epe loop '||l_package,85);
1739 --
1740 close c_epe;
1741 --
1742 --gevity fix
1743 -- to prevent any bleeding of the variable it is reinitialised
1744 l_flex_call := false;
1745
1746 open c_epe3(l_pil.per_in_ler_id);
1747 --
1748 loop
1749 --
1750 hr_utility.set_location ('In loop '||l_package,321);
1751 --
1752 fetch c_epe3 into l_epe;
1753 exit when c_epe3%notfound;
1754 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1964);
1755 l_pen_found := false;
1756 l_enrt_cvg_strt_dt := null;
1757 l_rt_strt_dt := null;
1758
1759 if l_epe.elctbl_flag = 'N' or p_mode = 'R' then
1760 --
1761 hr_utility.set_location ('An epe is not electable'||
1762 to_char(l_epe.elig_per_elctbl_chc_id),33);
1763 --
1764 open c_pen;
1765 fetch c_pen into l_pen;
1766 --
1767 if c_pen%found then
1768 --
1769 l_pen_found := true;
1770 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1964);
1771 --
1772 else
1773 hr_utility.set_location('pen not found',1963);
1774 end if;
1775 --
1776 close c_pen;
1777 --
1778 if l_pen_found then
1779 --
1780 if ben_manage_life_events.fonm = 'Y' then
1781 ben_manage_life_events.g_fonm_cvg_strt_dt :=
1782 l_epe.fonm_cvg_strt_dt;
1783 end if;
1784
1785 ben_determine_coverage.main
1786 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
1787 ,p_effective_date => p_effective_date
1788 ,p_lf_evt_ocrd_dt => l_pil.lf_evt_ocrd_dt
1789 ,p_perform_rounding_flg => true
1790 --
1791 ,p_enb_valrow => l_enb_valrow
1792 ,p_calculate_only_mode => TRUE
1793 );
1794 --
1795 if nvl(l_enb_valrow.val,0) <> nvl(l_pen.bnft_amt,0) then
1796 --
1797 open c_enb;
1798 fetch c_enb into l_enb;
1799 close c_enb;
1800 --
1801 ben_election_information.election_information
1802 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
1803 p_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id,
1804 p_effective_date => l_effective_date,
1805 p_enrt_mthd_cd => l_pen.enrt_mthd_cd,
1806 p_enrt_bnft_id => l_enb.enrt_bnft_id,
1807 p_bnft_val => l_enb_valrow.val,
1808 p_prtt_rt_val_id1 => l_dummy_number,
1809 p_prtt_rt_val_id2 => l_dummy_number,
1810 p_prtt_rt_val_id3 => l_dummy_number,
1811 p_prtt_rt_val_id4 => l_dummy_number,
1812 p_prtt_rt_val_id5 => l_dummy_number,
1813 p_prtt_rt_val_id6 => l_dummy_number,
1814 p_prtt_rt_val_id7 => l_dummy_number,
1815 p_prtt_rt_val_id8 => l_dummy_number,
1816 p_prtt_rt_val_id9 => l_dummy_number,
1817 p_prtt_rt_val_id10 => l_dummy_number,
1818 p_pen_attribute_category => l_pen.pen_attribute_category,
1819 p_pen_attribute1 => l_pen.pen_attribute1,
1820 p_pen_attribute2 => l_pen.pen_attribute2,
1821 p_pen_attribute3 => l_pen.pen_attribute3,
1822 p_pen_attribute4 => l_pen.pen_attribute4,
1823 p_pen_attribute5 => l_pen.pen_attribute5,
1824 p_pen_attribute6 => l_pen.pen_attribute6,
1825 p_pen_attribute7 => l_pen.pen_attribute7,
1826 p_pen_attribute8 => l_pen.pen_attribute8,
1827 p_pen_attribute9 => l_pen.pen_attribute9,
1828 p_pen_attribute10 => l_pen.pen_attribute10,
1829 p_pen_attribute11 => l_pen.pen_attribute11,
1830 p_pen_attribute12 => l_pen.pen_attribute12,
1831 p_pen_attribute13 => l_pen.pen_attribute13,
1832 p_pen_attribute14 => l_pen.pen_attribute14,
1833 p_pen_attribute15 => l_pen.pen_attribute15,
1834 p_pen_attribute16 => l_pen.pen_attribute16,
1835 p_pen_attribute17 => l_pen.pen_attribute17,
1836 p_pen_attribute18 => l_pen.pen_attribute18,
1837 p_pen_attribute19 => l_pen.pen_attribute19,
1838 p_pen_attribute20 => l_pen.pen_attribute20,
1839 p_pen_attribute21 => l_pen.pen_attribute21,
1840 p_pen_attribute22 => l_pen.pen_attribute22,
1841 p_pen_attribute23 => l_pen.pen_attribute23,
1842 p_pen_attribute24 => l_pen.pen_attribute24,
1843 p_pen_attribute25 => l_pen.pen_attribute25,
1844 p_pen_attribute26 => l_pen.pen_attribute26,
1845 p_pen_attribute27 => l_pen.pen_attribute27,
1846 p_pen_attribute28 => l_pen.pen_attribute28,
1847 p_pen_attribute29 => l_pen.pen_attribute29,
1848 p_pen_attribute30 => l_pen.pen_attribute30,
1849 p_datetrack_mode => hr_api.g_update,
1850 p_suspend_flag => l_dummy_char,
1851 p_effective_start_date => l_dummy_date,
1852 p_effective_end_date => l_dummy_date,
1853 p_object_version_number => l_pen.object_version_number,
1854 p_prtt_enrt_interim_id => l_dummy_number,
1855 p_business_group_id => p_business_group_id,
1856 p_dpnt_actn_warning => l_dummy_bool,
1857 p_bnf_actn_warning => l_dummy_bool,
1858 p_ctfn_actn_warning => l_dummy_bool);
1859
1860 --Start Bug 5768795
1861 if((not pgm_id_table.exists(l_pen.pgm_id)) and l_pen.pgm_id is not null)then
1862 pgm_id_table(l_pen.pgm_id):=l_pen.pgm_id;
1863 hr_utility.set_location('pgm_id_table(l_pen.pgm_id) ' ||pgm_id_table(l_pen.pgm_id),20);
1864 end if;
1865 --End Bug 5768795
1866
1867
1868 for l_ecr in c_ecr(l_epe.elig_per_elctbl_chc_id,
1869 l_enb.enrt_bnft_id) loop
1870
1871 if ben_manage_life_events.fonm = 'Y' then
1872 if l_ecr.rt_strt_dt_cd is null then
1873 l_rt_strt_dt := l_ecr.rt_strt_dt;
1874 end if;
1875 --
1876 -- derive new rt strt dt
1877 --
1878 if l_rt_strt_dt is null then
1879 ben_determine_date.rate_and_coverage_dates
1880 (p_which_dates_cd => 'R'
1881 ,p_date_mandatory_flag => 'Y'
1882 ,p_compute_dates_flag => 'Y'
1883 ,p_business_group_id => p_business_group_id
1884 ,p_per_in_ler_id => l_pil.per_in_ler_id
1885 ,p_person_id => p_person_id
1886 ,p_pgm_id => l_pen.pgm_id
1887 ,p_pl_id => l_pen.pl_id
1888 ,p_oipl_id => l_pen.oipl_id
1889 ,p_lee_rsn_id => l_epe.lee_rsn_id
1890 ,p_enrt_perd_id => l_epe.enrt_perd_id
1891 ,p_enrt_cvg_strt_dt => l_dummy_date
1892 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
1893 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
1894 ,p_rt_strt_dt => l_rt_strt_dt
1895 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
1896 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
1897 ,p_enrt_cvg_end_dt => l_dummy_date
1898 ,p_enrt_cvg_end_dt_cd => l_dummy_char
1899 ,p_enrt_cvg_end_dt_rl => l_dummy_number
1900 ,p_rt_end_dt => l_dummy_date
1901 ,p_rt_end_dt_cd => l_dummy_char
1902 ,p_rt_end_dt_rl => l_dummy_number
1903 ,p_effective_date => p_effective_date
1904 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
1905 );
1906 end if;
1907
1908 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
1909
1910 end if;
1911
1912 l_flex_call := true;
1913 ben_election_information.election_rate_information
1914 (p_enrt_mthd_cd => l_pen.enrt_mthd_cd
1915 ,p_effective_date => nvl(l_effective_date,p_effective_date)
1916 ,p_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id
1917 ,p_per_in_ler_id => l_pil.per_in_ler_id
1918 ,p_person_id => p_person_id
1919 ,p_pgm_id => l_pen.pgm_id
1920 ,p_pl_id => l_pen.pl_id
1921 ,p_oipl_id => l_pen.oipl_id
1922 ,p_enrt_rt_id => l_ecr.enrt_rt_id
1923 ,p_prtt_rt_val_id => l_prtt_rt_val_id
1924 ,p_rt_val => l_ecr.val
1925 ,p_ann_rt_val => l_ecr.ann_val
1926 ,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
1927 ,p_acty_ref_perd_cd => l_epe.acty_ref_perd_cd
1928 ,p_datetrack_mode => null
1929 ,p_business_group_id => p_business_group_id
1930 --
1931 ,p_prv_rt_val => l_dummy_number
1932 ,p_prv_ann_rt_val => l_dummy_number
1933 );
1934 end loop; -- c_ecr
1935 --
1936 end if;
1937 --
1938 end if;
1939 --
1940 end if;
1941 --
1942 end loop; -- c_epe3
1943 close c_epe3;
1944 -- canon fix
1945 hr_utility.set_location ('Before c_epe2 loop '||l_package,86);
1946 --
1947 l_pen_found := false;
1948 --
1949 open c_epe2(l_pil.per_in_ler_id);
1950 --
1951 loop
1952 --
1953 hr_utility.set_location ('In loop '||l_package,31);
1954 --
1955 fetch c_epe2 into l_epe;
1956 exit when c_epe2%notfound;
1957 hr_utility.set_location('Found an epe', 32);
1958 hr_utility.set_location('pl_id='||l_epe.pl_id,1964);
1959 hr_utility.set_location('GP oipl_id='||l_epe.oipl_id,1964);
1960 hr_utility.set_location('epe_id='||l_epe.elig_per_elctbl_chc_id,1964);
1961 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1964);
1962 l_pen_found := false;
1963 l_enb_found := false;
1964 l_prv_found := false;
1965 l_enrt_cvg_strt_dt := null;
1966 l_rt_strt_dt := null;
1967 if l_epe.elctbl_flag = 'N' or p_mode = 'R' then
1968 --
1969 hr_utility.set_location ('An epe is not electable'||
1970 to_char(l_epe.elig_per_elctbl_chc_id),33);
1971 --
1972 open c_pen;
1973 fetch c_pen into l_pen;
1974 --
1975 if c_pen%found then
1976 --
1977 l_pen_found := true;
1978 hr_utility.set_location('pen found',1964);
1979 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1964);
1980 --
1981 else
1982 hr_utility.set_location('pen not found',1963);
1983 hr_utility.set_location('pen_id='||l_epe.prtt_enrt_rslt_id,1963);
1984 end if;
1985 --
1986 close c_pen;
1987 --
1988 if l_pen_found then
1989 --
1990 hr_utility.set_location (l_package,40);
1991 --
1992 if ben_manage_life_events.fonm = 'Y' then
1993 ben_manage_life_events.g_fonm_cvg_strt_dt :=
1994 l_epe.fonm_cvg_strt_dt;
1995 end if;
1996
1997 open c_enb;
1998 fetch c_enb into l_enb;
1999 --
2000 if c_enb%found then
2001 --
2002 l_enb_found := true;
2003 else
2004 --
2005 l_enb.enrt_bnft_id := null;
2006 --
2007 end if; --
2008 close c_enb;
2009 for l_ecr in c_ecr2(l_epe.elig_per_elctbl_chc_id,
2010 l_enb.enrt_bnft_id) loop
2011 if ben_manage_life_events.fonm = 'Y' then
2012 if l_ecr.rt_strt_dt_cd is null then
2013 l_rt_strt_dt := l_ecr.rt_strt_dt;
2014 end if;
2015 --
2016 -- derive new rt strt dt
2017 --
2018 if l_rt_strt_dt is null then
2019 ben_determine_date.rate_and_coverage_dates
2020 (p_which_dates_cd => 'R'
2021 ,p_date_mandatory_flag => 'Y'
2022 ,p_compute_dates_flag => 'Y'
2023 ,p_business_group_id => p_business_group_id
2024 ,p_per_in_ler_id => l_pil.per_in_ler_id
2025 ,p_person_id => p_person_id
2026 ,p_pgm_id => l_pen.pgm_id
2027 ,p_pl_id => l_pen.pl_id
2028 ,p_oipl_id => l_pen.oipl_id
2029 ,p_lee_rsn_id => l_epe.lee_rsn_id
2030 ,p_enrt_perd_id => l_epe.enrt_perd_id
2031 ,p_enrt_cvg_strt_dt => l_dummy_date
2032 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
2033 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
2034 ,p_rt_strt_dt => l_rt_strt_dt
2035 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
2036 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
2037 ,p_enrt_cvg_end_dt => l_dummy_date
2038 ,p_enrt_cvg_end_dt_cd => l_dummy_char
2039 ,p_enrt_cvg_end_dt_rl => l_dummy_number
2040 ,p_rt_end_dt => l_dummy_date
2041 ,p_rt_end_dt_cd => l_dummy_char
2042 ,p_rt_end_dt_rl => l_dummy_number
2043 ,p_effective_date => p_effective_date
2044 ,p_lf_evt_ocrd_dt => nvl(l_pil.lf_evt_ocrd_dt,p_effective_date)
2045 );
2046 end if;
2047
2048 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
2049
2050 end if;
2051
2052 l_flex_call := true;
2053 --
2054 ben_election_information.election_rate_information
2055 (p_enrt_mthd_cd => l_pen.enrt_mthd_cd
2056 ,p_effective_date => nvl(l_effective_date,p_effective_date)
2057 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
2058 ,p_per_in_ler_id => l_pil.per_in_ler_id
2059 ,p_person_id => p_person_id
2060 ,p_pgm_id => l_pen.pgm_id
2061 ,p_pl_id => l_pen.pl_id
2062 ,p_oipl_id => l_pen.oipl_id
2063 ,p_enrt_rt_id => l_ecr.enrt_rt_id
2064 ,p_prtt_rt_val_id => l_prtt_rt_val_id
2065 ,p_rt_val => l_ecr.val
2066 ,p_ann_rt_val => l_ecr.ann_val
2067 ,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
2068 ,p_acty_ref_perd_cd => l_epe.acty_ref_perd_cd
2069 ,p_datetrack_mode => null
2070 ,p_business_group_id => p_business_group_id
2071 --
2072 ,p_prv_rt_val => l_dummy_number
2073 ,p_prv_ann_rt_val => l_dummy_number
2074 );
2075 end loop; -- c_ecr2
2076 --
2077 --
2078 end if;
2079 --
2080 end if;
2081 --
2082 end loop; -- c_epe2
2083 close c_epe2;
2084 --
2085
2086 --Start Bug 5768795
2087
2088 pgm_id_table_index := pgm_id_table.first;
2089
2090
2091 if(pgm_id_table_index is not null) then
2092
2093 loop
2094
2095 ben_PRTT_ENRT_RESULT_api.chk_coverage_across_plan_types
2096 (p_person_id =>p_person_id,
2097 p_effective_date =>p_effective_date,
2098 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
2099 p_business_group_id =>p_business_group_id,
2100 p_pgm_id =>pgm_id_table(pgm_id_table_index),
2101 p_minimum_check_flag =>'N',
2102 p_suspended_enrt_check_flag =>'N');
2103
2104 exit when pgm_id_table_index = pgm_id_table.last;
2105
2106 pgm_id_table_index := pgm_id_table.next(pgm_id_table_index);
2107 end loop;
2108
2109 end if;
2110
2111 --End Bug 5768795
2112
2113 if l_pen_found or l_flex_call then
2114 --
2115 open c_prtt_enrt (l_pen.pgm_id);
2116 fetch c_prtt_enrt into l_prtt_enrt_rslt_id_shell,l_enrt_perd_strt_dt ;
2117 close c_prtt_enrt;
2118 --Bug 3044116
2119 l_total_pools_eff_dt := l_effective_date ;
2120 --
2121 /*
2122 if l_enrt_perd_strt_dt > l_effective_date then
2123 l_total_pools_eff_dt := l_enrt_perd_strt_dt + 1 ;
2124 end if ;
2125 */
2126 --
2127 if l_prtt_enrt_rslt_id_shell is not null then
2128 ben_provider_pools.total_pools(
2129 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_shell,
2130 p_prtt_rt_val_id => l_prtt_rt_val_id,
2131 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
2132 p_acty_base_rt_id => l_acty_base_rt_id,
2133 p_rt_strt_dt => l_bpp_rt_strt_dt,
2134 p_rt_val => l_rt_val,
2135 p_element_type_id => l_element_type_id,
2136 p_person_id => p_person_id,
2137 p_per_in_ler_id => p_per_in_ler_id,
2138 p_enrt_mthd_cd => 'E' ,
2139 p_effective_date => l_total_pools_eff_dt, -- l_pil.lf_evt_ocrd_dt,
2140 p_business_group_id => p_business_group_id,
2141 p_pgm_id => l_pen.pgm_id
2142 );
2143 --
2144 end if;
2145 --
2146 end if;
2147
2148 -- Bug#1969043
2149 if l_perform_imp = 'Y' then
2150 --
2151 open c_imputed_rslt;
2152 fetch c_imputed_rslt into l_start_date;
2153 close c_imputed_rslt;
2154 --
2155 if l_start_date is not null then
2156 if l_effective_date < l_start_date then
2157 l_effective_date := l_start_date ;
2158 end if;
2159 end if;
2160 --
2161 ben_det_imputed_income.p_comp_imputed_income
2162 (p_person_id => p_person_id
2163 ,p_enrt_mthd_cd => 'E'
2164 ,p_per_in_ler_id => l_pil.per_in_ler_id
2165 ,p_effective_date => l_effective_date
2166 ,p_business_group_id => p_business_group_id
2167 ,p_ctrlm_fido_call => false
2168 ,p_validate => false);
2169 --
2170 end if;
2171 --
2172 hr_utility.set_location ('Leaving '||l_package,10);
2173 --
2174 end main;
2175 --
2176 procedure prv_delete(p_prtt_rt_val_id in number ,
2177 p_enrt_rt_id in number,
2178 p_rt_val in number,
2179 p_rt_strt_dt in date,
2180 p_business_group_id in number,
2181 p_prtt_enrt_rslt_id in number,
2182 p_person_id in number,
2183 p_effective_date in date,
2184 p_mode in varchar2 default 'NEW'
2185 ) is
2186 --
2187 l_object_version_number number;
2188 l_acty_base_rt_id number ;
2189 cursor c_prv is
2190 select
2191 prv.rt_val,
2192 prv.rt_strt_dt,
2193 prv.object_version_number
2194 from ben_prtt_rt_val prv
2195 where prv.prtt_rt_val_id = p_prtt_rt_val_id and
2196 prv.business_group_id = p_business_group_id and
2197 prv.prtt_rt_val_stat_cd is null
2198 ;
2199 --
2200 l_prv c_prv%rowtype;
2201 --
2202 cursor c_prv_future is
2203 select
2204 prv.*
2205 from ben_prtt_rt_val prv
2206 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
2207 prv.business_group_id = p_business_group_id and
2208 prv.acty_base_rt_id = l_acty_base_rt_id and
2209 prv.prtt_rt_val_stat_cd is null
2210 order by prv.rt_strt_dt desc
2211 ;
2212 --
2213 cursor c_prv_past is
2214 select
2215 prv.*
2216 from ben_prtt_rt_val prv
2217 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
2218 prv.rt_end_dt = (l_prv.rt_strt_dt -1) and
2219 prv.business_group_id = p_business_group_id and
2220 prv.acty_base_rt_id = l_acty_base_rt_id and
2221 prv.prtt_rt_val_stat_cd is null
2222 order by prv.rt_end_dt desc
2223 ;
2224 --
2225 cursor c_ecr is
2226 select
2227 ecr.*
2228 from ben_enrt_rt ecr
2229 where ecr.enrt_rt_id = p_enrt_rt_id and
2230 ecr.business_group_id = p_business_group_id ;
2231 --
2232 cursor c_abr is
2233 select
2234 abr.*,
2235 py.processing_type
2236 from ben_acty_base_rt_f abr,
2237 pay_element_types_f py
2238 where
2239 abr.acty_base_rt_id = l_acty_base_rt_id
2240 and abr.business_group_id = p_business_group_id
2241 and p_effective_date between
2242 abr.effective_start_date and abr.effective_end_date
2243 and abr.element_type_id = py.element_type_id(+)
2244 and p_effective_date between
2245 py.effective_start_date(+) and py.effective_end_date(+);
2246
2247 --
2248 l_prv_future c_prv_future%rowtype;
2249 l_ecr c_ecr%rowtype;
2250 l_abr c_abr%rowtype;
2251 l_delete boolean := false;
2252 l_update_prtt boolean := false;
2253 --
2254 begin
2255 --
2256 open c_prv ;
2257 fetch c_prv into l_prv ;
2258 close c_prv;
2259 --
2260 open c_ecr ;
2261 fetch c_ecr into l_ecr;
2262 close c_ecr ;
2263 l_acty_base_rt_id := l_ecr.acty_base_rt_id;
2264 --
2265
2266 if l_prv.rt_strt_dt < p_rt_strt_dt and p_mode = 'UPD' then
2267 ben_prtt_rt_val_api.delete_prtt_rt_val
2268 (p_prtt_rt_val_id => p_prtt_rt_val_id
2269 ,p_person_id => p_person_id
2270 ,p_business_group_id => p_business_group_id
2271 ,p_object_version_number => l_prv.object_version_number
2272 ,p_effective_date => p_effective_date
2273 );
2274 --
2275 update ben_enrt_rt ecr
2276 set prtt_rt_val_id = null
2277 where enrt_rt_id = p_enrt_rt_id ;
2278 --
2279 elsif l_prv.rt_strt_dt < p_rt_strt_dt and p_mode = 'NEW' then
2280 -- do nothing
2281 null;
2282 --
2283 elsif l_prv.rt_strt_dt > p_rt_strt_dt then
2284 --
2285 open c_abr ;
2286 fetch c_abr into l_abr;
2287 close c_abr;
2288 --
2289 open c_prv_future ;
2290 loop
2291 fetch c_prv_future into l_prv_future ;
2292 if c_prv_future%notfound
2293 then
2294 exit ;
2295 end if;
2296 --
2297 if l_prv_future.rt_strt_dt > p_rt_strt_dt then
2298 --
2299 ben_prtt_rt_val_api.delete_prtt_rt_val
2300 (p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
2301 ,p_person_id => p_person_id
2302 ,p_business_group_id => p_business_group_id
2303 ,p_object_version_number => l_prv_future.object_version_number
2304 ,p_effective_date => p_effective_date
2305 );
2306 --
2307 l_delete := true;
2308 --
2309 elsif l_delete then -- if l_prv_future.rt_val <> p_rt_val then
2310 --
2311 /*
2312 update ben_enrt_rt ecr
2313 set prtt_rt_val_id = null -- l_prv_future.prtt_rt_val_id
2314 where enrt_rt_id = p_enrt_rt_id ;
2315 */
2316 if l_abr.processing_type = 'N' or
2317 l_abr.rcrrg_cd = 'ONCE' then
2318 -- do nothing for non recurring rate
2319 null;
2320 else
2321
2322 ben_prtt_rt_val_api.update_prtt_rt_val(
2323 p_validate => FALSE
2324 ,p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
2325 ,p_person_id => p_person_id
2326 ,p_input_value_id => l_abr.input_value_id
2327 ,p_element_type_id => l_abr.element_type_id
2328 ,p_enrt_rt_id => l_ecr.enrt_rt_id
2329 ,p_rt_strt_dt => l_prv_future.rt_strt_dt
2330 ,p_rt_end_dt => p_rt_strt_dt-1
2331 ,p_rt_typ_cd => l_prv_future.rt_typ_cd
2332 ,p_tx_typ_cd => l_prv_future.tx_typ_cd
2333 ,p_acty_typ_cd => l_abr.acty_typ_cd
2334 ,p_mlt_cd => l_prv_future.mlt_cd
2335 ,p_acty_ref_perd_cd => l_prv_future.acty_ref_perd_cd
2336 ,p_rt_val => l_prv_future.rt_val
2337 ,p_ann_rt_val => l_prv_future.ann_rt_val
2338 ,p_cmcd_rt_val => l_prv_future.cmcd_rt_val
2339 ,p_cmcd_ref_perd_cd => l_prv_future.cmcd_ref_perd_cd
2340 ,p_bnft_rt_typ_cd => l_prv_future.bnft_rt_typ_cd
2341 ,p_dsply_on_enrt_flag => l_prv_future.dsply_on_enrt_flag
2342 ,p_rt_ovridn_flag => l_prv_future.rt_ovridn_flag
2343 ,p_rt_ovridn_thru_dt => l_prv_future.rt_ovridn_thru_dt
2344 ,p_elctns_made_dt => l_prv_future.elctns_made_dt
2345 ,p_prtt_rt_val_stat_cd => l_prv_future.prtt_rt_val_stat_cd
2346 ,p_prtt_enrt_rslt_id => l_prv_future.prtt_enrt_rslt_id
2347 ,p_cvg_amt_calc_mthd_id => l_prv_future.cvg_amt_calc_mthd_id
2348 ,p_actl_prem_id => l_prv_future.actl_prem_id
2349 ,p_comp_lvl_fctr_id => l_prv_future.comp_lvl_fctr_id
2350 ,p_element_entry_value_id => l_prv_future.element_entry_value_id
2351 ,p_per_in_ler_id => l_prv_future.per_in_ler_id
2352 ,p_ended_per_in_ler_id => l_prv_future.ended_per_in_ler_id
2353 ,p_acty_base_rt_id => l_prv_future.acty_base_rt_id
2354 ,p_prtt_reimbmt_rqst_id => l_prv_future.prtt_reimbmt_rqst_id
2355 ,p_business_group_id => l_prv_future.business_group_id
2356 ,p_prv_attribute_category => l_prv_future.prv_attribute_category
2357 ,p_prv_attribute1 => l_prv_future.prv_attribute1
2358 ,p_prv_attribute2 => l_prv_future.prv_attribute2
2359 ,p_prv_attribute3 => l_prv_future.prv_attribute3
2360 ,p_prv_attribute4 => l_prv_future.prv_attribute4
2361 ,p_prv_attribute5 => l_prv_future.prv_attribute5
2362 ,p_prv_attribute6 => l_prv_future.prv_attribute6
2363 ,p_prv_attribute7 => l_prv_future.prv_attribute7
2364 ,p_prv_attribute8 => l_prv_future.prv_attribute8
2365 ,p_prv_attribute9 => l_prv_future.prv_attribute9
2366 ,p_prv_attribute10 => l_prv_future.prv_attribute10
2367 ,p_prv_attribute11 => l_prv_future.prv_attribute11
2368 ,p_prv_attribute12 => l_prv_future.prv_attribute12
2369 ,p_prv_attribute13 => l_prv_future.prv_attribute13
2370 ,p_prv_attribute14 => l_prv_future.prv_attribute14
2371 ,p_prv_attribute15 => l_prv_future.prv_attribute15
2372 ,p_prv_attribute16 => l_prv_future.prv_attribute16
2373 ,p_prv_attribute17 => l_prv_future.prv_attribute17
2374 ,p_prv_attribute18 => l_prv_future.prv_attribute18
2375 ,p_prv_attribute19 => l_prv_future.prv_attribute19
2376 ,p_prv_attribute20 => l_prv_future.prv_attribute20
2377 ,p_prv_attribute21 => l_prv_future.prv_attribute21
2378 ,p_prv_attribute22 => l_prv_future.prv_attribute22
2379 ,p_prv_attribute23 => l_prv_future.prv_attribute23
2380 ,p_prv_attribute24 => l_prv_future.prv_attribute24
2381 ,p_prv_attribute25 => l_prv_future.prv_attribute25
2382 ,p_prv_attribute26 => l_prv_future.prv_attribute26
2383 ,p_prv_attribute27 => l_prv_future.prv_attribute27
2384 ,p_prv_attribute28 => l_prv_future.prv_attribute28
2385 ,p_prv_attribute29 => l_prv_future.prv_attribute29
2386 ,p_prv_attribute30 => l_prv_future.prv_attribute30
2387 ,p_object_version_number => l_prv_future.object_version_number
2388 ,p_effective_date => p_effective_date
2389 );
2390 --
2391 exit;
2392 --
2393 end if;
2394 end if;
2395 --
2396 end loop;
2397 --
2398 if l_delete then
2399 --
2400 update ben_enrt_rt ecr
2401 set prtt_rt_val_id = null -- l_prv_future.prtt_rt_val_id
2402 where enrt_rt_id = p_enrt_rt_id ;
2403 --
2404 end if;
2405
2406 close c_prv_future ;
2407 --
2408 end if ;
2409 --
2410 end prv_delete;
2411 --
2412 --
2413 -- This is a wrapper to get rt_end_dt_cd and rt_srt_dt_cd from forms.
2414 --
2415 procedure get_rate_codes
2416 (p_business_group_id in number
2417 ,p_elig_per_elctbl_chc_id in number
2418 ,p_rt_strt_dt_cd out nocopy varchar2
2419 ,p_rt_end_dt_cd out nocopy varchar2
2420 ,p_acty_base_rt_id in number
2421 ,p_effective_date in date) is
2422 --
2423 l_dummy_var varchar2(30);
2424 l_dummy_date date;
2425 l_dummy_number number;
2426 --
2427 begin
2428 --
2429 ben_determine_date.rate_and_coverage_dates
2430 (p_which_dates_cd => 'R'
2431 ,p_compute_dates_flag => 'N'
2432 ,p_business_group_id => p_business_group_id
2433 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2434 ,p_enrt_cvg_strt_dt => l_dummy_date
2435 ,p_enrt_cvg_strt_dt_cd => l_dummy_var
2436 ,p_enrt_cvg_strt_dt_rl => l_dummy_var
2437 ,p_rt_strt_dt => l_dummy_date
2438 ,p_rt_strt_dt_cd => p_rt_strt_dt_cd
2439 ,p_rt_strt_dt_rl => l_dummy_var
2440 ,p_enrt_cvg_end_dt => l_dummy_date
2441 ,p_enrt_cvg_end_dt_cd => l_dummy_var
2442 ,p_enrt_cvg_end_dt_rl => l_dummy_var
2443 ,p_rt_end_dt => l_dummy_date
2444 ,p_rt_end_dt_cd => p_rt_end_dt_cd
2445 ,p_rt_end_dt_rl => l_dummy_var
2446 ,p_acty_base_rt_id => p_acty_base_rt_id
2447 ,p_effective_date => p_effective_date);
2448 --
2449 end get_rate_codes;
2450 --
2451 Function Determine_change_in_flex
2452 (p_prtt_enrt_rslt_id number,
2453 p_per_in_ler_id number,
2454 p_effective_date date)
2455 return boolean is
2456 --
2457 cursor c_prvdd_val is
2458 select bpl.acty_base_rt_id,
2459 bpl.bnft_prvdr_pool_id,
2460 bpl.prvdd_val,
2461 bpl.business_group_id,
2462 pil.person_id
2463 from ben_bnft_prvdd_ldgr_f bpl,
2464 ben_per_in_ler pil
2465 where bpl.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2466 and bpl.prvdd_val is not null
2467 and bpl.effective_end_date = hr_api.g_eot
2468 and bpl.per_in_ler_id = pil.per_in_ler_id
2469 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
2470 --
2471 cursor c_new_cr_val (p_acty_base_rt_id number,
2472 p_bnft_prvdr_pool_id number) is
2473 select ecr.val,
2474 ecr.elig_per_elctbl_chc_id
2475 from ben_enrt_rt ecr
2476 where ecr.acty_base_rt_id = p_acty_base_rt_id
2477 and ecr.elig_per_elctbl_chc_id in
2478 (select
2479 epe.elig_per_elctbl_chc_id
2480 from ben_elig_per_elctbl_chc epe, ben_BNFT_PRVDR_POOL_f bpl,
2481 ben_acty_base_rt_f abr
2482 where epe.BNFT_PRVDR_POOL_ID = p_bnft_prvdr_pool_id
2483 and epe.bnft_prvdr_pool_id = bpl.bnft_prvdr_pool_id
2484 and p_effective_date between bpl.effective_start_date and
2485 bpl.effective_end_date
2486 and epe.per_in_ler_id = p_per_in_ler_id
2487 and abr.acty_base_rt_id = p_acty_base_rt_id
2488 and (abr.pgm_id = epe.pgm_id or
2489 abr.pl_id = epe.pl_id or
2490 abr.plip_id = epe.plip_id or
2491 abr.ptip_id = epe.ptip_id or
2492 abr.OIPLIP_ID = epe.OIPLIP_ID or
2493 abr.CMBN_PTIP_ID = epe.CMBN_PTIP_ID or
2494 abr.CMBN_PTIP_OPT_ID = epe.CMBN_PTIP_OPT_ID or
2495 abr.CMBN_PLIP_ID = epe.CMBN_PLIP_ID)
2496 and p_effective_date between abr.effective_start_date and
2497 abr.effective_end_date);
2498 --
2499 l_new_cr_val c_new_cr_val%rowtype;
2500 l_prvdd_val c_prvdd_val%rowtype;
2501 l_return boolean := false;
2502 --
2503 Begin
2504 --
2505 open c_prvdd_val;
2506 loop
2507 fetch c_prvdd_val into l_prvdd_val;
2508 if c_prvdd_val%notfound then
2509 exit;
2510 end if;
2511 --
2512 hr_utility.set_location ('Provided Val'||l_prvdd_val.prvdd_val,10);
2513 hr_utility.set_location ('Acty base Rt'||l_prvdd_val.acty_base_rt_id,11);
2514 open c_new_cr_val (p_acty_base_rt_id =>l_prvdd_val.acty_base_rt_id,
2515 p_bnft_prvdr_pool_id =>l_prvdd_val.bnft_prvdr_pool_id);
2516 loop
2517 fetch c_new_cr_val into l_new_cr_val;
2518 if c_new_cr_val%found then
2519 --
2520 if l_prvdd_val.prvdd_val <> l_new_cr_val.val then
2521 l_return := true;
2522 --
2523 ben_provider_pools.create_credit_ledger_entry
2524 ( p_person_id => l_prvdd_val.person_id ,
2525 p_elig_per_elctbl_chc_id => l_new_cr_val.elig_per_elctbl_chc_id ,
2526 p_per_in_ler_id => p_per_in_ler_id,
2527 p_business_group_id => l_prvdd_val.business_group_id ,
2528 p_bnft_prvdr_pool_id => l_prvdd_val.bnft_prvdr_pool_id,
2529 p_enrt_mthd_cd => 'E',
2530 p_effective_date => p_effective_date );
2531 end if;
2532 else
2533 exit ;
2534 end if;
2535 end loop;
2536 close c_new_cr_val;
2537 end loop;
2538 close c_prvdd_val;
2539 return l_return;
2540
2541 End Determine_change_in_flex;
2542
2543 end ben_determine_rate_chg;