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