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