1 PACKAGE BODY HXC_RPT_LOAD_TC_SNAPSHOT AS
2 /* $Header: hxcrpttcsnpsht.pkb 120.18.12020000.2 2012/07/04 09:32:24 amnaraya ship $ */
3
4 g_res_list_cs VARCHARTABLE;
5 g_request_id VARCHAR2(30);
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7
8
9 newline VARCHAR2(1) :=
10 '
11 ';
12
13 -- RESOURCE_WHERE_CLAUSE
14 -- Creates the dynamic query which filters out all the active assignments
15 -- available in the given date range. The pulled out active assignments
16 -- are stored in a data structure and used later to filter out timecards.
17
18 PROCEDURE resource_where_clause ( p_date_from IN DATE
19 , p_date_to IN DATE
20 , p_org_id IN NUMBER DEFAULT NULL
21 , p_locn_id IN NUMBER DEFAULT NULL
22 , p_payroll_id IN NUMBER DEFAULT NULL
23 , p_supervisor_id IN NUMBER DEFAULT NULL
24 , p_person_id IN NUMBER DEFAULT NULL
25 )
26 AS
27
28 l_base_query VARCHAR2(6000)
29 := 'SELECT person_id
30 FROM per_all_assignments_f
31 WHERE assignment_status_type_id IN ( SELECT assignment_status_type_id
32 FROM per_assignment_status_types
33 WHERE user_status IN ( ''Active Assignment''
34 ,''Active Contingent Assignment'') )
35 AND assignment_type IN (''E'',''C'')
36 AND business_group_id = FND_GLOBAL.per_business_group_id
37 AND ( effective_start_date BETWEEN ''p_date_from''
38 AND ''p_date_to''
39 OR effective_end_date BETWEEN ''p_date_from''
40 AND ''p_date_to''
41 OR ''p_date_from'' BETWEEN effective_start_date
42 AND effective_end_date
43 OR ''p_date_to'' BETWEEN effective_Start_date
44 AND effective_end_date )';
45
46
47 l_resource_list NUMTABLE;
48 l_res_list_cs_buff VARCHAR2(400);
49 l_rlc_cnt PLS_INTEGER := 0;
50 l_resource_cur SYS_REFCURSOR;
51
52
53 BEGIN
54
55 -- Public Procedure resource_where_clause
56 -- Takes in all the data filter parameters specified while the request
57 -- was submitted.
58 -- Browses thru all the parameters passed and checks if they are not
59 -- NULL. If not NULL, a relevant AND clause is attached to the
60 -- dynamic sql string.
61 -- Execute the dynamic sql and pull out the active resources, 20 at a
62 -- time.
63 -- Store these results in a plsql table of VARCHAR2 type, each element
64 -- having a comma separated list of 20 person_ids.
65
66 IF g_debug
67 THEN
68 hr_utility.trace('Resource_where_clause');
69 hr_utility.trace('Parameters');
70 hr_utility.trace('==========');
71 hr_utility.trace('p_date_from '||p_date_from);
72 hr_utility.trace('p_date_to '||p_date_to);
73 hr_utility.trace('p_org_id '||p_org_id);
74 hr_utility.trace('p_locn_id '||p_locn_id);
75 hr_utility.trace('p_payroll_id '||p_payroll_id);
76 hr_utility.trace('p_supervisor_id '||p_supervisor_id);
77 hr_utility.trace('p_person_id '||p_person_id);
78 END IF;
79
80 l_base_query := REPLACE(l_base_query,'p_date_from',TO_CHAR(p_date_from));
81 l_base_query := REPLACE(l_base_query,'p_date_to',TO_CHAR(p_date_to));
82
83 IF p_org_id IS NOT NULL
84 THEN
85 l_base_query := l_base_query||'
86 AND organization_id = '||p_org_id;
87 END IF;
88 IF p_payroll_id IS NOT NULL
89 THEN
90 l_base_query := l_base_query||'
91 AND payroll_id = '||p_payroll_id;
92 END IF;
93 IF p_supervisor_id IS NOT NULL
94 THEN
95 l_base_query := l_base_query||'
96 AND supervisor_id = '||p_supervisor_id;
97 END IF;
98 IF p_locn_id IS NOT NULL
99 THEN
100 l_base_query := l_base_query||'
101 AND location_id = '||p_locn_id;
102 END IF;
103 IF p_person_id IS NOT NULL
104 THEN
105 l_base_query := l_base_query||'
106 AND person_id = '||p_person_id;
107 END IF;
108
109
110 IF g_debug
111 THEN
112 hr_utility.trace('Dynamic query to be executed is ');
113 hr_utility.trace(l_base_query);
114 END IF;
115
116 g_res_list_cs := VARCHARTABLE(' ');
117 l_rlc_cnt := g_res_list_cs.FIRST;
118 l_res_list_cs_buff := ' ';
119
120 OPEN l_resource_cur FOR l_base_query;
121 LOOP
122 FETCH l_resource_cur
123 BULK COLLECT
124 INTO l_resource_list LIMIT 20;
125 EXIT WHEN l_resource_list.COUNT=0;
126 FOR j IN l_resource_list.FIRST..l_resource_list.LAST
127 LOOP
128 l_res_list_cs_buff := l_res_list_cs_buff||', '||l_resource_list(j);
129 END LOOP;
130 g_res_list_cs(l_rlc_cnt) := l_res_list_cs_buff;
131 IF g_debug
132 THEN
133 hr_utility.trace('Resource List No.'||l_rlc_cnt);
134 hr_utility.trace('---> '||g_res_list_cs(l_rlc_cnt));
135 END IF;
136
137 l_resource_list.DELETE;
138 l_rlc_cnt := l_rlc_cnt + 1;
139 l_res_list_cs_buff := ' ';
140
141 g_res_list_cs.EXTEND(1);
142
143 END LOOP;
144 CLOSE l_resource_cur;
145
146 IF g_debug
147 THEN
148 hr_utility.trace('resource_where_clause completed alright');
149 END IF;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 hr_utility.trace('No Active Resources For the given criteria ');
154
155 END resource_where_clause;
156
157
158
159 -- LOAD_TC_LEVEL_INFO
160 -- Loads the timecard level information into HXC_RPT_TC_RESOURCE_TEMP
161 -- for all the relevant employees whose person ids are provided as parameter.
162
163 PROCEDURE load_tc_level_info( p_resource_list IN VARCHAR2,
164 p_tc_from IN DATE,
165 p_tc_to IN DATE,
166 p_request_id IN VARCHAR2 DEFAULT NULL)
167 AS
168
169 l_tc_query VARCHAR2(2000) :=
170 ' INSERT INTO hxc_rpt_tc_resource_temp
171 ( resource_id,
172 tc_start_time,
173 tc_stop_time,
174 tc_bb_id,
175 resource_name,
176 request_id )
177 SELECT resource_id,
178 start_time,
179 stop_time,
180 time_building_block_id,
181 MIN(full_name||'' [''||COALESCE(DECODE(current_employee_flag,''Y'',employee_number),
182 DECODE(current_npw_flag,''Y'',npw_number),
183 '' ''
184 )||'']''),
185 ''p_request_id''
186 FROM hxc_time_building_blocks hxc,
187 per_all_people_f ppf
188 WHERE scope = ''TIMECARD''
189 AND person_id = resource_id
190 AND start_time >= effective_start_date
191 AND stop_time <= effective_end_date
192 AND start_time >= ''p_date_from''
193 AND TRUNC(stop_time) <= ''p_date_to''
194 AND resource_id IN ( ';
195
196 BEGIN
197
198
199 -- Public Procedure load_tc_level_info
200 -- Takes in a comma separated list of resource ids, the timecard start date,
201 -- stop_date and request id.
202 -- Attaches the parameters to the timecard query defined above, in the date
203 -- and the resource_id AND clauses.
204 -- Execute the query, which selects from HXC_TIME_BUILDING_BLOCKS and PER_ALL
205 -- _PEOPLE_F and inserts into HXC_RPT_TC_RESOURCE_TEMP
206
207
208 IF g_debug
209 THEN
210 hr_utility.trace('load_tc_level_info');
211 hr_utility.trace('Parameters ');
212 hr_utility.trace('===========');
213 hr_utility.trace('p_resource_list :'||p_resource_list);
214 hr_utility.trace('p_tc_from :'||p_tc_from);
215 hr_utility.trace('p_tc_to :'||p_tc_to);
216 hr_utility.trace('p_request_id :'||p_request_id);
217 END IF;
218
219
220
221 l_tc_query := REPLACE(l_tc_query,'p_date_from',TO_CHAR(p_tc_from));
222 l_tc_query := REPLACE(l_tc_query,'p_date_to',TO_CHAR(p_tc_to));
223 l_tc_query := REPLACE(l_tc_query,'p_request_id',TO_CHAR(p_request_id));
224
225
226 l_tc_query := l_tc_query||p_resource_list||')';
227 l_tc_query :=
228 l_tc_query||'
229 GROUP
230 BY resource_id,
231 start_time,
232 stop_time,
233 time_building_block_id ';
234
235 IF g_debug
236 THEN
237 hr_utility.trace('Timecard select query is ');
238 hr_utility.trace(l_tc_query);
239 END IF;
240 EXECUTE IMMEDIATE l_tc_query;
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 hr_utility.trace('No Timecards found for resource list '||p_resource_list);
245
246 END load_tc_level_info;
247
248
249
250
251
252 -- CLEAR_HISTORY_DATA
253 -- If chosen to regenerate all data and clear history data, deletes all information
254 -- already collected from HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
255
256 PROCEDURE clear_history_data
257 AS
258
259 BEGIN
260
261 -- Public Procedure clear_history_data
262 -- Delete from HXC_RPT_TC_HIST_LOG
263 -- Delete from HXC_RPT_TC_DETAILS_ALL
264 -- Commit the changes.
265
266 IF g_debug
267 THEN
268 hr_utility.trace('Started clear_history_data');
269 END IF;
270
271 DELETE FROM hxc_rpt_tc_hist_log;
272
273 DELETE FROM hxc_rpt_tc_details_all;
274
275 COMMIT;
276
277 IF g_debug
278 THEN
279 hr_utility.trace('clear_history_data completed alright');
280 END IF;
281
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 NULL;
285
286 END clear_history_data;
287
288
289
290 -- FETCH_HISTORY_FROM_DATE
291 -- If chosen to use previously loaded data, this procedure determines from what
292 -- date new changes would have come, or in other words, finds out last date till
293 -- changes where recorded, so that this time history has to be fetched from that
294 -- last recorded date.
295
296 PROCEDURE fetch_history_from_date
297 AS
298
299 resource_id_tab NUMTABLE;
300 start_time_tab DATETABLE;
301 stop_time_tab DATETABLE;
302 history_tab DATETABLE;
303
304 CURSOR get_history_date
305 IS SELECT /*+ LEADING(gt)
306 USE_NL(gt hist)
307 INDEX(hist HXC_RPT_TC_HIST_LOG_PK) */
308 hist.resource_id,
309 hist.tc_start_time,
310 hist.tc_stop_time,
311 hist.history_till_date
312 FROM hxc_rpt_tc_hist_log hist,
313 hxc_rpt_tc_resource_temp gt
314 WHERE gt.resource_id = hist.resource_id
315 AND gt.tc_start_time = hist.tc_start_time
316 AND gt.tc_stop_time = hist.tc_stop_time ;
317
318 BEGIN
319
320 -- Public Procedure fetch_history_from_date
321 -- Look into HXC_RPT_TC_HIST_LOG to find out upto which details are
322 -- captured into HXC_RPT_TC_DETAILS_ALL for all the
323 -- resource_id-tc_start_time-tc_stop_time combinations loaded
324 -- right now into HXC_RPT_TC_RESOURCE_TEMP.
325 -- Update these values as history_from_date in HXC_RPT_TC_RESOURCE_TEMP
326 -- for corresponding resource_id-tc_start_time-tc_stop_time
327 -- combinations.
328 -- For those combinations which there is no record in HXC_RPT_TC_HIST_LOG
329 -- update history_from_date as hr_general.start_of_time
330
331
332 IF g_debug
333 THEN
334 hr_utility.trace('Started fetch_history_from_date ');
335 END IF;
336
337 OPEN get_history_date;
338 FETCH get_history_date
339 BULK COLLECT INTO resource_id_tab,
340 start_time_tab,
341 stop_time_tab,
342 history_tab;
343 CLOSE get_history_date;
344
345 IF g_debug
346 THEN
347 hr_utility.trace('Fetched values for get_history_date ');
348 hr_utility.trace('Total number of rows fetched :'||resource_id_tab.COUNT);
349 END IF;
350
351 IF resource_id_tab.COUNT > 0
352 THEN
353 FORALL i IN resource_id_tab.FIRST..resource_id_tab.LAST
354 UPDATE hxc_rpt_tc_resource_temp
355 SET history_from_date = history_tab(i)
356 WHERE resource_id = resource_id_tab(i)
357 AND tc_start_time = start_time_tab(i)
358 AND tc_stop_time = stop_time_tab(i);
359 END IF;
360
361 history_tab.DELETE;
362 resource_id_tab.DELETE;
363 start_time_tab.DELETE;
364 stop_time_tab.DELETE;
365
366 UPDATE hxc_rpt_tc_resource_temp
367 SET history_from_date = hr_general.start_of_time
368 WHERE history_from_date IS NULL ;
369
370 IF g_debug
371 THEN
372 hr_utility.trace('fetch_history_from_date completed alright');
373 END IF;
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 NULL;
378
379 END fetch_history_from_date;
380
381
382
383 -- UPDATE_LAYOUT_IDS
384 -- Updates the timecard records captured in HXC_RPT_TC_RESOURCE_TEMP with
385 -- their layout_ids from HXC_TIME_ATTRIBUTES table.
386
387 PROCEDURE update_layout_ids
388 AS
389
390 CURSOR get_layout_ids
391 IS SELECT /*+ LEADING(gt)
392 INDEX(hau HXC_TIME_ATTRIBUTE_USAGES_FK2)
393 INDEX(ha HXC_TIME_ATTRIBUTES_PK) */
394 gt.tc_bb_id,
395 ha.attribute1
396 FROM hxc_rpt_tc_resource_temp gt,
397 hxc_time_attribute_usages hau,
398 hxc_time_attributes ha
399 WHERE gt.tc_bb_id = hau.time_building_block_id
400 AND hau.time_building_block_ovn = 1
401 AND hau.time_attribute_id = ha.time_attribute_id
402 AND ha.attribute_category = 'LAYOUT';
403
404 l_tbb_tab NUMTABLE ;
405 l_layout_id_tab NUMTABLE ;
406
407 BEGIN
408
409 -- Public Procedure update_layout_ids
410 -- Join HXC_RPT_TC_RESOURCE_TEMP, HXC_TIME_ATTRIBUTE_USAGES, and
411 -- HXC_TIME_ATTRIBUTES to pick out Attribute1 from
412 -- LAYOUT Attribute_categroy. -- This is the layout_id.
413 -- Update HXC_RPT_TC_RESOURCE_TEMP with the corresponding layout_id
414 -- for each of the timecard records.
415
416 IF g_debug
417 THEN
418 hr_utility.trace('Starting update_layout_ids');
419 END IF;
420
421 OPEN get_layout_ids;
422 LOOP
423 FETCH get_layout_ids
424 BULK COLLECT
425 INTO l_tbb_tab,
426 l_layout_id_tab LIMIT 1000;
427 EXIT WHEN l_layout_id_tab.COUNT = 0;
428
429 IF g_debug
430 THEN
431 hr_utility.trace('Fetched from get_layout_ids');
432 hr_utility.trace('Number of rows fetched '||l_layout_id_tab.COUNT);
433 END IF;
434
435 IF l_layout_id_tab.COUNT > 0
436 THEN
437 FORALL i IN l_layout_id_tab.FIRST..l_layout_id_tab.LAST
438 UPDATE hxc_rpt_tc_resource_temp
439 SET layout_id = l_layout_id_tab(i)
440 WHERE tc_bb_id = l_tbb_tab(i) ;
441 END IF;
442 l_layout_id_tab.DELETE;
443 l_tbb_tab.DELETE;
444
445 END LOOP;
446 CLOSE get_layout_ids;
447
448 IF g_debug
449 THEN
450 hr_utility.trace('update_layout_ids completed alright');
451 END IF;
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 hr_utility.trace('No layout ids found for the timecards, something wrong ');
456
457 END update_layout_ids;
458
459
460 -- UPDATE_LAST_TOUCHED_DATE
461 -- For those records which are already recorded in the past and are being
462 -- reused, update the last touched date, last updated date and last updated
463 -- user.
464
465
466 PROCEDURE update_last_touched_date
467 AS
468
469 -- Bug 13924301
470 -- Reordered the tables in the FROM list
471 CURSOR get_last_touched_date
472 IS SELECT /*+ ORDERED
473 INDEX(det HXC_RPT_TC_DETAILS_FK2)
474 INDEX(bb HXC_TIME_BUILDING_BLOCKS_PK) */
475 bb.time_building_block_id,
476 bb.object_version_number,
477 bb.date_to,
478 bb.last_update_date,
479 bb.last_updated_by
480 FROM hxc_rpt_tc_resource_temp gt,
481 hxc_rpt_tc_details_all det,
482 hxc_time_building_blocks bb
483 WHERE bb.scope = 'DETAIL'
484 AND bb.time_building_block_id = det.detail_bb_id
485 AND bb.object_version_number = det.detail_bb_ovn
486 AND det.tc_bb_id = gt.tc_bb_id
487 AND det.date_to <> bb.date_to ;
488
489 bb_id_tab NUMTABLE;
490 bb_ovn_tab NUMTABLE;
491 date_to_tab DATETABLE;
492 update_date_tab DATETABLE;
493 update_user_tab NUMTABLE;
494
495
496 BEGIN
497
498 -- Public Procedure update_last_touched_date
499 -- Used for those detail records that are already collected in
500 -- HXC_RPT_TC_DETAILS_ALL table.
501 -- For those records in HXC_RPT_TC_DETAILS_ALL which belong to
502 -- the timecard records in HXC_RPT_TC_RESOURCE_TEMP, get the
503 -- last_updated_date, last_updated_by, and date_to values
504 -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
505 -- is different.
506 -- Update all such records with the last_updated_date, last_updated_by
507 -- and date_to columns in HXC_RPT_TC_DETAILS_ALL
508
509
510 IF g_debug
511 THEN
512 hr_utility.trace('Starting update_last_touched_date');
513 END IF;
514
515 OPEN get_last_touched_date;
516
517 FETCH get_last_touched_date
518 BULK COLLECT INTO bb_id_tab,
519 bb_ovn_tab,
520 date_to_tab,
521 update_date_tab,
522 update_user_tab;
523
524 CLOSE get_last_touched_date;
525
526 IF g_debug
527 THEN
528 hr_utility.trace('Fetched from get_last_touched_date');
529 hr_utility.trace('Total number of rows fetched '||bb_id_tab.COUNT);
530 END IF;
531
532 IF bb_id_tab.COUNT > 0
533 THEN
534 FORALL i IN bb_id_tab.FIRST..bb_id_tab.LAST
535 UPDATE hxc_rpt_tc_details_all
536 SET last_update_date = update_date_tab(i),
537 last_updated_by = update_user_tab(i),
538 last_updated_by_user = NULL,
539 date_to = date_to_tab(i)
540 WHERE detail_bb_id = bb_id_tab(i)
541 AND detail_bb_ovn = bb_ovn_tab(i);
542 END IF;
543
544 bb_id_tab.DELETE;
545 bb_ovn_tab.DELETE;
546 date_to_tab.DELETE;
547 update_date_tab.DELETE;
548
549
550 EXCEPTION
551 WHEN NO_DATA_FOUND THEN
552 hr_utility.trace('update_last_touched_date threw NO DATA FOUND');
553
554 END update_last_touched_date;
555
556
557 -- UPDATE_TIMECARD_COMMENTS
558 -- For all the records recorded in a previous run in HXC_RPT_TC_DETAILS_ALL
559 -- updates the timecard comments, if they were changed.
560
561
562 PROCEDURE update_timecard_comments
563 AS
564
565 CURSOR get_timecard_comments
566 IS SELECT /*+ INDEX(det HXC_RPT_TC_DETAILS_FK2)*/
567 comment_text,
568 detail_bb_id,
569 detail_bb_ovn
570 FROM hxc_time_building_blocks bb,
571 hxc_rpt_tc_details_all det,
572 hxc_rpt_tc_resource_temp gt
573 WHERE bb.time_building_block_id = det.tc_bb_id
574 AND bb.object_version_number = det.tc_bb_ovn
575 AND bb.comment_text IS NOT NULL
576 AND det.tc_bb_id = gt.tc_bb_id
577 AND det.request_id = gt.request_id;
578
579 comment_tab VARCHARTABLE;
580 det_bb_tab NUMTABLE;
581 det_ovn_tab NUMTABLE;
582
583 BEGIN
584
585 -- Public Procedure update_timecard_comments
586 -- For all the records previously loaded into HXC_RPT_TC_DETAILS_ALL
587 -- query from HXC_TIME_BUILDING_BLOCKS, their relevant timecard
588 -- comments.
589 -- Update HXC_RPT_TC_DETAILS_ALL with the comments picked up above.
590
591 IF g_debug
592 THEN
593 hr_utility.trace('Starting update_timecard_comments');
594 END IF;
595
596
597 OPEN get_timecard_comments;
598
599 FETCH get_timecard_comments
600 BULK COLLECT INTO comment_tab,
601 det_bb_tab,
602 det_ovn_tab ;
603 CLOSE get_timecard_comments;
604
605 IF g_debug
606 THEN
607 hr_utility.trace('Fetched from get_timecard_comments');
608 hr_utility.trace('Total number of rows fetched '||det_bb_tab.COUNT);
609 END IF;
610
611 IF det_bb_tab.COUNT > 0
612 THEN
613 FORALL i IN det_bb_tab.FIRST..det_bb_tab.LAST
614 UPDATE hxc_rpt_tc_details_all
615 SET tc_comments = comment_tab(i)
616 WHERE detail_bb_id = det_bb_tab(i)
617 AND detail_bb_ovn = det_ovn_tab(i);
618 END IF;
619
620 det_bb_tab.DELETE;
621 det_ovn_tab.DELETE;
622 comment_tab.DELETE;
623
624 IF g_debug
625 THEN
626 hr_utility.trace('Completed update_timecard_comments alright');
627 END IF;
628
629
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632 NULL;
633
634 END update_timecard_comments;
635
636
637
638
639 -- LOAD_DETAIL_INFO
640 -- Now that all history records are processed, new detail information
641 -- is put into HXC_RPT_TC_DETAILS_ALL table.
642
643
644 PROCEDURE load_detail_info ( p_request_sysdate IN DATE)
645 AS
646
647 BEGIN
648
649 -- Public Procedure load_detail_info
650 -- Insert into HXC_RPT_TC_DETAILS_ALL detail information
651 -- and day information from HXC_TIME_BUILDING_BLOCKS
652 -- WHERE clauses are placed taking care that data is picked up
653 -- only from the history_from_date already recorded -- meaning
654 -- we need data that is not existing only. Anyways, at this
655 -- point, an already existing detail is picked up again, the request
656 -- will error out, as there is a primary key on detail bb id and ovn
657 -- in HXC_RPT_TC_DETAILS_ALL.
658
659 IF g_debug
660 THEN
661 hr_utility.trace('load_detail_info begins '||p_request_sysdate);
662 END IF;
663
664 INSERT INTO hxc_rpt_tc_details_all
665 ( resource_id,
666 tc_start_time,
667 tc_stop_time,
668 tc_bb_id,
669 tc_bb_ovn,
670 day_bb_id,
671 day_bb_ovn,
672 day_start_time,
673 day_stop_time,
674 detail_bb_id,
675 detail_bb_ovn,
676 hours_measure,
677 layout_id,
678 detail_comments,
679 creation_date,
680 created_by,
681 last_update_date,
682 last_updated_by,
683 date_from,
684 date_to,
685 request_id,
686 resource_name,
687 day_date_to,
688 status )
689 SELECT gt.resource_id,
690 gt.tc_start_time,
691 gt.tc_stop_time,
692 day.parent_building_block_id,
693 day.parent_building_block_ovn,
694 day.time_building_block_id,
695 day.object_version_number,
696 NVL(detail.start_time,day.start_time),
697 NVL(detail.stop_time,day.stop_time),
698 detail.time_building_block_id,
699 detail.object_version_number,
700 NVL(detail.measure,(detail.stop_time-detail.start_time)*24),
701 gt.layout_id,
702 detail.comment_text,
703 detail.creation_date,
704 detail.created_by,
705 detail.last_update_date,
706 detail.last_updated_by,
707 detail.date_from,
708 detail.date_to,
709 gt.request_id,
710 gt.resource_name,
711 day.date_to,
712 detail.approval_status
713 FROM hxc_rpt_tc_resource_temp gt,
714 hxc_time_building_blocks day,
715 hxc_time_building_blocks detail
716 WHERE gt.tc_bb_id = day.parent_building_block_id
717 AND gt.resource_id = day.resource_id
718 AND day.time_building_block_id = detail.parent_building_block_id
719 AND day.object_version_number = detail.parent_building_block_ovn
720 AND detail.resource_id = day.resource_id
721 AND detail.creation_date > gt.history_from_date ;
722
723 IF g_debug
724 THEN
725 hr_utility.trace('load_detail_info completed alright');
726 END IF;
727
728 EXCEPTION
729 WHEN NO_DATA_FOUND THEN
730 hr_utility.trace('No detail data found, something wrong with this list ');
731
732 END load_detail_info;
733
734
735 -- POPULATE_ATTRIBUTES
736 -- For all the detail time loaded via this request into HXC_RPT_TC_DETAILS_ALL
737 -- populate the relevant (those visible to the user; no hidden values ) time attributes
738 -- from HXC_TIME_ATTRIBUTES.
739
740
741 PROCEDURE populate_attributes(p_layout_id IN NUMBER,
742 p_alias_tab OUT NOCOPY ALIASTAB )
743 AS
744
745 l_curr_layout NUMBER(15);
746 element_where VARCHAR2(50) ;
747
748 CURSOR get_layout_fields ( p_curr_layout NUMBER)
749 IS SELECT 'MAX(DECODE('||DECODE(ATTRIBUTE_CATEGORY,
750 'ELEMENT','SUBSTR(ATTRIBUTE_CATEGORY,1,7)','ATTRIBUTE_CATEGORY'
751 )
752 ||','''||attribute_category||''',ha.'||attribute||'))',
753 attribute_category,
754 component_name,
755 row_num
756 FROM ( SELECT hlc.layout_id,
757 hlc.layout_component_id,
758 REGEXP_REPLACE(hlc.component_name,'.*- ') component_name,
759 DECODE( hlcq.qualifier_attribute26,
760 'Dummy Element Context','ELEMENT',
761 SUBSTR(hlcq.qualifier_attribute26,1,30)
762 ) attribute_category,
763 SUBSTR(hlcq.qualifier_attribute27,1,30) attribute,
764 RANK() OVER ( ORDER BY hlc.layout_component_id ) row_num
765 FROM hxc_layouts hl,
766 hxc_layout_components hlc,
767 hxc_layout_comp_qualifiers hlcq
768 WHERE hlc.layout_id = hl.layout_id
769 AND hl.layout_id = p_curr_layout
770 AND hl.layout_type = 'TIMECARD'
771 AND hlcq.layout_component_id = hlc.layout_component_id
772 AND hlcq.qualifier_attribute25 = 'FLEX'
773 AND hlcq.qualifier_attribute_category IN ('LOV','CHOICE_LIST',
774 'PACKAGE_CHOICE_LIST',
775 'TEXT_FIELD',
776 'DESCRIPTIVE_FLEX')
777 );
778
779
780 dynamic_header VARCHAR2(1000);
781 l_dynamic_header VARCHAR2(1000) :=
782 'BEGIN
783 DECLARE
784 TYPE numtable IS TABLE OF NUMBER;
785 TYPE varchartable IS TABLE OF VARCHAR2(150);
786 det_bb_id_tab numtable;
787 det_bb_ovn_tab numtable;
788 cla_reason_tab varchartable;
789 cla_comments_tab varchartable;
790 cla_type_tab varchartable;
791 ';
792
793 dynamic_cursor_select VARCHAR2(1000);
794 l_dynamic_cursor_select VARCHAR2(1000) :=
795 ' CURSOR get_attributes IS
796 SELECT det.detail_bb_id,
797 det.detail_bb_ovn,
798 MAX(DECODE(ATTRIBUTE_CATEGORY,''REASON'',ha.ATTRIBUTE1)),
799 MAX(DECODE(ATTRIBUTE_CATEGORY,''REASON'',ha.ATTRIBUTE2)),
800 MAX(DECODE(ATTRIBUTE_CATEGORY,''REASON'',ha.ATTRIBUTE3)),
801 ';
802 dynamic_cursor_where VARCHAR2(2000);
803 l_dynamic_cursor_where VARCHAR2(2000) := '
804 FROM hxc_rpt_tc_resource_temp gt,
805 hxc_rpt_tc_details_all det,
806 hxc_time_attribute_usages hau,
807 hxc_time_attributes ha
808 WHERE gt.tc_bb_id = det.tc_bb_id
809 AND gt.layout_id = curr_layout_id
810 AND gt.request_id = det.request_id
811 AND det.detail_bb_id = hau.time_building_block_id
812 AND det.detail_bb_ovn = hau.time_building_block_ovn
813 AND ha.time_attribute_id = hau.time_attribute_id
814 AND (ha.attribute_category in (''REASON'',';
815
816 dynamic_cursor_group_by VARCHAR2(500);
817 l_dynamic_cursor_group_by VARCHAR2(500) :=
818 '
819
820 GROUP by det.detail_bb_id,
821 det.detail_bb_ovn ; ';
822
823 dynamic_cursor_open VARCHAR2(1000);
824 l_dynamic_cursor_open VARCHAR2(1000) :=
825 '
826 BEGIN
827 OPEN get_attributes;
828 FETCH get_attributes BULK COLLECT INTO det_bb_id_tab,
829 det_bb_ovn_tab,
830 cla_reason_tab,
831 cla_comments_tab,
832 cla_type_tab,
833 ';
834 dynamic_cursor_close VARCHAR2(1000) ;
835 l_dynamic_cursor_close VARCHAR2(1000) :=
836 '
837 CLOSE get_attributes;';
838
839 dynamic_update VARCHAR2(2000);
840 l_dynamic_update VARCHAR2(2000) :=
841 ' IF det_bb_id_tab.COUNT > 0 THEN
842 FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
843 UPDATE hxc_rpt_tc_details_all
844 SET cla_reason = cla_reason_tab(i),
845 cla_comments = cla_comments_tab(i),
846 cla_type = cla_type_tab(i),';
847 dynamic_update_where VARCHAR2(1000);
848 l_dynamic_update_where VARCHAR2(1000) :=
849 '
850 WHERE detail_bb_id = det_bb_id_tab(i)
851 AND detail_bb_ovn = det_bb_ovn_tab(i);
852 END IF; ';
853
854 dynamic_footer VARCHAR2(200);
855 l_dynamic_footer VARCHAR2(200) :=
856 '
857 END;
858 END;';
859
860 dynamic_query LONG;
861
862 l_layout_fld_column VARCHAR2(200);
863 l_layout_fld_where VARCHAR2(200);
864 l_layout_fld_rownum NUMBER(15);
865 l_layout_fld_name VARCHAR2(30);
866 alias_cnt NUMBER := 0;
867
868
869 -- INITIALIZE_DYNAMIC_VARIABLES
870 -- The dynamic pl/sql block bits and pieces are constant variables -- you cant
871 -- alter them each time this function is accessed, because they have to be
872 -- reused. The constant variables are all having 'l_' prefixed and the real
873 -- dynamic strings are all equated to these constant variables at the start of
874 -- the parent procedure.
875
876 PROCEDURE initialize_dynamic_variables
877 AS
878
879 BEGIN
880
881 -- Private Procedure initialize_dynamic_variables
882 -- Initialize all the dynamic variables with the dynamic string constants
883
884 IF g_debug
885 THEN
886 hr_utility.trace('Starting initialize_dynamic_variables');
887 END IF;
888
889 dynamic_header := l_dynamic_header;
890 dynamic_cursor_select := l_dynamic_cursor_select;
891 dynamic_cursor_where := l_dynamic_cursor_where;
892 dynamic_cursor_group_by := l_dynamic_cursor_group_by;
893 dynamic_cursor_open := l_dynamic_cursor_open;
894 dynamic_cursor_close := l_dynamic_cursor_close;
895 dynamic_update := l_dynamic_update;
896 dynamic_update_where := l_dynamic_update_where;
897 dynamic_footer := l_dynamic_footer;
898 element_where := ' ) ';
899
900 IF g_debug
901 THEN
902 hr_utility.trace('initialize_dynamic_variables completed alright');
903 END IF;
904
905
906 END initialize_dynamic_variables;
907
908
909
910 BEGIN
911
912 -- Public Procedure populate_attributes
913 -- This one is one of the most processing intensive one in this whole
914 -- request.
915 -- Has dynamic sql bits and pieces, which when processed and joined
916 -- together yield a plsql block which picks out the attributes
917 -- relevant for the detail records.
918 -- This is how the plsql block will look like -- this is from a test run
919 -- which is pasted here for future reference.
920
921 -- Dynamic Pl/Sql Block
922 -- --------------------
923 --
924 -- BEGIN
925 -- DECLARE
926 -- TYPE numtable IS TABLE OF NUMBER;
927 -- TYPE varchartable IS TABLE OF VARCHAR2(200);
928 -- det_bb_id_tab numtable;
929 -- det_bb_ovn_tab numtable;
930 -- cla_reason_tab varchartable;
931 -- cla_comments_tab varchartable;
932 -- cla_type_tab varchartable;
933 -- display_val1 varchartable;
934 -- display_val3 varchartable;
935 --
936 -- CURSOR get_attributes IS
937 -- SELECT det.detail_bb_id,
938 -- det.detail_bb_ovn,
939 -- MAX(DECODE(ATTRIBUTE_CATEGORY,'REASON',ha.ATTRIBUTE1)),
940 -- MAX(DECODE(ATTRIBUTE_CATEGORY,'REASON',ha.ATTRIBUTE2)),
941 -- MAX(DECODE(ATTRIBUTE_CATEGORY,'REASON',ha.ATTRIBUTE3)),
942 -- MAX(DECODE(ATTRIBUTE_CATEGORY,'APPROVAL',ha.Attribute10))
943 -- ,MAX(DECODE(ATTRIBUTE_CATEGORY,'Dummy Cost Context',ha.Attribute1))
944 -- FROM hxc_rpt_tc_resource_temp gt,
945 -- hxc_rpt_tc_details_all det,
946 -- hxc_time_attribute_usages hau,
947 -- hxc_time_attributes ha
948 -- WHERE gt.tc_bb_id = det.tc_bb_id
949 -- AND gt.layout_id = 7
950 -- AND gt.request_id = det.request_id
951 -- AND det.detail_bb_id = hau.time_building_block_id
952 -- AND det.detail_bb_ovn = hau.time_building_block_ovn
953 -- AND ha.time_attribute_id = hau.time_attribute_id
954 -- AND (ha.attribute_category in ('REASON','APPROVAL','Dummy Cost Context') )
955 -- GROUP by det.detail_bb_id,
956 -- det.detail_bb_ovn ;
957 --
958 -- BEGIN
959 -- OPEN get_attributes;
960 -- FETCH get_attributes BULK COLLECT INTO det_bb_id_tab,
961 -- det_bb_ovn_tab,
962 -- cla_reason_tab,
963 -- cla_comments_tab,
964 -- cla_type_tab,
965 -- display_val1,
966 -- display_val3;
967 -- CLOSE get_attributes;
968 -- IF det_bb_id_tab.COUNT > 0 THEN
969 -- FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
970 -- UPDATE hxc_rpt_tc_details_all
971 -- SET cla_reason = cla_reason_tab(i),
972 -- cla_comments = cla_comments_tab(i),
973 -- cla_type = cla_type_tab(i),
974 -- attribute1 = display_val1(i),
975 -- attribute3 = display_val3(i)
976 -- WHERE detail_bb_id = det_bb_id_tab(i)
977 -- AND detail_bb_ovn = det_bb_ovn_tab(i);
978 -- END IF;
979
980 -- END;
981 -- END;
982
983
984 -- This block is created by concatenating a set of bits, which are processed
985 -- after picking values from the tables. Explanation goes below.
986 --
987 -- 1. Dynamic_header -- This has the header info for the block, with the
988 -- Declare statement, types declared and the plsql table objects defined
989 -- which are common for all detail records -- the detail bb ids, ovns
990 -- and CLA reasons. CLA reasons are not present for all detail records,
991 -- but for all records having them, they have the same structure, so the query
992 -- is hardcoded here.
993 -- 2. Dynamic_cursor_select -- This holds the select placeholders or the columns to
994 -- be selected from. Here I chose to select all the attributes at one shot, so
995 -- used GROUP BY and MAX to have them in separate columns. Which columns are to be
996 -- selected, is determined by the get_layout_fields query.
997 -- 3. Dynamic_cursor_where -- This will attach a WHERE clause to the query putting
998 -- in which all attribute categories you have to look at. REASON is added by
999 -- default to pick up the CLA reasons.
1000 -- 4. Dynamic_cursor_group_by -- This will attach a GROUP BY clause to the query
1001 -- putting in all the relevant column names.
1002 -- 5. Dynamic_cursor_open -- This will create the OPEN and FETCH statement for the
1003 -- dynamic cursor.
1004 -- 6. Dynamic_cursor_close -- Will create the close statement for the cursor.
1005 -- 7. Dynamic_update -- Holds the static string to update the time detail records.
1006 -- The results of get_layout_fields query will determine what other columns
1007 -- are to be put in.
1008 -- 8. Dynamic_update_where -- WHERE clause for the above dynamic update clause, again
1009 -- building the WHERE clauses depending upon what is thrown out of get_layout_fields.
1010 -- 9. Dynamic_footer -- Footer for the block, holding END statements.
1011
1012
1013 -- A critical factor in this dynamic block builder is the get_layout_fields cursor
1014 -- which selects out the layout fields -- essentially meaning the user enterable
1015 -- components in the layouts. The four values returned for each user enterable components
1016 -- are --
1017 -- * l_layout_fld_column
1018 -- prints out something like this.
1019 -- MAX(DECODE(ATTRIBUTE_CATEGORY,'Dummy Cost Context',ha.Attribute1))
1020 -- * l_layout_fld_where
1021 -- 'Dummy Cost Context' ( inserted in ATTRIBUTE_CATEGORY IN (..) )
1022 -- * l_layout_fld_name
1023 -- For eg. Hours Type
1024 -- * l_layout_fld_rownum
1025 -- The rank in terms of layout_component_id. Plays a key role because
1026 -- this determines which of the 30 Attributes in HXC_RPT_TC_DETAILS_ALL
1027 -- are used for this component.
1028 --
1029 -- Inline comments are put in below for reference.
1030 --
1031 --
1032
1033 IF g_debug
1034 THEN
1035 hr_utility.trace('populate_attributes');
1036 hr_utility.trace('Parameter - p_layout_id : '||p_layout_id);
1037 END IF;
1038
1039 l_curr_layout := p_layout_id;
1040 initialize_dynamic_variables;
1041 p_alias_tab := ALIASTAB();
1042 OPEN get_layout_fields( l_curr_layout) ;
1043 LOOP
1044 FETCH get_layout_fields INTO l_layout_fld_column,
1045 l_layout_fld_where,
1046 l_layout_fld_name,
1047 l_layout_fld_rownum;
1048 EXIT WHEN get_layout_fields%NOTFOUND;
1049
1050 IF g_debug
1051 THEN
1052 hr_utility.trace('Ftetched from get_layout_fields ');
1053 hr_utility.trace('l_layout_fld_column :'||l_layout_fld_column);
1054 hr_utility.trace('l_layout_fld_where :'||l_layout_fld_where);
1055 hr_utility.trace('l_layout_fld_name :'||l_layout_fld_name);
1056 hr_utility.trace('l_layout_fld_rownum :'||l_layout_fld_rownum);
1057 END IF;
1058
1059 IF l_layout_fld_where NOT LIKE 'OTL_ALIAS%'
1060 THEN
1061 dynamic_header := dynamic_header||'display_val'||l_layout_fld_rownum||' varchartable;
1062 ';
1063 dynamic_cursor_select := dynamic_cursor_select||l_layout_fld_column||'
1064 ,';
1065 dynamic_cursor_where := dynamic_cursor_where||''''||l_layout_fld_where||''',';
1066 dynamic_cursor_open := dynamic_cursor_open||'display_val'||l_layout_fld_rownum||',';
1067 dynamic_update := dynamic_update||'attribute'||l_layout_fld_rownum||
1068 ' = display_val'||l_layout_fld_rownum||'(i),';
1069 IF l_layout_fld_where = 'ELEMENT'
1070 THEN
1071 element_where := ' OR substr(ha.attribute_category,1,7) = ''ELEMENT'')';
1072 IF g_debug
1073 THEN
1074 hr_utility.trace('Attribute category is Element ');
1075 hr_utility.trace('element_where : '||element_where);
1076 END IF;
1077 END IF;
1078 ELSE
1079 IF g_debug
1080 THEN
1081 hr_utility.trace('This is an alias value ');
1082 END IF;
1083 p_alias_tab.EXTEND;
1084 alias_cnt := alias_cnt+1;
1085 p_alias_tab(alias_cnt).layout_id := p_layout_id;
1086 p_alias_tab(alias_cnt).alias_column := l_layout_fld_rownum;
1087 p_alias_tab(alias_cnt).alias_name := l_layout_fld_name;
1088 END IF;
1089 END LOOP;
1090 CLOSE get_layout_fields;
1091
1092
1093 dynamic_cursor_select := RTRIM(dynamic_cursor_select,',');
1094 dynamic_cursor_where := RTRIM(dynamic_cursor_where,',');
1095 dynamic_cursor_where := REPLACE(dynamic_cursor_where,'curr_layout_id',l_curr_layout);
1096 dynamic_cursor_open := RTRIM(dynamic_cursor_open,',');
1097 dynamic_cursor_open := dynamic_cursor_open||';';
1098 dynamic_update := RTRIM(dynamic_update,',');
1099 dynamic_cursor_where := dynamic_cursor_where||')'||element_where;
1100
1101 IF g_debug
1102 THEN
1103 hr_utility.trace('Dynamic Pl/Sql Block created ');
1104 hr_utility.trace('------------------------------');
1105 hr_utility.trace(' ');
1106 hr_utility.trace(dynamic_header);
1107 hr_utility.trace(dynamic_cursor_select);
1108 hr_utility.trace(dynamic_cursor_where);
1109 hr_utility.trace(dynamic_cursor_group_by);
1110 hr_utility.trace(dynamic_cursor_open);
1111 hr_utility.trace(dynamic_cursor_close);
1112 hr_utility.trace(dynamic_update);
1113 hr_utility.trace(dynamic_update_where);
1114 hr_utility.trace(dynamic_footer);
1115 hr_utility.trace('/');
1116 END IF;
1117
1118 dynamic_query := dynamic_header||
1119 dynamic_cursor_select||
1120 dynamic_cursor_where||
1121 dynamic_cursor_group_by||
1122 dynamic_cursor_open||
1123 dynamic_cursor_close||
1124 dynamic_update||
1125 dynamic_update_where||
1126 dynamic_footer;
1127
1128 BEGIN
1129 EXECUTE IMMEDIATE dynamic_query;
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 hr_utility.trace('Execute Immediate in populate attributes threw Sql Error : '||SQLCODE);
1133 RAISE;
1134 END;
1135
1136 IF g_debug
1137 THEN
1138 hr_utility.trace('populate_attributes completed alright ');
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN NO_DATA_FOUND THEN
1143 hr_utility.trace('No data found from Populate attributes');
1144
1145 END populate_attributes;
1146
1147
1148 -- TRANSLATE_ATTRIBUTES
1149 -- Translates the attributes already loaded into HXC_RPT_TC_DETAILS_ALL table
1150 -- against the queries in HXC_RPT_LAYOUT_COMP_QUERIES
1151
1152
1153 PROCEDURE translate_attributes(p_layout_id NUMBER)
1154 AS
1155
1156 CURSOR get_layout_queries( p_layout NUMBER)
1157 IS SELECT attribute||' = '''||component_name||': ''||NVL(('||query||'hx.'||attribute||'),hx.'||attribute||')'
1158 FROM hxc_rpt_layout_comp_queries
1159 WHERE layout_id = p_layout;
1160
1161 l_update VARCHAR2(4000);
1162 l_curr_layout NUMBER(15);
1163
1164 l_update_predicate VARCHAR2(4000) :=
1165 ' UPDATE /*+ INDEX(hx HXC_RPT_TC_DETAILS_FK2) */
1166 hxc_rpt_tc_details_all hx
1167 SET ';
1168
1169 l_update_where VARCHAR2(4000) :=
1170 ' WHERE tc_bb_id IN ( SELECT tc_bb_id
1171 FROM hxc_rpt_tc_resource_temp gt
1172 WHERE layout_id = current_layout
1173 )
1174 AND request_id = THIS_REQUEST_ID';
1175
1176 curr_query VARCHAR2(2000);
1177
1178 BEGIN
1179
1180 -- Public Procedure translate_attributes
1181 -- Get the queries associated with layout components from HXC_RPT_LAYOUT_COMP_QUERIES
1182 -- one by one thru get_layout_queries
1183 -- Create the dynamic update sql string attaching this query for all the detail
1184 -- records belonging to timecards having this layout id.
1185 -- Execute the dynamic update; repeat all the above steps for each component in the
1186 -- corresponding layout, which has a record in HXC_RPT_LAYOUT_COMP_QUERIES.
1187
1188
1189 IF g_debug
1190 THEN
1191 hr_utility.trace('translate_attributes');
1192 hr_utility.trace('Parameter - p_layout_id : '||p_layout_id);
1193 END IF;
1194 l_curr_layout := p_layout_id;
1195 OPEN get_layout_queries(l_curr_layout);
1196 LOOP
1197 FETCH get_layout_queries
1198 INTO curr_query;
1199 EXIT WHEN get_layout_queries%NOTFOUND;
1200 l_update := l_update_predicate||curr_query||l_update_where;
1201 l_update := REPLACE(l_update,'current_layout',l_curr_layout);
1202 l_update := REPLACE(l_update,'THIS_REQUEST_ID',g_request_id);
1203 IF g_debug
1204 THEN
1205 hr_utility.trace('Dynamic Update query is ');
1206 hr_utility.trace(l_update);
1207 END IF;
1208
1209 BEGIN
1210 EXECUTE IMMEDIATE l_update;
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 hr_utility.trace('Execute Immediate in translate_attributes threw Sql Error : '||SQLCODE);
1214 END;
1215
1216 END LOOP;
1217 CLOSE get_layout_queries;
1218
1219 IF g_debug
1220 THEN
1221 hr_utility.trace('translate_attributes completed alright ');
1222 END IF;
1223
1224 EXCEPTION
1225 WHEN NO_DATA_FOUND THEN
1226 hr_utility.trace('No Data Found from translate_attributes ');
1227
1228 END translate_attributes;
1229
1230
1231
1232 -- TRANSLATE_ALIASES
1233 -- This procedure loads and translates all the Alternate Name components
1234 -- associated with the timecard, for a given layout.
1235
1236 PROCEDURE translate_aliases(p_layout_id NUMBER,
1237 p_alias_tab ALIASTAB)
1238 AS
1239
1240
1241 CURSOR get_alias_defs (p_layout_id NUMBER)
1242 IS SELECT DISTINCT alias_definition_id
1243 FROM hxc_rpt_tc_resource_temp
1244 WHERE layout_id = p_layout_id
1245 AND alias_definition_id <> 0;
1246
1247 l_curr_layout NUMBER;
1248 alias_exists NUMBER;
1249 l_curr_alias NUMBER;
1250
1251 CURSOR get_alias_columns ( p_alias_def NUMBER)
1252 IS SELECT 'MAX(DECODE(bld_blk_info_type_id,'||hmc.bld_blk_info_type_id||','
1253 ||DECODE(segment,'ATTRIBUTE_CATEGORY','LTRIM(ha.'||segment||','''||building_block_category||' - '')','ha.'||segment)||'))',
1254 hatc.component_type
1255 FROM hxc_mapping_components hmc,
1256 hxc_alias_types hat,
1257 hxc_alias_type_components hatc,
1258 hxc_alias_definitions had,
1259 hxc_bld_blk_info_type_usages bldu,
1260 hxc_bld_blk_info_types bld
1261 WHERE had.alias_type_id = hat.alias_type_id
1262 AND hatc.alias_type_id = hat.alias_type_id
1263 AND hmc.mapping_component_id = hatc.mapping_component_id
1264 AND bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1265 AND bld.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1266 AND bld.bld_blk_info_type_id = bldu.bld_blk_info_type_id
1267 AND had.alias_definition_id = p_alias_def
1268 ORDER
1269 BY hatc.component_type ;
1270
1271 l_alias_column VARCHAR2(500);
1272
1273 dynamic_cursor VARCHAR2(2000);
1274 l_dynamic_cursor VARCHAR2(2000) :=
1275 ' alias_value varchartable;
1276 CURSOR get_alias_attributes IS
1277 SELECT detail_bb_id,
1278 detail_bb_ovn,
1279 ';
1280
1281 dynamic_where VARCHAR2(2000);
1282 l_dynamic_where VARCHAR2(2000) :=
1283 ' '' ''
1284 FROM hxc_rpt_tc_details_all det,
1285 hxc_rpt_tc_resource_temp gt,
1286 hxc_time_attribute_usages hau,
1287 hxc_time_attributes ha
1288 WHERE gt.tc_bb_id = det.tc_bb_id
1289 AND gt.layout_id = curr_layout_id
1290 AND gt.alias_definition_id = curr_alias_id
1291 AND gt.request_id = det.request_id
1292 AND hau.time_building_block_id = detail_bb_id
1293 AND hau.time_building_block_ovn = detail_bb_ovn
1294 AND ha.time_attribute_id = hau.time_attribute_id
1295 GROUP
1296 BY detail_bb_id,
1297 detail_bb_ovn ;';
1298
1299 dynamic_cursor2_head VARCHAR2(500);
1300 l_dynamic_cursor2_head VARCHAR2(500) :=
1301 ' CURSOR get_alias_values (';
1302
1303 dynamic_cursor2 VARCHAR2(2000);
1304 l_dynamic_cursor2 VARCHAR2(2000) :=
1305 ' ) IS
1306 SELECT alias_value_name
1307 FROM hxc_alias_values
1308 WHERE alias_definition_id = curr_alias_def
1309 AND ';
1310
1311
1312 dynamic_header VARCHAR2(2000);
1313 l_dynamic_header VARCHAR2(2000) :=
1314 'BEGIN
1315 DECLARE
1316 TYPE numtable IS TABLE OF NUMBER;
1317 TYPE varchartable IS TABLE OF VARCHAR2(200);
1318 det_bb_id_tab numtable;
1319 det_bb_ovn_tab numtable;
1320 ';
1321 alias_attribute VARCHAR2(30);
1322
1323 dynamic_core VARCHAR2(2000);
1324 l_dynamic_core VARCHAR2(2000) :=
1325 ' BEGIN
1326 OPEN get_alias_attributes;
1327 FETCH get_alias_attributes
1328 BULK COLLECT INTO det_bb_id_tab,
1329 det_bb_ovn_tab,
1330 ';
1331 dynamic_core2 VARCHAR2(2000);
1332 l_dynamic_core2 VARCHAR2(2000) :=
1333 ' alias_value;
1334 CLOSE get_alias_attributes;
1335 IF det_bb_id_tab.COUNT > 0
1336 THEN
1337 FOR i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1338 LOOP
1339 OPEN get_alias_values(';
1340
1341 dynamic_core3 VARCHAR2(2000);
1342 l_dynamic_core3 VARCHAR2(2000) :=
1343 ' FETCH get_alias_values INTO alias_value(i);
1344 CLOSE get_alias_values;
1345 END LOOP;
1346
1347 FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1348 UPDATE hxc_rpt_tc_details_all
1349 set attributeATTR_COL = ''ALIASNAME: ''||alias_value(i)
1350 WHERE detail_bb_id = det_bb_id_tab(i)
1351 AND detail_bb_ovn = det_bb_ovn_tab(i);
1352 END IF;
1353 END;
1354 END;
1355 ';
1356
1357 dynamic_query LONG;
1358
1359 l_attr_col NUMBER;
1360 l_alias_name VARCHAR2(30);
1361
1362
1363 -- INITIALIZE_DYNAMIC_VARIABLES
1364 -- The dynamic pl/sql block bits and pieces are constant variables -- you cant
1365 -- alter them each time this function is accessed, because they have to be
1366 -- reused. The constant variables are all having 'l_' prefixed and the real
1367 -- dynamic strings are all equated to these constant variables at the start of
1368 -- the parent procedure.
1369
1370 PROCEDURE initialize_dynamic_variables
1371 AS
1372 BEGIN
1373
1374 -- Private Procedure initialize_dynamic_variables
1375 -- Initializes all dynamic variables with the constant values, each time
1376 -- translate_aliases is called.
1377
1378 IF g_debug
1379 THEN
1380 hr_utility.trace('initialize_dynamic_variables');
1381 END IF;
1382
1383 dynamic_cursor := l_dynamic_cursor;
1384 dynamic_where := l_dynamic_where;
1385 dynamic_cursor2_head := l_dynamic_cursor2_head;
1386 dynamic_cursor2 := l_dynamic_cursor2;
1387 dynamic_header := l_dynamic_header;
1388 dynamic_core := l_dynamic_core;
1389 dynamic_core2 := l_dynamic_core2;
1390 dynamic_core3 := l_dynamic_core3;
1391 END initialize_dynamic_variables;
1392
1393
1394 BEGIN
1395
1396
1397 -- Public Procedure translate_aliases
1398 -- Like Populate_attributes, this is also very much processing intensive.
1399 -- Creates a dynamic pl/sql block from the sql string bits and pieces,
1400 -- depending on the alias definition ids and executes the same
1401 -- loading the translated alias values into HXC_RPT_TC_DETAILS_ALL.
1402 -- Pasted below is a sample Pl/Sql block created in one of the test runs.
1403
1404 -- -----------------------------
1405 --
1406 -- BEGIN
1407 -- DECLARE
1408 -- TYPE numtable IS TABLE OF NUMBER;
1409 -- TYPE varchartable IS TABLE OF VARCHAR2(100);
1410 -- det_bb_id_tab numtable;
1411 -- det_bb_ovn_tab numtable;
1412 -- ATTRIBUTE1tab varchartable;
1413 -- ATTRIBUTE2tab varchartable;
1414 -- alias_value varchartable;
1415 -- CURSOR get_alias_attributes IS
1416 -- SELECT detail_bb_id,
1417 -- detail_bb_ovn,
1418 -- MAX(DECODE(bld_blk_info_type_id,1,LTRIM(ha.ATTRIBUTE_CATEGORY,'ELEMENT - '))),
1419 -- MAX(DECODE(bld_blk_info_type_id,201,ha.ATTRIBUTE1)),
1420 -- ' '
1421 -- FROM hxc_rpt_tc_details_all det,
1422 -- hxc_rpt_tc_resource_temp gt,
1423 -- hxc_time_attribute_usages hau,
1424 -- hxc_time_attributes ha
1425 -- WHERE gt.tc_bb_id = det.tc_bb_id
1426 -- AND gt.layout_id = 7
1427 -- AND gt.request_id = det.request_id
1428 -- AND hau.time_building_block_id = detail_bb_id
1429 -- AND hau.time_building_block_ovn = detail_bb_ovn
1430 -- AND ha.time_attribute_id = hau.time_attribute_id
1431 -- GROUP
1432 -- BY detail_bb_id,
1433 -- detail_bb_ovn ;
1434 --
1435 -- CURSOR get_alias_values (p_ATTRIBUTE1 VARCHAR2
1436 -- ,p_ATTRIBUTE2 VARCHAR2
1437 -- ) IS
1438 -- SELECT alias_value_name
1439 -- FROM hxc_alias_values
1440 -- WHERE alias_definition_id = 13546
1441 -- AND NVL(ATTRIBUTE1,'0') = NVL(p_ATTRIBUTE1,'0')
1442 -- AND NVL(ATTRIBUTE2,'0') = NVL(p_ATTRIBUTE2,'0')
1443 -- ;
1444 --
1445 -- BEGIN
1446 -- OPEN get_alias_attributes;
1447 -- FETCH get_alias_attributes
1448 -- BULK COLLECT INTO det_bb_id_tab,
1449 -- det_bb_ovn_tab,
1450 -- ATTRIBUTE1tab,
1451 -- ATTRIBUTE2tab,
1452 -- alias_value;
1453 -- CLOSE get_alias_attributes;
1454 -- IF det_bb_id_tab.COUNT > 0
1455 -- THEN
1456 -- FOR i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1457 -- LOOP
1458 -- OPEN get_alias_values(ATTRIBUTE1tab(i)
1459 -- ,ATTRIBUTE2tab(i)
1460 -- );
1461 -- FETCH get_alias_values INTO
1462 -- alias_value(i);
1463 -- CLOSE get_alias_values;
1464 -- END LOOP;
1465 -- FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1466 -- UPDATE hxc_rpt_tc_details_all
1467 -- set attribute2 = 'Hours Type : '||alias_value(i)
1468 -- WHERE detail_bb_id = det_bb_id_tab(i)
1469 -- AND detail_bb_ovn = det_bb_ovn_tab(i);
1470 -- END IF;
1471 -- END;
1472 -- END;
1473 --
1474
1475 -- From populate_attributes, p_alias_tab, a plsql table would have been populated
1476 -- if this current layout is having atleast one alias value associated.
1477 -- Browse thru that to find out the alias attribute ( attribute to be used in HXC_
1478 -- RPT_TC_DETAILS_ALL table.
1479 -- Find out the alias definition pref associated for all the resources, in this
1480 -- layout and attach them to the table.
1481 -- Open get_alias_columns, passing on this alias definition id, and get the columns
1482 -- to look at for the alias attributes in HXC_TIME_ATTRIBUTES.
1483 -- Attach this to the dynamic strings and do the formatting.
1484 -- Concatenate all the dynamic sql strings, and execute the pl/sql block.
1485
1486
1487
1488 IF g_debug
1489 THEN
1490 hr_utility.trace('translate_aliases');
1491 hr_utility.trace('Parameters ');
1492 hr_utility.trace('p_layout_id : '||p_layout_id);
1493 IF p_alias_tab.COUNT > 0
1494 THEN
1495 FOR i IN p_alias_tab.FIRST..p_alias_tab.LAST
1496 LOOP
1497 hr_utility.trace('Alias number '||i);
1498 hr_utility.trace('Layout : '||p_alias_tab(i).layout_id);
1499 hr_utility.trace('Name : '||p_alias_tab(i).alias_name);
1500 hr_utility.trace('Column : '||p_alias_tab(i).alias_column);
1501 END LOOP;
1502 END IF;
1503 END IF;
1504
1505 l_curr_layout := p_layout_id;
1506
1507 IF p_alias_tab.COUNT > 0
1508 THEN
1509 FOR i IN p_alias_tab.FIRST..p_alias_tab.LAST
1510 LOOP
1511
1512 IF g_debug
1513 THEN
1514 hr_utility.trace('Processing Alias No. '||i);
1515 hr_utility.trace('Alias Name :'||p_alias_tab(i).alias_name);
1516 END IF;
1517
1518 l_attr_col := p_alias_tab(i).alias_column;
1519 l_alias_name := p_alias_tab(i).alias_name;
1520
1521 UPDATE hxc_rpt_tc_resource_temp
1522 SET alias_definition_id = NVL( hxc_preference_evaluation.resource_preferences
1523 (resource_id,
1524 'TC_W_TCRD_ALIASES',
1525 i,
1526 tc_start_time),0)
1527 WHERE layout_id = l_curr_layout;
1528
1529 IF g_debug
1530 THEN
1531 hr_utility.trace('Updated alias definition ids for current list of resources ');
1532 END IF;
1533
1534 OPEN get_alias_defs(l_curr_layout);
1535 LOOP
1536 FETCH get_alias_defs
1537 INTO l_curr_alias;
1538 EXIT WHEN get_alias_defs%NOTFOUND;
1539
1540 IF g_debug
1541 THEN
1542 hr_utility.trace('Fetched from get_alias_defs ');
1543 END IF;
1544
1545
1546 initialize_dynamic_variables;
1547
1548 OPEN get_alias_columns(l_curr_alias);
1549 LOOP
1550 FETCH get_alias_columns
1551 INTO l_alias_column,
1552 alias_attribute;
1553 EXIT WHEN get_alias_columns%NOTFOUND;
1554
1555 IF g_debug
1556 THEN
1557 hr_utility.trace('Fetched from get_alias_columns ');
1558 END IF;
1559
1560 dynamic_cursor := dynamic_cursor||l_alias_column||',
1561 ';
1562 dynamic_header := dynamic_header||'
1563 '||alias_attribute||'tab varchartable;';
1564 dynamic_core := dynamic_core||alias_attribute||'tab,
1565 ';
1566 dynamic_core2 := dynamic_core2||alias_attribute||'tab(i)
1567 ,';
1568 dynamic_cursor2_head := dynamic_cursor2_head||'p_'||alias_attribute||' VARCHAR2
1569 ,';
1570 dynamic_cursor2 :=
1571 dynamic_cursor2||'NVL('||alias_attribute||',''0'')'||' = '||'NVL(p_'||alias_attribute||',''0'')
1572 AND ';
1573
1574 END LOOP;
1575 CLOSE get_alias_columns;
1576 dynamic_cursor := RTRIM(dynamic_cursor,',');
1577 dynamic_header := RTRIM(dynamic_header,',');
1578 dynamic_where := REPLACE(dynamic_where,'curr_layout_id',l_curr_layout);
1579 dynamic_where := REPLACE(dynamic_where,'curr_alias_id',l_curr_alias);
1580 dynamic_core2 := RTRIM(dynamic_core2,',');
1581 dynamic_core2 := dynamic_core2||');';
1582 dynamic_cursor2_head := RTRIM(dynamic_cursor2_head,',');
1583 dynamic_cursor2 := RTRIM(dynamic_cursor2,'AND ');
1584 dynamic_cursor2 := dynamic_cursor2||';';
1585 dynamic_cursor2 := REPLACE(dynamic_cursor2,'curr_alias_def',l_curr_alias);
1586 dynamic_core3 := REPLACE(dynamic_core3,'ATTR_COL',l_attr_col);
1587 dynamic_core3 := REPLACE(dynamic_core3,'ALIASNAME',l_alias_name);
1588 dynamic_query := dynamic_header||
1589 dynamic_cursor||
1590 dynamic_where||
1591 dynamic_cursor2_head||
1592 dynamic_cursor2||
1593 dynamic_core||
1594 dynamic_core2||
1595 dynamic_core3;
1596
1597 IF g_debug
1598 THEN
1599 hr_utility.trace('Dynamic Pl/Sql block created ');
1600 hr_utility.trace('-----------------------------');
1601 hr_utility.trace(' ');
1602 hr_utility.trace(dynamic_header);
1603 hr_utility.trace(dynamic_cursor);
1604 hr_utility.trace(dynamic_where);
1605 hr_utility.trace(dynamic_cursor2_head);
1606 hr_utility.trace(dynamic_cursor2);
1607 hr_utility.trace(dynamic_core);
1608 hr_utility.trace(dynamic_core2);
1609 hr_utility.trace(dynamic_core3);
1610 END IF;
1611
1612 BEGIN
1613
1614 EXECUTE IMMEDIATE dynamic_query;
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 hr_utility.trace('Execute Immediate in translate_aliases threw Sql Error :'
1619 ||SQLCODE);
1620 RAISE;
1621 END;
1622
1623 END LOOP;
1624 CLOSE get_alias_defs;
1625 END LOOP;
1626 END IF;
1627
1628 IF g_debug
1629 THEN
1630 hr_utility.trace('translate_aliases completed alright ');
1631 END IF;
1632
1633
1634 EXCEPTION
1635 WHEN NO_DATA_FOUND THEN
1636 hr_utility.trace('No Data Found in translate aliases, something wrong ');
1637
1638 END translate_aliases;
1639
1640
1641 -- TRANSLATE_CLA_REASONS
1642 -- Translates the CLA reasons captured earlier against the lookup values.
1643
1644
1645 PROCEDURE translate_cla_reasons
1646 AS
1647
1648 CURSOR get_cla_reasons
1649 IS SELECT flv.meaning,
1650 rtrim(substr(flv.lookup_type,5,6),'_A'),
1651 det.detail_bb_id,
1652 det.detail_bb_ovn
1653 FROM hxc_rpt_tc_details_all det,
1654 hxc_rpt_tc_resource_temp gt,
1655 fnd_lookup_values flv
1656 WHERE gt.request_id = det.request_id
1657 AND gt.resource_id = det.resource_id
1658 AND gt.tc_start_time = det.tc_start_time
1659 AND gt.tc_stop_time = det.tc_stop_time
1660 AND flv.lookup_code = det.cla_reason
1661 AND flv.language = userenv('LANG')
1662 AND flv.lookup_type IN ( 'HXC_CHANGE_AUDIT_REASONS',
1663 'HXC_LATE_AUDIT_REASONS')
1664 AND flv.view_application_id = 3
1665 AND flv.security_group_id = FND_GLOBAL.lookup_security_group(flv.lookup_type,
1666 flv.view_application_id);
1667
1668 l_meaning_tab VARCHARTABLE;
1669 l_type_tab VARCHARTABLE;
1670 l_det_bb_id_tab NUMTABLE;
1671 l_det_bb_ovn_tab NUMTABLE;
1672
1673
1674 BEGIN
1675
1676 -- Public Procedure translate_cla_reasons
1677 -- Fetch all the lookup codes for CLA reasons from HXC_RPT_TC_DETAILS_ALL
1678 -- wherever they exist.
1679 -- Fetch the corresponding Lookup names from FND_LOOKUP_VALUES.
1680 -- Update HXC_RPT_TC_DETAILS_ALL with the relevant lookup names
1681
1682 IF g_debug
1683 THEN
1684 hr_utility.trace('translate_cla_reasons');
1685 END IF;
1686
1687
1688 OPEN get_cla_reasons;
1689 FETCH get_cla_reasons BULK COLLECT INTO l_meaning_tab,
1690 l_type_tab,
1691 l_det_bb_id_tab,
1692 l_det_bb_ovn_tab ;
1693
1694 CLOSE get_cla_reasons;
1695
1696 IF g_debug
1697 THEN
1698 hr_utility.trace('Fetched from get_cla_reasons ');
1699 hr_utility.trace('Total Number of rows : '||l_meaning_tab.COUNT);
1700 END IF;
1701
1702 IF l_meaning_tab.COUNT > 0
1703 THEN
1704 FORALL i IN l_meaning_tab.FIRST..l_meaning_tab.LAST
1705 UPDATE hxc_rpt_tc_details_all
1706 SET cla_reason = l_meaning_tab(i)
1707 WHERE detail_bb_id = l_det_bb_id_tab(i)
1708 AND detail_bb_ovn = l_det_bb_ovn_tab(i)
1709 AND cla_type = l_type_tab(i) ;
1710
1711 l_meaning_tab.DELETE;
1712 l_det_bb_id_tab.DELETE;
1713 l_det_bb_ovn_tab.DELETE;
1714 l_type_tab.DELETE;
1715
1716 END IF;
1717
1718 IF g_debug
1719 THEN
1720 hr_utility.trace('translate_cla_reasons completed alright');
1721 END IF;
1722
1723
1724 EXCEPTION
1725 WHEN NO_DATA_FOUND THEN
1726 IF g_debug
1727 THEN
1728 hr_utility.trace('No Data Found from translate_cla_reasons');
1729 END IF;
1730
1731 END translate_cla_reasons;
1732
1733
1734
1735 -- UPDATE_TRANSACTION_IDS
1736 -- Finds out the relevant transaction_ids and transaction_detail_ids for the
1737 -- chosen detail records from HXC_RPT_TC_DETAILS_ALL.
1738
1739 PROCEDURE update_transaction_ids(p_record_save IN VARCHAR2)
1740 AS
1741
1742 -- Bug 8888812
1743 -- Calling this cursor with a diff name now, because
1744 -- we no longer store DEPOSIT transactions in these tables.
1745 CURSOR get_old_transaction_details
1746 IS SELECT /*+ INDEX(det HXC_RPT_TC_DETAILS_FK1) */
1747 htd.transaction_id,
1748 htd.transaction_detail_id,
1749 det.detail_bb_id,
1750 det.detail_bb_ovn
1751 FROM hxc_rpt_tc_details_all det,
1752 hxc_rpt_tc_resource_temp gt,
1753 hxc_transaction_details htd,
1754 hxc_transactions ht
1755 WHERE gt.tc_start_time = det.tc_start_time
1756 AND gt.tc_stop_time = det.tc_stop_time
1757 AND gt.resource_id = det.resource_id
1758 AND det.detail_bb_id = htd.time_building_block_id
1759 AND det.detail_bb_ovn = htd.time_building_block_ovn
1760 AND htd.transaction_id = ht.transaction_id
1761 AND det.transaction_id IS NULL
1762 AND ht.type = 'DEPOSIT'
1763 AND ht.status = 'SUCCESS'
1764 AND htd.status = 'SUCCESS';
1765
1766 -- Bug 8888812
1767 -- New cursor written to pick up records from
1768 -- the new set of tables.
1769 CURSOR get_transaction_details
1770 IS SELECT /*+ INDEX(det HXC_RPT_TC_DETAILS_FK1) */
1771 htd.transaction_id,
1772 htd.transaction_detail_id,
1773 det.detail_bb_id,
1774 det.detail_bb_ovn
1775 FROM hxc_rpt_tc_details_all det,
1776 hxc_rpt_tc_resource_temp gt,
1777 hxc_dep_transaction_details htd,
1778 hxc_dep_transactions ht
1779 WHERE gt.tc_start_time = det.tc_start_time
1780 AND gt.tc_stop_time = det.tc_stop_time
1781 AND gt.resource_id = det.resource_id
1782 AND det.detail_bb_id = htd.time_building_block_id
1783 AND det.detail_bb_ovn = htd.time_building_block_ovn
1784 AND htd.transaction_id = ht.transaction_id
1785 AND ht.type = 'DEPOSIT'
1786 AND ht.status = 'SUCCESS'
1787 AND htd.status = 'SUCCESS';
1788
1789
1790
1791 --
1792 --
1793 -- The following complex cursor pulls out the records from
1794 -- hxc_rpt_tc_details_all, grouped by creation_date.
1795 --
1796 -- After the update above, the records would have transaction_id
1797 -- populated if they belong to a SUBMIT action. The inner query
1798 -- with DENSE_RANK would pull out these records with a
1799 -- Dense Rank -- Rank with consecutive values. For details on
1800 -- how this works, check the Oracle 10g documentation.
1801 -- Ranks are partitioned by resource_id, start_time, and stop_time
1802 -- and are ordered by creation date.
1803 --
1804 -- The outer query would pull out the distinct records (ie.grouped by
1805 -- dense rank, creation_date, and PERCENT_RANK. Percent rank works
1806 -- the same way as RANK, but gives a value between 0 and 1 for the
1807 -- Ranks. This percent rank is the decimal factor to be added to
1808 -- the transaction_id to generate the pseudo transaction_id
1809 -- which is a decimal between the preceeding and succeeding
1810 -- transaction_ids.
1811 --
1812 --
1813
1814
1815 CURSOR get_working_trans
1816 IS SELECT DISTINCT creation_date,
1817 transaction_id,
1818 resource_id,
1819 tc_start_time,
1820 tc_stop_time,
1821 dense,
1822 ROUND(PERCENT_RANK() OVER(PARTITION BY resource_id,
1823 tc_start_time,
1824 tc_stop_time
1825 ORDER BY creation_date),5)
1826 FROM ( SELECT creation_date,
1827 transaction_id,
1828 DENSE_RANK() OVER(PARTITION BY det.resource_id,
1829 det.tc_start_time,
1830 det.tc_stop_time
1831 ORDER BY creation_date) dense,
1832 det.resource_id,
1833 det.tc_start_time,
1834 det.tc_stop_time
1835 FROM hxc_rpt_tc_details_all det,
1836 hxc_rpt_tc_resource_temp temp
1837 WHERE temp.resource_id = det.resource_id
1838 AND temp.tc_start_time = det.tc_start_time
1839 AND temp.tc_stop_time = det.tc_stop_time
1840 )
1841 ORDER BY resource_id,
1842 tc_start_time,
1843 tc_stop_time,
1844 dense ;
1845
1846
1847
1848 det_bb_id_tab NUMTABLE;
1849 det_bb_ovn_tab NUMTABLE;
1850 det_trans_id_tab NUMTABLE;
1851 det_trans_detail_id_tab NUMTABLE;
1852
1853 res_id_tab NUMTABLE;
1854 start_timetab DATETABLE;
1855 stop_timetab DATETABLE;
1856 creation_tab DATETABLE;
1857 trans_tab FLOATTABLE;
1858 fac_tab FLOATTABLE;
1859 densetab NUMTABLE;
1860
1861
1862
1863 BEGIN
1864
1865 -- Public Procedure update_transaction_ids
1866 -- Joins HXC_RPT_TC_DETAILS_ALL against HXC_TRANSACTION_DETAILS,
1867 -- and HXC_TRANSACTIONS to pick up all the transactions and
1868 -- transaction_details for a successful deposit.
1869 -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
1870 -- If p_record_save is set to Y, we need to give pseudo transaction_ids
1871 -- to the working status timecards. Do it in the following way.
1872
1873 -- Note: This needs to be carried out only for Self Service time entry.
1874 -- Timekeeper time entries create transaction records even for
1875 -- Working status timecards. Such timecards wont be affected
1876 -- at all because the FORALL update works on those records with
1877 -- transaction_id as NULL.
1878 --
1879 -- Eg. The timecard has been acted upon multiple ways in the following
1880 -- way. Actions in Initcaps are timecard actions, and those are the ones
1881 -- to look for transaction id in.
1882 --
1883 -- Action Transaction_id
1884 -- ======= ================
1885 -- entered
1886 -- Saved NULL
1887 -- edited
1888 -- Saved NULL
1889 -- edited
1890 -- Submitted 234
1891 -- Deleted 335
1892 -- entered again.
1893 -- Saved NULL
1894 -- edited
1895 -- Saved NULL
1896 -- Submitted 436
1897 --
1898 -- If you observe the above table, all actions except Save creates transaction
1899 -- records in hxc_transactions, and have a transaction_id.
1900 --
1901 -- We need to populate some pseudo transaction_id to the Save actions, and this
1902 -- can be done in the following way.
1903 --
1904 -- entered
1905 -- Saved 1
1906 -- edited
1907 -- Saved 1.1
1908 -- edited
1909 -- Submitted 234
1910 -- Deleted 335
1911 -- entered again.
1912 -- Saved 335.1
1913 -- edited
1914 -- Saved 335.2
1915 -- Submitted 436
1916 --
1917 -- Here we are settling for a decimal value between the previous and next
1918 -- valid transaction_ids ordered by the sequence of action. These pseudo
1919 -- transactions would be generated by the logic that follows.
1920 --
1921 -- The following sample data is the output of the cursor get_working_trans
1922 -- for a sequence of save, save, save, submit, save, submit.
1923
1924
1925 -- 10/22/2008 4:49:14 AM 8110 1/7/2008 1/13/2008 11:59:59 PM 1 0
1926 -- 10/22/2008 4:50:09 AM 8110 1/7/2008 1/13/2008 11:59:59 PM 2 0.09091
1927 -- 10/22/2008 4:50:38 AM 8110 1/7/2008 1/13/2008 11:59:59 PM 3 0.22727
1928 -- 10/22/2008 4:51:12 AM 196362 8110 1/7/2008 1/13/2008 11:59:59 PM 4 0.27273
1929 -- 10/22/2008 6:39:21 AM 8110 1/7/2008 1/13/2008 11:59:59 PM 5 0.5
1930 -- 10/22/2008 6:40:05 AM 196366 8110 1/7/2008 1/13/2008 11:59:59 PM 6 0.77273
1931 --
1932 --
1933 -- Note that transaction_id is populated only for the submit actions.
1934 -- The logic that follows would populate 1 for the first record.
1935 -- The FORALL update below would update transaction_id plus the 'dense'
1936 -- value from the cursor( the decimal column -- the last one ) as the pseudo
1937 -- transaction id.
1938 --
1939 -- After the update the data would look like this. Note that only those records
1940 -- with transaction_id as NULL would have the pseudo values populated.
1941 --
1942 -- 10/22/2008 4:49:14 AM 1 8110 1/7/2008 1/13/2008 11:59:59 PM 1 0
1943 -- 10/22/2008 4:50:09 AM 1.09091 8110 1/7/2008 1/13/2008 11:59:59 PM 2 0.09091
1944 -- 10/22/2008 4:50:38 AM 1.22727 8110 1/7/2008 1/13/2008 11:59:59 PM 3 0.22727
1945 -- 10/22/2008 4:51:12 AM 196362 8110 1/7/2008 1/13/2008 11:59:59 PM 4 0.27273
1946 -- 10/22/2008 6:39:21 AM 196362.5 8110 1/7/2008 1/13/2008 11:59:59 PM 5 0.5
1947 -- 10/22/2008 6:40:05 AM 196366 8110 1/7/2008 1/13/2008 11:59:59 PM 6 0.77273
1948
1949
1950
1951 IF g_debug
1952 THEN
1953 hr_utility.trace('update_transaction_ids');
1954 END IF;
1955
1956 OPEN get_transaction_details;
1957
1958 FETCH get_transaction_details
1959 BULK COLLECT INTO det_trans_id_tab,
1960 det_trans_detail_id_tab,
1961 det_bb_id_tab,
1962 det_bb_ovn_tab;
1963
1964 CLOSE get_transaction_details;
1965
1966 IF g_debug
1967 THEN
1968 hr_utility.trace('Fetched from get_transaction_details ');
1969 hr_utility.trace('Total Number of rows : '||det_trans_id_tab.COUNT);
1970 END IF;
1971
1972 IF det_bb_id_tab.COUNT > 0
1973 THEN
1974 FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
1975 UPDATE hxc_rpt_tc_details_all
1976 SET transaction_id = det_trans_id_tab(i),
1977 transaction_detail_id = det_trans_detail_id_tab(i)
1978 WHERE detail_bb_id = det_bb_id_tab(i)
1979 AND detail_bb_ovn = det_bb_ovn_tab(i);
1980
1981
1982 det_bb_id_tab.DELETE;
1983 det_bb_ovn_tab.DELETE;
1984 det_trans_id_tab.DELETE;
1985 det_trans_detail_id_tab.DELETE;
1986 END IF;
1987
1988 -- Bug 8888812
1989 -- In case the restructuring upgrade is not complete,
1990 -- you may have some old timecards with the old structure.
1991 -- Pick them up and do the same procedure as above.
1992 IF NOT hxc_upgrade_pkg.txn_upgrade_completed
1993 THEN
1994 OPEN get_old_transaction_details;
1995
1996 FETCH get_old_transaction_details
1997 BULK COLLECT INTO det_trans_id_tab,
1998 det_trans_detail_id_tab,
1999 det_bb_id_tab,
2000 det_bb_ovn_tab;
2001
2002 CLOSE get_old_transaction_details;
2003
2004 IF g_debug
2005 THEN
2006 hr_utility.trace('Fetched from get_transaction_details ');
2007 hr_utility.trace('Total Number of rows : '||det_trans_id_tab.COUNT);
2008 END IF;
2009
2010 IF det_bb_id_tab.COUNT > 0
2011 THEN
2012 FORALL i IN det_bb_id_tab.FIRST..det_bb_id_tab.LAST
2013 UPDATE hxc_rpt_tc_details_all
2014 SET transaction_id = det_trans_id_tab(i),
2015 transaction_detail_id = det_trans_detail_id_tab(i)
2016 WHERE detail_bb_id = det_bb_id_tab(i)
2017 AND detail_bb_ovn = det_bb_ovn_tab(i);
2018
2019
2020 det_bb_id_tab.DELETE;
2021 det_bb_ovn_tab.DELETE;
2022 det_trans_id_tab.DELETE;
2023 det_trans_detail_id_tab.DELETE;
2024 END IF;
2025 END IF;
2026
2027
2028 -- If record_save option is Yes
2029 IF p_record_save = 'Y'
2030 THEN
2031 -- Get the cursor to pick out the transaction_ids
2032 -- and the decimal factor to be added to the transaction_ids
2033 -- to generate the pseudo transactions.
2034
2035 OPEN get_working_trans;
2036
2037 FETCH get_working_trans
2038 BULK COLLECT INTO creation_tab,
2039 trans_tab,
2040 res_id_tab,
2041 start_timetab,
2042 stop_timetab,
2043 densetab,
2044 fac_tab;
2045
2046
2047 CLOSE get_working_trans;
2048
2049 -- If the first transaction is NULL, assign 1 to it.
2050
2051 -- Bug 7707609
2052 -- If any other transaction is NULL, assign the previous one to it,
2053 -- if the factor obtained from above query is not zero.
2054 -- If it is zero, it means that this is the first ever transaction
2055 -- for the given timecard.
2056
2057 IF trans_tab.COUNT > 0
2058 THEN
2059 FOR i IN trans_tab.FIRST..trans_tab.LAST
2060 LOOP
2061 IF trans_tab(i) IS NULL
2062 THEN
2063 IF ( NOT trans_tab.EXISTS(i-1) ) -- For the first record
2064 OR ( fac_tab(i) = 0 ) -- For the first record for each timecard.
2065 THEN
2066 trans_tab(i) := 1;
2067 ELSE
2068 trans_tab(i) := trans_tab(i-1);
2069 END IF;
2070 END IF;
2071 END LOOP;
2072 END IF;
2073
2074
2075 -- This is to take care of large timecards created using templates.
2076 -- For such timecards, all the details would not have the same
2077 -- creation_date -- may differ by one second or two seconds.
2078 -- This may be a bug in Time Store, but this would make the
2079 -- transactions look like two Save operations, because they have
2080 -- different creation_dates and would be ranked differently.
2081 -- For these guys, equate the decimal factor of the latest
2082 -- one to the earlier ones, so that only one Save comes up.
2083
2084 IF trans_tab.COUNT > 0
2085 THEN
2086 FOR i IN trans_tab.FIRST..trans_tab.LAST
2087 LOOP
2088 IF trans_tab.EXISTS(i+1)
2089 THEN
2090 IF trans_tab(i) = trans_tab(i+1)
2091 AND ((creation_tab(i+1) - creation_tab(i))*24*60*60) <=2
2092 THEN
2093 fac_tab(i) := fac_tab(i+1);
2094 END IF;
2095 END IF;
2096 END LOOP;
2097 END IF;
2098
2099
2100
2101 IF creation_tab.COUNT > 0
2102 THEN
2103 FORALL i IN creation_tab.FIRST..creation_tab.LAST
2104 UPDATE hxc_rpt_tc_details_all
2105 SET transaction_id = trans_tab(i)+fac_tab(i)
2106 WHERE resource_id = res_id_tab(i)
2107 AND tc_start_time = start_timetab(i)
2108 AND tc_stop_time = stop_timetab(i)
2109 AND creation_date = creation_tab(i)
2110 AND transaction_id IS NULL;
2111
2112
2113 res_id_tab.DELETE;
2114 start_timetab.DELETE;
2115 stop_timetab.DELETE;
2116 creation_tab.DELETE;
2117 trans_tab.DELETE;
2118 END IF;
2119
2120 END IF;
2121
2122
2123 IF g_debug
2124 THEN
2125 hr_utility.trace('update_transaction_ids completed alright');
2126 END IF;
2127
2128
2129 EXCEPTION
2130 WHEN NO_DATA_FOUND THEN
2131 IF g_debug
2132 THEN
2133 hr_utility.trace('No Data Found from update_transaction_ids');
2134 END IF;
2135
2136 END update_transaction_ids;
2137
2138
2139 -- TRANSLATE_CREATED_BY
2140 -- Translates the created by user ids to "user_name(resource_name)" format.
2141
2142 PROCEDURE translate_created_by
2143 AS
2144
2145 CURSOR get_created_user ( p_request_id VARCHAR2 )
2146 IS SELECT /*+ ORDERED */
2147 det.detail_bb_id,
2148 det.detail_bb_ovn,
2149 fnd.user_name||newline||'['||
2150 ppf.full_name||']'
2151 FROM hxc_rpt_tc_resource_temp gt,
2152 hxc_rpt_tc_details_all det,
2153 fnd_user fnd,
2154 per_all_people_f ppf
2155 WHERE gt.tc_bb_id = det.tc_bb_id
2156 AND gt.request_id = p_request_id
2157 AND det.created_by = fnd.user_id
2158 AND fnd.employee_id = ppf.person_id
2159 AND det.day_start_time BETWEEN ppf.effective_start_date
2160 AND ppf.effective_end_date
2161 AND det.created_by_user IS NULL ;
2162
2163 l_bb_id_tab numtable;
2164 l_bb_ovn_tab numtable;
2165 l_person_tab varchartable;
2166
2167 BEGIN
2168
2169
2170 -- Public Procedure translate_created_by
2171 -- Find out user_name for the corresponding user_ids from FND_USER
2172 -- and full_name from PER_ALL_PEOPLE_F for the employee_ids from
2173 -- FND_USER.
2174 -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
2175
2176 IF g_debug
2177 THEN
2178 hr_utility.trace('translate_created_by');
2179 END IF;
2180
2181
2182 OPEN get_created_user(g_request_id);
2183
2184 FETCH get_created_user BULK COLLECT INTO l_bb_id_tab,
2185 l_bb_ovn_tab,
2186 l_person_tab ;
2187
2188 CLOSE get_created_user;
2189
2190 IF g_debug
2191 THEN
2192 hr_utility.trace('Fetched from get_created_user ');
2193 hr_utility.trace('Total number of rows fetched : '||l_bb_id_tab.COUNT);
2194 END IF;
2195
2196 IF l_bb_id_tab.COUNT > 0
2197 THEN
2198 FORALL i IN l_bb_id_tab.FIRST..l_bb_id_tab.LAST
2199 UPDATE hxc_rpt_tc_details_all
2200 SET created_by_user = l_person_tab(i)
2201 WHERE detail_bb_id = l_bb_id_tab(i)
2202 AND detail_bb_ovn = l_bb_ovn_tab(i);
2203
2204 l_bb_id_tab.DELETE;
2205 l_bb_ovn_tab.DELETE;
2206 l_person_tab.DELETE;
2207
2208 END IF;
2209
2210 IF g_debug
2211 THEN
2212 hr_utility.trace('translate_created_by completed alright');
2213 END IF;
2214
2215
2216 EXCEPTION
2217 WHEN NO_DATA_FOUND THEN
2218 hr_utility.trace('No Data Found from translate_created_by, something wrong');
2219
2220
2221 END translate_created_by;
2222
2223
2224
2225 -- TRANSLATE_LAST_UPDATED_BY
2226 -- Translates the created by user ids to "user_name(resource_name)" format.
2227
2228 PROCEDURE translate_last_updated_by
2229 AS
2230
2231 CURSOR get_updated_user ( p_request_id VARCHAR2)
2232 IS SELECT /*+ ORDERED */
2233 det.detail_bb_id,
2234 det.detail_bb_ovn,
2235 fnd.user_name||newline||'['||
2236 ppf.full_name||']'
2237 FROM hxc_rpt_tc_resource_temp gt,
2238 hxc_rpt_tc_details_all det,
2239 fnd_user fnd,
2240 per_all_people_f ppf
2241 WHERE gt.tc_bb_id = det.tc_bb_id
2242 AND gt.request_id = p_request_id
2243 AND det.last_updated_by = fnd.user_id
2244 AND fnd.employee_id = ppf.person_id
2245 AND det.day_start_time BETWEEN ppf.effective_start_date
2246 AND ppf.effective_end_date
2247 AND det.last_updated_by_user IS NULL ;
2248
2249 l_bb_id_tab NUMTABLE;
2250 l_bb_ovn_tab NUMTABLE;
2251 l_person_tab VARCHARTABLE;
2252
2253 BEGIN
2254
2255 -- Public Procedure translate_last_updated_by
2256 -- Find out user_name for the corresponding user_ids from FND_USER
2257 -- and full_name from PER_ALL_PEOPLE_F for the employee_ids from
2258 -- FND_USER.
2259 -- Update HXC_RPT_TC_DETAILS_ALL with the corresponding values.
2260
2261
2262 IF g_debug
2263 THEN
2264 hr_utility.trace('translate_last_updated_by');
2265 END IF;
2266
2267
2268
2269 OPEN get_updated_user(g_request_id);
2270
2271 FETCH get_updated_user BULK COLLECT INTO l_bb_id_tab,
2272 l_bb_ovn_tab,
2273 l_person_tab ;
2274
2275 CLOSE get_updated_user;
2276
2277
2278 IF g_debug
2279 THEN
2280 hr_utility.trace('Fetched from get_updated_user ');
2281 hr_utility.trace('Total number of rows fetched : '||l_bb_id_tab.COUNT);
2282 END IF;
2283
2284
2285 IF l_bb_id_tab.COUNT > 0
2286 THEN
2287 FORALL i IN l_bb_id_tab.FIRST..l_bb_id_tab.LAST
2288 UPDATE hxc_rpt_tc_details_all
2289 SET last_updated_by_user = l_person_tab(i)
2290 WHERE detail_bb_id = l_bb_id_tab(i)
2291 AND detail_bb_ovn = l_bb_ovn_tab(i);
2292
2293 l_bb_id_tab.DELETE;
2294 l_bb_ovn_tab.DELETE;
2295 l_person_tab.DELETE;
2296
2297 END IF;
2298
2299
2300 IF g_debug
2301 THEN
2302 hr_utility.trace('translate_last_updated_by completed alright');
2303 END IF;
2304
2305
2306 EXCEPTION
2307 WHEN NO_DATA_FOUND THEN
2308 hr_utility.trace('No Data Found from translate_last_updated_by, something wrong');
2309
2310 END translate_last_updated_by;
2311
2312
2313
2314 -- LOG_TIME_CAPTURE
2315 -- Makes an entry in HXC_RPT_TC_HIST_LOG with the timecard identification
2316 -- parameters, for future reference.
2317
2318 PROCEDURE log_time_capture ( p_request_id IN VARCHAR2,
2319 p_request_sysdate IN DATE )
2320 AS
2321
2322 resource_id_tab NUMTABLE;
2323 start_time_tab DATETABLE;
2324 stop_time_tab DATETABLE;
2325
2326 BEGIN
2327
2328 -- Public Procedure log_time_capture
2329 -- If the timecard is already recorded in HXC_RPT_TC_HIST_LOG, update the
2330 -- history_till_date column with the request date.
2331 -- Delete from HXC_RPT_TC_RESOURCE_TEMP all records which are updated as above.
2332 -- For all the distinct timecard records existing in HXC_RPT_TC_RESOURCE_TEMP,
2333 -- insert a relevant record into HXC_RPT_TC_HIST_LOG.
2334
2335 IF g_debug
2336 THEN
2337 hr_utility.trace('log_time_capture');
2338 END IF;
2339
2340
2341 UPDATE hxc_rpt_tc_hist_log
2342 SET request_id = p_request_id,
2343 history_till_date = p_request_sysdate
2344 WHERE (resource_id,
2345 tc_start_time,
2346 tc_stop_time)
2347 IN ( SELECT resource_id,
2348 tc_start_time,
2349 tc_stop_time
2350 FROM hxc_rpt_tc_resource_temp )
2351 RETURNING resource_id,
2352 tc_start_time,
2353 tc_stop_time BULK COLLECT INTO resource_id_tab,
2354 start_time_tab,
2355 stop_time_tab ;
2356
2357 IF g_debug
2358 THEN
2359 hr_utility.trace('Updated hxc_rpt_tc_hist_log ');
2360 hr_utility.trace('Total Number of timecards updated here : '||resource_id_tab.COUNT);
2361 END IF;
2362
2363 IF resource_id_tab.COUNT > 0
2364 THEN
2365 FORALL i IN resource_id_tab.FIRST..resource_id_tab.LAST
2366 DELETE FROM hxc_rpt_tc_resource_temp
2367 WHERE resource_id = resource_id_tab(i)
2368 AND tc_start_time = start_time_tab(i)
2369 AND tc_stop_time = stop_time_tab(i);
2370
2371 END IF;
2372
2373 INSERT INTO hxc_rpt_tc_hist_log
2374 ( resource_id,
2375 tc_start_time,
2376 tc_stop_time,
2377 request_id,
2378 history_till_date )
2379 SELECT resource_id,
2380 tc_start_time,
2381 tc_stop_time,
2382 MIN(p_request_id),
2383 MIN(p_request_sysdate)
2384 FROM hxc_rpt_tc_resource_temp
2385 GROUP BY resource_id,
2386 tc_start_time,
2387 tc_stop_time;
2388
2389
2390 resource_id_tab.DELETE;
2391 start_time_tab.DELETE;
2392 stop_time_tab.DELETE;
2393
2394
2395 IF g_debug
2396 THEN
2397 hr_utility.trace('log_time_capture completed alright');
2398 END IF;
2399
2400 EXCEPTION
2401 WHEN NO_DATA_FOUND THEN
2402 IF g_debug
2403 THEN
2404 hr_utility.trace('No Data Found from log_time_capture ');
2405 END IF;
2406
2407 END log_time_capture;
2408
2409
2410
2411 -- LOAD_TC_SNAPSHOT
2412 -- Main action block for Load Timecard Snapshot Concurrent Program.
2413
2414
2415 PROCEDURE load_tc_snapshot ( errbuf OUT NOCOPY VARCHAR2 ,
2416 retcode OUT NOCOPY NUMBER ,
2417 p_date_from IN VARCHAR2 ,
2418 p_date_to IN VARCHAR2 ,
2419 p_data_regen IN VARCHAR2 ,
2420 p_record_save IN VARCHAR2 ,
2421 p_org_id IN NUMBER DEFAULT NULL,
2422 p_locn_id IN NUMBER DEFAULT NULL,
2423 p_payroll_id IN NUMBER DEFAULT NULL,
2424 p_supervisor_id IN NUMBER DEFAULT NULL,
2425 p_person_id IN NUMBER DEFAULT NULL ) AS
2426
2427
2428 CURSOR get_layout_ids
2429 IS SELECT DISTINCT layout_id
2430 FROM hxc_rpt_tc_resource_temp;
2431
2432 l_layout_id NUMBER(15);
2433 l_alias_tab ALIASTAB;
2434 l_where_clause VARCHAR2(6000);
2435 timecard_exists NUMBER := 0;
2436
2437 BEGIN
2438
2439
2440 -- Public Procedure load_tc_snapshot
2441 -- Get the relevant time filter parameters.
2442 -- Get the request id and sysdates.
2443 -- Pass on the parameters to create the resources list ( comma separated, groups
2444 -- of 20).
2445 -- If chosen to delete history data and recreate, delete all info from
2446 -- HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
2447 -- For each valid list of resources picked, execute load_tc_level_info.
2448 -- Execute fetch_history_from_date to get the date from which history has to
2449 -- be considered for each timecard.
2450 -- If chosen to reuse history data, execute update_last_touched_date.
2451 -- Update the layout_ids for the timecards.
2452 -- Load the detail and day level info to HXC_RPT_TC_DETAILS_ALL.
2453 -- Update the timecard comments.
2454 -- Loop thru all the distinct layout_ids picked out.
2455 -- For each layout id
2456 -- * Populate the attributes for the details
2457 -- * Translate the attributes to user readable format.
2458 -- * Translate the alias values, if any.
2459 -- Translate CLA reasons and type, if any.
2460 -- Update transaction_ids for the records.
2461 -- Translate the created by user_ids to user_names and employee names.
2462 -- Translate the last updated by user_ids to user_names and employee names.
2463 -- Log the time capture for each timecard records in HXC_RPT_TC_HIST_LOG for
2464 -- future reference.
2465 -- Clear HXC_RPT_TC_RESOURCE_TEMP, for the next iteration ( next 20 resources ).
2466
2467 g_request_sysdate := SYSDATE;
2468 g_request_id := FND_GLOBAL.CONC_PRIORITY_REQUEST;
2469
2470 IF g_debug
2471 THEN
2472 hr_utility.trace('Load Timecard Snapshot ');
2473 hr_utility.trace('Parameters');
2474 hr_utility.trace('==========');
2475 hr_utility.trace('p_date_from '||p_date_from);
2476 hr_utility.trace('p_date_to '||p_date_to);
2477 hr_utility.trace('p_data_regen '||p_data_regen);
2478 hr_utility.trace('p_record_save '||p_record_save);
2479 hr_utility.trace('p_org_id '||p_org_id);
2480 hr_utility.trace('p_locn_id '||p_locn_id);
2481 hr_utility.trace('p_payroll_id '||p_payroll_id);
2482 hr_utility.trace('p_supervisor_id '||p_supervisor_id);
2483 hr_utility.trace('p_person_id '||p_person_id);
2484 hr_utility.trace('Priority Request Id '||g_request_id);
2485 hr_utility.trace('Request starts execution at '||
2486 TO_CHAR(g_request_sysdate,'dd-MON-yyyy HH:MI:SS'));
2487 END IF;
2488
2489 resource_where_clause ( p_date_from => fnd_date.canonical_to_date(p_date_from)
2490 , p_date_to => fnd_date.canonical_to_date(p_date_to)
2491 , p_org_id => p_org_id
2492 , p_locn_id => p_locn_id
2493 , p_payroll_id => p_payroll_id
2494 , p_supervisor_id => p_supervisor_id
2495 , p_person_id => p_person_id
2496 );
2497
2498 IF p_data_regen = 'Y'
2499 THEN
2500 IF g_debug
2501 THEN
2502 hr_utility.trace('Clearing history...');
2503 END IF;
2504 clear_history_data;
2505 END IF;
2506
2507
2508 DELETE FROM hxc_rpt_tc_resource_temp;
2509
2510 IF g_debug
2511 THEN
2512 hr_utility.trace('Cleared hxc_rpt_tc_resource_temp, just in case the last run crashed');
2513 hr_utility.trace(SQLCODE);
2514 END IF;
2515
2516
2517 FOR i in g_res_list_cs.FIRST..g_res_list_cs.LAST
2518 LOOP
2519 -- Bug 9137834
2520 -- Added the Exception block for list of resources.
2521 BEGIN
2522 IF (g_res_list_cs(i) IS NOT NULL) AND (g_res_list_cs(i) <> ' ')
2523 THEN
2524 IF g_debug
2525 THEN
2526 hr_utility.trace('Processing resource list '||i);
2527 hr_utility.trace(g_res_list_cs(i));
2528 END IF;
2529 load_tc_level_info ( p_resource_list => ltrim(g_res_list_cs(i),' ,'),
2530 p_tc_from => fnd_date.canonical_to_date(p_date_from),
2531 p_tc_to => fnd_date.canonical_to_date(p_date_to),
2532 p_request_id => g_request_id);
2533
2534
2535 SELECT count(*)
2536 INTO timecard_exists
2537 FROM hxc_rpt_tc_resource_temp
2538 WHERE rownum < 2;
2539
2540 IF timecard_exists = 0
2541 THEN
2542 IF g_debug
2543 THEN
2544 hr_utility.trace('No timecards exist for this resource list ');
2545 hr_utility.trace(g_res_list_cs(i));
2546 END IF;
2547
2548 ELSE
2549
2550 fetch_history_from_date;
2551
2552 IF p_data_regen <> 'Y'
2553 THEN
2554 IF g_debug
2555 THEN
2556 hr_utility.trace('Using history data, so update Last Touched Dates');
2557 END IF;
2558 update_last_touched_date;
2559 END IF;
2560
2561 update_layout_ids;
2562
2563 load_detail_info(p_request_sysdate => g_request_sysdate);
2564
2565 update_timecard_comments;
2566
2567 OPEN get_layout_ids;
2568 LOOP
2569 -- Bug 9137834
2570 -- Added exception handling for layouts so that if
2571 -- one layout fails, the rest continue.
2572 BEGIN
2573 FETCH get_layout_ids
2574 INTO l_layout_id;
2575 EXIT WHEN get_layout_ids%NOTFOUND;
2576
2577 IF g_debug
2578 THEN
2579 hr_utility.trace('Processing Layout '||l_layout_id);
2580 END IF;
2581
2582 populate_attributes (p_layout_id => l_layout_id,
2583 p_alias_tab => l_alias_tab);
2584
2585 translate_attributes(p_layout_id => l_layout_id);
2586
2587 translate_aliases (p_layout_id => l_layout_id,
2588 p_alias_tab => l_alias_tab);
2589
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 hr_utility.trace('Error Stack ');
2593 hr_utility.trace(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2594 hr_utility.trace('Exception '||SQLERRM||' while processing layout '||l_layout_id);
2595 END;
2596
2597 END LOOP;
2598 CLOSE get_layout_ids;
2599
2600 translate_cla_reasons;
2601 update_transaction_ids(p_record_save);
2602 translate_created_by;
2603 translate_last_updated_by;
2604 log_time_capture(p_request_id => g_request_id,
2605 p_request_sysdate => g_request_sysdate );
2606
2607 IF g_debug
2608 THEN
2609 hr_utility.trace('Finished processing for resource list '||i);
2610 hr_utility.trace('Clear the resource table and COMMIT the data collection ');
2611 END IF;
2612 DELETE FROM hxc_rpt_tc_resource_temp;
2613 COMMIT;
2614 END IF;
2615
2616 END IF;
2617
2618 EXCEPTION
2619 WHEN OTHERS THEN
2620 hr_utility.trace('Error Stack ');
2621 hr_utility.trace(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2622 hr_utility.trace('Exception '||SQLERRM||' while processing the following list ');
2623 hr_utility.trace(g_res_list_cs(i));
2624 END;
2625
2626
2627 END LOOP;
2628
2629 IF g_debug
2630 THEN
2631 hr_utility.trace('Request Finishes execution at '||to_char(SYSDATE,'dd-MON-yyyy HH:MI:SS')
2632 ||' and took '||ROUND(((SYSDATE-g_request_sysdate)*24*60),2)||' minutes to complete');
2633 END IF;
2634
2635
2636
2637 EXCEPTION
2638 WHEN NO_DATA_FOUND THEN
2639 NULL;
2640
2641
2642 END load_tc_snapshot;
2643
2644
2645 -- INSERT_QUERIES
2646 -- Used by hxcldvo.lct to load records into HXC_RPT_LAYOUT_COMP_QUERIES.
2647
2648 PROCEDURE insert_queries(p_vo_name VARCHAR2,
2649 p_query VARCHAR2)
2650 AS
2651
2652 layout_tab NUMTABLE;
2653 layout_comp_tab NUMTABLE;
2654 comp_tab VARCHARTABLE;
2655 attribute_tab VARCHARTABLE;
2656
2657 CURSOR get_comp_rank ( p_vo_name VARCHAR2)
2658 IS SELECT layout_id,
2659 layout_component_id,
2660 component_name,
2661 attribute
2662 FROM (
2663 SELECT /*+ INDEX( hlc HXC_LAYOUT_COMPONENTS_FK1 )
2664 INDEX( hlcq HXC_LAYOUT_COMP_QUALIFIERS_FK1) */
2665 hlc.layout_id ,
2666 hlc.layout_component_id ,
2667 REGEXP_REPLACE(hlc.component_name,'.*- ') component_name,
2668 hlcq.qualifier_attribute1 vo_name,
2669 'ATTRIBUTE'||RANK() OVER ( PARTITION BY hlc.layout_id
2670 ORDER BY hlc.layout_component_id ) AS attribute
2671 FROM hxc_layouts hl,
2672 hxc_layout_components hlc,
2673 hxc_layout_comp_qualifiers hlcq
2674 WHERE hlc.layout_id = hl.layout_id
2675 AND hl.layout_type = 'TIMECARD'
2676 AND hlcq.layout_component_id = hlc.layout_component_id
2677 AND hlcq.qualifier_attribute25 = 'FLEX'
2678 AND hlcq.qualifier_attribute_category IN ('LOV',
2679 'CHOICE_LIST',
2680 'PACKAGE_CHOICE_LIST',
2681 'TEXT_FIELD',
2682 'DESCRIPTIVE_FLEX')
2683 ) layout_all
2684 WHERE layout_all.vo_name = p_vo_name ;
2685
2686
2687 BEGIN
2688
2689
2690 -- Public Procedure insert_queries
2691 -- Not used by Load Timecard Snapshot Request.
2692 -- Used by hxcldvo.lct to load records into HXC_RPT_LAYOUT_COMP_QUERIES
2693 -- Inserts the relevant layout information and column name of
2694 -- HXC_RPT_TC_DETAILS_ALL that carries a given component.
2695
2696 OPEN get_comp_rank ( p_vo_name);
2697
2698 FETCH get_comp_rank BULK COLLECT
2699 INTO layout_tab,
2700 layout_comp_tab,
2701 comp_tab,
2702 attribute_tab ;
2703 CLOSE get_comp_rank;
2704
2705 IF layout_comp_tab.COUNT > 0
2706 THEN
2707
2708 FORALL i IN layout_comp_tab.FIRST..layout_comp_tab.LAST
2709 DELETE FROM hxc_rpt_layout_comp_queries
2710 WHERE layout_component_id = layout_comp_tab(i);
2711
2712 FORALL i IN layout_tab.FIRST..layout_tab.LAST
2713 DELETE FROM hxc_rpt_layout_comp_queries
2714 WHERE layout_id = layout_tab(i)
2715 AND attribute = attribute_tab(i);
2716
2717 FORALL i IN layout_comp_tab.FIRST..layout_comp_tab.LAST
2718 INSERT INTO hxc_rpt_layout_comp_queries
2719 ( layout_id,
2720 layout_component_id,
2721 component_name,
2722 query,
2723 attribute )
2724 VALUES ( layout_tab(i),
2725 layout_comp_tab(i),
2726 comp_tab(i),
2727 p_query,
2728 attribute_tab(i) );
2729
2730
2731 END IF;
2732
2733
2734
2735
2736 END insert_queries;
2737
2738
2739
2740 END HXC_RPT_LOAD_TC_SNAPSHOT;
2741
[Home] [Help]