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