[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_GB_P11D_MILEAGE_EXTRACT
Source
1 package body pay_gb_p11d_mileage_extract as
2 /* $Header: pygbmxpl.pkb 120.5 2011/03/01 13:46:44 krreddy ship $
3 ******************************************************************
4 * *
5 * Copyright (C) 2003 Oracle Corporation UK Ltd., *
6 * Reading, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21
22 Name : pay_gb_p11d_mileage_extract
23
24 Description : This package contains functions and procedures to
25 create the extract file for P11d Mileage Claims
26
27 Uses :
28
29 Used By : P11d 2003 Mileage Claims Extract Process
30
31
32 Change List :
33
34 Version Date Author Bug Description
35 ------- ----- -------- ---------- ----------------
36
37 115.0 14/4/03 GBUTLER Created
38 115.2 06/5/03 GBUTLER Altered detail record layout
39 115.3 23/1/11 KRREDDY 9536332 Implemented the P11D Mileage Extract process
40 115.4 25/1/11 KRREDDY 9536332 Added nocopy hint for out parameters
41 115.5 27/1/11 KRREDDY 9536332 Modified further to make upload process work.
42 115.6 28/1/11 KRREDDY 9536332 Modified further to make upload process work.
43 115.7 18/2/11 KRREDDY 10066755 Modified to avoid impact of enabling skip term leg rule.
44
45 */
46
47 g_package_name varchar2(27) := 'pay_gb_p11d_mileage_extract';
48
49 -- Declare global variables:
50 g_veh_rcd_id NUMBER;
51 g_ext_rslt_id NUMBER;
52 g_person_id NUMBER;
53 g_bg_id NUMBER;
54
55 ---------------------------------------------------------------------------
56 -- Function: GET_BUS_GROUP_ID
57 -- Description: This function gets business group_id
58 ---------------------------------------------------------------------------
59 FUNCTION get_bus_group_id (p_asg_id IN NUMBER) RETURN NUMBER IS
60 --
61 CURSOR get_bus_group_id IS
62 SELECT business_group_id
63 FROM per_all_assignments_f
64 WHERE assignment_id = p_asg_id;
65 --
66 l_bus_group_id NUMBER;
67
68 BEGIN
69
70 -- Get Business Group ID
71 OPEN get_bus_group_id;
72 FETCH get_bus_group_id INTO l_bus_group_id;
73 CLOSE get_bus_group_id;
74 --
75 RETURN l_bus_group_id;
76 --
77
78 END get_bus_group_id;
79
80 ---------------------------------------------------------------------------
81 -- Function: GET_PARAM_TAX_YEAR
82 -- Description: This function gets tax year parameter from UDT
83 ---------------------------------------------------------------------------
84 FUNCTION get_param_tax_year (p_bus_grp_id IN NUMBER) return number is
85
86 l_tax_year number;
87
88 --
89 BEGIN
90
91 BEGIN
92 hr_utility.set_location('get_param_tax_year',1);
93
94 l_tax_year := hruserdt.get_table_value(p_bus_grp_id,
95 'PAY GB P11d Mileage Extract',
96 'Parameter Value',
97 'Tax Year (YYYY)',
98 ben_ext_person.g_effective_date );
99
100
101 EXCEPTION
102
103 WHEN others THEN
104 -- tax year parameter not set or wrong format
105 -- raise an error
106 hr_utility.set_location('get_param_tax_year',2);
107 hr_utility.trace('ERROR: Seeded value for tax year not found or data corrupt');
108 raise;
109
110 END;
111 --
112 hr_utility.set_location('get_param_tax_year',3);
113
114 hr_utility.trace('Tax year: '||to_char(l_tax_year));
115 hr_utility.set_location('get_param_tax_year',99);
116
117 RETURN l_tax_year;
118
119 end get_param_tax_year;
120
121
122 ---------------------------------------------------------------------------
123 -- Function: GET_TAX_YEAR_START
124 -- Description: This function gets tax year start date based on the
125 -- extract end date or effective date of the concurrent
126 -- process if extract end date is null
127 ---------------------------------------------------------------------------
128 FUNCTION get_tax_year_start (p_bus_grp_id IN NUMBER) return date is
129
130 l_tax_year_start date;
131
132 l_tax_year number;
133
134 l_cal_year_start date;
135 --
136 BEGIN
137
138 hr_utility.set_location('get_tax_year_start',1);
139
140 l_tax_year := get_param_tax_year(p_bus_grp_id);
141
142 hr_utility.set_location('get_tax_year_start',2);
143
144 l_cal_year_start := fnd_date.displaydate_to_date('01-JAN-'||to_char(l_tax_year));
145
146 l_tax_year_start := fnd_date.displaydate_to_date('06-APR-'||to_char(l_cal_year_start - 365, 'YYYY'));
147
148 hr_utility.trace('Tax year start: '||to_char(l_tax_year_start,'DD-MON-YYYY'));
149 hr_utility.set_location('get_tax_year_start',99);
150
151 RETURN l_tax_year_start;
152
153 end get_tax_year_start;
154
155 ---------------------------------------------------------------------------
156 -- Function: GET_TAX_YEAR_END
157 -- Description: Overloaded function gets tax year end date based on the
158 -- extract end date or effective date of the concurrent
159 -- process if extract end date is null
160 ---------------------------------------------------------------------------
161
162 FUNCTION get_tax_year_end (p_bus_grp_id IN NUMBER) return date is
163
164 l_tax_year_end date;
165
166 l_tax_year number;
167
168 l_cal_year_start date;
169 --
170 BEGIN
171
172 hr_utility.set_location('get_tax_year_end',1);
173
174 l_tax_year := get_param_tax_year(p_bus_grp_id);
175 --
176 hr_utility.set_location('get_tax_year_end',2);
177
178 l_cal_year_start := fnd_date.displaydate_to_date('01-JAN-'||to_char(l_tax_year));
179
180 l_tax_year_end := fnd_date.displaydate_to_date('05-APR-'||to_char(l_cal_year_start,'YYYY'));
181
182 hr_utility.trace('Tax year end: '||to_char(l_tax_year_end,'DD-MON-YYYY'));
183 hr_utility.set_location('get_tax_year_end',99);
184
185 RETURN l_tax_year_end;
186
187 end get_tax_year_end;
188
189 ---------------------------------------------------------------------------
190 -- Function: GET_PARAM_EXT_START_DATE
191 -- Desctiption: This function gets value of extract start date parameter
192 ---------------------------------------------------------------------------
193 FUNCTION get_param_ext_start_date(p_bus_group_id IN NUMBER) RETURN DATE IS
194 --
195 l_ext_start_date DATE;
196 --
197 BEGIN
198 BEGIN
199 hr_utility.set_location('get_param_ext_start_date',1);
200
204 'Extract Start Date (DD-MON-YYYY)',
201 l_ext_start_date := fnd_date.displaydate_to_date(hruserdt.get_table_value(p_bus_group_id,
202 'PAY GB P11D Mileage Extract',
203 'Parameter Value',
205 ben_ext_person.g_effective_date ));
206
207 l_ext_start_date := nvl(l_ext_start_date,get_tax_year_start(p_bus_group_id));
208
209 EXCEPTION
210 WHEN others THEN
211 -- extract date parameter not set or wrong format
212 -- default to end of fiscal year
213 hr_utility.set_location('get_param_ext_end_date',2);
214 l_ext_start_date := get_tax_year_start(p_bus_group_id);
215
216 END;
217 --
218 hr_utility.set_location('get_param_ext_start_date',99);
219 RETURN l_ext_start_date;
220 END get_param_ext_start_date;
221
222 ---------------------------------------------------------------------------
223 -- Function: GET_PARAM_EXT_END_DATE
224 -- Description: This function gets value of extract end date parameter
225 ---------------------------------------------------------------------------
226 FUNCTION get_param_ext_end_date(p_bus_group_id IN NUMBER) RETURN DATE IS
227 --
228 l_ext_end_date DATE;
229 --
230 BEGIN
231
232 BEGIN
233 hr_utility.set_location('get_param_ext_end_date',1);
234
235 l_ext_end_date := fnd_date.displaydate_to_date(hruserdt.get_table_value(p_bus_group_id,
236 'PAY GB P11D Mileage Extract',
237 'Parameter Value',
238 'Extract End Date (DD-MON-YYYY)',
239 ben_ext_person.g_effective_date ));
240
241
242 l_ext_end_date := nvl(l_ext_end_date,get_tax_year_end(p_bus_group_id));
243
244 EXCEPTION
245
246 WHEN others THEN
247 -- extract date parameter not set or wrong format
248 -- default to end of fiscal year
249 hr_utility.set_location('get_param_ext_end_date',2);
250
251 l_ext_end_date := get_tax_year_end(p_bus_group_id);
252
253 END;
254 --
255 hr_utility.set_location('get_param_ext_end_date',99);
256
257 RETURN l_ext_end_date;
258
259 END get_param_ext_end_date;
260
261
262 ---------------------------------------------------------------------------
263 -- Function: GET_PARAM_PAYROLL_ID
264 -- Description: This function gets id of payroll name parameter
265 ---------------------------------------------------------------------------
266 FUNCTION get_param_payroll_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
267 --
268 l_payroll_name pay_all_payrolls_f.payroll_name%TYPE;
269 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
270 --
271 CURSOR get_payroll_id IS
272 SELECT payroll_id
273 FROM pay_all_payrolls_f
274 WHERE payroll_name = l_payroll_name
275 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id
276 AND ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;
277 --
278 BEGIN
279
280 -- Get user Table Value
281
282 BEGIN
283
284 -- Get Payroll Name Parameter Value
285 l_payroll_name := hruserdt.get_table_value(p_bus_group_id,
286 'PAY GB P11D Mileage Extract',
287 'Parameter Value',
288 'Payroll Name',
289 ben_ext_person.g_effective_date );
290 --
291 -- Get Payroll_id
292 OPEN get_payroll_id;
293 FETCH get_payroll_id INTO l_payroll_id;
294 CLOSE get_payroll_id;
295 --
296 EXCEPTION
297
298 WHEN others THEN
299 l_payroll_name := NULL;
300 l_payroll_id := NULL;
301 END;
302 --
303 RETURN l_payroll_id;
304 --
305 END get_param_payroll_id;
306
307 ---------------------------------------------------------------------------
308 -- Function: GET_PARAM_TAX_DIST
309 -- Description: This function gets value of Tax District Reference
310 -- parameter
311 ---------------------------------------------------------------------------
312 FUNCTION get_param_tax_dist(p_bus_group_id IN NUMBER) RETURN VARCHAR2 IS
313 --
314 l_tax_dist hr_organization_information.org_information1%TYPE;
315 --
316 BEGIN
317 BEGIN
318 -- Get Tax District Reference Parameter Value
319 l_tax_dist := hruserdt.get_table_value(p_bus_group_id,
320 'PAY GB P11D Mileage Extract',
321 'Parameter Value',
322 'Tax District Reference',
323 ben_ext_person.g_effective_date );
324 --
325 EXCEPTION
326 WHEN others THEN
327 l_tax_dist := NULL;
328 END;
329 --
330 RETURN l_tax_dist;
331 --
332 END get_param_tax_dist;
333
334 ---------------------------------------------------------------------------
335 -- Function: GET_PARAM_PERSON_ID
336 -- Description: This function gets person id based on employee number
337 -- parameter
338 ---------------------------------------------------------------------------
342 l_person_id per_all_people_f.person_id%TYPE;
339 FUNCTION get_param_person_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
340 --
341 l_employee_number per_all_people_f.employee_number%TYPE;
343 --
344 CURSOR get_person_id IS
345 SELECT person_id
346 FROM per_all_people_f
347 WHERE employee_number = l_employee_number
348 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id
349 AND ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;
350 --
351 BEGIN
352 BEGIN
353 -- Get Employee Number Parameter Value
354 l_employee_number := hruserdt.get_table_value(p_bus_group_id,
355 'PAY GB P11D Mileage Extract',
356 'Parameter Value',
357 'Employee Number',
358 ben_ext_person.g_effective_date );
359 --
360 -- Get Person Id
361 OPEN get_person_id;
362 FETCH get_person_id INTO l_person_id;
363 CLOSE get_person_id;
364 --
365 EXCEPTION
366 WHEN others THEN
367 l_employee_number := NULL;
368 l_person_id := NULL;
369 END;
370 --
371 RETURN l_person_id;
372 --
373 END get_param_person_id;
374
375 ---------------------------------------------------------------------------
376 -- Function: GET_PARAM_CONSOLIDATION_SET_ID
377 -- Description: This function gets consolidation set id based on
378 -- consolidation set parameter
379 ---------------------------------------------------------------------------
380 FUNCTION get_param_consolidation_set_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
381 --
382 l_consolidation_set pay_consolidation_sets.consolidation_set_name%TYPE;
383 l_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE;
384 --
385 CURSOR get_consolidation_set_id IS
386 SELECT consolidation_set_id
387 FROM pay_consolidation_sets
388 WHERE consolidation_Set_name = l_consolidation_set
389 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id;
390 --
391 BEGIN
392 BEGIN
393 -- Get Consolidation Set Parameter Value
394 l_consolidation_set := hruserdt.get_table_value(p_bus_group_id,
395 'PAY GB P11D Mileage Extract',
396 'Parameter Value',
397 'Consolidation Set',
398 ben_ext_person.g_effective_date );
399 --
400 -- Get Consolidation Set Id
401 OPEN get_consolidation_set_id;
402 FETCH get_consolidation_set_id INTO l_consolidation_set_id;
403 CLOSE get_consolidation_set_id;
404 --
405 EXCEPTION
406 WHEN others THEN
407 l_consolidation_set := NULL;
408 l_consolidation_set_id := NULL;
409 END;
410 --
411 RETURN l_consolidation_set_id;
412 --
413 END get_param_consolidation_set_id;
414
415
416 ---------------------------------------------------------------------------
417 -- Function: GET_PARAM_ASSIGNMENT_SET_ID
418 -- Description: This function gets assignment set id based on
419 -- assignment set parameter
420 ---------------------------------------------------------------------------
421 FUNCTION get_param_assignment_set_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
422 --
423 l_assignment_set hr_assignment_sets.assignment_set_name%TYPE;
424 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
425 --
426 CURSOR get_assignment_set_id IS
427 SELECT assignment_set_id
428 FROM hr_assignment_sets
429 WHERE assignment_set_name = l_assignment_set
430 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id;
431 --
432 BEGIN
433 BEGIN
434 -- Get Assignment Set Parameter Value
435 l_assignment_set := hruserdt.get_table_value(p_bus_group_id,
436 'PAY GB P11D Mileage Extract',
437 'Parameter Value',
438 'Assignment Set',
439 ben_ext_person.g_effective_date );
440 --
441 -- Get Assignment Set Id
442 OPEN get_assignment_set_id;
443 FETCH get_assignment_set_id INTO l_assignment_set_id;
444 CLOSE get_assignment_set_id;
445 --
446 EXCEPTION
447 WHEN others THEN
448 l_assignment_set := NULL;
449 l_assignment_set_id := NULL;
450 END;
451 --
452 RETURN l_assignment_set_id;
453 --
454 END get_param_assignment_set_id;
455
456
457
458 ---------------------------------------------------------------------------
459 -- Function: CHECK_ASG_INCLUSION
460 -- Description: This function checks whether given assignment satisfies
461 -- input criteria and mileage ASG_YTD > 0
462 ---------------------------------------------------------------------------
463 FUNCTION check_asg_inclusion(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
464 --
465 l_bus_group_id NUMBER;
466
467 l_ext_end_Date DATE;
468 l_ext_payroll_id NUMBER;
469 l_ext_person_id NUMBER;
470 l_ext_tax_dist VARCHAR2(150);
471 l_ext_con_set_id NUMBER;
475 --
472 l_ext_asg_set_id NUMBER;
473 --
474 l_asg_set_include VARCHAR2(1) := 'N';
476 l_asg_include VARCHAR2(1) := 'N';
477 --
478 l_mileage_balance NUMBER;
479 --
480 CURSOR get_asg_eff_dates(p_asg_id IN NUMBER) IS
481 SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_date
482 FROM per_all_assignments_f
483 WHERE assignment_id = p_asg_id;
484 --
485 l_min_start_date DATE;
486 l_max_end_date DATE;
487 --
488 CURSOR get_asg_details(p_asg_id IN NUMBER) IS
489 SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
490 FROM pay_all_payrolls_f pp,
491 per_all_assignments_f asg,
492 hr_soft_coding_keyflex flex
493 WHERE asg.assignment_id = p_asg_id
494 AND asg.payroll_id = pp.payroll_id
495 AND ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
496 AND pp.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
497 AND asg.effective_start_date < l_ext_end_date
498 AND asg.effective_end_date > ben_start_date(p_asg_id);
499 --
500 CURSOR check_asg_set_include(p_asg_id IN NUMBER) IS
501 SELECT 'Y' include_flag
502 FROM hr_assignment_set_amendments hasa,
503 hr_assignment_sets has,
504 per_all_assignments_f paaf
505 WHERE has.assignment_set_id = l_ext_asg_set_id
506 AND paaf.assignment_id = p_asg_id
507 -- AND ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
508 AND has.assignment_set_id = hasa.assignment_set_id (+)
509 AND NVL (hasa.assignment_id, paaf.assignment_id) = paaf.assignment_id
510 AND NVL (hasa.include_or_exclude, 'I') = 'I'
511 AND NVL (has.payroll_id, paaf.payroll_id) = paaf.payroll_id;
512 --
513
514 --
515 BEGIN
516
517 hr_utility.trace('Entering CHECK_ASG_INCLUSION, p_assignment_id='||p_assignment_id);
518
519 -- Get Business Group Id
520 l_bus_group_id := get_bus_group_id(p_assignment_id);
521
522 -- Get Input Parameter Values
523
524 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
525 l_ext_payroll_id := get_param_payroll_id(l_bus_group_id);
526 l_ext_person_id := get_param_person_id(l_bus_group_id);
527 l_ext_tax_dist := get_param_tax_dist(l_bus_group_id);
528 l_ext_con_set_id := get_param_consolidation_set_id(l_bus_group_id);
529 l_ext_asg_set_id := get_param_assignment_set_id(l_bus_group_id);
530
531 --
532
533 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_end_date='||to_char(l_ext_end_date, 'DD-MON-YYYY'));
534 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_payroll_id='||to_char(l_ext_payroll_id));
535 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_tax_dist='||l_ext_tax_dist);
536 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_con_set_id='||to_char(l_ext_con_set_id));
537 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_asg_set_id='||to_char(l_ext_asg_set_id));
538
539 --
540 -- Get details of primary assignment
541 -- Start with effective dates
542 OPEN get_asg_eff_dates(p_assignment_id);
543 FETCH get_asg_eff_dates INTO l_min_start_date, l_max_end_date;
544 CLOSE get_asg_eff_dates;
545 --
546 hr_utility.trace('CHECK_ASG_INCLUSION: l_min_start_date='||to_char(l_min_start_date, 'DD-MON-YYYY'));
547 hr_utility.trace('CHECK_ASG_INCLUSION: l_max_end_date='||to_char(l_max_end_date, 'DD-MON-YYYY'));
548 --
549
550 IF l_min_start_date > l_ext_end_date OR l_max_end_date < get_tax_year_start(l_bus_group_id) THEN
551 -- Person not active within input date range therefore exclude
552 RETURN 'N';
553 END IF;
554
555 -- Check if assignment is included in the input assignment set
556 IF l_ext_asg_set_id IS NOT NULL THEN
557 -- Get asg set include flag
558 OPEN check_asg_set_include(p_assignment_id);
559 FETCH check_asg_set_include INTO l_asg_set_include;
560 CLOSE check_asg_set_include;
561 --
562 ELSE
563 l_asg_set_include := 'Y'; -- no input asg set specified
564 END IF;
565
566 --
567 hr_utility.trace('CHECK_ASG_INCLUSION: l_asg_set_include='||l_asg_set_include);
568
569 -- Loop through all changes in the assignment during the input date range
570 FOR asg_det_rec IN get_asg_details(p_assignment_id) LOOP
571 --
572 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.payroll_id='||asg_det_rec.payroll_id);
573 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.person_id='||asg_det_rec.person_id);
574 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.tax_dist='||asg_det_rec.tax_dist);
575 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.consolidation_set_id='||asg_det_rec.consolidation_set_id);
576 --
577 IF (nvl(l_ext_payroll_id, nvl(asg_det_rec.payroll_id, -999)) = nvl(asg_det_rec.payroll_id, -999)
578 AND nvl(l_ext_person_id, nvl(asg_det_rec.person_id, -999)) = nvl(asg_det_rec.person_id, -999)
579 AND nvl(l_ext_tax_dist, nvl(asg_det_rec.tax_dist, 'ZZZ')) = nvl(asg_det_rec.tax_dist, 'ZZZ')
580 AND nvl(l_ext_con_set_id, nvl(asg_det_rec.consolidation_set_id, -999)) = nvl(asg_det_rec.consolidation_set_id, -999)
581 AND l_asg_set_include = 'Y') THEN
582
583 -- Assignment satisfies input criteria,
584 -- now check whether Mileage ASG_YTD balance > 0
585
586 l_mileage_balance := to_number(mileage_balance(p_assignment_id));
587
588 if l_mileage_balance > 0
589 then
590
591 l_asg_include := 'Y';
592
593 end if;
594
595 --
596 hr_utility.trace('CHECK_ASG_INCLUSION: In Loop, l_asg_include='||l_asg_include);
600 --
597 END IF;
598 END LOOP;
599
601 hr_utility.trace('Leaving CHECK_ASG_INCLUSION, l_asg_include='||l_asg_include);
602
603 RETURN l_asg_include;
604 --
605 END check_asg_inclusion;
606
607 ---------------------------------------------------------------------------
608 -- Function: GET_LATEST_ASG_ACT_EXT
609 -- Description: Gets latest assignment action id for a specific assignment
610 -- before extract end date
611 ---------------------------------------------------------------------------
612 function get_latest_asg_act_ext (p_assignment_id in number,
613 p_ext_end_date in date,
614 p_ben_start_date in date)
615 return pay_assignment_actions.assignment_action_id%type is
616
617 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
618
619 cursor csr_latest_asg_act_id is
620 select max(paa.assignment_action_id)
621 from per_time_periods ptp,
622 pay_payroll_actions pact,
623 pay_assignment_actions paa
624 where paa.assignment_id = p_assignment_id
625 and paa.payroll_action_id = pact.payroll_action_id
626 and pact.time_period_id = ptp.time_period_id
627 and pact.action_type in ('Q','R','B','I','V')
628 --and paa.action_status = 'C'
629 and paa.action_status in ('C','S') --Modified for the bug 10066755
630 and pact.effective_date <= p_ext_end_date
631 and ptp.regular_payment_date between p_ben_start_date
632 and p_ext_end_date;
633
634 begin
635
636 hr_utility.set_location('get_latest_asg_act_ext',1);
637
641
638 open csr_latest_asg_act_id;
639 fetch csr_latest_asg_act_id into l_asg_act_id;
640 close csr_latest_asg_act_id;
642 hr_utility.trace('Asg act id: '||l_asg_act_id);
643 hr_utility.set_location('get_latest_asg_act_ext',99);
644
645 return l_asg_act_id;
646
647 exception
648
649 when others then
650 hr_utility.set_location('get_latest_asg_act_ext',999);
651 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
652 raise;
653
654 end get_latest_asg_act_ext;
655
656 ---------------------------------------------------------------------------
657 -- Function: GET_LATEST_ASG_ACT_TYE
658 -- Description: Gets latest assignment action id for a specific assignment
659 -- as at Tax Year End
660 ---------------------------------------------------------------------------
661 function get_latest_asg_act_tye (p_assignment_id in number,
662 p_tax_year_end_date in date,
663 p_ben_start_date in date)
664 return pay_assignment_actions.assignment_action_id%type is
665
666 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
667
668 cursor csr_latest_asg_act_id is
669 select max(paa.assignment_action_id)
670 from per_time_periods ptp,
671 pay_payroll_actions pact,
672 pay_assignment_actions paa
673 where paa.assignment_id = p_assignment_id
674 and paa.payroll_action_id = pact.payroll_action_id
675 and pact.time_period_id = ptp.time_period_id
676 and pact.action_type in ('Q','R','B','I','V')
677 --and paa.action_status = 'C'
678 and paa.action_status in ('C','S') --Modified for the bug 10066755
679 and pact.effective_date <= p_tax_year_end_date
680 and ptp.regular_payment_date between p_ben_start_date
681 and p_tax_year_end_date;
682
683 begin
684
685 hr_utility.set_location('get_latest_asg_act_tye',1);
686
687 open csr_latest_asg_act_id;
688 fetch csr_latest_asg_act_id into l_asg_act_id;
689 close csr_latest_asg_act_id;
690
691 hr_utility.trace('Asg act id: '||l_asg_act_id);
692 hr_utility.set_location('get_latest_asg_act_tye',99);
693
694 return l_asg_act_id;
695
696 exception
697
698 when others then
699 hr_utility.set_location('get_latest_asg_act_tye',999);
700 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
701 raise;
702
703 end get_latest_asg_act_tye;
704
705 ---------------------------------------------------------------------------
706 -- Function: CHECK_ODD_EVEN_YEAR
707 -- Description: Returns 'ODD' or 'EVEN' depending on whether the year
708 -- for the supplied tax year end date is odd or even
709 ---------------------------------------------------------------------------
710 function check_odd_even_year (p_tax_year_end_date in date) return varchar2 is
711
712 l_odd_even_marker varchar2(5);
713
714 begin
715
716 hr_utility.set_location('check_odd_even_year',1);
717
718 hr_utility.trace('Tax Year End Date: '||p_tax_year_end_date);
719
720 if mod(to_number(to_char(p_tax_year_end_date,'YYYY')),2) = 0
721 then
722
723 l_odd_even_marker := 'EVEN';
724
725 else
726
727 l_odd_even_marker := 'ODD';
728
729 end if;
730
731 hr_utility.set_location('check_odd_even_year',99);
732 return l_odd_even_marker;
733
734 end check_odd_even_year;
735 ---------------------------------------------------------------------------
736 -- Function: BEN_START_DATE
737 -- Description: Function returns benefit start date for Mileage Claim/
738 -- Additional Passenger Claim assignments
739 ---------------------------------------------------------------------------
740
741 function ben_start_date (p_assignment_id in number) return varchar2 is
742
743 l_ben_start_date date;
744
745 l_bus_group_id number;
746
747 l_tax_year_start date;
748
749
750 cursor csr_ben_start_date is
751 select greatest(pps.date_start, l_tax_year_start)
752 from per_periods_of_service pps,
753 per_all_assignments_f paf
754 where paf.period_of_service_id = pps.period_of_service_id
755 and paf.assignment_id = p_assignment_id;
756
757 begin
758
759 hr_utility.set_location('ben_start_date',1);
760
761 l_bus_group_id := get_bus_group_id(p_assignment_id);
762 l_tax_year_start := get_tax_year_start(l_bus_group_id);
763
764 hr_utility.set_location('ben_start_date',2);
765
766 open csr_ben_start_date;
767 fetch csr_ben_start_date into l_ben_start_date;
768 close csr_ben_start_date;
769
770 hr_utility.trace('Benefit start date: '||to_char(l_ben_start_date,'DD-MON-YYYY'));
771 hr_utility.set_location('ben_start_date',99);
772
773 return to_char(l_ben_start_date,'DD-MON-YYYY');
774
775 exception
776
777 when others then
778 hr_utility.set_location('ben_start_date',999);
779 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
780 raise;
781
782 end ben_start_date;
783
784 ---------------------------------------------------------------------------
785 -- Function: BEN_END_DATE
786 -- Description: Function returns benefit end date for Mileage Claim/
787 -- Additional Passenger Claim assignments
788 ---------------------------------------------------------------------------
789 function ben_end_date (p_assignment_id in number) return varchar2 is
790
791 l_bus_group_id number;
792
793 l_ben_end_date date;
794 l_tax_year_end date;
795
796 cursor csr_ben_end_date is
797 select least(nvl(pps.actual_termination_date,hr_general.end_of_time),
798 l_tax_year_end)
799 from per_periods_of_service pps,
800 per_all_assignments_f paf
801 where paf.period_of_service_id = pps.period_of_service_id
802 and paf.assignment_id = p_assignment_id;
803
804 begin
805
806 hr_utility.set_location('ben_end_date',1);
807
808 l_bus_group_id := get_bus_group_id(p_assignment_id);
809 l_tax_year_end := get_tax_year_end(l_bus_group_id);
810
811 hr_utility.set_location('ben_end_date',2);
812
813 open csr_ben_end_date;
814 fetch csr_ben_end_date into l_ben_end_date;
815 close csr_ben_end_date;
816
817 hr_utility.trace('Benefit end date: '||to_char(l_ben_end_date,'DD-MON-YYYY'));
818 hr_utility.set_location('ben_end_date',99);
819
820 return to_char(l_ben_end_date,'DD-MON-YYYY');
821
822 exception
823
824 when others then
825 hr_utility.set_location('ben_end_date',999);
826 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
827 raise;
828
829 end ben_end_date;
830
831 ---------------------------------------------------------------------------
832 -- Function: ASG_START_DATE
833 -- Description: Function returns greater of assignment start date or TYS
834 ---------------------------------------------------------------------------
835 function asg_start_date (p_assignment_id in number) return varchar2 is
836
837 l_bus_group_id number;
838
839 l_asg_start_date date;
840 l_tax_year_start date;
841
842 cursor csr_asg_start_date is
843 select greatest(min(paf.effective_start_date),l_tax_year_start)
844 from per_all_assignments_f paf
845 where paf.assignment_id = p_assignment_id;
846
847 begin
848
849 hr_utility.set_location('asg_start_date',1);
850
851 l_bus_group_id := get_bus_group_id(p_assignment_id);
852 l_tax_year_start := get_tax_year_start(l_bus_group_id);
853
854 hr_utility.set_location('asg_start_date',2);
855
856 open csr_asg_start_date;
857 fetch csr_asg_start_date into l_asg_start_date;
858 close csr_asg_start_date;
859
860 hr_utility.trace('Asg start date: '||to_char(l_asg_start_date,'DD-MON-YYYY'));
861 hr_utility.set_location('asg_start_date',99);
862
863 return to_char(l_asg_start_date,'DD-MON-YYYY');
864
865 exception
866
867 when others then
868 hr_utility.set_location('asg_start_date',999);
869 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
870 raise;
871
872 end asg_start_date;
873
874
875 ---------------------------------------------------------------------------
876 -- Function: ASG_END_DATE
877 -- Description: Function returns lesser of assignment end date or TYE
878 ---------------------------------------------------------------------------
879 function asg_end_date (p_assignment_id in number) return varchar2 is
880
881 l_bus_group_id number;
882
883 l_asg_end_date date;
884 l_tax_year_end date;
885
886 cursor csr_asg_end_date is
887 select least(max(paf.effective_end_date),l_tax_year_end)
888 from per_all_assignments_f paf,
889 per_assignment_status_types past
890 where paf.assignment_id = p_assignment_id
891 and past.per_system_status = 'ACTIVE_ASSIGN'
892 and paf.assignment_status_type_id = past.assignment_status_type_id;
893
894 begin
895
896 hr_utility.set_location('asg_end_date',1);
897
898 l_bus_group_id := get_bus_group_id(p_assignment_id);
899 l_tax_year_end := get_tax_year_end(l_bus_group_id);
900
901 hr_utility.set_location('asg_end_date',2);
902
903 open csr_asg_end_date;
904 fetch csr_asg_end_date into l_asg_end_date;
905 close csr_asg_end_date;
906
907 hr_utility.trace('Asg end date: '||to_char(l_asg_end_date,'DD-MON-YYYY'));
908 hr_utility.set_location('asg_end_date',99);
909
910 return to_char(l_asg_end_date,'DD-MON-YYYY');
911
912 exception
913
914 when others then
915 hr_utility.set_location('asg_end_date',999);
916 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
917 raise;
918
919 end asg_end_date;
920
921
922 ---------------------------------------------------------------------------
923 -- Function: MILEAGE_BALANCE
924 -- Description: Function returns mileage balance for a specific assignment
925 ---------------------------------------------------------------------------
926 function mileage_balance (p_assignment_id in number) return varchar2 is
927
928 l_asg_act_id_tye pay_assignment_actions.assignment_action_id%type;
929 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
930
931 l_bus_group_id number;
932
933 l_ext_end_date date;
934
935 l_ben_start_date date;
936
937 l_def_bal_id pay_defined_balances.defined_balance_id%type;
938
939 cursor csr_get_odd_def_bal_id is
940 select pdb.defined_balance_id
941 from pay_balance_types pbt,
942 pay_defined_balances pdb,
943 pay_balance_dimensions pbd
944 where pbt.balance_type_id = pdb.balance_type_id
945 and pdb.balance_dimension_id = pbd.balance_dimension_id
946 and pbt.balance_name = 'Mileage Odd Taxable Amt'
947 and pbd.dimension_name = '_ASG_YTD';
948
949 cursor csr_get_even_def_bal_id is
950 select pdb.defined_balance_id
951 from pay_balance_types pbt,
952 pay_defined_balances pdb,
953 pay_balance_dimensions pbd
954 where pbt.balance_type_id = pdb.balance_type_id
955 and pdb.balance_dimension_id = pbd.balance_dimension_id
956 and pbt.balance_name = 'Mileage Even Taxable Amt'
957 and pbd.dimension_name = '_ASG_YTD';
958
959 l_odd_even_marker varchar2(5);
960
961 l_tax_year_end date;
962
963 l_tye_balance number;
964 l_ext_balance number;
965
966 l_balance_amt number;
967
968 begin
969
970 hr_utility.set_location('mileage_balance',1);
971
972 -- get necessary data
973
974 l_bus_group_id := get_bus_group_id(p_assignment_id);
975
976 hr_utility.set_location('mileage_balance',2);
977
978 l_tax_year_end := get_tax_year_end(l_bus_group_id);
979
980 hr_utility.set_location('mileage_balance',3);
981
982
983 -- check whether year in which tax year end date falls is odd or even
984
985 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
986
987 -- retrieve appropriate odd/even defined balance id for Mileage Taxable Amt
988 -- based on odd/even marker
989 hr_utility.set_location('mileage_balance',4);
990
991 if l_odd_even_marker = 'ODD'
992 then
993
994 open csr_get_odd_def_bal_id;
995 fetch csr_get_odd_def_bal_id into l_def_bal_id;
996 close csr_get_odd_def_bal_id;
997
998 else
999
1000 open csr_get_even_def_bal_id;
1001 fetch csr_get_even_def_bal_id into l_def_bal_id;
1002 close csr_get_even_def_bal_id;
1003
1004 end if;
1005
1006 hr_utility.set_location('mileage_balance',5);
1007
1008 -- get params to pass to get_latest_asg_act
1009
1010 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
1011
1012 hr_utility.set_location('mileage_balance',6);
1013
1014 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_assignment_id));
1015
1016 hr_utility.set_location('mileage_balance',7);
1017
1018 -- fetch latest assignment action ids for that assignment as at TYE and
1019 -- extract end date (or closest asg action to either)
1020
1021 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_assignment_id,
1022 p_ext_end_date => l_ext_end_date,
1023 p_ben_start_date => l_ben_start_date);
1024
1025 l_asg_act_id_tye := get_latest_asg_act_tye(p_assignment_id => p_assignment_id,
1026 p_tax_year_end_date => l_tax_year_end,
1027 p_ben_start_date => l_ben_start_date);
1028
1029 -- now get ASG_YTD balances for that assignment, using defined balance id
1030 -- and latest assignment actions just obtained
1031 hr_utility.set_location('mileage_balance',8);
1032
1033 l_ext_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1034 p_assignment_action_id => l_asg_act_id_ext);
1035
1036 l_tye_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1037 p_assignment_action_id => l_asg_act_id_tye);
1038
1039 hr_utility.trace('l_ext_balance: '||l_ext_balance);
1040 hr_utility.trace('l_tye_balance: '||l_tye_balance);
1041
1042 if l_tye_balance <> l_ext_balance
1043 -- e.g. if user has processed back-dated mileage claims after TYE so balance
1044 -- as of extract end date is greater
1045 then
1046
1047 l_balance_amt := l_tye_balance + l_ext_balance;
1048
1049 else
1050 -- just take the balance as at TYE or closest asg action to that
1051
1052 l_balance_amt := l_tye_balance;
1053
1054 end if;
1055
1056 hr_utility.set_location('mileage_balance',9);
1057 hr_utility.trace('Mileage Balance Amount: '||l_balance_amt);
1058 hr_utility.trace('Assignment id: '||p_assignment_id);
1059
1060 hr_utility.set_location('mileage_balance',99);
1061
1062 return to_char(l_balance_amt);
1063
1064 exception
1065
1066 when others then
1067 hr_utility.set_location('mileage_balance',999);
1068 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1069 raise;
1070
1071 end mileage_balance;
1072
1073 ---------------------------------------------------------------------------
1074 -- Function: ADD_PASS_BALANCE
1075 -- Description: Function returns addl passenger balance for a specific
1076 -- assignment
1077 ---------------------------------------------------------------------------
1078 function add_pass_balance (p_assignment_id in number) return varchar2 is
1079
1080 l_asg_act_id_tye pay_assignment_actions.assignment_action_id%type;
1081 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
1082
1083 l_bus_group_id number;
1084
1085 l_ext_end_date date;
1086
1087 l_ben_start_date date;
1088
1089 l_def_bal_id pay_defined_balances.defined_balance_id%type;
1090
1091 cursor csr_get_odd_def_bal_id is
1092 select pdb.defined_balance_id
1093 from pay_balance_types pbt,
1094 pay_defined_balances pdb,
1095 pay_balance_dimensions pbd
1096 where pbt.balance_type_id = pdb.balance_type_id
1097 and pdb.balance_dimension_id = pbd.balance_dimension_id
1098 and pbt.balance_name = 'Addl Pasg Odd Taxable Amt'
1099 and pbd.dimension_name = '_ASG_YTD';
1100
1101 cursor csr_get_even_def_bal_id is
1102 select pdb.defined_balance_id
1103 from pay_balance_types pbt,
1104 pay_defined_balances pdb,
1105 pay_balance_dimensions pbd
1106 where pbt.balance_type_id = pdb.balance_type_id
1107 and pdb.balance_dimension_id = pbd.balance_dimension_id
1108 and pbt.balance_name = 'Addl Pasg Even Taxable Amt'
1109 and pbd.dimension_name = '_ASG_YTD';
1110
1111 l_odd_even_marker varchar2(5);
1112
1113 l_tax_year_end date;
1114
1115 l_tye_balance number;
1116 l_ext_balance number;
1117
1118 l_balance_amt number;
1119
1120 begin
1121
1122 hr_utility.set_location('add_pass_balance',1);
1123
1127
1124 -- get necessary data
1125
1126 l_bus_group_id := get_bus_group_id(p_assignment_id);
1128 hr_utility.set_location('add_pass_balance',2);
1129
1130 l_tax_year_end := get_tax_year_end(l_bus_group_id);
1131
1132 hr_utility.set_location('add_pass_balance',3);
1133
1134 -- check whether year in which tax year end date falls is odd or even
1135
1136 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
1137
1138 -- retrieve appropriate odd/even defined balance id for Mileage Taxable Amt
1139 -- based on odd/even marker
1140 hr_utility.set_location('add_pass_balance',4);
1141
1142 if l_odd_even_marker = 'ODD'
1143 then
1144
1145 open csr_get_odd_def_bal_id;
1146 fetch csr_get_odd_def_bal_id into l_def_bal_id;
1147 close csr_get_odd_def_bal_id;
1148
1149 else
1150
1151 open csr_get_even_def_bal_id;
1152 fetch csr_get_even_def_bal_id into l_def_bal_id;
1153 close csr_get_even_def_bal_id;
1154
1155 end if;
1156
1157 hr_utility.set_location('add_pass_balance',5);
1158
1159 -- get params to pass to get_latest_asg_act
1160
1161 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
1162
1163 hr_utility.set_location('add_pass_balance',6);
1164
1165 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_assignment_id));
1166
1167 hr_utility.set_location('add_pass_balance',7);
1168
1169 -- fetch latest assignment action ids for that assignment as at TYE and
1170 -- extract end date (or closest asg act to either)
1171
1172 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_assignment_id,
1173 p_ext_end_date => l_ext_end_date,
1174 p_ben_start_date => l_ben_start_date);
1175
1176 l_asg_act_id_tye := get_latest_asg_act_tye(p_assignment_id => p_assignment_id,
1177 p_tax_year_end_date => l_tax_year_end,
1178 p_ben_start_date => l_ben_start_date);
1179
1180 -- now get ASG_YTD balances for that assignment, using defined balance id
1181 -- and latest assignment actions just obtained
1182 hr_utility.set_location('add_pass_balance',8);
1183
1184 l_ext_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1185 p_assignment_action_id => l_asg_act_id_ext);
1186
1187 l_tye_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1188 p_assignment_action_id => l_asg_act_id_tye);
1189
1190 hr_utility.trace('l_ext_balance: '||l_ext_balance);
1191 hr_utility.trace('l_tye_balance: '||l_tye_balance);
1192
1193 if l_tye_balance <> l_ext_balance
1194 -- e.g. if user has processed back-dated mileage claims after TYE so balance
1195 -- as of extract end date is greater
1196 then
1197
1198 l_balance_amt := l_tye_balance + l_ext_balance;
1199
1200 else
1201 -- just take the balance as at TYE or closest asg action to that
1202
1203 l_balance_amt := l_tye_balance;
1204
1205 end if;
1206
1207 hr_utility.set_location('add_pass_balance',9);
1208 hr_utility.trace('Addl Passenger Balance Amount: '||l_balance_amt);
1209 hr_utility.trace('Assignment id: '||p_assignment_id);
1210
1211 hr_utility.set_location('add_pass_balance',99);
1212
1213 return to_char(l_balance_amt);
1214
1215 exception
1216
1217 when others then
1218 hr_utility.set_location('mileage_balance',999);
1219 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1220 raise;
1221
1222 end add_pass_balance;
1223
1224 ---------------------------------------------------------------------------
1225 -- Function: CREATE_EXT_RSLT_DTL
1226 -- Description: Write new details records to extract table
1227 ---------------------------------------------------------------------------
1228 PROCEDURE create_ext_rslt_dtl(p_asg_id in number,
1229 p_asg_start_date in varchar2,
1230 p_asg_end_date in varchar2,
1231 p_benefit_start_date in varchar2,
1232 p_benefit_end_date in varchar2,
1233 p_vehicle_type in varchar2
1234 ) IS
1235 --
1236 l_ext_rslt_dtl_id NUMBER;
1237 l_object_version_no NUMBER;
1238 --
1239 CURSOR chk_exists IS
1240 select 1 from dual;
1241
1242 --
1243 l_chk_exists chk_exists%ROWTYPE;
1244
1245 cursor get_odd_miles_bal_id is
1246 select pdb.defined_balance_id
1247 from pay_balance_types pbt,
1248 pay_defined_balances pdb,
1249 pay_balance_dimensions pbd
1250 where pbt.balance_type_id = pdb.balance_type_id
1251 and pdb.balance_dimension_id = pbd.balance_dimension_id
1252 and pbt.balance_name in ('Car Casual Odd Actual Miles',
1253 'Motorcycle Casual Odd Actual Miles',
1257 'Pedalcycle Essential Odd Actual Miles')
1254 'Pedalcycle Casual Odd Actual Miles',
1255 'Car Essential Odd Actual Miles',
1256 'Motorcycle Essential Odd Actual Miles',
1258 and pbd.dimension_name = '_ASG_TD_YTD';
1259
1260 cursor get_even_miles_bal_id is
1261 select pdb.defined_balance_id
1262 from pay_balance_types pbt,
1263 pay_defined_balances pdb,
1264 pay_balance_dimensions pbd
1265 where pbt.balance_type_id = pdb.balance_type_id
1266 and pdb.balance_dimension_id = pbd.balance_dimension_id
1267 and pbt.balance_name in ('Car Casual Even Actual Miles',
1268 'Motorcycle Casual Even Actual Miles',
1269 'Pedalcycle Casual Even Actual Miles',
1270 'Car Essential Even Actual Miles',
1271 'Motorcycle Essential Even Actual Miles',
1272 'Pedalcycle Essential Even Actual Miles')
1273 and pbd.dimension_name = '_ASG_TD_YTD';
1274
1275 l_odd_even_marker varchar2(5);
1276 l_ext_end_date date;
1277 l_ben_start_date date;
1278 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
1279 l_miles number;
1280 l_miles_temp number;
1281 l_include varchar2(10);
1282 l_tax_year_end date;
1283 l_bus_group_id number;
1284 ---
1285 l_mileage_allowance_payments varchar2(10);
1286 l_tax_deducted_payments varchar2(10);
1287 l_business_miles varchar2(10);
1288 l_taxable_payments varchar2(10);
1289 l_passenger_payments varchar2(10);
1290 l_passenger_business_miles varchar2(10);
1291 l_taxable_passenger_payments varchar2(10);
1292 l_bal_return varchar2(10);
1293
1294 BEGIN
1295
1296 hr_utility.trace('Entering CREATE_EXT_RSLT_DTL: p_asg_id='|| p_asg_id);
1297 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_benefit_start_date='||p_benefit_start_date);
1298 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_benefit_end_date='||p_benefit_end_date);
1299 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_asg_start_date='||p_asg_start_date);
1300 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_asg_end_date='||p_asg_end_date);
1301 --
1302 l_bus_group_id := get_bus_group_id(p_asg_id);
1303
1304 l_tax_year_end := get_tax_year_end(l_bus_group_id);
1305
1306 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
1307
1308 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
1309
1310 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_asg_id));
1311
1312 -- fetch latest assignment action ids for that assignment as at TYE and
1313 -- extract end date (or closest asg action to either)
1314
1315 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_asg_id,
1316 p_ext_end_date => l_ext_end_date,
1317 p_ben_start_date => l_ben_start_date);
1318
1319 if l_odd_even_marker = 'ODD'
1320 then
1321
1322 l_miles := 0;
1323 l_miles_temp := 0;
1324 for l_miles_bal_id in get_odd_miles_bal_id
1325 loop
1326 l_miles_temp := pay_balance_pkg.get_value (p_defined_balance_id => l_miles_bal_id.defined_balance_id,
1327 p_assignment_action_id => l_asg_act_id_ext);
1328 l_miles := l_miles_temp + l_miles;
1329
1330 exit when l_miles>0;
1331
1332 end loop;
1333
1334 if (l_miles > 0) then
1335 l_include := 'TRUE';
1336 else
1337 l_include := 'FALSE';
1338 end if;
1339
1340 end if;
1341
1342 IF l_include = 'TRUE' THEN
1343 BEGIN
1344
1345 l_bal_return := get_mil_claim_values (p_asg_id
1346 ,p_vehicle_type
1347 ,l_mileage_allowance_payments
1348 ,l_tax_deducted_payments
1349 ,l_business_miles
1350 ,l_taxable_payments
1351 ,l_passenger_payments
1352 ,l_passenger_business_miles
1353 ,l_taxable_passenger_payments
1354 );
1355 null;
1356
1357 hr_utility.set_location('l_bal_return returned: '||l_bal_return,8);
1358
1359 ben_ext_rslt_dtl_api.create_ext_rslt_dtl( p_ext_rslt_dtl_id => l_ext_rslt_dtl_id
1360 ,p_ext_rslt_id => g_ext_rslt_id
1361 ,p_ext_rcd_id => g_veh_rcd_id
1362 ,p_person_id => g_person_id
1363 ,p_business_group_id => g_bg_id
1364 ,p_val_01 => 'A'
1365 ,p_val_02 => to_char(p_asg_id)
1366 ,p_val_03 => '~~~~~~~~~~~~~~~~~~~~~~~~~'
1367 ,p_val_04 => p_benefit_end_date
1368 ,p_val_05 => p_asg_start_date
1369 ,p_val_06 => p_asg_end_date
1370 ,p_val_07 => 'Mileage Allowance and PPayment'
1371 ,p_val_08 => '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
1372 ,p_val_09 => p_benefit_start_date
1373 ,p_val_10 => p_benefit_end_date
1374 ,p_val_11 => l_mileage_allowance_payments
1375 ,p_val_12 => l_tax_deducted_payments
1379 ,p_val_16 => l_passenger_payments
1376 ,p_val_13 => p_vehicle_type
1377 ,p_val_14 => l_business_miles
1378 ,p_val_15 => l_taxable_payments
1380 ,p_val_17 => l_passenger_business_miles
1381 ,p_val_18 => l_taxable_passenger_payments
1382 ,p_val_19 => '~~'
1383 ,p_val_20 => '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
1384 ,p_object_version_number => l_object_version_no);
1385
1386 hr_utility.trace('Wrote detail record: '||l_ext_rslt_dtl_id);
1387
1388 EXCEPTION WHEN others THEN
1389 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 1, 100));
1390 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 101, 100));
1391 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 201, 100));
1392 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 301, 100));
1393 RAISE;
1394 END;
1395 END IF;
1396 --
1397
1398 end create_ext_rslt_dtl;
1399
1400 ---------------------------------------------------------------------------
1401 -- Procedure: PROCESS_CAR_04
1402 -- Description: This procedure will create an extract result detail record
1403 -- for the vehicle type Car
1404 ---------------------------------------------------------------------------
1405 PROCEDURE process_car_04(p_asg_id IN NUMBER) IS
1406 --
1407 l_ext_start_date DATE := NULL;
1408 l_ext_end_date DATE := NULL;
1409 --
1410 l_asg_start_date DATE := NULL;
1411 l_asg_end_date DATE := NULL;
1412 --
1413 l_ben_start_date DATE := NULL;
1414 --
1415 CURSOR get_veh_id IS
1416 SELECT greatest(min(paaf.effective_start_date), l_ext_start_date) effective_start_date,
1417 least(max(paaf.effective_end_date), l_ext_end_date) effective_end_date
1418 FROM pqp_vehicle_allocations_f paaf,
1419 pqp_vehicle_repository_f pvd
1420 WHERE paaf.assignment_id = p_asg_id
1421 AND paaf.effective_start_date <= l_ext_end_date
1422 AND paaf.effective_end_date >= l_ext_start_date
1423 AND paaf.vehicle_repository_id is NOT NULL
1424 AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1425 AND pvd.vehicle_type = 'C'
1426 ORDER BY paaf.effective_start_date ;
1427
1428 --
1429 l_veh_id_rec get_veh_id%ROWTYPE;
1430 --
1431
1432 BEGIN
1433 hr_utility.trace('Entering PROCESS_CAR_04, p_asg_id='||p_asg_id);
1434 -- Get extract date parameter values
1435 l_ext_start_date := get_param_ext_start_date(get_bus_group_id(p_asg_id));
1436 l_ext_end_date := get_param_ext_end_date(get_bus_group_id(p_asg_id));
1437 -- Get Assignment Start and End Dates
1438 l_asg_start_date := fnd_date.displaydate_to_date(asg_start_date(p_asg_id));
1439 l_asg_end_date := fnd_date.displaydate_to_date(asg_end_date(p_asg_id));
1440 --
1441 OPEN get_veh_id;
1442 -- Fetch vehicle record for Car vehicle type and create extract result detail
1443
1444 FETCH get_veh_id INTO l_veh_id_rec;
1445 hr_utility.trace('PROCESS_CAR_04, After Fetch.');
1446 hr_utility.trace('PROCESS_CAR_04, effective_start_date='||to_char(l_veh_id_rec.effective_start_date, 'DD-MON-YYYY'));
1447 hr_utility.trace('PROCESS_CAR_04, effective_end_date='||to_char(l_veh_id_rec.effective_end_date, 'DD-MON-YYYY'));
1448
1449 --
1450 IF l_veh_id_rec.effective_start_date is null or l_veh_id_rec.effective_end_date is null THEN
1451 hr_utility.trace('PROCESS_CAR_04: No Car present for this assignment.');
1452 ELSE
1453 -- create reslt dtl record
1454 hr_utility.trace('PROCESS_CAR_04: Creating Car Vehicle Record for this assignment.');
1455 create_ext_rslt_dtl(p_asg_id, l_asg_start_date, l_asg_end_date,
1456 l_veh_id_rec.effective_start_date,
1457 l_veh_id_rec.effective_end_date,'Car or Van'
1458 );
1459 --
1460 END IF;
1461 --
1462 close get_veh_id;
1463 hr_utility.trace('Leaving PROCESS_CAR_04');
1464 --
1465 END process_car_04;
1466 ---------------------------------------------------------------------------
1467
1468 ---------------------------------------------------------------------------
1469 -- Procedure: PROCESS_MOTORCYCLE_04
1470 -- Description: This procedure will create an extract result detail record
1471 -- for the vehicle type Motorcycle
1472 ---------------------------------------------------------------------------
1473 PROCEDURE process_motorcycle_04(p_asg_id IN NUMBER) IS
1474 --
1475 l_ext_start_date DATE := NULL;
1476 l_ext_end_date DATE := NULL;
1477 --
1478 l_asg_start_date DATE := NULL;
1479 l_asg_end_date DATE := NULL;
1480 --
1481 l_ben_start_date DATE := NULL;
1482 --
1483 CURSOR get_veh_id IS
1484 SELECT greatest(min(paaf.effective_start_date), l_ext_start_date) effective_start_date,
1485 least(max(paaf.effective_end_date), l_ext_end_date) effective_end_date
1486 FROM pqp_vehicle_allocations_f paaf,
1487 pqp_vehicle_repository_f pvd
1488 WHERE paaf.assignment_id = p_asg_id
1489 AND paaf.effective_start_date <= l_ext_end_date
1490 AND paaf.effective_end_date >= l_ext_start_date
1491 AND paaf.vehicle_repository_id is NOT NULL
1492 AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1496 --
1493 AND pvd.vehicle_type = 'M'
1494 ORDER BY paaf.effective_start_date ;
1495
1497 l_veh_id_rec get_veh_id%ROWTYPE;
1498 --
1499
1500 BEGIN
1501 hr_utility.trace('Entering process_motorcycle_04, p_asg_id='||p_asg_id);
1502 -- Get extract date parameter values
1503 l_ext_start_date := get_param_ext_start_date(get_bus_group_id(p_asg_id));
1504 l_ext_end_date := get_param_ext_end_date(get_bus_group_id(p_asg_id));
1505 -- Get Assignment Start and End Dates
1506 l_asg_start_date := fnd_date.displaydate_to_date(asg_start_date(p_asg_id));
1507 l_asg_end_date := fnd_date.displaydate_to_date(asg_end_date(p_asg_id));
1508 --
1509 OPEN get_veh_id;
1510 -- Fetch vehicle record for Car vehicle type and create extract result detail
1511
1512 FETCH get_veh_id INTO l_veh_id_rec;
1513 hr_utility.trace('process_motorcycle_04, After Fetch.');
1514 hr_utility.trace('process_motorcycle_04, effective_start_date='||to_char(l_veh_id_rec.effective_start_date, 'DD-MON-YYYY'));
1515 hr_utility.trace('process_motorcycle_04, effective_end_date='||to_char(l_veh_id_rec.effective_end_date, 'DD-MON-YYYY'));
1516
1517 --
1518 --
1519 IF l_veh_id_rec.effective_start_date is null or l_veh_id_rec.effective_end_date is null THEN
1520 hr_utility.trace('PROCESS_MOTORCYCLE_04: No Motor Cycle present for this assignment.');
1521 ELSE
1522 -- create reslt dtl record
1523 hr_utility.trace('process_motorcycle_04: Creating Motorcycle Record for this assignment.');
1524 create_ext_rslt_dtl(p_asg_id, l_asg_start_date, l_asg_end_date,
1525 l_veh_id_rec.effective_start_date,
1526 l_veh_id_rec.effective_end_date,'Motorcycle'
1527 );
1528 --
1529 END IF;
1530 --
1531 close get_veh_id;
1532 hr_utility.trace('Leaving process_motorcycle_04');
1533 --
1534 END process_motorcycle_04;
1535 ---------------------------------------------------------------------------
1536
1537 ---------------------------------------------------------------------------
1538 -- Procedure: process_pedal_cycle_04
1539 -- Description: This procedure will create an extract result detail record
1540 -- for the vehicle type Motorcycle
1541 ---------------------------------------------------------------------------
1542 PROCEDURE process_pedal_cycle_04(p_asg_id IN NUMBER) IS
1543 --
1544 l_ext_start_date DATE := NULL;
1545 l_ext_end_date DATE := NULL;
1546 --
1547 l_asg_start_date DATE := NULL;
1548 l_asg_end_date DATE := NULL;
1549 --
1550 l_ben_start_date DATE := NULL;
1551 --
1552 CURSOR get_veh_id IS
1553 SELECT greatest(min(paaf.effective_start_date), l_ext_start_date) effective_start_date,
1554 least(max(paaf.effective_end_date), l_ext_end_date) effective_end_date
1555 FROM pqp_vehicle_allocations_f paaf,
1556 pqp_vehicle_repository_f pvd
1557 WHERE paaf.assignment_id = p_asg_id
1558 AND paaf.effective_start_date <= l_ext_end_date
1559 AND paaf.effective_end_date >= l_ext_start_date
1560 AND paaf.vehicle_repository_id is NOT NULL
1561 AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1562 AND pvd.vehicle_type = 'P'
1563 ORDER BY paaf.effective_start_date ;
1564
1565 --
1566 l_veh_id_rec get_veh_id%ROWTYPE;
1567 --
1568
1569 BEGIN
1570 hr_utility.trace('Entering process_pedal_cycle_04, p_asg_id='||p_asg_id);
1571 -- Get extract date parameter values
1572 l_ext_start_date := get_param_ext_start_date(get_bus_group_id(p_asg_id));
1573 l_ext_end_date := get_param_ext_end_date(get_bus_group_id(p_asg_id));
1574 -- Get Assignment Start and End Dates
1575 l_asg_start_date := fnd_date.displaydate_to_date(asg_start_date(p_asg_id));
1576 l_asg_end_date := fnd_date.displaydate_to_date(asg_end_date(p_asg_id));
1577 --
1578 OPEN get_veh_id;
1579 -- Fetch vehicle record for Car vehicle type and create extract result detail
1580
1581 FETCH get_veh_id INTO l_veh_id_rec;
1582 hr_utility.trace('process_pedal_cycle_04, After Fetch.');
1583 hr_utility.trace('process_pedal_cycle_04, effective_start_date='||to_char(l_veh_id_rec.effective_start_date, 'DD-MON-YYYY'));
1584 hr_utility.trace('process_pedal_cycle_04, effective_end_date='||to_char(l_veh_id_rec.effective_end_date, 'DD-MON-YYYY'));
1585
1586 --
1587 --
1588 IF l_veh_id_rec.effective_start_date is null or l_veh_id_rec.effective_end_date is null THEN
1589 hr_utility.trace('process_pedal_cycle_04: No Pedal Cycle present for this assignment.');
1590 ELSE
1591 -- create reslt dtl record
1592 hr_utility.trace('process_pedal_cycle_04: Creating Pedal cycle Record for this assignment.');
1593 create_ext_rslt_dtl(p_asg_id, l_asg_start_date, l_asg_end_date,
1594 l_veh_id_rec.effective_start_date,
1595 --l_veh_id_rec.effective_end_date,'Pedalcycle'
1596 l_veh_id_rec.effective_end_date,'Bicycle'
1597 );
1598 --
1599 END IF;
1600 --
1601 close get_veh_id;
1602 hr_utility.trace('Leaving process_pedal_cycle_04');
1603 --
1604 END process_pedal_cycle_04;
1605 ---------------------------------------------------------------------------
1606
1607 ---------------------------------------------------------------------------
1608 -- Function: PROCESS_SEC_ASG
1612 ---------------------------------------------------------------------------
1609 -- Description: Process secondary assignments. Check that assignment meets
1610 -- criteria, retrieve any balances and write new detail
1611 -- records
1613 PROCEDURE process_sec_asg(p_asg_id IN NUMBER) IS
1614 --
1615 CURSOR csr_sec_asg IS
1616 SELECT distinct paa1.assignment_id
1617 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1618 WHERE paa2.assignment_id = p_asg_id
1619 AND paa2.person_id = paa1.person_id
1620 AND nvl(paa1.primary_flag, 'N') = 'N';
1621 --
1622 l_asg_include VARCHAR2(1) := 'N';
1623
1624 l_ben_start_date varchar2(30);
1625 l_ben_end_date varchar2(30);
1626
1627 l_mileage_balance varchar2(30);
1628 l_add_pass_balance varchar2(30);
1629
1630 l_asg_start_date varchar2(30);
1631 l_asg_end_date varchar2(30);
1632 --
1633 --
1634 BEGIN
1635 hr_utility.set_location('process_sec_asg',1);
1636 hr_utility.trace('p_asg_id= '||p_asg_id);
1637 --
1638 -- Loop through all secondary assignments
1639 FOR sec_asg_rec IN csr_sec_asg LOOP
1640 --
1641 hr_utility.set_location('process_sec_asg',2);
1642 l_asg_include := 'N';
1643 l_asg_include := check_asg_inclusion(sec_asg_rec.assignment_id);
1644 --
1645 -- if assignment is eligible for inclusion call program to create detail record
1646 --
1647 IF l_asg_include = 'Y' THEN
1648
1649 hr_utility.set_location('process_sec_asg',3);
1650
1651 process_car_04(sec_asg_rec.assignment_id);
1652 process_motorcycle_04(sec_asg_rec.assignment_id);
1653 process_pedal_cycle_04(sec_asg_rec.assignment_id);
1654
1655 END IF;
1656
1657 END LOOP;
1658 --
1659 hr_utility.set_location('process_sec_asg',99);
1660
1661 exception
1662
1663 when others then
1664
1665 hr_utility.set_location('process_sec_asg',999);
1666 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1667 raise;
1668
1669 END process_sec_asg;
1670
1671 ---------------------------------------------------------------------------
1672 -- Function: PROCESS_TERM_PRIMARY_ASG
1673 -- Description: Process primary assignments that were terminated prior to
1674 -- the extract being run e.g. for rehires where primary assignment
1675 -- being passed in is the current primary assignment.
1676 -- Check that assignment meets criteria, retrieve any balances
1677 -- and write new detail records
1678 ---------------------------------------------------------------------------
1679 PROCEDURE process_term_primary_asg(p_asg_id IN NUMBER) IS
1680 --
1681 CURSOR csr_term_prim_asg IS
1682 SELECT distinct paa1.assignment_id
1683 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1684 WHERE paa2.assignment_id = p_asg_id
1685 AND paa2.person_id = paa1.person_id
1686 AND paa1.effective_end_date < paa2.effective_start_date
1687 -- AND paa1.period_of_service_id <> paa2.period_of_service_id
1688 AND nvl(paa1.primary_flag, 'Y') = 'Y';
1689 --
1690 l_asg_include VARCHAR2(1) := 'N';
1691
1692 l_ben_start_date varchar2(30);
1693 l_ben_end_date varchar2(30);
1694
1695 l_mileage_balance varchar2(30);
1696 l_add_pass_balance varchar2(30);
1697
1698 l_asg_start_date varchar2(30);
1699 l_asg_end_date varchar2(30);
1700 --
1701 BEGIN
1702 hr_utility.set_location('process_term_primary_asg',1);
1703 hr_utility.trace('p_asg_id= '||p_asg_id);
1704 --
1705 -- Loop through all terminated primary assignments
1706 FOR term_prim_asg_rec IN csr_term_prim_asg LOOP
1707 --
1708 hr_utility.set_location('process_term_primary_asg',2);
1709 l_asg_include := 'N';
1710 l_asg_include := check_asg_inclusion(term_prim_asg_rec.assignment_id);
1711 --
1712 -- if assignment is eligible for inclusion call program to create detail record
1713
1714 IF l_asg_include = 'Y' THEN
1715
1716 hr_utility.set_location('process_term_primary_asg',3);
1717
1718 process_car_04(term_prim_asg_rec.assignment_id);
1719 process_motorcycle_04(term_prim_asg_rec.assignment_id);
1720 process_pedal_cycle_04(term_prim_asg_rec.assignment_id);
1721
1722 END IF;
1723
1724 END LOOP;
1725 --
1726 hr_utility.set_location('process_term_primary_asg',99);
1727
1728 exception
1729
1730 when others then
1731
1732 hr_utility.set_location('process_term_primary_asg',999);
1733 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1734 raise;
1735
1736 END process_term_primary_asg;
1737
1738 ---------------------------------------------------------------------------
1739 -- Function: POST_PROCESS_RULE
1740 -- Description: Extract post-processing functionality. Processes any
1741 -- secondary assignments that meet the criteria, as well as
1742 -- cleaning up any unnecessary assignment and details records
1743 ---------------------------------------------------------------------------
1744 FUNCTION post_process_rule(p_ext_rslt_id IN NUMBER) RETURN VARCHAR2 IS
1745
1746 CURSOR csr_asg_record_id IS
1747 SELECT rcd.ext_rcd_id
1748 FROM ben_ext_rcd rcd,
1749 ben_ext_rcd_in_file rif,
1750 ben_ext_dfn dfn,
1751 ben_ext_rslt rslt
1752 WHERE rslt.ext_rslt_id = p_ext_rslt_id
1753 AND rslt.ext_dfn_id = dfn.ext_dfn_id
1754 AND dfn.ext_file_id = rif.ext_file_id
1758 AND rcd.name like '%PAY GB P11D Mileage Extract - Assignment Details Record';
1755 AND rif.ext_rcd_id = rcd.ext_rcd_id
1756 -- AND rcd.name like '%PAY GB P11D Mileage Extract 2003 - Assignment Details Record';
1757 -- AND rcd.name like '%PAY GB P11D Mileage Extract Test PV - Assignment Details Record';
1759 --
1760 l_asg_rcd_id NUMBER;
1761 --
1762 CURSOR csr_detail_record_id IS
1763 SELECT rcd.ext_rcd_id
1764 FROM ben_ext_rcd rcd,
1765 ben_ext_rcd_in_file rif,
1766 ben_ext_dfn dfn,
1767 ben_ext_rslt rslt
1768 WHERE rslt.ext_rslt_id = p_ext_rslt_id
1769 AND rslt.ext_dfn_id = dfn.ext_dfn_id
1770 AND dfn.ext_file_id = rif.ext_file_id
1771 AND rif.ext_rcd_id = rcd.ext_rcd_id
1772 -- AND rcd.name like '%PAY GB P11D Mileage Extract 2003 - Mileage and Passenger Record';
1773 -- AND rcd.name like '%PAY GB P11D Mileage Extract Test PV - Mileage and Passenger Record';
1774 AND rcd.name like '%PAY GB P11D Mileage Extract - Mileage and Passenger Record';
1775 --
1776 --
1777 CURSOR csr_ext_asg IS
1778 SELECT person_id, val_01 asg_id, ext_rslt_dtl_id, object_version_number
1779 FROM ben_ext_rslt_dtl
1780 WHERE ext_rslt_id = p_ext_rslt_id
1781 AND ext_rcd_id = l_asg_rcd_id;
1782 --
1783 l_obj_no NUMBER := NULL;
1784 --
1785 CURSOR csr_balance_detail(p_person_id IN NUMBER) IS
1786 SELECT *
1787 FROM ben_ext_rslt_dtl
1788 WHERE ext_rslt_id = p_ext_rslt_id
1789 AND ext_rcd_id = g_veh_rcd_id
1790 AND person_id = p_person_id;
1791 --
1792 l_balance_detail csr_balance_detail%ROWTYPE;
1793 --
1794 l_asg_include VARCHAR2(1);
1795 --
1796 BEGIN
1797
1798 hr_utility.trace('Entering POST_PROCESS_RULE, p_ext_rslt_id='||p_ext_rslt_id);
1799
1800 g_ext_rslt_id := p_ext_rslt_id;
1801
1802 -- Get assignment details record id
1803 OPEN csr_asg_record_id;
1804 FETCH csr_asg_record_id INTO l_asg_rcd_id;
1805 CLOSE csr_asg_record_id;
1806 --
1807 hr_utility.trace('POST_PROCESS_RULE: l_asg_rcd_id='||l_asg_rcd_id);
1808
1809 -- Get Balance Detail Record Id
1810 OPEN csr_detail_record_id;
1811 FETCH csr_detail_record_id INTO g_veh_rcd_id;
1812 CLOSE csr_detail_record_id;
1813 --
1814 hr_utility.trace('POST_PROCESS_RULE: g_veh_rcd_id='||g_veh_rcd_id);
1815
1816 -- Loop through all people extracted
1817 FOR ext_asg_rec IN csr_ext_asg LOOP
1818
1819 -- reset balance detail record at start of each loop
1820 l_balance_detail := null;
1821
1822 g_person_id := ext_asg_rec.person_id;
1823 g_bg_id := get_bus_group_id(ext_asg_rec.asg_id);
1824 --
1825 hr_utility.trace('POST_PROCESS_RULE: ext_asg_rec.asg_id='||ext_asg_rec.asg_id);
1826 hr_utility.trace('POST_PROCESS_RULE: g_person_id='||g_person_id);
1827
1828 -- Fetch in full detail record for primary assignment
1829 -- Delete balance detail record if mileage ASG_YTD balance is zero
1830 OPEN csr_balance_detail(ext_asg_rec.person_id);
1831 FETCH csr_balance_detail INTO l_balance_detail;
1832 CLOSE csr_balance_detail;
1833 --
1834 hr_utility.trace('POST_PROCESS_RULE: l_balance_detail.ext_rslt_dtl_id= '||l_balance_detail.ext_rslt_dtl_id);
1835 hr_utility.trace('POST_PROCESS_RULE: l_balance_detail.val_12(mileage balance)= '||l_balance_detail.val_12);
1836
1837 --
1838 l_asg_include := check_asg_inclusion(ext_asg_rec.asg_id);
1839
1840 IF l_balance_detail.ext_rslt_dtl_id IS NOT NULL AND l_asg_include = 'N'
1841 THEN
1842 -- Primary assignment does not qualify for extract
1843 -- Delete this detail record
1844 hr_utility.trace('Primary asg does not qualify for extract - deleting detail record');
1845 ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_balance_detail.ext_rslt_dtl_id,
1846 p_object_version_number => l_balance_detail.object_version_number);
1847
1848
1849 ELSIF l_asg_include = 'Y' THEN
1850 -- Include this Assignment
1851 process_car_04(ext_asg_rec.asg_id);
1852 process_motorcycle_04(ext_asg_rec.asg_id);
1853 process_pedal_cycle_04(ext_asg_rec.asg_id);
1854 hr_utility.trace('First l_balance_detail.ext_rslt_dtl_id: '||l_balance_detail.ext_rslt_dtl_id);
1855
1856 ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_balance_detail.ext_rslt_dtl_id,
1857 p_object_version_number => l_balance_detail.object_version_number);
1858
1859
1860 END IF;
1861
1862 --
1863 -- Process any terminated primary assignments which may qualify
1864 process_term_primary_asg(ext_asg_rec.asg_id);
1865 --
1866 -- Process any secondary assignments which may qualify
1867 process_sec_asg(ext_asg_rec.asg_id);
1868 --
1869 hr_utility.trace('POST_PROCESS_RULE: Assignment processed, remove it from the extract details table.');
1870 l_obj_no := ext_asg_rec.object_version_number;
1871
1872 -- Delete this assignment details record
1873 hr_utility.trace('This is not needed ext_asg_rec.ext_rslt_dtl_id: '||ext_asg_rec.ext_rslt_dtl_id);
1874 ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => ext_asg_rec.ext_rslt_dtl_id,
1875 p_object_version_number => l_obj_no);
1876
1877 --
1878 END LOOP;
1879 --
1880 hr_utility.trace('Leaving Post_process_rule.');
1881 RETURN 'Y';
1882 --
1883 exception
1884
1885 when others then
1886
1887 hr_utility.set_location('post_process_rule',999);
1888 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1889 raise;
1890
1891 end post_process_rule;
1892
1893
1894 ---------------------------------------------------------------------------
1895 -- Function: CHECK_PERSON_INCLUSION
1896 -- Description: This function checks all primary and secondary assignments
1897 -- for inclusion and returns Y if any of them should be
1898 -- included.
1899 ---------------------------------------------------------------------------
1900 FUNCTION check_person_inclusion(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
1901 --
1902 l_person_include VARCHAR2(1);
1903 --
1904 CURSOR get_all_asg IS
1905 SELECT distinct paa1.assignment_id
1906 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1907 WHERE paa2.assignment_id = p_assignment_id
1908 AND paa2.person_id = paa1.person_id
1909 ORDER BY paa1.assignment_id;
1910 --
1911 l_asg_id NUMBER;
1912
1913 BEGIN
1914
1915 hr_utility.trace('Entering CHECK_PERSON_INCLUSION, p_assignment_id='||p_assignment_id);
1916
1917 -- check whether any assignment qualifies
1918 OPEN get_all_asg;
1919 LOOP
1920
1921 FETCH get_all_asg INTO l_asg_id;
1922 IF get_all_asg%FOUND THEN
1923 l_person_include := check_asg_inclusion(l_asg_id);
1924 END IF;
1925 EXIT WHEN (get_all_asg%NOTFOUND OR l_person_include = 'Y');
1926
1927 END LOOP;
1928 --
1929 hr_utility.trace('Leaving CHECK_PERSON_INCLUSION, l_person_include='||l_person_include);
1930
1931 RETURN l_person_include;
1932 --
1933 END check_person_inclusion;
1934
1935 /*---------------------------------------------------------------------------
1936 -- Bug: 9536332 - P11D Mileage Extract process
1937 -- Function: get_data_element_value
1938 -- Description: Function returns --
1939 'include' if the record needs to be creaed
1940 'null' if the record need not be creaed
1941 ---------------------------------------------------------------------------*/
1942 function get_data_element_value (p_assignment_id in number
1943 ,p_rule_parameter in varchar2) return varchar2 is
1944
1945 CURSOR check_veh_exists(c_ext_start_date date, c_ext_end_date date) IS
1946 SELECT 1
1947 FROM pqp_vehicle_allocations_f paaf,
1948 pqp_vehicle_repository_f pvd
1949 WHERE paaf.assignment_id = p_assignment_id
1950 AND paaf.effective_start_date <= c_ext_end_date
1951 AND paaf.effective_end_date >= c_ext_start_date
1952 AND paaf.vehicle_repository_id is NOT NULL
1953 AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1954 AND pvd.vehicle_type in ('C','M','P')
1955 ORDER BY paaf.effective_start_date ;
1956
1957 l_check_veh_exists number;
1958 l_ext_start_date date;
1959 l_ext_end_date date;
1960 begin
1961
1962 hr_utility.trace('Entering get_data_element_value');
1963 hr_utility.trace('p_assignment_id= '|| p_assignment_id);
1964 hr_utility.trace('p_rule_parameter= '|| p_rule_parameter);
1965
1966 l_ext_end_date := get_param_ext_end_date(get_bus_group_id(p_assignment_id));
1967
1968 l_ext_start_date := get_param_ext_start_date(get_bus_group_id(p_assignment_id));
1969
1970 OPEN check_veh_exists(l_ext_start_date, l_ext_end_date);
1971 FETCH check_veh_exists
1972 INTO l_check_veh_exists;
1973
1974 if check_veh_exists%found then
1975 return 'Include';
1976 else
1977 return null;
1978 end if;
1979
1980 close check_veh_exists;
1981
1982 exception
1983
1984 when others then
1985 hr_utility.set_location('get_data_element_value',999);
1986 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1987 raise;
1988
1989 end get_data_element_value;
1990
1991 /*---------------------------------------------------------------------------
1992 -- Bug: 9536332 - P11D Mileage Extract process
1993 -- Function: get_mil_claim_values
1994 -- Description: Function returns mileage balance claim related values
1995 for a specific assignment
1996 ---------------------------------------------------------------------------*/
1997 function get_mil_claim_values (p_assignment_id in number
1998 ,p_vehicle_type in varchar2
1999 ,p_mileage_allowance_payments out nocopy varchar2
2000 ,p_tax_deducted_payments out nocopy varchar2
2001 ,p_business_miles out nocopy varchar2
2002 ,p_taxable_payments out nocopy varchar2
2003 ,p_passenger_payments out nocopy varchar2
2004 ,p_passenger_business_miles out nocopy varchar2
2005 ,p_taxable_passenger_payments out nocopy varchar2
2006 )
2007 return varchar2 is
2008
2009 l_asg_act_id_tye pay_assignment_actions.assignment_action_id%type;
2010 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
2011
2012 l_bus_group_id number;
2013
2014 l_ext_end_date date;
2015
2016 l_ben_start_date date;
2017
2018 l_odd_even_marker varchar2(5);
2019
2020 l_tax_year_end date;
2021
2022 l_map_bal_string varchar2(1000);
2023 l_tdp_bal_string varchar2(1000);
2024 l_bm_bal_string varchar2(1000);
2025 l_tp_bal_string varchar2(1000);
2026 l_pp_bal_string varchar2(1000);
2027 l_pbm_bal_string varchar2(1000);
2028 l_tpp_bal_string varchar2(1000);
2029
2030 TYPE r_def_bal IS RECORD
2031 (def_bal_id pay_defined_balances.defined_balance_id%type
2032 ,bal_name pay_balance_types.balance_name%type);
2033
2034 TYPE t_def_bal IS TABLE OF r_def_bal
2035 INDEX BY BINARY_INTEGER;
2036
2037 l_def_bal t_def_bal;
2038 begin
2039
2040 hr_utility.set_location('Entering get_mil_claim_values',1);
2041
2042 -- get necessary data
2043 l_bus_group_id := get_bus_group_id(p_assignment_id);
2044
2045 l_tax_year_end := get_tax_year_end(l_bus_group_id);
2046
2047 -- check whether year in which tax year end date falls is odd or even
2048
2049 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
2050
2051 -- get params to pass to get_latest_asg_act
2052 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
2053
2054 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_assignment_id));
2055
2056 -- fetch latest assignment action ids for that assignment as at TYE and
2057 -- extract end date (or closest asg action to either)
2058
2059 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_assignment_id,
2060 p_ext_end_date => l_ext_end_date,
2061 p_ben_start_date => l_ben_start_date);
2062
2063 l_asg_act_id_tye := get_latest_asg_act_tye(p_assignment_id => p_assignment_id,
2064 p_tax_year_end_date => l_tax_year_end,
2065 p_ben_start_date => l_ben_start_date);
2066
2067 -- now get ASG_TD_YTD balances for that assignment, using defined balance id
2068 -- and latest assignment actions just obtained
2069
2070 if l_odd_even_marker = 'ODD'
2074 --if (p_vehicle_type = 'Car')then
2071 then
2072 hr_utility.set_location('inside odd',8);
2073
2075 if (p_vehicle_type = 'Car or Van')then
2076 --car starts
2077 l_map_bal_string := '('||'''Car Casual Odd Processed Amt'''||','||
2078 '''Car Essential Odd Processed Amt'''||')';
2079
2080 l_tdp_bal_string := '('||'''Car Casual Paye Taxable Odd Amt'''||','||
2081 '''Car Essential Paye Taxable Odd Amt'''||')';
2082
2083 l_bm_bal_string := '('||'''Car Casual Odd Actual Miles'''||','||
2084 '''Car Essential Odd Actual Miles'''||')';
2085
2086 l_tp_bal_string := '('||'''Car Taxable Odd Amt'''||')';
2087
2088 l_pp_bal_string := '('||'''Car Company Passenger Odd Processed Amt'''||','||
2089 '''Car Private Passenger Odd Processed Amt'''||')';
2090
2091 l_pbm_bal_string := '('||'''Car Company Passenger Odd Actual Miles'''||','||
2092 '''Car Private Passenger Odd Actual Miles'''||')';
2093
2094 l_tpp_bal_string := '('||'''Car Private Passenger Odd Taxable Amt'''||','||
2095 '''Car Company Passenger Odd Taxable Amt'''||')';
2096
2097 --car ends
2098
2099 elsif (p_vehicle_type = 'Motorcycle')then
2100 --motorcycle starts
2101 l_map_bal_string := '('||'''Motorcycle Casual Odd Processed Amt'''||','||
2102 '''Motorcycle Essential Odd Processed Amt'''||')';
2103
2104 l_tdp_bal_string := '('||'''Motorcycle Casual Paye Taxable Odd Amt'''||','||
2105 '''Motorcycle Essential Paye Taxable Odd Amt'''||')';
2106
2107 l_bm_bal_string := '('||'''Motorcycle Casual Odd Actual Miles'''||','||
2108 '''Motorcycle Essential Odd Actual Miles'''||')';
2109
2110 l_tp_bal_string := '('||'''Motorcycle Taxable Odd Amt'''||')';
2111
2112 l_pp_bal_string := '('||'''Motorcycle Company Passenger Odd Processed Amt'''||','||
2113 '''Motorcycle Private Passenger Odd Processed Amt'''||')';
2114
2115 l_pbm_bal_string := '('||'''Motorcycle Company Passenger Odd Actual Miles'''||','||
2116 '''Motorcycle Private Passenger Odd Actual Miles'''||')';
2117
2118 l_tpp_bal_string := '('||'''Motorcycle Private Passenger Odd Taxable Amt'''||','||
2119 '''Motorcycle Company Passenger Odd Taxable Amt'''||')';
2120
2121 --motorcycle ends
2122
2123 --elsif (p_vehicle_type = 'Pedalcycle')then
2124 elsif (p_vehicle_type = 'Bicycle')then
2125 --pedalcycle starts
2126 l_map_bal_string := '('||'''Pedalcycle Casual Odd Processed Amt'''||','||
2127 '''Pedalcycle Essential Odd Processed Amt'''||')';
2128
2129 l_tdp_bal_string := '('||'''Pedalcycle Casual Paye Taxable Odd Amt'''||','||
2130 '''Pedalcycle Essential Paye Taxable Odd Amt'''||')';
2131
2132 l_bm_bal_string := '('||'''Pedalcycle Casual Odd Actual Miles'''||','||
2133 '''Pedalcycle Essential Odd Actual Miles'''||')';
2134
2135 l_tp_bal_string := '('||'''Pedalcycle Taxable Odd Amt'''||')';
2136
2137 l_pp_bal_string := null;
2138
2139 l_pbm_bal_string := null;
2140
2141 l_tpp_bal_string := null;
2142
2143 --pedalcycle ends
2144 end if;
2145
2146 -- l_odd_even_marker is EVEN
2147 else
2148 hr_utility.set_location('inside Even',8);
2149
2150 --if (p_vehicle_type = 'Car')then
2151 if (p_vehicle_type = 'Car or Van')then
2152 --car starts
2153
2154 l_map_bal_string := '('||'''Car Casual Even Processed Amt'''||','||
2155 '''Car Essential Even Processed Amt'''||')';
2156
2157 l_tdp_bal_string := '('||'''Car Casual Paye Taxable Even Amt'''||','||
2158 '''Car Essential Paye Taxable Even Amt'''||')';
2159
2160 l_bm_bal_string := '('||'''Car Casual Even Actual Miles'''||','||
2161 '''Car Essential Even Actual Miles'''||')';
2162
2163 l_tp_bal_string := '('||'''Car Taxable Even Amt'''||')';
2164
2165 l_pp_bal_string := '('||'''Car Company Passenger Even Processed Amt'''||','||
2166 '''Car Private Passenger Even Processed Amt'''||')';
2167
2168 l_pbm_bal_string := '('||'''Car Company Passenger Even Actual Miles'''||','||
2169 '''Car Private Passenger Even Actual Miles'''||')';
2170
2171 l_tpp_bal_string := '('||'''Car Private Passenger Even Taxable Amt'''||','||
2172 '''Car Company Passenger Even Taxable Amt'''||')';
2173
2174 --car ends
2175
2176 elsif (p_vehicle_type = 'Motorcycle')then
2177 --motorcycle starts
2178 l_map_bal_string := '('||'''Motorcycle Casual Even Processed Amt'''||','||
2179 '''Motorcycle Essential Even Processed Amt'''||')';
2180
2181 l_tdp_bal_string := '('||'''Motorcycle Casual Paye Taxable Even Amt'''||','||
2182 '''Motorcycle Essential Paye Taxable Even Amt'''||')';
2183
2184 l_bm_bal_string := '('||'''Motorcycle Casual Even Actual Miles'''||','||
2185 '''Motorcycle Essential Even Actual Miles'''||')';
2186
2187 l_tp_bal_string := '('||'''Motorcycle Taxable Even Amt'''||')';
2188
2189 l_pp_bal_string := '('||'''Motorcycle Company Passenger Even Processed Amt'''||','||
2190 '''Motorcycle Private Passenger Even Processed Amt'''||')';
2191
2192 l_pbm_bal_string := '('||'''Motorcycle Company Passenger Even Actual Miles'''||','||
2193 '''Motorcycle Private Passenger Even Actual Miles'''||')';
2194
2195 l_tpp_bal_string := '('||'''Motorcycle Private Passenger Even Taxable Amt'''||','||
2196 '''Motorcycle Company Passenger Even Taxable Amt'''||')';
2197
2198 --motorcycle ends
2199
2200 --elsif (p_vehicle_type = 'Pedalcycle')then
2201 elsif (p_vehicle_type = 'Bicycle')then
2202 --pedalcycle starts
2203 l_map_bal_string := '('||'''Pedalcycle Casual Evend Processed Amt'''||','||
2204 '''Pedalcycle Essential Odd Processed Amt'''||')';
2205
2206 l_tdp_bal_string := '('||'''Pedalcycle Casual Paye Taxable Even Amt'''||','||
2207 '''Pedalcycle Essential Paye Taxable Odd Amt'''||')';
2208
2209 l_bm_bal_string := '('||'''Pedalcycle Casual Even Actual Miles'''||','||
2213
2210 '''Pedalcycle Essential Odd Actual Miles'''||')';
2211
2212 l_tp_bal_string := '('||'''Pedalcycle Taxable Even Amt'''||')';
2214 l_pp_bal_string := null;
2215
2216 l_pbm_bal_string := null;
2217
2218 l_tpp_bal_string := null;
2219
2220 --pedalcycle ends
2221 end if;
2222
2223 end if;
2224 ----------
2225
2226 hr_utility.set_location('Before invoking get_bal_value',8);
2227 p_mileage_allowance_payments := get_bal_value (l_map_bal_string
2228 ,l_asg_act_id_ext
2229 ,l_asg_act_id_tye);
2230 hr_utility.set_location('p_mileage_allowance_payments: '||p_mileage_allowance_payments,8);
2231
2232 ---
2233 hr_utility.set_location('Before invoking get_bal_value',8);
2234 p_tax_deducted_payments := get_bal_value (l_tdp_bal_string
2235 ,l_asg_act_id_ext
2236 ,l_asg_act_id_tye);
2237 hr_utility.set_location('p_tax_deducted_payments: '||p_tax_deducted_payments,8);
2238
2239 --
2240 hr_utility.set_location('Before invoking get_bal_value',8);
2241 p_business_miles := get_bal_value (l_bm_bal_string
2242 ,l_asg_act_id_ext
2243 ,l_asg_act_id_tye);
2244 hr_utility.set_location('p_business_miles: '||p_business_miles,8);
2245
2246 --
2247 hr_utility.set_location('Before invoking get_bal_value',8);
2248 p_taxable_payments := get_bal_value (l_tp_bal_string
2249 ,l_asg_act_id_ext
2250 ,l_asg_act_id_tye);
2251 hr_utility.set_location('p_taxable_payments: '||p_taxable_payments,8);
2252
2253 --
2254 hr_utility.set_location('Before invoking get_bal_value',8);
2255 p_passenger_payments := get_bal_value (l_pp_bal_string
2256 ,l_asg_act_id_ext
2257 ,l_asg_act_id_tye);
2258 hr_utility.set_location('p_passenger_payments: '||p_passenger_payments,8);
2259
2260 --
2261 hr_utility.set_location('Before invoking get_bal_value',8);
2262 p_passenger_business_miles := get_bal_value (l_pbm_bal_string
2263 ,l_asg_act_id_ext
2264 ,l_asg_act_id_tye);
2265 hr_utility.set_location('p_passenger_business_miles: '||p_passenger_business_miles,8);
2266
2267 --
2268 hr_utility.set_location('Before invoking get_bal_value',8);
2269 p_taxable_passenger_payments := get_bal_value (l_tpp_bal_string
2270 ,l_asg_act_id_ext
2271 ,l_asg_act_id_tye);
2272 hr_utility.set_location('p_taxable_passenger_payments: '||p_taxable_passenger_payments,8);
2273
2274 return to_char(5000); -- This return value is just a dummy value
2275
2276 exception
2277
2278 when others then
2279 hr_utility.set_location('get_mil_claim_values',999);
2280 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
2281 raise;
2282
2283 end get_mil_claim_values;
2284
2285 /*---------------------------------------------------------------------------
2286 -- Bug: 9536332 - P11D Mileage Extract process
2287 -- Function: get_bal_value
2288 -- Description: Function returns balance values related
2289 to a specific assignment_action_id
2290 ---------------------------------------------------------------------------*/
2291
2292 function get_bal_value (p_bal_string in varchar2
2293 ,p_asg_act_id_ext in number
2294 ,p_asg_act_id_tye in number)
2295 return varchar2 is
2296
2297 l_tye_balance number;
2298 l_ext_balance number;
2299 l_balance_amt number;
2300 l_ext_balance_temp number;
2301 l_tye_balance_temp number;
2302 l_sql_stmt varchar2(2000);
2303
2304 TYPE r_def_bal IS RECORD
2305 (def_bal_id pay_defined_balances.defined_balance_id%type
2306 ,bal_name pay_balance_types.balance_name%type);
2307
2308 TYPE t_def_bal IS TABLE OF r_def_bal
2309 INDEX BY BINARY_INTEGER;
2310 l_def_bal t_def_bal;
2311
2312 begin
2313
2314 hr_utility.trace('Entering get_bal_value');
2315 hr_utility.trace('p_bal_string = '|| p_bal_string);
2316 hr_utility.trace('p_asg_act_id_ext = '|| p_asg_act_id_ext);
2317 hr_utility.trace('p_asg_act_id_tye = '|| p_asg_act_id_tye);
2318
2319 if p_bal_string is not null then
2320 l_ext_balance_temp := 0;
2321 l_ext_balance := 0;
2322
2323 l_tye_balance_temp := 0;
2324 l_tye_balance := 0;
2325
2326 l_sql_stmt :=
2327 'select pdb.defined_balance_id,
2328 pbt.balance_name
2329 from pay_balance_types pbt,
2330 pay_defined_balances pdb,
2331 pay_balance_dimensions pbd
2332 where pbt.balance_type_id = pdb.balance_type_id
2333 and pdb.balance_dimension_id = pbd.balance_dimension_id
2334 and pbd.dimension_name = '||'''_ASG_TD_YTD'''||
2335 'and pbt.balance_name in ' || p_bal_string ;
2336
2337 hr_utility.set_location('after l_sql_stmt: ',8);
2338
2339 EXECUTE IMMEDIATE l_sql_stmt bulk collect into l_def_bal;
2340
2341 if l_def_bal.FIRST is not null then
2342 FOR i in l_def_bal.FIRST .. l_def_bal.LAST
2343 LOOP
2344 l_ext_balance_temp := 0;
2345 l_tye_balance_temp := 0;
2346
2347 l_ext_balance_temp := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal(i).def_bal_id,
2348 p_assignment_action_id => p_asg_act_id_ext);
2349
2353 l_ext_balance := l_ext_balance + l_ext_balance_temp;
2350 l_tye_balance_temp := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal(i).def_bal_id,
2351 p_assignment_action_id => p_asg_act_id_tye);
2352
2354 l_tye_balance := l_tye_balance + l_tye_balance_temp;
2355
2356 END LOOP;
2357 end if;
2358
2359 hr_utility.trace('l_balance_amt: '||l_balance_amt );
2360 l_balance_amt := 0;
2361
2362 if l_tye_balance <> l_ext_balance
2363 -- e.g. if user has processed back-dated mileage claims after TYE so balance
2364 -- as of extract end date is greater
2365 then
2366 l_balance_amt := l_tye_balance + l_ext_balance;
2367 else
2368 l_balance_amt := l_tye_balance;
2369 end if;
2370
2371 end if;
2372
2373 if (l_balance_amt is null) then
2374 l_balance_amt := 0;
2375 end if;
2376
2377 return to_char(l_balance_amt);
2378 exception
2379
2380 when others then
2381 hr_utility.set_location('get_bal_value',999);
2382 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
2383 raise;
2384
2385 end get_bal_value;
2386
2387 /* end of package body */
2388 end pay_gb_p11d_mileage_extract;