1 PACKAGE BODY pqp_gb_mileage_claim_pkg AS
2 /* $Header: pqgbmgcm.pkb 120.0 2005/05/29 01:49:38 appldev noship $ */
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 pqp_gb_mileage_claim_pkg.get_config_info (
1148 p_business_group_id =>p_business_group_id
1149 ,p_ownership =>p_ownership
1150 ,p_usage_type =>p_usage_type
1151 ,p_vehicle_type =>p_vehicle_type
1152 ,p_fuel_type =>p_fuel_type
1153 ,p_sl_rates_type =>l_sliding_rates
1154 ,p_rates =>l_get_rate_id
1155 ,p_element_id =>l_get_element_type_id
1156 );
1157
1158 l_rates_table_id :=to_number(l_get_rate_id);
1159 l_get_asg_rate_id:=to_number(l_get_rate_id);
1160 IF l_get_asg_rate_id IS NULL OR l_get_asg_rate_id=-1 THEN
1161 l_get_asg_rate_id := get_default_value
1162 (p_business_group_id =>p_business_group_id
1163 ,p_element_type_id =>l_element_type_id
1164 ,p_effective_date =>p_effective_date
1165 ,p_search_type =>'User Rates Table'
1166 );
1167
1168 l_rates_table_id :=to_number(l_get_rate_id);
1169 ELSE
1170 l_rates_table_id :=l_get_asg_rate_id;
1171 END IF;
1172 ELSE
1173 l_rates_table_id :=NVL(l_get_asg_rate_id,l_rates_table_id);
1174 END IF;
1175 ELSE
1176 l_rates_table_id :=(p_user_rates_table);
1177 END IF;
1178
1179
1180 hr_utility.set_location(' Enter mndtry field chk:' || l_proc,120);
1181 --All mandatory field validations
1182 l_chk_mndtry:= chk_mndtry_fields (
1183 p_effective_date =>p_effective_date
1184 ,p_assignment_id =>p_assignment_id
1185 ,p_business_group_id =>p_business_group_id
1186 ,p_ownership =>p_ownership
1187 ,p_usage_type =>p_usage_type
1188 ,p_vehicle_type =>p_vehicle_type
1189 ,p_start_date =>p_start_date
1190 ,p_end_date =>p_end_date
1191 ,p_claimed_mileage =>p_claimed_mileage
1192 ,p_actual_mileage =>p_actual_mileage
1193 ,p_registration_number =>p_registration_number
1194 ,p_engine_capacity =>l_engine_capacity
1195 ,p_fuel_type =>p_fuel_type
1196 ,p_element_type_id =>l_element_type_id
1197 ,p_data_source =>p_data_source
1198 ,p_message =>l_message
1199 );
1200
1201
1202 IF l_chk_mndtry = 'N' THEN
1203 fnd_message.raise_error;
1204 hr_multi_message.end_validation_set;
1205 END IF;
1206
1207 --Input values are vary from Private to Company vehicles
1208 --So inserting the values besed on ownership
1209 IF p_ownership='C' THEN
1210 hr_utility.set_location(' Enter company mileage:' || l_proc,130);
1211 insert_company_mileage_claim
1212 ( p_effective_date =>l_effective_date
1213 ,p_assignment_id =>p_assignment_id
1214 ,p_business_group_id =>p_business_group_id
1215 ,p_ownership =>p_ownership
1216 ,p_usage_type =>p_usage_type
1217 ,p_vehicle_type =>p_vehicle_type
1218 ,p_start_date =>p_start_date
1219 ,p_end_date =>p_end_date
1220 ,p_claimed_mileage =>p_claimed_mileage
1221 ,p_actual_mileage =>p_actual_mileage
1222 ,p_registration_number =>p_registration_number
1223 ,p_engine_capacity =>l_engine_capacity
1224 ,p_fuel_type =>p_fuel_type
1225 ,p_calculation_method =>p_calculation_method
1226 ,p_user_rates_table =>l_rates_table_id --p_user_rates_table
1227 ,p_fiscal_ratings =>p_fiscal_ratings
1228 ,p_PAYE_taxable =>l_PAYE_taxable
1229 ,p_no_of_passengers =>p_no_of_passengers
1230 ,p_purpose =>p_purpose
1231 ,p_payroll_id =>l_get_pay_det
1232 ,p_mileage_claim_element =>l_element_type_id
1233 ,p_element_entry_id =>p_element_entry_id
1234 ,p_element_entry_date =>p_element_entry_date
1235 ,p_element_link_id =>l_element_link_id
1236 );
1237
1238 ELSIF p_ownership='P' THEN
1239 hr_utility.set_location(' Enter private mileage:' || l_proc,140);
1240 insert_private_mileage_claim
1241 ( p_effective_date =>l_effective_date
1242 ,p_assignment_id =>p_assignment_id
1243 ,p_business_group_id =>p_business_group_id
1244 ,p_ownership =>p_ownership
1245 ,p_usage_type =>p_usage_type
1246 ,p_vehicle_type =>p_vehicle_type
1247 ,p_start_date =>p_start_date
1248 ,p_end_date =>p_end_date
1249 ,p_claimed_mileage =>p_claimed_mileage
1250 ,p_actual_mileage =>p_actual_mileage
1251 ,p_registration_number =>p_registration_number
1252 ,p_engine_capacity =>l_engine_capacity
1253 ,p_fuel_type =>p_fuel_type
1254 ,p_calculation_method =>p_calculation_method
1255 ,p_user_rates_table =>l_rates_table_id --p_user_rates_table
1256 ,p_fiscal_ratings =>p_fiscal_ratings
1257 ,p_PAYE_taxable =>l_PAYE_taxable
1258 ,p_no_of_passengers =>p_no_of_passengers
1259 ,p_purpose =>p_purpose
1260 ,p_payroll_id =>l_get_pay_det
1261 ,p_mileage_claim_element =>l_element_type_id
1262 ,p_element_entry_id =>p_element_entry_id
1263 ,p_element_entry_date =>p_element_entry_date
1264 ,p_element_link_id =>l_element_link_id
1265 );
1266 END IF;
1267 --This call is for webADI related stuff
1268 ELSIF p_info_id IS NOT NULL THEN
1269 hr_utility.set_location(' Enter WEBADI:' || l_proc,150);
1270 l_assignment_id := substr(p_info_id,1,instr(p_info_id,l_string)-1);
1271 -- l_businesss_group_id:=substr(p_info_id,instr(p_info_id,l_string)+LENGTH(l_string),
1272 -- instr(p_info_id,l_string,1,2)-instr(p_info_id,l_string)-LENGTH(l_string));
1273 -- l_session_id :=substr(p_info_id,instr(p_info_id,l_string,1,2)+LENGTH(l_string)) ;
1274 /*This is changed now as the url contains -1,-1 for both values below
1275 now these values are derived from the profile value*/
1276 l_businesss_group_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
1277 l_session_id := fnd_profile.value('USER_ID');
1278
1279
1280 l_st_dt :=FND_DATE.CHARDATE_TO_DATE(p_start_date);
1281 l_ed_dt :=FND_DATE.CHARDATE_TO_DATE(p_end_date);
1282 l_canonical_st_dt :=fnd_date.date_to_canonical(l_st_dt);
1283 l_canonical_ed_dt :=fnd_date.date_to_canonical(l_ed_dt);
1284 -- l_canonical_st_dt :=fnd_date.date_to_displaydt(p_start_date);
1285 -- l_canonical_ed_dt :=fnd_date.date_to_displaydt(p_end_date);
1286
1287 INSERT INTO pay_us_rpt_totals
1288 (gre_name , -- Stores session id
1289 state_name, -- Stores timestamp
1290 state_abbrev, -- Stores info that tells
1291 --the date is for Web ADI. A string ADI
1292 attribute1, -- Stores Reg #
1293 attribute2, -- Stores Claimed Mileage
1294 attribute3, -- Stores Vehicle Type
1295 attribute4, -- Stores start Date
1296 attribute5, -- Stores end date
1297 attribute6, -- Stores actual mileage
1298 attribute7, -- Stores usage type
1299 attribute8 , -- Stores ownership
1300 bUsiness_group_id ,
1301 location_id --Stores assignmentId
1302 )
1303 VALUES
1304 (
1305 l_session_id ,
1306 p_time_stamp ,
1307 'ADI' ,
1308 p_registration_number ,
1309 p_claimed_mileage ,
1310 p_vehicle_type ,
1311 l_canonical_st_dt ,
1312 l_canonical_ed_dt ,
1313 p_actual_mileage ,
1314 p_usage_type ,
1315 p_ownership ,
1316 l_businesss_group_id ,
1317 l_assignment_id
1318 );
1319 END IF;
1320 END;
1321
1322
1323 -------------------------------------------------------------------------
1324 ------------ --Procedure for deleteting the claim import ----------------
1325 --------------------------------------------------------------------------
1326 --Used to delete the imported claims from UI
1327 PROCEDURE delete_claim_import
1328 ( p_info_id IN VARCHAR2
1329 ,p_assignment_id IN NUMBER
1330 ,p_business_group_id IN NUMBER
1331 ,p_effective_date IN DATE
1332 ,p_return_status OUT NOCOPY VARCHAR2
1333 ) AS
1334 l_proc varchar2(72) := g_package ||'delete_claim_import';
1335 BEGIN
1336 hr_utility.set_location(' Enter delete claim import:' || l_proc,160);
1337 DELETE
1338 FROM pay_us_rpt_totals
1339 WHERE GRE_NAME = p_info_id
1340 AND business_group_id = p_business_group_id
1341 AND location_id = p_assignment_id
1342 AND STATE_ABBREV ='ADI';
1343 --Deleting all rows which are older than a day
1344 DELETE
1345 FROM pay_us_rpt_totals
1346 WHERE STATE_ABBREV ='ADI'
1347 AND (p_effective_date-fnd_date.canonical_to_date(state_name))>1;
1348
1349 COMMIT;
1350 p_return_status := 'S';
1351 hr_utility.set_location('Leaving delete mileage:' || l_proc,170);
1352 exception
1353 when others then
1354 p_return_status := 'E';
1355 END;
1356
1357 --------------------------------------------------------------------------
1358 --Call for updating the mileage claim
1359 PROCEDURE update_mileage_claim
1360 (
1361 p_effective_date IN DATE,
1362 p_assignment_id IN NUMBER,
1363 p_business_group_id IN NUMBER,
1364 p_ownership IN VARCHAR2 ,
1365 p_usage_type IN VARCHAR2 ,
1366 p_vehicle_type IN VARCHAR2,
1367 p_start_date_o IN VARCHAR2 ,
1368 p_start_date IN VARCHAR2 ,
1369 p_end_date_o IN VARCHAR2 ,
1370 p_end_date IN VARCHAR2 ,
1371 p_claimed_mileage_o IN VARCHAR2 ,
1372 p_claimed_mileage IN VARCHAR2 ,
1373 p_actual_mileage_o IN VARCHAR2 ,
1374 p_actual_mileage IN VARCHAR2 ,
1375 p_registration_number IN VARCHAR2 ,
1376 p_engine_capacity IN VARCHAR2 ,
1377 p_fuel_type IN VARCHAR2 ,
1378 p_calculation_method IN VARCHAR2 ,
1379 p_user_rates_table IN VARCHAR2 ,
1380 p_fiscal_ratings_o IN VARCHAR2 ,
1381 p_fiscal_ratings IN VARCHAR2 ,
1382 p_PAYE_taxable IN VARCHAR2 ,
1383 p_no_of_passengers_o IN VARCHAR2 ,
1384 p_no_of_passengers IN VARCHAR2 ,
1385 p_purpose IN VARCHAR2 ,
1386 p_data_source IN VARCHAR2 ,
1387 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
1388 p_element_entry_id IN OUT NOCOPY NUMBER ,
1389 p_element_entry_date IN OUT NOCOPY DATE
1390 ) IS
1391
1392 CURSOR c_get_input_value
1393 IS
1394 SELECT DISTINCT piv.input_value_id
1395 ,piv.name
1396 ,piv.lookup_type
1397 ,piv.default_value
1398 FROM pay_input_values_f piv
1399 WHERE piv.name IN ('Claimed Mileage'
1400 ,'Actual Mileage'
1401 ,'Claim Start Date'
1402 ,'Claim End Date'
1403 ,'No of Passengers'
1404 ,'CO2 Emissions'
1405 ,'User Rates Table'
1406 ,'Vehicle Type'
1407 ,'Rate Type'
1408 ,'PAYE Taxable'
1409 ,'Calculation Method'
1410 ,'Purpose'
1411 )
1412 AND piv.element_type_id=p_mileage_claim_element
1413 AND piv.business_group_id=p_business_group_id
1414 AND p_effective_date BETWEEN piv.effective_start_date
1415 AND piv.effective_end_date;
1416
1417 CURSOR c_get_table_name ( cp_user_rates_table VARCHAR2
1418 ,cp_business_group_id NUMBER
1419 )
1420 IS
1421 SELECT put.user_table_name
1422 FROM pay_user_tables put
1423 WHERE user_table_id =cp_user_rates_table
1424 AND put.business_group_id=cp_business_group_id;
1425
1426
1427 CURSOR c_get_end_date
1428 IS
1429 SELECT MAX(pee.effective_end_date) effective_end_date
1430 FROM pay_element_entries_f pee
1431 WHERE pee.element_entry_id=p_element_entry_id
1432 AND pee.assignment_id=p_assignment_id;
1433
1434 l_get_table_name c_get_table_name%ROWTYPE;
1435 l_delete_mode VARCHAR2(30) :='FUTURE_CHANGE';
1436 l_update_mode VARCHAR2(30) :='CORRECTION';
1437 l_input_value_id_tbl hr_entry.number_table;
1438 l_entry_value_tbl hr_entry.varchar2_table;
1439 l_num_entry_values NUMBER;
1440 l_get_input_value c_get_input_value%ROWTYPE;
1441 l_get_end_date c_get_end_date%ROWTYPE;
1442 l_proc varchar2(72) := g_package ||'update_mileage_claim';
1443 BEGIN
1444
1445 hr_utility.set_location('Enter update Claim:' || l_proc,10);
1446 OPEN c_get_end_date;
1447 LOOP
1448 FETCH c_get_end_date INTO l_get_end_date;
1449 EXIT WHEN c_get_end_date%NOTFOUND;
1450
1451 END LOOP;
1452 CLOSE c_get_end_date;
1453 hr_utility.set_location(l_proc,20);
1454 OPEN c_get_input_value;
1455 LOOP
1456 FETCH c_get_input_value INTO l_get_input_value;
1457 EXIT WHEN c_get_input_value%NOTFOUND;
1458 hr_utility.set_location('Enter set input values:' || l_proc,30);
1459 IF l_get_input_value.name='Vehicle Type'OR
1460 l_get_input_value.name='Rate Type' THEN
1461 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1462 :=l_get_input_value.input_value_id;
1463 l_entry_value_tbl(l_entry_value_tbl.count+1)
1464 :=get_lkp_meaning(p_usage_type
1465 ,l_get_input_value.lookup_type);
1466 ELSIF l_get_input_value.name='Claimed Mileage' THEN
1467 hr_utility.set_location(l_proc,40);
1468 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1469 :=l_get_input_value.input_value_id;
1470 l_entry_value_tbl(l_entry_value_tbl.count+1)
1471 :=p_claimed_mileage;
1472 ELSIF l_get_input_value.name='Actual Mileage' THEN
1473 hr_utility.set_location(l_proc,50);
1474 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1475 :=l_get_input_value.input_value_id;
1476
1477 l_entry_value_tbl(l_entry_value_tbl.count+1)
1478 :=p_actual_mileage;
1479 ELSIF l_get_input_value.name='Claim Start Date' THEN
1480 hr_utility.set_location(l_proc,60);
1481 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1482 :=l_get_input_value.input_value_id;
1483
1484 l_entry_value_tbl(l_entry_value_tbl.count+1)
1485 :=p_start_date;
1486 ELSIF l_get_input_value.name='Claim End Date' THEN
1487 hr_utility.set_location(l_proc,70);
1488 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1489 :=l_get_input_value.input_value_id;
1490
1491 l_entry_value_tbl(l_entry_value_tbl.count+1)
1492 :=p_end_date;
1493 ELSIF l_get_input_value.name='User Rates Table' THEN
1494 hr_utility.set_location(l_proc,80);
1495 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1496 :=l_get_input_value.input_value_id;
1497
1498
1499
1500 OPEN c_get_table_name (p_user_rates_table
1501 ,p_business_group_id
1502 );
1503 FETCH c_get_table_name INTO l_get_table_name;
1504
1505 CLOSE c_get_table_name;
1506
1507 IF l_get_table_name.user_table_name IS NULL THEN
1508
1509 hr_utility.set_location(l_proc,90);
1510 l_entry_value_tbl(l_entry_value_tbl.count+1)
1511 :=l_get_input_value.default_value;
1512 ELSE
1513
1514 hr_utility.set_location(l_proc,100);
1515 l_entry_value_tbl(l_entry_value_tbl.count+1)
1516 :=l_get_table_name.user_table_name;
1517
1518 END IF;
1519 ELSIF l_get_input_value.name='No of Passengers' THEN
1520 hr_utility.set_location(l_proc,110);
1521 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1522 :=l_get_input_value.input_value_id;
1523
1524 l_entry_value_tbl(l_entry_value_tbl.count+1)
1525 :=p_no_of_passengers;
1526 ELSIF l_get_input_value.name='CO2 Emissions' THEN
1527 hr_utility.set_location(l_proc,120);
1528 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1529 :=l_get_input_value.input_value_id;
1530
1531 l_entry_value_tbl(l_entry_value_tbl.count+1)
1532 :=p_fiscal_ratings;
1533 ELSIF l_get_input_value.name='Calculation Method' THEN
1534 hr_utility.set_location(l_proc,130);
1535 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1536 :=l_get_input_value.input_value_id;
1537
1538 IF p_calculation_method IS NULL THEN
1539 l_entry_value_tbl(l_entry_value_tbl.count+1)
1540 :=p_calculation_method;
1541 ELSE
1542 l_entry_value_tbl(l_entry_value_tbl.count+1)
1543 :=get_lkp_meaning(p_calculation_method
1544 ,l_get_input_value.lookup_type);
1545
1546 END IF;
1547 ELSIF l_get_input_value.name='PAYE Taxable' THEN
1548 hr_utility.set_location(l_proc,140);
1549 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1550 :=l_get_input_value.input_value_id;
1551
1552 IF l_get_input_value.lookup_type IS NOT NULL THEN
1553 l_entry_value_tbl(l_entry_value_tbl.count+1)
1554 :=get_lkp_meaning(p_PAYE_Taxable
1555 ,l_get_input_value.lookup_type);
1556 ELSE
1557 l_entry_value_tbl(l_entry_value_tbl.count+1):= p_PAYE_Taxable;
1558 END IF;
1559
1560 ELSIF l_get_input_value.name='Purpose' THEN
1561 hr_utility.set_location(l_proc,70);
1562 l_input_value_id_tbl(l_input_value_id_tbl.count +1)
1563 :=l_get_input_value.input_value_id;
1564
1565 l_entry_value_tbl(l_entry_value_tbl.count+1)
1566 :=p_purpose;
1567
1568 END IF;
1569
1570 l_num_entry_values :=l_input_value_id_tbl.count;
1571
1572 END LOOP;
1573 CLOSE c_get_input_value;
1574
1575 IF l_get_end_date.effective_end_date <> hr_api.g_eot
1576 THEN
1577 hr_utility.set_location(l_proc,150);
1578 IF p_claimed_mileage_o <> p_claimed_mileage OR
1579 p_actual_mileage_o <> p_actual_mileage THEN
1580 hr_utility.set_location(l_proc,160);
1581 hr_utility.set_location('Enter delete api:' || l_proc,200);
1582 hr_entry_api.delete_element_entry
1583 (
1584 p_dt_delete_mode =>l_delete_mode,
1585 p_session_date =>p_effective_date,
1586 p_element_entry_id =>p_element_entry_id
1587 );
1588 END IF;
1589
1590 END IF;
1591
1592 hr_utility.set_location(l_proc,170);
1593 hr_utility.set_location('Enter correction:' || l_proc,210);
1594 hr_entry_api.update_element_entry
1595 (
1596 p_dt_update_mode =>l_update_mode,
1597 p_session_date =>p_effective_date,
1598 p_element_entry_id =>p_element_entry_id,
1599 p_num_entry_values =>l_num_entry_values,
1600 p_input_value_id_tbl =>l_input_value_id_tbl,
1601 p_entry_value_tbl =>l_entry_value_tbl
1602 );
1603
1604 exception
1605 --------
1606 When others then
1607 hr_utility.set_location(l_proc,180);
1608 fnd_message.raise_error;
1609 hr_multi_message.end_validation_set;
1610
1611 END;
1612
1613 --Call for deleting the mileage claim
1614 PROCEDURE delete_mileage_claim
1615 ( p_effective_date IN DATE,
1616 p_assignment_id IN NUMBER,
1617 p_mileage_claim_element IN NUMBER ,
1618 p_element_entry_id IN NUMBER ,
1619 p_element_entry_date IN DATE
1620 ) IS
1621
1622 CURSOR c_get_end_date(cp_element_entry_id NUMBER
1623 ,cp_assignment_id NUMBER)
1624 IS
1625 SELECT MAX(pee.effective_end_date) effective_end_date
1626 FROM pay_element_entries_f pee
1627 WHERE pee.element_entry_id=cp_element_entry_id
1628 AND pee.assignment_id =cp_assignment_id;
1629
1630 CURSOR c_get_process_status (cp_assignment_id NUMBER
1631 ,cp_element_type_id NUMBER
1632 ,cp_element_entry_id NUMBER
1633 )
1634 IS
1635 SELECT prr.assignment_action_id
1636 FROM pay_run_results prr
1637 WHERE
1638 --prr.assignment_id =cp_assignment_id
1639 --AND
1640 prr.element_type_id=cp_element_type_id
1641 AND prr.source_id=cp_element_entry_id;
1642
1643 l_get_end_date c_get_end_date%ROWTYPE;
1644 l_get_process_status c_get_process_status%ROWTYPE;
1645
1646 l_proc varchar2(72) := g_package ||'delete_mileage_claim';
1647 BEGIN
1648
1649 hr_utility.set_location(l_proc,10);
1650 OPEN c_get_end_date (p_element_entry_id
1651 ,p_assignment_id
1652 );
1653 LOOP
1654 FETCH c_get_end_date INTO l_get_end_date;
1655 EXIT WHEN c_get_end_date%NOTFOUND;
1656 hr_utility.set_location(l_proc,20);
1657 IF l_get_end_date.effective_end_date =hr_api.g_eot THEN
1658
1659 OPEN c_get_process_status ( p_assignment_id
1660 ,p_mileage_claim_element
1661 ,p_element_entry_id
1662 );
1663 LOOP
1664 FETCH c_get_process_status INTO l_get_process_status;
1665 EXIT WHEN c_get_process_status%NOTFOUND;
1666 END LOOP;
1667 CLOSE c_get_process_status;
1668
1669 IF l_get_process_status.assignment_action_id IS NULL THEN
1670
1671 hr_utility.set_location(l_proc,30);
1672 hr_entry_api.delete_element_entry
1673 (
1674 p_dt_delete_mode =>'ZAP'
1675 ,p_session_date =>p_effective_date
1676 ,p_element_entry_id =>p_element_entry_id
1677 );
1678 ELSE
1679 hr_utility.set_location(l_proc,40);
1680 fnd_message.set_name('PQP','PQP_230718_CLM_PROC_DEL');
1681 fnd_message.raise_error;
1682 hr_multi_message.end_validation_set;
1683 END IF;
1684
1685 ELSE
1686
1687 hr_utility.set_location(l_proc,50);
1688 fnd_message.set_name('PQP','PQP_230718_CLM_PROC_DEL');
1689 fnd_message.raise_error;
1690 hr_multi_message.end_validation_set;
1691 END IF;
1692 END LOOP;
1693 exception
1694 --------
1695 When others then
1696 hr_utility.set_location(l_proc,60);
1697 fnd_message.raise_error;
1698 hr_multi_message.end_validation_set;
1699 END;
1700
1701 --get element or rates from configuration
1702 PROCEDURE get_config_info ( p_business_group_id IN NUMBER
1703 ,p_ownership IN VARCHAR2
1704 ,p_usage_type IN VARCHAR2
1705 ,p_vehicle_type IN VARCHAR2
1706 ,p_fuel_type IN VARCHAR2
1707 ,p_sl_rates_type IN VARCHAR2
1708 ,p_rates OUT NOCOPY NUMBER
1709 ,p_element_id OUT NOCOPY NUMBER
1710 )
1711 IS
1712 CURSOR c_get_config_rates_info
1713 IS
1714 SELECT pcv.pcv_information_category
1715 ,pcv.pcv_information1 Ownership
1716 ,pcv.pcv_information2 Usage_type
1717 ,pcv.pcv_information3 Vehicle_type
1718 ,pcv.pcv_information4 Fuel_type
1719 ,pcv.pcv_information5 rates_type
1720 FROM pqp_configuration_values pcv
1721 WHERE business_group_id=p_business_group_id
1722 AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
1723 AND pcv_information1=p_ownership
1724 AND (pcv_information2=p_usage_type
1725 OR pcv_information2 IS NULL)
1726 AND (pcv_information3=p_vehicle_type
1727 OR pcv_information3 IS NULL)
1728 AND (pcv_information4=p_fuel_type
1729 OR pcv_information4 IS NULL)
1730 AND pcv.pcv_information5 IS NOT NULL
1731 ORDER BY 1,2,3,4;
1732
1733 CURSOR c_get_config_element_info
1734 IS
1735 SELECT pcv.pcv_information_category
1736 ,pcv.pcv_information1 Ownership
1737 ,pcv.pcv_information2 Usage_type
1738 ,pcv.pcv_information3 Vehicle_type
1739 ,pcv.pcv_information4 Fuel_type
1740 ,pcv.pcv_information6 element_id
1741 FROM pqp_configuration_values pcv
1742 WHERE business_group_id=p_business_group_id
1743 AND pcv.pcv_information_category='GB_VEHICLE_CALC_INFO'
1744 AND pcv_information1=p_ownership
1745 AND (pcv_information2=p_usage_type
1746 OR pcv_information2 IS NULL)
1747 AND (pcv_information3=p_vehicle_type
1748 OR pcv_information3 IS NULL)
1749 AND (pcv_information4=p_fuel_type
1750 OR pcv_information4 IS NULL)
1751 AND pcv.pcv_information6 IS NOT NULL
1752 ORDER BY 1,2,3,4;
1753
1754 CURSOR c_ele_type (cp_element_type_id NUMBER)
1755 IS
1756 SELECT NVL(pete.eei_information2 ,'N') ele_type
1757 FROM pay_element_type_extra_info pete
1758 WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
1759 AND pete.element_type_id= cp_element_type_id;
1760
1761 l_ele_type c_ele_type%ROWTYPE;
1762 l_get_config_rates_info c_get_config_rates_info%ROWTYPE;
1763 l_get_config_element_info c_get_config_element_info%ROWTYPE;
1764 wrong_table EXCEPTION;
1765 BEGIN
1766
1767 OPEN c_get_config_rates_info;
1768 FETCH c_get_config_rates_info INTO l_get_config_rates_info;
1769 CLOSE c_get_config_rates_info;
1770
1771 IF p_sl_rates_type='Y' THEN
1772 BEGIN
1773 p_rates :=to_number(l_get_config_rates_info.rates_type) ;
1774 RAISE wrong_table;
1775
1776 EXCEPTION
1777 ---------
1778 WHEN wrong_table THEN
1779 p_rates:=NULL;
1780 WHEN OTHERS THEN
1781 p_rates :=l_get_config_rates_info.rates_type;
1782 END;
1783 ELSE
1784 BEGIN
1785 p_rates :=to_number(l_get_config_rates_info.rates_type) ;
1786
1787 EXCEPTION
1788 ---------
1789 WHEN OTHERS THEN
1790 p_rates :=NULL;
1791 END;
1792 END IF;
1793
1794 OPEN c_get_config_element_info;
1795 FETCH c_get_config_element_info INTO l_get_config_element_info;
1796
1797 OPEN c_ele_type (l_get_config_element_info.element_id);
1798 FETCH c_ele_type INTO l_ele_type;
1799 CLOSE c_ele_type;
1800 IF p_sl_rates_type=l_ele_type.ele_type THEN
1801 p_element_id :=l_get_config_element_info.element_id;
1802 ELSE
1803 p_element_id :=NULL;
1804 END IF;
1805 CLOSE c_get_config_element_info;
1806
1807 END;
1808 --chk if vehicle is active during the claim period
1809 FUNCTION chk_vehicle_active ( p_ownership IN VARCHAR2
1810 ,p_usage_type IN VARCHAR2
1811 ,p_assignment_id IN NUMBER
1812 ,p_business_group_id IN NUMBER
1813 ,p_start_date IN VARCHAR2
1814 ,p_end_date IN VARCHAR2
1815 ,p_registration_number IN VARCHAR2
1816 ,p_message OUT NOCOPY VARCHAR2
1817 )
1818 RETURN NUMBER
1819 IS
1820
1821 CURSOR c_get_reg_num
1822 IS
1823 SELECT pvr.registration_number
1824 ,pva.default_vehicle
1825 FROM pqp_vehicle_repository_f pvr
1826 ,pqp_vehicle_allocations_f pva
1827 WHERE pva.vehicle_repository_id=pvr.vehicle_repository_id
1828 AND pvr.vehicle_ownership=p_ownership
1829 AND pva.usage_type=p_usage_type
1830 AND pva.assignment_id=p_assignment_id
1831 AND pva.business_group_id=p_business_group_id
1832 AND pva.business_group_id=pvr.business_group_id
1833 AND p_start_date BETWEEN pva.effective_start_date
1834 AND pva.effective_end_date
1835 AND p_start_date BETWEEN pvr.effective_start_date
1836 AND pvr.effective_end_date ;
1837
1838 CURSOR c_chk_active (cp_registration_number VARCHAR2)
1839 IS
1840 SELECT pvr.vehicle_status,'Start_Date' clm_date
1841 FROM pqp_vehicle_repository_f pvr
1842 WHERE pvr.registration_number=cp_registration_number
1843 AND pvr.vehicle_status='I'
1844 AND p_start_date BETWEEN pvr.effective_start_date
1845 AND pvr.effective_end_date
1846 UNION
1847 SELECT pvr.vehicle_status,'End_Date' clm_date
1848 FROM pqp_vehicle_repository_f pvr
1849 WHERE pvr.registration_number=cp_registration_number
1850 AND pvr.vehicle_status='I'
1851 AND p_end_date BETWEEN pvr.effective_start_date
1852 AND pvr.effective_end_date;
1853
1854 CURSOR c_chk_alloc (cp_registration_number VARCHAR2)
1855 IS
1856 SELECT 'Start_Valid' valid_date
1857 FROM pqp_vehicle_allocations_f pva
1858 ,per_all_assignments_f paa
1859 ,pqp_vehicle_repository_f pvr
1860 WHERE pva.assignment_id=p_assignment_id
1861 AND pvr.registration_number=cp_registration_number
1862 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1863 AND pva.business_group_id=p_business_group_id
1864 AND pva.assignment_id =paa.assignment_id
1865 AND pva.business_group_id=paa.business_group_id
1866 AND pva.business_group_id=pvr.business_group_id
1867 AND p_start_date BETWEEN pva.effective_start_date
1868 AND pva.effective_end_date
1869 AND p_start_date BETWEEN paa.effective_start_date
1870 AND paa.effective_end_date
1871 AND p_start_date BETWEEN pvr.effective_start_date
1872 AND pvr.effective_end_date
1873 UNION
1874 SELECT 'End_Valid' valid_date
1875 FROM pqp_vehicle_allocations_f pva
1876 ,per_all_assignments_f paa
1877 ,pqp_vehicle_repository_f pvr
1878 WHERE pva.assignment_id=p_assignment_id
1879 AND pvr.registration_number=cp_registration_number
1880 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1881 AND pva.business_group_id=p_business_group_id
1882 AND pva.assignment_id =paa.assignment_id
1883 AND pva.business_group_id=paa.business_group_id
1884 AND pva.business_group_id=pvr.business_group_id
1885 AND p_end_date BETWEEN pva.effective_start_date
1886 AND pva.effective_end_date
1887 AND p_end_date BETWEEN paa.effective_start_date
1888 AND paa.effective_end_date
1889 AND p_end_date BETWEEN pvr.effective_start_date
1890 AND pvr.effective_end_date;
1891
1892
1893
1894 l_get_reg_num c_get_reg_num%ROWTYPE;
1895 l_chk_alloc c_chk_alloc%ROWTYPE;
1896 l_chk_active c_chk_active%ROWTYPE;
1897 l_st_date VARCHAR2(10);
1898 l_end_date VARCHAR2(10);
1899 BEGIN
1900 IF p_registration_number IS NULL THEN
1901 OPEN c_get_reg_num;
1902 LOOP
1903 FETCH c_get_reg_num INTO l_get_reg_num;
1904 EXIT WHEN c_get_reg_num%NOTFOUND;
1905 END LOOP;
1906 CLOSE c_get_reg_num;
1907
1908 END IF;
1909 OPEN c_chk_active (NVL(p_registration_number,
1910 l_get_reg_num.registration_number));
1911 LOOP
1912 FETCH c_chk_active INTO l_chk_active;
1913 EXIT WHEN c_chk_active%NOTFOUND;
1914 IF l_chk_active.clm_date='Start_Date' THEN
1915 l_st_date:=l_chk_active.clm_date;
1916 ELSIF l_chk_active.clm_date='End_Date' THEN
1917
1918 l_end_date:=l_chk_active.clm_date;
1919
1920 END IF;
1921
1922 END LOOP;
1923 CLOSE c_chk_active;
1924 IF l_st_date IS NOT NULL AND
1925 l_end_date IS NOT NULL THEN
1926
1927 fnd_message.raise_error;
1928
1929 ELSIF l_st_date IS NOT NULL AND
1930 l_end_date IS NULL THEN
1931
1932 fnd_message.raise_error;
1933 ELSIF l_st_date IS NULL AND
1934 l_end_date IS NOT NULL THEN
1935
1936 fnd_message.raise_error;
1937
1938 END IF;
1939
1940 END;
1941
1942 --Check for Mandatory columns
1943 FUNCTION chk_mndtry_fields ( p_effective_date IN DATE
1944 ,p_assignment_id IN NUMBER
1945 ,p_business_group_id IN NUMBER
1946 ,p_ownership IN VARCHAR2
1947 ,p_usage_type IN VARCHAR2
1948 ,p_vehicle_type IN VARCHAR2
1949 ,p_start_date IN VARCHAR2
1950 ,p_end_date IN VARCHAR2
1951 ,p_claimed_mileage IN VARCHAR2
1952 ,p_actual_mileage IN VARCHAR2
1953 ,p_registration_number IN VARCHAR2
1954 ,p_engine_capacity IN VARCHAR2
1955 ,p_fuel_type IN VARCHAR2
1956 ,p_element_type_id IN NUMBER
1957 ,p_data_source IN VARCHAR2
1958 ,p_message OUT NOCOPY VARCHAR2
1959 )
1960
1961 RETURN VARCHAR2
1962 AS
1963 CURSOR c_get_veh_det
1964 IS
1965 SELECT pvr.vehicle_type
1966 ,pvr.vehicle_ownership
1967 ,pvr.fiscal_ratings
1968 ,pvr.engine_capacity_in_cc
1969 ,pvr.fuel_type
1970 FROM pqp_vehicle_repository_f pvr
1971 WHERE pvr.registration_number = p_registration_number
1972 AND pvr.business_group_id = p_business_group_id
1973 AND p_effective_date BETWEEN pvr.effective_start_date
1974 AND pvr.effective_end_date;
1975
1976 CURSOR c_validate_comp_veh
1977 IS
1978 SELECT pvr.vehicle_type
1979 ,pvr.vehicle_ownership
1980 ,pvr.fiscal_ratings
1981 ,pvr.engine_capacity_in_cc
1982 ,pvr.fuel_type
1983 FROM pqp_vehicle_repository_f pvr
1984 ,pqp_vehicle_allocations_f pva
1985 WHERE (pvr.registration_number =p_registration_number
1986 OR p_registration_number IS NULL)
1987 AND pva.assignment_id= p_assignment_id
1988 AND pvr.business_group_id = p_business_group_id
1989 AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1990 AND pvr.business_group_id=pva.business_group_id
1991 AND FND_DATE.CHARDATE_TO_DATE(p_start_date) BETWEEN pvr.effective_start_date
1992 AND pvr.effective_end_date
1993 AND pva.usage_type IN ('P','S');
1994
1995 l_validate_comp_veh c_validate_comp_veh%ROWTYPE;
1996 l_get_veh_det c_get_veh_det%ROWTYPE;
1997 l_retvalue VARCHAR2(1) :='Y';
1998 l_proc varchar2(72) := g_package ||'chk_mndtry_fields';
1999 BEGIN
2000
2001 hr_utility.set_location('Enter ' || l_proc,10);
2002 IF p_registration_number IS NOT NULL THEN
2003 OPEN c_get_veh_det;
2004 FETCH c_get_veh_det INTO l_get_veh_det;
2005 IF c_get_veh_det%FOUND THEN
2006 hr_utility.set_location( l_proc,20);
2007 IF l_get_veh_det.vehicle_type<>p_vehicle_type THEN
2008 hr_utility.set_location( l_proc,30);
2009 fnd_message.set_name('PQP', 'PQP_230859_VEHICLE_TYP_VALIDAT');
2010 l_retvalue :='N';
2011 END IF;
2012 IF l_get_veh_det.engine_capacity_in_cc
2013 <>p_engine_capacity THEN
2014 hr_utility.set_location( l_proc,40);
2015 fnd_message.set_name('PQP', 'PQP_230860_ENGINE_CAP_VALIDAT');
2016 l_retvalue :='N';
2017
2018 END IF;
2019 IF l_get_veh_det.fuel_type <>p_fuel_type THEN
2020 hr_utility.set_location( l_proc,50);
2021 fnd_message.set_name('PQP', 'PQP_230861_FUEL_TYP_VALIDAT');
2022 l_retvalue :='N';
2023 END IF;
2024
2025
2026 END IF;
2027 CLOSE c_get_veh_det;
2028 END IF;
2029 --Check if the an assignemnt has company vehicle
2030 --allocated
2031 IF p_ownership='C' THEN
2032
2033 hr_utility.set_location( l_proc,60);
2034 OPEN c_validate_comp_veh;
2035 FETCH c_validate_comp_veh INTO l_validate_comp_veh;
2036 IF c_validate_comp_veh%NOTFOUND THEN
2037
2038 hr_utility.set_location( l_proc,70);
2039 fnd_message.set_name('PQP', 'PQP_230866_COMP_VEH_NOT_ALLOC');
2040 l_retvalue :='N';
2041 END IF;
2042
2043 CLOSE c_validate_comp_veh;
2044
2045 END IF;
2046 IF p_ownership IS NULL THEN
2047 hr_utility.set_location( l_proc,80);
2048 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2049 fnd_message.set_token('TOKEN','Ownership');
2050 l_retvalue :='N';
2051 END IF;
2052
2053 --Commented out this now because this condition is no longer
2054 --required as the code handles this during element entry.
2055 /*IF p_usage_type IS NULL THEN
2056 hr_utility.set_location( l_proc,90);
2057 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2058 fnd_message.set_token('TOKEN','Usage Type');
2059 l_retvalue :='N';
2060
2061 END IF;*/
2062
2063 IF p_vehicle_type IS NULL THEN
2064 hr_utility.set_location( l_proc,100);
2065 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2066 fnd_message.set_token('TOKEN','Vehicle Type');
2067 l_retvalue :='N';
2068
2069 END IF;
2070
2071
2072 IF p_start_date IS NULL THEN
2073 hr_utility.set_location( l_proc,110);
2074 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2075 fnd_message.set_token('TOKEN','Start Date');
2076 l_retvalue :='N';
2077
2078 END IF;
2079
2080 IF p_end_date IS NULL THEN
2081 hr_utility.set_location( l_proc,120);
2082 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2083 fnd_message.set_token('TOKEN','End Date');
2084
2085 l_retvalue :='N';
2086 END IF;
2087 IF p_claimed_mileage IS NULL THEN
2088 hr_utility.set_location( l_proc,130);
2089 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2090 fnd_message.set_token('TOKEN','Claimed Mileage');
2091 l_retvalue :='N';
2092
2093 END IF;
2094
2095
2096 IF p_engine_capacity IS NULL THEN
2097 hr_utility.set_location( l_proc,140);
2098 fnd_message.set_name('PQP', 'PQP_230734_FLD_MANDTRY');
2099 fnd_message.set_token('TOKEN','Engine Capacity');
2100 l_retvalue :='N';
2101 END IF;
2102
2103 IF p_element_type_id IS NULL THEN
2104 hr_utility.set_location( l_proc,150);
2105
2106 fnd_message.set_name('PQP', 'PQP_230732_VLD_MLG_ELE_FAIL');
2107 l_retvalue :='N';
2108 END IF;
2109
2110 IF FND_DATE.CHARDT_TO_DATE(p_end_date) <
2111 FND_DATE.CHARDT_TO_DATE(p_start_date) THEN
2112 hr_utility.set_location( l_proc,160);
2113 fnd_message.set_name('PER','HR_289262_ST_DATE_BEFORE_EDATE');
2114 l_retvalue :='N';
2115 END IF;
2116
2117 --RETURN('Y');
2118 RETURN (l_retvalue);
2119 hr_utility.set_location( 'Leaving :'||l_proc,170);
2120 END;
2121
2122 --Check for eligibility
2123 FUNCTION chk_eligibility ( p_effective_date IN DATE
2124 ,p_assignment_id IN NUMBER
2125 ,p_business_group_id IN NUMBER
2126 ,p_ownership IN VARCHAR2
2127 ,p_usage_type IN VARCHAR2
2128 ,p_vehicle_type IN VARCHAR2
2129 ,p_start_date IN VARCHAR2
2130 ,p_end_date IN VARCHAR2
2131 ,p_claimed_mileage IN VARCHAR2
2132 ,p_actual_mileage IN VARCHAR2
2133 ,p_registration_number IN VARCHAR2
2134 ,p_data_source IN VARCHAR2
2135 ,p_message OUT NOCOPY VARCHAR2
2136 )
2137
2138 RETURN VARCHAR2
2139 IS
2140
2141 --get info from config table to chk
2142 --for eligibility.
2143 l_retvalue VARCHAR2(1) :='Y';
2144 CURSOR c_get_config_info (cp_leg_code VARCHAR2)
2145 IS
2146 SELECT pcv_information6 prev_tax_yr_valid
2147 ,pcv_information7 allow_both_veh_clm
2148 ,pcv_information9 validate_pvt_veh
2149 FROM pqp_configuration_values pcv
2150 WHERE pcv.legislation_code=cp_leg_code
2151 AND pcv.pcv_information_category='PQP_VEHICLE_MILEAGE';
2152
2153 --Check the status of vehicle
2154 CURSOR c_validate_veh ( cp_reg_num VARCHAR2
2155 ,cp_ownership VARCHAR2
2156 ,cp_business_group_id VARCHAR2
2157 ,cp_start_date VARCHAR2
2158 )
2159 IS
2160 SELECT 'X' exst
2161 FROM pqp_vehicle_repository_f pvr
2162 WHERE pvr.registration_number=cp_reg_num
2163 AND pvr.business_group_id =cp_business_group_id
2164 AND TO_DATE(cp_start_date,'YYYY/MM/DD') BETWEEN
2165 pvr.effective_start_date AND
2166 pvr.effective_end_date;
2167
2168 CURSOR c_get_alloc_info ( cp_assignment_id NUMBER
2169 ,cp_ownership VARCHAR2
2170 ,cp_business_group_id VARCHAR2
2171 ,cp_start_date VARCHAR2
2172 )
2173 IS
2174 SELECT DISTINCT pvr.vehicle_ownership ownership
2175 FROM pqp_vehicle_allocations_f pva,
2176 pqp_vehicle_repository_f pvr
2177 WHERE pva.assignment_id =cp_assignment_id
2178 AND pva.business_group_id =cp_business_group_id
2179 AND pva.vehicle_repository_id=pvr.vehicle_repository_id
2180 AND pva.business_group_id=pvr.business_group_id
2181 AND fnd_date.CHARDATE_TO_DATE(cp_start_date) BETWEEN
2182 pva.effective_start_date AND
2183 pva.effective_end_date
2184 AND fnd_date.CHARDATE_TO_DATE(cp_start_date) BETWEEN
2185 pvr.effective_start_date AND
2186 pvr.effective_end_date
2187 AND pvr.vehicle_ownership='C';
2188
2189 l_get_alloc_info c_get_alloc_info%ROWTYPE;
2190 l_validate_veh c_validate_veh%ROWTYPE;
2191 l_get_config_info c_get_config_info%ROWTYPE;
2192
2193 l_proc varchar2(72) := g_package ||'check_eligibility';
2194 BEGIN
2195 hr_utility.set_location('Enter chk eligibility:' || l_proc,220);
2196 OPEN c_get_config_info('GB'
2197 );
2198 LOOP
2199 FETCH c_get_config_info INTO l_get_config_info;
2200 EXIT WHEN c_get_config_info%NOTFOUND;
2201
2202 END LOOP;
2203 CLOSE c_get_config_info;
2204
2205 --Check if the claim is made for last tax
2206 --year after the cut off date.
2207 IF l_get_config_info.prev_tax_yr_valid IS NOT NULL THEN
2208 IF fnd_date.CHARDATE_TO_DATE(p_start_date) <
2209 TO_DATE(TO_CHAR
2210 (p_effective_date, 'YYYY')||'04/06','YYYY/MM/DD')
2211 THEN
2212 --checking the effective date is greater than previous tax year sumit
2213 --valid untill date,so user cannot sumit claim after previous tax year
2214 IF p_effective_date > fnd_date.CHARDATE_TO_DATE
2215 (l_get_config_info.prev_tax_yr_valid||
2216 TO_CHAR(p_effective_date,'YYYY'))
2217 THEN
2218 --Error handling missing
2219 fnd_message.set_name('PQP', 'PQP_230715_CLM_CUT_OFF_DT');
2220 p_message :='Violated Valid Tax Year';
2221
2222 l_retvalue :='N';
2223 END IF;
2224 END IF;
2225 END IF;
2226
2227 hr_utility.set_location('leave chk eligibility:' || l_proc,230);
2228
2229 --Check if pvt vehicle need to be validated against
2230 --repository.
2231 IF l_get_config_info.validate_pvt_veh ='Y' THEN
2232
2233 OPEN c_validate_veh (p_registration_number
2234 ,p_ownership
2235 ,p_business_group_id
2236 ,p_start_date
2237 );
2238 LOOP
2239 FETCH c_validate_veh INTO l_validate_veh;
2240 EXIT WHEN c_validate_veh%NOTFOUND;
2241 END LOOP;
2242 CLOSE c_validate_veh;
2243
2244 IF l_validate_veh.exst IS NULL THEN
2245 fnd_message.set_name('PQP', 'PQP_230735_REGNUM_FRM_REP');
2246 p_message:='Enter Only Vehicle in Repository';
2247
2248 l_retvalue :='N';
2249 END IF;
2250 END IF;
2251
2252 --Check if the claim can be entered for both.
2253 IF l_get_config_info.allow_both_veh_clm ='N' THEN
2254 OPEN c_get_alloc_info ( p_assignment_id
2255 ,p_ownership
2256 ,p_business_group_id
2257 ,p_start_date
2258 );
2259 LOOP
2260 FETCH c_get_alloc_info INTO l_get_alloc_info;
2261 EXIT WHEN c_get_alloc_info%NOTFOUND;
2262
2263 END LOOP;
2264 CLOSE c_get_alloc_info;
2265 IF l_get_alloc_info.ownership<>p_ownership THEN
2266 fnd_message.set_name('PQP', 'PQP_230740_ONE_OWNRSHP_RSTRICT');
2267 p_message :='Enter Only one type of Claim';
2268 l_retvalue :='N';
2269 END IF;
2270 END IF;
2271 --RETURN('Y');
2272 RETURN( l_retvalue);
2273 END;
2274
2275
2276
2277 --Check for same record
2278 FUNCTION chk_record_exist ( p_effective_date IN DATE
2279 ,p_assignment_id IN NUMBER
2280 ,p_business_group_id IN NUMBER
2281 ,p_ownership IN VARCHAR2
2282 ,p_usage_type IN VARCHAR2
2283 ,p_vehicle_type IN VARCHAR2
2284 ,p_start_date IN VARCHAR2
2285 ,p_end_date IN VARCHAR2
2286 ,p_claimed_mileage IN VARCHAR2
2287 ,p_actual_mileage IN VARCHAR2
2288 ,p_registration_number IN VARCHAR2
2289 ,p_data_source IN VARCHAR2
2290 )
2291 RETURN VARCHAR2
2292 IS
2293
2294 CURSOR c_get_ele_details ( cp_vehicle_type VARCHAR2
2295 ,cp_business_group_id NUMBER
2296 )
2297
2298 IS
2299 SELECT pet.element_type_id
2300 ,pel.element_link_id
2301 FROM pay_element_types_f pet
2302 ,pay_element_type_extra_info pete
2303 ,pay_element_links_f pel
2304 WHERE pete.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
2305 AND pet.element_type_id=pete.element_type_id
2306 AND pete.element_type_id=pel.element_type_id
2307 AND pete.eei_information1=cp_vehicle_type
2308 AND pet.business_group_id=pel.business_group_id
2309 AND pet.business_group_id=cp_business_group_id
2310 AND pet.element_type_id=pel.element_type_id;
2311
2312
2313 CURSOR c_get_input_val (cp_element_type_id NUMBER
2314 ,cp_business_group_id NUMBER
2315 )
2316 IS
2317 SELECT piv.element_type_id
2318 ,piv.input_value_id
2319 ,piv.name
2320 FROM pay_input_values_f piv
2321 WHERE piv.name in ('Claim Start Date','Claim End Date')
2322 AND piv.element_type_id=cp_element_type_id
2323 and piv.business_group_id=cp_business_group_id;
2324
2325
2326
2327
2328 CURSOR c_get_date_exist ( cp_assignment_id NUMBER
2329 ,cp_start_date DATE
2330 ,cp_end_date DATE
2331 ,cp_ipvalue1 NUMBER
2332 ,cp_ipvalue2 NUMBER
2333 )
2334 IS
2335 SELECT pee.assignment_id
2336 ,pev1.screen_entry_value scr1
2337 ,pev2.screen_entry_value scr2
2338 ,pev1.element_entry_id
2339 FROM pay_element_entries_f pee
2340 ,pay_element_entry_values_f pev1
2341 ,pay_element_entry_values_f pev2
2342 WHERE pee.assignment_id=cp_assignment_id
2343 AND pee.element_entry_id=pev1.element_entry_id
2344 AND pee.element_entry_id=pev2.element_entry_id
2345 AND pev1.element_entry_id=pev2.element_entry_id
2346 AND pev1.input_value_id =cp_ipvalue1
2347 AND pev2.input_value_id=cp_ipvalue2
2348 AND pev1.screen_entry_value =
2349 fnd_date.DATE_TO_CANONICAL(cp_start_date)
2350 AND pev2.SCREEN_ENTRY_VALUE =
2351 fnd_date.DATE_TO_CANONICAL(cp_end_date);
2352
2353
2354 l_get_ele_details c_get_ele_details%ROWTYPE;
2355 l_get_input_val c_get_input_val%ROWTYPE;
2356 l_get_date_exist c_get_date_exist%ROWTYPE;
2357 l_input_val1 NUMBER;
2358 l_input_val2 NUMBER;
2359 l_exist VARCHAR2(1):='N';
2360 l_start_date DATE;
2361 l_end_date DATE;
2362 BEGIN
2363 l_start_date:= fnd_date.chardt_to_date (p_start_date);
2364 l_end_date :=fnd_date.chardt_to_date(p_end_date);
2365 OPEN c_get_ele_details( p_vehicle_type
2366 ,p_business_group_id
2367 );
2368 LOOP
2369 FETCH c_get_ele_details INTO l_get_ele_details;
2370 EXIT WHEN c_get_ele_details%NOTFOUND;
2371 OPEN c_get_input_val (l_get_ele_details.element_type_id
2372 ,p_business_group_id
2373 );
2374 LOOP
2375 FETCH c_get_input_val INTO l_get_input_val;
2376 EXIT WHEN c_get_input_val%NOTFOUND;
2377
2378 IF l_get_input_val.NAME='Claim Start Date' THEN
2379 l_input_val1:=l_get_input_val.input_value_id;
2380 ELSIF l_get_input_val.NAME='Claim End Date' THEN
2381 l_input_val1:=l_get_input_val.input_value_id;
2382 END IF;
2383 END LOOP;
2384 CLOSE c_get_input_val;
2385 OPEN c_get_date_exist ( p_assignment_id
2386 ,l_start_date
2387 ,l_end_date
2388 ,l_input_val1
2389 ,l_input_val2
2390 );
2391 LOOP
2392 FETCH c_get_date_exist INTO l_get_date_exist;
2393 EXIT WHEN c_get_date_exist%NOTFOUND;
2394
2395 l_exist :='Y';
2396 END LOOP;
2397 CLOSE c_get_date_exist;
2398 END LOOP;
2399 CLOSE c_get_ele_details;
2400
2401 RETURN(l_exist);
2402
2403 END;
2404
2405 ---Called from JDEV ----
2406 --
2407 -- Function get_code returns the code of the meaning passed
2408 --
2409 -- The Code depends on the value of the p_option parameter
2410 -- p_option = 'R' -> p_field has the rates table name
2411 --and it Returns the Rates table id
2412 --
2413 FUNCTION get_code
2414 (p_option IN VARCHAR2
2415 ,p_field IN VARCHAR2
2416 ) RETURN VARCHAR2
2417 IS
2418
2419 --
2420 -- Cursor to fetch the Rate Table id given the rates table name
2421 --
2422 CURSOR c_get_rates_table_id
2423 IS
2424 select user_table_id
2425 from pay_user_tables
2426 where range_or_match = 'M'
2427 and user_table_name = p_field;
2428
2429 l_field varchar2(100);
2430 BEGIN
2431
2432 IF (p_field IS NULL) THEN
2433 RETURN null;
2434 END IF;
2435 IF (p_option = 'R') THEN
2436 OPEN c_get_rates_table_id;
2437 FETCH c_get_rates_table_id INTO l_field;
2438 CLOSE c_get_rates_table_id;
2439 END IF;
2440 RETURN l_field;
2441 END get_code;
2442
2443 --
2444 -- Function get_meaning returns the meaning string of the id passed
2445 --
2446 -- The Meaning depends on the value of the p_option parameter
2447 -- p_option = 'R' -> p_field_id has the rates table id
2448 --and it Returns the Rates table Name
2449 -- p_option = 'E' -> p_field_id has the element type id
2450 --and it Returns the Element Name
2451 --
2452 FUNCTION get_meaning
2453 (p_option IN VARCHAR2
2454 ,p_field_id IN NUMBER
2455 ) RETURN VARCHAR2
2456 IS
2457
2458 --
2459 -- Cursor to fetch the Element Name given the element type id
2460 --
2461 CURSOR c_get_element_name
2462 IS
2463 select element_name
2464 from pay_element_types_f_tl
2465 where element_type_id = p_field_id;
2466
2467 --
2468 -- Cursor to fetch the Rates Table Name given the rates table id
2469 --
2470 CURSOR c_get_rates_table_name
2471 IS
2472 select user_table_name
2473 from pay_user_tables
2474 where user_table_id = p_field_id;
2475
2476 CURSOR c_get_purpose_name
2477 IS
2478 SELECT pur.row_low_range_or_name
2479 FROM pay_user_tables put
2480 ,pay_user_rows_f pur
2481 WHERE put.range_or_match = 'M'
2482 AND put.user_table_name ='PQP_TRAVEL_PURPOSE'
2483 AND put.user_table_id = pur.user_table_id
2484 AND pur.user_row_id= p_field_id;
2485
2486 l_field_meaning varchar2(100);
2487 l_sliding_rates VARCHAR2(10);
2488 l_get_purpose_name c_get_purpose_name%ROWTYPE;
2489 BEGIN
2490 IF (p_field_id IS NULL) THEN
2491 RETURN NULL;
2492 END IF;
2493 IF (p_option = 'R') THEN
2494 OPEN c_get_rates_table_name;
2495 FETCH c_get_rates_table_name INTO l_field_meaning;
2496 CLOSE c_get_rates_table_name;
2497 ELSIF (p_option = 'E') THEN
2498 OPEN c_get_element_name;
2499 FETCH c_get_element_name INTO l_field_meaning;
2500 CLOSE c_get_element_name;
2501 END IF;
2502 RETURN l_field_meaning;
2503 END get_meaning;
2504 ----------------------------------
2505
2506 ----------Generic Procedures
2507
2508 FUNCTION get_lkp_meaning (p_lookup_code IN VARCHAR2,
2509 p_lookup_type IN VARCHAR2
2510 )
2511 RETURN VARCHAR2
2512 AS
2513
2514 CURSOR c_get_lkp_meaning
2515 IS
2516 SELECT hl.meaning
2517 FROM hr_lookups hl
2518 WHERE hl.lookup_type=p_lookup_type
2519 AND hl.lookup_code=p_lookup_code;
2520
2521
2522 l_get_lkp_meaning c_get_lkp_meaning%ROWTYPE;
2523
2524 BEGIN
2525
2526 OPEN c_get_lkp_meaning;
2527 LOOP
2528 FETCH c_get_lkp_meaning INTO l_get_lkp_meaning;
2529 EXIT WHEN c_get_lkp_meaning%NOTFOUND;
2530 END LOOP;
2531 CLOSE c_get_lkp_meaning;
2532
2533 RETURN (l_get_lkp_meaning.meaning);
2534
2535 END;
2536 --Get lookup meaning
2537 FUNCTION get_meaning ( p_inp_type VARCHAR2
2538 ,p_code VARCHAR2
2539 )
2540 RETURN VARCHAR2
2541 AS
2542
2543 CURSOR c_get_meaning_u
2544 IS
2545 SELECT lkp.lookup_code
2546 ,lkp.meaning
2547 FROM hr_lookups lkp
2548 WHERE lkp.lookup_type IN ('PQP_PRIVATE_VEHICLE_USER'
2549 ,'PQP_COMPANY_VEHICLE_USER')
2550 AND lkp.lookup_code=p_code;
2551 --AND lkp.application_id=8303
2552
2553
2554 CURSOR c_get_meaning_o
2555 IS
2556 SELECT lkp.lookup_code
2557 ,lkp.meaning
2558 FROM hr_lookups lkp
2559 WHERE lookup_type in ('PQP_VEHICLE_OWNERSHIP_TYPE')
2560 AND lkp.lookup_code=p_code;
2561 --AND lkp.application_id=8303
2562
2563 CURSOR c_get_meaning_f
2564 IS
2565 select meaning
2566 from hr_lookups
2567 where lookup_type = 'PQP_FUEL_TYPE'
2568 and enabled_flag = 'Y'
2569 and lookup_code = p_code;
2570
2571 CURSOR c_get_meaning_cm
2572 IS
2573 select meaning
2574 from hr_lookups
2575 where lookup_type = 'PQP_VEHICLE_CALC_METHOD'
2576 and enabled_flag='Y'
2577 and lookup_code = p_code;
2578
2579 CURSOR c_get_meaning_vt
2580 IS
2581 select hl.meaning
2582 from pqp_vehicle_repository_f pvr,
2583 hr_lookups hl
2584 where pvr.registration_number = p_code
2585 and hl.lookup_type = 'PQP_VEHICLE_TYPE'
2586 and hl.enabled_flag = 'Y'
2587 and hl.lookup_code = pvr.vehicle_type;
2588
2589 CURSOR c_get_meaning_vehtype
2590 IS
2591 select hl.meaning
2592 from hr_lookups hl
2593 where hl.lookup_type = 'PQP_VEHICLE_TYPE'
2594 and hl.enabled_flag = 'Y'
2595 and hl.lookup_code = p_code;
2596
2597
2598 CURSOR c_get_purpose_name
2599 IS
2600 SELECT pur.row_low_range_or_name
2601 FROM pay_user_tables put
2602 ,pay_user_rows_f pur
2603 WHERE put.range_or_match = 'M'
2604 AND put.user_table_name ='PQP_TRAVEL_PURPOSE'
2605 AND put.user_table_id = pur.user_table_id
2606 AND pur.user_row_id= p_code;
2607
2608
2609 l_get_meaning_u c_get_meaning_u%ROWTYPE;
2610 l_get_meaning_o c_get_meaning_o%ROWTYPE;
2611 l_meaning hr_lookups.meaning%TYPE;
2612
2613 l_proc varchar2(72) := g_package ||'get_meaning';
2614 BEGIN
2615
2616 hr_utility.set_location('enter get meaning:' || l_proc,240);
2617 IF p_inp_type = 'EI' THEN
2618 OPEN c_get_meaning_o;
2619 LOOP
2620 FETCH c_get_meaning_o INTO l_get_meaning_o;
2621 EXIT WHEN c_get_meaning_o%NOTFOUND;
2622 RETURN(l_get_meaning_o.meaning);
2623 END LOOP;
2624 CLOSE c_get_meaning_o;
2625
2626 IF l_get_meaning_o.meaning IS NULL THEN
2627 RETURN(p_code);
2628 END IF;
2629 ELSIF p_inp_type = 'Rate Type' THEN
2630 OPEN c_get_meaning_u;
2631 LOOP
2632 FETCH c_get_meaning_u INTO l_get_meaning_u;
2633 EXIT WHEN c_get_meaning_u%NOTFOUND;
2634 RETURN (l_get_meaning_u.meaning);
2635 END LOOP;
2636 CLOSE c_get_meaning_u;
2637
2638
2639 IF l_get_meaning_u.meaning IS NULL THEN
2640 RETURN(p_code);
2641 END IF;
2642 --RETURN('NONE');
2643
2644 -- Gets the Vehicle Type for the given Reg. No.
2645 ELSIF p_inp_type = 'VT' THEN
2646 OPEN c_get_meaning_vt;
2647 FETCH c_get_meaning_vt INTO l_meaning;
2648 CLOSE c_get_meaning_vt;
2649 RETURN(l_meaning);
2650 -- Gets the Vehicle Type for the given VehType Code
2651 ELSIF p_inp_type = 'V' THEN
2652 OPEN c_get_meaning_vehtype;
2653 FETCH c_get_meaning_vehtype INTO l_meaning;
2654 CLOSE c_get_meaning_vehtype;
2655 RETURN(l_meaning);
2656 -- Gets the Fuel Type meaning given the code
2657 ELSIF p_inp_type = 'Fuel Type' THEN
2658 OPEN c_get_meaning_f;
2659 FETCH c_get_meaning_f INTO l_meaning;
2660 CLOSE c_get_meaning_f;
2661 RETURN(l_meaning);
2662
2663 -- Gets the Calculation Method meaning given the code
2664 ELSIF p_inp_type = 'CM' THEN
2665 OPEN c_get_meaning_cm;
2666 FETCH c_get_meaning_cm INTO l_meaning;
2667 CLOSE c_get_meaning_cm;
2668 RETURN(l_meaning);
2669
2670 ELSIF p_inp_type='Purpose' THEN
2671 OPEN c_get_purpose_name;
2672 FETCH c_get_purpose_name INTO l_meaning;
2673 CLOSE c_get_purpose_name;
2674 RETURN(l_meaning);
2675 END IF;
2676 hr_utility.set_location('Leaving get meaning:' || l_proc,250);
2677 exception
2678 when others then
2679 return(p_code);
2680 END;
2681
2682
2683 --This func is temporary
2684 FUNCTION get_total ( p_element_name IN VARCHAR2
2685 ,p_assignment_action_id IN NUMBER
2686 ,p_element_entry_id IN NUMBER
2687 ,p_business_group_id IN NUMBER
2688 )
2689 return NUMBER
2690 IS
2691 CURSOR c_get_balance_name
2692 IS
2693 SELECT balance_type_id ,pbd.balance_dimension_id
2694 FROM pay_balance_types pbt
2695 ,pay_balance_dimensions pbd
2696 WHERE balance_name = p_element_name||' Processed Amt'
2697 AND pbd.legislation_code='GB'
2698 AND pbd.dimension_name='_ELEMENT_ITD';
2699
2700 --This cursor fetches the balance when the option
2701 --is PAYE Taxable is YES.
2702 CURSOR c_get_tax_element_type_id
2703 IS
2704 SELECT DISTINCT pet.element_type_id
2705 FROM pay_element_types_f pet
2706 WHERE pet.element_name = p_element_name||' Taxable'
2707 AND pet.business_group_id=p_business_group_id ;
2708
2709 CURSOR c_get_gross_pay_bal_name
2710 IS
2711 SELECT balance_type_id
2712 FROM pay_balance_types pbt
2713 WHERE balance_name = 'Gross Pay'
2714 AND pbt.legislation_code='GB' ;
2715
2716
2717 CURSOR c_get_def_balance ( cp_balance_typ_id NUMBER
2718 ,cp_balance_dim_id NUMBER
2719 )
2720 IS
2721 SELECT pdb.defined_balance_id
2722 from pay_defined_balances pdb
2723 where pdb.balance_type_id =cp_balance_typ_id
2724 and pdb.balance_dimension_id=cp_balance_dim_id;
2725
2726 cursor c1(cp_balance_type_id NUMBER
2727 ,cp_assignment_action_id NUMBER
2728 ,cp_element_entry_id NUMBER)
2729 is
2730 SELECT nvl(SUM(fnd_number.canonical_to_number(TARGET.result_value)
2731 * FEED.scale),0) tot
2732 FROM pay_run_result_values TARGET
2733 , pay_balance_feeds_f FEED
2734 , pay_run_results RR
2735 , pay_assignment_actions ASSACT
2736 , pay_assignment_actions BAL_ASSACT
2737 , pay_payroll_actions PACT
2738 WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
2739 AND FEED.balance_type_id = cp_balance_type_id
2740 AND FEED.input_value_id = TARGET.input_value_id
2741 AND TARGET.run_result_id = RR.run_result_id
2742 AND RR.assignment_action_id = ASSACT.assignment_action_id
2743 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2744 AND PACT.effective_date between FEED.effective_start_date
2745 AND FEED.effective_end_date
2746 AND RR.status in ('P','PA')
2747 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2748 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
2749 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
2750 OR ( rr.source_type in ('R','V') /* reversal */
2751 AND exists
2752 ( SELECT null from pay_run_results rr1
2753 WHERE rr1.source_id = cp_element_entry_id
2754 AND rr1.run_result_id = rr.source_id
2755 AND rr1.source_type in ( 'E','I'))));
2756
2757
2758 cursor c1_tax(cp_balance_type_id NUMBER
2759 ,cp_assignment_action_id NUMBER
2760 ,cp_element_entry_id NUMBER
2761 ,cp_element_type_id NUMBER)
2762 IS
2763 SELECT target.result_value tot
2764 FROM pay_run_result_values TARGET
2765 , pay_balance_feeds_f FEED
2766 , pay_run_results RR
2767 , pay_assignment_actions ASSACT
2768 , pay_assignment_actions BAL_ASSACT
2769 , pay_payroll_actions PACT
2770 WHERE bal_assact.assignment_action_id = cp_assignment_action_id
2771 AND FEED.input_value_id = TARGET.input_value_id
2772 AND TARGET.run_result_id = RR.run_result_id
2773 AND RR.assignment_action_id = ASSACT.assignment_action_id
2774 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2775 AND PACT.effective_date between FEED.effective_start_date
2776 AND FEED.effective_end_date
2777 AND RR.status in ('P','PA')
2778 AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2779 AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
2780 AND rr.element_type_id=cp_element_type_id
2781 AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
2782 OR ( rr.source_type in ('R','V')
2783 AND exists
2784 ( SELECT null from pay_run_results rr1
2785 WHERE rr1.source_id = cp_element_entry_id
2786 AND rr1.run_result_id = rr.source_id
2787 AND rr1.source_type in ( 'E','I'))))
2788 and feed.balance_type_id=cp_balance_type_id;
2789
2790
2791
2792
2793 l_get_balance_name c_get_balance_name%ROWTYPE;
2794 l_get_tax_element_type_id c_get_tax_element_type_id%ROWTYPE;
2795 l_get_gross_pay_bal_name c_get_gross_pay_bal_name%ROWTYPE;
2796 l_get_def_balance c_get_def_balance%ROWTYPE;
2797 lc1 c1%ROWTYPE;
2798 lc1_tax c1_tax%ROWTYPE;
2799 BEGIN
2800
2801 OPEN c_get_balance_name;
2802 FETCH c_get_balance_name INTO l_get_balance_name;
2803 OPEN c1(l_get_balance_name.balance_type_id
2804 ,p_assignment_action_id
2805 ,p_element_entry_id );
2806 FETCH c1 INTO lc1;
2807 IF lc1.tot = 0 THEN
2808 OPEN c_get_tax_element_type_id;
2809 FETCH c_get_tax_element_type_id
2810 INTO l_get_tax_element_type_id;
2811 CLOSE c_get_tax_element_type_id;
2812
2813 OPEN c_get_gross_pay_bal_name;
2814 FETCH c_get_gross_pay_bal_name INTO
2815 l_get_gross_pay_bal_name;
2816 CLOSE c_get_gross_pay_bal_name;
2817
2818 OPEN c1_tax (l_get_gross_pay_bal_name.balance_type_id
2819 ,p_assignment_action_id
2820 ,p_element_entry_id
2821 ,l_get_tax_element_type_id.element_type_id
2822 );
2823 FETCH c1_tax INTO lc1_tax;
2824 RETURN(NVL(lc1_tax.tot,0));
2825 CLOSE c1_tax;
2826 ELSE
2827
2828 return(NVL(lc1.tot,0));
2829 END IF;
2830
2831 --return(NVL(lc1.tot,0));
2832 CLOSE c1;
2833 CLOSE c_get_balance_name;
2834
2835
2836 return(0);
2837 END;
2838 --Function to get balance for the view.
2839 FUNCTION get_amount ( p_element_name IN VARCHAR2
2840 ,p_element_type_id IN NUMBER
2841 ,p_effective_date IN DATE
2842 ,p_assignment_id IN NUMBER
2843 )
2844
2845 return NUMBER
2846 IS
2847
2848 CURSOR c_get_balance_name
2849 IS
2850 SELECT balance_type_id ,pbd.balance_dimension_id
2851 FROM pay_balance_types pbt
2852 ,pay_balance_dimensions pbd
2853 WHERE balance_name = p_element_name||' Processed Amt'
2854 AND pbd.legislation_code='GB'
2855 AND pbd.dimension_name='_ELEMENT_ITD';
2856
2857
2858 CURSOR c_get_def_balance ( cp_balance_typ_id NUMBER
2859 ,cp_balance_dim_id NUMBER
2860 )
2861 IS
2862 SELECT pdb.DEFINED_BALANCE_ID
2863 from pay_defined_balances pdb
2864 where pdb.balance_type_id =cp_balance_typ_id
2865 and pdb.balance_dimension_id=cp_balance_dim_id;
2866
2867
2868
2869 BEGIN
2870
2871 FOR l_get_balance_name IN c_get_balance_name
2872 LOOP
2873
2874 FOR l_get_def_balance IN c_get_def_balance
2875 (l_get_balance_name.balance_type_id
2876 ,l_get_balance_name.balance_dimension_id
2877 )
2878 LOOP
2879 return(hr_dirbal.get_balance(p_assignment_id
2880 ,l_get_def_balance.DEFINED_BALANCE_ID
2881 ,p_effective_date));
2882
2883 END LOOP;
2884 END LOOP;
2885 return(0);
2886 EXCEPTION
2887 WHEN OTHERS THEN
2888 return(0);
2889
2890 END;
2891
2892 PROCEDURE insert_company_mileage_claim
2893 ( p_effective_date IN DATE,
2894 p_assignment_id IN NUMBER,
2895 p_business_group_id IN NUMBER,
2896 p_ownership IN VARCHAR2 ,
2897 p_usage_type IN VARCHAR2 ,
2898 p_vehicle_type IN VARCHAR2,
2899 p_start_date IN VARCHAR2 ,
2900 p_end_date IN VARCHAR2 ,
2901 p_claimed_mileage IN VARCHAR2 ,
2902 p_actual_mileage IN VARCHAR2 ,
2903 p_registration_number IN VARCHAR2 ,
2904 p_engine_capacity IN VARCHAR2 ,
2905 p_fuel_type IN VARCHAR2 ,
2906 p_calculation_method IN VARCHAR2 ,
2907 p_user_rates_table IN VARCHAR2 ,
2908 p_fiscal_ratings IN VARCHAR2 ,
2909 p_PAYE_taxable IN VARCHAR2 ,
2910 p_no_of_passengers IN VARCHAR2 ,
2911 p_purpose IN VARCHAR2 ,
2912 p_payroll_id IN NUMBER,
2913 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
2914 p_element_entry_id IN OUT NOCOPY NUMBER ,
2915 p_element_entry_date IN OUT NOCOPY DATE,
2916 p_element_link_id IN NUMBER
2917 )
2918
2919 IS
2920
2921 CURSOR c_get_table_name (cp_user_rates_table VARCHAR2
2922 ,cp_business_group_id NUMBER
2923 )
2924 IS
2925 SELECT put.user_table_name
2926 FROM pay_user_tables put
2927 WHERE user_table_id =cp_user_rates_table
2928 AND put.business_group_id=cp_business_group_id;
2929
2930 CURSOR c_get_input_details (cp_element_type_id NUMBER
2931 ,cp_effective_date DATE
2932 ,cp_business_group_id NUMBER
2933 )
2934 IS
2935 SELECT piv.input_value_id,
2936 piv.name,
2937 piv.display_sequence,
2938 piv.lookup_type,
2939 piv.default_value
2940 FROM pay_input_values_f piv
2941 WHERE piv.element_type_id=cp_element_type_id
2942 AND piv.business_group_id=cp_business_group_id
2943 AND cp_effective_date BETWEEN piv.effective_start_date
2944 AND piv.effective_end_date;
2945
2946
2947 l_effective_start_date DATE;
2948 l_effective_end_date DATE;
2949 l_get_input_details c_get_input_details%ROWTYPE;
2950 l_get_table_name c_get_table_name%ROWTYPE;
2951 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
2952 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
2953 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
2954 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
2955 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
2956 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
2957 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
2958 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
2959 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
2960 l_input_value_id10 pay_input_values_f.input_value_id%TYPE:=NULL;
2961 l_input_value_id11 pay_input_values_f.input_value_id%TYPE:=NULL;
2962 l_input_value_id12 pay_input_values_f.input_value_id%TYPE:=NULL;
2963 l_input_value_id13 pay_input_values_f.input_value_id%TYPE:=NULL;
2964 l_input_value_id14 pay_input_values_f.input_value_id%TYPE:=NULL;
2965 l_input_value_id15 pay_input_values_f.input_value_id%TYPE:=NULL;
2966
2967 l_entry_value1 pay_element_entry_values_f.screen_entry_value%TYPE;
2968 l_entry_value2 pay_element_entry_values_f.screen_entry_value%TYPE;
2969 l_entry_value3 pay_element_entry_values_f.screen_entry_value%TYPE;
2970 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
2971 l_entry_value5 pay_element_entry_values_f.screen_entry_value%TYPE;
2972 l_entry_value6 pay_element_entry_values_f.screen_entry_value%TYPE;
2973 l_entry_value7 pay_element_entry_values_f.screen_entry_value%TYPE;
2974 l_entry_value8 pay_element_entry_values_f.screen_entry_value%TYPE;
2975 l_entry_value9 pay_element_entry_values_f.screen_entry_value%TYPE;
2976 l_entry_value10 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2977 l_entry_value11 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2978 l_entry_value12 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2979 l_entry_value13 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2980 l_entry_value14 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2981 l_entry_value15 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
2982 l_element_entry_id number;
2983 l_proc varchar2(72) := g_package ||'get_meaning';
2984 l_temp_var NUMBER;
2985 BEGIN
2986 hr_utility.set_location('Insert_company_mileage_claim:' || l_proc,260);
2987 l_effective_start_date :=p_effective_date;
2988 l_effective_end_date :=hr_api.g_eot;
2989
2990
2991 BEGIN
2992 l_temp_var := to_number(p_user_rates_table);
2993
2994
2995
2996 OPEN c_get_table_name ( p_user_rates_table
2997 ,p_business_group_id
2998 );
2999 FETCH c_get_table_name INTO l_get_table_name;
3000 CLOSE c_get_table_name;
3001 EXCEPTION
3002 ---------
3003 WHEN OTHERS THEN
3004 l_get_table_name.user_table_name:=p_user_rates_table;
3005 END;
3006
3007 OPEN c_get_input_details (p_mileage_claim_element
3008 ,p_effective_date
3009 ,p_business_group_id
3010 );
3011 LOOP
3012 FETCH c_get_input_details INTO l_get_input_details;
3013 EXIT WHEN c_get_input_details%NOTFOUND;
3014
3015
3016
3017 IF l_get_input_details.name='Pay Value' THEN
3018 l_input_value_id1:=l_get_input_details.input_value_id;
3019 l_entry_value1 :=NULL;
3020 ELSIF l_get_input_details.name='Vehicle Type' THEN
3021
3022 l_input_value_id2:=l_get_input_details.input_value_id;
3023 IF l_get_input_details.lookup_type IS NOT NULL THEN
3024 l_entry_value2:=get_lkp_meaning(p_usage_type
3025 ,l_get_input_details.lookup_type);
3026 ELSE
3027 l_entry_value2 :=p_usage_type;
3028 END IF;
3029 ELSIF l_get_input_details.name='Two Wheeler Type' THEN
3030 l_input_value_id3:=l_get_input_details.input_value_id;
3031 IF l_get_input_details.lookup_type IS NOT NULL THEN
3032 l_entry_value3:=get_lkp_meaning(p_start_date
3033 ,l_get_input_details.lookup_type);
3034 ELSE
3035 l_entry_value3 :=p_start_date;
3036 END IF;
3037 ELSIF l_get_input_details.name='Claim Start Date' THEN
3038 l_input_value_id3:=l_get_input_details.input_value_id;
3039 IF l_get_input_details.lookup_type IS NOT NULL THEN
3040 l_entry_value3:=get_lkp_meaning(p_start_date
3041 ,l_get_input_details.lookup_type);
3042 ELSE
3043 l_entry_value3 :=p_start_date;
3044 END IF;
3045
3046
3047 ELSIF l_get_input_details.name='Claim End Date' THEN
3048 l_input_value_id4:=l_get_input_details.input_value_id;
3049 IF l_get_input_details.lookup_type IS NOT NULL THEN
3050 l_entry_value4:=get_lkp_meaning(p_end_date
3051 ,l_get_input_details.lookup_type);
3052 ELSE
3053 l_entry_value4 :=p_end_date;
3054 END IF;
3055 ELSIF l_get_input_details.name='Claimed Mileage' THEN
3056 l_input_value_id5:=l_get_input_details.input_value_id;
3057 IF l_get_input_details.lookup_type IS NOT NULL THEN
3058 l_entry_value5:=get_lkp_meaning(p_claimed_mileage
3059 ,l_get_input_details.lookup_type);
3060 ELSE
3061 l_entry_value5 :=p_claimed_mileage;
3062 END IF;
3063 ELSIF l_get_input_details.name='Actual Mileage' THEN
3064 l_input_value_id6:=l_get_input_details.input_value_id;
3065 IF l_get_input_details.lookup_type IS NOT NULL THEN
3066 l_entry_value6:=get_lkp_meaning(p_actual_mileage
3067 ,l_get_input_details.lookup_type);
3068 ELSE
3069 l_entry_value6 :=p_actual_mileage;
3070 END IF;
3071 ELSIF l_get_input_details.name='User Rates Table'
3072 OR l_get_input_details.name='Sliding Rates Table' THEN
3073 l_input_value_id7:=l_get_input_details.input_value_id;
3074 IF l_get_input_details.lookup_type IS NOT NULL THEN
3075 l_entry_value7:=get_lkp_meaning(p_user_rates_table
3076 ,l_get_input_details.lookup_type);
3077 ELSE
3078
3079 l_entry_value7 :=l_get_table_name.user_table_name;
3080 END IF;
3081 ELSIF l_get_input_details.name='PAYE Taxable' THEN
3082 l_input_value_id8:=l_get_input_details.input_value_id;
3083 IF l_get_input_details.lookup_type IS NOT NULL THEN
3084 l_entry_value8:=get_lkp_meaning(NVL(p_PAYE_taxable
3085 ,l_get_input_details.default_value)
3086 ,l_get_input_details.lookup_type);
3087 ELSE
3088 l_entry_value8 :=p_PAYE_taxable;
3089 END IF;
3090 ELSIF l_get_input_details.name='No of Passengers' THEN
3091 l_input_value_id9:=l_get_input_details.input_value_id;
3092 IF l_get_input_details.lookup_type IS NOT NULL THEN
3093 l_entry_value9:=get_lkp_meaning(p_no_of_Passengers
3094 ,l_get_input_details.lookup_type);
3095 ELSE
3096 l_entry_value9 :=p_no_of_Passengers;
3097 END IF;
3098 ELSIF l_get_input_details.name='Vehicle Reg Number' THEN
3099 l_input_value_id10:=l_get_input_details.input_value_id;
3100 IF l_get_input_details.lookup_type IS NOT NULL THEN
3101 l_entry_value10:=get_lkp_meaning(p_registration_number
3102 ,l_get_input_details.lookup_type);
3103 ELSE
3104 l_entry_value10 := p_registration_number;
3105 END IF;
3106 ELSIF l_get_input_details.name='Engine Capacity' THEN
3107 l_input_value_id11:=l_get_input_details.input_value_id;
3108 IF l_get_input_details.lookup_type IS NOT NULL THEN
3109 l_entry_value11:=get_lkp_meaning(p_engine_capacity
3110 ,l_get_input_details.lookup_type);
3111 ELSE
3112 l_entry_value11 :=p_engine_capacity;
3113 END IF;
3114 ELSIF l_get_input_details.name='Fuel Type' THEN
3115 l_input_value_id12:=l_get_input_details.input_value_id;
3116 IF l_get_input_details.lookup_type IS NOT NULL THEN
3117 l_entry_value12:=get_lkp_meaning(p_fuel_type
3118 ,l_get_input_details.lookup_type);
3119 ELSE
3120 l_entry_value12 :=p_fuel_type;
3121 END IF;
3122 ELSIF l_get_input_details.name='Calculation Method' THEN
3123 l_input_value_id13:=l_get_input_details.input_value_id;
3124 IF l_get_input_details.lookup_type IS NOT NULL THEN
3125 l_entry_value13:=get_lkp_meaning(p_calculation_method
3126 ,l_get_input_details.lookup_type);
3127 ELSE
3128 l_entry_value13 :=p_calculation_method;
3129 END IF;
3130 ELSIF l_get_input_details.name='Purpose' THEN
3131 l_input_value_id14:=l_get_input_details.input_value_id;
3132 IF l_get_input_details.lookup_type IS NOT NULL THEN
3133 l_entry_value14:=get_lkp_meaning(p_purpose
3134 ,l_get_input_details.lookup_type);
3135 ELSE
3136 l_entry_value14 :=p_purpose;
3137 END IF;
3138 END IF;
3139
3140
3141 END LOOP;
3142 CLOSE c_get_input_details;
3143 hr_utility.set_location('Entering hr_entry_apiinsert_element_entry',270);
3144 hr_entry_api.insert_element_entry
3145 (
3146 p_effective_start_date =>l_effective_start_date,
3147 p_effective_end_date =>l_effective_end_date,
3148 p_element_entry_id =>p_element_entry_id,
3149 p_original_entry_id =>null,
3150 p_assignment_id =>p_assignment_id,
3151 p_element_link_id =>p_element_link_id,
3152 p_creator_type =>'F',
3153 p_entry_type =>'E',
3154 p_creator_id => null,
3155 p_input_value_id1 =>l_input_value_id1,
3156 p_input_value_id2 =>l_input_value_id2,
3157 p_input_value_id3 =>l_input_value_id3,
3158 p_input_value_id4 =>l_input_value_id4,
3159 p_input_value_id5 =>l_input_value_id5,
3160 p_input_value_id6 =>l_input_value_id6,
3161 p_input_value_id7 =>l_input_value_id7,
3162 p_input_value_id8 =>l_input_value_id8,
3163 p_input_value_id9 =>l_input_value_id9,
3164 p_input_value_id10 =>l_input_value_id10 ,
3165 p_input_value_id11 =>l_input_value_id11,
3166 p_input_value_id12 =>l_input_value_id12,
3167 p_input_value_id13 =>l_input_value_id13,
3168 p_input_value_id14 =>l_input_value_id14,
3169 p_input_value_id15 =>l_input_value_id15,
3170 p_entry_value1 =>l_entry_value1,
3171 p_entry_value2 =>l_entry_value2 ,
3172 p_entry_value3 =>l_entry_value3,
3173 p_entry_value4 =>l_entry_value4,
3174 p_entry_value5 =>l_entry_value5,
3175 p_entry_value6 =>l_entry_value6,
3176 p_entry_value7 =>l_entry_value7,
3177 p_entry_value8 =>l_entry_value8,
3178 p_entry_value9 =>l_entry_value9,
3179 p_entry_value10 =>l_entry_value10,
3180 p_entry_value11 =>l_entry_value11,
3181 p_entry_value12 =>l_entry_value12,
3182 p_entry_value13 =>l_entry_value13,
3183 p_entry_value14 =>l_entry_value14,
3184 p_entry_value15 =>l_entry_value15
3185 );
3186
3187 END;
3188
3189 --Inserts GB specific claim
3190 PROCEDURE insert_private_mileage_claim
3191 ( p_effective_date IN DATE,
3192 p_assignment_id IN NUMBER,
3193 p_business_group_id IN NUMBER,
3194 p_ownership IN VARCHAR2 ,
3195 p_usage_type IN VARCHAR2 ,
3196 p_vehicle_type IN VARCHAR2,
3197 p_start_date IN VARCHAR2 ,
3198 p_end_date IN VARCHAR2 ,
3199 p_claimed_mileage IN VARCHAR2 ,
3200 p_actual_mileage IN VARCHAR2 ,
3201 p_registration_number IN VARCHAR2 ,
3202 p_engine_capacity IN VARCHAR2 ,
3203 p_fuel_type IN VARCHAR2 ,
3204 p_calculation_method IN VARCHAR2 ,
3205 p_user_rates_table IN VARCHAR2 ,
3206 p_fiscal_ratings IN VARCHAR2 ,
3207 p_PAYE_taxable IN VARCHAR2 ,
3208 p_no_of_passengers IN VARCHAR2 ,
3209 p_purpose IN VARCHAR2 ,
3210 p_payroll_id IN NUMBER,
3211 p_mileage_claim_element IN OUT NOCOPY NUMBER ,
3212 p_element_entry_id IN OUT NOCOPY NUMBER ,
3213 p_element_entry_date IN OUT NOCOPY DATE,
3214 p_element_link_id IN NUMBER
3215 )
3216
3217 IS
3218
3219
3220 CURSOR c_get_input_details (cp_element_type_id NUMBER
3221 ,cp_effective_date DATE
3222 ,cp_business_group_id NUMBER
3223 )
3224 IS
3225 SELECT piv.input_value_id,
3226 piv.name,
3227 piv.display_sequence,
3228 piv.lookup_type,
3229 piv.default_value
3230 FROM pay_input_values_f piv
3231 WHERE piv.element_type_id=cp_element_type_id
3232 AND piv.business_group_id=cp_business_group_id
3233 AND cp_effective_date BETWEEN piv.effective_start_date
3234 AND piv.effective_end_date;
3235
3236 CURSOR c_get_table_name ( cp_user_rates_table VARCHAR2
3237 ,cp_business_group_id NUMBER
3238 )
3239 IS
3240 SELECT put.user_table_name
3241 FROM pay_user_tables put
3242 WHERE user_table_id =cp_user_rates_table
3243 AND put.business_group_id=cp_business_group_id;
3244 l_effective_start_date DATE;
3245 l_effective_end_date DATE;
3246 l_get_input_details c_get_input_details%ROWTYPE;
3247 l_get_table_name c_get_table_name%ROWTYPE;
3248
3249 l_input_value_id1 pay_input_values_f.input_value_id%TYPE;
3250 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
3251 l_input_value_id3 pay_input_values_f.input_value_id%TYPE;
3252 l_input_value_id4 pay_input_values_f.input_value_id%TYPE;
3253 l_input_value_id5 pay_input_values_f.input_value_id%TYPE;
3254 l_input_value_id6 pay_input_values_f.input_value_id%TYPE;
3255 l_input_value_id7 pay_input_values_f.input_value_id%TYPE;
3256 l_input_value_id8 pay_input_values_f.input_value_id%TYPE;
3257 l_input_value_id9 pay_input_values_f.input_value_id%TYPE;
3258 l_input_value_id10 pay_input_values_f.input_value_id%TYPE:=NULL;
3259 l_input_value_id11 pay_input_values_f.input_value_id%TYPE:=NULL;
3260 l_input_value_id12 pay_input_values_f.input_value_id%TYPE:=NULL;
3261 l_input_value_id13 pay_input_values_f.input_value_id%TYPE:=NULL;
3262 l_input_value_id14 pay_input_values_f.input_value_id%TYPE:=NULL;
3263 l_input_value_id15 pay_input_values_f.input_value_id%TYPE:=NULL;
3264 l_input_value_id16 pay_input_values_f.input_value_id%TYPE:=NULL;
3265
3266 l_entry_value1 pay_element_entry_values_f.screen_entry_value%TYPE;
3267 l_entry_value2 pay_element_entry_values_f.screen_entry_value%TYPE;
3268 l_entry_value3 pay_element_entry_values_f.screen_entry_value%TYPE;
3269 l_entry_value4 pay_element_entry_values_f.screen_entry_value%TYPE;
3270 l_entry_value5 pay_element_entry_values_f.screen_entry_value%TYPE;
3271 l_entry_value6 pay_element_entry_values_f.screen_entry_value%TYPE;
3272 l_entry_value7 pay_element_entry_values_f.screen_entry_value%TYPE;
3273 l_entry_value8 pay_element_entry_values_f.screen_entry_value%TYPE;
3274 l_entry_value9 pay_element_entry_values_f.screen_entry_value%TYPE;
3275 l_entry_value10 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3276 l_entry_value11 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3277 l_entry_value12 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3278 l_entry_value13 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3279 l_entry_value14 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3280 l_entry_value15 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3281 l_entry_value16 pay_element_entry_values_f.screen_entry_value%TYPE:=NULL;
3282 l_temp_var NUMBER;
3283 BEGIN
3284 hr_utility.set_location('Enter private element entry process',280);
3285 l_effective_start_date :=p_effective_date;
3286 l_effective_end_date :=hr_api.g_eot;
3287
3288 BEGIN
3289 l_temp_var := to_number(p_user_rates_table);
3290
3291
3292
3293 OPEN c_get_table_name ( p_user_rates_table
3294 ,p_business_group_id
3295 );
3296 FETCH c_get_table_name INTO l_get_table_name;
3297 CLOSE c_get_table_name;
3298 EXCEPTION
3299 ---------
3300 WHEN OTHERS THEN
3301 l_get_table_name.user_table_name:=p_user_rates_table;
3302 END;
3303
3304 OPEN c_get_input_details (p_mileage_claim_element
3305 ,p_effective_date
3306 ,p_business_group_id
3307 );
3308 LOOP
3309 FETCH c_get_input_details INTO l_get_input_details;
3310 EXIT WHEN c_get_input_details%NOTFOUND;
3311
3312
3313 IF l_get_input_details.name='Pay Value' THEN
3314 l_input_value_id1:=l_get_input_details.input_value_id;
3315 l_entry_value1 :=NULL;
3316 ELSIF l_get_input_details.name='Rate Type' THEN
3317
3318 l_input_value_id2:=l_get_input_details.input_value_id;
3319 IF l_get_input_details.lookup_type IS NOT NULL THEN
3320 IF p_usage_type IS NULL THEN
3321 l_entry_value2 :=get_dflt_input_value (l_input_value_id2
3322 ,p_mileage_claim_element
3323 ,p_business_group_id
3324 ,p_effective_date
3325 );
3326 l_entry_value2:=get_lkp_meaning(l_entry_value2
3327 ,l_get_input_details.lookup_type);
3328 ELSE
3329 l_entry_value2:=get_lkp_meaning(p_usage_type
3330 ,l_get_input_details.lookup_type);
3331 END IF;
3332 ELSE
3333 l_entry_value2 :=p_usage_type;
3334
3335 END IF;
3336 ELSIF l_get_input_details.name='Two Wheeler Type' THEN
3337 l_input_value_id3:=l_get_input_details.input_value_id;
3338 IF l_get_input_details.lookup_type IS NOT NULL THEN
3339 l_entry_value3:=get_lkp_meaning(p_start_date
3340 ,l_get_input_details.lookup_type);
3341 ELSE
3342 l_entry_value3 :=p_start_date;
3343 END IF;
3344 ELSIF l_get_input_details.name='Claim Start Date' THEN
3345 l_input_value_id4:=l_get_input_details.input_value_id;
3346 IF l_get_input_details.lookup_type IS NOT NULL THEN
3347 l_entry_value4:=get_lkp_meaning(p_start_date
3348 ,l_get_input_details.lookup_type);
3349 ELSE
3350 l_entry_value4 :=p_start_date;
3351 END IF;
3352
3353 ELSIF l_get_input_details.name='Claim End Date' THEN
3354 l_input_value_id5:=l_get_input_details.input_value_id;
3355 IF l_get_input_details.lookup_type IS NOT NULL THEN
3356 l_entry_value5:=get_lkp_meaning(p_end_date
3357 ,l_get_input_details.lookup_type);
3358 ELSE
3359 l_entry_value5 :=p_end_date;
3360 END IF;
3361 ELSIF l_get_input_details.name='Claimed Mileage' THEN
3362 l_input_value_id6:=l_get_input_details.input_value_id;
3363 IF l_get_input_details.lookup_type IS NOT NULL THEN
3364 l_entry_value6:=get_lkp_meaning(p_claimed_mileage
3365 ,l_get_input_details.lookup_type);
3366 ELSE
3367 l_entry_value6 :=p_claimed_mileage;
3368 END IF;
3369 ELSIF l_get_input_details.name='Actual Mileage' THEN
3370 l_input_value_id7:=l_get_input_details.input_value_id;
3371 IF l_get_input_details.lookup_type IS NOT NULL THEN
3372 l_entry_value7:=get_lkp_meaning(p_actual_mileage
3373 ,l_get_input_details.lookup_type);
3374 ELSE
3375 l_entry_value7 :=p_actual_mileage;
3376 END IF;
3377 ELSIF l_get_input_details.name='Vehicle Reg Number' THEN
3378 l_input_value_id8:=l_get_input_details.input_value_id;
3379 IF l_get_input_details.lookup_type IS NOT NULL THEN
3380 l_entry_value8:=get_lkp_meaning(p_registration_number
3381 ,l_get_input_details.lookup_type);
3382 ELSE
3383 l_entry_value8 := p_registration_number;
3384 END IF;
3385 ELSIF l_get_input_details.name='Engine Capacity' THEN
3386 l_input_value_id9:=l_get_input_details.input_value_id;
3387 IF l_get_input_details.lookup_type IS NOT NULL THEN
3388 l_entry_value9:=get_lkp_meaning(p_engine_capacity
3389 ,l_get_input_details.lookup_type);
3390 ELSE
3391 l_entry_value9 :=p_engine_capacity;
3392 END IF;
3393 ELSIF l_get_input_details.name='Fuel Type' THEN
3394 l_input_value_id10:=l_get_input_details.input_value_id;
3395 IF l_get_input_details.lookup_type IS NOT NULL THEN
3396 l_entry_value10:=get_lkp_meaning(p_fuel_type
3397 ,l_get_input_details.lookup_type);
3398 ELSE
3399 l_entry_value10 :=p_fuel_type;
3400 END IF;
3401 ELSIF l_get_input_details.name='Calculation Method' THEN
3402 l_input_value_id11:=l_get_input_details.input_value_id;
3403 IF l_get_input_details.lookup_type IS NOT NULL THEN
3404 l_entry_value11:=get_lkp_meaning(p_calculation_method
3405 ,l_get_input_details.lookup_type);
3406 ELSE
3407 l_entry_value11 :=p_calculation_method;
3408 END IF;
3409 ELSIF l_get_input_details.name='User Rates Table' OR
3410 l_get_input_details.name='Sliding Rates Table' THEN
3411 l_input_value_id12:=l_get_input_details.input_value_id;
3412 IF l_get_input_details.lookup_type IS NOT NULL THEN
3413 l_entry_value12:=get_lkp_meaning(p_user_rates_table
3414 ,l_get_input_details.lookup_type);
3415 ELSE
3416 IF p_user_rates_table IS NOT NULL THEN
3417 l_entry_value12 :=l_get_table_name.user_table_name;
3418 ELSE
3419
3420 l_entry_value12 :=l_get_input_details.default_value;
3421
3422 END IF;
3423 END IF;
3424 ELSIF l_get_input_details.name='PAYE Taxable' THEN
3425 l_input_value_id13:=l_get_input_details.input_value_id;
3426 IF l_get_input_details.lookup_type IS NOT NULL THEN
3427 l_entry_value13:=get_lkp_meaning(NVL(p_PAYE_Taxable
3428 ,l_get_input_details.default_value)
3429 ,l_get_input_details.lookup_type);
3430 ELSE
3431 l_entry_value13 :=p_PAYE_Taxable;
3432 END IF;
3433 ELSIF l_get_input_details.name='No of Passengers' THEN
3434 l_input_value_id14:=l_get_input_details.input_value_id;
3435 IF l_get_input_details.lookup_type IS NOT NULL THEN
3436 l_entry_value14:=get_lkp_meaning(p_no_of_passengers
3437 ,l_get_input_details.lookup_type);
3438 ELSE
3439 l_entry_value14 :=p_no_of_passengers;
3440 END IF;
3441 ELSIF l_get_input_details.name='CO2 Emissions' THEN
3442 l_input_value_id15:=l_get_input_details.input_value_id;
3443 IF l_get_input_details.lookup_type IS NOT NULL THEN
3444 l_entry_value15:=get_lkp_meaning(p_fiscal_ratings
3445 ,l_get_input_details.lookup_type);
3446 ELSE
3447 l_entry_value15 :=p_fiscal_ratings;
3448 END IF;
3449 ELSIF l_get_input_details.name='Purpose' THEN
3450 l_input_value_id16:=l_get_input_details.input_value_id;
3451 IF l_get_input_details.lookup_type IS NOT NULL THEN
3452 l_entry_value16:=get_lkp_meaning(p_purpose
3453 ,l_get_input_details.lookup_type);
3454 ELSE
3455 l_entry_value16 :=p_purpose;
3456 END IF;
3457 END IF;
3458
3459
3460
3461
3462
3463 END LOOP;
3464 CLOSE c_get_input_details;
3465
3466 IF l_input_value_id3 IS NULL THEN
3467 l_entry_value3 :=l_entry_value16;
3468 l_input_value_id3 :=l_input_value_id16;
3469
3470
3471 END IF;
3472 hr_utility.set_location('Enter hr_entry_apiinsert_element_entr',290);
3473 hr_entry_api.insert_element_entry
3474 (
3475 --
3476 -- Common Parameters
3477 --
3478 p_effective_start_date =>l_effective_start_date,
3479 p_effective_end_date =>l_effective_end_date,
3480 p_element_entry_id =>p_element_entry_id,
3481 p_original_entry_id =>null,
3482 p_assignment_id =>p_assignment_id,
3483 p_element_link_id =>p_element_link_id,
3484 p_creator_type =>'F',
3485 p_entry_type =>'E',
3486 p_creator_id => null,
3487 p_input_value_id1 =>l_input_value_id1,
3488 p_input_value_id2 =>l_input_value_id2,
3489 p_input_value_id3 =>l_input_value_id3,
3490 p_input_value_id4 =>l_input_value_id4,
3491 p_input_value_id5 =>l_input_value_id5,
3492 p_input_value_id6 =>l_input_value_id6,
3493 p_input_value_id7 =>l_input_value_id7,
3494 p_input_value_id8 =>l_input_value_id8,
3495 p_input_value_id9 =>l_input_value_id9,
3496 p_input_value_id10 =>l_input_value_id10 ,
3497 p_input_value_id11 =>l_input_value_id11,
3498 p_input_value_id12 =>l_input_value_id12,
3499 p_input_value_id13 =>l_input_value_id13,
3500 p_input_value_id14 =>l_input_value_id14,
3501 p_input_value_id15 =>l_input_value_id15,
3502 p_entry_value1 =>l_entry_value1,
3503 p_entry_value2 =>l_entry_value2 ,
3504 p_entry_value3 =>l_entry_value3,
3505 p_entry_value4 =>l_entry_value4,
3506 p_entry_value5 =>l_entry_value5,
3507 p_entry_value6 =>l_entry_value6,
3508 p_entry_value7 =>l_entry_value7,
3509 p_entry_value8 =>l_entry_value8,
3510 p_entry_value9 =>l_entry_value9,
3511 p_entry_value10 =>l_entry_value10,
3512 p_entry_value11 =>l_entry_value11,
3513 p_entry_value12 =>l_entry_value12,
3514 p_entry_value13 =>l_entry_value13,
3515 p_entry_value14 =>l_entry_value14,
3516 p_entry_value15 =>l_entry_value15
3517 );
3518 hr_utility.set_location('leaving hr_entry_apiinsert_element_entr',300);
3519 END;
3520 END;
[Home] [Help]