1 package body pay_gb_nicar_06042002 as
2 /* $Header: pygbncpl.pkb 115.21 2003/01/15 09:38:10 mmahmad noship $
3 ******************************************************************
4 * *
5 * Copyright (C) 1989 Oracle Corporation UK Ltd., *
6 * Richmond, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21
22 Name : pay_gb_nicar_2002
23
24 Description : This package contains calculations for use in processing
25 of NI Car Primary and Secondary elements
26
27 Uses :
28
29 Used By : NI Car fast formulas for tax year 2002/3
30
31
32 Change List :
33
34 Version Date Author Description
35 ------- ----- -------- ----------------
36
37 115.0 20/8/01 GBUTLER Created
38 115.1 30/9/01 GBUTLER Commented out trace_on
39 and trace_Off commands
40 115.2 5/10/01 GBUTLER Changed pro-ration aspects
41 on benefit charges and fuel
42 scale
43 115.3 8/11/01 GBUTLER Check on CO2 emissions for
44 petrol/diesel & bi-fuel conversion
45 cars - if emissions figure
46 entered below min level for year, level
47 adjusted up to match min level.
48 115.4 9/11/01 GBUTLER Included fix for potential week 53 problem
49 when comparing current payroll period to
50 number of payroll periods in year
51 115.5 4/12/01 vmkhande Modified some functions
52 such that they can be used to
53 do the calculation for P11D element
54 verifications
55 115.9 15/1/02 GBUTLER Bug 2142983. Added nicar_main procedure to
56 deal with date-tracked updates to car
57 element entries and modified nicar_nicable_
58 value_CO2 and nicar_nicable_value_non_CO2 to
59 be called from nicar_main. Fast formulas for
60 NI Car will now call nicar_main directly
61 instead of calling the other 2 procedures as
62 happened previously. Also removed specific
63 parameters for P11d and get_next_pay_date function as
64 no longer required. Changed code for deduction of
65 employee contributions (payments)
66 115.10 11/4/02 GBUTLER Put nvl() around condition which determines which
67 calculation procedure to call to deal with cars registered
68 before 01-JAN-1998 with no fuel type attached. Also included
69 defaults for engine size where required so that auto
70 calculation of fuel scale charges work correctly. Bugs 2281529
71 and 2279049. Changed fuel default to diesel.
72 Added functionality to process Euro IV diesel cars
73 115.11 24/4/02 GBUTLER Updated handling of messages to provide more logical approach.
74 Moved defaulting of fuel type and engine size to nicar_main
75 function.
76 115.12 10/5/02 GBUTLER Replaced p_message parameter in nicar_nicable_value_non_co2
77 function as parameter required by p11d packages that call
78 this function. Added l_cc_message in nicar_main to capture
79 output from p_message even though output will be null.
80 This fix for 11i only (not required on 11 or 10.7)
81 115.13 03/7/02 GBUTLER Bug 2444082. Added functionality to annualise period
82 numbers for non-standard payrolls (e.g. lunar) so that
83 comparison between current payroll period number and
84 payroll periods per year is effective when determining
85 whether to use tax year end date or current payroll period
86 end date in calculation.
87 115.14 13/11/02 RMAKHIJA Legislative changes to fuel scale charge for
88 tax year 2003-2004
89 115.15 29/11/02 RMAKHIJA Commented Out trace on and off
90 115.16 29/11/02 RMAKHIJA Added WHWENVER OSERROR command at the top to fix
91 GSCC warning.
92 115.17 09/12/02 RMAKHIJA Changed to count 29-FEB in calculation for leap years
93 115.18 09/12/02 RMAKHIJA Added NOCOPY to out parameters to fix GSCC warning
94 115.19 12/12/02 RMAKHIJA Added g_last_opt_out_date and csr_last_opt_out_date cursor
95 to make sure fuel scale charge is not calculated for the
96 last opted out period if the employee remains opted out.
97 115.20 07/01/03 MMAHMAD Added HYBRID ELECTRIC in the IN clause for the calculation
98 of fuel charge.
99 115.21 15/01/03 MMAHMAD Added a call for the get_CO2_percentage function to calculate
100 CO2 percentage for HYBRID ELECTRIC, LPG_CNG, LPG_CNG_Petrol and
101 LPG_CNG_Petrol_Conv fuel types.
102 */
103
104 g_package_name VARCHAR2(21) := 'PAY_GB_NICAR_06042002';
105
106 g_ignore_fuel_opt_out VARCHAR2(1) := 'N';
107 g_last_opt_out_date date := to_date('01-01-0001', 'DD-MM-YYYY');
108 g_tax_year_start date := to_date('06-04-2002', 'DD-MM-YYYY');
109 g_tax_year_end date := to_date('05-04-2003', 'DD-MM-YYYY');
110 g_days_in_year number := 365;
111
112 /* New NI Car Fast formula determines whether or not calculation of taxable / NICable */
113 /* value will be based on CO2 emissions or engine capacity - appropriate function is */
114 /* then called here */
115
116
117 /* Function to calculate taxable / NICable value based on CO2 emissions data for car */
118 /* Parameters p_business_group_id, p_assignment_id, p_element_type_id provided by */
119 /* context-set variables */
120 function nicar_nicable_value_CO2
121 ( p_assignment_id IN NUMBER,
122 p_element_type_id IN NUMBER,
123 p_business_group_id IN NUMBER,
124 /* Import direct from fast formula */
125 p_car_price IN NUMBER,
126 p_reg_date IN DATE,
127 p_fuel_type IN VARCHAR2,
128 p_engine_size IN NUMBER,
129 p_fuel_scale IN NUMBER DEFAULT NULL,
130 p_payment IN NUMBER,
131 p_CO2_emissions IN NUMBER DEFAULT NULL,
132 p_start_date IN DATE,
133 p_end_date IN DATE,
134 p_end_of_period_date IN DATE,
135 p_emp_term_date IN DATE,
136 p_session_date IN DATE,
137 p_message OUT NOCOPY VARCHAR2,
138 p_number_of_days IN NUMBER DEFAULT 0)
139 return NUMBER is
140
141 l_function_name VARCHAR2(23) := 'nicar_nicable_value_CO2';
142
143 l_CO2_emissions NUMBER;
144 l_percentage NUMBER;
145
146 l_difference NUMBER;
147
148 l_fixed_discount NUMBER;
149 l_extra_discount NUMBER;
150 l_discount_total NUMBER;
151
152 l_diesel_supplement NUMBER;
153
154 l_benefit_charge NUMBER;
155
156 l_car_price NUMBER;
157
158 l_min_qual_level NUMBER;
159 l_max_level NUMBER;
160
161 l_min_percentage NUMBER;
162 l_max_percentage NUMBER;
163
164 l_reg_date DATE;
165
166 l_start_date DATE;
167 l_end_date DATE;
168
169 l_end_of_period_date DATE;
170
171 l_price_cap NUMBER;
172
173 l_days_available NUMBER;
174
175 l_fuel_scale NUMBER;
176
177 l_fuel_type VARCHAR2(100);
178
179
180
181
182 cursor csr_min_qual_level is
183 select min(pur.row_low_range_or_name)
184 from pay_user_rows_f pur,
185 pay_user_tables put
186 where put.user_table_id = pur.user_table_id
187 and put.user_table_name = 'GB_CO2_EMISSIONS'
188 and p_session_date between
189 pur.effective_start_date and pur.effective_end_date;
190
191 cursor csr_max_level is
192 select max(pur.row_low_range_or_name)
193 from pay_user_rows_f pur,
194 pay_user_tables put
195 where put.user_table_id = pur.user_table_id
196 and put.user_table_name = 'GB_CO2_EMISSIONS'
197 and p_session_date between
198 pur.effective_start_date and pur.effective_end_date;
199
200
201 cursor csr_min_percentage is
202 select min(value)
203 from pay_user_column_instances_f puci,
204 pay_user_columns puc
205 where puc.user_column_id = puci.user_column_id
206 and puc.user_table_id =
207 (select user_table_id
208 from pay_user_tables
209 where user_table_name = 'GB_CO2_EMISSIONS')
210 and p_session_date between
211 puci.effective_start_date and puci.effective_end_date;
212
213
214 cursor csr_max_percentage is
215 select max(value)
216 from pay_user_column_instances_f puci,
217 pay_user_columns puc
218 where puc.user_column_id = puci.user_column_id
219 and puc.user_table_id =
220 (select user_table_id
221 from pay_user_tables
222 where user_table_name = 'GB_CO2_EMISSIONS')
223 and p_session_date between
224 puci.effective_start_date and puci.effective_end_date;
225
226
227 cursor csr_max_price_global is
228 select to_number(global_value)
229 from ff_globals_f
230 where global_name = 'NI_CAR_MAX_PRICE'
231 and p_session_date between
232 effective_start_date and effective_end_date;
233
234
235
236
237 begin
238
239
240
241 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,1);
242
243 hr_utility.trace('** Parameter values ** ');
244 hr_utility.trace('p_assignment_id: '||p_assignment_id);
245 hr_utility.trace('p_element_type_id: '||p_element_type_id);
246 hr_utility.trace('p_business_group_id: '||p_business_group_id);
247 hr_utility.trace('p_car_price: '||p_car_price);
248 hr_utility.trace('p_reg_date: '||p_reg_date);
249 hr_utility.trace('p_fuel_type: '||p_fuel_type);
250 hr_utility.trace('p_engine_size: '||p_engine_size);
251 hr_utility.trace('p_fuel_scale: '||p_fuel_scale);
252 hr_utility.trace('p_payment: '||p_payment);
253 hr_utility.trace('p_CO2_emissions: '||p_CO2_emissions);
254 hr_utility.trace('p_start_date: '||p_start_date);
255 hr_utility.trace('p_end_date: '||p_end_date);
256 hr_utility.trace('p_end_of_period_date: '||p_end_of_period_date);
257 hr_utility.trace('p_emp_term_date: '||p_emp_term_date);
258 hr_utility.trace('p_session_date: '||p_session_date);
259
260 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,2);
261
262
263 l_start_date := trunc(p_start_date);
264 l_end_date := trunc(p_end_date);
265 l_reg_date := trunc(p_reg_date);
266 l_end_of_period_date := trunc(p_end_of_period_date);
267
268 l_car_price := p_car_price;
269 l_fuel_type := p_fuel_type;
270
271
272
273 hr_utility.trace('l_start_date: '||l_start_date);
274 hr_utility.trace('l_end_date: '||l_end_date);
275 hr_utility.trace('l_reg_date: '||l_reg_date);
276 hr_utility.trace('l_end_of_period_date: '||l_end_of_period_date);
277 hr_utility.trace('p_session_date: '||p_session_date);
278 hr_utility.trace('l_fuel_type: '||l_fuel_type);
279
280 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,4);
281
282 /* Check if CO2 emissions figure is a multiple of 5, if not round down to nearest 5 */
283 l_CO2_emissions := p_CO2_emissions;
284
285 if mod(l_CO2_emissions,5) <> 0
286 then
287
288 l_CO2_emissions := round_CO2_val(l_CO2_emissions);
289
290 end if;
291
292 hr_utility.trace('l_CO2_emissions: '||l_CO2_emissions);
293
294 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,5);
295
296 /* Get values for minimum and maximum percentage rates */
297
298 open csr_min_percentage;
299 fetch csr_min_percentage into l_min_percentage;
300 close csr_min_percentage;
301
302 hr_utility.trace('l_min_percentage: '||l_min_percentage);
303
304
305 open csr_max_percentage;
306 fetch csr_max_percentage into l_max_percentage;
307 close csr_max_percentage;
308
309 hr_utility.trace('l_max_percentage: '||l_max_percentage);
310
311 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,6);
312
313
314 /* Determine percentage rate for car based on fuel type and emissions data */
315
316 if l_fuel_type = 'BATTERY_ELECTRIC' -- battery electric
317 then
318 /* No CO2 emissions figure, deduct fixed discount from minimum percentage rate */
319
320 l_percentage := l_min_percentage - get_discount(l_fuel_type,p_session_date);
321 hr_utility.trace('l_percentage: '||l_percentage);
322
323 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,7);
324
325 else
326 /* All other fuel types have CO2 emissions rating, find applicable percentage rate */
327 /* Calculation of discounts and percentage rates vary according to fuel type, check each in sequence */
328
329 if l_fuel_type in ('HYBRID_ELECTRIC','LPG_CNG') -- hybrid electric or LPG/CNG
330 then
331
332 /* find minumum qualifying level for year, check if car CO2 rating on or below that level */
333 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,8);
334
335 open csr_min_qual_level;
336 fetch csr_min_qual_level into l_min_qual_level;
337 close csr_min_qual_level;
338
339 hr_utility.trace('l_min_qual_level: '||l_min_qual_level);
340 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,9);
341
342 if l_CO2_emissions < l_min_qual_level
343 then
344 /* Extra discount for every full 20 g/km below min qualifying level */
345
346 l_difference := l_min_qual_level - l_CO2_emissions;
347 l_extra_discount := trunc((l_difference/20),0);
348 l_CO2_emissions := l_min_qual_level;
349
350 else
351 /* No extra discount if car CO2 rating same as min qualifying level */
352
353 l_extra_discount := 0;
354
355 end if;
356
357 /* Check that CO2 level entered does not exceed maximum level for year */
358 /* If it does, adjust it down to match maximum level */
359
360 open csr_max_level;
361 fetch csr_max_level into l_max_level;
362 close csr_max_level;
363
364 if l_co2_emissions > l_max_level
365 then
366
367 hr_utility.trace('Car CO2 emissions above max level for year, adjusting down');
368 l_co2_emissions := l_max_level;
369 p_message := p_message||'CO2 emissions adjusted down to max level.';
370
371 end if;
372
373 l_percentage := get_CO2_percentage(p_business_group_id,l_co2_emissions,p_session_date);
377 hr_utility.trace('l_extra_discount: '||l_extra_discount);
374 hr_utility.trace('l_percentage: '||l_percentage);
375
376
378 /* Fixed discount varies according to fuel type */
379
380 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,10);
381
382 l_fixed_discount := get_discount(l_fuel_type,p_session_date);
383
384 hr_utility.trace('l_fixed_discount: '||l_fixed_discount);
385 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,11);
386
387 /* Calculate final discount amount, subtract from minimum percentage amount */
388
389 l_discount_total := l_fixed_discount + l_extra_discount;
390 l_percentage := l_percentage - l_discount_total;
391
392
393 hr_utility.trace('l_percentage: '||l_percentage);
394 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,12);
395
396 elsif l_fuel_type in ('LPG_CNG_PETROL', 'LPG_CNG_PETROL_CONV') -- bi-fuel (built as bi-fuel and converted)
397 then
398
399 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,13);
400
401 /* Calculation based on whether car built as bi-fuel or later converted */
402 /* Check when car first registered as calculation will differ based on registration date */
403
404 if ( l_fuel_type = 'LPG_CNG_PETROL' -- Bi-fuel (not converted)
405 and l_reg_date >= to_date('01/01/2000','DD/MM/YYYY')) -- registered on/after Jan 1 2000
406 or l_fuel_type = 'LPG_CNG_PETROL_CONV'
407 then
408
409 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,14);
410 /* find minumum qualifying level for year, check if car CO2 rating on or below that level */
411
412 open csr_min_qual_level;
413 fetch csr_min_qual_level into l_min_qual_level;
414 close csr_min_qual_level;
415
416 hr_utility.trace('l_min_qual_level: '||l_min_qual_level);
417 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,15);
418
419 if l_CO2_emissions < l_min_qual_level
420 then
421 /* Extra discount for every full 20 g/km below min qualifying level */
422
423 l_difference := l_min_qual_level - l_CO2_emissions;
424 l_extra_discount := trunc((l_difference/20),0);
425 l_CO2_emissions := l_min_qual_level;
426
427 else
428 /* No extra discount if car CO2 rating same as min qualifying level */
429
430 l_extra_discount := 0;
431
432 end if;
433
434 /* Check that CO2 level entered does not exceed maximum level for year */
435 /* If it does, adjust it down to match maximum level */
436
437 open csr_max_level;
438 fetch csr_max_level into l_max_level;
439 close csr_max_level;
440
441 if l_co2_emissions > l_max_level
442 then
443
444 hr_utility.trace('Car CO2 emissions above max level for year, adjusting down');
445 l_co2_emissions := l_max_level;
446 p_message := p_message||'CO2 emissions adjusted down to max level.';
447
448 end if;
449
450 l_percentage := get_CO2_percentage(p_business_group_id,l_co2_emissions,p_session_date);
451 hr_utility.trace('l_percentage: '||l_percentage);
452
453 hr_utility.trace('l_extra_discount: '||l_extra_discount);
454
455 /* Find fixed discount */
456
457 l_fixed_discount := get_discount(l_fuel_type,p_session_date);
458
459 hr_utility.trace('l_fixed_discount: '||l_fixed_discount);
460 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,16);
461
462 /* Calculate final discount amount, subtract from minimum percentage amount */
463 l_discount_total := l_fixed_discount + l_extra_discount;
464 l_percentage := l_percentage - l_discount_total;
465
466 hr_utility.trace('l_percentage: '||l_percentage);
467 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,17);
468
469 /* Conversion bi-fuel cars and bi-fuel cars registered before Jan 1 2000 */
470
471 elsif (l_fuel_type = 'LPG_CNG_PETROL' and l_reg_date < to_date('01/01/2000','DD/MM/YYYY'))
472 then
473
474 p_message := 'Bi-fuel car calculated as if converted to bi-fuel.';
475 hr_utility.trace('Calculating bi-fuel car as if converted');
476
477 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,18);
478
479 /* get percentage rating based on car's actual CO2 emissions */
480 /* if CO2 level below minimum level for year, adjust up to match minimum level */
481
482 open csr_min_qual_level;
483 fetch csr_min_qual_level into l_min_qual_level;
484 close csr_min_qual_level;
485
486 if l_co2_emissions < l_min_qual_level
487 then
488
489 hr_utility.trace('Car CO2 emissions below min level for year, adjusting up');
490 l_co2_emissions := l_min_qual_level;
491 p_message := p_message||'CO2 emissions adjusted up to min level.';
492
493 end if;
494
495 /* Check that CO2 level entered does not exceed maximum level for year */
496 /* If it does, adjust it down to match maximum level */
497
498
502
499 open csr_max_level;
500 fetch csr_max_level into l_max_level;
501 close csr_max_level;
503 if l_co2_emissions > l_max_level
504 then
505
506 hr_utility.trace('Car CO2 emissions above max level for year, adjusting down');
507 l_co2_emissions := l_max_level;
508 p_message := p_message||'CO2 emissions adjusted down to max level.';
509
510 end if;
511
512 l_percentage := get_CO2_percentage(p_business_group_id,
513 l_co2_emissions,
514 p_session_date);
515
516
517 hr_utility.trace('l_percentage: '||l_percentage);
518 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,19);
519
520 /* fixed discount of 1% for this fuel type */
521
522 l_fixed_discount := get_discount(l_fuel_type,p_session_date);
523 hr_utility.trace('l_fixed_discount: '||l_fixed_discount);
524
525
526 l_percentage := l_percentage - l_fixed_discount;
527
528
529 end if;
530
531
532 elsif l_fuel_type in ('PETROL','DIESEL','EURO_IV_DIESEL') -- petrol, diesel, Euro IV diesel for compliant cars
533 then
534
535 /* Find appropriate percentage charge for CO2 emissions rating */
536
537 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,20);
538
539 /* if CO2 level below minimum level for year, adjust up to match minimum level */
540
541 open csr_min_qual_level;
542 fetch csr_min_qual_level into l_min_qual_level;
543 close csr_min_qual_level;
544
545 if l_co2_emissions < l_min_qual_level
546 then
547
548 hr_utility.trace('Car CO2 emissions below min level for year, adjusting up');
549 l_co2_emissions := l_min_qual_level;
550 p_message := 'CO2 emissions adjusted up to min level.';
551
552 end if;
553
554 /* Check that CO2 level entered does not exceed maximum level for year */
555 /* If it does, adjust it down to match maximum level */
556
557
558 open csr_max_level;
559 fetch csr_max_level into l_max_level;
560 close csr_max_level;
561
562 if l_co2_emissions > l_max_level
563 then
564
565 hr_utility.trace('Car CO2 emissions above max level for year, adjusting down');
566 l_co2_emissions := l_max_level;
567 p_message := 'CO2 emissions adjusted down to max level.';
568
569 end if;
570
571 l_percentage := get_CO2_percentage(p_business_group_id,
572 l_co2_emissions,
573 p_session_date);
574
575 hr_utility.trace('l_percentage: '||l_percentage);
576 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,21);
577
578 /* Extra supplements apply if fuel type is diesel */
579 /* Supplement varies according to percentage rate */
580
581 if l_fuel_type = 'DIESEL'
582 then
583
584 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,22);
585
586 if (l_percentage >= l_min_percentage and l_percentage <= 32)
587 then
588
589 l_diesel_supplement := 3;
590
591 elsif (l_percentage = 33)
592 then
593
594 l_diesel_supplement := 2;
595
596 elsif (l_percentage = 34)
597 then
598
599 l_diesel_supplement := 1;
600
601 else l_diesel_supplement := 0;
602
603 end if;
604
605 hr_utility.trace('l_diesel_supplement: '||l_diesel_supplement);
606 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,23);
607
608 l_percentage := l_percentage + l_diesel_supplement;
609
610 end if;
611
612 end if;
613
614 end if;
615
616
617 /* Calculate final car benefit charge based on car list price and percentage rate */
618
619 l_benefit_charge := l_car_price * (l_percentage/100);
620
621 hr_utility.trace('l_benefit_charge: '||l_benefit_charge);
622 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,24);
623
624 IF p_element_type_id IS NOT NULL
625 THEN
626
627
628 /* Find number of days for which car is available to employee */
629 /* Need to find 1) greatest between start date of NI Car element entry or start of tax year */
630 /* 2) least between entry end date, payroll period/ tax year end date, employee termination date */
631 /* Relevant dates set above */
632
633
634 hr_utility.trace('l_start_date: '||l_start_date);
635 hr_utility.trace('l_end_date: '||l_end_date);
636
637 IF g_tax_year_start >= to_date('06-04-2003', 'DD-MM-YYYY') THEN -- Effective from year 2003-04
638 l_days_available := trunc(l_end_date) - trunc(l_start_date) + 1; -- use actual number of days
639 ELSE
640 l_days_available := hr_gbnicar.nicar_days_between(l_start_date,l_end_date); -- ignore 29-FEB
641 END IF;
642
643 ELSE
644
645 l_days_available := p_number_of_days;
646
647 END IF;
648
649 hr_utility.trace('l_days_available: '||l_days_available);
650 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,25);
651
655
652 /* Deduct annual payment from annual benefit charge */
653
654 hr_utility.trace('p_payment: '||p_payment);
656 l_benefit_charge := l_benefit_charge - p_payment;
657
658
659 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,28);
660
661 /* If result < 0, then benefit charge will be 0 */
662 if l_benefit_charge < 0
663 then
664
665 l_benefit_charge := 0;
666 hr_utility.trace('Benefit charge reset to 0');
667
668 end if;
669
670 hr_utility.trace('l_benefit_charge (after payment): '||l_benefit_charge);
671
672 /* Pro-rate the benefit charge, based on the number of days available */
673
674 l_benefit_charge := (l_benefit_charge / g_days_in_year) * l_days_available;
675 hr_utility.trace('l_benefit_charge (pro-rated): '||l_benefit_charge);
676 hr_utility.trace('g_days_in_year = '||g_days_in_year);
677 hr_utility.trace('p_start_date: '||p_start_date);
678 hr_utility.trace('p_end_date: '||p_end_date);
679 hr_utility.trace('p_assignment_id = '||p_assignment_id);
680 hr_utility.trace('p_element_type_id = '||p_element_type_id);
681 hr_utility.trace('l_percentage = '||l_percentage);
682 hr_utility.trace('l_days_available = '||l_days_available);
683 hr_utility.trace('g_ignore_fuel_opt_out = '||g_ignore_fuel_opt_out);
684 hr_utility.trace('g_last_opt_out_date = '||to_char(g_last_opt_out_date, 'DD/MM/YYYY'));
685 hr_utility.trace('l_fuel_type = '||l_fuel_type);
686
687 IF p_element_type_id IS NOT NULL
688 THEN
689
690 /* Add on any fuel scale charges after pro-ration */
691 /* Use manually entered value if exists, else retrieve value from user table */
692 /* based on engine size and fuel type */
693 /* Value of p_fuel_scale parameter will be null unless overriden */
694 hr_utility.trace('p_fuel_scale: '||p_fuel_scale);
695
696 /* Check inputs to see if fuel scale user table should be queried or not */
697
698
699 /* if p_fuel_scale > 0 then user-entered input */
700 if (p_fuel_scale > 0)
701 then
702
703 l_fuel_scale := p_fuel_scale;
704
705 /* Pro-rate fuel scale according to total days available */
706
707 l_fuel_scale := (l_fuel_scale / g_days_in_year) * l_days_available;
708
709 /* if fuel scale entry is 0, user wants fuel scale calculated automatically */
710 /* check fuel type and visit user table if required */
711 elsif (p_fuel_scale = 0 or (p_fuel_scale is null and g_ignore_fuel_opt_out = 'Y' and p_start_date < g_last_opt_out_date))
712 then
713 if l_fuel_type in ('HYBRID_ELECTRIC','PETROL','DIESEL','LPG_CNG','LPG_CNG_PETROL','LPG_CNG_PETROL_CONV','EURO_IV_DIESEL')
714 then
715
716 hr_utility.trace('Fuel type: '||l_fuel_type);
717
718
719 if l_fuel_type in ('HYBRID_ELECTRIC','LPG_CNG','LPG_CNG_PETROL','LPG_CNG_PETROL_CONV')
720 then
721
722 l_fuel_type := 'PETROL';
723
724
725 elsif l_fuel_type = 'EURO_IV_DIESEL'
726 then
727
728 l_fuel_type := 'DIESEL';
729
730 else
731
732 l_fuel_type := l_fuel_type;
733
734 end if;
735
736
737 hr_utility.trace('Engine size: '||p_engine_size);
738 hr_utility.trace('Session date: '||p_session_date);
739 l_fuel_scale := fnd_number.canonical_to_number(hruserdt.get_table_value(p_business_group_id,
740 'FUEL_SCALE',
741 l_fuel_type,
742 p_engine_size,
743 p_session_date));
744
745 /* Pro-rate fuel scale according to total days available */
746
747 l_fuel_scale := (l_fuel_scale / g_days_in_year) * l_days_available;
748
749 else -- alternative fuel types
750
751 l_fuel_scale := 0;
752
753
754 end if;
755
756
757 /* if p_fuel_scale is null, then set l_fuel_scale to 0 */
758 elsif (p_fuel_scale is null and (g_ignore_fuel_opt_out = 'N' OR p_start_date >= g_last_opt_out_date) )
759 then
760
761 l_fuel_scale := 0;
762
763 /* anything else, do nothing */
764
765 else null;
766
767 end if;
768
769 ELSE
770
771 l_fuel_scale := 0;
772
773 END IF;
774
775
776 hr_utility.trace('l_fuel_scale: '||l_fuel_scale);
777 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,29);
778 IF nvl(p_fuel_scale, 0) = 0 and g_tax_year_start >= to_date('06-04-2003', 'DD-MM-YYYY') THEN -- Effective from year 2003-04
779 l_fuel_scale := l_fuel_scale * (l_percentage/100);
780 END IF;
781 hr_utility.trace('l_fuel_scale: '||l_fuel_scale);
782 l_benefit_charge:= l_benefit_charge + l_fuel_scale;
783
784 hr_utility.trace('l_benefit_charge (after fuel scale): '||l_benefit_charge);
785 /* Check if message has been set, if not leave as null */
786
787 p_message := nvl(p_message,null);
788
789 /* Round down benefit charge to nearest pound */
790
794
791 l_benefit_charge := trunc(l_benefit_charge);
792
793 hr_utility.trace('l_benefit_charge (final): '||l_benefit_charge);
795
796 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,999);
797
798
799
800 /* Return final benefit charge */
801
802 return l_benefit_charge;
803
804 exception
805
806 when others
807 then raise;
808
809
810 end nicar_nicable_value_CO2;
811
812 /* Function to calculate taxable / NICable value based on engine capacity for cars without */
813 /* approved CO2 emissions ratings */
814 /* Parameter p_business_group_id provided by context-set variable */
815 function nicar_nicable_value_non_CO2
816 ( p_assignment_id IN NUMBER,
817 p_element_type_id IN NUMBER,
818 p_business_group_id IN NUMBER,
819 /* Import direct from fast formula */
820 p_car_price IN NUMBER,
821 p_reg_date IN DATE,
822 p_fuel_type IN VARCHAR2,
823 p_engine_size IN NUMBER,
824 p_fuel_scale IN NUMBER DEFAULT NULL,
825 p_payment IN NUMBER,
826 p_start_date IN DATE,
827 p_end_date IN DATE,
828 p_end_of_period_date IN DATE,
829 p_emp_term_date IN DATE,
830 p_session_date IN DATE,
831 p_message OUT NOCOPY VARCHAR2,
832 p_number_of_days IN NUMBER DEFAULT 0)
833 return NUMBER is
834
835 l_function_name VARCHAR2(27) := 'nicar_nicable_value_non_CO2';
836
837
838
839 l_percentage NUMBER;
840 l_diesel_supplement NUMBER;
841
842 l_benefit_charge NUMBER;
843
844 l_car_price NUMBER;
845
846 l_reg_date DATE;
847
848 l_entry_start_date DATE;
849 l_entry_end_date DATE;
850
851 l_end_of_period_date DATE;
852
853 l_start_date DATE;
854 l_end_date DATE;
855
856 l_price_cap NUMBER;
857
858 l_days_available NUMBER;
859
860 l_fuel_scale NUMBER;
861
862 l_fuel_type VARCHAR2(100);
863
864
865 cursor csr_max_price_global is
866 select to_number(global_value)
867 from ff_globals_f
868 where global_name = 'NI_CAR_MAX_PRICE'
869 and p_session_date between
870 effective_start_date and effective_end_date;
871
872
873
874
875
876 begin
877
878
879
880 hr_utility.set_location(g_package_name||'.'||l_function_name,100);
881
882
883 hr_utility.trace('** Parameter values ** ');
884 hr_utility.trace('p_assignment_id: '||p_assignment_id);
885 hr_utility.trace('p_element_type_id: '||p_element_type_id);
886 hr_utility.trace('p_business_group_id: '||p_business_group_id);
887 hr_utility.trace('p_car_price: '||p_car_price);
888 hr_utility.trace('p_reg_date: '||p_reg_date);
889 hr_utility.trace('p_fuel_type: '||p_fuel_type);
890 hr_utility.trace('p_engine_size: '||p_engine_size);
891 hr_utility.trace('p_fuel_scale: '||p_fuel_scale);
892 hr_utility.trace('p_payment: '||p_payment);
893 hr_utility.trace('p_start_date: '||p_start_date);
894 hr_utility.trace('p_end_date: '||p_end_date);
895 hr_utility.trace('p_end_of_period_date: '||p_end_of_period_date);
896 hr_utility.trace('p_emp_term_date: '||p_emp_term_date);
897 hr_utility.trace('p_session_date: '||p_session_date);
898
899
900
901 hr_utility.set_location(g_package_name||'.'||l_function_name,102);
902
903 l_start_date := trunc(p_start_date);
904 l_end_date := trunc(p_end_date);
905 l_reg_date := trunc(p_reg_date);
906 l_end_of_period_date := trunc(p_end_of_period_date);
907
908 l_car_price := p_car_price;
909 l_fuel_type := p_fuel_type;
910
911 hr_utility.trace('l_start_date: '||l_start_date);
912 hr_utility.trace('l_end_date: '||l_end_date);
913 hr_utility.trace('l_reg_date: '||l_reg_date);
914 hr_utility.trace('l_end_of_period_date: '||l_end_of_period_date);
915 hr_utility.trace('l_fuel_type: '||l_fuel_type);
916 hr_utility.trace('p_session_date: '||p_session_date);
917
918
919
920 hr_utility.set_location(g_package_name||'.'||l_function_name,104);
921
922
923 /* Find percentage charge for vehicle */
924
925 l_percentage := get_cc_percentage(p_business_group_id,
926 p_engine_size,
927 p_reg_date,
928 p_session_date);
929
930
931 hr_utility.trace('l_percentage: '||l_percentage);
932 hr_utility.set_location(g_package_name||'.'||l_function_name,106);
933
934 /* Check if diesel supplements apply to that vehicle */
935
936 if (p_reg_date >= to_date('01/01/1998','DD/MM/YYYY')
937 and l_fuel_type = 'DIESEL')
938 then
939
940 /* supplements vary according to engine cc */
941 if to_number(p_engine_size) between 0 and 2000
942 then
943
944 l_diesel_supplement := 3;
945
946 else
947
948 l_diesel_supplement := 0;
949
950 end if;
951
952
953
954 /* Add supplement to percentage */
955 hr_utility.trace('l_diesel_supplement: '||l_diesel_supplement);
956 hr_utility.set_location(g_package_name||'.'||l_function_name,106);
957
961
958 l_percentage := l_percentage + l_diesel_supplement;
959
960 end if;
962 hr_utility.set_location(g_package_name||'.'||l_function_name,107);
963
964 /* Calculate car benefit charge */
965
966 l_benefit_charge := l_car_price * (l_percentage/100);
967
968 hr_utility.trace('l_benefit_charge: '||l_benefit_charge);
969 hr_utility.set_location(g_package_name||'.'||l_function_name,108);
970
971 IF p_element_type_id is NOT NULL
972 THEN
973
974 /* Find number of days for which car is available to employee */
975 /* Need to find 1) greatest between start date of NI Car element entry or start of tax year */
976 /* 2) least between entry end date, payroll period/ tax year end date, employee termination date */
977 /* Relevant dates set above */
978
979 hr_utility.trace('l_start_date: '||l_start_date);
980 hr_utility.trace('l_end_date: '||l_end_date);
981
982 IF g_tax_year_start >= to_date('06-04-2003', 'DD-MM-YYYY') THEN -- Effective from year 2003-04
983 l_days_available := trunc(l_end_date) - trunc(l_start_date) + 1; -- use actual number of days
984 ELSE
985 l_days_available := hr_gbnicar.nicar_days_between(l_start_date,l_end_date); -- ignore 29-FEB
986 END IF;
987
988 ELSE
989
990 l_days_available := p_number_of_days;
991
992 END IF;
993
994 hr_utility.trace('l_days_available: '||l_days_available);
995 hr_utility.set_location(g_package_name||'.'||l_function_name,109);
996
997 /* Deduct annual payment from annual benefit charge */
998
999 hr_utility.trace('p_payment: '||p_payment);
1000
1001 l_benefit_charge := l_benefit_charge - p_payment;
1002
1003
1004 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,110);
1005
1006 /* If result < 0, then benefit charge will be 0 */
1007 if l_benefit_charge < 0
1008 then
1009
1010 l_benefit_charge := 0;
1011 hr_utility.trace('Benefit charge reset to 0');
1012
1013 end if;
1014
1015 hr_utility.trace('l_benefit_charge (after payment): '||l_benefit_charge);
1016
1017 /* Pro-rate the benefit charge, based on the number of days available */
1018
1019 l_benefit_charge := (l_benefit_charge / g_days_in_year) * l_days_available;
1020 hr_utility.trace('l_benefit_charge (pro-rated): '||l_benefit_charge);
1021 hr_utility.trace('g_days_in_year = '||g_days_in_year);
1022 hr_utility.trace('p_start_date: '||p_start_date);
1023 hr_utility.trace('p_end_date: '||p_end_date);
1024 hr_utility.trace('p_assignment_id = '||p_assignment_id);
1025 hr_utility.trace('p_element_type_id = '||p_element_type_id);
1026 hr_utility.trace('l_percentage = '||l_percentage);
1027 hr_utility.trace('l_days_available = '||l_days_available);
1028 hr_utility.trace('g_ignore_fuel_opt_out = '||g_ignore_fuel_opt_out);
1029 hr_utility.trace('g_last_opt_out_date = '||to_char(g_last_opt_out_date, 'DD/MM/YYYY'));
1030 hr_utility.trace('l_fuel_type = '||l_fuel_type);
1031
1032 IF p_element_type_id is NOT NULL
1033 THEN
1034
1035 /* Add on any fuel scale charges after pro-ration */
1036 /* Use manually entered value if exists, else retrieve value from user table */
1037 /* based on engine size and fuel type */
1038 /* Value of p_fuel_scale parameter will be null unless overriden */
1039 hr_utility.trace('p_fuel_scale: '||p_fuel_scale);
1040
1041 /* Check inputs to see if fuel scale user table should be queried or not */
1042
1043 /* If fuel scale > 0 then manual user-entered input */
1044
1045 if (p_fuel_scale > 0)
1046 then
1047
1048 l_fuel_scale := p_fuel_scale;
1049
1050 /* Pro-rate fuel scale according to total days available */
1051
1052 l_fuel_scale := (l_fuel_scale / g_days_in_year) * l_days_available;
1053
1054
1055 /* if fuel scale entry is 0, user wants fuel scale calculated automatically */
1056 /* check fuel type and visit user table if required */
1057 elsif (p_fuel_scale = 0 or (p_fuel_scale is null and g_ignore_fuel_opt_out = 'Y' and p_start_date < g_last_opt_out_date))
1058 then
1059 if l_fuel_type in ('HYBRID_ELECTRIC','PETROL','DIESEL','LPG_CNG','LPG_CNG_PETROL','LPG_CNG_PETROL_CONV','EURO_IV_DIESEL')
1060 then
1061
1062 hr_utility.trace('Fuel type: '||l_fuel_type);
1063
1064
1065 if l_fuel_type in ('HYBRID_ELECTRIC','LPG_CNG','LPG_CNG_PETROL','LPG_CNG_PETROL_CONV')
1066 then
1067
1068 l_fuel_type := 'PETROL';
1069
1070 elsif l_fuel_type = 'EURO_IV_DIESEL'
1071 then
1072
1073 l_fuel_type := 'DIESEL';
1074
1075 else
1076
1077 l_fuel_type := l_fuel_type;
1078
1079 end if;
1080
1081
1082 hr_utility.trace('Engine size: '||p_engine_size);
1083 hr_utility.trace('Session date: '||p_session_date);
1084 l_fuel_scale := fnd_number.canonical_to_number(hruserdt.get_table_value(p_business_group_id,
1085 'FUEL_SCALE',
1086 l_fuel_type,
1087 p_engine_size,
1088 p_session_date));
1089
1090
1091 /* Pro-rate fuel scale according to total days available */
1092
1096
1093 l_fuel_scale := (l_fuel_scale / g_days_in_year) * l_days_available;
1094
1095 else -- alternative fuel types
1097 l_fuel_scale := 0;
1098
1099
1100 end if;
1101
1102
1103
1104 /* if p_fuel_scale is null, then set l_fuel_scale to 0 */
1105 elsif (p_fuel_scale is null and (g_ignore_fuel_opt_out = 'N' OR p_start_date >= g_last_opt_out_date) )
1106 then
1107
1108 l_fuel_scale := 0;
1109
1110 /* anything else, do nothing */
1111
1112 else null;
1113
1114 end if;
1115 ELSE
1116
1117 l_fuel_scale := 0;
1118
1119 END IF;
1120
1121
1122 hr_utility.trace('l_fuel_scale: '||l_fuel_scale);
1123 hr_utility.SET_LOCATION(g_package_name||'.'||l_function_name,113);
1124
1125 IF nvl(p_fuel_scale, 0) = 0 and g_tax_year_start >= to_date('06-04-2003', 'DD-MM-YYYY') THEN -- Effective from year 2003-04
1126 l_fuel_scale := l_fuel_scale * (l_percentage/100);
1127 END IF;
1128 hr_utility.trace('l_fuel_scale: '||l_fuel_scale);
1129 l_benefit_charge:= l_benefit_charge + l_fuel_scale;
1130
1131
1132 /* Return final benefit charge */
1133
1134 hr_utility.trace('l_benefit_charge (after fuel scale): '||l_benefit_charge);
1135
1136
1137
1138 /* Round down benefit charge to nearest pound */
1139
1140 l_benefit_charge := trunc(l_benefit_charge);
1141 hr_utility.trace('l_benefit_charge (final): '||l_benefit_charge);
1142
1143 /* Set p_message parameter to null */
1144 p_message := null;
1145
1146 hr_utility.set_location(g_package_name||'.'||l_function_name,999);
1147
1148 return l_benefit_charge;
1149
1150 exception
1151
1152 when others
1153 then raise;
1154
1155 end nicar_nicable_value_non_CO2;
1156
1157
1158 /* Round CO2 emissions figure down to nearest multiple of 5 */
1159 function round_CO2_val
1160 (p_value IN NUMBER)
1161 return NUMBER is
1162
1163 l_function_name VARCHAR2(13) := 'round_CO2_val';
1164 l_value NUMBER;
1165
1166 begin
1167
1168 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
1169
1170 l_value := p_value;
1171 hr_utility.trace('Value before rounding: '||l_value);
1172
1173 loop
1174
1175 l_value := l_value - 1;
1176
1177 exit when mod(l_value,5) = 0;
1178 end loop;
1179
1180 hr_utility.trace('Value after rounding: '||l_value);
1181 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
1182
1183 return l_value;
1184
1185 end round_CO2_val;
1186
1187 /* Return applicable percentage charge based on CO2 emissions for car */
1188 function get_CO2_percentage
1189 (p_business_group_id IN NUMBER,
1190 p_co2_emissions IN NUMBER,
1191 p_session_date IN DATE)
1192 return NUMBER is
1193
1194 l_percentage NUMBER;
1195 l_function_name VARCHAR2(18) := 'get_CO2_percentage';
1196
1197 l_co2_emissions NUMBER;
1198
1199 e_invalid_input EXCEPTION;
1200
1201 begin
1202
1203 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
1204
1205 /* Validate inputs */
1206
1207 if p_business_group_id is null
1208 or p_co2_emissions is null
1209 or p_session_date is null
1210 then
1211
1212 raise e_invalid_input;
1213
1214 end if;
1215
1216 hr_utility.trace('p_business_group_id: '||p_business_group_id);
1217 hr_utility.trace('p_co2_emissions: '||p_co2_emissions);
1218 hr_utility.trace('p_session_date: '||p_session_date);
1219
1220 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
1221
1222 l_co2_emissions := p_co2_emissions;
1223
1224 /* Get table value */
1225 l_percentage := fnd_number.canonical_to_number(hruserdt.get_table_value(p_business_group_id,
1226 'GB_CO2_EMISSIONS',
1227 'PERCENTAGE_CHARGE',
1228 l_co2_emissions,
1229 p_session_date));
1230
1231
1232
1233 hr_utility.set_location(g_package_name||'.'||l_function_name,4);
1234
1235 return l_percentage;
1236
1237 exception
1238
1239 when e_invalid_input
1240 then
1241 hr_utility.trace('ERROR: Input into get_CO2_percentage invalid');
1242 hr_utility.trace('Business group id: '||p_business_group_id);
1243 hr_utility.trace('CO2 emissions figure: '||p_co2_emissions);
1244 hr_utility.trace('Session date: '||to_char(p_session_date,'DD/MM/YYYY'));
1245 raise;
1246
1247 when too_many_rows
1248 then
1249 hr_utility.trace('ERROR: Retrieving CO2 percentage from user table retrieved too many rows');
1250 raise;
1251
1252
1253 when no_data_found
1254 then
1255 hr_utility.trace('ERROR: Call to GB_CO2_EMISSIONS user table retrieved no value');
1256 raise;
1257
1258 when others
1259 then raise;
1260
1261
1262 end get_CO2_percentage;
1263
1264
1265 /* Return applicable percentage based on engine size */
1266 function get_cc_percentage
1267 ( p_business_group_id IN NUMBER,
1271 return NUMBER is
1268 p_engine_size IN NUMBER,
1269 p_reg_date IN DATE,
1270 p_session_date IN DATE)
1272
1273 l_percentage NUMBER;
1274 l_function_name VARCHAR2(17) := 'get_cc_percentage';
1275 l_column_name VARCHAR2(40);
1276
1277
1278 begin
1279
1280 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
1281
1282 /* Check to see what registration date is as value retrieved will differ */
1283
1284 if p_reg_date < to_date('01/01/1998','DD/MM/YYYY')
1285 then
1286
1287 l_column_name := 'BEFORE_JAN_1_1998';
1288
1289 else
1290
1291 l_column_name := 'ON_AFTER_JAN_1_1998';
1292
1293 end if;
1294
1295 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
1296
1297 l_percentage := fnd_number.canonical_to_number(hruserdt.get_table_value(p_business_group_id,
1298 'GB_CC_SCALE',
1299 l_column_name,
1300 p_engine_size,
1301 p_session_date));
1302
1303
1304 hr_utility.set_location(g_package_name||'.'||l_function_name,3);
1305
1306 return l_percentage;
1307
1308 exception
1309
1310 when too_many_rows
1311 then
1312 hr_utility.trace('ERROR: Retrieving CC percentage from user table retrieved too many rows');
1313 raise;
1314
1315 when no_data_found
1316 then
1317 hr_utility.trace('ERROR: Retrieving CC percentage from user table retrieved no value');
1318 hr_utility.trace('p_business_group_id: '||p_business_group_id);
1319 hr_utility.trace('p_engine_size: '||p_engine_size);
1320 hr_utility.trace('p_session_date: '||p_session_date);
1321 raise;
1322
1323
1324 when others
1325 then raise;
1326
1327
1328
1329 end get_cc_percentage;
1330
1331
1332 /* Return fixed discount percentage for certain alternative fuel cars */
1333 function get_discount
1334 ( p_fuel_type IN VARCHAR2,
1335 p_session_date IN DATE)
1336 return NUMBER is
1337
1338 l_percentage NUMBER;
1339 l_global_value ff_globals_f.global_value%type;
1340 l_global_name ff_globals_f.global_name%type;
1341
1342 l_function_name VARCHAR2(12):= 'get_discount';
1343
1344 l_discount NUMBER;
1345
1346 cursor csr_global_value is
1347 select to_number(global_value)
1348 from ff_globals_f
1349 where legislation_code = 'GB'
1350 and upper(global_name) = upper(l_global_name)
1351 and p_session_date between effective_start_date
1352 and effective_end_date;
1353
1354
1355 begin
1356
1357 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
1358
1359 /* Check which fuel type is being used */
1360
1361 if p_fuel_type = 'BATTERY_ELECTRIC' -- Battery electric
1362 then
1363
1364 l_global_name := 'NI_CAR_BATTERY_ELECTRIC_DISCOUNT';
1365
1366 elsif p_fuel_type in ('LPG_CNG_PETROL','LPG_CNG_PETROL_CONV') -- Bi-fuel/conversion
1367 then
1368
1369 l_global_name := 'NI_CAR_BI_FUEL_DISCOUNT';
1370
1371 elsif p_fuel_type = 'HYBRID_ELECTRIC' -- Hybrid electric
1372 then
1373
1374 l_global_name := 'NI_CAR_HYBRID_ELECTRIC_DISCOUNT';
1375
1376 elsif p_fuel_type = 'LPG_CNG' -- LPG/CNG
1377 then
1378
1379 l_global_name := 'NI_CAR_LPG_DISCOUNT';
1380
1381 end if;
1382
1383 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
1384
1385 /* Get value of the global based on its name */
1386
1387 open csr_global_value;
1388 fetch csr_global_value into l_global_value;
1389 close csr_global_value;
1390
1391
1392 hr_utility.set_location(g_package_name||'.'||l_function_name,10);
1393
1394 l_discount := to_number(l_global_value) * 100;
1395
1396 return l_discount;
1397
1398 exception
1399
1400 when too_many_rows
1401 then
1402 hr_utility.trace('ERROR: Retrieving global value for '||l_global_name||' retrieved too many rows');
1403 raise;
1404
1405 when no_data_found
1406 then
1407 hr_utility.trace('ERROR: Retrieving global value for '||l_global_name||' retrieved no value');
1408 hr_utility.trace('p_fuel_type: '||p_fuel_type);
1409 hr_utility.trace('p_session_date: '||p_session_date);
1410 raise;
1411
1412
1413 when others
1414 then raise;
1415
1416
1417
1418 end get_discount;
1419
1420
1421
1422 /* Primary function for pay_gb_nicar_06042002 package */
1423 /* Called by fast formula and NI Car Detail report */
1424 /* Function invokes nicar_nicable_value_CO2 and nicar_nicable_value_non_CO2 as appropriate */
1425 /* Parameter list:
1426 Context-set parameters
1427 p_assignment_id
1428 p_element_type_id
1429 p_business_group_id
1430
1431 Fast formula parameters:
1432 p_pay_periods_of_year
1433 p_pay_period_number
1434 p_pay_period_end_date
1435 p_emp_term_date
1436 p_session_date
1437
1438 Returns:
1439
1443 */
1440 l_running_total - running total of car benefit value up to end date
1441 1-4 messages, dependent on how many messages produced
1442
1444 function nicar_main
1445 ( /* Context set parameters */
1446 p_assignment_id IN number,
1447 p_element_type_id IN number,
1448 p_business_group_id IN number,
1449 /* Fast formula parameters */
1450 p_pay_periods_per_year IN number,
1451 p_curr_payroll_period IN number,
1452 p_curr_payroll_period_end_date IN date,
1453 p_emp_term_date IN date,
1454 p_session_date IN date,
1455 /* OUT parameter */
1456 p_message_1 OUT NOCOPY varchar2,
1457 p_message_2 OUT NOCOPY varchar2,
1458 p_message_3 OUT NOCOPY varchar2,
1459 p_message_4 OUT NOCOPY varchar2)
1460 return number is
1461
1462 --
1463 -- N.B. When called from FastFormula, p_assignment_id, p_element_type_id
1464 --- and p_business_group_id are
1465 -- provided via context-set variables.
1466 --
1467 csr0_session_date date;
1468 --
1469 csr1_price_max number;
1470 csr1_ni_rate number;
1471 --
1472 csr2_element_name pay_element_types_f.element_name%type;
1473 csr2_pr number;
1474 csr2_rd number;
1475 csr2_rn number;
1476 csr2_mb number;
1477 csr2_ft number;
1478 csr2_cc number;
1479 csr2_fs number;
1480 csr2_ap number;
1481 csr2_co2 number;
1482 --
1483
1484 csr3_price number;
1485 csr3_reg_date date;
1486 csr3_mileage_band number;
1487 csr3_fuel_scale number;
1488 csr3_fuel_type varchar2(100);
1489 csr3_engine_cc number;
1490 csr3_payment number;
1491 csr3_co2_emissions number;
1492 csr3_start_date date;
1493 csr3_end_date date;
1494 --
1495 l_fuel_scale number :=0;
1496 l_running_total number :=0;
1497 l_nicable_benefit number :=0;
1498
1499 l_end_of_period_date date;
1500 l_start_date date;
1501 l_end_date date;
1502
1503 l_reg_date_lower_limit date := to_date('01/01/1998','DD/MM/YYYY');
1504
1505 l_message varchar2(500);
1506 l_co2_message varchar2(500);
1507 l_cc_message varchar2(500);
1508 l_msg_count number := 1;
1509 type l_msg_tabtype is table of varchar2(500) index by binary_integer;
1510 tbl_msg_table l_msg_tabtype;
1511
1512 l_pay_period_type per_time_period_types.period_type%type;
1513 l_periods_per_period number;
1514 l_annualised_pay_period number;
1515 l_next_period_end_date date;
1516
1517
1518
1519 l_function_name varchar2(10) := 'nicar_main';
1520
1521
1522
1523
1524 --
1525 --
1526 cursor csr1_globals is
1527 SELECT fnd_number.canonical_to_number(LIM.global_value)
1528 , fnd_number.canonical_to_number(NIR.global_value)
1529 FROM ff_globals_f LIM
1530 , ff_globals_f NIR
1531 WHERE LIM.global_name = 'NI_CAR_MAX_PRICE'
1532 AND csr0_session_date between LIM.effective_start_date and LIM.effective_end_date
1533 AND NIR.global_name = 'NI_ERS_RATE'
1534 AND csr0_session_date between NIR.effective_start_date and NIR.effective_end_date;
1535 --
1536 cursor csr2_pri_sec is
1537 SELECT E_TL.element_name
1538 , IPR.input_value_id
1539 , IRD.input_value_id
1540 , IRN.input_value_id
1541 , IMB.input_value_id
1542 , IFT.input_value_id
1543 , ICC.input_value_id
1544 , IFS.input_value_id
1545 , IAP.input_value_id
1546 , ICO.input_value_id
1547 FROM pay_input_values_f IPR
1548 , pay_input_values_f IRD
1549 , pay_input_values_f IRN
1550 , pay_input_values_f IMB
1551 , pay_input_values_f IFT
1552 , pay_input_values_f ICC
1553 , pay_input_values_f IFS
1554 , pay_input_values_f IAP
1555 , pay_input_values_f ICO
1556 , pay_element_types_f_tl E_TL
1557 , pay_element_types_f E
1558 WHERE E_TL.element_type_id = E.element_type_id
1559 AND E.element_type_id = p_element_type_id
1560 AND userenv('LANG') = E_TL.language
1561 AND IPR.element_type_id = E.element_type_id
1562 AND IPR.name = 'Price'
1563 AND IRD.element_type_id =E.element_type_id
1564 AND IRD.name = 'Registration Date'
1565 AND IRN.element_type_id = E.element_type_id
1566 AND IRN.name = 'Registration Number'
1567 AND IMB.element_type_id = E.element_type_id
1568 AND IMB.name = 'Mileage Band'
1569 AND IFT.element_type_id = E.element_type_id
1570 AND IFT.name = 'Fuel Type'
1571 AND ICC.element_type_id = E.element_type_id
1572 AND ICC.name = 'Engine cc'
1573 AND IFS.element_type_id = E.element_type_id
1574 AND IFS.name = 'Fuel Scale'
1575 AND IAP.element_type_id = E.element_type_id
1576 AND IAP.name = 'Payment'
1577 AND ICO.element_type_id = E.element_type_id
1578 AND ICO.name = 'CO2 Emissions'
1579 AND csr0_session_date between E.effective_start_date and E.effective_end_date;
1580 --
1581 --
1585 select
1582 cursor csr3_nicar is
1583 -- bug 504994 changed the entry cursor to use a single join on values table
1584 -- to improve performance
1586 max(decode(V.input_value_id,csr2_pr,
1587 fnd_number.canonical_to_number(V.Screen_entry_value),null) ) csr3_price
1588 ,max(decode(V.input_value_id, csr2_mb,
1589 fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_mileage_band
1590 ,max(decode(V.input_value_id,csr2_rd,
1591 fnd_date.canonical_to_date(V.Screen_entry_value),null)) csr3_reg_date
1592 ,max(decode(V.input_value_id,csr2_ft,
1593 V.Screen_entry_value,null)) csr3_fuel_type
1594 ,max(decode(V.input_value_id,csr2_cc,
1595 fnd_number.canonical_to_number(v.Screen_entry_value),null)) csr3_engine_cc
1596 ,max(decode(V.input_value_id,csr2_fs,
1597 fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_fuel_scale
1598 ,max(decode(V.input_value_id,csr2_ap,
1599 nvl(fnd_number.canonical_to_number(V.Screen_entry_value),0),null)) csr3_payment
1600 ,max(decode(V.input_value_id,csr2_co2,
1601 fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_co2
1602 ,EENT.effective_end_date csr3_end_date
1603 ,EENT.effective_start_date csr3_start_date
1604
1605 FROM pay_element_entries_f EENT
1606 , pay_element_links_f LINK
1607 , pay_element_entry_values_f V
1608 WHERE EENT.effective_end_date >= g_tax_year_start
1609 AND EENT.effective_start_date <=
1610 least(p_emp_term_date,l_end_of_period_date,g_tax_year_end)
1611 AND EENT.assignment_id = p_assignment_id
1612 AND LINK.element_type_id = p_element_type_id
1613 AND EENT.element_link_id = LINK.element_link_id
1614 AND EENT.effective_start_date >= LINK.effective_start_date
1615 AND EENT.effective_end_date <= LINK.effective_end_date
1616 AND EENT.entry_type = 'E'
1617 AND V.Element_entry_id = EENT.element_entry_id
1618 AND V.Effective_start_date = EENT.effective_start_date
1619 group by EENT.effective_end_date, EENT.effective_start_date;
1620
1621 /* Find out if employee has opted back into free fuel part way through the tax year */
1622 CURSOR csr_ignore_fuel_opt_out IS
1623 SELECT 'Y'
1624 FROM pay_element_entries_f pee,
1625 pay_element_links_f pel,
1626 pay_element_entry_values_f peev
1627 WHERE pee.effective_start_date > g_tax_year_start
1628 AND pee.effective_start_date <= least(p_emp_term_date, l_end_of_period_date, g_tax_year_end)
1629 AND pee.assignment_id = p_assignment_id
1630 AND pel.element_type_id = p_element_type_id
1631 AND pee.element_link_id = pel.element_link_id
1632 AND pee.effective_start_date >= pel.effective_start_date
1633 AND pee.effective_end_date <= pel.effective_end_date
1634 AND pee.entry_type = 'E'
1635 AND peev.element_entry_id = pee.element_entry_id
1636 AND peev.effective_start_date = pee.effective_start_date
1637 AND peev.input_value_id = csr2_fs
1638 AND peev.screen_entry_value IS NOT NULL;
1639
1640 /* If employee has currently opted out of the free fuel then find out the start date of this change
1641 else return a date after end of the year */
1642 CURSOR csr_last_opt_out_date IS
1643 SELECT nvl(max(pee.effective_start_date), g_tax_year_end+1)
1644 FROM pay_element_entries_f pee,
1645 pay_element_links_f pel,
1646 pay_element_entry_values_f peev
1647 WHERE least(p_emp_term_date, l_end_of_period_date, g_tax_year_end) BETWEEN
1648 pee.effective_start_date AND pee.effective_end_date
1649 AND pee.assignment_id = p_assignment_id
1650 AND pel.element_type_id = p_element_type_id
1651 AND pee.element_link_id = pel.element_link_id
1652 AND pee.effective_start_date >= pel.effective_start_date
1653 AND pee.effective_end_date <= pel.effective_end_date
1654 AND pee.entry_type = 'E'
1655 AND peev.element_entry_id = pee.element_entry_id
1656 AND peev.effective_start_date = pee.effective_start_date
1657 AND peev.input_value_id = csr2_fs
1658 AND peev.screen_entry_value IS NULL;
1659
1660 /* Get payroll period type for payroll period currently being processed */
1661 cursor csr_period_type is
1662 select ptpt.period_type
1663 from per_time_period_types ptpt,
1664 per_time_periods ptp,
1665 per_assignments_f paf
1666 where paf.payroll_id = ptp.payroll_id
1667 and ptpt.period_type = ptp.period_type
1668 and paf.assignment_id = p_assignment_id
1669 and trunc(p_session_date) between trunc(ptp.start_date) and trunc(ptp.end_date);
1670
1671 /* Get end date of next payroll period after the one currently being processed */
1672 cursor csr_next_end_date is
1673 select ptp.end_date
1674 from per_time_periods ptp,
1675 pay_all_payrolls_f papf,
1676 per_assignments_f paf
1677 where paf.payroll_id = papf.payroll_id
1678 and papf.payroll_id = ptp.payroll_id
1679 and paf.assignment_id = p_assignment_id
1680 and ptp.start_date = p_curr_payroll_period_end_date + 1;
1681
1682 --
1683 --
1684 BEGIN
1685 -- hr_utility.trace_on(null,'NICAR');
1686
1687 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
1688
1689
1690 --
1691 -- Get the session date
1692 csr0_session_date := p_session_date;
1693
1694
1695 --
1696 --
1697 -- Get the tax year start and end dates from the session date;
1698 --
1699 g_tax_year_start := hr_gbnicar.uk_tax_yr_start(csr0_session_date);
1700 g_tax_year_end := hr_gbnicar.uk_tax_yr_end(csr0_session_date);
1701 g_days_in_year := trunc(g_tax_year_end) - trunc(g_tax_year_start) + 1;
1702
1703 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
1704
1708 fetch csr_period_type into l_pay_period_type;
1705
1706
1707 open csr_period_type;
1709 close csr_period_type;
1710
1711
1712 IF l_pay_period_type = 'Calendar Month'
1713 THEN
1714 l_periods_per_period := 1;
1715
1716 ELSIF l_pay_period_type = 'Week'
1717 THEN
1718 l_periods_per_period := 1;
1719
1720 ELSIF l_pay_period_type = 'Bi-Week'
1721 THEN
1722 l_periods_per_period := 2;
1723
1724 ELSIF l_pay_period_type = 'Lunar Month'
1725 THEN
1726 l_periods_per_period := 4;
1727
1728 ELSIF l_pay_period_type = 'Semi-Year'
1729 THEN
1730 l_periods_per_period := 6;
1731
1732 ELSIF l_pay_period_type = 'Year'
1733 THEN
1734 l_periods_per_period := 12;
1735
1736 END IF;
1737
1738 l_annualised_pay_period := p_curr_payroll_period / l_periods_per_period;
1739
1740 /* Check if pay period being run is last in current tax year */
1741 if l_annualised_pay_period >= p_pay_periods_per_year
1742 then
1743
1744 open csr_next_end_date;
1745 fetch csr_next_end_date into l_next_period_end_date;
1746 close csr_next_end_date;
1747
1748 if l_next_period_end_date > g_tax_year_end
1749 then
1750 /* use tax year end as end of period date */
1751
1752 l_end_of_period_date := g_tax_year_end;
1753
1754 else
1755 /* use end date of current payroll period as end of period date */
1756
1757 l_end_of_period_date := p_curr_payroll_period_end_date;
1758
1759 end if;
1760
1761
1762 else
1763 /* at all other times, use end date of current payroll period */
1764
1765 l_end_of_period_date := p_curr_payroll_period_end_date;
1766
1767 end if;
1768
1769
1770 hr_utility.set_location(g_package_name||'.'||l_function_name,3);
1771 --
1772 --
1773 -- Get the max allowable price, and the contrib rate
1774 --
1775 hr_utility.set_location(g_package_name||'.'||l_function_name,10);
1776 open csr1_globals;
1777 hr_utility.set_location(g_package_name||'.'||l_function_name,20);
1778 fetch csr1_globals
1779 into csr1_price_max
1780 , csr1_ni_rate;
1781 close csr1_globals;
1782
1783 hr_utility.trace('csr1_price_max: '||csr1_price_max);
1784 hr_utility.trace('csr1_ni_rate: '||csr1_ni_rate);
1785 --
1786 --
1787 -- Get the element_name for the element type id, and all the associated
1788 -- input value ids.
1789 --
1790 hr_utility.set_location(g_package_name||'.'||l_function_name,30);
1791 open csr2_pri_sec;
1792 hr_utility.set_location(g_package_name||'.'||l_function_name,40);
1793 fetch csr2_pri_sec
1794 into csr2_element_name
1795 , csr2_pr
1796 , csr2_rd
1797 , csr2_rn
1798 , csr2_mb
1799 , csr2_ft
1800 , csr2_cc
1801 , csr2_fs
1802 , csr2_ap
1803 , csr2_co2;
1804
1805 close csr2_pri_sec;
1806
1807 g_ignore_fuel_opt_out := 'N';
1808 hr_utility.trace('p_assignment_id = '||p_assignment_id);
1809 hr_utility.trace('g_tax_year_start = '||to_char(g_tax_year_start, 'DD-MON-YYYY'));
1810 hr_utility.trace('g_tax_year_end = '||to_char(g_tax_year_end, 'DD-MON-YYYY'));
1811 -- Find out if employee has opted back into free fuel part way through the
1812 -- tax year. If yes then fuel scale will be charged for full availability
1813 -- for the car.
1814 IF g_tax_year_start >= to_date('06-04-2003', 'DD-MM-YYYY') THEN --Effective from year 2003
1815 hr_utility.trace('Opening csr_ignore_opt_out.');
1816 open csr_ignore_fuel_opt_out;
1817 fetch csr_ignore_fuel_opt_out into g_ignore_fuel_opt_out;
1818 close csr_ignore_fuel_opt_out;
1819 --
1820 hr_utility.trace('Opening csr_last_opt_out_date.');
1821 open csr_last_opt_out_date;
1822 fetch csr_last_opt_out_date into g_last_opt_out_date;
1823 close csr_last_opt_out_date;
1824 END IF;
1825 --
1826 hr_utility.trace('g_ignore_fuel_out_out = '||g_ignore_fuel_opt_out);
1827 hr_utility.trace('g_last_opt_out_date = '||g_last_opt_out_date);
1828 --
1829 --
1830 -- Get the required details for all company car benefits for the assignment
1831 --
1832 hr_utility.set_location(g_package_name||'.'||l_function_name,50);
1833 open csr3_nicar;
1834 --
1835 hr_utility.set_location(g_package_name||'.'||l_function_name,60);
1836
1837
1838
1839 loop
1840 fetch csr3_nicar
1841 into csr3_price
1842 , csr3_mileage_band
1843 , csr3_reg_date
1844 , csr3_fuel_type
1845 , csr3_engine_cc
1846 , csr3_fuel_scale
1847 , csr3_payment
1848 , csr3_co2_emissions
1849 , csr3_end_date
1850 , csr3_start_date;
1851 --
1852 exit when csr3_nicar%notfound;
1853 --
1854
1855 --
1856 /* Initialise message variables to null */
1857
1858 l_message := null;
1859 l_co2_message := null;
1860 l_cc_message := null;
1861
1862 /* Choose either tax year start date or entry start date, whichever is greater */
1863
1864 l_start_date := greatest(g_tax_year_start,csr3_start_date);
1865
1866
1867 /* Choose either entry end date, payroll period end date/tax year end date or employee termination date, */
1868 /* whichever is the least */
1869
1870 l_end_date := least(csr3_end_date,l_end_of_period_date,p_emp_term_date);
1871
1872 hr_utility.set_location(g_package_name||'.'||l_function_name,70);
1873
1874 /* Check all parameters are valid */
1875
1879 hr_utility.raise_error;
1876 if csr3_price <0
1877 then
1878 hr_utility.set_message(800,'HR_7361_LOC_INVALID_PRICE');
1880
1881 elsif csr3_reg_date > g_tax_year_end
1882 then
1883 hr_utility.set_message(800,'HR_7367_LOC_INVALID_REG_DATE');
1884 hr_utility.raise_error;
1885
1886 elsif csr3_reg_date > l_start_date
1887 then
1888 hr_utility.set_message(800,'HR_7367_LOC_INVALID_REG_DATE');
1889 hr_utility.raise_error;
1890
1891 elsif csr3_fuel_scale <0
1892 then
1893 hr_utility.set_message(800,'HR_7368_LOC_INVALID_FUELCHG');
1894 hr_utility.raise_error;
1895
1896 elsif csr3_payment <0
1897 then
1898 hr_utility.set_message(800,'HR_7369_LOC_INVALID_ANN_PAY');
1899 hr_utility.raise_error;
1900 end if;
1901
1902 hr_utility.set_location(g_package_name||'.'||l_function_name,80);
1903
1904 /* Check car price does not exceed the price cap */
1905 if csr3_price > csr1_price_max
1906 then
1907
1908 csr3_price := csr1_price_max;
1909 hr_utility.trace('Price cap applied to car list price.');
1910 l_message := 'Price Cap applied to car price.';
1911
1912 end if;
1913
1914 /* Check if fuel type retrieved is null */
1915 if csr3_fuel_type is null
1916 then
1917
1918 csr3_fuel_type := 'DIESEL';
1919 hr_utility.trace('Fuel type defaulted to Diesel.');
1920 l_message := l_message||'Fuel type defaulted to Diesel.';
1921
1922 end if;
1923
1924 /* Check if engine size retrieved is null */
1925 if csr3_engine_cc is null
1926 then
1927
1928 csr3_engine_cc := 9999;
1929 hr_utility.trace('Defaulted engine size to 9999cc');
1930 l_message := l_message||'Engine size defaulted to 9999cc.';
1931
1932 end if;
1933
1934
1935
1936 hr_utility.set_location(g_package_name||'.'||l_function_name,90);
1937
1938 hr_utility.trace('***************************');
1939 hr_utility.trace('Input parameters to calculation: ');
1940 hr_utility.trace('p_assignment_id: '||p_assignment_id);
1941 hr_utility.trace('p_element_type_id: '||p_element_type_id);
1942 hr_utility.trace('p_business_group_id: '||p_business_group_id);
1943
1944 hr_utility.trace('csr3_start_date: '||l_start_date);
1945 hr_utility.trace('csr3_end_date: '||l_end_date);
1946 hr_utility.trace('csr3_price: '||csr3_price);
1947 hr_utility.trace('csr3_reg_date: '||csr3_reg_date);
1948 hr_utility.trace('csr3_mileage_band: '||csr3_mileage_band);
1949 hr_utility.trace('csr3_fuel_type: '||csr3_fuel_type);
1950 hr_utility.trace('csr3_engine_cc: '||csr3_engine_cc);
1951 hr_utility.trace('csr3_fuel_scale: '||csr3_fuel_scale);
1952 hr_utility.trace('csr3_payment: '||csr3_payment);
1953 hr_utility.trace('csr3_co2_emissions: '||csr3_co2_emissions);
1954 hr_utility.trace('***************************');
1955
1956 IF (csr3_co2_emissions is null AND csr3_fuel_type <> 'BATTERY_ELECTRIC')
1957 OR (csr3_reg_date < l_reg_date_lower_limit AND csr3_fuel_type <> 'BATTERY_ELECTRIC')
1958 THEN
1959 /* Calculate nicable value based on engine size */
1960 hr_utility.trace('Engine size calc');
1961
1962 l_nicable_benefit := pay_gb_nicar_06042002.nicar_nicable_value_non_CO2
1963 ( p_assignment_id => p_assignment_id,
1964 p_element_type_id => p_element_type_id,
1965 p_business_group_id => p_business_group_id,
1966 p_car_price => csr3_price,
1967 p_reg_date => csr3_reg_date,
1968 p_fuel_type => csr3_fuel_type,
1969 p_engine_size => csr3_engine_cc,
1970 p_fuel_scale => csr3_fuel_scale,
1971 p_payment => csr3_payment,
1972 p_start_date => l_start_date,
1973 p_end_date => l_end_date,
1974 p_end_of_period_date => l_end_of_period_date,
1975 p_emp_term_date => p_emp_term_date,
1976 p_session_date => p_session_date,
1977 p_message => l_cc_message);
1978
1979 else
1980 /* Calculate nicable value based on CO2 emissions */
1981 hr_utility.trace('CO2 emissions calc');
1982
1983 l_nicable_benefit := pay_gb_nicar_06042002.nicar_nicable_value_CO2
1984 ( p_assignment_id => p_assignment_id,
1985 p_element_type_id => p_element_type_id,
1986 p_business_group_id => p_business_group_id,
1987 p_car_price => csr3_price,
1988 p_reg_date => csr3_reg_date,
1989 p_fuel_type => csr3_fuel_type,
1990 p_engine_size => csr3_engine_cc,
1991 p_fuel_scale => csr3_fuel_scale,
1992 p_payment => csr3_payment,
1993 p_CO2_emissions => csr3_co2_emissions,
1994 p_start_date => l_start_date,
1995 p_end_date => l_end_date,
1996 p_end_of_period_date => l_end_of_period_date,
1997 p_emp_term_date => p_emp_term_date,
1998 p_session_date => p_session_date,
1999 p_message => l_co2_message);
2000
2001 end if;
2002
2003 hr_utility.set_location(g_package_name||'.'||l_function_name,100);
2004
2008
2005 l_nicable_benefit := trunc((l_nicable_benefit * csr1_ni_rate),2);
2006
2007 hr_utility.trace('l_nicable_benefit: '||l_nicable_benefit);
2009 l_running_total := l_running_total + l_nicable_benefit;
2010
2011
2012 /* Check for messages originating from the CO2 emissions calculation; if they */
2013 /* exist, add them to any messages from the engine size/fuel type/price cap */
2014 /* check section above, then add to message table */
2015
2016 if l_message is not null and l_co2_message is not null
2017 then
2018
2019 l_message := l_message||l_co2_message;
2020 tbl_msg_table(l_msg_count) := 'Entry dated '||l_start_date||': '||l_message;
2021 l_msg_count := l_msg_count + 1;
2022
2023 elsif l_message is not null and l_co2_message is null
2024 then
2025
2026 tbl_msg_table(l_msg_count) := 'Entry dated '||l_start_date||': '||l_message;
2027 l_msg_count := l_msg_count + 1;
2028
2029 elsif l_message is null and l_co2_message is not null
2030 then
2031
2032 tbl_msg_table(l_msg_count) := 'Entry dated '||l_start_date||': '||l_co2_message;
2033 l_msg_count := l_msg_count + 1;
2034
2035 end if;
2036
2037
2038
2039 end loop;
2040 --
2041 close csr3_nicar;
2042 --
2043 hr_utility.set_location(g_package_name||'.'||l_function_name,110);
2044
2045
2046 /* Deal with any messages produced by calculation runs */
2047 /* If 5 or more messages produced then output first 3 messages plus generic message */
2048 /* otherwise output messages to 1 to 4 to fast formula */
2049
2050 hr_utility.trace('Message lines: '||tbl_msg_table.COUNT);
2051
2052 if tbl_msg_table.COUNT >= 5
2053 then
2054
2055 hr_utility.trace('In messages(1)');
2056
2057 p_message_1 := tbl_msg_table(1);
2058 p_message_2 := tbl_msg_table(2);
2059 p_message_3 := tbl_msg_table(3);
2060 p_message_4 := 'More than 4 messages were generated for NI Car in this run.' ||
2061 ' Check element entries for NI Car up to current payroll run date';
2062
2063
2064
2065 else
2066
2067 hr_utility.trace('In messages(2)');
2068
2069 for i in 1..tbl_msg_table.COUNT loop
2070
2071 if i = 1
2072 then
2073 p_message_1 := tbl_msg_table(i);
2074 end if;
2075
2076 if i = 2
2077 then
2078 p_message_2 := tbl_msg_table(i);
2079 end if;
2080
2081 if i = 3
2082 then
2083 p_message_3 := tbl_msg_table(i);
2084 end if;
2085
2086 if i = 4
2087 then
2088 p_message_4 := tbl_msg_table(i);
2089 end if;
2090
2091 end loop;
2092
2093 end if;
2094
2095
2096
2097 hr_utility.trace('l_running_total: '||l_running_total);
2098
2099
2100
2101 --
2102 hr_utility.set_location(g_package_name||'.'||l_function_name,999);
2103
2104 --hr_utility.trace_off;
2105
2106 return l_running_total;
2107
2108
2109 end nicar_main;
2110
2111
2112 /* end of package body */
2113 end pay_gb_nicar_06042002;
[Home] [Help]