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