1 package body ben_distribute_rates as
2 /* $Header: bendisrt.pkb 120.10.12010000.3 2008/08/14 07:17:21 ubhat ship $ */
3 /*
4 --------------------------------------------------------------------------------
5 Name
6
7 Purpose
8
9
10 History
11 Date Who Version What?
12 ---- --- ------- -----
13 23 Sep 98 maagrawa 115.0 Created.
14 04 Nov 98 maagrawa 115.1 If activity period is yearly(PYR),
15 then don't calculate no_of_periods
16 in annual_to_period and
17 period_to_annual functions.
18 18 Jan 99 G Perry 115.3 LED V ED
19 09 Apr 99 mhoyes 115.4 get_balance/c_pil
20 28 Sep 99 lmcdonal 115.5 Added Compare_Balances,
21 Prorate_min_max procedures.
22 Add PP to get_periods_between.
23 Change get_periods_between from
24 rounding to 1 decimal point to
25 truncating the value.
26 Add payroll_id to annual_to_period
27 and period_to_annual.
28 02 Oct 99 lmcdonal 115.6 Fixed bugs in Compare_Balances,
29 Prorate_min_max
30 12 Oct 99 tguy 115.7 fixed bug 3637. get_periods_between
31 was returning wrong number of periods
32 12-Nov-99 lmcdonal 115.8 Better debugging messages.
33 14-Nov-99 pbodla 115.9 acty_base_rt is added as context to
34 rule : prort_mn_ann_elcn_val_rl,
35 prort_mx_ann_elcn_val_rl,
36 17-Nov-99 pbodla 115.10 acty_base_rt is passed to
37 ben_determine_date.main, This is
38 applicable only for ecr.
39 19-Nov-99 pbodla 115.11 p_elig_per_elctbl_chc_id is passed to
40 formula.
41 10-Dec-99 lmcdonal 115.12 In prorate_min_max, if input vals are
42 null, do not perform proration.
43 And in compare_balances, if p_ann_mn_val
44 is null, do not load with 0.
45 14-Mar-00 maagrawa 115.13 Modified the balance calculation process
46 to also estimate balances, if required.
47 Number of pay periods now calculated based
48 on regular_payment_date rather than the
49 period start and end date.(1237278).
50 05-Apr-00 mmogel 115.14 Added tokens to message calls to make the
51 messages more meaningful to the user
52 21-Apr-00 jcarpent 115.15 Pass more args to ben_distribute_rates
53 (1205931,4600)
54 03-May-00 gperry 115.16 Added rounding logic to period_to_annual
55 and annual_to_period. Fixes internal bug
56 5148.
57 28-Jun-00 shdas 115.17 set cmplt yr flag based on det_pl_ytd_cntrs_cd
58 29-Jun-00 mhoyes 115.18 - Bypassed calculations in annual_to_period and
59 period_to_annual when amount is null.
60 10-Aug-00 gperry 115.19 Fixed WWBUG 1309417.
61 Only include time periods that are valid
62 for the period in question.
63 22 Sep 00 mhoyes 115.20 - Added function caching for period_to_annual and
64 annual_to_period functions.
65 25 Sep 00 mhoyes 115.21 - Removed dbms_outputs.
66 23 Oct 00 kmahendr 115.22 - Corrected message_name for message_number 91824
67 Bug#1471114
68 07 Nov 00 mhoyes 115.23 - Added set_no_cache_context to turn caching off.
69 - Referenced electable choice context global.
70 03 jan 01 tilak 115.24 - p_end_dt parameter added get_balanc
71 20 feb 01 kmahendr 115.25 - Bug#1628706 - check date is the criteria for
72 computing no. of pay periods if it is not null
73 -cursor in get_periods_between changed after
74 going thro Bug#1309417 and 1237278 - also modified
75 parameter value for opening cursor parse_period
76 17-May-01 maagrawa 115.26 - Performance changes.
77 Call hr_elements.check_element_freq only
78 if the rule exists.
79 29-Aug-01 pbodla 115.27 - bug:1949361 jurisdiction code is
80 derived inside benutils.formula
81 18-Sep-01 kmahendr 115.28 - bug-1996066-where clause in cursors c_parse_periods and
82 c_count_periods changed to take end_date in place of
83 regular_payment_date
84 11-Nov-01 tmathers 115.29 - Test harness for get_periods between
85 completed with no differnces.
86 01-Dec-01 tmathers 115.30 - Fixed compliance error.
87 27-Dec-01 ikasire 115.31 Bug 2151055 fixes to set_default_dates if the Coverage/rate
88 starts in next calender year
89 28-Dec-01 ikasire 115.32 Bug 2151055 more changes to get_periods_between not to return
90 0 periods in case last month/pay period of the Year when
91 complete year flag is N. This is for SAREC case
92 31-Dec-01 ikasire 115.33 Bug 2164741 Wrong calculation of communication amount
93 in subsequent enrollments because of using the
94 cvg start date of epe
95 21-Apr-02 ashrivas 115.34 Added convert_rates_w for self-service
96 23-May-02 kmahendr 115.35 Added a procedure - annual_to_period_out
97 23 May 02 kmahendr 115.36 No changes
98 03 Jun 02 pabodla 115.37 Bug 2367556 : Changed STANDARD.bitand to just bitand
99 08-Jun-02 pabodla 115.38 Do not select the contingent worker
100 assignment when assignment data is
101 fetched.
102 04-Sep-02 kmahendr 115.39 added codes in get_periods_between for new acty_ref_perd_cd.
103 15-Oct-02 kmahendr 115.40 Added overloaded function - get_periods_between and parameter
104 to annual_to_period - Bug#2556948
105 07-jan-03 vsethi 115.41 No copy changes
106 09-jan-03 kmahendr 115.42 Bug#2734491-Child rate is treated as parent- codes added in
107 annual to period
108 16-an-03 kmullapu 115.43 Bug 2745691. Added convert_pcr_rates_w
109 23-Jan-03 ikasire 115.45 Bug 2149438 Added overloaded funcrtions for
110 period_to_annual and annual_to_period to handle the
111 rounding externally.
112 13-feb-02 vsethi 115.31 Enclosed all hr_utility debug calls inside if
113 17-Mar-03 kmullapu 115.47 Bug 2745691:modified convert_pcr_rates_w to set
114 p_use_balance_flag:='Y' only when parent rate is SAREC or SAAEAR
115 26-Jun-03 lakrish 115.48 Bug 2992321, made ann_rt_val parameters
116 as IN OUT in convert_pcr_rates_w
117 12-Sep-03 rpillay 115.49 GRADE/STEP : Changes to set_default_dates to not throw error
118 for G mode when year periods are not set up.
119 26-Sep-03 rpillay 115.50 GRADE/STEP : Check for Grade Step program instead of
120 looking for 'G' mode
121 21-Oct-03 ikasire 115.51 BUG 3191928 fixes if the year period used it not right.
122 22-Oct-03 ikasire 115.52 BUG 3191928 fixed the typo order of select clause list
123 28-Oct-03 ikasire 115.53 BUG 3159774 c_count_periods_chq modified.see comments in the code
124 31-Oct-03 kmahendr 115.54 Bug#3231548 - added additional parameter to get_periods
125 between
126 31-oct-03 kmahendr 115.55 Bug#3231548 - the condition added to another tot-periods
127 28-jan-03 ikasire 115.56 Bug#3394862 - The estimate_balance procedure is calling
128 get_periods_between with the new payroll_id for determining
129 old periods in a different payroll. We need to use the one
130 on element entries with a nvl getting from the p_payroll_id
131 parameter.
132 10-Feb-03 ikasire 115.57 Bug 3430334. Search with tag 3430334 for more details.
133 12-Feb-03 ikasire 115.59 reverted the changes made in 115.58 until futher review by PM
134 not to get into some other patch before we complete the
135 review and testing.
136 26-Apr-04 kmahendr 115.60 Bug#3510633 - Added parameter person_id to function
137 annual_to_period
138 21-Jun-04 bmanyam 115.62 Bug# 3704632 - Added NVL() to p_end_date parameter
139 in the cursot to find number_of_periods in function get_periods_between().
140 22-Jun-04 bmanyam 115.63 Bug# 3704632 - Removed the above change. Added
141 select clause to fetch end-date of pay-year from per_time_periods
142 for 'Calender Month' and 'Lunar Month'
143 29-Nov-04 kmahendr 115.64 Codes added for new Rate Start date
144 03-Dec-04 vvprabhu 115.65 Bug 3980063 SSBEN Trace Enhancement
145 21-Dec-04 kmahendr 115.66 Bug#4037102 - nvl used to pass start date
146 27-Apr-05 swjain 115.67 Bug#4290565 Modified procedure prorate_min_max
147 25-Jul-05 kmahendr 115.68 Bug#4504449 - changed IYYY to YYYY in add_months
148 27-Jul-05 kmahendr 115.69 Bug#4504449 - l_periods defaulted to 1 in the
149 case of element with frequency rule in
150 get_periods_between
151 21-Mar-06 vborkar 115.70 5104247 Added p_child_rt_flag parameters to
152 convert_pcr_rates_w procedure.
153 27-Mar-06 kmahendr 115.71 Bug#5077258 - nvl added to return 0 for
154 balance if null in get_balance func
155 16-Aug-06 vborkar 115.72 5460638 For enterable rate, adjusted the defined rate(upto .01)
156 when it falls outside min-max window due to rounding error.
157 15-Nov-05 bmanyam 115.74 5642552 For Annual Rates, annual_to_period, the pay_period_amt
158 should be evaluated from pay_period_start NOT yr_start_dt.
159 20-Feb-07 rtagarra 115.75 ICM Changes
160 04-Dec-07 krupani 115.76 Incorporated changes of secure views and Bug 6455096 from branchline to mainline
161 12-Aug-08 ubhat 120.11 Forward port bug fix Bug 6830210:
162 14-Aug-08 veparame 120.12 For FwdPort of Bug 6913654: Modified get_periods_function to return correct number of periods.
163 Modified period_to_annual function to calculate correct annual rates for
164 'Estimate Only' rates
165 -- ==========================================================================================================
166 */
167 --
168 --
169 g_package varchar2(80) := 'ben_distribute_rates';
170 --
171 g_hash_key number := ben_hash_utility.get_hash_key;
172 g_hash_jump number := ben_hash_utility.get_hash_jump;
173 --
174 -- Function cache stuff
175 --
176 type g_period_to_annual_row is record
177 (amount number
178 ,enrt_rt_id number
179 ,elig_per_elctbl_chc_id number
180 ,acty_ref_perd_cd varchar2(30)
181 ,business_group_id number
182 ,effective_date date
183 ,lf_evt_ocrd_dt date
184 ,complete_year_flag varchar2(30)
185 ,use_balance_flag varchar2(30)
186 ,start_date date
187 ,end_date date
188 ,payroll_id number
189 ,element_type_id number
190 ,annual_amt number
191 );
192 --
193 type g_period_to_annual_tbl is table of g_period_to_annual_row
194 index by binary_integer;
195 --
196 type g_annual_to_period_row is record
197 (amount number
198 ,enrt_rt_id number
199 ,elig_per_elctbl_chc_id number
200 ,acty_ref_perd_cd varchar2(30)
201 ,business_group_id number
202 ,effective_date date
203 ,lf_evt_ocrd_dt date
204 ,complete_year_flag varchar2(30)
205 ,use_balance_flag varchar2(30)
206 ,start_date date
207 ,end_date date
208 ,payroll_id number
209 ,element_type_id number
210 ,period_amt number
211 ,pp_in_yr_used_num number
212 );
213 --
214 type g_annual_to_period_tbl is table of g_annual_to_period_row
215 index by binary_integer;
216 --
217 type g_element_pay_freq_periods is record
218 (element_type_id number
219 ,payroll_id number
220 ,start_date date
221 ,end_date date
222 ,num_periods number);
223 --
224 type g_element_pay_freq_periods_tbl is table of g_element_pay_freq_periods
225 index by binary_integer;
226 --
227 g_period_to_annual_cache g_period_to_annual_tbl;
228 g_period_to_annual_cached pls_integer := 0;
229 --
230 g_annual_to_period_cache g_annual_to_period_tbl;
231 g_annual_to_period_cached pls_integer := 0;
232 --
233 g_element_pay_freq_perd_cache g_element_pay_freq_periods_tbl;
234 g_element_pay_freq_perd_cached pls_integer := 0;
235 --
236 g_debug boolean := hr_utility.debug_enabled;
237 --
238 ---------------------------------------------------------------------------
239 -- GEVITY
240 -- This procedure returns the defined, communicated and annual amounts.
241 -- This uses a fast formula and user is completely responsible for returning
242 -- the right values
243 -- Scope of function: Call from external procedures allowed.
244 ---------------------------------------------------------------------------
245 PROCEDURE periodize_with_rule
246 (p_formula_id in number,
247 p_effective_date in date,
248 p_assignment_id in number,
249 p_convert_from_val in number,
250 p_convert_from in varchar2,
251 p_elig_per_elctbl_chc_id in number,
252 p_acty_base_rt_id in number,
253 p_business_group_id in number,
254 p_enrt_rt_id in number default null,
255 p_ann_val out nocopy number,
256 p_cmcd_val out nocopy number,
257 p_val out nocopy number ) IS
258 --
259 l_package varchar2(80) := g_package || '.periodize_with_rule';
260 --
261 l_outputs ff_exec.outputs_t;
262 l_ann_val number;
263 l_cmcd_val number;
264 l_val number;
265 --
266 BEGIN
267 --
268 g_debug := hr_utility.debug_enabled;
269 --
270 if g_debug then
271 hr_utility.set_location('Entering '||l_package, 10);
272 hr_utility.set_location(' p_convert_from_val '||p_convert_from_val,10);
273 end if;
274 --
275 --
276 IF p_convert_from_val IS NOT NULL THEN
277 --
278 l_outputs := benutils.formula
279 (p_formula_id => p_formula_id,
280 p_effective_date => p_effective_date,
281 p_assignment_id => p_assignment_id,
282 p_business_group_id=> p_business_group_id,
283 p_param1 => 'BEN_IV_CONVERT_FROM',
284 p_param1_value => p_convert_from,
285 p_param2 => 'BEN_IV_CONVERT_FROM_VAL',
286 p_param2_value => to_char(p_convert_from_val),
287 p_param3 => 'BEN_ABR_IV_ACTY_BASE_RT_ID',
288 p_param3_value => to_char(p_acty_base_rt_id),
289 p_param4 => 'BEN_EPE_IV_ELIG_PER_ELCTBL_CHC_ID',
290 p_param4_value => to_char(p_elig_per_elctbl_chc_id),
291 p_param5 => 'BEN_ECR_IV_ENRT_RT_ID',
292 p_param5_value => to_char(p_enrt_rt_id)
293 );
294 --
295 hr_utility.set_location('Done with Formula',10);
296 --
297 -- Loop through the returned table and make sure that the returned
298 -- values have been found
299 --
300 for l_count in l_outputs.first..l_outputs.last loop
301 --
302 begin
303 --
304 if l_outputs(l_count).name = 'DFND_VAL' then
305 --
306 l_val := l_outputs(l_count).value;
307 --
308 elsif l_outputs(l_count).name = 'CMCD_VAL' then
309 --
310 l_cmcd_val := l_outputs(l_count).value;
311 --
312 elsif l_outputs(l_count).name = 'ANN_VAL' then
313 --
314 l_ann_val := l_outputs(l_count).value;
315 --
316 else
317 --
318 -- Account for cases where formula returns an unknown
319 -- variable name
320 hr_utility.set_location('In the Loop wrong Name ',10);
321 --
322 fnd_message.set_name('BEN','BEN_92310_FORMULA_RET_PARAM');
323 fnd_message.set_token('PROC',l_package);
324 fnd_message.set_token('FORMULA',p_formula_id);
325 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
326 fnd_message.raise_error;
327 --
328 end if;
329 --
330 exception when others then
331 --
332 hr_utility.set_location('BEN_92311_FORMULA_VAL_PARAM ',10);
333 --
334 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
335 fnd_message.set_token('PROC',l_package);
336 fnd_message.set_token('FORMULA',p_formula_id);
337 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
338 fnd_message.raise_error;
339 --
340 end;
341 end loop ;
342 ELSE
343 --
344 if g_debug then
345 hr_utility.set_location('p_convert_from_val IS NULL returning NULLS',10);
346 end if;
347 --
348 l_val := NULL;
349 l_cmcd_val := NULL;
350 l_ann_val := NULL;
351 --
352 END IF;
353 --
354 p_val := l_val;
355 p_cmcd_val := l_cmcd_val;
356 p_ann_val := l_ann_val;
357 if g_debug then
358 hr_utility.set_location('Defined Amount '||p_val,110);
359 hr_utility.set_location('Cmcd Amount '||p_cmcd_val,110);
360 hr_utility.set_location('Ann Amount '||p_ann_val,110);
361 hr_utility.set_location('Leaving '||l_package, 20);
362 end if;
363 --
364 END periodize_with_rule ;
365 ---------------------------------------------------------------------------
366
367 -- Scope of function: Call from external procedures allowed.
368 ---------------------------------------------------------------------------
369 function get_periods_between(
370 p_acty_ref_perd_cd in varchar2,
371 p_start_date in date,
372 p_end_date in date default null,
373 p_payroll_id in number default null,
374 p_business_group_id in number default null,
375 p_element_type_id in number default null,
376 p_enrt_rt_id in number default null,
377 p_effective_date in date default null,
378 p_called_from_est in boolean
379 ) return number is
380 l_package varchar2(80) := g_package || '.get_periods_between';
381
382 cursor c_get_element_type_id is
383 select element_type_id
384 from ben_enrt_rt ert,
385 ben_acty_base_rt_f abr
386 where ert.enrt_rt_id=p_enrt_rt_id and
387 ert.business_group_id=p_business_group_id and
388 abr.acty_base_rt_id=ert.acty_base_rt_id and
389 p_effective_date between
390 abr.effective_start_date and abr.effective_end_date;
391 --
392 l_element_type_id number;
393 --
394 cursor c_element_rule_exists is
395 select 'Y'
396 from pay_ele_payroll_freq_rules epf
397 where epf.element_type_id = l_element_type_id
398 and epf.payroll_id = p_payroll_id
399 and epf.business_group_id = p_business_group_id;
400 --
401 l_element_rule_exists varchar2(1) := 'N';
402 --
403 -- Parse Periods gets the information about the
404 -- number of pay_periods by start date
405 -- for the rate.
406 --
407 cursor c_parse_periods1(v_payroll_id in number,
408 v_start_date in date,
409 v_end_date in date) is
410 select ptp.start_date,
411 ptp.end_date
412 from per_time_periods ptp
413 where ptp.payroll_id = v_payroll_id
414 and ptp.end_date between
415 v_start_date and v_end_date;
416 --
417 cursor get_period_type(p_payroll_id IN NUMBER
418 ,p_date IN DATE) is
419 select period_type
420 from pay_all_payrolls_f
421 where payroll_id = p_payroll_id
422 and p_date between effective_start_date
423 and effective_end_date;
424
425 --
426 l_period_type VARCHAR2(30);
427 --
428 cursor pay_freq_rule_exists(p_payroll_id IN NUMBER
429 ,p_element_type_id IN NUMBER) is
430 select sum(power(2,(FRP.period_no_in_reset_period) - 1))
431 ,decode(epf.reset_period_type,'Year','YYYY','MM')
432 from pay_ele_payroll_freq_rules EPF
433 , pay_freq_rule_periods FRP
434 where FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
435 and EPF.payroll_id = p_payroll_id
436 and EPF.element_type_id = p_element_type_id
437 group by epf.ele_payroll_freq_rule_id ,epf.reset_period_type;
438 --
439 cursor parse_periods(p_payroll_id IN NUMBER
440 ,p_rt_start IN DATE
441 ,p_eoy IN DATE
442 ,p_reset IN VARCHAR2
443 ,p_frq_bitmap_no IN NUMBER) is
444 select sum(
445 ben_distribute_rates.decde_bits(
446 bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
447 ))
448 from per_time_periods
449 where payroll_id = p_payroll_id
450 and --end_date -- Bug 6830210
451 regular_payment_date -- Bug 6830210
452 between p_rt_start
453 and p_eoy
454 group by to_char(end_date,p_reset)
455 ;
456
457 cursor c_count_periods(v_payroll_id in number,
458 v_start_date in date,
459 v_end_date in date) is
460 select count(1)
461 from per_time_periods ptp
462 where ptp.payroll_id = v_payroll_id
463 -- and nvl(ptp.regular_payment_date,ptp.end_date) between
464 and ptp.end_date between
465 v_start_date and v_end_date;
466 -- and nvl(ptp.regular_payment_date,ptp.end_date) >= v_start_date;
467 --
468 l_periods number := null;
469 l_start_date date;
470 l_end_date date;
471 l_date date;
472 l_fortnight_end_date date;
473 l_temp varchar2(1);
474 l_tot_perd number := 0;
475 l_skip_element varchar2(30);
476 l_pay_freq_bitmap_no NUMBER;
477 l_reset VARCHAR2(30):= 'MM';
478 l_pay_annualization_factor number ;
479 l_max_end_date DATE;
480
481 begin
482 --
483 g_debug := hr_utility.debug_enabled;
484 --
485 if g_debug then
486 hr_utility.set_location('Entering '||l_package, 10);
487 end if;
488 --
489 hr_api.mandatory_arg_error(p_api_name => l_package,
490 p_argument => 'p_acty_ref_perd_cd',
491 p_argument_value => p_acty_ref_perd_cd);
492 --
493 hr_api.mandatory_arg_error(p_api_name => l_package,
494 p_argument => 'p_start_date',
495 p_argument_value => p_start_date);
496
497 --
498 if g_debug then
499 hr_utility.set_location('p_acty_ref_perd_cd '||p_acty_ref_perd_cd,20);
500 hr_utility.set_location('p_start_date '||to_char(p_start_date),30);
501 hr_utility.set_location('p_end_date '||to_char(p_end_date),40);
502 end if;
503 --
504 if p_end_date is null then
505 --
506 -- End of the year (of the start date)
507 --
508 l_end_date := add_months(trunc(p_start_date,'YYYY'),12);
509 --
510 else
511 --
512 -- Start Date should be less than end date.
513 --
514 if p_end_date < p_start_date then
515 --
516 fnd_message.set_name('BEN','BEN_91824_START_DT_AFTR_END_DT');
517 fnd_message.set_token('PROC',l_package);
518 fnd_message.set_token('START_DT',to_char(p_start_date));
519 fnd_message.set_token('END_DT',to_char(p_end_date));
520 fnd_message.raise_error;
521 --
522 else
523 --
524 -- need to add 1 day to correctly calc time between 2 dates.
525 -- because we are truncating down we get the correct answer.
526 -- ie months_between(1/1/99,12/31/99) = 11.999999999
527 -- by adding 1 day to the end date we get the correct answer
528 -- of 12
529 --
530 l_end_date := p_end_date + 1;
531 --
532 end if;
533 --
534 end if;
535 --
536 if g_debug then
537 hr_utility.set_location(l_package,50);
538 end if;
539 --
540 if p_acty_ref_perd_cd = 'PWK' then
541 --
542 -- Weekly
543 --
544 l_periods := trunc(((l_end_date - p_start_date)/7),0);
545 --
546 elsif p_acty_ref_perd_cd = 'PHR' then
547 --
548 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
549 if l_pay_annualization_factor is null then
550 l_pay_annualization_factor := 2080;
551 end if;
552 --
553 l_periods := trunc(((l_end_date - p_start_date)/365 * l_pay_annualization_factor),0);
554 --
555 elsif p_acty_ref_perd_cd = 'PP' or p_acty_ref_perd_cd = 'EPP'
556 or p_acty_ref_perd_cd = 'PPF' then
557 --
558 -- Pay Period
559 --
560 hr_utility.set_location('Inside PP',2007);
561 if p_element_type_id is not null then
562 l_element_type_id:=p_element_type_id;
563 else
564 open c_get_element_type_id;
565 -- ok to be null if p_enrt_rt_id is null
566 fetch c_get_element_type_id into l_element_type_id;
567 close c_get_element_type_id;
568 end if;
569 --
570 l_periods := 0;
571 --
572 if p_payroll_id is null then
573 fnd_message.set_name('BEN','BEN_92403_PAYROLL_ID_REQ');
574 fnd_message.set_token('PROC',l_package);
575 fnd_message.raise_error;
576 end if;
577 -- Check if period_type of the payroll is
578 -- either Calendar or Lunar month
579 -- Standard skip rules aren't applied to these
580 --
581 open get_period_type(p_payroll_id,p_start_date);
582 fetch get_period_type into l_period_type;
583 close get_period_type;
584 --
585 if l_period_type in ('Calendar Month','Lunar Month') then
586 hr_utility.set_location('Inside Calendar Month',2007);
587 -- 3704632 : Added IF-CLAUSE here.
588 if (p_end_date is null) THEN
589 select MAX(end_date)
590 into l_max_end_date
591 from per_time_periods
592 where payroll_id = p_payroll_id
593 and TO_CHAR(end_date,'YYYY') =
594 (SELECT TO_CHAR(end_date,'YYYY')
595 from per_time_periods
596 where payroll_id = p_payroll_id
597 and p_start_date between start_date and end_date
598 );
599 END IF;
600 -- 3704632 : End changes
601
602 select count(*)
603 into l_periods
604 from per_time_periods
605 where payroll_id = p_payroll_id
606 and end_date between p_start_date and NVL(p_end_date,l_max_end_date); -- 3704632 : Added NVL() here.
607
608 else
609 -- Check whether any element rule available for the element type.
610 -- If not, do not call the payroll check routine.
611 --
612 open pay_freq_rule_exists(p_payroll_id,l_element_type_id);
613 --
614 fetch pay_freq_rule_exists into l_pay_freq_bitmap_no,l_reset;
615 --
616 if pay_freq_rule_exists%FOUND THEN
617 --
618 close pay_freq_rule_exists;
619 --
620 IF l_reset <>'MM' THEN -- Year
621 hr_utility.set_location('Frequency Rule Year',2007);
622 BEGIN
623 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
624 p_start_date,
625 nvl(p_end_date,l_end_date))
626 LOOP
627 hr_elements.check_element_freq(
628 p_payroll_id =>p_payroll_id,
629 p_bg_id =>p_business_group_id,
630 p_pay_action_id =>TO_NUMBER(null),
631 p_date_earned =>l_parse_periods.end_date,
632 p_ele_type_id =>l_element_type_id,
633 p_skip_element =>l_skip_element);
634 --
635 IF l_skip_element='N' THEN
636 -- count the rows found
637 l_periods := l_periods + 1;
638 END IF;
639 --
640 --
641 END LOOP;
642 END;
643 ELSE -- Month
644
645 --Bug 6913654, Added 'if..else' condition if p_start_date and p_end_date fall in the same month.Old code does not handle this.
646 if TRUNC(p_start_date,l_reset) = TRUNC(p_end_date,l_reset) then
647 hr_utility.set_location('Frequency Rule Month',2007);
648 BEGIN
649 if g_debug then
650 hr_utility.set_location('get_periods_between ',10);
651 end if;
652 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
653 p_start_date,
654 p_end_date)
655 LOOP
656 hr_elements.check_element_freq(
657 p_payroll_id =>p_payroll_id,
658 p_bg_id =>p_business_group_id,
659 p_pay_action_id =>TO_NUMBER(null),
660 p_date_earned =>l_parse_periods.end_date,
661 p_ele_type_id =>l_element_type_id,
662 p_skip_element =>l_skip_element);
663 --
664 IF l_skip_element='N' THEN
665 -- count the rows found
666 l_periods := l_periods + 1;
667 END IF;
668 --
669 --
670 END LOOP;
671 --dbms_output.put_line('10:start'||to_char(p_start_date)||' end '||to_char(ADD_MONTHS(TRUNC(p_start_date,l_reset),1)-1)||' periods '||to_char(l_periods));
672 END;
673 else
674 IF p_start_date <> TRUNC(p_start_date,l_reset) THEN
675 hr_utility.set_location('Frequency Rule Month',2007);
676 BEGIN
677 if g_debug then
678 hr_utility.set_location('get_periods_between ',10);
679 end if;
680 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
681 p_start_date,
682 ADD_MONTHS(TRUNC(p_start_date,l_reset),1) -1)
683 LOOP
684 hr_elements.check_element_freq(
685 p_payroll_id =>p_payroll_id,
686 p_bg_id =>p_business_group_id,
687 p_pay_action_id =>TO_NUMBER(null),
688 p_date_earned =>l_parse_periods.end_date,
689 p_ele_type_id =>l_element_type_id,
690 p_skip_element =>l_skip_element);
691 --
692 IF l_skip_element='N' THEN
693 -- count the rows found
694 l_periods := l_periods + 1;
695 END IF;
696 --
697 --
698 END LOOP;
699 --dbms_output.put_line('10:start'||to_char(p_start_date)||' end '||to_char(ADD_MONTHS(TRUNC(p_start_date,l_reset),1)-1)||' periods '||to_char(l_periods));
700
701 l_start_date := ADD_MONTHS(TRUNC(p_start_date,l_reset),1);
702 END;
703 ELSE
704 l_start_date := p_start_date;
705 END IF;
706 IF NVL(p_end_date, l_end_date) <> (ADD_MONTHS(TRUNC(NVL(p_end_date, l_end_date),l_reset),1) -1) THEN -- 3704632 : Added NVL() to p_end_date
707 hr_utility.set_location('Frequency Other1',2007);
708 BEGIN
709 if g_debug then
710 hr_utility.set_location('get_periods_between ',15);
711 end if;
712 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
713 TRUNC(NVL(p_end_date, l_end_date),l_reset), -- 3704632 : Added NVL() to p_end_date
714 NVL(p_end_date, l_end_date)) -- 3704632 : Added NVL() to p_end_date
715 LOOP
716 hr_elements.check_element_freq(
717 p_payroll_id =>p_payroll_id,
718 p_bg_id =>p_business_group_id,
719 p_pay_action_id =>TO_NUMBER(null),
720 p_date_earned =>l_parse_periods.end_date,
721 p_ele_type_id =>l_element_type_id,
722 p_skip_element =>l_skip_element);
723 --
724 IF l_skip_element='N' THEN
725 -- count the rows found
726 l_periods := l_periods + 1;
727 END IF;
728 --
729 --
730 END LOOP;
731
732 --dbms_output.put_line('15:start'||to_char(p_start_date)||' end '||to_char(ADD_MONTHS(TRUNC(p_start_date,l_reset),1)-1)||' periods '||to_char(l_periods));
733 l_end_date := TRUNC(NVL(p_end_date, l_end_date),l_reset) - 1; -- 3704632 : Added NVL() to p_end_date
734 END;
735 ELSE
736 l_end_date := NVL(p_end_date, l_end_date); -- 3704632 : Added NVL() to p_end_date
737 END IF;
738 IF (l_start_date <= l_end_date ) THEN
739 if g_debug then
740 hr_utility.set_location('get_periods_between ',20);
741 end if;
742 OPEN parse_periods(p_payroll_id => p_payroll_id
743 ,p_rt_start =>l_start_date
744 ,p_eoy =>l_end_date
745 ,p_reset =>l_reset
746 ,p_frq_bitmap_no => l_pay_freq_bitmap_no);
747 --
748 FETCH parse_periods INTO l_tot_perd;
749 --
750 CLOSE parse_periods;
751 --
752 hr_utility.set_location('Frequency Year others 2',2007);
753 l_periods := l_periods + l_tot_perd;
754 -- dbms_output.put_line('20:start'||to_char(l_start_Date)||' end '||to_char(l_end_date)||' periods '||to_char(l_periods));
755 END IF; -- l_start_date <= l_end_date
756 end if;
757
758 END IF;
759 --
760 --bug#4504449 - defaulted to one to avoid error if not from estimate like
761 --elements without frequency
762 if (l_periods = 0 and not p_called_from_est) then
763 --
764 l_periods := 1 ;
765 --
766 end if;
767 --
768 ELSE -- no pay frequency rules
769 CLOSE pay_freq_rule_exists;
770 --
771 IF months_between(p_start_date,nvl(p_end_date,l_end_date)) =12
772 THEN
773 SELECT TPT.number_per_fiscal_year
774 INTO l_periods
775 FROM per_time_period_types TPT,
776 pay_payrolls_f PRL
777 WHERE TPT.period_type = PRL.period_type
778 AND PRL.business_group_id = p_business_group_id
779 AND PRL.payroll_id = p_payroll_id;
780 ELSE
781 OPEN c_count_periods(p_payroll_id,
782 p_start_date,
783 nvl(p_end_date,l_end_date));
784 FETCH c_count_periods into l_tot_perd;
785 CLOSE c_count_periods;
786 --
787 --Bug 2151055
788 --
789 if (l_tot_perd = 0 and not p_called_from_est) then
790 --
791 l_tot_perd := 1 ;
792 --
793 end if;
794 --
795 hr_utility.set_location('Frequency Year other3',2007);
796 l_periods := l_tot_perd;
797 --
798 END IF;
799 END IF;
800 END IF;
801 if g_debug then
802 hr_utility.set_location(' Before if l_periods = 0 ',233);
803 end if;
804 --
805 if l_periods = 0 or l_periods is null then
806 l_periods := l_tot_perd;
807 end if;
808
809 if (l_tot_perd = 0 AND l_periods = 0 and not p_called_from_est) then
810 --
811 -- Raise error as payroll was not found.
812 if g_debug then
813 hr_utility.set_location('l_tot_perd = 0 and l_periods = 0 ' ,234);
814 end if;
815 --
816 fnd_message.set_name('BEN', 'BEN_92346_PAYROLL_NOT_DEFINED');
817 fnd_message.set_token('PROC',l_package);
818 fnd_message.raise_error;
819 end if;
820 --
821 if p_acty_ref_perd_cd = 'EPP' then
822 --
823 if l_period_type = 'Bi-Week' then
824 --
825 if l_periods > 26 then
826 l_periods := 26;
827 end if;
828 --
829 elsif l_period_type = 'Week' then
830 --
831 if l_periods > 52 then
832 l_periods := 52;
833 end if;
834 --
835 end if;
836 --
837 end if;
838 --
839 elsif p_acty_ref_perd_cd = 'PP1' then
840 --
841 -- Pay Period
842 --
843 if p_element_type_id is not null then
844 l_element_type_id:=p_element_type_id;
845 else
846 open c_get_element_type_id;
847 -- ok to be null if p_enrt_rt_id is null
848 fetch c_get_element_type_id into l_element_type_id;
849 close c_get_element_type_id;
850 end if;
851 --
852 l_periods := 0;
853 --
854 if p_payroll_id is null then
855 fnd_message.set_name('BEN','BEN_92403_PAYROLL_ID_REQ');
856 fnd_message.set_token('PROC',l_package);
857 fnd_message.raise_error;
858 end if;
859 --
860 -- Check whether any element rule available for the element type.
861 -- If not, do not call the payroll check routine.
862 --
863 if l_element_type_id is null then
864 l_element_rule_exists := 'N';
865 else
866 open c_element_rule_exists;
867 fetch c_element_rule_exists into l_element_rule_exists;
868 close c_element_rule_exists;
869 end if;
870 --
871 if g_debug then
872 hr_utility.set_location('Pay roll id'||p_payroll_id,111);
873 hr_utility.set_location('start date '||p_start_date,111);
874 hr_utility.set_location('end date '||l_end_date,111);
875 end if;
876 if l_element_rule_exists = 'Y' then
877 --
878 for l_parse_periods in c_parse_periods1(p_payroll_id,
879 p_start_date,
880 nvl(p_end_date,l_end_date))
881 loop
882 hr_elements.check_element_freq(
883 p_payroll_id =>p_payroll_id,
884 p_bg_id =>p_business_group_id,
885 p_pay_action_id =>to_number(null),
886 p_date_earned =>l_parse_periods.end_date,
887 p_ele_type_id =>l_element_type_id,
888 p_skip_element =>l_skip_element);
889 --
890 if l_skip_element='N' then
891 -- count the rows found
892 l_periods := l_periods + 1;
893 end if;
894 --
895 -- removed as gives incorrect answers
896 -- if we're in here then there's a frequency
897 -- rule so we shouldn't be counting the periods
898 -- as we get errors later.
899 --l_tot_perd := l_tot_perd + 1;
900 --
901 --
902 end loop;
903 --
904 else
905 --
906 open c_count_periods(p_payroll_id,
907 p_start_date,
908 nvl(p_end_date,l_end_date));
909 fetch c_count_periods into l_tot_perd;
910 close c_count_periods;
911 --
912 l_periods := l_tot_perd;
913 --
914 end if;
915
916 if l_periods = 0 or l_periods is null then
917 l_periods := l_tot_perd;
918 end if;
919
920 if l_periods = 0 then
921 --
922 -- Raise error as payroll was not found.
923 --
924 fnd_message.set_name('BEN', 'BEN_92346_PAYROLL_NOT_DEFINED');
925 fnd_message.set_token('PROC',l_package);
926 fnd_message.raise_error;
927 end if;
928
929
930 elsif p_acty_ref_perd_cd = 'BWK' then
931 --
932 -- Bi-weekly
933 --
934 l_periods := trunc(((l_end_date - p_start_date)/14),0);
935 --
936 elsif p_acty_ref_perd_cd = 'SMO' then
937 --
938 -- Semi-monthly
939 --
940 l_periods := trunc((months_between(l_end_date, p_start_date) * 2),0);
941 --
942 elsif p_acty_ref_perd_cd = 'MO' then
943 --
944 -- Monthly
945 --
946 l_periods := trunc((months_between(l_end_date, p_start_date)),0);
947 --
948 --Bug 2151055
949 --
950 if (l_periods = 0 and not p_called_from_est) then
951 --
952 l_periods := 1 ;
953 --
954 end if;
955 --
956 elsif p_acty_ref_perd_cd = 'PQU' then
957 --
958 -- Per Quarter
959 --
960 l_periods := trunc((months_between(l_end_date, p_start_date)/3),0);
961 --
962 elsif p_acty_ref_perd_cd = 'SAN' then
963 --
964 -- Semi-Annual
965 --
966 l_periods := trunc((months_between(l_end_date, p_start_date)/6),0);
967 --
968 elsif p_acty_ref_perd_cd = 'PYR' then
969 --
970 -- Annual
971 --
972 l_periods := trunc((months_between(l_end_date, p_start_date)/12),0);
973 --
974 elsif p_acty_ref_perd_cd = 'LFT' then
975 --
976 -- Lifetime.
977 --
978 l_periods := 1;
979
980 else
981 --
982 -- Invalid Activity reference period code.
983 --
984 fnd_message.set_name('BEN','BEN_91299_INV_ACTY_REF_PERD_CD');
985 fnd_message.set_token('PROC',l_package);
986 fnd_message.set_token('ACTY_REF_PERD_CD',p_acty_ref_perd_cd);
987 fnd_message.raise_error;
988 --
989 end if;
990
991 if g_debug then
992 hr_utility.set_location('Number of Periods: '||to_char(l_periods), 90);
993 hr_utility.set_location('Leaving '||l_package , 90);
994 end if;
995
996 return(l_periods);
997
998 exception
999 --
1000 when others then
1001 fnd_message.raise_error;
1002 --
1003 end get_periods_between;
1004 --
1005 --overloaded the function to calculate periods based on cheque dateS
1006 --
1007 --
1008 function get_periods_between(
1009 p_acty_ref_perd_cd in varchar2,
1010 p_start_date in date,
1011 p_end_date in date default null,
1012 p_payroll_id in number default null,
1013 p_business_group_id in number default null,
1014 p_element_type_id in number default null,
1015 p_enrt_rt_id in number default null,
1016 p_effective_date in date default null,
1017 p_use_check_date in boolean
1018 ) return number is
1019 l_package varchar2(80) := g_package || '.get_periods_between';
1020
1021 cursor c_get_element_type_id is
1022 select element_type_id
1023 from ben_enrt_rt ert,
1024 ben_acty_base_rt_f abr
1025 where ert.enrt_rt_id=p_enrt_rt_id and
1026 ert.business_group_id=p_business_group_id and
1027 abr.acty_base_rt_id=ert.acty_base_rt_id and
1028 p_effective_date between
1029 abr.effective_start_date and abr.effective_end_date;
1030 --
1031 l_element_type_id number;
1032 --
1033 cursor c_element_rule_exists is
1034 select 'Y'
1035 from pay_ele_payroll_freq_rules epf
1036 where epf.element_type_id = l_element_type_id
1037 and epf.payroll_id = p_payroll_id
1038 and epf.business_group_id = p_business_group_id;
1039 --
1040 l_element_rule_exists varchar2(1) := 'N';
1041 --
1042 -- Parse Periods gets the information about the
1043 -- number of pay_periods by start date
1044 -- for the rate.
1045 --
1046 cursor c_parse_periods1(v_payroll_id in number,
1047 v_start_date in date,
1048 v_end_date in date) is
1049 select ptp.start_date,
1050 ptp.end_date
1051 from per_time_periods ptp
1052 where ptp.payroll_id = v_payroll_id
1053 and ptp.end_date between
1054 v_start_date and v_end_date;
1055 --
1056 cursor get_period_type(p_payroll_id IN NUMBER
1057 ,p_date IN DATE) is
1058 select period_type
1059 ,pay_date_offset
1060 from pay_all_payrolls_f
1061 where payroll_id = p_payroll_id
1062 and p_date between effective_start_date
1063 and effective_end_date;
1064
1065 --
1066 l_period_type VARCHAR2(30);
1067 l_pay_date_offset number;
1068 --
1069 cursor pay_freq_rule_exists(p_payroll_id IN NUMBER
1070 ,p_element_type_id IN NUMBER) is
1071 select sum(power(2,(FRP.period_no_in_reset_period) - 1))
1072 ,decode(epf.reset_period_type,'Year','YYYY','MM')
1073 from pay_ele_payroll_freq_rules EPF
1074 , pay_freq_rule_periods FRP
1075 where FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
1076 and EPF.payroll_id = p_payroll_id
1077 and EPF.element_type_id = p_element_type_id
1078 group by epf.ele_payroll_freq_rule_id ,epf.reset_period_type;
1079 --
1080 cursor parse_periods(p_payroll_id IN NUMBER
1081 ,p_rt_start IN DATE
1082 ,p_eoy IN DATE
1083 ,p_reset IN VARCHAR2
1084 ,p_frq_bitmap_no IN NUMBER) is
1085 select sum(
1086 ben_distribute_rates.decde_bits(
1087 bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
1088 ))
1089 from per_time_periods
1090 where payroll_id = p_payroll_id
1091 and --end_date -- Bug 6830210
1092 regular_payment_date -- Bug 6830210
1093 between p_rt_start
1094 and p_eoy
1095 group by to_char(end_date,p_reset)
1096 ;
1097
1098 cursor c_count_periods(v_payroll_id in number,
1099 v_start_date in date,
1100 v_end_date in date) is
1101 select count(1)
1102 from per_time_periods ptp
1103 where ptp.payroll_id = v_payroll_id
1104 -- and nvl(ptp.regular_payment_date,ptp.end_date) between
1105 and ptp.end_date between
1106 v_start_date and v_end_date;
1107 -- and nvl(ptp.regular_payment_date,ptp.end_date) >= v_start_date;
1108 --
1109 -- BUG 3159774 looks like a typo- we shouldn't be having
1110 -- ptp.end_date condition and also nvl(ptp.regular_payment_date,ptp.end_date)
1111 -- condition which fails for cases like the one in the bug.
1112 --
1113 cursor c_count_periods_chq(v_payroll_id in number,
1114 v_start_date in date,
1115 v_end_date in date) is
1116 select count(1)
1117 from per_time_periods ptp
1118 where ptp.payroll_id = v_payroll_id
1119 -- and ptp.end_date between
1120 -- v_start_date and v_end_date
1121 -- Bug 6455096
1122 -- and ptp.end_date >= v_start_date
1123 and ptp.regular_payment_date >= v_start_date
1124 -- Bug 6455096
1125 and nvl(ptp.regular_payment_date,ptp.end_date) between
1126 v_start_date and v_end_date;
1127 --
1128 l_periods number := null;
1129 l_start_date date;
1130 l_end_date date;
1131 l_date date;
1132 l_fortnight_end_date date;
1133 l_temp varchar2(1);
1134 l_tot_perd number := 0;
1135 l_skip_element varchar2(30);
1136 l_pay_freq_bitmap_no NUMBER;
1137 l_reset VARCHAR2(30):= 'MM';
1138 l_pay_annualization_factor number ;
1139
1140 begin
1141 --
1142 g_debug := hr_utility.debug_enabled;
1143 --
1144 if g_debug then
1145 hr_utility.set_location('Entering '||l_package, 11);
1146 end if;
1147 --
1148 hr_api.mandatory_arg_error(p_api_name => l_package,
1149 p_argument => 'p_acty_ref_perd_cd',
1150 p_argument_value => p_acty_ref_perd_cd);
1151 --
1152 hr_api.mandatory_arg_error(p_api_name => l_package,
1153 p_argument => 'p_start_date',
1154 p_argument_value => p_start_date);
1155
1156 --
1157 if g_debug then
1158 hr_utility.set_location('p_acty_ref_perd_cd '||p_acty_ref_perd_cd,21);
1159 hr_utility.set_location('p_start_date '||to_char(p_start_date),31);
1160 hr_utility.set_location('p_end_date '||to_char(p_end_date),41);
1161 end if;
1162 --
1163 if p_end_date is null then
1164 --
1165 -- End of the year (of the start date)
1166 --
1167 l_end_date := add_months(trunc(p_start_date,'YYYY'),12);
1168 --
1169 else
1170 --
1171 -- Start Date should be less than end date.
1172 --
1173 if p_end_date < p_start_date then
1174 --
1175 fnd_message.set_name('BEN','BEN_91824_START_DT_AFTR_END_DT');
1176 fnd_message.set_token('PROC',l_package);
1177 fnd_message.set_token('START_DT',to_char(p_start_date));
1178 fnd_message.set_token('END_DT',to_char(p_end_date));
1179 fnd_message.raise_error;
1180 --
1181 else
1182 --
1183 -- need to add 1 day to correctly calc time between 2 dates.
1184 -- because we are truncating down we get the correct answer.
1185 -- ie months_between(1/1/99,12/31/99) = 11.999999999
1186 -- by adding 1 day to the end date we get the correct answer
1187 -- of 12
1188 --
1189 l_end_date := p_end_date + 1;
1190 --
1191 end if;
1192 --
1193 end if;
1194 --
1195 if g_debug then
1196 hr_utility.set_location(l_package,51);
1197 end if;
1198 --
1199 if p_acty_ref_perd_cd = 'PWK' then
1200 --
1201 -- Weekly
1202 --
1203 l_periods := trunc(((l_end_date - p_start_date)/7),0);
1204 --
1205 elsif p_acty_ref_perd_cd = 'PHR' then
1206 --
1207 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
1208 if l_pay_annualization_factor is null then
1209 l_pay_annualization_factor := 2080;
1210 end if;
1211 --
1212 l_periods := trunc(((l_end_date - p_start_date)/365 * l_pay_annualization_factor),0);
1213 --
1214 elsif p_acty_ref_perd_cd = 'PP' or p_acty_ref_perd_cd = 'EPP'
1215 or p_acty_ref_perd_cd = 'PPF' then
1216 --
1217 -- Pay Period
1218 --
1219 if p_element_type_id is not null then
1220 l_element_type_id:=p_element_type_id;
1221 else
1222 open c_get_element_type_id;
1223 -- ok to be null if p_enrt_rt_id is null
1224 fetch c_get_element_type_id into l_element_type_id;
1225 close c_get_element_type_id;
1226 end if;
1227 --
1228 l_periods := 0;
1229 --
1230 if p_payroll_id is null then
1231 fnd_message.set_name('BEN','BEN_92403_PAYROLL_ID_REQ');
1232 fnd_message.set_token('PROC',l_package);
1233 fnd_message.raise_error;
1234 end if;
1235 -- Check if period_type of the payroll is
1236 -- either Calendar or Lunar month
1237 -- Standard skip rules aren't applied to these
1238 --
1239 open get_period_type(p_payroll_id,p_start_date);
1240 fetch get_period_type into l_period_type, l_pay_date_offset;
1241 close get_period_type;
1242 --
1243 if l_period_type in ('Calendar Month','Lunar Month') then
1244 if p_use_check_date then
1245 --
1246 OPEN c_count_periods_chq(p_payroll_id,
1247 p_start_date,
1248 nvl(p_end_date,l_end_date));
1249 FETCH c_count_periods_chq into l_tot_perd;
1250 CLOSE c_count_periods_chq;
1251 --
1252 else
1253 --
1254 select count(*)
1255 into l_periods
1256 from per_time_periods
1257 where payroll_id = p_payroll_id
1258 and end_date
1259 between p_start_date
1260 and p_end_date;
1261 --
1262 end if;
1263 else
1264 -- Check whether any element rule available for the element type.
1265 -- If not, do not call the payroll check routine.
1266 --
1267 open pay_freq_rule_exists(p_payroll_id,l_element_type_id);
1268 --
1269 fetch pay_freq_rule_exists into l_pay_freq_bitmap_no,l_reset;
1270 --
1271 if pay_freq_rule_exists%FOUND THEN
1272 --
1273 close pay_freq_rule_exists;
1274 --
1275 IF l_reset <>'MM' THEN -- Year
1276 BEGIN
1277 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
1278 p_start_date,
1279 nvl(p_end_date,l_end_date))
1280 LOOP
1281 hr_elements.check_element_freq(
1282 p_payroll_id =>p_payroll_id,
1283 p_bg_id =>p_business_group_id,
1284 p_pay_action_id =>TO_NUMBER(null),
1285 p_date_earned =>l_parse_periods.end_date,
1286 p_ele_type_id =>l_element_type_id,
1287 p_skip_element =>l_skip_element);
1288 --
1289 IF l_skip_element='N' THEN
1290 -- count the rows found
1291 l_periods := l_periods + 1;
1292 END IF;
1293 --
1294 --
1295 END LOOP;
1296 END;
1297 ELSE -- Month
1298
1299 --Bug 6913654, Added 'if..else' condition if p_start_date and p_end_date fall in the same month.Old code does not handle this.
1300 IF TRUNC(p_start_date,l_reset) = TRUNC(p_end_date,l_reset) THEN
1301 BEGIN
1302 if g_debug then
1303 hr_utility.set_location('get_periods_between ',10);
1304 end if;
1305 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
1306 p_start_date,
1307 p_end_date)
1308 LOOP
1309 hr_elements.check_element_freq(
1310 p_payroll_id =>p_payroll_id,
1311 p_bg_id =>p_business_group_id,
1312 p_pay_action_id =>TO_NUMBER(null),
1313 p_date_earned =>l_parse_periods.end_date,
1314 p_ele_type_id =>l_element_type_id,
1315 p_skip_element =>l_skip_element);
1316 --
1317 IF l_skip_element='N' THEN
1318 -- count the rows found
1319 l_periods := l_periods + 1;
1320 END IF;
1321 --
1322 --
1323 END LOOP;
1324 end;
1325 else
1326 IF p_start_date <> TRUNC(p_start_date,l_reset) THEN
1327 BEGIN
1328 if g_debug then
1329 hr_utility.set_location('get_periods_between ',10);
1330 end if;
1331 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
1332 p_start_date,
1333 ADD_MONTHS(TRUNC(p_start_date,l_reset),1) -1)
1334 LOOP
1335 hr_elements.check_element_freq(
1336 p_payroll_id =>p_payroll_id,
1337 p_bg_id =>p_business_group_id,
1338 p_pay_action_id =>TO_NUMBER(null),
1339 p_date_earned =>l_parse_periods.end_date,
1340 p_ele_type_id =>l_element_type_id,
1341 p_skip_element =>l_skip_element);
1342 --
1343 IF l_skip_element='N' THEN
1344 -- count the rows found
1345 l_periods := l_periods + 1;
1346 END IF;
1347 --
1348 --
1349 END LOOP;
1350
1351 l_start_date := ADD_MONTHS(TRUNC(p_start_date,l_reset),1);
1352 END;
1353 ELSE
1354 l_start_date := p_start_date;
1355 END IF;
1356 IF p_end_date <> (ADD_MONTHS(TRUNC(p_end_date,l_reset),1) -1) THEN
1357 BEGIN
1358 if g_debug then
1359 hr_utility.set_location('get_periods_between ',15);
1360 end if;
1361 FOR l_parse_periods IN c_parse_periods1(p_payroll_id,
1362 TRUNC(p_end_date,l_reset),
1363 p_end_date)
1364 LOOP
1365 hr_elements.check_element_freq(
1366 p_payroll_id =>p_payroll_id,
1367 p_bg_id =>p_business_group_id,
1368 p_pay_action_id =>TO_NUMBER(null),
1369 p_date_earned =>l_parse_periods.end_date,
1370 p_ele_type_id =>l_element_type_id,
1371 p_skip_element =>l_skip_element);
1372 --
1373 IF l_skip_element='N' THEN
1374 -- count the rows found
1375 l_periods := l_periods + 1;
1376 END IF;
1377 --
1378 --
1379 END LOOP;
1380
1381 l_end_date := TRUNC(p_end_date,l_reset) - 1;
1382 END;
1383 ELSE
1384 l_end_date := p_end_date;
1385 END IF;
1386 IF (l_start_date <= l_end_date ) THEN
1387 if g_debug then
1388 hr_utility.set_location('get_periods_between ',20);
1389 end if;
1390 OPEN parse_periods(p_payroll_id => p_payroll_id
1391 ,p_rt_start =>l_start_date
1392 ,p_eoy =>l_end_date
1393 ,p_reset =>l_reset
1394 ,p_frq_bitmap_no => l_pay_freq_bitmap_no);
1395 --
1396 FETCH parse_periods INTO l_tot_perd;
1397 --
1398 CLOSE parse_periods;
1399 --
1400 l_periods := l_periods + l_tot_perd;
1401 END IF; -- l_start_date <= l_end_date
1402 end if;
1403 END IF;
1404 ELSE -- no pay frequency rules
1405 CLOSE pay_freq_rule_exists;
1406 --
1407 /* IF months_between(p_start_date,nvl(p_end_date,l_end_date)) =12
1408 THEN
1409 SELECT TPT.number_per_fiscal_year
1410 INTO l_periods
1411 FROM per_time_period_types TPT,
1412 pay_payrolls_f PRL
1413 WHERE TPT.period_type = PRL.period_type
1414 AND PRL.business_group_id = p_business_group_id
1415 AND PRL.payroll_id = p_payroll_id;
1416 */
1417 OPEN c_count_periods_chq(p_payroll_id,
1418 p_start_date,
1419 nvl(p_end_date,l_end_date));
1420 FETCH c_count_periods_chq into l_tot_perd;
1421 CLOSE c_count_periods_chq;
1422 --
1423 --Bug 2151055
1424 --
1425 if l_tot_perd = 0 then
1426 --
1427 l_tot_perd := 1 ;
1428 --
1429 end if;
1430 --
1431 l_periods := l_tot_perd;
1432 --
1433 END IF;
1434 END IF;
1435 if g_debug then
1436 hr_utility.set_location(' Before if l_periods = 0 ',233);
1437 end if;
1438 --
1439 if l_periods = 0 or l_periods is null then
1440 l_periods := l_tot_perd;
1441 end if;
1442
1443 if (l_tot_perd = 0 AND l_periods = 0) then
1444 --
1445 -- Raise error as payroll was not found.
1446 if g_debug then
1447 hr_utility.set_location('l_tot_perd = 0 and l_periods = 0 ' ,234);
1448 end if;
1449 --
1450 fnd_message.set_name('BEN', 'BEN_92346_PAYROLL_NOT_DEFINED');
1451 fnd_message.set_token('PROC',l_package);
1452 fnd_message.raise_error;
1453 end if;
1454 --
1455 if p_acty_ref_perd_cd = 'EPP' then
1456 --
1457 if l_period_type = 'Bi-Week' then
1458 --
1459 if l_periods > 26 then
1460 l_periods := 26;
1461 end if;
1462 --
1463 elsif l_period_type = 'Week' then
1464 --
1465 if l_periods > 52 then
1466 l_periods := 52;
1467 end if;
1468 --
1469 end if;
1470 --
1471 end if;
1472 --
1473
1474 elsif p_acty_ref_perd_cd = 'BWK' then
1475 --
1476 -- Bi-weekly
1477 --
1478 l_periods := trunc(((l_end_date - p_start_date)/14),0);
1479 --
1480 elsif p_acty_ref_perd_cd = 'SMO' then
1481 --
1482 -- Semi-monthly
1483 --
1484 l_periods := trunc((months_between(l_end_date, p_start_date) * 2),0);
1485 --
1486 elsif p_acty_ref_perd_cd = 'MO' then
1487 --
1488 -- Monthly
1489 --
1490 l_periods := trunc((months_between(l_end_date, p_start_date)),0);
1491 --
1492 --Bug 2151055
1493 --
1494 if l_periods = 0 then
1495 --
1496 l_periods := 1 ;
1497 --
1498 end if;
1499 --
1500 elsif p_acty_ref_perd_cd = 'PQU' then
1501 --
1502 -- Per Quarter
1503 --
1504 l_periods := trunc((months_between(l_end_date, p_start_date)/3),0);
1505 --
1506 elsif p_acty_ref_perd_cd = 'SAN' then
1507 --
1508 -- Semi-Annual
1509 --
1510 l_periods := trunc((months_between(l_end_date, p_start_date)/6),0);
1511 --
1512 elsif p_acty_ref_perd_cd = 'PYR' then
1513 --
1514 -- Annual
1515 --
1516 l_periods := trunc((months_between(l_end_date, p_start_date)/12),0);
1517 --
1518 elsif p_acty_ref_perd_cd = 'LFT' then
1519 --
1520 -- Lifetime.
1521 --
1522 l_periods := 1;
1523
1524 else
1525 --
1526 -- Invalid Activity reference period code.
1527 --
1528 fnd_message.set_name('BEN','BEN_91299_INV_ACTY_REF_PERD_CD');
1529 fnd_message.set_token('PROC',l_package);
1530 fnd_message.set_token('ACTY_REF_PERD_CD',p_acty_ref_perd_cd);
1531 fnd_message.raise_error;
1532 --
1533 end if;
1534
1535 if g_debug then
1536 hr_utility.set_location('Number of Periods: '||to_char(l_periods), 90);
1537 hr_utility.set_location('Leaving '||l_package , 90);
1538 end if;
1539
1540 return(l_periods);
1541
1542 exception
1543 --
1544 when others then
1545 fnd_message.raise_error;
1546 --
1547 end get_periods_between;
1548 --
1549 ---------------------------------------------------------------------------
1550 -- This procedure is used to estimate the balance for the activity base rate
1551 -- for the peiod defined by p_date_from and p_date_to.
1552 -- The estimation is based on element entries and their corresponding values.
1553 --
1554 -- Scope of function: Internal.
1555 ---------------------------------------------------------------------------
1556 procedure estimate_balance
1557 (p_person_id in number,
1558 p_acty_base_rt_id in number,
1559 p_payroll_id in number,
1560 p_effective_date in date,
1561 p_business_group_id in number,
1562 p_date_from in date,
1563 p_date_to in date,
1564 p_balance out nocopy number) is
1565 --
1566 -- This cursor gets all the possible element entries which can in the
1567 -- date range of p_date_from and p_date_to.
1568 --
1569 cursor c_element is
1570 select to_number(evl.screen_entry_value) entry_value,
1571 evl.effective_start_date,
1572 evl.effective_end_date,
1573 ety.processing_type,
1574 asg.payroll_id,
1575 eln.element_type_id
1576 from ben_prtt_rt_val prv,
1577 ben_per_in_ler pil,
1578 pay_element_entry_values_f evl,
1579 per_all_assignments_f asg,
1580 pay_element_entries_f een,
1581 pay_element_links_f eln,
1582 pay_element_types_f ety
1583 where prv.acty_base_rt_id = p_acty_base_rt_id
1584 and prv.per_in_ler_id = pil.per_in_ler_id
1585 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1586 and prv.element_entry_value_id = evl.element_entry_value_id
1587 and evl.element_entry_id = een.element_entry_id
1588 and een.assignment_id = asg.assignment_id
1589 and asg.person_id = p_person_id
1590 and een.element_link_id = eln.element_link_id
1591 and eln.element_type_id = ety.element_type_id
1592 and prv.prtt_rt_val_stat_cd is null
1593 and prv.business_group_id = p_business_group_id
1594 and evl.effective_start_date <= p_date_to
1595 and evl.effective_end_date >= p_date_from
1596 and evl.effective_start_date between
1597 prv.rt_strt_dt and prv.rt_end_dt
1598 and evl.effective_start_date between
1599 asg.effective_start_date and asg.effective_end_date
1600 and evl.effective_start_date between
1601 een.effective_start_date and een.effective_end_date
1602 and evl.effective_start_date between
1603 eln.effective_start_date and eln.effective_end_date
1604 and evl.effective_start_date between
1605 ety.effective_start_date and ety.effective_end_date;
1606 --
1607 l_estimate number := 0;
1608 l_start_date date;
1609 l_end_date date;
1610 l_periods number;
1611 l_package varchar2(80) := g_package || '.estimate_balance';
1612 --
1613 begin
1614 --
1615 g_debug := hr_utility.debug_enabled;
1616 --
1617 if g_debug then
1618 hr_utility.set_location('Entering '||l_package, 10);
1619 end if;
1620 --
1621 for l_element in c_element loop
1622 --
1623 if l_element.processing_type = 'R' then
1624 --
1625 -- If the amount is recurring, multiply by number of pay periods.
1626 --
1627 l_start_date := l_element.effective_start_date;
1628 l_end_date := l_element.effective_end_date;
1629 --
1630 -- The start date of element should not be less than the p_date_from.
1631 -- If it is less than that, we make the start date as the p_date_from
1632 -- as we are only interested after the p_date_from.
1633 --
1634 if l_start_date < p_date_from then
1635 --
1636 l_start_date := p_date_from;
1637 --
1638 end if;
1639 --
1640 -- Similar logic applies to the end date.
1641 --
1642 if l_end_date > p_date_to then
1643 --
1644 l_end_date := p_date_to;
1645 --
1646 end if;
1647 --
1648 if g_debug then
1649 hr_utility.set_location('strt dt '||to_char(l_start_date), 110);
1650 hr_utility.set_location('end dt '||to_char(l_end_date), 110);
1651 end if;
1652 l_periods := get_periods_between
1653 (p_start_date => l_start_date,
1654 p_end_date => l_end_date,
1655 p_acty_ref_perd_cd => 'PP',
1656 /* Bug 3394862 We need to get the l_element.payroll_id first
1657 if null then get from p_payroll_id .. Why do you have null...
1658 p_payroll_id => nvl(p_payroll_id,
1659 l_element.payroll_id), */
1660 p_payroll_id => nvl(l_element.payroll_id,p_payroll_id),
1661 p_business_group_id => p_business_group_id,
1662 p_element_type_id => l_element.element_type_id,
1663 p_effective_date => p_effective_date,
1664 p_called_from_est => true
1665 );
1666 --
1667 l_estimate := l_estimate + (l_periods * l_element.entry_value) + 0;
1668 --
1669 else
1670 --
1671 l_estimate := l_estimate + l_element.entry_value + 0;
1672 --
1673 end if;
1674 --
1675 end loop;
1676 --
1677 p_balance := nvl(l_estimate,0);
1678 --
1679 if g_debug then
1680 hr_utility.set_location('Estimated Balance'||p_balance,11);
1681 hr_utility.set_location('Leaving '||l_package, 10);
1682 end if;
1683 --
1684 end estimate_balance;
1685 --
1686 ---------------------------------------------------------------------------
1687 -- The function calculates the balance as of effective date.
1688 --
1689 -- Scope of function: Internal only.
1690 ---------------------------------------------------------------------------
1691 function get_balance
1692 (p_enrt_rt_id in number default null,
1693 p_person_id in number default null,
1694 p_per_in_ler_id in number default null,
1695 p_pgm_id in number default null,
1696 p_pl_id in number default null,
1697 p_oipl_id in number default null,
1698 p_enrt_perd_id in number default null,
1699 p_lee_rsn_id in number default null,
1700 p_acty_base_rt_id in number default null,
1701 p_payroll_id in number default null,
1702 p_ptd_comp_lvl_fctr_id in number default null,
1703 p_det_pl_ytd_cntrs_cd in varchar2 default null,
1704 p_lf_evt_ocrd_dt in date default null,
1705 p_business_group_id in number,
1706 p_start_date in date,
1707 p_end_date in date default null,
1708 p_effective_date in date)
1709 return number
1710 is
1711 --
1712 cursor c_ecr is
1713 select ecr.ptd_comp_lvl_fctr_id,
1714 ecr.elig_per_elctbl_chc_id,
1715 ecr.enrt_bnft_id,
1716 ecr.acty_base_rt_id,
1717 abr.det_pl_ytd_cntrs_cd,
1718 abr.parnt_chld_cd
1719 from ben_enrt_rt ecr,
1720 ben_acty_base_rt_f abr
1721 where ecr.enrt_rt_id = p_enrt_rt_id
1722 and ecr.business_group_id = p_business_group_id
1723 and ecr.acty_base_rt_id = abr.acty_base_rt_id
1724 and p_effective_date between
1725 abr.effective_start_date and abr.effective_end_date;
1726 --
1727 l_ecr c_ecr%rowtype;
1728 --
1729 cursor c_epe is
1730 select epe.per_in_ler_id,
1731 epe.pgm_id,
1732 epe.pl_id,
1733 epe.oipl_id,
1734 pel.lee_rsn_id,
1735 pel.enrt_perd_id,
1736 pil.person_id,
1737 pil.lf_evt_ocrd_dt
1738 from ben_elig_per_elctbl_chc epe,
1739 ben_pil_elctbl_chc_popl pel,
1740 ben_per_in_ler pil
1741 where epe.elig_per_elctbl_chc_id = l_ecr.elig_per_elctbl_chc_id
1742 and epe.per_in_ler_id = pil.per_in_ler_id
1743 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1744 and pel.per_in_ler_id = pil.per_in_ler_id
1745 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
1746 UNION
1747 select epe.per_in_ler_id,
1748 epe.pgm_id,
1749 epe.pl_id,
1750 epe.oipl_id,
1751 pel.lee_rsn_id,
1752 pel.enrt_perd_id,
1753 pil.person_id,
1754 pil.lf_evt_ocrd_dt
1755 from ben_enrt_bnft enb,
1756 ben_elig_per_elctbl_chc epe,
1757 ben_pil_elctbl_chc_popl pel,
1758 ben_per_in_ler pil
1759 where enb.enrt_bnft_id = l_ecr.enrt_bnft_id
1760 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
1761 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1762 and pel.per_in_ler_id = pil.per_in_ler_id
1763 and epe.per_in_ler_id = pil.per_in_ler_id
1764 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
1765 --
1766 l_epe c_epe%rowtype;
1767 --
1768 cursor c_clf is
1769 select clf.comp_src_cd,
1770 clf.defined_balance_id
1771 from ben_comp_lvl_fctr clf
1772 where clf.comp_lvl_fctr_id = l_ecr.ptd_comp_lvl_fctr_id
1773 and clf.business_group_id = p_business_group_id;
1774 --
1775 l_clf c_clf%rowtype;
1776 --
1777 -- This cursor gets the date till which balances are found.
1778 --
1779 cursor c_bal_date(v_start_date date, v_end_date in date) is
1780 select max(pac.effective_date) + 1
1781 from pay_person_latest_balances plb,
1782 per_all_assignments_f asg,
1783 pay_assignment_actions paa,
1784 pay_payroll_actions pac
1785 where plb.person_id = l_epe.person_id
1786 and asg.assignment_type <> 'C'
1787 and plb.defined_balance_id = l_clf.defined_balance_id
1788 and asg.person_id = l_epe.person_id
1789 and asg.primary_flag = 'Y'
1790 and asg.assignment_id = paa.assignment_id
1791 and paa.payroll_action_id = pac.payroll_action_id
1792 and asg.business_group_id = p_business_group_id
1793 and p_effective_date between
1794 asg.effective_start_date and asg.effective_end_date
1795 and pac.effective_date between
1796 v_start_date and v_end_date;
1797 --
1798 cursor c_abr2
1799 (c_effective_date in date,
1800 c_acty_base_rt_id in number
1801 )
1802 is
1803 select abr2.det_pl_ytd_cntrs_cd
1804 from ben_acty_base_rt_f abr,
1805 ben_acty_base_rt_f abr2
1806 where abr.acty_base_rt_id = c_acty_base_rt_id
1807 and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
1808 and abr2.parnt_chld_cd = 'PARNT'
1809 and c_effective_date
1810 between abr.effective_start_date
1811 and abr.effective_end_date
1812 and c_effective_date
1813 between abr2.effective_start_date
1814 and abr2.effective_end_date;
1815
1816
1817
1818 l_enrt_cvg_strt_dt date;
1819 l_enrt_cvg_end_dt date;
1820 l_rt_strt_dt date;
1821 l_rt_end_dt date;
1822 l_enrt_cvg_strt_dt_cd varchar2(30);
1823 l_enrt_cvg_end_dt_cd varchar2(30);
1824 l_rt_strt_dt_cd varchar2(30);
1825 l_rt_end_dt_cd varchar2(30);
1826 l_enrt_cvg_strt_dt_rl number;
1827 l_enrt_cvg_end_dt_rl number;
1828 l_rt_strt_dt_rl number;
1829 l_rt_end_dt_rl number;
1830 --
1831 l_start_date date := p_start_date;
1832 l_balance_amt number := 0;
1833 l_estimated_bal number := 0;
1834 --
1835 l_package varchar2(80) := g_package || '.get_balance';
1836 --
1837 begin
1838 --
1839 if g_debug then
1840 hr_utility.set_location('Entering '||l_package, 10);
1841 end if;
1842 --
1843 if p_enrt_rt_id is not null then
1844 --
1845 open c_ecr;
1846 fetch c_ecr into l_ecr;
1847 close c_ecr;
1848 --
1849 if l_ecr.det_pl_ytd_cntrs_cd is null and l_ecr.parnt_chld_cd = 'CHLD' then
1850 --
1851 open c_abr2(p_effective_date, l_ecr.acty_base_rt_id);
1852 fetch c_abr2 into l_ecr.det_pl_ytd_cntrs_cd;
1853 close c_abr2;
1854 end if;
1855 --
1856 open c_epe;
1857 fetch c_epe into l_epe;
1858 close c_epe;
1859 --
1860 else
1861 --
1862 l_epe.person_id := p_person_id;
1863 l_epe.per_in_ler_id := p_per_in_ler_id;
1864 l_epe.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
1865 l_epe.pgm_id := p_pgm_id;
1866 l_epe.pl_id := p_pl_id;
1867 l_epe.oipl_id := p_oipl_id;
1868 l_epe.lee_rsn_id := p_lee_rsn_id;
1869 l_epe.enrt_perd_id := p_enrt_perd_id;
1870 l_ecr.ptd_comp_lvl_fctr_id := p_ptd_comp_lvl_fctr_id;
1871 l_ecr.acty_base_rt_id := p_acty_base_rt_id;
1872 l_ecr.det_pl_ytd_cntrs_cd := p_det_pl_ytd_cntrs_cd;
1873 --
1874 end if;
1875 --
1876 if l_ecr.det_pl_ytd_cntrs_cd is null or
1877 l_start_date is null then
1878 --
1879 if g_debug then
1880 hr_utility.set_location('Leaving '||l_package , 91);
1881 end if;
1882 return(0);
1883 --
1884 end if;
1885 --
1886 -- Get the rate end date.
1887 --
1888 ben_determine_date.rate_and_coverage_dates
1889 (p_which_dates_cd => 'R'
1890 ,p_date_mandatory_flag => 'N'
1891 ,p_compute_dates_flag => 'Y'
1892 ,p_business_group_id => p_business_group_id
1893 ,P_PER_IN_LER_ID => l_epe.per_in_ler_id
1894 ,P_PERSON_ID => l_epe.person_id
1895 ,P_PGM_ID => l_epe.pgm_id
1896 ,P_PL_ID => l_epe.pl_id
1897 ,P_OIPL_ID => l_epe.oipl_id
1898 ,P_LEE_RSN_ID => l_epe.lee_rsn_id
1899 ,P_ENRT_PERD_ID => l_epe.enrt_perd_id
1900 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1901 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
1902 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
1903 ,p_rt_strt_dt => l_rt_strt_dt
1904 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
1905 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
1906 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
1907 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
1908 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
1909 ,p_rt_end_dt => l_rt_end_dt
1910 ,p_rt_end_dt_cd => l_rt_end_dt_cd
1911 ,p_rt_end_dt_rl => l_rt_end_dt_rl
1912 ,p_effective_date => p_effective_date
1913 ,p_lf_evt_ocrd_dt => l_epe.lf_evt_ocrd_dt
1914 );
1915 -- when this function called from reimbursement reqist
1916 --- the end date is sent as parameter bug : 1182293
1917
1918 if p_end_date is not null then
1919 l_rt_end_dt := p_end_date ;
1920 end if ;
1921 ---
1922 if g_debug then
1923 hr_utility.set_location('yr start date ' || l_start_date , 293);
1924 hr_utility.set_location('l_rt_strt_dt '|| l_rt_strt_dt, 293);
1925 hr_utility.set_location('end date ' || l_rt_end_dt , 293);
1926 hr_utility.set_location('level facotr ' || l_ecr.ptd_comp_lvl_fctr_id , 293);
1927 end if;
1928
1929
1930 if l_rt_end_dt is null or
1931 l_start_date > l_rt_end_dt then
1932 --
1933 -- As the year period start date is greater than the rate
1934 -- end date, you might be in the new year. So no YTD balances
1935 -- available.
1936 -- For e.g: Year start date is 1/1/90 and the rate end date
1937 -- calculated is 12/31/89. Here the old rate will belong in the
1938 -- previous year (1989) and should not be used for current year (1990)
1939 --
1940 if g_debug then
1941 hr_utility.set_location('Leaving '||l_package , 92);
1942 end if;
1943 return(0);
1944 --
1945 end if;
1946 --
1947 open c_clf;
1948 fetch c_clf into l_clf;
1949 close c_clf;
1950 --
1951 if l_ecr.det_pl_ytd_cntrs_cd in ('BALONLY', 'BALTHENEST','BTDADDEST') and
1952 l_ecr.ptd_comp_lvl_fctr_id is not null then
1953 --
1954 -- Balances.
1955 --
1956 ben_derive_factors.determine_compensation
1957 (p_comp_lvl_fctr_id => l_ecr.ptd_comp_lvl_fctr_id,
1958 p_person_id => l_epe.person_id,
1959 p_pgm_id => l_epe.pgm_id,
1960 p_pl_id => l_epe.pl_id,
1961 p_oipl_id => l_epe.oipl_id,
1962 p_per_in_ler_id => l_epe.per_in_ler_id,
1963 p_business_group_id => p_business_group_id,
1964 p_perform_rounding_flg => TRUE,
1965 p_effective_date => p_effective_date,
1966 p_lf_evt_ocrd_dt => l_epe.lf_evt_ocrd_dt,
1967 p_calc_bal_to_date => l_rt_end_dt,
1968 p_value => l_balance_amt);
1969 --
1970 -- Get the date till which balances have been found.
1971 -- We require this date only when balance to-date is added to estimate
1972 -- (BTDADDEST)
1973 -- This date also makes sense only for defined balances (BALTYP).
1974 --
1975 if g_debug then
1976 hr_utility.set_location('balance '||l_balance_amt , 92);
1977 end if;
1978 if l_clf.comp_src_cd = 'BALTYP' and
1979 l_ecr.det_pl_ytd_cntrs_cd = 'BTDADDEST' and
1980 l_balance_amt > 0 then
1981 --
1982 open c_bal_date(l_start_date,l_rt_end_dt);
1983 fetch c_bal_date into l_start_date;
1984 close c_bal_date;
1985 --
1986 end if;
1987 --
1988 end if;
1989 --
1990 -- Compute estimates in three cases.
1991 -- 1. Only Estimtes (ESTONLY)
1992 -- 2. To-date balances to be added to estimates. Do this only when
1993 -- we are dealing with defined balances.(BTDADDEST)
1994 -- 3. If balances are not available, we need the estimates.(BALTHENEST)
1995 --
1996 if (l_ecr.det_pl_ytd_cntrs_cd = 'ESTONLY') OR
1997 (l_ecr.det_pl_ytd_cntrs_cd = 'BTDADDEST'
1998 and l_rt_end_dt > l_start_date
1999 and l_clf.comp_src_cd = 'BALTYP') OR
2000 (l_ecr.det_pl_ytd_cntrs_cd = 'BALTHENEST'
2001 and nvl(l_balance_amt,0) = 0) then
2002 --
2003 -- Bug 3430334 The estimate balance is from the Yr Period start Date to the new
2004 -- Rate start Date. So the parametes should be Yr Period Start Date for p_date_from
2005 -- For p_date_to it has to be one day before the new rate start date.
2006 -- if l_rt_end_dt is greater than the rate start date, we need to take the rates
2007 -- rate start date - 1 else take the rate end end date.
2008 --
2009 if g_debug then
2010 hr_utility.set_location(' p_date_to '||least(l_rt_strt_dt-1, l_rt_end_dt),10);
2011 hr_utility.set_location(' l_start_date'||l_start_date,10);
2012 end if;
2013 --
2014 estimate_balance(p_person_id => l_epe.person_id,
2015 p_acty_base_rt_id => l_ecr.acty_base_rt_id,
2016 p_payroll_id => p_payroll_id,
2017 p_effective_date => p_effective_date,
2018 p_business_group_id => p_business_group_id,
2019 p_date_from => l_start_date,
2020 p_date_to => least(l_rt_strt_dt-1, l_rt_end_dt) , --Bug 3430334
2021 p_balance => l_estimated_bal);
2022 --
2023 end if;
2024 --
2025 if g_debug then
2026 hr_utility.set_location('Leaving '||l_package , 99);
2027 end if;
2028 --
2029 return(nvl(l_balance_amt,0) +l_estimated_bal);
2030 --
2031 exception
2032 --
2033 when others then
2034 fnd_message.raise_error;
2035 --
2036 end get_balance;
2037 ---------------------------------------------------------------------------
2038 -- This procedure set the start date, end date, activity reference
2039 -- period code if they are not supplied. To set these parameters
2040 -- it uses the enrt_rt_id or elig_per_elctbl_chc_id. The parameters are
2041 -- set only if they are null. But when complete year flag is on, then
2042 -- the start date and end date are always set using plan year period.
2043 -- To get what sets what, refer to the following logic.
2044 --
2045 -- Priority 1:
2046 -- p_complete_year_flag = 'Y'
2047 -- p_start_date is set to ben_yr_perd.start_date
2048 -- p_end_date is set to ben_yr_perd.end_date
2049 -- In this case, the start date and end date values are overridden
2050 -- even if these date parameters are not null.
2051 --
2052 -- Priority 2:
2053 -- p_enrt_rt_id is not null
2054 -- p_start_date is set to ben_enrt_rt.rt_strt_dt
2055 -- p_end_date is set to ben_yr_perd.end_date
2056 -- p_acty_ref_perd_cd is set to ben_pil_elctbl_chc_popl.acty_ref_perd_cd
2057 -- In this case, the parameters are set only if they are null.
2058 --
2059 -- Priority 3:
2060 -- p_elig_per_elctbl_chc_id is not null
2061 -- p_start_date is set to ben_elig_per_elctbl_chc.enrt_cvg_strt_dt
2062 -- p_end_date is set to ben_yr_perd.end_date
2063 -- p_acty_ref_perd_cd is set to ben_pil_elctbl_chc_popl.acty_ref_perd_cd
2064 -- In this case, the parameters are set only if they are null.
2065 ---------------------------------------------------------------------------
2066 procedure set_default_dates(p_enrt_rt_id in number,
2067 p_elig_per_elctbl_chc_id in number,
2068 p_business_group_id in number,
2069 p_complete_year_flag in varchar2,
2070 p_effective_date in date,
2071 p_payroll_id in number default null,
2072 p_lf_evt_ocrd_dt in date default null,
2073 p_start_date in out nocopy date,
2074 p_end_date in out nocopy date,
2075 p_acty_ref_perd_cd in out nocopy varchar2,
2076 p_yr_start_date out nocopy date)
2077 is
2078 --
2079 l_package varchar2(80) := g_package ||'.set_default_dates';
2080 l_elig_per_elctbl_chc_id number := p_elig_per_elctbl_chc_id;
2081 l_start_date date := p_start_date;
2082 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
2083 l_start_date_cd varchar2(30);
2084 l_start_date_rl number;
2085 l_acty_base_rt_id number;
2086 l_enrt_perd_start_dt date;
2087 l_yr_perd_id number;
2088 l_start_date_nc_buffer date := p_start_date; -- no copy changes
2089 l_end_date_nc_buffer date := p_end_date; -- no copy changes
2090 --START 3191928
2091 l_enrt_cvg_strt_dt date;
2092 l_enrt_cvg_end_dt date;
2093 l_rt_strt_dt date;
2094 l_rt_end_dt date;
2095 l_enrt_cvg_strt_dt_cd varchar2(30);
2096 l_enrt_cvg_end_dt_cd varchar2(30);
2097 l_rt_strt_dt_cd varchar2(30);
2098 l_rt_end_dt_cd varchar2(30);
2099 l_enrt_cvg_strt_dt_rl number;
2100 l_enrt_cvg_end_dt_rl number;
2101 l_rt_strt_dt_rl number;
2102 l_rt_end_dt_rl number;
2103 --
2104 l_per_in_ler_id number;
2105 l_pgm_id number;
2106 l_pl_id number;
2107 l_oipl_id number;
2108 l_lee_rsn_id number;
2109 l_enrt_perd_id number;
2110 l_person_id number;
2111 l_lf_evt_ocrd_dt date;
2112 --END
2113
2114 --
2115 cursor c_epe is
2116 select epe.enrt_cvg_strt_dt,
2117 epe.enrt_cvg_strt_dt_cd,
2118 epe.enrt_cvg_strt_dt_rl,
2119 pel.enrt_perd_strt_dt,
2120 epe.yr_perd_id,
2121 pel.acty_ref_perd_cd,
2122 --BUG 3191928
2123 epe.per_in_ler_id,
2124 epe.pgm_id,
2125 epe.pl_id,
2126 epe.oipl_id,
2127 pel.lee_rsn_id,
2128 pel.enrt_perd_id,
2129 pil.person_id,
2130 pil.lf_evt_ocrd_dt,
2131 --END BUG 3191928
2132 epe.pl_typ_id -- ICM
2133 from ben_elig_per_elctbl_chc epe,
2134 ben_pil_elctbl_chc_popl pel,
2135 ben_per_in_ler pil
2136 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2137 and epe.business_group_id = p_business_group_id
2138 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
2139 and pel.per_in_ler_id = pil.per_in_ler_id
2140 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
2141 --
2142 l_epe c_epe%rowtype;
2143 --
2144 cursor c_ecr is
2145 select ecr.rt_strt_dt,
2146 ecr.rt_strt_dt_cd,
2147 ecr.rt_strt_dt_rl,
2148 pel.enrt_perd_strt_dt,
2149 epe.elig_per_elctbl_chc_id,
2150 epe.yr_perd_id,
2151 pel.acty_ref_perd_cd,
2152 ecr.acty_base_rt_id,
2153 --BUG 3191928
2154 epe.per_in_ler_id,
2155 epe.pgm_id,
2156 epe.pl_id,
2157 epe.oipl_id,
2158 pel.lee_rsn_id,
2159 pel.enrt_perd_id,
2160 pil.person_id,
2161 pil.lf_evt_ocrd_dt
2162 --END BUG 3191928
2163 from ben_enrt_rt ecr,
2164 ben_enrt_bnft enb,
2165 ben_elig_per_elctbl_chc epe,
2166 ben_pil_elctbl_chc_popl pel,
2167 ben_per_in_ler pil
2168 where ecr.enrt_rt_id = p_enrt_rt_id
2169 and ecr.business_group_id = p_business_group_id
2170 and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
2171 enb.elig_per_elctbl_chc_id) =
2172 epe.elig_per_elctbl_chc_id
2173 and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
2174 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
2175 and pel.per_in_ler_id = pil.per_in_ler_id
2176 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
2177 --
2178 l_ecr c_ecr%rowtype;
2179 --
2180 -- Get the popl_yr_perd record for program or plan.
2181 -- If plan is part of a program, get the popl_yr_perd for program.
2182 -- If plan not in program, get the pol_yr_perd for the plan.
2183 --
2184 cursor c_yrp is
2185 select yrp.end_date end_date,
2186 yrp.start_date start_date
2187 from ben_yr_perd yrp
2188 where yrp.yr_perd_id = l_yr_perd_id
2189 and yrp.business_group_id = p_business_group_id;
2190 --
2191 l_yrp c_yrp%rowtype;
2192 --
2193 --BUG 3191928 fixes
2194 --
2195 CURSOR c_pl_popl_yr_period_current IS
2196 SELECT yp.end_date end_date,
2197 yp.start_date start_date
2198 FROM ben_popl_yr_perd pyp,
2199 ben_yr_perd yp
2200 WHERE pyp.pl_id = l_pl_id
2201 AND pyp.yr_perd_id = yp.yr_perd_id
2202 AND pyp.business_group_id = p_business_group_id
2203 AND l_start_date between yp.start_date AND yp.end_date
2204 AND yp.business_group_id = p_business_group_id ;
2205 --
2206 -- Parse Periods gets the information about the
2207 -- number of pay_periods by start date
2208 -- for the rate.
2209 -- 2164741
2210 --
2211 cursor c_parse_periods(v_payroll_id in number,
2212 v_start_date in date,
2213 v_end_date in date ) is
2214 select min(ptp.start_date)
2215 from per_time_periods ptp
2216 where ptp.payroll_id = v_payroll_id
2217 and ptp.start_date between
2218 v_start_date and v_end_date;
2219 --
2220
2221 cursor c_pgm_typ_cd(v_elig_per_elctbl_chc_id in number) is
2222 select pgm.pgm_typ_cd
2223 from ben_pgm_f pgm,
2224 ben_elig_per_elctbl_chc epe
2225 where epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
2226 and pgm.pgm_id = epe.pgm_id
2227 and p_effective_date between pgm.effective_start_date
2228 and pgm.effective_end_date;
2229 --
2230 --ICM Changes
2231 cursor c_opt_typ_cd(p_pl_typ_id number) is
2232 --
2233 select ptp.opt_typ_cd
2234 from ben_pl_typ_f ptp
2235 where ptp.pl_typ_id = p_pl_typ_id
2236 and p_effective_date between ptp.effective_start_date
2237 and ptp.effective_end_date;
2238 --
2239 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%TYPE;
2240 --
2241 --ICM Changes
2242 l_batch_param_rec benutils.g_batch_param_rec;
2243 l_year varchar2(30);
2244 l_pgm_typ_cd ben_pgm_f.pgm_typ_cd%type;
2245 --
2246 l_benmngle_parm_rec benutils.g_batch_param_rec;
2247 l_env_rec ben_env_object.g_global_env_rec_type;
2248 --
2249 begin
2250 --
2251 hr_utility.set_location('Entering '||l_package, 10);
2252 --
2253 g_debug := hr_utility.debug_enabled;
2254 --
2255 p_yr_start_date := p_start_date;
2256 --
2257 ben_env_object.get(p_rec => l_env_rec);
2258 benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
2259 ,p_rec => l_benmngle_parm_rec);
2260 --
2261 if p_start_date is null
2262 or p_end_date is null
2263 or p_acty_ref_perd_cd is null
2264 or p_complete_year_flag = 'Y'
2265 then
2266 --
2267 if p_enrt_rt_id is not null then
2268 --
2269 open c_ecr;
2270 fetch c_ecr into l_ecr;
2271 --
2272 if c_ecr%notfound then
2273 --
2274 close c_ecr;
2275 fnd_message.set_name('BEN','BEN_91825_ENRT_RT_NOT_FOUND');
2276 fnd_message.set_token('PROC',l_package);
2277 fnd_message.set_token('ENRT_RT_ID',to_char(p_enrt_rt_id));
2278 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
2279 fnd_message.raise_error;
2280 --
2281 else
2282 --
2283 close c_ecr;
2284 l_elig_per_elctbl_chc_id := l_ecr.elig_per_elctbl_chc_id;
2285 --
2286 end if;
2287 --
2288 l_elig_per_elctbl_chc_id := l_ecr.elig_per_elctbl_chc_id;
2289 l_acty_ref_perd_cd := l_ecr.acty_ref_perd_cd;
2290 l_start_date := l_ecr.rt_strt_dt;
2291 l_start_date_cd := l_ecr.rt_strt_dt_cd;
2292 l_start_date_rl := l_ecr.rt_strt_dt_rl;
2293 l_enrt_perd_start_dt := l_ecr.enrt_perd_strt_dt;
2294 l_yr_perd_id := l_ecr.yr_perd_id;
2295 l_acty_base_rt_id := l_ecr.acty_base_rt_id;
2296 --BUG 3191928
2297 l_per_in_ler_id := l_ecr.per_in_ler_id;
2298 l_pgm_id := l_ecr.pgm_id;
2299 l_pl_id := l_ecr.pl_id;
2300 l_oipl_id := l_ecr.oipl_id;
2301 l_lee_rsn_id := l_ecr.lee_rsn_id;
2302 l_enrt_perd_id := l_ecr.enrt_perd_id;
2303 l_person_id := l_ecr.person_id;
2304 l_lf_evt_ocrd_dt := l_ecr.lf_evt_ocrd_dt;
2305 --
2306 --
2307 -- Check if the epe context global is set
2308 --
2309 elsif ben_epe_cache.g_currepe_row.elig_per_elctbl_chc_id is not null
2310 then
2311 --
2312 l_elig_per_elctbl_chc_id := ben_epe_cache.g_currepe_row.elig_per_elctbl_chc_id;
2313 l_acty_ref_perd_cd := ben_epe_cache.g_currepe_row.acty_ref_perd_cd;
2314 l_start_date := ben_epe_cache.g_currepe_row.enrt_cvg_strt_dt;
2315 l_start_date_cd := ben_epe_cache.g_currepe_row.enrt_cvg_strt_dt_cd;
2316 l_start_date_rl := ben_epe_cache.g_currepe_row.enrt_cvg_strt_dt_rl;
2317 l_enrt_perd_start_dt := ben_epe_cache.g_currepe_row.enrt_perd_strt_dt;
2318 l_yr_perd_id := ben_epe_cache.g_currepe_row.yr_perd_id;
2319 --
2320 l_per_in_ler_id := ben_epe_cache.g_currepe_row.per_in_ler_id;
2321 l_pgm_id := ben_epe_cache.g_currepe_row.pgm_id;
2322 l_pl_id := ben_epe_cache.g_currepe_row.pl_id;
2323 l_oipl_id := ben_epe_cache.g_currepe_row.oipl_id;
2324 l_lee_rsn_id := ben_epe_cache.g_currepe_row.lee_rsn_id;
2325 l_enrt_perd_id := ben_epe_cache.g_currepe_row.enrt_perd_id;
2326 l_person_id := ben_epe_cache.g_currepe_row.person_id;
2327 l_lf_evt_ocrd_dt := ben_epe_cache.g_currepe_row.lf_evt_ocrd_dt;
2328 --
2329 elsif p_elig_per_elctbl_chc_id is not null then
2330 --
2331 open c_epe;
2332 fetch c_epe into l_epe;
2333 --
2334 if c_epe%notfound then
2335 --
2336 close c_epe;
2337 -- hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND ID:'||
2338 -- to_char(p_elig_per_elctbl_chc_id), 50);
2339 fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
2340 fnd_message.set_token('ID',to_char(p_elig_per_elctbl_chc_id));
2341 fnd_message.set_token('PROC',l_package);
2342 fnd_message.raise_error;
2343 --
2344 else
2345 --
2346 close c_epe;
2347 --
2348 end if;
2349 --
2350 l_acty_ref_perd_cd := l_epe.acty_ref_perd_cd;
2351 l_start_date := l_epe.enrt_cvg_strt_dt;
2352 l_start_date_cd := l_epe.enrt_cvg_strt_dt_cd;
2353 l_start_date_rl := l_epe.enrt_cvg_strt_dt_rl;
2354 l_enrt_perd_start_dt := l_epe.enrt_perd_strt_dt;
2355 l_yr_perd_id := l_epe.yr_perd_id;
2356 l_elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
2357 --
2358 l_per_in_ler_id := l_epe.per_in_ler_id;
2359 l_pgm_id := l_epe.pgm_id;
2360 l_pl_id := l_epe.pl_id;
2361 l_oipl_id := l_epe.oipl_id;
2362 l_lee_rsn_id := l_epe.lee_rsn_id;
2363 l_enrt_perd_id := l_epe.enrt_perd_id;
2364 l_person_id := l_epe.person_id;
2365 l_lf_evt_ocrd_dt := l_epe.lf_evt_ocrd_dt;
2366 --
2367 else
2368 --
2369 -- hr_utility.set_location('BEN_91884_CHC_N_RT_NOT_FOUND:', 50);
2370 fnd_message.set_name('BEN','BEN_91884_CHC_N_RT_NOT_FOUND');
2371 fnd_message.set_token('PROC',l_package);
2372 fnd_message.raise_error;
2373 --
2374 end if;
2375 --
2376 --end if;
2377 --
2378 --BUG 3191928 Determining the rate start date to make sure we are determining the
2379 --right year periods.
2380 --
2381 if l_start_date is null then
2382 --
2383 ben_determine_date.rate_and_coverage_dates
2384 (p_which_dates_cd => 'R'
2385 ,p_date_mandatory_flag => 'N'
2386 ,p_compute_dates_flag => 'Y'
2387 ,p_business_group_id => p_business_group_id
2388 ,P_PER_IN_LER_ID => l_per_in_ler_id
2389 ,P_PERSON_ID => l_person_id
2390 ,P_PGM_ID => l_pgm_id
2391 ,P_PL_ID => l_pl_id
2392 ,P_OIPL_ID => l_oipl_id
2393 ,P_LEE_RSN_ID => l_lee_rsn_id
2394 ,P_ENRT_PERD_ID => l_enrt_perd_id
2395 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
2396 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
2397 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
2398 ,p_rt_strt_dt => l_start_date -- l_rt_strt_dt
2399 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
2400 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
2401 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
2402 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
2403 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
2404 ,p_rt_end_dt => l_rt_end_dt
2405 ,p_rt_end_dt_cd => l_rt_end_dt_cd
2406 ,p_rt_end_dt_rl => l_rt_end_dt_rl
2407 ,p_effective_date => p_effective_date
2408 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2409 );
2410 --
2411 end if;
2412 --
2413 end if;
2414 --
2415 if p_end_date is null or
2416 p_complete_year_flag = 'Y' or
2417 p_yr_start_date is null then
2418 --
2419 open c_yrp;
2420 fetch c_yrp into l_yrp;
2421 --
2422 if c_yrp%notfound and l_benmngle_parm_rec.mode_cd <> 'D' then --ICM Changes
2423 --
2424 close c_yrp;
2425 -- hr_utility.set_location('BEN_91334_PLAN_YR_PERD', 50);
2426
2427 -- GRADE/STEP Do not throw error for GSP programs
2428 -- Use the Start and End Dates of the year in which
2429 -- the Effective Date falls
2430
2431 open c_pgm_typ_cd(l_elig_per_elctbl_chc_id);
2432 fetch c_pgm_typ_cd into l_pgm_typ_cd;
2433 if c_pgm_typ_cd%notfound then
2434 l_pgm_typ_cd := null;
2435 end if;
2436 close c_pgm_typ_cd;
2437 if l_pgm_typ_cd ='GSP' then
2438 --
2439 l_year := TO_CHAR(p_effective_date,'YYYY');
2440 l_yrp.start_date := TO_DATE('1/1/'||l_year,'MM/DD/YYYY');
2441 l_yrp.end_date := TO_DATE('12/31/'||l_year,'MM/DD/YYYY');
2442 --
2443 else
2444 --
2445 fnd_message.set_name('BEN','BEN_91334_PLAN_YR_PERD');
2446 fnd_message.set_token('PROC',l_package);
2447 fnd_message.raise_error;
2448 --
2449 end if;
2450 --
2451 end if;
2452 --BUG 3191928 fixes
2453 if l_yrp.end_date < l_start_date then
2454 hr_utility.set_location('strt dt is'||to_char(l_start_date),123);
2455 open c_pl_popl_yr_period_current;
2456 fetch c_pl_popl_yr_period_current into l_yrp;
2457 close c_pl_popl_yr_period_current;
2458 end if;
2459 --
2460 p_yr_start_date := l_yrp.start_date;
2461 p_end_date := l_yrp.end_date;
2462 --
2463 if p_complete_year_flag = 'Y' then
2464 --
2465 p_start_date := l_yrp.start_date;
2466 hr_utility.set_location('strt dt is'||to_char(p_start_date),123);
2467 --
2468 end if;
2469 --
2470 end if;
2471 --
2472 -- Bug 2164741 we are getting the first enrt_cvg_strt_dt from
2473 -- epe which is causing this problem.
2474 -- To avoid this if p_complete_year_flag is 'N' and
2475 -- the start_date is less that the p_yr_start_date then we derive the start_date
2476 if p_complete_year_flag = 'N' and nvl(p_start_date,l_start_date) < p_yr_start_date then
2477 --
2478 if g_debug then
2479 hr_utility.set_location(' p_complete_year_flag = N ',123);
2480 end if;
2481 if p_acty_ref_perd_cd = 'PP' then
2482 --
2483 open c_parse_periods(p_payroll_id,p_yr_start_date,p_end_date );
2484 fetch c_parse_periods into p_start_date ;
2485 close c_parse_periods ;
2486 --
2487 elsif p_acty_ref_perd_cd = 'MO' then
2488 --
2489 p_start_date := p_yr_start_date ;
2490 --
2491 end if;
2492 --
2493 end if;
2494 --
2495 hr_utility.set_location('IK RT STRT p_start_date'||p_start_date,111);
2496 if p_start_date is null then
2497 --
2498 if l_start_date is null then
2499 --
2500 -- Determine the start date using the codes and rules.
2501 --
2502 ben_determine_date.main(
2503 p_date_cd => l_start_date_cd,
2504 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
2505 p_business_group_id => p_business_group_id,
2506 p_effective_date => p_effective_date,
2507 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2508 p_formula_id => l_start_date_rl,
2509 p_returned_date => l_start_date,
2510 p_acty_base_rt_id => l_acty_base_rt_id,
2511 p_start_date => l_enrt_perd_start_dt);
2512 --
2513 end if;
2514 --
2515 p_start_date := l_start_date;
2516 --
2517 end if;
2518 --Bug 2151055
2519 hr_utility.set_location('IK RT STRT p_start_date'||p_start_date,112);
2520 --
2521 if p_start_date > p_end_date and p_yr_start_date < p_start_date then
2522 --
2523 p_start_date := p_yr_start_date ;
2524 --
2525 end if ;
2526 --
2527 --
2528 if p_acty_ref_perd_cd is null then
2529 --
2530 p_acty_ref_perd_cd := l_acty_ref_perd_cd;
2531 --
2532 end if;
2533 --
2534 -- hr_utility.set_location('Leaving '||l_package , 99);
2535 --
2536 exception
2537 --
2538 when others then
2539 if g_debug then
2540 hr_utility.set_location('WHEN OTHERS: '||l_package, 100);
2541 end if;
2542 p_start_date := l_start_date_nc_buffer; -- no copy changes
2543 p_end_date := l_end_date_nc_buffer; -- no copy changes
2544 p_yr_start_date := null; -- no copy changes
2545 fnd_message.raise_error;
2546 --
2547 end set_default_dates;
2548 --
2549 ---------------------------------------------------------------------------
2550 -- This function is used to convert the period amount to annual amount
2551 -- The annual period is computed as the period between the start date
2552 -- and end date. When the complete year flag is on, the start date and
2553 -- end date are overridden by plan year start and end date respectively.
2554 ---------------------------------------------------------------------------
2555 -- !!! THIS IS OVERLOADED - MAKE CHANGES IN BOTH THE FUNCTIONS !!!
2556 --------------------------------------------------------------------------
2557 function period_to_annual(p_amount in number,
2558 p_enrt_rt_id in number default null,
2559 p_elig_per_elctbl_chc_id in number default null,
2560 p_acty_ref_perd_cd in varchar2 default null,
2561 p_business_group_id in number default null,
2562 p_effective_date in date default null,
2563 p_lf_evt_ocrd_dt in date default null,
2564 p_complete_year_flag in varchar2 default 'N',
2565 p_use_balance_flag in varchar2 default 'N',
2566 p_start_date in date default null,
2567 p_end_date in date default null,
2568 p_payroll_id in number default null,
2569 p_element_type_id in number default null)
2570 return number
2571 is
2572 --
2573 l_hv pls_integer;
2574 --
2575 l_period_amt number := p_amount;
2576 l_balance_amt number := 0;
2577 l_annual_amt number := 0;
2578 l_start_date date := p_start_date;
2579 l_end_date date := p_end_date;
2580 l_yr_start_date date := null;
2581 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
2582 l_no_of_periods number := 1;
2583 l_package varchar2(80) := g_package || '.period_to_annual';
2584 l_cmplt_yr_flag varchar2(20) := p_complete_year_flag;
2585 --
2586 cursor c_cd is
2587 select
2588 abr.det_pl_ytd_cntrs_cd
2589 from ben_enrt_rt ecr,
2590 ben_acty_base_rt_f abr
2591 where ecr.enrt_rt_id = p_enrt_rt_id
2592 and ecr.business_group_id = p_business_group_id
2593 and ecr.acty_base_rt_id = abr.acty_base_rt_id
2594 and p_effective_date between
2595 abr.effective_start_date and abr.effective_end_date;
2596
2597 --Added for Bug 6913654
2598 cursor c_get_cmmd_amt is
2599 select cmcd_val
2600 from ben_enrt_rt ecr
2601 where ecr.enrt_rt_id = p_enrt_rt_id
2602 and ecr.business_group_id = p_business_group_id;
2603
2604 l_cd ben_acty_base_rt_f.det_pl_ytd_cntrs_cd%TYPE := null;
2605 l_call_balance boolean;
2606 --End of code for Bug 6913654
2607
2608 begin
2609 --
2610 -- hr_utility.set_location('Entering '||l_package, 10);
2611 --
2612 -- Check for a null amount. Assumption is that a calculation
2613 -- cannot be performed on a null amount.
2614 --
2615 if p_amount is null then
2616 --
2617 return null;
2618 --
2619 end if;
2620 --
2621 if g_period_to_annual_cached > 0 then
2622 --
2623 begin
2624 --
2625 l_hv := mod(nvl(p_amount,1)
2626 +nvl(p_enrt_rt_id,2)
2627 +nvl(p_elig_per_elctbl_chc_id,3)
2628 +nvl(p_start_date-hr_api.g_sot,4)
2629 +nvl(p_end_date-hr_api.g_sot,5)
2630 +nvl(p_payroll_id,6)
2631 +nvl(p_element_type_id,7)
2632 ,ben_hash_utility.get_hash_key);
2633 --
2634 if nvl(g_period_to_annual_cache(l_hv).amount,-1) = nvl(p_amount,-1)
2635 and nvl(g_period_to_annual_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
2636 and nvl(g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
2637 and nvl(g_period_to_annual_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
2638 and nvl(g_period_to_annual_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
2639 and nvl(g_period_to_annual_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
2640 and nvl(g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
2641 and nvl(g_period_to_annual_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
2642 and nvl(g_period_to_annual_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
2643 and nvl(g_period_to_annual_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
2644 and nvl(g_period_to_annual_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
2645 and nvl(g_period_to_annual_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
2646 and nvl(g_period_to_annual_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
2647 then
2648 --
2649 null;
2650 --
2651 else
2652 --
2653 l_hv := l_hv+g_hash_jump;
2654 --
2655 loop
2656 --
2657 if nvl(g_period_to_annual_cache(l_hv).amount,-1) = nvl(p_amount,-1)
2658 and nvl(g_period_to_annual_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
2659 and nvl(g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
2660 and nvl(g_period_to_annual_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
2661 and nvl(g_period_to_annual_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
2662 and nvl(g_period_to_annual_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
2663 and nvl(g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
2664 and nvl(g_period_to_annual_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
2665 and nvl(g_period_to_annual_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
2666 and nvl(g_period_to_annual_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
2667 and nvl(g_period_to_annual_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
2668 and nvl(g_period_to_annual_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
2669 and nvl(g_period_to_annual_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
2670 then
2671 --
2672 exit;
2673 --
2674 else
2675 --
2676 l_hv := l_hv+g_hash_jump;
2677 --
2678 end if;
2679 --
2680 end loop;
2681 --
2682 end if;
2683 --
2684 exception
2685 when no_data_found then
2686 --
2687 l_hv := null;
2688 end;
2689 --
2690 if l_hv is not null then
2691 --
2692 return g_period_to_annual_cache(l_hv).annual_amt;
2693 --
2694 end if;
2695 --
2696 end if;
2697 --
2698 if p_use_balance_flag = 'Y' then
2699 l_call_balance := true;
2700 if p_enrt_rt_id is not null then
2701 open c_cd;
2702 fetch c_cd into l_cd;
2703 close c_cd;
2704 if l_cd is null then
2705 l_call_balance := false;
2706 else
2707 l_cmplt_yr_flag := 'N';
2708 end if;
2709 end if;
2710 end if;
2711 --
2712 set_default_dates(p_enrt_rt_id => p_enrt_rt_id,
2713 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2714 p_business_group_id => p_business_group_id,
2715 p_complete_year_flag => l_cmplt_yr_flag,
2716 p_effective_date => p_effective_date,
2717 p_payroll_id => p_payroll_id,
2718 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2719 p_start_date => l_start_date,
2720 p_end_date => l_end_date,
2721 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
2722 p_yr_start_date => l_yr_start_date);
2723 --
2724 -- Compute balances only if use_balance_flag is ON.
2725 --
2726 if l_call_balance then
2727 --
2728 l_balance_amt := get_balance
2729 (p_enrt_rt_id => p_enrt_rt_id,
2730 p_payroll_id => p_payroll_id,
2731 p_business_group_id => p_business_group_id,
2732 p_effective_date => p_effective_date,
2733 p_start_date => l_yr_start_date);
2734 --
2735 end if;
2736 --
2737 if l_acty_ref_perd_cd = 'PYR' then
2738 --
2739 -- If Annually, don't need to get number of periods.
2740 --
2741 l_no_of_periods := 1;
2742 --
2743 else
2744 --
2745 --Bug 6913654, for estimate only rates no of periods should be calculated based on the remaining
2746 --Per Pay periods. Get the communicated amount and multiply it with the periods obtained to get the
2747 --amount to be paid for the remaining periods.
2748 if l_cd = 'ESTONLY' then
2749 l_no_of_periods := get_periods_between
2750 (p_acty_ref_perd_cd => 'PP',
2751 p_start_date => l_start_date,
2752 p_end_date => l_end_date,
2753 p_payroll_id => p_payroll_id,
2754 p_business_group_id => p_business_group_id,
2755 p_enrt_rt_id => p_enrt_rt_id,
2756 p_element_type_id => p_element_type_id,
2757 p_effective_date => p_effective_date
2758 );
2759 --hr_utility.set_location('Inside period ESTONLY periods: '||l_no_of_periods, 9999);
2760 --hr_utility.set_location('Inside period ESTONLY enrt_id: '||p_enrt_rt_id, 9999);
2761 open c_get_cmmd_amt ;
2762 fetch c_get_cmmd_amt into l_period_amt;
2763 close c_get_cmmd_amt;
2764 --hr_utility.set_location('Inside period ESTONLY amt: '||l_period_amt, 9999);
2765 else
2766 l_no_of_periods := get_periods_between
2767 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
2768 p_start_date => l_start_date,
2769 p_end_date => l_end_date,
2770 p_payroll_id => p_payroll_id,
2771 p_business_group_id => p_business_group_id,
2772 p_enrt_rt_id => p_enrt_rt_id,
2773 p_element_type_id => p_element_type_id,
2774 p_effective_date => p_effective_date
2775 );
2776 end if;
2777 --
2778 end if;
2779 --
2780 l_annual_amt := (l_period_amt * l_no_of_periods) + l_balance_amt;
2781 -- hr_utility.set_location('perd amt '||to_char(l_period_amt), 11);
2782 -- hr_utility.set_location('perd no '||to_char(l_no_of_periods), 11);
2783 -- hr_utility.set_location('bal amt '||to_char(l_balance_amt), 11);
2784 --
2785 l_annual_amt := round(l_annual_amt,2);
2786 --
2787 if g_period_to_annual_cached > 0 then
2788 --
2789 -- Only store the
2790 --
2791 l_hv := mod(nvl(p_amount,1)
2792 +nvl(p_enrt_rt_id,2)
2793 +nvl(p_elig_per_elctbl_chc_id,3)
2794 +nvl(p_start_date-hr_api.g_sot,4)
2795 +nvl(p_end_date-hr_api.g_sot,5)
2796 +nvl(p_payroll_id,6)
2797 +nvl(p_element_type_id,7)
2798 ,ben_hash_utility.get_hash_key);
2799 --
2800 while g_period_to_annual_cache.exists(l_hv)
2801 loop
2802 --
2803 l_hv := l_hv+g_hash_jump;
2804 --
2805 end loop;
2806 --
2807 g_period_to_annual_cache(l_hv).amount := p_amount;
2808 g_period_to_annual_cache(l_hv).enrt_rt_id := p_enrt_rt_id;
2809 g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
2810 g_period_to_annual_cache(l_hv).acty_ref_perd_cd := p_acty_ref_perd_cd;
2811 g_period_to_annual_cache(l_hv).business_group_id := p_business_group_id;
2812 g_period_to_annual_cache(l_hv).effective_date := p_effective_date;
2813 g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
2814 g_period_to_annual_cache(l_hv).complete_year_flag := p_complete_year_flag;
2815 g_period_to_annual_cache(l_hv).use_balance_flag := p_use_balance_flag;
2816 g_period_to_annual_cache(l_hv).start_date := p_start_date;
2817 g_period_to_annual_cache(l_hv).end_date := p_end_date;
2818 g_period_to_annual_cache(l_hv).payroll_id := p_payroll_id;
2819 g_period_to_annual_cache(l_hv).element_type_id := p_element_type_id;
2820 g_period_to_annual_cache(l_hv).annual_amt := l_annual_amt;
2821 --
2822 end if;
2823 --
2824 -- hr_utility.set_location('Leaving '||l_package , 90);
2825 --
2826 return(l_annual_amt);
2827 --
2828 exception
2829 --
2830 when others then
2831 fnd_message.raise_error;
2832 --
2833 end period_to_annual;
2834
2835 --
2836 ---------------------------------------------------------------------------
2837 -- This function is used to convert the period amount to annual amount
2838 -- The annual period is computed as the period between the start date
2839 -- and end date. When the complete year flag is on, the start date and
2840 -- end date are overridden by plan year start and end date respectively.
2841 ---------------------------------------------------------------------------
2842 -- !!! THIS IS OVERLOADED - MAKE CHANGES IN BOTH THE FUNCTIONS !!!
2843 ---------------------------------------------------------------------------
2844 function period_to_annual(p_amount in number,
2845 p_enrt_rt_id in number default null,
2846 p_elig_per_elctbl_chc_id in number default null,
2847 p_acty_ref_perd_cd in varchar2 default null,
2848 p_business_group_id in number default null,
2849 p_effective_date in date default null,
2850 p_lf_evt_ocrd_dt in date default null,
2851 p_complete_year_flag in varchar2 default 'N',
2852 p_use_balance_flag in varchar2 default 'N',
2853 p_start_date in date default null,
2854 p_end_date in date default null,
2855 p_payroll_id in number default null,
2856 p_element_type_id in number default null,
2857 p_rounding_flag in varchar2 )
2858 return number
2859 is
2860 --
2861 l_hv pls_integer;
2862 --
2863 l_period_amt number := p_amount;
2864 l_balance_amt number := 0;
2865 l_annual_amt number := 0;
2866 l_start_date date := p_start_date;
2867 l_end_date date := p_end_date;
2868 l_yr_start_date date := null;
2869 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
2870 l_no_of_periods number := 1;
2871 l_package varchar2(80) := g_package || '.period_to_annual';
2872 l_cmplt_yr_flag varchar2(20) := p_complete_year_flag;
2873 --
2874 cursor c_cd is
2875 select
2876 abr.det_pl_ytd_cntrs_cd
2877 from ben_enrt_rt ecr,
2878 ben_acty_base_rt_f abr
2879 where ecr.enrt_rt_id = p_enrt_rt_id
2880 and ecr.business_group_id = p_business_group_id
2881 and ecr.acty_base_rt_id = abr.acty_base_rt_id
2882 and p_effective_date between
2883 abr.effective_start_date and abr.effective_end_date;
2884
2885 cursor c_get_cmmd_amt is
2886 select cmcd_val
2887 from ben_enrt_rt ecr
2888 where ecr.enrt_rt_id = p_enrt_rt_id
2889 and ecr.business_group_id = p_business_group_id;
2890
2891 l_cd ben_acty_base_rt_f.det_pl_ytd_cntrs_cd%TYPE := null;
2892 l_call_balance boolean;
2893 begin
2894 --
2895 -- hr_utility.set_location('Entering '||l_package, 10);
2896 --
2897 -- Check for a null amount. Assumption is that a calculation
2898 -- cannot be performed on a null amount.
2899 --
2900 if p_amount is null then
2901 --
2902 return null;
2903 --
2904 end if;
2905 --
2906 if g_period_to_annual_cached > 0 then
2907 --
2908 begin
2909 --
2910 l_hv := mod(nvl(p_amount,1)
2911 +nvl(p_enrt_rt_id,2)
2912 +nvl(p_elig_per_elctbl_chc_id,3)
2913 +nvl(p_start_date-hr_api.g_sot,4)
2914 +nvl(p_end_date-hr_api.g_sot,5)
2915 +nvl(p_payroll_id,6)
2916 +nvl(p_element_type_id,7)
2917 ,ben_hash_utility.get_hash_key);
2918 --
2919 if nvl(g_period_to_annual_cache(l_hv).amount,-1) = nvl(p_amount,-1)
2920 and nvl(g_period_to_annual_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
2921 and nvl(g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
2922 and nvl(g_period_to_annual_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
2923 and nvl(g_period_to_annual_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
2924 and nvl(g_period_to_annual_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
2925 and nvl(g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
2926 and nvl(g_period_to_annual_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
2927 and nvl(g_period_to_annual_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
2928 and nvl(g_period_to_annual_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
2929 and nvl(g_period_to_annual_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
2930 and nvl(g_period_to_annual_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
2931 and nvl(g_period_to_annual_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
2932 then
2933 --
2934 null;
2935 --
2936 else
2937 --
2938 l_hv := l_hv+g_hash_jump;
2939 --
2940 loop
2941 --
2942 if nvl(g_period_to_annual_cache(l_hv).amount,-1) = nvl(p_amount,-1)
2943 and nvl(g_period_to_annual_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
2944 and nvl(g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
2945 and nvl(g_period_to_annual_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
2946 and nvl(g_period_to_annual_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
2947 and nvl(g_period_to_annual_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
2948 and nvl(g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
2949 and nvl(g_period_to_annual_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
2950 and nvl(g_period_to_annual_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
2951 and nvl(g_period_to_annual_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
2952 and nvl(g_period_to_annual_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
2953 and nvl(g_period_to_annual_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
2954 and nvl(g_period_to_annual_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
2955 then
2956 --
2957 exit;
2958 --
2959 else
2960 --
2961 l_hv := l_hv+g_hash_jump;
2962 --
2963 end if;
2964 --
2965 end loop;
2966 --
2967 end if;
2968 --
2969 exception
2970 when no_data_found then
2971 --
2972 l_hv := null;
2973 end;
2974 --
2975 if l_hv is not null then
2976 --
2977 return g_period_to_annual_cache(l_hv).annual_amt;
2978 --
2979 end if;
2980 --
2981 end if;
2982 --
2983 if p_use_balance_flag = 'Y' then
2984 l_call_balance := true;
2985 if p_enrt_rt_id is not null then
2986 open c_cd;
2987 fetch c_cd into l_cd;
2988 close c_cd;
2989 if l_cd is null then
2990 l_call_balance := false;
2991 else
2992 l_cmplt_yr_flag := 'N';
2993 end if;
2994 end if;
2995 end if;
2996 --
2997 set_default_dates(p_enrt_rt_id => p_enrt_rt_id,
2998 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2999 p_business_group_id => p_business_group_id,
3000 p_complete_year_flag => l_cmplt_yr_flag,
3001 p_effective_date => p_effective_date,
3002 p_payroll_id => p_payroll_id,
3003 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3004 p_start_date => l_start_date,
3005 p_end_date => l_end_date,
3006 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3007 p_yr_start_date => l_yr_start_date);
3008 --
3009 -- Compute balances only if use_balance_flag is ON.
3010 --
3011 if l_call_balance then
3012 --
3013 l_balance_amt := get_balance
3014 (p_enrt_rt_id => p_enrt_rt_id,
3015 p_payroll_id => p_payroll_id,
3016 p_business_group_id => p_business_group_id,
3017 p_effective_date => p_effective_date,
3018 p_start_date => l_yr_start_date);
3019 --
3020 end if;
3021 --
3022 if l_acty_ref_perd_cd = 'PYR' then
3023 --
3024 -- If Annually, don't need to get number of periods.
3025 --
3026 l_no_of_periods := 1;
3027 --
3028 else
3029 --
3030 --Bug 6913654, for estimate only rates no of periods should be calculated based on the remaining
3031 --Per Pay periods. Get the communicated amount and multiply it with the periods obtained to get the
3032 --amount to be paid for the remaining periods.
3033 if l_cd = 'ESTONLY' then
3034 l_no_of_periods := get_periods_between
3035 (p_acty_ref_perd_cd => 'PP',
3036 p_start_date => l_start_date,
3037 p_end_date => l_end_date,
3038 p_payroll_id => p_payroll_id,
3039 p_business_group_id => p_business_group_id,
3040 p_enrt_rt_id => p_enrt_rt_id,
3041 p_element_type_id => p_element_type_id,
3042 p_effective_date => p_effective_date
3043 );
3044 --hr_utility.set_location('Inside period ETONLY periods: '||l_no_of_periods, 9999);
3045 --hr_utility.set_location('Inside period ETONLY enrt_id: '||p_enrt_rt_id, 9999);
3046 open c_get_cmmd_amt ;
3047 fetch c_get_cmmd_amt into l_period_amt;
3048 close c_get_cmmd_amt;
3049 --hr_utility.set_location('Inside period ETONLY amt: '||l_period_amt, 9999);
3050 else
3051 l_no_of_periods := get_periods_between
3052 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3053 p_start_date => l_start_date,
3054 p_end_date => l_end_date,
3055 p_payroll_id => p_payroll_id,
3056 p_business_group_id => p_business_group_id,
3057 p_enrt_rt_id => p_enrt_rt_id,
3058 p_element_type_id => p_element_type_id,
3059 p_effective_date => p_effective_date
3060 );
3061 end if;
3062 --
3063 end if;
3064 --
3065 l_annual_amt := (l_period_amt * l_no_of_periods) + l_balance_amt;
3066 -- hr_utility.set_location('perd amt '||to_char(l_period_amt), 11);
3067 -- hr_utility.set_location('perd no '||to_char(l_no_of_periods), 11);
3068 -- hr_utility.set_location('bal amt '||to_char(l_balance_amt), 11);
3069 --
3070 if p_rounding_flag = 'Y' then
3071 --
3072 l_annual_amt := round(l_annual_amt,2);
3073 --
3074 end if;
3075 --
3076 if g_period_to_annual_cached > 0 then
3077 --
3078 -- Only store the
3079 --
3080 l_hv := mod(nvl(p_amount,1)
3081 +nvl(p_enrt_rt_id,2)
3082 +nvl(p_elig_per_elctbl_chc_id,3)
3083 +nvl(p_start_date-hr_api.g_sot,4)
3084 +nvl(p_end_date-hr_api.g_sot,5)
3085 +nvl(p_payroll_id,6)
3086 +nvl(p_element_type_id,7)
3087 ,ben_hash_utility.get_hash_key);
3088 --
3089 while g_period_to_annual_cache.exists(l_hv)
3090 loop
3091 --
3092 l_hv := l_hv+g_hash_jump;
3093 --
3094 end loop;
3095 --
3096 g_period_to_annual_cache(l_hv).amount := p_amount;
3097 g_period_to_annual_cache(l_hv).enrt_rt_id := p_enrt_rt_id;
3098 g_period_to_annual_cache(l_hv).elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
3099 g_period_to_annual_cache(l_hv).acty_ref_perd_cd := p_acty_ref_perd_cd;
3100 g_period_to_annual_cache(l_hv).business_group_id := p_business_group_id;
3101 g_period_to_annual_cache(l_hv).effective_date := p_effective_date;
3102 g_period_to_annual_cache(l_hv).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
3103 g_period_to_annual_cache(l_hv).complete_year_flag := p_complete_year_flag;
3104 g_period_to_annual_cache(l_hv).use_balance_flag := p_use_balance_flag;
3105 g_period_to_annual_cache(l_hv).start_date := p_start_date;
3106 g_period_to_annual_cache(l_hv).end_date := p_end_date;
3107 g_period_to_annual_cache(l_hv).payroll_id := p_payroll_id;
3108 g_period_to_annual_cache(l_hv).element_type_id := p_element_type_id;
3109 g_period_to_annual_cache(l_hv).annual_amt := l_annual_amt;
3110 --
3111 end if;
3112 --
3113 -- hr_utility.set_location('Leaving '||l_package , 90);
3114 --
3115 return(l_annual_amt);
3116 --
3117 exception
3118 --
3119 when others then
3120 fnd_message.raise_error;
3121 --
3122 end period_to_annual;
3123
3124 --
3125
3126 --
3127 ---------------------------------------------------------------------------
3128 -- This function is used to convert the annual amount to period amount
3129 -- The annual period is computed as the period between the start date
3130 -- and end date. When the complete year flag is on, the start date and
3131 -- end date are overridden by plan year start and end date respectively.
3132 ---------------------------------------------------------------------------
3133 -- !!! THIS IS OVERLOADED - MAKE CHANGES IN BOTH THE FUNCTIONS !!!
3134 ---------------------------------------------------------------------------
3135 function annual_to_period(p_amount in number,
3136 p_enrt_rt_id in number default null,
3137 p_elig_per_elctbl_chc_id in number default null,
3138 p_acty_ref_perd_cd in varchar2 default null,
3139 p_business_group_id in number default null,
3140 p_effective_date in date default null,
3141 p_lf_evt_ocrd_dt in date default null,
3142 p_complete_year_flag in varchar2 default 'N',
3143 p_use_balance_flag in varchar2 default 'N',
3144 p_start_date in date default null,
3145 p_end_date in date default null,
3146 p_payroll_id in number default null,
3147 p_element_type_id in number default null,
3148 p_annual_target in boolean default false,
3149 p_person_id in number default null)
3150 return number is
3151 --
3152 l_hv pls_integer;
3153 --
3154 l_period_amt number := 0;
3155 l_balance_amt number := 0;
3156 l_annual_amt number := p_amount;
3157 l_no_of_periods number := 1;
3158 l_start_date date := p_start_date;
3159 l_end_date date := p_end_date;
3160 l_yr_start_date date := null;
3161 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
3162 l_package varchar2(80) := g_package || '.annual_to_period';
3163 l_cmplt_yr_flag varchar2(20) := p_complete_year_flag;
3164 --
3165 cursor c_cd is
3166 select
3167 abr.det_pl_ytd_cntrs_cd,
3168 abr.entr_ann_val_flag,
3169 abr.rt_mlt_cd
3170 from ben_enrt_rt ecr,
3171 ben_acty_base_rt_f abr
3172 where ecr.enrt_rt_id = p_enrt_rt_id
3173 and ecr.business_group_id = p_business_group_id
3174 and ecr.acty_base_rt_id = abr.acty_base_rt_id
3175 and p_effective_date between
3176 abr.effective_start_date and abr.effective_end_date;
3177 --
3178 l_prnt_ann_rt varchar2(1):= 'N';
3179 --
3180 cursor c_payroll (p_payroll_id number,
3181 p_effective_date date) is
3182 select pay_date_offset
3183 from pay_all_payrolls_f prl
3184 where prl.payroll_id = p_payroll_id
3185 and p_effective_date between prl.effective_start_date
3186 and prl.effective_end_date;
3187 --
3188 cursor c_first_payroll (p_person_id number,
3189 p_effective_date date) is
3190 select payroll_id
3191 from per_all_assignments_f ass
3192 where ass.person_id = p_person_id
3193 and ass.primary_flag = 'Y'
3194 and ass.assignment_type <> 'C'
3195 and p_effective_date between ass.effective_start_date
3196 and ass.effective_end_date
3197 order by decode(ass.assignment_type, 'E',1,'B',2,3);
3198
3199
3200
3201
3202 l_cd ben_acty_base_rt_f.det_pl_ytd_cntrs_cd%TYPE := null;
3203 l_entr_ann_val_flag varchar2(30);
3204 l_rt_mlt_cd varchar2(30);
3205 l_call_balance boolean;
3206 l_pay_date_offset number;
3207 l_first_payroll_id number;
3208 l_first_pay_date_offset number;
3209 l_start_date_check date;
3210 --
3211 begin
3212 --
3213 if g_debug then
3214 hr_utility.set_location('Entering '||l_package, 10);
3215 end if;
3216 hr_utility.set_location('Annual Amount in Annual to Period '||p_amount, 10);
3217 --
3218 -- Check for a null amount. Assumption is that a calculation
3219 -- cannot be performed on a null amount.
3220 --
3221 if p_amount is null then
3222 --
3223 return null;
3224 --
3225 end if;
3226 --
3227 open c_payroll (p_payroll_id, p_effective_date);
3228 fetch c_payroll into l_pay_date_offset;
3229 close c_payroll;
3230 --
3231 if g_annual_to_period_cached > 0 then
3232 --
3233 begin
3234 --
3235 l_hv := mod(nvl(p_amount,1)
3236 +nvl(p_enrt_rt_id,2)
3237 +nvl(p_elig_per_elctbl_chc_id,3)
3238 +nvl(p_start_date-hr_api.g_sot,4)
3239 +nvl(p_end_date-hr_api.g_sot,5)
3240 +nvl(p_payroll_id,6)
3241 +nvl(p_element_type_id,7)
3242 ,ben_hash_utility.get_hash_key);
3243 --
3244 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3245 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3246 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3247 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3248 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3249 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3250 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3251 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3252 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3253 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3254 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3255 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3256 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3257 then
3258 --
3259 null;
3260 --
3261 else
3262 --
3263 l_hv := l_hv+g_hash_jump;
3264 --
3265 loop
3266 --
3267 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3268 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3269 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3270 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3271 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3272 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3273 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3274 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3275 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3276 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3277 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3278 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3279 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3280 then
3281 --
3282 exit;
3283 --
3284 else
3285 --
3286 l_hv := l_hv+g_hash_jump;
3287 --
3288 end if;
3289 --
3290 end loop;
3291 --
3292 end if;
3293 --
3294 exception
3295 when no_data_found then
3296 --
3297 l_hv := null;
3298 end;
3299 --
3300 if l_hv is not null then
3301 --
3302 return g_annual_to_period_cache(l_hv).period_amt;
3303 --
3304 end if;
3305 --
3306 end if;
3307 --
3308 if p_use_balance_flag = 'Y' then
3309 l_call_balance := true;
3310 if p_enrt_rt_id is not null then
3311 open c_cd;
3312 fetch c_cd into l_cd, l_entr_ann_val_flag,l_rt_mlt_cd;
3313 close c_cd;
3314 if l_cd is null then
3315 if l_rt_mlt_cd = 'PRNT' then
3316 l_cmplt_yr_flag := 'N';
3317 l_prnt_ann_rt := 'Y';
3318 else
3319 l_call_balance := false;
3320 end if;
3321 -- l_call_balance := false;
3322 else
3323 l_cmplt_yr_flag := 'N';
3324 end if;
3325 end if;
3326 end if;
3327 --
3328 set_default_dates(p_enrt_rt_id => p_enrt_rt_id,
3329 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3330 p_business_group_id => p_business_group_id,
3331 p_complete_year_flag => l_cmplt_yr_flag,
3332 p_effective_date => p_effective_date,
3333 p_payroll_id => p_payroll_id,
3334 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3335 p_start_date => l_start_date,
3336 p_end_date => l_end_date,
3337 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3338 p_yr_start_date => l_yr_start_date);
3339 --
3340 -- Compute balances only if use_balance_flag is ON.
3341 --
3342 -- bug#3510633
3343 if (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3344 p_annual_target or l_prnt_ann_rt = 'Y') and
3345 p_person_id is not null then
3346 --
3347 open c_first_payroll (p_person_id, (l_yr_start_date - 1));
3348 fetch c_first_payroll into l_first_payroll_id;
3349 close c_first_payroll;
3350 --
3351 if l_first_payroll_id is not null then
3352 --
3353 open c_payroll(l_first_payroll_id, (l_yr_start_date - 1));
3354 fetch c_payroll into l_first_pay_date_offset;
3355 close c_payroll;
3356 --
3357 end if;
3358 --
3359 end if;
3360
3361 -- 5642552 : Evem of check_offset is 0, start annual-rates as of the period start.
3362 -- l_first_pay_date_offset <> 0 and
3363 l_start_date_check := l_start_date;
3364 --
3365 if (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3366 p_annual_target or l_prnt_ann_rt = 'Y') then
3367 --
3368 if p_start_date is not null and p_start_date < l_yr_start_date then
3369 --
3370 l_start_date_check := p_start_date;
3371 --
3372 else
3373 l_yr_start_date := l_yr_start_date - NVL(l_first_pay_date_offset,0); -- 5642552: Added NVL.
3374 l_start_date_check := l_start_date;
3375 end if;
3376 if g_debug then
3377 hr_utility.set_location('Pay Date Offset'||l_first_pay_date_offset,11);
3378 end if;
3379 end if;
3380 --
3381
3382 if l_call_balance then
3383 --
3384 l_balance_amt := get_balance
3385 (p_enrt_rt_id => p_enrt_rt_id,
3386 p_payroll_id => p_payroll_id,
3387 p_business_group_id => p_business_group_id,
3388 p_effective_date => p_effective_date,
3389 p_start_date => l_yr_start_date);
3390 --
3391 end if;
3392 --
3393 if l_acty_ref_perd_cd = 'PYR' then
3394 --
3395 -- If Annualy, don't need to get number of periods.
3396 --
3397 l_no_of_periods := 1;
3398 --
3399 else
3400 if l_pay_date_offset <> 0 and (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3401 p_annual_target or l_prnt_ann_rt = 'Y') then
3402 l_no_of_periods := get_periods_between
3403 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3404 p_start_date => nvl(l_start_date_check,l_start_date),
3405 p_end_date => l_end_date,
3406 p_payroll_id => p_payroll_id,
3407 p_business_group_id => p_business_group_id,
3408 p_enrt_rt_id => p_enrt_rt_id,
3409 p_element_type_id => p_element_type_id,
3410 p_effective_date => p_effective_date,
3411 p_use_check_date => true
3412 );
3413
3414 --
3415 else
3416 --
3417 l_no_of_periods := get_periods_between
3418 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3419 p_start_date => l_start_date_check, -- 5642552: Replaced l_start_date.
3420 p_end_date => l_end_date,
3421 p_payroll_id => p_payroll_id,
3422 p_business_group_id => p_business_group_id,
3423 p_enrt_rt_id => p_enrt_rt_id,
3424 p_element_type_id => p_element_type_id,
3425 p_effective_date => p_effective_date
3426 );
3427 --
3428 end if;
3429 --
3430 end if;
3431 --
3432 l_period_amt := (l_annual_amt - l_balance_amt)/l_no_of_periods;
3433 hr_utility.set_location('Annual to Period Amount '||l_period_amt , 90);
3434 --
3435 l_period_amt := round(l_period_amt,2);
3436 --
3437 if g_annual_to_period_cached > 0 then
3438 --
3439 -- Only store the
3440 --
3441 l_hv := mod(nvl(p_amount,1)
3442 +nvl(p_enrt_rt_id,2)
3443 +nvl(p_elig_per_elctbl_chc_id,3)
3444 +nvl(p_start_date-hr_api.g_sot,4)
3445 +nvl(p_end_date-hr_api.g_sot,5)
3446 +nvl(p_payroll_id,6)
3447 +nvl(p_element_type_id,7)
3448 ,ben_hash_utility.get_hash_key);
3449 --
3450 while g_annual_to_period_cache.exists(l_hv)
3451 loop
3452 --
3453 l_hv := l_hv+g_hash_jump;
3454 --
3455 end loop;
3456 --
3457 g_annual_to_period_cache(l_hv).amount := p_amount;
3458 g_annual_to_period_cache(l_hv).enrt_rt_id := p_enrt_rt_id;
3459 g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
3460 g_annual_to_period_cache(l_hv).acty_ref_perd_cd := p_acty_ref_perd_cd;
3461 g_annual_to_period_cache(l_hv).business_group_id := p_business_group_id;
3462 g_annual_to_period_cache(l_hv).effective_date := p_effective_date;
3463 g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
3464 g_annual_to_period_cache(l_hv).complete_year_flag := p_complete_year_flag;
3465 g_annual_to_period_cache(l_hv).use_balance_flag := p_use_balance_flag;
3466 g_annual_to_period_cache(l_hv).start_date := p_start_date;
3467 g_annual_to_period_cache(l_hv).end_date := p_end_date;
3468 g_annual_to_period_cache(l_hv).payroll_id := p_payroll_id;
3469 g_annual_to_period_cache(l_hv).element_type_id := p_element_type_id;
3470 g_annual_to_period_cache(l_hv).period_amt := l_period_amt;
3471 g_annual_to_period_cache(l_hv).pp_in_yr_used_num := l_no_of_periods;
3472 --
3473 end if;
3474 --
3475 hr_utility.set_location('Leaving '||l_package , 90);
3476 --
3477 return(l_period_amt);
3478 --
3479 exception
3480 --
3481 when others then
3482 fnd_message.raise_error;
3483 --
3484 end annual_to_period;
3485 --
3486 ---------------------------------------------------------------------------
3487 -- This function is used to convert the annual amount to period amount
3488 -- The annual period is computed as the period between the start date
3489 -- and end date. When the complete year flag is on, the start date and
3490 -- end date are overridden by plan year start and end date respectively.
3491 ---------------------------------------------------------------------------
3492 -- !!! THIS IS OVERLOADED - MAKE CHANGES IN BOTH THE FUNCTIONS !!!
3493 ---------------------------------------------------------------------------
3494 function annual_to_period(p_amount in number,
3495 p_enrt_rt_id in number default null,
3496 p_elig_per_elctbl_chc_id in number default null,
3497 p_acty_ref_perd_cd in varchar2 default null,
3498 p_business_group_id in number default null,
3499 p_effective_date in date default null,
3500 p_lf_evt_ocrd_dt in date default null,
3501 p_complete_year_flag in varchar2 default 'N',
3502 p_use_balance_flag in varchar2 default 'N',
3503 p_start_date in date default null,
3504 p_end_date in date default null,
3505 p_payroll_id in number default null,
3506 p_element_type_id in number default null,
3507 p_annual_target in boolean default false,
3508 p_rounding_flag in varchar2,
3509 p_person_id in number default null )
3510 return number is
3511 --
3512 l_hv pls_integer;
3513 --
3514 l_period_amt number := 0;
3515 l_balance_amt number := 0;
3516 l_annual_amt number := p_amount;
3517 l_no_of_periods number := 1;
3518 l_start_date date := p_start_date;
3519 l_end_date date := p_end_date;
3520 l_yr_start_date date := null;
3521 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
3522 l_package varchar2(80) := g_package || '.annual_to_period';
3523 l_cmplt_yr_flag varchar2(20) := p_complete_year_flag;
3524 --
3525 cursor c_cd is
3526 select
3527 abr.det_pl_ytd_cntrs_cd,
3528 abr.entr_ann_val_flag,
3529 abr.rt_mlt_cd
3530 from ben_enrt_rt ecr,
3531 ben_acty_base_rt_f abr
3532 where ecr.enrt_rt_id = p_enrt_rt_id
3533 and ecr.business_group_id = p_business_group_id
3534 and ecr.acty_base_rt_id = abr.acty_base_rt_id
3535 and p_effective_date between
3536 abr.effective_start_date and abr.effective_end_date;
3537 --
3538 l_prnt_ann_rt varchar2(1):= 'N';
3539 --
3540 cursor c_payroll (p_payroll_id number,
3541 p_effective_date date) is
3542 select pay_date_offset
3543 from pay_all_payrolls_f prl
3544 where prl.payroll_id = p_payroll_id
3545 and p_effective_date between prl.effective_start_date
3546 and prl.effective_end_date;
3547 --
3548 cursor c_first_payroll (p_person_id number,
3549 p_effective_date date) is
3550 select payroll_id
3551 from per_all_assignments_f ass
3552 where ass.person_id = p_person_id
3553 and ass.primary_flag = 'Y'
3554 and ass.assignment_type <> 'C'
3555 and p_effective_date between ass.effective_start_date
3556 and ass.effective_end_date
3557 order by decode(ass.assignment_type, 'E',1,'B',2,3);
3558
3559
3560 l_cd ben_acty_base_rt_f.det_pl_ytd_cntrs_cd%TYPE := null;
3561 l_entr_ann_val_flag varchar2(30);
3562 l_rt_mlt_cd varchar2(30);
3563 l_call_balance boolean;
3564 l_pay_date_offset number;
3565 l_first_payroll_id number;
3566 l_first_pay_date_offset number;
3567 --
3568 begin
3569 --
3570 if g_debug then
3571 hr_utility.set_location('Entering '||l_package, 10);
3572 end if;
3573 hr_utility.set_location('Annual Amount in Annual to Period '||p_amount, 10);
3574
3575 --
3576 -- Check for a null amount. Assumption is that a calculation
3577 -- cannot be performed on a null amount.
3578 --
3579 if p_amount is null then
3580 --
3581 return null;
3582 --
3583 end if;
3584 --
3585 open c_payroll (p_payroll_id, p_effective_date);
3586 fetch c_payroll into l_pay_date_offset;
3587 close c_payroll;
3588 --
3589 if g_annual_to_period_cached > 0 then
3590 --
3591 begin
3592 --
3593 l_hv := mod(nvl(p_amount,1)
3594 +nvl(p_enrt_rt_id,2)
3595 +nvl(p_elig_per_elctbl_chc_id,3)
3596 +nvl(p_start_date-hr_api.g_sot,4)
3597 +nvl(p_end_date-hr_api.g_sot,5)
3598 +nvl(p_payroll_id,6)
3599 +nvl(p_element_type_id,7)
3600 ,ben_hash_utility.get_hash_key);
3601 --
3602 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3603 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3604 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3605 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3606 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3607 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3608 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3609 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3610 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3611 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3612 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3613 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3614 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3615 then
3616 --
3617 null;
3618 --
3619 else
3620 --
3621 l_hv := l_hv+g_hash_jump;
3622 --
3623 loop
3624 --
3625 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3626 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3627 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3628 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3629 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3630 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3631 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3632 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3633 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3634 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3635 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3636 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3637 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3638 then
3639 --
3640 exit;
3641 --
3642 else
3643 --
3644 l_hv := l_hv+g_hash_jump;
3645 --
3646 end if;
3647 --
3648 end loop;
3649 --
3650 end if;
3651 --
3652 exception
3653 when no_data_found then
3654 --
3655 l_hv := null;
3656 end;
3657 --
3658 if l_hv is not null then
3659 --
3660 return g_annual_to_period_cache(l_hv).period_amt;
3661 --
3662 end if;
3663 --
3664 end if;
3665 --
3666 if p_use_balance_flag = 'Y' then
3667 l_call_balance := true;
3668 if p_enrt_rt_id is not null then
3669 open c_cd;
3670 fetch c_cd into l_cd, l_entr_ann_val_flag,l_rt_mlt_cd;
3671 close c_cd;
3672 if l_cd is null then
3673 if l_rt_mlt_cd = 'PRNT' then
3674 l_cmplt_yr_flag := 'N';
3675 l_prnt_ann_rt := 'Y';
3676 else
3677 l_call_balance := false;
3678 end if;
3679 -- l_call_balance := false;
3680 else
3681 l_cmplt_yr_flag := 'N';
3682 end if;
3683 end if;
3684 end if;
3685 --
3686 set_default_dates(p_enrt_rt_id => p_enrt_rt_id,
3687 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3688 p_business_group_id => p_business_group_id,
3689 p_complete_year_flag => l_cmplt_yr_flag,
3690 p_effective_date => p_effective_date,
3691 p_payroll_id => p_payroll_id,
3692 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3693 p_start_date => l_start_date,
3694 p_end_date => l_end_date,
3695 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3696 p_yr_start_date => l_yr_start_date);
3697 --
3698 -- Compute balances only if use_balance_flag is ON.
3699 --
3700 -- bug#3510633
3701 if (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3702 p_annual_target or l_prnt_ann_rt = 'Y') and
3703 p_person_id is not null then
3704 --
3705 open c_first_payroll (p_person_id, (l_yr_start_date - 1));
3706 fetch c_first_payroll into l_first_payroll_id;
3707 close c_first_payroll;
3708 --
3709 if l_first_payroll_id is not null then
3710 --
3711 open c_payroll(l_first_payroll_id, (l_yr_start_date - 1));
3712 fetch c_payroll into l_first_pay_date_offset;
3713 close c_payroll;
3714 --
3715 end if;
3716 --
3717 end if;
3718 -- 5642552 : Evem of check_offset is 0, start annual-rates as of the period start.
3719 -- l_first_pay_date_offset <> 0 and
3720 --
3721 if (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3722 p_annual_target or l_prnt_ann_rt = 'Y') then
3723 --
3724 -- 5642552: Added this.
3725 if p_start_date is not null and p_start_date < l_yr_start_date then
3726 l_start_date := p_start_date;
3727 end if;
3728 -- 5642552: End.
3729 --
3730 l_yr_start_date := l_yr_start_date - NVL(l_first_pay_date_offset,0);
3731 --
3732 if g_debug then
3733 hr_utility.set_location('Pay Date Offset'||l_pay_date_offset,11);
3734 end if;
3735 end if;
3736 --
3737
3738 if l_call_balance then
3739 --
3740 l_balance_amt := get_balance
3741 (p_enrt_rt_id => p_enrt_rt_id,
3742 p_payroll_id => p_payroll_id,
3743 p_business_group_id => p_business_group_id,
3744 p_effective_date => p_effective_date,
3745 p_start_date => l_yr_start_date);
3746 --
3747 end if;
3748 --
3749 if l_acty_ref_perd_cd = 'PYR' then
3750 --
3751 -- If Annualy, don't need to get number of periods.
3752 --
3753 l_no_of_periods := 1;
3754 --
3755 else
3756 if l_pay_date_offset <> 0 and (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC' or
3757 p_annual_target or l_prnt_ann_rt = 'Y') then
3758 l_no_of_periods := get_periods_between
3759 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3760 p_start_date => l_start_date,
3761 p_end_date => l_end_date,
3762 p_payroll_id => p_payroll_id,
3763 p_business_group_id => p_business_group_id,
3764 p_enrt_rt_id => p_enrt_rt_id,
3765 p_element_type_id => p_element_type_id,
3766 p_effective_date => p_effective_date,
3767 p_use_check_date => true
3768 );
3769
3770 --
3771 else
3772 --
3773 l_no_of_periods := get_periods_between
3774 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3775 p_start_date => l_start_date,
3776 p_end_date => l_end_date,
3777 p_payroll_id => p_payroll_id,
3778 p_business_group_id => p_business_group_id,
3779 p_enrt_rt_id => p_enrt_rt_id,
3780 p_element_type_id => p_element_type_id,
3781 p_effective_date => p_effective_date
3782 );
3783 --
3784 end if;
3785 --
3786 end if;
3787 --
3788 l_period_amt := (l_annual_amt - l_balance_amt)/l_no_of_periods;
3789 --
3790 if p_rounding_flag = 'Y' then
3791 --
3792 l_period_amt := round(l_period_amt,2);
3793 --
3794 end if;
3795 --
3796 if g_annual_to_period_cached > 0 then
3797 --
3798 -- Only store the
3799 --
3800 l_hv := mod(nvl(p_amount,1)
3801 +nvl(p_enrt_rt_id,2)
3802 +nvl(p_elig_per_elctbl_chc_id,3)
3803 +nvl(p_start_date-hr_api.g_sot,4)
3804 +nvl(p_end_date-hr_api.g_sot,5)
3805 +nvl(p_payroll_id,6)
3806 +nvl(p_element_type_id,7)
3807 ,ben_hash_utility.get_hash_key);
3808 --
3809 while g_annual_to_period_cache.exists(l_hv)
3810 loop
3811 --
3812 l_hv := l_hv+g_hash_jump;
3813 --
3814 end loop;
3815 --
3816 g_annual_to_period_cache(l_hv).amount := p_amount;
3817 g_annual_to_period_cache(l_hv).enrt_rt_id := p_enrt_rt_id;
3818 g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
3819 g_annual_to_period_cache(l_hv).acty_ref_perd_cd := p_acty_ref_perd_cd;
3820 g_annual_to_period_cache(l_hv).business_group_id := p_business_group_id;
3821 g_annual_to_period_cache(l_hv).effective_date := p_effective_date;
3822 g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
3823 g_annual_to_period_cache(l_hv).complete_year_flag := p_complete_year_flag;
3824 g_annual_to_period_cache(l_hv).use_balance_flag := p_use_balance_flag;
3825 g_annual_to_period_cache(l_hv).start_date := p_start_date;
3826 g_annual_to_period_cache(l_hv).end_date := p_end_date;
3827 g_annual_to_period_cache(l_hv).payroll_id := p_payroll_id;
3828 g_annual_to_period_cache(l_hv).element_type_id := p_element_type_id;
3829 g_annual_to_period_cache(l_hv).period_amt := l_period_amt;
3830 g_annual_to_period_cache(l_hv).pp_in_yr_used_num := l_no_of_periods;
3831 --
3832 end if;
3833 --
3834 hr_utility.set_location('Leaving '||l_package , 90);
3835 --
3836 return(l_period_amt);
3837 --
3838 exception
3839 --
3840 when others then
3841 fnd_message.raise_error;
3842 --
3843 end annual_to_period;
3844 function annual_to_period_out(p_amount in number,
3845 p_enrt_rt_id in number default null,
3846 p_elig_per_elctbl_chc_id in number default null,
3847 p_acty_ref_perd_cd in varchar2 default null,
3848 p_business_group_id in number default null,
3849 p_effective_date in date default null,
3850 p_lf_evt_ocrd_dt in date default null,
3851 p_complete_year_flag in varchar2 default 'N',
3852 p_use_balance_flag in varchar2 default 'N',
3853 p_start_date in date default null,
3854 p_end_date in date default null,
3855 p_payroll_id in number default null,
3856 p_element_type_id in number default null,
3857 p_pp_in_yr_used_num out nocopy number)
3858 return number is
3859 --
3860 l_hv pls_integer;
3861 --
3862 l_period_amt number := 0;
3863 l_balance_amt number := 0;
3864 l_annual_amt number := p_amount;
3865 l_no_of_periods number := 1;
3866 l_start_date date := p_start_date;
3867 l_end_date date := p_end_date;
3868 l_yr_start_date date := null;
3869 l_acty_ref_perd_cd varchar2(30) := p_acty_ref_perd_cd;
3870 l_package varchar2(80) := g_package || '.annual_to_period';
3871 l_cmplt_yr_flag varchar2(20) := p_complete_year_flag;
3872 --
3873 cursor c_cd is
3874 select
3875 abr.det_pl_ytd_cntrs_cd,
3876 abr.entr_ann_val_flag,
3877 abr.rt_mlt_cd
3878 from ben_enrt_rt ecr,
3879 ben_acty_base_rt_f abr
3880 where ecr.enrt_rt_id = p_enrt_rt_id
3881 and ecr.business_group_id = p_business_group_id
3882 and ecr.acty_base_rt_id = abr.acty_base_rt_id
3883 and p_effective_date between
3884 abr.effective_start_date and abr.effective_end_date;
3885 --
3886 cursor c_payroll is
3887 select pay_date_offset
3888 from pay_all_payrolls_f prl
3889 where prl.payroll_id = p_payroll_id
3890 and p_effective_date between prl.effective_start_date
3891 and prl.effective_end_date;
3892 --
3893 l_cd ben_acty_base_rt_f.det_pl_ytd_cntrs_cd%TYPE := null;
3894 l_entr_ann_val_flag varchar2(30);
3895 l_rt_mlt_cd varchar2(30);
3896 l_call_balance boolean;
3897 l_pay_date_offset number;
3898 --
3899 begin
3900 --
3901 -- hr_utility.set_location('Entering '||l_package, 10);
3902 --
3903 -- Check for a null amount. Assumption is that a calculation
3904 -- cannot be performed on a null amount.
3905 --
3906 if p_amount is null then
3907 --
3908 return null;
3909 --
3910 end if;
3911 --
3912 open c_payroll;
3913 fetch c_payroll into l_pay_date_offset;
3914 close c_payroll;
3915 --
3916 if g_annual_to_period_cached > 0 then
3917 --
3918 begin
3919 --
3920 l_hv := mod(nvl(p_amount,1)
3921 +nvl(p_enrt_rt_id,2)
3922 +nvl(p_elig_per_elctbl_chc_id,3)
3923 +nvl(p_start_date-hr_api.g_sot,4)
3924 +nvl(p_end_date-hr_api.g_sot,5)
3925 +nvl(p_payroll_id,6)
3926 +nvl(p_element_type_id,7)
3927 ,ben_hash_utility.get_hash_key);
3928 --
3929 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3930 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3931 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3932 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3933 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3934 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3935 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3936 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3937 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3938 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3939 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3940 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3941 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3942 then
3943 --
3944 null;
3945 --
3946 else
3947 --
3948 l_hv := l_hv+g_hash_jump;
3949 --
3950 loop
3951 --
3952 if nvl(g_annual_to_period_cache(l_hv).amount,-1) = nvl(p_amount,-1)
3953 and nvl(g_annual_to_period_cache(l_hv).enrt_rt_id,-1) = nvl(p_enrt_rt_id,-1)
3954 and nvl(g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1)
3955 and nvl(g_annual_to_period_cache(l_hv).acty_ref_perd_cd,hr_api.g_varchar2) = nvl(p_acty_ref_perd_cd,hr_api.g_varchar2)
3956 and nvl(g_annual_to_period_cache(l_hv).business_group_id,-1) = nvl(p_business_group_id,-1)
3957 and nvl(g_annual_to_period_cache(l_hv).effective_date,hr_api.g_sot) = nvl(p_effective_date,hr_api.g_sot)
3958 and nvl(g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt,hr_api.g_sot) = nvl(p_lf_evt_ocrd_dt,hr_api.g_sot)
3959 and nvl(g_annual_to_period_cache(l_hv).complete_year_flag,hr_api.g_varchar2) = nvl(p_complete_year_flag,hr_api.g_varchar2)
3960 and nvl(g_annual_to_period_cache(l_hv).use_balance_flag,hr_api.g_varchar2) = nvl(p_use_balance_flag,hr_api.g_varchar2)
3961 and nvl(g_annual_to_period_cache(l_hv).start_date,hr_api.g_sot) = nvl(p_start_date,hr_api.g_sot)
3962 and nvl(g_annual_to_period_cache(l_hv).end_date,hr_api.g_sot) = nvl(p_end_date,hr_api.g_sot)
3963 and nvl(g_annual_to_period_cache(l_hv).payroll_id,-1) = nvl(p_payroll_id,-1)
3964 and nvl(g_annual_to_period_cache(l_hv).element_type_id,-1) = nvl(p_element_type_id,-1)
3965 then
3966 --
3967 exit;
3968 --
3969 else
3970 --
3971 l_hv := l_hv+g_hash_jump;
3972 --
3973 end if;
3974 --
3975 end loop;
3976 --
3977 end if;
3978 --
3979 exception
3980 when no_data_found then
3981 --
3982 l_hv := null;
3983 end;
3984 --
3985 if l_hv is not null then
3986 --
3987 p_pp_in_yr_used_num := g_annual_to_period_cache(l_hv).pp_in_yr_used_num;
3988 return g_annual_to_period_cache(l_hv).period_amt;
3989 --
3990 end if;
3991 --
3992 end if;
3993 --
3994 if p_use_balance_flag = 'Y' then
3995 l_call_balance := true;
3996 if p_enrt_rt_id is not null then
3997 open c_cd;
3998 fetch c_cd into l_cd,l_entr_ann_val_flag,l_rt_mlt_cd;
3999 close c_cd;
4000 if l_cd is null then
4001 l_call_balance := false;
4002 else
4003 l_cmplt_yr_flag := 'N';
4004 end if;
4005 end if;
4006 end if;
4007 --
4008 set_default_dates(p_enrt_rt_id => p_enrt_rt_id,
4009 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
4010 p_business_group_id => p_business_group_id,
4011 p_complete_year_flag => l_cmplt_yr_flag,
4012 p_effective_date => p_effective_date,
4013 p_payroll_id => p_payroll_id,
4014 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
4015 p_start_date => l_start_date,
4016 p_end_date => l_end_date,
4017 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
4018 p_yr_start_date => l_yr_start_date);
4019 --
4020 -- Compute balances only if use_balance_flag is ON.
4021 --
4022 if l_call_balance then
4023 --
4024 l_balance_amt := get_balance
4025 (p_enrt_rt_id => p_enrt_rt_id,
4026 p_payroll_id => p_payroll_id,
4027 p_business_group_id => p_business_group_id,
4028 p_effective_date => p_effective_date,
4029 p_start_date => l_yr_start_date);
4030 --
4031 end if;
4032 --
4033 if l_acty_ref_perd_cd = 'PYR' then
4034 --
4035 -- If Annualy, don't need to get number of periods.
4036 --
4037 l_no_of_periods := 1;
4038 --
4039 else
4040 --
4041 if l_pay_date_offset <> 0 and (l_entr_ann_val_flag = 'Y' or l_rt_mlt_cd = 'SAREC') then
4042 l_no_of_periods := get_periods_between
4043 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
4044 p_start_date => l_start_date,
4045 p_end_date => l_end_date,
4046 p_payroll_id => p_payroll_id,
4047 p_business_group_id => p_business_group_id,
4048 p_enrt_rt_id => p_enrt_rt_id,
4049 p_element_type_id => p_element_type_id,
4050 p_effective_date => p_effective_date,
4051 p_use_check_date => true
4052 );
4053 else
4054 l_no_of_periods := get_periods_between
4055 (p_acty_ref_perd_cd => l_acty_ref_perd_cd,
4056 p_start_date => l_start_date,
4057 p_end_date => l_end_date,
4058 p_payroll_id => p_payroll_id,
4059 p_business_group_id => p_business_group_id,
4060 p_enrt_rt_id => p_enrt_rt_id,
4061 p_element_type_id => p_element_type_id,
4062 p_effective_date => p_effective_date
4063 );
4064 --
4065 end if;
4066 --
4067 end if;
4068 --
4069 l_period_amt := (l_annual_amt - l_balance_amt)/l_no_of_periods;
4070 --
4071 l_period_amt := round(l_period_amt,2);
4072 --
4073 if g_annual_to_period_cached > 0 then
4074 --
4075 -- Only store the
4076 --
4077 l_hv := mod(nvl(p_amount,1)
4078 +nvl(p_enrt_rt_id,2)
4079 +nvl(p_elig_per_elctbl_chc_id,3)
4080 +nvl(p_start_date-hr_api.g_sot,4)
4081 +nvl(p_end_date-hr_api.g_sot,5)
4082 +nvl(p_payroll_id,6)
4083 +nvl(p_element_type_id,7)
4084 ,ben_hash_utility.get_hash_key);
4085 --
4086 while g_annual_to_period_cache.exists(l_hv)
4087 loop
4088 --
4089 l_hv := l_hv+g_hash_jump;
4090 --
4091 end loop;
4092 --
4093 g_annual_to_period_cache(l_hv).amount := p_amount;
4094 g_annual_to_period_cache(l_hv).enrt_rt_id := p_enrt_rt_id;
4095 g_annual_to_period_cache(l_hv).elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
4096 g_annual_to_period_cache(l_hv).acty_ref_perd_cd := p_acty_ref_perd_cd;
4097 g_annual_to_period_cache(l_hv).business_group_id := p_business_group_id;
4098 g_annual_to_period_cache(l_hv).effective_date := p_effective_date;
4099 g_annual_to_period_cache(l_hv).lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
4100 g_annual_to_period_cache(l_hv).complete_year_flag := p_complete_year_flag;
4101 g_annual_to_period_cache(l_hv).use_balance_flag := p_use_balance_flag;
4102 g_annual_to_period_cache(l_hv).start_date := p_start_date;
4103 g_annual_to_period_cache(l_hv).end_date := p_end_date;
4104 g_annual_to_period_cache(l_hv).payroll_id := p_payroll_id;
4105 g_annual_to_period_cache(l_hv).element_type_id := p_element_type_id;
4106 g_annual_to_period_cache(l_hv).period_amt := l_period_amt;
4107 g_annual_to_period_cache(l_hv).pp_in_yr_used_num := l_no_of_periods;
4108 --
4109 end if;
4110 --
4111 -- hr_utility.set_location('Leaving '||l_package , 90);
4112 --
4113 p_pp_in_yr_used_num := l_no_of_periods;
4114 return(l_period_amt);
4115 --
4116 exception
4117 --
4118 when others then
4119 p_pp_in_yr_used_num := null; -- no copy changes
4120 fnd_message.raise_error;
4121 --
4122 end annual_to_period_out;
4123 ---------------------------------------------------------------------------
4124 -- compare_balances
4125 --
4126 -- Find a person's period-to-date balance and their claims-to-date balance.
4127 -- Return new min or max annual value based upon these balances.
4128 --
4129 -- This procedure can be called to just obtain the ptd and clm balances.
4130 -- Just call with req parms and zero p_ann_mn_val and p_ann_mx_val values.
4131 -- If there are no balances, null will be returned.
4132 --
4133 -- you can call this will elig_per_elctbl_chc_id or ALL of the following:
4134 -- ,p_lf_evt_ocrd_dt
4135 -- ,p_pgm_id and/or ,p_pl_id and/or ,p_oipl_id
4136 -- ,p_per_in_ler_id
4137 -- ,p_business_group_id
4138 --
4139 ---------------------------------------------------------------------------
4140 procedure compare_balances
4141 (p_person_id in number
4142 ,p_effective_date in date
4143 ,p_lf_evt_ocrd_dt in date default null
4144 ,p_elig_per_elctbl_chc_id in number default null
4145 ,p_pgm_id in number default null
4146 ,p_pl_id in number default null
4147 ,p_oipl_id in number default null
4148 ,p_per_in_ler_id in number default null
4149 ,p_business_group_id in number default null
4150 ,p_acty_base_rt_id in number
4151 ,p_perform_edit_flag in varchar2 default 'N'
4152 ,p_entered_ann_val in number default null
4153 ,p_ann_mn_val in out nocopy number
4154 ,p_ann_mx_val in out nocopy number
4155 ,p_ptd_balance out nocopy number
4156 ,p_clm_balance out nocopy number) is
4157
4158 l_package varchar2(80) := g_package || '.compare_balances';
4159 --
4160 cursor c_pel is
4161 select pel.enrt_perd_id,
4162 pel.lee_rsn_id,
4163 epe.yr_perd_id
4164 from ben_pil_elctbl_chc_popl pel,
4165 ben_elig_per_elctbl_chc epe
4166 where pel.per_in_ler_id = p_per_in_ler_id
4167 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
4168 and pel.per_in_ler_id = epe.per_in_ler_id
4169 and ((p_pgm_id is not null and
4170 pel.pgm_id = p_pgm_id)
4171 OR
4172 (p_pgm_id is null and
4173 pel.pl_id = p_pl_id));
4174 --
4175 cursor get_epe is
4176 select pil.lf_evt_ocrd_dt,
4177 epe.pgm_id,
4178 epe.pl_id,
4179 epe.oipl_id,
4180 epe.per_in_ler_id,
4181 epe.yr_perd_id,
4182 pel.enrt_perd_id,
4183 pel.lee_rsn_id,
4184 pil.business_group_id
4185 from ben_elig_per_elctbl_chc epe,
4186 ben_pil_elctbl_chc_popl pel,
4187 ben_per_in_ler pil
4188 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
4189 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
4190 and pel.per_in_ler_id = pil.per_in_ler_id;
4191 --
4192 l_get_epe get_epe%rowtype;
4193 --
4194 cursor c_yrp is
4195 select yrp.start_date
4196 from ben_yr_perd yrp
4197 where yrp.yr_perd_id = l_get_epe.yr_perd_id
4198 and yrp.business_group_id = l_get_epe.business_group_id;
4199 --
4200 l_yr_start_date date;
4201 --
4202 cursor abr_balance is
4203 select abr.ptd_comp_lvl_fctr_id,
4204 abr.clm_comp_lvl_fctr_id,
4205 abr.det_pl_ytd_cntrs_cd
4206 from ben_acty_base_rt_f abr
4207 where abr.acty_base_rt_id = p_acty_base_rt_id
4208 and p_effective_date between
4209 abr.effective_start_date and abr.effective_end_date;
4210 --
4211 l_abr_balance abr_balance%rowtype;
4212 --
4213 begin
4214 --
4215 g_debug := hr_utility.debug_enabled;
4216 --
4217 if g_debug then
4218 hr_utility.set_location('Entering '||l_package , 10);
4219 end if;
4220
4221 if p_elig_per_elctbl_chc_id is not null then
4222 -- get needed values
4223 open get_epe;
4224 fetch get_epe into l_get_epe;
4225 close get_epe;
4226 elsif (p_pgm_id is null and p_pl_id is null and p_oipl_id is null)
4227 or p_per_in_ler_id is null or p_business_group_id is null
4228 or p_lf_evt_ocrd_dt is null then
4229 fnd_message.set_name('BEN','BEN_92404_NEED_MORE_VARS');
4230 fnd_message.set_token('PROC',l_package);
4231 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4232 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
4233 fnd_message.set_token('PL_ID',to_char(p_pl_id));
4234 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
4235 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
4236 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
4237 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
4238 fnd_message.raise_error;
4239 else
4240 --
4241 open c_pel;
4242 fetch c_pel into l_get_epe.enrt_perd_id,
4243 l_get_epe.lee_rsn_id,
4244 l_get_epe.yr_perd_id;
4245 close c_pel;
4246 --
4247 l_get_epe.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
4248 l_get_epe.pgm_id := p_pgm_id;
4249 l_get_epe.pl_id := p_pl_id;
4250 l_get_epe.oipl_id := p_oipl_id;
4251 l_get_epe.per_in_ler_id := p_per_in_ler_id;
4252 l_get_epe.business_group_id := p_business_group_id;
4253 --
4254 end if;
4255
4256 if p_entered_ann_val is null and p_perform_edit_flag = 'Y' then
4257 fnd_message.set_name('BEN','BEN_92405_NEED_ENTERED_VAL');
4258 fnd_message.set_token('PROC',l_package);
4259 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4260 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
4261 fnd_message.set_token('PL_ID',to_char(p_pl_id));
4262 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
4263 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
4264 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
4265 fnd_message.raise_error;
4266 end if;
4267
4268 p_ptd_balance := null;
4269 p_clm_balance := null;
4270
4271 open abr_balance;
4272 fetch abr_balance into l_abr_balance;
4273 close abr_balance;
4274
4275 if l_abr_balance.det_pl_ytd_cntrs_cd is not null then
4276 --
4277 open c_yrp;
4278 fetch c_yrp into l_yr_start_date;
4279 close c_yrp;
4280 --
4281 p_ptd_balance := get_balance
4282 (p_person_id => p_person_id,
4283 p_per_in_ler_id => l_get_epe.per_in_ler_id,
4284 p_pgm_id => l_get_epe.pgm_id,
4285 p_pl_id => l_get_epe.pl_id,
4286 p_oipl_id => l_get_epe.oipl_id,
4287 p_enrt_perd_id => l_get_epe.enrt_perd_id,
4288 p_lee_rsn_id => l_get_epe.lee_rsn_id,
4289 p_acty_base_rt_id => p_acty_base_rt_id,
4290 p_ptd_comp_lvl_fctr_id => l_abr_balance.ptd_comp_lvl_fctr_id,
4291 p_det_pl_ytd_cntrs_cd => l_abr_balance.det_pl_ytd_cntrs_cd,
4292 p_lf_evt_ocrd_dt => l_get_epe.lf_evt_ocrd_dt,
4293 p_business_group_id => l_get_epe.business_group_id,
4294 p_start_date => l_yr_start_date,
4295 p_effective_date => p_effective_date);
4296
4297 if p_ptd_balance is not null and p_entered_ann_val < p_ptd_balance and
4298 p_perform_edit_flag = 'Y' then
4299 -- The period-to-date is what the prtt has already paid into this plan.
4300 -- Do not allow their elected annual value to fall below the ptd-balance.
4301 fnd_message.set_name('BEN','BEN_92406_BELOW_PTD');
4302 fnd_message.set_token('PROC',l_package);
4303 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4304 fnd_message.set_token('ANN_VAL',to_char(p_entered_ann_val));
4305 fnd_message.set_token('PTD_BAL',to_char(p_ptd_balance));
4306 fnd_message.raise_error;
4307 end if;
4308 end if;
4309
4310 if l_abr_balance.clm_comp_lvl_fctr_id is not null then
4311 -- Get claims to date.
4312 ben_derive_factors.determine_compensation
4313 (p_comp_lvl_fctr_id => l_abr_balance.clm_comp_lvl_fctr_id,
4314 p_person_id => p_person_id,
4315 p_pgm_id => l_get_epe.pgm_id,
4316 p_pl_id => l_get_epe.pl_id,
4317 p_oipl_id => l_get_epe.oipl_id,
4318 p_per_in_ler_id => l_get_epe.per_in_ler_id,
4319 p_business_group_id => l_get_epe.business_group_id,
4320 p_perform_rounding_flg => TRUE,
4321 p_effective_date => p_effective_date,
4322 p_lf_evt_ocrd_dt => l_get_epe.lf_evt_ocrd_dt,
4323 p_value => p_clm_balance);
4324
4325 if p_clm_balance is not null and p_entered_ann_val < p_clm_balance and
4326 p_perform_edit_flag = 'Y' then
4327 --The claims-to-date is what the prtt has already claimed against the FSA
4328 --Do not allow their elected annual value to fall below the clm-balance.
4329 fnd_message.set_name('BEN','BEN_92407_BELOW_CLM');
4330 fnd_message.set_token('PROC',l_package);
4331 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4332 fnd_message.set_token('ANN_VAL',to_char(p_entered_ann_val));
4333 fnd_message.set_token('CLM_BAL',to_char(p_clm_balance));
4334 fnd_message.raise_error;
4335 end if;
4336 end if;
4337
4338 if g_debug then
4339 hr_utility.set_location('p_clm_balance '||to_char(p_clm_balance)||
4340 ' p_ptd_balance '||to_char(p_ptd_balance) , 97);
4341 end if;
4342
4343 -- the minimum that the prtt can select is the higher of payments-to-date,
4344 -- claims-to-date or the already computed minimum that was passed in.
4345 if p_ann_mn_val is not null or p_clm_balance is not null or p_ptd_balance
4346 is not null then
4347 p_ann_mn_val := greatest(nvl(p_ann_mn_val,0), nvl(p_clm_balance,0),
4348 nvl(p_ptd_balance,0));
4349 end if;
4350
4351 -- if the re-computed min val is greater than the previously computed max
4352 -- val, raise the max val.
4353 if p_ann_mx_val is not null and nvl(p_ann_mn_val,0) > p_ann_mx_val then
4354 p_ann_mx_val := p_ann_mn_val;
4355 end if;
4356
4357 if g_debug then
4358 hr_utility.set_location('p_ann_mn_val '||to_char(p_ann_mn_val)||
4359 ' p_ann_mx_val '||to_char(p_ann_mx_val) , 97);
4360 hr_utility.set_location('Leaving '||l_package , 99);
4361 end if;
4362 end compare_balances;
4363
4364 ---------------------------------------------------------------------------
4365 -- prorate_min_max
4366 --
4367 -- Recompute a person's prorated minimum and maximum rate values.
4368 -- Return new min or max annual value based upon these balances.
4369 -- Optionally, edit the entered value against these min's and max's.
4370 ---------------------------------------------------------------------------
4371 procedure prorate_min_max
4372 (p_person_id in number
4373 ,p_effective_date in date
4374 ,p_elig_per_elctbl_chc_id in number
4375 ,p_acty_base_rt_id in number
4376 ,p_rt_strt_dt in date
4377 ,p_ann_mn_val in out nocopy number
4378 ,p_ann_mx_val in out nocopy number ) is
4379
4380 l_package varchar2(80) := g_package || '.prorate_min_max';
4381
4382 cursor get_cds is
4383 select abr.prort_mn_ann_elcn_val_cd
4384 ,abr.prort_mx_ann_elcn_val_cd
4385 ,abr.prort_mn_ann_elcn_val_rl
4386 ,abr.prort_mx_ann_elcn_val_rl
4387 ,abr.element_type_id
4388 from ben_acty_base_rt_f abr
4389 where abr.acty_base_rt_id = p_acty_base_rt_id
4390 and p_effective_date between
4391 abr.effective_start_date and abr.effective_end_date;
4392 l_get_cds get_cds%rowtype;
4393
4394 cursor get_epe_pl_yr is
4395 select yrp.start_date, yrp.end_date, epe.pgm_id, epe.pl_id,
4396 epe.pl_typ_id, epe.business_group_id, pil.ler_id,
4397 oipl.opt_id
4398 from ben_yr_perd yrp
4399 ,ben_elig_per_elctbl_chc epe
4400 ,ben_per_in_ler pil
4401 ,ben_oipl_f oipl
4402 where epe.yr_perd_id = yrp.yr_perd_id
4403 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
4404 and epe.per_in_ler_id = pil.per_in_ler_id
4405 and epe.oipl_id = oipl.oipl_id(+)
4406 and p_effective_date between
4407 nvl(oipl.effective_start_date, p_effective_date)
4408 and nvl(oipl.effective_end_date, p_effective_date);
4409 l_get_epe_pl_yr get_epe_pl_yr%rowtype;
4410
4411 cursor get_pgm_acty_ref_cd (p_pgm_id number) is
4412 select pgm.acty_ref_perd_cd
4413 from ben_pgm_f pgm
4414 where pgm.pgm_id = p_pgm_id
4415 and p_effective_date between
4416 pgm.effective_start_date and pgm.effective_end_date;
4417
4418 cursor get_pl_acty_ref_cd (p_pl_id number) is
4419 select pl.nip_acty_ref_perd_cd
4420 from ben_pl_f pl
4421 where pl.pl_id = p_pl_id
4422 and p_effective_date between
4423 pl.effective_start_date and pl.effective_end_date;
4424
4425
4426 cursor get_asg_info is
4427 select asg.assignment_id, asg.organization_id, loc.region_2 state, asg.location_id,
4428 asg.payroll_id
4429 from hr_locations_all loc, per_all_assignments_f asg
4430 where asg.person_id = p_person_id
4431 and asg.assignment_type <> 'C'
4432 and asg.primary_flag = 'Y'
4433 and loc.location_id(+) = asg.location_id
4434 and p_effective_date between
4435 asg.effective_start_date and asg.effective_end_date
4436 order by 1;
4437 l_get_asg_info get_asg_info%rowtype;
4438
4439
4440 l_12_months_back date;
4441 l_dividend number;
4442 l_divisor number;
4443 l_percent number;
4444 l_periods number := null;
4445 l_acty_ref_perd_cd varchar2(30);
4446 l_outputs ff_exec.outputs_t;
4447 l_jurisdiction PAY_CA_EMP_PROV_TAX_INFO_F.JURISDICTION_CODE%type := null;
4448
4449 begin
4450 --
4451 g_debug := hr_utility.debug_enabled;
4452 --
4453 if g_debug then
4454 hr_utility.set_location ('Entering '||l_package,10);
4455 end if;
4456
4457 open get_cds;
4458 fetch get_cds into l_get_cds;
4459 close get_cds;
4460
4461 if g_debug then
4462 hr_utility.set_location('prort_mn_ann_elcn_val_cd '||
4463 l_get_cds.prort_mn_ann_elcn_val_cd,20);
4464 end if;
4465
4466 if g_debug then
4467 hr_utility.set_location('prort_mx_ann_elcn_val_cd '||
4468 l_get_cds.prort_mx_ann_elcn_val_cd,20);
4469 end if;
4470
4471 -- do not prorate null values.
4472 if p_ann_mn_val is null then
4473 l_get_cds.prort_mn_ann_elcn_val_cd := null;
4474 end if;
4475 if p_ann_mx_val is null then
4476 l_get_cds.prort_mx_ann_elcn_val_cd := null;
4477 end if;
4478
4479 if l_get_cds.prort_mn_ann_elcn_val_cd is not null
4480 or l_get_cds.prort_mx_ann_elcn_val_cd is not null then
4481 open get_epe_pl_yr;
4482 fetch get_epe_pl_yr into l_get_epe_pl_yr;
4483 if get_epe_pl_yr%NOTFOUND or get_epe_pl_yr%NOTFOUND is null then
4484 close get_epe_pl_yr;
4485 fnd_message.set_name('BEN','BEN_92408_EPE_PL_YR_NOTF');
4486 fnd_message.set_token('PROC',l_package);
4487 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4488 fnd_message.set_token('CHC',to_char(p_elig_per_elctbl_chc_id));
4489 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
4490 fnd_message.raise_error;
4491 end if;
4492 close get_epe_pl_yr;
4493 --
4494 -- Bug No 4290565
4495 -- Added 1 to l_12_months_back as add_months is counting the last day of the previous year also
4496 --
4497 l_12_months_back := (add_months(l_get_epe_pl_yr.end_date,-12)) + 1;
4498 end if;
4499
4500 if l_get_cds.prort_mn_ann_elcn_val_cd = 'DR'
4501 or l_get_cds.prort_mx_ann_elcn_val_cd = 'DR' then
4502
4503 -- number of days from rate start to end of plan year.
4504 l_dividend := l_get_epe_pl_yr.end_date - p_rt_strt_dt;
4505 -- number of days in 12 months ending with end of plan year.
4506 l_divisor := l_get_epe_pl_yr.end_date - l_12_months_back;
4507
4508 l_percent := l_dividend/l_divisor;
4509 if l_get_cds.prort_mn_ann_elcn_val_cd = 'DR' then
4510 p_ann_mn_val := l_percent * p_ann_mn_val;
4511 end if;
4512 if l_get_cds.prort_mx_ann_elcn_val_cd = 'DR' then
4513 p_ann_mx_val := l_percent * p_ann_mx_val;
4514 end if;
4515 end if;
4516
4517 if l_get_cds.prort_mn_ann_elcn_val_cd = 'FPR'
4518 or l_get_cds.prort_mx_ann_elcn_val_cd = 'FPR' then
4519
4520 if l_get_epe_pl_yr.pgm_id is not null then
4521 open get_pgm_acty_ref_cd(p_pgm_id=> l_get_epe_pl_yr.pgm_id);
4522 fetch get_pgm_acty_ref_cd into l_acty_ref_perd_cd;
4523 close get_pgm_acty_ref_cd;
4524 else
4525 open get_pl_acty_ref_cd(p_pl_id=> l_get_epe_pl_yr.pl_id);
4526 fetch get_pl_acty_ref_cd into l_acty_ref_perd_cd;
4527 close get_pl_acty_ref_cd;
4528 end if;
4529 -- number of periods between rate start and end of plan year.
4530 l_dividend := get_periods_between
4531 (p_acty_ref_perd_cd => l_acty_ref_perd_cd
4532 ,p_start_date => p_rt_strt_dt
4533 ,p_end_date => l_get_epe_pl_yr.end_date
4534 ,p_business_group_id => l_get_epe_pl_yr.business_group_id
4535 ,p_element_type_id => l_get_cds.element_type_id
4536 ,p_effective_date => p_effective_date
4537 );
4538 -- number of periods from 12 months before end of plan year to end of plan year.
4539 l_divisor := get_periods_between
4540 (p_acty_ref_perd_cd => l_acty_ref_perd_cd
4541 ,p_start_date => l_12_months_back
4542 ,p_end_date => l_get_epe_pl_yr.end_date
4543 ,p_business_group_id => l_get_epe_pl_yr.business_group_id
4544 ,p_element_type_id => l_get_cds.element_type_id
4545 ,p_effective_date => p_effective_date
4546 );
4547 l_percent := l_dividend/l_divisor;
4548 if l_get_cds.prort_mn_ann_elcn_val_cd = 'FPR' then
4549 p_ann_mn_val := l_percent * p_ann_mn_val;
4550 end if;
4551 if l_get_cds.prort_mx_ann_elcn_val_cd = 'FPR' then
4552 p_ann_mx_val := l_percent * p_ann_mx_val;
4553 end if;
4554 end if;
4555
4556 if l_get_cds.prort_mn_ann_elcn_val_cd = 'FPPR'
4557 or l_get_cds.prort_mx_ann_elcn_val_cd = 'FPPR'
4558 or l_get_cds.prort_mn_ann_elcn_val_cd = 'RL'
4559 or l_get_cds.prort_mx_ann_elcn_val_cd = 'RL' then
4560 -- Find the person's assignment info
4561 open get_asg_info;
4562 fetch get_asg_info into l_get_asg_info;
4563 if get_asg_info%NOTFOUND or get_asg_info%NOTFOUND is null then
4564 close get_asg_info;
4565 fnd_message.set_name('BEN','BEN_92409_ASG_NOT_FOUND');
4566 fnd_message.set_token('PROC',l_package);
4567 fnd_message.set_token('PERSON',to_char(p_person_id));
4568 fnd_message.raise_error;
4569 end if;
4570 close get_asg_info;
4571 end if;
4572
4573 if l_get_cds.prort_mn_ann_elcn_val_cd = 'FPPR'
4574 or l_get_cds.prort_mx_ann_elcn_val_cd = 'FPPR' then
4575
4576 -- number of pay periods between rate start and end of plan year.
4577 l_dividend := get_periods_between
4578 (p_acty_ref_perd_cd => 'PP'
4579 ,p_start_date => p_rt_strt_dt
4580 ,p_end_date => l_get_epe_pl_yr.end_date
4581 ,p_payroll_id => l_get_asg_info.payroll_id
4582 ,p_business_group_id => l_get_epe_pl_yr.business_group_id
4583 ,p_element_type_id => l_get_cds.element_type_id
4584 ,p_effective_date => p_effective_date
4585 );
4586 -- number of pay periods from 12 months before end of plan year to end of plan year.
4587 l_divisor := get_periods_between
4588 (p_acty_ref_perd_cd => 'PP'
4589 ,p_start_date => l_12_months_back
4590 ,p_end_date => l_get_epe_pl_yr.end_date
4591 ,p_payroll_id => l_get_asg_info.payroll_id
4592 ,p_business_group_id => l_get_epe_pl_yr.business_group_id
4593 ,p_element_type_id => l_get_cds.element_type_id
4594 ,p_effective_date => p_effective_date
4595 );
4596
4597 l_percent := l_dividend/l_divisor;
4598 if l_get_cds.prort_mn_ann_elcn_val_cd = 'FPPR' then
4599 p_ann_mn_val := l_percent * p_ann_mn_val;
4600 end if;
4601 if l_get_cds.prort_mx_ann_elcn_val_cd = 'FPPR' then
4602 p_ann_mx_val := l_percent * p_ann_mx_val;
4603 end if;
4604 end if;
4605
4606
4607 if l_get_cds.prort_mn_ann_elcn_val_cd = 'RL' then
4608 /*
4609 if l_get_asg_info.state is not null then
4610 l_jurisdiction := pay_mag_utils.lookup_jurisdiction_code
4611 (p_state => l_get_asg_info.state);
4612 end if;
4613 */
4614 -- this rule returns an amount.
4615 l_outputs := benutils.formula
4616 (p_formula_id => l_get_cds.prort_mn_ann_elcn_val_rl,
4617 p_effective_date => p_effective_date,
4618 p_business_group_id => l_get_epe_pl_yr.business_group_id,
4619 p_assignment_id => l_get_asg_info.assignment_id,
4620 p_organization_id => l_get_asg_info.organization_id,
4621 p_acty_base_rt_id => p_acty_base_rt_id,
4622 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
4623 p_pgm_id => l_get_epe_pl_yr.pgm_id,
4624 p_pl_id => l_get_epe_pl_yr.pl_id,
4625 p_pl_typ_id => l_get_epe_pl_yr.pl_typ_id,
4626 p_opt_id => l_get_epe_pl_yr.opt_id,
4627 p_ler_id => l_get_epe_pl_yr.ler_id,
4628 p_jurisdiction_code => l_jurisdiction);
4629 p_ann_mn_val := l_outputs(l_outputs.first).value;
4630 end if;
4631 if l_get_cds.prort_mx_ann_elcn_val_cd = 'RL' then
4632 /*
4633 if l_get_asg_info.state is not null then
4634 l_jurisdiction := pay_mag_utils.lookup_jurisdiction_code
4635 (p_state => l_get_asg_info.state);
4636 end if;
4637 */
4638 -- this rule returns an amount.
4639 l_outputs := benutils.formula
4640 (p_formula_id => l_get_cds.prort_mx_ann_elcn_val_rl,
4641 p_effective_date => p_effective_date,
4642 p_business_group_id => l_get_epe_pl_yr.business_group_id,
4643 p_assignment_id => l_get_asg_info.assignment_id,
4644 p_organization_id => l_get_asg_info.organization_id,
4645 p_acty_base_rt_id => p_acty_base_rt_id,
4646 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
4647 p_pgm_id => l_get_epe_pl_yr.pgm_id,
4648 p_pl_id => l_get_epe_pl_yr.pl_id,
4649 p_pl_typ_id => l_get_epe_pl_yr.pl_typ_id,
4650 p_opt_id => l_get_epe_pl_yr.opt_id,
4651 p_ler_id => l_get_epe_pl_yr.ler_id,
4652 p_jurisdiction_code => l_jurisdiction);
4653 p_ann_mx_val := l_outputs(l_outputs.first).value;
4654 end if;
4655 if g_debug then
4656 hr_utility.set_location('p_ann_mn_val '||to_char(p_ann_mn_val)||
4657 ' p_ann_mx_val '||to_char(p_ann_mx_val) , 97);
4658 end if;
4659
4660 p_ann_mn_val := round(p_ann_mn_val,2);
4661 p_ann_mx_val := round(p_ann_mx_val,2);
4662
4663 if g_debug then
4664 hr_utility.set_location('p_ann_mn_val '||to_char(p_ann_mn_val)||
4665 ' p_ann_mx_val '||to_char(p_ann_mx_val) , 98);
4666 hr_utility.set_location('Leaving '||l_package , 99);
4667 end if;
4668 end prorate_min_max;
4669 --
4670 -- 0 - Always refresh
4671 -- 1 - Initialise cache
4672 -- 2 - Cache hit
4673 --
4674 procedure clear_down_cache
4675 is
4676
4677 begin
4678 --
4679 g_period_to_annual_cache.delete;
4680 g_period_to_annual_cached := 1;
4681 --
4682 g_annual_to_period_cache.delete;
4683 g_annual_to_period_cached := 1;
4684 --
4685 end clear_down_cache;
4686 --
4687 procedure set_no_cache_context
4688 is
4689
4690 begin
4691 --
4692 g_period_to_annual_cache.delete;
4693 g_period_to_annual_cached := 0;
4694 --
4695 g_annual_to_period_cache.delete;
4696 g_annual_to_period_cached := 0;
4697 --
4698 end set_no_cache_context;
4699 --
4700 -------------------------------------------------------------------------------
4701 -- Scope of function: Call from Internal procedures allowed.
4702 --
4703 function decde_bits(p_number IN NUMBER) return NUMBER is
4704 /*
4705 ,1,1,2,1,4,1,8,1,16,1,32,1,64,1
4706 ,3,2,5,2,6,2,9,2,10,2,12,2,17,2,18,2,20,2,24,2,33,2,34,2,36,2,40,2,48,2
4707 ,7,3,11,3,14,3,13,3,19,3,21,3,22,3
4708 ,25,3,26,3,28,3,35,3,37,3,38,3,41,3,42,3,44,3,49,3,50,3,52,3,56,3
4709 ,15,4,23,4,27,4,29,4,30,4,39,4,43,4,45,4,46,4,51,4,53,4,54,4,57,4,58,4,60,4
4710 ,31,5,47,5,55,5,59,5,61,5,62,5
4711 ,63,6
4712 ,power(2,count(start_date )) -1)
4713 */
4714 l_number NUMBER;
4715 begin
4716 if
4717 p_number in (1,2,4,8,16,32,64)
4718 then
4719 l_number := 1;
4720 elsif
4721 p_number in (3,5,6,9,10,12,17,18,20,24,33,34,36,40,48)
4722 then
4723 l_number := 2;
4724 elsif
4725 p_number in (15,23,27,29,30,39,43,45,46,51,53,54,57,58,60)
4726 then
4727 l_number := 4;
4728 elsif
4729 p_number in (31,47,55,59,61,62)
4730 then
4731 l_number := 5;
4732 elsif
4733 p_number = 63
4734 then
4735 l_number := 6;
4736 elsif p_number between 1 and 64 then
4737 l_number := 3;
4738 elsif p_number >64 then
4739 l_number :=power(2,p_number -1);
4740 else
4741 l_number := p_number;
4742 end if;
4743 return l_number;
4744 end decde_bits;
4745 --
4746 procedure convert_rates_w(p_person_id in number,
4747 p_amount in number,
4748 p_enrt_rt_id in number default null,
4749 p_elig_per_elctbl_chc_id in number default null,
4750 p_acty_ref_perd_cd in varchar2 default null,
4751 p_cmcd_acty_ref_perd_cd in varchar2 default null,
4752 p_business_group_id in number default null,
4753 p_effective_date in date default null,
4754 p_lf_evt_ocrd_dt in date default null,
4755 p_complete_year_flag in varchar2 default 'N',
4756 p_use_balance_flag in varchar2 default 'N',
4757 p_start_date in date default null,
4758 p_end_date in date default null,
4759 p_payroll_id in number default null,
4760 p_element_type_id in number default null,
4761 p_convert_from_rt in varchar2,
4762 p_ann_rt_val out nocopy number,
4763 p_cmcd_rt_val out nocopy number,
4764 p_val out nocopy number )
4765 is
4766 /*
4767 cursor c_payroll_id is
4768 select payroll_id from
4769 per_all_assignments_f
4770 where person_id = p_person_id
4771 and assignment_type <> 'C'
4772 and p_effective_date between effective_start_date and effective_end_date
4773 and primary_flag = 'Y';
4774 l_payroll_id per_all_assignments_f.payroll_id%type;
4775 */
4776 --GEVITY
4777 cursor c_abr(cv_enrt_rt_id number,cv_effective_date date )
4778 is select abr.rate_periodization_rl, ecr.acty_base_rt_id
4779 from ben_enrt_rt ecr,
4780 ben_acty_base_rt_f abr
4781 where ecr.enrt_rt_id = cv_enrt_rt_id
4782 and abr.acty_base_rt_id = ecr.acty_base_rt_id
4783 and cv_effective_date between abr.effective_start_date
4784 and abr.effective_end_date ;
4785 --
4786 l_rate_periodization_rl NUMBER;
4787 l_acty_base_rt_id NUMBER;
4788 --
4789 cursor c_legislation_code is
4790 select pbg.legislation_code
4791 from per_business_groups pbg
4792 where pbg.business_group_id = p_business_group_id;
4793 --
4794 l_legislation_code varchar2(30);
4795 --
4796 cursor c_ecr_rates is
4797 select mn_elcn_val, mx_elcn_val
4798 from ben_enrt_rt ecr
4799 where ecr.enrt_rt_id = p_enrt_rt_id;
4800 --
4801 l_min_val number;
4802 l_max_val number;
4803 --
4804 l_dfnd_dummy number;
4805 l_ann_dummy number;
4806 l_cmcd_dummy number;
4807 l_assignment_id per_all_assignments_f.assignment_id%type;
4808 l_payroll_id per_all_assignments_f.payroll_id%type;
4809 l_organization_id per_all_assignments_f.organization_id%type;
4810 --GEVITY
4811 not_supported exception;
4812 l_trace_param varchar2(30);
4813 l_trace_on boolean;
4814 l_proc varchar2(200) := 'ben_distribute_rates.convert_rates_w';
4815
4816 begin
4817 /*
4818 if p_payroll_id is null then
4819 open c_payroll_id;
4820 fetch c_payroll_id into l_payroll_id;
4821 close c_payroll_id;
4822 else
4823 l_payroll_id := p_payroll_id;
4824 end if;
4825 */
4826 --GEVITY
4827 -- hr_utility.trace_on(null,'BENDISRT');
4828 l_trace_param := null;
4829 l_trace_on := false;
4830 --
4831 l_trace_param := fnd_profile.value('BEN_SS_TRACE_VALUE');
4832 --
4833 if l_trace_param = 'BENDISRT' then
4834 l_trace_on := true;
4835 else
4836 l_trace_on := false;
4837 end if;
4838 --
4839 if l_trace_on then
4840 hr_utility.trace_on(null,'BENDISRT');
4841 end if;
4842 hr_utility.set_location('l_trace_param : '|| l_trace_param, 5);
4843 hr_utility.set_location ('Entering '||l_proc,10);
4844
4845 open c_abr(p_enrt_rt_id,nvl(p_lf_evt_ocrd_dt,p_effective_date)) ;
4846 fetch c_abr into l_rate_periodization_rl,l_acty_base_rt_id;
4847 close c_abr;
4848 --
4849 ben_element_entry.get_abr_assignment
4850 (p_person_id => p_person_id
4851 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date)
4852 ,p_acty_base_rt_id => l_acty_base_rt_id
4853 ,p_organization_id => l_organization_id
4854 ,p_payroll_id => l_payroll_id
4855 ,p_assignment_id => l_assignment_id
4856 );
4857 --
4858 --END GEVITY
4859 -- Based on the conversion required, need to call different routines.
4860 if p_convert_from_rt = 'ANNUAL'then
4861 -- call the annual to periods function
4862 p_ann_rt_val := p_amount;
4863 --
4864 IF l_rate_periodization_rl IS NOT NULL THEN
4865 --
4866 l_ann_dummy := p_amount;
4867 --
4868 ben_distribute_rates.periodize_with_rule
4869 (p_formula_id => l_rate_periodization_rl
4870 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date)
4871 ,p_assignment_id => l_assignment_id
4872 ,p_convert_from_val => l_ann_dummy
4873 ,p_convert_from => 'ANNUAL'
4874 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
4875 ,p_acty_base_rt_id => l_acty_base_rt_id
4876 ,p_business_group_id => p_business_group_id
4877 ,p_enrt_rt_id => p_enrt_rt_id
4878 ,p_ann_val => p_ann_rt_val
4879 ,p_cmcd_val => p_cmcd_rt_val
4880 ,p_val => p_val
4881 );
4882 --
4883 ELSE
4884 --
4885 p_cmcd_rt_val := annual_to_period
4886 (p_amount =>p_amount,
4887 p_enrt_rt_id =>p_enrt_rt_id,
4888 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
4889 p_acty_ref_perd_cd =>p_cmcd_acty_ref_perd_cd,
4890 p_business_group_id =>p_business_group_id,
4891 p_effective_date =>p_effective_date,
4892 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
4893 p_complete_year_flag =>p_complete_year_flag,
4894 p_use_balance_flag =>p_use_balance_flag,
4895 p_start_date =>p_start_date,
4896 p_end_date =>p_end_date,
4897 p_payroll_id =>l_payroll_id,
4898 p_element_type_id =>p_element_type_id);
4899 --
4900 p_val := annual_to_period
4901 (p_amount =>p_amount,
4902 p_enrt_rt_id =>p_enrt_rt_id,
4903 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
4904 p_acty_ref_perd_cd =>p_acty_ref_perd_cd,
4905 p_business_group_id =>p_business_group_id,
4906 p_effective_date =>p_effective_date,
4907 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
4908 p_complete_year_flag =>p_complete_year_flag,
4909 p_use_balance_flag =>p_use_balance_flag,
4910 p_start_date =>p_start_date,
4911 p_end_date =>p_end_date,
4912 p_payroll_id =>l_payroll_id,
4913 p_element_type_id =>p_element_type_id);
4914 --
4915 END IF; --GEVITY
4916 elsif p_convert_from_rt = 'CMCD'then
4917 -- call the period to annual function
4918 IF l_rate_periodization_rl IS NOT NULL THEN
4919 --
4920 l_cmcd_dummy := p_amount;
4921 --
4922 ben_distribute_rates.periodize_with_rule
4923 (p_formula_id => l_rate_periodization_rl
4924 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date)
4925 ,p_assignment_id => l_assignment_id
4926 ,p_convert_from_val => l_cmcd_dummy
4927 ,p_convert_from => 'CMCD'
4928 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
4929 ,p_acty_base_rt_id => l_acty_base_rt_id
4930 ,p_business_group_id => p_business_group_id
4931 ,p_enrt_rt_id => p_enrt_rt_id
4932 ,p_ann_val => p_ann_rt_val
4933 ,p_cmcd_val => p_cmcd_rt_val
4934 ,p_val => p_val
4935 );
4936 --
4937 ELSE
4938 p_ann_rt_val := period_to_annual
4939 (p_amount =>p_amount,
4940 p_enrt_rt_id =>p_enrt_rt_id,
4941 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
4942 p_acty_ref_perd_cd =>p_cmcd_acty_ref_perd_cd,
4943 p_business_group_id =>p_business_group_id,
4944 p_effective_date =>p_effective_date,
4945 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
4946 p_complete_year_flag =>p_complete_year_flag,
4947 p_use_balance_flag =>p_use_balance_flag,
4948 p_start_date =>p_start_date,
4949 p_end_date =>p_end_date,
4950 p_payroll_id =>l_payroll_id,
4951 p_element_type_id =>p_element_type_id);
4952 -- convert the annual to defined rate
4953 p_val := annual_to_period
4954 (p_amount =>p_ann_rt_val,
4955 p_enrt_rt_id =>p_enrt_rt_id,
4956 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
4957 p_acty_ref_perd_cd =>p_acty_ref_perd_cd,
4958 p_business_group_id =>p_business_group_id,
4959 p_effective_date =>p_effective_date,
4960 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
4961 p_complete_year_flag =>p_complete_year_flag,
4962 p_use_balance_flag =>p_use_balance_flag,
4963 p_start_date =>p_start_date,
4964 p_end_date =>p_end_date,
4965 p_payroll_id =>l_payroll_id,
4966 p_element_type_id =>p_element_type_id);
4967 p_cmcd_rt_val := p_amount;
4968
4969 --start 5460638 : Adjust the defined rate when
4970 --1) It is different from communicated rate
4971 --2) Legislation code is 'US' and
4972 --3) It falls out of min-max window by <= .01
4973 if p_cmcd_rt_val <> p_val then
4974 open c_legislation_code;
4975 fetch c_legislation_code into l_legislation_code;
4976 close c_legislation_code;
4977
4978 if l_legislation_code = 'US' then
4979 open c_ecr_rates;
4980 fetch c_ecr_rates into l_min_val, l_max_val;
4981 close c_ecr_rates;
4982
4983 if l_min_val is not null and p_val < l_min_val and
4984 (l_min_val - p_val) <= 0.01 then
4985 p_val := l_min_val;
4986 hr_utility.set_location('Rounding error adjustment in defined rate. value='|| p_val,15.1);
4987 elsif l_max_val is not null and p_val > l_max_val and
4988 (p_val - l_max_val) <= 0.01 then
4989 p_val := l_max_val;
4990 hr_utility.set_location('Rounding error adjustment in defined rate. value='|| p_val,15.2);
4991 end if;
4992 end if;
4993 end if;
4994 --end 5460638
4995
4996 END IF; --GEVITY
4997 --
4998 elsif p_convert_from_rt = 'DEFINED'then
4999 -- call the period to annual function
5000 IF l_rate_periodization_rl IS NOT NULL THEN
5001 --
5002 l_dfnd_dummy := p_amount;
5003 --
5004 ben_distribute_rates.periodize_with_rule
5005 (p_formula_id => l_rate_periodization_rl
5006 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date)
5007 ,p_assignment_id => l_assignment_id
5008 ,p_convert_from_val => l_dfnd_dummy
5009 ,p_convert_from => 'DEFINED'
5010 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
5011 ,p_acty_base_rt_id => l_acty_base_rt_id
5012 ,p_business_group_id => p_business_group_id
5013 ,p_enrt_rt_id => p_enrt_rt_id
5014 ,p_ann_val => p_ann_rt_val
5015 ,p_cmcd_val => p_cmcd_rt_val
5016 ,p_val => p_val
5017 );
5018 --
5019 ELSE
5020 --
5021 p_ann_rt_val := period_to_annual
5022 (p_amount =>p_amount,
5023 p_enrt_rt_id =>p_enrt_rt_id,
5024 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
5025 p_acty_ref_perd_cd =>p_acty_ref_perd_cd,
5026 p_business_group_id =>p_business_group_id,
5027 p_effective_date =>p_effective_date,
5028 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
5029 p_complete_year_flag =>p_complete_year_flag,
5030 p_use_balance_flag =>p_use_balance_flag,
5031 p_start_date =>p_start_date,
5032 p_end_date =>p_end_date,
5033 p_payroll_id =>l_payroll_id,
5034 p_element_type_id =>p_element_type_id);
5035 -- convert the annual to communicated rate
5036 p_cmcd_rt_val := annual_to_period
5037 (p_amount =>p_ann_rt_val,
5038 p_enrt_rt_id =>p_enrt_rt_id,
5039 p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id,
5040 p_acty_ref_perd_cd =>p_cmcd_acty_ref_perd_cd,
5041 p_business_group_id =>p_business_group_id,
5042 p_effective_date =>p_effective_date,
5043 p_lf_evt_ocrd_dt =>p_lf_evt_ocrd_dt,
5044 p_complete_year_flag =>p_complete_year_flag,
5045 p_use_balance_flag =>p_use_balance_flag,
5046 p_start_date =>p_start_date,
5047 p_end_date =>p_end_date,
5048 p_payroll_id =>l_payroll_id,
5049 p_element_type_id =>p_element_type_id);
5050 p_val := p_amount;
5051 END IF; --GEVITY
5052 else -- this is not supported
5053 raise not_supported;
5054 end if;
5055
5056 hr_utility.set_location ('Leaving '||l_proc,20);
5057 if l_trace_on then
5058 hr_utility.trace_off;
5059 l_trace_param := null;
5060 l_trace_on := false;
5061 end if;
5062 --
5063 exception
5064 when not_supported then
5065 -- This should never happen
5066 if l_trace_on then
5067 hr_utility.trace_off;
5068 l_trace_param := null;
5069 l_trace_on := false;
5070 end if;
5071 raise;
5072 when others then
5073 p_ann_rt_val := null; -- no copy changes
5074 p_cmcd_rt_val := null; -- no copy changes
5075 p_val := null; -- no copy changes
5076 if l_trace_on then
5077 hr_utility.trace_off;
5078 l_trace_param := null;
5079 l_trace_on := false;
5080 end if;
5081
5082 raise;
5083 end convert_rates_w ;
5084 --------------------------------------------------------------------------------------------------------
5085 -- Procedure to re-calculate Child rates also if Parent Rate value is modified
5086 --------------------------------------------------------------------------------------------------------
5087 procedure convert_pcr_rates_w(
5088 p_person_id in number,
5089 p_amount in number,
5090 p_rate_index in number,
5091 p_prnt_acty_base_rt_id in number,
5092 p_enrt_rt_id in number default null,
5093 p_enrt_rt_id2 in number default null,
5094 p_enrt_rt_id3 in number default null,
5095 p_enrt_rt_id4 in number default null,
5096 p_elig_per_elctbl_chc_id in number default null,
5097 p_acty_ref_perd_cd in varchar2 default null,
5098 p_cmcd_acty_ref_perd_cd in varchar2 default null,
5099 p_business_group_id in number default null,
5100 p_effective_date in date default null,
5101 p_lf_evt_ocrd_dt in date default null,
5102 p_use_balance_flag in varchar2 default 'N',
5103 p_start_date in date default null,
5104 p_end_date in date default null,
5105 p_payroll_id in number default null,
5106 p_element_type_id in number default null,
5107 p_convert_from_rt in varchar2,
5108 p_ann_rt_val in out nocopy number,
5109 p_cmcd_rt_val out nocopy number,
5110 p_val out nocopy number,
5111 p_child_rt_flag out nocopy varchar2, --5104247
5112 p_ann_rt_val2 in out nocopy number,
5113 p_cmcd_rt_val2 out nocopy number,
5114 p_val2 out nocopy number,
5115 p_child_rt_flag2 out nocopy varchar2,
5116 p_ann_rt_val3 in out nocopy number,
5117 p_cmcd_rt_val3 out nocopy number,
5118 p_val3 out nocopy number,
5119 p_child_rt_flag3 out nocopy varchar2,
5120 p_ann_rt_val4 in out nocopy number,
5121 p_cmcd_rt_val4 out nocopy number,
5122 p_val4 out nocopy number,
5123 p_child_rt_flag4 out nocopy varchar2 ) is
5124 --
5125 --If the rate setup is either Enter annual value or Set Annual Rate Equal to coverage we calculate the
5126 --communicated amount and defined amount for that Rate and its child rates based on the
5127 --remaining pay periods in the year.
5128 --
5129 cursor c_cmplt_year is
5130 Select 'N'
5131 From ben_acty_base_rt_f
5132 Where acty_base_rt_id=p_prnt_acty_base_rt_id
5133 and p_effective_date between effective_start_date and effective_end_date
5134 and (nvl(rt_mlt_cd,'XX')='SAREC' or entr_ann_val_flag='Y');
5135 --
5136 -- Find if enrt_rt_id is child of parent rate
5137 --
5138 cursor csr_is_child(c_enrt_rate_id number) is
5139 select
5140 abr.val
5141 ,abr.RT_TYP_CD
5142 ,ecr.cmcd_acty_ref_perd_cd
5143 from ben_acty_base_rt_f abr,
5144 ben_enrt_rt ecr
5145 where
5146 abr.PARNT_ACTY_BASE_RT_ID= p_prnt_acty_base_rt_id
5147 and abr.rt_mlt_cd='PRNT'
5148 and abr.ACTY_BASE_RT_ID = ecr.ACTY_BASE_RT_ID
5149 and ecr.enrt_rt_id=c_enrt_rate_id
5150 and p_effective_date between effective_start_date and effective_end_date;
5151 --
5152 -- Local variables
5153 --
5154 l_enrt_rt_id number;
5155 l_ann_rt_val number;
5156 l_cmcd_rt_val number;
5157 l_rt_val number;
5158 l_prnt_rt_value number;
5159 l_chld_rt_value number;
5160
5161 l_operand number;
5162 l_operator varchar2(30);
5163 l_complete_year_flag varchar2(10);
5164 l_use_balance_flag varchar2(10) :='N';
5165 l_prnt_cmplt_year varchar2(10);
5166 l_cmcd_perd_cd ben_enrt_rt.cmcd_acty_ref_perd_cd%TYPE;
5167
5168 l_trace_param varchar2(30);
5169 l_trace_on boolean;
5170 l_proc varchar2(200) := 'ben_distribute_rates.convert_pcr_rates_w';
5171 --
5172 BEGIN
5173 -- hr_utility.trace_on(null,'BENDISRT');
5174 l_trace_param := null;
5175 l_trace_on := false;
5176 --
5177 l_trace_param := fnd_profile.value('BEN_SS_TRACE_VALUE');
5178 --
5179 if l_trace_param = 'BENDISRT' then
5180 l_trace_on := true;
5181 else
5182 l_trace_on := false;
5183 end if;
5184 --
5185 if l_trace_on then
5186 hr_utility.trace_on(null,'BENDISRT');
5187 end if;
5188 --
5189 hr_utility.set_location('l_trace_param : '|| l_trace_param, 5);
5190 hr_utility.set_location ('Entering '||l_proc,10);
5191 --
5192 OPEN c_cmplt_year;
5193 Fetch c_cmplt_year into l_complete_year_flag;
5194 if c_cmplt_year%NOTFOUND
5195 THEN
5196 l_complete_year_flag :='Y';
5197 end if;
5198 l_prnt_cmplt_year :='Y';
5199 if p_convert_from_rt = 'ANNUAL' AND l_complete_year_flag='N' then
5200 l_prnt_cmplt_year :='N';
5201 end if;
5202
5203 if p_rate_index =1 THEN l_enrt_rt_id :=p_enrt_rt_id;
5204 elsif p_rate_index =2 THEN l_enrt_rt_id :=p_enrt_rt_id2;
5205 elsif p_rate_index =3 THEN l_enrt_rt_id :=p_enrt_rt_id3;
5206 elsif p_rate_index =4 THEN l_enrt_rt_id :=p_enrt_rt_id4;
5207 end if;
5208 convert_rates_w(p_person_id => p_person_id,
5209 p_amount => p_amount,
5210 p_enrt_rt_id => l_enrt_rt_id,
5211 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
5212 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
5213 p_cmcd_acty_ref_perd_cd => p_cmcd_acty_ref_perd_cd,
5214 p_business_group_id => p_business_group_id,
5215 p_effective_date => p_effective_date,
5216 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
5217 p_complete_year_flag => l_prnt_cmplt_year,
5218 p_use_balance_flag => p_use_balance_flag,
5219 p_start_date => p_start_date,
5220 p_end_date => p_end_date ,
5221 p_payroll_id => p_payroll_id ,
5222 p_element_type_id => p_element_type_id,
5223 p_convert_from_rt => p_convert_from_rt ,
5224 p_ann_rt_val => l_ann_rt_val,
5225 p_cmcd_rt_val => l_cmcd_rt_val ,
5226 p_val => l_rt_val);
5227 if p_rate_index =1 THEN
5228 p_ann_rt_val := l_ann_rt_val;
5229 p_cmcd_rt_val := l_cmcd_rt_val;
5230 p_val := l_rt_val;
5231 elsif p_rate_index =2 THEN
5232 p_ann_rt_val2 := l_ann_rt_val;
5233 p_cmcd_rt_val2 := l_cmcd_rt_val;
5234 p_val2 := l_rt_val;
5235 elsif p_rate_index =3 THEN
5236 p_ann_rt_val3 := l_ann_rt_val;
5237 p_cmcd_rt_val3 := l_cmcd_rt_val;
5238 p_val3 := l_rt_val;
5239 elsif p_rate_index =4 THEN
5240 p_ann_rt_val4 := l_ann_rt_val;
5241 p_cmcd_rt_val4 := l_cmcd_rt_val;
5242 p_val4 := l_rt_val;
5243 end if;
5244 l_prnt_rt_value :=l_ann_rt_val;
5245 --
5246 -- use balances only if parent is SAAEAR or SAREC
5247 --
5248 if l_complete_year_flag='N' then l_use_balance_flag :='Y'; end if;
5249
5250 -- 5104247 Set the child rate flags to 'N' by default
5251 p_child_rt_flag := 'N';
5252 p_child_rt_flag2 := 'N';
5253 p_child_rt_flag3 := 'N';
5254 p_child_rt_flag4 := 'N';
5255
5256 --
5257 -- We have right now only four rates displayed in SSBEN
5258 --
5259 For i in 1..4
5260 LOOP
5261 IF i <> p_rate_index THEN
5262 if i =1 THEN l_enrt_rt_id :=p_enrt_rt_id;
5263 elsif i =2 THEN l_enrt_rt_id :=p_enrt_rt_id2;
5264 elsif i =3 THEN l_enrt_rt_id :=p_enrt_rt_id3;
5265 elsif i =4 THEN l_enrt_rt_id :=p_enrt_rt_id4;
5266 end if;
5267
5268 --
5269 --If this rate is null, that means we have no further rates
5270 --
5271 if l_enrt_rt_id is null then exit; end if;
5272 OPEN csr_is_child(l_enrt_rt_id);
5273 FETCH csr_is_child into l_operand,l_operator,l_cmcd_perd_cd;
5274 IF csr_is_child%FOUND THEN
5275 benutils.rt_typ_calc
5276 (p_rt_typ_cd => l_operator
5277 ,p_val => l_operand
5278 ,p_val_2 => l_prnt_rt_value
5279 ,p_calculated_val => l_chld_rt_value);
5280 convert_rates_w(
5281 p_person_id =>p_person_id,
5282 p_amount => l_chld_rt_value,
5283 p_enrt_rt_id => l_enrt_rt_id,
5284 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
5285 p_acty_ref_perd_cd => p_acty_ref_perd_cd,
5286 p_cmcd_acty_ref_perd_cd => l_cmcd_perd_cd,
5287 p_business_group_id => p_business_group_id,
5288 p_effective_date => p_effective_date,
5289 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
5290 p_complete_year_flag => l_complete_year_flag,
5291 p_use_balance_flag => l_use_balance_flag,
5292 p_start_date => p_start_date,
5293 p_end_date => p_end_date,
5294 p_payroll_id => p_payroll_id,
5295 p_element_type_id => p_element_type_id,
5296 p_convert_from_rt => 'ANNUAL',
5297 p_ann_rt_val => l_ann_rt_val,
5298 p_cmcd_rt_val => l_cmcd_rt_val,
5299 p_val => l_rt_val);
5300 if i =1 THEN
5301 p_ann_rt_val := l_ann_rt_val;
5302 p_cmcd_rt_val := l_cmcd_rt_val;
5303 p_val := l_rt_val;
5304 p_child_rt_flag := 'Y'; -- 5104247
5305 elsif i=2 THEN
5306 p_ann_rt_val2 := l_ann_rt_val;
5307 p_cmcd_rt_val2 := l_cmcd_rt_val;
5308 p_val2 := l_rt_val;
5309 p_child_rt_flag2 := 'Y';
5310 elsif i =3 THEN
5311 p_ann_rt_val3 := l_ann_rt_val;
5312 p_cmcd_rt_val3 := l_cmcd_rt_val;
5313 p_val3 := l_rt_val;
5314 p_child_rt_flag3 := 'Y';
5315 elsif i =4 THEN
5316 p_ann_rt_val4 := l_ann_rt_val;
5317 p_cmcd_rt_val4 := l_cmcd_rt_val;
5318 p_val4 := l_rt_val;
5319 p_child_rt_flag4 := 'Y';
5320 end if;
5321 END IF; -- cursor found
5322 CLOSE csr_is_child;
5323 END IF; -- i <> p_rate_index
5324 END LOOP;
5325 --
5326 hr_utility.set_location ('Leaving '||l_proc,20);
5327 if l_trace_on then
5328 hr_utility.trace_off;
5329 l_trace_param := null;
5330 l_trace_on := false;
5331 end if;
5332 --
5333 EXCEPTION
5334 When others then
5335 p_ann_rt_val := null; -- no copy changes
5336 p_cmcd_rt_val := null; -- no copy changes
5337 p_val := null;
5338 p_ann_rt_val2 := null; -- no copy changes
5339 p_cmcd_rt_val2 := null; -- no copy changes
5340 p_val2 := null;
5341 p_ann_rt_val3 := null; -- no copy changes
5342 p_cmcd_rt_val3 := null; -- no copy changes
5343 p_val3 := null;
5344 p_ann_rt_val4 := null; -- no copy changes
5345 p_cmcd_rt_val4 := null; -- no copy changes
5346 p_val4 := null;
5347 if l_trace_on then
5348 hr_utility.trace_off;
5349 l_trace_param := null;
5350 l_trace_on := false;
5351 end if;
5352 raise;
5353 END convert_pcr_rates_w;
5354
5355 end ben_distribute_rates;
[Home] [Help]