1 PACKAGE BODY pay_au_foreign_workers AS
2 /* $Header: pyaufwrp.pkb 120.5 2010/12/06 14:08:01 avenkatk noship $*/
3
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
8 * Chertsey, England. *
9 * *
10 * All rights reserved. *
11 * *
12 * This material has been provided pursuant to an agreement *
13 * containing restrictions on its use. The material is also *
14 * protected by copyright law. No part of this material may *
15 * be copied or distributed, transmitted or transcribed, in *
16 * any form or by any means, electronic, mechanical, magnetic, *
17 * manual, or otherwise, or disclosed to third parties without *
18 * the express written permission of Oracle Corporation UK Ltd, *
19 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
20 * England. *
21 * *
22 ******************************************************************
23
24 ------------------------------------------------------------------------+
25 *** Program: pay_au_foreign_workers (Package Body)
26 ***
27 ***
28 *** Change History
29 ***
30 *** Date Changed By Version Bug No Description of Change
31 *** --------- ---------- ------- ------ --------------------------------+
32 *** 05-May-10 avenkatk 115.0 9147421 Initial Version
33 *** 23-Jun-10 avenkatk 115.1 9147430 Added Foreign Worker Phase II changes
34 *** 22-Jul-10 avenkatk 115.2 9147430 Resolved gscc errors
35 *** 12-Aug-10 avenkatk 115.3 10019629 Modified populate_asg_status_id - corrected status names
36 *** 18-Nov-10 avenkatk 115.4 9950136 Made changes for Accruals, Leave. Added function get_fw_between_dates
37 *** 02-Dec-10 avenkatk 115.5 10331262 Modified get_foreign_payment_amount to return gracefully
38 *** when balance table count is 0
39 */
40
41
42 /*
43 Name : populate_asg_status_id
44 Type : Procedure
45 Access : Public
46 Description : This private procedure is called in all functions/procedures
47 to populate the global values
48 Inputs : None
49 Outputs : None
50 */
51
52 PROCEDURE populate_asg_status_id
53 AS
54 CURSOR get_assignment_status_id
55 (c_status_name per_assignment_status_types.user_status%TYPE)
56 IS
57 SELECT past.assignment_status_type_id
58 FROM per_assignment_status_types past
59 WHERE past.user_status = c_status_name
60 AND past.legislation_code = 'AU';
61
62
63 CURSOR get_defined_balance_id
64 IS
65 SELECT pdb.defined_balance_id
66 FROM pay_defined_balances pdb
67 ,pay_balance_types pbt
68 ,pay_balance_dimensions pbd
69 WHERE pdb.balance_type_id = pbt.balance_type_id
70 AND pdb.balance_dimension_id = pbd.balance_dimension_id
71 AND pbt.legislation_code = 'AU'
72 AND pbd.legislation_code = 'AU'
73 AND pbt.balance_name = 'Foreign Worker Days'
74 AND pbd.dimension_name = '_ASG_LE_PTD';
75
76
77 raise_no_status_type EXCEPTION;
78 raise_no_def_bal EXCEPTION;
79
80 l_proc_name VARCHAR2(100);
81
82 BEGIN
83
84 g_debug := hr_utility.debug_enabled;
85
86 IF g_debug
87 THEN
88 l_proc_name := g_package||'.populate_asg_status_id';
89 hr_utility.set_location('Entering procedure '||l_proc_name,100);
90 END IF;
91
92 IF (NOT cached)
93 THEN
94 OPEN get_assignment_status_id('Foreign Worker F');
95 FETCH get_assignment_status_id INTO g_fw_asg_f_status_id;
96 IF get_assignment_status_id%NOTFOUND
97 THEN
98 RAISE raise_no_status_type;
99 END IF;
100 CLOSE get_assignment_status_id;
101
102 OPEN get_assignment_status_id('Foreign Worker J');
103 FETCH get_assignment_status_id INTO g_fw_asg_j_status_id;
104 IF get_assignment_status_id%NOTFOUND
105 THEN
106 RAISE raise_no_status_type;
107 END IF;
108 CLOSE get_assignment_status_id;
109
110 OPEN get_defined_balance_id;
111 FETCH get_defined_balance_id INTO g_fw_def_bal_id;
112 IF get_defined_balance_id%NOTFOUND
113 THEN
114 RAISE raise_no_def_bal;
115 END IF;
116 CLOSE get_defined_balance_id;
117
118 cached := TRUE;
119 END IF;
120
121 IF g_debug
122 THEN
123 hr_utility.set_location('Leaving procedure '||l_proc_name,100);
124 END IF;
125
126 EXCEPTION
127 WHEN raise_no_status_type
128 THEN
129 hr_utility.set_location('Exception No Assignment Status Type Foreign Worker Found '||l_proc_name,100);
130 CLOSE get_assignment_status_id;
131 raise;
132 END populate_asg_status_id;
133
134
135
136 /*
137 Name : get_asg_status_id
138 Type : Function
139 Access : Private
140 Description : This private procedure is called to get the Assignment Status ID
141 for the given Foreign Worker Type
142 Inputs : p_fw_type - Foreign Worker Type, Valid Values are,
143 pay_au_foreign_workers.g_fw_f_type - Foreign Worker F
144 pay_au_foreign_workers.g_fw_j_type - Foreign Worker J
145
146 Outputs : None
147 */
148
149
150 FUNCTION get_asg_status_id(p_fw_type IN VARCHAR2)
151 RETURN NUMBER
152 IS
153 l_proc_name VARCHAR2(100);
154 l_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
155 BEGIN
156
157 g_debug := hr_utility.debug_enabled;
158
159 IF g_debug
160 THEN
161 l_proc_name := g_package||'.get_asg_status_id';
162 hr_utility.set_location('Entering procedure '||l_proc_name,200);
163 hr_utility.set_location('IN p_fw_type '||p_fw_type,200);
164 END IF;
165
166 IF (NOT cached)
167 THEN
168 populate_asg_status_id;
169 END IF;
170
171 l_status_id := -1;
172
173 IF (p_fw_type = pay_au_foreign_workers.g_fw_f_type)
174 THEN
175 l_status_id := g_fw_asg_f_status_id;
176 ELSIF (p_fw_type = pay_au_foreign_workers.g_fw_j_type)
177 THEN
178 l_status_id := g_fw_asg_j_status_id;
179 END IF;
180
181 IF g_debug
182 THEN
183 hr_utility.set_location('OUT l_status_id '||l_status_id,200);
184 hr_utility.set_location('Leaving procedure '||l_proc_name,200);
185 END IF;
186
187 RETURN l_status_id;
188
189 END get_asg_status_id;
190
191
192 /*
193 Name : check_foreign_worker
194 Type : Function
195 Access : Public
196 Description : This function checks if assignment is Foreign Worker of given type
197 between the given dates
198 Inputs : p_assignment_id - Assignment ID
199 p_tax_unit_id - Legal Employer
200 p_start_date - Start Date
201 p_end_date - End Date
202 p_fw_type - Foreign Worker Type, Valid Values are,
203 pay_au_foreign_workers.g_fw_f_type - Foreign Worker F
204 pay_au_foreign_workers.g_fw_j_type - Foreign Worker J
205 */
206
207
208
209 FUNCTION check_foreign_worker(p_assignment_id IN NUMBER
210 ,p_tax_unit_id IN NUMBER
211 ,p_start_date IN DATE
212 ,p_end_date IN DATE
213 ,p_fw_type IN VARCHAR2)
214 RETURN VARCHAR2
215 IS
216 CURSOR is_foreign_worker
217 (c_assignment_id per_assignments_f.assignment_id%TYPE
218 ,c_start_date DATE
219 ,c_end_date DATE
220 ,c_tax_unit_id NUMBER
221 ,c_status_id NUMBER)
222 IS
223 SELECT 'Y'
224 FROM DUAL
225 WHERE EXISTS
226 ( SELECT 1
227 FROM per_assignments_f paf
228 ,hr_soft_coding_keyflex hsc
229 WHERE paf.assignment_id = c_assignment_id
230 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
231 AND hsc.segment1 = c_tax_unit_id
232 AND paf.assignment_status_type_id = c_status_id
233 AND paf.effective_start_date <= c_end_date
234 AND paf.effective_end_date >= c_start_date
235 );
236
237 l_return_value VARCHAR2(10);
238 l_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
239
240 l_proc_name VARCHAR2(100);
241
242 BEGIN
243
244 g_debug := hr_utility.debug_enabled;
245
246 IF g_debug
247 THEN
248 l_proc_name := g_package||'.check_foreign_worker';
249 hr_utility.set_location('Entering procedure '||l_proc_name,300);
250 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,300);
251 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,300);
252 hr_utility.set_location('IN p_start_date '||p_start_date,300);
253 hr_utility.set_location('IN p_end_date '||p_end_date,300);
254 hr_utility.set_location('IN p_fw_type '||p_fw_type,300);
255 END IF;
256
257 IF (NOT cached)
258 THEN
259 populate_asg_status_id;
260 END IF;
261
262
263 l_status_id := get_asg_status_id(p_fw_type);
264
265 OPEN is_foreign_worker(p_assignment_id
266 ,p_start_date
267 ,p_end_date
268 ,p_tax_unit_id
269 ,l_status_id);
270 FETCH is_foreign_worker INTO l_return_value;
271 IF is_foreign_worker%NOTFOUND
272 THEN
273 l_return_value := 'N';
274 END IF;
275 CLOSE is_foreign_worker;
276
277 IF g_debug
278 THEN
279 hr_utility.set_location('OUT l_return_value '||l_return_value,300);
280 hr_utility.set_location('Leaving procedure '||l_proc_name,300);
281 END IF;
282
283 RETURN NVL(l_return_value,'N');
284
285 END check_foreign_worker;
286
287
288
289 /*
290 Name : check_foreign_worker
291 Type : Function
292 Access : Public
293 Description : This function checks if assignment is Foreign Worker between the given dates.
294 Internally calls overloaded function with the FW Type parameter.
295 Inputs : p_assignment_id - Assignment ID
296 p_tax_unit_id - Legal Employer
297 p_start_date - Start Date
298 p_end_date - End Date
299 */
300
301
302 FUNCTION check_foreign_worker(p_assignment_id IN NUMBER
303 ,p_tax_unit_id IN NUMBER
304 ,p_start_date IN DATE
305 ,p_end_date IN DATE)
306 RETURN VARCHAR2
307 IS
308 l_return_value VARCHAR2(10);
309
310 l_proc_name VARCHAR2(100);
311
312 BEGIN
313
314 g_debug := hr_utility.debug_enabled;
315
316 IF g_debug
317 THEN
318 l_proc_name := g_package||'.check_foreign_worker';
319 hr_utility.set_location('Entering procedure '||l_proc_name,400);
320 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,400);
321 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,400);
322 hr_utility.set_location('IN p_start_date '||p_start_date,400);
323 hr_utility.set_location('IN p_end_date '||p_end_date,400);
324 END IF;
325
326 IF (NOT cached)
327 THEN
328 populate_asg_status_id;
329 END IF;
330
331 /* Call the overloaded functions for each type. */
332
333 l_return_value := check_foreign_worker
334 (p_assignment_id
335 ,p_tax_unit_id
336 ,p_start_date
337 ,p_end_date
338 ,pay_au_foreign_workers.g_fw_f_type);
339
340 IF NVL(l_return_value,'N') = 'N'
341 THEN
342 l_return_value := check_foreign_worker
343 (p_assignment_id
344 ,p_tax_unit_id
345 ,p_start_date
346 ,p_end_date
347 ,pay_au_foreign_workers.g_fw_j_type);
348 END IF;
349
350 IF g_debug
351 THEN
352 hr_utility.set_location('OUT l_return_value '||l_return_value,400);
353 hr_utility.set_location('Leaving procedure '||l_proc_name,400);
354 END IF;
355
356 RETURN NVL(l_return_value,'N');
357
358 END check_foreign_worker;
359
360
361
362
363 /*
364 Name : check_fw_terminated
365 Type : Function
366 Access : Public
367 Description : This function checks if assignment is terminated as a Foreign Worker
368 of given type.
369 Inputs : p_assignment_id - Assignment ID
370 p_tax_unit_id - Legal Employer
371 p_start_date - Start Date
372 p_end_date - End Date
373 p_fw_type - Foreign Worker Type, Valid Values are,
374 pay_au_foreign_workers.g_fw_f_type - Foreign Worker F
375 pay_au_foreign_workers.g_fw_j_type - Foreign Worker J
376 Outputs : p_act_term_date - Actual Termination Date
377
378 */
379
380 FUNCTION check_fw_terminated(p_assignment_id IN per_assignments_f.assignment_id%TYPE
381 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
382 ,p_start_date IN DATE
383 ,p_end_date IN DATE
384 ,p_fw_type IN VARCHAR2
385 ,p_act_term_date OUT NOCOPY per_periods_of_service.actual_termination_date%TYPE)
386 RETURN VARCHAR2
387 AS
388
389 CURSOR is_fw_terminated
390 (c_assignment_id per_assignments_f.assignment_id%TYPE
391 ,c_tax_unit_id VARCHAR2
392 ,c_start_date DATE
393 ,c_end_date DATE
394 ,c_status_id per_assignments_f.assignment_status_type_id%TYPE)
395 IS
396 SELECT pps.actual_termination_date
397 FROM per_assignments_f paf
398 ,hr_soft_coding_keyflex hsc
399 ,per_periods_of_service pps
400 WHERE paf.assignment_id = c_assignment_id
401 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
402 AND hsc.segment1 LIKE c_tax_unit_id
403 AND paf.period_of_service_id = pps.period_of_service_id
404 AND pps.actual_termination_date IS NOT NULL
405 AND pps.actual_termination_date BETWEEN c_start_date AND c_end_date
406 AND pps.actual_termination_date BETWEEN paf.effective_start_date and paf.effective_end_date
407 AND paf.assignment_status_type_id = c_status_id;
408
409 l_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
410 l_return_value VARCHAR2(10);
411 l_actual_term_date DATE;
412 l_proc_name VARCHAR2(100);
413
414
415 BEGIN
416
417 g_debug := hr_utility.debug_enabled;
418
419 IF g_debug
420 THEN
421 l_proc_name := g_package||'.check_fw_terminated';
422 hr_utility.set_location('Entering procedure '||l_proc_name,500);
423 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,500);
424 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,500);
425 hr_utility.set_location('IN p_start_date '||p_start_date,500);
426 hr_utility.set_location('IN p_end_date '||p_end_date,500);
427 hr_utility.set_location('IN p_fw_type '||p_fw_type,500);
428 END IF;
429
430 IF (NOT cached)
431 THEN
432 populate_asg_status_id;
433 END IF;
434
435 l_status_id := get_asg_status_id(p_fw_type);
436
437 l_return_value := 'N'; /* Default value */
438 OPEN is_fw_terminated(p_assignment_id
439 ,NVL(to_char(p_tax_unit_id),'%')
440 ,p_start_date
441 ,p_end_date
442 ,l_status_id);
443 FETCH is_fw_terminated INTO l_actual_term_date;
444 IF is_fw_terminated%FOUND
445 THEN
446 l_return_value := 'Y';
447 p_act_term_date := l_actual_term_date;
448 END IF;
449 CLOSE is_fw_terminated;
450
451 IF g_debug
452 THEN
453 hr_utility.set_location('OUT p_act_term_date '||p_act_term_date,500);
454 hr_utility.set_location('OUT l_return_value '||l_return_value,500);
455 hr_utility.set_location('Leaving procedure '||l_proc_name,500);
456 END IF;
457
458 RETURN l_return_value;
459
460 END check_fw_terminated;
461
462
463 /*
464 Name : get_foreign_worker_dates
465 Type : Procedure
466 Access : Public
467 Description : This procedure returns a complex Data type with the Assignment Details
468 of given Foreign Worker Type between given dates.
469 Inputs : p_assignment_id - Assignment ID
470 p_tax_unit_id - Legal Employer
471 p_start_date - Start Date
472 p_end_date - End Date
473 p_fw_type - Foreign Worker Type, Valid Values are,
474 pay_au_foreign_workers.g_fw_f_type - Foreign Worker F
475 pay_au_foreign_workers.g_fw_j_type - Foreign Worker J
476 Outputs : Complex Data Type - pay_au_foreign_workers.tab_fw_dates
477 contains Assignment Details
478
479 */
480
481 PROCEDURE get_foreign_worker_dates (p_assignment_id IN per_assignments_f.assignment_id%TYPE
482 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
483 ,p_start_date IN DATE
484 ,p_end_date IN DATE
485 ,p_fw_type IN VARCHAR2
486 ,p_tab_fw_dates OUT NOCOPY pay_au_foreign_workers.tab_fw_dates)
487 AS
488
489 CURSOR get_fw_asg_dates
490 (c_assignment_id per_assignments_f.assignment_id%TYPE
491 ,c_tax_unit_id VARCHAR2
492 ,c_start_date DATE
493 ,c_end_date DATE
494 ,c_status_id VARCHAR2)
495 IS
496 SELECT paf.assignment_id
497 ,paf.effective_start_date
498 ,paf.effective_end_date
499 ,paf.payroll_id
500 ,hsc.segment1 tax_unit_id
501 FROM per_assignments_f paf
502 ,hr_soft_coding_keyflex hsc
503 WHERE paf.assignment_id = c_assignment_id
504 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
505 AND hsc.segment1 LIKE c_tax_unit_id
506 AND to_char(paf.assignment_status_type_id) like c_status_id
507 AND paf.effective_start_date <= c_end_date
508 AND paf.effective_end_date >= c_start_date
509 ORDER BY paf.effective_start_date ASC;
510
511 l_tab_fw_dates tab_fw_dates;
512 l_ret_fw_dates tab_fw_dates;
513
514 i_index NUMBER;
515 i_last NUMBER;
516 l_proc_name VARCHAR2(100);
517
518 l_term_emp VARCHAR2(10);
519 l_act_term_date DATE;
520 l_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
521
522 BEGIN
523
524 g_debug := hr_utility.debug_enabled;
525
526 IF g_debug
527 THEN
528 l_proc_name := g_package||'.get_foreign_worker_dates';
529 hr_utility.set_location('Entering procedure '||l_proc_name,600);
530 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,600);
531 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,600);
532 END IF;
533
534 IF (NOT cached)
535 THEN
536 populate_asg_status_id;
537 END IF;
538
539 l_status_id := get_asg_status_id(p_fw_type);
540
541 i_index := -1;
542 FOR csr_rec IN get_fw_asg_dates(p_assignment_id
543 ,NVL(to_char(p_tax_unit_id),'%') /* Bug 9950136 - for Accruals independent of LE */
544 ,p_start_date
545 ,p_end_date
546 ,to_char(l_status_id))
547 LOOP
548
549 i_index := i_index + 1;
550 l_tab_fw_dates(i_index).assignment_id := p_assignment_id;
551 l_tab_fw_dates(i_index).start_date := csr_rec.effective_start_date;
552 l_tab_fw_dates(i_index).end_date := csr_rec.effective_end_date;
553 l_tab_fw_dates(i_index).payroll_id := csr_rec.payroll_id;
554 l_tab_fw_dates(i_index).tax_unit_id := csr_rec.tax_unit_id;
555
556 END LOOP;
557
558 /* Adjust for the terminated as a FW case - add periods post termination as FW periods
559 An employee should be deemed terminated as a Foreign Worker if the assignment status is set as 'Foreign Worker'
560 as on Actual Termination Date
561 */
562 l_term_emp := check_fw_terminated(p_assignment_id
563 ,p_tax_unit_id
564 ,p_start_date
565 ,p_end_date
566 ,p_fw_type
567 ,l_act_term_date);
568
569 IF l_term_emp = 'Y'
570 THEN
571 /* Assumption - that the last row in PL/SQL table corresponds to the assignment row of ACT
572 Now pick all asg rows from ACT + 1 to p_end_date and add to PL/SQL Table */
573 FOR csr_rec IN get_fw_asg_dates(p_assignment_id
574 ,NVL(to_char(p_tax_unit_id),'%')
575 ,l_act_term_date + 1
576 ,p_end_date
577 ,'%')
578 LOOP
579
580 i_index := i_index + 1;
581 l_tab_fw_dates(i_index).assignment_id := p_assignment_id;
582 l_tab_fw_dates(i_index).start_date := csr_rec.effective_start_date;
583 l_tab_fw_dates(i_index).end_date := csr_rec.effective_end_date;
584 l_tab_fw_dates(i_index).payroll_id := csr_rec.payroll_id;
585 l_tab_fw_dates(i_index).tax_unit_id := csr_rec.tax_unit_id;
586 END LOOP;
587 END IF;
588
589 /* Adjust the Start/End Dates to be within the period specified - just in case FW periods stretched across
590 start/end date params */
591
592 IF l_tab_fw_dates.COUNT > 0
593 THEN
594 i_index := l_tab_fw_dates.FIRST;
595 l_tab_fw_dates(i_index).start_date := GREATEST(p_start_date,l_tab_fw_dates(i_index).start_date);
596
597 i_index := l_tab_fw_dates.LAST;
598 l_tab_fw_dates(i_index).end_date := LEAST(p_end_date,l_tab_fw_dates(i_index).end_date);
599 END IF;
600
601 /* Adjust the start date/end date for continuous periods that have non-Payroll and non-Tax Unit changes
602 As such since the cursor picks assignment records only for a particular tax unit, effectively we'll
603 be adjusting only for any payroll changes */
604
605 i_index := -1;
606
607 IF l_tab_fw_dates.COUNT > 1
608 THEN
609 FOR i in l_tab_fw_dates.FIRST..l_tab_fw_dates.LAST-1
610 LOOP
611 IF i_index = -1
612 THEN
613 i_index := i_index + 1;
614 l_ret_fw_dates(i_index) := l_tab_fw_dates(i);
615 END IF;
616
617 IF ( l_tab_fw_dates(i).end_date + 1 = l_tab_fw_dates(i+1).start_date
618 AND l_tab_fw_dates(i).payroll_id = l_tab_fw_dates(i+1).payroll_id
619 AND l_tab_fw_dates(i).tax_unit_id = l_tab_fw_dates(i+1).tax_unit_id
620 )
621 THEN
622 l_ret_fw_dates(i_index).end_date := l_tab_fw_dates(i+1).end_date;
623 ELSE
624 i_index := i_index + 1;
625 l_ret_fw_dates(i_index) := l_tab_fw_dates(i+1);
626 END IF;
627 END LOOP;
628 p_tab_fw_dates := l_ret_fw_dates;
629 ELSE
630 p_tab_fw_dates := l_tab_fw_dates;
631 END IF;
632
633
634 IF g_debug
635 THEN
636 IF p_tab_fw_dates.COUNT > 0
637 THEN
638 FOR i IN p_tab_fw_dates.FIRST..p_tab_fw_dates.LAST
639 LOOP
640 hr_utility.set_location('i '||i,600);
641 hr_utility.set_location('start_date '||p_tab_fw_dates(i).start_date,600);
642 hr_utility.set_location('end_date '||p_tab_fw_dates(i).end_date,600);
643 hr_utility.set_location('payroll_id '||p_tab_fw_dates(i).payroll_id,600);
644 hr_utility.set_location('tax_unit_id '||p_tab_fw_dates(i).tax_unit_id,600);
645 END LOOP;
646 END IF;
647 hr_utility.set_location('Leaving procedure '||l_proc_name,600);
648 END IF;
649
650 EXCEPTION
651 WHEN OTHERS then
652 IF g_debug
653 THEN
654 hr_utility.set_location('Error in procedure '||l_proc_name,600);
655 END IF;
656
657 END get_foreign_worker_dates;
658
659
660 /*
661 Name : get_defined_balances
662 Type : Procedure
663 Access : Public
664 Description : This procedure returns a Balance pl/sql table of the
665 defined_balance_id for use in pay_balance_pkg call
666 Inputs : p_balances_tab - List of Balance names
667 p_dimension_name - Dimension name
668 Outputs : p_def_bal_tab - Defined Balance ID Pl/sql table
669
670 */
671
672
673 PROCEDURE get_defined_balances(p_balances_tab IN pay_au_foreign_workers.tab_bal_type
674 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
675 ,p_def_bal_tab OUT NOCOPY pay_balance_pkg.t_balance_value_tab
676 )
677 IS
678
679 CURSOR csr_get_def_bal
680 (c_balance_type_id pay_balance_types.balance_type_id%TYPE
681 ,c_dim_name pay_balance_dimensions.dimension_name%TYPE)
682 IS
683 SELECT pdb.defined_balance_id
684 FROM pay_defined_balances pdb
685 ,pay_balance_dimensions pbd
686 ,pay_balance_types pbt
687 WHERE pbt.balance_type_id = c_balance_type_id
688 AND pdb.balance_type_id = pbt.balance_type_id
689 AND pdb.balance_dimension_id = pbd.balance_dimension_id
690 AND pbd.dimension_name = c_dim_name;
691
692 l_proc_name VARCHAR2(100);
693 l_def_bal_id NUMBER;
694
695 l_def_bal_tab pay_balance_pkg.t_balance_value_tab;
696
697 l_no_def_bal EXCEPTION;
698
699 BEGIN
700
701 g_debug := hr_utility.debug_enabled;
702
703 IF g_debug
704 THEN
705 l_proc_name := g_package||'.get_defined_balances';
706 hr_utility.set_location('Entering procedure '||l_proc_name,1000);
707 END IF;
708
709 l_def_bal_tab.DELETE;
710 IF p_balances_tab.COUNT > 0
711 THEN
712 FOR i IN p_balances_tab.FIRST..p_balances_tab.LAST
713 LOOP
714
715 /* Handle case where dimension is not attached to balance
716 PTD may not be there for allowance balance. Anitha - Revisit the Manage Allowances Conc Prog.
717 */
718 OPEN csr_get_def_bal(p_balances_tab(i).balance_type_id,p_dimension_name);
719 FETCH csr_get_def_bal INTO l_def_bal_id;
720 IF csr_get_def_bal%NOTFOUND
721 THEN
722 l_def_bal_id := p_balances_tab(i).balance_type_id;
723 raise l_no_def_bal;
724 END IF;
725
726 CLOSE csr_get_def_bal;
727
728 l_def_bal_tab(i).defined_balance_id := l_def_bal_id;
729
730 END LOOP;
731 END IF;
732
733 p_def_bal_tab := l_def_bal_tab;
734
735 IF g_debug
736 THEN
737 hr_utility.set_location('Leaving procedure '||l_proc_name,1000);
738 END IF;
739 EXCEPTION
740 WHEN l_no_def_bal THEN
741 hr_utility.set_location('Exception in procedure '||l_proc_name,1000);
742 hr_utility.set_location('Defined Balance not found for Bal Type ID '||l_def_bal_id||' and Dimension '||p_dimension_name,1000);
743 raise;
744 WHEN OTHERS THEN
745 hr_utility.set_location('Exception others in procedure '||l_proc_name,1000);
746 END get_defined_balances;
747
748 /*
749 Name : is_le_changed
750 Type : Function
751 Access : Public
752 Description : This function returns 'Y' if the assignment has two
753 LE's in the payroll period
754 Inputs : p_assignment_id - Assignment ID
755 p_tax_unit_id - Tax Unit ID
756 p_payroll_id - Payroll ID
757 p_time_period_id - Time Period ID
758 Outputs : 'Y'/'N' - Employee has more than one LE, then 'Y' else 'N'
759
760 */
761
762 FUNCTION is_le_changed(p_assignment_id IN per_assignments_f.assignment_id%TYPE
763 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
764 ,p_payroll_id IN per_time_periods.payroll_id%TYPE
765 ,p_time_period_id IN per_time_periods.time_period_id%TYPE)
766 RETURN VARCHAR2
767 IS
768
769 CURSOR other_le_exists(c_assignment_id per_assignments_f.assignment_id%TYPE
770 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
771 ,c_payroll_id per_time_periods.payroll_id%TYPE
772 ,c_time_period_id per_time_periods.time_period_id%TYPE)
773 IS
774 SELECT 'Y'
775 FROM DUAL
776 WHERE EXISTS
777 ( SELECT '1'
778 FROM per_assignments_f paf
779 ,hr_soft_coding_keyflex hsc
780 ,per_time_periods ptp
781 WHERE ptp.payroll_id = c_payroll_id
782 AND ptp.time_period_id = c_time_period_id
783 AND paf.assignment_id = c_assignment_id
784 AND paf.effective_start_date <= ptp.end_date
785 AND paf.effective_end_date >= ptp.start_date
786 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
787 AND hsc.segment1 <> c_tax_unit_id
788 );
789
790 l_return_value VARCHAR2(10);
791 l_procedure VARCHAR2(200);
792
793 BEGIN
794
795 g_debug := hr_utility.debug_enabled;
796 IF g_debug
797 THEN
798 l_procedure := g_package||'.is_le_changed';
799 hr_utility.set_location('In procedure '||l_procedure,1000);
800 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,1000);
801 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,1000);
802 hr_utility.set_location('IN p_time_period_id '||p_time_period_id,1000);
803 END IF;
804
805 OPEN other_le_exists(p_assignment_id
806 ,p_tax_unit_id
807 ,p_payroll_id
808 ,p_time_period_id);
809 FETCH other_le_exists INTO l_return_value;
810 IF other_le_exists%NOTFOUND
811 THEN
812 l_return_value := 'N';
813 END IF;
814 CLOSE other_le_exists;
815
816 l_return_value := NVL(l_return_value,'N');
817
818 IF g_debug
819 THEN
820 hr_utility.set_location('OUT l_return_value '||l_return_value,1000);
821 hr_utility.set_location('Leaving procedure '||l_procedure,1000);
822 END IF;
823
824 RETURN l_return_value;
825
826 END is_le_changed;
827
828
829 /*
830 Name : get_le_changed_prorate_factor
831 Type : Function
832 Access : Private
833 Description : This function returns the number of days to be considered
834 for proration when there is more than one LE active in the period
835 Inputs : p_assignment_id - Assignment ID
836 p_tax_unit_id - Tax Unit ID
837 p_payroll_id - Payroll ID
838 p_time_period_id - Time Period ID
839 p_fw_type - Foreign Worker Type
840 Outputs : Number - Number of days to be taken for proration
841
842 */
843
844 FUNCTION get_le_changed_prorate_factor(p_assignment_id IN per_assignments_f.assignment_id%TYPE
845 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
846 ,p_payroll_id IN per_time_periods.payroll_id%TYPE
847 ,p_time_period_id IN per_time_periods.time_period_id%TYPE
848 ,p_fw_type IN VARCHAR2
849 )
850 RETURN NUMBER
851 IS
852
853 CURSOR le_at_period_end(c_assignment_id per_assignments_f.assignment_id%TYPE
854 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
855 ,c_payroll_id per_time_periods.payroll_id%TYPE
856 ,c_time_period_id per_time_periods.time_period_id%TYPE)
857 IS
858 SELECT 'Y'
859 FROM DUAL
860 WHERE EXISTS
861 ( SELECT '1'
862 FROM per_assignments_f paf
863 ,hr_soft_coding_keyflex hsc
864 ,per_time_periods ptp
865 WHERE ptp.payroll_id = c_payroll_id
866 AND ptp.time_period_id = c_time_period_id
867 AND paf.assignment_id = c_assignment_id
868 AND ptp.end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
869 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
870 AND hsc.segment1 = c_tax_unit_id
871 );
872
873 CURSOR get_fw_days(c_assignment_id per_assignments_f.assignment_id%TYPE
874 ,c_status_id per_assignment_status_types.assignment_status_type_id%TYPE
875 ,c_payroll_id per_time_periods.payroll_id%TYPE
876 ,c_time_period_id per_time_periods.time_period_id%TYPE)
877 IS
878 SELECT ptp.time_period_id
879 ,GREATEST(paf.effective_start_date,ptp.start_date) prorate_start
880 ,LEAST(paf.effective_end_date,ptp.end_date) prorate_end
881 FROM per_assignments_f paf
882 ,per_time_periods ptp
883 WHERE paf.assignment_id = c_assignment_id
884 AND ptp.payroll_id = c_payroll_id
885 AND ptp.time_period_id = c_time_period_id
886 AND paf.assignment_status_type_id = c_status_id
887 AND ptp.start_date <= paf.effective_end_date
888 AND ptp.end_date >= paf.effective_start_date;
889
890
891 l_procedure VARCHAR2(100);
892 l_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
893
894 l_le_exists VARCHAR2(10);
895 l_return_value NUMBER;
896
897 BEGIN
898 /*
899 Check if the LE passed is the same LE active as of End of Period.
900 If So, payments in this period corresponds to this LE - else return prorate factor as 0.
901 Eg:
902 01-Jan-2010 to 15-Jan-2010 - LE1
903 16-Jan-2010 to 31-Jan-2010 - LE2
904
905 If LE1 is passed,
906 As on end of period(31-Jan-2010) - this LE is not active. So all payments in this period
907 does not correspond to LE. Return prorate factor as 0.
908 If LE2 is passed,
909 LE is active as on end of period (31-Jan-2010) - Check the days marked as Foreign Worker
910 in period irrespective of LE and return as prorate factor.
911 */
912
913 g_debug := hr_utility.debug_enabled;
914 IF g_debug
915 THEN
916 l_procedure := g_package||'.get_le_changed_prorate_factor';
917 hr_utility.set_location('In procedure '||l_procedure,1000);
918 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,1000);
919 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,1000);
920 hr_utility.set_location('IN p_time_period_id '||p_time_period_id,1000);
921 hr_utility.set_location('IN p_fw_type '||p_fw_type,1000);
922 END IF;
923
924 l_status_id := get_asg_status_id(p_fw_type);
925
926 OPEN le_at_period_end(p_assignment_id
927 ,p_tax_unit_id
928 ,p_payroll_id
929 ,p_time_period_id);
930 FETCH le_at_period_end INTO l_le_exists;
931 IF le_at_period_end%NOTFOUND
932 THEN
933 l_le_exists := 'N';
934 END IF;
935 CLOSE le_at_period_end;
936
937 IF l_le_exists = 'N'
938 THEN
939 /* Proration factor is 0 */
940 l_return_value := 0;
941 ELSE
942 /* Count the number of days in a loop */
943 l_return_value := 0;
944
945 FOR csr_rec IN get_fw_days(p_assignment_id
946 ,l_status_id
947 ,p_payroll_id
948 ,p_time_period_id)
949 LOOP
950 l_return_value := l_return_value + ((csr_rec.prorate_end - csr_rec.prorate_start) + 1);
951 END LOOP;
952 END IF;
953
954 IF g_debug
955 THEN
956 hr_utility.set_location('OUT l_return_value '||l_return_value,1000);
957 hr_utility.set_location('Leaving procedure '||l_procedure,1000);
958 END IF;
959
960 RETURN l_return_value;
961
962 END get_le_changed_prorate_factor;
963
964
965 /*
966 Name : get_foreign_payment_amounts
967 Type : Procedure
968 Access : Public
969 Description : This procedure returns a complex Data type with the Prorated Balance Value Details
970 of given Foreign Worker Type between given dates.
971 This function implements the Proration Logic
972 Inputs : p_assignment_id - Assignment ID
973 p_tax_unit_id - Legal Employer
974 p_tab_fw_dates - FW Assignment Details Complex Data Type
975 p_start_date - Start Date
976 p_end_date - End Date
977 p_fw_type - Foreign Worker Type, Valid Values are,
978 pay_au_foreign_workers.g_fw_f_type - Foreign Worker F
979 pay_au_foreign_workers.g_fw_j_type - Foreign Worker J
980 p_balances_tab - PL/SQL table of Balances for which prorated values should be fetched
981 Outputs : Complex Data Type - pay_balance_pkg.t_detailed_bal_out_tab
982 contains Balance Values
983
984 */
985
986 PROCEDURE get_foreign_payment_amounts(p_assignment_id IN per_assignments_f.assignment_id%TYPE
987 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
988 ,p_tab_fw_dates IN pay_au_foreign_workers.tab_fw_dates
989 ,p_start_date IN DATE
990 ,p_end_date IN DATE
991 ,p_fw_type IN VARCHAR2
992 ,p_balances_tab IN pay_au_foreign_workers.tab_bal_type
993 ,p_output_table OUT NOCOPY pay_balance_pkg.t_detailed_bal_out_tab)
994 IS
995
996 TYPE prorate_dat IS RECORD
997 (
998 prorate_start DATE
999 ,prorate_end DATE
1000 ,period_start_date DATE
1001 ,period_end_date DATE
1002 ,prorate_factor NUMBER
1003 ,number_of_periods NUMBER
1004 ,denom_factor NUMBER);
1005
1006 TYPE prorate_tab IS TABLE OF prorate_dat INDEX BY BINARY_INTEGER;
1007
1008 /* Cost 7 on hr2rw102 */
1009 CURSOR csr_get_period_prorate(c_assignment_id per_assignments_f.assignment_id%TYPE
1010 ,c_fw_start_date DATE
1011 ,c_fw_end_date DATE
1012 ,c_start_date DATE
1013 ,c_end_date DATE)
1014 IS
1015 SELECT ptp.time_period_id
1016 ,GREATEST(c_start_date,c_fw_start_date,ptp.start_date) prorate_start
1017 ,LEAST(c_end_date,c_fw_end_date,ptp.end_date) prorate_end
1018 ,((ptp.end_date - ptp.start_date) + 1) denom_factor
1019 ,ptp.start_date
1020 ,ptp.end_date
1021 ,ptp.payroll_id
1022 ,pay_au_foreign_workers.is_le_changed(c_assignment_id,p_tax_unit_id,ptp.payroll_id,ptp.time_period_id) le_change_flag
1023 FROM per_assignments_f paf
1024 ,per_time_periods ptp
1025 WHERE paf.assignment_id = c_assignment_id
1026 AND c_fw_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1027 AND paf.payroll_id = ptp.payroll_id
1028 AND ptp.start_date <= c_fw_end_date
1029 AND ptp.end_date >= c_fw_start_date
1030 AND ptp.start_date <= c_end_date
1031 AND ptp.end_date >= c_start_date;
1032
1033 /* Check cost - 13 on hr2rw102 - should be fine! */
1034 CURSOR csr_max_action_id
1035 (c_assignment_id per_assignments_f.assignment_id%TYPE
1036 ,c_time_period_id per_time_periods.time_period_id%TYPE
1037 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
1038 ,c_start_date DATE
1039 ,c_end_date DATE
1040 )
1041 IS
1042 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1043 -- ,MAX(paa.action_sequence) action_sequence
1044 FROM pay_assignment_actions paa,
1045 pay_payroll_actions ppa,
1046 per_assignments_f paf,
1047 per_time_periods ptp
1048 WHERE paa.assignment_id = paf.assignment_id
1049 AND paf.assignment_id = c_assignment_id
1050 AND ppa.payroll_action_id = paa.payroll_action_id
1051 AND ppa.effective_date BETWEEN c_start_date AND c_end_date /* Join Ensures I pick Ass Act within year only */
1052 AND ppa.payroll_id = paf.payroll_id
1053 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
1054 AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
1055 AND paa.action_status = 'C'
1056 AND paa.tax_unit_id = c_tax_unit_id
1057 AND ptp.time_period_id = c_time_period_id
1058 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
1059
1060
1061 /* Check cost - 13 on hr2rw102 - should be fine! */
1062 CURSOR csr_ytd_max_action_id
1063 (c_assignment_id per_assignments_f.assignment_id%TYPE
1064 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
1065 ,c_start_date DATE
1066 ,c_end_date DATE
1067 )
1068 IS
1069 SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1070 -- ,MAX(paa.action_sequence) action_sequence
1071 FROM pay_assignment_actions paa,
1072 pay_payroll_actions ppa,
1073 per_assignments_f paf
1074 WHERE paa.assignment_id = paf.assignment_id
1075 AND paf.assignment_id = c_assignment_id
1076 AND ppa.payroll_action_id = paa.payroll_action_id
1077 AND ppa.effective_date BETWEEN c_start_date AND c_end_date /* Join Ensures I pick Ass Act within year only */
1078 AND ppa.payroll_id = paf.payroll_id
1079 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
1080 AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
1081 AND paa.action_status = 'C'
1082 AND paa.tax_unit_id = c_tax_unit_id;
1083
1084
1085 l_output_tab pay_balance_pkg.t_detailed_bal_out_tab;
1086 l_return_tab pay_balance_pkg.t_detailed_bal_out_tab;
1087 l_contexts_tab pay_balance_pkg.t_context_tab;
1088
1089 l_proc_name VARCHAR2(100);
1090 l_prorate_tab prorate_tab;
1091
1092 l_time_period_id NUMBER;
1093 l_max_ass_act_id NUMBER;
1094
1098 l_run_dimension VARCHAR2(20) := '_ASG_LE_RUN';
1095 l_fin_year_start DATE;
1096 l_fin_year_end DATE;
1097
1099 l_ptd_dimension VARCHAR2(20) := '_ASG_LE_PTD';
1100 l_ytd_dimension VARCHAR2(20) := '_ASG_LE_YTD';
1101
1102 l_run_balance_tab pay_balance_pkg.t_balance_value_tab;
1103 l_ptd_balance_tab pay_balance_pkg.t_balance_value_tab;
1104 l_ytd_balance_tab pay_balance_pkg.t_balance_value_tab;
1105
1106 l_fw_days_index NUMBER;
1107
1108 l_le_fw_days NUMBER;
1109
1110 l_gen NUMBER; /* Anitha - remove it later */
1111
1112 BEGIN
1113
1114 g_debug := hr_utility.debug_enabled;
1115
1116 IF g_debug
1117 THEN
1118 l_proc_name := g_package||'get_foreign_payment_amounts';
1119 hr_utility.set_location('Entering l_proc_name '||l_proc_name,1000);
1120 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,1000);
1121 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,1000);
1122 END IF;
1123
1124 IF (NOT cached)
1125 THEN
1126 populate_asg_status_id;
1127 END IF;
1128
1129 l_fin_year_start := hr_au_routes.span_start(p_start_date, 1, '01-07-');
1130 l_fin_year_end := add_months(l_fin_year_start,12) - 1;
1131
1132 IF (p_tab_fw_dates.COUNT > 0 AND
1133 p_tab_fw_dates(0).start_date = l_fin_year_start AND
1134 p_tab_fw_dates(0).end_date = l_fin_year_end)
1135 THEN
1136 /* FW Worker the whole year - entire YTD is FW earnings */
1137 /* Populate the YTD Dimensions Defined Balances */
1138 l_ytd_balance_tab.DELETE ;
1139 get_defined_balances(p_balances_tab
1140 ,l_ytd_dimension
1141 ,l_ytd_balance_tab);
1142
1143 /* Call pay_balance_pkg */
1144 l_output_tab.DELETE;
1145 l_return_tab.DELETE;
1146 l_contexts_tab(1).tax_unit_id := p_tax_unit_id ;
1147
1148 OPEN csr_ytd_max_action_id(p_assignment_id
1149 ,p_tax_unit_id
1150 ,p_start_date
1151 ,p_end_date);
1152 FETCH csr_ytd_max_action_id INTO l_max_ass_act_id;
1153 CLOSE csr_ytd_max_action_id;
1154
1155 pay_balance_pkg.get_value
1156 (p_assignment_action_id => l_max_ass_act_id
1157 ,p_defined_balance_lst => l_ytd_balance_tab
1158 ,p_context_lst => l_contexts_tab
1159 ,p_output_table => l_return_tab);
1160
1161 p_output_table := l_return_tab;
1162
1163 ELSE
1164 /* Populate the PTD Dimensions Defined Balances */
1165 l_ptd_balance_tab.DELETE ;
1166 get_defined_balances(p_balances_tab
1167 ,l_ptd_dimension
1168 ,l_ptd_balance_tab);
1169
1170 /* Add the Balance 'Foreign Worker Days' to the list of balances to be fetched
1171 */
1172 IF l_ptd_balance_tab.COUNT >0
1173 THEN
1174 l_fw_days_index := l_ptd_balance_tab.LAST + 1;
1175 l_ptd_balance_tab(l_fw_days_index).defined_balance_id := g_fw_def_bal_id;
1176 END IF;
1177
1178 l_output_tab.DELETE;
1179
1180 IF l_ptd_balance_tab.COUNT >0
1181 THEN
1182 FOR i IN l_ptd_balance_tab.FIRST..l_ptd_balance_tab.LAST
1183 LOOP
1184 l_output_tab(i).defined_balance_id := l_ptd_balance_tab(i).defined_balance_id;
1185 l_output_tab(i).balance_value := 0;
1186 END LOOP;
1187 END IF;
1188
1189 IF (p_tab_fw_dates.COUNT > 0 AND l_ptd_balance_tab.COUNT >0)
1190 THEN
1191 FOR i IN p_tab_fw_dates.FIRST..p_tab_fw_dates.LAST
1192 LOOP
1193
1194 FOR csr_rec IN csr_get_period_prorate(p_assignment_id
1195 ,p_tab_fw_dates(i).start_date
1196 ,p_tab_fw_dates(i).end_date
1197 ,p_start_date
1198 ,p_end_date)
1199 LOOP
1200 IF l_prorate_tab.EXISTS(csr_rec.time_period_id)
1201 THEN
1202 /* Since the denominator is the same, we can just add the prorate factor */
1203 l_prorate_tab(csr_rec.time_period_id).prorate_start := LEAST(l_prorate_tab(csr_rec.time_period_id).prorate_start
1204 ,csr_rec.prorate_start);
1205 l_prorate_tab(csr_rec.time_period_id).prorate_end := GREATEST(l_prorate_tab(csr_rec.time_period_id).prorate_end
1206 ,csr_rec.prorate_end);
1207 l_prorate_tab(csr_rec.time_period_id).period_start_date := csr_rec.start_date;
1208 l_prorate_tab(csr_rec.time_period_id).period_end_date := csr_rec.end_date;
1209 l_prorate_tab(csr_rec.time_period_id).prorate_factor := l_prorate_tab(csr_rec.time_period_id).prorate_factor +
1210 ((csr_rec.prorate_end - csr_rec.prorate_start)+1)/csr_rec.denom_factor;
1211 l_prorate_tab(csr_rec.time_period_id).number_of_periods := l_prorate_tab(csr_rec.time_period_id).number_of_periods + 1 ;
1212 ELSE
1216 l_prorate_tab(csr_rec.time_period_id).period_end_date := csr_rec.end_date;
1213 l_prorate_tab(csr_rec.time_period_id).prorate_start := csr_rec.prorate_start;
1214 l_prorate_tab(csr_rec.time_period_id).prorate_end := csr_rec.prorate_end;
1215 l_prorate_tab(csr_rec.time_period_id).period_start_date := csr_rec.start_date;
1217 l_prorate_tab(csr_rec.time_period_id).prorate_factor := ((csr_rec.prorate_end - csr_rec.prorate_start)+1)/csr_rec.denom_factor;
1218 l_prorate_tab(csr_rec.time_period_id).number_of_periods := 1 ;
1219 l_prorate_tab(csr_rec.time_period_id).denom_factor := csr_rec.denom_factor;
1220 END IF;
1221
1222 /* Adjust Prorate Factor for LE Change
1223 By design of LE, we expect that in a period only one LE can be set. Now. Check if the fw record is cos of a LE change, if so
1224 adjust the proration factor accordingly
1225 */
1226
1227 IF csr_rec.le_change_flag = 'Y'
1228 THEN
1229
1230 NULL;
1231 /* Call function to set the prorate_factor */
1232 l_le_fw_days := get_le_changed_prorate_factor
1233 (p_assignment_id
1234 ,p_tax_unit_id
1235 ,csr_rec.payroll_id
1236 ,csr_rec.time_period_id
1237 ,p_fw_type);
1238 l_prorate_tab(csr_rec.time_period_id).prorate_factor := l_le_fw_days/csr_rec.denom_factor;
1239 l_prorate_tab(csr_rec.time_period_id).number_of_periods := -999 ; /* Indicates LE change case */
1240 END IF;
1241
1242 END LOOP; /* Periods Cursor Loop */
1243 END LOOP; /* Asg PL/SQL table loop */
1244
1245 /* Comment if not required to print debug */
1246 IF g_debug
1247 THEN
1248 l_time_period_id := l_prorate_tab.FIRST;
1249
1250 WHILE ( l_time_period_id IS NOT NULL)
1251 LOOP
1252 hr_utility.set_location('l_time_period_id '||l_time_period_id,1000);
1253 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_start '||l_prorate_tab(l_time_period_id).prorate_start,1000);
1254 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_end '||l_prorate_tab(l_time_period_id).prorate_end,1000);
1255 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_factor '||l_prorate_tab(l_time_period_id).prorate_factor,1000);
1256 hr_utility.set_location('l_prorate_tab(l_time_period_id).number_of_periods '||l_prorate_tab(l_time_period_id).number_of_periods,1000);
1257 l_time_period_id := l_prorate_tab.NEXT(l_time_period_id);
1258 END LOOP;
1259
1260 END IF;
1261
1262
1263 /* Anitha - Adjust for consecutive assignment records - DONE in fw_dates proc
1264 - Adjust for changed payrolls - DONE Actual Number of Days in Asg is used and prorated
1265 - Adjust for end of year case where PTD Not equal to YTD. I think we can do that at the cursor level
1266 only while picking on Assignment action ID's
1267 - Adjust for broken FW periods in the same payroll period - DONE - Add prorate factor since denom is same
1268 */
1269
1270 l_time_period_id := l_prorate_tab.FIRST;
1271
1272 WHILE ( l_time_period_id IS NOT NULL)
1273 LOOP
1274
1275 OPEN csr_max_action_id(p_assignment_id
1276 ,l_time_period_id
1277 ,p_tax_unit_id
1278 ,p_start_date
1279 ,p_end_date);
1280 FETCH csr_max_action_id INTO l_max_ass_act_id;
1281 CLOSE csr_max_action_id;
1282
1283 l_return_tab.DELETE;
1284 l_contexts_tab(1).tax_unit_id := p_tax_unit_id ;
1285
1286 pay_balance_pkg.get_value
1287 (p_assignment_action_id => l_max_ass_act_id
1288 ,p_defined_balance_lst => l_ptd_balance_tab
1289 ,p_context_lst => l_contexts_tab
1290 ,p_output_table => l_return_tab);
1291
1292 IF g_debug
1293 THEN
1294 hr_utility.set_location('l_time_period_id '||l_time_period_id,1000);
1295 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_start '||l_prorate_tab(l_time_period_id).prorate_start,1000);
1296 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_end '||l_prorate_tab(l_time_period_id).prorate_end,1000);
1297 hr_utility.set_location('l_prorate_tab(l_time_period_id).prorate_factor '||l_prorate_tab(l_time_period_id).prorate_factor,1000);
1298 hr_utility.set_location('l_prorate_tab(l_time_period_id).number_of_periods '||l_prorate_tab(l_time_period_id).number_of_periods,1000);
1299 END IF;
1300
1301 /* Alter proration Factor if Balance 'Foreign Days Worked' is set in period */
1302 IF (NVL(l_return_tab(l_fw_days_index).balance_value,0) > 0) /* Assume its always postive */
1303 THEN
1304 l_prorate_tab(l_time_period_id).prorate_factor := NVL(l_return_tab(l_fw_days_index).balance_value,0)/l_prorate_tab(l_time_period_id).denom_factor;
1305 IF g_debug
1306 THEN
1307 hr_utility.set_location('Adjusted prorate_factor '||l_prorate_tab(l_time_period_id).prorate_factor,1000);
1308 END IF;
1309 END IF;
1310
1311 FOR i IN l_output_tab.FIRST..l_output_tab.LAST
1312 LOOP
1313
1314 l_output_tab(i).balance_value := l_output_tab(i).balance_value +
1315 NVL(l_return_tab(i).balance_value,0) *
1316 l_prorate_tab(l_time_period_id).prorate_factor;
1317 l_gen := NVL(l_return_tab(i).balance_value,0) *
1318 l_prorate_tab(l_time_period_id).prorate_factor;
1319 hr_utility.set_location('Anitha Value '||l_gen,1000);
1320 END LOOP;
1321
1322 l_time_period_id := l_prorate_tab.NEXT(l_time_period_id);
1323
1324 END LOOP;
1325
1326 FOR i IN l_output_tab.FIRST..l_output_tab.LAST
1327 LOOP
1328 hr_utility.set_location('l_output_tab.defined_balance_id '||l_output_tab(i).defined_balance_id,1000);
1329 hr_utility.set_location('l_output_tab.balance_value '||l_output_tab(i).balance_value,1000);
1330 END LOOP;
1331 ELSE
1332 IF g_debug
1333 THEN
1334 hr_utility.set_location('Count = 0, no processing done!',1000);
1335 END IF;
1336 END IF; /* End Count Check */
1337
1338 p_output_table := l_output_tab;
1339
1340 END IF; /* Not YTD case check */
1341
1342 IF g_debug
1343 THEN
1344 hr_utility.set_location('Leaving l_proc_name '||l_proc_name,1000);
1345 END IF;
1346
1347 END get_foreign_payment_amounts;
1348
1349
1350
1351 /*
1352 Name : get_fw_between_dates
1353 Type : Procedure
1354 Access : Public
1355 Description : This procedure returns a subset of FW Dates from a larger PL/SQL table
1356 of dates.
1357 Inputs : p_fw_tab_dates - Complex Date type - Superset of FW Dates
1358 p_start_date - Start Date
1359 p_end_date - End Date
1360 Outputs : Complex Data Type - Complex Data type having subset based on dates
1361 given
1362
1363 */
1364
1365 PROCEDURE get_fw_between_dates
1366 (p_fw_tab_dates IN pay_au_foreign_workers.tab_fw_dates
1367 ,p_start_date IN DATE
1368 ,p_end_date IN DATE
1369 ,p_result_tab_dates OUT NOCOPY pay_au_foreign_workers.tab_fw_dates)
1370 IS
1371
1372 l_procedure VARCHAR2(100);
1373
1374 l_result_tab_dates pay_au_foreign_workers.tab_fw_dates;
1375 i_index NUMBER;
1376
1377 BEGIN
1378
1379 g_debug := hr_utility.debug_enabled;
1380 IF g_debug
1381 THEN
1382 l_procedure := g_package||'.get_fw_between_dates';
1383 hr_utility.set_location('Entering l_procedure '||l_procedure,1200);
1384 END IF;
1385
1386 IF p_fw_tab_dates.COUNT > 0
1387 THEN
1388 i_index := -1;
1389 FOR i IN p_fw_tab_dates.FIRST..p_fw_tab_dates.LAST
1390 LOOP
1391 IF ( p_fw_tab_dates(i).start_date <= p_end_date
1392 AND p_fw_tab_dates(i).end_date >= p_start_date)
1393 THEN
1394
1395 i_index := i_index + 1;
1396 l_result_tab_dates(i_index) := p_fw_tab_dates(i);
1397 END IF;
1398 END LOOP;
1399 END IF;
1400
1401 IF l_result_tab_dates.COUNT > 0
1402 THEN
1403
1404 i_index := l_result_tab_dates.FIRST;
1405 l_result_tab_dates(i_index).start_date := GREATEST(p_start_date,l_result_tab_dates(i_index).start_date);
1406
1407 i_index := l_result_tab_dates.LAST;
1408 l_result_tab_dates(i_index).end_date := LEAST(p_end_date,l_result_tab_dates(i_index).end_date);
1409 END IF;
1410
1411 IF g_debug
1412 THEN
1413 IF l_result_tab_dates.COUNT > 0
1414 THEN
1415 FOR i IN l_result_tab_dates.FIRST..l_result_tab_dates.LAST
1416 LOOP
1417 hr_utility.set_location('l_result_tab_dates(i_index).start_date '||l_result_tab_dates(i).start_date,1200);
1418 hr_utility.set_location('l_result_tab_dates(i_index).end_date '||l_result_tab_dates(i).end_date,1200);
1419 END LOOP;
1420 END IF;
1421 hr_utility.set_location('Leaving l_procedure '||l_procedure,1200);
1422 END IF;
1423
1424 p_result_tab_dates := l_result_tab_dates;
1425
1426 END get_fw_between_dates;
1427
1428 END pay_au_foreign_workers;
[Home] [Help]