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