1 package body ben_prem_prtt_monthly as
2 /* $Header: benprprm.pkb 120.2.12010000.3 2008/10/20 05:30:22 sallumwa ship $ */
3 /*
4 ================================================================================
5 | Copyright (c) 1997 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 ================================================================================
9
10 Name
11 Premium Participant Monthly
12 Purpose
13 This package is used to calculate participant monthly premiums.
14 History
15 Date Who Version What?
16 ---- --- ------- -----
17 02 Jun 99 lmcdonal 115.0 Created.
18 06 Jul 99 lmcdonal 115.1 Added cost-allocation writing.
19 Added reporting.
20 09 Jul 99 jcarpent 115.2 Added checks for backed out nocopy pil
21 19 Jul 99 lmcdonal 115.3 Task 418. Check upper and lower
22 limits on partial month values.
23 Execute rules. genutils to benutils.
24 27 Jul 99 lmcdonal 115.4 Allow prtl_mo_rt_prtn_val from and
25 to dy_mo_num's to be null.
26 05 Aug 99 lmcdonal 115.5 Allow strt_r_stp_cd to be ETHR.
27 06 Aug 99 lmcdonal 115.6 Better set locations.
28 19 Aug 99 lmcdonal 115.7 Add premium_warning.
29 01 Oct 99 jcarpent 115.8 Changed compute_partial_mo to
30 call benelmen.prorate_amount
31 03 Nov 99 lmcdonal 115.9 region_2 was defined as number,
32 should be char.
33 08 Nov 99 lmcdonal 115.10 cleanup some comments.
34 15 Feb 00 lmcdonal 115.11 clear out nocopy l_opt if not loaded
35 from cursor.
36 08 May 00 lmcdonal 115.12 Bug 1277372, don't create monthly
37 premium record for prior months
38 if result was not created this
39 month.
40 23 Jun 00 jcarpent 115.13 Bug 5322, back out nocopy of prev fix
41 to version from 115.11 since
42 115.12 did not fix bug 5127/
43 1277372 anyway, and messed up
44 prior functionality.
45 25 Jul 00 pbodla 115.14 - Bug 5127 When premium process
46 is rerun,If manual adjustement flag
47 is Y, Do not revert back to the
48 standard premium value
49 27-aug-01 tilak 115.15 bug:1949361 jurisdiction code is
50 derived inside benutils.formula.
51 31-aug-01 tilak 115.16 1970990, update_prtt_prem_by_mo is
52 called only when there is a changes
53 in uom or val
54 04-aug-01 tilak 115.17 cost_allocation_keyflex_id added in
55 the condition to call update_prtt_prem_by_mo
56 13-mar-02 ikasire 115.18 UTF8 changes
57 14-mar-02 ikasire 115.19 GSCC errors
58 08-Jun-02 pabodla 115.20 Do not select the contingent worker
59 assignment when assignment data is
60 fetched.
61 30-Dec-02 mmudigon 115.21 NOCOPY
62 21-feb-03 vsethi 115.22 Bug 2784213. Premium records should be
63 created with effective date of end of
64 every month and not process date
65 30-Jan-04 ikasire 115.23 Bug3379060 Proration doesnot work if
66 the coverage starts on first on a
67 Month
68 12-Jul-04 tjesumic 115.24 NONE code calcualtion is changed
69 if the start and end mont is not partial , partiam_mo is not
70 called. bug 3742713
71 07-Sep-04 tjesumic 115.25 charges created when credit and debit exisit for a month
72 and credit is no more valid# 3879156
73 07-Sep-04 tjesumic 115.26 # 3879156
74 08-Sep-04 tjesumic 115.27 # 3666347 where to end the calucaltion logic changed
75 14-Sep-04 tjesumic 115.28 # 3666347 the lookback period added to end the calcualtion
76 14-Sep-04 tjesumic 115.29 # 3666347 where to end the calucaltion validated the premium start date
77 instead of effective end date. OSB may not have date tracked result but prem
78 22-Mar-05 tjesumic 115.30 # 4222031 Whne a plan start and end on the same month and wash rule is
79 defined , the end date is used for premium computation
80 21-jun-2005 tjesumic 115.31 round of the date to chnged to trunc to find the first date of the month
81 20-Dec-05 abparekh 115.32 Bug 4892354 : In procedure compute_prem get valid update modes before
82 updating PRM record
83 22-Feb-08 rtagarra 115.33 Bug 6840074
84 20-Oct-08 sallumwa 115.34 Bug 7414822 : Do not write into ben_reporting table when the coverage for
85 the same is end-dated.
86
87 */
88 --------------------------------------------------------------------------------
89 g_package varchar2(80) := 'ben_prem_prtt_monthly';
90 -- ----------------------------------------------------------------------------
91 -- |------------------------< get_rule_data >----------------------------|
92 -- ----------------------------------------------------------------------------
93 -- Procedure used to get data needed when calling fast formula.
94 procedure get_rule_data(p_person_id in number
95 ,p_business_group_id in number
96 ,p_effective_date in date
97 ,p_assignment_id out nocopy number
98 ,p_location_id out nocopy number
99 ,p_organization_id out nocopy number
100 ,p_region_2 out nocopy varchar2
101 ,p_jurisdiction out nocopy varchar2)is
102 l_package varchar2(80) := g_package||'.get_rule_data';
103
104 cursor csr_asg is
105 select asg.assignment_id, asg.organization_id, loc.region_2, asg.location_id
106 from hr_locations_all loc, per_assignments_f asg
107 where asg.person_id = p_person_id
108 and asg.primary_flag = 'Y'
109 and asg.assignment_type <> 'C'
110 and loc.location_id(+) = asg.location_id
111 and asg.business_group_id+0 = p_business_group_id
112 and p_effective_date between
113 asg.effective_start_date and asg.effective_end_date
114 order by 1;
115
116 l_jurisdiction PAY_CA_EMP_PROV_TAX_INFO_F.JURISDICTION_CODE%type := null;
117
118 begin
119 hr_utility.set_location ('Entering '||l_package,10);
120 open csr_asg;
121 fetch csr_asg into p_assignment_id, p_organization_id,
122 p_region_2, p_location_id;
123 if csr_asg%NOTFOUND or csr_asg%NOTFOUND is null then
124 p_assignment_id := null;
125 p_organization_id := null;
126 p_region_2 := null;
127 end if;
128 close csr_asg;
129 --if p_region_2 is not null then
130 -- p_jurisdiction := pay_mag_utils.lookup_jurisdiction_code
131 -- (p_state => p_region_2);
132 --else
133 p_jurisdiction := null;
134 --end if;
135 hr_utility.set_location ('Leaving '||l_package,99);
136 end get_rule_data;
137
138 -- ----------------------------------------------------------------------------
139 -- |------------------------< determine_costing >----------------------------|
140 -- ----------------------------------------------------------------------------
141 -- Procedure used to compute and write costing info from actl_prem to
142 -- cost_allocation_keyflex.
143 procedure determine_costing
144 (p_actl_prem_id in number
145 ,p_effective_date in date
146 ,p_business_group_id in number
147 ,p_person_id in number
148 ,p_cak_id out nocopy number) is
149 --
150 l_package varchar2(80) := g_package||'.determine_costing';
151 l_error_text varchar2(200) := null;
152 --
153
154 cursor csr_cost_id is
155 select pbg.cost_allocation_structure
156 from per_business_groups pbg
157 where pbg.business_group_id+0 = p_business_group_id;
158 l_cost_id fnd_id_flex_segments.id_flex_num%TYPE;
159
160 cursor csr_apr_cak is
161 select segment1, segment2, segment3, segment4, segment5, segment6,
162 segment7, segment8, segment9, segment10, segment11, segment12,
163 segment13, segment14, segment15, segment16, segment17, segment18,
164 segment19, segment20, segment21, segment22, segment23, segment24,
165 segment25, segment26, segment27, segment28, segment29, segment30
166 from pay_cost_allocation_keyflex cak, ben_actl_prem_f apr
167 where apr.actl_prem_id = p_actl_prem_id
168 and apr.cost_allocation_keyflex_id = cak.cost_allocation_keyflex_id
169 and apr.business_group_id+0 = p_business_group_id
170 and p_effective_date between
171 nvl(cak.start_date_active, p_effective_date)
172 and nvl(cak.end_date_active, p_effective_date)
173 and cak.enabled_flag = 'Y'
174 and p_effective_date between
175 apr.effective_start_date and apr.effective_end_date;
176 --l_apr_cak c_apr_cak%rowtype;
177 l_apr_cak g_apr_cak_table;
178
179 /* type g_apr_cak_rec is record
180 (segment varchar2(60));
181
182 type g_apr_cak_table is table of g_apr_cak_rec
183 index by binary_integer;
184 */
185
186 cursor csr_cbs is
187 select cbs.sgmt_num, cbs.sgmt_cstg_mthd_cd, cbs.sgmt_cstg_mthd_rl
188 from ben_prem_cstg_by_sgmt_f cbs
189 where cbs.actl_prem_id = p_actl_prem_id
190 and cbs.business_group_id+0 = p_business_group_id
191 and p_effective_date between
192 cbs.effective_start_date and cbs.effective_end_date
193 order by 1;
194 --l_cbs c_cbs%rowtype;
195
196 cursor csr_asg is
197 select asg.assignment_id, asg.organization_id, loc.region_2, asg.location_id
198 from hr_locations_all loc, per_assignments_f asg
199 where asg.person_id = p_person_id
200 and asg.assignment_type <> 'C'
201 and asg.primary_flag = 'Y'
202 and loc.location_id(+) = asg.location_id
203 and asg.business_group_id+0 = p_business_group_id
204 and p_effective_date between
205 asg.effective_start_date and asg.effective_end_date
206 order by 1;
207 l_asg csr_asg%rowtype;
208
209 l_effective_date date;
210 l_session_id number;
211 l_segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
212 l_cnt number;
213 l_cnt2 number;
214 l_outputs ff_exec.outputs_t;
215
216 begin
217 hr_utility.set_location ('Entering '||l_package,10);
218 l_effective_date := trunc(p_effective_date);
219 --
220 --
221 -- Look for cost allocation definition
222 open csr_cost_id;
223 fetch csr_cost_id into l_cost_id;
224 if csr_cost_id%FOUND then
225 hr_utility.set_location(l_package, 27);
226
227 -- get the actl-prem cost-allocation info to copy to the prtt-prem cost-allocation.
228 open csr_apr_cak;
229 fetch csr_apr_cak into l_apr_cak(1).sgmt, l_apr_cak(2).sgmt,
230 l_apr_cak(3).sgmt, l_apr_cak(4).sgmt,
231 l_apr_cak(5).sgmt, l_apr_cak(6).sgmt, l_apr_cak(7).sgmt,
232 l_apr_cak(8).sgmt, l_apr_cak(9).sgmt,
233 l_apr_cak(10).sgmt, l_apr_cak(11).sgmt, l_apr_cak(12).sgmt,
234 l_apr_cak(13).sgmt, l_apr_cak(14).sgmt,
235 l_apr_cak(15).sgmt, l_apr_cak(16).sgmt, l_apr_cak(17).sgmt,
236 l_apr_cak(18).sgmt, l_apr_cak(19).sgmt,
237 l_apr_cak(20).sgmt, l_apr_cak(21).sgmt, l_apr_cak(22).sgmt,
238 l_apr_cak(23).sgmt, l_apr_cak(24).sgmt,
239 l_apr_cak(25).sgmt, l_apr_cak(26).sgmt, l_apr_cak(27).sgmt,
240 l_apr_cak(28).sgmt, l_apr_cak(29).sgmt,
241 l_apr_cak(30).sgmt;
242 if csr_apr_cak%FOUND then
243 hr_utility.set_location(l_package, 29);
244
245 -- check for overrides to the actl-prem cost-allocation info, stored in
246 -- prem-cstg-by-sgmt.
247 open csr_asg;
248 fetch csr_asg into l_asg;
249 if csr_asg%FOUND then
250 -- if we find an assignment we can override the values in the actl-prem
251 -- cost allocation. if not, use all the values from actl-prem.
252 l_cnt := 1;
253 for l_cbs in csr_cbs loop
254 if l_cbs.sgmt_num > 30 or l_cbs.sgmt_num < 1 or l_cbs.sgmt_num is null then
255 fnd_message.set_name('BEN', 'BEN_92247_INVALID_SGMT_NUM');
256 fnd_message.raise_error;
257 end if;
258 for crt in l_cnt..30 loop
259 if l_cbs.sgmt_num = crt then
260 if l_cbs.sgmt_cstg_mthd_cd = 'UOFA' then
261 -- use org from assignment
262 l_apr_cak(crt).sgmt := l_asg.organization_id;
263 elsif l_cbs.sgmt_cstg_mthd_cd = 'ULFA' then
264 -- use loc from assignment
265 l_apr_cak(crt).sgmt := l_asg.location_id;
266 elsif l_cbs.sgmt_cstg_mthd_cd = 'UCCFA' then
267 -- use cost center from assignment ??
268 l_apr_cak(crt).sgmt := null; --l_asg.location_id;
269 elsif l_cbs.sgmt_cstg_mthd_cd = 'RL' then
270 -- use rule ??
271 /* l_outputs := benutils.formula
272 (p_formula_id => l_cbs.sgmt_cstg_mthd_rl,
273 p_effective_date => p_effective_date,
274 p_business_group_id => p_business_group_id,
275 p_assignment_id => l_asg.assignment_id,
276 p_organization_id => l_asg.organization_id,
277 p_pgm_id => l_epe.pgm_id,
278 p_pl_id => l_epe.pl_id,
279 p_pl_typ_id => l_epe.pl_typ_id,
280 p_opt_id => l_opt.opt_id,
281 p_ler_id => l_epe.ler_id,
282 p_jurisdiction_code => pay_mag_utils.lookup_jurisdiction_code
283 (p_state => l_state.region_2)
284 );
285 p_val := l_outputs(l_outputs.first).value;
286 */
287 null;
288 end if;
289 l_cnt2 := crt + 1;
290 exit;
291 end if;
292 end loop;
293 l_cnt := l_cnt2;
294 end loop;
295 end if;
296 close csr_asg;
297
298 hr_utility.set_location(l_package, 31);
299
300 hr_kflex_utility.ins_or_sel_keyflex_comb
301 (p_appl_short_name => 'PAY'
302 ,p_flex_code => 'COST'
303 ,p_flex_num => l_cost_id
304 ,p_segment1 => l_apr_cak(1).sgmt
305 ,p_segment2 => l_apr_cak(2).sgmt
306 ,p_segment3 => l_apr_cak(3).sgmt
307 ,p_segment4 => l_apr_cak(4).sgmt
308 ,p_segment5 => l_apr_cak(5).sgmt
309 ,p_segment6 => l_apr_cak(6).sgmt
310 ,p_segment7 => l_apr_cak(7).sgmt
311 ,p_segment8 => l_apr_cak(8).sgmt
312 ,p_segment9 => l_apr_cak(9).sgmt
313 ,p_segment10 => l_apr_cak(10).sgmt
314 ,p_segment11 => l_apr_cak(11).sgmt
315 ,p_segment12 => l_apr_cak(12).sgmt
316 ,p_segment13 => l_apr_cak(13).sgmt
317 ,p_segment14 => l_apr_cak(14).sgmt
318 ,p_segment15 => l_apr_cak(15).sgmt
319 ,p_segment16 => l_apr_cak(16).sgmt
320 ,p_segment17 => l_apr_cak(17).sgmt
321 ,p_segment18 => l_apr_cak(18).sgmt
322 ,p_segment19 => l_apr_cak(19).sgmt
323 ,p_segment20 => l_apr_cak(20).sgmt
324 ,p_segment21 => l_apr_cak(21).sgmt
325 ,p_segment22 => l_apr_cak(22).sgmt
326 ,p_segment23 => l_apr_cak(23).sgmt
327 ,p_segment24 => l_apr_cak(24).sgmt
328 ,p_segment25 => l_apr_cak(25).sgmt
329 ,p_segment26 => l_apr_cak(26).sgmt
330 ,p_segment27 => l_apr_cak(27).sgmt
331 ,p_segment28 => l_apr_cak(28).sgmt
332 ,p_segment29 => l_apr_cak(29).sgmt
333 ,p_segment30 => l_apr_cak(30).sgmt
334 ,p_concat_segments_in => null
335 ,p_ccid => p_cak_id -- out
336 ,p_concat_segments_out => l_segments -- out
337 );
338
339 hr_utility.set_location(l_package, 35);
340 end if;
341 close csr_apr_cak;
342
343 end if;
344 close csr_cost_id;
345
346 hr_utility.set_location ('Leaving '||l_package,99);
347 exception
348 when others then
349 l_error_text := sqlerrm;
350 hr_utility.set_location ('Fail in '||l_package,999);
351 hr_utility.set_location('Error:'||l_error_text,999);
352 fnd_message.raise_error;
353 end determine_costing;
354
355 -- ----------------------------------------------------------------------------
356 -- |------------------------< premium_warning >----------------------------|
357 -- ----------------------------------------------------------------------------
358 -- Procedure used to create warning messages for premiums.
359 procedure premium_warning
360 (p_person_id in number default null
361 ,p_prtt_enrt_rslt_id in number
362 ,p_effective_start_date in date
363 ,p_effective_date in date
364 ,p_warning in varchar2)is
365 l_package varchar2(80) := g_package||'.premium_warning';
366
367 cursor c_person (p_person_id number) is
368 select full_name from per_people_f
369 where person_id = p_person_id
370 and p_effective_date between effective_start_date
371 and effective_end_date;
372 l_full_name per_all_people_f.full_name%TYPE := ''; -- UTF8 varchar2(240) := '';
373
374 cursor c_premium (p_prtt_enrt_rslt_id number,
375 p_effective_start_date date, p_effective_date date) is
376 select distinct 'Y'
377 from ben_prtt_prem_by_mo_f prm, ben_prtt_prem_f ppe
378 where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
379 and ppe.prtt_prem_id = prm.prtt_prem_id
380 -- any premiums between esd of result and date we are voided it
381 and to_date(to_char(prm.mo_num)||'-'||to_char(prm.yr_num), 'mm-yyyy')
382 between p_effective_start_date and p_effective_date
383 and p_effective_date between ppe.effective_start_date
384 and ppe.effective_end_date
385 and p_effective_date between prm.effective_start_date
386 and prm.effective_end_date;
387 l_premiums_exist varchar2(1) := 'N';
388 l_message fnd_new_messages.message_name%type := 'BEN_92320_INVALID_WARNING';
389 begin
390 hr_utility.set_location ('Entering '||l_package,10);
391
392 -- write warning messages if a premium exists during the time that
393 -- the result was created thru to the time that we are doing something
394 -- in correction mode.
395 open c_premium(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
396 p_effective_start_date =>
397 to_date(to_char(p_effective_start_date, 'mm-yyyy'), 'mm-yyyy'),
398 p_effective_date => p_effective_date);
399 fetch c_premium into l_premiums_exist;
400 if c_premium%FOUND then
401 if p_person_id is not null then
402 open c_person(p_person_id => p_person_id);
403 fetch c_person into l_full_name;
404 close c_person;
405 end if;
406
407 if p_warning = 'VOID' then
408 l_message := 'BEN_92316_VOID_CORR_OLD';
409 elsif p_warning = 'SUSPEND' then
410 l_message := 'BEN_92315_SUS_CORR_OLD';
411 elsif p_warning = 'UNSUSPEND' then
412 l_message := 'BEN_92314_UNSUS_CORR_OLD';
413 end if;
414
415 ben_warnings.load_warning
416 (p_application_short_name => 'BEN',
417 p_message_name => l_message,
418 p_parma => l_full_name,
419 p_person_id => p_person_id);
420
421 end if;
422 close c_premium;
423
424 hr_utility.set_location ('Leaving '||l_package,99);
425 end premium_warning;
426
427 -- ----------------------------------------------------------------------------
428 -- |------------------------< compute_partial_mo >----------------------------|
429 -- ----------------------------------------------------------------------------
430 -- Procedure used to compute partial month premiums. it's called internally
431 -- and from benprprc.pkb
432 procedure compute_partial_mo
433 (p_business_group_id in number
434 ,p_effective_date in date
435 ,p_actl_prem_id in number
436 ,p_person_id in number
437 ,p_enrt_cvg_strt_dt in date
438 ,p_enrt_cvg_thru_dt in date
439 ,p_prtl_mo_det_mthd_cd in varchar2 default null
440 ,p_prtl_mo_det_mthd_rl in number default null
441 ,p_wsh_rl_dy_mo_num in number default null
442 ,p_rndg_cd in varchar2 default null
443 ,p_rndg_rl in number default null
444 ,p_lwr_lmt_calc_rl in number default null
445 ,p_lwr_lmt_val in number default null
446 ,p_upr_lmt_calc_rl in number default null
447 ,p_upr_lmt_val in number default null
448 ,p_pgm_id in number default null
449 ,p_pl_typ_id in number default null
450 ,p_pl_id in number default null
451 ,p_opt_id in number default null
452 ,p_val in out nocopy number) is
453 --
454 l_package varchar2(80) := g_package||'.compute_partial_mo';
455 l_error_text varchar2(200) := null;
456 --
457 l_val number;
458
459 -- Rules variables:
460 l_outputs ff_exec.outputs_t;
461 l_prtl_mo_det_mthd_cd varchar2(30);
462 l_jurisdiction PAY_CA_EMP_PROV_TAX_INFO_F.JURISDICTION_CODE%type :=
463 null;
464 l_assignment_id number;
465 l_location_id number;
466 l_organization_id number;
467 l_region_2 hr_locations_all.region_2%TYPE; -- UTF8 varchar2(70);
468 l_start_or_stop_cd varchar2(30);
469 l_start_or_stop_date date;
470 l_prorate_flag varchar2(30);
471 --
472 begin
473 hr_utility.set_location ('Entering '||l_package,10);
474 -- load the full premium into a local. This may change to a pro-rated
475 -- or zero value.
476 get_rule_data(p_person_id => p_person_id
477 ,p_business_group_id => p_business_group_id
478 ,p_effective_date => p_effective_date
479 ,p_assignment_id => l_assignment_id
480 ,p_location_id => l_location_id
481 ,p_organization_id => l_organization_id
482 ,p_region_2 => l_region_2
483 ,p_jurisdiction => l_jurisdiction);
484 l_val := p_val;
485 hr_utility.set_location ('Proration code to use: '||
486 p_prtl_mo_det_mthd_cd,20);
487 if p_enrt_cvg_strt_dt is not null then
488 hr_utility.set_location ('coverage started this month '||
489 l_package,14);
490 l_start_or_stop_cd:='STRT';
491 l_start_or_stop_date:=p_enrt_cvg_strt_dt;
492 elsif p_enrt_cvg_thru_dt is not null then
493 -- coverage ended this month....
494 hr_utility.set_location ('coverage ended this month '||
495 l_package,20);
496 l_start_or_stop_cd:='STP';
497 l_start_or_stop_date:=p_enrt_cvg_thru_dt;
498 end if;
499 if l_start_or_stop_cd is not null then
500 l_prtl_mo_det_mthd_cd:=p_prtl_mo_det_mthd_cd;
501 l_val:=ben_element_entry.prorate_amount(
502 p_amt =>l_val
503 ,p_actl_prem_id =>p_actl_prem_id
504 ,p_person_id =>p_person_id
505 ,p_rndg_cd =>p_rndg_cd
506 ,p_rndg_rl =>p_rndg_rl
507 ,p_pgm_id =>p_pgm_id
508 ,p_pl_typ_id =>p_pl_typ_id
509 ,p_pl_id =>p_pl_id
510 ,p_opt_id =>p_opt_id
511 ,p_ler_id =>null
512 ,p_prorate_flag =>l_prorate_flag
513 ,p_effective_date =>p_effective_date
514 ,p_start_or_stop_cd =>l_start_or_stop_cd
515 ,p_start_or_stop_date =>l_start_or_stop_date
516 ,p_business_group_id =>p_business_group_id
517 ,p_assignment_id =>l_assignment_id
518 ,p_organization_id =>l_organization_id
519 ,p_jurisdiction_code =>l_jurisdiction
520 ,p_wsh_rl_dy_mo_num =>p_wsh_rl_dy_mo_num
521 ,p_prtl_mo_det_mthd_cd =>l_prtl_mo_det_mthd_cd
522 ,p_prtl_mo_det_mthd_rl =>p_prtl_mo_det_mthd_rl
523 );
524 hr_utility.set_location ('Proration code used: '||
525 l_prtl_mo_det_mthd_cd,20);
526 end if;
527 --
528 -- Since we are changing the value of the premium,
529 -- re-check the upper and lower limits.
530 --
531 if l_val <> p_val then
532 hr_utility.set_location('Variable Limits Checking'||l_package,68);
533 -- get data needed for rules, if we didn't already get it.
534 if p_lwr_lmt_calc_rl is not null or p_upr_lmt_calc_rl is not null then
535 null;
536 else
537 l_assignment_id := null;
538 l_organization_id := null;
539 l_region_2 := null;
540 l_jurisdiction := null;
541 end if;
542 benutils.limit_checks
543 (p_upr_lmt_val => p_upr_lmt_val,
544 p_lwr_lmt_val => p_lwr_lmt_val,
545 p_upr_lmt_calc_rl => p_upr_lmt_calc_rl,
546 p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl,
547 p_effective_date => p_effective_date,
548 p_business_group_id => p_business_group_id,
549 p_assignment_id => l_assignment_id,
550 p_organization_id => l_organization_id,
551 p_pgm_id => p_pgm_id,
552 p_pl_id => p_pl_id,
553 p_pl_typ_id => p_pl_typ_id,
554 p_opt_id => p_opt_id,
555 p_ler_id => null, -- we aren't dealing with a ler.
556 p_state => l_region_2,
557 p_val => l_val);
558
559 end if;
560 p_val := l_val;
561 hr_utility.set_location ('Leaving '||l_package,99);
562 exception
563 when others then
564 l_error_text := sqlerrm;
565 hr_utility.set_location ('Fail in '||l_package,999);
566 hr_utility.set_location('Error:'||l_error_text,999);
567 fnd_message.raise_error;
568 end compute_partial_mo;
569 -- ----------------------------------------------------------------------------
570 -- |------------------------------< compute_prem >----------------------------|
571 -- ----------------------------------------------------------------------------
572 -- Procedure used internally to compute and write premium records.
573 procedure compute_prem
574 (p_validate in varchar2 default 'N'
575 ,p_person_id in number
576 ,p_business_group_id in number
577 ,p_effective_date in date
578 ,p_first_day_of_month in date
579 ,p_last_day_of_month in date
580 ,p_enrt_cvg_strt_dt in date
581 ,p_enrt_cvg_thru_dt in date
582 ,p_prtl_mo_det_mthd_cd in varchar2 default null
583 ,p_prtl_mo_det_mthd_rl in number default null
584 ,p_wsh_rl_dy_mo_num in number default null
585 ,p_rndg_cd in varchar2 default null
586 ,p_rndg_rl in number default null
587 ,p_lwr_lmt_calc_rl in number default null
588 ,p_lwr_lmt_val in number default null
589 ,p_upr_lmt_calc_rl in number default null
590 ,p_upr_lmt_val in number default null
591 ,p_pgm_id in number default null
592 ,p_pl_typ_id in number default null
593 ,p_pl_id in number default null
594 ,p_opt_id in number default null
595 ,p_val in number
596 ,p_actl_prem_id in number
597 ,p_prtt_prem_id in number
598 ,p_mo_num in number
599 ,p_uom in varchar2
600 ,p_yr_num in number
601 ,p_stop_looking out nocopy varchar2
602 ,p_out_val out nocopy number) is
603 --
604 l_package varchar2(80) := g_package||'.compute_prem';
605 l_error_text varchar2(200) := null;
606 --
607
608 cursor c_prm (p_prtt_prem_id number) is
609 select prm.prtt_prem_by_mo_id, prm.object_version_number,
610 prm.mnl_adj_flag,prm.uom,prm.val,prm.cr_val,prm.cost_allocation_keyflex_id
611 , effective_start_date
612 from ben_prtt_prem_by_mo_f prm
613 where prm.mo_num = p_mo_num
614 and prm.yr_num = p_yr_num
615 and prm.prtt_prem_id = p_prtt_prem_id
616 -- order by make sure all the time cursor hit the first row
617 order by prm.effective_start_date ;
618 --and p_effective_date between prm.effective_start_date and prm.effective_end_date; -- bug 2784213
619 l_prm c_prm%rowtype;
620
621
622 cursor c_prm_ovn (p_prtt_prem_id number,p_effective_dt date) is
623 select prm.prtt_prem_by_mo_id, prm.object_version_number,
624 prm.mnl_adj_flag,prm.uom,prm.val,prm.cr_val,prm.cost_allocation_keyflex_id
625 from ben_prtt_prem_by_mo_f prm
626 where prm.mo_num = p_mo_num
627 and prm.yr_num = p_yr_num
628 and prm.prtt_prem_id = p_prtt_prem_id
629 and p_effective_dt between prm.effective_start_date and prm.effective_end_date;
630
631
632
633 --l_prtt_prem_by_mo_id number;
634 l_effective_start_date date;
635 l_effective_end_date date;
636 l_cak number;
637 l_ovn number;
638 l_val number;
639 l_val_net number;
640
641 l_effective_date_mo date;
642 l_last_effective_dt date;
643 --
644 -- Bug 4892354
645 l_prm_update_mode varchar2(60);
646 l_correction_mode boolean;
647 l_update_mode boolean;
648 l_update_override_mode boolean;
649 l_update_change_insert_mode boolean;
650 -- Bug 4892354
651 --
652
653 begin
654 hr_utility.set_location ('Entering '||l_package,10);
655
656 -- This procedure is first called with the effective date (or effective-date plus one
657 -- month) as the processing month.
658 -- Then, if the main procedure determines that prior month premiums may be due,
659 -- this procedure is called with each prior month as the processing month
660 -- p_last_day_of_month is always the last day of the processing month
661 -- p_first_day_of_month is always the first day of the processing month
662 hr_utility.set_location ('Actl Prem:'||to_char(p_actl_prem_id),10);
663 hr_utility.set_location ('first date '||
664 to_char(p_first_day_of_month,'dd-mon-yyyy'),10);
665 hr_utility.set_location ('last date '||
666 to_char(p_last_day_of_month,'dd-mon-yyyy'),10);
667 hr_utility.set_location ('p_enrt_cvg_strt_dt '||p_enrt_cvg_strt_dt,10);
668 hr_utility.set_location ('p_enrt_cvg_thru_dt :'|| p_enrt_cvg_thru_dt, 10) ;
669
670 -- load the full premium into a local. This may change to a pro-rated
671 -- or zero value.
672 l_val := p_val;
673 p_stop_looking := 'N';
674 l_last_effective_dt := last_day(p_effective_date) ;
675
676 -- does coverage begin or end within the month (ie do we need to prorate)
677 -- and is there a proration code (wash, rule, prtval etc). All and None
678 -- mean don't do proration.
679 -- If cvg begins and ends in month, the start date check overrides
680 -- the end date check.
681 -- BUG3379060 if p_enrt_cvg_strt_dt between (p_first_day_of_month + 1)
682 --- BUG3379060 revetred for 3742713 if p_enrt_cvg_strt_dt between (p_first_day_of_month
683 if ((p_enrt_cvg_strt_dt between (p_first_day_of_month + 1)
684 and p_last_day_of_month
685 )
686 or
687 (p_enrt_cvg_strt_dt between p_first_day_of_month
688 and p_last_day_of_month
689 and p_prtl_mo_det_mthd_cd in ('PRTVAL','WASHRULE','RL')
690 )
691 )
692 --- if the month starts and ends on the same month use the end calcualtion
693 and not ( p_prtl_mo_det_mthd_cd = 'WASHRULE' and p_enrt_cvg_thru_dt between (p_first_day_of_month-1)
694 and p_last_day_of_month)
695 then
696 -- coverage started during this month....
697 -- no need to continue to look back thru months
698 p_stop_looking := 'Y';
699 hr_utility.set_location ('coverage started this month ' || p_stop_looking ,14);
700 -- compute partial month premium
701 compute_partial_mo
702 (p_business_group_id => p_business_group_id
703 ,p_effective_date => p_effective_date
704 ,p_actl_prem_id => p_actl_prem_id
705 ,p_person_id => p_person_id
706 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
707 ,p_enrt_cvg_thru_dt => null
708 ,p_prtl_mo_det_mthd_cd => p_prtl_mo_det_mthd_cd
709 ,p_prtl_mo_det_mthd_rl => p_prtl_mo_det_mthd_rl
710 ,p_wsh_rl_dy_mo_num => p_wsh_rl_dy_mo_num
711 ,p_rndg_cd => p_rndg_cd
712 ,p_rndg_rl => p_rndg_rl
713 ,p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl
714 ,p_lwr_lmt_val => p_lwr_lmt_val
715 ,p_upr_lmt_calc_rl => p_upr_lmt_calc_rl
716 ,p_upr_lmt_val => p_upr_lmt_val
717 ,p_pgm_id => p_pgm_id
718 ,p_pl_typ_id => p_pl_typ_id
719 ,p_pl_id => p_pl_id
720 ,p_opt_id => p_opt_id
721 ,p_val => l_val);
722 elsif ( p_enrt_cvg_thru_dt between p_first_day_of_month
723 and (p_last_day_of_month - 1)
724 )
725 or
726 (p_enrt_cvg_thru_dt between p_first_day_of_month
727 and p_last_day_of_month
728 and p_prtl_mo_det_mthd_cd in ('PRTVAL','WASHRULE','RL')
729 ) then
730
731 -- BUG3379060 and (p_last_day_of_month - 1) then
732 -- BUG3379060 revetred for 3742713 and p_last_day_of_month then
733 -- coverage ended this month....
734 hr_utility.set_location ('coverage ended this month ',20);
735 -- compute partial month premium
736 compute_partial_mo
737 (p_business_group_id => p_business_group_id
738 ,p_effective_date => p_effective_date
739 ,p_actl_prem_id => p_actl_prem_id
740 ,p_person_id => p_person_id
741 ,p_enrt_cvg_strt_dt => null
742 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
743 ,p_prtl_mo_det_mthd_cd => p_prtl_mo_det_mthd_cd
744 ,p_prtl_mo_det_mthd_rl => p_prtl_mo_det_mthd_rl
745 ,p_wsh_rl_dy_mo_num => p_wsh_rl_dy_mo_num
746 ,p_rndg_cd => p_rndg_cd
747 ,p_rndg_rl => p_rndg_rl
748 ,p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl
749 ,p_lwr_lmt_val => p_lwr_lmt_val
750 ,p_upr_lmt_calc_rl => p_upr_lmt_calc_rl
751 ,p_upr_lmt_val => p_upr_lmt_val
752 ,p_pgm_id => p_pgm_id
753 ,p_pl_typ_id => p_pl_typ_id
754 ,p_pl_id => p_pl_id
755 ,p_opt_id => p_opt_id
756 ,p_val => l_val);
757 else
758 -- using a full month value, round per rounding rule in actl_prem
759 if p_rndg_cd is not null and l_val <>0 then
760 l_val := benutils.do_rounding
761 (p_rounding_cd => p_rndg_cd
762 ,p_rounding_rl => p_rndg_rl
763 ,p_value => l_val
764 ,p_effective_date => p_effective_date);
765 end if;
766
767 end if;
768 if p_enrt_cvg_strt_dt = p_first_day_of_month then
769 -- coverage started the first day of this month....
770 -- no need to continue to look back thru months
771 p_stop_looking := 'Y';
772 hr_utility.set_location ('coverage started this month ' || p_stop_looking ,15);
773 end if;
774
775 hr_utility.set_location ('write costing ',30);
776 -- first insert into cost allocation keyflex
777 determine_costing (p_actl_prem_id => p_actl_prem_id
778 ,p_person_id => p_person_id
779 ,p_effective_date => p_effective_date
780 ,p_business_group_id => p_business_group_id
781 ,p_cak_id => l_cak);
782 hr_utility.set_location ('write premium. Actl Prem:'||
783 to_char(p_actl_prem_id)||' val:'||to_char(l_val),31);
784 open c_prm(p_prtt_prem_id => p_prtt_prem_id);
785 fetch c_prm into l_prm;
786 if c_prm%notfound or c_prm%notfound is null then
787 --
788 l_effective_date_mo := last_day(to_date(p_yr_num||lpad(p_mo_num,2,0),'YYYYMM')); -- bug 2784213
789 hr_utility.set_location ('l_effective_date_mo :'|| l_effective_date_mo, 10) ;
790 --
791 ben_prtt_prem_by_mo_api.create_prtt_prem_by_mo
792 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
793 ,p_effective_start_date => l_effective_start_date
794 ,p_effective_end_date => l_effective_end_date
795 ,p_mnl_adj_flag => 'N'
796 ,p_mo_num => p_mo_num
797 ,p_yr_num => p_yr_num
798 ,p_antcpd_prtt_cntr_uom => null
799 ,p_antcpd_prtt_cntr_val => null
800 ,p_val => l_val
801 ,p_cr_val => null
802 ,p_cr_mnl_adj_flag => 'N'
803 ,p_alctd_val_flag => 'N'
804 ,p_uom => p_uom
805 ,p_prtt_prem_id => p_prtt_prem_id
806 ,p_cost_allocation_keyflex_id => l_cak
807 ,p_business_group_id => p_business_group_id
808 ,p_object_version_number => l_prm.object_version_number
809 ,p_request_id => fnd_global.conc_request_id
810 ,p_program_application_id => fnd_global.prog_appl_id
811 ,p_program_id => fnd_global.conc_program_id
812 ,p_program_update_date => sysdate
813 ,p_effective_date => l_effective_date_mo);
814 else
815 --
816 -- Bug 5127 : When premium process is rerun,
817 -- Do not revert back to the standard premium value,
818 -- If manual adjustement flag is Y.
819 --
820 if l_prm.mnl_adj_flag = 'N' then
821
822 -- get the right value
823 /* Bug 4892354 : commented as all reqd data is available from c_prm => l_prm
824 open c_prm_ovn (p_prtt_prem_id,l_last_effective_dt) ;
825 fetch c_prm_ovn into l_prm ;
826 close c_prm_ovn ;
827 */
828 if l_prm.cr_val > 0 and l_val > 0 then
829 hr_utility.set_location ('update the premium:'|| l_prm.prtt_prem_by_mo_id, 10) ;
830 --
831 -- Bug 4892354 : Get Valid Update Modes
832 --
833 dt_api.Find_DT_Upd_Modes
834 (p_effective_date => l_prm.effective_start_date,
835 p_base_table_name => 'BEN_PRTT_PREM_BY_MO_F',
836 p_base_key_column => 'PRTT_PREM_BY_MO_ID',
837 p_base_key_value => l_prm.prtt_prem_by_mo_id,
838 p_correction => l_correction_mode,
839 p_update => l_update_mode,
840 p_update_override => l_update_override_mode,
841 p_update_change_insert => l_update_change_insert_mode);
842 --
843 if l_update_change_insert_mode
844 then
845 l_prm_update_mode := hr_api.g_update_change_insert;
846 elsif l_update_override_mode
847 then
848 l_prm_update_mode := hr_api.g_update_override;
849 elsif l_update_mode
850 then
851 l_prm_update_mode := hr_api.g_update;
852 else
853 l_prm_update_mode := hr_api.g_correction;
854 end if;
855 --
856 --
857 ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
858 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
859 ,p_effective_start_date => l_effective_start_date
860 ,p_effective_end_date => l_effective_end_date
861 ,p_mnl_adj_flag => 'N'
862 ,p_val => l_val
863 ,p_cr_val => null
864 ,p_alctd_val_flag => 'N'
865 ,p_uom => p_uom
866 ,p_prtt_prem_id => p_prtt_prem_id
867 ,p_cost_allocation_keyflex_id => l_cak
868 ,p_object_version_number => l_prm.object_version_number
869 ,p_request_id => fnd_global.conc_request_id
870 ,p_program_application_id => fnd_global.prog_appl_id
871 ,p_program_id => fnd_global.conc_program_id
872 ,p_program_update_date => sysdate
873 ,p_effective_date => l_prm.effective_start_date
874 ,p_datetrack_mode => l_prm_update_mode);
875
876
877
878 --update only any changes happens for the row
879 -- every time updating the row the update date changes
880 -- this trouble the exract to get the record updated on
881 -- certain period of time
882 --whne the cvg ended dont update the premium create credit
883
884 elsif (l_prm.val > 0 and p_enrt_cvg_thru_dt > p_last_day_of_month )
885 and ( l_prm.uom <> p_uom or l_prm.val <> l_val or
886 nvl(l_prm.cost_allocation_keyflex_id,-1) <> nvl(l_cak,-1) ) then
887 --
888 hr_utility.set_location ('correct the premium:'|| l_prm.prtt_prem_by_mo_id, 10) ;
889 --
890 -- Bug 4892354 : Get Valid Update Modes
891 --
892 dt_api.Find_DT_Upd_Modes
893 (p_effective_date => l_prm.effective_start_date,
894 p_base_table_name => 'BEN_PRTT_PREM_BY_MO_F',
895 p_base_key_column => 'PRTT_PREM_BY_MO_ID',
896 p_base_key_value => l_prm.prtt_prem_by_mo_id,
897 p_correction => l_correction_mode,
898 p_update => l_update_mode,
899 p_update_override => l_update_override_mode,
900 p_update_change_insert => l_update_change_insert_mode);
901 --
902 if l_update_change_insert_mode
903 then
904 l_prm_update_mode := hr_api.g_update_change_insert;
905 elsif l_update_override_mode
906 then
907 l_prm_update_mode := hr_api.g_update_override;
908 elsif l_correction_mode
909 then
910 l_prm_update_mode := hr_api.g_correction;
911 else
912 l_prm_update_mode := hr_api.g_update;
913 end if;
914 --
915 --
916 ben_prtt_prem_by_mo_api.update_prtt_prem_by_mo
917 (p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id
918 ,p_effective_start_date => l_effective_start_date
919 ,p_effective_end_date => l_effective_end_date
920 ,p_mnl_adj_flag => 'N'
921 ,p_val => l_val
922 ,p_alctd_val_flag => 'N'
923 ,p_uom => p_uom
924 ,p_prtt_prem_id => p_prtt_prem_id
925 ,p_cost_allocation_keyflex_id => l_cak
926 ,p_object_version_number => l_prm.object_version_number
927 ,p_request_id => fnd_global.conc_request_id
928 ,p_program_application_id => fnd_global.prog_appl_id
929 ,p_program_id => fnd_global.conc_program_id
930 ,p_program_update_date => sysdate
931 ,p_effective_date => l_prm.effective_start_date
932 ,p_datetrack_mode => l_prm_update_mode);
933 else
934 -- if monthly chg found without any change dont go further
935 p_stop_looking := 'Y';
936 hr_utility.set_location (' monthly chg found ' || p_stop_looking ,14);
937 end if ;
938 --
939 else
940 -- if manually adjusted flag found dont go further to generate the premium
941 p_stop_looking := 'Y';
942 hr_utility.set_location (' manually adjusted flag found ' || p_stop_looking ,14);
943 end if;
944 --
945 end if;
946 p_out_val := l_val;
947 hr_utility.set_location ('Leaving '||l_package,99);
948 exception
949 when others then
950 l_error_text := sqlerrm;
951 hr_utility.set_location ('Fail in '||l_package,999);
952 hr_utility.set_location('Error:'||l_error_text,999);
953 fnd_message.raise_error;
954 end compute_prem;
955 -- ----------------------------------------------------------------------------
956 -- |------------------------------< main >------------------------------------|
957 -- ----------------------------------------------------------------------------
958 -- This is the procedure to call to determine all the 'ENRT' type premiums for
959 -- the month.
960 procedure main
961 (p_validate in varchar2 default 'N'
962 ,p_person_id in number default null
963 ,p_person_action_id in number default null
964 ,p_comp_selection_rl in number default null
965 ,p_pgm_id in number default null
966 ,p_pl_typ_id in number default null
967 ,p_pl_id in number default null
968 ,p_object_version_number in out nocopy number
969 ,p_business_group_id in number
970 ,p_mo_num in number
971 ,p_yr_num in number
972 ,p_first_day_of_month in date
973 ,p_effective_date in date) is
974 --
975 l_package varchar2(80) := g_package||'.main';
976 l_error_text varchar2(200) := null;
977 --
978 cursor c_results is
979 select pen.person_id, pen.pl_id, pen.oipl_id, pen.effective_start_date,
980 pen.effective_end_date, pen.enrt_cvg_strt_dt, pen.enrt_cvg_thru_dt,
981 pen.pgm_id, pen.pl_typ_id, pen.ler_id, pen.prtt_enrt_rslt_id
982 from ben_prtt_enrt_rslt_f pen
983 where pen.prtt_enrt_rslt_stat_cd is null
984 and pen.sspndd_flag = 'N'
985 and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') -- not a dummy plan
986 -- cvg starts sometime before end of next month
987 and pen.enrt_cvg_strt_dt <= add_months(p_effective_date,1)
988 and pen.person_id = p_person_id
989 -- check criteria user entered on the submit form:
990 and (pen.pl_id = p_pl_id or p_pl_id is null)
991 and (pen.pl_typ_id = p_pl_typ_id or p_pl_typ_id is null)
992 and (pen.pgm_id = p_pgm_id or p_pgm_id is null)
993 and pen.business_group_id+0 = p_business_group_id
994 and p_effective_date between
995 pen.effective_start_date and pen.effective_end_date;
996 --l_results c_results%rowtype;
997
998 -- There is an assumption that if the actl_prem is 'enrt' then there should
999 -- already be a row in prtt_prem written by the enrollment process.
1000 cursor c_prems(p_prtt_enrt_rslt_id number) is
1001 select ppe.std_prem_val, ppe.std_prem_uom, apr.prtl_mo_det_mthd_cd,
1002 apr.prtl_mo_det_mthd_rl, apr.wsh_rl_dy_mo_num, apr.actl_prem_id,
1003 ppe.prtt_prem_id, apr.rndg_cd, apr.rndg_rl, apr.prsptv_r_rtsptv_cd,
1004 apr.lwr_lmt_calc_rl, apr.lwr_lmt_val,
1005 apr.upr_lmt_calc_rl, apr.upr_lmt_val,
1006 apr.cr_lkbk_val,apr.cr_lkbk_crnt_py_only_flag,
1007 ppe.effective_start_date
1008 from ben_actl_prem_f apr,
1009 ben_per_in_ler pil,
1010 ben_prtt_prem_f ppe
1011 where apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplo.pkb
1012 and apr.business_group_id+0 = p_business_group_id
1013 and ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1014 and p_effective_date between
1015 apr.effective_start_date and apr.effective_end_date
1016 and ppe.actl_prem_id = apr.actl_prem_id
1017 and p_effective_date between
1018 ppe.effective_start_date and ppe.effective_end_date
1019 and pil.per_in_ler_id=ppe.per_in_ler_id
1020 and pil.business_group_id+0=ppe.business_group_id+0
1021 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1022 ;
1023 -- l_prems c_prems%rowtype;
1024
1025 cursor c_old_result (p_prtt_enrt_rslt_id number,
1026 p_effective_start_date date) is
1027 select pen.effective_start_date,
1028 pen.effective_end_date, pen.prtt_enrt_rslt_id
1029 from ben_prtt_enrt_rslt_f pen
1030 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1031 and pen.prtt_enrt_rslt_stat_cd is null
1032 and pen.effective_start_date < p_effective_start_date;
1033 l_old_result c_old_result%rowtype;
1034
1035 l_months_to_subtract number;
1036 l_first_day_of_month date;
1037 l_last_day_of_month date;
1038 l_stop_looking varchar2(1);
1039 l_current_month varchar2(1);
1040 l_mo_num number;
1041 l_yr_num number;
1042 l_val number;
1043 l_look_back_dt date ;
1044
1045 -- Concurrent Code Begin
1046 cursor c_opt(l_oipl_id number) is
1047 select opt_id from ben_oipl_f oipl
1048 where oipl.oipl_id = l_oipl_id
1049 and p_effective_date between
1050 oipl.effective_start_date and oipl.effective_end_date;
1051 l_opt c_opt%rowtype;
1052
1053 -------Bug 7414822
1054 cursor c_ler_typ_cd(p_ler_id number) is
1055 SELECT typ_cd
1056 FROM ben_ler_f
1057 WHERE ler_id = p_ler_id
1058 AND business_group_id = p_business_group_id;
1059
1060 l_ler_typ_cd varchar2(100);
1061
1062 cursor c_check_mo_prem(p_var VARCHAR2,l_pen_id number,p_ler_typ_cd varchar2,p_ler_id number) IS
1063 SELECT 'Y'
1064 FROM ben_prtt_enrt_rslt_f pen
1065 WHERE pen.prtt_enrt_rslt_id = l_pen_id
1066 AND pen.business_group_id = p_business_group_id
1067 AND ((p_ler_typ_cd <> 'SCHEDDO'
1068 and p_effective_date BETWEEN pen.effective_start_date
1069 AND Decode(p_var,'RETRO',pen.effective_end_date,Add_Months(last_day(pen.effective_end_date),1))
1070 AND pen.effective_start_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt)
1071 or (p_ler_typ_cd = 'SCHEDDO'
1072 and p_effective_date BETWEEN pen.enrt_cvg_strt_dt
1073 AND Decode(p_var,'RETRO',pen.enrt_cvg_thru_dt,Add_Months(last_day(pen.enrt_cvg_thru_dt),1))
1074 and pen.enrt_cvg_thru_dt >= pen.effective_start_date))
1075 and pen.ler_id = p_ler_id;
1076
1077 l_check_mo_prem varchar2(10) := 'N';
1078 -----Bug 7414822
1079
1080 l_actn varchar2(80);
1081 l_rule_ret varchar2(30);
1082 l_person_ended varchar2(30):='N';
1083 -- Concurrent Code End
1084 begin
1085 -- p_effective_date is always the last day of the month this is being run
1086 hr_utility.set_location ('Entering '||l_package,10);
1087 hr_utility.set_location ('For person:'||to_char(p_person_id),20);
1088
1089 -- Concurrent Code Begin
1090 l_actn := 'Initializing...';
1091 Savepoint process_premium_savepoint;
1092 --
1093 -- Cache person data and write personal data into cache.
1094 --
1095 l_actn := 'Calling ben_batch_utils.person_header...';
1096 ben_batch_utils.person_header
1097 (p_person_id => p_person_id
1098 ,p_business_group_id => p_business_group_id
1099 ,p_effective_date => p_effective_date
1100 );
1101 --
1102 l_actn := 'Calling ben_batch_utils.ini(COMP_OBJ)...';
1103 ben_batch_utils.ini('COMP_OBJ');
1104 -- Concurrent Code End
1105
1106 for l_results in c_results loop
1107 -- Concurrent Code Begin
1108 -- Check if the comp object rule requirements are satisfied
1109 -- Note: several args already checked in the cursor here and in 'process' proc
1110 --
1111 if l_results.oipl_id is not null then
1112 open c_opt(l_results.oipl_id);
1113 fetch c_opt into l_opt;
1114 close c_opt;
1115 else l_opt := null;
1116 end if;
1117
1118 hr_utility.set_location ('Result id '||l_results.prtt_enrt_rslt_id,10);
1119
1120 l_rule_ret:='Y';
1121 if p_comp_selection_rl is not null then
1122 hr_utility.set_location('found a rule',12);
1123 l_rule_ret:=ben_maintain_designee_elig.comp_selection_rule(
1124 p_person_id => p_person_id
1125 ,p_business_group_id => p_business_group_id
1126 ,p_pgm_id => l_results.pgm_id
1127 ,p_pl_id => l_results.pl_id
1128 ,p_pl_typ_id => l_results.pl_typ_id
1129 ,p_opt_id => l_opt.opt_id
1130 ,p_oipl_id => l_results.oipl_id
1131 ,p_ler_id => l_results.ler_id
1132 ,p_comp_selection_rule_id => p_comp_selection_rl
1133 ,p_effective_date => p_effective_date
1134 );
1135 end if;
1136 hr_utility.set_location(l_package,13);
1137 if l_rule_ret='Y' then
1138 -- Concurrent Code End
1139 for l_prems in c_prems(p_prtt_enrt_rslt_id => l_results.prtt_enrt_rslt_id) loop
1140 if l_prems.prsptv_r_rtsptv_cd = 'PRO' or
1141 (l_prems.prsptv_r_rtsptv_cd = 'RETRO' and
1142 l_results.enrt_cvg_strt_dt <= p_effective_date) then
1143 -- if the premium is retrospective, then we do not want to look at results
1144 -- whose coverage starts next month. skip this premium and go to next one.
1145
1146 if l_prems.prsptv_r_rtsptv_cd = 'RETRO' then
1147 -- start with efective date month
1148 l_first_day_of_month :=p_first_day_of_month;
1149 l_last_day_of_month := p_effective_date;
1150 l_mo_num := p_mo_num;
1151 l_yr_num := p_yr_num;
1152 else -- l_prem.prsptv_r_rtsptv_cd = 'PRO'
1153 -- start with next months premium and work backwards thru time.
1154 l_first_day_of_month := add_months(p_first_day_of_month,1);
1155 l_last_day_of_month := add_months(p_effective_date,1);
1156 l_mo_num := to_char(l_last_day_of_month,'MM');
1157 l_yr_num := to_char(l_last_day_of_month,'YYYY');
1158 end if;
1159 -- Decide the lookback period
1160 l_look_back_dt := null ;
1161 if nvl(l_prems.cr_lkbk_crnt_py_only_flag,'N') = 'Y' then
1162 l_look_back_dt := l_last_day_of_month ;
1163 else
1164 if l_prems.cr_lkbk_val is not null then
1165 l_look_back_dt := add_months( l_last_day_of_month , (l_prems.cr_lkbk_val * -1)) ;
1166 end if ;
1167 end if ;
1168 hr_utility.set_location('look back date ' || l_look_back_dt , 56 ) ;
1169 --
1170 l_current_month := 'Y';
1171 loop
1172 l_stop_looking := 'N';
1173 hr_utility.set_location(l_package,133);
1174 if l_results.enrt_cvg_thru_dt >= l_first_day_of_month then
1175 -- they have coverage during the month we are processing.
1176 -- If they don't this if stmt will ensure we don't write
1177 -- a premium for them.
1178 compute_prem(p_validate => p_validate
1179 ,p_person_id => l_results.person_id
1180 ,p_business_group_id => p_business_group_id
1181 ,p_effective_date => p_effective_date
1182 ,p_first_day_of_month => l_first_day_of_month
1183 ,p_last_day_of_month => l_last_day_of_month
1184 ,p_enrt_cvg_strt_dt => l_results.enrt_cvg_strt_dt
1185 ,p_enrt_cvg_thru_dt => l_results.enrt_cvg_thru_dt
1186 ,p_prtl_mo_det_mthd_cd => l_prems.prtl_mo_det_mthd_cd
1187 ,p_prtl_mo_det_mthd_rl => l_prems.prtl_mo_det_mthd_rl
1188 ,p_wsh_rl_dy_mo_num => l_prems.wsh_rl_dy_mo_num
1189 ,p_rndg_cd => l_prems.rndg_cd
1190 ,p_rndg_rl => l_prems.rndg_rl
1191 ,p_lwr_lmt_calc_rl => l_prems.lwr_lmt_calc_rl
1192 ,p_lwr_lmt_val => l_prems.lwr_lmt_val
1193 ,p_upr_lmt_calc_rl => l_prems.upr_lmt_calc_rl
1194 ,p_upr_lmt_val => l_prems.upr_lmt_val
1195 ,p_pgm_id => l_results.pgm_id
1196 ,p_pl_typ_id => l_results.pl_typ_id
1197 ,p_pl_id => l_results.pl_id
1198 ,p_opt_id => l_opt.opt_id
1199 ,p_val => l_prems.std_prem_val
1200 ,p_actl_prem_id => l_prems.actl_prem_id
1201 ,p_prtt_prem_id => l_prems.prtt_prem_id
1202 ,p_mo_num => l_mo_num
1203 ,p_uom => l_prems.std_prem_uom
1204 ,p_yr_num => l_yr_num
1205 ,p_stop_looking => l_stop_looking
1206 ,p_out_val => l_val);
1207
1208 -- write info to reporting table
1209 if l_current_month = 'Y' then
1210 -- if we are processing this month for retrospective or next
1211 -- month for prospective, the report considers this 'current month'.
1212 g_rec.rep_typ_cd := 'PRCURMOP';
1213 l_current_month := 'N';
1214 else
1215 -- otherwise, it's a retroactive premium. That's different
1216 -- than retrospective premium type.
1217 g_rec.rep_typ_cd := 'PRRETROP';
1218 end if;
1219 -------------Bug 7414822
1220 l_check_mo_prem := 'N';
1221 ---get the ler_typ_code
1222 open c_ler_typ_cd(l_results.ler_id);
1223 fetch c_ler_typ_cd into l_ler_typ_cd;
1224 close c_ler_typ_cd;
1225 open c_check_mo_prem(l_prems.prsptv_r_rtsptv_cd,l_results.prtt_enrt_rslt_id,l_ler_typ_cd,l_results.ler_id);
1226 fetch c_check_mo_prem into l_check_mo_prem;
1227 if c_check_mo_prem%found then
1228 -------------Bug 7414822
1229 g_rec.person_id := l_results.person_id;
1230 g_rec.pgm_id := l_results.pgm_id;
1231 g_rec.pl_id := l_results.pl_id;
1232 g_rec.oipl_id := l_results.oipl_id;
1233 g_rec.pl_typ_id := l_results.pl_typ_id;
1234 g_rec.actl_prem_id := l_prems.actl_prem_id;
1235 g_rec.val := l_val;
1236 g_rec.mo_num := l_mo_num;
1237 g_rec.yr_num := l_yr_num;
1238
1239 benutils.write(p_rec => g_rec);
1240 -------------Bug 7414822
1241 end if;
1242 close c_check_mo_prem;
1243 -------------Bug 7414822
1244 end if;
1245 --
1246 -- If l_stop_looking is Y, the proc determined that the cvg started
1247 -- in the month we are processing, there is no need to continue to
1248 -- look back for other month's premiums.
1249 -- We also don't look back if the result was created prior to this
1250 -- month (because prior runs would have created the premiums).
1251 hr_utility.set_location('l_stop_looking = ' || l_stop_looking, 999);
1252 hr_utility.set_location('l_results.effective_start_date = ' || l_results.effective_start_date, 999);
1253 hr_utility.set_location('l_first_day_of_month = ' || l_first_day_of_month, 999);
1254
1255 if l_stop_looking = 'N' then
1256 -- For results that were created for the first time
1257 -- this month, we want to look back thru prior months to
1258 -- create additional premiums. Results that were created
1259 -- prior to this month (and perhaps are just being date-
1260 -- tracked updated this month) would have had those premiums
1261 -- already created by a prior month run of this job.
1262
1263 -- the following cursor has 2 issues -- tilak
1264 -- 1) if the premium is not executed every month and result is date tracked
1265 -- the process does not generate the premium for previous months
1266 -- this is not a serious issue, cause the assumption is ct runs the process
1267 -- every month
1268 -- 2) if a LE created 2 months back and covered in new premium option.plan
1269 -- wich generated the 1 month credit entry for the original premium plan
1270 -- now the LE is backed out and the original plan continues .
1271 -- in this case the process should generate 2 months premium charges
1272 -- for the orignal plan
1273
1274 -- so the logic changed to generate the premium till it find the previous
1275 -- monthly charges without any changes or till it find the entry which manually adjusted
1276
1277 -- or the premium effective start date is higher then the month start date
1278 -- we dont generate premium for the previous results rows cause there may be changes of
1279 -- premium and assume the premium generated before the LE executed or
1280 -- the process is executed again withn the period of the process
1281
1282 --open c_old_result(p_prtt_enrt_rslt_id =>
1283 -- l_results.prtt_enrt_rslt_id,
1284 -- p_effective_start_date => l_results.effective_start_date);
1285 --fetch c_old_result into l_old_result;
1286 --if c_old_result%notfound or c_old_result%notfound is null then
1287 hr_utility.set_location ('Look for prior months ',50);
1288 l_first_day_of_month := add_months(l_first_day_of_month, -1);
1289 l_last_day_of_month := add_months(l_last_day_of_month, -1);
1290 l_mo_num := to_char(l_last_day_of_month,'MM');
1291 l_yr_num := to_char(l_last_day_of_month,'YYYY');
1292 --else
1293 -- close c_old_result;
1294 -- exit;
1295 --end if;
1296 --close c_old_result;
1297 -- for OSP the the result will be the same so we hve to validate the
1298 -- condition with premium row
1299
1300 hr_utility.set_location ('l_first_day_of_month '|| l_first_day_of_month ||
1301 ' l_prem.effective_start_date '||trunc(l_prems.effective_start_date,'MM') ,50);
1302
1303 --if trunc(l_first_day_of_month) < trunc(round(l_results.effective_start_date,'MM')) then
1304 if trunc(l_first_day_of_month) < trunc(l_prems.effective_start_date,'MM') then
1305
1306 hr_utility.set_location ( ' exit calcualtion ' ,50);
1307 exit ;
1308 end if ;
1309
1310
1311 -- if the month end date is below than look back date dont
1312 -- calcualte
1313
1314 if l_look_back_dt is not null and l_look_back_dt > l_last_day_of_month then
1315 hr_utility.set_location ( ' exit look back ' || l_look_back_dt ,50);
1316 exit ;
1317 end if ;
1318
1319 else
1320 exit;
1321 end if;
1322 end loop; -- calling compute_prem
1323 end if; -- if retro and cvg earlier than next month
1324 end loop; -- premiums
1325 end if; -- comp object rule passed
1326 end loop; -- results
1327 -- Concurrent Code Begin
1328 hr_utility.set_location(l_package,110);
1329 l_actn := 'Calling Ben_batch_utils.write_comp...';
1330 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1331 ,p_effective_date => p_effective_date
1332 );
1333 l_actn := 'About to optionally rollback...';
1334 If (p_validate = 'Y') then
1335 Rollback to process_premium_savepoint;
1336 End if;
1337 --
1338 --
1339 --
1340 If p_person_action_id is not null then
1341 --
1342 l_actn := 'Calling ben_person_actions_api.update_person_actions...';
1343 --
1344 ben_person_actions_api.update_person_actions
1345 (p_person_action_id => p_person_action_id
1346 ,p_action_status_cd => 'P'
1347 ,p_object_version_number => p_object_version_number
1348 ,p_effective_date => p_effective_date
1349 );
1350 End if;
1351 commit;
1352 hr_utility.set_location ('Leaving '||l_package,99);
1353 Exception
1354 When others then
1355 l_error_text := sqlerrm;
1356 hr_utility.set_location ('Fail in '||l_package,998);
1357 hr_utility.set_location (' with error '||l_error_text,999);
1358 rollback to process_premium_savepoint;
1359 ben_batch_utils.write_error_rec;
1360 ben_batch_utils.rpt_error(p_proc => l_package
1361 ,p_last_actn => l_actn
1362 ,p_rpt_flag => TRUE);
1363 Ben_batch_utils.write_comp(p_business_group_id => p_business_group_id
1364 ,p_effective_date => p_effective_date
1365 );
1366 If p_person_action_id is not null then
1367 ben_person_actions_api.update_person_actions
1368 (p_person_action_id => p_person_action_id
1369 ,p_action_status_cd => 'E'
1370 ,p_object_version_number => p_object_version_number
1371 ,p_effective_date => p_effective_date
1372 );
1373 End if;
1374 commit;
1375 raise ben_batch_utils.g_record_error;
1376 -- Concurrent Code End
1377 end main;
1378 end ben_prem_prtt_monthly;
[Home] [Help]