[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P11D_MILEAGE_EXTRACT
Source
1 package body pay_gb_p11d_mileage_extract as
2 /* $Header: pygbmxpl.pkb 115.2 2003/05/06 11:01:42 gbutler noship $
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 Description
35 ------- ----- -------- ----------------
36
37 115.0 14/4/03 GBUTLER Created
38 115.2 06/5/03 GBUTLER Altered detail record layout
39
40 */
41
42 g_package_name varchar2(27) := 'pay_gb_p11d_mileage_extract';
43
44 -- Declare global variables:
45 g_veh_rcd_id NUMBER;
46 g_ext_rslt_id NUMBER;
47 g_person_id NUMBER;
48 g_bg_id NUMBER;
49
50 ---------------------------------------------------------------------------
51 -- Function: GET_BUS_GROUP_ID
52 -- Description: This function gets business group_id
53 ---------------------------------------------------------------------------
54 FUNCTION get_bus_group_id (p_asg_id IN NUMBER) RETURN NUMBER IS
55 --
56 CURSOR get_bus_group_id IS
57 SELECT business_group_id
58 FROM per_all_assignments_f
59 WHERE assignment_id = p_asg_id;
60 --
61 l_bus_group_id NUMBER;
62
63 BEGIN
64
65 -- Get Business Group ID
66 OPEN get_bus_group_id;
67 FETCH get_bus_group_id INTO l_bus_group_id;
68 CLOSE get_bus_group_id;
69 --
70 RETURN l_bus_group_id;
71 --
72
73 END get_bus_group_id;
74
75 ---------------------------------------------------------------------------
76 -- Function: GET_PARAM_TAX_YEAR
77 -- Description: This function gets tax year parameter from UDT
78 ---------------------------------------------------------------------------
79 FUNCTION get_param_tax_year (p_bus_grp_id IN NUMBER) return number is
80
81 l_tax_year number;
82
83 --
84 BEGIN
85
86 BEGIN
87 hr_utility.set_location('get_param_tax_year',1);
88
89 l_tax_year := hruserdt.get_table_value(p_bus_grp_id,
90 'PAY GB P11D Mileage Extract',
91 'Parameter Value',
92 'Tax Year (YYYY)',
93 ben_ext_person.g_effective_date );
94
95
96 EXCEPTION
97
98 WHEN others THEN
99 -- tax year parameter not set or wrong format
100 -- raise an error
101 hr_utility.set_location('get_param_tax_year',2);
102 hr_utility.trace('ERROR: Seeded value for tax year not found or data corrupt');
103 raise;
104
105 END;
106 --
107 hr_utility.set_location('get_param_tax_year',3);
108
109 hr_utility.trace('Tax year: '||to_char(l_tax_year));
110 hr_utility.set_location('get_param_tax_year',99);
111
112 RETURN l_tax_year;
113
114 end get_param_tax_year;
115
116
117 ---------------------------------------------------------------------------
118 -- Function: GET_TAX_YEAR_START
119 -- Description: This function gets tax year start date based on the
120 -- extract end date or effective date of the concurrent
121 -- process if extract end date is null
122 ---------------------------------------------------------------------------
123 FUNCTION get_tax_year_start (p_bus_grp_id IN NUMBER) return date is
124
125 l_tax_year_start date;
126
127 l_tax_year number;
128
129 l_cal_year_start date;
130 --
131 BEGIN
132
133 hr_utility.set_location('get_tax_year_start',1);
134
135 l_tax_year := get_param_tax_year(p_bus_grp_id);
136
137 hr_utility.set_location('get_tax_year_start',2);
138
139 l_cal_year_start := fnd_date.displaydate_to_date('01-JAN-'||to_char(l_tax_year));
140
141 l_tax_year_start := fnd_date.displaydate_to_date('06-APR-'||to_char(l_cal_year_start - 365, 'YYYY'));
142
143 hr_utility.trace('Tax year start: '||to_char(l_tax_year_start,'DD-MON-YYYY'));
144 hr_utility.set_location('get_tax_year_start',99);
145
146 RETURN l_tax_year_start;
147
148 end get_tax_year_start;
149
150 ---------------------------------------------------------------------------
151 -- Function: GET_TAX_YEAR_END
152 -- Description: Overloaded function gets tax year end date based on the
153 -- extract end date or effective date of the concurrent
154 -- process if extract end date is null
155 ---------------------------------------------------------------------------
156
157 FUNCTION get_tax_year_end (p_bus_grp_id IN NUMBER) return date is
158
159 l_tax_year_end date;
160
161 l_tax_year number;
162
163 l_cal_year_start date;
164 --
165 BEGIN
166
167 hr_utility.set_location('get_tax_year_end',1);
168
169 l_tax_year := get_param_tax_year(p_bus_grp_id);
170 --
171 hr_utility.set_location('get_tax_year_end',2);
172
173 l_cal_year_start := fnd_date.displaydate_to_date('01-JAN-'||to_char(l_tax_year));
174
175 l_tax_year_end := fnd_date.displaydate_to_date('05-APR-'||to_char(l_cal_year_start,'YYYY'));
176
177 hr_utility.trace('Tax year end: '||to_char(l_tax_year_end,'DD-MON-YYYY'));
178 hr_utility.set_location('get_tax_year_end',99);
179
180 RETURN l_tax_year_end;
181
182 end get_tax_year_end;
183
184
185 ---------------------------------------------------------------------------
186 -- Function: GET_PARAM_EXT_END_DATE
187 -- Description: This function gets value of extract end date parameter
188 ---------------------------------------------------------------------------
189 FUNCTION get_param_ext_end_date(p_bus_group_id IN NUMBER) RETURN DATE IS
190 --
191 l_ext_end_date DATE;
192 --
193 BEGIN
194
195 BEGIN
196 hr_utility.set_location('get_param_ext_end_date',1);
197
198 l_ext_end_date := fnd_date.displaydate_to_date(hruserdt.get_table_value(p_bus_group_id,
199 'PAY GB P11D Mileage Extract',
200 'Parameter Value',
201 'Extract End Date (DD-MON-YYYY)',
202 ben_ext_person.g_effective_date ));
203
204
205 l_ext_end_date := nvl(l_ext_end_date,get_tax_year_end(p_bus_group_id));
206
207 EXCEPTION
208
209 WHEN others THEN
210 -- extract date parameter not set or wrong format
211 -- default to end of fiscal year
212 hr_utility.set_location('get_param_ext_end_date',2);
213
214 l_ext_end_date := get_tax_year_end(p_bus_group_id);
215
216 END;
217 --
218 hr_utility.set_location('get_param_ext_end_date',99);
219
220 RETURN l_ext_end_date;
221
222 END get_param_ext_end_date;
223
224
225 ---------------------------------------------------------------------------
226 -- Function: GET_PARAM_PAYROLL_ID
227 -- Description: This function gets id of payroll name parameter
228 ---------------------------------------------------------------------------
229 FUNCTION get_param_payroll_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
230 --
231 l_payroll_name pay_all_payrolls_f.payroll_name%TYPE;
232 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
233 --
234 CURSOR get_payroll_id IS
235 SELECT payroll_id
239 AND ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;
236 FROM pay_all_payrolls_f
237 WHERE payroll_name = l_payroll_name
238 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id
240 --
241 BEGIN
242
243 -- Get user Table Value
244
245 BEGIN
246
247 -- Get Payroll Name Parameter Value
248 l_payroll_name := hruserdt.get_table_value(p_bus_group_id,
249 'PAY GB P11D Mileage Extract',
250 'Parameter Value',
251 'Payroll Name',
252 ben_ext_person.g_effective_date );
253 --
254 -- Get Payroll_id
255 OPEN get_payroll_id;
256 FETCH get_payroll_id INTO l_payroll_id;
257 CLOSE get_payroll_id;
258 --
259 EXCEPTION
260
261 WHEN others THEN
262 l_payroll_name := NULL;
263 l_payroll_id := NULL;
264 END;
265 --
266 RETURN l_payroll_id;
267 --
268 END get_param_payroll_id;
269
270 ---------------------------------------------------------------------------
271 -- Function: GET_PARAM_TAX_DIST
272 -- Description: This function gets value of Tax District Reference
273 -- parameter
274 ---------------------------------------------------------------------------
275 FUNCTION get_param_tax_dist(p_bus_group_id IN NUMBER) RETURN VARCHAR2 IS
276 --
277 l_tax_dist hr_organization_information.org_information1%TYPE;
278 --
279 BEGIN
280 BEGIN
281 -- Get Tax District Reference Parameter Value
282 l_tax_dist := hruserdt.get_table_value(p_bus_group_id,
283 'PAY GB P11D Mileage Extract',
284 'Parameter Value',
285 'Tax District Reference',
286 ben_ext_person.g_effective_date );
287 --
288 EXCEPTION
289 WHEN others THEN
290 l_tax_dist := NULL;
291 END;
292 --
293 RETURN l_tax_dist;
294 --
295 END get_param_tax_dist;
296
297 ---------------------------------------------------------------------------
298 -- Function: GET_PARAM_PERSON_ID
299 -- Description: This function gets person id based on employee number
300 -- parameter
301 ---------------------------------------------------------------------------
302 FUNCTION get_param_person_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
303 --
304 l_employee_number per_all_people_f.employee_number%TYPE;
305 l_person_id per_all_people_f.person_id%TYPE;
306 --
307 CURSOR get_person_id IS
308 SELECT person_id
309 FROM per_all_people_f
310 WHERE employee_number = l_employee_number
311 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id
312 AND ben_ext_person.g_effective_Date BETWEEN effective_start_date AND effective_end_Date;
313 --
314 BEGIN
315 BEGIN
316 -- Get Employee Number Parameter Value
317 l_employee_number := hruserdt.get_table_value(p_bus_group_id,
318 'PAY GB P11D Mileage Extract',
319 'Parameter Value',
320 'Employee Number',
321 ben_ext_person.g_effective_date );
322 --
323 -- Get Person Id
324 OPEN get_person_id;
325 FETCH get_person_id INTO l_person_id;
326 CLOSE get_person_id;
327 --
328 EXCEPTION
329 WHEN others THEN
330 l_employee_number := NULL;
331 l_person_id := NULL;
332 END;
333 --
334 RETURN l_person_id;
335 --
336 END get_param_person_id;
337
338 ---------------------------------------------------------------------------
339 -- Function: GET_PARAM_CONSOLIDATION_SET_ID
340 -- Description: This function gets consolidation set id based on
341 -- consolidation set parameter
342 ---------------------------------------------------------------------------
343 FUNCTION get_param_consolidation_set_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
344 --
345 l_consolidation_set pay_consolidation_sets.consolidation_set_name%TYPE;
346 l_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE;
347 --
348 CURSOR get_consolidation_set_id IS
349 SELECT consolidation_set_id
350 FROM pay_consolidation_sets
351 WHERE consolidation_Set_name = l_consolidation_set
352 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id;
353 --
354 BEGIN
355 BEGIN
356 -- Get Consolidation Set Parameter Value
357 l_consolidation_set := hruserdt.get_table_value(p_bus_group_id,
358 'PAY GB P11D Mileage Extract',
359 'Parameter Value',
360 'Consolidation Set',
361 ben_ext_person.g_effective_date );
362 --
363 -- Get Consolidation Set Id
364 OPEN get_consolidation_set_id;
365 FETCH get_consolidation_set_id INTO l_consolidation_set_id;
366 CLOSE get_consolidation_set_id;
367 --
368 EXCEPTION
369 WHEN others THEN
370 l_consolidation_set := NULL;
371 l_consolidation_set_id := NULL;
372 END;
373 --
374 RETURN l_consolidation_set_id;
375 --
376 END get_param_consolidation_set_id;
377
378
379 ---------------------------------------------------------------------------
383 ---------------------------------------------------------------------------
380 -- Function: GET_PARAM_ASSIGNMENT_SET_ID
381 -- Description: This function gets assignment set id based on
382 -- assignment set parameter
384 FUNCTION get_param_assignment_set_id(p_bus_group_id IN NUMBER) RETURN NUMBER IS
385 --
386 l_assignment_set hr_assignment_sets.assignment_set_name%TYPE;
387 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
388 --
389 CURSOR get_assignment_set_id IS
390 SELECT assignment_set_id
391 FROM hr_assignment_sets
392 WHERE assignment_set_name = l_assignment_set
393 AND nvl(business_group_id, p_bus_group_id) = p_bus_group_id;
394 --
395 BEGIN
396 BEGIN
397 -- Get Assignment Set Parameter Value
398 l_assignment_set := hruserdt.get_table_value(p_bus_group_id,
399 'PAY GB P11D Mileage Extract',
400 'Parameter Value',
401 'Assignment Set',
402 ben_ext_person.g_effective_date );
403 --
404 -- Get Assignment Set Id
405 OPEN get_assignment_set_id;
406 FETCH get_assignment_set_id INTO l_assignment_set_id;
407 CLOSE get_assignment_set_id;
408 --
409 EXCEPTION
410 WHEN others THEN
411 l_assignment_set := NULL;
412 l_assignment_set_id := NULL;
413 END;
414 --
415 RETURN l_assignment_set_id;
416 --
417 END get_param_assignment_set_id;
418
419
420
421 ---------------------------------------------------------------------------
422 -- Function: CHECK_ASG_INCLUSION
423 -- Description: This function checks whether given assignment satisfies
424 -- input criteria and mileage ASG_YTD > 0
425 ---------------------------------------------------------------------------
426 FUNCTION check_asg_inclusion(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
427 --
428 l_bus_group_id NUMBER;
429
430 l_ext_end_Date DATE;
431 l_ext_payroll_id NUMBER;
432 l_ext_person_id NUMBER;
433 l_ext_tax_dist VARCHAR2(150);
434 l_ext_con_set_id NUMBER;
435 l_ext_asg_set_id NUMBER;
436 --
437 l_asg_set_include VARCHAR2(1) := 'N';
438 --
439 l_asg_include VARCHAR2(1) := 'N';
440 --
441 l_mileage_balance NUMBER;
442 --
443 CURSOR get_asg_eff_dates(p_asg_id IN NUMBER) IS
444 SELECT min(effective_start_date) min_start_date, max(effective_end_date) max_end_date
445 FROM per_all_assignments_f
446 WHERE assignment_id = p_asg_id;
447 --
448 l_min_start_date DATE;
449 l_max_end_date DATE;
450 --
451 CURSOR get_asg_details(p_asg_id IN NUMBER) IS
452 SELECT pp.payroll_id, asg.person_id, pp.consolidation_set_id, flex.segment1 tax_dist
453 FROM pay_all_payrolls_f pp,
454 per_all_assignments_f asg,
455 hr_soft_coding_keyflex flex
456 WHERE asg.assignment_id = p_asg_id
457 AND asg.payroll_id = pp.payroll_id
458 AND ben_ext_person.g_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
459 AND pp.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
460 AND asg.effective_start_date < l_ext_end_date
461 AND asg.effective_end_date > ben_start_date(p_asg_id);
462 --
463 CURSOR check_asg_set_include(p_asg_id IN NUMBER) IS
464 SELECT 'Y' include_flag
465 FROM hr_assignment_set_amendments hasa,
466 hr_assignment_sets has,
467 per_all_assignments_f paaf
468 WHERE has.assignment_set_id = l_ext_asg_set_id
469 AND paaf.assignment_id = p_asg_id
470 -- AND ben_ext_person.g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
471 AND has.assignment_set_id = hasa.assignment_set_id (+)
472 AND NVL (hasa.assignment_id, paaf.assignment_id) = paaf.assignment_id
473 AND NVL (hasa.include_or_exclude, 'I') = 'I'
474 AND NVL (has.payroll_id, paaf.payroll_id) = paaf.payroll_id;
475 --
476
477 --
478 BEGIN
479
480 hr_utility.trace('Entering CHECK_ASG_INCLUSION, p_assignment_id='||p_assignment_id);
481
482 -- Get Business Group Id
483 l_bus_group_id := get_bus_group_id(p_assignment_id);
484
485 -- Get Input Parameter Values
486
487 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
488 l_ext_payroll_id := get_param_payroll_id(l_bus_group_id);
489 l_ext_person_id := get_param_person_id(l_bus_group_id);
490 l_ext_tax_dist := get_param_tax_dist(l_bus_group_id);
491 l_ext_con_set_id := get_param_consolidation_set_id(l_bus_group_id);
492 l_ext_asg_set_id := get_param_assignment_set_id(l_bus_group_id);
493
494 --
495
496 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_end_date='||to_char(l_ext_end_date, 'DD-MON-YYYY'));
497 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_payroll_id='||to_char(l_ext_payroll_id));
498 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_tax_dist='||l_ext_tax_dist);
499 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_con_set_id='||to_char(l_ext_con_set_id));
500 hr_utility.trace('CHECK_ASG_INCLUSION: l_ext_asg_set_id='||to_char(l_ext_asg_set_id));
501
502 --
503 -- Get details of primary assignment
504 -- Start with effective dates
505 OPEN get_asg_eff_dates(p_assignment_id);
506 FETCH get_asg_eff_dates INTO l_min_start_date, l_max_end_date;
507 CLOSE get_asg_eff_dates;
508 --
509 hr_utility.trace('CHECK_ASG_INCLUSION: l_min_start_date='||to_char(l_min_start_date, 'DD-MON-YYYY'));
510 hr_utility.trace('CHECK_ASG_INCLUSION: l_max_end_date='||to_char(l_max_end_date, 'DD-MON-YYYY'));
511 --
512
516 END IF;
513 IF l_min_start_date > l_ext_end_date OR l_max_end_date < get_tax_year_start(l_bus_group_id) THEN
514 -- Person not active within input date range therefore exclude
515 RETURN 'N';
517
518 -- Check if assignment is included in the input assignment set
519 IF l_ext_asg_set_id IS NOT NULL THEN
520 -- Get asg set include flag
521 OPEN check_asg_set_include(p_assignment_id);
522 FETCH check_asg_set_include INTO l_asg_set_include;
523 CLOSE check_asg_set_include;
524 --
525 ELSE
526 l_asg_set_include := 'Y'; -- no input asg set specified
527 END IF;
528
529 --
530 hr_utility.trace('CHECK_ASG_INCLUSION: l_asg_set_include='||l_asg_set_include);
531
532 -- Loop through all changes in the assignment during the input date range
533 FOR asg_det_rec IN get_asg_details(p_assignment_id) LOOP
534 --
535 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.payroll_id='||asg_det_rec.payroll_id);
536 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.person_id='||asg_det_rec.person_id);
537 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.tax_dist='||asg_det_rec.tax_dist);
538 hr_utility.trace('CHECK_ASG_INCLUSION: asg_det_rec.consolidation_set_id='||asg_det_rec.consolidation_set_id);
539 --
540 IF (nvl(l_ext_payroll_id, nvl(asg_det_rec.payroll_id, -999)) = nvl(asg_det_rec.payroll_id, -999)
541 AND nvl(l_ext_person_id, nvl(asg_det_rec.person_id, -999)) = nvl(asg_det_rec.person_id, -999)
542 AND nvl(l_ext_tax_dist, nvl(asg_det_rec.tax_dist, 'ZZZ')) = nvl(asg_det_rec.tax_dist, 'ZZZ')
543 AND nvl(l_ext_con_set_id, nvl(asg_det_rec.consolidation_set_id, -999)) = nvl(asg_det_rec.consolidation_set_id, -999)
544 AND l_asg_set_include = 'Y') THEN
545
546 -- Assignment satisfies input criteria,
547 -- now check whether Mileage ASG_YTD balance > 0
548
549 l_mileage_balance := to_number(mileage_balance(p_assignment_id));
550
551 if l_mileage_balance > 0
552 then
553
554 l_asg_include := 'Y';
555
556 end if;
557
558 --
559 hr_utility.trace('CHECK_ASG_INCLUSION: In Loop, l_asg_include='||l_asg_include);
560 END IF;
561 END LOOP;
562
563 --
564 hr_utility.trace('Leaving CHECK_ASG_INCLUSION, l_asg_include='||l_asg_include);
565
566 RETURN l_asg_include;
567 --
568 END check_asg_inclusion;
569
570 ---------------------------------------------------------------------------
571 -- Function: GET_LATEST_ASG_ACT_EXT
572 -- Description: Gets latest assignment action id for a specific assignment
573 -- before extract end date
574 ---------------------------------------------------------------------------
575 function get_latest_asg_act_ext (p_assignment_id in number,
576 p_ext_end_date in date,
577 p_ben_start_date in date)
578 return pay_assignment_actions.assignment_action_id%type is
579
580 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
581
582 cursor csr_latest_asg_act_id is
583 select max(paa.assignment_action_id)
584 from per_time_periods ptp,
585 pay_payroll_actions pact,
586 pay_assignment_actions paa
587 where paa.assignment_id = p_assignment_id
588 and paa.payroll_action_id = pact.payroll_action_id
589 and pact.time_period_id = ptp.time_period_id
590 and pact.action_type in ('Q','R','B','I','V')
591 and paa.action_status = 'C'
592 and pact.effective_date <= p_ext_end_date
593 and ptp.regular_payment_date between p_ben_start_date
594 and p_ext_end_date;
595
596 begin
597
598 hr_utility.set_location('get_latest_asg_act_ext',1);
599
600 open csr_latest_asg_act_id;
601 fetch csr_latest_asg_act_id into l_asg_act_id;
602 close csr_latest_asg_act_id;
603
604 hr_utility.trace('Asg act id: '||l_asg_act_id);
605 hr_utility.set_location('get_latest_asg_act_ext',99);
606
607 return l_asg_act_id;
608
609 exception
610
611 when others then
612 hr_utility.set_location('get_latest_asg_act_ext',999);
613 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
614 raise;
615
616 end get_latest_asg_act_ext;
617
618 ---------------------------------------------------------------------------
619 -- Function: GET_LATEST_ASG_ACT_TYE
620 -- Description: Gets latest assignment action id for a specific assignment
621 -- as at Tax Year End
622 ---------------------------------------------------------------------------
623 function get_latest_asg_act_tye (p_assignment_id in number,
624 p_tax_year_end_date in date,
625 p_ben_start_date in date)
626 return pay_assignment_actions.assignment_action_id%type is
627
628 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
629
630 cursor csr_latest_asg_act_id is
631 select max(paa.assignment_action_id)
632 from per_time_periods ptp,
633 pay_payroll_actions pact,
634 pay_assignment_actions paa
635 where paa.assignment_id = p_assignment_id
636 and paa.payroll_action_id = pact.payroll_action_id
637 and pact.time_period_id = ptp.time_period_id
638 and pact.action_type in ('Q','R','B','I','V')
639 and paa.action_status = 'C'
640 and pact.effective_date <= p_tax_year_end_date
641 and ptp.regular_payment_date between p_ben_start_date
642 and p_tax_year_end_date;
643
644 begin
645
646 hr_utility.set_location('get_latest_asg_act_tye',1);
647
648 open csr_latest_asg_act_id;
649 fetch csr_latest_asg_act_id into l_asg_act_id;
653 hr_utility.set_location('get_latest_asg_act_tye',99);
650 close csr_latest_asg_act_id;
651
652 hr_utility.trace('Asg act id: '||l_asg_act_id);
654
655 return l_asg_act_id;
656
657 exception
658
659 when others then
660 hr_utility.set_location('get_latest_asg_act_tye',999);
661 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
662 raise;
663
664 end get_latest_asg_act_tye;
665
666 ---------------------------------------------------------------------------
667 -- Function: CHECK_ODD_EVEN_YEAR
668 -- Description: Returns 'ODD' or 'EVEN' depending on whether the year
669 -- for the supplied tax year end date is odd or even
670 ---------------------------------------------------------------------------
671 function check_odd_even_year (p_tax_year_end_date in date) return varchar2 is
672
673 l_odd_even_marker varchar2(5);
674
675 begin
676
677 hr_utility.set_location('check_odd_even_year',1);
678
679 hr_utility.trace('Tax Year End Date: '||p_tax_year_end_date);
680
681 if mod(to_number(to_char(p_tax_year_end_date,'YYYY')),2) = 0
682 then
683
684 l_odd_even_marker := 'EVEN';
685
686 else
687
688 l_odd_even_marker := 'ODD';
689
690 end if;
691
692 hr_utility.set_location('check_odd_even_year',99);
693 return l_odd_even_marker;
694
695 end check_odd_even_year;
696 ---------------------------------------------------------------------------
697 -- Function: BEN_START_DATE
698 -- Description: Function returns benefit start date for Mileage Claim/
699 -- Additional Passenger Claim assignments
700 ---------------------------------------------------------------------------
701
702 function ben_start_date (p_assignment_id in number) return varchar2 is
703
704 l_ben_start_date date;
705
706 l_bus_group_id number;
707
708 l_tax_year_start date;
709
710
711 cursor csr_ben_start_date is
712 select greatest(pps.date_start, l_tax_year_start)
713 from per_periods_of_service pps,
714 per_all_assignments_f paf
715 where paf.period_of_service_id = pps.period_of_service_id
716 and paf.assignment_id = p_assignment_id;
717
718 begin
719
720 hr_utility.set_location('ben_start_date',1);
721
722 l_bus_group_id := get_bus_group_id(p_assignment_id);
723 l_tax_year_start := get_tax_year_start(l_bus_group_id);
724
725 hr_utility.set_location('ben_start_date',2);
726
727 open csr_ben_start_date;
728 fetch csr_ben_start_date into l_ben_start_date;
729 close csr_ben_start_date;
730
731 hr_utility.trace('Benefit start date: '||to_char(l_ben_start_date,'DD-MON-YYYY'));
732 hr_utility.set_location('ben_start_date',99);
733
734 return to_char(l_ben_start_date,'DD-MON-YYYY');
735
736 exception
737
738 when others then
739 hr_utility.set_location('ben_start_date',999);
740 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
741 raise;
742
743 end ben_start_date;
744
745 ---------------------------------------------------------------------------
746 -- Function: BEN_END_DATE
747 -- Description: Function returns benefit end date for Mileage Claim/
748 -- Additional Passenger Claim assignments
749 ---------------------------------------------------------------------------
750 function ben_end_date (p_assignment_id in number) return varchar2 is
751
752 l_bus_group_id number;
753
754 l_ben_end_date date;
755 l_tax_year_end date;
756
757 cursor csr_ben_end_date is
758 select least(nvl(pps.actual_termination_date,hr_general.end_of_time),
759 l_tax_year_end)
760 from per_periods_of_service pps,
761 per_all_assignments_f paf
762 where paf.period_of_service_id = pps.period_of_service_id
763 and paf.assignment_id = p_assignment_id;
764
765 begin
766
767 hr_utility.set_location('ben_end_date',1);
768
769 l_bus_group_id := get_bus_group_id(p_assignment_id);
770 l_tax_year_end := get_tax_year_end(l_bus_group_id);
771
772 hr_utility.set_location('ben_end_date',2);
773
774 open csr_ben_end_date;
775 fetch csr_ben_end_date into l_ben_end_date;
776 close csr_ben_end_date;
777
778 hr_utility.trace('Benefit end date: '||to_char(l_ben_end_date,'DD-MON-YYYY'));
779 hr_utility.set_location('ben_end_date',99);
780
781 return to_char(l_ben_end_date,'DD-MON-YYYY');
782
783 exception
784
785 when others then
786 hr_utility.set_location('ben_end_date',999);
787 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
788 raise;
789
790 end ben_end_date;
791
792 ---------------------------------------------------------------------------
793 -- Function: ASG_START_DATE
794 -- Description: Function returns greater of assignment start date or TYS
795 ---------------------------------------------------------------------------
796 function asg_start_date (p_assignment_id in number) return varchar2 is
797
798 l_bus_group_id number;
799
800 l_asg_start_date date;
801 l_tax_year_start date;
802
803 cursor csr_asg_start_date is
804 select greatest(min(paf.effective_start_date),l_tax_year_start)
805 from per_all_assignments_f paf
806 where paf.assignment_id = p_assignment_id;
807
808 begin
809
810 hr_utility.set_location('asg_start_date',1);
811
812 l_bus_group_id := get_bus_group_id(p_assignment_id);
813 l_tax_year_start := get_tax_year_start(l_bus_group_id);
814
818 fetch csr_asg_start_date into l_asg_start_date;
815 hr_utility.set_location('asg_start_date',2);
816
817 open csr_asg_start_date;
819 close csr_asg_start_date;
820
821 hr_utility.trace('Asg start date: '||to_char(l_asg_start_date,'DD-MON-YYYY'));
822 hr_utility.set_location('asg_start_date',99);
823
824 return to_char(l_asg_start_date,'DD-MON-YYYY');
825
826 exception
827
828 when others then
829 hr_utility.set_location('asg_start_date',999);
830 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
831 raise;
832
833 end asg_start_date;
834
835
836 ---------------------------------------------------------------------------
837 -- Function: ASG_END_DATE
838 -- Description: Function returns lesser of assignment end date or TYE
839 ---------------------------------------------------------------------------
840 function asg_end_date (p_assignment_id in number) return varchar2 is
841
842 l_bus_group_id number;
843
844 l_asg_end_date date;
845 l_tax_year_end date;
846
847 cursor csr_asg_end_date is
848 select least(max(paf.effective_end_date),l_tax_year_end)
849 from per_all_assignments_f paf,
850 per_assignment_status_types past
851 where paf.assignment_id = p_assignment_id
852 and past.per_system_status = 'ACTIVE_ASSIGN'
853 and paf.assignment_status_type_id = past.assignment_status_type_id;
854
855 begin
856
857 hr_utility.set_location('asg_end_date',1);
858
859 l_bus_group_id := get_bus_group_id(p_assignment_id);
860 l_tax_year_end := get_tax_year_end(l_bus_group_id);
861
862 hr_utility.set_location('asg_end_date',2);
863
864 open csr_asg_end_date;
865 fetch csr_asg_end_date into l_asg_end_date;
866 close csr_asg_end_date;
867
868 hr_utility.trace('Asg end date: '||to_char(l_asg_end_date,'DD-MON-YYYY'));
869 hr_utility.set_location('asg_end_date',99);
870
871 return to_char(l_asg_end_date,'DD-MON-YYYY');
872
873 exception
874
875 when others then
876 hr_utility.set_location('asg_end_date',999);
877 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
878 raise;
879
880 end asg_end_date;
881
882
883 ---------------------------------------------------------------------------
884 -- Function: MILEAGE_BALANCE
885 -- Description: Function returns mileage balance for a specific assignment
886 ---------------------------------------------------------------------------
887 function mileage_balance (p_assignment_id in number) return varchar2 is
888
889 l_asg_act_id_tye pay_assignment_actions.assignment_action_id%type;
890 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
891
892 l_bus_group_id number;
893
894 l_ext_end_date date;
895
896 l_ben_start_date date;
897
898 l_def_bal_id pay_defined_balances.defined_balance_id%type;
899
900 cursor csr_get_odd_def_bal_id is
901 select pdb.defined_balance_id
902 from pay_balance_types pbt,
903 pay_defined_balances pdb,
904 pay_balance_dimensions pbd
905 where pbt.balance_type_id = pdb.balance_type_id
906 and pdb.balance_dimension_id = pbd.balance_dimension_id
907 and pbt.balance_name = 'Mileage Odd Taxable Amt'
908 and pbd.dimension_name = '_ASG_YTD';
909
910 cursor csr_get_even_def_bal_id is
911 select pdb.defined_balance_id
912 from pay_balance_types pbt,
913 pay_defined_balances pdb,
914 pay_balance_dimensions pbd
915 where pbt.balance_type_id = pdb.balance_type_id
916 and pdb.balance_dimension_id = pbd.balance_dimension_id
917 and pbt.balance_name = 'Mileage Even Taxable Amt'
918 and pbd.dimension_name = '_ASG_YTD';
919
920 l_odd_even_marker varchar2(5);
921
922 l_tax_year_end date;
923
924 l_tye_balance number;
925 l_ext_balance number;
926
927 l_balance_amt number;
928
929 begin
930
931 hr_utility.set_location('mileage_balance',1);
932
933 -- get necessary data
934
935 l_bus_group_id := get_bus_group_id(p_assignment_id);
936
937 hr_utility.set_location('mileage_balance',2);
938
939 l_tax_year_end := get_tax_year_end(l_bus_group_id);
940
941 hr_utility.set_location('mileage_balance',3);
942
943
944 -- check whether year in which tax year end date falls is odd or even
945
946 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
947
948 -- retrieve appropriate odd/even defined balance id for Mileage Taxable Amt
949 -- based on odd/even marker
950 hr_utility.set_location('mileage_balance',4);
951
952 if l_odd_even_marker = 'ODD'
953 then
954
955 open csr_get_odd_def_bal_id;
956 fetch csr_get_odd_def_bal_id into l_def_bal_id;
957 close csr_get_odd_def_bal_id;
958
959 else
960
961 open csr_get_even_def_bal_id;
962 fetch csr_get_even_def_bal_id into l_def_bal_id;
963 close csr_get_even_def_bal_id;
964
965 end if;
966
967 hr_utility.set_location('mileage_balance',5);
968
969 -- get params to pass to get_latest_asg_act
970
971 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
972
973 hr_utility.set_location('mileage_balance',6);
974
975 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_assignment_id));
976
977 hr_utility.set_location('mileage_balance',7);
978
979 -- fetch latest assignment action ids for that assignment as at TYE and
980 -- extract end date (or closest asg action to either)
984 p_ben_start_date => l_ben_start_date);
981
982 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_assignment_id,
983 p_ext_end_date => l_ext_end_date,
985
986 l_asg_act_id_tye := get_latest_asg_act_tye(p_assignment_id => p_assignment_id,
987 p_tax_year_end_date => l_tax_year_end,
988 p_ben_start_date => l_ben_start_date);
989
990 -- now get ASG_YTD balances for that assignment, using defined balance id
991 -- and latest assignment actions just obtained
992 hr_utility.set_location('mileage_balance',8);
993
994 l_ext_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
995 p_assignment_action_id => l_asg_act_id_ext);
996
997 l_tye_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
998 p_assignment_action_id => l_asg_act_id_tye);
999
1000 hr_utility.trace('l_ext_balance: '||l_ext_balance);
1001 hr_utility.trace('l_tye_balance: '||l_tye_balance);
1002
1003 if l_tye_balance <> l_ext_balance
1004 -- e.g. if user has processed back-dated mileage claims after TYE so balance
1005 -- as of extract end date is greater
1006 then
1007
1008 l_balance_amt := l_tye_balance + l_ext_balance;
1009
1010 else
1011 -- just take the balance as at TYE or closest asg action to that
1012
1013 l_balance_amt := l_tye_balance;
1014
1015 end if;
1016
1017 hr_utility.set_location('mileage_balance',9);
1018 hr_utility.trace('Mileage Balance Amount: '||l_balance_amt);
1019 hr_utility.trace('Assignment id: '||p_assignment_id);
1020
1021 hr_utility.set_location('mileage_balance',99);
1022
1023 return to_char(l_balance_amt);
1024
1025 exception
1026
1027 when others then
1028 hr_utility.set_location('mileage_balance',999);
1029 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1030 raise;
1031
1032 end mileage_balance;
1033
1034 ---------------------------------------------------------------------------
1035 -- Function: ADD_PASS_BALANCE
1036 -- Description: Function returns addl passenger balance for a specific
1037 -- assignment
1038 ---------------------------------------------------------------------------
1039 function add_pass_balance (p_assignment_id in number) return varchar2 is
1040
1041 l_asg_act_id_tye pay_assignment_actions.assignment_action_id%type;
1042 l_asg_act_id_ext pay_assignment_actions.assignment_action_id%type;
1043
1044 l_bus_group_id number;
1045
1046 l_ext_end_date date;
1047
1048 l_ben_start_date date;
1049
1050 l_def_bal_id pay_defined_balances.defined_balance_id%type;
1051
1052 cursor csr_get_odd_def_bal_id is
1053 select pdb.defined_balance_id
1054 from pay_balance_types pbt,
1055 pay_defined_balances pdb,
1056 pay_balance_dimensions pbd
1057 where pbt.balance_type_id = pdb.balance_type_id
1058 and pdb.balance_dimension_id = pbd.balance_dimension_id
1059 and pbt.balance_name = 'Addl Pasg Odd Taxable Amt'
1060 and pbd.dimension_name = '_ASG_YTD';
1061
1062 cursor csr_get_even_def_bal_id is
1063 select pdb.defined_balance_id
1064 from pay_balance_types pbt,
1065 pay_defined_balances pdb,
1066 pay_balance_dimensions pbd
1067 where pbt.balance_type_id = pdb.balance_type_id
1068 and pdb.balance_dimension_id = pbd.balance_dimension_id
1069 and pbt.balance_name = 'Addl Pasg Even Taxable Amt'
1070 and pbd.dimension_name = '_ASG_YTD';
1071
1072 l_odd_even_marker varchar2(5);
1073
1074 l_tax_year_end date;
1075
1076 l_tye_balance number;
1077 l_ext_balance number;
1078
1079 l_balance_amt number;
1080
1081 begin
1082
1083 hr_utility.set_location('add_pass_balance',1);
1084
1085 -- get necessary data
1086
1087 l_bus_group_id := get_bus_group_id(p_assignment_id);
1088
1089 hr_utility.set_location('add_pass_balance',2);
1090
1091 l_tax_year_end := get_tax_year_end(l_bus_group_id);
1092
1093 hr_utility.set_location('add_pass_balance',3);
1094
1095 -- check whether year in which tax year end date falls is odd or even
1096
1097 l_odd_even_marker := check_odd_even_year(l_tax_year_end);
1098
1099 -- retrieve appropriate odd/even defined balance id for Mileage Taxable Amt
1100 -- based on odd/even marker
1101 hr_utility.set_location('add_pass_balance',4);
1102
1103 if l_odd_even_marker = 'ODD'
1104 then
1105
1106 open csr_get_odd_def_bal_id;
1107 fetch csr_get_odd_def_bal_id into l_def_bal_id;
1108 close csr_get_odd_def_bal_id;
1109
1110 else
1111
1112 open csr_get_even_def_bal_id;
1113 fetch csr_get_even_def_bal_id into l_def_bal_id;
1114 close csr_get_even_def_bal_id;
1115
1116 end if;
1117
1118 hr_utility.set_location('add_pass_balance',5);
1119
1120 -- get params to pass to get_latest_asg_act
1121
1122 l_ext_end_date := get_param_ext_end_date(l_bus_group_id);
1123
1124 hr_utility.set_location('add_pass_balance',6);
1125
1126 l_ben_start_date := fnd_date.displaydate_to_date(ben_start_date(p_assignment_id));
1127
1128 hr_utility.set_location('add_pass_balance',7);
1129
1130 -- fetch latest assignment action ids for that assignment as at TYE and
1131 -- extract end date (or closest asg act to either)
1132
1133 l_asg_act_id_ext := get_latest_asg_act_ext(p_assignment_id => p_assignment_id,
1134 p_ext_end_date => l_ext_end_date,
1138 p_tax_year_end_date => l_tax_year_end,
1135 p_ben_start_date => l_ben_start_date);
1136
1137 l_asg_act_id_tye := get_latest_asg_act_tye(p_assignment_id => p_assignment_id,
1139 p_ben_start_date => l_ben_start_date);
1140
1141 -- now get ASG_YTD balances for that assignment, using defined balance id
1142 -- and latest assignment actions just obtained
1143 hr_utility.set_location('add_pass_balance',8);
1144
1145 l_ext_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1146 p_assignment_action_id => l_asg_act_id_ext);
1147
1148 l_tye_balance := pay_balance_pkg.get_value (p_defined_balance_id => l_def_bal_id,
1149 p_assignment_action_id => l_asg_act_id_tye);
1150
1151 hr_utility.trace('l_ext_balance: '||l_ext_balance);
1152 hr_utility.trace('l_tye_balance: '||l_tye_balance);
1153
1154 if l_tye_balance <> l_ext_balance
1155 -- e.g. if user has processed back-dated mileage claims after TYE so balance
1156 -- as of extract end date is greater
1157 then
1158
1159 l_balance_amt := l_tye_balance + l_ext_balance;
1160
1161 else
1162 -- just take the balance as at TYE or closest asg action to that
1163
1164 l_balance_amt := l_tye_balance;
1165
1166 end if;
1167
1168 hr_utility.set_location('add_pass_balance',9);
1169 hr_utility.trace('Addl Passenger Balance Amount: '||l_balance_amt);
1170 hr_utility.trace('Assignment id: '||p_assignment_id);
1171
1172 hr_utility.set_location('add_pass_balance',99);
1173
1174 return to_char(l_balance_amt);
1175
1176 exception
1177
1178 when others then
1179 hr_utility.set_location('mileage_balance',999);
1180 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1181 raise;
1182
1183 end add_pass_balance;
1184
1185 ---------------------------------------------------------------------------
1186 -- Function: CREATE_EXT_RSLT_DTL
1187 -- Description: Write new details records to extract table
1188 ---------------------------------------------------------------------------
1189 PROCEDURE create_ext_rslt_dtl(p_asg_id in number,
1190 p_benefit_start_date in varchar2,
1191 p_benefit_end_date in varchar2,
1192 p_mileage_balance in varchar2,
1193 p_add_pass_balance in varchar2,
1194 p_asg_start_date in varchar2,
1195 p_asg_end_date in varchar2) IS
1196 --
1197 l_ext_rslt_dtl_id NUMBER;
1198 l_object_version_no NUMBER;
1199 --
1200 CURSOR chk_exists IS
1201 SELECT ext_rslt_dtl_id
1202 FROM ben_ext_rslt_dtl
1203 WHERE ext_rslt_id = g_ext_rslt_id
1204 AND person_id = g_person_id
1205 AND ext_rcd_id = g_veh_rcd_id
1206 AND val_02 = to_char(p_asg_id)
1207 AND val_04 = p_benefit_end_date
1208 AND val_09 = p_benefit_start_date
1209 AND val_10 = p_benefit_end_date
1210 AND val_12 = p_mileage_balance;
1211 --
1212 l_chk_exists chk_exists%ROWTYPE;
1213
1214 BEGIN
1215
1216 hr_utility.trace('Entering CREATE_EXT_RSLT_DTL: p_asg_id='|| p_asg_id);
1217 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_benefit_start_date='||p_benefit_start_date);
1218 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_benefit_end_date='||p_benefit_end_date);
1219 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_mileage_balance='||p_mileage_balance);
1220 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_add_pass_balance='||p_add_pass_balance);
1221 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_asg_start_date='||p_asg_start_date);
1222 hr_utility.trace('CREATE_EXT_RSLT_DTL: p_asg_end_date='||p_asg_end_date);
1223 --
1224 -- CHeck if record already exists
1225 OPEN chk_exists;
1226 FETCH chk_exists INTO l_chk_exists;
1227 --
1228 IF chk_exists%NOTFOUND THEN
1229 -- Record does not exist
1230 -- Call API to create extract details record
1231 BEGIN
1232 ben_ext_rslt_dtl_api.create_ext_rslt_dtl( p_ext_rslt_dtl_id => l_ext_rslt_dtl_id
1233 ,p_ext_rslt_id => g_ext_rslt_id
1234 ,p_ext_rcd_id => g_veh_rcd_id
1235 ,p_person_id => g_person_id
1236 ,p_business_group_id => g_bg_id
1237 ,p_val_01 => 'A'
1238 ,p_val_02 => to_char(p_asg_id)
1239 ,p_val_03 => '~~~~~~~~~~~~~~~~~~~~~~~~~'
1240 ,p_val_04 => p_benefit_end_date
1241 ,p_val_05 => p_asg_start_date
1242 ,p_val_06 => p_asg_end_date
1243 ,p_val_07 => 'Mileage Allowance and PPayment'
1244 ,p_val_08 => '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
1245 ,p_val_09 => p_benefit_start_date
1246 ,p_val_10 => p_benefit_end_date
1247 ,p_val_11 => '~~~'
1248 ,p_val_12 => p_mileage_balance
1249 ,p_val_13 => '~'
1250 ,p_val_14 => p_add_pass_balance
1251 ,p_val_15 => '~~'
1255 hr_utility.trace('Wrote detail record: '||l_ext_rslt_dtl_id);
1252 ,p_val_16 => '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
1253 ,p_object_version_number => l_object_version_no);
1254
1256
1257
1258 EXCEPTION WHEN others THEN
1259 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 1, 100));
1260 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 101, 100));
1261 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 201, 100));
1262 hr_utility.trace('CREATE_EXT_RSLT_DTL: '||substr(sqlerrm, 301, 100));
1263 RAISE;
1264 END;
1265 END IF;
1266 --
1267 CLOSE chk_exists;
1268
1269 end create_ext_rslt_dtl;
1270
1271 ---------------------------------------------------------------------------
1272 -- Function: PROCESS_SEC_ASG
1273 -- Description: Process secondary assignments. Check that assignment meets
1274 -- criteria, retrieve any balances and write new detail
1275 -- records
1276 ---------------------------------------------------------------------------
1277 PROCEDURE process_sec_asg(p_asg_id IN NUMBER) IS
1278 --
1279 CURSOR csr_sec_asg IS
1280 SELECT distinct paa1.assignment_id
1281 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1282 WHERE paa2.assignment_id = p_asg_id
1283 AND paa2.person_id = paa1.person_id
1284 AND nvl(paa1.primary_flag, 'N') = 'N';
1285 --
1286 l_asg_include VARCHAR2(1) := 'N';
1287
1288 l_ben_start_date varchar2(30);
1289 l_ben_end_date varchar2(30);
1290
1291 l_mileage_balance varchar2(30);
1292 l_add_pass_balance varchar2(30);
1293
1294 l_asg_start_date varchar2(30);
1295 l_asg_end_date varchar2(30);
1296 --
1297 BEGIN
1298 hr_utility.set_location('process_sec_asg',1);
1299 hr_utility.trace('p_asg_id= '||p_asg_id);
1300 --
1301 -- Loop through all secondary assignments
1302 FOR sec_asg_rec IN csr_sec_asg LOOP
1303 --
1304 hr_utility.set_location('process_sec_asg',2);
1305 l_asg_include := 'N';
1306 l_asg_include := check_asg_inclusion(sec_asg_rec.assignment_id);
1307 --
1308 -- if assignment meets criteria and mileage balance for that assignment
1309 -- is greater than zero, then retrieve all details and write new
1310 -- detail record
1311 IF l_asg_include = 'Y' and to_number(mileage_balance(sec_asg_rec.assignment_id)) > 0
1312 THEN
1313
1314 hr_utility.set_location('process_sec_asg',3);
1315
1316 l_ben_start_date := ben_start_date(sec_asg_rec.assignment_id);
1317 l_ben_end_date := ben_end_date(sec_asg_rec.assignment_id);
1318 l_mileage_balance := mileage_balance(sec_asg_rec.assignment_id);
1319 l_add_pass_balance := add_pass_balance(sec_asg_rec.assignment_id);
1320 l_asg_start_date := asg_start_date(sec_asg_rec.assignment_id);
1321 l_asg_end_date := asg_end_date(sec_asg_rec.assignment_id);
1322
1323 create_ext_rslt_dtl( p_asg_id => sec_asg_rec.assignment_id,
1324 p_benefit_start_date => l_ben_start_date,
1325 p_benefit_end_date => l_ben_end_date,
1326 p_mileage_balance => l_mileage_balance,
1327 p_add_pass_balance => l_add_pass_balance,
1328 p_asg_start_date => l_asg_start_date,
1329 p_asg_end_date => l_asg_end_date);
1330
1331 END IF;
1332
1333 END LOOP;
1334 --
1335 hr_utility.set_location('process_sec_asg',99);
1336
1337 exception
1338
1339 when others then
1340
1341 hr_utility.set_location('process_sec_asg',999);
1342 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1343 raise;
1344
1345 END process_sec_asg;
1346
1347 ---------------------------------------------------------------------------
1348 -- Function: PROCESS_TERM_PRIMARY_ASG
1349 -- Description: Process primary assignments that were terminated prior to
1350 -- the extract being run e.g. for rehires where primary assignment
1351 -- being passed in is the current primary assignment.
1352 -- Check that assignment meets criteria, retrieve any balances
1353 -- and write new detail records
1354 ---------------------------------------------------------------------------
1355 PROCEDURE process_term_primary_asg(p_asg_id IN NUMBER) IS
1356 --
1357 CURSOR csr_term_prim_asg IS
1358 SELECT distinct paa1.assignment_id
1359 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1360 WHERE paa2.assignment_id = p_asg_id
1361 AND paa2.person_id = paa1.person_id
1362 AND paa1.effective_end_date < paa2.effective_start_date
1363 -- AND paa1.period_of_service_id <> paa2.period_of_service_id
1364 AND nvl(paa1.primary_flag, 'Y') = 'Y';
1365 --
1366 l_asg_include VARCHAR2(1) := 'N';
1367
1368 l_ben_start_date varchar2(30);
1369 l_ben_end_date varchar2(30);
1370
1371 l_mileage_balance varchar2(30);
1372 l_add_pass_balance varchar2(30);
1373
1374 l_asg_start_date varchar2(30);
1375 l_asg_end_date varchar2(30);
1376 --
1377 BEGIN
1378 hr_utility.set_location('process_term_primary_asg',1);
1379 hr_utility.trace('p_asg_id= '||p_asg_id);
1380 --
1381 -- Loop through all terminated primary assignments
1382 FOR term_prim_asg_rec IN csr_term_prim_asg LOOP
1383 --
1384 hr_utility.set_location('process_term_primary_asg',2);
1385 l_asg_include := 'N';
1386 l_asg_include := check_asg_inclusion(term_prim_asg_rec.assignment_id);
1387 --
1391 IF l_asg_include = 'Y' and to_number(mileage_balance(term_prim_asg_rec.assignment_id)) > 0
1388 -- if assignment meets criteria and mileage balance for that assignment
1389 -- is greater than zero, then retrieve all details and write new
1390 -- detail record
1392 THEN
1393
1394 hr_utility.set_location('process_term_primary_asg',3);
1395
1396 l_ben_start_date := ben_start_date(term_prim_asg_rec.assignment_id);
1397 l_ben_end_date := ben_end_date(term_prim_asg_rec.assignment_id);
1398 l_mileage_balance := mileage_balance(term_prim_asg_rec.assignment_id);
1399 l_add_pass_balance := add_pass_balance(term_prim_asg_rec.assignment_id);
1400 l_asg_start_date := asg_start_date(term_prim_asg_rec.assignment_id);
1401 l_asg_end_date := asg_end_date(term_prim_asg_rec.assignment_id);
1402
1403 create_ext_rslt_dtl( p_asg_id => term_prim_asg_rec.assignment_id,
1404 p_benefit_start_date => l_ben_start_date,
1405 p_benefit_end_date => l_ben_end_date,
1406 p_mileage_balance => l_mileage_balance,
1407 p_add_pass_balance => l_add_pass_balance,
1408 p_asg_start_date => l_asg_start_date,
1409 p_asg_end_date => l_asg_end_date);
1410
1411 END IF;
1412
1413 END LOOP;
1414 --
1415 hr_utility.set_location('process_term_primary_asg',99);
1416
1417 exception
1418
1419 when others then
1420
1421 hr_utility.set_location('process_term_primary_asg',999);
1422 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1423 raise;
1424
1425 END process_term_primary_asg;
1426
1427 ---------------------------------------------------------------------------
1428 -- Function: POST_PROCESS_RULE
1429 -- Description: Extract post-processing functionality. Processes any
1430 -- secondary assignments that meet the criteria, as well as
1431 -- cleaning up any unnecessary assignment and details records
1432 ---------------------------------------------------------------------------
1433 FUNCTION post_process_rule(p_ext_rslt_id IN NUMBER) RETURN VARCHAR2 IS
1434
1435 CURSOR csr_asg_record_id IS
1436 SELECT rcd.ext_rcd_id
1437 FROM ben_ext_rcd rcd,
1438 ben_ext_rcd_in_file rif,
1439 ben_ext_dfn dfn,
1440 ben_ext_rslt rslt
1441 WHERE rslt.ext_rslt_id = p_ext_rslt_id
1442 AND rslt.ext_dfn_id = dfn.ext_dfn_id
1443 AND dfn.ext_file_id = rif.ext_file_id
1444 AND rif.ext_rcd_id = rcd.ext_rcd_id
1445 AND rcd.name like '%PAY GB P11D Mileage Extract 2003 - Assignment Details Record';
1446 --
1447 l_asg_rcd_id NUMBER;
1448 --
1449 CURSOR csr_detail_record_id IS
1450 SELECT rcd.ext_rcd_id
1451 FROM ben_ext_rcd rcd,
1452 ben_ext_rcd_in_file rif,
1453 ben_ext_dfn dfn,
1454 ben_ext_rslt rslt
1455 WHERE rslt.ext_rslt_id = p_ext_rslt_id
1456 AND rslt.ext_dfn_id = dfn.ext_dfn_id
1457 AND dfn.ext_file_id = rif.ext_file_id
1458 AND rif.ext_rcd_id = rcd.ext_rcd_id
1459 AND rcd.name like '%PAY GB P11D Mileage Extract 2003 - Mileage and Passenger Record';
1460 --
1461 --
1462 CURSOR csr_ext_asg IS
1463 SELECT person_id, val_01 asg_id, ext_rslt_dtl_id, object_version_number
1464 FROM ben_ext_rslt_dtl
1465 WHERE ext_rslt_id = p_ext_rslt_id
1466 AND ext_rcd_id = l_asg_rcd_id;
1467 --
1468 l_obj_no NUMBER := NULL;
1469 --
1470 CURSOR csr_balance_detail(p_person_id IN NUMBER) IS
1471 SELECT *
1472 FROM ben_ext_rslt_dtl
1473 WHERE ext_rslt_id = p_ext_rslt_id
1474 AND ext_rcd_id = g_veh_rcd_id
1475 AND person_id = p_person_id;
1476 --
1477 l_balance_detail csr_balance_detail%ROWTYPE;
1478 --
1479 l_asg_include VARCHAR2(1);
1480 --
1481 BEGIN
1482
1483 hr_utility.trace('Entering POST_PROCESS_RULE, p_ext_rslt_id='||p_ext_rslt_id);
1484
1485 g_ext_rslt_id := p_ext_rslt_id;
1486
1487 -- Get assignment details record id
1488 OPEN csr_asg_record_id;
1489 FETCH csr_asg_record_id INTO l_asg_rcd_id;
1490 CLOSE csr_asg_record_id;
1491 --
1492 hr_utility.trace('POST_PROCESS_RULE: l_asg_rcd_id='||l_asg_rcd_id);
1493
1494 -- Get Balance Detail Record Id
1495 OPEN csr_detail_record_id;
1496 FETCH csr_detail_record_id INTO g_veh_rcd_id;
1497 CLOSE csr_detail_record_id;
1498 --
1499 hr_utility.trace('POST_PROCESS_RULE: g_veh_rcd_id='||g_veh_rcd_id);
1500
1501 -- Loop through all people extracted
1502 FOR ext_asg_rec IN csr_ext_asg LOOP
1503
1504 -- reset balance detail record at start of each loop
1505 l_balance_detail := null;
1506
1507 g_person_id := ext_asg_rec.person_id;
1508 g_bg_id := get_bus_group_id(ext_asg_rec.asg_id);
1509 --
1510 hr_utility.trace('POST_PROCESS_RULE: ext_asg_rec.asg_id='||ext_asg_rec.asg_id);
1511 hr_utility.trace('POST_PROCESS_RULE: g_person_id='||g_person_id);
1512
1513 -- Fetch in full detail record for primary assignment
1514 -- Delete balance detail record if mileage ASG_YTD balance is zero
1515 OPEN csr_balance_detail(ext_asg_rec.person_id);
1516 FETCH csr_balance_detail INTO l_balance_detail;
1517 CLOSE csr_balance_detail;
1518 --
1519 hr_utility.trace('POST_PROCESS_RULE: l_balance_detail.ext_rslt_dtl_id= '||l_balance_detail.ext_rslt_dtl_id);
1520 hr_utility.trace('POST_PROCESS_RULE: l_balance_detail.val_12(mileage balance)= '||l_balance_detail.val_12);
1521
1522 --
1523 l_asg_include := check_asg_inclusion(ext_asg_rec.asg_id);
1524
1528 -- Delete this detail record
1525 IF l_balance_detail.ext_rslt_dtl_id IS NOT NULL AND l_asg_include = 'N'
1526 THEN
1527 -- Primary assignment does not qualify for extract
1529 hr_utility.trace('Primary asg does not qualify for extract - deleting detail record');
1530 ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => l_balance_detail.ext_rslt_dtl_id,
1531 p_object_version_number => l_balance_detail.object_version_number);
1532
1533 END IF;
1534
1535 --
1536 -- Process any terminated primary assignments which may qualify
1537 process_term_primary_asg(ext_asg_rec.asg_id);
1538 --
1539 -- Process any secondary assignments which may qualify
1540 process_sec_asg(ext_asg_rec.asg_id);
1541 --
1542 hr_utility.trace('POST_PROCESS_RULE: Assignment processed, remove it from the extract details table.');
1543 l_obj_no := ext_asg_rec.object_version_number;
1544 -- Delete this assignment details record
1545 ben_ext_rslt_dtl_api.delete_ext_rslt_dtl(p_ext_rslt_dtl_id => ext_asg_rec.ext_rslt_dtl_id,
1546 p_object_version_number => l_obj_no);
1547 --
1548 END LOOP;
1549 --
1550 hr_utility.trace('Leaving Post_process_rule.');
1551 RETURN 'Y';
1552 --
1553 exception
1554
1555 when others then
1556
1557 hr_utility.set_location('post_process_rule',999);
1558 hr_utility.trace('SQLERRM: '||substr(sqlerrm,1,200));
1559 raise;
1560
1561
1562
1563 end post_process_rule;
1564
1565
1566 ---------------------------------------------------------------------------
1567 -- Function: CHECK_PERSON_INCLUSION
1568 -- Description: This function checks all primary and secondary assignments
1569 -- for inclusion and returns Y if any of them should be
1570 -- included.
1571 ---------------------------------------------------------------------------
1572 FUNCTION check_person_inclusion(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
1573 --
1574 l_person_include VARCHAR2(1);
1575 --
1576 CURSOR get_all_asg IS
1577 SELECT distinct paa1.assignment_id
1578 FROM per_all_assignments_f paa1, per_all_assignments_f paa2
1579 WHERE paa2.assignment_id = p_assignment_id
1580 AND paa2.person_id = paa1.person_id
1581 ORDER BY paa1.assignment_id;
1582 --
1583 l_asg_id NUMBER;
1584
1585 BEGIN
1586
1587 hr_utility.trace('Entering CHECK_PERSON_INCLUSION, p_assignment_id='||p_assignment_id);
1588
1589 -- check whether any assignment qualifies
1590 OPEN get_all_asg;
1591 LOOP
1592
1593 FETCH get_all_asg INTO l_asg_id;
1594 IF get_all_asg%FOUND THEN
1595 l_person_include := check_asg_inclusion(l_asg_id);
1596 END IF;
1597 EXIT WHEN (get_all_asg%NOTFOUND OR l_person_include = 'Y');
1598
1599 END LOOP;
1600 --
1601 hr_utility.trace('Leaving CHECK_PERSON_INCLUSION, l_person_include='||l_person_include);
1602
1603 RETURN l_person_include;
1604 --
1605 END check_person_inclusion;
1606
1607 /* end of package body */
1608 end pay_gb_p11d_mileage_extract;