[Home] [Help]
PACKAGE BODY: APPS.HXC_RDB_POST_RETRIEVAL
Source
1 PACKAGE BODY HXC_RDB_POST_RETRIEVAL AS
2 /* $Header: hxcrdbpostret.pkb 120.22.12020000.2 2013/02/27 10:11:40 jnerella ship $ */
3
4 PROCEDURE go(p_application IN VARCHAR2,
5 p_start_date IN VARCHAR2 DEFAULT NULL,
6 p_end_date IN VARCHAR2 DEFAULT NULL,
7 p_payroll_id IN NUMBER DEFAULT NULL,
8 p_org_id IN NUMBER DEFAULT NULL,
9 p_person_id IN NUMBER DEFAULT NULL,
10 p_batch_ref IN VARCHAR2 DEFAULT NULL,
11 p_msg OUT NOCOPY VARCHAR2,
12 p_level OUT NOCOPY VARCHAR2
13 )
14 IS
15
16
17 l_level VARCHAR2(50);
18
19 -- Bug 9662707
20 -- Replaced hxc_timecard_summary with hxc_time_building_blocks
21 -- picking up OVN instead of status. Status would be updated later.
22
23 -- Bug 9656063
24 -- Added Asg Org search criteria
25 l_pay_sql VARCHAR2(32000) :=
26 ' SELECT DISTINCT SUM.time_building_block_id,
27 SUM.object_version_number,
28 SUM.resource_id,
29 batch_id,
30 old_batch_id,
31 retro_batch_id,
32 SUM.start_time,
33 TRUNC(SUM.stop_time)
34 FROM hxc_ret_pay_latest_details ret,
35 hxc_time_building_blocks SUM
36 WHERE ret.start_time BETWEEN :p_start_date
37 AND :p_end_date
38 AND ret.timecard_id = SUM.time_building_block_id
39 AND business_group_id = FND_PROFILE.VALUE(''PER_BUSINESS_GROUP_ID'')
40 PERSONCRITERIA
41 PAYROLLCRITERIA
42 BATCHCRITERIA
43 ORGCRITERIA
44 ';
45
46 -- Bug 9626621
47 -- Added Org id condition.
48 l_pa_sql VARCHAR2(32000) :=
49 ' SELECT DISTINCT SUM.time_building_block_id,
50 SUM.object_version_number,
51 SUM.resource_id,
52 exp_group,
53 old_exp_group,
54 retro_exp_group,
55 SUM.start_time,
56 TRUNC(SUM.stop_time)
57 FROM hxc_ret_pa_latest_details ret,
58 hxc_time_building_blocks SUM
59 WHERE ret.start_time BETWEEN :p_start_date
60 AND :p_end_date
61 AND ret.timecard_id = SUM.time_building_block_id
62 AND ret.org_id = NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE(''ORG_ID''))
63 PERSONCRITERIA
64 BATCHCRITERIA
65 ORGCRITERIA
66 ';
67
68 l_batch_criteria VARCHAR2(2000) :=
69 'AND ( EXISTS (SELECT 1
70 FROM pay_batch_headers pbh
71 WHERE pbh.batch_id = ret.batch_id
72 AND pbh.batch_reference = ''BATCHREF'' )
73 OR EXISTS (SELECT 1
74 FROM pay_batch_headers pbh
75 WHERE pbh.batch_id = ret.retro_batch_id
76 AND pbh.batch_reference = ''BATCHREF'' )
77 )
78 ';
79
80 -- Bug 12605349: Used secure views where ever needed to add HR security
81 l_payroll_criteria VARCHAR2(2000) :=
82 'AND EXISTS ( SELECT 1
83 FROM per_assignments_f paf /*Bug 12605349*/
84 WHERE paf.person_id = ret.resource_id
85 AND ret.start_time BETWEEN paf.effective_start_date
86 AND paf.effective_end_date
87 AND paf.payroll_id = PAYROLL )'
88 ;
89
90 -- Bug 9656063
91
92 l_org_criteria VARCHAR2(2000) :=
93 'AND EXISTS ( SELECT 1
94 FROM per_assignments_f paf /*Bug 9656063*/
95 WHERE paf.person_id = ret.resource_id
96 AND ret.start_time BETWEEN paf.effective_start_date
97 AND paf.effective_end_date
98 AND paf.organization_id = ORGANIZATION )'
99 ;
100
101
102 -- Bug 12605349
103 l_hr_sec VARCHAR2(3000) :=
104 'AND EXISTS (SELECT 1
105 FROM per_people_f ppf
106 WHERE ppf.person_id = ret.resource_id
107 AND ret.start_time BETWEEN ppf.effective_start_date
108 AND ppf.effective_end_date)
109 ';
110 -- Bug 12605349
111 tctab NUMBERTAB;
112 statustab VARCHARTAB;
113 restab NUMBERTAB;
114 batchtab VARCHARTAB;
115 oldtab VARCHARTAB; -- Bug 9662707
116 rettab VARCHARTAB;
117 starttab DATETAB;
118 stoptab DATETAB;
119
120 l_pay_cursor SYS_REFCURSOR;
121
122
123 -- Bug 9662707
124 -- Added this code to create pseudo records for old batch ids
125 -- This just picks up the records having Old_batch_id and create
126 -- new records for those timecards, with old_batch_id put into
127 -- the batch_id column -- just picking up the old batch ids to
128 -- look like normal batces.
129
130 PROCEDURE find_and_update_old
131 IS
132
133 -- Bug 9701527
134 -- Added the NOT EXISTS to avoid same batch coming
135 -- twice because batch_id and old_batch_id are same.
136 CURSOR find_old_tcs
137 IS SELECT timecard_id,
138 approval_status,
139 resource_id,
140 batch_id,
141 old_batch_id,
142 retro_batch_id,
143 start_time,
144 stop_time,
145 ROWIDTOCHAR(rdb.rowid)
146 FROM hxc_rdb_post_timecards rdb
147 WHERE ret_user_id = FND_GLOBAL.user_id
148 AND old_batch_id IS NOT NULL
149 AND retro_batch_id IS NOT NULL
150 AND NOT EXISTS ( SELECT 1
151 FROM hxc_rdb_post_timecards rdb2
152 WHERE rdb2.ret_user_id = FND_GLOBAL.user_id
153 AND rdb2.batch_id = rdb.old_batch_id );
154
155 -- Bug 9705704
156 -- Cursor to pick up unique combo to delete duplicates.
157 CURSOR get_duplicates
158 IS SELECT MAX(ROWIDTOCHAR(rdb.rowid)),
159 timecard_id,
160 NVL(batch_id,'0'),
161 NVL(retro_batch_id,'0')
162 FROM hxc_rdb_post_timecards rdb
163 WHERE ret_user_id = FND_GLOBAL.user_id
164 GROUP BY timecard_id,batch_id,retro_batch_id;
165
166 tctab NUMBERTAB;
167 statustab VARCHARTAB;
168 restab NUMBERTAB;
169 batchtab VARCHARTAB;
170 oldtab VARCHARTAB; -- Bug 9662707
171 rettab VARCHARTAB;
172 starttab DATETAB;
173 stoptab DATETAB;
174 rowtab VARCHARTAB;
175
176 BEGIN
177
178 OPEN find_old_tcs;
179 LOOP
180 FETCH find_old_tcs BULK COLLECT INTO tctab,
181 statustab,
182 restab,
183 batchtab,
184 oldtab,
185 rettab,
186 starttab,
187 stoptab,
188 rowtab LIMIT 500;
189
190 EXIT WHEN tctab.COUNT = 0;
191
192 FORALL i IN tctab.FIRST..tctab.LAST
193 UPDATE hxc_rdb_post_timecards
194 SET old_batch_id = NULL
195 WHERE ROWID = CHARTOROWID(rowtab(i));
196
197 FORALL i IN tctab.FIRST..tctab.LAST
198 INSERT INTO hxc_rdb_post_timecards
199 (timecard_id,
200 approval_status,
201 resource_id,
202 batch_id,
203 start_time,
204 stop_time,
205 ret_user_id)
206 VALUES
207 (tctab(i),
208 statustab(i),
209 restab(i),
210 oldtab(i),
211 starttab(i),
212 stoptab(i),
213 FND_GLOBAL.user_id);
214
215 COMMIT;
216
217 END LOOP;
218 CLOSE find_old_tcs;
219
220 -- Bug 9705704
221 -- Update all the records with a NULL old_batch_id,
222 -- because now we have picked up all the old batches
223 -- as batch_id itself.
224
225 UPDATE hxc_rdb_post_timecards
226 SET old_batch_id = NULL
227 WHERE ret_user_id = FND_GLOBAL.user_id;
228
229 -- Pick up the unique combos.
230 OPEN get_duplicates;
231 LOOP
232 FETCH get_duplicates BULK COLLECT INTO
233 rowtab,
234 tctab,
235 batchtab,
236 rettab LIMIT 500;
237 EXIT WHEN rowtab.COUNT = 0;
238
239 -- Delete those duplicates which do not have the max
240 -- rowid.
241 FORALL i IN rowtab.FIRST..rowtab.LAST
242 DELETE FROM hxc_rdb_post_timecards
243 WHERE ret_user_id = FND_GLOBAL.user_id
244 AND timecard_id = tctab(i)
245 AND NVL(batch_id,'0') = batchtab(i)
246 AND NVL(retro_batch_id,'0') = rettab(i)
247 AND ROWID <> CHARTOROWID(rowtab(i));
248
249 COMMIT;
250 END LOOP;
251 CLOSE get_duplicates;
252
253
254
255
256
257
258 END find_and_update_old;
259
260
261
262 -- Bug 9662707
263 -- Added this procedure to trim out the records having
264 -- OVNs which are not latest. Just picks out the ones having
265 -- rank <> 1 and then deletes these.
266
267 PROCEDURE delete_duplicate_tcs
268 IS
269
270 CURSOR get_rank
271 IS SELECT ROWIDTOCHAR(ROWID),
272 RANK() OVER ( PARTITION BY timecard_id
273 ORDER BY TO_NUMBER(approval_status) DESC ) rank
274 FROM hxc_rdb_post_timecards rdb
275 WHERE ret_user_id = FND_GLOBAL.user_id;
276
277 tctab VARCHARTAB;
278 ovntab NUMBERTAB;
279 ranktab NUMBERTAB;
280
281 BEGIN
282
283 OPEN get_rank;
284 LOOP
285 FETCH get_rank BULK COLLECT INTO tctab,
286 ranktab LIMIT 500;
287 EXIT WHEN tctab.COUNT = 0;
288
289 FORALL i IN tctab.FIRST..tctab.LAST
290 DELETE FROM hxc_rdb_post_timecards
291 WHERE ret_user_id = FND_GLOBAL.user_id
292 AND ROWID = CHARTOROWID(tctab(i))
293 AND ranktab(i) <> 1 ;
294
295
296 COMMIT;
297 END LOOP;
298 CLOSE get_rank;
299
300 END delete_duplicate_tcs;
301
302
303
304 PROCEDURE update_supervisor
305 IS
306
307 CURSOR get_supervisor
308 IS SELECT asg.supervisor_id,
309 asg.payroll_id,
310 asg.organization_id,
311 asg.job_id,
312 ROWIDTOCHAR(tc.ROWID)
313 FROM hxc_rdb_post_timecards tc,
314 per_assignments_f asg -- Bug 12605349
315 WHERE tc.ret_user_id = FND_GLOBAL.user_id
316 AND tc.resource_id = asg.person_id
317 AND tc.start_time BETWEEN asg.effective_start_date
318 AND asg.effective_end_date;
319
320 suptab NUMBERTAB;
321 paytab NUMBERTAB;
322 orgtab NUMBERTAB;
323 jobtab NUMBERTAB;
324 rowtab VARCHARTAB;
325
326 BEGIN
327 OPEN get_supervisor;
328 LOOP
329 FETCH get_supervisor BULK COLLECT INTO suptab,
330 paytab,
331 orgtab,
332 jobtab,
333 rowtab LIMIT 500;
334 EXIT WHEN suptab.COUNT = 0;
335
336 FORALL i IN suptab.FIRST..suptab.LAST
337 UPDATE hxc_rdb_post_timecards
338 SET supervisor_id = suptab(i),
339 payroll_id = paytab(i),
340 org_job_id = DECODE(p_application,'PAY', orgtab(i),
341 'PA', jobtab(i))
342 WHERE ROWID = CHARTOROWID(rowtab(i));
343 COMMIT;
344 END LOOP;
345
346 END update_supervisor;
347
348
349 PROCEDURE update_emp_details
350 IS
351
352 CURSOR get_emp_name
353 IS SELECT ppf.full_name,
354 DECODE(ppf.current_npw_flag,'Y',
355 ppf.npw_number,
356 ppf.employee_number),
357 ROWIDTOCHAR(rdb.ROWID)
358 FROM hxc_rdb_post_timecards rdb,
359 per_people_f ppf -- Bug 12605349
360 WHERE SYSDATE BETWEEN ppf.effective_start_date
361 AND ppf.effective_end_date
362 AND rdb.resource_id = ppf.person_id
363 AND rdb.ret_user_id = FND_GLOBAL.user_id;
364
365 nametab VARCHARTAB;
366 notab VARCHARTAB;
367 rowtab VARCHARTAB;
368
369 BEGIN
370 OPEN get_emp_name;
371 LOOP
372 FETCH get_emp_name BULK COLLECT INTO nametab,
373 notab,
374 rowtab LIMIT 500;
375 EXIT WHEN nametab.COUNT = 0;
376
377 FORALL i IN nametab.FIRST..nametab.LAST
378 UPDATE hxc_rdb_post_timecards
379 SET emp_name = nametab(i),
380 emp_no = notab(i)
381 WHERE ROWID = CHARTOROWID(rowtab(i));
382
383 COMMIT;
384
385 END LOOP;
386 CLOSE get_emp_name;
387
388
389 END update_emp_details;
390
391
392
393 PROCEDURE pick_up_details(p_application IN VARCHAR2)
394 IS
395
396 l_details VARCHAR2(32000) :=
397 'SELECT ret.resource_id,
398 ret.time_building_block_id,
399 ret.object_version_number ovn,
400 ret.timecard_id,
401 ret.start_time date_worked,
402 ret.attribute1,
403 ret.attribute2,
404 ret.attribute3,
405 ret.measure,
406 ret.old_attribute1,
407 ret.old_attribute2,
408 ret.old_attribute3,
409 ret.old_measure,
410 ret.attribute1,
411 ret.pbl_id,
412 ret.retro_pbl_id,
413 ret.old_pbl_id,
414 ret.batch_id,
415 ret.retro_batch_id,
416 ret.request_id,
417 ret.old_request_id,
418 ret.old_batch_id
419 FROM hxc_ret_pay_latest_details ret,
420 hxc_rdb_post_timecards tc
421 WHERE ret.timecard_id = tc.timecard_id
422 AND NVL(tc.batch_id,''0'') = NVL(ret.batch_id,''0'')
423 AND NVL(tc.retro_batch_id,''0'') = NVL(ret.retro_batch_id,''0'')
424 AND tc.ret_user_id = USERID '
425 ;
426
427 l_pa_details VARCHAR2(32000) :=
428 'SELECT ret.resource_id,
429 ret.time_building_block_id,
430 ret.object_version_number ovn,
431 ret.timecard_id,
432 ret.start_time date_worked,
433 ret.attribute1,
434 ret.attribute2,
435 ret.attribute3,
436 ret.measure,
437 ret.old_attribute1,
438 ret.old_attribute2,
439 ret.old_attribute3,
440 ret.old_measure,
441 ret.attribute1,
442 ret.pei_id,
443 ret.retro_pei_id,
444 ret.old_pei_id,
445 ret.exp_group,
446 ret.retro_exp_group,
447 ret.request_id,
448 ret.old_request_id,
449 ret.old_exp_group
450 FROM hxc_ret_pa_latest_details ret,
451 hxc_rdb_post_timecards tc
452 WHERE ret.timecard_id = tc.timecard_id
453 AND tc.ret_user_id = USERID
454 AND NVL(tc.batch_id,''0'') = NVL(ret.exp_group,''0'')
455 AND NVL(tc.retro_batch_id,''0'') = NVL(ret.retro_exp_group,''0'')
456 ';
457
458
459 l_ref_cursor SYS_REFCURSOR;
460 restab NUMBERTAB;
461 bbtab NUMBERTAB;
462 ovntab NUMBERTAB;
463 stattab VARCHARTAB;
464 tctab NUMBERTAB;
465 dwtab DATETAB;
466 att1tab VARCHARTAB;
467 att2tab VARCHARTAB;
468 att3tab VARCHARTAB;
469 measuretab NUMBERTAB;
470 oatt1tab VARCHARTAB;
471 oatt2tab VARCHARTAB;
472 oatt3tab VARCHARTAB;
473 omeasuretab NUMBERTAB;
474 recline NUMBERTAB;
475 orecline NUMBERTAB;
476 retroline NUMBERTAB;
477 batchid VARCHARTAB;
478 rbatchid VARCHARTAB;
479 hrspmtab NUMBERTAB;
480 reqid NUMBERTAB;
481 rreqid NUMBERTAB;
482 obatchid VARCHARTAB;
483
484 BEGIN
485 IF p_application = 'PA'
486 THEN
487 l_details := l_pa_details;
488 ELSIF p_application = 'PAY'
489 THEN
490 l_details := REPLACE(l_details,'LATEST_DETAILS','HXC_RET_PAY_LATEST_DETAILS');
491 END IF;
492 l_details := REPLACE(l_details,'USERID',FND_GLOBAL.user_id);
493
494 OPEN l_ref_cursor FOR l_details;
495 LOOP
496 FETCH l_ref_cursor BULK COLLECT INTO restab,
497 bbtab,
498 ovntab,
499 tctab,
500 dwtab,
501 att1tab,
502 att2tab,
503 att3tab,
504 measuretab,
505 oatt1tab,
506 oatt2tab,
507 oatt3tab,
508 omeasuretab,
509 hrspmtab ,
510 recline,
511 retroline,
512 orecline,
513 batchid,
514 rbatchid,
515 reqid,
516 rreqid,
517 obatchid
518 LIMIT 500;
519 EXIT WHEN restab.COUNT = 0;
520
521 FORALL i IN restab.FIRST..restab.LAST
522 INSERT INTO hxc_rdb_post_details
523 (resource_id,
524 time_building_block_id,
525 ovn,
526 timecard_id,
527 date_worked,
528 attribute1,
529 attribute2,
530 attribute3,
531 measure,
532 old_attribute1,
533 old_attribute2,
534 old_attribute3,
535 old_measure,
536 hrs_pm,
537 rec_line_id,
538 rec_retro_line_id,
539 adj_rec_line_id,
540 batch_id,
541 retro_batch_id,
542 request_id,
543 old_request_id,
544 old_batch_id,
545 ret_user_id )
546 VALUES
547 ( restab(i),
548 bbtab(i),
549 ovntab(i),
550 tctab(i),
551 dwtab(i),
552 att1tab(i),
553 att2tab(i),
554 att3tab(i),
555 measuretab(i),
556 oatt1tab(i),
557 oatt2tab(i),
558 oatt3tab(i),
559 omeasuretab(i),
560 DECODE(p_application,'PA',hrspmtab(i),NULL),
561 recline(i),
562 retroline(i),
563 orecline(i),
564 batchid(i),
565 rbatchid(i),
566 reqid(i),
567 rreqid(i),
568 obatchid(i),
569 FND_GLOBAL.user_id);
570
571 COMMIT;
572 END LOOP;
573
574 END pick_up_details;
575
576
577
578
579
580 -- Bug 9662707
581 -- Added this procedure mimicking the above one, just to pick up the
582 -- old details for each adjusted entry.
583
584
585 PROCEDURE pick_up_old_details(p_application IN VARCHAR2)
586 IS
587
588
589 -- The below cursor is pretty much like the one used for the above proc.
590 -- Instead of the current details, it picks up the old batch line details
591 -- (attributes, measure etc) and picks up NULL in place of old batch details
592 -- and retro batch details. This would create some details just like the
593 -- old entries are created newly as new lines.
594 --
595 -- Eg. Reg 8 hrs
596 -- Retrieved.
597 -- Changed to Reg 6 hours.
598 -- The above proc would have picked it up like this.
599
600 -- current details 6 hrs, Reg.
601 -- retro details -8 hrs, Reg.
602 --
603 -- The original + 8 hrs is lost here. This procedure would just pick up
604 -- and put it into the table.
605 --
606
607 l_details VARCHAR2(32000) :=
608 'SELECT ret.resource_id,
609 ret.time_building_block_id,
610 ret.object_version_number ovn,
611 ret.timecard_id,
612 ret.start_time date_worked,
613 ret.old_attribute1,
614 ret.old_attribute2,
615 ret.old_attribute3,
616 ret.old_measure,
617 NULL,
618 NULL,
619 NULL,
620 NULL,
621 ret.old_attribute1,
622 ret.old_pbl_id,
623 NULL,
624 NULL,
625 ret.old_batch_id,
626 NULL,
627 ret.old_request_id,
628 NULL,
629 NULL
630 FROM hxc_ret_pay_latest_details ret,
631 hxc_rdb_post_timecards tc
632 WHERE ret.timecard_id = tc.timecard_id
633 AND tc.batch_id = ret.old_batch_id
634 AND ret.old_pbl_id <> NVL(ret.pbl_id,0)
635 AND tc.ret_user_id = USERID '
636 ;
637
638 l_pa_details VARCHAR2(32000) :=
639 'SELECT ret.resource_id,
640 ret.time_building_block_id,
641 ret.object_version_number ovn,
642 ret.timecard_id,
643 ret.start_time date_worked,
644 ret.old_attribute1,
645 ret.old_attribute2,
646 ret.old_attribute3,
647 ret.old_measure,
648 NULL,
649 NULL,
650 NULL,
651 NULL,
652 ret.old_attribute1,
653 ret.old_pei_id,
654 NULL,
655 NULL,
656 ret.old_exp_group,
657 NULL,
658 ret.old_request_id,
659 NULL,
660 NULL
661 FROM hxc_ret_pa_latest_details ret,
662 hxc_rdb_post_timecards tc
663 WHERE ret.timecard_id = tc.timecard_id
664 AND tc.ret_user_id = USERID
665 AND tc.batch_id = ret.old_exp_group
666 AND ret.old_pei_id <> NVL(ret.pei_id,0)
667 ';
668
669
670 l_ref_cursor SYS_REFCURSOR;
671 restab NUMBERTAB;
672 bbtab NUMBERTAB;
673 ovntab NUMBERTAB;
674 stattab VARCHARTAB;
675 tctab NUMBERTAB;
676 dwtab DATETAB;
677 att1tab VARCHARTAB;
678 att2tab VARCHARTAB;
679 att3tab VARCHARTAB;
680 measuretab NUMBERTAB;
681 oatt1tab VARCHARTAB;
682 oatt2tab VARCHARTAB;
683 oatt3tab VARCHARTAB;
684 omeasuretab NUMBERTAB;
685 recline NUMBERTAB;
686 orecline NUMBERTAB;
687 retroline NUMBERTAB;
688 batchid VARCHARTAB;
689 rbatchid VARCHARTAB;
690 hrspmtab NUMBERTAB;
691 reqid NUMBERTAB;
692 rreqid NUMBERTAB;
693 obatchid VARCHARTAB;
694
695 BEGIN
696 IF p_application = 'PA'
697 THEN
698 l_details := l_pa_details;
699 ELSIF p_application = 'PAY'
700 THEN
701 l_details := REPLACE(l_details,'LATEST_DETAILS','HXC_RET_PAY_LATEST_DETAILS');
702 END IF;
703 l_details := REPLACE(l_details,'USERID',FND_GLOBAL.user_id);
704
705 OPEN l_ref_cursor FOR l_details;
706 LOOP
707 FETCH l_ref_cursor BULK COLLECT INTO restab,
708 bbtab,
709 ovntab,
710 tctab,
711 dwtab,
712 att1tab,
713 att2tab,
714 att3tab,
715 measuretab,
716 oatt1tab,
717 oatt2tab,
718 oatt3tab,
719 omeasuretab,
720 hrspmtab ,
721 recline,
722 retroline,
723 orecline,
724 batchid,
725 rbatchid,
726 reqid,
727 rreqid,
728 obatchid
729 LIMIT 500;
730 EXIT WHEN restab.COUNT = 0;
731
732 FORALL i IN restab.FIRST..restab.LAST
733 INSERT INTO hxc_rdb_post_details
734 (resource_id,
735 time_building_block_id,
736 ovn,
737 timecard_id,
738 date_worked,
739 attribute1,
740 attribute2,
741 attribute3,
742 measure,
743 old_attribute1,
744 old_attribute2,
745 old_attribute3,
746 old_measure,
747 hrs_pm,
748 rec_line_id,
749 rec_retro_line_id,
750 adj_rec_line_id,
751 batch_id,
752 retro_batch_id,
753 request_id,
754 old_request_id,
755 old_batch_id,
756 ret_user_id )
757 VALUES
758 ( restab(i),
759 bbtab(i),
760 ovntab(i),
761 tctab(i),
762 dwtab(i),
763 att1tab(i),
764 att2tab(i),
765 att3tab(i),
766 measuretab(i),
767 oatt1tab(i),
768 oatt2tab(i),
769 oatt3tab(i),
770 omeasuretab(i),
771 DECODE(p_application,'PA',hrspmtab(i),NULL),
772 recline(i),
773 retroline(i),
774 orecline(i),
775 batchid(i),
776 rbatchid(i),
777 reqid(i),
778 rreqid(i),
779 obatchid(i),
780 FND_GLOBAL.user_id);
781
782 COMMIT;
783 END LOOP;
784
785 END pick_up_old_details;
786
787
788
789 -- Bug 9662707
790 -- Added this procedure to update the statuses of the timecard
791 -- records.
792
793 PROCEDURE update_statuses
794 IS
795
796 -- To pick up the guys which are active now.
797 CURSOR get_summary
798 IS SELECT sum.approval_status,
799 rdb.timecard_id
800 FROM hxc_rdb_post_timecards rdb,
801 hxc_timecard_summary sum
802 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
803 AND rdb.timecard_id = sum.timecard_id;
804
805
806 -- To pick up those which are deleted now or overwritten with a
807 -- template, or deleted and recreated.
808
809 CURSOR get_blocks
810 IS SELECT ROWIDTOCHAR(rowid)
811 FROM hxc_rdb_post_timecards
812 WHERE ret_user_id = FND_GLOBAL.user_id
813 AND approval_status NOT IN ( SELECT lookup_code
814 FROM fnd_lookup_values
815 WHERE lookup_type = 'HXC_APPROVAL_STATUS'
816 AND language = USERENV('LANG') );
817
818 -- To get the latest versions of the those which are mow deleted
819 -- and later versions exist.
820
821 CURSOR get_latest
822 IS SELECT rdb.timecard_id,
823 sum.timecard_id,
824 sum.approval_status,
825 ROWIDTOCHAR(rdb.rowid)
826 FROM hxc_rdb_post_timecards rdb,
827 hxc_timecard_summary sum
828 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
829 AND rdb.approval_status = 'RDBDELETED'
830 AND rdb.resource_id = sum.resource_id
831 AND rdb.start_time = sum.start_time
832 AND rdb.stop_time = TRUNC(sum.stop_time)
833 AND rdb.timecard_id <> sum.timecard_id ;
834
835
836 stattab VARCHARTAB;
837 tctab NUMBERTAB;
838 rowtab VARCHARTAB;
839
840
841 rdbtab NUMBERTAB;
842 sumtab NUMBERTAB;
843 statustab VARCHARTAB;
844 rowidtab VARCHARTAB;
845
846
847 BEGIN
848
849 -- Update from hxc_timecard_summary for those in there.
850
851 OPEN get_summary;
852 LOOP
853 FETCH get_summary BULK COLLECT INTO stattab,
854 tctab LIMIT 500;
855 EXIT WHEN stattab.COUNT = 0;
856
857 FORALL i IN tctab.FIRST..tctab.LAST
858 UPDATE hxc_rdb_post_timecards
859 SET approval_status = stattab(i)
860 WHERE ret_user_id = FND_GLOBAL.user_id
861 AND timecard_id = tctab(i);
862
863 COMMIT;
864 END LOOP;
865 CLOSE get_summary;
866
867 -- Update everything else to DELETED.
868
869 OPEN get_blocks;
870 FETCH get_blocks BULK COLLECT INTO rowtab;
871 CLOSE get_blocks;
872
873 FORALL i IN rowtab.FIRST..rowtab.LAST
874 UPDATE hxc_rdb_post_timecards
875 SET approval_status = 'RDBDELETED'
876 WHERE rowid = CHARTOROWID(rowtab(i));
877
878 COMMIT;
879
880 -- Pick up later versions.
881 OPEN get_latest;
882 FETCH get_latest BULK COLLECT INTO rdbtab,
883 sumtab,
884 statustab,
885 rowidtab;
886
887 CLOSE get_latest;
888
889
890
891 -- Update the timecard ids and statuses for the timecards.
892 FORALL i IN rowidtab.FIRST..rowidtab.LAST
893 UPDATE hxc_rdb_post_timecards
894 SET approval_status = statustab(i),
895 timecard_id = sumtab(i)
896 WHERE ROWID = CHARTOROWID(rowidtab(i));
897
898 -- Update the timecard_id in the details table because they
899 -- are referenced below.
900
901 FORALL i IN rowidtab.FIRST..rowidtab.LAST
902 UPDATE hxc_rdb_post_details
903 SET timecard_id = sumtab(i)
904 WHERE timecard_id = rdbtab(i);
905
906
907 COMMIT;
908
909
910
911 END update_statuses;
912
913
914 -- Bug 9662707
915 -- Added this proc to update the retro batches with suffix (Retro)
916 -- Works only for PA.
917
918 PROCEDURE update_retro_batches
919 IS
920
921 CURSOR pick_timecards
922 IS SELECT ROWIDTOCHAR(rdb.rowid)
923 FROM hxc_rdb_post_timecards rdb
924 WHERE ret_user_id = FND_GLOBAL.user_id
925 AND retro_batch_id IS NOT NULL;
926
927
928 CURSOR pick_details
929 IS SELECT ROWIDTOCHAR(rdb.rowid)
930 FROM hxc_rdb_post_details rdb
931 WHERE ret_user_id = FND_GLOBAL.user_id
932 AND retro_batch_id IS NOT NULL;
933
934 rowtab VARCHARTAB;
935
936
937 BEGIN
938
939 OPEN pick_timecards;
940 LOOP
941 FETCH pick_timecards BULK COLLECT INTO rowtab LIMIT 500;
942 EXIT WHEN rowtab.COUNT = 0;
943 FORALL i IN rowtab.FIRST..rowtab.LAST
944 UPDATE hxc_rdb_post_timecards
945 SET retro_batch_id = retro_batch_id||'(Retro)'
946 WHERE rowid = CHARTOROWID(rowtab(i));
947
948 COMMIT;
949 END LOOP;
950 CLOSE pick_timecards;
951
952 OPEN pick_details;
953 LOOP
954 FETCH pick_details BULK COLLECT INTO rowtab LIMIT 500;
955 EXIT WHEN rowtab.COUNT = 0;
956 FORALL i IN rowtab.FIRST..rowtab.LAST
957 UPDATE hxc_rdb_post_details
958 SET retro_batch_id = retro_batch_id||'(Retro)'
959 WHERE rowid = CHARTOROWID(rowtab(i));
960
961 COMMIT;
962 END LOOP;
963 CLOSE pick_details;
964
965 END update_retro_batches;
966
967
968
969 PROCEDURE update_partially_retrieved(p_application IN VARCHAR2)
970 IS
971
972 CURSOR get_partially_retrieved_pay
973 IS SELECT ROWIDTOCHAR(tc.ROWID)
974 FROM hxc_rdb_post_timecards tc,
975 hxc_pay_latest_details pay
976 WHERE tc.ret_user_id = FND_GLOBAL.user_id
977 AND tc.timecard_id = pay.timecard_id;
978
979 CURSOR get_partially_retrieved_pa
980 IS SELECT ROWIDTOCHAR(tc.ROWID)
981 FROM hxc_rdb_post_timecards tc,
982 hxc_pa_latest_details pay
983 WHERE tc.ret_user_id = FND_GLOBAL.user_id
984 AND tc.timecard_id = pay.timecard_id;
985
986
987 rowtab VARCHARTAB;
988
989 BEGIN
990 IF p_application = 'PAY'
991 THEN
992 OPEN get_partially_retrieved_pay;
993 LOOP
994 FETCH get_partially_retrieved_pay BULK COLLECT INTO rowtab LIMIT 500;
995 EXIT WHEN rowtab.COUNT = 0;
996
997 FORALL i IN rowtab.FIRST..rowtab.LAST
998 UPDATE hxc_rdb_post_timecards
999 SET partially_retrieved = 'Y'
1000 WHERE ROWID = CHARTOROWID(rowtab(i));
1001 COMMIT;
1002 END LOOP;
1003 CLOSE get_partially_retrieved_pay;
1004 END IF;
1005
1006 IF p_application = 'PA'
1007 THEN
1008 OPEN get_partially_retrieved_pa;
1009 LOOP
1010 FETCH get_partially_retrieved_pa BULK COLLECT INTO rowtab LIMIT 500;
1011 EXIT WHEN rowtab.COUNT = 0;
1012
1013 FORALL i IN rowtab.FIRST..rowtab.LAST
1014 UPDATE hxc_rdb_post_timecards
1015 SET partially_retrieved = 'Y'
1016 WHERE ROWID = CHARTOROWID(rowtab(i));
1017 COMMIT;
1018 END LOOP;
1019 CLOSE get_partially_retrieved_pa;
1020 END IF;
1021
1022
1023 END update_partially_retrieved;
1024
1025
1026
1027 PROCEDURE summarize_batches
1028 IS
1029
1030 BEGIN
1031 INSERT
1032 INTO hxc_rdb_post_batches
1033 (batch_id,
1034 timecards,
1035 retro_flag,
1036 ret_user_id)
1037 SELECT DISTINCT batch_id,
1038 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id) Timecards,
1039 'N',
1040 FND_GLOBAL.user_id
1041 FROM hxc_rdb_post_timecards
1042 WHERE ret_user_id = FND_GLOBAL.user_id
1043 AND batch_id IS NOT NULL
1044 UNION
1045 SELECT DISTINCT retro_batch_id,
1046 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id) Timecards,
1047 'Y',
1048 FND_GLOBAL.user_id
1049 FROM hxc_rdb_post_timecards
1050 WHERE ret_user_id = FND_GLOBAL.user_id
1051 AND retro_batch_id IS NOT NULL
1052 AND NVL(retro_batch_id,'0') <> NVL(batch_id,'0')
1053 AND NVL(retro_batch_id,'0') <> NVL(old_batch_id,'0') ;
1054 COMMIT;
1055
1056 END summarize_batches;
1057
1058
1059 PROCEDURE summarize_attributes
1060 IS
1061
1062 BEGIN
1063 INSERT
1064 INTO hxc_rdb_post_attributes
1065 (batch_id,
1066 attribute1,
1067 attribute2,
1068 attribute3,
1069 measure,
1070 negative_flag,
1071 ret_user_id)
1072 SELECT DISTINCT batch_id,
1073 attribute1,
1074 attribute2,
1075 attribute3,
1076 SUM(measure) OVER (PARTITION BY batch_id,
1077 attribute1,
1078 attribute2,
1079 attribute3),
1080 1,
1081 FND_GLOBAL.user_id
1082 FROM hxc_rdb_post_details
1083 WHERE rec_line_id IS NOT NULL
1084 AND ret_user_id = FND_GLOBAL.user_id
1085 UNION
1086 SELECT DISTINCT '0' batch_id,
1087 attribute1,
1088 attribute2,
1089 attribute3,
1090 SUM(measure) OVER (PARTITION BY attribute1,
1091 attribute2,
1092 attribute3),
1093 1,
1094 FND_GLOBAL.user_id
1095 FROM hxc_rdb_post_details
1096 WHERE rec_line_id IS NOT NULL
1097 AND ret_user_id = FND_GLOBAL.user_id;
1098
1099
1100 INSERT
1101 INTO hxc_rdb_post_attributes
1102 (batch_id,
1103 attribute1,
1104 attribute2,
1105 attribute3,
1106 measure,
1107 negative_flag,
1108 ret_user_id)
1109 SELECT DISTINCT retro_batch_id,
1110 old_attribute1,
1111 old_attribute2,
1112 old_attribute3,
1113 SUM(-1*old_measure) OVER (PARTITION BY retro_batch_id,
1114 old_attribute1,
1115 old_attribute2,
1116 old_attribute3),
1117 -1,
1118 FND_GLOBAL.user_id
1119 FROM hxc_rdb_post_details
1120 WHERE rec_retro_line_id IS NOT NULL
1121 AND ret_user_id = FND_GLOBAL.user_id
1122 UNION
1123 SELECT DISTINCT '0' retro_batch_id,
1124 old_attribute1,
1125 old_attribute2,
1126 old_attribute3,
1127 SUM(-1*old_measure) OVER (PARTITION BY old_attribute1,
1128 old_attribute2,
1129 old_attribute3),
1130 -1,
1131 FND_GLOBAL.user_id
1132 FROM hxc_rdb_post_details
1133 WHERE rec_retro_line_id IS NOT NULL
1134 AND ret_user_id = FND_GLOBAL.user_id;
1135
1136
1137 INSERT INTO HXC_RDB_POST_ATTRIBUTES
1138 ( attribute1,
1139 attribute2,
1140 attribute3,
1141 measure,
1142 batch_id,
1143 total,
1144 negative_flag,
1145 ret_user_id
1146 )
1147 SELECT attribute1,
1148 attribute2,
1149 attribute3,
1150 SUM(measure),
1151 batch_id,
1152 ' (Total) 'total,
1153 1,
1154 FND_GLOBAL.user_id
1155 FROM hxc_rdb_post_attributes
1156 WHERE ret_user_id = FND_GLOBAL.user_id
1157 GROUP BY batch_id,
1158 attribute1,
1159 attribute2,
1160 attribute3 ;
1161
1162
1163 COMMIT;
1164
1165 END summarize_attributes;
1166
1167 PROCEDURE summarize_hrs_pm(p_application IN VARCHAR2)
1168 IS
1169
1170 BEGIN
1171
1172 IF p_application = 'PAY'
1173 THEN
1174 INSERT INTO hxc_rdb_post_hrs_pm
1175 (batch_id,
1176 hrs_pm,
1177 timecards,
1178 ret_user_id)
1179 SELECT DISTINCT batch_id,
1180 supervisor_id,
1181 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1182 supervisor_id) ,
1183 FND_GLOBAL.user_id
1184 FROM hxc_rdb_post_timecards
1185 WHERE ret_user_id = FND_GLOBAL.user_id
1186 AND supervisor_id IS NOT NULL
1187 AND batch_id IS NOT NULL
1188 UNION
1189 SELECT DISTINCT retro_batch_id,
1190 supervisor_id,
1191 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1192 supervisor_id) ,
1193 FND_GLOBAL.user_id
1194 FROM hxc_rdb_post_timecards
1195 WHERE ret_user_id = FND_GLOBAL.user_id
1196 AND supervisor_id IS NOT NULL
1197 AND retro_batch_id IS NOT NULL
1198 UNION
1199 SELECT '0' batch_id,
1200 supervisor_id,
1201 COUNT(DISTINCT timecard_id) OVER (PARTITION BY supervisor_id) ,
1202 FND_GLOBAL.user_id
1203 FROM hxc_rdb_post_timecards
1204 WHERE ret_user_id = FND_GLOBAL.user_id
1205 AND supervisor_id IS NOT NULL;
1206 COMMIT;
1207 END IF;
1208
1209 IF p_application = 'PA'
1210 THEN
1211 INSERT
1212 INTO hxc_rdb_post_hrs_pm
1213 (batch_id,
1214 hrs_pm,
1215 timecards,
1216 ret_user_id)
1217 SELECT DISTINCT batch_id,
1218 hrs_pm,
1219 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1220 hrs_pm) ,
1221 fnd_global.user_id
1222 FROM hxc_rdb_post_details
1223 WHERE ret_user_id = FND_GLOBAL.user_id
1224 AND batch_id IS NOT NULL
1225 UNION
1226 SELECT DISTINCT retro_batch_id,
1227 hrs_pm,
1228 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1229 hrs_pm) ,
1230 fnd_global.user_id
1231 FROM hxc_rdb_post_details
1232 WHERE ret_user_id = FND_GLOBAL.user_id
1233 AND retro_batch_id IS NOT NULL
1234 UNION
1235 SELECT DISTINCT '0' batch_id,
1236 hrs_pm,
1237 COUNT(DISTINCT timecard_id) OVER (PARTITION BY hrs_pm) measure,
1238 fnd_global.user_id
1239 FROM hxc_rdb_post_details
1240 WHERE ret_user_id = FND_GLOBAL.user_id;
1241
1242 COMMIT;
1243 END IF;
1244
1245
1246 END summarize_hrs_pm;
1247
1248 PROCEDURE summarize_payroll_exp(p_application IN VARCHAR2)
1249 IS
1250
1251 BEGIN
1252
1253 IF p_application = 'PAY'
1254 THEN
1255 INSERT INTO hxc_rdb_post_payroll_exp_type
1256 (batch_id,
1257 payroll_exp_id,
1258 timecards,
1259 ret_user_id)
1260 SELECT DISTINCT batch_id,
1261 payroll_id,
1262 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1263 payroll_id) ,
1264 FND_GLOBAL.user_id
1265 FROM hxc_rdb_post_timecards
1266 WHERE ret_user_id = FND_GLOBAL.user_id
1267 AND payroll_id IS NOT NULL
1268 AND batch_id IS NOT NULL
1269 UNION
1270 SELECT DISTINCT retro_batch_id,
1271 payroll_id,
1272 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1273 payroll_id) ,
1274 FND_GLOBAL.user_id
1275 FROM hxc_rdb_post_timecards
1276 WHERE ret_user_id = FND_GLOBAL.user_id
1277 AND payroll_id IS NOT NULL
1278 AND retro_batch_id IS NOT NULL
1279 UNION
1280 SELECT '0' batch_id,
1281 payroll_id,
1282 COUNT(DISTINCT timecard_id) OVER (PARTITION BY payroll_id) ,
1283 FND_GLOBAL.user_id
1284 FROM hxc_rdb_post_timecards
1285 WHERE ret_user_id = FND_GLOBAL.user_id
1286 AND payroll_id IS NOT NULL;
1287 COMMIT;
1288 END IF;
1289
1290
1291 -- Bug 9626265
1292 -- Added the condition to avoid the totals
1293 -- adding up here.
1294 IF p_application = 'PA'
1295 THEN
1296 INSERT INTO hxc_rdb_post_payroll_exp_type
1297 (batch_id,
1298 payroll_exp_id,
1299 timecards,
1300 ret_user_id)
1301 SELECT DISTINCT batch_id,
1302 attribute3,
1303 SUM(measure) OVER (PARTITION BY batch_id,
1304 attribute3) ,
1305 FND_GLOBAL.user_id
1306 FROM hxc_rdb_post_attributes
1307 WHERE ret_user_id = FND_GLOBAL.user_id
1308 AND total IS NULL ;
1309 COMMIT;
1310 END IF;
1311
1312
1313 END summarize_payroll_exp;
1314
1315
1316
1317
1318 PROCEDURE summarize_partial
1319 IS
1320
1321 BEGIN
1322
1323 INSERT INTO hxc_rdb_post_partial_timecards
1324 (batch_id,
1325 start_time,
1326 stop_time,
1327 timecards,
1328 ret_user_id)
1329 SELECT DISTINCT batch_id,
1330 start_time,
1331 stop_time,
1332 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1333 start_time,
1334 stop_time),
1335 FND_GLOBAL.user_id
1336 FROM hxc_rdb_post_timecards
1337 WHERE partially_retrieved = 'Y'
1338 AND batch_id IS NOT NULL
1339 AND ret_user_id = FND_GLOBAL.user_id
1340 UNION
1341 SELECT DISTINCT retro_batch_id,
1342 start_time,
1343 stop_time,
1344 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1345 start_time,
1346 stop_time),
1347 FND_GLOBAL.user_id
1348 FROM hxc_rdb_post_timecards
1349 WHERE partially_retrieved = 'Y'
1350 AND retro_batch_id IS NOT NULL
1351 AND ret_user_id = FND_GLOBAL.user_id
1352 UNION
1353 SELECT DISTINCT '0' batch_id,
1354 start_time,
1355 stop_time,
1356 COUNT(DISTINCT timecard_id) OVER (PARTITION BY start_time,
1357 stop_time),
1358 FND_GLOBAL.user_id
1359 FROM hxc_rdb_post_timecards
1360 WHERE partially_retrieved = 'Y'
1361 AND ret_user_id = FND_GLOBAL.user_id ;
1362 COMMIT;
1363
1364 END summarize_partial;
1365
1366
1367
1368
1369 PROCEDURE summarize_distinct
1370 IS
1371
1372 BEGIN
1373
1374 INSERT INTO hxc_rdb_post_dist_timecards
1375 (batch_id,
1376 start_time,
1377 stop_time,
1378 timecards,
1379 ret_user_id)
1380 SELECT DISTINCT batch_id,
1381 start_time,
1382 stop_time,
1383 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1384 start_time,
1385 stop_time),
1386 FND_GLOBAL.user_id
1387 FROM hxc_rdb_post_timecards
1388 WHERE batch_id IS NOT NULL
1389 AND ret_user_id = FND_GLOBAL.user_id
1390 UNION
1391 SELECT DISTINCT retro_batch_id,
1392 start_time,
1393 stop_time,
1394 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1395 start_time,
1396 stop_time),
1397 FND_GLOBAL.user_id
1398 FROM hxc_rdb_post_timecards
1399 WHERE retro_batch_id IS NOT NULL
1400 AND ret_user_id = FND_GLOBAL.user_id
1401 UNION
1402 SELECT DISTINCT '0' batch_id,
1403 start_time,
1404 stop_time,
1405 COUNT(DISTINCT timecard_id) OVER (PARTITION BY start_time,
1406 stop_time),
1407 FND_GLOBAL.user_id
1408 FROM hxc_rdb_post_timecards
1409 WHERE ret_user_id = FND_GLOBAL.user_id ;
1410 COMMIT;
1411
1412 END summarize_distinct;
1413
1414
1415
1416 PROCEDURE summarize_org(p_application IN VARCHAR2)
1417 IS
1418
1419 BEGIN
1420
1421 INSERT INTO hxc_rdb_post_org_job
1422 (batch_id,
1423 org_job_id,
1424 timecards,
1425 ret_user_id)
1426 SELECT DISTINCT batch_id,
1427 org_job_id,
1428 COUNT(DISTINCT timecard_id) OVER (PARTITION BY batch_id,
1429 org_job_id) ,
1430 FND_GLOBAL.user_id
1431 FROM hxc_rdb_post_timecards
1432 WHERE ret_user_id = FND_GLOBAL.user_id
1433 AND org_job_id IS NOT NULL
1434 AND batch_id IS NOT NULL
1435 UNION
1436 SELECT DISTINCT retro_batch_id,
1437 org_job_id,
1438 COUNT(DISTINCT timecard_id) OVER (PARTITION BY retro_batch_id,
1439 org_job_id) ,
1440 FND_GLOBAL.user_id
1441 FROM hxc_rdb_post_timecards
1442 WHERE ret_user_id = FND_GLOBAL.user_id
1443 AND org_job_id IS NOT NULL
1444 AND retro_batch_id IS NOT NULL
1445 UNION
1446 SELECT '0' batch_id,
1447 org_job_id,
1448 COUNT(DISTINCT timecard_id) OVER (PARTITION BY org_job_id) ,
1449 FND_GLOBAL.user_id
1450 FROM hxc_rdb_post_timecards
1451 WHERE ret_user_id = FND_GLOBAL.user_id
1452 AND org_job_id IS NOT NULL;
1453 COMMIT;
1454
1455 END summarize_org;
1456
1457
1458
1459 PROCEDURE translate_hrs_pm(p_application IN VARCHAR2)
1460 IS
1461
1462 CURSOR get_proj_manager
1463 IS SELECT ppf.full_name||'('||proj.name||')',
1464 ppf.person_id,
1465 ROWIDTOCHAR(rdb.ROWID)
1466 FROM hxc_rdb_post_hrs_pm rdb,
1467 PA_PROJECT_PARTIES PPP ,
1468 PA_PROJECT_ROLE_TYPES_B PPRT,
1469 per_people_f ppf, -- Bug 12605349
1470 pa_projects_all proj
1471 WHERE PPP.PROJECT_ID = rdb.hrs_pm
1472 AND rdb.ret_user_id = FND_GLOBAL.user_id
1473 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1474 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1475 AND PPRT.role_party_class = 'PERSON'
1476 AND SYSDATE BETWEEN ppf.effective_start_date
1477 AND ppf.effective_end_date
1478 AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
1479 AND rdb.hrs_pm = proj.project_id
1480 AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.start_date_active)
1481 AND NVL(TRUNC(PPP.end_date_active),SYSDATE);
1482
1483 CURSOR get_hrs_name
1484 IS SELECT ppf.full_name,
1485 ppf.person_id,
1486 ROWIDTOCHAR(rdb.ROWID)
1487 FROM hxc_rdb_post_hrs_pm rdb,
1488 per_people_f ppf -- Bug 12605349
1489 WHERE SYSDATE BETWEEN ppf.effective_start_date
1490 AND ppf.effective_end_date
1491 AND rdb.hrs_pm = ppf.person_id
1492 AND rdb.ret_user_id = FND_GLOBAL.user_id;
1493
1494
1495 nametab VARCHARTAB;
1496 idtab NUMBERTAB;
1497 rowtab VARCHARTAB;
1498
1499 BEGIN
1500
1501 IF p_application = 'PAY'
1502 THEN
1503 OPEN get_hrs_name;
1504 LOOP
1505 FETCH get_hrs_name BULK COLLECT INTO nametab,
1506 idtab,
1507 rowtab LIMIT 500;
1508 EXIT WHEN nametab.COUNT = 0;
1509
1510 FORALL i IN nametab.FIRST..nametab.LAST
1511 UPDATE hxc_rdb_post_hrs_pm
1512 SET hrs_pm_name = nametab(i),
1513 resource_id = idtab(i)
1514 WHERE ROWID = CHARTOROWID(rowtab(i));
1515
1516 COMMIT;
1517
1518 END LOOP;
1519 CLOSE get_hrs_name;
1520
1521 END IF;
1522
1523 IF p_application = 'PA'
1524 THEN
1525 OPEN get_proj_manager;
1526 LOOP
1527 FETCH get_proj_manager BULK COLLECT INTO nametab,
1528 idtab,
1529 rowtab LIMIT 500;
1530 EXIT WHEN nametab.COUNT = 0;
1531
1532 FORALL i IN nametab.FIRST..nametab.LAST
1533 UPDATE hxc_rdb_post_hrs_pm
1534 SET hrs_pm_name = nametab(i),
1535 resource_id = idtab(i)
1536 WHERE ROWID = CHARTOROWID(rowtab(i));
1537
1538 COMMIT;
1539
1540 END LOOP;
1541 CLOSE get_proj_manager;
1542 END IF;
1543
1544
1545
1546 END translate_hrs_pm;
1547
1548
1549
1550
1551 PROCEDURE translate_batches(p_application IN VARCHAR2)
1552 IS
1553
1554 -- Bug 9714916
1555 -- While picking up the payroll batches, remove
1556 -- the retro flag from the batch id.
1557 -- Add the tag to the batch name.
1558 CURSOR get_batch_name
1559 IS SELECT pbh.batch_name||DECODE(retro_flag,'Y','(Retro)'),
1560 ROWIDTOCHAR(rdb.ROWID)
1561 FROM hxc_rdb_post_batches rdb,
1562 pay_batch_headers pbh
1563 WHERE REPLACE(rdb.batch_id,'(Retro)') = pbh.batch_id
1564 AND rdb.ret_user_id = FND_GLOBAL.user_id;
1565
1566
1567 nametab VARCHARTAB;
1568 rowtab VARCHARTAB;
1569
1570 BEGIN
1571
1572 IF p_application = 'PAY'
1573 THEN
1574 OPEN get_batch_name;
1575 LOOP
1576 FETCH get_batch_name BULK COLLECT INTO nametab,
1577 rowtab LIMIT 500;
1578 EXIT WHEN nametab.COUNT = 0;
1579
1580 FORALL i IN nametab.FIRST..nametab.LAST
1581 UPDATE hxc_rdb_post_batches
1582 SET batch_name = nametab(i)
1583 WHERE ROWID = CHARTOROWID(rowtab(i));
1584
1585 COMMIT;
1586
1587 END LOOP;
1588 CLOSE get_batch_name;
1589
1590 END IF;
1591
1592 IF p_application = 'PA'
1593 THEN
1594 UPDATE hxc_rdb_post_batches
1595 SET batch_name = batch_id
1596 WHERE ret_user_id = FND_GLOBAL.user_id;
1597 END IF;
1598
1599
1600 END translate_batches;
1601
1602
1603
1604 PROCEDURE translate_attributes(p_application IN VARCHAR2)
1605 IS
1606
1607
1608 CURSOR get_projects
1609 IS SELECT proj.name||' - '||
1610 task.task_number||' - '||
1611 rdb.attribute3,
1612 ROWIDTOCHAR(rdb.ROWID)
1613 FROM hxc_rdb_post_attributes rdb,
1614 pa_projects_all proj,
1615 pa_tasks_expend_v task /*Bug 16391367*/
1616
1617 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
1618 AND rdb.attribute1 = proj.project_id
1619 AND rdb.attribute2 = task.task_id;
1620
1621 CURSOR get_elements
1622 IS SELECT pay.element_name,
1623 ROWIDTOCHAR(rdb.ROWID)
1624 FROM hxc_rdb_post_attributes rdb,
1625 pay_element_types_f_tl pay
1626 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
1627 AND pay.language = USERENV('LANG')
1628 AND rdb.attribute1 = pay.element_type_id;
1629
1630 atttab VARCHARTAB;
1631 rowtab VARCHARTAB;
1632
1633
1634
1635 BEGIN
1636
1637 IF p_application = 'PA'
1638 THEN
1639 OPEN get_projects;
1640 LOOP
1641 FETCH get_projects BULK COLLECT INTO atttab,
1642 rowtab LIMIT 500;
1643 EXIT WHEN atttab.COUNT = 0;
1644
1645 FORALL i IN atttab.FIRST..atttab.LAST
1646 UPDATE hxc_rdb_post_attributes
1647 SET attribute_name = atttab(i)
1648 WHERE ROWID = CHARTOROWID(rowtab(i));
1649
1650
1651 COMMIT;
1652
1653 END LOOP;
1654
1655 CLOSE get_projects;
1656
1657 END IF;
1658
1659 IF p_application = 'PAY'
1660 THEN
1661 OPEN get_elements;
1662 LOOP
1663 FETCH get_elements BULK COLLECT INTO atttab,
1664 rowtab LIMIT 500;
1665 EXIT WHEN atttab.COUNT = 0;
1666
1667 FORALL i IN atttab.FIRST..atttab.LAST
1668 UPDATE hxc_rdb_post_attributes
1669 SET attribute_name = atttab(i)
1670 WHERE ROWID = CHARTOROWID(rowtab(i));
1671
1672
1673 COMMIT;
1674
1675 END LOOP;
1676
1677 CLOSE get_elements;
1678
1679 END IF;
1680
1681 END translate_attributes;
1682
1683
1684
1685
1686 PROCEDURE translate_payroll(p_application IN VARCHAR2)
1687 IS
1688
1689 CURSOR get_proj_manager
1690 IS SELECT ppf.full_name,
1691 ROWIDTOCHAR(rdb.ROWID)
1692 FROM hxc_rdb_pre_hrs_pm rdb,
1693 PA_PROJECT_PARTIES PPP ,
1694 PA_PROJECT_ROLE_TYPES_B PPRT,
1695 per_people_f ppf -- Bug 12605349
1696 WHERE PPP.PROJECT_ID = rdb.hrs_pm
1697 AND rdb.ret_user_id = FND_GLOBAL.user_id
1698 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1699 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1700 AND PPRT.role_party_class = 'PERSON'
1701 AND SYSDATE BETWEEN ppf.effective_start_date
1702 AND ppf.effective_end_date
1703 AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
1704 AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.start_date_active)
1705 AND NVL(TRUNC(PPP.end_date_active),SYSDATE);
1706
1707 CURSOR get_payroll_name
1708 IS SELECT ppf.payroll_name,
1709 ROWIDTOCHAR(rdb.ROWID)
1710 FROM hxc_rdb_post_payroll_exp_type rdb,
1711 pay_payrolls_f ppf
1712 WHERE rdb.payroll_exp_id = ppf.payroll_id
1713 AND rdb.ret_user_id = FND_GLOBAL.user_id;
1714
1715
1716 nametab VARCHARTAB;
1717 rowtab VARCHARTAB;
1718
1719 BEGIN
1720
1721 IF p_application = 'PAY'
1722 THEN
1723 OPEN get_payroll_name;
1724 LOOP
1725 FETCH get_payroll_name BULK COLLECT INTO nametab,
1726 rowtab LIMIT 500;
1727 EXIT WHEN nametab.COUNT = 0;
1728
1729 FORALL i IN nametab.FIRST..nametab.LAST
1730 UPDATE hxc_rdb_post_payroll_exp_type
1731 SET payroll_exp_name = nametab(i)
1732 WHERE ROWID = CHARTOROWID(rowtab(i));
1733
1734 COMMIT;
1735
1736 END LOOP;
1737 CLOSE get_payroll_name;
1738
1739 END IF;
1740
1741 IF p_application = 'PA'
1742 THEN
1743 UPDATE hxc_rdb_post_payroll_exp_type
1744 SET payroll_exp_name = payroll_exp_id
1745 WHERE ret_user_id = FND_GLOBAL.user_id;
1746 END IF;
1747
1748
1749 END translate_payroll;
1750
1751
1752
1753
1754 PROCEDURE translate_org_job(p_application IN VARCHAR2)
1755 IS
1756
1757 CURSOR get_proj_manager
1758 IS SELECT ppf.full_name,
1759 ROWIDTOCHAR(rdb.ROWID)
1760 FROM hxc_rdb_pre_hrs_pm rdb,
1761 PA_PROJECT_PARTIES PPP ,
1762 PA_PROJECT_ROLE_TYPES_B PPRT,
1763 per_people_f ppf -- Bug 12605349
1764 WHERE PPP.PROJECT_ID = rdb.hrs_pm
1765 AND rdb.ret_user_id = FND_GLOBAL.user_id
1766 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1767 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1768 AND PPRT.role_party_class = 'PERSON'
1769 AND SYSDATE BETWEEN ppf.effective_start_date
1770 AND ppf.effective_end_date
1771 AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
1772 AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.start_date_active)
1773 AND NVL(TRUNC(PPP.end_date_active),SYSDATE);
1774
1775 CURSOR get_org_name
1776 IS SELECT org.name,
1777 ROWIDTOCHAR(rdb.ROWID)
1778 FROM hxc_rdb_post_org_job rdb,
1779 hr_organization_units org -- Bug 12605349
1780 WHERE rdb.org_job_id = org.organization_id
1781 AND rdb.ret_user_id = FND_GLOBAL.user_id;
1782
1783 CURSOR get_job_name
1784 IS SELECT job.name,
1785 ROWIDTOCHAR(rdb.ROWID)
1786 FROM hxc_rdb_post_org_job rdb,
1787 per_jobs job
1788 WHERE rdb.org_job_id = job.job_id
1789 AND rdb.ret_user_id = FND_GLOBAL.user_id;
1790
1791
1792
1793 nametab VARCHARTAB;
1794 rowtab VARCHARTAB;
1795
1796 BEGIN
1797
1798 IF p_application = 'PAY'
1799 THEN
1800 OPEN get_org_name;
1801 LOOP
1802 FETCH get_org_name BULK COLLECT INTO nametab,
1803 rowtab LIMIT 500;
1804 EXIT WHEN nametab.COUNT = 0;
1805
1806 FORALL i IN nametab.FIRST..nametab.LAST
1807 UPDATE hxc_rdb_post_org_job
1808 SET org_job_name = nametab(i)
1809 WHERE ROWID = CHARTOROWID(rowtab(i));
1810
1811 COMMIT;
1812
1813 END LOOP;
1814 CLOSE get_org_name;
1815
1816 END IF;
1817
1818 IF p_application = 'PA'
1819 THEN
1820 OPEN get_job_name;
1821 LOOP
1822 FETCH get_job_name BULK COLLECT INTO nametab,
1823 rowtab LIMIT 500;
1824 EXIT WHEN nametab.COUNT = 0;
1825
1826 FORALL i IN nametab.FIRST..nametab.LAST
1827 UPDATE hxc_rdb_post_org_job
1828 SET org_job_name = nametab(i)
1829 WHERE ROWID = CHARTOROWID(rowtab(i));
1830
1831 COMMIT;
1832
1833 END LOOP;
1834 CLOSE get_job_name;
1835
1836 END IF;
1837
1838
1839 END translate_org_job;
1840
1841
1842
1843
1844
1845 BEGIN
1846
1847 -- Begin go
1848
1849 -- Bug 9654164
1850 -- Added this code snippet to manage the validity of
1851 -- this or other sessions by the same user.
1852 l_level := hxc_rdb_pre_retrieval.validate_current_session;
1853 IF l_level = 'ERROR'
1854 THEN
1855 p_msg := 'HXC_RDB_INVALID_SESSION_ERR';
1856 p_level := 'ERROR';
1857 ELSIF l_level = 'WARNING'
1858 THEN
1859 p_msg := 'HXC_RDB_STALE_SESSIONS_WRN';
1860 p_level := 'WARNING';
1861 END IF;
1862
1863
1864 clear_old_data;
1865
1866 -- Bug 12605349 : IF condition is since secure views are already used in l_payroll_criteria and l_org_criteria
1867 IF p_payroll_id IS NULL AND p_org_id IS NULL
1868 THEN
1869 l_pay_sql := l_pay_sql||l_hr_sec;
1870
1871 END IF;
1872 -- Bug 12605349
1873
1874 IF p_application = 'PAY'
1875 THEN
1876 IF p_person_id IS NOT NULL
1877 THEN
1878 l_pay_sql := REPLACE(l_pay_sql,'PERSONCRITERIA','AND ret.resource_id ='||p_person_id);
1879 ELSE
1880 l_pay_sql := REPLACE(l_pay_sql,'PERSONCRITERIA');
1881 END IF;
1882
1883 IF p_batch_ref IS NOT NULL
1884 THEN
1885 l_batch_criteria := REPLACE(l_batch_criteria,'BATCHREF',p_batch_ref);
1886 l_pay_sql := REPLACE(l_pay_sql,'BATCHCRITERIA',l_batch_criteria);
1887 ELSE
1888 l_pay_sql := REPLACE(l_pay_sql,'BATCHCRITERIA');
1889 END IF;
1890
1891 IF p_payroll_id IS NOT NULL
1892 THEN
1893 l_payroll_criteria := REPLACE(l_payroll_criteria,'PAYROLL',p_payroll_id);
1894 l_pay_sql := REPLACE(l_pay_sql,'PAYROLLCRITERIA',l_payroll_criteria);
1895 ELSE
1896 l_pay_sql := REPLACE(l_pay_sql,'PAYROLLCRITERIA');
1897
1898 END IF;
1899
1900 -- Bug 9656063
1901 -- Added this construct for filtering based on Asg's organization.
1902 IF p_org_id IS NOT NULL
1903 THEN
1904 l_org_criteria := REPLACE(l_org_criteria,'ORGANIZATION',p_org_id);
1905 l_pay_sql := REPLACE(l_pay_sql,'ORGCRITERIA',l_org_criteria);
1906 ELSE
1907 l_pay_sql := REPLACE(l_pay_sql,'ORGCRITERIA');
1908
1909 END IF;
1910
1911
1912 OPEN l_pay_cursor FOR l_pay_sql USING TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
1913 ,TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
1914 LOOP
1915 FETCH l_pay_cursor BULK COLLECT INTO tctab,
1916 statustab,
1917 restab,
1918 batchtab,
1919 oldtab,
1920 rettab,
1921 starttab,
1922 stoptab LIMIT 500;
1923 EXIT WHEN tctab.COUNT = 0;
1924
1925 FORALL i IN tctab.FIRST..tctab.LAST
1926 INSERT INTO hxc_rdb_post_timecards
1927 (timecard_id,
1928 approval_status,
1929 resource_id,
1930 batch_id,
1931 old_batch_id,
1932 retro_batch_id,
1933 start_time,
1934 stop_time,
1935 ret_user_id)
1936 VALUES
1937 ( tctab(i),
1938 statustab(i),
1939 restab(i),
1940 batchtab(i),
1941 oldtab(i),
1942 rettab(i),
1943 starttab(i),
1944 stoptab(i),
1945 FND_GLOBAL.user_id);
1946
1947 COMMIT;
1948
1949 END LOOP;
1950
1951
1952 CLOSE l_pay_cursor;
1953
1954
1955
1956 END IF;
1957
1958
1959 IF p_application = 'PA'
1960 THEN
1961 IF p_person_id IS NOT NULL
1962 THEN
1963 l_pa_sql := REPLACE(l_pa_sql,'PERSONCRITERIA','AND ret.resource_id ='||p_person_id);
1964 ELSE
1965 l_pa_sql := REPLACE(l_pa_sql,'PERSONCRITERIA');
1966 END IF;
1967
1968 IF p_batch_ref IS NOT NULL
1969 THEN
1970 l_pa_sql := REPLACE(l_pa_sql,'BATCHCRITERIA','AND ( ret.exp_group = '''||p_batch_ref||''''||
1971 ' OR ret.retro_exp_group = '''||p_batch_ref||''')');
1972 ELSE
1973 l_pa_sql := REPLACE(l_pa_sql,'BATCHCRITERIA');
1974 END IF;
1975
1976
1977 -- Bug 9656063
1978 -- Added this construct for filtering based on Asg's organization.
1979 IF p_org_id IS NOT NULL
1980 THEN
1981 l_org_criteria := REPLACE(l_org_criteria,'ORGANIZATION',p_org_id);
1982 l_pa_sql := REPLACE(l_pa_sql,'ORGCRITERIA',l_org_criteria);
1983 ELSE
1984 l_pa_sql := REPLACE(l_pa_sql,'ORGCRITERIA');
1985
1986 END IF;
1987
1988 -- Bug 12605349
1989 hr_utility.trace(' sql : '||l_pa_sql);
1990 hr_utility.trace(' start : '||TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')));
1991 hr_utility.trace(' end : '||TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK')));
1992 hr_utility.trace(' Org_id: '||NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE('ORG_ID')));
1993
1994
1995
1996 OPEN l_pay_cursor FOR l_pa_sql USING TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
1997 ,TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
1998 LOOP
1999 FETCH l_pay_cursor BULK COLLECT INTO tctab,
2000 statustab,
2001 restab,
2002 batchtab,
2003 oldtab,
2004 rettab,
2005 starttab,
2006 stoptab LIMIT 500;
2007 -- Bug 12605349
2008 hr_utility.trace(' l_pay_cursor count: '||l_pay_cursor%rowcount);
2009
2010 EXIT WHEN tctab.COUNT = 0;
2011
2012 FORALL i IN tctab.FIRST..tctab.LAST
2013 INSERT INTO hxc_rdb_post_timecards
2014 (timecard_id,
2015 approval_status,
2016 resource_id,
2017 batch_id,
2018 old_batch_id,
2019 retro_batch_id,
2020 start_time,
2021 stop_time,
2022 ret_user_id)
2023 VALUES
2024 ( tctab(i),
2025 statustab(i),
2026 restab(i),
2027 batchtab(i),
2028 oldtab(i),
2029 rettab(i),
2030 starttab(i),
2031 stoptab(i),
2032 FND_GLOBAL.user_id);
2033
2034 COMMIT;
2035
2036 END LOOP;
2037
2038
2039 CLOSE l_pay_cursor;
2040
2041
2042 END IF;
2043
2044 -- Bug 9662707
2045 find_and_update_old;
2046 delete_duplicate_tcs;
2047 update_supervisor;
2048 update_emp_details;
2049 update_partially_retrieved(p_application);
2050 pick_up_details(p_application);
2051 -- Bug 9662707
2052 pick_up_old_details(p_application);
2053 update_statuses;
2054 -- Bug 9714916
2055 -- Removed the application check here.
2056 -- Calling this procedure for Payroll (for the sake of OTLR) and projects.
2057 update_retro_batches;
2058 summarize_batches;
2059 summarize_attributes;
2060 summarize_partial;
2061 summarize_distinct;
2062 summarize_hrs_pm(p_application);
2063 summarize_payroll_exp(p_application);
2064 summarize_org(p_application);
2065 translate_hrs_pm(p_application);
2066 translate_batches(p_application);
2067 translate_attributes(p_application);
2068 translate_org_job(p_application);
2069 translate_payroll(p_application);
2070
2071
2072 END go;
2073
2074
2075
2076 PROCEDURE clear_old_data
2077 IS
2078
2079 CURSOR get_old_timecards
2080 IS SELECT ROWIDTOCHAR(ROWID)
2081 FROM hxc_rdb_post_timecards
2082 WHERE ret_user_id = FND_GLOBAL.user_id;
2083
2084 CURSOR get_old_details
2085 IS SELECT ROWIDTOCHAR(ROWID)
2086 FROM hxc_rdb_post_details
2087 WHERE ret_user_id = FND_GLOBAL.user_id;
2088
2089 rowtab VARCHARTAB;
2090
2091 BEGIN
2092 OPEN get_old_timecards;
2093 LOOP
2094 FETCH get_old_timecards BULK COLLECT INTO rowtab LIMIT 500;
2095 EXIT WHEN rowtab.COUNT = 0;
2096
2097 FORALL i IN rowtab.FIRST..rowtab.LAST
2098 DELETE FROM hxc_rdb_post_timecards
2099 WHERE ROWID = CHARTOROWID(rowtab(i));
2100
2101 COMMIT;
2102
2103 END LOOP;
2104 CLOSE get_old_timecards;
2105
2106 OPEN get_old_details;
2107 LOOP
2108 FETCH get_old_details BULK COLLECT INTO rowtab LIMIT 500;
2109 EXIT WHEN rowtab.COUNT = 0;
2110
2111 FORALL i IN rowtab.FIRST..rowtab.LAST
2112 DELETE FROM hxc_rdb_post_details
2113 WHERE ROWID = CHARTOROWID(rowtab(i));
2114
2115 COMMIT;
2116
2117 END LOOP;
2118 CLOSE get_old_details;
2119
2120
2121 DELETE FROM hxc_rdb_post_batches
2122 WHERE ret_user_id = FND_GLOBAL.user_id;
2123
2124 DELETE FROM hxc_rdb_post_attributes
2125 WHERE ret_user_id = FND_GLOBAL.user_id;
2126
2127 DELETE FROM hxc_rdb_post_hrs_pm
2128 WHERE ret_user_id = FND_GLOBAL.user_id;
2129
2130 DELETE FROM hxc_rdb_post_payroll_exp_type
2131 WHERE ret_user_id = FND_GLOBAL.user_id;
2132
2133 DELETE FROM hxc_rdb_post_partial_timecards
2134 WHERE ret_user_id = FND_GLOBAL.user_id;
2135
2136 DELETE FROM hxc_rdb_post_dist_timecards
2137 WHERE ret_user_id = FND_GLOBAL.user_id;
2138
2139 DELETE FROM hxc_rdb_post_org_job
2140 WHERE ret_user_id = FND_GLOBAL.user_id;
2141
2142 DELETE FROM hxc_rdb_post_tc_details
2143 WHERE ret_user_id = FND_GLOBAL.user_id;
2144
2145
2146 COMMIT;
2147
2148 END clear_old_data;
2149
2150
2151 PROCEDURE load_retrieved_details( p_application IN VARCHAR2,
2152 p_timecard_id IN NUMBER)
2153 IS
2154
2155
2156
2157
2158 CURSOR get_projects
2159 IS SELECT proj.name||' - '||
2160 task.task_number||' - '||
2161 rdb.attribute3,
2162 ROWIDTOCHAR(rdb.ROWID)
2163 FROM hxc_rdb_post_tc_details rdb,
2164 pa_projects_all proj,
2165 pa_tasks_expend_v task /*Bug 16391367*/
2166
2167 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
2168 AND rdb.attribute1 = proj.project_id
2169 AND rdb.attribute2 = task.task_id;
2170
2171 CURSOR get_elements
2172 IS SELECT pay.element_name,
2173 ROWIDTOCHAR(rdb.ROWID)
2174 FROM hxc_rdb_post_tc_details rdb,
2175 pay_element_types_f_tl pay
2176 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
2177 AND pay.language = USERENV('LANG')
2178 AND rdb.attribute1 = pay.element_type_id;
2179
2180
2181 -- Bug 9714916
2182 -- If the batch names comes with a piped retro tag,
2183 -- remove those. Works only for Payroll retro batches.
2184 CURSOR get_batch_name
2185 IS SELECT pbh.batch_name,
2186 ROWIDTOCHAR(rdb.ROWID)
2187 FROM hxc_rdb_post_tc_details rdb,
2188 pay_batch_headers pbh
2189 WHERE REPLACE(rdb.batch_id,'(Retro)') = pbh.batch_id
2190 AND rdb.ret_user_id = FND_GLOBAL.user_id;
2191
2192 CURSOR get_batch_name_old
2193 IS SELECT pbh.batch_name,
2194 ROWIDTOCHAR(rdb.ROWID)
2195 FROM hxc_rdb_post_tc_details rdb,
2196 pay_batch_headers pbh
2197 WHERE rdb.old_batch_id = pbh.batch_id
2198 AND rdb.ret_user_id = FND_GLOBAL.user_id;
2199
2200
2201 atttab VARCHARTAB;
2202 rowtab VARCHARTAB;
2203 nametab VARCHARTAB;
2204
2205 BEGIN
2206
2207 DELETE FROM hxc_rdb_post_tc_details
2208 WHERE ret_user_id = FND_GLOBAL.user_id;
2209 COMMIT;
2210
2211 INSERT INTO hxc_rdb_post_tc_details
2212 (time_building_block_id,
2213 date_worked,
2214 measure,
2215 attribute1,
2216 attribute2,
2217 attribute3,
2218 rec_line_id,
2219 batch_id,
2220 request_id,
2221 adj_rec_line_id,
2222 old_batch_id,
2223 old_request_id,
2224 timecard_id,
2225 ret_user_id)
2226 SELECT DISTINCT time_building_block_id,
2227 date_worked,
2228 measure,
2229 attribute1,
2230 attribute2,
2231 attribute3,
2232 rec_line_id,
2233 batch_id,
2234 request_id,
2235 NULL,
2236 NULL,
2237 NULL,
2238 timecard_id,
2239 FND_GLOBAL.user_id
2240 FROM hxc_rdb_post_details det
2241 WHERE timecard_id = p_timecard_id
2242 AND rec_line_id IS NOT NULL
2243 AND ret_user_id = FND_GLOBAL.user_id
2244 UNION
2245 ALL
2246 SELECT DISTINCT time_building_block_id,
2247 date_worked,
2248 -1*old_measure,
2249 old_attribute1,
2250 old_attribute2,
2251 old_attribute3,
2252 rec_retro_line_id,
2253 retro_batch_id,
2254 request_id,
2255 adj_rec_line_id,
2256 old_batch_id,
2257 old_request_id,
2258 timecard_id,
2259 FND_GLOBAL.user_id
2260 FROM hxc_rdb_post_details det
2261 WHERE timecard_id = p_timecard_id
2262 AND rec_retro_line_id IS NOT NULL
2263 AND ret_user_id = FND_GLOBAL.user_id ;
2264
2265 COMMIT;
2266
2267 IF p_application = 'PA'
2268 THEN
2269 OPEN get_projects;
2270 LOOP
2271 FETCH get_projects BULK COLLECT INTO atttab,
2272 rowtab LIMIT 500;
2273 EXIT WHEN atttab.COUNT = 0;
2274
2275 FORALL i IN atttab.FIRST..atttab.LAST
2276 UPDATE hxc_rdb_post_tc_details
2277 SET attribute_name = atttab(i)
2278 WHERE ROWID = CHARTOROWID(rowtab(i));
2279
2280 COMMIT;
2281
2282 END LOOP;
2283
2284 CLOSE get_projects;
2285
2286 UPDATE hxc_rdb_post_tc_details
2287 SET batch_name = batch_id,
2288 old_line_details = RTRIM(adj_rec_line_id||' - '||old_batch_id||' - '||old_request_id,' - ')
2289 WHERE timecard_id = p_timecard_id
2290 AND ret_user_id = FND_GLOBAL.user_id;
2291
2292 END IF;
2293
2294 IF p_application = 'PAY'
2295 THEN
2296 OPEN get_elements;
2297 LOOP
2298
2299 FETCH get_elements BULK COLLECT INTO atttab,
2300 rowtab LIMIT 500;
2301 EXIT WHEN atttab.COUNT = 0;
2302
2303 FORALL i IN atttab.FIRST..atttab.LAST
2304 UPDATE hxc_rdb_post_tc_details
2305 SET attribute_name = atttab(i)
2306 WHERE ROWID = CHARTOROWID(rowtab(i));
2307
2308 COMMIT;
2309
2310 END LOOP;
2311 CLOSE get_elements;
2312
2313 OPEN get_batch_name;
2314 LOOP
2315 FETCH get_batch_name BULK COLLECT INTO nametab,
2316 rowtab LIMIT 500;
2317 EXIT WHEN nametab.COUNT = 0;
2318
2319 FORALL i IN nametab.FIRST..nametab.LAST
2320 UPDATE hxc_rdb_post_tc_details
2321 SET batch_name = nametab(i)
2322 WHERE ROWID = CHARTOROWID(rowtab(i));
2323
2324 COMMIT;
2325
2326 END LOOP;
2327 CLOSE get_batch_name;
2328
2329 OPEN get_batch_name_old;
2330 LOOP
2331 FETCH get_batch_name_old BULK COLLECT INTO nametab,
2332 rowtab LIMIT 500;
2333 EXIT WHEN nametab.COUNT = 0;
2334
2335 FORALL i IN nametab.FIRST..nametab.LAST
2336 UPDATE hxc_rdb_post_tc_details
2337 SET old_line_details = adj_rec_line_id||' - '||nametab(i)||' - '||old_request_id
2338 WHERE ROWID = CHARTOROWID(rowtab(i));
2339
2340 COMMIT;
2341
2342 END LOOP;
2343 CLOSE get_batch_name_old;
2344 END IF;
2345
2346 COMMIT;
2347
2348
2349 END load_retrieved_details;
2350
2351
2352 /*********************************************************************************************************
2353 Procedure Name : generate_post_retrieval_xml
2354 Description : This procedure is used to dynamically generate the XML structure when the user clicks on
2355 "Generate PDF" button on the Timecard Retrieval Dashboard > Post Retrieval page.
2356 This procedure is called from the Controller of the post retrieval dashboard page and the XML
2357 is passed back to the same Controller which then generates the PDF and launches it on the
2358 self-service page.
2359 *********************************************************************************************************/
2360
2361
2362 PROCEDURE generate_post_retrieval_xml(p_application_code IN VARCHAR2 DEFAULT 'PAY',
2363 p_user_name IN VARCHAR2 DEFAULT 'ANONYMOUS',
2364 p_batch_name IN VARCHAR2 DEFAULT NULL,
2365 p_attribute_name IN VARCHAR2 DEFAULT NULL,
2366 p_sup_name IN VARCHAR2 DEFAULT NULL,
2367 p_payroll_name IN VARCHAR2 DEFAULT NULL,
2368 p_distinct_tc IN VARCHAR2 DEFAULT NULL,
2369 p_partial_tc IN VARCHAR2 DEFAULT NULL,
2370 p_organization IN VARCHAR2 DEFAULT NULL,
2371 p_dynamic_sql IN VARCHAR2,
2372 p_post_xml OUT NOCOPY CLOB
2373 )
2374 IS
2375
2376 l_icx_date_format VARCHAR2(20);
2377 l_language_code VARCHAR2(30);
2378 l_report_info VARCHAR2(100);
2379
2380 query1 varchar2(200);
2381
2382 qryCtx1 dbms_xmlgen.ctxType;
2383 xmlresult1 CLOB;
2384 l_post_xml CLOB DEFAULT empty_clob();
2385 l_resultOffset int;
2386
2387 l_dynamic_cursor SYS_REFCURSOR;
2388
2389
2390 TYPE r_details IS RECORD
2391 (person_name hxc_rdb_post_timecards.emp_name%TYPE,
2392 person_number hxc_rdb_post_timecards.emp_no%TYPE,
2393 start_time varchar2(50),
2394 stop_time varchar2(50),
2395 status fnd_lookup_values.meaning%TYPE,
2396 last_update_date varchar2(50),
2397 resource_id varchar2(20),
2398 timecard_id varchar2(20));
2399
2400 TYPE t_details IS TABLE OF r_details
2401 INDEX BY BINARY_INTEGER;
2402
2403 timecard_details_tab t_details;
2404
2405 BEGIN
2406
2407
2408 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
2409 l_language_code := USERENV('LANG');
2410
2411 l_report_info := '<?xml version="1.0" encoding="UTF-8"?> <HXCRDBPOST> ';
2412
2413 query1 := 'SELECT '
2414 || 'user_name INITIATED_BY, '
2415 || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') RUN_DATE '
2416 || 'from fnd_user '
2417 || 'where user_id = fnd_global.user_id' ;
2418
2419 qryCtx1 := dbms_xmlgen.newContext(query1);
2420 dbms_xmlgen.setRowTag(qryCtx1, NULL);
2421 dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
2422 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
2423 dbms_xmlgen.closecontext(qryctx1);
2424 l_post_xml := xmlresult1;
2425 dbms_lob.write(l_post_xml, length(l_report_info), 1, l_report_info);
2426 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
2427 dbms_lob.copy(l_post_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_report_info), l_resultOffset +1);
2428
2429
2430 dbms_lob.writeappend(l_post_xml, length('<G_PARAMETER_DETAILS>
2431 <APP>' || p_application_code || '</APP>
2432 <BATCH_NAME>' || p_batch_name || '</BATCH_NAME>
2433 <ATTRIBUTE_NAME>' || p_attribute_name || '</ATTRIBUTE_NAME>
2434 <SUP_PM_NAME>' || p_sup_name || '</SUP_PM_NAME>
2435 <PAYROLL_JOB_NAME>' || p_payroll_name || '</PAYROLL_JOB_NAME>
2436 <DISTINCT_PERIOD>' || p_distinct_tc || '</DISTINCT_PERIOD>
2437 <PARTIAL_TC>' || p_partial_tc || '</PARTIAL_TC>
2438 <ORG>' || p_organization || '</ORG>
2439 </G_PARAMETER_DETAILS>
2440 '), '<G_PARAMETER_DETAILS>
2441 <APP>' || p_application_code || '</APP>
2442 <BATCH_NAME>' || p_batch_name || '</BATCH_NAME>
2443 <ATTRIBUTE_NAME>' || p_attribute_name || '</ATTRIBUTE_NAME>
2444 <SUP_PM_NAME>' || p_sup_name || '</SUP_PM_NAME>
2445 <PAYROLL_JOB_NAME>' || p_payroll_name || '</PAYROLL_JOB_NAME>
2446 <DISTINCT_PERIOD>' || p_distinct_tc || '</DISTINCT_PERIOD>
2447 <PARTIAL_TC>' || p_partial_tc || '</PARTIAL_TC>
2448 <ORG>' || p_organization || '</ORG>
2449 </G_PARAMETER_DETAILS>
2450 ');
2451
2452
2453 dbms_lob.writeappend(l_post_xml, length('<LIST_G_DETAILS> '), '<LIST_G_DETAILS> ');
2454
2455 OPEN l_dynamic_cursor FOR p_dynamic_sql;
2456 LOOP
2457 FETCH l_dynamic_cursor BULK COLLECT INTO timecard_details_tab LIMIT 300;
2458 EXIT WHEN timecard_details_tab.COUNT = 0;
2459
2460 FOR l_index IN 1..timecard_details_tab.COUNT
2461 LOOP
2462
2463 dbms_lob.writeappend(l_post_xml, length('<G_DETAILS>
2464 <TIMECARD_ID>' || timecard_details_tab(l_index).timecard_id || '</TIMECARD_ID>
2465 <START_TIME>' || timecard_details_tab(l_index).start_time || '</START_TIME>
2466 <STOP_TIME>' || timecard_details_tab(l_index).stop_time || '</STOP_TIME>
2467 <STATUS>' || timecard_details_tab(l_index).status || '</STATUS>
2468 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
2469 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
2470 </G_DETAILS>
2471 '), '<G_DETAILS>
2472 <TIMECARD_ID>' || timecard_details_tab(l_index).timecard_id || '</TIMECARD_ID>
2473 <START_TIME>' || timecard_details_tab(l_index).start_time || '</START_TIME>
2474 <STOP_TIME>' || timecard_details_tab(l_index).stop_time || '</STOP_TIME>
2475 <STATUS>' || timecard_details_tab(l_index).status || '</STATUS>
2476 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
2477 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
2478 </G_DETAILS>
2479 ');
2480
2481 END LOOP;
2482
2483 END LOOP;
2484
2485 CLOSE l_dynamic_cursor;
2486
2487 dbms_lob.writeappend(l_post_xml, length('</LIST_G_DETAILS>
2488 </HXCRDBPOST>
2489 '), '</LIST_G_DETAILS>
2490 </HXCRDBPOST>
2491 ');
2492
2493 p_post_xml := l_post_xml;
2494
2495 END generate_post_retrieval_xml;
2496
2497
2498 END HXC_RDB_POST_RETRIEVAL;
2499