[Home] [Help]
PACKAGE BODY: APPS.PAY_RUN_BALANCE_BUILD
Source
1 PACKAGE BODY pay_run_balance_build AS
2 /* $Header: pycorubl.pkb 120.9.12020000.7 2013/01/25 11:06:33 asnell 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 g_att_cached boolean; /*Bug 9315998*/
20 g_debug boolean := hr_utility.debug_enabled;
21 g_purge_only boolean default FALSE;
22 g_program_name fnd_concurrent_programs.concurrent_program_name%type;
23 g_generate_pact pay_payroll_actions.payroll_action_id%type;
24
25 Type typ_def_bal is table of pay_defined_balances.defined_balance_id%type index by binary_integer;
26 g_tab_def_bal typ_def_bal;
27
28 type t_balance_validation_rec is record
29 ( DEFINED_BALANCE_ID pay_balance_validation.defined_balance_id%type,
30 BALANCE_LOAD_DATE pay_balance_validation.balance_load_date%type,
31 RUN_BALANCE_STATUS pay_balance_validation.run_balance_status%type,
32 DEL_BAL_BEFORE DATE,
33 PURGE_ONLY varchar2(1) );
34 type t_balance_validation_tab is table of t_balance_validation_rec index by binary_integer;
35 g_old_balance_validation_tab t_balance_validation_tab;
36
37 --
38 ----------------------------------------------------------------------
39 --
40 -- mark_run_balance_status
41 --
42 -- Description
43 -- This procedure sets the run_balance status
44 --
45 ----------------------------------------------------------------------
46 procedure mark_run_balance_status(p_defined_balace_id in number,
47 p_business_group_id in number,
48 p_status in varchar2,
49 p_from_status in varchar2)
50 is
51 l_bal_valid_id pay_balance_validation.balance_validation_id%type;
52 l_update boolean;
53 begin
54 --
55 /* Can only set a valid status if the old status was processing */
56 l_update := FALSE;
57 if (p_status = 'V') then
58 if (p_from_status = 'P') then
59 l_update := TRUE;
60 end if;
61 else
62 l_update := TRUE;
63 end if;
64 --
65 if (l_update = TRUE) then
66 begin
67 --
68 select balance_validation_id
69 into l_bal_valid_id
70 from pay_balance_validation
71 where defined_balance_id = p_defined_balace_id
72 and business_group_id = p_business_group_id;
73 --
74 update pay_balance_validation
75 set run_balance_status = p_status,
76 balance_load_date = g_start_date
77 where balance_validation_id = l_bal_valid_id;
78 --
79 exception
80 when no_data_found then
81 --
82 insert into pay_balance_validation
83 (balance_validation_id,
84 defined_balance_id,
85 business_group_id,
86 run_balance_status,
87 balance_load_date)
88 values ( pay_balance_validation_s.nextval,
89 p_defined_balace_id,
90 p_business_group_id,
91 p_status,
92 g_start_date);
93 --
94 end;
95 end if;
96 end mark_run_balance_status;
97 ----------------------------------------------------------------------
98 --
99 -- set_run_bal_status
100 --
101 -- Description
102 -- This procedure sets the run_balance status
103 --
104 ----------------------------------------------------------------------
105 procedure set_run_bal_status (p_pactid in number,
106 p_status in varchar2)
107 is
108 cursor get_all_bals (p_pact_id in number
109 ) is
110 select pdb.defined_balance_id,
111 ppa.business_group_id,
112 nvl(pay_core_utils.get_parameter('BAL_LVL',
113 ppa.legislative_parameters),
114 'BOTH') balance_level,
115 pbd.dimension_level
116 from pay_payroll_actions ppa,
117 per_business_groups pbg,
118 pay_defined_balances pdb,
119 pay_balance_dimensions pbd
120 where ppa.payroll_action_id = p_pact_id
121 and ppa.business_group_id = pbg.business_group_id
122 and ((pdb.business_group_id = pbg.business_group_id
123 and pdb.legislation_code is null)
124 or
125 (pdb.legislation_code = pbg.legislation_code
126 and pdb.business_group_id is null)
127 or
128 (pdb.legislation_code is null
129 and pdb.business_group_id is null)
130 )
131 and pdb.save_run_balance = 'Y'
132 and pbd.dimension_type = 'R'
133 and pdb.balance_dimension_id = pbd.balance_dimension_id
134 and ((pbd.dimension_level =
135 nvl(pay_core_utils.get_parameter('BAL_LVL',
136 ppa.legislative_parameters),
137 'BOTH'))
138 or
139 (nvl(pay_core_utils.get_parameter('BAL_LVL',
140 ppa.legislative_parameters),
141 'BOTH')
142 = 'BOTH')
143 );
144 --
145 l_run_bal_stat pay_balance_validation.run_balance_status%type;
146 l_bus_grp pay_payroll_actions.business_group_id%type;
147 l_request_id fnd_concurrent_requests.request_id%type;
148 l_old_balance_load_date pay_balance_validation.balance_load_date%type;
149 --
150 begin
151 g_purge_only := FALSE;
152 --
153 select business_group_id, request_id
154 into l_bus_grp, l_request_id
155 from pay_payroll_actions
156 where payroll_action_id = p_pactid;
157
158 -- bug 14301014 mark non run balances as invalid
159 update pay_balance_validation bv
160 set RUN_BALANCE_STATUS = 'I'
161 where RUN_BALANCE_STATUS <> 'I'
162 and bv.business_group_id = l_bus_grp
163 and not exists ( select 'run balance candidate' from pay_balance_dimensions bd,
164 pay_defined_balances db
165 where db.defined_balance_id = bv.defined_balance_id
166 and bd.balance_dimension_id = db.balance_dimension_id
167 and bd.dimension_type = 'R'
168 and bd.DIMENSION_LEVEL in ('ASG','GRP'));
169
170 select fcp.CONCURRENT_PROGRAM_NAME
171 into g_program_name
172 from FND_CONCURRENT_REQUESTS fcr,
173 FND_CONCURRENT_PROGRAMS fcp
174 where fcr.REQUEST_ID = l_request_id
175 and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID;
176 hr_utility.trace('get CONCURRENT_PROGRAM_NAME. g_program_name:'||g_program_name);
177
178 if (g_proc_mode <> 'SINGLE') then
179 -- bug 11874880 fetch pregenerate load date
180 if g_program_name = 'RB_ADJD' then
181 select defined_balance_id,
182 balance_load_date,
183 run_balance_status,
184 case when run_balance_status = 'V' and balance_load_date <= g_start_date then g_start_date else hr_api.g_eot end del_bal_before,
185 case when run_balance_status = 'V' and balance_load_date <= g_start_date then 'Y' else 'N' end purge_only
186 bulk collect into g_old_balance_validation_tab
187 from pay_balance_validation
188 where business_group_id = l_bus_grp;
189 if g_debug then
190 hr_utility.trace('set_run_bal_status store g_old_balance_validation_tab. '|| g_old_balance_validation_tab.count);
191 end if;
192 end if;
193
194 for dbrec in get_all_bals(p_pactid) loop
195 --
196 -- Only change the status if the legislation
197 -- rule allows the run balances to be created.
198 --
199 if (( dbrec.dimension_level = 'ASG'
200 and g_save_asg_run_bals = 'Y')
201 or
202 ( dbrec.dimension_level = 'GRP'
203 and g_save_run_bals = 'Y')
204 ) then
205 --
206 if (g_load_type <> 'DELTA') then
207 --
208 select nvl(pbv.run_balance_status, 'I'), nvl(pbv.balance_load_date,hr_api.g_eot)
209 into l_run_bal_stat, l_old_balance_load_date
210 from pay_defined_balances pdb,
211 pay_balance_validation pbv
212 where pdb.defined_balance_id = dbrec.defined_balance_id
213 and pbv.defined_balance_id (+) = pdb.defined_balance_id
214 and pbv.business_group_id (+) = dbrec.business_group_id;
215 --
216 if (g_proc_mode = 'INVALID' and l_run_bal_stat <> 'V') then
217 --
218 mark_run_balance_status(dbrec.defined_balance_id,
219 dbrec.business_group_id,
220 p_status,
221 l_run_bal_stat);
222 --
223 elsif (g_proc_mode = 'ALL') then
224 --
225 mark_run_balance_status(dbrec.defined_balance_id,
226 dbrec.business_group_id,
227 p_status,
228 l_run_bal_stat);
229 --
230 end if;
231 --
232 else
233 --
234 if (p_status = 'V') then
235 update pay_balance_validation
236 set balance_load_date = g_start_date
237 where dbrec.defined_balance_id = defined_balance_id
238 and dbrec.business_group_id = business_group_id;
239 end if;
240 --
241 end if;
242 end if;
243 --
244 end loop;
245 else
246 --
247 /* Single Balance load */
248 --
249 if (g_load_type <> 'DELTA') then
250 --
251 select nvl(pbv.run_balance_status, 'I'), nvl(pbv.balance_load_date,hr_api.g_eot)
252
253 into l_run_bal_stat, l_old_balance_load_date
254 from pay_defined_balances pdb,
255 pay_balance_validation pbv
256 where pdb.defined_balance_id = g_def_bal_id
257 and pbv.defined_balance_id (+) = pdb.defined_balance_id
258 and pbv.business_group_id (+) = l_bus_grp;
259
260 if g_program_name = 'RB_ADJD' and l_old_balance_load_date <= g_start_date and l_run_bal_stat = 'V' then
261 g_purge_only := TRUE;
262 update pay_payroll_actions set legislative_parameters = legislative_parameters || ' PURGE_ONLY=Y'
263 where payroll_action_id = p_pactid
264 and legislative_parameters not like '%PURGE_ONLY%';
265 if g_debug then
266 hr_utility.trace('set_run_bal_status g_purge_only. l_old_balance_load_date:'||
267 l_old_balance_load_date||
268 ' g_start_date:'||g_start_date);
269 end if;
270 else g_purge_only := FALSE;
271 end if;
272 --
273 mark_run_balance_status(g_def_bal_id,
274 l_bus_grp,
275 p_status,
276 l_run_bal_stat);
277 --
278 else
279 --
280 if (p_status = 'V') then
281 --
282 update pay_balance_validation
283 set balance_load_date = g_start_date
284 where defined_balance_id = g_def_bal_id
285 and business_group_id = l_bus_grp;
286 --
287 end if;
288 --
289 end if;
290 --
291 end if;
292 --
293 end set_run_bal_status;
294 --
295 procedure set_globals(p_pact_id in number)
296 is
297 begin
298 --
299 if (g_globals_set = FALSE) then
300 --
301 g_globals_set := TRUE;
302 --
303 /* Get parameters */
304 select
305 pay_core_utils.get_parameter('DEF_BAL_ID',
306 pa1.legislative_parameters),
307 nvl(pay_core_utils.get_parameter('BAL_LVL',
308 pa1.legislative_parameters),
309 'BOTH'),
310 nvl(pay_core_utils.get_parameter('PROC_MODE',
311 pa1.legislative_parameters),
312 'ALL'),
313 to_date(pay_core_utils.get_parameter('BAL_START_DATE',
314 pa1.legislative_parameters),
315 'YYYY/MM/DD'),
316 nvl(pay_core_utils.get_parameter('LOAD_TYPE',
317 pa1.legislative_parameters),
318 'ALL'),
319 pbg.legislation_code,
320 pbg.business_group_id,
321 'GEN_BAL_'||p_pact_id,
322 p_pact_id generate_pact
323 into
324 g_def_bal_id,
325 g_bal_lvl,
326 g_proc_mode,
327 g_start_date,
328 g_load_type,
329 g_leg_code,
330 g_bus_grp,
331 g_att_name,
332 g_generate_pact
333 from pay_payroll_actions pa1,
334 per_business_groups pbg
335 where pa1.payroll_action_id = p_pact_id
336 and pa1.business_group_id = pbg.business_group_id;
337 --
338 begin
339 select rule_mode
340 into g_save_run_bals
341 from pay_legislation_rules
342 where legislation_code = g_leg_code
343 and rule_type = 'SAVE_RUN_BAL';
344 exception
345 when no_data_found then
346 g_save_run_bals := 'N';
347 end;
348 --
349 begin
350 select rule_mode
351 into g_save_asg_run_bals
352 from pay_legislation_rules
353 where legislation_code = g_leg_code
354 and rule_type = 'SAVE_ASG_RUN_BAL';
355 exception
356 when no_data_found then
357 g_save_asg_run_bals := 'N';
358 end;
359 --
360 if (g_def_bal_id is not null) then
361 --
362 -- Override the balance level for a single balance load
363 --
364 select nvl(pbd.dimension_level, 'ASG')
365 into g_bal_lvl
366 from pay_defined_balances pdb,
367 pay_balance_dimensions pbd
368 where pdb.defined_balance_id = g_def_bal_id
369 and pdb.balance_dimension_id = pbd.balance_dimension_id;
370 --
371 g_proc_mode := 'SINGLE';
372 --
373 end if;
374 --
375 end if;
376 --
377 end set_globals;
378 --
379 /* Name : calculate_delta_asg_balances
380 Purpose :
381 Arguments :
382 Notes :
383 */
384
385 procedure calculate_delta_asg_balances( p_asg_id in number,
386 p_asg_act_id in number,
387 p_bg_id in number,
388 p_eff_date in date,
389 p_bal_load_date in date,
390 p_chunk_number in number
391 )
392 is
393 --
394 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type
395 index by binary_integer;
396 --
397 l_delete_bals boolean;
398 l_def_bal_list pay_balance_pkg.t_balance_value_tab;
399 l_def_bal_id t_def_bal_id;
400 l_delta varchar2(10);
401 l_rb_row_status varchar2(1);
402 --
403 begin
404 --
405 hr_utility.set_location
406 ('Entering: pay_run_balance_build.calculate_delta_asg_balnces', 10);
407 if (g_proc_mode = 'SINGLE') then
408 l_rb_row_status := 'U';
409 --
410
411 if g_def_bal_id is not null then
412 l_rb_row_status := pay_balance_pkg.run_balance_row_status(g_def_bal_id, p_asg_id, p_asg_act_id,
413 p_bg_id, p_bal_load_date,g_generate_pact,p_chunk_number);
414 else l_rb_row_status := 'U';
415 end if;
416
417 -- If we have been supplied with a start date then delete
418 -- any run balance prior to this date.
419 --
420 l_delete_bals := FALSE;
421 if (g_start_date is not null
422 and g_start_date > p_eff_date) then
423 l_delete_bals := TRUE;
424 end if;
425 --
426 if (l_delete_bals) then
427 --
428 if l_rb_row_status in ('I','U','O') then
429 delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
430 from pay_run_balances prb
431 where prb.defined_balance_id = g_def_bal_id
432 and prb.assignment_action_id = p_asg_act_id;
433 end if;
434 --
435 else
436 --
437 -- Altered to use balance attributes
438 --
439 if g_load_type = 'DELTA' then
440 l_delta := 'Y';
441 else
442 l_delta := 'N';
443 end if;
444 --
445 if ((p_eff_date < p_bal_load_date) and (l_rb_row_status in ('I','U','M'))) then
446 pay_balance_pkg.create_asg_balance(g_def_bal_id
447 ,p_asg_act_id
448 ,'FORCE'
449 ,g_att_name
450 ,p_eff_date
451 ,l_delta);
452 end if;
453 --
454 end if;
455 --
456 else
457 -- If we have been supplied with a start date then delete
458 -- any run balance prior to this date.
459 --
460 l_delete_bals := FALSE;
461 if (g_start_date is not null
462 and g_start_date > p_eff_date) then
463 l_delete_bals := TRUE;
464 end if;
465 --
466 if (l_delete_bals) then
467 null;
468 --
469 else
470 --
471 if g_load_type = 'DELTA' then
472 l_delta := 'Y';
473 else
474 l_delta := 'N';
475 end if;
476 --
477 pay_balance_pkg.create_all_asg_balances(p_asg_act_id
478 ,g_att_name
479 ,'FORCE'
480 ,p_eff_date
481 ,l_delta
482 );
483 --
484 end if;
485 end if;
486 --
487 hr_utility.set_location
488 ('Leaving: pay_run_balance_build.calculate_delta_asg_balances', 100);
489 end calculate_delta_asg_balances;
490 --
491 /* Name : calculate_full_asg_balances
492 Purpose :
493 Arguments :
494 Notes :
495 */
496
497 procedure calculate_full_asg_balances( p_asg_id in number,
498 p_asg_act_id in number,
499 p_bg_id in number,
500 p_eff_date in date,
501 p_chunk_number in number
502 )
503 is
504 l_delete_bals boolean;
505 l_rb_row_status varchar2(1);
506 begin
507 --
508 --
509 hr_utility.set_location(
510 'Enter:pay_run_balance_build.calculate_full_asg_balances',10);
511 l_rb_row_status := 'U';
512 --
513 if (g_proc_mode = 'SINGLE') then
514 if g_def_bal_id is not null then
515 l_rb_row_status := pay_balance_pkg.run_balance_row_status(g_def_bal_id, p_asg_id, p_asg_act_id,
516 p_bg_id, g_start_date, g_generate_pact, p_chunk_number);
517 else l_rb_row_status := 'U';
518 end if;
519 --
520 -- If we have been supplied with a start date then delete
521 -- any run balance prior to this date.
522 --
523 hr_utility.set_location(
524 'pay_run_balance_build.calculate_full_asg_balances',20);
525 --
526 l_delete_bals := FALSE;
527 if (g_start_date is not null
528 and g_start_date > p_eff_date) then
529 l_delete_bals := TRUE;
530 end if;
531 --
532 if (l_delete_bals) then
533 --
534 --
535 hr_utility.set_location(
536 'pay_run_balance_build.calculate_full_asg_balances',30);
537 -- bug 11874880 reduce number of deletes by checking runbal row status
538 if (l_rb_row_status in ('I','U','O')) then
539 delete /*+ INDEX(prb PAY_RUN_BALANCES_N2) */
540 from pay_run_balances prb
541 where prb.defined_balance_id = g_def_bal_id
542 and prb.assignment_action_id = p_asg_act_id;
543 end if;
544 --
545 end if;
546 if not(l_delete_bals) then
547 --
548 --
549 hr_utility.set_location(
550 'pay_run_balance_build.calculate_full_asg_balances',40);
551 --
552 if l_rb_row_status in ( 'I', 'U','M' ) then
553 pay_balance_pkg.create_asg_balance(g_def_bal_id
554 ,p_asg_act_id
555 ,'FORCE'
556 ,g_att_name);
557 end if;
558 --
559 end if;
560 --
561 else
562 --
563 hr_utility.set_location(
564 'pay_run_balance_build.calculate_full_asg_balances',50);
565 --
566 -- If we have been supplied with a start date then delete
567 -- any run balance prior to this date.
568 --
569 l_delete_bals := FALSE;
570 if (g_start_date is not null
571 and g_start_date > p_eff_date) then
572 l_delete_bals := TRUE;
573 end if;
574 --
575 if (l_delete_bals) then
576 --
577 hr_utility.set_location(
578 'pay_run_balance_build.calculate_full_asg_balances',60);
579 null;
580 --
581 else
582 --
583 hr_utility.set_location(
584 'pay_run_balance_build.calculate_full_asg_balances',70);
585 --
586 pay_balance_pkg.create_all_asg_balances(p_asg_act_id,
587 g_att_name,
588 'TRUSTED'
589 );
590 end if;
591 end if;
592 --
593 --
594 hr_utility.set_location(
595 'Leaving:pay_run_balance_build.calculate_full_asg_balances',80);
596 --
597 end calculate_full_asg_balances;
598 --
599 /* Name : process_asg_lvl_balances
600 Purpose :
601 Arguments :
602 Notes :
603 */
604
605 /* Bug 13773218, removed join with per_business_groups_perf table */
606 procedure process_asg_lvl_balances(p_asg_id in number,
607 p_bus_grp in number,
608 p_chunk_number number)
609 is
610 --
611 cursor getaa (p_assid in number, p_bal_load_date in date
612 ) is
613 select /*+ ORDERED USE_NL(ppa prt)
614 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
615 INDEX(prt PAY_RUN_TYPES_PK) */
616 paa.assignment_action_id,
617 ppa.effective_date,
618 nvl(prt.run_method, 'N') run_method,
619 ppa.business_group_id
620 from pay_assignment_actions paa,
621 pay_payroll_actions ppa,
622 pay_run_types_f prt
623 where paa.assignment_id = p_assid
624 and paa.assignment_action_id in
625 ( select assignment_action_id from pay_assignment_actions where assignment_id = p_assid
626 minus select source_action_id from pay_assignment_actions where assignment_id = p_assid)
627 and paa.payroll_action_id = ppa.payroll_action_id
628 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
629 and ppa.effective_date >= nvl(p_bal_load_date, ppa.effective_date)
630 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
631 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
632 and nvl(prt.effective_end_date, ppa.effective_date)
633 order by 2;
634 --
635 l_run_bal_status pay_balance_validation.run_balance_status%type;
636 l_bal_load_date pay_balance_validation.balance_load_date%type;
637
638 /*Bug 9315998*/
639 Type typ_getaa is table of getaa%rowtype index by binary_integer;
640 lt_tab_getaa typ_getaa;
641 l_rb_row_status varchar2(1);
642
643 begin
644 --
645 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',10);
646 --
647 l_run_bal_status := 'U';
648 --
649 if (g_proc_mode <> 'SINGLE') then
650 --
651 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',20);
652 --
653 if (g_proc_mode = 'INVALID') then
654 --
655 -- altered delete statement to use pay_balance_attributes rather than
656 -- pay_balance_validation to identify rows to be deleted
657 --
658 /*Bug 9315998 Begin */
659 If g_att_cached = FALSE then
660 select pba.defined_balance_id
661 bulk collect
662 INTO g_tab_def_bal
663 from pay_balance_attributes pba
664 , pay_bal_attribute_definitions bad
665 where pba.attribute_id = bad.attribute_id
666 and bad.attribute_name = g_att_name;
667
668 g_att_cached := TRUE;
669 end if;
670
671 forall a in g_tab_def_bal.first..g_tab_def_bal.last
672 delete
673 from pay_run_balances prb
674 where prb.assignment_id = p_asg_id
675 and prb.defined_balance_id = g_tab_def_bal(a);
676 --
677 else
678 --
679 -- bug 11874880 for adjust balance dates selectivly delete rbs
680 if (g_load_type <> 'DELTA') then
681
682 if ((g_program_name = 'RB_ADJD') and (g_old_balance_validation_tab.count > 0)
683 and (g_proc_mode <> 'SINGLE')) then --{
684 -- Oracle10 implementation restriction
685 -- forall old_bv in g_old_balance_validation_tab.first..g_old_balance_validation_tab.last
686 -- delete from pay_run_balances
687 -- where defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id
688 -- and effective_date < g_old_balance_validation_tab(old_bv).del_bal_before
689 -- and assignment_id = p_asg_id;
690 for old_bv in g_old_balance_validation_tab.first..g_old_balance_validation_tab.last loop
691 delete from pay_run_balances
692 where defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id
693 and effective_date < g_old_balance_validation_tab(old_bv).del_bal_before
694 and assignment_id = p_asg_id;
695 end loop;
696
697 if g_debug then
698 hr_utility.trace('process_asg_lvl_balances. Delete rbs by load date. p_asg_id:'||p_asg_id||
699 ' cnt:'||sql%rowcount);
700 end if;
701 else
702 -- We must be regenerating all balances
703 --
704 if not g_purge_only then --{ not purge only so rb delete not by load date
705 delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
706 from pay_run_balances prb
707 where prb.assignment_id = p_asg_id;
708 if g_debug then
709 hr_utility.trace('process_asg_lvl_balances. Delete rbs by asg . p_asg_id:'||p_asg_id||
710 ' cnt:'||sql%rowcount);
711 end if;
712 end if; --} not purge only
713 end if; --}
714 --
715 else
716 --
717 delete /*+ INDEX(prb PAY_RUN_BALANCES_N1) */
718 from pay_run_balances prb
719 where prb.assignment_id = p_asg_id
720 and exists (select ''
721 from pay_balance_validation pbv
722 where pbv.defined_balance_id = prb.defined_balance_id
723 and pbv.business_group_id = p_bus_grp
724 and prb.effective_date < greatest(nvl(pbv.balance_load_date,
725 to_date('0001/01/01 00:00:00',
726 'YYYY/MM/DD HH24:MI:SS')
727 ),
728 nvl(g_start_date,
729 to_date('0001/01/01 00:00:00',
730 'YYYY/MM/DD HH24:MI:SS')
731 )
732 )
733 );
734 --
735 end if;
736 --
737 end if;
738 else
739 --
740 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',20);
741 --
742 -- No need to delete the balance, but we do need to get the status
743 --
744 begin
745 --
746 select balance_load_date,
747 run_balance_status
748 into l_bal_load_date,
749 l_run_bal_status
750 from pay_balance_validation
751 where defined_balance_id = g_def_bal_id
752 and business_group_id = p_bus_grp;
753 --
754 exception
755 when no_data_found then
756 l_run_bal_status := 'I';
757 end;
758 end if;
759 --
760 -- bug 11874880 delete run balances prior to load date
761 delete from pay_run_balances where assignment_id = p_asg_id and
762 effective_date < l_bal_load_date
763 and defined_balance_id = g_def_bal_id;
764 if g_debug then
765 hr_utility.trace('pay_run_balance_build.process_asg_lvl_balance delete rb rows before load date:' ||SQL%ROWCOUNT);
766 end if;
767
768
769 if not g_purge_only then -- bug 11874880 adjust balance load date
770 /*Begin Bug 9315998*/
771 Open getaa(p_asg_id, l_bal_load_date);
772 fetch getaa bulk collect into lt_tab_getaa;
773 close getaa;
774
775
776
777 If lt_tab_getaa.count > 0 then
778
779 for aarec in lt_tab_getaa.first..lt_tab_getaa.last loop
780 --
781 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',30);
782 --
783 if (lt_tab_getaa(aarec).run_method <> 'C') then
784 --
785 --
786 hr_utility.set_location('pay_run_balance_build.process_asg_lvl_balance',40);
787 --
788 if (g_load_type <> 'DELTA') then
789 --
790 --
791 hr_utility.set_location(
792 'pay_run_balance_build.process_asg_lvl_balance',50);
793 if g_def_bal_id is not null then
794 l_rb_row_status := pay_balance_pkg.run_balance_row_status(g_def_bal_id, p_asg_id,
795 lt_tab_getaa(aarec).assignment_action_id,
796 lt_tab_getaa(aarec).business_group_id,
797 l_bal_load_date, g_generate_pact, p_chunk_number);
798 else l_rb_row_status := 'U';
799 end if;
800 --
801 if l_rb_row_status in ('I','U','M') then
802 calculate_full_asg_balances( p_asg_id,
803 lt_tab_getaa(aarec).assignment_action_id,
804 lt_tab_getaa(aarec).business_group_id,
805 lt_tab_getaa(aarec).effective_date,
806 p_chunk_number
807 );
808 end if;
809 --
810 else
811 --
812 --
813 hr_utility.set_location(
814 'pay_run_balance_build.process_asg_lvl_balance',60);
815 --
816 if (l_run_bal_status = 'V') then
817 --
818 --
819 hr_utility.set_location(
820 'pay_run_balance_build.process_asg_lvl_balance',70);
821 --
822 calculate_delta_asg_balances( p_asg_id,
823 lt_tab_getaa(aarec).assignment_action_id,
824 lt_tab_getaa(aarec).business_group_id,
825 lt_tab_getaa(aarec).effective_date,
826 l_bal_load_date,
827 p_chunk_number
828 );
829 end if;
830 --
831 end if;
832 --
833 end if;
834 end loop;
835 --
836 --
837 end if;
838
839 lt_tab_getaa.delete;
840
841 end if;
842 hr_utility.set_location(
843 'Leaving:pay_run_balance_build.process_asg_lvl_balance',80);
844 --
845 end process_asg_lvl_balances;
846 --
847 /* Name : calculate_delta_grp_balances
848 Purpose :
849 Arguments :
850 Notes :
851 */
852
853 procedure calculate_delta_grp_balances(p_pactid in number,
854 p_eff_date in date,
855 p_bus_grp in number,
856 p_leg_code in varchar2,
857 p_bal_load_date in date
858 )
859 is
860 --
861 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type
862 index by binary_integer;
863 --
864 l_delete_bals boolean;
865 l_def_bal_list pay_balance_pkg.t_balance_value_tab;
866 l_def_bal_id t_def_bal_id;
867 l_delta varchar2(10);
868 begin
869 --
870 hr_utility.set_location
871 ('Entering: pay_run_balance_build.calculate_delta_grp_balances', 10);
872 --
873 if (g_proc_mode = 'SINGLE') then
874 --
875 -- If we have been supplied with a start date then delete
876 -- any run balance prior to this date.
877 --
878 l_delete_bals := FALSE;
879 if (g_start_date is not null
880 and g_start_date > p_eff_date) then
881 l_delete_bals := TRUE;
882 end if;
883 --
884 if (l_delete_bals) then
885 --
886 delete from pay_run_balances
887 where defined_balance_id = g_def_bal_id
888 and payroll_action_id = p_pactid;
889 --
890 else
891 --
892 -- Altered to use balance_attributes
893 --
894 if g_load_type = 'DELTA' then
895 l_delta := 'Y';
896 else
897 l_delta := 'N';
898 end if;
899 --
900 if ((p_eff_date < p_bal_load_date) and (not g_purge_only)) then
901 pay_balance_pkg.create_group_balance
902 (g_def_bal_id
903 ,p_pactid
904 ,'FORCE'
905 ,g_att_name
906 ,p_eff_date
907 ,l_delta);
908 end if;
909 --
910 end if;
911 else
912 --
913 -- If we have been supplied with a start date then delete
914 -- any run balance prior to this date.
915 --
916 l_delete_bals := FALSE;
917 if (g_start_date is not null
918 and g_start_date > p_eff_date) then
919 l_delete_bals := TRUE;
920 end if;
921 --
922 if (l_delete_bals) then
923 --
924 if (g_proc_mode = 'INVALID') then
925 --
926 delete from pay_run_balances prb
927 where prb.payroll_action_id = p_pactid
928 and exists (select ''
929 from pay_balance_validation pbv
930 where pbv.defined_balance_id =
931 prb.defined_balance_id
932 and pbv.run_balance_status = 'P'
933 and pbv.business_group_id = p_bus_grp);
934 else
935 --
936 -- We must be regenerating all balances
937 --
938 if (g_load_type <> 'DELTA') and ( not g_purge_only ) then
939 --
940 delete from pay_run_balances
941 where payroll_action_id = p_pactid;
942 else
943 delete
944 from pay_run_balances prb
945 where prb.payroll_action_id = p_pactid
946 and g_start_date is not null
947 and g_start_date > p_eff_date;
948 --
949 end if;
950 end if;
951 --
952 else
953 --
954 -- Use call to create_all_grp_balances to take advantage of balance
955 -- attributes
956 --
957 if g_load_type = 'DELTA' then
958 l_delta := 'Y';
959 else
960 l_delta := 'N';
961 end if;
962 --
963 if not g_purge_only then
964 pay_balance_pkg.create_all_group_balances
965 (p_pact_id => p_pactid
966 ,p_bal_list => g_att_name
967 ,p_load_type => 'FORCE'
968 ,p_eff_date => p_eff_date
969 ,p_delta => l_delta
970 );
971 end if;
972 end if;
973 end if;
974 hr_utility.set_location
975 ('Leaving: pay_run_balance_build.calculate_delta_grp_balances', 100);
976 --
977 end calculate_delta_grp_balances;
978 --
979 /* Name : calculate_full_grp_balances
980 Purpose :
981 Arguments :
982 Notes :
983 */
984
985 procedure calculate_full_grp_balances( p_pactid in number,
986 p_eff_date in date,
987 p_bus_grp in number
988 )
989 is
990 l_delete_bals boolean;
991 begin
992 if g_debug then
993 hr_utility.set_location ('Entering: pay_run_balance_build.calculate_full_grp_balances', 10);
994 hr_utility.trace('p_eff_date:'||p_eff_date||' g_start_date:'||g_start_date);
995 end if;
996 if (g_proc_mode = 'SINGLE') then
997 --
998 -- If we have been supplied with a start date then delete
999 -- any run balance prior to this date.
1000 --
1001 l_delete_bals := FALSE;
1002 if (g_start_date is not null
1003 and g_start_date > p_eff_date) then
1004 l_delete_bals := TRUE;
1005 end if;
1006 --
1007 if (l_delete_bals) then
1008 --
1009 delete from pay_run_balances
1010 where defined_balance_id = g_def_bal_id
1011 and payroll_action_id = p_pactid;
1012 hr_utility.trace('calculate_full_grp_balances1. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
1013 --
1014 else
1015 --
1016 if not g_purge_only then
1017 pay_balance_pkg.create_group_balance
1018 (g_def_bal_id
1019 ,p_pactid
1020 ,'FORCE'
1021 ,g_att_name);
1022 end if;
1023 end if;
1024 else
1025 --
1026 -- If we have been supplied with a start date then delete
1027 -- any run balance prior to this date.
1028 --
1029 l_delete_bals := FALSE;
1030 if (g_start_date is not null
1031 and g_start_date > p_eff_date) then
1032 l_delete_bals := TRUE;
1033 end if;
1034 --
1035 if (l_delete_bals) then
1036 --
1037 if (g_proc_mode = 'INVALID') then
1038 --
1039 delete from pay_run_balances prb
1040 where prb.payroll_action_id = p_pactid
1041 and exists (select ''
1042 from pay_balance_validation pbv
1043 where pbv.defined_balance_id = prb.defined_balance_id
1044 and pbv.run_balance_status = 'P'
1045 and pbv.business_group_id = p_bus_grp);
1046 hr_utility.trace('calculate_full_grp_balances2. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
1047 else
1048 --
1049 -- We must be regenerating all balances
1050 --
1051 if ( g_purge_only and g_start_date > p_eff_date ) or (not g_purge_only) then
1052 delete from pay_run_balances
1053 where payroll_action_id = p_pactid;
1054 hr_utility.trace('calculate_full_grp_balances3. Delete by pact . p_pactid:'||p_pactid|| ' cnt:'||sql%rowcount);
1055 end if;
1056 end if;
1057 --
1058 else
1059 --
1060
1061 if not g_purge_only then
1062 pay_balance_pkg.create_all_group_balances(p_pactid,
1063 g_att_name,
1064 'TRUSTED'
1065 );
1066 end if;
1067 end if;
1068 end if;
1069 if g_debug then
1070 hr_utility.set_location ('Leaving: pay_run_balance_build.calculate_full_grp_balances', 90);
1071 end if;
1072 --
1073 end calculate_full_grp_balances;
1074 --
1075 /* Name : process_group_lvl_balances
1076 Purpose :
1077 Arguments :
1078 Notes :
1079 */
1080
1081 procedure process_group_lvl_balances( p_pactid in number)
1082 is
1083 l_grp_eff_date date;
1084 l_bus_grp pay_payroll_actions.business_group_id%type;
1085 l_run_bal_status pay_balance_validation.run_balance_status%type;
1086 l_bal_load_date pay_balance_validation.balance_load_date%type;
1087 l_leg_code per_business_groups.legislation_code%type;
1088 l_del_cnt number;
1089 begin
1090 --
1091 if g_debug then
1092 hr_utility.set_location ('Entering: pay_run_balance_build.process_group_lvl_balances', 10);
1093 end if;
1094 begin
1095 select ppa.effective_date,
1096 ppa.business_group_id,
1097 pbg.legislation_code
1098 into l_grp_eff_date,
1099 l_bus_grp,
1100 l_leg_code
1101 from pay_payroll_actions ppa,
1102 per_business_groups pbg
1103 where ppa.payroll_action_id = p_pactid
1104 and ppa.business_group_id = pbg.business_group_id;
1105 exception
1106 --
1107 -- Bug 4031667: If the payroll action no longer exists then continue without
1108 -- erroring and without attempting to process the payroll action.
1109 --
1110 when no_data_found then
1111 return;
1112 end;
1113 --
1114 l_run_bal_status := 'V';
1115 --
1116 if (g_proc_mode <> 'SINGLE') then
1117 --
1118 if (g_proc_mode = 'INVALID') then
1119 --
1120 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
1121 from pay_run_balances prb
1122 where prb.payroll_action_id = p_pactid
1123 and exists (select /*+ INDEX(pbv PAY_BALANCE_VALIDATION_UK1) */ ''
1124 from pay_balance_validation pbv
1125 where pbv.defined_balance_id = prb.defined_balance_id
1126 and pbv.run_balance_status = 'P'
1127 and pbv.business_group_id = l_bus_grp);
1128 if g_debug then
1129 hr_utility.trace('process_group_lvl_balances1. Delete rbs by rbstatus . p_pactid:'||p_pactid||
1130 ' cnt:'||sql%rowcount);
1131 end if;
1132 --
1133 else
1134 --
1135 -- We must be regenerating all balances
1136 --
1137 if (g_load_type <> 'DELTA') then
1138
1139 if ((g_program_name = 'RB_ADJD') and (g_old_balance_validation_tab.count > 0)
1140 and (g_proc_mode <> 'SINGLE')) then --{
1141 l_del_cnt := 0;
1142 for old_bv in g_old_balance_validation_tab.first..g_old_balance_validation_tab.last loop
1143 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */ from pay_run_balances prb
1144 where prb.defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id
1145 and prb.effective_date < g_old_balance_validation_tab(old_bv).del_bal_before
1146 and prb.payroll_action_id = p_pactid;
1147 l_del_cnt := l_del_cnt + sql%rowcount;
1148
1149 end loop;
1150
1151 if g_debug then
1152 hr_utility.trace('process_group_lvl_balances. Delete rbs by load date. p_pactid:'||p_pactid||
1153 ' cnt:'||l_del_cnt);
1154 end if;
1155 else
1156 --
1157 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
1158 from pay_run_balances prb
1159 where prb.payroll_action_id = p_pactid;
1160 if g_debug then
1161 hr_utility.trace('process_group_lvl_balances3. Delete rbs by pact . p_pactid:'||p_pactid||
1162 ' cnt:'||sql%rowcount);
1163 end if;
1164 end if;
1165 --
1166 else
1167 --
1168 delete /*+ INDEX(prb PAY_RUN_BALANCES_N4) */
1169 from pay_run_balances prb
1170 where prb.payroll_action_id = p_pactid
1171 and exists (select ''
1172 from pay_balance_validation pbv
1173 where pbv.defined_balance_id = prb.defined_balance_id
1174 and pbv.business_group_id = l_bus_grp
1175 and prb.effective_date < greatest(nvl(pbv.balance_load_date,
1176 to_date('0001/01/01 00:00:00',
1177 'YYYY/MM/DD HH24:MI:SS')
1178 ),
1179 nvl(g_start_date,
1180 to_date('0001/01/01 00:00:00',
1181 'YYYY/MM/DD HH24:MI:SS')
1182 )
1183 )
1184 );
1185 if g_debug then
1186 hr_utility.trace('process_group_lvl_balances4. Delete rbs date . p_pactid:'||p_pactid||
1187 ' cnt:'||sql%rowcount);
1188 end if;
1189 --
1190 end if;
1191 --
1192 end if;
1193 --
1194 else
1195 -- No need to delete the balance, but we do need to get the status
1196 --
1197 begin
1198 --
1199 select balance_load_date,
1200 run_balance_status
1201 into l_bal_load_date,
1202 l_run_bal_status
1203 from pay_balance_validation
1204 where defined_balance_id = g_def_bal_id
1205 and business_group_id = l_bus_grp;
1206 --
1207 exception
1208 when no_data_found then
1209 l_run_bal_status := 'I';
1210 end;
1211 --
1212 end if;
1213 --
1214 if (g_load_type <> 'DELTA') then
1215 --
1216 calculate_full_grp_balances( p_pactid,
1217 l_grp_eff_date,
1218 l_bus_grp
1219 );
1220 --
1221 else
1222 --
1223 if (l_run_bal_status = 'V') then
1224 --
1225 calculate_delta_grp_balances( p_pactid,
1226 l_grp_eff_date,
1227 l_bus_grp,
1228 l_leg_code,
1229 l_bal_load_date
1230 );
1231 end if;
1232 --
1233 end if;
1234 if g_debug then
1235 hr_utility.set_location ('Leaving: pay_run_balance_build.process_group_lvl_balances', 90);
1236 end if;
1237 --
1238 end process_group_lvl_balances;
1239 --
1240 /* Name : action_achive_data
1241 Purpose : This performs the US specific employee context setting for the
1242 Tax Remittance Archiver and for the payslip,check writer and
1243 Deposit Advice modules.
1244 Arguments :
1245 Notes :
1246 */
1247
1248 PROCEDURE action_archive_data( p_assactid in number
1249 ,p_effective_date in date)
1250 IS
1251 --
1252 cursor getaa (p_assactid in number
1253 ) is
1254 select paa.assignment_action_id,
1255 ppa.effective_date,
1256 nvl(prt.run_method, 'N') run_method,
1257 ppa.business_group_id
1258 from pay_assignment_actions paa,
1259 pay_payroll_actions ppa,
1260 pay_run_types_f prt,
1261 pay_assignment_actions paa_arch
1262 where paa_arch.assignment_action_id = p_assactid
1263 and paa_arch.assignment_id = paa.assignment_id
1264 and paa.payroll_action_id = ppa.payroll_action_id
1265 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1266 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
1267 and ppa.effective_date between nvl(prt.effective_start_date, ppa.effective_date)
1268 and nvl(prt.effective_end_date, ppa.effective_date)
1269 order by 2;
1270 --
1271 load_bals boolean;
1272 current_date date;
1273 l_payroll_action_id number;
1274 l_object_id number;
1275 l_delete_bals boolean;
1276 l_grp_eff_date date;
1277 l_bus_grp pay_payroll_actions.business_group_id%type;
1278 l_asg_id pay_assignment_actions.assignment_id%type;
1279 l_chunk_number pay_assignment_actions.chunk_number%type;
1280 --
1281 BEGIN
1282 hr_utility.set_location(
1283 'Entering:pay_run_balance_build.action_archive_data',10);
1284 --
1285 select paa.payroll_action_id,
1286 paa.object_id,
1287 paa.assignment_id,
1288 paa.chunk_number,
1289 ppa.business_group_id
1290 into l_payroll_action_id, l_object_id, l_asg_id, l_chunk_number, l_bus_grp
1291 from pay_assignment_actions paa,
1292 pay_payroll_actions ppa
1293 where paa.assignment_action_id = p_assactid
1294 and ppa.payroll_action_id = paa.payroll_action_id;
1295 --
1296 set_globals(l_payroll_action_id);
1297 --
1298 --
1299 hr_utility.set_location(
1300 'pay_run_balance_build.action_archive_data',20);
1301 --
1302 /* Have we timed out */
1303 select sysdate
1304 into current_date
1305 from sys.dual;
1306 --
1307 if (current_date > g_timeout) then
1308 /* Error, timed out process */
1309 hr_utility.set_message(801,'PAY_289014_PUR_TIMEOUT');
1310 hr_utility.raise_error;
1311 end if;
1312 --
1313 /* do we need to load assignment balances */
1314 load_bals := TRUE;
1315 --
1316 if (load_bals) then
1317 --
1318 --
1319 hr_utility.set_location(
1320 'pay_run_balance_build.action_archive_data',30);
1321 --
1322 if (g_bal_lvl = 'GRP') then
1323 --
1324 --
1325 hr_utility.set_location(
1326 'pay_run_balance_build.action_archive_data',40);
1327 --
1328 /* Only do something if the legislation rule is set */
1329 if (g_save_run_bals = 'Y') then
1330 --
1331 --
1332 hr_utility.set_location(
1333 'pay_run_balance_build.action_archive_data',50);
1334 --
1335 process_group_lvl_balances(l_object_id);
1336 --
1337 end if;
1338 else
1339 --
1340 --
1341 hr_utility.set_location(
1342 'pay_run_balance_build.action_archive_data',60);
1343 --
1344 /* Only do something if the legislation rule is set */
1345 if (g_save_asg_run_bals = 'Y') then
1346 --
1347 --
1348 hr_utility.set_location(
1349 'pay_run_balance_build.action_archive_data',70);
1350 --
1351 process_asg_lvl_balances(l_asg_id,
1352 l_bus_grp, l_chunk_number);
1353 --
1354 end if;
1355 end if;
1356 --
1357 --
1358 hr_utility.set_location(
1359 'pay_run_balance_build.action_archive_data',80);
1360 --
1361 end if;
1362 --
1363 --
1364 hr_utility.set_location(
1365 'Leaving:pay_run_balance_build.action_archive_data',90);
1366 --
1367 END action_archive_data;
1368
1369
1370 /* Name : generate_attribute
1371 Purpose : This generates the attribute to process in the generation.
1372 Arguments :
1373 Notes :
1374 */
1375
1376 PROCEDURE generate_attribute( p_payroll_action_id in number)
1377 is
1378 cursor get_grp (p_bus_grp_id in number,
1379 p_leg_code in varchar2,
1380 p_bal_list in varchar2,
1381 p_def_bal in number)
1382 is
1383 select
1384 pdb.defined_balance_id
1385 from
1386 pay_balance_types pbt,
1387 pay_defined_balances pdb,
1388 pay_balance_dimensions pbd
1389 where pbd.dimension_level = 'GRP'
1390 and pdb.save_run_balance = 'Y'
1391 and pbd.dimension_type = 'R'
1392 and pdb.balance_dimension_id = pbd.balance_dimension_id
1393 and pdb.balance_type_id = pbt.balance_type_id
1394 and ((pdb.business_group_id = p_bus_grp_id
1395 and pdb.legislation_code is null)
1396 or
1397 (pdb.legislation_code = p_leg_code
1398 and pdb.business_group_id is null)
1399 or
1400 (pdb.legislation_code is null
1401 and pdb.business_group_id is null)
1402 )
1403 and ( p_bal_list <> 'INVALID'
1404 or ( p_bal_list = 'INVALID'
1405 and exists (select ''
1406 from pay_balance_validation pbv
1407 where pbv.defined_balance_id = pdb.defined_balance_id
1408 and pbv.business_group_id = p_bus_grp_id
1409 and pbv.run_balance_status = 'P')
1410 )
1411 )
1412 and ( p_def_bal is null
1413 or p_def_bal = pdb.defined_balance_id
1414 );
1415 --
1416 cursor get_asg (p_bus_grp_id in number,
1417 p_leg_code in varchar2,
1418 p_bal_list in varchar2,
1419 p_def_bal in number)
1420 is
1421 select
1422 pdb.defined_balance_id
1423 from
1424 pay_balance_types pbt,
1425 pay_defined_balances pdb,
1426 pay_balance_dimensions pbd
1427 where pbd.dimension_level = 'ASG'
1428 and pdb.save_run_balance = 'Y'
1429 and pbd.dimension_type = 'R'
1430 and pdb.balance_dimension_id = pbd.balance_dimension_id
1431 and pdb.balance_type_id = pbt.balance_type_id
1432 and ( p_bal_list <> 'INVALID'
1433 or ( p_bal_list = 'INVALID'
1434 and exists (select ''
1435 from pay_balance_validation pbv
1436 where pbv.defined_balance_id = pdb.defined_balance_id
1437 and pbv.business_group_id = p_bus_grp_id
1438 and pbv.run_balance_status = 'P')
1439 )
1440 )
1441 and ( p_def_bal is null
1442 or p_def_bal = pdb.defined_balance_id
1443 )
1444 and ((pdb.business_group_id = p_bus_grp_id
1445 and pdb.legislation_code is null)
1446 or
1447 (pdb.legislation_code = p_leg_code
1448 and pdb.business_group_id is null)
1449 or
1450 (pdb.legislation_code is null
1451 and pdb.business_group_id is null)
1452 );
1453 --
1454 l_attribute_name pay_bal_attribute_definitions.attribute_name%type;
1455 l_attribute_id pay_bal_attribute_definitions.attribute_id%type;
1456 l_purge_only boolean;
1457 begin
1458 --
1459 -- Use for both DELTA and NON DELTA modes
1460 --
1461 if g_debug then
1462 hr_utility.set_location( 'Enter:runbal_bld.generate_attribute. g_bal_lvl:'||g_bal_lvl||
1463 ' g_proc_mode:'||g_proc_mode,10);
1464 end if;
1465 l_attribute_name := 'GEN_BAL_'||p_payroll_action_id;
1466 --
1467 select pay_bal_attribute_definition_s.nextval
1468 into l_attribute_id
1469 from dual;
1470 --
1471 insert into pay_bal_attribute_definitions
1472 (attribute_id,
1473 attribute_name,
1474 alterable,
1475 business_group_id)
1476 values (l_attribute_id,
1477 l_attribute_name,
1478 'N',
1479 g_bus_grp);
1480 --
1481 if ( g_bal_lvl = 'GRP'
1482 or g_bal_lvl = 'BOTH') then
1483 --
1484 for grprec in get_grp (g_bus_grp,
1485 g_leg_code,
1486 g_proc_mode,
1487 g_def_bal_id) loop
1488 -- bug 11874880 don't attribute in purge only mode
1489 l_purge_only := FALSE;
1490 if ((g_program_name = 'RB_ADJD') and (g_old_balance_validation_tab.count > 0)) then
1491 hr_utility.trace(' looping grprec g_old_balance_validation_tab.count:'||g_old_balance_validation_tab.count);
1492 for old_bv in g_old_balance_validation_tab.first..g_old_balance_validation_tab.last loop
1493 if grprec.defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id then
1494 if g_old_balance_validation_tab(old_bv).purge_only = 'Y' then
1495 if g_debug then
1496 hr_utility.trace('generate_attribute. purge only. defined_balance_id:'||grprec.defined_balance_id);
1497 end if;
1498 l_purge_only := TRUE;
1499 end if;
1500 exit;
1501 end if;
1502 end loop;
1503 end if;
1504 --
1505 if not l_purge_only then
1506 if g_debug then
1507 hr_utility.trace('generate_attribute. defined_balance_id:'||grprec.defined_balance_id||
1508 ' l_attribute_name:'||l_attribute_name);
1509 end if;
1510 insert into pay_balance_attributes
1511 (balance_attribute_id,
1512 attribute_id,
1513 defined_balance_id
1514 )
1515 values
1516 (pay_balance_attributes_s.nextval,
1517 l_attribute_id,
1518 grprec.defined_balance_id
1519 );
1520 end if;
1521 --
1522 end loop;
1523 --
1524 end if;
1525 --
1526 if ( g_bal_lvl = 'ASG'
1527 or g_bal_lvl = 'BOTH') then
1528 --
1529 for asgrec in get_asg (g_bus_grp,
1530 g_leg_code,
1531 g_proc_mode,
1532 g_def_bal_id) loop
1533 --
1534 -- bug 11874880 don't attribute in purge only mode
1535 l_purge_only := FALSE;
1536 if ((g_program_name = 'RB_ADJD') and (g_old_balance_validation_tab.count > 0)) then
1537 for old_bv in g_old_balance_validation_tab.first..g_old_balance_validation_tab.last loop
1538 if asgrec.defined_balance_id = g_old_balance_validation_tab(old_bv).defined_balance_id then
1539 if g_old_balance_validation_tab(old_bv).purge_only = 'Y' then
1540 if g_debug then
1541 hr_utility.trace('generate_attribute. purge only. defined_balance_id:'||asgrec.defined_balance_id);
1542 end if;
1543 l_purge_only := TRUE;
1544 end if;
1545 exit;
1546 end if;
1547 end loop;
1548 end if;
1549 if not l_purge_only then
1550 if g_debug then
1551 hr_utility.trace('generate_attribute. defined_balance_id:'||asgrec.defined_balance_id||
1552 ' l_attribute_name:'||l_attribute_name);
1553 end if;
1554 insert into pay_balance_attributes
1555 (balance_attribute_id,
1556 attribute_id,
1557 defined_balance_id
1558 )
1559 values
1560 (pay_balance_attributes_s.nextval,
1561 l_attribute_id,
1562 asgrec.defined_balance_id
1563 );
1564 end if;
1565 --
1566 end loop;
1567 --
1568 end if;
1569 --
1570 --
1571 end generate_attribute;
1572 --
1573 /* Name : action_range_cursor
1574 Purpose : This returns the select statement that is used to created the
1575 range rows for the Tax Filing (FLS) Archiver.
1576 Arguments :
1577 Notes :
1578 */
1579
1580 PROCEDURE action_range_cursor( p_payroll_action_id in number
1581 ,p_sqlstr out nocopy varchar2)
1582 IS
1583 --
1584 lv_sql_string VARCHAR2(32000);
1585 begin
1586 hr_utility.trace('In Range cursor before building string ');
1587 --
1588 set_globals(p_payroll_action_id);
1589 set_run_bal_status(p_payroll_action_id, 'P');
1590 --
1591 lv_sql_string :=
1592 'select 1
1593 from dual
1594 where 1 = 0
1595 and 1 = :payroll_action_id';
1596 --
1597 /* Only do something meaningful if the legislation rule is set */
1598 if (g_bal_lvl = 'GRP') then
1599 if (g_save_run_bals = 'Y') then
1600 lv_sql_string :=
1601 'select ppa_r.payroll_action_id
1602 from pay_payroll_actions ppa,
1603 pay_payroll_actions ppa_r
1604 where ppa.payroll_action_id = :payroll_action_id
1605 and ppa_r.action_type in (''R'',''Q'',''B'', ''I'',''V'')
1606 and ppa.business_group_id = ppa_r.business_group_id
1607 order by ppa_r.payroll_action_id';
1608 end if;
1609 else
1610 if (g_save_asg_run_bals = 'Y') then
1611 lv_sql_string :=
1612 'select distinct asg.person_id
1613 from
1614 per_periods_of_service pos,
1615 per_assignments_f asg,
1616 pay_payroll_actions ppa
1617 where ppa.payroll_action_id = :payroll_action_id
1618 and pos.person_id = asg.person_id
1619 and pos.period_of_service_id = asg.period_of_service_id
1620 and pos.business_group_id = ppa.business_group_id
1621 and asg.business_group_id = ppa.business_group_id
1622 order by asg.person_id';
1623 end if;
1624 end if;
1625 --
1626 p_sqlstr := lv_sql_string;
1627 hr_utility.trace('In Range cursor after building string ');
1628 --
1629 hr_utility.trace('Build Bal Attribute Group');
1630 --
1631 generate_attribute(p_payroll_action_id);
1632 --
1633 hr_utility.trace('Built Bal Attribute Group');
1634
1635 END action_range_cursor;
1636
1637
1638 /* Name : action_action_creation
1639 Purpose : This creates the assignment actions for a specific chunk
1640 of people to be archived by the year end pre-process.
1641 Arguments :
1642 Notes :
1643 */
1644
1645 PROCEDURE action_action_creation( p_payroll_action_id in number
1646 ,p_start_person_id in number
1647 ,p_end_person_id in number
1648 ,p_chunk in number)
1649 IS
1650 --
1651 cursor get_pact(stpactid in number,
1652 enpactid in number,
1653 pact_id in number
1654 ) is
1655 select ppa.payroll_action_id
1656 from pay_payroll_actions ppa,
1657 pay_payroll_actions ppa_arc
1658 where ppa.payroll_action_id between stpactid and enpactid
1659 and ppa_arc.payroll_action_id = pact_id
1660 and ppa.business_group_id = ppa_arc.business_group_id
1661 and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
1662 order by 1;
1663 --
1664 cursor get_asg( stperson in number
1665 ,endperson in number
1666 , pact_id in number
1667 ) is
1668 select distinct
1669 paf.assignment_id assignment_id,
1670 paf.person_id
1671 from
1672 per_periods_of_service pos,
1673 per_all_assignments_f paf,
1674 pay_payroll_actions ppa
1675 where pos.person_id between stperson and endperson
1676 and pos.person_id = paf.person_id
1677 and pos.period_of_service_id = paf.period_of_service_id
1678 and pos.business_group_id = ppa.business_group_id
1679 and ppa.payroll_action_id = pact_id
1680 and ppa.business_group_id = paf.business_group_id
1681 order by 1, 2;
1682
1683 cursor get_asg_range(c_chunk in number
1684 , pact_id in number
1685 ) is
1686 select distinct
1687 paf.assignment_id assignment_id,
1688 paf.person_id
1689 from
1690 per_periods_of_service pos,
1691 per_all_assignments_f paf,
1692 pay_payroll_actions ppa,
1693 pay_population_ranges ppr
1694 where ppr.chunk_number = c_chunk
1695 and ppr.payroll_action_id = ppa.payroll_action_id
1696 and pos.person_id = ppr.person_id
1697 and pos.person_id = paf.person_id
1698 and pos.period_of_service_id = paf.period_of_service_id
1699 and pos.business_group_id = ppa.business_group_id
1700 and ppa.payroll_action_id = p_payroll_action_id
1701 and ppa.business_group_id = paf.business_group_id
1702 order by 1, 2;
1703 --
1704 ln_lockingactid number;
1705 l_range_person BOOLEAN default FALSE; -- 8361030 Variable used to check if RANGE_PERSON_ID is enabled
1706
1707 begin
1708 --
1709 hr_utility.trace('In action Creation before getting payroll information');
1710 --
1711 set_globals(p_payroll_action_id);
1712 --
1713 if (g_bal_lvl = 'GRP') then
1714 for pactrec in get_pact( p_start_person_id,
1715 p_end_person_id,
1716 p_payroll_action_id
1717 ) loop
1718 --
1719 select pay_assignment_actions_s.nextval
1720 into ln_lockingactid
1721 from dual;
1722 --
1723 -- insert into pay_assignment_actions.
1724 hr_nonrun_asact.insact(ln_lockingactid,
1725 -1,
1726 p_payroll_action_id,
1727 p_chunk,
1728 null,
1729 null,
1730 'U',
1731 null,
1732 pactrec.payroll_action_id,
1733 'PPA');
1734 --
1735 end loop;
1736 else
1737 /* Bug 8361030 */
1738 l_range_person:=pay_ac_utility.range_person_on(
1739 p_report_type => 'BALANCE_BUILD'
1740 ,p_report_format => 'DEFAULT'
1741 ,p_report_qualifier => 'DEFAULT'
1742 ,p_report_category => 'PROCESS');
1743
1744
1745 if l_range_person then
1746 hr_utility.trace('In action Creation range_person_id = Y ');
1747 insert into pay_assignment_actions (
1748 assignment_action_id,
1749 assignment_id,
1750 payroll_action_id,
1751 action_status,
1752 chunk_number,
1753 action_sequence,
1754 object_version_number )
1755 select
1756 pay_assignment_actions_s.nextval assignment_action_id,
1757 asg.assignment_id assignment_id,
1758 p_payroll_action_id payroll_action_id,
1759 'U' action_status,
1760 p_chunk chunk_number,
1761 pay_assignment_actions_s.currval action_sequence,
1762 1 object_version_number
1763 from
1764 ( select distinct paf.assignment_id from
1765 per_periods_of_service pos,
1766 per_all_assignments_f paf,
1767 pay_payroll_actions ppa,
1768 pay_population_ranges ppr
1769 where ppr.chunk_number = p_chunk
1770 and ppr.payroll_action_id = ppa.payroll_action_id
1771 and pos.person_id = ppr.person_id
1772 and pos.person_id = paf.person_id
1773 and pos.period_of_service_id = paf.period_of_service_id
1774 and pos.business_group_id = ppa.business_group_id
1775 and ppa.payroll_action_id = p_payroll_action_id
1776 and ppa.business_group_id = paf.business_group_id
1777 order by 1 ) asg ;
1778 hr_utility.trace(' Action creation p_chunk:'||p_chunk||' SQL%ROWCOUNT:'||SQL%ROWCOUNT);
1779 else
1780 hr_utility.trace('In action Creation range_person_id = N ');
1781 insert into pay_assignment_actions (
1782 assignment_action_id,
1783 assignment_id,
1784 payroll_action_id,
1785 action_status,
1786 chunk_number,
1787 action_sequence,
1788 object_version_number )
1789 select
1790 pay_assignment_actions_s.nextval assignment_action_id,
1791 asg.assignment_id assignment_id,
1792 p_payroll_action_id payroll_action_id,
1793 'U' action_status,
1794 p_chunk chunk_number,
1795 pay_assignment_actions_s.currval action_sequence,
1796 1 object_version_number
1797 from
1798 ( select distinct paf.assignment_id from
1799 per_periods_of_service pos,
1800 per_all_assignments_f paf,
1801 pay_payroll_actions ppa,
1802 pay_population_ranges ppr
1803 where pos.person_id between p_start_person_id and p_end_person_id
1804 and ppr.payroll_action_id = ppa.payroll_action_id
1805 and pos.person_id = paf.person_id
1806 and pos.period_of_service_id = paf.period_of_service_id
1807 and pos.business_group_id = ppa.business_group_id
1808 and ppa.payroll_action_id = p_payroll_action_id
1809 and ppa.business_group_id = paf.business_group_id
1810 order by 1 ) asg ;
1811 hr_utility.trace(' Action creation p_start_person_id:'||p_start_person_id||
1812 ' p_end_person_id:'||p_end_person_id||' SQL%ROWCOUNT:'||SQL%ROWCOUNT);
1813 end if;
1814 end if;
1815 --
1816 end action_action_creation;
1817
1818 /* Name : action_archinit
1819 Purpose : This performs the context initialization.
1820 Arguments :
1821 Notes :
1822 */
1823
1824 procedure action_archinit(p_payroll_action_id in number) is
1825 --
1826 l_timeout_sec number;
1827 current_time date;
1828 l_run_bal_stat pay_balance_validation.run_balance_status%type;
1829 l_bus_grp pay_payroll_actions.business_group_id%type;
1830 l_request_id fnd_concurrent_requests.request_id%type;
1831 l_old_balance_load_date pay_balance_validation.balance_load_date%type;
1832 l_parm_purge_only varchar2(1);
1833 --
1834 begin
1835 --
1836 /* Get Action Parameters */
1837 declare
1838 begin
1839 --
1840 select to_number(parameter_value),
1841 sysdate
1842 into l_timeout_sec,
1843 current_time
1844 from pay_action_parameters
1845 where parameter_name = 'PROCESS_TIMEOUT';
1846 --
1847 --
1848 -- l_timeout_sec is in minutes, convert to seconds
1849 -- then convert to oracle time.
1850 --
1851 l_timeout_sec := l_timeout_sec * 60;
1852 l_timeout_sec := l_timeout_sec/86400;
1853 g_timeout := current_time + l_timeout_sec;
1854 --
1855 exception
1856 when no_data_found then
1857 l_timeout_sec := null;
1858 g_timeout := to_date('4712/12/31', 'YYYY/MM/DD');
1859 end;
1860
1861 --
1862 begin
1863 -- bug 16029858 initialize purge only globals
1864 --
1865
1866 if g_program_name is null then --{
1867
1868 g_purge_only := FALSE;
1869 set_globals(p_payroll_action_id);
1870
1871 select business_group_id, request_id,
1872 nvl(pay_core_utils.get_parameter('PURGE_ONLY', legislative_parameters), 'N') parm_purge_only
1873 into l_bus_grp, l_request_id, l_parm_purge_only
1874 from pay_payroll_actions
1875 where payroll_action_id = p_payroll_action_id;
1876
1877 select fcp.CONCURRENT_PROGRAM_NAME
1878 into g_program_name
1879 from FND_CONCURRENT_REQUESTS fcr,
1880 FND_CONCURRENT_PROGRAMS fcp
1881 where fcr.REQUEST_ID = l_request_id
1882 and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID;
1883 if g_debug then
1884 hr_utility.trace('ACTION_ARCHINIT get CONCURRENT_PROGRAM_NAME. g_program_name:'||g_program_name||' g_proc_mode:'||g_proc_mode);
1885 end if;
1886
1887 if (g_proc_mode <> 'SINGLE') then --{ single
1888 -- bug 11874880 fetch pregenerate load date
1889 if g_program_name = 'RB_ADJD' then --{ RB_ADJD
1890 select defined_balance_id,
1891 balance_load_date,
1892 run_balance_status,
1893 case when balance_status is null then g_start_date else hr_api.g_eot end del_bal_before,
1894 case when balance_status is null then 'Y' else 'N' end purge_only
1895 bulk collect into g_old_balance_validation_tab
1896 from ( select bv.defined_balance_id,
1897 bv.balance_load_date, run_balance_status, bv.business_group_id,
1898 (select 'P' from pay_bal_attribute_definitions bad,
1899 pay_balance_attributes ba
1900 where bad.attribute_name = g_att_name
1901 and bad.ATTRIBUTE_ID = ba.ATTRIBUTE_ID
1902 and ba.defined_balance_id = bv.defined_balance_id) balance_status
1903 from pay_balance_validation bv
1904 where bv.business_group_id = l_bus_grp) bv2;
1905 if g_debug then --{
1906 hr_utility.trace('set_run_bal_status store g_old_balance_validation_tab. '|| g_old_balance_validation_tab.count);
1907 end if; --}
1908 end if; --}
1909 ELSE
1910 if g_program_name = 'RB_ADJD' and l_parm_purge_only = 'Y' then --{ Single RB_ADJD Purge Only
1911 g_purge_only := TRUE;
1912 if g_debug then --{
1913 hr_utility.trace('set_run_bal_status store g_purge_only from legislation_parameters. ');
1914 end if; --}
1915 end if; --}
1916 end if; --}
1917 end if; --}
1918 end;
1919
1920 --
1921 end action_archinit;
1922 --
1923 /* Name : deinitialise
1924 Purpose : This procedure simply removes all the actions processed
1925 in this run
1926 Arguments :
1927 Notes :
1928 */
1929 procedure deinitialise (pactid in number)
1930 is
1931 --
1932 cursor getpa (p_pact_id in number) is
1933 select ppa.payroll_action_id ,
1934 ppa.action_sequence,
1935 ppa.effective_date,
1936 ppa.business_group_id
1937 from
1938 pay_payroll_actions ppa_arch,
1939 pay_payroll_actions ppa
1940 where ppa_arch.payroll_action_id = p_pact_id
1941 and ppa_arch.business_group_id = ppa.business_group_id
1942 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1943 order by 2;
1944 --
1945 remove_act varchar2(10);
1946 act_com number;
1947 load_bals boolean;
1948 cnt number;
1949 l_delete_bals boolean;
1950 begin
1951 --
1952 set_globals(pactid);
1953 --
1954 hr_utility.set_location('pay_run_balance_build.deinitialise', 10);
1955 /* only do something if the legislation rule is set */
1956 if (g_save_run_bals = 'Y') then
1957 /* do we need to load assignment balances */
1958 hr_utility.set_location('pay_run_balance_build.deinitialise', 20);
1959 load_bals := TRUE;
1960 if (g_proc_mode = 'SINGLE') then
1961 load_bals := FALSE;
1962 elsif (g_bal_lvl = 'GRP') then
1963 load_bals := FALSE;
1964 end if;
1965 --
1966 cnt := 0;
1967 if (load_bals) then
1968 --
1969 hr_utility.set_location('pay_run_balance_build.deinitialise', 30);
1970 --
1971 if (g_bal_lvl = 'BOTH') then
1972 hr_utility.set_location('pay_run_balance_build.deinitialise', 40);
1973 for parec in getpa(pactid) loop
1974 hr_utility.set_location('pay_run_balance_build.deinitialise', 50);
1975 --
1976 process_group_lvl_balances(parec.payroll_action_id);
1977 --
1978 end loop;
1979 end if;
1980 --
1981 end if;
1982 end if;
1983 --
1984 hr_utility.set_location('pay_run_balance_build.deinitialise', 60);
1985 --
1986 /* Remove the actions if needed */
1987 select count(*)
1988 into act_com
1989 from pay_assignment_actions
1990 where payroll_action_id = pactid
1991 and action_status <> 'C';
1992 --
1993 if act_com = 0 then
1994 --
1995 /* Set the Balance Status */
1996 --
1997 set_run_bal_status(pactid, 'V');
1998 --
1999 select pay_core_utils.get_parameter('REMOVE_ACT',
2000 pa1.legislative_parameters)
2001 into remove_act
2002 from pay_payroll_actions pa1
2003 where pa1.payroll_action_id = pactid;
2004 --
2005 if (remove_act is null or remove_act = 'Y') then
2006 pay_archive.remove_report_actions(pactid);
2007 --
2008 --
2009 delete from pay_balance_attributes
2010 where attribute_id in (select attribute_id
2011 from pay_bal_attribute_definitions
2012 where attribute_name = g_att_name
2013 );
2014 delete from pay_bal_attribute_definitions
2015 where attribute_name = g_att_name;
2016 end if;
2017 end if;
2018 --
2019 --hr_utility.trace_off;
2020 end deinitialise;
2021 --
2022 begin
2023 g_globals_set := FALSE;
2024 g_att_cached := FALSE; /*Bug 9315998*/
2025 end pay_run_balance_build;