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