[Home] [Help]
PACKAGE BODY: APPS.HXC_RDB_PRE_RETRIEVAL
Source
1 PACKAGE BODY HXC_RDB_PRE_RETRIEVAL AS
2 /* $Header: hxcrdbpreret.pkb 120.16.12020000.3 2013/02/27 10:05:06 jnerella ship $ */
3
4
5 PROCEDURE go(p_application IN VARCHAR2,
6 p_start_date IN VARCHAR2 DEFAULT NULL,
7 p_end_date IN VARCHAR2 DEFAULT NULL,
8 p_payroll_id IN NUMBER DEFAULT NULL,
9 p_gre_id IN NUMBER DEFAULT NULL,
10 p_org_id IN NUMBER DEFAULT NULL,
11 p_person_id IN NUMBER DEFAULT NULL,
12 p_cutoff IN VARCHAR2 DEFAULT NULL,
13 p_changes_since IN VARCHAR2 DEFAULT NULL,
14 p_msg OUT NOCOPY VARCHAR2,
15 p_level OUT NOCOPY VARCHAR2
16 )
17 IS
18
19 -- Bug 9626200
20 -- Added this dynamic cursor text to enable search for PA
21 -- application also.
22 l_pa_sql VARCHAR2(32000) :=
23
24 'SELECT DISTINCT ret.resource_id,
25 sum.timecard_id,
26 sum.approval_status,
27 sum.start_time,
28 TRUNC(sum.stop_time),
29 FIRST_VALUE(ret.time_building_block_id)
30 OVER (PARTITION BY ret.timecard_id
31 ORDER BY ret.last_update_date DESC,
32 ret.time_building_block_id DESC),
33 FIRST_VALUE(ret.object_version_number)
34 OVER (PARTITION BY ret.timecard_id
35 ORDER BY ret.last_update_date DESC,
36 ret.time_building_block_id DESC),
37 FIRST_VALUE(ret.last_update_date)
38 OVER (PARTITION BY ret.timecard_id
39 ORDER BY ret.last_update_date DESC,
40 ret.time_building_block_id DESC)
41 FROM hxc_pa_latest_details ret,
42 hxc_timecard_summary sum
43 WHERE ret.last_update_date >= FND_DATE.canonical_to_date(:SINCEDATE)
44 AND ret.timecard_id = sum.timecard_id
45 AND ret.org_id = :ORGID ';
46
47
48 l_pay_sql VARCHAR2(32000) :=
49 'SELECT DISTINCT ret.resource_id,
50 sum.timecard_id,
51 sum.approval_status,
52 sum.start_time,
53 TRUNC(sum.stop_time),
54 FIRST_VALUE(ret.time_building_block_id)
55 OVER (PARTITION BY ret.timecard_id
56 ORDER BY ret.last_update_date DESC,
57 ret.time_building_block_id DESC),
58 FIRST_VALUE(ret.object_version_number)
59 OVER (PARTITION BY ret.timecard_id
60 ORDER BY ret.last_update_date DESC,
61 ret.time_building_block_id DESC),
62 FIRST_VALUE(ret.last_update_date)
63 OVER (PARTITION BY ret.timecard_id
64 ORDER BY ret.last_update_date DESC,
65 ret.time_building_block_id DESC)
66 FROM hxc_pay_latest_details ret,
67 hxc_timecard_summary sum
68 WHERE ret.timecard_id = sum.timecard_id
69 AND ret.business_group_id = BUSINESSID ';
70
71 -- Bug 12605349: Used secure views where ever needed to add HR security
72
73
74 l_pay_asg VARCHAR2(3000) :=
75 'AND EXISTS ( SELECT 1
76 FROM per_assignments_f paf /*Bug 12605349*/
77 WHERE paf.person_id = ret.resource_id
78 AND ret.start_time BETWEEN paf.effective_start_date
79 AND paf.effective_end_date
80 PAYROLLCRITERIA
81 ORGCRITERIA
82 GRECRITERIA )';
83
84 -- Bug 12605349
85 l_hr_sec VARCHAR2(3000) :=
86 'AND EXISTS (SELECT 1
87 FROM per_people_f ppf
88 WHERE ppf.person_id = ret.resource_id
89 AND ret.start_time BETWEEN ppf.effective_start_date
90 AND ppf.effective_end_date)
91 ';
92 -- Bug 12605349
93
94
95
96 l_pay_cursor SYS_REFCURSOR;
97 l_pa_cursor SYS_REFCURSOR;
98 restab NUMBERTAB;
99 tctab NUMBERTAB;
100 stattab VARCHARTAB;
101 starttab DATETAB;
102 stoptab DATETAB;
103 dettab NUMBERTAB;
104 ovntab NUMBERTAB;
105 ludtab DATETAB;
106 l_level VARCHAR2(50);
107
108 PROCEDURE update_last_touched
109 IS
110
111 CURSOR get_last_touched
112 IS SELECT det.last_updated_by,
113 ROWIDTOCHAR(rdb.rowid)
114 FROM hxc_rdb_pre_timecards rdb,
115 hxc_time_building_blocks det,
116 fnd_user fnd
117 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
118 AND rdb.lu_bb_id = det.time_building_block_id
119 AND rdb.lu_ovn = det.object_version_number
120 AND det.last_updated_by = fnd.user_id
121 AND fnd.employee_id <> det.resource_id
122 ;
123
124 usertab VARCHARTAB;
125 rowtab VARCHARTAB;
126 BEGIN
127 OPEN get_last_touched;
128 LOOP
129 FETCH get_last_touched BULK COLLECT INTO usertab,
130 rowtab LIMIT 1000;
131 EXIT WHEN usertab.COUNT = 0;
132
133 FORALL i IN usertab.FIRST..usertab.LAST
134 UPDATE hxc_rdb_pre_timecards
135 SET last_updated_by = usertab(i)
136 WHERE rowid = CHARTOROWID(rowtab(i));
137
138 COMMIT;
139 END LOOP;
140
141 END update_last_touched;
142
143
144 PROCEDURE update_supervisor
145 IS
146
147 CURSOR get_supervisor
148 IS SELECT paf.supervisor_id,
149 ROWIDTOCHAR(tc.rowid)
150 FROM hxc_rdb_pre_timecards tc,
151 per_assignments_f paf -- Bug 12605349
152 WHERE tc.ret_user_id = FND_GLOBAL.user_id
153 AND tc.resource_id = paf.person_id
154 AND tc.start_time BETWEEN paf.effective_start_date
155 AND paf.effective_end_date;
156
157 suptab NUMBERTAB;
158 rowtab VARCHARTAB;
159
160 BEGIN
161 OPEN get_supervisor;
162 LOOP
163 FETCH get_supervisor BULK COLLECT INTO suptab,
164 rowtab LIMIT 500;
165 EXIT WHEN suptab.COUNT = 0;
166
167 FORALL i IN suptab.FIRST..suptab.LAST
168 UPDATE hxc_rdb_pre_timecards
169 SET supervisor_id = suptab(i)
170 WHERE ROWID = CHARTOROWID(rowtab(i));
171 COMMIT;
172 END LOOP;
173
174 END update_supervisor;
175
176
177 PROCEDURE update_emp_details
178 IS
179
180 CURSOR get_emp_name
181 IS SELECT ppf.full_name,
182 DECODE(ppf.current_npw_flag,'Y',
183 ppf.npw_number,
184 ppf.employee_number),
185 ROWIDTOCHAR(rdb.rowid)
186 FROM hxc_rdb_pre_timecards rdb,
187 per_people_f ppf -- Bug 12605349
188 WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date --Bug 14026118
189 AND ppf.effective_end_date
190 AND rdb.resource_id = ppf.person_id
191 AND rdb.ret_user_id = FND_GLOBAL.user_id;
192
193 nametab VARCHARTAB;
194 notab VARCHARTAB;
195 rowtab VARCHARTAB;
196
197 BEGIN
198 OPEN get_emp_name;
199 LOOP
200 FETCH get_emp_name BULK COLLECT INTO nametab,
201 notab,
202 rowtab LIMIT 500;
203 EXIT WHEN nametab.COUNT = 0;
204
205 FORALL i IN nametab.FIRST..nametab.LAST
206 UPDATE hxc_rdb_pre_timecards
207 SET emp_name = nametab(i),
208 emp_no = notab(i)
209 WHERE rowid = chartorowid(rowtab(i));
210
211 COMMIT;
212
213 END LOOP;
214 CLOSE get_emp_name;
215
216
217 END update_emp_details;
218
219
220 PROCEDURE pick_up_details(p_application IN VARCHAR2)
221 IS
222
223 l_details VARCHAR2(32000) :=
224 'SELECT ret.resource_id,
225 ret.time_building_block_id,
226 ret.object_version_number ovn,
227 tc.approval_status,
228 ret.timecard_id,
229 ret.start_time date_worked,
230 ret.attribute1,
231 ret.attribute2,
232 ret.attribute3,
233 ret.measure,
234 ret.attribute1
235 from LATEST_DETAILS ret,
236 hxc_rdb_pre_timecards tc
237 where ret.timecard_id = tc.timecard_id
238 and tc.ret_user_id = USERID '
239 ;
240
241 l_ref_cursor SYS_REFCURSOR;
242 restab NUMBERTAB;
243 bbtab NUMBERTAB;
244 ovntab NUMBERTAB;
245 stattab VARCHARTAB;
246 tctab NUMBERTAB;
247 dwtab DATETAB;
248 att1tab VARCHARTAB;
249 att2tab VARCHARTAB;
250 att3tab VARCHARTAB;
251 measuretab NUMBERTAB;
252 hrspmtab NUMBERTAB;
253
254 BEGIN
255 IF p_application = 'PA'
256 THEN
257 l_details := REPLACE(l_details,'LATEST_DETAILS','HXC_PA_LATEST_DETAILS');
258 ELSIF p_application = 'PAY'
259 THEN
260 l_details := REPLACE(l_details,'LATEST_DETAILS','HXC_PAY_LATEST_DETAILS');
261 END IF;
262 l_details := REPLACE(l_details,'USERID',FND_GLOBAL.user_id);
263
264 OPEN l_ref_cursor FOR l_details;
265 LOOP
266 FETCH l_ref_cursor BULK COLLECT INTO restab,
267 bbtab,
268 ovntab,
269 stattab,
270 tctab,
271 dwtab,
272 att1tab,
273 att2tab,
274 att3tab,
275 measuretab,
276 hrspmtab LIMIT 500;
277 EXIT WHEN restab.count = 0;
278
279 FORALL i IN restab.FIRST..restab.LAST
280 INSERT INTO hxc_rdb_pre_details
281 (resource_id,
282 time_building_block_id,
283 ovn,
284 approval_status,
285 timecard_id,
286 date_worked,
287 attribute1,
288 attribute2,
289 attribute3,
290 measure,
291 hrs_pm,
292 ret_user_id )
293 VALUES
294 ( restab(i),
295 bbtab(i),
296 ovntab(i),
297 stattab(i),
298 tctab(i),
299 dwtab(i),
300 att1tab(i),
301 att2tab(i),
302 att3tab(i),
303 measuretab(i),
304 DECODE(p_application,'PA',hrspmtab(i),NULL),
305 FND_GLOBAL.user_id);
306
307 commit;
308 end loop;
309
310 END pick_up_details;
311
312
313 PROCEDURE summarize_statuses
314 IS
315
316 BEGIN
317 INSERT
318 INTO hxc_rdb_pre_status
319 (approval_status,
320 timecards,
321 ret_user_id)
322 SELECT approval_status,
323 count(timecard_id) Timecards,
324 FND_GLOBAL.user_id
325 FROM hxc_rdb_pre_timecards
326 WHERE ret_user_id = FND_GLOBAL.user_id
327 GROUP by approval_status
328 UNION
329 SELECT 'Total',count(*),
330 FND_GLOBAL.user_id
331 FROM hxc_rdb_pre_timecards
332 WHERE ret_user_id = FND_GLOBAL.user_id;
333 COMMIT;
334
335 END summarize_statuses;
336
337
338 PROCEDURE summarize_attributes
339 IS
340
341 BEGIN
342 INSERT
343 INTO hxc_rdb_pre_attributes
344 (approval_status,
345 attribute1,
346 attribute2,
347 attribute3,
348 measure,
349 ret_user_id)
350 SELECT DISTINCT approval_status,
351 attribute1,
352 attribute2,
353 attribute3,
354 SUM(measure) OVER (PARTITION BY approval_status,
355 attribute1||
356 attribute2||
357 attribute3) measure,
358 FND_GLOBAL.user_id
359 FROM hxc_rdb_pre_details
360 WHERE ret_user_id = FND_GLOBAL.user_id
361 UNION
362 ALL
363 SELECT DISTINCT 'Total' approval_status,
364 attribute1,
365 attribute2,
366 attribute3,
367 SUM(measure) OVER (PARTITION BY
368 attribute1||
369 attribute2||
370 attribute3) measure,
371 fnd_global.user_id
372 FROM hxc_rdb_pre_details
373 WHERE ret_user_id = FND_GLOBAL.user_id;
374 COMMIT;
375
376 END summarize_attributes;
377
378 PROCEDURE summarize_hrs_pm(p_application IN VARCHAR2)
379 IS
380
381 BEGIN
382 IF p_application = 'PA'
383 THEN
384 INSERT
385 INTO hxc_rdb_pre_hrs_pm
386 (approval_status,
387 hrs_pm,
388 timecards,
389 ret_user_id)
390 SELECT distinct approval_status,
391 hrs_pm,
392 COUNT(DISTINCT timecard_id) OVER (PARTITION BY approval_status,
393 hrs_pm) ,
394 fnd_global.user_id
395 FROM hxc_rdb_pre_details
396 WHERE ret_user_id = FND_GLOBAL.user_id
397 UNION
398 ALL
399 SELECT DISTINCT 'Total' approval_status,
400 hrs_pm,
401 COUNT(DISTINCT timecard_id) OVER (PARTITION BY hrs_pm) measure,
402 fnd_global.user_id
403 FROM hxc_rdb_pre_details
404 WHERE ret_user_id = FND_GLOBAL.user_id;
405
406 COMMIT;
407 END IF;
408
409 IF p_application = 'PAY'
410 THEN
411 INSERT into hxc_rdb_pre_hrs_pm
412 (approval_status,
413 hrs_pm,
414 timecards,
415 ret_user_id)
416 SELECT DISTINCT approval_status,
417 supervisor_id,
418 COUNT(*) OVER (PARTITION BY approval_status,
419 supervisor_id) ,
420 FND_GLOBAL.user_id
421 FROM hxc_rdb_pre_timecards
422 WHERE ret_user_id = FND_GLOBAL.user_id
423 AND supervisor_id IS NOT NULL
424 UNION
425 ALL
426 SELECT DISTINCT 'Total' approval_status,
427 supervisor_id,
428 COUNT(*) OVER (PARTITION BY supervisor_id),
429 FND_GLOBAL.user_id
430 FROM hxc_rdb_pre_timecards
431 WHERE ret_user_id = FND_GLOBAL.user_id
432 AND supervisor_id IS NOT NULL;
433 COMMIT;
434 END IF;
435
436 END summarize_hrs_pm;
437
438 PROCEDURE summarize_updated
439 IS
440
441 BEGIN
442 INSERT
443 INTO hxc_rdb_pre_updated
444 (approval_status,
445 last_updated_by,
446 timecards,
447 ret_user_id)
448 SELECT distinct approval_status,
449 last_updated_by,
450 COUNT(*) OVER (PARTITION BY approval_status,
451 last_updated_by) ,
452 FND_GLOBAL.user_id
453 FROM hxc_rdb_pre_timecards
454 WHERE ret_user_id = FND_GLOBAL.user_id
455 AND last_updated_by IS NOT NULL
456 UNION
457 ALL
458 SELECT distinct 'Total' approval_status,
459 last_updated_by,
460 COUNT(*) OVER (PARTITION BY last_updated_by),
461 FND_GLOBAL.user_id
462 FROM hxc_rdb_pre_timecards
463 WHERE ret_user_id = FND_GLOBAL.user_id
464 AND last_updated_by IS NOT NULL;
465 COMMIT;
466
467 END summarize_updated;
468
469 PROCEDURE translate_hrs_pm(p_application IN VARCHAR2)
470 IS
471
472 CURSOR get_proj_manager
473 IS SELECT ppf.full_name||'('||proj.name||')',
474 ppf.person_id,
475 ROWIDTOCHAR(rdb.rowid)
476 FROM hxc_rdb_pre_hrs_pm rdb,
477 PA_PROJECT_PARTIES PPP ,
478 PA_PROJECT_ROLE_TYPES_B PPRT,
479 per_people_f ppf, -- Bug 12605349
480 pa_projects_all proj
481 WHERE PPP.PROJECT_ID = rdb.hrs_pm
482 AND rdb.ret_user_id = FND_GLOBAL.user_id
483 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
484 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
485 AND PPRT.role_party_class = 'PERSON'
486 AND trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
487 AND ppf.effective_end_date
488 AND PPP.RESOURCE_SOURCE_ID = ppf.person_id
489 AND rdb.hrs_pm = proj.project_id
490 AND trunc(SYSDATE) BETWEEN trunc(PPP.start_date_active)
491 AND NVL(trunc(PPP.end_date_active),SYSDATE);
492
493 CURSOR get_hrs_name
494 IS SELECT ppf.full_name,
495 ppf.person_id,
496 ROWIDTOCHAR(rdb.rowid)
497 FROM hxc_rdb_pre_hrs_pm rdb,
498 per_people_f ppf -- Bug 12605349
499 WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
500 AND ppf.effective_end_date
501 AND rdb.hrs_pm = ppf.person_id
502 AND rdb.ret_user_id = FND_GLOBAL.user_id;
503
504
505 nametab VARCHARTAB;
506 idtab NUMBERTAB;
507 rowtab VARCHARTAB;
508
509 BEGIN
510 IF p_application = 'PA'
511 THEN
512 OPEN get_proj_manager;
513 LOOP
514 FETCH get_proj_manager BULK COLLECT INTO nametab,
515 idtab,
516 rowtab LIMIT 500;
517 EXIT WHEN nametab.COUNT = 0;
518
519 FORALL i IN nametab.FIRST..nametab.LAST
520 UPDATE hxc_rdb_pre_hrs_pm
521 SET hrs_pm_name = nametab(i),
522 resource_id = idtab(i)
523 WHERE rowid = chartorowid(rowtab(i));
524
525 COMMIT;
526
527 END LOOP;
528 CLOSE get_proj_manager;
529 END IF;
530
531 IF p_application = 'PAY'
532 THEN
533 OPEN get_hrs_name;
534 LOOP
535 FETCH get_hrs_name BULK COLLECT INTO nametab,
536 idtab,
537 rowtab LIMIT 500;
538 EXIT WHEN nametab.COUNT = 0;
539
540 FORALL i IN nametab.FIRST..nametab.LAST
541 UPDATE hxc_rdb_pre_hrs_pm
542 SET hrs_pm_name = nametab(i),
543 resource_id = idtab(i)
544 WHERE ROWID = CHARTOROWID(rowtab(i));
545
546 COMMIT;
547
548 END LOOP;
549 CLOSE get_hrs_name;
550
551 END IF;
552
553
554 END translate_hrs_pm;
555
556
557 PROCEDURE translate_attributes(p_application IN VARCHAR2)
558 IS
559
560
561 CURSOR get_projects
562 IS SELECT proj.name||' - '||
563 task.task_number||' - '||
564 rdb.attribute3,
565 ROWIDTOCHAR(rdb.rowid)
566 FROM hxc_rdb_pre_attributes rdb,
567 pa_projects_all proj,
568 pa_tasks_expend_v task
569 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
570 AND rdb.attribute1 = proj.project_id
571 AND rdb.attribute2 = task.task_id;
572
573 CURSOR get_elements
574 IS SELECT pay.element_name,
575 ROWIDTOCHAR(rdb.rowid)
576 FROM hxc_rdb_pre_attributes rdb,
577 pay_element_types_f_tl pay
578 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
579 AND pay.language = USERENV('LANG')
580 AND rdb.attribute1 = pay.element_type_id;
581
582 atttab VARCHARTAB;
583 rowtab VARCHARTAB;
584
585
586
587 BEGIN
588
589 IF p_application = 'PA'
590 THEN
591 OPEN get_projects;
592 LOOP
593 FETCH get_projects BULK COLLECT INTO atttab,
594 rowtab LIMIT 500;
595 EXIT WHEN atttab.COUNT = 0;
596
597 FORALL i IN atttab.FIRST..atttab.LAST
598 UPDATE hxc_rdb_pre_attributes
599 SET attribute_name = atttab(i)
600 WHERE rowid = CHARTOROWID(rowtab(i));
601
602
603 COMMIT;
604
605 END LOOP;
606
607 CLOSE get_projects;
608
609 END IF;
610
611 IF p_application = 'PAY'
612 THEN
613 OPEN get_elements;
614 LOOP
615 FETCH get_elements BULK COLLECT INTO atttab,
616 rowtab LIMIT 500;
617 EXIT WHEN atttab.COUNT = 0;
618
619 FORALL i IN atttab.FIRST..atttab.LAST
620 UPDATE hxc_rdb_pre_attributes
621 SET attribute_name = atttab(i)
622 WHERE rowid = CHARTOROWID(rowtab(i));
623
624
625 COMMIT;
626
627 END LOOP;
628
629 CLOSE get_elements;
630
631 END IF;
632
633 END translate_attributes;
634
635 PROCEDURE translate_updated_by
636 IS
637
638 CURSOR get_updated
639 IS SELECT ppf.full_name,
640 ppf.person_id,
641 ROWIDTOCHAR(rdb.rowid)
642 FROM hxc_rdb_pre_updated rdb,
643 fnd_user fnd,
644 per_people_f ppf -- Bug 12605349
645 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
646 AND rdb.last_updated_by = fnd.user_id
647 AND fnd.employee_id = ppf.person_id
648 AND trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
649 AND ppf.effective_end_date;
650
651 nametab VARCHARTAB;
652 idtab NUMBERTAB;
653 rowtab VARCHARTAB;
654
655
656 BEGIN
657 OPEN get_updated;
658 LOOP
659 FETCH get_updated BULK COLLECT INTO nametab,
660 idtab,
661 rowtab LIMIT 500;
662 EXIT WHEN nametab.COUNT = 0;
663
664 FORALL i IN nametab.FIRST..nametab.LAST
665 UPDATE hxc_rdb_pre_updated
666 SET last_updated_name = nametab(i),
667 resource_id = idtab(i)
668 WHERE rowid = CHARTOROWID(rowtab(i));
669
670 COMMIT;
671 END LOOP;
672 END translate_updated_by;
673
674
675 PROCEDURE translate_skipped
676 IS
677
678 CURSOR get_emp_name
679 IS SELECT ppf.full_name,
680 DECODE(ppf.current_npw_flag,'Y',
681 ppf.npw_number,
682 ppf.employee_number),
683 ROWIDTOCHAR(rdb.rowid)
684 FROM hxc_rdb_pre_skipped rdb,
685 per_people_f ppf -- Bug 12605349
686 WHERE trunc(SYSDATE) BETWEEN ppf.effective_start_date -- Bug 14026118
687 AND ppf.effective_end_date
688 AND rdb.resource_id = ppf.person_id
689 AND rdb.ret_user_id = FND_GLOBAL.user_id;
690
691 nametab VARCHARTAB;
692 notab VARCHARTAB;
693 rowtab VARCHARTAB;
694
695 BEGIN
696 OPEN get_emp_name;
697 LOOP
698 FETCH get_emp_name BULK COLLECT INTO nametab,
699 notab,
700 rowtab LIMIT 500;
701 EXIT WHEN nametab.COUNT = 0;
702
703 FORALL i IN nametab.FIRST..nametab.LAST
704 UPDATE hxc_rdb_pre_skipped
705 SET emp_name = nametab(i),
706 emp_no = notab(i)
707 WHERE rowid = chartorowid(rowtab(i));
708
709 COMMIT;
710
711 END LOOP;
712 CLOSE get_emp_name;
713
714 END translate_skipped;
715
716
717
718
719 BEGIN
720 -- BEGIN GO
721 -- Bug 9654164
722 -- Added this code snippet to manage the validity of
723 -- this or other sessions by the same user.
724 l_level := validate_current_session;
725 IF l_level = 'ERROR'
726 THEN
727 p_msg := 'HXC_RDB_INVALID_SESSION_ERR';
728 p_level := 'ERROR';
729 ELSIF l_level = 'WARNING'
730 THEN
731 p_msg := 'HXC_RDB_STALE_SESSIONS_WRN';
732 p_level := 'WARNING';
733 END IF;
734
735 -- Bug 12605349
736 IF p_org_id IS NULL AND COALESCE(p_payroll_id,p_gre_id,p_org_id) IS NULL
737 THEN
738
739 l_pay_sql := l_pay_sql||l_hr_sec;
740
741 END IF;
742 -- Bug 12605349
743
744
745 clear_old_data;
746 IF p_application = 'PA'
747 THEN
748 IF p_org_id IS NOT NULL
749 THEN
750 l_pa_sql := l_pa_sql||l_pay_asg;
751 l_pa_sql := REPLACE(l_pa_sql,'PAYROLLCRITERIA');
752 l_pa_sql := REPLACE(l_pa_sql,'GRECRITERIA');
753 l_pa_sql := REPLACE(l_pa_sql,'ORGCRITERIA','AND paf.organization_id = '||p_org_id||' ');
754
755 END IF;
756 IF p_person_id IS NOT NULL
757 THEN
758 l_pa_sql := l_pa_sql||' AND ret.resource_id = '||p_person_id;
759 END IF;
760
761 IF p_start_date IS NOT NULL
762 THEN
763 l_pa_sql := l_pa_sql||' AND sum.start_time >= fnd_date.canonical_to_date('''||
764 fnd_date.date_to_canonical(TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
765 )||''') ';
766 END IF;
767
768 IF p_end_date IS NOT NULL
769 THEN
770 l_pa_sql := l_pa_sql||' AND TRUNC(sum.stop_time) <= fnd_date.canonical_to_date('''||
771 fnd_date.date_to_canonical(TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
772 )||''') ';
773 END IF;
774
775
776 -- Bug 12605349
777 hr_utility.trace(' sql: '||l_pa_sql);
778 hr_utility.trace(' Since_date: '||FND_DATE.date_to_canonical( SYSDATE-FND_PROFILE.VALUE('HXC_RETRIEVAL_CHANGES_DATE')));
779 hr_utility.trace(' Org_id: '||NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE('ORG_ID')));
780
781 OPEN l_pa_cursor FOR l_pa_sql
782 USING FND_DATE.date_to_canonical( SYSDATE-FND_PROFILE.VALUE('HXC_RETRIEVAL_CHANGES_DATE')),
783 NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE('ORG_ID'));
784 LOOP
785 FETCH l_pa_cursor BULK COLLECT INTO restab,
786 tctab,
787 stattab,
788 starttab,
789 stoptab,
790 dettab,
791 ovntab,ludtab LIMIT 500;
792
793 -- Bug 12605349
794 hr_utility.trace(' l_pa_cursor count: '||l_pa_cursor%rowcount);
795
796
797 EXIT WHEN restab.COUNT = 0;
798
799 FORALL i IN restab.FIRST..restab.LAST
800 INSERT INTO hxc_rdb_pre_timecards
801 ( resource_id,
802 timecard_id,
803 approval_status,
804 start_time,
805 stop_time,
806 lu_bb_id,
807 lu_ovn,
808 last_update_date,
809 ret_user_id)
810 VALUES (restab(i),
811 tctab(i),
812 stattab(i),
813 starttab(i),
814 stoptab(i),
815 dettab(i),
816 ovntab(i),ludtab(i),
817 FND_GLOBAL.user_id);
818
819 COMMIT;
820 END LOOP;
821
822 CLOSE l_pa_cursor;
823
824
825 l_pa_sql := REPLACE(l_pa_sql,'ret.last_update_date >= FND_DATE.canonical_to_date',
826 'ret.last_update_date < FND_DATE.canonical_to_date');
827
828 OPEN l_pa_cursor FOR l_pa_sql
829 USING FND_DATE.date_to_canonical( SYSDATE-FND_PROFILE.VALUE('HXC_RETRIEVAL_CHANGES_DATE')),
830 NVL(Pa_Moac_Utils.Get_Current_Org_Id,FND_PROFILE.VALUE('ORG_ID'));
831
832 LOOP
833 FETCH l_pa_cursor BULK COLLECT INTO restab,
834 tctab,
835 stattab,
836 starttab,
837 stoptab,
838 dettab,
839 ovntab,ludtab LIMIT 500;
840 EXIT WHEN restab.COUNT = 0;
841
842 FORALL i IN restab.FIRST..restab.LAST
843 INSERT INTO hxc_rdb_pre_skipped
844 ( resource_id,
845 timecard_id,
846 approval_status,
847 start_time,
848 stop_time,
849 ret_user_id)
850 VALUES (restab(i),
851 tctab(i),
852 stattab(i),
853 starttab(i),
854 stoptab(i),
855 FND_GLOBAL.user_id);
856
857 COMMIT;
858 END LOOP;
859 CLOSE l_pa_cursor;
860
861
862
863
864
865 ELSIF p_application = 'PAY'
866 THEN
867 l_pay_sql := REPLACE(l_pay_sql,'BUSINESSID',FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'));
868
869 IF p_start_date IS NOT NULL
870 THEN
871 l_pay_sql := l_pay_sql||' AND sum.start_time >= fnd_date.canonical_to_date('''||
872 fnd_date.date_to_canonical(TO_DATE(p_start_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
873 )||''') ';
874 END IF;
875 IF p_end_date IS NOT NULL
876 THEN
877 l_pay_sql := l_pay_sql||' AND TRUNC(sum.stop_time) <= fnd_date.canonical_to_date('''||
878 fnd_date.date_to_canonical(TO_DATE(p_end_date,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
879 )||''') ';
880
881 END IF;
882
883 IF p_person_id IS NOT NULL
884 THEN
885 l_pay_sql := l_pay_sql||' AND ret.resource_id = '||p_person_id ;
886 END IF;
887
888 IF p_changes_since IS NOT NULL
889 THEN
890 l_pay_sql := l_pay_sql||' AND ret.last_update_date >= fnd_date.canonical_to_date('''||
891 fnd_date.date_to_canonical(TO_DATE(p_changes_since,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
892 )||''') ';
893
894 ELSE
895 l_pay_sql := l_pay_sql||' AND ret.last_update_date >= fnd_date.canonical_to_date('''||
896 fnd_date.date_to_canonical(SYSDATE-FND_PROFILE.VALUE('HXC_RETRIEVAL_CHANGES_DATE'))||''') ';
897 END IF;
898
899
900 IF COALESCE(p_payroll_id,p_gre_id,p_org_id) IS NOT NULL
901 THEN
902 l_pay_sql := l_pay_sql||l_pay_asg;
903 IF p_payroll_id IS NOT NULL
904 THEN
905 l_pay_sql := REPLACE(l_pay_sql,'PAYROLLCRITERIA','AND paf.payroll_id = '||p_payroll_id||' ');
906 ELSE
907 l_pay_sql := REPLACE(l_pay_sql,'PAYROLLCRITERIA');
908 END IF;
909 IF p_org_id IS NOT NULL
910 THEN
911 l_pay_sql := REPLACE(l_pay_sql,'ORGCRITERIA','AND paf.organization_id = '||p_org_id||' ');
912 ELSE
913 l_pay_sql := REPLACE(l_pay_sql,'ORGCRITERIA');
914 END IF;
915
916 IF p_gre_id IS NOT NULL
917 THEN
918 l_pay_sql := REPLACE(l_pay_sql,'GRECRITERIA');
919 ELSE
920 l_pay_sql := REPLACE(l_pay_sql,'GRECRITERIA');
921 END IF;
922
923 END IF;
924
925
926 OPEN l_pay_cursor FOR l_pay_sql;
927 LOOP
928 FETCH l_pay_cursor BULK COLLECT INTO restab,
929 tctab,
930 stattab,
931 starttab,
932 stoptab,
933 dettab,
934 ovntab,ludtab LIMIT 500;
935 EXIT WHEN restab.COUNT = 0;
936
937 FORALL i IN restab.FIRST..restab.LAST
938 INSERT INTO hxc_rdb_pre_timecards
939 ( resource_id,
940 timecard_id,
941 approval_status,
942 start_time,
943 stop_time,
944 lu_bb_id,
945 lu_ovn,
946 last_update_date,
947 ret_user_id)
948 VALUES (restab(i),
949 tctab(i),
950 stattab(i),
951 starttab(i),
952 stoptab(i),
953 dettab(i),
954 ovntab(i),ludtab(i),
955 FND_GLOBAL.user_id);
956
957 COMMIT;
958 END LOOP;
959
960 CLOSE l_pay_cursor;
961
962 l_pay_sql := REPLACE(l_pay_sql,' AND ret.last_update_date >= fnd_date.canonical_to_date(',
963 ' AND ret.last_update_date < fnd_date.canonical_to_date(');
964 OPEN l_pay_cursor FOR l_pay_sql;
965 LOOP
966 FETCH l_pay_cursor BULK COLLECT INTO restab,
967 tctab,
968 stattab,
969 starttab,
970 stoptab,
971 dettab,
972 ovntab,ludtab LIMIT 500;
973 EXIT WHEN restab.COUNT = 0;
974
975 FORALL i IN restab.FIRST..restab.LAST
976 INSERT INTO hxc_rdb_pre_skipped
977 ( resource_id,
978 timecard_id,
979 approval_status,
980 start_time,
981 stop_time,
982 ret_user_id)
983 VALUES (restab(i),
984 tctab(i),
985 stattab(i),
986 starttab(i),
987 stoptab(i),
988 FND_GLOBAL.user_id);
989
990 COMMIT;
991 END LOOP;
992
993
994
995
996 END IF;
997
998 update_last_touched;
999 update_supervisor;
1000 update_emp_details;
1001 pick_up_details(p_application);
1002 summarize_statuses;
1003 summarize_attributes;
1004 summarize_hrs_pm(p_application);
1005 summarize_updated;
1006 translate_hrs_pm(p_application);
1007 translate_attributes(p_application);
1008 translate_updated_by;
1009 translate_skipped;
1010
1011
1012
1013
1014 END go;
1015
1016
1017 PROCEDURE unlock
1018 IS
1019
1020 CURSOR pick_lock_rowid
1021 IS SELECT ROWIDTOCHAR(loc.rowid)
1022 FROM hxc_rdb_pre_timecards rdb,
1023 hxc_locks loc
1024 WHERE rdb.resource_id = loc.resource_id
1025 AND rdb.start_time = loc.start_time
1026 AND TRUNC(rdb.stop_time) = TRUNC(loc.stop_time)
1027 AND lock_date <= SYSDATE - (1/48);
1028
1029 rowtab VARCHARTAB;
1030
1031 BEGIN
1032
1033 OPEN pick_lock_rowid;
1034 LOOP
1035 FETCH pick_lock_rowid BULK COLLECT INTO rowtab LIMIT 500;
1036 EXIT WHEN rowtab.COUNT = 0;
1037
1038 FORALL i IN rowtab.FIRST..rowtab.LAST
1039 DELETE FROM HXC_LOCKS
1040 WHERE ROWID = CHARTOROWID(rowtab(i));
1041 COMMIT;
1042 END LOOP;
1043
1044
1045 CLOSE pick_lock_rowid;
1046
1047 END unlock;
1048
1049 PROCEDURE clear_old_data
1050 IS
1051
1052 CURSOR get_old_timecards
1053 IS SELECT ROWIDTOCHAR(rowid)
1054 FROM hxc_rdb_pre_timecards
1055 WHERE ret_user_id = FND_GLOBAL.user_id;
1056
1057 CURSOR get_old_details
1058 IS SELECT ROWIDTOCHAR(rowid)
1059 FROM hxc_rdb_pre_details
1060 WHERE ret_user_id = FND_GLOBAL.user_id;
1061
1062 rowtab VARCHARTAB;
1063
1064 BEGIN
1065 OPEN get_old_timecards;
1066 LOOP
1067 FETCH get_old_timecards BULK COLLECT INTO rowtab LIMIT 500;
1068 EXIT WHEN rowtab.COUNT = 0;
1069
1070 FORALL i IN rowtab.FIRST..rowtab.LAST
1071 DELETE FROM hxc_rdb_pre_timecards
1072 WHERE ROWID = CHARTOROWID(rowtab(i));
1073
1074 COMMIT;
1075
1076 END LOOP;
1077 CLOSE get_old_timecards;
1078
1079 OPEN get_old_details;
1080 LOOP
1081 FETCH get_old_details BULK COLLECT INTO rowtab LIMIT 500;
1082 EXIT WHEN rowtab.COUNT = 0;
1083
1084 FORALL i IN rowtab.FIRST..rowtab.LAST
1085 DELETE FROM hxc_rdb_pre_details
1086 WHERE ROWID = CHARTOROWID(rowtab(i));
1087
1088 COMMIT;
1089
1090 END LOOP;
1091 CLOSE get_old_details;
1092
1093
1094 DELETE FROM hxc_rdb_pre_status
1095 WHERE ret_user_id = FND_GLOBAL.user_id;
1096
1097 DELETE FROM hxc_rdb_pre_attributes
1098 WHERE ret_user_id = FND_GLOBAL.user_id;
1099
1100 DELETE FROM hxc_rdb_pre_hrs_pm
1101 WHERE ret_user_id = FND_GLOBAL.user_id;
1102
1103 DELETE FROM hxc_rdb_pre_updated
1104 WHERE ret_user_id = FND_GLOBAL.user_id;
1105
1106 DELETE FROM hxc_rdb_pre_skipped
1107 WHERE ret_user_id = FND_GLOBAL.user_id;
1108
1109 COMMIT;
1110
1111 END clear_old_data;
1112
1113
1114 /*********************************************************************************************************
1115 Procedure Name : GENERATE_PRE_RETRIEVAL_XML
1116 Description : This procedure is used to dynamically generate the XML structure when the user clicks on
1117 "Generate PDF" button on the Timecard Retrieval Dashboard > Pre Retrieval page.
1118 This procedure is called from the Controller of the pre retrieval dashboard page and the XML
1119 is passed back to the same Controller which then generates the PDF and launches it on the
1120 self-service page.
1121 *********************************************************************************************************/
1122
1123
1124 PROCEDURE generate_pre_retrieval_xml(p_application_code IN VARCHAR2 DEFAULT 'PAY',
1125 p_user_name IN VARCHAR2 DEFAULT 'ANONYMOUS',
1126 p_timecard_status IN VARCHAR2 DEFAULT NULL,
1127 p_attribute_name IN VARCHAR2 DEFAULT NULL,
1128 p_sup_name IN VARCHAR2 DEFAULT NULL,
1129 p_delegated_person IN VARCHAR2 DEFAULT NULL,
1130 p_dynamic_sql IN VARCHAR2,
1131 p_pre_xml OUT NOCOPY CLOB
1132 )
1133 IS
1134
1135 l_icx_date_format VARCHAR2(20);
1136 l_language_code VARCHAR2(30);
1137 l_report_info VARCHAR2(100);
1138
1139 query1 varchar2(200);
1140
1141 qryCtx1 dbms_xmlgen.ctxType;
1142 xmlresult1 CLOB;
1143 l_pre_xml CLOB DEFAULT empty_clob();
1144 l_resultOffset int;
1145
1146 l_dynamic_cursor SYS_REFCURSOR; -- new code
1147
1148
1149 TYPE r_details IS RECORD
1150 (person_name hxc_rdb_pre_timecards.emp_name%TYPE,
1151 person_number hxc_rdb_pre_timecards.emp_no%TYPE,
1152 start_time varchar2(50),
1153 stop_time varchar2(50),
1154 status fnd_lookup_values.meaning%TYPE,
1155 last_update_date varchar2(50),
1156 resource_id varchar2(20),
1157 timecard_id varchar2(20));
1158
1159 TYPE t_details IS TABLE OF r_details
1160 INDEX BY BINARY_INTEGER;
1161
1162 timecard_details_tab t_details;
1163
1164 BEGIN
1165
1166
1167 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
1168 l_language_code := USERENV('LANG');
1169
1170 l_report_info := '<?xml version="1.0" encoding="UTF-8"?> <HXCRDBPRE> ';
1171
1172 query1 := 'SELECT '
1173 || 'user_name INITIATED_BY, '
1174 || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') RUN_DATE '
1175 || 'from fnd_user '
1176 || 'where user_id = fnd_global.user_id' ;
1177
1178 qryCtx1 := dbms_xmlgen.newContext(query1);
1179 dbms_xmlgen.setRowTag(qryCtx1, NULL);
1180 dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
1181 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1182 dbms_xmlgen.closecontext(qryctx1);
1183 l_pre_xml := xmlresult1;
1184 dbms_lob.write(l_pre_xml, length(l_report_info), 1, l_report_info);
1185 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1186 dbms_lob.copy(l_pre_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_report_info), l_resultOffset +1);
1187
1188
1189 dbms_lob.writeappend(l_pre_xml, length('<G_PARAMETER_DETAILS>
1190 <APP>' || p_application_code || '</APP>
1191 <TIMECARD_STATUS>' || p_timecard_status || '</TIMECARD_STATUS>
1192 <ATTRIBUTE_NAME>' || p_attribute_name || '</ATTRIBUTE_NAME>
1193 <SUPERVISOR_NAME>' || p_sup_name || '</SUPERVISOR_NAME>
1194 <DELEGATED_PERSON>' || p_delegated_person || '</DELEGATED_PERSON>
1195 </G_PARAMETER_DETAILS>
1196 '), '<G_PARAMETER_DETAILS>
1197 <APP>' || p_application_code || '</APP>
1198 <TIMECARD_STATUS>' || p_timecard_status || '</TIMECARD_STATUS>
1199 <ATTRIBUTE_NAME>' || p_attribute_name || '</ATTRIBUTE_NAME>
1200 <SUPERVISOR_NAME>' || p_sup_name || '</SUPERVISOR_NAME>
1201 <DELEGATED_PERSON>' || p_delegated_person || '</DELEGATED_PERSON>
1202 </G_PARAMETER_DETAILS>
1203 ');
1204
1205
1206 dbms_lob.writeappend(l_pre_xml, length('<LIST_G_DETAILS> '), '<LIST_G_DETAILS> ');
1207
1208 OPEN l_dynamic_cursor FOR p_dynamic_sql;
1209 LOOP
1210 FETCH l_dynamic_cursor BULK COLLECT INTO timecard_details_tab LIMIT 300;
1211 EXIT WHEN timecard_details_tab.COUNT = 0;
1212
1213 FOR l_index IN 1..timecard_details_tab.COUNT
1214 LOOP
1215
1216 dbms_lob.writeappend(l_pre_xml, length('<G_DETAILS>
1217 <TIMECARD_ID>' || timecard_details_tab(l_index).timecard_id || '</TIMECARD_ID>
1218 <START_TIME>' || timecard_details_tab(l_index).start_time || '</START_TIME>
1219 <STOP_TIME>' || timecard_details_tab(l_index).stop_time || '</STOP_TIME>
1220 <STATUS>' || timecard_details_tab(l_index).status || '</STATUS>
1221 <LAST_UPDATE_DATE>' || timecard_details_tab(l_index).last_update_date || '</LAST_UPDATE_DATE>
1222 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
1223 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
1224 </G_DETAILS>
1225 '), '<G_DETAILS>
1226 <TIMECARD_ID>' || timecard_details_tab(l_index).timecard_id || '</TIMECARD_ID>
1227 <START_TIME>' || timecard_details_tab(l_index).start_time || '</START_TIME>
1228 <STOP_TIME>' || timecard_details_tab(l_index).stop_time || '</STOP_TIME>
1229 <STATUS>' || timecard_details_tab(l_index).status || '</STATUS>
1230 <LAST_UPDATE_DATE>' || timecard_details_tab(l_index).last_update_date || '</LAST_UPDATE_DATE>
1231 <PERSON_NAME>' || timecard_details_tab(l_index).person_name || '</PERSON_NAME>
1232 <PERSON_NUMBER>' || timecard_details_tab(l_index).person_number || '</PERSON_NUMBER>
1233 </G_DETAILS>
1234 ');
1235
1236 END LOOP;
1237
1238 END LOOP;
1239
1240 CLOSE l_dynamic_cursor;
1241
1242 dbms_lob.writeappend(l_pre_xml, length('</LIST_G_DETAILS>
1243 </HXCRDBPRE>
1244 '), '</LIST_G_DETAILS>
1245 </HXCRDBPRE>
1246 ');
1247
1248 p_pre_xml := l_pre_xml;
1249
1250 END generate_pre_retrieval_xml;
1251
1252
1253
1254
1255
1256 -- Added the below procedure to pick up each timecard's details
1257 -- On demand.
1258 PROCEDURE load_unretrieved_details( p_application IN VARCHAR2,
1259 p_timecard_id IN NUMBER)
1260 IS
1261
1262 CURSOR get_projects
1263 IS SELECT proj.name||' - '||
1264 task.task_number||' - '||
1265 rdb.attribute3,
1266 ROWIDTOCHAR(rdb.ROWID)
1267 FROM hxc_rdb_pre_tc_details rdb,
1268 pa_projects_all proj,
1269 pa_tasks_expend_v task
1270 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
1271 AND rdb.attribute1 = proj.project_id
1272 AND rdb.attribute2 = task.task_id;
1273
1274 CURSOR get_elements
1275 IS SELECT pay.element_name,
1276 ROWIDTOCHAR(rdb.ROWID)
1277 FROM hxc_rdb_pre_tc_details rdb,
1278 pay_element_types_f_tl pay
1279 WHERE rdb.ret_user_id = FND_GLOBAL.user_id
1280 AND pay.language = USERENV('LANG')
1281 AND rdb.attribute1 = pay.element_type_id;
1282
1283
1284
1285 atttab VARCHARTAB;
1286 rowtab VARCHARTAB;
1287 nametab VARCHARTAB;
1288
1289 BEGIN
1290
1291 DELETE FROM hxc_rdb_pre_tc_details
1292 WHERE ret_user_id = FND_GLOBAL.user_id;
1293 COMMIT;
1294
1295 INSERT INTO hxc_rdb_pre_tc_details
1296 (time_building_block_id,
1297 date_worked,
1298 measure,
1299 attribute1,
1300 attribute2,
1301 attribute3,
1302 start_time,
1303 stop_time,
1304 timecard_id,
1305 ret_user_id)
1306 SELECT det.time_building_block_id,
1307 det.date_worked,
1308 det.measure,
1309 attribute1,
1310 attribute2,
1311 attribute3,
1312 TO_CHAR(detail.start_time,'HH24:MI'), -- Bug 9656636
1313 TO_CHAR(detail.stop_time,'HH24:MI'),
1314 timecard_id,
1315 FND_GLOBAL.user_id
1316 FROM hxc_rdb_pre_details det,
1317 hxc_time_building_blocks detail
1318 WHERE timecard_id = p_timecard_id
1319 AND det.time_building_block_id = detail.time_building_block_id
1320 AND det.ovn = detail.object_version_number
1321 AND det.ret_user_id = FND_GLOBAL.user_id;
1322
1323 COMMIT;
1324
1325 IF p_application = 'PA'
1326 THEN
1327 OPEN get_projects;
1328 LOOP
1329 FETCH get_projects BULK COLLECT INTO atttab,
1330 rowtab LIMIT 500;
1331 EXIT WHEN atttab.COUNT = 0;
1332
1333 FORALL i IN atttab.FIRST..atttab.LAST
1334 UPDATE hxc_rdb_pre_tc_details
1335 SET attribute_name = atttab(i)
1336 WHERE ROWID = CHARTOROWID(rowtab(i));
1337
1338
1339 COMMIT;
1340
1341 END LOOP;
1342
1343 CLOSE get_projects;
1344
1345 END IF;
1346
1347 IF p_application = 'PAY'
1348 THEN
1349 OPEN get_elements;
1350 LOOP
1351 FETCH get_elements BULK COLLECT INTO atttab,
1352 rowtab LIMIT 500;
1353 EXIT WHEN atttab.COUNT = 0;
1354
1355 FORALL i IN atttab.FIRST..atttab.LAST
1356 UPDATE hxc_rdb_pre_tc_details
1357 SET attribute_name = atttab(i)
1358 WHERE ROWID = CHARTOROWID(rowtab(i));
1359
1360
1361 COMMIT;
1362
1363 END LOOP;
1364
1365 CLOSE get_elements;
1366
1367 END IF;
1368
1369 COMMIT;
1370
1371
1372 END load_unretrieved_details;
1373
1374
1375 -- Bug 9494445
1376 -- Added this procedure to unlock specific timecards.
1377
1378 PROCEDURE release_timecard_lock ( p_resource_id IN VARCHAR2,
1379 p_start_time IN VARCHAR2,
1380 p_stop_time IN VARCHAR2 )
1381
1382 IS
1383 PRAGMA AUTONOMOUS_TRANSACTION;
1384
1385
1386 BEGIN
1387
1388 DELETE FROM hxc_locks
1389 WHERE resource_id = TO_NUMBER(p_resource_id)
1390 AND TRUNC(start_time) = TO_DATE(p_start_time,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'))
1391 AND TRUNC(stop_time) = TO_DATE(p_stop_time,FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'));
1392 COMMIT;
1393
1394
1395 END release_timecard_lock;
1396
1397
1398 -- Bug 9654164
1399 -- Added this function to be called before
1400 -- all events in the dashboard so that any invalid
1401 -- session is notified right away.
1402
1403 FUNCTION validate_login
1404 RETURN VARCHAR2
1405 IS
1406
1407 l_exists NUMBER;
1408
1409 BEGIN
1410 SELECT 1
1411 INTO l_exists
1412 FROM hxc_rdb_logins
1413 WHERE user_id = FND_GLOBAL.user_id
1414 AND login_id = FND_GLOBAL.login_id
1415 AND status = 'VALID';
1416
1417 RETURN NULL;
1418
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 RETURN 'HXC_RDB_INVALID_SESSION_ERR';
1422
1423 END validate_login;
1424
1425
1426
1427
1428 -- Bug 9654164
1429 -- Added this function to record the session into the list of
1430 -- valid sessions. Only one session is allowed to be valid for
1431 -- a given user. The specific login checks to see if any other login
1432 -- is active, and terminates those sessions.
1433
1434 FUNCTION validate_current_session
1435 RETURN VARCHAR2
1436 IS
1437
1438 l_exists NUMBER;
1439 l_rowid VARCHAR2(50);
1440 l_tab VARCHARTAB := VARCHARTAB();
1441 rowtab VARCHARTAB;
1442 edtab DATETAB;
1443
1444 CURSOR get_others
1445 IS SELECT ROWIDTOCHAR(rdb.rowid),
1446 NVL(fnd.end_time,hr_general.end_of_time)
1447 FROM hxc_rdb_logins rdb,
1448 fnd_logins fnd
1449 WHERE rdb.login_id = fnd.login_id
1450 AND rdb.user_id = fnd_global.user_id
1451 AND rdb.login_id <> fnd_global.login_id;
1452
1453
1454 BEGIN
1455
1456
1457 -- Delete anything which is older than half an hour.
1458 DELETE FROM hxc_rdb_logins
1459 WHERE user_id = FND_GLOBAL.user_id
1460 AND login_id <> FND_GLOBAL.login_id
1461 AND last_action_date < SYSDATE - (1/48);
1462
1463
1464 -- Find out if this session is already invalidated.
1465 BEGIN
1466
1467 SELECT 1
1468 INTO l_exists
1469 FROM hxc_rdb_logins
1470 WHERE user_id = fnd_global.user_id
1471 AND login_id = fnd_global.login_id
1472 AND status = 'INVALID';
1473
1474 IF l_exists = 1
1475 THEN
1476 RETURN 'ERROR';
1477 END IF;
1478
1479 EXCEPTION
1480 WHEN NO_DATA_FOUND THEN
1481 NULL;
1482 END;
1483
1484
1485
1486 -- Either insert a new row or update the last touched date
1487 -- if already existing.
1488 BEGIN
1489 INSERT INTO hxc_rdb_logins
1490 (user_id,
1491 login_id,
1492 last_action_date,
1493 status,
1494 notified)
1495 VALUES
1496 (FND_GLOBAL.user_id,
1497 FND_GLOBAL.login_id,
1498 SYSDATE,
1499 'VALID',
1500 'N');
1501
1502 EXCEPTION
1503 WHEN DUP_VAL_ON_INDEX THEN
1504 UPDATE hxc_rdb_logins
1505 SET last_action_date = SYSDATE
1506 WHERE user_id = FND_GLOBAL.user_id
1507 AND login_id = FND_GLOBAL.login_id;
1508 END;
1509
1510 -- Pick up other sessions which are active
1511 -- right now.
1512 OPEN get_others;
1513 FETCH get_others BULK COLLECT INTO rowtab,
1514 edtab;
1515
1516 CLOSE get_others;
1517
1518 -- Anything which is properly logged out
1519 -- can be deleted.
1520 FORALL i IN rowtab.FIRST..rowtab.LAST
1521 DELETE FROM hxc_rdb_logins
1522 WHERE ROWID = CHARTOROWID(rowtab(i))
1523 AND edtab(i) <> hr_general.end_of_time;
1524
1525
1526 -- Update any other session to be invalid.
1527 FORALL i IN rowtab.FIRST..rowtab.LAST
1528 UPDATE hxc_rdb_logins
1529 SET status = 'INVALID'
1530 WHERE ROWID = CHARTOROWID(rowtab(i))
1531 AND edtab(i) = hr_general.end_of_time
1532 RETURNING rowid BULK COLLECT INTO l_tab;
1533
1534 -- Check if this session is already notified.
1535 -- If notified, no need to send the warning again.
1536 l_rowid := NULL;
1537 BEGIN
1538 SELECT ROWIDTOCHAR(rdb.rowid)
1539 INTO l_rowid
1540 FROM hxc_rdb_logins rdb
1541 WHERE login_id = FND_GLOBAL.login_id
1542 AND user_id = FND_GLOBAL.user_id
1543 AND notified = 'N';
1544
1545 EXCEPTION
1546 WHEN NO_DATA_FOUND THEN
1547 l_rowid := NULL;
1548 END;
1549
1550
1551 IF l_tab.COUNT > 0
1552 AND l_rowid IS NOT NULL
1553 THEN
1554 UPDATE hxc_rdb_logins
1555 SET notified = 'Y'
1556 WHERE rowid = chartorowid(l_rowid);
1557 RETURN 'WARNING';
1558
1559 END IF;
1560
1561 COMMIT;
1562
1563
1564 RETURN NULL;
1565
1566 END validate_current_session;
1567
1568 END HXC_RDB_PRE_RETRIEVAL;
1569