[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_RATES
Source
1 PACKAGE BODY ben_determine_rates AS
2 /* $Header: benrates.pkb 120.6.12010000.3 2008/08/05 14:52:17 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Determine Rates.
12 Purpose:
13 This process determines rates for either elctable choices or coverages, and
14 writes them to the ben_enrt_rt table. This process can only run in benmngle.
15 History:
16 Date Who Version What?
17 ---- --- ------- -----
18 7 May 98 Ty Hayden 110.0 Created.
19 16 Jun 98 T Guy 110.1 Deleted others exception
20 18 Jun 98 Ty Hayden 110.2 Added p_rt_usg_cd
21 p_bnft_prvdr_pool_id
22 p_prtt_rt_val_id
23 08 jul 98 Pbodla 110.3 Added a local variable
24 l_elig_per_elctbl_chc_id1 to
25 avoid making
26 l_epe.elig_per_elctbl_chc_id null
27 08 jul 98 jcarpent 110.4 Added PLANFC and PLANIMP
28 10 aug 98 tguy 110.5 Changed inner looping cursor into
29 seperate cursors for performance
30 reasons. Put inner loop logic
31 into procedure ben_rates.This is
32 called by associated cursor
33 loop, (ie. plan, ptip, plip, pgm,
34 or oipl).
35 11 Aug 98 Ty Hayden 110.6 Added local variables l_pl_id etc.
36 11 Aug 98 Ty Hayden 110.7 Fix to local variable placement.
37 07 Oct 98 T Guy 115.2 Implemented schema changes for
38 ben_enrt_rt. Added location
39 debugging calls.Added message
40 numbers.
41 18 Oct 98 T Guy 115.3 Added annual values and cmcd
42 values
43 20 Oct 98 T Guy 115.4 p_actl_prem_id,
44 p_cvg_amt_cal_mthd_id
45 p_bnft_rt_typ_cd, p_rt_typ_cd
46 p_rt_mlt_cd, p_comp_lvl_fctr_id
47 p_entr_ann_val_flag
48 p_ptd_comp_lvl_fctr_id
49 p_ann_dflt_val
50 p_rt_start_dt, p_rt_start_dt_cd
51 p_rt_start_dt_rl
52 23 Oct 98 T Guy 115.5 added person_id to main
53 02 Nov 98 T Guy 115.6 fixed asn_on_enrt_flag
54 assignment for ben_enrt_rt
55 02 Dec 98 T Guy 115.7 fixed mx_ann_elcn_val variable
56 assignment error.
57 20 Dec 98 T Guy 115.8 Added in caching information
58 for the created enrollment rate.
59 11 Jan 99 T Guy 115.9 added elctbl_chc_id as passed in parm.
60 added edit to check if elctbl_chc_id
61 was passed if so use it, if not then
62 use the one from electbl_chc
63 18 Jan 99 G Perry 115.10 LED V ED
64 30 Jan 99 S Das 115.11 Added codes for comp objects.
65 04 Mar 99 T Guy 115.12 Fixed dflt_flag and ctfn_rqd_flag
66 09 Mar 99 G Perry 115.14 IS to AS.
67 05 Apr 99 mhoyes 115.15 - Un-datetrack of per_in_ler_f changes.
68 - Removed DT restriction from
69 - main/c_epe
70 30 Apr 99 lmcdonal 115.16 Add per_in_ler status restriction.
71 08 May 99 G Perry 115.17 Changed PLIP Cursor so it checks
72 for PLIP first and if there are
73 no PLIP's of type 'STD' then
74 it switches to PL or if there are
75 no PLIP rates. LM speced change.
76 18 Jun 99 maagrawa 115.18 Override Oipl level rates, when
77 oiplip rates are available.
78 Override Plan rates when plip
79 rates are present.
80 20-JUL-99 Gperry 115.19 genutils -> benutils package
81 rename.
82 22-JUL-99 mhoyes 115.20 - Added new trace messages
83 07-SEP-99 shdas 115.21 Added codes for cmbn ptip opt .
84 02-Oct-99 lmcdonal 115.22 was loading annual-min into annual-
85 max field. fixed it.
86 15-Nov-99 mhoyes 115.23 - Added trace messages.
87 09-Mar-00 lmcdonal 115.24 Add handling of new comp-lvl-cds
88 for flex credit choices. Restructured
89 main.
90 21-Mar-00 mhoyes 115.25 - Fixed batch rate info problem caused
91 by not setting g_rec.person_id before
92 calling ben_rates from the sub
93 electable choice loop.
94 30-Mar-00 mmogel 115.26 - Added tokens to messages to more
95 clearly identify the source of the
96 problem from the message text
97 03-May-00 mhoyes 115.27 - Tuned c_epe. Removed
98 nvl on p_elig_per_elctbl_chc_id and
99 request_id restriction.
100 12-May-00 mhoyes 115.28 - Added profiling trace messages.
101 15-May-00 mhoyes 115.29 - Called performance API.
102 29-May-00 mhoyes 115.30 - Added EPE context record.
103 - Passed around record structures.
104 31-May-00 mhoyes 115.31 - Removed nvls from SQL.
105 28-Jun-00 mhoyes 115.32 - Moved eligibility cursor outside of
106 abr loop.
107 - Referenced elig per cache rather than
108 sql.
109 03-Aug-00 mhoyes 115.34 - Bulk bind of ben_enrt_rt.
110 08-Sep-00 kmahendr 115.35 - added more attributes to cursors-plan not in
111 programs - www#1186195- Variable rate profiles
112 overriden
113 22-Sep-00 mhoyes 115.36 - Added calls clear down ATP and PTA function
114 caches.
115 07-Nov-00 mhoyes 115.37 - Added electable choice context global.
116 05-Jan-01 kmahendr 115.38 - Added parameter per_in_ler_id for unrestricted
117 02-Aug-01 ikasire 115.39 Bug1895846 added exclusion condition for
118 suspended enrollment results
119 02-Aug-01 ikasre 115.40 added modification history for 115.39
120 28-Aug-01 kmahendr 115.41 bug#1936976-for coverage ranze code
121 prtt_rt_val_id is populated based on
122 benefit amount
123 19-dec-01 pbodla 115.42 CWB Changes - ben_old_retes only
124 looks at non comp per in ler's
125 20-dec-01 ikasire 115.43 added dbdrv lines
126 22-feb-02 pbodla 115.44 Bug 2234582 : Some times null
127 second parameter is giving error.
128 Explicit second parameter is passed
129 to hr_utility. set_location
130 15-may-02 ikasire 115.45 Bug 2200139 added a new parameter
131 p_elig_per_elctbl_chc_id to the main
132 procedure to called from override
133 process.For other processes it is
134 always passed as null .
135 23-May-02 kmahendr 115.46 Added a parameter to ben_determine_acty_base_rt
136 10-jun-02 pabodla 115.47 LGE : Create rate certifications
137 28-Sep-02 ikasire 115.49 reverted the changes made in 115.48
138 11-Oct-02 vsethi 115.50 Rates Sequence no enhancements. Modified to cater
139 to new column ord_num on ben_acty_base_rt_f
140 28-Oct-02 kmahendr 115.51 Land O Lakes performance fix - cursor c_enrt_rt
141 split into two.
142 26-Dec-02 rpillay 115.52 NOCOPY changes
143 13-feb-02 vsethi 115.53 Enclosed all hr_utility debug calls inside if
144 13-feb-02 kmahendr 115.54 Added a parameter to call -acty_base_rt.main
145 15-Apr-02 tjesumic 115.55 # 2897152 When the Std. Rate Calc Method code
146 is No Standard Values Used' NSVU and all vapro
147 attached to the rate are failed then no rate
148 will be created for the std rate.
149 This is fixed by deleting all the enrt_rt for
150 the above condition
151 08-May-03 ikasire 115.56 Option Level rates enhancements.
152 16-Sep-03 kmahendr 115.57 GSP changes
153 17-Dec-03 vvprabhu 115.58 Added the assignment for g_debug at the start of
154 each public procedure
155 14-Jan-03 bmanyam 115.59 Bug: 3234092: GSP Changes. Enabling
156 calculation of 'Salary Caclculation Rule'
157 instead of Activity Rates, in GSP mode.
158 14-Jan-03 pbodla 115.60,61 GLOBALCWB : Added code to populate
159 06-Feb-04 pbodla 115.62 GLOBALCWB : Added code to populate
160 WS_RT_START_DAT in ben_cwb_person_rates
161 25-Feb-04 pbodla 115.63 GLOBALCWB : Even if rates are not
162 defined still populate the cwb
163 tables - person_groups and
164 person_rates.
165 14-Apr-04 mmudigon 115.64 FONM changes
166 21-May-04 pbodla 115.65 FONM changes continued
167 mmudigon
168 24-May-04 nhunur 115.66 3633345 - changed cursor c_enrt_ctfn_rt.
169 16-Jun-04 mmudigon 115.68 same as version 115.66. Incorrect
170 arcs in version 115.67
171 03-sep-04 nhunur 115.69 3274902 : Changed code to populate cmcd_dflt_val
172 03-sep-04 nhunur 115.70 3234092 : Added code to get rt_strt_dt for GSP
173 17-sep-04 nhunur 115.71 iRec : c_per_in_ler modified not to look
174 at gsp/irec/abs/comp type of events.
175 15-Nov-04 kmahendr 115.73 Unrest. enh changes
176 18-Jan-05 kmahendr 115.74 Bug#4126093 - added check before creating
177 enrollment rate certificate
178 10-Mar-05 abparekh 115.75 Bug 4230502 : Populate L_ENRT_RT_ID_TAB
179 with created / updated ECR PK Ids
180 30-mar-05 nhunur 115.76 GSP change to ensure ff contexts are correctly passed.
181 26-Jul-05 nhunur 115.77 assign the FONM CVG date before calling rate calcaultion
182 08-Nov-05 pbodla 115.78 Bug 4693040 : When a rate is defined as nsvu
183 in cwb mode no need to delete the enrt_rt row
184 as it is not created at all, also rate
185 certifications not relevant for CWB rates.
186 25-Jan-05 swjain 115.79 Absences Enhancement: Added order by abr_seq_num
187 in all the abr cursors in procedure main
188 21-feb-06 pbodla 115.80 CWB : added multi currency support code.
189 24-feb-06 pbodla 115.81 CWB : fixed data type for
190 l_currency_cd
191 21-Apr-06 ikasired 115.82 CWB : 5148387 handling for benefit assignment
192 22-Feb-08 rtagarra 115.83 Bug 6840074
193 17-Jun-08 sagnanas 116.84 Bug 7154229
194 */
195 ----------------------------------------------------------------------------------------------
196 g_package VARCHAR2(80) := 'ben_determine_rates';
197 g_rec benutils.g_batch_rate_rec;
198 --
199 g_debug boolean := hr_utility.debug_enabled;
200 --
201 TYPE g_acty_base_rt_id_table IS TABLE OF ben_acty_base_rt_f.acty_base_rt_id%TYPE
202 INDEX BY BINARY_INTEGER;
203 TYPE g_asn_on_enrt_flag_table IS TABLE OF ben_acty_base_rt_f.asn_on_enrt_flag%TYPE
204 INDEX BY BINARY_INTEGER;
205 --
206 -- ----------------------------------------------------------------------------
207 -- |------< ben_rates_old >------|
208 -- ----------------------------------------------------------------------------
209 --
210 -- Description
211 -- This procedure is used to get the old rate/coverage amount for batch information
212 --
213 -- Pre Conditions
214 -- None.
215 --
216 -- In Parameters
217 -- p_person_id Person's primary key value
218 -- p_elig_per_elctble_chc_id
219 -- p_enrt_bnft_id
220 -- p_acty_base_rt_id
221 --
222 -- Out Parameters
223 -- p_old_val To assign old rate/coverage amount
224 --
225 -- Post Success
226 -- Processing continues
227 --
228 -- Post Failure
229 -- Error handled by procedure
230 --
231 -- Access Status
232 -- Internal use only.
233 --
234 PROCEDURE ben_rates_old(
235 p_elig_per_elctbl_chc_id IN NUMBER,
236 p_enrt_bnft_id IN NUMBER,
237 p_acty_base_rt_id IN NUMBER,
238 p_effective_date IN DATE,
239 p_person_id IN NUMBER,
240 p_lf_evt_ocrd_dt IN DATE,
241 p_pgm_id IN NUMBER,
242 p_pl_id IN NUMBER,
243 p_oipl_id IN NUMBER,
244 p_old_val OUT NOCOPY NUMBER) IS
245 --
246 CURSOR c_rate(
247 l_elctbl_chc_id NUMBER) IS
248 SELECT b.val
249 FROM ben_enrt_rt b
250 WHERE b.acty_base_rt_id = p_acty_base_rt_id
251 AND b.elig_per_elctbl_chc_id = l_elctbl_chc_id;
252 CURSOR c_elig_per_elctbl_chc(
253 l_per_in_ler NUMBER) IS
254 SELECT b.elig_per_elctbl_chc_id
255 FROM ben_elig_per_elctbl_chc b
256 WHERE b.per_in_ler_id = l_per_in_ler
257 AND ( b.pgm_id = p_pgm_id
258 OR pgm_id IS NULL)
259 AND ( b.pl_id = p_pl_id
260 OR pl_id IS NULL)
261 AND ( b.oipl_id = p_oipl_id
262 OR p_oipl_id IS NULL);
263 l_val ben_batch_rate_info.old_val%TYPE := 0;
264 --
265 -- CWB Chnages and GSP changes:
266 --
267 CURSOR c_per_in_ler IS
268 SELECT pil.per_in_ler_id
269 FROM ben_per_in_ler pil,
270 ben_ler_f ler
271 WHERE pil.person_id = p_person_id
272 and pil.ler_id = ler.ler_id
273 and ler.typ_cd not in ('COMP','GSP', 'IREC', 'ABS')
274 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
275 AND lf_evt_ocrd_dt =
276 (SELECT MAX(b.lf_evt_ocrd_dt)
277 FROM ben_per_in_ler b,
278 ben_ler_f ler1
279 WHERE b.person_id = p_person_id
280 and b.ler_id = ler1.ler_id
281 and b.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
282 and ler1.typ_cd not in ('COMP','GSP', 'IREC', 'ABS')
283 AND b.lf_evt_ocrd_dt < p_lf_evt_ocrd_dt);
284 --
285 l_per_in_ler NUMBER;
286 l_elctbl_chc_id NUMBER;
287 BEGIN
288 --
289 if g_debug then
290 hr_utility.set_location('Inside procedure Old Rate ', 0000);
291 hr_utility.set_location('elig per elctbl chc id ' || p_elig_per_elctbl_chc_id,0000);
292 hr_utility.set_location('acty base rate id ' || p_acty_base_rt_id, 0000);
293 end if;
294 OPEN c_per_in_ler;
295 FETCH c_per_in_ler INTO l_per_in_ler;
296 CLOSE c_per_in_ler;
297 IF l_per_in_ler IS NOT NULL THEN
298 OPEN c_elig_per_elctbl_chc(l_per_in_ler);
299 FETCH c_elig_per_elctbl_chc INTO l_elctbl_chc_id;
300 CLOSE c_elig_per_elctbl_chc;
301 END IF;
302 IF l_elctbl_chc_id IS NOT NULL THEN
303 OPEN c_rate(l_elctbl_chc_id);
304 FETCH c_rate INTO l_val;
305 CLOSE c_rate;
306 END IF;
307 IF l_val > 0 THEN
308 p_old_val := l_val;
309 END IF;
310 END ben_rates_old;
311 --
312 PROCEDURE ben_rates(
313 p_currepe_row IN ben_epe_cache.g_pilepe_inst_row,
314 p_per_row IN per_all_people_f%ROWTYPE,
315 p_asg_row IN per_all_assignments_f%ROWTYPE,
316 p_ast_row IN per_assignment_status_types%ROWTYPE,
317 p_adr_row IN per_addresses%ROWTYPE,
318 p_person_id IN NUMBER,
319 p_pgm_id IN NUMBER,
320 p_pl_id IN NUMBER,
321 p_oipl_id IN NUMBER,
322 p_elig_per_elctbl_chc_id IN NUMBER,
323 p_enrt_bnft_id IN NUMBER,
324 p_acty_base_rt_id_table IN Out nocopy ben_determine_rates.g_acty_base_rt_id_table,
325 p_asn_on_enrt_flag_table IN ben_determine_rates.g_asn_on_enrt_flag_table,
326 p_effective_date IN DATE,
327 p_lf_evt_ocrd_dt IN DATE,
328 p_perform_rounding_flg IN BOOLEAN,
329 p_business_group_id IN NUMBER,
330 p_dflt_flag IN VARCHAR2,
331 p_ctfn_rqd_flag IN VARCHAR2,
332 p_mode in varchar2) IS
333 --
334 l_package VARCHAR2(80) := g_package ||
335 '.ben_rates';
336 --
337 l_currepe_row ben_determine_rates.g_curr_epe_rec;
338 --
339 l_elig_per_elctbl_chc_id1 NUMBER;
340 --
341 l_created_by ben_enrt_rt.created_by%TYPE;
342 l_creation_date ben_enrt_rt.creation_date%TYPE;
343 l_last_update_date ben_enrt_rt.last_update_date%TYPE;
344 l_last_updated_by ben_enrt_rt.last_updated_by%TYPE;
345 l_last_update_login ben_enrt_rt.last_update_login%TYPE;
346 l_request_id ben_enrt_rt.request_id%TYPE
347 := fnd_global.conc_request_id;
348 l_program_application_id ben_enrt_rt.program_application_id%TYPE
349 := fnd_global.prog_appl_id;
350 l_program_id ben_enrt_rt.program_id%TYPE
351 := fnd_global.conc_program_id;
352 l_program_update_date ben_enrt_rt.program_update_date%TYPE
353 := SYSDATE;
354 l_object_version_number ben_enrt_rt.object_version_number%TYPE := 1;
355 l_no VARCHAR2(1) := 'N';
356 -- the null variables are used to ensure the INSERT statement contains bind
357 -- parameters and increases shareability
358 l_varchar2_null VARCHAR2(1); -- automatically set to NULL
359 l_number_null NUMBER(1); -- automatically set to NULL
360 --
361 TYPE l_number_15_table_type IS TABLE OF NUMBER(15)
362 INDEX BY BINARY_INTEGER;
363 TYPE l_number_15_2_table_type IS TABLE OF NUMBER(15, 2)
364 INDEX BY BINARY_INTEGER;
365 TYPE l_number_table_type IS TABLE OF NUMBER
366 INDEX BY BINARY_INTEGER;
367 TYPE l_varchar2_30_table_type IS TABLE OF VARCHAR2(30)
368 INDEX BY BINARY_INTEGER;
369 TYPE l_date_table_type IS TABLE OF DATE
370 INDEX BY BINARY_INTEGER;
371 --
372 cursor c_prtt_enrt_rslt (c_prtt_enrt_rslt_id number) is
373 select null
374 from ben_prtt_enrt_rslt_f pen,
375 ben_enrt_bnft enb
376 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
377 and pen.bnft_ordr_num = enb.ordr_num
378 and enb.enrt_bnft_id = p_enrt_bnft_id
379 and pen.prtt_enrt_rslt_stat_cd is null
380 and p_effective_date between
381 pen.effective_start_date and pen.effective_end_date;
382 --
383 l_val l_number_table_type;
384 l_mn_elcn_val l_number_table_type;
385 l_mx_elcn_val l_number_table_type;
386 l_ann_val l_number_table_type;
387 l_ann_mn_elcn_val l_number_table_type;
388 l_ann_mx_elcn_val l_number_table_type;
389 l_cmcd_val l_number_table_type;
390 l_cmcd_mn_elcn_val l_number_table_type;
391 l_cmcd_mx_elcn_val l_number_table_type;
392 l_cmcd_acty_ref_perd_cd l_varchar2_30_table_type;
393 l_incrmt_elcn_val l_number_table_type;
394 l_dflt_val l_number_table_type;
395 l_tx_typ_cd l_varchar2_30_table_type;
396 l_acty_typ_cd l_varchar2_30_table_type;
397 l_nnmntry_uom l_varchar2_30_table_type;
398 l_entr_val_at_enrt_flag l_varchar2_30_table_type;
399 l_dsply_on_enrt_flag l_varchar2_30_table_type;
400 l_use_to_calc_net_flx_cr_flag l_varchar2_30_table_type;
401 l_rt_usg_cd l_varchar2_30_table_type;
402 l_decr_bnft_prvdr_pool_id l_number_15_table_type;
403 l_actl_prem_id l_number_15_table_type;
404 l_cvg_amt_calc_mthd_id l_number_15_table_type;
405 l_bnft_rt_typ_cd l_varchar2_30_table_type;
406 l_rt_typ_cd l_varchar2_30_table_type;
407 l_rt_mlt_cd l_varchar2_30_table_type;
408 l_comp_lvl_fctr_id l_number_15_table_type;
409 l_entr_ann_val_flag l_varchar2_30_table_type;
410 l_ptd_comp_lvl_fctr_id l_number_15_table_type;
411 l_clm_comp_lvl_fctr_id l_number_15_table_type;
412 l_ann_dflt_val l_number_15_2_table_type;
413 l_rt_strt_dt l_date_table_type;
414 l_rt_strt_dt_cd l_varchar2_30_table_type;
415 l_rt_strt_dt_rl l_number_15_table_type;
416 l_prtt_rt_val_id l_number_15_table_type;
417 l_dsply_mn_elcn_val l_number_15_2_table_type;
418 l_dsply_mx_elcn_val l_number_15_2_table_type;
419 l_pp_in_yr_used_num l_number_15_table_type;
420 l_ordr_num l_number_15_table_type;
421 l_iss_val l_number_table_type;
422 l_enrt_rt_id_tab l_number_15_table_type; -- 2897152
423
424 --
425 l_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
426 l_enrt_rt_ctfn_id ben_enrt_rt_ctfn.enrt_rt_ctfn_id%type;
427 l_old_val NUMBER;
428 l_dummy varchar2(1);
429 --
430 cursor c_enrt_rt_id is
431 select ben_enrt_rt_s.nextval
432 from sys.dual;
433 --
434 /* cursor c_enrt_ctfn(p_acty_base_rt_id number,
435 p_elig_per_elctbl_chc_id number) is
436 select ecr.enrt_rt_id,
437 abc.enrt_ctfn_typ_cd,
438 abc.rqd_flag
439 from ben_enrt_rt ecr,
440 ben_enrt_bnft enb,
441 BEN_ACTY_BASE_RT_CTFN_F abc
442 where ecr.acty_base_rt_id = p_acty_base_rt_id
443 and ecr.business_group_id = p_business_group_id
444 and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
445 enb.elig_per_elctbl_chc_id) =
446 p_elig_per_elctbl_chc_id
447 and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
448 and abc.acty_base_rt_id = ecr.acty_base_rt_id;
449 */
450 cursor c_enrt_ctfn_rt(p_acty_base_rt_id number,
451 p_elig_per_elctbl_chc_id number) is
452 select ecr.enrt_rt_id,
453 abc.enrt_ctfn_typ_cd,
454 abc.rqd_flag
455 from ben_enrt_rt ecr,
456 BEN_ACTY_BASE_RT_CTFN_F abc
457 where ecr.acty_base_rt_id = p_acty_base_rt_id
458 and ecr.business_group_id = p_business_group_id
459 and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
460 and abc.acty_base_rt_id = ecr.acty_base_rt_id+0;
461 --
462 --
463 cursor c_enrt_ctfn_bnft(p_acty_base_rt_id number,
464 p_elig_per_elctbl_chc_id number) is
465 select ecr.enrt_rt_id,
466 abc.enrt_ctfn_typ_cd,
467 abc.rqd_flag
468 from ben_enrt_rt ecr,
469 ben_enrt_bnft enb,
470 BEN_ACTY_BASE_RT_CTFN_F abc
471 where ecr.acty_base_rt_id = p_acty_base_rt_id
472 and ecr.business_group_id = p_business_group_id
473 and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
474 and enb.enrt_bnft_id = ecr.enrt_bnft_id
475 and abc.acty_base_rt_id = ecr.acty_base_rt_id;
476
477 --
478 cursor c_enrt_ctfn_exists (p_enrt_rt_id number,
479 p_enrt_ctfn_typ_cd varchar2) is
480 select null
481 from ben_enrt_rt_ctfn erc
482 where erc.enrt_rt_id = p_enrt_rt_id
483 and erc.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd;
484 --
485 -- 2897152
486 TYPE l_numlist is table of number(2) index by BINARY_INTEGER ;
487 l_nsuv_lst l_numlist ;
488 l_nsuv_num integer ;
489 -- Bug: 3234092 Changes begin **********************************************
490 cursor c_pgm is
491 SELECT pgm.pgm_typ_cd, pgm.salary_calc_mthd_cd, pgm.salary_calc_mthd_rl
492 FROM ben_pgm_f pgm
493 where pgm.pgm_id = p_pgm_id
494 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
495
496
497 cursor c_abr(p_acty_base_rt_id NUMBER) is
498 select abr.acty_typ_cd,
499 abr.tx_typ_cd,
500 abr.dsply_on_enrt_flag,
501 abr.use_to_calc_net_flx_cr_flag,
502 abr.entr_val_at_enrt_flag,
503 abr.entr_ann_val_flag,
504 abr.currency_det_cd,
505 abr.element_det_rl,
506 abr.element_type_id
507 from ben_acty_base_rt_f abr
508 where abr.acty_base_rt_id = p_acty_base_rt_id
509 and p_effective_date between abr.effective_start_date and abr.effective_end_date;
510
511 l_env ben_env_object.g_global_env_rec_type;
512 l_mode l_env.mode_cd%TYPE;
513 l_pgm_rec c_pgm%ROWTYPE;
514 l_abr c_abr%ROWTYPE;
515 l_eval_sal_rule boolean := FALSE;
516 l_rate_outputs ff_exec.outputs_t;
517 l_jurisdiction VARCHAR2(30);
518 -- l_assignment_id NUMBER;
519 -- l_organization_id NUMBER;
520 -- Bug: 3234092 Changes end **********************************************
521 l_cmcd_dflt_val number ;
522 l_currency_det_cd varchar2(30);
523 l_element_det_rl number;
524 l_base_element_type_id number;
525 l_cwb_acty_base_rt_id number;
526 --
527 l_asg_benass_row per_all_assignments_f%ROWTYPE;
528 --
529 BEGIN
530 --
531 l_currepe_row.elig_per_elctbl_chc_id := p_currepe_row.elig_per_elctbl_chc_id;
532 l_currepe_row.business_group_id := p_currepe_row.business_group_id;
533 l_currepe_row.person_id := p_currepe_row.person_id;
534 l_currepe_row.ler_id := p_currepe_row.ler_id;
535 l_currepe_row.per_in_ler_id := p_currepe_row.per_in_ler_id;
536 l_currepe_row.pgm_id := p_currepe_row.pgm_id;
537 l_currepe_row.pl_typ_id := p_currepe_row.pl_typ_id;
538 l_currepe_row.ptip_id := p_currepe_row.ptip_id;
539 l_currepe_row.plip_id := p_currepe_row.plip_id;
540 l_currepe_row.pl_id := p_currepe_row.pl_id;
541 l_currepe_row.oipl_id := p_currepe_row.oipl_id;
542 l_currepe_row.oiplip_id := p_currepe_row.oiplip_id;
543 l_currepe_row.opt_id := p_currepe_row.opt_id;
544 l_currepe_row.enrt_perd_id := p_currepe_row.enrt_perd_id;
545 l_currepe_row.lee_rsn_id := p_currepe_row.lee_rsn_id;
546 l_currepe_row.enrt_perd_strt_dt := p_currepe_row.enrt_perd_strt_dt;
547 l_currepe_row.prtt_enrt_rslt_id := p_currepe_row.prtt_enrt_rslt_id;
548 l_currepe_row.prtn_strt_dt := p_currepe_row.prtn_strt_dt;
549 l_currepe_row.enrt_cvg_strt_dt := p_currepe_row.enrt_cvg_strt_dt;
550 l_currepe_row.enrt_cvg_strt_dt_cd := p_currepe_row.enrt_cvg_strt_dt_cd;
551 l_currepe_row.enrt_cvg_strt_dt_rl := p_currepe_row.enrt_cvg_strt_dt_rl;
552 l_currepe_row.yr_perd_id := p_currepe_row.yr_perd_id;
553 l_currepe_row.prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
554 l_currepe_row.prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
555 l_currepe_row.rt_age_val := p_currepe_row.rt_age_val;
556 l_currepe_row.rt_los_val := p_currepe_row.rt_los_val;
557 l_currepe_row.rt_hrs_wkd_val := p_currepe_row.rt_hrs_wkd_val;
558 l_currepe_row.rt_cmbn_age_n_los_val := p_currepe_row.rt_cmbn_age_n_los_val;
559 --
560 l_nsuv_num := 1 ;
561 --
562 if nvl(p_mode, 'X') = 'W' then
563 --
564 ben_manage_cwb_life_events.g_cwb_person_groups_rec := ben_manage_cwb_life_events.g_cwb_person_groups_rec_temp;
565 ben_manage_cwb_life_events.g_cwb_person_rates_rec := ben_manage_cwb_life_events.g_cwb_person_rates_rec_temp;
566 --
567 end if;
568 --
569 FOR i IN p_acty_base_rt_id_table.FIRST .. p_acty_base_rt_id_table.LAST LOOP
570 if g_debug then
571 hr_utility.set_location('Entering ' || l_package, 10);
572 end if;
573 --
574
575 -- Bug: 3234092 Changes begin **********************************************
576 l_eval_sal_rule := false;
577 ben_env_object.get(p_rec => l_env);
578 --
579 if g_debug then
580 hr_utility.set_location(' ben_env_object: Mode ' || l_env.mode_cd, 10);
581 end if;
582 --
583 -- If mode='G' (gsp) and pgm_typ_cd for the pgm-record is 'GSP'
584 -- and formula is defined at pgm-level
585 -- then evaluate the formula instead of rates
586 -- variable l_eval_sal_rule tells whether to evaluate rule (or the rates).
587 if ((l_env.mode_cd IS NOT NULL) AND (l_env.mode_cd = 'G')) then
588 open c_pgm;
589 fetch c_pgm INTO l_pgm_rec;
590 --
591 if g_debug then
592 hr_utility.set_location('l_pgm_rec.pgm_typ_cd ' || l_pgm_rec.pgm_typ_cd, 20);
593 hr_utility.set_location('l_pgm_rec.salary_calc_mthd_cd ' || l_pgm_rec.salary_calc_mthd_cd, 20);
594 hr_utility.set_location('l_pgm_rec.salary_calc_mthd_rl ' || l_pgm_rec.salary_calc_mthd_rl, 20);
595 end if;
596 --
597 if (c_pgm%FOUND) THEN
598 if (l_pgm_rec.pgm_typ_cd = 'GSP' and l_pgm_rec.salary_calc_mthd_cd = 'RULE'
599 and l_pgm_rec.salary_calc_mthd_rl IS NOT NULL) then
600 l_eval_sal_rule := true;
601 end if;
602 end if;
603 close c_pgm;
604 end if;
605 --
606 if (l_eval_sal_rule) THEN
607
608 ben_determine_activity_base_rt.main(
609 p_currepe_row => l_currepe_row,
610 p_per_row => p_per_row,
611 p_asg_row => p_asg_row,
612 p_ast_row => p_ast_row,
613 p_adr_row => p_adr_row,
614 p_person_id => p_person_id,
615 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
616 p_enrt_bnft_id => p_enrt_bnft_id,
617 p_acty_base_rt_id => p_acty_base_rt_id_table(i),
618 p_effective_date => p_effective_date,
619 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
620 p_perform_rounding_flg => TRUE,
621 p_val => l_val(i),
622 p_mn_elcn_val => l_mn_elcn_val(i),
623 p_mx_elcn_val => l_mx_elcn_val(i),
624 p_ann_val => l_ann_val(i),
625 p_ann_mn_elcn_val => l_ann_mn_elcn_val(i),
626 p_ann_mx_elcn_val => l_ann_mx_elcn_val(i),
627 p_cmcd_val => l_cmcd_val(i),
628 p_cmcd_mn_elcn_val => l_cmcd_mn_elcn_val(i),
629 p_cmcd_mx_elcn_val => l_cmcd_mx_elcn_val(i),
630 p_cmcd_acty_ref_perd_cd => l_cmcd_acty_ref_perd_cd(i),
631 p_incrmt_elcn_val => l_incrmt_elcn_val(i),
632 p_dflt_val => l_dflt_val(i),
633 p_tx_typ_cd => l_tx_typ_cd(i),
634 p_acty_typ_cd => l_acty_typ_cd(i),
635 p_nnmntry_uom => l_nnmntry_uom(i),
636 p_entr_val_at_enrt_flag => l_entr_val_at_enrt_flag(i),
637 p_dsply_on_enrt_flag => l_dsply_on_enrt_flag(i),
638 p_use_to_calc_net_flx_cr_flag => l_use_to_calc_net_flx_cr_flag(i),
639 p_rt_usg_cd => l_rt_usg_cd(i),
640 p_bnft_prvdr_pool_id => l_decr_bnft_prvdr_pool_id(i),
641 p_actl_prem_id => l_actl_prem_id(i),
642 p_cvg_calc_amt_mthd_id => l_cvg_amt_calc_mthd_id(i),
643 p_bnft_rt_typ_cd => l_bnft_rt_typ_cd(i),
644 p_rt_typ_cd => l_rt_typ_cd(i),
645 p_rt_mlt_cd => l_rt_mlt_cd(i),
646 p_comp_lvl_fctr_id => l_comp_lvl_fctr_id(i),
647 p_entr_ann_val_flag => l_entr_ann_val_flag(i),
648 p_ptd_comp_lvl_fctr_id => l_ptd_comp_lvl_fctr_id(i),
649 p_clm_comp_lvl_fctr_id => l_clm_comp_lvl_fctr_id(i),
650 p_ann_dflt_val => l_ann_dflt_val(i),
651 p_rt_strt_dt => l_rt_strt_dt(i),
652 p_rt_strt_dt_cd => l_rt_strt_dt_cd(i),
653 p_rt_strt_dt_rl => l_rt_strt_dt_rl(i),
654 p_prtt_rt_val_id => l_prtt_rt_val_id(i),
655 p_dsply_mn_elcn_val => l_dsply_mn_elcn_val(i),
656 p_dsply_mx_elcn_val => l_dsply_mx_elcn_val(i),
657 p_pp_in_yr_used_num => l_pp_in_yr_used_num(i),
658 p_ordr_num => l_ordr_num(i),
659 p_iss_val => l_iss_val(i));
660
661 -- GSP issue raised by hallmark cards 30/3/05
662 -- contexts are passed using in parameters now instead of local variable
663 --
664 hr_utility.set_location(' p_asg_row.assignment_id ' || p_asg_row.assignment_id, 20);
665 hr_utility.set_location(' p_asg_row.organization_id ' || p_asg_row.organization_id, 20);
666 --
667 l_rate_outputs := benutils.formula
668 (p_formula_id => l_pgm_rec.salary_calc_mthd_rl,
669 p_effective_date => p_effective_date,
670 p_assignment_id => p_asg_row.assignment_id, -- l_assignment_id,
671 p_organization_id => p_asg_row.organization_id, -- l_organization_id,
672 p_business_group_id => p_business_group_id,
673 p_pgm_id => p_pgm_id,
674 p_pl_id => p_pl_id,
675 p_pl_typ_id => l_currepe_row.pl_typ_id,
676 p_opt_id => l_currepe_row.opt_id,
677 p_ler_id => l_currepe_row.ler_id,
678 p_acty_base_rt_id => p_acty_base_rt_id_table(i),
679 p_elig_per_elctbl_chc_id => l_currepe_row.elig_per_elctbl_chc_id,
680 p_jurisdiction_code => l_jurisdiction);
681 --
682 l_val(i) := l_rate_outputs(l_rate_outputs.first).value;
683 --
684 if g_debug then
685 hr_utility.set_location(' val(i) from Formula ' || l_val(i), 20);
686 end if;
687 --
688 -- set all other values to NULL
689 l_mn_elcn_val(i) := NULL;
690 l_mx_elcn_val(i) := NULL;
691 l_ann_val(i) := NULL;
692 l_ann_mn_elcn_val(i) := NULL;
693 l_ann_mx_elcn_val(i) := NULL;
694 l_cmcd_val(i) := NULL;
695 l_cmcd_mn_elcn_val(i) := NULL;
696 l_cmcd_mx_elcn_val(i) := NULL;
697 l_cmcd_acty_ref_perd_cd(i) := NULL;
698 l_incrmt_elcn_val(i) := NULL;
699 l_dflt_val(i) := NULL;
700 l_tx_typ_cd(i) := NULL;
701 l_acty_typ_cd(i) := NULL;
702 l_nnmntry_uom(i) := NULL;
703 l_entr_val_at_enrt_flag(i) := NULL;
704 l_dsply_on_enrt_flag(i) := NULL;
705 l_use_to_calc_net_flx_cr_flag(i) := NULL;
706 l_rt_usg_cd(i) := NULL;
707 l_decr_bnft_prvdr_pool_id(i) := NULL;
708 l_actl_prem_id(i) := NULL;
709 l_cvg_amt_calc_mthd_id(i) := NULL;
710 l_bnft_rt_typ_cd(i) := NULL;
711 l_rt_typ_cd(i) := NULL;
712 l_rt_mlt_cd(i) := NULL;
713 l_comp_lvl_fctr_id(i) := NULL;
714 l_entr_ann_val_flag(i) := NULL;
715 l_ptd_comp_lvl_fctr_id(i) := NULL;
716 l_clm_comp_lvl_fctr_id(i) := NULL;
717 l_ann_dflt_val(i) := NULL;
718 -- GSP needs this date
719 -- l_rt_strt_dt(i) := NULL;
720 -- l_rt_strt_dt_cd(i) := NULL;
721 -- l_rt_strt_dt_rl(i) := NULL;
722 l_prtt_rt_val_id(i) := NULL;
723 l_dsply_mn_elcn_val(i) := NULL;
724 l_dsply_mx_elcn_val(i) := NULL;
725 l_pp_in_yr_used_num(i) := NULL;
726 l_ordr_num(i) := NULL;
727 l_iss_val(i) := NULL;
728 --
729 -- Fetch acty_typ_cd and other not-null values
730 -- from ben_acty_base_rt_f table.
731 open c_abr(p_acty_base_rt_id_table(i));
732 fetch c_abr into l_abr;
733 if c_abr%FOUND then
734 l_acty_typ_cd(i) := l_abr.acty_typ_cd;
735 l_tx_typ_cd(i) := l_abr.tx_typ_cd;
736 l_dsply_on_enrt_flag(i) := l_abr.dsply_on_enrt_flag;
737 l_use_to_calc_net_flx_cr_flag(i) := l_abr.use_to_calc_net_flx_cr_flag;
738 l_entr_val_at_enrt_flag(i) := l_abr.entr_val_at_enrt_flag;
739 l_entr_ann_val_flag(i) := l_abr.entr_ann_val_flag;
740 end if;
741 close c_abr;
742 --
743 if g_debug then
744 hr_utility.set_location(' l_acty_typ_cd ' || l_acty_typ_cd(i), 20);
745 end if;
746 ELSE
747 -- In all other cases evaluate rates, the usual way
748 if g_debug then
749 hr_utility.set_location(' calculate rate the usual way ' , 20);
750 end if;
751 --
752 -- Bug: 3234092 Changes end **********************************************
753 ben_determine_activity_base_rt.main(
754 p_currepe_row => l_currepe_row,
755 p_per_row => p_per_row,
756 p_asg_row => p_asg_row,
757 p_ast_row => p_ast_row,
758 p_adr_row => p_adr_row,
759 p_person_id => p_person_id,
760 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
761 p_enrt_bnft_id => p_enrt_bnft_id,
762 p_acty_base_rt_id => p_acty_base_rt_id_table(i),
763 p_effective_date => p_effective_date,
764 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
765 p_perform_rounding_flg => TRUE,
766 p_val => l_val(i),
767 p_mn_elcn_val => l_mn_elcn_val(i),
768 p_mx_elcn_val => l_mx_elcn_val(i),
769 p_ann_val => l_ann_val(i),
770 p_ann_mn_elcn_val => l_ann_mn_elcn_val(i),
771 p_ann_mx_elcn_val => l_ann_mx_elcn_val(i),
772 p_cmcd_val => l_cmcd_val(i),
773 p_cmcd_mn_elcn_val => l_cmcd_mn_elcn_val(i),
774 p_cmcd_mx_elcn_val => l_cmcd_mx_elcn_val(i),
775 p_cmcd_acty_ref_perd_cd => l_cmcd_acty_ref_perd_cd(i),
776 p_incrmt_elcn_val => l_incrmt_elcn_val(i),
777 p_dflt_val => l_dflt_val(i),
778 p_tx_typ_cd => l_tx_typ_cd(i),
779 p_acty_typ_cd => l_acty_typ_cd(i),
780 p_nnmntry_uom => l_nnmntry_uom(i),
781 p_entr_val_at_enrt_flag => l_entr_val_at_enrt_flag(i),
782 p_dsply_on_enrt_flag => l_dsply_on_enrt_flag(i),
783 p_use_to_calc_net_flx_cr_flag => l_use_to_calc_net_flx_cr_flag(i),
784 p_rt_usg_cd => l_rt_usg_cd(i),
785 p_bnft_prvdr_pool_id => l_decr_bnft_prvdr_pool_id(i),
786 p_actl_prem_id => l_actl_prem_id(i),
787 p_cvg_calc_amt_mthd_id => l_cvg_amt_calc_mthd_id(i),
788 p_bnft_rt_typ_cd => l_bnft_rt_typ_cd(i),
789 p_rt_typ_cd => l_rt_typ_cd(i),
790 p_rt_mlt_cd => l_rt_mlt_cd(i),
791 p_comp_lvl_fctr_id => l_comp_lvl_fctr_id(i),
792 p_entr_ann_val_flag => l_entr_ann_val_flag(i),
793 p_ptd_comp_lvl_fctr_id => l_ptd_comp_lvl_fctr_id(i),
794 p_clm_comp_lvl_fctr_id => l_clm_comp_lvl_fctr_id(i),
795 p_ann_dflt_val => l_ann_dflt_val(i),
796 p_rt_strt_dt => l_rt_strt_dt(i),
797 p_rt_strt_dt_cd => l_rt_strt_dt_cd(i),
798 p_rt_strt_dt_rl => l_rt_strt_dt_rl(i),
799 p_prtt_rt_val_id => l_prtt_rt_val_id(i),
800 p_dsply_mn_elcn_val => l_dsply_mn_elcn_val(i),
801 p_dsply_mx_elcn_val => l_dsply_mx_elcn_val(i),
802 p_pp_in_yr_used_num => l_pp_in_yr_used_num(i),
803 p_ordr_num => l_ordr_num(i),
804 p_iss_val => l_iss_val(i));
805 END IF;
806 --
807 -- As p_iss_val is only used by CWB and no other mode, we have overloaded it to return
808 -- cmcd_dflt_val for which we need not add a new out param in actbr and change all dependencies.
809 --
810 if nvl(p_mode, 'X') = 'W' then
811 l_cmcd_dflt_val := null ;
812 else
813 l_cmcd_dflt_val := l_iss_val(i) ;
814 l_iss_val(i) := null ;
815 end if;
816 --
817 -- GLOBALCWB : If plan is group plan and mode is CWB then populate
818 -- CWB rate structures.
819 --
820 if nvl(p_mode, 'X') = 'W' then
821 --
822 if l_acty_typ_cd(i) = 'CWBDB' then -- Distribution Budget values.
823 --
824 if l_currepe_row.pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
825 --
826 -- Populate dist_bdgt_val, dist_bdgt_mn_val, dist_bdgt_mx_val,
827 -- dist_bdgt_incr_val, dist_bdgt_iss_val, dist_bdgt_iss_date,
828 --
829 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_val :=
830 nvl(l_val(i), l_dflt_val(i));
831 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_mn_val := l_mn_elcn_val(i);
832 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_mx_val := l_mx_elcn_val(i);
833 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_incr_val := l_incrmt_elcn_val(i);
834 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_iss_val := l_iss_val(i);
835 ben_manage_cwb_life_events.g_cwb_person_groups_rec.dist_bdgt_iss_date := null; -- 9999 What is this val
836 else
837 --
838 -- Populate dist_bdgt_val, dist_bdgt_mn_val, dist_bdgt_mx_val,
839 -- dist_bdgt_incr_val, dist_bdgt_iss_val, dist_bdgt_iss_date,
840 --
841 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_val := nvl(l_val(i), l_dflt_val(i));
842 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_mn_val := l_mn_elcn_val(i);
843 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_mx_val := l_mx_elcn_val(i);
844 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_incr_val := l_incrmt_elcn_val(i);
845 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_iss_val := l_iss_val(i);
846 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_dist_bdgt_iss_date := null; -- 9999 What is this val
847 end if;
848 --
849 elsif l_acty_typ_cd(i) = 'CWBWB' then -- Worksheet Budget values.
850 --
851 if l_currepe_row.pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
852 --
853 -- Populate ws_bdgt_val, ws_bdgt_mn_val, ws_bdgt_mx_val,
854 -- ws_bdgt_incr_val, ws_bdgt_iss_val, ws_bdgt_iss_date,
855 --
856 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_val :=
857 nvl(l_val(i), l_dflt_val(i));
858 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_mn_val := l_mn_elcn_val(i);
859 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_mx_val := l_mx_elcn_val(i);
860 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_incr_val := l_incrmt_elcn_val(i);
861 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_iss_val := l_iss_val(i);
862 ben_manage_cwb_life_events.g_cwb_person_groups_rec.ws_bdgt_iss_date := null; -- 9999 What is this val
863 --
864 else
865 --
866 -- Populate ws_bdgt_val, ws_bdgt_mn_val, ws_bdgt_mx_val,
867 -- ws_bdgt_incr_val, ws_bdgt_iss_val, ws_bdgt_iss_date,
868 --
869 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_val :=
870 nvl(l_val(i), l_dflt_val(i));
871 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_mn_val := l_mn_elcn_val(i);
872 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_mx_val := l_mx_elcn_val(i);
873 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_incr_val := l_incrmt_elcn_val(i);
874 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_iss_val := l_iss_val(i);
875 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_ws_bdgt_iss_date := null; -- 9999 What is this val
876 --
877 end if;
878 --
879 elsif l_acty_typ_cd(i) = 'CWBR' then -- Reserve Budget values.
880 --
881 if l_currepe_row.pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
882 --
883 -- Populate rsrv_val, rsrv_mn_val, rsrv_mx_val, rsrv_incr_val
884 --
885 ben_manage_cwb_life_events.g_cwb_person_groups_rec.rsrv_val :=
886 nvl(l_val(i), l_dflt_val(i));
887 ben_manage_cwb_life_events.g_cwb_person_groups_rec.rsrv_mn_val := l_mn_elcn_val(i);
888 ben_manage_cwb_life_events.g_cwb_person_groups_rec.rsrv_mx_val := l_mx_elcn_val(i);
889 ben_manage_cwb_life_events.g_cwb_person_groups_rec.rsrv_incr_val := l_incrmt_elcn_val(i);
890 --
891 else
892 --
893 -- Populate rsrv_val, rsrv_mn_val, rsrv_mx_val, rsrv_incr_val
894 --
895 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_rsrv_val :=
896 nvl(l_val(i), l_dflt_val(i));
897 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_rsrv_mn_val := l_mn_elcn_val(i);
898 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_rsrv_mx_val := l_mx_elcn_val(i);
899 ben_manage_cwb_life_events.g_cwb_person_rates_rec.copy_rsrv_incr_val := l_incrmt_elcn_val(i);
900 --
901 end if;
902 --
903 elsif l_acty_typ_cd(i) = 'CWBWS' then -- Worksheet values.
904 --
905 -- Populate
906 -- ws_val
907 -- ws_mn_val
908 -- ws_mx_val
909 -- ws_incr_val
910 --
911 ben_manage_cwb_life_events.g_cwb_person_rates_rec.WS_RT_START_DATE := l_rt_strt_dt(i);
912 ben_manage_cwb_life_events.g_cwb_person_rates_rec.ws_val :=
913 nvl(l_val(i), l_dflt_val(i));
914 ben_manage_cwb_life_events.g_cwb_person_rates_rec.ws_mn_val := l_mn_elcn_val(i);
915 ben_manage_cwb_life_events.g_cwb_person_rates_rec.ws_mx_val := l_mx_elcn_val(i);
916 ben_manage_cwb_life_events.g_cwb_person_rates_rec.ws_incr_val := l_incrmt_elcn_val(i);
917 --
918 -- Multi currency support. Add caching mechanism
919 --
920 open c_abr(p_acty_base_rt_id_table(i));
921 fetch c_abr into l_abr;
922 close c_abr;
923 l_currency_det_cd := l_abr.currency_det_cd;
924 l_element_det_rl := l_abr.element_det_rl;
925 l_base_element_type_id := l_abr.element_type_id;
926 l_cwb_acty_base_rt_id := p_acty_base_rt_id_table(i);
927 --
928 elsif l_acty_typ_cd(i) = 'CWBES' then -- Worksheet values.
929 --
930 -- elig_sal_val
931 --
932 ben_manage_cwb_life_events.g_cwb_person_rates_rec.elig_sal_val := l_val(i);
933 --
934 elsif l_acty_typ_cd(i) = 'CWBSS' then -- Worksheet values.
935 -- stat_sal_val
936 --
937 ben_manage_cwb_life_events.g_cwb_person_rates_rec.stat_sal_val := l_val(i);
938 --
939 elsif l_acty_typ_cd(i) = 'CWBOS' then -- Worksheet values.
940 --
941 -- oth_comp_val
942 --
943 ben_manage_cwb_life_events.g_cwb_person_rates_rec.oth_comp_val := l_val(i);
944 --
945 elsif l_acty_typ_cd(i) = 'CWBTC' then -- Worksheet values.
946 --
947 -- tot_comp_val
948 --
949 ben_manage_cwb_life_events.g_cwb_person_rates_rec.tot_comp_val := l_val(i);
950 --
951 elsif l_acty_typ_cd(i) = 'CWBMR1' then -- Worksheet values.
952 --
953 -- misc1_val
954 --
955 ben_manage_cwb_life_events.g_cwb_person_rates_rec.misc1_val := l_val(i);
956 --
957 elsif l_acty_typ_cd(i) = 'CWBMR2' then -- Worksheet values.
958 --
959 -- misc2_val
960 --
961 ben_manage_cwb_life_events.g_cwb_person_rates_rec.misc2_val := l_val(i);
962 --
963 elsif l_acty_typ_cd(i) = 'CWBMR3' then -- Worksheet values.
964 --
965 -- misc3_val
966 --
967 ben_manage_cwb_life_events.g_cwb_person_rates_rec.misc3_val := l_val(i);
968 --
969 elsif l_acty_typ_cd(i) = 'CWBRA' then -- Worksheet values.
970 --
971 -- rec_val number,
972 -- rec_mn_val number,
973 -- rec_mx_val number,
974 -- rec_incr_val
975 --
976 ben_manage_cwb_life_events.g_cwb_person_rates_rec.rec_val :=
977 nvl(l_val(i), l_dflt_val(i)) ;
978 ben_manage_cwb_life_events.g_cwb_person_rates_rec.rec_mn_val := l_mn_elcn_val(i);
979 ben_manage_cwb_life_events.g_cwb_person_rates_rec.rec_mx_val := l_mx_elcn_val(i);
980 ben_manage_cwb_life_events.g_cwb_person_rates_rec.rec_incr_val := l_incrmt_elcn_val(i);
981 --
982 end if;
983 --
984 end if;
985 if nvl(p_mode, 'X') not in ('I', 'W', 'G') then -- No need call for CWB
986 --
987 -- call procedure ben_rates_old to get old value
988 --
989 if g_debug then
990 hr_utility.set_location('ben_rates_old ' || l_package, 10);
991 end if;
992 ben_rates_old(
993 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
994 p_enrt_bnft_id => p_enrt_bnft_id,
995 p_acty_base_rt_id => p_acty_base_rt_id_table(i),
996 p_effective_date => p_effective_date,
997 p_person_id => p_person_id,
998 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
999 p_pgm_id => p_pgm_id,
1000 p_pl_id => p_pl_id,
1001 p_oipl_id => p_oipl_id,
1002 p_old_val => l_old_val);
1003 if g_debug then
1004 hr_utility.set_location('Dn ben_rates_old ' || l_package, 10);
1005 end if;
1006 --
1007 -- Bug 2234582 : Some times null second parameter is giving error.
1008 -- explicit second parameter is passed to hr_utility. set_location
1009 --
1010 if g_debug then
1011 hr_utility.set_location('Old Rate ' || l_old_val, 11);
1012 end if;
1013 --
1014 g_rec.person_id := p_person_id;
1015 g_rec.pgm_id := p_pgm_id;
1016 g_rec.pl_id := p_pl_id;
1017 g_rec.oipl_id := p_oipl_id;
1018 g_rec.dflt_flag := l_no;
1019 g_rec.business_group_id := p_business_group_id;
1020 g_rec.effective_date := p_effective_date;
1021 --
1022 g_rec.bnft_rt_typ_cd := l_bnft_rt_typ_cd(i);
1023 g_rec.val := l_val(i);
1024 g_rec.old_val := l_old_val;
1025 g_rec.tx_typ_cd := l_tx_typ_cd(i);
1026 g_rec.acty_typ_cd := l_acty_typ_cd(i);
1027 g_rec.mn_elcn_val := l_mn_elcn_val(i);
1028 g_rec.mx_elcn_val := l_mx_elcn_val(i);
1029 g_rec.incrmt_elcn_val := l_incrmt_elcn_val(i);
1030 g_rec.dflt_val := l_dflt_val(i);
1031 g_rec.rt_strt_dt := l_rt_strt_dt(i);
1032
1033 benutils.write(p_rec => g_rec);
1034
1035 end if;
1036 -- 2897152 if the dt mlt cd is NSVU and vapro fail
1037 --- null value add to a pl/sql table so this can be
1038 --- delted in latter
1039 if g_debug then
1040 hr_utility.set_location('mlt_cd ' || l_rt_mlt_cd(i), 10);
1041 hr_utility.set_location('value ' || l_val(i), 10);
1042 end if ;
1043
1044 if l_rt_mlt_cd(i)= 'NSVU' and l_val(i) is null then
1045 l_nsuv_lst(l_nsuv_num) := i ;
1046 l_nsuv_num := l_nsuv_num + 1 ;
1047 end if ;
1048
1049 --
1050 if g_debug then
1051 hr_utility.set_location('After write', 000);
1052 end if;
1053
1054 END LOOP;
1055
1056 --
1057 -- GLOBALCWB : Populate the CWB tables.
1058 --
1059 if nvl(p_mode, 'X') = 'W' then
1060 --
1061 If p_asg_row.assignment_id IS NULL THEN
1062 --BUG 5148387 Need to get Benefit Assignment
1063 ben_person_object.get_benass_object(
1064 p_person_id => p_person_id,
1065 p_rec => l_asg_benass_row
1066 );
1067 --
1068 END IF;
1069 -- Handle auto issue of budgets.
1070 -- For multi currency support added new paramaters.
1071 --
1072 ben_manage_cwb_life_events.populate_cwb_rates(
1073 --
1074 -- Columns needed for ben_cwb_person_rates
1075 --
1076 p_person_id => p_person_id
1077 ,p_assignment_id => NVL(p_asg_row.assignment_id,l_asg_benass_row.assignment_id)
1078 ,p_organization_id => NVL(p_asg_row.organization_id,l_asg_benass_row.organization_id)
1079 ,p_pl_id => l_currepe_row.pl_id
1080 ,p_oipl_id => l_currepe_row.oipl_id
1081 ,p_opt_id => l_currepe_row.opt_id
1082 ,p_ler_id => l_currepe_row.ler_id
1083 ,p_business_group_id=> p_business_group_id
1084 ,p_acty_base_rt_id => l_cwb_acty_base_rt_id
1085 ,p_elig_flag => null -- 9999 it should come from g_curr_epe_rec
1086 ,p_inelig_rsn_cd => null -- 9999 it should come from g_curr_epe_rec
1087 --
1088 -- Columns needed by BEN_CWB_PERSON_GROUPS
1089 --
1090 ,p_due_dt => null
1091 ,p_access_cd => null -- passed inside the poppulate_cwb_rates
1092 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1093 -- Multi currency support
1094 ,p_currency_det_cd => l_currency_det_cd
1095 ,p_element_det_rl => l_element_det_rl
1096 ,p_base_element_type_id => l_base_element_type_id
1097 );
1098 --
1099 end if;
1100 --
1101 if g_debug then
1102 hr_utility.set_location('l_nsuv_num ' || l_nsuv_num, 10);
1103 hr_utility.set_location('Done BDABRT_MN ' || l_package, 10);
1104 end if;
1105
1106 --
1107 -- Since enrt_bnft_id and elig_per_elctbl_chc_id are
1108 -- mutually exclusive
1109 -- in ben_enrt_rt then nullify chc_id when bnft_id is not null;
1110 --
1111
1112 IF p_enrt_bnft_id IS NOT NULL THEN
1113 l_elig_per_elctbl_chc_id1 := NULL;
1114 open c_prtt_enrt_rslt(l_currepe_row.prtt_enrt_rslt_id);
1115 fetch c_prtt_enrt_rslt into l_dummy;
1116 if c_prtt_enrt_rslt%notfound then
1117 FOR i IN l_prtt_rt_val_id.FIRST .. l_prtt_rt_val_id.LAST
1118 loop
1119 l_prtt_rt_val_id(i) := null;
1120 end loop;
1121 end if;
1122 close c_prtt_enrt_rslt;
1123 ELSE
1124 l_elig_per_elctbl_chc_id1 := p_elig_per_elctbl_chc_id;
1125 END IF;
1126
1127 --
1128 if g_debug then
1129 hr_utility.set_location('Insert ECR: ' || l_package, 5);
1130 end if;
1131
1132 --
1133 -- GLOBALCWB : No need to populate the ben_enrt_rt table.
1134 --
1135
1136 if nvl(p_mode, 'X') <> 'W' then
1137 --
1138 if p_mode in ('U','R') then
1139 --
1140 FOR i IN p_acty_base_rt_id_table.FIRST .. p_acty_base_rt_id_table.LAST loop
1141 l_enrt_rt_id := null;
1142 l_enrt_rt_id := ben_manage_unres_life_events.ecr_exists
1143 (l_elig_per_elctbl_chc_id1,
1144 p_enrt_bnft_id,
1145 p_acty_base_rt_id_table(i));
1146 if l_enrt_rt_id is not null then
1147 --
1148 l_enrt_rt_id_tab(i) := l_enrt_rt_id; /* Bug 4230502 */
1149 --
1150 ben_manage_unres_life_events.update_enrt_rt
1151 ( p_enrt_rt_id => l_enrt_rt_id,
1152 p_acty_typ_cd => l_acty_typ_cd(i),
1153 p_tx_typ_cd => l_tx_typ_cd(i),
1154 p_ctfn_rqd_flag => p_ctfn_rqd_flag,
1155 p_dflt_flag => p_dflt_flag,
1156 p_dflt_pndg_ctfn_flag => l_no,
1157 p_dsply_on_enrt_flag => l_dsply_on_enrt_flag(i),
1158 p_use_to_calc_net_flx_cr_flag => l_use_to_calc_net_flx_cr_flag(i),
1159 p_entr_val_at_enrt_flag => l_entr_val_at_enrt_flag(i),
1160 p_asn_on_enrt_flag => p_asn_on_enrt_flag_table(i),
1161 p_rl_crs_only_flag => l_no,
1162 p_dflt_val => l_dflt_val(i),
1163 p_ann_val => l_ann_val(i),
1164 p_ann_mn_elcn_val => l_ann_mn_elcn_val(i),
1165 p_ann_mx_elcn_val => l_ann_mx_elcn_val(i),
1166 p_val => l_val(i),
1167 p_nnmntry_uom => l_nnmntry_uom(i),
1168 p_mx_elcn_val => l_mx_elcn_val(i),
1169 p_mn_elcn_val => l_mn_elcn_val(i),
1170 p_incrmt_elcn_val => l_incrmt_elcn_val(i),
1171 p_cmcd_acty_ref_perd_cd => l_cmcd_acty_ref_perd_cd(i),
1172 p_cmcd_mn_elcn_val => l_cmcd_mn_elcn_val(i),
1173 p_cmcd_mx_elcn_val => l_cmcd_mx_elcn_val(i),
1174 p_cmcd_val => l_cmcd_val(i),
1175 p_cmcd_dflt_val => l_cmcd_val(i), --7154229
1176 p_rt_usg_cd => l_rt_usg_cd(i),
1177 p_ann_dflt_val => l_ann_dflt_val(i),
1178 p_bnft_rt_typ_cd => l_bnft_rt_typ_cd(i),
1179 p_rt_mlt_cd => l_rt_mlt_cd(i),
1180 p_dsply_mn_elcn_val => l_dsply_mn_elcn_val(i),
1181 p_dsply_mx_elcn_val => l_dsply_mx_elcn_val(i),
1182 p_entr_ann_val_flag => l_entr_ann_val_flag(i),
1183 p_rt_strt_dt => l_rt_strt_dt(i),
1184 p_rt_strt_dt_cd => l_rt_strt_dt_cd(i),
1185 p_rt_strt_dt_rl => l_rt_strt_dt_rl(i),
1186 p_rt_typ_cd => l_rt_typ_cd(i),
1187 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id1,
1188 p_acty_base_rt_id => p_acty_base_rt_id_table(i),
1189 p_spcl_rt_enrt_rt_id => l_number_null,
1190 p_enrt_bnft_id => p_enrt_bnft_id,
1191 p_prtt_rt_val_id => l_prtt_rt_val_id(i),
1192 p_decr_bnft_prvdr_pool_id => l_decr_bnft_prvdr_pool_id(i),
1193 p_cvg_amt_calc_mthd_id => l_cvg_amt_calc_mthd_id(i),
1194 p_actl_prem_id => l_actl_prem_id(i),
1195 p_comp_lvl_fctr_id => l_comp_lvl_fctr_id(i),
1196 p_ptd_comp_lvl_fctr_id => l_ptd_comp_lvl_fctr_id(i),
1197 p_clm_comp_lvl_fctr_id => l_clm_comp_lvl_fctr_id(i),
1198 p_business_group_id => p_business_group_id,
1199 p_request_id => l_request_id,
1200 p_program_application_id => l_program_application_id,
1201 p_program_id =>l_program_id,
1202 p_program_update_date => l_program_update_date ,
1203 p_effective_date => p_effective_date,
1204 p_pp_in_yr_used_num => l_pp_in_yr_used_num(i),
1205 p_ordr_num => l_ordr_num(i),
1206 p_iss_val => l_iss_val(i))
1207 ;
1208 --
1209 else
1210 --
1211 INSERT INTO ben_enrt_rt
1212 (
1213 enrt_rt_id,
1214 acty_typ_cd,
1215 tx_typ_cd,
1216 ctfn_rqd_flag,
1217 dflt_flag,
1218 dflt_pndg_ctfn_flag,
1219 dsply_on_enrt_flag,
1220 use_to_calc_net_flx_cr_flag,
1221 entr_val_at_enrt_flag,
1222 asn_on_enrt_flag,
1223 rl_crs_only_flag,
1224 dflt_val,
1225 ann_val,
1226 ann_mn_elcn_val,
1227 ann_mx_elcn_val,
1228 val,
1229 nnmntry_uom,
1230 mx_elcn_val,
1231 mn_elcn_val,
1232 incrmt_elcn_val,
1233 cmcd_acty_ref_perd_cd,
1234 cmcd_mn_elcn_val,
1235 cmcd_mx_elcn_val,
1236 cmcd_val,
1237 cmcd_dflt_val,
1238 rt_usg_cd,
1239 ann_dflt_val,
1240 bnft_rt_typ_cd,
1241 rt_mlt_cd,
1242 dsply_mn_elcn_val,
1243 dsply_mx_elcn_val,
1244 entr_ann_val_flag,
1245 rt_strt_dt,
1246 rt_strt_dt_cd,
1247 rt_strt_dt_rl,
1248 rt_typ_cd,
1249 elig_per_elctbl_chc_id,
1250 acty_base_rt_id,
1251 spcl_rt_enrt_rt_id,
1252 enrt_bnft_id,
1253 prtt_rt_val_id,
1254 decr_bnft_prvdr_pool_id,
1255 cvg_amt_calc_mthd_id,
1256 actl_prem_id,
1257 comp_lvl_fctr_id,
1258 ptd_comp_lvl_fctr_id,
1259 clm_comp_lvl_fctr_id,
1260 business_group_id,
1261 ecr_attribute_category,
1262 ecr_attribute1,
1263 ecr_attribute2,
1264 ecr_attribute3,
1265 ecr_attribute4,
1266 ecr_attribute5,
1267 ecr_attribute6,
1268 ecr_attribute7,
1269 ecr_attribute8,
1270 ecr_attribute9,
1271 ecr_attribute10,
1272 ecr_attribute11,
1273 ecr_attribute12,
1274 ecr_attribute13,
1275 ecr_attribute14,
1276 ecr_attribute15,
1277 ecr_attribute16,
1278 ecr_attribute17,
1279 ecr_attribute18,
1280 ecr_attribute19,
1281 ecr_attribute20,
1282 ecr_attribute21,
1283 ecr_attribute22,
1284 ecr_attribute23,
1285 ecr_attribute24,
1286 ecr_attribute25,
1287 ecr_attribute26,
1288 ecr_attribute27,
1289 ecr_attribute28,
1290 ecr_attribute29,
1291 ecr_attribute30,
1292 last_update_login,
1293 created_by,
1294 creation_date,
1295 last_updated_by,
1296 last_update_date,
1297 request_id,
1298 program_application_id,
1299 program_id,
1300 program_update_date,
1301 object_version_number,
1302 pp_in_yr_used_num,
1303 ordr_num,
1304 iss_val)
1305 VALUES(
1306 ben_enrt_rt_s.nextval,
1307 l_acty_typ_cd(i),
1308 l_tx_typ_cd(i),
1309 p_ctfn_rqd_flag,
1310 p_dflt_flag,
1311 l_no,
1312 l_dsply_on_enrt_flag(i),
1313 l_use_to_calc_net_flx_cr_flag(i),
1314 l_entr_val_at_enrt_flag(i),
1315 p_asn_on_enrt_flag_table(i),
1316 l_no,
1317 l_dflt_val(i),
1318 l_ann_val(i),
1319 l_ann_mn_elcn_val(i),
1320 l_ann_mx_elcn_val(i),
1321 l_val(i),
1322 l_nnmntry_uom(i),
1323 l_mx_elcn_val(i),
1324 l_mn_elcn_val(i),
1325 l_incrmt_elcn_val(i),
1326 l_cmcd_acty_ref_perd_cd(i),
1327 l_cmcd_mn_elcn_val(i),
1328 l_cmcd_mx_elcn_val(i),
1329 l_cmcd_val(i),
1330 l_cmcd_val(i) , --7154229
1331 l_rt_usg_cd(i),
1332 l_ann_dflt_val(i),
1333 l_bnft_rt_typ_cd(i),
1334 l_rt_mlt_cd(i),
1335 l_dsply_mn_elcn_val(i),
1336 l_dsply_mx_elcn_val(i),
1337 l_entr_ann_val_flag(i),
1338 l_rt_strt_dt(i),
1339 l_rt_strt_dt_cd(i),
1340 l_rt_strt_dt_rl(i),
1341 l_rt_typ_cd(i),
1342 l_elig_per_elctbl_chc_id1,
1343 p_acty_base_rt_id_table(i),
1344 l_number_null,
1345 p_enrt_bnft_id,
1346 l_prtt_rt_val_id(i),
1347 l_decr_bnft_prvdr_pool_id(i),
1348 l_cvg_amt_calc_mthd_id(i),
1349 l_actl_prem_id(i),
1350 l_comp_lvl_fctr_id(i),
1351 l_ptd_comp_lvl_fctr_id(i),
1352 l_clm_comp_lvl_fctr_id(i),
1353 p_business_group_id,
1354 l_varchar2_null,
1355 l_varchar2_null,
1356 l_varchar2_null,
1357 l_varchar2_null,
1358 l_varchar2_null,
1359 l_varchar2_null,
1360 l_varchar2_null,
1361 l_varchar2_null,
1362 l_varchar2_null,
1363 l_varchar2_null,
1364 l_varchar2_null,
1365 l_varchar2_null,
1366 l_varchar2_null,
1367 l_varchar2_null,
1368 l_varchar2_null,
1369 l_varchar2_null,
1370 l_varchar2_null,
1371 l_varchar2_null,
1372 l_varchar2_null,
1373 l_varchar2_null,
1374 l_varchar2_null,
1375 l_varchar2_null,
1376 l_varchar2_null,
1377 l_varchar2_null,
1378 l_varchar2_null,
1379 l_varchar2_null,
1380 l_varchar2_null,
1381 l_varchar2_null,
1382 l_varchar2_null,
1383 l_varchar2_null,
1384 l_varchar2_null,
1385 l_last_update_login,
1386 l_created_by,
1387 l_creation_date,
1388 l_last_updated_by,
1389 l_last_update_date,
1390 l_request_id,
1391 l_program_application_id,
1392 l_program_id,
1393 l_program_update_date,
1394 l_object_version_number,
1395 l_pp_in_yr_used_num(i),
1396 l_ordr_num(i),
1397 l_iss_val(i))
1398 returning enrt_rt_id into l_enrt_rt_id_tab(i) ; /* Bug 4230502 */
1399 --
1400 end if;
1401 --
1402 end loop;
1403 --
1404 else
1405 --
1406 FORALL i IN p_acty_base_rt_id_table.FIRST .. p_acty_base_rt_id_table.LAST
1407 INSERT INTO ben_enrt_rt
1408 (
1409 enrt_rt_id,
1410 acty_typ_cd,
1411 tx_typ_cd,
1412 ctfn_rqd_flag,
1413 dflt_flag,
1414 dflt_pndg_ctfn_flag,
1415 dsply_on_enrt_flag,
1416 use_to_calc_net_flx_cr_flag,
1417 entr_val_at_enrt_flag,
1418 asn_on_enrt_flag,
1419 rl_crs_only_flag,
1420 dflt_val,
1421 ann_val,
1422 ann_mn_elcn_val,
1423 ann_mx_elcn_val,
1424 val,
1425 nnmntry_uom,
1426 mx_elcn_val,
1427 mn_elcn_val,
1428 incrmt_elcn_val,
1429 cmcd_acty_ref_perd_cd,
1430 cmcd_mn_elcn_val,
1431 cmcd_mx_elcn_val,
1432 cmcd_val,
1433 cmcd_dflt_val,
1434 rt_usg_cd,
1435 ann_dflt_val,
1436 bnft_rt_typ_cd,
1437 rt_mlt_cd,
1438 dsply_mn_elcn_val,
1439 dsply_mx_elcn_val,
1440 entr_ann_val_flag,
1441 rt_strt_dt,
1442 rt_strt_dt_cd,
1443 rt_strt_dt_rl,
1444 rt_typ_cd,
1445 elig_per_elctbl_chc_id,
1446 acty_base_rt_id,
1447 spcl_rt_enrt_rt_id,
1448 enrt_bnft_id,
1449 prtt_rt_val_id,
1450 decr_bnft_prvdr_pool_id,
1451 cvg_amt_calc_mthd_id,
1452 actl_prem_id,
1453 comp_lvl_fctr_id,
1454 ptd_comp_lvl_fctr_id,
1455 clm_comp_lvl_fctr_id,
1456 business_group_id,
1457 ecr_attribute_category,
1458 ecr_attribute1,
1459 ecr_attribute2,
1460 ecr_attribute3,
1461 ecr_attribute4,
1462 ecr_attribute5,
1463 ecr_attribute6,
1464 ecr_attribute7,
1465 ecr_attribute8,
1466 ecr_attribute9,
1467 ecr_attribute10,
1468 ecr_attribute11,
1469 ecr_attribute12,
1470 ecr_attribute13,
1471 ecr_attribute14,
1472 ecr_attribute15,
1473 ecr_attribute16,
1474 ecr_attribute17,
1475 ecr_attribute18,
1476 ecr_attribute19,
1477 ecr_attribute20,
1478 ecr_attribute21,
1479 ecr_attribute22,
1480 ecr_attribute23,
1481 ecr_attribute24,
1482 ecr_attribute25,
1483 ecr_attribute26,
1484 ecr_attribute27,
1485 ecr_attribute28,
1486 ecr_attribute29,
1487 ecr_attribute30,
1488 last_update_login,
1489 created_by,
1490 creation_date,
1491 last_updated_by,
1492 last_update_date,
1493 request_id,
1494 program_application_id,
1495 program_id,
1496 program_update_date,
1497 object_version_number,
1498 pp_in_yr_used_num,
1499 ordr_num,
1500 iss_val)
1501 VALUES(
1502 ben_enrt_rt_s.nextval,
1503 l_acty_typ_cd(i),
1504 l_tx_typ_cd(i),
1505 p_ctfn_rqd_flag,
1506 p_dflt_flag,
1507 l_no,
1508 l_dsply_on_enrt_flag(i),
1509 l_use_to_calc_net_flx_cr_flag(i),
1510 l_entr_val_at_enrt_flag(i),
1511 p_asn_on_enrt_flag_table(i),
1512 l_no,
1513 l_dflt_val(i),
1514 l_ann_val(i),
1515 l_ann_mn_elcn_val(i),
1516 l_ann_mx_elcn_val(i),
1517 l_val(i),
1518 l_nnmntry_uom(i),
1519 l_mx_elcn_val(i),
1520 l_mn_elcn_val(i),
1521 l_incrmt_elcn_val(i),
1522 l_cmcd_acty_ref_perd_cd(i),
1523 l_cmcd_mn_elcn_val(i),
1524 l_cmcd_mx_elcn_val(i),
1525 l_cmcd_val(i),
1526 l_cmcd_val(i) , --7154229
1527 l_rt_usg_cd(i),
1528 l_ann_dflt_val(i),
1529 l_bnft_rt_typ_cd(i),
1530 l_rt_mlt_cd(i),
1531 l_dsply_mn_elcn_val(i),
1532 l_dsply_mx_elcn_val(i),
1533 l_entr_ann_val_flag(i),
1534 l_rt_strt_dt(i),
1535 l_rt_strt_dt_cd(i),
1536 l_rt_strt_dt_rl(i),
1537 l_rt_typ_cd(i),
1538 l_elig_per_elctbl_chc_id1,
1539 p_acty_base_rt_id_table(i),
1540 l_number_null,
1541 p_enrt_bnft_id,
1542 l_prtt_rt_val_id(i),
1543 l_decr_bnft_prvdr_pool_id(i),
1544 l_cvg_amt_calc_mthd_id(i),
1545 l_actl_prem_id(i),
1546 l_comp_lvl_fctr_id(i),
1547 l_ptd_comp_lvl_fctr_id(i),
1548 l_clm_comp_lvl_fctr_id(i),
1549 p_business_group_id,
1550 l_varchar2_null,
1551 l_varchar2_null,
1552 l_varchar2_null,
1553 l_varchar2_null,
1554 l_varchar2_null,
1555 l_varchar2_null,
1556 l_varchar2_null,
1557 l_varchar2_null,
1558 l_varchar2_null,
1559 l_varchar2_null,
1560 l_varchar2_null,
1561 l_varchar2_null,
1562 l_varchar2_null,
1563 l_varchar2_null,
1564 l_varchar2_null,
1565 l_varchar2_null,
1566 l_varchar2_null,
1567 l_varchar2_null,
1568 l_varchar2_null,
1569 l_varchar2_null,
1570 l_varchar2_null,
1571 l_varchar2_null,
1572 l_varchar2_null,
1573 l_varchar2_null,
1574 l_varchar2_null,
1575 l_varchar2_null,
1576 l_varchar2_null,
1577 l_varchar2_null,
1578 l_varchar2_null,
1579 l_varchar2_null,
1580 l_varchar2_null,
1581 l_last_update_login,
1582 l_created_by,
1583 l_creation_date,
1584 l_last_updated_by,
1585 l_last_update_date,
1586 l_request_id,
1587 l_program_application_id,
1588 l_program_id,
1589 l_program_update_date,
1590 l_object_version_number,
1591 l_pp_in_yr_used_num(i),
1592 l_ordr_num(i),
1593 l_iss_val(i))
1594 returning enrt_rt_id BULK COLLECT into l_enrt_rt_id_tab ;
1595 end if;
1596 --
1597 end if;
1598
1599 --
1600 if g_debug then
1601 hr_utility.set_location('after Insert ' , 5);
1602 end if;
1603
1604 -- 2897152 when the date mult code is NSVU and rate does not have any
1605 -- values then no rate will be created . so the
1606 -- rows are deleted here
1607 -- we could not find way to stop the insert without changing bulk insert
1608 -- for the above condition
1609 -- so the row are deleted after insert
1610
1611 -- Bug 4693040 : In CWB mode as no rates are written no need to delete data.
1612 -- Also rate certifications are not relevant for cwb mode.
1613 --
1614 if nvl(p_mode, 'X') <> 'W' then
1615 --
1616 if l_nsuv_lst.COUNT > 0 then
1617
1618 if g_debug then
1619 hr_utility.set_location(' COUNT ' || l_nsuv_lst.COUNT , 99 );
1620 hr_utility.set_location(' Deleting ' , 99 );
1621 end if;
1622 for i in l_nsuv_lst.FIRST .. l_nsuv_lst.LAST Loop
1623 l_nsuv_num := l_nsuv_lst(i) ;
1624 if p_acty_base_rt_id_table.EXISTS(l_nsuv_num)
1625 and l_rt_mlt_cd(l_nsuv_num) = 'NSVU'
1626 and l_val(l_nsuv_num) is null then
1627 if g_debug then
1628 hr_utility.set_location(' enrt rt_id '||l_enrt_rt_id_tab(l_nsuv_num) , 99 );
1629 end if ;
1630
1631 Delete from Ben_enrt_rt
1632 where val is null
1633 and rt_mlt_cd = l_rt_mlt_cd(l_nsuv_num)
1634 and acty_base_rt_id = p_acty_base_rt_id_table(l_nsuv_num)
1635 and business_group_id = p_business_group_id
1636 and enrt_rt_id = l_enrt_rt_id_tab(l_nsuv_num) ;
1637 end if ;
1638 End Loop ;
1639
1640 end if ;
1641 --
1642
1643 -- LGE : Now create the rate certifications.
1644 --
1645 FOR i IN p_acty_base_rt_id_table.FIRST .. p_acty_base_rt_id_table.LAST loop
1646 --
1647 if l_rt_mlt_cd(i)= 'NSVU' and l_val(i) is null then
1648 --- Do nothing
1649 null ;
1650 else
1651
1652 if p_enrt_bnft_id is not null then
1653 --
1654 for l_enrt_ctfn in c_enrt_ctfn_bnft(p_acty_base_rt_id_table(i),
1655 p_elig_per_elctbl_chc_id)
1656 loop
1657 -- bug#4126093
1658 open c_enrt_ctfn_exists (l_enrt_ctfn.enrt_rt_id,l_enrt_ctfn.enrt_ctfn_typ_cd);
1659 fetch c_enrt_ctfn_exists into l_dummy;
1660 if c_enrt_ctfn_exists%notfound then
1661 ben_enrt_rt_ctfn_api.create_enrt_rt_ctfn
1662 (p_validate => false
1663 ,p_enrt_rt_ctfn_id => l_enrt_rt_ctfn_id
1664 ,p_enrt_ctfn_typ_cd => l_enrt_ctfn.enrt_ctfn_typ_cd
1665 ,p_rqd_flag => l_enrt_ctfn.rqd_flag
1666 ,p_enrt_rt_id => l_enrt_ctfn.enrt_rt_id
1667 ,p_business_group_id => p_business_group_id
1668 ,p_request_id => l_request_id
1669 ,p_program_application_id => l_program_application_id
1670 ,p_program_id => l_program_id
1671 ,p_program_update_date => l_program_update_date
1672 ,p_object_version_number => l_object_version_number
1673 ,p_effective_date => p_effective_date
1674 ) ;
1675 end if;
1676 close c_enrt_ctfn_exists;
1677 --
1678 end loop;
1679 else
1680 --
1681 for l_enrt_ctfn in c_enrt_ctfn_rt(p_acty_base_rt_id_table(i),
1682 p_elig_per_elctbl_chc_id)
1683 loop
1684 --
1685 open c_enrt_ctfn_exists (l_enrt_ctfn.enrt_rt_id,l_enrt_ctfn.enrt_ctfn_typ_cd);
1686 fetch c_enrt_ctfn_exists into l_dummy;
1687 if c_enrt_ctfn_exists%notfound then
1688 ben_enrt_rt_ctfn_api.create_enrt_rt_ctfn
1689 (p_validate => false
1690 ,p_enrt_rt_ctfn_id => l_enrt_rt_ctfn_id
1691 ,p_enrt_ctfn_typ_cd => l_enrt_ctfn.enrt_ctfn_typ_cd
1692 ,p_rqd_flag => l_enrt_ctfn.rqd_flag
1693 ,p_enrt_rt_id => l_enrt_ctfn.enrt_rt_id
1694 ,p_business_group_id => p_business_group_id
1695 ,p_request_id => l_request_id
1696 ,p_program_application_id => l_program_application_id
1697 ,p_program_id => l_program_id
1698 ,p_program_update_date => l_program_update_date
1699 ,p_object_version_number => l_object_version_number
1700 ,p_effective_date => p_effective_date
1701 ) ;
1702 end if;
1703 close c_enrt_ctfn_exists;
1704 --
1705 end loop;
1706 --
1707 end if;
1708 end if ;
1709 End loop ;
1710 end if;
1711 --
1712 if g_debug then
1713 hr_utility.set_location('Leaving ' || l_package, 10);
1714 end if;
1715 --
1716 END ben_rates;
1717 --
1718 PROCEDURE main(
1719 p_effective_date IN DATE,
1720 p_lf_evt_ocrd_dt IN DATE,
1721 p_person_id IN NUMBER,
1722 -- added per_in_ler_id parameter for unrestricted enhancement
1723 p_per_in_ler_id in number,
1724 p_elig_per_elctbl_chc_id IN number default null ,
1725 p_mode in varchar2 default null) IS
1726 --
1727 l_package VARCHAR2(80) := g_package ||
1728 '.main';
1729 --
1730 l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
1731 l_epo_row ben_derive_part_and_rate_facts.g_cache_structure;
1732 --
1733 l_currepe_set ben_epe_cache.g_pilepe_inst_tbl;
1734 --
1735 l_currepe_row ben_epe_cache.g_pilepe_inst_row;
1736 --
1737 /*
1738 l_currepe_row ben_determine_rates.g_curr_epe_rec;
1739 */
1740 l_per_row per_all_people_f%ROWTYPE;
1741 l_asg_row per_all_assignments_f%ROWTYPE;
1742 l_ast_row per_assignment_status_types%ROWTYPE;
1743 l_adr_row per_addresses%ROWTYPE;
1744 --
1745 l_pl_id NUMBER;
1746 l_pgm_id NUMBER;
1747 l_oipl_id NUMBER;
1748 l_oiplip_id NUMBER;
1749 l_plip_id NUMBER;
1750 l_ptip_id NUMBER;
1751 l_cmbn_plip_id NUMBER;
1752 l_cmbn_ptip_id NUMBER;
1753 l_cmbn_ptip_opt_id NUMBER;
1754 l_elig_per_elctbl_chc_id NUMBER;
1755 l_lwr_lvl_rt_exist BOOLEAN := FALSE;
1756 --START ENH
1757 l_opt_id NUMBER;
1758 --END ENH
1759 --
1760 l_dummy_num number;
1761 l_dummy_char varchar2(30);
1762 l_dummy_date date;
1763 l_rt_strt_dt date;
1764 l_rt_strt_dt_cd varchar2(30);
1765 l_rt_strt_dt_rl number;
1766
1767 CURSOR c_epe IS
1768 SELECT epe.elig_per_elctbl_chc_id,
1769 epe.comp_lvl_cd,
1770 epe.oipl_id,
1771 epe.pl_id,
1772 epe.pgm_id,
1773 epe.plip_id,
1774 epe.oiplip_id,
1775 epe.ptip_id,
1776 epe.pl_typ_id,
1777 epe.cmbn_plip_id,
1778 epe.cmbn_ptip_id,
1779 epe.cmbn_ptip_opt_id,
1780 epe.business_group_id,
1781 epe.dflt_flag,
1782 epe.ctfn_rqd_flag,
1783 enb.enrt_bnft_id,
1784 epe.per_in_ler_id,
1785 epe.prtt_enrt_rslt_id,
1786 epe.enrt_cvg_strt_dt,
1787 epe.enrt_cvg_strt_dt_cd,
1788 epe.enrt_cvg_strt_dt_rl,
1789 epe.yr_perd_id,
1790 pel.enrt_perd_strt_dt,
1791 pel.enrt_perd_id,
1792 pel.lee_rsn_id,
1793 pel.acty_ref_perd_cd,
1794 pil.ler_id,
1795 pil.person_id
1796 FROM ben_elig_per_elctbl_chc epe,
1797 ben_pil_elctbl_chc_popl pel,
1798 ben_enrt_bnft enb,
1799 ben_per_in_ler pil
1800 WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
1801 AND epe.per_in_ler_id = pil.per_in_ler_id
1802 AND epe.per_in_ler_id = pel.per_in_ler_id
1803 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1804 -- AND pil.per_in_ler_stat_cd = 'STRTD'
1805 -- added for unrestricted enhancement
1806 and pil.per_in_ler_id = p_per_in_ler_id
1807 AND pil.person_id = p_person_id
1808 -- Override Enrollment Changes
1809 AND nvl(epe.elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1);
1810 -- Override Enrollment Changes
1811 --
1812 CURSOR c_abr_oiplip(
1813 c_effective_date DATE) IS
1814 SELECT abr.acty_base_rt_id,
1815 abr.asn_on_enrt_flag
1816 FROM ben_acty_base_rt_f abr
1817 WHERE abr.oiplip_id = l_oiplip_id
1818 AND abr.rt_usg_cd <> 'FLXCR'
1819 AND abr.acty_base_rt_stat_cd = 'A'
1820 AND c_effective_date BETWEEN abr.effective_start_date
1821 AND abr.effective_end_date
1822 ORDER BY abr_seq_num;
1823 CURSOR c_abr_oipl(
1824 c_effective_date DATE) IS
1825 SELECT abr.acty_base_rt_id,
1826 abr.asn_on_enrt_flag
1827 FROM ben_acty_base_rt_f abr
1828 WHERE abr.acty_base_rt_stat_cd = 'A'
1829 AND abr.oipl_id = l_oipl_id
1830 AND abr.rt_usg_cd <> 'FLXCR'
1831 AND c_effective_date BETWEEN abr.effective_start_date
1832 AND abr.effective_end_date
1833 ORDER BY abr_seq_num;
1834 --START ENH
1835 CURSOR c_abr_opt(
1836 c_effective_date DATE) IS
1837 SELECT abr.acty_base_rt_id,
1838 abr.asn_on_enrt_flag
1839 FROM ben_acty_base_rt_f abr
1840 WHERE abr.acty_base_rt_stat_cd = 'A'
1841 AND abr.opt_id = l_opt_id
1842 AND abr.rt_usg_cd <> 'FLXCR'
1843 AND c_effective_date BETWEEN abr.effective_start_date
1844 AND abr.effective_end_date
1845 ORDER BY abr_seq_num;
1846 --END ENH
1847 CURSOR c_abr_plip(
1848 c_effective_date DATE) IS
1849 SELECT abr.acty_base_rt_id,
1850 abr.asn_on_enrt_flag
1851 FROM ben_acty_base_rt_f abr
1852 WHERE abr.acty_base_rt_stat_cd = 'A'
1853 AND abr.plip_id = l_plip_id
1854 AND abr.rt_usg_cd <> 'FLXCR'
1855 AND abr.cmbn_plip_id IS NULL
1856 AND c_effective_date BETWEEN abr.effective_start_date
1857 AND abr.effective_end_date
1858 ORDER BY abr_seq_num;
1859 CURSOR c_abr_pl(
1860 c_effective_date DATE) IS
1861 SELECT abr.acty_base_rt_id,
1862 abr.asn_on_enrt_flag
1863 FROM ben_acty_base_rt_f abr
1864 WHERE abr.acty_base_rt_stat_cd = 'A'
1865 AND abr.pl_id = l_pl_id
1866 AND abr.rt_usg_cd <> 'FLXCR'
1867 AND c_effective_date BETWEEN abr.effective_start_date
1868 AND abr.effective_end_date
1869 ORDER BY abr_seq_num;
1870 CURSOR c_abr_pgm(
1871 c_effective_date DATE) IS
1872 SELECT abr.acty_base_rt_id,
1873 abr.asn_on_enrt_flag
1874 FROM ben_acty_base_rt_f abr
1875 WHERE abr.acty_base_rt_stat_cd = 'A'
1876 AND abr.pgm_id = l_pgm_id
1877 AND c_effective_date BETWEEN abr.effective_start_date
1878 AND abr.effective_end_date
1879 ORDER BY abr_seq_num;
1880 CURSOR c_abr_ptip(
1881 c_effective_date DATE) IS
1882 SELECT abr.acty_base_rt_id,
1883 abr.asn_on_enrt_flag
1884 FROM ben_acty_base_rt_f abr
1885 WHERE abr.acty_base_rt_stat_cd = 'A'
1886 AND abr.ptip_id = l_ptip_id
1887 AND abr.cmbn_ptip_id IS NULL
1888 AND abr.cmbn_ptip_opt_id IS NULL
1889 AND c_effective_date BETWEEN abr.effective_start_date
1890 AND abr.effective_end_date
1891 ORDER BY abr_seq_num;
1892 CURSOR c_abr_cmbn_ptip(
1893 c_effective_date DATE) IS
1894 SELECT abr.acty_base_rt_id,
1895 abr.asn_on_enrt_flag
1896 FROM ben_acty_base_rt_f abr
1897 WHERE abr.acty_base_rt_stat_cd = 'A'
1898 AND abr.cmbn_ptip_id = l_cmbn_ptip_id
1899 AND c_effective_date BETWEEN abr.effective_start_date
1900 AND abr.effective_end_date
1901 AND abr.cmbn_ptip_opt_id IS NULL
1902 AND abr.ptip_id IS NULL
1903 ORDER BY abr_seq_num;
1904 CURSOR c_abr_flx_plip(
1905 c_effective_date DATE) IS
1906 SELECT abr.acty_base_rt_id,
1907 abr.asn_on_enrt_flag
1908 FROM ben_acty_base_rt_f abr
1909 WHERE abr.acty_base_rt_stat_cd = 'A'
1910 AND abr.plip_id = l_plip_id
1911 AND abr.rt_usg_cd = 'FLXCR'
1912 AND abr.cmbn_plip_id IS NULL
1913 AND c_effective_date BETWEEN abr.effective_start_date
1914 AND abr.effective_end_date
1915 ORDER BY abr_seq_num;
1916 CURSOR c_abr_cmbn_plip(
1917 c_effective_date DATE) IS
1918 SELECT abr.acty_base_rt_id,
1919 abr.asn_on_enrt_flag
1920 FROM ben_acty_base_rt_f abr
1921 WHERE abr.acty_base_rt_stat_cd = 'A'
1922 AND abr.cmbn_plip_id = l_cmbn_plip_id
1923 AND c_effective_date BETWEEN abr.effective_start_date
1924 AND abr.effective_end_date
1925 AND abr.plip_id IS NULL
1926 ORDER BY abr_seq_num;
1927 CURSOR c_abr_flx_oiplip(
1928 c_effective_date DATE) IS
1929 SELECT abr.acty_base_rt_id,
1930 abr.asn_on_enrt_flag
1931 FROM ben_acty_base_rt_f abr
1932 WHERE abr.oiplip_id = l_oiplip_id
1933 AND abr.rt_usg_cd = 'FLXCR'
1934 AND abr.acty_base_rt_stat_cd = 'A'
1935 AND c_effective_date BETWEEN abr.effective_start_date
1936 AND abr.effective_end_date
1937 ORDER BY abr_seq_num;
1938 CURSOR c_abr_cmbn_ptip_opt(
1939 c_effective_date DATE) IS
1940 SELECT abr.acty_base_rt_id,
1941 abr.asn_on_enrt_flag
1942 FROM ben_acty_base_rt_f abr
1943 WHERE abr.acty_base_rt_stat_cd = 'A'
1944 AND abr.cmbn_ptip_opt_id = l_cmbn_ptip_opt_id
1945 AND c_effective_date BETWEEN abr.effective_start_date
1946 AND abr.effective_end_date
1947 AND abr.ptip_id IS NULL
1948 AND abr.cmbn_ptip_id IS NULL
1949 ORDER BY abr_seq_num;
1950 CURSOR c_current_plnippepelig(
1951 c_person_id NUMBER,
1952 c_pl_id NUMBER,
1953 c_effective_date DATE) IS
1954 SELECT ep.prtn_strt_dt,
1955 ep.prtn_ovridn_flag,
1956 ep.prtn_ovridn_thru_dt,
1957 ep.rt_age_val,
1958 ep.rt_los_val,
1959 ep.rt_hrs_wkd_val,
1960 ep.rt_cmbn_age_n_los_val
1961 FROM ben_elig_per_f ep, ben_per_in_ler pil
1962 WHERE ep.person_id = c_person_id
1963 AND ep.pgm_id IS NULL
1964 AND ep.pl_id = c_pl_id
1965 AND c_effective_date BETWEEN ep.effective_start_date
1966 AND ep.effective_end_date
1967 AND pil.per_in_ler_id (+) = ep.per_in_ler_id
1968 AND (
1969 pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
1970 OR pil.per_in_ler_stat_cd IS NULL);
1971 CURSOR c_current_plnipepoelig(
1972 c_person_id NUMBER,
1973 c_pl_id NUMBER,
1974 c_opt_id NUMBER,
1975 c_effective_date DATE) IS
1976 SELECT epo.prtn_strt_dt,
1977 epo.prtn_ovridn_flag,
1978 epo.prtn_ovridn_thru_dt,
1979 epo.rt_age_val,
1980 epo.rt_los_val,
1981 epo.rt_hrs_wkd_val,
1982 epo.rt_cmbn_age_n_los_val
1983 FROM ben_elig_per_f ep, ben_elig_per_opt_f epo, ben_per_in_ler pil
1984 WHERE ep.person_id = c_person_id
1985 AND ep.pgm_id IS NULL
1986 AND ep.pl_id = c_pl_id
1987 AND epo.opt_id = c_opt_id
1988 AND c_effective_date BETWEEN ep.effective_start_date
1989 AND ep.effective_end_date
1990 AND ep.elig_per_id = epo.elig_per_id
1991 AND c_effective_date BETWEEN epo.effective_start_date
1992 AND epo.effective_end_date
1993 AND pil.per_in_ler_id (+) = ep.per_in_ler_id
1994 AND (
1995 pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
1996 OR pil.per_in_ler_stat_cd IS NULL);
1997 --
1998 CURSOR c_opt(
1999 c_effective_date DATE,
2000 c_oipl_id NUMBER) IS
2001 SELECT oipl.opt_id
2002 FROM ben_oipl_f oipl
2003 WHERE oipl.oipl_id = c_oipl_id
2004 AND c_effective_date BETWEEN oipl.effective_start_date
2005 AND oipl.effective_end_date;
2006 --
2007 l_effective_date DATE;
2008 --
2009 l_acty_base_rt_id_table ben_determine_rates.g_acty_base_rt_id_table;
2010 l_asn_on_enrt_flag_table ben_determine_rates.g_asn_on_enrt_flag_table;
2011 --
2012 l_asg_benass_row per_all_assignments_f%ROWTYPE;
2013 --
2014 BEGIN
2015 g_debug := hr_utility.debug_enabled;
2016 if g_debug then
2017 hr_utility.set_location('Entering ' || l_package, 10);
2018 end if;
2019 IF (p_effective_date IS NULL) THEN
2020 if g_debug then
2021 hr_utility.set_location('BEN_91552_BENRATES_INPT_EFFDT', 10);
2022 end if;
2023 fnd_message.set_name('BEN', 'BEN_91552_BENRATES_INPT_EFFDT');
2024 fnd_message.set_token('PACKAGE', l_package);
2025 fnd_message.set_token('PERSON_ID', p_person_id);
2026 fnd_message.set_token('LF_EVT_OCRD_DT', p_lf_evt_ocrd_dt);
2027 fnd_message.raise_error;
2028 END IF;
2029 --
2030 l_effective_date := NVL(p_lf_evt_ocrd_dt, p_effective_date);
2031 --
2032 -- Get person info
2033 --
2034 ben_person_object.get_object(
2035 p_person_id => p_person_id,
2036 p_rec => l_per_row);
2037 --
2038 ben_person_object.get_object(
2039 p_person_id => p_person_id,
2040 p_rec => l_asg_row);
2041 --
2042 IF l_asg_row.assignment_status_type_id IS NOT NULL THEN
2043 --
2044 ben_person_object.get_object(
2045 p_assignment_status_type_id => l_asg_row.assignment_status_type_id,
2046 p_rec => l_ast_row);
2047 --
2048 END IF;
2049 --
2050 ben_person_object.get_object(
2051 p_person_id => p_person_id,
2052 p_rec => l_adr_row);
2053 --
2054 -- added a parameter per_in_ler_id for unrestricted enhancement
2055 --
2056 -- Bug 2200139 Override Enrollment changes if the p_elig_per_elctbl_chc_id
2057 -- is not null let us call only the record not the table from
2058 -- the cache.
2059 if p_elig_per_elctbl_chc_id is null then
2060 --
2061 ben_epe_cache.get_perpilepe_list
2062 (p_person_id => p_person_id
2063 ,p_per_in_ler_id => p_per_in_ler_id
2064 --
2065 ,p_inst_set => l_currepe_set
2066 );
2067 else
2068 -- Override Case
2069 ben_epe_cache.epe_getepedets
2070 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2071 ,p_per_in_ler_id => p_per_in_ler_id
2072 ,p_inst_row => l_currepe_row
2073 );
2074 -- hr_utility.set_location('EPE '||l_currepe_row.elig_per_elctbl_chc_id,45);
2075 -- hr_utility.set_location(' comp_lvl_cd '||l_currepe_row.comp_lvl_cd ,45);
2076 --
2077 -- l_currepe_set(1) := l_currepe_row ;
2078 --
2079 end if;
2080 --
2081 -- Clear epe context row
2082 --
2083 ben_epe_cache.init_context_pileperow;
2084 --
2085 /*
2086 ben_distribute_rates.clear_down_cache;
2087 --
2088 */
2089 -- Bug 2200139 Override Enrollment changes
2090 if p_elig_per_elctbl_chc_id is not null then
2091 --
2092 l_currepe_set(1) := l_currepe_row ;
2093 --
2094 end if;
2095 -- Bug 2200139 Override Enrollment changes
2096 if l_currepe_set.count > 0 then
2097 --
2098 for epe_elenum in l_currepe_set.first..l_currepe_set.last loop
2099 --
2100 if g_debug then
2101 hr_utility.set_location('St EPE loop ' || l_package, 10);
2102 end if;
2103 --
2104 l_currepe_row := l_currepe_set(epe_elenum);
2105 --
2106 -- Bug 1895846
2107 --
2108 if nvl(l_currepe_row.in_pndg_wkflow_flag,'N') = 'N' then
2109 --
2110 l_elig_per_elctbl_chc_id := l_currepe_row.elig_per_elctbl_chc_id;
2111 --
2112 l_pgm_id := l_currepe_row.pgm_id;
2113 l_ptip_id := l_currepe_row.ptip_id;
2114 l_plip_id := l_currepe_row.plip_id;
2115 l_pl_id := l_currepe_row.pl_id;
2116 l_oipl_id := l_currepe_row.oipl_id;
2117 l_oiplip_id := l_currepe_row.oiplip_id;
2118 l_lwr_lvl_rt_exist := TRUE;
2119 -- start with it 'on' for all comp-lvl-cds
2120 l_cmbn_plip_id := l_currepe_row.cmbn_plip_id;
2121 l_cmbn_ptip_id := l_currepe_row.cmbn_ptip_id;
2122 l_cmbn_ptip_opt_id := l_currepe_row.cmbn_ptip_opt_id;
2123 --
2124 -- Get opt id for the oipl
2125 --
2126 IF l_currepe_row.oipl_id IS NOT NULL THEN
2127 --
2128 OPEN c_opt(
2129 c_effective_date => l_effective_date,
2130 c_oipl_id => l_currepe_row.oipl_id);
2131 FETCH c_opt INTO l_currepe_row.opt_id;
2132 CLOSE c_opt;
2133 --
2134 END IF;
2135 --
2136 -- Get eligibility prtn start date for the comp object
2137 --
2138 -- Check for oipl eligibility for plan in and not in a program
2139 --
2140 -- Note: split into multiple cursors for performance reasons rather
2141 -- than a union statement
2142 --
2143 IF l_currepe_row.opt_id IS NOT NULL THEN
2144 --START ENH
2145 l_opt_id := l_currepe_row.opt_id;
2146 --END ENH
2147 --
2148 IF l_currepe_row.pgm_id IS NOT NULL THEN
2149 --
2150 -- Get the cached elig per opt info
2151 --
2152 if g_debug then
2153 hr_utility.set_location('PILEPO cache ' || l_package, 10);
2154 end if;
2155 ben_pep_cache.get_pilepo_dets(
2156 p_person_id => p_person_id,
2157 p_business_group_id => l_currepe_row.business_group_id,
2158 p_effective_date => p_effective_date,
2159 p_pgm_id => l_currepe_row.pgm_id,
2160 p_pl_id => l_currepe_row.pl_id,
2161 p_opt_id => l_currepe_row.opt_id,
2162 p_inst_row => l_epo_row);
2163 if g_debug then
2164 hr_utility.set_location('Dn PILEPO cache ' || l_package, 10);
2165 end if;
2166 --
2167 l_currepe_row.prtn_strt_dt := l_epo_row.prtn_strt_dt;
2168 l_currepe_row.prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
2169 l_currepe_row.prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
2170 l_currepe_row.rt_age_val := l_epo_row.rt_age_val;
2171 l_currepe_row.rt_los_val := l_epo_row.rt_los_val;
2172 l_currepe_row.rt_hrs_wkd_val := l_epo_row.rt_hrs_wkd_val;
2173 l_currepe_row.rt_cmbn_age_n_los_val := l_epo_row.rt_cmbn_age_n_los_val;
2174
2175 --
2176 ELSE
2177 --
2178 OPEN c_current_plnipepoelig(
2179 c_person_id => p_person_id,
2180 c_pl_id => l_currepe_row.pl_id,
2181 c_opt_id => l_currepe_row.opt_id,
2182 c_effective_date => l_effective_date);
2183 FETCH c_current_plnipepoelig INTO l_currepe_row.prtn_strt_dt,
2184 l_currepe_row.prtn_ovridn_flag,
2185 l_currepe_row.prtn_ovridn_thru_dt,
2186 l_currepe_row.rt_age_val,
2187 l_currepe_row.rt_los_val,
2188 l_currepe_row.rt_hrs_wkd_val,
2189 l_currepe_row.rt_cmbn_age_n_los_val;
2190 if g_debug then
2191 hr_utility.set_location('plan,opt,rt'||l_currepe_row.pl_id||l_currepe_row.opt_id||l_currepe_row.rt_los_val, 111);
2192 end if;
2193
2194 CLOSE c_current_plnipepoelig;
2195 --
2196 END IF;
2197 --
2198 ELSE
2199 --
2200 IF l_currepe_row.pgm_id IS NOT NULL THEN
2201 --
2202 -- Get the cached elig per info
2203 --
2204 if g_debug then
2205 hr_utility.set_location('PILPEP cache ' || l_package, 10);
2206 end if;
2207 ben_pep_cache.get_pilpep_dets(
2208 p_person_id => p_person_id,
2209 p_business_group_id => l_currepe_row.business_group_id,
2210 p_effective_date => p_effective_date,
2211 p_pgm_id => l_currepe_row.pgm_id,
2212 p_pl_id => l_currepe_row.pl_id,
2213 p_inst_row => l_pep_row);
2214 if g_debug then
2215 hr_utility.set_location('Dn PILPEP cache ' || l_package, 10);
2216 end if;
2217 --
2218 l_currepe_row.prtn_strt_dt := l_pep_row.prtn_strt_dt;
2219 l_currepe_row.prtn_ovridn_flag := l_pep_row.prtn_ovridn_flag;
2220 l_currepe_row.prtn_ovridn_thru_dt := l_pep_row.prtn_ovridn_thru_dt;
2221 l_currepe_row.rt_age_val := l_pep_row.rt_age_val;
2222 l_currepe_row.rt_los_val := l_pep_row.rt_los_val;
2223 l_currepe_row.rt_hrs_wkd_val := l_pep_row.rt_hrs_wkd_val;
2224 l_currepe_row.rt_cmbn_age_n_los_val := l_pep_row.rt_cmbn_age_n_los_val;
2225 --
2226 ELSE
2227 --
2228 OPEN c_current_plnippepelig(
2229 c_person_id => p_person_id,
2230 c_pl_id => l_currepe_row.pl_id,
2231 c_effective_date => l_effective_date);
2232 FETCH c_current_plnippepelig INTO l_currepe_row.prtn_strt_dt,
2233 l_currepe_row.prtn_ovridn_flag,
2234 l_currepe_row.prtn_ovridn_thru_dt,
2235 l_currepe_row.rt_age_val,
2236 l_currepe_row.rt_los_val,
2237 l_currepe_row.rt_hrs_wkd_val,
2238 l_currepe_row.rt_cmbn_age_n_los_val;
2239
2240 CLOSE c_current_plnippepelig;
2241 --
2242 END IF;
2243 --
2244 END IF;
2245 --
2246 g_rec.person_id := l_currepe_row.person_id;
2247 g_rec.pgm_id := l_currepe_row.pgm_id;
2248 g_rec.pl_id := l_currepe_row.pl_id;
2249 g_rec.oipl_id := l_currepe_row.oipl_id;
2250 -- reset the collection tables
2251 l_acty_base_rt_id_table.DELETE;
2252 l_asn_on_enrt_flag_table.DELETE;
2253 --
2254 -- Set the electable choice context variable
2255 --
2256 ben_epe_cache.g_currepe_row := l_currepe_row;
2257
2258 if l_currepe_row.fonm_cvg_strt_dt is not null then
2259 ben_manage_life_events.fonm := 'Y';
2260 -- assign the FONM CVG date before calling rate calcaultion
2261 if ben_manage_life_events.g_fonm_cvg_strt_dt is null or
2262 ben_manage_life_events.g_fonm_cvg_strt_dt <> l_currepe_row.fonm_cvg_strt_dt then
2263 ben_manage_life_events.g_fonm_cvg_strt_dt := l_currepe_row.fonm_cvg_strt_dt ;
2264 end if ;
2265
2266 hr_utility.set_location ('fonm cvg '||ben_manage_life_events.g_fonm_cvg_strt_dt ,10);
2267 else
2268 ben_manage_life_events.fonm := 'N';
2269 ben_manage_life_events.g_fonm_rt_strt_dt := null;
2270 ben_manage_life_events.g_fonm_cvg_strt_dt := null;
2271 end if;
2272
2273 hr_utility.set_location ('fonm ?'||ben_manage_life_events.fonm,10);
2274
2275 if ben_manage_life_events.fonm = 'Y' then
2276 hr_utility.set_location ('BDD_RACD '||l_package,10);
2277 hr_utility.set_location ('inside .. '||l_package,10);
2278 ben_determine_date.rate_and_coverage_dates
2279 (p_cache_mode => TRUE
2280 ,p_par_ptip_id => l_currepe_row.ptip_id
2281 ,p_par_plip_id => l_currepe_row.plip_id
2282 ,p_person_id => l_currepe_row.person_id
2283 ,p_per_in_ler_id => l_currepe_row.per_in_ler_id
2284 ,p_pgm_id => l_currepe_row.pgm_id
2285 ,p_pl_id => l_currepe_row.pl_id
2286 ,p_oipl_id => l_currepe_row.oipl_id
2287 ,p_enrt_perd_id => l_currepe_row.enrt_perd_id
2288 ,p_lee_rsn_id => l_currepe_row.lee_rsn_id
2289 ,p_which_dates_cd => 'R'
2290 ,p_date_mandatory_flag => 'Y'
2291 ,p_compute_dates_flag => 'Y'
2292 ,p_business_group_id => l_currepe_row.business_group_id
2293 ,p_acty_base_rt_id => null --99999 sent as formula context
2294 ,p_effective_date => l_effective_date
2295 ,p_lf_evt_ocrd_dt => greatest
2296 (nvl(p_lf_evt_ocrd_dt,l_currepe_row.prtn_strt_dt)
2297 ,nvl(l_currepe_row.prtn_strt_dt,p_lf_evt_ocrd_dt))
2298 ,p_rt_strt_dt => l_rt_strt_dt
2299 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
2300 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
2301 ,p_enrt_cvg_strt_dt => l_dummy_date
2302 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
2303 ,p_enrt_cvg_strt_dt_rl => l_dummy_num
2304 ,p_enrt_cvg_end_dt => l_dummy_date
2305 ,p_enrt_cvg_end_dt_cd => l_dummy_char
2306 ,p_enrt_cvg_end_dt_rl => l_dummy_num
2307 ,p_rt_end_dt => l_dummy_date
2308 ,p_rt_end_dt_cd => l_dummy_char
2309 ,p_rt_end_dt_rl => l_dummy_num
2310 );
2311 hr_utility.set_location ('Dn BDD_RACD '||l_package,10);
2312 --
2313 -- If previous rate start date and current date is different then
2314 -- clear the caches.
2315 --
2316 if nvl(ben_manage_life_events.g_fonm_rt_strt_dt, hr_api.g_sot) <>
2317 l_rt_strt_dt
2318 then
2319 --
2320 ben_use_cvg_rt_date.fonm_clear_down_cache;
2321 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
2322 ben_manage_life_events.g_fonm_cvg_strt_dt := l_currepe_row.fonm_cvg_strt_dt;
2323 --
2324 -- Get person info
2325 --
2326 ben_person_object.get_object(
2327 p_person_id => p_person_id,
2328 p_rec => l_per_row);
2329 --
2330 ben_person_object.get_object(
2331 p_person_id => p_person_id,
2332 p_rec => l_asg_row);
2333 --
2334 IF l_asg_row.assignment_status_type_id IS NOT NULL THEN
2335 --
2336 ben_person_object.get_object(
2337 p_assignment_status_type_id => l_asg_row.assignment_status_type_id,
2338 p_rec => l_ast_row);
2339 --
2340 END IF;
2341 --
2342 ben_person_object.get_object(
2343 p_person_id => p_person_id,
2344 p_rec => l_adr_row);
2345 end if;
2346 --
2347 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
2348 ben_manage_life_events.g_fonm_cvg_strt_dt := l_currepe_row.fonm_cvg_strt_dt;
2349 l_effective_date := l_rt_strt_dt;
2350
2351 end if;
2352
2353 --
2354 -- -----------------------------------------------------------------
2355 -- note
2356 -- ----
2357 -- the use of the cursor loops has replaced the bulk fetch operation
2358 -- because of a ora-3113 error
2359 -- -----------------------------------------------------------------
2360 IF l_currepe_row.comp_lvl_cd = 'OIPL' THEN
2361 FOR c1 IN c_abr_oiplip(l_effective_date) LOOP
2362 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2363 c1.acty_base_rt_id;
2364 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2365 c1.asn_on_enrt_flag;
2366 END LOOP;
2367 IF l_acty_base_rt_id_table.COUNT = 0 THEN
2368 FOR c1 IN c_abr_oipl(l_effective_date) LOOP
2369 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2370 c1.acty_base_rt_id;
2371 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2372 c1.asn_on_enrt_flag;
2373 END LOOP;
2374 END IF;
2375 --START ENH
2376 IF l_acty_base_rt_id_table.COUNT = 0 THEN
2377 FOR c1 IN c_abr_opt(l_effective_date) LOOP
2378 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2379 c1.acty_base_rt_id;
2380 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2381 c1.asn_on_enrt_flag;
2382 END LOOP;
2383 END IF;
2384 --END ENH
2385 ELSIF l_currepe_row.comp_lvl_cd IN ('PLAN', 'PLANFC', 'PLANIMP') THEN
2386 FOR c1 IN c_abr_plip(l_effective_date) LOOP
2387 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2388 c1.acty_base_rt_id;
2389 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2390 c1.asn_on_enrt_flag;
2391 END LOOP;
2392 IF l_acty_base_rt_id_table.COUNT = 0 THEN
2393 FOR c1 IN c_abr_pl(l_effective_date) LOOP
2394 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2395 c1.acty_base_rt_id;
2396 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2397 c1.asn_on_enrt_flag;
2398 END LOOP;
2399 END IF;
2400 ELSIF l_currepe_row.comp_lvl_cd = 'PGM' THEN
2401 FOR c1 IN c_abr_pgm(l_effective_date) LOOP
2402 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2403 c1.acty_base_rt_id;
2404 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2405 c1.asn_on_enrt_flag;
2406 END LOOP;
2407 ELSIF l_currepe_row.comp_lvl_cd = 'PTIP' THEN
2408 FOR c1 IN c_abr_ptip(l_effective_date) LOOP
2409 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2410 c1.acty_base_rt_id;
2411 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2412 c1.asn_on_enrt_flag;
2413 END LOOP;
2414 ELSIF l_currepe_row.comp_lvl_cd = 'CPTIP' THEN
2415 FOR c1 IN c_abr_cmbn_ptip(l_effective_date) LOOP
2416 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2417 c1.acty_base_rt_id;
2418 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2419 c1.asn_on_enrt_flag;
2420 END LOOP;
2421 ELSIF l_currepe_row.comp_lvl_cd = 'PLIP' THEN
2422 FOR c1 IN c_abr_flx_plip(l_effective_date) LOOP
2423 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2424 c1.acty_base_rt_id;
2425 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2426 c1.asn_on_enrt_flag;
2427 END LOOP;
2428 ELSIF l_currepe_row.comp_lvl_cd = 'CPLIP' THEN
2429 FOR c1 IN c_abr_cmbn_plip(l_effective_date) LOOP
2430 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2431 c1.acty_base_rt_id;
2432 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2433 c1.asn_on_enrt_flag;
2434 END LOOP;
2435 ELSIF l_currepe_row.comp_lvl_cd = 'OIPLIP' THEN
2436 FOR c1 IN c_abr_flx_oiplip(l_effective_date) LOOP
2437 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2438 c1.acty_base_rt_id;
2439 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2440 c1.asn_on_enrt_flag;
2441 END LOOP;
2442 ELSIF l_currepe_row.comp_lvl_cd = 'CPTIPOPT' THEN
2443 FOR c1 IN c_abr_cmbn_ptip_opt(l_effective_date) LOOP
2444 l_acty_base_rt_id_table(l_acty_base_rt_id_table.COUNT + 1) :=
2445 c1.acty_base_rt_id;
2446 l_asn_on_enrt_flag_table(l_asn_on_enrt_flag_table.COUNT + 1) :=
2447 c1.asn_on_enrt_flag;
2448 END LOOP;
2449 ELSE
2450 if g_debug then
2451 hr_utility.set_location('BEN_91553_BENRATES_UNKN_COMP ', 10);
2452 end if;
2453 fnd_message.set_name('BEN', 'BEN_91553_BENRATES_UNKN_COMP');
2454 fnd_message.set_token('PACKAGE', l_package);
2455 fnd_message.set_token('PERSON_ID', TO_CHAR(l_currepe_row.person_id));
2456 fnd_message.set_token(
2457 'ELIG_PER_ELCTBL_CHC_ID',
2458 TO_CHAR(l_currepe_row.elig_per_elctbl_chc_id));
2459 fnd_message.set_token('PGM_ID', TO_CHAR(l_currepe_row.pgm_id));
2460 fnd_message.set_token('PL_ID', TO_CHAR(l_currepe_row.pl_id));
2461 fnd_message.set_token('OIPL_ID', TO_CHAR(l_currepe_row.oipl_id));
2462 fnd_message.set_token('PLIP_ID', TO_CHAR(l_currepe_row.plip_id));
2463 fnd_message.set_token('PTIP_ID', TO_CHAR(l_currepe_row.ptip_id));
2464 fnd_message.set_token('OIPLIP_ID', TO_CHAR(l_currepe_row.oiplip_id));
2465 fnd_message.set_token('CMBN_PLIP_ID', TO_CHAR(l_currepe_row.cmbn_plip_id));
2466 fnd_message.set_token('CMBN_PTIP_ID', TO_CHAR(l_currepe_row.cmbn_ptip_id));
2467 fnd_message.set_token(
2468 'CMBN_PTIP_OPT_ID',
2469 TO_CHAR(l_currepe_row.cmbn_ptip_opt_id));
2470 fnd_message.raise_error;
2471 END IF;
2472 if g_debug then
2473 hr_utility.set_location('Call ben_rates ' || l_package, 10);
2474 end if;
2475 -- call ben_rates providing at least 1 row exists
2476 IF l_acty_base_rt_id_table.COUNT > 0
2477 OR l_asn_on_enrt_flag_table.COUNT > 0 THEN
2478 --
2479 ben_rates(
2480 p_currepe_row => l_currepe_row,
2481 p_per_row => l_per_row,
2482 p_asg_row => l_asg_row,
2483 p_ast_row => l_ast_row,
2484 p_adr_row => l_adr_row,
2485 p_person_id => l_currepe_row.person_id,
2486 p_pgm_id => l_currepe_row.pgm_id,
2487 p_pl_id => l_currepe_row.pl_id,
2488 p_oipl_id => l_currepe_row.oipl_id,
2489 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
2490 p_enrt_bnft_id => l_currepe_row.enrt_bnft_id,
2491 p_acty_base_rt_id_table => l_acty_base_rt_id_table,
2492 p_asn_on_enrt_flag_table => l_asn_on_enrt_flag_table,
2493 p_effective_date => p_effective_date,
2494 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2495 p_perform_rounding_flg => TRUE,
2496 p_business_group_id => l_currepe_row.business_group_id,
2497 p_dflt_flag => l_currepe_row.dflt_flag,
2498 p_ctfn_rqd_flag => l_currepe_row.ctfn_rqd_flag,
2499 p_mode => p_mode);
2500 --
2501 ELSE
2502 --
2503 -- GLOBALCWB : even if group plan rates are not defined still need to
2504 -- write the data into person groups table.
2505 --
2506 if nvl(p_mode, 'X') = 'W' then
2507 --
2508 --BUG 5148387 Need to get Benefit Assignment is this is null
2509 IF l_asg_row.assignment_id IS NULL THEN
2510 ben_person_object.get_benass_object(
2511 p_person_id => p_person_id,
2512 p_rec => l_asg_benass_row
2513 );
2514 END IF;
2515 --
2516 ben_manage_cwb_life_events.g_cwb_person_groups_rec
2517 := ben_manage_cwb_life_events.g_cwb_person_groups_rec_temp;
2518 ben_manage_cwb_life_events.g_cwb_person_rates_rec
2519 := ben_manage_cwb_life_events.g_cwb_person_rates_rec_temp;
2520 ben_manage_cwb_life_events.populate_cwb_rates(
2521 --
2522 -- Columns needed for ben_cwb_person_rates
2523 --
2524 p_person_id => p_person_id
2525 ,p_pl_id => l_currepe_row.pl_id
2526 ,p_oipl_id => l_currepe_row.oipl_id
2527 ,p_opt_id => l_currepe_row.opt_id
2528 ,p_assignment_id => NVL(l_asg_row.assignment_id,l_asg_benass_row.assignment_id)
2529 ,p_elig_flag => null -- 9999 it should come from g_curr_epe_rec
2530 ,p_inelig_rsn_cd => null -- 9999 it should come from g_curr_epe_rec --
2531 -- Columns needed by BEN_CWB_PERSON_GROUPS
2532 --
2533 ,p_due_dt => null
2534 ,p_access_cd => null -- passed inside the poppulate_cwb_rates
2535 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
2536 -- ,p_no_person_rates => 'Y' -- Only create the group rates.
2537 );
2538 --
2539 end if;
2540 --
2541 END IF;
2542 --
2543 ELSE -- This is for suspended pen or pending workflow epe records
2544 --
2545 if g_debug then
2546 hr_utility.set_location( ' l_currepe_row.elig_per_elctbl_chc_id'||l_currepe_row.elig_per_elctbl_chc_id , 15);
2547 hr_utility.set_location( 'Suspended pen or in_pndg_wkflow_flag is Y ' ,15);
2548 hr_utility.set_location('Leaving ' || l_package, 10);
2549 end if;
2550 --
2551 null ;
2552 --
2553 END IF;
2554 --
2555 END LOOP;
2556 if g_debug then
2557 hr_utility.set_location('Leaving ' || l_package, 10);
2558 end if;
2559 --
2560 end if;
2561 --
2562 -- Clear epe context row
2563 --
2564 ben_epe_cache.init_context_pileperow;
2565 --
2566 /*
2567 ben_distribute_rates.clear_down_cache;
2568 --
2569 */
2570 END main;
2571 --
2572 END ben_determine_rates;