1 PACKAGE BODY pqp_gb_mileage_claim_pkg AS
2 /* $Header: pqgbmgcm.pkb 120.3 2011/05/20 06:38:38 nchinnam ship $ */
3 g_package varchar2(33):='pqp_gb_insert_mileage_claim.';
4
5 FUNCTION get_dflt_input_value (p_input_value_id NUMBER
6 ,p_element_type_id NUMBER
7 ,p_business_group_id NUMBER
8 ,p_effective_date DATE
9 )
10 RETURN VARCHAR2 IS
11
12 CURSOR c_get_dflt_val
13 IS
14 SELECT piv.default_value default_value
15 FROM pay_input_values_f piv
16 WHERE piv.input_value_id = p_input_value_id
17 AND piv.element_type_id = p_element_type_id
18 AND piv.business_group_id = p_business_group_id
19 AND p_effective_date BETWEEN piv.effective_start_date
20 AND piv.effective_end_date;
21
22
23 l_get_dflt_val c_get_dflt_val%ROWTYPE;
24 BEGIN
25
26 OPEN c_get_dflt_val;
27 FETCH c_get_dflt_val INTO l_get_dflt_val;
28 CLOSE c_get_dflt_val;
29
30 RETURN NVL(l_get_dflt_val.default_value, 'NONE');
31
32
33 END;
34 --This procedure gets all required info on element,rates
35 --when the condition is SS or config is yes and PUI.
36 --the info is fetched from user defined table
37 PROCEDURE get_purpose_details (p_business_group_id IN NUMBER
38 ,p_assignment_id IN NUMBER
39 ,p_effective_date IN DATE
40 ,p_purpose IN VARCHAR2
41 ,p_ownership IN VARCHAR2
42 ,p_vehicle_type IN VARCHAR2
43 ,p_rate_type IN VARCHAR2
44 ,p_element_id OUT NOCOPY NUMBER
45 ,p_element_name OUT NOCOPY VARCHAR2
46 ,p_rate_table_id OUT NOCOPY VARCHAR2
47 ,p_rate_table OUT NOCOPY VARCHAR2
48 ,p_taxable OUT NOCOPY VARCHAR2
49 )
50 IS
51
52
53 CURSOR c_get_column_info (cp_business_group_id NUMBER
54 ,cp_legislation_code VARCHAR2
55 ,cp_table_id NUMBER
56 ,cp_column_name VARCHAR2
57 )
58 IS
59 SELECT puc.user_column_id column_id
60 FROM pay_user_columns puc
61 WHERE puc.user_table_id =cp_table_id
62 AND puc.user_column_name =cp_column_name
63 AND puc.legislation_code = cp_legislation_code;
64
65 CURSOR c_get_row_info (cp_business_group_id NUMBER
66 ,cp_legislation_code VARCHAR2
67 ,cp_row_name VARCHAR2
68 ,cp_effective_date DATE
69 )
70 IS
71 SELECT pur.user_row_id,pur.row_low_range_or_name,put.user_table_id
72 FROM pay_user_tables put
73 ,pay_user_rows_f pur
74 WHERE put.range_or_match = 'M'
75 AND put.user_table_name ='PQP_TRAVEL_PURPOSE'
76 AND put.user_table_id = pur.user_table_id
77 AND cp_effective_date BETWEEN pur.effective_start_date
78 AND pur.effective_end_date
79 AND pur.row_low_range_or_name = cp_row_name ;
80
81 CURSOR c_get_value (cp_business_group_id NUMBER
82 ,cp_legislation_code VARCHAR2
83 ,cp_row_id NUMBER
84 ,cp_column_id NUMBER
85 ,cp_effective_date DATE
86 )
87 IS
88 SELECT puci.value
89 from pay_user_column_instances_f puci
90 WHERE puci.user_row_id = cp_row_id
91 AND puci.user_column_id = cp_column_id
92 AND ( puci.legislation_code= cp_legislation_code OR
93 puci.business_group_id= cp_business_group_id)
94 AND cp_effective_date between puci.effective_start_date
95 AND puci.effective_end_date;
96
97 CURSOR c_get_element_id ( cp_business_group_id NUMBER
98 ,cp_legislation_code VARCHAR2
99 ,cp_element_name VARCHAR2
100 ,cp_effective_date DATE
101 )
102 IS
103 SELECT pet.element_type_id
104 FROM pay_element_types_f pet
105 WHERE pet.element_name = cp_element_name
106 AND pet.business_group_id = cp_business_group_id
107 AND cp_effective_date BETWEEN pet.effective_start_date
108 AND pet.effective_end_date;
109
110 CURSOR c_get_rate_id ( cp_business_group_id NUMBER
111 ,cp_legislation_code VARCHAR2
112 ,cp_rate_table_name VARCHAR2
113 ,cp_effective_date DATE
114 )
115 IS
116 SELECT put.user_table_id
117 FROM pay_user_tables put
118 WHERE put.user_table_name = cp_rate_table_name
119 AND put.business_group_id = cp_business_group_id;
120
121 l_get_column_info c_get_column_info%ROWTYPE;
122 l_get_row_info c_get_row_info%ROWTYPE;
123 l_get_value c_get_value%ROWTYPE;
124 l_get_element_id c_get_element_id%ROWTYPE;
125 l_get_rate_id c_get_rate_id%ROWTYPE;
126 l_legislation_code pqp_configuration_values.legislation_code%TYPE;
127 l_column_name pay_user_columns.user_column_name%TYPE;
128 l_vehicle VARCHAR2(30) ;
129 l_ownership VARCHAR2(30);
130 l_rates VARCHAR2(30) := 'Rates Table' ;
131 l_element VARCHAR2(30) := 'Claim Element';
132 l_Tax VARCHAr2(30) := 'Taxable';
133
134 BEGIN
135 l_legislation_code := pqp_car_mileage_functions.
136 get_legislation_code (p_business_group_id);
137 OPEN c_get_row_info (p_business_group_id
138 ,l_legislation_code
139 ,p_purpose
140 ,p_effective_date
141 );
142 FETCH c_get_row_info INTO l_get_row_info;
143 CLOSE c_get_row_info ;
144
145 IF p_vehicle_type = 'C' AND
146 p_ownership='P' THEN
147 l_ownership :='Private' ;
148 l_vehicle :='Car';
149
150 ELSIF p_vehicle_type ='C'
151 AND p_ownership='C' THEN
152
153 l_ownership :='Company' ;
154 l_vehicle :='Car';
155 ELSIF p_vehicle_type ='M' AND
156 p_ownership='P' THEN
157
158 l_ownership :='Private' ;
159 l_vehicle :='Motorcycle';
160 ELSIF p_vehicle_type ='P'
161 AND p_ownership='P' THEN
162
163 l_ownership :='Private' ;
164 l_vehicle :='Pedalcycle';
165 ELSIF p_vehicle_type ='M'
166 AND p_ownership='C' THEN
167 l_ownership :='Company' ;
168 l_vehicle :='Motorcycle';
169
170 ELSIF p_vehicle_type ='P'
171 AND p_ownership='C' THEN
172
173 l_ownership :='Company' ;
174 l_vehicle :='Pedalcycle';
175 END IF;
176 FOR i in 1..3
177 LOOP
178 IF i= 1 THEN
179 l_column_name := l_ownership||' '||l_vehicle||' '||l_element;
180 ELSIF i=2 THEN
181 l_column_name := l_ownership||' '||l_vehicle||' '||l_rates;
182 ELSIF i=3 THEN
183 l_column_name := l_ownership||' '||l_tax;
184 END IF;
185 OPEN c_get_column_info (p_business_group_id
186 ,l_legislation_code
187 ,l_get_row_info.user_table_id
188 ,l_column_name
189 );
190 FETCH c_get_column_info INTO l_get_column_info;
191 CLOSE c_get_column_info;
192
193
194 OPEN c_get_value (p_business_group_id
195 ,l_legislation_code
196 ,l_get_row_info.user_row_id
197 ,l_get_column_info.column_id
198 ,p_effective_date
199 );
200 FETCH c_get_value INTO l_get_value;
201 CLOSE c_get_value;
202
203 IF i= 1 THEN
204 p_element_name := l_get_value.value ;
205 OPEN c_get_element_id ( p_business_group_id
206 ,l_legislation_code
207 ,p_element_name
208 ,p_effective_date
209 );
210 FETCH c_get_element_id INTO l_get_element_id;
211 CLOSE c_get_element_id;
212
213 p_element_id:=l_get_element_id.element_type_id;
214 l_get_value.value:=null;
215 ELSIF i=2 THEN
216 p_rate_table :=l_get_value.value ;
217 IF p_rate_type='N' THEN
218 OPEN c_get_rate_id ( p_business_group_id
219 ,l_legislation_code
220 ,p_rate_table
221 ,p_effective_date
222 );
223 FETCH c_get_rate_id INTO l_get_rate_id;
224 CLOSE c_get_rate_id;
225 p_rate_table_id :=l_get_rate_id.user_table_id;
226 END IF;
227 l_get_value.value:=null;
228 ELSIF i=3 THEN
229 p_taxable := SUBSTR(l_get_value.value,0,1) ;
230 l_get_value.value:=null;
231 END IF;
232 END LOOP;
233
234 END;
235
236
237 FUNCTION get_payroll_id ( p_assignment_id IN NUMBER
238 ,p_business_group_id IN NUMBER
239 ,p_effective_date IN DATE
240 ,p_payroll_id OUT NOCOPY NUMBER
241 )
242 RETURN NUMBER
243 IS
244 --Get payroll_id
245 CURSOR c_get_asg_det ( cp_assignment_id NUMBER
246 ,cp_business_group_id NUMBER
247 ,cp_effective_date DATE
248 )
249 IS
250 SELECT payroll_id
251 FROM per_all_assignments_f paaf
252 WHERE paaf.assignment_id=cp_assignment_id
253 AND cp_effective_date BETWEEN paaf.effective_start_date
254 AND paaf.effective_end_date
255 AND paaf.business_group_id =cp_business_group_id
256 AND paaf.payroll_id IS NOT NULL;
257
258 l_get_asg_det c_get_asg_det%ROWTYPE;
259 l_proc varchar2(72) := g_package ||'get_payroll_id';
260 BEGIN
261
262 hr_utility.set_location('Enter get payroll id',10);
263 OPEN c_get_asg_det ( p_assignment_id
264 ,p_business_group_id
265 ,p_effective_date
266 );
267 FETCH c_get_asg_det INTO l_get_asg_det;
268 hr_utility.set_location('Inside the Loop',15);
269 IF c_get_asg_det%NOTFOUND THEN
270 RETURN(-1);
271 ELSE
272 p_payroll_id:=l_get_asg_det.payroll_id;
273
274 RETURN(0);
275 END IF;
276 CLOSE c_get_asg_det;
277
278
279 hr_utility.set_location(' Leaving get payroll id',20);
280
281 END;
282 --Decides effective date to create an element entry
283 FUNCTION get_effective_date (p_assignment_id IN NUMBER
284 ,p_business_group_id IN NUMBER
285 ,p_payroll_id IN NUMBER
286 ,p_effective_date IN DATE
287 )
288 RETURN DATE
289 IS
290
291 --Get next payroll date
292 CURSOR c_get_payroll_det (cp_assignment_id NUMBER
293 ,cp_business_group_id NUMBER
294 ,cp_payroll_id NUMBER
295 ,cp_effective_date DATE
296 )
297 IS
298 SELECT ptp.start_date,ptp.end_date
299 FROM per_time_periods ptp
300 WHERE (ptp.end_date > ( SELECT MAX(effective_date)
301 FROM pay_payroll_actions ppa
302 ,pay_assignment_actions paa
303 WHERE ppa.action_status='C'
304 AND ppa.action_type in ('R','Q')
305 AND ppa.business_group_id=cp_business_group_id
306 AND ppa.payroll_id=cp_payroll_id
307 AND ppa.payroll_id=ptp.payroll_id
308 AND ppa.payroll_action_id=paa.payroll_action_id
309 AND paa.assignment_id=cp_assignment_id
310 AND paa.action_status='C' ))
311 AND ptp.payroll_id=cp_payroll_id
312 AND ROWNUM=1 ;
313
314 --If the above cursor fails to fetch data
315 --when conditions like new employees claim
316 --is processed then this cursor is used.
317
318 CURSOR c_get_alt_payroll_det (cp_payroll_id NUMBER
319 ,cp_effective_date DATE
320 )
321 IS
322 SELECT ptp.start_date,ptp.end_date
323 FROM per_time_periods ptp
324 WHERE ptp.end_date >= cp_effective_date
325 AND ptp.payroll_id=cp_payroll_id
326 AND ROWNUM=1 ;
327
328
329 l_get_payroll_det c_get_payroll_det%ROWTYPE;
330 l_get_alt_payroll_det c_get_alt_payroll_det%ROWTYPE;
331 l_effective_date DATE;
332 l_proc varchar2(72) := g_package ||'get_effective_date';
333 BEGIN
334 hr_utility.set_location(l_proc,10);
335 OPEN c_get_payroll_det ( p_assignment_id
336 ,p_business_group_id
337 ,p_payroll_id
338 ,p_effective_date
339 );
340 FETCH c_get_payroll_det INTO l_get_payroll_det;
341 IF c_get_payroll_det%NOTFOUND THEN
342 OPEN c_get_alt_payroll_det (p_payroll_id
343 ,p_effective_date
344 );
345 FETCH c_get_alt_payroll_det INTO l_get_payroll_det;
346
347 CLOSE c_get_alt_payroll_det;
348 END IF;
349 hr_utility.set_location(l_proc,20);
350 CLOSE c_get_payroll_det;
351 IF p_effective_date BETWEEN l_get_payroll_det.start_date
352 AND l_get_payroll_det.end_date
353 OR p_effective_date > l_get_payroll_det.end_date THEN
354 l_effective_date :=p_effective_date;
355 ELSE
356 l_effective_date :=l_get_payroll_det.start_date;
357 END IF;
358
359 RETURN(l_effective_date);
360 hr_utility.set_location(l_proc,30);
361 END;
362
363 FUNCTION get_asg_element (p_assignment_id IN NUMBER,
364 p_business_group_id IN NUMBER,
365 p_effective_date IN DATE,
366 p_ownership_type IN VARCHAR2,
367 p_registration_number IN VARCHAR2,
368 p_usage_type IN VARCHAR2,
369 p_check_type IN VARCHAR2,
370 p_sl_rates_type IN VARCHAR2
371 )
372 RETURN VARCHAR2
373 IS
374 --If mileage element is null then fetch element and rates from
375 -- assignment level
376 CURSOR c_get_asg_element (cp_assignment_id NUMBER,
377 cp_business_group_id NUMBER,
378 cp_effective_date DATE,
379 cp_ownership_type VARCHAR2,
380 cp_registration_number VARCHAR2
381 )
382 IS
383 SELECT pvaf.element_type_id
384 ,pvaf.rates_table_id
385 ,pvaf.sliding_rates_info sliding_rates
386 FROM pqp_vehicle_allocations_f pvaf,
387 pqp_vehicle_repository_f pvrf
388 WHERE pvaf.assignment_id=cp_assignment_id
389 AND pvaf.business_group_id=cp_business_group_id
390 AND pvrf.vehicle_ownership=cp_ownership_type
391 AND (p_registration_number IS NULL or
392 pvrf.registration_number=cp_registration_number)
393 AND pvaf.usage_type=p_usage_type
394 AND cp_effective_date BETWEEN pvaf.effective_start_date
395 AND pvaf.effective_end_date
396 AND pvaf.vehicle_repository_id=pvrf.vehicle_repository_id
397 AND pvaf.business_group_id=pvrf.business_group_id
398 AND cp_effective_date BETWEEN pvrf.effective_start_date
399 AND pvrf.effective_end_date;
400
401 CURSOR c_ele_type (cp_element_type_id NUMBER)
402 IS
403 SELECT NVL(pete.eei_information2 ,'N') ele_type
404 FROM pay_element_type_extra_info pete
405 WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
406 AND pete.element_type_id= cp_element_type_id;
407
408 l_proc varchar2(72) := g_package ||'get_asg_element';
409 l_get_asg_element c_get_asg_element%ROWTYPE;
410 l_ele_type c_ele_type%ROWTYPE;
411 BEGIN
412 hr_utility.set_location('Inside get asg element'||l_proc,10);
413 OPEN c_get_asg_element (
414 p_assignment_id
415 ,p_business_group_id
416 ,p_effective_date
417 ,p_ownership_type
418 ,p_registration_number
419 );
420 FETCH c_get_asg_element INTO l_get_asg_element;
421 CLOSE c_get_asg_element;
422
423 OPEN c_ele_type (l_get_asg_element.element_type_id);
424 FETCH c_ele_type INTO l_ele_type;
425 CLOSE c_ele_type;
426 hr_utility.set_location(l_proc,20);
427
428 IF p_check_type='E' AND (l_ele_type.ele_type=p_sl_rates_type) THEN
429 RETURN(NVL(l_get_asg_element.element_type_id,-1));
430 ELSE
431 RETURN (-1);
432 END IF;
433 IF p_check_type='R' AND p_sl_rates_type ='N' THEN
434
435 RETURN(NVL(l_get_asg_element.rates_table_id,-1));
436 ELSIF p_check_type='R' AND p_sl_rates_type ='Y' THEN
437
438 RETURN(NVL(l_get_asg_element.sliding_rates,-1));
439 END IF;
440 hr_utility.set_location('leaving get asg element'||l_proc,30);
441 END;
442
443 --Get default rates if entered
444 FUNCTION get_default_value(p_business_group_id IN NUMBER
445 ,p_element_type_id IN NUMBER
446 ,p_effective_date IN DATE
447 ,p_search_type IN VARCHAR2
448 )
449 RETURN NUMBER
450 IS
451 CURSOR c_get_default_value
452 IS
453 SELECT piv.default_value
454 FROM pay_input_values_f piv
455 WHERE piv.element_type_id= p_element_type_id
456 AND piv.name =p_search_type
457 AND p_effective_date BETWEEN piv.effective_start_date
458 AND piv.effective_end_date;
459
460 CURSOR c_get_rate_id(cp_user_table_name VARCHAR2)
461 IS
462 SELECT user_table_id
463 from pay_user_tables put
464 WHERE user_table_name=cp_user_table_name
465 AND put.business_group_id =p_business_group_id;
466
467 l_get_default_value c_get_default_value%ROWTYPE;
468 l_get_rate_id c_get_rate_id%ROWTYPE;
469 l_proc varchar2(72) := g_package ||'get_default_value';
470
471 BEGIN
472 hr_utility.set_location(l_proc,10);
473 OPEN c_get_default_value;
474 FETCH c_get_default_value INTO l_get_default_value;
475 IF c_get_default_value%FOUND THEN
476 OPEN c_get_rate_id(l_get_default_value.default_value);
477 FETCH c_get_rate_id INTO l_get_rate_id;
478 RETURN(NVL(l_get_rate_id.user_table_id,-1));
479
480 CLOSE c_get_rate_id;
481 ELSE
482 RETURN(-1);
483 END IF;
484 CLOSE c_get_default_value;
485
486 hr_utility.set_location(l_proc,20);
487 END;
488
489
490 --Validate Mileage Element.
491 FUNCTION validate_mileage_element ( p_assignment_id IN NUMBER
492 ,p_business_group_id IN NUMBER
493 ,p_effective_date IN DATE
494 ,p_vehicle_type IN VARCHAR2
495 ,p_ownership IN VARCHAR2
496 ,p_element_type_id IN NUMBER
497 ,p_sl_rate_type IN VARCHAR2
498 ,p_element_link_id OUT NOCOPY NUMBER
499 )
500 RETURN NUMBER
501 IS
502
503 --Validate mileage element
504 CURSOR c_validate_mileage_element( cp_assignment_id NUMBER
505 ,cp_business_group_id NUMBER
506 ,cp_effective_date DATE
507 ,cp_vehicle_type VARCHAR2
508 ,cp_ownership VARCHAR2
509 ,cp_element_type_id NUMBER
510 ,cp_sl_rate_type VARCHAR2
511 )
512 IS
513 SELECT element.element_type_id
514 , elementtl.element_name
515 ,link.element_link_id
516 FROM pay_element_types_f_tl elementtl,
517 pay_element_types_f element,
518 pay_element_links_f link,
519 per_all_assignments_f asgt ,
520 pay_element_type_extra_info pete,
521 per_periods_of_service service_period
522 WHERE element.element_type_id = elementtl.element_type_id
523 AND elementtl.language = USERENV('LANG')
524 AND asgt.business_group_id = link.business_group_id
525 AND asgt.business_group_id = element.business_group_id
526 AND element.business_group_id =link.business_group_id
527 AND asgt.business_group_id =service_period.business_group_id
528 AND element.element_type_id = link.element_type_id
529 AND service_period.period_of_service_id = asgt.period_of_service_id
530 AND cp_effective_date
531 between element.effective_start_date and element.effective_end_date
532 AND cp_effective_date
533 between asgt.effective_start_date and asgt.effective_end_date
534 AND cp_effective_date
535 between link.effective_start_date and link.effective_end_date
536 AND element.indirect_only_flag = 'N'
537 AND ((link.payroll_id is NOT NULL AND
538 link.payroll_id = asgt.payroll_id)
539 OR (link.link_to_all_payrolls_flag = 'Y'
540 AND asgt.payroll_id IS NOT NULL)
541 OR (link.payroll_id IS NULL AND link.link_to_all_payrolls_flag = 'N'))
542 AND (link.organization_id = asgt.organization_id
543 OR link.organization_id IS NULL)
544 AND (link.position_id = asgt.position_id OR link.position_id IS NULL)
545 AND (link.job_id = asgt.job_id OR link.job_id IS NULL)
546 AND (link.grade_id = asgt.grade_id OR link.grade_id IS NULL)
547 AND (link.location_id = asgt.location_id OR link.location_id IS NULL)
548 AND (link.pay_basis_id = asgt.pay_basis_id OR link.pay_basis_id IS NULL)
549 AND (link.employment_category = asgt.employment_category
550 OR link.employment_category IS NULL)
551 AND (link.people_group_id IS NULL
552 OR EXISTS
553 ( SELECT 1 FROM pay_assignment_link_usages_f usage
554 WHERE usage.assignment_id = asgt.assignment_id
555 AND usage.element_link_id = link.element_link_id
556 AND cp_effective_date BETWEEN usage.effective_start_date
557 AND usage.effective_end_date))
558 AND (service_period.actual_termination_date
559 IS NULL OR (service_period.actual_termination_date IS NOT NULL
560 AND cp_effective_date <= DECODE(element.post_termination_rule, 'L',
561 service_period.last_standard_process_date, 'F',
562 NVL(service_period.final_process_date,hr_api.g_eot),
563 service_period.actual_termination_date) ))
564 AND element.element_type_id = pete.element_type_id
565 AND pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
566 AND pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
567 AND pete.eei_information1<>'L'
568 AND NVL(pete.eei_information2,'N')=cp_sl_rate_type
569 AND asgt.assignment_id=cp_assignment_id
570 AND asgt.business_group_id=cp_business_group_id
571 AND pete.eei_information1=DECODE(cp_vehicle_type,'C',cp_ownership,
572 cp_ownership||cp_vehicle_type)
573 AND element.element_type_id=cp_element_type_id;
574
575
576 l_proc varchar2(72) := g_package ||'validate_mileage_element';
577 l_validate_mileage_element c_validate_mileage_element%ROWTYPE;
578 BEGIN
579
580 hr_utility.set_location(l_proc,10);
581
582 OPEN c_validate_mileage_element( p_assignment_id
583 ,p_business_group_id
584 ,p_effective_date
585 ,p_vehicle_type
586 ,p_ownership
587 ,p_element_type_id
588 ,p_sl_rate_type
589 );
590 LOOP
591 FETCH c_validate_mileage_element INTO
592 l_validate_mileage_element;
593 EXIT WHEN c_validate_mileage_element%NOTFOUND;
594 hr_utility.set_location(l_proc,20);
595 IF l_validate_mileage_element.element_type_id IS NOT NULL THEN
596 p_element_link_id := l_validate_mileage_element.element_link_id;
597 RETURN(l_validate_mileage_element.element_type_id);
598 EXIT;
599 END IF;
600 END LOOP;
601 CLOSE c_validate_mileage_element;
602 IF l_validate_mileage_element.element_type_id IS NULL THEN
603 p_element_link_id :=-1;
604 RETURN(-1);
605 END IF;
606
607 hr_utility.set_location(l_proc,30);
608 EXCEPTION
609 --------
610 WHEN OTHERS THEN
611 RETURN(-1);
612 hr_utility.set_location(l_proc,40);
613 END;
614
615 --Get eligible mileage element if no element is entered at
616 --all levels.
617 FUNCTION get_elig_mileage_element( p_assignment_id IN NUMBER
618 ,p_business_group_id IN NUMBER
619 ,p_effective_date IN DATE
620 ,p_vehicle_type IN VARCHAR2
621 ,p_ownership IN VARCHAR2
622 ,p_sl_rate_type IN VARCHAR2
623 ,p_element_link_id OUT NOCOPY NUMBER
624 )
625
626 RETURN NUMBER
627 IS
628
629 CURSOR c_get_elig_mileage_element( cp_assignment_id NUMBER
630 ,cp_business_group_id NUMBER
631 ,cp_effective_date DATE
632 ,cp_vehicle_type VARCHAR2
633 ,cp_ownership VARCHAR2
634 ,cp_sl_rate_type VARCHAR2
635 )
636 IS
637 SELECT element.element_type_id , elementtl.element_name
638 FROM pay_element_types_f_tl elementtl,
639 pay_element_types_f element,
640 pay_element_links_f link,
641 per_all_assignments_f asgt ,
642 pay_element_type_extra_info pete,
643 per_periods_of_service service_period
644 WHERE element.element_type_id = elementtl.element_type_id
645 AND elementtl.language = USERENV('LANG')
646 AND asgt.business_group_id = link.business_group_id
647 AND asgt.business_group_id = element.business_group_id
648 AND element.business_group_id =link.business_group_id
649 AND asgt.business_group_id =service_period.business_group_id
650 AND element.element_type_id = link.element_type_id
651 AND service_period.period_of_service_id = asgt.period_of_service_id
652 AND cp_effective_date
653 between element.effective_start_date and element.effective_end_date
654 AND cp_effective_date
655 between asgt.effective_start_date and asgt.effective_end_date
656 AND cp_effective_date
657 between link.effective_start_date and link.effective_end_date
658 AND element.indirect_only_flag = 'N'
659 AND ((link.payroll_id is NOT NULL AND
660 link.payroll_id = asgt.payroll_id)
661 OR (link.link_to_all_payrolls_flag = 'Y'
662 AND asgt.payroll_id IS NOT NULL)
663 OR (link.payroll_id IS NULL
664 AND link.link_to_all_payrolls_flag = 'N'))
665 AND (link.organization_id = asgt.organization_id
666 OR link.organization_id IS NULL)
667 AND (link.position_id = asgt.position_id
668 OR link.position_id IS NULL)
669 AND (link.job_id = asgt.job_id OR link.job_id IS NULL)
670 AND (link.grade_id = asgt.grade_id OR link.grade_id IS NULL)
671 AND (link.location_id = asgt.location_id
672 OR link.location_id IS NULL)
673 AND (link.pay_basis_id = asgt.pay_basis_id
674 OR link.pay_basis_id IS NULL)
675 AND (link.employment_category = asgt.employment_category
676 OR link.employment_category IS NULL)
677 AND (link.people_group_id IS NULL OR EXISTS
678 ( SELECT 1 FROM pay_assignment_link_usages_f usage
679 WHERE usage.assignment_id = asgt.assignment_id
680 AND usage.element_link_id = link.element_link_id
681 AND cp_effective_date BETWEEN usage.effective_start_date
682 AND usage.effective_end_date))
683 AND (service_period.actual_termination_date
684 IS NULL OR (service_period.actual_termination_date IS NOT NULL
685 AND cp_effective_date <=
686 DECODE(element.post_termination_rule, 'L',
687 service_period.last_standard_process_date, 'F',
688 NVL(service_period.final_process_date,hr_api.g_eot),
689 service_period.actual_termination_date) ))
690 AND element.element_type_id = pete.element_type_id
691 AND pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
692 AND pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
693 AND pete.eei_information1<>'L'
694 AND NVL(pete.eei_information2,'N')= cp_sl_rate_type
695 AND asgt.assignment_id=cp_assignment_id
696 AND asgt.business_group_id=cp_business_group_id
697 AND pete.eei_information1=DECODE(cp_vehicle_type,'C',cp_ownership,
698 cp_ownership||cp_vehicle_type)
699 ORDER BY element.effective_start_date DESC;
700
701
702
703
704 l_proc varchar2(72) := g_package ||'get_elig_mileage_element';
705 l_get_elig_mileage_element c_get_elig_mileage_element%ROWTYPE;
706 l_validate_mileage_element pay_element_types_f.element_type_id%TYPE;
707 BEGIN
708
709 hr_utility.set_location(l_proc,10);
710 OPEN c_get_elig_mileage_element( p_assignment_id
711 ,p_business_group_id
712 ,p_effective_date
713 ,p_vehicle_type
714 ,p_ownership
715 ,p_sl_rate_type
716 );
717 LOOP
718 FETCH c_get_elig_mileage_element INTO
719 l_get_elig_mileage_element;
720 EXIT WHEN c_get_elig_mileage_element%NOTFOUND;
721 hr_utility.set_location(' Enter validate element:' || l_proc,100);
722 l_validate_mileage_element:= validate_mileage_element(
723 p_assignment_id =>p_assignment_id
724 ,p_business_group_id =>p_business_group_id
725 ,p_effective_date =>p_effective_date
726 ,p_vehicle_type =>p_vehicle_type
727 ,p_ownership =>p_ownership
728 ,p_element_type_id
729 =>l_get_elig_mileage_element.element_type_id
730 ,p_sl_rate_type =>p_sl_rate_type
731 ,p_element_link_id =>p_element_link_id
732 );
733 hr_utility.set_location(l_proc,20);
734 IF l_validate_mileage_element <> -1 THEN
735 RETURN(l_get_elig_mileage_element.element_type_id);
736 EXIT;
737 END IF;
738 END LOOP;
739 CLOSE c_get_elig_mileage_element;
740
741 hr_utility.set_location(l_proc,30);
742 IF l_get_elig_mileage_element.element_type_id IS NULL
743 OR l_validate_mileage_element =-1 THEN
744 RETURN(-1);
745 END IF;
746
747 hr_utility.set_location(l_proc,40);
748 EXCEPTION
749 --------
750 WHEN OTHERS THEN
751 RETURN(-1);
752 hr_utility.set_location(l_proc,50);
753 END;
754
755 --Check if CC is available in the repository
756 FUNCTION get_cc (p_effective_date IN DATE,
757 p_business_group_id IN NUMBER,
758 p_registration_number IN VARCHAR2,
759 p_engine_capacity OUT NOCOPY VARCHAR2
760 )
761 RETURN VARCHAR2
762 IS
763 CURSOR c_get_cc
764 IS
765 SELECT pvr.engine_capacity_in_cc engine_capacity
766 FROM pqp_vehicle_repository_f pvr
767 WHERE pvr.business_group_id =p_business_group_id
768 AND pvr.registration_number =p_registration_number
769 AND p_effective_date BETWEEN pvr.effective_start_date
770 AND pvr.effective_end_date
771 AND pvr.engine_capacity_in_cc IS NOT NULL;
772 l_get_cc c_get_cc%ROWTYPE;
773 l_proc varchar2(72) := g_package ||'get_cc';
774 BEGIN
775
776 hr_utility.set_location(l_proc,10);
777 OPEN c_get_cc;
778 FETCH c_get_cc INTO l_get_cc;
779 IF c_get_cc%FOUND THEN
780 p_engine_capacity :=l_get_cc.engine_capacity;
781 RETURN ('Y');
782 hr_utility.set_location(l_proc,20);
783 ELSE
784 RETURN ('N');
785 hr_utility.set_location(l_proc,30);
786 END IF;
787 CLOSE c_get_cc;
788
789 hr_utility.set_location(l_proc,40);
790 EXCEPTION
791 --------
792 WHEN OTHERS THEN
793 hr_utility.set_location(l_proc,50);
794 RETURN ('N');
795
796 END;
797
798 --Main Insert Procedure
799 --here the process works in following order
800 --If mileage element is entered in the UI then
801 --the element is created directly by just checking the
802 --link . If the element is not entered at UI level then
803 --the allocation level is checked for any elements
804 --if not then the config level is checked, finally the link
805 --is checked and if any element is found that can be created for
806 --the employee.The rates work the same way.
807 PROCEDURE insert_mileage_claim
808 ( p_effective_date IN DATE,
809 p_web_adi_identifier IN VARCHAR2 ,
810 p_info_id IN VARCHAR2 ,
811 p_time_stamp IN VARCHAR2 ,
812 p_assignment_id IN NUMBER,
813 p_business_group_id IN NUMBER,
814 p_ownership IN VARCHAR2 ,
815 p_usage_type IN VARCHAR2 ,
816 p_vehicle_type IN VARCHAR2,
817 p_start_date IN VARCHAR2 ,
818 p_end_date IN VARCHAR2 ,
819 p_claimed_mileage IN VARCHAR2 ,
820 p_actual_mileage IN VARCHAR2 ,
821 p_registration_number IN VARCHAR2 ,
822 p_engine_capacity IN VARCHAR2 ,
823 p_fuel_type IN VARCHAR2 ,
824 p_calculation_method IN VARCHAR2 ,
825 p_user_rates_table IN VARCHAR2 ,
826 p_fiscal_ratings IN VARCHAR2 ,
827 p_PAYE_taxable IN VARCHAR2 ,
828 p_no_of_passengers IN VARCHAR2 ,
829 p_purpose IN VARCHAR2 ,
830 p_data_source IN VARCHAR2 ,
831 p_user_type IN VARCHAR2 ,
832 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
833 p_element_entry_id IN OUT NOCOPY NUMBER ,
834 p_element_entry_date IN OUT NOCOPY DATE
835 )
836
837 IS
838
839
840 --If Mileage element is not found at assignment level
841 --then fetch from configuration value table.
842 CURSOR c_get_config_element(cp_business_group_id NUMBER)
843 IS
844 select TO_NUMBER(pcv.pcv_information6) element_type_id
845 ,TO_NUMBER(pcv.pcv_information5) Rates_table_id
846 from pqp_configuration_values pcv
847 WHERE pcv.pcv_information_category='PQP_MILEAGE CALC_INFO'
848 AND pcv.business_group_id=cp_business_group_id;
849
850
851
852 --Validate mileage element
853 --Get element link details
854 CURSOR c_get_ele_link ( cp_element_type_id NUMBER
855 ,cp_effective_date DATE
856 ,cp_business_group_id NUMBER
857 )
858 IS
859 SELECT pelf.element_link_id
860 FROM pay_element_links_f pelf
861 WHERE pelf.element_type_id=cp_element_type_id
862 AND cp_effective_date BETWEEN pelf.effective_start_date
863 AND pelf.effective_end_date
864 AND pelf.business_group_id=cp_business_group_id;
865
866
867 --Variables
868 l_proc varchar2(72) := g_package ||'insert_mileage_claim';
869 l_get_pay_det per_all_assignments_f.payroll_id%TYPE;
870 --l_get_payroll_det c_get_payroll_det%ROWTYPE;
871 --l_get_alt_payroll_det c_get_alt_payroll_det%ROWTYPE;
872 l_get_asg_element_id pay_element_types_f.element_type_id%TYPE;
873 l_get_purp_element_id pay_element_types_f.element_type_id%TYPE;
874 l_get_asg_rate_id VARCHAR2(80);
875 l_get_config_element c_get_config_element%ROWTYPE;
876 l_valid_mileage_element_id pay_element_types_f.element_type_id%TYPE;
877 l_elig_mileage_element_id pay_element_types_f.element_type_id%TYPE;
878 l_get_ele_link c_get_ele_link%ROWTYPE;
879 l_effective_date DATE;
880 l_element_type_id pay_element_types_f.element_type_id%TYPE;
881 l_element_link_id pay_element_links_f.element_link_id%TYPE;
882 l_rates_table_id VARCHAR2(80); --pay_user_tables.user_table_id%TYPE;
883 l_get_element_type_id pqp_configuration_values.pcv_information5%TYPE;
884 l_get_rate_id pqp_configuration_values.pcv_information6%TYPE;
885 l_chk_eligibility VARCHAR2(1);
886 l_chk_mndtry VARCHAR2(1);
887 l_message VARCHAR2(100);
888 l_session_id pay_us_rpt_totals.GRE_NAME%TYPE;
889 l_businesss_group_id pay_us_rpt_totals.BUSINESS_GROUP_ID%TYPE;
890 l_assignment_id pay_us_rpt_totals.LOCATION_ID%TYPE;
891 l_string VARCHAR2(3):='___';
892 l_ret_mesg NUMBER(2);
893 l_ret_cc VARCHAR2(1);
894 l_engine_capacity VARCHAR2(9);
895 l_canonical_st_dt VARCHAR2(24);
896 l_canonical_ed_dt VARCHAR2(24);
897 l_st_dt DATE;
898 l_ed_dt DATE;
899 l_user_type VARCHAR2(3);
900 l_purp_element_name pay_element_types_f.element_name%TYPE;
901 l_purp_rates_table_id pay_user_tables.user_table_id%TYPE;
902 l_purp_rates_table pay_user_tables.user_table_name%TYPE;
903 l_purp_taxable VARCHAR2(3);
904 l_sliding_rates VARCHAR2(1);
905 l_paye_taxable VARCHAR2(3);
906 BEGIN
907 l_paye_taxable :=p_paye_taxable;
908 hr_utility.set_location(' Enter:' || l_proc,10);
909
910 IF p_user_type='PUI' THEN
911 l_user_type := pqp_car_mileage_functions.get_config_info
912 (p_business_group_id => p_business_group_id
913 ,p_info_type => 'Professional User'
914 );
915 END IF;
916
917 l_sliding_rates :=pqp_car_mileage_functions.get_config_info
918 (p_business_group_id => p_business_group_id
919 ,p_info_type => 'Rates'
920 );
921
922 --This call for claim Insert
923 IF p_info_id IS NULL THEN
924
925 --Check if CC is correct
926
927 l_ret_cc :=get_cc
928 (
929 p_effective_date =>p_effective_date,
930 p_business_group_id =>p_business_group_id,
931 p_registration_number =>p_registration_number,
932 p_engine_capacity =>l_engine_capacity
933 );
934
935 IF l_ret_cc ='N' AND p_engine_capacity IS NOT NULL THEN
936
937 l_engine_capacity :=p_engine_capacity;
938 ELSIF p_engine_capacity IS NULL AND l_ret_cc ='N'AND p_vehicle_type<>'P' THEN
939 fnd_message.set_name('PQP','PQP_230736_CC_MNDTRY');
940 fnd_message.raise_error;
941 hr_multi_message.end_validation_set;
942 ELSIF p_vehicle_type ='P' THEN
943
944 l_engine_capacity:='0';
945 END IF;
946
947 hr_utility.set_location(' Enter payroll check cursor:' || l_proc,20);
948
949 ---Get assignemnt payroll info.
950 l_ret_mesg:=get_payroll_id
951 (p_assignment_id =>p_assignment_id
952 ,p_business_group_id=>p_business_group_id
953 ,p_effective_date =>p_effective_date
954 ,p_payroll_id =>l_get_pay_det
955 );
956 IF l_ret_mesg<>0 THEN
957 fnd_message.set_name('PQP','PQP_230857_PAYROLL_NOT_EXST');
958 fnd_message.raise_error;
959 hr_multi_message.end_validation_set;
960 END IF;
961
962 hr_utility.set_location(' Enter payroll details cursor:' || l_proc,30);
963 --get next unprocessed payroll date for that asg.
964 --If the effective date is in between or less then
965 --payroll run date then the date the element
966 --entry created will be effective date or the start date
967 --if the effective date is lesser then the start date.
968 --If greated than the payroll dates even then the effective
969 --date is considered.
970 l_effective_date:=get_effective_date
971 ( p_assignment_id =>p_assignment_id
972 ,p_business_group_id =>p_business_group_id
973 ,p_payroll_id =>l_get_pay_det
974 ,p_effective_date =>p_effective_date
975 );
976
977 hr_utility.set_location(' Enter check eligibility:' || l_proc,40);
978 --Check for claim eligibility
979 l_chk_eligibility:=pqp_gb_mileage_claim_pkg.
980 chk_eligibility (
981 p_effective_date =>l_effective_date
982 ,p_assignment_id =>p_assignment_id
983 ,p_business_group_id =>p_business_group_id
984 ,p_ownership =>p_ownership
985 ,p_usage_type =>p_usage_type
986 ,p_vehicle_type =>p_vehicle_type
987 ,p_start_date =>p_start_date
988 ,p_end_date =>p_end_date
989 ,p_claimed_mileage =>p_claimed_mileage
990 ,p_actual_mileage =>p_actual_mileage
991 ,p_registration_number =>p_registration_number
992 ,p_data_source =>p_data_source
993 ,p_message =>l_message
994 );
995
996 IF l_chk_eligibility = 'N' THEN
997 fnd_message.raise_error;
998 hr_multi_message.end_validation_set;
999 END IF;
1000 hr_utility.set_location(' Enter Assignment level Element search:' || l_proc,50);
1001 --Find if element is present at the assignment level
1002
1003 IF p_mileage_claim_element IS NULL THEN
1004 IF p_user_type='SS' OR ( p_user_type='PUI' AND l_user_type='Y') THEN
1005 get_purpose_details (p_business_group_id =>p_business_group_id
1006 ,p_assignment_id =>p_assignment_id
1007 ,p_effective_date =>l_effective_date
1008 ,p_purpose =>p_purpose
1009 ,p_ownership =>p_ownership
1010 ,p_vehicle_type =>p_vehicle_type
1011 ,p_rate_type =>l_user_type
1012 ,p_element_id =>l_get_purp_element_id
1013 ,p_element_name =>l_purp_element_name
1014 ,p_rate_table_id =>l_purp_rates_table_id
1015 ,p_rate_table =>l_purp_rates_table
1016 ,p_taxable =>l_purp_taxable
1017 );
1018 l_paye_taxable:=l_purp_taxable;
1019 END IF;
1020 IF l_get_purp_element_id IS NULL THEN
1021 l_get_asg_element_id:= get_asg_element (
1022 p_assignment_id =>p_assignment_id
1023 ,p_business_group_id =>p_business_group_id
1024 ,p_effective_date =>p_effective_date
1025 ,p_ownership_type =>p_ownership
1026 ,p_registration_number =>p_registration_number
1027 ,p_usage_type =>p_usage_type
1028 ,p_check_type =>'E'
1029 ,p_sl_rates_type =>l_sliding_rates
1030 );
1031
1032 END IF;
1033 hr_utility.set_location(' Enter config level Element search:' || l_proc,60);
1034 --if element is not present at assignment level then fetch from conf table
1035 IF (l_get_asg_element_id IS NULL OR l_get_asg_element_id=-1 )
1036 AND l_get_purp_element_id IS NULL THEN
1037 pqp_gb_mileage_claim_pkg.get_config_info (
1038 p_business_group_id =>p_business_group_id
1039 ,p_ownership =>p_ownership
1040 ,p_usage_type =>p_usage_type
1041 ,p_vehicle_type =>p_vehicle_type
1042 ,p_fuel_type =>p_fuel_type
1043 ,p_sl_rates_type =>l_sliding_rates
1044 ,p_rates =>l_get_rate_id
1045 ,p_element_id =>l_get_element_type_id
1046 );
1047
1048 l_element_type_id :=to_number(l_get_element_type_id);
1049 ELSE
1050 l_element_type_id :=NVL(l_get_purp_element_id,l_get_asg_element_id);
1051 END IF;
1052 ELSE
1053 l_element_type_id :=to_number(p_mileage_claim_element);
1054 END IF;
1055
1056
1057 hr_utility.set_location(' Enter db level element search:' || l_proc,90);
1058 --If no element is entered in all levels then
1059 --it must be searched in the database and validated.
1060 IF l_element_type_id IS NULL THEN
1061 l_elig_mileage_element_id:=get_elig_mileage_element
1062 (p_assignment_id => p_assignment_id
1063 ,p_business_group_id =>p_business_group_id
1064 ,p_effective_date =>l_effective_date
1065 ,p_vehicle_type =>p_vehicle_type
1066 ,p_ownership =>p_ownership
1067 ,p_sl_rate_type =>l_sliding_rates
1068 ,p_element_link_id =>l_element_link_id
1069 );
1070 IF l_elig_mileage_element_id <> -1 THEN
1071 l_element_type_id :=l_elig_mileage_element_id;
1072 END IF;
1073 ELSE
1074 l_valid_mileage_element_id:=validate_mileage_element
1075 (p_assignment_id => p_assignment_id
1076 ,p_business_group_id =>p_business_group_id
1077 ,p_effective_date =>p_effective_date
1078 ,p_vehicle_type =>p_vehicle_type
1079 ,p_ownership =>p_ownership
1080 ,p_element_type_id =>to_number(l_element_type_id)
1081 ,p_sl_rate_type =>l_sliding_rates
1082 ,p_element_link_id =>l_element_link_id
1083 );
1084 IF l_valid_mileage_element_id IS NULL OR l_valid_mileage_element_id=-1 THEN
1085
1086 l_elig_mileage_element_id:=get_elig_mileage_element
1087 (p_assignment_id => p_assignment_id
1088 ,p_business_group_id =>p_business_group_id
1089 ,p_effective_date =>l_effective_date
1090 ,p_vehicle_type =>p_vehicle_type
1091 ,p_ownership =>p_ownership
1092 ,p_sl_rate_type =>l_sliding_rates
1093 ,p_element_link_id =>l_element_link_id
1094 );
1095 IF l_elig_mileage_element_id <> -1 THEN
1096 l_element_type_id :=l_elig_mileage_element_id;
1097
1098 END IF;
1099 ELSE
1100 l_element_type_id :=l_valid_mileage_element_id;
1101 END IF;
1102 END IF;
1103
1104 IF l_element_type_id IS NULL THEN
1105 fnd_message.set_name('PQP','PQP_230732_VLD_MLG_ELE_FAIL');
1106 fnd_message.raise_error;
1107 hr_multi_message.end_validation_set;
1108 END IF;
1109
1110 hr_utility.set_location(' Enter element link search:' || l_proc,110);
1111 /* OPEN c_get_ele_link (l_element_type_id
1112 ,l_effective_date
1113 ,p_business_group_id
1114 );
1115 LOOP
1116 FETCH c_get_ele_link INTO l_get_ele_link;
1117 EXIT WHEN c_get_ele_link%NOTFOUND;
1118 l_element_link_id:=l_get_ele_link.element_link_id;
1119 END LOOP;
1120 CLOSE c_get_ele_link;*/
1121
1122 hr_utility.set_location(' Enter Assignment level rates search:' || l_proc,70);
1123 --If Rates is Null ,then fetch from assignment level
1124 IF p_user_rates_table IS NULL THEN
1125 IF p_user_type='SS' OR ( p_user_type='PUI' AND l_user_type='Y') THEN
1126 hr_utility.set_location(' Enter Assignment If condition:' || l_proc,80);
1127 l_rates_table_id:=l_purp_rates_table;
1128 END IF;
1129 IF l_rates_table_id IS NULL THEN
1130 hr_utility.set_location(' Enter Assignment Second If condition:' || l_proc,80);
1131 l_get_asg_rate_id:= get_asg_element
1132 (p_assignment_id =>p_assignment_id
1133 ,p_business_group_id =>p_business_group_id
1134 ,p_effective_date =>p_effective_date
1135 ,p_ownership_type =>p_ownership
1136 ,p_registration_number =>p_registration_number
1137 ,p_usage_type =>p_usage_type
1138 ,p_check_type =>'R'
1139 ,p_sl_rates_type =>l_sliding_rates
1140 );
1141 END IF;
1142 --if rates table is not present at assignment level
1143 --then fetch from conf table
1144 hr_utility.set_location(' Enter conf level rates search:' || l_proc,80);
1145 IF (l_get_asg_rate_id IS NULL OR l_get_asg_rate_id=-1 ) AND
1146 l_rates_table_id IS NULL THEN
1147 l_get_element_type_id := l_element_type_id;
1148 pqp_gb_mileage_claim_pkg.get_config_info (
1149 p_business_group_id =>p_business_group_id
1150 ,p_ownership =>p_ownership
1151 ,p_usage_type =>p_usage_type
1152 ,p_vehicle_type =>p_vehicle_type
1153 ,p_fuel_type =>p_fuel_type
1154 ,p_sl_rates_type =>l_sliding_rates
1155 ,p_rates =>l_get_rate_id
1156 ,p_element_id =>l_get_element_type_id
1157 );
1158
1159 l_rates_table_id :=to_number(l_get_rate_id);
1160 l_get_asg_rate_id:=to_number(l_get_rate_id);
1161 IF l_get_asg_rate_id IS NULL OR l_get_asg_rate_id=-1 THEN
1162 l_get_asg_rate_id := get_default_value
1163 (p_business_group_id =>p_business_group_id
1164 ,p_element_type_id =>l_element_type_id
1165 ,p_effective_date =>p_effective_date
1166 ,p_search_type =>'User Rates Table'
1167 );
1168
1169 --l_rates_table_id :=to_number(l_get_rate_id);
1170 l_rates_table_id :=to_number(l_get_asg_rate_id);
1171 ELSE
1172 l_rates_table_id :=l_get_asg_rate_id;
1173 END IF;
1174 ELSE
1175 l_rates_table_id :=NVL(l_get_asg_rate_id,l_rates_table_id);
1176 END IF;
1177 ELSE
1178 l_rates_table_id :=(p_user_rates_table);
1179 END IF;
1180
1181
1182 hr_utility.set_location(' Enter mndtry field chk:' || l_proc,120);
1183 --All mandatory field validations
1184 l_chk_mndtry:= chk_mndtry_fields (
1185 p_effective_date =>p_effective_date
1186 ,p_assignment_id =>p_assignment_id
1187 ,p_business_group_id =>p_business_group_id
1188 ,p_ownership =>p_ownership
1189 ,p_usage_type =>p_usage_type
1190 ,p_vehicle_type =>p_vehicle_type
1191 ,p_start_date =>p_start_date
1192 ,p_end_date =>p_end_date
1193 ,p_claimed_mileage =>p_claimed_mileage
1194 ,p_actual_mileage =>p_actual_mileage
1195 ,p_registration_number =>p_registration_number
1196 ,p_engine_capacity =>l_engine_capacity
1197 ,p_fuel_type =>p_fuel_type
1198 ,p_element_type_id =>l_element_type_id
1199 ,p_data_source =>p_data_source
1200 ,p_message =>l_message
1201 );
1202
1203
1204 IF l_chk_mndtry = 'N' THEN
1205 fnd_message.raise_error;
1206 hr_multi_message.end_validation_set;
1207 END IF;
1208
1209 --Input values are vary from Private to Company vehicles
1210 --So inserting the values besed on ownership
1211 IF p_ownership='C' THEN
1212 hr_utility.set_location(' Enter company mileage:' || l_proc,130);
1213 insert_company_mileage_claim
1214 ( p_effective_date =>l_effective_date
1215 ,p_assignment_id =>p_assignment_id
1216 ,p_business_group_id =>p_business_group_id
1217 ,p_ownership =>p_ownership
1218 ,p_usage_type =>p_usage_type
1219 ,p_vehicle_type =>p_vehicle_type
1220 ,p_start_date =>p_start_date
1221 ,p_end_date =>p_end_date
1222 ,p_claimed_mileage =>p_claimed_mileage
1223 ,p_actual_mileage =>p_actual_mileage
1224 ,p_registration_number =>p_registration_number
1225 ,p_engine_capacity =>l_engine_capacity
1226 ,p_fuel_type =>p_fuel_type
1227 ,p_calculation_method =>p_calculation_method
1228 ,p_user_rates_table =>l_rates_table_id --p_user_rates_table
1229 ,p_fiscal_ratings =>p_fiscal_ratings
1230 ,p_PAYE_taxable =>l_PAYE_taxable
1231 ,p_no_of_passengers =>p_no_of_passengers
1232 ,p_purpose =>p_purpose
1233 ,p_payroll_id =>l_get_pay_det
1234 ,p_mileage_claim_element =>l_element_type_id
1235 ,p_element_entry_id =>p_element_entry_id
1236 ,p_element_entry_date =>p_element_entry_date
1237 ,p_element_link_id =>l_element_link_id
1238 );
1239
1240 ELSIF p_ownership='P' THEN
1241 hr_utility.set_location(' Enter private mileage:' || l_proc,140);
1242 insert_private_mileage_claim
1243 ( p_effective_date =>l_effective_date
1244 ,p_assignment_id =>p_assignment_id
1245 ,p_business_group_id =>p_business_group_id
1246 ,p_ownership =>p_ownership
1247 ,p_usage_type =>p_usage_type
1248 ,p_vehicle_type =>p_vehicle_type
1249 ,p_start_date =>p_start_date
1250 ,p_end_date =>p_end_date
1251 ,p_claimed_mileage =>p_claimed_mileage
1252 ,p_actual_mileage =>p_actual_mileage
1253 ,p_registration_number =>p_registration_number
1254 ,p_engine_capacity =>l_engine_capacity
1255 ,p_fuel_type =>p_fuel_type
1256 ,p_calculation_method =>p_calculation_method
1257 ,p_user_rates_table =>l_rates_table_id --p_user_rates_table
1258 ,p_fiscal_ratings =>p_fiscal_ratings
1259 ,p_PAYE_taxable =>l_PAYE_taxable
1260 ,p_no_of_passengers =>p_no_of_passengers
1261 ,p_purpose =>p_purpose
1262 ,p_payroll_id =>l_get_pay_det
1263 ,p_mileage_claim_element =>l_element_type_id
1264 ,p_element_entry_id =>p_element_entry_id
1265 ,p_element_entry_date =>p_element_entry_date
1266 ,p_element_link_id =>l_element_link_id
1267 );
1268 END IF;
1269 --This call is for webADI related stuff
1270 ELSIF p_info_id IS NOT NULL THEN
1271 hr_utility.set_location(' Enter WEBADI:' || l_proc,150);
1272 l_assignment_id := substr(p_info_id,1,instr(p_info_id,l_string)-1);
1273 -- l_businesss_group_id:=substr(p_info_id,instr(p_info_id,l_string)+LENGTH(l_string),
1274 -- instr(p_info_id,l_string,1,2)-instr(p_info_id,l_string)-LENGTH(l_string));
1275 -- l_session_id :=substr(p_info_id,instr(p_info_id,l_string,1,2)+LENGTH(l_string)) ;
1276 /*This is changed now as the url contains -1,-1 for both values below
1277 now these values are derived from the profile value*/
1278 l_businesss_group_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
1279 l_session_id := fnd_profile.value('USER_ID');
1280
1281
1282 l_st_dt :=FND_DATE.CHARDATE_TO_DATE(p_start_date);
1283 l_ed_dt :=FND_DATE.CHARDATE_TO_DATE(p_end_date);
1284 l_canonical_st_dt :=fnd_date.date_to_canonical(l_st_dt);
1285 l_canonical_ed_dt :=fnd_date.date_to_canonical(l_ed_dt);
1286 -- l_canonical_st_dt :=fnd_date.date_to_displaydt(p_start_date);
1287 -- l_canonical_ed_dt :=fnd_date.date_to_displaydt(p_end_date);
1288
1289 INSERT INTO pay_us_rpt_totals
1290 (gre_name , -- Stores session id
1291 state_name, -- Stores timestamp
1292 state_abbrev, -- Stores info that tells
1293 --the date is for Web ADI. A string ADI
1294 attribute1, -- Stores Reg #
1295 attribute2, -- Stores Claimed Mileage
1296 attribute3, -- Stores Vehicle Type
1297 attribute4, -- Stores start Date
1298 attribute5, -- Stores end date
1299 attribute6, -- Stores actual mileage
1300 attribute7, -- Stores usage type
1301 attribute8 , -- Stores ownership
1302 bUsiness_group_id ,
1303 location_id --Stores assignmentId
1304 )
1305 VALUES
1306 (
1307 l_session_id ,
1308 p_time_stamp ,
1309 'ADI' ,
1310 p_registration_number ,
1311 p_claimed_mileage ,
1312 p_vehicle_type ,
1313 l_canonical_st_dt ,
1314 l_canonical_ed_dt ,
1315 p_actual_mileage ,
1316 p_usage_type ,
1317 p_ownership ,
1318 l_businesss_group_id ,
1319 l_assignment_id
1320 );
1321 END IF;
1322 END;
1323
1324
1325 -------------------------------------------------------------------------
1326 ------------ --Procedure for deleteting the claim import ----------------
1327 --------------------------------------------------------------------------
1328 --Used to delete the imported claims from UI
1329 PROCEDURE delete_claim_import
1330 ( p_info_id IN VARCHAR2
1331 ,p_assignment_id IN NUMBER
1332 ,p_business_group_id IN NUMBER
1333 ,p_effective_date IN DATE
1334 ,p_return_status OUT NOCOPY VARCHAR2
1335 ) AS
1336 l_proc varchar2(72) := g_package ||'delete_claim_import';
1337 BEGIN
1338 hr_utility.set_location(' Enter delete claim import:' || l_proc,160);
1339 DELETE
1340 FROM pay_us_rpt_totals
1341 WHERE GRE_NAME = p_info_id
1342 AND business_group_id = p_business_group_id
1343 AND location_id = p_assignment_id
1344 AND STATE_ABBREV ='ADI';
1345 --Deleting all rows which are older than a day
1346 DELETE
1347 FROM pay_us_rpt_totals
1348 WHERE STATE_ABBREV ='ADI'
1349 AND (p_effective_date-fnd_date.canonical_to_date(state_name))>1;
1350
1351 COMMIT;
1352 p_return_status := 'S';
1353 hr_utility.set_location('Leaving delete mileage:' || l_proc,170);
1354 exception
1355 when others then
1356 p_return_status := 'E';
1357 END;
1358
1359 --------------------------------------------------------------------------
1360 --Call for updating the mileage claim
1361 PROCEDURE update_mileage_claim
1362 (
1363 p_effective_date IN DATE,
1364 p_assignment_id IN NUMBER,
1365 p_business_group_id IN NUMBER,
1366 p_ownership IN VARCHAR2 ,
1367 p_usage_type IN VARCHAR2 ,
1368 p_vehicle_type IN VARCHAR2,
1369 p_start_date_o IN VARCHAR2 ,
1370 p_start_date IN VARCHAR2 ,
1371 p_end_date_o IN VARCHAR2 ,
1372 p_end_date IN VARCHAR2 ,
1373 p_claimed_mileage_o IN VARCHAR2 ,
1374 p_claimed_mileage IN VARCHAR2 ,
1375 p_actual_mileage_o IN VARCHAR2 ,
1376 p_actual_mileage IN VARCHAR2 ,
1377 p_registration_number IN VARCHAR2 ,
1378 p_engine_capacity IN VARCHAR2 ,
1379 p_fuel_type IN VARCHAR2 ,
1380 p_calculation_method IN VARCHAR2 ,
1381 p_user_rates_table IN VARCHAR2 ,
1382 p_fiscal_ratings_o IN VARCHAR2 ,
1383 p_fiscal_ratings IN VARCHAR2 ,
1384 p_PAYE_taxable IN VARCHAR2 ,
1385 p_no_of_passengers_o IN VARCHAR2 ,
1386 p_no_of_passengers IN VARCHAR2 ,
1387 p_purpose IN VARCHAR2 ,
1388 p_data_source IN VARCHAR2 ,
1389 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
1390 p_element_entry_id IN OUT NOCOPY NUMBER ,
1391 p_element_entry_date IN OUT NOCOPY DATE
1392 ) IS
1393
1394 CURSOR c_get_input_value
1395 IS
1396 SELECT DISTINCT piv.input_value_id
1397 ,piv.name
1398 ,piv.lookup_type
1399 ,piv.default_value
1400 FROM pay_input_values_f piv
1401 WHERE piv.name IN ('Claimed Mileage'
1402 ,'Actual Mileage'
1403 ,'Claim Start Date'
1404 ,'Claim End Date'
1405 ,'No of Passengers'
1406 ,'CO2 Emissions'
1407 ,'User Rates Table'
1408 ,'Vehicle Type'
1409 ,'Rate Type'
1410 ,'PAYE Taxable'
1411 ,'Calculation Method'
1412 ,'Purpose'
1413 )
1414 AND piv.element_type_id=p_mileage_claim_element
1415 AND piv.business_group_id=p_business_group_id
1416 AND p_effective_date BETWEEN piv.effective_start_date
1417 AND piv.effective_end_date;
1418
1419 CURSOR c_get_table_name ( cp_user_rates_table VARCHAR2
1420 ,cp_business_group_id NUMBER
1421 )
1422 IS
1423 SELECT put.user_table_name
1424 FROM pay_user_tables put
1425 WHERE user_table_id =cp_user_rates_table
1426 AND put.business_group_id=cp_business_group_id;
1427
1428
1429 CURSOR c_get_end_date
1430 IS
1431 SELECT MAX(pee.effective_end_date) effective_end_date
1432 FROM pay_element_entries_f pee
1433 WHERE pee.element_entry_id=p_element_entry_id
1434 AND pee.assignment_id=p_assignment_id;
1435
1436 l_get_table_name c_get_table_name%ROWTYPE;
1437 l_delete_mode VARCHAR2(30) :='FUTURE_CHANGE';
1438 l_update_mode VARCHAR2(30) :='CORRECTION';
1439 l_input_value_id_tbl hr_entry.number_table;
1440 l_entry_value_tbl hr_entry.varchar2_table;
1441 l_num_entry_values NUMBER;
1442 l_get_input_value c_get_input_value%ROWTYPE;
1443 l_get_end_date c_get_end_date%ROWTYPE;
1444 l_proc varchar2(72) := g_package ||'update_mileage_claim';
1445 BEGIN
1446
1447 hr_utility.set_location('Enter update Claim:' || l_proc,10);
1448 OPEN c_get_end_date;
1449 LOOP
1450 FETCH c_get_end_date INTO l_get_end_date;
1451 EXIT WHEN c_get_end_date%NOTFOUND;
1452
1453 END LOOP;
1454 CLOSE c_get_end_date;
1455 hr_utility.set_location(l_proc,20);
1456 OPEN c_get_input_value;
1457 LOOP
1458 FETCH c_get_input_value INTO l_get_input_value;
1459 EXIT WHEN c_get_input_value%NOTFOUND;
1460 hr_utility.set_location('Enter set input values:' || l_proc,30);
1461 IF l_get_input_value.name='Vehicle Type'OR
1462 l_get_input_value.name='Rate Type' THEN
1463 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1464 :=l_get_input_value.input_value_id;
1465 l_entry_value_tbl(l_entry_value_tbl.count+1)
1466 :=get_lkp_meaning(p_usage_type
1467 ,l_get_input_value.lookup_type);
1468 ELSIF l_get_input_value.name='Claimed Mileage' THEN
1469 hr_utility.set_location(l_proc,40);
1470 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1471 :=l_get_input_value.input_value_id;
1472 l_entry_value_tbl(l_entry_value_tbl.count+1)
1473 :=p_claimed_mileage;
1474 ELSIF l_get_input_value.name='Actual Mileage' THEN
1475 hr_utility.set_location(l_proc,50);
1476 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1477 :=l_get_input_value.input_value_id;
1478
1479 l_entry_value_tbl(l_entry_value_tbl.count+1)
1480 :=p_actual_mileage;
1481 ELSIF l_get_input_value.name='Claim Start Date' THEN
1482 hr_utility.set_location(l_proc,60);
1483 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1484 :=l_get_input_value.input_value_id;
1485
1486 l_entry_value_tbl(l_entry_value_tbl.count+1)
1487 :=p_start_date;
1488 ELSIF l_get_input_value.name='Claim End Date' THEN
1489 hr_utility.set_location(l_proc,70);
1490 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1491 :=l_get_input_value.input_value_id;
1492
1493 l_entry_value_tbl(l_entry_value_tbl.count+1)
1494 :=p_end_date;
1495 ELSIF l_get_input_value.name='User Rates Table' THEN
1496 hr_utility.set_location(l_proc,80);
1497 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1498 :=l_get_input_value.input_value_id;
1499
1500
1501
1502 OPEN c_get_table_name (p_user_rates_table
1503 ,p_business_group_id
1504 );
1505 FETCH c_get_table_name INTO l_get_table_name;
1506
1507 CLOSE c_get_table_name;
1508
1509 IF l_get_table_name.user_table_name IS NULL THEN
1510
1511 hr_utility.set_location(l_proc,90);
1512 l_entry_value_tbl(l_entry_value_tbl.count+1)
1513 :=l_get_input_value.default_value;
1514 ELSE
1515
1516 hr_utility.set_location(l_proc,100);
1517 l_entry_value_tbl(l_entry_value_tbl.count+1)
1518 :=l_get_table_name.user_table_name;
1519
1520 END IF;
1521 ELSIF l_get_input_value.name='No of Passengers' THEN
1522 hr_utility.set_location(l_proc,110);
1523 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1524 :=l_get_input_value.input_value_id;
1525
1526 l_entry_value_tbl(l_entry_value_tbl.count+1)
1527 :=p_no_of_passengers;
1528 ELSIF l_get_input_value.name='CO2 Emissions' THEN
1529 hr_utility.set_location(l_proc,120);
1530 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1531 :=l_get_input_value.input_value_id;
1532
1533 l_entry_value_tbl(l_entry_value_tbl.count+1)
1534 :=p_fiscal_ratings;
1535 ELSIF l_get_input_value.name='Calculation Method' THEN
1536 hr_utility.set_location(l_proc,130);
1537 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1538 :=l_get_input_value.input_value_id;
1539
1540 /*IF p_calculation_method IS NULL THEN
1541 l_entry_value_tbl(l_entry_value_tbl.count+1)
1542 :=p_calculation_method;
1543 ELSE
1544 l_entry_value_tbl(l_entry_value_tbl.count+1)
1545 :=get_lkp_meaning(p_calculation_method
1546 ,l_get_input_value.lookup_type);
1547
1548 END IF;*/
1549 IF l_get_input_value.lookup_type IS NOT NULL THEN
1550 l_entry_value_tbl(l_entry_value_tbl.count+1)
1551 :=get_lkp_meaning(NVL(p_calculation_method
1552 ,l_get_input_value.default_value)
1553 ,l_get_input_value.lookup_type);
1554 ELSE
1555 l_entry_value_tbl(l_entry_value_tbl.count+1)
1556 :=NVL(p_calculation_method,
1557 l_get_input_value.default_value);
1558 END IF;
1559 ELSIF l_get_input_value.name='PAYE Taxable' THEN
1560 hr_utility.set_location(l_proc,140);
1561 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1562 :=l_get_input_value.input_value_id;
1563
1564 IF l_get_input_value.lookup_type IS NOT NULL THEN
1565 l_entry_value_tbl(l_entry_value_tbl.count+1)
1566 :=get_lkp_meaning(p_PAYE_Taxable
1567 ,l_get_input_value.lookup_type);
1568 ELSE
1569 l_entry_value_tbl(l_entry_value_tbl.count+1):= p_PAYE_Taxable;
1570 END IF;
1571
1572 ELSIF l_get_input_value.name='Purpose' THEN
1573 hr_utility.set_location(l_proc,70);
1574 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1575 :=l_get_input_value.input_value_id;
1576
1577 l_entry_value_tbl(l_entry_value_tbl.count+1)
1578 :=p_purpose;
1579
1580 END IF;
1581
1582 l_num_entry_values :=l_input_value_id_tbl.count;
1583
1584 END LOOP;
1585 CLOSE c_get_input_value;
1586
1587 IF l_get_end_date.effective_end_date <> hr_api.g_eot
1588 THEN
1589 hr_utility.set_location(l_proc,150);
1590 IF p_claimed_mileage_o <> p_claimed_mileage OR
1591 p_actual_mileage_o <> p_actual_mileage THEN
1592 hr_utility.set_location(l_proc,160);
1593 hr_utility.set_location('Enter delete api:' || l_proc,200);
1594 hr_entry_api.delete_element_entry
1595 (
1596 p_dt_delete_mode =>l_delete_mode,
1597 p_session_date =>p_effective_date,
1598 p_element_entry_id =>p_element_entry_id
1599 );
1600 END IF;
1601
1602 END IF;
1603
1604 hr_utility.set_location(l_proc,170);
1605 hr_utility.set_location('Enter correction:' || l_proc,210);
1606 hr_entry_api.update_element_entry
1607 (
1608 p_dt_update_mode =>l_update_mode,
1609 p_session_date =>p_effective_date,
1610 p_element_entry_id =>p_element_entry_id,
1611 p_num_entry_values =>l_num_entry_values,
1612 p_input_value_id_tbl =>l_input_value_id_tbl,
1613 p_entry_value_tbl =>l_entry_value_tbl
1614 );
1615
1616 exception
1617 --------
1618 When others then
1619 hr_utility.set_location(l_proc,180);
1620 fnd_message.raise_error;
1621 hr_multi_message.end_validation_set;
1622
1623 END;
1624
1625 --Call for deleting the mileage claim
1626 PROCEDURE delete_mileage_claim
1627 ( p_effective_date IN DATE,
1628 p_assignment_id IN NUMBER,
1629 p_mileage_claim_element IN NUMBER ,
1630 p_element_entry_id IN NUMBER ,
1631 p_element_entry_date IN DATE
1632 ) IS
1633
1634 CURSOR c_get_end_date(cp_element_entry_id NUMBER
1635 ,cp_assignment_id NUMBER)
1636 IS
1637 SELECT MAX(pee.effective_end_date) effective_end_date
1638 FROM pay_element_entries_f pee
1639 WHERE pee.element_entry_id=cp_element_entry_id
1640 AND pee.assignment_id =cp_assignment_id;
1641
1642 CURSOR c_get_process_status (cp_assignment_id NUMBER
1643 ,cp_element_type_id NUMBER
1644 ,cp_element_entry_id NUMBER
1645 )
1646 IS
1647 SELECT prr.assignment_action_id
1648 FROM pay_run_results prr
1649 WHERE
1650 --prr.assignment_id =cp_assignment_id
1651 --AND
1652 prr.element_type_id=cp_element_type_id
1653 AND prr.source_id=cp_element_entry_id;
1654
1655 l_get_end_date c_get_end_date%ROWTYPE;
1656 l_get_process_status c_get_process_status%ROWTYPE;
1657
1658 l_proc varchar2(72) := g_package ||'delete_mileage_claim';
1659 BEGIN
1660
1661 hr_utility.set_location(l_proc,10);
1662 OPEN c_get_end_date (p_element_entry_id
1663 ,p_assignment_id
1664 );
1665 LOOP
1666 FETCH c_get_end_date INTO l_get_end_date;
1667 EXIT WHEN c_get_end_date%NOTFOUND;
1668 hr_utility.set_location(l_proc,20);
1669 IF l_get_end_date.effective_end_date =hr_api.g_eot THEN
1670
1671 OPEN c_get_process_status ( p_assignment_id
1672 ,p_mileage_claim_element
1673 ,p_element_entry_id
1674 );
1675 LOOP
1676 FETCH c_get_process_status INTO l_get_process_status;
1677 EXIT WHEN c_get_process_status%NOTFOUND;
1678 END LOOP;
1679 CLOSE c_get_process_status;
1680
1681 IF l_get_process_status.assignment_action_id IS NULL THEN
1682
1683 hr_utility.set_location(l_proc,30);
1684 hr_entry_api.delete_element_entry
1685 (
1686 p_dt_delete_mode =>'ZAP'
1687 ,p_session_date =>p_effective_date
1688 ,p_element_entry_id =>p_element_entry_id
1689 );
1690 ELSE
1691 hr_utility.set_location(l_proc,40);
1692 fnd_message.set_name('PQP','PQP_230718_CLM_PROC_DEL');
1693 fnd_message.raise_error;
1694 hr_multi_message.end_validation_set;
1695 END IF;
1696
1697 ELSE
1698
1699 hr_utility.set_location(l_proc,50);
1700 fnd_message.set_name('PQP','PQP_230718_CLM_PROC_DEL');
1701 fnd_message.raise_error;
1702 hr_multi_message.end_validation_set;
1703 END IF;
1704 END LOOP;
1705 exception
1706 --------
1707 When others then
1708 hr_utility.set_location(l_proc,60);
1709 fnd_message.raise_error;
1710 hr_multi_message.end_validation_set;
1711 END;
1712
1713 --get element or rates from configuration
1714 PROCEDURE get_config_info ( p_business_group_id IN NUMBER
1715 ,p_ownership IN VARCHAR2
1716 ,p_usage_type IN VARCHAR2
1717 ,p_vehicle_type IN VARCHAR2
1718 ,p_fuel_type IN VARCHAR2
1719 ,p_sl_rates_type IN VARCHAR2
1720 ,p_rates OUT NOCOPY NUMBER
1721 ,p_element_id IN OUT NOCOPY NUMBER
1722 )
1723 IS
1724 CURSOR c_get_config_rates_info
1725 IS
1726 SELECT pcv.pcv_information_category
1727 ,pcv.pcv_information1 Ownership
1728 ,pcv.pcv_information2 Usage_type
1729 ,pcv.pcv_information3 Vehicle_type
1730 ,pcv.pcv_information4 Fuel_type
1731 ,pcv.pcv_information5 rates_type
1732 FROM pqp_configuration_values pcv
1733 WHERE business_group_id=p_business_group_id
1734 AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
1735 AND pcv_information1=p_ownership
1736 AND (pcv_information2=p_usage_type
1737 OR pcv_information2 IS NULL)
1738 AND (pcv_information3=p_vehicle_type
1739 OR pcv_information3 IS NULL)
1740 AND (pcv_information4=p_fuel_type
1741 OR pcv_information4 IS NULL)
1742 AND (pcv_information6=p_element_id
1743 OR pcv_information6 IS NULL)
1744 AND pcv.pcv_information5 IS NOT NULL
1745 ORDER BY 1,2,3,4;
1746
1747 CURSOR c_get_config_element_info
1748 IS
1749 SELECT pcv.pcv_information_category
1750 ,pcv.pcv_information1 Ownership
1751 ,pcv.pcv_information2 Usage_type
1752 ,pcv.pcv_information3 Vehicle_type
1753 ,pcv.pcv_information4 Fuel_type
1754 ,pcv.pcv_information6 element_id
1755 FROM pqp_configuration_values pcv
1756 WHERE business_group_id=p_business_group_id
1757 AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
1758 AND pcv_information1=p_ownership
1759 AND (pcv_information2=p_usage_type
1760 OR pcv_information2 IS NULL)
1761 AND (pcv_information3=p_vehicle_type
1762 OR pcv_information3 IS NULL)
1763 AND (pcv_information4=p_fuel_type
1764 OR pcv_information4 IS NULL)
1765 AND pcv.pcv_information6 IS NOT NULL
1766 ORDER BY 1,2,3,4;
1767
1768 CURSOR c_ele_type (cp_element_type_id NUMBER)
1769 IS
1770 SELECT NVL(pete.eei_information2 ,'N') ele_type
1771 FROM pay_element_type_extra_info pete
1772 WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
1773 AND pete.element_type_id= cp_element_type_id;
1774
1775 l_ele_type c_ele_type%ROWTYPE;
1776 l_get_config_rates_info c_get_config_rates_info%ROWTYPE;
1777 l_get_config_element_info c_get_config_element_info%ROWTYPE;
1778 wrong_table EXCEPTION;
1779 BEGIN
1780
1781 OPEN c_get_config_rates_info;
1782 FETCH c_get_config_rates_info INTO l_get_config_rates_info;
1783 CLOSE c_get_config_rates_info;
1784
1785 IF p_sl_rates_type='Y' THEN
1786 BEGIN
1787 p_rates :=to_number(l_get_config_rates_info.rates_type) ;
1788 RAISE wrong_table;
1789
1790 EXCEPTION
1791 ---------
1792 WHEN wrong_table THEN
1793 p_rates:=NULL;
1794 WHEN OTHERS THEN
1795 p_rates :=l_get_config_rates_info.rates_type;
1796 END;
1797 ELSE
1798 BEGIN
1799 p_rates :=to_number(l_get_config_rates_info.rates_type) ;
1800
1801 EXCEPTION
1802 ---------
1803 WHEN OTHERS THEN
1804 p_rates :=NULL;
1805 END;
1806 END IF;
1807
1808 OPEN c_get_config_element_info;
1809 FETCH c_get_config_element_info INTO l_get_config_element_info;
1810
1811 OPEN c_ele_type (l_get_config_element_info.element_id);
1812 FETCH c_ele_type INTO l_ele_type;
1813 CLOSE c_ele_type;
1814 IF p_sl_rates_type=l_ele_type.ele_type THEN
1815 p_element_id :=l_get_config_element_info.element_id;
1816 ELSE
1817 p_element_id :=NULL;
1818 END IF;
1819 CLOSE c_get_config_element_info;
1820
1821 END;
1822 --chk if vehicle is active during the claim period
1823 FUNCTION chk_vehicle_active ( p_ownership IN VARCHAR2
1824 ,p_usage_type IN VARCHAR2
1825 ,p_assignment_id IN NUMBER
1826 ,p_business_group_id IN NUMBER
1827 ,p_start_date IN VARCHAR2
1828 ,p_end_date IN VARCHAR2
1829 ,p_registration_number IN VARCHAR2
1830 ,p_message OUT NOCOPY VARCHAR2
1831 )
1832 RETURN NUMBER
1833 IS
1834
1835 CURSOR c_get_reg_num
1836 IS
1837 SELECT pvr.registration_number
1838 ,pva.default_vehicle
1839 FROM pqp_vehicle_repository_f pvr
1840 ,pqp_vehicle_allocations_f pva
1841 WHERE pva.vehicle_repository_id=pvr.vehicle_repository_id
1842 AND pvr.vehicle_ownership=p_ownership
1843 AND pva.usage_type=p_usage_type
1844 AND pva.assignment_id=p_assignment_id
1845 AND pva.business_group_id=p_business_group_id
1846 AND pva.business_group_id=pvr.business_group_id
1847 AND p_start_date BETWEEN pva.effective_start_date
1848 AND pva.effective_end_date
1849 AND p_start_date BETWEEN pvr.effective_start_date
1850 AND pvr.effective_end_date ;
1851
1852 CURSOR c_chk_active (cp_registration_number VARCHAR2)
1853 IS
1854 SELECT pvr.vehicle_status,'Start_Date' clm_date
1855 FROM pqp_vehicle_repository_f pvr
1856 WHERE pvr.registration_number=cp_registration_number
1857 AND pvr.vehicle_status='I'
1858 AND p_start_date BETWEEN pvr.effective_start_date
1859 AND pvr.effective_end_date
1860 UNION
1861 SELECT pvr.vehicle_status,'End_Date' clm_date
1862 FROM pqp_vehicle_repository_f pvr
1863 WHERE pvr.registration_number=cp_registration_number
1864 AND pvr.vehicle_status='I'
1865 AND p_end_date BETWEEN pvr.effective_start_date
1866 AND pvr.effective_end_date;
1867
1868 CURSOR c_chk_alloc (cp_registration_number VARCHAR2)
1869 IS
1870 SELECT 'Start_Valid' valid_date
1871 FROM pqp_vehicle_allocations_f pva
1872 ,per_all_assignments_f paa
1873 ,pqp_vehicle_repository_f pvr
1874 WHERE pva.assignment_id=p_assignment_id
1875 AND pvr.registration_number=cp_registration_number
1876 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1877 AND pva.business_group_id=p_business_group_id
1878 AND pva.assignment_id =paa.assignment_id
1879 AND pva.business_group_id=paa.business_group_id
1880 AND pva.business_group_id=pvr.business_group_id
1881 AND p_start_date BETWEEN pva.effective_start_date
1882 AND pva.effective_end_date
1883 AND p_start_date BETWEEN paa.effective_start_date
1884 AND paa.effective_end_date
1885 AND p_start_date BETWEEN pvr.effective_start_date
1886 AND pvr.effective_end_date
1887 UNION
1888 SELECT 'End_Valid' valid_date
1889 FROM pqp_vehicle_allocations_f pva
1890 ,per_all_assignments_f paa
1891 ,pqp_vehicle_repository_f pvr
1892 WHERE pva.assignment_id=p_assignment_id
1893 AND pvr.registration_number=cp_registration_number
1894 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1895 AND pva.business_group_id=p_business_group_id
1896 AND pva.assignment_id =paa.assignment_id
1897 AND pva.business_group_id=paa.business_group_id
1898 AND pva.business_group_id=pvr.business_group_id
1899 AND p_end_date BETWEEN pva.effective_start_date
1900 AND pva.effective_end_date
1901 AND p_end_date BETWEEN paa.effective_start_date
1902 AND paa.effective_end_date
1903 AND p_end_date BETWEEN pvr.effective_start_date
1904 AND pvr.effective_end_date;
1905
1906
1907
1908 l_get_reg_num c_get_reg_num%ROWTYPE;
1909 l_chk_alloc c_chk_alloc%ROWTYPE;
1910 l_chk_active c_chk_active%ROWTYPE;
1911 l_st_date VARCHAR2(10);
1912 l_end_date VARCHAR2(10);
1913 BEGIN
1914 IF p_registration_number IS NULL THEN
1915 OPEN c_get_reg_num;
1916 LOOP
1917 FETCH c_get_reg_num INTO l_get_reg_num;
1918 EXIT WHEN c_get_reg_num%NOTFOUND;
1919 END LOOP;
1920 CLOSE c_get_reg_num;
1921
1922 END IF;
1923 OPEN c_chk_active (NVL(p_registration_number,
1924 l_get_reg_num.registration_number));
1925 LOOP
1926 FETCH c_chk_active INTO l_chk_active;
1927 EXIT WHEN c_chk_active%NOTFOUND;
1928 IF l_chk_active.clm_date='Start_Date' THEN
1929 l_st_date:=l_chk_active.clm_date;
1930 ELSIF l_chk_active.clm_date='End_Date' THEN
1931
1932 l_end_date:=l_chk_active.clm_date;
1933
1934 END IF;
1935
1936 END LOOP;
1937 CLOSE c_chk_active;
1938 IF l_st_date IS NOT NULL AND
1939 l_end_date IS NOT NULL THEN
1940
1941 fnd_message.raise_error;
1942
1943 ELSIF l_st_date IS NOT NULL AND
1944 l_end_date IS NULL THEN
1945
1946 fnd_message.raise_error;
1947 ELSIF l_st_date IS NULL AND
1948 l_end_date IS NOT NULL THEN
1949
1950 fnd_message.raise_error;
1951
1952 END IF;
1953
1954 END;
1955
1956 --Check for Mandatory columns
1957 FUNCTION chk_mndtry_fields ( p_effective_date IN DATE
1958 ,p_assignment_id IN NUMBER
1959 ,p_business_group_id IN NUMBER
1960 ,p_ownership IN VARCHAR2
1961 ,p_usage_type IN VARCHAR2
1962 ,p_vehicle_type IN VARCHAR2
1963 ,p_start_date IN VARCHAR2
1964 ,p_end_date IN VARCHAR2
1965 ,p_claimed_mileage IN VARCHAR2
1966 ,p_actual_mileage IN VARCHAR2
1967 ,p_registration_number IN VARCHAR2
1968 ,p_engine_capacity IN VARCHAR2
1969 ,p_fuel_type IN VARCHAR2
1970 ,p_element_type_id IN NUMBER
1971 ,p_data_source IN VARCHAR2
1972 ,p_message OUT NOCOPY VARCHAR2
1973 )
1974
1975 RETURN VARCHAR2
1976 AS
1977 CURSOR c_get_veh_det
1978 IS
1979 SELECT pvr.vehicle_type
1980 ,pvr.vehicle_ownership
1981 ,pvr.fiscal_ratings
1982 ,pvr.engine_capacity_in_cc
1983 ,pvr.fuel_type
1984 FROM pqp_vehicle_repository_f pvr
1985 WHERE pvr.registration_number = p_registration_number
1986 AND pvr.business_group_id = p_business_group_id
1987 AND p_effective_date BETWEEN pvr.effective_start_date
1988 AND pvr.effective_end_date;
1989
1990 CURSOR c_validate_comp_veh
1991 IS
1992 SELECT pvr.vehicle_type
1993 ,pvr.vehicle_ownership
1994 ,pvr.fiscal_ratings
1995 ,pvr.engine_capacity_in_cc
1996 ,pvr.fuel_type
1997 FROM pqp_vehicle_repository_f pvr
1998 ,pqp_vehicle_allocations_f pva
1999 WHERE (pvr.registration_number =p_registration_number
2000 OR p_registration_number IS NULL)
2001 AND pva.assignment_id= p_assignment_id
2002 AND pvr.business_group_id = p_business_group_id
2003 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
2004 AND pvr.business_group_id=pva.business_group_id
2005 AND FND_DATE.CHARDATE_TO_DATE(p_start_date) BETWEEN pvr.effective_start_date
2006 AND pvr.effective_end_date
2007 AND pva.usage_type IN ('P','S');
2008
2009 l_validate_comp_veh c_validate_comp_veh%ROWTYPE;
2010 l_get_veh_det c_get_veh_det%ROWTYPE;
2011 l_retvalue VARCHAR2(1) :='Y';
2012 l_proc varchar2(72) := g_package ||'chk_mndtry_fields';
2013 BEGIN
2014
2015 hr_utility.set_location('Enter ' || l_proc,10);
2016 IF p_registration_number IS NOT NULL THEN
2017 OPEN c_get_veh_det;
2018 FETCH c_get_veh_det INTO l_get_veh_det;
2019 IF c_get_veh_det%FOUND THEN
2020 hr_utility.set_location( l_proc,20);
2021 IF l_get_veh_det.vehicle_type<>p_vehicle_type THEN
2022 hr_utility.set_location( l_proc,30);
2023 fnd_message.set_name('PQP', 'PQP_230859_VEHICLE_TYP_VALIDAT');
2024 l_retvalue :='N';
2025 END IF;
2026 IF l_get_veh_det.engine_capacity_in_cc
2027 <>p_engine_capacity THEN
2028 hr_utility.set_location( l_proc,40);
2029 fnd_message.set_name('PQP', 'PQP_230860_ENGINE_CAP_VALIDAT');
2030 l_retvalue :='N';
2031
2032 END IF;
2033 IF l_get_veh_det.fuel_type <>p_fuel_type THEN
2034 hr_utility.set_location( l_proc,50);
2035 fnd_message.set_name('PQP', 'PQP_230861_FUEL_TYP_VALIDAT');
2036 l_retvalue :='N';
2037 END IF;
2038
2039
2040 END IF;
2041 CLOSE c_get_veh_det;
2042 END IF;
2043 --Check if the an assignemnt has company vehicle
2044 --allocated
2045 IF p_ownership='C' THEN
2046
2047 hr_utility.set_location( l_proc,60);
2048 OPEN c_validate_comp_veh;
2049 FETCH c_validate_comp_veh INTO l_validate_comp_veh;
2050 IF c_validate_comp_veh%NOTFOUND THEN
2051
2052 hr_utility.set_location( l_proc,70);
2053 fnd_message.set_name('PQP', 'PQP_230866_COMP_VEH_NOT_ALLOC');
2054 l_retvalue :='N';
2055 END IF;
2056
2057 CLOSE c_validate_comp_veh;
2058
2059 END IF;
2060 IF p_ownership IS NULL THEN
2061 hr_utility.set_location( l_proc,80);
2062 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2063 fnd_message.set_token('TOKEN','Ownership');
2064 l_retvalue :='N';
2065 END IF;
2066
2067 --Commented out this now because this condition is no longer
2068 --required as the code handles this during element entry.
2069 /*IF p_usage_type IS NULL THEN
2070 hr_utility.set_location( l_proc,90);
2071 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2072 fnd_message.set_token('TOKEN','Usage Type');
2073 l_retvalue :='N';
2074
2075 END IF;*/
2076
2077 IF p_vehicle_type IS NULL THEN
2078 hr_utility.set_location( l_proc,100);
2079 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2080 fnd_message.set_token('TOKEN','Vehicle Type');
2081 l_retvalue :='N';
2082
2083 END IF;
2084
2085
2086 IF p_start_date IS NULL THEN
2087 hr_utility.set_location( l_proc,110);
2088 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2089 fnd_message.set_token('TOKEN','Start Date');
2090 l_retvalue :='N';
2091
2092 END IF;
2093
2094 IF p_end_date IS NULL THEN
2095 hr_utility.set_location( l_proc,120);
2096 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2097 fnd_message.set_token('TOKEN','End Date');
2098
2099 l_retvalue :='N';
2100 END IF;
2101 IF p_claimed_mileage IS NULL THEN
2102 hr_utility.set_location( l_proc,130);
2103 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2104 fnd_message.set_token('TOKEN','Claimed Mileage');
2105 l_retvalue :='N';
2106
2107 END IF;
2108
2109
2110 IF p_engine_capacity IS NULL THEN
2111 hr_utility.set_location( l_proc,140);
2112 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2113 fnd_message.set_token('TOKEN','Engine Capacity');
2114 l_retvalue :='N';
2115 END IF;
2116
2117 IF p_element_type_id IS NULL THEN
2118 hr_utility.set_location( l_proc,150);
2119
2120 fnd_message.set_name('PQP', 'PQP_230732_VLD_MLG_ELE_FAIL');
2121 l_retvalue :='N';
2122 END IF;
2123
2124 IF FND_DATE.CHARDT_TO_DATE(p_end_date) <
2125 FND_DATE.CHARDT_TO_DATE(p_start_date) THEN
2126 hr_utility.set_location( l_proc,160);
2127 fnd_message.set_name('PER','HR_289262_ST_DATE_BEFORE_EDATE');
2128 l_retvalue :='N';
2129 END IF;
2130
2131 --RETURN('Y');
2132 RETURN (l_retvalue);
2133 hr_utility.set_location( 'Leaving :'||l_proc,170);
2134 END;
2135
2136 --Check for eligibility
2137 FUNCTION chk_eligibility ( p_effective_date IN DATE
2138 ,p_assignment_id IN NUMBER
2139 ,p_business_group_id IN NUMBER
2140 ,p_ownership IN VARCHAR2
2141 ,p_usage_type IN VARCHAR2
2142 ,p_vehicle_type IN VARCHAR2
2143 ,p_start_date IN VARCHAR2
2144 ,p_end_date IN VARCHAR2
2145 ,p_claimed_mileage IN VARCHAR2
2146 ,p_actual_mileage IN VARCHAR2
2147 ,p_registration_number IN VARCHAR2
2148 ,p_data_source IN VARCHAR2
2149 ,p_message OUT NOCOPY VARCHAR2
2150 )
2151
2152 RETURN VARCHAR2
2153 IS
2154
2155 --get info from config table to chk
2156 --for eligibility.
2157 l_retvalue VARCHAR2(1) :='Y';
2158 CURSOR c_get_config_info (cp_leg_code VARCHAR2)
2159 IS
2160 SELECT pcv_information6 prev_tax_yr_valid
2161 ,pcv_information7 allow_both_veh_clm
2162 ,pcv_information9 validate_pvt_veh
2163 FROM pqp_configuration_values pcv
2164 WHERE pcv.legislation_code=cp_leg_code
2165 AND pcv.pcv_information_category='PQP_VEHICLE_MILEAGE';
2166
2167 --Check the status of vehicle
2168 CURSOR c_validate_veh ( cp_reg_num VARCHAR2
2169 ,cp_ownership VARCHAR2
2170 ,cp_business_group_id VARCHAR2
2171 ,cp_start_date VARCHAR2
2172 )
2173 IS
2174 SELECT 'X' exst
2175 FROM pqp_vehicle_repository_f pvr
2176 WHERE pvr.registration_number=cp_reg_num
2177 AND pvr.business_group_id =cp_business_group_id
2178 AND TO_DATE(cp_start_date,'YYYY/MM/DD') BETWEEN
2179 pvr.effective_start_date AND
2180 pvr.effective_end_date;
2181
2182 CURSOR c_get_alloc_info ( cp_assignment_id NUMBER
2183 ,cp_ownership VARCHAR2
2184 ,cp_business_group_id VARCHAR2
2185 ,cp_start_date VARCHAR2
2186 )
2187 IS
2188 SELECT DISTINCT pvr.vehicle_ownership ownership
2189 FROM pqp_vehicle_allocations_f pva,
2190 pqp_vehicle_repository_f pvr
2191 WHERE pva.assignment_id =cp_assignment_id
2192 AND pva.business_group_id =cp_business_group_id
2193 AND pva.vehicle_repository_id=pvr.vehicle_repository_id
2194 AND pva.business_group_id=pvr.business_group_id
2195 AND fnd_date.CHARDATE_TO_DATE(cp_start_date) BETWEEN
2196 pva.effective_start_date AND
2197 pva.effective_end_date
2198 AND fnd_date.CHARDATE_TO_DATE(cp_start_date) BETWEEN
2199 pvr.effective_start_date AND
2200 pvr.effective_end_date
2201 AND pvr.vehicle_ownership='C';
2202
2203 l_get_alloc_info c_get_alloc_info%ROWTYPE;
2204 l_validate_veh c_validate_veh%ROWTYPE;
2205 l_get_config_info c_get_config_info%ROWTYPE;
2206
2207 l_proc varchar2(72) := g_package ||'check_eligibility';
2208 BEGIN
2209 hr_utility.set_location('Enter chk eligibility:' || l_proc,220);
2210 OPEN c_get_config_info('GB'
2211 );
2212 LOOP
2213 FETCH c_get_config_info INTO l_get_config_info;
2214 EXIT WHEN c_get_config_info%NOTFOUND;
2215
2216 END LOOP;
2217 CLOSE c_get_config_info;
2218
2219 --Check if the claim is made for last tax
2220 --year after the cut off date.
2221 IF l_get_config_info.prev_tax_yr_valid IS NOT NULL THEN
2222 IF fnd_date.CHARDATE_TO_DATE(p_start_date) <
2223 TO_DATE(TO_CHAR
2224 (p_effective_date, 'YYYY')||'04/06','YYYY/MM/DD')
2225 THEN
2226 --checking the effective date is greater than previous tax year sumit
2227 --valid untill date,so user cannot sumit claim after previous tax year
2228 IF p_effective_date > fnd_date.CHARDATE_TO_DATE
2229 (l_get_config_info.prev_tax_yr_valid||
2230 TO_CHAR(p_effective_date,'YYYY'))
2231 THEN
2232 --Error handling missing
2233 fnd_message.set_name('PQP', 'PQP_230715_CLM_CUT_OFF_DT');
2234 p_message :='Violated Valid Tax Year';
2235
2236 l_retvalue :='N';
2237 END IF;
2238 END IF;
2239 END IF;
2240
2241 hr_utility.set_location('leave chk eligibility:' || l_proc,230);
2242
2243 --Check if pvt vehicle need to be validated against
2244 --repository.
2245 IF l_get_config_info.validate_pvt_veh ='Y' THEN
2246
2247 OPEN c_validate_veh (p_registration_number
2248 ,p_ownership
2249 ,p_business_group_id
2250 ,p_start_date
2251 );
2252 LOOP
2253 FETCH c_validate_veh INTO l_validate_veh;
2254 EXIT WHEN c_validate_veh%NOTFOUND;
2255 END LOOP;
2256 CLOSE c_validate_veh;
2257
2258 IF l_validate_veh.exst IS NULL THEN
2259 fnd_message.set_name('PQP', 'PQP_230735_REGNUM_FRM_REP');
2260 p_message:='Enter Only Vehicle in Repository';
2261
2262 l_retvalue :='N';
2263 END IF;
2264 END IF;
2265
2266 --Check if the claim can be entered for both.
2267 IF l_get_config_info.allow_both_veh_clm ='N' THEN
2268 OPEN c_get_alloc_info ( p_assignment_id
2269 ,p_ownership
2270 ,p_business_group_id
2271 ,p_start_date
2272 );
2273 LOOP
2274 FETCH c_get_alloc_info INTO l_get_alloc_info;
2275 EXIT WHEN c_get_alloc_info%NOTFOUND;
2276
2277 END LOOP;
2278 CLOSE c_get_alloc_info;
2279 IF l_get_alloc_info.ownership<>p_ownership THEN
2280 fnd_message.set_name('PQP', 'PQP_230740_ONE_OWNRSHP_RSTRICT');
2281 p_message :='Enter Only one type of Claim';
2282 l_retvalue :='N';
2283 END IF;
2284 END IF;
2285 --RETURN('Y');
2286 RETURN( l_retvalue);
2287 END;
2288
2289
2290
2291 --Check for same record
2292 FUNCTION chk_record_exist ( p_effective_date IN DATE
2293 ,p_assignment_id IN NUMBER
2294 ,p_business_group_id IN NUMBER
2295 ,p_ownership IN VARCHAR2
2296 ,p_usage_type IN VARCHAR2
2297 ,p_vehicle_type IN VARCHAR2
2298 ,p_start_date IN VARCHAR2
2299 ,p_end_date IN VARCHAR2
2300 ,p_claimed_mileage IN VARCHAR2
2301 ,p_actual_mileage IN VARCHAR2
2302 ,p_registration_number IN VARCHAR2
2303 ,p_data_source IN VARCHAR2
2304 )
2305 RETURN VARCHAR2
2306 IS
2307
2308 CURSOR c_get_ele_details ( cp_vehicle_type VARCHAR2
2309 ,cp_business_group_id NUMBER
2310 )
2311
2312 IS
2313 SELECT pet.element_type_id
2314 ,pel.element_link_id
2315 FROM pay_element_types_f pet
2316 ,pay_element_type_extra_info pete
2317 ,pay_element_links_f pel
2318 WHERE pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
2319 AND pet.element_type_id=pete.element_type_id
2320 AND pete.element_type_id=pel.element_type_id
2321 AND pete.eei_information1=cp_vehicle_type
2322 AND pet.business_group_id=pel.business_group_id
2323 AND pet.business_group_id=cp_business_group_id
2324 AND pet.element_type_id=pel.element_type_id;
2325
2326
2327 CURSOR c_get_input_val (cp_element_type_id NUMBER
2328 ,cp_business_group_id NUMBER
2329 )
2330 IS
2331 SELECT piv.element_type_id
2332 ,piv.input_value_id
2333 ,piv.name
2334 FROM pay_input_values_f piv
2335 WHERE piv.name in ('Claim Start Date','Claim End Date')
2336 AND piv.element_type_id=cp_element_type_id
2337 and piv.business_group_id=cp_business_group_id;
2338
2339
2340
2341
2342 CURSOR c_get_date_exist ( cp_assignment_id NUMBER
2343 ,cp_start_date DATE
2344 ,cp_end_date DATE
2345 ,cp_ipvalue1 NUMBER
2346 ,cp_ipvalue2 NUMBER
2347 )
2348 IS
2349 SELECT pee.assignment_id
2350 ,pev1.screen_entry_value scr1
2351 ,pev2.screen_entry_value scr2
2352 ,pev1.element_entry_id
2353 FROM pay_element_entries_f pee
2354 ,pay_element_entry_values_f pev1
2355 ,pay_element_entry_values_f pev2
2356 WHERE pee.assignment_id=cp_assignment_id
2357 AND pee.element_entry_id=pev1.element_entry_id
2358 AND pee.element_entry_id=pev2.element_entry_id
2359 AND pev1.element_entry_id=pev2.element_entry_id
2360 AND pev1.input_value_id =cp_ipvalue1
2361 AND pev2.input_value_id=cp_ipvalue2
2362 AND pev1.screen_entry_value =
2363 fnd_date.DATE_TO_CANONICAL(cp_start_date)
2364 AND pev2.SCREEN_ENTRY_VALUE =
2365 fnd_date.DATE_TO_CANONICAL(cp_end_date);
2366
2367
2368 l_get_ele_details c_get_ele_details%ROWTYPE;
2369 l_get_input_val c_get_input_val%ROWTYPE;
2370 l_get_date_exist c_get_date_exist%ROWTYPE;
2371 l_input_val1 NUMBER;
2372 l_input_val2 NUMBER;
2373 l_exist VARCHAR2(1):='N';
2374 l_start_date DATE;
2375 l_end_date DATE;
2376 BEGIN
2377 l_start_date:= fnd_date.chardt_to_date (p_start_date);
2378 l_end_date :=fnd_date.chardt_to_date(p_end_date);
2379 OPEN c_get_ele_details( p_vehicle_type
2380 ,p_business_group_id
2381 );
2382 LOOP
2383 FETCH c_get_ele_details INTO l_get_ele_details;
2384 EXIT WHEN c_get_ele_details%NOTFOUND;
2385 OPEN c_get_input_val (l_get_ele_details.element_type_id
2386 ,p_business_group_id
2387 );
2388 LOOP
2389 FETCH c_get_input_val INTO l_get_input_val;
2390 EXIT WHEN c_get_input_val%NOTFOUND;
2391
2392 IF l_get_input_val.NAME='Claim Start Date' THEN
2393 l_input_val1:=l_get_input_val.input_value_id;
2394 ELSIF l_get_input_val.NAME='Claim End Date' THEN
2395 l_input_val1:=l_get_input_val.input_value_id;
2396 END IF;
2397 END LOOP;
2398 CLOSE c_get_input_val;
2399 OPEN c_get_date_exist ( p_assignment_id
2400 ,l_start_date
2401 ,l_end_date
2402 ,l_input_val1
2403 ,l_input_val2
2404 );
2405 LOOP
2406 FETCH c_get_date_exist INTO l_get_date_exist;
2407 EXIT WHEN c_get_date_exist%NOTFOUND;
2408
2409 l_exist :='Y';
2410 END LOOP;
2411 CLOSE c_get_date_exist;
2412 END LOOP;
2413 CLOSE c_get_ele_details;
2414
2415 RETURN(l_exist);
2416
2417 END;
2418
2419 ---Called from JDEV ----
2420 --
2421 -- Function get_code returns the code of the meaning passed
2422 --
2423 -- The Code depends on the value of the p_option parameter
2424 -- p_option = 'R' -> p_field has the rates table name
2425 --and it Returns the Rates table id
2426 --
2427 FUNCTION get_code
2428 (p_option IN VARCHAR2
2429 ,p_field IN VARCHAR2
2430 ) RETURN VARCHAR2
2431 IS
2432
2433 --
2434 -- Cursor to fetch the Rate Table id given the rates table name
2435 --
2436 CURSOR c_get_rates_table_id
2437 IS
2438 select user_table_id
2439 from pay_user_tables
2440 where range_or_match = 'M'
2441 and user_table_name = p_field;
2442
2443 l_field varchar2(100);
2444 BEGIN
2445
2446 IF (p_field IS NULL) THEN
2447 RETURN null;
2448 END IF;
2449 IF (p_option = 'R') THEN
2450 OPEN c_get_rates_table_id;
2451 FETCH c_get_rates_table_id INTO l_field;
2452 CLOSE c_get_rates_table_id;
2453 END IF;
2454 RETURN l_field;
2455 END get_code;
2456
2457 --
2458 -- Function get_meaning returns the meaning string of the id passed
2459 --
2460 -- The Meaning depends on the value of the p_option parameter
2461 -- p_option = 'R' -> p_field_id has the rates table id
2462 --and it Returns the Rates table Name
2463 -- p_option = 'E' -> p_field_id has the element type id
2464 --and it Returns the Element Name
2465 --
2466 FUNCTION get_meaning
2467 (p_option IN VARCHAR2
2468 ,p_field_id IN NUMBER
2469 ) RETURN VARCHAR2
2470 IS
2471
2472 --
2473 -- Cursor to fetch the Element Name given the element type id
2474 --
2475 CURSOR c_get_element_name
2476 IS
2477 select element_name
2478 from pay_element_types_f_tl
2479 where element_type_id = p_field_id;
2480
2481 --
2482 -- Cursor to fetch the Rates Table Name given the rates table id
2483 --
2484 CURSOR c_get_rates_table_name
2485 IS
2486 select user_table_name
2487 from pay_user_tables
2488 where user_table_id = p_field_id;
2489
2490 CURSOR c_get_purpose_name
2491 IS
2492 SELECT pur.row_low_range_or_name
2493 FROM pay_user_tables put
2494 ,pay_user_rows_f pur
2495 WHERE put.range_or_match = 'M'
2496 AND put.user_table_name ='PQP_TRAVEL_PURPOSE'
2497 AND put.user_table_id = pur.user_table_id
2498 AND pur.user_row_id= p_field_id;
2499
2500 l_field_meaning varchar2(100);
2501 l_sliding_rates VARCHAR2(10);
2502 l_get_purpose_name c_get_purpose_name%ROWTYPE;
2503 BEGIN
2504 IF (p_field_id IS NULL) THEN
2505 RETURN NULL;
2506 END IF;
2507 IF (p_option = 'R') THEN
2508 OPEN c_get_rates_table_name;
2509 FETCH c_get_rates_table_name INTO l_field_meaning;
2510 CLOSE c_get_rates_table_name;
2511 ELSIF (p_option = 'E') THEN
2512 OPEN c_get_element_name;
2513 FETCH c_get_element_name INTO l_field_meaning;
2514 CLOSE c_get_element_name;
2515 END IF;
2516 RETURN l_field_meaning;
2517 END get_meaning;
2518 ----------------------------------
2519
2520 ----------Generic Procedures
2521
2522 FUNCTION get_lkp_meaning (p_lookup_code IN VARCHAR2,
2523 p_lookup_type IN VARCHAR2
2524 )
2525 RETURN VARCHAR2
2526 AS
2527
2528 CURSOR c_get_lkp_meaning
2529 IS
2530 SELECT hl.meaning
2531 FROM hr_lookups hl
2532 WHERE hl.lookup_type=p_lookup_type
2533 AND hl.lookup_code=p_lookup_code;
2534
2535
2536 l_get_lkp_meaning c_get_lkp_meaning%ROWTYPE;
2537
2538 BEGIN
2539
2540 OPEN c_get_lkp_meaning;
2541 LOOP
2542 FETCH c_get_lkp_meaning INTO l_get_lkp_meaning;
2543 EXIT WHEN c_get_lkp_meaning%NOTFOUND;
2544 END LOOP;
2545 CLOSE c_get_lkp_meaning;
2546
2547 RETURN (l_get_lkp_meaning.meaning);
2548
2549 END;
2550 --Get lookup meaning
2551 FUNCTION get_meaning ( p_inp_type VARCHAR2
2552 ,p_code VARCHAR2
2553 )
2554 RETURN VARCHAR2
2555 AS
2556
2557 CURSOR c_get_meaning_u
2558 IS
2559 SELECT lkp.lookup_code
2560 ,lkp.meaning
2561 FROM hr_lookups lkp
2562 WHERE lkp.lookup_type IN ('PQP_PRIVATE_VEHICLE_USER'
2563 ,'PQP_COMPANY_VEHICLE_USER')
2564 AND lkp.lookup_code=p_code;
2565 --AND lkp.application_id=8303
2566
2567
2568 CURSOR c_get_meaning_o
2569 IS
2570 SELECT lkp.lookup_code
2571 ,lkp.meaning
2572 FROM hr_lookups lkp
2573 WHERE lookup_type in ('PQP_VEHICLE_OWNERSHIP_TYPE')
2574 AND lkp.lookup_code=p_code;
2575 --AND lkp.application_id=8303
2576
2577 CURSOR c_get_meaning_f
2578 IS
2579 select meaning
2580 from hr_lookups
2581 where lookup_type = 'PQP_FUEL_TYPE'
2582 and enabled_flag = 'Y'
2583 and lookup_code = p_code;
2584
2585 CURSOR c_get_meaning_cm
2586 IS
2587 select meaning
2588 from hr_lookups
2589 where lookup_type = 'PQP_VEHICLE_CALC_METHOD'
2590 and enabled_flag='Y'
2591 and lookup_code = p_code;
2592
2593 CURSOR c_get_meaning_vt
2594 IS
2595 select hl.meaning
2596 from pqp_vehicle_repository_f pvr,
2597 hr_lookups hl
2598 where pvr.registration_number = p_code
2599 and hl.lookup_type = 'PQP_VEHICLE_TYPE'
2600 and hl.enabled_flag = 'Y'
2601 and hl.lookup_code = pvr.vehicle_type;
2602
2603 CURSOR c_get_meaning_vehtype
2604 IS
2605 select hl.meaning
2606 from hr_lookups hl
2607 where hl.lookup_type = 'PQP_VEHICLE_TYPE'
2608 and hl.enabled_flag = 'Y'
2609 and hl.lookup_code = p_code;
2610
2611
2612 CURSOR c_get_purpose_name
2613 IS
2614 SELECT pur.row_low_range_or_name
2615 FROM pay_user_tables put
2616 ,pay_user_rows_f pur
2617 WHERE put.range_or_match = 'M'
2618 AND put.user_table_name ='PQP_TRAVEL_PURPOSE'
2619 AND put.user_table_id = pur.user_table_id
2620 AND pur.user_row_id= p_code;
2621
2622
2623 l_get_meaning_u c_get_meaning_u%ROWTYPE;
2624 l_get_meaning_o c_get_meaning_o%ROWTYPE;
2625 l_meaning hr_lookups.meaning%TYPE;
2626
2627 l_proc varchar2(72) := g_package ||'get_meaning';
2628 BEGIN
2629
2630 hr_utility.set_location('enter get meaning:' || l_proc,240);
2631 IF p_inp_type = 'EI' THEN
2632 OPEN c_get_meaning_o;
2633 LOOP
2634 FETCH c_get_meaning_o INTO l_get_meaning_o;
2635 EXIT WHEN c_get_meaning_o%NOTFOUND;
2636 RETURN(l_get_meaning_o.meaning);
2637 END LOOP;
2638 CLOSE c_get_meaning_o;
2639
2640 IF l_get_meaning_o.meaning IS NULL THEN
2641 RETURN(p_code);
2642 END IF;
2643 ELSIF p_inp_type = 'Rate Type' THEN
2644 OPEN c_get_meaning_u;
2645 LOOP
2646 FETCH c_get_meaning_u INTO l_get_meaning_u;
2647 EXIT WHEN c_get_meaning_u%NOTFOUND;
2648 RETURN (l_get_meaning_u.meaning);
2649 END LOOP;
2650 CLOSE c_get_meaning_u;
2651
2652
2653 IF l_get_meaning_u.meaning IS NULL THEN
2654 RETURN(p_code);
2655 END IF;
2656 --RETURN('NONE');
2657
2658 -- Gets the Vehicle Type for the given Reg. No.
2659 ELSIF p_inp_type = 'VT' THEN
2660 OPEN c_get_meaning_vt;
2661 FETCH c_get_meaning_vt INTO l_meaning;
2662 CLOSE c_get_meaning_vt;
2663 RETURN(l_meaning);
2664 -- Gets the Vehicle Type for the given VehType Code
2665 ELSIF p_inp_type = 'V' THEN
2666 OPEN c_get_meaning_vehtype;
2667 FETCH c_get_meaning_vehtype INTO l_meaning;
2668 CLOSE c_get_meaning_vehtype;
2669 RETURN(l_meaning);
2670 -- Gets the Fuel Type meaning given the code
2671 ELSIF p_inp_type = 'Fuel Type' THEN
2672 OPEN c_get_meaning_f;
2673 FETCH c_get_meaning_f INTO l_meaning;
2674 CLOSE c_get_meaning_f;
2675 RETURN(l_meaning);
2676
2677 -- Gets the Calculation Method meaning given the code
2678 ELSIF p_inp_type = 'CM' THEN
2679 OPEN c_get_meaning_cm;
2680 FETCH c_get_meaning_cm INTO l_meaning;
2681 CLOSE c_get_meaning_cm;
2682 RETURN(l_meaning);
2683
2684 ELSIF p_inp_type='Purpose' THEN
2685 OPEN c_get_purpose_name;
2686 FETCH c_get_purpose_name INTO l_meaning;
2687 CLOSE c_get_purpose_name;
2688 RETURN(l_meaning);
2689 END IF;
2690 hr_utility.set_location('Leaving get meaning:' || l_proc,250);
2691 exception
2692 when others then
2693 return(p_code);
2694 END;
2695
2696
2697 --This func is temporary
2698 FUNCTION get_total ( p_element_name IN VARCHAR2
2699 ,p_assignment_action_id IN NUMBER
2700 ,p_element_entry_id IN NUMBER
2701 ,p_business_group_id IN NUMBER
2702 )
2703 return NUMBER
2704 IS
2705 CURSOR c_get_balance_name
2706 IS
2707 SELECT balance_type_id ,pbd.balance_dimension_id
2708 FROM pay_balance_types pbt
2709 ,pay_balance_dimensions pbd
2710 WHERE balance_name = p_element_name||' Processed Amt'
2711 AND pbd.legislation_code='GB'
2712 AND pbd.dimension_name='_ELEMENT_ITD'
2713 AND pbt.business_group_id = p_business_group_id;
2714
2715 --This cursor fetches the balance when the option
2716 --is PAYE Taxable is YES.
2717 CURSOR c_get_tax_element_type_id
2718 IS
2719 SELECT DISTINCT pet.element_type_id
2720 FROM pay_element_types_f pet
2721 WHERE pet.element_name = p_element_name||' Taxable'
2722 AND pet.business_group_id=p_business_group_id ;
2723
2724 CURSOR c_get_gross_pay_bal_name
2725 IS
2726 SELECT balance_type_id
2727 FROM pay_balance_types pbt
2728 WHERE balance_name = 'Gross Pay'
2729 AND pbt.legislation_code='GB' ;
2730
2731
2732 CURSOR c_get_def_balance ( cp_balance_typ_id NUMBER
2733 ,cp_balance_dim_id NUMBER
2734 )
2735 IS
2736 SELECT pdb.defined_balance_id
2737 from pay_defined_balances pdb
2738 where pdb.balance_type_id =cp_balance_typ_id
2739 and pdb.balance_dimension_id=cp_balance_dim_id;
2740
2741 cursor c1(cp_balance_type_id NUMBER
2742 ,cp_assignment_action_id NUMBER
2743 ,cp_element_entry_id NUMBER)
2744 is
2745 SELECT nvl(SUM(fnd_number.canonical_to_number(TARGET.result_value)
2746 * FEED.scale),0) tot
2747 FROM pay_run_result_values TARGET
2748 , pay_balance_feeds_f FEED
2749 , pay_run_results RR
2750 , pay_assignment_actions ASSACT
2751 , pay_assignment_actions BAL_ASSACT
2752 , pay_payroll_actions PACT
2753 WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
2754 AND FEED.balance_type_id = cp_balance_type_id
2755 AND FEED.input_value_id = TARGET.input_value_id
2756 AND TARGET.run_result_id = RR.run_result_id
2757 AND RR.assignment_action_id = ASSACT.assignment_action_id
2758 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2759 AND PACT.effective_date between FEED.effective_start_date
2760 AND FEED.effective_end_date
2761 AND RR.status in ('P','PA')
2762 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2763 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
2764 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
2765 OR ( rr.source_type in ('R','V') /* reversal */
2766 AND exists
2767 ( SELECT null from pay_run_results rr1
2768 WHERE rr1.source_id = cp_element_entry_id
2769 AND rr1.run_result_id = rr.source_id
2770 AND rr1.source_type in ( 'E','I'))));
2771
2772
2773 cursor c1_tax(cp_balance_type_id NUMBER
2774 ,cp_assignment_action_id NUMBER
2775 ,cp_element_entry_id NUMBER
2776 ,cp_element_type_id NUMBER)
2777 IS
2778 SELECT target.result_value tot
2779 FROM pay_run_result_values TARGET
2780 , pay_balance_feeds_f FEED
2781 , pay_run_results RR
2782 , pay_assignment_actions ASSACT
2783 , pay_assignment_actions BAL_ASSACT
2784 , pay_payroll_actions PACT
2785 WHERE bal_assact.assignment_action_id = cp_assignment_action_id
2786 AND FEED.input_value_id = TARGET.input_value_id
2787 AND TARGET.run_result_id = RR.run_result_id
2788 AND RR.assignment_action_id = ASSACT.assignment_action_id
2789 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2790 AND PACT.effective_date between FEED.effective_start_date
2791 AND FEED.effective_end_date
2792 AND RR.status in ('P','PA')
2793 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2794 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
2795 AND rr.element_type_id=cp_element_type_id
2796 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
2797 OR ( rr.source_type in ('R','V')
2798 AND exists
2799 ( SELECT null from pay_run_results rr1
2800 WHERE rr1.source_id = cp_element_entry_id
2801 AND rr1.run_result_id = rr.source_id
2802 AND rr1.source_type in ( 'E','I'))))
2803 and feed.balance_type_id=cp_balance_type_id;
2804
2805
2806
2807
2808 l_get_balance_name c_get_balance_name%ROWTYPE;
2809 l_get_tax_element_type_id c_get_tax_element_type_id%ROWTYPE;
2810 l_get_gross_pay_bal_name c_get_gross_pay_bal_name%ROWTYPE;
2811 l_get_def_balance c_get_def_balance%ROWTYPE;
2812 lc1 c1%ROWTYPE;
2813 lc1_tax c1_tax%ROWTYPE;
2814 BEGIN
2815
2816 OPEN c_get_balance_name;
2817 FETCH c_get_balance_name INTO l_get_balance_name;
2818 OPEN c1(l_get_balance_name.balance_type_id
2819 ,p_assignment_action_id
2820 ,p_element_entry_id );
2821 FETCH c1 INTO lc1;
2822 IF lc1.tot = 0 THEN
2823 OPEN c_get_tax_element_type_id;
2824 FETCH c_get_tax_element_type_id
2825 INTO l_get_tax_element_type_id;
2826 CLOSE c_get_tax_element_type_id;
2827
2828 OPEN c_get_gross_pay_bal_name;
2829 FETCH c_get_gross_pay_bal_name INTO
2830 l_get_gross_pay_bal_name;
2831 CLOSE c_get_gross_pay_bal_name;
2832
2833 OPEN c1_tax (l_get_gross_pay_bal_name.balance_type_id
2834 ,p_assignment_action_id
2835 ,p_element_entry_id
2836 ,l_get_tax_element_type_id.element_type_id
2837 );
2838 FETCH c1_tax INTO lc1_tax;
2839 RETURN(NVL(lc1_tax.tot,0));
2840 CLOSE c1_tax;
2841 ELSE
2842
2843 return(NVL(lc1.tot,0));
2844 END IF;
2845
2846 --return(NVL(lc1.tot,0));
2847 CLOSE c1;
2848 CLOSE c_get_balance_name;
2849
2850
2851 return(0);
2852 END;
2853 --Function to get balance for the view.
2854 FUNCTION get_amount ( p_element_name IN VARCHAR2
2855 ,p_element_type_id IN NUMBER
2856 ,p_effective_date IN DATE
2857 ,p_assignment_id IN NUMBER
2858 )
2859
2860 return NUMBER
2861 IS
2862
2863 CURSOR c_get_balance_name
2864 IS
2865 SELECT balance_type_id ,pbd.balance_dimension_id
2866 FROM pay_balance_types pbt
2867 ,pay_balance_dimensions pbd
2868 WHERE balance_name = p_element_name||' Processed Amt'
2869 AND pbd.legislation_code='GB'
2870 AND pbd.dimension_name='_ELEMENT_ITD';
2871
2872
2873 CURSOR c_get_def_balance ( cp_balance_typ_id NUMBER
2874 ,cp_balance_dim_id NUMBER
2875 )
2876 IS
2877 SELECT pdb.DEFINED_BALANCE_ID
2878 from pay_defined_balances pdb
2879 where pdb.balance_type_id =cp_balance_typ_id
2880 and pdb.balance_dimension_id=cp_balance_dim_id;
2881
2882
2883
2884 BEGIN
2885
2886 FOR l_get_balance_name IN c_get_balance_name
2887 LOOP
2888
2889 FOR l_get_def_balance IN c_get_def_balance
2890 (l_get_balance_name.balance_type_id
2891 ,l_get_balance_name.balance_dimension_id
2892 )
2893 LOOP
2894 return(hr_dirbal.get_balance(p_assignment_id
2895 ,l_get_def_balance.DEFINED_BALANCE_ID
2896 ,p_effective_date));
2897
2898 END LOOP;
2899 END LOOP;
2900 return(0);
2901 EXCEPTION
2902 WHEN OTHERS THEN
2903 return(0);
2904
2905 END;
2906
2907 PROCEDURE insert_company_mileage_claim
2908 ( p_effective_date IN DATE,
2909 p_assignment_id IN NUMBER,
2910 p_business_group_id IN NUMBER,
2911 p_ownership IN VARCHAR2 ,
2912 p_usage_type IN VARCHAR2 ,
2913 p_vehicle_type IN VARCHAR2,
2914 p_start_date IN VARCHAR2 ,
2915 p_end_date IN VARCHAR2 ,
2916 p_claimed_mileage IN VARCHAR2 ,
2917 p_actual_mileage IN VARCHAR2 ,
2918 p_registration_number IN VARCHAR2 ,
2919 p_engine_capacity IN VARCHAR2 ,
2920 p_fuel_type IN VARCHAR2 ,
2921 p_calculation_method IN VARCHAR2 ,
2922 p_user_rates_table IN VARCHAR2 ,
2923 p_fiscal_ratings IN VARCHAR2 ,
2924 p_PAYE_taxable IN VARCHAR2 ,
2925 p_no_of_passengers IN VARCHAR2 ,
2926 p_purpose IN VARCHAR2 ,
2927 p_payroll_id IN NUMBER,
2928 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
2929 p_element_entry_id IN OUT NOCOPY NUMBER ,
2930 p_element_entry_date IN OUT NOCOPY DATE,
2931 p_element_link_id IN NUMBER
2932 )
2933
2934 IS
2935
2936 CURSOR c_get_table_name (cp_user_rates_table VARCHAR2
2937 ,cp_business_group_id NUMBER
2938 )
2939 IS
2940 SELECT put.user_table_name
2941 FROM pay_user_tables put
2942 WHERE user_table_id =cp_user_rates_table
2943 AND put.business_group_id=cp_business_group_id;
2944
2945 CURSOR c_get_input_details (cp_element_type_id NUMBER
2946 ,cp_effective_date DATE
2947 ,cp_business_group_id NUMBER
2948 )
2949 IS
2950 SELECT piv.input_value_id,
2951 piv.name,
2952 piv.display_sequence,
2953 piv.lookup_type,
2954 piv.default_value
2955 FROM pay_input_values_f piv
2956 WHERE piv.element_type_id=cp_element_type_id
2957 AND piv.business_group_id=cp_business_group_id
2958 AND cp_effective_date BETWEEN piv.effective_start_date
2959 AND piv.effective_end_date;
2960
2961
2962 l_effective_start_date DATE;
2963 l_effective_end_date DATE;
2964 l_get_input_details c_get_input_details%ROWTYPE;
2965 l_get_table_name c_get_table_name%ROWTYPE;
2966 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
2967 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
2968 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
2969 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
2970 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
2971 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
2972 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
2973 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
2974 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
2975 l_input_value_id10 pay_input_values_f.input_value_id%TYPE:=NULL;
2976 l_input_value_id11 pay_input_values_f.input_value_id%TYPE:=NULL;
2977 l_input_value_id12 pay_input_values_f.input_value_id%TYPE:=NULL;
2978 l_input_value_id13 pay_input_values_f.input_value_id%TYPE:=NULL;
2979 l_input_value_id14 pay_input_values_f.input_value_id%TYPE:=NULL;
2980 l_input_value_id15 pay_input_values_f.input_value_id%TYPE:=NULL;
2981
2982 l_entry_value1 pay_element_entry_values_f.screen_entry_value%TYPE;
2983 l_entry_value2 pay_element_entry_values_f.screen_entry_value%TYPE;
2984 l_entry_value3 pay_element_entry_values_f.screen_entry_value%TYPE;
2985 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
2986 l_entry_value5 pay_element_entry_values_f.screen_entry_value%TYPE;
2987 l_entry_value6 pay_element_entry_values_f.screen_entry_value%TYPE;
2988 l_entry_value7 pay_element_entry_values_f.screen_entry_value%TYPE;
2989 l_entry_value8 pay_element_entry_values_f.screen_entry_value%TYPE;
2990 l_entry_value9 pay_element_entry_values_f.screen_entry_value%TYPE;
2991 l_entry_value10 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2992 l_entry_value11 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2993 l_entry_value12 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2994 l_entry_value13 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2995 l_entry_value14 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2996 l_entry_value15 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2997 l_element_entry_id number;
2998 l_proc varchar2(72) := g_package ||'get_meaning';
2999 l_temp_var NUMBER;
3000 BEGIN
3001 hr_utility.set_location('Insert_company_mileage_claim:' || l_proc,260);
3002 l_effective_start_date :=p_effective_date;
3003 l_effective_end_date :=hr_api.g_eot;
3004
3005
3006 BEGIN
3007 l_temp_var := to_number(p_user_rates_table);
3008
3009
3010
3011 OPEN c_get_table_name ( p_user_rates_table
3012 ,p_business_group_id
3013 );
3014 FETCH c_get_table_name INTO l_get_table_name;
3015 CLOSE c_get_table_name;
3016 EXCEPTION
3017 ---------
3018 WHEN OTHERS THEN
3019 l_get_table_name.user_table_name:=p_user_rates_table;
3020 END;
3021
3022 OPEN c_get_input_details (p_mileage_claim_element
3023 ,p_effective_date
3024 ,p_business_group_id
3025 );
3026 LOOP
3027 FETCH c_get_input_details INTO l_get_input_details;
3028 EXIT WHEN c_get_input_details%NOTFOUND;
3029
3030
3031
3032 IF l_get_input_details.name='Pay Value' THEN
3033 l_input_value_id1:=l_get_input_details.input_value_id;
3034 l_entry_value1 :=NULL;
3035 ELSIF l_get_input_details.name='Vehicle Type' THEN
3036
3037 l_input_value_id2:=l_get_input_details.input_value_id;
3038 IF l_get_input_details.lookup_type IS NOT NULL THEN
3039 l_entry_value2:=get_lkp_meaning(p_usage_type
3040 ,l_get_input_details.lookup_type);
3041 ELSE
3042 l_entry_value2 :=p_usage_type;
3043 END IF;
3044 ELSIF l_get_input_details.name='Two Wheeler Type' THEN
3045 l_input_value_id3:=l_get_input_details.input_value_id;
3046 IF l_get_input_details.lookup_type IS NOT NULL THEN
3047 l_entry_value3:=get_lkp_meaning(p_start_date
3048 ,l_get_input_details.lookup_type);
3049 ELSE
3050 l_entry_value3 :=p_start_date;
3051 END IF;
3052 ELSIF l_get_input_details.name='Claim Start Date' THEN
3053 l_input_value_id3:=l_get_input_details.input_value_id;
3054 IF l_get_input_details.lookup_type IS NOT NULL THEN
3055 l_entry_value3:=get_lkp_meaning(p_start_date
3056 ,l_get_input_details.lookup_type);
3057 ELSE
3058 l_entry_value3 :=p_start_date;
3059 END IF;
3060
3061
3062 ELSIF l_get_input_details.name='Claim End Date' THEN
3063 l_input_value_id4:=l_get_input_details.input_value_id;
3064 IF l_get_input_details.lookup_type IS NOT NULL THEN
3065 l_entry_value4:=get_lkp_meaning(p_end_date
3066 ,l_get_input_details.lookup_type);
3067 ELSE
3068 l_entry_value4 :=p_end_date;
3069 END IF;
3070 ELSIF l_get_input_details.name='Claimed Mileage' THEN
3071 l_input_value_id5:=l_get_input_details.input_value_id;
3072 IF l_get_input_details.lookup_type IS NOT NULL THEN
3073 l_entry_value5:=get_lkp_meaning(p_claimed_mileage
3074 ,l_get_input_details.lookup_type);
3075 ELSE
3076 l_entry_value5 :=p_claimed_mileage;
3077 END IF;
3078 ELSIF l_get_input_details.name='Actual Mileage' THEN
3079 l_input_value_id6:=l_get_input_details.input_value_id;
3080 IF l_get_input_details.lookup_type IS NOT NULL THEN
3081 l_entry_value6:=get_lkp_meaning(p_actual_mileage
3082 ,l_get_input_details.lookup_type);
3083 ELSE
3084 l_entry_value6 :=p_actual_mileage;
3085 END IF;
3086 ELSIF l_get_input_details.name='User Rates Table'
3087 OR l_get_input_details.name='Sliding Rates Table' THEN
3088 l_input_value_id7:=l_get_input_details.input_value_id;
3089 IF l_get_input_details.lookup_type IS NOT NULL THEN
3090 l_entry_value7:=get_lkp_meaning(p_user_rates_table
3091 ,l_get_input_details.lookup_type);
3092 ELSE
3093
3094 l_entry_value7 :=l_get_table_name.user_table_name;
3095 END IF;
3096 ELSIF l_get_input_details.name='PAYE Taxable' THEN
3097 l_input_value_id8:=l_get_input_details.input_value_id;
3098 IF l_get_input_details.lookup_type IS NOT NULL THEN
3099 l_entry_value8:=get_lkp_meaning(NVL(p_PAYE_taxable
3100 ,l_get_input_details.default_value)
3101 ,l_get_input_details.lookup_type);
3102 ELSE
3103 l_entry_value8 :=p_PAYE_taxable;
3104 END IF;
3105 ELSIF l_get_input_details.name='No of Passengers' THEN
3106 l_input_value_id9:=l_get_input_details.input_value_id;
3107 IF l_get_input_details.lookup_type IS NOT NULL THEN
3108 l_entry_value9:=get_lkp_meaning(p_no_of_Passengers
3109 ,l_get_input_details.lookup_type);
3110 ELSE
3111 l_entry_value9 :=p_no_of_Passengers;
3112 END IF;
3113 ELSIF l_get_input_details.name='Vehicle Reg Number' THEN
3114 l_input_value_id10:=l_get_input_details.input_value_id;
3115 IF l_get_input_details.lookup_type IS NOT NULL THEN
3116 l_entry_value10:=get_lkp_meaning(p_registration_number
3117 ,l_get_input_details.lookup_type);
3118 ELSE
3119 l_entry_value10 := p_registration_number;
3120 END IF;
3121 ELSIF l_get_input_details.name='Engine Capacity' THEN
3122 l_input_value_id11:=l_get_input_details.input_value_id;
3123 IF l_get_input_details.lookup_type IS NOT NULL THEN
3124 l_entry_value11:=get_lkp_meaning(p_engine_capacity
3125 ,l_get_input_details.lookup_type);
3126 ELSE
3127 l_entry_value11 :=p_engine_capacity;
3128 END IF;
3129 ELSIF l_get_input_details.name='Fuel Type' THEN
3130 l_input_value_id12:=l_get_input_details.input_value_id;
3131 IF l_get_input_details.lookup_type IS NOT NULL THEN
3132 l_entry_value12:=get_lkp_meaning(p_fuel_type
3133 ,l_get_input_details.lookup_type);
3134 ELSE
3135 l_entry_value12 :=p_fuel_type;
3136 END IF;
3137 ELSIF l_get_input_details.name='Calculation Method' THEN
3138 l_input_value_id13:=l_get_input_details.input_value_id;
3139 IF l_get_input_details.lookup_type IS NOT NULL THEN
3140 --l_entry_value13:=get_lkp_meaning(p_calculation_method
3141 -- ,l_get_input_details.lookup_type);
3142 l_entry_value13:=get_lkp_meaning(NVL(p_calculation_method
3143 ,l_get_input_details.default_value)
3144 ,l_get_input_details.lookup_type);
3145 ELSE
3146 --l_entry_value13 :=p_calculation_method;
3147 l_entry_value13 :=NVL(p_calculation_method,
3148 l_get_input_details.default_value);
3149 END IF;
3150 ELSIF l_get_input_details.name='Purpose' THEN
3151 l_input_value_id14:=l_get_input_details.input_value_id;
3152 IF l_get_input_details.lookup_type IS NOT NULL THEN
3153 l_entry_value14:=get_lkp_meaning(p_purpose
3154 ,l_get_input_details.lookup_type);
3155 ELSE
3156 l_entry_value14 :=p_purpose;
3157 END IF;
3158 END IF;
3159
3160
3161 END LOOP;
3162 CLOSE c_get_input_details;
3163 hr_utility.set_location('Entering hr_entry_apiinsert_element_entry',270);
3164 hr_entry_api.insert_element_entry
3165 (
3166 p_effective_start_date =>l_effective_start_date,
3167 p_effective_end_date =>l_effective_end_date,
3168 p_element_entry_id =>p_element_entry_id,
3169 p_original_entry_id =>null,
3170 p_assignment_id =>p_assignment_id,
3171 p_element_link_id =>p_element_link_id,
3172 p_creator_type =>'F',
3173 p_entry_type =>'E',
3174 p_creator_id => null,
3175 p_input_value_id1 =>l_input_value_id1,
3176 p_input_value_id2 =>l_input_value_id2,
3177 p_input_value_id3 =>l_input_value_id3,
3178 p_input_value_id4 =>l_input_value_id4,
3179 p_input_value_id5 =>l_input_value_id5,
3180 p_input_value_id6 =>l_input_value_id6,
3181 p_input_value_id7 =>l_input_value_id7,
3182 p_input_value_id8 =>l_input_value_id8,
3183 p_input_value_id9 =>l_input_value_id9,
3184 p_input_value_id10 =>l_input_value_id10 ,
3185 p_input_value_id11 =>l_input_value_id11,
3186 p_input_value_id12 =>l_input_value_id12,
3187 p_input_value_id13 =>l_input_value_id13,
3188 p_input_value_id14 =>l_input_value_id14,
3189 p_input_value_id15 =>l_input_value_id15,
3190 p_entry_value1 =>l_entry_value1,
3191 p_entry_value2 =>l_entry_value2 ,
3192 p_entry_value3 =>l_entry_value3,
3193 p_entry_value4 =>l_entry_value4,
3194 p_entry_value5 =>l_entry_value5,
3195 p_entry_value6 =>l_entry_value6,
3196 p_entry_value7 =>l_entry_value7,
3197 p_entry_value8 =>l_entry_value8,
3198 p_entry_value9 =>l_entry_value9,
3199 p_entry_value10 =>l_entry_value10,
3200 p_entry_value11 =>l_entry_value11,
3201 p_entry_value12 =>l_entry_value12,
3202 p_entry_value13 =>l_entry_value13,
3203 p_entry_value14 =>l_entry_value14,
3204 p_entry_value15 =>l_entry_value15
3205 );
3206
3207 END;
3208
3209 --Inserts GB specific claim
3210 PROCEDURE insert_private_mileage_claim
3211 ( p_effective_date IN DATE,
3212 p_assignment_id IN NUMBER,
3213 p_business_group_id IN NUMBER,
3214 p_ownership IN VARCHAR2 ,
3215 p_usage_type IN VARCHAR2 ,
3216 p_vehicle_type IN VARCHAR2,
3217 p_start_date IN VARCHAR2 ,
3218 p_end_date IN VARCHAR2 ,
3219 p_claimed_mileage IN VARCHAR2 ,
3220 p_actual_mileage IN VARCHAR2 ,
3221 p_registration_number IN VARCHAR2 ,
3222 p_engine_capacity IN VARCHAR2 ,
3223 p_fuel_type IN VARCHAR2 ,
3224 p_calculation_method IN VARCHAR2 ,
3225 p_user_rates_table IN VARCHAR2 ,
3226 p_fiscal_ratings IN VARCHAR2 ,
3227 p_PAYE_taxable IN VARCHAR2 ,
3228 p_no_of_passengers IN VARCHAR2 ,
3229 p_purpose IN VARCHAR2 ,
3230 p_payroll_id IN NUMBER,
3231 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
3232 p_element_entry_id IN OUT NOCOPY NUMBER ,
3233 p_element_entry_date IN OUT NOCOPY DATE,
3234 p_element_link_id IN NUMBER
3235 )
3236
3237 IS
3238
3239
3240 CURSOR c_get_input_details (cp_element_type_id NUMBER
3241 ,cp_effective_date DATE
3242 ,cp_business_group_id NUMBER
3243 )
3244 IS
3245 SELECT piv.input_value_id,
3246 piv.name,
3247 piv.display_sequence,
3248 piv.lookup_type,
3249 piv.default_value
3250 FROM pay_input_values_f piv
3251 WHERE piv.element_type_id=cp_element_type_id
3252 AND piv.business_group_id=cp_business_group_id
3253 AND cp_effective_date BETWEEN piv.effective_start_date
3254 AND piv.effective_end_date;
3255
3256 CURSOR c_get_table_name ( cp_user_rates_table VARCHAR2
3257 ,cp_business_group_id NUMBER
3258 )
3259 IS
3260 SELECT put.user_table_name
3261 FROM pay_user_tables put
3262 WHERE user_table_id =cp_user_rates_table
3263 AND put.business_group_id=cp_business_group_id;
3264 l_effective_start_date DATE;
3265 l_effective_end_date DATE;
3266 l_get_input_details c_get_input_details%ROWTYPE;
3267 l_get_table_name c_get_table_name%ROWTYPE;
3268
3269 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
3270 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
3271 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
3272 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
3273 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
3274 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
3275 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
3276 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
3277 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
3278 l_input_value_id10 pay_input_values_f.input_value_id%TYPE:=NULL;
3279 l_input_value_id11 pay_input_values_f.input_value_id%TYPE:=NULL;
3280 l_input_value_id12 pay_input_values_f.input_value_id%TYPE:=NULL;
3281 l_input_value_id13 pay_input_values_f.input_value_id%TYPE:=NULL;
3282 l_input_value_id14 pay_input_values_f.input_value_id%TYPE:=NULL;
3283 l_input_value_id15 pay_input_values_f.input_value_id%TYPE:=NULL;
3284 l_input_value_id16 pay_input_values_f.input_value_id%TYPE:=NULL;
3285
3286 l_entry_value1 pay_element_entry_values_f.screen_entry_value%TYPE;
3287 l_entry_value2 pay_element_entry_values_f.screen_entry_value%TYPE;
3288 l_entry_value3 pay_element_entry_values_f.screen_entry_value%TYPE;
3289 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
3290 l_entry_value5 pay_element_entry_values_f.screen_entry_value%TYPE;
3291 l_entry_value6 pay_element_entry_values_f.screen_entry_value%TYPE;
3292 l_entry_value7 pay_element_entry_values_f.screen_entry_value%TYPE;
3293 l_entry_value8 pay_element_entry_values_f.screen_entry_value%TYPE;
3294 l_entry_value9 pay_element_entry_values_f.screen_entry_value%TYPE;
3295 l_entry_value10 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3296 l_entry_value11 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3297 l_entry_value12 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3298 l_entry_value13 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3299 l_entry_value14 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3300 l_entry_value15 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3301 l_entry_value16 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3302 l_temp_var NUMBER;
3303 BEGIN
3304 hr_utility.set_location('Enter private element entry process',280);
3305 l_effective_start_date :=p_effective_date;
3306 l_effective_end_date :=hr_api.g_eot;
3307
3308 BEGIN
3309 l_temp_var := to_number(p_user_rates_table);
3310
3311
3312
3313 OPEN c_get_table_name ( p_user_rates_table
3314 ,p_business_group_id
3315 );
3316 FETCH c_get_table_name INTO l_get_table_name;
3317 CLOSE c_get_table_name;
3318 EXCEPTION
3319 ---------
3320 WHEN OTHERS THEN
3321 l_get_table_name.user_table_name:=p_user_rates_table;
3322 END;
3323
3324 OPEN c_get_input_details (p_mileage_claim_element
3325 ,p_effective_date
3326 ,p_business_group_id
3327 );
3328 LOOP
3329 FETCH c_get_input_details INTO l_get_input_details;
3330 EXIT WHEN c_get_input_details%NOTFOUND;
3331
3332
3333 IF l_get_input_details.name='Pay Value' THEN
3334 l_input_value_id1:=l_get_input_details.input_value_id;
3335 l_entry_value1 :=NULL;
3336 ELSIF l_get_input_details.name='Rate Type' THEN
3337
3338 l_input_value_id2:=l_get_input_details.input_value_id;
3339 IF l_get_input_details.lookup_type IS NOT NULL THEN
3340 IF p_usage_type IS NULL THEN
3341 l_entry_value2 :=get_dflt_input_value (l_input_value_id2
3342 ,p_mileage_claim_element
3343 ,p_business_group_id
3344 ,p_effective_date
3345 );
3346 l_entry_value2:=get_lkp_meaning(l_entry_value2
3347 ,l_get_input_details.lookup_type);
3348 ELSE
3349 l_entry_value2:=get_lkp_meaning(p_usage_type
3350 ,l_get_input_details.lookup_type);
3351 END IF;
3352 ELSE
3353 l_entry_value2 :=p_usage_type;
3354
3355 END IF;
3356 ELSIF l_get_input_details.name='Two Wheeler Type' THEN
3357 l_input_value_id3:=l_get_input_details.input_value_id;
3358 IF l_get_input_details.lookup_type IS NOT NULL THEN
3359 l_entry_value3:=get_lkp_meaning(p_start_date
3360 ,l_get_input_details.lookup_type);
3361 ELSE
3362 l_entry_value3 :=p_start_date;
3363 END IF;
3364 ELSIF l_get_input_details.name='Claim Start Date' THEN
3365 l_input_value_id4:=l_get_input_details.input_value_id;
3366 IF l_get_input_details.lookup_type IS NOT NULL THEN
3367 l_entry_value4:=get_lkp_meaning(p_start_date
3368 ,l_get_input_details.lookup_type);
3369 ELSE
3370 l_entry_value4 :=p_start_date;
3371 END IF;
3372
3373 ELSIF l_get_input_details.name='Claim End Date' THEN
3374 l_input_value_id5:=l_get_input_details.input_value_id;
3375 IF l_get_input_details.lookup_type IS NOT NULL THEN
3376 l_entry_value5:=get_lkp_meaning(p_end_date
3377 ,l_get_input_details.lookup_type);
3378 ELSE
3379 l_entry_value5 :=p_end_date;
3380 END IF;
3381 ELSIF l_get_input_details.name='Claimed Mileage' THEN
3382 l_input_value_id6:=l_get_input_details.input_value_id;
3383 IF l_get_input_details.lookup_type IS NOT NULL THEN
3384 l_entry_value6:=get_lkp_meaning(p_claimed_mileage
3385 ,l_get_input_details.lookup_type);
3386 ELSE
3387 l_entry_value6 :=p_claimed_mileage;
3388 END IF;
3389 ELSIF l_get_input_details.name='Actual Mileage' THEN
3390 l_input_value_id7:=l_get_input_details.input_value_id;
3391 IF l_get_input_details.lookup_type IS NOT NULL THEN
3392 l_entry_value7:=get_lkp_meaning(p_actual_mileage
3393 ,l_get_input_details.lookup_type);
3394 ELSE
3395 l_entry_value7 :=p_actual_mileage;
3396 END IF;
3397 ELSIF l_get_input_details.name='Vehicle Reg Number' THEN
3398 l_input_value_id8:=l_get_input_details.input_value_id;
3399 IF l_get_input_details.lookup_type IS NOT NULL THEN
3400 l_entry_value8:=get_lkp_meaning(p_registration_number
3401 ,l_get_input_details.lookup_type);
3402 ELSE
3403 l_entry_value8 := p_registration_number;
3404 END IF;
3405 ELSIF l_get_input_details.name='Engine Capacity' THEN
3406 l_input_value_id9:=l_get_input_details.input_value_id;
3407 IF l_get_input_details.lookup_type IS NOT NULL THEN
3408 l_entry_value9:=get_lkp_meaning(p_engine_capacity
3409 ,l_get_input_details.lookup_type);
3410 ELSE
3411 l_entry_value9 :=p_engine_capacity;
3412 END IF;
3413 ELSIF l_get_input_details.name='Fuel Type' THEN
3414 l_input_value_id10:=l_get_input_details.input_value_id;
3415 IF l_get_input_details.lookup_type IS NOT NULL THEN
3416 l_entry_value10:=get_lkp_meaning(p_fuel_type
3417 ,l_get_input_details.lookup_type);
3418 ELSE
3419 l_entry_value10 :=p_fuel_type;
3420 END IF;
3421 ELSIF l_get_input_details.name='Calculation Method' THEN
3422 l_input_value_id11:=l_get_input_details.input_value_id;
3423 IF l_get_input_details.lookup_type IS NOT NULL THEN
3424 --l_entry_value11:=get_lkp_meaning(p_calculation_method
3425 -- ,l_get_input_details.lookup_type);
3426 l_entry_value11:=get_lkp_meaning(NVL(p_calculation_method
3427 ,l_get_input_details.default_value)
3428 ,l_get_input_details.lookup_type);
3429 ELSE
3430 --l_entry_value11 :=p_calculation_method;
3431 l_entry_value11 :=NVL(p_calculation_method,
3432 l_get_input_details.default_value);
3433 END IF;
3434 ELSIF l_get_input_details.name='User Rates Table' OR
3435 l_get_input_details.name='Sliding Rates Table' THEN
3436 l_input_value_id12:=l_get_input_details.input_value_id;
3437 IF l_get_input_details.lookup_type IS NOT NULL THEN
3438 l_entry_value12:=get_lkp_meaning(p_user_rates_table
3439 ,l_get_input_details.lookup_type);
3440 ELSE
3441 IF p_user_rates_table IS NOT NULL THEN
3442 l_entry_value12 :=l_get_table_name.user_table_name;
3443 ELSE
3444
3445 l_entry_value12 :=l_get_input_details.default_value;
3446
3447 END IF;
3448 END IF;
3449 ELSIF l_get_input_details.name='PAYE Taxable' THEN
3450 l_input_value_id13:=l_get_input_details.input_value_id;
3451 IF l_get_input_details.lookup_type IS NOT NULL THEN
3452 l_entry_value13:=get_lkp_meaning(NVL(p_PAYE_Taxable
3453 ,l_get_input_details.default_value)
3454 ,l_get_input_details.lookup_type);
3455 ELSE
3456 l_entry_value13 :=p_PAYE_Taxable;
3457 END IF;
3458 ELSIF l_get_input_details.name='No of Passengers' THEN
3459 l_input_value_id14:=l_get_input_details.input_value_id;
3460 IF l_get_input_details.lookup_type IS NOT NULL THEN
3461 l_entry_value14:=get_lkp_meaning(p_no_of_passengers
3462 ,l_get_input_details.lookup_type);
3463 ELSE
3464 l_entry_value14 :=p_no_of_passengers;
3465 END IF;
3466 ELSIF l_get_input_details.name='CO2 Emissions' THEN
3467 l_input_value_id15:=l_get_input_details.input_value_id;
3468 IF l_get_input_details.lookup_type IS NOT NULL THEN
3469 l_entry_value15:=get_lkp_meaning(p_fiscal_ratings
3470 ,l_get_input_details.lookup_type);
3471 ELSE
3472 l_entry_value15 :=p_fiscal_ratings;
3473 END IF;
3474 ELSIF l_get_input_details.name='Purpose' THEN
3475 l_input_value_id16:=l_get_input_details.input_value_id;
3476 IF l_get_input_details.lookup_type IS NOT NULL THEN
3477 l_entry_value16:=get_lkp_meaning(p_purpose
3478 ,l_get_input_details.lookup_type);
3479 ELSE
3480 l_entry_value16 :=p_purpose;
3481 END IF;
3482 END IF;
3483
3484
3485
3486
3487
3488 END LOOP;
3489 CLOSE c_get_input_details;
3490
3491 IF l_input_value_id3 IS NULL THEN
3492 l_entry_value3 :=l_entry_value16;
3493 l_input_value_id3 :=l_input_value_id16;
3494
3495
3496 END IF;
3497 hr_utility.set_location('Enter hr_entry_apiinsert_element_entr',290);
3498 hr_entry_api.insert_element_entry
3499 (
3500 --
3501 -- Common Parameters
3502 --
3503 p_effective_start_date =>l_effective_start_date,
3504 p_effective_end_date =>l_effective_end_date,
3505 p_element_entry_id =>p_element_entry_id,
3506 p_original_entry_id =>null,
3507 p_assignment_id =>p_assignment_id,
3508 p_element_link_id =>p_element_link_id,
3509 p_creator_type =>'F',
3510 p_entry_type =>'E',
3511 p_creator_id => null,
3512 p_input_value_id1 =>l_input_value_id1,
3513 p_input_value_id2 =>l_input_value_id2,
3514 p_input_value_id3 =>l_input_value_id3,
3515 p_input_value_id4 =>l_input_value_id4,
3516 p_input_value_id5 =>l_input_value_id5,
3517 p_input_value_id6 =>l_input_value_id6,
3518 p_input_value_id7 =>l_input_value_id7,
3519 p_input_value_id8 =>l_input_value_id8,
3520 p_input_value_id9 =>l_input_value_id9,
3521 p_input_value_id10 =>l_input_value_id10 ,
3522 p_input_value_id11 =>l_input_value_id11,
3523 p_input_value_id12 =>l_input_value_id12,
3524 p_input_value_id13 =>l_input_value_id13,
3525 p_input_value_id14 =>l_input_value_id14,
3526 p_input_value_id15 =>l_input_value_id15,
3527 p_entry_value1 =>l_entry_value1,
3528 p_entry_value2 =>l_entry_value2 ,
3529 p_entry_value3 =>l_entry_value3,
3530 p_entry_value4 =>l_entry_value4,
3531 p_entry_value5 =>l_entry_value5,
3532 p_entry_value6 =>l_entry_value6,
3533 p_entry_value7 =>l_entry_value7,
3534 p_entry_value8 =>l_entry_value8,
3535 p_entry_value9 =>l_entry_value9,
3536 p_entry_value10 =>l_entry_value10,
3537 p_entry_value11 =>l_entry_value11,
3538 p_entry_value12 =>l_entry_value12,
3539 p_entry_value13 =>l_entry_value13,
3540 p_entry_value14 =>l_entry_value14,
3541 p_entry_value15 =>l_entry_value15
3542 );
3543 hr_utility.set_location('leaving hr_entry_apiinsert_element_entr',300);
3544 END;
3545 END;
[Home] [Help]