[Home] [Help]
PACKAGE BODY: APPS.HR_GBNICAR
Source
1 package body hr_gbnicar as
2 /* $Header: pygbnicr.pkb 115.4 1999/11/12 04:01:34 pkm ship $ */
3 ----------------------------------------------------------------------------------
4 -- --
5 -- NICAR_CLASS1A_YTD --
6 -- --
7 ----------------------------------------------------------------------------------
8 function nicar_class1a_ytd
9 (
10 p_business_group_id number,
11 p_assignment_id number,
12 p_element_type_id number,
13 p_end_of_period_date date,
14 p_term_date date
15 )
16 return number is
17 --
18 -- N.B. When called from FastFormula, p_assignment_id, p_element_type_id
19 --- and p_business_group_id are
20 -- provided via context-set variables.
21 --
22 csr0_session_date date;
23 v_tax_year_start date;
24 v_tax_year_end date;
25 --
26 csr1_price_max number;
27 csr1_ni_rate number;
28 --
29 csr2_element_name varchar2(30);
30 csr2_pr number;
31 csr2_rd number;
32 csr2_rn number;
33 csr2_mb number;
34 csr2_ft number;
35 csr2_cc number;
36 csr2_fs number;
37 csr2_ap number;
38 --
39 v_pri_sec_ind varchar2(1);
40 --
41 csr3_price number;
42 csr3_reg_date date;
43 csr3_mileage_band number;
44 csr3_fuel_scale number;
45 csr3_fuel_type varchar2(10);
46 csr3_engine_cc number;
47 csr3_payment number;
48 csr3_start_date date;
49 csr3_end_date date;
50 --
51 v_fuel_scale number :=0;
52 v_running_total number :=0;
53 --
54 --
55 cursor csr1_globals is
56 SELECT fnd_number.canonical_to_number(LIM.global_value)
57 , fnd_number.canonical_to_number(NIR.global_value)
58 FROM ff_globals_f LIM
59 , ff_globals_f NIR
60 WHERE LIM.global_name = 'NI_CAR_MAX_PRICE'
61 AND csr0_session_date between LIM.effective_start_date and LIM.effective_end_date
62 AND NIR.global_name = 'NI_CAR_CONTRIB_RATE'
63 AND csr0_session_date between NIR.effective_start_date and NIR.effective_end_date;
64 --
65 cursor csr2_pri_sec is
66 SELECT E_TL.element_name
67 , IPR.input_value_id
68 , IRD.input_value_id
69 , IRN.input_value_id
70 , IMB.input_value_id
71 , IFT.input_value_id
72 , ICC.input_value_id
73 , IFS.input_value_id
74 , IAP.input_value_id
75 FROM pay_input_values_f IPR
76 , pay_input_values_f IRD
77 , pay_input_values_f IRN
78 , pay_input_values_f IMB
79 , pay_input_values_f IFT
80 , pay_input_values_f ICC
81 , pay_input_values_f IFS
82 , pay_input_values_f IAP
83 , pay_element_types_f_tl E_TL
84 , pay_element_types_f E
85 WHERE E_TL.element_type_id = E.element_type_id
86 AND E.element_type_id = p_element_type_id
87 AND userenv('LANG') = E_TL.language
88 AND IPR.element_type_id = E.element_type_id
89 AND IPR.name = 'Price'
90 AND IRD.element_type_id =E.element_type_id
91 AND IRD.name = 'Registration Date'
92 AND IRN.element_type_id = E.element_type_id
93 AND IRN.name = 'Registration Number'
94 AND IMB.element_type_id = E.element_type_id
95 AND IMB.name = 'Mileage Band'
96 AND IFT.element_type_id = E.element_type_id
97 AND IFT.name = 'Fuel Type'
98 AND ICC.element_type_id = E.element_type_id
99 AND ICC.name = 'Engine cc'
100 AND IFS.element_type_id = E.element_type_id
101 AND IFS.name = 'Fuel Scale'
102 AND IAP.element_type_id = E.element_type_id
103 AND IAP.name = 'Payment'
104 AND csr0_session_date between E.effective_start_date and E.effective_end_date;
105 --
106 --
107 cursor csr3_nicar is
108 -- bug 504994 changed the entry cursor to use a single join on values table
109 -- to improve performance
110 select
111 max(decode(V.input_value_id,csr2_pr,
112 fnd_number.canonical_to_number(V.Screen_entry_value),null) ) csr3_price
113 ,max(decode(V.input_value_id, csr2_mb,
114 fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_mileage_band
115 ,max(decode(V.input_value_id,csr2_rd,
116 fnd_date.canonical_to_date(V.Screen_entry_value),null)) csr3_reg_date
117 ,max(decode(V.input_value_id,csr2_ft,
118 V.Screen_entry_value,null)) csr3_fuel_type
119 ,max(decode(V.input_value_id,csr2_cc,
120 nvl(fnd_number.canonical_to_number(v.Screen_entry_value),0),null)) csr3_engine_cc
121 ,max(decode(V.input_value_id,csr2_fs,
122 fnd_number.canonical_to_number(V.Screen_entry_value),null)) csr3_fuel_scale
123 ,max(decode(V.input_value_id,csr2_ap,
124 nvl(fnd_number.canonical_to_number(V.Screen_entry_value),0),null)) csr3_payment
125 ,EENT.effective_end_date csr3_end_date
126 ,EENT.effective_start_date csr3_start_date
127 FROM pay_element_entries_f EENT
128 , pay_element_links_f LINK
129 , pay_element_entry_values_f V
130 WHERE EENT.effective_end_date >= v_tax_year_start
131 AND EENT.effective_start_date <=
132 least(p_term_date,p_end_of_period_date,v_tax_year_end)
133 AND EENT.assignment_id = p_assignment_id
134 AND LINK.element_type_id = p_element_type_id
135 AND EENT.element_link_id = LINK.element_link_id
136 AND EENT.effective_start_date >= LINK.effective_start_date
137 AND EENT.effective_end_date <= LINK.effective_end_date
138 AND EENT.entry_type = 'E'
139 AND V.Element_entry_id = EENT.element_entry_id
140 AND V.Effective_start_date = EENT.effective_start_date
141 group by EENT.effective_end_date, EENT.effective_start_date;
142 --
143 --
144 BEGIN
145 --
146 -- Get the session date
147 csr0_session_date := nicar_session_date(0);
148 --
149 --
150 -- Get the tax year start and end dates from the session date;
151 --
152 v_tax_year_start := uk_tax_yr_start(csr0_session_date);
153 v_tax_year_end := uk_tax_yr_end(csr0_session_date);
154 --
155 --
156 -- Get the max allowable price, and the contrib rate
157 --
158 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',10);
159 open csr1_globals;
160 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',20);
161 fetch csr1_globals
162 into csr1_price_max
163 , csr1_ni_rate;
164 close csr1_globals;
165 --
166 --
167 -- Get the element_name for the element type id, and all the associated
168 -- input value ids.
169 --
170 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',30);
171 open csr2_pri_sec;
172 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',40);
173 fetch csr2_pri_sec
174 into csr2_element_name
175 , csr2_pr
176 , csr2_rd
177 , csr2_rn
178 , csr2_mb
179 , csr2_ft
180 , csr2_cc
181 , csr2_fs
182 , csr2_ap
183 ;
184 close csr2_pri_sec;
185 --
186 --
187 -- Set the primary/Secondary indicator according to the element name
188 --
189 if csr2_element_name = 'NI Car Primary'
190 then
191 v_pri_sec_ind := 'P';
192 else
193 v_pri_sec_ind := 'S';
194 end if;
195 --
196 --
197 -- Get the required details for all company car benefits for the assignment
198 --
199 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',50);
200 open csr3_nicar;
201 --
202 hr_utility.set_location('hr_gbnicar.nicar_class1a_ytd',60);
203 loop
204 fetch csr3_nicar
205 into csr3_price
206 , csr3_mileage_band
207 , csr3_reg_date
208 , csr3_fuel_type
209 , csr3_engine_cc
210 , csr3_fuel_scale
211 , csr3_payment
212 , csr3_end_date
213 , csr3_start_date;
214 --
215 exit when csr3_nicar%notfound;
216 --
217 --
218 -- If appropriate, get fuel scale charge from user table
219 -- A manually entered fuel scale value will always take precedence
220 -- so check it first.
221 -- If no fuel scale entered, check fuel type and engine cc
222 -- If valid, get the right charge from the table
223 --
224 if csr3_fuel_scale is not null
225 then
226 v_fuel_scale := csr3_fuel_scale;
227 else
228 if csr3_fuel_type is null
229 then
230 v_fuel_scale := 0;
231 else
232 v_fuel_scale :=
233 fnd_number.canonical_to_number(hruserdt.get_table_value(p_business_group_id,
234 'FUEL_SCALE',
235 csr3_fuel_type,
236 csr3_engine_cc,
237 csr0_session_date));
238 end if;
239 end if;
240 --
241 v_running_total := v_running_total +
242 ( trunc (csr1_ni_rate *
243 trunc( nicar_niable_value(csr3_price,
244 csr1_price_max,
245 csr3_mileage_band,
246 v_pri_sec_ind,
247 csr3_reg_date,
248 v_fuel_scale,
249 csr3_payment,
250 greatest(csr3_start_date,
251 v_tax_year_start),
252 least(csr3_end_date,
253 p_end_of_period_date,
254 p_term_date),
255 v_tax_year_end)
256 ),2));
257 end loop;
258 --
259 close csr3_nicar;
260 --
261 RETURN v_running_total;
262 --
263 end nicar_class1a_ytd;
264 ----------------------------------------------------------------------------------
265 -- --
266 -- NICAR_DAYS_BETWEEN --
267 -- --
268 ----------------------------------------------------------------------------------
269 function nicar_days_between
270 (p_start_date date,
271 p_end_date date)
272 return number is
273 --
274 v_start_feb date := last_day(to_date('01-02-'||to_char(p_start_date,'YYYY'),'DD-MM-YYYY'));
275 v_end_feb date := last_day(to_date('01-02-'||to_char(p_end_date,'YYYY'),'DD-MM-YYYY'));
276 v_start_ld number(2) := 0;
277 v_end_ld number(2) := 0;
278 v_days_between number := 0;
279 --
280 begin
281 v_days_between := p_end_date - p_start_date +1;
282 v_start_ld := to_number(to_char(v_start_feb,'DD'));
283 v_end_ld := to_number(to_char(v_end_feb,'DD'));
284 --
285 if (v_start_ld = 29 and v_start_feb between p_start_date and p_end_date) or
286 (v_end_ld = 29 and v_end_feb between p_start_date and p_end_date) then
287 v_days_between := v_days_between -1;
288 end if;
289 --
290 return v_days_between;
291 --
292 end nicar_days_between;
293 ----------------------------------------------------------------------------------
294 -- --
295 -- NICAR_NIABLE_VALUE --
296 -- --
297 ----------------------------------------------------------------------------------
298 function nicar_niable_value
299 (p_price number,
300 p_price_cap number,
301 p_mileage_factor number,
302 p_pri_sec_ind char,
303 p_reg_date date,
304 p_fuel_scale number,
305 p_ann_payment number,
306 p_start_date date,
307 p_end_date date,
308 p_tax_end_date date)
309 return number is
310 --
311 v_price number;
312 v_mileage_factor number;
313 v_age_factor number;
314 v_days number;
315 v_net_car_benefit number;
316 v_niable_car_benefit number;
317
318 v_reg_date date;
319 v_start_date date;
320 v_end_date date;
321 v_tax_end_date date;
322 --
323 csr1_value varchar2(20);
324 csr1_car_benefit number;
325 --
326 cursor csr1_car_ben (cp_age_factor IN NUMBER,
327 cp_primary_ind IN VARCHAR2,
328 cp_mileage_factor IN NUMBER,
329 cp_date IN DATE) is
330 SELECT puc.value
331 FROM pay_user_column_instances_f puc,
332 pay_user_columns col,
333 pay_user_rows_f ur,
334 pay_user_tables tab
335 WHERE tab.user_table_id = col.user_table_id
336 AND col.user_column_id = puc.user_column_id
337 AND puc.user_row_id = ur.user_row_id
338 AND tab.user_table_name = 'NI_CAR_BENEFIT'
339 --AGE BAND
340 AND col.user_column_name =
341 DECODE(cp_primary_ind,
342 'P',DECODE(cp_age_factor,
343 2,'CAR_1_OVER_4','CAR_1_UNDER_4'),
344 'S',DECODE(cp_age_factor,
345 2,'CAR_2_OVER_4','CAR_2_UNDER_4'))
346 --MILEAGE BAND
347 AND ur.user_row_id = (SELECT ur.user_row_id
348 FROM pay_user_column_instances_f puc,
349 pay_user_columns col,
350 pay_user_tables tab,
351 pay_user_rows_f ur
352 WHERE col.user_table_id = tab.user_table_id
353 AND col.user_column_id = puc.user_column_id
354 AND ur.user_row_id = puc.user_row_id
355 AND tab.user_table_name = 'NI_CAR_BENEFIT'
356 AND col.user_column_name = 'LABEL'
357 AND cp_mileage_factor = to_number(puc.value)
358 AND cp_date between
359 puc.effective_start_date AND puc.effective_end_date
360 AND cp_date between
361 ur.effective_start_date AND ur.effective_end_date)
362 --
363 AND cp_date BETWEEN
364 puc.effective_start_date AND puc.effective_end_date
365 AND cp_date BETWEEN
366 ur.effective_start_date AND ur.effective_end_date;
367 --
368
369 begin
370 --
371 -- Make sure the mileage factor is an integer,
372 -- also strip any time element from the date parameters
373 --
374 v_mileage_factor := trunc(p_mileage_factor);
375 v_reg_date := trunc(p_reg_date);
376 v_start_date := trunc(p_start_date);
377 v_end_date := trunc(p_end_date);
378 v_tax_end_date := trunc(p_tax_end_date);
379 --
380 if p_price <0
381 then
382 hr_utility.set_message(801,'HR_7361_LOC_INVALID_PRICE');
383 hr_utility.raise_error;
384 elsif v_mileage_factor not between 1 and 3
385 then
386 hr_utility.set_message(801,'HR_7366_LOC_INVALID_MILEAGE');
387 hr_utility.raise_error;
388 elsif v_reg_date > v_tax_end_date
389 then
390 hr_utility.set_message(801,'HR_7367_LOC_INVALID_REG_DATE');
391 hr_utility.raise_error;
392 elsif v_reg_date > v_start_date
393 then
394 hr_utility.set_message(801,'HR_7367_LOC_INVALID_REG_DATE');
395 hr_utility.raise_error;
396 elsif p_fuel_scale <0
397 then
398 hr_utility.set_message(801,'HR_7368_LOC_INVALID_FUELCHG');
399 hr_utility.raise_error;
400 elsif p_ann_payment <0
401 then
402 hr_utility.set_message(801,'HR_7369_LOC_INVALID_ANN_PAY');
403 hr_utility.raise_error;
404 end if;
405 --
406 -- Check price against price cap
407 --
408 if p_price > p_price_cap
409 then
410 v_price := p_price_cap;
411 else
412 v_price := p_price;
413 end if;
414 --
415 -- Set age factor according to whether or not registration date
416 -- is more than 4 years older than the tax year end date
417 --
418 if add_months(v_tax_end_date,-48) > v_reg_date
419 then
420 v_age_factor := 2;
421 else
422 v_age_factor := 3;
423 end if;
424 --
425 -- Derive availability days from start and end dates
426 --
427 v_days := hr_gbnicar.nicar_days_between (v_start_date,v_end_date);
428 --
429 -- Now derive the net car benefit
430 --
431 hr_utility.set_location('hr_gbnicar.nicar_niable_value',10);
432 open csr1_car_ben(v_age_factor,
433 p_pri_sec_ind,
434 v_mileage_factor,
435 v_tax_end_date);
436 hr_utility.set_location('hr_gbnicar.nicar_niable_value',20);
437 fetch csr1_car_ben
438 into csr1_value;
439 close csr1_car_ben;
440 --
441 csr1_car_benefit := to_number(csr1_value);
442 --
443 v_net_car_benefit := (v_price * csr1_car_benefit)/100;
444 --
445 -- Now adjust the net car benefit by the annual payment.
446 -- Note that under current legislation, any payment in excess
447 -- of the net car benefit is NOT allowed to reduce the fuel
448 -- scale charge, and therefore any negative result here must
449 -- be forced to zero before adding the fuel scale charge
450 --
451 v_net_car_benefit := v_net_car_benefit - p_ann_payment;
452 if v_net_car_benefit < 0
453 then
454 v_net_car_benefit := 0;
455 end if;
456 --
457 -- Finally derive niable car benefit from net car benefit
458 -- and fuel scale charge, and pro-rate according to
459 -- availability
460 --
461 v_niable_car_benefit := trunc((v_net_car_benefit + p_fuel_scale)
462 * v_days / 365);
463 --
464 return v_niable_car_benefit;
465 --
466 end nicar_niable_value;
467 --
468 ----------------------------------------------------------------------------------
469 -- --
470 -- NICAR_PAYMENT_YTD --
471 -- --
472 ----------------------------------------------------------------------------------
473 function nicar_payment_ytd
474 (
475 p_assignment_id number,
476 p_element_type_id number,
477 p_end_of_period_date date,
478 p_term_date date
479 )
480 return number is
481 --
482 -- N.B. p_assignment_id and p_element_type_id are provided via
483 -- context-set variables
484 --
485 f_tax_year_start date;
486 f_tax_year_end date;
487 --
488 c_session_date date;
489 c_start_date date;
490 c_end_date date;
491 c_payment number;
492 c_price number;
493 --
494 v_start_date date;
495 v_end_date date;
496 v_days number := 0;
497 --
498 f_running_total number :=0;
499 --
500 cursor c1_nicar_payment is
501 -- bug 504994 changed the entry cursor to use a single join on values table
502 -- to improve performance
503 SELECT max(decode(I.name,'Payment',
504 nvl(fnd_number.canonical_to_number(V.screen_entry_value),0),null)) Payment
505 , max(decode(I.name,'Price',
506 fnd_number.canonical_to_number(V.screen_entry_value),null)) Price
507 , EENT.effective_end_date
508 , EENT.effective_start_date
509 FROM pay_element_entries_f EENT
510 , pay_element_links_f LINK
511 , pay_element_entry_values_f V
512 , pay_input_values_x I
513 WHERE EENT.effective_end_date >= f_tax_year_start
514 AND EENT.effective_start_date <=
515 least(p_term_date,p_end_of_period_date,f_tax_year_end)
516 AND EENT.assignment_id = p_assignment_id
517 AND LINK.element_type_id = p_element_type_id
518 AND EENT.element_link_id = LINK.element_link_id
519 AND EENT.effective_start_date >= LINK.effective_start_date
520 AND EENT.effective_end_date <= LINK.effective_end_date
521 AND EENT.entry_type = 'E'
522 AND I.element_type_id = p_element_type_id
523 + decode(EENT.element_entry_id,0,0,0)
524 AND V.element_entry_id = EENT.element_entry_id
525 AND V.input_value_id = I.input_value_id
526 + decode(EENT.element_entry_id,0,0,0)
527 AND V.effective_start_date = EENT.effective_start_date
528 AND V.effective_end_date = EENT.effective_end_date
529 group by EENT.effective_end_date, EENT.effective_start_date;
530 --
531 begin
532 --
533 -- Get the session date
534 --
535 c_session_date := nicar_session_date(0);
536 --
537 -- Now get the tax year start and end dates from the session date;
538 --
539 f_tax_year_start := uk_tax_yr_start(c_session_date);
540 f_tax_year_end := uk_tax_yr_end(c_session_date);
541 --
542 -- Get the sum of all annual payments and pro-rate
543 --
544 hr_utility.set_location('hr_gbnicar.nicar_payment_ytd',10);
545 open c1_nicar_payment;
546 --
547 hr_utility.set_location('hr_gbnicar.nicar_payment_ytd',20);
548 loop
549 fetch c1_nicar_payment
550 into c_payment,
551 c_price,
552 c_end_date,
553 c_start_date;
554 exit when c1_nicar_payment%notfound;
555 --
556 -- If the price found is 0, don't add the annual payment to the
557 -- running total
558 --
559 if c_price >0
560 then
561 v_start_date := greatest(c_start_date,
562 f_tax_year_start);
563 v_end_date := least(p_end_of_period_date,
564 c_end_date,
565 p_term_date);
566 v_days := hr_gbnicar.nicar_days_between(v_start_date, v_end_date);
567
568 f_running_total := f_running_total +
569 trunc((c_payment * v_days / 365),2);
570 end if;
571 end loop;
572 --
573 close c1_nicar_payment;
574 --
575 return f_running_total;
576 --
577 end nicar_payment_ytd;
578 --
579 ----------------------------------------------------------------------------------
580 -- --
581 -- NICAR_SESSION_DATE --
582 -- --
583 ----------------------------------------------------------------------------------
584 function nicar_session_date
585 (p_dummy number)
586 return date is
587 c_session_date date;
588 --
589 cursor c0_effdate is
590 SELECT effective_date
591 FROM fnd_sessions
592 WHERE session_id = userenv('sessionid');
593 --
594 begin
595 --
596 -- Get the session date from fnd_sessions
597 --
598 hr_utility.set_location('hr_gbnicar.nicar_session_date',10);
599 open c0_effdate;
600 hr_utility.set_location('hr_gbnicar.nicar_session_date',20);
601 fetch c0_effdate into c_session_date;
602 if c0_effdate%notfound
603 then
604 c_session_date := trunc(sysdate);
605 end if;
606 close c0_effdate;
607 return c_session_date;
608 end nicar_session_date;
609 --
610 ----------------------------------------------------------------------------------
611 -- --
612 -- UK_TAX_YR_START --
613 -- --
614 ----------------------------------------------------------------------------------
615 function uk_tax_yr_start
616 (p_input_date date)
617 return date is
618 f_year number(4);
619 f_start_dd_mon varchar2(7) := '06-04-';
620 f_tax_year_start date;
621 --
622 begin
623 f_year := to_number(to_char(p_input_date,'YYYY'));
624 --
625 if p_input_date >= to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY')
626 then
627 f_tax_year_start := to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY');
628 else
629 f_tax_year_start := to_date(f_start_dd_mon||to_char(f_year -1),'DD-MM-YYYY');
630 end if;
631 --
632 return f_tax_year_start;
633 end uk_tax_yr_start;
634 --
635 ----------------------------------------------------------------------------------
636 -- --
637 -- UK_TAX_YR_END --
638 -- --
639 ----------------------------------------------------------------------------------
640 function uk_tax_yr_end
641 (p_input_date date)
642 return date is
643 f_year number(4);
644 f_end_dd_mon varchar2(7) := '05-04-';
645 f_tax_year_end date;
646 --
647 begin
648 f_year := to_number(to_char(p_input_date,'YYYY'));
649 --
650 if p_input_date > to_date(f_end_dd_mon||to_char(f_year),'DD-MM-YYYY')
651 then
652 f_tax_year_end := to_date(f_end_dd_mon||to_char(f_year +1),'DD-MM-YYYY');
653 else
654 f_tax_year_end := to_date(f_end_dd_mon||to_char(f_year),'DD-MM-YYYY');
655 end if;
656 --
657 return f_tax_year_end;
658 --
659 end uk_tax_yr_end;
660 --
661 --
662 --
663 end hr_gbnicar;