1 PACKAGE BODY pay_mix_rollback_pkg AS
2 /* $Header: pymixrbk.pkb 120.1 2006/11/29 12:29:29 susivasu noship $ */
3 --
4 -- type defs
5 --
6
7 type varchar2_table is table of varchar2(240)
8 index by binary_integer;
9
10 type varchar2_table2 is table of varchar2(1)
11 index by binary_integer;
12
13 type number_table is table of number
14 index by binary_integer;
15
16 --
17 -- global declarations
18 --
19
20 g_message_tbl varchar2_table;
21 g_message_level_tbl varchar2_table2;
22 g_message_id_tbl number_table;
23 g_message_count number := 0;
24 g_message_severity_tbl varchar2_table2;
25
26
27 --
28 -- undo_mix
29 --
30
31 procedure undo_mix(
32 p_errbuf out nocopy varchar2,
33 p_retcode out nocopy number,
34 p_batch_header_id in number,
35 p_commit_all_or_nothing in varchar2 default 'Y',
36 p_reject_if_run_results_exist in varchar2 default 'Y',
37 p_dml_mode in varchar2,
38 p_leave_batch in varchar2 default 'Y',
39 p_assignment_id in number default null,
40 p_asg_action_id in number default null
41 ) is
42
43 type batch_line is record
44 (
45 batch_line_id pay_batch_lines.batch_id%type,
46 assignment_id pay_batch_lines.assignment_id%type,
47 assignment_number pay_batch_lines.assignment_number%type,
48 element_type_id pay_batch_lines.element_type_id%type,
49 effective_date pay_batch_lines.effective_date%type,
50 effective_start_date pay_batch_lines.effective_start_date%type
51 );
52
53 l_business_group_id number;
54 l_check_batch_id varchar2(1) := 'N';
55 l_allow_rollback varchar2(30);
56 l_reject_ent_not_removed varchar2(30);
57 l_DATE_EFFECTIVE_CHANGES varchar2(30);
58 l_element_entry_id number;
59 l_creator_id number;
60 l_creator_type varchar2(1);
61 l_assignments_processed number := 0;
62 l_max_errors pay_action_parameters.parameter_value%type;
63 l_batch_line batch_line;
64 l_effective_session_date date;
65 l_errbuf varchar2(2000);
66 l_retcode number;
67
68 cursor csr_check_classification is
69 select 'Y'
70 from pay_element_types_f pet,
71 pay_element_classifications pec
72 where pet.element_type_id = l_batch_line.element_type_id
73 and pet.CLASSIFICATION_ID = pec.CLASSIFICATION_ID
74 and pet.PROCESSING_TYPE = 'R'
75 and pec.legislation_code is not null
76 and pec.CLASSIFICATION_name like 'EXTERNAL_REPORTING%'
77 and pec.legislation_code = 'GB';
78
79 l_ele_class_chk varchar2(1);
80
81 cursor c_batch_lines is
82 select pbl.batch_line_id,
83 pbl.assignment_id,
84 pbl.assignment_number,
85 pbl.element_type_id,
86 pbl.effective_date,
87 pbl.effective_start_date
88 from pay_batch_lines pbl
89 where pbl.batch_id = p_batch_header_id
90 and pbl.batch_line_status = 'T'
91 and (p_assignment_id is null or pbl.assignment_id = p_assignment_id)
92 union all
93 select to_number(null) batch_line_id,
94 to_number(null) assignment_id,
95 to_char(null) assignment_number,
96 to_number(null) element_type_id,
97 to_date(null) effective_date,
98 to_date(null) effective_start_date
99 from dual
100 where not exists
101 (select null
102 from pay_batch_headers pbh
103 where pbh.batch_id = p_batch_header_id);
104
105 cursor c_batch_entries (c_assignment_id number) is
106 select pee.element_entry_id, pee.creator_type,
107 pee.creator_id, pee.effective_start_date
108 from pay_element_entries_f pee,
109 pay_element_links_f pel,
110 pay_element_types_f pet
111 where pee.creator_id = p_batch_header_id
112 and pee.creator_type = 'H'
113 and (pee.source_id is null or pee.source_id = p_asg_action_id)
114 and pee.element_link_id = pel.element_link_id
115 and pel.element_type_id = l_batch_line.element_type_id
116 and pet.element_type_id = pel.element_type_id
117 and pee.assignment_id = c_assignment_id
118 and ((pet.processing_type = 'R'
119 and pee.effective_start_date = l_batch_line.effective_date)
120 or (pet.processing_type = 'N'
121 and l_batch_line.effective_date between pee.effective_start_date
122 and pee.effective_end_date))
123 and l_batch_line.effective_date between pel.effective_start_date
124 and pel.effective_end_date
125 and l_batch_line.effective_date between pet.effective_start_date
126 and pet.effective_end_date
127 and l_ele_class_chk is null
128 union all
129 select pee.element_entry_id, pee.creator_type,
130 pee.creator_id, pee.effective_start_date
131 from per_absence_attendances paa,
132 pay_element_entries_f pee,
133 pay_element_links_f pel,
134 pay_element_types_f pet
135 where paa.batch_id = p_batch_header_id
136 and pee.creator_id = paa.absence_attendance_id
137 and pee.creator_type = 'A'
138 and (pee.source_id is null or pee.source_id = p_asg_action_id)
139 and pee.element_link_id = pel.element_link_id
140 and pel.element_type_id = l_batch_line.element_type_id
141 and pet.element_type_id = pel.element_type_id
142 and pee.assignment_id = c_assignment_id
143 and l_batch_line.effective_date between pel.effective_start_date
144 and pel.effective_end_date
145 and l_batch_line.effective_date between pet.effective_start_date
146 and pet.effective_end_date
147 and l_ele_class_chk is null
148 union all
149 select pee.element_entry_id, pee.creator_type,
150 pee.creator_id, pee.effective_start_date
151 from pay_element_entries_f pee,
152 pay_element_links_f pel,
153 pay_element_types_f pet
154 where pee.creator_id = p_batch_header_id
155 and pee.creator_type = 'H'
156 and (pee.source_id is null or pee.source_id = p_asg_action_id)
157 and pee.element_link_id = pel.element_link_id
158 and pel.element_type_id = l_batch_line.element_type_id
159 and pet.element_type_id = pel.element_type_id
160 and pee.assignment_id = c_assignment_id
161 and pet.processing_type = 'R'
162 and ((l_batch_line.effective_start_date is not null and pee.effective_start_date=l_batch_line.effective_start_date)
163 or
164 (l_batch_line.effective_start_date is null and pee.effective_start_date=l_batch_line.effective_date))
165 and l_batch_line.effective_date between pel.effective_start_date
166 and pel.effective_end_date
167 and l_batch_line.effective_date between pet.effective_start_date
168 and pet.effective_end_date
169 and l_ele_class_chk is not null
170 union all
171 select pee.element_entry_id, pee.creator_type,
172 pee.creator_id, pee.effective_start_date
173 from pay_element_entries_f pee
174 where pee.creator_id = p_batch_header_id
175 and pee.creator_type = 'H'
176 and pee.source_id = p_asg_action_id
177 and pee.assignment_id = c_assignment_id
178 and pee.entry_type = 'E'
179 and l_batch_line.element_type_id is null
180 union all
181 select pee.element_entry_id, pee.creator_type,
182 pee.creator_id, pee.effective_start_date
183 from per_absence_attendances paa,
184 pay_element_entries_f pee
185 where paa.batch_id = p_batch_header_id
186 and pee.creator_id = paa.absence_attendance_id
187 and pee.creator_type = 'A'
188 and pee.source_id = p_asg_action_id
189 and pee.assignment_id = c_assignment_id
190 and pee.entry_type = 'E'
191 and l_batch_line.element_type_id is null;
192
193 cursor csr_control_lines (p_batch_id number) is
194 select pct.batch_control_id
195 from pay_batch_control_totals pct
196 where pct.batch_id = p_batch_id;
197
198 l_ctl_rec csr_control_lines%ROWTYPE;
199
200 cursor csr_pay_act_exists (p_batch_id number) is
201 select 'Y'
202 from pay_payroll_actions pact
203 where pact.batch_id = p_batch_id
204 and pact.action_type = 'BEE';
205
206 l_pay_act_exists varchar2(1) := 'N';
207
208 cursor csr_payroll_action_exists is
209 select pact.payroll_action_id,
210 pact.business_group_id
211 from pay_payroll_actions pact
212 where pact.batch_id = p_batch_header_id
213 and pact.action_type = 'BEE'
214 and pact.batch_process_mode = 'TRANSFER';
215
216 cursor csr_check_entry_modified (p_ee_id number, p_eff_date date) is
217 select 'Y'
218 from pay_element_entries_f pee
219 where pee.element_entry_id = p_ee_id
220 and p_eff_date between pee.effective_start_date
221 and pee.effective_end_date
222 and pee.creator_type in ('A','H')
223 and pee.creator_id is not null
224 and exists (select null
225 from pay_element_entries_f pee1
226 where pee.element_entry_id = pee1.element_entry_id
227 and (pee1.creator_type <> pee.creator_type
228 or pee1.creator_id <> pee.creator_id));
229
230 l_chk_entry_modified varchar2(1);
231 l_chk_rollback_upd varchar2(1);
232
233 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
234 l_assignment_id per_assignments_f.assignment_id%TYPE;
235 l_request_id number := 0;
236 l_entry_exists varchar2(1) := 'N';
237
238 begin
239
240 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix',10);
241
242 --
243 if p_asg_action_id is null then
244 l_payroll_action_id := null;
245 open csr_payroll_action_exists;
246 fetch csr_payroll_action_exists into l_payroll_action_id,l_business_group_id;
247 close csr_payroll_action_exists;
248 --
249 if l_payroll_action_id is not null then
250
251 l_request_id := pay_paywsqee_pkg.paylink_request_id(
252 p_business_group_id => l_business_group_id,
253 p_mode => 'ROLLBACK',
254 p_batch_id => p_batch_header_id,
255 p_wait => 'Y' );
256
257 open csr_payroll_action_exists;
258 fetch csr_payroll_action_exists into l_payroll_action_id,l_business_group_id;
259 if ( l_request_id = 0 or csr_payroll_action_exists%found) then
260 close csr_payroll_action_exists;
261 hr_utility.raise_error;
262 end if;
263 close csr_payroll_action_exists;
264
265 return;
266 end if;
267 end if;
268
269 --
270 SAVEPOINT RB;
271 --
272 -- No longer needed since this is supported within the
273 -- PYUGEN processes.
274 -- -- Get max_errors_allowed value
275 -- l_max_errors := action_parameter('MAX_ERRORS_ALLOWED');
276
277 begin
278
279 -- Ensure batch is valid for rollback
280 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix',20);
281
282 select 'Y'
283 into l_check_batch_id
284 from pay_batch_headers
285 where batch_id = p_batch_header_id
286 and batch_status = 'T';
287
288 -- Get business_group_id
289 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix',30);
290
291 select business_group_id,
292 nvl(REJECT_ENTRY_NOT_REMOVED,'N'),
293 nvl(ROLLBACK_ENTRY_UPDATES,'N'),
294 DATE_EFFECTIVE_CHANGES
295 into l_business_group_id,
296 l_reject_ent_not_removed,
297 l_allow_rollback,
298 l_DATE_EFFECTIVE_CHANGES
299 from pay_batch_headers
300 where batch_id = p_batch_header_id;
301
302 exception
303 when no_data_found then
304 l_check_batch_id := 'N';
305
306 end;
307
308 -- If no action id is passed and if payroll actions exits for this batch then
309 -- do not undo mix.
310 open csr_pay_act_exists(p_batch_header_id);
311 fetch csr_pay_act_exists into l_pay_act_exists;
312 if csr_pay_act_exists%found and p_asg_action_id is null then
313 --
314 close csr_pay_act_exists;
315 hr_utility.set_message(800,'HR_289717_BEE_CANNOT_ROLLBACK');
316 hr_utility.raise_error;
317 --
318 end if;
319 close csr_pay_act_exists;
320 --
321 -- Only purge header message if it is has been called by outside of the PYUGEN.
322 --
323 if p_asg_action_id is null then
324 -- First delete any messages relating to this batch from pay_message_lines
325 purge_rollback_messages(p_batch_header_id,'H');
326 end if;
327
328 if (l_check_batch_id = 'Y') or (l_check_batch_id = 'N' and p_asg_action_id is not null) then
329 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix',40);
330
331 open c_batch_lines;
332 fetch c_batch_lines into l_batch_line;
333
334 while c_batch_lines%found loop
335
336 purge_rollback_messages(l_batch_line.batch_line_id,'L');
337
338 open csr_check_classification;
339 fetch csr_check_classification into l_ele_class_chk;
340 close csr_check_classification;
341
342 if (l_batch_line.assignment_id is not null or p_assignment_id is not null) then
343 l_assignment_id := nvl(p_assignment_id,l_batch_line.assignment_id);
344 else
345
346 select assignment_id
347 into l_assignment_id
348 from per_assignments_f asg
349 where upper(asg.assignment_number) = upper(l_batch_line.assignment_number)
350 and asg.business_group_id = l_business_group_id
351 and ((l_batch_line.effective_start_date is not null
352 and l_batch_line.effective_start_date between asg.effective_start_date
353 and asg.effective_end_date)
354 or (l_batch_line.effective_start_date is null
355 and l_batch_line.effective_date between asg.effective_start_date
356 and asg.effective_end_date));
357 end if;
358
359 open c_batch_entries(l_assignment_id);
360 fetch c_batch_entries into l_element_entry_id, l_creator_type,
361 l_creator_id, l_effective_session_date;
362
363 l_entry_exists := 'N';
364 while c_batch_entries%found loop
365 --
366 --
367 -- Check the entry is modifed.
368 l_chk_rollback_upd := 'N';
369 open csr_check_entry_modified(l_element_entry_id,l_effective_session_date);
370 fetch csr_check_entry_modified into l_chk_entry_modified;
371 if csr_check_entry_modified%found then
372 l_chk_entry_modified := 'Y';
373 --
374 if (l_allow_rollback = 'Y' and l_date_effective_changes = 'U') then
375 l_chk_rollback_upd := 'Y';
376 end if;
377 --
378 else
379 l_chk_entry_modified := 'N';
380 end if;
384 --
381 close csr_check_entry_modified;
382 --
383 if (l_chk_entry_modified <> 'Y' or l_chk_rollback_upd='Y') then
385 -- If run results exist for the element entry, the user may want us
386 -- to error the line.
387 if p_reject_if_run_results_exist = 'Y'
388 and run_results_exist(l_element_entry_id,
389 l_effective_session_date,
390 l_chk_rollback_upd) then
391
392 -- MAx errros checks and commit all or nothing are
393 -- done at the payroll_action level.
394 --
395 -- if p_commit_all_or_nothing = 'Y' then
396 -- g_message_count := g_message_count + 1;
397 -- insert_rollback_message('L', l_batch_line.batch_line_id, 'F', false);
398 --
399 -- elsif p_commit_all_or_nothing = 'N'
400 -- and g_message_count >= fnd_number.canonical_to_number(l_max_errors) then
401 --
402 -- g_message_count := g_message_count + 1;
403 -- insert_rollback_message('L', l_batch_line.batch_line_id, 'F', false);
404 --
405 -- else
406 --
407 -- g_message_count := g_message_count + 1;
408 -- insert_rollback_message('L', l_batch_line.batch_line_id, 'I', false);
409 --
410 -- end if;
411 g_message_count := g_message_count + 1;
412 hr_utility.set_message(801,'PAY_52014_RUN_RESULTS_EXIST');
413 insert_rollback_message('L', l_batch_line.batch_line_id, 'F', false);
414
415 else
416
417 -- in the case of an absence remove the absence record
418 if (l_creator_type = 'A') then
419
420 delete from per_absence_attendances
421 where absence_attendance_id = l_creator_id;
422
423 end if;
424
425 -- remove entry
426 begin
427 l_entry_exists := 'Y';
428 --
429 if l_chk_rollback_upd = 'Y' then
430 hr_entry_api.delete_element_entry('DELETE_NEXT_CHANGE',
431 l_effective_session_date-1,
432 l_element_entry_id);
433 else
434 hr_entry_api.delete_element_entry('ZAP',
435 l_effective_session_date,
436 l_element_entry_id);
437 end if;
438 exception
439 when others then
440 commit_messages;
441 g_message_count := 0;
442 close c_batch_entries;
443 close c_batch_lines;
444 hr_utility.set_message(800,'PER_289522_CANNOT_RBK_BEE_LINE');
445 hr_utility.raise_error;
446 end;
447
448 -- change batch line status to 'unprocessed'
449 --
450 payplnk.g_payplnk_call := true;
451 --
452 update pay_batch_lines
453 set batch_line_status = 'U'
454 where batch_line_id = l_batch_line.batch_line_id;
455 --
456 payplnk.g_payplnk_call := false;
457
458 l_assignments_processed := l_assignments_processed + 1;
459
460 end if;
461
462 end if;
463
464 fetch c_batch_entries into l_element_entry_id, l_creator_type,
465 l_creator_id, l_effective_session_date;
466
467 end loop;
468
469 -- if no entries were found for batch line, reset status
470
471 if (c_batch_entries%notfound and l_entry_exists <> 'Y') then
472 if (l_check_batch_id = 'N' or (l_check_batch_id ='Y'
473 and l_reject_ent_not_removed <> 'Y')) then
474 --
475 payplnk.g_payplnk_call := true;
476 --
477 update pay_batch_lines
478 set batch_line_status = 'U'
479 where batch_line_id = l_batch_line.batch_line_id;
480 --
481 payplnk.g_payplnk_call := false;
482 --
483 else
484 --
485 commit_messages;
486 g_message_count := 0;
487 close c_batch_entries;
488 close c_batch_lines;
489 hr_utility.set_message(800,'PER_449031_CANNOT_RBK_BEE_ENR');
490 hr_utility.raise_error;
491 --
492 end if;
493 end if;
494
495 close c_batch_entries;
496 fetch c_batch_lines into l_batch_line;
497
498 end loop;
499
500 close c_batch_lines;
501
502 if g_message_count = 0 then
503
504 -- Following only applies to previous single threaded BEE
505 -- processes.
506 --
507 if p_asg_action_id is null then
508 -- -- Change batch header status to 'unprocessed'
509 update pay_batch_headers
510 set batch_status = 'U'
511 where batch_id = p_batch_header_id;
512 --
513 update pay_batch_control_totals
517 for l_ctl_rec in csr_control_lines(p_batch_header_id) loop
514 set control_status = 'U'
515 where batch_id = p_batch_header_id;
516 --
518 purge_rollback_messages(l_ctl_rec.batch_control_id,'C');
519 end loop;
520 --
521 if p_leave_batch = 'N' then
522 --
523 -- The user wants the batch to be deleted from the database.
524 payplnk.run_process(l_errbuf,
525 l_retcode,
526 l_business_group_id,
527 'PURGE',
528 p_batch_header_id);
529 --
530 end if;
531 --
532 hr_utility.set_message(801,'PAY_52013_MIX_ROLLBACK_SUCCESS');
533 hr_utility.set_message_token('ASGN_COUNT', l_assignments_processed);
534 g_message_count := g_message_count + 1;
535 insert_rollback_message('H', p_batch_header_id, 'I', false);
536 --
537 -- commit;
538 --
539 end if;
540 --
541 else
542 --
543 if (p_asg_action_id is null and p_commit_all_or_nothing='N') then
544 --
545 update pay_batch_control_totals
546 set control_status = 'U'
547 where batch_id = p_batch_header_id;
548 --
549 for l_ctl_rec in csr_control_lines(p_batch_header_id) loop
550 purge_rollback_messages(l_ctl_rec.batch_control_id,'C');
551 end loop;
552 --
553 hr_utility.set_message(801,'PAY_52013_MIX_ROLLBACK_SUCCESS');
554 hr_utility.set_message_token('ASGN_COUNT', l_assignments_processed);
555 g_message_count := g_message_count + 1;
556 insert_rollback_message('H', p_batch_header_id, 'I', false);
557 --
558 else
559 rollback to RB;
560 end if;
561 --
562 end if;
563 else
564 -- Following only applies to previous single threaded BEE
565 -- processes.
566 --
567 if p_asg_action_id is null then
568 g_message_count := g_message_count + 1;
569 hr_utility.set_message(801,'PAY_52015_INVALID_BATCH');
570 insert_rollback_message('H', p_batch_header_id, 'F', false);
571 end if;
572 --
573 --
574 end if;
575 --
576 commit_messages;
577 --
578 if g_message_count > 0 and p_asg_action_id is not null then
579 -- Must manually reset global message counter, since concurrent
580 -- manager does not start a new session for PL/SQL stored procedures.
581 g_message_count := 0;
582 --
583 hr_utility.set_message(800,'PER_289522_CANNOT_RBK_BEE_LINE');
584 hr_utility.raise_error;
585 --
586 end if;
587 --
588 -- Following only applies to previous single threaded BEE
589 -- processes.
590 --
591 if p_asg_action_id is null then
592 commit;
593 end if;
594 --
595 g_message_count := 0;
596
597 end undo_mix;
598
599 --
600 -- undo_mix_asg
601 --
602
603 procedure undo_mix_asg(
604 p_asg_action_id in number
605 ) is
606 --
607 cursor csr_asg_act is
608 select pbh.batch_id,
609 pac.assignment_id,
610 nvl(pbh.reject_if_results_exists,'Y') reject_if_results_exists,
611 pbh.batch_status
612 from pay_assignment_actions pac,
613 pay_payroll_actions ppa,
614 pay_batch_headers pbh
615 where pac.assignment_action_id = p_asg_action_id
616 and ppa.payroll_action_id = pac.payroll_action_id
617 and pbh.batch_id = ppa.batch_id
618 and ppa.action_type = 'BEE'
619 union all
620 select ppa.batch_id,
621 pac.assignment_id,
622 'Y' reject_if_results_exists,
623 'T' batch_status
624 from pay_assignment_actions pac,
625 pay_payroll_actions ppa
626 where pac.assignment_action_id = p_asg_action_id
627 and ppa.payroll_action_id = pac.payroll_action_id
628 and ppa.action_type = 'BEE'
629 and not exists
630 (select null
631 from pay_batch_headers pbh1
632 where pbh1.batch_id = ppa.batch_id);
633 --
634 cursor csr_reset_control_total (p_batch_id number) is
635 select 'Y'
636 from dual
637 where exists
638 (select null
639 from pay_batch_control_totals pct
640 where pct.batch_id = p_batch_id
641 and pct.control_status <> 'U')
642 and exists
643 (select null
644 from pay_batch_lines pbl
645 where pbl.batch_id = p_batch_id
646 and pbl.batch_line_status <> 'T')
647 and exists
648 (select null
649 from pay_batch_headers pbh
650 where pbh.batch_id = p_batch_id
651 and pbh.batch_status = 'T');
652 --
653 cursor csr_control_lines (p_batch_id number) is
654 select pct.batch_control_id
655 from pay_batch_control_totals pct
656 where pct.batch_id = p_batch_id;
657 --
661 --
658 l_ctl_rec csr_control_lines%ROWTYPE;
659 l_rec_exists varchar2(1);
660 l_rec csr_asg_act%ROWTYPE;
662 l_errbuf varchar2(1000);
663 l_retcode number;
664 --
665 --
666 begin
667 --
668 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',10);
669 --
670 open csr_asg_act;
671 fetch csr_asg_act into l_rec;
672 close csr_asg_act;
673 --
674 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',20);
675 --
676 if l_rec.batch_status = 'T' then
677 --
678 undo_mix(
679 p_errbuf => l_errbuf,
680 p_retcode => l_retcode,
681 p_batch_header_id => l_rec.batch_id,
682 p_reject_if_run_results_exist => l_rec.reject_if_results_exists,
683 p_dml_mode => null,
684 p_assignment_id => l_rec.assignment_id,
685 p_asg_action_id => p_asg_action_id
686 );
687 --
688 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',30);
689 --
690 -- Now check o see if the batch lines have been changed. If so
691 -- then reset the control totals.
692 open csr_reset_control_total(l_rec.batch_id);
693 fetch csr_reset_control_total into l_rec_exists;
694 --
695 if csr_reset_control_total%found then
696 --
697 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',40);
698 --
699 for l_ctl_rec in csr_control_lines(l_rec.batch_id) loop
700 purge_rollback_messages(l_ctl_rec.batch_control_id,'C');
701 end loop;
702 --
703 payplnk.g_payplnk_call := true;
704 --
705 update pay_batch_control_totals
706 set control_status = 'U'
707 where batch_id = l_rec.batch_id;
708 --
709 payplnk.g_payplnk_call := false;
710 --
711 end if;
712 close csr_reset_control_total;
713 --
714 end if;
715 --
716 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',50);
717 --
718 end;
719
720 --
721 -- set_status
722 --
723
724 procedure set_status(
725 p_payroll_action_id in number,
726 p_leave_row in boolean
727 ) is
728 --
729 cursor csr_asg_act is
730 select pbh.batch_id,
731 pbh.business_group_id,
732 ppa.BATCH_PROCESS_MODE,
733 nvl(pbh.purge_after_rollback,'N') purge_after_rollback
734 from pay_payroll_actions ppa,
735 pay_batch_headers pbh
736 where ppa.payroll_action_id = p_payroll_action_id
737 and pbh.batch_id = ppa.batch_id
738 and ppa.action_type = 'BEE'
739 and not exists
740 (select null
741 from pay_batch_lines pbl
742 where pbl.batch_id = pbh.batch_id
743 and pbl.batch_line_status = 'T');
744 --
745 l_rec csr_asg_act%ROWTYPE;
746 l_leave_batch varchar2(30);
747 --
748 l_errbuf varchar2(2000);
749 l_retcode number;
750 --
751 --
752 begin
753 --
754 hr_utility.set_location('pay_mix_rollback_pkg.set_status',10);
755 --
756 open csr_asg_act;
757 fetch csr_asg_act into l_rec;
758 -- IF batch doesn't exists thenno need to reset the batch status.
759 if csr_asg_act%notfound then
760 close csr_asg_act;
761 return;
762 end if;
763 --
764 close csr_asg_act;
765 --
766 hr_utility.set_location('pay_mix_rollback_pkg.set_status',20);
767 --
768 -- Only purge the batch if the payroll action is purged.
769 if (l_rec.purge_after_rollback = 'Y' and l_rec.BATCH_PROCESS_MODE = 'TRANSFER') then
770 -- Purge the batch regarless of the status of the leave_row flag.
771 -- and p_leave_row = false) then
772 --
773 hr_utility.set_location('pay_mix_rollback_pkg.set_status',30);
774 --
775 -- The user wants the batch to be deleted from the database.
776 payplnk.run_process(l_errbuf,
777 l_retcode,
778 l_rec.business_group_id,
779 'PURGE',
780 l_rec.batch_id);
781 --
782 hr_utility.set_location('pay_mix_rollback_pkg.set_status',40);
783 --
784 else
785 --
786 hr_utility.set_location('pay_mix_rollback_pkg.set_status',50);
787 -- Change batch header status to 'unprocessed'
788 --
789 purge_rollback_messages(l_rec.batch_id,'H');
790 --
791 update pay_batch_headers
792 set batch_status = 'U'
793 where batch_id = l_rec.batch_id;
794 --
795 hr_utility.set_location('pay_mix_rollback_pkg.set_status',60);
796 --
797 end if;
798 --
799 hr_utility.set_location('pay_mix_rollback_pkg.set_status',70);
800 --
801 end;
802
803
804 --
805 -- run_results_exist
806 --
807
808 function run_results_exist(p_element_entry_id in number
809 ,p_effective_session_date in date default null
810 ,p_chk_rollback_upd in varchar default null) return boolean is
814 begin
811
812 l_results_found varchar2(1) := 'N';
813
815
816 begin
817 hr_utility.set_location('pay_mix_rollback_pkg.run_results_exist',10);
818
819 if p_chk_rollback_upd = 'Y' then
820 select 'Y' into l_results_found
821 from pay_run_results prr,
822 pay_assignment_actions paa,
823 pay_payroll_actions ppa,
824 pay_element_entries_f pee
825 where prr.source_type = 'E'
826 and pee.element_entry_id = p_element_entry_id
827 and p_effective_session_date between pee.effective_start_date
828 and pee.effective_end_date
829 and prr.source_id = pee.element_entry_id
830 and prr.status = 'P'
831 and prr.assignment_action_id = paa.assignment_action_id
832 and paa.payroll_action_id = ppa.payroll_action_id
833 and ppa.date_earned between pee.effective_start_date
834 and pee.effective_end_date ;
835 else
836 select 'Y' into l_results_found
837 from pay_run_results
838 where source_type = 'E'
839 and source_id = p_element_entry_id
840 and status = 'P';
841 end if;
842
843 exception
844 when no_data_found then
845 null;
846
847 end;
848
849 if l_results_found = 'Y' then
850 return true;
851 else
852 return false;
853 end if;
854
855 end run_results_exist;
856
857
858 --
859 -- insert_rollback_message
860 --
861
862 procedure insert_rollback_message(
863 p_level in varchar2,
864 p_batch_id in number,
865 p_severity in varchar2,
866 p_fail in boolean
867 ) is
868
869 l_line_text pay_message_lines.line_text%type;
870 l_payroll_id number;
871
872 begin
873 hr_utility.set_location('pay_mix_rollback_pkg.insert_rollback_message',10);
874
875 if p_level = 'H' then -- error occurred at header level
876
877 l_line_text := substrb(hr_utility.get_message, 1, 240);
878
879 elsif p_level = 'L' then -- error occurred at line level
880
881 l_line_text := substrb(hr_utility.get_message, 1, 240);
882
883 end if;
884
885 -- Store the message information in PL/SQL tables for committing at the end of the process.
886 g_message_tbl(g_message_count) := l_line_text;
887 g_message_level_tbl(g_message_count) := p_level;
888 g_message_id_tbl(g_message_count) := p_batch_id;
889 g_message_severity_tbl(g_message_count) := p_severity;
890
891 if p_fail then
892
893 -- Stop the process now.
894 hr_utility.raise_error;
895
896 end if;
897
898 end insert_rollback_message;
899
900
901 --
902 -- action_parameter
903 --
904
905 function action_parameter(p_param_name in varchar2)
906 return varchar2 is
907
908 l_name pay_action_parameters.parameter_name%type;
909 param_value pay_action_parameters.parameter_value%type;
910
911 begin
912 begin
913 hr_utility.set_location('pay_mix_rollback_pkg.action_parameter',10);
914
915 -- attempt to find value of the parameter in the action parameter table.
916 select par.parameter_value
917 into param_value
918 from pay_action_parameters par
919 where par.parameter_name = p_param_name;
920
921 exception
922 when no_data_found then
923 if(p_param_name = 'MAX_ERRORS_ALLOWED') then
924 -- If we can't get the max errors allowed, we
925 -- default to chunk_size - make recursive call
926 -- to get this value.
927 param_value := action_parameter('CHUNK_SIZE');
928 end if;
929 end;
930 --
931 return (param_value);
932 --
933 end action_parameter;
934
935 --
936 -- commit_messages
937 --
938
939 procedure commit_messages is
940
941 i number;
942
943 begin
944 hr_utility.set_location('pay_mix_rollback_pkg.commit_messages',10);
945
946 for i in 1..g_message_count loop
947 if g_message_tbl(i) is not null and g_message_id_tbl(i) is not null then
948 insert into pay_message_lines(
949 line_sequence,
950 message_level,
951 source_id,
952 source_type,
953 line_text)
954 values(
955 pay_message_lines_s.nextval,
956 g_message_severity_tbl(i),
960 end if;
957 g_message_id_tbl(i),
958 g_message_level_tbl(i),
959 g_message_tbl(i));
961 end loop;
962
963 -- Empty global PL/SQL message tables
964 for i in 1..g_message_count loop
965 g_message_severity_tbl(i) := null;
966 g_message_id_tbl(i) := null;
967 g_message_level_tbl(i) := null;
968 g_message_tbl(i) := null;
969 end loop;
970
971 --
972 -- commit;
973 --
974
975 hr_utility.set_location('pay_mix_rollback_pkg.commit_messages',20);
976
977 end commit_messages;
978
979 --
980 -- purge_rollback_messages
981 --
982
983 procedure purge_rollback_messages(p_source_id in number, p_msg_type varchar2) is
984
985 begin
986 hr_utility.set_location('pay_mix_rollback_pkg.purge_rollback_messages',10);
987
988 delete from pay_message_lines
989 where source_id = p_source_id
990 and source_type = p_msg_type;
991
992 -- commit;
993
994 end purge_rollback_messages;
995
996
997 end pay_mix_rollback_pkg;
[Home] [Help]