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