[Home] [Help]
PACKAGE BODY: APPS.HR_AUBAL
Source
1 package body hr_aubal as
2 -- $Header: pyaubal.pkb 120.2.12020000.2 2012/07/04 20:42:18 amnaraya ship $
3
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All rights reserved
6
7 -- Date Author Bug/CR Num Notes
8 -- -----------+--------+----------+-----------------------------------------
9 -- 8-jan-2012 dduvvuri 13558427 Modified cursor get_latest_id in function get_latest_action_id for performance improvement
10 -- 21-Aug-2007 avenkatk 5371102 Cursor get_latest_id modified for performance, added join on payroll ID.
11 -- 23-Mar-2007 ksingla 5371102 Added hint USE_NL(ppa,paa) to cursor get_latest_id to remove MJC
12 -- 23-Jun-2004 srrajago 3603495 Cursor 'get_latest_id' modified for
13 -- Performance Fix.
14 -- 07-May-2004 avenkatk 3580487 Changed calls to pay_balance_pkg.get_value
15 -- 07-May-2004 avenkatk 3580487 Changed calls to pay_balance_pkg.get_value
16 -- Resolved GSCC errors.
17 -- 28-Aug-2003 Puchil 3010965 Changed the public functions to use
18 -- pay_balance_pkg.get_value
19 -- 28-Jul-2003 Vgsriniv 3057155 Replaced > than condition with >= for
20 -- checking expired year date in all the
21 -- relevant functions
22 -- 25-Jul-2003 Vgsriniv 3057155 Replaced > than condition with >= for
23 -- checking expired year date in the
24 -- function calc_asg_ytd
25 -- 10-Jul-2003 Apunekar 3019374 Performance fix in cursor get_latest_id
26 -- 03-Dec-2002 Ragovind 2689226 Added NOCOPY for the function get_owning_balance.
27 -- Added DBDRV command also.
28 -- 20-oct-2000 sgoggin 1472624 Added qualifier to end_date.
29 -- 22 Feb 2000 JTurner Added missing dimensions to
30 -- calc_all_balances (date mode)
31 -- 24-NOV-1999 sgoggin Created
32 g_tax_year_start constant varchar2(6) := '01-07-';
33 g_fbt_year_start constant varchar2(6) := '01-04-';
34 g_cal_year_start constant varchar2(6) := '01-01-';
35 --
36 --------------------------------------------------------------------------------
37 --
38 -- get correct type (private)
39 --
40 --------------------------------------------------------------------------------
41 --
42 -- this is a validation check to ensure that the assignment action is of the
43 -- correct type. this is called from all assignment action mode functions.
44 -- the assignment id is returned (and not assignment action id) because
45 -- this is to be used in the expired latest balance check. this function thus
46 -- has two uses - to validate the assignment action, and give the corresponding
47 -- assignmment id for that action.
48 --
49 function get_correct_type(p_assignment_action_id in number)
50 return number is
51 --
52 l_assignment_id number;
53 --
54 cursor get_corr_type ( c_assignment_action_id in number ) is
55 select assignment_id
56 from pay_assignment_actions paa
57 , pay_payroll_actions ppa
58 where paa.assignment_action_id = c_assignment_action_id
59 and ppa.payroll_action_id = paa.payroll_action_id
60 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
61 --
62 begin
63 --
64 open get_corr_type(p_assignment_action_id);
65 fetch get_corr_type into l_assignment_id;
66 close get_corr_type;
67 --
68 return l_assignment_id;
69 --
70 end get_correct_type;
71 --------------------------------------------------------------------------------
72 --
73 -- dimension relevant (private)
74 --
75 --------------------------------------------------------------------------------
76 --
77 -- this function checks that a value is required for the dimension
78 -- for this particular balance type. if so, the defined balance is returned.
79 --
80 function dimension_relevant ( p_balance_type_id in number
81 , p_database_item_suffix in varchar2
82 )
83 return number is
84 --
85 l_defined_balance_id number;
86 --
87 cursor relevant ( c_balance_type_id in number
88 , c_db_item_suffix in varchar2
89 ) is
90 select pdb.defined_balance_id
91 from pay_defined_balances pdb
92 , pay_balance_dimensions pbd
93 where pdb.balance_dimension_id = pbd.balance_dimension_id
94 and pbd.database_item_suffix = c_db_item_suffix
95 and pdb.balance_type_id = c_balance_type_id;
96 --
97 begin
98 --
99 open relevant ( p_balance_type_id
100 , p_database_item_suffix
101 );
102 fetch relevant into l_defined_balance_id;
103 close relevant;
104 --
105 return l_defined_balance_id;
106 --
107 end dimension_relevant;
108 --------------------------------------------------------------------------------
109 --
110 -- get latest action id (private)
111 --
112 --------------------------------------------------------------------------------
113 -- this function returns the latest assignment action id given an assignment
114 -- and effective date. this is called from all date mode functions.
115 --
116 function get_latest_action_id ( p_assignment_id in number
117 , p_effective_date in date
118 )
119 return number is
120 --
121 l_assignment_action_id number;
122 --
123 /* Bug No: 3603495 - Performance Fix */
124 /* Bug No 5371102 - Removed ORDERED and added join on Payroll ID */
125 /* Bug No 13558427 - Added condition for assignment id on assignments table for performance improvement for westpac customer*/
126
127 cursor get_latest_id ( c_assignment_id in number
128 , c_effective_date in date
129 ) is
130 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
131 from pay_assignment_actions paa
132 , per_assignments_f paf
133 , pay_payroll_actions ppa
134 where paa.assignment_id = c_assignment_id
135 and paf.assignment_id = c_assignment_id
136 and paf.assignment_id = paa.assignment_id
137 and ppa.payroll_action_id = paa.payroll_action_id
138 and ppa.effective_date <= c_effective_date
139 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
140 and paa.action_status='C'
141 and ppa.payroll_id = paf.payroll_id
142 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
143 and ppa.action_status='C' ; /*Bug - 3019374 */
144 --
145 begin
146 --
147 open get_latest_id(p_assignment_id, p_effective_date);
148 fetch get_latest_id into l_assignment_action_id;
149 close get_latest_id;
150 --
151 return l_assignment_action_id;
152 --
153 end get_latest_action_id;
154 --------------------------------------------------------------------------------
155 --
156 -- get latest date (private)
157 --
158 --
159 --------------------------------------------------------------------------------
160 --
161 -- find out the effective date of the latest balance of a particular
162 -- assignment action.
163 --
164 function get_latest_date(p_assignment_action_id number)
165 return date is
166 --
167 l_effective_date date;
168 --
169 cursor c_bal_date is
170 select ppa.effective_date
171 from pay_payroll_actions ppa
172 , pay_assignment_actions paa
173 where paa.payroll_action_id = ppa.payroll_action_id
174 and paa.assignment_action_id = p_assignment_action_id;
175 --
176 begin
177 --
178 open c_bal_date;
179 fetch c_bal_date into l_effective_date;
180 if c_bal_date%notfound then
181 l_effective_date := null;
182 -- raise_application_error(-20000,'this assignment action is invalid');
183 end if;
184 close c_bal_date;
185 --
186 return l_effective_date;
187 end get_latest_date;
188 --
189 -------------------------------------------------------------------------------
190 --
191 -- get_expired_year_date (private)
192 --
193 -------------------------------------------------------------------------------
194 --
195 -- find out the expiry of the year of the assignment action's effective date,
196 -- for expiry checking in the main functions.
197 --
198 function get_expired_year_date( p_action_effective_date date
199 , p_start_dd_mm varchar2)
200 return date is
201 --
202 l_expired_date date;
203 l_year_add_no number;
204 --
205 begin
206 --
207 if p_action_effective_date is not null then
208 --
209 if p_action_effective_date < to_date
210 (p_start_dd_mm || to_char
211 (p_action_effective_date,'yyyy'),'dd-mm-yyyy'
212 ) then
213 --
214 l_year_add_no := 0;
215 else
216 l_year_add_no := 1;
217 end if;
218 --
219 -- set expired date to the 1st of april next.
220 --
221 l_expired_date :=
222 ( to_date
223 (p_start_dd_mm || to_char
224 (to_number
225 (
226 to_char(p_action_effective_date,'yyyy')
227 )+ l_year_add_no
228 ),'dd-mm-yyyy'
229 )
230 );
231 --
232 end if;
233 --
234 return l_expired_date;
235 --
236 end get_expired_year_date;
237 --
238 -----------------------------------------------------------------------------
239 --
240 -- calc_all_balances
241 -- this is the generic overloaded function for calculating all balances
242 -- in assignment action mode.
243 --
244 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
245 -----------------------------------------------------------------------------
246 --
247 function calc_all_balances ( p_assignment_action_id in number
248 , p_defined_balance_id in number
249 )
250 return number
251 is
252 --
253 --
254 begin
255 --
256 return pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => p_defined_balance_id
257 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
258 ,P_TAX_UNIT_ID => null
259 ,P_JURISDICTION_CODE => null
260 ,P_SOURCE_ID => null
261 ,P_SOURCE_TEXT => null
262 ,P_TAX_GROUP => null
263 ,P_DATE_EARNED => null
264 );
265 --
266 end calc_all_balances;
267 --
268 -----------------------------------------------------------------------------
269 --
270 -- calc_all_balances
271 -- this is the overloaded generic function for calculating all balances
272 -- in date mode.
273 --
274 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
275 -----------------------------------------------------------------------------
276 --
277 function calc_all_balances ( p_effective_date in date
278 , p_assignment_id in number
279 , p_defined_balance_id in number
280 )
281 return number
282 is
283 --
284 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
285 --
286 begin
287 --
288 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
289 --
290 return pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => p_defined_balance_id
291 ,P_ASSIGNMENT_ACTION_ID => l_assignment_action_id
292 ,P_TAX_UNIT_ID => null
293 ,P_JURISDICTION_CODE => null
294 ,P_SOURCE_ID => null
295 ,P_SOURCE_TEXT => null
296 ,P_TAX_GROUP => null
297 ,P_DATE_EARNED => null
298 );
299 --
300 end calc_all_balances;
301 --
302 --------------------------------------------------------------------------------
303 --
304 -- calc_asg_ytd
305 -- calculate balances for assignment year to date
306 --
307 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
308 --------------------------------------------------------------------------------
309 --
310 function calc_asg_ytd ( p_assignment_action_id in number
311 , p_balance_type_id in number
312 , p_effective_date in date default null
313 , p_assignment_id in number
314 )
315 return number
316 is
317 --
318 l_balance number;
319 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
320 --
321 begin
322 --
323 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_YTD');
324 --
325 if l_defined_bal_id is not null then
326 --
327 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
328 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
329 ,P_TAX_UNIT_ID => null
330 ,P_JURISDICTION_CODE => null
331 ,P_SOURCE_ID => null
332 ,P_SOURCE_TEXT => null
333 ,P_TAX_GROUP => null
334 ,P_DATE_EARNED => null
335 );
336 --
337 else
338 l_balance := null;
339 end if;
340 --
341 return l_balance;
342 --
343 end calc_asg_ytd;
344 --
345 -----------------------------------------------------------------------------
346 --
347 --
348 -- calc_asg_ytd_action
349 --
350 -- this is the function for calculating assignment year to
351 -- date in asg action mode
352 --
353 -----------------------------------------------------------------------------
354 --
355 function calc_asg_ytd_action( p_assignment_action_id in number
356 , p_balance_type_id in number
357 , p_effective_date in date
358 )
359 return number
360 is
361 --
362 l_assignment_action_id number;
363 l_balance number;
364 l_assignment_id number;
365 l_effective_date date;
366 --
367 begin
368 --
369 l_assignment_id := get_correct_type(p_assignment_action_id);
370 if l_assignment_id is null then
371 --
372 -- the assignment action is not a payroll or quickpay type, so return null
373 --
374 l_balance := null;
375 else
376 --
377 l_balance := calc_asg_ytd ( p_assignment_action_id => p_assignment_action_id
378 , p_balance_type_id => p_balance_type_id
379 , p_effective_date => p_effective_date
380 , p_assignment_id => l_assignment_id
381 );
382 end if;
383 --
384 return l_balance;
385 --
386 end calc_asg_ytd_action;
387 --
388 -----------------------------------------------------------------------------
389 ---
390 --
391 -- calc_asg_ytd_date
392 --
393 -- this is the function for calculating assignment year to
394 -- date in date mode
395 --
396 -----------------------------------------------------------------------------
397 --
398 function calc_asg_ytd_date ( p_assignment_id in number
399 , p_balance_type_id in number
400 , p_effective_date in date
401 )
402 return number
403 is
404 --
405 l_assignment_action_id number;
406 l_balance number;
407 l_end_date date;
408 l_action_eff_date date;
409 l_start_dd_mm varchar2(9);
410 --
411 begin
412 --
413 l_start_dd_mm := g_tax_year_start;
414 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
415 if l_assignment_action_id is null then
416 l_balance := 0;
417 else
418 -- start expiry chk now
419 l_action_eff_date := get_latest_date(l_assignment_action_id);
420 --
421 -- is effective date (sess) later than the expiry of the financial year of the
422 -- effective date.
423 --
424 if p_effective_date >= get_expired_year_date(l_action_eff_date, l_start_dd_mm) then
425 l_balance := 0;
426 else
427 --
428 l_balance := calc_asg_ytd ( p_assignment_action_id => l_assignment_action_id
429 , p_balance_type_id => p_balance_type_id
430 , p_effective_date => p_effective_date
431 , p_assignment_id => p_assignment_id
432 );
433 end if;
434 end if;
435 --
436 return l_balance;
437 --
438 end calc_asg_ytd_date;
439 --
440 --------------------------------------------------------------------------------
441 --
442 -- calc_asg_mtd
443 -- calculate balances for assignment month to date
444 --
445 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
446 --------------------------------------------------------------------------------
447 --
448 -- will be set, otherwise session date is used.
449 --
450 function calc_asg_mtd ( p_assignment_action_id in number
451 , p_balance_type_id in number
452 , p_effective_date in date default null
453 , p_assignment_id in number
454 )
455 return number
456 is
457 --
458 l_balance number;
459 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
460 --
461 begin
462 --
463 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_MTD');
464 --
465 if l_defined_bal_id is not null then
466 --
467 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
468 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
469 ,P_TAX_UNIT_ID => null
470 ,P_JURISDICTION_CODE => null
471 ,P_SOURCE_ID => null
472 ,P_SOURCE_TEXT => null
473 ,P_TAX_GROUP => null
474 ,P_DATE_EARNED => null
475 );
476 --
477 else
478 l_balance := null;
479 end if;
480 --
481 return l_balance;
482 --
483 end calc_asg_mtd;
484 --
485 -----------------------------------------------------------------------------
486 --
487 --
488 -- calc_asg_mtd_action
489 --
490 -- this is the function for calculating assignment year to
491 -- date in asg action mode
492 --
493 -----------------------------------------------------------------------------
494 --
495 function calc_asg_mtd_action
496 (p_assignment_action_id in number
497 ,p_balance_type_id in number
498 ,p_effective_date in date default null)
499 return number
500 is
501 --
502 l_assignment_action_id number;
503 l_balance number;
504 l_assignment_id number;
505 l_effective_date date;
506 --
507 begin
508 --
509 l_assignment_id := get_correct_type(p_assignment_action_id);
510 if l_assignment_id is null then
511 --
512 -- the assignment action is not a payroll or quickpay type, so return null
513 --
514 l_balance := null;
515 else
516 --
517 l_balance := calc_asg_mtd ( p_assignment_action_id => p_assignment_action_id
518 , p_balance_type_id => p_balance_type_id
519 , p_effective_date => p_effective_date
520 , p_assignment_id => l_assignment_id
521 );
522 end if;
523 --
524 return l_balance;
525 --
526 end calc_asg_mtd_action;
527 --
528 -----------------------------------------------------------------------------
529 ---
530 --
531 -- calc_asg_mtd_date
532 --
533 -- this is the function for calculating assignment year to
534 -- date in date mode
535 --
536 -----------------------------------------------------------------------------
537 --
538 function calc_asg_mtd_date ( p_assignment_id in number
539 , p_balance_type_id in number
540 , p_effective_date in date
541 )
542 return number
543 is
544 --
545 l_assignment_action_id number;
546 l_balance number;
547 l_end_date date;
548 l_action_eff_date date;
549 l_start_dd_mm varchar2(9);
550 --
551 begin
552 --
553 l_start_dd_mm := g_tax_year_start;
554 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
555 if l_assignment_action_id is null then
556 l_balance := 0;
557 else
558 -- start expiry chk now
559 l_action_eff_date := get_latest_date(l_assignment_action_id);
560 --
561 -- is effective date (sess) later than the expiry of the financial year of the
562 -- effective date.
563 --
564 if p_effective_date >= get_expired_year_date(l_action_eff_date, l_start_dd_mm) then
565 l_balance := 0;
566 else
567 --
568 l_balance := calc_asg_mtd ( p_assignment_action_id => l_assignment_action_id
569 , p_balance_type_id => p_balance_type_id
570 , p_effective_date => p_effective_date
571 , p_assignment_id => p_assignment_id
572 );
573 end if;
574 end if;
575 --
576 return l_balance;
577 --
578 end calc_asg_mtd_date;
579 --
580 --------------------------------------------------------------------------------
581 --
582 -- calc_asg_qtd
583 -- calculate balances for assignment month to date
584 --
585 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
586 --------------------------------------------------------------------------------
587 --
588 -- will be set, otherwise session date is used.
589 --
590 function calc_asg_qtd ( p_assignment_action_id in number
591 , p_balance_type_id in number
592 , p_effective_date in date default null
593 , p_assignment_id in number
594 )
595 return number
596 is
597 --
598 l_balance number;
599 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
600 --
601 begin
602 --
603 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_QTD');
604 --
605 if l_defined_bal_id is not null then
606 --
607 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
608 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
609 ,P_TAX_UNIT_ID => null
610 ,P_JURISDICTION_CODE => null
611 ,P_SOURCE_ID => null
612 ,P_SOURCE_TEXT => null
613 ,P_TAX_GROUP => null
614 ,P_DATE_EARNED => null
615 );
616 --
617 else
618 l_balance := null;
619 end if;
620 --
621 return l_balance;
622 --
623 end calc_asg_qtd;
624 --
625 -----------------------------------------------------------------------------
626 --
627 --
628 -- calc_asg_qtd_action
629 --
630 -- this is the function for calculating assignment year to
631 -- date in asg action mode
632 --
633 -----------------------------------------------------------------------------
634 --
635 function calc_asg_qtd_action
636 (p_assignment_action_id in number
637 ,p_balance_type_id in number
638 ,p_effective_date in date default null)
639 return number
640 is
641 --
642 l_assignment_action_id number;
643 l_balance number;
644 l_assignment_id number;
645 l_effective_date date;
646 --
647 begin
648 --
649 l_assignment_id := get_correct_type(p_assignment_action_id);
650 if l_assignment_id is null then
651 --
652 -- the assignment action is not a payroll or quickpay type, so return null
653 --
654 l_balance := null;
655 else
656 --
657 l_balance := calc_asg_qtd ( p_assignment_action_id => p_assignment_action_id
658 , p_balance_type_id => p_balance_type_id
659 , p_effective_date => p_effective_date
660 , p_assignment_id => l_assignment_id
661 );
662 end if;
663 --
664 return l_balance;
665 --
666 end calc_asg_qtd_action;
667 --
668 -----------------------------------------------------------------------------
669 --
670 -- calc_asg_qtd_date
671 -- this is the function for calculating assignment year to
672 -- date in date mode
673 --
674 -----------------------------------------------------------------------------
675 --
676 function calc_asg_qtd_date
677 (p_assignment_id in number
678 ,p_balance_type_id in number
679 ,p_effective_date in date)
680 return number is
681 l_fn_name constant varchar2(61) := 'hr_aubal.calc_asg_qtd_date' ;
682 --
683 l_assignment_action_id number;
684 l_balance number;
685 l_end_date date;
686 l_action_eff_date date;
687 l_start_dd_mm varchar2(9);
688 --
689 begin
690 --
691 l_start_dd_mm := g_tax_year_start;
692 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
693 if l_assignment_action_id is null then
694 l_balance := 0;
695 else
696 -- start expiry chk now
697 l_action_eff_date := get_latest_date(l_assignment_action_id);
698 --
699 -- is effective date (sess) later than the expiry of the financial year of the
700 -- effective date.
701 --
702 if p_effective_date >= get_expired_year_date(l_action_eff_date, l_start_dd_mm) then
703 l_balance := 0;
704 else
705 --
706 l_balance := calc_asg_qtd
707 (p_assignment_action_id => l_assignment_action_id
708 ,p_balance_type_id => p_balance_type_id
709 ,p_effective_date => p_effective_date
710 ,p_assignment_id => p_assignment_id);
711 end if;
712 end if;
713 --
714 return l_balance;
715 --
716 end calc_asg_qtd_date;
717 --
718 --------------------------------------------------------------------------------
719 --
720 -- calc_asg_cal_ytd
721 -- calculate balances for assignment year to date
722 --
723 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
724 --------------------------------------------------------------------------------
725 --
726 function calc_asg_cal_ytd( p_assignment_action_id in number
727 , p_balance_type_id in number
728 , p_effective_date in date default null
729 , p_assignment_id in number
730 )
731 return number
732 is
733 --
734 l_balance number;
735 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
736 --
737 begin
738 --
739 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_CAL_YTD');
740 --
741 if l_defined_bal_id is not null then
742 --
743 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
744 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
745 ,P_TAX_UNIT_ID => null
746 ,P_JURISDICTION_CODE => null
747 ,P_SOURCE_ID => null
748 ,P_SOURCE_TEXT => null
749 ,P_TAX_GROUP => null
750 ,P_DATE_EARNED => null
751 );
752 --
753 else
754 l_balance := null;
755 end if;
756 --
757 return l_balance;
758 --
759 end calc_asg_cal_ytd;
760 --
761 -----------------------------------------------------------------------------
762 --
763 -- calc_asg_cal_ytd_action
764 -- this is the function for calculating assignment year to
765 -- date in asg action mode
766 --
767 -----------------------------------------------------------------------------
768 --
769 function calc_asg_cal_ytd_action
770 (p_assignment_action_id in number
771 ,p_balance_type_id in number
772 ,p_effective_date in date default null)
773 return number
774 is
775 --
776 l_assignment_action_id number;
777 l_balance number;
778 l_assignment_id number;
779 l_effective_date date;
780 --
781 begin
782 --
783 l_assignment_id := get_correct_type(p_assignment_action_id);
784 if l_assignment_id is null then
785 --
786 -- the assignment action is not a payroll or quickpay type, so return null
787 --
788 l_balance := null;
789 else
790 --
791 l_balance := calc_asg_cal_ytd ( p_assignment_action_id => p_assignment_action_id
792 , p_balance_type_id => p_balance_type_id
793 , p_effective_date => p_effective_date
794 , p_assignment_id => l_assignment_id
795 );
796 end if;
797 --
798 return l_balance;
799 --
800 end calc_asg_cal_ytd_action;
801 --
802 -----------------------------------------------------------------------------
803 --
804 -- calc_asg_cal_ytd_date
805 -- this is the function for calculating assignment year to
806 -- date in date mode
807 --
808 -----------------------------------------------------------------------------
809 --
810 function calc_asg_cal_ytd_date
811 (p_assignment_id in number
812 ,p_balance_type_id in number
813 ,p_effective_date in date)
814 return number
815 is
816 --
817 l_assignment_action_id number;
818 l_balance number;
819 l_end_date date;
820 l_action_eff_date date;
821 l_start_dd_mm varchar2(9);
822 --
823 begin
824 --
825 l_start_dd_mm := g_cal_year_start;
826 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
827 if l_assignment_action_id is null then
828 l_balance := 0;
829 else
830 -- start expiry chk now
831 l_action_eff_date := get_latest_date(l_assignment_action_id);
832 --
833 -- is effective date (sess) later than the expiry of the financial year of the
834 -- effective date.
835 --
836 if p_effective_date > get_expired_year_date(l_action_eff_date, l_start_dd_mm) then
837 l_balance := 0;
838 else
839 --
840 l_balance := calc_asg_cal_ytd ( p_assignment_action_id => l_assignment_action_id
841 , p_balance_type_id => p_balance_type_id
842 , p_effective_date => p_effective_date
843 , p_assignment_id => p_assignment_id
844 );
845 end if;
846 end if;
847 --
848 return l_balance;
849 --
850 end calc_asg_cal_ytd_date;
851 --
852 --------------------------------------------------------------------------------
853 --
854 -- calc_asg_fbt_ytd
855 -- calculate balances for FBT year to date
856 --
857 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
858 --------------------------------------------------------------------------------
859 --
860 function calc_asg_fbt_ytd( p_assignment_action_id in number
861 , p_balance_type_id in number
862 , p_effective_date in date default null
863 , p_assignment_id in number)
864 return number
865 is
866 --
867 l_balance number;
868 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
869 --
870 begin
871 --
872 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_FBT_YTD');
873 --
874 if l_defined_bal_id is not null then
875 --
876 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
877 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
878 ,P_TAX_UNIT_ID => null
879 ,P_JURISDICTION_CODE => null
880 ,P_SOURCE_ID => null
881 ,P_SOURCE_TEXT => null
882 ,P_TAX_GROUP => null
883 ,P_DATE_EARNED => null
884 );
885 --
886 else
887 l_balance := null;
888 end if;
889 --
890 return l_balance;
891 --
892 end calc_asg_fbt_ytd;
893 --
894 -----------------------------------------------------------------------------
895 --
896 --
897 -- calc_asg_fbt_ytd_action
898 --
899 -- this is the function for calculating assignment anniversary year to
900 -- date in asg action mode
901 --
902 -----------------------------------------------------------------------------
903 --
904 function calc_asg_fbt_ytd_action
905 (p_assignment_action_id in number
906 ,p_balance_type_id in number
907 ,p_effective_date in date default null)
908 return number is
909 l_fn_name constant varchar2(61) := 'hr_aubal.calc_asg_fbt_ytd_action' ;
910 --
911 l_assignment_action_id number;
912 l_balance number;
913 l_assignment_id number;
914 l_effective_date date;
915 --
916 begin
917 --
918 l_assignment_id := get_correct_type(p_assignment_action_id);
919 if l_assignment_id is null then
920 --
921 -- the assignment action is not a payroll or quickpay type, so return null
922 --
923 l_balance := null;
924 else
925 --
926 l_balance := calc_asg_fbt_ytd ( p_assignment_action_id => p_assignment_action_id
927 , p_balance_type_id => p_balance_type_id
928 , p_effective_date => p_effective_date
929 , p_assignment_id => l_assignment_id
930 );
931 end if;
932 --
933 return l_balance;
934 --
935 end calc_asg_fbt_ytd_action;
936 --
937 -----------------------------------------------------------------------------
938 ---
939 --
940 -- calc_asg_fbt_ytd_date
941 --
942 -- this is the function for calculating assignment anniversary year to
943 -- date in date mode
944 --
945 -----------------------------------------------------------------------------
946 --
947 function calc_asg_fbt_ytd_date
948 (p_assignment_id in number
949 ,p_balance_type_id in number
950 ,p_effective_date in date)
951 return number is
952
953 l_fn_name constant varchar2(61) := 'hr_aubal.calc_asg_fbt_ytd_date' ;
954 l_assignment_action_id number;
955 l_balance number;
956 l_end_date date;
957 l_action_eff_date date;
958
959 begin
960
961 hr_utility.trace('In: ' || l_fn_name) ;
962 hr_utility.trace(' p_balance_type_id:' || to_char(p_balance_type_id)) ;
963 hr_utility.trace(' p_effective_date:' || to_char(p_effective_date,'dd Mon yyyy')) ;
964 hr_utility.trace(' p_assignment_id:' || to_char(p_assignment_id)) ;
965
966 hr_utility.set_location(l_fn_name, 10) ;
967 l_assignment_action_id := get_latest_action_id
968 (p_assignment_id
969 ,p_effective_date);
970 hr_utility.trace(' l_assignment_action_id:' || to_char(l_assignment_action_id)) ;
971
972 if l_assignment_action_id is null
973 then
974 hr_utility.set_location(l_fn_name, 20) ;
975 l_balance := 0;
976 else
977 hr_utility.set_location(l_fn_name, 30) ;
978 -- start expiry chk now
979 l_action_eff_date := get_latest_date(l_assignment_action_id);
980 hr_utility.trace(' l_action_eff_date:' || to_char(l_action_eff_date, 'dd Mon yyyy')) ;
981
982 -- is effective date (sess) later than the expiry of the FBT year of the
983 -- effective date.
984
985 if p_effective_date >= get_expired_year_date(l_action_eff_date, g_fbt_year_start)
986 then
987 hr_utility.set_location(l_fn_name, 40) ;
988 l_balance := 0;
989 else
990 hr_utility.set_location(l_fn_name, 50) ;
991 l_balance := calc_asg_fbt_ytd
992 (p_assignment_action_id => l_assignment_action_id
993 ,p_balance_type_id => p_balance_type_id
994 ,p_effective_date => p_effective_date
995 ,p_assignment_id => p_assignment_id);
996 end if;
997 end if;
998
999 hr_utility.trace(l_fn_name || ' return:' || to_char(l_balance)) ;
1000 hr_utility.trace('Out: ' || l_fn_name) ;
1001 return l_balance;
1002
1003 end calc_asg_fbt_ytd_date;
1004 --
1005 --------------------------------------------------------------------------------
1006 --
1007 -- calc_asg_fy_ytd
1008 -- calculate balances for assignment fiscal year to date
1009 --
1010 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1011 --------------------------------------------------------------------------------
1012 --
1013 function calc_asg_fy_ytd( p_assignment_action_id in number
1014 , p_balance_type_id in number
1015 , p_effective_date in date default null
1016 , p_assignment_id in number
1017 )
1018 return number
1019 is
1020 --
1021 l_balance number;
1022 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1023 --
1024 begin
1025 --
1026 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_FY_YTD');
1027 --
1028 if l_defined_bal_id is not null then
1029 --
1030 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1031 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1032 ,P_TAX_UNIT_ID => null
1033 ,P_JURISDICTION_CODE => null
1034 ,P_SOURCE_ID => null
1035 ,P_SOURCE_TEXT => null
1036 ,P_TAX_GROUP => null
1037 ,P_DATE_EARNED => null
1038 );
1039 --
1040 else
1041 l_balance := null;
1042 end if;
1043 --
1044 return l_balance;
1045 --
1046 end calc_asg_fy_ytd;
1047
1048 --
1049 -----------------------------------------------------------------------------
1050 --
1051 --
1052 -- calc_asg_fy_ytd_action
1053 --
1054 -- this is the function for calculating assignment fiscal year to
1055 -- date in asg action mode
1056 --
1057 -----------------------------------------------------------------------------
1058 --
1059 function calc_asg_fy_ytd_action ( p_assignment_action_id in number
1060 , p_balance_type_id in number
1061 , p_effective_date in date
1062 )
1063 return number
1064 is
1065 --
1066 l_assignment_action_id number;
1067 l_balance number;
1068 l_assignment_id number;
1069 l_effective_date date;
1070 --
1071 begin
1072 --
1073 l_assignment_id := get_correct_type(p_assignment_action_id);
1074 if l_assignment_id is null then
1075 --
1076 -- the assignment action is not a payroll or quickpay type, so return null
1077 --
1078 l_balance := null;
1079 else
1080 --
1081 l_balance := calc_asg_fy_ytd( p_assignment_action_id => p_assignment_action_id
1082 , p_balance_type_id => p_balance_type_id
1083 , p_effective_date => p_effective_date
1084 , p_assignment_id => l_assignment_id
1085 );
1086 end if;
1087 --
1088 return l_balance;
1089 --
1090 end calc_asg_fy_ytd_action;
1091 --
1092 -----------------------------------------------------------------------------
1093 ---
1094 --
1095 -- calc_asg_fy_ytd_date
1096 --
1097 -- this is the function for calculating assignment fiscal year to
1098 -- date in date mode
1099 --
1100 -----------------------------------------------------------------------------
1101 --
1102 function calc_asg_fy_ytd_date ( p_assignment_id in number
1103 , p_balance_type_id in number
1104 , p_effective_date in date
1105 )
1106 return number
1107 is
1108 --
1109 cursor csr_business_group(p_assignment_id number) is
1110 select business_group_id
1111 from per_assignments_f
1112 where assignment_id = p_assignment_id;
1113 --
1114 l_assignment_action_id number;
1115 l_balance number;
1116 l_end_date date;
1117 l_action_eff_date date;
1118 l_start_dd_mm varchar2(9);
1119 l_business_group_id per_assignments_f.business_group_id%type;
1120 --
1121 begin
1122 --
1123 l_assignment_action_id := get_latest_action_id( p_assignment_id
1124 , p_effective_date
1125 );
1126 if l_assignment_action_id is null then
1127 l_balance := 0;
1128 else
1129 -- start expiry chk now
1130 l_action_eff_date := get_latest_date(l_assignment_action_id);
1131 --
1132 -- is effective date (sess) later than the expiry of the fiscal year of the
1133 -- effective date.
1134 --
1135 open csr_business_group(p_assignment_id);
1136 fetch csr_business_group into l_business_group_id;
1137 close csr_business_group;
1138 l_start_dd_mm := to_char(hr_AU_routes.get_fiscal_date(l_business_group_id),'dd-mm-');
1139 --
1140 if p_effective_date >= get_expired_year_date(l_action_eff_date, l_start_dd_mm) then
1141 l_balance := 0;
1142 else
1143 --
1144 l_balance := calc_asg_fy_ytd ( p_assignment_action_id => l_assignment_action_id
1145 , p_balance_type_id => p_balance_type_id
1146 , p_effective_date => p_effective_date
1147 , p_assignment_id => p_assignment_id
1148 );
1149 end if;
1150 end if;
1151 --
1152 return l_balance;
1153 --
1154 end calc_asg_fy_ytd_date;
1155 --
1156 --------------------------------------------------------------------------------
1157 --
1158 -- calc_asg_fy_qtd
1159 -- calculate balances for assignment fiscal quarter to date
1160 --
1161 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1162 --------------------------------------------------------------------------------
1163 --
1164 function calc_asg_fy_qtd( p_assignment_action_id in number
1165 , p_balance_type_id in number
1166 , p_effective_date in date default null
1167 , p_assignment_id in number
1168 )
1169 return number
1170 is
1171 --
1172 l_balance number;
1173 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1174 --
1175 begin
1176 --
1177 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_FY_QTD');
1178 --
1179 if l_defined_bal_id is not null then
1180 --
1181 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1182 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1183 ,P_TAX_UNIT_ID => null
1184 ,P_JURISDICTION_CODE => null
1185 ,P_SOURCE_ID => null
1186 ,P_SOURCE_TEXT => null
1187 ,P_TAX_GROUP => null
1188 ,P_DATE_EARNED => null
1189 );
1190 --
1191 else
1192 l_balance := null;
1193 end if;
1194 --
1195 return l_balance;
1196 --
1197 end calc_asg_fy_qtd;
1198 -----------------------------------------------------------------------------
1199 --
1200 -- calc_asg_fy_qtd_action
1201 -- this is the function for calculating assignment fiscal quarter to
1202 -- to date in asg action mode
1203 --
1204 -----------------------------------------------------------------------------
1205 --
1206 function calc_asg_fy_qtd_action ( p_assignment_action_id in number
1207 , p_balance_type_id in number
1208 , p_effective_date in date
1209 )
1210 return number
1211 is
1212 --
1213 l_assignment_action_id number;
1214 l_balance number;
1215 l_assignment_id number;
1216 l_effective_date date;
1217 --
1218 begin
1219 --
1220 l_assignment_id := get_correct_type(p_assignment_action_id);
1221 if l_assignment_id is null then
1222 --
1223 -- the assignment action is not a payroll or quickpay type, so return null
1224 --
1225 l_balance := null;
1226 else
1227 --
1228 l_balance := calc_asg_fy_qtd( p_assignment_action_id => p_assignment_action_id
1229 , p_balance_type_id => p_balance_type_id
1230 , p_effective_date => p_effective_date
1231 , p_assignment_id => l_assignment_id
1232 );
1233 end if;
1234 --
1235 return l_balance;
1236 --
1237 end calc_asg_fy_qtd_action;
1238 --
1239 -----------------------------------------------------------------------------
1240 --
1241 -- calc_asg_fy_qtd_date
1242 -- this is the function for calculating assignment fiscal quarter
1243 -- to date in date mode
1244 --
1245 -----------------------------------------------------------------------------
1246 --
1247 function calc_asg_fy_qtd_date ( p_assignment_id in number
1248 , p_balance_type_id in number
1249 , p_effective_date in date
1250 )
1251 return number
1252 is
1253 --
1254 cursor csr_business_group(p_assignment_id number) is
1255 select business_group_id
1256 from per_assignments_f
1257 where assignment_id = p_assignment_id;
1258 --
1259 l_assignment_action_id number;
1260 l_balance number;
1261 l_end_date date;
1262 l_action_eff_date date;
1263 l_business_group_id per_assignments_f.business_group_id%type;
1264 --
1265 begin
1266 --
1267 l_assignment_action_id := get_latest_action_id( p_assignment_id
1268 , p_effective_date
1269 );
1270 if l_assignment_action_id is null then
1271 l_balance := 0;
1272 else
1273 -- start expiry chk now
1274 l_action_eff_date := get_latest_date(l_assignment_action_id);
1275 --
1276 open csr_business_group(p_assignment_id);
1277 fetch csr_business_group into l_business_group_id;
1278 close csr_business_group;
1279 --
1280 -- is effective date (sess) later than the expiry of the fiscal quarter ( the start of the
1281 -- next fiscal quarter) of the effective date.
1282 --
1283 if p_effective_date > hr_AU_routes.fiscal_span_start(add_months(l_action_eff_date,4),4,l_business_group_id) then
1284 l_balance := 0;
1285 else
1286 --
1287 l_balance := calc_asg_fy_qtd ( p_assignment_action_id => l_assignment_action_id
1288 , p_balance_type_id => p_balance_type_id
1289 , p_effective_date => p_effective_date
1290 , p_assignment_id => p_assignment_id
1291 );
1292 end if;
1293 end if;
1294 --
1295 return l_balance;
1296 --
1297 end calc_asg_fy_qtd_date;
1298 --
1299 -----------------------------------------------------------------------------
1300 --
1301 -- calc_asg_ptd
1302 -- calculate balances for assignment process period to date
1303 --
1304 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1305 -----------------------------------------------------------------------------
1306 --
1307 function calc_asg_ptd ( p_assignment_action_id in number
1308 , p_balance_type_id in number
1309 , p_effective_date in date default null
1310 , p_assignment_id in number
1311 )
1312 --
1313 return number
1314 is
1315 --
1316 l_balance number;
1317 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1318 --
1319 begin
1320 --
1321 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_PTD');
1322 --
1323 if l_defined_bal_id is not null then
1324 --
1325 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1326 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1327 ,P_TAX_UNIT_ID => null
1328 ,P_JURISDICTION_CODE => null
1329 ,P_SOURCE_ID => null
1330 ,P_SOURCE_TEXT => null
1331 ,P_TAX_GROUP => null
1332 ,P_DATE_EARNED => null
1333 );
1334 --
1335 else
1336 l_balance := null;
1337 end if;
1338 --
1339 return l_balance;
1340 --
1341 end calc_asg_ptd;
1342 --
1343 -----------------------------------------------------------------------------
1344 --
1345 -- calc_asg_ptd_action
1346 --
1347 -- this is the function for calculating assignment
1348 -- proc. period to date in assignment action mode
1349 --
1350 -----------------------------------------------------------------------------
1351 --
1352 function calc_asg_ptd_action( p_assignment_action_id in number
1353 , p_balance_type_id in number
1354 , p_effective_date in date
1355 )
1356 return number
1357 is
1358 --
1359 l_assignment_action_id number;
1360 l_balance number;
1361 l_assignment_id number;
1362 l_effective_date date;
1363 --
1364 begin
1365 --
1366 l_assignment_id := get_correct_type ( p_assignment_action_id );
1367 if l_assignment_id is null then
1368 --
1369 -- the assignment action is not a payroll or quickpay type, so return null
1370 --
1371 l_balance := null;
1372 else
1373 --
1374 l_balance := calc_asg_ptd ( p_assignment_action_id => p_assignment_action_id
1375 , p_balance_type_id => p_balance_type_id
1376 , p_effective_date => p_effective_date
1377 , p_assignment_id => l_assignment_id
1378 );
1379 end if;
1380 --
1381 return l_balance;
1382 end calc_asg_ptd_action;
1383 --
1384 -----------------------------------------------------------------------------
1385 --
1386 --
1387 -- calc_asg_ptd_date
1388 --
1389 -- this is the function for calculating assignment processing
1390 -- period to date in date mode
1391 --
1392 -----------------------------------------------------------------------------
1393 --
1394 function calc_asg_ptd_date ( p_assignment_id in number
1395 , p_balance_type_id in number
1396 , p_effective_date in date
1397 )
1398 return number
1399 is
1400 --
1401 l_assignment_action_id number;
1402 l_balance number;
1403 l_end_date date;
1404 --
1405 -- has the processing time period expired
1406 --
1407 cursor expired_time_period (c_assignment_action_id number) is
1408 select ptp.end_date
1409 from per_time_periods ptp
1410 , pay_payroll_actions ppa
1411 , pay_assignment_actions paa
1412 where paa.assignment_action_id = c_assignment_action_id
1413 and paa.payroll_action_id = ppa.payroll_action_id
1414 and ppa.time_period_id = ptp.time_period_id;
1415 --
1416 begin
1417 --
1418 l_assignment_action_id := get_latest_action_id( p_assignment_id
1419 , p_effective_date
1420 );
1421 if l_assignment_action_id is null then
1422 l_balance := 0;
1423 else
1424 open expired_time_period(l_assignment_action_id);
1425 fetch expired_time_period into l_end_date;
1426 close expired_time_period;
1427 --
1428 if l_end_date < p_effective_date then
1429 l_balance := 0;
1430 else
1431 l_balance := calc_asg_ptd ( p_assignment_action_id => l_assignment_action_id
1432 , p_balance_type_id => p_balance_type_id
1433 , p_effective_date => p_effective_date
1434 , p_assignment_id => p_assignment_id
1435 );
1436 end if;
1437 end if;
1438 --
1439 return l_balance;
1440 end calc_asg_ptd_date;
1441 --
1442 -----------------------------------------------------------------------------
1443 --
1444 --
1445 -- calc_asg_td
1446 --
1447 -- calculate balances for assignment to date
1448 --
1449 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1450 -----------------------------------------------------------------------------
1451 --
1452 -- sum of all run items since inception.
1453 --
1454 function calc_asg_td( p_assignment_action_id in number
1455 , p_balance_type_id in number
1456 , p_effective_date in date default null
1457 , p_assignment_id in number
1458 )
1459 return number
1460 is
1461 --
1462 l_balance number;
1463 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1464 --
1465 begin
1466 --
1467 l_defined_bal_id := dimension_relevant(p_balance_type_id,'_ASG_TD');
1468 --
1469 if l_defined_bal_id is not null then
1470 --
1471 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1472 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1473 ,P_TAX_UNIT_ID => null
1474 ,P_JURISDICTION_CODE => null
1475 ,P_SOURCE_ID => null
1476 ,P_SOURCE_TEXT => null
1477 ,P_TAX_GROUP => null
1478 ,P_DATE_EARNED => null
1479 );
1480 --
1481 else
1482 l_balance := null;
1483 end if;
1484 --
1485 return l_balance;
1486 --
1487 end calc_asg_td;
1488
1489 --
1490 -----------------------------------------------------------------------------
1491 --
1492 -- calc_asg_td_action
1493 -- this is the function for calculating assignment
1494 -- to date in assignment action mode
1495 --
1496 -----------------------------------------------------------------------------
1497 --
1498 function calc_asg_td_action ( p_assignment_action_id in number
1499 , p_balance_type_id in number
1500 , p_effective_date in date
1501 )
1502 return number
1503 is
1504 --
1505 l_assignment_action_id number;
1506 l_balance number;
1507 l_assignment_id number;
1508 l_effective_date date;
1509 --
1510 begin
1511 --
1512 l_assignment_id := get_correct_type(p_assignment_action_id);
1513 if l_assignment_id is null then
1514 --
1515 -- the assignment action is not a payroll or quickpay type, so return null
1516 --
1517 l_balance := null;
1518 else
1519 --
1520 l_balance := calc_asg_td( p_assignment_id => l_assignment_id
1521 , p_assignment_action_id => p_assignment_action_id
1522 , p_balance_type_id => p_balance_type_id
1523 , p_effective_date => p_effective_date
1524 );
1525 end if;
1526 --
1527 return l_balance;
1528 --
1529 end calc_asg_td_action;
1530 --
1531 -----------------------------------------------------------------------------
1532 --
1533 -- calc_asg_td_date
1534 -- this is the function for calculating assignment to
1535 -- date in date mode
1536 --
1537 -----------------------------------------------------------------------------
1538 --
1539 function calc_asg_td_date ( p_assignment_id in number
1540 , p_balance_type_id in number
1541 , p_effective_date in date
1542 )
1543 return number
1544 is
1545 --
1546 l_assignment_action_id number;
1547 l_balance number;
1548 l_end_date date;
1549 --
1550 begin
1551 --
1552 l_assignment_action_id := get_latest_action_id( p_assignment_id
1553 , p_effective_date
1554 );
1555 if l_assignment_action_id is null then
1556 l_balance := 0;
1557 else
1558 l_balance := calc_asg_td( p_assignment_id => p_assignment_id
1559 , p_assignment_action_id => l_assignment_action_id
1560 , p_balance_type_id => p_balance_type_id
1561 , p_effective_date => p_effective_date
1562 );
1563 end if;
1564 --
1565 return l_balance;
1566 --
1567 end calc_asg_td_date;
1568 --
1569 -----------------------------------------------------------------------------
1570 ---
1571 --
1572 -- calc_asg_run
1573 -- calculate balances for assignment run
1574 --
1575 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1576 -----------------------------------------------------------------------------
1577 --
1578 function calc_asg_run ( p_assignment_action_id in number
1579 , p_balance_type_id in number
1580 , p_effective_date in date default null
1581 , p_assignment_id in number
1582 )
1583 return number
1584 is
1585 --
1586 l_balance number;
1587 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1588 --
1589 begin
1590 --
1591 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_RUN');
1592 if l_defined_bal_id is not null then
1593 --
1594 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1595 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1596 ,P_TAX_UNIT_ID => null
1597 ,P_JURISDICTION_CODE => null
1598 ,P_SOURCE_ID => null
1599 ,P_SOURCE_TEXT => null
1600 ,P_TAX_GROUP => null
1601 ,P_DATE_EARNED => null
1602 );
1603 --
1604 else
1605 l_balance := null;
1606 end if;
1607 --
1608 return l_balance;
1609 --
1610 end calc_asg_run;
1611 --
1612 -----------------------------------------------------------------------------
1613 --
1614 -- calc_asg_run_action
1615 -- this is the function for calculating assignment
1616 -- runs in assignment action mode
1617 --
1618 -----------------------------------------------------------------------------
1619 --
1620 function calc_asg_run_action( p_assignment_action_id in number
1621 , p_balance_type_id in number
1622 , p_effective_date in date
1623 )
1624 return number
1625 is
1626 --
1627 l_assignment_action_id number;
1628 l_balance number;
1629 l_assignment_id number;
1630 l_effective_date date;
1631 --
1632 begin
1633 --
1634 l_assignment_id := get_correct_type(p_assignment_action_id);
1635 if l_assignment_id is null then
1636 --
1637 -- the assignment action is not a payroll or quickpay type, so return null
1638 --
1639 l_balance := null;
1640 else
1641 --
1642 l_balance := calc_asg_run ( p_assignment_action_id => p_assignment_action_id
1643 , p_balance_type_id => p_balance_type_id
1644 , p_effective_date => p_effective_date
1645 , p_assignment_id => l_assignment_id
1646 );
1647 end if;
1648 --
1649 return l_balance;
1650 end calc_asg_run_action;
1651 --
1652 -----------------------------------------------------------------------------
1653 --
1654 -- calc_asg_run_date
1655 -- this is the function for calculating assignment run in
1656 -- date mode
1657 --
1658 -----------------------------------------------------------------------------
1659 --
1660 function calc_asg_run_date ( p_assignment_id in number
1661 , p_balance_type_id in number
1662 , p_effective_date in date
1663 )
1664 return number
1665 is
1666 --
1667 l_assignment_action_id number;
1668 l_balance number;
1669 l_end_date date;
1670 --
1671 cursor expired_time_period ( c_assignment_action_id in number ) is
1672 select ptp.end_date
1673 from per_time_periods ptp
1674 , pay_payroll_actions ppa
1675 , pay_assignment_actions paa
1676 where paa.assignment_action_id = c_assignment_action_id
1677 and paa.payroll_action_id = ppa.payroll_action_id
1678 and ppa.time_period_id = ptp.time_period_id;
1679 --
1680 begin
1681 --
1682 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
1683 if l_assignment_action_id is null then
1684 l_balance := 0;
1685 else
1686 open expired_time_period(l_assignment_action_id);
1687 fetch expired_time_period into l_end_date;
1688 close expired_time_period;
1689 --
1690 if l_end_date < p_effective_date then
1691 l_balance := 0;
1692 else
1693 l_balance := calc_asg_run ( p_assignment_action_id => l_assignment_action_id
1694 , p_balance_type_id => p_balance_type_id
1695 , p_effective_date => p_effective_date
1696 , p_assignment_id => p_assignment_id
1697 );
1698 end if;
1699 end if;
1700 --
1701 return l_balance;
1702 end calc_asg_run_date;
1703 --
1704 -----------------------------------------------------------------------------
1705 --
1706 -- calc_payment
1707 -- calculate balances for payments
1708 --
1709 -- Bug 3010965 - Changed the function to use pay_balance_pkg.get_value
1710 -----------------------------------------------------------------------------
1711 --
1712 function calc_payment ( p_assignment_action_id in number
1713 , p_balance_type_id in number
1714 , p_effective_date in date default null
1715 , p_assignment_id in number
1716 )
1717 return number
1718 is
1719 --
1720 l_balance number;
1721 l_defined_bal_id pay_defined_balances.defined_balance_id%TYPE;
1722 --
1723 begin
1724 --
1725 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_PAYMENTS');
1726 --
1727 if l_defined_bal_id is not null then
1728 --
1729 l_balance := pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID => l_defined_bal_id
1730 ,P_ASSIGNMENT_ACTION_ID => p_assignment_action_id
1731 ,P_TAX_UNIT_ID => null
1732 ,P_JURISDICTION_CODE => null
1733 ,P_SOURCE_ID => null
1734 ,P_SOURCE_TEXT => null
1735 ,P_TAX_GROUP => null
1736 ,P_DATE_EARNED => null
1737 );
1738 --
1739 else
1740 l_balance := null;
1741 end if;
1742 --
1743 return l_balance;
1744 --
1745 end calc_payment;
1746 --
1747 -----------------------------------------------------------------------------
1748 --
1749 -- calc_payment_action
1750 -- this is the function for calculating payments
1751 -- in assignment action mode
1752 --
1753 -----------------------------------------------------------------------------
1754 --
1755 function calc_payment_action( p_assignment_action_id in number
1756 , p_balance_type_id in number
1757 , p_effective_date in date
1758 )
1759 return number
1760 is
1761 --
1762 l_assignment_action_id number;
1763 l_balance number;
1764 l_assignment_id number;
1765 l_effective_date date;
1766 --
1767 begin
1768 --
1769 l_assignment_id := get_correct_type(p_assignment_action_id);
1770 if l_assignment_id is null then
1771 --
1772 -- the assignment action is not a payroll or quickpay type, so return null
1773 --
1774 l_balance := null;
1775 else
1776 --
1777 l_balance := calc_payment ( p_assignment_action_id => p_assignment_action_id
1778 , p_balance_type_id => p_balance_type_id
1779 , p_effective_date => p_effective_date
1780 , p_assignment_id => l_assignment_id
1781 );
1782 end if;
1783 --
1784 return l_balance;
1785 end calc_payment_action;
1786 --
1787 -----------------------------------------------------------------------------
1788 --
1789 -- calc_payment_date
1790 -- this is the function for calculating payments in
1791 -- date mode
1792 --
1793 -----------------------------------------------------------------------------
1794 --
1795 function calc_payment_date ( p_assignment_id in number
1796 , p_balance_type_id in number
1797 , p_effective_date in date
1798 )
1799 return number
1800 is
1801 --
1802 l_assignment_action_id number;
1803 l_balance number;
1804 l_end_date date;
1805 --
1806 cursor expired_time_period( c_assignment_action_id in number ) is
1807 select ptp.end_date
1808 from per_time_periods ptp
1809 , pay_payroll_actions ppa
1810 , pay_assignment_actions paa
1811 where paa.assignment_action_id = c_assignment_action_id
1812 and paa.payroll_action_id = ppa.payroll_action_id
1813 and ppa.time_period_id = ptp.time_period_id;
1814 --
1815 begin
1816 --
1817 l_assignment_action_id := get_latest_action_id( p_assignment_id, p_effective_date );
1818 if l_assignment_action_id is null then
1819 l_balance := 0;
1820 else
1821 open expired_time_period(l_assignment_action_id);
1822 fetch expired_time_period into l_end_date;
1823 close expired_time_period;
1824 --
1825 if l_end_date < p_effective_date then
1826 l_balance := 0;
1827 else
1828 l_balance := calc_payment ( p_assignment_action_id => l_assignment_action_id
1829 , p_balance_type_id => p_balance_type_id
1830 , p_effective_date => p_effective_date
1831 , p_assignment_id => p_assignment_id
1832 );
1833 end if;
1834 end if;
1835 --
1836 return l_balance;
1837 end calc_payment_date;
1838 --
1839 ------------------------------------------------------------------------------
1840 --
1841 end hr_aubal;