1 PACKAGE BODY py_za_bal AS
2 /* $Header: pyzabal1.pkb 120.3 2005/12/19 04:15:59 amahanty noship $ */
3 --------------------------------------------------------------------------------
4
5 --------------------------------------------------------------------------------
6 --------------------------------------------------------------------------------
7 -- return the start of the span (year/quarter/week)
8 FUNCTION span_start(
9 p_input_date DATE,
10 p_frequency NUMBER DEFAULT 1,
11 p_start_dd_mm VARCHAR2 DEFAULT '06-04-')
12 RETURN DATE
13 IS
14 l_year NUMBER(4);
15 l_start DATE;
16 --
17 BEGIN
18 l_year := TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
19 --
20 IF p_input_date >= TO_DATE(p_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY') THEN
21 l_start := TO_DATE(p_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
22 ELSE
23 l_start := TO_DATE(p_start_dd_mm||TO_CHAR(l_year -1),'DD-MM-YYYY');
24 END IF;
25 -- cater for weekly based frequency based on 52 per annum
26 IF p_frequency IN (52,26,13) THEN -- [
27 l_start := p_input_date - MOD(p_input_date - l_start,7 * 52/p_frequency);
28 ELSE
29 -- cater for monthly based frequency based on 12 per annum
30 l_start := ADD_MONTHS(l_start, (12/p_frequency) * TRUNC(MONTHS_BETWEEN(
31 p_input_date,l_start)/(12/p_frequency)));
32 END IF;
33 --
34 RETURN l_start;
35 END span_start;
36 --------------------------------------------------------------------------------
37 --
38 -- GET OWNING BALANCE (private)
39 --
40 --
41 --------------------------------------------------------------------------------
42 -- This procedure checks whether there is a value in the lb table for an
43 -- assignment and a defined balance id.
44 --
45 PROCEDURE get_owning_balance(p_assignment_id IN NUMBER,
46 p_defined_balance_id IN NUMBER,
47 p_assignment_action_id OUT NOCOPY NUMBER,
48 p_value OUT NOCOPY NUMBER) IS
49 --
50 l_value NUMBER;
51 l_assignment_action_id NUMBER;
52 --
53 cursor c2 (c_assignment_id IN NUMBER,
54 c_defined_bal_id IN NUMBER) is
55 select value,
56 assignment_action_id
57 from pay_assignment_latest_balances
58 where assignment_id = c_assignment_id
59 and defined_balance_id = c_defined_bal_id;
60 --
61 BEGIN
62 --
63 open c2(p_assignment_id, p_defined_balance_id);
64 fetch c2 into l_value, l_assignment_action_id;
65 close c2;
66 --
67 p_value := l_value;
68 p_assignment_action_id := l_assignment_action_id;
69 exception
70 when others then
71 p_value := null;
72 p_assignment_action_id := null;
73
74 END get_owning_balance;
75 --
76 --------------------------------------------------------------------------------
77 --
78 -- SEQUENCE (private)
79 --
80 --
81 --------------------------------------------------------------------------------
82 --
83 FUNCTION sequence(p_assignment_action_id IN NUMBER)
84 --
85 RETURN NUMBER IS
86 --
87 l_action_sequence NUMBER;
88 --
89 cursor get_sequence(c_assignment_action_id IN NUMBER) is
90 select action_sequence from
91 pay_assignment_actions
92 where assignment_action_id = c_assignment_action_id;
93 --
94 BEGIN
95 --
96 open get_sequence(p_assignment_action_id);
97 fetch get_sequence into l_action_sequence;
98 close get_sequence;
99 --
100 RETURN l_action_sequence;
101 --
102 END sequence;
103 --------------------------------------------------------------------------------
104 --
105 -- GET LATEST BALANCE (private)
106 --
107 --
108 --------------------------------------------------------------------------------
109 --
110 -- Retrieve the latest balance given an assignment action and def. balance
111 --
112 FUNCTION get_latest_balance (p_assignment_action_id IN NUMBER,
113 p_defined_balance_id IN NUMBER)
114 --
115 RETURN NUMBER IS
116 --
117 l_value NUMBER;
118 --
119 cursor c1 (c_asg_action_id IN NUMBER,
120 c_defined_balance_id IN NUMBER) is
121 SELECT value
122 from pay_assignment_latest_balances
123 Where assignment_action_id = c_asg_action_id
124 and defined_balance_id = c_defined_balance_id;
125 --
126 BEGIN
127 --
128 open c1(p_assignment_action_id
129 ,p_defined_balance_id);
130 fetch c1 into l_value;
131 close c1;
132 --
133 RETURN l_value;
134 --
135 END get_latest_balance;
136 --
137 --------------------------------------------------------------------------------
138 --
139 -- GET CORRECT TYPE (private)
140 --
141 --
142 --------------------------------------------------------------------------------
143 --
144 -- This is a validation check to ensure that the assignment action is of the
145 -- correct type. This is called from all assignment action mode functions.
146 -- The assignment id is returned (and not assignment action id) because
147 -- this is to be used in the expired latest balance check. This function thus
148 -- has two uses - to validate the assignment action, and give the corresponding
149 -- assignmment id for that action.
150 --
151 FUNCTION get_correct_type(p_assignment_action_id IN NUMBER)
152 --
153 RETURN NUMBER IS
154 --
155 l_assignment_id NUMBER;
156 --
157 cursor get_corr_type (c_assignment_action_id IN NUMBER) is
158 SELECT assignment_id
159 FROM pay_assignment_actions paa,
160 pay_payroll_actions ppa
161 WHERE
162 paa.assignment_action_id = c_assignment_action_id
163 AND ppa.payroll_action_id = paa.payroll_action_id
164 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
165 --
166 BEGIN
167 --
168 open get_corr_type(p_assignment_action_id);
169 fetch get_corr_type into l_assignment_id;
170 close get_corr_type;
171 --
172 RETURN l_assignment_id;
173 --
174 END get_correct_type;
175 --------------------------------------------------------------------------------
176 --
177 -- GET LATEST ACTION ID (private)
178 --
179 --
180 --------------------------------------------------------------------------------
181 -- This function returns the latest assignment action ID given an assignment
182 -- and effective date. This is called from all Date Mode functions.
183 --
184 FUNCTION get_latest_action_id (p_assignment_id IN NUMBER,
185 p_effective_date IN DATE)
186 RETURN NUMBER IS
187 --
188 l_assignment_action_id NUMBER;
189 --
190 /* Start Bug 3229579 */
191 /*
192 cursor get_latest_id (c_assignment_id IN NUMBER,
193 c_effective_date IN DATE) is
194 SELECT
195 to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
196 FROM pay_assignment_actions paa,
197 pay_payroll_actions ppa
198 WHERE
199 paa.assignment_id = c_assignment_id
200 AND ppa.payroll_action_id = paa.payroll_action_id
201 AND ppa.effective_date <= c_effective_date
202 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'); */
203
204
205 cursor get_latest_id (c_assignment_id IN NUMBER,
206 c_effective_date IN DATE) is
207 SELECT /*+ ORDERED
208 USE_NL(PAA PPA)
209 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
210 INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
211 TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
212 paa.assignment_action_id),16))
213 FROM pay_assignment_actions paa,
214 pay_payroll_actions ppa
215 WHERE paa.assignment_id = c_assignment_id
216 AND ppa.payroll_action_id = paa.payroll_action_id
217 AND ppa.effective_date <= c_effective_date
218 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');
219
220 /* End Bug 3229579 */
221 --
222 BEGIN
223 --
224 open get_latest_id(p_assignment_id, p_effective_date);
225 fetch get_latest_id into l_assignment_action_id;
226 close get_latest_id;
227 --
228 RETURN l_assignment_action_id;
229 --
230 END get_latest_action_id;
231 --
232 --------------------------------------------------------------------------------
233 --
234 -- CHECK EXPIRED ACTION (private)
235 --
236 --
237 --------------------------------------------------------------------------------
238 --
239 -- This function checks to see whether an expired_value can be used
240 -- from the pay_assignment_latest_balances table.
241 --
242 FUNCTION check_expired_action(
243 p_defined_balance_id IN NUMBER,
244 p_assignment_id IN NUMBER,
245 p_assignment_action_id IN NUMBER)
246 RETURN NUMBER IS
247 --
248 l_value NUMBER;
249 --
250 cursor expired_val (c_defined_balance_id IN NUMBER,
251 c_assignment_action_id IN NUMBER,
252 c_assignment_id IN NUMBER) is
253 SELECT expired_value
254 FROM pay_assignment_latest_balances
255 WHERE expired_assignment_action_id = c_assignment_action_id
256 AND assignment_id = c_assignment_id
257 AND defined_balance_id = c_defined_balance_id;
258 --
259 BEGIN
260 --
261 open expired_val(p_defined_balance_id,
262 p_assignment_action_id,
263 p_assignment_id);
264 fetch expired_val into l_value;
265 close expired_val;
266 --
267 --
268 RETURN l_value;
269 --
270 END check_expired_action;
271 --------------------------------------------------------------------------------
272 --
273 -- DIMENSION RELEVANT (private)
274 --
275 --
276 --------------------------------------------------------------------------------
277 --
278 -- This function checks that a value is required for the dimension
279 -- for this particular balance type. If so, the defined balance is returned.
280 --
281 FUNCTION dimension_relevant(p_balance_type_id IN NUMBER,
282 p_database_item_suffix IN VARCHAR2)
283 RETURN NUMBER IS
284 --
285 l_defined_balance_id NUMBER;
286 --
287 cursor relevant(c_balance_type_id IN NUMBER,
288 c_db_item_suffix IN VARCHAR2) IS
289 select pdb.defined_balance_id from
290 pay_defined_balances pdb,
291 pay_balance_dimensions pbd
292 where pdb.balance_dimension_id = pbd.balance_dimension_id
293 and pbd.database_item_suffix = c_db_item_suffix
294 and pdb.balance_type_id = c_balance_type_id;
295 --
296 BEGIN
297 --
298 open relevant(p_balance_type_id, p_database_item_suffix);
299 fetch relevant into l_defined_balance_id;
300 close relevant;
301 --
302 RETURN l_defined_balance_id;
303 --
304 END dimension_relevant;
305 --------------------------------------------------------------------------------
306 --
307 -- GET LATEST DATE (private)
308 --
309 --
310 --------------------------------------------------------------------------------
311 --
312 -- Find out the effective date of the latest balance of a particular
313 -- assignment action.
314 --
315 FUNCTION get_latest_date(
316 p_assignment_action_id NUMBER)
317 RETURN DATE IS
318 --
319 l_effective_date date;
320 --
321 cursor c_bal_date is
322 SELECT ppa.effective_date
323 FROM pay_payroll_actions ppa,
324 pay_assignment_actions paa
325 WHERE paa.payroll_action_id = ppa.payroll_action_id
326 AND paa.assignment_action_id = p_assignment_action_id;
327 --
328 begin
329 --
330 OPEN c_bal_date;
331 FETCH c_bal_date into l_effective_date;
332 if c_bal_date%NOTFOUND then
333 l_effective_date := null;
334 -- raise_application_error(-20000,'This assignment action is invalid');
335 end if;
336 CLOSE c_bal_date;
337 --
338 RETURN l_effective_date;
339 END get_latest_date;
340 --
341 -------------------------------------------------------------------------------
342 --
343 -- GET_EXPIRED_YEAR_DATE (private)
344 --
345 -------------------------------------------------------------------------------
346 --
347 -- Find out the expiry of the Tax year of the assignment action's effective date,
348 -- for expiry checking in the main functions.
349 --
350 FUNCTION get_expired_year_date(
351 p_assignment_action_id NUMBER)
352 RETURN DATE IS
353 --
354 l_expired_date DATE;
355 l_tax_year NUMBER;
356 l_year_add_no NUMBER;
357 l_payroll_id NUMBER;
358 --
359 -- Get the tax year end date
360 --
361 cursor tax_year (c_assignment_action_id IN NUMBER) is
362 SELECT ptp.prd_information1, ppa.payroll_id
363 FROM per_time_periods ptp,
364 pay_payroll_actions ppa,
365 pay_assignment_actions paa
366 WHERE
367 paa.assignment_action_id = c_assignment_action_id
368 AND paa.payroll_action_id = ppa.payroll_action_id
369 AND ppa.time_period_id = ptp.time_period_id;
370 --
371 cursor tax_year_end (c_tax_year IN NUMBER, c_payroll_id IN NUMBER) is
372 SELECT max(ptp.end_date)
373 FROM per_time_periods ptp
374 WHERE
375 ptp.prd_information1 = c_tax_year
376 AND ptp.payroll_id = c_payroll_id;
377 --
378 BEGIN
379 --
380 open tax_year(p_assignment_action_id);
381 FETCH tax_year INTO l_tax_year, l_payroll_id;
382 close tax_year;
383 --
384 open tax_year_end(l_tax_year, l_payroll_id);
385 FETCH tax_year_end INTO l_expired_date;
386 close tax_year_end;
387 --
388 RETURN l_expired_date;
389 --
390 END get_expired_year_date;
391 --
392 -------------------------------------------------------------------------------
393 --------------------------------------------------------------------------------
394 --
395 -- what is the latest reset date for a particular dimension
396 FUNCTION dimension_reset_date(
397 p_dimension_name VARCHAR2,
398 p_user_date DATE,
399 p_business_group_id NUMBER)
400 RETURN DATE
401 IS
402 l_start_dd_mon VARCHAR2(7);
403 l_global_name VARCHAR2(30);
404 l_period_from_date DATE;
405 l_frequency NUMBER;
406 l_start_reset NUMBER;
407 BEGIN
408 IF SUBSTR(p_dimension_name,31,8) = 'USER-REG' THEN -- [
409 l_start_reset := INSTR(p_dimension_name,'RESET',30);
410 l_start_dd_mon := SUBSTR(p_dimension_name, l_start_reset - 6, 6);
411 l_frequency := TO_NUMBER(SUBSTR(p_dimension_name, l_start_reset + 6, 2));
412 l_period_from_date := span_start(p_user_date, l_frequency, l_start_dd_mon);
413 END IF; -- ]
414
415 /* */
416 /* User Irregular Balance are not yet implemented */
417 /* */
418 /*
419 IF SUBSTR(p_dimension_name,1,14) = 'USER IRREGULAR' THEN -- [
420 -- find the global set up with the reset dates
421 -- need to code exception if there isn't a valid one (default to calendar
422 -- also make this code a local function
423 l_start_word := INSTR(p_dimension_name,'BASED ON') + 8;
424 l_global_name := SUBSTR(p_dimension_name, l_start_word);
425 SELECT
426 effective_start_date
427 INTO
428 l_period_from_date
429 FROM
430 ff_globals_f
431 WHERE global_name = l_global_name
432 AND business_group_id = p_business_group_id
433 AND p_user_date BETWEEN effective_start_date AND effective_end_date;
434 END IF; -- ]
435 */
436
437 RETURN l_period_from_date;
438 END dimension_reset_date;
439 --------------------------------------------------------------------------------
440 -- when did the director become a director
441 -- find the earliest person row that was date effcetive in this year with
442 -- director flag set
443 FUNCTION start_director(
444 p_assignment_id NUMBER,
445 p_start_date DATE ,
446 p_end_date DATE )
447 RETURN DATE
448 IS
449 l_event_from_date date;
450 BEGIN
451 select nvl(min(P.effective_start_date)
452 ,to_date('31-12-4712','dd-mm-yyyy'))
453 into l_event_from_date
454 from per_people_f p, /* should this be all ? */
455 per_assignments_f ass
456 where p.per_information2 = 'Y'
457 and ASS.person_id = P.person_id
458 and P.effective_start_date <= p_end_date
459 and p.effective_end_date >= p_start_date
460 and p_end_date between
461 ass.effective_start_date and ass.effective_end_date
462 and ass.assignment_id = p_assignment_id ;
463
464 RETURN l_event_from_date;
465 END start_director;
466 --------------------------------------------------------------------------------
467 --
468 -- BALANCE --
469 -- FASTFORMULA cover for evaluating balances based on assignment_action_id
470 --
471 --
472 --------------------------------------------------------------------------------
473 --
474 FUNCTION balance(
475 p_assignment_action_id IN NUMBER,
476 p_defined_balance_id IN NUMBER)
477 RETURN NUMBER
478 IS
479 --
480 l_balance NUMBER;
481 l_assignment_id NUMBER;
482 l_balance_type_id NUMBER;
483 l_period_from_date DATE := TO_DATE('01-01-0001','dd-mm-yyyy');
484 l_event_from_date DATE := TO_DATE('01-01-0001','dd-mm-yyyy') ;
485 l_to_date DATE;
486 l_regular_payment_date DATE;
487 l_action_sequence NUMBER;
488 l_business_group_id NUMBER;
489 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
490 l_database_item_suffix pay_balance_dimensions.database_item_suffix%TYPE;
491 l_legislation_code pay_balance_dimensions.legislation_code%TYPE;
492 l_latest_value_exists VARCHAR(2);
493 --
494 cursor c1 (c_asg_action_id IN NUMBER,
495 c_defined_balance_id IN NUMBER)is
496 SELECT value, assignment_id
497 from pay_assignment_latest_balances
498 Where assignment_action_id = c_asg_action_id
499 and defined_balance_id = c_defined_balance_id;
500 --
501 cursor action_context is
502 SELECT
503 BAL_ASSACT.assignment_id,
504 BAL_ASSACT.action_sequence,
505 BACT.effective_date,
506 PTP.regular_payment_date,
507 BACT.business_group_id
508 FROM
509 pay_assignment_actions BAL_ASSACT,
510 pay_payroll_actions BACT,
511 per_time_periods PTP
512 WHERE
513 BAL_ASSACT.assignment_action_id = p_assignment_action_id
514 AND PTP.time_period_id = BACT.time_period_id
515 AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
516 --
517 cursor balance_dimension is
518 SELECT
519 DB.balance_type_id,
520 DIM.dimension_name,
521 DIM.database_item_suffix ,
522 DIM.legislation_code
523 FROM
524 pay_defined_balances DB,
525 pay_balance_dimensions DIM
526 WHERE DB.defined_balance_id = p_defined_balance_id
527 AND DIM.balance_dimension_id = DB.balance_dimension_id;
528 --
529 BEGIN
530 --
531 -- get the context of the using action
532 --
533 open action_context;
534 FETCH action_context INTO
535 l_assignment_id,
536 l_action_sequence,
537 l_to_date,
538 l_regular_payment_date,
539 l_business_group_id;
540 CLOSE action_context;
541 --
542 -- from the item name determine what balance and dimension it is
543 --
544 open balance_dimension;
545 FETCH balance_dimension INTO
546 l_balance_type_id,
547 l_dimension_name,
548 l_database_item_suffix ,
549 l_legislation_code;
550 close balance_dimension;
551 --
552 -- Does the assignment action id exist in the latest balances table
553 --
554 OPEN c1 (p_assignment_action_id, p_defined_balance_id);
555 FETCH c1 INTO l_balance, l_assignment_id;
556 IF c1%FOUND THEN l_latest_value_exists := 'T';
557 ELSE l_latest_value_exists := 'F';
558 END IF;
559 CLOSE c1;
560 --
561 -- If the latest bal value doesn't exist further action is necessary
562 --
563 IF l_latest_value_exists = 'F' then
564 --
565 -- for seeded person level dimensions call the person_bal function
566 --
567 IF substr(l_database_item_suffix,1,4) = '_PER' and l_legislation_code = 'GB'
568 THEN
569 /*-------------------------------------
570 l_balance := calc_person_bal(
571 l_assignment_id,
572 p_assignment_action_id,
573 l_balance_type_id,
574 l_database_item_suffix,
575 l_to_date,
576 l_action_sequence);
577 -------------------------------------*/
578 null;
579 ELSE
580 --
581 -- from the dimension work out the from dates
582 -- CALENDAR has no event start
583 --
584 IF l_dimension_name = '_ASG_CALENDAR_YTD' THEN
585 l_period_from_date := TRUNC(l_regular_payment_date,'YYYY');
586 END IF;
587 IF l_dimension_name = '_ASG_CALENDAR_QTD' THEN
588 l_period_from_date := TRUNC(l_regular_payment_date,'Q');
589 END IF;
590 --
591 -- evaluate user dimensions COMPANY, PENSION ..
592 --
593 IF SUBSTR(l_dimension_name,31,4) = 'USER' THEN
594 l_period_from_date := dimension_reset_date(
595 l_dimension_name,
596 l_regular_payment_date,
597 l_business_group_id);
598 END IF;
599 --
600 l_balance := calc_balance(
601 l_assignment_id,
602 l_balance_type_id,
603 l_period_from_date,
604 l_event_from_date,
605 l_to_date,
606 l_action_sequence);
607 END IF;
608 -- ELSE the balance remains the same from the latest balances table
609 END IF;
610 --
611 RETURN l_balance;
612 --
613 END balance;
614 --
615 --
616 -----------------------------------------------------------------------------
617 --
618 -- CALC_ALL_BALANCES
619 -- This is the generic overloaded function for calculating all balances
620 -- in assignment action mode. NB Element level balances cannot be called
621 -- from here as they require further context.
622 -----------------------------------------------------------------------------
623 --
624 FUNCTION calc_all_balances(
625 p_assignment_action_id IN NUMBER,
626 p_defined_balance_id IN NUMBER)
627 --
628 RETURN NUMBER
629 IS
630 --
631 l_balance NUMBER;
632 l_balance_type_id NUMBER;
633 l_dimension_name VARCHAR2(80);
634 --
635 cursor get_balance_type_id(c_defined_balance_id IN NUMBER) IS
636 select pdb.balance_type_id,
637 pbd.dimension_name
638 from pay_balance_dimensions pbd,
639 pay_defined_balances pdb
640 where pdb.defined_balance_id = c_defined_balance_id
641 and pdb.balance_dimension_id = pbd.balance_dimension_id;
642 --
643 BEGIN
644 --
645 open get_balance_type_id(p_defined_balance_id);
646 FETCH get_balance_type_id INTO
647 l_balance_type_id, l_dimension_name;
648 CLOSE get_balance_type_id;
649 --
650 If l_dimension_name like '%_ASG_TAX_PTD' then
651 l_balance := calc_asg_tax_ptd_action(p_assignment_action_id,
652 l_balance_type_id);
653 Elsif l_dimension_name like '%_ASG_TAX_YTD' then
654 l_balance := calc_asg_tax_ytd_action(p_assignment_action_id,
655 l_balance_type_id);
656 Elsif l_dimension_name like '%_ASG_TAX_MTD' then
657 l_balance := calc_asg_tax_mtd_action(p_assignment_action_id,
658 l_balance_type_id);
659 Elsif l_dimension_name like '%_ASG_TAX_QTD' then
660 l_balance := calc_asg_tax_qtd_action(p_assignment_action_id,
661 l_balance_type_id);
662 Elsif l_dimension_name like '%_PAYMENTS' then
663 l_balance := calc_payments_action(p_assignment_action_id,
664 l_balance_type_id);
665 Elsif l_dimension_name like '%_ASG_RUN' then
666 l_balance := calc_asg_run_action(p_assignment_action_id,
667 l_balance_type_id);
668 Elsif l_dimension_name like '%_ASG_ITD' then
669 l_balance := calc_asg_itd_action(p_assignment_action_id,
670 l_balance_type_id);
671 Elsif l_dimension_name like '%_ASG_CAL_PTD' then
672 l_balance := calc_asg_cal_ptd_action(p_assignment_action_id,
673 l_balance_type_id);
674 Elsif l_dimension_name like '%_ASG_CAL_YTD' then
675 l_balance := calc_asg_cal_ytd_action(p_assignment_action_id,
676 l_balance_type_id);
677 Elsif l_dimension_name like '%_ASG_CAL_MTD' then
678 l_balance := calc_asg_cal_mtd_action(p_assignment_action_id,
679 l_balance_type_id);
680 --Else the balance must be for a USER-REG or person level dimension
681 Else
682 /*----------------------------------------------------------------------------
683 -- l_balance := balance(p_assignment_action_id, p_defined_balance_id);
684 ----------------------------------------------------------------------------*/
685 null;
686 End If;
687 --
688 RETURN l_balance;
689 --
690 END calc_all_balances;
691 --
692 -----------------------------------------------------------------------------
693 --
694 -- CALC_ALL_BALANCES
695 --
696 -- This is the overloaded generic function for calculating all balances
697 -- in Date Mode. NB Element level balances cannot be obtained from here as
698 -- they require further context.
699 -----------------------------------------------------------------------------
700 --
701 FUNCTION calc_all_balances(
702 p_effective_date IN DATE,
703 p_assignment_id IN NUMBER,
704 p_defined_balance_id IN NUMBER)
705 --
706 RETURN NUMBER
707 IS
708 --
709 l_balance NUMBER;
710 l_balance_type_id NUMBER;
711 l_dimension_name VARCHAR2(80);
712 l_assignment_action_id NUMBER;
713 --
714 cursor get_balance_type_id(c_defined_balance_id IN NUMBER) IS
715 select pdb.balance_type_id,
716 pbd.dimension_name
717 from pay_balance_dimensions pbd,
718 pay_defined_balances pdb
719 where pdb.defined_balance_id = c_defined_balance_id
720 and pdb.balance_dimension_id = pbd.balance_dimension_id;
721 --
722 BEGIN
723 --
724 open get_balance_type_id(p_defined_balance_id);
725 FETCH get_balance_type_id INTO
726 l_balance_type_id, l_dimension_name;
727 CLOSE get_balance_type_id;
728 --
729 If l_dimension_name like '%_ASG_TAX_PTD' then
730 l_balance := calc_asg_tax_ptd_date(p_assignment_id,
731 l_balance_type_id,
732 p_effective_date);
733 Elsif l_dimension_name like '%_ASG_TAX_YTD' then
734 l_balance := calc_asg_tax_ytd_date(p_assignment_id,
735 l_balance_type_id,
736 p_effective_date);
737 Elsif l_dimension_name like '%_ASG_TAX_MTD' then
738 l_balance := calc_asg_tax_mtd_date(p_assignment_id,
739 l_balance_type_id,
740 p_effective_date);
741 Elsif l_dimension_name like '%_ASG_TAX_QTD' then
742 l_balance := calc_asg_tax_qtd_date(p_assignment_id,
743 l_balance_type_id,
744 p_effective_date);
745 Elsif l_dimension_name like '%_PAYMENTS' then
746 l_balance := calc_payments_date(p_assignment_id,
747 l_balance_type_id,
748 p_effective_date);
749 Elsif l_dimension_name like '%_ASG_RUN' then
750 l_balance := calc_asg_run_date(p_assignment_id,
751 l_balance_type_id,
752 p_effective_date);
753 Elsif l_dimension_name like '%_ASG_ITD' then
754 l_balance := calc_asg_itd_date(p_assignment_id,
755 l_balance_type_id,
756 p_effective_date);
757 Elsif l_dimension_name like '%_ASG_CAL_PTD' then
758 l_balance := calc_asg_cal_ptd_date(p_assignment_id,
759 l_balance_type_id,
760 p_effective_date);
761 Elsif l_dimension_name like '%_ASG_CAL_YTD' then
762 l_balance := calc_asg_cal_ytd_date(p_assignment_id,
763 l_balance_type_id,
764 p_effective_date);
765 Elsif l_dimension_name like '%_ASG_CAL_MTD' then
766 l_balance := calc_asg_cal_mtd_date(p_assignment_id,
767 l_balance_type_id,
768 p_effective_date);
769 Else
770 --This will trap USER-REG and PERSON level balances
771 /*--------------------------------------------------------------------------
772 -- l_assignment_action_id := get_latest_action_id(p_assignment_id,
773 -- p_effective_date);
774 -- l_balance := balance(l_assignment_action_id,
775 -- p_defined_balance_id);
776 --------------------------------------------------------------------------*/
777 null;
778 End If;
779 --
780 RETURN l_balance;
781 --
782 END calc_all_balances;
783 --
784 -----------------------------------------------------------------------------
785 ---
786 --
787 -- CALC_ASG_RUN_ACTION -
788 --
789 -- This is the function for calculating assignment
790 -- runs in assignment action mode
791 --
792 -----------------------------------------------------------------------------
793 --
794 FUNCTION calc_asg_run_action(
795 p_assignment_action_id IN NUMBER,
796 p_balance_type_id IN NUMBER,
797 p_effective_date IN DATE)
798 RETURN NUMBER
799 IS
800 --
801 l_assignment_action_id NUMBER;
802 l_balance NUMBER;
803 l_assignment_id NUMBER;
804 l_effective_date DATE;
805 --
806 BEGIN
807 --
808 l_assignment_id := get_correct_type(p_assignment_action_id);
809 IF l_assignment_id is null THEN
810 --
811 -- The assignment action is not a payroll or quickpay type, so return null
812 --
813 l_balance := null;
814 ELSE
815 --
816 l_balance := calc_asg_run(
817 p_assignment_action_id => p_assignment_action_id,
818 p_balance_type_id => p_balance_type_id,
819 p_effective_date => p_effective_date,
820 p_assignment_id => l_assignment_id);
821 END IF;
822 --
823 RETURN l_balance;
824 end calc_asg_run_action;
825 --
826 -----------------------------------------------------------------------------
827 ---
828 --
829 -- CALC_ASG_RUN_DATE -
830 --
831 -- This is the function for calculating assignment run in
832 -- DATE MODE
833 --
834 -----------------------------------------------------------------------------
835 --
836 FUNCTION calc_asg_run_date(
837 p_assignment_id IN NUMBER,
838 p_balance_type_id IN NUMBER,
839 p_effective_date IN DATE)
840 RETURN NUMBER
841 IS
842 --
843 l_assignment_action_id NUMBER;
844 l_balance NUMBER;
845 l_end_date DATE;
846 --
847 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
848 SELECT ptp.end_date
849 FROM per_time_periods ptp,
850 pay_payroll_actions ppa,
851 pay_assignment_actions paa
852 WHERE
853 paa.assignment_action_id = c_assignment_action_id
854 AND paa.payroll_action_id = ppa.payroll_action_id
855 AND ppa.time_period_id = ptp.time_period_id;
856 --
857 BEGIN
858 --
859 l_assignment_action_id := get_latest_action_id(p_assignment_id,
860 p_effective_date);
861 IF l_assignment_action_id is null THEN
862 l_balance := 0;
863 ELSE
864 open expired_time_period(l_assignment_action_id);
865 FETCH expired_time_period INTO l_end_date;
866 close expired_time_period;
867 --
868 if l_end_date < p_effective_date then
869 l_balance := 0;
870 else
871 l_balance := calc_asg_run(
872 p_assignment_action_id => l_assignment_action_id,
873 p_balance_type_id => p_balance_type_id,
874 p_effective_date => p_effective_date,
875 p_assignment_id => p_assignment_id);
876 end if;
877 END IF;
878 --
879 RETURN l_balance;
880 end calc_asg_run_date;
881 --
882 -----------------------------------------------------------------------------
883 ---
884 --
885 -- CALC_ASG_RUN -
886 --
887 -- calculate balances for Assignment Run
888 --
889 -----------------------------------------------------------------------------
890 --
891 -- Run
892 -- the simplest dimension retrieves run values where the context
893 -- is this assignment action and this balance feed. Balance is the
894 -- specified input value. The related payroll action determines the
895 -- date effectivity of the feeds
896 --
897 FUNCTION calc_asg_run(
898 p_assignment_action_id IN NUMBER,
899 p_balance_type_id IN NUMBER,
900 p_effective_date IN DATE DEFAULT NULL,
901 p_assignment_id IN NUMBER
902 )
903 RETURN NUMBER
904 IS
905 --
906 --
907 l_balance NUMBER;
908 l_defined_bal_id NUMBER;
909 --
910 BEGIN
911 --
912 --Do we need to work out a value for this dimension/balance combination.
913 --
914 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_RUN');
915 if l_defined_bal_id is not null then
916 --
917 -- Run balances will never have a value in pay_assignment_latest_balances
918 -- table, as they are only used for the duration of the payroll run.
919 -- We therefore don't need to check the table, time can be saved by
920 -- simply calling the route code, which is incidentally the most
921 -- performant (ie simple) route.
922 --
923 l_balance := py_za_routes.asg_run(p_assignment_action_id,
924 p_balance_type_id);
925 --
926 else l_balance := null;
927 end if;
928 --
929 RETURN l_balance;
930 --
931 END calc_asg_run;
932 --
933 -----------------------------------------------------------------------------
934 ---
935 --
936 -- CALC_PAYMENTS_ACTION -
937 --
938 -- This is the function for calculating payments
939 -- in assignment action mode
940 --
941 -----------------------------------------------------------------------------
942 --
943 FUNCTION calc_payments_action(
944 p_assignment_action_id IN NUMBER,
945 p_balance_type_id IN NUMBER,
946 p_effective_date IN DATE)
947 RETURN NUMBER
948 IS
949 --
950 l_assignment_action_id NUMBER;
951 l_balance NUMBER;
952 l_assignment_id NUMBER;
953 l_effective_date DATE;
954 --
955 BEGIN
956 --
957 l_assignment_id := get_correct_type(p_assignment_action_id);
958 IF l_assignment_id is null THEN
959 --
960 -- The assignment action is not a payroll or quickpay type, so return null
961 --
962 l_balance := null;
963 ELSE
964 --
965 l_balance := calc_payments(
966 p_assignment_action_id => p_assignment_action_id,
967 p_balance_type_id => p_balance_type_id,
968 p_effective_date => p_effective_date,
969 p_assignment_id => l_assignment_id);
970 END IF;
971 --
972 RETURN l_balance;
973 end calc_payments_action;
974 --
975 -----------------------------------------------------------------------------
976 ---
977 --
978 -- CALC_PAYMENTS_DATE -
979 --
980 -- This is the function for calculating payments in
981 -- DATE MODE
982 --
983 -----------------------------------------------------------------------------
984 --
985 FUNCTION calc_payments_date(
986 p_assignment_id IN NUMBER,
987 p_balance_type_id IN NUMBER,
988 p_effective_date IN DATE)
989 RETURN NUMBER
990 IS
991 --
992 l_assignment_action_id NUMBER;
993 l_balance NUMBER;
994 l_end_date DATE;
995 --
996 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
997 SELECT ptp.end_date
998 FROM per_time_periods ptp,
999 pay_payroll_actions ppa,
1000 pay_assignment_actions paa
1001 WHERE
1002 paa.assignment_action_id = c_assignment_action_id
1003 AND paa.payroll_action_id = ppa.payroll_action_id
1004 AND ppa.time_period_id = ptp.time_period_id;
1005 --
1006 BEGIN
1007 --
1008 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1009 p_effective_date);
1010 IF l_assignment_action_id is null THEN
1011 l_balance := 0;
1012 ELSE
1013 open expired_time_period(l_assignment_action_id);
1014 FETCH expired_time_period INTO l_end_date;
1015 close expired_time_period;
1016 --
1017 if l_end_date < p_effective_date then
1018 l_balance := 0;
1019 else
1020 l_balance := calc_payments(
1021 p_assignment_action_id => l_assignment_action_id,
1022 p_balance_type_id => p_balance_type_id,
1023 p_effective_date => p_effective_date,
1024 p_assignment_id => p_assignment_id);
1025 end if;
1026 END IF;
1027 --
1028 RETURN l_balance;
1029 end calc_payments_date;
1030 --
1031 -----------------------------------------------------------------------------
1032 ---
1033 --
1034 -- CALC_PAYMENTS -
1035 --
1036 -- calculate balances for payments
1037 --
1038 -----------------------------------------------------------------------------
1039 --
1040 -- this dimension is used in the pre-payments process - that process
1041 -- creates interlocks for the actions that are included and the payments
1042 -- dimension uses those interlocks to decide which run results to sum
1043 --
1044 --
1045 FUNCTION calc_payments(
1046 p_assignment_action_id IN NUMBER,
1047 p_balance_type_id IN NUMBER,
1048 p_effective_date IN DATE DEFAULT NULL,
1049 p_assignment_id IN NUMBER
1050 )
1051 RETURN NUMBER
1052 IS
1053 --
1054 --
1055 l_expired_balance NUMBER;
1056 l_balance NUMBER;
1057 l_latest_value_exists VARCHAR2(2);
1058 l_assignment_action_id NUMBER;
1059 l_action_eff_date DATE;
1060 l_end_date DATE;
1061 l_defined_bal_id NUMBER;
1062 --
1063 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
1064 SELECT ptp.end_date
1065 FROM per_time_periods ptp,
1066 pay_payroll_actions ppa,
1067 pay_assignment_actions paa
1068 WHERE
1069 paa.assignment_action_id = c_assignment_action_id
1070 AND paa.payroll_action_id = ppa.payroll_action_id
1071 AND ppa.time_period_id = ptp.time_period_id;
1072 --
1073 BEGIN
1074 --
1075 --Do we need to work out a value for this dimension/balance combination.
1076 --
1077 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_PAYMENTS');
1078 if l_defined_bal_id is not null then
1079 --
1080 -- Is there a value in the latest balances table ..
1081 --
1082 l_balance := get_latest_balance(p_assignment_action_id,
1083 l_defined_bal_id);
1084 --
1085 if l_balance is null then l_latest_value_exists := 'F';
1086 else l_latest_value_exists := 'T';
1087 end if;
1088 --
1089 if l_latest_value_exists = 'F' then
1090 --
1091 --c1. See if there is a balance value for the assignment/defined balance
1092 get_owning_balance(p_assignment_id => p_assignment_id,
1093 p_defined_balance_id => l_defined_bal_id,
1094 p_assignment_action_id => l_assignment_action_id,
1095 p_value => l_balance);
1096 --
1097 if l_balance is null then l_latest_value_exists := 'F';
1098 else
1099 --check just-retrieved action has a lower action sequence than
1100 --original assignment action
1101 if sequence(p_assignment_action_id) >
1102 sequence(l_assignment_action_id) then
1103 -- Action from past, so usable.
1104 l_latest_value_exists := 'T';
1105 --we have to validate
1106 l_action_eff_date := get_latest_date(p_assignment_action_id);
1107 open expired_time_period(l_assignment_action_id);
1108 FETCH expired_time_period INTO l_end_date;
1109 close expired_time_period;
1110 --
1111 if l_end_date < l_action_eff_date then
1112 l_balance := 0;
1113 -- else the balance remains the same from c1.
1114 end if;
1115 else
1116 -- The action is from the future.
1117 -- Is the action the 'expired action' held on the table
1118 l_expired_balance := check_expired_action(l_defined_bal_id,
1119 p_assignment_id,
1120 p_assignment_action_id);
1121 if l_expired_balance is not null then
1122 --we have matched the expired action with the current action,
1123 --therefore we can assign the expired value as the current value.
1124 l_balance := l_expired_balance;
1125 l_latest_value_exists := 'T';
1126 end if;
1127 end if;
1128 end if;
1129 --
1130 end if;
1131 --
1132 -- If the balance is STILL not found,
1133 --
1134 if l_latest_value_exists = 'F' then
1135 --
1136 l_balance := py_za_routes.payments(p_assignment_action_id,
1137 p_balance_type_id);
1138 end if;
1139 --
1140 else l_balance := null;
1141 end if;
1142 --
1143 RETURN l_balance;
1144 --
1145 END calc_payments;
1146 --
1147 -----------------------------------------------------------------------------
1148 ---
1149 --
1150 -- CALC_ASG_ITD_ACTION -
1151 --
1152 -- This is the function for calculating assignment
1153 -- Inception to date in assignment action mode
1154 --
1155 -----------------------------------------------------------------------------
1156 --
1157 FUNCTION calc_asg_itd_action(
1158 p_assignment_action_id IN NUMBER,
1159 p_balance_type_id IN NUMBER,
1160 p_effective_date IN DATE)
1161 RETURN NUMBER
1162 IS
1163 --
1164 l_assignment_action_id NUMBER;
1165 l_balance NUMBER;
1166 l_assignment_id NUMBER;
1167 l_effective_date DATE;
1168 --
1169 BEGIN
1170 --
1171 l_assignment_id := get_correct_type(p_assignment_action_id);
1172 IF l_assignment_id is null THEN
1173 --
1174 -- The assignment action is not a payroll or quickpay type, so return null
1175 --
1176 l_balance := null;
1177 ELSE
1178 --
1179 l_balance := calc_asg_itd(p_assignment_id => l_assignment_id,
1180 p_assignment_action_id => p_assignment_action_id,
1181 p_balance_type_id => p_balance_type_id,
1182 p_effective_date => p_effective_date);
1183 END IF;
1184 --
1185 RETURN l_balance;
1186 end calc_asg_itd_action;
1187 --
1188 -----------------------------------------------------------------------------
1189 ---
1190 --
1191 -- CALC_ASG_ITD_DATE -
1192 --
1193 -- This is the function for calculating assignment inception to
1194 -- date in DATE MODE
1195 --
1196 -----------------------------------------------------------------------------
1197 --
1198 FUNCTION calc_asg_itd_date(
1199 p_assignment_id IN NUMBER,
1200 p_balance_type_id IN NUMBER,
1201 p_effective_date IN DATE)
1202 RETURN NUMBER
1203 IS
1204 --
1205 l_assignment_action_id NUMBER;
1206 l_balance NUMBER;
1207 l_end_date DATE;
1208 --
1209 BEGIN
1210 --
1211 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1212 p_effective_date);
1213 IF l_assignment_action_id is null THEN
1214 l_balance := 0;
1215 ELSE
1216 l_balance := calc_asg_itd(
1217 p_assignment_id => p_assignment_id,
1218 p_assignment_action_id => l_assignment_action_id,
1219 p_balance_type_id => p_balance_type_id,
1220 p_effective_date => p_effective_date);
1221 END IF;
1222 --
1223 RETURN l_balance;
1224 end calc_asg_itd_date;
1225 --
1226 -----------------------------------------------------------------------------
1227 ---
1228 --
1229 -- CALC_ASG_ITD -
1230 --
1231 -- calculate balances for Assignment Inception to Date
1232 --
1233 -----------------------------------------------------------------------------
1234 --
1235 -- Sum of all run items since inception.
1236 --
1237 FUNCTION calc_asg_itd(
1238 p_assignment_action_id IN NUMBER,
1239 p_balance_type_id IN NUMBER,
1240 p_effective_date IN DATE DEFAULT NULL, -- in for consistency
1241 p_assignment_id IN NUMBER
1242 )
1243 RETURN NUMBER
1244 IS
1245 --
1246 --
1247 l_balance NUMBER;
1248 l_latest_value_exists VARCHAR2(2);
1249 l_assignment_action_id NUMBER;
1250 l_action_eff_date DATE;
1251 l_defined_bal_id NUMBER;
1252 --
1253 BEGIN
1254 --
1255 --Do we need to work out a value for this dimension/balance combination.
1256 --
1257 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_ITD');
1258 if l_defined_bal_id is not null then
1259 --
1260 -- Is there a value in the latest balances table ..
1261 --
1262 l_balance := get_latest_balance(p_assignment_action_id,
1263 l_defined_bal_id);
1264 --
1265 if l_balance is null then l_latest_value_exists := 'F';
1266 else l_latest_value_exists := 'T';
1267 end if;
1268 --
1269 if l_latest_value_exists = 'F' then
1270 --
1271 --See if there is a balance value for the assignment/defined balance
1272 get_owning_balance(p_assignment_id => p_assignment_id,
1273 p_defined_balance_id => l_defined_bal_id,
1274 p_assignment_action_id => l_assignment_action_id,
1275 p_value => l_balance);
1276 --
1277 if l_balance is null then l_latest_value_exists := 'F';
1278 else
1279 --check just-retrieved action has a lower action sequence than
1280 --original assignment action
1281 if sequence(p_assignment_action_id) >
1282 sequence(l_assignment_action_id) then
1283 -- Action from past, so usable.
1284 -- No Expiry check for Inception To Date balance.
1285 l_latest_value_exists := 'T';
1286 --else balance is from future, can't use.
1287 else l_balance := null;
1288 end if;
1289 end if;
1290 end if;
1291 --
1292 -- still No balance, so use route code
1293 --
1294 if l_latest_value_exists = 'F' then
1295 --
1296 l_balance := py_za_routes.asg_itd(p_assignment_action_id,
1297 p_balance_type_id);
1298 end if;
1299 --
1300 else l_balance := null;
1301 end if;
1302 --
1303 RETURN l_balance;
1304 --
1305 END calc_asg_itd;
1306 --
1307 --
1308 -----------------------------------------------------------------------------
1309 ---
1310 --
1311 -- CALC_ASG_TAX_PTD_ACTION
1312 --
1313 -- This is the function for calculating assignment
1314 -- tax period to date in assignment action mode
1315 --
1316 -----------------------------------------------------------------------------
1317 --
1318 FUNCTION calc_ASG_TAX_PTD_action(
1319 p_assignment_action_id IN NUMBER,
1320 p_balance_type_id IN NUMBER,
1321 p_effective_date IN DATE)
1322 RETURN NUMBER
1323 IS
1324 --
1325 l_assignment_action_id NUMBER;
1326 l_balance NUMBER;
1327 l_assignment_id NUMBER;
1328 l_effective_date DATE;
1329 --
1330 BEGIN
1331 --
1332 l_assignment_id := get_correct_type(p_assignment_action_id);
1333 IF l_assignment_id is null THEN
1334 --
1335 -- The assignment action is not a payroll or quickpay type, so return null
1336 --
1337 l_balance := null;
1338 ELSE
1339 --
1340 l_balance := calc_ASG_TAX_PTD(
1341 p_assignment_action_id => p_assignment_action_id,
1342 p_balance_type_id => p_balance_type_id,
1343 p_effective_date => p_effective_date,
1344 p_assignment_id => l_assignment_id);
1345 END IF;
1346 --
1347 RETURN l_balance;
1348 end calc_ASG_TAX_PTD_action;
1349 --
1350 -----------------------------------------------------------------------------
1351 ---
1352 --
1353 -- CALC_ASG_TAX_PTD_DATE
1354 --
1355 -- This is the function for calculating assignment tax
1356 -- period to date in date mode
1357 --
1358 -----------------------------------------------------------------------------
1359 --
1360 FUNCTION calc_ASG_TAX_PTD_date(
1361 p_assignment_id IN NUMBER,
1362 p_balance_type_id IN NUMBER,
1363 p_effective_date IN DATE)
1364 RETURN NUMBER
1365 IS
1366 --
1367 l_assignment_action_id NUMBER;
1368 l_balance NUMBER;
1369 l_end_date DATE;
1370 --
1371 -- Has the processing time period expired
1372 --
1373 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
1374 SELECT ptp.end_date
1375 FROM per_time_periods ptp,
1376 pay_payroll_actions ppa,
1377 pay_assignment_actions paa
1378 WHERE
1379 paa.assignment_action_id = c_assignment_action_id
1380 AND paa.payroll_action_id = ppa.payroll_action_id
1381 AND ppa.time_period_id = ptp.time_period_id;
1382 --
1383 BEGIN
1384 --
1385 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1386 p_effective_date);
1387 IF l_assignment_action_id is null THEN
1388 l_balance := 0;
1389 ELSE
1390 open expired_time_period(l_assignment_action_id);
1391 FETCH expired_time_period INTO l_end_date;
1392 close expired_time_period;
1393 --
1394 if l_end_date < p_effective_date then
1395 l_balance := 0;
1396 else
1397 l_balance := calc_ASG_TAX_PTD(
1398 p_assignment_action_id => l_assignment_action_id,
1399 p_balance_type_id => p_balance_type_id,
1400 p_effective_date => p_effective_date,
1401 p_assignment_id => p_assignment_id);
1402 end if;
1403 END IF;
1404 --
1405 RETURN l_balance;
1406 end calc_ASG_TAX_PTD_date;
1407 --
1408 --------------------------------------------------------------------------------
1409 --
1410 -- CALC_ASG_TAX_PTD
1411 --
1412 -- calculate Assignment tax period to date
1413 --
1414 --------------------------------------------------------------------------------
1415 --
1416 --
1417 -- This dimension is the total for an assignment within the processing
1418 -- period of its current payroll.
1419 --
1420 FUNCTION calc_ASG_TAX_PTD(
1421 p_assignment_action_id IN NUMBER,
1422 p_balance_type_id IN NUMBER,
1423 p_effective_date IN DATE DEFAULT NULL,
1424 p_assignment_id IN NUMBER
1425 )
1426 RETURN NUMBER
1427 IS
1428 --
1429 --
1430 l_expired_balance NUMBER;
1431 l_balance NUMBER;
1432 l_latest_value_exists VARCHAR2(2);
1433 l_assignment_action_id NUMBER;
1434 l_action_eff_date DATE;
1435 l_end_date DATE;
1436 l_defined_bal_id NUMBER;
1437 --
1438 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
1439 SELECT ptp.end_date
1440 FROM per_time_periods ptp,
1441 pay_payroll_actions ppa,
1442 pay_assignment_actions paa
1443 WHERE
1444 paa.assignment_action_id = c_assignment_action_id
1445 AND paa.payroll_action_id = ppa.payroll_action_id
1446 AND ppa.time_period_id = ptp.time_period_id;
1447 --
1448 BEGIN
1449 --
1450 --Do we need to work out a value for this dimension/balance combination.
1451 --
1452 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TAX_PTD');
1453 if l_defined_bal_id is not null then
1454 --
1455 -- Is there a value in the latest balances table ..
1456 --
1457 l_balance := get_latest_balance(p_assignment_action_id,
1458 l_defined_bal_id);
1459 --
1460 if l_balance is null then l_latest_value_exists := 'F';
1461 else l_latest_value_exists := 'T';
1462 end if;
1463 --
1464 if l_latest_value_exists = 'F' then
1465 --
1466 --c1. See if there is a balance value for the assignment/defined balance
1467 get_owning_balance(p_assignment_id => p_assignment_id,
1468 p_defined_balance_id => l_defined_bal_id,
1469 p_assignment_action_id => l_assignment_action_id,
1470 p_value => l_balance);
1471 --
1472 if l_balance is null then l_latest_value_exists := 'F';
1473 else
1474 --check just-retrieved action has a lower action sequence than
1475 --original assignment action
1476 if sequence(p_assignment_action_id) >
1477 sequence(l_assignment_action_id) then
1478 -- Action from past, so usable.
1479 -- l_latest_value_exists := 'T';
1480 l_action_eff_date := get_latest_date(p_assignment_action_id);
1481 open expired_time_period(l_assignment_action_id);
1482 FETCH expired_time_period INTO l_end_date;
1483 close expired_time_period;
1484 --
1485 if l_end_date < l_action_eff_date then
1486 l_balance := 0;
1487 else --the balance remains the same from c1.
1488 l_latest_value_exists := 'T';
1489 end if;
1490 else
1491 -- The action is from the future.
1492 -- Is the action the 'expired action' held on the table
1493 l_expired_balance := check_expired_action(l_defined_bal_id,
1494 p_assignment_id,
1495 p_assignment_action_id);
1496 if l_expired_balance is not null then
1497 --we have matched the expired action with the current action,
1498 --therefore we can assign the expired value as the current value.
1499 l_balance := l_expired_balance;
1500 l_latest_value_exists := 'T';
1501 end if;
1502 end if;
1503 end if;
1504 --
1505 end if;
1506 --
1507 -- If the balance is STILL not found,
1508 --
1509 if l_latest_value_exists = 'F' then
1510 --
1511 l_balance := py_za_routes.ASG_TAX_PTD(p_assignment_action_id,
1512 p_balance_type_id);
1513 end if;
1514 --
1515 else l_balance := null;
1516 end if;
1517 --
1518 RETURN l_balance;
1519 --
1520 END calc_ASG_TAX_PTD;
1521 --
1522 -----------------------------------------------------------------------------
1523 ---
1524 --
1525 -- CALC_ASG_TAX_MTD_ACTION
1526 --
1527 -- This is the function for calculating assignment
1528 -- tax month to date in assignment action mode
1529 --
1530 -----------------------------------------------------------------------------
1531 --
1532 FUNCTION calc_ASG_TAX_MTD_action(
1533 p_assignment_action_id IN NUMBER,
1534 p_balance_type_id IN NUMBER,
1535 p_effective_date IN DATE)
1536 RETURN NUMBER
1537 IS
1538 --
1539 l_assignment_action_id NUMBER;
1540 l_balance NUMBER;
1541 l_assignment_id NUMBER;
1542 l_effective_date DATE;
1543 --
1544 BEGIN
1545 --
1546 l_assignment_id := get_correct_type(p_assignment_action_id);
1547 IF l_assignment_id is null THEN
1548 --
1549 -- The assignment action is not a payroll or quickpay type, so return null
1550 --
1551 l_balance := null;
1552 ELSE
1553 --
1554 l_balance := calc_ASG_TAX_MTD(
1555 p_assignment_action_id => p_assignment_action_id,
1556 p_balance_type_id => p_balance_type_id,
1557 p_effective_date => p_effective_date,
1558 p_assignment_id => l_assignment_id);
1559 END IF;
1560 --
1561 RETURN l_balance;
1562 end calc_ASG_TAX_MTD_action;
1563 --
1564 -----------------------------------------------------------------------------
1565 ---
1566 --
1567 -- CALC_ASG_TAX_MTD_DATE
1568 --
1569 -- This is the function for calculating assignment tax
1570 -- month to date in date mode
1571 --
1572 -----------------------------------------------------------------------------
1573 --
1574 FUNCTION calc_ASG_TAX_MTD_date(
1575 p_assignment_id IN NUMBER,
1576 p_balance_type_id IN NUMBER,
1577 p_effective_date IN DATE)
1578 RETURN NUMBER
1579 IS
1580 --
1581 l_assignment_action_id NUMBER;
1582 l_balance NUMBER;
1583 l_end_date DATE;
1584 --
1585 -- Has the processing time period expired (Month end date)
1586 --
1587 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
1588 SELECT ptp.pay_advice_date
1589 FROM per_time_periods ptp,
1590 pay_payroll_actions ppa,
1591 pay_assignment_actions paa
1592 WHERE
1593 paa.assignment_action_id = c_assignment_action_id
1594 AND paa.payroll_action_id = ppa.payroll_action_id
1595 AND ppa.time_period_id = ptp.time_period_id;
1596 --
1597 BEGIN
1598 --
1599 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1600 p_effective_date);
1601 IF l_assignment_action_id is null THEN
1602 l_balance := 0;
1603 ELSE
1604 open expired_time_period(l_assignment_action_id);
1605 FETCH expired_time_period INTO l_end_date;
1606 close expired_time_period;
1607 --
1608 if l_end_date < p_effective_date then
1609 l_balance := 0;
1610 else
1611 l_balance := calc_ASG_TAX_MTD(
1612 p_assignment_action_id => l_assignment_action_id,
1613 p_balance_type_id => p_balance_type_id,
1614 p_effective_date => p_effective_date,
1615 p_assignment_id => p_assignment_id);
1616 end if;
1617 END IF;
1618 --
1619 RETURN l_balance;
1620 end calc_ASG_TAX_MTD_date;
1621 --
1622 --------------------------------------------------------------------------------
1623 --
1624 -- CALC_ASG_TAX_MTD
1625 --
1626 -- calculate Assignment tax month to date
1627 --
1628 --------------------------------------------------------------------------------
1629 --
1630 --
1631 -- This dimension is the tax month total for an assignment within
1632 -- its current payroll.
1633 --
1634 FUNCTION calc_ASG_TAX_MTD(
1635 p_assignment_action_id IN NUMBER,
1636 p_balance_type_id IN NUMBER,
1637 p_effective_date IN DATE DEFAULT NULL,
1638 p_assignment_id IN NUMBER
1639 )
1640 RETURN NUMBER
1641 IS
1642 --
1643 --
1644 l_expired_balance NUMBER;
1645 l_balance NUMBER;
1646 l_latest_value_exists VARCHAR2(2);
1647 l_assignment_action_id NUMBER;
1648 l_action_eff_date DATE;
1649 l_owning_month_end_date DATE;
1650 l_defined_bal_id NUMBER;
1651 l_original_month_end_date DATE;
1652 --
1653 -- Has the processing time period expired (Month end date)
1654 --
1655 cursor owning_month_end_date (c_assignment_action_id IN NUMBER) is
1656 SELECT ptp.pay_advice_date
1657 FROM per_time_periods ptp,
1658 pay_payroll_actions ppa,
1659 pay_assignment_actions paa
1660 WHERE
1661 paa.assignment_action_id = c_assignment_action_id
1662 AND paa.payroll_action_id = ppa.payroll_action_id
1663 AND ppa.time_period_id = ptp.time_period_id;
1664 --
1665 cursor original_month_end_date (c_assignment_action_id IN NUMBER) is
1666 SELECT ptp.pay_advice_date
1667 FROM per_time_periods ptp,
1668 pay_payroll_actions ppa,
1669 pay_assignment_actions paa
1670 WHERE
1671 paa.assignment_action_id = c_assignment_action_id
1672 AND paa.payroll_action_id = ppa.payroll_action_id
1673 AND ppa.time_period_id = ptp.time_period_id;
1674 --
1675 BEGIN
1676 --
1677 --Do we need to work out a value for this dimension/balance combination.
1678 --
1679 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TAX_MTD');
1680 if l_defined_bal_id is not null then
1681 --
1682 -- Is there a value in the latest balances table ..
1683 --
1684 l_balance := get_latest_balance(p_assignment_action_id,
1685 l_defined_bal_id);
1686 --
1687 if l_balance is null then l_latest_value_exists := 'F';
1688 else l_latest_value_exists := 'T';
1689 end if;
1690 --
1691 if l_latest_value_exists = 'F' then
1692 --
1693 --c1. See if there is a balance value for the assignment/defined balance
1694 get_owning_balance(p_assignment_id => p_assignment_id,
1695 p_defined_balance_id => l_defined_bal_id,
1696 p_assignment_action_id => l_assignment_action_id,
1697 p_value => l_balance);
1698 --
1699 if l_balance is null then l_latest_value_exists := 'F';
1700 else
1701 --check just-retrieved action has a lower action sequence than
1702 --original assignment action
1703 if sequence(p_assignment_action_id) >
1704 sequence(l_assignment_action_id) then
1705 -- Action from past, so usable.
1706 l_latest_value_exists := 'T';
1707 --
1708 open original_month_end_date(p_assignment_action_id);
1709 FETCH original_month_end_date INTO l_original_month_end_date;
1710 close original_month_end_date;
1711 --
1712 open owning_month_end_date(l_assignment_action_id);
1713 FETCH owning_month_end_date INTO l_owning_month_end_date;
1714 close owning_month_end_date;
1715 -- has the balance expired?
1716 if l_owning_month_end_date < l_original_month_end_date then
1717 l_balance := 0;
1718 -- else the balance remains the same from c1.
1719 end if;
1720 else
1721 -- The action is from the future.
1722 -- Is the action the 'expired action' held on the table
1723 l_expired_balance := check_expired_action(l_defined_bal_id,
1724 p_assignment_id,
1725 p_assignment_action_id);
1726 if l_expired_balance is not null then
1727 --we have matched the expired action with the current action,
1728 --therefore we can assign the expired value as the current value.
1729 l_balance := l_expired_balance;
1730 l_latest_value_exists := 'T';
1731 end if;
1732 end if;
1733 end if;
1734 --
1735 end if;
1736 --
1737 -- If the balance is STILL not found,
1738 --
1739 if l_latest_value_exists = 'F' then
1740 --
1741 l_balance := py_za_routes.ASG_TAX_MTD(p_assignment_action_id,
1742 p_balance_type_id);
1743 end if;
1744 --
1745 else l_balance := null;
1746 end if;
1747 --
1748 RETURN l_balance;
1749 --
1750 END calc_ASG_TAX_MTD;
1751 --
1752 -----------------------------------------------------------------------------
1753 ---
1754 --
1755 -- GET_QUARTERS (private)
1756 --
1757 -- This is a local procedure to establish the Tax Quarters for the
1758 -- latest assignment action and the effective date. This is used by the
1759 -- CALC_ASG_TAX_QTD_DATE and CALC_ASG_TAX_QTD functions.
1760 --
1761 -----------------------------------------------------------------------------
1762 --
1763 PROCEDURE get_quarters(
1764 p_latest_action_id IN NUMBER,
1765 p_latest_action_quarter OUT NOCOPY NUMBER,
1766 p_eff_date_for_quarter IN DATE DEFAULT NULL,
1767 p_effective_date_quarter OUT NOCOPY NUMBER,
1768 p_orig_action_id IN NUMBER DEFAULT NULL,
1769 p_orig_action_quarter OUT NOCOPY NUMBER)
1770 IS
1771 --
1772 l_latest_action_quarter NUMBER;
1773 l_payroll_id NUMBER;
1774 l_effective_date_quarter NUMBER;
1775 l_orig_action_quarter NUMBER;
1776 --
1777 cursor latest_action_quarter (c_assignment_action_id IN NUMBER) is
1778 SELECT ptp.prd_information2, ppa.payroll_id
1779 FROM per_time_periods ptp,
1780 pay_payroll_actions ppa,
1781 pay_assignment_actions paa
1782 WHERE
1783 paa.assignment_action_id = c_assignment_action_id
1784 AND paa.payroll_action_id = ppa.payroll_action_id
1785 AND ppa.time_period_id = ptp.time_period_id;
1786 --
1787 cursor effective_date_quarter (c_effective_date IN DATE, c_payroll_id IN NUMBER) is
1788 SELECT ptp.prd_information2
1789 FROM per_time_periods ptp
1790 WHERE
1791 ptp.start_date <= c_effective_date
1792 AND ptp.end_date >= c_effective_date
1793 AND ptp.payroll_id = c_payroll_id;
1794 --
1795 cursor orig_action_quarter (c_assignment_action_id IN NUMBER) is
1796 SELECT ptp.prd_information2
1797 FROM per_time_periods ptp,
1798 pay_payroll_actions ppa,
1799 pay_assignment_actions paa
1800 WHERE
1801 paa.assignment_action_id = c_assignment_action_id
1802 AND paa.payroll_action_id = ppa.payroll_action_id
1803 AND ppa.time_period_id = ptp.time_period_id;
1804 --
1805 BEGIN
1806 --
1807 if p_eff_date_for_quarter is not null then
1808 open latest_action_quarter(p_latest_action_id);
1809 FETCH latest_action_quarter INTO l_latest_action_quarter, l_payroll_id;
1810 close latest_action_quarter;
1811 --
1812 open effective_date_quarter(p_eff_date_for_quarter, l_payroll_id);
1813 FETCH effective_date_quarter INTO l_effective_date_quarter;
1814 close effective_date_quarter;
1815 --
1816 p_latest_action_quarter := l_latest_action_quarter;
1817 p_effective_date_quarter := l_effective_date_quarter;
1818 else
1819 open latest_action_quarter(p_latest_action_id);
1820 FETCH latest_action_quarter INTO l_latest_action_quarter, l_payroll_id;
1821 close latest_action_quarter;
1822 --
1823 open orig_action_quarter(p_orig_action_id);
1824 FETCH orig_action_quarter INTO l_orig_action_quarter;
1825 close orig_action_quarter;
1826 --
1827 p_latest_action_quarter := l_latest_action_quarter;
1828 p_orig_action_quarter := l_orig_action_quarter;
1829 end if;
1830 --
1831 exception
1832 when others then
1833 p_latest_action_quarter := null;
1834 p_orig_action_quarter := null;
1835 p_effective_date_quarter := null;
1836 end get_quarters;
1837 -----------------------------------------------------------------------------
1838 --
1839 --
1840 -- CALC_ASG_TAX_QTD_ACTION
1841 --
1842 -- This is the function for calculating assignment
1843 -- tax Quarter to date in assignment action mode
1844 --
1845 -----------------------------------------------------------------------------
1846 --
1847 FUNCTION calc_ASG_TAX_QTD_action(
1848 p_assignment_action_id IN NUMBER,
1849 p_balance_type_id IN NUMBER,
1850 p_effective_date IN DATE)
1851 RETURN NUMBER
1852 IS
1853 --
1854 l_assignment_action_id NUMBER;
1855 l_balance NUMBER;
1856 l_assignment_id NUMBER;
1857 l_effective_date DATE;
1858 --
1859 BEGIN
1860 --
1861 l_assignment_id := get_correct_type(p_assignment_action_id);
1862 IF l_assignment_id is null THEN
1863 --
1864 -- The assignment action is not a payroll or quickpay type, so return null
1865 --
1866 l_balance := null;
1867 ELSE
1868 --
1869 l_balance := calc_ASG_TAX_QTD(
1870 p_assignment_action_id => p_assignment_action_id,
1871 p_balance_type_id => p_balance_type_id,
1872 p_effective_date => p_effective_date,
1873 p_assignment_id => l_assignment_id);
1874 END IF;
1875 --
1876 RETURN l_balance;
1877 end calc_ASG_TAX_QTD_action;
1878 --
1879 -----------------------------------------------------------------------------
1880 ---
1881 --
1882 -- CALC_ASG_TAX_QTD_DATE
1883 --
1884 -- This is the function for calculating assignment tax
1885 -- Quarter to date in date mode
1886 --
1887 -----------------------------------------------------------------------------
1888 --
1889 FUNCTION calc_ASG_TAX_QTD_date(
1890 p_assignment_id IN NUMBER,
1891 p_balance_type_id IN NUMBER,
1892 p_effective_date IN DATE)
1893 RETURN NUMBER
1894 IS
1895 --
1896 l_assignment_action_id NUMBER;
1897 l_balance NUMBER;
1898 l_latest_action_quarter NUMBER;
1899 l_payroll_id NUMBER;
1900 l_effective_date_quarter NUMBER;
1901 l_orig_action_quarter NUMBER;
1902 --
1903 BEGIN
1904 --
1905 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1906 p_effective_date);
1907 IF l_assignment_action_id is null THEN
1908 l_balance := 0;
1909 ELSE
1910 --
1911 --Establish in which tax quarters the latest assignment action and the
1912 --effective date fall
1913 get_quarters(p_latest_action_id => l_assignment_action_id,
1914 p_latest_action_quarter => l_latest_action_quarter,
1915 p_eff_date_for_quarter => p_effective_date,
1916 p_effective_date_quarter => l_effective_date_quarter,
1917 p_orig_action_quarter => l_orig_action_quarter);
1918 --
1919 if l_latest_action_quarter <> l_effective_date_quarter then
1920 l_balance := 0;
1921 else
1922 l_balance := calc_ASG_TAX_QTD(
1923 p_assignment_action_id => l_assignment_action_id,
1924 p_balance_type_id => p_balance_type_id,
1925 p_effective_date => p_effective_date,
1926 p_assignment_id => p_assignment_id);
1927 end if;
1928 END IF;
1929 --
1930 RETURN l_balance;
1931 end calc_ASG_TAX_QTD_date;
1932 --
1933 --------------------------------------------------------------------------------
1934 --
1935 -- CALC_ASG_TAX_QTD
1936 --
1937 -- calculate Assignment tax Quarter to date
1938 --
1939 --------------------------------------------------------------------------------
1940 --
1941 --
1942 -- This dimension is the tax Quarter total for an assignment within
1943 -- its current payroll.
1944 --
1945 FUNCTION calc_ASG_TAX_QTD(
1946 p_assignment_action_id IN NUMBER,
1947 p_balance_type_id IN NUMBER,
1948 p_effective_date IN DATE DEFAULT NULL,
1949 p_assignment_id IN NUMBER
1950 )
1951 RETURN NUMBER
1952 IS
1953 --
1954 --
1955 l_expired_balance NUMBER;
1956 l_balance NUMBER;
1957 l_latest_value_exists VARCHAR2(2);
1958 l_assignment_action_id NUMBER;
1959 l_action_eff_date DATE;
1960 l_end_date DATE;
1961 l_defined_bal_id NUMBER;
1962 l_latest_action_quarter NUMBER;
1963 l_orig_action_quarter NUMBER;
1964 l_effective_date_quarter NUMBER;
1965 --
1966 --
1967 BEGIN
1968 --
1969 --Do we need to work out a value for this dimension/balance combination.
1970 --
1971 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TAX_QTD');
1972 if l_defined_bal_id is not null then
1973 --
1974 -- Is there a value in the latest balances table ..
1975 --
1976 l_balance := get_latest_balance(p_assignment_action_id,
1977 l_defined_bal_id);
1978 --
1979 if l_balance is null then l_latest_value_exists := 'F';
1980 else l_latest_value_exists := 'T';
1981 end if;
1982 --
1983 if l_latest_value_exists = 'F' then
1984 --
1985 --c1. See if there is a latest balance value for the assignment/defined balance
1986 get_owning_balance(p_assignment_id => p_assignment_id,
1987 p_defined_balance_id => l_defined_bal_id,
1988 p_assignment_action_id => l_assignment_action_id,
1989 p_value => l_balance);
1990 --
1991 if l_balance is null then l_latest_value_exists := 'F';
1992 else
1993 --check just-retrieved action has a lower action sequence than
1994 --original assignment action
1995 if sequence(p_assignment_action_id) >
1996 sequence(l_assignment_action_id) then
1997 --Action from past, so usable.
1998 l_latest_value_exists := 'T';
1999 --Establish in which tax quarters the latest- and the original
2000 --assignment actions fall
2001 get_quarters(
2002 p_latest_action_id => l_assignment_action_id,
2003 p_latest_action_quarter => l_latest_action_quarter,
2004 p_orig_action_id => p_assignment_action_id,
2005 p_orig_action_quarter => l_orig_action_quarter,
2006 p_effective_date_quarter => l_effective_date_quarter);
2007 --
2008 -- has the balance expired?
2009 if l_latest_action_quarter <> l_orig_action_quarter then
2010 --expired, can't use balance
2011 l_balance := 0;
2012 -- else the balance remains the same from c1.
2013 end if;
2014 else
2015 -- The action is from the future.
2016 -- Is the action the 'expired action' held on the table
2017 l_expired_balance := check_expired_action(l_defined_bal_id,
2018 p_assignment_id,
2019 p_assignment_action_id);
2020 if l_expired_balance is not null then
2021 --we have matched the expired action with the current action,
2022 --therefore we can assign the expired value as the current value.
2023 l_balance := l_expired_balance;
2024 l_latest_value_exists := 'T';
2025 end if;
2026 end if;
2027 end if;
2028 --
2029 end if;
2030 --
2031 -- If the balance is STILL not found,
2032 --
2033 if l_latest_value_exists = 'F' then
2034 --
2035 l_balance := py_za_routes.ASG_TAX_QTD(p_assignment_action_id,
2036 p_balance_type_id);
2037 end if;
2038 --
2039 else l_balance := null;
2040 end if;
2041 --
2042 RETURN l_balance;
2043 --
2044 END calc_ASG_TAX_QTD;
2045 --
2046 -----------------------------------------------------------------------------
2047 ---
2048 --
2049 -- CALC_ASG_TAX_YTD_ACTION
2050 --
2051 -- This is the function for calculating assignment tax year to
2052 -- date in asg action mode
2053 --
2054 -----------------------------------------------------------------------------
2055 --
2056 FUNCTION calc_asg_tax_ytd_action(
2057 p_assignment_action_id IN NUMBER,
2058 p_balance_type_id IN NUMBER,
2059 p_effective_date IN DATE)
2060 RETURN NUMBER
2061 IS
2062 --
2063 l_assignment_action_id NUMBER;
2064 l_balance NUMBER;
2065 l_assignment_id NUMBER;
2066 l_effective_date DATE;
2067 --
2068 BEGIN
2069 --
2070 l_assignment_id := get_correct_type(p_assignment_action_id);
2071 IF l_assignment_id is null THEN
2072 --
2073 -- The assignment action is not a payroll or quickpay type, so return null
2074 --
2075 l_balance := null;
2076 --
2077 ELSE
2078 --
2079 l_balance := calc_asg_tax_ytd(
2080 p_assignment_action_id => p_assignment_action_id,
2081 p_balance_type_id => p_balance_type_id,
2082 p_effective_date => p_effective_date,
2083 p_assignment_id => l_assignment_id);
2084 END IF;
2085 --
2086 RETURN l_balance;
2087 end calc_asg_tax_ytd_action;
2088 --
2089 -----------------------------------------------------------------------------
2090 ---
2091 --
2092 -- CALC_ASG_TAX_YTD_DATE -
2093 --
2094 -- This is the function for calculating assignment tax year to
2095 -- date in date mode
2096 --
2097 -----------------------------------------------------------------------------
2098 --
2099 FUNCTION calc_asg_tax_ytd_date(
2100 p_assignment_id IN NUMBER,
2101 p_balance_type_id IN NUMBER,
2102 p_effective_date IN DATE)
2103 RETURN NUMBER
2104 IS
2105 --
2106 l_assignment_action_id NUMBER;
2107 l_balance NUMBER;
2108 l_end_date DATE;
2109 --
2110 BEGIN
2111 --
2112 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2113 p_effective_date);
2114 IF l_assignment_action_id is null THEN
2115 l_balance := 0;
2116 ELSE
2117 --
2118 -- Is effective date (sess) later than the expiry of the tax year of the
2119 -- assignment action.
2120 --
2121 if p_effective_date > get_expired_year_date(l_assignment_action_id) then
2122 l_balance := 0;
2123 else
2124 --
2125 l_balance := calc_asg_tax_ytd(
2126 p_assignment_action_id => l_assignment_action_id,
2127 p_balance_type_id => p_balance_type_id,
2128 p_effective_date => p_effective_date,
2129 p_assignment_id => p_assignment_id);
2130 end if;
2131 END IF;
2132 --
2133 RETURN l_balance;
2134 end calc_asg_tax_ytd_date;
2135 --
2136 --------------------------------------------------------------------------------
2137 --
2138 -- CALC_ASG_TAX_YTD --
2139 -- calculate balances for Assignment tax year to date
2140 --
2141 --------------------------------------------------------------------------------
2142 --
2143 -- This dimension is the total for an assignment within the tax
2144 -- year of any payrolls he has been on this year
2145 --
2146 FUNCTION calc_asg_tax_ytd(
2147 p_assignment_action_id IN NUMBER,
2148 p_balance_type_id IN NUMBER,
2149 p_effective_date IN DATE DEFAULT NULL,
2150 p_assignment_id IN NUMBER
2151 )
2152 RETURN NUMBER
2153 IS
2154 --
2155 l_expired_balance NUMBER;
2156 l_balance NUMBER;
2157 l_session_date DATE;
2158 l_assignment_id NUMBER;
2159 l_action_eff_date DATE;
2160 l_latest_value_exists VARCHAR2(2);
2161 l_assignment_action_id NUMBER;
2162 l_defined_bal_id NUMBER;
2163 --
2164 BEGIN
2165 --
2166 --Do we need to work out a value for this dimension/balance combination.
2167 --
2168 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TAX_YTD');
2169 if l_defined_bal_id is not null then
2170 --
2171 l_balance := get_latest_balance(p_assignment_action_id,
2172 l_defined_bal_id);
2173 --
2174 if l_balance is null then l_latest_value_exists := 'F';
2175 else l_latest_value_exists := 'T';
2176 end if;
2177 --
2178 if l_latest_value_exists = 'F' then
2179 --
2180 --c1. See if there is a balance value for the assignment/defined balance
2181 get_owning_balance(p_assignment_id => p_assignment_id,
2182 p_defined_balance_id => l_defined_bal_id,
2183 p_assignment_action_id => l_assignment_action_id,
2184 p_value => l_balance);
2185 --
2186 if l_balance is null then l_latest_value_exists := 'F';
2187 else
2188 --check just-retrieved action has a lower action sequence than
2189 --original assignment action
2190 if sequence(p_assignment_action_id) >
2191 sequence(l_assignment_action_id) then
2192 -- Action from past, so usable.
2193 l_latest_value_exists := 'T';
2194 --we have to validate
2195 if nvl(p_effective_date,get_latest_date(p_assignment_action_id))
2196 > get_expired_year_date(l_assignment_action_id) then
2197 l_balance := 0;
2198 -- else the balance remains the same from c1 fetch.
2199 end if;
2200 else
2201 -- The action is from the future.
2202 -- Is the action the 'expired action' held on the table
2203 l_expired_balance := check_expired_action(l_defined_bal_id,
2204 p_assignment_id,
2205 p_assignment_action_id);
2206 if l_expired_balance is not null then
2207 --we have matched the expired action with the current action,
2208 --therefore we can assign the expired value as the current value.
2209 l_balance := l_expired_balance;
2210 l_latest_value_exists := 'T';
2211 end if;
2212 end if;
2213 end if;
2214 --
2215 end if;
2216 --
2217 -- If the balance is STILL not found,
2218 --
2219 if l_latest_value_exists = 'F' then
2220 --
2221 -- Use parameter assignment action id to calculate route code
2222 --
2223 l_balance := py_za_routes.asg_tax_ytd(p_assignment_action_id,
2224 p_balance_type_id);
2225 --
2226 end if;
2227 --
2228 else l_balance := null;
2229 end if;
2230 --
2231 RETURN l_balance;
2232 --
2233 END calc_asg_tax_ytd;
2234 --
2235 -----------------------------------------------------------------------------
2236 ---
2237 --
2238 -- CALC_ASG_CAL_PTD_ACTION
2239 --
2240 -- This is the function for calculating assignment
2241 -- Calendar period to date in assignment action mode
2242 --
2243 -----------------------------------------------------------------------------
2244 --
2245 FUNCTION calc_ASG_CAL_PTD_action(
2246 p_assignment_action_id IN NUMBER,
2247 p_balance_type_id IN NUMBER,
2248 p_effective_date IN DATE)
2249 RETURN NUMBER
2250 IS
2251 --
2252 l_assignment_action_id NUMBER;
2253 l_balance NUMBER;
2254 l_assignment_id NUMBER;
2255 l_effective_date DATE;
2256 --
2257 BEGIN
2258 --
2259 l_assignment_id := get_correct_type(p_assignment_action_id);
2260 IF l_assignment_id is null THEN
2261 --
2262 -- The assignment action is not a payroll or quickpay type, so return null
2263 --
2264 l_balance := null;
2265 ELSE
2266 --
2267 l_balance := calc_ASG_CAL_PTD(
2268 p_assignment_action_id => p_assignment_action_id,
2269 p_balance_type_id => p_balance_type_id,
2270 p_effective_date => p_effective_date,
2271 p_assignment_id => l_assignment_id);
2272 END IF;
2273 --
2274 RETURN l_balance;
2275 end calc_ASG_CAL_PTD_action;
2276 --
2277 -----------------------------------------------------------------------------
2278 ---
2279 --
2280 -- CALC_ASG_CAL_PTD_DATE
2281 --
2282 -- This is the function for calculating assignment Calendar
2283 -- period to date in date mode
2284 --
2285 -----------------------------------------------------------------------------
2286 --
2287 FUNCTION calc_ASG_CAL_PTD_date(
2288 p_assignment_id IN NUMBER,
2289 p_balance_type_id IN NUMBER,
2290 p_effective_date IN DATE)
2291 RETURN NUMBER
2292 IS
2293 --
2294 l_assignment_action_id NUMBER;
2295 l_balance NUMBER;
2296 l_end_date DATE;
2297 --
2298 -- Has the processing time period expired
2299 --
2300 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2301 SELECT ptp.end_date
2302 FROM per_time_periods ptp,
2303 pay_payroll_actions ppa,
2304 pay_assignment_actions paa
2305 WHERE
2306 paa.assignment_action_id = c_assignment_action_id
2307 AND paa.payroll_action_id = ppa.payroll_action_id
2308 AND ppa.time_period_id = ptp.time_period_id;
2309 --
2310 BEGIN
2311 --
2312 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2313 p_effective_date);
2314 IF l_assignment_action_id is null THEN
2315 l_balance := 0;
2316 ELSE
2317 open expired_time_period(l_assignment_action_id);
2318 FETCH expired_time_period INTO l_end_date;
2319 close expired_time_period;
2320 --
2321 if l_end_date < p_effective_date then
2322 l_balance := 0;
2323 else
2324 l_balance := calc_ASG_CAL_PTD(
2325 p_assignment_action_id => l_assignment_action_id,
2326 p_balance_type_id => p_balance_type_id,
2327 p_effective_date => p_effective_date,
2328 p_assignment_id => p_assignment_id);
2329 end if;
2330 END IF;
2331 --
2332 RETURN l_balance;
2333 end calc_ASG_CAL_PTD_date;
2334 --
2335 --------------------------------------------------------------------------------
2336 --
2337 -- CALC_ASG_CAL_PTD
2338 --
2339 -- calculate Assignment Calendar period to date
2340 --
2341 --------------------------------------------------------------------------------
2342 --
2343 --
2344 -- This dimension is the total for an assignment within the processing
2345 -- period of its current payroll.
2346 --
2347 FUNCTION calc_ASG_CAL_PTD(
2348 p_assignment_action_id IN NUMBER,
2349 p_balance_type_id IN NUMBER,
2350 p_effective_date IN DATE DEFAULT NULL,
2351 p_assignment_id IN NUMBER
2352 )
2353 RETURN NUMBER
2354 IS
2355 --
2356 --
2357 l_expired_balance NUMBER;
2358 l_balance NUMBER;
2359 l_latest_value_exists VARCHAR2(2);
2360 l_assignment_action_id NUMBER;
2361 l_action_eff_date DATE;
2362 l_end_date DATE;
2363 l_defined_bal_id NUMBER;
2364 --
2365 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2366 SELECT ptp.end_date
2367 FROM per_time_periods ptp,
2368 pay_payroll_actions ppa,
2369 pay_assignment_actions paa
2370 WHERE
2371 paa.assignment_action_id = c_assignment_action_id
2372 AND paa.payroll_action_id = ppa.payroll_action_id
2373 AND ppa.time_period_id = ptp.time_period_id;
2374 --
2375 BEGIN
2376 --
2377 --Do we need to work out a value for this dimension/balance combination.
2378 --
2379 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_CAL_PTD');
2380 if l_defined_bal_id is not null then
2381 --
2382 -- Is there a value in the latest balances table ..
2383 --
2384 l_balance := get_latest_balance(p_assignment_action_id,
2385 l_defined_bal_id);
2386 --
2387 if l_balance is null then l_latest_value_exists := 'F';
2388 else l_latest_value_exists := 'T';
2389 end if;
2390 --
2391 if l_latest_value_exists = 'F' then
2392 --
2393 --c1. See if there is a balance value for the assignment/defined balance
2394 get_owning_balance(p_assignment_id => p_assignment_id,
2395 p_defined_balance_id => l_defined_bal_id,
2396 p_assignment_action_id => l_assignment_action_id,
2397 p_value => l_balance);
2398 --
2399 if l_balance is null then l_latest_value_exists := 'F';
2400 else
2401 --check just-retrieved action has a lower action sequence than
2402 --original assignment action
2403 if sequence(p_assignment_action_id) >
2404 sequence(l_assignment_action_id) then
2405 -- Action from past, so usable.
2406 l_latest_value_exists := 'T';
2407 l_action_eff_date := get_latest_date(p_assignment_action_id);
2408 open expired_time_period(l_assignment_action_id);
2409 FETCH expired_time_period INTO l_end_date;
2410 close expired_time_period;
2411 -- has the balance expired?
2412 if l_end_date < l_action_eff_date then
2413 l_balance := 0;
2414 -- else the balance remains the same from c1.
2415 end if;
2416 else
2417 -- The action is from the future.
2418 -- Is the action the 'expired action' held on the table
2419 l_expired_balance := check_expired_action(l_defined_bal_id,
2420 p_assignment_id,
2421 p_assignment_action_id);
2422 if l_expired_balance is not null then
2423 --we have matched the expired action with the current action,
2424 --therefore we can assign the expired value as the current value.
2425 l_balance := l_expired_balance;
2426 l_latest_value_exists := 'T';
2427 end if;
2428 end if;
2429 end if;
2430 --
2431 end if;
2432 --
2433 -- If the balance is STILL not found,
2434 --
2435 if l_latest_value_exists = 'F' then
2436 --
2437 l_balance := py_za_routes.ASG_CAL_PTD(p_assignment_action_id,
2438 p_balance_type_id);
2439 end if;
2440 --
2441 else l_balance := null;
2442 end if;
2443 --
2444 RETURN l_balance;
2445 --
2446 END calc_ASG_CAL_PTD;
2447 --
2448 -----------------------------------------------------------------------------
2449 ---
2450 --
2451 -- CALC_ASG_CAL_MTD_ACTION
2452 --
2453 -- This is the function for calculating assignment
2454 -- Calendar month to date in assignment action mode
2455 --
2456 -----------------------------------------------------------------------------
2457 --
2458 FUNCTION calc_ASG_CAL_MTD_action(
2459 p_assignment_action_id IN NUMBER,
2460 p_balance_type_id IN NUMBER,
2461 p_effective_date IN DATE)
2462 RETURN NUMBER
2463 IS
2464 --
2465 l_assignment_action_id NUMBER;
2466 l_balance NUMBER;
2467 l_assignment_id NUMBER;
2468 l_effective_date DATE;
2469 --
2470 BEGIN
2471 --
2472 l_assignment_id := get_correct_type(p_assignment_action_id);
2473 IF l_assignment_id is null THEN
2474 --
2475 -- The assignment action is not a payroll or quickpay type, so return null
2476 --
2477 l_balance := null;
2478 ELSE
2479 --
2480 l_balance := calc_ASG_CAL_MTD(
2481 p_assignment_action_id => p_assignment_action_id,
2482 p_balance_type_id => p_balance_type_id,
2483 p_effective_date => p_effective_date,
2484 p_assignment_id => l_assignment_id);
2485 END IF;
2486 --
2487 RETURN l_balance;
2488 end calc_ASG_CAL_MTD_action;
2489 --
2490 -----------------------------------------------------------------------------
2491 ---
2492 --
2493 -- CALC_ASG_CAL_MTD_DATE
2494 --
2495 -- This is the function for calculating assignment Calendar
2496 -- month to date in date mode
2497 --
2498 -----------------------------------------------------------------------------
2499 --
2500 FUNCTION calc_ASG_CAL_MTD_date(
2501 p_assignment_id IN NUMBER,
2502 p_balance_type_id IN NUMBER,
2503 p_effective_date IN DATE)
2504 RETURN NUMBER
2505 IS
2506 --
2507 l_assignment_action_id NUMBER;
2508 l_balance NUMBER;
2509 l_end_date DATE;
2510 --
2511 -- Has the processing time period expired (Month end date)
2512 --
2513 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2514 SELECT ptp.pay_advice_date
2515 FROM per_time_periods ptp,
2516 pay_payroll_actions ppa,
2517 pay_assignment_actions paa
2518 WHERE
2519 paa.assignment_action_id = c_assignment_action_id
2520 AND paa.payroll_action_id = ppa.payroll_action_id
2521 AND ppa.time_period_id = ptp.time_period_id;
2522 --
2523 BEGIN
2524 --
2525 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2526 p_effective_date);
2527 IF l_assignment_action_id is null THEN
2528 l_balance := 0;
2529 ELSE
2530 open expired_time_period(l_assignment_action_id);
2531 FETCH expired_time_period INTO l_end_date;
2532 close expired_time_period;
2533 --
2534 if l_end_date < p_effective_date then
2535 l_balance := 0;
2536 else
2537 l_balance := calc_ASG_CAL_MTD(
2538 p_assignment_action_id => l_assignment_action_id,
2539 p_balance_type_id => p_balance_type_id,
2540 p_effective_date => p_effective_date,
2541 p_assignment_id => p_assignment_id);
2542 end if;
2543 END IF;
2544 --
2545 RETURN l_balance;
2546 end calc_ASG_CAL_MTD_date;
2547 --
2548 --------------------------------------------------------------------------------
2549 --
2550 -- CALC_ASG_CAL_MTD
2551 --
2552 -- calculate Assignment Calendar month to date
2553 --
2554 --------------------------------------------------------------------------------
2555 --
2556 --
2557 -- This dimension is the Calendar month total for an assignment within
2558 -- its current payroll.
2559 --
2560 FUNCTION calc_ASG_CAL_MTD(
2561 p_assignment_action_id IN NUMBER,
2562 p_balance_type_id IN NUMBER,
2563 p_effective_date IN DATE DEFAULT NULL,
2564 p_assignment_id IN NUMBER
2565 )
2566 RETURN NUMBER
2567 IS
2568 --
2569 --
2570 l_expired_balance NUMBER;
2571 l_balance NUMBER;
2572 l_latest_value_exists VARCHAR2(2);
2573 l_assignment_action_id NUMBER;
2574 l_action_eff_date DATE;
2575 l_owning_month_end_date DATE;
2576 l_defined_bal_id NUMBER;
2577 l_original_month_end_date DATE;
2578 --
2579 -- Has the processing time period expired (Month end date)
2580 --
2581 cursor owning_month_end_date (c_assignment_action_id IN NUMBER) is
2582 SELECT ptp.pay_advice_date
2583 FROM per_time_periods ptp,
2584 pay_payroll_actions ppa,
2585 pay_assignment_actions paa
2586 WHERE
2587 paa.assignment_action_id = c_assignment_action_id
2588 AND paa.payroll_action_id = ppa.payroll_action_id
2589 AND ppa.time_period_id = ptp.time_period_id;
2590 --
2591 cursor original_month_end_date (c_assignment_action_id IN NUMBER) is
2592 SELECT ptp.pay_advice_date
2593 FROM per_time_periods ptp,
2594 pay_payroll_actions ppa,
2595 pay_assignment_actions paa
2596 WHERE
2597 paa.assignment_action_id = c_assignment_action_id
2598 AND paa.payroll_action_id = ppa.payroll_action_id
2599 AND ppa.time_period_id = ptp.time_period_id;
2600 --
2601 BEGIN
2602 --
2603 --Do we need to work out a value for this dimension/balance combination.
2604 --
2605 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_CAL_MTD');
2606 if l_defined_bal_id is not null then
2607 --
2608 -- Is there a value in the latest balances table ..
2609 --
2610 l_balance := get_latest_balance(p_assignment_action_id,
2611 l_defined_bal_id);
2612 --
2613 if l_balance is null then l_latest_value_exists := 'F';
2614 else l_latest_value_exists := 'T';
2615 end if;
2616 --
2617 if l_latest_value_exists = 'F' then
2618 --
2619 --c1. See if there is a balance value for the assignment/defined balance
2620 get_owning_balance(p_assignment_id => p_assignment_id,
2621 p_defined_balance_id => l_defined_bal_id,
2622 p_assignment_action_id => l_assignment_action_id,
2623 p_value => l_balance);
2624 --
2625 if l_balance is null then l_latest_value_exists := 'F';
2626 else
2627 --check just-retrieved action has a lower action sequence than
2628 --original assignment action
2629 if sequence(p_assignment_action_id) >
2630 sequence(l_assignment_action_id) then
2631 -- Action from past, so usable.
2632 l_latest_value_exists := 'T';
2633 --
2634 open original_month_end_date(p_assignment_action_id);
2635 FETCH original_month_end_date INTO l_original_month_end_date;
2636 close original_month_end_date;
2637 --
2638 open owning_month_end_date(l_assignment_action_id);
2639 FETCH owning_month_end_date INTO l_owning_month_end_date;
2640 close owning_month_end_date;
2641 -- has the balance expired?
2642 if l_owning_month_end_date < l_original_month_end_date then
2643 l_balance := 0;
2644 -- else the balance remains the same from c1.
2645 end if;
2646 else
2647 -- The action is from the future.
2648 -- Is the action the 'expired action' held on the table
2649 l_expired_balance := check_expired_action(l_defined_bal_id,
2650 p_assignment_id,
2651 p_assignment_action_id);
2652 if l_expired_balance is not null then
2653 --we have matched the expired action with the current action,
2654 --therefore we can assign the expired value as the current value.
2655 l_balance := l_expired_balance;
2656 l_latest_value_exists := 'T';
2657 end if;
2658 end if;
2659 end if;
2660 --
2661 end if;
2662 --
2663 -- If the balance is STILL not found,
2664 --
2665 if l_latest_value_exists = 'F' then
2666 --
2667 l_balance := py_za_routes.ASG_CAL_MTD(p_assignment_action_id,
2668 p_balance_type_id);
2669 end if;
2670 --
2671 else l_balance := null;
2672 end if;
2673 --
2674 RETURN l_balance;
2675 --
2676 END calc_ASG_CAL_MTD;
2677 --
2678 -----------------------------------------------------------------------------
2679 ---
2680 --
2681 -- CALC_ASG_CAL_YTD_ACTION
2682 --
2683 -- This is the function for calculating assignment Calendar year to
2684 -- date in asg action mode
2685 --
2686 -----------------------------------------------------------------------------
2687 --
2688 FUNCTION calc_asg_cal_ytd_action(
2689 p_assignment_action_id IN NUMBER,
2690 p_balance_type_id IN NUMBER,
2691 p_effective_date IN DATE)
2692 RETURN NUMBER
2693 IS
2694 --
2695 l_assignment_action_id NUMBER;
2696 l_balance NUMBER;
2697 l_assignment_id NUMBER;
2698 l_effective_date DATE;
2699 --
2700 BEGIN
2701 --
2702 l_assignment_id := get_correct_type(p_assignment_action_id);
2703 IF l_assignment_id is null THEN
2704 --
2705 -- The assignment action is not a payroll or quickpay type, so return null
2706 --
2707 l_balance := null;
2708 --
2709 ELSE
2710 --
2711 l_balance := calc_asg_cal_ytd(
2712 p_assignment_action_id => p_assignment_action_id,
2713 p_balance_type_id => p_balance_type_id,
2714 p_effective_date => p_effective_date,
2715 p_assignment_id => l_assignment_id);
2716 END IF;
2717 --
2718 RETURN l_balance;
2719 end calc_asg_cal_ytd_action;
2720 --
2721 -----------------------------------------------------------------------------
2722 ---
2723 --
2724 -- CALC_ASG_CAL_YTD_DATE -
2725 --
2726 -- This is the function for calculating assignment Calendar year to
2727 -- date in date mode
2728 --
2729 -----------------------------------------------------------------------------
2730 --
2731 FUNCTION calc_asg_cal_ytd_date(
2732 p_assignment_id IN NUMBER,
2733 p_balance_type_id IN NUMBER,
2734 p_effective_date IN DATE)
2735 RETURN NUMBER
2736 IS
2737 --
2738 l_assignment_action_id NUMBER;
2739 l_balance NUMBER;
2740 l_end_date DATE;
2741 l_calendar_year NUMBER;
2742 --
2743 cursor calendar_year (c_assignment_action_id IN NUMBER) is
2744 SELECT to_number(ptp.prd_information3)
2745 FROM per_time_periods ptp,
2746 pay_payroll_actions ppa,
2747 pay_assignment_actions paa
2748 WHERE
2749 paa.assignment_action_id = c_assignment_action_id
2750 AND paa.payroll_action_id = ppa.payroll_action_id
2751 AND ppa.time_period_id = ptp.time_period_id;
2752 --
2753 BEGIN
2754 --
2755 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2756 p_effective_date);
2757 IF l_assignment_action_id is null THEN
2758 l_balance := 0;
2759 ELSE
2760 --
2761 -- Is effective date later than the expiry of the Calendar year of the
2762 -- assignment action?
2763 --
2764 open calendar_year(l_assignment_action_id);
2765 fetch calendar_year into l_calendar_year;
2766 close calendar_year;
2767
2768 if to_number(to_char(p_effective_date, 'YYYY')) <> l_calendar_year then
2769 l_balance := 0;
2770 else
2771 --
2772 l_balance := calc_asg_cal_ytd(
2773 p_assignment_action_id => l_assignment_action_id,
2774 p_balance_type_id => p_balance_type_id,
2775 p_effective_date => p_effective_date,
2776 p_assignment_id => p_assignment_id);
2777 end if;
2778 END IF;
2779 --
2780 RETURN l_balance;
2781 end calc_asg_cal_ytd_date;
2782 --
2783 --------------------------------------------------------------------------------
2784 --
2785 -- CALC_ASG_CAL_YTD --
2786 -- calculate balances for Assignment Calendar year to date
2787 --
2788 --------------------------------------------------------------------------------
2789 --
2790 -- This dimension is the total for an assignment within the Calendar
2791 -- year of any payrolls he has been on this year
2792 --
2793 FUNCTION calc_asg_cal_ytd(
2794 p_assignment_action_id IN NUMBER,
2795 p_balance_type_id IN NUMBER,
2796 p_effective_date IN DATE DEFAULT NULL,
2797 p_assignment_id IN NUMBER
2798 )
2799 RETURN NUMBER
2800 IS
2801 --
2802 l_expired_balance NUMBER;
2803 l_balance NUMBER;
2804 l_session_date DATE;
2805 l_assignment_id NUMBER;
2806 l_action_eff_date DATE;
2807 l_latest_value_exists VARCHAR2(2);
2808 l_assignment_action_id NUMBER;
2809 l_defined_bal_id NUMBER;
2810 l_original_cal_year NUMBER;
2811 l_owning_cal_year NUMBER;
2812 --
2813 cursor original_cal_year (c_assignment_action IN NUMBER) is
2814 select to_number(ptp.prd_information3)
2815 from per_time_periods ptp,
2816 pay_payroll_actions ppa,
2817 pay_assignment_actions paa
2818 where
2819 paa.assignment_action_id = c_assignment_action
2820 and ppa.payroll_action_id = paa.payroll_action_id
2821 and ptp.time_period_id = ppa.time_period_id;
2822 --
2823 cursor owning_cal_year (c_assignment_action IN NUMBER) is
2824 select to_number(ptp.prd_information3)
2825 from per_time_periods ptp,
2826 pay_payroll_actions ppa,
2827 pay_assignment_actions paa
2828 where
2829 paa.assignment_action_id = c_assignment_action
2830 and ppa.payroll_action_id = paa.payroll_action_id
2831 and ptp.time_period_id = ppa.time_period_id;
2832 --
2833 BEGIN
2834 --
2835 --Do we need to work out a value for this dimension/balance combination.
2836 --
2837 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_CAL_YTD');
2838 if l_defined_bal_id is not null then
2839 --
2840 l_balance := get_latest_balance(p_assignment_action_id,
2841 l_defined_bal_id);
2842 --
2843 if l_balance is null then l_latest_value_exists := 'F';
2844 else l_latest_value_exists := 'T';
2845 end if;
2846 --
2847 if l_latest_value_exists = 'F' then
2848 --
2849 --c1. See if there is a balance value for the assignment/defined balance
2850 get_owning_balance(p_assignment_id => p_assignment_id,
2851 p_defined_balance_id => l_defined_bal_id,
2852 p_assignment_action_id => l_assignment_action_id,
2853 p_value => l_balance);
2854 --
2855 if l_balance is null then l_latest_value_exists := 'F';
2856 else
2857 --check just-retrieved action has a lower action sequence than
2858 --original assignment action
2859 if sequence(p_assignment_action_id) >
2860 sequence(l_assignment_action_id) then
2861 -- Action from past, so usable.
2862 l_latest_value_exists := 'T';
2863 --Establish the Calendar year ends for the original and the
2864 --owning actions
2865 open original_cal_year(p_assignment_action_id);
2866 fetch original_cal_year into l_original_cal_year;
2867 close original_cal_year;
2868 --
2869 open owning_cal_year(l_assignment_action_id);
2870 fetch owning_cal_year into l_owning_cal_year;
2871 close owning_cal_year;
2872 --we have to validate
2873 if l_original_cal_year <> l_owning_cal_year then
2874 l_balance := 0;
2875 -- else the balance remains the same from c1 fetch.
2876 end if;
2877 else
2878 -- The action is from the future.
2879 -- Is the action the 'expired action' held on the table
2880 l_expired_balance := check_expired_action(l_defined_bal_id,
2881 p_assignment_id,
2882 p_assignment_action_id);
2883 if l_expired_balance is not null then
2884 --we have matched the expired action with the current action,
2885 --therefore we can assign the expired value as the current value.
2886 l_balance := l_expired_balance;
2887 l_latest_value_exists := 'T';
2888 end if;
2889 end if;
2890 end if;
2891 --
2892 end if;
2893 --
2894 -- If the balance is STILL not found,
2895 --
2896 if l_latest_value_exists = 'F' then
2897 --
2898 -- Use parameter assignment action id to calculate route code
2899 --
2900 l_balance := py_za_routes.asg_cal_ytd(p_assignment_action_id,
2901 p_balance_type_id);
2902 --
2903 end if;
2904 --
2905 else l_balance := null;
2906 end if;
2907 --
2908 RETURN l_balance;
2909 --
2910 END calc_asg_cal_ytd;
2911 --
2912
2913
2914 --------------------------------------------------------------------------------
2915 --
2916 -- CALC_BALANCE --
2917 -- General function for accumulating a balance between two dates
2918 --
2919 --------------------------------------------------------------------------------
2920
2921 FUNCTION calc_balance(
2922 p_assignment_id IN NUMBER,
2923 p_balance_type_id IN NUMBER, -- balance
2924 p_period_from_date IN DATE, -- since regular pay date of period
2925 p_event_from_date IN DATE, -- since effective date of
2926 p_to_date IN DATE, -- sum up to this date
2927 p_action_sequence IN NUMBER) -- sum up to this sequence
2928 RETURN NUMBER
2929 IS
2930 --
2931 --
2932 l_balance NUMBER;
2933 --
2934 BEGIN
2935 --
2936 SELECT
2937 NVL(SUM(TARGET.result_value * FEED.scale),0)
2938 INTO
2939 l_balance
2940 FROM
2941 pay_balance_feeds_f FEED
2942 ,pay_run_result_values TARGET
2943 ,pay_run_results RR
2944 ,pay_payroll_actions PACT
2945 ,per_time_periods PPTP
2946 ,pay_assignment_actions ASSACT
2947 WHERE
2948 FEED.balance_type_id = P_BALANCE_TYPE_ID
2949 AND FEED.input_value_id = TARGET.input_value_id
2950 AND TARGET.run_result_id = RR.run_result_id
2951 AND RR.assignment_action_id = ASSACT.assignment_action_id
2952 AND ASSACT.payroll_action_id = PACT.payroll_action_id
2953 AND PACT.effective_date BETWEEN FEED.effective_start_date AND FEED.effective_end_date
2954 AND RR.status IN ('P','PA')
2955 AND PACT.time_period_id = PPTP.time_period_id
2956 AND PPTP.regular_payment_date >= P_PERIOD_FROM_DATE
2957 AND PACT.effective_date >= P_EVENT_FROM_DATE
2958 AND PACT.effective_date <= P_TO_DATE
2959 AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
2960 AND ASSACT.assignment_id = P_ASSIGNMENT_ID;
2961
2962 RETURN l_balance;
2963 --
2964 END calc_balance;
2965 --
2966
2967 --------------------------------------------------------------------------------
2968 -- --
2969 -- CREATE DIMENSION --
2970 -- --
2971 --------------------------------------------------------------------------------
2972
2973 PROCEDURE create_dimension(
2974 errbuf OUT NOCOPY VARCHAR2,
2975 retcode OUT NOCOPY NUMBER,
2976 p_business_group_id IN NUMBER,
2977 p_suffix IN VARCHAR2,
2978 p_level IN VARCHAR2,
2979 p_start_dd_mm IN VARCHAR2,
2980 p_frequency IN NUMBER,
2981 p_global_name IN VARCHAR2 DEFAULT NULL)
2982 IS
2983 BEGIN
2984 errbuf := NULL;
2985 retcode := 0;
2986 ---------------------------
2987 -- INSERT INTO FF_ROUTES --
2988 ---------------------------
2989 DECLARE
2990 l_route_text ff_routes.text%TYPE;
2991 l_bal_next number;
2992 BEGIN
2993 SELECT
2994 pay_balance_dimensions_s.NEXTVAL
2995 INTO
2996 l_bal_next
2997 FROM DUAL;
2998
2999 l_route_text :=
3000 'pay_gb_balances_v TARGET,
3001 pay_dummy_feeds_v FEED
3002 WHERE
3003 TARGET.assignment_action_id =
3004 AND TARGET.balance_type_id =
3005 AND TARGET.balance_dimension_id = ' || TO_CHAR(l_bal_next);
3006 --
3007 --
3008
3009 INSERT INTO FF_ROUTES
3010 (
3011 route_id,
3012 route_name,
3013 user_defined_flag,
3014 description,
3015 text
3016 )
3017 VALUES
3018 (
3019 ff_routes_s.NEXTVAL,
3020 'ROUTE_NAME_' || ff_routes_s.CURRVAL ,
3021 'N',
3022 'User balance dimension for '||
3023 UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ASG '||
3024 p_start_dd_mm || ' RESET'|| TO_CHAR(p_frequency,'00'),
3025 l_route_text
3026 );
3027 END;
3028
3029 -----------------------------------------
3030 -- INSERT INTO FF_ROUTE_CONTEXT_USAGES --
3031 -----------------------------------------
3032
3033 BEGIN
3034 INSERT INTO ff_route_context_usages
3035 (
3036 route_id,
3037 context_id,
3038 sequence_no
3039 )
3040 SELECT
3041 ff_routes_s.CURRVAL,
3042 context_id,
3043 1
3044 FROM
3045 ff_contexts
3046 WHERE
3047 context_name = 'ASSIGNMENT_ACTION_ID';
3048 END;
3049
3050 ------------------------------------
3051 -- INSERT INTO FF_ROUTE_PARAMETER --
3052 ------------------------------------
3053
3054 BEGIN
3055 INSERT INTO ff_route_parameters
3056 (
3057 route_parameter_id,
3058 route_id,
3059 sequence_no,
3060 parameter_name,
3061 data_type
3062 )
3063 VALUES
3064 (
3065 ff_route_parameters_s.NEXTVAL,
3066 ff_routes_s.CURRVAL,
3067 1,
3068 'BALANCE TYPE ID',
3069 'N'
3070 );
3071 END;
3072
3073 -----------------------------
3074 -- CREATION DIMENSION NAME --
3075 -----------------------------
3076
3077 DECLARE
3078 l_dim_name VARCHAR2(256);
3079 l_dim_type VARCHAR2(1);
3080 l_dim_level VARCHAR2(3);
3081 l_req_id NUMBER;
3082
3083 BEGIN
3084
3085 -- fill the dimension type
3086 IF p_level = 'ASSIGNMENT' THEN
3087 l_dim_type := 'A';
3088 l_dim_level := 'ASG';
3089 ELSIF p_level = 'PERSON' THEN
3090 l_dim_type := 'P';
3091 l_dim_level := 'PER';
3092 ELSIF p_level = 'ELEMENT' THEN
3093 l_dim_type := 'P';
3094 l_dim_level := 'ELE';
3095 ELSE
3096 l_dim_type := 'P';
3097 l_dim_level := 'PER';
3098 END IF;
3099
3100
3101 -- Fill the dimension name
3102 IF p_global_name IS NULL THEN
3103 -- USER REGULAR
3104 l_dim_name := UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ';
3105 l_dim_name := l_dim_name || l_dim_level || ' ' ;
3106 l_dim_name := l_dim_name || p_start_dd_mm || ' RESET';
3107 l_dim_name := l_dim_name || TO_CHAR(p_frequency,'00');
3108 ELSE
3109 -- USER IRREGULAR
3110 /****************************/
3111 /* Not yet implemented */
3112 /****************************/
3113 /*
3114 l_dim_name := 'USER IRREGULAR DIMENSION FOR ';
3115 l_dim_name := l_dim_name || p_level || ' BASED ON ' || p_global_name;
3116 */
3117 null;
3118 END IF;
3119
3120 -- Find the current request id
3121 l_req_id := fnd_profile.value('CONC_REQUEST_ID');
3122
3123 -- insert into the table
3124 INSERT INTO pay_balance_dimensions
3125 (
3126 balance_dimension_id,
3127 business_group_id,
3128 legislation_code,
3129 route_id,
3130 database_item_suffix,
3131 dimension_name,
3132 dimension_type,
3133 description,
3134 feed_checking_code,
3135 legislation_subgroup,
3136 payments_flag,
3137 expiry_checking_code,
3138 expiry_checking_level,
3139 feed_checking_type
3140 )
3141 VALUES
3142 (
3143 pay_balance_dimensions_s.CURRVAL,
3144 p_business_group_id,
3145 NULL,
3146 ff_routes_s.CURRVAL,
3147 p_suffix,
3148 l_dim_name,
3149 l_dim_type,
3150 'User dimension defined by Request Id ' || l_req_id,
3151 NULL,
3152 NULL,
3153 'N',
3154 'hr_gbbal.check_expiry',
3155 'P',
3156 NULL
3157 );
3158
3159 END;
3160 END create_dimension;
3161 --------------------------------------------------------------------------------
3162 -- --
3163 -- EXPIRY CHECKING CODE --
3164 -- --
3165 --------------------------------------------------------------------------------
3166 PROCEDURE check_expiry(
3167 p_owner_payroll_action_id IN NUMBER,
3168 p_user_payroll_action_id IN NUMBER,
3169 p_owner_assignment_action_id IN NUMBER,
3170 p_user_assignment_action_id IN NUMBER,
3171 p_owner_effective_date IN DATE,
3172 p_user_effective_date IN DATE,
3173 p_dimension_name IN VARCHAR2,
3174 p_expiry_information OUT NOCOPY NUMBER)
3175 IS
3176 p_user_start_period DATE;
3177 p_owner_start_period DATE;
3178 BEGIN
3179
3180 -- This is only for USER REGULAR BALANCES
3181 p_user_start_period := hr_gbbal.dimension_reset_date(p_dimension_name, p_user_effective_date,null);
3182 p_owner_start_period := hr_gbbal.dimension_reset_date(p_dimension_name, p_owner_effective_date,null);
3183 IF p_user_start_period = p_owner_start_period THEN
3184 p_expiry_information := 0; -- FALSE
3185 -- dbms_output.put_line(' p_expiry_information = FALSE ');
3186 ELSE
3187 p_expiry_information := 1; -- TRUE
3188 -- dbms_output.put_line(' p_expiry_information = TRUE ');
3189 END IF;
3190
3191 END check_expiry;
3192 -------------------------------------------------------------------------------
3193 --
3194 -- Bug 3491357 : ZA BRA Enhancement
3195 -- balance values retrieved from pay_balance_pkg only
3196
3197 --
3198 -- Has the processing time period expired
3199 --
3200 FUNCTION expired_time_period (c_assignment_action_id NUMBER)
3201 RETURN DATE IS
3202 --
3203 l_end_date DATE;
3204 --
3205 BEGIN
3206 SELECT ptp.end_date
3207 INTO l_end_date
3208 FROM per_time_periods ptp,
3209 pay_payroll_actions ppa,
3210 pay_assignment_actions paa
3211 WHERE paa.assignment_action_id = c_assignment_action_id
3212 AND paa.payroll_action_id = ppa.payroll_action_id
3213 AND ppa.time_period_id = ptp.time_period_id;
3214 --
3215 RETURN l_end_date;
3216 END expired_time_period;
3217
3218 --
3219 --To find out if the processing time period has expired (Month end date)
3220 --
3221 FUNCTION expired_time_period_month (
3222 c_assignment_action_id NUMBER)
3223 RETURN DATE IS
3224 --
3225 l_end_date DATE;
3226 --
3227 BEGIN
3228 SELECT ptp.pay_advice_date
3229 INTO l_end_date
3230 FROM per_time_periods ptp,
3231 pay_payroll_actions ppa,
3232 pay_assignment_actions paa
3233 WHERE paa.assignment_action_id = c_assignment_action_id
3234 AND paa.payroll_action_id = ppa.payroll_action_id
3235 AND ppa.time_period_id = ptp.time_period_id;
3236 --
3237 RETURN l_end_date;
3238 END expired_time_period_month;
3239 --
3240 --To find out if the effective date is later than the expiry of the Calendar year of the
3241 --assignment action
3242 --
3243 FUNCTION calendar_year (
3244 c_assignment_action_id NUMBER)
3245 RETURN NUMBER IS
3246 --
3247 l_calendar_year NUMBER ;
3248 --
3249 BEGIN
3250 SELECT to_number(ptp.prd_information3)
3251 INTO l_calendar_year
3252 FROM per_time_periods ptp,
3253 pay_payroll_actions ppa,
3254 pay_assignment_actions paa
3255 WHERE paa.assignment_action_id = c_assignment_action_id
3256 AND paa.payroll_action_id = ppa.payroll_action_id
3257 AND ppa.time_period_id = ptp.time_period_id;
3258 --
3259 RETURN l_calendar_year;
3260 END calendar_year;
3261 --
3262 -- This is a local procedure to establish the Tax Quarters for the
3263 -- latest assignment action and the effective date.
3264 --
3265 PROCEDURE get_quarters(
3266 p_latest_action_id IN NUMBER,
3267 p_latest_action_quarter OUT NOCOPY NUMBER,
3268 p_eff_date_for_quarter IN DATE,
3269 p_effective_date_quarter OUT NOCOPY NUMBER)
3270 IS
3271 --
3272 l_latest_action_quarter NUMBER;
3273 l_payroll_id NUMBER;
3274 l_effective_date_quarter NUMBER;
3275 --
3276 CURSOR latest_action_quarter (c_assignment_action_id IN NUMBER) is
3277 SELECT ptp.prd_information2, ppa.payroll_id
3278 FROM per_time_periods ptp,
3279 pay_payroll_actions ppa,
3280 pay_assignment_actions paa
3281 WHERE paa.assignment_action_id = c_assignment_action_id
3282 AND paa.payroll_action_id = ppa.payroll_action_id
3283 AND ppa.time_period_id = ptp.time_period_id;
3284 --
3285 CURSOR effective_date_quarter (c_effective_date IN DATE, c_payroll_id IN NUMBER) is
3286 SELECT ptp.prd_information2
3287 FROM per_time_periods ptp
3288 WHERE ptp.start_date <= c_effective_date
3289 AND ptp.end_date >= c_effective_date
3290 AND ptp.payroll_id = c_payroll_id;
3291 --
3292 BEGIN
3293 --
3294 OPEN latest_action_quarter(p_latest_action_id);
3295 FETCH latest_action_quarter INTO l_latest_action_quarter, l_payroll_id;
3296 close latest_action_quarter;
3297 --
3298 OPEN effective_date_quarter(p_eff_date_for_quarter, l_payroll_id);
3299 FETCH effective_date_quarter INTO l_effective_date_quarter;
3300 CLOSE effective_date_quarter;
3301 --
3302 p_latest_action_quarter := l_latest_action_quarter;
3303 p_effective_date_quarter := l_effective_date_quarter;
3304 --
3305 EXCEPTION
3306 WHEN OTHERS THEN
3307 p_latest_action_quarter := null;
3308 p_effective_date_quarter := null;
3309 --
3310 END get_quarters;
3311
3312 --
3313 -- Returns the balance values (Date Mode)
3314 FUNCTION get_balance_value
3315 (
3316 p_assignment_id IN NUMBER,
3317 p_balance_type_id IN NUMBER,
3318 p_dimension IN VARCHAR2,
3319 p_effective_date IN DATE
3320 )
3321 RETURN NUMBER IS
3322 --Variables
3323 l_latest_asg_action NUMBER;
3324 l_defined_balance_id NUMBER;
3325 l_balance_value NUMBER ;
3326 l_latest_action_quarter NUMBER ;
3327 l_effective_date_quarter NUMBER ;
3328
3329 -- This cursor gives the latest assignment action ID given an assignment
3330 -- and effective date.
3331 --
3332 CURSOR csr_get_latest_id (c_assignment_id IN NUMBER,
3333 c_effective_date IN DATE)
3334 IS
3335 SELECT /*+ ORDERED
3336 USE_NL(PAA PPA)
3337 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
3338 INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
3339 TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
3340 paa.assignment_action_id),16))
3341 FROM pay_assignment_actions paa,
3342 pay_payroll_actions ppa
3343 WHERE paa.assignment_id = c_assignment_id
3344 AND ppa.payroll_action_id = paa.payroll_action_id
3345 AND ppa.effective_date <= c_effective_date
3346 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');
3347 --
3348 CURSOR csr_defined_balance_id(c_balance_type_id IN NUMBER,
3349 c_db_item_suffix IN VARCHAR2)
3350 IS
3351 SELECT pdb.defined_balance_id
3352 FROM pay_defined_balances pdb,
3353 pay_balance_dimensions pbd
3354 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
3355 AND pbd.database_item_suffix = c_db_item_suffix
3356 AND pdb.balance_type_id = c_balance_type_id;
3357 --
3358 BEGIN
3359 OPEN csr_get_latest_id(p_assignment_id, p_effective_date);
3360 FETCH csr_get_latest_id INTO l_latest_asg_action;
3361 CLOSE csr_get_latest_id;
3362
3363 OPEN csr_defined_balance_id(p_balance_type_id, p_dimension);
3364 FETCH csr_defined_balance_id INTO l_defined_balance_id;
3365 CLOSE csr_defined_balance_id;
3366
3367 IF p_dimension = '_ASG_TAX_QTD' THEN
3368 --Establish in which tax quarters the latest assignment action and the
3369 --effective date fall
3370 get_quarters(p_latest_action_id => l_latest_asg_action,
3371 p_latest_action_quarter => l_latest_action_quarter,
3372 p_eff_date_for_quarter => p_effective_date,
3373 p_effective_date_quarter => l_effective_date_quarter);
3374 END IF ;
3375
3376 IF l_defined_balance_id IS NULL THEN
3377 l_balance_value := NULL ;
3378 ELSE
3379 IF (
3380 l_latest_asg_action IS NULL
3381 OR
3382 --If effective date later than the expiry of the tax year of the
3383 --assignment action. then set the balance value to zero
3384 (p_dimension = '_ASG_TAX_YTD'
3385 AND p_effective_date > get_expired_year_date(l_latest_asg_action)
3386 )
3387 OR
3388 --IF effective date later than the expiry of the Calendar year of the
3389 --assignment action then set balance value to zero
3390 (p_dimension = '_ASG_CAL_YTD'
3391 AND to_number(to_char(p_effective_date, 'YYYY')) <> calendar_year(l_latest_asg_action)
3392 )
3393 OR
3394 -- Has the processing time period expired (Month end date)
3395 ( (p_dimension = '_ASG_CAL_MTD' OR p_dimension = '_ASG_TAX_MTD')
3396 AND expired_time_period_month(l_latest_asg_action) < p_effective_date
3397 )
3398 OR
3399 -- Has the processing time period expired
3400 ( (p_dimension = '_ASG_TAX_PTD'
3401 OR p_dimension = '_ASG_CAL_PTD'
3402 OR p_dimension = '_PAYMENTS'
3403 OR p_dimension = '_ASG_RUN'
3404 )
3405 AND expired_time_period(l_latest_asg_action) < p_effective_date
3406 )
3407 OR
3408 --Check if the tax quarters for the latest assignment action and the
3409 --effective date are the same
3410 (p_dimension = '_ASG_TAX_QTD'
3411 AND l_latest_action_quarter <> l_effective_date_quarter
3412 )
3413 )
3414 THEN
3415 l_balance_value := 0;
3416 ELSE
3417 l_balance_value := pay_balance_pkg.get_value
3418 (
3419 p_defined_balance_id => l_defined_balance_id,
3420 p_assignment_action_id => l_latest_asg_action
3421 );
3422 END IF;
3423 END IF;
3424 --
3425 RETURN l_balance_value;
3426 hr_utility.set_location('py_za_bal.get_balance_value',10);
3427 hr_utility.set_location('l_defined_balance_id'||to_char(l_defined_balance_id),10);
3428 hr_utility.set_location('l_balance_value'||to_char(l_balance_value),10);
3429
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 hr_utility.set_location('py_za_bal.get_balance_value',20);
3433 hr_utility.set_message(801,'Sql Err Code: '||to_char(sqlcode));
3434 hr_utility.raise_error;
3435
3436 END get_balance_value;
3437
3438 -- Bug 4365925
3439 -- Returns the balance values (Date Mode)
3440 FUNCTION get_balance_value
3441 (
3442 p_defined_balance_id IN NUMBER,
3443 p_assignment_id IN NUMBER,
3444 p_effective_date IN DATE
3445 )
3446 RETURN NUMBER IS
3447 --Variables
3448 l_latest_asg_action NUMBER;
3449 l_balance_value NUMBER ;
3450
3451 -- This cursor gives the latest assignment action ID given an assignment
3452 -- and effective date.
3453 --
3454 CURSOR csr_get_latest_id (c_assignment_id IN NUMBER,
3455 c_effective_date IN DATE)
3456 IS
3457 SELECT /*+ ORDERED
3458 USE_NL(PAA PPA)
3459 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
3460 INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
3461 TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
3462 paa.assignment_action_id),16))
3463 FROM pay_assignment_actions paa,
3464 pay_payroll_actions ppa
3465 WHERE paa.assignment_id = c_assignment_id
3466 AND ppa.payroll_action_id = paa.payroll_action_id
3467 AND ppa.effective_date <= c_effective_date
3468 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');
3469
3470 BEGIN
3471 OPEN csr_get_latest_id(p_assignment_id, p_effective_date);
3472 FETCH csr_get_latest_id INTO l_latest_asg_action;
3473 CLOSE csr_get_latest_id;
3474
3475 IF p_defined_balance_id IS NULL THEN
3476 l_balance_value := NULL ;
3477 ELSE
3478 IF l_latest_asg_action IS NULL THEN
3479 l_balance_value := 0;
3480 ELSE
3481 l_balance_value := pay_balance_pkg.get_value
3482 (
3483 p_defined_balance_id => p_defined_balance_id,
3484 p_assignment_action_id => l_latest_asg_action
3485 );
3486 END IF;
3487 END IF;
3488 --
3489 RETURN l_balance_value;
3490 hr_utility.set_location('py_za_bal.get_balance_value',50);
3491 hr_utility.set_location('l_balance_value'||to_char(l_balance_value),50);
3492
3493 EXCEPTION
3494 WHEN OTHERS THEN
3495 hr_utility.set_location('py_za_bal.get_balance_value',60);
3496 hr_utility.set_message(801,'Sql Err Code: '||to_char(sqlcode));
3497 hr_utility.raise_error;
3498
3499 END get_balance_value;
3500 --Bug 4365925 changes end
3501 --
3502 -- Returns the balance values ( assignment action mode )
3503 --
3504 FUNCTION get_balance_value_action
3505 ( p_assignment_action_id IN NUMBER
3506 , p_balance_type_id IN NUMBER
3507 , p_dimension IN VARCHAR2
3508 )
3509 RETURN NUMBER IS
3510 --Variables
3511 l_balance_value NUMBER;
3512 l_assignment_id NUMBER;
3513 l_defined_balance_id NUMBER;
3514
3515 CURSOR csr_defined_balance_id(c_balance_type_id IN NUMBER,
3516 c_db_item_suffix IN VARCHAR2)
3517 IS
3518 SELECT pdb.defined_balance_id
3519 FROM pay_defined_balances pdb,
3520 pay_balance_dimensions pbd
3521 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
3522 AND pbd.database_item_suffix = c_db_item_suffix
3523 AND pdb.balance_type_id = c_balance_type_id;
3524
3525 BEGIN
3526 l_assignment_id := get_correct_type(p_assignment_action_id);
3527
3528 OPEN csr_defined_balance_id(p_balance_type_id, p_dimension);
3529 FETCH csr_defined_balance_id INTO l_defined_balance_id;
3530 CLOSE csr_defined_balance_id;
3531
3532 IF (l_assignment_id IS NULL OR l_defined_balance_id IS NULL )THEN
3533 --
3534 -- The assignment action is not a payroll or quickpay type, so return null
3535 --
3536 l_balance_value := NULL ;
3537 ELSE
3538 l_balance_value := pay_balance_pkg.get_value
3539 (
3540 p_defined_balance_id => l_defined_balance_id,
3541 p_assignment_action_id => p_assignment_action_id
3542 );
3543 END IF ;
3544 RETURN l_balance_value;
3545 hr_utility.set_location('py_za_bal.get_balance_value_action',30);
3546 hr_utility.set_location('l_defined_balance_id'||to_char(l_defined_balance_id),30);
3547 hr_utility.set_location('l_balance_value'||to_char(l_balance_value),30);
3548 --
3549 EXCEPTION
3550 WHEN OTHERS THEN
3551 hr_utility.set_location('py_za_bal.get_balance_value_action',40);
3552 hr_utility.set_message(801,'Sql Err Code: '||to_char(sqlcode));
3553 hr_utility.raise_error;
3554
3555 END get_balance_value_action;
3556 --
3557 ------------------------------------------------------------------
3558
3559 END py_za_bal;
[Home] [Help]