1 PACKAGE BODY hr_gbbal AS
2 /* $Header: pygbbal.pkb 120.6.12000000.4 2007/09/03 12:55:30 ajeyam noship $ */
3 --------------------------------------------------------------------------------
4 g_assignment_action_id number;
5 g_start_of_year per_time_periods.regular_payment_date%type;
6 -- g_initialization_exists number;
7 g_asg_td_ytd number;
8 g_assignment_action_id2 number;
9 g_assignment_id number;
10 g_action_sequence number;
11 g_effective_date date;
12 g_ni_a_id number;
13 g_ni_b_id number;
14 g_ni_c_id number;
15 g_ni_d_id number;
16 g_ni_e_id number;
17 g_ni_f_id number;
18 g_ni_g_id number;
19 g_ni_j_id number;
20 g_ni_l_id number;
21 g_ni_s_id number;
22 g_ni_a_able_id number;
23 g_ni_b_able_id number;
24 g_ni_c_able_id number;
25 g_ni_d_able_id number;
26 g_ni_e_able_id number;
27 g_ni_f_able_id number;
28 g_ni_g_able_id number;
29 g_ni_j_able_id number;
30 g_ni_l_able_id number;
31 g_ni_s_able_id number;
32 g_ni_a_defbal_id number;
33 g_ni_b_defbal_id number;
34 g_ni_c_defbal_id number;
35 g_ni_d_defbal_id number;
36 g_ni_e_defbal_id number;
37 g_ni_f_defbal_id number;
38 g_ni_g_defbal_id number;
39 g_ni_j_defbal_id number;
40 g_ni_l_defbal_id number;
41 g_ni_s_defbal_id number;
42 g_ni_a_able_defbal_id number;
43 g_ni_b_able_defbal_id number;
44 g_ni_c_able_defbal_id number;
45 g_ni_d_able_defbal_id number;
46 g_ni_e_able_defbal_id number;
47 g_ni_f_able_defbal_id number;
48 g_ni_g_able_defbal_id number;
49 g_ni_j_able_defbal_id number;
50 g_ni_l_able_defbal_id number;
51 g_ni_s_able_defbal_id number;
52 g_ni_a_exists number;
53 g_ni_b_exists number;
54 g_ni_c_exists number;
55 g_ni_d_exists number;
56 g_ni_e_exists number;
57 g_ni_f_exists number;
58 g_ni_g_exists number;
59 g_ni_j_exists number;
60 g_ni_l_exists number;
61 g_ni_s_exists number;
62 g_ni_cat_indicator_table_id number;
63 g_ni_element_type_id number;
64 g_ni_a_element_type_id number;
65 g_ni_b_element_type_id number;
66 g_ni_c_element_type_id number;
67 g_ni_d_element_type_id number;
68 g_ni_e_element_type_id number;
69 g_ni_f_element_type_id number;
70 g_ni_g_element_type_id number;
71 g_ni_j_element_type_id number;
72 g_ni_l_element_type_id number;
73 g_ni_s_element_type_id number;
74 g_action_typer pay_payroll_actions.action_type%TYPE;
75 g_action_typeq pay_payroll_actions.action_type%TYPE;
76 g_action_typeb pay_payroll_actions.action_type%TYPE;
77 g_balance number;
78
79 -- return the start of the span (year/quarter/week)
80 FUNCTION span_start(
81 p_input_date DATE,
82 p_frequency NUMBER DEFAULT 1,
83 p_start_dd_mm VARCHAR2 DEFAULT '06-04-')
84 RETURN DATE
85 IS
86 l_year NUMBER(4);
87 l_start DATE;
88 l_start_dd_mm varchar2(6);
89 l_correct_format BOOLEAN;
90 --
91 BEGIN
92 l_year := FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
93 --
94 -- Check that the passed in start of year
95 -- is in the correct format. Add a hyphen if one is missing
96 -- from the end, and ensure DD-MM- only has 6 characters.
97 -- If none of these 2 criteria are met, return null.
98 --
99 if length(p_start_dd_mm) = 5 and instr(p_start_dd_mm,'-',-1) = 3 then
100 l_start_dd_mm := p_start_dd_mm||'-';
101 l_correct_format := TRUE;
102 elsif length(p_start_dd_mm) = 6 and instr(p_start_dd_mm,'-',-1) = 6 then
103 l_start_dd_mm := p_start_dd_mm;
104 l_correct_format := TRUE;
105 else
106 l_correct_format := FALSE;
107 end if;
108 --
109 if l_correct_format then
110 IF p_input_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY') THEN
111 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
112 ELSE
113 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year -1),'DD-MM-YYYY');
114 END IF;
115 -- cater for weekly based frequency based on 52 per annum
116 IF p_frequency IN (52,26,13) THEN
117 l_start := p_input_date - MOD(p_input_date - l_start, 7 * (52/p_frequency));
118 ELSE
119 -- cater for monthly based frequency based on 12 per annum
120 l_start := ADD_MONTHS(l_start, (12/p_frequency) * TRUNC(MONTHS_BETWEEN(
121 p_input_date,l_start)/(12/p_frequency)));
122 END IF;
123 end if;
124 --
125 RETURN l_start;
126 END span_start;
127 -------------------------------------------------------------------------------
128 --
129 -- Function SPAN_END. This returns the end of the person level
130 -- (statutory) period.
131 --
132 -------------------------------------------------------------------------------
133 -- return the end of the span (year/quarter/week)
134 FUNCTION span_end(
135 p_input_date DATE,
136 p_frequency NUMBER DEFAULT 1,
137 p_start_dd_mm VARCHAR2 DEFAULT '06-04-')
138 RETURN DATE
139 IS
140 l_year NUMBER(4);
141 l_end DATE;
142 l_start_dd_mm varchar2(6);
143 l_correct_format BOOLEAN;
144 --
145 BEGIN
146 l_year := FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(p_input_date,'YYYY'));
147 --
148 -- Check that the passed in start of year
149 -- is in the correct format. Add a hyphen if one is missing
150 -- from the end, and ensure DD-MM- only has 6 characters.
151 -- If none of these 2 criteria are met, return null.
152 --
153 if length(p_start_dd_mm) = 5 and instr(p_start_dd_mm,'-',-1) = 3 then
154 l_start_dd_mm := p_start_dd_mm||'-';
155 l_correct_format := TRUE;
156 elsif length(p_start_dd_mm) = 6 and instr(p_start_dd_mm,'-',-1) = 6 then
157 l_start_dd_mm := p_start_dd_mm;
158 l_correct_format := TRUE;
159 else
160 l_correct_format := FALSE;
161 end if;
162 --
163 if l_correct_format then
164 IF p_input_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY') THEN
165 l_end := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
166 ELSE
167 l_end := TO_DATE(l_start_dd_mm||TO_CHAR(l_year -1),'DD-MM-YYYY');
168 END IF;
169 -- cater for weekly based frequency based on 52 per annum
170 IF p_frequency IN (52,26,13) THEN
171 l_end := p_input_date - MOD(p_input_date - l_end, 7 * (52/p_frequency)) + ((7 * 52/p_frequency)-1);
172 ELSE
173 -- cater for monthly based frequency based on 12 per annum
174 l_end := (add_months (ADD_MONTHS(l_end, (12/p_frequency)
175 * TRUNC(MONTHS_BETWEEN(p_input_date,l_end)/(12/p_frequency))),
176 12/p_frequency) -1);
177 END IF;
178 end if;
179 --
180 RETURN l_end;
181 END span_end;
182 --
183 --------------------------------------------------------------------------------
184 --
185 -- GET CORRECT TYPE (private)
186 --
187 --
188 --------------------------------------------------------------------------------
189 --
190 -- This is a validation check to ensure that the assignment action is of the
191 -- correct type. This is called from all assignment action mode functions.
192 -- The assignment id is returned (and not assignment action id) because
193 -- this is to be used in the expired latest balance check. This function thus
194 -- has two uses - to validate the assignment action, and give the corresponding
195 -- assignmment id for that action.
196 --
197 FUNCTION get_correct_type(p_assignment_action_id IN NUMBER)
198 --
199 RETURN NUMBER IS
200 --
201 l_assignment_id NUMBER;
202 --
203 cursor get_corr_type (c_assignment_action_id IN NUMBER) is
204 SELECT assignment_id
205 FROM pay_assignment_actions paa,
206 pay_payroll_actions ppa
207 WHERE
208 paa.assignment_action_id = c_assignment_action_id
209 AND ppa.payroll_action_id = paa.payroll_action_id
210 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
211 --
212 BEGIN
213 --
214 open get_corr_type(p_assignment_action_id);
215 fetch get_corr_type into l_assignment_id;
216 close get_corr_type;
217 --
218 RETURN l_assignment_id;
219 --
220 END get_correct_type;
221 --------------------------------------------------------------------------------
222 --
223 -- GET LATEST ACTION ID (private)
224 --
225 --
226 --------------------------------------------------------------------------------
227 -- This function returns the latest assignment action ID given an assignment
228 -- and effective date. This is called from all Date Mode functions.
229 --
230 FUNCTION get_latest_action_id (p_assignment_id IN NUMBER,
231 p_effective_date IN DATE)
232 RETURN NUMBER IS
233 --
234 l_assignment_action_id NUMBER;
235 l_master_asg_action_id NUMBER;
236 l_child_asg_action_id NUMBER;
237 --
238
239 /* bug fix 4493616 start*/
240 cursor get_master_latest_id (c_assignment_id IN NUMBER,
241 c_effective_date IN DATE) is
242 SELECT /*+ USE_NL(paa, ppa) */
243 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
244 paa.assignment_action_id),16))
245 FROM pay_assignment_actions paa,
246 pay_payroll_actions ppa
247 WHERE
248 paa.assignment_id = c_assignment_id
249 AND ppa.payroll_action_id = paa.payroll_action_id
250 AND paa.source_action_id is null
251 AND ppa.effective_date <= c_effective_date
252 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
253 -- AND paa.action_status = 'C';
254 --
255
256 cursor get_latest_id (c_assignment_id IN NUMBER,
257 c_effective_date IN DATE,
258 c_master_asg_action_id IN NUMBER) is
259 SELECT /*+ USE_NL(paa, ppa) */
260 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
261 paa.assignment_action_id),16))
262 FROM pay_assignment_actions paa,
263 pay_payroll_actions ppa
264 WHERE
265 paa.assignment_id = c_assignment_id
266 AND ppa.payroll_action_id = paa.payroll_action_id
267 AND paa.source_action_id is not null
268 AND ppa.effective_date <= c_effective_date
269 AND ppa.action_type in ('R', 'Q')
270 --AND paa.action_status = 'C'
271 AND paa.source_action_id = c_master_asg_action_id ;
272 --
273 BEGIN
274 --
275
276 open get_master_latest_id(p_assignment_id, p_effective_date);
277 fetch get_master_latest_id into l_master_asg_action_id;
278
279 if get_master_latest_id%found then
280
281 open get_latest_id(p_assignment_id, p_effective_date,l_master_asg_action_id);
282 fetch get_latest_id into l_child_asg_action_id;
283
284 if l_child_asg_action_id is not null then
285 l_assignment_action_id := l_child_asg_action_id;
286 else
287 l_assignment_action_id := l_master_asg_action_id;
288 end if;
289 close get_latest_id;
290 end if;
291 close get_master_latest_id;
292 /* bug fix 4493616 end*/
293 --
294 RETURN l_assignment_action_id;
295 --
296 END get_latest_action_id;
297 --
298 --------------------------------------------------------------------------------
299 --
300 -- DIMENSION RELEVANT (private)
301 --
302 --
303 --------------------------------------------------------------------------------
304 --
305 -- This function checks that a value is required for the dimension
306 -- for this particular balance type. If so, the defined balance is returned.
307 --
308 FUNCTION dimension_relevant(p_balance_type_id IN NUMBER,
309 p_database_item_suffix IN VARCHAR2)
310 RETURN NUMBER IS
311 --
312 l_defined_balance_id NUMBER;
313 --
314 cursor relevant(c_balance_type_id IN NUMBER,
315 c_db_item_suffix IN VARCHAR2) IS
316 select pdb.defined_balance_id from
317 pay_defined_balances pdb,
318 pay_balance_dimensions pbd
319 where pdb.balance_dimension_id = pbd.balance_dimension_id
320 and pbd.database_item_suffix = c_db_item_suffix
321 and pdb.balance_type_id = c_balance_type_id;
322 --
323 BEGIN
324 --
325 open relevant(p_balance_type_id, p_database_item_suffix);
326 fetch relevant into l_defined_balance_id;
327 close relevant;
328 --
329 RETURN l_defined_balance_id;
330 --
331 END dimension_relevant;
332 --------------------------------------------------------------------------------
333 --
334 -- GET LATEST DATE (private)
335 --
336 --
337 --------------------------------------------------------------------------------
338 --
339 -- Find out the effective date of the latest balance of a particular
340 -- assignment action.
341 --
342 FUNCTION get_latest_date(
343 p_assignment_action_id NUMBER)
344 RETURN DATE IS
345 --
346 l_effective_date date;
347 --
348 cursor c_bal_date is
349 SELECT ppa.effective_date
350 FROM pay_payroll_actions ppa,
351 pay_assignment_actions paa
352 WHERE paa.payroll_action_id = ppa.payroll_action_id
353 AND paa.assignment_action_id = p_assignment_action_id;
354 --
355 begin
356 --
357 OPEN c_bal_date;
358 FETCH c_bal_date into l_effective_date;
359 if c_bal_date%NOTFOUND then
360 l_effective_date := null;
361 -- raise_application_error(-20000,'This assignment action is invalid');
362 end if;
363 CLOSE c_bal_date;
364 --
365 RETURN l_effective_date;
366 END get_latest_date;
367 --
368 -------------------------------------------------------------------------------
369 --
370 -- GET_EXPIRED_YEAR_DATE (private)
371 --
372 -------------------------------------------------------------------------------
373 --
374 -- Find out the expiry of the year of the assignment action's effective date,
375 -- for expiry checking in the main functions.
376 --
377 FUNCTION get_expired_year_date(
378 p_action_effective_date DATE)
379 RETURN DATE IS
380 --
381 l_expired_date DATE;
382 l_year_add_no NUMBER;
383 --
384 BEGIN
385 --
386 if p_action_effective_date is not null then
387 --
388 if p_action_effective_date <
389 to_date('06-04-' || to_char(p_action_effective_date,'YYYY'),
390 'DD-MM-YYYY') then
391 l_year_add_no := 0;
392 else l_year_add_no := 1;
393 end if;
394 --
395 -- Set expired date to the 6th of April next.
396 --
397 l_expired_date :=
398 ( to_date('06-04-' || to_char( fnd_number.canonical_to_number(to_char(
399 p_action_effective_date,'YYYY')) + l_year_add_no),'DD-MM-YYYY'));
400 --
401 end if;
402 --
403 RETURN l_expired_date;
404 --
405 END get_expired_year_date;
406 --
407 ------------------------------------------------------------------------------
408 --
409 -- GET_EXPIRED_TWO_YEAR_DATE (private)
410 -------------------------------------------------------------------------------
411 --
412 -- Find out the expiry of the year of the assignment action's effective date,
413 -- for the ASG_TD_ODD_TWO_YTD and ASG_TD_EVEN_TWO_YTD
414 --
415 FUNCTION get_expired_two_year_date(
416 p_action_effective_date DATE,
417 p_odd_even VARCHAR2 )
418 RETURN DATE IS
419 --
420 l_expired_date DATE;
421 l_year_add_no NUMBER;
422 --
423 BEGIN
424 --
425 IF p_action_effective_date is not null THEN
426 --
427 IF p_action_effective_date < to_date('06-04-' ||
428 to_char(p_action_effective_date,'YYYY'),'DD-MM-YYYY') THEN
429 l_year_add_no := 0;
430 ELSE
431 l_year_add_no := 1;
432 END IF;
433 --
434 -- add a year depending on the odd or even dimension
435 --
436 IF p_odd_even = 'EVEN' THEN
437 IF mod(to_number(to_char(p_action_effective_date,'yyyy')),2) = 1 THEN
438 l_year_add_no := l_year_add_no + 1;
439 ELSE
440 l_year_add_no := l_year_add_no;
441 END IF;
442 ELSIF p_odd_even = 'ODD' then
443 IF mod(to_number(to_char(p_action_effective_date,'yyyy')),2) = 1 THEN
444 l_year_add_no := l_year_add_no;
445 ELSE
446 l_year_add_no := l_year_add_no + 1;
447 END IF;
448 END IF;
449 --
450 -- Set expired date to the 6th of April of the expiring year.
451 --
452 l_expired_date := ( to_date('06-04-' ||
453 to_char( fnd_number.canonical_to_number(to_char(
454 p_action_effective_date,'YYYY')) + l_year_add_no),'DD-MM-YYYY'));
455 --
456 END IF;
457 --
458 RETURN l_expired_date;
459 --
460 END get_expired_two_year_date;
461 ---------------------------------------------------------------------------
462 --
463 -- what is the latest reset date for a particular dimension
464 FUNCTION dimension_reset_date(
465 p_dimension_name VARCHAR2,
466 p_user_date DATE,
467 p_business_group_id NUMBER)
468 RETURN DATE
469 IS
470 l_start_dd_mon VARCHAR2(7);
471 l_global_name VARCHAR2(30);
472 l_period_from_date DATE;
473 l_frequency NUMBER;
474 l_start_reset NUMBER;
475 BEGIN
476 IF SUBSTR(p_dimension_name,31,8) = 'USER-REG' THEN -- [
477 l_start_reset := INSTR(p_dimension_name,'RESET',30);
478 l_start_dd_mon := SUBSTR(p_dimension_name, l_start_reset - 6, 5);
479 l_frequency := FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR
480 (p_dimension_name, l_start_reset + 6, 2));
481 l_period_from_date := span_start(p_user_date,
482 l_frequency, l_start_dd_mon);
483 END IF; -- ]
484
485 /* */
486 /* User Irregular Balance are not yet implemented */
487 /* */
488 /*
489 IF SUBSTR(p_dimension_name,1,14) = 'USER IRREGULAR' THEN -- [
490 -- find the global set up with the reset dates
491 -- need to code exception if there isn't a valid one (default to calendar
492 -- also make this code a local function
493 l_start_word := INSTR(p_dimension_name,'BASED ON') + 8;
494 l_global_name := SUBSTR(p_dimension_name, l_start_word);
495 SELECT
496 effective_start_date
497 INTO
498 l_period_from_date
499 FROM
500 ff_globals_f
501 WHERE global_name = l_global_name
502 AND business_group_id = p_business_group_id
503 AND p_user_date BETWEEN effective_start_date AND effective_end_date;
504 END IF; -- ]
505 */
506
507 RETURN l_period_from_date;
508 END dimension_reset_date;
509 --------------------------------------------------------------------------------
510 -- when did the director become a director
511 -- find the earliest person row that was date effcetive in this year with
512 -- director flag set
513 FUNCTION start_director(
514 p_assignment_id NUMBER,
515 p_start_date DATE ,
516 p_end_date DATE )
517 RETURN DATE
518 IS
519 l_event_from_date date;
520 BEGIN
521 select nvl(min(P.effective_start_date)
522 ,fnd_date.canonical_to_date('4712/12/31'))
523 into l_event_from_date
524 from per_people_f p, /* should this be all ? */
525 per_assignments_f ass
526 where p.per_information2 = 'Y'
527 and ASS.person_id = P.person_id
528 and P.effective_start_date <= p_end_date
529 and p.effective_end_date >= p_start_date
530 and p_end_date between
531 ass.effective_start_date and ass.effective_end_date
532 and ass.assignment_id = p_assignment_id ;
533
534 RETURN l_event_from_date;
535 END start_director;
536 --------------------------------------------------------------------------------
537 -- Function: per_datemode_balance
538 -- Description: Introduced for bug fix 3436701, this expires Person level
539 -- balances before calling the core balance UE process to get the value.
540 -- The Core BUE cannot be called in datemode directly as this causes an
541 -- error from the view, because it issues DML and commits.
542 --------------------------------------------------------------------------------
543 Function per_datemode_balance(p_assignment_action_id in number,
544 p_defined_balance_id in number,
545 p_database_item_suffix in varchar2,
546 p_effective_date in date) return number is
547 --
548 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
549 SELECT ptp.end_date, ppa.effective_date
550 FROM per_time_periods ptp,
551 pay_payroll_actions ppa,
552 pay_assignment_actions paa
553 WHERE
554 paa.assignment_action_id = c_assignment_action_id
555 AND paa.payroll_action_id = ppa.payroll_action_id
556 AND ppa.time_period_id = ptp.time_period_id;
557 --
558 l_period_end_date DATE;
559 l_date_paid DATE;
560 l_balance NUMBER;
561 l_action_eff_date DATE;
562 l_expiry_date DATE;
563 --
564 BEGIN
565 --
566 IF p_database_item_suffix in ('_PER_TD_STAT_PTD',
567 '_PER_TD_PTD',
568 '_PER_NI_PTD',
569 '_PER_TD_CPE_STAT_PTD') THEN
570 open expired_time_period(p_assignment_action_id);
571 fetch expired_time_period INTO l_period_end_date, l_date_paid;
572 close expired_time_period;
573 --hr_utility.trace('PER - Dates: end='||l_period_end_date||' paid='|| l_date_paid);
574 --
575 l_expiry_date := greatest(l_period_end_date,l_date_paid);
576 --
577 ELSIF p_database_item_suffix in ('_PER_TD_YTD',
578 '_PER_TD_DIR_YTD',
579 '_PER_TD_CPE_YTD') THEN
580 l_action_eff_date := get_latest_date(p_assignment_action_id);
581 --
582 l_expiry_date := get_expired_year_date(l_action_eff_date);
583 ELSIF p_database_item_suffix = '_PER_TD_EVEN_TWO_YTD' THEN
584 --
585 l_action_eff_date := get_latest_date(p_assignment_action_id);
589 l_action_eff_date := get_latest_date(p_assignment_action_id);
586 l_expiry_date := get_expired_two_year_date(l_action_eff_date,'EVEN');
587 ELSIF p_database_item_suffix = '_PER_TD_ODD_TWO_YTD' THEN
588 --
590 l_expiry_date := get_expired_two_year_date(l_action_eff_date,'ODD');
591 ELSE
592 -- A non-covered PER expiry, call pkg without expiring here
593 l_expiry_date := to_date('31/12/4712','DD/MM/YYYY');
594 END IF;
595 --
596 -- Expiry dates set, check the effective date
597 --
598 /*Bug fix 5104943*/
599 IF p_database_item_suffix in ('_PER_TD_STAT_PTD', '_PER_TD_PTD','_PER_NI_PTD','_PER_TD_CPE_STAT_PTD') THEN
600 if p_effective_date > l_expiry_date then
601 -- Balance has expired
602 l_balance := 0;
603 else
604 l_balance := pay_balance_pkg.get_value(p_defined_balance_id,
605 p_assignment_action_id);
606 end if;
607 ELSE
608 if p_effective_date >= l_expiry_date then
609 -- Balance has expired
610 l_balance := 0;
611 else
612 l_balance := pay_balance_pkg.get_value(p_defined_balance_id,
613 p_assignment_action_id);
614 end if;
615 END IF;
616
617 --
618 RETURN l_balance;
619 --
620 END per_datemode_balance;
621 --------------------------------------------------------------------------------
622 --
623 -- BALANCE --
624 -- Called from calc_all_balances for User Regulars and other non UK seeded
625 -- balances, also called from pay_gb_balances_v.
626 --
627 --------------------------------------------------------------------------------
628 --
629 FUNCTION balance(
630 p_assignment_action_id IN NUMBER,
631 p_defined_balance_id IN NUMBER,
632 p_effective_date IN DATE DEFAULT NULL)
633 RETURN NUMBER
634 IS
635 --
636 l_balance NUMBER;
637 l_balance1 NUMBER;
638 l_assignment_id NUMBER;
639 l_balance_type_id NUMBER;
640 l_period_from_date DATE := FND_DATE.CANONICAL_TO_DATE('0001/01/01');
641 l_event_from_date DATE := FND_DATE.CANONICAL_TO_DATE('0001/01/01');
642 l_to_date DATE;
643 l_regular_payment_date DATE;
644 l_action_sequence NUMBER;
645 l_business_group_id NUMBER;
646 l_dimension_bgid NUMBER;
647 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
648 l_database_item_suffix pay_balance_dimensions.database_item_suffix%TYPE;
649 l_legislation_code pay_balance_dimensions.legislation_code%TYPE;
650 l_latest_value_exists VARCHAR(2);
651 l_period_end_date DATE;
652 l_date_paid DATE;
653 --
654 cursor c1 (c_asg_action_id IN NUMBER,
655 c_defined_balance_id IN NUMBER)is
656 SELECT value, assignment_id
657 from pay_assignment_latest_balances
658 Where assignment_action_id = c_asg_action_id
659 and defined_balance_id = c_defined_balance_id;
660 --
661 cursor action_context is
662 SELECT
663 BAL_ASSACT.assignment_id,
664 BAL_ASSACT.action_sequence,
665 BACT.effective_date,
666 PTP.regular_payment_date,
667 BACT.business_group_id
668 FROM
669 pay_assignment_actions BAL_ASSACT,
670 pay_payroll_actions BACT,
671 per_time_periods PTP
672 WHERE
673 BAL_ASSACT.assignment_action_id = p_assignment_action_id
674 AND PTP.time_period_id = BACT.time_period_id
675 AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
676 --
677 cursor balance_dimension is
678 SELECT
679 DB.balance_type_id,
680 DIM.dimension_name,
681 DIM.database_item_suffix ,
682 DIM.legislation_code,
683 DIM.business_group_id
684 FROM
685 pay_defined_balances DB,
686 pay_balance_dimensions DIM
687 WHERE DB.defined_balance_id = p_defined_balance_id
688 AND DIM.balance_dimension_id = DB.balance_dimension_id;
689 --
690 BEGIN
691 --
692 -- get the context of the using action
693 --
694 open action_context;
695 FETCH action_context INTO
696 l_assignment_id,
697 l_action_sequence,
698 l_to_date,
699 l_regular_payment_date,
700 l_business_group_id;
701 CLOSE action_context;
702 --
703 -- from the item name determine what balance and dimension it is
704 --
705 open balance_dimension;
706 FETCH balance_dimension INTO
707 l_balance_type_id,
708 l_dimension_name,
709 l_database_item_suffix ,
710 l_legislation_code,
711 l_dimension_bgid;
712 close balance_dimension;
713 --
714 -- Bug 2755875. New routes added that are core routes. In this case
715 -- the core BUE must be called with the passed-in parameters.
716 -- This will use the latest balance if it exists.
717 -- Check the BGID incase this is a user-defined user-reg dimension.
721 --
718 -- Bug 2886012, handle the exception just incase no dimension found.
719
720 IF l_legislation_code is null AND l_dimension_bgid is null then
722 BEGIN
723 l_balance := pay_balance_pkg.get_value(
724 p_assignment_action_id => p_assignment_action_id,
725 p_defined_balance_id => p_defined_balance_id);
726 EXCEPTION WHEN NO_DATA_FOUND THEN
727 l_balance := null;
728 END;
729 --
730 ELSE -- A GB or user balance
731
732 -- Does the assignment action id exist in the latest balances table
733 --
734 /* Commented for bug fix 4452262*/
735 /* OPEN c1 (p_assignment_action_id, p_defined_balance_id);
736 FETCH c1 INTO l_balance, l_assignment_id;
737 IF c1%FOUND THEN l_latest_value_exists := 'T';
738 ELSE l_latest_value_exists := 'F';
739 END IF;
740 CLOSE c1;
741 */
742
743 /*For bug fix 4452262*/
744 l_latest_value_exists := 'F';
745 for i in c1 (p_assignment_action_id, p_defined_balance_id)
746 loop
747 l_balance := nvl(l_balance,0) + nvl(i.value,0);
748 l_assignment_id := i.assignment_id;
749
750 l_latest_value_exists := 'T';
751
752 end loop;
753 --
754 -- Bug 923689. Raise NDF to stop date-format error from span_start.
755 --
756 IF l_to_date is null
757 then RAISE NO_DATA_FOUND;
758 end if;
759 --
760 -- If the latest bal value doesn't exist further action is necessary
761 --
762 IF l_latest_value_exists = 'F' then
763 --
764 -- for seeded person level dimensions call the core function
765 --
766 IF substr(l_database_item_suffix,1,4) = '_PER'
767 and l_legislation_code = 'GB' THEN
768 BEGIN
769 -- Bug fix 3436701.
770 IF p_effective_date is not null then
771 -- This is a Datemode call for PER level balance
772 l_balance1 := per_datemode_balance(p_assignment_action_id,
773 p_defined_balance_id,
774 l_database_item_suffix,
775 p_effective_date);
776
777 /*For bug fix 4452262*/
778 g_balance := nvl(g_balance,0) + nvl(l_balance1,0);
779 l_balance := g_balance;
780
781
782 ELSE
783 -- Assignment Action mode call as before
784 l_balance := pay_balance_pkg.get_value(p_defined_balance_id,
785 p_assignment_action_id);
786
787
788 END IF;
789 EXCEPTION WHEN NO_DATA_FOUND THEN
790 l_balance := null;
791 END;
792 --
793 ELSE -- Not a person-level balance, either USER-REG OR CALENDAR
794 --
795 -- IMPORTANT NOTE: For user-regs this must never call core
796 -- balance package, must work out route locally.
797 --
798 -- from the dimension work out the from dates
799 -- CALENDAR has no event start
800 --
801 IF l_dimension_name = '_ASG_CALENDAR_YTD' THEN
802 l_period_from_date := TRUNC(l_regular_payment_date,'YYYY');
803 END IF;
804 IF l_dimension_name = '_ASG_CALENDAR_QTD' THEN
805 l_period_from_date := TRUNC(l_regular_payment_date,'Q');
806 END IF;
807 --
808 -- evaluate user-defined (USER-REG) dimensions
809 --
810 IF SUBSTR(l_dimension_name,31,4) = 'USER' THEN
811 l_period_from_date := dimension_reset_date(
812 l_dimension_name,
813 l_regular_payment_date,
814 l_business_group_id);
815 END IF;
816 --
817 -- USER REGS MUST USE THIS GENERIC ROUTE FUNCTION.
818 --
819 l_balance := calc_balance(
820 l_assignment_id,
821 l_balance_type_id,
822 l_period_from_date,
823 l_event_from_date,
824 l_to_date,
825 l_action_sequence);
826 END IF; -- Person Level Balance
827 END IF; -- Latest Balance
828 END IF; -- Core Balance
829 --
830 RETURN l_balance;
831 --
832 END balance;
833 --
834 --------------------------------------------------------------------------------
835 --
836 -- GET_LATEST_ELEMENT_BAL (Private)
837 -- calculate latest balances for element dimensions
838 --
839 --------------------------------------------------------------------------------
840 --
841 FUNCTION get_latest_element_bal(
842 p_assignment_action_id IN NUMBER,
843 p_defined_bal_id IN NUMBER,
844 p_source_id IN NUMBER)
845 --
846 RETURN NUMBER IS
847 --
848 l_balance NUMBER;
849 l_db_item_suffix VARCHAR2(30);
850 l_defined_bal_id NUMBER;
851 --
852 cursor element_latest_bal(c_assignment_action_id IN NUMBER,
853 c_defined_bal_id IN NUMBER,
857 pay_balance_context_values pbcv
854 c_source_id IN NUMBER) is
855 select palb.value
856 from pay_assignment_latest_balances palb,
858 where pbcv.context_id = c_source_id
859 and pbcv.latest_balance_id = palb.latest_balance_id
860 and palb.assignment_action_id = c_assignment_action_id
861 and palb.defined_balance_id = c_defined_bal_id;
862 --
863 BEGIN
864 --
865 open element_latest_bal(p_assignment_action_id,
866 p_defined_bal_id,
867 p_source_id);
868 fetch element_latest_bal into l_balance;
869 close element_latest_bal;
870 --
871 RETURN l_balance;
872 --
873 END get_latest_element_bal;
874
875 --
876 -----------------------------------------------------------------------------
877 --
878 -- CALC_ELEMENT_CO_REF_ITD_BAL
879 -----------------------------------------------------------------------------
880 --
881 /* For bug fix 4452262*/
882 FUNCTION calc_element_co_itd_bal(p_assignment_action_id IN NUMBER,
883 p_balance_type_id IN NUMBER,
884 p_source_id IN NUMBER,
885 p_source_text IN VARCHAR2)
886 RETURN NUMBER IS
887 --
888 l_balance NUMBER;
889 l_defined_bal_id NUMBER;
890 l_context NUMBER;
891 --
892
893 cursor get_context_id is
894 SELECT CONTEXT_ID
895 FROM FF_CONTEXTS
896 where context_name ='SOURCE_TEXT';
897
898 BEGIN
899 --
900 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ELEMENT_CO_REF_ITD');
901
902 open get_context_id;
903 fetch get_context_id into l_context;
904 close get_context_id;
905
906 if l_defined_bal_id is not null then
907
908 l_balance := get_latest_element_bal(p_assignment_action_id,
909 l_defined_bal_id,
910 p_source_id);
911 if l_balance is null then
912 pay_balance_pkg.set_context('ORIGINAL_ENTRY_ID'
913 , p_source_id);
914 pay_balance_pkg.set_context('SOURCE_TEXT'
915 , p_source_text);
916
917 l_balance := pay_balance_pkg.get_value(l_defined_bal_id, p_assignment_action_id);
918 end if;
919 else l_balance := null;
920 --
921 end if;
922 --
923 RETURN l_balance;
924 --
925 END calc_element_co_itd_bal;
926 -----------------------------------------------------------------------------
927 --
928 -- CALC_ELEMENT_ITD_BAL
929 -----------------------------------------------------------------------------
930 --
931 FUNCTION calc_element_itd_bal(p_assignment_action_id IN NUMBER,
932 p_balance_type_id IN NUMBER,
933 p_source_id IN NUMBER)
934 RETURN NUMBER IS
935 --
936 l_balance NUMBER;
937 l_defined_bal_id NUMBER;
938 --
939 BEGIN
940 --
941 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ELEMENT_ITD');
942 if l_defined_bal_id is not null then
943 l_balance := get_latest_element_bal(p_assignment_action_id,
944 l_defined_bal_id,
945 p_source_id);
946 if l_balance is null then
947 pay_balance_pkg.set_context('ORIGINAL_ENTRY_ID'
948 , p_source_id);
949 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
950 p_assignment_action_id);
951 --
952 end if;
953 else l_balance := null;
954 --
955 end if;
956 --
957 RETURN l_balance;
958 --
959 END calc_element_itd_bal;
960 --
961 -----------------------------------------------------------------------------
962 --
963 -- CALC_ELEMENT_PTD_BAL
964 -----------------------------------------------------------------------------
965 --
966 FUNCTION calc_element_ptd_bal(p_assignment_action_id IN NUMBER,
967 p_balance_type_id IN NUMBER,
968 p_source_id IN NUMBER)
969 RETURN NUMBER IS
970 --
971 l_balance NUMBER;
972 l_defined_bal_id NUMBER;
973 --
974 BEGIN
975 --
976 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ELEMENT_PTD');
977 if l_defined_bal_id is not null then
978
979 l_balance := get_latest_element_bal(p_assignment_action_id,
980 l_defined_bal_id,
981 p_source_id);
982 if l_balance is null then
983 --
984 pay_balance_pkg.set_context('ORIGINAL_ENTRY_ID'
985 , p_source_id);
986 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
987 p_assignment_action_id);
988 end if;
989 else l_balance := null;
990 --
991 end if;
992 --
993 RETURN l_balance;
994 --
995 END calc_element_ptd_bal;
996 --
997 -----------------------------------------------------------------------------
998 --
999 -- CALC_ALL_BALANCES
1003 -----------------------------------------------------------------------------
1000 -- This is the generic overloaded function for calculating all balances
1001 -- in assignment action mode. NB Element level balances cannot be called
1002 -- from here as they require further context.
1004 --
1005 FUNCTION calc_all_balances(
1006 p_assignment_action_id IN NUMBER,
1007 p_defined_balance_id IN NUMBER)
1008 --
1009 RETURN NUMBER
1010 IS
1011 --
1012 l_balance NUMBER;
1013 l_balance_type_id NUMBER;
1014 l_dimension_name VARCHAR2(80);
1015 --
1016 cursor get_balance_type_id(c_defined_balance_id IN NUMBER) IS
1017 select pdb.balance_type_id,
1018 pbd.dimension_name
1019 from pay_balance_dimensions pbd,
1020 pay_defined_balances pdb
1021 where pdb.defined_balance_id = c_defined_balance_id
1022 and pdb.balance_dimension_id = pbd.balance_dimension_id;
1023 --
1024 BEGIN
1025 --
1026 open get_balance_type_id(p_defined_balance_id);
1027 FETCH get_balance_type_id INTO
1028 l_balance_type_id, l_dimension_name;
1029 CLOSE get_balance_type_id;
1030 --
1031 If l_dimension_name like '%_ASG_YTD' then
1032 l_balance := calc_asg_ytd_action(p_assignment_action_id,
1033 l_balance_type_id);
1034 Elsif l_dimension_name like '%_ASG_PROC_YTD' then
1035 l_balance := calc_asg_proc_ytd_action(p_assignment_action_id,
1036 l_balance_type_id);
1037 Elsif l_dimension_name like '%_ASG_RUN' then
1038 l_balance := calc_asg_run_action(p_assignment_action_id,
1039 l_balance_type_id);
1040 Elsif l_dimension_name like '%_ASG_TD_YTD' then
1041 l_balance := calc_asg_td_ytd_action(p_assignment_action_id,
1042 l_balance_type_id);
1043 Elsif l_dimension_name like '%_ASG_ITD' then
1044 l_balance := calc_asg_itd_action(p_assignment_action_id,
1045 l_balance_type_id);
1046 Elsif l_dimension_name like '%_ASG_QTD' then
1047 l_balance := calc_asg_qtd_action(p_assignment_action_id,
1048 l_balance_type_id);
1049 Elsif l_dimension_name like '%_ASG_STAT_YTD' then
1050 l_balance := calc_asg_stat_ytd_action(p_assignment_action_id,
1051 l_balance_type_id);
1052 Elsif l_dimension_name like '%_ASG_PROC_PTD' then
1053 l_balance := calc_asg_proc_ptd_action(p_assignment_action_id,
1054 l_balance_type_id);
1055 Elsif l_dimension_name like '%_ASG_TD_ITD' then
1056 l_balance := calc_asg_td_itd_action(p_assignment_action_id,
1057 l_balance_type_id);
1058 Elsif l_dimension_name like '%_ASG_TRANSFER_PTD' then
1059 l_balance := calc_asg_tfr_ptd_action(p_assignment_action_id,
1060 l_balance_type_id);
1061 --
1062 -- added odd and even by skutteti
1063 --
1064 Elsif l_dimension_name like '%_ASG_TD_ODD_TWO_YTD' then
1065 l_balance := calc_asg_td_odd_two_ytd_action(p_assignment_action_id,
1066 l_balance_type_id);
1067 Elsif l_dimension_name like '%_ASG_TD_EVEN_TWO_YTD' then
1068 l_balance := calc_asg_td_even_two_ytd_actio(p_assignment_action_id,
1069 l_balance_type_id);
1070 Elsif l_dimension_name like '%_PAYMENTS' then
1071 l_balance := calc_payment_action(p_assignment_action_id,
1072 l_balance_type_id);
1073 Elsif l_dimension_name like '%_SOE_RUN' then
1074 l_balance := calc_payment_action(p_assignment_action_id,
1075 l_balance_type_id);
1076 Elsif l_dimension_name like '%_PER_PTD' then
1077 --hr_utility.trace('PER - Action');
1078 l_balance := calc_per_ptd_action(p_assignment_action_id,
1079 l_balance_type_id);
1080 --Else for all other dimensions
1081 Else
1082 l_balance := pay_balance_pkg.get_value(p_defined_balance_id,
1083 p_assignment_action_id);
1084 End If;
1085 --
1086 RETURN l_balance;
1087 --
1088 END calc_all_balances;
1089 --
1090 -----------------------------------------------------------------------------
1091 --
1092 -- CALC_ALL_BALANCES
1093 --
1094 -- This is the overloaded generic function for calculating all balances
1095 -- in Date Mode. NB Element level balances cannot be obtained from here as
1096 -- they require further context.
1097 -- This now calls the Core balance package, which could not be called directly
1098 -- from a view in date mode as core date-mode creates an asg action.
1099 -----------------------------------------------------------------------------
1100 --
1101 FUNCTION calc_all_balances(
1102 p_effective_date IN DATE,
1103 p_assignment_id IN NUMBER,
1104 p_defined_balance_id IN NUMBER)
1105 --
1106 RETURN NUMBER
1107 IS
1108 --
1109 l_balance NUMBER;
1113 l_dimension_name VARCHAR2(80);
1110 l_balance1 NUMBER;
1111 l_balance_type_id NUMBER;
1112 l_route_id NUMBER;
1114 l_assignment_action_id NUMBER;
1115 l_context_name VARCHAR2(80);
1116 l_context_value VARCHAR2(80);
1117 --
1118 cursor get_balance_type_id(c_defined_balance_id IN NUMBER) IS
1119 select pdb.balance_type_id,
1120 pbd.dimension_name,
1121 pbd.route_id
1122 from pay_balance_dimensions pbd,
1123 pay_defined_balances pdb
1124 where pdb.defined_balance_id = c_defined_balance_id
1125 and pdb.balance_dimension_id = pbd.balance_dimension_id;
1126 --
1127 cursor get_context(p_route_id IN NUMBER,
1128 p_act_id IN NUMBER,
1129 p_context_01 IN VARCHAR2,
1130 p_context_02 IN VARCHAR2
1131 )
1132 is
1133 select pca.context_value,
1134 ffc.context_name
1135 from pay_action_contexts pca,
1136 ff_contexts ffc,
1137 ff_route_context_usages frc,
1138 pay_balance_dimensions pbd
1139 where pbd.route_id = p_route_id
1140 and pbd.route_id = frc.route_id
1141 and frc.context_id = ffc.context_id
1142 and ffc.context_id = pca.context_id
1143 and pca.assignment_action_id = p_act_id
1144 and (ffc.context_name = p_context_01 OR ffc.context_name = p_context_02)
1145 and (ffc.context_name <> 'SOURCE_TEXT'
1146 or (ffc.context_name = 'SOURCE_TEXT' AND
1147 exists ( select 1
1148 from pay_run_results rr,
1149 pay_run_result_values rrv,
1150 pay_input_values_f piv,
1151 pay_element_types_f petf
1152 where rr.assignment_action_id = pca.assignment_action_id
1153 and rr.element_type_id = petf.element_type_id
1154 and rr.run_result_id = rrv.run_result_id
1155 and piv.input_value_id = rrv.input_value_id
1156 and piv.name = 'Reference'
1157 and nvl(rrv.result_value, 'Unknown') = pca.context_value
1158 and petf.element_name IN
1159 (
1160 'CAO Scotland', 'CAO Scotland NTPP', 'CMA Scotland', 'CMA Scotland NTPP', 'Court Order',
1161 'Court Order NTPP', 'Court Order Non Priority', 'Court Order Non Priority NTPP',
1162 'EAS Scotland', 'EAS Scotland NTPP', 'Setup Court Order Balance'
1163 )
1164 )
1165 )
1166 );
1167 --
1168 BEGIN
1169 --
1170 open get_balance_type_id(p_defined_balance_id);
1171 FETCH get_balance_type_id INTO
1172 l_balance_type_id, l_dimension_name, l_route_id;
1173 CLOSE get_balance_type_id;
1174
1175 -- begin bug fix 4311080
1176 l_assignment_action_id := get_latest_action_id(p_assignment_id, p_effective_date);
1177
1178 /* for Bug 6262406 */
1179 /*
1180 OPEN get_context(l_route_id, l_assignment_action_id);
1181 FETCH get_context INTO l_context_value, l_context_name;
1182 CLOSE get_context;
1183
1184 IF l_context_name = 'SOURCE_TEXT' then
1185 pay_balance_pkg.set_context(l_context_name, l_context_value);
1186 END IF;
1187
1188 -- end bug fix 4311080 */
1189 --
1190 If l_dimension_name like '%_ASG_YTD' then
1191 l_balance := calc_asg_ytd_date(p_assignment_id,
1192 l_balance_type_id,
1193 p_effective_date);
1194 Elsif l_dimension_name like '%_ASG_PROC_YTD' then
1195 l_balance := calc_asg_proc_ytd_date(p_assignment_id,
1196 l_balance_type_id,
1197 p_effective_date);
1198 Elsif l_dimension_name like '%_ASG_RUN' then
1199 l_balance := calc_asg_run_date(p_assignment_id,
1200 l_balance_type_id,
1201 p_effective_date);
1202 Elsif l_dimension_name like '%_ASG_TD_YTD' then
1203 l_balance := calc_asg_td_ytd_date(p_assignment_id,
1204 l_balance_type_id,
1205 p_effective_date);
1206 Elsif l_dimension_name like '%_ASG_ITD' then
1207 l_balance := calc_asg_itd_date(p_assignment_id,
1208 l_balance_type_id,
1209 p_effective_date);
1210 Elsif l_dimension_name like '%_ASG_QTD' then
1211 l_balance := calc_asg_qtd_date(p_assignment_id,
1212 l_balance_type_id,
1213 p_effective_date);
1214 Elsif l_dimension_name like '%_ASG_STAT_YTD' then
1215 l_balance := calc_asg_stat_ytd_date(p_assignment_id,
1216 l_balance_type_id,
1217 p_effective_date);
1218 Elsif l_dimension_name like '%_ASG_PROC_PTD' then
1219 l_balance := calc_asg_proc_ptd_date(p_assignment_id,
1220 l_balance_type_id,
1221 p_effective_date);
1222 Elsif l_dimension_name like '%_ASG_TD_ITD' then
1226 Elsif l_dimension_name like '%_ASG_TRANSFER_PTD' then
1223 l_balance := calc_asg_td_itd_date(p_assignment_id,
1224 l_balance_type_id,
1225 p_effective_date);
1227 l_balance := calc_asg_tfr_ptd_date(p_assignment_id,
1228 l_balance_type_id,
1229 p_effective_date);
1230 Elsif l_dimension_name like '%_ASG_TD_ODD_TWO_YTD' then
1231 l_balance := calc_asg_td_odd_two_ytd_date(p_assignment_id,
1232 l_balance_type_id,
1233 p_effective_date);
1234 Elsif l_dimension_name like '%_ASG_TD_EVEN_TWO_YTD' then
1235 l_balance := calc_asg_td_even_two_ytd_date(p_assignment_id,
1236 l_balance_type_id,
1237 p_effective_date);
1238 Elsif l_dimension_name like '%_PAYMENTS' then
1239 l_balance := calc_payment_date(p_assignment_id,
1240 l_balance_type_id,
1241 p_effective_date);
1242 Elsif l_dimension_name like '%_SOE_RUN' then
1243 l_balance := calc_payment_date(p_assignment_id,
1244 l_balance_type_id,
1245 p_effective_date);
1246 Elsif l_dimension_name like '%_PER_PTD' then
1247 --hr_utility.trace('PER - Date');
1248 l_balance := calc_per_ptd_date(p_assignment_id,
1249 l_balance_type_id,
1250 p_effective_date);
1251 /*For bug fix 4452262*/
1252 /* for Bug 6262406 */
1253 Elsif l_dimension_name like '%_PER_CO_TD_REF_ITD' or l_dimension_name like '%_PER_CO_TD_REF_PTD' then
1254 FOR J IN get_context(l_route_id, l_assignment_action_id, 'SOURCE_TEXT', 'SOURCE_TEXT')
1255 LOOP
1256 IF j.context_name = 'SOURCE_TEXT' then
1257 pay_balance_pkg.set_context(j.context_name, j.context_value);
1258 END IF;
1259 l_balance := balance(l_assignment_action_id, p_defined_balance_id, p_effective_date);
1260 END LOOP;
1261 g_balance := 0;
1262
1263 Elsif l_dimension_name like '%_ELEMENT_ITD' or l_dimension_name like '%_ELEMENT_PTD' or
1264 l_dimension_name like '%_ELEMENT_CO_REF_ITD' then
1265 l_balance := balance(l_assignment_action_id, p_defined_balance_id, p_effective_date);
1266 g_balance := 0;
1267
1268 Else
1269 -- For all other dimensions
1270 -- latest assignment action is set at the top, so comment out this called
1271 -- l_assignment_action_id := get_latest_action_id(p_assignment_id, p_effective_date);
1272 g_balance := 0;
1273 l_balance := balance(l_assignment_action_id,
1274 p_defined_balance_id,
1275 p_effective_date);
1276 End If;
1277 --
1278 RETURN l_balance;
1279 --
1280 END calc_all_balances;
1281
1282 --
1283 -----------------------------------------------------------------------------
1284 --
1285 -- CALC_PER_PTD
1286 -----------------------------------------------------------------------------
1287 --
1288
1289 FUNCTION calc_per_ptd(
1290 p_assignment_action_id IN NUMBER,
1291 p_balance_type_id IN NUMBER,
1292 p_effective_date IN DATE DEFAULT NULL,
1293 p_assignment_id IN NUMBER)
1294 RETURN NUMBER IS
1295 --
1296 l_balance NUMBER;
1297 l_defined_bal_id NUMBER;
1298 --
1299 BEGIN
1300 --
1301 --Do we need to work out a value for this dimension/balance combination.
1302 --
1303 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_PER_PTD');
1304 --hr_utility.trace('PER - Dimension relevant?');
1305 IF l_defined_bal_id IS NOT NULL THEN
1306 --hr_utility.trace('PER - Dimension is relevant');
1307 --
1308 -- Call core balance pkg with the defined balance just retrieved.
1309 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
1310 p_assignment_action_id);
1311
1312 ELSE
1313 --hr_utility.trace('PER - Dimension not relevant');
1314 l_balance := null;
1315 END IF;
1316 RETURN l_balance;
1317 --
1318 END calc_per_ptd;
1319
1320 -----------------------------------------------------------------------
1321 FUNCTION calc_per_ptd_action(
1322 p_assignment_action_id IN NUMBER,
1323 p_balance_type_id IN NUMBER,
1324 p_effective_date IN DATE DEFAULT NULL)
1325 RETURN NUMBER
1326 IS
1327 --
1328 l_assignment_action_id NUMBER;
1329 l_balance NUMBER;
1330 l_assignment_id NUMBER;
1331 l_action_eff_date DATE;
1332 --
1333 BEGIN
1334 --
1335 --
1336 -- Check if assignment action is of type ('R', 'Q', 'I', 'V', 'B')
1337 --
1338 l_assignment_id := get_correct_type(p_assignment_action_id);
1339 --hr_utility.trace('PER - Ass id='||l_assignment_id );
1340 IF l_assignment_id is null THEN
1341 l_balance := null;
1342 ELSE
1343 --hr_utility.trace('PER - get bal');
1344 l_balance := calc_per_ptd(
1345 p_assignment_action_id => p_assignment_action_id,
1349 --hr_utility.trace('PER - Bal='|| l_balance);
1346 p_balance_type_id => p_balance_type_id,
1347 p_effective_date => p_effective_date,
1348 p_assignment_id => l_assignment_id);
1350 END IF;
1351 --
1352 RETURN l_balance;
1353 end calc_per_ptd_action;
1354
1355 -----------------------------------------------------------------------
1356 FUNCTION calc_per_ptd_date(
1357 p_assignment_id IN NUMBER,
1358 p_balance_type_id IN NUMBER,
1359 p_effective_date IN DATE)
1360 RETURN NUMBER
1361 IS
1362 --
1363 l_assignment_action_id NUMBER;
1364 l_balance NUMBER;
1365 l_end_date DATE;
1366 l_action_eff_date DATE;
1367 --
1368 BEGIN
1369 --
1370 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1371 p_effective_date);
1372 --hr_utility.trace('PER - Action id='||l_assignment_action_id );
1373 IF l_assignment_action_id is null then
1374 l_balance := 0;
1375 ELSE
1376 -- Chk date now
1377 l_action_eff_date := get_latest_date(l_assignment_action_id);
1378 --hr_utility.trace('PER - Action dt='||l_action_eff_date );
1379 --
1380 -- Is effective date (sess) later than the action effective date.
1381 --
1382 IF p_effective_date > l_action_eff_date THEN
1383 --hr_utility.trace('PER - Not Getting Bal');
1384 l_balance := 0;
1385 ELSE
1386 --hr_utility.trace('PER - Getting Bal');
1387 l_balance := calc_per_ptd(
1388 p_assignment_action_id => l_assignment_action_id,
1389 p_balance_type_id => p_balance_type_id,
1390 p_effective_date => p_effective_date,
1391 p_assignment_id => p_assignment_id);
1392 --hr_utility.trace('PER - Bal='||l_balance);
1393 END IF;
1394 END IF;
1395 --
1396 RETURN l_balance;
1397 end calc_per_ptd_date;
1398 --
1399 -----------------------------------------------------------------------------
1400 ---
1401 --
1402 -- CALC_ASG_PROC_YTD_ACTION
1403 --
1404 -- This is the function for calculating assignment year to
1405 -- date in asg action mode
1406 --
1407 -----------------------------------------------------------------------------
1408 --
1409 FUNCTION calc_asg_proc_ytd_action(
1410 p_assignment_action_id IN NUMBER,
1411 p_balance_type_id IN NUMBER,
1412 p_effective_date IN DATE)
1413 RETURN NUMBER
1414 IS
1415 --
1416 l_assignment_action_id NUMBER;
1417 l_balance NUMBER;
1418 l_assignment_id NUMBER;
1419 l_effective_date DATE;
1420 --
1421 BEGIN
1422 --
1423 l_assignment_id := get_correct_type(p_assignment_action_id);
1424 IF l_assignment_id is null THEN
1425 --
1426 -- The assignment action is not a payroll or quickpay type, so return null
1427 --
1428 l_balance := null;
1429 ELSE
1430 --
1431 l_balance := calc_asg_proc_ytd(
1432 p_assignment_action_id => p_assignment_action_id,
1433 p_balance_type_id => p_balance_type_id,
1434 p_effective_date => p_effective_date,
1435 p_assignment_id => l_assignment_id);
1436 END IF;
1437 --
1438 RETURN l_balance;
1439 end calc_asg_proc_ytd_action;
1440 --
1441 -----------------------------------------------------------------------------
1442 ---
1443 --
1444 -- CALC_ASG_PROC_YTD_DATE -
1445 --
1446 -- This is the function for calculating assignment proc year to
1447 -- date in date mode
1448 --
1449 -----------------------------------------------------------------------------
1450 --
1451 FUNCTION calc_asg_proc_ytd_date(
1452 p_assignment_id IN NUMBER,
1453 p_balance_type_id IN NUMBER,
1454 p_effective_date IN DATE)
1455 RETURN NUMBER
1456 IS
1457 --
1458 l_assignment_action_id NUMBER;
1459 l_balance NUMBER;
1460 l_end_date DATE;
1461 l_action_eff_date DATE;
1462 --
1463 BEGIN
1464 --
1465 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1466 p_effective_date);
1467 IF l_assignment_action_id is null then
1468 l_balance := 0;
1469 ELSE
1470 -- start expiry chk now
1471 l_action_eff_date := get_latest_date(l_assignment_action_id);
1472 --
1473 -- Is effective date (sess) later than the expiry of the financial year of the
1474 -- effective date.
1475 --
1476 if p_effective_date >= get_expired_year_date(l_action_eff_date) then
1477 l_balance := 0;
1478 else
1479 --
1480 l_balance := calc_asg_proc_ytd(
1481 p_assignment_action_id => l_assignment_action_id,
1482 p_balance_type_id => p_balance_type_id,
1483 p_effective_date => p_effective_date,
1487 --
1484 p_assignment_id => p_assignment_id);
1485 end if;
1486 END IF;
1488 RETURN l_balance;
1489 end calc_asg_proc_ytd_date;
1490 --
1491 --------------------------------------------------------------------------------
1492 --
1493 -- CALC_ASG_PROC_YTD --
1494 -- calculate balances for Assignment process year to date
1495 --
1496 --------------------------------------------------------------------------------
1497 -- Assignment Process Year -
1498 -- This dimension is the total for an assignment within the processing
1499 -- year of his current payroll, OR if the assignment has transferred
1500 -- payroll within the current processing year, it is the total since
1501 -- he joined the current payroll.
1502
1503 -- This dimension should be used for the year dimension of balances
1504 -- which are reset to zero on transferring payroll.
1505 --
1506 FUNCTION calc_asg_proc_ytd(
1507 p_assignment_action_id IN NUMBER,
1508 p_balance_type_id IN NUMBER,
1509 p_effective_date IN DATE DEFAULT NULL,
1510 p_assignment_id IN NUMBER
1511 )
1512 RETURN NUMBER
1513 IS
1514 --
1515 l_balance NUMBER;
1516 l_defined_bal_id NUMBER;
1517 --
1518 BEGIN
1519 --
1520 --Do we need to work out a value for this dimension/balance combination.
1521 --
1522 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_PROC_YTD');
1523 if l_defined_bal_id is not null then
1524 --
1525 -- Call core balance pkg with the defined balance just retrieved.
1526 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
1527 p_assignment_action_id);
1528 --
1529 else l_balance := null;
1530 end if;
1531 RETURN l_balance;
1532 --
1533 END calc_asg_proc_ytd;
1534 -----------------------------------------------------------------------------
1535 ---
1536 --
1537 -- CALC_ASG_QTD_ACTION -
1538 --
1539 -- This is the function for calculating assignment quarter to
1540 -- date in asg action mode
1541 --
1542 -----------------------------------------------------------------------------
1543 --
1544 FUNCTION calc_asg_qtd_action(
1545 p_assignment_action_id IN NUMBER,
1546 p_balance_type_id IN NUMBER,
1547 p_effective_date IN DATE)
1548 RETURN NUMBER
1549 IS
1550 --
1551 l_assignment_action_id NUMBER;
1552 l_balance NUMBER;
1553 l_assignment_id NUMBER;
1554 l_effective_date DATE;
1555 --
1556 BEGIN
1557 --
1558 l_assignment_id := get_correct_type(p_assignment_action_id);
1559 IF l_assignment_id is null THEN
1560 --
1561 -- The assignment action is not a payroll or quickpay type, so return null
1562 --
1563 l_balance := null;
1564 ELSE
1565 --
1566 l_balance := calc_asg_qtd(
1567 p_assignment_action_id => p_assignment_action_id,
1568 p_balance_type_id => p_balance_type_id,
1569 p_effective_date => p_effective_date,
1570 p_assignment_id => l_assignment_id);
1571 END IF;
1572 --
1573 RETURN l_balance;
1574 end calc_asg_qtd_action;
1575 --
1576 -----------------------------------------------------------------------------
1577 ---
1578 --
1579 -- CALC_ASG_QTD_DATE -
1580 --
1581 -- This is the function for calculating assignment quarter
1582 -- to date in DATE MODE
1583 --
1584 -----------------------------------------------------------------------------
1585 --
1586 FUNCTION calc_asg_qtd_date(
1587 p_assignment_id IN NUMBER,
1588 p_balance_type_id IN NUMBER,
1589 p_effective_date IN DATE)
1590 RETURN NUMBER
1591 IS
1592 --
1593 l_assignment_action_id NUMBER;
1594 l_balance NUMBER;
1595 l_conv_us_gb_qd DATE;
1596 l_quarter_expiry_date DATE;
1597 l_action_eff_date DATE;
1598 --
1599 BEGIN
1600 --
1601 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1602 p_effective_date);
1603 IF l_assignment_action_id is null THEN
1604 l_balance := 0;
1605 ELSE
1606 l_balance := calc_asg_qtd(
1607 p_assignment_action_id => l_assignment_action_id,
1608 p_balance_type_id => p_balance_type_id,
1609 p_effective_date => p_effective_date,
1610 p_assignment_id => p_assignment_id);
1611 END IF;
1612 --
1613 RETURN l_balance;
1614 end calc_asg_qtd_date;
1615 --
1616 --------------------------------------------------------------------------------
1617 --
1618 -- CALC_ASG_QTD --
1619 -- calculate balances for Assignment Quarter to date
1620 --
1621 --------------------------------------------------------------------------------
1625 p_assignment_action_id IN NUMBER,
1622 -- This dimension is the total for an assignment within the quarter. It uses
1623 --
1624 FUNCTION calc_asg_qtd(
1626 p_balance_type_id IN NUMBER,
1627 p_effective_date IN DATE DEFAULT NULL,
1628 p_assignment_id IN NUMBER
1629 )
1630 RETURN NUMBER
1631 IS
1632 --
1633 l_balance NUMBER;
1634 l_defined_bal_id NUMBER;
1635 --
1636 BEGIN
1637 --
1638 --Do we need to work out a value for this dimension/balance combination.
1639 --
1640 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_QTD');
1641 if l_defined_bal_id is not null then
1642 --
1643 -- Call core balance pkg with the defined balance just retrieved.
1644 --
1645 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
1646 p_assignment_action_id);
1647 end if;
1648 --
1649 RETURN l_balance;
1650 --
1651 END calc_asg_qtd;
1652 --
1653 -----------------------------------------------------------------------------
1654 ---
1655 --
1656 -- CALC_ASG_YTD_ACTION -
1657 --
1658 -- This is the function for calculating assignment year to
1659 -- date in asg action mode
1660 --
1661 -----------------------------------------------------------------------------
1662 --
1663 FUNCTION calc_asg_ytd_action(
1664 p_assignment_action_id IN NUMBER,
1665 p_balance_type_id IN NUMBER,
1666 p_effective_date IN DATE)
1667 RETURN NUMBER
1668 IS
1669 --
1670 l_assignment_action_id NUMBER;
1671 l_balance NUMBER;
1672 l_assignment_id NUMBER;
1673 l_effective_date DATE;
1674 --
1675 BEGIN
1676 --
1677 l_assignment_id := get_correct_type(p_assignment_action_id);
1678 IF l_assignment_id is null THEN
1679 --
1680 -- The assignment action is not a payroll or quickpay type, so return null
1681 --
1682 l_balance := null;
1683 ELSE
1684 --
1685 l_balance := calc_asg_ytd(
1686 p_assignment_action_id => p_assignment_action_id,
1687 p_balance_type_id => p_balance_type_id,
1688 p_effective_date => p_effective_date,
1689 p_assignment_id => l_assignment_id);
1690 END IF;
1691 --
1692 RETURN l_balance;
1693 end calc_asg_ytd_action;
1694 --
1695 -----------------------------------------------------------------------------
1696 ---
1697 --
1698 -- CALC_ASG_YTD_DATE -
1699 --
1700 -- This is the function for calculating assignment year to
1701 -- date in date mode
1702 --
1703 -----------------------------------------------------------------------------
1704 --
1705 FUNCTION calc_asg_ytd_date(
1706 p_assignment_id IN NUMBER,
1707 p_balance_type_id IN NUMBER,
1708 p_effective_date IN DATE)
1709 RETURN NUMBER
1710 IS
1711 --
1712 l_assignment_action_id NUMBER;
1713 l_balance NUMBER;
1714 l_end_date DATE;
1715 l_action_eff_date DATE;
1716 --
1717 BEGIN
1718 --
1719 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1720 p_effective_date);
1721 IF l_assignment_action_id is null THEN
1722 l_balance := 0;
1723 ELSE
1724 -- start expiry chk now
1725 l_action_eff_date := get_latest_date(l_assignment_action_id);
1726 --
1727 -- Is effective date (sess) later than the expiry of the financial year of the
1728 -- effective date.
1729 --
1730 if p_effective_date >= get_expired_year_date(l_action_eff_date) then
1731 l_balance := 0;
1732 else
1733 --
1734 l_balance := calc_asg_ytd(
1735 p_assignment_action_id => l_assignment_action_id,
1736 p_balance_type_id => p_balance_type_id,
1737 p_effective_date => p_effective_date,
1738 p_assignment_id => p_assignment_id);
1739 end if;
1740 END IF;
1741 --
1742 RETURN l_balance;
1743 end calc_asg_ytd_date;
1744 --
1745 --------------------------------------------------------------------------------
1746 --
1747 -- CALC_ASG_YTD --
1748 -- calculate balances for Assignment year to date
1749 -- Call core balance package.
1750 --------------------------------------------------------------------------------
1751 --
1752 -- Assignment Year -
1753 --
1754 -- This dimension is the total for an assignment within the processing
1755 -- year of any payrolls he has been on this year. That is in the case
1756 -- of a transfer the span will go back to the start of the processing
1757 -- year he was on at the start of year.
1758 --
1759 -- This dimension should be used for the year dimension of balances
1760 -- which are not reset to zero on transferring payroll.
1761 -- If this has been called from the date mode function, the effective date
1765 p_assignment_action_id IN NUMBER,
1762 -- will be set, otherwise session date is used.
1763 --
1764 FUNCTION calc_asg_ytd(
1766 p_balance_type_id IN NUMBER,
1767 p_effective_date IN DATE DEFAULT NULL,
1768 p_assignment_id IN NUMBER
1769 )
1770 RETURN NUMBER
1771 IS
1772 --
1773 l_expired_balance NUMBER;
1774 l_balance NUMBER;
1775 l_session_date DATE;
1776 l_assignment_id NUMBER;
1777 l_action_eff_date DATE;
1778 l_latest_value_exists VARCHAR2(2);
1779 l_assignment_action_id NUMBER;
1780 l_defined_bal_id NUMBER;
1781 --
1782 BEGIN
1783 --
1784 -- Similar to proc yr, we need to find out firstly whether there is a
1785 -- value in latest balances, and then find out whether this can be used.
1786 -- The latest balances table is then checked again to see whether there was
1787 -- a value in the past, not necessarily for this assignment action, and whether
1788 -- it is valid.
1789 -- If not, the route code will be used to calculate the correct balance figure.
1790 --
1791 --Do we need to work out a value for this dimension/balance combination.
1792 --
1793 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_YTD');
1794 if l_defined_bal_id is not null then
1795 --
1796 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
1797 p_assignment_action_id);
1798 --
1799 else l_balance := null;
1800 end if;
1801 --
1802 RETURN l_balance;
1803 --
1804 END calc_asg_ytd;
1805 --
1806 -----------------------------------------------------------------------------
1807 ---
1808 --
1809 -- CALC_ASG_STAT_YTD_ACTION -
1810 --
1811 -- This is the function for calculating assignment stat. year to
1812 -- date in asg action mode
1813 --
1814 -----------------------------------------------------------------------------
1815 --
1816 FUNCTION calc_asg_stat_ytd_action(
1817 p_assignment_action_id IN NUMBER,
1818 p_balance_type_id IN NUMBER,
1819 p_effective_date IN DATE)
1820 RETURN NUMBER
1821 IS
1822 --
1823 l_assignment_action_id NUMBER;
1824 l_balance NUMBER;
1825 l_assignment_id NUMBER;
1826 l_effective_date DATE;
1827 --
1828 BEGIN
1829 --
1830 l_assignment_id := get_correct_type(p_assignment_action_id);
1831 IF l_assignment_id is null THEN
1832 --
1833 -- The assignment action is not a payroll or quickpay type, so return null
1834 --
1835 l_balance := null;
1836 --
1837 ELSE
1838 --
1839 l_balance := calc_asg_stat_ytd(
1840 p_assignment_action_id => p_assignment_action_id,
1841 p_balance_type_id => p_balance_type_id,
1842 p_effective_date => p_effective_date,
1843 p_assignment_id => l_assignment_id);
1844 END IF;
1845 --
1846 RETURN l_balance;
1847 end calc_asg_stat_ytd_action;
1848 --
1849 -----------------------------------------------------------------------------
1850 ---
1851 --
1852 -- CALC_ASG_STAT_YTD_DATE -
1853 --
1854 -- This is the function for calculating assignment stat. year to
1855 -- date in date mode
1856 --
1857 -----------------------------------------------------------------------------
1858 --
1859 FUNCTION calc_asg_stat_ytd_date(
1860 p_assignment_id IN NUMBER,
1861 p_balance_type_id IN NUMBER,
1862 p_effective_date IN DATE)
1863 RETURN NUMBER
1864 IS
1865 --
1866 l_assignment_action_id NUMBER;
1867 l_balance NUMBER;
1868 l_end_date DATE;
1869 l_action_eff_date DATE;
1870 --
1871 BEGIN
1872 --
1873 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1874 p_effective_date);
1875 IF l_assignment_action_id is null THEN
1876 l_balance := 0;
1877 ELSE
1878 -- start expiry chk now
1879 l_action_eff_date := get_latest_date(l_assignment_action_id);
1880 --
1881 -- Is effective date (sess) later than the expiry of the financial year of the
1882 -- effective date.
1883 --
1884 if p_effective_date >= get_expired_year_date(l_action_eff_date) then
1885 l_balance := 0;
1886 else
1887 --
1888 l_balance := calc_asg_stat_ytd(
1889 p_assignment_action_id => l_assignment_action_id,
1890 p_balance_type_id => p_balance_type_id,
1891 p_effective_date => p_effective_date,
1892 p_assignment_id => p_assignment_id);
1893 end if;
1894 END IF;
1895 --
1896 RETURN l_balance;
1897 end calc_asg_stat_ytd_date;
1898 --
1899 --------------------------------------------------------------------------------
1900 --
1901 -- CALC_ASG_STAT_YTD --
1902 -- calculate balances for Assignment stat year to date
1903 --
1904 --------------------------------------------------------------------------------
1905 --
1906 -- This dimension is the total for an assignment within the statutory
1907 -- year (since the previous 6th April)of any payrolls he has been on this year
1908 --
1909 FUNCTION calc_asg_stat_ytd(
1910 p_assignment_action_id IN NUMBER,
1911 p_balance_type_id IN NUMBER,
1912 p_effective_date IN DATE DEFAULT NULL,
1913 p_assignment_id IN NUMBER
1914 )
1915 RETURN NUMBER
1916 IS
1917 --
1918 l_expired_balance NUMBER;
1919 l_balance NUMBER;
1920 l_session_date DATE;
1921 l_assignment_id NUMBER;
1922 l_action_eff_date DATE;
1923 l_latest_value_exists VARCHAR2(2);
1924 l_assignment_action_id NUMBER;
1925 l_defined_bal_id NUMBER;
1926 --
1927 BEGIN
1928 --
1929 --Do we need to work out a value for this dimension/balance combination.
1930 --
1931 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_STAT_YTD');
1932 if l_defined_bal_id is not null then
1933 --
1934 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
1935 p_assignment_action_id);
1936 --
1937 else l_balance := null;
1938 end if;
1939 --
1940 RETURN l_balance;
1941 --
1942 END calc_asg_stat_ytd;
1943 --
1944 -----------------------------------------------------------------------------
1945 ---
1946 --
1947 -- CALC_ASG_PROC_PTD_ACTION
1948 --
1949 -- This is the function for calculating assignment
1950 -- proc. period to date in assignment action mode
1951 --
1952 -----------------------------------------------------------------------------
1953 --
1954 FUNCTION calc_asg_proc_ptd_action(
1955 p_assignment_action_id IN NUMBER,
1956 p_balance_type_id IN NUMBER,
1957 p_effective_date IN DATE)
1958 RETURN NUMBER
1959 IS
1960 --
1961 l_assignment_action_id NUMBER;
1962 l_balance NUMBER;
1963 l_assignment_id NUMBER;
1964 l_effective_date DATE;
1965 --
1966 BEGIN
1967 --
1968 l_assignment_id := get_correct_type(p_assignment_action_id);
1969 IF l_assignment_id is null THEN
1970 --
1971 -- The assignment action is not a payroll or quickpay type, so return null
1972 --
1973 l_balance := null;
1974 ELSE
1975 --
1976 l_balance := calc_asg_proc_ptd(
1977 p_assignment_action_id => p_assignment_action_id
1978 ,
1979 p_balance_type_id => p_balance_type_id,
1980 p_effective_date => p_effective_date,
1981 p_assignment_id => l_assignment_id);
1982 END IF;
1983 --
1984 RETURN l_balance;
1985 end calc_asg_proc_ptd_action;
1986 --
1987 -----------------------------------------------------------------------------
1988 ---
1989 --
1990 -- CALC_ASG_PROC_PTD_DATE
1991 --
1992 -- This is the function for calculating assignment processing
1993 -- period to date in date mode
1994 --
1995 -----------------------------------------------------------------------------
1996 --
1997 FUNCTION calc_asg_proc_ptd_date(
1998 p_assignment_id IN NUMBER,
1999 p_balance_type_id IN NUMBER,
2000 p_effective_date IN DATE)
2001 RETURN NUMBER
2002 IS
2003 --
2004 l_assignment_action_id NUMBER;
2005 l_balance NUMBER;
2006 l_period_end_date DATE;
2007 l_date_paid DATE;
2008 --
2009 -- Has the processing time period expired
2010 --
2011 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2012 SELECT ptp.end_date, ppa.effective_date
2013 FROM per_time_periods ptp,
2014 pay_payroll_actions ppa,
2015 pay_assignment_actions paa
2016 WHERE
2017 paa.assignment_action_id = c_assignment_action_id
2018 AND paa.payroll_action_id = ppa.payroll_action_id
2019 AND ppa.time_period_id = ptp.time_period_id;
2020 --
2021 BEGIN
2022 --
2023 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2024 p_effective_date);
2025 IF l_assignment_action_id is null THEN
2026 l_balance := 0;
2027 ELSE
2028 open expired_time_period(l_assignment_action_id);
2029 FETCH expired_time_period INTO l_period_end_date, l_date_paid;
2030 close expired_time_period;
2031 --
2032 if greatest(l_period_end_date,l_date_paid) < p_effective_date then
2033 l_balance := 0;
2034 else
2035 l_balance := calc_asg_proc_ptd(
2036 p_assignment_action_id => l_assignment_action_id,
2037 p_balance_type_id => p_balance_type_id,
2038 p_effective_date => p_effective_date,
2039 p_assignment_id => p_assignment_id);
2040 end if;
2041 END IF;
2042 --
2043 RETURN l_balance;
2044 end calc_asg_proc_ptd_date;
2048 --
2045 --
2046 -----------------------------------------------------------------------------
2047 ---
2049 -- CALC_ASG_PROC_PTD -
2050 -- calculate balances for Assignment process period to date
2051 -- Calls Core Balance pkg.
2052 --
2053 -----------------------------------------------------------------------------
2054 ---
2055 --
2056 -- This dimension is the total for an assignment within the processing
2057 -- period of his current payroll, OR if the assignment has transferred
2058 -- payroll within the current processing period, it is the total since
2059 -- he joined the current payroll.
2060 --
2061 -- This dimension should be used for the period dimension of balances
2062 -- which are reset to zero on transferring payroll.
2063 --
2064 FUNCTION calc_asg_proc_ptd(
2065 p_assignment_action_id IN NUMBER,
2066 p_balance_type_id IN NUMBER,
2067 p_effective_date IN DATE DEFAULT NULL,
2068 p_assignment_id IN NUMBER
2069 )
2070 --
2071 RETURN NUMBER
2072 IS
2073 --
2074 --
2075 l_expired_balance NUMBER;
2076 l_assignment_action_id NUMBER;
2077 l_balance NUMBER;
2078 l_latest_value_exists VARCHAR2(2);
2079 l_action_eff_date DATE;
2080 l_end_date DATE;
2081 l_defined_bal_id NUMBER;
2082 --
2083 BEGIN
2084 --
2085 --Do we need to work out a value for this dimension/balance combination.
2086 --
2087 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_PROC_PTD');
2088 if l_defined_bal_id is not null then
2089 --
2090 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2091 p_assignment_action_id);
2092 --
2093 else l_balance := null;
2094 end if;
2095 --
2096 RETURN l_balance;
2097 --
2098 END calc_asg_proc_ptd;
2099 --
2100 --
2101 -----------------------------------------------------------------------------
2102 ---
2103 --
2104 -- CALC_ASG_RUN_ACTION -
2105 --
2106 -- This is the function for calculating assignment
2107 -- runs in assignment action mode
2108 --
2109 -----------------------------------------------------------------------------
2110 --
2111 FUNCTION calc_asg_run_action(
2112 p_assignment_action_id IN NUMBER,
2113 p_balance_type_id IN NUMBER,
2114 p_effective_date IN DATE)
2115 RETURN NUMBER
2116 IS
2117 --
2118 l_assignment_action_id NUMBER;
2119 l_balance NUMBER;
2120 l_assignment_id NUMBER;
2121 l_effective_date DATE;
2122 --
2123 BEGIN
2124 --
2125 l_assignment_id := get_correct_type(p_assignment_action_id);
2126 IF l_assignment_id is null THEN
2127 --
2128 -- The assignment action is not a payroll or quickpay type, so return null
2129 --
2130 l_balance := null;
2131 ELSE
2132 --
2133 l_balance := calc_asg_run(
2134 p_assignment_action_id => p_assignment_action_id
2135 ,
2136 p_balance_type_id => p_balance_type_id,
2137 p_effective_date => p_effective_date,
2138 p_assignment_id => l_assignment_id);
2139 END IF;
2140 --
2141 RETURN l_balance;
2142 end calc_asg_run_action;
2143 --
2144 -----------------------------------------------------------------------------
2145 ---
2146 --
2147 -- CALC_ASG_RUN_DATE -
2148 --
2149 -- This is the function for calculating assignment run in
2150 -- DATE MODE
2151 --
2152 -----------------------------------------------------------------------------
2153 --
2154 FUNCTION calc_asg_run_date(
2155 p_assignment_id IN NUMBER,
2156 p_balance_type_id IN NUMBER,
2157 p_effective_date IN DATE)
2158 RETURN NUMBER
2159 IS
2160 --
2161 l_assignment_action_id NUMBER;
2162 l_balance NUMBER;
2163 l_period_end_date DATE;
2164 l_date_paid DATE;
2165 --
2166 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2167 SELECT ptp.end_date, ppa.effective_date
2168 FROM per_time_periods ptp,
2169 pay_payroll_actions ppa,
2170 pay_assignment_actions paa
2171 WHERE
2172 paa.assignment_action_id = c_assignment_action_id
2173 AND paa.payroll_action_id = ppa.payroll_action_id
2174 AND ppa.time_period_id = ptp.time_period_id;
2175 --
2176 BEGIN
2177 --
2178 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2179 p_effective_date);
2180 IF l_assignment_action_id is null THEN
2181 l_balance := 0;
2182 ELSE
2183 open expired_time_period(l_assignment_action_id);
2184 FETCH expired_time_period INTO l_period_end_date, l_date_paid;
2185 close expired_time_period;
2186 --
2187 if greatest(l_period_end_date,l_date_paid) < p_effective_date then
2188 l_balance := 0;
2189 else
2190 l_balance := calc_asg_run(
2194 p_assignment_id => p_assignment_id);
2191 p_assignment_action_id => l_assignment_action_id,
2192 p_balance_type_id => p_balance_type_id,
2193 p_effective_date => p_effective_date,
2195 end if;
2196 END IF;
2197 --
2198 RETURN l_balance;
2199 end calc_asg_run_date;
2200 --
2201 -----------------------------------------------------------------------------
2202 ---
2203 --
2204 -- CALC_ASG_RUN -
2205 -- calculate balances for Assignment Run . Now calls core package.
2206 --
2207 -----------------------------------------------------------------------------
2208 --
2209 -- Run
2210 -- the simplest dimension retrieves run values where the context
2211 -- is this assignment action and this balance feed. Balance is the
2212 -- specified input value. The related payroll action determines the
2213 -- date effectivity of the feeds
2214 --
2215 FUNCTION calc_asg_run(
2216 p_assignment_action_id IN NUMBER,
2217 p_balance_type_id IN NUMBER,
2218 p_effective_date IN DATE DEFAULT NULL,
2219 p_assignment_id IN NUMBER
2220 )
2221 RETURN NUMBER
2222 IS
2223 --
2224 --
2225 l_balance NUMBER;
2226 l_defined_bal_id NUMBER;
2227 --
2228 BEGIN
2229 --
2230 --Do we need to work out a value for this dimension/balance combination.
2231 --
2232 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_RUN');
2233 if l_defined_bal_id is not null then
2234 --
2235 -- Call core balance pkg with the defined balance just retrieved.
2236 --
2237 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2238 p_assignment_action_id);
2239 --
2240 else l_balance := null;
2241 end if;
2242 --
2243 RETURN l_balance;
2244 --
2245 END calc_asg_run;
2246 --
2247 -----------------------------------------------------------------------------
2248 --
2249 -- CALC_PAYMENT_ACTION -
2250 --
2251 -- This is the function for calculating payments
2252 -- in assignment action mode
2253 -----------------------------------------------------------------------------
2254 --
2255 FUNCTION calc_payment_action(
2256 p_assignment_action_id IN NUMBER,
2257 p_balance_type_id IN NUMBER,
2258 p_effective_date IN DATE)
2259 RETURN NUMBER
2260 IS
2261 --
2262 l_assignment_action_id NUMBER;
2263 l_balance NUMBER;
2264 l_assignment_id NUMBER;
2265 l_effective_date DATE;
2266 --
2267 BEGIN
2268 --
2269 l_assignment_id := get_correct_type(p_assignment_action_id);
2270 IF l_assignment_id is null THEN
2271 --
2272 -- The assignment action is not a payroll or quickpay type, so return null
2273 --
2274 l_balance := null;
2275 ELSE
2276 --
2277 l_balance := calc_payment(
2278 p_assignment_action_id => p_assignment_action_id
2279 ,
2280 p_balance_type_id => p_balance_type_id,
2281 p_effective_date => p_effective_date,
2282 p_assignment_id => l_assignment_id);
2283 END IF;
2284 --
2285 RETURN l_balance;
2286 end calc_payment_action;
2287 --
2288 -----------------------------------------------------------------------------
2289 --
2290 -- CALC_PAYMENT_DATE -
2291 --
2292 -- This is the function for calculating payments in
2293 -- DATE MODE
2294 -----------------------------------------------------------------------------
2295 --
2296 FUNCTION calc_payment_date(
2297 p_assignment_id IN NUMBER,
2298 p_balance_type_id IN NUMBER,
2299 p_effective_date IN DATE)
2300 RETURN NUMBER
2301 IS
2302 --
2303 l_assignment_action_id NUMBER;
2304 l_balance NUMBER;
2305 l_period_end_date DATE;
2306 l_date_paid DATE;
2307 --
2308 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2309 SELECT ptp.end_date, ppa.effective_date
2310 FROM per_time_periods ptp,
2311 pay_payroll_actions ppa,
2312 pay_assignment_actions paa
2313 WHERE
2314 paa.assignment_action_id = c_assignment_action_id
2315 AND paa.payroll_action_id = ppa.payroll_action_id
2316 AND ppa.time_period_id = ptp.time_period_id;
2317 --
2318 BEGIN
2319 --
2320 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2321 p_effective_date);
2322 IF l_assignment_action_id is null THEN
2323 l_balance := 0;
2324 ELSE
2325 open expired_time_period(l_assignment_action_id);
2326 FETCH expired_time_period INTO l_period_end_date, l_date_paid;
2327 close expired_time_period;
2328 --
2329 if greatest(l_period_end_date,l_date_paid) < p_effective_date then
2330 l_balance := 0;
2331 else
2335 p_effective_date => p_effective_date,
2332 l_balance := calc_payment(
2333 p_assignment_action_id => l_assignment_action_id,
2334 p_balance_type_id => p_balance_type_id,
2336 p_assignment_id => p_assignment_id);
2337 end if;
2338 END IF;
2339 --
2340 RETURN l_balance;
2341 end calc_payment_date;
2342 --
2343 -----------------------------------------------------------------------------
2344 --
2345 -- CALC_PAYMENT -
2346 --
2347 -- calculate balances for payments . Now calls core package.
2348 -----------------------------------------------------------------------------
2349 --
2350 -- this dimension is used in the pre-payments process - that process
2351 -- creates interlocks for the actions that are included and the payments
2352 -- dimension uses those interlocks to decide which run results to sum
2353 --
2354 --
2355 FUNCTION calc_payment(
2356 p_assignment_action_id IN NUMBER,
2357 p_balance_type_id IN NUMBER,
2358 p_effective_date IN DATE DEFAULT NULL,
2359 p_assignment_id IN NUMBER
2360 )
2361 RETURN NUMBER
2362 IS
2363 --
2364 l_balance NUMBER;
2365 l_latest_value_exists VARCHAR2(2);
2366 l_assignment_action_id NUMBER;
2367 l_action_eff_date DATE;
2368 l_end_date DATE;
2369 l_defined_bal_id NUMBER;
2370 --
2371 BEGIN
2372 --
2373 --Do we need to work out a value for this dimension/balance combination.
2374 --
2375 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_PAYMENTS');
2376 if l_defined_bal_id is not null then
2377 --
2378 -- Call core balance pkg with the defined balance just retrieved.
2379 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2380 p_assignment_action_id);
2381 --
2382 else l_balance := null;
2383 end if;
2384 --
2385 RETURN l_balance;
2386 --
2387 END calc_payment;
2388 --
2389 -----------------------------------------------------------------------------
2390 --
2391 -- CALC_ASG_ITD_ACTION -
2392 --
2393 -- This is the function for calculating assignment
2394 -- Inception to date in assignment action mode
2395 -----------------------------------------------------------------------------
2396 --
2397 FUNCTION calc_asg_itd_action(
2398 p_assignment_action_id IN NUMBER,
2399 p_balance_type_id IN NUMBER,
2400 p_effective_date IN DATE)
2401 RETURN NUMBER
2402 IS
2403 --
2404 l_assignment_action_id NUMBER;
2405 l_balance NUMBER;
2406 l_assignment_id NUMBER;
2407 l_effective_date DATE;
2408 --
2409 BEGIN
2410 --
2411 l_assignment_id := get_correct_type(p_assignment_action_id);
2412 IF l_assignment_id is null THEN
2413 --
2414 -- The assignment action is not a payroll or quickpay type, so return null
2415 --
2416 l_balance := null;
2417 ELSE
2418 --
2419 l_balance := calc_asg_itd(p_assignment_id => l_assignment_id,
2420 p_assignment_action_id => p_assignment_action_id,
2421 p_balance_type_id => p_balance_type_id,
2422 p_effective_date => p_effective_date);
2423 END IF;
2424 --
2425 RETURN l_balance;
2426 end calc_asg_itd_action;
2427 --
2428 -----------------------------------------------------------------------------
2429 --
2430 -- CALC_ASG_ITD_DATE -
2431 --
2432 -- This is the function for calculating assignment inception to
2433 -- date in DATE MODE
2434 -----------------------------------------------------------------------------
2435 --
2436 FUNCTION calc_asg_itd_date(
2437 p_assignment_id IN NUMBER,
2438 p_balance_type_id IN NUMBER,
2439 p_effective_date IN DATE)
2440 RETURN NUMBER
2441 IS
2442 --
2443 l_assignment_action_id NUMBER;
2444 l_balance NUMBER;
2445 l_end_date DATE;
2446 --
2447 BEGIN
2448 --
2449 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2450 p_effective_date);
2451 IF l_assignment_action_id is null THEN
2452 l_balance := 0;
2453 ELSE
2454 l_balance := calc_asg_itd(
2455 p_assignment_id => p_assignment_id,
2456 p_assignment_action_id => l_assignment_action_id,
2457 p_balance_type_id => p_balance_type_id,
2458 p_effective_date => p_effective_date);
2459 END IF;
2460 --
2461 RETURN l_balance;
2462 end calc_asg_itd_date;
2463 --
2464 -----------------------------------------------------------------------------
2465 --
2466 -- CALC_ASG_ITD -
2467 --
2468 -- calculate balances for Assignment Inception to Date
2472 --
2469 -----------------------------------------------------------------------------
2470 --
2471 -- Sum of all run items since inception.
2473 FUNCTION calc_asg_itd(
2474 p_assignment_id IN NUMBER,
2475 p_assignment_action_id IN NUMBER,
2476 p_balance_type_id IN NUMBER,
2477 p_effective_date IN DATE DEFAULT NULL -- in for consistency
2478 )
2479 RETURN NUMBER
2480 IS
2481 --
2482 --
2483 l_balance NUMBER;
2484 l_latest_value_exists VARCHAR2(2);
2485 l_assignment_action_id NUMBER;
2486 l_action_eff_date DATE;
2487 l_defined_bal_id NUMBER;
2488 --
2489 BEGIN
2490 --
2491 --Do we need to work out a value for this dimension/balance combination.
2492 --
2493 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_ITD');
2494 if l_defined_bal_id is not null then
2495 --
2496 -- Is there a value in the latest balances table ..
2497 --
2498 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2499 p_assignment_action_id);
2500 --
2501 else l_balance := null;
2502 end if;
2503 --
2504 RETURN l_balance;
2505 --
2506 END calc_asg_itd;
2507 --
2508 --
2509 -----------------------------------------------------------------------------
2510 --
2511 -- CALC_ASG_TD_ITD_ACTION -
2512 --
2513 -- This is the function for calculating assignment tax district
2514 -- Inception to date in assignment action mode
2515 -----------------------------------------------------------------------------
2516 --
2517 FUNCTION calc_asg_td_itd_action(
2518 p_assignment_action_id IN NUMBER,
2519 p_balance_type_id IN NUMBER,
2520 p_effective_date IN DATE)
2521 RETURN NUMBER
2522 IS
2523 --
2524 l_assignment_action_id NUMBER;
2525 l_balance NUMBER;
2526 l_assignment_id NUMBER;
2527 l_effective_date DATE;
2528 --
2529 BEGIN
2530 --
2531 l_assignment_id := get_correct_type(p_assignment_action_id);
2532 IF l_assignment_id is null THEN
2533 --
2534 -- The assignment action is not a payroll or quickpay type, so return null
2535 --
2536 l_balance := null;
2537 ELSE
2538 --
2539 l_balance := calc_asg_td_itd(p_assignment_id => l_assignment_id,
2540 p_assignment_action_id => p_assignment_action_id,
2541 p_balance_type_id => p_balance_type_id,
2542 p_effective_date => p_effective_date);
2543 END IF;
2544 --
2545 RETURN l_balance;
2546 end calc_asg_td_itd_action;
2547 --
2548 -----------------------------------------------------------------------------
2549 --
2550 -- CALC_ASG_TD_ITD_DATE -
2551 --
2552 -- This is the function for calculating assignment inception tax district
2553 -- to date in DATE MODE
2554 -----------------------------------------------------------------------------
2555 --
2556 FUNCTION calc_asg_td_itd_date(
2557 p_assignment_id IN NUMBER,
2558 p_balance_type_id IN NUMBER,
2559 p_effective_date IN DATE)
2560 RETURN NUMBER
2561 IS
2562 --
2563 l_assignment_action_id NUMBER;
2564 l_balance NUMBER;
2565 l_end_date DATE;
2566 --
2567 BEGIN
2568 --
2569 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2570 p_effective_date);
2571 IF l_assignment_action_id is null THEN
2572 l_balance := 0;
2573 ELSE
2574 l_balance := calc_asg_td_itd(p_assignment_id => p_assignment_id,
2575 p_assignment_action_id => l_assignment_action_id,
2576 p_balance_type_id => p_balance_type_id,
2577 p_effective_date => p_effective_date);
2578 END IF;
2579 --
2580 RETURN l_balance;
2581 end calc_asg_td_itd_date;
2582 --
2583 -----------------------------------------------------------------------------
2584 --
2585 -- CALC_ASG_TD_ITD -
2586 --
2587 -- calculate balances for Assignment tax district Inception to Date
2588 -- Calls Core Balance pkg.
2589 -----------------------------------------------------------------------------
2590 --
2591 -- Sum of all run items since inception (tax district)
2592 --
2593 FUNCTION calc_asg_td_itd(
2594 p_assignment_id IN NUMBER,
2595 p_assignment_action_id IN NUMBER,
2596 p_balance_type_id IN NUMBER,
2597 p_effective_date IN DATE DEFAULT NULL -- in for consistency
2598 )
2599 RETURN NUMBER
2600 IS
2601 --
2602 l_balance NUMBER;
2603 l_defined_bal_id NUMBER;
2604 --
2605 BEGIN
2606 --
2607 --Do we need to work out a value for this dimension/balance combination.
2608 --
2609 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TD_ITD');
2610 if l_defined_bal_id is not null then
2611 --
2615 --
2612 -- Call core balance pkg with the defined balance just retrieved.
2613 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2614 p_assignment_action_id);
2616 else l_balance := null;
2617 end if;
2618 --
2619 RETURN l_balance;
2620 --
2621 END calc_asg_td_itd;
2622 --
2623 -----------------------------------------------------------------------------
2624 --
2625 -- CALC_ASG_TFR_PTD_ACTION
2626 --
2627 -- This is the function for calculating assignment
2628 -- transfer period to date in assignment action mode
2629 -----------------------------------------------------------------------------
2630 --
2631 FUNCTION calc_asg_tfr_ptd_action(
2632 p_assignment_action_id IN NUMBER,
2633 p_balance_type_id IN NUMBER,
2634 p_effective_date IN DATE)
2635 RETURN NUMBER
2636 IS
2637 --
2638 l_assignment_action_id NUMBER;
2639 l_balance NUMBER;
2640 l_assignment_id NUMBER;
2641 l_effective_date DATE;
2642 --
2643 BEGIN
2644 --
2645 l_assignment_id := get_correct_type(p_assignment_action_id);
2646 IF l_assignment_id is null THEN
2647 --
2648 -- The assignment action is not a payroll or quickpay type, so return null
2649 --
2650 l_balance := null;
2651 ELSE
2652 --
2653 l_balance := calc_asg_tfr_ptd(
2654 p_assignment_action_id => p_assignment_action_id
2655 ,
2656 p_balance_type_id => p_balance_type_id,
2657 p_effective_date => p_effective_date,
2658 p_assignment_id => l_assignment_id);
2659 END IF;
2660 --
2661 RETURN l_balance;
2662 end calc_asg_tfr_ptd_action;
2663 --
2664 -----------------------------------------------------------------------------
2665 --
2666 -- CALC_ASG_TFR_PTD_DATE
2667 --
2668 -- This is the function for calculating assignment transfer
2669 -- period to date in date mode
2670 -----------------------------------------------------------------------------
2671 --
2672 FUNCTION calc_asg_tfr_ptd_date(
2673 p_assignment_id IN NUMBER,
2674 p_balance_type_id IN NUMBER,
2675 p_effective_date IN DATE)
2676 RETURN NUMBER
2677 IS
2678 --
2679 l_assignment_action_id NUMBER;
2680 l_balance NUMBER;
2681 l_period_end_date DATE;
2682 l_date_paid DATE;
2683 --
2684 -- Has the processing time period expired
2685 --
2686 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
2687 SELECT ptp.end_date, ppa.effective_date
2688 FROM per_time_periods ptp,
2689 pay_payroll_actions ppa,
2690 pay_assignment_actions paa
2691 WHERE
2692 paa.assignment_action_id = c_assignment_action_id
2693 AND paa.payroll_action_id = ppa.payroll_action_id
2694 AND ppa.time_period_id = ptp.time_period_id;
2695 --
2696 BEGIN
2697 --
2698 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2699 p_effective_date);
2700 IF l_assignment_action_id is null THEN
2701 l_balance := 0;
2702 ELSE
2703 open expired_time_period(l_assignment_action_id);
2704 FETCH expired_time_period INTO l_period_end_date, l_date_paid;
2705 close expired_time_period;
2706 --
2707 if greatest(l_period_end_date,l_date_paid) < p_effective_date then
2708 l_balance := 0;
2709 else
2710 l_balance := calc_asg_tfr_ptd(
2711 p_assignment_action_id => l_assignment_action_id,
2712 p_balance_type_id => p_balance_type_id,
2713 p_effective_date => p_effective_date,
2714 p_assignment_id => p_assignment_id);
2715 end if;
2716 END IF;
2717 --
2718 RETURN l_balance;
2719 end calc_asg_tfr_ptd_date;
2720 --
2721 --------------------------------------------------------------------------------
2722 --
2723 -- CALC_ASG_TFR_PTD --
2724 -- calculate Assignment transfer period to date
2725 -- Call the Core Balance function
2726 --------------------------------------------------------------------------------
2727 --
2728 --
2729 -- This dimension is the total for an assignment within the processing
2730 -- period of his current payroll, OR if the assignment has transferred
2731 -- payroll it includes run results generated from actions that are
2732 -- within the same statutory period.
2733 -- The start of the statutory period is based on a fixed calendar which
2734 -- begins on the 6th April of each calendar year. Monthly periods
2735 -- start at the 6th of each month, weekly based periods are at 7 day
2736 -- intervals from the 6th April.
2737 -- The regular payment date for the payroll period determines which
2738 -- statutory period it is in so the statutory start of period is
2739 -- compared against the regular payment date of the payroll period
2740 -- that the actions were created for.
2741 --
2742 FUNCTION calc_asg_tfr_ptd(
2743 p_assignment_action_id IN NUMBER,
2747 )
2744 p_balance_type_id IN NUMBER,
2745 p_effective_date IN DATE DEFAULT NULL,
2746 p_assignment_id IN NUMBER
2748 RETURN NUMBER
2749 IS
2750 --
2751 l_balance NUMBER;
2752 l_assignment_action_id NUMBER;
2753 l_action_eff_date DATE;
2754 l_defined_bal_id NUMBER;
2755 --
2756 BEGIN
2757 --
2758 --Do we need to work out a value for this dimension/balance combination.
2759 --
2760 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TRANSFER_PTD');
2761 if l_defined_bal_id is not null then
2762 --
2763 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2764 p_assignment_action_id);
2765 --
2766 else l_balance := null;
2767 end if;
2768 --
2769 RETURN l_balance;
2770 --
2771 END calc_asg_tfr_ptd;
2772 --
2773 -----------------------------------------------------------------------------
2774 --
2775 -- CALC_ASG_TD_YTD_ACTION -
2776 --
2777 -- This is the function for calculating assignment td year to
2778 -- date in asg action mode
2779 -----------------------------------------------------------------------------
2780 --
2781 FUNCTION calc_asg_td_ytd_action(
2782 p_assignment_action_id IN NUMBER,
2783 p_balance_type_id IN NUMBER,
2784 p_effective_date IN DATE)
2785 RETURN NUMBER
2786 IS
2787 --
2788 l_assignment_action_id NUMBER;
2789 l_balance NUMBER;
2790 l_assignment_id NUMBER;
2791 l_effective_date DATE;
2792 --
2793 BEGIN
2794 --
2795 l_assignment_id := get_correct_type(p_assignment_action_id);
2796 IF l_assignment_id is null THEN
2797 --
2798 -- The assignment action is not a payroll or quickpay type, so return null
2799 --
2800 l_balance := null;
2801 ELSE
2802 --
2803 l_balance := calc_asg_td_ytd(
2804 p_assignment_action_id => p_assignment_action_id,
2805 p_balance_type_id => p_balance_type_id,
2806 p_effective_date => p_effective_date,
2807 p_assignment_id => l_assignment_id);
2808 END IF;
2809 --
2810 RETURN l_balance;
2811 end calc_asg_td_ytd_action;
2812 --
2813 ------------------------------------------------------------------------------
2814 --
2815 -- CALC_ASG_TD_YTD
2816 -- This function is for assignment tax district year to date
2817 -- Calls core balance package
2818 ------------------------------------------------------------------------------
2819 --
2820 FUNCTION calc_asg_td_ytd(
2821 p_assignment_action_id IN NUMBER,
2822 p_balance_type_id IN NUMBER,
2823 p_effective_date IN DATE DEFAULT NULL,
2824 p_assignment_id IN NUMBER
2825 )
2826 RETURN NUMBER
2827 IS
2828 --
2829 --
2830 l_balance NUMBER;
2831 l_session_date DATE;
2832 l_action_eff_date DATE;
2833 l_expired_balance NUMBER;
2834 l_assignment_id NUMBER;
2835 l_assignment_action_id NUMBER;
2836 l_latest_value_exists VARCHAR2(2);
2837 l_defined_bal_id NUMBER;
2838 --
2839 BEGIN
2840 --
2841 --Do we need to work out a value for this dimension/balance combination.
2842 --
2843 l_defined_bal_id := dimension_relevant(p_balance_type_id, '_ASG_TD_YTD');
2844 if l_defined_bal_id is not null then
2845 --
2846 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
2847 p_assignment_action_id);
2848 --
2849 else l_balance := null;
2850 end if;
2851 --
2852 RETURN l_balance;
2853 --
2854 END calc_asg_td_ytd;
2855 --
2856 -----------------------------------------------------------------------------
2857 --
2858 -- CALC_ASG_TD_YTD_DATE -
2859 --
2860 -- This is the function for calculating assignment year to
2861 -- date in date mode
2862 -----------------------------------------------------------------------------
2863 --
2864 FUNCTION calc_asg_td_ytd_date(
2865 p_assignment_id IN NUMBER,
2866 p_balance_type_id IN NUMBER,
2867 p_effective_date IN DATE)
2868 RETURN NUMBER
2869 IS
2870 --
2871 l_assignment_action_id NUMBER;
2872 l_balance NUMBER;
2873 l_end_date DATE;
2874 l_action_eff_date DATE;
2875 --
2876 BEGIN
2877 --
2878 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2879 p_effective_date);
2880 IF l_assignment_action_id is null THEN
2881 l_balance := 0;
2882 ELSE
2883 -- start expiry chk now
2884 l_action_eff_date := get_latest_date(l_assignment_action_id);
2885 --
2886 -- Is effective date (sess) later than the expiry of the financial year of the
2887 -- effective date.
2888 --
2889 if p_effective_date >= get_expired_year_date(l_action_eff_date) then
2890 l_balance := 0;
2894 p_assignment_action_id => l_assignment_action_id,
2891 else
2892 --
2893 l_balance := calc_asg_td_ytd(
2895 p_balance_type_id => p_balance_type_id,
2896 p_effective_date => p_effective_date,
2897 p_assignment_id => p_assignment_id);
2898 end if;
2899 END IF;
2900 --
2901 RETURN l_balance;
2902 end calc_asg_td_ytd_date;
2903 --
2904 -----------------------------------------------------------------------------
2905 --added by skutteti
2906 -----------------------------------------------------------------------------
2907 --
2908 -- CALC_ASG_TD_ODD_TWO_YTD_ACTION
2909 --
2910 -- This is the function for calculating assignment td two years to
2911 -- date in asg action mode
2912 --
2913 -----------------------------------------------------------------------------
2914 --
2915 FUNCTION calc_asg_td_odd_two_ytd_action(
2916 p_assignment_action_id IN NUMBER,
2917 p_balance_type_id IN NUMBER,
2918 p_effective_date IN DATE DEFAULT NULL)
2919 RETURN NUMBER
2920 IS
2921 --
2922 l_assignment_action_id NUMBER;
2923 l_balance NUMBER;
2924 l_assignment_id NUMBER;
2925 l_effective_date DATE;
2926 --
2927 BEGIN
2928 --
2929 l_assignment_id := get_correct_type(p_assignment_action_id);
2930 IF l_assignment_id is null THEN
2931 --
2932 -- The assignment action is not a payroll or quickpay type, so return null
2933 --
2934 l_balance := null;
2935 ELSE
2936 --
2937 l_balance := calc_asg_td_odd_two_ytd(
2938 p_assignment_action_id => p_assignment_action_id,
2939 p_balance_type_id => p_balance_type_id,
2940 p_effective_date => p_effective_date,
2941 p_assignment_id => l_assignment_id);
2942 END IF;
2943 --
2944 RETURN l_balance;
2945 end calc_asg_td_odd_two_ytd_action;
2946 --
2947 -----------------------------------------------------------------------------
2948 --
2949 -- CALC_ASG_TD_ODD_TWO_YTD_DATE
2950 --
2951 -- This is the function for calculating assignment two years to
2952 -- date in date mode
2953 --
2954 -----------------------------------------------------------------------------
2955 --
2956 FUNCTION calc_asg_td_odd_two_ytd_date(
2957 p_assignment_id IN NUMBER,
2958 p_balance_type_id IN NUMBER,
2959 p_effective_date IN DATE)
2960 RETURN NUMBER
2961 IS
2962 --
2963 l_assignment_action_id NUMBER;
2964 l_balance NUMBER;
2965 l_end_date DATE;
2966 l_action_eff_date DATE;
2967 --
2968 BEGIN
2969 --
2970 l_assignment_action_id := get_latest_action_id(p_assignment_id,
2971 p_effective_date);
2972 IF l_assignment_action_id is null THEN
2973 l_balance := 0;
2974 ELSE
2975 -- start expiry chk now
2976 l_action_eff_date := get_latest_date(l_assignment_action_id);
2977 --
2978 -- Is effective date (sess) later than the expiry of the
2979 -- financial year of the effective date.
2980 --
2981 if p_effective_date >= get_expired_two_year_date(l_action_eff_date
2982 ,'ODD') then
2983 l_balance := 0;
2984 else
2985 --
2986 l_balance := calc_asg_td_odd_two_ytd(
2987 p_assignment_action_id => l_assignment_action_id,
2988 p_balance_type_id => p_balance_type_id,
2989 p_effective_date => p_effective_date,
2990 p_assignment_id => p_assignment_id);
2991 end if;
2992 END IF;
2993 --
2994 RETURN l_balance;
2995 --
2996 end calc_asg_td_odd_two_ytd_date;
2997 --
2998 ------------------------------------------------------------------------------
2999 --
3000 -- CALC_ASG_TD_ODD_TWO_YTD
3001 -- This function is for assignment tax district two years to date
3002 -- Calls Core balance package
3003 ------------------------------------------------------------------------------
3004 --
3005 FUNCTION calc_asg_td_odd_two_ytd(
3006 p_assignment_action_id IN NUMBER,
3007 p_balance_type_id IN NUMBER,
3008 p_effective_date IN DATE DEFAULT NULL,
3009 p_assignment_id IN NUMBER
3010 )
3011 RETURN NUMBER
3012 IS
3013 --
3014 l_balance NUMBER;
3015 l_defined_bal_id NUMBER;
3016 --
3017 BEGIN
3018 --
3019 --Do we need to work out a value for this dimension/balance combination.
3020 --
3021 l_defined_bal_id := dimension_relevant(p_balance_type_id,
3022 '_ASG_TD_ODD_TWO_YTD');
3023 if l_defined_bal_id is not null then
3024 --
3025 -- Call core balance pkg with the defined balance just retrieved.
3026 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
3027 p_assignment_action_id);
3031 --
3028 --
3029 else l_balance := null;
3030 end if;
3032 RETURN l_balance;
3033 --
3034 END calc_asg_td_odd_two_ytd;
3035 -------------------------------------------------------------------------------
3036 --
3037 -- CALC_ASG_TD_EVEN_TWO_YTD_ACTION
3038 --
3039 -- This is the function for calculating assignment td two years to
3040 -- date in asg action mode
3041 --
3042 -----------------------------------------------------------------------------
3043 --
3044 FUNCTION calc_asg_td_even_two_ytd_actio(
3045 p_assignment_action_id IN NUMBER,
3046 p_balance_type_id IN NUMBER,
3047 p_effective_date IN DATE DEFAULT NULL)
3048 RETURN NUMBER
3049 IS
3050 --
3051 l_assignment_action_id NUMBER;
3052 l_balance NUMBER;
3053 l_assignment_id NUMBER;
3054 l_effective_date DATE;
3055 --
3056 BEGIN
3057 --
3058 l_assignment_id := get_correct_type(p_assignment_action_id);
3059 IF l_assignment_id is null THEN
3060 --
3061 -- The assignment action is not a payroll or quickpay type, so return null
3062 --
3063 l_balance := null;
3064 ELSE
3065 --
3066 l_balance := calc_asg_td_even_two_ytd(
3067 p_assignment_action_id => p_assignment_action_id,
3068 p_balance_type_id => p_balance_type_id,
3069 p_effective_date => p_effective_date,
3070 p_assignment_id => l_assignment_id);
3071 END IF;
3072 --
3073 RETURN l_balance;
3074 end calc_asg_td_even_two_ytd_actio;
3075 --
3076 -----------------------------------------------------------------------------
3077 --
3078 -- CALC_ASG_TD_EVEN_TWO_YTD_DATE
3079 --
3080 -- This is the function for calculating assignment two years to
3081 -- date in date mode
3082 -----------------------------------------------------------------------------
3083 --
3084 FUNCTION calc_asg_td_even_two_ytd_date(
3085 p_assignment_id IN NUMBER,
3086 p_balance_type_id IN NUMBER,
3087 p_effective_date IN DATE)
3088 RETURN NUMBER
3089 IS
3090 --
3091 l_assignment_action_id NUMBER;
3092 l_balance NUMBER;
3093 l_end_date DATE;
3094 l_action_eff_date DATE;
3095 --
3096 BEGIN
3097 --
3098 l_assignment_action_id := get_latest_action_id(p_assignment_id,
3099 p_effective_date);
3100 IF l_assignment_action_id is null THEN
3101 l_balance := 0;
3102 ELSE
3103 -- start expiry chk now
3104 l_action_eff_date := get_latest_date(l_assignment_action_id);
3105 --
3106 -- Is effective date (sess) later than the expiry of the
3107 -- financial year of the effective date.
3108 --
3109 if p_effective_date >= get_expired_two_year_date(l_action_eff_date
3110 ,'EVEN') then
3111 l_balance := 0;
3112 else
3113 --
3114 l_balance := calc_asg_td_even_two_ytd(
3115 p_assignment_action_id => l_assignment_action_id,
3116 p_balance_type_id => p_balance_type_id,
3117 p_effective_date => p_effective_date,
3118 p_assignment_id => p_assignment_id);
3119 end if;
3120 END IF;
3121 --
3122 RETURN l_balance;
3123 end calc_asg_td_even_two_ytd_date;
3124 --
3125 ------------------------------------------------------------------------------
3126 --
3127 -- CALC_ASG_TD_EVEN_TWO_YTD
3128 -- This function is for assignment tax district two years to date
3129 ------------------------------------------------------------------------------
3130 --
3131 FUNCTION calc_asg_td_even_two_ytd(
3132 p_assignment_action_id IN NUMBER,
3133 p_balance_type_id IN NUMBER,
3134 p_effective_date IN DATE DEFAULT NULL,
3135 p_assignment_id IN NUMBER
3136 )
3137 RETURN NUMBER
3138 IS
3139 --
3140 l_balance NUMBER;
3141 l_defined_bal_id NUMBER;
3142 --
3143 BEGIN
3144 --
3145 --Do we need to work out a value for this dimension/balance combination.
3146 --
3147 l_defined_bal_id := dimension_relevant(p_balance_type_id,
3148 '_ASG_TD_EVEN_TWO_YTD');
3149 if l_defined_bal_id is not null then
3150 --
3151 -- Call core balance pkg with the defined balance just retrieved.
3152 l_balance := pay_balance_pkg.get_value(l_defined_bal_id,
3153 p_assignment_action_id);
3154 --
3155 else l_balance := null;
3156 end if;
3157 --
3158 RETURN l_balance;
3159 --
3160 END calc_asg_td_even_two_ytd;
3161 ----------------------------------------------------------------------------------
3162 --
3163 -- CALC_BALANCE --
3164 -- General function for accumulating a balance between two dates
3165 --
3166 --------------------------------------------------------------------------------
3167
3171 p_period_from_date IN DATE, -- since regular pay date of period
3168 FUNCTION calc_balance(
3169 p_assignment_id IN NUMBER,
3170 p_balance_type_id IN NUMBER, -- balance
3172 p_event_from_date IN DATE, -- since effective date of
3173 p_to_date IN DATE, -- sum up to this date
3174 p_action_sequence IN NUMBER) -- sum up to this sequence
3175 RETURN NUMBER
3176 IS
3177 --
3178 --
3179 l_balance NUMBER;
3180 --
3181 BEGIN
3182 --
3183 SELECT /*+ ORDERED INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
3184 PACT PAY_PAYROLL_ACTIONS_PK,
3185 FEED PAY_BALANCE_FEEDS_F_UK2,
3186 PPTP PER_TIME_PERIODS_PK,
3187 RR PAY_RUN_RESULTS_N50,
3188 TARGET PAY_RUN_RESULT_VALUES_PK) */
3189 NVL(SUM(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
3190 INTO
3191 l_balance
3192 FROM
3193 pay_assignment_actions ASSACT
3194 ,pay_payroll_actions PACT
3195 ,pay_balance_feeds_f FEED
3196 ,per_time_periods PPTP
3197 ,pay_run_results RR
3198 ,pay_run_result_values TARGET
3199 WHERE
3200 FEED.balance_type_id = P_BALANCE_TYPE_ID
3201 AND FEED.input_value_id = TARGET.input_value_id
3202 AND TARGET.run_result_id = RR.run_result_id
3203 AND RR.assignment_action_id = ASSACT.assignment_action_id
3204 AND ASSACT.payroll_action_id = PACT.payroll_action_id
3205 AND nvl(TARGET.result_value,'0') <> '0'
3206 AND PACT.effective_date BETWEEN
3207 FEED.effective_start_date AND FEED.effective_end_date
3208 AND RR.status IN ('P','PA')
3209 AND PACT.time_period_id = PPTP.time_period_id
3210 AND PPTP.regular_payment_date >= P_PERIOD_FROM_DATE
3211 AND PACT.effective_date >= P_EVENT_FROM_DATE
3212 AND PACT.effective_date <= P_TO_DATE
3213 AND ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
3214 AND ASSACT.assignment_id = P_ASSIGNMENT_ID;
3215
3216 RETURN l_balance;
3217 --
3218 END calc_balance;
3219 --
3220
3221 --------------------------------------------------------------------------------
3222 -- --
3223 -- CREATE DIMENSION --
3224 -- --
3225 --------------------------------------------------------------------------------
3226
3227 PROCEDURE create_dimension(
3228 errbuf OUT NOCOPY VARCHAR2,
3229 retcode OUT NOCOPY NUMBER,
3230 p_business_group_id IN NUMBER,
3231 p_suffix IN VARCHAR2,
3232 p_level IN VARCHAR2,
3233 p_start_dd_mm IN VARCHAR2,
3234 p_frequency IN NUMBER,
3235 p_global_name IN VARCHAR2 DEFAULT NULL)
3236 IS
3237 BEGIN
3238 errbuf := NULL;
3239 retcode := 0;
3240 ---------------------------
3241 -- INSERT INTO FF_ROUTES --
3242 ---------------------------
3243 DECLARE
3244 l_route_text ff_routes.text%TYPE;
3245 l_bal_next number;
3246 BEGIN
3247 SELECT
3248 pay_balance_dimensions_s.NEXTVAL
3249 INTO
3250 l_bal_next
3251 FROM DUAL;
3252
3253 l_route_text :=
3254 'pay_gb_balances_v TARGET,
3255 pay_dummy_feeds_v FEED
3256 WHERE
3257 TARGET.assignment_action_id = &B1
3258 AND TARGET.balance_type_id = &U1
3259 AND TARGET.balance_dimension_id = ' || TO_CHAR(l_bal_next);
3260 --
3261 --
3262
3263 INSERT INTO FF_ROUTES
3264 (
3265 route_id,
3266 route_name,
3267 user_defined_flag,
3268 description,
3269 text
3270 )
3271 VALUES
3272 (
3273 ff_routes_s.NEXTVAL,
3274 'ROUTE_NAME_' || ff_routes_s.CURRVAL ,
3275 'N',
3276 'User balance dimension for '||
3277 UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ASG '||
3278 p_start_dd_mm || ' RESET'|| TO_CHAR(p_frequency,'00'),
3279 l_route_text
3280 );
3281 END;
3282
3283 -----------------------------------------
3284 -- INSERT INTO FF_ROUTE_CONTEXT_USAGES --
3285 -----------------------------------------
3286
3287 BEGIN
3288 INSERT INTO ff_route_context_usages
3289 (
3290 route_id,
3291 context_id,
3292 sequence_no
3293 )
3294 SELECT
3295 ff_routes_s.CURRVAL,
3296 context_id,
3297 1
3298 FROM
3299 ff_contexts
3300 WHERE
3301 context_name = 'ASSIGNMENT_ACTION_ID';
3302 END;
3303
3304 ------------------------------------
3305 -- INSERT INTO FF_ROUTE_PARAMETER --
3306 ------------------------------------
3307
3308 BEGIN
3309 INSERT INTO ff_route_parameters
3310 (
3311 route_parameter_id,
3312 route_id,
3313 sequence_no,
3314 parameter_name,
3315 data_type
3319 ff_route_parameters_s.NEXTVAL,
3316 )
3317 VALUES
3318 (
3320 ff_routes_s.CURRVAL,
3321 1,
3322 'BALANCE TYPE ID',
3323 'N'
3324 );
3325 END;
3326
3327 -----------------------------
3328 -- CREATION DIMENSION NAME --
3329 -----------------------------
3330
3331 DECLARE
3332 l_dim_name VARCHAR2(256);
3333 l_dim_type VARCHAR2(1);
3334 l_dim_level VARCHAR2(3);
3335 l_req_id NUMBER;
3336
3337 BEGIN
3338
3339 -- fill the dimension type
3340 IF p_level = 'ASSIGNMENT' THEN
3341 l_dim_type := 'A';
3342 l_dim_level := 'ASG';
3343 ELSIF p_level = 'PERSON' THEN
3344 l_dim_type := 'P';
3345 l_dim_level := 'PER';
3346 ELSIF p_level = 'ELEMENT' THEN
3347 l_dim_type := 'P';
3348 l_dim_level := 'ELE';
3349 ELSE
3350 l_dim_type := 'P';
3351 l_dim_level := 'PER';
3352 END IF;
3353
3354
3355 -- Fill the dimension name
3356 IF p_global_name IS NULL THEN
3357 -- USER REGULAR
3358 l_dim_name := UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ';
3359 l_dim_name := l_dim_name || l_dim_level || ' ' ;
3360 l_dim_name := l_dim_name || p_start_dd_mm || ' RESET';
3361 l_dim_name := l_dim_name || TO_CHAR(p_frequency,'00');
3362 ELSE
3363 -- USER IRREGULAR
3364 /****************************/
3365 /* Not yet implemented */
3366 /****************************/
3367 /*
3368 l_dim_name := 'USER IRREGULAR DIMENSION FOR ';
3369 l_dim_name := l_dim_name || p_level || ' BASED ON ' || p_global_name;
3370 */
3371 null;
3372 END IF;
3373
3374 -- Find the current request id
3375 l_req_id := fnd_profile.value('CONC_REQUEST_ID');
3376
3377 -- insert into the table
3378 INSERT INTO pay_balance_dimensions
3379 (
3380 balance_dimension_id,
3381 business_group_id,
3382 legislation_code,
3383 route_id,
3384 database_item_suffix,
3385 dimension_name,
3386 dimension_type,
3387 description,
3388 feed_checking_code,
3389 legislation_subgroup,
3390 payments_flag,
3391 expiry_checking_code,
3392 expiry_checking_level,
3393 feed_checking_type
3394 )
3395 VALUES
3396 (
3397 pay_balance_dimensions_s.CURRVAL,
3398 p_business_group_id,
3399 NULL,
3400 ff_routes_s.CURRVAL,
3401 p_suffix,
3402 l_dim_name,
3403 l_dim_type,
3404 'User dimension defined by Request Id ' || l_req_id,
3405 NULL,
3406 NULL,
3407 'N',
3408 'hr_gbbal.check_expiry',
3409 'P',
3410 NULL
3411 );
3412
3413 END;
3414 END create_dimension;
3415 --------------------------------------------------------------------------------
3416 -- --
3417 -- EXPIRY CHECKING CODE --
3418 -- --
3419 --------------------------------------------------------------------------------
3420 PROCEDURE check_expiry(
3421 p_owner_payroll_action_id IN NUMBER,
3422 p_user_payroll_action_id IN NUMBER,
3423 p_owner_assignment_action_id IN NUMBER,
3424 p_user_assignment_action_id IN NUMBER,
3425 p_owner_effective_date IN DATE,
3426 p_user_effective_date IN DATE,
3427 p_dimension_name IN VARCHAR2,
3428 p_expiry_information OUT NOCOPY NUMBER)
3429 IS
3430 p_user_start_period DATE;
3431 p_owner_start_period DATE;
3432 BEGIN
3433
3434 -- This is only for USER REGULAR BALANCES
3435 p_user_start_period := hr_gbbal.dimension_reset_date(p_dimension_name, p_user_effective_date,null);
3436 p_owner_start_period := hr_gbbal.dimension_reset_date(p_dimension_name, p_owner_effective_date,null);
3437 IF p_user_start_period = p_owner_start_period THEN
3438 p_expiry_information := 0; -- FALSE
3439 ELSE
3440 p_expiry_information := 1; -- TRUE
3441 END IF;
3442
3443 END check_expiry;
3444 -------------------------------------------------------------------------------
3445 --
3446 -- FUNCTION get_element_reference.
3447 -- This function returns an element balance reference number
3448 -- for identification purposes, which is suffixed by ITD or PTD
3449 -- depending on the balance, and used as the reported dimension name.
3450 -- Where there is no reference, the displayed dimension defaults to
3451 -- _ELEMENT_PTD or _ELEMENT_ITD.
3452 -- Bug 1146055, use Run Results instead of element entries,
3453 -- note still uses view pay_input_values.
3454 --
3455 -------------------------------------------------------------------------------
3456 --
3457 FUNCTION get_element_reference(p_run_result_id IN NUMBER,
3458 p_database_item_suffix IN VARCHAR2)
3459 RETURN VARCHAR2 IS
3460 --
3461 l_reference varchar2(60);
3462 l_suffix varchar2(4);
3463 l_prefix varchar2(20);
3464 l_original_entry_id number;
3465 --
3469 pay_run_results prr,
3466 cursor get_run_result_value (c_run_result_id NUMBER) is
3467 SELECT prrv.result_value
3468 FROM pay_run_result_values prrv,
3470 pay_input_values iv
3471 WHERE prr.run_result_id = c_run_result_id
3472 AND prr.run_result_id = prrv.run_result_id
3473 AND iv.name = 'Reference'
3474 AND iv.input_value_id = prrv.input_value_id;
3475
3476 cursor get_source_id(c_run_result_id NUMBER) IS
3477 SELECT prr.source_id
3478 FROM pay_run_results prr
3479 WHERE prr.run_result_id = c_run_result_id;
3480
3481 --
3482 BEGIN
3483 --
3484 open get_run_result_value (p_run_result_id);
3485 fetch get_run_result_value into l_reference;
3486 close get_run_result_value;
3487
3488 open get_source_id (p_run_result_id);
3489 fetch get_source_id into l_original_entry_id;
3490 close get_source_id;
3491 --
3492 /*For bug fix 4452262*/
3493 if p_database_item_suffix in ('_ELEMENT_ITD','_ELEMENT_PTD') then
3494
3495 l_prefix := substr(p_database_item_suffix,1, length(p_database_item_suffix)-3);
3496 l_suffix := substr(p_database_item_suffix, -4);
3497 l_reference := l_prefix|| l_original_entry_id || l_suffix;
3498
3499 elsif p_database_item_suffix in ('_ELEMENT_CO_REF_ITD') then
3500
3501 l_prefix := substr(p_database_item_suffix,1, length(p_database_item_suffix)-3);
3502 l_suffix := substr(p_database_item_suffix, -4);
3503 l_reference := l_prefix|| l_reference || l_suffix;
3504
3505 elsif (l_reference is null or l_reference = 'Unknown') then
3506 l_reference := p_database_item_suffix;
3507
3508 else
3509 l_reference := p_database_item_suffix;
3510 end if;
3511 --
3512 RETURN l_reference;
3513 END get_element_reference;
3514
3515 -------------------------------------------------------------------------------
3516 --
3517 -- FUNCTION get_context_references.
3518 -- This function returns context value, which is suffixed by ITD or PTD
3519 -- depending on the balance, and used as the reported dimension name.
3520 -- Where there is no context value, the displayed dimension defaults to
3521 -- database item suffix.
3522 --
3523 -------------------------------------------------------------------------------
3524
3525 FUNCTION get_context_references(p_context_value IN VARCHAR2,
3526 p_database_item_suffix IN VARCHAR2)
3527 RETURN VARCHAR2 IS
3528 --
3529 l_context varchar2(60);
3530 l_suffix varchar2(4);
3531 l_prefix varchar2(15);
3532 --
3533 BEGIN
3534
3535 if p_context_value is null or p_context_value = 'Unknown' then
3536 l_context := p_database_item_suffix;
3537 else
3538 l_context := p_database_item_suffix;
3539 l_suffix := substr(p_database_item_suffix, -4);
3540 l_prefix := substr(p_database_item_suffix,1,11);
3541 l_context := l_prefix || p_context_value || l_suffix;
3542
3543 end if;
3544 --
3545 RETURN l_context;
3546 END get_context_references;
3547
3548 -----------------------------------------------------------------------
3549 function ni_category_exists_in_year (p_assignment_action_id in number,
3550 p_category in varchar2)
3551 RETURN number is
3552 l_return number;
3553 l_regular_payment_date per_time_periods.regular_payment_date%type;
3554 l_niable_def_id pay_defined_balances.defined_balance_id%type;
3555 l_nitotal_def_id pay_defined_balances.defined_balance_id%type;
3556 l_nitotal_value number;
3557 l_niable_value number;
3558 --
3559
3560 /*Added for bug fix 4088228, to get the child assignment_action_id*/
3561
3562 cursor csr_child_asg_actid
3563 is
3564 SELECT max(paa.assignment_action_id)
3565 FROM pay_assignment_actions paa
3566 WHERE
3567 paa.source_action_id = p_assignment_action_id
3568 AND paa.source_action_id is not null;
3569
3570 cursor csr_latest_bal (c_asg_action_id IN NUMBER,
3571 c_defined_balance_id IN NUMBER) is
3572 SELECT value
3573 from pay_assignment_latest_balances
3574 Where assignment_action_id = c_asg_action_id
3575 and defined_balance_id = c_defined_balance_id;
3576
3577 cursor CSR_ni_entries is
3578 select distinct pel.element_type_id element_type_id,
3579 nvl(ent.original_entry_id, ent.element_entry_id) source_id
3580 from pay_element_entries_f ent,
3581 pay_element_links_f pel,
3582 pay_user_rows_f urows,
3583 pay_payroll_actions bact,
3584 per_time_periods bptp,
3585 pay_assignment_actions bassact
3586 where bassact.assignment_action_id = p_assignment_action_id
3587 and UROWS.user_table_id = g_ni_cat_indicator_table_id
3588 and fnd_number.canonical_to_number(UROWS.ROW_LOW_RANGE_OR_NAME) = PEL.ELEMENT_TYPE_ID
3589 and g_start_of_year between
3590 UROWS.effective_start_date and UROWS.effective_end_date
3591 and bact.payroll_action_id = bassact.payroll_action_id
3592 and bptp.time_period_id = bact.time_period_id
3593 and ent.assignment_id = bassact.assignment_id
3594 and ent.effective_end_date >= g_start_of_year
3595 and ent.effective_start_date <= bptp.end_date
3596 and ent.element_link_id = pel.element_link_id
3597 and pel.business_group_id + 0 = bact.business_group_id
3601 cursor CSR_ni_run_results_exist (p_source_id number) is
3598 and ent.effective_end_date between
3599 pel.effective_start_date and pel.effective_end_date;
3600
3602 select max(decode(PRR.element_type_id,g_ni_a_element_type_id,1,0))
3603 , max(decode(PRR.element_type_id,g_ni_b_element_type_id,1,0))
3604 , max(decode(PRR.element_type_id,g_ni_c_element_type_id,1,0))
3605 , max(decode(PRR.element_type_id,g_ni_d_element_type_id,1,0))
3606 , max(decode(PRR.element_type_id,g_ni_e_element_type_id,1,0))
3607 , max(decode(PRR.element_type_id,g_ni_f_element_type_id,1,0))
3608 , max(decode(PRR.element_type_id,g_ni_g_element_type_id,1,0))
3609 , max(decode(PRR.element_type_id,g_ni_j_element_type_id,1,0))
3610 , max(decode(PRR.element_type_id,g_ni_l_element_type_id,1,0))
3611 , max(decode(PRR.element_type_id,g_ni_s_element_type_id,1,0))
3612 from
3613 PAY_RUN_RESULTS PRR
3614 , PER_TIME_PERIODS PPTP
3615 , PAY_PAYROLL_ACTIONS PACT
3616 , PAY_ASSIGNMENT_ACTIONS ASSACT
3617 , PAY_ASSIGNMENT_ACTIONS BASSACT
3618 where PRR.source_id = p_source_id
3619 and PRR.source_type = 'I'
3620 AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
3621 AND PACT.ACTION_TYPE <> 'I'
3622 AND PPTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID
3623 AND PPTP.regular_payment_date >= g_start_of_year
3624 AND BASSACT.ASSIGNMENT_ACTION_ID = p_assignment_action_id
3625 AND ASSACT.ACTION_SEQUENCE <= BASSACT.ACTION_SEQUENCE
3626 AND ASSACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
3627 AND ASSACT.ASSIGNMENT_ID = BASSACT.ASSIGNMENT_ID;
3628
3629 cursor CSR_run_results_exist is
3630 select max(decode(FEED.balance_type_id,g_ni_a_id,1,g_ni_a_able_id,1,0))
3631 , max(decode(FEED.balance_type_id,g_ni_b_id,1,g_ni_b_able_id,1,0))
3632 , max(decode(FEED.balance_type_id,g_ni_c_id,1,g_ni_c_able_id,1,0))
3633 , max(decode(FEED.balance_type_id,g_ni_d_id,1,g_ni_d_able_id,1,0))
3634 , max(decode(FEED.balance_type_id,g_ni_e_id,1,g_ni_e_able_id,1,0))
3635 , max(decode(FEED.balance_type_id,g_ni_f_id,1,g_ni_f_able_id,1,0))
3636 , max(decode(FEED.balance_type_id,g_ni_g_id,1,g_ni_g_able_id,1,0))
3637 , max(decode(FEED.balance_type_id,g_ni_j_id,1,g_ni_j_able_id,1,0))
3638 , max(decode(FEED.balance_type_id,g_ni_l_id,1,g_ni_l_able_id,1,0))
3639 , max(decode(FEED.balance_type_id,g_ni_s_id,1,g_ni_s_able_id,1,0))
3640 from
3641 PAY_BALANCE_FEEDS_F FEED
3642 , PAY_RUN_RESULT_VALUES PRRV
3643 , PAY_RUN_RESULTS PRR
3644 , PER_TIME_PERIODS PPTP
3645 , PAY_PAYROLL_ACTIONS PACT
3646 , PAY_ASSIGNMENT_ACTIONS ASSACT
3647 , PAY_ASSIGNMENT_ACTIONS BASSACT
3648 WHERE FEED.balance_type_id in (
3649 g_ni_a_id, g_ni_a_able_id
3650 ,g_ni_b_id, g_ni_b_able_id
3651 ,g_ni_c_id, g_ni_c_able_id
3652 ,g_ni_d_id, g_ni_d_able_id
3653 ,g_ni_e_id, g_ni_e_able_id
3654 ,g_ni_f_id, g_ni_f_able_id
3655 ,g_ni_g_id, g_ni_g_able_id
3656 ,g_ni_j_id, g_ni_j_able_id
3657 ,g_ni_l_id, g_ni_l_able_id
3658 ,g_ni_s_id, g_ni_s_able_id
3659 )
3660 AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
3661 AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
3662 AND PACT.action_type in ('I',g_action_typer,g_action_typeq,g_action_typeb)
3663 AND PPTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID
3664 AND PPTP.regular_payment_date >= g_start_of_year
3665 AND BASSACT.ASSIGNMENT_ACTION_ID = p_assignment_action_id
3666 AND PRRV.RESULT_VALUE IS NOT NULL
3667 AND PRRV.RESULT_VALUE <> '0'
3668 AND PPTP.regular_payment_date is not null
3669 AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
3670 AND PACT.effective_date between
3671 FEED.effective_start_date and FEED.effective_end_date
3672 AND ASSACT.ACTION_SEQUENCE <= BASSACT.ACTION_SEQUENCE
3673 AND ASSACT.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
3674 AND ASSACT.ASSIGNMENT_ID = BASSACT.ASSIGNMENT_ID;
3675 --BUG Changed cursor for improving performance 3221422
3676 --Remove this cursor for bug 4120063
3677 /*
3678 cursor CSR_initialization_exists is
3679 select 1
3680 from per_time_periods ptp
3681 where ptp.regular_payment_date >= g_start_of_year
3682 and ptp.time_period_id in
3683 (
3684 select
3685 null
3686 from pay_payroll_actions pact
3687 where pact.action_type = 'I'
3688 );
3689 */
3690 --
3691 cursor csr_asg_action_info (c_assignment_action_id IN NUMBER) IS
3692 select paa.assignment_id,
3693 paa.action_sequence,
3694 ppa.effective_date
3695 from pay_assignment_actions paa,
3696 pay_payroll_actions ppa
3697 where paa.assignment_action_id = c_assignment_action_id
3698 and paa.payroll_action_id = ppa.payroll_action_id;
3699 --
3700 l_ni_a_exists_adj number;
3701 l_ni_b_exists_adj number;
3702 l_ni_c_exists_adj number;
3703 l_ni_d_exists_adj number;
3704 l_ni_e_exists_adj number;
3705 l_ni_f_exists_adj number;
3706 l_ni_g_exists_adj number;
3707 l_ni_j_exists_adj number;
3708 l_ni_l_exists_adj number;
3709 l_ni_s_exists_adj number;
3710
3711 /*Added for bug fix 4088228*/
3715
3712 v_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3713 p_assignment_action_id_child pay_assignment_actions.assignment_action_id%TYPE;
3714 v_master_exist varchar2(1);
3716 begin
3717 --
3718
3719 /*Added for bug fix 4088228*/
3720
3721 open csr_child_asg_actid;
3722 fetch csr_child_asg_actid into v_assignment_action_id;
3723 close csr_child_asg_actid;
3724
3725 if v_assignment_action_id is not null then
3726 p_assignment_action_id_child := v_assignment_action_id;
3727 else
3728 p_assignment_action_id_child := p_assignment_action_id;
3729 end if;
3730
3731 if p_assignment_action_id_child is null or p_category is null then --Bug fix 4099228
3732 return null;
3733 end if;
3734 --
3735 if g_ni_a_id is null then -- first call this session
3736 begin
3737 select user_table_id
3738 into g_ni_cat_indicator_table_id
3739 from pay_user_tables
3740 where user_table_name = 'NI_CATEGORY_INDICATOR_ELEMENTS'
3741 and legislation_code = 'GB';
3742 -- if not found raise error
3743 EXCEPTION WHEN no_data_found THEN
3744 g_action_typer := 'R';
3745 g_action_typeq := 'Q';
3746 g_action_typeb := 'B';
3747 end;
3748
3749 end if;
3750
3751 if g_ni_a_id is null then -- first call this session
3752 select max(decode(balance_name, 'NI A Total',balance_type_id,0))
3753 ,max(decode(balance_name, 'NI A Able' ,balance_type_id,0))
3754 ,max(decode(balance_name, 'NI B Total',balance_type_id,0))
3755 ,max(decode(balance_name, 'NI B Able' ,balance_type_id,0))
3756 ,max(decode(balance_name, 'NI C Total',balance_type_id,0))
3757 ,max(decode(balance_name, 'NI C Able' ,balance_type_id,0))
3758 ,max(decode(balance_name, 'NI D Total',balance_type_id,0))
3759 ,max(decode(balance_name, 'NI D Able' ,balance_type_id,0))
3760 ,max(decode(balance_name, 'NI E Total',balance_type_id,0))
3761 ,max(decode(balance_name, 'NI E Able' ,balance_type_id,0))
3762 ,max(decode(balance_name, 'NI F Total',balance_type_id,0))
3763 ,max(decode(balance_name, 'NI F Able' ,balance_type_id,0))
3764 ,max(decode(balance_name, 'NI G Total',balance_type_id,0))
3765 ,max(decode(balance_name, 'NI G Able' ,balance_type_id,0))
3766 ,max(decode(balance_name, 'NI J Total',balance_type_id,0))
3767 ,max(decode(balance_name, 'NI J Able' ,balance_type_id,0))
3768 ,max(decode(balance_name, 'NI L Total',balance_type_id,0))
3769 ,max(decode(balance_name, 'NI L Able' ,balance_type_id,0))
3770 ,max(decode(balance_name, 'NI S Total',balance_type_id,0))
3771 ,max(decode(balance_name, 'NI S Able' ,balance_type_id,0))
3772 into
3773 g_ni_a_id, g_ni_a_able_id
3774 ,g_ni_b_id, g_ni_b_able_id
3775 ,g_ni_c_id, g_ni_c_able_id
3776 ,g_ni_d_id, g_ni_d_able_id
3777 ,g_ni_e_id, g_ni_e_able_id
3778 ,g_ni_f_id, g_ni_f_able_id
3779 ,g_ni_g_id, g_ni_g_able_id
3780 ,g_ni_j_id, g_ni_j_able_id
3781 ,g_ni_l_id, g_ni_l_able_id
3782 ,g_ni_s_id, g_ni_s_able_id
3783 from pay_balance_types
3784 where balance_name in (
3785 'NI A Total', 'NI A Able'
3786 ,'NI B Total', 'NI B Able'
3787 ,'NI C Total', 'NI C Able'
3788 ,'NI D Total', 'NI D Able'
3789 ,'NI E Total', 'NI E Able'
3790 ,'NI F Total', 'NI F Able'
3791 ,'NI G Total', 'NI G Able'
3792 ,'NI J Total', 'NI J Able'
3793 ,'NI L Total', 'NI L Able'
3794 ,'NI S Total', 'NI S Able'
3795 )
3796 and legislation_code = 'GB';
3797 end if;
3798 --
3799 if g_ni_element_type_id is null then -- first call this session
3800 select
3801 max(ptp.regular_payment_date)
3802 ,max(decode(e.element_name,'NI',e.element_type_id,0))
3803 ,max(decode(e.element_name,'NI A',e.element_type_id,0))
3804 ,max(decode(e.element_name,'NI B',e.element_type_id,0))
3805 ,max(decode(e.element_name,'NI C',e.element_type_id,0))
3806 ,max(decode(e.element_name,'NI D',e.element_type_id,0))
3807 ,max(decode(e.element_name,'NI E',e.element_type_id,0))
3808 ,max(decode(e.element_name,'NI F',e.element_type_id,0))
3809 ,max(decode(e.element_name,'NI G',e.element_type_id,0))
3810 ,max(decode(e.element_name,'NI J Deferment',e.element_type_id,0))
3811 ,max(decode(e.element_name,'NI L Deferment',e.element_type_id,0))
3812 ,max(decode(e.element_name,'NI S',e.element_type_id,0))
3813 into
3814 l_regular_payment_date
3815 ,g_ni_element_type_id
3816 ,g_ni_a_element_type_id
3817 ,g_ni_b_element_type_id
3818 ,g_ni_c_element_type_id
3819 ,g_ni_d_element_type_id
3820 ,g_ni_e_element_type_id
3821 ,g_ni_f_element_type_id
3822 ,g_ni_g_element_type_id
3823 ,g_ni_j_element_type_id
3824 ,g_ni_l_element_type_id
3825 ,g_ni_s_element_type_id
3826 from pay_element_types_f e,
3827 per_time_periods ptp,
3828 pay_payroll_actions bact,
3829 pay_assignment_actions bassact
3830 where element_name in ( 'NI'
3831 ,'NI A'
3835 ,'NI E'
3832 ,'NI B'
3833 ,'NI C'
3834 ,'NI D'
3836 ,'NI F'
3837 ,'NI G'
3838 ,'NI J Deferment'
3839 ,'NI L Deferment'
3840 ,'NI S')
3841 and e.legislation_code = 'GB'
3842 and bassact.assignment_action_id = p_assignment_action_id_child -- bug fix 4088228
3843 and bassact.payroll_action_id = bact.payroll_action_id
3844 and ptp.time_period_id = bact.time_period_id
3845 and bact.date_earned between
3846 e.effective_start_date and e.effective_end_date;
3847 end if;
3848 --
3849 -- first time through check whether any balance initializations have happened
3850 -- in the tax year - if not we don't need to check the initialization on
3851 -- individual balances.
3852
3853
3854
3855 begin
3856 if g_start_of_year is null then -- first call this session
3857 g_start_of_year := hr_gbbal.span_start(l_regular_payment_date, 1, '06-04');
3858 /*
3859 open CSR_initialization_exists;
3860 fetch CSR_initialization_exists into g_initialization_exists;
3861 close CSR_initialization_exists;
3862 */
3863 end if;
3864 end;
3865 --
3866 -- setup balance dimension id
3867 --
3868 if g_asg_td_ytd is null then
3869 select balance_dimension_id
3870 into g_asg_td_ytd
3871 from pay_balance_dimensions
3872 where dimension_name = '_ASG_TD_YTD';
3873 end if;
3874 --
3875 -- Check to see whether there are any latest balances for the
3876 -- NI <CAT> Total or NI <CAT> Able balances, for the dimension
3877 -- _ASG_TD_YTD. If so, we do not need to loop through the
3878 -- run results below. Use already cached balance type id's.
3879 --
3880 IF g_ni_a_defbal_id is null then
3881 -- First call this session, set up defined balances.
3882 select max(decode(balance_type_id,g_ni_a_id,defined_balance_id,0))
3883 ,max(decode(balance_type_id,g_ni_a_able_id,defined_balance_id,0))
3884 ,max(decode(balance_type_id,g_ni_b_id,defined_balance_id,0))
3885 ,max(decode(balance_type_id,g_ni_b_able_id,defined_balance_id,0))
3886 ,max(decode(balance_type_id,g_ni_c_id,defined_balance_id,0))
3887 ,max(decode(balance_type_id,g_ni_c_able_id,defined_balance_id,0))
3888 ,max(decode(balance_type_id,g_ni_d_id,defined_balance_id,0))
3889 ,max(decode(balance_type_id,g_ni_d_able_id,defined_balance_id,0))
3890 ,max(decode(balance_type_id,g_ni_e_id,defined_balance_id,0))
3891 ,max(decode(balance_type_id,g_ni_e_able_id,defined_balance_id,0))
3892 ,max(decode(balance_type_id,g_ni_f_id,defined_balance_id,0))
3893 ,max(decode(balance_type_id,g_ni_f_able_id,defined_balance_id,0))
3894 ,max(decode(balance_type_id,g_ni_g_id,defined_balance_id,0))
3895 ,max(decode(balance_type_id,g_ni_g_able_id,defined_balance_id,0))
3896 ,max(decode(balance_type_id,g_ni_j_id,defined_balance_id,0))
3897 ,max(decode(balance_type_id,g_ni_j_able_id,defined_balance_id,0))
3898 ,max(decode(balance_type_id,g_ni_l_id,defined_balance_id,0))
3899 ,max(decode(balance_type_id,g_ni_l_able_id,defined_balance_id,0))
3900 ,max(decode(balance_type_id,g_ni_s_id,defined_balance_id,0))
3901 ,max(decode(balance_type_id,g_ni_s_able_id,defined_balance_id,0))
3902 into
3903 g_ni_a_defbal_id,
3904 g_ni_a_able_defbal_id,
3905 g_ni_b_defbal_id,
3906 g_ni_b_able_defbal_id,
3907 g_ni_c_defbal_id,
3908 g_ni_c_able_defbal_id,
3909 g_ni_d_defbal_id,
3910 g_ni_d_able_defbal_id,
3911 g_ni_e_defbal_id,
3912 g_ni_e_able_defbal_id,
3913 g_ni_f_defbal_id,
3914 g_ni_f_able_defbal_id,
3915 g_ni_g_defbal_id,
3916 g_ni_g_able_defbal_id,
3917 g_ni_j_defbal_id,
3918 g_ni_j_able_defbal_id,
3919 g_ni_l_defbal_id,
3920 g_ni_l_able_defbal_id,
3921 g_ni_s_defbal_id,
3922 g_ni_s_able_defbal_id
3923 from pay_defined_balances
3924 where balance_dimension_id = g_asg_td_ytd
3925 and balance_type_id in
3926 (g_ni_a_id, g_ni_a_able_id
3927 ,g_ni_b_id, g_ni_b_able_id
3928 ,g_ni_c_id, g_ni_c_able_id
3929 ,g_ni_d_id, g_ni_d_able_id
3930 ,g_ni_e_id, g_ni_e_able_id
3931 ,g_ni_f_id, g_ni_f_able_id
3932 ,g_ni_g_id, g_ni_g_able_id
3933 ,g_ni_j_id, g_ni_j_able_id
3934 ,g_ni_l_id, g_ni_l_able_id
3935 ,g_ni_s_id, g_ni_s_able_id)
3936 and legislation_code = 'GB'
3937 and business_group_id is null;
3938 --
3939 END IF; -- Setup cached defined balances
3940 --
3941 -- Choose the relevant defined balance for latest balance
3942 -- call according to category.
3943 --
3944 If p_category = 'A' then
3945 l_nitotal_def_id := g_ni_a_defbal_id;
3946 l_niable_def_id := g_ni_a_able_defbal_id;
3947 Elsif p_category = 'B' then
3948 l_nitotal_def_id := g_ni_b_defbal_id;
3949 l_niable_def_id := g_ni_b_able_defbal_id;
3953 Elsif p_category = 'D' then
3950 Elsif p_category = 'C' then
3951 l_nitotal_def_id := g_ni_c_defbal_id;
3952 l_niable_def_id := g_ni_c_able_defbal_id;
3954 l_nitotal_def_id := g_ni_d_defbal_id;
3955 l_niable_def_id := g_ni_d_able_defbal_id;
3956 Elsif p_category = 'E' then
3957 l_nitotal_def_id := g_ni_e_defbal_id;
3958 l_niable_def_id := g_ni_e_able_defbal_id;
3959 Elsif p_category = 'F' then
3960 l_nitotal_def_id := g_ni_f_defbal_id;
3961 l_niable_def_id := g_ni_f_able_defbal_id;
3962 Elsif p_category = 'G' then
3963 l_nitotal_def_id := g_ni_g_defbal_id;
3964 l_niable_def_id := g_ni_g_able_defbal_id;
3965 Elsif p_category = 'J' then
3966 l_nitotal_def_id := g_ni_j_defbal_id;
3967 l_niable_def_id := g_ni_j_able_defbal_id;
3968 Elsif p_category = 'L' then
3969 l_nitotal_def_id := g_ni_l_defbal_id;
3970 l_niable_def_id := g_ni_l_able_defbal_id;
3971 Elsif p_category = 'S' then
3972 l_nitotal_def_id := g_ni_s_defbal_id;
3973 l_niable_def_id := g_ni_s_able_defbal_id;
3974 End If;
3975 --
3976
3977
3978 if p_assignment_action_id_child <> nvl(g_assignment_action_id2, -1) then --bug fix 4088228
3979 --
3980 open csr_asg_action_info(p_assignment_action_id_child); --bug fix 4088228
3981 fetch csr_asg_action_info into g_assignment_id,
3982 g_action_sequence,
3983 g_effective_date;
3984 close csr_asg_action_info;
3985 --
3986 g_assignment_action_id2 := p_assignment_action_id_child; -- bug fix 4088228
3987 --
3988 end if;
3989 --
3990 -- Check to see if any latest balances first.
3991 --
3992 l_nitotal_value := null;
3993 l_niable_value := null;
3994 --
3995 open csr_latest_bal(p_assignment_action_id_child, l_nitotal_def_id); -- bug fix 4088228
3996 fetch csr_latest_bal into l_nitotal_value;
3997 close csr_latest_bal;
3998 --
3999 open csr_latest_bal(p_assignment_action_id_child, l_niable_def_id); --bug fix 4088228
4000 fetch csr_latest_bal into l_niable_value;
4001 close csr_latest_bal;
4002 --
4003 -- If either total or able latest balances are null, then the
4004 -- Run Results cursors are used.
4005 --
4006 IF l_nitotal_value is null OR l_niable_value is null THEN
4007 --
4008 -- if a non zero result exists for either the NI Cat Total or the
4009 -- NI Cat Niable balance within the year the category has existed
4010 -- Prior to April 00 NI Cat Total indicated a category was reported
4011 -- for the assignment. However the introduction of the EET threshold
4012 -- and balances means that even without a deduction being taken
4013 -- EET balances, Able Balances and EES Rebate balances need to be
4014 -- reported.
4015 -- If NI Earnings are above the LEL than NI Cat Able is recorded up
4016 -- test NI Cat Total which will be non zero in this instance.
4017 -- first call for this assignment action
4018 if nvl(g_assignment_action_id,-1) <> p_assignment_action_id_child then --bug fix 4088228
4019
4020 g_assignment_action_id := p_assignment_action_id_child; --bug fix 4088228
4021 -- first check for the normal run indirects in the year
4022 -- The normal way for NI Balances is fed is from indirects
4023 -- returned by the NI Formula.
4024 -- exceptionally users adjust NI balances in a run or adjustment
4025 -- by giving an individual NI Category Element to an assignment
4026 -- the ni_run_result cursor caters for these two types of
4027 -- results using the optimal N51 index to retreive results.
4028 -- To achieve this it joins first to the element entries
4029 -- Table for a list of NI elements defined in a user table.
4030 begin
4031 g_ni_a_exists := 0;
4032 g_ni_b_exists := 0;
4033 g_ni_c_exists := 0;
4034 g_ni_d_exists := 0;
4035 g_ni_e_exists := 0;
4036 g_ni_f_exists := 0;
4037 g_ni_g_exists := 0;
4038 g_ni_j_exists := 0;
4039 g_ni_l_exists := 0;
4040 g_ni_s_exists := 0;
4041 if g_action_typer is null then -- [ ? check for user table redundant
4042 for l_entry in CSR_ni_entries loop -- { loop through the entries
4043 -- for NI itself look for the indirect results it has produced
4044 if l_entry.element_type_id = g_ni_element_type_id then -- [ NI
4045 open CSR_ni_run_results_exist(l_entry.source_id);
4046 fetch CSR_ni_run_results_exist
4047 into l_ni_a_exists_adj
4048 ,l_ni_b_exists_adj
4049 ,l_ni_c_exists_adj
4050 ,l_ni_d_exists_adj
4051 ,l_ni_e_exists_adj
4052 ,l_ni_f_exists_adj
4053 ,l_ni_g_exists_adj
4054 ,l_ni_j_exists_adj
4055 ,l_ni_l_exists_adj
4056 ,l_ni_s_exists_adj;
4057 close CSR_ni_run_results_exist;
4058 if l_ni_a_exists_adj = 1 then g_ni_a_exists := 1; end if;
4059 if l_ni_b_exists_adj = 1 then g_ni_b_exists := 1; end if;
4060 if l_ni_c_exists_adj = 1 then g_ni_c_exists := 1; end if;
4061 if l_ni_d_exists_adj = 1 then g_ni_d_exists := 1; end if;
4062 if l_ni_e_exists_adj = 1 then g_ni_e_exists := 1; end if;
4063 if l_ni_f_exists_adj = 1 then g_ni_f_exists := 1; end if;
4064 if l_ni_g_exists_adj = 1 then g_ni_g_exists := 1; end if;
4065 if l_ni_j_exists_adj = 1 then g_ni_j_exists := 1; end if;
4066 if l_ni_l_exists_adj = 1 then g_ni_l_exists := 1; end if;
4067 if l_ni_s_exists_adj = 1 then g_ni_s_exists := 1; end if;
4068 end if; -- ] NI
4069 if l_entry.element_type_id = g_ni_a_element_type_id
4070 then g_ni_a_exists := 1; end if;
4071 if l_entry.element_type_id = g_ni_b_element_type_id
4072 then g_ni_b_exists := 1; end if;
4073 if l_entry.element_type_id = g_ni_c_element_type_id
4074 then g_ni_c_exists := 1; end if;
4075 if l_entry.element_type_id = g_ni_d_element_type_id
4076 then g_ni_d_exists := 1; end if;
4077 if l_entry.element_type_id = g_ni_e_element_type_id
4078 then g_ni_e_exists := 1; end if;
4079 if l_entry.element_type_id = g_ni_f_element_type_id
4080 then g_ni_f_exists := 1; end if;
4081 if l_entry.element_type_id = g_ni_g_element_type_id
4082 then g_ni_g_exists := 1; end if;
4083 if l_entry.element_type_id = g_ni_j_element_type_id
4084 then g_ni_j_exists := 1; end if;
4085 if l_entry.element_type_id = g_ni_l_element_type_id
4086 then g_ni_l_exists := 1; end if;
4087 if l_entry.element_type_id = g_ni_s_element_type_id
4088 then g_ni_s_exists := 1; end if;
4089 end loop; -- } ni_entries loop
4090 end if; -- ]
4091 end;
4092 begin
4093 -- now select initialization in the year
4094 -- initialization results don't have source_id set to the NI Element
4095 -- so for these actions use a more expensive execution plan that
4096 -- retrieves all initialization results in the year and then tests
4097 -- whether any feed the NI Balances. Condition this step out all together
4098 -- if no initialization actions are detected in the year. If no seeded
4099 -- user table exists then also use this cursor
4100 if /* g_initialization_exists = 1 or */
4101 g_action_typer = 'R' then
4102 --
4103 open CSR_run_results_exist;
4104 fetch CSR_run_results_exist
4105 into l_ni_a_exists_adj
4106 ,l_ni_b_exists_adj
4107 ,l_ni_c_exists_adj
4108 ,l_ni_d_exists_adj
4109 ,l_ni_e_exists_adj
4110 ,l_ni_f_exists_adj
4111 ,l_ni_g_exists_adj
4112 ,l_ni_j_exists_adj
4113 ,l_ni_l_exists_adj
4114 ,l_ni_s_exists_adj;
4115 close CSR_run_results_exist;
4116 end if;
4117 end;
4118 --
4119 --
4120 if l_ni_a_exists_adj = 1 then g_ni_a_exists := 1; end if;
4121 if l_ni_b_exists_adj = 1 then g_ni_b_exists := 1; end if;
4122 if l_ni_c_exists_adj = 1 then g_ni_c_exists := 1; end if;
4123 if l_ni_d_exists_adj = 1 then g_ni_d_exists := 1; end if;
4124 if l_ni_e_exists_adj = 1 then g_ni_e_exists := 1; end if;
4125 if l_ni_f_exists_adj = 1 then g_ni_f_exists := 1; end if;
4126 if l_ni_g_exists_adj = 1 then g_ni_g_exists := 1; end if;
4127 if l_ni_j_exists_adj = 1 then g_ni_j_exists := 1; end if;
4128 if l_ni_l_exists_adj = 1 then g_ni_l_exists := 1; end if;
4129 if l_ni_s_exists_adj = 1 then g_ni_s_exists := 1; end if;
4130 --
4131 end if; -- g_asg_action = p_asg_action.
4132 --
4133 ELSIF l_nitotal_value = 0 AND l_niable_value = 0 THEN
4134 --
4135 -- There are latest balances but they are zero so could be
4136 -- from a previous asg action. The rest of this category's
4137 -- balances are not needed anyway, so return a 0.
4138 --
4139 if P_category = 'A' then g_ni_a_exists := 0; end if;
4140 if P_category = 'B' then g_ni_b_exists := 0; end if;
4141 if P_category = 'C' then g_ni_c_exists := 0; end if;
4142 if P_category = 'D' then g_ni_d_exists := 0; end if;
4143 if P_category = 'E' then g_ni_e_exists := 0; end if;
4144 if P_category = 'F' then g_ni_f_exists := 0; end if;
4145 if P_category = 'G' then g_ni_g_exists := 0; end if;
4146 if P_category = 'J' then g_ni_j_exists := 0; end if;
4147 if P_category = 'L' then g_ni_l_exists := 0; end if;
4148 if P_category = 'S' then g_ni_s_exists := 0; end if;
4149 --
4150 ELSE
4151 --
4152 -- The latest balances are not null or 0, so there must be a
4153 -- balance value for this category, set the individual existance
4154 -- variables due to reset of master return variable below.
4155 --
4156 if P_category = 'A' then g_ni_a_exists := 1; end if;
4157 if P_category = 'B' then g_ni_b_exists := 1; end if;
4158 if P_category = 'C' then g_ni_c_exists := 1; end if;
4159 if P_category = 'D' then g_ni_d_exists := 1; end if;
4160 if P_category = 'E' then g_ni_e_exists := 1; end if;
4161 if P_category = 'F' then g_ni_f_exists := 1; end if;
4162 if P_category = 'G' then g_ni_g_exists := 1; end if;
4163 if P_category = 'J' then g_ni_j_exists := 1; end if;
4164 if P_category = 'L' then g_ni_l_exists := 1; end if;
4165 if P_category = 'S' then g_ni_s_exists := 1; end if;
4166
4167 END IF; -- (Latest balances)
4168 --
4169 l_return := 0;
4170 if P_category = 'A' then l_return := g_ni_a_exists; end if;
4171 if P_category = 'B' then l_return := g_ni_b_exists; end if;
4172 if P_category = 'C' then l_return := g_ni_c_exists; end if;
4173 if P_category = 'D' then l_return := g_ni_d_exists; end if;
4174 if P_category = 'E' then l_return := g_ni_e_exists; end if;
4175 if P_category = 'F' then l_return := g_ni_f_exists; end if;
4176 if P_category = 'G' then l_return := g_ni_g_exists; end if;
4177 if P_category = 'J' then l_return := g_ni_j_exists; end if;
4178 if P_category = 'L' then l_return := g_ni_l_exists; end if;
4179 if P_category = 'S' then l_return := g_ni_s_exists; end if;
4180 --
4181 return l_return;
4182 end ni_category_exists_in_year;
4183 --
4184 FUNCTION get_master_action_id(p_action_type IN VARCHAR2,
4185 p_action_id IN NUMBER)
4186 RETURN NUMBER
4187 IS
4188 l_action_id number;
4189 BEGIN
4190 l_action_id := null;
4191 if (p_action_type in ('R','Q')) then
4192 select nvl(assact.source_action_id, assact.assignment_action_id)
4193 into l_action_id
4194 from pay_assignment_actions assact
4195 where assact.assignment_action_id = p_action_id;
4196 end if;
4197
4198 return l_action_id;
4199 END get_master_action_id;
4200 --
4201 --
4202 END hr_gbbal;
[Home] [Help]