1 package body ben_prem_pl_oipl_monthly as
2 /* $Header: benprplo.pkb 120.0 2005/05/28 09:20:35 appldev noship $ */
3 g_package varchar2(80) := 'ben_prem_pl_oipl_monthly';
4
5 -- ----------------------------------------------------------------------------
6 -- |----------------------< get_comp_object_info >----------------------------|
7 -- ----------------------------------------------------------------------------
8 -- This procedure is called from main and from ben_premium_plan_concurrent
9 -- to get premium comp object ids.
10 procedure get_comp_object_info
11 (p_oipl_id in number default null
12 ,p_pl_id in number default null
13 ,p_pgm_id in number default null
14 ,p_effective_date in date
15 ,p_out_pgm_id out nocopy number
16 ,p_out_pl_typ_id out nocopy number
17 ,p_out_pl_id out nocopy number
18 ,p_out_opt_id out nocopy number) is
19
20 cursor c_opt(p_oipl_id number) is
21 select oipl.opt_id, oipl.pl_id, pl.pl_typ_id
22 from ben_oipl_f oipl, ben_pl_f pl
23 where oipl.oipl_id = p_oipl_id
24 and pl.pl_id = oipl.pl_id
25 and p_effective_date
26 between pl.effective_start_date
27 and pl.effective_end_date
28 and p_effective_date
29 between oipl.effective_start_date
30 and oipl.effective_end_date;
31
32 cursor c_pl(p_pl_id number) is
33 select pl.pl_id, pl.pl_typ_id
34 from ben_pl_f pl
35 where pl.pl_id = p_pl_id
36 and p_effective_date
37 between pl.effective_start_date
38 and pl.effective_end_date;
39
40 cursor c_plip (p_pl_id number)is
41 select plip.pgm_id
42 from ben_plip_f plip
43 where plip.pgm_id = p_pgm_id
44 and plip.pl_id = p_pl_id
45 and p_effective_date
46 between plip.effective_start_date
47 and plip.effective_end_date;
48
49 begin
50 p_out_pl_typ_id := null ;
51 p_out_pl_id := null ;
52 p_out_opt_id := null ;
53 p_out_pgm_id := null ;
54
55 if p_oipl_id is not null then
56 open c_opt(p_oipl_id);
57 fetch c_opt into p_out_opt_id, p_out_pl_id, p_out_pl_typ_id;
58 close c_opt;
59 else -- pl id must be not null
60 open c_pl(p_pl_id);
61 fetch c_pl into p_out_pl_id, p_out_pl_typ_id;
62 close c_pl;
63 end if;
64
65 if p_pgm_id is not null then
66 open c_plip(p_pl_id => p_out_pl_id);
67 fetch c_plip into p_out_pgm_id;
68 close c_plip;
69 end if;
70
71 end get_comp_object_info;
72 -- ----------------------------------------------------------------------------
73 -- |----------------------< determine_vrbl_prfls >----------------------------|
74 -- ----------------------------------------------------------------------------
75 -- Procedure used internally to compute variable actual premiums.
76 procedure determine_vrbl_prfls
77 (p_actl_prem_id in number
78 ,p_business_group_id in number
79 ,p_effective_date in date
80 ,p_first_day_of_month in date
81 ,p_last_day_of_month in date
82 ,p_pl_id in number
83 ,p_oipl_id in number
84 ,p_pl_typ_id in number
85 ,p_pl2_id in number
86 ,p_opt_id in number
87 ,p_wsh_rl_dy_mo_num in number
88 ,p_rndg_cd in varchar2 default null
89 ,p_rndg_rl in number default null
90 ,p_num_of_prtts in number
91 ,p_total_cvg in number
92 ,p_actl_prem_val in number
93 ,p_bnft_rt_typ_cd in varchar2
94 ,p_mlt_cd in varchar2
95 ,p_vrbl_rt_add_on_calc_rl in number
96 ,p_val out nocopy number
97 ,p_matched_vrbl_prfl out nocopy varchar2) is
98 --
99 l_package varchar2(80) := g_package||'.determine_vrbl_prfls';
100 l_error_text varchar2(200) := null;
101 --
102 -- participants that have this coverage this month that should be
103 -- paying the premium.
104 -- If this cursor changes, check c_results and c_each_result
105 -- Assuming person can't be in same plan in two programs.
106 -- if they are, they will be counted twice due to this cursor.
107 cursor c_people is
108 select distinct pen.person_id , pen.pgm_id, nvl(pen.bnft_amt,0) bnft_amt
109 from ben_prtt_enrt_rslt_f pen,
110 per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
111 where per.person_id = pen.person_id
112 and per.business_group_id = pen.business_group_id
113 and p_effective_date between per.effective_start_date
114 and per.effective_end_date
115 and pen.prtt_enrt_rslt_stat_cd is null
116 and pen.sspndd_flag = 'N'
117 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
118 -- cvg is active entire month
119 and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
120 and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
121 or
122 -- is no washrule and cvg was for at least part of the month
123 (pen.enrt_cvg_strt_dt <= p_last_day_of_month
124 and pen.enrt_cvg_thru_dt >= p_first_day_of_month
125 and p_wsh_rl_dy_mo_num is null)
126 or
127 -- if washrule there, and cvg strts this month it starts before wash day.
128 (p_wsh_rl_dy_mo_num is not null
129 and pen.enrt_cvg_strt_dt between
130 p_first_day_of_month and p_last_day_of_month
131 and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
132 or
133 -- if washrule there, and cvg end this month it ends after wash day.
134 (p_wsh_rl_dy_mo_num is not null
135 and pen.enrt_cvg_thru_dt between
136 p_first_day_of_month and p_last_day_of_month
137 and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
138 and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
139 and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
140 and pen.business_group_id = p_business_group_id
141 /* Bug#2903964 - it is better to get the results based on effective end date rather
142 filtering on effective_date
143 and p_effective_date between
144 pen.effective_start_date and pen.effective_end_date */
145 and pen.effective_end_date = hr_api.g_eot;
146 l_people c_people%rowtype;
147
148 cursor c_vrbl_val (p_vrbl_rt_prfl_id number) is
149 select nvl(vpf.val,0) val, vpf.upr_lmt_val, vpf.upr_lmt_calc_rl
150 ,vpf.lwr_lmt_val, vpf.lwr_lmt_calc_rl, vpf.rndg_cd,
151 vpf.rndg_rl, vpf.bnft_rt_typ_cd, vpf.mlt_cd
152 from ben_vrbl_rt_prfl_f vpf
153 where vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
154 and vpf.business_group_id = p_business_group_id
155 and p_effective_date between
156 vpf.effective_start_date and vpf.effective_end_date;
157 l_vrbl_val c_vrbl_val%rowtype;
158
159 -- make sure flags default to 'Y' ??
160 cursor c_alwys_cnt_no is
161 select 'Y'
162 from ben_vrbl_rt_prfl_f vpf, ben_actl_prem_vrbl_rt_f apv
163 where apv.actl_prem_id = p_actl_prem_id
164 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
165 and ((vpf.alwys_cnt_all_prtts_flag = 'N' and exists
166 (select 'x' from ben_ttl_prtt_rt_f ttp
167 where ttp.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id))
168 or (vpf.alwys_sum_all_cvg_flag = 'N'and exists
169 (select 'x' from ben_ttl_cvg_vol_rt_f tcv
170 where tcv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id)))
171 and vpf.business_group_id = p_business_group_id
172 and p_effective_date between
173 vpf.effective_start_date and vpf.effective_end_date;
174 l_alwys_cnt_no varchar2(1) := 'N';
175
176
177 l_vrbl_rt_prfl_id number;
178 l_num_of_prfls_matched number := 0;
179
180 l_persons g_person_table;
181 /*type g_person_rec is record
182 (person_id number
183 ,pgm_id number
184 ,bnft_amt number); */
185
186 l_insert_record varchar2(1);
187 l_val number;
188 l_persons_matched number := 0;
189 i number := 0;
190 l_use_globals boolean := false;
191 l_outputs ff_exec.outputs_t;
192
193
194 begin
195 hr_utility.set_location ('Entering '||l_package,10);
196 hr_utility.set_location ('actl_prem_id:'||
197 to_char(p_actl_prem_id),10);
198
199 -- p_pl_id is the actl_prem.pl_id, which can be null
200 -- p_pl2_id is the pl_id of the actl_prem.oipl_id or actl_prem.pl_id. will
201 -- never be null.
202
203 p_matched_vrbl_prfl := 'N'; -- used to see if ANYONE matched a profile.
204 p_val := 0;
205
206 ben_evaluate_rate_profiles.init_globals;
207
208 open c_alwys_cnt_no;
209 fetch c_alwys_cnt_no into l_alwys_cnt_no;
210 close c_alwys_cnt_no;
211 -- ?? this assumes that all vrbl prfls for an actl prem have this flag
212 -- set the same....
213
214 if l_alwys_cnt_no = 'Y' then
215 -- there are variable rate profiles with one of the ALWAYS flags set to NO.
216 l_use_globals := true; -- for use in SECOND call to main.
217 for l_people in c_people loop
218 -- these calls are required so we can call ben_evaluate_rate_profiles.main
219 ben_env_object.setenv(p_person_id => l_people.person_id);
220 --ben_env_object.setenv(p_business_group_id => p_business_group_id);
221 if l_people.pgm_id is not null then
222 ben_env_object.setenv(p_pgm_id => l_people.pgm_id);
223 end if;
224 if p_pl2_id is not null then
225 ben_env_object.setenv(p_pl_id => p_pl2_id);
226 end if;
227 if p_oipl_id is not null then
228 ben_env_object.setenv(p_oipl_id => p_oipl_id);
229 end if;
230 -- Call main with the all_prfls = true. This tells main to find all the profiles
231 -- that a person matches, not just the first one. It loads these into a global
232 -- table structure. It skips the ttl_prtt and ttl_cvg evaulation.
233 ben_evaluate_rate_profiles.main
234 (p_person_id => l_people.person_id,
235 p_elig_per_elctbl_chc_id => null,
236 p_acty_base_rt_id => null,
237 p_actl_prem_id => p_actl_prem_id,
238 p_cvg_amt_calc_mthd_id => null,
239 p_effective_date => p_effective_date,
240 p_lf_evt_ocrd_dt => p_effective_date,
241 p_calc_only_rt_val_flag => true,
242 p_pgm_id => l_people.pgm_id,
243 p_pl_id => p_pl2_id, -- pl id of apr's oipl or pl
244 p_pl_typ_id => p_pl_typ_id,
245 p_oipl_id => p_oipl_id,
246 p_per_in_ler_id => null,
247 p_ler_id => null,
248 p_business_group_id => p_business_group_id,
249 p_ttl_prtt => null,
250 p_ttl_cvg => null,
251 p_all_prfls => true,
252 p_use_globals => false,
253 p_use_prfls => false,
254 p_bnft_amt => l_people.bnft_amt,
255 p_vrbl_rt_prfl_id => l_vrbl_rt_prfl_id); -- output
256
257 if l_vrbl_rt_prfl_id is not null then
258 -- the person matched at least one profile, save them for the
259 -- second looping.
260 l_persons_matched := l_persons_matched + 1;
261 l_persons(l_persons_matched).person_id := l_people.person_id;
262 l_persons(l_persons_matched).pgm_id := l_people.pgm_id;
263 l_persons(l_persons_matched).bnft_amt := l_people.bnft_amt;
264 end if;
265 end loop; -- c_people
266
267 end if;
268
269 -- All people were evaluated thru all profiles. We know how many matched no
270 -- profiles so far and how many matched each profile. Now we want to loop through
271 -- those folks that matched a profile and find which profile they match first
272 -- WITH evaluation of ttl_cvg and ttl_prtt.
273
274 -- This loop is either looping through people that matched a profile in
275 -- the loop above, or if we didn't execute that loop, thru all people
276 -- in c_people cursor.
277 if l_persons_matched > 0 or l_alwys_cnt_no = 'N' then
278 if l_alwys_cnt_no = 'N' then
279 open c_people;
280 end if;
281 loop
282 if l_alwys_cnt_no = 'N' then
283 fetch c_people into l_people;
284 if c_people%NOTFOUND or c_people%NOTFOUND is null then
285 close c_people;
286 -- ?? check what's done on exit.
287 exit;
288 end if;
289 else
290 i := i + 1;
291 if i > l_persons_matched then exit; end if;
292 l_people.person_id := l_persons(i).person_id;
293 l_people.pgm_id := l_persons(i).pgm_id;
294 l_people.bnft_amt := l_persons(i).bnft_amt;
295 end if;
296 hr_utility.set_location ('looping for person '||
297 to_char(l_people.person_id),12);
298
299 -- these calls are required so we can call ben_evaluate_rate_profiles.main
300 ben_env_object.setenv(p_person_id => l_people.person_id);
301 --ben_env_object.setenv(p_business_group_id => p_business_group_id);
302 if l_people.pgm_id is not null then
303 ben_env_object.setenv(p_pgm_id => l_people.pgm_id);
304 end if;
305 if p_pl2_id is not null then
306 ben_env_object.setenv(p_pl_id => p_pl2_id);
307 end if;
308 if p_oipl_id is not null then
309 ben_env_object.setenv(p_oipl_id => p_oipl_id);
310 end if;
311
312 -- ?? should we pass pl_id of actl_prem (which could be null)
313 -- or of actl_prem's pl or oipl? currently passing pl/oipl's.
314 ben_evaluate_rate_profiles.main
315 (p_person_id => l_people.person_id,
316 p_elig_per_elctbl_chc_id => null,
317 p_acty_base_rt_id => null,
318 p_actl_prem_id => p_actl_prem_id,
319 p_cvg_amt_calc_mthd_id => null,
320 p_effective_date => p_effective_date,
321 p_lf_evt_ocrd_dt => p_effective_date,
322 p_calc_only_rt_val_flag => true,
323 p_pgm_id => l_people.pgm_id,
324 p_pl_id => p_pl2_id, -- pl id of apr's oipl or pl
325 p_pl_typ_id => p_pl_typ_id,
326 p_oipl_id => p_oipl_id,
327 p_per_in_ler_id => null,
328 p_ler_id => null,
329 p_business_group_id => p_business_group_id,
330 p_ttl_prtt => p_num_of_prtts,
331 p_ttl_cvg => p_total_cvg,
332 p_all_prfls => false,
333 p_use_globals => l_use_globals,
334 p_use_prfls => true, -- bug 1211317 added parm.
335 p_bnft_amt => l_people.bnft_amt,
336 p_vrbl_rt_prfl_id => l_vrbl_rt_prfl_id); -- output
337
338 if l_vrbl_rt_prfl_id is not null then
339 p_matched_vrbl_prfl := 'Y';
340 end if;
341 end loop; -- c_people or matched people
342 end if;
343
344
345 ----------- compute total premium value -------------------------------
346 -- first use the actual premium value for those persons that didn't match
347 -- any variable profiles:
348 if ben_evaluate_rate_profiles.g_no_match_cnt > 0 then
349 hr_utility.set_location ('g_no_match_cnt:'||
350 to_char(ben_evaluate_rate_profiles.g_no_match_cnt),18);
351 if p_mlt_cd = 'NSVU' then
352 if p_vrbl_rt_add_on_calc_rl is null then
353 -- there is no standard value and no profiles matched, error.
354 fnd_message.set_name('BEN', 'BEN_92290_NSVU_NO_PROFILES');
355 fnd_message.raise_error;
356 else
357 -- this rule returns an amount.
358 l_outputs := benutils.formula
359 (p_formula_id => p_vrbl_rt_add_on_calc_rl,
360 p_effective_date => p_effective_date,
361 p_business_group_id => p_business_group_id,
362 p_assignment_id => null, -- we are not processing a single
363 p_organization_id => null, -- person, but a group.
364 p_pgm_id => null, -- and we don't know the pgm.
365 p_pl_id => p_pl2_id,
366 p_pl_typ_id => p_pl_typ_id,
367 p_opt_id => p_opt_id,
368 p_ler_id => null,
369 p_jurisdiction_code => null);
370 p_val := l_outputs(l_outputs.first).value;
371 end if;
372 elsif p_mlt_cd = 'TTLPRTT' then
373 hr_utility.set_location ('ttlprtt p_actl_prem_id:'||to_char(p_actl_prem_id)||
374 ' p_bnft_rt_typ_cd:'||p_bnft_rt_typ_cd, 22);
375 benutils.rt_typ_calc
376 (p_rt_typ_cd => p_bnft_rt_typ_cd
377 ,p_val => ben_evaluate_rate_profiles.g_no_match_cnt
378 ,p_val_2 => p_actl_prem_val
379 ,p_calculated_val => p_val); -- output p_val
380 else -- p_mlt_cd = 'TTLCVG'
381 hr_utility.set_location ('ttlcvg p_actl_prem_id:'||to_char(p_actl_prem_id)||
382 ' p_bnft_rt_typ_cd:'||p_bnft_rt_typ_cd, 24);
383 benutils.rt_typ_calc
384 (p_rt_typ_cd => p_bnft_rt_typ_cd
385 ,p_val => ben_evaluate_rate_profiles.g_no_match_cvg
386 ,p_val_2 => p_actl_prem_val
387 ,p_calculated_val => p_val); -- output p_val
388 end if;
389 end if;
390 -- round against actl_prem.
391 p_val := benutils.do_rounding
392 (p_rounding_cd => p_rndg_cd
393 ,p_rounding_rl => p_rndg_rl
394 ,p_value => p_val
395 ,p_effective_date => p_effective_date);
396 -- then loop thru profiles matched and add up rates based on number of people
397 -- that matched that profile or amount of coverage of those people.
398 if ben_evaluate_rate_profiles.g_num_of_prfls_used > 0 then
399 for i in 1..ben_evaluate_rate_profiles.g_num_of_prfls_used loop
400 hr_utility.set_location('vrbl_rt_prfl_id'||
401 to_char(ben_evaluate_rate_profiles.g_use_prfls(i).vrbl_rt_prfl_id),26);
402 open c_vrbl_val(p_vrbl_rt_prfl_id =>
403 ben_evaluate_rate_profiles.g_use_prfls(i).vrbl_rt_prfl_id);
404 fetch c_vrbl_val into l_vrbl_val;
405 if c_vrbl_val%found then
406 if l_vrbl_val.mlt_cd = 'TTLPRTT' then
407 benutils.rt_typ_calc
408 (p_rt_typ_cd => l_vrbl_val.bnft_rt_typ_cd
409 ,p_val =>
410 ben_evaluate_rate_profiles.g_use_prfls(i).match_cnt
411 ,p_val_2 => l_vrbl_val.val
412 ,p_calculated_val => l_val); -- output val
413 else -- l_vrbl_val.mlt_cd = 'TTLCVG'
414 benutils.rt_typ_calc
415 (p_rt_typ_cd => l_vrbl_val.bnft_rt_typ_cd
416 ,p_val =>
417 ben_evaluate_rate_profiles.g_use_prfls(i).match_cvg
418 ,p_val_2 => l_vrbl_val.val
419 ,p_calculated_val => l_val); -- output val
420 end if;
421 -- round and check limits against variable rate profiles.
422 l_val := benutils.do_rounding
423 (p_rounding_cd => l_vrbl_val.rndg_cd
424 ,p_rounding_rl => l_vrbl_val.rndg_rl
425 ,p_value => l_val
426 ,p_effective_date => p_effective_date);
427 hr_utility.set_location('Variable Limits Checking',28);
428 benutils.limit_checks
429 (p_upr_lmt_val => l_vrbl_val.upr_lmt_val,
430 p_lwr_lmt_val => l_vrbl_val.lwr_lmt_val,
431 p_upr_lmt_calc_rl => l_vrbl_val.upr_lmt_calc_rl,
432 p_lwr_lmt_calc_rl => l_vrbl_val.lwr_lmt_calc_rl,
433 p_effective_date => p_effective_date,
434 p_business_group_id => p_business_group_id,
435 p_assignment_id => null, -- we are not processing a single
436 p_organization_id => null, -- person, but a group.
437 p_pgm_id => null, -- and we don't know the pgm.
438 p_pl_id => p_pl2_id,
439 p_pl_typ_id => p_pl_typ_id,
440 p_opt_id => p_opt_id,
441 p_ler_id => null,
442 p_state => null,
443 p_val => l_val);
444 -- Add rounded value to running total
445 p_val := p_val + l_val;
446 else
447 -- ?? error
448 null;
449 end if;
450 close c_vrbl_val;
451 end loop;
452 end if;
453 hr_utility.set_location ('Leaving '||l_package,99);
454 exception
455 when others then
456 l_error_text := sqlerrm;
457 hr_utility.set_location ('Fail in '||l_package||' error:',999);
458 hr_utility.set_location (l_error_text,999);
459 fnd_message.raise_error;
460 end determine_vrbl_prfls;
461 -- ----------------------------------------------------------------------------
462 -- |------------------------------< main >------------------------------------|
463 -- ----------------------------------------------------------------------------
464 -- This is the procedure to call to determine all the 'PROC' type premiums for
465 -- the month.
466 procedure main
467 (p_validate in varchar2 default 'N',
468 p_actl_prem_id in number,
469 p_business_group_id in number,
470 p_mo_num in number,
471 p_yr_num in number,
472 p_first_day_of_month in date,
473 p_effective_date in date) is
474 -- p_pl_typ_id in number,
475 -- p_pl_id in number,
476 -- p_opt_id in number
477 --
478 l_package varchar2(80) := g_package||'.main';
479 l_error_text varchar2(200) := null;
480
481 cursor c_prems is
482 select apr.wsh_rl_dy_mo_num, apr.actl_prem_id, apr.prem_asnmt_lvl_cd,
483 apr.val, apr.uom, apr.pl_id, apr.oipl_id, apr.bnft_rt_typ_cd,
484 apr.rndg_cd, apr.rndg_rl, apr.upr_lmt_calc_rl, apr.upr_lmt_val,
485 apr.lwr_lmt_calc_rl, apr.lwr_lmt_val, apr.prsptv_r_rtsptv_cd,
486 apr.mlt_cd, apr.cost_allocation_keyflex_id, apr.vrbl_rt_add_on_calc_rl
487 from ben_actl_prem_f apr
488 where apr.actl_prem_id = p_actl_prem_id
489 and p_effective_date between
490 apr.effective_start_date and apr.effective_end_date;
491 l_prems c_prems%rowtype;
492
493 --
494 -- Number of participants that have this coverage this month that should be
495 -- paying the premium:
496 -- If this cursor changes, check c_people and c_each_result
497 cursor c_results (p_first_day_of_month date,
498 p_last_day_of_month date, p_wsh_rl_dy_mo_num number,
499 p_pl_id number, p_oipl_id number) is
500 select count('s') num_of_prtts, sum(nvl(pen.bnft_amt,0)) total_cvg
501 from ben_prtt_enrt_rslt_f pen,
502 per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
503 where per.person_id = pen.person_id
504 and per.business_group_id = pen.business_group_id
505 and p_effective_date between per.effective_start_date
506 and per.effective_end_date
507 and pen.prtt_enrt_rslt_stat_cd is null
508 and pen.sspndd_flag = 'N'
509 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
510 -- cvg is active entire month
511 and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
512 and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
513 or
514 -- is no washrule and cvg was for at least part of the month
515 (pen.enrt_cvg_strt_dt <= p_last_day_of_month
516 and pen.enrt_cvg_thru_dt >= p_first_day_of_month
517 and p_wsh_rl_dy_mo_num is null)
518 or
519 -- if washrule there, and cvg strts this month it starts before wash day.
520 (p_wsh_rl_dy_mo_num is not null
521 and pen.enrt_cvg_strt_dt between
522 p_first_day_of_month and p_last_day_of_month
523 and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
524 or
525 -- if washrule there, and cvg end this month it ends after wash day.
526 (p_wsh_rl_dy_mo_num is not null
527 and pen.enrt_cvg_thru_dt between
528 p_first_day_of_month and p_last_day_of_month
529 and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
530 and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
531 and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
532 and pen.business_group_id = p_business_group_id
533 /* Bug#2903964 - it is better to get the results based on effective end date rather
534 filtering on effective_date
535 and p_effective_date between
536 pen.effective_start_date and pen.effective_end_date */
537 and pen.effective_end_date = hr_api.g_eot;
538 l_results c_results%rowtype;
539 --
540 -- participants that have this coverage this month that should be
541 -- paying the premium. Used when we need to allocate prem to each participant.
542 -- If this cursor changes, check c_results and c_people
543 cursor c_each_result (p_first_day_of_month date,
544 p_last_day_of_month date, p_wsh_rl_dy_mo_num number,
545 p_pl_id number, p_oipl_id number) is
546 select pen.prtt_enrt_rslt_id, pen.person_id, pen.pl_id, pen.oipl_id,
547 pen.pgm_id, pen.pl_typ_id,
548 /* Start of Code Change for WWBUG: 1646442: added following table */
549 pen.enrt_cvg_strt_dt
550 /* End of Code Change for WWBUG: 1646442 */
551 from ben_prtt_enrt_rslt_f pen,
552 per_all_people_f per -- Bug 1750817 : Filter out enrollments of deleted person.
553 where per.person_id = pen.person_id
554 and per.business_group_id = pen.business_group_id
555 and p_effective_date between per.effective_start_date
556 and per.effective_end_date
557 and pen.prtt_enrt_rslt_stat_cd is null
558 and pen.sspndd_flag = 'N'
559 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
560 -- cvg is active entire month
561 and ((pen.enrt_cvg_strt_dt <= p_last_day_of_month
562 and pen.enrt_cvg_thru_dt >= p_first_day_of_month)
563 or
564 -- is no washrule and cvg was for at least part of the month
565 (pen.enrt_cvg_strt_dt <= p_last_day_of_month
566 and pen.enrt_cvg_thru_dt >= p_first_day_of_month
567 and p_wsh_rl_dy_mo_num is null)
568 or
569 -- if washrule there, and cvg strts this month it starts before wash day.
570 (p_wsh_rl_dy_mo_num is not null
571 and pen.enrt_cvg_strt_dt between
572 p_first_day_of_month and p_last_day_of_month
573 and to_char(pen.enrt_cvg_strt_dt,'dd') < p_wsh_rl_dy_mo_num )
574 or
575 -- if washrule there, and cvg end this month it ends after wash day.
576 (p_wsh_rl_dy_mo_num is not null
577 and pen.enrt_cvg_thru_dt between
578 p_first_day_of_month and p_last_day_of_month
579 and to_char(pen.enrt_cvg_thru_dt,'dd') > p_wsh_rl_dy_mo_num ))
580 and ((pen.pl_id = p_pl_id and pen.oipl_id is null) or p_pl_id is null)
581 and (pen.oipl_id = p_oipl_id or p_oipl_id is null)
582 and pen.business_group_id = p_business_group_id
583 /* Bug#2903964 - it is better to get the results based on effective end date rather
584 filtering on effective_date
585 and p_effective_date between
586 pen.effective_start_date and pen.effective_end_date*/
587 and pen.effective_end_date = hr_api.g_eot;
588 l_each_result c_each_result%rowtype;
589
590 -- participant prem row:
591 cursor c_ppe (p_prtt_enrt_rslt_id number
592 ,p_actl_prem_id number ) is
593 select ppe.std_prem_uom, ppe.prtt_prem_id
594 from ben_prtt_prem_f ppe,
595 ben_per_in_ler pil
596 where ppe.actl_prem_id = p_actl_prem_id
597 and ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
598 and ppe.business_group_id = p_business_group_id
599 and p_effective_date between
600 ppe.effective_start_date and ppe.effective_end_date
601 and pil.per_in_ler_id(+)=ppe.per_in_ler_id
602 and pil.business_group_id(+)=ppe.business_group_id
603 and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
604 or pil.per_in_ler_stat_cd is null -- outer join condition
605 );
606 l_ppe c_ppe%rowtype;
607
608 cursor c_pbm (p_actl_prem_id number, p_mo_num number, p_yr_num number) is
609 select pbm.pl_r_oipl_prem_by_mo_id, pbm.object_version_number
610 from ben_pl_r_oipl_prem_by_mo_f pbm
611 where pbm.mo_num = p_mo_num
612 and pbm.yr_num = p_yr_num
613 and pbm.actl_prem_id = p_actl_prem_id ;
614 -- and p_effective_date between pbm.effective_start_date and pbm.effective_end_date; -- bug 2784213
615 l_pbm c_pbm%rowtype;
616
617 cursor c_prm (p_prtt_prem_id number, p_mo_num number, p_yr_num number) is
618 select prm.prtt_prem_by_mo_id,prm.val,prm.cr_val,mnl_adj_flag, prm.object_version_number
619 from ben_prtt_prem_by_mo_f prm
620 where prm.mo_num = p_mo_num
621 and prm.yr_num = p_yr_num
622 and prm.prtt_prem_id = p_prtt_prem_id
623 order by prm.effective_start_date ;
624 -- and p_effective_date between prm.effective_start_date and prm.effective_end_date;-- bug 2784213
625 l_prm c_prm%rowtype;
626
627
628 cursor c_prm_ovn (p_prtt_prem_id number ,
629 p_mo_num number,
630 p_yr_num number,
631 p_effective_dt date) is
632 select prm.prtt_prem_by_mo_id,prm.val,prm.cr_val,mnl_adj_flag, prm.object_version_number
633 from ben_prtt_prem_by_mo_f prm
634 where prm.mo_num = p_mo_num
635 and prm.yr_num = p_yr_num
636 and prm.prtt_prem_id = p_prtt_prem_id
637 and p_effective_dt between prm.effective_start_date and prm.effective_end_date;
638
639
640
641
642
643 l_pl_typ_id number ;
644 l_pl_id number ;
645 l_opt_id number ;
646 l_pgm_id number ;
647 l_val_net number ;
648
649 l_effective_start_date date;
650 l_effective_end_date date;
651 l_cak number;
652 l_ovn number;
653 l_val number;
654 l_prtt_val number;
655 l_reg_prtt_val number;
656 l_balance_val number;
657 l_matched_vrbl_prfl varchar2(1);
658 l_rule_ret varchar2(1);
659 l_last_day_of_month date;
660 l_first_day_of_month date;
661 l_mo_num number;
662 l_yr_num number;
663
664 l_outputs ff_exec.outputs_t;
665 l_effective_date_mo date;
666 l_last_effective_dt date;
667 begin
668 hr_utility.set_location ('Entering '||l_package,10);
669 Savepoint process_pl_premium_savepoint;
670
671 -- this call is required so we can call ben_evaluate_rate_profiles.main
672 ben_env_object.init(p_business_group_id => p_business_group_id,
673 p_effective_date => p_effective_date,
674 p_thread_id => null,
675 p_chunk_size => null,
676 p_threads => null,
677 p_max_errors => null,
678 p_benefit_action_id => null) ;
679
680 -- p_effective_date is always the last day of the month this is being run
681
682 hr_utility.set_location ('process actl_prem_id:'||to_char(p_actl_prem_id),12);
683
684 open c_prems;
685 fetch c_prems into l_prems;
686 close c_prems;
687
688 if l_prems.prsptv_r_rtsptv_cd = 'RETRO' then
689 -- if the premium is retrospective, process with this month
690 l_last_day_of_month := p_effective_date;
691 l_first_day_of_month := p_first_day_of_month;
692 l_mo_num := p_mo_num;
693 l_yr_num := p_yr_num;
694 else
695 -- if premium is prospective, process with next month's date.
696 l_last_day_of_month := add_months(p_effective_date,1);
697 l_first_day_of_month := add_months(p_first_day_of_month,1);
698 l_mo_num := to_char(l_last_day_of_month,'mm');
699 l_yr_num := to_char(l_last_day_of_month,'YYYY');
700 end if;
701 l_last_effective_dt := last_day(l_last_day_of_month) ;
702
703 -- Find total number of prtt for this premium.
704 hr_utility.set_location ('pl_id:'||to_char(l_prems.pl_id)||
705 ' oipl_id:'||to_char(l_prems.oipl_id)||' washrule:'||
706 to_char(l_prems.wsh_rl_dy_mo_num)||' last:'||to_char(l_last_day_of_month)||
707 ' first:'||to_char(l_first_day_of_month),1);
708
709 open c_results
710 (p_first_day_of_month => l_first_day_of_month
711 ,p_last_day_of_month => l_last_day_of_month
712 ,p_wsh_rl_dy_mo_num => l_prems.wsh_rl_dy_mo_num
713 ,p_pl_id => l_prems.pl_id
714 ,p_oipl_id => l_prems.oipl_id) ;
715 fetch c_results into l_results;
716 close c_results;
717 if l_results.num_of_prtts = 0 then
718 -- c_results%notfound is irrelevant when cursor uses a 'sum'
719 -- if no people, skip all the vrbl prfl stuff and write a zero value premium
720 l_val := 0;
721 else
722 hr_utility.set_location ('total results:'||to_char(l_results.num_of_prtts)||
723 ' tot cvg:'||to_char(l_results.total_cvg),14);
724
725 ben_prem_pl_oipl_monthly.get_comp_object_info
726 (p_oipl_id => l_prems.oipl_id
727 ,p_pl_id => l_prems.pl_id
728 ,p_pgm_id => null
729 ,p_effective_date => p_effective_date
730 ,p_out_pgm_id => l_pgm_id
731 ,p_out_pl_typ_id => l_pl_typ_id
732 ,p_out_pl_id => l_pl_id
733 ,p_out_opt_id => l_opt_id);
734
735 -- Determine if there are any variable profiles matching and if so,
736 -- compute the premium values.
737 l_matched_vrbl_prfl := 'N';
738 determine_vrbl_prfls (p_actl_prem_id => l_prems.actl_prem_id
739 ,p_business_group_id => p_business_group_id
740 ,p_effective_date => p_effective_date
741 ,p_first_day_of_month => l_first_day_of_month
742 ,p_last_day_of_month => l_last_day_of_month
743 ,p_pl_id => l_prems.pl_id
744 ,p_oipl_id => l_prems.oipl_id
745 ,p_pl_typ_id => l_pl_typ_id
746 ,p_pl2_id => l_pl_id
747 ,p_opt_id => l_opt_id
748 ,p_wsh_rl_dy_mo_num => l_prems.wsh_rl_dy_mo_num
749 ,p_rndg_cd => l_prems.rndg_cd
750 ,p_rndg_rl => l_prems.rndg_rl
751 ,p_num_of_prtts => l_results.num_of_prtts
752 ,p_total_cvg => l_results.total_cvg
753 ,p_actl_prem_val => l_prems.val
754 ,p_bnft_rt_typ_cd => l_prems.bnft_rt_typ_cd
755 ,p_mlt_cd => l_prems.mlt_cd
756 ,p_vrbl_rt_add_on_calc_rl => l_prems.vrbl_rt_add_on_calc_rl
757 ,p_val => l_val
758 ,p_matched_vrbl_prfl => l_matched_vrbl_prfl);
759 if l_matched_vrbl_prfl = 'N' then
760 hr_utility.set_location ('l_matched_vrbl_prfl = N',16);
761 -- compute total premium based on number of participants or
762 -- total coverage amt of prtts.
763 if l_prems.mlt_cd = 'NSVU' then
764 if l_prems.vrbl_rt_add_on_calc_rl is null then
765 -- there is no standard value and no profiles matched, error.
766 fnd_message.set_name('BEN', 'BEN_92290_NSVU_NO_PROFILES');
767 fnd_message.raise_error;
768 else
769 -- this rule returns an amount.
770 l_outputs := benutils.formula
771 (p_formula_id => l_prems.vrbl_rt_add_on_calc_rl,
772 p_effective_date => p_effective_date,
773 p_business_group_id => p_business_group_id,
774 p_assignment_id => null, -- we are not processing a single
775 p_organization_id => null, -- person, but a group.
776 p_pgm_id => null, -- and we don't know the pgm.
777 p_pl_id => l_prems.pl_id,
778 p_pl_typ_id => l_pl_typ_id,
779 p_opt_id => l_opt_id,
780 p_ler_id => null,
781 p_jurisdiction_code => null);
782 l_val := l_outputs(l_outputs.first).value;
783 end if;
784 elsif l_prems.mlt_cd = 'TTLPRTT' then
785 benutils.rt_typ_calc
786 (p_rt_typ_cd => l_prems.bnft_rt_typ_cd
787 ,p_val => l_prems.val
788 ,p_val_2 => l_results.num_of_prtts
789 ,p_calculated_val => l_val);
790 else -- l_prems.mlt_cd = 'TTLCVG'
791 benutils.rt_typ_calc
792 (p_rt_typ_cd => l_prems.bnft_rt_typ_cd
793 ,p_val => l_prems.val
794 ,p_val_2 => l_results.total_cvg
795 ,p_calculated_val => l_val);
796 end if;
797 -- round against actl_prem
798 l_val := benutils.do_rounding
799 (p_rounding_cd => l_prems.rndg_cd
800 ,p_rounding_rl => l_prems.rndg_rl
801 ,p_value => l_val
802 ,p_effective_date => p_effective_date);
803 end if;
804
805 hr_utility.set_location('Premium Limits Checking',20);
806 benutils.limit_checks
807 (p_upr_lmt_val => l_prems.upr_lmt_val,
808 p_lwr_lmt_val => l_prems.lwr_lmt_val,
809 p_upr_lmt_calc_rl => l_prems.upr_lmt_calc_rl,
810 p_lwr_lmt_calc_rl => l_prems.lwr_lmt_calc_rl,
811 p_effective_date => p_effective_date,
812 p_business_group_id => p_business_group_id,
813 p_assignment_id => null, -- we are not processing a single
814 p_organization_id => null, -- person, but a group.
815 p_pgm_id => null, -- and we don't know the pgm.
816 p_pl_id => l_pl_id,
817 p_pl_typ_id => l_pl_typ_id,
818 p_opt_id => l_opt_id,
819 p_ler_id => null,
820 p_state => null,
821 p_val => l_val);
822
823
824 -- l_val should be the total premium to be written to pl_r_oipl_prem_by_mo
825
826
827 if l_prems.prem_asnmt_lvl_cd = 'PRTTNPLOIPL' then
828 -- allocate premium to participants
829 -- l_prtt_val should be the total prem divided by number of prtts,
830 -- and written to prtt_prem_by_mo.
831 -- compute Per participant value
832 l_prtt_val := l_val / l_results.num_of_prtts;
833
834 -- Task 416, July 99 : balance individual prem to total prem.
835 -- One person's premium may be more than the others to compenstate.
836 -- round individual prem against actl_prem rounding code
837 l_prtt_val := benutils.do_rounding
838 (p_rounding_cd => l_prems.rndg_cd
839 ,p_rounding_rl => l_prems.rndg_rl
840 ,p_value => l_prtt_val
841 ,p_effective_date => p_effective_date);
842 l_balance_val := l_val - (l_prtt_val * l_results.num_of_prtts);
843 l_reg_prtt_val := l_prtt_val;
844 l_prtt_val := l_prtt_val + l_balance_val;
845
846
847 -- looping thru results matching actl_prem's pl and oipl
848 for l_each_result in c_each_result
849 (p_first_day_of_month => l_first_day_of_month
850 ,p_last_day_of_month => l_last_day_of_month
851 ,p_wsh_rl_dy_mo_num => l_prems.wsh_rl_dy_mo_num
852 ,p_pl_id => l_prems.pl_id
853 ,p_oipl_id => l_prems.oipl_id) loop
854 -- for each result find a prtt prem row matching the actl_prem_id.
855 -- If it doesn't exist, create one.
856 hr_utility.set_location ('looping c_each_result',28);
857 open c_ppe(p_prtt_enrt_rslt_id => l_each_result.prtt_enrt_rslt_id
858 ,p_actl_prem_id => l_prems.actl_prem_id);
859 fetch c_ppe into l_ppe;
860 if c_ppe%notfound or c_ppe%notfound is null then
861 ben_prtt_prem_api.create_prtt_prem
862 (p_prtt_prem_id => l_ppe.prtt_prem_id
863 ,p_effective_start_date => l_effective_start_date
864 ,p_effective_end_date => l_effective_end_date
865 ,p_std_prem_uom => l_prems.uom
866 ,p_std_prem_val => l_prtt_val
867 ,p_actl_prem_id => l_prems.actl_prem_id
868 ,p_prtt_enrt_rslt_id => l_each_result.prtt_enrt_rslt_id
869 ,p_business_group_id => p_business_group_id
870 ,p_object_version_number => l_ovn
871 ,p_request_id => fnd_global.conc_request_id
872 ,p_program_application_id => fnd_global.prog_appl_id
873 ,p_program_id => fnd_global.conc_program_id
874 ,p_program_update_date => sysdate
875 /* CODE PRIOR TO WWBUG: 1646442
876 ,p_effective_date => p_effective_date);
877 */
878 /* Start of Changes for WWBUG: 1646442 */
879 ,p_effective_date => l_each_result.enrt_cvg_strt_dt);
880 /* End of Changes for WWBUG: 1646442 */
881 l_ppe.std_prem_uom := l_prems.uom;
882 end if;
883 close c_ppe;
884 hr_utility.set_location ('write prtt premium by mo. val:'||
885 to_char(l_prtt_val),31);
886 open c_prm(p_prtt_prem_id => l_ppe.prtt_prem_id
887 ,p_mo_num => l_mo_num
888 ,p_yr_num => l_yr_num);
889 fetch c_prm into l_prm;
890 if c_prm%notfound or c_prm%notfound is null then
891 -- bug 2784213
892 l_effective_date_mo := last_day(to_date(l_yr_num||lpad(l_mo_num,2,0),'YYYYMM'));
893 --
894 ben_prtt_prem_by_mo_api.create_prtt_prem_by_mo
895 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
896 ,p_effective_start_date => l_effective_start_date
897 ,p_effective_end_date => l_effective_end_date
898 ,p_mnl_adj_flag => 'N'
899 ,p_mo_num => l_mo_num
900 ,p_yr_num => l_yr_num
901 ,p_antcpd_prtt_cntr_uom => null
902 ,p_antcpd_prtt_cntr_val => null
903 ,p_val => l_prtt_val
904 ,p_cr_val => null
905 ,p_cr_mnl_adj_flag => 'N'
906 ,p_alctd_val_flag => 'Y'
907 ,p_uom => l_ppe.std_prem_uom -- uom from prtt_prem if exists
908 ,p_prtt_prem_id => l_ppe.prtt_prem_id
909 ,p_cost_allocation_keyflex_id => l_prems.cost_allocation_keyflex_id
910 ,p_business_group_id => p_business_group_id
911 ,p_object_version_number => l_prm.object_version_number
912 ,p_request_id => fnd_global.conc_request_id
913 ,p_program_application_id => fnd_global.prog_appl_id
914 ,p_program_id => fnd_global.conc_program_id
915 ,p_program_update_date => sysdate
916 ,p_effective_date => l_effective_date_mo); --p_effective_date);
917 else
918
919 -- get the net value
920 open c_prm_ovn (p_prtt_prem_id => l_ppe.prtt_prem_id
921 ,p_mo_num => l_mo_num
922 ,p_yr_num => l_yr_num
923 ,p_effective_dt => l_last_effective_dt );
924 fetch c_prm_ovn into l_prm ;
925 close c_prm_ovn ;
926 --
927 if l_prm.mnl_adj_flag = 'N' then
928 if l_prm.cr_val> 0 and l_prtt_val > 0 then
929
930 hr_utility.set_location ('update the premium:'|| l_prm.prtt_prem_by_mo_id, 10) ;
931
932 ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
933 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
934 ,p_effective_start_date => l_effective_start_date
935 ,p_effective_end_date => l_effective_end_date
936 ,p_mnl_adj_flag => 'N'
937 ,p_val => l_prtt_val
938 ,p_cr_val => null
939 ,p_alctd_val_flag => 'Y'
940 ,p_uom => l_ppe.std_prem_uom -- uom from prtt_prem if exists
941 ,p_cost_allocation_keyflex_id => l_prems.cost_allocation_keyflex_id
942 ,p_object_version_number => l_prm.object_version_number
943 ,p_request_id => fnd_global.conc_request_id
944 ,p_program_application_id => fnd_global.prog_appl_id
945 ,p_program_id => fnd_global.conc_program_id
946 ,p_program_update_date => sysdate
947 ,p_effective_date => l_last_effective_dt
948 ,p_datetrack_mode => hr_api.g_correction);
949
950 else
951
952 ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
953 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
954 ,p_effective_start_date => l_effective_start_date
955 ,p_effective_end_date => l_effective_end_date
956 ,p_mnl_adj_flag => 'N'
957 ,p_val => l_prtt_val
958 ,p_alctd_val_flag => 'Y'
959 ,p_uom => l_ppe.std_prem_uom -- uom from prtt_prem if exists
960 ,p_cost_allocation_keyflex_id => l_prems.cost_allocation_keyflex_id
961 ,p_object_version_number => l_prm.object_version_number
962 ,p_request_id => fnd_global.conc_request_id
963 ,p_program_application_id => fnd_global.prog_appl_id
964 ,p_program_id => fnd_global.conc_program_id
965 ,p_program_update_date => sysdate
966 ,p_effective_date => l_last_effective_dt
967 ,p_datetrack_mode => hr_api.g_correction);
968 end if ;
969 end if ;
970 end if;
971 close c_prm;
972 -- write info to reporting table
973 -- if we are processing this month for retrospective or next
974 -- month for prospective, the report considers this 'current month'.
975 g_rec.rep_typ_cd := 'PRPPOIPL';
976 g_rec.person_id := l_each_result.person_id;
977 g_rec.pgm_id := l_each_result.pgm_id;
978 g_rec.pl_id := l_each_result.pl_id;
979 g_rec.oipl_id := l_each_result.oipl_id;
980 g_rec.pl_typ_id := l_each_result.pl_typ_id;
981 g_rec.actl_prem_id := l_prems.actl_prem_id;
982 g_rec.val := l_prtt_val;
983 g_rec.mo_num := l_mo_num;
984 g_rec.yr_num := l_yr_num;
985
986 benutils.write(p_rec => g_rec);
987
988 -- Task 416: set individual prem back to regular value for the rest
989 -- of the prtts.
990 l_prtt_val := l_reg_prtt_val;
991
992 end loop; -- looping thru results matching actl_prem's pl and oipl
993 end if;
994 end if; -- if found participants enrolled (c_results)
995
996 hr_utility.set_location ('write costing 2 ',40);
997 -- first insert into cost allocation keyflex ??
998 if l_prems.prem_asnmt_lvl_cd = 'PLOIPL' then
999 -- premiums were not allocated to prtts, cost at the pl/oipl level.
1000 -- Costing a PROC premium is simply pointing to the same cak id
1001 -- as the actl_prem record. We use prem-cstg-by-sgmt only for
1002 -- ENRT premiums (benprprm.pkb).
1003 l_cak := l_prems.cost_allocation_keyflex_id;
1004 else l_cak := null;
1005 end if;
1006
1007 hr_utility.set_location ('write premium by mo. val:'||to_char(l_val),41);
1008 open c_pbm(p_actl_prem_id => l_prems.actl_prem_id
1009 ,p_mo_num => l_mo_num
1010 ,p_yr_num => l_yr_num);
1011 fetch c_pbm into l_pbm;
1012 if c_pbm%notfound or c_pbm%notfound is null then
1013 -- bug 2784213
1014 l_effective_date_mo := last_day(to_date(l_yr_num||lpad(l_mo_num,2,0),'YYYYMM'));
1015 --
1016 ben_pl_r_oipl_prem_by_mo_api.create_pl_r_oipl_prem_by_mo
1017 (p_pl_r_oipl_prem_by_mo_id => l_pbm.pl_r_oipl_prem_by_mo_id
1018 ,p_effective_start_date => l_effective_start_date
1019 ,p_effective_end_date => l_effective_end_date
1020 ,p_mnl_adj_flag => 'N'
1021 ,p_mo_num => l_mo_num
1022 ,p_yr_num => l_yr_num
1023 ,p_val => l_val
1024 ,p_uom => l_prems.uom -- uom from actl_prem
1025 ,p_prtts_num => l_results.num_of_prtts
1026 ,p_actl_prem_id => l_prems.actl_prem_id
1027 ,p_cost_allocation_keyflex_id => l_cak
1028 ,p_business_group_id => p_business_group_id
1029 ,p_object_version_number => l_ovn
1030 ,p_request_id => fnd_global.conc_request_id
1031 ,p_program_application_id => fnd_global.prog_appl_id
1032 ,p_program_id => fnd_global.conc_program_id
1033 ,p_program_update_date => sysdate
1034 ,p_effective_date => l_effective_date_mo ); -- p_effective_date);
1035 else
1036 --
1037 l_effective_date_mo := last_day(to_date(l_yr_num||lpad(l_mo_num,2,0),'YYYYMM'));
1038 ben_pl_r_oipl_prem_by_mo_api.update_pl_r_oipl_prem_by_mo
1039 (p_pl_r_oipl_prem_by_mo_id => l_pbm.pl_r_oipl_prem_by_mo_id
1040 ,p_effective_start_date => l_effective_start_date
1041 ,p_effective_end_date => l_effective_end_date
1042 ,p_mnl_adj_flag => 'N'
1043 ,p_val => l_val
1044 ,p_uom => l_prems.uom -- uom from actl_prem
1045 ,p_prtts_num => l_results.num_of_prtts
1046 ,p_cost_allocation_keyflex_id => l_cak
1047 ,p_object_version_number => l_pbm.object_version_number
1048 ,p_request_id => fnd_global.conc_request_id
1049 ,p_program_application_id => fnd_global.prog_appl_id
1050 ,p_program_id => fnd_global.conc_program_id
1051 ,p_program_update_date => sysdate
1052 ,p_effective_date => l_effective_date_mo -- p_effective_date
1053 ,p_datetrack_mode => hr_api.g_correction);
1054 end if;
1055 close c_pbm;
1056 -- write info to reporting table
1057 g_rec.rep_typ_cd := 'PRPLOIPL';
1058 g_rec.person_id := null;
1059 g_rec.pgm_id := null; -- ??l_pgm_id;
1060 g_rec.pl_id := l_pl_id;
1061 g_rec.oipl_id := l_prems.oipl_id;
1062 g_rec.pl_typ_id := l_pl_typ_id;
1063 g_rec.actl_prem_id := l_prems.actl_prem_id;
1064 g_rec.val := l_val;
1065 g_rec.mo_num := l_mo_num;
1066 g_rec.yr_num := l_yr_num;
1067
1068 benutils.write(p_rec => g_rec);
1069
1070 If (p_validate = 'Y') then
1071 Rollback to process_pl_premium_savepoint;
1072 End if;
1073
1074 hr_utility.set_location ('Leaving '||l_package,99);
1075 exception
1076 when others then
1077 l_error_text := sqlerrm;
1078 hr_utility.set_location ('Fail in '||l_package||' error:',999);
1079 hr_utility.set_location (l_error_text,999);
1080 fnd_message.raise_error;
1081 end main;
1082 end ben_prem_pl_oipl_monthly;
[Home] [Help]