[Home] [Help]
PACKAGE BODY: APPS.PAY_BATCH_BALANCE_ADJ_PKG
Source
1 PACKAGE BODY PAY_BATCH_BALANCE_ADJ_PKG AS
2 /* $Header: pybbautl.pkb 120.9 2006/05/26 11:17:28 jabubaka noship $ */
3
4 g_package constant varchar2(33) := ' PAY_BATCH_BALANCE_ADJ_PKG.';
5
6 type varchar240_table is table of varchar2(240)
7 index by binary_integer;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< submit_conc_request >------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 -- Description:
14 -- This function is called from the BBA webadi interface to submit the
15 -- concurrent request for batch balance adjustment.
16 -- Returns the request id of the submitted request.
17 --
18 -- ----------------------------------------------------------------------------
19 function submit_conc_request
20 (
21 p_business_group_id in number,
22 p_mode in varchar2,
23 p_batch_id in number,
24 p_wait in varchar2 default 'N',
25 p_act_parameter_group_id in number default null
26 ) return number is
27 --
28 l_request_id number := 0;
29 l_pac_id pay_payroll_actions.payroll_action_id%TYPE;
30 l_batch_status pay_balance_batch_headers.batch_status%TYPE := null;
31 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.SUBMIT_CONC_REQUEST';
32 --
33 begin
34 --
35 hr_utility.set_location('Entering ' || l_proc, 5);
36
37 l_batch_status := batch_overall_status(p_batch_id);
38
39 if not check_operation_allowed (p_batch_status => l_batch_status, p_process_mode => p_mode) then
40
41 fnd_message.set_name('PAY', 'PAY_34292_BBABP_INV_OPERATION');
42 fnd_message.set_token('BATCHOP', p_mode);
43 fnd_message.set_token('STATUS',l_batch_status);
44 fnd_message.raise_error;
45
46 end if;
47
48 --
49 if p_mode in ('PURGE', 'TRANSFER', 'ROLLBACK', 'VALIDATE') then
50
51 l_request_id := fnd_request.submit_request
52 ('PAY',
53 'PAYBBABP',
54 null,
55 null,
56 null,
57 p_mode,
58 p_batch_id,
59 null
60 );
61
62
63 end if;
64
65 if l_request_id = 0 then
66 fnd_message.raise_error;
67 else
68 commit;
69 end if;
70 --
71
72 hr_utility.set_location('Leaving ' || l_proc, 5);
73 return (l_request_id);
74 --
75 End submit_conc_request;
76 --
77 --
78 -- ----------------------------------------------------------------------------
79 -- |---------------------------< batch_overall_status >-----------------------|
80 -- ----------------------------------------------------------------------------
81 --
82 -- Description:
83 -- This function derives the overall stauts of the batch. The overall status
84 -- is not just the batch status but also considers the status of the
85 -- batch groups.
86 --
87 -- ----------------------------------------------------------------------------
88 function batch_overall_status (p_batch_id in number)
89 return varchar2 is
90 --
91 valid_groups_exist boolean := FALSE;
92 error_groups_exist boolean := FALSE;
93 unprocessed_groups_exist boolean := FALSE;
94 transferred_groups_exist boolean := FALSE;
95 header_transferred boolean := FALSE;
96 header_processing boolean := FALSE;
97 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.BATCH_OVERALL_STATUS';
98 --
99 cursor csr_status is
100 select pay_batch_balance_adj_pkg.batch_group_overall_status
101 (pabg.batch_id, pabg.batch_group_id) status
102 from pay_adjust_batch_groups pabg
103 where pabg.batch_id = p_batch_id
104 union
105 select batch_status status
106 from pay_balance_batch_headers
107 where batch_id = p_batch_id
108 union
109 select 'Y' status
110 from pay_balance_batch_headers bth
111 where bth.batch_id = p_batch_id
112 and bth.batch_status = 'T'
113 order by 1 desc;
114 --
115 begin
116 --
117 hr_utility.set_location('Entering ' || l_proc, 5);
118
119 hr_utility.trace('Batch status for batch ' || p_batch_id);
120
121 for distinct_status in csr_status LOOP
122 --
123 if distinct_status.status = 'E' then
124 hr_utility.trace('Errored batch groups exist');
125 error_groups_exist := TRUE;
126 exit; -- we do not need to know the rest
127 --
128 elsif distinct_status.status = 'U' then
129 hr_utility.trace('Unprocessed batch groups exist');
130 unprocessed_groups_exist := TRUE;
131 --
132 elsif distinct_status.status = 'T' then
133 hr_utility.trace('Transferred batch groups exist');
134 transferred_groups_exist := TRUE;
135 --
136 elsif distinct_status.status = 'V' then
137 hr_utility.trace('Valid batch groups exist');
138 valid_groups_exist := TRUE;
139 --
140 elsif distinct_status.status = 'Y' then
141 hr_utility.trace('Batch header is transferred');
142 header_transferred := TRUE;
143 --
144 elsif distinct_status.status in ('L','P') then
145 hr_utility.trace('Batch in Processing state');
146 header_processing := TRUE;
147 --
148 end if;
149 --
150 -- we do not need to know the rest if it is the following case.
151
152 if (header_transferred and
153 (unprocessed_groups_exist or valid_groups_exist or error_groups_exist))
154 or (not header_transferred and error_groups_exist) then
155 --
156 exit;
157 --
158 end if;
159 --
160 end loop;
161 --
162 if header_processing then
163 return 'P'; -- batch is currently under process.
164 elsif header_transferred
165 and NOT unprocessed_groups_exist
166 and NOT valid_groups_exist
167 and NOT error_groups_exist then
168 return 'T'; -- all groups (if exists) has been transferred.
169 elsif header_transferred then
170 return 'ST'; -- some lines might not have transferred.
171 elsif error_groups_exist then
172 return 'E'; -- there is at least one error group
173 elsif unprocessed_groups_exist
174 and NOT transferred_groups_exist then
175 return 'U'; -- there is at least one unprocessed line
176 elsif valid_groups_exist
177 and NOT transferred_groups_exist
178 and NOT unprocessed_groups_exist then
179 return 'V'; -- all lines are valid
180 else
181 return 'SM'; -- mismatch of statuses
182 end if;
183
184 hr_utility.set_location('Leaving ' || l_proc, 5);
185 --
186 end batch_overall_status;
187 --
188 --
189 -- ----------------------------------------------------------------------------
190 -- |-------------------------< batch_group_overall_status >-------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 -- Description:
194 -- This function derives the overall status of the batch group. The overall
195 -- status is not just the batch group status but also considers the status of
196 -- the batch lines.
197 --
198 -- ----------------------------------------------------------------------------
199 function batch_group_overall_status
200 ( p_batch_id in number,
201 p_batch_group_id in number )
202 return varchar2 is
203 --
204 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.BATCH_GROUP_OVERALL_STATUS';
205 --
206 valid_lines_exist boolean := FALSE;
207 error_lines_exist boolean := FALSE;
208 unprocessed_lines_exist boolean := FALSE;
209 transferred_lines_exist boolean := FALSE;
210 group_transferred boolean := FALSE;
211 group_processing boolean := FALSE;
212 --
213 cursor csr_status is
214 select pabl.batch_line_status status
215 from pay_adjust_batch_lines pabl
216 where pabl.batch_id = p_batch_id
217 and pabl.batch_group_id = p_batch_group_id
218 union
219 select batch_group_status status
220 from pay_adjust_batch_groups
221 where batch_id = p_batch_id
222 and batch_group_id = p_batch_group_id
223 union
224 select 'Y' status
225 from pay_adjust_batch_groups
226 where batch_group_id = p_batch_group_id
227 and batch_id = p_batch_id
228 and batch_group_status = 'T'
229 order by 1 desc;
230 --
231 begin
232 --
233 hr_utility.set_location('Entering ' || l_proc, 5);
234 hr_utility.trace('Status for batch group ' || p_batch_group_id);
235
236 for distinct_status in csr_status LOOP
237 --
238 if distinct_status.status = 'E' then
239 hr_utility.trace('Error lines exist');
240 error_lines_exist := TRUE;
241 exit; -- we do not need to know the rest
242 --
243 elsif distinct_status.status = 'U' then
244 hr_utility.trace('Unprocessed lines exist');
245 unprocessed_lines_exist := TRUE;
246 --
247 elsif distinct_status.status = 'T' then
248 hr_utility.trace('Transferred lines exist');
249 transferred_lines_exist := TRUE;
250 --
251 elsif distinct_status.status = 'V' then
252 hr_utility.trace('Valid lines exist');
253 valid_lines_exist := TRUE;
254 --
255 elsif distinct_status.status = 'Y' then
256 hr_utility.trace('Batch group is transferred');
257 group_transferred := TRUE;
258 --
259 elsif distinct_status.status in ('L','P') then
260 hr_utility.trace('Batch group is currently loaded');
261 group_processing := TRUE;
262 --
263 end if;
264 --
265 -- we do not need to know the rest if it is the following case.
266 if (group_transferred and
267 (unprocessed_lines_exist or valid_lines_exist or error_lines_exist))
268 or (not group_transferred and error_lines_exist) then
269 --
270 exit;
271 --
272 end if;
273 --
274 end loop;
275 --
276 if group_processing then
277 return 'P'; -- batch group is currently under process.
278 elsif group_transferred
279 and NOT unprocessed_lines_exist
280 and NOT valid_lines_exist
281 and NOT error_lines_exist then
282 return 'T'; -- all lines (if exists) has been transferred.
283 elsif group_transferred then
284 return 'ST'; -- some lines might not have transferred.
285 elsif error_lines_exist then
286 return 'E'; -- there is at least one error group
287 elsif unprocessed_lines_exist
288 and NOT transferred_lines_exist then
289 return 'U'; -- there is at least one unprocessed line
290 elsif valid_lines_exist
291 and NOT transferred_lines_exist
292 and NOT unprocessed_lines_exist then
293 return 'V'; -- all lines are valid
294 else
295 return 'SM'; -- mismatch of statuses
296 end if;
297 --
298 hr_utility.set_location('Leaving ' || l_proc, 100);
299 --
300 end batch_group_overall_status;
301 --
302 -- ----------------------------------------------------------------------------
303 -- |---------------------------------< purge >--------------------------------|
304 -- ----------------------------------------------------------------------------
305 --
306 -- Description:
307 -- This procedures deletes all records associated with the batch balance
308 -- adjustment tables and the pay_message_lines table.
309 --
310 -- ----------------------------------------------------------------------------
311 --
312 procedure purge
313 ( p_batch_id in number,
314 p_batch_group_id in number)
315 is
316 --
317 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.PURGE';
318 --
319 cursor csr_batch_lines is
320 select pabl.batch_line_id
321 from pay_adjust_batch_lines pabl
322 where pabl.batch_id = p_batch_id
323 and pabl.batch_group_id = nvl(p_batch_group_id, pabl.batch_group_id) ;
324 --
325 cursor csr_batch_groups is
326 select pabg.batch_group_id
327 from pay_adjust_batch_groups pabg
328 where pabg.batch_id = p_batch_id
329 and pabg.batch_group_id = nvl(p_batch_group_id, pabg.batch_group_id) ;
330
331 --
332 begin
333 --
337
334 hr_utility.set_location('Entering: '||l_proc, 5);
335
336 hr_utility.trace('Purging batch '||p_batch_id);
338 hr_utility.trace('Purging batch lines of the batch');
339
340
341 for cssr in csr_batch_lines loop
342
343 --
344 -- Delete all messages of the batch line
345 --
346 delete
347 from pay_message_lines
348 where source_type = 'I'
349 and source_id = cssr.batch_line_id;
350 --
351
352 --
353 -- Delete the batch line
354 --
355
356 delete
357 from pay_adjust_batch_lines
358 where batch_line_id = cssr.batch_line_id;
359
360 end loop;
361
362 hr_utility.trace('Purging batch groups of the batch');
363
364 for cssr in csr_batch_groups loop
365
366 --
370 from pay_message_lines
367 -- Delete all messages of the batch group
368 --
369 delete
371 where source_type = 'G'
372 and source_id = cssr.batch_group_id;
373 --
374
375 --
376 -- Delete the batch group
377 --
378
379 delete
380 from pay_adjust_batch_groups
381 where batch_group_id = cssr.batch_group_id;
382
383 end loop;
384
385
386 hr_utility.trace('Purging the batch header');
387
388 if p_batch_group_id is null then
389
390 --
391 -- Deletes all messages of the batch header
392 --
393 delete
394 from pay_message_lines
395 where source_type = 'H'
396 and source_id = p_batch_id;
397 --
398
399 --
400 -- Delete the batch header
401 --
402
403 delete
404 from pay_balance_batch_headers
405 where batch_id = p_batch_id;
406
407 end if;
408
409 hr_utility.set_location('Leaving: '||l_proc, 5);
410 commit;
411 --
412 end purge;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |---------------------------< check_operation_allowed >--------------------|
416 -- ----------------------------------------------------------------------------
417 --
418 -- Description:
419 -- Given the current batch status and the required processing mode this
420 -- function determines if its a valid operation or not.
421 --
422 -- ----------------------------------------------------------------------------
423 --
424 function check_operation_allowed
425 ( p_batch_status in varchar2 ,
426 p_process_mode in varchar2 )
427 return boolean is
428 --
429 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.CHECK_OPERATION_ALLOWED';
430 --
431 begin
432 --
433 hr_utility.set_location('Entering: '||l_proc, 5);
434
435 -- 'L' stands for Loading (Processing)
436 if (p_batch_status = 'T' and p_process_mode in ('TRANSFER')) or
437 (p_batch_status = 'P') or
438 (p_batch_status = 'L') then
439 return false;
440 else
441 return true;
442 end if;
443
444 hr_utility.set_location('Leaving: '||l_proc, 5);
445 --
446 end check_operation_allowed;
447 -- ----------------------------------------------------------------------------
448 -- |--------------------------------< rollback_batch >-------------------------|
449 -- ----------------------------------------------------------------------------
450 --
451 -- Description:
452 -- This procedures rollbacks all the actions of the given batch id.
453 --
454 -- ----------------------------------------------------------------------------
455 --
456 procedure rollback_batch
457 ( p_batch_id in number,
458 p_batch_group_id in number )
459 is
460 --
461 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
462 l_proc varchar2(72) := 'PAY_BATCH_BALANCE_ADJ_PKG.ROLLBACK_BATCH';
463
464 l_message_source_id_tbl hr_entry.number_table;
465 l_message_source_type_tbl hr_entry.varchar2_table;
466 l_message_level_tbl hr_entry.varchar2_table;
467 l_message_text_tbl varchar240_table;
468 l_count binary_integer := 0;
469 l_error_text pay_message_lines.line_text%type;
470
471 --
472 cursor csr_batch_groups is
473 select batch_group_id
474 from pay_adjust_batch_groups
475 where batch_id = p_batch_id
476 and batch_group_id = nvl(p_batch_group_id, batch_group_id)
477 and batch_group_status = 'T';
478 --
479 cursor csr_payroll_action (p_batch_group_id number) is
480 select pact.payroll_action_id
481 from pay_payroll_actions pact
482 where pact.batch_id = p_batch_group_id
483 and pact.action_type = 'B';
484 --and pact.batch_process_mode = 'TRANSFER';
485 --
486 begin
487 --
488 hr_utility.set_location('Entering: '||l_proc, 10);
489
490 l_count := 0;
491
492 for lcsr in csr_batch_groups loop
493
494 open csr_payroll_action (p_batch_group_id => lcsr.batch_group_id);
495 fetch csr_payroll_action into l_payroll_action_id;
496 close csr_payroll_action;
497
498 if l_payroll_action_id is not null then
499
500 begin
501 hr_utility.trace('Rollback batch group ' || lcsr.batch_group_id);
502 hr_utility.trace('Rollback payroll action ' || l_payroll_action_id);
503
504 savepoint RG;
505 py_rollback_pkg.rollback_payroll_action(l_payroll_action_id,'ROLLBACK',FALSE);
506 commit;
507
508 update pay_adjust_batch_groups
509 set batch_group_status = 'U'
510 where batch_group_id = lcsr.batch_group_id;
511
512 update pay_adjust_batch_lines
513 set batch_line_status = 'U'
514 where batch_group_id = lcsr.batch_group_id;
515
516 commit;
517
518 l_count := l_count + 1;
519 l_message_level_tbl(l_count) := 'I';
520 l_message_source_type_tbl(l_count) := 'G';
521 l_message_source_id_tbl(l_count) := lcsr.batch_group_id;
522
523 hr_utility.set_message(801,'PAY_34293_BBABP_BTCHGRP_RB');
524 l_error_text := substrb(hr_utility.get_message, 1, 240);
525
526 l_message_text_tbl(l_count) := l_error_text;
527
528
529 exception
530
531 when others then
532
533 rollback to RG;
534
535 l_count := l_count + 1;
539
536 l_message_level_tbl(l_count) := 'W';
537 l_message_source_type_tbl(l_count) := 'G';
538 l_message_source_id_tbl(l_count) := lcsr.batch_group_id;
540 hr_utility.set_message(801,'PAY_34294_BBABP_ERR_IN_PROC');
541 hr_utility.set_message_token('PROC', l_proc);
542 l_error_text := substrb(hr_utility.get_message || ' ' || sqlerrm, 1, 240);
543 l_message_text_tbl(l_count) := l_error_text;
544
545 end;
546
547 end if;
548
549 end loop;
550
551 hr_utility.set_location(l_proc, 30);
552
553 begin
554
555 for i in 1..l_count loop
556
557 if l_message_text_tbl(i) is not null then
558 --
559 insert into pay_message_lines
560 (line_sequence,
561 payroll_id,
562 message_level,
563 source_id,
564 source_type,
565 line_text )
566 values
567 (pay_message_lines_s.nextval,
568 null,
569 l_message_level_tbl(i),
573 );
570 l_message_source_id_tbl(i),
571 l_message_source_type_tbl(i),
572 l_message_text_tbl(i)
574 --
575 end if;
576
577 end loop;
578
579 hr_utility.set_location(l_proc, 40);
580
581 --
582 exception
583 when no_data_found then
584 null;
585 end;
586
587 commit;
588
589 hr_utility.set_location('Leaving : ' || l_proc, 100);
590 --
591 end rollback_batch;
592 --
593
594 --
595 ------------------------------------------------------------------------------
596 --|--------------------------------< validate_and_transfer >-----------------|
597 ------------------------------------------------------------------------------
598 --
599 -- Description:
600 -- This procedure does validate or transfer based on the batch_process_mode
601 -- specified. This is internally called by validate_batch and transfer_batch
602 --------------------------------------------------------------------------------
603 --
604 procedure validate_and_transfer
605 (p_batch_id in number,
606 p_batch_group_id in number,
607 p_batch_process_mode in varchar2)
608 is
609
610
611
612
613 l_line_error boolean := FALSE;
614 invalid_batch_group_details EXCEPTION;
615 process_batch_failed EXCEPTION;
616 l_proc varchar2(100);
617 status_T number := 0;
618 status_E number := 0;
619 l_element_link_id number := 0;
620
621 dummy_consolidation_id pay_consolidation_sets.consolidation_set_id%type;
622 dummy_payroll_id pay_all_payrolls_f.payroll_id%type;
623 dummy_msg_source_id pay_message_lines.source_id%type;
624
625 -- Temperorary table for storing error messages and used for later putting into
626 -- pay_message_lines
627 l_message_source_id_tbl hr_entry.number_table;
628 l_message_source_type_tbl hr_entry.varchar2_table;
629 l_message_level_tbl hr_entry.varchar2_table;
630 l_message_text_tbl varchar240_table;
631 l_count binary_integer := 0;
632 l_error_text pay_message_lines.line_text%type;
633 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
634 p_batch_name pay_balance_batch_headers.batch_name%type;
635
636 --
637
638 --
639
640 --cursors needed
641 cursor csr_batch_groups is
642 select batch_group_id, consolidation_set_id,
643 payroll_id, effective_date, prepay_flag
644 from pay_adjust_batch_groups
645 where batch_id = p_batch_id
646 and batch_group_id = nvl(p_batch_group_id, batch_group_id)
647 and batch_group_status not in ('L','P','T');
648 --
649
650 --
651 cursor csr_batch_lines(p_arg_batch_group_id pay_adjust_batch_groups.batch_group_id%type) is
652 select *
653 from pay_adjust_batch_lines
654 where batch_id = p_batch_id
655 and batch_group_id = p_arg_batch_group_id;
656 --
657
658 cursor batch_grp_stts(batch_status_arg pay_adjust_batch_groups.batch_group_status%type) is
659 select 1 from pay_adjust_batch_groups
660 where batch_group_status = batch_status_arg
661 and batch_id = p_batch_id;
662 --
663 begin
664
665 update pay_balance_batch_headers
666 set batch_status = 'P'
667 where batch_id = p_batch_id;
668
669 select batch_name into p_batch_name
673 commit;
670 from pay_balance_batch_headers
671 where batch_id = p_batch_id;
672
674
675 for group_csr in csr_batch_groups loop
676 begin
677
678 update pay_adjust_batch_lines
679 set batch_line_status = 'U'
680 where batch_group_id = group_csr.batch_group_id;
681
682 update pay_adjust_batch_groups
683 set batch_group_status = 'P'
684 where batch_group_id = group_csr.batch_group_id;
685
686 -- deleting all the message lines for this group
687 delete from pay_message_lines
688 where source_type = 'G'
689 and source_id = group_csr.batch_group_id;
690 --deleting all the message lines for the batch lines in this group
691 for line_csr in csr_batch_lines(group_csr.batch_group_id) loop
692 delete from pay_message_lines
693 where source_type = 'I'
694 and source_id = line_csr.batch_line_id;
695 end loop;
696
697 -- committing at this stage so the monitor request region
698 -- can pick it up to show the status
699 commit;
700
701 savepoint main_SP;
702
703 l_payroll_action_id := 0;
704
705
706 -- Checking the payroll id and consolidation set id
707 -- are valid or not. If not valid then raise exception here
708 --
709 begin
710 select pcs.consolidation_set_id into dummy_consolidation_id
711 from pay_consolidation_sets pcs
712 where group_csr.consolidation_set_id = pcs.consolidation_set_id;
713 exception
714 when no_data_found then
715 raise invalid_batch_group_details;
716 end;
717
718 -- invalid consolidation_set_id,.... raising Exception
719
720 begin
721 select papf.payroll_id into dummy_payroll_id
722 from pay_all_payrolls_f papf
723 where group_csr.payroll_id = papf.payroll_id
724 AND group_csr.effective_date between
725 papf.effective_start_date and papf.effective_end_date;
726 exception
727 when no_data_found then
728 raise invalid_batch_group_details;
729 end;
730 -- invlaid payroll_id,...... raising Exception
731 --
732 begin
733 l_payroll_action_id := PAY_BAL_ADJUST.init_batch(
734 p_batch_name,
735 group_csr.effective_date,
736 group_csr.consolidation_set_id,
737 group_csr.payroll_id,
738 'B', -- B for balance adjustment
739 'NO_COMMIT', --to avoid process_batch commiting
740 group_csr.prepay_flag
741 );
742 exception
743 when others then
744 hr_utility.set_message(801,'HR_6614_PAY_NO_TIME_PERIOD');
745 l_error_text := substrb(hr_utility.get_message, 1, 240);
746 insert into pay_message_lines
747 (line_sequence,
751 source_type,
748 payroll_id,
749 message_level,
750 source_id,
752 line_text)
753 values
754 (pay_message_lines_s.nextval,
755 null,
756 'F',
757 group_csr.batch_group_id,
758 'G',
759 l_error_text);
760 raise process_batch_failed;
761 end;
762
763 -- Updating the payroll action table
764 update pay_payroll_actions
765 set batch_id = group_csr.batch_group_id
766 where payroll_action_id=l_payroll_action_id
767 and action_type ='B';
768
769 -- Reset the temperorary PL/SQL message table
770 l_count := 0;
771
772 l_line_error := FALSE;
773
774
775 for line_csr in csr_batch_lines(group_csr.batch_group_id) loop
776 savepoint SP2;
777 --calling adjust_balance from pybaladj.pkb
778 begin
779
780 /*
781 Here caching of the element_link_id for a combination can improve performance
782 */
783 l_element_link_id := hr_entry_api.get_link(
784 p_assignment_id => line_csr.ASSIGNMENT_ID,
785 p_element_type_id => line_csr.ELEMENT_TYPE_ID,
786 p_session_date => group_csr.effective_date);
787
788
789 PAY_BAL_ADJUST.adjust_balance
790 (
791 p_batch_id => l_payroll_action_id,
792 p_assignment_id => line_csr.ASSIGNMENT_ID,
796 p_input_value_id2 => line_csr.INPUT_VALUE_ID2,
793 --p_element_link_id => line_csr.ELEMENT_LINK_ID,
794 p_element_link_id => l_element_link_id,
795 p_input_value_id1 => line_csr.INPUT_VALUE_ID1,
797 p_input_value_id3 => line_csr.INPUT_VALUE_ID3,
798 p_input_value_id4 => line_csr.INPUT_VALUE_ID4,
799 p_input_value_id5 => line_csr.INPUT_VALUE_ID5,
800 p_input_value_id6 => line_csr.INPUT_VALUE_ID6,
801 p_input_value_id7 => line_csr.INPUT_VALUE_ID7,
802 p_input_value_id8 => line_csr.INPUT_VALUE_ID8,
803 p_input_value_id9 => line_csr.INPUT_VALUE_ID9,
804 p_input_value_id10 => line_csr.INPUT_VALUE_ID10,
805 p_input_value_id11 => line_csr.INPUT_VALUE_ID11,
806 p_input_value_id12 => line_csr.INPUT_VALUE_ID12,
807 p_input_value_id13 => line_csr.INPUT_VALUE_ID13,
808 p_input_value_id14 => line_csr.INPUT_VALUE_ID14,
809 p_input_value_id15 => line_csr.INPUT_VALUE_ID15,
810 p_entry_value1 => line_csr.ENTRY_VALUE1,
811 p_entry_value2 => line_csr.ENTRY_VALUE2,
812 p_entry_value3 => line_csr.ENTRY_VALUE3,
813 p_entry_value4 => line_csr.ENTRY_VALUE4,
814 p_entry_value5 => line_csr.ENTRY_VALUE5,
815 p_entry_value6 => line_csr.ENTRY_VALUE6,
816 p_entry_value7 => line_csr.ENTRY_VALUE7,
817 p_entry_value8 => line_csr.ENTRY_VALUE8,
818 p_entry_value9 => line_csr.ENTRY_VALUE9,
819 p_entry_value10 => line_csr.ENTRY_VALUE10,
820 p_entry_value11 => line_csr.ENTRY_VALUE11,
821 p_entry_value12 => line_csr.ENTRY_VALUE12,
822 p_entry_value13 => line_csr.ENTRY_VALUE13,
823 p_entry_value14 => line_csr.ENTRY_VALUE14,
824 p_entry_value15 => line_csr.ENTRY_VALUE15,
825
826 --Costing Information
827 p_balance_adj_cost_flag => line_csr.BALANCE_ADJ_COST_FLAG,
828 p_cost_allocation_keyflex_id=> line_csr.COST_ALLOCATION_KEYFLEX_ID,
829 p_attribute_category => line_csr.ATTRIBUTE_CATEGORY,
830 p_attribute1 => line_csr.ATTRIBUTE1,
831 p_attribute2 => line_csr.ATTRIBUTE2,
832 p_attribute3 => line_csr.ATTRIBUTE3,
833 p_attribute4 => line_csr.ATTRIBUTE4,
834 p_attribute5 => line_csr.ATTRIBUTE5,
835 p_attribute6 => line_csr.ATTRIBUTE6,
836 p_attribute7 => line_csr.ATTRIBUTE7,
837 p_attribute8 => line_csr.ATTRIBUTE8,
838 p_attribute9 => line_csr.ATTRIBUTE9,
839 p_attribute10 => line_csr.ATTRIBUTE10,
840 p_attribute11 => line_csr.ATTRIBUTE11,
841 p_attribute12 => line_csr.ATTRIBUTE12,
842 p_attribute13 => line_csr.ATTRIBUTE13,
843 p_attribute14 => line_csr.ATTRIBUTE14,
844 p_attribute15 => line_csr.ATTRIBUTE15,
845 p_attribute16 => line_csr.ATTRIBUTE16,
846 p_attribute17 => line_csr.ATTRIBUTE17,
847 p_attribute18 => line_csr.ATTRIBUTE18,
848 p_attribute19 => line_csr.ATTRIBUTE19,
849 p_attribute20 => line_csr.ATTRIBUTE20,
850 p_run_type_id => line_csr.RUN_TYPE_ID,
851 p_original_entry_id => line_csr.ORIGINAL_ENTRY_ID,
852 p_tax_unit_id => line_csr.TAX_UNIT_ID
853 );
854
855 exception
856 when others then
857
858 -- enter error msg in to PL/SQL msg table at line level
859 l_count := l_count + 1;
860 l_message_level_tbl(l_count) := 'F';
861 l_message_source_type_tbl(l_count) := 'I';
862 l_message_source_id_tbl(l_count) := line_csr.batch_line_id;
863 --hr_utility.set_message(801, 'PAY_34294_BBABP_ERR_IN_PROC');
864 -- message is "Error in test"
865 --l_proc := 'PAY_BAL_ADJUST.adjust_balance';
866 --hr_utility.set_message_token('PROC', l_proc);
867 l_error_text := substrb(hr_utility.get_message || ' ' ||
868 sqlerrm, 1, 240);
869 l_message_text_tbl(l_count) := l_error_text;
870 --
871 l_line_error := TRUE;
872 rollback to SP2;
873 end;
874 end loop;
875
876 begin
877 PAY_BAL_ADJUST.process_batch(l_payroll_action_id);
878 -- put a message in PL/SQL msg tbl at group level
879
880 exception
881 when others
882 then
883 l_count := l_count + 1;
884 l_message_level_tbl(l_count) := 'F';
885 l_message_source_type_tbl(l_count) := 'G';
886 l_message_source_id_tbl(l_count) := group_csr.batch_group_id;
887
888 --hr_utility.set_message(801, 'PAY_34294_BBABP_ERR_IN_PROC');
889 -- message is "Error in test"
890 --l_proc := 'PAY_BAL_ADJUST.process_batch';
891 --hr_utility.set_message_token('PROC', l_proc);
892 l_error_text := substrb(hr_utility.get_message || ' ' ||
893 sqlerrm, 1, 240);
894
895 l_message_text_tbl(l_count) := l_error_text;
896 raise process_batch_failed;
897
898 end;
899
900 if (l_line_error = TRUE) then
901 rollback to main_SP;
902 end if;
903
904 -- the batch operation was a validate so we rollback
905 if (p_batch_process_mode = 'V' AND l_line_error = FALSE) then
906 rollback to main_SP;
910 -- for each error message
907 end if;
908
909 for i in 1..l_count loop
911 update pay_adjust_batch_lines
912 set batch_line_status = 'E'
913 where batch_line_id = l_message_source_id_tbl(i)
914 and batch_group_id = group_csr.batch_group_id;
915 end loop;
916
917
918 /*
919 Any remainging unprocessed lines we set the status as `V` or `T` if line_error is FALSE otherwise as `V`.
920 Update the batch_group status as `V` or `T` if line_error is FALSE otherwise as `E`.
921 */
922
923 if (l_line_error = FALSE) then
924 ---
925 update pay_adjust_batch_lines
926 set batch_line_status = p_batch_process_mode
927 where batch_line_status = 'U'
928 and batch_group_id = group_csr.batch_group_id;
929 --
930 update pay_adjust_batch_groups
931 set batch_group_status = p_batch_process_mode
932 where batch_group_id = group_csr.batch_group_id;
933 ---
934 elsif (l_line_error = TRUE) then
935 ---
936 update pay_adjust_batch_lines
937 set batch_line_status = 'V'
938 where batch_line_status = 'U'
939 and batch_group_id = group_csr.batch_group_id;
940 --
941 update pay_adjust_batch_groups
942 set batch_group_status = 'E'
943 where batch_group_id = group_csr.batch_group_id;
944 ---
945 end if;
946
947 /*
948 For each error message in PL/SQL table create an entry in pay_message_line
949 */
950 for j in 1..l_count loop
951 --
952 if l_message_text_tbl(j) is not null then
953 insert into pay_message_lines
954 (line_sequence,
955 payroll_id,
956 message_level,
957 source_id,
958 source_type,
959 line_text)
960 values
961 (pay_message_lines_s.nextval,
962 null,
963 l_message_level_tbl(j),
964 l_message_source_id_tbl(j),
965 l_message_source_type_tbl(j),
966 l_message_text_tbl(j)
967 );
968 end if;
969 --
970 end loop; -- for inserting into pay_message_lines
971
972 commit;
973
974 -- the main exception block handling errors in initial checking like if payroll_id, consolidation_id
975 -- are valid.
976 exception
977 when invalid_batch_group_details
978 then
979 rollback to main_SP;
980 --
981 when process_batch_failed
982 then
983 update pay_adjust_batch_groups
984 set batch_group_status = 'E'
985 where batch_group_id = group_csr.batch_group_id;
986 --put a message against batch_group in pay_message_lines at group_level
987 begin
988 select pml.source_id id into dummy_msg_source_id
989 from pay_message_lines pml
990 where pml.source_id = group_csr.batch_group_id
991 and source_type = 'G';
992 exception
993 when no_data_found then
994 hr_utility.set_message(801, 'PAY_34294_BBABP_ERR_IN_PROC');
995 l_proc := 'validating the batch group';
996 hr_utility.set_message_token('PROC', l_proc);
997 l_error_text := substrb(hr_utility.get_message || ' ' ||
998 sqlerrm, 1, 240);
999 insert into pay_message_lines
1000 (line_sequence,
1001 payroll_id,
1002 message_level,
1003 source_id,
1004 source_type,
1005 line_text)
1006 values
1007 (pay_message_lines_s.nextval,
1008 null,
1009 'F',
1010 group_csr.batch_group_id,
1011 'G',
1012 l_error_text);
1013
1014
1015 end;
1016 --
1017
1018 commit;
1019 --
1020 end; -- batch groups end
1021 end loop; -- batch groups end loop
1022
1023
1024
1025 open batch_grp_stts('T');
1026 -- check if there are any transferred group
1027 fetch batch_grp_stts into status_T;
1028 close batch_grp_stts;
1029 open batch_grp_stts('E');
1030 -- check if there are any errored group
1031 fetch batch_grp_stts into status_E;
1032 close batch_grp_stts;
1033
1034 if (status_T = 1) then
1035 update pay_balance_batch_headers
1036 set batch_status = 'T'
1037 where batch_id = p_batch_id;
1038 elsif ((status_T = 0) and (status_E = 1)) then
1039 update pay_balance_batch_headers
1040 set batch_status = 'E'
1041 where batch_id = p_batch_id;
1042 elsif ((status_T = 0) and (status_E = 0)) then
1043 update pay_balance_batch_headers
1044 set batch_status = 'V'
1045 where batch_id = p_batch_id;
1046 end if;
1047 end validate_and_transfer; -- end of procedure
1048 --
1049 --
1050 --
1051
1052 ------------------------------------------------------------------------------
1053 --|--------------------------------< validate_batch >-----------------|
1054 ------------------------------------------------------------------------------
1055 --
1056 -- Description:
1057 -- This procedure validates the batch specified
1058 --------------------------------------------------------------------------------
1059 procedure validate_batch
1060 (p_batch_id in number,
1061 p_batch_group_id in number
1062 )is
1063 begin
1064
1065 validate_and_transfer(p_batch_id,
1066 p_batch_group_id,
1067 'V');
1068
1069 end validate_batch;
1070 --
1071
1072 ------------------------------------------------------------------------------
1076 -- Description:
1073 --|--------------------------------< transfer_batch >-----------------|
1074 ------------------------------------------------------------------------------
1075 --
1077 -- This procedure transfers the batch specified
1078 --------------------------------------------------------------------------------
1079 procedure transfer_batch(p_batch_id in number,
1080 p_batch_group_id in number)
1081 is
1082 begin
1083 validate_and_transfer (p_batch_id,
1084 p_batch_group_id,
1085 'T'
1086 );
1087 end transfer_batch;
1088 --
1089
1090
1091
1092 -- ----------------------------------------------------------------------------
1093 -- |-----------------------------< run_process >------------------------------|
1094 -- ----------------------------------------------------------------------------
1095 --
1096 -- Description:
1097 -- This procedure is used in the executable of the bba process.
1098 --
1099 -- ----------------------------------------------------------------------------
1100 --
1101 procedure run_process
1102 (errbuf out nocopy varchar2,
1103 retcode out nocopy number,
1104 p_batch_operation in varchar2,
1105 p_batch_id in number,
1106 p_batch_group_id in number
1107 ) is
1108 --
1109 l_proc varchar2(72) := 'pay_batch_balance_adj_pkg.run_process';
1110 l_batch_status pay_balance_batch_headers.batch_status%type;
1111 --
1112 begin
1113 --
1114 hr_utility.set_location('Entering ' || l_proc, 10);
1115
1116 savepoint rprc;
1117
1118 l_batch_status := batch_overall_status(p_batch_id => p_batch_id);
1119
1120 if not check_operation_allowed (p_batch_status => l_batch_status, p_process_mode => p_batch_operation) then
1121
1122 hr_utility.set_location('Leaving: '||l_proc, 10);
1123 fnd_message.set_name('PAY','PAY_34292_BBABP_INV_OPERATION');
1124 fnd_message.set_token('BATCHOP', p_batch_operation);
1125 fnd_message.set_token('STATUS', l_batch_status);
1126 fnd_message.raise_error;
1127
1128 end if;
1129
1130 hr_utility.set_location(l_proc,20);
1131
1132 if p_batch_operation = 'ROLLBACK' then
1133
1134 hr_utility.set_location(l_proc,30);
1135
1136 pay_batch_balance_adj_pkg.rollback_batch
1137 ( p_batch_id => p_batch_id,
1138 p_batch_group_id => p_batch_group_id );
1139 hr_utility.set_location(l_proc,40);
1140
1141 elsif p_batch_operation = 'PURGE' then
1142
1143 hr_utility.set_location(l_proc,50);
1144
1145 pay_batch_balance_adj_pkg.purge
1146 ( p_batch_id => p_batch_id,
1147 p_batch_group_id => p_batch_group_id );
1148
1149 hr_utility.set_location(l_proc,60);
1150
1151 elsif p_batch_operation = 'VALIDATE' then
1152
1153 hr_utility.set_location(l_proc,70);
1154
1155 pay_batch_balance_adj_pkg.validate_batch
1156 ( p_batch_id => p_batch_id,
1157 p_batch_group_id => p_batch_group_id );
1158 hr_utility.set_location(l_proc,80);
1159
1160 elsif p_batch_operation = 'TRANSFER' then
1161
1162 hr_utility.set_location(l_proc,90);
1163
1164 pay_batch_balance_adj_pkg.transfer_batch
1165 ( p_batch_id => p_batch_id,
1166 p_batch_group_id => p_batch_group_id );
1167 hr_utility.set_location(l_proc,100);
1168 end if;
1169
1170 hr_utility.set_location(l_proc,110);
1171
1172 errbuf := null;
1173 retcode := 0;
1174
1175 hr_utility.set_location('Leaving ' || l_proc, 120);
1176 --
1177 exception
1178 when others then
1179 rollback to rprc;
1180 errbuf := sqlerrm;
1181 retcode := 2;
1182 hr_utility.set_location(l_proc,130);
1183 raise;
1184 --
1185 end run_process;
1186 --
1187 END PAY_BATCH_BALANCE_ADJ_PKG;