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