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