1 Package Body BEN_DERIVE_FACTORS as
2 /* $Header: bendefct.pkb 120.17.12020000.7 2012/08/09 18:46:48 amnaraya ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Derive Factors (external version)
12 Purpose:
13 This program determines values for the six 'derivable factors'
14 for a given person.
15 For example DETERMINE_AGE will calculate the input persons age
16 as of the age factor date.
17 Each procedure can be called externally.
18 History:
19 Date Who Version What?
20 ---- --- ------- -----
21 21-May-1998 Ty Hayden 110.0 Created.
22 16 Jun 98 T Guy 110.01 Removed other exception.
23 27-Jul-98 T Guy 110.2 Changed stated salary cursor to
24 changed_date <= l_date and commented
25 out nocopy return statements and put in
26 fnd messages and exceptions.
27 24-AUG-98 JMohapat 115.2 Added los and comb_age_los
28 Procedure to this Package.
29 08-OCT-98 G Perry 115.3 Corrected error messages.
30 25-OCT-98 G Perry 115.4 Added in benefits balance type
31 compensation level.
32 26-OCT-98 G Perry 115.5 Fixed ben_elig_per_f cursor so
33 it works for plans and programs.
34 04-DEC-98 jcarpent 115.6 Allow null per_in_ler_id.
35 Won't always have per_in_ler for
36 non-event dependent elig checking
37 10-Dec-98 T Guy 115.7 Fixed c_stated_salary cursor in
38 determine_compensation.
39 20-Dec-98 G Perry 115.8 Added support for compensation
40 balances.
41 21-Dec-98 jcarpent 115.9 added change date to det age
42 18 Jan 99 G Perry 115.10 LED V ED
43 09 Apr 99 S Tee 115.11 New salary schema. Changed
44 proposed_salary to
45 proposed_salary_n.
46 28 Apr 99 Shdas 115.12 Added contexts to rule calls.
47 14 May 99 T Guy 115.13 Added annualization and periodizing
48 for compensation. We need to determine
49 how to handle pay_basis type of PERIOD.
50 For now we are treating it as if it
51 has been annualized already.
52 12 Jul 99 jcarpent 115.14 Added checks for backed out nocopy pil
53 20-JUL-99 Gperry 115.15 genutils -> benutils package rename.
54 04-AUG-99 T Guy 115.16 spouse and dependent age calculation
55 removed nvl() of lf_evt_ocrd_dt for
56 performance issues.
57 30-AUG-99 pbodla 115.17 If called from what if analysis then
58 user specifies the compensations to
59 use then return user specified value
60 10-Sep-99 maagrawa 115.18 Added p_start_date to determine_los.
61 27-Oct-99 lmcdonal 115.19 Added some debugging messages.
62 18-Nov-99 gperry 115.20 Corrected error messages.
63 19-Nov-99 gperry 115.21 Fixed bug 4000.
64 set l_effective_date based on
65 whether life event mode is being
66 used.
67 22-Nov-99 pbodla 115.22 Bug 3299 : Passed bnfts_bal_id
68 to ben_determine_date.main when
69 comp_lvl_det_cd is evaluated.
70 11-Jan-00 pbodla 115.23 run_rule function added to evaluate
71 los_calc_rl
72 24-Jan-00 lmcdonal 115.24 Add:
73 los ohd calc, Bug 4069.
74 los date-to-use-rl, bug 1161293.
75 comp_calc_rl, bug 1118118.
76 los_calc_rl, bug 1161293.
77 Modify run_rule to return date.
78 27-Jan-00 tguy 115.25 bug 1167919/4470
79 11-Feb-00 bbulusu 115.26 bug 4068
80 18-Feb-00 maagrawa 115.27 Removed the error
81 BEN_91849_COMP_OBJECT_VAL_NULL from
82 determine_age and determine_los
83 so as to run without any comp.
84 object (1169627).
85 22-Feb-00 gperry 115.28 Fixed WWBUG 1118118.
86 23-Feb-00 tguy 115.29 Fixed WWBUG 1120685,1161287,1178659
87 28-Feb-00 tguy 115.30 Fixed WWBUG 1161293
88 29-Feb-00 tguy 115.31 Fixed WWBUG 1179545
89 07-Mar-00 tguy 115.32 Fixed Inherited codes in LOS
90 determination
91 14-Mar-00 maagrawa 115.33 Added p_calc_bal_to_date to
92 determine_compensation.
93 If this date is not null, use
94 it to calculate balances.
95 29-Mar-00 mmogel 115.34 I changed the message numbers from
96 91382 to 91832 in the message name
97 BEN_91382_PACKAGE_PARAM_NULL and the
98 91383 to 92833 in the message name
99 BEN_91833_CURSOR_RETURN_NO_ROW and
100 added tokens to other messages to
101 enhance current error messages
102 24-may-00 tmicheal 115.35 bug 4844 fixed by intialising local value
103 l_value to p_value
104 29-may-00 mhoyes 115.36 - Added p_per_dob to determine_age.
105 21-jun-00 gperry 115.37 Fixed WWBUG 1329380.
106 Now drive off salary basis and if
107 that doesn't exist then drive
108 off normal hours and frequency.
109 26-jun-00 gperry 115.38 Added p_parent_person_id to
110 determine_age so we can drive off
111 the parent person id for
112 dependents.
113 27-jun-00 gperry 115.39 Added age_calc_rl support
114 25-aug-00 kmahendr 115.40 Fixed WWBUG 1386872
115 If cmp_lvl_fct is stated compensation and determination code is
116 Use previous October 1 and if the participant is not having salary
117 for previous october, then current salary will be taken for coverage
118 calculation
119 11-sep-00 tilak 115.41 bug 1393301 compensation calacualtion used per_annualization_
120 factor instead of hardcoded hour and weeks
121 14-sep-00 rchase 115.42 Leapfrog version based on 115.40.
122 Include person_id as input to formula call in run_rule.
123 This allows for processing individuals without assignments
124 14-sep-00 jcarpent 115.43 Merge version of 115.41 and 115.42
125 28-nov-00 tilak 115.44 for determinfing date l_person passed as param
126 bug : 1510665
127 30-nov-00 gperry 115.45 Fixed WWBUG 1522319.
128 For compensation if you can't find
129 value as of determination date then
130 use life event occurred date.
131 04-dec-00 tilak 115.46 changed in 115.44 is reversed , the validation for the
132 dependent is added in bendetdt
133 07-dec-00 rchase 115.47 Bug 1518211. Determine age parm p_dob
134 is now an in/out parm.
135 Leapfrog version based on 115.43.
136 07-dec-00 jcarpent 115.48 Merge version of 115.47+115.46.
137
138 07-dec-00 TMathers 115.49 Backport of 115.45 fixes issue.
139
140 12-jan-01 kmahendr 115.50 Merge version of 115.48+115.49
141 13-feb-01 tilak 115.51 bug : 1632450 c_stated_salary cursor
142 changed to retrive annaulizatin factor and
143 prposed_salary from same period
144 14-feb-01 tilak 115.53 brout forward of lattest version 51 with leapfrog of 52
145 27-Aug-01 ikasire 115.54 Bug 1949361 fixes to jurisdiction code
146 06-dec-01 tjesumic 115.55 Salary calcualtion date setermination changed , bug 2124453
147 11-dec-01 tjesumic 115.55 Salary calcualtion date setermination changed , bug 2124453
148 12-dec-01 tjesumic 115.56 approved='y' added in c_stated salary to fetch
149 only approved salary
150 10-Jan-01 ikasire 115.59 Bug-2168233 fixed- determine_los - added order by clause
151 to get the right records for adjested service date
152 30-jan-02 tjesumic 115.60 bug 2180602 procedure set_taxunnit_context
153 called to set the tax_unit_id
154 context before calling get_value
155 15-feb-02 pabodla 115.61 bug 2202764 fix: When determine_compensation procedure
156 is called from CWB plan if salary not found then return
157 salary as 0.
158 26-Mar-02 kmahendr 115.62 Bug#1833008 - Added a parameter to determine_comp-
159 sation and multiple assignment is handled.
160 30-Apr-02 kmahendr 115.63 Added token to message 91832
161 08-Jun-02 pabodla 115.64 Do not select the contingent worker
162 assignment when assignment data is
163 fetched.
164 04-Sep-02 kmahendr 115.65 New acty_ref_perd_cd added.
165 16 Dec 02 hnarayan 115.66 Added NOCOPY hint
166
167 9-JAN-2002 glingapp 115.67 Bug 2519393
168 Created new message 93298 to make message more informative.
169 Changed the cursor 'c_opt_typ_cd'.
170 28-APR-2003 rpgupta 115.69 Bug 2924077
171 Added a check on effective dates while
172 picking up details of the person's spouse
173 The same chk has been added to all cursors
174 using per_contact_relationships
175 14-JUL-2003 glingapp 115.70 added outer join on per_periods_of_service in
176 cursor c_person of determine_los
177 24-Sep-2003 ikasire 115.71 Bug 3151737 - made the join to pay_all_payrolls_f
178 as outer join and added the effective date
179 clause for the same table.
180 25-Nov-2003 bmanyam 115.72 Bug:3265142. Changed the cursor c_stated_salary.
181 Fetching salary from per_assignment_extra_info.
182 aei_information6 column for 'Benefit Assignment'
183 records (ie. assignment_type = 'B')
184 16-Dec-2003 ikasire 115.73 Bug: 3315997 When salary is not defined return with NULL
185 17-Dec-03 vvprabhu 115.74 Added the assignment for g_debug at the start
186 08-Apr-04 pbodla 115.75 FONM : use cvg start date or rate start
187 date from processing.
188 p_effective_date is overloaded.
189 18-Apr-04 mmudigon 115.76 Universal Eligibility
190 13-Jul-04 rpgupta 115.77 3752107: If no salary/ benefit balance is found on
191 the determine date, 1st look at the closest assignment
192 after the determine date
193 13-aug-04 tjesumic 115,78 fonm parameter added
194 28-sep-04 kmahendr 115.79 Bug#3899510 - cursor c_stated_salary modified
195 to take assignment_id
196 13-Oct-04 mmudigon 115.80 Forward port from 115.74.11510.4
197 Bug 3818453. Added call to get_latest_paa_id()
198 17-Feb-05 ssarkar 115.81 Bug 4120426--Called load_warnings in proc determine_compensation.
199 23-feb-05 ssarkar 115.82 changed to_char(p_effective_date, 'DD-MON-RRRR')
200 to fnd_date.date_to_displaydate(p_effective_date).
201 07-apr-05 nhunur 115.83 apply fnd_number on what FF returns in run_rule.
202 27-Apr-05 mmudigon 115.84 OIC integration. Addition of the
203 codes 'OICAMTEARNED' and'OICAMTPAID'
204 02-May-05 bmanyam 115.85 Bug 4343063. Fixed a Typo..
205
206 07-Jul-05 Tmathers 115.86 Bug 4455689. changed
207 asg.assignment_id = nvl(p_assignment_id,asg.assignment_id)
208 into
209 ((asg.assignment_id = p_assignment_id)
210 or (p_assignment_id is null))
211 to fixe performance issue in 9.2.0.5.0
212 19-jul-05 ssarkar 115.87 Bug : 4500760 : determine_date.main should be bypassed for OIC.
213 21-jul-05 ssarkar 115.88 Bug : 4500760 : l_clf.proration_flag mapped to 'T'/'F' for OIC evaluation.
214 27-jul-05 pbodla 115.89 Bug : 4509422 : p_init_msg_list is
215 passed oic procedure to clear message
216 stack.
217 08-sep-05 pbodla 115.90 Bug 4509422 : Even if the oic code
218 errors still we need to continue with
219 0 values populated to l_comp_earned, l_comp_paid
220 This is temp fix, once iic code is changed
221 to handle no person data or setup not found cases
222 then this error can be un commented again.
223 06-Mar-2005 bmanyam 115.91 5075001 - To calculate Hourly Compensation (PHR)
224 divide the ANNUAL_VALUE by profile BEN_HRLY_ANAL_FCTR.
225 27-Mar-2006 abparekh 115.92 Bug 5118063 : CWB : Fixed issue : when there is single pay
226 proposal with salary as zero, then p_value
227 remains unassigned.
228 23-May-2006 nhunur 115.93 5187379 : avoid using secure views.
229 18-Aug-2006 kmahendr 115.94 5473471 - Output parameter is assigned
230 a value before return in determine_compensation
231 29-Mar-2007 rtagarra 115.95 Bug 5931412 : To take care of short months case.
232 09-Apr-2007 rtagarra 115.96 Bug 5931412 : Leap Year Case.
233 08-Jun-2007 sshetty 115.97 Bug 6067726. Annualization factor will be
234 derived from per_time_periods based on
235 the payroll info if the
236 Pay Annualization Factor value on Salary
237 basis is null.
238 31-Oct-2007 rtagarra 115.98 Bug 6601294: Fixed cursor c_stated_salary.
239 19-Nov-2007 rtagarra 115.99 Bug 6627329 : Fixed cursor c_stated_salary for Perform Issue.
240 23-Sep-2008 velvanop 115.100 Bug 7313778 : For determining the compensation of a rehired employee,
241 rehire date should be used instead of the hire date.
242 21-Nov-2009 krupani 115.101 Bug 9143371 : In procedure determine_age, l_effective_date was not
243 getting initialized while running Maintain Designee Eligibility. Fixed the same.
244 11-May-2012 amnaraya 120.9.12000000.10 Bug 14023180: In procedure determine_los, l_effective_date was not
245 getting initialized while running Maintain Designee Eligibility. Fixed the same.
246 08-Jun-2012 amnaraya 120.9.12000000.12 Bug 14161081: In Procedure determine_compensation for
247 the comp_src_cd='RL', l_value will never return null as the mandatory return
248 parameter is number for the rule. So, instead of null check decided to check
249 for a -ve number so as to decide whether any compnsation value is returned or not.
250 19-Jun-2012 amnaraya 120.9.12000000.13 Bug 14161081: In Procedure determine_compensation for the
251 comp_src_cd='RL'; Set l_value to 0 if the assignment record exists as of
252 determination date.
253 09-Aug-2012 amnaraya 120.9.12000000.15 Bug 14456969. In Procedure determine_compensation for the
254 comp_src_cd='RL'; When an Employee is terminated and re-hired, changed to code
255 to fetch the Compensation from the re-hire assignment instead of the
256 Ex-Emp assignment.
257 */
258 --------------------------------------------------------------------------------
259 --
260 g_package varchar2(30) := 'ben_derive_factors.';
261 g_debug boolean := hr_utility.debug_enabled;
262 --
263 procedure run_rule
264 (p_formula_id in number,
265 p_rule_type in varchar2 default 'NUMBER',
266 p_effective_date in date,
267 p_lf_evt_ocrd_dt in date,
268 p_business_group_id in number,
269 p_person_id in number,
270 p_pgm_id in number,
271 p_pl_id in number,
272 p_oipl_id in number,
273 p_plip_id in number,
274 p_ptip_id in number,
275 p_ret_date out nocopy date,
276 p_ret_val out nocopy number,
277 p_fonm_cvg_strt_dt in date default null,
278 p_fonm_rt_strt_dt in date default null) is
279 --
280 l_package varchar2(80) := g_package||'.run_rule';
281 l_result number;
282 l_outputs ff_exec.outputs_t;
283 l_loc_rec hr_locations_all%rowtype;
284 l_ass_rec per_all_assignments_f%rowtype;
285 l_pil_rec ben_per_in_ler%rowtype;
286 l_pl_rec ben_pl_f%rowtype;
287 l_oipl_rec ben_oipl_f%rowtype;
288 l_jurisdiction_code varchar2(30);
289 l_env ben_env_object.g_global_env_rec_type;
290 --
291 begin
292 --
293 if g_debug then
294 hr_utility.set_location ('Entering '||l_package,10);
295 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
296 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
297 end if;
298 --
299 if p_oipl_id is not null then
300 --
301 ben_comp_object.get_object(p_rec => l_oipl_rec,
302 p_oipl_id => p_oipl_id);
303 --
304 end if;
305 --
306 if p_pl_id is not null then
307 --
308 ben_comp_object.get_object(p_rec => l_pl_rec,
309 p_pl_id => p_pl_id);
310 --
311 end if;
312 --
313 -- Call formula initialise routine
314 --
315 ben_person_object.get_object(p_person_id => p_person_id,
316 p_rec => l_ass_rec);
317 --
318 if l_ass_rec.assignment_id is null then
319 --
320 ben_person_object.get_benass_object(p_person_id => p_person_id,
321 p_rec => l_ass_rec);
322 --
323 end if;
324 --
325 ben_person_object.get_object(p_person_id => p_person_id,
326 p_rec => l_pil_rec);
327 --
328 if l_ass_rec.location_id is not null then
329 --
330 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
331 p_rec => l_loc_rec);
332 --
333 --Bug 1949361 commented the following code
334 /*
335 if l_loc_rec.region_2 is not null then
336 --
337 l_jurisdiction_code :=
338 pay_mag_utils.lookup_jurisdiction_code
339 (p_state => l_loc_rec.region_2);
340 --
341 end if;
342 */
343 --
344 end if;
345 --
346 l_outputs := benutils.formula
347 (p_formula_id => p_formula_id,
348 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
349 p_assignment_id => l_ass_rec.assignment_id,
350 p_organization_id => l_ass_rec.organization_id,
351 p_business_group_id => p_business_group_id,
352 p_pgm_id => p_pgm_id,
353 p_pl_id => p_pl_id,
354 p_pl_typ_id => l_pl_rec.pl_typ_id,
355 p_opt_id => l_oipl_rec.opt_id,
356 p_ler_id => l_pil_rec.ler_id,
357 p_jurisdiction_code => l_jurisdiction_code,
358 --RCHASE Bug Fix - Include person_id for evaluating other than participants
359 p_param1 => 'PERSON_ID',
360 p_param1_value => to_char(p_person_id),
361 p_param2 => 'BEN_IV_RT_STRT_DT' ,
362 p_param2_value => fnd_date.date_to_canonical(p_fonm_rt_strt_dt) ,
363 p_param3 => 'BEN_IV_CVG_STRT_DT' ,
364 p_param3_value => fnd_date.date_to_canonical(p_fonm_cvg_strt_dt)
365 );
366 --
367 if p_rule_type = 'NUMBER' then
368 --
369 begin
370 --
371 p_ret_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value);
372 p_ret_date := null;
373 --
374 exception
375 --
376 when others then
377 --
378 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
379 fnd_message.set_token('PROC',l_package);
380 fnd_message.set_token('FORMULA',p_formula_id);
381 fnd_message.set_token('PARAMETER',l_outputs(l_outputs.first).name);
382 fnd_message.raise_error;
383 --
384 end;
385 --
386 elsif p_rule_type = 'DATE' then
387 --
388 begin
389 --
390 p_ret_date :=
391 fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
392 p_ret_val := null;
393 --
394 exception
395 --
396 when others then
397 --
398 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
399 fnd_message.set_token('PROC',l_package);
400 fnd_message.set_token('FORMULA',p_formula_id);
401 fnd_message.set_token('PARAMETER',l_outputs(l_outputs.first).name);
402 fnd_message.raise_error;
403 --
404 end;
405 --
406 else
407 --
408 if g_debug then
409 hr_utility.set_location ('INVALID RULE TYPE PASSED'||l_package,99);
410 end if;
411 --
412 end if;
413 --
414 if g_debug then
415 hr_utility.set_location ('Leaving '||l_package,99);
416 end if;
417 --
418 exception
419 --
420 when others then
421 --
422 p_ret_date := null;
423 p_ret_val := null;
424 raise;
425 end;
426 --
427 PROCEDURE determine_compensation
428 (p_comp_lvl_fctr_id in number,
429 p_person_id in number,
430 p_pgm_id in number default null,
431 p_pl_id in number default null,
432 p_oipl_id in number default null,
433 p_comp_obj_mode in boolean default true,
434 p_per_in_ler_id in number,
435 p_business_group_id in number,
436 p_perform_rounding_flg in boolean default true,
437 p_effective_date in date,
438 p_lf_evt_ocrd_dt in date default null,
439 p_fonm_cvg_strt_dt in date default null,
440 p_fonm_rt_strt_dt in date default null,
441 p_calc_bal_to_date in date default null,
442 p_cal_for in varchar2 default null,
443 p_value out nocopy number) IS
444 --
445 l_proc varchar2(100) := g_package||'determine_compensation';
446 l_effective_date date;
447 l_comp_lvl_uom varchar2(30);
448 l_comp_src_cd varchar2(30);
449 l_comp_lvl_det_cd varchar2(30);
450 l_comp_lvl_det_rl varchar2(30);
451 l_rndg_cd varchar2(30);
452 l_rndg_rl number;
453 l_date date;
454 l_value number;
455 l_dummy_date date;
456 --
457 cursor c_clf is
458 select clf.comp_lvl_uom,
459 clf.comp_src_cd,
460 clf.comp_lvl_det_cd,
461 clf.comp_lvl_det_rl,
462 clf.rndg_cd,
463 clf.rndg_rl,
464 clf.bnfts_bal_id,
465 clf.defined_balance_id,
466 clf.sttd_sal_prdcty_cd,
467 clf.comp_calc_rl,
468 clf.start_day_mo,
469 clf.end_day_mo,
470 clf.start_year,
471 clf.end_year,
472 clf.proration_flag
473 from ben_comp_lvl_fctr clf
474 where p_comp_lvl_fctr_id = clf.comp_lvl_fctr_id;
475 --
476 l_clf c_clf%rowtype;
477 --
478 -- cursor modified to get salary across all the assignments
479 --
480 --bug#3899510 - to fix rehire issue assignment id is added
481 cursor c_stated_salary (p_primary_flag varchar2, p_assignment_id number) is
482 select ppp.proposed_salary_n proposed_salary,
483 ppb.pay_basis,
484 ppb.pay_annualization_factor,
485 paf.period_type payroll,
486 asg.normal_hours,
487 asg.payroll_id,
488 asg.frequency,
489 asg.assignment_id,
490 ppp.change_date -- Bug:3265142. Added this for order-by clause
491 from per_pay_proposals ppp,
492 -- per_assignments_f asg,
493 per_all_assignments_f asg,
494 per_pay_bases ppb,
495 pay_all_payrolls_f paf,
496 per_all_people_f per
497 where per.person_id = p_person_id
498
499 /* Bug:3265142 Start: Fetching salary from per_pay_proposals for assignment_type = 'E'
500 and per_assignment_extra_info.aei_information6 assignment_type = 'B' (Refer UNIONed-query).
501 */
502 --and asg.assignment_type <> 'C'
503 and asg.assignment_type = 'E'
504 -- Bug:3265142 End
505
506 and asg.person_id = per.person_id
507 -- 4455689
508 and ((asg.assignment_id = p_assignment_id)
509 or (p_assignment_id is null))
510 -- and asg.primary_flag = 'Y'
511 and ((asg.primary_flag = p_primary_flag)
512 or ( p_primary_flag is null))
513 -- End of 4455689
514 and ppb.pay_basis_id = asg.pay_basis_id
515 and asg.payroll_id = paf.payroll_id(+) -- Bug 3151737 Why do we need payroll here???
516 and l_date
517 between nvl(paf.effective_start_date,l_date)
518 and nvl(paf.effective_end_date,l_date)
519 AND nvl(ppp.approved,'N') = 'Y'
520 -- and l_effective_date
521 and l_date
522 between asg.effective_start_date
523 and asg.effective_end_date
524 and l_date -- l_effective_date
525 between per.effective_start_date
526 and per.effective_end_date
527 and asg.assignment_id = ppp.assignment_id
528 and ppp.change_date <= l_date
529 /* Bug:3265142 Start: Fetching salary from per_pay_proposals for assignment_type = 'E'
530 and per_assignment_extra_info.aei_information6 assignment_type = 'B' (Refer UNIONed-query).
531 */
532 UNION
533 select fnd_number.canonical_to_number(aei.aei_information6) proposed_salary,
534 ppb.pay_basis,
535 ppb.pay_annualization_factor,
536 paf.period_type payroll,
537 asg.normal_hours,
538 asg.payroll_id,
539 asg.frequency,
540 asg.assignment_id assignment_id,
541 fnd_date.canonical_to_date(aei.aei_information8) change_date
542 from --per_assignments_f asg,
543 per_all_assignments_f asg,
544 per_assignment_extra_info aei,
545 per_pay_bases ppb,
546 pay_all_payrolls_f paf,
547 per_all_people_f per
548 where per.person_id = p_person_id
549 and asg.assignment_type = 'B'
550 and asg.person_id = per.person_id
551 -- 4455689
552 and ((asg.assignment_id = p_assignment_id)
553 or (p_assignment_id is null))
554 and ((asg.primary_flag = p_primary_flag)
555 or ( p_primary_flag is null))
556 -- End of 4455689
557 and ppb.pay_basis_id = asg.pay_basis_id
558 and asg.payroll_id = paf.payroll_id(+)
559 and l_date between nvl(paf.effective_start_date,l_date)and nvl(paf.effective_end_date,l_date)
560 and l_date between asg.effective_start_date and asg.effective_end_date -- 3752107
561 --and l_date <= asg.effective_end_date
562 and l_date between per.effective_start_date and per.effective_end_date
563 and asg.assignment_id = aei.assignment_id
564 and not exists (select 1
565 from per_all_assignments_f asg,
566 per_all_people_f per
567 where per.person_id = p_person_id
568 and asg.assignment_type = 'E'
569 and asg.person_id = per.person_id
570 and l_date between asg.effective_start_date and asg.effective_end_date
571 and l_date between per.effective_start_date and per.effective_end_date)
572 order by 8, 9 desc; -- Bug 6601294
573 --order by asg.assignment_id,ppp.change_date desc;
574 -- Bug:3265142 End
575
576 --
577 l_salary c_stated_salary%rowtype;
578
579 --
580 --Bug 2202764
581 --
582 /*cursor c_opt_typ_cd is
583 select opt.OPT_TYP_CD
584 from BEN_PL_F pln, BEN_PL_TYP_f opt
585 where opt.pl_typ_id = pln.pl_typ_id
586 and opt.OPT_TYP_CD = 'CWB'
587 and l_date
588 between pln.effective_start_date
589 and pln.effective_end_date
590 and l_effective_date
591 between opt.effective_start_date
592 and opt.effective_end_date;*/
593
594 /* Bug2519393 Changed the cursor c_opt_typ_cd.
595 The old cursor was not using any of the parameters 'pgm_id','pl_id', 'oipl_id' and the cursor always
596 returned 'CWB' as option type. The if condition using this cursor always passes as true irrespective of whether
597 the plan type is CWB or not.
598 */
599
600 cursor c_opt_typ_cd is
601 select distinct ptp.OPT_TYP_CD
602 from BEN_PL_TYP_f ptp
603 where ( p_pl_id is null
604 or exists ( select 1
605 from ben_pl_f pl1
606 where pl1.pl_id = p_pl_id
607 and ptp.OPT_TYP_CD = 'CWB'
608 and pl1.pl_typ_id = ptp.pl_typ_id
609 and pl1.business_group_id = p_business_group_id
610 and l_effective_date between pl1.effective_start_date and pl1.effective_end_date))
611 and ( p_oipl_id is null
612 or exists ( select 1
613 from ben_pl_f pl2 , ben_oipl_f oipl2
614 where oipl2.oipl_id = p_oipl_id
615 and ptp.OPT_TYP_CD = 'CWB'
616 and pl2.pl_id = oipl2.pl_id
617 and pl2.pl_typ_id = ptp.pl_typ_id
618 and pl2.business_group_id = p_business_group_id
619 and oipl2.business_group_id = p_business_group_id
620 and l_effective_date between oipl2.effective_start_date and oipl2.effective_end_date
621 and l_effective_date between pl2.effective_start_date and pl2.effective_end_date) )
622 and ( p_pgm_id is null
623 or exists ( select 1
624 from ben_ptip_f ptip
625 where ptip.pgm_id = p_pgm_id
626 and ptp.OPT_TYP_CD = 'CWB'
627 and ptip.pl_typ_id = ptp.pl_typ_id
628 and ptip.business_group_id = p_business_group_id
629 and l_effective_date between ptip.effective_start_date and ptip.effective_end_date) )
630 and ptp.business_group_id = p_business_group_id
631 and l_effective_date between ptp.effective_start_date and ptp.effective_end_date;
632
633 --
634 l_opt_typ_cd c_opt_typ_cd%rowtype;
635 --
636
637 cursor c_person_balance(p_date date) is
638 select pbb.val, bnb.name
639 from ben_per_bnfts_bal_f pbb,
640 ben_bnfts_bal_f bnb
641 where pbb.person_id = p_person_id
642 and pbb.business_group_id = p_business_group_id
643 and pbb.bnfts_bal_id = bnb.bnfts_bal_id
644 and p_date
645 between bnb.effective_start_date
646 and bnb.effective_end_date
647 and p_date
648 between pbb.effective_start_date
649 and pbb.effective_end_date
650 and pbb.bnfts_bal_id = l_clf.bnfts_bal_id;
651 --
652 l_person_balance c_person_balance%rowtype;
653 --
654 cursor c_assignment is
655 select assignment_id
656 from per_all_assignments_f paf
657 where primary_flag = 'Y'
658 and person_id = p_person_id
659 and paf.assignment_type <> 'C'
660 and business_group_id = p_business_group_id
661 and l_effective_date
662 between paf.effective_start_date
663 and paf.effective_end_date
664 order by decode(paf.assignment_type, 'E',1,2);
665
666 Cursor c_ass is
667 select min(effective_start_date)
668 From per_all_assignments_f ass
669 where person_id = p_person_id
670 and ass.assignment_type <> 'C'
671 and primary_flag = 'Y' ;
672
673 -- 3752197
674 Cursor c_ass_after_detdt is
675 --Start Bug 14456969: Modified the cursor conditions to identify Ex-Emp Rehire
676 SELECT min (ass.effective_start_date)
677 FROM per_all_people_f per
678 , per_all_assignments_f ass
679 WHERE per.person_id = p_person_id
680 AND per.person_id = ass.person_id
681 AND per.current_employee_flag = 'Y'
682 AND ass.assignment_type NOT IN ('B', 'C')
683 AND primary_flag = 'Y'
684 AND trunc (ass.effective_start_date)
685 BETWEEN trunc (per.effective_start_date)
686 AND trunc (per.effective_end_date)
687 AND ass.effective_start_date >= l_date;
688 --End Bug 14456969
689
690 --Start Bug 14161081
691 --Start Bug 14456969: Modified the cursor conditions to identify Ex-Emp Rehire
692 Cursor c_exists_ass_as_of_detdt is
693 SELECT 1
694 FROM per_all_people_f per
695 , per_all_assignments_f ass
696 WHERE per.person_id = p_person_id
697 AND per.person_id = ass.person_id
698 AND per.current_employee_flag = 'Y'
699 AND ass.assignment_type NOT IN ('B', 'C')
700 AND ass.primary_flag = 'Y'
701 AND l_date
702 BETWEEN per.effective_start_date
703 AND per.effective_end_date
704 AND l_date
705 BETWEEN ass.effective_start_date
706 AND ass.effective_end_date;
707 --End Bug 14456969
708 l_exists_ass_as_of_detdt c_exists_ass_as_of_detdt%rowtype;
709 --End Bug 14161081
710
711 --Start Bug 14456969
712 --Added an Cursor to fetch the latest termination date
713 Cursor c_per_lst_term_dt is
714 SELECT max(per.effective_start_date)
715 FROM per_all_people_f per
716 WHERE per.person_id = p_person_id
717 AND per.current_employee_flag IS NULL
718 AND trunc (per.effective_start_date) >= trunc (l_date);
719
720 l_per_lst_term_dt date;
721
722 --Cursor to fetch the latest hire date
723 Cursor c_per_lst_hire_dt is
724 SELECT min(per.effective_start_date)
725 FROM per_all_people_f per
726 WHERE per.person_id = p_person_id
727 AND per.current_employee_flag = 'Y'
728 AND per.effective_start_date > l_per_lst_term_dt;
729
730 l_per_lst_hire_dt date;
731 --End Bug 14456969
732 --
733 cursor c_pgm is
734 select uses_all_asmts_for_rts_flag
735 from ben_pgm_f pgm
736 where pgm.pgm_id = p_pgm_id
737 and l_effective_date between
738 pgm.effective_start_date and pgm.effective_end_date;
739 --
740 cursor c_pln is
741 select use_all_asnts_for_rt_flag
742 from ben_pl_f pln
743 where pln.pl_id = p_pl_id
744 and p_effective_date between
745 pln.effective_start_date and pln.effective_end_date;
746
747 cursor c_get_period_num (cp_payroll_id number,
748 cp_effective_date date) is
749 select ptp.period_num period_num
750 from per_time_periods ptp
751 where payroll_id=cp_payroll_id
752 and to_char(cut_off_date,'RRRR')= to_char(cp_effective_date,'RRRR')
753 order by 1 desc;
754 --
755 l_get_period_num c_get_period_num%rowtype;
756 l_rate_flag varchar2(200) := 'N';
757 l_assignment c_assignment%rowtype;
758 l_not_found boolean := false;
759 l_bnb_rec ben_bnfts_bal_f%ROWTYPE;
760 l_primary_flag varchar2(1):= 'Y';
761 l_assignment_id number := 0;
762 l_pay_annualization_factor number ;
763 l_assignment_action_id number;
764 --
765 -- FONM
766 l_orig_effective_date date;
767 --
768 l_start_date date;
769 l_end_date date;
770 l_ret_status varchar2(30);
771 l_msg_count number;
772 l_msg_data varchar2(2000);
773 l_comp_paid number;
774 l_comp_earned number;
775 BEGIN
776 --
777 g_debug := hr_utility.debug_enabled;
778 if g_debug then
779 hr_utility.set_location('Entering :'||l_proc,10);
780 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
781 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
782 end if;
783 --
784 if p_effective_date is null then
785 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
786 fnd_message.set_token('PACKAGE',l_proc);
787 fnd_message.set_token('PARAM','p_effective_date');
788 fnd_message.set_token('PROC','Derived Factors');
789 fnd_message.raise_error;
790 elsif p_person_id is null then
791 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
792 fnd_message.set_token('PACKAGE',l_proc);
793 fnd_message.set_token('PARAM','p_person_id');
794 fnd_message.set_token('PROC','Derived Factors');
795 fnd_message.raise_error;
796 elsif p_comp_lvl_fctr_id is null then
797 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
798 fnd_message.set_token('PACKAGE',l_proc);
799 fnd_message.set_token('PARAM','p_comp_lvl_fctr_id');
800 fnd_message.set_token('PROC','Derived Factors');
801 fnd_message.raise_error;
802 elsif p_business_group_id is null then
803 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
804 fnd_message.set_token('PACKAGE',l_proc);
805 fnd_message.set_token('PARAM','p_business_group_id');
806 fnd_message.set_token('PROC','Derived Factors');
807 fnd_message.raise_error;
808 end if;
809 --
810 if (p_comp_obj_mode and
811 p_pl_id is null and
812 p_oipl_id is null and
813 p_pgm_id is null) then
814 fnd_message.set_name('BEN','BEN_91849_COMP_OBJECT_VAL_NULL');
815 fnd_message.set_token('PACKAGE',l_proc);
816 fnd_message.set_token('PERSON_ID',p_person_id);
817 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
818 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
819 fnd_message.raise_error;
820 end if;
821 --
822 -- FONM
823 --if ben_manage_life_events.fonm = 'Y' then
824 --
825 -- FONM : calling procedures pass the p_effective_date as
826 -- nvl of fonm_rt_strt_dt, fonm_cvg_strt_dt, effective_date
827 -- tilak : new fonm paramter passes the value no more overring the dates
828 l_effective_date := nvl(nvl(p_fonm_rt_Strt_dt, p_fonm_cvg_strt_dt), p_lf_evt_ocrd_dt);
829 --
830
831 /* Bug 7313778: While determining the Compensation Level Derived factor eligibility of a Rehired Employee,
832 l_effective_date is set to null, because of which assignment is not picked and Compensation is
833 determined as of Hire Date instead of Rehire date*/
834 if(l_effective_date is null) then
835 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
836 end if;
837
838
839 l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
840 --
841 -- else
842 --
843 -- l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
844 --
845 -- l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
846 --
847 -- end if;
848
849 --
850 open c_clf;
851 --
852 fetch c_clf into l_clf;
853 if c_clf%notfound then
854 close c_clf;
855 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
856 fnd_message.set_token('PACKAGE',l_proc);
857 fnd_message.set_token('CURSOR','c_clf');
858 fnd_message.raise_error;
859 end if;
860 --
861 close c_clf;
862 --
863 --
864 if p_cal_for = 'R' then
865 --
866 if p_pgm_id is not null then
867 --
868 open c_pgm;
869 fetch c_pgm into l_rate_flag;
870 close c_pgm;
871 --
872 end if;
873 if l_rate_flag = 'N' then
874 --
875 open c_pln;
876 fetch c_pln into l_rate_flag;
877 close c_pln;
878 --
879 end if;
880 --
881 end if;
882 --
883 open c_assignment;
884 --
885 fetch c_assignment into l_assignment;
886 --
887 close c_assignment;
888 --
889 -- if the setup says sum all assignments then assign null to primary flag
890 if l_rate_flag = 'Y' then
891 --
892 l_primary_flag := null;
893 --
894 end if;
895 -- calculate date to be used in calculation.
896 --
897 if l_clf.comp_src_cd not in ('OICAMTEARNED','OICAMTPAID') then --bug 4500760
898 ben_determine_date.main
899 (p_date_cd => l_clf.comp_lvl_det_cd,
900 p_per_in_ler_id => p_per_in_ler_id,
901 p_person_id => p_person_id,
902 p_pgm_id => p_pgm_id,
903 p_pl_id => p_pl_id,
904 p_oipl_id => p_oipl_id,
905 p_comp_obj_mode => p_comp_obj_mode,
906 p_business_group_id => p_business_group_id,
907 p_formula_id => l_clf.comp_lvl_det_rl,
908 p_bnfts_bal_id => l_clf.bnfts_bal_id,
909 p_effective_date => l_orig_effective_date, -- FONM
910 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
911 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
912 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt,
913 p_returned_date => l_date);
914 end if;
915 --
916 if l_date is null then
917 --
918 l_date := l_effective_date;
919 --
920 end if;
921 --
922 if g_debug then
923 hr_utility.set_location('l_date :'||l_date,450);
924 end if;
925 if g_debug then
926 hr_utility.set_location('l_clf.comp_src_cd :'||l_clf.comp_src_cd,12);
927 end if;
928 if g_debug then
929 hr_utility.set_location('p_comp_lvl_fctr_id :'||p_comp_lvl_fctr_id,12);
930 end if;
931 if g_debug then
932 hr_utility.set_location('l_clf.bnfts_bal_id :'||l_clf.bnfts_bal_id,12);
933 end if;
934 --Start Bug 14456969
935 /*
936 If an employee is terminated and re-hired, the l_date should be reset to
937 the latest hire date.
938 */
939 open c_per_lst_term_dt;
940 fetch c_per_lst_term_dt into l_per_lst_term_dt;
941 close c_per_lst_term_dt;
942 if l_per_lst_term_dt is not null then
943 hr_utility.set_location('Latest Termination Date is '||l_per_lst_term_dt,1178);
944
945 open c_per_lst_hire_dt;
946 fetch c_per_lst_hire_dt into l_per_lst_hire_dt;
947 close c_per_lst_hire_dt;
948
949 hr_utility.set_location('Latest Hire Date is '||l_per_lst_hire_dt,1179);
950 l_date := l_per_lst_hire_dt;
951 end if;
952
953 --End Bug 14456969
954 --
955 if l_clf.comp_calc_rl is not null then
956 --
957 run_rule
958 (p_formula_id => l_clf.comp_calc_rl,
959 p_effective_date => l_date,
960 p_lf_evt_ocrd_dt => l_date, -- nvl(p_lf_evt_ocrd_dt, l_date), -- FONM why pass l_date 999
961 p_business_group_id => p_business_group_id,
962 p_person_id => p_person_id,
963 p_pgm_id => p_pgm_id,
964 p_pl_id => p_pl_id,
965 p_oipl_id => p_oipl_id,
966 p_plip_id => null,
967 p_ptip_id => null,
968 p_ret_date => l_dummy_date,
969 p_ret_val => p_value,
970 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
971 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt);
972 --
973 l_value := p_value;
974 /*AMN Bug 14161081
975 l_value will never return null as the mandatory return parameter is number
976 So, instead of null decided to check for a -ve number so as to decide
977 whether any compnsation value is returned or not
978 */
979 hr_utility.set_location('l_value :'||l_value,1171);--Bug 14161081
980 /*Start AMN Bug 14161081: If the assignment exists as of the current determination date
981 then reset the value from -1 to 0 so as to skip the re-evaluation
982 */
983 if l_value < 0 then
984 open c_exists_ass_as_of_detdt;
985 fetch c_exists_ass_as_of_detdt into l_exists_ass_as_of_detdt;
986 if c_exists_ass_as_of_detdt%FOUND then
987 l_value := 0;
988 hr_utility.set_location('c_exists_ass_as_of_detdt%FOUND',1172);
989 hr_utility.set_location('l_value reseted to :'||l_value,1173);
990 end if;
991 close c_exists_ass_as_of_detdt;
992 end if;
993 --End Bug 14161081
994 --
995 hr_utility.set_location('l_value :'||l_value,1174);--Bug 14161081
996 if l_value is null or l_value < 0 then --Bug 14161081
997 --
998 fnd_message.set_name('BEN','BEN_92319_SAL_BALANCE_NULL');
999 fnd_message.set_token('DATE',l_date);
1000 benutils.write(p_text=> fnd_message.get);
1001 --Start AMN Bug 14161081
1002 ---if date code is
1003 -- first of year,half year,quarter,month,semi month,previos oct 1
1004 -- then take the first salary
1005 open c_ass_after_detdt;
1006 fetch c_ass_after_detdt into l_date;
1007 close c_ass_after_detdt;
1008 hr_utility.set_location('Executing the rule with the new dt date :'||l_date,1175);
1009 --End AMN Bug 14161081
1010 if l_date is not null then
1011 run_rule
1012 (p_formula_id => l_clf.comp_calc_rl,
1013 p_effective_date => l_date, --Bug 14161081
1014 p_lf_evt_ocrd_dt => l_date, --Bug 14161081
1015 p_business_group_id => p_business_group_id,
1016 p_person_id => p_person_id,
1017 p_pgm_id => p_pgm_id,
1018 p_pl_id => p_pl_id,
1019 p_oipl_id => p_oipl_id,
1020 p_plip_id => null,
1021 p_ptip_id => null,
1022 p_ret_date => l_dummy_date,
1023 p_ret_val => p_value,
1024 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
1025 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt);
1026 --
1027 l_value := p_value;
1028 hr_utility.set_location('New date l_value :'||l_value,1176);
1029 else
1030 hr_utility.set_location('New Date is Null: Resetting the Value to -1',1177);
1031 l_value := -1;
1032 end if;
1033 --
1034 IF l_value IS NULL or l_value < 0 THEN--Bug 14161081
1035 p_value := 0;--Bug 14161081
1036 --
1037 fnd_message.set_name('BEN','BEN_92319_SAL_BALANCE_NULL');
1038 fnd_message.set_token('DATE',p_effective_date);
1039 benutils.write(p_text=> fnd_message.get);
1040 return;
1041 --
1042 END IF;
1043 --
1044 end if;
1045 --
1046 elsif l_clf.comp_src_cd in ('OICAMTEARNED','OICAMTPAID') then
1047
1048 hr_utility.set_location('Inside OIC call',20);
1049 declare
1050
1051 begin
1052 l_start_date := to_date(l_clf.start_day_mo||nvl(l_clf.start_year,to_char(l_date,'YYYY')),'DDMMYYYY');
1053 l_end_date := to_date(l_clf.end_day_mo||nvl(l_clf.end_year,to_char(l_date,'YYYY')),'DDMMYYYY');
1054 exception
1055 when others then
1056 fnd_message.set_name('BEN','BEN_92603_INVALID_DATE');
1057 fnd_message.raise_error;
1058 end;
1059
1060 if l_start_date > l_end_date then
1061 fnd_message.set_name('BEN','BEN_91824_START_DT_AFTR_END_DT');
1062 fnd_message.set_token('PROC',l_proc);
1063 fnd_message.set_token('START_DT',l_start_date);
1064 fnd_message.set_token('END_DT',l_end_date);
1065 fnd_message.raise_error;
1066 end if;
1067
1068 -- 4500760
1069 if l_clf.proration_flag = 'Y' then
1070 l_clf.proration_flag := FND_API.G_TRUE;
1071 else
1072 l_clf.proration_flag := FND_API.G_FALSE;
1073 end if;
1074 -- 4500760
1075
1076 cn_get_comm_pmt_paid_grp.get_comm_and_paid_pmt
1077 (p_api_version => 1
1078 ,p_person_id => p_person_id
1079 ,p_start_date => l_start_date
1080 ,p_end_date => l_end_date
1081 ,p_target_currency_code => l_clf.comp_lvl_uom
1082 ,p_proration_flag => l_clf.proration_flag
1083 ,p_init_msg_list => FND_API.G_TRUE -- 'TRUE'
1084 ,x_return_status => l_ret_status
1085 ,x_msg_count => l_msg_count
1086 ,x_msg_data => l_msg_data
1087 ,x_comp_earned => l_comp_earned
1088 ,x_comp_paid => l_comp_paid
1089 ,x_new_start_date => l_dummy_date
1090 ,x_new_end_date => l_dummy_date);
1091
1092 hr_utility.set_location('msg = '||substr(l_msg_data,1,100), 999);
1093 hr_utility.set_location('l_start_date = '||l_start_date, 999);
1094 hr_utility.set_location('l_end_date = '||l_end_date, 999);
1095 hr_utility.set_location('l_clf.comp_lvl_uom = '||l_clf.comp_lvl_uom, 999);
1096 hr_utility.set_location('l_clf.proration_flag = '||l_clf.proration_flag, 999);
1097 hr_utility.set_location('l_msg_count = '||l_msg_count, 999);
1098 if l_ret_status in ('E','U') then
1099 -- Bug 4509422 : Even if the oic code errors still we need to
1100 -- continue with 0 values populated to l_comp_earned, l_comp_paid
1101 -- This is temp fix, once iic code is changed to handle no person data
1102 -- or setup not found cases then this error can be un commented again.
1103 --
1104 /*
1105 fnd_message.set_name('BEN','BEN_93934_CWB_EMP_SAVE_API_ERR');
1106 fnd_message.set_token('NAME','CN_GET_COMM_PMT_PAID_GRP');
1107 fnd_message.set_token('MESSAGE',substr(l_msg_data,1,100));
1108 fnd_message.raise_error;
1109 */
1110 benutils.write(p_text=> substr(l_msg_data,1,100));
1111 l_comp_earned := 0;
1112 l_comp_paid := 0;
1113 --
1114 end if;
1115
1116 if l_clf.comp_src_cd = 'OICAMTEARNED' then
1117 l_value := l_comp_earned;
1118 else
1119 l_value := l_comp_paid;
1120 end if;
1121 --
1122 IF l_value IS NULL THEN
1123 --
1124 hr_utility.set_location('null value returned ',20);
1125 return;
1126 --
1127 END IF;
1128 --
1129 elsif l_clf.comp_src_cd = 'STTDCOMP' then
1130 --
1131 if ben_whatif_elig.g_stat_comp is not null then
1132 --
1133 -- This is the case where benmngle is called from the
1134 -- watif form and user wants to simulate compensation level
1135 -- changed. Use the user supplied compensation value rather
1136 -- than the fetched value.
1137 --
1138 l_value := ben_whatif_elig.g_stat_comp;
1139 --
1140 else
1141 --
1142 open c_stated_salary (l_primary_flag, l_assignment.assignment_id);
1143 --
1144 fetch c_stated_salary into l_salary;
1145 if c_stated_salary%NOTFOUND then
1146 --
1147 -- then take current salary
1148 --
1149 l_not_found := true;
1150 --
1151 end if;
1152 --
1153 close c_stated_salary;
1154 ---if date code is
1155 -- first of year,half year,quarter,month,semi month,previos oct 1
1156 -- then take the first salary
1157
1158 if l_not_found then
1159
1160 if l_clf.comp_lvl_det_cd in
1161 ('AFDCPPY','AFDCSPPY','AFDCPPQ','AFDCM','AFDCSM','APOCT1','AFDECY' ) then
1162 -- 3752107
1163 -- Find the sal on the min ass start date after the determined date
1164 open c_ass_after_detdt;
1165 fetch c_ass_after_detdt into l_date;
1166 close c_ass_after_detdt;
1167 --
1168 hr_utility.set_location('next date after det date is:'||l_date, 233);
1169
1170 --
1171 -- there is an assignment after the det dt, find the sal as of that date
1172 -- else, l_not_found would anyway remain true and it would call c_ass
1173
1174 if l_date is not null then
1175 open c_stated_salary (l_primary_flag, l_assignment.assignment_id);
1176 fetch c_stated_salary into l_salary;
1177 hr_utility.set_location('l_salary with nxt dt is:'||l_salary.proposed_salary, 234);
1178 if c_stated_salary%NOTFOUND then
1179 l_not_found := true;
1180 --
1181 else
1182 l_not_found := false;
1183 end if;
1184 close c_stated_salary;
1185 end if;
1186
1187 if l_not_found then
1188 hr_utility.set_location('no sal found - resort to old means:'||l_salary.proposed_salary, 234);
1189 -- end 3752107
1190 open c_ass ;
1191 fetch c_ass into l_date ;
1192 close c_ass ;
1193 hr_utility.set_location('min l_date is:'||l_date, 234);
1194
1195 open c_stated_salary (l_primary_flag, l_assignment.assignment_id);
1196 fetch c_stated_salary into l_salary;
1197 hr_utility.set_location('l_salary with min dt is:'||l_salary.proposed_salary, 234);
1198 if c_stated_salary%NOTFOUND then
1199 -- then take current salary
1200 l_not_found := true;
1201 --
1202 else
1203 l_not_found := false;
1204 end if;
1205
1206 --
1207 close c_stated_salary;
1208 --
1209 end if;
1210
1211
1212 end if ;
1213
1214
1215 end if ;
1216
1217 if l_not_found then
1218 --
1219 l_date := l_effective_date;
1220 --
1221 open c_stated_salary (l_primary_flag , l_assignment.assignment_id);
1222 --
1223 fetch c_stated_salary into l_salary;
1224 --
1225 if c_stated_salary%NOTFOUND then
1226 --
1227 --Bug 2202764
1228 --
1229 l_opt_typ_cd.opt_typ_cd := 'YYY';
1230 open c_opt_typ_cd;
1231 fetch c_opt_typ_cd into l_opt_typ_cd;
1232 close c_opt_typ_cd;
1233 if nvl(l_opt_typ_cd.opt_typ_cd, 'YYY') ='CWB' then
1234 l_value := 0;
1235 l_salary.proposed_salary := 0;
1236 else
1237 fnd_message.set_name('BEN','BEN_93298_SAL_NOT_DFND');
1238 --Bug 2519393 Message made more clear.
1239 -- start bug # 4185334 -- changed the tokens --
1240 fnd_message.set_token('PARMA','c_stated_salary');
1241 fnd_message.set_token('PARMB',l_proc);
1242 fnd_message.set_token('PARMC',fnd_date.date_to_displaydate(p_effective_date,calendar_aware=>2));
1243 fnd_message.set_token('PARM1',p_person_id);
1244 --- end bug # 4185334
1245 /*
1246 fnd_message.raise_error;
1247 */
1248 --
1249 -- BUG 3315997
1250 --
1251 l_value := NULL ;
1252 p_value := l_value ;
1253 --
1254 --start bug # 4185334 -- called load_warnings
1255 ben_warnings.load_warning
1256 (p_application_short_name => 'BEN',
1257 p_message_name => 'BEN_93298_SAL_NOT_DFND',
1258 p_parma => 'c_stated_salary',
1259 p_parmb => l_proc,
1260 p_parmc => fnd_date.date_to_displaydate(p_effective_date,calendar_aware=>2),
1261 p_parm1 => p_person_id,
1262 p_person_id => p_person_id);
1263 -- end bug # 4185334
1264 benutils.write(p_text=> fnd_message.get);
1265 return;
1266 --
1267 end if;
1268 --
1269 end if;
1270 --
1271 close c_stated_salary;
1272 --
1273 end if;
1274 --
1275 if g_debug then
1276 hr_utility.set_location('Primary flag'||l_primary_flag,100);
1277 end if;
1278 if g_debug then
1279 hr_utility.set_location('l_effective_date'||l_effective_date,101);
1280 end if;
1281 for i in c_stated_salary (l_primary_flag, null)
1282 loop
1283 --
1284 exit when l_salary.proposed_salary = 0;
1285 --
1286 if i.assignment_id <> l_assignment_id then
1287 if g_debug then
1288 hr_utility.set_location('Pay basis'|| i.pay_basis, 102);
1289 end if;
1290 if i.pay_basis is not null then
1291 --
1292 -- Assumption no multi assignment for annualization factor
1293 l_pay_annualization_factor := i.pay_annualization_factor;
1294 --fix for bug#6067726.
1295 --Considering that the value entered
1296 -- in pay annualization_factor in salary basis form
1297 -- always overrides the period number of payroll
1298
1299 if l_pay_annualization_factor is null then
1300 open c_get_period_num (i.payroll_id, l_effective_date);
1301 fetch c_get_period_num into l_get_period_num;
1302 close c_get_period_num;
1303 l_pay_annualization_factor := l_get_period_num.period_num;
1304 end if;
1305 l_value := i.proposed_salary *
1306 nvl(l_pay_annualization_factor,1) + nvl(l_value,0);
1307 --
1308 elsif i.frequency is not null and
1309 i.normal_hours is not null then
1310 --
1311 if i.frequency = 'D' then
1312 --
1313 -- assumption is 5 days a week * 52 weeks in a year = 260 working days
1314 --
1315 l_value := i.proposed_salary * (i.normal_hours*260) + nvl(l_value,0);
1316 --
1317 elsif i.frequency = 'W' then
1318 --
1319 l_value := i.proposed_salary * (i.normal_hours*52) + nvl(l_value,0);
1320 --
1321 elsif i.frequency = 'M' then
1322 --
1323 l_value := i.proposed_salary * (i.normal_hours*12) + nvl(l_value,0);
1324 --
1325 elsif i.frequency = 'Y' then
1326 --
1327 l_value := i.proposed_salary + nvl(l_value,0);
1328 --
1329 else
1330 --
1331 fnd_message.set_name('BEN','BEN_92463_INVALID_FREQUENCY');
1332 fnd_message.set_token('PACKAGE',l_proc);
1333 fnd_message.set_token('PERSON_ID',p_person_id);
1334 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
1335 fnd_message.set_token('COMP_LVL_FCTR_ID',p_comp_lvl_fctr_id);
1336 fnd_message.set_token('PGM_ID',p_pgm_id);
1337 fnd_message.set_token('PL_ID',p_pl_id);
1338 fnd_message.set_token('OIPL_ID',p_oipl_id);
1339 fnd_message.raise_error;
1340 --
1341 end if;
1342 --
1343 end if;
1344 --
1345 end if;
1346 l_assignment_id := i.assignment_id;
1347 --
1348 end loop;
1349 --
1350 /* Bug 5118063 : If there is only one proposal which is zero, then l_value returns as
1351 unassigned, so set it properly
1352 */
1353 if l_salary.proposed_salary = 0 and l_value is null
1354 then
1355 --
1356 l_value := l_salary.proposed_salary ;
1357 --
1358 end if;
1359 --
1360 end if; -- what if end if
1361 --
1362 -- if the annualization factor is null get it from profile
1363 /* 5075001 -- Commented this part.
1364 if l_pay_annualization_factor is null then
1365 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
1366 if l_pay_annualization_factor is null then
1367 l_pay_annualization_factor := 2080;
1368 end if;
1369 end if;
1370 */
1371 -- Now take annualized salary and translate it into the appropriate
1372 -- acty ref period as defined by the plan or program
1373 --
1374 -- Per Week
1375 --
1376 if g_debug then
1377 hr_utility.set_location('l_clf.sttd_sal_prdcty_cd :'||l_clf.sttd_sal_prdcty_cd,18);
1378 end if;
1379 if l_clf.sttd_sal_prdcty_cd = 'PWK' then
1380 --
1381 l_value := l_value/52;
1382 --
1383 -- Bi-Weekly
1384 --
1385 elsif l_clf.sttd_sal_prdcty_cd = 'BWK' then
1386 --
1387 l_value := l_value/26;
1388 --
1389 -- Semi-Monthly
1390 --
1391 elsif l_clf.sttd_sal_prdcty_cd = 'SMO' then
1392 --
1393 l_value := l_value/24;
1394 --
1395 -- Per Quarter
1396 --
1397 elsif l_clf.sttd_sal_prdcty_cd = 'PQU' then
1398 --
1399 l_value := l_value/4;
1400 --
1401 -- Per Year
1402 -- don't really need to do this since l_value is already periodized,
1403 -- but to make it easier to read we'll go ahead and go through the
1404 -- motions.
1405 --
1406 elsif l_clf.sttd_sal_prdcty_cd = 'PYR' then
1407 --
1408 l_value := l_value;
1409 --
1410 -- Semi-Annual
1411 --
1412 elsif l_clf.sttd_sal_prdcty_cd = 'SAN' then
1413 --
1414 l_value := l_value/2;
1415 --
1416 -- Monthly
1417 --
1418 elsif l_clf.sttd_sal_prdcty_cd = 'MO' then
1419 --
1420 l_value := l_value/12;
1421 --
1422 --
1423 elsif l_clf.sttd_sal_prdcty_cd = 'PHR' then
1424 --
1425 -- 5075001 : Hourly Compensation. Get from Profile
1426 -- Else, default to 2080.
1427 --
1428 l_pay_annualization_factor := NVL(to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR')),2080);
1429 --
1430 l_value := l_value/l_pay_annualization_factor;
1431 --
1432 -- Unknown periodicity, Error out
1433 else
1434 --
1435 fnd_message.set_name('BEN','BEN_92465_INVALID_PRDCTY_CD');
1436 fnd_message.set_token('PACKAGE',l_proc);
1437 fnd_message.set_token('PERSON_ID',p_person_id);
1438 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
1439 fnd_message.set_token('COMP_LVL_FCTR_ID',p_comp_lvl_fctr_id);
1440 fnd_message.set_token('PGM_ID',p_pgm_id);
1441 fnd_message.set_token('PL_ID',p_pl_id);
1442 fnd_message.set_token('OIPL_ID',p_oipl_id);
1443 fnd_message.raise_error;
1444 --
1445 end if;
1446 --
1447 elsif l_clf.comp_src_cd = 'BALTYP' then
1448 --
1449 if ben_whatif_elig.g_bal_comp is not null then
1450 --
1451 -- This is the case where benmngle is called from the
1452 -- watif form and user wants to simulate compensation level
1453 -- changed. Use the user supplied compensation value rather
1454 -- than the fetched value.
1455 --
1456 l_value := ben_whatif_elig.g_bal_comp;
1457 --
1458 else
1459
1460
1461 ben_derive_part_and_rate_facts.set_taxunit_context
1462 (p_person_id => p_person_id
1463 ,p_business_group_id => p_business_group_id
1464 ,p_effective_date => l_effective_date
1465 ) ;
1466
1467 --
1468 open c_assignment;
1469 --
1470 fetch c_assignment into l_assignment;
1471 --
1472 close c_assignment;
1473 --
1474 -- Bug 3818453. Pass assignment_action_id to get_value() to
1475 -- improve performance
1476 --
1477 l_assignment_action_id :=
1478 ben_derive_part_and_rate_facts.get_latest_paa_id
1479 (p_person_id => p_person_id
1480 ,p_business_group_id => p_business_group_id
1481 ,p_effective_date => nvl(p_calc_bal_to_date,l_date));
1482
1483 if l_assignment_action_id is not null then
1484 --
1485 begin
1486 l_value :=
1487 pay_balance_pkg.get_value(l_clf.defined_balance_id
1488 ,l_assignment_action_id);
1489 exception
1490 when others then
1491 l_value := null ;
1492 end ;
1493 --
1494 --
1495 end if ;
1496
1497 if l_value is null then
1498 fnd_message.set_name('BEN' ,'BEN_92318_BEN_BALANCE_NULL');
1499 fnd_message.set_token('DATE' ,nvl(p_calc_bal_to_date,l_date));
1500 benutils.write(p_text=> fnd_message.get);
1501 return;
1502 end if;
1503
1504 --
1505 -- old code prior to 3818453
1506 --
1507 /* ---- exception is not handled in the function
1508 begin
1509 l_value := pay_balance_pkg.get_value
1510 (l_clf.defined_balance_id,
1511 l_assignment.assignment_id,
1512 nvl(p_calc_bal_to_date,l_date));
1513 exception
1514 when others then
1515 l_value := null ;
1516 end ;
1517 --
1518 IF l_value IS NULL THEN
1519 begin
1520 if l_clf.comp_lvl_det_cd in
1521 ('AFDCPPY','AFDCSPPY','AFDCPPQ','AFDCM','AFDCSM','APOCT1','AFDECY' ) then
1522
1523 -- 3752107
1524 -- Find the bal on the min ass start date after the determined date
1525 open c_ass_after_detdt;
1526 fetch c_ass_after_detdt into l_date;
1527 close c_ass_after_detdt;
1528
1529 -- there is an assignment after the det dt, find the sal as of that date
1530 -- else, l_not_found would anyway remain true and it would call c_ass
1531
1532 if l_date is not null then
1533 begin
1534 l_value := pay_balance_pkg.get_value
1535 (l_clf.defined_balance_id,
1536 l_assignment.assignment_id,
1537 l_date);
1538 exception
1539 when others then
1540 l_value := null;
1541 end;
1542 end if; -- l_date is not null
1543
1544 if l_value is null then
1545 -- end 3752107
1546
1547 open c_ass ;
1548 fetch c_ass into l_date ;
1549 close c_ass ;
1550 if g_debug then
1551 hr_utility.set_location( ' in first of year ' || l_clf.comp_lvl_det_cd || l_date, 450);
1552 end if;
1553 l_value := pay_balance_pkg.get_value
1554 (l_clf.defined_balance_id,
1555 l_assignment.assignment_id,
1556 l_date);
1557
1558 end if ;--l_value is null
1559 end if;--l_clf.comp_lvl_det_cd in
1560 exception
1561 when others then
1562 l_value := null ;
1563 end ;
1564
1565 Begin
1566
1567 IF l_value IS NULL THEN
1568 --
1569 -- Person does not have a balance, recheck if they have a balance
1570 -- as of the life event occurred date or effective date.
1571 -- Fix for bug 216.
1572 --
1573 fnd_message.set_name('BEN','BEN_92318_BEN_BALANCE_NULL');
1574 fnd_message.set_token('DATE',l_date);
1575 benutils.write(p_text=> fnd_message.get);
1576 l_value :=
1577 pay_balance_pkg.get_value(l_clf.defined_balance_id,
1578 l_assignment.assignment_id,
1579 p_effective_date);
1580 --
1581 IF l_value IS NULL THEN
1582 --
1583 fnd_message.set_name('BEN','BEN_92318_BEN_BALANCE_NULL');
1584 fnd_message.set_token('DATE',p_effective_date);
1585 benutils.write(p_text=> fnd_message.get);
1586 RETURN;
1587 --
1588 END IF;
1589 --
1590 END IF;
1591 --
1592 exception
1593 when others then
1594 fnd_message.set_name('BEN','BEN_92318_BEN_BALANCE_NULL');
1595 fnd_message.set_token('DATE',p_effective_date);
1596 benutils.write(p_text=> fnd_message.get);
1597 RETURN;
1598 end ;
1599
1600 End If ; */
1601 end if;
1602 --
1603 elsif l_clf.comp_src_cd = 'BNFTBALTYP' then
1604 --
1605 if ben_whatif_elig.g_bnft_bal_comp is not null then
1606 --
1607 -- This is the case where benmngle is called from the
1608 -- watif form and user wants to simulate compensation level
1609 -- changed. Use the user supplied compensation value rather
1610 -- than the fetched value.
1611 --
1612 l_value := ben_whatif_elig.g_bnft_bal_comp;
1613 --
1614 else
1615 --
1616 if g_debug then
1617 hr_utility.set_location( ' calc_bal_date ' || p_calc_bal_to_date,450) ;
1618 end if;
1619 if g_debug then
1620 hr_utility.set_location( ' l_date ' || l_date,450) ;
1621 end if;
1622 open c_person_balance(nvl(p_calc_bal_to_date,l_date));
1623 --
1624 fetch c_person_balance into l_person_balance;
1625 l_value := l_person_balance.val;
1626 --
1627 close c_person_balance;
1628 --
1629 if l_value is null then
1630 if l_clf.comp_lvl_det_cd in
1631 ('AFDCPPY','AFDCSPPY','AFDCPPQ','AFDCM','AFDCSM','APOCT1','AFDECY' ) then
1632
1633 -- 3752107
1634 -- Find the per bal on the min ass start date after the determined date
1635 open c_ass_after_detdt;
1636 fetch c_ass_after_detdt into l_date;
1637 close c_ass_after_detdt;
1638
1639 -- there is an assignment after the det dt, find the sal as of that date
1640 -- else, l_not_found would anyway remain true and it would call c_ass
1641
1642 if l_date is not null then
1643 open c_person_balance(l_date);
1644 --
1645 fetch c_person_balance into l_person_balance;
1646 l_value := l_person_balance.val;
1647 --
1648 close c_person_balance;
1649 end if;
1650
1651 if l_value is null then
1652 -- end 3752107
1653 -- end paste
1654
1655 open c_ass ;
1656 fetch c_ass into l_date ;
1657 close c_ass ;
1658 if g_debug then
1659 hr_utility.set_location( ' in first of year ' || l_clf.comp_lvl_det_cd || l_date, 450);
1660 end if;
1661 open c_person_balance(l_date);
1662 --
1663 fetch c_person_balance into l_person_balance;
1664 l_value := l_person_balance.val;
1665 --
1666 close c_person_balance;
1667 end if; --l_value is null -- 3752107
1668 end if;--l_clf.comp_lvl_det_cd in
1669
1670 if l_value is null then
1671 --
1672 -- FONM : This cache routine should fetch the data as of
1673 -- cvg based effective_date.
1674 --
1675 ben_person_object.get_object(p_bnfts_bal_id => l_clf.bnfts_bal_id,
1676 p_rec => l_bnb_rec);
1677 --
1678 fnd_message.set_name('BEN','BEN_92317_PER_BALANCE_NULL');
1679 fnd_message.set_token('NAME',l_bnb_rec.name);
1680 fnd_message.set_token('DATE',l_date);
1681 benutils.write(p_text=> fnd_message.get);
1682 --
1683 l_date := l_effective_date;
1684 --
1685 open c_person_balance(l_date);
1686 --
1687 fetch c_person_balance into l_person_balance;
1688 l_value := l_person_balance.val;
1689 --
1690 close c_person_balance;
1691 --
1692 IF l_value IS NULL THEN
1693 --
1694 --Bug 2202764
1695 l_opt_typ_cd.opt_typ_cd := 'YYY';
1696 open c_opt_typ_cd;
1697 fetch c_opt_typ_cd into l_opt_typ_cd;
1698 close c_opt_typ_cd;
1699 if nvl(l_opt_typ_cd.opt_typ_cd, 'YYY') ='CWB' then
1700 l_value := 0;
1701 p_value := l_value;
1702 else
1703 fnd_message.set_name('BEN','BEN_92317_PER_BALANCE_NULL');
1704 fnd_message.set_token('NAME',l_bnb_rec.name);
1705 fnd_message.set_token('DATE',p_effective_date);
1706 benutils.write(p_text=> fnd_message.get);
1707 end if;
1708 RETURN;
1709 --
1710 END IF;
1711 --
1712 end if;
1713 --
1714 End if ;
1715 end if;
1716 --
1717 end if;
1718 --
1719 -- perform appropriate rounding based on the source table.
1720 -- rounding_cd or rule cannot both be null, perform_rounding_flag
1721 -- must be true....
1722 --
1723 if (l_clf.rndg_cd is not null or
1724 l_clf.rndg_rl is not null) and
1725 p_perform_rounding_flg = true
1726 and l_value is not null then
1727 --
1728 p_value := benutils.do_rounding
1729 (p_rounding_cd => l_clf.rndg_cd,
1730 p_rounding_rl => l_clf.rndg_rl,
1731 p_value => l_value,
1732 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1733 --
1734 else
1735 --
1736 p_value := l_value;
1737 --
1738 end if;
1739 --
1740 if g_debug then
1741 hr_utility.set_location('p_value :'||p_value,10);
1742 end if;
1743 if g_debug then
1744 hr_utility.set_location('Leaving :'||l_proc,10);
1745 end if;
1746 --
1747 exception
1748 --
1749 when others then
1750 --
1751 p_value := null;
1752 raise;
1753 --
1754 end determine_compensation;
1755 --
1756 ---------------------------------------------------------------------------
1757 --
1758 PROCEDURE determine_age
1759 (p_person_id in number
1760 --RCHASE add out
1761 ,p_per_dob in out nocopy date
1762 --End RCHASE
1763 ,p_age_fctr_id in number
1764 ,p_pgm_id in number default null
1765 ,p_pl_id in number default null
1766 ,p_oipl_id in number default null
1767 ,p_comp_obj_mode in boolean default true
1768 ,p_per_in_ler_id in number
1769 ,p_effective_date in date
1770 ,p_lf_evt_ocrd_dt in date default null
1771 ,p_fonm_cvg_strt_dt in date default null
1772 ,p_fonm_rt_strt_dt in date default null
1773 ,p_business_group_id in number
1774 ,p_perform_rounding_flg in boolean default true
1775 ,p_value out nocopy number
1776 ,p_change_date out nocopy date
1777 ,p_parent_person_id in number default null
1778 )
1779 is
1780 --
1781 l_value number;
1782 l_date date;
1783 l_proc varchar2(80) := g_package||'determine_age';
1784 l_effective_date date;
1785 l_person_id number;
1786 -- FONM
1787 l_orig_effective_date date;
1788 -- FONM
1789 --
1790 cursor c_per is
1791 select per.date_of_birth
1792 from per_all_people_f per
1793 where per.person_id = p_person_id
1794 and per.business_group_id = p_business_group_id
1795 and l_effective_date
1796 between per.effective_start_date
1797 and per.effective_end_date;
1798 --
1799
1800 cursor c_per_spouse(l_person_id number) is
1801 select per.date_of_birth
1802 from per_contact_relationships ctr,
1803 per_all_people_f per
1804 where ctr.person_id = l_person_id
1805 and per.person_id = ctr.contact_person_id
1806 and per.business_group_id = p_business_group_id
1807 and ctr.personal_flag = 'Y'
1808 and ctr.contact_type = 'S'
1809 and l_effective_date
1810 between per.effective_start_date
1811 and per.effective_end_date
1812 /* bug 2924077 */
1813 and l_effective_date
1814 between nvl(ctr.date_start, hr_api.g_sot)
1815 and nvl(ctr.date_end, hr_api.g_eot);
1816 --
1817 cursor c_per_depen_first(l_person_id number) is
1818 select per.date_of_birth
1819 from per_contact_relationships ctr,
1820 per_all_people_f per
1821 where ctr.person_id = l_person_id
1822 and per.person_id = ctr.contact_person_id
1823 and per.business_group_id = p_business_group_id
1824 and ctr.personal_flag = 'Y'
1825 and ctr.dependent_flag = 'Y'
1826 and l_effective_date
1827 between per.effective_start_date
1828 and per.effective_end_date
1829 /* bug 2924077 */
1830 and l_effective_date
1831 between nvl(ctr.date_start, hr_api.g_sot)
1832 and nvl(ctr.date_end, hr_api.g_eot);
1833
1834 --
1835 cursor c_per_child_first(l_person_id number) is
1836 select per.date_of_birth
1837 from per_contact_relationships ctr,
1838 per_all_people_f per
1839 where ctr.person_id = l_person_id
1840 and per.person_id = ctr.contact_person_id
1841 and per.business_group_id = p_business_group_id
1842 and ctr.personal_flag = 'Y'
1843 and ctr.contact_type in ('C','O','A','T')
1844 and l_effective_date
1845 between per.effective_start_date
1846 and per.effective_end_date
1847 /* bug 2924077 */
1848 and l_effective_date
1849 between nvl(ctr.date_start, hr_api.g_sot)
1850 and nvl(ctr.date_end, hr_api.g_eot);
1851
1852 --
1853 cursor c_per_depen_oldest(l_person_id number) is
1854 select min(per.date_of_birth)
1855 from per_contact_relationships ctr,
1856 per_all_people_f per
1857 where ctr.person_id = l_person_id
1858 and per.person_id = ctr.contact_person_id
1859 and per.business_group_id = p_business_group_id
1860 and ctr.personal_flag = 'Y'
1861 and ctr.dependent_flag = 'Y'
1862 and l_effective_date
1863 between per.effective_start_date
1864 and per.effective_end_date
1865 /* bug 2924077 */
1866 and l_effective_date
1867 between nvl(ctr.date_start, hr_api.g_sot)
1868 and nvl(ctr.date_end, hr_api.g_eot)
1869 order by per.date_of_birth;
1870 --
1871 cursor c_per_child_oldest(l_person_id number) is
1872 select min(per.date_of_birth)
1873 from per_contact_relationships ctr,
1874 per_all_people_f per
1875 where ctr.person_id = l_person_id
1876 and per.person_id = ctr.contact_person_id
1877 and per.business_group_id = p_business_group_id
1878 and ctr.personal_flag = 'Y'
1879 and ctr.contact_type in ('C','O','A','T')
1880 and l_effective_date
1881 between per.effective_start_date
1882 and per.effective_end_date
1883 /* bug 2924077 */
1884 and l_effective_date
1885 between nvl(ctr.date_start, hr_api.g_sot)
1886 and nvl(ctr.date_end, hr_api.g_eot)
1887 order by per.date_of_birth;
1888 --
1889 cursor c_per_depen_young(l_person_id number) is
1890 select max(per.date_of_birth)
1891 from per_contact_relationships ctr,
1892 per_all_people_f per
1893 where ctr.person_id = l_person_id
1894 and per.person_id = ctr.contact_person_id
1895 and per.business_group_id = p_business_group_id
1896 and ctr.personal_flag = 'Y'
1897 and ctr.dependent_flag = 'Y'
1898 and l_effective_date
1899 between per.effective_start_date
1900 and per.effective_end_date
1901 /* bug 2924077 */
1902 and l_effective_date
1903 between nvl(ctr.date_start, hr_api.g_sot)
1904 and nvl(ctr.date_end, hr_api.g_eot)
1905 order by per.date_of_birth;
1906 --
1907 cursor c_per_child_young(l_person_id number) is
1908 select max(per.date_of_birth)
1909 from per_contact_relationships ctr,
1910 per_all_people_f per
1911 where ctr.person_id = l_person_id
1912 and per.business_group_id = p_business_group_id
1913 and per.person_id = ctr.contact_person_id
1914 and ctr.personal_flag = 'Y'
1915 and ctr.contact_type in ('C','O','A','T')
1916 and l_effective_date
1917 between per.effective_start_date
1918 and per.effective_end_date
1919 /* bug 2924077 */
1920 and l_effective_date
1921 between nvl(ctr.date_start, hr_api.g_sot)
1922 and nvl(ctr.date_end, hr_api.g_eot)
1923 order by per.date_of_birth;
1924 --
1925 l_per c_per%rowtype;
1926 --
1927 cursor c_per_extra (p_person_id in number) is
1928 select aei.aei_information1
1929 from per_all_assignments_f asg,
1930 per_assignment_extra_info aei
1931 where asg.person_id = p_person_id
1932 and asg.assignment_id = aei.assignment_id
1933 and asg.primary_flag = 'Y'
1934 and asg.assignment_type = 'B'
1935 and asg.business_group_id = p_business_group_id
1936 and aei.information_type = 'BEN_DERIVED'
1937 and l_effective_date
1938 between asg.effective_start_date
1939 and asg.effective_end_date;
1940 --
1941 l_per_extra c_per_extra%rowtype;
1942 --
1943 cursor c_agf is
1944 select agf.age_det_rl,
1945 agf.age_det_cd,
1946 agf.age_to_use_cd,
1947 agf.age_uom,
1948 agf.rndg_cd,
1949 agf.rndg_rl,
1950 agf.age_calc_rl
1951 from ben_age_fctr agf
1952 where agf.age_fctr_id = p_age_fctr_id;
1953 --
1954 l_agf c_agf%rowtype;
1955 --
1956 l_per_dob date;
1957 begin
1958 --
1959 g_debug := hr_utility.debug_enabled;
1960 if g_debug then
1961 hr_utility.set_location('Entering: '||l_proc,10);
1962 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
1963 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
1964 end if;
1965 --
1966 l_per_dob := p_per_dob;
1967 --
1968 if p_effective_date is null then
1969 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1970 fnd_message.set_token('PACKAGE',l_proc);
1971 fnd_message.set_token('PARAM','p_effective_date');
1972 fnd_message.set_token('PROC','Derived Factors');
1973 fnd_message.raise_error;
1974 elsif p_person_id is null then
1975 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1976 fnd_message.set_token('PACKAGE',l_proc);
1977 fnd_message.set_token('PARAM','p_person_id');
1978 fnd_message.set_token('PROC','Derived Factors');
1979 fnd_message.raise_error;
1980 elsif p_age_fctr_id is null then
1981 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1982 fnd_message.set_token('PACKAGE',l_proc);
1983 fnd_message.set_token('PARAM','p_age_fctr_id');
1984 fnd_message.set_token('PROC','Derived Factors');
1985 fnd_message.raise_error;
1986 elsif p_business_group_id is null then
1987 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1988 fnd_message.set_token('PACKAGE',l_proc);
1989 fnd_message.set_token('PARAM','p_business_group_id');
1990 fnd_message.set_token('PROC','Derived Factors');
1991 fnd_message.raise_error;
1992 end if;
1993 --
1994 /*
1995 if (p_pl_id is null and p_oipl_id is null and p_pgm_id is null) then
1996 fnd_message.set_name('BEN','BEN_91849_COMP_OBJECT_VAL_NULL');
1997 fnd_message.set_token('PACKAGE',l_proc);
1998 fnd_message.set_token('PERSON_ID',p_person_id);
1999 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
2000 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
2001 fnd_message.raise_error;
2002 end if;
2003 */
2004 --
2005 -- We need to drive off the correct person id when we are coming in from
2006 -- dependent eligibility since then we are passing the contact and not
2007 -- the person therefore we want to drive off the real person.
2008 --
2009 if p_parent_person_id is not null then
2010 --
2011 l_person_id := p_parent_person_id;
2012 --
2013 else
2014 --
2015 l_person_id := p_person_id;
2016 --
2017 end if;
2018 --
2019 --
2020 -- FONM
2021 --if ben_manage_life_events.fonm = 'Y' then
2022 --
2023 -- FONM : calling procedures pass the p_effective_date as
2024 -- nvl of fonm_rt_strt_dt, fonm_cvg_strt_dt, effective_date
2025 -- tilak : new fonm paramter passes the value no more overring the dates
2026
2027 -- Bug 9143371: If p_lf_evt_ocrd_dt is null, consider p_effective_date
2028 l_effective_date := nvl(nvl(nvl(p_fonm_rt_Strt_dt, p_fonm_cvg_strt_dt), p_lf_evt_ocrd_dt),p_effective_date);
2029 --
2030 l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2031 --
2032 -- else
2033 --
2034 -- l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2035 --
2036 -- l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2037 --
2038 -- end if;
2039 --
2040 open c_agf;
2041 fetch c_agf into l_agf;
2042 --
2043 if c_agf%notfound then
2044 close c_agf;
2045 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
2046 fnd_message.set_token('PACKAGE',l_proc);
2047 fnd_message.set_token('CURSOR','c_agf');
2048 fnd_message.raise_error;
2049 end if;
2050 close c_agf;
2051 --
2052 -- hr_utility.set_location('l_agf.age_to_use_cd -> '||l_agf.age_to_use_cd,511);
2053 -- hr_utility.set_location('p_person_id -> '||p_person_id,511);
2054 --
2055 if l_agf.age_to_use_cd = 'P' and l_agf.age_calc_rl is null then
2056 --
2057 -- Check if the date of birth is passed in
2058 --
2059 if p_per_dob is null then
2060 --
2061 open c_per;
2062 fetch c_per into l_per;
2063 close c_per;
2064 --
2065 else
2066 --
2067 l_per.date_of_birth := p_per_dob;
2068 --
2069 end if;
2070 --
2071 if l_per.date_of_birth is null then
2072 --
2073 p_value := null;
2074 return;
2075 --
2076 end if;
2077 --
2078 elsif l_agf.age_calc_rl is not null then
2079 --
2080 run_rule(p_formula_id => l_agf.age_calc_rl,
2081 p_rule_type => 'DATE',
2082 p_effective_date => l_orig_effective_date, -- FONM
2083 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2084 p_business_group_id => p_business_group_id,
2085 p_person_id => p_person_id,
2086 p_pgm_id => p_pgm_id,
2087 p_pl_id => p_pl_id,
2088 p_oipl_id => p_oipl_id,
2089 p_plip_id => null,
2090 p_ptip_id => null,
2091 p_ret_date => l_per.date_of_birth,
2092 p_ret_val => l_value,
2093 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2094 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt);
2095 --
2096 if l_per.date_of_birth is null then
2097 --
2098 p_value := null;
2099 return;
2100 --
2101 --RCHASE
2102 else
2103 p_per_dob:=l_per.date_of_birth;
2104 --End RCHASE
2105 end if;
2106 --
2107 if g_debug then
2108 hr_utility.set_location(l_per.date_of_birth,10);
2109 end if;
2110 --
2111 elsif l_agf.age_to_use_cd = 'PS' then
2112 --
2113 open c_per_spouse(l_person_id);
2114 --
2115 fetch c_per_spouse into l_per;
2116 --
2117 close c_per_spouse;
2118 --
2119 if l_per.date_of_birth is null then
2120 --
2121 p_value := null;
2122 return;
2123 --
2124 end if;
2125 --
2126 elsif l_agf.age_to_use_cd = 'PD1' then
2127 --
2128 open c_per_depen_first(l_person_id);
2129 --
2130 fetch c_per_depen_first into l_per;
2131 --
2132 close c_per_depen_first;
2133 --
2134 if l_per.date_of_birth is null then
2135 --
2136 p_value := null;
2137 return;
2138 --
2139 end if;
2140 --
2141 elsif l_agf.age_to_use_cd = 'PC1' then
2142 --
2143 open c_per_child_first(l_person_id);
2144 --
2145 fetch c_per_child_first into l_per;
2146 --
2147 close c_per_child_first;
2148 --
2149 if l_per.date_of_birth is null then
2150 --
2151 p_value := null;
2152 return;
2153 --
2154 end if;
2155 --
2156 elsif l_agf.age_to_use_cd = 'PDO' then
2157 --
2158 open c_per_depen_oldest(l_person_id);
2159 --
2160 fetch c_per_depen_oldest into l_per;
2161 --
2162 close c_per_depen_oldest;
2163 --
2164 if l_per.date_of_birth is null then
2165 --
2166 p_value := null;
2167 return;
2168 --
2169 end if;
2170 --
2171 elsif l_agf.age_to_use_cd = 'PCO' then
2172 --
2173 open c_per_child_oldest(l_person_id);
2174 --
2175 fetch c_per_child_oldest into l_per;
2176 --
2177 close c_per_child_oldest;
2178 --
2179 if l_per.date_of_birth is null then
2180 --
2181 p_value := null;
2182 return;
2183 --
2184 end if;
2185 --
2186 elsif l_agf.age_to_use_cd = 'PDY' then
2187 --
2188 open c_per_depen_young(l_person_id);
2189 --
2190 fetch c_per_depen_young into l_per;
2191 --
2192 close c_per_depen_young;
2193 --
2194 if l_per.date_of_birth is null then
2195 --
2196 p_value := null;
2197 return;
2198 --
2199 end if;
2200 --
2201 elsif l_agf.age_to_use_cd = 'PCY' then
2202 --
2203 open c_per_child_young(l_person_id);
2204 --
2205 fetch c_per_child_young into l_per;
2206 --
2207 close c_per_child_young;
2208 --
2209 if l_per.date_of_birth is null then
2210 --
2211 p_value := null;
2212 return;
2213 --
2214 end if;
2215 --
2216 elsif l_agf.age_to_use_cd = 'IA' then
2217 --
2218 open c_per_extra(p_person_id);
2219 --
2220 fetch c_per_extra into l_per_extra;
2221 --
2222 close c_per_extra;
2223 --
2224 if l_per_extra.aei_information1 is null then
2225 --
2226 p_value := null;
2227 return;
2228 --
2229 else
2230 --
2231 if l_agf.age_uom = 'YR' then
2232 l_value := to_number(l_per_extra.aei_information1);
2233 elsif l_agf.age_uom = 'MO' then
2234 l_value := to_number(l_per_extra.aei_information1)*12;
2235 elsif l_agf.age_uom = 'WK' then
2236 l_value := (to_number(l_per_extra.aei_information1)*365) / 7;
2237 elsif l_agf.age_uom = 'DY' then
2238 l_value := to_number(l_per_extra.aei_information1)*365;
2239 else
2240 l_value := to_number(l_per_extra.aei_information1);
2241 end if;
2242 --
2243 end if;
2244 --
2245 end if;
2246 --
2247 -- hr_utility.set_location('l_per.date_of_birth -> '||l_per.date_of_birth ,511);
2248 -- hr_utility.set_location('l_agf.age_det_cd -> '||l_agf.age_det_cd,511);
2249 --
2250 if l_agf.age_to_use_cd <> 'IA' or l_agf.age_calc_rl is not null then
2251 --
2252 ben_determine_date.main
2253 (p_date_cd => l_agf.age_det_cd,
2254 p_per_in_ler_id => p_per_in_ler_id,
2255 p_person_id => p_person_id ,
2256 p_pgm_id => p_pgm_id,
2257 p_pl_id => p_pl_id,
2258 p_oipl_id => p_oipl_id,
2259 p_comp_obj_mode => p_comp_obj_mode,
2260 p_business_group_id => p_business_group_id,
2261 p_formula_id => l_agf.age_det_rl,
2262 p_effective_date => l_orig_effective_date, -- FONM
2263 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2264 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2265 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt,
2266 p_returned_date => l_date,
2267 p_parent_person_id => p_parent_person_id );
2268
2269 if l_date is null then
2270 --
2271 l_date := l_effective_date;
2272 --
2273 end if;
2274 --
2275 -- Set return change date
2276 --
2277 p_change_date:=l_date;
2278 --
2279 -- Calculate in form of UOM
2280 --
2281 if g_debug then
2282 hr_utility.set_location('DOB'||l_per.date_of_birth,10);
2283 end if;
2284 if g_debug then
2285 hr_utility.set_location('DATE'||l_date,10);
2286 end if;
2287 if l_agf.age_uom = 'YR' then
2288 l_value := months_between(l_date,l_per.date_of_birth)/12;
2289 elsif l_agf.age_uom = 'MO' then
2290 l_value := months_between(l_date,l_per.date_of_birth);
2291 --
2292 -- months_between fails when calculated between 29th Jan,30th Jan AND 28th Feb,
2293 -- for months_between('28-Feb-RRRR','28-Jan-RRRR') it gives 1 but for months_between('28-Feb-RRRR','29/30-Jan-RRRR')
2294 -- it gives < 1 and again as per functionality of months_between for months_between('28-Feb-RRRR','31-Jan-RRRR') it gives 1.
2295 -- So code is made to work for this specific case.
2296 --Bug 5931412
2297 if substr(to_char(l_date,'DD-MON-YYYY'),4,3) = 'FEB'
2298 and substr(to_char(l_per.date_of_birth,'DD-MON-YYYY'),1,2) > '28'
2299 and substr(to_char(l_date,'DD-MON-YYYY'),1,2) in ('28','29') then
2300 --
2301 l_value := ceil(l_value);
2302 --
2303 end if;
2304 --Bug 5931412
2305
2306 elsif l_agf.age_uom = 'WK' then
2307 l_value := to_number(l_date - l_per.date_of_birth) / 7;
2308 elsif l_agf.age_uom = 'DY' then
2309 l_value := to_number(l_date - l_per.date_of_birth);
2310 else
2311 l_value := months_between(l_per.date_of_birth,l_date)/12;
2312 end if;
2313 --
2314 end if;
2315 --
2316 if g_debug then
2317 hr_utility.set_location('VALUE is '||l_value,10);
2318 end if;
2319 -- perform appropriate rounding based on the source table.
2320 -- rounding_cd or rule cannot both be null, perform_rounding_flag
2321 -- must be true....
2322 --
2323 if (l_agf.rndg_cd is not null or
2324 l_agf.rndg_rl is not null) and
2325 p_perform_rounding_flg = true
2326 and l_value is not null then
2327 --
2328 p_value := benutils.do_rounding
2329 (p_rounding_cd => l_agf.rndg_cd,
2330 p_rounding_rl => l_agf.rndg_rl,
2331 p_value => l_value,
2332 p_effective_date => l_effective_date);
2333 else
2334 --
2335 p_value := l_value;
2336 --
2337 end if;
2338 --
2339 if g_debug then
2340 hr_utility.set_location('Leaving: '||l_proc,10);
2341 end if;
2342 --
2343 exception
2344 --
2345 when others then
2346 --
2347 p_per_dob := l_per_dob;
2348 p_value := null;
2349 p_change_date := null;
2350 raise;
2351 --
2352 end determine_age;
2353 -----------------------------------------------------------------------------
2354 procedure determine_los
2355 (p_person_id in number,
2356 p_los_fctr_id in number,
2357 p_pgm_id in number default null,
2358 p_pl_id in number default null,
2359 p_oipl_id in number default null,
2360 p_comp_obj_mode in boolean default true,
2361 p_per_in_ler_id in number,
2362 p_effective_date in date,
2363 p_lf_evt_ocrd_dt in date,
2364 p_fonm_cvg_strt_dt in date default null,
2365 p_fonm_rt_strt_dt in date default null,
2366 p_business_group_id in number,
2367 p_perform_rounding_flg in boolean default true,
2368 p_value out nocopy number,
2369 p_start_date out nocopy date) is
2370 --
2371 l_proc varchar2(80) := g_package||'determine_los';
2372 l_effective_date date;
2373 l_value number;
2374 l_date date;
2375 l_start_date date;
2376 l_end_date date;
2377 l_dummy_num number;
2378 l_dummy_date date;
2379 --
2380 -- FONM
2381 l_orig_effective_date date;
2382 --
2383 cursor c_person is
2384 select pps.date_start,
2385 pps.adjusted_svc_date,
2386 ppf.original_date_of_hire
2387 from per_all_people_f ppf,
2388 per_periods_of_service pps
2389 where pps.person_id(+) = ppf.person_id
2390 and ppf.person_id = p_person_id
2391 and ppf.business_group_id = p_business_group_id
2392 and l_effective_date
2393 between ppf.effective_start_date
2394 and ppf.effective_end_date
2395 --Bug2168233 to get the right record from per_periods_of_service
2396 and l_effective_date >= pps.date_start(+) /*Bug 2973791 outer join added*/
2397 order by pps.date_start desc ;
2398 --
2399 l_person c_person%rowtype;
2400 --
2401 cursor c_elig_per is
2402 select pep.ovrid_svc_dt
2403 from ben_elig_per_f pep,
2404 ben_per_in_ler pil
2405 where pep.person_id = p_person_id
2406 and pep.business_group_id = p_business_group_id
2407 and nvl(pep.pl_id,-1) = nvl(p_pl_id,-1)
2408 and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
2409 and l_effective_date
2410 between pep.effective_start_date
2411 and pep.effective_end_date
2412 and pil.per_in_ler_id(+)=pep.per_in_ler_id
2413 and pil.business_group_id(+)=pep.business_group_id
2414 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
2415 or pil.per_in_ler_stat_cd is null -- outer join condition
2416 )
2417 ;
2418 --
2419 l_elig_per c_elig_per%rowtype;
2420 --
2421 cursor c_lsf is
2422 select lsf.los_det_rl,
2423 lsf.los_det_cd,
2424 lsf.los_uom,
2425 lsf.rndg_cd,
2426 lsf.rndg_rl,
2427 lsf.use_overid_svc_dt_flag,
2428 lsf.los_dt_to_use_cd,
2429 lsf.los_dt_to_use_rl,
2430 lsf.los_calc_rl
2431 from ben_los_fctr lsf
2432 where lsf.los_fctr_id = p_los_fctr_id
2433 and lsf.business_group_id = p_business_group_id;
2434 --
2435 l_lsf c_lsf%rowtype;
2436 --
2437 cursor c_person_extra is
2438 select aei.aei_information2 iasd,
2439 aei.aei_information13 idoh,
2440 aei.aei_information3 iohd
2441 from per_all_assignments_f asg,
2442 per_assignment_extra_info aei
2443 where asg.person_id = p_person_id
2444 and asg.assignment_id = aei.assignment_id
2445 and asg.primary_flag = 'Y'
2446 and asg.assignment_type = 'B'
2447 and asg.business_group_id = p_business_group_id
2448 and aei.information_type = 'BEN_DERIVED'
2449 and l_effective_date
2450 between asg.effective_start_date
2451 and asg.effective_end_date;
2452 --
2453 l_person_extra c_person_extra%rowtype;
2454 --
2455 begin
2456 g_debug := hr_utility.debug_enabled;
2457 if g_debug then
2458 hr_utility.set_location('Entering: '||l_proc,10);
2459 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
2460 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
2461
2462 end if;
2463 if p_effective_date is null then
2464 if g_debug then
2465 hr_utility.set_location('BEN_91832_PACKAGE_PARAM_NULL',10);
2466 end if;
2467 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
2468 fnd_message.set_token('PACKAGE',l_proc);
2469 fnd_message.set_token('PARAM','p_effective_date');
2470 fnd_message.set_token('PROC','Derived Factors');
2471 fnd_message.raise_error;
2472 elsif p_person_id is null then
2473 if g_debug then
2474 hr_utility.set_location('BEN_91832_PACKAGE_PARAM_NULL',10);
2475 end if;
2476 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
2477 fnd_message.set_token('PACKAGE',l_proc);
2478 fnd_message.set_token('PARAM','p_person_id');
2479 fnd_message.set_token('PROC','Derived Factors');
2480 fnd_message.raise_error;
2481 elsif p_los_fctr_id is null then
2482 if g_debug then
2483 hr_utility.set_location('BEN_91832_PACKAGE_PARAM_NULL',10);
2484 end if;
2485 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
2486 fnd_message.set_token('PACKAGE',l_proc);
2487 fnd_message.set_token('PARAM','p_los_fctr_id');
2488 fnd_message.set_token('PROC','Derived Factors');
2489 fnd_message.raise_error;
2490 elsif p_business_group_id is null then
2491 if g_debug then
2492 hr_utility.set_location('BEN_91832_PACKAGE_PARAM_NULL',10);
2493 end if;
2494 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
2495 fnd_message.set_token('PACKAGE',l_proc);
2496 fnd_message.set_token('PARAM','p_business_group_id');
2497 fnd_message.set_token('PROC','Derived Factors');
2498 fnd_message.raise_error;
2499 end if;
2500 --
2501 /*
2502 if (p_pl_id is null and p_oipl_id is null and p_pgm_id is null) then
2503 if g_debug then
2504 hr_utility.set_location('BEN_91849_COMP_OBJECT_VAL_NULL',10);
2505 end if;
2506 fnd_message.set_name('BEN','BEN_91849_COMP_OBJECT_VAL_NULL');
2507 fnd_message.set_token('PACKAGE',l_proc);
2508 fnd_message.set_token('PERSON_ID',p_person_id);
2509 fnd_message.set_token('BUSINESS_GROUP_ID',p_business_group_id);
2510 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
2511 fnd_message.raise_error;
2512 end if;
2513 */
2514
2515
2516 -- FONM
2517 --if ben_manage_life_events.fonm = 'Y' then
2518 --
2519 -- FONM : calling procedures pass the p_effective_date as
2520 -- nvl of fonm_rt_strt_dt, fonm_cvg_strt_dt, effective_date
2521 -- tilak : new fonm paramter passes the value no more overring the dates
2522 --AMN Bug 14023180 Added the nvl for p_effective_date
2523 l_effective_date := nvl(nvl(nvl(p_fonm_rt_Strt_dt, p_fonm_cvg_strt_dt), p_lf_evt_ocrd_dt),p_effective_date);
2524 --
2525 l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2526 --
2527 -- else
2528 --
2529 -- l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2530 --
2531 -- l_orig_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
2532 --
2533 -- end if;
2534
2535 --
2536
2537 open c_lsf;
2538 fetch c_lsf into l_lsf;
2539 if c_lsf%notfound then
2540 close c_lsf;
2541 if g_debug then
2542 hr_utility.set_location('BEN_91833_CURSOR_RETURN_NO_ROW',10);
2543 end if;
2544 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
2545 fnd_message.set_token('PACKAGE',l_proc);
2546 fnd_message.set_token('CURSOR','c_lsf');
2547 fnd_message.raise_error;
2548 end if;
2549 close c_lsf;
2550
2551 ben_determine_date.main
2552 (p_date_cd => l_lsf.los_det_cd,
2553 p_per_in_ler_id => p_per_in_ler_id,
2554 p_person_id => p_person_id,
2555 p_pgm_id => p_pgm_id,
2556 p_pl_id => p_pl_id,
2557 p_oipl_id => p_oipl_id,
2558 p_comp_obj_mode => p_comp_obj_mode,
2559 p_business_group_id => p_business_group_id,
2560 p_formula_id => l_lsf.los_det_rl,
2561 p_effective_date => l_orig_effective_date, -- FONM
2562 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2563 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2564 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt,
2565 p_returned_date => l_date);
2566 --
2567 if l_date is null then
2568 l_date := l_effective_date;
2569 end if;
2570 --
2571 open c_person;
2572 fetch c_person into l_person;
2573 if c_person%notfound then
2574 close c_person;
2575 if g_debug then
2576 hr_utility.set_location('BEN_91833_CURSOR_RETURN_NO_ROW',20);
2577 end if;
2578 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
2579 fnd_message.set_token('PACKAGE',l_proc);
2580 fnd_message.set_token('CURSOR','c_person');
2581 fnd_message.raise_error;
2582 end if;
2583 close c_person;
2584 --
2585 open c_person_extra;
2586 fetch c_person_extra into l_person_extra;
2587 close c_person_extra;
2588 --
2589 open c_elig_per;
2590 fetch c_elig_per into l_elig_per;
2591 close c_elig_per;
2592 --
2593 if l_lsf.los_calc_rl is not null then
2594 run_rule(
2595 p_formula_id => l_lsf.los_calc_rl,
2596 p_rule_type => 'DATE',
2597 p_effective_date => l_orig_effective_date, -- FONM
2598 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2599 p_business_group_id => p_business_group_id,
2600 p_person_id => p_person_id,
2601 p_pgm_id => p_pgm_id,
2602 p_pl_id => p_pl_id,
2603 p_oipl_id => p_oipl_id,
2604 p_plip_id => null,
2605 p_ptip_id => null,
2606 p_ret_date => l_dummy_date,
2607 p_ret_val => p_value,
2608 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2609 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt);
2610 else
2611 --
2612 if l_lsf.los_dt_to_use_cd = 'OHD' then
2613 -- Original hire date
2614 if l_person.original_date_of_hire is not null then
2615 l_start_date := l_person.original_date_of_hire;
2616 else
2617 l_start_date := l_person.date_start;
2618 end if;
2619 elsif l_lsf.los_dt_to_use_cd ='DOH' then
2620 if l_lsf.use_overid_svc_dt_flag = 'Y' then
2621 if l_elig_per.ovrid_svc_dt is not null then
2622 l_start_date := l_elig_per.ovrid_svc_dt ;
2623 else
2624 l_start_date := l_person.date_start;
2625 end if;
2626 else
2627 l_start_date := l_person.date_start;
2628 end if;
2629 elsif l_lsf.los_dt_to_use_cd ='ASD' then
2630 if l_lsf.use_overid_svc_dt_flag = 'Y' then
2631 l_start_date := l_elig_per.ovrid_svc_dt;
2632 elsif l_person.adjusted_svc_date is not null then
2633 l_start_date := l_person.adjusted_svc_date ;
2634 else
2635 l_start_date := l_person.date_start;
2636 end if;
2637 --
2638 elsif l_lsf.los_dt_to_use_cd ='IASD' then
2639 --
2640 -- inherited adjusted start date
2641 --
2642 if l_lsf.use_overid_svc_dt_flag = 'Y' then
2643 l_start_date := l_elig_per.ovrid_svc_dt;
2644 if l_start_date is null then
2645 l_start_date := fnd_date.canonical_to_date(l_person_extra.iasd);
2646 end if;
2647 else
2648 l_start_date := fnd_date.canonical_to_date(l_person_extra.iasd);
2649 end if;
2650 --
2651 elsif l_lsf.los_dt_to_use_cd ='IDOH' then
2652 --
2653 -- inherited date of hire
2654 --
2655 if l_lsf.use_overid_svc_dt_flag = 'Y' then
2656 l_start_date := l_elig_per.ovrid_svc_dt;
2657 if l_start_date is null then
2658 l_start_date := fnd_date.canonical_to_date(l_person_extra.idoh);
2659 end if;
2660 else
2661 l_start_date := fnd_date.canonical_to_date(l_person_extra.idoh);
2662 end if;
2663 --
2664 elsif l_lsf.los_dt_to_use_cd ='IOHD' then
2665 --
2666 -- inherited original hire date
2667 --
2668 l_start_date := fnd_date.canonical_to_date(l_person_extra.iohd);
2669 --
2670 elsif l_lsf.los_dt_to_use_cd ='RL' then
2671 run_rule(
2672 p_formula_id => l_lsf.los_dt_to_use_rl,
2673 p_rule_type => 'DATE',
2674 p_effective_date => l_orig_effective_date, -- FONM
2675 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2676 p_business_group_id => p_business_group_id,
2677 p_person_id => p_person_id,
2678 p_pgm_id => p_pgm_id,
2679 p_pl_id => p_pl_id,
2680 p_oipl_id => p_oipl_id,
2681 p_plip_id => null,
2682 p_ptip_id => null,
2683 p_ret_date => l_start_date,
2684 p_ret_val => l_dummy_num,
2685 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2686 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt);
2687 else
2688 if g_debug then
2689 hr_utility.set_location('BEN_91342_UNKNOWN_CODE_1',20);
2690 end if;
2691 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
2692 fnd_message.set_token('PROC',l_proc);
2693 fnd_message.set_token('CODE1',l_lsf.los_dt_to_use_cd);
2694 raise ben_manage_life_events.g_record_error;
2695 end if;
2696
2697 p_start_date := l_start_date;
2698
2699 if l_start_date is null then
2700 p_value := null;
2701 else
2702 if l_lsf.los_uom = 'YR' then
2703 l_value := months_between(l_date,l_start_date)/12;
2704 elsif l_lsf.los_uom = 'QT' then
2705 l_value := months_between(l_date,l_start_date)/4;
2706 elsif l_lsf.los_uom = 'MO' then
2707 l_value := months_between(l_date,l_start_date);
2708 --
2709 -- months_between fails when calculated between 29th Jan,30th Jan AND 28th Feb,
2710 -- for months_between('28-Feb-RRRR','28-Jan-RRRR') it gives 1 but for months_between('28-Feb-RRRR','29/30-Jan-RRRR')
2711 -- it gives < 1 and again as per functionality of months_between for months_between('28-Feb-RRRR','31-Jan-RRRR') it gives 1.
2712 -- So code is made to work for this specific case.
2713 --Bug 5931412
2714 if substr(to_char(l_date,'DD-MON-YYYY'),4,3) = 'FEB'
2715 and substr(to_char(l_start_date,'DD-MON-YYYY'),1,2) > '28'
2716 and substr(to_char(l_date,'DD-MON-YYYY'),1,2) in ('28','29') then
2717 --
2718 l_value := ceil(l_value);
2719 --
2720 end if;
2721 --
2722 --Bug 5931412
2723 elsif l_lsf.los_uom = 'WK' then
2724 l_value := (l_date-l_start_date) / 7;
2725 elsif l_lsf.los_uom = 'DY' then
2726 l_value := l_date-l_start_date;
2727 else -- return years
2728 l_value := months_between(l_date,l_start_date)/12;
2729 end if;
2730 end if;
2731
2732 if (l_lsf.rndg_cd is not null or
2733 l_lsf.rndg_rl is not null) and
2734 p_perform_rounding_flg = true
2735 and l_value is not null then
2736
2737 p_value := benutils.do_rounding
2738 (p_rounding_cd => l_lsf.rndg_cd,
2739 p_rounding_rl => l_lsf.rndg_rl,
2740 p_value => l_value,
2741 p_effective_date => l_effective_date);
2742 else
2743 p_value := l_value;
2744 end if;
2745 end if;
2746 if g_debug then
2747 hr_utility.set_location('Leaving: '||l_proc,99);
2748 end if;
2749 exception
2750 --
2751 when others then
2752 --
2753 p_value := null;
2754 p_start_date := null;
2755 raise;
2756 --
2757 end determine_los;
2758
2759
2760 procedure determine_comb_age_los
2761 (p_person_id in number,
2762 p_cmbn_age_los_fctr_id in number,
2763 p_pgm_id in number,
2764 p_pl_id in number,
2765 p_oipl_id in number,
2766 p_comp_obj_mode in boolean default true,
2767 p_per_in_ler_id in number,
2768 p_effective_date in date,
2769 p_lf_evt_ocrd_dt in date,
2770 p_fonm_cvg_strt_dt in date default null,
2771 p_fonm_rt_strt_dt in date default null,
2772 p_business_group_id in number,
2773 p_value out nocopy number) is
2774 --
2775 l_result number;
2776 l_age_val number;
2777 l_los_val number;
2778 l_proc varchar2(80) := g_package||'determine_los';
2779 l_elig_change_dt date;
2780 l_start_date date;
2781 --RCHASE
2782 l_dob date:=null;
2783 --End RCHASE
2784 --
2785 cursor c_cla_elig1 is
2786 select cla.los_fctr_id ,
2787 cla.age_fctr_id
2788 from ben_cmbn_age_los_fctr cla
2789 where cla.cmbn_age_los_fctr_id = p_cmbn_age_los_fctr_id
2790 and cla.business_group_id = p_business_group_id;
2791 --
2792 l_cla_elig1 c_cla_elig1%rowtype;
2793 --
2794 begin
2795 --
2796 g_debug := hr_utility.debug_enabled;
2797 if g_debug then
2798 hr_utility.set_location ('Entering '||l_proc,10);
2799 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
2800 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
2801 end if;
2802 --
2803 open c_cla_elig1;
2804 --
2805 fetch c_cla_elig1 into l_cla_elig1;
2806 if c_cla_elig1%notfound then
2807 --
2808 close c_cla_elig1;
2809 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
2810 fnd_message.set_token('PACKAGE',l_proc);
2811 fnd_message.set_token('CURSOR','c_cla_elig1');
2812 fnd_message.raise_error;
2813 --
2814 end if;
2815 --
2816 close c_cla_elig1;
2817 --
2818 ben_derive_factors.determine_los
2819 (p_person_id => p_person_id,
2820 p_los_fctr_id => l_cla_elig1.los_fctr_id,
2821 p_pgm_id => p_pgm_id ,
2822 p_pl_id => p_pl_id ,
2823 p_oipl_id => p_oipl_id ,
2824 p_comp_obj_mode => p_comp_obj_mode,
2825 p_per_in_ler_id => p_per_in_ler_id ,
2826 p_effective_date => p_effective_date,
2827 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2828 p_business_group_id => p_business_group_id ,
2829 p_perform_rounding_flg => TRUE ,
2830 p_value => l_los_val,
2831 p_start_date => l_start_date,
2832 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2833 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt) ;
2834 --
2835 ben_derive_factors.determine_age
2836 (p_person_id => p_person_id,
2837 --RCHASE
2838 p_per_dob => l_dob,
2839 --End RCHASE
2840 p_age_fctr_id => l_cla_elig1.age_fctr_id,
2841 p_pgm_id => p_pgm_id ,
2842 p_pl_id => p_pl_id ,
2843 p_oipl_id => p_oipl_id ,
2844 p_comp_obj_mode => p_comp_obj_mode,
2845 p_per_in_ler_id => p_per_in_ler_id ,
2846 p_effective_date => p_effective_date,
2847 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2848 p_business_group_id => p_business_group_id ,
2849 p_perform_rounding_flg => TRUE,
2850 p_value => l_age_val,
2851 p_change_date => l_elig_change_dt,
2852 p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt,
2853 p_fonm_rt_strt_dt => p_fonm_rt_strt_dt) ;
2854 --
2855 l_result := l_age_val + l_los_val;
2856 p_value := l_result;
2857 --
2858 if g_debug then
2859 hr_utility.set_location('Leaving: '||l_proc,10);
2860 end if;
2861 --
2862 exception
2863 --
2864 when others then
2865 --
2866 p_value := null;
2867 raise;
2868 --
2869 end DETERMINE_COMB_AGE_LOS;
2870 --
2871 procedure determine_hours_worked
2872 (p_person_id in number,
2873 p_assignment_id in number,
2874 p_hrs_wkd_in_perd_fctr_id in number,
2875 p_pgm_id in number default null,
2876 p_pl_id in number default null,
2877 p_oipl_id in number default null,
2878 p_comp_obj_mode in boolean default true,
2879 p_per_in_ler_id in number default null,
2880 p_effective_date in date,
2881 p_lf_evt_ocrd_dt in date,
2882 p_fonm_cvg_strt_dt in date default null,
2883 p_fonm_rt_strt_dt in date default null,
2884 p_business_group_id in number,
2885 p_value out nocopy number) is
2886 --
2887 l_package VARCHAR2(80) := g_package || '.determine_hours_worked';
2888 l_start_date DATE;
2889 l_result NUMBER;
2890 l_bal_rec ben_per_bnfts_bal_f%ROWTYPE;
2891 l_bnb_rec ben_bnfts_bal_f%ROWTYPE;
2892 l_jurisdiction_code VARCHAR2(30);
2893 l_min_ass_date date ;
2894 l_effective_date date ;
2895
2896 cursor c_ass is
2897 select min(effective_start_date)
2898 from per_all_assignments_f ass
2899 where person_id = p_person_id
2900 and (assignment_id = p_assignment_id or
2901 (p_assignment_id is null and
2902 ass.primary_flag = 'Y' and
2903 ass.assignment_type <> 'C'));
2904
2905 cursor c_assignment is
2906 select assignment_id
2907 from per_all_assignments_f paf
2908 where person_id = p_person_id
2909 and (assignment_id = p_assignment_id or
2910 (p_assignment_id is null and
2911 primary_flag = 'Y' and
2912 paf.assignment_type <> 'C'))
2913 and business_group_id = p_business_group_id
2914 and l_effective_date
2915 between paf.effective_start_date
2916 and paf.effective_end_date;
2917 l_assignment c_assignment%rowtype;
2918
2919 cursor c_hwf is
2920 select hwf.hrs_wkd_in_perd_fctr_id
2921 ,hwf.hrs_src_cd
2922 ,hwf.hrs_wkd_det_cd
2923 ,hwf.hrs_wkd_det_rl
2924 ,hwf.rndg_cd
2925 ,hwf.rndg_rl
2926 ,hwf.defined_balance_id
2927 ,hwf.bnfts_bal_id
2928 ,hwf.mn_hrs_num
2929 ,hwf.mx_hrs_num
2930 ,hwf.once_r_cntug_cd
2931 ,hwf.hrs_wkd_calc_rl
2932 from ben_hrs_wkd_in_perd_fctr hwf
2933 where hwf.hrs_wkd_in_perd_fctr_id = p_hrs_wkd_in_perd_fctr_id
2934 and hwf.business_group_id = p_business_group_id;
2935 --
2936 l_hwf c_hwf%rowtype;
2937
2938 --
2939 BEGIN
2940 --
2941 if g_debug then
2942 hr_utility.set_location('Entering ' || l_package,10);
2943 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
2944 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
2945 end if;
2946
2947 -- tilak : new fonm paramter passes the value no more overring the dates
2948 l_effective_date := nvl(nvl(p_fonm_rt_Strt_dt, p_fonm_cvg_strt_dt), p_lf_evt_ocrd_dt);
2949 --
2950
2951
2952
2953 open c_hwf;
2954 fetch c_hwf into l_hwf;
2955 --
2956 if c_hwf%notfound then
2957 close c_hwf;
2958 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
2959 fnd_message.set_token('PACKAGE',l_package);
2960 fnd_message.set_token('CURSOR','c_hwf');
2961 fnd_message.raise_error;
2962 end if;
2963 close c_hwf;
2964 --
2965 -- Steps to perform process
2966 --
2967 -- 1) Work out the start date
2968 -- 2) Perform Rounding
2969 --
2970 ben_determine_date.main(p_date_cd=> l_hwf.hrs_wkd_det_cd
2971 ,p_formula_id => l_hwf.hrs_wkd_det_rl
2972 ,p_person_id => p_person_id
2973 ,p_bnfts_bal_id => l_hwf.bnfts_bal_id
2974 ,p_pgm_id => p_pgm_id
2975 ,p_pl_id => p_pl_id
2976 ,p_oipl_id => p_oipl_id
2977 ,p_comp_obj_mode => p_comp_obj_mode
2978 ,p_business_group_id => p_business_group_id
2979 ,p_returned_date => l_start_date
2980 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2981 ,p_effective_date => NVL(p_lf_evt_ocrd_dt,p_effective_date)
2982 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
2983 ,p_fonm_rt_strt_dt => p_fonm_rt_strt_dt
2984 );
2985 --
2986 if g_debug then
2987 hr_utility.set_location('l_hwf.hrs_src_cd '||l_hwf.hrs_src_cd ,20);
2988 end if;
2989 IF l_hwf.hrs_src_cd = 'BNFTBALTYP' THEN
2990 --
2991 IF ben_whatif_elig.g_bnft_bal_hwf_val IS NOT NULL THEN
2992 --
2993 -- This is the case where benmngle is called from the
2994 -- watif form and user wants to simulate hours worked
2995 -- changed. Use the user supplied simulate hours value rather
2996 -- than the fetched value.
2997 --
2998 l_result := ben_whatif_elig.g_bnft_bal_hwf_val;
2999 --
3000 ELSE
3001 --
3002 -- Get the persons balance
3003 if g_debug then
3004 hr_utility.set_location(' l_hwf.bnfts_bal_id '||l_hwf.bnfts_bal_id , 30);
3005 hr_utility.set_location(' p_person_id '||p_person_id , 30);
3006 hr_utility.set_location(' l_start_date '||l_start_date, 30);
3007 end if;
3008 --
3009 ben_person_object.get_object(p_person_id=> p_person_id
3010 ,p_effective_date => l_start_date
3011 ,p_bnfts_bal_id => l_hwf.bnfts_bal_id
3012 ,p_rec => l_bal_rec);
3013 --
3014 l_result := l_bal_rec.val;
3015 --
3016 IF l_result IS NULL THEN
3017
3018 if l_hwf.hrs_wkd_det_cd in
3019 ('AFDCPPY','AFDCSPPY','AFDCPPQ','AFDCM','AFDCSM','APOCT1','AFDECY' ) then
3020
3021 open c_ass ;
3022 fetch c_ass into l_min_ass_date ;
3023 close c_ass ;
3024
3025 if g_debug then
3026 hr_utility.set_location('l_min_ass_date ' || l_min_ass_date,19);
3027 end if;
3028 if l_min_ass_date is not null then
3029 ben_person_object.get_object(p_person_id=> p_person_id
3030 ,p_effective_date => l_min_ass_date
3031 ,p_bnfts_bal_id => l_hwf.bnfts_bal_id
3032 ,p_rec => l_bal_rec);
3033 --
3034 l_result := l_bal_rec.val;
3035 end if ;
3036 end if ;
3037
3038 end if ;
3039
3040 IF l_result IS NULL THEN
3041 --
3042 if g_debug then
3043 hr_utility.set_location(' Person does not have a balance ',40);
3044 end if;
3045 --
3046 -- Person does not have a balance, recheck if they have a balance
3047 -- as of the life event occurred date or effective date.
3048 -- Fix for bug 216.
3049 --
3050 ben_person_object.get_object(p_bnfts_bal_id=> l_hwf.bnfts_bal_id
3051 ,p_rec => l_bnb_rec);
3052 --
3053 fnd_message.set_name('BEN'
3054 ,'BEN_92317_PER_BALANCE_NULL');
3055 fnd_message.set_token('NAME'
3056 ,l_bnb_rec.name);
3057 fnd_message.set_token('DATE'
3058 ,l_start_date);
3059 benutils.write(p_text=> fnd_message.get);
3060 --
3061 ben_person_object.get_object(p_person_id=> p_person_id
3062 ,p_effective_date => NVL(p_lf_evt_ocrd_dt
3063 ,p_effective_date)
3064 ,p_bnfts_bal_id => l_hwf.bnfts_bal_id
3065 ,p_rec => l_bal_rec);
3066 --
3067 l_result := l_bal_rec.val;
3068 if g_debug then
3069 hr_utility.set_location(' Person does not l_bal_rec.val '||l_bal_rec.val ,50);
3070 end if;
3071 --
3072 IF l_result IS NULL THEN
3073 --
3074 fnd_message.set_name('BEN'
3075 ,'BEN_92317_PER_BALANCE_NULL');
3076 fnd_message.set_token('NAME'
3077 ,l_bnb_rec.name);
3078 fnd_message.set_token('DATE'
3079 ,NVL(p_lf_evt_ocrd_dt
3080 ,p_effective_date));
3081 benutils.write(p_text=> fnd_message.get);
3082 RETURN;
3083 --
3084 END IF;
3085 --
3086 END IF;
3087 --
3088 END IF; -- whatif hours worked existence check
3089 --
3090 ELSIF l_hwf.hrs_src_cd = 'BALTYP' THEN
3091 --
3092 IF ben_whatif_elig.g_bal_hwf_val IS NOT NULL THEN
3093 --
3094 -- This is the case where benmngle is called from the
3095 -- watif form and user wants to simulate hours worked
3096 -- changed. Use the user supplied simulate hours value rather
3097 -- than the fetched value.
3098 --
3099 l_result := ben_whatif_elig.g_bal_hwf_val;
3100 --
3101 ELSE
3102 --
3103 -- Get the persons balance
3104 --
3105 if p_assignment_id is null then
3106 --
3107 open c_assignment;
3108 fetch c_assignment into l_assignment;
3109 close c_assignment;
3110 --
3111 else
3112 --
3113 l_assignment.assignment_id := p_assignment_id;
3114 --
3115 end if;
3116 --
3117 -- before calling the get_value set the tax_unit_id context
3118 --
3119 ben_derive_part_and_rate_facts.set_taxunit_context
3120 (p_person_id => p_person_id
3121 ,p_business_group_id => p_business_group_id
3122 ,p_effective_date => p_effective_date
3123 ) ;
3124 --
3125 begin
3126 l_result :=
3127 pay_balance_pkg.get_value(l_hwf.defined_balance_id
3128 ,l_assignment.assignment_id
3129 ,l_start_date);
3130 exception
3131 when others then
3132 l_result := null ;
3133 end ;
3134
3135 IF l_result IS NULL THEN
3136 if l_hwf.hrs_wkd_det_cd in
3137 ('AFDCPPY','AFDCSPPY','AFDCPPQ','AFDCM','AFDCSM','APOCT1','AFDECY' ) then
3138 open c_ass ;
3139 fetch c_ass into l_min_ass_date ;
3140 close c_ass ;
3141 if g_debug then
3142 hr_utility.set_location (' l_min_ass_date ' || l_min_ass_date, 1999);
3143 end if;
3144 l_result :=
3145 pay_balance_pkg.get_value(l_hwf.defined_balance_id
3146 ,l_assignment.assignment_id
3147 ,l_min_ass_date);
3148
3149 end if ;
3150 end if ;
3151
3152 --
3153 IF l_result IS NULL THEN
3154 --
3155 -- Person does not have a balance, recheck if they have a balance
3156 -- as of the life event occurred date or effective date.
3157 -- Fix for bug 216.
3158 --
3159 fnd_message.set_name('BEN'
3160 ,'BEN_92318_BEN_BALANCE_NULL');
3161 fnd_message.set_token('DATE'
3162 ,l_start_date);
3163 benutils.write(p_text=> fnd_message.get);
3164 --
3165 l_result :=
3166 pay_balance_pkg.get_value(l_hwf.defined_balance_id
3167 ,l_assignment.assignment_id
3168 ,l_effective_date
3169 );
3170 --
3171 IF l_result IS NULL THEN
3172 --
3173 fnd_message.set_name('BEN'
3174 ,'BEN_92318_BEN_BALANCE_NULL');
3175 fnd_message.set_token('DATE'
3176 , l_effective_date);
3177 benutils.write(p_text=> fnd_message.get);
3178 RETURN ;
3179 --
3180 END IF;
3181 --
3182 END IF;
3183 --
3184 END IF; -- whatif hours worked existence check.
3185 --
3186 END IF;
3187 --
3188 IF l_hwf.rndg_cd IS NOT NULL
3189 OR l_hwf.rndg_rl IS NOT NULL THEN
3190 --
3191 l_result :=
3192 benutils.do_rounding(p_rounding_cd=> l_hwf.rndg_cd
3193 ,p_rounding_rl => l_hwf.rndg_rl
3194 ,p_value => l_result
3195 ,p_effective_date => NVL(p_lf_evt_ocrd_dt
3196 ,p_effective_date));
3197 --
3198 END IF;
3199 --
3200 p_value := l_result;
3201
3202 if g_debug then
3203 hr_utility.set_location(' End of hours_calculation l_result '||l_result, 50);
3204 end if;
3205 --
3206 --
3207 end determine_hours_worked;
3208
3209 procedure determine_pct_fulltime
3210 (p_person_id in number,
3211 p_assignment_id in number,
3212 p_pct_fl_tm_fctr_id in number,
3213 p_effective_date in date,
3214 p_lf_evt_ocrd_dt in date,
3215 p_fonm_cvg_strt_dt in date default null,
3216 p_fonm_rt_strt_dt in date default null,
3217 p_comp_obj_mode in boolean,
3218 p_business_group_id in number,
3219 p_value out nocopy number) is
3220 --
3221 l_package VARCHAR2(80) := g_package || '.determine_pct_fulltime';
3222 l_result NUMBER;
3223 l_rec ben_person_object.g_person_fte_info_rec;
3224 l_effective_date date ;
3225
3226 cursor c_assignment is
3227 select assignment_id
3228 from per_all_assignments_f paf
3229 where primary_flag = 'Y'
3230 and person_id = p_person_id
3231 and paf.assignment_type <> 'C'
3232 and business_group_id = p_business_group_id
3233 and l_effective_date
3234 between paf.effective_start_date
3235 and paf.effective_end_date;
3236 l_assignment c_assignment%rowtype;
3237
3238 cursor c_pff is
3239 select pff.pct_fl_tm_fctr_id
3240 ,pff.use_prmry_asnt_only_flag
3241 ,pff.use_sum_of_all_asnts_flag
3242 ,pff.rndg_cd
3243 ,pff.rndg_rl
3244 ,pff.mn_pct_val
3245 ,pff.mx_pct_val
3246 from ben_pct_fl_tm_fctr pff
3247 where pff.pct_fl_tm_fctr_id = p_pct_fl_tm_fctr_id
3248 and pff.business_group_id = p_business_group_id;
3249 l_pff c_pff%rowtype;
3250
3251 begin
3252 --
3253 hr_utility.set_location('Entering ' || l_package,10);
3254 hr_utility.set_location('fonm_cvg :'||p_fonm_cvg_strt_dt ,10);
3255 hr_utility.set_location('fonm_rt :'||p_fonm_cvg_strt_dt,10);
3256 --
3257 -- tilak : new fonm paramter passes the value no more overring the dates
3258 l_effective_date := nvl(nvl(p_fonm_rt_Strt_dt, p_fonm_cvg_strt_dt), p_lf_evt_ocrd_dt);
3259 --
3260
3261 open c_pff;
3262 fetch c_pff into l_pff;
3263 --
3264 if c_pff%notfound then
3265 close c_pff;
3266 fnd_message.set_name('BEN','BEN_91833_CURSOR_RETURN_NO_ROW');
3267 fnd_message.set_token('PACKAGE',l_package);
3268 fnd_message.set_token('CURSOR','c_pff');
3269 fnd_message.raise_error;
3270 end if;
3271 close c_pff;
3272
3273 if p_assignment_id is null then
3274 --
3275 open c_assignment;
3276 fetch c_assignment into l_assignment;
3277 close c_assignment;
3278 --
3279 else
3280 --
3281 l_assignment.assignment_id := p_assignment_id;
3282 --
3283 end if;
3284
3285 if l_assignment.assignment_id is not null then
3286 --
3287 ben_person_object.get_object
3288 (p_assignment_id=> l_assignment.assignment_id
3289 ,p_rec => l_rec);
3290 --
3291 -- Get percent fulltime values
3292 --
3293 if l_pff.use_prmry_asnt_only_flag ='Y' then
3294 l_result := l_rec.fte;
3295 else
3296 l_result := l_rec.total_fte;
3297 end if;
3298 --
3299 if l_pff.rndg_cd is not null
3300 or l_pff.rndg_rl is not null then
3301 --
3302 l_result :=
3303 benutils.do_rounding(p_rounding_cd=> l_pff.rndg_cd
3304 ,p_rounding_rl => l_pff.rndg_rl
3305 ,p_value => l_result
3306 ,p_effective_date => l_effective_date);
3307 --
3308 end if;
3309 --
3310 end if;
3311
3312 p_value := l_result;
3313
3314 if g_debug then
3315 hr_utility.set_location('Leaving ' || l_package,10);
3316 end if;
3317 --
3318 end determine_pct_fulltime;
3319 end ben_derive_factors;
[Home] [Help]