[Home] [Help]
PACKAGE BODY: APPS.HXT_RETRIEVAL_ROLLBACK
Source
1 PACKAGE BODY HXT_RETRIEVAL_ROLLBACK AS
2 /* $Header: hxtrollret.pkb 120.1.12020000.2 2012/07/31 08:41:50 asrajago noship $ */
3
4 l_retro_tab NUMTAB;
5 g_validate_mode VARCHAR2(50) := 'VALIDATE';
6 g_program VARCHAR2(20) := 'HXTROLLOTLR';
7
8 g_pbl_tab NUMTAB;
9 g_seq NUMBER := 0;
10
11 TABLE_EXCEPTION EXCEPTION;
12 PRAGMA EXCEPTION_INIT(TABLE_EXCEPTION,-24381);
13 INVALID_DETAIL EXCEPTION;
14 PRAGMA EXCEPTION_INIT(INVALID_DETAIL,-6502);
15
16
17
18 PROCEDURE display_numtab (p_text VARCHAR2,
19 p_list NUMTAB)
20 IS
21
22 BEGIN
23 put_log('Displaying '||p_text);
24 put_log;
25 IF p_list.COUNT > 0
26 THEN
27 FOR i IN p_list.FIRST..p_list.LAST
28 LOOP
29 put_log(p_list(i));
30 END LOOP;
31 END IF;
32 put_log;
33 END display_numtab;
34
35 FUNCTION string_list(p_list NUMTAB)
36 RETURN VARCHAR2
37 IS
38
39 l_string VARCHAR2(1000) := ' ';
40
41 BEGIN
42 IF p_list.COUNT > 0
43 THEN
44 FOR i IN p_list.FIRST..p_list.LAST
45 LOOP
46 IF p_list(i) IS NOT NULL
47 THEN
48 l_string := l_string||p_list(i)||',';
49 END IF;
50 END LOOP;
51 l_string := RTRIM(l_string,',');
52 RETURN l_string;
53 ELSE
54 RETURN ' ';
55 END IF;
56 END string_list;
57
58
59 PROCEDURE set_session_date
60 IS
61
62
63 BEGIN
64
65 DELETE FROM FND_SESSIONS
66 WHERE session_id = USERENV('SESSIONID');
67
68
69 INSERT INTO FND_SESSIONS
70 ( session_id,effective_date)
71 VALUES ( USERENV('SESSIONID'),TRUNC(SYSDATE));
72
73 END set_session_date;
74
75
76 FUNCTION payroll_name(p_payroll_id IN NUMBER)
77 RETURN VARCHAR2
78 IS
79
80 CURSOR get_payroll_name
81 IS SELECT payroll_name
82 FROM pay_all_payrolls_f
83 WHERE payroll_id = p_payroll_id
84 AND SYSDATE BETWEEN effective_start_date
85 AND effective_end_date;
86
87 l_payroll_name VARCHAR2(40);
88
89 BEGIN
90 IF NOT g_payroll_name.EXISTS(TO_CHAR(p_payroll_id))
91 THEN
92 OPEN get_payroll_name;
93 FETCH get_payroll_name INTO l_payroll_name;
94 CLOSE get_payroll_name;
95
96 g_payroll_name(TO_CHAR(p_payroll_id)) := l_payroll_name;
97 RETURN l_payroll_name;
98 ELSE
99 RETURN g_payroll_name(TO_CHAR(p_payroll_id));
100 END IF;
101
102 END payroll_name;
103
104
105 -- Function to return Batch Name for a given Batch id
106 -- Stores once retrieved Batch names in Cache so that
107 -- next time they are not queried.
108
109 FUNCTION batch_name(p_batch_id IN NUMBER)
110 RETURN VARCHAR2
111 IS
112
113 CURSOR get_batch_name
114 IS SELECT batch_name,
115 batch_status
116 FROM pay_batch_headers
117 WHERE batch_id = p_batch_id;
118
119 l_batch_name VARCHAR2(40);
120 l_batch_status VARCHAR2(10);
121
122 BEGIN
123 IF p_batch_id IS NULL
124 THEN
125
126 RETURN ' ';
127
128 END IF;
129
130 IF NOT g_batch_name.EXISTS(TO_CHAR(p_batch_id))
131 THEN
132
133 OPEN get_batch_name;
134
135 FETCH get_batch_name
136 INTO l_batch_name,l_batch_status;
137
138 CLOSE get_batch_name;
139
140 g_batch_name(TO_CHAR(p_batch_id)) := l_batch_name;
141 g_batch_status(TO_CHAR(p_batch_id)) := l_batch_status;
142
143 RETURN l_batch_name;
144
145 ELSE
146
147 RETURN g_batch_name(TO_CHAR(p_batch_id));
148
149 END IF;
150
151 END batch_name;
152
153 FUNCTION batch_status(p_batch_id IN NUMBER)
154 RETURN VARCHAR2
155 IS
156
157 CURSOR get_batch_name
158 IS SELECT batch_name,
159 batch_status
160 FROM pay_batch_headers
161 WHERE batch_id = p_batch_id;
162
163 l_batch_name VARCHAR2(40);
164 l_batch_status VARCHAR2(10);
165
166 BEGIN
167 IF p_batch_id IS NULL
168 THEN
169 RETURN ' ';
170 END IF;
171 IF NOT g_batch_status.EXISTS(TO_CHAR(p_batch_id))
172 THEN
173 OPEN get_batch_name;
174 FETCH get_batch_name INTO l_batch_name,l_batch_status;
175 CLOSE get_batch_name;
176
177 g_batch_name(TO_CHAR(p_batch_id)) := l_batch_name;
178 g_batch_status(TO_CHAR(p_batch_id)) := l_batch_status;
179 RETURN l_batch_status;
180 ELSE
181 RETURN g_batch_status(TO_CHAR(p_batch_id));
182 END IF;
183
184 END batch_status;
185
186 FUNCTION data_set_status(p_data_set_id IN NUMBER)
187 RETURN VARCHAR2
188 IS
189
190 CURSOR get_data_set_status(p_data_set_id IN NUMBER)
191 IS SELECT status
192 FROM hxc_data_sets
193 WHERE data_set_id = p_data_set_id;
194
195 l_status VARCHAR2(50);
196
197 BEGIN
198
199 IF g_data_set_status.EXISTS(TO_CHAR(p_data_set_id))
200 THEN
201 RETURN g_data_set_status(TO_CHAR(p_data_set_id));
202 ELSE
203 put_log('Data Set Id :'||p_data_set_id);
204 OPEN get_data_set_status(p_data_set_id);
205 FETCH get_data_set_status INTO l_status;
206 CLOSE get_data_set_status;
207 put_log('Data Set Status is '||l_status);
208
209 g_data_set_status(TO_CHAR(p_data_set_id)) := l_status;
210
211 END IF;
212
213 END data_set_status;
214
215 FUNCTION person_name(p_person_id IN NUMBER)
216 RETURN VARCHAR2
217 IS
218
219 CURSOR get_person_name
220 IS SELECT full_name
221 FROM per_all_people_f
222 WHERE person_id = p_person_id
223 AND SYSDATE BETWEEN effective_start_date
224 AND effective_end_date ;
225
226 l_person_name VARCHAR2(240);
227
228 BEGIN
229 put_log('Trying '||p_person_id);
230
231 IF NOT g_person_name.EXISTS(TO_CHAR(p_person_id))
232 THEN
233 OPEN get_person_name;
234 FETCH get_person_name INTO l_person_name;
235 CLOSE get_person_name;
236 put_log('Name is '||l_person_name);
237 g_person_name(TO_CHAR(p_person_id)) := l_person_name;
238 RETURN l_person_name;
239 ELSE
240 RETURN g_person_name(TO_CHAR(p_person_id));
241 END IF;
242
243 END person_name;
244
245 FUNCTION element_name(p_element_id IN NUMBER)
246 RETURN VARCHAR2
247 IS
248
249 CURSOR get_element_name
250 IS SELECT element_name
251 FROM pay_element_types_f
252 WHERE element_type_id = p_element_id
253 AND SYSDATE BETWEEN effective_start_date
254 AND effective_end_date;
255
256 l_element_name VARCHAR2(240):= 'XXXX';
257
258 BEGIN
259 IF NOT g_element_name.EXISTS(TO_CHAR(p_element_id))
260 THEN
261 OPEN get_element_name;
262 FETCH get_element_name INTO l_element_name;
263 CLOSE get_element_name;
264 g_element_name(TO_CHAR(p_element_id)) := l_element_name;
265 RETURN l_element_name;
266 ELSE
267 RETURN g_element_name(TO_CHAR(p_element_id));
268 END IF;
269
270 END element_name;
271
272
273 FUNCTION period_details(p_time_period_id IN NUMBER)
274 RETURN VARCHAR2
275 IS
276
277 CURSOR get_period_details
278 IS select 'Period Name: '||period_name
279 ||' Start: '||TO_CHAR(start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
280 ||' End: '||TO_CHAR(end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
281 from per_time_periods
282 where time_period_id = p_time_period_id;
283
284 l_period_details VARCHAR2(240):= 'XXXX';
285
286 BEGIN
287 IF NOT g_period_details.EXISTS(TO_CHAR(p_time_period_id))
288 THEN
289 OPEN get_period_details;
290 FETCH get_period_details INTO l_period_details;
291 CLOSE get_period_details;
292 g_period_details(TO_CHAR(p_time_period_id)) := l_period_details;
293 RETURN l_period_details;
294 ELSE
295 RETURN g_period_details(TO_CHAR(p_time_period_id));
296 END IF;
297
298 END period_details;
299
300 PROCEDURE display_cached_log
301 IS
302
303 PRAGMA AUTONOMOUS_TRANSACTION;
304
305 CURSOR get_log
306 IS SELECT text
307 FROM hxc_rollback_log
308 WHERE type = g_program
309 ORDER BY seq;
310
311
312
313 l_tab VARCHARTAB;
314
315 BEGIN
316
317 /*
318
319 put_log;
320 put_log;
321 put_log;
322 put_log;
323 put_log;
324 put_log;
325 put_log('**************************************************************************************************');
326 put_log(' Rollback Report ');
327 put_log('**************************************************************************************************');
328 put_log;
329 put_log;
330 put_log;
331
332
333 OPEN get_log;
334 LOOP
335 FETCH get_log BULK COLLECT INTO l_tab LIMIT 100;
336 EXIT WHEN l_tab.COUNT = 0;
337
338 FOR i IN l_tab.FIRST..l_tab.LAST
339 LOOP
340 put_log(l_tab(i));
341 END LOOP;
342 END LOOP;
343 CLOSE get_log;
344
345 */
346
347 fnd_file.new_line(fnd_file.output);
348 fnd_file.new_line(fnd_file.output);
349 fnd_file.new_line(fnd_file.output);
350 fnd_file.new_line(fnd_file.output);
351 fnd_file.new_line(fnd_file.output);
352 fnd_file.new_line(fnd_file.output);
353 fnd_file.put_line(fnd_file.output,'**************************************************************************************************');
354 fnd_file.put_line(fnd_file.output,' Rollback Report ');
355 fnd_file.put_line(fnd_file.output,'**************************************************************************************************');
356 fnd_file.new_line(fnd_file.output);
357 fnd_file.new_line(fnd_file.output);
358 fnd_file.new_line(fnd_file.output);
359
360
361 OPEN get_log;
362 LOOP
363 FETCH get_log BULK COLLECT INTO l_tab LIMIT 100;
364 EXIT WHEN l_tab.COUNT = 0;
365
366 FOR i IN l_tab.FIRST..l_tab.LAST
367 LOOP
368 fnd_file.put_line(fnd_file.output,l_tab(i));
369 END LOOP;
370 END LOOP;
371 CLOSE get_log;
372
373
374 DELETE FROM hxc_rollback_log
375 WHERE type = g_program;
376 COMMIt;
377
378
379
380 END display_cached_log;
381
382 -- Deletes any old log that exists in the table for Logging.
383 -- Covers any scenario where an ORA error stopped the earlier
384 -- run.
385
386 PROCEDURE delete_old_log
387 IS
388
389 PRAGMA AUTONOMOUS_TRANSACTION;
390
391 BEGIN
392
393 DELETE FROM hxc_rollback_log
394 WHERE type = g_program;
395 COMMIT;
396
397 END delete_old_log;
398
399
400 PROCEDURE display_tc_details(p_tim_id IN NUMBER)
401 IS
402
403 CURSOR get_tc_attributes
404 IS SELECT payroll_id,
405 time_period_id,
406 batch_id,
407 for_person_id
408 FROM hxt_timecards
409 WHERE id = p_tim_id;
410
411 l_person_id NUMBER;
412 l_batch_id NUMBER;
413 l_payroll_id NUMBER;
414 l_time_period_id NUMBER;
415
416 BEGIN
417
418 OPEN get_tc_attributes;
419 FETCH get_tc_attributes INTO l_payroll_id,
420 l_time_period_id,
421 l_batch_id,
422 l_person_id;
423
424 CLOSE get_tc_attributes;
425
426 put_log(' ',g_cache);
427 put_log(' ',g_cache);
428 put_log(' ',g_cache);
429 put_log('Tim Id : '||p_tim_id,g_cache);
430 put_log('Person : '||person_name(l_person_id),g_cache);
431 put_log('Payroll : '||payroll_name(l_payroll_id),g_cache);
432 put_log(period_details(l_time_period_id),g_cache);
433 put_log('Batch : '||batch_name(l_batch_id),g_cache);
434
435
436 END display_tc_details;
437
438
439 FUNCTION verify_rollback_eligibility(p_timecard_id IN NUMBER)
440 RETURN VARCHAR2
441 IS
442
443 CURSOR get_rollback_eligibility(p_timecard_id IN NUMBER)
444 IS SELECT timecard_id
445 FROM hxc_rollback_timecards_all
446 WHERE timecard_id = p_timecard_id
447 AND timecard_type = 'HXT_TIMECARDS_F';
448
449 l_id NUMBER;
450
451
452 BEGIN
453 IF g_all_rlbk_eligible.EXISTS(TO_CHAR(p_timecard_id))
454 THEN
455 RETURN g_all_rlbk_eligible(TO_CHAR(p_timecard_id));
456 ELSE
457 OPEN get_rollback_eligibility(p_timecard_id);
458 FETCH get_rollback_eligibility INTO l_id;
459
460 IF get_rollback_eligibility%NOTFOUND
461 THEN
462 g_all_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'NO';
463 put_log('System does not have enough details to Rollback this timecard',g_cache);
464 ELSE
465 g_all_rlbk_eligible(TO_CHAR(p_timecard_id)) := 'YES';
466 END IF;
467 CLOSE get_rollback_eligibility;
468 RETURN g_all_rlbk_eligible(TO_CHAR(p_timecard_id));
469 END IF;
470
471 END verify_rollback_eligibility;
472
473 -- Bug 13777315
474 FUNCTION verify_if_archived(p_timecard_id IN NUMBER)
475 RETURN VARCHAR2
476 IS
477
478 CURSOR get_data_set_id(p_timecard_id NUMBER)
479 IS SELECT data_set_id
480 FROM hxt_timecards_f
481 WHERE id = p_timecard_id;
482
483 l_data_set_id NUMBER;
484
485
486 BEGIN
487 IF g_archive_status.EXISTS(TO_CHAR(p_timecard_id))
488 THEN
489 RETURN g_archive_status(TO_CHAR(p_timecard_id));
490 ELSE
491 OPEN get_data_set_id(p_timecard_id);
492 FETCH get_data_set_id INTO l_data_set_id;
493 CLOSE get_data_set_id;
494
495 IF l_data_set_id IS NOT NULL
496 THEN
497 IF data_set_status(l_data_set_id) <> 'ON_LINE'
498 THEN
499 g_archive_status(TO_CHAR(p_timecard_id)) := 'YES';
500 put_log('Data set id '||l_data_set_id);
501 put_log('Data Set Status : '||data_set_status(l_data_set_id));
502 put_log('This timecard may be Archived and cannot be processed ',g_cache);
503 ELSE
504 g_archive_status(TO_CHAR(p_timecard_id)) := 'NO';
505 put_log('Data set id '||l_data_set_id);
506 put_log('Data Set Status : '||data_set_status(l_data_set_id));
507 END IF;
508 ELSE
509 g_archive_status(TO_CHAR(p_timecard_id)) := 'NO';
510 END IF;
511 END IF;
512
513 RETURN g_archive_status(TO_CHAR(p_timecard_id));
514
515 END verify_if_archived;
516
517 FUNCTION verify_retro_details(p_tim_id IN NUMBER)
518 RETURN VARCHAR2
519 IS
520
521 CURSOR get_retro_details
522 IS SELECT DISTINCT retro_batch_id
523 FROM hxt_det_hours_worked_f
524 WHERE tim_id = p_tim_id
525 AND retro_batch_id IS NOT NULL
526 AND rollback_status IS NULL;
527
528 l_retro_id NUMTAB;
529
530 BEGIN
531
532 OPEN get_retro_details;
533 FETCH get_retro_details BULK COLLECT INTO l_retro_id;
534 CLOSE get_retro_details;
535
536 IF l_retro_id.COUNT > 0
537 THEN
538 FOR i IN l_retro_id.FIRST..l_retro_id.LAST
539 LOOP
540 put_log('The following retro batches exist ',g_cache);
541 put_log('Batch: '||batch_name(l_retro_id(i))||' Batch Id:'||l_retro_id(i),g_cache);
542 put_log('Use OTL: Rollback OTLR Timecards or OTL: Rollback Batches From BEE(Retro)',g_cache);
543 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
544 END LOOP;
545 RETURN 'YES';
546 END IF;
547
548 RETURN 'NO';
549
550 END verify_retro_details;
551
552
553 PROCEDURE update_transferred_to(p_timecards IN NUMTAB)
554 IS
555
556 CURSOR get_transferred_to(p_timecard_id IN NUMBER)
557 IS SELECT transferred_to
558 FROM hxc_timecard_summary
559 WHERE timecard_id = p_timecard_id;
560
561 l_transferred_to VARCHAR2(100);
562
563 BEGIN
564
565 put_log('Updating Transferred To ');
566 IF p_timecards.COUNT > 0
567 THEN
568 FOR i IN p_timecards.FIRST..p_timecards.LAST
569 LOOP
570 IF p_timecards(i) IS NOT NULL
571 THEN
572
573 put_log('Timecard Id '||p_timecards(i));
574 OPEN get_transferred_to(p_timecards(i));
575 FETCH get_transferred_to INTO l_transferred_to;
576 CLOSE get_transferred_to;
577
578 put_log('Transferred to for this timecard '||l_transferred_to);
579
580 l_transferred_to := LTRIM(RTRIM(REPLACE(l_transferred_to,'Payroll'),','),',');
581
582 put_log('Modified Transferred to for this timecard '||l_transferred_to);
583
584 UPDATE hxc_timecard_summary
585 SET transferred_to = l_transferred_to
586 WHERE timecard_id = p_timecards(i);
587 END IF;
588 END LOOP;
589 END IF;
590
591 END update_transferred_to;
592
593
594 PROCEDURE display_tc_details_retro(p_tim_id IN NUMBER,
595 p_batch_id IN NUMBER)
596 IS
597
598 CURSOR get_tc_attributes
599 IS SELECT payroll_id,
600 time_period_id,
601 batch_id,
602 for_person_id
603 FROM hxt_timecards
604 WHERE id = p_tim_id;
605
606 l_person_id NUMBER;
607 l_batch_id NUMBER;
608 l_payroll_id NUMBER;
609 l_time_period_id NUMBER;
610
611 BEGIN
612
613 OPEN get_tc_attributes;
614 FETCH get_tc_attributes INTO l_payroll_id,
615 l_time_period_id,
616 l_batch_id,
617 l_person_id;
618
619 CLOSE get_tc_attributes;
620
621 put_log('Tim Id : '||p_tim_id,g_cache);
622 put_log('Person : '||person_name(l_person_id),g_cache);
623 put_log('Payroll : '||payroll_name(l_payroll_id),g_cache);
624 put_log(period_details(l_time_period_id),g_cache);
625 put_log('Original Batch : '||batch_name(l_batch_id),g_cache);
626 put_log('Retro Batch : '||batch_name(p_batch_id),g_cache);
627 put_log(' ',g_cache);
628
629
630 END display_tc_details_retro;
631
632
633
634 PROCEDURE display_details(p_batch_id IN NUMBER,
635 p_detail_list IN NUMTAB)
636 IS
637
638
639
640 l_detail_list VARCHAR2(4000) := '(';
641
642 l_sql VARCHAR2(4000) :=
643 ' select id,
644 parent_id,
645 date_worked,
646 decode(effective_end_date,hr_general.end_of_time,''A'',''I'') status,
647 hours,
648 element_type_id,
649 pay_status,
650 pbl_line_id,
651 retro_pbl_line_id,
652 retro_batch_id,
653 retro_batch_id_2,
654 retro_pbl_line_id_2
655 from hxt_det_hours_worked_f
656 WHERE id IN ';
657
658 l_order_by VARCHAR2(150) :=
659 ' ORDER BY effective_end_date,id,date_worked, parent_id ';
660
661 idtab NUMTAB;
662 parenttab NUMTAB;
663 datetab VARCHARTAB;
664 statustab VARCHARTAB;
665 hrstab NUMTAB;
666 elementtab NUMTAB;
667 paytab VARCHARTAB;
668 pbltab NUMTAB;
669 retpbltab NUMTAB;
670 rettab NUMTAB;
671 ret2tab NUMTAB;
672 retpbl2tab NUMTAB;
673
674
675 l_space VARCHAR2(15) := ' |';
676 l_space1 VARCHAR2(15) := ' |';
677 l_line VARCHAR2(15) := '==|';
678
679
680 BEGIN
681
682 IF p_detail_list.COUNT > 0
683 THEN
684 FOR i IN p_detail_list.FIRST..p_detail_list.LAST
685 LOOP
686 l_detail_list := l_detail_list||p_detail_list(i)||',';
687 END LOOP;
688
689 l_detail_list := RTRIM(l_detail_list,',')||')';
690
691 put_log(l_detail_list);
692
693 l_sql := l_sql||l_detail_list||l_order_by;
694
695 put_log(l_sql);
696
697 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO idtab,
698 parenttab,
699 datetab,
700 statustab,
701 hrstab,
702 elementtab,
703 paytab,
704 pbltab,
705 retpbltab,
706 rettab,
707 ret2tab,
708 retpbl2tab;
709
710 IF idtab.COUNT > 0
711 THEN
712
713 put_log(' Displaying Details for this timecard',g_cache);
714
715 put_log(l_space1||RPAD('===============',10)||
716 l_line||
717 RPAD('===============',10)||
718 l_line||
719 RPAD('===============',10)||
720 l_line||
721 RPAD('====================',5)||
722 l_line||
723 RPAD('===============',5)||
724 l_line||
725 RPAD('========================================',20)||
726 l_line||
727 RPAD('===============',5)||
728 l_line||
729 RPAD('===============',10)||
730 l_line||
731 RPAD('===============',10)||
732 l_line||
733 RPAD('===============',10)||
734 -- l_line||
735 -- RPAD('===============',10)||
736 -- l_line||
737 -- RPAD('===============',10)||
738 '|',g_cache);
739
740 put_log(l_space1||RPAD('ID ',10)||
741 l_space||
742 RPAD('Parent Id',10)||
743 l_space||
744 RPAD('Date',10)||
745 l_space||
746 RPAD('A/I',5)||
747 l_space||
748 RPAD('Hours',5)||
749 l_space||
750 RPAD('Element Name',20)||
751 l_space||
752 RPAD('Pay Status',5)||
753 l_space||
754 RPAD('PBL Id',10)||
755 l_space||
756 RPAD('Ret PBL Id',10)||
757 l_space||
758 RPAD('Ret Id',10)||
759 -- l_space||
760 -- RPAD('Ret ID2',10)||
761 -- l_space||
762 -- RPAD('Ret PBL Id2',10)||
763 '|',g_cache);
764
765 put_log(l_space1||RPAD('===============',10)||
766 l_line||
767 RPAD('===============',10)||
768 l_line||
769 RPAD('===============',10)||
770 l_line||
771 RPAD('====================',5)||
772 l_line||
773 RPAD('===============',5)||
774 l_line||
775 RPAD('========================================',20)||
776 l_line||
777 RPAD('===============',5)||
778 l_line||
779 RPAD('===============',10)||
780 l_line||
781 RPAD('===============',10)||
782 l_line||
783 RPAD('===============',10)||
784 -- l_line||
785 -- RPAD('===============',10)||
786 -- l_line||
787 -- RPAD('===============',10)||
788 '|',g_cache);
789
790
791 FOR i IN idtab.FIRST..idtab.LAST
792 LOOP
793
794 put_log(l_space1||RPAD(idtab(i),10)||
795 l_space||
796 RPAD(parenttab(i),10)||
797 l_space||
798 RPAD(datetab(i),10)||
799 l_space||
800 RPAD(statustab(i),5)||
801 l_space||
802 RPAD(hrstab(i),5)||
803 l_space||
804 RPAD(element_name(elementtab(i)),20)||
805 l_space||
806 RPAD(paytab(i),5)||
807 l_space||
808 RPAD(NVL(TO_CHAR(pbltab(i)),' '),10)||
809 l_space||
810 RPAD(NVL(TO_CHAR(retpbltab(i)),' '),10)||
811 l_space||
812 RPAD(NVL(TO_CHAR(rettab(i)),' '),10)||
813 -- l_space||
814 -- RPAD(NVL(TO_CHAR(ret2tab(i)),' '),10)||
815 -- l_space||
816 -- RPAD(NVL(TO_CHAR(retpbl2tab(i)),' '),10)||
817 '|',g_cache);
818
819 END LOOP;
820
821 put_log(l_space1||RPAD('===============',10)||
822 l_line||
823 RPAD('===============',10)||
824 l_line||
825 RPAD('===============',10)||
826 l_line||
827 RPAD('====================',5)||
828 l_line||
829 RPAD('===============',5)||
830 l_line||
831 RPAD('========================================',20)||
832 l_line||
833 RPAD('===============',5)||
834 l_line||
835 RPAD('===============',10)||
836 l_line||
837 RPAD('===============',10)||
838 l_line||
839 RPAD('===============',10)||
840 -- l_line||
841 -- RPAD('===============',10)||
842 -- l_line||
843 -- RPAD('===============',10)||
844 '|',g_cache);
845
846
847
848 END IF;
849
850 END IF;
851
852
853 END display_details;
854
855 -- Bug 13954828
856 -- Added tim_id parameter
857 FUNCTION display_pre_rollback_details(p_batch_id IN NUMBER,
858 p_tim_id IN NUMBER)
859 RETURN VARCHAR2
860 IS
861
862 CURSOR get_tim_id
863 IS SELECT tim_id
864 FROM hxt_det_hours_worked_f
865 WHERE retro_batch_id = p_batch_id;
866
867 CURSOR get_details_adjusted(p_tim_id IN NUMBER,
868 p_batch_id IN NUMBER)
869 IS SELECT id
870 FROM hxt_det_hours_worked_f
871 WHERE tim_id = p_tim_id
872 AND retro_batch_id_2 = p_batch_id
873 AND pay_status IN ('A','D');
874
875 CURSOR get_details_backed_out(p_tim_id IN NUMBER,
876 p_batch_id IN NUMBER)
877 IS SELECT id
878 FROM hxt_det_hours_worked_f det,
879 pay_batch_lines pbl
880 WHERE tim_id = p_tim_id
881 AND pbl.batch_id = p_batch_id
882 AND pbl.batch_line_id = retro_pbl_line_id
883 AND pay_status = 'B';
884
885 CURSOR get_new_details(p_tim_id IN NUMBER,
886 p_batch_id IN NUMBER)
887 IS SELECT id
888 FROM hxt_det_hours_worked_f
889 WHERE tim_id = p_tim_id
890 AND retro_batch_id = p_batch_id
891 AND pay_status = 'C' ;
892
893
894 l_tim_id NUMBER;
895 l_id_tab NUMTAB;
896 id_list NUMTAB := NUMTAB();
897
898
899 BEGIN
900
901
902 /*
903 OPEN get_tim_id;
904 FETCH get_tim_id INTO l_tim_id;
905 CLOSE get_tim_id;
906 */
907 l_tim_id := p_tim_id;
908 display_tc_details_retro(l_tim_id,p_batch_id);
909
910 IF verify_rollback_eligibility(l_tim_id) = 'NO'
911 THEN
912 g_errored_tc(TO_CHAR(l_tim_id)) := 1;
913 RETURN 'NO';
914 END IF;
915
916 -- Bug 13777315
917 IF verify_if_archived(l_tim_id) = 'YES'
918 THEN
919 put_log('This may be an archived timecard ');
920 g_errored_tc(TO_CHAR(l_tim_id)) := 1;
921 RETURN 'NO';
922 END IF;
923
924
925 OPEN get_details_adjusted(l_tim_id,p_batch_id);
926 FETCH get_details_adjusted BULK COLLECT INTO l_id_tab;
927 CLOSE get_details_adjusted;
928
929 id_list := id_list MULTISET UNION l_id_tab;
930
931 OPEN get_details_backed_out(l_tim_id,p_batch_id);
932 FETCH get_details_backed_out BULK COLLECT INTO l_id_tab;
933 CLOSE get_details_backed_out;
934
935 id_list := id_list MULTISET UNION l_id_tab;
936
937 OPEN get_new_details(l_tim_id,p_batch_id);
938 FETCH get_new_details BULK COLLECT INTO l_id_tab;
939 CLOSE get_new_details;
940
941 id_list := id_list MULTISET UNION l_id_tab;
942
943
944 display_details(p_batch_id,id_list);
945
946 RETURN 'YES';
947
948 END display_pre_rollback_details;
949
950 PROCEDURE display_all_details(p_tim_id IN NUMBER)
951 IS
952
953 l_sql VARCHAR2(4000) :=
954 ' select id,
955 parent_id,
956 date_worked,
957 decode(effective_end_date,hr_general.end_of_time,''A'',''I'') status,
958 hours,
959 element_type_id,
960 pay_status,
961 pbl_line_id,
962 retro_pbl_line_id,
963 retro_batch_id,
964 retro_batch_id_2,
965 retro_pbl_line_id_2
966 from hxt_det_hours_worked_f
967 WHERE tim_id =:1
968 ORDER BY effective_end_date,id,date_worked, parent_id ';
969
970
971 l_det_tab NUMTAB;
972 idtab NUMTAB;
973 parenttab NUMTAB;
974 datetab VARCHARTAB;
975 statustab VARCHARTAB;
976 hrstab NUMTAB;
977 elementtab NUMTAB;
978 paytab VARCHARTAB;
979 pbltab NUMTAB;
980 retpbltab NUMTAB;
981 rettab NUMTAB;
982 ret2tab NUMTAB;
983 retpbl2tab NUMTAB;
984
985
986 l_space VARCHAR2(15) := ' |';
987 l_space1 VARCHAR2(15) := ' |';
988 l_line VARCHAR2(15) := '==|';
989
990
991 BEGIN
992
993
994 put_log(l_sql);
995
996 put_log('',g_cache);
997 put_log('Displaying all details for this timecard ',g_cache);
998
999 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO idtab,
1000 parenttab,
1001 datetab,
1002 statustab,
1003 hrstab,
1004 elementtab,
1005 paytab,
1006 pbltab,
1007 retpbltab,
1008 rettab,
1009 ret2tab,
1010 retpbl2tab USING p_tim_id;
1011
1012 IF idtab.COUNT > 0
1013 THEN
1014
1015 put_log(l_space1||RPAD('===============',10)||
1016 l_line||
1017 RPAD('===============',10)||
1018 l_line||
1019 RPAD('===============',10)||
1020 l_line||
1021 RPAD('====================',5)||
1022 l_line||
1023 RPAD('===============',5)||
1024 l_line||
1025 RPAD('========================================',20)||
1026 l_line||
1027 RPAD('===============',5)||
1028 l_line||
1029 RPAD('===============',10)||
1030 l_line||
1031 RPAD('===============',10)||
1032 l_line||
1033 RPAD('===============',10)||
1034 -- l_line||
1035 -- RPAD('===============',10)||
1036 -- l_line||
1037 -- RPAD('===============',10)||
1038 '|',g_cache);
1039
1040 put_log(l_space1||RPAD('ID ',10)||
1041 l_space||
1042 RPAD('Parent Id',10)||
1043 l_space||
1044 RPAD('Date',10)||
1045 l_space||
1046 RPAD('A/I',5)||
1047 l_space||
1048 RPAD('Hours',5)||
1049 l_space||
1050 RPAD('Element Name',20)||
1051 l_space||
1052 RPAD('Pay Status',5)||
1053 l_space||
1054 RPAD('PBL Id',10)||
1055 l_space||
1056 RPAD('Ret PBL Id',10)||
1057 l_space||
1058 RPAD('Ret Id',10)||
1059 -- l_space||
1060 -- RPAD('Ret ID2',10)||
1061 -- l_space||
1062 -- RPAD('Ret PBL Id2',10)||
1063 '|',g_cache);
1064
1065 put_log(l_space1||RPAD('===============',10)||
1066 l_line||
1067 RPAD('===============',10)||
1068 l_line||
1069 RPAD('===============',10)||
1070 l_line||
1071 RPAD('====================',5)||
1072 l_line||
1073 RPAD('===============',5)||
1074 l_line||
1075 RPAD('========================================',20)||
1076 l_line||
1077 RPAD('===============',5)||
1078 l_line||
1079 RPAD('===============',10)||
1080 l_line||
1081 RPAD('===============',10)||
1082 l_line||
1083 RPAD('===============',10)||
1084 -- l_line||
1085 -- RPAD('===============',10)||
1086 -- l_line||
1087 -- RPAD('===============',10)||
1088 '|',g_cache);
1089
1090
1091 FOR i IN idtab.FIRST..idtab.LAST
1092 LOOP
1093
1094 put_log(l_space1||RPAD(idtab(i),10)||
1095 l_space||
1096 RPAD(parenttab(i),10)||
1097 l_space||
1098 RPAD(datetab(i),10)||
1099 l_space||
1100 RPAD(statustab(i),5)||
1101 l_space||
1102 RPAD(hrstab(i),5)||
1103 l_space||
1104 RPAD(element_name(elementtab(i)),20)||
1105 l_space||
1106 RPAD(paytab(i),5)||
1107 l_space||
1108 RPAD(NVL(TO_CHAR(pbltab(i)),' '),10)||
1109 l_space||
1110 RPAD(NVL(TO_CHAR(retpbltab(i)),' '),10)||
1111 l_space||
1112 RPAD(NVL(TO_CHAR(rettab(i)),' '),10)||
1113 -- l_space||
1114 -- RPAD(NVL(TO_CHAR(ret2tab(i)),' '),10)||
1115 -- l_space||
1116 -- RPAD(NVL(TO_CHAR(retpbl2tab(i)),' '),10)||
1117 '|',g_cache);
1118
1119 END LOOP;
1120
1121 put_log(l_space1||RPAD('===============',10)||
1122 l_line||
1123 RPAD('===============',10)||
1124 l_line||
1125 RPAD('===============',10)||
1126 l_line||
1127 RPAD('====================',5)||
1128 l_line||
1129 RPAD('===============',5)||
1130 l_line||
1131 RPAD('========================================',20)||
1132 l_line||
1133 RPAD('===============',5)||
1134 l_line||
1135 RPAD('===============',10)||
1136 l_line||
1137 RPAD('===============',10)||
1138 l_line||
1139 RPAD('===============',10)||
1140 -- l_line||
1141 -- RPAD('===============',10)||
1142 -- l_line||
1143 -- RPAD('===============',10)||
1144 '|',g_cache);
1145
1146
1147
1148 END IF;
1149
1150
1151
1152 END display_all_details;
1153
1154 -- Bug 13954828
1155 -- Added tim_id
1156 PROCEDURE display_post_rollback_details(p_batch_id IN NUMBER,
1157 p_tim_id IN NUMBER)
1158 IS
1159
1160 CURSOR get_tim_id
1161 IS SELECT tim_id
1162 FROM hxt_det_hours_worked_f
1163 WHERE retro_batch_id = p_batch_id;
1164
1165 CURSOR get_details_adjusted(p_tim_id IN NUMBER,
1166 p_batch_id IN NUMBER)
1167 IS SELECT id
1168 FROM hxt_det_hours_worked_f
1169 WHERE tim_id = p_tim_id
1170 AND retro_batch_id = p_batch_id
1171 AND pay_status = 'R';
1172
1173
1174 CURSOR get_old_details(p_tim_id IN NUMBER)
1175 IS SELECT id
1176 FROM hxt_det_hours_worked_f
1177 WHERE tim_id = p_tim_id
1178 AND pay_status = 'C' ;
1179
1180
1181 l_tim_id NUMBER;
1182 l_id_tab NUMTAB;
1183 id_list NUMTAB := NUMTAB();
1184
1185
1186 BEGIN
1187
1188 /*
1189 OPEN get_tim_id;
1190 FETCH get_tim_id INTO l_tim_id;
1191 CLOSE get_tim_id;
1192 */
1193 l_tim_id := p_tim_id;
1194 /*
1195 display_tc_details(l_tim_id);
1196 */
1197
1198 put_log(' ',g_cache);
1199 put_log('Finished Rollback for this timecard in Batch '||batch_name(p_batch_id),g_cache);
1200 put_log(' ',g_cache);
1201
1202 OPEN get_details_adjusted(l_tim_id,p_batch_id);
1203 FETCH get_details_adjusted BULK COLLECT INTO l_id_tab;
1204 CLOSE get_details_adjusted;
1205
1206 id_list := id_list MULTISET UNION l_id_tab;
1207
1208 OPEN get_old_details(l_tim_id);
1209 FETCH get_old_details BULK COLLECT INTO l_id_tab;
1210 CLOSE get_old_details;
1211
1212 id_list := id_list MULTISET UNION l_id_tab;
1213
1214
1215 display_details(p_batch_id,id_list);
1216
1217 END display_post_rollback_details;
1218
1219
1220 PROCEDURE put_log(p_text IN VARCHAR2,
1221 p_cache IN VARCHAR2 DEFAULT 'N')
1222 IS
1223
1224 PRAGMA AUTONOMOUS_TRANSACTION;
1225 g_debug BOOLEAN := hr_utility.debug_enabled;
1226
1227 BEGIN
1228 IF g_debug
1229 THEN
1230 hr_utility.trace(p_text);
1231 END IF;
1232 FND_FILE.put_line(FND_FILE.log,p_text);
1233
1234 IF p_cache = 'Y'
1235 THEN
1236 g_seq := g_seq+1;
1237 INSERT INTO hxc_rollback_log
1238 (seq,text,type)
1239 VALUES
1240 (g_seq,p_text,g_program);
1241 COMMIT;
1242 END IF;
1243
1244 END put_log;
1245
1246
1247 PROCEDURE commit_session
1248 IS
1249
1250 BEGIN
1251
1252 IF g_validate_mode <> 'VALIDATE'
1253 THEN
1254 COMMIT;
1255 ELSE
1256 put_log('Not committing');
1257 END IF;
1258 RETURN;
1259 END commit_session;
1260
1261 FUNCTION reversal_batch_sequence(p_batch_id IN NUMBER)
1262 RETURN NUMBER
1263 IS
1264
1265 BEGIN
1266 IF NOT g_rev_batch_seq.EXISTS(TO_CHAR(p_batch_id))
1267 THEN
1268 g_rev_batch_seq(TO_CHAR(p_batch_id)) := 1;
1269 RETURN 1;
1270 ELSE
1271 g_rev_batch_seq(TO_CHAR(p_batch_id)) :=
1272 g_rev_batch_seq(TO_CHAR(p_batch_id)) + 1;
1273 RETURN g_rev_batch_seq(TO_CHAR(p_batch_id));
1274 END IF;
1275 END reversal_batch_sequence;
1276
1277
1278 FUNCTION reversed_line(p_lines PAY_BATCH_LINES%ROWTYPE)
1279 RETURN pay_batch_lines%ROWTYPE
1280 IS
1281
1282 CURSOR get_sequence(p_element_type_id IN NUMBER)
1283 IS SELECT display_sequence
1284 FROM pay_input_values_f
1285 WHERE element_type_id = p_element_type_id
1286 AND name = 'Hours';
1287
1288
1289 l_sequence NUMBER;
1290 l_out_lines pay_batch_lines%ROWTYPE;
1291
1292 BEGIN
1293 IF NOT g_hours_iv_position.EXISTS(TO_CHAR(p_lines.element_type_id))
1294 THEN
1295
1296 OPEN get_sequence(p_lines.element_type_id);
1297 FETCH get_sequence INTO l_sequence;
1298 CLOSE get_sequence;
1299
1300 g_hours_iv_position(TO_CHAR(p_lines.element_type_id)) := l_sequence;
1301 END IF;
1302
1303 l_sequence := g_hours_iv_position(TO_CHAR(p_lines.element_type_id));
1304 l_out_lines := p_lines;
1305
1306 IF l_sequence = 1
1307 THEN
1308 l_out_lines.value_1 := -1*TO_NUMBER(l_out_lines.value_1 );
1309 ELSIF l_sequence = 2
1310 THEN
1311 l_out_lines.value_2 := -1*TO_NUMBER(l_out_lines.value_2 );
1312 ELSIF l_sequence = 3
1313 THEN
1314 l_out_lines.value_3 := -1*TO_NUMBER(l_out_lines.value_3 );
1315 ELSIF l_sequence = 4
1316 THEN
1317 l_out_lines.value_4 := -1*TO_NUMBER(l_out_lines.value_4 );
1318 ELSIF l_sequence = 5
1319 THEN
1320 l_out_lines.value_5 := -1*TO_NUMBER(l_out_lines.value_5 );
1321 ELSIF l_sequence = 6
1322 THEN
1323 l_out_lines.value_6 := -1*TO_NUMBER(l_out_lines.value_6 );
1324 ELSIF l_sequence = 7
1325 THEN
1326 l_out_lines.value_7 := -1*TO_NUMBER(l_out_lines.value_7 );
1327 ELSIF l_sequence = 8
1328 THEN
1329 l_out_lines.value_8 := -1*TO_NUMBER(l_out_lines.value_8 );
1330 ELSIF l_sequence = 9
1331 THEN
1332 l_out_lines.value_9 := -1*TO_NUMBER(l_out_lines.value_9 );
1333 ELSIF l_sequence = 10
1334 THEN
1335 l_out_lines.value_10 := -1*TO_NUMBER(l_out_lines.value_10);
1336 ELSIF l_sequence = 11
1337 THEN
1338 l_out_lines.value_11 := -1*TO_NUMBER(l_out_lines.value_11);
1339 ELSIF l_sequence = 12
1340 THEN
1341 l_out_lines.value_12 := -1*TO_NUMBER(l_out_lines.value_12);
1342 ELSIF l_sequence = 13
1343 THEN
1344 l_out_lines.value_13 := -1*TO_NUMBER(l_out_lines.value_13);
1345 ELSIF l_sequence = 14
1346 THEN
1347 l_out_lines.value_14 := -1*TO_NUMBER(l_out_lines.value_14);
1348 ELSIF l_sequence = 15
1349 THEN
1350 l_out_lines.value_15 := -1*TO_NUMBER(l_out_lines.value_15);
1351 END IF;
1352
1353 RETURN l_out_lines;
1354
1355 END reversed_line;
1356
1357
1358
1359 FUNCTION reversal_batch(p_batch_id IN NUMBER)
1360 RETURN NUMBER
1361 IS
1362
1363 l_new_batch NUMBER;
1364 l_object_version_number NUMBER;
1365
1366 BEGIN
1367 IF g_reversal_batches.EXISTS(p_batch_id)
1368 THEN
1369 RETURN g_reversal_batches(TO_CHAR(p_batch_id));
1370 ELSE
1371 -- Bug 14389835
1372 pay_batch_element_entry_api.create_batch_header
1373 (p_session_date => TRUNC(SYSDATE),
1374 p_batch_name => 'REV_'||p_batch_id||'_'||FND_GLOBAL.conc_request_id,
1375 p_business_group_id => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
1376 p_batch_reference => 'REV_'||p_batch_id,
1377 p_batch_source => 'OTM',
1378 p_action_if_exists => 'I',
1379 p_batch_id => l_new_batch,
1380 p_object_version_number => l_object_version_number
1381 );
1382
1383 put_log('Reversal Batch header created for '||p_batch_id);
1384 put_log('new header '||l_new_batch);
1385
1386 g_reversal_batches(TO_CHAR(p_batch_id)) := l_new_batch;
1387 RETURN l_new_batch;
1388 END IF;
1389 END reversal_batch;
1390
1391 FUNCTION replacement_batch(p_batch_id IN NUMBER)
1392 RETURN NUMBER
1393 IS
1394
1395 l_new_batch NUMBER;
1396 l_object_version_number NUMBER;
1397
1398 BEGIN
1399 IF g_replacement_batches.EXISTS(p_batch_id)
1400 THEN
1401 RETURN g_replacement_batches(TO_CHAR(p_batch_id));
1402 ELSE
1403 -- Bug 14389835
1404 pay_batch_element_entry_api.create_batch_header
1405 (p_session_date => SYSDATE,
1406 p_batch_name => 'REP_'||p_batch_id||'_'||FND_GLOBAL.conc_request_id,
1407 p_business_group_id => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
1408 p_batch_reference => 'REP_'||p_batch_id,
1409 p_batch_source => 'OTM',
1410 p_action_if_exists => 'I',
1411 p_batch_id => l_new_batch,
1412 p_object_version_number => l_object_version_number
1413 );
1414
1415 put_log('Replacement Batch header created for '||p_batch_id);
1416 put_log('new header '||l_new_batch);
1417
1418 g_replacement_batches(TO_CHAR(p_batch_id)) := l_new_batch;
1419 RETURN l_new_batch;
1420 END IF;
1421 END replacement_batch;
1422
1423 PROCEDURE reverse_batch_lines(p_batch_id IN NUMBER,
1424 p_lines IN NUMTAB)
1425 IS
1426
1427 l_lines NUMTAB;
1428
1429 CURSOR get_batch_status
1430 IS SELECT batch_status
1431 FROM pay_batch_headers
1432 WHERE batch_id = p_batch_id;
1433 /*
1434
1435 CURSOR get_lines
1436 IS SELECT batch_line_id,
1437 object_version_number
1438 FROM pay_batch_lines pbl,
1439 TABLE(return_ids(l_lines)) list
1440 WHERE list.column_value = pbl.batch_line_id;
1441 */
1442
1443 l_sql VARCHAR2(4000) :=
1444 'SELECT *
1445 FROM pay_batch_lines
1446 WHERE batch_id = :1
1447 AND batch_line_id IN ';
1448
1449 l_sql2 VARCHAR2(4000) :=
1450 'SELECT *
1451 FROM pay_batch_lines
1452 WHERE batch_line_id IN ';
1453
1454 l_lines_list VARCHAR2(4000):= '(';
1455 l_lines_list2 VARCHAR2(4000) := '(';
1456
1457 TYPE LINESTAB IS TABLE OF pay_batch_lines%ROWTYPE;
1458
1459 l_status VARCHAR2(5);
1460 line_tab LINESTAB;
1461 ovn_tab NUMTAB;
1462 l_object_version_number NUMBER;
1463 l_batch_line_id NUMBER;
1464 l_space VARCHAR2(15) := ' |';
1465 l_space1 VARCHAR2(15) := ' |';
1466 l_line VARCHAR2(15) := '==|';
1467
1468 l_reversal_batch_id NUMBER;
1469
1470 BEGIN
1471
1472 /*
1473 OPEN get_batch_status;
1474 FETCH get_batch_status INTO l_status;
1475 CLOSE get_batch_status;
1476 */
1477 put_log(' ',g_cache);
1478 put_log(' Batch: '||batch_name(p_batch_id),g_cache);
1479 put_log('Batch Status: '||batch_status(p_batch_id));
1480
1481
1482 -- Bug 13777315
1483 -- Initializing l_status
1484 l_status := batch_status(p_batch_id);
1485
1486 IF l_status = 'U'
1487 THEN
1488 put_log(' Batch is Unprocessed and will be reversed by deleting lines',g_cache);
1489 ELSE
1490 put_log(' Batch is Transferred and will be reversed by Creating a retro batch',g_cache);
1491 END IF;
1492
1493
1494 IF p_lines.COUNT = 0
1495 THEN
1496 RETURN;
1497 END IF;
1498
1499 FOR n IN p_lines.FIRST..p_lines.LAST
1500 LOOP
1501 IF p_lines(n) IS NOT NULL
1502 THEN
1503 l_lines_list := l_lines_list||p_lines(n)||',';
1504 END IF;
1505 END LOOP;
1506
1507 l_lines_list := RTRIM(l_lines_list,',');
1508 l_lines_list := l_lines_list||')';
1509 l_sql := l_sql||l_lines_list;
1510
1511 l_sql := REPLACE(l_sql,':1',p_batch_id);
1512
1513 put_log(l_sql);
1514
1515 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO line_tab;
1516
1517 put_log(l_space1||RPAD('===============',10)||
1518 l_line||
1519 RPAD('=====',5)||
1520 l_line||
1521 RPAD('===============',10)||
1522 l_line||
1523 RPAD('====================',20)||
1524 l_line||
1525 RPAD('===============',15)||
1526 l_line||
1527 RPAD('========================================================',40)||
1528 l_line||
1529 RPAD('===============',10)||'|',g_cache);
1530
1531 put_log(l_space1||RPAD('Batch Line',10)||
1532 l_space||
1533 RPAD('OVN',5)||
1534 l_space||
1535 RPAD('Asg Number',10)||
1536 l_space||
1537 RPAD('Element Name',20)||
1538 l_space||
1539 RPAD('Date',15)||
1540 l_space||
1541 RPAD('Values 1-10',40)||
1542 l_space||
1543 RPAD('Cost Allocation KFlex Id',10)||'|',g_cache);
1544
1545 put_log(l_space1||RPAD('===============',10)||
1546 l_line||
1547 RPAD('=====',5)||
1548 l_line||
1549 RPAD('===============',10)||
1550 l_line||
1551 RPAD('====================',20)||
1552 l_line||
1553 RPAD('===============',15)||
1554 l_line||
1555 RPAD('========================================================',40)||
1556 l_line||
1557 RPAD('===============',10)||'|',g_cache);
1558
1559 IF line_tab.COUNT = 0
1560 THEN
1561 RETURN;
1562 END IF;
1563 FOR i IN line_tab.FIRST..line_tab.LAST
1564 LOOP
1565 -- put_log(line_tab(i).batch_line_id||'-'||line_tab(i).object_version_number);
1566 put_log(l_space1||RPAD(line_tab(i).batch_line_id,10)||
1567 l_space||
1568 RPAD(line_tab(i).object_version_number,5)||
1569 l_space||
1570 RPAD(line_tab(i).assignment_number,10)||
1571 l_space||
1572 RPAD(element_name(line_tab(i).element_type_id),20)||
1573 l_space||
1574 RPAD(TO_CHAR(line_tab(i).effective_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')),15)||
1575 l_space||
1576 RPAD(NVL(line_tab(i).value_1||'/'||
1577 line_tab(i).value_2||'/'||
1578 line_tab(i).value_3||'/'||
1579 line_tab(i).value_4||'/'||
1580 line_tab(i).value_5||'/'||
1581 line_tab(i).value_6||'/'||
1582 line_tab(i).value_7||'/'||
1583 line_tab(i).value_8||'/'||
1584 line_tab(i).value_9||'/'||
1585 line_tab(i).value_10,
1586 ' '),40)||
1587 l_space||
1588 RPAD(NVL(To_char(line_tab(i).cost_allocation_keyflex_id),' '),10)||'|',g_cache);
1589
1590 END LOOP;
1591 put_log(l_space1||RPAD('===============',10)||
1592 l_line||
1593 RPAD('=====',5)||
1594 l_line||
1595 RPAD('===============',10)||
1596 l_line||
1597 RPAD('====================',20)||
1598 l_line||
1599 RPAD('===============',15)||
1600 l_line||
1601 RPAD('========================================================',40)||
1602 l_line||
1603 RPAD('===============',10)||'|',g_cache);
1604
1605
1606 IF l_status IN ('U','V')
1607 THEN
1608 put_log('Deleting lines: Batch status '||l_status);
1609 put_log(' ',g_cache);
1610 l_lines := p_lines;
1611
1612
1613
1614 FOR i IN line_tab.FIRST..line_tab.LAST
1615 LOOP
1616 pay_batch_element_entry_api.delete_batch_line
1617 ( p_batch_line_id => line_tab(i).batch_line_id,
1618 p_object_version_number => line_tab(i).object_version_number);
1619 END LOOP;
1620
1621
1622 ELSIF l_status = 'T'
1623 THEN
1624 put_log(' ',g_cache);
1625 l_lines := p_lines;
1626
1627 l_reversal_batch_id := reversal_batch(p_batch_id);
1628
1629 FOR i IN line_tab.FIRST..line_tab.LAST
1630 LOOP
1631 line_tab(i) := reversed_line(line_tab(i));
1632 pay_batch_element_entry_api.create_batch_line
1633 (p_session_date => TRUNC(SYSDATE),
1634 p_batch_id => l_reversal_batch_id,
1635 p_assignment_id => line_tab(i).assignment_id,
1636 p_assignment_number => line_tab(i).assignment_number,
1637 p_batch_sequence => reversal_batch_sequence(line_tab(i).batch_id),
1638 p_concatenated_segments => line_tab(i).concatenated_segments,
1639 p_cost_allocation_keyflex_id => line_tab(i).cost_allocation_keyflex_id,
1640 p_date_earned => line_tab(i).date_earned,
1641 p_effective_date => line_tab(i).effective_date,
1642 p_effective_start_date => line_tab(i).effective_date,
1643 p_effective_end_date => line_tab(i).effective_date,
1644 p_element_name => line_tab(i).element_name,
1645 p_element_type_id => line_tab(i).element_type_id,
1646 p_segment1 => line_tab(i).segment1,
1647 p_segment2 => line_tab(i).segment2,
1648 p_segment3 => line_tab(i).segment3,
1649 p_segment4 => line_tab(i).segment4,
1650 p_segment5 => line_tab(i).segment5,
1651 p_segment6 => line_tab(i).segment6,
1652 p_segment7 => line_tab(i).segment7,
1653 p_segment8 => line_tab(i).segment8,
1654 p_segment9 => line_tab(i).segment9,
1655 p_segment10 => line_tab(i).segment10,
1656 p_segment11 => line_tab(i).segment11,
1657 p_segment12 => line_tab(i).segment12,
1658 p_segment13 => line_tab(i).segment13,
1659 p_segment14 => line_tab(i).segment14,
1660 p_segment15 => line_tab(i).segment15,
1661 p_segment16 => line_tab(i).segment16,
1662 p_segment17 => line_tab(i).segment17,
1663 p_segment18 => line_tab(i).segment18,
1664 p_segment19 => line_tab(i).segment19,
1665 p_segment20 => line_tab(i).segment20,
1666 p_segment21 => line_tab(i).segment21,
1667 p_segment22 => line_tab(i).segment22,
1668 p_segment23 => line_tab(i).segment23,
1669 p_segment24 => line_tab(i).segment24,
1670 p_segment25 => line_tab(i).segment25,
1671 p_segment26 => line_tab(i).segment26,
1672 p_segment27 => line_tab(i).segment27,
1673 p_segment28 => line_tab(i).segment28,
1674 p_segment29 => line_tab(i).segment29,
1675 p_segment30 => line_tab(i).segment30,
1676 p_value_1 => line_tab(i).value_1 ,
1677 p_value_2 => line_tab(i).value_2 ,
1678 p_value_3 => line_tab(i).value_3 ,
1679 p_value_4 => line_tab(i).value_4 ,
1680 p_value_5 => line_tab(i).value_5 ,
1681 p_value_6 => line_tab(i).value_6 ,
1682 p_value_7 => line_tab(i).value_7 ,
1683 p_value_8 => line_tab(i).value_8 ,
1684 p_value_9 => line_tab(i).value_9 ,
1685 p_value_10 => line_tab(i).value_10,
1686 p_value_11 => line_tab(i).value_11,
1687 p_value_12 => line_tab(i).value_12,
1688 p_value_13 => line_tab(i).value_13,
1689 p_value_14 => line_tab(i).value_14,
1690 p_value_15 => line_tab(i).value_15,
1691 p_batch_line_id => l_batch_line_id,
1692 p_object_version_number => l_object_version_number
1693 );
1694
1695 put_log(l_batch_line_id||' Created ');
1696 put_log(l_object_version_number||' Created ');
1697
1698 INSERT INTO hxt_reversed_batch_lines_all
1699 (batch_id,
1700 replacement_batch_id,
1701 batch_line_id,
1702 batch_line_ovn,
1703 replacement_batch_line_id,
1704 replacement_batch_line_ovn)
1705 VALUES ( line_tab(i).batch_id,
1706 l_reversal_batch_id,
1707 line_tab(i).batch_line_id,
1708 line_tab(i).object_version_number,
1709 l_batch_line_id,
1710 l_object_version_number);
1711
1712
1713 l_lines_list2 := l_lines_list2||l_batch_line_id||',';
1714
1715 END LOOP;
1716
1717 l_lines_list2 := RTRIM(l_lines_list2,',');
1718 l_lines_list2 := l_lines_list2||')';
1719 l_sql2 := l_sql2||l_lines_list2;
1720 put_log(l_sql2);
1721
1722 line_tab := LINESTAB();
1723
1724
1725
1726 EXECUTE IMMEDIATE l_sql2 BULK COLLECT INTO line_tab;
1727 put_log(' ',g_cache);
1728 put_log(' Reversal Batch: '||batch_name(line_tab(line_tab.FIRST).batch_id),g_cache);
1729 put_log(' ',g_cache);
1730
1731
1732 put_log(l_space1||RPAD('===============',10)||
1733 l_line||
1734 RPAD('=====',5)||
1735 l_line||
1736 RPAD('===============',10)||
1737 l_line||
1738 RPAD('===============',10)||
1739 l_line||
1740 RPAD('===============',15)||
1741 l_line||
1742 RPAD('===============',10)||
1743 l_line||
1744 RPAD('========================================================',40)||
1745 l_line||
1746 RPAD('===============',10)||'|',g_cache);
1747
1748 put_log(l_space1||RPAD('Batch Line',10)||
1749 l_space||
1750 RPAD('OVN',5)||
1751 l_space||
1752 RPAD('Asg Number',10)||
1753 l_space||
1754 RPAD('Asg ID',10)||
1755 l_space||
1756 RPAD('Element Name',15)||
1757 l_space||
1758 RPAD('Date',10)||
1759 l_space||
1760 RPAD('Values 1-10',40)||
1761 l_space||
1762 RPAD('Cost Allocation KFlex Id',10)||'|',g_cache);
1763
1764 put_log(l_space1||RPAD('===============',10)||
1765 l_line||
1766 RPAD('=====',5)||
1767 l_line||
1768 RPAD('===============',10)||
1769 l_line||
1770 RPAD('===============',10)||
1771 l_line||
1772 RPAD('===============',15)||
1773 l_line||
1774 RPAD('===============',10)||
1775 l_line||
1776 RPAD('========================================================',40)||
1777 l_line||
1778 RPAD('===============',10)||'|',g_cache);
1779
1780 FOR i IN line_tab.FIRST..line_tab.LAST
1781 LOOP
1782 -- put_log(line_tab(i).batch_line_id||'-'||line_tab(i).object_version_number);
1783 put_log(l_space1||RPAD(line_tab(i).batch_line_id,10)||
1784 l_space||
1785 RPAD(line_tab(i).object_version_number,5)||
1786 l_space||
1787 RPAD(line_tab(i).assignment_number,10)||
1788 l_space||
1789 RPAD(line_tab(i).assignment_id,10)||
1790 l_space||
1791 RPAD(element_name(line_tab(i).element_type_id),15)||
1792 l_space||
1793 RPAD(TO_CHAR(line_tab(i).effective_date),10)||
1794 l_space||
1795 RPAD(NVL(line_tab(i).value_1||'/'||
1796 line_tab(i).value_2||'/'||
1797 line_tab(i).value_3||'/'||
1798 line_tab(i).value_4||'/'||
1799 line_tab(i).value_5||'/'||
1800 line_tab(i).value_6||'/'||
1801 line_tab(i).value_7||'/'||
1802 line_tab(i).value_8||'/'||
1803 line_tab(i).value_9||'/'||
1804 line_tab(i).value_10,
1805 ' '),40)||
1806 l_space||
1807 RPAD(NVL(To_char(line_tab(i).cost_allocation_keyflex_id),' '),10)||'|',g_cache);
1808
1809 END LOOP;
1810 put_log(l_space1||RPAD('===============',10)||
1811 l_line||
1812 RPAD('=====',5)||
1813 l_line||
1814 RPAD('===============',10)||
1815 l_line||
1816 RPAD('===============',10)||
1817 l_line||
1818 RPAD('===============',15)||
1819 l_line||
1820 RPAD('===============',10)||
1821 l_line||
1822 RPAD('========================================================',40)||
1823 l_line||
1824 RPAD('===============',10)||'|',g_cache);
1825
1826
1827 END IF;
1828
1829 NULL;
1830 END reverse_batch_lines;
1831
1832
1833 PROCEDURE update_replacement_batch_id(p_batch_id IN NUMBER,
1834 p_tim_id IN NUMBER)
1835 IS
1836
1837 CURSOR get_row
1838 IS SELECT ROWIDTOCHAR(tim.ROWID)
1839 FROM hxt_timecards_f tim,
1840 FND_sessions sess
1841 WHERE id = p_tim_id
1842 AND session_id = USERENV('SESSIONID')
1843 AND effective_date BETWEEN effective_start_date
1844 AND effective_end_date;
1845
1846 l_rowid VARCHAR2(25);
1847
1848
1849 BEGIN
1850
1851 OPEN get_row;
1852 FETCH get_row INTO l_rowid;
1853 CLOSE get_row;
1854
1855
1856 UPDATE hxt_timecards_f
1857 SET batch_id = p_batch_id
1858 WHERE ROWID = CHARTOROWID(l_rowid);
1859
1860
1861
1862 END update_replacement_batch_id;
1863
1864 PROCEDURE update_original_batch_id(p_batch_id NUMBER,
1865 p_replacement_batch_id NUMBER,
1866 p_tim_tab NUMTAB)
1867 IS
1868
1869 CURSOR get_batch_details(p_batch NUMBER)
1870 IS SELECT object_version_number
1871 FROM pay_batch_headers pbh
1872 WHERE batch_id = p_batch;
1873
1874 l_ovn NUMBER;
1875
1876 BEGIN
1877
1878 IF p_tim_tab.COUNT > 0
1879 THEN
1880 FORALL i IN p_tim_tab.FIRST..p_tim_tab.LAST
1881 UPDATE hxt_timecards
1882 SET batch_id = p_batch_id
1883 WHERE id = p_tim_tab(i)
1884 AND batch_id = p_replacement_batch_id;
1885
1886 FORALL i IN p_tim_tab.FIRST..p_tim_tab.LAST
1887 DELETE FROM hxt_reversed_batch_tc_all
1888 WHERE batch_id = p_batch_id
1889 AND replacement_batch_id = p_replacement_batch_id
1890 AND tim_id = p_tim_tab(i);
1891
1892 put_log('Updated the batches back ');
1893 END IF;
1894
1895 UPDATE hxt_batch_states
1896 SET status = 'H'
1897 WHERE batch_id = p_batch_id;
1898
1899 put_log('Picking up replacement details ');
1900 OPEN get_batch_details(p_replacement_batch_id);
1901 FETCH get_batch_details INTO l_ovn;
1902 CLOSE get_batch_details;
1903
1904 put_log('Purging Replacement Batch');
1905 pay_batch_element_entry_api.delete_batch_header(p_batch_id => p_replacement_batch_id,
1906 p_object_version_number => l_ovn);
1907
1908
1909 END update_original_batch_id;
1910
1911
1912
1913 PROCEDURE delete_batch_line(p_batch_line_id IN NUMBER,
1914 p_ovn IN NUMBER)
1915 IS
1916
1917 BEGIN
1918
1919 put_log('Deleting line '||p_batch_line_id||', Ovn '||p_ovn);
1920 PAY_BATCH_ELEMENT_ENTRY_API.delete_batch_line
1921 (p_batch_line_id => p_batch_line_id
1922 ,p_object_version_number => p_ovn
1923 );
1924 put_log('Deleted ');
1925
1926 END delete_batch_line;
1927
1928
1929
1930 PROCEDURE rollback_normal_batches(p_batch_id IN NUMBER,
1931 p_batch_status IN VARCHAR2,
1932 p_person_id IN NUMBER DEFAULT 0)
1933 IS
1934
1935 CURSOR check_retro_batches(p_batch_id IN NUMBER)
1936 IS SELECT retro_batch_id,
1937 tim_id
1938 FROM hxt_timecards_f tim,
1939 hxt_det_hours_worked_f det
1940 WHERE tim.batch_id = p_batch_id
1941 AND tim.id = det.tim_id;
1942
1943
1944 CURSOR get_timecards(p_batch_id IN NUMBER)
1945 IS SELECT id
1946 FROM hxt_timecards
1947 WHERE batch_id = p_batch_id;
1948
1949 CURSOR get_timecards_person(p_batch_id IN NUMBER,
1950 p_person_id IN NUMBER)
1951 IS SELECT id
1952 FROM hxt_timecards
1953 WHERE batch_id = p_batch_id
1954 AND for_person_id = p_person_id;
1955
1956
1957
1958 CURSOR check_retro_batches_person(p_person_id IN NUMBER,
1959 p_batch_id IN NUMBER)
1960 IS SELECT retro_batch_id,
1961 tim_id
1962 FROM hxt_timecards_f tim,
1963 hxt_det_hours_worked_f det
1964 WHERE tim.batch_id = p_batch_id
1965 AND tim.id = det.tim_id
1966 AND tim.for_person_id = p_person_id;
1967
1968
1969 l_retro_tab NUMTAB;
1970 l_tim_tab NUMTAB;
1971
1972
1973 l_det_rowtab VARCHARTAB;
1974 l_ret_rowtab VARCHARTAB;
1975 l_pbl_tab NUMTAB;
1976 l_pbl_ovn_tab NUMTAB;
1977 l_bb_tab NUMTAB;
1978 l_ovn_tab NUMTAB;
1979 l_hrs_tab NUMTAB;
1980 l_att1_tab VARCHARTAB;
1981 l_att2_tab VARCHARTAB;
1982 l_att3_tab VARCHARTAB;
1983 l_req_tab NUMTAB;
1984 l_rank_tab NUMTAB;
1985
1986 CURSOR get_details_in_ss(p_tim_id IN NUMBER)
1987 IS SELECT det.ROWID,
1988 ret.ROWID,
1989 det.pbl_line_id,
1990 pbl.object_version_number,
1991 ret.time_building_block_id,
1992 ret.object_version_number,
1993 ret2.measure,
1994 ret2.attribute1,
1995 ret2.attribute2,
1996 ret2.attribute3,
1997 ret2.request_id,
1998 rank() over ( partition by ret.time_building_block_id,ret.object_version_number
1999 ORDER by ret.ROWID) rank
2000 FROM hxt_sum_hours_worked_f sum,
2001 hxt_det_hours_worked_f det,
2002 hxc_ret_pay_latest_details ret,
2003 hxc_ret_pay_details ret2,
2004 FND_sessions sess,
2005 pay_batch_lines pbl
2006 WHERE sum.tim_id = p_tim_id
2007 AND sess.session_id = USERENV('sessionid')
2008 AND sess.effective_date BETWEEN sum.effective_start_date
2009 AND sum.effective_end_date
2010 AND det.parent_id = sum.id
2011 AND det.pay_status = 'C'
2012 AND det.pbl_line_id = pbl.batch_line_id
2013 AND sess.effective_date BETWEEN det.effective_start_date
2014 AND det.effective_end_date
2015 AND sum.time_building_block_id = ret.time_building_block_id
2016 AND sum.time_building_block_ovn = ret.object_version_number
2017 AND sum.time_building_block_id = ret2.time_building_block_id
2018 AND sum.time_building_block_ovn = ret2.object_version_number;
2019
2020
2021 CURSOR get_details_not_in_ss(p_tim_id IN NUMBER)
2022 IS SELECT det.ROWID,
2023 det.pbl_line_id,
2024 pbl.object_version_number
2025 FROM hxt_det_hours_worked_f det,
2026 hxt_sum_hours_worked_f sum,
2027 pay_batch_lines pbl,
2028 FND_Sessions sess
2029 WHERE sum.tim_id = p_tim_id
2030 AND sess.session_id = USERENV('sessionid')
2031 AND sess.effective_date BETWEEN sum.effective_start_date
2032 AND sum.effective_end_date
2033 AND det.parent_id = sum.id
2034 AND det.pay_status = 'C'
2035 AND det.pbl_line_id = pbl.batch_line_id
2036 AND sess.effective_date BETWEEN det.effective_start_date
2037 AND det.effective_end_date;
2038
2039 l_exit BOOLEAN := FALSE;
2040 l_replacement_batch NUMBER := 0;
2041
2042 BEGIN
2043
2044 IF p_person_id = 0
2045 THEN
2046 IF p_batch_status = 'T'
2047 THEN
2048 put_log('Transferred batch issue ');
2049 return;
2050 END IF;
2051 END IF;
2052 /*
2053 OPEN check_retro_batches(p_batch_id);
2054 FETCH check_retro_batches BULK COLLECT INTO l_retro_tab,l_tim_tab;
2055 CLOSE check_retro_batches;
2056 ELSE
2057 OPEN check_retro_batches_person(p_person_id,p_batch_id);
2058 FETCH check_retro_batches_person BULK COLLECT INTO l_retro_tab,l_tim_tab;
2059 CLOSE check_retro_batches_person;
2060 END IF;
2061 */
2062
2063 IF p_person_id = 0
2064 THEN
2065
2066 OPEN get_timecards(p_batch_id);
2067 FETCH get_timecards BULK COLLECT INTO l_tim_tab;
2068 CLOSE get_timecards;
2069
2070 ELSE
2071 OPEN get_timecards_person(p_batch_id,p_person_id);
2072 FETCH get_timecards_person BULK COLLECT INTO l_tim_tab;
2073 CLOSE get_timecards_person;
2074
2075 END IF;
2076
2077
2078 l_replacement_batch := replacement_batch(p_batch_id);
2079
2080 /*
2081 l_retro_tab := SET(l_retro_tab);
2082
2083 IF l_retro_tab.COUNT > 0
2084 THEN
2085 FOR i IN l_retro_tab.FIRST..l_retro_tab.LAST
2086 LOOP
2087 IF l_retro_tab(i) IS NOT NULL
2088 THEN
2089 put_log('The following retro batches exist ',g_cache);
2090 put_log('Batch: '||l_retro_tab(i),g_cache);
2091 put_log('Use OTL: Rollback OTLR Timecards or OTL: Rollback Batches From BEE(Retro)',g_cache);
2092 g_errored_tc(TO_CHAR(l_retro_tab(i))) := 1;
2093 l_exit := TRUE;
2094 END IF;
2095 END LOOP;
2096 IF l_exit
2097 THEN
2098 RETURN;
2099 END IF;
2100 END IF;
2101 */
2102
2103 l_tim_tab := SET(l_tim_tab);
2104
2105 IF l_tim_tab.COUNT > 0
2106 THEN
2107 FOR i IN l_tim_tab.FIRST..l_tim_tab.LAST
2108 LOOP
2109
2110 <<CONTINUE_TO_NEXT_TIMECARD>>
2111 LOOP
2112
2113 put_log('Rolling Back for tim: '||l_tim_tab(i));
2114 display_tc_details(l_tim_tab(i));
2115
2116 IF verify_retro_details(l_tim_tab(i)) = 'YES'
2117 THEN
2118 l_exit := TRUE;
2119 g_errored_tc(TO_CHAR(l_tim_tab(i))) := 1;
2120 EXIT CONTINUE_TO_NEXT_TIMECARD;
2121 END IF;
2122
2123 -- Bug 13777315
2124 IF verify_if_archived(l_tim_tab(i)) = 'YES'
2125 THEN
2126 l_exit := TRUE;
2127 g_errored_tc(TO_CHAR(l_tim_tab(i))) := 1;
2128 EXIT CONTINUE_TO_NEXT_TIMECARD;
2129 END IF;
2130
2131 IF verify_rollback_eligibility(l_tim_tab(i)) = 'NO'
2132 THEN
2133 l_exit := TRUE;
2134 g_errored_tc(TO_CHAR(l_tim_tab(i))) := 1;
2135 EXIT CONTINUE_TO_NEXT_TIMECARD;
2136 END IF;
2137
2138 put_log(' ',g_cache);
2139 put_log(' ',g_cache);
2140 put_log(' ',g_cache);
2141 put_log('Pre Rollback: All Details ',g_cache);
2142 display_all_details(l_tim_tab(i));
2143
2144 OPEN get_details_in_ss(l_tim_tab(i));
2145 FETCH get_details_in_ss BULK COLLECT INTO l_det_rowtab,
2146 l_ret_rowtab,
2147 l_pbl_tab,
2148 l_pbl_ovn_tab,
2149 l_bb_tab,
2150 l_ovn_tab,
2151 l_hrs_tab,
2152 l_att1_tab,
2153 l_att2_tab,
2154 l_att3_tab,
2155 l_req_tab,
2156 l_rank_tab ;
2157 CLOSE get_details_in_ss;
2158
2159 IF l_det_rowtab.COUNT > 0
2160 THEN
2161
2162 put_log('Updating hxt_det ');
2163 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2164 UPDATE hxt_det_hours_worked_f
2165 SET pay_status = 'P',
2166 pbl_line_id = NULL
2167 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2168
2169 put_log('Deleting duplicates from Ret Pay ');
2170 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2171 DELETE FROM hxc_ret_pay_latest_details
2172 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
2173 AND l_rank_tab(i) <> 1;
2174
2175 put_log('Updating Ret Pay');
2176 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2177 UPDATE hxc_ret_pay_latest_details
2178 SET request_id = l_req_tab(i),
2179 measure = l_hrs_tab(i),
2180 attribute1 = l_att1_tab(i),
2181 attribute2 = l_att2_tab(i),
2182 attribute3 = l_att3_tab(i)
2183 WHERE time_building_block_id = l_bb_tab(i)
2184 AND object_version_number = l_ovn_tab(i)
2185 AND l_rank_tab(i) = 1;
2186
2187
2188 l_pbl_tab := SET(l_pbl_tab);
2189 reverse_batch_lines(p_batch_id,
2190 l_pbl_tab);
2191
2192 set_session_date;
2193
2194 put_log('Updating Replacement Batch');
2195 update_replacement_batch_id(l_replacement_batch,
2196 l_tim_tab(i));
2197
2198 INSERT INTO hxt_reversed_batch_tc_all
2199 (batch_id,
2200 replacement_batch_id,
2201 tim_id)
2202 VALUES
2203 (p_batch_id,
2204 l_replacement_batch,
2205 l_tim_tab(i));
2206
2207 END IF;
2208
2209 OPEN get_details_not_in_ss(l_tim_tab(i));
2210 FETCH get_details_not_in_ss BULK COLLECT INTO l_det_rowtab,
2211 l_pbl_tab,
2212 l_pbl_ovn_tab;
2213 CLOSE get_details_not_in_ss;
2214
2215 IF l_det_rowtab.COUNT > 0
2216 THEN
2217
2218 put_log('Updating hxt_det for OTLR only');
2219 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2220 UPDATE hxt_det_hours_worked_f
2221 SET pay_status = 'P',
2222 pbl_line_id = NULL
2223 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2224
2225
2226 l_pbl_tab := SET(l_pbl_tab);
2227 reverse_batch_lines(p_batch_id,
2228 l_pbl_tab);
2229
2230 set_session_date;
2231
2232 put_log('Updating Replacement Batch');
2233 update_replacement_batch_id(l_replacement_batch,
2234 l_tim_tab(i));
2235
2236
2237 END IF;
2238
2239 put_log(' ',g_cache);
2240 put_log(' ',g_cache);
2241 put_log(' ',g_cache);
2242 put_log('Post Rollback: All Details',g_cache);
2243 display_all_details(l_tim_tab(i));
2244 commit_session;
2245
2246 EXIT CONTINUE_TO_NEXT_TIMECARD;
2247 END LOOP CONTINUE_TO_NEXT_TIMECARD;
2248
2249 END LOOP;
2250
2251 IF p_person_id = 0
2252 AND NOT l_exit
2253 THEN
2254 update_original_batch_id(p_batch_id,
2255 l_replacement_batch,
2256 l_tim_tab);
2257 END IF;
2258
2259 END IF;
2260
2261 END rollback_normal_batches;
2262
2263
2264
2265 PROCEDURE rollback_bee(errbuff IN OUT NOCOPY VARCHAR2,
2266 retcode IN OUT NOCOPY NUMBER,
2267 p_bg_id IN NUMBER,
2268 p_validate_mode IN VARCHAR2 DEFAULT 'VALIDATE',
2269 p_from_batch IN NUMBER,
2270 p_to_batch IN NUMBER DEFAULT NULL,
2271 p_batch_reference IN VARCHAR2 DEFAULT NULL,
2272 p_person_id IN NUMBER DEFAULT 0,
2273 p_single IN VARCHAR2 DEFAULT NULL,
2274 p_rollback_mode IN VARCHAR2 DEFAULT 'U'
2275 )
2276 IS
2277
2278
2279 l_sql VARCHAR2(4000) :=
2280 ' SELECT pbh.batch_id,
2281 pbh.batch_status
2282 FROM pay_batch_headers pbh,
2283 hxt_batch_states hbs
2284 WHERE pbh.business_group_id = :bg_id
2285 AND pbh.batch_id = hbs.batch_id
2286 AND hbs.status = ''VT''';
2287
2288
2289 l_batch_tab NUMTAB;
2290 l_status_tab VARCHARTAB;
2291 l_rollback_mode VARCHAR2(5);
2292
2293 BEGIN
2294
2295 put_log('Rollback Started ');
2296 g_program := 'HXTROLLBEE';
2297 delete_old_log;
2298
2299 put_log('===========',g_cache);
2300 put_log('Parameters ',g_cache);
2301 put_log('===========',g_cache);
2302 put_log('Business Group : '||p_bg_id,g_cache);
2303 put_log('Mode : '||p_validate_mode,g_cache);
2304 put_log('Start Batch : '||p_from_batch,g_cache);
2305 put_log('End Batch : '||p_to_batch,g_cache);
2306 put_log('Batch Reference : '||p_batch_reference,g_cache);
2307 put_log('Person : '||p_person_id,g_cache);
2308 put_log('Trans Batch Action : '||p_rollback_mode);
2309
2310 g_validate_mode := p_validate_mode;
2311
2312 set_session_date;
2313
2314 l_rollback_mode := NVL(p_rollback_mode,'U');
2315
2316
2317 IF p_from_batch IS NOT NULL
2318 AND p_to_batch IS NULL
2319 THEN
2320
2321 l_sql := l_sql||' AND pbh.batch_id = '||p_from_batch;
2322
2323 ELSIF p_from_batch IS NOT NULL
2324 AND p_to_batch IS NOT NULL
2325 THEN
2326
2327 l_sql := l_sql||' AND pbh.batch_id BETWEEN '||p_from_batch||' AND '||p_to_batch;
2328
2329 END IF;
2330
2331 IF p_batch_reference IS NOT NULL
2332 THEN
2333
2334 l_sql := l_sql||' AND pbh.batch_reference = '''||p_batch_reference||'''';
2335
2336 END IF;
2337
2338 put_log(l_sql);
2339
2340 EXECUTE IMMEDIATE l_sql
2341 BULK COLLECT INTO l_batch_tab,
2342 l_status_tab
2343 USING p_bg_id;
2344
2345 IF l_batch_tab.COUNT > 0
2346 THEN
2347
2348 FOR i IN l_batch_tab.FIRST..l_batch_tab.LAST
2349 LOOP
2350
2351 put_log('Rolling Back Batch: '||batch_name(l_batch_tab(i)),g_cache);
2352
2353 put_log('l_rollback Mode '||l_rollback_mode);
2354 put_log('l_status '||l_status_tab(i));
2355 IF ( l_rollback_mode = 'U'
2356 AND l_status_tab(i) IN ('U','V'))
2357 OR ( l_rollback_mode = 'T'
2358 AND NVL(p_person_id,0) <> 0)
2359 THEN
2360
2361 rollback_normal_batches(l_batch_tab(i),
2362 l_status_tab(i),
2363 NVL(p_person_id,0));
2364 ELSIF p_person_id IS NULL
2365 THEN
2366 put_log('Batch '||batch_name(l_batch_tab(i))||
2367 ' is Transferred. Transferred Batches cannot be rolled back without specifying a person ',g_cache);
2368 g_errored_tc(TO_CHAR(l_batch_tab(i))) := 1;
2369 ELSE
2370 put_log('Batch '||batch_name(l_batch_tab(i))||
2371 ' is Transferred. Option selected is to Delete lines from Unprocessed batches ',g_cache);
2372 g_errored_tc(TO_CHAR(l_batch_tab(i))) := 1;
2373 END IF;
2374
2375 END LOOP;
2376
2377 END IF;
2378
2379 IF g_validate_mode = 'VALIDATE'
2380 THEN
2381 put_log('Rollback Executed');
2382 ROLLBACK;
2383 END IF;
2384
2385 IF g_errored_tc.COUNT > 0
2386 THEN
2387 retcode := 1;
2388 END IF;
2389 display_cached_log;
2390
2391 END rollback_bee;
2392
2393
2394
2395 -- Bug 13954828
2396 -- Added processing for multiple timecards in the same retro batch.
2397
2398 PROCEDURE rollback_retro_batches(p_batch_id IN NUMBER)
2399 IS
2400
2401 CURSOR get_timecards(p_batch_id IN NUMBER)
2402 IS SELECT DISTINCT tim_id
2403 FROM pay_batch_lines pbl,
2404 hxt_det_hours_worked_f det
2405 WHERE pbl.batch_id = p_batch_id
2406 AND ( pbl.batch_line_id = det.retro_pbl_line_id
2407 OR pbl.batch_id = det.retro_batch_id);
2408
2409
2410
2411 -- Bug 14010150
2412 -- Added DISTINCT to the below cursors to take care of
2413 -- displaying the details correctly in RDB
2414 CURSOR get_inactive_summary_backouts(p_batch_id IN NUMBER,
2415 p_tim_id IN NUMBER)
2416 IS SELECT DISTINCT
2417 ROWIDTOCHAR(det.ROWID),
2418 ROWIDTOCHAR(ret.ROWID),
2419 pbl.batch_line_id
2420 FROM pay_batch_lines pbl,
2421 hxt_det_hours_worked_f det,
2422 hxt_sum_hours_worked_f sum,
2423 hxc_ret_pay_latest_details ret
2424 WHERE pbl.batch_id = p_batch_id
2425 AND pbl.batch_line_id = det.retro_pbl_line_id
2426 AND det.tim_id = p_tim_id
2427 AND det.pay_status = 'B'
2428 AND det.parent_id = sum.id
2429 AND sum.effective_start_date <= det.effective_start_date
2430 AND sum.effective_end_date >= det.effective_end_date
2431 AND sum.effective_end_date < hr_general.end_of_time
2432 AND sum.time_building_block_id = ret.time_building_block_id
2433 AND sum.time_building_block_ovn = ret.old_ovn
2434 AND det.retro_pbl_line_id = ret.retro_pbl_id
2435 AND ret.retro_batch_id = pbl.batch_id;
2436
2437
2438 CURSOR get_active_summary_backouts(p_batch_id IN NUMBER,
2439 p_tim_id IN NUMBER)
2440 IS SELECT DISTINCT
2441 ROWIDTOCHAR(det.ROWID),
2442 ROWIDTOCHAR(ret.ROWID),
2443 pbl.batch_line_id
2444 FROM pay_batch_lines pbl,
2445 hxt_det_hours_worked_f det,
2446 hxt_sum_hours_worked_f sum,
2447 hxc_ret_pay_latest_details ret
2448 WHERE pbl.batch_id = p_batch_id
2449 AND pbl.batch_line_id = det.retro_pbl_line_id
2450 AND det.pay_status = 'B'
2451 AND det.tim_id = p_tim_id
2452 AND det.parent_id = sum.id
2453 AND sum.effective_start_date <= det.effective_start_date
2454 AND sum.effective_end_date >= det.effective_end_date
2455 AND sum.effective_end_date = hr_general.end_of_time
2456 AND sum.time_building_block_id = ret.time_building_block_id
2457 AND sum.time_building_block_ovn = ret.object_version_number
2458 AND det.retro_pbl_line_id = ret.retro_pbl_id
2459 AND ret.retro_batch_id = pbl.batch_id;
2460
2461 CURSOR get_updated_records(p_batch_id IN NUMBER,
2462 p_tim_id IN NUMBER)
2463 IS SELECT DISTINCT
2464 ROWIDTOCHAR(ret.ROWID)
2465 FROM hxc_ret_pay_latest_details ret,
2466 hxt_sum_hours_worked_f sum
2467 WHERE hxt_batch_id = p_batch_id
2468 AND retro_batch_id IS NULL
2469 AND retro_pbl_id IS NULL
2470 AND pbl_id IS NOT NULL
2471 AND sum.tim_id = p_tim_id
2472 AND sum.time_building_block_id = ret.time_building_block_id;
2473
2474 CURSOR get_new_details_for_old_sum(p_batch_id IN NUMBER,
2475 p_tim_id IN NUMBER)
2476 IS SELECT DISTINCT
2477 ROWIDTOCHAR(det.ROWID),
2478 ROWIDTOCHAR(ret.ROWID),
2479 pbl.batch_line_id
2480 FROM hxt_det_hours_worked_f det,
2481 hxt_sum_hours_worked_f sum,
2482 hxc_ret_pay_latest_details ret,
2483 pay_batch_lines pbl
2484 WHERE det.pay_status = 'C'
2485 AND det.retro_batch_id = p_batch_id
2486 AND det.retro_pbl_line_id = pbl.batch_line_id
2487 AND det.tim_id = p_tim_id
2488 AND pbl.batch_id = det.retro_batch_id
2489 AND det.parent_id = sum.id
2490 AND det.effective_start_date >= sum.effective_start_date
2491 AND det.effective_end_date <= sum.effective_end_date
2492 AND sum.time_building_block_id = ret.time_building_block_id
2493 AND sum.time_building_block_ovn = ret.object_version_number
2494 AND ret.pbl_id = det.retro_pbl_line_id
2495 AND hxt_request_id IS NULL ;
2496
2497 CURSOR get_new_details_for_new_sum(p_batch_id IN NUMBER,
2498 p_tim_id IN NUMBER)
2499 IS SELECT DISTINCT
2500 ROWIDTOCHAR(det.ROWID),
2501 ROWIDTOCHAR(ret.ROWID),
2502 pbl.batch_line_id,
2503 RANK() OVER ( PARTITION BY ret.time_building_block_id,
2504 ret.object_version_number
2505 ORDER BY det.ROWID) rank
2506 FROM hxt_det_hours_worked_f det,
2507 hxt_sum_hours_worked_f sum,
2508 hxc_ret_pay_latest_details ret,
2509 pay_batch_lines pbl
2510 WHERE det.pay_status = 'C'
2511 AND det.retro_batch_id = p_batch_id
2512 AND det.tim_id = p_tim_id
2513 AND det.retro_pbl_line_id = pbl.batch_line_id
2514 AND pbl.batch_id = det.retro_batch_id
2515 AND det.parent_id = sum.id
2516 AND det.effective_start_date >= sum.effective_start_date
2517 AND det.effective_end_date <= sum.effective_end_date
2518 AND sum.time_building_block_id = ret.time_building_block_id
2519 AND sum.time_building_block_ovn = ret.object_version_number
2520 AND ret.pbl_id = det.retro_pbl_line_id
2521 AND hxt_request_id IS NOT NULL ;
2522
2523
2524 CURSOR get_adjusted_records(p_batch_id IN NUMBER,
2525 p_tim_id IN NUMBER)
2526 IS SELECT DISTINCT
2527 ROWIDTOCHAR(det.ROWID)
2528 FROM hxt_det_hours_worked_f det
2529 WHERE det.pay_status IN ('A','D')
2530 AND det.tim_id = p_tim_id
2531 AND retro_batch_id_2 = p_batch_id;
2532
2533 -- Bug 13814489
2534 -- Added NVL
2535 CURSOR get_backouts_not_in_ss(p_batch_id IN NUMBER,
2536 p_tim_id IN NUMBER)
2537 IS SELECT ROWIDTOCHAR(det.ROWID),
2538 pbl.batch_line_id
2539 FROM pay_batch_lines pbl,
2540 hxt_det_hours_worked_f det
2541 WHERE pbl.batch_id = p_batch_id
2542 AND pbl.batch_line_id = det.retro_pbl_line_id
2543 AND det.tim_id = p_tim_id
2544 AND det.pay_status = 'B'
2545 AND NVL(det.retro_batch_id,0) <> p_batch_id;
2546
2547
2548 CURSOR get_untouched_not_in_ss(p_batch_id IN NUMBER,
2549 p_tim_id IN NUMBER)
2550 IS SELECT ROWIDTOCHAR(det.ROWID)
2551 FROM hxt_det_hours_worked_f det
2552 WHERE retro_batch_id = p_batch_id
2553 AND pay_status = 'C'
2554 AND det.tim_id = p_tim_id
2555 AND retro_pbl_line_id IS NULL;
2556
2557 CURSOR get_touched_not_in_ss(p_batch_id IN NUMBER,
2558 p_tim_id IN NUMBER)
2559 IS SELECT ROWIDTOCHAR(det.ROWID),
2560 pbl.batch_line_id
2561 FROM pay_batch_lines pbl,
2562 hxt_det_hours_worked_f det
2563 WHERE pbl.batch_id = p_batch_id
2564 AND pbl.batch_line_id = det.retro_pbl_line_id
2565 AND det.tim_id = p_tim_id
2566 AND det.retro_batch_id = pbl.batch_id
2567 AND pay_status = 'C';
2568
2569
2570
2571 l_det_rowtab VARCHARTAB;
2572 l_ret_rowtab VARCHARTAB;
2573 l_rank_tab NUMTAB;
2574
2575 lines_tab NUMTAB := NUMTAB();
2576 l_pbl_tab NUMTAB;
2577 l_idtab NUMTAB;
2578 l_tim_tab NUMTAB;
2579
2580 BEGIN
2581
2582
2583 put_log(' ',g_cache);
2584 put_log(' ',g_cache);
2585 put_log(' ',g_cache);
2586 put_log('Starting Rollback for Batch '||batch_name(p_batch_id),g_cache);
2587 put_log('Batch Id : '||p_batch_id,g_cache);
2588 put_log(' ',g_cache);
2589 put_log(' ',g_cache);
2590
2591
2592 OPEN get_timecards(p_batch_id);
2593 FETCH get_timecards BULK COLLECT INTO l_tim_tab;
2594 CLOSE get_timecards;
2595
2596 IF l_tim_tab.COUNT > 0
2597 THEN
2598
2599 FOR x IN l_tim_tab.FIRST..l_tim_tab.LAST
2600 LOOP
2601
2602 IF display_pre_rollback_details(p_batch_id,l_tim_tab(x)) = 'NO'
2603 THEN
2604 put_log('Rollback Eligibility Error');
2605 RETURN;
2606 END IF;
2607
2608
2609 lines_tab := NUMTAB();
2610
2611
2612 OPEN get_inactive_summary_backouts(p_batch_id,l_tim_tab(x));
2613
2614 FETCH get_inactive_summary_backouts BULK COLLECT INTO l_det_rowtab,
2615 l_ret_rowtab,
2616 l_pbl_tab;
2617 CLOSE get_inactive_summary_backouts;
2618
2619
2620 IF l_det_rowtab.COUNT > 0
2621 THEN
2622
2623 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2624 UPDATE hxt_det_hours_worked_f
2625 SET pay_status = 'C',
2626 retro_pbl_line_id = retro_pbl_line_id_2
2627 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2628
2629 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2630 UPDATE hxc_ret_pay_latest_details
2631 SET retro_batch_id = NULL,
2632 retro_pbl_id = NULL,
2633 request_id = hxt_request_id,
2634 measure = hxt_measure,
2635 attribute1 = hxt_element_id,
2636 attribute2 = hxt_attribute2,
2637 attribute3 = hxt_attribute3,
2638 batch_id = hxt_batch_id,
2639 pbl_id = NULL,
2640 hxt_request_id = NULL,
2641 hxt_measure = NULL,
2642 hxt_element_id = NULL,
2643 hxt_attribute2 = NULL,
2644 hxt_attribute3 = NULL,
2645 hxt_batch_id = NULL
2646 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
2647
2648 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2649
2650 END IF;
2651
2652 OPEN get_active_summary_backouts(p_batch_id,l_tim_tab(x));
2653
2654 FETCH get_active_summary_backouts BULK COLLECT INTO l_det_rowtab,
2655 l_ret_rowtab,
2656 l_pbl_tab;
2657
2658 CLOSE get_active_summary_backouts;
2659
2660 IF l_det_rowtab.COUNT > 0
2661 THEN
2662
2663 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2664 UPDATE hxt_det_hours_worked_f
2665 SET pay_status = 'C',
2666 retro_pbl_line_id = retro_pbl_line_id_2
2667 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2668
2669
2670 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2671 UPDATE hxc_ret_pay_latest_details
2672 SET retro_batch_id = NULL,
2673 retro_pbl_id = NULL,
2674 request_id = old_request_id,
2675 measure = old_measure,
2676 attribute1 = old_attribute1,
2677 attribute2 = old_attribute2,
2678 attribute3 = old_attribute3,
2679 batch_id = old_batch_id,
2680 pbl_id = old_pbl_id,
2681 old_request_id = NULL,
2682 old_measure = NULL,
2683 old_attribute1 = NULL,
2684 old_attribute2 = NULL,
2685 old_attribute3 = NULL,
2686 old_batch_id = NULL,
2687 old_pbl_id = NULL
2688 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
2689
2690 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2691
2692 END IF;
2693
2694
2695 OPEN get_new_details_for_old_sum(p_batch_id,l_tim_tab(x));
2696
2697 FETCH get_new_details_for_old_sum BULK COLLECT INTO l_det_rowtab,
2698 l_ret_rowtab,
2699 l_pbl_tab;
2700
2701 CLOSE get_new_details_for_old_sum;
2702
2703
2704
2705 IF l_det_rowtab.COUNT > 0
2706 THEN
2707 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2708 DELETE FROM hxc_ret_pay_latest_details
2709 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
2710
2711 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2712 UPDATE hxt_det_hours_worked_f
2713 SET retro_pbl_line_id = NULL,
2714 pay_status = 'R'
2715 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2716
2717 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2718
2719 END IF;
2720
2721
2722
2723 OPEN get_new_details_for_new_sum(p_batch_id,l_tim_tab(x));
2724 FETCH get_new_details_for_new_sum BULK COLLECT INTO l_det_rowtab,
2725 l_ret_rowtab,
2726 l_pbl_tab,
2727 l_rank_tab;
2728 CLOSE get_new_details_for_new_sum;
2729
2730
2731 IF l_det_rowtab.COUNT > 0
2732 THEN
2733
2734 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2735 DELETE FROM hxc_ret_pay_latest_details
2736 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
2737 AND l_rank_tab(i) <> 1;
2738
2739
2740 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2741 UPDATE hxc_ret_pay_latest_details
2742 SET request_id = hxt_request_id,
2743 attribute1 = hxt_element_id,
2744 attribute2 = hxt_attribute2,
2745 attribute3 = hxt_attribute3,
2746 measure = hxt_measure,
2747 pbl_id = NULL,
2748 hxt_request_id = NULL,
2749 hxt_measure = NULL,
2750 hxt_element_id = NULL,
2751 hxt_attribute2 = NULL,
2752 hxt_attribute3 = NULL,
2753 hxt_batch_id = NULL
2754 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i))
2755 AND l_rank_tab(i) = 1;
2756
2757
2758 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2759 UPDATE hxt_det_hours_worked_f
2760 SET retro_pbl_line_id = NULL,
2761 pay_status = 'R'
2762 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2763
2764
2765 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2766
2767 END IF;
2768
2769 OPEN get_updated_records(p_batch_id,l_tim_tab(x));
2770 FETCH get_updated_records BULK COLLECT INTO l_ret_rowtab;
2771 CLOSE get_updated_records;
2772
2773 IF l_ret_rowtab.COUNT > 0
2774 THEN
2775
2776 FORALL i IN l_ret_rowtab.FIRST..l_ret_rowtab.LAST
2777 UPDATE hxc_ret_pay_latest_details
2778 SET old_measure = measure,
2779 old_attribute1 = attribute1,
2780 old_attribute2 = attribute2,
2781 old_attribute3 = attribute3,
2782 old_request_id = request_id,
2783 old_pbl_id = pbl_id,
2784 old_batch_id = batch_id,
2785 attribute1 = hxt_element_id,
2786 attribute2 = hxt_attribute2,
2787 attribute3 = hxt_attribute3,
2788 measure = hxt_measure,
2789 request_id = hxt_request_id,
2790 batch_id = hxt_batch_id,
2791 pbl_id = NULL,
2792 hxt_measure = NULL,
2793 hxt_request_id = NULL,
2794 hxt_element_id = NULL,
2795 hxt_attribute2 = NULL,
2796 hxt_attribute3 = NULL,
2797 hxt_batch_id = NULL
2798 WHERE ROWID = CHARTOROWID(l_ret_rowtab(i));
2799
2800 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2801
2802 END IF;
2803
2804
2805
2806
2807 OPEN get_adjusted_records(p_batch_id,l_tim_tab(x));
2808
2809 FETCH get_adjusted_records
2810 BULK COLLECT INTO l_det_rowtab;
2811
2812 CLOSE get_adjusted_records;
2813
2814 IF l_det_rowtab.COUNT > 0
2815 THEN
2816
2817 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2818 UPDATE hxt_det_hours_worked_f
2819 SET pay_status = 'C'
2820 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2821
2822 END IF;
2823
2824 OPEN get_backouts_not_in_ss(p_batch_id,l_tim_tab(x));
2825
2826 FETCH get_backouts_not_in_ss
2827 BULK COLLECT INTO l_det_rowtab,
2828 l_pbl_tab;
2829
2830 CLOSE get_backouts_not_in_ss;
2831
2832
2833 IF l_det_rowtab.COUNT > 0
2834 THEN
2835
2836 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2837 UPDATE hxt_det_hours_worked_f
2838 SET pay_status = 'C',
2839 retro_pbl_line_id = retro_pbl_line_id_2,
2840 retro_pbl_line_id_2 = NULL
2841 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2842
2843 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2844
2845 END IF;
2846
2847
2848 OPEN get_untouched_not_in_ss(p_batch_id,l_tim_tab(x));
2849
2850 FETCH get_untouched_not_in_ss BULK COLLECT INTO l_det_rowtab;
2851
2852 CLOSE get_untouched_not_in_ss;
2853
2854 IF l_det_rowtab.COUNT > 0
2855 THEN
2856
2857 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2858 UPDATE hxt_det_hours_worked_f
2859 SET pay_status = 'R'
2860 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2861
2862
2863 END IF;
2864
2865
2866 OPEN get_touched_not_in_ss(p_batch_id,l_tim_tab(x));
2867
2868 FETCH get_touched_not_in_ss BULK COLLECT INTO l_det_rowtab,
2869 l_pbl_tab;
2870
2871 CLOSE get_touched_not_in_ss;
2872
2873 IF l_det_rowtab.COUNT > 0
2874 THEN
2875
2876 FORALL i IN l_det_rowtab.FIRST..l_det_rowtab.LAST
2877 UPDATE hxt_det_hours_worked_f
2878 SET pay_status = 'R',
2879 retro_pbl_line_id = NULL
2880 WHERE ROWID = CHARTOROWID(l_det_rowtab(i));
2881
2882 lines_tab := lines_tab MULTISET UNION l_pbl_tab;
2883
2884 END IF;
2885
2886
2887 lines_tab := SET(lines_tab);
2888
2889 reverse_batch_lines(p_batch_id,lines_tab);
2890
2891
2892 display_post_rollback_details(p_batch_id,l_tim_tab(x));
2893
2894 END LOOP;
2895
2896 END IF;
2897
2898 UPDATE hxt_batch_states
2899 SET status = 'H'
2900 WHERE batch_id = p_batch_id;
2901
2902 commit_session;
2903
2904
2905 END rollback_retro_batches;
2906
2907
2908
2909
2910 PROCEDURE rollback_bee_retro(errbuff IN OUT NOCOPY VARCHAR2,
2911 retcode IN OUT NOCOPY NUMBER,
2912 p_bg_id IN NUMBER,
2913 p_validate_mode IN VARCHAR2 DEFAULT 'VALIDATE',
2914 p_from_batch IN NUMBER,
2915 p_to_batch IN NUMBER DEFAULT NULL,
2916 p_batch_reference IN VARCHAR2 DEFAULT NULL
2917 )
2918 IS
2919
2920 l_sql VARCHAR2(4000) :=
2921 ' SELECT pbh.batch_id,
2922 pbh.batch_status
2923 FROM pay_batch_headers pbh,
2924 hxt_batch_states hbs
2925 WHERE pbh.business_group_id = :bg_id
2926 AND pbh.batch_id = hbs.batch_id
2927 AND hbs.status = ''VT''';
2928
2929
2930 l_batch_tab NUMTAB;
2931 l_status_tab VARCHARTAB;
2932
2933
2934 BEGIN
2935
2936 put_log('Rollback Started ');
2937 g_program := 'HXTROLLBEERETRO';
2938 delete_old_log;
2939
2940 put_log('===========',g_cache);
2941 put_log('Parameters ',g_cache);
2942 put_log('===========',g_cache);
2943 put_log('Business Group : '||p_bg_id,g_cache);
2944 put_log('Mode : '||p_validate_mode,g_cache);
2945 put_log('Start Batch : '||p_from_batch,g_cache);
2946 put_log('End Batch : '||p_to_batch,g_cache);
2947 put_log('Batch Reference : '||p_batch_reference,g_cache);
2948
2949
2950 g_validate_mode := p_validate_mode;
2951 set_session_date;
2952
2953
2954 IF p_from_batch IS NOT NULL
2955 AND p_to_batch IS NULL
2956 THEN
2957
2958 l_sql := l_sql||' AND pbh.batch_id = '||p_from_batch;
2959
2960 ELSIF p_from_batch IS NOT NULL
2961 AND p_to_batch IS NOT NULL
2962 THEN
2963
2964 l_sql := l_sql||' AND pbh.batch_id BETWEEN '||p_from_batch||' AND '||p_to_batch;
2965
2966 END IF;
2967
2968 IF p_batch_reference IS NOT NULL
2969 THEN
2970
2971 l_sql := l_sql||' AND pbh.batch_reference = '''||p_batch_reference||'''';
2972
2973 END IF;
2974
2975 put_log(l_sql);
2976
2977 EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_batch_tab,
2978 l_status_tab
2979 USING p_bg_id;
2980
2981 IF l_batch_tab.COUNT > 0
2982 THEN
2983
2984 FOR i IN l_batch_tab.FIRST..l_batch_tab.LAST
2985 LOOP
2986 <<CONTINUE_TO_NEXT>>
2987 LOOP
2988
2989 put_log('Rolling Back Batch: '||batch_name(l_batch_tab(i)),g_cache);
2990 IF l_status_tab(i) = 'T'
2991 THEN
2992
2993 put_log('This batch is Transferred and need to be rolled Back from Element Entries first',g_cache);
2994 g_errored_tc(TO_CHAR(l_batch_tab(i))) := 1;
2995 EXIT CONTINUE_TO_NEXT;
2996
2997 END IF;
2998
2999 rollback_retro_batches(l_batch_tab(i));
3000
3001 EXIT CONTINUE_TO_NEXT;
3002
3003 END LOOP CONTINUE_TO_NEXT;
3004
3005 END LOOP;
3006
3007 END IF;
3008
3009 IF g_validate_mode = 'VALIDATE'
3010 THEN
3011 put_log('Rollback Executed');
3012
3013 ROLLBACK;
3014
3015 END IF;
3016
3017 IF g_errored_tc.COUNT > 0
3018 THEN
3019 retcode := 1;
3020 END IF;
3021
3022 display_cached_log;
3023
3024 END rollback_bee_retro;
3025
3026
3027
3028 PROCEDURE mark_timecards_fresh(p_tim_id IN NUMBER,
3029 p_batch_id IN NUMBER)
3030 IS
3031
3032 CURSOR get_latest_details
3033 IS SELECT id,
3034 parent_id,
3035 tim_id,
3036 date_worked,
3037 assignment_id,
3038 hours,
3039 time_in,
3040 time_out,
3041 element_type_id,
3042 fcl_earn_reason_code,
3043 ffv_cost_center_id,
3044 tas_id,
3045 location_id,
3046 sht_id,
3047 hrw_comment,
3048 ffv_rate_code_id,
3049 rate_multiple,
3050 hourly_rate,
3051 amount,
3052 fcl_tax_rule_code,
3053 separate_check_flag,
3054 seqno,
3055 created_by,
3056 creation_date,
3057 last_updated_by,
3058 last_update_date,
3059 last_update_login,
3060 actual_time_in,
3061 actual_time_out,
3062 effective_start_date,
3063 effective_end_date,
3064 project_id,
3065 job_id,
3066 earn_pol_id,
3067 NULL retro_batch_id,
3068 'P' pa_status,
3069 'P' pay_status,
3070 object_version_number,
3071 STATE_NAME ,
3072 COUNTY_NAME ,
3073 CITY_NAME ,
3074 ZIP_CODE
3075 FROM hxt_det_hours_worked
3076 WHERE tim_id = p_tim_id;
3077
3078 -- Bug 13832327
3079
3080 CURSOR c_get_ids(p_count number) IS
3081 SELECT hxt_seqno.NEXTVAL
3082 FROM dual
3083 connect by level <= p_count;
3084
3085 l_ids_tab NUMTAB; -- Bug 13832327
3086
3087 TYPE DETTABLE IS TABLE OF get_latest_details%ROWTYPE;
3088 l_det_tab DETTABLE;
3089 l_rowid VARCHAR2(30);
3090 l_ovn NUMBER;
3091 l_id NUMBER;
3092
3093 l_replacement_batch NUMBER;
3094
3095
3096 BEGIN
3097
3098 OPEN get_latest_details;
3099
3100 FETCH get_latest_details BULK COLLECT INTO l_det_tab;
3101
3102 CLOSE get_latest_details;
3103
3104
3105 -- Bug 13814489
3106 -- Added 'R' in the IN list.
3107 UPDATE hxt_det_hours_worked_f
3108 SET effective_end_date = SYSDATE -1
3109 WHERE tim_id = p_tim_id
3110 AND effective_end_date = hr_general.end_of_time
3111 AND pay_status IN ('A','B','D','R');
3112
3113 -- Bug 13777315
3114 UPDATE hxt_det_hours_worked_f
3115 SET pay_status = 'Z'
3116 WHERE tim_id = p_tim_id
3117 AND effective_end_date <> hr_general.end_of_time
3118 AND pay_status = 'C';
3119
3120 UPDATE hxt_det_hours_worked_f
3121 SET effective_end_date = SYSDATE -1,
3122 pay_status = 'Z'
3123 WHERE tim_id = p_tim_id
3124 AND effective_end_date = hr_general.end_of_time
3125 AND pay_status = 'C';
3126
3127 UPDATE hxt_det_hours_worked_f
3128 SET rollback_status = 'R'
3129 WHERE tim_id = p_tim_id
3130 AND rollback_status IS NULL;
3131
3132 -- Bug 13832327
3133 open c_get_ids(l_det_tab.count);
3134 fetch c_get_ids bulk collect into l_ids_tab limit l_det_tab.count;
3135 close c_get_ids;
3136
3137 FOR i IN l_det_tab.FIRST..l_det_tab.LAST
3138 LOOP
3139
3140 -- l_id := hxt_seqno.NEXTVAL;
3141 -- Commented this statement and added the above cursor for the Bug 13832327
3142
3143 HXT_DML.insert_HXT_DET_HOURS_WORKED
3144 (p_rowid => l_rowid
3145 ,p_id => l_ids_tab(i) -- Bug 13832327
3146 ,p_parent_id => l_det_tab(i).parent_id
3147 ,p_tim_id => l_det_tab(i).tim_id
3148 ,p_date_worked => l_det_tab(i).date_worked
3149 ,p_assignment_id => l_det_tab(i).assignment_id
3150 ,p_hours => l_det_tab(i).hours
3151 ,p_time_in => l_det_tab(i).time_in
3152 ,p_time_out => l_det_tab(i).time_out
3153 ,p_element_type_id => l_det_tab(i).element_type_id
3154 ,p_fcl_earn_reason_code => l_det_tab(i).fcl_earn_reason_code
3155 ,p_ffv_cost_center_id => l_det_tab(i).ffv_cost_center_id
3156 ,p_ffv_labor_account_id => NULL
3157 ,p_tas_id => l_det_tab(i).tas_id
3158 ,p_location_id => l_det_tab(i).location_id
3159 ,p_sht_id => l_det_tab(i).sht_id
3160 ,p_hrw_comment => l_det_tab(i).hrw_comment
3161 ,p_ffv_rate_code_id => l_det_tab(i).ffv_rate_code_id
3162 ,p_rate_multiple => l_det_tab(i).rate_multiple
3163 ,p_hourly_rate => l_det_tab(i).hourly_rate
3164 ,p_amount => l_det_tab(i).amount
3165 ,p_fcl_tax_rule_code => l_det_tab(i).fcl_tax_rule_code
3166 ,p_separate_check_flag => l_det_tab(i).separate_check_flag
3167 ,p_seqno => l_det_tab(i).seqno
3168 ,p_created_by => l_det_tab(i).created_by
3169 ,p_creation_date => l_det_tab(i).creation_date
3170 ,p_last_updated_by => l_det_tab(i).last_updated_by
3171 ,p_last_update_date => l_det_tab(i).last_update_date
3172 ,p_last_update_login => l_det_tab(i).last_update_login
3173 ,p_actual_time_in => l_det_tab(i).actual_time_in
3174 ,p_actual_time_out => l_det_tab(i).actual_time_out
3175 ,p_effective_start_date => l_det_tab(i).effective_start_date
3176 ,p_effective_end_date => l_det_tab(i).effective_end_date
3177 ,p_project_id => l_det_tab(i).project_id
3178 ,p_job_id => l_det_tab(i).job_id
3179 ,p_earn_pol_id => l_det_tab(i).earn_pol_id
3180 ,p_retro_batch_id => l_det_tab(i).retro_batch_id
3181 ,p_pa_status => l_det_tab(i).pa_status
3182 ,p_pay_status => l_det_tab(i).pay_status
3183 ,p_object_version_number => l_ovn
3184 ,p_STATE_NAME => l_det_tab(i).STATE_NAME
3185 ,p_COUNTY_NAME => l_det_tab(i).COUNTY_NAME
3186 ,p_CITY_NAME => l_det_tab(i).CITY_NAME
3187 ,p_ZIP_CODE => l_det_tab(i).ZIP_CODE
3188 );
3189
3190 put_log(l_rowid||l_id||l_ovn);
3191
3192 END LOOP;
3193
3194
3195 l_replacement_batch := replacement_batch(p_batch_id);
3196
3197 UPDATE hxt_timecards
3198 SET batch_id = l_replacement_batch
3199 WHERE id = p_tim_id;
3200
3201 INSERT INTO hxt_reversed_batch_tc_all
3202 (batch_id,
3203 replacement_batch_id,
3204 tim_id)
3205 VALUES
3206 (p_batch_id,
3207 l_replacement_batch,
3208 p_tim_id);
3209
3210
3211 END mark_timecards_fresh;
3212
3213
3214
3215 PROCEDURE purge_timecard(p_tim_id IN NUMBER)
3216 IS
3217
3218
3219
3220 BEGIN
3221
3222 put_log('Purging this timecard '||p_tim_id);
3223
3224 DELETE FROM hxt_det_hours_worked_f
3225 WHERE tim_id = p_tim_id;
3226
3227 DELETE FROM hxt_sum_hours_worked_f
3228 WHERE tim_id = p_tim_id;
3229
3230 DELETE FROM hxt_timecards_f
3231 WHERE id = p_tim_id;
3232
3233 put_log('All Details for this timecard are deleted from OTM',g_cache);
3234
3235
3236 END purge_timecard;
3237
3238
3239
3240 PROCEDURE rollback_all_details_to_otm(p_tim_id IN NUMBER,
3241 p_batch_id IN NUMBER)
3242 IS
3243
3244 CURSOR get_outstanding_details
3245 IS SELECT det.tim_id
3246 FROM hxt_timecards tim,
3247 hxt_det_hours_worked det,
3248 hxt_batch_states hbs
3249 WHERE tim.id = p_tim_id
3250 AND det.tim_id = tim.id
3251 AND hbs.batch_id = tim.batch_id
3252 AND hbs.status NOT IN ('VT')
3253 AND det.pay_status = 'P';
3254
3255 CURSOR get_all_details
3256 IS SELECT id,
3257 pbl_line_id,
3258 retro_pbl_line_id,
3259 retro_pbl_line_id_2,
3260 retro_batch_id,
3261 retro_batch_id_2,
3262 pay_status
3263 FROM hxt_det_hours_worked_f
3264 WHERE tim_id = p_tim_id
3265 AND pay_status IN ('A','B','C','D')
3266 ORDER BY pay_status;
3267
3268
3269 CURSOR get_details_from_ss
3270 IS SELECT DISTINCT
3271 sum.id,
3272 ret.time_building_block_id,
3273 sum.time_building_block_ovn,
3274 ret2.object_version_number,
3275 ret2.measure,
3276 ret2.attribute1,
3277 ret2.attribute2,
3278 ret2.attribute3,
3279 ret2.request_id
3280 FROM hxt_sum_hours_worked sum,
3281 hxc_ret_pay_latest_details ret,
3282 hxc_ret_pay_details ret2
3283 WHERE sum.tim_id = p_tim_id
3284 AND sum.time_building_block_id = ret.time_building_block_id
3285 AND ret.time_building_block_id = ret2.time_building_block_id;
3286
3287
3288
3289 l_id_tab NUMTAB;
3290 l_pbl_tab NUMTAB;
3291 l_rpbl_tab NUMTAB;
3292 l_rpbl2_tab NUMTAB;
3293 l_ret_tab NUMTAB;
3294 l_ret2_tab NUMTAB;
3295 l_status_tab VARCHARTAB;
3296
3297 l_sum_id_tab NUMTAB;
3298 l_sum_ovn_tab NUMTAB;
3299 l_ret_ovn_tab NUMTAB;
3300 l_hrs_tab NUMTAB;
3301 l_att1_tab VARCHARTAB;
3302 l_att2_tab VARCHARTAB;
3303 l_att3_tab VARCHARTAB;
3304 l_req_tab NUMTAB;
3305
3306 l_batch_tab NUMTAB;
3307 l_lines_list NUMTAB;
3308
3309
3310 BEGIN
3311
3312 OPEN get_outstanding_details;
3313
3314 FETCH get_outstanding_details
3315 BULK COLLECT INTO l_id_tab;
3316
3317 CLOSE get_outstanding_details;
3318
3319
3320 IF l_id_tab.COUNT >0
3321 THEN
3322
3323 put_log('This '||p_tim_id||' is still untransferred to BEE');
3324 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3325 RETURN;
3326
3327 END IF;
3328
3329
3330 IF verify_rollback_eligibility(p_tim_id) = 'NO'
3331 THEN
3332 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3333 RETURN;
3334 END IF;
3335
3336 -- Bug 13777315
3337 IF verify_if_archived(p_tim_id) = 'YES'
3338 THEN
3339 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3340 RETURN;
3341 END IF;
3342
3343 SAVEPOINT TC_SAVEPOINT;
3344
3345 OPEN get_all_details;
3346 FETCH get_all_details BULK COLLECT INTO l_id_tab,
3347 l_pbl_tab,
3348 l_rpbl_tab,
3349 l_rpbl2_tab,
3350 l_ret_tab,
3351 l_ret2_tab,
3352 l_status_tab;
3353 CLOSE get_all_details;
3354
3355 IF l_id_tab.COUNT > 0
3356 THEN
3357
3358 -- Bug 13814489
3359 -- Displaying all details here
3360 -- display_details(p_tim_id,l_id_tab);
3361 display_all_details(p_tim_id);
3362
3363
3364 l_pbl_tab := SET(l_pbl_tab);
3365
3366 l_batch_tab := l_ret_tab MULTISET UNION l_ret2_tab MULTISET UNION NUMTAB(p_batch_id);
3367
3368 l_lines_list := l_pbl_tab MULTISET UNION l_rpbl_tab MULTISET UNION l_rpbl2_tab;
3369
3370 l_lines_list := SET(l_lines_list);
3371
3372 l_batch_tab := SET(l_batch_tab);
3373
3374 FOR i IN l_batch_tab.FIRST..l_batch_tab.LAST
3375 LOOP
3376 IF NVL(l_batch_tab(i),0) <> 0
3377 THEN
3378 put_log('Reversing Batch '||batch_name(l_batch_tab(i)));
3379
3380 IF g_rlbk_status = 'U'
3381 AND batch_status(l_batch_tab(i)) NOT IN ('U','V')
3382 THEN
3383 ROLLBACK TO TC_SAVEPOINT;
3384 put_log('Batch '||batch_name(l_batch_tab(i))
3385 ||' is transferred and we are not Rolling back such timecards',g_cache);
3386 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3387 RETURN;
3388 END IF;
3389
3390 reverse_batch_lines(l_batch_tab(i),
3391 l_lines_list);
3392
3393 set_session_date;
3394 END IF;
3395
3396 END LOOP;
3397
3398 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3399 UPDATE hxt_det_hours_worked_f
3400 SET rollback_status = 'X'
3401 WHERE id = l_id_tab(i);
3402
3403
3404 END IF;
3405
3406 OPEN get_details_from_ss;
3407 FETCH get_details_from_ss BULK COLLECT INTO l_id_tab,
3408 l_sum_id_tab,
3409 l_sum_ovn_tab,
3410 l_ret_ovn_tab,
3411 l_hrs_tab,
3412 l_att1_tab,
3413 l_att2_tab,
3414 l_att3_tab,
3415 l_req_tab;
3416 CLOSE get_details_from_ss;
3417
3418 IF l_id_tab.COUNT > 0
3419 THEN
3420 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3421 DELETE FROM hxc_ret_pay_latest_details
3422 WHERE time_building_block_id = l_sum_id_tab(i)
3423 AND object_version_number <> l_sum_ovn_tab(i);
3424
3425
3426 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3427 DELETE FROM hxc_ret_pay_details
3428 WHERE time_building_block_id = l_sum_id_tab(i)
3429 AND object_version_number <> l_sum_ovn_tab(i);
3430
3431 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3432 UPDATE hxc_ret_pay_latest_details
3433 SET measure = l_hrs_tab(i),
3434 attribute1 = l_att1_tab(i),
3435 attribute2 = l_att2_tab(i),
3436 attribute3 = l_att3_tab(i),
3437 request_id = l_req_tab(i),
3438 old_measure = NULL,
3439 old_attribute1 = NULL,
3440 old_attribute2 = NULL,
3441 old_attribute3 = NULL,
3442 old_request_id = NULL,
3443 old_batch_id = NULL,
3444 old_pbl_id = NULL,
3445 retro_batch_id = NULL,
3446 retro_pbl_id = NULL,
3447 hxt_request_id = NULL,
3448 hxt_element_id = NULL,
3449 hxt_attribute2 = NULL,
3450 hxt_attribute3 = NULL,
3451 old_ovn = NULL
3452 WHERE time_building_block_id = l_sum_id_tab(i)
3453 AND object_version_number = l_sum_ovn_tab(i)
3454 AND l_ret_ovn_tab(i) = l_sum_ovn_tab(i);
3455
3456 END IF;
3457
3458 mark_timecards_fresh(p_tim_id,
3459 p_batch_id);
3460
3461 display_all_details(p_tim_id);
3462
3463 commit_session;
3464
3465
3466 END rollback_all_details_to_otm;
3467
3468
3469
3470
3471 PROCEDURE rollback_all_details_to_otl(p_tim_id IN NUMBER,
3472 p_batch_id IN NUMBER,
3473 p_otm_entries IN VARCHAR2)
3474 IS
3475
3476 CURSOR get_outstanding_details
3477 IS SELECT det.tim_id
3478 FROM hxt_timecards tim,
3479 hxt_det_hours_worked det,
3480 hxt_batch_states hbs
3481 WHERE tim.id = p_tim_id
3482 AND det.tim_id = tim.id
3483 AND hbs.batch_id = tim.batch_id
3484 AND hbs.status NOT IN ('VT')
3485 AND det.pay_status = 'P';
3486
3487 CURSOR get_all_details
3488 IS SELECT id,
3489 pbl_line_id,
3490 retro_pbl_line_id,
3491 retro_pbl_line_id_2,
3492 retro_batch_id,
3493 retro_batch_id_2,
3494 pay_status
3495 FROM hxt_det_hours_worked_f
3496 WHERE tim_id = p_tim_id
3497 AND pay_status IN ('A','B','C','D')
3498 ORDER BY pay_status;
3499
3500 CURSOR get_otm_entries
3501 IS SELECT id
3502 FROM hxt_sum_hours_worked_f
3503 WHERE tim_id = p_tim_id
3504 AND time_building_block_id IS NULL ;
3505
3506
3507 CURSOR get_details_from_ss
3508 IS SELECT DISTINCT
3509 sum.id,
3510 ret.time_building_block_id,
3511 ret2.object_version_number,
3512 ret.resource_id,
3513 ret.approval_status,
3514 ret.start_time,
3515 ret.stop_time,
3516 ret.application_set_id,
3517 ret.last_update_date,
3518 ret.comment_text,
3519 ret.resource_type ,
3520 ret.org_id,
3521 ret.business_group_id,
3522 ret.timecard_id,
3523 ret2.attribute1,
3524 ret2.attribute2,
3525 ret2.attribute3,
3526 ret2.measure
3527 FROM hxt_sum_hours_worked sum,
3528 hxc_ret_pay_latest_details ret,
3529 hxc_ret_pay_details ret2
3530 WHERE sum.tim_id = p_tim_id
3531 AND sum.time_building_block_id = ret2.time_building_block_id
3532 AND sum.time_building_block_ovn = ret2.object_version_number
3533 AND ret.time_building_block_id = ret2.time_building_block_id
3534 AND ret.object_version_number = ret2.object_version_number;
3535
3536 CURSOR get_history_from_ss
3537 IS SELECT DISTINCT
3538 sum.id,
3539 ret2.time_building_block_id,
3540 ret2.object_version_number,
3541 htd.transaction_detail_id
3542 FROM hxt_sum_hours_worked_f sum,
3543 hxc_ret_pay_details ret2,
3544 hxc_transaction_details htd,
3545 hxc_transactions ht
3546 WHERE sum.tim_id = p_tim_id
3547 AND sum.time_building_block_id = ret2.time_building_block_id
3548 AND sum.time_building_block_ovn = ret2.object_version_number
3549 AND htd.time_building_block_id = ret2.time_building_block_id
3550 AND htd.time_building_block_ovn = ret2.object_version_number
3551 AND htd.transaction_id = ht.transaction_id
3552 AND ht.type = 'RETRIEVAL'
3553 AND ht.transaction_process_id = -1 ;
3554
3555
3556 l_id_tab NUMTAB;
3557 l_pbl_tab NUMTAB;
3558 l_rpbl_tab NUMTAB;
3559 l_rpbl2_tab NUMTAB;
3560 l_ret_tab NUMTAB;
3561 l_ret2_tab NUMTAB;
3562 l_status_tab VARCHARTAB;
3563
3564 l_sum_id_tab NUMTAB;
3565 l_sum_ovn_tab NUMTAB;
3566 l_ret_ovn_tab NUMTAB;
3567 l_hrs_tab NUMTAB;
3568 l_att1_tab VARCHARTAB;
3569 l_att2_tab VARCHARTAB;
3570 l_att3_tab VARCHARTAB;
3571 l_req_tab NUMTAB;
3572
3573 l_batch_tab NUMTAB;
3574 l_lines_list NUMTAB;
3575
3576 l_lines_old_batch_id NUMTAB ;
3577 l_lines_request_id NUMTAB ;
3578 l_lines_old_request_id NUMTAB ;
3579 l_lines_resource_id NUMTAB ;
3580 l_lines_time_building_block_id NUMTAB ;
3581 l_lines_approval_status VARCHARTAB ;
3582 l_lines_start_time DATETAB ;
3583 l_lines_stop_time DATETAB ;
3584 l_lines_org_id NUMTAB ;
3585 l_lines_business_group_id NUMTAB ;
3586 l_lines_timecard_id NUMTAB ;
3587 l_lines_attribute1 VARCHARTAB ;
3588 l_lines_attribute2 VARCHARTAB ;
3589 l_lines_attribute3 VARCHARTAB ;
3590 l_lines_measure NUMTAB ;
3591 l_lines_object_version_number NUMTAB ;
3592 l_lines_old_ovn NUMTAB ;
3593 l_lines_old_measure NUMTAB ;
3594 l_lines_old_attribute1 VARCHARTAB ;
3595 l_lines_old_attribute2 VARCHARTAB ;
3596 l_lines_old_attribute3 VARCHARTAB ;
3597 l_lines_pbl_id NUMTAB ;
3598 l_lines_retro_pbl_id NUMTAB ;
3599 l_lines_old_pbl_id NUMTAB ;
3600 l_lines_batch_id NUMTAB ;
3601 l_lines_retro_batch_id NUMTAB ;
3602 l_lines_application_set_id NUMTAB ;
3603 l_lines_comment_text VARCHARTAB ;
3604 l_lines_last_update_date DATETAB ;
3605 l_lines_resource_type VARCHARTAB ;
3606 l_lines_td_id NUMTAB;
3607 l_lines_trans_id NUMTAB;
3608
3609 l_ora_error BOOLEAN;
3610
3611
3612
3613 BEGIN
3614
3615 IF p_otm_entries = 'REPORT'
3616 THEN
3617
3618 OPEN get_otm_entries;
3619
3620 FETCH get_otm_entries
3621 BULK COLLECT INTO l_id_tab;
3622
3623 CLOSE get_otm_entries;
3624
3625 IF l_id_tab.COUNT > 0
3626 THEN
3627
3628 put_log('There are OTM manual entries ');
3629 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3630 RETURN;
3631
3632 END IF;
3633
3634 END IF;
3635
3636
3637 IF verify_rollback_eligibility(p_tim_id) = 'NO'
3638 THEN
3639 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3640 RETURN;
3641 END IF;
3642
3643 -- Bug 13777315
3644 IF verify_if_archived(p_tim_id) = 'YES'
3645 THEN
3646 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3647 RETURN;
3648 END IF;
3649
3650
3651 SAVEPOINT TC_SAVEPOINT;
3652
3653 OPEN get_all_details;
3654
3655 FETCH get_all_details BULK COLLECT INTO l_id_tab,
3656 l_pbl_tab,
3657 l_rpbl_tab,
3658 l_rpbl2_tab,
3659 l_ret_tab,
3660 l_ret2_tab,
3661 l_status_tab;
3662 CLOSE get_all_details;
3663
3664 IF l_id_tab.COUNT > 0
3665 THEN
3666
3667 -- Bug 13814489
3668 -- Displaying all details here.
3669 -- display_details(p_tim_id,l_id_tab);
3670 display_all_details(p_tim_id);
3671
3672
3673 l_pbl_tab := SET(l_pbl_tab);
3674
3675 l_batch_tab := l_ret_tab MULTISET UNION l_ret2_tab MULTISET UNION NUMTAB(p_batch_id);
3676
3677 l_lines_list := l_pbl_tab MULTISET UNION l_rpbl_tab MULTISET UNION l_rpbl2_tab;
3678
3679 l_lines_list := SET(l_lines_list);
3680
3681 FOR i IN l_batch_tab.FIRST..l_batch_tab.LAST
3682 LOOP
3683
3684 IF NVL(l_batch_tab(i),0) <> 0
3685 THEN
3686 put_log('Reversing Batch '||batch_name(l_batch_tab(i)));
3687
3688 IF g_rlbk_status = 'U'
3689 AND batch_status(l_batch_tab(i)) NOT IN ('U','V')
3690 THEN
3691 ROLLBACK TO TC_SAVEPOINT;
3692 put_log('Batch '||batch_name(l_batch_tab(i))
3693 ||' is transferred and we are not Rolling back such timecards',g_cache);
3694 g_errored_tc(TO_CHAR(p_tim_id)) := 1;
3695 RETURN;
3696 END IF;
3697
3698 reverse_batch_lines(l_batch_tab(i),
3699 l_lines_list);
3700
3701 set_session_date;
3702 END IF;
3703
3704 END LOOP;
3705
3706 END IF;
3707
3708
3709 OPEN get_details_from_ss;
3710 FETCH get_details_from_ss BULK COLLECT INTO l_id_tab,
3711 l_lines_time_building_block_id,
3712 l_lines_object_version_number,
3713 l_lines_resource_id,
3714 l_lines_approval_status,
3715 l_lines_start_time,
3716 l_lines_stop_time,
3717 l_lines_application_set_id,
3718 l_lines_last_update_date,
3719 l_lines_comment_text,
3720 l_lines_resource_type,
3721 l_lines_org_id,
3722 l_lines_business_group_id,
3723 l_lines_timecard_id,
3724 l_lines_attribute1,
3725 l_lines_attribute2,
3726 l_lines_attribute3,
3727 l_lines_measure;
3728 -- l_lines_td_id;
3729
3730 CLOSE get_details_from_ss;
3731
3732
3733 IF l_id_tab.COUNT > 0
3734 THEN
3735
3736 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3737 DELETE FROM hxc_ret_pay_latest_details
3738 WHERE time_building_block_id = l_lines_time_building_block_id(i);
3739
3740 /*
3741 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3742 DELETE FROM hxc_ret_pay_details
3743 WHERE time_building_block_id = l_lines_time_building_block_id(i);
3744
3745 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3746 UPDATE hxc_transaction_details
3747 SET status = 'ROLLEDBACK'
3748 WHERE transaction_detail_id = l_lines_td_id(i);
3749 */
3750
3751
3752
3753 l_ora_error := FALSE;
3754 BEGIN
3755 FORALL i IN l_lines_time_building_block_id.FIRST..l_lines_time_building_block_id.LAST SAVE EXCEPTIONS
3756 INSERT INTO hxc_pay_latest_details
3757 (
3758 resource_id,
3759 time_building_block_id,
3760 object_version_number,
3761 approval_status,
3762 start_time,
3763 stop_time,
3764 application_set_id,
3765 last_update_date,
3766 comment_text,
3767 resource_type ,
3768 org_id,
3769 business_group_id,
3770 timecard_id,
3771 attribute1,
3772 attribute2,
3773 attribute3,
3774 measure)
3775 VALUES (
3776 l_lines_resource_id(i),
3777 l_lines_time_building_block_id(i),
3778 l_lines_object_version_number(i),
3779 l_lines_approval_status(i),
3780 l_lines_start_time(i),
3781 l_lines_stop_time(i),
3782 l_lines_application_set_id(i),
3783 NVL(l_lines_last_update_date(i),SYSDATE),
3784 l_lines_comment_text(i),
3785 'PERSON',
3786 l_lines_org_id(i),
3787 l_lines_business_group_id(i),
3788 l_lines_timecard_id(i),
3789 l_lines_attribute1(i),
3790 l_lines_attribute2(i),
3791 l_lines_attribute3(i),
3792 l_lines_measure(i));
3793
3794 EXCEPTION
3795 WHEN TABLE_EXCEPTION
3796 THEN
3797 put_log(' Exception raised ');
3798 -- Bug 13777315
3799 IF SQL%BULK_EXCEPTIONS.COUNT > 0
3800 THEN
3801 put_log('List of Details with Exceptions ');
3802 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
3803 LOOP
3804 put_log('TBB_Id '||l_lines_time_building_block_id(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
3805 ' TBB_OVN '||l_lines_object_version_number(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
3806 put_log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
3807 IF SQL%BULK_EXCEPTIONS(n).ERROR_CODE <> 1
3808 THEN
3809 l_ora_error := TRUE;
3810 END IF;
3811 END LOOP;
3812 IF l_ora_error
3813 THEN
3814 RAISE INVALID_DETAIL;
3815 END IF;
3816
3817 END IF;
3818 END;
3819
3820 display_numtab('List of timecards ',SET(l_lines_timecard_id));
3821 update_transferred_to(SET(l_lines_timecard_id));
3822
3823 END IF;
3824
3825 -- Bug 13777315
3826 put_log('Selecting Retrieval History from Time Store');
3827 OPEN get_history_from_ss;
3828 FETCH get_history_from_ss BULK COLLECT INTO l_id_tab,
3829 l_lines_time_building_block_id,
3830 l_lines_object_version_number,
3831 l_lines_td_id;
3832 CLOSE get_history_from_ss;
3833
3834 IF l_id_tab.COUNT > 0
3835 THEN
3836
3837 display_numtab('Transaction Details ',l_lines_td_id);
3838
3839 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3840 DELETE FROM hxc_ret_pay_details
3841 WHERE time_building_block_id = l_lines_time_building_block_id(i);
3842
3843 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
3844 UPDATE hxc_transaction_details
3845 SET status = 'ROLLEDBACK',
3846 exception_description = 'Rollback Request:'||FND_GLOBAL.conc_request_id
3847 WHERE transaction_detail_id = l_lines_td_id(i);
3848
3849 END IF;
3850
3851
3852
3853 purge_timecard(p_tim_id);
3854
3855 commit_session;
3856
3857
3858 END rollback_all_details_to_otl;
3859
3860
3861 PROCEDURE rollback_xfer_time_otlr(errbuff OUT NOCOPY VARCHAR2,
3862 retcode OUT NOCOPY NUMBER,
3863 p_bg_id IN NUMBER,
3864 p_session_date IN VARCHAR2,
3865 p_validate_mode IN VARCHAR2 DEFAULT 'VALIDATE',
3866 p_payroll_id IN NUMBER,
3867 p_time_period_id IN NUMBER,
3868 p_gre_id IN NUMBER DEFAULT NULL,
3869 p_organization_id IN NUMBER DEFAULT NULL,
3870 p_location_id IN NUMBER DEFAULT NULL,
3871 p_person_id IN NUMBER DEFAULT NULL,
3872 p_batch_ref IN VARCHAR2 DEFAULT NULL,
3873 p_batch_id IN NUMBER DEFAULT NULL,
3874 p_batch_status IN VARCHAR2 DEFAULT 'U',
3875 p_destination IN VARCHAR2 DEFAULT 'OTM',
3876 p_is_timestore IN VARCHAR2 DEFAULT '0',
3877 p_delete IN VARCHAR2 DEFAULT 'REPORT'
3878 )
3879 IS
3880
3881
3882 l_query VARCHAR2(4000) :=
3883 'SELECT id,batch_id
3884 FROM hxt_timecards_f tim,
3885 per_time_periods ptp
3886 WHERE tim.payroll_id = :payroll_id
3887 AND tim.time_period_id = :period_id
3888 AND ptp.time_period_id = tim.time_period_id
3889 AND ptp.payroll_id = tim.payroll_id';
3890
3891
3892 l_asg_sql VARCHAR2(400) :=
3893 'SELECT id
3894 FROM hxt_timecards_f tim,
3895 per_all_assignments_f
3896 WHERE payroll_id = :payroll_id
3897 AND time_period_id = :period_id';
3898
3899
3900 CURSOR get_batches(p_batch_ref IN VARCHAR2)
3901 IS SELECT batch_id
3902 FROM pay_batch_headers
3903 WHERE batch_reference = p_batch_ref;
3904
3905
3906 l_asg_criteria VARCHAR2(4000);
3907 l_location_filter VARCHAR2(100);
3908 l_org_filter VARCHAR2(100);
3909 l_payroll_filter VARCHAR2(100);
3910 l_batch_filter VARCHAR2(400) := ' ';
3911 l_batches_list VARCHAR2(400);
3912
3913
3914 l_tc_tab NUMTAB;
3915 l_batches NUMTAB;
3916 l_tc_batch_tab NUMTAB;
3917
3918
3919 BEGIN
3920
3921 set_session_date;
3922 put_log('p_bg_id '||p_bg_id );
3923 put_log('p_session_date '||p_session_date );
3924 put_log('p_validate_mode '||p_validate_mode );
3925 put_log('p_payroll_id '||p_payroll_id );
3926 put_log('p_time_period_id '||p_time_period_id );
3927 put_log('p_gre_id '||p_gre_id );
3928 put_log('p_organization_id '||p_organization_id );
3929 put_log('p_location_id '||p_location_id );
3930 put_log('p_person_id '||p_person_id );
3931 put_log('p_batch_ref '||p_batch_ref );
3932 put_log('p_batch_id '||p_batch_id );
3933 put_log('p_batch_status '||p_batch_status );
3934 put_log('p_destination '||p_destination );
3935 put_log('p_is_timestore '||p_is_timestore );
3936 put_log('p_delete '||p_delete );
3937
3938
3939 g_validate_mode := p_validate_mode;
3940 g_rlbk_status := p_batch_status;
3941 g_program := 'HXTROLLOTLR';
3942 delete_old_log;
3943
3944 put_log('===========',g_cache);
3945 put_log('Parameters ',g_cache);
3946 put_log('===========',g_cache);
3947 put_log;
3948 put_log('Business Group Id : '||p_bg_id,g_cache);
3949 put_log('Session Date '||p_session_date);
3950 put_log('Validate Mode : '||p_validate_mode,g_cache);
3951 put_log('Payroll : '||p_payroll_id,g_cache);
3952 put_log('GRE Id : '||p_gre_id,g_cache);
3953 put_log('Organization : '||p_organization_id,g_cache);
3954 put_log('Location : '||p_location_id,g_cache);
3955 put_log('Person : '||p_person_id,g_cache);
3956 put_log('Batch Reference : '||p_batch_ref,g_cache);
3957 put_log('Batch Name : '||p_batch_id,g_cache);
3958 put_log('Rollback to : '||p_destination,g_cache);
3959
3960
3961 -- Bug 13777315
3962 IF p_person_id IS NOT NULL
3963 THEN
3964 l_query := l_query||FND_GLOBAL.newline||' AND tim.for_person_id = '||p_person_id;
3965 END IF;
3966
3967 IF p_location_id IS NOT NULL
3968 THEN
3969 l_location_filter := FND_Global.newline||' AND paf.location_id = '||p_location_id;
3970 END iF;
3971
3972 IF p_organization_id IS NOT NULL
3973 THEN
3974 l_org_filter := FND_Global.newline||' AND paf.organization_id = '||p_organization_id;
3975 END iF;
3976
3977 IF p_payroll_id IS NOT NULL
3978 THEN
3979 l_payroll_filter := FND_Global.newline||' AND paf.payroll_id = '||p_payroll_id;
3980 END iF;
3981
3982 IF p_batch_id IS NOT NULL
3983 THEN
3984 l_batch_filter := FND_Global.newline||' AND tim.batch_id = '||p_batch_id;
3985 END IF;
3986
3987 IF p_batch_ref IS NOT NULL
3988 THEN
3989
3990 OPEN get_batches(p_batch_ref);
3991
3992 FETCH get_batches BULK COLLECT INTO l_batches;
3993
3994 CLOSE get_batches;
3995
3996 l_batches_list := '('||string_list(l_batches)||')';
3997 l_batch_filter := l_batch_filter||FND_Global.newline||' AND tim.batch_id IN '||l_batches_list;
3998
3999 END IF;
4000
4001
4002
4003 IF p_gre_id IS NULL
4004 THEN
4005
4006 l_asg_criteria := l_batch_filter||FND_Global.newline||' AND for_person_id IN ( SELECT person_id
4007 FROM per_all_assignments_f paf
4008 WHERE paf.effective_start_date <= ptp.end_date
4009 AND paf.effective_end_date >= ptp.start_date
4010 AND business_group_id = '||p_bg_id
4011 ||l_location_filter
4012 ||l_org_filter
4013 ||l_payroll_filter
4014 ||')';
4015
4016 ELSIF p_gre_id IS NOT NULL
4017 THEN
4018
4019 l_asg_criteria := l_batch_filter||FND_Global.newline||' AND for_person_id IN ( SELECT person_id
4020 FROM per_all_assignments_f paf,
4021 hr_soft_coding_keyflex hsk
4022 WHERE paf.effective_start_date <= ptp.end_date
4023 AND paf.effective_end_date >= ptp.start_date
4024 AND business_group_id = '||p_bg_id
4025 ||l_location_filter
4026 ||l_org_filter
4027 ||l_payroll_filter
4028 ||FND_Global.newline||' AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
4029 AND hsk.segment1 = '''||p_gre_id||''''
4030 ||')';
4031
4032 END IF;
4033
4034 l_query := l_query||l_asg_criteria;
4035 l_query := l_query||' ORDER BY id ';
4036
4037 put_log(l_query);
4038
4039 EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_tc_tab,
4040 l_tc_batch_tab USING p_payroll_id,
4041 p_time_period_id;
4042
4043 display_numtab('Timecards',l_tc_tab);
4044
4045 IF l_tc_tab.COUNT > 0
4046 THEN
4047 FOR i IN l_tc_tab.FIRST..l_tc_tab.LAST
4048 LOOP
4049
4050 display_tc_details(l_tc_tab(i));
4051
4052 --display_all_details(l_tc_tab(i));
4053
4054 IF p_destination = 'OTM'
4055 THEN
4056
4057 rollback_all_details_to_otm(l_tc_tab(i),
4058 l_tc_batch_tab(i));
4059 ELSIF p_destination = 'TS'
4060 THEN
4061
4062 rollback_all_details_to_otl(l_tc_tab(i),
4063 l_tc_batch_tab(i),
4064 p_delete);
4065 END IF;
4066
4067 END LOOP;
4068
4069 END IF;
4070
4071 IF g_errored_tc.COUNT > 0
4072 THEN
4073 retcode := 1;
4074 END IF;
4075
4076 display_cached_log;
4077 IF p_validate_mode = 'VALIDATE'
4078 THEN
4079 put_log('Validate Mode: Rolling Back');
4080 ROLLBACK;
4081 END IF;
4082
4083
4084
4085 END rollback_xfer_time_otlr;
4086
4087
4088
4089 End hxt_retrieval_rollback;
4090
4091