1 PACKAGE BODY pay_mix_rollback_pkg AS
2 /* $Header: pymixrbk.pkb 120.4 2010/10/05 10:02:36 priupadh ship $ */
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;
67
64 l_effective_session_date date;
65 l_errbuf varchar2(2000);
66 l_retcode number;
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
188 and pee.source_id = p_asg_action_id
185 where paa.batch_id = p_batch_header_id
186 and pee.creator_id = paa.absence_attendance_id
187 and pee.creator_type = 'A'
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 and p_reject_if_run_results_exist <> 'Y')) 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
332 fetch c_batch_lines into l_batch_line;
329 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix',40);
330
331 open c_batch_lines;
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;
381 close csr_check_entry_modified;
382 --
383 if (l_chk_entry_modified <> 'Y' or l_chk_rollback_upd='Y') then
384 --
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;
458 l_assignments_processed := l_assignments_processed + 1;
455 --
456 payplnk.g_payplnk_call := false;
457
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
514 set control_status = 'U'
515 where batch_id = p_batch_header_id;
516 --
517 for l_ctl_rec in csr_control_lines(p_batch_header_id) loop
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 nvl(pbh.purge_after_rollback,'Y') leave_batch,
612 pbh.batch_status
613 from pay_assignment_actions pac,
614 pay_payroll_actions ppa,
615 pay_batch_headers pbh
616 where pac.assignment_action_id = p_asg_action_id
617 and ppa.payroll_action_id = pac.payroll_action_id
618 and pbh.batch_id = ppa.batch_id
619 and ppa.action_type = 'BEE'
620 union all
621 select ppa.batch_id,
622 pac.assignment_id,
623 'Y' reject_if_results_exists,
624 'Y' leave_batch,
625 'T' batch_status
626 from pay_assignment_actions pac,
627 pay_payroll_actions ppa
628 where pac.assignment_action_id = p_asg_action_id
629 and ppa.payroll_action_id = pac.payroll_action_id
630 and ppa.action_type = 'BEE'
631 and not exists
632 (select null
633 from pay_batch_headers pbh1
634 where pbh1.batch_id = ppa.batch_id);
635 --
636 cursor csr_reset_control_total (p_batch_id number) is
637 select 'Y'
638 from dual
639 where exists
640 (select null
641 from pay_batch_control_totals pct
642 where pct.batch_id = p_batch_id
643 and pct.control_status <> 'U')
644 and exists
645 (select null
646 from pay_batch_lines pbl
647 where pbl.batch_id = p_batch_id
648 and pbl.batch_line_status <> 'T')
649 and exists
650 (select null
651 from pay_batch_headers pbh
652 where pbh.batch_id = p_batch_id
653 and pbh.batch_status = 'T');
654 --
655 cursor csr_control_lines (p_batch_id number) is
656 select pct.batch_control_id
657 from pay_batch_control_totals pct
658 where pct.batch_id = p_batch_id;
659 -- For bug 8971846
660 /*Bug 10152705 Added join of concurrent_program_name = 'UNDO_MIX' to avoid picking for normal ROLLBACK process */
661 cursor csr_parent_req_id (p_request_id NUMBER) is
662 select decode(parspa.parent_request_id,-1,pa.parent_request_id,parspa.parent_request_id)
663 from fnd_concurrent_requests pa,
664 fnd_concurrent_requests parspa,
665 fnd_concurrent_programs fcp
666 where pa.request_id = p_request_id
667 and parspa.concurrent_program_id = fcp.concurrent_program_id
668 and fcp.concurrent_program_name = 'UNDO_MIX'
669 and pa.parent_request_id = parspa.request_id;
670 --
671 l_ctl_rec csr_control_lines%ROWTYPE;
672 l_rec_exists varchar2(1);
673 l_rec csr_asg_act%ROWTYPE;
674 --
675 l_errbuf varchar2(1000);
676 l_retcode number;
677 -- For bug 8971846
678 l_reject_if_run_results_exist varchar2(240) := null;
679 l_leave_batch varchar2(240) := null;
680 l_parent_id NUMBER := null;
681 --
682 begin
683 --
684 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',10);
685 --
686 open csr_asg_act;
687 fetch csr_asg_act into l_rec;
688 close csr_asg_act;
689 --
690 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',20);
691 --
692 if l_rec.batch_status = 'T' then
693 -- For bug 8971846
694 open csr_parent_req_id (fnd_global.conc_request_id);
695 fetch csr_parent_req_id into l_parent_id;
696 close csr_parent_req_id;
697 if l_parent_id is not null then
698 select argument3, argument5
699 into l_reject_if_run_results_exist,l_leave_batch
700 from fnd_concurrent_requests
701 where request_id = l_parent_id;
702 else
703 l_reject_if_run_results_exist := l_rec.reject_if_results_exists;
704 l_leave_batch := l_rec.leave_batch;
705 end if;
706 -- For bug 8971846
707 undo_mix(
708 p_errbuf => l_errbuf,
709 p_retcode => l_retcode,
710 p_batch_header_id => l_rec.batch_id,
711 p_reject_if_run_results_exist => l_reject_if_run_results_exist,
712 p_leave_batch => l_leave_batch,
713 p_dml_mode => null,
714 p_assignment_id => l_rec.assignment_id,
715 p_asg_action_id => p_asg_action_id
716 );
717 --
718 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',30);
719 --
720 -- Now check o see if the batch lines have been changed. If so
721 -- then reset the control totals.
722 open csr_reset_control_total(l_rec.batch_id);
723 fetch csr_reset_control_total into l_rec_exists;
724 --
725 if csr_reset_control_total%found then
726 --
730 purge_rollback_messages(l_ctl_rec.batch_control_id,'C');
727 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',40);
728 --
729 for l_ctl_rec in csr_control_lines(l_rec.batch_id) loop
731 end loop;
732 --
733 payplnk.g_payplnk_call := true;
734 --
735 update pay_batch_control_totals
736 set control_status = 'U'
737 where batch_id = l_rec.batch_id;
738 --
739 payplnk.g_payplnk_call := false;
740 --
741 end if;
742 close csr_reset_control_total;
743 --
744 end if;
745 --
746 hr_utility.set_location('pay_mix_rollback_pkg.undo_mix_asg',50);
747 --
748 end;
749
750 --
751 -- set_status
752 --
753
754 procedure set_status(
755 p_payroll_action_id in number,
756 p_leave_row in boolean
757 ) is
758 --
759 cursor csr_asg_act is
760 select pbh.batch_id,
761 pbh.business_group_id,
762 ppa.BATCH_PROCESS_MODE,
763 nvl(pbh.purge_after_rollback,'N') purge_after_rollback
764 from pay_payroll_actions ppa,
765 pay_batch_headers pbh
766 where ppa.payroll_action_id = p_payroll_action_id
767 and pbh.batch_id = ppa.batch_id
768 and ppa.action_type = 'BEE'
769 and not exists
770 (select null
771 from pay_batch_lines pbl
772 where pbl.batch_id = pbh.batch_id
773 and pbl.batch_line_status = 'T');
774
775 cursor csr_get_parent_req_id (p_request_id NUMBER) is
776 select decode(parspa.parent_request_id,-1,pa.parent_request_id,parspa.parent_request_id)
777 from fnd_concurrent_requests pa,
778 fnd_concurrent_requests parspa,
779 fnd_concurrent_programs fcp
780 where pa.request_id = p_request_id
781 and parspa.concurrent_program_id = fcp.concurrent_program_id
782 and fcp.concurrent_program_name = 'UNDO_MIX'
783 and pa.parent_request_id = parspa.request_id;
784
785 --
786 l_rec csr_asg_act%ROWTYPE;
787 --
788 l_leave_batch varchar2(10) := null;
789 l_parent_id NUMBER := null;
790 --
791 l_errbuf varchar2(2000);
792 l_retcode number;
793 --
794 --
795 begin
796 --
797 hr_utility.set_location('pay_mix_rollback_pkg.set_status',10);
798 --
799 open csr_asg_act;
800 fetch csr_asg_act into l_rec;
801 -- IF batch doesn't exists thenno need to reset the batch status.
802 if csr_asg_act%notfound then
803 close csr_asg_act;
804 return;
805 end if;
806 --
807 close csr_asg_act;
808 --
809 l_parent_id := null;
810 /*Bug 10152705 Get the request id of BEE Batch Process (Rollback) and get the parameter value of Leave Batch*/
811 open csr_get_parent_req_id(fnd_global.conc_request_id);
812 fetch csr_get_parent_req_id into l_parent_id;
813 close csr_get_parent_req_id;
814
815 if l_parent_id is not null then
816 select argument5
817 into l_leave_batch
818 from fnd_concurrent_requests
819 where request_id = l_parent_id;
820 else
821 if l_rec.purge_after_rollback = 'Y' then
822 l_leave_batch := 'N';
823 else
824 l_leave_batch := 'Y';
825 end if;
826 end if;
827
828
829 hr_utility.set_location('pay_mix_rollback_pkg.set_status',20);
830 --
831 -- Only purge the batch if the payroll action is purged.
832 if (l_leave_batch = 'N' and l_rec.BATCH_PROCESS_MODE = 'TRANSFER') then
833 -- Purge the batch regarless of the status of the leave_row flag.
834 -- and p_leave_row = false) then
835 --
836 hr_utility.set_location('pay_mix_rollback_pkg.set_status',30);
837 --
838 -- The user wants the batch to be deleted from the database.
839 payplnk.run_process(l_errbuf,
840 l_retcode,
841 l_rec.business_group_id,
842 'PURGE',
843 l_rec.batch_id);
844 --
845 hr_utility.set_location('pay_mix_rollback_pkg.set_status',40);
846 --
847 else
848 --
849 hr_utility.set_location('pay_mix_rollback_pkg.set_status',50);
850 -- Change batch header status to 'unprocessed'
851 --
852 purge_rollback_messages(l_rec.batch_id,'H');
853 --
854 update pay_batch_headers
855 set batch_status = 'U'
856 where batch_id = l_rec.batch_id;
857 --
858 hr_utility.set_location('pay_mix_rollback_pkg.set_status',60);
859 --
860 end if;
861 --
862 hr_utility.set_location('pay_mix_rollback_pkg.set_status',70);
863 --
864 end;
865
866
867 --
868 -- run_results_exist
869 --
870
871 function run_results_exist(p_element_entry_id in number
872 ,p_effective_session_date in date default null
873 ,p_chk_rollback_upd in varchar default null) return boolean is
874
875 l_results_found varchar2(1) := 'N';
876
877 begin
878
879 begin
880 hr_utility.set_location('pay_mix_rollback_pkg.run_results_exist',10);
881
885 pay_assignment_actions paa,
882 if p_chk_rollback_upd = 'Y' then
883 select 'Y' into l_results_found
884 from pay_run_results prr,
886 pay_payroll_actions ppa,
887 pay_element_entries_f pee
888 where prr.source_type = 'E'
889 and pee.element_entry_id = p_element_entry_id
890 and p_effective_session_date between pee.effective_start_date
891 and pee.effective_end_date
892 and prr.source_id = pee.element_entry_id
893 and prr.status = 'P'
894 and prr.assignment_action_id = paa.assignment_action_id
895 and paa.payroll_action_id = ppa.payroll_action_id
896 and ppa.date_earned between pee.effective_start_date
897 and pee.effective_end_date ;
898 else
899 select 'Y' into l_results_found
900 from pay_run_results
901 where source_type = 'E'
902 and source_id = p_element_entry_id
903 and status = 'P';
904 end if;
905
906 exception
907 when no_data_found then
908 null;
909
910 end;
911
912 if l_results_found = 'Y' then
913 return true;
914 else
915 return false;
916 end if;
917
918 end run_results_exist;
919
920
921 --
922 -- insert_rollback_message
923 --
924
925 procedure insert_rollback_message(
926 p_level in varchar2,
927 p_batch_id in number,
928 p_severity in varchar2,
929 p_fail in boolean
930 ) is
931
932 l_line_text pay_message_lines.line_text%type;
933 l_payroll_id number;
934
935 begin
936 hr_utility.set_location('pay_mix_rollback_pkg.insert_rollback_message',10);
937
938 if p_level = 'H' then -- error occurred at header level
939
940 l_line_text := substrb(hr_utility.get_message, 1, 240);
941
942 elsif p_level = 'L' then -- error occurred at line level
943
944 l_line_text := substrb(hr_utility.get_message, 1, 240);
945
946 end if;
947
948 -- Store the message information in PL/SQL tables for committing at the end of the process.
949 g_message_tbl(g_message_count) := l_line_text;
950 g_message_level_tbl(g_message_count) := p_level;
951 g_message_id_tbl(g_message_count) := p_batch_id;
952 g_message_severity_tbl(g_message_count) := p_severity;
953
954 if p_fail then
955
956 -- Stop the process now.
957 hr_utility.raise_error;
958
959 end if;
960
961 end insert_rollback_message;
962
963
964 --
965 -- action_parameter
966 --
967
968 function action_parameter(p_param_name in varchar2)
969 return varchar2 is
970
971 l_name pay_action_parameters.parameter_name%type;
972 param_value pay_action_parameters.parameter_value%type;
973
974 begin
975 begin
976 hr_utility.set_location('pay_mix_rollback_pkg.action_parameter',10);
977
978 -- attempt to find value of the parameter in the action parameter table.
979 select par.parameter_value
980 into param_value
981 from pay_action_parameters par
982 where par.parameter_name = p_param_name;
983
984 exception
985 when no_data_found then
986 if(p_param_name = 'MAX_ERRORS_ALLOWED') then
987 -- If we can't get the max errors allowed, we
988 -- default to chunk_size - make recursive call
989 -- to get this value.
990 param_value := action_parameter('CHUNK_SIZE');
991 end if;
992 end;
993 --
994 return (param_value);
995 --
996 end action_parameter;
997
998 --
999 -- commit_messages
1000 --
1001
1002 procedure commit_messages is
1003
1004 i number;
1005
1006 begin
1007 hr_utility.set_location('pay_mix_rollback_pkg.commit_messages',10);
1008
1009 for i in 1..g_message_count loop
1010 if g_message_tbl(i) is not null and g_message_id_tbl(i) is not null then
1011 insert into pay_message_lines(
1012 line_sequence,
1013 message_level,
1014 source_id,
1015 source_type,
1016 line_text)
1017 values(
1018 pay_message_lines_s.nextval,
1019 g_message_severity_tbl(i),
1020 g_message_id_tbl(i),
1021 g_message_level_tbl(i),
1022 g_message_tbl(i));
1023 end if;
1024 end loop;
1025
1026 -- Empty global PL/SQL message tables
1027 for i in 1..g_message_count loop
1028 g_message_severity_tbl(i) := null;
1029 g_message_id_tbl(i) := null;
1030 g_message_level_tbl(i) := null;
1031 g_message_tbl(i) := null;
1032 end loop;
1033
1034 --
1035 -- commit;
1036 --
1037
1038 hr_utility.set_location('pay_mix_rollback_pkg.commit_messages',20);
1039
1040 end commit_messages;
1041
1042 --
1043 -- purge_rollback_messages
1044 --
1045
1046 procedure purge_rollback_messages(p_source_id in number, p_msg_type varchar2) is
1047
1048 begin
1049 hr_utility.set_location('pay_mix_rollback_pkg.purge_rollback_messages',10);
1050
1051 delete from pay_message_lines
1052 where source_id = p_source_id
1053 and source_type = p_msg_type;
1054
1055 -- commit;
1056
1057 end purge_rollback_messages;
1058
1059
1060 end pay_mix_rollback_pkg;
[Home] [Help]