[Home] [Help]
PACKAGE BODY: APPS.PAY_RUN_BALANCE_BUILD
Source
1 PACKAGE BODY pay_run_balance_build AS
2 /* $Header: pycorubl.pkb 120.6.12010000.1 2008/07/27 22:23:31 appldev ship $ */
3 --
4 --
5 /* Setup Glabals */
6 --
7 g_timeout date;
8 g_def_bal_id pay_defined_balances.defined_balance_id%type;
9 g_bal_lvl pay_balance_dimensions.dimension_level%type;
10 g_proc_mode varchar2(30);
11 g_load_type varchar2(30);
12 g_save_run_bals pay_legislation_rules.rule_mode%type;
13 g_save_asg_run_bals pay_legislation_rules.rule_mode%type;
14 g_globals_set boolean;
15 g_start_date date;
16 g_bus_grp number;
17 g_leg_code varchar2(30);
18 g_att_name varchar2(30);
19 --
20 ----------------------------------------------------------------------
21 --
22 -- mark_run_balance_status
23 --
24 -- Description
25 -- This procedure sets the run_balance status
26 --
27 ----------------------------------------------------------------------
28 procedure mark_run_balance_status(p_defined_balace_id in number,
29 p_business_group_id in number,
30 p_status in varchar2,
31 p_from_status in varchar2)
32 is
33 l_bal_valid_id pay_balance_validation.balance_validation_id%type;
34 l_update boolean;
35 begin
36 --
37 /* Can only set a valid status if the old status was processing */
38 l_update := FALSE;
39 if (p_status = 'V') then
40 if (p_from_status = 'P') then
41 l_update := TRUE;
42 end if;
43 else
44 l_update := TRUE;
45 end if;
46 --
47 if (l_update = TRUE) then
48 begin
49 --
50 select balance_validation_id
51 into l_bal_valid_id
52 from pay_balance_validation
53 where defined_balance_id = p_defined_balace_id
54 and business_group_id = p_business_group_id;
55 --
56 update pay_balance_validation
57 set run_balance_status = p_status,
58 balance_load_date = g_start_date
59 where balance_validation_id = l_bal_valid_id;
60 --
61 exception
62 when no_data_found then
63 --
64 insert into pay_balance_validation
65 (balance_validation_id,
66 defined_balance_id,
67 business_group_id,
68 run_balance_status,
69 balance_load_date)
70 values ( pay_balance_validation_s.nextval,
71 p_defined_balace_id,
72 p_business_group_id,
73 p_status,
74 g_start_date);
75 --
76 end;
77 end if;
78 end mark_run_balance_status;
79 ----------------------------------------------------------------------
80 --
81 -- set_run_bal_status
82 --
83 -- Description
84 -- This procedure sets the run_balance status
85 --
86 ----------------------------------------------------------------------
87 procedure set_run_bal_status (p_pactid in number,
88 p_status in varchar2)
89 is
90 cursor get_all_bals (p_pact_id in number
91 ) is
92 select pdb.defined_balance_id,
93 ppa.business_group_id,
94 nvl(pay_core_utils.get_parameter('BAL_LVL',
95 ppa.legislative_parameters),
96 'BOTH') balance_level,
97 pbd.dimension_level
98 from pay_payroll_actions ppa,
99 per_business_groups pbg,
100 pay_defined_balances pdb,
101 pay_balance_dimensions pbd
102 where ppa.payroll_action_id = p_pact_id
103 and ppa.business_group_id = pbg.business_group_id
104 and ((pdb.business_group_id = pbg.business_group_id
105 and pdb.legislation_code is null)
106 or
107 (pdb.legislation_code = pbg.legislation_code
108 and pdb.business_group_id is null)
109 or
110 (pdb.legislation_code is null
111 and pdb.business_group_id is null)
112 )
113 and pdb.save_run_balance = 'Y'
114 and pdb.balance_dimension_id = pbd.balance_dimension_id
115 and ((pbd.dimension_level =
116 nvl(pay_core_utils.get_parameter('BAL_LVL',
117 ppa.legislative_parameters),
118 'BOTH'))
119 or
120 (nvl(pay_core_utils.get_parameter('BAL_LVL',
121 ppa.legislative_parameters),
122 'BOTH')
123 = 'BOTH')
124 );
125 --
126 l_run_bal_stat pay_balance_validation.run_balance_status%type;
127 l_bus_grp pay_payroll_actions.business_group_id%type;
128 --
129 begin
130 --
131 if (g_proc_mode <> 'SINGLE') then
132 for dbrec in get_all_bals(p_pactid) loop
133 --
134 -- Only change the status if the legislation
135 -- rule allows the run balances to be created.
136 --
137 if (( dbrec.dimension_level = 'ASG'
138 and g_save_asg_run_bals = 'Y')
139 or
140 ( dbrec.dimension_level = 'GRP'
141 and g_save_run_bals = 'Y')
142 ) then
143 --
144 if (g_load_type <> 'DELTA') then
145 --
146 select nvl(pbv.run_balance_status, 'I')
147 into l_run_bal_stat
148 from pay_defined_balances pdb,
149 pay_balance_validation pbv
150 where pdb.defined_balance_id = dbrec.defined_balance_id
151 and pbv.defined_balance_id (+) = pdb.defined_balance_id
152 and pbv.business_group_id (+) = dbrec.business_group_id;
153 --
154 if (g_proc_mode = 'INVALID' and l_run_bal_stat <> 'V') then
155 --
156 mark_run_balance_status(dbrec.defined_balance_id,
157 dbrec.business_group_id,
158 p_status,
159 l_run_bal_stat);
160 --
161 elsif (g_proc_mode = 'ALL') then
162 --
163 mark_run_balance_status(dbrec.defined_balance_id,
164 dbrec.business_group_id,
165 p_status,
166 l_run_bal_stat);
167 --
168 end if;
169 --
170 else
171 --
172 if (p_status = 'V') then
173 update pay_balance_validation
174 set balance_load_date = g_start_date
175 where dbrec.defined_balance_id = defined_balance_id
176 and dbrec.business_group_id = business_group_id;
177 end if;
178 --
179 end if;
180 end if;
181 --
182 end loop;
183 else
184 --
185 /* Single Balance load */
186 --
187 select business_group_id
188 into l_bus_grp
189 from pay_payroll_actions
190 where payroll_action_id = p_pactid;
191 --
192 if (g_load_type <> 'DELTA') then
193 --
194 select nvl(pbv.run_balance_status, 'I')
195 into l_run_bal_stat
196 from pay_defined_balances pdb,
197 pay_balance_validation pbv
198 where pdb.defined_balance_id = g_def_bal_id
199 and pbv.defined_balance_id (+) = pdb.defined_balance_id
200 and pbv.business_group_id (+) = l_bus_grp;
201 --
202 mark_run_balance_status(g_def_bal_id,
203 l_bus_grp,
204 p_status,
205 l_run_bal_stat);
206 --
207 else
208 --
209 if (p_status = 'V') then
210 --
211 update pay_balance_validation
212 set balance_load_date = g_start_date
213 where defined_balance_id = g_def_bal_id
214 and business_group_id = l_bus_grp;
215 --
216 end if;
217 --
218 end if;
219 --
220 end if;
221 --
222 end set_run_bal_status;
223 --
224 procedure set_globals(p_pact_id in number)
225 is
226 begin
227 --
228 if (g_globals_set = FALSE) then
229 --
230 g_globals_set := TRUE;
231 --
232 /* Get parameters */
233 select
234 pay_core_utils.get_parameter('DEF_BAL_ID',
235 pa1.legislative_parameters),
236 nvl(pay_core_utils.get_parameter('BAL_LVL',
237 pa1.legislative_parameters),
238 'BOTH'),
239 nvl(pay_core_utils.get_parameter('PROC_MODE',
240 pa1.legislative_parameters),
241 'ALL'),
242 to_date(pay_core_utils.get_parameter('BAL_START_DATE',
243 pa1.legislative_parameters),
244 'YYYY/MM/DD'),
245 nvl(pay_core_utils.get_parameter('LOAD_TYPE',
246 pa1.legislative_parameters),
247 'ALL'),
248 pbg.legislation_code,
249 pbg.business_group_id,
250 'GEN_BAL_'||p_pact_id
251 into
252 g_def_bal_id,
253 g_bal_lvl,
254 g_proc_mode,
255 g_start_date,
256 g_load_type,
257 g_leg_code,
258 g_bus_grp,
259 g_att_name
260 from pay_payroll_actions pa1,
261 per_business_groups pbg
262 where pa1.payroll_action_id = p_pact_id
263 and pa1.business_group_id = pbg.business_group_id;
264 --
265 begin
266 select rule_mode
267 into g_save_run_bals
268 from pay_legislation_rules
269 where legislation_code = g_leg_code
270 and rule_type = 'SAVE_RUN_BAL';
271 exception
272 when no_data_found then
273 g_save_run_bals := 'N';
274 end;
275 --
276 begin
277 select rule_mode
278 into g_save_asg_run_bals
279 from pay_legislation_rules
280 where legislation_code = g_leg_code
281 and rule_type = 'SAVE_ASG_RUN_BAL';
282 exception
283 when no_data_found then
284 g_save_asg_run_bals := 'N';
285 end;
286 --
287 if (g_def_bal_id is not null) then
288 --
289 -- Override the balance level for a single balance load
290 --
291 select nvl(pbd.dimension_level, 'ASG')
292 into g_bal_lvl
293 from pay_defined_balances pdb,
294 pay_balance_dimensions pbd
295 where pdb.defined_balance_id = g_def_bal_id
296 and pdb.balance_dimension_id = pbd.balance_dimension_id;
297 --
298 g_proc_mode := 'SINGLE';
299 --
300 end if;
301 --
302 end if;
303 --
304 end set_globals;
305 --
306 /* Name : calculate_delta_asg_balances
307 Purpose :
308 Arguments :
309 Notes :
310 */
311
312 procedure calculate_delta_asg_balances( p_asg_act_id in number,
313 p_eff_date in date,
314 p_bal_load_date in date
315 )
316 is
317 --
318 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type
319 index by binary_integer;
320 --
321 l_delete_bals boolean;
322 l_def_bal_list pay_balance_pkg.t_balance_value_tab;
323 l_def_bal_id t_def_bal_id;
324 l_delta varchar2(10);
325 --
326 begin
327 --
328 hr_utility.set_location
329 ('Entering: pay_run_balance_build.calculate_delta_asg_balnces', 10);
330 if (g_proc_mode = 'SINGLE') then
331 --
332 -- If we have been supplied with a start date then delete
333 -- any run balance prior to this date.
334 --
335 l_delete_bals := FALSE;
336 if (g_start_date is not null
337 and g_start_date > p_eff_date) then
338 l_delete_bals := TRUE;
339 end if;
340 --
341 if (l_delete_bals) then
342 --
343 delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
344 from pay_run_balances prb
345 where prb.defined_balance_id = g_def_bal_id
346 and prb.assignment_action_id = p_asg_act_id;
347 --
348 else
349 --
350 -- Altered to use balance attributes
351 --
352 if g_load_type = 'DELTA' then
353 l_delta := 'Y';
354 else
355 l_delta := 'N';
356 end if;
357 --
358 if (p_eff_date < p_bal_load_date) then
359 pay_balance_pkg.create_asg_balance(g_def_bal_id
360 ,p_asg_act_id
361 ,'FORCE'
362 ,g_att_name
363 ,p_eff_date
364 ,l_delta);
365 end if;
366 --
367 end if;
368 --
369 else
370 -- If we have been supplied with a start date then delete
371 -- any run balance prior to this date.
372 --
373 l_delete_bals := FALSE;
374 if (g_start_date is not null
375 and g_start_date > p_eff_date) then
376 l_delete_bals := TRUE;
377 end if;
378 --
379 if (l_delete_bals) then
380 null;
381 --
382 else
383 --
384 if g_load_type = 'DELTA' then
385 l_delta := 'Y';
386 else
387 l_delta := 'N';
388 end if;
389 --
390 pay_balance_pkg.create_all_asg_balances(p_asg_act_id
391 ,g_att_name
392 ,'FORCE'
393 ,p_eff_date
394 ,l_delta
395 );
396 --
397 end if;
398 end if;
399 --
400 hr_utility.set_location
401 ('Leaving: pay_run_balance_build.calculate_delta_asg_balances', 100);
402 end calculate_delta_asg_balances;
403 --
404 /* Name : calculate_full_asg_balances
405 Purpose :
406 Arguments :
407 Notes :
408 */
409
410 procedure calculate_full_asg_balances( p_asg_act_id in number,
411 p_eff_date in date
412 )
413 is
417 --
414 l_delete_bals boolean;
415 begin
416 --
418 hr_utility.set_location(
419 'Enter:pay_run_balance_build.calculate_full_asg_balances',10);
420 --
421 if (g_proc_mode = 'SINGLE') then
422 --
423 -- If we have been supplied with a start date then delete
424 -- any run balance prior to this date.
425 --
426 hr_utility.set_location(
427 'pay_run_balance_build.calculate_full_asg_balances',20);
428 --
429 l_delete_bals := FALSE;
430 if (g_start_date is not null
431 and g_start_date > p_eff_date) then
432 l_delete_bals := TRUE;
433 end if;
434 --
435 if (l_delete_bals) then
436 --
437 --
438 hr_utility.set_location(
439 'pay_run_balance_build.calculate_full_asg_balances',30);
440
441 --
442 delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
443 from pay_run_balances prb
444 where prb.defined_balance_id = g_def_bal_id
445 and prb.assignment_action_id = p_asg_act_id;
446 --
447 else
448 --
449 --
450 hr_utility.set_location(
451 'pay_run_balance_build.calculate_full_asg_balances',40);
452 --
453 pay_balance_pkg.create_asg_balance(g_def_bal_id
454 ,p_asg_act_id
455 ,'FORCE'
456 ,g_att_name);
457 --
458 end if;
459 --
460 else
461 --
462 hr_utility.set_location(
463 'pay_run_balance_build.calculate_full_asg_balances',50);
464 --
465 -- If we have been supplied with a start date then delete
466 -- any run balance prior to this date.
467 --
468 l_delete_bals := FALSE;
469 if (g_start_date is not null
470 and g_start_date > p_eff_date) then
471 l_delete_bals := TRUE;
472 end if;
473 --
474 if (l_delete_bals) then
475 --
476 hr_utility.set_location(
477 'pay_run_balance_build.calculate_full_asg_balances',60);
478 null;
479 --
480 else
481 --
482 hr_utility.set_location(
483 'pay_run_balance_build.calculate_full_asg_balances',70);
484 --
485 pay_balance_pkg.create_all_asg_balances(p_asg_act_id,
486 g_att_name,
487 'TRUSTED'
488 );
489 end if;
490 end if;
491 --
492 --
493 hr_utility.set_location(
494 'Leaving:pay_run_balance_build.calculate_full_asg_balances',80);
495 --
496 end calculate_full_asg_balances;
497 --
498 /* Name : process_asg_lvl_balances
499 Purpose :
500 Arguments :
501 Notes :
502 */
503
504 procedure process_asg_lvl_balances(p_asg_id in number,
505 p_bus_grp in number)
506 is
507 --
508 cursor getaa (p_assid in number
509 ) is
510 select /*+ ORDERED USE_NL(ppa prt)
511 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
512 INDEX(prt PAY_RUN_TYPES_PK) */
513 paa.assignment_action_id,
514 ppa.effective_date,
515 nvl(prt.run_method, 'N') run_method,
516 ppa.business_group_id,
517 pbg.legislation_code
518 from pay_assignment_actions paa,
519 pay_payroll_actions ppa,
520 pay_run_types_f prt,
521 per_business_groups_perf pbg
522 where paa.assignment_id = p_assid
523 and paa.payroll_action_id = ppa.payroll_action_id
524 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
525 and ppa.business_group_id = pbg.business_group_id
526 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
527 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
528 and nvl(prt.effective_end_date, ppa.effective_date)
529 order by 2;
530 --
531 l_run_bal_status pay_balance_validation.run_balance_status%type;
532 l_bal_load_date pay_balance_validation.balance_load_date%type;
533 begin
534 --
535 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',10);
536 --
537 l_run_bal_status := 'V';
538 --
539 if (g_proc_mode <> 'SINGLE') then
540 --
541 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',20);
542 --
543 if (g_proc_mode = 'INVALID') then
544 --
545 -- altered delete statement to use pay_balance_attributes rather than
546 -- pay_balance_validation to identify rows to be deleted
547 --
548 delete /*+ USE_NL(prb) INDEX(prb PAY_RUN_BALANCES_N1) */
549 from pay_run_balances prb
550 where prb.assignment_id = p_asg_id
551 and prb.defined_balance_id in
552 (select pba.defined_balance_id
553 from pay_balance_attributes pba
554 , pay_bal_attribute_definitions bad
555 where pba.attribute_id = bad.attribute_id
556 and bad.attribute_name = g_att_name);
560 -- We must be regenerating all balances
557 --
558 else
559 --
561 --
562 if (g_load_type <> 'DELTA') then
563 --
564 delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
565 from pay_run_balances prb
566 where prb.assignment_id = p_asg_id;
567 --
568 else
569 --
570 delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
571 from pay_run_balances prb
572 where prb.assignment_id = p_asg_id
573 and exists (select ''
574 from pay_balance_validation pbv
575 where pbv.defined_balance_id = prb.defined_balance_id
576 and pbv.business_group_id = p_bus_grp
577 and prb.effective_date < greatest(nvl(pbv.balance_load_date,
578 to_date('0001/01/01 00:00:00',
579 'YYYY/MM/DD HH24:MI:SS')
580 ),
581 nvl(g_start_date,
582 to_date('0001/01/01 00:00:00',
583 'YYYY/MM/DD HH24:MI:SS')
584 )
585 )
586 );
587 --
588 end if;
589 --
590 end if;
591 else
592 --
593 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',20);
594 --
595 -- No need to delete the balance, but we do need to get the status
596 --
597 begin
598 --
599 select balance_load_date,
600 run_balance_status
601 into l_bal_load_date,
602 l_run_bal_status
603 from pay_balance_validation
604 where defined_balance_id = g_def_bal_id
605 and business_group_id = p_bus_grp;
606 --
607 exception
608 when no_data_found then
609 l_run_bal_status := 'I';
610 end;
611 end if;
612 --
613 for aarec in getaa(p_asg_id) loop
614 --
615 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',30);
616 --
617 if (aarec.run_method <> 'C') then
618 --
619 --
620 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',40);
621 --
622 if (g_load_type <> 'DELTA') then
623 --
624 --
625 hr_utility.set_location(
626 'pay_run_balance_build.process_asg_lvl_balance',50);
627 --
628 calculate_full_asg_balances( aarec.assignment_action_id,
629 aarec.effective_date
630 );
631 --
632 else
633 --
634 --
635 hr_utility.set_location(
636 'pay_run_balance_build.process_asg_lvl_balance',60);
637 --
638 if (l_run_bal_status = 'V') then
639 --
640 --
641 hr_utility.set_location(
642 'pay_run_balance_build.process_asg_lvl_balance',70);
643 --
644 calculate_delta_asg_balances( aarec.assignment_action_id,
645 aarec.effective_date,
646 l_bal_load_date
647 );
648 end if;
649 --
650 end if;
651 --
652 end if;
653 end loop;
654 --
655 --
656 hr_utility.set_location(
657 'Leaving:pay_run_balance_build.process_asg_lvl_balance',80);
658 --
659 end process_asg_lvl_balances;
660 --
661 /* Name : calculate_delta_grp_balances
662 Purpose :
663 Arguments :
664 Notes :
665 */
666
667 procedure calculate_delta_grp_balances(p_pactid in number,
668 p_eff_date in date,
669 p_bus_grp in number,
670 p_leg_code in varchar2,
671 p_bal_load_date in date
672 )
673 is
674 --
675 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type
676 index by binary_integer;
677 --
678 l_delete_bals boolean;
679 l_def_bal_list pay_balance_pkg.t_balance_value_tab;
680 l_def_bal_id t_def_bal_id;
681 l_delta varchar2(10);
682 begin
683 --
684 hr_utility.set_location
685 ('Entering: pay_run_balance_build.calculate_delta_grp_balances', 10);
686 --
687 if (g_proc_mode = 'SINGLE') then
688 --
689 -- If we have been supplied with a start date then delete
690 -- any run balance prior to this date.
691 --
692 l_delete_bals := FALSE;
693 if (g_start_date is not null
694 and g_start_date > p_eff_date) then
695 l_delete_bals := TRUE;
696 end if;
697 --
698 if (l_delete_bals) then
702 and payroll_action_id = p_pactid;
699 --
700 delete from pay_run_balances
701 where defined_balance_id = g_def_bal_id
703 --
704 else
705 --
706 -- Altered to use balance_attributes
707 --
708 if g_load_type = 'DELTA' then
709 l_delta := 'Y';
710 else
711 l_delta := 'N';
712 end if;
713 --
714 if(p_eff_date < p_bal_load_date) then
715 pay_balance_pkg.create_group_balance
716 (g_def_bal_id
717 ,p_pactid
718 ,'FORCE'
719 ,g_att_name
720 ,p_eff_date
721 ,l_delta);
722 end if;
723 --
724 end if;
725 else
726 --
727 -- If we have been supplied with a start date then delete
728 -- any run balance prior to this date.
729 --
730 l_delete_bals := FALSE;
731 if (g_start_date is not null
732 and g_start_date > p_eff_date) then
733 l_delete_bals := TRUE;
734 end if;
735 --
736 if (l_delete_bals) then
737 --
738 if (g_proc_mode = 'INVALID') then
739 --
740 delete from pay_run_balances prb
741 where prb.payroll_action_id = p_pactid
742 and exists (select ''
743 from pay_balance_validation pbv
744 where pbv.defined_balance_id =
745 prb.defined_balance_id
746 and pbv.run_balance_status = 'P'
747 and pbv.business_group_id = p_bus_grp);
748 else
749 --
750 -- We must be regenerating all balances
751 --
752 if (g_load_type <> 'DELTA') then
753 --
754 delete from pay_run_balances
755 where payroll_action_id = p_pactid;
756 else
757 delete
758 from pay_run_balances prb
759 where prb.payroll_action_id = p_pactid
760 and g_start_date is not null
761 and g_start_date > p_eff_date;
762 --
763 end if;
764 end if;
765 --
766 else
767 --
768 -- Use call to create_all_grp_balances to take advantage of balance
769 -- attributes
770 --
771 if g_load_type = 'DELTA' then
772 l_delta := 'Y';
773 else
774 l_delta := 'N';
775 end if;
776 --
777 pay_balance_pkg.create_all_group_balances
778 (p_pact_id => p_pactid
779 ,p_bal_list => g_att_name
780 ,p_load_type => 'FORCE'
781 ,p_eff_date => p_eff_date
782 ,p_delta => l_delta
783 );
784 end if;
785 end if;
786 hr_utility.set_location
787 ('Leaving: pay_run_balance_build.calculate_delta_grp_balances', 100);
788 --
789 end calculate_delta_grp_balances;
790 --
791 /* Name : calculate_full_grp_balances
792 Purpose :
793 Arguments :
794 Notes :
795 */
796
797 procedure calculate_full_grp_balances( p_pactid in number,
798 p_eff_date in date,
799 p_bus_grp in number
800 )
801 is
802 l_delete_bals boolean;
803 begin
804 if (g_proc_mode = 'SINGLE') then
805 --
806 -- If we have been supplied with a start date then delete
807 -- any run balance prior to this date.
808 --
809 l_delete_bals := FALSE;
810 if (g_start_date is not null
811 and g_start_date > p_eff_date) then
812 l_delete_bals := TRUE;
813 end if;
814 --
815 if (l_delete_bals) then
816 --
817 delete from pay_run_balances
818 where defined_balance_id = g_def_bal_id
819 and payroll_action_id = p_pactid;
820 --
821 else
822 --
823 pay_balance_pkg.create_group_balance
824 (g_def_bal_id
825 ,p_pactid
826 ,'FORCE'
827 ,g_att_name);
828 end if;
829 else
830 --
831 -- If we have been supplied with a start date then delete
832 -- any run balance prior to this date.
833 --
834 l_delete_bals := FALSE;
835 if (g_start_date is not null
836 and g_start_date > p_eff_date) then
837 l_delete_bals := TRUE;
838 end if;
839 --
840 if (l_delete_bals) then
841 --
842 if (g_proc_mode = 'INVALID') then
843 --
844 delete from pay_run_balances prb
845 where prb.payroll_action_id = p_pactid
846 and exists (select ''
850 and pbv.business_group_id = p_bus_grp);
847 from pay_balance_validation pbv
848 where pbv.defined_balance_id = prb.defined_balance_id
849 and pbv.run_balance_status = 'P'
851 else
852 --
853 -- We must be regenerating all balances
854 --
855 delete from pay_run_balances
856 where payroll_action_id = p_pactid;
857 end if;
858 --
859 else
860 --
861 pay_balance_pkg.create_all_group_balances(p_pactid,
862 g_att_name,
863 'TRUSTED'
864 );
865 end if;
866 end if;
867 --
868 end calculate_full_grp_balances;
869 --
870 /* Name : process_group_lvl_balances
871 Purpose :
872 Arguments :
873 Notes :
874 */
875
876 procedure process_group_lvl_balances( p_pactid in number)
877 is
878 l_grp_eff_date date;
879 l_bus_grp pay_payroll_actions.business_group_id%type;
880 l_run_bal_status pay_balance_validation.run_balance_status%type;
881 l_bal_load_date pay_balance_validation.balance_load_date%type;
882 l_leg_code per_business_groups.legislation_code%type;
883 begin
884 --
885 begin
886 select ppa.effective_date,
887 ppa.business_group_id,
888 pbg.legislation_code
889 into l_grp_eff_date,
890 l_bus_grp,
891 l_leg_code
892 from pay_payroll_actions ppa,
893 per_business_groups pbg
894 where ppa.payroll_action_id = p_pactid
895 and ppa.business_group_id = pbg.business_group_id;
896 exception
897 --
898 -- Bug 4031667: If the payroll action no longer exists then continue without
899 -- erroring and without attempting to process the payroll action.
900 --
901 when no_data_found then
902 return;
903 end;
904 --
905 l_run_bal_status := 'V';
906 --
907 if (g_proc_mode <> 'SINGLE') then
908 --
909 if (g_proc_mode = 'INVALID') then
910 --
911 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
912 from pay_run_balances prb
913 where prb.payroll_action_id = p_pactid
914 and exists (select /*+ INDEX(pbv PAY_BALANCE_VALIDATION_UK1) */ ''
915 from pay_balance_validation pbv
916 where pbv.defined_balance_id = prb.defined_balance_id
917 and pbv.run_balance_status = 'P'
918 and pbv.business_group_id = l_bus_grp);
919 --
920 else
921 --
922 -- We must be regenerating all balances
923 --
924 if (g_load_type <> 'DELTA') then
925 --
926 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
927 from pay_run_balances prb
928 where prb.payroll_action_id = p_pactid;
929 --
930 else
931 --
932 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
933 from pay_run_balances prb
934 where prb.payroll_action_id = p_pactid
935 and exists (select ''
936 from pay_balance_validation pbv
937 where pbv.defined_balance_id = prb.defined_balance_id
938 and pbv.business_group_id = l_bus_grp
939 and prb.effective_date < greatest(nvl(pbv.balance_load_date,
940 to_date('0001/01/01 00:00:00',
941 'YYYY/MM/DD HH24:MI:SS')
942 ),
943 nvl(g_start_date,
944 to_date('0001/01/01 00:00:00',
945 'YYYY/MM/DD HH24:MI:SS')
946 )
947 )
948 );
949 --
950 end if;
951 --
952 end if;
953 --
954 else
955 -- No need to delete the balance, but we do need to get the status
956 --
957 begin
958 --
959 select balance_load_date,
960 run_balance_status
961 into l_bal_load_date,
962 l_run_bal_status
963 from pay_balance_validation
964 where defined_balance_id = g_def_bal_id
965 and business_group_id = l_bus_grp;
966 --
967 exception
968 when no_data_found then
969 l_run_bal_status := 'I';
970 end;
971 --
972 end if;
973 --
974 if (g_load_type <> 'DELTA') then
975 --
976 calculate_full_grp_balances( p_pactid,
977 l_grp_eff_date,
978 l_bus_grp
979 );
980 --
981 else
982 --
986 l_grp_eff_date,
983 if (l_run_bal_status = 'V') then
984 --
985 calculate_delta_grp_balances( p_pactid,
987 l_bus_grp,
988 l_leg_code,
989 l_bal_load_date
990 );
991 end if;
992 --
993 end if;
994 --
995 end process_group_lvl_balances;
996 --
997 /* Name : action_achive_data
998 Purpose : This performs the US specific employee context setting for the
999 Tax Remittance Archiver and for the payslip,check writer and
1000 Deposit Advice modules.
1001 Arguments :
1002 Notes :
1003 */
1004
1005 PROCEDURE action_archive_data( p_assactid in number
1006 ,p_effective_date in date)
1007 IS
1008 --
1009 cursor getaa (p_assactid in number
1010 ) is
1011 select paa.assignment_action_id,
1012 ppa.effective_date,
1013 nvl(prt.run_method, 'N') run_method,
1014 ppa.business_group_id
1015 from pay_assignment_actions paa,
1016 pay_payroll_actions ppa,
1017 pay_run_types_f prt,
1018 pay_assignment_actions paa_arch
1019 where paa_arch.assignment_action_id = p_assactid
1020 and paa_arch.assignment_id = paa.assignment_id
1021 and paa.payroll_action_id = ppa.payroll_action_id
1022 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1023 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
1024 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
1025 and nvl(prt.effective_end_date, ppa.effective_date)
1026 order by 2;
1027 --
1028 load_bals boolean;
1029 current_date date;
1030 l_payroll_action_id number;
1031 l_object_id number;
1032 l_delete_bals boolean;
1033 l_grp_eff_date date;
1034 l_bus_grp pay_payroll_actions.business_group_id%type;
1035 l_asg_id pay_assignment_actions.assignment_id%type;
1036 --
1037 BEGIN
1038 hr_utility.set_location(
1039 'Entering:pay_run_balance_build.action_archive_data',10);
1040 --
1041 select paa.payroll_action_id,
1042 paa.object_id,
1043 paa.assignment_id,
1044 ppa.business_group_id
1045 into l_payroll_action_id, l_object_id, l_asg_id, l_bus_grp
1046 from pay_assignment_actions paa,
1047 pay_payroll_actions ppa
1048 where paa.assignment_action_id = p_assactid
1049 and ppa.payroll_action_id = paa.payroll_action_id;
1050 --
1051 set_globals(l_payroll_action_id);
1052 --
1053 --
1054 hr_utility.set_location(
1055 'pay_run_balance_build.action_archive_data',20);
1056 --
1057 /* Have we timed out */
1058 select sysdate
1059 into current_date
1060 from sys.dual;
1061 --
1062 if (current_date > g_timeout) then
1063 /* Error, timed out process */
1064 hr_utility.set_message(801,'PAY_289014_PUR_TIMEOUT');
1065 hr_utility.raise_error;
1066 end if;
1067 --
1068 /* do we need to load assignment balances */
1069 load_bals := TRUE;
1070 --
1071 if (load_bals) then
1072 --
1073 --
1074 hr_utility.set_location(
1075 'pay_run_balance_build.action_archive_data',30);
1076 --
1077 if (g_bal_lvl = 'GRP') then
1078 --
1079 --
1080 hr_utility.set_location(
1081 'pay_run_balance_build.action_archive_data',40);
1082 --
1083 /* Only do something if the legislation rule is set */
1084 if (g_save_run_bals = 'Y') then
1085 --
1086 --
1087 hr_utility.set_location(
1088 'pay_run_balance_build.action_archive_data',50);
1089 --
1090 process_group_lvl_balances(l_object_id);
1091 --
1092 end if;
1093 else
1094 --
1095 --
1096 hr_utility.set_location(
1097 'pay_run_balance_build.action_archive_data',60);
1098 --
1099 /* Only do something if the legislation rule is set */
1100 if (g_save_asg_run_bals = 'Y') then
1101 --
1102 --
1103 hr_utility.set_location(
1104 'pay_run_balance_build.action_archive_data',70);
1105 --
1106 process_asg_lvl_balances(l_asg_id,
1107 l_bus_grp);
1108 --
1109 end if;
1110 end if;
1111 --
1112 --
1113 hr_utility.set_location(
1114 'pay_run_balance_build.action_archive_data',80);
1115 --
1116 end if;
1117 --
1118 --
1119 hr_utility.set_location(
1120 'Leaving:pay_run_balance_build.action_archive_data',90);
1121 --
1122 END action_archive_data;
1123
1124
1125 /* Name : generate_attribute
1126 Purpose : This generates the attribute to process in the generation.
1127 Arguments :
1128 Notes :
1129 */
1130
1131 PROCEDURE generate_attribute( p_payroll_action_id in number)
1132 is
1133 cursor get_grp (p_bus_grp_id in number,
1134 p_leg_code in varchar2,
1135 p_bal_list in varchar2,
1136 p_def_bal in number)
1137 is
1138 select
1139 pdb.defined_balance_id
1140 from
1141 pay_balance_types pbt,
1142 pay_defined_balances pdb,
1143 pay_balance_dimensions pbd
1144 where pbd.dimension_level = 'GRP'
1145 and pdb.save_run_balance = 'Y'
1146 and pdb.balance_dimension_id = pbd.balance_dimension_id
1147 and pdb.balance_type_id = pbt.balance_type_id
1148 and ((pdb.business_group_id = p_bus_grp_id
1149 and pdb.legislation_code is null)
1150 or
1151 (pdb.legislation_code = p_leg_code
1152 and pdb.business_group_id is null)
1153 or
1154 (pdb.legislation_code is null
1155 and pdb.business_group_id is null)
1156 )
1157 and ( p_bal_list <> 'INVALID'
1158 or ( p_bal_list = 'INVALID'
1159 and exists (select ''
1160 from pay_balance_validation pbv
1161 where pbv.defined_balance_id = pdb.defined_balance_id
1162 and pbv.business_group_id = p_bus_grp_id
1163 and pbv.run_balance_status = 'P')
1164 )
1165 )
1166 and ( p_def_bal is null
1167 or p_def_bal = pdb.defined_balance_id
1168 );
1169 --
1170 cursor get_asg (p_bus_grp_id in number,
1171 p_leg_code in varchar2,
1172 p_bal_list in varchar2,
1173 p_def_bal in number)
1174 is
1175 select
1176 pdb.defined_balance_id
1177 from
1178 pay_balance_types pbt,
1179 pay_defined_balances pdb,
1180 pay_balance_dimensions pbd
1181 where pbd.dimension_level = 'ASG'
1182 and pdb.save_run_balance = 'Y'
1183 and pdb.balance_dimension_id = pbd.balance_dimension_id
1184 and pdb.balance_type_id = pbt.balance_type_id
1185 and ( p_bal_list <> 'INVALID'
1186 or ( p_bal_list = 'INVALID'
1187 and exists (select ''
1188 from pay_balance_validation pbv
1189 where pbv.defined_balance_id = pdb.defined_balance_id
1190 and pbv.business_group_id = p_bus_grp_id
1191 and pbv.run_balance_status = 'P')
1192 )
1193 )
1194 and ( p_def_bal is null
1195 or p_def_bal = pdb.defined_balance_id
1196 )
1197 and ((pdb.business_group_id = p_bus_grp_id
1198 and pdb.legislation_code is null)
1199 or
1203 (pdb.legislation_code is null
1200 (pdb.legislation_code = p_leg_code
1201 and pdb.business_group_id is null)
1202 or
1204 and pdb.business_group_id is null)
1205 );
1206 --
1207 l_attribute_name pay_bal_attribute_definitions.attribute_name%type;
1208 l_attribute_id pay_bal_attribute_definitions.attribute_id%type;
1209 begin
1210 --
1211 -- Use for both DELTA and NON DELTA modes
1212 --
1213 l_attribute_name := 'GEN_BAL_'||p_payroll_action_id;
1214 --
1215 select pay_bal_attribute_definition_s.nextval
1216 into l_attribute_id
1217 from dual;
1218 --
1219 insert into pay_bal_attribute_definitions
1220 (attribute_id,
1221 attribute_name,
1222 alterable,
1223 business_group_id)
1224 values (l_attribute_id,
1225 l_attribute_name,
1226 'N',
1227 g_bus_grp);
1228 --
1229 if ( g_bal_lvl = 'GRP'
1230 or g_bal_lvl = 'BOTH') then
1231 --
1232 for grprec in get_grp (g_bus_grp,
1233 g_leg_code,
1234 g_proc_mode,
1235 g_def_bal_id) loop
1236 --
1237 insert into pay_balance_attributes
1238 (balance_attribute_id,
1239 attribute_id,
1240 defined_balance_id
1241 )
1242 values
1243 (pay_balance_attributes_s.nextval,
1244 l_attribute_id,
1245 grprec.defined_balance_id
1246 );
1247 --
1248 end loop;
1249 --
1250 end if;
1251 --
1252 if ( g_bal_lvl = 'ASG'
1253 or g_bal_lvl = 'BOTH') then
1254 --
1255 for asgrec in get_asg (g_bus_grp,
1256 g_leg_code,
1257 g_proc_mode,
1258 g_def_bal_id) loop
1259 --
1260 insert into pay_balance_attributes
1261 (balance_attribute_id,
1262 attribute_id,
1263 defined_balance_id
1264 )
1265 values
1266 (pay_balance_attributes_s.nextval,
1267 l_attribute_id,
1268 asgrec.defined_balance_id
1269 );
1270 --
1271 end loop;
1272 --
1273 end if;
1274 --
1275 --
1276 end generate_attribute;
1277 --
1278 /* Name : action_range_cursor
1279 Purpose : This returns the select statement that is used to created the
1280 range rows for the Tax Filing (FLS) Archiver.
1281 Arguments :
1282 Notes :
1283 */
1284
1285 PROCEDURE action_range_cursor( p_payroll_action_id in number
1286 ,p_sqlstr out nocopy varchar2)
1287 IS
1288 --
1289 lv_sql_string VARCHAR2(32000);
1290 begin
1291 hr_utility.trace('In Range cursor before building string ');
1292 --
1293 set_globals(p_payroll_action_id);
1294 set_run_bal_status(p_payroll_action_id, 'P');
1295 --
1296 lv_sql_string :=
1297 'select 1
1298 from dual
1299 where 1 = 0
1300 and 1 = :payroll_action_id';
1301 --
1302 /* Only do something meaningful if the legislation rule is set */
1303 if (g_bal_lvl = 'GRP') then
1304 if (g_save_run_bals = 'Y') then
1305 lv_sql_string :=
1306 'select ppa_r.payroll_action_id
1307 from pay_payroll_actions ppa,
1308 pay_payroll_actions ppa_r
1309 where ppa.payroll_action_id = :payroll_action_id
1310 and ppa_r.action_type in (''R'',''Q'',''B'', ''I'',''V'')
1311 and ppa.business_group_id = ppa_r.business_group_id
1312 order by ppa_r.payroll_action_id';
1313 end if;
1314 else
1315 if (g_save_asg_run_bals = 'Y') then
1316 lv_sql_string :=
1317 'select distinct asg.person_id
1318 from
1319 per_periods_of_service pos,
1320 per_assignments_f asg,
1321 pay_payroll_actions ppa
1322 where ppa.payroll_action_id = :payroll_action_id
1323 and pos.person_id = asg.person_id
1324 and pos.period_of_service_id = asg.period_of_service_id
1325 and pos.business_group_id = ppa.business_group_id
1326 and asg.business_group_id = ppa.business_group_id
1327 order by asg.person_id';
1328 end if;
1329 end if;
1330 --
1331 p_sqlstr := lv_sql_string;
1332 hr_utility.trace('In Range cursor after building string ');
1333 --
1334 hr_utility.trace('Build Bal Attribute Group');
1335 --
1336 generate_attribute(p_payroll_action_id);
1337 --
1338 hr_utility.trace('Built Bal Attribute Group');
1339
1340 END action_range_cursor;
1341
1342
1343 /* Name : action_action_creation
1344 Purpose : This creates the assignment actions for a specific chunk
1345 of people to be archived by the year end pre-process.
1346 Arguments :
1347 Notes :
1348 */
1349
1353 ,p_chunk in number)
1350 PROCEDURE action_action_creation( p_payroll_action_id in number
1351 ,p_start_person_id in number
1352 ,p_end_person_id in number
1354 IS
1355 --
1356 cursor get_pact(stpactid in number,
1357 enpactid in number,
1358 pact_id in number
1359 ) is
1360 select ppa.payroll_action_id
1361 from pay_payroll_actions ppa,
1362 pay_payroll_actions ppa_arc
1363 where ppa.payroll_action_id between stpactid and enpactid
1364 and ppa_arc.payroll_action_id = pact_id
1365 and ppa.business_group_id = ppa_arc.business_group_id
1366 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
1367 order by 1;
1368 --
1369 cursor get_asg( stperson in number
1370 ,endperson in number
1371 , pact_id in number
1372 ) is
1373 select distinct
1374 paf.assignment_id assignment_id,
1375 paf.person_id
1376 from
1377 per_periods_of_service pos,
1378 per_all_assignments_f paf,
1379 pay_payroll_actions ppa
1380 where pos.person_id between stperson and endperson
1381 and pos.person_id = paf.person_id
1382 and pos.period_of_service_id = paf.period_of_service_id
1383 and pos.business_group_id = ppa.business_group_id
1384 and ppa.payroll_action_id = pact_id
1385 and ppa.business_group_id = paf.business_group_id
1386 order by 1, 2;
1387 --
1388 ln_lockingactid number;
1389 --
1390 begin
1391 --
1392 hr_utility.trace('In action Creation before getting payroll information');
1393 --
1394 set_globals(p_payroll_action_id);
1395 --
1396 if (g_bal_lvl = 'GRP') then
1397 for pactrec in get_pact( p_start_person_id,
1398 p_end_person_id,
1399 p_payroll_action_id
1400 ) loop
1401 --
1402 select pay_assignment_actions_s.nextval
1403 into ln_lockingactid
1404 from dual;
1405 --
1406 -- insert into pay_assignment_actions.
1407 hr_nonrun_asact.insact(ln_lockingactid,
1408 -1,
1409 p_payroll_action_id,
1410 p_chunk,
1411 null,
1412 null,
1413 'U',
1414 null,
1415 pactrec.payroll_action_id,
1416 'PPA');
1417 --
1418 end loop;
1419 else
1420 for aarec in get_asg( p_start_person_id,
1421 p_end_person_id,
1422 p_payroll_action_id
1423 ) loop
1424 --
1425 select pay_assignment_actions_s.nextval
1426 into ln_lockingactid
1427 from dual;
1428 --
1429 -- insert into pay_assignment_actions.
1430 hr_nonrun_asact.insact(ln_lockingactid,
1431 aarec.assignment_id,
1432 p_payroll_action_id,
1433 p_chunk,
1434 null,
1435 null,
1436 'U',
1437 null);
1438 --
1439 end loop;
1440 end if;
1441 --
1442 end action_action_creation;
1443
1444 /* Name : action_archinit
1445 Purpose : This performs the context initialization.
1446 Arguments :
1447 Notes :
1448 */
1449
1450 procedure action_archinit(p_payroll_action_id in number) is
1451 --
1452 l_timeout_sec number;
1453 current_time date;
1454 --
1455 begin
1456 --
1457 /* Get Action Parameters */
1458 declare
1459 begin
1460 --
1461 select to_number(parameter_value),
1462 sysdate
1463 into l_timeout_sec,
1464 current_time
1465 from pay_action_parameters
1466 where parameter_name = 'PROCESS_TIMEOUT';
1467 --
1468 --
1469 -- l_timeout_sec is in minutes, convert to seconds
1470 -- then convert to oracle time.
1471 --
1472 l_timeout_sec := l_timeout_sec * 60;
1473 l_timeout_sec := l_timeout_sec/86400;
1474 g_timeout := current_time + l_timeout_sec;
1475 --
1476 exception
1477 when no_data_found then
1478 l_timeout_sec := null;
1479 g_timeout := to_date('4712/12/31', 'YYYY/MM/DD');
1480 end;
1481 --
1482 end action_archinit;
1483 --
1484 /* Name : deinitialise
1485 Purpose : This procedure simply removes all the actions processed
1486 in this run
1487 Arguments :
1488 Notes :
1489 */
1490 procedure deinitialise (pactid in number)
1491 is
1492 --
1493 cursor getpa (p_pact_id in number) is
1494 select ppa.payroll_action_id ,
1495 ppa.action_sequence,
1496 ppa.effective_date,
1497 ppa.business_group_id
1498 from
1499 pay_payroll_actions ppa_arch,
1500 pay_payroll_actions ppa
1501 where ppa_arch.payroll_action_id = p_pact_id
1502 and ppa_arch.business_group_id = ppa.business_group_id
1503 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1504 order by 2;
1505 --
1506 remove_act varchar2(10);
1507 act_com number;
1508 load_bals boolean;
1509 cnt number;
1510 l_delete_bals boolean;
1511 begin
1512 --
1513 set_globals(pactid);
1514 --
1515 hr_utility.set_location('pay_run_balance_build.deinitialise', 10);
1516 /* only do something if the legislation rule is set */
1517 if (g_save_run_bals = 'Y') then
1518 /* do we need to load assignment balances */
1519 hr_utility.set_location('pay_run_balance_build.deinitialise', 20);
1520 load_bals := TRUE;
1521 if (g_proc_mode = 'SINGLE') then
1522 load_bals := FALSE;
1523 elsif (g_bal_lvl = 'GRP') then
1524 load_bals := FALSE;
1525 end if;
1526 --
1527 cnt := 0;
1528 if (load_bals) then
1529 --
1530 hr_utility.set_location('pay_run_balance_build.deinitialise', 30);
1531 --
1532 if (g_bal_lvl = 'BOTH') then
1533 hr_utility.set_location('pay_run_balance_build.deinitialise', 40);
1534 for parec in getpa(pactid) loop
1535 hr_utility.set_location('pay_run_balance_build.deinitialise', 50);
1536 --
1537 process_group_lvl_balances(parec.payroll_action_id);
1538 --
1539 end loop;
1540 end if;
1541 --
1542 end if;
1543 end if;
1544 --
1545 hr_utility.set_location('pay_run_balance_build.deinitialise', 60);
1546 --
1547 /* Remove the actions if needed */
1548 select count(*)
1549 into act_com
1550 from pay_assignment_actions
1551 where payroll_action_id = pactid
1552 and action_status <> 'C';
1553 --
1554 if act_com = 0 then
1555 --
1556 /* Set the Balance Status */
1557 --
1558 set_run_bal_status(pactid, 'V');
1559 --
1560 select pay_core_utils.get_parameter('REMOVE_ACT',
1561 pa1.legislative_parameters)
1562 into remove_act
1563 from pay_payroll_actions pa1
1564 where pa1.payroll_action_id = pactid;
1565 --
1566 if (remove_act is null or remove_act = 'Y') then
1567 pay_archive.remove_report_actions(pactid);
1568 --
1569 --
1570 delete from pay_balance_attributes
1571 where attribute_id in (select attribute_id
1572 from pay_bal_attribute_definitions
1573 where attribute_name = g_att_name
1574 );
1575 delete from pay_bal_attribute_definitions
1576 where attribute_name = g_att_name;
1577 end if;
1578 end if;
1579 --
1580 --hr_utility.trace_off;
1581 end deinitialise;
1582 --
1583 begin
1584 g_globals_set := FALSE;
1585 end pay_run_balance_build;