[Home] [Help]
PACKAGE BODY: APPS.HXC_TIME_CATEGORY_UTILS_PKG
Source
1 Package Body hxc_time_category_utils_pkg as
2 /* $Header: hxchtcutl.pkb 120.23.12020000.6 2012/12/12 06:35:12 jnerella ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8
9 g_debug boolean := hr_utility.debug_enabled;
10
11 -- caching structures
12
13 TYPE rec_time_category_cache IS RECORD ( operator hxc_time_categories.operator%TYPE
14 , start_index PLS_INTEGER
15 , stop_index PLS_INTEGER
16 , time_sql CLOB -- Bug 15977687
17 , cache_date DATE );
18
19 TYPE tab_time_category_cache IS TABLE OF rec_time_category_cache INDEX BY BINARY_INTEGER;
20
21 g_tc_cache tab_time_category_cache;
22
23 TYPE rec_time_category_component IS RECORD ( type hxc_time_category_comps.type%TYPE
24 , ref_tc_id hxc_time_category_comps.time_category_id%TYPE
25 , sql_string CLOB );
26
27 TYPE tab_time_category_component IS TABLE OF rec_time_category_component INDEX BY BINARY_INTEGER;
28
29 g_tc_component_cache tab_time_category_component;
30
31 TYPE rec_time_category_bb_ok_cache IS RECORD ( timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
32 ,attribute_count NUMBER
33 ,bb_ok_string VARCHAR2(32000) );
34
35 TYPE tab_time_category_bb_ok_cache IS TABLE OF rec_time_category_bb_ok_cache INDEX BY BINARY_INTEGER;
36
37 g_tc_bb_ok_cache tab_time_category_bb_ok_cache;
38
39 -- bld blk types
40
41 TYPE tab_bb_id IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
42 TYPE tab_measure IS TABLE OF hxc_time_building_blocks.measure%TYPE INDEX BY BINARY_INTEGER;
43 TYPE tab_type IS TABLE OF hxc_time_building_blocks.type%TYPE INDEX BY BINARY_INTEGER;
44 TYPE tab_start_time IS TABLE OF DATE INDEX BY BINARY_INTEGER;
45 TYPE tab_stop_time IS TABLE OF DATE INDEX BY BINARY_INTEGER;
46 TYPE tab_scope IS TABLE OF hxc_time_building_blocks.scope%TYPE INDEX BY BINARY_INTEGER;
47 TYPE tab_comment_text IS TABLE OF hxc_time_building_blocks.comment_text%TYPE INDEX BY BINARY_INTEGER;
48
49 -- attribute types
50
51 TYPE tab_time_attribute_id IS TABLE OF hxc_time_attributes.time_attribute_id%TYPE INDEX BY BINARY_INTEGER;
52 TYPE tab_attribute_category IS TABLE OF hxc_time_attributes.attribute_category%TYPE INDEX BY BINARY_INTEGER;
53 TYPE tab_attribute IS TABLE OF hxc_time_attributes.attribute1%TYPE INDEX BY BINARY_INTEGER;
54 TYPE tab_bld_blk_info_type_id IS TABLE OF hxc_time_attributes.bld_blk_info_type_id%TYPE INDEX BY BINARY_INTEGER;
55
56 TYPE eval_time_category_params IS RECORD (
57 p_time_category_id hxc_time_categories.time_category_id%TYPE
58 , p_use_tc_cache BOOLEAN
59 , p_use_tc_bb_cache BOOLEAN
60 , p_use_temp_table BOOLEAN
61 , p_scope VARCHAR2(10)
62 , p_tbb_id hxc_time_building_blocks.time_building_block_id%TYPE
63 , p_tbb_ovn hxc_time_building_blocks.object_version_number%TYPE );
64
65 g_params eval_time_category_params;
66
67 /* Bug 5076837 used to store the time_categories along with the category component status
68 if the time_category does not have any component then the status will be true
69 otherwise it will be false */
70
71 TYPE rec_empty_time_category is record ( p_status BOOLEAN);
72
73 TYPE tab_empty_time_category IS TABLE OF rec_empty_time_category INDEX BY BINARY_INTEGER;
74
75 g_empty_time_category_tab tab_empty_time_category;
76 /* end of changes for bug 5076837 */
77
78
79 l_first_time_round BOOLEAN;
80
81 l_continue_evaluation BOOLEAN := TRUE;
82
83 l_tc_bb_cache_exists BOOLEAN := FALSE;
84 l_tc_cache_exists BOOLEAN := FALSE;
85
86 l_operator hxc_time_categories.operator%TYPE;
87
88 CURSOR csr_get_operator ( p_time_category_id NUMBER ) IS
89 SELECT operator
90 FROM hxc_time_categories
91 WHERE time_category_id = p_time_category_id;
92
93
94 CURSOR csr_get_category_comps ( p_time_category_id NUMBER ) IS
95 SELECT
96 bbit.bld_blk_info_type context
97 , bbit.bld_blk_info_type_id
98 , mpc.segment
99 , NVL(tcc.value_id, DECODE(tcc.is_null, 'N', '<WILDCARD>', '<IS NULL>')) value_id
100 , tcc.ref_time_category_id
101 , tcc.flex_value_set_id
102 , tcc.equal_to
103 FROM
104 hxc_bld_blk_info_types bbit
105 , hxc_mapping_components mpc
106 , hxc_time_category_comps tcc
107 WHERE tcc.time_category_id = p_time_category_id AND
108 tcc.type = 'MC'
109 AND
110 mpc.mapping_component_id (+) = tcc.component_type_id
111 AND
112 bbit.bld_blk_info_type_id (+) = mpc.bld_blk_info_type_id;
113
114 CURSOR csr_get_alternate_name_comps ( p_alias_value_id NUMBER ) IS
115 SELECT
116 bbit.bld_blk_info_type context
117 , bbit.bld_blk_info_type_id
118 , DECODE( bbit.bld_blk_info_type,
119 'Dummy Cost Context', REPLACE( mpc.segment, 'CostSegment', 'ATTRIBUTE' ),
120 'Dummy Grp Context', REPLACE( mpc.segment, 'GrpSegment', 'ATTRIBUTE' ),
121 'Dummy Job Context', REPLACE( mpc.segment, 'JobSegment', 'ATTRIBUTE' ),
122 'Dummy Pos Context', REPLACE( mpc.segment, 'PosSegment', 'ATTRIBUTE' ),
123 'Dummy Paexpitdff Context', REPLACE( mpc.segment, 'PADFFAttribute', 'ATTRIBUTE' ),
124 mpc.segment ) segment
125 , mpc.name
126 , atc.component_type application_column_name
127 , av.attribute1
128 , av.attribute2
129 , av.attribute3
130 , av.attribute4
131 , av.attribute5
132 , av.attribute6
133 , av.attribute7
134 , av.attribute8
135 , av.attribute9
136 , av.attribute10
137 , av.attribute11
138 , av.attribute12
139 , av.attribute13
140 , av.attribute14
141 , av.attribute15
142 , av.attribute16
143 , av.attribute17
144 , av.attribute18
145 , av.attribute19
146 , av.attribute20
147 , av.attribute21
148 , av.attribute22
149 , av.attribute23
150 , av.attribute24
151 , av.attribute25
152 , av.attribute26
153 , av.attribute27
154 , av.attribute28
155 , av.attribute29
156 , av.attribute30
157 FROM
158 hxc_bld_blk_info_types bbit
159 , hxc_mapping_components mpc
160 , hxc_alias_type_components atc
161 , hxc_alias_types hat
162 , hxc_alias_definitions ad
163 , hxc_alias_values av
164 WHERE
165 av.alias_value_id = p_alias_value_id
166 AND
167 ad.alias_definition_id = av.alias_definition_id
168 AND
169 hat.alias_type_id = ad.alias_type_id
170 AND
171 atc.alias_type_id = hat.alias_type_id
172 AND
173 mpc.mapping_component_id = atc.mapping_component_id
174 AND
175 bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id;
176
177
178 CURSOR csr_chk_tcc_sql_exists ( p_tcc_id NUMBER ) IS
179 SELECT time_category_comp_sql_id tcc_sql_id
180 FROM hxc_time_category_comp_sql
181 WHERE time_category_comp_id = p_tcc_id;
182
183 CURSOR csr_get_time_category ( p_time_category_id NUMBER ) IS
184 SELECT htc.time_sql
185 , htc.operator
186 FROM hxc_time_categories htc
187 WHERE htc.time_category_id = p_time_category_id
188 AND EXISTS ( select 'x'
189 from hxc_time_category_comps tcc
190 where tcc.time_category_id = htc.time_category_id );
191
192 l_comps_r csr_get_category_comps%ROWTYPE;
193 l_alternate_name_comps_r csr_get_alternate_name_comps%ROWTYPE;
194
195 TYPE l_comps_tab IS TABLE OF csr_get_category_comps%ROWTYPE INDEX BY BINARY_INTEGER;
196
197
198 PROCEDURE add_tc_to_cache ( p_time_category_id NUMBER
199 , p_time_category_info csr_get_time_category%ROWTYPE
200 , p_vs_comp_tab t_vs_comp
201 , p_an_comp_tab t_an_comp
202 , p_tc_comp_tab t_tc_comp ) IS
203
204 l_proc varchar2(72);
205
206 l_tc_comp_ind PLS_INTEGER;
207
208 l_ind PLS_INTEGER;
209
210 l_rec hxc_tcc_shd.g_rec_type;
211
212 l_sql_string CLOB;
213
214 l_start_index PLS_INTEGER;
215
216 CURSOR csr_get_new_sql_string ( p_tcc_id NUMBER ) IS
217 SELECT sql_string
218 FROM hxc_time_category_comp_sql
219 WHERE time_category_comp_id = p_tcc_id;
220
221 -- private function to check if the value set has been
222 -- changed since the TCC row was updated
223 -- If so populate and return l_rec
224
225 FUNCTION value_set_changed ( p_vs_comp_rec r_vs_comp
226 , p_rec IN OUT NOCOPY hxc_tcc_shd.g_rec_type )
227 RETURN BOOLEAN IS
228
229 CURSOR get_value_set_last_update_date ( p_flex_value_set_id NUMBER ) IS
230 SELECT vs.last_update_date
231 FROM fnd_flex_value_sets vs
232 WHERE vs.flex_value_set_id = p_flex_value_set_id;
233
234 CURSOR get_vset_tab_last_update_date ( p_flex_value_set_id NUMBER ) IS
235 SELECT vst.last_update_date
236 FROM fnd_flex_validation_tables vst
237 WHERE vst.flex_value_set_id = p_flex_value_set_id;
238
239 l_vs_last_update_date DATE;
240
241 l_proc varchar2(72);
242
243 BEGIN
244
245
246
247 IF ( g_debug ) THEN
248 l_proc := g_package||'value_set_changed';
249 hr_utility.set_location('Leaving '||l_proc, 10);
250 END IF;
251
252 OPEN get_value_set_last_update_date ( p_vs_comp_rec.flex_value_set_id );
253 FETCH get_value_set_last_update_date INTO l_vs_last_update_date;
254 CLOSE get_value_set_last_update_date;
255
256 IF ( g_debug ) THEN
257 hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
258 hr_utility.trace('Value set last update date is '||to_char(l_vs_last_update_date));
259 hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
260 END IF;
261
262 IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
263 THEN
264
265 -- value set definition changed since time category comp
266 -- created
267
268 p_rec.time_category_comp_id := p_vs_comp_rec.time_category_comp_id;
269 p_rec.time_category_id := p_vs_comp_rec.time_category_id;
270 p_rec.ref_time_category_id := NULL;
271 p_rec.component_type_id := p_vs_comp_rec.component_type_id;
272 p_rec.flex_value_set_id := p_vs_comp_rec.flex_value_set_id;
273 p_rec.value_id := '<VALUE_SET_SQL>';
274 p_rec.is_null := p_vs_comp_rec.is_null;
275 p_rec.equal_to := p_vs_comp_rec.equal_to;
276 p_rec.type := 'MC_VS';
277 p_rec.object_version_number := NULL;
278
279 RETURN TRUE;
280
281 ELSE
282
283 -- check table last update date
284
285 OPEN get_vset_tab_last_update_date ( p_vs_comp_rec.flex_value_set_id );
286 FETCH get_vset_tab_last_update_date INTO l_vs_last_update_date;
287 CLOSE get_vset_tab_last_update_date;
288
289 IF ( g_debug ) THEN
290 hr_utility.trace('Value set is '||to_char(p_vs_comp_rec.flex_value_set_id));
291 hr_utility.trace('Value set table last update date is '||to_char(l_vs_last_update_date));
292 hr_utility.trace('TCC row last update date is '||to_char(p_vs_comp_rec.last_update_date));
293 END IF;
294
295 IF ( l_vs_last_update_date > p_vs_comp_rec.last_update_date )
296 THEN
297
298 -- value set definition changed since time category comp
299 -- created
300
301 p_rec.time_category_comp_id := p_vs_comp_rec.time_category_comp_id;
302 p_rec.time_category_id := p_vs_comp_rec.time_category_id;
303 p_rec.ref_time_category_id := NULL;
304 p_rec.component_type_id := p_vs_comp_rec.component_type_id;
305 p_rec.flex_value_set_id := p_vs_comp_rec.flex_value_set_id;
306 p_rec.value_id := '<VALUE_SET_SQL>';
307 p_rec.is_null := p_vs_comp_rec.is_null;
308 p_rec.equal_to := p_vs_comp_rec.equal_to;
309 p_rec.type := 'MC_VS';
310 p_rec.object_version_number := NULL;
311
312 RETURN TRUE;
313
314 ELSE
315
316 RETURN FALSE;
317
318 END IF;
319
320 END IF;
321
322 IF ( g_debug ) THEN
323 hr_utility.set_location('Leaving '||l_proc, 10);
324 END IF;
325
326 END value_set_changed;
327
328
329 BEGIN -- add_tc_to_cache
330
331
332
333 IF ( g_debug ) THEN
334 l_proc := g_package||'add_tc_to_cache';
335 hr_utility.set_location('Entering '||l_proc, 10);
336 END IF;
337
338 l_start_index := NVL( g_tc_component_cache.LAST, 0 )+1;
339 g_tc_cache( p_time_category_id ).time_sql := p_time_category_info.time_sql;
340 g_tc_cache( p_time_category_id ).operator := p_time_category_info.operator;
341 g_tc_cache( p_time_category_id ).cache_date := sysdate;
342
343 -- this is quite straight forward other than having to check that
344 -- the value set associated with the MC_VS has not changed since
345 -- the MC_VS row was last updated.
346
347 IF ( p_vs_comp_tab.COUNT > 0 )
348 THEN
349
350 g_tc_cache( p_time_category_id ).start_index := l_start_index;
351
352 FOR x IN p_vs_comp_tab.FIRST .. p_vs_comp_tab.LAST
353 LOOP
354
355 l_tc_comp_ind := NVL( g_tc_component_cache.LAST, 0 )+1;
356
357 IF ( value_set_changed ( p_vs_comp_tab(x), l_rec ) )
358 THEN
359
360 -- re-evaluate
361
362 IF ( g_debug ) THEN
363 hr_utility.trace('Value Set Changed!!!');
364 END IF;
365
366 update_time_category_comp_sql ( p_rec => l_rec );
367
368 -- touch tcc row
369
370 UPDATE hxc_time_category_comps tcc
371 SET time_category_comp_id = l_rec.time_category_comp_id
372 WHERE time_category_comp_id = l_rec.time_category_comp_id;
373
374 OPEN csr_get_new_sql_string ( l_rec.time_category_comp_id );
375 FETCH csr_get_new_sql_string INTO l_sql_string;
376 CLOSE csr_get_new_sql_string;
377
378 g_tc_component_cache(l_tc_comp_ind).sql_string := l_sql_string;
379
380 ELSE
381
382 IF ( g_debug ) THEN
383 hr_utility.trace('Value Set Not Changed');
384 END IF;
385
386 g_tc_component_cache(l_tc_comp_ind).sql_string := p_vs_comp_tab(x).sql_string;
387
388 END IF;
389
390 g_tc_component_cache(l_tc_comp_ind).type := 'MC_VS';
391
392 END LOOP;
393
394 END IF; -- IF ( p_vs_comp_tab.COUNT > 0 )
395
396
397 IF ( p_an_comp_tab.COUNT > 0 )
398 THEN
399
400 g_tc_cache( p_time_category_id ).start_index := l_start_index;
401
402 -- now cache the Alternate Name Components
403
404 FOR x IN p_an_comp_tab.FIRST .. p_an_comp_tab.LAST
405 LOOP
406
407 l_tc_comp_ind := NVL( g_tc_component_cache.LAST, 0 )+1;
408
409 g_tc_component_cache(l_tc_comp_ind).sql_string := p_an_comp_tab(x).sql_string;
410 g_tc_component_cache(l_tc_comp_ind).type := 'AN';
411
412 END LOOP;
413
414 END IF; -- IF ( p_an_comp_tab.COUNT > 0 )
415
416
417 IF ( p_tc_comp_tab.COUNT > 0 )
418 THEN
419
420 g_tc_cache( p_time_category_id ).start_index := l_start_index;
421
422 -- now cache the Time Category Components
423
424 FOR x IN p_tc_comp_tab.FIRST .. p_tc_comp_tab.LAST
425 LOOP
426
427 l_tc_comp_ind := NVL( g_tc_component_cache.LAST, 0 )+1;
428
429 g_tc_component_cache(l_tc_comp_ind).ref_tc_id := p_tc_comp_tab(x).ref_tc_id;
430 g_tc_component_cache(l_tc_comp_ind).type := 'TC';
431
432 END LOOP;
433
434 END IF; -- IF ( p_tc_comp_tab.COUNT > 0 )
435
436 IF ( g_tc_cache(p_time_category_id).start_index IS NOT NULL )
437 THEN
438
439 g_tc_cache( p_time_category_id ).stop_index := l_tc_comp_ind;
440
441 END IF;
442
443 IF ( g_debug ) THEN
444 hr_utility.set_location('Leaving '||l_proc, 100);
445
446 hr_utility.trace('TC Cache for '||to_char(p_time_category_id));
447
448 hr_utility.trace('g_tc_cache operator is '||g_tc_cache(p_time_category_id).operator);
449 hr_utility.trace('g_tc_cache start is '||to_char(g_tc_cache(p_time_category_id).start_index));
450 hr_utility.trace('g_tc_cache stop is '||to_char(g_tc_cache(p_time_category_id).stop_index));
451 hr_utility.trace(SUBSTR( 'g_tc_cache time sql is '||g_tc_cache(p_time_category_id).time_sql,1,250));
452
453
454 IF ( g_tc_cache(p_time_category_id).start_index IS NOT NULL )
455 THEN
456
457 hr_utility.trace('TC Cache components are ');
458
459 FOR x IN g_tc_cache(p_time_category_id).start_index ..
460 g_tc_cache(p_time_category_id).stop_index
461 LOOP
462
463 hr_utility.trace('Type is '||g_tc_component_cache(x).type);
464 hr_utility.trace('ref tc id is '||to_char(g_tc_component_cache(x).ref_tc_id));
465
466 END LOOP;
467
468 ELSE
469
470 hr_utility.trace('No components');
471
472 END IF;
473 END IF; -- g_debug
474
475 END add_tc_to_cache;
476
477
478
479 PROCEDURE get_tc_from_cache ( p_time_category_id NUMBER
480 , p_vs_comp_tab IN OUT NOCOPY t_vs_comp
481 , p_an_comp_tab IN OUT NOCOPY t_an_comp
482 , p_tc_comp_tab IN OUT NOCOPY t_tc_comp ) IS
483
484 l_proc varchar2(72);
485
486 l_vs_ind PLS_INTEGER := 1;
487 l_tc_ind PLS_INTEGER := 1;
488 l_an_ind PLS_INTEGER := 1;
489
490 l_tc_cache_start PLS_INTEGER;
491 l_tc_cache_stop PLS_INTEGER;
492
493 BEGIN
494
495
496
497 IF ( g_debug ) THEN
498 l_proc := g_package||'get_tc_from_cache';
499 hr_utility.set_location('Entering '||l_proc, 10);
500
501 hr_utility.trace('Getting info from cache for TC '||to_char(p_time_category_id));
502
503 END IF;
504
505 l_tc_cache_start := g_tc_cache(p_time_category_id).start_index;
506 l_tc_cache_stop := g_tc_cache(p_time_category_id).stop_index;
507
508 IF ( l_tc_cache_start IS NOT NULL )
509 THEN
510
511 IF ( g_debug ) THEN
512 hr_utility.trace('Cache components exist');
513 END IF;
514
515 FOR x IN l_tc_cache_start .. l_tc_cache_stop
516 LOOP
517
518 IF ( g_tc_component_cache(x).type = 'MC_VS' )
519 THEN
520 p_vs_comp_tab(l_vs_ind).sql_string := g_tc_component_cache(x).sql_string;
521
522 l_vs_ind := l_vs_ind + 1;
523
524 ELSIF ( g_tc_component_cache(x).type = 'AN' )
525 THEN
526 p_an_comp_tab(l_an_ind).sql_string := g_tc_component_cache(x).sql_string;
527
528 l_an_ind := l_an_ind + 1;
529
530 ELSIF ( g_tc_component_cache(x).type = 'TC' )
531 THEN
532 p_tc_comp_tab(l_tc_ind).ref_tc_id := g_tc_component_cache(x).ref_tc_id;
533
534 l_tc_ind := l_tc_ind + 1;
535
536 ELSE
537 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
538 fnd_message.set_token('PROCEDURE', l_proc);
539 fnd_message.set_token('STEP','Invalid TYPE');
540 fnd_message.raise_error;
541 END IF;
542
543 END LOOP;
544
545 ELSE
546
547 IF ( g_debug ) THEN
548 hr_utility.trace('No Cache Components');
549 END IF;
550
551 END IF;
552
553 IF ( g_debug ) THEN
554 hr_utility.set_location('Leaving '||l_proc, 20);
555 END IF;
556
557 END get_tc_from_cache;
558
559
560 FUNCTION tc_cache_exists ( p_time_category_id NUMBER ) RETURN BOOLEAN IS
561
562 BEGIN
563
564
565
566 IF ( g_tc_cache.EXISTS ( p_time_category_id ) )
567 THEN
568
569 IF ( g_debug ) THEN
570 hr_utility.trace('TC cache exists');
571 END IF;
572
573 -- is cache more than 30 mins old?
574
575 -- 10 mins IF ( ( sysdate - g_tc_cache(p_time_category_id).cache_date ) <= .007 )
576 IF ( ( sysdate - g_tc_cache(p_time_category_id).cache_date ) <= .02 )
577 THEN
578 IF ( g_debug ) THEN
579 hr_utility.trace('TC Cache Current');
580 END IF;
581
582 RETURN TRUE;
583 ELSE
584 IF ( g_debug ) THEN
585 hr_utility.trace('TC Cache Expired');
586 END IF;
587
588 g_tc_cache.DELETE;
589 g_tc_bb_ok_cache.DELETE;
590
591 RETURN FALSE;
592 END IF;
593 ELSE
594
595 IF ( g_debug ) THEN
596 hr_utility.trace('TC Cache does not exist');
597 END IF;
598
599 RETURN FALSE;
600 END IF;
601
602 END tc_cache_exists;
603
604
605 FUNCTION get_bb_ok_tab_from_string ( p_time_category_id NUMBER ) RETURN t_tc_bb_ok
606
607 IS
608
609 l_bb_ok_string VARCHAR2(32000);
610 l_bb_ok_tab t_tc_bb_ok;
611 l_bb_id NUMBER;
612
613 l_proc varchar2(72);
614
615 BEGIN
616
617
618
619 IF ( g_debug ) THEN
620 l_proc := g_package||'get_bb_ok_tab_from_string';
621 hr_utility.set_location('Entering '||l_proc, 10);
622 END IF;
623
624 l_bb_ok_string := g_tc_bb_ok_cache(p_time_category_id).bb_ok_string;
625
626 WHILE l_bb_ok_string IS NOT NULL
627 LOOP
628
629 IF ( INSTR( l_bb_ok_string, ',' ) = 0 )
630 THEN
631
632 l_bb_id := RTRIM(LTRIM(l_bb_ok_string));
633
634 l_bb_ok_tab(l_bb_id).bb_id_ok := 'Y';
635
636 l_bb_ok_string := REPLACE( l_bb_ok_string, l_bb_ok_string );
637
638 ELSE
639
640 l_bb_id := SUBSTR( l_bb_ok_string, 1, ( INSTR( l_bb_ok_string, ',' )-1) );
641
642 l_bb_ok_tab(l_bb_id).bb_id_ok := 'Y';
643
644 l_bb_ok_string := REPLACE( l_bb_ok_string, l_bb_id||', ' );
645
646 END IF;
647
648 END LOOP;
649
650 IF ( g_debug ) THEN
651 hr_utility.set_location('Leaving '||l_proc, 20);
652 END IF;
653
654 RETURN l_bb_ok_tab;
655
656 END get_bb_ok_tab_from_string;
657
658 --
659 -- private procedure
660 --
661 function get_token(
662 the_list varchar2,
663 the_index number,
664 delim varchar2 := ','
665 )
666 return varchar2
667 is
668 start_pos number;
669 end_pos number;
670 begin
671 if the_index = 1 then
672 start_pos := 1;
673 else
674 start_pos := instr(the_list,delim,1,the_index - 1);
675 if start_pos = 0 then
676 return null;
677 else
678 start_pos := start_pos + length(delim);
679 end if;
680 end if;
681
682 end_pos := instr(the_list,delim,start_pos,1);
683
684 if end_pos = 0 then
685 return substr(the_list,start_pos);
686 else
687 return substr(the_list,start_pos,end_pos - start_pos);
688 end if;
689
690 end get_token;
691
692 --
693 -- private procedure
694 --
695 FUNCTION get_token_count(
696 the_list varchar2,
697 delim varchar2 := ','
698 )
699 return number
700
701 is
702
703 l_result varchar2(30):= 'not null';
704 l_index number :=1;
705 l_count number :=0;
706
707
708 BEGIN
709 WHILE l_result is not null
710 LOOP
711 l_result:= get_token (the_list,l_index,delim);
712 l_index := l_index + 1;
713 l_count := l_count + 1;
714 END LOOP;
715
716 return l_count-1;
717
718 end get_token_count;
719
720 --
721 -- private procedure
722 --
723 function get_token_string(
724 the_list varchar2,
725 delim varchar2 := ','
726 )
727 return varchar2
728
729 is
730
731 l_result varchar2(32000);
732
733
734 BEGIN
735
736 FOR i in 1..get_token_count(the_list,',') LOOP
737 if (i=1) then
738 l_result := ':'||i;
739 else
740 l_result := l_result||',:'||i;
741 end if;
742 END LOOP;
743 return l_result;
744
745 end get_token_string;
746
747
748 --
749 -- PRIVATE FUNCTION
750 --
751
752 FUNCTION parse_time_sql_to_bind(p_time_sql CLOB) -- Bug 15977687
753 RETURN VARCHAR2 IS
754
755 l_bind_time_sql CLOB;
756 l_between_or varchar2(32000) := 'not null';
757 l_before_and varchar2(32000);
758 l_after_and varchar2(32000);
759 l_before_equal1 varchar2(32000);
760 l_before_equal2 varchar2(32000);
761
762 -- Bug 8589919
763 -- Changed the index to start from 1000.
764 -- There are two sets of indexes, one for attributes one for blk bind variables.
765 -- Initially blocks were to start from 1, increment by 1.
766 -- Attributes to start by 100, increment by 100.
767 -- No problem as long as there are no 100 blks, where there would be a clash.
768 -- Bug 7432755 reported this issue, and the fix was to change the attribute
769 -- indexes to start from 1000, step by 1000.
770 -- This causes the bind variables to pass above ~65k which is the allowed semantic
771 -- limit for plsql. Issue raised for Apps IT in bug 8589919.
772 -- Fixed it by holding the blk index as such, and attribute index to start from 1000,
773 -- and step by 5.
774 l_index number :=1000;
775
776 l_null BOOLEAN := FALSE;
777 l_pass BOOLEAN := FALSE;
778
779 BEGIN
780
781
782 WHILE l_between_or is not null
783 LOOP
784 l_between_or := get_token (p_time_sql,l_index,' OR ');
785
786 IF (l_between_or IS NOT NULL AND get_token (l_between_or,3,' AND ') IS NULL) THEN
787 -- now I look for the and
788 l_before_and := get_token (l_between_or,1,' AND ');
789 l_after_and := get_token (l_between_or,2,' AND ');
790
791 -- now I can replace the value with the bind
792 l_before_equal1 := get_token (l_before_and,1,'=');
793 -- Bug 8589919
794 l_before_equal1 := l_before_equal1 ||'=:'||l_index;
795
796 -- now I can replace the value with the bind
797 IF (instr(l_after_and,'IS NOT NULL') = 0 AND instr(l_after_and,'IS NULL') = 0
798 AND instr(l_after_and,'<>') = 0 AND instr(l_after_and,'IN') = 0)
799 THEN
800 l_before_equal2 := get_token (l_after_and,1,'=');
801 -- Bug 8589919
802 --l_before_equal2 := l_before_equal2 ||'=:'||((l_index*1000)+1)||')';
803 l_before_equal2 := l_before_equal2 ||'=:'||((l_index)+1)||')';
804 l_null := FALSE;
805 ELSE
806 l_before_equal2 := l_after_and;
807 l_null := TRUE;
808 END IF;
809
810 IF l_bind_time_sql IS NULL THEN
811 l_bind_time_sql := l_before_equal1 ||' AND '|| l_before_equal2 ;
812 ELSE
813 l_bind_time_sql := l_bind_time_sql ||' OR '||l_before_equal1 ||' AND '|| l_before_equal2;
814 END IF;
815 l_pass := TRUE;
816 ELSE
817 IF l_bind_time_sql IS NULL THEN
818 l_bind_time_sql := p_time_sql;
819 ELSIF (l_null=FALSE AND l_pass = TRUE) THEN
820 l_bind_time_sql := l_bind_time_sql ||' )';
821 END IF;
822
823 l_pass := FALSE;
824 END IF;
825
826 -- Bug 8589919
827 l_index := l_index + 5;
828
829 END LOOP;
830
831 return l_bind_time_sql;
832
833 END parse_time_sql_to_bind;
834
835
836
837 PROCEDURE validate_time_category_sql ( p_sql_string IN CLOB ) IS -- Bug 15977687
838
839 l_sql CLOB := 'select distinct ta.bb_id from hxc_tmp_atts ta where '; -- Bug 15977687
840
841 t_bb_id dbms_sql.Number_Table;
842
843 l_csr INTEGER;
844 l_rows_fetched INTEGER;
845 l_dummy INTEGER;
846
847 l_parse_time_sql CLOB; -- Bug 15977687
848
849
850 l_between_or varchar2(32000) := 'not null';
851 l_before_and varchar2(32000);
852 l_after_and varchar2(32000);
853 l_before_equal1 varchar2(32000);
854 l_before_equal2 varchar2(32000);
855
856 -- Bug 8589919
857 l_index number :=1000;
858
859
860
861
862
863 BEGIN
864
865 -- the SQL MUST returns rows to show all possible errors
866 -- particularly implicit character to number and vice
867 -- versa
868
869 INSERT INTO hxc_tmp_atts (
870 ta_id
871 , bb_id
872 , attribute1
873 , attribute2
874 , attribute3
875 , attribute4
876 , attribute5
877 , attribute6
878 , attribute7
879 , attribute8
880 , attribute9
881 , attribute10
882 , attribute11
883 , attribute12
884 , attribute13
885 , attribute14
886 , attribute15
887 , attribute16
888 , attribute17
889 , attribute18
890 , attribute19
891 , attribute20
892 , attribute21
893 , attribute22
894 , attribute23
895 , attribute24
896 , attribute25
897 , attribute26
898 , attribute27
899 , attribute28
900 , attribute29
901 , attribute30
902 , bld_blk_info_type_id
903 , attribute_category )
904 VALUES (
905 1
906 , 2
907 , 'Dummy'
908 , 'Dummy'
909 , 'Dummy'
910 , 'Dummy'
911 , 'Dummy'
912 , 'Dummy'
913 , 'Dummy'
914 , 'Dummy'
915 , 'Dummy'
916 , 'Dummy'
917 , 'Dummy'
918 , 'Dummy'
919 , 'Dummy'
920 , 'Dummy'
921 , 'Dummy'
922 , 'Dummy'
923 , 'Dummy'
924 , 'Dummy'
925 , 'Dummy'
926 , 'Dummy'
927 , 'Dummy'
928 , 'Dummy'
929 , 'Dummy'
930 , 'Dummy'
931 , 'Dummy'
932 , 'Dummy'
933 , 'Dummy'
934 , 'Dummy'
935 , 'Dummy'
936 , 'Dummy'
937 , 1
938 , 'Dummy' );
939
940 l_parse_time_sql := parse_time_sql_to_bind(p_sql_string);
941
942 l_sql := 'select distinct ta.bb_id from hxc_tmp_atts ta where '||l_parse_time_sql;
943
944 BEGIN
945 l_rows_fetched := 100;
946
947 l_csr := dbms_sql.open_cursor;
948
949 dbms_sql.parse ( l_csr, l_sql, dbms_sql.native );
950
951 -- replace the parse_time_sql bind
952 WHILE l_between_or is not null
953 LOOP
954 l_between_or := get_token (p_sql_string,l_index,' OR ');
955
956 IF (l_between_or IS NOT NULL AND get_token (l_between_or,3,' AND ') IS NULL) THEN
957 -- now I look for the and
958 l_before_and := get_token (l_between_or,1,'AND');
959 l_after_and := get_token (l_between_or,2,'AND');
960
961 -- now I can replace the value with the bind
962 l_before_equal1 := replace(get_token (l_before_and,2,'='),')','');
963 l_before_equal1 := trim(replace(l_before_equal1,'''',''));
964 -- Bug 8589919
965 --dbms_sql.bind_variable( l_csr, ':'||l_index*1000, l_before_equal1 );
966 dbms_sql.bind_variable( l_csr, ':'||l_index, l_before_equal1 );
967
968 IF (instr(l_after_and,'IS NOT NULL') = 0 AND instr(l_after_and,'IS NULL') = 0
969 AND instr(l_after_and,'<>') = 0 AND instr(l_after_and,'IN') = 0)
970 THEN
971
972 -- now I can replace the value with the bind
973 l_before_equal2 := replace(get_token (l_after_and,2,'='),')','');
974 l_before_equal2 := trim(replace(l_before_equal2,'''',''));
975
976 -- Bug 8589919
977 dbms_sql.bind_variable( l_csr, ':'||((l_index)+1),l_before_equal2);
978
979 END IF;
980
981 END IF;
982 -- Bug 8589919
983 l_index := l_index + 5;
984
985 END LOOP;
986
987 dbms_sql.define_array (
988 c => l_csr
989 , position => 1
990 , n_tab => t_bb_id
991 , cnt => l_rows_fetched
992 , lower_bound => 1 );
993
994 l_dummy := dbms_sql.execute ( l_csr );
995
996 -- loop to ensure we fetch all the rows
997
998 WHILE ( l_rows_fetched = 100 )
999 LOOP
1000
1001 l_rows_fetched := dbms_sql.fetch_rows ( l_csr );
1002
1003 IF ( l_rows_fetched > 0 )
1004 THEN
1005
1006 dbms_sql.column_value (
1007 c => l_csr
1008 , position => 1
1009 , n_tab => t_bb_id );
1010
1011 t_bb_id.DELETE;
1012
1013 END IF;
1014
1015 END LOOP;
1016
1017 dbms_sql.close_cursor ( l_csr );
1018
1019 -- execute immediate l_sql INTO l_dummy;
1020
1021 EXCEPTION WHEN NO_DATA_FOUND THEN
1022
1023 null;
1024
1025 WHEN OTHERS THEN
1026
1027 fnd_message.set_name('HXC', 'HXC_HTC_INVALID_SQL');
1028 fnd_message.set_token('ERROR', SQLERRM );
1029 fnd_message.raise_error;
1030
1031 END;
1032
1033 END validate_time_category_sql;
1034
1035
1036
1037 PROCEDURE chk_profile_flex ( p_flex_value_set_id NUMBER
1038 ,p_where OUT NOCOPY LONG
1039 ,p_sql_ok OUT NOCOPY BOOLEAN ) IS
1040
1041 l_proc varchar2(72) := g_package||'chk_profile_flex';
1042
1043 l_sql LONG;
1044 l_sql_ok BOOLEAN := FALSE;
1045
1046 CURSOR csr_get_value_set_sql IS
1047 SELECT additional_where_clause
1048 FROM fnd_flex_validation_tables
1049 WHERE flex_value_set_id = p_flex_value_set_id;
1050
1051 BEGIN
1052
1053 OPEN csr_get_value_set_sql;
1054 FETCH csr_get_value_set_sql INTO l_sql;
1055 CLOSE csr_get_value_set_sql;
1056
1057
1058 IF ((( INSTR(UPPER(l_sql),':$FLEX$') = 0 ) AND
1059 ( INSTR(UPPER(l_sql),'$FLEX$') = 0 ) AND
1060 ( INSTR(UPPER(l_sql),'$PROFILE$') = 0 ) AND
1061 ( INSTR(UPPER(l_sql),':$PROFILE$') = 0 ) AND
1062 ( INSTR(UPPER(l_sql),'$PROFILES$') = 0 ) AND
1063 ( INSTR(UPPER(l_sql),':$PROFILES$') = 0 )) OR l_sql IS NULL )
1064 THEN
1065
1066 l_sql_ok := TRUE;
1067
1068 END IF;
1069
1070 p_where := l_sql;
1071 p_sql_ok := l_sql_ok;
1072
1073 END chk_profile_flex;
1074
1075
1076
1077 FUNCTION continue_evaluation ( p_operator VARCHAR2
1078 , p_tc_bb_ok_string VARCHAR2
1079 , p_tc_bb_not_ok_string VARCHAR2 ) RETURN BOOLEAN IS
1080
1081 l_return BOOLEAN := TRUE;
1082
1083 BEGIN
1084
1085
1086
1087 IF ( p_operator = 'OR' )
1088 THEN
1089
1090 IF ( p_tc_bb_not_ok_string IS NULL )
1091 THEN
1092 l_return := FALSE;
1093 END IF;
1094
1095 ELSE -- p_operator = 'AND'
1096
1097 IF ( p_tc_bb_ok_string IS NULL )
1098 THEN
1099 l_return := FALSE;
1100 END IF;
1101
1102 END IF;
1103
1104 IF ( l_return )
1105 THEN
1106 IF ( g_debug ) THEN
1107 hr_utility.trace('Continue evaluation is TRUE');
1108 END IF;
1109 ELSE
1110 IF ( g_debug ) THEN
1111 hr_utility.trace('Continue evaluation is FALSE');
1112 END IF;
1113 END IF;
1114
1115 RETURN l_return;
1116
1117 END continue_evaluation;
1118
1119
1120
1121 PROCEDURE get_dyn_sql ( p_time_sql IN OUT NOCOPY CLOB -- Bug 15977687
1122 , p_comps_r IN csr_get_category_comps%ROWTYPE
1123 , p_operator IN VARCHAR2
1124 , p_an_sql IN BOOLEAN DEFAULT FALSE
1125 , p_vs_sql IN BOOLEAN DEFAULT FALSE ) IS
1126
1127 l_proc varchar2(72);
1128 l_dyn_sql CLOB; -- Bug 15977687
1129 l_ref_dyn_sql CLOB; -- Bug 15977687
1130
1131 l_value_string VARCHAR2(150);
1132 l_string_start VARCHAR2(30) := '( ta.bld_blk_info_type_id = ';
1133 l_string_and VARCHAR2(10) := ' AND ta.';
1134
1135
1136 BEGIN
1137
1138
1139
1140 IF ( g_debug ) THEN
1141 l_proc := g_package||'get_dyn_sql';
1142 hr_utility.trace('get dyn sql params are ....');
1143 hr_utility.trace('dyn sql is '||p_time_sql);
1144 END IF;
1145
1146 -- we want the dynamic sql string
1147
1148 l_dyn_sql := p_time_sql;
1149
1150 l_ref_dyn_sql := NULL;
1151
1152 IF ( p_comps_r.context = 'Dummy Element Context' AND p_comps_r.flex_value_set_id = -1 AND
1153 p_comps_r.value_id <> '<WILDCARD>' )
1154 THEN
1155
1156 l_value_string := 'ELEMENT - '||p_comps_r.value_id;
1157
1158 ELSE
1159
1160 l_value_string := p_comps_r.value_id;
1161
1162 END IF;
1163
1164
1165 IF ( l_first_time_round )
1166 THEN
1167
1168 -- set string for an sql
1169
1170 IF ( p_an_sql )
1171 THEN
1172 l_string_and := ' AND ( ta.';
1173 END IF;
1174
1175 IF ( p_comps_r.segment IS NOT NULL )
1176 THEN
1177
1178 IF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1179 THEN
1180 IF ( p_an_sql )
1181 THEN
1182 l_dyn_sql := l_dyn_sql
1183 ||l_string_start||p_comps_r.bld_blk_info_type_id
1184 ||l_string_and ||p_comps_r.segment
1185 ||' IS NOT NULL ';
1186 ELSE
1187 l_dyn_sql := l_dyn_sql
1188 ||l_string_start||p_comps_r.bld_blk_info_type_id
1189 ||l_string_and ||p_comps_r.segment
1190 ||' IS NOT NULL )';
1191 END IF;
1192
1193 ELSIF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'N' ) )
1194 THEN
1195
1196 IF ( g_debug ) THEN
1197 hr_utility.trace('GAZ - INVALID COMBO');
1198 END IF;
1199
1200 fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1201 fnd_message.raise_error;
1202
1203 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1204 THEN
1205 IF ( p_an_sql )
1206 THEN
1207 l_dyn_sql := l_dyn_sql
1208 ||l_string_start||p_comps_r.bld_blk_info_type_id
1209 ||l_string_and ||p_comps_r.segment
1210 ||' IS NULL ';
1211 ELSE
1212 l_dyn_sql := l_dyn_sql
1213 ||l_string_start||p_comps_r.bld_blk_info_type_id
1214 ||l_string_and ||p_comps_r.segment
1215 ||' IS NULL )';
1216 END IF;
1217
1218 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'N' ) )
1219 THEN
1220 IF ( p_an_sql )
1221 THEN
1222 l_dyn_sql := l_dyn_sql
1223 ||l_string_start||p_comps_r.bld_blk_info_type_id
1224 ||l_string_and ||p_comps_r.segment
1225 ||' IS NOT NULL ';
1226 ELSE
1227 l_dyn_sql := l_dyn_sql
1228 ||l_string_start||p_comps_r.bld_blk_info_type_id
1229 ||l_string_and ||p_comps_r.segment
1230 ||' IS NOT NULL )';
1231 END IF;
1232
1233 ELSIF ( p_comps_r.equal_to = 'Y' )
1234 THEN
1235 IF ( p_an_sql )
1236 THEN
1237 l_dyn_sql := l_dyn_sql
1238 ||l_string_start||p_comps_r.bld_blk_info_type_id
1239 ||l_string_and ||p_comps_r.segment
1240 ||' = '''||l_value_string||''' ';
1241 ELSIF ( p_vs_sql )
1242 THEN
1243 l_dyn_sql := l_dyn_sql
1244 ||l_string_start||p_comps_r.bld_blk_info_type_id
1245 ||l_string_and ||p_comps_r.segment
1246 ||' IN ( '||l_value_string||' ) ';
1247 ELSE
1248 l_dyn_sql := l_dyn_sql
1249 ||l_string_start||p_comps_r.bld_blk_info_type_id
1250 ||l_string_and ||p_comps_r.segment
1251 ||' = '''||l_value_string||''' )';
1252 END IF;
1253 ELSE
1254 IF ( p_an_sql )
1255 THEN
1256 l_dyn_sql := l_dyn_sql
1257 ||l_string_start||p_comps_r.bld_blk_info_type_id
1258 ||l_string_and ||p_comps_r.segment
1259 ||' <> '''||l_value_string||''' ';
1260 ELSIF ( p_vs_sql )
1261 THEN
1262 l_dyn_sql := l_dyn_sql
1263 ||l_string_start||p_comps_r.bld_blk_info_type_id
1264 ||l_string_and ||p_comps_r.segment
1265 ||' NOT IN ( '||l_value_string||' ) ';
1266 ELSE
1267 l_dyn_sql := l_dyn_sql
1268 ||l_string_start||p_comps_r.bld_blk_info_type_id
1269 ||l_string_and ||p_comps_r.segment
1270 ||' <> '''||l_value_string||''' )';
1271 END IF;
1272 END IF;
1273
1274 ELSE
1275
1276 -- Ignore these TC components
1277 -- EAch Time Category SQL to be evaluated seperately from
1278 -- now on so combining of TIME_SQL not necessary
1279
1280 IF ( g_debug ) THEN
1281 hr_utility.trace('GAZ - another TC !!!!');
1282 END IF;
1283
1284 END IF;
1285
1286 ELSE
1287
1288 IF ( g_debug ) THEN
1289 hr_utility.trace('not first time round');
1290 hr_utility.trace('sql is '||l_dyn_sql);
1291 END IF;
1292
1293 -- set l_string_start for the case when generating SQL for alernate name
1294
1295
1296 IF ( p_comps_r.segment IS NOT NULL )
1297 THEN
1298
1299 IF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1300 THEN
1301
1302 IF ( p_an_sql )
1303 THEN
1304 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1305 ||p_comps_r.segment||' IS NOT NULL ';
1306
1307 ELSE
1308 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1309 ||l_string_start||p_comps_r.bld_blk_info_type_id
1310 ||l_string_and ||p_comps_r.segment
1311 ||' IS NOT NULL )';
1312 END IF;
1313
1314 ELSIF ( ( l_value_string = '<WILDCARD>' ) AND ( p_comps_r.equal_to = 'N' ) )
1315 THEN
1316
1317 IF ( g_debug ) THEN
1318 hr_utility.trace('GAZ - INVALID COMBO');
1319 END IF;
1320
1321 fnd_message.set_name('HXC', 'HXC_TC_INV_EQUAL_IS_NULL_COMBO');
1322 fnd_message.raise_error;
1323
1324 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'Y' ) )
1325 THEN
1326
1327 IF ( p_an_sql )
1328 THEN
1329 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1330 ||p_comps_r.segment||' IS NULL ';
1331 ELSE
1332 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1333 ||l_string_start||p_comps_r.bld_blk_info_type_id
1334 ||l_string_and ||p_comps_r.segment
1335 ||' IS NULL )';
1336 END IF;
1337
1338 ELSIF ( ( l_value_string = '<IS NULL>' ) AND ( p_comps_r.equal_to = 'N' ) )
1339 THEN
1340
1341 IF ( p_an_sql )
1342 THEN
1343 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.'
1344 ||p_comps_r.segment||' IS NOT NULL ';
1345 ELSE
1346 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1347 ||l_string_start||p_comps_r.bld_blk_info_type_id
1348 ||l_string_and ||p_comps_r.segment
1349 ||' IS NOT NULL )';
1350 END IF;
1351
1352 ELSIF ( p_comps_r.equal_to = 'Y' )
1353 THEN
1354
1355 IF ( p_an_sql )
1356 THEN
1357 /* Changes made for the bug 5475464
1358 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.' */
1359 l_dyn_sql := l_dyn_sql||' AND ta.'
1360 /* Changes made for the bug 5475464 */
1361 ||p_comps_r.segment||' = '''||l_value_string||''' ';
1362 ELSE
1363 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1364 ||l_string_start||p_comps_r.bld_blk_info_type_id
1365 ||l_string_and ||p_comps_r.segment
1366 ||' = '''||l_value_string||''' )';
1367 END IF;
1368 ELSE
1369
1370 IF ( p_an_sql )
1371 THEN
1372 /* Changes made for the bug 5475464
1373 l_dyn_sql := l_dyn_sql||' '||p_operator||' ta.' */
1374 l_dyn_sql := l_dyn_sql||' AND ta.'
1375 /* Changes made for the bug 5475464 */
1376 ||p_comps_r.segment||' <> '''||l_value_string||''' ';
1377 ELSE
1378 l_dyn_sql := l_dyn_sql||' '||p_operator||' '
1379 ||l_string_start||p_comps_r.bld_blk_info_type_id
1380 ||l_string_and ||p_comps_r.segment
1381 ||' <> '''||l_value_string||''' )';
1382 END IF;
1383 END IF;
1384
1385 ELSE
1386
1387 -- Ignore these TC components
1388 -- EAch Time Category SQL to be evaluated seperately from
1389 -- now on so combining of TIME_SQL not necessary
1390
1391 IF ( g_debug ) THEN
1392 hr_utility.trace('GAZ - another TC !!!!');
1393 END IF;
1394
1395 END IF;
1396 END IF;
1397
1398 IF ( g_debug ) THEN
1399 hr_utility.trace('dyn sql is '||l_dyn_sql);
1400 END IF;
1401
1402 p_time_sql := l_dyn_sql;
1403
1404 END get_dyn_sql;
1405
1406
1407 FUNCTION get_alternate_name_value ( p_alternate_name_comp_r csr_get_alternate_name_comps%ROWTYPE )
1408 RETURN VARCHAR2 IS
1409
1410 l_return hxc_time_attributes.attribute1%TYPE;
1411
1412 BEGIN
1413
1414 IF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE1' )
1415 THEN
1416 l_return := p_alternate_name_comp_r.attribute1;
1417
1418 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE2' )
1419 THEN
1420 l_return := p_alternate_name_comp_r.attribute2;
1421
1422 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE3' )
1423 THEN
1424 l_return := p_alternate_name_comp_r.attribute3;
1425
1426 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE4' )
1427 THEN
1428 l_return := p_alternate_name_comp_r.attribute4;
1429
1430 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE5' )
1431 THEN
1432 l_return := p_alternate_name_comp_r.attribute5;
1433
1434 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE6' )
1435 THEN
1436 l_return := p_alternate_name_comp_r.attribute6;
1437
1438 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE7' )
1439 THEN
1440 l_return := p_alternate_name_comp_r.attribute7;
1441
1442 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE8' )
1443 THEN
1444 l_return := p_alternate_name_comp_r.attribute8;
1445
1446 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE9' )
1447 THEN
1448 l_return := p_alternate_name_comp_r.attribute9;
1449
1450 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE10' )
1451 THEN
1452 l_return := p_alternate_name_comp_r.attribute10;
1453
1454 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE11' )
1455 THEN
1456 l_return := p_alternate_name_comp_r.attribute11;
1457
1458 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE12' )
1459 THEN
1460 l_return := p_alternate_name_comp_r.attribute12;
1461
1462 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE13' )
1463 THEN
1464 l_return := p_alternate_name_comp_r.attribute13;
1465
1466 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE14' )
1467 THEN
1468 l_return := p_alternate_name_comp_r.attribute14;
1469
1470 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE15' )
1471 THEN
1472 l_return := p_alternate_name_comp_r.attribute15;
1473
1474 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE16' )
1475 THEN
1476 l_return := p_alternate_name_comp_r.attribute16;
1477
1478 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE17' )
1479 THEN
1480 l_return := p_alternate_name_comp_r.attribute17;
1481
1482 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE18' )
1483 THEN
1484 l_return := p_alternate_name_comp_r.attribute18;
1485
1486 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE19' )
1487 THEN
1488 l_return := p_alternate_name_comp_r.attribute19;
1489
1490 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE20' )
1491 THEN
1492 l_return := p_alternate_name_comp_r.attribute20;
1493
1494 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE21' )
1495 THEN
1496 l_return := p_alternate_name_comp_r.attribute21;
1497
1498 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE22' )
1499 THEN
1500 l_return := p_alternate_name_comp_r.attribute22;
1501
1502 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE23' )
1503 THEN
1504 l_return := p_alternate_name_comp_r.attribute23;
1505
1506 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE24' )
1507 THEN
1508 l_return := p_alternate_name_comp_r.attribute24;
1509
1510 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE25' )
1511 THEN
1512 l_return := p_alternate_name_comp_r.attribute25;
1513
1514 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE26' )
1515 THEN
1516 l_return := p_alternate_name_comp_r.attribute26;
1517
1518 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE27' )
1519 THEN
1520 l_return := p_alternate_name_comp_r.attribute27;
1521
1522 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE28' )
1523 THEN
1524 l_return := p_alternate_name_comp_r.attribute28;
1525
1526 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE29' )
1527 THEN
1528 l_return := p_alternate_name_comp_r.attribute29;
1529
1530 ELSIF ( p_alternate_name_comp_r.application_column_name = 'ATTRIBUTE30' )
1531 THEN
1532 l_return := p_alternate_name_comp_r.attribute30;
1533
1534 END IF;
1535
1536 RETURN l_return;
1537
1538 END get_alternate_name_value;
1539
1540
1541
1542
1543 PROCEDURE mapping_component_string ( p_time_category_id NUMBER
1544 , p_time_sql IN OUT NOCOPY CLOB ) IS -- Bug 15977687
1545
1546 l_proc varchar2(72);
1547
1548 l_dynamic_sql CLOB; -- Bug 15977687
1549 l_ref_dyn_sql CLOB; -- Bug 15977687
1550
1551
1552 BEGIN -- mapping_component_string
1553
1554 g_debug := hr_utility.debug_enabled;
1555
1556 l_first_time_round := TRUE;
1557
1558 -- ***************************************
1559 -- MAPPING_COMPONENT_STRING
1560 -- ***************************************
1561
1562 IF ( g_debug ) THEN
1563 l_proc := g_package||'mapping_component_string';
1564 hr_utility.set_location('Processing '||l_proc, 10);
1565
1566 hr_utility.trace('Time Category ID is '||to_char(p_time_category_id));
1567 END IF;
1568
1569 -- get the time category operator
1570
1571 OPEN csr_get_operator ( p_time_category_id);
1572 FETCH csr_get_operator INTO l_operator;
1573 CLOSE csr_get_operator;
1574
1575 -- check for cached value first
1576
1577 -- maintain index value
1578
1579 OPEN csr_get_category_comps ( p_time_category_id );
1580 FETCH csr_get_category_comps INTO l_comps_r;
1581
1582 IF ( g_debug ) THEN
1583 hr_utility.set_location('Processing '||l_proc, 20);
1584 END IF;
1585
1586 WHILE csr_get_category_comps%FOUND
1587 LOOP
1588
1589 IF ( g_debug ) THEN
1590 hr_utility.set_location('Processing '||l_proc, 30);
1591 END IF;
1592
1593 get_dyn_sql ( p_time_sql => l_dynamic_sql
1594 , p_comps_r => l_comps_r
1595 , p_operator => l_operator );
1596
1597 IF ( g_debug ) THEN
1598 hr_utility.set_location('Processing '||l_proc, 60);
1599 END IF;
1600
1601 FETCH csr_get_category_comps INTO l_comps_r;
1602
1603 l_first_time_round := FALSE;
1604
1605 END LOOP;
1606
1607 IF ( g_debug ) THEN
1608 hr_utility.set_location('Processing '||l_proc, 70);
1609 END IF;
1610
1611 CLOSE csr_get_category_comps;
1612
1613 IF ( g_debug ) THEN
1614 hr_utility.set_location('Processing '||l_proc, 80);
1615 END IF;
1616
1617 IF ( l_dynamic_sql IS NOT NULL )
1618 THEN
1619 l_dynamic_sql := ' ( '||l_dynamic_sql||' ) ';
1620
1621 validate_time_category_sql ( l_dynamic_sql );
1622
1623 END IF;
1624
1625 p_time_sql := l_dynamic_sql;
1626
1627 IF ( g_debug ) THEN
1628 hr_utility.trace('Final dyn sql is '||NVL(p_time_sql,'Empty'));
1629 END IF;
1630
1631 END mapping_component_string;
1632
1633
1634
1635
1636 PROCEDURE alternate_name_string ( p_alias_value_id NUMBER
1637 , p_operator VARCHAR2
1638 , p_is_null VARCHAR2
1639 , p_equal_to VARCHAR2
1640 , p_time_sql IN OUT NOCOPY CLOB ) IS -- Bug 15977687
1641
1642 l_proc varchar2(72);
1643
1644 l_dynamic_sql CLOB; -- Bug 15977687
1645 l_ref_dyn_sql CLOB; -- Bug 15977687
1646 l_ind PLS_INTEGER := 1;
1647
1648 l_value hxc_time_attributes.attribute1%TYPE;
1649
1650 l_is_null VARCHAR2(20);
1651
1652 l_comps_t l_comps_tab;
1653
1654 l_first_context hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
1655
1656 BEGIN -- alternate_name_string
1657
1658 g_debug := hr_utility.debug_enabled;
1659
1660 IF ( g_debug ) THEN
1661 l_proc := g_package||'alternate_name_string';
1662 hr_utility.set_location('Entering '||l_proc, 10);
1663 END IF;
1664
1665 l_comps_t.DELETE;
1666
1667 l_first_time_round := TRUE;
1668
1669 -- ***************************************
1670 -- ALTERNATE_NAME_STRING
1671 -- ***************************************
1672
1673 IF ( g_debug ) THEN
1674 hr_utility.set_location('Processing '||l_proc, 20);
1675 END IF;
1676
1677 OPEN csr_get_alternate_name_comps ( p_alias_value_id );
1678 FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1679
1680 l_first_context := l_alternate_name_comps_r.context;
1681
1682 IF ( g_debug ) THEN
1683 hr_utility.set_location('Processing '||l_proc, 25);
1684 END IF;
1685
1686 WHILE csr_get_alternate_name_comps%FOUND
1687 LOOP
1688
1689 IF ( g_debug ) THEN
1690 hr_utility.set_location('Processing '||l_proc, 30);
1691 END IF;
1692
1693 l_value := get_alternate_name_value ( p_alternate_name_comp_r => l_alternate_name_comps_r );
1694
1695 IF ( p_is_null = 'N' )
1696 THEN
1697 l_is_null := '<WILDCARD>';
1698 ELSE
1699 l_is_null := '<IS NULL>';
1700 END IF;
1701
1702 l_comps_t(l_ind).context := l_alternate_name_comps_r.context;
1703 l_comps_t(l_ind).bld_blk_info_type_id := l_alternate_name_comps_r.bld_blk_info_type_id;
1704 l_comps_t(l_ind).segment := l_alternate_name_comps_r.segment;
1705 l_comps_t(l_ind).value_id := NVL(l_value, l_is_null );
1706 l_comps_t(l_ind).ref_time_category_id := -1; -- dummy value not used
1707 l_comps_t(l_ind).flex_value_set_id := -1; -- dummy value not used
1708 l_comps_t(l_ind).equal_to := p_equal_to;
1709
1710 IF ( g_debug ) THEN
1711 hr_utility.set_location('Processing '||l_proc, 60);
1712 END IF;
1713
1714 FETCH csr_get_alternate_name_comps INTO l_alternate_name_comps_r;
1715
1716 l_ind := l_ind + 1;
1717
1718 l_first_time_round := FALSE;
1719
1720 -- Test to make sure that this alternate name is homogenous
1721
1722 IF ( l_first_context <> l_alternate_name_comps_r.context )
1723 THEN
1724 CLOSE csr_get_alternate_name_comps;
1725
1726 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1727 fnd_message.set_token('PROCEDURE', l_proc);
1728 fnd_message.set_token('STEP','Alternate name component contexts different');
1729 fnd_message.raise_error;
1730 END IF;
1731
1732 END LOOP;
1733
1734 IF ( g_debug ) THEN
1735 hr_utility.set_location('Processing '||l_proc, 70);
1736 END IF;
1737
1738 CLOSE csr_get_alternate_name_comps;
1739
1740
1741 -- Now we have a table of the alternate name components
1742 -- Since the alternate name components must all have the context
1743 -- the SQL required from get dyn sql is different
1744
1745 l_first_time_round := TRUE;
1746
1747 l_ind := l_comps_t.FIRST;
1748
1749 WHILE l_ind IS NOT NULL
1750 LOOP
1751
1752 get_dyn_sql ( p_time_sql => l_dynamic_sql
1753 , p_comps_r => l_comps_t(l_ind)
1754 , p_operator => p_operator
1755 , p_an_sql => TRUE );
1756
1757 l_ind := l_comps_t.NEXT(l_ind);
1758
1759 l_first_time_round := FALSE;
1760
1761 END LOOP;
1762
1763 IF ( g_debug ) THEN
1764 hr_utility.set_location('Processing '||l_proc, 80);
1765 END IF;
1766
1767 IF ( l_dynamic_sql IS NOT NULL )
1768 THEN
1769 l_dynamic_sql := ' ( '||l_dynamic_sql||' ) ) )';
1770 END IF;
1771
1772 p_time_sql := l_dynamic_sql;
1773
1774 IF ( g_debug ) THEN
1775 hr_utility.trace('alternate name sql is '||p_time_sql);
1776 END IF;
1777
1778 END alternate_name_string;
1779
1780
1781
1782 PROCEDURE push_attributes ( p_attributes hxc_attribute_table_type ) IS
1783
1784
1785 l_proc VARCHAR2(72) := g_package||'push_attributes';
1786
1787 l_dummy VARCHAR2(1);
1788
1789 CURSOR csr_chk_bld_blks_not_empty IS
1790 SELECT 'x'
1791 FROM dual
1792 WHERE EXISTS ( select 'y'
1793 from hxc_tmp_blks );
1794
1795 t_bb_id tab_bb_id;
1796 t_ta_id tab_time_attribute_id;
1797 t_attribute_category tab_attribute_category;
1798 t_attribute tab_attribute;
1799 t_attribute1 tab_attribute;
1800 t_attribute2 tab_attribute;
1801 t_attribute3 tab_attribute;
1802 t_attribute4 tab_attribute;
1803 t_attribute5 tab_attribute;
1804 t_attribute6 tab_attribute;
1805 t_attribute7 tab_attribute;
1806 t_attribute8 tab_attribute;
1807 t_attribute9 tab_attribute;
1808 t_attribute10 tab_attribute;
1809 t_attribute11 tab_attribute;
1810 t_attribute12 tab_attribute;
1811 t_attribute13 tab_attribute;
1812 t_attribute14 tab_attribute;
1813 t_attribute15 tab_attribute;
1814 t_attribute16 tab_attribute;
1815 t_attribute17 tab_attribute;
1816 t_attribute18 tab_attribute;
1817 t_attribute19 tab_attribute;
1818 t_attribute20 tab_attribute;
1819 t_attribute21 tab_attribute;
1820 t_attribute22 tab_attribute;
1821 t_attribute23 tab_attribute;
1822 t_attribute24 tab_attribute;
1823 t_attribute25 tab_attribute;
1824 t_attribute26 tab_attribute;
1825 t_attribute27 tab_attribute;
1826 t_attribute28 tab_attribute;
1827 t_attribute29 tab_attribute;
1828 t_attribute30 tab_attribute;
1829 t_bld_blk_info_type_id tab_bld_blk_info_type_id;
1830
1831 l_ind PLS_INTEGER;
1832
1833 x PLS_INTEGER := 0;
1834
1835
1836 BEGIN
1837
1838 -- check to make sure bld blk not empty
1839
1840 OPEN csr_chk_bld_blks_not_empty;
1841 FETCH csr_chk_bld_blks_not_empty INTO l_dummy;
1842
1843 IF csr_chk_bld_blks_not_empty%NOTFOUND
1844 THEN
1845
1846 CLOSE csr_chk_bld_blks_not_empty;
1847
1848 -- we did error here but on delete there are never
1849 -- going to be any blocks
1850
1851 ELSE
1852
1853 CLOSE csr_chk_bld_blks_not_empty;
1854
1855 -- populate attribute array
1856
1857 l_ind := p_attributes.FIRST;
1858
1859 WHILE l_ind IS NOT NULL
1860 LOOP
1861 /* removed 'ALTERNATE NAME IDENTIFIERS' from the if condition as part of the fix to bug 5642255 */
1862 IF ( p_attributes(l_ind).attribute_category NOT IN
1863 ( 'TEMPLATES', 'SECURITY', 'REASON', 'LAYOUT', 'APPROVAL' ) )
1864 THEN
1865
1866 x := x + 1;
1867
1868 t_ta_id(x) := p_attributes(l_ind).time_attribute_id;
1869 t_bb_id(x) := p_attributes(l_ind).building_block_id;
1870 t_attribute_category(x) := p_attributes(l_ind).attribute_category;
1871 t_bld_blk_info_type_id(x) := p_attributes(l_ind).bld_blk_info_type_id;
1872 t_attribute1(x) := p_attributes(l_ind).attribute1;
1873 t_attribute2(x) := p_attributes(l_ind).attribute2;
1874 t_attribute3(x) := p_attributes(l_ind).attribute3;
1875 t_attribute4(x) := p_attributes(l_ind).attribute4;
1876 t_attribute5(x) := p_attributes(l_ind).attribute5;
1877 t_attribute6(x) := p_attributes(l_ind).attribute6;
1878 t_attribute7(x) := p_attributes(l_ind).attribute7;
1879 t_attribute8(x) := p_attributes(l_ind).attribute8;
1880 t_attribute9(x) := p_attributes(l_ind).attribute9;
1881 t_attribute10(x) := p_attributes(l_ind).attribute10;
1882 t_attribute11(x) := p_attributes(l_ind).attribute11;
1883 t_attribute12(x) := p_attributes(l_ind).attribute12;
1884 t_attribute13(x) := p_attributes(l_ind).attribute13;
1885 t_attribute14(x) := p_attributes(l_ind).attribute14;
1886 t_attribute15(x) := p_attributes(l_ind).attribute15;
1887 t_attribute16(x) := p_attributes(l_ind).attribute16;
1888 t_attribute17(x) := p_attributes(l_ind).attribute17;
1889 t_attribute18(x) := p_attributes(l_ind).attribute18;
1890 t_attribute19(x) := p_attributes(l_ind).attribute19;
1891 t_attribute20(x) := p_attributes(l_ind).attribute20;
1892 t_attribute21(x) := p_attributes(l_ind).attribute21;
1893 t_attribute22(x) := p_attributes(l_ind).attribute22;
1894 t_attribute23(x) := p_attributes(l_ind).attribute23;
1895 t_attribute24(x) := p_attributes(l_ind).attribute24;
1896 t_attribute25(x) := p_attributes(l_ind).attribute25;
1897 t_attribute26(x) := p_attributes(l_ind).attribute26;
1898 t_attribute27(x) := p_attributes(l_ind).attribute27;
1899 t_attribute28(x) := p_attributes(l_ind).attribute28;
1900 t_attribute29(x) := p_attributes(l_ind).attribute29;
1901 t_attribute30(x) := p_attributes(l_ind).attribute30;
1902
1903 END IF;
1904
1905 l_ind := p_attributes.NEXT(l_ind);
1906
1907 END LOOP;
1908
1909 -- attribute insert
1910
1911 FORALL attx IN 1 .. x
1912
1913 INSERT INTO hxc_tmp_atts (
1914 ta_id
1915 , bb_id
1916 , attribute1
1917 , attribute2
1918 , attribute3
1919 , attribute4
1920 , attribute5
1921 , attribute6
1922 , attribute7
1923 , attribute8
1924 , attribute9
1925 , attribute10
1926 , attribute11
1927 , attribute12
1928 , attribute13
1929 , attribute14
1930 , attribute15
1931 , attribute16
1932 , attribute17
1933 , attribute18
1934 , attribute19
1935 , attribute20
1936 , attribute21
1937 , attribute22
1938 , attribute23
1939 , attribute24
1940 , attribute25
1941 , attribute26
1942 , attribute27
1943 , attribute28
1944 , attribute29
1945 , attribute30
1946 , bld_blk_info_type_id
1947 , attribute_category )
1948 VALUES (
1949 t_ta_id(attx)
1950 , t_bb_id(attx)
1951 , t_attribute1(attx)
1952 , t_attribute2(attx)
1953 , t_attribute3(attx)
1954 , t_attribute4(attx)
1955 , t_attribute5(attx)
1956 , t_attribute6(attx)
1957 , t_attribute7(attx)
1958 , t_attribute8(attx)
1959 , t_attribute9(attx)
1960 , t_attribute10(attx)
1961 , t_attribute11(attx)
1962 , t_attribute12(attx)
1963 , t_attribute13(attx)
1964 , t_attribute14(attx)
1965 , t_attribute15(attx)
1966 , t_attribute16(attx)
1967 , t_attribute17(attx)
1968 , t_attribute18(attx)
1969 , t_attribute19(attx)
1970 , t_attribute20(attx)
1971 , t_attribute21(attx)
1972 , t_attribute22(attx)
1973 , t_attribute23(attx)
1974 , t_attribute24(attx)
1975 , t_attribute25(attx)
1976 , t_attribute26(attx)
1977 , t_attribute27(attx)
1978 , t_attribute28(attx)
1979 , t_attribute29(attx)
1980 , t_attribute30(attx)
1981 , t_bld_blk_info_type_id(attx)
1982 , t_attribute_category(attx) );
1983
1984 hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count := x;
1985
1986 -- delete attribute array
1987
1988 t_bb_id.delete;
1989 t_ta_id.delete;
1990 t_bld_blk_info_type_id.delete;
1991 t_attribute_category.delete;
1992 t_attribute1.delete;
1993 t_attribute2.delete;
1994 t_attribute3.delete;
1995 t_attribute4.delete;
1996 t_attribute5.delete;
1997 t_attribute6.delete;
1998 t_attribute7.delete;
1999 t_attribute8.delete;
2000 t_attribute9.delete;
2001 t_attribute10.delete;
2002 t_attribute11.delete;
2003 t_attribute12.delete;
2004 t_attribute13.delete;
2005 t_attribute14.delete;
2006 t_attribute15.delete;
2007 t_attribute16.delete;
2008 t_attribute17.delete;
2009 t_attribute18.delete;
2010 t_attribute19.delete;
2011 t_attribute20.delete;
2012 t_attribute21.delete;
2013 t_attribute22.delete;
2014 t_attribute23.delete;
2015 t_attribute24.delete;
2016 t_attribute25.delete;
2017 t_attribute26.delete;
2018 t_attribute27.delete;
2019 t_attribute28.delete;
2020 t_attribute29.delete;
2021 t_attribute30.delete;
2022
2023 END IF; -- IF csr_chk_bld_blks_not_empty%NOTFOUND
2024
2025 END push_attributes;
2026
2027
2028 PROCEDURE push_attributes ( p_attributes hxc_self_service_time_deposit.building_block_attribute_info ) IS
2029
2030 l_attributes hxc_attribute_table_type;
2031
2032 BEGIN
2033
2034 l_attributes := hxc_deposit_wrapper_utilities.attributes_to_array(
2035 p_attributes => p_attributes );
2036
2037 push_attributes ( l_attributes );
2038
2039 END push_attributes;
2040
2041 procedure gaz_debug_push_timecard is
2042
2043 CURSOR gaz_blk IS
2044 SELECT *
2045 from hxc_tmp_blks;
2046
2047 CURSOR gaz_att IS
2048 SELECT *
2049 from hxc_tmp_atts;
2050
2051 l_blk gaz_blk%ROWTYPE;
2052 l_att gaz_att%ROWTYPE;
2053
2054 begin
2055
2056
2057
2058 IF ( g_debug ) THEN
2059 hr_utility.trace('Here are the build blocks....');
2060 END IF;
2061
2062 OPEN gaz_blk;
2063 FETCH gaz_blk into l_blk;
2064
2065 WHILE gaz_blk%FOUND
2066 LOOP
2067
2068 IF ( g_debug ) THEN
2069 hr_utility.trace('bb id is : '||to_char(l_blk.bb_id));
2070 hr_utility.trace('measure is : '||to_char(l_blk.measure));
2071 hr_utility.trace('scope is : '||l_blk.scope);
2072 hr_utility.trace('start time : '||to_char(l_blk.start_time,'hh24:mi:ss dd-mon-yy'));
2073 hr_utility.trace('stop time : '||to_char(l_blk.stop_time,'hh24:mi:ss dd-mon-yy'));
2074 END IF;
2075
2076 FETCH gaz_blk INTO l_blk;
2077
2078 END LOOP;
2079
2080 CLOSE gaz_blk;
2081
2082 IF ( g_debug ) THEN
2083 hr_utility.trace('Here are the attributes ....');
2084 END IF;
2085
2086 OPEN gaz_att;
2087 FETCH gaz_att into l_att;
2088
2089 WHILE gaz_att%FOUND
2090 LOOP
2091
2092 IF ( g_debug ) THEN
2093 hr_utility.trace('ta id is : '||to_char(l_att.ta_id));
2094 hr_utility.trace('bb id is : '||to_char(l_att.bb_id));
2095 hr_utility.trace('bb info is : '||to_char(l_att.bld_blk_info_type_id));
2096 hr_utility.trace('att cat is : '||l_att.attribute_category);
2097 hr_utility.trace('bbit id is : '||to_char(l_att.bld_blk_info_type_id));
2098 hr_utility.trace('att 1 is : '||l_att.attribute1);
2099 hr_utility.trace('att 2 is : '||l_att.attribute2);
2100 hr_utility.trace('att 3 is : '||l_att.attribute3);
2101 hr_utility.trace('att 4 is : '||l_att.attribute4);
2102 hr_utility.trace('att 5 is : '||l_att.attribute5);
2103 END IF;
2104
2105 FETCH gaz_att INTO l_att;
2106
2107 END LOOP;
2108
2109 CLOSE gaz_att;
2110
2111 end gaz_debug_push_timecard;
2112
2113
2114
2115 -- public procedure
2116 -- push_timecard
2117 --
2118 -- description
2119 --
2120 -- SEE PACKAGE HEADER
2121
2122 PROCEDURE push_timecard ( p_blocks hxc_block_table_type,
2123 p_attributes hxc_attribute_table_type,
2124 p_detail_blocks_only BOOLEAN ) IS
2125
2126
2127 TYPE day_index_tab IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
2128 l_day_index_tab day_index_tab;
2129
2130 l_proc VARCHAR2(72);
2131
2132 t_bb_id tab_bb_id;
2133 t_measure tab_measure;
2134 t_type tab_type;
2135 t_start_time tab_start_time;
2136 t_stop_time tab_stop_time;
2137 t_scope tab_scope;
2138 t_comment_text tab_comment_text;
2139
2140 l_ind PLS_INTEGER;
2141
2142 x PLS_INTEGER := 0;
2143
2144 l_start_time DATE;
2145 l_stop_time DATE;
2146
2147 l_trunc_blks VARCHAR2(30) := 'delete from hxc_tmp_blks';
2148 l_trunc_atts VARCHAR2(30) := 'delete from hxc_tmp_atts';
2149
2150 BEGIN
2151
2152 g_debug := hr_utility.debug_enabled;
2153
2154 IF ( g_debug ) THEN
2155 l_proc := g_package||'push_timecard';
2156 hr_utility.set_location('Entering '||l_proc, 10);
2157 END IF;
2158
2159 execute immediate l_trunc_blks;
2160 execute immediate l_trunc_atts;
2161
2162 hxc_time_category_utils_pkg.g_tc_bb_not_ok_string := NULL;
2163
2164 -- populate bld blk array
2165
2166 l_ind := p_blocks.FIRST;
2167
2168 WHILE l_ind is not null
2169 loop
2170
2171 IF ( g_debug ) THEN
2172 hr_utility.trace('scope is '||p_blocks(l_ind).scope);
2173 hr_utility.trace('start time is '||p_blocks(l_ind).start_time);
2174 hr_utility.trace('stop time is '||p_blocks(l_ind).stop_time);
2175 END IF;
2176
2177 l_ind := p_blocks.NEXT(l_ind);
2178
2179 end loop;
2180
2181 l_ind := p_blocks.FIRST;
2182
2183 WHILE l_ind IS NOT NULL
2184 LOOP
2185
2186 -- always set the master timecard id
2187
2188 IF ( p_blocks(l_ind).scope = 'TIMECARD' )
2189 THEN
2190 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id := p_blocks(l_ind).time_building_block_id;
2191 END IF;
2192
2193 -- only copy blocks which are NOT deleted
2194
2195 IF ( FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).date_to) = hr_general.end_of_time )
2196 THEN
2197
2198 IF ( g_debug ) THEN
2199 hr_utility.trace('Scope : start time '||p_blocks(l_ind).scope||' : '||p_blocks(l_ind).start_time);
2200 END IF;
2201
2202 IF ( NOT p_detail_blocks_only )
2203 THEN
2204
2205 IF ( p_blocks(l_ind).scope = 'DAY' )
2206 THEN
2207
2208 l_day_index_tab(p_blocks(l_ind).time_building_block_id) := l_ind;
2209
2210 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).start_time);
2211 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).stop_time);
2212
2213 ELSIF ( p_blocks(l_ind).scope = 'DETAIL' )
2214 THEN
2215
2216 IF ( p_blocks(l_ind).type = 'MEASURE' )
2217 THEN
2218
2219 IF ( NOT l_day_index_tab.EXISTS(p_blocks(l_ind).parent_building_block_id))
2220 THEN
2221 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2222 fnd_message.set_token('PROCEDURE', l_proc);
2223 fnd_message.set_token('STEP','DAY index does not exist ');
2224 fnd_message.raise_error;
2225 END IF;
2226
2227 l_start_time :=
2228 FND_DATE.CANONICAL_TO_DATE(p_blocks(l_day_index_tab(p_blocks(l_ind).parent_building_block_id)).start_time);
2229 l_stop_time :=
2230 FND_DATE.CANONICAL_TO_DATE(p_blocks(l_day_index_tab(p_blocks(l_ind).parent_building_block_id)).stop_time);
2231
2232 ELSE
2233
2234 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).start_time);
2235 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).stop_time);
2236
2237 END IF; -- ( p_blocks(l_ind).type = 'MEASURE' )
2238
2239 ELSE
2240
2241 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).start_time);
2242 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).stop_time);
2243
2244 END IF; -- scope test
2245
2246 ELSE -- all DETAILS with date already denormalised from DAY
2247
2248 l_start_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).start_time);
2249 l_stop_time := FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).stop_time);
2250
2251 END IF; -- IF ( NOT p_details_only )
2252
2253 x := x + 1;
2254
2255 t_bb_id(x) := p_blocks(l_ind).time_building_block_id;
2256 t_measure(x) := p_blocks(l_ind).measure;
2257 t_type(x) := p_blocks(l_ind).type;
2258 t_start_time(x) := l_start_time;
2259 t_stop_time(x) := l_stop_time;
2260 t_scope(x) := p_blocks(l_ind).scope;
2261 t_comment_text(x) := p_blocks(l_ind).comment_text;
2262
2263 -- maintain global tc bb not ok string
2264
2265 IF ( t_scope(x) = 'DETAIL' )
2266 THEN
2267
2268 IF ( hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL )
2269 THEN
2270
2271 hxc_time_category_utils_pkg.g_tc_bb_not_ok_string := t_bb_id(x);
2272
2273 ELSE
2274
2275 hxc_time_category_utils_pkg.g_tc_bb_not_ok_string
2276 := hxc_time_category_utils_pkg.g_tc_bb_not_ok_string || ', ' || t_bb_id(x);
2277
2278 END IF;
2279
2280 END IF; -- t_scope(x) = 'DETAIL'
2281
2282
2283 END IF; -- IF ( FND_DATE.CANONICAL_TO_DATE(p_blocks(l_ind).date_to) = hr_general.end_of_time )
2284
2285 l_ind := p_blocks.NEXT(l_ind);
2286
2287 END LOOP;
2288
2289 -- blk insert
2290
2291 FORALL blkx IN 1 .. x
2292
2293 INSERT INTO hxc_tmp_blks (
2294 bb_id
2295 , measure
2296 , type
2297 , start_time
2298 , stop_time
2299 , scope
2300 , comment_text )
2301 VALUES (
2302 t_bb_id(blkx)
2303 , t_measure(blkx)
2304 , t_type(blkx)
2305 , t_start_time(blkx)
2306 , t_stop_time(blkx)
2307 , t_scope(blkx)
2308 , t_comment_text(blkx) );
2309
2310 -- delete bld blk array
2311
2312 t_bb_id.delete;
2313 t_measure.delete;
2314 t_type.delete;
2315 t_start_time.delete;
2316 t_stop_time.delete;
2317 t_comment_text.delete;
2318 t_scope.delete;
2319
2320 push_attributes ( p_attributes );
2321
2322
2323 /* **********************************************
2324
2325 Debug Section
2326
2327 ********************************************** */
2328
2329 -- gaz_debug_push_timecard;
2330
2331 IF ( g_debug ) THEN
2332 hr_utility.set_location('Leaving '||l_proc, 110);
2333 END IF;
2334
2335 END push_timecard;
2336
2337
2338 -- private procedure
2339 -- evaluate_time_sql
2340 --
2341 -- description
2342 --
2343 -- Evaluates the given time category's TIME_SQL against the timecard
2344 -- stored in the temporary table
2345
2346 -- Returns a table of time building blocks which satisfied the TIME_SQL
2347
2348 -- parameters
2349 -- p_time_sql - Time Category's TIME_SQL
2350 -- p_tc_bb_ok_tab - Table of Valid bb ids
2351 -- p_tc_bb_ok_string - string of the valid building blocks
2352 -- p_tc_bb_not_ok_string - string of the building blocks which are still not OK
2353 -- p_operator - time category operator
2354
2355 PROCEDURE evaluate_time_sql ( p_time_sql CLOB -- Bug 15977687
2356 , p_time_sql_clob CLOB
2357 , p_tc_bb_ok_tab IN OUT NOCOPY t_tc_bb_ok
2358 , p_tc_bb_ok_string IN OUT NOCOPY VARCHAR2
2359 , p_tc_bb_not_ok_string IN OUT NOCOPY VARCHAR2
2360 , p_operator IN VARCHAR2 ) IS
2361
2362 l_proc VARCHAR2(72);
2363
2364
2365 l_select VARCHAR2(75) := '
2366 SELECT DISTINCT ta.bb_id
2367 FROM hxc_tmp_atts ta
2368 WHERE ';
2369
2370 l_live_detail VARCHAR2(300) := '
2371 SELECT DISTINCT tau.time_building_block_id bb_id
2372 FROM hxc_time_attributes ta
2373 , hxc_time_attribute_usages tau
2374 WHERE tau.time_building_block_id = :p_tbb_id AND
2375 tau.time_building_block_ovn = :p_tbb_ovn
2376 AND ta.time_attribute_id = tau.time_attribute_id
2377 AND ';
2378
2379 l_live_timecard VARCHAR2(875) := '
2380 SELECT DISTINCT detail.time_building_block_id bb_id
2381 from hxc_time_attributes ta,
2382 hxc_time_attribute_usages tau,
2383 hxc_latest_details tbb_latest,
2384 hxc_time_building_blocks detail,
2385 hxc_time_building_blocks day
2386 where day.parent_building_block_id = :p_tbb_id
2387 and day.parent_building_block_ovn = :p_tbb_ovn
2388 and detail.parent_building_block_id =
2389 day.time_building_block_id
2390 and detail.parent_building_block_ovn =
2391 day.object_version_number
2392 and detail.date_to = hr_general.end_of_time
2393 and tbb_latest.time_building_block_id = detail.time_building_Block_id
2394 and tbb_latest.object_version_number = detail.object_version_number
2395 and tau.time_building_block_id = tbb_latest.time_building_block_id
2396 and tau.time_building_block_ovn = tbb_latest.object_Version_number
2397 AND ta.time_attribute_id = tau.time_attribute_id
2398 AND ';
2399
2400 l_sql CLOB; -- Bug 15977687
2401
2402 t_bb_id dbms_sql.Number_Table;
2403
2404 l_csr INTEGER;
2405 l_rows_fetched INTEGER;
2406 l_dummy INTEGER;
2407
2408 l_bb_ok_string VARCHAR2(32000);
2409 l_bb_not_ok_string VARCHAR2(32000);
2410
2411 l_time_sql CLOB; -- Bug 15977687
2412
2413 l_parse_time_sql CLOB; -- Bug 15977687
2414
2415
2416 l_between_or varchar2(32000) := 'not null';
2417 l_before_and varchar2(32000);
2418 l_after_and varchar2(32000);
2419 l_before_equal1 varchar2(32000);
2420 l_before_equal2 varchar2(32000);
2421
2422 -- Bug 8589919
2423 l_index number :=1000;
2424
2425 BEGIN
2426
2427 IF ( p_time_sql IS NOT NULL )
2428 THEN
2429 l_time_sql := p_time_sql;
2430 ELSE
2431 l_time_sql := p_time_sql_clob; -- Bug 15977687
2432 END IF;
2433
2434
2435 IF ( g_debug ) THEN
2436 l_proc := g_package||'evaluate_time_sql';
2437 hr_utility.trace('Params for Evaluate Time SQL are ....');
2438
2439 hr_utility.trace('p_time_sql is : '|| l_time_sql );
2440 hr_utility.trace('p_tc_bb_ok_string is : '||p_tc_bb_ok_string);
2441 hr_utility.trace('p_tc_bb_not_ok_string is : '||p_tc_bb_not_ok_string);
2442 hr_utility.trace('p_operator is : '||p_operator);
2443
2444 hr_utility.set_location('Entering '||l_proc, 10);
2445 END IF;
2446
2447
2448 l_bb_not_ok_string := p_tc_bb_not_ok_string;
2449 l_bb_ok_string := p_tc_bb_ok_string;
2450
2451 l_parse_time_sql := parse_time_sql_to_bind(l_time_sql);
2452
2453 IF g_debug
2454 THEN
2455
2456 hr_utility.trace('l_parse_time_sql is ');
2457 hr_utility.trace( substr(l_parse_time_sql,1,250) );
2458 hr_utility.trace( substr(l_parse_time_sql,251,250) );
2459 hr_utility.trace( substr(l_parse_time_sql,501,250) );
2460 hr_utility.trace( substr(l_parse_time_sql,751,250) );
2461 hr_utility.trace( substr(l_parse_time_sql,1001,250) );
2462 hr_utility.trace( substr(l_parse_time_sql,1251,250) );
2463 hr_utility.trace( substr(l_parse_time_sql,1501,250) );
2464 hr_utility.trace( substr(l_parse_time_sql,1751,250) );
2465
2466 END IF;
2467
2468 IF ( g_params.p_use_temp_table )
2469 THEN
2470
2471 IF g_debug
2472 THEN
2473 hr_utility.trace('g_params.p_use_temp_table is TRUE ');
2474 hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2475 END IF;
2476
2477 IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )THEN
2478
2479 l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_ok_string)||' ) ';
2480 p_tc_bb_ok_tab.DELETE;
2481 ELSIF ( p_operator = 'OR' ) AND ( p_tc_bb_not_ok_string IS NOT NULL ) THEN
2482
2483 l_sql := l_select || l_parse_time_sql || ' AND ta.bb_id IN ( '||get_token_string (p_tc_bb_not_ok_string)||' ) ';
2484
2485 ELSIF ( p_operator = 'AND' ) THEN
2486 l_sql := l_select || l_parse_time_sql;
2487
2488 END IF;
2489
2490 ELSIF ( g_params.p_scope = 'DETAIL' )
2491 THEN
2492
2493
2494 IF g_debug
2495 THEN
2496 hr_utility.trace(' g_params.p_scope is DETAIL ');
2497 hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2498 END IF;
2499
2500 IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2501 THEN
2502 l_sql := l_live_detail || l_parse_time_sql ||
2503 --' AND tau.time_building_block_id IN ( ' || p_tc_bb_ok_string || ' ) ';
2504 ' AND tau.time_building_block_id IN ( ' || get_token_string(p_tc_bb_ok_string) || ' ) ';
2505 p_tc_bb_ok_tab.DELETE;
2506
2507 ELSIF ( p_operator = 'AND' )
2508 THEN
2509 l_sql := l_live_detail || l_parse_time_sql;
2510 ELSIF ( p_operator = 'OR' )
2511 THEN
2512 l_sql := l_live_detail || l_parse_time_sql ||
2513 --' AND tau.time_building_block_id IN ( ' || p_tc_bb_not_ok_string || ' ) ';
2514 ' AND tau.time_building_block_id IN ( ' || get_token_string(p_tc_bb_not_ok_string) || ' ) ';
2515
2516 END IF;
2517
2518 ELSIF ( g_params.p_scope = 'TIME' )
2519 THEN
2520
2521 IF g_debug
2522 THEN
2523 hr_utility.trace(' g_params.p_scope is TIME ');
2524 hr_utility.trace('p_operator is '||p_operator||' and p_tc_bb_ok_string is '||p_tc_bb_ok_string);
2525 END IF;
2526
2527
2528 -- sum for timecard
2529
2530 IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2531 THEN
2532
2533 l_sql := l_live_timecard || l_parse_time_sql ||
2534 --' AND tau.time_building_block_id IN ( ' || p_tc_bb_ok_string || ' ) ';
2535 ' AND tau.time_building_block_id IN ( ' || get_token_string(p_tc_bb_ok_string) || ' ) ';
2536
2537 p_tc_bb_ok_tab.DELETE;
2538
2539 ELSIF ( p_operator = 'AND' )
2540 THEN
2541 l_sql := l_live_timecard || l_parse_time_sql;
2542
2543 ELSIF ( p_operator = 'OR' )
2544 THEN
2545
2546 l_sql := l_live_timecard || l_parse_time_sql ||
2547 --' AND tau.time_building_block_id IN ( ' || p_tc_bb_not_ok_string || ' ) ';
2548 ' AND tau.time_building_block_id IN ( ' || get_token_string(p_tc_bb_not_ok_string) || ' ) ';
2549
2550 END IF;
2551
2552
2553 END IF; -- p_use_temp_table
2554
2555 IF ( g_debug ) THEN
2556 hr_utility.trace( 'dynamic time sql is ');
2557 hr_utility.trace( substr(l_sql,1,250) );
2558 hr_utility.trace( substr(l_sql,251,250) );
2559 hr_utility.trace( substr(l_sql,501,250) );
2560 hr_utility.trace( substr(l_sql,751,250) );
2561 hr_utility.trace( substr(l_sql,1001,250) );
2562 hr_utility.trace( substr(l_sql,1251,250) );
2563 hr_utility.trace( substr(l_sql,1501,250) );
2564 hr_utility.trace( substr(l_sql,1751,250) );
2565 END IF;
2566
2567 -- for the AND operator need to reset p_tc_bb_ok_string
2568 -- since each AND evaluation should start from scratch
2569
2570 IF ( p_operator = 'AND' )
2571 THEN
2572
2573 l_bb_ok_string := NULL;
2574
2575 END IF;
2576
2577 -- now fetch rows
2578
2579 l_rows_fetched := 100;
2580
2581 l_csr := dbms_sql.open_cursor;
2582
2583 dbms_sql.parse ( l_csr, l_sql, dbms_sql.native );
2584
2585 IF ( NOT g_params.p_use_temp_table )
2586 THEN
2587
2588 dbms_sql.bind_variable ( l_csr, ':p_tbb_id' , g_params.p_tbb_id );
2589 dbms_sql.bind_variable ( l_csr, ':p_tbb_ovn', g_params.p_tbb_ovn );
2590
2591
2592 IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) )
2593 THEN
2594
2595 FOR i IN 1..get_token_count(p_tc_bb_ok_string,',') LOOP
2596 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_ok_string,i,',' ));
2597 END LOOP;
2598
2599 ELSIF ( p_operator = 'OR' )
2600 THEN
2601
2602 FOR i IN 1..get_token_count(p_tc_bb_not_ok_string,',') LOOP
2603 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_not_ok_string,i,',' ));
2604 END LOOP;
2605
2606 END IF;
2607
2608 ELSE
2609
2610 IF ( ( p_operator = 'AND' ) AND ( p_tc_bb_ok_string IS NOT NULL ) ) THEN
2611
2612 FOR i IN 1..get_token_count(p_tc_bb_ok_string,',') LOOP
2613
2614 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_ok_string,i,',' ));
2615
2616 END LOOP;
2617
2618 ELSIF ( p_operator = 'OR' ) AND ( p_tc_bb_not_ok_string IS NOT NULL ) THEN
2619
2620 FOR i IN 1..get_token_count(p_tc_bb_not_ok_string,',') LOOP
2621 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_not_ok_string,i,',' ));
2622 END LOOP;
2623
2624
2625 END IF;
2626 -- replace the parse_time_sql bind
2627
2628 END IF;
2629
2630 -- replace the parse_time_sql bind
2631
2632 WHILE l_between_or is not null
2633 LOOP
2634 l_between_or := get_token (l_time_sql,l_index,' OR ');
2635
2636 IF (l_between_or IS NOT NULL AND get_token (l_between_or,3,' AND ') IS NULL) THEN
2637 -- now I look for the and
2638 l_before_and := get_token (l_between_or,1,'AND');
2639 l_after_and := get_token (l_between_or,2,'AND');
2640
2641 -- now I can replace the value with the bind
2642 l_before_equal1 := replace(get_token (l_before_and,2,'='),')','');
2643 l_before_equal1 := trim(replace(l_before_equal1,'''',''));
2644 -- Bug 8589919
2645 dbms_sql.bind_variable( l_csr, ':'||l_index, l_before_equal1 );
2646
2647 IF (instr(l_after_and,'IS NOT NULL') = 0 AND instr(l_after_and,'IS NULL') = 0
2648 AND instr(l_after_and,'<>') = 0 AND instr(l_after_and,'IN') = 0) THEN
2649
2650 -- now I can replace the value with the bind
2651 l_before_equal2 := replace(get_token (l_after_and,2,'='),')','');
2652 l_before_equal2 := trim(replace(l_before_equal2,'''',''));
2653
2654 -- Bug 8589919
2655 dbms_sql.bind_variable( l_csr, ':'||((l_index)+1),l_before_equal2);
2656
2657 END IF;
2658
2659 END IF;
2660 -- Bug 8589919
2661 l_index := l_index + 5;
2662
2663 END LOOP;
2664
2665
2666
2667 dbms_sql.define_array (
2668 c => l_csr
2669 , position => 1
2670 , n_tab => t_bb_id
2671 , cnt => l_rows_fetched
2672 , lower_bound => 1 );
2673
2674 l_dummy := dbms_sql.execute ( l_csr );
2675
2676 -- loop to ensure we fetch all the rows
2677
2678 WHILE ( l_rows_fetched = 100 )
2679 LOOP
2680
2681 l_rows_fetched := dbms_sql.fetch_rows ( l_csr );
2682
2683 IF ( g_debug ) THEN
2684 hr_utility.trace('l rows fetched is '||to_char(l_rows_fetched));
2685 END IF;
2686
2687 IF ( l_rows_fetched > 0 )
2688 THEN
2689
2690 dbms_sql.column_value (
2691 c => l_csr
2692 , position => 1
2693 , n_tab => t_bb_id );
2694
2695 -- populate p_tc_bb_ok_tab and calc DETAIL hrs
2696
2697 FOR x IN t_bb_id.FIRST .. t_bb_id.LAST
2698 LOOP
2699
2700 p_tc_bb_ok_tab(t_bb_id(x)).bb_id_ok := 'Y';
2701
2702 IF ( g_debug ) THEN
2703 hr_utility.trace('bb ok id is '||to_char(t_bb_id(x)));
2704 END IF;
2705
2706 -- maintain bb ok string for OR operator
2707
2708 IF ( x = 1 AND l_bb_ok_string IS NULL )
2709 THEN
2710 l_bb_ok_string := to_char(t_bb_id(x));
2711 ELSE
2712 l_bb_ok_string := l_bb_ok_string || ', ' || to_char(t_bb_id(x));
2713 END IF;
2714
2715 IF ( g_debug ) THEN
2716 hr_utility.trace('bb ok string is '||l_bb_ok_string);
2717 hr_utility.trace('bb NOT ok string is '||l_bb_not_ok_string);
2718 END IF;
2719
2720 -- maintain bb not ok string i.e. remove the building block from the string
2721
2722 IF ( SUBSTR( l_bb_not_ok_string, ( INSTR( l_bb_not_ok_string, t_bb_id(x)) + LENGTH(t_bb_id(x))),1)
2723 = ',' )
2724 THEN
2725
2726 -- bb id is followed by a comma
2727
2728 l_bb_not_ok_string := REPLACE( l_bb_not_ok_string, to_char(t_bb_id(x))||', ');
2729
2730 ELSIF ( LENGTH( t_bb_id(x) ) < LENGTH( l_bb_not_ok_string ) )
2731 THEN
2732 -- bb id not followed by comma and not the last bb id
2733 -- therefore remove blocks and preceeding comma
2734
2735 l_bb_not_ok_string := REPLACE( l_bb_not_ok_string, ', '||to_char(t_bb_id(x)));
2736
2737 ELSE
2738 -- bb id is the last block in list and the last block therfore
2739 -- just remove the block - no comma
2740
2741 l_bb_not_ok_string := REPLACE( l_bb_not_ok_string, to_char(t_bb_id(x)));
2742
2743 END IF;
2744
2745 IF ( g_debug ) THEN
2746 hr_utility.trace('bb NOT ok string AFTER is '||l_bb_not_ok_string);
2747 END IF;
2748
2749 END LOOP;
2750
2751 t_bb_id.DELETE;
2752
2753 END IF; -- l_rows_fetched > 0
2754
2755 END LOOP;
2756
2757 dbms_sql.close_cursor ( l_csr );
2758
2759 IF ( g_debug ) THEN
2760 hr_utility.trace('GAZ - BB OK string is '||l_bb_ok_string);
2761 hr_utility.trace('GAZ - BB NOT OK string is '||l_bb_not_ok_string);
2762 END IF;
2763
2764 p_tc_bb_ok_string := l_bb_ok_string;
2765 p_tc_bb_not_ok_string := l_bb_not_ok_string;
2766
2767 IF ( g_debug ) THEN
2768 hr_utility.set_location('Leaving '||l_proc, 170);
2769 END IF;
2770
2771 exception when others then
2772
2773 IF ( g_debug ) THEN
2774 hr_utility.trace('in exception');
2775 END IF;
2776
2777 raise;
2778
2779 END evaluate_time_sql;
2780
2781
2782 -- private procedure
2783 -- value_set_string
2784 --
2785 -- Description
2786 --
2787 -- Creates the dynamic sql string associated with the TCC
2788
2789 -- parameters
2790 -- p_rec - Time Category Component record
2791 -- p_vs_sql - dynamic sql string
2792
2793
2794 PROCEDURE value_set_string ( p_rec hxc_tcc_shd.g_rec_type
2795 , p_vs_sql IN OUT NOCOPY LONG ) IS
2796
2797 CURSOR csr_get_mpc_info ( p_mapping_component_id NUMBER ) IS
2798 SELECT
2799 bbit.bld_blk_info_type context
2800 , bbit.bld_blk_info_type_id
2801 , mpc.segment
2802 FROM
2803 hxc_bld_blk_info_types bbit
2804 , hxc_mapping_components mpc
2805 WHERE
2806 mpc.mapping_component_id = p_mapping_component_id
2807 AND
2808 bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id;
2809
2810
2811 l_comps_r csr_get_category_comps%ROWTYPE;
2812
2813 l_mpc_r csr_get_mpc_info%ROWTYPE;
2814
2815 l_sql LONG;
2816 l_vs_sql LONG;
2817
2818 r_valueset fnd_vset.valueset_r;
2819 r_format fnd_vset.valueset_dr;
2820
2821 l_where_clause VARCHAR2(32000);
2822 l_sql_ok BOOLEAN;
2823
2824 l_order_by_start NUMBER;
2825
2826
2827 BEGIN -- value_set_string
2828
2829
2830
2831 l_first_time_round := TRUE;
2832
2833 IF ( p_rec.type <> 'MC_VS' )
2834 THEN
2835 fnd_message.set_name ('HXC','HXC_GAZ_NOT_A_VS_ROW');
2836 fnd_message.raise_error;
2837 END IF;
2838
2839 -- first check the SQL associated with the value set has
2840 -- no $FLEX$ OR $PROFILE$
2841
2842 chk_profile_flex( p_rec.flex_value_set_id
2843 , l_where_clause
2844 , l_sql_ok);
2845
2846 IF ( NOT l_sql_ok )
2847 THEN
2848 fnd_message.set_name('HXC', 'HXC_TCC_CANNOT_USE_MPC');
2849 fnd_message.raise_error;
2850 END IF;
2851
2852 -- get the time category operator
2853
2854 OPEN csr_get_operator ( p_rec.time_category_id );
2855 FETCH csr_get_operator INTO l_operator;
2856 CLOSE csr_get_operator;
2857
2858 -- get the mapping component information
2859
2860 OPEN csr_get_mpc_info ( p_rec.component_type_id );
2861 FETCH csr_get_mpc_info INTO l_mpc_r;
2862 CLOSE csr_get_mpc_info;
2863
2864
2865 l_comps_r.context := l_mpc_r.context;
2866 l_comps_r.bld_blk_info_type_id := l_mpc_r.bld_blk_info_type_id;
2867 l_comps_r.segment := l_mpc_r.segment;
2868 l_comps_r.value_id := '<VALUE_SET_SQL>';
2869 l_comps_r.ref_time_category_id := -1; -- dummy value not used
2870 l_comps_r.flex_value_set_id := p_rec.flex_value_set_id;
2871 l_comps_r.equal_to := p_rec.equal_to;
2872
2873 get_dyn_sql ( p_time_sql => l_sql
2874 , p_comps_r => l_comps_r
2875 , p_operator => l_operator
2876 , p_vs_sql => TRUE );
2877
2878 -- Now get the value set sql
2879
2880 fnd_vset.GET_VALUESET (
2881 VALUESET_ID => l_comps_r.flex_value_set_id
2882 ,VALUESET => r_valueset
2883 ,FORMAT => r_format );
2884
2885 IF ( g_debug ) THEN
2886 hr_utility.trace('where is '||r_valueset.table_info.where_clause);
2887 END IF;
2888
2889
2890
2891 l_order_by_start := INSTR( UPPER ( r_valueset.table_info.where_clause ), 'ORDER' );
2892
2893 IF ( l_order_by_start <> 0 )
2894 THEN
2895
2896 l_where_clause := SUBSTR( r_valueset.table_info.where_clause, 1, (l_order_by_start-1));
2897
2898 ELSE
2899
2900 l_where_clause := r_valueset.table_info.where_clause;
2901
2902 END IF;
2903
2904 IF ( ( INSTR ( UPPER( l_where_clause ), 'WHERE' ) = 0 )
2905 AND
2906 ( LENGTH ( l_where_clause ) <> 0 ) )
2907 THEN
2908 -- no where
2909
2910 l_where_clause := ' WHERE '||l_where_clause;
2911
2912 END IF;
2913
2914 l_vs_sql := ' SELECT ' || r_valueset.table_info.id_column_name || ' FROM ' ||
2915 r_valueset.table_info.table_name || ' ' ||
2916 l_where_clause || ' ) ';
2917
2918 IF ( g_debug ) THEN
2919 hr_utility.trace('Value Set SQL is ');
2920
2921 hr_utility.trace( substr(l_vs_sql,1,250) );
2922 hr_utility.trace( substr(l_vs_sql,251,250) );
2923 hr_utility.trace( substr(l_vs_sql,501,250) );
2924 hr_utility.trace( substr(l_vs_sql,751,250) );
2925 hr_utility.trace( substr(l_vs_sql,1001,250) );
2926 hr_utility.trace( substr(l_vs_sql,1251,250) );
2927 hr_utility.trace( substr(l_vs_sql,1501,250) );
2928 hr_utility.trace( substr(l_vs_sql,1751,250) );
2929 END IF;
2930
2931 l_vs_sql := REPLACE ( l_sql, '<VALUE_SET_SQL>', l_vs_sql );
2932
2933 IF ( g_debug ) THEN
2934 hr_utility.trace('Final Value Set String ...');
2935 hr_utility.trace( substr(l_vs_sql,1,250) );
2936 hr_utility.trace( substr(l_vs_sql,251,250) );
2937 hr_utility.trace( substr(l_vs_sql,501,250) );
2938 hr_utility.trace( substr(l_vs_sql,751,250) );
2939 hr_utility.trace( substr(l_vs_sql,1001,250) );
2940 hr_utility.trace( substr(l_vs_sql,1251,250) );
2941 hr_utility.trace( substr(l_vs_sql,1501,250) );
2942 hr_utility.trace( substr(l_vs_sql,1751,250) );
2943 END IF;
2944
2945 p_vs_sql := l_vs_sql;
2946
2947
2948 END value_set_string;
2949
2950
2951
2952
2953
2954 PROCEDURE sum_tc_bb_ok_hrs ( p_tc_bb_ok_string VARCHAR2
2955 , p_hrs IN OUT NOCOPY NUMBER
2956 , p_period_start DATE
2957 , p_period_end DATE ) IS
2958
2959 l_proc VARCHAR2(72);
2960
2961 -- Modified for Bug 13528722
2962 l_select VARCHAR2(500) := '
2963 SELECT SUM( DECODE( tbb.type, ''RANGE'',
2964 FUNCTION((((tbb.stop_time)-(tbb.start_time))*24),PRECISION) + power(ZERO,PRECISION),
2965 NVL(tbb.measure, 0) )) hrs
2966 FROM hxc_tmp_blks tbb
2967 WHERE tbb.scope = ''DETAIL'' AND
2968 tbb.start_time < :p_period_end AND
2969 tbb.start_time >= :p_period_start AND
2970 tbb.bb_id IN ( ';
2971
2972 -- Modified for Bug 13528722
2973 l_select_null VARCHAR2(500) := '
2974 SELECT SUM( DECODE( tbb.type, ''RANGE'',
2975 FUNCTION((((tbb.stop_time)-(tbb.start_time))*24),PRECISION) + power(ZERO,PRECISION),
2976 NVL(tbb.measure, 0) )) hrs
2977 FROM hxc_tmp_blks tbb
2978 WHERE tbb.scope = ''DETAIL'' AND
2979 tbb.start_time < :p_period_end AND
2980 tbb.start_time >= :p_period_start ';
2981
2982 l_csr INTEGER;
2983
2984 l_sql VARCHAR2(32000);
2985
2986 l_hrs NUMBER := 0;
2987
2988 l_rows_processed NUMBER := 0;
2989
2990 l_precision varchar2(4);
2991 l_rounding_rule varchar2(80);
2992 l_index number;
2993
2994 BEGIN
2995
2996 g_debug := hr_utility.debug_enabled;
2997
2998
2999 IF ( g_debug ) THEN
3000 l_proc := g_package||'sum_bb_ok_hrs';
3001 hr_utility.trace('Params are ');
3002 hr_utility.trace('p_tc_bb_ok_string is '||p_tc_bb_ok_string);
3003 hr_utility.trace('p_hrs are '||to_char(p_hrs));
3004 hr_utility.trace('p_period_start is '||to_char(p_period_start,'hh24:mi:ss DD-MON-YY'));
3005 hr_utility.trace('p_period_end is '||to_char(p_period_end,'HH24:MI:SS DD-MON-YY'));
3006
3007
3008 hr_utility.set_location('Entering '||l_proc, 10);
3009 END IF;
3010
3011
3012 -- Added for Bug 13528722
3013 /*
3014 if roud up
3015 function = trunc;
3016 zero = 0.1
3017 precision =
3018
3019 if round down
3020 function = trunc
3021 zero 0
3022
3023 if round nearest
3024 function = round
3025 zero = 0
3026
3027 */
3028
3029 IF hxc_timecard_properties.g_rounding_rule = 'ROUND_UP'
3030 THEN
3031
3032 l_select := REPLACE(l_select,'FUNCTION','TRUNC');
3033 l_select := REPLACE(l_select,'ZERO','0.1');
3034 l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
3035
3036 l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
3037 l_select_null := REPLACE(l_select_null,'ZERO','0.1');
3038 l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
3039
3040
3041 ELSIF hxc_timecard_properties.g_rounding_rule = 'ROUND_DOWN'
3042 THEN
3043
3044 l_select := REPLACE(l_select,'FUNCTION','TRUNC');
3045 l_select := REPLACE(l_select,'ZERO','0');
3046 l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
3047
3048 l_select_null := REPLACE(l_select_null,'FUNCTION','TRUNC');
3049 l_select_null := REPLACE(l_select_null,'ZERO','0');
3050 l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
3051
3052
3053 ELSIF hxc_timecard_properties.g_rounding_rule = 'ROUND_TO_NEAREST'
3054 THEN
3055
3056 l_select := REPLACE(l_select,'FUNCTION','ROUND');
3057 l_select := REPLACE(l_select,'ZERO','0');
3058 l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
3059
3060 l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
3061 l_select_null := REPLACE(l_select_null,'ZERO','0');
3062 l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
3063
3064 ELSIF hxc_timecard_properties.g_rounding_rule = 'SHOW_ERROR' -- Added for Bug 14582971
3065 THEN
3066
3067 l_select := REPLACE(l_select,'FUNCTION','ROUND');
3068 l_select := REPLACE(l_select,'ZERO','0');
3069 l_select := REPLACE(l_select,'PRECISION',hxc_timecard_properties.g_precision);
3070
3071 l_select_null := REPLACE(l_select_null,'FUNCTION','ROUND');
3072 l_select_null := REPLACE(l_select_null,'ZERO','0');
3073 l_select_null := REPLACE(l_select_null,'PRECISION',hxc_timecard_properties.g_precision);
3074
3075
3076 END IF;
3077
3078
3079 IF ( g_debug ) THEN
3080 hr_utility.trace( 'p_tc_bb_ok_string ::'||p_tc_bb_ok_string);
3081 hr_utility.trace( 'dynamic hrs l_select is ::'||l_select);
3082 hr_utility.trace( 'dynamic hrs l_select_null is ::'||l_select_null);
3083 END IF;
3084
3085 -- End of Bug 13528722
3086
3087 IF ( p_tc_bb_ok_string IS NOT NULL )
3088 THEN
3089
3090 --l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3091 l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3092
3093 IF ( g_debug ) THEN
3094 hr_utility.trace('p_tc_bb_ok_string 1');
3095 hr_utility.trace( 'dynamic hrs sql is ');
3096 hr_utility.trace( substr(l_sql,1,250) );
3097 hr_utility.trace( substr(l_sql,251,250) );
3098 hr_utility.trace( substr(l_sql,501,250) );
3099 hr_utility.trace( substr(l_sql,751,250) );
3100 hr_utility.trace( substr(l_sql,1001,250) );
3101 hr_utility.trace( substr(l_sql,1251,250) );
3102 hr_utility.trace( substr(l_sql,1501,250) );
3103 hr_utility.trace( substr(l_sql,1751,250) );
3104 END IF;
3105
3106 l_csr := dbms_sql.open_cursor;
3107
3108 dbms_sql.parse ( l_csr, l_sql, dbms_sql.native );
3109
3110 FOR i IN 1..get_token_count(p_tc_bb_ok_string,',') LOOP
3111
3112 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_ok_string,i,',' ));
3113
3114 END LOOP;
3115
3116 dbms_sql.bind_variable ( l_csr, ':p_period_end' , p_period_end );
3117 dbms_sql.bind_variable ( l_csr, ':p_period_start', p_period_start);
3118
3119 DBMS_SQL.DEFINE_COLUMN (l_csr, 1, l_hrs);
3120 --execute immediate l_sql INTO l_hrs USING p_period_end, p_period_start;
3121 l_rows_processed := dbms_sql.execute ( l_csr );
3122
3123 IF DBMS_SQL.FETCH_ROWS (l_csr) > 0 THEN
3124 DBMS_SQL.COLUMN_VALUE (l_csr, 1, l_hrs);
3125 END IF;
3126
3127 dbms_sql.close_cursor ( l_csr );
3128
3129 ELSIF ( hxc_time_category_utils_pkg.g_time_category_id IS NULL )
3130 THEN
3131 IF ( g_debug ) THEN
3132 hr_utility.trace('KRISH p_tc_bb_ok_string 2');
3133 END IF;
3134
3135 l_sql := l_select_null;
3136
3137 execute immediate l_sql INTO l_hrs USING p_period_end, p_period_start;
3138
3139 ELSE
3140 IF ( g_debug ) THEN
3141 hr_utility.trace('KRISH p_tc_bb_ok_string 3');
3142 END IF;
3143
3144 l_hrs := 0;
3145
3146
3147 END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3148
3149 IF ( g_debug ) THEN
3150 hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3151 END IF;
3152
3153 p_hrs := NVL(l_hrs,0);
3154
3155 IF ( g_debug ) THEN
3156 hr_utility.set_location('Leaving '||l_proc, 170);
3157 END IF;
3158
3159 END sum_tc_bb_ok_hrs;
3160
3161
3162
3163
3164 PROCEDURE sum_live_tc_bb_ok_hrs ( p_tc_bb_ok_string VARCHAR2
3165 , p_hrs IN OUT NOCOPY NUMBER ) IS
3166
3167 l_proc VARCHAR2(72);
3168
3169 l_select VARCHAR2(450) := '
3170 SELECT SUM( DECODE( tbb.type, ''RANGE'',
3171 (((tbb.stop_time)-(tbb.start_time))*24),
3172 NVL(tbb.measure, 0) )) hrs
3173 FROM hxc_time_building_blocks tbb
3174 , hxc_latest_details hld
3175 WHERE tbb.time_building_block_id = hld.time_building_block_id
3176 AND tbb.object_version_number = hld.object_version_number
3177 AND hld.time_building_block_id IN ( ';
3178
3179 l_csr INTEGER;
3180
3181 l_sql VARCHAR2(32000);
3182
3183 l_hrs NUMBER := 0;
3184
3185 l_rows_processed NUMBER := 0;
3186
3187 BEGIN
3188
3189
3190 IF ( p_tc_bb_ok_string IS NOT NULL )
3191 THEN
3192
3193 --l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3194
3195 l_sql := l_select || get_token_string(p_tc_bb_ok_string) || ' ) ';
3196
3197 IF ( g_debug ) THEN
3198 l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3199 hr_utility.trace( 'dynamic hrs sql is ');
3200 hr_utility.trace( substr(l_sql,1,250) );
3201 hr_utility.trace( substr(l_sql,251,250) );
3202 hr_utility.trace( substr(l_sql,501,250) );
3203 hr_utility.trace( substr(l_sql,751,250) );
3204 hr_utility.trace( substr(l_sql,1001,250) );
3205 hr_utility.trace( substr(l_sql,1251,250) );
3206 hr_utility.trace( substr(l_sql,1501,250) );
3207 hr_utility.trace( substr(l_sql,1751,250) );
3208 END IF;
3209
3210 l_csr := dbms_sql.open_cursor;
3211
3212 dbms_sql.parse ( l_csr, l_sql, dbms_sql.native );
3213
3214 FOR i IN 1..get_token_count(p_tc_bb_ok_string,',') LOOP
3215
3216 dbms_sql.bind_variable_char ( l_csr, ':'||i , get_token(p_tc_bb_ok_string,i,',' ));
3217
3218 END LOOP;
3219
3220 DBMS_SQL.DEFINE_COLUMN (l_csr, 1, l_hrs);
3221 --execute immediate l_sql INTO l_hrs;
3222 l_rows_processed := dbms_sql.execute ( l_csr );
3223
3224 IF DBMS_SQL.FETCH_ROWS (l_csr) > 0 THEN
3225 DBMS_SQL.COLUMN_VALUE (l_csr, 1, l_hrs);
3226 END IF;
3227
3228 dbms_sql.close_cursor ( l_csr );
3229
3230
3231 ELSE
3232
3233 l_hrs := 0;
3234
3235 END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3236
3237 IF ( g_debug ) THEN
3238 hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3239 END IF;
3240
3241 p_hrs := NVL( l_hrs, 0 );
3242
3243 IF ( g_debug ) THEN
3244 hr_utility.set_location('Leaving '||l_proc, 170);
3245 END IF;
3246
3247 END sum_live_tc_bb_ok_hrs;
3248
3249
3250
3251
3252
3253 --Same as above
3254 --But we need to process each detail block
3255 --according to precision and rounding rule
3256
3257
3258 PROCEDURE sum_live_tc_bb_ok_hrs ( p_tc_bb_ok_string IN VARCHAR2
3259 , p_hrs IN OUT NOCOPY NUMBER
3260 , p_rounding_rule IN VARCHAR2
3261 , p_decimal_precision IN VARCHAR2) IS
3262
3263 l_proc VARCHAR2(72);
3264
3265 l_select VARCHAR2(700) := 'SELECT SUM( HXC_FIND_NOTIFY_APRS_PKG.apply_round_rule('||''''||
3266 p_rounding_rule||''''||','||''''||
3267 p_decimal_precision||''''||',
3268 (DECODE( tbb.type, ''RANGE'',
3269 (((tbb.stop_time)-(tbb.start_time))*24),
3270 NVL(tbb.measure, 0) ))
3271 )) hrs
3272 FROM hxc_time_building_blocks tbb
3273 , hxc_latest_details hld
3274 WHERE tbb.time_building_block_id = hld.time_building_block_id
3275 AND tbb.object_version_number = hld.object_version_number
3276 AND hld.time_building_block_id IN ( ';
3277
3278 l_sql VARCHAR2(32000);
3279
3280 l_hrs NUMBER := 0;
3281
3282 BEGIN
3283
3284
3285 IF ( p_tc_bb_ok_string IS NOT NULL )
3286 THEN
3287
3288 l_sql := l_select || p_tc_bb_ok_string || ' ) ';
3289
3290 IF ( g_debug ) THEN
3291 l_proc := g_package||'sum_live_tc_bb_ok_hrs';
3292 hr_utility.trace( 'dynamic hrs sql is ');
3293 hr_utility.trace( substr(l_sql,1,250) );
3294 hr_utility.trace( substr(l_sql,251,250) );
3295 hr_utility.trace( substr(l_sql,501,250) );
3296 hr_utility.trace( substr(l_sql,751,250) );
3297 hr_utility.trace( substr(l_sql,1001,250) );
3298 hr_utility.trace( substr(l_sql,1251,250) );
3299 hr_utility.trace( substr(l_sql,1501,250) );
3300 hr_utility.trace( substr(l_sql,1751,250) );
3301 END IF;
3302
3303 execute immediate l_sql INTO l_hrs;
3304
3305 ELSE
3306
3307 l_hrs := 0;
3308
3309 END IF; -- IF ( p_tc_bb_ok_string IS NOT NULL )
3310
3311 IF ( g_debug ) THEN
3312 hr_utility.trace('GAZ - HOURS ARE : '||to_char(NVL(l_hrs,0)));
3313 END IF;
3314
3315 p_hrs := NVL( l_hrs, 0 );
3316
3317 IF ( g_debug ) THEN
3318 hr_utility.set_location('Leaving '||l_proc, 170);
3319 END IF;
3320
3321 END sum_live_tc_bb_ok_hrs;
3322
3323
3324
3325
3326
3327 -- public procedure
3328 -- evaluate_time_category
3329 --
3330 -- description
3331 --
3332 -- SEE HEADER
3333 --
3334
3335 PROCEDURE evaluate_time_category ( p_time_category_id IN NUMBER
3336 , p_tc_bb_ok_tab IN OUT NOCOPY t_tc_bb_ok
3337 , p_tc_bb_ok_string IN OUT NOCOPY VARCHAR2
3338 , p_tc_bb_not_ok_string IN OUT NOCOPY VARCHAR2
3339 , p_use_tc_cache IN BOOLEAN DEFAULT TRUE
3340 , p_use_tc_bb_cache IN BOOLEAN DEFAULT TRUE
3341 , p_use_temp_table IN BOOLEAN DEFAULT TRUE
3342 , p_scope IN VARCHAR2 DEFAULT 'TIME'
3343 , p_tbb_id IN NUMBER DEFAULT NULL
3344 , p_tbb_ovn IN NUMBER DEFAULT NULL ) IS
3345
3346 l_proc VARCHAR2(72);
3347
3348 CURSOR csr_get_category_comps ( p_time_category_id NUMBER ) IS
3349 SELECT
3350 tcc.time_category_id
3351 , tcc.time_category_comp_id
3352 , tcc.type
3353 , bbit.bld_blk_info_type_id
3354 , mpc.segment
3355 , tcc.component_type_id
3356 , tcc.ref_time_category_id
3357 , tcc.flex_value_set_id
3358 , tcc.value_id
3359 , tcc.is_null
3360 , tcc.equal_to
3361 , tccs.sql_string
3362 , tcc.last_update_date
3363 FROM
3364 hxc_time_category_comp_sql tccs
3365 , hxc_bld_blk_info_types bbit
3366 , hxc_mapping_components mpc
3367 , hxc_time_category_comps tcc
3368 WHERE tcc.time_category_id = p_time_category_id AND
3369 tcc.type <> 'MC'
3370 AND
3371 mpc.mapping_component_id (+) = tcc.component_type_id
3372 AND
3373 bbit.bld_blk_info_type_id (+) = mpc.bld_blk_info_type_id
3374 AND
3375 tccs.time_category_comp_id (+) = tcc.time_category_comp_id;
3376
3377 CURSOR csr_get_tbbs IS
3378 SELECT bb_id,
3379 scope
3380 FROM hxc_tmp_blks;
3381
3382
3383 CURSOR csr_get_live_tbbs ( p_bb_id NUMBER, p_bb_ovn NUMBER ) IS
3384 SELECT detail.time_building_block_id bb_id,
3385 detail.scope
3386 FROM hxc_latest_details tbb_latest,
3387 hxc_time_building_blocks detail,
3388 hxc_time_building_blocks day
3389 where day.parent_building_block_id = p_bb_id
3390 and day.parent_building_block_ovn = p_bb_ovn
3391 and detail.parent_building_block_id =
3392 day.time_building_block_id
3393 and detail.parent_building_block_ovn =
3394 day.object_version_number
3395 and tbb_latest.time_building_block_id = detail.time_building_Block_id
3396 and tbb_latest.object_version_number = detail.object_version_number
3397 and detail.date_to = hr_general.end_of_time;
3398
3399
3400 -- local variable defintions
3401
3402 l_time_category_info csr_get_time_category%ROWTYPE;
3403 l_time_category_comps csr_get_category_comps%ROWTYPE;
3404
3405 l_empty_time_category BOOLEAN := FALSE;
3406
3407 l_tc_bb_not_ok_string VARCHAR2(32000);
3408
3409 l_vs_comp_tab t_vs_comp;
3410 l_vs_ind PLS_INTEGER := 1;
3411
3412 l_an_comp_tab t_an_comp;
3413 l_an_ind PLS_INTEGER := 1;
3414
3415 l_tc_comp_tab t_tc_comp;
3416 l_tc_ind PLS_INTEGER := 1;
3417
3418
3419 BEGIN -- evaluate_time_category
3420 g_debug := hr_utility.debug_enabled;
3421
3422 g_params.p_time_category_id := p_time_category_id;
3423 g_params.p_use_tc_cache := p_use_tc_cache;
3424 g_params.p_use_tc_bb_cache := p_use_tc_bb_cache;
3425 g_params.p_use_temp_table := p_use_temp_table;
3426 g_params.p_scope := p_scope;
3427 g_params.p_tbb_id := p_tbb_id;
3428 g_params.p_tbb_ovn := p_tbb_ovn;
3429
3430 -- gaz_debug_push_timecard;
3431
3432 IF ( g_debug ) THEN
3433 l_proc := g_package||'evaluate_time_category';
3434 hr_utility.trace('*****************************************************');
3435 hr_utility.trace('Params are :');
3436 hr_utility.trace('tc id is : '||TO_CHAR(g_params.p_time_category_id));
3437 hr_utility.trace('scope is : '||g_params.p_scope);
3438 hr_utility.trace('tbb id is : '||TO_CHAR(g_params.p_tbb_id));
3439 hr_utility.trace('tbb ovn is : '||TO_CHAR(g_params.p_tbb_ovn));
3440 hr_utility.trace('Master tc id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id));
3441 hr_utility.trace('Master TC id '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id));
3442 hr_utility.trace('Master att cnt '||to_char(hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count));
3443
3444 IF ( g_params.p_use_tc_cache )
3445 THEN
3446 hr_utility.trace('p_use_tc_cache is TRUE');
3447 ELSE
3448 hr_utility.trace('p_use_tc_cache is FALSE');
3449 END IF;
3450
3451 IF ( g_params.p_use_tc_bb_cache )
3452 THEN
3453 hr_utility.trace('p_use_tc_bb_cache is TRUE');
3454 ELSE
3455 hr_utility.trace('p_use_tc_bb_cache is FALSE');
3456 END IF;
3457 IF ( g_params.p_use_temp_table )
3458 THEN
3459 hr_utility.trace('p_use_temp_table is TRUE');
3460 ELSE
3461 hr_utility.trace('p_use_temp_table is FALSE');
3462 END IF;
3463
3464 hr_utility.trace('tc bb ok string '||p_tc_bb_ok_string);
3465
3466 END IF; -- l debug
3467
3468
3469 -- check time category cache
3470
3471 IF ( p_use_tc_cache AND tc_cache_exists ( p_time_category_id ) )
3472 THEN
3473
3474 IF ( g_debug ) THEN
3475 hr_utility.trace('Using time category cache');
3476 END IF;
3477
3478 l_tc_cache_exists := TRUE;
3479
3480 l_time_category_info.operator := g_tc_cache(p_time_category_id).operator;
3481 l_time_category_info.time_sql := g_tc_cache(p_time_category_id).time_sql;
3482
3483 ELSE
3484
3485 IF ( g_debug ) THEN
3486 hr_utility.trace('NOT using time category cache');
3487 END IF;
3488
3489 -- get time category TIME_SQL and OPERATOR
3490
3491 OPEN csr_get_time_category ( p_time_category_id);
3492 FETCH csr_get_time_category INTO l_time_category_info;
3493
3494 IF csr_get_time_category%NOTFOUND
3495 THEN
3496 -- empty time category
3497
3498 IF ( g_debug ) THEN
3499 hr_utility.trace('Time Category EMPTY anyway !!!');
3500 END IF;
3501
3502 l_empty_time_category := TRUE;
3503 /* bug fix for 5076837 */
3504 g_empty_time_category_tab(p_time_category_id).p_status:=TRUE;
3505 /* end of fix for bug 5078637 */
3506 END IF;
3507
3508 CLOSE csr_get_time_category;
3509
3510 l_tc_cache_exists := FALSE;
3511
3512 -- must be false since TC not in cache
3513
3514 l_tc_bb_cache_exists := FALSE;
3515
3516 END IF; -- using cache
3517
3518
3519 -- DO NOTHING IF THE TIME CATEGORY IS EMPTY
3520
3521
3522 IF ( NOT l_empty_time_category )
3523 THEN
3524
3525
3526 -- For the master time category check the cache
3527
3528 IF ( hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id IS NULL )
3529 THEN
3530
3531 IF ( g_debug ) THEN
3532 hr_utility.trace('Master Time Category !!!!!!');
3533 END IF;
3534
3535 -- set master TC info
3536
3537 hxc_time_category_utils_pkg.g_time_category_id := p_time_category_id;
3538
3539 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id := p_time_category_id;
3540 hxc_time_category_utils_pkg.g_master_tc_info_rec.operator := l_time_category_info.operator;
3541
3542
3543
3544 -- check tc bb cache
3545
3546 IF ( g_tc_bb_ok_cache.exists( p_time_category_id ) AND p_use_tc_bb_cache )
3547 THEN
3548
3549 IF ( g_debug ) THEN
3550 hr_utility.trace('tc bb ok cache exists');
3551 END IF;
3552
3553 IF ( ( g_tc_bb_ok_cache(p_time_category_id).timecard_id =
3554 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id ) AND
3555 ( g_tc_bb_ok_cache(p_time_category_id).attribute_count =
3556 hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count ) )
3557 THEN
3558
3559 IF ( g_debug ) THEN
3560 hr_utility.trace('Using tc bb ok cache');
3561 END IF;
3562
3563 l_tc_bb_cache_exists := TRUE;
3564
3565 ELSE
3566 -- different timecard therefore cannot use cache
3567
3568 IF ( g_debug ) THEN
3569 hr_utility.trace('NOT using tc bb ok cache - diff TC ID');
3570 END IF;
3571
3572 l_tc_bb_cache_exists := FALSE;
3573 g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := NULL;
3574
3575 END IF;
3576
3577 ELSE
3578
3579 IF ( g_debug ) THEN
3580 hr_utility.trace('tc bb ok cache NOT EXISTS');
3581 END IF;
3582
3583 l_tc_bb_cache_exists := FALSE;
3584
3585 END IF;
3586
3587
3588
3589
3590 IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
3591 THEN
3592
3593 -- if the TC building block cache does not exist or we are
3594 -- not supposed to use it then generate the not ok string
3595 -- for time category evaluation since we are going to evaluate
3596 -- the time category from scratch
3597
3598 IF ( p_use_temp_table )
3599 THEN
3600 IF ( g_debug ) THEN
3601 hr_utility.trace('Using temporary table to generate not ok bbs');
3602 END IF;
3603
3604 IF ( hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL )
3605 THEN
3606
3607 -- generate tc_bb_not_ok_string (this should always be generated in
3608 -- push_timecard)
3609
3610 FOR tmp_bb_rec IN csr_get_tbbs
3611 LOOP
3612
3613 IF ( tmp_bb_rec.scope = 'DETAIL' )
3614 THEN
3615
3616 IF ( p_tc_bb_not_ok_string IS NULL )
3617 THEN
3618
3619 p_tc_bb_not_ok_string := tmp_bb_rec.bb_id;
3620
3621 ELSE
3622
3623 p_tc_bb_not_ok_string := p_tc_bb_not_ok_string || ', ' || tmp_bb_rec.bb_id;
3624
3625 END IF;
3626
3627 END IF;
3628
3629 END LOOP;
3630
3631 IF ( g_debug ) THEN
3632 hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3633 END IF;
3634
3635 ELSE -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NOT NULL
3636
3637 p_tc_bb_not_ok_string := hxc_time_category_utils_pkg.g_tc_bb_not_ok_string;
3638
3639 IF ( g_debug ) THEN
3640 hr_utility.trace('Using cache : bb not ok string is '||p_tc_bb_not_ok_string);
3641 END IF;
3642
3643 END IF; -- hxc_time_category_utils_pkg.g_tc_bb_not_ok_string IS NULL
3644
3645 ELSE -- using live table
3646
3647 IF ( g_debug ) THEN
3648 hr_utility.trace('Using the live table');
3649 END IF;
3650
3651 IF ( p_scope = 'TIME' )
3652 THEN
3653
3654 IF ( g_debug ) THEN
3655 hr_utility.trace('Scope is TIME');
3656 END IF;
3657
3658 -- populate p tc bb not ok string from timecard
3659
3660 FOR tmp_bb_rec IN csr_get_live_tbbs ( g_params.p_tbb_id, g_params.p_tbb_ovn )
3661 LOOP
3662 IF ( tmp_bb_rec.scope = 'DETAIL' )
3663 THEN
3664
3665 IF ( p_tc_bb_not_ok_string IS NULL )
3666 THEN
3667 p_tc_bb_not_ok_string := tmp_bb_rec.bb_id;
3668 ELSE
3669
3670 p_tc_bb_not_ok_string := p_tc_bb_not_ok_string || ', ' || tmp_bb_rec.bb_id;
3671
3672 END IF;
3673
3674 IF ( g_debug ) THEN
3675 hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3676 END IF;
3677
3678 END IF;
3679
3680 END LOOP;
3681
3682 ELSIF ( p_scope = 'DAY' )
3683 THEN
3684 -- not supported
3685
3686 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3687 fnd_message.set_token('PROCEDURE', l_proc);
3688 fnd_message.set_token('STEP','DAY not supported!');
3689 fnd_message.raise_error;
3690
3691 ELSIF ( p_scope = 'DETAIL' )
3692 THEN
3693
3694 IF ( g_debug ) THEN
3695 hr_utility.trace('Scope is DETAIL');
3696 END IF;
3697
3698 p_tc_bb_not_ok_string := g_params.p_tbb_id;
3699
3700 IF ( g_debug ) THEN
3701 hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
3702 END IF;
3703
3704 END IF;
3705
3706 END IF; -- p_use_temp_table
3707
3708 p_tc_bb_not_ok_string := NVL( p_tc_bb_not_ok_string, '-99999999' );
3709
3710 END IF; -- IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
3711
3712 ELSE -- not master time category
3713
3714 -- can only use bb cache for master time category
3715
3716 l_tc_bb_cache_exists := FALSE;
3717
3718 END IF; -- IF ( hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id IS NULL )
3719
3720
3721
3722
3723 IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
3724 THEN
3725
3726 IF ( g_debug ) THEN
3727 hr_utility.trace('NOT using tc bb cache');
3728 END IF;
3729
3730 -- get time category components
3731
3732 IF ( l_tc_cache_exists )
3733 THEN
3734
3735 -- populate TC component tables from cache
3736
3737 l_vs_comp_tab.DELETE;
3738 l_an_comp_tab.DELETE;
3739 l_tc_comp_tab.DELETE;
3740
3741 get_tc_from_cache ( p_time_category_id => p_time_category_id
3742 , p_vs_comp_tab => l_vs_comp_tab
3743 , p_an_comp_tab => l_an_comp_tab
3744 , p_tc_comp_tab => l_tc_comp_tab );
3745
3746 ELSE
3747
3748 IF ( g_debug ) THEN
3749 hr_utility.trace('about to get tc comps');
3750 END IF;
3751
3752 OPEN csr_get_category_comps( p_time_category_id );
3753 FETCH csr_get_category_comps INTO l_time_category_comps;
3754
3755 WHILE csr_get_category_comps%FOUND
3756 LOOP
3757
3758 IF ( l_time_category_comps.type = 'MC_VS' )
3759 THEN
3760
3761 l_vs_comp_tab(l_vs_ind).time_category_id := l_time_category_comps.time_category_id;
3762 l_vs_comp_tab(l_vs_ind).time_category_comp_id := l_time_category_comps.time_category_comp_id;
3763 l_vs_comp_tab(l_vs_ind).component_type_id := l_time_category_comps.component_type_id;
3764 l_vs_comp_tab(l_vs_ind).flex_value_set_id := l_time_category_comps.flex_value_set_id;
3765 l_vs_comp_tab(l_vs_ind).sql_string := l_time_category_comps.sql_string;
3766 l_vs_comp_tab(l_vs_ind).is_null := l_time_category_comps.is_null;
3767 l_vs_comp_tab(l_vs_ind).equal_to := l_time_category_comps.equal_to;
3768 l_vs_comp_tab(l_vs_ind).last_update_date := l_time_category_comps.last_update_date;
3769
3770 l_vs_ind := l_vs_ind + 1;
3771
3772 ELSIF ( l_time_category_comps.type = 'AN' )
3773 THEN
3774 -- maintain alternate name table
3775
3776 l_an_comp_tab(l_an_ind).sql_string := l_time_category_comps.sql_string;
3777
3778 l_an_ind := l_an_ind + 1;
3779
3780 ELSIF ( l_time_category_comps.type = 'BB' )
3781 THEN
3782 -- maintain building block table
3783
3784 null;
3785
3786 ELSIF ( l_time_category_comps.type = 'TC' )
3787 THEN
3788 -- maintain ref time category table
3789
3790 l_tc_comp_tab(l_tc_ind).ref_tc_id := l_time_category_comps.ref_time_category_id;
3791
3792 l_tc_ind := l_tc_ind + 1;
3793
3794 ELSIF ( l_time_category_comps.type = 'FF' )
3795 THEN
3796 -- maintain fast formula table
3797
3798 null;
3799
3800 END IF;
3801
3802 FETCH csr_get_category_comps INTO l_time_category_comps;
3803
3804 END LOOP;
3805
3806 IF ( g_debug ) THEN
3807 hr_utility.trace('Category Component Table Counts are .......');
3808 hr_utility.trace('MC_VS count is '||to_char(l_vs_comp_tab.count));
3809 hr_utility.trace('AN count is '||to_char(l_an_comp_tab.count));
3810 hr_utility.trace('TC count is '||to_char(l_tc_comp_tab.count));
3811 END IF;
3812
3813 -- maintain TC CACHE
3814
3815 add_tc_to_cache ( p_time_category_id => p_time_category_id
3816 , p_time_category_info => l_time_category_info
3817 , p_vs_comp_tab => l_vs_comp_tab
3818 , p_an_comp_tab => l_an_comp_tab
3819 , p_tc_comp_tab => l_tc_comp_tab );
3820
3821 END IF; -- IF ( l_tc_cache_exists )
3822
3823
3824 l_continue_evaluation := TRUE;
3825
3826 -- ***************************************************
3827 -- now evaluate the different time category components
3828 -- easiest components first i.e.
3829 -- BB, MC, MC_VS, AN, FF, TC
3830 -- ***************************************************
3831
3832 -- ******************** Mapping Component Components ********************
3833
3834 IF ( l_time_category_info.time_sql IS NOT NULL AND l_continue_evaluation )
3835 THEN
3836
3837 -- TYPE = MC
3838
3839 IF ( g_debug ) THEN
3840 hr_utility.trace('Evaluating MC');
3841 END IF;
3842
3843 evaluate_time_sql ( l_time_category_info.time_sql
3844 , NULL
3845 , p_tc_bb_ok_tab
3846 , p_tc_bb_ok_string
3847 , p_tc_bb_not_ok_string
3848 , hxc_time_category_utils_pkg.g_master_tc_info_rec.operator );
3849
3850 l_continue_evaluation := continue_evaluation ( hxc_time_category_utils_pkg.g_master_tc_info_rec.operator
3851 , p_tc_bb_ok_string
3852 , p_tc_bb_not_ok_string );
3853
3854
3855 END IF;
3856
3857
3858 -- *********** Mapping Component with Value Set Components ************
3859
3860 IF ( l_vs_comp_tab.COUNT <> 0 AND l_continue_evaluation )
3861 THEN
3862
3863 -- TYPE = MC_VS
3864
3865 l_vs_ind := l_vs_comp_tab.FIRST;
3866
3867 WHILE ( l_vs_ind IS NOT NULL AND l_continue_evaluation )
3868 LOOP
3869 -- check that the value set definition has not
3870 -- changed since the tc comp row was updated
3871 -- if so - then call value_set_string and
3872 -- maintain tccs again
3873
3874 IF ( g_debug ) THEN
3875 hr_utility.trace('Evaluating MC_VS Loop');
3876 END IF;
3877
3878 evaluate_time_sql ( NULL
3879 , l_vs_comp_tab(l_vs_ind).sql_string
3880 , p_tc_bb_ok_tab
3881 , p_tc_bb_ok_string
3882 , p_tc_bb_not_ok_string
3883 , hxc_time_category_utils_pkg.g_master_tc_info_rec.operator );
3884
3885 l_vs_ind := l_vs_comp_tab.NEXT(l_vs_ind);
3886
3887 l_continue_evaluation := continue_evaluation ( hxc_time_category_utils_pkg.g_master_tc_info_rec.operator
3888 , p_tc_bb_ok_string
3889 , p_tc_bb_not_ok_string );
3890
3891 END LOOP;
3892
3893 END IF; -- l_vs_comp_tab.COUNT <> 0
3894
3895
3896 -- ******************** Alternate Name Components *****************************
3897
3898 IF ( l_an_comp_tab.COUNT <> 0 AND l_continue_evaluation )
3899 THEN
3900
3901 -- TYPE = AN
3902
3903 l_an_ind := l_an_comp_tab.FIRST;
3904
3905 WHILE ( l_an_ind IS NOT NULL AND l_continue_evaluation )
3906 LOOP
3907
3908 IF ( g_debug ) THEN
3909 hr_utility.trace('Evaluating AN Loop');
3910 END IF;
3911
3912 evaluate_time_sql ( NULL
3913 , l_an_comp_tab(l_an_ind).sql_string
3914 , p_tc_bb_ok_tab
3915 , p_tc_bb_ok_string
3916 , p_tc_bb_not_ok_string
3917 , hxc_time_category_utils_pkg.g_master_tc_info_rec.operator );
3918
3919 l_an_ind := l_an_comp_tab.NEXT(l_an_ind);
3920
3921 l_continue_evaluation := continue_evaluation ( hxc_time_category_utils_pkg.g_master_tc_info_rec.operator
3922 , p_tc_bb_ok_string
3923 , p_tc_bb_not_ok_string );
3924
3925 END LOOP;
3926
3927 END IF; -- l_an_comp_tab.COUNT <> 0
3928
3929
3930 -- ******************** Fast Formula Components *****************************
3931 /*
3932
3933 IF ( l_ff_comp_tab.COUNT <> 0 AND l_continue_evaluation )
3934 THEN
3935
3936 -- TYPE = FF
3937
3938 l_ff_ind := l_ff_comp_tab.FIRST;
3939
3940 WHILE ( l_ff_ind IS NOT NULL AND l_continue_evaluation )
3941 LOOP
3942
3943 evaluate_fast_formula (
3944 p_formula_id => l_tc_comp_tab(l_tc_ind).component_type_id
3945 , p_tc_bb_ok_tab => p_tc_bb_ok_tab
3946 , p_tc_bb_ok_string => p_tc_bb_ok_string
3947 , p_tc_bb_not_ok_string => p_tc_bb_not_ok_string );
3948
3949 l_ff_ind := l_tc_comp_tab.NEXT(l_ff_ind);
3950
3951 l_continue_evaluation := continue_evaluation ( hxc_time_category_utils_pkg.g_master_tc_info_rec.operator
3952 , p_tc_bb_ok_string
3953 , p_tc_bb_not_ok_string );
3954
3955 END LOOP;
3956
3957 END IF;
3958
3959 */
3960
3961
3962 -- ******************** Time Category Components *****************************
3963
3964 IF ( l_tc_comp_tab.COUNT <> 0 AND l_continue_evaluation )
3965 THEN
3966
3967 -- TYPE = TC
3968
3969 l_tc_ind := l_tc_comp_tab.FIRST;
3970
3971 WHILE ( l_tc_ind IS NOT NULL AND l_continue_evaluation )
3972 LOOP
3973
3974 evaluate_time_category (
3975 p_time_category_id => l_tc_comp_tab(l_tc_ind).ref_tc_id
3976 , p_tc_bb_ok_tab => p_tc_bb_ok_tab
3977 , p_tc_bb_ok_string => p_tc_bb_ok_string
3978 , p_tc_bb_not_ok_string => p_tc_bb_not_ok_string
3979 , p_use_tc_cache => g_params.p_use_tc_cache
3980 , p_use_tc_bb_cache => g_params.p_use_tc_bb_cache
3981 , p_use_temp_table => g_params.p_use_temp_table
3982 , p_scope => g_params.p_scope
3983 , p_tbb_id => g_params.p_tbb_id
3984 , p_tbb_ovn => g_params.p_tbb_ovn );
3985
3986 l_tc_ind := l_tc_comp_tab.NEXT(l_tc_ind);
3987
3988 END LOOP;
3989
3990 END IF;
3991
3992
3993 -- Only maintain these global variables for the master time category
3994
3995 IF ( p_time_category_id = hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id )
3996 THEN
3997
3998 -- setting global time category variables
3999
4000 hxc_time_category_utils_pkg.g_tc_in_bb_ok := p_time_category_id;
4001 hxc_time_category_utils_pkg.g_tc_bb_ok_tab := p_tc_bb_ok_tab;
4002 hxc_time_category_utils_pkg.g_tc_bb_ok_string := p_tc_bb_ok_string;
4003
4004 -- *****************************************************************************
4005 -- bb ok cache is not being used currently
4006 -- *****************************************************************************
4007
4008 /*
4009
4010 IF ( g_debug ) THEN
4011 hr_utility.trace('Setting tc bb ok cache for master tc !!!! '||to_char(p_time_category_id));
4012 END IF;
4013
4014 g_tc_bb_ok_cache(p_time_category_id).bb_ok_string := p_tc_bb_ok_string;
4015
4016 g_tc_bb_ok_cache(p_time_category_id).timecard_id :=
4017 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id;
4018
4019 g_tc_bb_ok_cache(p_time_category_id).attribute_count :=
4020 hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count;
4021
4022 */
4023
4024
4025 END IF;
4026
4027
4028 ELSE -- IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
4029
4030 -- using the cache
4031
4032 IF ( g_debug ) THEN
4033 hr_utility.trace('Using bb outcome cache');
4034 END IF;
4035
4036 hxc_time_category_utils_pkg.g_tc_in_bb_ok := p_time_category_id;
4037
4038 hxc_time_category_utils_pkg.g_tc_bb_ok_string := g_tc_bb_ok_cache( p_time_category_id ).bb_ok_string;
4039
4040 hxc_time_category_utils_pkg.g_tc_bb_ok_tab := get_bb_ok_tab_from_string ( p_time_category_id );
4041
4042 p_tc_bb_ok_string := g_tc_bb_ok_cache( p_time_category_id ).bb_ok_string;
4043
4044
4045 END IF; -- IF ( NOT l_tc_bb_cache_exists OR NOT p_use_tc_bb_cache )
4046
4047 END IF; -- IF ( NOT l_empty_time_category )
4048
4049 IF ( g_debug ) THEN
4050 hr_utility.trace('***************************************************');
4051 hr_utility.trace('Return values from evaluate time category for : '||to_char(p_time_category_id));
4052 hr_utility.trace('bb ok string is '||p_tc_bb_ok_string);
4053 hr_utility.trace('bb not ok string is '||p_tc_bb_not_ok_string);
4054 hr_utility.trace('bb ok tab is ....');
4055
4056 l_tc_ind := p_tc_bb_ok_tab.FIRST;
4057
4058 WHILE l_tc_ind IS NOT NULL
4059 LOOP
4060
4061 hr_utility.trace('bb id is : '||to_char(l_tc_ind));
4062
4063 l_tc_ind := p_tc_bb_ok_tab.NEXT(l_tc_ind);
4064
4065 END LOOP;
4066 hr_utility.trace('***************************************************');
4067
4068 END IF; -- l debug
4069
4070 -- Reset Variables for the Master TC
4071
4072 IF ( p_time_category_id = hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id )
4073 THEN
4074
4075 -- reset master time category variables now the cache has been maintained
4076
4077 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id := NULL;
4078
4079 END IF;
4080
4081
4082
4083 exception when others then
4084
4085 IF ( g_debug ) THEN
4086 hr_utility.trace('In exception error is '||SQLERRM);
4087 END IF;
4088 raise;
4089
4090 END evaluate_time_category;
4091
4092
4093
4094 -- public function
4095 -- chk_tc_bb_ok
4096
4097 -- description
4098 --
4099 -- SEE HEADER FOR DETAILS
4100
4101 FUNCTION chk_tc_bb_ok (
4102 p_tbb_id NUMBER ) RETURN BOOLEAN IS
4103
4104 l_proc VARCHAR2(72);
4105
4106 BEGIN
4107
4108 g_debug := hr_utility.debug_enabled;
4109
4110 IF ( g_debug ) THEN
4111 l_proc := g_package||'chk_tc_bb_ok';
4112 hr_utility.set_location('Entering '||l_proc, 10);
4113 END IF;
4114
4115 IF ( ( g_tc_in_bb_ok <> hxc_time_category_utils_pkg.g_time_category_id ) OR
4116 ( g_tc_in_bb_ok IS NULL ) )
4117 THEN
4118 IF hxc_time_category_utils_pkg.g_time_category_id is not null then
4119 IF g_empty_time_category_tab.exists(hxc_time_category_utils_pkg.g_time_category_id) then
4120 IF NOT g_empty_time_category_tab(hxc_time_category_utils_pkg.g_time_category_id).p_status THEN
4121 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4122 fnd_message.set_token('PROCEDURE', l_proc);
4123 fnd_message.set_token('STEP','g tc id not tc tab id');
4124 fnd_message.raise_error;
4125 END IF;
4126 END IF;
4127 END IF;
4128 END IF;
4129
4130
4131 IF ( g_tc_bb_ok_tab.EXISTS( p_tbb_id ) )
4132 THEN
4133 RETURN TRUE;
4134 ELSE
4135 RETURN FALSE;
4136 END IF;
4137
4138 END chk_tc_bb_ok;
4139
4140
4141
4142 PROCEDURE insert_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
4143
4144 l_proc VARCHAR2(72);
4145
4146 l_sql CLOB; -- Bug 15977687
4147 l_operator VARCHAR2(3);
4148
4149 BEGIN
4150
4151 g_debug := hr_utility.debug_enabled;
4152
4153 IF ( g_debug ) THEN
4154 l_proc := g_package||'insert_time_category_comp_sql';
4155 hr_utility.set_location('Entering '||l_proc, 10);
4156
4157 hr_utility.trace('Inserting tcc SQL for type '||p_rec.type);
4158 END IF;
4159
4160 OPEN csr_get_operator ( p_rec.time_category_id );
4161 FETCH csr_get_operator INTO l_operator;
4162 CLOSE csr_get_operator;
4163
4164 IF ( p_rec.type = 'AN' )
4165 THEN
4166
4167 alternate_name_string ( p_alias_value_id => p_rec.component_type_id
4168 , p_operator => l_operator
4169 , p_is_null => p_rec.is_null
4170 , p_equal_to => p_rec.equal_to
4171 , p_time_sql => l_sql );
4172
4173 ELSIF ( p_rec.type = 'MC_VS' )
4174 THEN
4175
4176 value_set_string ( p_rec => p_rec
4177 , p_vs_sql => l_sql );
4178
4179 ELSE
4180
4181 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4182 fnd_message.set_token('PROCEDURE', l_proc);
4183 fnd_message.set_token('STEP','Invalid TYPE');
4184 fnd_message.raise_error;
4185
4186 END IF;
4187
4188 validate_time_category_sql ( l_sql );
4189
4190 INSERT INTO hxc_time_category_comp_sql (
4191 time_category_comp_sql_id
4192 , time_category_comp_id
4193 , sql_string )
4194 VALUES (
4195 hxc_time_category_comp_sql_s.nextval
4196 , p_rec.time_category_comp_id
4197 , l_sql );
4198
4199 IF ( g_debug ) THEN
4200 hr_utility.set_location('Entering '||l_proc, 20);
4201 END IF;
4202
4203 END insert_time_category_comp_sql;
4204
4205
4206 PROCEDURE update_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
4207
4208 l_proc VARCHAR2(72);
4209
4210 l_sql CLOB; -- Bug 15977687
4211 l_operator VARCHAR2(3);
4212
4213 l_tcc_sql csr_chk_tcc_sql_exists%ROWTYPE;
4214
4215 BEGIN
4216
4217 g_debug := hr_utility.debug_enabled;
4218
4219 IF ( g_debug ) THEN
4220 l_proc := g_package||'update_time_category_comp_sql';
4221 hr_utility.trace('Updating tcc SQL for type '||p_rec.type);
4222 END IF;
4223
4224 -- First check to see row exists
4225 -- user may have changed the TYPE of tcc record
4226
4227 OPEN csr_chk_tcc_sql_exists ( p_rec.time_category_comp_id );
4228 FETCH csr_chk_tcc_sql_exists INTO l_tcc_sql;
4229
4230 IF ( csr_chk_tcc_sql_exists%FOUND )
4231 THEN
4232
4233 IF ( p_rec.type IN ( 'MC_VS', 'AN' ) )
4234 THEN
4235
4236 -- row exists and TCC row still of correct type
4237
4238 IF ( g_debug ) THEN
4239 hr_utility.trace('is nul is '||p_rec.is_null);
4240 hr_utility.trace('equal to is '||p_Rec.equal_to);
4241 END IF;
4242
4243 OPEN csr_get_operator ( p_rec.time_category_id );
4244 FETCH csr_get_operator INTO l_operator;
4245 CLOSE csr_get_operator;
4246
4247 IF ( p_rec.type = 'AN' )
4248 THEN
4249
4250 alternate_name_string ( p_alias_value_id => p_rec.component_type_id
4251 , p_operator => l_operator
4252 , p_is_null => p_rec.is_null
4253 , p_equal_to => p_rec.equal_to
4254 , p_time_sql => l_sql );
4255
4256 ELSIF ( p_rec.type = 'MC_VS' )
4257 THEN
4258
4259 value_set_string ( p_rec => p_rec
4260 , p_vs_sql => l_sql );
4261
4262 END IF;
4263
4264 validate_time_category_sql ( l_sql );
4265
4266 UPDATE hxc_time_category_comp_sql
4267 SET sql_string = l_sql
4268 WHERE time_category_comp_id = p_rec.time_category_comp_id;
4269
4270 ELSE
4271
4272 -- rows exists but TCC row no longer of type which uses
4273 -- TCC SQL therefore delete redundant row
4274
4275 DELETE from hxc_time_category_comp_sql
4276 WHERE time_category_comp_sql_id = l_tcc_sql.tcc_sql_id;
4277
4278 END IF; -- IF ( p_rec.type IN ( 'MC_VS', 'AN' )
4279
4280 ELSE
4281
4282 -- row does not exists
4283
4284 IF ( p_rec.type in ( 'MC_VS', 'AN' ) )
4285 THEN
4286
4287 insert_time_category_comp_sql ( p_rec );
4288
4289 END IF;
4290
4291
4292 END IF; -- IF ( csr_chk_tcc_sql%FOUND )
4293
4294 CLOSE csr_chk_tcc_sql_exists;
4295
4296 EXCEPTION WHEN OTHERS THEN
4297
4298 CLOSE csr_chk_tcc_sql_exists;
4299 raise;
4300
4301 END update_time_category_comp_sql;
4302
4303
4304 PROCEDURE delete_time_category_comp_sql ( p_rec hxc_tcc_shd.g_rec_type ) IS
4305
4306 l_proc VARCHAR2(72);
4307
4308 BEGIN
4309
4310 g_debug := hr_utility.debug_enabled;
4311
4312 IF ( g_debug ) THEN
4313 l_proc := g_package||'delete_time_category_comp_sql';
4314 hr_utility.trace('Deleting tcc SQL for type '||p_rec.type);
4315 END IF;
4316
4317 -- First check to see row exists
4318 -- user may have changed the TYPE of tcc record
4319
4320 FOR tcc_sql IN csr_chk_tcc_sql_exists ( p_rec.time_category_comp_id )
4321 LOOP
4322
4323 DELETE from hxc_time_category_comp_sql
4324 WHERE time_category_comp_sql_id = tcc_sql.tcc_sql_id;
4325
4326 END LOOP;
4327
4328
4329 END delete_time_category_comp_sql;
4330
4331 -- ----------------------------------------------------------------------------
4332 -- |----------------------------< get_value_set_sql >-------------------------|
4333 -- ----------------------------------------------------------------------------
4334 --
4335 -- public function
4336 -- get_value_set_sql
4337 --
4338 -- description
4339 -- get the SQL associated with a particular value set
4340
4341
4342 FUNCTION get_value_set_sql
4343 (p_flex_value_set_id IN NUMBER,
4344 p_session_date IN DATE ) RETURN LONG
4345 is
4346 --
4347 -- Declare local variables
4348 --
4349 l_sql_text LONG;
4350 l_sql_text_id LONG;
4351 l_valueset_r fnd_vset.valueset_r;
4352 l_valueset_dr fnd_vset.valueset_dr;
4353 l_value_set_id fnd_flex_value_sets.flex_value_set_id%TYPE;
4354 l_proc varchar2(72);
4355 l_order_by_start NUMBER;
4356 l_from_start NUMBER;
4357 l_additional_and_clause VARCHAR2(2000);
4358 l_from_where VARCHAR2(2000);
4359 l_select_clause VARCHAR2(2000);
4360 l_dep_parent_column_name fnd_columns.column_name%TYPE;
4361 --
4362 begin -- get_value_set_sql
4363
4364 g_debug := hr_utility.debug_enabled;
4365
4366 IF ( g_debug ) THEN
4367 l_proc := g_package||'get_value_set_sql';
4368 hr_utility.set_location('Entering:'|| l_proc, 5);
4369 END IF;
4370
4371
4372 l_value_set_id := p_flex_value_set_id;
4373
4374 fnd_vset.get_valueset(l_value_set_id,l_valueset_r,l_valueset_dr);
4375
4376 --
4377 -- Initailize the SQL text columns.
4378 --
4379 l_sql_text := '';
4380 l_sql_text_id := '';
4381 --
4382 IF ( g_debug ) THEN
4383 hr_utility.set_location(l_proc, 10);
4384 END IF;
4385
4386 -- Ok next build the SQL text that can be used to build a pop-list
4387 -- for this segment, if this is a table validated or independant
4388 -- validated value set - i.e. it has an associated list of values.
4389 -- We are going to build two versions of the SQL. One can be used
4390 -- to define the list of values associated with this segment(SQL_TEXT), the
4391 -- other is used to converted a value (ID) stored on the database into a
4392 -- a description (VALUE) (SQL_DESCR_TXT).
4393 --
4394 IF l_valueset_r.validation_type = 'F'
4395 THEN
4396 -- TABLE validated
4397
4398 IF ( g_debug ) THEN
4399 hr_utility.set_location(l_proc, 20);
4400 END IF;
4401
4402 select 'SELECT ' ||
4403 l_valueset_r.table_info.value_column_name ||
4404 decode(l_valueset_r.table_info.meaning_column_name,null,',NULL ',
4405 ','||l_valueset_r.table_info.meaning_column_name)||
4406 decode(l_valueset_r.table_info.id_column_name,null,',NULL ',
4407 ','||l_valueset_r.table_info.id_column_name)||
4408 ' FROM ' ||
4409 l_valueset_r.table_info.table_name || ' ' ||
4410 l_valueset_r.table_info.where_clause
4411 into l_sql_text
4412 from dual;
4413
4414 IF ( g_debug ) THEN
4415 hr_utility.set_location(l_proc, 30);
4416 END IF;
4417
4418 l_order_by_start := INSTR(upper(l_sql_text),'ORDER BY');
4419 l_from_start := INSTR(upper(l_sql_text),'FROM');
4420
4421 IF ( g_debug ) THEN
4422 hr_utility.set_location(l_proc, 60);
4423 END IF;
4424
4425 -- Build the SQL for the FROM clause
4426
4427 if(l_order_by_start >0) then
4428 l_from_where := substr(l_sql_text,l_from_start,(
4429 l_order_by_start-l_from_start));
4430 else
4431 l_from_where := substr(l_sql_text,l_from_start);
4432 end if;
4433 --
4434 IF ( g_debug ) THEN
4435 hr_utility.set_location(l_proc, 90);
4436 END IF;
4437 --
4438 if(l_valueset_r.table_info.meaning_column_name is not null) then
4439 l_select_clause := 'SELECT '||l_valueset_r.table_info.
4440 meaning_column_name||' ';
4441 else
4442 l_select_clause := 'SELECT '||l_valueset_r.table_info.
4443 value_column_name||' ';
4444 end if;
4445
4446 l_sql_text_id := l_select_clause||l_from_where;
4447
4448 IF ( INSTR( UPPER(l_sql_text_id) , 'WHERE') = 0 )
4449 THEN
4450
4451 l_sql_text_id := l_select_clause||l_from_where ||'WHERE '||l_valueset_r.table_info.id_column_name||' = ';
4452
4453 ELSE
4454
4455 l_sql_text_id := l_select_clause||l_from_where ||' and '||l_valueset_r.table_info.id_column_name||' = ';
4456
4457 END IF;
4458
4459
4460 elsif l_valueset_r.validation_type = 'I' then
4461
4462 IF ( g_debug ) THEN
4463 hr_utility.set_location(l_proc, 120);
4464 END IF;
4465 --
4466 -- We can hard code the DESC SQL this time, since we know explicitly
4467 -- how independant value sets are built. This should be changed once
4468 -- we have the procedure from AOL.
4469 --
4470 l_sql_text_id := 'SELECT FLEX_VALUE'||
4471 ' FROM FND_FLEX_VALUES_VL'||
4472 ' WHERE FLEX_VALUE_SET_ID =' || l_value_set_id ||
4473 ' AND ENABLED_FLAG = ''Y'''||
4474 ' AND '''||P_SESSION_DATE||''' BETWEEN'||
4475 ' NVL(START_DATE_ACTIVE,'''||
4476 P_SESSION_DATE||''')'||
4477 ' AND NVL(END_DATE_ACTIVE,'''||
4478 P_SESSION_DATE||''')'||
4479 ' AND FLEX_VALUE = ';
4480
4481 else
4482
4483 -- should only be table or independent value sets
4484
4485 fnd_message.set_name(809,'HXC_GAZ_VALUE_SET_CHANGED');
4486
4487 end if; -- validation type
4488
4489 IF ( g_debug ) THEN
4490 hr_utility.set_location(' Leaving:'||l_proc, 150);
4491 END IF;
4492
4493 RETURN l_sql_text_id;
4494
4495 end get_value_set_sql;
4496
4497
4498 -- public procedure
4499 -- get_flex_info
4500 --
4501 -- description
4502 -- get flex field context segment info. In particular information
4503 -- on the validation and value set associated with each segment
4504 -- within the context
4505 -- Used in the Time Categories form to dynamically set the LOV associated
4506 -- with each mapping component chosen.
4507
4508 PROCEDURE get_flex_info (
4509 p_context_code IN VARCHAR2
4510 , p_seg_info OUT NOCOPY t_seg_info
4511 , p_session_date IN DATE ) IS
4512
4513 l_proc varchar2(72);
4514 r_segments_t hr_flexfield_info.hr_segments_info; -- remember this is a record of tables
4515 l_t_seg_info t_seg_info; -- local table
4516
4517 l_where LONG;
4518 l_upper_where LONG;
4519
4520 FUNCTION parse_sql ( p_sql LONG ) RETURN LONG IS
4521
4522 l_proc varchar2(72);
4523
4524 l_sql_text LONG;
4525
4526 BEGIN
4527
4528
4529 IF ( g_debug ) THEN
4530 l_proc := g_package||'parse_sql';
4531 hr_utility.set_location('Processing:'||l_proc, 5);
4532 END IF;
4533
4534 l_sql_text :=
4535 REPLACE(UPPER(
4536 SUBSTR(p_sql ,1,INSTR(p_sql,',',1,1)-1)||' A,'||
4537 SUBSTR(p_sql ,INSTR(p_sql,',',1,1)+1, ( (INSTR(p_sql,',',1,2)) - (INSTR(p_sql,',',1,1)+1) ))||' B,TO_CHAR('||
4538 SUBSTR(p_sql ,INSTR(p_sql,',',1,2)+1)), 'FROM', ') C FROM');
4539
4540 IF ( g_debug ) THEN
4541 hr_utility.set_location('Processing:'||l_proc, 10);
4542 END IF;
4543
4544 RETURN l_sql_text;
4545
4546 END parse_sql;
4547
4548 BEGIN -- get_flex_info
4549
4550 g_debug := hr_utility.debug_enabled;
4551
4552 IF ( g_debug ) THEN
4553 l_proc := g_package||'get_flex_info';
4554 hr_utility.set_location('Processing:'||l_proc, 5);
4555 END IF;
4556
4557 hr_flexfield_info.initialize;
4558
4559 IF ( g_debug ) THEN
4560 hr_utility.set_location('Processing:'||l_proc, 10);
4561 END IF;
4562
4563 hr_flexfield_info.get_segments (
4564 p_appl_short_name => 'HXC'
4565 , p_flexfield_name => 'OTC Information Types'
4566 , p_context_code => p_context_code
4567 , p_enabled_only => TRUE
4568 , p_segments => r_segments_t
4569 , p_session_date => p_session_date );
4570
4571 IF ( g_debug ) THEN
4572 hr_utility.set_location('Processing:'||l_proc, 20);
4573 END IF;
4574
4575 -- reduce r_segments_t to l_r_seg_info_t
4576
4577 FOR x IN r_segments_t.sequence.FIRST .. r_segments_t.sequence.LAST
4578 LOOP
4579 IF ( g_debug ) THEN
4580 hr_utility.set_location('Processing:'||l_proc, 30);
4581 END IF;
4582
4583 l_t_seg_info(x).application_column_name := r_segments_t.application_column_name(x);
4584 l_t_seg_info(x).segment_name := r_segments_t.segment_name(x);
4585 l_t_seg_info(x).column_prompt := r_segments_t.column_prompt(x);
4586 l_t_seg_info(x).value_set := r_segments_t.value_set(x);
4587 l_t_seg_info(x).validation_type := r_segments_t.validation_type(x);
4588 l_t_seg_info(x).sql_text := r_segments_t.sql_text(x);
4589
4590 l_where := NULL;
4591 l_upper_where := NULL;
4592
4593 -- if value set not table or list or SQL has any $FLEX$ or $PROFILE$ references
4594 -- then we cannot use this to set the record group in the form
4595
4596 IF ( g_debug ) THEN
4597 hr_utility.trace('SQL is '||l_t_seg_info(x).sql_text);
4598 END IF;
4599
4600 IF ( l_t_seg_info(x).validation_type = 'NONE' OR l_t_seg_info(x).value_set IS NULL )
4601 THEN
4602 IF ( g_debug ) THEN
4603 hr_utility.trace('validation type NONE');
4604 END IF;
4605
4606 l_t_seg_info(x).sql_ok := FALSE;
4607 l_t_seg_info(x).no_sql := TRUE;
4608
4609 ELSIF ( l_t_seg_info(x).validation_type = 'INDEPENDENT')
4610 THEN
4611 IF ( g_debug ) THEN
4612 hr_utility.trace('validation type INDEPENDENT');
4613 END IF;
4614
4615 l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4616 l_t_seg_info(x).sql_ok := TRUE;
4617 ELSE
4618 IF ( g_debug ) THEN
4619 hr_utility.trace('validation type '||l_t_seg_info(x).validation_type);
4620 END IF;
4621
4622 l_t_seg_info(x).sql_text := parse_sql ( l_t_seg_info(x).sql_text );
4623
4624 chk_profile_flex( l_t_seg_info(x).value_set
4625 , l_where
4626 , l_t_seg_info(x).sql_ok);
4627
4628 IF ( l_where IS NOT NULL )
4629 THEN
4630
4631 l_upper_where := UPPER(l_where);
4632
4633 l_t_seg_info(x).sql_text := REPLACE(l_t_seg_info(x).sql_text, l_upper_where, l_where);
4634
4635 END IF;
4636
4637 END IF;
4638
4639 END LOOP;
4640
4641 IF ( g_debug ) THEN
4642 hr_utility.set_location('Processing:'||l_proc, 40);
4643 END IF;
4644
4645 p_seg_info := l_t_seg_info;
4646
4647 END get_flex_info;
4648
4649
4650
4651 -- public function
4652 -- get_flex_value
4653 --
4654 -- description
4655 -- retrieves the value based on the id and flex value set id
4656 -- used in the hxc_time_category_comps_v view.
4657
4658 FUNCTION get_flex_value ( p_flex_value_set_id NUMBER
4659 , p_id VARCHAR2 ) RETURN VARCHAR2 IS
4660
4661 l_sql LONG;
4662 l_description VARCHAR2(150) := NULL;
4663
4664 -- GPM v115.26
4665
4666 CURSOR csr_get_element_name ( p_element_type_id VARCHAR2 ) IS
4667 select pett.element_name Display_Value
4668 from pay_element_types_f_tl pett
4669 where pett.element_type_id = p_element_type_id
4670 and pett.language = USERENV('LANG');
4671
4672 l_csr INTEGER;
4673
4674 BEGIN
4675
4676 g_debug := hr_utility.debug_enabled;
4677
4678 IF ( p_flex_value_set_id = -1 )
4679 THEN
4680
4681 -- no value set therefore at the moment is 'Dummy Element Context'
4682
4683 OPEN csr_get_element_name ( p_id );
4684 FETCH csr_get_element_name INTO l_description;
4685 CLOSE csr_get_element_name;
4686
4687 ELSIF ( p_flex_value_set_id = -2 )
4688 THEN
4689
4690 -- no value set at all -free form text Valeu = Value_Id
4691
4692 l_description := p_id;
4693
4694 ELSE
4695
4696 IF ( g_debug ) THEN
4697 hr_utility.trace('gaz - before');
4698 END IF;
4699
4700 l_sql := get_value_set_sql (
4701 p_flex_value_set_id => p_flex_value_set_id
4702 , p_session_date => sysdate );
4703
4704 IF ( g_debug ) THEN
4705 hr_utility.trace('gaz - before');
4706 hr_utility.trace('gaz - l sql is '||l_sql);
4707 hr_utility.trace('gaz - p_id is '||p_id);
4708 END IF;
4709
4710 BEGIN
4711
4712 execute immediate l_sql||''''||REPLACE(p_id,'''','''''')||'''' INTO l_description;
4713
4714 EXCEPTION WHEN OTHERS THEN
4715
4716 -- GPM v115.12 WWB 3254482
4717 -- for customers who modify the value sets
4718 -- which allow duplicate entries !!!
4719
4720 IF SQLCODE = -1422 -- exact fetch returns more then one row
4721 THEN
4722 null;
4723 ELSE
4724 raise;
4725 END IF;
4726 END;
4727
4728 END IF;
4729
4730 RETURN l_description;
4731
4732 END get_flex_value;
4733
4734
4735 -- prublic function
4736 -- get_time_category_id
4737 --
4738 -- description
4739 -- get time category id based on time category name
4740
4741 FUNCTION get_time_category_id ( p_time_category_name VARCHAR2 ) RETURN NUMBER IS
4742
4743 CURSOR csr_get_time_category_id IS
4744 SELECT htc.time_category_id
4745 FROM hxc_time_categories htc
4746 WHERE htc.time_category_name = p_time_category_name;
4747
4748 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4749
4750 BEGIN
4751
4752 OPEN csr_get_time_category_id;
4753 FETCH csr_get_time_category_id INTO l_time_category_id;
4754 CLOSE csr_get_time_category_id;
4755
4756 RETURN l_time_category_id;
4757
4758 END get_time_category_id;
4759
4760
4761
4762 -- PUBLIC function for backward compatibility with Phase I Time Categories
4763
4764 PROCEDURE initialise_time_category (
4765 p_time_category_id NUMBER
4766 , p_tco_att hxc_self_service_time_deposit.building_block_attribute_info ) IS
4767
4768 l_tc_bb_ok_tab hxc_time_category_utils_pkg.g_tc_bb_ok_tab%TYPE;
4769 l_tc_bb_ok_string VARCHAR2(32000);
4770 l_tc_bb_not_ok_string VARCHAR2(32000);
4771
4772 l_proc VARCHAR2(72);
4773
4774
4775 BEGIN
4776
4777 g_debug := hr_utility.debug_enabled;
4778
4779 g_tc_bb_ok_tab.delete;
4780
4781
4782 -- Bug 6710408
4783 -- Put down the below statements to explicitly NULL out the master
4784 -- time category info record elements. Had this change when customer
4785 -- complained of some value getting preloaded here, making the process
4786 -- error out.
4787 -- Added few extra trace messages too, for clarity in future.
4788
4789 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_category_id := NULL;
4790 hxc_time_category_utils_pkg.g_master_tc_info_rec.time_card_id := NULL;
4791 hxc_time_category_utils_pkg.g_master_tc_info_rec.operator := NULL;
4792 hxc_time_category_utils_pkg.g_master_tc_info_rec.attribute_count := NULL;
4793
4794
4795
4796 IF ( g_debug ) THEN
4797 l_proc := g_package||'initialise_time_category';
4798 hr_utility.set_location('Entering '||l_proc, 10);
4799 hr_utility.trace('Initialise_time_category for time_category_id :'||p_time_category_id);
4800 hr_utility.trace('Assigned NULL to the master tc id record ');
4801 END IF;
4802
4803 hxc_time_category_utils_pkg.evaluate_time_category (
4804 p_time_category_id => p_time_category_id
4805 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
4806 , p_tc_bb_ok_string => l_tc_bb_ok_string
4807 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string );
4808
4809 IF ( g_debug ) THEN
4810 hr_utility.set_location('Leaving '||l_proc, 20);
4811 END IF;
4812
4813 END initialise_time_category;
4814
4815
4816 -- PUBLIC function for backward compatibility with Phase I Time Categories
4817
4818 PROCEDURE initialise_time_category (
4819 p_time_category_id NUMBER
4820 , p_tco_att hxc_attribute_table_type ) IS
4821
4822 l_dummy_att hxc_self_service_time_deposit.building_block_attribute_info;
4823
4824 BEGIN
4825
4826 initialise_time_category (
4827 p_time_category_id => p_time_category_id
4828 , p_tco_att => l_dummy_att );
4829
4830 END initialise_time_category;
4831
4832
4833
4834 -- public function
4835 -- category_timecard_hrs
4836 --
4837 -- description
4838 -- Returns the number of hours for timecard
4839 -- for a specified time category name
4840
4841 FUNCTION category_timecard_hrs (
4842 p_tbb_id NUMBER
4843 , p_tbb_ovn NUMBER
4844 , p_time_category_name VARCHAR2 ) RETURN NUMBER IS
4845
4846 CURSOR csr_sum_all_timecard_hrs ( p_tbb_id NUMBER, p_tbb_ovn NUMBER ) IS
4847 SELECT SUM( DECODE( detail.type, 'RANGE',
4848 (((detail.stop_time)-(detail.start_time))*24),
4849 NVL(detail.measure, 0) )) hrs
4850 FROM hxc_latest_details tbb_latest,
4851 hxc_time_building_blocks detail,
4852 hxc_time_building_blocks day
4853 where day.parent_building_block_id = p_tbb_id
4854 and day.parent_building_block_ovn = p_tbb_ovn
4855 and detail.parent_building_block_id =
4856 day.time_building_block_id
4857 and detail.parent_building_block_ovn =
4858 day.object_version_number
4859 and tbb_latest.time_building_block_id = detail.time_building_Block_id
4860 and tbb_latest.object_version_number = detail.object_version_number
4861 and detail.date_to = hr_general.end_of_time;
4862
4863
4864 l_timecard_hrs NUMBER := 0;
4865 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4866
4867 l_tc_bb_ok_tab hxc_time_category_utils_pkg.t_tc_bb_ok;
4868 l_tc_bb_ok_string VARCHAR2(32000);
4869 l_tc_bb_not_ok_string VARCHAR2(32000);
4870
4871 l_proc varchar2(72);
4872
4873 BEGIN
4874
4875 g_debug := hr_utility.debug_enabled;
4876
4877 IF ( g_debug ) THEN
4878 l_proc := g_package||'category_timecard_hrs';
4879 hr_utility.set_location('Entering '||l_proc, 10);
4880
4881 hr_utility.trace('gaz - time cat id is '||p_time_category_name);
4882 END IF;
4883
4884 IF ( p_time_category_name is not null )
4885 THEN
4886
4887 l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
4888
4889 IF ( g_debug ) THEN
4890 hr_utility.trace('gaz - time cat id is '||to_char(l_time_category_id));
4891 hr_utility.trace('gaz - time bb id is '||to_char(p_tbb_id));
4892 hr_utility.trace('gaz - time ovn id is '||to_char(p_tbb_ovn));
4893
4894 hr_utility.set_location('Processing '||l_proc, 20);
4895 END IF;
4896
4897 -- call evaluate time category with p_scope = 'DETAIL'
4898
4899 evaluate_time_category (
4900 p_time_category_id => l_time_category_id
4901 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
4902 , p_tc_bb_ok_string => l_tc_bb_ok_string
4903 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string
4904 , p_use_temp_table => FALSE
4905 , p_scope => 'TIME'
4906 , p_tbb_id => p_tbb_id
4907 , p_tbb_ovn => p_tbb_ovn );
4908
4909 sum_live_tc_bb_ok_hrs ( p_tc_bb_ok_string => l_tc_bb_ok_string
4910 , p_hrs => l_timecard_hrs );
4911
4912 ELSE
4913
4914 OPEN csr_sum_all_timecard_hrs ( p_tbb_id => p_tbb_id, p_tbb_ovn => p_tbb_ovn );
4915 FETCH csr_sum_all_timecard_hrs INTO l_timecard_hrs;
4916 CLOSE csr_sum_all_timecard_hrs;
4917
4918 END IF;
4919
4920 IF ( g_debug ) THEN
4921 hr_utility.trace('l timecard hrs are '||to_char(NVL( l_timecard_hrs, 0 ) ));
4922 hr_utility.set_location('Leaving '||l_proc, 30);
4923 END IF;
4924
4925 RETURN NVL(l_timecard_hrs, 0);
4926
4927 END category_timecard_hrs;
4928
4929
4930 --Similar to above
4931 --needed because we have to process each detail block
4932 --individually according to precision and rounding rule
4933
4934 FUNCTION category_timecard_hrs_ind (
4935 p_tbb_id NUMBER
4936 , p_tbb_ovn NUMBER
4937 , p_time_category_name VARCHAR2 ) RETURN NUMBER IS
4938
4939 CURSOR csr_timecard_hrs ( p_tbb_id NUMBER, p_tbb_ovn NUMBER ) IS
4940 SELECT DECODE( detail.type, 'RANGE',
4941 nvl((((detail.stop_time)-(detail.start_time))*24),0),
4942 NVL(detail.measure, 0) ) hrs
4943 FROM hxc_latest_details tbb_latest,
4944 hxc_time_building_blocks detail,
4945 hxc_time_building_blocks day
4946 where day.parent_building_block_id = p_tbb_id
4947 and day.parent_building_block_ovn = p_tbb_ovn
4948 and detail.parent_building_block_id =
4949 day.time_building_block_id
4950 and detail.parent_building_block_ovn =
4951 day.object_version_number
4952 and tbb_latest.time_building_block_id = detail.time_building_Block_id
4953 and tbb_latest.object_version_number = detail.object_version_number
4954 and detail.date_to = hr_general.end_of_time;
4955
4956 CURSOR c_tc_resource_id(
4957 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE,
4958 p_tbb_ovn hxc_time_building_blocks.object_version_number%TYPE
4959 )IS
4960 SELECT tbb.resource_id
4961 FROM hxc_time_building_blocks tbb
4962 WHERE tbb.time_building_block_id = p_tc_bbid
4963 AND tbb.object_version_number = p_tbb_ovn;
4964
4965 /* Bug fix for 5526281 */
4966 CURSOR get_timecard_start_date(
4967 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE,
4968 p_tc_ovnid hxc_time_building_blocks.object_version_number%TYPE
4969 ) IS
4970 SELECT tbb.start_time,tbb.stop_time
4971 FROM hxc_time_building_blocks tbb
4972 WHERE tbb.time_building_block_id = p_tc_bbid
4973 AND tbb.object_version_number = p_tc_ovnid;
4974
4975 cursor emp_hire_info(p_resource_id hxc_time_building_blocks.resource_id%TYPE) IS
4976 select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;
4977 /* end of bug fix for 5526281 */
4978
4979 l_timecard_hrs NUMBER := 0;
4980 l_time_category_id hxc_time_categories.time_category_id%TYPE;
4981
4982 l_tc_bb_ok_tab hxc_time_category_utils_pkg.t_tc_bb_ok;
4983 l_tc_bb_ok_string VARCHAR2(32000);
4984 l_tc_bb_not_ok_string VARCHAR2(32000);
4985
4986 l_precision VARCHAR2(4);
4987 l_rounding_rule VARCHAR2(20);
4988 l_index NUMBER :=1;
4989 l_resource_id NUMBER;
4990 l_tc_start_date DATE;
4991
4992 /* Bug fix for 5526281 */
4993 l_tc_end_date date;
4994 l_pref_eval_date date;
4995 l_emp_hire_date date;
4996 /* end of bug fix for 5526281 */
4997
4998 l_proc varchar2(72);
4999
5000 BEGIN
5001
5002 g_debug := hr_utility.debug_enabled;
5003
5004 open c_tc_resource_id(p_tbb_id, p_tbb_ovn);
5005 fetch c_tc_resource_id into l_resource_id;
5006 close c_tc_resource_id;
5007
5008 /* Bug fix for 5526281 */
5009 OPEN get_timecard_start_date (p_tbb_id, p_tbb_ovn);
5010 FETCH get_timecard_start_date into l_tc_start_date,l_tc_end_date;
5011 CLOSE get_timecard_start_date;
5012
5013 OPEN emp_hire_info (l_resource_id);
5014 FETCH emp_hire_info into l_emp_hire_date;
5015 CLOSE emp_hire_info;
5016
5017 if trunc(l_emp_hire_date) >= trunc(l_tc_start_date) and trunc(l_emp_hire_date) <= trunc(l_tc_end_date) then
5018 l_pref_eval_date := trunc(l_emp_hire_date);
5019 else
5020 l_pref_eval_date := trunc(l_tc_start_date);
5021 end if;
5022
5023 l_precision := hxc_preference_evaluation.resource_preferences
5024 (l_resource_id,
5025 'TC_W_TCRD_UOM',
5026 3,
5027 l_pref_eval_date
5028 );
5029
5030 l_rounding_rule := hxc_preference_evaluation.resource_preferences
5031 (l_resource_id,
5032 'TC_W_TCRD_UOM',
5033 4,
5034 l_pref_eval_date
5035 );
5036 /* end of bug fix for 5526281 */
5037 if l_precision is null
5038 then
5039 l_precision := '2';
5040 end if;
5041
5042 if l_rounding_rule is null
5043 then
5044 l_rounding_rule := 'ROUND_TO_NEAREST';
5045 end if;
5046
5047 IF ( p_time_category_name is not null )
5048 THEN
5049
5050 l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
5051
5052 -- call evaluate time category with p_scope = 'DETAIL'
5053
5054 evaluate_time_category (
5055 p_time_category_id => l_time_category_id
5056 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
5057 , p_tc_bb_ok_string => l_tc_bb_ok_string
5058 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string
5059 , p_use_temp_table => FALSE
5060 , p_scope => 'TIME'
5061 , p_tbb_id => p_tbb_id
5062 , p_tbb_ovn => p_tbb_ovn );
5063
5064
5065 sum_live_tc_bb_ok_hrs( p_tc_bb_ok_string => l_tc_bb_ok_string
5066 , p_hrs => l_timecard_hrs
5067 , p_rounding_rule => l_rounding_rule
5068 , p_decimal_precision => l_precision);
5069
5070 ELSE
5071
5072 for hrs_rec in csr_timecard_hrs(p_tbb_id,p_tbb_ovn) loop
5073
5074 l_timecard_hrs := l_timecard_hrs + hxc_find_notify_aprs_pkg.apply_round_rule(
5075 l_rounding_rule,
5076 l_precision,
5077 nvl(hrs_rec.hrs,0)
5078 );
5079
5080 l_index := l_index +1;
5081 end loop;
5082
5083 END IF;
5084
5085 RETURN NVL(l_timecard_hrs, 0);
5086
5087 END category_timecard_hrs_ind;
5088
5089
5090
5091
5092 -- public function
5093 -- category_detail_hrs (Overloaded)
5094 --
5095 -- description
5096 -- Returns the number of hours for 1 DETAIL time building block
5097 -- for a specified time category name
5098
5099 FUNCTION category_detail_hrs (
5100 p_tbb_id NUMBER
5101 , p_tbb_ovn NUMBER
5102 , p_time_category_name VARCHAR2 ) RETURN NUMBER IS
5103
5104 l_timecard_hrs NUMBER;
5105 l_time_category_id hxc_time_categories.time_category_id%TYPE;
5106
5107 BEGIN
5108
5109 l_time_category_id := get_time_category_id ( p_time_category_name => p_time_category_name );
5110
5111 l_timecard_hrs := category_detail_hrs (
5112 p_tbb_id => p_tbb_id
5113 , p_tbb_ovn => p_tbb_ovn
5114 , p_time_category_id => l_time_category_id );
5115
5116 RETURN l_timecard_hrs;
5117
5118 END category_detail_hrs;
5119
5120
5121
5122 -- public function
5123 -- category_detail_hrs (Overloaded)
5124 --
5125 -- description
5126 -- Returns the number of hours for 1 DETAIL time building block
5127 -- (the global variable g time category id is presumed to be set)
5128
5129 FUNCTION category_detail_hrs (
5130 p_tbb_id NUMBER
5131 , p_tbb_ovn NUMBER ) RETURN NUMBER IS
5132
5133 l_timecard_hrs NUMBER;
5134
5135 BEGIN
5136
5137 l_timecard_hrs := category_detail_hrs (
5138 p_tbb_id => p_tbb_id
5139 , p_tbb_ovn => p_tbb_ovn
5140 , p_time_category_id => hxc_time_category_utils_pkg.g_time_category_id );
5141
5142 RETURN l_timecard_hrs;
5143
5144 END category_detail_hrs;
5145
5146
5147
5148
5149 -- public function
5150 -- category_detail_hrs
5151 --
5152 -- description
5153 -- Returns the number of hours for 1 DETAIL time building block
5154 -- for a specified time category id
5155
5156 FUNCTION category_detail_hrs (
5157 p_tbb_id NUMBER
5158 , p_tbb_ovn NUMBER
5159 , p_time_category_id NUMBER ) RETURN NUMBER IS
5160
5161 l_hrs NUMBER := 0;
5162 l_proc VARCHAR2(72);
5163
5164 l_tc_bb_ok_tab hxc_time_category_utils_pkg.t_tc_bb_ok;
5165 l_tc_bb_ok_string VARCHAR2(32000);
5166 l_tc_bb_not_ok_string VARCHAR2(32000);
5167
5168
5169 CURSOR csr_calc_all IS
5170 SELECT
5171 SUM(NVL(tbb.measure,0) +
5172 ((( NVL(tbb.stop_time,sysdate) - NVL(tbb.start_time,sysdate))*24)))
5173 FROM
5174 hxc_time_building_blocks tbb
5175 WHERE
5176 tbb.time_building_block_id = p_tbb_id AND
5177 tbb.object_version_number = p_tbb_ovn;
5178
5179
5180 BEGIN
5181 g_debug := hr_utility.debug_enabled;
5182
5183 IF ( g_debug ) THEN
5184 l_proc := g_package||'category_detail_hrs';
5185 hr_utility.set_location('Entering '||l_proc, 10);
5186 END IF;
5187
5188 IF ( p_time_category_id IS NULL )
5189 THEN
5190 IF ( g_debug ) THEN
5191 hr_utility.set_location('Processing '||l_proc, 20);
5192 END IF;
5193
5194 OPEN csr_calc_all;
5195 FETCH csr_calc_all INTO l_hrs;
5196 CLOSE csr_calc_all;
5197
5198 ELSE
5199
5200 IF ( g_debug ) THEN
5201 hr_utility.set_location('Processing '||l_proc, 30);
5202 END IF;
5203
5204 -- call evaluate time category with p_scope = 'DETAIL'
5205
5206 evaluate_time_category (
5207 p_time_category_id => p_time_category_id
5208 , p_tc_bb_ok_tab => l_tc_bb_ok_tab
5209 , p_tc_bb_ok_string => l_tc_bb_ok_string
5210 , p_tc_bb_not_ok_string => l_tc_bb_not_ok_string
5211 , p_use_tc_bb_cache => FALSE
5212 , p_use_temp_table => FALSE
5213 , p_scope => 'DETAIL'
5214 , p_tbb_id => p_tbb_id
5215 , p_tbb_ovn => p_tbb_ovn );
5216
5217 sum_live_tc_bb_ok_hrs ( p_tc_bb_ok_string => l_tc_bb_ok_string
5218 , p_hrs => l_hrs );
5219
5220 END IF;
5221
5222 IF ( g_debug ) THEN
5223 hr_utility.set_location('Leaving '||l_proc, 60);
5224 END IF;
5225
5226 RETURN NVL(l_hrs,0);
5227
5228 END category_detail_hrs;
5229
5230
5231
5232
5233
5234 -- public function
5235 -- category_app_period_tc_hrs
5236 --
5237 -- description
5238 -- Returns the number of hours for person within a date range
5239 -- and specified time category and application_period_id
5240
5241 FUNCTION category_app_period_tc_hrs (
5242 p_period_start_time IN DATE
5243 , p_period_stop_time IN DATE
5244 , p_resource_id IN NUMBER
5245 , p_time_category_name IN VARCHAR2
5246 , p_application_period_id IN NUMBER ) RETURN NUMBER IS
5247
5248 l_time_category_id hxc_time_categories.time_category_id%TYPE;
5249 l_hours NUMBER := 0;
5250 l_total_hours NUMBER :=0;
5251 l_precision VARCHAR2(4);
5252 l_rounding_rule VARCHAR2(20);
5253
5254 CURSOR csr_get_total_timecard_hrs IS
5255 SELECT NVL( hours_worked, 0 )
5256 FROM hxc_app_period_total_time_v
5257 WHERE resource_id = p_resource_id
5258 AND start_date BETWEEN p_period_start_time AND p_period_stop_time
5259 AND stop_date BETWEEN p_period_start_time AND p_period_stop_time
5260 AND application_period_id = p_application_period_id;
5261
5262 BEGIN
5263
5264 g_debug := hr_utility.debug_enabled;
5265
5266 IF ( g_debug ) THEN
5267 hr_utility.trace('period_start_time is '||to_char(p_period_start_time, 'dd-mon-yy'));
5268 hr_utility.trace('period_end_time is '||to_char(p_period_stop_time, 'dd-mon-yy'));
5269 hr_utility.trace('resource id is '||to_char(p_resource_id));
5270 hr_utility.trace('app period id is '||to_char(p_application_period_id));
5271 hr_utility.trace('time category name is '||p_time_category_name);
5272 END IF;
5273
5274 l_precision := hxc_preference_evaluation.resource_preferences
5275 (p_resource_id,
5276 'TC_W_TCRD_UOM',
5277 3,
5278 p_period_start_time
5279 );
5280
5281 l_rounding_rule := hxc_preference_evaluation.resource_preferences
5282 (p_resource_id,
5283 'TC_W_TCRD_UOM',
5284 4,
5285 p_period_start_time
5286 );
5287 if l_precision is null
5288 then
5289 l_precision := '2';
5290 end if;
5291
5292 if l_rounding_rule is null
5293 then
5294 l_rounding_rule := 'ROUND_TO_NEAREST';
5295 end if;
5296
5297 hxc_time_category_utils_pkg.g_time_category_id :=
5298 get_time_category_id ( p_time_category_name => p_time_category_name );
5299
5300 OPEN csr_get_total_timecard_hrs;
5301 FETCH csr_get_total_timecard_hrs INTO l_hours;
5302
5303 WHILE csr_get_total_timecard_hrs%FOUND
5304 LOOP
5305
5306 l_total_hours := l_total_hours + hxc_find_notify_aprs_pkg.apply_round_rule(
5307 l_rounding_rule,
5308 l_precision,
5309 l_hours
5310 );
5311
5312 FETCH csr_get_total_timecard_hrs INTO l_hours;
5313
5314 END LOOP;
5315
5316 CLOSE csr_get_total_timecard_hrs;
5317
5318 RETURN l_total_hours;
5319
5320 END category_app_period_tc_hrs;
5321
5322
5323
5324
5325 -- public procedure
5326 -- process_tc_timecard
5327 --
5328 -- description
5329 --
5330 -- SEE HEADER FOR DETAILS
5331
5332 PROCEDURE process_tc_timecard (
5333 p_tco_att hxc_self_service_time_deposit.building_block_attribute_info
5334 , p_time_cat t_time_category
5335 , p_bb_ok_tab IN OUT NOCOPY t_tc_bb_ok
5336 , p_operator VARCHAR2 default 'OR' ) IS
5337
5338 l_proc VARCHAR2(72) := g_package||'process_tc_timecard';
5339
5340 BEGIN
5341
5342 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5343 fnd_message.set_token('PROCEDURE', l_proc);
5344 fnd_message.set_token('STEP','Procedure redundant use evaluate_time_category');
5345 fnd_message.raise_error;
5346
5347 END process_tc_timecard;
5348
5349
5350 PROCEDURE time_category_string ( p_time_category_id NUMBER
5351 , p_dyn_or_tab IN VARCHAR2
5352 , p_dyn_sql IN OUT NOCOPY LONG
5353 , p_category_tab IN OUT NOCOPY t_time_category
5354 , p_operator IN OUT NOCOPY VARCHAR2 ) IS
5355
5356 l_proc varchar2(72) := g_package||'time_category_string';
5357
5358 BEGIN
5359
5360 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5361 fnd_message.set_token('PROCEDURE', l_proc);
5362 fnd_message.set_token('STEP','Procedure redundant use evaluate_time_category');
5363 fnd_message.raise_error;
5364
5365 END time_category_string;
5366
5367
5368
5369 PROCEDURE alias_value_ref_int_chk ( p_alias_value_id NUMBER
5370 , p_action VARCHAR2 ) IS
5371
5372 CURSOR csr_get_time_category IS
5373 SELECT
5374 htc.time_category_name
5375 , tcc.time_category_comp_id
5376 , tcc.time_category_id
5377 , tcc.ref_time_category_id
5378 , tcc.component_type_id
5379 , tcc.flex_value_set_id
5380 , tcc.value_id
5381 , tcc.is_null
5382 , tcc.equal_to
5383 , tcc.type
5384 , tcc.object_version_number
5385 FROM hxc_time_categories htc
5386 , hxc_time_category_comps tcc
5387 WHERE tcc.time_category_id = htc.time_category_id
5388 AND tcc.type = 'AN'
5389 AND tcc.component_type_id = p_alias_value_id
5390 ORDER BY htc.time_category_id;
5391
5392 l_time_category_name hxc_time_categories.time_category_name%TYPE;
5393
5394 l_rec hxc_tcc_shd.g_rec_type;
5395
5396 BEGIN
5397
5398 FOR tc IN csr_get_time_category
5399 LOOP
5400
5401 IF ( p_action = 'DELETE' )
5402 THEN
5403
5404 IF ( l_time_category_name IS NULL )
5405 THEN
5406
5407 l_time_category_name := tc.time_category_name;
5408
5409 ELSE
5410
5411 l_time_category_name := l_time_category_name||', '||tc.time_category_name;
5412
5413 END IF;
5414
5415 ELSE
5416
5417 -- must be update
5418
5419 l_rec.time_category_comp_id := tc.time_category_comp_id;
5420 l_rec.time_category_id := tc.time_category_id;
5421 l_rec.ref_time_category_id := tc.ref_time_category_id;
5422 l_rec.component_type_id := tc.component_type_id;
5423 l_rec.flex_value_set_id := tc.flex_value_set_id;
5424 l_rec.value_id := tc.value_id;
5425 l_rec.is_null := tc.is_null;
5426 l_rec.equal_to := tc.equal_to;
5427 l_rec.type := tc.type;
5428 l_rec.object_version_number := tc.object_version_number;
5429
5430 update_time_category_comp_sql ( l_rec );
5431
5432 END IF;
5433
5434 END LOOP;
5435
5436 IF ( l_time_category_name IS NOT NULL )
5437 THEN
5438
5439 fnd_message.set_name('HXC', 'HXC_HTC_ALIAS_REF_INT_CHECK');
5440 fnd_message.set_token('TC_NAME', l_time_category_name );
5441 fnd_message.raise_error;
5442
5443 END IF;
5444
5445
5446 END alias_value_ref_int_chk;
5447
5448
5449
5450 PROCEDURE alias_definition_ref_int_chk ( p_alias_definition_id NUMBER ) IS
5451
5452 CURSOR csr_get_time_category IS
5453 SELECT htc.time_category_name
5454 FROM hxc_time_categories htc
5455 , hxc_time_category_comps tcc
5456 , hxc_alias_values av
5457 , hxc_alias_definitions ad
5458 WHERE
5459 ad.alias_definition_id = p_alias_definition_id
5460 AND
5461 av.alias_definition_id = ad.alias_definition_id
5462 AND
5463 tcc.component_type_id = av.alias_value_id AND
5464 tcc.type = 'AN'
5465 AND
5466 tcc.time_category_id = htc.time_category_id;
5467
5468 l_time_category_name hxc_time_categories.time_category_name%TYPE;
5469
5470
5471 BEGIN
5472
5473 FOR tc IN csr_get_time_category
5474 LOOP
5475
5476 IF ( l_time_category_name IS NULL )
5477 THEN
5478
5479 l_time_category_name := tc.time_category_name;
5480
5481 ELSE
5482
5483 l_time_category_name := l_time_category_name||', '||tc.time_category_name;
5484
5485 END IF;
5486
5487 END LOOP;
5488
5489
5490 IF ( l_time_category_name IS NOT NULL )
5491 THEN
5492
5493 fnd_message.set_name('HXC', 'HXC_HTC_ALIAS_REF_INT_CHECK');
5494 fnd_message.set_token('TC_NAME', l_time_category_name );
5495 fnd_message.raise_error;
5496
5497 END IF;
5498
5499
5500
5501
5502 null;
5503
5504 END alias_definition_ref_int_chk;
5505
5506
5507
5508 PROCEDURE alias_type_comp_ref_int_chk ( p_alias_type_id NUMBER ) IS
5509
5510 CURSOR csr_get_time_category_comps IS
5511 SELECT
5512 tcc.time_category_comp_id
5513 , tcc.time_category_id
5514 , tcc.ref_time_category_id
5515 , tcc.component_type_id
5516 , tcc.flex_value_set_id
5517 , tcc.value_id
5518 , tcc.is_null
5519 , tcc.equal_to
5520 , tcc.type
5521 , tcc.object_version_number
5522 FROM hxc_time_category_comps tcc
5523 , hxc_alias_values av
5524 , hxc_alias_definitions ad
5525 , hxc_alias_types hat
5526 WHERE
5527 hat.alias_type_id = p_alias_type_id
5528 AND
5529 ad.alias_type_id = hat.alias_type_id
5530 AND
5531 av.alias_definition_id = ad.alias_definition_id
5532 AND
5533 tcc.component_type_id = av.alias_value_id AND
5534 tcc.type = 'AN';
5535
5536 l_rec hxc_tcc_shd.g_rec_type;
5537
5538 BEGIN
5539
5540 g_debug := hr_utility.debug_enabled;
5541
5542 IF ( g_debug ) THEN
5543 hr_utility.trace('In ref chk');
5544 END IF;
5545
5546 FOR tc IN csr_get_time_category_comps
5547 LOOP
5548
5549 l_rec.time_category_comp_id := tc.time_category_comp_id;
5550 l_rec.time_category_id := tc.time_category_id;
5551 l_rec.ref_time_category_id := tc.ref_time_category_id;
5552 l_rec.component_type_id := tc.component_type_id;
5553 l_rec.flex_value_set_id := tc.flex_value_set_id;
5554 l_rec.value_id := tc.value_id;
5555 l_rec.is_null := tc.is_null;
5556 l_rec.equal_to := tc.equal_to;
5557 l_rec.type := tc.type;
5558 l_rec.object_version_number := tc.object_version_number;
5559
5560 IF ( g_debug ) THEN
5561 hr_utility.trace('about to call update');
5562 END IF;
5563
5564 update_time_category_comp_sql ( l_rec );
5565
5566 END LOOP;
5567
5568 END alias_type_comp_ref_int_chk;
5569 --
5570 -- ---------------------------------------------------------------------------
5571 -- |------------------------< reset_cache >----------------------------------|
5572 -- ---------------------------------------------------------------------------
5573 --
5574 Function reset_cache Return Boolean is
5575
5576 Begin
5577 -- Bug 5469357 : Called from Project Manager approval.
5578 -- Keep this up to date.
5579 g_tc_cache.delete;
5580 g_tc_bb_ok_cache.delete;
5581
5582 return true;
5583
5584 Exception
5585 When Others then
5586 return false;
5587
5588 End reset_cache;
5589
5590 -- Bug 13817976
5591 -- Procedure below creates a remodelled version of time category comps.
5592
5593 PROCEDURE insert_tc_comps2(p_tc_id IN NUMBER,
5594 p_tcc_id IN NUMBER,
5595 p_type IN VARCHAR2,
5596 p_value_id IN VARCHAR2,
5597 p_component_type_id IN NUMBER)
5598 IS
5599
5600
5601 l_bbit_id NUMBER;
5602 l_bb_type VARCHAR2(200);
5603 l_rowid VARCHAR2(50);
5604 l_att1 VARCHAR2(150);
5605 l_att2 VARCHAR2(150);
5606 l_segment VARCHAR2(150);
5607 l_value VARCHAR2(150);
5608
5609 CURSOR get_mapping_columns(p_comp_id IN NUMBER)
5610 IS SELECT hmc.bld_blk_info_type_id,
5611 hmc.segment,
5612 bbit.bld_blk_info_type
5613 FROM hxc_mapping_components hmc,
5614 hxc_bld_blk_info_types bbit
5615 WHERE hmc.mapping_component_id = p_comp_id
5616 AND hmc.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
5617
5618
5619 CURSOR get_mapping_columns_an(p_comp_id IN NUMBER)
5620 IS SELECT hmc.bld_blk_info_type_id,
5621 hmc.segment,
5622 bbit.bld_blk_info_type,
5623 decode(hatc.component_type,'ATTRIBUTE1',hav.attribute1,
5624 'ATTRIBUTE2',hav.attribute2,
5625 'ATTRIBUTE1',hav.attribute1,
5626 'ATTRIBUTE2',hav.attribute2,
5627 'ATTRIBUTE3',hav.attribute3,
5628 'ATTRIBUTE4',hav.attribute4,
5629 'ATTRIBUTE5',hav.attribute5,
5630 'ATTRIBUTE6',hav.attribute6,
5631 'ATTRIBUTE7',hav.attribute7,
5632 'ATTRIBUTE8',hav.attribute8,
5633 'ATTRIBUTE9',hav.attribute9,
5634 'ATTRIBUTE10',hav.attribute10,
5635 'ATTRIBUTE11',hav.attribute11,
5636 'ATTRIBUTE12',hav.attribute12,
5637 'ATTRIBUTE13',hav.attribute13,
5638 'ATTRIBUTE14',hav.attribute14,
5639 'ATTRIBUTE15',hav.attribute15,
5640 'ATTRIBUTE16',hav.attribute16,
5641 'ATTRIBUTE17',hav.attribute17,
5642 'ATTRIBUTE18',hav.attribute18,
5643 'ATTRIBUTE19',hav.attribute19,
5644 'ATTRIBUTE20',hav.attribute20,
5645 'ATTRIBUTE21',hav.attribute21,
5646 'ATTRIBUTE22',hav.attribute22,
5647 'ATTRIBUTE23',hav.attribute23,
5648 'ATTRIBUTE24',hav.attribute24,
5649 'ATTRIBUTE25',hav.attribute25,
5650 'ATTRIBUTE26',hav.attribute26,
5651 'ATTRIBUTE27',hav.attribute27,
5652 'ATTRIBUTE28',hav.attribute28,
5653 'ATTRIBUTE29',hav.attribute29,
5654 'ATTRIBUTE30',hav.attribute30 ) value
5655 FROM hxc_alias_values hav,
5656 hxc_alias_definitions had,
5657 hxc_alias_type_components hatc,
5658 hxc_mapping_components hmc,
5659 hxc_bld_blk_info_types bbit
5660 WHERE alias_value_id = p_comp_id
5661 AND hav.alias_definition_id = had.alias_definition_id
5662 AND had.alias_type_id = hatc.alias_type_id
5663 AND hatc.mapping_component_id = hmc.mapping_component_id
5664 AND hmc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
5665 ORDER BY hmc.segment ;
5666
5667 l_sql VARCHAR2(4000) :=
5668 'UPDATE hxc_time_category_comps2
5669 SET COLUMN = :1,
5670 bld_blk_info_type_id = :2,
5671 attribute_category = :3
5672 WHERE rowid = :4 ';
5673
5674
5675 l_dummy_sql VARCHAR2(4000) :=
5676 'UPDATE hxc_time_category_comps2
5677 SET attribute_category = :1,
5678 bld_blk_info_type_id = :2
5679 WHERE rowid = :3';
5680
5681
5682 l_sql1 VARCHAR2(4000);
5683
5684
5685 BEGIN
5686
5687 -- Delete if the component exists already.
5688 DELETE FROM hxc_time_category_comps2
5689 WHERE time_category_id = p_tc_id
5690 AND time_category_comp_id = p_tcc_id;
5691
5692
5693 -- Insert into the table one record with NULL against all the attribute columns.
5694 -- We need the value 'NULL' and not NULL value.
5695 -- If we use NULL value for all the empty columns, the application would have to query
5696 -- the table later like below.
5697 --
5698 -- htcc2.attribute1 IS NULL OR
5699 -- htcc2.atttribute1 = hta.attribute1
5700 --
5701 -- This way, an index cannot be used on the table.
5702 --
5703 -- htcc2.attribute1 = 'NULL'
5704 -- htcc2.atttribute1 = hta.attribute1
5705 -- uses an INDEX on the column if available.
5706 --
5707
5708 INSERT INTO hxc_time_category_comps2
5709 (time_category_id,
5710 time_category_comp_id,
5711 ATTRIBUTE1,
5712 ATTRIBUTE2,
5713 ATTRIBUTE3,
5714 ATTRIBUTE4,
5715 ATTRIBUTE5,
5716 ATTRIBUTE6,
5717 ATTRIBUTE7,
5718 ATTRIBUTE8,
5719 ATTRIBUTE9,
5720 ATTRIBUTE10,
5721 ATTRIBUTE11,
5722 ATTRIBUTE12,
5723 ATTRIBUTE13,
5724 ATTRIBUTE14,
5725 ATTRIBUTE15,
5726 ATTRIBUTE16,
5727 ATTRIBUTE17,
5728 ATTRIBUTE18,
5729 ATTRIBUTE19,
5730 ATTRIBUTE20,
5731 ATTRIBUTE21,
5732 ATTRIBUTE22,
5733 ATTRIBUTE23,
5734 ATTRIBUTE24,
5735 ATTRIBUTE25,
5736 ATTRIBUTE26,
5737 ATTRIBUTE27,
5738 ATTRIBUTE28,
5739 ATTRIBUTE29,
5740 ATTRIBUTE30,
5741 attribute_category
5742 )
5743 VALUES ( p_tc_id,
5744 p_tcc_id,
5745 'NULL',
5746 'NULL',
5747 'NULL',
5748 'NULL',
5749 'NULL',
5750 'NULL',
5751 'NULL',
5752 'NULL',
5753 'NULL',
5754 'NULL',
5755 'NULL',
5756 'NULL',
5757 'NULL',
5758 'NULL',
5759 'NULL',
5760 'NULL',
5761 'NULL',
5762 'NULL',
5763 'NULL',
5764 'NULL',
5765 'NULL',
5766 'NULL',
5767 'NULL',
5768 'NULL',
5769 'NULL',
5770 'NULL',
5771 'NULL',
5772 'NULL',
5773 'NULL',
5774 'NULL',
5775 'NULL')
5776 RETURNING rowid INto l_rowid;
5777
5778
5779 IF p_type = 'MC'
5780 THEN
5781 -- Fetch the mapping details.
5782 OPEN get_mapping_columns(p_component_type_id);
5783 FETCH get_mapping_columns INTO l_bbit_id,
5784 l_segment,
5785 l_bb_type;
5786 CLOSE get_mapping_columns;
5787
5788
5789 IF l_segment <> 'ATTRIBUTE_CATEGORY'
5790 THEN
5791
5792 -- Replace the correct column in the UPDATE statement.
5793 l_sql1 := REPLACE(l_sql,'COLUMN',l_segment);
5794
5795 EXECUTE IMMEDIATE l_sql1 USING p_value_id,
5796 l_bbit_id,
5797 l_bb_type,
5798 l_rowid;
5799 ELSE
5800 -- This means we have a Dummmy XXXX Context.
5801 l_bb_type := UPPER(l_bb_type);
5802 l_bb_type := REPLACE(l_bb_type,'DUMMY ');
5803 l_bb_type := REPLACE(l_bb_type,'CONTEXT');
5804 l_bb_type := l_bb_type||'- '||p_value_id;
5805
5806 EXECUTE IMMEDIATE l_dummy_sql USING l_bb_type,
5807 l_bbit_id,
5808 l_rowid;
5809
5810 END IF;
5811
5812 ELSIF p_type = 'AN'
5813 THEN
5814 -- Fetch the ampping details for AN
5815
5816 OPEN get_mapping_columns_an(p_component_type_id);
5817 LOOP
5818 FETCH get_mapping_columns_an INTO l_bbit_id,
5819 l_segment,
5820 l_bb_type,
5821 l_value;
5822 EXIT WHEN get_mapping_columns_an%NOTFOUND;
5823
5824 IF l_segment <> 'ATTRIBUTE_CATEGORY'
5825 THEN
5826
5827 l_sql1 := REPLACE(l_sql,'COLUMN',l_segment);
5828
5829 EXECUTE IMMEDIATE l_sql1 USING l_value,
5830 l_bbit_id,
5831 l_bb_type,
5832 l_rowid;
5833 ELSE
5834 l_bb_type := UPPER(l_bb_type);
5835 l_bb_type := REPLACE(l_bb_type,'DUMMY ');
5836 l_bb_type := REPLACE(l_bb_type,'CONTEXT');
5837 l_bb_type := l_bb_type||'- '||l_value;
5838
5839 EXECUTE IMMEDIATE l_dummy_sql USING l_bb_type,l_bbit_id,l_rowid;
5840
5841 END IF;
5842
5843
5844 END LOOP;
5845 CLOSE get_mapping_columns_an;
5846
5847
5848 END IF;
5849
5850 END insert_tc_comps2;
5851
5852 -- Procedure deletes any entry in hxc_time_category_comps2 for this component.
5853
5854 PROCEDURE delete_tc_comps2(p_tcc_id IN NUMBER)
5855 IS
5856
5857 BEGIN
5858 DELETE FROM hxc_time_category_comps2
5859 WHERE time_category_comp_id = p_tcc_id;
5860
5861 END delete_tc_comps2;
5862
5863
5864 end hxc_time_category_utils_pkg;