[Home] [Help]
PACKAGE BODY: APPS.HXC_RETRIEVAL_ROLLBACK
Source
1 PACKAGE BODY HXC_RETRIEVAL_ROLLBACK AS
2 /* $Header: hxcrollret.pkb 120.1.12020000.2 2013/03/12 10:13:57 asrajago noship $ */
3
4
5 -- Exception type for all BULK Insert/Updates
6 TABLE_EXCEPTION EXCEPTION;
7 PRAGMA EXCEPTION_INIT(TABLE_EXCEPTION,-24381);
8 INVALID_DETAIL EXCEPTION;
9 PRAGMA EXCEPTION_INIT(INVALID_DETAIL,-6502);
10
11
12 TYPE BATCHARRAY IS TABLE OF NUMTAB INDEX BY VARCHAR2(50);
13
14 -- Global Variables
15 g_reverse_batches BATCHARRAY;
16 g_seq NUMBER := 0;
17 g_cache VARCHAR2(5) := 'Y';
18 g_bee_process_id NUMBER;
19 g_validate_mode VARCHAR2(50) := 'VALIDATE';
20 g_current_timecard NUMBER;
21 g_debug BOOLEAN := hr_utility.debug_enabled;
22
23
24 -- Function to return Person Name for a given person id
25 -- Stores once retrieved person names in Cache so that
26 -- next time they are not queried.
27
28 FUNCTION person_name(p_person_id IN NUMBER)
29 RETURN VARCHAR2
30 IS
31
32 CURSOR get_person_name
33 IS SELECT full_name
34 FROM per_all_people_f
35 WHERE person_id = p_person_id
36 AND SYSDATE BETWEEN effective_start_date
37 AND effective_end_date ;
38
39 l_person_name VARCHAR2(400);
40
41 BEGIN
42
43 IF NOT g_person_name.EXISTS(TO_CHAR(p_person_id))
44 THEN
45
46 OPEN get_person_name;
47
48 FETCH get_person_name
49 INTO l_person_name;
50
51 CLOSE get_person_name;
52
53 g_person_name(TO_CHAR(p_person_id)) := l_person_name;
54
55 RETURN l_person_name;
56
57 ELSE
58
59 RETURN g_person_name(TO_CHAR(p_person_id));
60
61 END IF;
62
63 END person_name;
64
65
66 -- Function to return Element Name for a given Element id
67 -- Stores once retrieved Element names in Cache so that
68 -- next time they are not queried.
69
70 FUNCTION element_name(p_element_id IN NUMBER)
71 RETURN VARCHAR2
72 IS
73
74 CURSOR get_element_name
75 IS SELECT element_name
76 FROM pay_element_types_f
77 WHERE element_type_id = p_element_id
78 AND SYSDATE BETWEEN effective_start_date
79 AND effective_end_date;
80
81 l_element_name VARCHAR2(50):= 'XXXX';
82
83 BEGIN
84
85 IF NOT g_element_name.EXISTS(TO_CHAR(p_element_id))
86 THEN
87
88 OPEN get_element_name;
89
90 FETCH get_element_name
91 INTO l_element_name;
92
93 CLOSE get_element_name;
94
95 g_element_name(TO_CHAR(p_element_id)) := l_element_name;
96
97 RETURN l_element_name;
98
99 ELSE
100
101 RETURN g_element_name(TO_CHAR(p_element_id));
102
103 END IF;
104
105 END element_name;
106
107
108 -- Function to return Batch Name for a given Batch id
109 -- Stores once retrieved Batch names in Cache so that
110 -- next time they are not queried.
111
112 FUNCTION batch_name(p_batch_id IN NUMBER)
113 RETURN VARCHAR2
114 IS
115
116 CURSOR get_batch_name
117 IS SELECT batch_name,
118 batch_status
119 FROM pay_batch_headers
120 WHERE batch_id = p_batch_id;
121
122 l_batch_name VARCHAR2(40);
123 l_batch_status VARCHAR2(10);
124
125 BEGIN
126 IF p_batch_id IS NULL
127 THEN
128
129 RETURN ' ';
130
131 END IF;
132
133 IF NOT g_batch_name.EXISTS(TO_CHAR(p_batch_id))
134 THEN
135
136 OPEN get_batch_name;
137
138 FETCH get_batch_name
139 INTO l_batch_name,l_batch_status;
140
141 CLOSE get_batch_name;
142
143 g_batch_name(TO_CHAR(p_batch_id)) := l_batch_name;
144 g_batch_status(TO_CHAR(p_batch_id)) := l_batch_status;
145
146 RETURN l_batch_name;
147
148 ELSE
149
150 RETURN g_batch_name(TO_CHAR(p_batch_id));
151
152 END IF;
153
154 END batch_name;
155
156 -- Function to return Batch Status for a given Batch id
157 -- Stores once retrieved Batch Statuses in Cache so that
158 -- next time they are not queried.
159
160 FUNCTION batch_status(p_batch_id IN NUMBER)
161 RETURN VARCHAR2
162 IS
163
164 CURSOR get_batch_name
165 IS SELECT batch_name,
166 batch_status
167 FROM pay_batch_headers
168 WHERE batch_id = p_batch_id;
169
170 l_batch_name VARCHAR2(40);
171 l_batch_status VARCHAR2(10);
172
173 BEGIN
174 IF p_batch_id IS NULL
175 THEN
176 RETURN ' ';
177 END IF;
178 IF NOT g_batch_status.EXISTS(TO_CHAR(p_batch_id))
179 THEN
180 OPEN get_batch_name;
181 FETCH get_batch_name INTO l_batch_name,l_batch_status;
182 CLOSE get_batch_name;
183
184 g_batch_name(TO_CHAR(p_batch_id)) := l_batch_name;
185 g_batch_status(TO_CHAR(p_batch_id)) := l_batch_status;
186 RETURN l_batch_status;
187 ELSE
188 RETURN g_batch_status(TO_CHAR(p_batch_id));
189 END IF;
190
191 END batch_status;
192
193 -- Bug 13777315
194 -- Selects and caches the Data Set status.
195
196 FUNCTION data_set_status(p_data_set_id IN NUMBER)
197 RETURN VARCHAR2
198 IS
199
200 CURSOR get_data_set_status(p_data_set_id IN NUMBER)
201 IS SELECT status
202 FROM hxc_data_sets
203 WHERE data_set_id = p_data_set_id;
204
205 l_status VARCHAR2(50);
206
207 BEGIN
208
209 IF g_data_set_status.EXISTS(TO_CHAR(p_data_set_id))
210 THEN
211 RETURN g_data_set_status(TO_CHAR(p_data_set_id));
212 ELSE
213 log('Data Set id '||p_data_set_id);
214 OPEN get_data_set_status(p_data_set_id);
215 FETCH get_data_set_status INTO l_status;
216 CLOSE get_data_set_status;
217
218 log('Status :'||l_status);
219
220 g_data_set_status(TO_CHAR(p_data_set_id)) := l_status;
221
222 END IF;
223
224 END data_set_status;
225
226
227 -- Procedure to issue a COMMIT each time it is called
228 -- respecting the Validate mode
229 -- If running on Validate Mode, skips the COMMIT
230
231 PROCEDURE commit_session
232 IS
233
234 BEGIN
235
236 IF g_validate_mode <> 'VALIDATE'
237 THEN
238 log('Committing ');
239 COMMIT;
240 ELSE
241 log('Not committing');
242 END IF;
243 RETURN;
244 END commit_session;
245
246
247 -- Procedure to verify if this timecard has any details existing in
248 -- HXT schema
249 -- Caches the details for once verified timecard so that we dont query
250 -- again and again
251
252 FUNCTION verify_otlr_details(p_timecard_id IN NUMBER)
253 RETURN VARCHAR2
254 IS
255
256 CURSOR get_otlr_details(p_timecard_id IN NUMBER)
257 IS SELECT sum.id,
258 tim.batch_id
259 FROM hxc_ret_pay_latest_details ret,
260 hxt_sum_hours_worked_f sum,
261 hxt_timecards_f tim
262 WHERE ret.timecard_id = p_timecard_id
263 AND ret.time_building_block_id = sum.time_building_block_id
264 AND sum.tim_id = tim.id
265 AND ROWNUM < 2;
266
267 l_id NUMBER;
268 l_batch_id NUMBER;
269
270
271 BEGIN
272
273 IF g_otlr_status.EXISTS(TO_CHAR(p_timecard_id))
274 THEN
275
276 RETURN g_otlr_status(TO_CHAR(p_timecard_id));
277
278 ELSE
279
280 OPEN get_otlr_details(p_timecard_id);
281
282 FETCH get_otlr_details
283 INTO l_id,
284 l_batch_id;
285
286 IF get_otlr_details%NOTFOUND
287 THEN
288
289 g_otlr_status(TO_CHAR(p_timecard_id)) := 'NO';
290
291 ELSE
292
293 -- Xferred at least once with OTLR = Yes, so mark the value in Cache
294 -- and report the error.
295
296 g_otlr_status(TO_CHAR(p_timecard_id)) := 'YES';
297
298 log('This timecard was transferred with OTLR rules YES at least once',g_cache);
299 log('Batch Name :'||batch_name(l_batch_id),g_cache);
300
301 END IF;
302
303 CLOSE get_otlr_details;
304
305 RETURN g_otlr_status(TO_CHAR(p_timecard_id));
306
307 END IF;
308
309 END verify_otlr_details;
310
311 -- Bug 13777315
312
313 FUNCTION verify_if_archived(p_timecard_id IN NUMBER)
314 RETURN VARCHAR2
315 IS
316
317 CURSOR get_data_set_id(p_timecard_id NUMBER)
318 IS SELECT data_set_id
319 FROM hxc_timecard_summary
320 WHERE timecard_id = p_timecard_id;
321
322 l_data_set_id NUMBER;
323
324
325 BEGIN
326 IF g_archive_status.EXISTS(TO_CHAR(p_timecard_id))
327 THEN
328 RETURN g_archive_status(TO_CHAR(p_timecard_id));
329 ELSE
330 OPEN get_data_set_id(p_timecard_id);
331 FETCH get_data_set_id INTO l_data_set_id;
332 CLOSE get_data_set_id;
333
334 IF l_data_set_id IS NOT NULL
335 THEN
336 IF data_set_status(l_data_set_id) <> 'ON_LINE'
337 THEN
338 g_archive_status(TO_CHAR(p_timecard_id)) := 'YES';
339 log('Data Set :'||l_data_set_id);
340 log('Status '||data_Set_status(l_data_set_id));
341 log('This timecard may be Archived and cannot be processed',g_cache);
342 ELSE
343 g_archive_status(TO_CHAR(p_timecard_id)) := 'NO';
344 log('Data Set :'||l_data_set_id);
345 log('Status '||data_Set_status(l_data_set_id));
346 END IF;
347 ELSE
348 g_archive_status(TO_CHAR(p_timecard_id)) := 'NO';
349 END IF;
350 END IF;
351
352 RETURN g_archive_status(TO_CHAR(p_timecard_id));
353
354 END verify_if_archived;
355
356
357
358 -- Procedure to verify if this timecard was created after
359 -- rollback feature was installed.
360 -- Caches the results to avoid requerying again and again.
361
362
363 FUNCTION verify_rollback_eligibility(p_timecard_id IN NUMBER)
364 RETURN VARCHAR2
365 IS
366
367 CURSOR get_rollback_eligibility(p_timecard_id IN NUMBER)
368 IS SELECT timecard_id
369 FROM hxc_rollback_timecards_all
370 WHERE timecard_id = p_timecard_id
371 AND timecard_type = 'HXC_TIMECARD_SUMMARY';
372
373 l_id NUMBER;
374
375
376 BEGIN
377 IF g_all_rlbk_eligible.EXISTS(TO_CHAR(p_timecard_id))
378 THEN
379 RETURN g_all_rlbk_eligible(TO_CHAR(p_timecard_id));
380 ELSE
381 OPEN get_rollback_eligibility(p_timecard_id);
382 FETCH get_rollback_eligibility INTO l_id;
383
384 IF get_rollback_eligibility%NOTFOUND
385 THEN
386 g_all_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'NO';
387 log('System does not have enough details to Rollback this timecard',g_cache);
388 ELSE
389 g_all_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'YES';
390 END IF;
391 CLOSE get_rollback_eligibility;
392 RETURN g_all_rlbk_eligible(TO_CHAR(p_timecard_id));
393 END IF;
394
395 END verify_rollback_eligibility;
396
397
398 -- Procedure to verify if the latest details on this timecard
399 -- were once rolled back.
400 -- Caches the results to avoid requerying again and again.
401
402 FUNCTION verify_ld_rollback_eligibility(p_timecard_id IN NUMBER)
403 RETURN VARCHAR2
404 IS
405
406 CURSOR get_rollback_eligibility(p_timecard_id IN NUMBER)
407 IS SELECT timecard_id
408 FROM hxc_ld_rollback_timecards_all
409 WHERE timecard_id = p_timecard_id;
410
411 l_id NUMBER;
412
413
414 BEGIN
415 IF g_ld_rlbk_eligible.EXISTS(TO_CHAR(p_timecard_id))
416 THEN
417 RETURN g_ld_rlbk_eligible(TO_CHAR(p_timecard_id));
418 ELSE
419 OPEN get_rollback_eligibility(p_timecard_id);
420 FETCH get_rollback_eligibility INTO l_id;
421
422 IF get_rollback_eligibility%NOTFOUND
423 THEN
424 g_ld_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'NO';
425 log('Last Changes on this timecard were once rolled back. Rollback all details.',g_cache);
426 ELSE
427 g_ld_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'YES';
428 END IF;
429 CLOSE get_rollback_eligibility;
430 RETURN g_ld_rlbk_eligible(TO_CHAR(p_timecard_id));
431 END IF;
432
433 END verify_ld_rollback_eligibility;
434
435 -- Procedure to delete the latest rollback eligibility of a timecard
436 -- once such a rollback is complete. This timecard will now be eligible
437 -- for rollback only after it is retrieved again.
438
439 PROCEDURE invalidate_rollback_ld(p_timecard_id IN NUMBER)
440 IS
441
442 BEGIN
443
444 log('Deleting latest rollback record -- this timecard is ineligible for Rollback latest details until changed further');
445 DELETE FROM hxc_ld_rollback_timecards_all
446 WHERE timecard_id = p_timecard_id;
447
448 END invalidate_rollback_ld;
449
450 -- Procedure to verify batch status for a given list of batches
451 -- Takes in a table of batches and verifies their statuses.
452
453 FUNCTION verify_batch_status(p_tab NUMTAB)
454 RETURN VARCHAR2
455 IS
456
457 l_outcome VARCHAR2(10) := 'SUCCESS';
458
459 BEGIN
460 IF p_tab.COUNT > 0
461 THEN
462 FOR i IN p_tab.FIRST..p_tab.LAST
463 LOOP
464 IF p_tab(i) IS NOT NULL
465 THEN
466 IF batch_status(p_tab(i)) NOT IN ('U','V')
467 THEN
468 log('Batch '||batch_name(p_tab(i))||' has wrong status ');
469 -- Bug 13844693
470 -- Message for the Output File
471 IF g_rlbk_status = 'U'
472 THEN
473 log('Batch '||batch_name(p_tab(i))||' has Transferred status and we are not rolling back these ',g_cache);
474 END IF;
475 l_outcome := 'ERROR';
476 END IF;
477 END IF;
478 END LOOP;
479 END IF;
480 RETURN l_outcome;
481
482 END verify_batch_status;
483
484 --Procedure to update transferred to for a given timecard id.
485 -- Trims out Payroll from the already updated values.
486
487 PROCEDURE update_transferred_to(p_timecard_id IN NUMBER)
488 IS
489
490 CURSOR get_transferred_to
491 IS SELECT transferred_to
492 FROM hxc_timecard_summary
493 WHERE timecard_id = p_timecard_id;
494
495 l_transferred_to VARCHAR2(100);
496
497 BEGIN
498
499 OPEN get_transferred_to;
500 FETCH get_transferred_to INTO l_transferred_to;
501 CLOSE get_transferred_to;
502
503 log('Transferred to for this timecard '||l_transferred_to);
504
505 l_transferred_to := LTRIM(RTRIM(REPLACE(l_transferred_to,'Payroll'),','),',');
506
507 log('Modified Transferred to for this timecard '||l_transferred_to);
508
509 UPDATE hxc_timecard_summary
510 SET transferred_to = l_transferred_to
511 WHERE timecard_id = p_timecard_id;
512
513 END update_transferred_to;
514
515
516
517 -- Deletes any old log that exists in the table for Logging.
518 -- Covers any scenario where an ORA error stopped the earlier
519 -- run.
520
521 PROCEDURE delete_old_log
522 IS
523
524 PRAGMA AUTONOMOUS_TRANSACTION;
525
526 BEGIN
527
528 DELETE FROM hxc_rollback_log
529 WHERE type = 'HXCROLL';
530 COMMIT;
531
532 END delete_old_log;
533
534
535
536 -- Displays cached log in the Output File and deletes the cached log.
537
538 PROCEDURE display_cached_log
539 IS
540
541 PRAGMA AUTONOMOUS_TRANSACTION;
542
543 CURSOR get_log
544 IS SELECT text
545 FROM hxc_rollback_log
546 WHERE type = 'HXCROLL'
547 ORDER BY seq;
548
549
550
551 l_tab VARCHARTAB;
552
553 BEGIN
554
555 FND_FILE.put_line(FND_file.output,' ');
556 FND_FILE.put_line(FND_file.output,' ');
557 FND_FILE.put_line(FND_file.output,' ');
558 FND_FILE.put_line(FND_file.output,' ');
559 FND_FILE.put_line(FND_file.output,' ');
560 FND_FILE.put_line(FND_file.output,' ');
561 FND_FILE.put_line(FND_file.output,'**************************************************************************************************');
562 FND_FILE.put_line(FND_file.output,' Rollback Report ');
563 FND_FILE.put_line(FND_file.output,'**************************************************************************************************');
564 FND_FILE.put_line(FND_file.output,' ');
565 FND_FILE.put_line(FND_file.output,' ');
566 FND_FILE.put_line(FND_file.output,' ');
567
568
569 OPEN get_log;
570 LOOP
571 FETCH get_log BULK COLLECT INTO l_tab LIMIT 100;
572 EXIT WHEN l_tab.COUNT = 0;
573
574 FOR i IN l_tab.FIRST..l_tab.LAST
575 LOOP
576 FND_FILE.put_line(FND_file.output,l_tab(i));
577 END LOOP;
578 END LOOP;
579 CLOSE get_log;
580
581 DELETE FROM hxc_rollback_log;
582 COMMIT;
583
584
585 END display_cached_log;
586
587
588
589 -- An internal logging procedure used to display
590 -- any given table of Numbers along with a header.
591
592 PROCEDURE display_numtab (p_text VARCHAR2,
593 p_list NUMTAB)
594 IS
595
596 BEGIN
597 log('Displaying '||p_text);
598 log;
599 IF p_list.COUNT > 0
600 THEN
601 FOR i IN p_list.FIRST..p_list.LAST
602 LOOP
603 log(p_list(i));
604 END LOOP;
605 END IF;
606 log;
607 END display_numtab;
608
609
610
611 -- Function to return a comma separated string with the numbers
612 -- passed.
613
614 FUNCTION string_list(p_list NUMTAB)
615 RETURN VARCHAR2
616 IS
617
618 -- Bug 16446212
619 l_string VARCHAR2(32000) := ' ';
620
621 BEGIN
622 IF p_list.COUNT > 0
623 THEN
624
625 FOR i IN p_list.FIRST..p_list.LAST
626 LOOP
627 l_string := l_string||p_list(i)||',';
628 END LOOP;
629
630 l_string := RTRIM(l_string,',');
631
632 RETURN l_string;
633 ELSE
634
635 RETURN ' ';
636
637 END IF;
638
639 END string_list;
640
641
642 -- Scans thru the batches provided and verifies if they are
643 -- included in the global list of batches.
644 -- If not, we mark the timecards as tainted.
645 -- Can be used for more validations if required.
646
647 PROCEDURE mark_tainted_timecards(p_timecard_id IN NUMBER,
648 p_batch_id IN NUMBER,
649 p_retro_batch_id IN NUMBER)
650 IS
651
652 l_tab NUMTAB;
653
654 BEGIN
655 log('Checking taint');
656 log(p_timecard_id);
657 log(p_batch_id);
658 log(p_retro_batch_id);
659
660 -- Need to check if timecard is not already tainted
661 -- or not already checked for taint
662
663 IF ( g_tainted_timecards.EXISTS(TO_CHAR(p_timecard_id))
664 AND g_tainted_timecards(TO_CHAR(p_timecard_id)) = '0')
665 OR ( NOT g_tainted_timecards.EXISTS(TO_CHAR(p_timecard_id)))
666 THEN
667
668 -- Verify for the batch_id passed.
669 l_tab := NUMTAB(p_batch_id);
670
671 IF p_batch_id IS NOT NULL
672 AND l_tab MULTISET INTERSECT g_batches_to_process
673 <> l_tab
674 THEN
675 -- The given batch_id is not in the list of batches.
676 g_tainted_timecards(TO_CHAR(p_timecard_id)) := '1';
677
678 -- If we are not processing dependent batches, this is an issue.
679 IF NOT g_process_dependent_batches
680 THEN
681 log('Batch '||batch_name(p_batch_id)
682 ||' is a dependent batch and we are not processing dependent batches',g_cache);
683 END IF;
684
685 log('Setting taint for '||p_batch_id);
686
687 -- Log the error, set the taint and return.
688 RETURN;
689
690 END IF;
691
692 -- Verify for the retro_batch_id passed
693
694 l_tab := NUMTAB(p_retro_batch_id);
695
696 IF p_retro_batch_id IS NOT NULL
697 AND l_tab MULTISET INTERSECT g_batches_to_process
698 <> l_tab
699 THEN
700 -- Given retro batch is not in the list of batches.
701
702 g_tainted_timecards(TO_CHAR(p_timecard_id)) := '1';
703
704 -- If we are not processing dependent batches, this is an issue
705 IF NOT g_process_dependent_batches
706 THEN
707 log('Batch '||batch_name(p_retro_batch_id)
708 ||' is a dependent batch and we are not processing dependent batches',g_cache);
709 END IF;
710
711 log('Setting taint for '||p_retro_batch_id);
712
713 RETURN;
714 END IF;
715
716 -- If we are here, there is no issue with the batches.
717 -- Set taint to 0
718
719 g_tainted_timecards(TO_CHAR(p_timecard_id)) := 0;
720
721 RETURN;
722
723 END IF;
724
725 END mark_tainted_timecards;
726
727 PROCEDURE mark_tainted_timecards2(p_timecard_id IN NUMBER,
728 p_batch_id IN NUMBER,
729 p_retro_batch_id IN NUMBER)
730 IS
731 BEGIN
732 log('Checking taint2');
733 log(p_timecard_id);
734 log(p_batch_id);
735 log(p_retro_batch_id);
736 IF ( g_tainted_timecards2.EXISTS(TO_CHAR(p_timecard_id))
737 AND g_tainted_timecards2(TO_CHAR(p_timecard_id)) = '0')
738 OR ( NOT g_tainted_timecards2.EXISTS(TO_CHAR(p_timecard_id)))
739 THEN
740 IF p_batch_id IS NOT NULL
741 AND batch_status(p_batch_id) NOT IN ('U','V')
742 THEN
743 g_tainted_timecards2(TO_CHAR(p_timecard_id)) := '1';
744 IF g_rlbk_status = 'U'
745 THEN
746 log('Batch '||batch_name(p_batch_id)
747 ||' is transferred and we are not rolling back these',g_cache);
748 END IF;
749
750 log('Setting taint for '||p_batch_id);
751 RETURN;
752 END IF;
753
754 IF p_retro_batch_id IS NOT NULL
755 AND batch_status(p_retro_batch_id) NOT IN ('U','V')
756 THEN
757 g_tainted_timecards2(TO_CHAR(p_timecard_id)) := '1';
758 IF g_rlbk_status = 'U'
759 THEN
760 log('Batch '||batch_name(p_retro_batch_id)
761 ||' is transferred and we are not rolling back these',g_cache);
762 END IF;
763 log('Setting taint for '||p_retro_batch_id);
764 RETURN;
765 END IF;
766
767 g_tainted_timecards2(TO_CHAR(p_timecard_id)) := 0;
768 RETURN;
769 END IF;
770
771
772 END mark_tainted_timecards2;
773
774
775 -- Function to verify if a given timecard is tainted.
776 -- Checks the global table created above in mark_tainted_timecards.
777
778 FUNCTION is_tainted(p_timecard_id IN NUMBER)
779 RETURN BOOLEAN
780 IS
781
782 BEGIN
783 IF g_tainted_timecards.EXISTS(TO_CHAR(p_timecard_id))
784 AND g_tainted_timecards(TO_CHAR(p_timecard_id)) = 1
785 AND NOT g_process_dependent_batches
786 THEN
787
788 log('There is a dependent batch issue ');
789
790 RETURN TRUE;
791
792 ELSIF g_tainted_timecards2.EXISTS(TO_CHAR(p_timecard_id))
793 AND g_tainted_timecards2(TO_CHAR(p_timecard_id)) = 1
794 AND g_rlbk_status = 'U'
795 THEN
796
797 log('There is a transferred batch issue');
798 RETURN TRUE;
799
800 ELSE
801
802 RETURN FALSE;
803
804 END IF;
805
806 END is_tainted;
807
808
809 -- Init procedure to start fresh
810
811 PROCEDURE init
812 IS
813
814 CURSOR get_process_id
815 IS SELECT retrieval_process_id
816 FROM hxc_retrieval_processes
817 WHERE name = 'BEE Retrieval Process';
818
819 l_process_id NUMBER;
820
821 BEGIN
822 g_reverse_batches.DELETE;
823 g_hours_iv_position.DELETE;
824 g_reversal_batches.DELETE;
825 g_rev_batch_seq.DELETE;
826 g_tainted_timecards.DELETE;
827 delete_old_log;
828
829 OPEN get_process_id;
830 FETCH get_process_id INTO l_process_id;
831 CLOSE get_process_id;
832
833 g_bee_process_id := l_process_id;
834
835
836 END init;
837
838
839
840 -- Function to create and/or return reversal batch for a given
841 -- batch. Once created, reversal batches are cached for later use.
842
843 FUNCTION reversal_batch(p_batch_id IN NUMBER)
844 RETURN NUMBER
845 IS
846
847 l_new_batch NUMBER;
848 l_object_version_number NUMBER;
849
850 BEGIN
851
852 IF g_reversal_batches.EXISTS(p_batch_id)
853 THEN
854
855 IF g_debug
856 THEN
857 hr_utility.trace('Reversal batch already created '||p_batch_id);
858 END IF;
859
860 RETURN g_reversal_batches(TO_CHAR(p_batch_id));
861
862 ELSE
863 IF g_debug
864 THEN
865 hr_utility.trace('There is no reversal batch yet, creating one');
866 END IF;
867
868 pay_batch_element_entry_api.create_batch_header
869 (p_session_date => SYSDATE,
870 p_batch_name => 'REV_'||p_batch_id||'_'||FND_GLOBAL.conc_request_id,
871 p_business_group_id => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
872 p_batch_reference => 'REV_'||p_batch_id,
873 p_batch_source => 'Time Store',
874 p_action_if_exists => 'I',
875 p_batch_id => l_new_batch,
876 p_object_version_number => l_object_version_number
877 );
878
879 log('Replacement HEADER created for '||p_batch_id);
880 log('Replacement Header Id '||l_new_batch);
881 log('Replacement Header Name '||batch_name(l_new_batch));
882
883 -- Cache the new batch
884 g_reversal_batches(TO_CHAR(p_batch_id)) := l_new_batch;
885
886 RETURN l_new_batch;
887
888 END IF;
889
890 END reversal_batch;
891
892 -- Function to return batch_sequence for a new line
893 -- Called whenever a new line has to be created in a batch
894
895 FUNCTION reversal_batch_sequence(p_batch_id IN NUMBER)
896 RETURN NUMBER
897 IS
898
899 BEGIN
900 -- If it does not exists, return 1 and cache 1.
901 IF NOT g_rev_batch_seq.EXISTS(TO_CHAR(p_batch_id))
902 THEN
903
904 g_rev_batch_seq(TO_CHAR(p_batch_id)) := 1;
905 RETURN 1;
906
907 -- If it exists, increment one to the last value and return.
908 ELSE
909
910 g_rev_batch_seq(TO_CHAR(p_batch_id)) :=
911 g_rev_batch_seq(TO_CHAR(p_batch_id)) + 1;
912 RETURN g_rev_batch_seq(TO_CHAR(p_batch_id));
913
914 END IF;
915
916 END reversal_batch_sequence;
917
918
919
920 -- Function to reverse a given line for any batch.
921 -- Takes in one line ( rowtype of table PAY_BATCH_LINES)
922 -- and returns with reversed Hours
923 -- Eg. -8 for 8
924
925 FUNCTION reversed_line(p_lines PAY_BATCH_LINES%ROWTYPE)
926 RETURN pay_batch_lines%ROWTYPE
927 IS
928
929 CURSOR get_sequence(p_element_type_id IN NUMBER)
930 IS SELECT display_sequence
931 FROM pay_input_values_f
932 WHERE element_type_id = p_element_type_id
933 AND name = 'Hours';
934
935
936 l_sequence NUMBER;
937 l_out_lines pay_batch_lines%ROWTYPE;
938
939 BEGIN
940
941 -- If we already saw this element type id before, we know its
942 -- IV position for Hours.
943 -- If not, fetch the Hours IV position and cache it.
944
945 IF NOT g_hours_iv_position.EXISTS(TO_CHAR(p_lines.element_type_id))
946 THEN
947
948 OPEN get_sequence(p_lines.element_type_id);
949
950 FETCH get_sequence
951 INTO l_sequence;
952
953 CLOSE get_sequence;
954
955 g_hours_iv_position(TO_CHAR(p_lines.element_type_id)) := l_sequence;
956
957 END IF;
958
959 l_sequence := g_hours_iv_position(TO_CHAR(p_lines.element_type_id));
960 l_out_lines := p_lines;
961
962 -- As per the returned sequence, adjust Hours IV value.
963
964 IF l_sequence = 1
965 THEN
966 l_out_lines.value_1 := -1*TO_NUMBER(l_out_lines.value_1 );
967 ELSIF l_sequence = 2
968 THEN
969 l_out_lines.value_2 := -1*TO_NUMBER(l_out_lines.value_2 );
970 ELSIF l_sequence = 3
971 THEN
972 l_out_lines.value_3 := -1*TO_NUMBER(l_out_lines.value_3 );
973 ELSIF l_sequence = 4
974 THEN
975 l_out_lines.value_4 := -1*TO_NUMBER(l_out_lines.value_4 );
976 ELSIF l_sequence = 5
977 THEN
978 l_out_lines.value_5 := -1*TO_NUMBER(l_out_lines.value_5 );
979 ELSIF l_sequence = 6
980 THEN
981 l_out_lines.value_6 := -1*TO_NUMBER(l_out_lines.value_6 );
982 ELSIF l_sequence = 7
983 THEN
984 l_out_lines.value_7 := -1*TO_NUMBER(l_out_lines.value_7 );
985 ELSIF l_sequence = 8
986 THEN
987 l_out_lines.value_8 := -1*TO_NUMBER(l_out_lines.value_8 );
988 ELSIF l_sequence = 9
989 THEN
990 l_out_lines.value_9 := -1*TO_NUMBER(l_out_lines.value_9 );
991 ELSIF l_sequence = 10
992 THEN
993 l_out_lines.value_10 := -1*TO_NUMBER(l_out_lines.value_10);
994 ELSIF l_sequence = 11
995 THEN
996 l_out_lines.value_11 := -1*TO_NUMBER(l_out_lines.value_11);
997 ELSIF l_sequence = 12
998 THEN
999 l_out_lines.value_12 := -1*TO_NUMBER(l_out_lines.value_12);
1000 ELSIF l_sequence = 13
1001 THEN
1002 l_out_lines.value_13 := -1*TO_NUMBER(l_out_lines.value_13);
1003 ELSIF l_sequence = 14
1004 THEN
1005 l_out_lines.value_14 := -1*TO_NUMBER(l_out_lines.value_14);
1006 ELSIF l_sequence = 15
1007 THEN
1008 l_out_lines.value_15 := -1*TO_NUMBER(l_out_lines.value_15);
1009 END IF;
1010
1011 -- Return the reversed line
1012 RETURN l_out_lines;
1013
1014 END reversed_line;
1015
1016
1017
1018 -- Internal function to PIPE out the numbers for a given
1019 -- table of NUMBERS.
1020 -- Used in queries.
1021
1022 FUNCTION return_ids(p_lines NUMTAB)
1023 RETURN NUMTAB
1024 PIPELINED
1025 IS
1026
1027
1028 BEGIN
1029 FOR i IN p_lines.FIRST..p_lines.LAST
1030 LOOP
1031
1032 PIPE ROW(p_lines(i));
1033
1034 END LOOP;
1035 RETURN;
1036
1037 END return_ids;
1038
1039 -- Procedure to display Emp Name, emp_number, start/stop times
1040 -- etc for the timecard.
1041
1042 PROCEDURE display_tc_details( p_timecard_id IN NUMBER)
1043 IS
1044
1045 CURSOR get_timecard_details(p_timecard_id IN NUMBER)
1046 IS SELECT time_building_block_id,
1047 tc.object_version_number,
1048 start_time,
1049 TRUNC(stop_time),
1050 full_name,
1051 NVL(employee_number,npw_number)
1052 FROM hxc_time_building_blocks tc,
1053 per_all_people_f ppf
1054 WHERE tc.time_building_block_id = p_timecard_id
1055 AND tc.resource_id = ppf.person_id
1056 AND SYSDATE BETWEEN ppf.effective_start_date
1057 AND ppf.effective_end_date
1058 ORDER BY tc.object_version_number DESC;
1059
1060
1061 l_tc_id NUMBER;
1062 l_tc_ovn NUMBER;
1063 l_person_name VARCHAR2(400);
1064 l_start_time DATE;
1065 l_stop_time DATE;
1066 l_emp_number VARCHAR2(50);
1067
1068 BEGIN
1069 -- We store in cache the last processed timecard.
1070 -- If we are getting repeated calls, no need to display details again.
1071
1072 IF p_timecard_id = g_current_timecard
1073 THEN
1074 log('Continuing ...');
1075 RETURN;
1076
1077 ELSE
1078 -- There is a new timecard, so change the current timecard, and proceed to display.
1079
1080 g_current_timecard := p_timecard_id;
1081
1082 OPEN get_timecard_details(p_timecard_id);
1083
1084 FETCH get_timecard_details INTO l_tc_id,
1085 l_tc_ovn,
1086 l_start_time,
1087 l_stop_time,
1088 l_person_name,
1089 l_emp_number;
1090 CLOSE get_timecard_details;
1091
1092 -- Display the details here
1093 log;
1094 log('Person: '||l_person_name||'('||l_emp_number||')');
1095 log('Start Time: '||l_start_time);
1096 log('Stop Time: '||l_stop_time);
1097 log('Timecard_id: '||l_tc_id);
1098 log('Timecard_ovn: '||l_tc_ovn);
1099 log;
1100
1101 END IF;
1102
1103 END display_tc_details;
1104
1105
1106
1107 -- Procedure to display Emp Name, emp_number, start/stop times
1108 -- etc for the timecard.
1109
1110 PROCEDURE display_tc_details2( p_timecard_id IN NUMBER)
1111 IS
1112
1113 CURSOR get_timecard_details(p_timecard_id IN NUMBER)
1114 IS SELECT time_building_block_id,
1115 tc.object_version_number,
1116 start_time,
1117 TRUNC(stop_time),
1118 full_name,
1119 NVL(employee_number,npw_number)
1120 FROM hxc_time_building_blocks tc,
1121 per_all_people_f ppf
1122 WHERE tc.time_building_block_id = p_timecard_id
1123 AND tc.resource_id = ppf.person_id
1124 AND SYSDATE BETWEEN ppf.effective_start_date
1125 AND ppf.effective_end_date;
1126
1127 l_tc_id NUMBER;
1128 l_tc_ovn NUMBER;
1129 l_person_name VARCHAR2(400);
1130 l_start_time DATE;
1131 l_stop_time DATE;
1132 l_emp_number VARCHAR2(50);
1133
1134 BEGIN
1135 g_current_timecard := p_timecard_id;
1136
1137 OPEN get_timecard_details(p_timecard_id);
1138
1139 FETCH get_timecard_details INTO l_tc_id,
1140 l_tc_ovn,
1141 l_start_time,
1142 l_stop_time,
1143 l_person_name,
1144 l_emp_number;
1145 CLOSE get_timecard_details;
1146
1147 log(' ',g_cache);
1148 log(' ===============================================================================',g_cache);
1149 log(' '||l_person_name||'('||l_emp_number||') '||l_start_time||' - '||l_stop_time,g_cache);
1150 log(' ===============================================================================',g_cache);
1151 log(' ',g_cache);
1152
1153
1154 END display_tc_details2;
1155
1156
1157
1158 -- Procedure to reverse batch lines
1159
1160 PROCEDURE reverse_batch_lines(p_batch_id IN NUMBER,
1161 p_lines IN NUMTAB)
1162 IS
1163
1164 l_lines NUMTAB;
1165
1166 CURSOR get_batch_status
1167 IS SELECT batch_status
1168 FROM pay_batch_headers
1169 WHERE batch_id = p_batch_id;
1170 /*
1171 CURSOR get_lines
1172 IS SELECT batch_line_id,
1173 object_version_number
1174 FROM pay_batch_lines pbl,
1175 TABLE(return_ids(l_lines)) list
1176 WHERE list.column_value = pbl.batch_line_id;
1177 */ -- Commented for the bug 13832327
1178
1179 -- Bug 16446212
1180 l_sql VARCHAR2(32000) :=
1181 'SELECT *
1182 FROM pay_batch_lines
1183 WHERE batch_line_id IN ';
1184
1185 l_sql2 VARCHAR2(32000) :=
1186 'SELECT *
1187 FROM pay_batch_lines
1188 WHERE batch_line_id IN ';
1189
1190 l_lines_list VARCHAR2(32000) := '(';
1191 l_lines_list2 VARCHAR2(32000) := '(';
1192
1193 TYPE LINESTAB IS TABLE OF pay_batch_lines%ROWTYPE;
1194
1195 l_status VARCHAR2(5);
1196 line_tab LINESTAB;
1197 ovn_tab NUMTAB;
1198 l_object_version_number NUMBER;
1199 l_batch_line_id NUMBER;
1200
1201 l_space VARCHAR2(15) := ' |';
1202 l_space1 VARCHAR2(15) := ' |';
1203 l_line VARCHAR2(15) := '==|';
1204
1205
1206 BEGIN
1207
1208 OPEN get_batch_status;
1209
1210 FETCH get_batch_status INTO l_status;
1211
1212 CLOSE get_batch_status;
1213
1214 log(' ',g_cache);
1215 log(' Batch: '||batch_name(p_batch_id),g_cache);
1216 log(' Batch Status : '||l_status,g_cache);
1217
1218 IF l_status = 'U'
1219 THEN
1220 log(' Rollback Method: Reversed by deleting lines',g_cache);
1221 ELSE
1222 log(' Rollback Method: Reversed by Creating a retro batch',g_cache);
1223 END IF;
1224
1225
1226 FOR n IN p_lines.FIRST..p_lines.LAST
1227 LOOP
1228 l_lines_list := l_lines_list||p_lines(n)||',';
1229 END LOOP;
1230
1231 l_lines_list := RTRIM(l_lines_list,',');
1232 l_lines_list := l_lines_list||')';
1233 l_sql := l_sql||l_lines_list;
1234 log(l_sql);
1235
1236 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO line_tab;
1237
1238 log(l_space1||RPAD('===============',10)||
1239 l_line||
1240 RPAD('=====',5)||
1241 l_line||
1242 RPAD('===============',15)||
1243 l_line||
1244 RPAD('===============',10)||
1245 l_line||
1246 RPAD('========================================================',50)||
1247 l_line||
1248 RPAD('===============',10)||'|',g_cache);
1249
1250 log(l_space1||RPAD('Batch Line',10)||
1251 l_space||
1252 RPAD('OVN',5)||
1253 l_space||
1254 RPAD('Element Name',15)||
1255 l_space||
1256 RPAD('Date',10)||
1257 l_space||
1258 RPAD('Values 1-10',50)||
1259 l_space||
1260 RPAD('Cost Allocation KFlex Id',10)||'|',g_cache);
1261
1262 log(l_space1||RPAD('===============',10)||
1263 l_line||
1264 RPAD('=====',5)||
1265 l_line||
1266 RPAD('===============',15)||
1267 l_line||
1268 RPAD('===============',10)||
1269 l_line||
1270 RPAD('========================================================',50)||
1271 l_line||
1272 RPAD('===============',10)||'|',g_cache);
1273
1274 FOR i IN line_tab.FIRST..line_tab.LAST
1275 LOOP
1276
1277 log(l_space1||RPAD(line_tab(i).batch_line_id,10)||
1278 l_space||
1279 RPAD(line_tab(i).object_version_number,5)||
1280 l_space||
1281 RPAD(element_name(line_tab(i).element_type_id),15)||
1282 l_space||
1283 RPAD(TO_CHAR(line_tab(i).effective_date),10)||
1284 l_space||
1285 RPAD(NVL(line_tab(i).value_1||'/'||
1286 line_tab(i).value_2||'/'||
1287 line_tab(i).value_3||'/'||
1288 line_tab(i).value_4||'/'||
1289 line_tab(i).value_5||'/'||
1290 line_tab(i).value_6||'/'||
1291 line_tab(i).value_7||'/'||
1292 line_tab(i).value_8||'/'||
1293 line_tab(i).value_9||'/'||
1294 line_tab(i).value_10,
1295 ' '),50)||
1296 l_space||
1297 RPAD(NVL(TO_CHAR(line_tab(i).cost_allocation_keyflex_id),' '),10)||'|',g_cache);
1298
1299 END LOOP;
1300
1301 log(l_space1||RPAD('===============',10)||
1302 l_line||
1303 RPAD('=====',5)||
1304 l_line||
1305 RPAD('===============',15)||
1306 l_line||
1307 RPAD('===============',10)||
1308 l_line||
1309 RPAD('========================================================',50)||
1310 l_line||
1311 RPAD('===============',10)||'|',g_cache);
1312
1313
1314 IF l_status IN ('U','V')
1315 THEN
1316
1317 log('Deleting lines: Batch status '||l_status);
1318 log(' ',g_cache);
1319 l_lines := p_lines;
1320
1321 FOR i IN line_tab.FIRST..line_tab.LAST
1322 LOOP
1323 pay_batch_element_entry_api.delete_batch_line
1324 ( p_batch_line_id => line_tab(i).batch_line_id,
1325 p_object_version_number => line_tab(i).object_version_number);
1326 END LOOP;
1327
1328
1329 ELSIF l_status = 'T'
1330 THEN
1331 log(' ',g_cache);
1332 l_lines := p_lines;
1333
1334 FOR i IN line_tab.FIRST..line_tab.LAST
1335 LOOP
1336 line_tab(i) := reversed_line(line_tab(i));
1337 pay_batch_element_entry_api.create_batch_line
1338 (p_session_date => SYSDATE,
1339 p_batch_id => reversal_batch(line_tab(i).batch_id),
1340 p_assignment_id => line_tab(i).assignment_id,
1341 p_assignment_number => line_tab(i).assignment_number,
1342 p_batch_sequence => reversal_batch_sequence(line_tab(i).batch_id),
1343 p_concatenated_segments => line_tab(i).concatenated_segments,
1344 p_cost_allocation_keyflex_id => line_tab(i).cost_allocation_keyflex_id,
1345 p_date_earned => line_tab(i).date_earned,
1346 p_effective_date => line_tab(i).effective_date,
1347 p_effective_start_date => line_tab(i).effective_date,
1348 p_effective_end_date => line_tab(i).effective_date,
1349 p_element_name => line_tab(i).element_name,
1350 p_element_type_id => line_tab(i).element_type_id,
1351 p_segment1 => line_tab(i).segment1,
1352 p_segment2 => line_tab(i).segment2,
1353 p_segment3 => line_tab(i).segment3,
1354 p_segment4 => line_tab(i).segment4,
1355 p_segment5 => line_tab(i).segment5,
1356 p_segment6 => line_tab(i).segment6,
1357 p_segment7 => line_tab(i).segment7,
1358 p_segment8 => line_tab(i).segment8,
1359 p_segment9 => line_tab(i).segment9,
1360 p_segment10 => line_tab(i).segment10,
1361 p_segment11 => line_tab(i).segment11,
1362 p_segment12 => line_tab(i).segment12,
1363 p_segment13 => line_tab(i).segment13,
1364 p_segment14 => line_tab(i).segment14,
1365 p_segment15 => line_tab(i).segment15,
1366 p_segment16 => line_tab(i).segment16,
1367 p_segment17 => line_tab(i).segment17,
1368 p_segment18 => line_tab(i).segment18,
1369 p_segment19 => line_tab(i).segment19,
1370 p_segment20 => line_tab(i).segment20,
1371 p_segment21 => line_tab(i).segment21,
1372 p_segment22 => line_tab(i).segment22,
1373 p_segment23 => line_tab(i).segment23,
1374 p_segment24 => line_tab(i).segment24,
1375 p_segment25 => line_tab(i).segment25,
1376 p_segment26 => line_tab(i).segment26,
1377 p_segment27 => line_tab(i).segment27,
1378 p_segment28 => line_tab(i).segment28,
1379 p_segment29 => line_tab(i).segment29,
1380 p_segment30 => line_tab(i).segment30,
1381 p_value_1 => line_tab(i).value_1 ,
1382 p_value_2 => line_tab(i).value_2 ,
1383 p_value_3 => line_tab(i).value_3 ,
1384 p_value_4 => line_tab(i).value_4 ,
1385 p_value_5 => line_tab(i).value_5 ,
1386 p_value_6 => line_tab(i).value_6 ,
1387 p_value_7 => line_tab(i).value_7 ,
1388 p_value_8 => line_tab(i).value_8 ,
1389 p_value_9 => line_tab(i).value_9 ,
1390 p_value_10 => line_tab(i).value_10,
1391 p_value_11 => line_tab(i).value_11,
1392 p_value_12 => line_tab(i).value_12,
1393 p_value_13 => line_tab(i).value_13,
1394 p_value_14 => line_tab(i).value_14,
1395 p_value_15 => line_tab(i).value_15,
1396 p_batch_line_id => l_batch_line_id,
1397 p_object_version_number => l_object_version_number
1398 );
1399
1400 log(l_batch_line_id||' Created ');
1401 log(l_object_version_number||' Created ');
1402
1403 l_lines_list2 := l_lines_list2||l_batch_line_id||',';
1404
1405 END LOOP;
1406
1407 l_lines_list2 := RTRIM(l_lines_list2,',');
1408 l_lines_list2 := l_lines_list2||')';
1409 l_sql2 := l_sql2||l_lines_list2;
1410 log(l_sql2);
1411
1412 line_tab := LINESTAB();
1413
1414
1415
1416 EXECUTE IMMEDIATE l_sql2 BULK COLLECT INTO line_tab;
1417
1418 log(' ',g_cache);
1419 log(' Reversal Batch: '||batch_name(line_tab(line_tab.FIRST).batch_id),g_cache);
1420 log(' ',g_cache);
1421
1422
1423 log(l_space1||RPAD('===============',10)||
1424 l_line||
1425 RPAD('=====',5)||
1426 l_line||
1427 RPAD('===============',15)||
1428 l_line||
1429 RPAD('===============',10)||
1430 l_line||
1431 RPAD('========================================================',50)||
1432 l_line||
1433 RPAD('===============',10)||'|',g_cache);
1434
1435 log(l_space1||RPAD('Batch Line',10)||
1436 l_space||
1437 RPAD('OVN',5)||
1438 l_space||
1439 RPAD('Element Name',15)||
1440 l_space||
1441 RPAD('Date',10)||
1442 l_space||
1443 RPAD('Values 1-10',50)||
1444 l_space||
1445 RPAD('Cost Allocation KFlex Id',10)||'|',g_cache);
1446
1447 log(l_space1||RPAD('===============',10)||
1448 l_line||
1449 RPAD('=====',5)||
1450 l_line||
1451 RPAD('===============',15)||
1452 l_line||
1453 RPAD('===============',10)||
1454 l_line||
1455 RPAD('========================================================',50)||
1456 l_line||
1457 RPAD('===============',10)||'|',g_cache);
1458
1459 FOR i IN line_tab.FIRST..line_tab.LAST
1460 LOOP
1461 log(l_space1||RPAD(line_tab(i).batch_line_id,10)||
1462 l_space||
1463 RPAD(line_tab(i).object_version_number,5)||
1464 l_space||
1465 RPAD(element_name(line_tab(i).element_type_id),15)||
1466 l_space||
1467 RPAD(TO_CHAR(line_tab(i).effective_date),10)||
1468 l_space||
1469 RPAD(NVL(line_tab(i).value_1||'/'||
1470 line_tab(i).value_2||'/'||
1471 line_tab(i).value_3||'/'||
1472 line_tab(i).value_4||'/'||
1473 line_tab(i).value_5||'/'||
1474 line_tab(i).value_6||'/'||
1475 line_tab(i).value_7||'/'||
1476 line_tab(i).value_8||'/'||
1477 line_tab(i).value_9||'/'||
1478 line_tab(i).value_10,
1479 ' '),50)||
1480 l_space||
1481 RPAD(NVL(To_char(line_tab(i).cost_allocation_keyflex_id),' '),10)||'|',g_cache);
1482
1483 END LOOP;
1484 log(l_space1||RPAD('===============',10)||
1485 l_line||
1486 RPAD('=====',5)||
1487 l_line||
1488 RPAD('===============',15)||
1489 l_line||
1490 RPAD('===============',10)||
1491 l_line||
1492 RPAD('========================================================',50)||
1493 l_line||
1494 RPAD('===============',10)||'|',g_cache);
1495
1496
1497 END IF;
1498
1499
1500 END reverse_batch_lines;
1501
1502
1503
1504 PROCEDURE gather_snapshot(p_timecard_id IN NUMBER)
1505 IS
1506
1507 CURSOR get_measure
1508 IS SELECT NVL(htb.measure,(htb.stop_time-htb.start_time)*24),
1509 htb.time_building_block_id,
1510 htb.application_set_id
1511 FROM hxc_pay_latest_details hld,
1512 hxc_time_building_blocks htb
1513 WHERE hld.timecard_id = p_timecard_id
1514 AND htb.time_building_block_id = hld.time_building_block_id
1515 AND htb.object_version_number = hld.object_version_number;
1516
1517 CURSOR get_attribute
1518 IS SELECT REPLACE(hta.attribute_category,'ELEMENT - '),
1519 hld.time_building_block_id
1520 FROM hxc_pay_latest_details hld,
1521 hxc_time_attribute_usages hau,
1522 hxc_time_attributes hta
1523 WHERE hld.timecard_id = p_timecard_id
1524 AND hau.time_building_block_id = hld.time_building_block_id
1525 AND hau.time_building_block_ovn = hld.object_version_number
1526 AND hta.time_attribute_id = hau.time_attribute_id
1527 AND hta.attribute_category LIKE 'ELEMENT%';
1528
1529 l_dettab NUMTAB;
1530 l_measure NUMTAB;
1531 l_attribute NUMTAB;
1532 l_application NUMTAB;
1533
1534 BEGIN
1535
1536 OPEN get_measure;
1537 FETCH get_measure BULK COLLECT INTO l_measure,l_dettab,l_application;
1538 CLOSE get_measure;
1539
1540 IF l_measure.COUNT > 0
1541 THEN
1542 FORALL i IN l_measure.FIRST..l_measure.LAST
1543 UPDATE hxc_pay_latest_details
1544 SET measure = l_measure(i),
1545 application_set_id = l_application(i)
1546 WHERE time_building_block_id = l_dettab(i);
1547 END IF;
1548
1549 OPEN get_attribute;
1550 FETCH get_attribute BULK COLLECT INTO l_attribute,
1551 l_dettab;
1552 CLOSE get_attribute;
1553
1554 IF l_attribute.COUNT > 0
1555 THEN
1556 FORALL i IN l_attribute.FIRST..l_attribute.LAST
1557 UPDATE hxc_pay_latest_details
1558 SET attribute1 = l_attribute(i)
1559 WHERE time_building_block_id = l_dettab(i);
1560 END IF;
1561
1562 END gather_snapshot;
1563
1564
1565
1566 PROCEDURE rollback_latest_retrieval( p_bg_id IN NUMBER,
1567 p_session_date IN VARCHAR2,
1568 p_start_date IN VARCHAR2,
1569 p_end_date IN VARCHAR2,
1570 p_gre_id IN NUMBER DEFAULT NULL,
1571 p_organization_id IN NUMBER DEFAULT NULL,
1572 p_location_id IN NUMBER DEFAULT NULL,
1573 p_payroll_id IN NUMBER DEFAULT NULL,
1574 p_person_id IN NUMBER DEFAULT NULL,
1575 p_batches_list IN VARCHAR2,
1576 p_request_id IN NUMBER
1577 )
1578 IS
1579
1580 l_where_clause VARCHAR2(4000);
1581
1582 -- Bug 16446212
1583 l_query VARCHAR2(32000) :=
1584 'SELECT /*+ LEADING(tc)
1585 INDEX(det HXC_RET_PAY_LATEST_DETAILS_N3)
1586 INDEX(tc HXC_TIMECARD_SUMMARY_FK1) */
1587 DISTINCT det.timecard_id,batch_id,retro_batch_id
1588 FROM hxc_ret_pay_latest_details det,
1589 hxc_timecard_summary tc
1590 WHERE tc.timecard_id = det.timecard_id
1591 ';
1592
1593
1594 CURSOR get_both_lines(p_timecard_id IN NUMBER,
1595 p_batch_id IN NUMBER,
1596 p_retro_batch_id IN NUMBER)
1597 IS SELECT latest.old_batch_id,
1598 latest.request_id,
1599 latest.old_request_id,
1600 latest.resource_id,
1601 latest.time_building_block_id,
1602 latest.approval_status,
1603 latest.start_time,
1604 latest.stop_time,
1605 latest.org_id,
1606 latest.business_group_id,
1607 latest.timecard_id,
1608 latest.attribute1,
1609 latest.attribute2,
1610 latest.attribute3,
1611 latest.measure,
1612 latest.object_version_number,
1613 latest.old_ovn,
1614 latest.old_measure,
1615 latest.old_attribute1,
1616 latest.old_attribute2,
1617 latest.old_attribute3,
1618 latest.pbl_id,
1619 latest.retro_pbl_id,
1620 latest.old_pbl_id,
1621 latest.batch_id,
1622 latest.retro_batch_id,
1623 latest.application_set_id,
1624 latest.comment_text,
1625 latest.last_update_date,
1626 latest.resource_type,
1627 htd.transaction_detail_id,
1628 ht.transaction_id
1629 FROM hxc_ret_pay_latest_details latest,
1630 hxc_ret_pay_details det,
1631 hxc_transaction_details htd,
1632 hxc_transactions ht
1633 WHERE det.timecard_id = p_timecard_id
1634 AND det.batch_id = p_batch_id
1635 AND det.retro_batch_id = p_retro_batch_id
1636 AND det.time_building_block_id = latest.time_building_block_id
1637 AND det.object_version_number = latest.object_version_number
1638 AND latest.object_version_number = htd.time_building_block_ovn
1639 AND latest.time_building_block_id = htd.time_building_block_id
1640 AND htd.transaction_id = ht.transaction_id
1641 AND ht.type = 'RETRIEVAL'
1642 AND ht.transaction_process_id = g_bee_process_id;
1643
1644
1645
1646 CURSOR get_normal_lines(p_timecard_id IN NUMBER,
1647 p_batch_id IN NUMBER)
1648 IS SELECT latest.old_batch_id,
1649 latest.request_id,
1650 latest.old_request_id,
1651 latest.resource_id,
1652 latest.time_building_block_id,
1653 latest.approval_status,
1654 latest.start_time,
1655 latest.stop_time,
1656 latest.org_id,
1657 latest.business_group_id,
1658 latest.timecard_id,
1659 latest.attribute1,
1660 latest.attribute2,
1661 latest.attribute3,
1662 latest.measure,
1663 latest.object_version_number,
1664 latest.old_ovn,
1665 latest.old_measure,
1666 latest.old_attribute1,
1667 latest.old_attribute2,
1668 latest.old_attribute3,
1669 latest.pbl_id,
1670 latest.retro_pbl_id,
1671 latest.old_pbl_id,
1672 latest.batch_id,
1673 latest.retro_batch_id,
1674 latest.application_set_id,
1675 latest.comment_text,
1676 latest.last_update_date,
1677 latest.resource_type,
1678 htd.transaction_detail_id,
1679 ht.transaction_id
1680 FROM hxc_ret_pay_latest_details latest,
1681 hxc_ret_pay_details det,
1682 hxc_transaction_details htd,
1683 hxc_transactions ht
1684 WHERE det.timecard_id = p_timecard_id
1685 AND det.batch_id = p_batch_id
1686 AND det.retro_batch_id IS NULL
1687 AND det.time_building_block_id = latest.time_building_block_id
1688 AND det.object_version_number = latest.object_version_number
1689 AND latest.object_version_number = htd.time_building_block_ovn
1690 AND latest.time_building_block_id = htd.time_building_block_id
1691 AND htd.transaction_id = ht.transaction_id
1692 AND ht.type = 'RETRIEVAL'
1693 AND ht.transaction_process_id = g_bee_process_id;
1694
1695
1696
1697 CURSOR get_retro_lines(p_timecard_id IN NUMBER,
1698 p_retro_batch_id IN NUMBER)
1699 IS SELECT latest.old_batch_id,
1700 latest.request_id,
1701 latest.old_request_id,
1702 latest.resource_id,
1703 latest.time_building_block_id,
1704 latest.approval_status,
1705 latest.start_time,
1706 latest.stop_time,
1707 latest.org_id,
1708 latest.business_group_id,
1709 latest.timecard_id,
1710 latest.attribute1,
1711 latest.attribute2,
1712 latest.attribute3,
1713 latest.measure,
1714 latest.object_version_number,
1715 latest.old_ovn,
1716 latest.old_measure,
1717 latest.old_attribute1,
1718 latest.old_attribute2,
1719 latest.old_attribute3,
1720 latest.pbl_id,
1721 latest.retro_pbl_id,
1722 latest.old_pbl_id,
1723 latest.batch_id,
1724 latest.retro_batch_id,
1725 latest.application_set_id,
1726 latest.comment_text,
1727 latest.last_update_date,
1728 latest.resource_type,
1729 htd.transaction_detail_id,
1730 ht.transaction_id
1731 FROM hxc_ret_pay_latest_details latest,
1732 hxc_ret_pay_details det,
1733 hxc_transaction_details htd,
1734 hxc_transactions ht
1735 WHERE det.timecard_id = p_timecard_id
1736 AND det.batch_id IS NULL
1737 AND det.retro_batch_id = p_retro_batch_id
1738 AND det.time_building_block_id = latest.time_building_block_id
1739 AND det.object_version_number = latest.object_version_number
1740 AND latest.object_version_number = htd.time_building_block_ovn
1741 AND latest.time_building_block_id = htd.time_building_block_id
1742 AND htd.transaction_id = ht.transaction_id
1743 AND ht.type = 'RETRIEVAL'
1744 AND ht.transaction_process_id = g_bee_process_id;
1745
1746
1747
1748
1749 l_person_list VARCHAR2(4000);
1750 l_asg_criteria VARCHAR2(4000);
1751 l_location_filter VARCHAR2(100);
1752 l_org_filter VARCHAR2(100);
1753 l_payroll_filter VARCHAR2(100);
1754 l_start_date_filter VARCHAR2(100);
1755 l_end_date_filter VARCHAR2(100);
1756
1757 -- Bug 16446212
1758 l_batch_filter VARCHAR2(32000);
1759 l_retro_filter VARCHAR2(32000);
1760
1761 l_tc_tab NUMTAB;
1762 l_normal_tab NUMTAB;
1763 l_retro_tab NUMTAB;
1764
1765
1766
1767 l_lines_old_batch_id NUMTAB ;
1768 l_lines_request_id NUMTAB ;
1769 l_lines_old_request_id NUMTAB ;
1770 l_lines_resource_id NUMTAB ;
1771 l_lines_time_building_block_id NUMTAB ;
1772 l_lines_approval_status VARCHARTAB ;
1773 l_lines_start_time DATETAB ;
1774 l_lines_stop_time DATETAB ;
1775 l_lines_org_id NUMTAB ;
1776 l_lines_business_group_id NUMTAB ;
1777 l_lines_timecard_id NUMTAB ;
1778 l_lines_attribute1 VARCHARTAB ;
1779 l_lines_attribute2 VARCHARTAB ;
1780 l_lines_attribute3 VARCHARTAB ;
1781 l_lines_measure NUMTAB ;
1782 l_lines_object_version_number NUMTAB ;
1783 l_lines_old_ovn NUMTAB ;
1784 l_lines_old_measure NUMTAB ;
1785 l_lines_old_attribute1 VARCHARTAB ;
1786 l_lines_old_attribute2 VARCHARTAB ;
1787 l_lines_old_attribute3 VARCHARTAB ;
1788 l_lines_pbl_id NUMTAB ;
1789 l_lines_retro_pbl_id NUMTAB ;
1790 l_lines_old_pbl_id NUMTAB ;
1791 l_lines_batch_id NUMTAB ;
1792 l_lines_retro_batch_id NUMTAB ;
1793 l_lines_application_set_id NUMTAB ;
1794 l_lines_comment_text VARCHARTAB ;
1795 l_lines_last_update_date DATETAB ;
1796 l_lines_resource_type VARCHARTAB ;
1797 l_lines_td_id NUMTAB;
1798 l_lines_trans_id NUMTAB;
1799
1800
1801 g_reverse_batches BATCHARRAY;
1802 l_pointer NUMBER;
1803 l_index VARCHAR2(50);
1804
1805 -- Bug 13814489
1806 l_space VARCHAR2(15) := ' |';
1807 l_line VARCHAR2(15) := '==|';
1808
1809 l_ora_error BOOLEAN;
1810
1811 BEGIN
1812
1813
1814 IF p_person_id IS NOT NULL
1815 THEN
1816 l_person_list :='AND tc.resource_id = '||p_person_id;
1817 l_query := l_query||l_person_list;
1818 END IF;
1819
1820 l_batch_filter := FND_GLOBAL.newline||' AND ( batch_id IN '||p_batches_list||FND_GLOBAL.newline||
1821 ' OR retro_batch_id in '||p_batches_list||')';
1822
1823
1824
1825
1826
1827 IF p_start_date IS NOT NULL
1828 THEN
1829 l_start_date_filter := FND_GLOBAL.newline||' AND tc.start_time >= '''||p_start_date||'''';
1830 END IF;
1831
1832 IF p_end_date IS NOT NULL
1833 THEN
1834 l_end_date_filter := FND_GLOBAL.newline||' AND TRUNC(tc.stop_time) <= '''||p_end_date||'''';
1835 END IF;
1836
1837 IF p_location_id IS NOT NULL
1838 THEN
1839 l_location_filter := FND_GLOBAL.newline||' AND paf.location_id = '||p_location_id;
1840 END iF;
1841
1842 IF p_organization_id IS NOT NULL
1843 THEN
1844 l_org_filter := FND_GLOBAL.newline||' AND paf.organization_id = '||p_organization_id;
1845 END iF;
1846
1847 IF p_payroll_id IS NOT NULL
1848 THEN
1849 l_payroll_filter := FND_GLOBAL.newline||' AND paf.payroll_id = '||p_payroll_id;
1850 END iF;
1851
1852
1853 IF p_gre_id IS NULL
1854 THEN
1855 l_asg_criteria := FND_GLOBAL.newline||' AND tc.resource_id IN ( SELECT person_id
1856 FROM per_all_assignments_f paf
1857 WHERE business_group_id = '||p_bg_id
1858 ||l_location_filter
1859 ||l_org_filter
1860 ||l_payroll_filter
1861 ||')';
1862 ELSIF p_gre_id IS NOT NULL
1863 THEN
1864 l_asg_criteria := FND_GLOBAL.newline||' AND tc.resource_id IN ( SELECT person_id
1865 FROM per_all_assignments_f paf,
1866 hr_soft_coding_keyflex hsk
1867 WHERE business_group_id = '||p_bg_id
1868 ||l_location_filter
1869 ||l_org_filter
1870 ||l_payroll_filter
1871 ||FND_GLOBAL.newline||' AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
1872 AND hsk.segment1 = '''||p_gre_id||''''
1873 ||')';
1874 END IF;
1875
1876
1877
1878 l_query := l_query||l_start_date_filter||l_end_date_filter
1879 ||l_batch_filter||l_asg_criteria;
1880 l_query := l_query||' ORDER BY det.timecard_id ';
1881
1882 log(l_query);
1883
1884 EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_tc_tab,l_normal_tab,l_retro_tab;
1885
1886 log('Printing timecards ');
1887
1888 IF l_tc_tab.COUNT > 0
1889 THEN
1890
1891 for i IN l_tc_tab.FIRST..l_tc_tab.LAST
1892 LOOP
1893 log(l_tc_tab(i)||'-'||l_normal_tab(i)||'-'||l_retro_tab(i));
1894 END LOOP;
1895
1896 FOR x IN l_tc_tab.FIRST..l_tc_tab.LAST
1897 LOOP
1898 <<CONTINUE_TO_NEXT_TIMECARD>>
1899 LOOP
1900
1901 IF NOT l_tc_tab.EXISTS(x-1)
1902 OR ( l_tc_tab.EXISTS(x-1)
1903 AND l_tc_tab(x-1) <> l_tc_tab(x) )
1904 THEN
1905 display_tc_details(l_tc_tab(x));
1906 display_tc_details2(l_tc_tab(x));
1907 log('Setting savepoint');
1908 SAVEPOINT TC_START;
1909 END IF;
1910
1911 IF verify_rollback_eligibility(l_tc_tab(x)) = 'NO'
1912 THEN
1913 ROLLBACK TO TC_START;
1914 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
1915 EXIT CONTINUE_TO_NEXT_TIMECARD;
1916 END IF;
1917
1918 IF verify_ld_rollback_eligibility(l_tc_tab(x)) = 'NO'
1919 THEN
1920 ROLLBACK TO TC_START;
1921 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
1922 EXIT CONTINUE_TO_NEXT_TIMECARD;
1923 END IF;
1924
1925 IF verify_otlr_details(l_tc_tab(x)) = 'YES'
1926 THEN
1927 ROLLBACK TO TC_START;
1928 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
1929 EXIT CONTINUE_TO_NEXT_TIMECARD;
1930 END IF;
1931
1932 -- Bug 13777315
1933 IF verify_if_archived(l_tc_tab(x)) = 'YES'
1934 THEN
1935 ROLLBACK TO TC_START;
1936 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
1937 EXIT CONTINUE_TO_NEXT_TIMECARD;
1938 END IF;
1939
1940
1941
1942 mark_tainted_timecards(l_tc_tab(x),
1943 l_normal_tab(x),
1944 l_retro_tab(x));
1945
1946 mark_tainted_timecards2(l_tc_tab(x),
1947 l_normal_tab(x),
1948 l_retro_tab(x));
1949
1950
1951 IF l_tc_tab.EXISTS(x+1)
1952 AND l_tc_tab(x+1) = l_tc_tab(x)
1953 THEN
1954 mark_tainted_timecards(l_tc_tab(x+1),
1955 l_normal_tab(x+1),
1956 l_retro_tab(x+1));
1957 mark_tainted_timecards2(l_tc_tab(x+1),
1958 l_normal_tab(x+1),
1959 l_retro_tab(x+1));
1960
1961 END IF;
1962
1963 IF l_tc_tab.EXISTS(x+2)
1964 AND l_tc_tab(x+2) = l_tc_tab(x)
1965 THEN
1966 mark_tainted_timecards(l_tc_tab(x+2),
1967 l_normal_tab(x+2),
1968 l_retro_tab(x+2));
1969 mark_tainted_timecards2(l_tc_tab(x+2),
1970 l_normal_tab(x+2),
1971 l_retro_tab(x+2));
1972
1973 END IF;
1974 log('Is tainted check start ');
1975 IF is_tainted(l_tc_tab(x))
1976 THEN
1977 ROLLBACK TO TC_START;
1978 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
1979 EXIT CONTINUE_TO_NEXT_TIMECARD;
1980 END IF;
1981
1982
1983 IF l_retro_tab(x) IS NOT NULL
1984 AND l_normal_tab(x) IS NOT NULL
1985 THEN
1986 OPEN get_both_lines(l_tc_tab(x),l_normal_tab(x),l_retro_tab(x));
1987 FETCH get_both_lines BULK COLLECT INTO
1988 l_lines_old_batch_id ,
1989 l_lines_request_id ,
1990 l_lines_old_request_id ,
1991 l_lines_resource_id ,
1992 l_lines_time_building_block_id ,
1993 l_lines_approval_status ,
1994 l_lines_start_time ,
1995 l_lines_stop_time ,
1996 l_lines_org_id ,
1997 l_lines_business_group_id ,
1998 l_lines_timecard_id ,
1999 l_lines_attribute1 ,
2000 l_lines_attribute2 ,
2001 l_lines_attribute3 ,
2002 l_lines_measure ,
2003 l_lines_object_version_number ,
2004 l_lines_old_ovn ,
2005 l_lines_old_measure ,
2006 l_lines_old_attribute1 ,
2007 l_lines_old_attribute2 ,
2008 l_lines_old_attribute3 ,
2009 l_lines_pbl_id ,
2010 l_lines_retro_pbl_id ,
2011 l_lines_old_pbl_id ,
2012 l_lines_batch_id ,
2013 l_lines_retro_batch_id ,
2014 l_lines_application_set_id ,
2015 l_lines_comment_text ,
2016 l_lines_last_update_date ,
2017 l_lines_resource_type ,
2018 l_lines_td_id ,
2019 l_lines_trans_id ;
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033 CLOSE get_both_lines;
2034
2035 IF l_lines_time_building_block_id.COUNT > 0
2036 THEN
2037
2038 display_numtab('Transaction Details',l_lines_trans_id);
2039 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2040 UPDATE hxc_transaction_details
2041 SET status = 'ROLLEDBACK',
2042 exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
2043 WHERE transaction_detail_id = l_lines_trans_id(i);
2044
2045
2046 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2047 DELETE FROM hxc_ret_pay_details
2048 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2049 AND object_version_number = l_lines_object_version_number(i)
2050 AND batch_id = l_lines_batch_id(i)
2051 AND retro_batch_id = l_lines_retro_batch_id(i);
2052
2053 l_ora_error := FALSE;
2054 BEGIN
2055 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST SAVE EXCEPTIONS
2056 INSERT INTO hxc_pay_latest_details
2057 (
2058 resource_id,
2059 time_building_block_id,
2060 object_version_number,
2061 approval_status,
2062 start_time,
2063 stop_time,
2064 application_set_id,
2065 last_update_date,
2066 comment_text,
2067 resource_type ,
2068 org_id,
2069 business_group_id,
2070 timecard_id,
2071 attribute1,
2072 attribute2,
2073 attribute3,
2074 measure)
2075 VALUES (
2076 l_lines_resource_id(i),
2077 l_lines_time_building_block_id(i),
2078 l_lines_object_version_number(i),
2079 l_lines_approval_status(i),
2080 l_lines_start_time(i),
2081 l_lines_stop_time(i),
2082 l_lines_application_set_id(i),
2083 NVL(l_lines_last_update_date(i),SYSDATE),
2084 l_lines_comment_text(i),
2085 'PERSON',
2086 l_lines_org_id(i),
2087 l_lines_business_group_id(i),
2088 l_lines_timecard_id(i),
2089 l_lines_attribute1(i),
2090 l_lines_attribute2(i),
2091 l_lines_attribute3(i),
2092 l_lines_measure(i));
2093
2094 EXCEPTION
2095 WHEN TABLE_EXCEPTION
2096 THEN
2097 log(' Exception raised ');
2098 -- Bug 13777315
2099 IF SQL%BULK_EXCEPTIONS.COUNT > 0
2100 THEN
2101 log('List of Details with Exceptions ');
2102 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
2103 LOOP
2104 log('TBB_Id '||l_lines_time_building_block_id(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
2105 ' TBB_OVN '||l_lines_object_version_number(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
2106 log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
2107 IF SQL%BULK_EXCEPTIONS(n).ERROR_CODE <> 1
2108 THEN
2109 l_ora_error := TRUE;
2110 END IF;
2111 END LOOP;
2112 IF l_ora_error
2113 THEN
2114 RAISE INVALID_DETAIL;
2115 END IF;
2116
2117 END IF;
2118
2119 END;
2120
2121
2122 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2123 UPDATE hxc_ret_pay_latest_details
2124 SET attribute1 = l_lines_old_attribute1(i),
2125 attribute2 = l_lines_old_attribute2(i),
2126 attribute3 = l_lines_old_attribute3(i),
2127 measure = l_lines_old_measure(i),
2128 request_id = old_request_id,
2129 batch_id = old_batch_id,
2130 pbl_id = old_pbl_id,
2131 retro_batch_id = NULL,
2132 retro_pbl_id = NULL,
2133 comment_text = NULL,
2134 old_request_id = NULL,
2135 old_batch_id = NULL,
2136 old_pbl_id = NULL,
2137 old_attribute1 = NULL,
2138 old_attribute2 = NULL,
2139 old_attribute3 = NULL,
2140 old_measure = NULL,
2141 old_ovn = NULL
2142 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2143 AND object_version_number = l_lines_object_version_number(i);
2144
2145 END IF;
2146
2147
2148 ELSIF l_normal_tab(x) IS NOT NULL
2149 AND l_retro_tab(x) IS NULL
2150 THEN
2151
2152 OPEN get_normal_lines(l_tc_tab(x),l_normal_tab(x));
2153 FETCH get_normal_lines BULK COLLECT INTO
2154 l_lines_old_batch_id ,
2155 l_lines_request_id ,
2156 l_lines_old_request_id ,
2157 l_lines_resource_id ,
2158 l_lines_time_building_block_id ,
2159 l_lines_approval_status ,
2160 l_lines_start_time ,
2161 l_lines_stop_time ,
2162 l_lines_org_id ,
2163 l_lines_business_group_id ,
2164 l_lines_timecard_id ,
2165 l_lines_attribute1 ,
2166 l_lines_attribute2 ,
2167 l_lines_attribute3 ,
2168 l_lines_measure ,
2169 l_lines_object_version_number ,
2170 l_lines_old_ovn ,
2171 l_lines_old_measure ,
2172 l_lines_old_attribute1 ,
2173 l_lines_old_attribute2 ,
2174 l_lines_old_attribute3 ,
2175 l_lines_pbl_id ,
2176 l_lines_retro_pbl_id ,
2177 l_lines_old_pbl_id ,
2178 l_lines_batch_id ,
2179 l_lines_retro_batch_id ,
2180 l_lines_application_set_id ,
2181 l_lines_comment_text ,
2182 l_lines_last_update_date ,
2183 l_lines_resource_type ,
2184 l_lines_td_id ,
2185 l_lines_trans_id ;
2186 CLOSE get_normal_lines;
2187
2188 IF l_lines_time_building_block_id.COUNT > 0
2189 THEN
2190
2191
2192 display_numtab('Transaction Details',l_lines_trans_id);
2193 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2194 UPDATE hxc_transaction_details
2195 SET status = 'ROLLEDBACK',
2196 exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
2197 WHERE transaction_detail_id = l_lines_td_id(i);
2198
2199
2200 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2201 DELETE FROM hxc_ret_pay_details
2202 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2203 AND object_version_number = l_lines_object_version_number(i)
2204 AND batch_id = l_lines_batch_id(i)
2205 AND retro_batch_id IS NULL;
2206
2207 l_ora_error := FALSE;
2208 BEGIN
2209 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST SAVE EXCEPTIONS
2210 INSERT INTO hxc_pay_latest_details
2211 (
2212 resource_id,
2213 time_building_block_id,
2214 object_version_number,
2215 approval_status,
2216 start_time,
2217 stop_time,
2218 application_set_id,
2219 last_update_date,
2220 comment_text,
2221 resource_type ,
2222 org_id,
2223 business_group_id,
2224 timecard_id,
2225 attribute1,
2226 attribute2,
2227 attribute3,
2228 measure)
2229 VALUES (
2230 l_lines_resource_id(i),
2231 l_lines_time_building_block_id(i),
2232 l_lines_object_version_number(i),
2233 l_lines_approval_status(i),
2234 l_lines_start_time(i),
2235 l_lines_stop_time(i),
2236 l_lines_application_set_id(i),
2237 NVL(l_lines_last_update_date(i),SYSDATE),
2238 l_lines_comment_text(i),
2239 'PERSON',
2240 l_lines_org_id(i),
2241 l_lines_business_group_id(i),
2242 l_lines_timecard_id(i),
2243 l_lines_attribute1(i),
2244 l_lines_attribute2(i),
2245 l_lines_attribute3(i),
2246 l_lines_measure(i));
2247
2248 EXCEPTION
2249 WHEN TABLE_EXCEPTION
2250 THEN
2251 log(' Exception raised ');
2252 -- Bug 13777315
2253 IF SQL%BULK_EXCEPTIONS.COUNT > 0
2254 THEN
2255 log('List of Details with Exceptions ');
2256 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
2257 LOOP
2258 log('TBB_Id '||l_lines_time_building_block_id(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
2259 ' TBB_OVN '||l_lines_object_version_number(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
2260 log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
2261 IF SQL%BULK_EXCEPTIONS(n).ERROR_CODE <> 1
2262 THEN
2263 l_ora_error := TRUE;
2264 END IF;
2265 END LOOP;
2266 IF l_ora_error
2267 THEN
2268 RAISE INVALID_DETAIL;
2269 END IF;
2270
2271 END IF;
2272
2273 END;
2274
2275
2276 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2277 DELETE FROM hxc_ret_pay_latest_details
2278 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2279 AND object_version_number = l_lines_object_version_number(i);
2280
2281 END IF;
2282
2283
2284 ELSIF l_normal_tab(x) IS NULL
2285 AND l_retro_tab(x) IS NOT NULL
2286 THEN
2287
2288 OPEN get_retro_lines(l_tc_tab(x),
2289 l_retro_tab(x));
2290 FETCH get_retro_lines BULK COLLECT INTO
2291 l_lines_old_batch_id ,
2292 l_lines_request_id ,
2293 l_lines_old_request_id ,
2294 l_lines_resource_id ,
2295 l_lines_time_building_block_id ,
2296 l_lines_approval_status ,
2297 l_lines_start_time ,
2298 l_lines_stop_time ,
2299 l_lines_org_id ,
2300 l_lines_business_group_id ,
2301 l_lines_timecard_id ,
2302 l_lines_attribute1 ,
2303 l_lines_attribute2 ,
2304 l_lines_attribute3 ,
2305 l_lines_measure ,
2306 l_lines_object_version_number ,
2307 l_lines_old_ovn ,
2308 l_lines_old_measure ,
2309 l_lines_old_attribute1 ,
2310 l_lines_old_attribute2 ,
2311 l_lines_old_attribute3 ,
2312 l_lines_pbl_id ,
2313 l_lines_retro_pbl_id ,
2314 l_lines_old_pbl_id ,
2315 l_lines_batch_id ,
2316 l_lines_retro_batch_id ,
2317 l_lines_application_set_id ,
2318 l_lines_comment_text ,
2319 l_lines_last_update_date ,
2320 l_lines_resource_type ,
2321 l_lines_td_id ,
2322 l_lines_trans_id ;
2323 CLOSE get_retro_lines;
2324
2325
2326 IF l_lines_time_building_block_id.COUNT >0
2327 THEN
2328
2329 display_numtab('Transaction Details',l_lines_trans_id);
2330 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2331 UPDATE hxc_transaction_details
2332 SET status = 'ROLLEDBACK',
2333 exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
2334 WHERE transaction_detail_id = l_lines_td_id(i);
2335
2336
2337 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2338 DELETE FROM hxc_ret_pay_details
2339 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2340 AND object_version_number = l_lines_object_version_number(i)
2341 AND retro_batch_id = l_lines_retro_batch_id(i)
2342 AND batch_id IS NULL;
2343
2344 l_ora_error := FALSE;
2345 BEGIN
2346 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST SAVE EXCEPTIONS
2347 INSERT INTO hxc_pay_latest_details
2348 (
2349 resource_id,
2350 time_building_block_id,
2351 object_version_number,
2352 approval_status,
2353 start_time,
2354 stop_time,
2355 application_set_id,
2356 last_update_date,
2357 comment_text,
2358 resource_type ,
2359 org_id,
2360 business_group_id,
2361 timecard_id,
2362 attribute1,
2363 attribute2,
2364 attribute3,
2365 measure)
2366 VALUES (
2367 l_lines_resource_id(i),
2368 l_lines_time_building_block_id(i),
2369 l_lines_object_version_number(i),
2370 l_lines_approval_status(i),
2371 l_lines_start_time(i),
2372 l_lines_stop_time(i),
2373 l_lines_application_set_id(i),
2374 NVL(l_lines_last_update_date(i),SYSDATE),
2375 l_lines_comment_text(i),
2376 'PERSON',
2377 l_lines_org_id(i),
2378 l_lines_business_group_id(i),
2379 l_lines_timecard_id(i),
2380 l_lines_attribute1(i),
2381 l_lines_attribute2(i),
2382 l_lines_attribute3(i),
2383 l_lines_measure(i));
2384
2385 EXCEPTION
2386 WHEN TABLE_EXCEPTION
2387 THEN
2388 log(' Exception raised ');
2389 -- Bug 13777315
2390 IF SQL%BULK_EXCEPTIONS.COUNT > 0
2391 THEN
2392 log('List of Details with Exceptions ');
2393 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
2394 LOOP
2395 log('TBB_Id '||l_lines_time_building_block_id(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
2396 ' TBB_OVN '||l_lines_object_version_number(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
2397 log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
2398 IF SQL%BULK_EXCEPTIONS(n).ERROR_CODE <> 1
2399 THEN
2400 l_ora_error := TRUE;
2401 END IF;
2402 END LOOP;
2403 IF l_ora_error
2404 THEN
2405 RAISE INVALID_DETAIL;
2406 END IF;
2407
2408 END IF;
2409
2410 END;
2411
2412
2413 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2414 UPDATE hxc_ret_pay_latest_details
2415 SET attribute1 = l_lines_old_attribute1(i),
2416 attribute2 = l_lines_old_attribute2(i),
2417 attribute3 = l_lines_old_attribute3(i),
2418 measure = l_lines_old_measure(i),
2419 request_id = old_request_id,
2420 batch_id = old_batch_id,
2421 pbl_id = old_pbl_id,
2422 retro_batch_id = NULL,
2423 retro_pbl_id = NULL,
2424 comment_text = NULL,
2425 old_request_id = NULL,
2426 old_batch_id = NULL,
2427 old_pbl_id = NULL,
2428 old_attribute1 = NULL,
2429 old_attribute2 = NULL,
2430 old_attribute3 = NULL,
2431 old_measure = NULL,
2432 old_ovn = NULL
2433 WHERE time_building_block_id = l_lines_time_building_block_id(i)
2434 AND object_version_number = l_lines_object_version_number(i);
2435
2436
2437 END IF;
2438
2439
2440 END IF;
2441
2442 IF l_lines_time_building_block_id.COUNT > 0
2443 THEN
2444 FOR i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2445 LOOP
2446 IF l_lines_batch_id(i) IS NOT NULL
2447 AND g_reverse_batches.EXISTS(TO_CHAR(l_lines_batch_id(i)))
2448 THEN
2449 l_pointer := g_reverse_batches(TO_CHAR(l_lines_batch_id(i))).COUNT;
2450 g_reverse_batches(TO_CHAR(l_lines_batch_id(i))).EXTEND(1);
2451 g_reverse_batches(TO_CHAR(l_lines_batch_id(i)))(l_pointer+1) :=
2452 l_lines_pbl_id(i);
2453 ELSIF l_lines_batch_id(i) IS NOT NULL
2454 AND NOT g_reverse_batches.EXISTS(TO_CHAR(l_lines_batch_id(i)))
2455 THEN
2456 g_reverse_batches(TO_CHAR(l_lines_batch_id(i))) := NUMTAB();
2457 g_reverse_batches(TO_CHAR(l_lines_batch_id(i))).EXTEND(1);
2458 g_reverse_batches(TO_CHAR(l_lines_batch_id(i)))(1) :=
2459 l_lines_pbl_id(i);
2460 END IF;
2461
2462
2463 IF l_lines_retro_batch_id(i) IS NOT NULL
2464 AND g_reverse_batches.EXISTS(TO_CHAR(l_lines_retro_batch_id(i)))
2465 THEN
2466 l_pointer := g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i))).COUNT;
2467 g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i))).EXTEND(1);
2468 g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i)))(l_pointer+1) :=
2469 l_lines_retro_pbl_id(i);
2470 ELSIF l_lines_retro_batch_id(i) IS NOT NULL
2471 AND NOT g_reverse_batches.EXISTS(TO_CHAR(l_lines_retro_batch_id(i)))
2472 THEN
2473 g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i))) := NUMTAB();
2474 g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i))).EXTEND(1);
2475 g_reverse_batches(TO_CHAR(l_lines_retro_batch_id(i)))(1) :=
2476 l_lines_retro_pbl_id(i);
2477 END IF;
2478
2479 END LOOP;
2480
2481
2482
2483
2484 IF NOT l_tc_tab.EXISTS(x-1)
2485 OR ( l_tc_tab.EXISTS(x-1)
2486 AND l_tc_tab(x-1) <> l_tc_tab(x) )
2487 THEN
2488
2489 log(' ----------------------- Time Store Vs BEE -----------------------',g_cache);
2490
2491 log(l_space||RPAD('===============',15)||
2492 l_line||
2493 RPAD('========',5)||
2494 l_line||
2495 RPAD('===============',10)||
2496 l_line||
2497 RPAD('====================',20)||
2498 l_line||
2499 RPAD('===============',15)||
2500 l_line||
2501 RPAD('====================',20)||
2502 l_line||
2503 RPAD('===============',15)||
2504 l_line||
2505 RPAD('===============',15)||'|',g_cache);
2506
2507
2508 log(l_space||RPAD('Detail Id',15)||
2509 l_space||
2510 RPAD('OVN',5)||
2511 l_space||
2512 RPAD('Date',10)||
2513 l_space||
2514 RPAD('Batch Name',20)||
2515 l_space||
2516 RPAD('Batch Line Id',15)||
2517 l_space||
2518 RPAD('Retro Batch Name',20)||
2519 l_space||
2520 RPAD('Retro Line Id',15)||
2521 l_space||
2522 RPAD('Timecard Id',15)||'|',g_cache);
2523
2524 log(l_space||RPAD('===============',15)||
2525 l_line||
2526 RPAD('========',5)||
2527 l_line||
2528 RPAD('===============',10)||
2529 l_line||
2530 RPAD('====================',20)||
2531 l_line||
2532 RPAD('===============',15)||
2533 l_line||
2534 RPAD('====================',20)||
2535 l_line||
2536 RPAD('===============',15)||
2537 l_line||
2538 RPAD('===============',15)||'|',g_cache);
2539
2540 END IF;
2541
2542 FOR i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
2543 LOOP
2544 log(l_space||RPAD(l_lines_time_building_block_id(i),15)||
2545 l_space||
2546 RPAD(l_lines_object_version_number(i),5)||
2547 l_space||
2548 RPAD(TO_CHAR(l_lines_start_time(i)),10)||
2549 l_space||
2550 RPAD(NVL(SUBSTR(batch_name(l_lines_batch_id(i)),1,20),' '),20)||
2551 l_space||
2552 RPAD(NVL(TO_CHAR(l_lines_pbl_id(i)),' '),15)||
2553 l_space||
2554 RPAD(NVL(SUBSTR(batch_name(l_lines_retro_batch_id(i)),1,20),' '),20)||
2555 l_space||
2556 RPAD(NVL(TO_CHAR(l_lines_retro_pbl_id(i)),' '),15)||
2557 l_space||
2558 RPAD(l_lines_timecard_id(i),15)||'|',g_cache);
2559 END LOOP;
2560
2561 IF NOT l_tc_tab.EXISTS(x+1)
2562 OR ( l_tc_tab.EXISTS(x+1)
2563 AND l_tc_tab(x) <> l_tc_tab(x+1) )
2564 THEN
2565
2566 log(l_space||RPAD('===============',15)||
2567 l_line||
2568 RPAD('========',5)||
2569 l_line||
2570 RPAD('===============',10)||
2571 l_line||
2572 RPAD('====================',20)||
2573 l_line||
2574 RPAD('===============',15)||
2575 l_line||
2576 RPAD('====================',20)||
2577 l_line||
2578 RPAD('===============',15)||
2579 l_line||
2580 RPAD('===============',15)||'|',g_cache);
2581
2582 END IF;
2583
2584 END IF;
2585
2586 log(' ',g_cache);
2587 log(' --------------- Batch Details -------------------',g_cache);
2588 IF (l_tc_tab.EXISTS(x+1)
2589 AND l_tc_tab(x) <> l_tc_tab(x+1))
2590 OR (NOT l_tc_tab.EXISTS(x+1))
2591 THEN
2592 IF g_reverse_batches.COUNT > 0
2593 THEN
2594 l_index := g_reverse_batches.FIRST;
2595 LOOP
2596 log('Batch id '||l_index);
2597 g_reverse_batches(l_index) := SET(g_reverse_batches(l_index));
2598 FOR j IN g_reverse_batches(l_index).FIRST..g_reverse_batches(l_index).LAST
2599 LOOP
2600 log('-----Line '||g_reverse_batches(l_index)(j));
2601 END LOOP;
2602 reverse_batch_lines(l_index,g_reverse_batches(l_index));
2603 l_index := g_reverse_batches.NEXT(l_index);
2604 EXIT WHEN NOT g_reverse_batches.EXISTS(l_index);
2605 END LOOP;
2606 g_reverse_batches.DELETE;
2607 update_transferred_to(l_tc_tab(x));
2608 invalidate_rollback_ld(l_tc_tab(x));
2609 commit_session;
2610 END IF;
2611 END IF;
2612
2613
2614 EXIT CONTINUE_TO_NEXT_TIMECARD;
2615
2616 END LOOP CONTINUE_TO_NEXT_TIMECARD;
2617
2618 END LOOP; -- TC_TAB
2619
2620 END IF;
2621
2622
2623 END rollback_latest_retrieval;
2624
2625
2626 PROCEDURE rollback_all_retrievals(
2627 p_bg_id IN NUMBER,
2628 p_session_date IN VARCHAR2,
2629 p_start_date IN VARCHAR2,
2630 p_end_date IN VARCHAR2,
2631 p_gre_id IN NUMBER DEFAULT NULL,
2632 p_organization_id IN NUMBER DEFAULT NULL,
2633 p_location_id IN NUMBER DEFAULT NULL,
2634 p_payroll_id IN NUMBER DEFAULT NULL,
2635 p_person_id IN NUMBER DEFAULT NULL
2636 )
2637 IS
2638
2639 -- Bug 16446212
2640 l_query VARCHAR2(8000) :=
2641 'SELECT timecard_id
2642 FROM hxc_timecard_summary sum
2643 WHERE 1 = 1';
2644
2645
2646
2647 CURSOR get_timecard_data(p_timecard_id IN NUMBER)
2648 IS SELECT det.time_building_block_id,
2649 det.object_version_number,
2650 det.start_time,
2651 det.batch_id,
2652 det.pbl_id,
2653 det.retro_batch_id,
2654 det.retro_pbl_id,
2655 det.timecard_id,
2656 htd.transaction_detail_id
2657 FROM hxc_ret_pay_details det,
2658 hxc_transaction_details htd,
2659 hxc_transactions ht
2660 WHERE det.timecard_id = p_timecard_id
2661 AND det.time_building_block_id = htd.time_building_block_id
2662 AND det.object_version_number = htd.time_building_block_ovn
2663 AND htd.status = 'SUCCESS'
2664 AND htd.transaction_id = ht.transaction_id
2665 AND ht.type = 'RETRIEVAL'
2666 AND ht.status = 'SUCCESS';
2667
2668 CURSOR get_latest_batch_data (p_timecard_id IN NUMBER)
2669 IS SELECT latest.old_batch_id,
2670 latest.request_id,
2671 latest.old_request_id,
2672 latest.resource_id,
2673 latest.time_building_block_id,
2674 latest.approval_status,
2675 latest.start_time,
2676 latest.stop_time,
2677 latest.org_id,
2678 latest.business_group_id,
2679 latest.timecard_id,
2680 latest.attribute1,
2681 latest.attribute2,
2682 latest.attribute3,
2683 latest.measure,
2684 latest.object_version_number,
2685 latest.old_ovn,
2686 latest.old_measure,
2687 latest.old_attribute1,
2688 latest.old_attribute2,
2689 latest.old_attribute3,
2690 latest.pbl_id,
2691 latest.retro_pbl_id,
2692 latest.old_pbl_id,
2693 latest.batch_id,
2694 latest.retro_batch_id,
2695 latest.application_set_id,
2696 latest.comment_text,
2697 latest.last_update_date,
2698 latest.resource_type
2699 FROM hxc_ret_pay_latest_details latest
2700 WHERE timecard_id = p_timecard_id;
2701
2702
2703
2704
2705
2706 l_person_list VARCHAR2(4000);
2707
2708 l_asg_criteria VARCHAR2(4000);
2709 l_location_filter VARCHAR2(100);
2710 l_org_filter VARCHAR2(100);
2711 l_payroll_filter VARCHAR2(100);
2712 l_start_date_filter VARCHAR2(100);
2713 l_end_date_filter VARCHAR2(100);
2714
2715 l_tc_tab NUMTAB;
2716 TYPE tc_data IS TABLE OF get_timecard_data%ROWTYPE;
2717 --l_tc_data tc_data;
2718
2719
2720 l_tc_data_tbb_id NUMTAB;
2721 l_tc_data_ovn NUMTAB;
2722 l_tc_data_date DATETAB;
2723 l_tc_data_batch_id NUMTAB;
2724 l_tc_data_retro_batch_id NUMTAB;
2725 l_tc_data_pbl_id NUMTAB;
2726 l_tc_data_retro_pbl_id NUMTAB;
2727 l_tc_data_td_id NUMTAB;
2728 l_tc_data_timecard_id NUMTAB;
2729
2730 l_pointer NUMBER;
2731 TYPE LATEST_DATA IS TABLE OF get_latest_batch_data%ROWTYPE;
2732 --l_latest_data LATEST_DATA;
2733
2734 l_lines_old_batch_id NUMTAB ;
2735 l_lines_request_id NUMTAB ;
2736 l_lines_old_request_id NUMTAB ;
2737 l_lines_resource_id NUMTAB ;
2738 l_lines_time_building_block_id NUMTAB ;
2739 l_lines_approval_status VARCHARTAB ;
2740 l_lines_start_time DATETAB ;
2741 l_lines_stop_time DATETAB ;
2742 l_lines_org_id NUMTAB ;
2743 l_lines_business_group_id NUMTAB ;
2744 l_lines_timecard_id NUMTAB ;
2745 l_lines_attribute1 VARCHARTAB ;
2746 l_lines_attribute2 VARCHARTAB ;
2747 l_lines_attribute3 VARCHARTAB ;
2748 l_lines_measure NUMTAB ;
2749 l_lines_object_version_number NUMTAB ;
2750 l_lines_old_ovn NUMTAB ;
2751 l_lines_old_measure NUMTAB ;
2752 l_lines_old_attribute1 VARCHARTAB ;
2753 l_lines_old_attribute2 VARCHARTAB ;
2754 l_lines_old_attribute3 VARCHARTAB ;
2755 l_lines_pbl_id NUMTAB ;
2756 l_lines_retro_pbl_id NUMTAB ;
2757 l_lines_old_pbl_id NUMTAB ;
2758 l_lines_batch_id NUMTAB ;
2759 l_lines_retro_batch_id NUMTAB ;
2760 l_lines_application_set_id NUMTAB ;
2761 l_lines_comment_text VARCHARTAB ;
2762 l_lines_last_update_date DATETAB ;
2763 l_lines_resource_type VARCHARTAB ;
2764
2765 l_all_batches NUMTAB;
2766
2767
2768 l_index VARCHAR2(50);
2769
2770 -- Bug 13814489
2771 l_space VARCHAR2(15) := ' |';
2772 l_line VARCHAR2(15) := '==|';
2773
2774 l_ora_error BOOLEAN;
2775
2776
2777
2778 BEGIN
2779
2780 IF p_person_id IS NOT NULL
2781 THEN
2782 l_person_list :='AND sum.resource_id = '||p_person_id;
2783 l_query := l_query||l_person_list;
2784 END IF;
2785
2786
2787
2788
2789 IF p_start_date IS NOT NULL
2790 THEN
2791 l_start_date_filter := FND_GLOBAL.newline||' AND sum.start_time >= '''||p_start_date||'''';
2792 END IF;
2793
2794 IF p_end_date IS NOT NULL
2795 THEN
2796 l_end_date_filter := FND_GLOBAL.newline||' AND TRUNC(sum.stop_time) <= '''||p_end_date||'''';
2797 END IF;
2798
2799 IF p_location_id IS NOT NULL
2800 THEN
2801 l_location_filter := FND_GLOBAL.newline||' AND paf.location_id = '||p_location_id;
2802 END iF;
2803
2804 IF p_organization_id IS NOT NULL
2805 THEN
2806 l_org_filter := FND_GLOBAL.newline||' AND paf.organization_id = '||p_organization_id;
2807 END iF;
2808
2809 IF p_payroll_id IS NOT NULL
2810 THEN
2811 l_payroll_filter := FND_GLOBAL.newline||' AND paf.payroll_id = '||p_payroll_id;
2812 END iF;
2813
2814
2815 IF p_gre_id IS NULL
2816 THEN
2817 l_asg_criteria := FND_GLOBAL.newline||' AND resource_id IN ( SELECT person_id
2818 FROM per_all_assignments_f paf
2819 WHERE business_group_id = '||p_bg_id
2820 ||l_location_filter
2821 ||l_org_filter
2822 ||l_payroll_filter
2823 ||')';
2824 ELSIF p_gre_id IS NOT NULL
2825 THEN
2826 l_asg_criteria := FND_GLOBAL.newline||' AND resource_id IN ( SELECT person_id
2827 FROM per_all_assignments_f paf,
2828 hr_soft_coding_keyflex hsk
2829 WHERE business_group_id = '||p_bg_id
2830 ||l_location_filter
2831 ||l_org_filter
2832 ||l_payroll_filter
2833 ||FND_GLOBAL.newline||' AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
2834 AND hsk.segment1 = '''||p_gre_id||''''
2835 ||')';
2836 END IF;
2837
2838
2839
2840 l_query := l_query||l_start_date_filter||l_end_date_filter
2841 ||l_asg_criteria;
2842 l_query := l_query||' ORDER BY sum.timecard_id ';
2843
2844 log(l_query);
2845
2846 EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_tc_tab;
2847
2848 log('Printing timecards ');
2849
2850 IF l_tc_tab.COUNT > 0
2851 THEN
2852 for i IN l_tc_tab.FIRST..l_tc_tab.LAST
2853 LOOP
2854 log(l_tc_tab(i));
2855 END LOOP;
2856
2857 FOR x IN l_tc_tab.FIRST..l_tc_tab.LAST
2858 LOOP
2859 <<CONTINUE_TO_NEXT_TIMECARD>>
2860 LOOP
2861
2862 -- Bug 13844693
2863 -- Call modified to display the banner in the output file.
2864 display_tc_details2(l_tc_tab(x));
2865 log('Setting Savepoint ');
2866 SAVEPOINT TC_START;
2867
2868 IF verify_rollback_eligibility(l_tc_tab(x)) = 'NO'
2869 THEN
2870 ROLLBACK TO TC_START;
2871 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
2872 EXIT CONTINUE_TO_NEXT_TIMECARD;
2873 END IF;
2874
2875 IF verify_otlr_details(l_tc_tab(x)) = 'YES'
2876 THEN
2877 ROLLBACK TO TC_START;
2878 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
2879 EXIT CONTINUE_TO_NEXT_TIMECARD;
2880 END IF;
2881
2882 -- Bug 13777315
2883 IF verify_if_archived(l_tc_tab(x)) = 'YES'
2884 THEN
2885 ROLLBACK TO TC_START;
2886 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
2887 EXIT CONTINUE_TO_NEXT_TIMECARD;
2888 END IF;
2889
2890
2891 OPEN get_latest_batch_data(l_tc_tab(x));
2892 FETCH get_latest_batch_data BULK COLLECT INTO l_lines_old_batch_id ,
2893 l_lines_request_id ,
2894 l_lines_old_request_id ,
2895 l_lines_resource_id ,
2896 l_lines_time_building_block_id,
2897 l_lines_approval_status ,
2898 l_lines_start_time ,
2899 l_lines_stop_time ,
2900 l_lines_org_id ,
2901 l_lines_business_group_id ,
2902 l_lines_timecard_id ,
2903 l_lines_attribute1 ,
2904 l_lines_attribute2 ,
2905 l_lines_attribute3 ,
2906 l_lines_measure ,
2907 l_lines_object_version_number ,
2908 l_lines_old_ovn ,
2909 l_lines_old_measure ,
2910 l_lines_old_attribute1 ,
2911 l_lines_old_attribute2 ,
2912 l_lines_old_attribute3 ,
2913 l_lines_pbl_id ,
2914 l_lines_retro_pbl_id ,
2915 l_lines_old_pbl_id ,
2916 l_lines_batch_id ,
2917 l_lines_retro_batch_id ,
2918 l_lines_application_set_id ,
2919 l_lines_comment_text ,
2920 l_lines_last_update_date ,
2921 l_lines_resource_type;
2922
2923 CLOSE get_latest_batch_data;
2924
2925 IF l_lines_time_building_block_id.COUNT > 0
2926 THEN
2927
2928 l_all_batches := l_lines_batch_id MULTISET UNION l_lines_retro_batch_id;
2929 l_all_batches := SET(l_all_batches);
2930 IF g_rlbk_status = 'U'
2931 AND verify_batch_status(l_all_batches) = 'ERROR'
2932 THEN
2933 ROLLBACK TO TC_START;
2934 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
2935 EXIT CONTINUE_TO_NEXT_TIMECARD;
2936 END IF;
2937
2938 l_ora_error := FALSE;
2939 BEGIN
2940 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST SAVE EXCEPTIONS
2941 INSERT INTO hxc_pay_latest_details
2942 (
2943 resource_id,
2944 time_building_block_id,
2945 object_version_number,
2946 approval_status,
2947 start_time,
2948 stop_time,
2949 application_set_id,
2950 last_update_date,
2951 comment_text,
2952 resource_type ,
2953 org_id,
2954 business_group_id,
2955 timecard_id,
2956 attribute1,
2957 attribute2,
2958 attribute3,
2959 measure)
2960 VALUES (
2961 l_lines_resource_id(i),
2962 l_lines_time_building_block_id(i),
2963 l_lines_object_version_number(i),
2964 l_lines_approval_status(i),
2965 l_lines_start_time(i),
2966 l_lines_stop_time(i),
2967 l_lines_application_set_id(i),
2968 NVL(l_lines_last_update_date(i),SYSDATE),
2969 l_lines_comment_text(i),
2970 'PERSON',
2971 l_lines_org_id(i),
2972 l_lines_business_group_id(i),
2973 l_lines_timecard_id(i),
2974 l_lines_attribute1(i),
2975 l_lines_attribute2(i),
2976 l_lines_attribute3(i),
2977 l_lines_measure(i));
2978
2979 EXCEPTION
2980 WHEN TABLE_EXCEPTION
2981 THEN
2982 log(' Exception raised ');
2983 -- Bug 13777315
2984 IF SQL%BULK_EXCEPTIONS.COUNT > 0
2985 THEN
2986 log('List of Details with Exceptions ');
2987 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
2988 LOOP
2989 log('TBB_Id '||l_lines_time_building_block_id(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
2990 ' TBB_OVN '||l_lines_object_version_number(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
2991 log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
2992 IF SQL%BULK_EXCEPTIONS(n).ERROR_CODE <> 1
2993 THEN
2994 l_ora_error := TRUE;
2995 END IF;
2996 END LOOP;
2997 IF l_ora_error
2998 THEN
2999 RAISE INVALID_DETAIL;
3000 END IF;
3001
3002 END IF;
3003
3004 END;
3005
3006 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST
3007 DELETE FROM hxc_ret_pay_latest_details
3008 WHERE time_building_block_id = l_lines_time_building_block_id(i);
3009
3010 END IF;
3011
3012
3013 OPEN get_timecard_data(l_tc_tab(x));
3014 FETCH get_timecard_data BULK COLLECT INTO l_tc_data_tbb_id,
3015 l_tc_data_ovn,
3016 l_tc_data_date,
3017 l_tc_data_batch_id,
3018 l_tc_data_pbl_id,
3019 l_tc_data_retro_batch_id,
3020 l_tc_data_retro_pbl_id,
3021 l_tc_data_timecard_id,
3022 l_tc_data_td_id;
3023 CLOSE get_timecard_data;
3024
3025 IF l_tc_data_tbb_id.COUNT > 0
3026 THEN
3027
3028 l_all_batches := l_tc_data_batch_id MULTISET UNION l_tc_data_retro_batch_id;
3029 l_all_batches := SET(l_all_batches);
3030
3031 IF g_rlbk_status = 'U'
3032 AND verify_batch_status(l_all_batches) = 'ERROR'
3033 THEN
3034 ROLLBACK TO TC_START;
3035 g_errored_tc(TO_CHAR(l_tc_tab(x))) := 1;
3036 EXIT CONTINUE_TO_NEXT_TIMECARD;
3037 END IF;
3038
3039 -- Bug 13844693
3040 -- Displaying the banner earlier, so we dont need this.
3041
3042 --display_tc_details2(l_tc_data_timecard_id(1));
3043
3044
3045 display_numtab('Transaction Details',l_tc_data_td_id);
3046 FORALL i IN l_tc_data_td_id.FIRST..l_tc_data_td_id.LAST
3047 UPDATE hxc_transaction_details
3048 SET status = 'ROLLEDBACK',
3049 exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
3050 WHERE transaction_detail_id = l_tc_data_td_id(i);
3051
3052
3053 FORALL i IN l_tc_data_td_id.FIRST..l_tc_data_td_id.LAST
3054 DELETE FROM hxc_ret_pay_details
3055 WHERE time_building_block_id = l_tc_data_tbb_id(i)
3056 AND object_version_number = l_tc_data_ovn(i);
3057
3058
3059 log(' ----------------------- Time Store Vs BEE -----------------------',g_cache);
3060
3061
3062
3063 log(l_space||RPAD('===============',15)||
3064 l_line||
3065 RPAD('========',5)||
3066 l_line||
3067 RPAD('===============',10)||
3068 l_line||
3069 RPAD('====================',20)||
3070 l_line||
3071 RPAD('===============',15)||
3072 l_line||
3073 RPAD('====================',20)||
3074 l_line||
3075 RPAD('===============',15)||
3076 l_line||
3077 RPAD('===============',15)||'|',g_cache);
3078
3079
3080 log(l_space||RPAD('Detail Id',15)||
3081 l_space||
3082 RPAD('OVN',5)||
3083 l_space||
3084 RPAD('Date',10)||
3085 l_space||
3086 RPAD('Batch Name',20)||
3087 l_space||
3088 RPAD('Batch Line Id',15)||
3089 l_space||
3090 RPAD('Retro Batch Name',20)||
3091 l_space||
3092 RPAD('Retro Line Id',15)||
3093 l_space||
3094 RPAD('Timecard Id',15)||'|',g_cache);
3095
3096 log(l_space||RPAD('===============',15)||
3097 l_line||
3098 RPAD('========',5)||
3099 l_line||
3100 RPAD('===============',10)||
3101 l_line||
3102 RPAD('====================',20)||
3103 l_line||
3104 RPAD('===============',15)||
3105 l_line||
3106 RPAD('====================',20)||
3107 l_line||
3108 RPAD('===============',15)||
3109 l_line||
3110 RPAD('===============',15)||'|',g_cache);
3111
3112
3113
3114
3115 FOR i IN l_tc_data_tbb_id.FIRST..l_tc_data_tbb_id.LAST
3116 LOOP
3117
3118
3119 log(l_space||RPAD(l_tc_data_tbb_id(i),15)||
3120 l_space||
3121 RPAD(l_tc_data_ovn(i),5)||
3122 l_space||
3123 RPAD(TO_CHAR(l_tc_data_date(i)),10)||
3124 l_space||
3125 RPAD(NVL(batch_name(l_tc_data_batch_id(i)),' '),20)||
3126 l_space||
3127 RPAD(NVL(TO_CHAR(l_tc_data_pbl_id(i)),' '),15)||
3128 l_space||
3129 RPAD(NVL(batch_name(l_tc_data_retro_batch_id(i)),' '),20)||
3130 l_space||
3131 RPAD(NVL(TO_CHAR(l_tc_data_retro_pbl_id(i)),' '),15)||
3132 l_space||
3133 RPAD(l_tc_data_timecard_id(i),15)||'|',g_cache);
3134
3135
3136
3137 IF l_tc_data_batch_id(i) IS NOT NULL
3138 AND g_reverse_batches.EXISTS(TO_CHAR(l_tc_data_batch_id(i)))
3139 THEN
3140 l_pointer := g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i))).COUNT;
3141 g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i))).EXTEND(1);
3142 g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i)))(l_pointer+1) :=
3143 l_tc_data_pbl_id(i);
3144 ELSIF l_tc_data_batch_id(i) IS NOT NULL
3145 AND NOT g_reverse_batches.EXISTS(TO_CHAR(l_tc_data_batch_id(i)))
3146 THEN
3147 g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i))) := NUMTAB();
3148 g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i))).EXTEND(1);
3149 g_reverse_batches(TO_CHAR(l_tc_data_batch_id(i)))(1) :=
3150 l_tc_data_pbl_id(i);
3151 END IF;
3152
3153 IF l_tc_data_retro_batch_id(i) IS NOT NULL
3154 AND g_reverse_batches.EXISTS(TO_CHAR(l_tc_data_retro_batch_id(i)))
3155 THEN
3156 l_pointer := g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i))).COUNT;
3157 g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i))).EXTEND(1);
3158 g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i)))(l_pointer+1) :=
3159 l_tc_data_retro_pbl_id(i);
3160 ELSIF l_tc_data_retro_batch_id(i) IS NOT NULL
3161 AND NOT g_reverse_batches.EXISTS(TO_CHAR(l_tc_data_retro_batch_id(i)))
3162 THEN
3163 g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i))) := NUMTAB();
3164 g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i))).EXTEND(1);
3165 g_reverse_batches(TO_CHAR(l_tc_data_retro_batch_id(i)))(1) :=
3166 l_tc_data_retro_pbl_id(i);
3167 END IF;
3168
3169 END LOOP; -- TC DATa FOR
3170
3171
3172 log(l_space||RPAD('===============',15)||
3173 l_line||
3174 RPAD('========',5)||
3175 l_line||
3176 RPAD('===============',10)||
3177 l_line||
3178 RPAD('====================',20)||
3179 l_line||
3180 RPAD('===============',15)||
3181 l_line||
3182 RPAD('====================',20)||
3183 l_line||
3184 RPAD('===============',15)||
3185 l_line||
3186 RPAD('===============',15)||'|',g_cache);
3187
3188
3189 log(' ',g_cache);
3190 log(' --------------- Batch Details -------------------',g_cache);
3191 log(' ',g_cache);
3192
3193
3194 END IF;
3195
3196 IF g_reverse_batches.COUNT > 0
3197 THEN
3198 l_index := g_reverse_batches.FIRST;
3199 LOOP
3200 log('Batch id '||l_index);
3201 g_reverse_batches(l_index) := SET(g_reverse_batches(l_index));
3202 FOR j IN g_reverse_batches(l_index).FIRST..g_reverse_batches(l_index).LAST
3203 LOOP
3204 log('-----Line '||g_reverse_batches(l_index)(j));
3205 END LOOP;
3206 reverse_batch_lines(l_index,g_reverse_batches(l_index));
3207 l_index := g_reverse_batches.NEXT(l_index);
3208 EXIT WHEN NOT g_reverse_batches.EXISTS(l_index);
3209 END LOOP;
3210 g_reverse_batches.DELETE;
3211 END IF;
3212
3213 update_transferred_to(l_tc_tab(x));
3214 EXIT CONTINUE_TO_NEXT_TIMECARD;
3215 END LOOP CONTINUE_TO_NEXT_TIMECARD;
3216 commit_session;
3217
3218 END LOOP; -- TC_TAB
3219
3220
3221 END IF;
3222
3223
3224 END rollback_all_retrievals;
3225
3226
3227
3228
3229 PROCEDURE rollback_xfer_time(errbuff OUT NOCOPY VARCHAR2,
3230 retcode OUT NOCOPY NUMBER,
3231 p_bg_id IN NUMBER,
3232 p_session_date IN VARCHAR2,
3233 p_validate_mode IN VARCHAR2 DEFAULT 'VALIDATE',
3234 p_start_date IN VARCHAR2,
3235 p_end_date IN VARCHAR2,
3236 p_gre_id IN NUMBER DEFAULT NULL,
3237 p_organization_id IN NUMBER DEFAULT NULL,
3238 p_location_id IN NUMBER DEFAULT NULL,
3239 p_payroll_id IN NUMBER DEFAULT NULL,
3240 p_person_id IN NUMBER DEFAULT NULL,
3241 p_rollback_mode IN VARCHAR2 DEFAULT 'LATEST',
3242 p_is_latest IN VARCHAR2,
3243 p_request_id IN NUMBER DEFAULT NULL,
3244 p_retrieval_transaction_code IN VARCHAR2 DEFAULT NULL,
3245 p_batch_id IN NUMBER DEFAULT NULL,
3246 p_batch_ref IN VARCHAR2 DEFAULT NULL,
3247 p_batch_specified IN VARCHAR2,
3248 p_process_dependent IN VARCHAR2 DEFAULT 'NO',
3249 p_batch_status IN VARCHAR2 DEFAULT 'U'
3250 )
3251 IS
3252
3253 -- Bug 13939790
3254 -- Added the NOT NULL condition to avoid picking up OTLR
3255 -- records with NULL batches.
3256 CURSOR get_batches_x_code(p_trans_code IN VARCHAR2)
3257 IS SELECT batch_id
3258 FROM hxc_pay_trans_code_all
3259 WHERE transaction_code = p_trans_code
3260 AND batch_id IS NOT NULL;
3261
3262 CURSOR get_batches_request(p_request_id IN NUMBER)
3263 IS SELECT batch_id
3264 FROM hxc_pay_trans_code_all
3265 WHERE request_id = p_request_id
3266 AND batch_id IS NOT NULL;
3267
3268 CURSOR get_batches_ref(p_batch_ref IN VARCHAR2)
3269 IS SELECT batch_id
3270 FROM pay_batch_headers
3271 WHERE batch_reference = p_batch_ref;
3272
3273 l_x_batches NUMTAB := NUMTAB();
3274 l_r_batches NUMTAB := NUMTAB();
3275 l_b_batches NUMTAB := NUMTAB();
3276
3277 l_all_batches BATCHARRAY;
3278
3279 l_batches NUMTAB := NUMTAB();
3280 -- Bug 16446212
3281 l_batches_list VARCHAR2(32000);
3282
3283
3284 x VARCHAR2(50);
3285 BEGIN
3286
3287 init;
3288
3289 IF NVL(p_process_dependent,'YES') = 'YES'
3290 THEN
3291 g_process_dependent_batches := TRUE;
3292 ELSE
3293 g_process_dependent_batches := FALSE;
3294 END IF;
3295
3296 g_validate_mode := p_validate_mode;
3297 g_rlbk_status := p_batch_status;
3298
3299
3300 log('===========',g_cache);
3301 log('Parameters ',g_cache);
3302 log('===========',g_cache);
3303 log;
3304 log('Business Group Id : '||p_bg_id,g_cache);
3305 log('Session Date '||p_session_date);
3306 log('Validate Mode: '||p_validate_mode);
3307 log('Start Date : '||p_start_date,g_cache);
3308 log('End Date : '||p_end_date,g_cache);
3309 log('GRE Id : '||p_gre_id,g_cache);
3310 log('Organization : '||p_organization_id,g_cache);
3311 log('Location : '||p_location_id,g_cache);
3312 log('Payroll : '||p_payroll_id,g_cache);
3313 log('Person : '||p_person_id,g_cache);
3314 log('Rollback Mode: '||p_rollback_mode);
3315 log('p_is_latest: '||p_is_latest);
3316 log('Ret Transaction Code : '||p_retrieval_transaction_code,g_cache);
3317 log('Batch Reference : '||p_batch_ref,g_cache);
3318 log('Batch Name : '||p_batch_id,g_cache);
3319 log('Request Id : '||p_request_id,g_cache);
3320 log('p_batch_status: '||p_batch_status);
3321 log('p_process_dependent: '||p_process_dependent);
3322
3323 log(' ',g_cache);
3324 log(' ',g_cache);
3325
3326 log('Business Group :'||p_bg_id);
3327
3328 IF p_is_latest = '1'
3329 THEN
3330 log('P_latest is '||p_is_latest);
3331 ELSIF p_is_latest <> '1'
3332 THEN
3333 log(' P_latest is '||p_is_latest);
3334 END IF;
3335
3336 g_validate_mode := p_validate_mode;
3337
3338 IF NVL(p_is_latest,'-99') <> '1'
3339 THEN
3340
3341 HXC_RETRIEVAL_ROLLBACK.rollback_all_retrievals
3342 (p_bg_id => p_bg_id
3343 ,p_session_date => FND_DATE.CANONICAL_TO_DATE(p_session_date)
3344 ,p_start_date => FND_DATE.CANONICAL_TO_DATE(p_start_date)
3345 ,p_end_date => FND_DATE.CANONICAL_TO_DATE(p_end_date)
3346 ,p_gre_id => p_gre_id
3347 ,p_organization_id => p_organization_id
3348 ,p_location_id => p_location_id
3349 ,p_payroll_id => p_payroll_id
3350 ,p_person_id => p_person_id
3351 );
3352
3353 ELSIF p_is_latest = '1'
3354 THEN
3355
3356 IF p_batch_ref IS NULL
3357 AND p_retrieval_transaction_code IS NULL
3358 AND p_request_id IS NULL
3359 AND p_batch_id IS NULL
3360 THEN
3361 log('For Rolling Back specific details provide at least one of the following parameters',g_cache);
3362 log('Retrieval Transaction Code',g_cache);
3363 log('Request Id ',g_cache);
3364 log('Batch Reference ',g_cache);
3365 log('Batch Name ',g_cache);
3366 retcode := 2;
3367 return;
3368 END IF;
3369
3370
3371 IF p_batch_ref IS NOT NULL
3372 THEN
3373 OPEN get_batches_ref(p_batch_ref);
3374 FETCH get_batches_ref BULK COLLECT INTO l_b_batches;
3375 CLOSE get_batches_ref;
3376
3377 l_all_batches(p_batch_ref) := l_b_batches;
3378
3379 END IF;
3380
3381 IF p_retrieval_transaction_code IS NOT NULL
3382 THEN
3383 OPEN get_batches_x_code(p_retrieval_transaction_code);
3384 FETCH get_batches_x_code BULK COLLECT INTO l_x_batches;
3385 CLOSE get_batches_x_code;
3386
3387 l_all_batches(p_retrieval_transaction_code) := l_x_batches;
3388
3389 END IF;
3390
3391 IF p_request_id IS NOT NULL
3392 THEN
3393 OPEN get_batches_request(p_request_id);
3394 FETCH get_batches_request BULK COLLECT INTO l_r_batches;
3395 CLOSE get_batches_request;
3396
3397 l_all_batches(p_request_id) := l_r_batches;
3398
3399 END IF;
3400
3401 display_numtab('Batch references',l_b_batches);
3402 display_numtab('Request references',l_r_batches);
3403 display_numtab('Transcode references',l_x_batches);
3404
3405 IF l_all_batches.COUNT > 0
3406 THEN
3407 x := l_all_batches.FIRST;
3408 LOOP
3409 log(x);
3410 IF x = l_all_batches.FIRST
3411 THEN
3412 l_batches := l_all_batches(x);
3413 display_numtab(' for '||x,l_batches);
3414 ELSE
3415 l_batches := l_batches MULTISET INTERSECT l_all_batches(x);
3416 display_numtab(' for '||x,l_batches);
3417 END IF;
3418 x := l_all_batches.NEXT(x);
3419 EXIT WHEN NOT l_all_batches.EXISTS(x);
3420 END LOOP;
3421
3422 display_numtab('All batches ', l_batches);
3423
3424 g_batches_to_process := l_batches;
3425 l_batches_list := string_list(g_batches_to_process);
3426
3427 IF l_batches_list <> ' '
3428 THEN
3429 l_batches_list := '('||l_batches_list||')';
3430 END IF;
3431
3432 log(l_batches_list);
3433
3434 END IF;
3435
3436 IF p_batch_id IS NOT NULL
3437 THEN
3438 l_batches_list := '('||p_batch_id||')';
3439 log('Batches list '||l_batches_list);
3440 g_batches_to_process := NUMTAB(p_batch_id);
3441 END IF;
3442
3443 rollback_latest_retrieval
3444 (p_bg_id => p_bg_id
3445 ,p_session_date => FND_DATE.canonical_to_date(p_session_date)
3446 ,p_start_date => FND_DATE.canonical_to_date(p_start_date)
3447 ,p_end_date => FND_DATE.canonical_to_date(p_end_date)
3448 ,p_gre_id => p_gre_id
3449 ,p_organization_id => p_organization_id
3450 ,p_location_id => p_location_id
3451 ,p_payroll_id => p_payroll_id
3452 ,p_person_id => p_person_id
3453 ,p_batches_list => l_batches_list
3454 ,p_request_id => p_request_id
3455 );
3456
3457
3458
3459
3460 END IF;
3461
3462 IF g_errored_tc.COUNT > 0
3463 THEN
3464 retcode := 1;
3465 END IF;
3466
3467 display_cached_log;
3468
3469 IF p_validate_mode = 'VALIDATE'
3470 THEN
3471 log('Validate Mode : Rolling Back ');
3472 rollback;
3473 END IF;
3474
3475
3476 END rollback_xfer_time;
3477
3478
3479
3480
3481 PROCEDURE log(p_text IN VARCHAR2 DEFAULT ' ',
3482 p_cache IN VARCHAR2 DEFAULT 'N')
3483 IS
3484
3485 PRAGMA AUTONOMOUS_TRANSACTION;
3486
3487 BEGIN
3488 IF g_debug
3489 THEN
3490 hr_utility.trace(p_text);
3491 END IF;
3492 FND_FILE.put_line(FND_FILE.log,p_text);
3493 --dbms_output.put_line(p_text);
3494
3495 IF p_cache = 'Y'
3496 THEN
3497 g_seq := g_seq+1;
3498 INSERT INTO hxc_rollback_log
3499 (seq,text,type)
3500 VALUES
3501 (g_seq,p_text,'HXCROLL');
3502 COMMIT;
3503 END IF;
3504
3505 END log;
3506
3507
3508 END HXC_RETRIEVAL_ROLLBACK;
3509