1 package body py_rollback_pkg AS
2 /* $Header: pyrolbak.pkb 120.38.12020000.4 2013/03/26 07:03:41 apudiped ship $ */
3 /*------------- Payroll and Assignment Action Details ----------------*/
4 type rollback_rec is record
5 (
6 -- Payroll Action Level Details.
7 pact_id pay_payroll_actions.payroll_action_id%type,
8 action_name hr_lookups.meaning%type,
9 action_type pay_payroll_actions.action_type%type,
10 action_status pay_payroll_actions.action_status%type,
11 sequenced_flag boolean,
12 action_date date,
13 action_start_date date,
14 current_date date, -- holds sysdate.
15 payroll_name pay_all_payrolls_f.payroll_name%type,
16 bg_name hr_organization_units.name%type,
17 bg_id hr_organization_units.business_group_id%type,
18 independent_periods_flag pay_legislation_rules.rule_mode%type,
19 date_earned date,
20 purge_phase pay_payroll_actions.purge_phase%type,
21 object_type pay_assignment_actions.object_type%type,
22 retro_definition_id pay_payroll_actions.retro_definition_id%type,
23 batch_id pay_payroll_actions.batch_id%type,
24 --
25 -- Assignment Action Level Details.
26 assact_id pay_assignment_actions.assignment_action_id%type,
27 assignment_id per_all_assignments_f.assignment_id%type,
28 full_name per_all_people_f.full_name%type,
29 assignment_number per_all_assignments_f.assignment_number%type,
30 payroll_id pay_all_payrolls_f.payroll_id%type,
31 --
32 -- Other information.
33 legislation_code varchar2(2),
34 rollback_mode varchar2(20), -- 'ROLLBACK', 'RETRY', 'BACKPAY'.
35 rollback_level varchar2(1), -- 'A' (assact) or 'P' (pact).
36 leave_row boolean, -- i.e. leave_base_table_row.
37 all_or_nothing boolean,
38 multi_thread boolean,
39 grp_multi_thread boolean,
40 dml_mode varchar2(10), -- 'NONE', 'PARTIAL, 'FULL'.
41 max_errors_allowed number,
42 max_single_undo number,
43 limit_dml boolean,
44 retro_purge pay_action_parameters.parameter_value%type,
45 single_bal_table pay_action_parameters.parameter_value%type,
46 set_date_earned pay_action_parameters.parameter_value%type,
47 sub_ledger_acc pay_action_parameters.parameter_value%type
48 );
49 --
50 -- Data structure to hold information on next range.
51 type range_rec is record
52 (
53 chunk_number number,
54 starting_person number,
55 ending_person number
56 );
57 --
58 g_error_count number;
59 g_debug boolean := hr_utility.debug_enabled;
60 --
61 -- cache variables
62 mtgl_mode pay_action_parameters.parameter_value%type;
63 mtgl_mode_cached boolean := false;
64 --
65 -- The End of Time.
66 c_eot constant date := to_date('31/12/4712', 'DD/MM/YYYY');
67 --
68 --
69 /*
70 * Get the value of the specified legislation_rule.
71 * If no value is set on the database, it obtains
72 * the relevant default value.
73 */
74 function legislation_rule(p_leg_code in varchar2,
75 p_rule_name in varchar2)
76 return varchar2 is
77 l_rule_value pay_legislation_rules.rule_mode%type;
78 begin
79 --
80 begin
81 --
82 select rule_mode
83 into l_rule_value
84 from pay_legislation_rules
85 where legislation_code = p_leg_code
86 and rule_type = p_rule_name;
87 --
88 exception
89 when no_data_found then
90 --
91 /*Bug 10212578 */
92 if (p_rule_name = 'RETRO_DELETE') then
93 if substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2' then
94 l_rule_value := 'N';
95 else
96 l_rule_value := 'Y';
97 end if;
98 else
99 l_rule_value := null;
100 end if;
101 end;
102 --
103 return l_rule_value;
104 --
105 end legislation_rule;
106 --
107 /*
108 * Get the value of the specified action parameter.
109 * If no value is set on the database, it obtains
110 * the relevant default value.
111 */
112 function action_parameter(p_param_name in varchar2)
113 return varchar2 is
114 l_name pay_action_parameters.parameter_name%type;
115 param_value pay_action_parameters.parameter_value%type;
116 c_indent constant varchar2(40) := 'py_rollback_pkg.action_parameter';
117 l_found boolean;
118 begin
119 -- Attempt to find value of the parameter
120 -- in the action parameter table.
121 pay_core_utils.get_action_parameter(p_param_name, param_value, l_found );
122
123 if l_found=FALSE then
124 if(replace(p_param_name,' ','_') = 'CHUNK_SIZE') then
125 param_value := 20;
126 elsif(replace(p_param_name,' ','_') = 'MAX_SINGLE_UNDO') then
127 param_value := 50;
128 elsif(replace(p_param_name,' ','_') = 'MAX_ERRORS_ALLOWED') then
129 -- If we can't get the max errors allowed, we
130 -- default to chunk_size - make recursive call
131 -- to get this value.
132 param_value := action_parameter('CHUNK_SIZE');
133 elsif(replace(p_param_name,' ','_') = 'SET_DATE_EARNED') then
134 param_value := 'N';
135 end if;
136 end if;
137 --
138 return(param_value);
139 --
140 end action_parameter;
141 --
142 procedure remove_pact_payment(p_pactid in number)
143 is
144 cursor get_payment(p_pact in number)
145 is
146 select pre_payment_id
147 from pay_pre_payments
148 where payroll_action_id = p_pact;
149 begin
150 --
151 for payrec in get_payment(p_pactid) loop
152 --
153 update pay_contributing_payments
154 set pre_payment_id = null
155 where pre_payment_id = payrec.pre_payment_id;
156 delete from pay_pre_payments
157 where pre_payment_id = payrec.pre_payment_id;
158 --
159 end loop;
160 --
161 end remove_pact_payment;
162 --
163 procedure remove_action_information(
164 p_action_context_id in number,
165 p_action_context_type in varchar2 default 'AAP')
166 is
167 c_indent constant varchar2(100) := 'py_rollback_pkg.remove_action_information';
168 --
169 cursor actionitems (cp_action_context_id in number,
170 cp_action_context_type in varchar2) is
171 select action_information_id
172 from pay_action_information
173 where action_context_id = cp_action_context_id
174 and action_context_type = cp_action_context_type;
175 --
176 begin
177 for actionrec in actionitems(p_action_context_id,
178 p_action_context_type) loop
179
180 /* delete Action Information */
181 delete from pay_action_information
182 where action_information_id = actionrec.action_information_id;
183
184 end loop;
185 end remove_action_information;
186
187
188 procedure remove_archive_items(p_info in rollback_rec,
189 p_source_id in number,
190 p_archive_type in varchar2 default 'AAP')
191 is
192 c_indent constant varchar2(40) := 'py_rollback_pkg.remove_archive_items';
193 --
194 cursor architems (p_source number, p_archive_type varchar2) is
195 select archive_item_id
196 from ff_archive_items
197 where context1 = p_source
198 and nvl(archive_type, 'AAP') = p_archive_type;
199 --
200 begin
201 --
202 for arcrec in architems(p_source_id, p_archive_type) loop
203 --
204 -- delete archive item contexts.
205 delete from ff_archive_item_contexts
206 where archive_item_id = arcrec.archive_item_id;
207 --
208 -- delete the archive items.
209 delete from ff_archive_items
210 where archive_item_id = arcrec.archive_item_id;
211 end loop;
212 end;
213 --
214 procedure remove_file_details(p_info in rollback_rec,
215 p_source_id in number,
216 p_source_type in varchar2 default 'PAA')
217 is
218 c_indent constant varchar2(40) := 'py_rollback_pkg.remove_file_details';
219 --
220 begin
221 --
222 delete from pay_file_details
223 where source_id = p_source_id
224 and source_type = p_source_type;
225 --
226 end;
227 --
228 /*
229 * Procedure to remove all retropay elements and element
230 * values for the specified assignment action.
231 */
232 procedure remove_retro_ee(p_assact_id in number) is
233 c_indent varchar2(40);
234 cursor ceev is
235 select pee.element_entry_id
236 from pay_element_entries_f pee,
237 pay_assignment_actions paa
238 where paa.assignment_action_id = p_assact_id
239 and pee.assignment_id = paa.assignment_id
240 and pee.creator_id = paa.assignment_action_id
241 and pee.creator_type = 'P';
242 begin
243 if g_debug then
244 c_indent := 'py_rollback_pkg.remove_retro_ee';
245 hr_utility.set_location(c_indent,10);
246 end if;
247 --
248 /* Delete the entry values before the element entry */
249 for ceevrec in ceev loop
250 delete from pay_element_entry_values_f pev
251 where element_entry_id = ceevrec.element_entry_id;
252 --
253 delete from pay_element_entries_f
254 where creator_id = p_assact_id
255 and creator_type = 'P'
256 and element_entry_id = ceevrec.element_entry_id;
257 end loop;
258 --
259 if g_debug then
260 hr_utility.set_location(c_indent,20);
261 end if;
262 end remove_retro_ee;
263 --
264 /*
265 * Procedure to remove all Advance pay elements and element
266 * values for the specified assignment action.
267 */
268 procedure remove_adv_ee(p_assact_id in number) is
269 c_indent varchar2(40);
270 cursor aeev is
271 select pee.element_entry_id
272 from pay_element_entries_f pee,
273 pay_assignment_actions paa
274 where paa.assignment_action_id = p_assact_id
275 and pee.assignment_id = paa.assignment_id
276 and pee.creator_id = paa.assignment_action_id
277 and pee.creator_type = 'D';
278 begin
279 if g_debug then
280 c_indent := 'py_rollback_pkg.remove_adv_ee';
281 hr_utility.set_location(c_indent,10);
282 end if;
283 --
284 /* Delete the entry values before the element entry */
285 for aeevrec in aeev loop
286 delete from pay_element_entry_values_f pev
287 where element_entry_id = aeevrec.element_entry_id;
288 --
289 delete from pay_element_entries_f
290 where creator_id = p_assact_id
291 and creator_type = 'D'
292 and element_entry_id = aeevrec.element_entry_id;
293 end loop;
294 --
295 if g_debug then
296 hr_utility.set_location(c_indent,20);
297 end if;
298 end remove_adv_ee;
299 --
300 -- Procedure to remove advance pay by element entries.
301 --
302 procedure remove_advpayele_ee(p_assact_id in number) is
303 c_indent varchar2(40);
304 cursor aeev is
305 select pee.element_entry_id
306 from pay_element_entries_f pee,
307 pay_assignment_actions paa
308 where paa.assignment_action_id = p_assact_id
309 and pee.assignment_id = paa.assignment_id
310 and pee.creator_id = paa.assignment_action_id
311 and pee.creator_type in ('AD', 'AE','D');
312 begin
313 if g_debug then
314 c_indent := 'py_rollback_pkg.remove_advpayele_ee';
315 hr_utility.set_location(c_indent,10);
316 end if;
317 --
318 /* Delete the entry values before the element entry */
319 for aeevrec in aeev loop
320 delete from pay_element_entry_values_f pev
321 where element_entry_id = aeevrec.element_entry_id;
322 --
323 delete from pay_element_entries_f
324 where creator_id = p_assact_id
325 and creator_type in ('AD', 'AE','D')
326 and element_entry_id = aeevrec.element_entry_id;
327 end loop;
328 --
329 if g_debug then
330 hr_utility.set_location(c_indent,20);
331 end if;
332 end remove_advpayele_ee;
333 --
334 -- Procedure to remove retropay by action entries.
335 procedure remove_retroact_ee(p_assact_id in number) is
336 c_indent varchar2(40);
337 cursor raeev is
338 select pee.element_entry_id
339 from pay_element_entries_f pee,
340 pay_assignment_actions paa
341 where paa.assignment_action_id = p_assact_id
342 and pee.assignment_id = paa.assignment_id
343 and pee.creator_id = paa.assignment_action_id
344 and pee.creator_type = 'R';
345 begin
346 if g_debug then
347 c_indent := 'py_rollback_pkg.remove_retroact_ee';
348 hr_utility.set_location(c_indent,10);
349 end if;
350 --
351 /* Delete the entry values before the element entry */
352 for raeevrec in raeev loop
353 delete from pay_element_entry_values_f pev
354 where element_entry_id = raeevrec.element_entry_id;
355 --
356 delete from pay_element_entries_f
357 where creator_id = p_assact_id
358 and creator_type = 'R'
359 and element_entry_id = raeevrec.element_entry_id;
360 end loop;
361 --
362 if g_debug then
363 hr_utility.set_location(c_indent,20);
364 end if;
365 end remove_retroact_ee;
366 --
367 -- Procedure to remove retropay by element_entries.
368 procedure remove_retroele_ee(p_assact_id in number,
369 p_rollback_mode in varchar2) is
370 c_indent varchar2(40);
371 cursor remove_eev is
372 select pee.element_entry_id
373 from pay_element_entries_f pee,
374 pay_assignment_actions paa
375 where paa.assignment_action_id = p_assact_id
376 and pee.creator_id = paa.assignment_action_id
377 and pee.creator_type in ('RR', 'EE', 'NR', 'PR') ;
378 --
379 l_reprocess_date date;
380 --
381 cursor min_reprocess_date is
382 select min(pre.reprocess_date)
383 from pay_retro_entries pre,
384 pay_retro_assignments pra
385 where pre.retro_assignment_id = pra.retro_assignment_id
386 and pra.retro_assignment_action_id = p_assact_id;
387 --
388 begin
389 if g_debug then
390 c_indent := 'py_rollback_pkg.remove_retroele_ee';
391 hr_utility.set_location(c_indent,10);
392 end if;
393 --
394 /* Delete the entry values before the element entry */
395 for eev_rec in remove_eev loop
396 delete from pay_element_entry_values_f pev
397 where element_entry_id = eev_rec.element_entry_id;
398 --
399 delete from pay_entry_process_details
400 where element_entry_id = eev_rec.element_entry_id;
401 --
402 delete from pay_element_entries_f
403 where creator_id = p_assact_id
404 and creator_type in ('RR', 'EE', 'NR', 'PR')
405 and element_entry_id = eev_rec.element_entry_id;
406 end loop;
407 --
408 hr_utility.set_location(c_indent,20);
409 -- Finally reset the pay_retro_assignment_table if needed
410 if (p_rollback_mode <> 'RETRY') then
411 --
412 pay_retro_pkg.merge_retro_assignments(p_assact_id);
413 --
414 hr_utility.set_location(c_indent,30);
415 -- Remove or reset row on pay_recorded_requests
416 --
417 hr_utility.set_location(c_indent,40);
418 pay_retro_pkg.reset_recorded_request(p_assact_id);
419 hr_utility.set_location(c_indent,50);
420 --
421 -- Remove the asg act id, and in case merge has new earlier entries
422 -- also change the reprocess_date
423 open min_reprocess_date;
424 fetch min_reprocess_date into l_reprocess_date;
425 close min_reprocess_date;
426 --
427 update pay_retro_assignments ra
428 set ra.retro_assignment_action_id = null,
429 ra.reprocess_date = nvl(l_reprocess_date,ra.reprocess_date)
430 where ra.retro_assignment_action_id = p_assact_id;
431
432 end if;
433 --
434 if g_debug then
435 hr_utility.set_location(c_indent,20);
436 end if;
437 --
438 end remove_retroele_ee;
439 --
440 /*
441 * Procedure to delete entries from gl_interface tables.
442 */
443 procedure remove_gl_entries(p_info in rollback_rec) is
444 c_indent varchar2(40);
445 l_source_name gl_interface.user_je_source_name%type;
446 --
447 begin
448 if g_debug then
449 c_indent := 'py_rollback_pkg.remove_gl_entries';
450 hr_utility.set_location(c_indent, 10);
451 end if;
452 --
453 if mtgl_mode_cached = FALSE then
454 if g_debug then
455 hr_utility.set_location(c_indent, 20);
456 end if;
457 begin
458 select upper(parameter_value)
459 into mtgl_mode
460 from pay_action_parameters
461 where parameter_name = 'TRANSGL_THREAD';
462 exception
463 when others then
464 mtgl_mode := 'Y';
465 end;
466 --
467 if g_debug then
468 hr_utility.set_location(c_indent, 30);
469 end if;
470 -- Remove transfer table rows once only
471 -- first of all get source_name
472 select user_je_source_name
473 into l_source_name
474 from gl_je_sources
475 where je_source_name = 'Payroll';
476 --
477 if g_debug then
478 hr_utility.set_location(c_indent, 35);
479 end if;
480 delete from gl_interface gl
481 where gl.reference21 = to_char(p_info.pact_id)
482 and gl.user_je_source_name = l_source_name;
483 --
484 mtgl_mode_cached := TRUE;
485 end if;
486 --
487 if mtgl_mode <> 'N' then
488 -- Remove intermediate transfer table rows
489 -- for multi-threaded transfer to General Ledger only
490 if g_debug then
491 hr_utility.set_location(c_indent, 40);
492 end if;
493 delete from pay_gl_interface pgl
494 where pgl.assignment_action_id = p_info.assact_id;
495 end if;
496 --
497 end remove_gl_entries;
498 --
499 /*
500 * Procedure to reset the prenote date for magnetic tape
501 * rollback.
502 */
503 procedure reset_prenote(p_assact_id in number) is
504 cursor get_accnts_to_reset(asgact in number) is
505 select pea.external_account_id
506 from pay_external_accounts pea,
507 pay_payment_types ppt,
508 pay_personal_payment_methods_f ppm,
509 pay_org_payment_methods_f opm,
510 pay_pre_payments ppp,
511 pay_payroll_actions ppa,
512 pay_assignment_actions paa
513 where paa.assignment_action_id = asgact
514 and paa.payroll_action_id = ppa.payroll_action_id
515 and paa.pre_payment_id = ppp.pre_payment_id
516 and ppp.org_payment_method_id = opm.org_payment_method_id
517 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
518 and opm.payment_type_id = ppt.payment_type_id
519 and ppt.pre_validation_required = 'Y'
520 and ppt.validation_value = ppp.value
521 and ppm.external_account_id = pea.external_account_id
522 and ppa.effective_date between ppm.effective_start_date
523 and ppm.effective_end_date
524 and ppa.effective_date between opm.effective_start_date
525 and opm.effective_end_date;
526 begin
527 for eacrec in get_accnts_to_reset(p_assact_id) loop
528 update pay_external_accounts
529 set prenote_date = null
530 where external_account_id = eacrec.external_account_id
531 and prenote_date is not null;
532 end loop;
533 end reset_prenote;
534 --
535 /*
536 * Procedure to remove all run results and run result
537 * values for the specified assignment action.
538 */
539 procedure remove_run_results(p_info in rollback_rec) is
540 c_indent varchar2(40);
541 purge_rr boolean;
542 cursor crrv is
543 select prr.run_result_id
544 from pay_run_results prr
545 where prr.assignment_action_id = p_info.assact_id;
546 begin
547 -- Delete any run results and values created by
548 -- this action. There is no cascade trigger on
549 -- run results, so we are forced to do both.
550 -- We use a cursor loop here to avoid a full table
551 -- scan that occurs when you attempt to use a single
552 -- delete statement.
553 -- Tight loop, so the set_location call set outside it.
554 if g_debug then
555 c_indent := 'py_rollback_pkg.remove_run_results';
556 hr_utility.set_location(c_indent,10);
557 end if;
558 --
559 purge_rr := TRUE;
560 if (p_info.rollback_mode = 'BACKPAY'
561 and p_info.retro_purge = 'N') then
562 --
563 purge_rr := FALSE;
564 --
565 end if;
566 --
567 if (purge_rr = TRUE) then
568 for crrvrec in crrv loop
569 delete from pay_run_result_values rrv
570 where rrv.run_result_id = crrvrec.run_result_id;
571 end loop;
572 --
573 if g_debug then
574 hr_utility.set_location(c_indent,30);
575 end if;
576 delete from pay_run_results RR
577 where RR.assignment_action_id = p_info.assact_id;
578 --
579 else
580 --
581 if g_debug then
582 hr_utility.set_location(c_indent,40);
583 end if;
584 -- It must be a backpay.
585 update pay_run_results RR
586 set RR.status = 'B'
587 where RR.assignment_action_id = p_info.assact_id;
588 end if;
589 end remove_run_results;
590 --
591 /*
592 * Procedure to remove all action contexts.
593 */
594 procedure remove_action_contexts(p_assact_id in number) is
595 c_indent varchar2(40);
596 begin
597 if g_debug then
598 c_indent := 'py_rollback_pkg.remove_action_contexts';
599 hr_utility.set_location(c_indent,10);
600 end if;
601 --
602 delete from pay_action_contexts
603 where assignment_action_id = p_assact_id;
604 --
605 if g_debug then
606 hr_utility.set_location(c_indent,30);
607 end if;
608 end remove_action_contexts;
609 --
610 /*
611 * Deletes all latest balances and associated balance
612 * context values for the specified assignment action id.
613 * balances are only removed if a prev balance does not exist,
614 * other wise the prev blance is assigned to the latest balance
615 *
616 * Deletes all latest balances if the action before rolled back is a
617 * balance adjustment or a balance initialisation. This is because as
618 * these are 'special' types of sequenced actions, their results may
619 * have fed the balances without the latest balances being owned by them.
620 */
621
622 procedure remove_balances(p_info in rollback_rec) is
623 c_indent varchar2(40);
624 cursor baplb(l_person_id number) is
625 --
626 select /*+ INDEX(plb PAY_PERSON_LATEST_BALANCES_N3)*/
627 plb.latest_balance_id,
628 plb.prev_balance_value,
629 plb.prev_assignment_action_id,
630 plb.expired_value,
631 plb.expired_assignment_action_id,
632 plb.assignment_action_id
633 from pay_person_latest_balances plb,
634 pay_defined_balances pdb,
635 pay_balance_feeds_f pbf,
636 pay_run_result_values rrv,
637 pay_run_results prr
638 where prr.assignment_action_id = p_info.assact_id
639 and rrv.run_result_id = prr.run_result_id
640 and rrv.result_value is not null
641 and pbf.input_value_id = rrv.input_value_id
642 and pdb.balance_type_id = pbf.balance_type_id
643 and plb.defined_balance_id = pdb.defined_balance_id
644 and plb.person_id = l_person_id
645 and p_info.action_date between pbf.effective_start_date
646 and pbf.effective_end_date;
647 --
648 cursor baalb is
649 select /*+ INDEX(alb PAY_ASSIGNMENT_LATEST_BALA_N3)*/
650 alb.latest_balance_id,
651 alb.prev_balance_value,
652 alb.prev_assignment_action_id,
653 alb.expired_value,
654 alb.expired_assignment_action_id,
655 alb.assignment_action_id
656 from pay_assignment_latest_balances alb,
657 pay_defined_balances pdb,
658 pay_balance_feeds_f pbf,
659 pay_run_result_values rrv,
660 pay_run_results prr
661 where prr.assignment_action_id = p_info.assact_id
662 and rrv.run_result_id = prr.run_result_id
663 and rrv.result_value is not null
664 and pbf.input_value_id = rrv.input_value_id
665 and pdb.balance_type_id = pbf.balance_type_id
666 and alb.defined_balance_id = pdb.defined_balance_id
667 and alb.assignment_id = p_info.assignment_id
668 and p_info.action_date between pbf.effective_start_date
669 and pbf.effective_end_date;
670 --
671 cursor balb (l_person_id number)is
672 select
673 lb.latest_balance_id,
674 lb.prev_balance_value,
675 lb.prev_assignment_action_id,
676 lb.prev_expiry_date,
677 lb.expired_value,
678 lb.expired_assignment_action_id,
679 lb.assignment_action_id
680 from pay_latest_balances lb,
681 pay_defined_balances pdb,
682 pay_balance_feeds_f pbf,
683 pay_run_result_values rrv,
684 pay_run_results prr
685 where prr.assignment_action_id = p_info.assact_id
686 and rrv.run_result_id = prr.run_result_id
687 and rrv.result_value is not null
688 and pbf.input_value_id = rrv.input_value_id
689 and pdb.balance_type_id = pbf.balance_type_id
690 and lb.defined_balance_id = pdb.defined_balance_id
691 and lb.person_id = l_person_id
692 and ( lb.assignment_id = p_info.assignment_id
693 or lb.assignment_id is null)
694 and ( lb.process_group_id = (select distinct pog.parent_object_group_id
695 from pay_object_groups pog
696 where pog.source_id = p_info.assignment_id
697 and pog.source_type = 'PAF')
698 or lb.process_group_id is null)
699 and p_info.action_date between pbf.effective_start_date
700 and pbf.effective_end_date;
701 --
702 cursor cplb is
703 select plb.latest_balance_id,
704 plb.prev_balance_value,
705 plb.prev_assignment_action_id,
706 plb.expired_value,
707 plb.expired_assignment_action_id
708 from pay_person_latest_balances plb
709 where plb.assignment_action_id = p_info.assact_id;
710 --
711 cursor calb is
712 select alb.latest_balance_id,
713 alb.prev_balance_value,
714 alb.prev_assignment_action_id,
715 alb.expired_value,
716 alb.expired_assignment_action_id
717 from pay_assignment_latest_balances alb
718 where alb.assignment_action_id = p_info.assact_id;
719 --
720 cursor clb is
721 select lb.latest_balance_id,
722 lb.prev_balance_value,
723 lb.prev_assignment_action_id,
724 lb.prev_expiry_date,
725 lb.expired_value,
726 lb.expired_assignment_action_id
727 from pay_latest_balances lb
728 where lb.assignment_action_id = p_info.assact_id;
729 --
730 l_person_id per_all_assignments_f.person_id%TYPE;
731
732 begin
733 -- Remove latest balances and associated context values.
734 -- Delete cascade not used because a) efficiency and
735 -- b) must delete for both rollback and mark for retry.
736 -- Cursor loops used to avoid full table access of
737 -- the balance context table.
738
739 if g_debug then
740 c_indent := 'py_rollback_pkg.remove_balances';
741 hr_utility.set_location(c_indent,10);
742 end if;
743 if (p_info.action_type in ('B', 'I', 'V')) then
744 -- treat balance adjustment as a special case : we should
745 -- delete all latest balances for an assignment if its having
746 -- a balance adjustment being rolled back (because its very
747 -- difficult to work out which if any of the latest, previous
748 -- or expired balances were fed by the balance adjustment)
749 -- And Reversals now too!
750
751 select person_id
752 into l_person_id
753 from per_all_assignments_f
754 where assignment_id = p_info.assignment_id
755 and p_info.action_date between
756 effective_start_date and effective_end_date;
757
758 if g_debug then
759 hr_utility.set_location(c_indent,15);
760 end if;
761
762 if (p_info.single_bal_table <> 'Y') then
763
764 for bplbrec in baplb(l_person_id) loop
765 if bplbrec.assignment_action_id = p_info.assact_id then
766 if bplbrec.prev_balance_value=-9999
767 or bplbrec.prev_balance_value is NULL
768 or bplbrec.prev_assignment_action_id is NULL
769 then
770 delete from pay_balance_context_values bcv
771 where bcv.latest_balance_id = bplbrec.latest_balance_id;
772
773 delete from pay_person_latest_balances plb
774 where plb.latest_balance_id = bplbrec.latest_balance_id;
775 else
776 if bplbrec.prev_assignment_action_id = bplbrec.expired_assignment_action_id
777 then
778 update pay_person_latest_balances
779 set expired_assignment_action_id = -9999,
780 expired_value = -9999
781 where pay_person_latest_balances.latest_balance_id =
782 bplbrec.latest_balance_id;
783 end if;
784 update pay_person_latest_balances
785 set assignment_action_id = bplbrec.prev_assignment_action_id,
786 value = bplbrec.prev_balance_value,
787 prev_assignment_action_id = -9999,
788 prev_balance_value = -9999
789 where pay_person_latest_balances.latest_balance_id =
790 bplbrec.latest_balance_id;
791 end if;
792 else
793 delete from pay_balance_context_values bcv
794 where bcv.latest_balance_id = bplbrec.latest_balance_id;
795
796 delete from pay_person_latest_balances plb
797 where plb.latest_balance_id = bplbrec.latest_balance_id;
798 end if;
799 end loop;
800
801 for balbrec in baalb loop
802 if balbrec.assignment_action_id = p_info.assact_id then
803 if balbrec.prev_balance_value=-9999
804 or balbrec.prev_balance_value is NULL
805 or balbrec.prev_assignment_action_id is NULL
806 then
807 delete from pay_balance_context_values bcv
808 where bcv.latest_balance_id = balbrec.latest_balance_id;
809
810 delete from pay_assignment_latest_balances alb
811 where alb.latest_balance_id = balbrec.latest_balance_id;
812 else
813 if balbrec.prev_assignment_action_id = balbrec.expired_assignment_action_id
814 then
815 update pay_assignment_latest_balances
816 set expired_assignment_action_id = -9999,
817 expired_value = -9999
818 where pay_assignment_latest_balances.latest_balance_id =
819 balbrec.latest_balance_id;
820 end if;
821 update pay_assignment_latest_balances
822 set assignment_action_id = balbrec.prev_assignment_action_id,
823 value = balbrec.prev_balance_value,
824 prev_assignment_action_id = -9999,
825 prev_balance_value = -9999
826 where pay_assignment_latest_balances.latest_balance_id =
827 balbrec.latest_balance_id;
828 end if;
829 else
830 delete from pay_balance_context_values bcv
831 where bcv.latest_balance_id = balbrec.latest_balance_id;
832
833 delete from pay_assignment_latest_balances alb
834 where alb.latest_balance_id = balbrec.latest_balance_id;
835 end if;
836 end loop;
837
838 else /* single_bal_table */
839
840 for blbrec in balb(l_person_id) loop
841 if blbrec.assignment_action_id = p_info.assact_id then
842 if blbrec.prev_balance_value=-9999
843 or blbrec.prev_balance_value is NULL
844 or blbrec.prev_assignment_action_id is NULL
845 then
846 delete from pay_latest_balances lb
847 where lb.latest_balance_id = blbrec.latest_balance_id;
848 else
849 if blbrec.prev_assignment_action_id = blbrec.expired_assignment_action_id
850 then
851 update pay_latest_balances
852 set expired_assignment_action_id = -9999,
853 expired_value = -9999,
854 expired_date = null
855 where pay_latest_balances.latest_balance_id =
856 blbrec.latest_balance_id;
857 end if;
858 update pay_latest_balances
859 set assignment_action_id = blbrec.prev_assignment_action_id,
860 value = blbrec.prev_balance_value,
861 expiry_date = blbrec.prev_expiry_date,
862 prev_assignment_action_id = -9999,
863 prev_balance_value = -9999,
864 prev_expiry_date = null
865 where pay_latest_balances.latest_balance_id =
866 blbrec.latest_balance_id;
867 end if;
868 else
869 delete from pay_latest_balances alb
870 where alb.latest_balance_id = blbrec.latest_balance_id;
871 end if;
872 end loop;
873
874 end if; /* single_bal_table */
875
876 else
877 if g_debug then
878 hr_utility.set_location(c_indent,90);
879 end if;
880
881 if (p_info.single_bal_table <> 'Y') then
882
883 for calbrec in calb loop
884 if (calbrec.prev_balance_value=-9999
885 or calbrec.prev_balance_value is NULL
886 or calbrec.prev_assignment_action_id is NULL)
887 then
888 begin
889 delete from pay_balance_context_values bcv
890 where bcv.latest_balance_id = calbrec.latest_balance_id;
891 delete from pay_assignment_latest_balances alb
892 where alb.latest_balance_id=calbrec.latest_balance_id;
893 end;
894 else
895 begin
896 if calbrec.prev_assignment_action_id=calbrec.expired_assignment_action_id
897 then
898 update pay_assignment_latest_balances
899 set expired_assignment_action_id=-9999,
900 expired_value=-9999
901 where pay_assignment_latest_balances.latest_balance_id =
902 calbrec.latest_balance_id;
903 end if;
904 update pay_assignment_latest_balances
905 set assignment_action_id=calbrec.prev_assignment_action_id,
906 value=calbrec.prev_balance_value,
907 prev_assignment_action_id=-9999,
908 prev_balance_value=-9999
909 where pay_assignment_latest_balances.latest_balance_id =
910 calbrec.latest_balance_id;
911 end;
912 end if;
913 end loop;
914
915
916 if g_debug then
917 hr_utility.set_location(c_indent,100);
918 end if;
919 for cplbrec in cplb loop
920 if (cplbrec.prev_balance_value=-9999 or
921 cplbrec.prev_balance_value is NULL or
922 cplbrec.prev_assignment_action_id is NULL)
923 then
924 begin
925 delete from pay_balance_context_values bcv
926 where bcv.latest_balance_id = cplbrec.latest_balance_id;
927 delete from pay_person_latest_balances plb
928 where plb.latest_balance_id=cplbrec.latest_balance_id;
929 end;
930 else
931 begin
932 if cplbrec.prev_assignment_action_id=cplbrec.expired_assignment_action_id
933 then
934 update pay_person_latest_balances
935 set expired_assignment_action_id=-9999,
936 expired_value=-9999
937 where pay_person_latest_balances.latest_balance_id =
938 cplbrec.latest_balance_id;
939 end if;
940 update pay_person_latest_balances
941 set assignment_action_id=cplbrec.prev_assignment_action_id,
942 value=cplbrec.prev_balance_value,
943 prev_assignment_action_id=-9999,
944 prev_balance_value=-9999
945 where pay_person_latest_balances.latest_balance_id =
946 cplbrec.latest_balance_id;
947 end;
948 end if;
949 end loop;
950
951 else /* single_bal_table */
952
953 for clbrec in clb loop
954 if (clbrec.prev_balance_value=-9999
955 or clbrec.prev_balance_value is NULL
956 or clbrec.prev_assignment_action_id is NULL)
957 then
958 begin
959 delete from pay_latest_balances lb
960 where lb.latest_balance_id=clbrec.latest_balance_id;
961 end;
962 else
963 begin
964 if clbrec.prev_assignment_action_id=clbrec.expired_assignment_action_id
965 then
966 update pay_latest_balances
967 set expired_assignment_action_id=-9999,
968 expired_value=-9999,
969 expired_date = null
970 where pay_latest_balances.latest_balance_id =
971 clbrec.latest_balance_id;
972 end if;
973 update pay_latest_balances
974 set assignment_action_id=clbrec.prev_assignment_action_id,
975 value=clbrec.prev_balance_value,
976 expiry_date = clbrec.prev_expiry_date,
977 prev_assignment_action_id=-9999,
978 prev_balance_value=-9999,
979 prev_expiry_date = null
980 where pay_latest_balances.latest_balance_id =
981 clbrec.latest_balance_id;
982 end;
983 end if;
984 end loop;
985
986 end if; /* single_bal_table */
987 end if;
988
989 end remove_balances;
990 --
991 /*
992 * Delete all entries and entry values that were inserted
993 * by a balance adjustment.
994 * It is only called for a balance adjustment action.
995 */
996 procedure undo_bal_adjust(p_action_date in date, p_assact_id in number) is
997 -- Batch balance adjustment can have many adjustments
998 -- for an assignment action.
999 cursor c1 is
1000 select pee.element_entry_id
1001 from pay_element_entries_f pee,
1002 pay_assignment_actions paa
1003 where paa.assignment_action_id = p_assact_id
1004 and pee.assignment_id = paa.assignment_id
1005 and pee.creator_id = paa.assignment_action_id
1006 and pee.creator_type = 'B' -- (B)alance Adjustment
1007 and p_action_date between
1008 pee.effective_start_date and pee.effective_end_date;
1009 begin
1010 if g_debug then
1011 hr_utility.set_location('undo_bal_adjust', 60);
1012 end if;
1013 for c1rec in c1 loop
1014 -- Now, we attempt to delete the entry values.
1015 delete from pay_element_entry_values_f pev
1016 where pev.element_entry_id = c1rec.element_entry_id
1017 and p_action_date between
1018 pev.effective_start_date and pev.effective_end_date;
1019 --
1020 -- Now we attempt to delete the element entry row.
1021 -- Note, if this procedure is called from the balance
1022 -- adjustment form, the form may be attempting to delete
1023 -- this row. However, this could be called from the
1024 -- actions form, in which case we do need to do the delete.
1025 delete from pay_element_entries_f pee
1026 where pee.element_entry_id = c1rec.element_entry_id
1027 and p_action_date between
1028 pee.effective_start_date and pee.effective_end_date;
1029 end loop;
1030 if g_debug then
1031 hr_utility.set_location('undo_bal_adjust', 70);
1032 end if;
1033 --
1034 end undo_bal_adjust;
1035 --
1036 /*
1037 * Delete all messages (from pay_message_lines) as specified
1038 * by the source_type. in other words:
1039 * P : payroll_action_id
1040 * A : assignment_action_id
1041 */
1042 procedure remove_messages(p_info in rollback_rec, p_source_type in varchar2) is
1043 begin
1044 delete from pay_message_lines pml
1045 where pml.source_type = p_source_type
1046 and pml.source_id =
1047 decode(p_source_type,
1048 'P', p_info.pact_id,
1049 'A', p_info.assact_id);
1050 end remove_messages;
1051 --
1052 /*
1053 * Procedure to get information about the payroll action
1054 * this information is required for both payroll action
1055 * and assignment action rollback.
1056 * Note - it does perform a couple of validation
1057 * checks at the payroll action level and so might fail.
1058 */
1059 procedure get_pact_info(p_info in out nocopy rollback_rec)
1060 is
1061 c_indent varchar2(40);
1062 begin
1063 --
1064 -- get payroll action level information
1065 if g_debug then
1066 c_indent := 'py_rollback_pkg.get_pact_info';
1067 hr_utility.set_location(c_indent, 10);
1068 end if;
1069 select pac.business_group_id,
1070 pac.effective_date,
1071 pac.start_date,
1072 hrl.meaning,
1073 pac.action_type,
1074 pac.action_status,
1075 trunc(sysdate),
1076 pay.payroll_name,
1077 grp.name,
1078 grp.legislation_code,
1079 pac.date_earned,
1080 pac.purge_phase,
1081 pac.retro_definition_id,
1082 pac.batch_id,
1083 decode(pac.action_type, 'T',
1084 nvl(pay_core_utils.get_parameter('SLA_MODE',
1085 pac.legislative_parameters),
1086 'N'),
1087 'N')
1088 into p_info.bg_id,
1089 p_info.action_date,
1090 p_info.action_start_date,
1091 p_info.action_name,
1092 p_info.action_type,
1093 p_info.action_status,
1094 p_info.current_date,
1095 p_info.payroll_name,
1096 p_info.bg_name,
1097 p_info.legislation_code,
1098 p_info.date_earned,
1099 p_info.purge_phase,
1100 p_info.retro_definition_id,
1101 p_info.batch_id,
1102 p_info.sub_ledger_acc
1103 from pay_payroll_actions pac,
1104 pay_all_payrolls_f pay,
1105 per_business_groups_perf grp,
1106 hr_lookups hrl
1107 where pac.payroll_action_id = p_info.pact_id
1108 and hrl.lookup_code = pac.action_type
1109 and hrl.lookup_type = 'ACTION_TYPE'
1110 and grp.business_group_id = pac.business_group_id + 0
1111 and pay.payroll_id (+) = pac.payroll_id
1112 and pac.effective_date between
1113 pay.effective_start_date (+) and pay.effective_end_date (+);
1114 if g_debug then
1115 hr_utility.trace('action type is ' || p_info.action_type );
1116 end if;
1117 --
1118 --
1119 -- legislation information.
1120 p_info.independent_periods_flag := upper( hr_leg_rule.get_independent_periods(p_info.bg_id));
1121
1122 --
1123 -- Treat Retropays as special case as always Time Independent if not Group level
1124 --
1125 if (p_info.action_type in ('G', 'L', 'O') and
1126 p_info.independent_periods_flag = 'N') then
1127 p_info.independent_periods_flag := 'Y';
1128 end if;
1129 --
1130 -- see if this type of action is sequenced or not
1131 declare
1132 dummy number;
1133 begin
1134 p_info.sequenced_flag := TRUE;
1135 --
1136 select null
1137 into dummy
1138 from pay_action_classifications CLASS
1139 where CLASS.action_type = p_info.action_type
1140 and CLASS.classification_name = 'SEQUENCED';
1141 if g_debug then
1142 hr_utility.trace('this action type IS sequenced');
1143 end if;
1144 exception
1145 when no_data_found then
1146 p_info.sequenced_flag := FALSE;
1147 if g_debug then
1148 hr_utility.trace('this action type NOT sequenced');
1149 end if;
1150 end;
1151 --
1152 /* get the object Types for this payroll_action */
1153 begin
1154 select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N50)*/
1155 decode (ppa.action_type,
1156 'X', paa.object_type,
1157 decode(paa.object_type,
1158 'PAF', null,
1159 paa.object_type))
1160 into p_info.object_type
1161 from pay_assignment_actions paa,
1162 pay_payroll_actions ppa
1163 where paa.payroll_action_id = p_info.pact_id
1164 and p_info.pact_id = ppa.payroll_action_id
1165 and paa.source_action_id is null
1166 and rownum = 1;
1167 exception
1168 when no_data_found then
1169 p_info.object_type := null;
1170 end;
1171 end get_pact_info;
1172 --
1173 --
1174 /*
1175 * Insert a message indicating a rollback has occurred. This
1176 * is used for both assignment and payroll action rollback.
1177 */
1178 procedure ins_rollback_message(p_info in rollback_rec, p_level in varchar2) is
1179 c_indent varchar2(40);
1180 l_line_text pay_message_lines.line_text%type;
1181 l_line_sequence number;
1182 l_payroll_id number;
1183 l_source_id number;
1184 l_source_type pay_message_lines.source_type%type;
1185 l_action_name hr_lookups.meaning%type;
1186 begin
1187 g_debug := hr_utility.debug_enabled;
1188 if g_debug then
1189 c_indent := 'py_rollback_pkg.ins_rollback_message';
1190 hr_utility.set_location(c_indent, 10);
1191 end if;
1192 -- Set up a message for either rollback at assignment or
1193 -- payroll action level.
1194 if(p_level = 'A') then
1195 hr_utility.set_message (801, 'HR_ACTION_ASACT_ROLLOK');
1196 hr_utility.set_message_token('ASG_NUMBER',p_info.assignment_number);
1197 hr_utility.set_message_token('FULL_NAME',p_info.full_name);
1198 hr_utility.set_message_token
1199 ('SYSDATE',fnd_date.date_to_canonical(p_info.current_date));
1200 --
1201 -- Message will insert at payroll action level.
1202 l_source_id := p_info.pact_id;
1203 l_source_type := 'P';
1204 --
1205 else
1206 -- Rollback level is 'P'.
1207 --
1208 -- For Magnetic Transfer, the action name must be taken
1209 -- from the Payment Type Name (i.e. BACS, NACHA etc).
1210 if(p_info.action_type = 'M') then
1211 if g_debug then
1212 hr_utility.set_location(c_indent, 90);
1213 end if;
1214 select ppt.payment_type_name
1215 into l_action_name
1216 from pay_payroll_actions pac,
1217 pay_payment_types ppt
1218 where pac.payroll_action_id = p_info.pact_id
1219 and ppt.payment_type_id = pac.payment_type_id;
1220 else
1221 l_action_name := p_info.action_name;
1222 end if;
1223 --
1224 -- The message we set up depends on whether or not the
1225 -- Payroll Action is restricted or unrestricted.
1226 if(p_info.payroll_name is null) then
1227 hr_utility.set_message(801,'HR_ACTION_PACT_ROLLNOPAY');
1228 else
1229 hr_utility.set_message(801,'HR_ACTION_PACT_ROLLPAY');
1230 hr_utility.set_message_token('PAYROLL_NAME', p_info.payroll_name);
1231 end if;
1232 --
1233 -- Common message tokens.
1234 hr_utility.set_message_token('ACTION_TYPE',l_action_name);
1235 hr_utility.set_message_token('BG_NAME',p_info.bg_name);
1236 hr_utility.set_message_token('SYSDATE',
1237 fnd_date.date_to_canonical(p_info.current_date));
1238 --
1239 -- Message will insert at business group level.
1240 l_source_id := p_info.bg_id;
1241 l_source_type := 'B';
1242 end if; --- rollback level.
1243 --
1244 -- Get text of message we have set up.
1245 l_line_text := substrb(hr_utility.get_message, 1, 240);
1246 --
1247 -- Write message into message lines table.
1248 if g_debug then
1249 hr_utility.set_location(c_indent, 10);
1250 end if;
1251 insert into pay_message_lines (
1252 line_sequence,
1253 payroll_id,
1254 message_level,
1255 source_id,
1256 source_type,
1257 line_text)
1258 values (pay_message_lines_s.nextval,
1259 l_payroll_id,
1260 'I', -- information.
1261 l_source_id,
1262 l_source_type,
1263 l_line_text);
1264 --
1265 end ins_rollback_message;
1266 --
1267 /*---------------------- ins_rollback_message ---------------------------*/
1268 /*
1269 * overloaded procedure to insert a rollback message on successful
1270 * completion of the rolling back of a payroll action. This must
1271 * be callable externally, since the rollback process needs to be
1272 * able to insert this message, independently of the plsql.
1273 */
1274 procedure ins_rollback_message(p_payroll_action_id in number) is
1275 info rollback_rec;
1276 begin
1277 info.pact_id := p_payroll_action_id;
1278 get_pact_info(info); -- payroll action information.
1279 ins_rollback_message(info, 'P'); -- the message itself.
1280 end ins_rollback_message;
1281 --
1282 /*------------------------ undo_stop_update -----------------------------*/
1283 /*
1284 * This procedure is called when we have detected the need to undo the
1285 * effect of a stop or update recurring entry formula result rule.
1286 * Note that, due to the complexity of calculating entry end dates, we
1287 * call the existing routine, but trap error messages that are
1288 * inappropriate for our application.
1289 */
1290 procedure undo_stop_update(
1291 p_ee_id in number,
1292 p_mult in varchar,
1293 p_date in date,
1294 p_mode in varchar2) is
1295 --
1296 -- Local variables.
1297 effstart date;
1298 effend date;
1299 val_start date;
1300 val_end date;
1301 next_end date;
1302 max_end date;
1303 orig_ee_id number;
1304 prev_asgact_id number;
1305 asg_id number;
1306 el_id number;
1307 c_indent varchar2(40);
1308 begin
1309 -- Select some information about the entry we are operating on.
1310 if g_debug then
1311 c_indent := 'py_rollback_pkg.undo_stop_update';
1312 hr_utility.set_location(c_indent, 10);
1313 end if;
1314 select pee.effective_start_date,
1315 pee.effective_end_date,
1316 pee.original_entry_id,
1317 pee.assignment_id,
1318 pee.element_link_id
1319 into effstart, effend, orig_ee_id, asg_id, el_id
1320 from pay_element_entries_f pee
1321 where pee.element_entry_id = p_ee_id
1322 and p_date between
1323 pee.effective_start_date and pee.effective_end_date;
1324 --
1325 -- Do nothing if the entry end date is end of time.
1326 if(effend = c_eot) then
1327 return;
1328 end if;
1329 --
1330 -- For undo update, we have to get next effective start date.
1331 if(p_mode = 'DELETE_NEXT_CHANGE') then
1332 begin
1333 if g_debug then
1334 hr_utility.set_location(c_indent, 20);
1335 end if;
1336 select min(ee.effective_end_date)
1337 into next_end
1338 from pay_element_entries_f ee
1339 where ee.element_entry_id = p_ee_id
1340 and ee.effective_start_date > effend;
1341 exception
1342 when no_data_found then null;
1343 end;
1344 --
1345 val_start := effend + 1;
1346 --
1347 if next_end is null then
1348 val_end := c_eot;
1349 else
1350 val_end := next_end;
1351 end if;
1352 elsif(p_mode = 'FUTURE_CHANGE') then
1353 val_start := effend + 1;
1354 val_end := c_eot;
1355 end if;
1356 --
1357 -- For either mode, we need to obtain the date to which
1358 -- we may legally extend the entry.
1359 declare
1360 message varchar2(200);
1361 applid varchar2(200);
1362 begin
1363 max_end := hr_entry.recurring_entry_end_date (
1364 asg_id, el_id, p_date, 'Y', p_mult, p_ee_id, orig_ee_id);
1365 exception
1366 -- Several error messages can be raised from this procedure.
1367 -- We wish to trap a number of them, as they should be ignored
1368 -- for our purposes.
1369 when hr_utility.hr_error then
1370 hr_utility.get_message_details(message,applid);
1371 --
1372 if(message in ('HR_7699_ELE_ENTRY_REC_EXISTS',
1373 'HR_7700_ELE_ENTRY_REC_EXISTS',
1374 'HR_6281_ELE_ENTRY_DT_DEL_LINK',
1375 'HR_6283_ELE_ENTRY_DT_ELE_DEL',
1376 'HR_6284_ELE_ENTRY_DT_ASG_DEL')
1377 ) then
1378 -- We cannot extend the entry.
1379 if g_debug then
1380 hr_utility.set_location(c_indent, 25);
1381 end if;
1382 if(p_mode = 'DELETE_NEXT_CHANGE') then
1383 update pay_element_entries_f ee
1384 set updating_action_id=NULL
1385 ,updating_action_type =NULL
1386 where ee.element_entry_id = p_ee_id
1387 and ee.effective_start_date = val_start;
1388 elsif(p_mode = 'FUTURE_CHANGE') then
1389 update pay_element_entries_f ee
1390 set updating_action_id=NULL
1391 ,updating_action_type =NULL
1392 where ee.element_entry_id = p_ee_id
1393 and ee.effective_start_date = effstart;
1394 end if;
1395 return;
1396 else
1397 -- Should fail if it is anything else.
1398 raise;
1399 end if;
1400 end;
1401 --
1402 -- Process the delete of element entries.
1403 if(p_mode = 'DELETE_NEXT_CHANGE') then
1404 hr_utility.set_location(c_indent, 40);
1405 delete from pay_element_entries_f ee
1406 where ee.element_entry_id = p_ee_id
1407 and ee.effective_start_date = val_start;
1408 --
1409 hr_utility.set_location(c_indent, 45);
1410 delete from pay_element_entry_values_f eev
1411 where eev.element_entry_id = p_ee_id
1412 and eev.effective_start_date = val_start;
1413 --
1414 hr_utility.set_location(c_indent, 50);
1415 update pay_element_entries_f ee
1416 set ee.effective_end_date = next_end
1417 where ee.element_entry_id = p_ee_id
1418 and ee.effective_start_date = effstart;
1419 --
1420 hr_utility.set_location(c_indent, 55);
1421 update pay_element_entry_values_f eev
1422 set eev.effective_end_date = next_end
1423 where eev.element_entry_id = p_ee_id
1424 and eev.effective_start_date = effstart;
1425 --
1426 elsif(p_mode = 'FUTURE_CHANGE') then
1427 --
1428 hr_utility.set_location(c_indent, 60);
1429 delete from pay_element_entries_f ee
1430 where ee.element_entry_id = p_ee_id
1431 and ee.effective_start_date > effstart;
1432 --
1433 hr_utility.set_location(c_indent, 65);
1434 delete from pay_element_entry_values_f eev
1435 where eev.element_entry_id = p_ee_id
1436 and eev.effective_start_date > effstart;
1437 --
1438 hr_utility.set_location(c_indent, 70);
1439 update pay_element_entries_f ee
1440 set ee.effective_end_date = max_end,
1441 ee.updating_action_id=NULL,
1442 ee.updating_action_type=NULL
1443 where ee.element_entry_id = p_ee_id
1444 and ee.effective_start_date = effstart;
1445 /*BUG#6200530*/
1446 /*check for the additional assignment id (in case of an STOP RECURRING after an UPDATE RECURRING)*/
1447 select ee.prev_upd_action_id
1448 into prev_asgact_id
1449 from pay_element_entries_f ee
1450 where ee.element_entry_id = p_ee_id
1451 and ee.effective_start_date = effstart;
1452 /*If it is the case then update the updating_action_id with the asg_action_id of the previous UPDATE RECURRING operation*/
1453 if(prev_asgact_id is not null) then
1454 update pay_element_entries_f ee
1455 set ee.updating_action_id=prev_asgact_id,
1456 ee.prev_upd_action_id=NULL,
1457 ee.updating_action_type='U'
1458 where ee.element_entry_id = p_ee_id
1459 and ee.effective_start_date = effstart;
1460 end if;
1461 /*End of BUG#6200530*/
1462 --
1463 hr_utility.set_location(c_indent, 75);
1464 update pay_element_entry_values_f eev
1465 set eev.effective_end_date = max_end
1466 where eev.element_entry_id = p_ee_id
1467 and eev.effective_start_date = effstart;
1468 end if;
1469 --
1470 end undo_stop_update;
1471 --
1472 /*
1473 * This procedure attempts to reverse the effects of stop and/or
1474 * update formula result rules. Due to the implementation of
1475 * this functionality, the undo is non-deterministic, i.e. we
1476 * cannot guarantee to return the database to the exact state
1477 * it was in before the Payroll Run or QuickPay was processed.
1478 */
1479 procedure proc_entry_dml(p_info in rollback_rec) is
1480 -- This cursor returns candidates for possible undo
1481 -- of stop ree frr. i.e. recurring entries that have
1482 -- an effective_end_date that is same as runs date earned.
1483 cursor stp is
1484 select pet.multiple_entries_allowed_flag,
1485 pee.element_entry_id,pee.updating_action_type
1486 from pay_element_types_f pet,
1487 pay_element_links_f pel,
1488 pay_element_entries_f pee
1489 where pee.assignment_id = p_info.assignment_id
1490 and pee.entry_type = 'E'
1491 and pel.element_link_id = pee.element_link_id
1492 and p_info.action_date between
1493 pel.effective_start_date and pel.effective_end_date
1494 and pet.element_type_id = pel.element_type_id
1495 and p_info.action_date between
1496 pet.effective_start_date and pet.effective_end_date
1497 and pet.processing_type = 'R'
1498 and pee.updating_action_id = p_info.assact_id
1499 and ((pee.effective_end_date = p_info.date_earned
1500 and pee.effective_start_date <> p_info.action_date
1501 and pee.updating_action_type is NULL
1502 and p_info.date_earned between
1503 pee.effective_start_date and pee.effective_end_date)
1504 or(pee.updating_action_type='S'));
1505
1506 --
1507 -- This cursor returns candidates for undo update ree frr.
1508 -- i.e. entries that have been updated on the date of the
1509 -- Payroll Run, by the updating assignment action.
1510 cursor upd is
1511 select pet.multiple_entries_allowed_flag,
1512 pee.element_entry_id
1513 from pay_element_types_f pet,
1514 pay_element_links_f pel,
1515 pay_element_entries_f pee
1516 where pee.assignment_id = p_info.assignment_id
1517 and pee.entry_type = 'E'
1518 and ((pee.effective_start_date = p_info.action_date
1519 and pee.updating_action_type is NULL)
1520 or pee.updating_action_type='U')
1521 and pee.updating_action_id = p_info.assact_id
1522 and pel.element_link_id = pee.element_link_id
1523 and p_info.action_date between
1524 pel.effective_start_date and pel.effective_end_date
1525 and pet.element_type_id = pel.element_type_id
1526 and p_info.action_date between
1527 pet.effective_start_date and pet.effective_end_date
1528 and pet.processing_type = 'R';
1529 --
1530 c_indent varchar2(40);
1531 v_max_date date; -- maximum entry end date
1532 begin
1533 if g_debug then
1534 c_indent := 'py_rollback_pkg.proc_entry_dml';
1535 hr_utility.set_location(c_indent,90);
1536 end if;
1537 -- Begin by processing for stop entry dml.
1538 for stprec in stp loop
1539
1540 if (stprec.updating_action_type is NULL)
1541 then
1542 -- We may have a stopped entry, but we need to
1543 -- see if this really is the case.
1544 if g_debug then
1545 hr_utility.set_location(c_indent,90);
1546 end if;
1547 select max(pee.effective_end_date)
1548 into v_max_date
1549 from pay_element_entries_f pee
1550 where pee.element_entry_id = stprec.element_entry_id;
1551 else
1552 v_max_date := p_info.date_earned;
1553 end if;
1554 --
1555 if(v_max_date = p_info.date_earned) then
1556 -- Assume entry has been chopped by the run.
1557 -- Call procedure to actually perform undo.
1558 undo_stop_update (stprec.element_entry_id,
1559 stprec.multiple_entries_allowed_flag,
1560 p_info.date_earned, 'FUTURE_CHANGE');
1561 end if;
1562 end loop;
1563 --
1564 -- Process for update entry dml.
1565 for updrec in upd loop
1566 -- Since it is not possible to use the Payroll Run to
1567 -- perform a correction on an entry, we know there
1568 -- should be an entry record existing on the date
1569 -- before the run. This is important, since it is
1570 -- required by the procedure that follows.
1571 undo_stop_update (updrec.element_entry_id,
1572 updrec.multiple_entries_allowed_flag,
1573 (p_info.action_date - 1), 'DELETE_NEXT_CHANGE');
1574 end loop;
1575 end proc_entry_dml;
1576 --
1577 --
1578 /*
1579 * Following the main work of rolling back or marking an
1580 * assignment action for retry, the assignment action
1581 * row itself (the 'base table' row) may need updating or
1582 * deleting. (Depends on the wishes of the client). In
1583 * addition, we may need to remove interlock rows.
1584 */
1585 procedure act_base_table_dml(p_info in rollback_rec) is
1586 c_indent constant varchar2(40) := 'py_rollback_pkg.act_base_table_dml';
1587 begin
1588 -- see if we want to alter the assignment action itself (we wouldn't
1589 -- if we were being called from a form).
1590 -- However, if we are called from the payroll action level,
1591 -- we must process the assignment action row.
1592 if(not p_info.leave_row or p_info.rollback_level = 'P') then
1593 if(p_info.rollback_mode) = 'RETRY' then
1594 if g_debug then
1595 hr_utility.set_location(c_indent, 10);
1596 end if;
1597 --
1598 update pay_assignment_actions
1599 set action_status = 'M'
1600 where source_action_id = p_info.assact_id;
1601 --
1602 update pay_assignment_actions
1603 set action_status = 'M'
1604 where assignment_action_id = p_info.assact_id;
1605 --
1606 elsif(p_info.rollback_mode) = 'BACKPAY' then
1607 if g_debug then
1608 hr_utility.set_location(c_indent, 20);
1609 end if;
1610 --
1611 update pay_assignment_actions
1612 set action_status = 'B'
1613 where source_action_id = p_info.assact_id;
1614 --
1615 update pay_assignment_actions
1616 set action_status = 'B'
1617 where assignment_action_id = p_info.assact_id;
1618 --
1619 elsif(p_info.rollback_mode) = 'ROLLBACK' then
1620 -- there may be pay_action_interlock rows.
1621 -- which are locking other assignment actions.
1622 if g_debug then
1623 hr_utility.set_location(c_indent, 30);
1624 end if;
1625 delete from pay_action_interlocks lck
1626 where lck.locking_action_id = p_info.assact_id;
1627 --
1628 remove_archive_items(p_info, p_info.assact_id, 'AAC');
1629 remove_action_information(p_info.assact_id, 'AAC');
1630 --
1631 delete from pay_assignment_actions
1632 where source_action_id = p_info.assact_id;
1633 --
1634 delete from pay_assignment_actions
1635 where assignment_action_id = p_info.assact_id;
1636 end if;
1637 else
1638 -- In the case of rolling back (from the form), we
1639 -- still need to delete interlock rows. Of course,
1640 -- in this case we do not delete the action.
1641 if(p_info.rollback_mode = 'ROLLBACK') then
1642 --
1643 remove_archive_items(p_info, p_info.assact_id, 'AAC');
1644 remove_action_information(p_info.assact_id, 'AAC');
1645 --
1646 delete from pay_assignment_actions
1647 where source_action_id = p_info.assact_id;
1648 --
1649 delete from pay_action_interlocks lck
1650 where lck.locking_action_id = p_info.assact_id;
1651 --
1652 end if;
1653 end if;
1654 end act_base_table_dml;
1655 --
1656 /*---------------------- do_assact_rollback -------------------------------*/
1657 /*
1658 NAME
1659 do_assact_rollback - Perform dml to rollback assignment action.
1660 DESCRIPTION
1661 performs rollback/mark for retry dml for assignment action.
1662 NOTES
1663 This internal routine is central to the rollback process. It does
1664 the actual work of rolling back/marking for retry an assignment
1665 action. The routine makes no checks for validity of the action.
1666 --
1667 There are nested procedures to perform many of the specific
1668 actions required. This is in an attempt to keep the logic
1669 more understandable.
1670 */
1671 procedure do_assact_rollback(p_info in rollback_rec) is
1672 c_indent varchar2(40);
1673 chld_info rollback_rec;
1674 purge_child boolean;
1675 --
1676 v_prj_flg_yes_count number;
1677 v_prj_flg_no_count number;
1678 v_prj_flg_x_count number;
1679 v_return_status varchar2(40);
1680 v_person_id per_all_people_f.person_id%TYPE;
1681 v_time_period_id per_time_periods.time_period_id%TYPE;
1682 --
1683 cursor chdact (p_asgact_id in number) is
1684 select paa_chd.assignment_action_id
1685 from pay_assignment_actions paa_chd
1686 where paa_chd.source_action_id = p_asgact_id
1687 order by paa_chd.action_sequence desc;
1688 --
1689 cursor get_person_id (p_assignment_id in per_all_people_f.person_id%TYPE, p_effective_date in date) is
1690 select PERSON_ID
1691 from per_all_assignments_f paaf
1692 where paaf.assignment_id = p_assignment_id
1693 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1694 --
1695 cursor get_time_period_id (p_payroll_id in pay_payrolls_f.payroll_id%TYPE, p_effective_date in date) is
1696 select time_period_id
1697 from per_time_periods ptp
1698 where ptp.payroll_id = p_payroll_id
1699 and p_effective_date between ptp.start_date and ptp.end_date;
1700 --
1701 begin
1702 --
1703 if g_debug then
1704 c_indent := 'py_rollback_pkg.do_assact_rollback';
1705 hr_utility.set_location(c_indent, 10);
1706 hr_utility.set_location('p_info.assact_id'||p_info.assact_id,11);
1707 hr_utility.set_location('p_info.action_date'||p_info.action_date,12);
1708 hr_utility.set_location('p_info.pact_id'||p_info.pact_id,13);
1709 end if;
1710 --
1711 -- Firstly remove any child actions.
1712 chld_info := p_info;
1713 for chdrec in chdact (p_info.assact_id) loop
1714 chld_info.assact_id := chdrec.assignment_action_id;
1715 do_assact_rollback(chld_info);
1716 --
1717 -- Remove child assignment actions if needed.
1718 purge_child := TRUE;
1719 --
1720 if (p_info.rollback_mode = 'BACKPAY'
1721 and p_info.retro_purge = 'N') then
1722 purge_child := FALSE;
1723 end if;
1724 --
1725 -- If its an Enhanced retropay by Ele and using the
1726 -- process group level interlocking then do not
1727 -- remove the child actions.
1728 if (p_info.retro_definition_id is not null
1729 and p_info.independent_periods_flag = 'G') then
1730 purge_child := FALSE;
1731 end if;
1732 --
1733 if (purge_child = TRUE) then
1734 delete from pay_action_interlocks
1735 where locking_action_id = chld_info.assact_id;
1736 if g_debug then
1737 hr_utility.set_location('About to remove assg actions' , 11);
1738 end if;
1739 delete from pay_assignment_actions
1740 where assignment_action_id = chld_info.assact_id;
1741 end if;
1742 end loop;
1743 --
1744 -- Perform actions that are specific to sequenced actions.
1745 -- In other words, remove the rows that are only inserted
1746 -- by these types of actions.
1747 --
1748 if(p_info.sequenced_flag) then
1749 --
1750 -- if the assignment being rolled back contributes to a group run balance
1751 -- need to remove the contributing amount from the group run balance.
1752 --
1753 if (p_info.rollback_mode <> 'BACKPAY') then
1754 pay_balance_pkg.remove_asg_contribs(p_info.pact_id
1755 ,p_info.assact_id
1756 ,p_info.grp_multi_thread);
1757 --
1758 -- now delete assignment level run balances
1759 --
1760 delete from pay_run_balances
1761 where assignment_action_id = p_info.assact_id;
1762 else
1763 --
1764 -- now update assignment level run balances
1765 --
1766 update pay_run_balances
1767 set balance_value = 0
1768 where assignment_action_id = p_info.assact_id;
1769 end if;
1770 --
1771 remove_balances(p_info); -- latest balances.
1772 remove_run_results(p_info); -- run results and values.
1773 remove_action_contexts(p_info.assact_id); -- action contexts
1774 end if;
1775 --
1776 -- Delete specific types of rows for certain action types.
1777 if((p_info.action_type = 'B' and p_info.rollback_mode = 'ROLLBACK') OR
1778 (p_info.action_type = 'I' )) then
1779 --
1780 -- OK here's the scoop. If we are in rollback mode
1781 -- then remove the element entries for bal adjust
1782 --
1783 -- Otherwise we must be in Retry mode, hence only remove the
1784 -- Entries if it is a formula based balance adjustment.
1785 --
1786 -- entries/values for bal adjust.
1787 if (p_info.rollback_mode = 'ROLLBACK' or p_info.action_type = 'I') then
1788 undo_bal_adjust(p_info.action_date, p_info.assact_id);
1789 else
1790 declare
1791 l_et_id pay_payroll_actions.element_type_id%type;
1792 begin
1793 --
1794 select ppa.element_type_id
1795 into l_et_id
1796 from pay_assignment_actions paa,
1797 pay_payroll_actions ppa
1798 where ppa.payroll_action_id = paa.payroll_action_id
1799 and paa.assignment_action_id = p_info.assact_id;
1800 --
1801 if (l_et_id is not null) then
1802 undo_bal_adjust(p_info.action_date, p_info.assact_id);
1803 end if;
1804 --
1805 end;
1806 end if;
1807 --
1808 elsif(p_info.action_type in ('R', 'Q')) then
1809 proc_entry_dml(p_info); -- stop/update ree frr.
1810 --
1811 elsif(p_info.action_type in ('P', 'U')) then
1812 -- Remove pre-payment rows.
1813 -- Note, this causes cascade delete of pay_coin_anal_elements.
1814 if g_debug then
1815 hr_utility.set_location('Error abt to occur', 13);
1816 end if;
1817 delete from pay_pre_payments ppp
1818 where ppp.assignment_action_id = p_info.assact_id;
1819 --
1820 elsif(p_info.action_type in ('C', 'S', 'EC')) then
1821 -- Remove costing rows.
1822 if g_debug then
1823 hr_utility.set_location(c_indent, 20);
1824 end if;
1825 --Bug 11708644, PAYROLL/PROJECTS INTEGRATION changes FOR 12.2 starts here
1826 -- Check for transfered_to_prj flag.
1827 SELECT count(DECODE(transfered_to_prj,'Y','YES',NULL)) AS prj_flg_yes_count,
1828 count(DECODE(transfered_to_prj,'N','No',NULL)) AS prj_flg_no_count,
1829 count(DECODE(transfered_to_prj,'X','X',NULL)) AS prj_flg_x_count
1830 INTO v_prj_flg_yes_count,v_prj_flg_no_count,v_prj_flg_x_count
1831 from pay_costs pc
1832 where pc.assignment_action_id = p_info.assact_id;
1833 --
1834 if(v_prj_flg_yes_count <> 0) then
1835 hr_utility.set_message(801, 'PAY_449895_STP_RETRY_RLBK_COST');
1836 hr_utility.set_message_token('ASGN_ID', p_info.assignment_number);
1837 hr_utility.raise_error;
1838 elsif(v_prj_flg_no_count <> 0 OR v_prj_flg_x_count <> 0) then
1839 --
1840 if g_debug then
1841 hr_utility.set_location(c_indent, 21);
1842 end if;
1843 --
1844 -- First get personid, time_period_id, these are needed by projects
1845 open get_person_id(p_info.assignment_id,p_info.action_date);
1846 fetch get_person_id into v_person_id;
1847 close get_person_id;
1848 --
1849 open get_time_period_id(p_info.payroll_id,p_info.action_date);
1850 fetch get_time_period_id into v_time_period_id;
1851 close get_time_period_id;
1852 --
1853 -- Now call the projects audit API
1854 PA_PAY_UTIL.update_pa_audit (
1855 P_PAYROLL_ACTION_ID => p_info.pact_id,
1856 P_PAYROLL_ID => p_info.payroll_id,
1857 P_TIME_PERIOD_ID => v_time_period_id,
1858 P_PERSON_ID => v_person_id,
1859 P_ASSIGNMENT_ID => p_info.assignment_id,
1860 X_RETURN_STATUS => v_return_status
1861 );
1862 if g_debug then
1863 hr_utility.trace('Return status of projects audit API: ' || v_return_status);
1864 end if;
1865 -- Perform actual rollback
1866 delete from pay_costs pc
1867 where pc.assignment_action_id = p_info.assact_id;
1868 --
1869 ELSE
1870 --
1871 if g_debug then
1872 hr_utility.set_location(c_indent, 22);
1873 end if;
1874 -- Perform only rollback
1875 delete from pay_costs pc
1876 where pc.assignment_action_id = p_info.assact_id;
1877 --
1878 end if;
1879 if g_debug then
1880 hr_utility.set_location(c_indent, 25);
1881 end if;
1882 --Bug 11708644, PAYROLL/PROJECTS INTEGRATION changes FOR 12.2 end here
1883 --
1884 elsif(p_info.action_type = 'CP') then
1885 -- Remove costing rows.
1886 delete from pay_payment_costs ppc
1887 where ppc.assignment_action_id = p_info.assact_id;
1888 --
1889 elsif(p_info.action_type = 'T') then
1890 -- Remove intermediate transfer table rows.
1891 if (p_info.sub_ledger_acc = 'N') then
1892 remove_gl_entries(p_info);
1893 else
1894 pay_sla_pkg.delete_event(p_info.assact_id);
1895 end if;
1896 --
1897 elsif(p_info.action_type = 'M') then
1898 -- Reset prenote date if this is a dummy payment i.e. payment value
1899 -- of zero.
1900 reset_prenote(p_info.assact_id);
1901 remove_archive_items(p_info, p_info.assact_id, 'AAP');
1902 remove_file_details(p_info, p_info.assact_id, 'PAA');
1903 --
1904 elsif(p_info.action_type = 'PP') then
1905 remove_archive_items(p_info, p_info.assact_id, 'AAP');
1906 remove_file_details(p_info, p_info.assact_id, 'PAA');
1907 --
1908 elsif(p_info.action_type = 'O') then
1909 -- Remove Retropay rows.
1910 remove_retro_ee(p_info.assact_id);
1911 --
1912 elsif(p_info.action_type = 'F') then
1913 -- Remove Advance pay rows.
1914 remove_adv_ee(p_info.assact_id);
1915 --
1916 elsif(p_info.action_type = 'X') then
1917 --Remove Archive Items
1918 remove_archive_items(p_info, p_info.assact_id, 'AAP');
1919 remove_action_information(p_info.assact_id, 'AAP');
1920 remove_file_details(p_info, p_info.assact_id, 'PAA');
1921 --
1922 elsif(p_info.action_type = 'G') then
1923 -- Remove Retropay by Action rows
1924 remove_retroact_ee(p_info.assact_id);
1925 --
1926 elsif(p_info.action_type = 'L') then
1927 -- Remove Retropay by Element rows
1928 remove_retroele_ee(p_info.assact_id, p_info.rollback_mode);
1929 --
1930 -- The following logic in now moved within the above procedure.
1931 --
1932 -- -- Finally reset the pay_retro_assignment_table if needed
1933 -- if (p_info.rollback_mode <> 'RETRY') then
1934 --
1935 -- pay_retro_pkg.merge_retro_assignments(p_info.assact_id);
1936 --
1937 -- -- Remove the asg act id, and in case merge has new earlier entries
1938 -- -- also change the reprocess_date
1939 -- update pay_retro_assignments ra
1940 -- set ra.retro_assignment_action_id = null,
1941 -- ra.reprocess_date = nvl( (
1942 -- select min(effective_date)
1943 -- from pay_retro_entries re
1944 -- where re.retro_assignment_id = ra.retro_assignment_id )
1945 -- ,ra.reprocess_date)
1946 -- where ra.retro_assignment_action_id = p_info.assact_id;
1947 --
1948 -- end if;
1949 --
1950 elsif(p_info.action_type = 'W') then
1951 -- Remove Advance Pay by Element rows
1952 remove_advpayele_ee(p_info.assact_id);
1953 --
1954 elsif(p_info.action_type = 'Z') then
1955 -- Remove the purge rollup balance rows.
1956 delete from pay_purge_rollup_balances rub
1957 where rub.assignment_action_id = p_info.assact_id;
1958 elsif(p_info.action_type = 'BEE') then
1959 -- Remove Batch Element Entry rows
1960 pay_mix_rollback_pkg.undo_mix_asg(p_info.assact_id);
1961 elsif(p_info.action_type = 'PRU') then
1962 -- Remove Rolled up payments
1963 delete from pay_contributing_payments
1964 where assignment_action_id = p_info.assact_id;
1965 end if;
1966 --
1967 -- Delete messages for the assignment action.
1968 remove_messages(p_info, 'A');
1969 --
1970 -- Rollback specific code.
1971 if(p_info.rollback_mode = 'ROLLBACK') then
1972 -- Insert message indicating rollback of assignment action.
1973 -- Only insert message if action is not an initial
1974 -- balance adjustment.
1975 if (p_info.action_type <> 'I') then
1976 ins_rollback_message(p_info, 'A');
1977 end if;
1978 --
1979 -- When we are rolling back QuickPay, we need to
1980 -- remove the QuickPay Inclusions.
1981 if(p_info.action_type = 'Q') then
1982 if g_debug then
1983 hr_utility.set_location(c_indent, 30);
1984 end if;
1985 --
1986 -- Enhancement 3368211
1987 --
1988 -- Delete from both PAY_QUICKPAY_INCLUSIONS and PAY_QUICKPAY_EXCLUSIONS.
1989 --
1990 -- There is a chance the assignment action id exists in both tables if
1991 -- the assignment action was created before the QuickPay Exclusions
1992 -- data model was in use.
1993 --
1994 delete from pay_quickpay_exclusions exc
1995 where exc.assignment_action_id = p_info.assact_id;
1996 --
1997 delete from pay_quickpay_inclusions inc
1998 where inc.assignment_action_id = p_info.assact_id;
1999 --
2000 end if;
2001 end if;
2002 --
2003 -- Following main processing, may need to do some
2004 -- work on the assignment action row itself.
2005 act_base_table_dml(p_info);
2006 --
2007 --
2008 end do_assact_rollback;
2009 --
2010 /*
2011 * Internal procedure : called for an individual assignment
2012 * action to validate that a rollback or mark for retry is
2013 * legal. Note, it does not guarantee that the rollback
2014 * will succeed, as this does not perform any dml.
2015 *
2016 * Assumes that val_pact_rollback has already been called
2017 * to obtain payroll action level information.
2018 */
2019 procedure val_assact_rollback(p_info in out nocopy rollback_rec)
2020 is
2021 l_action_sequence pay_assignment_actions.action_sequence%type;
2022 l_action_status pay_assignment_actions.action_status%type;
2023 l_person_id per_all_people_f.person_id%type;
2024 l_sec_status pay_assignment_actions.secondary_status%type;
2025 c_indent varchar2(40);
2026 begin
2027 -- Obtain information about this assignment action which we will
2028 -- need later on.
2029 -- Some of this is required for messages.
2030 if g_debug then
2031 c_indent := 'py_rollback_pkg.val_assact_rollback';
2032 hr_utility.trace('assact_id : ' || p_info.assact_id);
2033 end if;
2034 --
2035 -- OK We need to run different statements for different types
2036 -- of processes
2037 --
2038 hr_utility.set_location(c_indent, 10);
2039 if ((p_info.action_type = 'L'
2040 and p_info.object_type = 'POG')
2041 or p_info.object_type is not null
2042 ) then
2043 --
2044 if (p_info.action_type = 'L') then
2045 --
2046 hr_utility.set_location(c_indent, 20);
2047 -- OK this is a retropay that using the Object Group
2048 -- actions
2049 select null,
2050 ACT.action_sequence,
2051 ACT.action_status,
2052 ACT.secondary_status,
2053 null,
2054 PEO.person_id,
2055 substr(PEO.full_name,1,80),
2056 null
2057 into p_info.assignment_id,
2058 l_action_sequence,
2059 l_action_status,
2060 l_sec_status,
2061 p_info.payroll_id,
2062 l_person_id,
2063 p_info.full_name,
2064 p_info.assignment_number
2065 from
2066 per_all_people_f PEO,
2067 pay_object_groups POG_PER,
2068 pay_assignment_actions ACT
2069 where ACT.assignment_action_id = p_info.assact_id
2070 and ACT.source_action_id is null
2071 and ACT.object_id = POG_PER.object_group_id
2072 and POG_PER.source_id = PEO.person_id
2073 and p_info.action_date between
2074 PEO.effective_start_date and PEO.effective_end_date;
2075 else
2076 --
2077 hr_utility.set_location(c_indent, 30);
2078 -- OK its some sort of object action
2079 select null,
2080 ACT.action_sequence,
2081 ACT.action_status,
2082 ACT.secondary_status,
2083 null,
2084 null,
2085 null,
2086 null
2087 into p_info.assignment_id,
2088 l_action_sequence,
2089 l_action_status,
2090 l_sec_status,
2091 p_info.payroll_id,
2092 l_person_id,
2093 p_info.full_name,
2094 p_info.assignment_number
2095 from pay_assignment_actions ACT
2096 where ACT.assignment_action_id = p_info.assact_id;
2097 --
2098 end if;
2099 --
2100 else
2101 --
2102 hr_utility.set_location(c_indent, 40);
2103 -- It's a normal action
2104 --
2105 select ACT.assignment_id,
2106 ACT.action_sequence,
2107 ACT.action_status,
2108 ACT.secondary_status,
2109 ASS.payroll_id,
2110 PEO.person_id,
2111 substr(PEO.full_name,1,80),
2112 ASS.assignment_number
2113 into p_info.assignment_id,
2114 l_action_sequence,
2115 l_action_status,
2116 l_sec_status,
2117 p_info.payroll_id,
2118 l_person_id,
2119 p_info.full_name,
2120 p_info.assignment_number
2121 from per_all_assignments_f ASS,
2122 per_all_people_f PEO,
2123 pay_assignment_actions ACT
2124 where ACT.assignment_action_id = p_info.assact_id
2125 and ASS.assignment_id = ACT.assignment_id
2126 and PEO.person_id = ASS.person_id
2127 and ((p_info.action_type = 'X'
2128 and ASS.effective_start_date = (select max(ASS2.effective_start_date)
2129 from per_all_assignments_f ASS2
2130 where ASS2.assignment_id = ASS.assignment_id)
2131 and PEO.effective_start_date = (select max(PEO2.effective_start_date)
2132 from per_all_people_f PEO2
2133 where PEO2.person_id = PEO.person_id)
2134 )
2135 or
2136 ((p_info.action_type = 'Z' or p_info.action_type = 'PRU')
2137 and ASS.effective_start_date = (select max(ASS2.effective_start_date)
2138 from per_all_assignments_f ASS2
2139 where ASS2.assignment_id = ASS.assignment_id
2140 and ASS2.effective_start_date <= p_info.action_date)
2141 and PEO.effective_start_date = (select max(PEO2.effective_start_date)
2142 from per_all_people_f PEO2
2143 where PEO2.person_id = PEO.person_id
2144 and PEO2.effective_start_date <= p_info.action_date)
2145 )
2146 or
2147 (p_info.action_type = 'BEE'
2148 and ASS.effective_start_date = (select max(ASS2.effective_start_date)
2149 from per_all_assignments_f ASS2,
2150 pay_batch_lines pbl
2151 where ASS2.assignment_id = ASS.assignment_id
2152 and pbl.batch_id (+) = p_info.batch_id
2153 and pbl.assignment_id (+) = ASS.assignment_id
2154 and pbl.effective_Date (+) between ASS2.effective_start_date
2155 and ASS2.effective_end_date)
2156 and PEO.effective_start_date = (select max(PEO2.effective_start_date)
2157 from per_all_people_f PEO2,
2158 pay_batch_lines pbl
2159 where PEO2.person_id = PEO.person_id
2160 and PEO2.person_id = ASS.person_id
2161 and pbl.batch_id (+) = p_info.batch_id
2162 and pbl.assignment_id (+) = ASS.assignment_id
2163 and pbl.effective_Date (+) between PEO2.effective_start_date
2164 and PEO2.effective_end_date)
2165 )
2166 or
2167 (p_info.action_type not in ('BEE','Z','X', 'PRU')
2168 and p_info.action_date between
2169 ASS.effective_start_date and ASS.effective_end_date
2170 and p_info.action_date between
2171 PEO.effective_start_date and PEO.effective_end_date));
2172 end if;
2173 --
2174 hr_utility.set_location(c_indent, 50);
2175 --
2176 -- for Purge, we simply wish to confirm that we are not
2177 -- attempting to rollback an assignment action that has
2178 -- a 'C' secondary status. If everything ok, we simpy
2179 -- exit this procedure.
2180 if(p_info.action_type = 'Z') then
2181 if(l_sec_status = 'C') then
2182 hr_utility.set_message(801, 'PAY_289118_PUR_NACT_ROLLBACK');
2183 hr_utility.raise_error;
2184 end if;
2185 --
2186 if g_debug then
2187 hr_utility.trace('Purge act : exit');
2188 end if;
2189 return;
2190 end if;
2191 --
2192 -- can only retry if already complete
2193 if(p_info.rollback_mode = 'RETRY' and l_action_status not in ('C', 'S'))
2194 then
2195 hr_utility.set_message (801, 'HR_7506_ACTION_RET_NOT_COMP');
2196 hr_utility.set_message_token ('ASG_NO', p_info.assignment_number);
2197 hr_utility.raise_error;
2198 end if;
2199 --
2200 --
2201 --
2202 -- If rolling back or retrying, we need to know if assignments
2203 -- can be considered in isolation (as prescribed by the
2204 -- independent time periods flag for this legislation). Assignments
2205 -- with no Payroll are independent.
2206 --
2207 -- Operation is disallowed if this is a sequenced action AND there
2208 -- exists any sequenced actions in the future. Also disallowed
2209 -- if any child action exists (e.g can't rollback a run if already
2210 -- costed).
2211 -- Note - exception is if are attempting to roll back Reversal or
2212 -- Balance Adjustment actions, where we do not bother to perform
2213 -- the future actions check.
2214 --
2215 declare
2216 dummy number;
2217 begin
2218 -- For either legislation, examine the assignment action
2219 -- to see if it is locked by another action. Peform
2220 -- slightly different checks for RETRY and ROLLBACK
2221 -- modes. See comments below.
2222 if(p_info.rollback_mode = 'RETRY')
2223 then
2224 -- Case for RETRY mode.
2225 -- Check that the assignment action we are attempting to
2226 -- mark for retry is not locked by an assignment action
2227 -- that has an action_status that is not mark for retry.
2228 --
2229 -- Bug 1923535. If the locking action is a Payments Process and has
2230 -- been 'Marked for Retry' then the locked action cannot be
2231 -- 'Marked for Retry'.
2232 --
2233 if g_debug then
2234 hr_utility.set_location(c_indent, 20);
2235 end if;
2236 select null
2237 into dummy
2238 from dual
2239 where not exists (
2240 select null
2241 from pay_action_interlocks int,
2242 pay_assignment_actions act
2243 where int.locked_action_id = p_info.assact_id
2244 and act.assignment_action_id = int.locking_action_id
2245 and ((exists
2246 (select null
2247 from pay_payroll_actions pac
2248 where pac.payroll_action_id = act.payroll_action_id
2249 and action_type in ('A','H','M', 'PP', 'PRU')
2250 and act.action_status = 'M'
2251 )
2252 )
2253 or act.action_status <> 'M'
2254 ));
2255 else
2256 -- Case for ROLLBACK mode.
2257 -- Check that the assignment action we are attempting to
2258 -- roll back is not locked by an assignment action.
2259 if g_debug then
2260 hr_utility.set_location(c_indent, 30);
2261 hr_utility.trace('Case for ROLLBACK mode');
2262 hr_utility.trace('before checking for interlocking in action interlocks');
2263 end if;
2264 select null
2265 into dummy
2266 from dual
2267 where not exists (
2268 select null
2269 from pay_action_interlocks int
2270 where int.locked_action_id = p_info.assact_id);
2271 end if;
2272 --
2273 -- Now, the following checks are only applicable to sequenced
2274 -- actions, excluding Balance Adjustment and Reversal. These
2275 -- are special cases.
2276 if (p_info.sequenced_flag and
2277 (p_info.action_type <> 'B' and p_info.action_type <> 'I'
2278 and p_info.action_type <> 'V'))
2279 then
2280 -- Check the legislation case.
2281 if(p_info.independent_periods_flag = 'Y')
2282 then
2283 -- Check for other actions on this ASSIGNMENT
2284 -- Perform different checks for RETRY or ROLLBACK.
2285 -- We deal with both 'RETRY' and 'ROLLBACK' (BACKPAY) cases.
2286 --
2287 -- For RETRY:
2288 -- disallow mark for retry assignment action if there are
2289 -- future SEQUENCED assignment actions for the assignment
2290 -- that are not marked for retry. (Nested mark for retry).
2291 --
2292 -- For ROLLBACK (and BACKPAY):
2293 -- disallow rollback assignment action if there are
2294 -- future SEQUENCED assignment actions for the assignment.
2295 if g_debug then
2296 hr_utility.trace('The p_info.independent_periods_flag is Y');
2297 hr_utility.set_location(c_indent, 40);
2298 end if;
2299 --
2300 if (p_info.rollback_mode = 'RETRY')
2301 then
2302 select null into dummy
2303 from dual
2304 where not exists
2305 (select null
2306 from pay_assignment_actions ACT,
2307 pay_payroll_actions PACT,
2308 pay_action_classifications CLASS
2309 where ACT.assignment_id = p_info.assignment_id
2310 and ACT.action_sequence > l_action_sequence
2311 and ACT.action_status in ('C', 'S')
2312 and ACT.payroll_action_id = PACT.payroll_action_id
2313 and PACT.action_type = CLASS.action_type
2314 and CLASS.classification_name = 'SEQUENCED');
2315 else
2316 select null into dummy
2317 from dual
2318 where not exists
2319 (select null
2320 from pay_assignment_actions ACT,
2321 pay_payroll_actions PACT,
2322 pay_action_classifications CLASS
2323 where ACT.assignment_id = p_info.assignment_id
2324 and ACT.action_sequence > l_action_sequence
2325 and ACT.action_status in ('C', 'S','M')
2326 and ACT.payroll_action_id = PACT.payroll_action_id
2327 and PACT.action_type = CLASS.action_type
2328 and CLASS.classification_name = 'SEQUENCED'
2329 and NVL(PACT.legislative_parameters,'TEST') <> 'PAY_SIM');
2330 end if;
2331 --
2332 elsif (p_info.independent_periods_flag = 'G') then
2333 hr_utility.trace('The p_info.independent_periods_flag is G');
2334 --
2335 -- There are 2 Types of processes here. They are
2336 -- either processing at the assignment level but
2337 -- doing Group interlocking or are processing
2338 -- at the group level
2339 --
2340 if (p_info.object_type is not null
2341 and p_info.object_type = 'POG') then
2342 if (p_info.rollback_mode = 'RETRY')
2343 then
2344 select null into dummy
2345 from dual
2346 where not exists
2347 (select null
2348 from pay_action_classifications CLASS,
2349 pay_payroll_actions PACT,
2350 pay_assignment_actions ACT,
2351 pay_object_groups POG_ASG,
2352 pay_object_groups POG_PER,
2353 pay_assignment_actions PAA_RET
2354 where PAA_RET.assignment_action_id = p_info.assact_id
2355 and POG_PER.object_group_id = PAA_RET.object_id
2356 and POG_PER.source_type = 'PPF'
2357 and POG_ASG.parent_object_group_id = POG_PER.object_group_id
2358 and POG_ASG.source_type = 'PAF'
2359 and POG_ASG.source_id = ACT.assignment_id
2360 and ACT.action_sequence > l_action_sequence
2361 and ACT.action_status in ('C', 'S')
2362 and ACT.payroll_action_id = PACT.payroll_action_id
2363 and PACT.action_type = CLASS.action_type
2364 and CLASS.classification_name = 'SEQUENCED');
2365 else
2366 select null into dummy
2367 from dual
2368 where not exists
2369 (select null
2370 from pay_action_classifications CLASS,
2371 pay_payroll_actions PACT,
2372 pay_assignment_actions ACT,
2373 pay_object_groups POG_ASG,
2374 pay_object_groups POG_PER,
2375 pay_assignment_actions PAA_RET
2376 where PAA_RET.assignment_action_id = p_info.assact_id
2377 and POG_PER.object_group_id = PAA_RET.object_id
2378 and POG_PER.source_type = 'PPF'
2379 and POG_ASG.parent_object_group_id = POG_PER.object_group_id
2380 and POG_ASG.source_type = 'PAF'
2381 and POG_ASG.source_id = ACT.assignment_id
2382 and ACT.action_sequence > l_action_sequence
2383 and ACT.action_status in ('C', 'S', 'M')
2384 and ACT.payroll_action_id = PACT.payroll_action_id
2385 and PACT.action_type = CLASS.action_type
2386 and CLASS.classification_name = 'SEQUENCED'
2387 and NVL(PACT.legislative_parameters,'TEST') <> 'PAY_SIM');
2388 end if;
2389 else
2390 if (p_info.rollback_mode = 'RETRY')
2391 then
2392
2393 select null into dummy
2394 from dual
2395 where not exists
2396 (select null
2397 from pay_action_classifications CLASS,
2398 pay_payroll_actions PACT,
2399 pay_assignment_actions ACT,
2400 pay_object_groups POG_ASG,
2401 pay_object_groups POG_ASG2
2402 where POG_ASG.source_type = 'PAF'
2403 and POG_ASG2.source_type = 'PAF'
2404 and POG_ASG.source_id = p_info.assignment_id
2405 and POG_ASG.parent_object_group_id = POG_ASG2.parent_object_group_id
2406 and POG_ASG2.source_id = ACT.assignment_id
2407 and ACT.action_sequence > l_action_sequence
2408 and ACT.action_status in ('C', 'S')
2409 and ACT.payroll_action_id = PACT.payroll_action_id
2410 and PACT.action_type = CLASS.action_type
2411 and CLASS.classification_name = 'SEQUENCED');
2412 else
2413 select null into dummy
2414 from dual
2415 where not exists
2416 (select null
2417 from pay_action_classifications CLASS,
2418 pay_payroll_actions PACT,
2419 pay_assignment_actions ACT,
2420 pay_object_groups POG_ASG,
2421 pay_object_groups POG_ASG2
2422 where POG_ASG.source_type = 'PAF'
2423 and POG_ASG2.source_type = 'PAF'
2424 and POG_ASG.source_id = p_info.assignment_id
2425 and POG_ASG.parent_object_group_id = POG_ASG2.parent_object_group_id
2426 and POG_ASG2.source_id = ACT.assignment_id
2427 and ACT.action_sequence > l_action_sequence
2428 and ACT.action_status in ('C', 'S', 'M')
2429 and ACT.payroll_action_id = PACT.payroll_action_id
2430 and PACT.action_type = CLASS.action_type
2431 and CLASS.classification_name = 'SEQUENCED'
2432 and NVL(PACT.legislative_parameters,'TEST') <> 'PAY_SIM');
2433 end if;
2434 end if;
2435 --
2436 else
2437 -- check for other actions on this PERSON.
2438 if g_debug then
2439 hr_utility.set_location(c_indent, 50);
2440 hr_utility.trace('case where p_info.independent_periods_flag is N');
2441 end if;
2442 --
2443 --
2444 if (p_info.rollback_mode = 'RETRY')
2445 then
2446 select null into dummy
2447 from dual
2448 where not exists
2449 (select null
2450 from pay_action_classifications CLASS,
2451 pay_payroll_actions PACT,
2452 pay_assignment_actions ACT,
2453 per_all_assignments_f ASS,
2454 per_periods_of_service POS
2455 where POS.person_id = l_person_id
2456 and ASS.period_of_service_id = POS.period_of_service_id
2457 and ACT.assignment_id = ASS.assignment_id
2458 and ACT.action_sequence > l_action_sequence
2459 and ACT.action_status in ('C', 'S')
2460 and ACT.payroll_action_id = PACT.payroll_action_id
2461 and PACT.action_type = CLASS.action_type
2462 and CLASS.classification_name = 'SEQUENCED');
2463 else
2464 select null into dummy
2465 from dual
2466 where not exists
2467 (select null
2468 from pay_action_classifications CLASS,
2469 pay_payroll_actions PACT,
2470 pay_assignment_actions ACT,
2471 per_all_assignments_f ASS,
2472 per_periods_of_service POS
2473 where POS.person_id = l_person_id
2474 and ASS.period_of_service_id = POS.period_of_service_id
2475 and ACT.assignment_id = ASS.assignment_id
2476 and ACT.action_sequence > l_action_sequence
2477 and ACT.action_status in ('C', 'S', 'M')
2478 and ACT.payroll_action_id = PACT.payroll_action_id
2479 and PACT.action_type = CLASS.action_type
2480 and CLASS.classification_name = 'SEQUENCED'
2481 and NVL(PACT.legislative_parameters,'TEST') <> 'PAY_SIM');
2482 end if;
2483 --
2484 end if;
2485 end if;
2486 --
2487 -- When rolling back a void payment then ensure that the void is against the
2488 -- latest chequewriter run for the payment.
2489 --
2490 if p_info.action_type = 'D' then
2491 select null
2492 into dummy
2493 from dual
2494 where not exists (select null
2495 from
2496 pay_assignment_actions paac2,
2497 pay_assignment_actions paac,
2498 pay_action_interlocks pai
2499 where pai.locking_action_id = p_info.assact_id
2500 and pai.locked_action_id = paac.assignment_action_id
2501 and paac.pre_payment_id = paac2.pre_payment_id
2502 and paac2.action_sequence > paac.action_sequence);
2503 end if;
2504 --
2505 exception
2506 when no_data_found then
2507 -- Catch all interlock failure message.
2508 if p_info.legislation_code = 'GB' then
2509 hr_utility.set_message (801, 'HR_52975_ACTION_UNDO_INTLOK_GB');
2510 else
2511 hr_utility.set_message (801, 'HR_7507_ACTION_UNDO_INTLOCK');
2512 end if;
2513 hr_utility.raise_error;
2514 end;
2515 end val_assact_rollback;
2516 --
2517 /*
2518 * assignment level error handler. When we encounter
2519 * an assignment level error, we call this procedure.
2520 * This controls the counting of errors and writing
2521 * messages to the message lines table.
2522 */
2523 procedure assact_error(p_info in rollback_rec,
2524 error_code in number, error_message in varchar2) is
2525 c_indent varchar2(40);
2526 message_text pay_message_lines.line_text%type;
2527 begin
2528 if g_debug then
2529 c_indent := 'py_rollback_pkg.assact_error';
2530 hr_utility.set_location(c_indent, 10);
2531 end if;
2532 --
2533 -- handle the assignment action level error.
2534 -- get the message text to write. Need to get it
2535 -- in diffferent ways for oracle and user errors.
2536 if(error_code = hr_utility.hr_error_number)
2537 then
2538 -- specific exception raised.
2539 message_text := substrb(hr_utility.get_message, 1, 240);
2540 else
2541 -- oracle error.
2542 message_text := error_message;
2543 end if;
2544 --
2545 -- we can now insert the messge to message lines.
2546 if g_debug then
2547 hr_utility.set_location(c_indent, 10);
2548 end if;
2549 insert into pay_message_lines (
2550 line_sequence,
2551 payroll_id,
2552 message_level,
2553 source_id,
2554 source_type,
2555 line_text)
2556 values (pay_message_lines_s.nextval,
2557 p_info.payroll_id,
2558 'F', -- it's a fatal message.
2559 p_info.assact_id,
2560 'A', -- assignment action level.
2561 message_text);
2562 --
2563 pay_core_utils.push_message(801, null, message_text, 'F');
2564 --
2565 -- keep track of the number of errors
2566 -- fail if we have had too many.
2567 g_error_count := g_error_count + 1;
2568 --
2569 if(g_error_count > p_info.max_errors_allowed)
2570 then
2571 -- too many errors. we now abort with a message.
2572 -- commit any work we have already done if
2573 -- we are allowed to do so.
2574 if(p_info.dml_mode = 'FULL' or p_info.multi_thread)
2575 then
2576 commit;
2577 end if;
2578 --
2579 -- raise error to indicate entire process has failed.
2580 hr_utility.set_message (801, 'HR_7269_ASS_TOO_MANY_ERRORS');
2581 hr_utility.raise_error;
2582 end if;
2583 end assact_error;
2584 --
2585 /*
2586 * Lock and return a range row.
2587 * A range of ids are locked by first setting a database
2588 * lock and then updating the status to 'P', at which
2589 * point is 'belongs' to the thread and we can commit.
2590 * If there are no lockable rows, returns a null for
2591 * the chunk number to indicate end of processing.
2592 */
2593 function next_range(p_info in rollback_rec)
2594 return range_rec is
2595 norows boolean;
2596 range_info range_rec;
2597 c_indent varchar2(40);
2598 begin
2599 if g_debug then
2600 c_indent := 'py_rollback_pkg.next_range';
2601 hr_utility.set_location(c_indent, 1);
2602 end if;
2603 -- select a range row for update.
2604 begin
2605 if g_debug then
2606 hr_utility.set_location(c_indent, 2);
2607 end if;
2608 select rge.chunk_number,
2609 rge.starting_person_id,
2610 rge.ending_person_id
2611 into range_info
2612 from pay_population_ranges rge
2613 where rge.payroll_action_id = p_info.pact_id
2614 and rge.range_status = 'U'
2615 and rownum < 2
2616 for update of rge.chunk_number;
2617 --
2618 if g_debug then
2619 hr_utility.set_location(c_indent, 3);
2620 end if;
2621 -- If we reach here, we have a range row
2622 -- and we therefore wish to lock it.
2623 update pay_population_ranges pop
2624 set pop.range_status = 'P'
2625 where pop.payroll_action_id = p_info.pact_id
2626 and pop.chunk_number = range_info.chunk_number;
2627 --
2628 if g_debug then
2629 hr_utility.set_location(c_indent, 4);
2630 end if;
2631 -- Only commit if we are allowed to.
2632 if(p_info.multi_thread or p_info.dml_mode = 'FULL')
2633 then
2634 if g_debug then
2635 hr_utility.set_location(c_indent, 5);
2636 end if;
2637 commit;
2638 end if;
2639 exception
2640 when no_data_found then
2641 if g_debug then
2642 hr_utility.set_location(c_indent, 7);
2643 end if;
2644 range_info.chunk_number := null;
2645 end;
2646 --
2647 if g_debug then
2648 hr_utility.set_location(c_indent, 8);
2649 end if;
2650 return(range_info);
2651 end next_range;
2652 --
2653 procedure perform_act_rollback(p_info in out nocopy rollback_rec)
2654 is
2655 begin
2656 if g_debug then
2657 hr_utility.set_location ('perform_asg_rollback',10);
2658 end if;
2659 --
2660 -- set a savepoint in case we fail.
2661 savepoint before;
2662 --
2663 -- make checks for validity of rollback.
2664 val_assact_rollback(p_info); --
2665 --
2666 -- actually perform the rollback/mark for retry.
2667 do_assact_rollback(p_info);
2668 --
2669 if g_debug then
2670 hr_utility.set_location ('perform_asg_rollback',20);
2671 end if;
2672 -- if succeeded in processing, we reset the
2673 -- error counter, since we only wish to count
2674 -- consecutive errors.
2675 g_error_count := 0;
2676 --
2677 exception
2678 -- we may be reaching here due to failure in validation
2679 -- or because we have an unhandled exception (oracle error).
2680 -- in both cases we attempt to write the message text to
2681 -- message lines before continuing. This is done up to
2682 -- the error limit, then we exit.
2683 when others then
2684 if g_debug then
2685 hr_utility.set_location ('perform_asg_rollback',30);
2686 end if;
2687 rollback to savepoint before; -- throw away any work.
2688 if(p_info.all_or_nothing)
2689 then
2690 -- fail immediately for this case.
2691 raise;
2692 else
2693 assact_error(p_info, sqlcode, sqlerrm);
2694 end if;
2695 end;
2696 /*
2697 * Performs the dml for rolling back or
2698 * marking for retry assignment actions.
2699 */
2700 procedure assact_dml(p_info in out nocopy rollback_rec) is
2701 --
2702 range_info range_rec;
2703 --
2704 /*
2705 * Notice we outer join to period of service to
2706 * allow locking when we have a row. We will not have
2707 * a period of service for benefit assignments.
2708 */
2709 cursor c1 is
2710 select act.assignment_action_id, -- Bug 6834017 ,Removed ordered hint
2711 asg.period_of_service_id
2712 from per_all_assignments_f asg,
2713 pay_assignment_actions act
2714 where act.payroll_action_id = p_info.pact_id
2715 and asg.assignment_id = act.assignment_id
2716 and act.source_action_id is null
2717 and ((p_info.action_type = 'X'
2718 and asg.effective_start_date = (select max(asg2.effective_start_date)
2719 from per_all_assignments_f asg2
2720 where asg2.assignment_id =
2721 asg.assignment_id)
2722 )
2723 or
2724 ((p_info.action_type = 'PRU' or p_info.action_type = 'Z')
2725 and asg.effective_start_date = (select max(asg2.effective_start_date)
2726 from per_all_assignments_f asg2
2727 where asg2.assignment_id =
2728 asg.assignment_id
2729 and asg2.effective_start_date <=
2730 p_info.action_date)
2731 )
2732 /* or
2733 (p_info.action_type = 'BEE'
2734 and asg.effective_start_date = (select max(ASS2.effective_start_date)
2735 from per_all_assignments_f ASS2,
2736 pay_batch_lines pbl
2737 where ASS2.assignment_id = asg.assignment_id
2738 and pbl.batch_id (+) = p_info.batch_id
2739 and pbl.assignment_id (+) = asg.assignment_id
2740 and pbl.effective_Date (+) between ASS2.effective_start_date
2741 and ASS2.effective_end_date))
2742 */ or
2743 (p_info.action_type not in ('BEE','Z','X')
2744 and p_info.action_date between
2745 asg.effective_start_date and asg.effective_end_date))
2746 and asg.person_id between
2747 range_info.starting_person and range_info.ending_person
2748 for update of act.action_status,
2749 asg.assignment_id
2750 order by act.action_sequence desc;
2751 --
2752 cursor c1_bee is
2753 select act.assignment_action_id, -- Bug 6834017 ,Removed ordered hint
2754 asg.period_of_service_id
2755 from per_all_assignments_f asg,
2756 pay_assignment_actions act
2757 where act.payroll_action_id = p_info.pact_id
2758 and asg.assignment_id = act.assignment_id
2759 and act.source_action_id is null
2760 and p_info.action_type = 'BEE'
2761 and asg.effective_start_date = (select max(ASS2.effective_start_date)
2762 from per_all_assignments_f ASS2,
2763 pay_batch_lines pbl
2764 where ASS2.assignment_id = asg.assignment_id
2765 and pbl.batch_id (+) = p_info.batch_id
2766 and pbl.assignment_id (+) = asg.assignment_id
2767 and pbl.effective_Date (+) between ASS2.effective_start_date
2768 and ASS2.effective_end_date)
2769 for update of act.action_status,
2770 asg.assignment_id
2771 order by act.action_sequence desc;
2772 --
2773 cursor c2 is
2774 select act.assignment_action_id
2775 from pay_assignment_actions act
2776 where act.payroll_action_id = p_info.pact_id
2777 and act.source_action_id is null
2778 and act.object_id between
2779 range_info.starting_person and range_info.ending_person
2780 for update of act.action_status
2781 order by act.action_sequence desc;
2782 --
2783 cursor c3(c_period_of_service_id number) is
2784 select pos.period_of_service_id
2785 from per_periods_of_service pos
2786 where pos.period_of_service_id = c_period_of_service_id
2787 for update of pos.period_of_service_id;
2788 --
2789 commit_limit number;
2790 begin
2791 if g_debug then
2792 hr_utility.set_location ('assact_dml',1);
2793 end if;
2794 -- Attempt to get a range to process.
2795 range_info := next_range(p_info);
2796 --
2797 if g_debug then
2798 if g_debug then
2799 hr_utility.trace('max_single_undo = ' || p_info.max_single_undo);
2800 hr_utility.trace('max_errors_allowed = ' || p_info.max_errors_allowed);
2801 hr_utility.set_location ('assact_dml',2);
2802 end if;
2803 end if;
2804 -- Continue to process a chunk at a time,
2805 -- as long as we can lock a range row.
2806 while(range_info.chunk_number is not null)
2807 loop
2808 hr_utility.set_location ('assact_dml',3);
2809 --
2810 /* By default it must be an assignment action */
2811 if (p_info.object_type is null) then
2812 if(p_info.action_type <> 'BEE') then
2813 for c1rec in c1 loop
2814 p_info.assact_id := c1rec.assignment_action_id;
2815 if c1rec.period_of_service_id is not null then
2816 for c3rec in c3(c1rec.period_of_service_id) loop
2817 null; -- Locking Period of service.
2818 end loop;
2819 end if;
2820 perform_act_rollback(p_info);
2821 end loop; -- assact loop.
2822 else
2823 for c1rec in c1_bee loop
2824 p_info.assact_id := c1rec.assignment_action_id;
2825 if c1rec.period_of_service_id is not null then
2826 for c3rec in c3(c1rec.period_of_service_id) loop
2827 null; -- Locking Period of service.
2828 end loop;
2829 end if;
2830 perform_act_rollback(p_info);
2831 end loop; -- assact loop.
2832 end if;
2833 else
2834 /* OK, it must be an object action */
2835 for c2rec in c2 loop
2836 p_info.assact_id := c2rec.assignment_action_id;
2837 perform_act_rollback(p_info);
2838 end loop; -- objact loop.
2839 end if;
2840 --
2841 -- this range row is finished with.
2842 if g_debug then
2843 hr_utility.set_location('assact_dml', 20);
2844 end if;
2845 delete from pay_population_ranges range
2846 where range.payroll_action_id = p_info.pact_id
2847 and range.chunk_number = range_info.chunk_number;
2848 --
2849 if g_debug then
2850 hr_utility.set_location ('assact_dml',10);
2851 end if;
2852 -- commit our processing, but only if we are
2853 -- mult-threading and dml_mode is appropriate.
2854 -- reset the commit limit to zero.
2855 if(p_info.multi_thread or p_info.dml_mode = 'FULL')
2856 then
2857 commit;
2858 commit_limit := 0;
2859 end if;
2860 --
2861 if g_debug then
2862 hr_utility.set_location ('assact_dml',11);
2863 end if;
2864 -- Attempt to lock another range.
2865 range_info := next_range(p_info);
2866 end loop;
2867 --
2868 if g_debug then
2869 hr_utility.set_location ('assact_dml',12);
2870 end if;
2871 end assact_dml;
2872 -- --
2873 /*
2874 * validates rules that control what type of
2875 * actions we can perform a rollback or mark
2876 * for retry payroll action on.
2877 */
2878 procedure val_pact_rr_rules (p_info in rollback_rec)
2879 is
2880 dummy NUMBER;
2881 begin
2882 if g_debug then
2883 hr_utility.set_location('val_pact_rr_rules', 10);
2884 end if;
2885 --
2886 if(p_info.rollback_mode = 'RETRY')
2887 then
2888 if(p_info.action_type in ( 'E', 'H', 'D', 'PP'))
2889 then
2890 hr_utility.set_message(801, 'HR_7093_ACTION_CANT_RETPAY');
2891 hr_utility.set_message_token('ACTION_NAME', p_info.action_name);
2892 hr_utility.raise_error;
2893 end if;
2894 end if;
2895 --
2896 -- Special check for Purge for both modes.
2897 -- We are not allowed to rollback or mark for retry an entire
2898 -- purge payroll action if there are any assignment actions
2899 -- with secondary_status = 'C'.
2900 if (p_info.action_type = 'Z') then
2901 declare
2902 l_complete number;
2903 begin
2904 select count(*)
2905 into l_complete
2906 from pay_assignment_actions act
2907 where act.payroll_action_id = p_info.pact_id
2908 and act.secondary_status = 'C';
2909 --
2910 if(l_complete > 0) then
2911 hr_utility.set_message(801, 'PAY_289020_PUR_CANT_ROLLBACK');
2912 hr_utility.raise_error;
2913 end if;
2914 end;
2915 end if;
2916 end val_pact_rr_rules;
2917 --
2918 --
2919 /*
2920 * checks if the process that is being checked for retry has a dependant, or
2921 * succeeding, process that is not in a status of complete.
2922 */
2923 procedure val_dependant (p_info in rollback_rec)
2924 is
2925 w_payroll_id_1 number(9);
2926
2927 cursor chk_dep is
2928 select distinct pac.payroll_action_id
2929 from pay_assignment_actions act2,
2930 pay_action_interlocks pai,
2931 pay_assignment_actions act,
2932 pay_payroll_actions pac,
2933 pay_payroll_actions pac2
2934 where pac2.payroll_action_id = p_info.pact_id
2935 and act2.payroll_action_id = pac2.payroll_action_id
2936 and pac.payroll_action_id = act.payroll_action_id
2937 and pai.locking_action_id = act.assignment_action_id
2938 and pai.locked_action_id = act2.assignment_action_id
2939 and pac.action_status <> 'C'
2940 and pac.action_type in ('A', 'H', 'M', 'PP');
2941
2942 begin
2943 if g_debug then
2944 hr_utility.set_location('val_dependant', 18);
2945 end if;
2946
2947 open chk_dep;
2948 fetch chk_dep into w_payroll_id_1;
2949
2950 if chk_dep%found
2951 then
2952 if p_info.legislation_code = 'GB' then
2953 hr_utility.set_message (801, 'HR_52975_ACTION_UNDO_INTLOK_GB');
2954 else
2955 hr_utility.set_message (801, 'HR_7507_ACTION_UNDO_INTLOCK');
2956 end if;
2957 hr_utility.raise_error;
2958 end if;
2959
2960 close chk_dep;
2961 --
2962 end val_dependant ;
2963 --
2964 /*
2965 * validates rules that control what type of actions we
2966 * can perform a rollback/mark for retry assignment action on.
2967 */
2968 procedure val_assact_rr_rules (p_info in rollback_rec)
2969 is
2970 begin
2971 -- Validate the rollback and mark for retry rules for
2972 -- assignment actions.
2973 if g_debug then
2974 hr_utility.set_location('val_assact_rr_rules', 10);
2975 end if;
2976 --
2977 if(p_info.rollback_mode = 'RETRY')
2978 then
2979 if(p_info.action_type in ( 'PRU', 'E', 'M', 'H', 'D', 'PP') or
2980 (p_info.action_type = 'T' and p_info.sub_ledger_acc = 'N'))
2981 then
2982 hr_utility.set_message(801, 'HR_7508_ACTION_ACT_RR_RULE');
2983 hr_utility.set_message_token('ACTION_NAME', p_info.action_name);
2984 hr_utility.raise_error;
2985 end if;
2986 else
2987 -- !!!! note - temporary change for RN project.
2988 -- allow rollback of individual Magnetic Transfer assact.
2989 if(p_info.action_type in ('Q', 'U', 'E', 'PRU') or
2990 (p_info.action_type = 'T' and p_info.sub_ledger_acc = 'N'))
2991 then
2992 hr_utility.set_message(801, 'HR_7508_ACTION_ACT_RR_RULE');
2993 hr_utility.set_message_token('ACTION_NAME', p_info.action_name);
2994 hr_utility.raise_error;
2995 end if;
2996 end if;
2997 end val_assact_rr_rules;
2998 --
2999 /*
3000 * validate the parameters passed to the rollback
3001 * assignment and payroll action procedures.
3002 */
3003 procedure val_params (p_info in rollback_rec)
3004 is
3005 begin
3006 -- where applicable, check that parameters
3007 -- have reasonable values.
3008 if g_debug then
3009 hr_utility.set_location('val_params', 10);
3010 end if;
3011 if(p_info.rollback_mode not in ('RETRY', 'ROLLBACK', 'BACKPAY'))
3012 then
3013 hr_utility.set_message(801, 'HR_7000_ACTION_BAD_ROLL_MODE');
3014 hr_utility.raise_error;
3015 end if;
3016 --
3017 if(p_info.dml_mode not in ('FULL', 'NO_COMMIT', 'NONE'))
3018 then
3019 hr_utility.set_message(801, 'HR_7509_ACTION_BAD_DML_MODE');
3020 hr_utility.raise_error;
3021 end if;
3022 --
3023 -- certain values are illegal if they are combined.
3024 -- note that these checks are only applicable to
3025 -- the payroll action rollback level.
3026 if(p_info.rollback_level = 'P')
3027 then
3028 null;
3029 end if;
3030 end val_params;
3031 --
3032 /*
3033 * if the rollback payroll action is being called in
3034 * single threaded mode, we need to insert a range
3035 * row. This allows the processing to have the same
3036 * interface for both multi and single-thread modes.
3037 * NOTE: no date track restriction is required for
3038 * this statement, as we obtaining min and max
3039 * values. This happens to be convenient for Purge.
3040 */
3041 procedure single_thread_range(p_info in rollback_rec)
3042 is
3043 l_payroll_action_id number;
3044 begin
3045 l_payroll_action_id := p_info.pact_id;
3046 -- ok, we are single-threading. Need to remove any existing
3047 -- range rows (thought there are unlikely to be any), and
3048 -- then insert a special row.
3049 if g_debug then
3050 hr_utility.set_location('single_thread_range', 10);
3051 end if;
3052 delete from pay_population_ranges range
3053 where range.payroll_action_id = l_payroll_action_id;
3054 --
3055 if g_debug then
3056 hr_utility.set_location('single_thread_range', 20);
3057 end if;
3058
3059 if (p_info.object_type is null) then
3060 insert into pay_population_ranges (
3061 payroll_action_id,
3062 chunk_number,
3063 starting_person_id,
3064 ending_person_id,
3065 range_status)
3066 select /*+ USE_NL(asg)
3067 INDEX(asg PER_ASSIGNMENTS_F_PK) */
3068 pac.payroll_action_id,
3069 1,
3070 min(asg.person_id),
3071 max(asg.person_id),
3072 'U'
3073 from pay_payroll_actions pac,
3074 pay_assignment_actions act,
3075 per_all_assignments_f asg
3076 where pac.payroll_action_id = l_payroll_action_id
3077 and act.payroll_action_id = pac.payroll_action_id
3078 and asg.assignment_id = act.assignment_id
3079 group by pac.payroll_action_id;
3080 else
3081 insert into pay_population_ranges (
3082 payroll_action_id,
3083 chunk_number,
3084 starting_person_id,
3085 ending_person_id,
3086 range_status)
3087 select pac.payroll_action_id,
3088 1,
3089 min(act.object_id),
3090 max(act.object_id),
3091 'U'
3092 from pay_payroll_actions pac,
3093 pay_assignment_actions act
3094 where pac.payroll_action_id = l_payroll_action_id
3095 and act.payroll_action_id = pac.payroll_action_id
3096 group by pac.payroll_action_id;
3097 end if;
3098 --
3099 if g_debug then
3100 hr_utility.set_location('single_thread_range', 30);
3101 end if;
3102 end single_thread_range;
3103 --
3104 /*
3105 * this is called when we are rolling back. We need to know
3106 * whether or not the assignment actions have all been
3107 * deleted, otherwise we do not wish to remove the payroll
3108 * action.
3109 */
3110 function rollback_complete(p_payroll_action_id in number)
3111 return boolean is
3112 dummy number;
3113 begin
3114 select null
3115 into dummy
3116 from sys.dual
3117 where exists (
3118 select null
3119 from pay_assignment_actions act
3120 where act.payroll_action_id = p_payroll_action_id);
3121 --
3122 -- There are still assignment actions.
3123 return(false);
3124 --
3125 exception
3126 when no_data_found then
3127 -- There are no longer assignment actions.
3128 -- the rollback is considered complete.
3129 return(true);
3130 end rollback_complete;
3131 --
3132 /*
3133 * If we are limiting the dml that can be performed, this procedure
3134 * is called to ensure that we do not breach the limit.
3135 * This should only occur when the rollback procedure is called
3136 * from a forms session. In this case, user is advised to launch
3137 * a Rollback process from SRS.
3138 */
3139 procedure val_limit_dml(p_info in rollback_rec) is
3140 action_count number;
3141 begin
3142 select count(*)
3143 into action_count
3144 from pay_assignment_actions act
3145 where act.payroll_action_id = p_info.pact_id
3146 and rownum < (p_info.max_single_undo + 2);
3147 --
3148 if(action_count > p_info.max_single_undo) then
3149 hr_utility.set_message(801, 'HR_7722_ACTION_COMMIT_LIMIT');
3150 hr_utility.set_message_token('COMMIT_LIMIT',p_info.max_single_undo);
3151 hr_utility.raise_error;
3152 end if;
3153 end val_limit_dml;
3154 --
3155 /*
3156 * Perform the rolling back or Marking for Retry of a Payroll Action.
3157 * Can also be used for validation that such an action is
3158 * permissible. For the use and meaning of the parameters, please
3159 * refer to the package header.
3160 */
3161 procedure rollback_payroll_action
3162 (
3163 p_payroll_action_id in number,
3164 p_rollback_mode in varchar2 default 'ROLLBACK',
3165 p_leave_base_table_row in boolean default false,
3166 p_all_or_nothing in boolean default true,
3167 p_dml_mode in varchar2 default 'NO_COMMIT',
3168 p_multi_thread in boolean default false,
3169 p_limit_dml in boolean default false,
3170 p_grp_multi_thread in boolean default false
3171 ) is
3172 info rollback_rec; -- 'global' information.
3173 c_indent varchar2(40);
3174 l_date_earned date;
3175 begin
3176 g_debug := hr_utility.debug_enabled;
3177 if g_debug then
3178 c_indent := 'py_rollback_pkg.rollback_payroll_action';
3179 hr_utility.set_location(c_indent, 5);
3180 end if;
3181 -- set up the parameters.
3182 info.rollback_mode := p_rollback_mode;
3183 info.rollback_level := 'P'; -- processing entire Payroll Action.
3184 info.leave_row := p_leave_base_table_row;
3185 info.all_or_nothing := p_all_or_nothing;
3186 info.multi_thread := p_multi_thread;
3187 info.grp_multi_thread := p_grp_multi_thread;
3188 info.dml_mode := p_dml_mode;
3189 info.limit_dml := p_limit_dml;
3190 info.pact_id := p_payroll_action_id;
3191 --
3192 -- Set the Continuous Calc override flag, so that the trigger points
3193 -- are not fired.
3194 pay_continuous_calc.g_override_cc := TRUE;
3195 --
3196 -- Ensure delete from gl_interface in remove_gl_entries on
3197 -- each execution.
3198 mtgl_mode_cached := FALSE;
3199 --
3200 -- how many errors can we stand, what commit limit do we have?
3201 info.max_errors_allowed := action_parameter('MAX_ERRORS_ALLOWED');
3202 info.max_single_undo := action_parameter('MAX_SINGLE_UNDO');
3203 info.set_date_earned := action_parameter('SET_DATE_EARNED');
3204 --
3205 -- May wish to limit number of actions that can
3206 -- be rolled back. This is mainly for forms.
3207 if(info.limit_dml and not info.multi_thread) then
3208 val_limit_dml(info);
3209 end if;
3210 --
3211 get_pact_info(info); -- get payroll action level information.
3212 --
3213 info.retro_purge := legislation_rule(info.legislation_code, 'RETRO_DELETE');
3214 pay_core_utils.get_upgrade_status(info.bg_id,
3215 'SINGLE_BAL_TABLE',
3216 info.single_bal_table);
3217 --
3218 val_params(info); -- validate parameters passed in.
3219
3220 if(info.rollback_mode = 'RETRY' and info.action_status in ('C', 'S'))
3221 then
3222 val_dependant(info); -- does the payroll action have an uncompleted
3223 -- dependant payroll action?
3224 end if;
3225
3226 val_pact_rr_rules(info); -- can we rollback the payroll action.
3227 --
3228 if(not info.multi_thread) then
3229 -- single threading, insert special range row.
3230 single_thread_range(info);
3231 end if;
3232 --
3233 -- Depending on the dml mode, we may wish to
3234 -- set a savepoint.
3235 if(info.dml_mode = 'NONE') then
3236 if g_debug then
3237 hr_utility.set_location(c_indent, 10);
3238 end if;
3239 savepoint no_dml;
3240 end if;
3241 --
3242 if (info.action_type = 'PRU') then
3243 remove_pact_payment(info.pact_id);
3244 end if;
3245 --
3246 assact_dml(info); -- do the rollback of assact rows.
3247 --
3248 -- delete archive_items for the context of payroll_action_id
3249 --
3250 if g_debug then
3251 hr_utility.set_location(c_indent, 12);
3252 end if;
3253 --
3254 -- Remove archiver items at the payroll action level
3255 if (info.action_type = 'X' or
3256 info.action_type = 'PP' or
3257 info.action_type = 'M') then
3258 remove_archive_items(info, info.pact_id, 'PA');
3259 remove_action_information(info.pact_id, 'PA');
3260 remove_file_details(info, info.pact_id, 'PPA');
3261 end if;
3262 --
3263 if g_debug then
3264 hr_utility.set_location(c_indent, 15);
3265 end if;
3266 --
3267 --
3268 -- delete group level run balances
3269 --
3270 if (info.sequenced_flag and
3271 rollback_complete(info.pact_id)) then
3272 delete from pay_run_balances
3273 where payroll_action_id = info.pact_id;
3274 end if;
3275 --
3276 if(info.dml_mode = 'NONE') then
3277 hr_utility.set_location(c_indent, 20);
3278 rollback to savepoint no_dml;
3279 end if;
3280 --
3281 -- we insert a message to indicate that the rollback was successful.
3282 -- Note that we perform this whether or not we are going to actually
3283 -- delete the payroll action row, as we assume that the caller
3284 -- will perform this action, even if we do not. Also note that it is
3285 -- only done if we have rolled back all assignment actions.
3286 -- Finally, if we are multi-threading, we wish to leave the process
3287 -- to insert the message.
3288 if(info.rollback_mode = 'ROLLBACK' and
3289 rollback_complete(info.pact_id) and
3290 not info.multi_thread)
3291 then
3292 remove_messages(info, 'P');
3293 -- Only insert message if the action is not an
3294 -- initial balance upload.
3295 if (info.action_type <> 'I') then
3296 ins_rollback_message(info, 'P');
3297 end if;
3298 end if;
3299 --
3300 -- now if the rollback is successful then we update the bee batch.
3301 if (rollback_complete(info.pact_id) and info.action_type ='BEE') then
3302 pay_mix_rollback_pkg.set_status(info.pact_id,info.leave_row);
3303 end if;
3304 --
3305 -- now we perform any action that we require (and
3306 -- are allowed to perform) on the payroll action
3307 -- row itself. Normally, we would not wish to touch
3308 -- the row if the client was a form.
3309 if(not info.leave_row and rollback_complete(info.pact_id))
3310 then
3311 -- delete the payroll action row if we are told to.
3312 if g_debug then
3313 hr_utility.set_location(c_indent, 30);
3314 end if;
3315 delete from pay_chunk_status
3316 where payroll_action_id = info.pact_id;
3317 delete from pay_payroll_actions pac
3318 where pac.payroll_action_id = info.pact_id;
3319 else
3320 if (info.sequenced_flag=FALSE) then
3321 if (info.rollback_mode <> 'BACKPAY'
3322 and info.rollback_mode <> 'RETRY'
3323 and info.action_type <> 'BEE') then
3324 if info.rollback_level = 'P' then
3325 if info.set_date_earned = 'Y' then
3326 select max(date_earned)
3327 into l_date_earned
3328 from pay_payroll_actions locked_pact,
3329 pay_assignment_actions locked,
3330 pay_assignment_actions locking,
3331 pay_action_interlocks locks
3332 where locking.payroll_action_id =info.pact_id
3333 and locking.assignment_action_id =locks.locking_action_id
3334 and locked.assignment_action_id =locks.locked_action_id
3335 and locked.payroll_action_id =locked_pact.payroll_action_id;
3336 --
3337 update pay_payroll_actions pac
3338 set pac.date_earned = l_date_earned
3339 where pac.payroll_action_id = info.pact_id;
3340 end if;
3341 end if;
3342 end if;
3343 end if;
3344 end if;
3345 --
3346 -- decide if we wish to perform that final commit
3347 if(info.dml_mode = 'FULL')
3348 then
3349 if g_debug then
3350 hr_utility.set_location(c_indent, 40);
3351 end if;
3352 commit;
3353 end if;
3354 --
3355 pay_continuous_calc.g_override_cc := FALSE;
3356 --
3357 exception
3358 when others then
3359 pay_continuous_calc.g_override_cc := FALSE;
3360 raise;
3361 end rollback_payroll_action;
3362 --
3363 /*
3364 * Interface to rollback/mark for retry of an assignment action.
3365 * see the package header for details of the parameters.
3366 * Takes into account assignment level erroring.
3367 */
3368 procedure rollback_ass_action
3369 (
3370 p_assignment_action_id in number,
3371 p_rollback_mode in varchar2 default 'ROLLBACK',
3372 p_leave_base_table_row in boolean default false,
3373 p_all_or_nothing in boolean default true,
3374 p_dml_mode in varchar2 default 'NO_COMMIT',
3375 p_multi_thread in boolean default false,
3376 p_grp_multi_thread in boolean default false
3377 ) is
3378 info rollback_rec;
3379 c_indent varchar2(40);
3380 l_date_earned date;
3381 l_current_date_earned date;
3382 src_action_id number;
3383 --
3384 begin
3385 g_debug := hr_utility.debug_enabled;
3386 -- need to know the payroll action.
3387 if g_debug then
3388 c_indent := 'py_rollback_pkg.rollback_ass_action';
3389 hr_utility.set_location(c_indent, 10);
3390 end if;
3391 select act.payroll_action_id, act.source_action_id, act.object_type
3392 into info.pact_id, src_action_id, info.object_type
3393 from pay_assignment_actions act
3394 where act.assignment_action_id = p_assignment_action_id;
3395 --
3396 -- instantiate the other parameters that are relevant.
3397 info.assact_id := p_assignment_action_id;
3398 info.rollback_mode := p_rollback_mode;
3399 info.rollback_level := 'A';
3400 info.leave_row := p_leave_base_table_row;
3401 info.dml_mode := p_dml_mode;
3402 info.multi_thread := p_multi_thread;
3403 info.grp_multi_thread := p_grp_multi_thread;
3404 --
3405 -- Check that it is a master action.
3406 if src_action_id is not null then
3407 hr_utility.set_message(801, 'PAY_289114_RLBK_CHLD_ACT');
3408 hr_utility.raise_error;
3409 end if;
3410 --
3411 -- how many errors can we stand, what commit limit do we have?
3412 info.max_errors_allowed := action_parameter('MAX_ERRORS_ALLOWED');
3413 info.max_single_undo := action_parameter('MAX_SINGLE_UNDO');
3414 info.set_date_earned := action_parameter('SET_DATE_EARNED');
3415 --
3416 get_pact_info(info); -- get info about payroll action.
3417 --
3418 info.retro_purge := legislation_rule(info.legislation_code, 'RETRO_DELETE');
3419 pay_core_utils.get_upgrade_status(info.bg_id,
3420 'SINGLE_BAL_TABLE',
3421 info.single_bal_table);
3422 --
3423 val_params(info); -- parameter validation.
3424 --
3425 -- For BackPay, we do not care about normal
3426 -- rules about whether a single action of a
3427 -- particular action type can be rolled
3428 -- back or not.
3429 if(info.rollback_mode <> 'BACKPAY') then
3430 val_assact_rr_rules(info);
3431 else
3432 select assignment_id
3433 into info.assignment_id
3434 from pay_assignment_actions
3435 where assignment_action_id = info.assact_id;
3436 end if;
3437 --
3438 -- perform the rollback/mark for retry itself.
3439 -- from now on, errors are considered to be assignment action
3440 -- level. Depending on the mode, we may stop immediately.
3441 -- In backpay case, we do not wish to validate the rollback,
3442 -- simply wishing to perform it. This is because BackPay does
3443 -- only performs rollback on Reversals, Runs and QuickPays.
3444 begin
3445 --
3446 -- in case we fail.
3447 savepoint before;
3448 --
3449 -- Set the Continuous Calc override flag, so that the trigger points
3450 -- are not fired.
3451 pay_continuous_calc.g_override_cc := TRUE;
3452 --
3453 if(info.rollback_mode <> 'BACKPAY') then
3454 val_assact_rollback(info);
3455 end if;
3456 --
3457 -- Only perform dml if allowed to.
3458 if(info.dml_mode <> 'NONE') then
3459 do_assact_rollback(info);
3460 -- update date earned for payroll action
3461 if (info.sequenced_flag=FALSE) then
3462 if (info.rollback_mode <> 'BACKPAY'
3463 and info.rollback_mode <> 'RETRY'
3464 and info.action_type <> 'X'
3465 and info.action_type <> 'BEE') then
3466 if info.rollback_level = 'A' then
3467
3468 if info.set_date_earned = 'Y' then
3469 begin
3470 -- Handle that the rolled back assignment action
3471 -- may have been the last one
3472 select max(locked_pact.date_earned), locking_pact.date_earned
3473 into l_date_earned, l_current_date_earned
3474 from pay_payroll_actions locked_pact,
3475 pay_assignment_actions locked,
3476 pay_assignment_actions locking,
3477 pay_action_interlocks locks,
3478 pay_payroll_actions locking_pact
3479 where locking_pact.payroll_action_id =info.pact_id
3480 and locking.payroll_action_id =locking_pact.payroll_action_id
3481 and locking.assignment_action_id =locks.locking_action_id
3482 and locked.assignment_action_id =locks.locked_action_id
3483 and locked.payroll_action_id =locked_pact.payroll_action_id
3484 group by locking_pact.date_earned;
3485 --
3486 if (l_date_earned < l_current_date_earned) then
3487 update pay_payroll_actions pac
3488 set pac.date_earned = l_date_earned
3489 where pac.payroll_action_id = info.pact_id;
3490 end if;
3491 --
3492 exception
3493 when others then
3494 update pay_payroll_actions pac
3495 set pac.date_earned = null
3496 where pac.payroll_action_id = info.pact_id;
3497 end;
3498 end if;
3499 end if;
3500 end if;
3501 end if;
3502 --
3503 -- Remove the group run balances for resersal.
3504 --
3505 if (info.action_type = 'V') then
3506 --
3507 delete from pay_run_balances
3508 where payroll_action_id = info.pact_id;
3509 --
3510 end if;
3511 end if;
3512 g_error_count := 0; -- only count consecutive errors.
3513 pay_continuous_calc.g_override_cc := FALSE;
3514 exception
3515 -- Throw away any work we have done.
3516 when others then
3517 rollback to savepoint before;
3518 pay_continuous_calc.g_override_cc := FALSE;
3519 if(p_all_or_nothing) then
3520 raise;
3521 else
3522 assact_error(info, sqlcode, sqlerrm);
3523 end if;
3524 end;
3525 --
3526 -- we may wish to commit.
3527 if(info.dml_mode = 'FULL')
3528 then
3529 commit;
3530 end if;
3531 end rollback_ass_action;
3532 --
3533 --
3534 begin
3535 --
3536 -- Having the error counter as a package global and
3537 -- initialising it here allows the error counting
3538 -- mechanism to work whether we are rolling back a
3539 -- whole payroll action or assignment action by
3540 -- assignment action.
3541 g_error_count := 0;
3542 -- hr_utility.trace_on(null, 'ORACLE');
3543 end py_rollback_pkg;
[Home] [Help]