[Home] [Help]
PACKAGE BODY: APPS.PQP_CAR_MILEAGE_FUNCTIONS
Source
1 PACKAGE BODY pqp_car_mileage_functions AS
2 --REM $Header: pqgbcmfn.pkb 120.5.12010000.1 2008/07/28 11:10:54 appldev ship $
3
4 ----------------------------------------------------------------------
5 --Function get_legislation_code
6 ----------------------------------------------------------------------------
7
8 FUNCTION get_legislation_code (p_business_group_id IN NUMBER)
9 RETURN VARCHAR2
10 IS
11
12 l_legislation_code_l per_business_groups.legislation_code%TYPE;
13 BEGIN
14 hr_utility.trace('Enter Legislation code');
15 SELECT legislation_code
16 INTO l_legislation_code_l
17 FROM per_business_groups
18 WHERE business_group_id =p_business_group_id;
19
20 RETURN (l_legislation_code_l);
21 hr_utility.trace('Leaving Legislation code' );
22
23 EXCEPTION
24 ---------
25 WHEN OTHERS THEN
26 RETURN(NULL);
27
28 END;
29
30
31
32
33
34 -----------------------------------------------------------------------------
35 -- FUNCTION CHECK_NUMERIC
36 -----------------------------------------------------------------------------
37 FUNCTION check_numeric(p_value_to_check IN VARCHAR2)
38 RETURN BOOLEAN IS
39
40 l_temp NUMBER;
41
42 BEGIN
43
44 l_temp := to_number(p_value_to_check);
45 RETURN(TRUE);
46
47 EXCEPTION
48
49 WHEN VALUE_ERROR THEN
50 RETURN(FALSE);
51
52 END check_numeric;
53 --------------------------------------------------------------------------------
54 --FUNCTION GET FUEL TYPE
55 ---------------------------------------------------------------------------------
56 FUNCTION get_fuel_type (p_veh_id IN NUMBER)
57 RETURN CHAR
58 IS
59 l_fuel_type pqp_vehicle_details.fuel_type%TYPE;
60 l_fuel VARCHAR2(50);
61 BEGIN
62 SELECT fuel_type
63 INTO l_fuel_type
64 FROM pqp_vehicle_details
65 WHERE vehicle_details_id=p_veh_id;
66
67 IF l_fuel_type='P' THEN
68 l_fuel:='Petrol';
69 RETURN(l_fuel);
70
71 ELSIF l_fuel_type='D' THEN
72 l_fuel:='Diesel';
73 RETURN(l_fuel);
74
75 ELSIF l_fuel_type='L' THEN
76 l_fuel:='Liquid Petroleum Gas';
77 RETURN(l_fuel);
78
79 ELSIF l_fuel_type='H' THEN
80 l_fuel:='Hybrid Electric';
81 RETURN(l_fuel);
82
83 ELSIF l_fuel_type='E' THEN
84 l_fuel:='Electricity Only';
85 RETURN(l_fuel);
86
87
88 ELSIF l_fuel_type='C' THEN
89 l_fuel:='Conversion';
90 RETURN(l_fuel);
91
92 ELSIF l_fuel_type='B' THEN
93 l_fuel:='Bi-Fuel';
94 RETURN(l_fuel);
95
96 END IF;
97
98 EXCEPTION
99 --------
100 WHEN OTHERS THEN
101 RETURN('NONE');
102
103 END get_fuel_type;
104
105 -----------------------------------------------------------------------------
106 -- FUNCTION PQP_GET_RANGE
107 -----------------------------------------------------------------------------
108 FUNCTION pqp_get_range( p_assignment_id IN NUMBER
109 ,p_business_group_id IN NUMBER
110 ,p_payroll_action_id IN NUMBER
111 ,p_table_name IN VARCHAR2
112 ,p_row_or_column IN VARCHAR2
113 ,p_value IN NUMBER
114 ,p_claim_date IN DATE
115 ,p_low_value OUT NOCOPY NUMBER
116 ,p_high_value OUT NOCOPY NUMBER)
117 RETURN NUMBER IS
118
119 CURSOR row_cur (in_claim_date IN DATE) IS
120 SELECT pur.row_low_range_or_name
121 FROM pay_user_rows_f pur,pay_user_tables put
122 WHERE pur.user_table_id = put.user_table_id
123 AND put.user_table_name = p_table_name
124 AND (put.business_group_id = p_business_group_id
125 OR put.legislation_code IS NOT NULL)
126 AND in_claim_date BETWEEN NVL(pur.effective_start_date,in_claim_date)
127 AND NVL(pur.effective_end_date,in_claim_date)
128 ORDER BY pur.row_low_range_or_name;
129
130 CURSOR col_cur (in_claim_date IN DATE)IS
131 SELECT puc.user_column_name
132 FROM pay_user_columns puc
133 ,pay_user_tables put
134 ,pay_user_column_instances_f puci
135 WHERE puc.user_table_id = put.user_table_id
136 AND put.user_table_name = p_table_name
137 AND puc.user_column_id = puci.user_column_id
138 AND (put.business_group_id = p_business_group_id
139 OR put.legislation_code IS NOT NULL)
140 AND (puci.business_group_id = p_business_group_id
141 OR puci.legislation_code IS NOT NULL)
142 AND puci.value IS NOT NULL
143 AND in_claim_date BETWEEN NVL(puci.effective_start_date,in_claim_date)
144 AND NVL(puci.effective_end_date,in_claim_date)
145 ORDER BY puc.user_column_name;
146
147
148 TYPE t_value IS TABLE OF NUMBER
149 INDEX BY BINARY_INTEGER;
150
151 l_value t_value;
152 l_temp NUMBER;
153 l_row pay_user_rows.ROW_LOW_RANGE_OR_NAME%TYPE ;
154 l_col pay_user_columns.USER_COLUMN_NAME%TYPE ;
155 l_val_to_return NUMBER;
156 l_temp_num NUMBER;
157 l_counter NUMBER := 0;
158 l_effective_date DATE;
159 l_val_count NUMBER:=0;
160 BEGIN
161
162 -- This was done because Formula Functions do not accept NULL
163 -- for input. If the date is 01/01/1900 then effective_date = date earned
164 IF TO_CHAR(TRUNC(p_claim_date),'DD/MM/YYYY') = '01/01/1900' THEN
165 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
166 (p_payroll_action_id));
167 ELSE
168 l_effective_date := p_claim_date;
169 END IF;
170
171 IF p_row_or_column NOT IN ('ROW','COL') THEN
172 --hr_utility.RAISE;
173 return -1;
174 ELSIF p_row_or_column = 'ROW' THEN
175 OPEN row_cur(l_effective_date);
176 LOOP
177 FETCH row_cur INTO l_row;
178 IF row_cur%ROWCOUNT > 0 THEN
179 -- Check if the value fetched is numeric
180 IF CHECK_NUMERIC(l_row) THEN
181 l_counter := l_counter + 1;
182 -- First time assign first value as high and low.
183 --*****************************************--
184 /* IF l_counter = 1 THEN
185 p_low_value := l_row;
186 p_high_value := l_row;
187 ELSIF l_counter > 1 THEN
188 IF p_value > p_high_value THEN
189 p_high_value := l_row;
190 ELSIF l_row < p_high_value AND l_row >= p_value THEN
191 p_high_value := l_row;
192 ELSIF l_row < p_low_value and l_row < p_value THEN
193 p_low_value := l_row;
194 ELSIF p_low_value = p_high_value
195 OR p_low_value > p_high_value THEN
196 p_low_value := 0;
197 END IF;
198 END IF;*/
199 --*****************************************--
200
201 l_value(l_counter):=to_number(l_row);
202 END IF;
203 ELSIF row_cur%ROWCOUNT = 0 THEN
204 close row_cur;
205 return -1;
206 END IF;
207 EXIT WHEN row_cur%NOTFOUND;
208 END LOOP;
209 l_value(l_value.count+1):=p_value;
210 IF l_counter=0 THEN
211 RETURN(-1);
212 END if;
213
214 /*IF p_value > p_high_value THEN
215 p_high_value := 0;
216 p_low_value := 0;
217 END IF;*/
218
219 CLOSE row_cur;
220
221 FOR i IN 1..l_value.count-1
222 LOOP
223 FOR j IN i+1..l_value.count
224 LOOP
225 IF l_value(i)>=l_value(j) THEN
226 l_temp:=l_value(i);
227 l_value(i):=l_value(j);
228 l_value(j):=l_temp;
229 END IF;
230 END LOOP;
231 END LOOP;
232
233
234 FOR i IN 1..l_value.count
235 LOOP
236
237 IF p_value=l_value(i) THEN
238 l_val_count:=i;
239 EXIT;
240 END IF;
241 END LOOP;
242
243 IF l_val_count=l_value.count AND l_val_count<>1 THEN
244 p_high_value:=0 ;---l_value(l_value.count-1);
245 p_low_value :=0; --l_value(l_value.count-1);
246 ELSIF
247 l_val_count=1 AND p_value=l_value(1) THEN
248 p_high_value:=l_value(2);
249 p_low_value:=0;
250 ELSIF l_val_count=1 AND l_value.count=1 THEN
251 p_high_value:=0;
252 p_low_value:=0;
253
254 ELSE
255
256 p_high_value:=l_value(l_val_count+1);
257 p_low_value :=l_value(l_val_count-1);
258
259
260
261 END IF;
262
263
264 RETURN(l_val_to_return);
265
266 ELSIF p_row_or_column = 'COL' THEN
267 OPEN col_cur(l_effective_date);
268 LOOP
269 FETCH col_cur INTO l_col;
270 IF col_cur%ROWCOUNT > 0 THEN
271 -- Check if the value fetched is numeric
272 IF CHECK_NUMERIC(l_col) THEN
273 l_counter := l_counter + 1;
274 -- First time assign first value as high and low.
275 IF l_counter = 1 THEN
276 p_low_value := l_col;
277 p_high_value := l_col;
278 ELSIF l_counter > 1 THEN
279 IF p_value > p_high_value THEN
280 p_high_value := l_col;
281 ELSIF l_col < p_high_value AND l_col >= p_value THEN
282 p_high_value := l_col;
283 ELSIF l_col < p_low_value and l_col < p_value THEN
284 p_low_value := l_col;
285 ELSIF p_low_value = p_high_value
286 OR p_low_value > p_high_value THEN
287 p_low_value := 0;
288 END IF;
289 END IF;
290 END IF;
291 ELSIF col_cur%ROWCOUNT = 0 THEN
292 close col_cur;
293 return -1;
294 END IF;
295 EXIT WHEN col_cur%NOTFOUND;
296 END LOOP;
297
298 IF p_value > p_high_value THEN
299 p_high_value := 0;
300 p_low_value := 0;
301 END IF;
302
303 CLOSE col_cur;
304
305 RETURN(l_val_to_return);
306
307 -- OLD CODE
308 /* LOOP
309 FETCH col_cur INTO l_col;
310 EXIT WHEN col_cur%NOTFOUND;
311 -- Check if the value fetched is numeric
312 IF CHECK_NUMERIC(l_col) THEN
313 -- Check if it is less than or equal to the input ( p_value)
314 IF p_value <= l_col THEN
315 -- Assign value to temp variable
316 l_temp_num := to_number(l_col);
317 IF l_col <= NVL(l_val_to_return,l_temp_num) THEN
318 l_val_to_return := to_number(l_col);
319 END IF ;
320 END IF;
321 END IF;
322 END LOOP;
323 CLOSE col_cur;
324
325 RETURN(l_val_to_return);
326 */
327 -- OLD CODE
328
329 END IF;
330
331 EXCEPTION
332 -- Code exception here
333 WHEN OTHERS THEN
334 p_low_value := 0;
335 p_high_value := 0;
336 raise;
337
338 END pqp_get_range;
339
340 ----------------------------------------------------------------------------
341 --FUNCTION get_config_info
342 ---------------------------------------------------------------------------
343 FUNCTION get_config_info (p_business_group_id IN NUMBER
344 ,p_info_type IN VARCHAR2
345 )
346 RETURN VARCHAR2
347 IS
348
349 CURSOR c_get_config_value_rates (cp_leg_code VARCHAR2)
350 IS
351 SELECT PCV_INFORMATION13 info
352 FROM pqp_configuration_values pcv
353 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
354 AND (pcv.business_group_id=p_business_group_id OR
355 ( legislation_code = 'GB' AND
356 NOT EXISTS
357 (SELECT 'X' from pqp_configuration_values pcv
358 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
359 AND pcv.business_group_id=p_business_group_id ))) ;
360
361
362 CURSOR c_get_config_value_taxinf (cp_leg_code VARCHAR2)
363 IS
364 SELECT PCV_INFORMATION14 info
365 FROM pqp_configuration_values pcv
366 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
367 AND (pcv.business_group_id=p_business_group_id OR
368 ( legislation_code = 'GB' AND
369 NOT EXISTS
370 (SELECT 'X' from pqp_configuration_values pcv
371 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
372 AND pcv.business_group_id=p_business_group_id ))) ;
373
374
375 CURSOR c_get_config_value_puiinf (cp_leg_code VARCHAR2)
376 IS
377 SELECT PCV_INFORMATION15 info
378 FROM pqp_configuration_values pcv
379 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
380 AND (pcv.business_group_id=p_business_group_id OR
381 ( legislation_code = 'GB' AND
382 NOT EXISTS
383 (SELECT 'X' from pqp_configuration_values pcv
384 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
385 AND pcv.business_group_id=p_business_group_id ))) ;
386
387
388 l_get_config_value c_get_config_value_rates%ROWTYPE;
389 l_legislation_code pqp_configuration_values.legislation_code%TYPE;
390 BEGIN
391 l_legislation_code := pqp_car_mileage_functions.
392 get_legislation_code (p_business_group_id);
393 IF p_info_type = 'Rates' THEN
394 OPEN c_get_config_value_rates (l_legislation_code);
395 FETCH c_get_config_value_rates INTO l_get_config_value;
396 CLOSE c_get_config_value_rates;
397
398 ELSIF p_info_type = 'Combined Limit' OR p_info_type = 'Combined Limit Co'
399 OR p_info_type = 'Combined Limit Pvt' THEN
400 OPEN c_get_config_value_taxinf (l_legislation_code);
401 FETCH c_get_config_value_taxinf INTO l_get_config_value;
402 CLOSE c_get_config_value_taxinf;
403
404 ELSIF p_info_type = 'Professional User' THEN
405 OPEN c_get_config_value_puiinf (l_legislation_code);
406 FETCH c_get_config_value_puiinf INTO l_get_config_value;
407 CLOSE c_get_config_value_puiinf;
408 END IF;
409
410 RETURN (NVL(l_get_config_value.info,'N'));
411
412 EXCEPTION
413 ---------
414 WHEN OTHERS THEN
415 RETURN ('N');
416
417
418 END get_config_info;
419
420 -----------------------------------------------------------------------------
421 -- FUNCTION PQP_GET_ATTR_VAL
422 -----------------------------------------------------------------------------
423 FUNCTION pqp_get_attr_val( p_assignment_id IN NUMBER
424 ,p_business_group_id IN NUMBER
425 ,p_payroll_action_id IN NUMBER
426 ,p_car_type IN VARCHAR2
427 ,p_cc OUT NOCOPY NUMBER
428 ,p_rates_table OUT NOCOPY VARCHAR2
429 ,p_calc_method OUT NOCOPY VARCHAR2
430 ,p_error_msg OUT NOCOPY VARCHAR2
431 ,p_claim_date IN DATE
432 ,p_fuel_type OUT NOCOPY VARCHAR2
433 ,p_veh_reg IN VARCHAR2 DEFAULT NULL)
434 RETURN NUMBER IS
435
436 l_effective_date DATE;
437 l_car_type VARCHAR2(3);
438
439 /*CURSOR c_get_config_rt (cp_usage_type VARCHAR2
440 ,cp_vehicle_type VARCHAR2
441 ,cp_fuel_type VARCHAR2
442 )
443 IS
444 SELECT pcv.pcv_information5 rates_type
445 FROM pqp_configuration_values pcv
446 WHERE business_group_id=p_business_group_id
447 AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
448 AND (pcv_information2=cp_usage_type
449 OR pcv_information2 IS NULL)
450 AND (pcv_information3=cp_vehicle_type
451 OR pcv_information3 IS NULL)
452 AND (pcv_information4=cp_fuel_type
453 OR pcv_information4 IS NULL)
454 AND pcv.pcv_information5 IS NOT NULL ; */
455
456 ---just to create a temp var for the dyn
457 ---curor.
458 CURSOR c_get_config_rt_temp
459 IS
460 SELECT pcv.aat_information5 rates_type
461 FROM pqp_assignment_attributes_f pcv
462 WHERE rownum=1;
463
464 CURSOR c_get_attr_val_temp
465 IS
466 SELECT pva.company_car_calc_method calculation_method
467 ,pva.private_car rates_table_id
468 ,pvr.engine_capacity_in_cc engine_capacity_in_cc
469 ,pvr.fuel_type fuel_type
470 ,pvr.vehicle_type default_vehicle
471 ,pvr.vehicle_type vehicle_type
472 FROM pqp_vehicle_details pvr
473 ,pqp_assignment_attributes_f pva
474 WHERE rownum=1;
475
476 CURSOR c_get_config_info_temp
477 IS
478 SELECT pcv.aat_information1 calculation_method
479 FROM pqp_assignment_attributes_f pcv
480 WHERE rownum=1;
481
482 /*CURSOR c_get_attr_val
483 IS
484 SELECT pva.calculation_method
485 ,pva.rates_table_id
486 ,pvr.engine_capacity_in_cc
487 ,pvr.fuel_type
488 ,pva.default_vehicle
489 ,pvr.vehicle_type
490 FROM pqp_vehicle_repository_f pvr
491 ,pqp_vehicle_allocations_f pva
492 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
493 AND pva.assignment_id=p_assignment_id
494 AND pva.business_group_id=p_business_group_id
495 AND pva.business_group_id=pvr.business_group_id
496 AND pva.usage_type=p_car_type
497 AND pva.usage_type IN ('P','S')
498 AND p_claim_date BETWEEN pva.effective_start_date
499 AND pva.effective_end_date
500 AND p_claim_date BETWEEN pvr.effective_start_date
501 AND pvr.effective_end_date
502 UNION
503 SELECT pva.calculation_method
504 ,pva.rates_table_id
505 ,pvr.engine_capacity_in_cc
506 ,pvr.fuel_type
507 ,pva.default_vehicle
508 ,pvr.vehicle_type
509 FROM pqp_vehicle_repository_f pvr
510 ,pqp_vehicle_allocations_f pva
511 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
512 AND pva.assignment_id=p_assignment_id
513 AND pva.business_group_id=p_business_group_id
514 AND pva.business_group_id=pvr.business_group_id
515 AND pvr.vehicle_ownership='P'
516 AND p_car_type in ('C','E')
517 -- AND decode(p_car_type,'E',pva.usage_type,'C',pva.usage_type,NULL) IS NULL
518 AND ( default_vehicle='Y' or default_vehicle IS NULL or default_vehicle='N')
519 AND p_claim_date BETWEEN pva.effective_start_date
520 AND pva.effective_end_date
521 AND p_claim_date BETWEEN pvr.effective_start_date
522 AND pvr.effective_end_date
523 ;*/
524 /*CURSOR c_get_config_info
525 IS
526 SELECT pcv_information1 calculation_method
527 FROM pqp_configuration_values pcv
528 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
529 AND pcv.legislation_code='GB'
530 AND NOT EXISTS (SELECT 'X'
531 FROM pqp_configuration_values pcv1
532 WHERE pcv1.business_group_id=p_business_group_id)
533 UNION
534 SELECT pcv_information1 calculation_method
535 FROM pqp_configuration_values pcv
536 WHERE pcv_information_category='PQP_VEHICLE_MILEAGE'
537 AND pcv.business_group_id=p_business_group_id;*/
538
539 CURSOR c_get_veh_typ (cp_vehicle_id NUMBER)
540 IS
541 SELECT pvd.vehicle_type
542 FROM pqp_vehicle_details pvd
543 WHERE pvd.vehicle_details_id =cp_vehicle_id
544 AND pvd.business_group_id=p_business_group_id;
545
546 CURSOR ATTR_CUR IS
547 SELECT primary_company_car
548 ,secondary_company_car
549 ,private_car
550 ,company_car_rates_table_id
551 ,company_car_secondary_table_id
552 ,private_car_rates_table_id
553 ,private_car_essential_table_id
554 ,company_car_calc_method
555 ,private_car_calc_method
556 FROM PQP_ASSIGNMENT_ATTRIBUTES_F
557 WHERE assignment_id = p_assignment_id
558 AND decode(TO_CHAR(TRUNC(p_claim_date),'DD/MM/YYYY'),
559 '01/01/1900',l_effective_date,p_claim_date)
560 BETWEEN effective_start_date AND effective_end_date;
561
562 CURSOR c_get_table_name (c_rates_table VARCHAR2)
563 IS
564 SELECT distinct user_table_name
565 FROM pay_user_tables
566 WHERE user_table_id = c_rates_table
567 AND business_group_id=p_business_group_id;
568
569 CURSOR c_exst
570 IS
571 SELECT 'X'
572 FROM fnd_tables ft
573 WHERE ft.application_id=8303
574 AND ft.table_name='PQP_VEHICLE_ALLOCATIONS_F'
575 AND rownum=1;
576
577 CURSOR c_get_attr_val(p_veh_reg VARCHAR2, p_assignment_id NUMBER, p_business_group_id NUMBER, l_car_type VARCHAR2, l_temp_effective_date DATE) IS
578
579 SELECT pva.calculation_method
580 ,pva.rates_table_id
581 ,pvr.engine_capacity_in_cc
582 ,pvr.fuel_type
583 ,pva.default_vehicle
584 ,pvr.vehicle_type
585 FROM pqp_vehicle_repository_f pvr
586 ,pqp_vehicle_allocations_f pva
587 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
588 AND pvr.registration_number=decode(p_veh_reg,'NE',pvr.registration_number,p_veh_reg)
589 AND pva.assignment_id=p_assignment_id
590 AND pva.business_group_id=p_business_group_id
591 AND pva.business_group_id=pvr.business_group_id
592 AND pva.usage_type=l_car_type
593 AND pva.usage_type IN ('P','S')
594 AND l_temp_effective_date BETWEEN pva.effective_start_date
595 AND pva.effective_end_date
596 AND l_temp_effective_date BETWEEN pvr.effective_start_date
597 AND pvr.effective_end_date
598 UNION
599 SELECT pva.calculation_method
600 ,pva.rates_table_id
601 ,pvr.engine_capacity_in_cc
602 ,pvr.fuel_type
603 ,pva.default_vehicle
604 ,pvr.vehicle_type
605 FROM pqp_vehicle_repository_f pvr
606 ,pqp_vehicle_allocations_f pva
607 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
608 AND pvr.registration_number=decode(p_veh_reg,'NE',pvr.registration_number,p_veh_reg)
609 AND pva.assignment_id=p_assignment_id
610 AND pva.business_group_id=p_business_group_id
611 AND pva.business_group_id=pvr.business_group_id
612 AND pvr.vehicle_ownership='P'
613 AND l_car_type in ('C','E')
614 AND ( default_vehicle='Y' or default_vehicle IS NULL
615 OR default_vehicle='N')
616 AND l_temp_effective_date BETWEEN pva.effective_start_date
617 AND pva.effective_end_date
618 AND l_temp_effective_date BETWEEN pvr.effective_start_date
619 AND pvr.effective_end_date;
620
621
622 l_get_veh_typ c_get_veh_typ%ROWTYPE;
623 l_get_config_info c_get_config_info_temp%ROWTYPE;
624 l_get_attr_val c_get_attr_val_temp%ROWTYPE;
625 l_vehicle_details_id PQP_VEHICLE_DETAILS.vehicle_details_id%TYPE;
626 l_rates_table_id NUMBER(9);
627 l_exst VARCHAR2(1);
628 l_rates_table_name pay_user_tables.user_table_name%TYPE;
629 l_get_config_rates c_get_config_rt_temp%ROWTYPE;
630 l_str VARCHAR2(2300);
631 l_str_val VARCHAR2(2300);
632 l_str_info VARCHAR2(2300);
633 TYPE ref_csr IS REF CURSOR;
634 --c_get_attr_val ref_csr;
635 c_get_config_rt ref_csr;
636 c_get_config_info ref_csr;
637 l_vehicle_type VARCHAR2(2);
638 l_err_num NUMBER:=0;
639 l_veh_typ VARCHAR2(3):=NULL;
640 l_temp_effective_date DATE;
641 BEGIN
642 l_effective_date :=TRUNC(pqp_car_mileage_functions.
643 pqp_get_date_paid(p_payroll_action_id));
644 l_vehicle_type := NVL(SUBSTR(p_car_type,2),'C');
645 l_car_type:=SUBSTR(p_car_type,0,1);
646 IF l_vehicle_type='CM' or l_vehicle_type='PM' THEN
647 l_vehicle_type:='M';
648 ELSIF l_vehicle_type ='NE' THEN
649
650 l_vehicle_type:='C';
651 END IF;
652
653 OPEN c_exst;
654 FETCH c_exst INTO l_exst;
655 CLOSE c_exst;
656 IF l_exst IS NULL THEN
657 IF l_car_type NOT IN ('P','S','C','E') THEN
658 p_error_msg := 'Error - Car Type Invalid';
659 RETURN -1;
660 ELSE
661 FOR attr_rec IN attr_cur
662 LOOP
663 IF l_car_type = 'C' THEN
664 l_vehicle_details_id := attr_rec.private_car;
665 l_rates_table_id := attr_rec.private_car_rates_table_id;
666 p_calc_method := NVL(attr_rec.private_car_calc_method,'NE');
667 p_fuel_type :=get_fuel_type(attr_rec.private_car);
668 OPEN c_get_veh_typ (l_vehicle_details_id);
669 FETCH c_get_veh_typ INTO l_get_veh_typ;
670 CLOSE c_get_veh_typ;
671 IF l_get_veh_typ.vehicle_type<>l_vehicle_type THEN
672 -- l_err_num:=-1;
673 null;
674 END IF;
675 EXIT;
676 ELSIF l_car_type = 'E' THEN
677 l_vehicle_details_id := attr_rec.private_car;
678 l_rates_table_id := attr_rec.private_car_essential_table_id;
679 p_calc_method := NVL(attr_rec.private_car_calc_method,'NE');
680 p_fuel_type :=get_fuel_type(attr_rec.private_car);
681 OPEN c_get_veh_typ (l_vehicle_details_id);
682 FETCH c_get_veh_typ INTO l_get_veh_typ;
683 CLOSE c_get_veh_typ;
684 IF l_get_veh_typ.vehicle_type<>l_vehicle_type THEN
685 --l_err_num:=-1;
686 null;
687 END IF;
688 EXIT;
689 ELSIF l_car_type = 'P' THEN
690 l_vehicle_details_id := attr_rec.primary_company_car;
691 l_rates_table_id := attr_rec.company_car_rates_table_id;
692 p_calc_method := NVL(attr_rec.company_car_calc_method,'NE');
693 p_fuel_type :=get_fuel_type(attr_rec.primary_company_car);
694 OPEN c_get_veh_typ (l_vehicle_details_id);
695 FETCH c_get_veh_typ INTO l_get_veh_typ;
696 CLOSE c_get_veh_typ;
697 IF l_get_veh_typ.vehicle_type<>l_vehicle_type THEN
698 l_err_num:=-1;
699
700 END IF;
701 EXIT;
702 ELSIF l_car_type ='S' THEN
703 l_vehicle_details_id := attr_rec.secondary_company_car;
704 l_rates_table_id := attr_rec.company_car_secondary_table_id;
705 p_calc_method := NVL(attr_rec.company_car_calc_method,'NE');
706 p_fuel_type := get_fuel_type(attr_rec.secondary_company_car);
707 OPEN c_get_veh_typ (l_vehicle_details_id);
708 FETCH c_get_veh_typ INTO l_get_veh_typ;
709 CLOSE c_get_veh_typ;
710 IF l_get_veh_typ.vehicle_type<>l_vehicle_type THEN
711 --l_err_num:=-1;
712 null;
713 END IF;
714 EXIT;
715 END IF;
716 END LOOP;
717 END IF;
718
719 IF l_vehicle_details_id IS NOT NULL THEN
720 SELECT engine_capacity_in_cc
721 INTO p_cc
722 FROM PQP_VEHICLE_DETAILS
723 WHERE vehicle_details_id = l_vehicle_details_id;
724 END IF;
725
726 IF l_rates_table_id IS NOT NULL THEN
727 OPEN c_get_table_name (l_rates_table_id);
728 FETCH c_get_table_name INTO p_rates_table;
729 CLOSE c_get_table_name;
730 END IF;
731 IF l_err_num <>-1 THEN
732 IF l_car_type IN ('E','C') THEN
733 p_error_msg := 'SUCCESS';
734 RETURN 0;
735 ELSIF l_car_type IN ('P','S') AND p_cc IS NOT NULL
736 AND p_rates_table IS NOT NULL THEN
737 p_error_msg := 'SUCCESS';
738 RETURN 0;
739 ELSE
740 p_error_msg := 'Error - Unable to find Table or Vehicle ';
741 RETURN -1;
742 END IF;
743 ELSE
744 p_error_msg := 'TYPERR';
745 RETURN -1;
746 END IF;
747
748 ELSE
749
750 IF TO_CHAR(TRUNC(p_claim_date),'DD/MM/YYYY') = '01/01/1900' THEN
751 l_temp_effective_date :=l_effective_date;
752 ELSE
753 l_temp_effective_date :=p_claim_date;
754
755 END IF;
756
757 /*l_str_val:= 'SELECT pva.calculation_method
758 ,pva.rates_table_id
759 ,pvr.engine_capacity_in_cc
760 ,pvr.fuel_type
761 ,pva.default_vehicle
762 ,pvr.vehicle_type
763 FROM pqp_vehicle_repository_f pvr
764 ,pqp_vehicle_allocations_f pva
765 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
766 AND pvr.registration_number='''||p_veh_reg||'''
767 AND pva.assignment_id='||p_assignment_id||
768 ' AND pva.business_group_id='||p_business_group_id||
769 ' AND pva.business_group_id=pvr.business_group_id
770 AND pva.usage_type='''||l_car_type ||'''
771 AND pva.usage_type IN (''P'''||','||'''S'')
772 AND '''||l_temp_effective_date||''' BETWEEN pva.effective_start_date
773 AND pva.effective_end_date
774 AND '''|| l_temp_effective_date||''' BETWEEN pvr.effective_start_date
775 AND pvr.effective_end_date
776 UNION
777 SELECT pva.calculation_method
778 ,pva.rates_table_id
779 ,pvr.engine_capacity_in_cc
780 ,pvr.fuel_type
781 ,pva.default_vehicle
782 ,pvr.vehicle_type
783 FROM pqp_vehicle_repository_f pvr
784 ,pqp_vehicle_allocations_f pva
785 WHERE pvr.vehicle_repository_id=pva.vehicle_repository_id
786 AND pvr.registration_number='''||p_veh_reg||'''
787 AND pva.assignment_id='||p_assignment_id||
788 ' AND pva.business_group_id='||p_business_group_id||
789 ' AND pva.business_group_id=pvr.business_group_id
790 AND pvr.vehicle_ownership=''P''
791 AND '''||l_car_type ||''' in (''C'',''E'')
792 AND ( default_vehicle=''Y'' or default_vehicle IS NULL
793 OR default_vehicle=''N'')
794 AND '''|| l_temp_effective_date ||''' BETWEEN pva.effective_start_date
795 AND pva.effective_end_date
796 AND '''|| l_temp_effective_date ||''' BETWEEN pvr.effective_start_date
797 AND pvr.effective_end_date' ;*/
798
799 OPEN c_get_attr_val(p_veh_reg, p_assignment_id, p_business_group_id, l_car_type, l_temp_effective_date);
800 LOOP
801 FETCH c_get_attr_val INTO l_get_attr_val;
802 EXIT WHEN c_get_attr_val%NOTFOUND;
803 IF l_get_attr_val.calculation_method IS NULL THEN
804 l_str_info :='SELECT pcv_information1 calculation_method
805 FROM pqp_configuration_values pcv
806 WHERE pcv_information_category=''PQP_VEHICLE_MILEAGE''
807 AND pcv.legislation_code=''GB''
808 AND NOT EXISTS (SELECT ''X''
809 FROM pqp_configuration_values pcv1
810 WHERE pcv1.business_group_id='||p_business_group_id ||'
811 -- added to check for this info category only (5632627)
812 AND pcv1.pcv_information_category=''PQP_VEHICLE_MILEAGE'')
813 UNION
814 SELECT pcv_information1 calculation_method
815 FROM pqp_configuration_values pcv
816 WHERE pcv_information_category=''PQP_VEHICLE_MILEAGE''
817 AND pcv.business_group_id='||p_business_group_id;
818
819 OPEN c_get_config_info FOR l_str_info;
820 LOOP
821 FETCH c_get_config_info INTO l_get_config_info;
822 EXIT WHEN c_get_config_info%NOTFOUND;
823 END LOOP;
824 CLOSE c_get_config_info;
825 IF l_car_type <> 'P'OR l_car_type<>'S' THEN
826 IF l_get_attr_val.default_vehicle='Y' THEN
827 EXIT;
828 END IF;
829 END IF;
830 END IF;
831 END LOOP;
832 CLOSE c_get_attr_val;
833 IF l_get_attr_val.rates_table_id IS NOT NULL THEN
834 OPEN c_get_table_name (l_get_attr_val.rates_table_id);
835 FETCH c_get_table_name INTO l_rates_table_name;
836 CLOSE c_get_table_name;
837 ELSE
838 l_str :='SELECT pcv.pcv_information5 rates_type
839 FROM pqp_configuration_values pcv
840 WHERE business_group_id='||p_business_group_id||
841 ' AND pcv.pcv_information_category=''GB_VEHICLE_CALC_INFO''
842 AND (pcv_information2='||'''||l_car_type||'''||
843 ' OR pcv_information2 IS NULL)
844 AND (pcv_information3='||'''||l_get_attr_val.vehicle_type||'''||'
845 OR pcv_information3 IS NULL)
846 AND (pcv_information4='||'''||l_get_attr_val.fuel_type||'''||
847 ' OR pcv_information4 IS NULL)
848 AND pcv.pcv_information5 IS NOT NULL';
849 OPEN c_get_config_rt FOR l_str;
850 FETCH c_get_config_rt INTO l_get_config_rates;
851 CLOSE c_get_config_rt;
852
853 OPEN c_get_table_name (to_number(l_get_config_rates.rates_type));
854 FETCH c_get_table_name INTO l_rates_table_name;
855 CLOSE c_get_table_name;
856
857 END IF;
858 p_cc :=NVL(l_get_attr_val.engine_capacity_in_cc,-1);
859 p_calc_method :=NVL(NVL(l_get_attr_val.calculation_method,
860 l_get_config_info.calculation_method),'NE');
861 p_fuel_type :=l_get_attr_val.fuel_type;
862 p_rates_table:=l_rates_table_name;
863 -- IF l_veh_typ<>l_vehicle_type AND l_veh_typ IS NOT NULL THEN
864 -- l_err_num:=-1;
865 -- END IF;
866 --IF l_err_num=-1 THEN
867 -- p_error_msg := 'TYPERR';
868 -- RETURN -1;
869 -- ELSE
870 p_error_msg := 'SUCCESS';
871 RETURN 0;
872 -- END IF;
873
874 END IF;
875
876 EXCEPTION
877
878 WHEN NO_DATA_FOUND THEN
879
880 -- added by tmehra for nocopy changes.
881 p_cc := -1;
882 p_rates_table := NULL;
883 p_calc_method := NULL;
884 p_fuel_type := NULL;
885
886 p_error_msg := 'Error - Unable to find Table or Vehicle';
887 RETURN -1;
888
889 WHEN OTHERS THEN
890 -- added by tmehra for nocopy changes.
891 p_cc := -1;
892 p_rates_table := NULL;
893 p_calc_method := NULL;
894 p_fuel_type := NULL;
895
896 p_error_msg := 'Error - Unable to find Table or Vehicle';
897 RETURN -1;
898 END pqp_get_attr_val;
899
900 -----------------------------------------------------------------------------
901 -- FUNCTION PQP_GET_PERIOD
902 -- This function will return the number of pay periods for a given claim date.
903 -----------------------------------------------------------------------------
904 FUNCTION pqp_get_period( p_assignment_id IN NUMBER
905 ,p_business_group_id IN NUMBER
906 ,p_payroll_id IN NUMBER
907 ,p_payroll_action_id IN NUMBER
908 ,p_claim_date IN DATE
909 ,p_period_num OUT NOCOPY NUMBER)
910 RETURN NUMBER IS
911
912 CURSOR period_type IS
913 SELECT period_type
914 FROM per_time_periods
915 WHERE payroll_id = p_payroll_id;
916
917 CURSOR period_cur(pdate DATE) IS
918 SELECT period_num, period_type,start_date,end_date
919 FROM per_time_periods
920 WHERE payroll_id = p_payroll_id
921 AND pdate
922 BETWEEN start_date AND end_date;
923
924 CURSOR max_period (pdate DATE) IS
925 SELECT period_num
926 FROM per_time_periods
927 WHERE payroll_id=p_payroll_id
928 AND end_date >=pdate
929 ORDER BY end_date;
930
931 l_period_num per_time_periods.period_num%TYPE;
932 l_period_type per_time_periods.period_type%TYPE;
933 l_multiple NUMBER;
934 l_base_period_type VARCHAR2(100);
935 l_periods NUMBER;
936 l_start_date DATE;
937 l_end_date DATE;
938 l_fiscal_year_begin DATE;
939 l_date DATE;
940 l_max_period NUMBER:=0;
941 c_max_period max_period%ROWTYPE;
942 l_max_period_num NUMBER;
943 l_periodtype per_time_periods.period_type%TYPE;
944 l_effective_date DATE;
945 BEGIN
946 l_effective_date :=TRUNC(pqp_car_mileage_functions.
947 pqp_get_date_paid(p_payroll_action_id));
948 OPEN period_type;
949 LOOP
950 FETCH period_type INTO l_periodtype;
951 EXIT WHEN period_type%NOTFOUND;
952 END LOOP;
953 CLOSE period_type;
954
955 /*Gets the multiple factor to divide period */
956 hr_payrolls.get_period_details( p_proc_period_type => l_periodtype
957 ,p_base_period_type => l_base_period_type
958 ,p_multiple => l_multiple);
959
960 IF p_claim_date=to_date('01/01/1900','DD/MM/RRRR') THEN
961 l_date:=l_effective_date;
962 OPEN period_cur(l_date);
963 LOOP
964 FETCH period_cur INTO l_period_num,l_period_type,l_start_date,l_end_date;
965 EXIT WHEN period_cur%NOTFOUND;
966 END LOOP;
967 CLOSE period_cur;
968
969 OPEN max_period(l_date);
970 LOOP
971 FETCH max_period INTO c_max_period;
972 EXIT WHEN max_period%NOTFOUND;
973 IF l_max_period > c_max_period.period_num THEN
974 EXIT;
975 ELSE
976 l_max_period :=c_max_period.period_num;
977 END IF;
978 END LOOP;
979 CLOSE max_period;
980
981 p_period_num:=l_max_period/l_multiple;
982 RETURN(l_period_num/l_multiple);
983
984 ELSIF (p_claim_date >=TO_DATE('01/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR')
985 AND p_claim_date < TO_DATE('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR'))
986 AND l_effective_date >=TO_DATE('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR') THEN
987
988 SELECT MAX(period_num) INTO l_max_period_num
989 FROM per_time_periods
990 WHERE payroll_id = p_payroll_id
991 AND end_date >= ADD_MONTHS(to_date('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR'),-6)
992 AND end_date <= to_date('06/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR');
993
994 p_period_num:=l_max_period_num/l_multiple;
995 RETURN(l_max_period_num)/l_multiple;
996
997 ELSIF p_claim_date < TO_DATE('01/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR')
998 AND l_effective_date >=TO_DATE('6/04/'||TO_CHAR(TRUNC(p_claim_date),'RRRR'),'DD/MM/RRRR')THEN
999
1000 OPEN max_period(p_claim_date);
1001 LOOP
1002 FETCH max_period INTO c_max_period;
1003 EXIT WHEN max_period%NOTFOUND;
1004 IF l_max_period_num > c_max_period.period_num THEN
1005 EXIT;
1006 ELSE
1007 l_max_period_num :=c_max_period.period_num;
1008 END IF;
1009 END LOOP;
1010 CLOSE max_period;
1011 p_period_num:=l_max_period_num/l_multiple;
1012 RETURN(l_max_period_num)/l_multiple;
1013 ELSE
1014 l_date:=l_effective_date;
1015
1016
1017 OPEN period_cur(l_date);
1018 LOOP
1019 FETCH period_cur INTO l_period_num,l_period_type,l_start_date,l_end_date;
1020 EXIT WHEN period_cur%NOTFOUND;
1021 END LOOP;
1022 CLOSE period_cur;
1023
1024 OPEN max_period(l_date);
1025 LOOP
1026 FETCH max_period INTO c_max_period;
1027 EXIT WHEN max_period%NOTFOUND;
1028 IF l_max_period > c_max_period.period_num THEN
1029 EXIT;
1030 ELSE
1031 l_max_period :=c_max_period.period_num;
1032 END IF;
1033 END LOOP;
1034 CLOSE max_period;
1035
1036 p_period_num:=l_max_period/l_multiple;
1037 RETURN(l_period_num)/l_multiple;
1038 END IF;
1039
1040 -- Added by tmehra for nocopy changes Feb'03
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 p_period_num := NULL;
1045 raise;
1046
1047
1048 END pqp_get_period;
1049 -----------------------------------------------------------------------------
1050 -- FUNCTION PQP_GET_VEH_CC
1051 -----------------------------------------------------------------------------
1052 FUNCTION pqp_get_veh_cc( p_assignment_id IN NUMBER
1053 ,p_business_group_id IN NUMBER
1054 ,p_reg_num IN VARCHAR2)
1055 RETURN NUMBER IS
1056
1057 CURSOR veh_cur IS
1058 SELECT engine_capacity_in_cc
1059 FROM pqp_vehicle_details
1060 WHERE registration_number = p_reg_num;
1061
1062 l_veh_cc NUMBER;
1063
1064 BEGIN
1065
1066 OPEN veh_cur;
1067
1068 FETCH veh_cur INTO l_veh_cc;
1069
1070 IF veh_cur%ROWCOUNT = 0 THEN
1071 -- Message and Exception
1072 CLOSE veh_cur;
1073 RETURN -1;
1074 ELSIF veh_cur%ROWCOUNT > 0 THEN
1075 CLOSE veh_cur;
1076 RETURN(l_veh_cc);
1077 END IF;
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 RETURN -1;
1081 END pqp_get_veh_cc;
1082
1083 -----------------------------------------------------------------------------
1084 -- FUNCTION PQP_GET_YEAR
1085 -----------------------------------------------------------------------------
1086 FUNCTION pqp_get_year( p_assignment_id IN NUMBER
1087 ,p_business_group_id IN NUMBER
1088 ,p_payroll_action_id IN NUMBER
1089 ,p_claim_date IN DATE)
1090 RETURN VARCHAR2 IS
1091
1092 l_mod_value NUMBER;
1093 l_fiscal_year_begin DATE;
1094 l_fiscal_year_end DATE;
1095 l_cur_year VARCHAR2(5);
1096 l_year NUMBER;
1097 l_claim_date DATE;
1098
1099 BEGIN
1100
1101 -- This was done because Formula Functions do not accept NULL
1102 -- for input. If the claim date is 1/01/1900 then claim date = date earned
1103 IF TO_CHAR(TRUNC(p_claim_date),'DD/MM/YYYY') = '01/01/1900' THEN
1104 l_claim_date := TRUNC(pqp_car_mileage_functions.
1105 pqp_get_date_paid(p_payroll_action_id));
1106 ELSE
1107 l_claim_date := p_claim_date;
1108 END IF;
1109
1110 l_cur_year := TO_CHAR(TRUNC(l_claim_date),'RRRR');
1111 l_fiscal_year_begin := TO_DATE(('6/04/'||l_cur_year),'DD/MM/RRRR');
1112 l_fiscal_year_end := TO_DATE(('5/04/'||(TO_NUMBER(l_cur_year)+1)),'DD/MM/RRRR');
1113
1114 IF TRUNC(l_claim_date) BETWEEN l_fiscal_year_begin
1115 AND l_fiscal_year_end THEN
1116 l_year := TO_NUMBER(l_cur_year)+1;
1117 ELSIF l_claim_date < l_fiscal_year_begin THEN
1118 l_year := TO_NUMBER(l_cur_year);
1119 END IF;
1120
1121 l_mod_value := MOD(l_year,2);
1122
1123 IF l_mod_value = 1 THEN
1124 RETURN('O');
1125 ELSIF l_mod_value = 0 THEN
1126 RETURN('E');
1127 END IF;
1128
1129 EXCEPTION
1130
1131 WHEN VALUE_ERROR THEN
1132 RETURN('-1');
1133
1134 END pqp_get_year;
1135
1136 -----------------------------------------------------------------------------
1137 -- FUNCTION PQP_MULTIPLE_ASG
1138 -----------------------------------------------------------------------------
1139 FUNCTION pqp_multiple_asg( p_assignment_id IN NUMBER
1140 ,p_business_group_id IN NUMBER
1141 ,p_payroll_action_id IN NUMBER)
1142 RETURN VARCHAR2 IS
1143
1144 l_start_date DATE;
1145 l_end_date DATE;
1146 l_fiscal_year_begin DATE;
1147 l_fiscal_year_end DATE;
1148 l_cur_year VARCHAR2(5);
1149 l_asg_year VARCHAR2(5);
1150 l_person_id PER_ASSIGNMENTS_F.person_id%TYPE;
1151 l_dummy VARCHAR2(1);
1152 l_effective_date DATE;
1153
1154 BEGIN
1155 l_effective_date :=TRUNC(pqp_car_mileage_functions.
1156 pqp_get_date_paid(p_payroll_action_id));
1157 -- Get the current fiscal year
1158 -- Specific only to GB hence the dates are hard coded
1159
1160
1161 RETURN('TRUE');
1162
1163
1164 END pqp_multiple_asg;
1165
1166 -----------------------------------------------------------------------------
1167 -- FUNCTION PQP_GET_TABLE_VALUE
1168 -----------------------------------------------------------------------------
1169 FUNCTION pqp_get_table_value ( p_bus_group_id IN NUMBER
1170 ,p_payroll_action_id IN NUMBER
1171 ,p_table_name IN VARCHAR2
1172 ,p_col_name IN VARCHAR2
1173 ,p_row_value IN VARCHAR2
1174 ,p_effective_date IN DATE DEFAULT NULL
1175 ,p_error_msg OUT NOCOPY VARCHAR2)
1176 RETURN VARCHAR2 IS
1177
1178 --**********************************************************
1179 -- BEGIN Code from original package HRUSERDT.GET_TABLE_VALUE
1180 -- Added code to handle exceptions and return SUCCESS or ERROR
1181 -- Change made by vjhanak
1182 --**********************************************************
1183
1184 l_effective_date DATE;
1185 l_range_or_match PAY_USER_TABLES.range_or_match%TYPE;
1186 l_table_id PAY_USER_TABLES.user_table_id%TYPE;
1187 l_value PAY_USER_COLUMN_INSTANCES_F.value%TYPE;
1188 g_leg_code VARCHAR2(2);
1189 cached BOOLEAN := FALSE;
1190 g_effective_date DATE := NULL;
1191
1192 CURSOR c_fuel_type (cp_lcode VARCHAR2) IS
1193 SELECT hrl.meaning
1194 FROM hr_lookups hrl
1195 WHERE hrl.lookup_code = cp_lcode
1196 AND hrl.application_id=800
1197 AND lookup_type='PQP_FUEL_TYPE';
1198
1199 l_fuel_type c_fuel_type%ROWTYPE;
1200
1201 BEGIN
1202
1203 -- This is done because in the formula functions NULL
1204 -- is not a valid input.
1205 -- If Effective date is null assign date earned to effective date
1206 IF TO_CHAR(TRUNC(p_effective_date),'DD/MM/YYYY') = '01/01/1900' THEN
1207 l_effective_date := trunc(pqp_car_mileage_functions.
1208 pqp_get_date_paid(p_payroll_action_id));
1209 ELSE
1210 l_effective_date := p_effective_date;
1211 END IF;
1212
1213
1214
1215
1216
1217 -- get the legislation code:
1218 --
1219 BEGIN
1220 hr_utility.set_location (' pqp_get_table_value', 2);
1221 IF cached = FALSE THEN
1222 SELECT legislation_code
1223 INTO g_leg_code
1224 FROM per_business_groups
1225 WHERE business_group_id = p_bus_group_id;
1226 cached := TRUE;
1227 END IF;
1228 END;
1229 --
1230 -- get the type of query to be performed, either range or match
1231 --
1232 hr_utility.set_location ('hruserdt.get_table_value', 3);
1233 SELECT range_or_match, user_table_id
1234 INTO l_range_or_match, l_table_id
1235 FROM pay_user_tables
1236 WHERE upper(user_table_name) = upper(p_table_name)
1237 AND nvl (business_group_id,
1238 p_bus_group_id) = p_bus_group_id
1239 AND nvl(legislation_code, g_leg_code) = g_leg_code;
1240 --
1241 IF (l_range_or_match = 'M') THEN -- matched
1242 BEGIN
1243 hr_utility.set_location ('hruserdt.get_table_value', 4);
1244 SELECT CINST.value
1245 INTO l_value
1246 FROM pay_user_column_instances_f CINST
1247 , pay_user_columns C
1248 , pay_user_rows_f R
1249 , pay_user_tables TAB
1250 WHERE TAB.user_table_id = l_table_id
1251 AND C.user_table_id = TAB.user_table_id
1252 AND nvl (C.business_group_id,
1253 p_bus_group_id) = p_bus_group_id
1254 AND nvl (C.legislation_code,
1255 g_leg_code) = g_leg_code
1256 AND upper (C.user_column_name) = upper (p_col_name)
1257 AND CINST.user_column_id = C.user_column_id
1258 AND R.user_table_id = TAB.user_table_id
1259 AND l_effective_date between R.effective_start_date
1260 AND R.effective_end_date
1261 AND nvl (R.business_group_id,
1262 p_bus_group_id) = p_bus_group_id
1263 AND nvl (R.legislation_code,
1264 g_leg_code) = g_leg_code
1265 AND decode
1266 (TAB.user_key_units,
1267 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
1268 'N',replace(replace( upper(p_row_value),'_',' '),' '),
1269 'T', replace(replace(upper (p_row_value),'_',' '),' '),
1270 null) =
1271 decode
1272 (TAB.user_key_units,
1273 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
1274 'N', replace(replace(upper(R.row_low_range_or_name),'_',' '),' '),
1275 'T', replace(replace(upper (R.row_low_range_or_name),'_',' '),' '),
1276 null)
1277 AND CINST.user_row_id = R.user_row_id
1278 AND l_effective_date between CINST.effective_start_date
1279 AND CINST.effective_end_date
1280 AND nvl (CINST.business_group_id,
1281 p_bus_group_id) = p_bus_group_id
1282 AND nvl (CINST.legislation_code,
1283 g_leg_code) = g_leg_code;
1284 --
1285 p_error_msg := 'SUCCESS';
1286 RETURN l_value;
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 BEGIN
1290 OPEN c_fuel_type (p_row_value);
1291 FETCH c_fuel_type INTO l_fuel_type;
1292 CLOSE c_fuel_type;
1293
1294 SELECT CINST.value
1295 INTO l_value
1296 FROM pay_user_column_instances_f CINST
1297 , pay_user_columns C
1298 , pay_user_rows_f R
1299 , pay_user_tables TAB
1300 WHERE TAB.user_table_id = l_table_id
1301 AND C.user_table_id = TAB.user_table_id
1302 AND nvl (C.business_group_id,
1303 p_bus_group_id) = p_bus_group_id
1304 AND nvl (C.legislation_code,
1305 g_leg_code) = g_leg_code
1306 AND upper (C.user_column_name) = upper (p_col_name)
1307 AND CINST.user_column_id = C.user_column_id
1308 AND R.user_table_id = TAB.user_table_id
1309 AND l_effective_date between R.effective_start_date
1310 AND R.effective_end_date
1311 AND nvl (R.business_group_id,
1312 p_bus_group_id) = p_bus_group_id
1313 AND nvl (R.legislation_code,
1314 g_leg_code) = g_leg_code
1315 AND decode
1316 (TAB.user_key_units,
1317 'D', to_char(fnd_date.canonical_to_date(l_fuel_type.meaning)),
1318 'T', replace(replace(upper (l_fuel_type.meaning),'_',' '),' '),
1319 null) =
1320 decode
1321 (TAB.user_key_units,
1322 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
1323 'N', replace(replace(upper(R.row_low_range_or_name),'_',' '),' '),
1324 'T', replace(replace(upper (R.row_low_range_or_name),'_',' '),' '),
1325 null)
1326 AND CINST.user_row_id = R.user_row_id
1327 AND l_effective_date between CINST.effective_start_date
1328 AND CINST.effective_end_date
1329 AND nvl (CINST.business_group_id,
1330 p_bus_group_id) = p_bus_group_id
1331 AND nvl (CINST.legislation_code,
1332 g_leg_code) = g_leg_code;
1333 p_error_msg := 'SUCCESS';
1334 RETURN l_value;
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 p_error_msg := 'ERROR';
1338 RETURN('-1');
1339 END;
1340 END;
1341 ELSE -- range
1342 BEGIN
1343 hr_utility.set_location ('hruserdt.get_table_value', 5);
1344 select CINST.value
1345 into l_value
1346 from pay_user_column_instances_f CINST
1347 , pay_user_columns C
1348 , pay_user_rows_f R
1349 , pay_user_tables TAB
1350 where TAB.user_table_id = l_table_id
1351 and C.user_table_id = TAB.user_table_id
1352 and nvl (C.business_group_id,
1353 p_bus_group_id) = p_bus_group_id
1354 and nvl (C.legislation_code,
1355 g_leg_code) = g_leg_code
1356 and upper (C.user_column_name) = upper (p_col_name)
1357 and CINST.user_column_id = C.user_column_id
1358 and R.user_table_id = TAB.user_table_id
1359 and l_effective_date between R.effective_start_date
1360 and R.effective_end_date
1361 and nvl (R.business_group_id,
1362 p_bus_group_id) = p_bus_group_id
1363 and nvl (R.legislation_code,
1364 g_leg_code) = g_leg_code
1365 and fnd_number.canonical_to_number (p_row_value)
1366 between fnd_number.canonical_to_number (R.row_low_range_or_name)
1367
1368 and fnd_number.canonical_to_number (R.row_high_range)
1369 and TAB.user_key_units = 'N'
1370 and CINST.user_row_id = R.user_row_id
1371 and l_effective_date between CINST.effective_start_date
1372 and CINST.effective_end_date
1373 and nvl (CINST.business_group_id,
1374 p_bus_group_id) = p_bus_group_id
1375 and nvl (CINST.legislation_code,
1376 g_leg_code) = g_leg_code;
1377 --
1378 p_error_msg := 'SUCCESS';
1379 return l_value;
1380
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 p_error_msg := 'ERROR';
1384 RETURN('-1');
1385 end;
1386 end if;
1387
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 p_error_msg := 'ERROR';
1391 RETURN('-1');
1392 --
1393 --********************************************************--
1394 -- END Code from original package HRUSERDT.GET_TABLE_VALUE
1395 -- Added code to handle exceptions and return SUCCESS or ERROR
1396 -- Change made by vjhanak
1397 --********************************************************--
1398 END pqp_get_table_value;
1399
1400 -----------------------------------------------------------------------------
1401 -- PQP_CHECK_RATES_TABLE
1402 -----------------------------------------------------------------------------
1403 FUNCTION pqp_check_rates_table(p_business_group_id IN NUMBER
1404 ,p_table_name IN VARCHAR2)
1405 RETURN VARCHAR2 IS
1406
1407 l_dummy VARCHAR2(1);
1408
1409 BEGIN
1410 SELECT 'x'
1411 INTO l_dummy
1412 FROM pay_user_tables
1413 WHERE user_table_name = p_table_name
1414 AND (business_group_id = p_business_group_id OR legislation_code is NOT NULL)
1415 AND rownum = 1;
1416
1417 RETURN ('S');
1418
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 RETURN ('E');
1422
1423 END pqp_check_rates_table;
1424
1425 -----------------------------------------------------------------------------
1426 -- PQP_VALIDATE_DATE
1427 -----------------------------------------------------------------------------
1428 FUNCTION pqp_validate_date(p_date_earned IN DATE
1429 ,p_claim_end_date IN DATE)
1430 RETURN VARCHAR2 IS
1431
1432 l_begin_year NUMBER;
1433 l_begin_date DATE;
1434
1435 BEGIN
1436
1437 IF p_date_earned < to_date('06/04/'||to_char(p_date_earned,'YYYY'),'DD/MM/YYYY') THEN
1438 l_begin_year := to_number(to_char(p_date_earned, 'yyyy')) - 1;
1439 ELSE
1440 l_begin_year := to_number(to_char(p_date_earned, 'yyyy'));
1441 END IF;
1442 --
1443 -- Set expired date to the 6th of April next.
1444 --
1445 l_begin_date := to_date('06/04/' || to_char(l_begin_year), 'DD/MM/YYYY');
1446 --
1447 IF p_date_earned > to_date('05/07/'||to_char(l_begin_date,'yyyy'),'dd/mm/yyyy') AND
1448 p_claim_end_date < l_begin_date THEN
1449 RETURN ('E');
1450 ELSE
1451 RETURN('S');
1452 END IF;
1453
1454 END pqp_validate_date;
1455
1456
1457 ------------------------------------------------------------------------
1458
1459 --Function Max_Limit_Calc
1460 /*This function determines whether the element for proration calculation
1461 needs to be end dated based on max rate reached for that fiscal year*/
1462 ------------------------------------------------------------------------
1463 FUNCTION Max_limit_calc ( p_assignment_id IN NUMBER
1464 ,p_bg_id IN NUMBER
1465 ,p_payroll_action_id IN NUMBER
1466 ,p_prorated_mileage IN NUMBER
1467 ,p_cc IN NUMBER
1468 ,p_claim_date IN date
1469 ,p_total_period IN NUMBER
1470 ,p_cl_period IN NUMBER
1471 ,p_rates_table IN VARCHAR2
1472
1473 )
1474
1475 Return Varchar2 as
1476 l_ltemp_mileage NUMBER:=0;
1477 l_htemp_mileage NUMBER:=0;
1478 l_mile NUMBER:=0;
1479 l_pro_mile NUMBER:=0;
1480 lband NUMBER:=0;
1481 l_hp_mileage NUMBER:=0;
1482 hi_hp_mileage NUMBER:=0;
1483
1484 l_effective_date DATE;
1485 Begin
1486 l_effective_date :=trunc(pqp_car_mileage_functions.
1487 pqp_get_date_paid(p_payroll_action_id));
1488 IF p_cl_period=p_total_period THEN
1489 RETURN('Y');
1490 ELSIF l_effective_date >TO_DATE('05/04/'||to_char(l_effective_date,'RRRR'),'DD/MM/RRRR') AND
1491 p_claim_date < TO_DATE('06/04/'||to_char(l_effective_date,'RRRR'),'DD/MM/RRRR') THEN
1492 RETURN('Y');
1493 END IF;
1494
1495 l_mile:=(p_prorated_mileage*p_cl_period)/ p_total_period;
1496
1497 FOR i in p_cl_period..p_total_period
1498 LOOP
1499 l_pro_mile:=(l_mile*p_total_period)/i;
1500 lband := pqp_car_mileage_functions.pqp_get_range( p_assignment_id
1501 , p_bg_id
1502 , p_payroll_action_id
1503 , p_rates_table
1504 , 'ROW'
1505 , l_pro_mile
1506 , p_claim_date
1507 , l_hp_mileage
1508 , hi_hp_mileage);
1509
1510
1511 IF l_ltemp_mileage =0 AND l_htemp_mileage=0 THEN
1512
1513 l_ltemp_mileage:=l_hp_mileage;
1514 l_htemp_mileage:=hi_hp_mileage;
1515
1516 Else
1517 IF l_ltemp_mileage<>l_hp_mileage
1518 AND l_htemp_mileage<>hi_hp_mileage THEN
1519 RETURN('N');
1520 exit;
1521 END IF;
1522 END IF;
1523
1524 END LOOP;
1525 RETURN('Y');
1526 END;
1527
1528
1529
1530 ---------------------------------------------------------------------
1531 --Function PRORATE_CALC Returns prorate calculated Amt
1532 ------------------------------------------------------------------------
1533 FUNCTION pqp_prorate_calc( p_assignment_id IN NUMBER
1534 ,p_bg_id IN NUMBER
1535 ,p_payroll_action_id IN NUMBER
1536 ,p_prorated_mileage IN NUMBER
1537 ,p_cc IN NUMBER
1538 ,p_claim_date IN date
1539 ,p_total_period IN NUMBER
1540 ,p_cl_period IN NUMBER
1541 ,p_rates_table IN VARCHAR2
1542 ,p_lower_pro_mileage IN NUMBER
1543 ,p_end_date IN OUT NOCOPY VARCHAR2)
1544 RETURN NUMBER IS
1545
1546 h_mileage VARCHAR2(20);
1547 lh_mileage VARCHAR2(20);
1548 err_msg VARCHAR2(60);
1549 lband NUMBER:=0;
1550 l_mileage NUMBER:=0;
1551 hi_mileage NUMBER:=0;
1552 tot_rate NUMBER:=0;
1553 l_value NUMBER:=0;
1554 ll_value NUMBER:=0;
1555 l_rate NUMBER:=0;
1556 ch_col VARCHAR2(10);
1557 e_same_val EXCEPTION;
1558 l_hp_mileage NUMBER:=0;
1559 hi_hp_mileage NUMBER:=0;
1560 l_end_date Varchar2(10):='NONE';
1561 l_effective_date DATE;
1562
1563 -- nocopy changes
1564 l_end_date_nc VARCHAR2(20);
1565
1566 BEGIN
1567 --
1568 l_end_date_nc := p_end_date;
1569
1570 l_effective_date :=TRUNC(pqp_car_mileage_functions.
1571 pqp_get_date_paid(p_payroll_action_id));
1572 IF NVL(p_end_date,'NO') <> 'NONE' THEN
1573
1574 p_end_date:= Max_limit_calc ( p_assignment_id
1575 ,p_bg_id
1576 ,p_payroll_action_id
1577 ,p_prorated_mileage
1578 ,p_cc
1579 ,p_claim_date
1580 ,p_total_period
1581 ,p_cl_period
1582 ,p_rates_table
1583
1584 );
1585
1586 END IF;
1587
1588 lband := pqp_car_mileage_functions.pqp_get_range( p_assignment_id
1589 , p_bg_id
1590 ,p_payroll_action_id
1591 , p_rates_table
1592 , 'ROW'
1593 , p_lower_pro_mileage
1594 , p_claim_date
1595 , l_mileage
1596 , hi_mileage);
1597
1598 lband := pqp_car_mileage_functions.pqp_get_range( p_assignment_id
1599 , p_bg_id
1600 ,p_payroll_action_id
1601 , p_rates_table
1602 , 'ROW'
1603 , p_prorated_mileage
1604 , p_claim_date
1605 , l_hp_mileage
1606 , hi_hp_mileage);
1607
1608
1609
1610 IF l_mileage=hi_mileage AND l_mileage<>0 AND hi_mileage<>0 THEN
1611 RAISE e_same_val;
1612 END IF;
1613 --
1614 ch_col :=to_char(p_cc);
1615 /*Positive claim*/
1616 IF p_prorated_mileage >= p_lower_pro_mileage THEN
1617
1618 IF hi_mileage=l_hp_mileage or
1619 (l_mileage=l_hp_mileage and hi_mileage=hi_hp_mileage) THEN
1620 --
1621 h_mileage:=to_char(hi_hp_mileage);
1622 lh_mileage:=to_char(hi_mileage);
1623 l_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1624 ,p_payroll_action_id
1625 ,p_rates_table
1626 ,ch_col
1627 ,h_mileage
1628 ,p_claim_date
1629 ,err_msg);
1630 ll_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1631 ,p_payroll_action_id
1632 ,p_rates_table
1633 ,ch_col
1634 ,lh_mileage
1635 ,p_claim_date
1636 ,err_msg);
1637
1638 RETURN (((((p_prorated_mileage-l_hp_mileage)*l_value)*p_cl_period)/p_total_period) +
1639 (((l_hp_mileage-p_lower_pro_mileage)*p_cl_period)/p_total_period)*ll_value);
1640 ELSE
1641 h_mileage:=to_char(hi_hp_mileage);
1642 l_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1643 ,p_payroll_action_id
1644 ,p_rates_table
1645 ,ch_col
1646 ,h_mileage
1647 ,p_claim_date
1648 ,err_msg);
1649
1650 tot_rate:= (((p_prorated_mileage-l_hp_mileage)*l_value)*p_cl_period)/
1651 p_total_period;
1652
1653 l_rate := pqp_prorate_calc( p_assignment_id
1654 ,p_bg_id
1655 ,p_payroll_action_id
1656 ,l_hp_mileage
1657 ,p_cc
1658 ,p_claim_date
1659 ,p_total_period
1660 ,p_cl_period
1661 ,p_rates_table
1662 ,p_lower_pro_mileage
1663 , l_end_date);
1664 return(tot_rate +l_rate);
1665
1666 END IF;
1667
1668 END IF;
1669
1670 /*For negative Claim */
1671
1672 IF p_prorated_mileage < p_lower_pro_mileage THEN
1673
1674
1675
1676 IF hi_hp_mileage=l_mileage
1677 OR (l_mileage=l_hp_mileage and hi_mileage=hi_hp_mileage) THEN
1678
1679 --
1680 h_mileage:=to_char(hi_mileage);
1681 lh_mileage:=to_char(hi_hp_mileage);
1682 l_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1683 ,p_payroll_action_id
1684 ,p_rates_table
1685 ,ch_col
1686 ,h_mileage
1687 ,p_claim_date
1688 ,err_msg);
1689 ll_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1690 ,p_payroll_action_id
1691 ,p_rates_table
1692 ,ch_col
1693 ,lh_mileage
1694 ,p_claim_date
1695 ,err_msg);
1696
1697 RETURN (-1*(((((p_lower_pro_mileage-l_mileage)*l_value)*p_cl_period)/p_total_period) +
1698 (((l_mileage-p_prorated_mileage)*p_cl_period)/p_total_period)*ll_value));
1699 ELSE
1700
1701 h_mileage:=to_char(hi_mileage);
1702 l_value:=pqp_car_mileage_functions.pqp_get_table_value(p_bg_id
1703 ,p_payroll_action_id
1704 ,p_rates_table
1705 ,ch_col
1706 ,h_mileage
1707 ,p_claim_date
1708 ,err_msg);
1709
1710 tot_rate:= (((p_lower_pro_mileage-l_mileage)*l_value)*p_cl_period)/
1711 p_total_period;
1712
1713 l_rate := pqp_prorate_calc( p_assignment_id
1714 ,p_bg_id
1715 ,p_payroll_action_id
1716 ,p_prorated_mileage
1717 ,p_cc
1718 ,p_claim_date
1719 ,p_total_period
1720 ,p_cl_period
1721 ,p_rates_table
1722 ,l_mileage
1723 , l_end_date);
1724 return(-1*(tot_rate -l_rate));
1725
1726 END IF;
1727
1728 END IF;
1729
1730 EXCEPTION
1731 WHEN e_same_val THEN
1732 p_end_date := l_end_date_nc;
1733 RETURN(0);
1734
1735 WHEN OTHERS THEN
1736 -- Added by tmehra for nocopy changes
1737 p_end_date := l_end_date_nc;
1738 RETURN(0);
1739
1740 END;
1741
1742 -------------------------------------------------------------
1743
1744 FUNCTION pqp_get_taxni_rates ( p_assignment_id IN NUMBER
1745 ,p_business_group_id IN NUMBER
1746 ,p_payroll_action_id IN NUMBER
1747 ,p_itd_ac_miles IN NUMBER
1748 ,p_actual_mileage IN NUMBER
1749 ,p_total_actual_mileage IN NUMBER
1750 ,p_ele_iram_itd IN NUMBER
1751 ,p_cc IN NUMBER
1752 ,p_claim_end_date IN DATE
1753 ,p_two_wheeler_type IN VARCHAR2
1754 ,p_wheeler_type IN VARCHAR2
1755 ,p_table_name IN VARCHAR2
1756 ,p_ele_iram_amt OUT NOCOPY NUMBER
1757 ,p_error_mesg OUT NOCOPY VARCHAR2)
1758 RETURN NUMBER AS
1759 l_veh_cc varchar2(10);
1760 ch_irh_mileage_band VARCHAR2(15);
1761 return_result NUMBER;
1762 irl_mileage_band NUMBER;
1763 irh_mileage_band NUMBER;
1764 hi_flag VARCHAR2(1):='N';
1765 irlo_mileage_band NUMBER;
1766 irhi_mileage_band NUMBER;
1767 ch_irhi_mileage_band VARCHAR2(15);
1768 lo_cc number;
1769 hi_cc NUMBER;
1770 chi_cc VARCHAR2(15);
1771 iram_rate NUMBER;
1772 iram_rate_hi NUMBER;
1773 tot_iram_amt NUMBER:=0;
1774 err_msg VARCHAR2(15);
1775 l_effective_date DATE;
1776 l_ret_value NUMBER;
1777 l_ele_iram_itd NUMBER(11,2);
1778 l_error_mesg VARCHAR2(80) ;
1779 BEGIN
1780 l_effective_date := TRUNC(pqp_car_mileage_functions.
1781 pqp_get_date_paid(p_payroll_action_id));
1782
1783
1784 IF p_itd_ac_miles=0 OR
1785 ( p_itd_ac_miles<>0 AND p_itd_ac_miles-p_actual_mileage<>0) THEN
1786
1787 l_veh_cc:=to_char(p_cc);
1788 --This is a new change that was done to handle the issue for Tax balance which
1789 --did not have an itd amount and to create a new itd balance would affect the
1790 --existing customers so the entire amount is recalculated in case of correction
1791 --and that amount is deducted from the new value.
1792
1793 IF p_itd_ac_miles<>0 AND p_itd_ac_miles-p_actual_mileage<>0 THEN
1794 l_ret_value:=pqp_get_taxni_rates
1795 ( p_assignment_id =>p_assignment_id
1796 ,p_business_group_id =>p_business_group_id
1797 ,p_payroll_action_id =>p_payroll_action_id
1798 ,p_itd_ac_miles =>0
1799 ,p_actual_mileage =>p_itd_ac_miles
1800 ,p_total_actual_mileage =>p_total_actual_mileage-p_itd_ac_miles
1801 ,p_ele_iram_itd =>0
1802 ,p_cc =>p_cc
1803 ,p_claim_end_date =>p_claim_end_date
1804 ,p_two_wheeler_type =>p_two_wheeler_type
1805 ,p_wheeler_type =>p_wheeler_type
1806 ,p_table_name =>p_table_name
1807 ,p_ele_iram_amt =>l_ele_iram_itd
1808 ,p_error_mesg =>l_error_mesg
1809 );
1810 ELSE
1811 l_ele_iram_itd:=0;
1812
1813 END IF;
1814
1815 IF p_Two_wheeler_type <>'PP' AND p_Two_wheeler_type <>'PM' THEN
1816
1817 return_result:= pqp_car_mileage_functions.pqp_get_range (
1818 p_assignment_id
1819 ,p_business_group_id
1820 ,p_payroll_action_id
1821 ,p_table_name
1822 ,'ROW'
1823 ,p_total_actual_mileage- p_itd_ac_miles
1824 ,p_claim_end_date
1825 ,irl_mileage_band
1826 ,irh_mileage_band);
1827
1828
1829 IF irh_mileage_band <
1830 (p_actual_mileage+(p_total_actual_mileage-p_itd_ac_miles))
1831 THEN
1832
1833 hi_flag:='Y';
1834 return_result:= pqp_car_mileage_functions.pqp_get_range (
1835 p_assignment_id
1836 ,p_business_group_id
1837 ,p_payroll_action_id
1838 ,p_table_name
1839 ,'ROW'
1840 ,p_total_actual_mileage+p_actual_mileage
1841 ,p_claim_end_date
1842 ,irlo_mileage_band
1843 ,irhi_mileage_band);
1844
1845 ch_irhi_mileage_band:=to_char(irhi_mileage_band);
1846 END IF;
1847 ch_irh_mileage_band:=to_char(irh_mileage_band);
1848
1849 ELSE
1850
1851 ch_irh_mileage_band:=p_wheeler_type;
1852 END IF;
1853
1854 return_result:= pqp_car_mileage_functions.pqp_get_range (
1855 p_assignment_id
1856 ,p_business_group_id
1857 ,p_payroll_action_id
1858 ,p_table_name
1859 ,'COL'
1860 ,p_cc
1861 ,p_claim_end_date
1862 ,lo_cc
1863 ,hi_cc);
1864
1865 IF return_result=-1 AND
1866 (p_two_wheeler_type<>'PP' OR p_two_wheeler_type<>'PM' ) THEN
1867
1868 p_error_mesg:='IRAM Rate Not Found';
1869
1870 END IF;
1871
1872 chi_cc:=to_char(hi_cc);
1873
1874 iram_rate:=to_number( pqp_car_mileage_functions.pqp_get_table_value(
1875 p_business_group_id
1876 ,p_payroll_action_id
1877 ,p_table_name
1878 ,chi_cc
1879 , ch_irh_mileage_band
1880 , p_Claim_end_date,err_msg));
1881 IF hi_flag='Y' THEN
1882
1883 iram_rate_hi:=to_number( pqp_car_mileage_functions.pqp_get_table_value(
1884 p_business_group_id
1885 ,p_payroll_action_id
1886 ,p_table_name
1887 ,chi_cc
1888 , ch_irhi_mileage_band
1889 , p_Claim_end_date,err_msg));
1890 END IF;
1891
1892 IF err_msg='ERROR' THEN
1893
1894 p_error_mesg:='No rate found for IRAM rate calculation';
1895
1896 END IF;
1897
1898 IF hi_flag='Y' THEN
1899
1900 tot_iram_amt:=((irh_mileage_band
1901 - p_total_actual_mileage)
1902 *iram_rate
1903 +
1904 (p_total_actual_mileage+p_actual_mileage -
1905 irh_mileage_band)
1906 * iram_rate_hi)-l_ele_iram_itd;
1907
1908
1909 ELSE
1910
1911 tot_iram_amt:=(p_actual_mileage) *iram_rate-l_ele_iram_itd;
1912
1913 END IF;
1914 END IF;
1915 p_ele_iram_amt:=NVL(tot_iram_amt,0);
1916 RETURN(0);
1917
1918 -- Added by tmehra for nocopy changes Feb'03
1919
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 p_ele_iram_amt := NULL;
1923 p_error_mesg := SQLERRM;
1924 raise;
1925
1926 END;
1927
1928
1929
1930 ---------------------------
1931 --This function will be used for paye taxable and company vehicles claims
1932 --as we needed to add two more parameters for actual mileage and itd mileage
1933 --for private vehicles.
1934
1935 FUNCTION pqp_get_addlpasg_rate ( p_business_group_id IN NUMBER
1936 ,p_payroll_action_id IN NUMBER
1937 ,p_vehicle_type IN VARCHAR2
1938 ,p_claimed_mileage IN NUMBER
1939 ,p_itd_miles IN NUMBER
1940 ,p_total_passengers IN NUMBER
1941 ,p_total_pasg_itd_val IN NUMBER
1942 ,p_cc IN NUMBER
1943 ,p_rates_table IN VARCHAR2
1944 ,p_claim_end_date IN DATE
1945 ,p_tax_free_amt OUT NOCOPY NUMBER
1946 ,p_ni_amt OUT NOCOPY NUMBER
1947 ,p_tax_amt OUT NOCOPY NUMBER
1948 ,p_err_msg OUT NOCOPY VARCHAR2)
1949
1950 RETURN number
1951 AS
1952 chi_cc VARCHAR2(15);
1953 chi_cc1 VARCHAR2(15);
1954 chi_cc_iram VARCHAR2(15);
1955 addl_rate NUMBER;
1956 addl_rate1 NUMBER;
1957 addl_ni_rate NUMBER(9,2) :=0;
1958 addl_tax_rate NUMBER(9,2) :=0;
1959 add_pasg_rate NUMBER(9,2) :=0;
1960 cal_ni_rate NUMBER(9,2) :=0;
1961 cal_tax_rate NUMBER(9,2) :=0;
1962 l_low_value NUMBER :=0;
1963 l_high_value NUMBER :=0;
1964 l_ret_value NUMBER :=0;
1965 l_high_val_ur1 NUMBER :=0;
1966 l_high_val_ur NUMBER :=0;
1967 l_low_val_ur1 NUMBER :=0;
1968 l_low_val_ur NUMBER :=0;
1969 l_effective_date DATE;
1970 l_rates_table1 VARCHAR2(80);
1971 l_rates_table2 VARCHAR2(80);
1972 l_rates_table VARCHAR2(160);
1973 l_length NUMBER(9);
1974 l_err VARCHAR2(10) := NULL;
1975 l_prev_ni_amt NUMBER(9,2) :=0;
1976 l_prev_tax_amt NUMBER(9,2) :=0;
1977 l_error_msg VARCHAR2(80);
1978 l_ret_num NUMBER :=0;
1979 l_rates_setting VARCHAR2(1);
1980 l_correction VARCHAR2(1) :='N';
1981
1982 BEGIN
1983 l_length :=0;
1984 BEGIN
1985 SELECT instr (p_rates_table,'+PLUS+',1 )
1986 INTO l_length
1987 FROM dual;
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 l_err :='NONE' ;
1991 END ;
1992 IF l_length <> 0 THEN
1993 l_rates_table2 := SUBSTR (p_rates_table,l_length+6);
1994 l_rates_table1 := SUBSTR (p_rates_table,0,l_length-1);
1995 l_rates_table2 := LTRIM(RTRIM(l_rates_table2));
1996 l_rates_table1 := LTRIM(RTRIM(l_rates_table1));
1997 ELSE
1998 l_rates_table1 := p_rates_table;
1999 l_rates_table2 := 'NONE';
2000 END IF;
2001 l_rates_setting :='N' ;
2002 IF p_total_pasg_itd_val <> 0
2003 AND ( p_total_pasg_itd_val - p_total_passengers) <> 0 AND l_rates_table2 <> 'NONE'
2004 AND l_err IS NULL THEN
2005 l_rates_setting := get_config_info (p_business_group_id,'Rates');
2006 IF l_rates_setting ='Y' THEN
2007 l_ret_value:=pqp_car_mileage_functions.pqp_get_range(
2008 p_assignment_id => 0
2009 ,p_business_group_id => p_business_group_id
2010 ,p_payroll_action_id => p_payroll_action_id
2011 ,p_table_name => l_rates_table2
2012 ,p_row_or_column => 'COL'
2013 ,p_value => p_cc
2014 ,p_claim_date => p_claim_end_date
2015 ,p_low_value => l_low_val_ur1
2016 ,p_high_value => l_high_val_ur1 );
2017 chi_cc1:=to_char(l_high_val_ur1);
2018 addl_rate1:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2019 (p_bus_group_id => p_business_group_id
2020 ,p_payroll_action_id => p_payroll_action_id
2021 ,p_table_name => l_rates_table2
2022 ,p_col_name => chi_cc1
2023 ,p_row_value => 'ADDITIONAL_PASSENGER'
2024 ,p_effective_date => p_Claim_end_date
2025 ,p_error_msg => p_err_msg));
2026 l_correction:='Y';
2027 END IF;
2028 END IF;
2029
2030 l_effective_date :=TRUNC(pqp_car_mileage_functions.
2031 pqp_get_date_paid(p_payroll_action_id));
2032 IF p_total_passengers=0 AND p_total_pasg_itd_val=0 THEN
2033
2034 p_tax_free_amt:=0;
2035 p_ni_amt:=0;
2036 p_tax_amt:=0;
2037 p_err_msg:='SUCCESS';
2038
2039 ELSE
2040 l_ret_value:=pqp_car_mileage_functions.pqp_get_range(
2041 p_assignment_id => 0
2042 ,p_business_group_id =>p_business_group_id
2043 ,p_payroll_action_id =>p_payroll_action_id
2044 ,p_table_name =>l_rates_table1
2045 ,p_row_or_column =>'COL'
2046 ,p_value =>p_cc
2047 ,p_claim_date =>p_claim_end_date
2048 ,p_low_value =>l_low_val_ur
2049 ,p_high_value =>l_high_val_ur );
2050 IF (p_Claimed_mileage-p_itd_miles)<>0
2051 OR (p_total_passengers- p_total_pasg_itd_val) <> 0 THEN
2052 chi_cc:=to_char(l_high_val_ur);
2053 chi_cc_iram:=to_char(l_high_value);
2054 addl_rate:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2055 (p_bus_group_id => p_business_group_id
2056 ,p_payroll_action_id =>p_payroll_action_id
2057 ,p_table_name => l_rates_table1
2058 ,p_col_name => chi_cc
2059 ,p_row_value => 'ADDITIONAL_PASSENGER'
2060 ,p_effective_date => p_Claim_end_date
2061 ,p_error_msg => p_err_msg));
2062 IF l_correction='N' THEN
2063 addl_rate1:= addl_rate;
2064 END IF;
2065 IF p_err_msg='SUCCESS' THEN
2066 IF p_Claimed_mileage-p_itd_miles <> 0
2067 AND p_total_passengers-p_total_pasg_itd_val<>0 THEN
2068
2069 -- Add_pasg_rate:=ABS((p_total_passengers)*(p_Claimed_mileage)*addl_rate)-(p_total_pasg_itd_val)*(p_itd_miles)*addl_rate1;
2070 Add_pasg_rate:=(p_total_passengers)*(p_Claimed_mileage)*addl_rate
2071 -(p_total_pasg_itd_val)*(p_itd_miles)*addl_rate1;
2072
2073
2074 ELSIF p_Claimed_mileage-p_itd_miles <> 0
2075 AND p_total_passengers-p_total_pasg_itd_val=0 THEN
2076
2077 Add_pasg_rate:=(p_total_passengers)
2078 *(p_Claimed_mileage-p_itd_miles)*addl_rate;
2079 ELSIF p_Claimed_mileage-p_itd_miles = 0
2080 AND p_total_passengers-p_total_pasg_itd_val <> 0 THEN
2081
2082 Add_pasg_rate:=(p_total_passengers-p_total_pasg_itd_val)
2083 *(p_Claimed_mileage)*addl_rate1;
2084 END IF;
2085 p_tax_free_amt:=add_pasg_rate;
2086
2087 ELSE
2088 p_tax_free_amt:=0;
2089 p_ni_amt:=0;
2090 p_tax_amt:=0;
2091 /*p_err_msg:=p_err_msg||'ERROR'||'pclm'||p_Claimed_mileage||'p_itd_miles'||p_itd_miles||'p_total_passengers'||p_total_passengers||'p_total_pasg_itd_val'||p_total_pasg_itd_val||'p_rates_table'||p_rates_table;*/
2092 p_err_msg := 'p_rates_table'||p_rates_table;
2093 END IF;
2094 ELSE
2095 p_tax_free_amt:=0;
2096 p_ni_amt:=0;
2097 p_tax_amt:=0;
2098 p_err_msg:='SUCCESS';
2099 END IF;
2100 END IF;
2101 return (0);
2102
2103 -- Added by tmehra for nocopy changes Feb'03
2104
2105 EXCEPTION
2106 WHEN OTHERS THEN
2107
2108 p_tax_free_amt := NULL;
2109 p_ni_amt := NULL;
2110 p_tax_amt := NULL;
2111 p_err_msg := SQLERRM||'myerror';
2112 raise;
2113
2114
2115 END;
2116
2117
2118 ------------------------------------------------------------------
2119 --Function pqp_get_date_paid
2120 -----------------------------------------------------------------
2121
2122 FUNCTION pqp_get_date_paid ( p_payroll_action_id IN NUMBER)
2123 RETURN DATE
2124 AS
2125
2126 CURSOR c_date_paid IS
2127 SELECT effective_date
2128 FROM pay_payroll_actions
2129 WHERE payroll_action_id= p_payroll_action_id;
2130
2131 l_date_paid c_date_paid%ROWTYPE;
2132 BEGIN
2133 OPEN c_date_paid;
2134 LOOP
2135 FETCH c_date_paid INTO l_date_paid;
2136 EXIT WHEN c_date_paid%NOTFOUND;
2137
2138 END LOOP;
2139 CLOSE c_date_paid;
2140
2141 RETURN (l_date_paid.effective_date);
2142
2143 EXCEPTION
2144 ---------
2145 WHEN OTHERS THEN
2146 RETURN(NULL);
2147
2148
2149 END;
2150
2151
2152 FUNCTION pqp_get_passenger_rate ( p_business_group_id IN NUMBER
2153 ,p_payroll_action_id IN NUMBER
2154 ,p_vehicle_type IN VARCHAR2
2155 ,p_claimed_mileage IN NUMBER
2156 ,p_cl_itd_miles IN NUMBER
2157 ,p_actual_mileage IN NUMBER
2158 ,p_ac_itd_miles IN NUMBER
2159 ,p_total_passengers IN NUMBER
2160 ,p_total_pasg_itd_val IN NUMBER
2161 ,p_cc IN NUMBER
2162 ,p_rates_table IN VARCHAR2
2163 ,p_claim_end_date IN DATE
2164 ,p_tax_free_amt OUT NOCOPY NUMBER
2165 ,p_ni_amt OUT NOCOPY NUMBER
2166 ,p_tax_amt OUT NOCOPY NUMBER
2167 ,p_err_msg OUT NOCOPY VARCHAR2)
2168
2169 RETURN number
2170 AS
2171 chi_cc VARCHAR2(15);
2172 chi_cc1 VARCHAR2(15);
2173 chi_cc_iram VARCHAR2(15);
2174 addl_rate NUMBER :=0;
2175 addl_rate1 NUMBER :=0;
2176 addl_ni_rate NUMBER :=0;
2177 addl_ni_rate1 NUMBER :=0;
2178 addl_tax_rate1 NUMBER :=0;
2179 addl_tax_rate NUMBER :=0;
2180 Add_pasg_rate NUMBER :=0;
2181 cal_ni_rate NUMBER :=0;
2182 cal_tax_rate NUMBER :=0;
2183 l_low_value NUMBER :=0;
2184 l_high_value NUMBER :=0;
2185 l_ret_value NUMBER :=0;
2186 l_high_val_ur NUMBER :=0;
2187 l_low_val_ur NUMBER :=0;
2188 l_high_val_ur1 NUMBER :=0;
2189 l_low_val_ur1 NUMBER :=0;
2190 l_effective_date DATE;
2191 l_rates_table1 VARCHAR2(80);
2192 l_rates_table2 VARCHAR2(80);
2193 l_rates_table VARCHAR2(160);
2194 l_length NUMBER(9);
2195 l_err VARCHAR2(10) := NULL;
2196 l_prev_tax_free_amt NUMBER(9,2) :=0;
2197 l_prev_ni_amt NUMBER(9,2) :=0;
2198 l_prev_tax_amt NUMBER(9,2) :=0;
2199 l_error_msg VARCHAR2(80);
2200 l_ret_num NUMBER :=0;
2201 l_rates_setting VARCHAR2(1);
2202 l_correction VARCHAR2(1) :='N';
2203
2204 BEGIN
2205 l_length :=0;
2206 BEGIN
2207 /*This section is introduced to handle rates from sliding scale
2208 for additional passengers. This is mainly used during correction
2209 of claims where additional passengers have been changed. The rate that
2210 must be calculated should be based on the old rate table that is used
2211 to calculte the old value and new rates table will be used to calcualte the
2212 new changed value for addl passengers. */
2213 SELECT INSTR (p_rates_table,'+PLUS+',1 )
2214 INTO l_length
2215 FROM dual;
2216
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 l_err :='NONE' ;
2220 END ;
2221 IF l_length <> 0 THEN
2222 l_rates_table2 := SUBSTR (p_rates_table,l_length+6);
2223 l_rates_table1 := SUBSTR (p_rates_table,0,l_length-1);
2224 l_rates_table2 := LTRIM(RTRIM(l_rates_table2));
2225 l_rates_table1 := LTRIM(RTRIM(l_rates_table1));
2226 ELSE
2227 l_rates_table1 := p_rates_table;
2228 l_rates_table2 := 'NONE';
2229 END IF;
2230 l_rates_setting :='N' ;
2231 IF p_total_pasg_itd_val <> 0
2232 AND ( p_total_pasg_itd_val - p_total_passengers) <> 0
2233 AND l_rates_table2 <> 'NONE'
2234 AND l_err IS NULL THEN
2235 l_rates_setting := get_config_info (p_business_group_id,'Rates');
2236 IF l_rates_setting ='Y' THEN
2237 l_ret_value:=pqp_car_mileage_functions.pqp_get_range
2238 (p_assignment_id =>0
2239 ,p_business_group_id =>p_business_group_id
2240 ,p_payroll_action_id =>p_payroll_action_id
2241 ,p_table_name =>'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
2242 ,p_row_or_column =>'COL'
2243 ,p_value =>p_cc
2244 ,p_claim_date =>p_claim_end_date
2245 ,p_low_value =>l_low_value
2246 ,p_high_value =>l_high_value );
2247
2248 l_ret_value:=pqp_car_mileage_functions.pqp_get_range(
2249 p_assignment_id => 0
2250 ,p_business_group_id =>p_business_group_id
2251 ,p_payroll_action_id =>p_payroll_action_id
2252 ,p_table_name =>l_rates_table2
2253 ,p_row_or_column =>'COL'
2254 ,p_value =>p_cc
2255 ,p_claim_date =>p_claim_end_date
2256 ,p_low_value =>l_low_val_ur1
2257 ,p_high_value =>l_high_val_ur1 );
2258 chi_cc1:=to_char(l_high_val_ur1);
2259 chi_cc_iram:=to_char(l_high_value);
2260 addl_rate1:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2261 (p_bus_group_id => p_business_group_id
2262 ,p_payroll_action_id =>p_payroll_action_id
2263 ,p_table_name => l_rates_table2
2264 ,p_col_name => chi_cc1
2265 ,p_row_value => 'ADDITIONAL_PASSENGER'
2266 ,p_effective_date => p_Claim_end_date
2267 ,p_error_msg => p_err_msg));
2268 IF p_err_msg='SUCCESS'
2269 AND ( p_vehicle_type='E' OR p_vehicle_type='C')THEN
2270 addl_ni_rate1:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2271 (p_bus_group_id => p_business_group_id
2272 ,p_payroll_action_id =>p_payroll_action_id
2273 ,p_table_name => 'PQP_NIC_MILEAGE_RATES'
2274 ,p_col_name => chi_cc_iram
2275 ,p_row_value => 'ADDL PASSENGER'
2276 ,p_effective_date => p_Claim_end_date
2277 ,p_error_msg =>p_err_msg));
2278 END IF;
2279 IF p_err_msg='SUCCESS'
2280 AND ( p_vehicle_type='E' OR p_vehicle_type='C') THEN
2281 addl_tax_rate1:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2282 (p_bus_group_id => p_business_group_id
2283 ,p_payroll_action_id =>p_payroll_action_id
2284 ,p_table_name => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
2285 ,p_col_name => chi_cc_iram
2286 ,p_row_value => 'ADDL PASSENGER'
2287 ,p_effective_date => p_Claim_end_date
2288 ,p_error_msg => p_err_msg));
2289 END IF;
2290 l_correction:='Y';
2291 END IF;
2292 END IF;
2293 l_effective_date :=TRUNC(pqp_car_mileage_functions.
2294 pqp_get_date_paid(p_payroll_action_id));
2295 IF p_total_passengers=0 AND p_total_pasg_itd_val=0 THEN
2296 p_tax_free_amt:=0;
2297 p_ni_amt:=0;
2298 p_tax_amt:=0;
2299 p_err_msg:='SUCCESS';
2300
2301 ELSE
2302 l_ret_value:=pqp_car_mileage_functions.pqp_get_range
2303 (p_assignment_id =>0
2304 ,p_business_group_id =>p_business_group_id
2305 ,p_payroll_action_id =>p_payroll_action_id
2306 ,p_table_name =>'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
2307 ,p_row_or_column =>'COL'
2308 ,p_value =>p_cc
2309 ,p_claim_date =>p_claim_end_date
2310 ,p_low_value =>l_low_value
2311 ,p_high_value =>l_high_value );
2312
2313 l_ret_value:=pqp_car_mileage_functions.pqp_get_range(
2314 p_assignment_id => 0
2315 ,p_business_group_id =>p_business_group_id
2316 ,p_payroll_action_id =>p_payroll_action_id
2317 ,p_table_name =>l_rates_table1
2318 ,p_row_or_column =>'COL'
2319 ,p_value =>p_cc
2320 ,p_claim_date =>p_claim_end_date
2321 ,p_low_value =>l_low_val_ur
2322 ,p_high_value =>l_high_val_ur );
2323 IF (p_Claimed_mileage-p_cl_itd_miles)<>0
2324 OR (p_total_passengers- p_total_pasg_itd_val) <> 0
2325 OR (p_actual_mileage-p_ac_itd_miles)<>0 THEN
2326 chi_cc:=to_char(l_high_val_ur);
2327 chi_cc_iram:=to_char(l_high_value);
2328 addl_rate:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2329 (p_bus_group_id => p_business_group_id
2330 ,p_payroll_action_id =>p_payroll_action_id
2331 ,p_table_name => l_rates_table1
2332 ,p_col_name => chi_cc
2333 ,p_row_value => 'ADDITIONAL_PASSENGER'
2334 ,p_effective_date => p_Claim_end_date
2335 ,p_error_msg => p_err_msg));
2336 IF l_correction='N' THEN
2337 addl_rate1:= addl_rate;
2338 END IF;
2339
2340
2341
2342 IF p_err_msg='SUCCESS'
2343 AND ( p_vehicle_type='E' OR p_vehicle_type='C')THEN
2344 addl_ni_rate:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2345 (p_bus_group_id => p_business_group_id
2346 ,p_payroll_action_id =>p_payroll_action_id
2347 ,p_table_name => 'PQP_NIC_MILEAGE_RATES'
2348 ,p_col_name => chi_cc_iram
2349 ,p_row_value => 'ADDL PASSENGER'
2350 ,p_effective_date => p_Claim_end_date
2351 ,p_error_msg =>p_err_msg));
2352 END IF;
2353 IF p_err_msg='SUCCESS'
2354 AND ( p_vehicle_type='E' OR p_vehicle_type='C') THEN
2355 addl_tax_rate:=to_number(pqp_car_mileage_functions.pqp_get_table_value
2356 (p_bus_group_id => p_business_group_id
2357 ,p_payroll_action_id =>p_payroll_action_id
2358 ,p_table_name => 'PQP_INLAND_REV_AUTH_MILEAGE_RATES'
2359 ,p_col_name => chi_cc_iram
2360 ,p_row_value => 'ADDL PASSENGER'
2361 ,p_effective_date => p_Claim_end_date
2362 ,p_error_msg => p_err_msg));
2363 END IF;
2364 IF p_err_msg='SUCCESS' THEN
2365 IF (p_claimed_mileage-p_ac_itd_miles <> 0
2366 AND p_total_passengers-p_total_pasg_itd_val<>0 )
2367 OR (p_actual_mileage-p_ac_itd_miles <> 0
2368 AND p_total_passengers-p_total_pasg_itd_val<>0) THEN
2369 Add_pasg_rate:=ABS((p_total_passengers)*(p_claimed_mileage)*addl_rate)
2370 -(p_total_pasg_itd_val)*(p_cl_itd_miles)*addl_rate1;
2371 IF p_vehicle_type='E' OR p_vehicle_type='C' THEN
2372 cal_ni_rate:=ABS((p_total_passengers)*(p_actual_mileage)*addl_ni_rate)
2373 -(p_total_pasg_itd_val)*(p_ac_itd_miles)*addl_ni_rate1;
2374 cal_tax_rate:=ABS((p_total_passengers)*(p_actual_mileage)*addl_tax_rate)
2375 -(p_total_pasg_itd_val)*(p_ac_itd_miles)*addl_tax_rate1;
2376 END IF;
2377 ELSIF (p_claimed_mileage-p_cl_itd_miles <> 0
2378 AND p_total_passengers-p_total_pasg_itd_val=0 )
2379 OR (p_actual_mileage-p_ac_itd_miles <> 0
2380 AND p_total_passengers-p_total_pasg_itd_val=0 ) THEN
2381
2382 Add_pasg_rate:=(p_total_passengers)
2383 *(p_claimed_mileage-p_cl_itd_miles)*addl_rate;
2384 IF p_vehicle_type='E' OR p_vehicle_type='C' THEN
2385 cal_ni_rate:=((p_total_passengers)
2386 *(p_actual_mileage-p_ac_itd_miles)*addl_ni_rate);
2387 cal_tax_rate:=((p_total_passengers)
2388 *(p_actual_mileage-p_ac_itd_miles)*addl_tax_rate);
2389
2390 END IF;
2391 ELSIF (p_claimed_mileage-p_cl_itd_miles = 0
2392 AND p_total_passengers-p_total_pasg_itd_val <>0 )
2393 OR (p_actual_mileage-p_ac_itd_miles = 0
2394 AND p_total_passengers-p_total_pasg_itd_val <>0 ) THEN
2395 Add_pasg_rate:=(p_total_passengers-p_total_pasg_itd_val)
2396 *(p_Claimed_mileage)*addl_rate1;
2397 IF p_vehicle_type='E' OR p_vehicle_type='C' THEN
2398 cal_ni_rate:=((p_total_passengers-p_total_pasg_itd_val)
2399 *(p_actual_mileage)*addl_ni_rate1);
2400 cal_tax_rate:=((p_total_passengers-p_total_pasg_itd_val)
2401 *(p_actual_mileage)*addl_tax_rate1);
2402
2403 END IF;
2404 END IF;
2405 p_tax_free_amt:=add_pasg_rate ;
2406
2407 IF p_vehicle_type='E' OR p_vehicle_type='C' THEN
2408 IF Add_pasg_rate>=0 THEN
2409 p_ni_amt:= GREATEST ((Add_pasg_rate- cal_ni_rate),0);
2410 p_tax_amt:=GREATEST ((Add_pasg_rate- cal_tax_rate),0);
2411 ELSE
2412 p_ni_amt:= LEAST ((Add_pasg_rate- cal_ni_rate),0);
2413 p_tax_amt:=LEAST ((Add_pasg_rate- cal_tax_rate),0);
2414 END IF;
2415 END IF;
2416 ELSE
2417 p_tax_free_amt:=0;
2418 p_ni_amt:=0;
2419 p_tax_amt:=0;
2420 p_err_msg:='ERROR';
2421 END IF;
2422 ELSE
2423 p_tax_free_amt:=0;
2424 p_ni_amt:=0;
2425 p_tax_amt:=0;
2426 p_err_msg:='SUCCESS';
2427 END IF;
2428 END IF;
2429 RETURN (0);
2430
2431 -- Added by tmehra for nocopy changes Feb'03
2432
2433 EXCEPTION
2434 WHEN OTHERS THEN
2435
2436 p_tax_free_amt := NULL;
2437 p_ni_amt := NULL;
2438 p_tax_amt := NULL;
2439 p_err_msg := SQLERRM;
2440 raise;
2441
2442
2443 END;
2444
2445 --------------------------------------------------------------
2446
2447 --FUNCTION pqp_get_ele_endate to get the end date of element
2448 --entry id so that the stop entry is over ridden and this would
2449 --enable element to be picked up even when it is end dated in the
2450 --mid pay period.
2451 ---------------------------------------------------------------
2452
2453
2454 FUNCTION pqp_get_ele_endate ( p_assignment_id IN NUMBER
2455 ,p_business_group_id IN NUMBER
2456 ,p_payroll_action_id IN NUMBER
2457 ,p_element_entry_id IN NUMBER
2458 )
2459 RETURN VARCHAR2
2460 AS
2461
2462 CURSOR c_get_payroll_date
2463 IS
2464 SELECT ppa.effective_date
2465 FROM pay_payroll_actions ppa
2466 WHERE ppa.payroll_action_id = p_payroll_action_id
2467 AND ppa.business_group_id = p_business_group_id;
2468
2469 CURSOR c_get_end_date (cp_date DATE)
2470 IS
2471 SELECT pee.effective_end_date
2472 FROM pay_element_entries_f pee
2473 WHERE pee.element_entry_id =p_element_entry_id
2474 AND pee.assignment_id =p_assignment_id
2475 AND pee.effective_end_date <=cp_date;
2476 l_get_payroll_date c_get_payroll_date%ROWTYPE;
2477 l_get_end_date c_get_end_date%ROWTYPE;
2478 l_end_dated VARCHAR2(1) :='N';
2479
2480 BEGIN
2481 OPEN c_get_payroll_date;
2482 FETCH c_get_payroll_date INTO l_get_payroll_date;
2483 CLOSE c_get_payroll_date;
2484
2485 OPEN c_get_end_date (l_get_payroll_date.effective_date);
2486 FETCH c_get_end_date INTO l_get_end_date;
2487 CLOSE c_get_end_date;
2488
2489 IF l_get_end_date.effective_end_date <>
2490 to_date('12/31/4712','MM/DD/YYYY') THEN
2491 l_end_dated :='Y';
2492 END IF;
2493 RETURN (l_end_dated);
2494 END;
2495
2496
2497 ---------------------------------------------------------------
2498 --FUNCTION pqp_is_emp_term checks if employee is terminated.
2499 ---------------------------------------------------------------
2500
2501 FUNCTION pqp_is_emp_term ( p_assignment_id IN NUMBER
2502 ,p_business_group_id IN NUMBER
2503 ,p_payroll_action_id IN NUMBER
2504 ,p_date_earned IN DATE
2505 )
2506 RETURN VARCHAR2
2507 AS
2508
2509 CURSOR c_get_term_date
2510 IS
2511 SELECT DECODE(NVL(TO_CHAR(pds.actual_termination_date), 'N'), 'N', 'N', 'Y') term_date
2512 FROM per_periods_of_service pds
2513 ,per_assignments_f pas
2514 WHERE pds.actual_termination_date <= p_date_earned
2515 AND pds.period_of_service_id = pas.period_of_service_id
2516 AND p_date_earned BETWEEN pas.effective_start_date
2517 AND pas.effective_end_date
2518 AND pas.primary_flag = 'Y'
2519 AND pas.assignment_id =p_assignment_id
2520 AND pds.business_group_id =p_business_group_id
2521 AND pds.business_group_id=pas.business_group_id;
2522
2523 l_term_date VARCHAR2(1) :='N';
2524 BEGIN
2525
2526 OPEN c_get_term_date;
2527 FETCH c_get_term_date INTO l_term_date;
2528 CLOSE c_get_term_date;
2529
2530 RETURN (NVL(l_term_date,'N'));
2531 END;
2532
2533
2534 ----------------------------------------------------------------------------
2535 --Function get_rates_table
2536 ----------------------------------------------------------------------------
2537
2538 FUNCTION get_rates_table (p_business_group_id IN NUMBER
2539 ,p_lookup_type IN VARCHAR2
2540 ,p_additional_passenger IN NUMBER
2541 )
2542 RETURN VARCHAR2
2543 IS
2544
2545 CURSOR c_get_rates_table
2546 IS
2547 SELECT flv.meaning
2548 FROM fnd_lookup_values flv
2549 WHERE flv.lookup_type=p_lookup_type
2550 AND flv.lookup_code <= p_additional_passenger
2551 ORDER BY flv.lookup_code DESC;
2552
2553 l_get_rates_table c_get_rates_table%ROWTYPE;
2554
2555 BEGIN
2556 OPEN c_get_rates_table;
2557 FETCH c_get_rates_table INTO l_get_rates_table;
2558 CLOSE c_get_rates_table;
2559
2560
2561 RETURN (NVL(l_get_rates_table.meaning,p_lookup_type));
2562
2563 EXCEPTION
2564 ---------
2565 WHEN OTHERS THEN
2566 RETURN p_lookup_type;
2567
2568 END get_rates_table;
2569
2570
2571 ---------------------------------------------------------------------
2572 ----FUNCTION get_vehicle_type
2573 --------------------------------------------------------------------
2574 FUNCTION get_vehicle_type (p_business_group_id IN NUMBER
2575 ,p_element_type_id IN NUMBER
2576 ,p_payroll_action_id IN NUMBER
2577 )
2578 RETURN VARCHAR2
2579 IS
2580
2581 CURSOR c_vehicle_type (cp_business_group_id NUMBER
2582 ,cp_element_type_id NUMBER
2583 ,cp_effective_date DATE )
2584 IS
2585 SELECT pete.eei_information1 vehicle_type
2586 FROM pay_element_types_f petf
2587 ,pay_element_type_extra_info pete
2588 WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
2589 AND pete.element_type_id=petf.element_type_id
2590 AND petf.element_type_id= cp_element_type_id
2591 AND petf.business_group_id= cp_business_group_id
2592 AND cp_effective_date BETWEEN petf.effective_start_date
2593 AND petf.effective_end_date
2594 AND pete.eei_information1 in ('P','PP',
2595 'PM','C','CM'
2596 ,'CP');
2597 l_vehicle_type c_vehicle_type%ROWTYPE;
2598 l_effective_date DATE;
2599
2600
2601 BEGIN
2602 l_effective_date :=TRUNC(pqp_car_mileage_functions.
2603 pqp_get_date_paid(p_payroll_action_id));
2604
2605
2606 OPEN c_vehicle_type (p_business_group_id
2607 ,p_element_type_id
2608 ,l_effective_date
2609 );
2610 FETCH c_vehicle_type INTO l_vehicle_type;
2611 CLOSE c_vehicle_type;
2612
2613 IF l_vehicle_type.vehicle_type = 'P' OR
2614 l_vehicle_type.vehicle_type = 'C' THEN
2615 RETURN ('NE');
2616 ELSE
2617 RETURN (l_vehicle_type.vehicle_type);
2618 END IF;
2619 EXCEPTION
2620 --------
2621 WHEN OTHERS THEN
2622 RETURN ('NE');
2623 END get_vehicle_type;
2624
2625 FUNCTION is_miles_nonreimbursed
2626 ( p_assignment_id IN NUMBER
2627 ,p_business_group_id IN NUMBER
2628 ,p_payroll_action_id IN NUMBER
2629 ,p_element_type_id IN NUMBER
2630 ,p_date_earned IN DATE
2631 ,p_to_date IN DATE
2632 )
2633 RETURN VARCHAR2
2634
2635 IS
2636
2637 CURSOR c_is_entry_found (cp_assignment_id NUMBER
2638 ,cp_business_group_id NUMBER
2639 ,cp_element_type_id NUMBER
2640 ,cp_date_earned DATE
2641 ,cp_to_date DATE
2642 ,cp_eff_dt DATE
2643 )
2644 IS
2645 SELECT 'Y'
2646 FROM pay_element_type_extra_info petef
2647 ,pay_element_types_f pet
2648 ,pay_element_entries_f pee
2649 ,pay_element_entry_values_f pev
2650 WHERE pet.element_type_id =petef.element_type_id
2651 AND petef.information_type='PQP_VEHICLE_MILEAGE_INFO'
2652 AND petef.eei_information1 = 'P'
2653 AND pet.element_type_id =pee.element_type_id
2654 AND pee.element_entry_id=pev.element_entry_id
2655 AND pev.input_value_id =(SELECT input_value_id
2656 FROM pay_input_values_f
2657 WHERE element_type_id=petef.element_type_id
2658 AND name='Claim Start Date') and pee.assignment_id=cp_assignment_id
2659 --AND FND_DATE.canonical_to_date(screen_entry_value) < cp_to_date
2660 AND (screen_entry_value) > FND_DATE.date_to_canonical (cp_to_date)
2661 AND EXISTS (select 'Y'
2662 from pay_run_results RESULT,
2663 pay_assignment_actions ASGT_ACTION,
2664 pay_payroll_actions PAY_ACTION,
2665 per_time_periods PERIOD
2666 where result.source_id = pev.element_entry_id --nvl (p_original_entry_id, p_element_entry_id)
2667 and result.status <> 'U'
2668 and result.source_type = 'E'
2669 and result.assignment_action_id = asgt_action.assignment_action_id
2670 and asgt_action.payroll_action_id = pay_action.payroll_action_id
2671 and pay_action.payroll_id = period.payroll_id
2672 and pay_action.date_earned between period.start_date and period.end_date
2673 and pay_action.effective_date between period.start_date and period.end_date
2674 and pay_action.effective_date < cp_eff_dt)
2675 AND rownum=1;
2676
2677
2678 CURSOR c_get_payroll_act_dt (cp_payroll_action_id NUMBER)
2679 IS
2680 SELECT ppa.effective_date
2681 FROM pay_payroll_actions ppa
2682 WHERE payroll_action_id=cp_payroll_action_id;
2683
2684
2685 l_eff_dt DATE;
2686
2687 l_is_entry_found VARCHAR2(1);
2688
2689 BEGIN
2690
2691 l_is_entry_found :='N';
2692
2693 OPEN c_get_payroll_act_dt (p_payroll_action_id);
2694 FETCH c_get_payroll_act_dt INTO l_eff_dt;
2695 CLOSE c_get_payroll_act_dt;
2696
2697 OPEN c_is_entry_found (cp_assignment_id => p_assignment_id
2698 ,cp_business_group_id => p_business_group_id
2699 ,cp_element_type_id => p_element_type_id
2700 ,cp_date_earned => p_date_earned
2701 ,cp_to_date => p_to_date
2702 ,cp_eff_dt =>l_eff_dt
2703 );
2704 FETCH c_is_entry_found INTO l_is_entry_found;
2705 CLOSE c_is_entry_found;
2706
2707 RETURN(NVL(l_is_entry_found,'N'));
2708
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711 RETURN('N');
2712 END;
2713
2714
2715 END pqp_car_mileage_functions;