[Home] [Help]
PACKAGE BODY: APPS.HXC_MAPPING_UTILITIES
Source
1 package body hxc_mapping_utilities as
2 /* $Header: hxcmputl.pkb 120.12 2011/07/12 06:14:24 pnshukla noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 CURSOR csr_get_timecard ( p_timecard_bb_id NUMBER
7 , p_timecard_ovn NUMBER
8 , p_start_date DATE
9 , p_end_date DATE ) IS
10 SELECT
11 bb.time_building_block_id bb_id
12 , bb.object_Version_number bb_ovn
13 , bb.scope
14 , bb.type
15 , bb.start_time
16 , bb.stop_time
17 , bb.measure
18 , bb.date_to
19 , bb.comment_text
20 FROM
21 hxc_time_building_blocks bb
22 WHERE
23 bb.object_version_number = (
24 SELECT MAX(bb1.object_version_number)
25 FROM hxc_time_building_blocks bb1
26 WHERE bb1.time_building_block_id = bb.time_building_block_id )
27 AND
28 ((
29 p_start_date BETWEEN
30 DECODE ( bb.type, 'RANGE', bb.start_time,
31 ( hxc_mapping_utilities.get_day_date
32 ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
33 AND
34 DECODE ( bb.type, 'RANGE', bb.stop_time,
35 ( hxc_mapping_utilities.get_day_date
36 ( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
37 OR
38 p_end_date BETWEEN
39 DECODE ( bb.type, 'RANGE', bb.start_time,
40 ( hxc_mapping_utilities.get_day_date
41 ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
42 AND
43 DECODE ( bb.type, 'RANGE', bb.stop_time,
44 ( hxc_mapping_utilities.get_day_date
45 ( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) ) )
46 OR (
47 DECODE ( bb.type, 'RANGE', bb.start_time,
48 ( hxc_mapping_utilities.get_day_date
49 ( 'START', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
50 BETWEEN p_start_date AND p_end_date
51 OR
52 DECODE ( bb.type, 'RANGE', bb.stop_time,
53 ( hxc_mapping_utilities.get_day_date
54 ( 'STOP', bb.parent_building_block_id, bb.parent_building_block_ovn ) ) )
55 BETWEEN p_start_date AND p_end_date ))
56 START WITH bb.time_building_block_id = p_timecard_bb_id
57 AND bb.object_version_number = p_timecard_ovn
58 CONNECT BY PRIOR bb.time_building_block_id = bb.parent_building_block_id
59 AND PRIOR bb.object_version_number = bb.parent_building_block_ovn;
60
61 -- ****************************************
62 -- Declare mapping changed local functions
63 -- ****************************************
64
65 FUNCTION get_field_mappings (
66 p_mapping_id hxc_mappings.mapping_id%TYPE )
67 RETURN hxc_generic_retrieval_pkg.t_field_mappings
68 IS
69 --
70 l_mapping_record hxc_generic_retrieval_pkg.r_field_mappings;
71 l_mappings_table hxc_generic_retrieval_pkg.t_field_mappings;
72 --
73 CURSOR csr_get_mappings IS
74 SELECT
75 mpc.bld_blk_info_type_id
76 , UPPER(mpc.field_name)
77 , mpc.segment
78 , bbit.bld_blk_info_type context
79 , bbitu.building_block_category category
80 FROM
81 hxc_bld_blk_info_type_usages bbitu
82 , hxc_bld_blk_info_types bbit
83 , hxc_mapping_components mpc
84 , hxc_mapping_comp_usages mcu
85 , hxc_mappings map
86 WHERE map.mapping_id = p_mapping_id
87 AND
88 mcu.mapping_id = map.mapping_id
89 AND
90 mpc.mapping_component_id= mcu.mapping_component_id
91 AND
92 bbit.bld_blk_info_type_id = mpc.bld_blk_info_type_id
93 AND
94 bbitu.bld_blk_info_type_id = bbit.bld_blk_info_type_id
95 ORDER BY 1;
96
97
98 l_table_index NUMBER := 0;
99
100 l_proc VARCHAR2(72);
101
102
103 BEGIN -- get field mappings
104 g_debug := hr_utility.debug_enabled;
105
106 if g_debug then
107 l_proc := g_package||'.get_field_mappings';
108 hr_utility.set_location('Entering '||l_proc, 10);
109 end if;
110
111 OPEN csr_get_mappings;
112 FETCH csr_get_mappings INTO l_mapping_record;
113 IF csr_get_mappings%NOTFOUND
114 THEN
115 fnd_message.set_name('HXC', 'HXC_0016_GNRET_NO_MAPPINGS');
116 fnd_message.raise_error;
117 CLOSE csr_get_mappings;
118 END IF;
119
120 LOOP
121
122 --if g_debug then
123 -- hr_utility.set_location('Processing '||l_proc, 20);
124 --end if;
125
126 l_table_index := l_table_index + 1;
127 l_mappings_table ( l_table_index ) := l_mapping_record;
128
129 FETCH csr_get_mappings INTO l_mapping_record;
130
131 EXIT WHEN csr_get_mappings%NOTFOUND;
132
133 END LOOP;
134
135 CLOSE csr_get_mappings;
136
137 if g_debug then
138 hr_utility.set_location('Leaving '||l_proc, 30);
139 end if;
140
141 RETURN l_mappings_table;
142
143 END get_field_mappings;
144
145
146 FUNCTION get_day_date ( p_type VARCHAR2, p_bb_id NUMBER, p_bb_ovn NUMBER ) RETURN DATE IS
147
148 CURSOR csr_get_start IS
149 SELECT start_time
150 FROM hxc_time_building_blocks bb
151 WHERE bb.time_building_block_id = p_bb_id
152 AND bb.object_version_number = p_bb_ovn;
153
154 CURSOR csr_get_stop IS
155 SELECT stop_time
156 FROM hxc_time_building_blocks bb
157 WHERE bb.time_building_block_id = p_bb_id
158 AND bb.object_version_number = p_bb_ovn;
159
160 l_date DATE;
161
162 BEGIN
163
164 IF ( p_type = 'START' )
165 THEN
166
167 OPEN csr_get_start;
168 FETCH csr_get_start INTO l_date;
169 CLOSE csr_get_start;
170
171 ELSE
172
173 OPEN csr_get_stop;
174 FETCH csr_get_stop INTO l_date;
175 CLOSE csr_get_stop;
176
177 END IF;
178
179 RETURN l_date;
180
181 END get_day_date;
182
183 -- private function
184 -- compare_attributes
185 -- description
186 -- function which returns TRUE if any attribute has changed between
187 -- the old and new tables of attributes
188
189
190 FUNCTION compare_attributes ( p_new_attributes_table hxc_self_service_time_deposit.building_block_attribute_info
191 , p_old_attributes_table hxc_self_service_time_deposit.building_block_attribute_info
192 , p_mappings_tab hxc_generic_retrieval_pkg.t_field_mappings )
193 RETURN BOOLEAN IS
194
195 l_changed BOOLEAN := FALSE;
196 l_attribute_row_found BOOLEAN := FALSE;
197
198 l_old_index BINARY_INTEGER;
199 l_new_index BINARY_INTEGER;
200
201 l_proc VARCHAR2(72);
202
203 -- private function
204 -- in_mapping
205 -- description
206 -- retruns true if the attribute is in the mapping
207
208 FUNCTION in_mapping ( p_bld_blk_info_type_id NUMBER
209 , p_mappings_tab hxc_generic_retrieval_pkg.t_field_mappings
210 , p_attribute VARCHAR2 )
211 RETURN BOOLEAN IS
212
213 l_return BOOLEAN := FALSE;
214
215 BEGIN
216
217 FOR map IN p_mappings_tab.FIRST .. p_mappings_tab.LAST
218 LOOP
219
220 IF ( p_mappings_tab(map).bld_blk_info_type_id = p_bld_blk_info_type_id )
221 THEN
222 IF ( p_mappings_tab(map).attribute = p_attribute )
223 THEN
224 l_return := TRUE;
225 EXIT;
226 END IF;
227 END IF;
228
229 END LOOP;
230
231 RETURN l_return;
232
233 END in_mapping;
234
235 BEGIN -- compare_attributes
236
237
238 if g_debug then
239 l_proc := g_package||'.compare_attributes';
240 hr_utility.set_location('Processing '||l_proc, 10);
241 end if;
242
243 IF ( p_old_attributes_table.COUNT = 0 AND p_new_attributes_table.COUNT = 0 )
244 THEN
245
246 l_changed := FALSE;
247
248 ELSIF ( p_old_attributes_table.COUNT = 0 )
249 THEN
250 if g_debug then
251 hr_utility.set_location('Processing '||l_proc, 20);
252 hr_utility.trace('No old attributes');
253 end if;
254
255 -- there were no prior time attributes to compare against
256 -- then for all intents and purposes the mapping has changed.
257
258 l_changed := TRUE;
259
260 ELSIF ( p_new_attributes_table.COUNT = 0 )
261 THEN
262 if g_debug then
263 hr_utility.set_location('Processing '||l_proc, 25);
264 hr_utility.trace('No new attributes');
265 end if;
266
267 l_changed := TRUE;
268
269 ELSE
270
271 if g_debug then
272 hr_utility.set_location('Processing '||l_proc, 30);
273 end if;
274
275 -- count the number of new and old bld blks.
276 -- if these numbers are different then changed
277
278 IF ( p_new_attributes_table.COUNT <> p_old_attributes_table.COUNT )
279 THEN
280
281 if g_debug then
282 hr_utility.trace('*******************************');
283 hr_utility.trace('number of info types different ');
284 hr_utility.trace('*******************************');
285 end if;
286
287 l_changed := TRUE;
288
289 ELSE
290 if g_debug then
291 hr_utility.set_location('Processing '||l_proc, 100);
292 end if;
293
294 -- check to see if even though we have the same number that there are
295 -- no new bld blk info types or that any have been removed.
296
297 if g_debug then
298 hr_utility.set_location('Processing '||l_proc, 30);
299 end if;
300
301 -- check to see if any new new
302
303 l_new_index := p_new_attributes_table.FIRST;
304
305 WHILE ( l_new_index IS NOT NULL AND NOT l_changed )
306 LOOP
307
308 IF NOT ( p_old_attributes_table.EXISTS(l_new_index) )
309 THEN
310 l_changed := TRUE;
311 END IF;
312
313 l_new_index := p_new_attributes_table.NEXT(l_new_index);
314
315 END LOOP;
316
317 -- check to see if any new old
318
319 l_old_index := p_old_attributes_table.FIRST;
320
321 WHILE ( l_old_index IS NOT NULL AND NOT l_changed )
322 LOOP
323
324 IF NOT ( p_new_attributes_table.EXISTS(l_old_index) )
325 THEN
326 l_changed := TRUE;
327 END IF;
328
329 l_old_index := p_old_attributes_table.NEXT(l_old_index);
330
331 END LOOP;
332
333 END IF; --( p_new_attributes_table.COUNT <> p_old_attributes_table.COUNT )
334
335 IF ( NOT l_changed )
336 THEN
337
338 -- compare the attributes directly
339
340 l_new_index := p_new_attributes_table.FIRST;
341
342 WHILE ( l_new_index IS NOT NULL )
343 LOOP
344 if g_debug then
345 hr_utility.set_location('Processing '||l_proc, 40);
346 end if;
347
348 IF ( ( NVL( p_old_attributes_table(l_new_index).attribute1 , 'zZz' )
349 <> NVL( p_new_attributes_table(l_new_index).attribute1 , 'zZz' ) )
350 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE1' ) ) )
351 THEN
352 if g_debug then
353 hr_utility.set_location('Processing '||l_proc, 80);
354 end if;
355
356 l_changed := TRUE;
357 EXIT;
358
359 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute2 , 'zZz' )
360 <> NVL( p_new_attributes_table(l_new_index).attribute2 , 'zZz' ) )
361 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE2' ) ) )
362 THEN
363
364 l_changed := TRUE;
365 EXIT;
366
367 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute3 , 'zZz' )
368 <> NVL( p_new_attributes_table(l_new_index).attribute3 , 'zZz' ) )
369 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE3' ) ) )
370 THEN
371
372 l_changed := TRUE;
373 EXIT;
374
375 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute4 , 'zZz' )
376 <> NVL( p_new_attributes_table(l_new_index).attribute4 , 'zZz' ) )
377 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE4' ) ) )
378 THEN
379
380 l_changed := TRUE;
381 EXIT;
382
383 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute5 , 'zZz' )
384 <> NVL( p_new_attributes_table(l_new_index).attribute5 , 'zZz' ) )
385 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE5' ) ) )
386 THEN
387
388 l_changed := TRUE;
389 EXIT;
390
391 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute6 , 'zZz' )
392 <> NVL( p_new_attributes_table(l_new_index).attribute6 , 'zZz' ) )
393 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE6' ) ) )
394 THEN
395
396 l_changed := TRUE;
397 EXIT;
398
399 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute7 , 'zZz' )
400 <> NVL( p_new_attributes_table(l_new_index).attribute7 , 'zZz' ) )
401 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE7' ) ) )
402 THEN
403
404 l_changed := TRUE;
405 EXIT;
406
407 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute8 , 'zZz' )
408 <> NVL( p_new_attributes_table(l_new_index).attribute8 , 'zZz' ) )
409 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE8' ) ) )
410 THEN
411
412 l_changed := TRUE;
413 EXIT;
414
415 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute9 , 'zZz' )
416 <> NVL( p_new_attributes_table(l_new_index).attribute9 , 'zZz' ) )
417 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE9' ) ) )
418 THEN
419
420 l_changed := TRUE;
421 EXIT;
422
423 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute10 , 'zZz' )
424 <> NVL( p_new_attributes_table(l_new_index).attribute10 , 'zZz' ) )
425 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE10' ) ) )
426 THEN
427
428 l_changed := TRUE;
429 EXIT;
430
431 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute11 , 'zZz' )
432 <> NVL( p_new_attributes_table(l_new_index).attribute11 , 'zZz' ) )
433 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE11' ) ) )
434 THEN
435
436 l_changed := TRUE;
437 EXIT;
438
439 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute12 , 'zZz' )
440 <> NVL( p_new_attributes_table(l_new_index).attribute12 , 'zZz' ) )
441 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE12' ) ) )
442 THEN
443
444 l_changed := TRUE;
445 EXIT;
446
447 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute13 , 'zZz' )
448 <> NVL( p_new_attributes_table(l_new_index).attribute13 , 'zZz' ) )
449 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE13' ) ) )
450 THEN
451
452 l_changed := TRUE;
453 EXIT;
454
455 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute14 , 'zZz' )
456 <> NVL( p_new_attributes_table(l_new_index).attribute14 , 'zZz' ) )
457 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE14' ) ) )
458 THEN
459
460 l_changed := TRUE;
461 EXIT;
462
463 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute15 , 'zZz' )
464 <> NVL( p_new_attributes_table(l_new_index).attribute15 , 'zZz' ) )
465 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE15' ) ) )
466 THEN
467
468 l_changed := TRUE;
469 EXIT;
470
471 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute16 , 'zZz' )
472 <> NVL( p_new_attributes_table(l_new_index).attribute16 , 'zZz' ) )
473 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE16' ) ) )
474 THEN
475
476 l_changed := TRUE;
477 EXIT;
478
479 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute17 , 'zZz' )
480 <> NVL( p_new_attributes_table(l_new_index).attribute17 , 'zZz' ) )
481 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE17' ) ) )
482 THEN
483
484 l_changed := TRUE;
485 EXIT;
486
487 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute18 , 'zZz' )
488 <> NVL( p_new_attributes_table(l_new_index).attribute18 , 'zZz' ) )
489 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE18' ) ) )
490 THEN
491
492 l_changed := TRUE;
493 EXIT;
494
495 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute19 , 'zZz' )
496 <> NVL( p_new_attributes_table(l_new_index).attribute19, 'zZz' ) )
497 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE19' ) ) )
498 THEN
499
500 l_changed := TRUE;
501 EXIT;
502
503 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute20 , 'zZz' )
504 <> NVL( p_new_attributes_table(l_new_index).attribute20 , 'zZz' ) )
505 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE20' ) ) )
506 THEN
507
508 l_changed := TRUE;
509 EXIT;
510
511 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute21 , 'zZz' )
512 <> NVL( p_new_attributes_table(l_new_index).attribute21 , 'zZz' ) )
513 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE21' ) ) )
514 THEN
515
516 l_changed := TRUE;
517 EXIT;
518
519 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute22 , 'zZz' )
520 <> NVL( p_new_attributes_table(l_new_index).attribute22 , 'zZz' ) )
521 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE22' ) ) )
522 THEN
523
524 l_changed := TRUE;
525 EXIT;
526
527 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute23 , 'zZz' )
528 <> NVL( p_new_attributes_table(l_new_index).attribute23 , 'zZz' ) )
529 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE23' ) ) )
530 THEN
531
532 l_changed := TRUE;
533 EXIT;
534
535 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute24 , 'zZz' )
536 <> NVL( p_new_attributes_table(l_new_index).attribute24 , 'zZz' ) )
537 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE24' ) ) )
538 THEN
539
540 l_changed := TRUE;
541 EXIT;
542
543 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute25 , 'zZz' )
544 <> NVL( p_new_attributes_table(l_new_index).attribute25 , 'zZz' ) )
545 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE25' ) ) )
546 THEN
547
548 l_changed := TRUE;
549 EXIT;
550
551 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute26 , 'zZz' )
552 <> NVL( p_new_attributes_table(l_new_index).attribute26 , 'zZz' ) )
553 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE26' ) ) )
554 THEN
555
556 l_changed := TRUE;
557 EXIT;
558
559 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute27 , 'zZz' )
560 <> NVL( p_new_attributes_table(l_new_index).attribute27 , 'zZz' ) )
561 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE27' ) ) )
562 THEN
563
564 l_changed := TRUE;
565 EXIT;
566
567 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute28 , 'zZz' )
568 <> NVL( p_new_attributes_table(l_new_index).attribute28 , 'zZz' ) )
569 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE28' ) ) )
570 THEN
571
572 l_changed := TRUE;
573 EXIT;
574
575 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute29 , 'zZz' )
576 <> NVL( p_new_attributes_table(l_new_index).attribute29 , 'zZz' ) )
577 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE29' ) ) )
578 THEN
579
580 l_changed := TRUE;
581 EXIT;
582
583 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute30 , 'zZz' )
584 <> NVL( p_new_attributes_table(l_new_index).attribute30 , 'zZz' ) )
585 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE30' ) ) )
586 THEN
587
588 l_changed := TRUE;
589 EXIT;
590
591
592 -- GPM - v115.13
593
594 ELSIF ( ( NVL( p_old_attributes_table(l_new_index).attribute_category , 'zZz' )
595 <> NVL( p_new_attributes_table(l_new_index).attribute_category , 'zZz' ) )
596 AND ( in_mapping ( l_new_index, p_mappings_tab, 'ATTRIBUTE_CATEGORY' ) ) )
597 THEN
598
599 l_changed := TRUE;
600 EXIT;
601
602 END IF;
603
604 if g_debug then
605 hr_utility.set_location('Processing '||l_proc, 90);
606 end if;
607
608
609 IF l_changed
610 THEN
611 if g_debug then
612 hr_utility.set_location('Processing '||l_proc, 140);
613 end if;
614
615 EXIT;
616 END IF;
617
618 if g_debug then
619 hr_utility.set_location('Processing '||l_proc, 160);
620 end if;
621
622 l_new_index := p_new_attributes_table.NEXT(l_new_index);
623
624 END LOOP; -- p_new_atts
625
626 if g_debug then
627 hr_utility.set_location('Processing '||l_proc, 170);
628 end if;
629
630 END IF;
631
632 END IF; -- if l changed
633
634 RETURN l_changed;
635
636 END compare_attributes;
637
638 -- public function
639 -- chk_mapping_changed
640 -- description
641 -- see package specification
642
643 FUNCTION chk_mapping_changed ( p_mapping_id NUMBER
644 , p_timecard_bb_id NUMBER
645 , p_timecard_ovn NUMBER
646 , p_start_date DATE
647 , p_end_date DATE
648 , p_last_status VARCHAR2
649 , p_time_building_blocks hxc_self_service_time_deposit.timecard_info
650 , p_time_attributes hxc_self_service_time_deposit.building_block_attribute_info
651 , p_called_from VARCHAR2 default 'APPROVALS' )
652 RETURN BOOLEAN IS
653
654 -- ***************************************
655 -- Declare mapping changed local variables
656 -- ***************************************
657
658 l_proc VARCHAR2(72);
659
660 t_mapping hxc_generic_retrieval_pkg.t_field_mappings;
661
662 r_new_atts hxc_self_service_time_deposit.attribute_info;
663 r_old_atts hxc_self_service_time_deposit.attribute_info;
664
665 t_old_attributes hxc_self_service_time_deposit.building_block_attribute_info;
666 t_new_attributes hxc_self_service_time_deposit.building_block_attribute_info;
667
668 l_parsed_attributes hxc_self_service_time_deposit.building_block_attribute_info;
669
670 l_mapping_changed BOOLEAN := FALSE;
671 l_no_such_bb_id BOOLEAN := TRUE;
672 l_all_first_ovn BOOLEAN := TRUE;
673
674 l_decision varchar2(10) := 'TEST';
675 l_bld_blk_info_tab t_bld_blk_info;
676
677 l_old_index BINARY_INTEGER :=1;
678 l_new_index BINARY_INTEGER :=1;
679 l_del_index BINARY_INTEGER;
680 l_index
681 BINARY_INTEGER;
682
683 -- ***************************************
684 -- Declare mapping changed local cursors
685 -- ***************************************
686
687 CURSOR csr_get_attributes ( p_bb_id NUMBER, p_bb_ovn NUMBER ) IS
688 SELECT
689 ta.time_attribute_id
690 , bb.time_building_block_id
691 , bbit.bld_blk_info_type
692 , ta.attribute_category
693 , ta.attribute1
694 , ta.attribute2
695 , ta.attribute3
696 , ta.attribute4
697 , ta.attribute5
698 , ta.attribute6
699 , ta.attribute7
700 , ta.attribute8
701 , ta.attribute9
702 , ta.attribute10
703 , ta.attribute11
704 , ta.attribute12
705 , ta.attribute13
706 , ta.attribute14
707 , ta.attribute15
708 , ta.attribute16
709 , ta.attribute17
710 , ta.attribute18
711 , ta.attribute19
712 , ta.attribute20
713 , ta.attribute21
714 , ta.attribute22
715 , ta.attribute23
716 , ta.attribute24
717 , ta.attribute25
718 , ta.attribute26
719 , ta.attribute27
720 , ta.attribute28
721 , ta.attribute29
722 , ta.attribute30
723 , ta.bld_blk_info_type_id
724 , ta.object_version_number
725 , 'X' dummy1
726 , 'X' dummy2
727 , 'X' process
728 FROM
729 hxc_time_building_blocks bb
730 , hxc_time_attributes ta
731 , hxc_time_attribute_usages tau
732 , hxc_bld_blk_info_types bbit
733 WHERE
734 bb.time_building_block_id = p_bb_id AND
735 bb.object_version_number = p_bb_ovn
736 AND
737 tau.time_building_block_id(+) = bb.time_building_block_id AND
738 tau.time_building_block_ovn(+) = bb.object_version_number
739 AND
740 ta.time_attribute_id(+) = tau.time_attribute_id AND
741 ta.object_version_number(+) = 1 -- gaz
742 AND
743 bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
744 ORDER BY
745 ta.bld_blk_info_type_id;
746
747 -- *********************************************************
748 -- private function
749 -- get_old_atts
750 --
751 -- description
752 -- gets all the attributes for a prior time building block
753 -- with a given status
754 -- returns a table of attributes - if no attributes then the
755 -- table is empty
756
757 FUNCTION get_old_atts ( p_bb_id NUMBER
758 , p_bb_ovn NUMBER
759 , p_status VARCHAR2
760 , p_bld_blk_info_tab t_bld_blk_info )
761 RETURN hxc_self_service_time_deposit.building_block_attribute_info IS
762
763 l_proc VARCHAR2(72);
764
765 l_decr_ovn hxc_time_building_blocks.object_version_number%TYPE := 0;
766 l_attributes_exist BOOLEAN := TRUE;
767 l_old BINARY_INTEGER := 1;
768 r_old_atts hxc_self_service_time_deposit.attribute_info;
769 t_old_atts hxc_self_service_time_deposit.building_block_attribute_info;
770
771
772 CURSOR csr_get_old_atts ( p_decr_ovn NUMBER ) IS
773 SELECT
774 ta.time_attribute_id
775 , bb.time_building_block_id
776 , bbit.bld_blk_info_type
777 , ta.attribute_category
778 , ta.attribute1
779 , ta.attribute2
780 , ta.attribute3
781 , ta.attribute4
782 , ta.attribute5
783 , ta.attribute6
784 , ta.attribute7
785 , ta.attribute8
786 , ta.attribute9
787 , ta.attribute10
788 , ta.attribute11
789 , ta.attribute12
790 , ta.attribute13
791 , ta.attribute14
792 , ta.attribute15
793 , ta.attribute16
794 , ta.attribute17
795 , ta.attribute18
796 , ta.attribute19
797 , ta.attribute20
798 , ta.attribute21
799 , ta.attribute22
800 , ta.attribute23
801 , ta.attribute24
802 , ta.attribute25
803 , ta.attribute26
804 , ta.attribute27
805 , ta.attribute28
806 , ta.attribute29
807 , ta.attribute30
808 , ta.bld_blk_info_type_id
809 , ta.object_version_number
810 , 'X'
811 , 'X'
812 , 'X'
813 FROM
814 hxc_time_attributes ta
815 , hxc_time_attribute_usages tau
816 , hxc_time_building_blocks bb
817 , hxc_bld_blk_info_types bbit
818 WHERE
819 bb.time_building_block_id = p_bb_id AND
820 bb.object_version_number = p_decr_ovn AND
821 bb.approval_status = p_status
822 AND
823 tau.time_building_block_id(+) = bb.time_building_block_id AND
824 tau.time_building_block_ovn(+) = bb.object_Version_number
825 AND
826 ta.time_attribute_id(+) = tau.time_attribute_id AND
827 ta.object_version_number(+) = 1 -- gaz
828 AND
829 bbit.bld_blk_info_type_id = ta.bld_blk_info_type_id
830 ORDER BY
831 ta.bld_blk_info_type_id;
832
833 BEGIN -- get_old_atts
834
835
836
837 if g_debug then
838 l_proc := g_package||'.get_old_attributes';
839 hr_utility.trace('************* In get old atts ***************');
840 hr_utility.trace('bb id is '||to_char(p_bb_id));
841 hr_utility.trace('bb ovn is '||to_char(p_bb_ovn));
842
843 hr_utility.set_location('Processing '||l_proc, 10);
844 end if;
845
846 -- remember that the smallest p_bb_ovn is going to be 2
847 -- otherwise this function is never called.
848
849 l_decr_ovn := p_bb_ovn;
850
851 l_decr_ovn := l_decr_ovn - 1;
852
853 -- iteratively open the cursor while decrementing the ovn
854 -- and looking for given status until rows are returned
855
856 OPEN csr_get_old_atts ( p_decr_ovn => l_decr_ovn );
857 FETCH csr_get_old_atts INTO r_old_atts;
858
859 WHILE ( csr_get_old_atts%NOTFOUND )
860 LOOP
861 if g_debug then
862 hr_utility.set_location('Processing '||l_proc, 20);
863 end if;
864
865 CLOSE csr_get_old_atts;
866 l_decr_ovn := l_decr_ovn - 1;
867
868 IF ( l_decr_ovn <> 0 )
869 THEN
870 if g_debug then
871 hr_utility.set_location('Processing '||l_proc, 30);
872 hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
873 end if;
874
875 OPEN csr_get_old_atts ( p_decr_ovn => l_decr_ovn );
876 FETCH csr_get_old_atts INTO r_old_atts;
877
878 ELSE
879 if g_debug then
880 hr_utility.set_location('Processing '||l_proc, 40);
881 end if;
882
883 -- if we reach here l_decr ovn is 0 and
884 -- so we have no old attributes
885
886 l_attributes_exist := FALSE;
887 EXIT;
888
889 END IF;
890
891 if g_debug then
892 hr_utility.set_location('Processing '||l_proc, 50);
893 end if;
894
895 END LOOP;
896
897 IF l_attributes_exist
898 THEN
899 if g_debug then
900 hr_utility.set_location('Processing '||l_proc, 60);
901 end if;
902
903 -- lets continue to get those attributes
904
905 WHILE csr_get_old_atts%FOUND
906 LOOP
907 if g_debug then
908 hr_utility.set_location('Processing '||l_proc, 70);
909 end if;
910
911 -- before we write it to the table check that it is
912 -- in the mapping
913
914 IF ( p_bld_blk_info_tab.EXISTS(r_old_atts.bld_blk_info_type_id) )
915 THEN
916
917 t_old_atts(r_old_atts.bld_blk_info_type_id) := r_old_atts;
918
919 END IF;
920
921 FETCH csr_get_old_atts INTO r_old_atts;
922
923 END LOOP;
924
925 if g_debug then
926 hr_utility.set_location('Processing '||l_proc, 80);
927 end if;
928
929 CLOSE csr_get_old_atts;
930
931 END IF;
932
933 if g_debug then
934 hr_utility.set_location('Processing '||l_proc, 90);
935 end if;
936
937 RETURN t_old_atts;
938
939 END get_old_atts;
940
941
942 -- private procedure
943 -- chk_att_tco_not_changed
944 -- description
945 -- creates a table of distinct bld blk info type ids which have changed
946 -- and are in the mapping
947 -- Also returns table (l_time_attributes) indexed by time_attribute
948 -- _id with the rows which need to be tested.
949 --
950 -- checks to see if the NEW flag is Y, if so, then if it is
951 -- for a bld blk info type which is in the mapping, returns
952 -- CHANGED. if not then deletes from table
953 --
954 -- IF NEW flag is N then checks changed flag, if Y
955 -- then checks that bld blk type in mapping, if so
956 -- then returns TEST, if not deletes from table
957 --
958 -- IF NEW flag is N and CHANGED flag is N, then
959 -- deletes from table
960 --
961 -- IF NEW flags are all N and changed flags N for bld blks
962 -- info types in mapping RETURN NOTCHANGED
963
964 PROCEDURE chk_att_tco_not_changed ( p_time_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
965 , p_mappings_tab hxc_generic_retrieval_pkg.t_field_mappings
966 , p_decision IN OUT NOCOPY VARCHAR2
967 , p_bld_blk_info_tab IN OUT NOCOPY t_bld_blk_info ) IS
968
969 l_changed VARCHAR2(10) := 'NOTCHANGED';
970 l_att_index BINARY_INTEGER;
971 l_map_index BINARY_INTEGER;
972 l_del_index BINARY_INTEGER;
973
974 l_time_attributes hxc_self_service_time_deposit.building_block_attribute_info;
975
976 l_proc VARCHAR2(72);
977
978 BEGIN
979
980
981 if g_debug then
982 l_proc := g_package||'.chk_att_tco_not_changed';
983 hr_utility.set_location('Processing '||l_proc, 10);
984 end if;
985
986 -- build table of mapping bld blk info type ids
987
988 l_map_index := p_mappings_tab.FIRST;
989
990 WHILE ( l_map_index IS NOT NULL )
991 LOOP
992
993 IF NOT ( p_bld_blk_info_tab.EXISTS(p_mappings_tab(l_map_index).bld_blk_info_type_id) )
994 THEN
995 p_bld_blk_info_tab(p_mappings_tab(l_map_index).bld_blk_info_type_id).bld_blk_info_type_id
996 := p_mappings_tab(l_map_index).bld_blk_info_type_id;
997
998 END IF;
999
1000 l_map_index := p_mappings_tab.NEXT(l_map_index);
1001
1002 END LOOP;
1003
1004 if g_debug then
1005 hr_utility.set_location('Processing '||l_proc, 20);
1006 end if;
1007
1008 -- kluge since APPROVALS does not pass tco anymore
1009 -- thus if attributes 0 called from approvals
1010
1011 l_att_index := p_time_attributes.COUNT;
1012
1013 IF ( l_att_index = 0 )
1014 THEN
1015 l_changed := 'TEST';
1016 END IF;
1017
1018 l_att_index := p_time_attributes.FIRST;
1019
1020 WHILE ( l_att_index IS NOT NULL )
1021 LOOP
1022 if g_debug then
1023 hr_utility.set_location('Processing '||l_proc, 30);
1024 end if;
1025
1026 IF ( p_time_attributes(l_att_index).new = 'Y' )
1027 THEN
1028 if g_debug then
1029 hr_utility.set_location('Processing '||l_proc, 40);
1030 end if;
1031
1032 -- check to see if this attribute category is in the mapping
1033
1034 IF ( p_bld_blk_info_tab.EXISTS(p_time_attributes(l_att_index).bld_blk_info_type_id) )
1035 THEN
1036 l_changed := 'CHANGED';
1037 EXIT;
1038 ELSE
1039 l_del_index := l_att_index;
1040 l_att_index := p_time_attributes.NEXT(l_att_index);
1041 p_time_attributes.DELETE(l_del_index);
1042
1043 END IF;
1044
1045 ELSIF ( p_time_attributes(l_att_index).changed = 'Y' )
1046 THEN
1047 if g_debug then
1048 hr_utility.set_location('Processing '||l_proc, 50);
1049 end if;
1050
1051 -- check to see if this attribute category is in the mapping
1052
1053 IF ( p_bld_blk_info_tab.EXISTS(p_time_attributes(l_att_index).bld_blk_info_type_id) )
1054 THEN
1055 l_changed := 'TEST';
1056
1057 -- maintain new parsed time attribute table with time attribute id index
1058
1059 l_time_attributes(p_time_attributes(l_att_index).time_attribute_id)
1060 := p_time_attributes(l_att_index);
1061
1062 l_att_index := p_time_attributes.NEXT(l_att_index);
1063
1064 ELSE
1065 l_del_index := l_att_index;
1066 l_att_index := p_time_attributes.NEXT(l_att_index);
1067 p_time_attributes.DELETE(l_del_index);
1068 END IF;
1069
1070 ELSE
1071 if g_debug then
1072 hr_utility.set_location('Processing '||l_proc, 60);
1073 end if;
1074
1075 l_del_index := l_att_index;
1076 l_att_index := p_time_attributes.NEXT(l_att_index);
1077 p_time_attributes.DELETE(l_del_index);
1078
1079 END IF;
1080
1081 if g_debug then
1082 hr_utility.set_location('Processing '||l_proc, 70);
1083 end if;
1084
1085 END LOOP;
1086
1087 if g_debug then
1088 hr_utility.set_location('Processing '||l_proc, 80);
1089 end if;
1090
1091 p_time_attributes.DELETE;
1092
1093 p_time_attributes := l_time_attributes;
1094
1095 p_decision := l_changed;
1096
1097 END chk_att_tco_not_changed;
1098
1099
1100 BEGIN -- chk_mapping_changed
1101
1102 g_debug := hr_utility.debug_enabled;
1103
1104 if g_debug then
1105 l_proc := g_package||'.chk_mapping_changed';
1106 hr_utility.set_location('Processing '||l_proc, 10);
1107 end if;
1108
1109 -- first of all populate the mapping table
1110
1111 t_mapping := get_field_mappings ( p_mapping_id );
1112
1113 -- regardless of where this is called from
1114 -- we should check the TCO to see if anything has changed
1115 -- which is relevant to the mapping
1116
1117 l_parsed_attributes := p_time_attributes;
1118
1119 if g_debug then
1120 hr_utility.set_location('Processing '||l_proc, 20);
1121 end if;
1122
1123 chk_att_tco_not_changed ( p_time_attributes => l_parsed_attributes
1124 , p_mappings_tab => t_mapping
1125 , p_decision => l_decision
1126 , p_bld_blk_info_tab=> l_bld_blk_info_tab );
1127
1128 if g_debug then
1129 hr_utility.set_location('Processing '||l_proc, 30);
1130 end if;
1131
1132 IF ( l_decision = 'NOTCHANGED' )
1133 THEN
1134 if g_debug then
1135 hr_utility.set_location('Processing '||l_proc, 40);
1136 end if;
1137
1138 l_mapping_changed := FALSE;
1139
1140 ELSIF ( l_decision = 'CHANGED' )
1141 THEN
1142 if g_debug then
1143 hr_utility.set_location('Processing '||l_proc, 50);
1144 end if;
1145
1146 l_mapping_changed := TRUE;
1147
1148 ELSE
1149
1150 IF ( p_called_from = 'TIME_ENTRY' )
1151 THEN
1152 if g_debug then
1153 hr_utility.set_location('Processing '||l_proc, 60);
1154 end if;
1155
1156 -- row has not been committed to the database
1157 -- TCO bb id and ovn is of the current row on the
1158 -- database but the values are the new values.
1159
1160 -- now loop through the building blocks one at a time
1161 -- and compare the current and new attribute tables
1162
1163 if g_debug then
1164 hr_utility.trace('************* TIME_ENTRY ***************');
1165 end if;
1166
1167 FOR tc IN csr_get_timecard (
1168 p_timecard_bb_id => p_timecard_bb_id
1169 , p_timecard_ovn => p_timecard_ovn
1170 , p_start_date => p_start_date
1171 , p_end_date => p_end_date )
1172 LOOP
1173
1174 l_no_such_bb_id := FALSE;
1175
1176 if g_debug then
1177 hr_utility.trace('************* Timecard Loop ***************');
1178 hr_utility.trace('bb id is '||to_char(tc.bb_id));
1179 hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1180 end if;
1181
1182
1183 FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1184 LOOP
1185
1186 if g_debug then
1187 hr_utility.trace('************* Attribute Loop ***************');
1188 hr_utility.trace('bb id is '||to_char(tc.bb_id));
1189 hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1190 hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1191
1192 hr_utility.set_location('Processing '||l_proc, 70);
1193 end if;
1194
1195 -- Maintain old attributes record and table
1196
1197 r_old_atts := bb;
1198
1199 IF ( l_parsed_attributes.EXISTS(r_old_atts.time_attribute_id) )
1200 THEN
1201
1202 -- this is called from TIME_ENTRY and thus
1203 -- these attributes are the old values
1204 -- since nothing has been committed yet
1205 -- and the values used to get this data
1206 -- are the old bb_id and bb_ovn
1207
1208 t_old_attributes(r_old_atts.bld_blk_info_type_id):= r_old_atts;
1209
1210 t_new_attributes(r_old_atts.bld_blk_info_type_id):=
1211 l_parsed_attributes(r_old_atts.time_attribute_id);
1212
1213 END IF;
1214
1215 END LOOP; -- csr_get_attributes
1216
1217 -- compare old and new values.
1218 -- if values are different return true
1219
1220 -- remember we delete from l_parsed_attributes (the table used to populate t_new_attributes)
1221 -- in chk_att_tco changed if the attribute row has not been changed
1222 -- thus when we select the old value from the database this does not necessarily
1223 -- need to be compared, we must first check that the new attributes table is not ZERO
1224
1225 IF ( compare_attributes ( p_new_attributes_table => t_new_attributes
1226 , p_old_attributes_table => t_old_attributes
1227 , p_mappings_tab => t_mapping ) )
1228 THEN
1229 if g_debug then
1230 hr_utility.set_location('Processing '||l_proc, 90);
1231 end if;
1232
1233 l_mapping_changed := TRUE;
1234 EXIT;
1235 END IF;
1236
1237 if g_debug then
1238 hr_utility.set_location('Processing '||l_proc, 100);
1239 end if;
1240
1241 t_old_attributes.delete;
1242 t_new_attributes.delete;
1243 l_new_index := 1;
1244 l_old_index := 1;
1245
1246 END LOOP; -- csr_get_timecard
1247
1248 -- raise an error if the bb_id, ovn and dates returned no rows
1249
1250 IF ( l_no_such_bb_id )
1251 THEN
1252 fnd_message.set_name('HXC', 'HXC_0013_GNRET_NO_BLD_BLKS');
1253 fnd_message.raise_error;
1254 END IF;
1255
1256 if g_debug then
1257 hr_utility.set_location('Processing '||l_proc, 110);
1258 end if;
1259
1260
1261 ELSE -- p_called_from = 'APPROVALS'
1262
1263 if g_debug then
1264 hr_utility.set_location('Processing '||l_proc, 120);
1265 end if;
1266
1267 -- now loop through the building blocks one at a time
1268 -- and compare the current and new attribute tables
1269
1270 FOR tc IN csr_get_timecard (
1271 p_timecard_bb_id => p_timecard_bb_id
1272 , p_timecard_ovn => p_timecard_ovn
1273 , p_start_date => p_start_date
1274 , p_end_date => p_end_date )
1275 LOOP
1276
1277 l_no_such_bb_id := FALSE;
1278
1279 -- make sure that this is not the first bb
1280 -- otherwise there cannot be any history
1281
1282 IF ( tc.bb_ovn <> 1 )
1283 THEN
1284
1285 if g_debug then
1286 hr_utility.trace('************* Timecard Loop ***************');
1287 hr_utility.trace('bb id is '||to_char(tc.bb_id));
1288 hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1289 end if;
1290
1291
1292 FOR bb IN csr_get_attributes ( p_bb_id => tc.bb_id, p_bb_ovn => tc.bb_ovn )
1293 LOOP
1294
1295 if g_debug then
1296 hr_utility.trace('************* Attribute Loop ***************');
1297 hr_utility.trace('bb id is '||to_char(tc.bb_id));
1298 hr_utility.trace('bb ovn is '||to_char(tc.bb_ovn));
1299 hr_utility.trace('bld blk info type '||to_char(bb.bld_blk_info_type_id));
1300 end if;
1301
1302 -- flag to be able to set changed flag if all the bld blks in the hierarchy
1303 -- have ovn = 1
1304
1305 l_all_first_ovn := FALSE;
1306
1307 if g_debug then
1308 hr_utility.set_location('Processing '||l_proc, 130);
1309 end if;
1310
1311 -- Maintain new attributes record and table
1312
1313 r_new_atts := bb;
1314
1315 IF ( l_bld_blk_info_tab.EXISTS(r_new_atts.bld_blk_info_type_id) )
1316 THEN
1317
1318 t_new_attributes(r_new_atts.bld_blk_info_type_id):= r_new_atts;
1319
1320 END IF;
1321
1322 END LOOP; -- csr_get_attributes
1323
1324 if g_debug then
1325 hr_utility.set_location('Processing '||l_proc, 140);
1326 end if;
1327
1328 -- populate old attributes
1329
1330 t_old_attributes := get_old_atts (
1331 p_bb_id => tc.bb_id
1332 , p_bb_ovn => tc.bb_ovn
1333 , p_status => p_last_status
1334 , p_bld_blk_info_tab => l_bld_blk_info_tab );
1335
1336 -- compare old and new values.
1337 -- if values are different return true
1338
1339 /*
1340 if g_debug then
1341 hr_utility.trace('');
1342 hr_utility.trace('****** old table is ********');
1343 end if;
1344
1345 l_new_index := t_old_attributes.FIRST;
1346
1347 WHILE ( l_new_index IS NOT NULL )
1348 LOOP
1349
1350 if g_debug then
1351 hr_utility.trace('');
1352 hr_utility.trace('bb id '||to_char(t_old_attributes(l_new_index).building_block_id));
1353 hr_utility.trace('bld blk id '||to_char(t_old_attributes(l_new_index).bld_blk_info_type_id));
1354 hr_utility.trace('attribute category '||t_old_attributes(l_new_index).attribute_category);
1355 hr_utility.trace('attribute1 '||t_old_attributes(l_new_index).attribute1);
1356 hr_utility.trace('attribute2 '||t_old_attributes(l_new_index).attribute2);
1357 hr_utility.trace('attribute3 '||t_old_attributes(l_new_index).attribute3);
1358 hr_utility.trace('attribute4 '||t_old_attributes(l_new_index).attribute4);
1359 hr_utility.trace('attribute5 '||t_old_attributes(l_new_index).attribute5);
1360 hr_utility.trace('new '||t_old_attributes(l_new_index).new);
1361 hr_utility.trace('changed '||t_old_attributes(l_new_index).changed);
1362 hr_utility.trace('');
1363 end if;
1364
1365 l_new_index := t_old_attributes.NEXT(l_new_index);
1366
1367 END LOOP;
1368
1369 if g_debug then
1370 hr_utility.trace('');
1371 hr_utility.trace('****** new table is ********');
1372 end if;
1373
1374 l_new_index := t_new_attributes.FIRST;
1375
1376 WHILE ( l_new_index IS NOT NULL )
1377 LOOP
1378
1379 if g_debug then
1380 hr_utility.trace('');
1381 hr_utility.trace('bb id '||to_char(t_new_attributes(l_new_index).building_block_id));
1382 hr_utility.trace('bld blk id '||to_char(t_new_attributes(l_new_index).bld_blk_info_type_id));
1383 hr_utility.trace('attribute category '||t_new_attributes(l_new_index).attribute_category);
1384 hr_utility.trace('attribute1 '||t_new_attributes(l_new_index).attribute1);
1385 hr_utility.trace('attribute2 '||t_new_attributes(l_new_index).attribute2);
1386 hr_utility.trace('attribute3 '||t_new_attributes(l_new_index).attribute3);
1387 hr_utility.trace('attribute4 '||t_new_attributes(l_new_index).attribute4);
1388 hr_utility.trace('attribute5 '||t_new_attributes(l_new_index).attribute5);
1389 hr_utility.trace('new '||t_new_attributes(l_new_index).new);
1390 hr_utility.trace('changed '||t_new_attributes(l_new_index).changed);
1391 hr_utility.trace('');
1392 end if;
1393
1394 l_new_index := t_new_attributes.NEXT(l_new_index);
1395
1396 END LOOP;
1397
1398 */
1399
1400 IF ( compare_attributes ( p_new_attributes_table => t_new_attributes
1401 , p_old_attributes_table => t_old_attributes
1402 , p_mappings_tab => t_mapping ) )
1403 THEN
1404 if g_debug then
1405 hr_utility.set_location('Processing '||l_proc, 150);
1406 end if;
1407
1408 l_mapping_changed := TRUE;
1409 EXIT;
1410 END IF;
1411
1412 if g_debug then
1413 hr_utility.set_location('Processing '||l_proc, 160);
1414 end if;
1415
1416 t_old_attributes.delete;
1417 t_new_attributes.delete;
1418 l_new_index := 1;
1419 l_old_index := 1;
1420
1421 -- Maintain old bld blk
1422
1423 END IF; -- chk ovn <> 1
1424
1425 if g_debug then
1426 hr_utility.set_location('Processing '||l_proc, 170);
1427 end if;
1428
1429 END LOOP; -- csr_get_timecard
1430
1431 -- raise an error if the bb_id, ovn and dates returned no rows
1432
1433 IF ( l_no_such_bb_id )
1434 THEN
1435 fnd_message.set_name('HXC', 'HXC_0013_GNRET_NO_BLD_BLKS');
1436 fnd_message.raise_error;
1437 END IF;
1438
1439 if g_debug then
1440 hr_utility.set_location('Processing '||l_proc, 180);
1441 end if;
1442
1443 -- chk that all bld blks were not the first bls blks
1444
1445 IF l_all_first_ovn
1446 THEN
1447 l_mapping_changed := TRUE;
1448 END IF;
1449
1450 END IF; -- p_called_from = 'TIME_ENTRY'
1451
1452 END IF; -- l_decision = 'NOTCHANGED'
1453
1454 RETURN l_mapping_changed;
1455
1456 END chk_mapping_changed;
1457
1458
1459 -- public function
1460 -- chk_bld_blk_changed
1461 -- description
1462 -- see package specification
1463
1464 FUNCTION chk_bld_blk_changed ( p_timecard_bb_id NUMBER
1465 , p_timecard_ovn NUMBER
1466 , p_start_date DATE
1467 , p_end_date DATE
1468 , p_last_status VARCHAR2
1469 , p_time_bld_blks hxc_self_service_time_deposit.timecard_info ) RETURN BOOLEAN IS
1470
1471 l_proc VARCHAR2(72);
1472
1473 r_bld_blks csr_get_timecard%rowtype;
1474 r_old_bld_blks csr_get_timecard%rowtype;
1475 r_del_bld_blks csr_get_timecard%rowtype;
1476 r_old_del_bld_blks csr_get_timecard%rowtype;
1477
1478 TYPE t_bld_blk IS TABLE OF csr_get_timecard%ROWTYPE INDEX BY BINARY_INTEGER;
1479
1480 t_bld_blks t_bld_blk;
1481 t_old_bld_blks t_bld_blk;
1482 t_del_bld_blks t_bld_blk;
1483
1484 l_return BOOLEAN := FALSE;
1485
1486 l_bld_blk_cnt BINARY_INTEGER := 0;
1487 l_old_bld_blk_cnt BINARY_INTEGER := 0;
1488
1489 l_index BINARY_INTEGER :=0;
1490
1491 l_decr_ovn NUMBER(9);
1492
1493 CURSOR csr_get_bld_blk ( p_bb_id NUMBER
1494 , p_bb_ovn NUMBER
1495 , p_status VARCHAR2 ) IS
1496 SELECT
1497 bb.time_building_block_id bb_id
1498 , bb.object_Version_number bb_ovn
1499 , bb.scope
1500 , bb.type
1501 , bb.start_time
1502 , bb.stop_time
1503 , bb.measure
1504 , bb.date_to
1505 , bb.comment_text
1506 FROM
1507 hxc_time_building_blocks bb
1508 WHERE
1509 bb.time_building_block_id = p_bb_id AND
1510 bb.object_version_number = p_bb_ovn AND
1511 bb.approval_status = p_status;
1512
1513
1514 BEGIN
1515 g_debug := hr_utility.debug_enabled;
1516
1517 if g_debug then
1518 l_proc := g_package||'.chk_bld_blk_changed';
1519 hr_utility.set_location('Entering '||l_proc, 10);
1520 hr_utility.trace('last status is '||p_last_status);
1521 end if;
1522
1523 -- before we do anything else lets check to see
1524 -- if any of the bld blks are new
1525
1526 l_index := p_time_bld_blks.FIRST;
1527
1528 WHILE ( l_index IS NOT NULL )
1529 LOOP
1530
1531 IF ( p_time_bld_blks(l_index).new = 'Y' )
1532 THEN
1533
1534 l_return := TRUE;
1535 RETURN l_return;
1536
1537 END IF;
1538
1539 l_index := p_time_bld_blks.NEXT(l_index);
1540
1541 END LOOP;
1542
1543 -- loop to populate the current bld blks record
1544
1545 FOR tc IN csr_get_timecard (
1546 p_timecard_bb_id => p_timecard_bb_id
1547 , p_timecard_ovn => p_timecard_ovn
1548 , p_start_date => p_start_date
1549 , p_end_date => p_end_date )
1550 LOOP
1551 if g_debug then
1552 hr_utility.set_location('Processing '||l_proc, 30);
1553 end if;
1554
1555 IF ( tc.bb_ovn <> 1 )
1556 THEN
1557 -- only want to populate current bld blks whose ovn is not 1
1558
1559 l_index := tc.bb_id;
1560
1561 IF ( tc.date_to = hr_general.end_of_time )
1562 THEN
1563 t_bld_blks(l_index) := tc;
1564 ELSE
1565 t_del_bld_blks(l_index) := tc;
1566 END IF;
1567
1568 END IF;
1569
1570 END LOOP; -- csr_get_timecard
1571
1572 if g_debug then
1573 hr_utility.set_location('Processing '||l_proc, 40);
1574 end if;
1575
1576 l_index := t_del_bld_blks.FIRST;
1577
1578 IF ( l_index IS NOT NULL )
1579 THEN
1580 if g_debug then
1581 hr_utility.set_location('Processing '||l_proc, 50);
1582 end if;
1583
1584 l_decr_ovn := t_del_bld_blks(l_index).bb_ovn;
1585
1586 WHILE ( l_index IS NOT NULL AND l_decr_ovn >= 2 )
1587 LOOP
1588 if g_debug then
1589 hr_utility.set_location('Processing '||l_proc, 60);
1590 end if;
1591
1592 -- loop to populate the last bld blk of given status
1593
1594 l_decr_ovn := l_decr_ovn - 1;
1595
1596 OPEN csr_get_bld_blk ( p_bb_id => l_index
1597 , p_bb_ovn => l_decr_ovn
1598 , p_status => p_last_status );
1599 FETCH csr_get_bld_blk INTO r_old_del_bld_blks;
1600
1601 if g_debug then
1602 hr_utility.set_location('Processing '||l_proc, 70);
1603 end if;
1604
1605 WHILE ( csr_get_bld_blk%NOTFOUND )
1606 LOOP
1607 if g_debug then
1608 hr_utility.set_location('Processing '||l_proc, 80);
1609 end if;
1610
1611 l_decr_ovn := l_decr_ovn - 1;
1612
1613 IF ( l_decr_ovn <> 0 )
1614 THEN
1615 if g_debug then
1616 hr_utility.set_location('Processing '||l_proc, 90);
1617 hr_utility.trace('bb id is '||TO_CHAR(l_index));
1618 hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1619 end if;
1620
1621 CLOSE csr_get_bld_blk;
1622
1623 OPEN csr_get_bld_blk (
1624 p_bb_id => l_index
1625 , p_bb_ovn => l_decr_ovn
1626 , p_status => p_last_status );
1627
1628 FETCH csr_get_bld_blk INTO r_old_del_bld_blks;
1629
1630 ELSE
1631 -- l_decr_ovn = 0
1632
1633 EXIT;
1634
1635 END IF;
1636
1637 if g_debug then
1638 hr_utility.set_location('Processing '||l_proc, 100);
1639 end if;
1640
1641 END LOOP; -- csr_get_timecard for old timecard
1642
1643 -- if we found a prior del bld blk check to see if it was deleted
1644 -- if it was deleted or we did not find one then do nothing
1645
1646 IF ( csr_get_bld_blk%FOUND )
1647 THEN
1648 IF ( r_old_del_bld_blks.date_to <> hr_general.end_of_time )
1649 THEN
1650 if g_debug then
1651 hr_utility.set_location('Processing '||l_proc, 110);
1652 end if;
1653
1654 CLOSE csr_get_bld_blk;
1655
1656 l_return := TRUE;
1657 RETURN l_return;
1658
1659 END IF;
1660 END IF;
1661
1662 CLOSE csr_get_bld_blk;
1663
1664 l_index := t_del_bld_blks.NEXT(l_index);
1665
1666 IF ( l_index IS NOT NULL )
1667 THEN
1668 l_decr_ovn := t_del_bld_blks(l_index).bb_ovn;
1669 END IF;
1670
1671 END LOOP; -- WHILE ( l_index IS NOT NULL )
1672
1673 END IF; -- ( l_index IS NOT NULL )
1674
1675 if g_debug then
1676 hr_utility.set_location('Processing '||l_proc, 120);
1677 end if;
1678
1679
1680 -- check non deleted bld blks
1681
1682 l_index := t_bld_blks.FIRST;
1683
1684 IF ( l_index IS NOT NULL )
1685 THEN
1686 if g_debug then
1687 hr_utility.set_location('Processing '||l_proc, 130);
1688 end if;
1689
1690 l_decr_ovn := t_bld_blks(l_index).bb_ovn;
1691
1692 WHILE ( l_index IS NOT NULL AND l_decr_ovn >= 2 )
1693 LOOP
1694 if g_debug then
1695 hr_utility.set_location('Processing '||l_proc, 140);
1696 end if;
1697
1698 -- loop to populate the last bld blk of given status
1699
1700 l_decr_ovn := l_decr_ovn - 1;
1701
1702 OPEN csr_get_bld_blk ( p_bb_id => l_index
1703 , p_bb_ovn => l_decr_ovn
1704 , p_status => p_last_status );
1705 FETCH csr_get_bld_blk INTO r_old_bld_blks;
1706
1707 if g_debug then
1708 hr_utility.set_location('Processing '||l_proc, 150);
1709 end if;
1710
1711 WHILE ( csr_get_bld_blk%NOTFOUND )
1712 LOOP
1713 if g_debug then
1714 hr_utility.set_location('Processing '||l_proc, 160);
1715 end if;
1716
1717 l_decr_ovn := l_decr_ovn - 1;
1718
1719 IF ( l_decr_ovn <> 0 )
1720 THEN
1721
1722 if g_debug then
1723 hr_utility.set_location('Processing '||l_proc, 170);
1724 hr_utility.trace('bb id is '||TO_CHAR(l_index));
1725 hr_utility.trace('object version number is '||TO_CHAR(l_decr_ovn));
1726 end if;
1727
1728 CLOSE csr_get_bld_blk;
1729
1730 OPEN csr_get_bld_blk (
1731 p_bb_id => l_index
1732 , p_bb_ovn => l_decr_ovn
1733 , p_status => p_last_status );
1734
1735 FETCH csr_get_bld_blk INTO r_old_bld_blks;
1736
1737 ELSE
1738 -- l_decr_ovn = 0
1739
1740 EXIT;
1741
1742 END IF;
1743
1744 if g_debug then
1745 hr_utility.set_location('Processing '||l_proc, 180);
1746 end if;
1747
1748 END LOOP; -- csr_get_timecard for old timecard
1749
1750 -- if bld blk of prior status found populate table for comparison
1751 -- otherwise flag change
1752
1753 IF ( csr_get_bld_blk%FOUND )
1754 THEN
1755 t_old_bld_blks(l_index) := r_old_bld_blks;
1756 END IF;
1757
1758 CLOSE csr_get_bld_blk;
1759
1760 l_index := t_bld_blks.NEXT(l_index);
1761
1762 IF ( l_index IS NOT NULL )
1763 THEN
1764 l_decr_ovn := t_bld_blks(l_index).bb_ovn;
1765 END IF;
1766
1767 END LOOP; -- WHILE ( l_index IS NOT NULL )
1768
1769 END IF; -- ( l_index IS NOT NULL )
1770
1771 if g_debug then
1772 hr_utility.set_location('Processing '||l_proc, 200);
1773 end if;
1774
1775 -- if we have reached here then we have two tables of bld blks
1776 -- one for the current timecard and one for the timecard of the prior status
1777 -- it is time to start comparing actual attributes
1778
1779 -- first lets just make sure that the timecard dates have not changed
1780 -- if the timecard bld blk has changed
1781
1782 IF ( t_old_bld_blks.EXISTS(p_timecard_bb_id) )
1783 THEN
1784 if g_debug then
1785 hr_utility.set_location('Processing '||l_proc, 210);
1786 end if;
1787
1788 -- compare old timecard dates with new
1789
1790 IF ( ( t_bld_blks(p_timecard_bb_id).start_time <> t_old_bld_blks(p_timecard_bb_id).start_time )
1791 OR ( t_bld_blks(p_timecard_bb_id).stop_time <> t_old_bld_blks(p_timecard_bb_id).stop_time ) )
1792 THEN
1793 if g_debug then
1794 hr_utility.set_location('Processing '||l_proc, 220);
1795 end if;
1796
1797 l_return := TRUE;
1798 RETURN l_return;
1799 END IF;
1800
1801 END IF; -- t_old_bld_blks.EXISTS(p_timecard_bb_id)
1802
1803 if g_debug then
1804 hr_utility.set_location('Processing '||l_proc, 230);
1805 end if;
1806
1807 -- compare DAY scope start and stop times
1808
1809 l_index := t_old_bld_blks.FIRST;
1810
1811 WHILE ( l_index IS NOT NULL )
1812 LOOP
1813 if g_debug then
1814 hr_utility.set_location('Processing '||l_proc, 240);
1815 end if;
1816
1817 -- GPM v115.22
1818
1819 IF ( NVL(t_bld_blks(l_index).comment_text,'XxX') <> NVL(t_old_bld_blks(l_index).comment_text,'XxX') )
1820 THEN
1821
1822 l_return := TRUE;
1823 RETURN l_return;
1824
1825 END IF;
1826
1827 if g_debug then
1828 hr_utility.set_location('Processing '||l_proc, 245);
1829 end if;
1830
1831 IF ( t_old_bld_blks(l_index).SCOPE = 'DAY' )
1832 THEN
1833 if g_debug then
1834 hr_utility.set_location('Processing '||l_proc, 250);
1835 end if;
1836
1837 -- compare dates
1838
1839 IF ( ( t_bld_blks(l_index).start_time <> t_old_bld_blks(l_index).start_time )
1840 OR ( t_bld_blks(l_index).stop_time <> t_old_bld_blks(l_index).stop_time ) )
1841 THEN
1842 if g_debug then
1843 hr_utility.set_location('Processing '||l_proc, 260);
1844 end if;
1845
1846 l_return := TRUE;
1847 RETURN l_return;
1848 END IF;
1849
1850 ELSIF ( t_old_bld_blks(l_index).SCOPE = 'DETAIL' )
1851 THEN
1852 if g_debug then
1853 hr_utility.set_location('Processing '||l_proc, 270);
1854 end if;
1855
1856 IF ( t_old_bld_blks(l_index).TYPE = 'MEASURE' )
1857 THEN
1858
1859 -- compare measure
1860
1861 IF ( t_bld_blks(l_index).measure <> t_old_bld_blks(l_index).measure )
1862 THEN
1863 if g_debug then
1864 hr_utility.set_location('Processing '||l_proc, 280);
1865 end if;
1866
1867 l_return := TRUE;
1868 RETURN l_return;
1869 END IF;
1870
1871 ELSE
1872
1873 IF ((t_bld_blks(l_index).stop_time - t_bld_blks(l_index).start_time ) <>
1874 (t_old_bld_blks(l_index).stop_time - t_old_bld_blks(l_index).start_time ))
1875 THEN
1876 if g_debug then
1877 hr_utility.set_location('Processing '||l_proc, 290);
1878 end if;
1879
1880 l_return := TRUE;
1881 RETURN l_return;
1882 END IF;
1883
1884
1885 END IF; -- TYPE = 'MEASURE'
1886
1887 END IF;
1888
1889 if g_debug then
1890 hr_utility.set_location('Processing '||l_proc, 300);
1891 end if;
1892
1893 l_index := t_old_bld_blks.NEXT(l_index);
1894
1895 END LOOP;
1896
1897 RETURN l_return;
1898
1899 END chk_bld_blk_changed;
1900
1901 -- function
1902 -- attribute_column
1903 --
1904 -- description
1905 -- returns the name of the attribute column in HXC_TIME_ATTRIBUTES which
1906 -- maps to the parameter p_field_name, based on the building block
1907 -- category and information type
1908 --
1909 -- parameters
1910 -- p_field_name - the name of the field to be mapped
1911 -- p_bld_blk_info_type - the information of the attribute
1912 -- p_descriptive_flexfield_name - the name of the flexfield
1913
1914 function attribute_column
1915 (p_field_name in varchar2
1916 ,p_bld_blk_info_type in varchar2
1917 ,p_descriptive_flexfield_name in varchar2
1918 ) return varchar2 is
1919
1920 cursor c_map is
1921 select hmc.segment
1922 from hxc_mapping_components hmc,
1923 hxc_bld_blk_info_types hit
1924 where hit.descriptive_flexfield_name = p_descriptive_flexfield_name
1925 and hit.bld_blk_info_type_id = hmc.bld_blk_info_type_id
1926 and hit.bld_blk_info_type = p_bld_blk_info_type
1927 and hmc.field_name = p_field_name;
1928
1929 e_no_mapping_exists exception;
1930
1931 l_column_name varchar2(30);
1932
1933 begin
1934
1935 open c_map;
1936 fetch c_map into l_column_name;
1937 if c_map%notfound then
1938 close c_map;
1939 raise e_no_mapping_exists;
1940 else
1941 close c_map;
1942 return l_column_name;
1943 end if;
1944
1945 exception
1946 when e_no_mapping_exists then
1947 -- no mapping has been defined for the specified combination
1948 fnd_message.set_name('HXC', 'HXC_NO_MAPPING_SEGMENT');
1949 fnd_message.raise_error;
1950 when others then
1951 raise;
1952
1953 end attribute_column;
1954
1955
1956 -- function
1957 -- attribute_column
1958 --
1959 -- description
1960 -- overload of attribute_column function. returns the name of the
1961 -- attribute column in HXC_TIME_ATTRIBUTES which maps to the parameter
1962 -- p_field_name, based on the deposit or retrieval process identifier.
1963 -- since there is no guarantee that mappings have been explicitly defined
1964 -- for the given process, the column name is returned in an out parameter,
1965 -- and the function returns true or false depending on whether a mapping
1966 -- was found.
1967 --
1968 -- parameters
1969 -- p_field_name - the name of the field to be mapped
1970 -- p_process_type - (D)eposit or (R)etrieval
1971 -- p_process_id - deposit or retrieval process id
1972 -- p_column_name (out) - the column name where the specified field is
1973 -- stored
1974 -- p_bld_blk_info_type (out) - the information type of the mapped field
1975
1976 function attribute_column
1977 (p_field_name in varchar2
1978 ,p_process_type in varchar2
1979 ,p_process_id in number
1980 ,p_column_name in out nocopy varchar2
1981 ,p_bld_blk_info_type in out nocopy varchar2
1982 ) return boolean is
1983
1984 cursor c_map_ret is
1985 select distinct hmc.segment, hit.bld_blk_info_type
1986 from hxc_mapping_components hmc
1987 , hxc_mapping_comp_usages hmu
1988 , hxc_mappings hmp
1989 , hxc_retrieval_processes hrp
1990 , hxc_bld_blk_info_types hit
1991 where hmu.mapping_id = hmp.mapping_id
1992 and hmp.mapping_id = hrp.mapping_id
1993 and hmc.bld_blk_info_type_id = hit.bld_blk_info_type_id
1994 and hrp.retrieval_process_id = p_process_id
1995 and hmc.mapping_component_id = hmu.mapping_component_id
1996 and hmc.field_name = p_field_name;
1997
1998 cursor c_map_dep is
1999 select distinct hmc.segment, hit.bld_blk_info_type
2000 from hxc_mapping_components hmc
2001 , hxc_mapping_comp_usages hmu
2002 , hxc_mappings hmp
2003 , hxc_deposit_processes hdp
2004 , hxc_bld_blk_info_types hit
2005 where hmu.mapping_id = hmp.mapping_id
2006 and hmp.mapping_id = hdp.mapping_id
2007 and hmc.bld_blk_info_type_id = hit.bld_blk_info_type_id
2008 and hdp.deposit_process_id = p_process_id
2009 and hmc.mapping_component_id = hmu.mapping_component_id
2010 and hmc.field_name = p_field_name;
2011
2012
2013 e_no_distinct_mapping exception;
2014
2015 l_column_name varchar2(30);
2016 l_bld_blk_info_type varchar2(80);
2017
2018 begin
2019
2020 if(p_process_type = 'D') then
2021 open c_map_dep;
2022 fetch c_map_dep into l_column_name, l_bld_blk_info_type;
2023 if c_map_dep%notfound then
2024 close c_map_dep;
2025 return false;
2026 elsif c_map_dep%rowcount > 1 then
2027 close c_map_dep;
2028 raise e_no_distinct_mapping;
2029 else
2030 close c_map_dep;
2031 p_column_name := l_column_name;
2032 p_bld_blk_info_type := l_bld_blk_info_type;
2033 return true;
2034 end if;
2035 elsif(p_process_type = 'R') then
2036 open c_map_ret;
2037 fetch c_map_ret into l_column_name, l_bld_blk_info_type;
2038 if c_map_ret%notfound then
2039 close c_map_ret;
2040 return false;
2041 elsif c_map_ret%rowcount > 1 then
2042 close c_map_ret;
2043 raise e_no_distinct_mapping;
2044 else
2045 close c_map_ret;
2046 p_column_name := l_column_name;
2047 p_bld_blk_info_type := l_bld_blk_info_type;
2048 return true;
2049 end if;
2050 end if;
2051
2052 exception
2053 when e_no_distinct_mapping then
2054 -- more than one mapping has been defined for the specified combination
2055 fnd_message.set_name('HXC', 'HXC_NO_DISTINCT_MAPPING');
2056 fnd_message.raise_error;
2057 when others then
2058 raise;
2059
2060 end attribute_column;
2061
2062
2063 Procedure get_mapping_value(p_bld_blk_info_type in varchar2,
2064 p_field_name in varchar2,
2065 p_segment out nocopy hxc_mapping_components.segment%TYPE,
2066 p_bld_blk_info_type_id out nocopy hxc_mapping_components.bld_blk_info_type_id%TYPE ) is
2067
2068 CURSOR csr_parse_mapping(p_bld_blk_info_type varchar2,p_field_name varchar2) IS
2069 SELECT segment
2070 , bld_blk_info_type_id
2071 FROM hxc_mapping_components_v
2072 WHERE bld_blk_info_type = p_bld_blk_info_type
2073 AND field_name = p_field_name;
2074
2075
2076 l_proc VARCHAR2(72);
2077
2078 begin
2079
2080 g_debug := hr_utility.debug_enabled;
2081
2082 OPEN csr_parse_mapping(p_bld_blk_info_type,p_field_name);
2083
2084 FETCH csr_parse_mapping INTO p_segment, p_bld_blk_info_type_id;
2085
2086 if g_debug then
2087 l_proc := g_package||'.get_mapping_value';
2088 hr_utility.set_location('Processing '||l_proc, 15);
2089 end if;
2090
2091 IF csr_parse_mapping%NOTFOUND
2092 THEN
2093 if g_debug then
2094 hr_utility.set_location('Processing '||l_proc, 20);
2095 end if;
2096
2097 CLOSE csr_parse_mapping;
2098 hr_utility.set_message(809, 'HXC_0026_MPC_TYPE_INVALID');
2099 hr_utility.raise_error;
2100 END IF;
2101
2102 CLOSE csr_parse_mapping;
2103
2104
2105 end get_mapping_value;
2106
2107
2108
2109 FUNCTION chk_mapping_exists ( p_bld_blk_info_type VARCHAR2
2110 , p_field_name VARCHAR2
2111 , p_field_value VARCHAR2
2112 , p_bld_blk_info_type2 VARCHAR2 default null
2113 , p_field_name2 VARCHAR2 default null
2114 , p_field_value2 VARCHAR2 default null
2115 , p_bld_blk_info_type3 VARCHAR2 default null
2116 , p_field_name3 VARCHAR2 default null
2117 , p_field_value3 VARCHAR2 default null
2118 , p_bld_blk_info_type4 VARCHAR2 default null
2119 , p_field_name4 VARCHAR2 default null
2120 , p_field_value4 VARCHAR2 default null
2121 , p_bld_blk_info_type5 VARCHAR2 default null
2122 , p_field_name5 VARCHAR2 default null
2123 , p_field_value5 VARCHAR2 default null
2124 , p_scope VARCHAR2
2125 , p_retrieval_process_name VARCHAR2 DEFAULT 'None'
2126 , p_status VARCHAR2 DEFAULT 'None'
2127 , p_end_date DATE DEFAULT null) RETURN BOOLEAN IS
2128
2129 l_proc VARCHAR2(72);
2130
2131 l_mapping_exists BOOLEAN := FALSE;
2132
2133 l_exists VARCHAR2(1) := 'N';
2134
2135 l_bld_blk_info_type_id hxc_mapping_components.bld_blk_info_type_id%TYPE;
2136 l_bld_blk_info_type_id2 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2137 l_bld_blk_info_type_id3 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2138 l_bld_blk_info_type_id4 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2139 l_bld_blk_info_type_id5 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2140 l_segment1 hxc_mapping_components.segment%TYPE;
2141 l_segment2 hxc_mapping_components.segment%TYPE;
2142 l_segment3 hxc_mapping_components.segment%TYPE;
2143 l_segment4 hxc_mapping_components.segment%TYPE;
2144 l_segment5 hxc_mapping_components.segment%TYPE;
2145
2146 l_bld_block_info_id_outer hxc_mapping_components.bld_blk_info_type_id%TYPE;
2147 l_field_name_outer varchar2(1000);
2148 l_field_value_outer varchar2(1000);
2149 l_segment_outer hxc_mapping_components.segment%TYPE;
2150
2151 l_bld_block_info_id_inner hxc_mapping_components.bld_blk_info_type_id%TYPE;
2152 l_field_name_inner varchar2(1000);
2153 l_field_value_inner varchar2(1000);
2154 l_segment_inner hxc_mapping_components.segment%TYPE;
2155
2156 l_ret_id hxc_retrieval_processes.retrieval_process_id%TYPE;
2157
2158 l_query VARCHAR2(8000);
2159
2160 l_status_list varchar2(400);
2161
2162 l_status varchar2(1000);
2163
2164 l_end_date varchar2(400);
2165
2166 l_field_value varchar2(4000);
2167
2168 l_index number;
2169
2170 l_index_inner number;
2171
2172 l_cons_index number;
2173
2174 t_consolidated_info hxc_mapping_utilities.t_consolidated_info_1;
2175
2176
2177 l_installed varchar2(1) := 'N';
2178
2179 -- Bug 11937354
2180 -- Added this type and variables to hold the index names and the Segment (attributeX) names.
2181 TYPE VARCHARTAB IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
2182 l_segment_name VARCHARTAB;
2183 l_index_name VARCHARTAB;
2184
2185
2186
2187 TYPE MapExistsCur IS REF CURSOR;
2188 map_cr MapExistsCur;
2189
2190 TYPE MapTxfrdCur IS REF CURSOR;
2191 txfrd_cr MapTxfrdCur;
2192
2193 CURSOR csr_chk_otl_installed IS
2194 SELECT 'Y'
2195 FROM fnd_product_installations pi
2196 WHERE pi.application_id = 809
2197 AND pi.status in ( 'S', 'I' );
2198
2199
2200 CURSOR csr_get_ret_id IS
2201 SELECT ret.retrieval_process_id
2202 FROM hxc_retrieval_processes ret
2203 WHERE ret.name = p_retrieval_process_name;
2204
2205 -- Bug 12410558
2206 -- Needed these to fix the regression in the prev fix.
2207 l_counter BINARY_INTEGER;
2208 l_found BOOLEAN;
2209
2210
2211 BEGIN -- chk_mapping_exists
2212
2213 g_debug := hr_utility.debug_enabled;
2214
2215 if g_debug then
2216 l_proc := g_package||'.chk_mapping_exists';
2217 hr_utility.set_location('Processing '||l_proc, 10);
2218 end if;
2219
2220 -- Bug 11937354
2221 -- Initialize the table with the index names -- this will later be used to
2222 -- specify the right hint.
2223
2224 l_index_name(0) := 'HXC_TIME_ATTRIBUTES_FK2';
2225 l_index_name(1) := 'HXC_TIME_ATTRIBUTES_FK3';
2226 l_index_name(2) := 'HXC_TIME_ATTRIBUTES_FK4';
2227 l_index_name(3) := 'HXC_TIME_ATTRIBUTES_FK5';
2228
2229 -- chk to see if OTL is installed
2230
2231 OPEN csr_chk_otl_installed;
2232
2233 FETCH csr_chk_otl_installed INTO l_installed;
2234
2235 IF ( csr_chk_otl_installed%FOUND )
2236 THEN
2237
2238 IF ( p_scope <> 'DETAIL' AND p_status <> 'None' )
2239 THEN
2240
2241 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2242 fnd_message.set_token('PROCEDURE', l_proc);
2243 fnd_message.set_token('STEP','Scope Status combo not supported');
2244 fnd_message.raise_error;
2245
2246 END IF;
2247
2248
2249
2250
2251 if(p_field_name is not null) then
2252
2253 get_mapping_value(p_bld_blk_info_type,p_field_name,l_segment1,l_bld_blk_info_type_id);
2254
2255 end if;
2256
2257 if(p_field_name2 is not null) then
2258
2259 get_mapping_value(p_bld_blk_info_type2,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
2260
2261 end if;
2262
2263 if(p_field_name3 is not null) then
2264
2265 get_mapping_value(p_bld_blk_info_type3,p_field_name3,l_segment3,l_bld_blk_info_type_id3);
2266
2267 end if;
2268
2269
2270 if(p_field_name4 is not null) then
2271
2272 get_mapping_value(p_bld_blk_info_type4,p_field_name4,l_segment4,l_bld_blk_info_type_id4);
2273
2274 end if;
2275
2276
2277 if(p_field_name5 is not null) then
2278
2279 get_mapping_value(p_bld_blk_info_type5,p_field_name5,l_segment5,l_bld_blk_info_type_id5);
2280
2281 end if;
2282
2283
2284
2285
2286
2287 if(p_bld_blk_info_type is not null and p_field_name is not null) then
2288 if(t_consolidated_info.count>0) then
2289 l_cons_index:=t_consolidated_info.count+1;
2290 else
2291 l_cons_index:=1;
2292 end if;
2293
2294 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id;
2295 t_consolidated_info(l_cons_index).field_name :=p_field_name;
2296 t_consolidated_info(l_cons_index).field_value:=p_field_value ;
2297 t_consolidated_info(l_cons_index).segment:=l_segment1;
2298
2299 end if;
2300
2301 if(p_bld_blk_info_type2 is not null and p_field_name2 is not null) then
2302 if(t_consolidated_info.count>0) then
2303 l_cons_index:=t_consolidated_info.count+1;
2304 else
2305 l_cons_index:=1;
2306 end if;
2307
2308 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id2;
2309 t_consolidated_info(l_cons_index).field_name :=p_field_name2;
2310 t_consolidated_info(l_cons_index).field_value:=p_field_value2 ;
2311 t_consolidated_info(l_cons_index).segment:=l_segment2;
2312
2313
2314 end if;
2315
2316 if(p_bld_blk_info_type3 is not null and p_field_name3 is not null) then
2317 if(t_consolidated_info.count>0) then
2318 l_cons_index:=t_consolidated_info.count+1;
2319 else
2320 l_cons_index:=1;
2321 end if;
2322
2323 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id3;
2324 t_consolidated_info(l_cons_index).field_name :=p_field_name3;
2325 t_consolidated_info(l_cons_index).field_value:=p_field_value3 ;
2326 t_consolidated_info(l_cons_index).segment:=l_segment3;
2327
2328 end if;
2329
2330 if(p_bld_blk_info_type4 is not null and p_field_name4 is not null) then
2331 if(t_consolidated_info.count>0) then
2332 l_cons_index:=t_consolidated_info.count+1;
2333 else
2334 l_cons_index:=1;
2335 end if;
2336
2337 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id4;
2338 t_consolidated_info(l_cons_index).field_name :=p_field_name4;
2339 t_consolidated_info(l_cons_index).field_value:=p_field_value4 ;
2340 t_consolidated_info(l_cons_index).segment:=l_segment4;
2341
2342 end if;
2343
2344 if(p_bld_blk_info_type5 is not null and p_field_name5 is not null) then
2345 if(t_consolidated_info.count>0) then
2346 l_cons_index:=t_consolidated_info.count+1;
2347 else
2348 l_cons_index:=1;
2349 end if;
2350
2351 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id5;
2352 t_consolidated_info(l_cons_index).field_name :=p_field_name5;
2353 t_consolidated_info(l_cons_index).field_value:=p_field_value5 ;
2354 t_consolidated_info(l_cons_index).segment:=l_segment5;
2355
2356 end if;
2357
2358
2359 if g_debug then
2360 hr_utility.set_location('Processing '||l_proc, 30);
2361 end if;
2362
2363 -- build dynamic SQL query
2364
2365
2366
2367 -- Bug 11937354
2368 -- Made the following changes here.
2369
2370
2371
2372 --- SELECT 'Y'
2373 --- FROM dual
2374 --- WHERE EXISTS (
2375 --- SELECT 1
2376 --- FROM hxc_latest_details tbb
2377 --- WHERE 1=1 AND (tbb.time_building_block_id,tbb.object_version_number)in (
2378 --- select /*+ LEADING(ta1) INDEX(ta1)
2379 --- INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2380 --- tau1.time_building_block_id,tau1.time_building_block_ovn from
2381 --- hxc_time_attribute_usages tau1,
2382 --- hxc_time_attributes ta1
2383 --- where tau1.time_building_block_id = tbb.time_building_block_id
2384 --- AND tau1.time_building_block_ovn = tbb.object_version_number
2385 --- AND tau1.time_attribute_id = ta1.time_attribute_id
2386 --- AND ta1.bld_blk_info_type_id = 13
2387 --- AND ta1.ATTRIBUTE2 = '10092612')
2388 --- AND exists (
2389 --- select 'Y'
2390 --- from hxc_time_building_blocks detbb,
2391 --- hxc_time_building_blocks daybb,
2392 --- hxc_timecard_summary time_status
2393 --- where tbb.time_building_Block_id = detbb.time_building_block_id
2394 --- and tbb.object_version_number = detbb.object_version_number
2395 --- and detbb.parent_building_block_id = daybb.time_building_block_id
2396 --- and detbb.parent_building_block_ovn = daybb.object_version_number
2397 --- and time_status.timecard_id = daybb.parent_building_block_id
2398 --- and detbb.date_to = hr_general.end_of_time
2399 --- and time_status.approval_status IN ('WORKING','SUBMITTED','APPROVED') ) )
2400 ---
2401 ---
2402 --- Rows Row Source Operation
2403 --- ------- ---------------------------------------------------
2404 --- 0 FILTER (cr=30795566 pr=1088713 pw=25995 time=585061710 us)
2405 --- 0 FAST DUAL (cr=0 pr=0 pw=0 time=0 us)
2406 --- 0 NESTED LOOPS (cr=30795566 pr=1088713 pw=25995 time=585061680 us)
2407 --- 3395698 NESTED LOOPS (cr=20608470 pr=734106 pw=25995 time=428086279 us)
2408 --- 3395698 NESTED LOOPS (cr=10421374 pr=689213 pw=25995 time=353373822 us)
2409 --- 3395698 HASH JOIN (cr=234278 pr=228654 pw=25995 time=176797447 us)
2410 --- 5070998 TABLE ACCESS FULL HXC_TIME_BUILDING_BLOCKS (cr=137452 pr=125820 pw=0 time=76065499 us)
2411 --- 3402919 HASH JOIN (cr=96826 pr=76824 pw=0 time=58232636 us)
2412 --- 1108942 TABLE ACCESS FULL HXC_LATEST_DETAILS (cr=12793 pr=12709 pw=0 time=1109375 us)
2413 --- 7971698 TABLE ACCESS BY INDEX ROWID HXC_TIME_ATTRIBUTE_USAGES (cr=84033 pr=64115 pw=0 time=55803941 us)
2414 --- 7971698 INDEX FULL SCAN HXC_TIME_ATTRIBUTE_USAGES_FK1 (cr=19245 pr=17458 pw=0 time=15977131 us)(object id 75329)
2415 --- 3395698 TABLE ACCESS BY INDEX ROWID HXC_TIME_BUILDING_BLOCKS (cr=10187096 pr=460559 pw=0 time=182819518 us)
2416 --- 3395698 INDEX UNIQUE SCAN HXC_TIME_BUILDING_BLOCKS_PK (cr=6791398 pr=81265 pw=0 time=49309421 us)(object id 249842)
2417 --- 3395698 TABLE ACCESS BY INDEX ROWID HXC_TIMECARD_SUMMARY (cr=10187096 pr=44893 pw=0 time=67496066 us)
2418 --- 3395698 INDEX UNIQUE SCAN HXC_TIMECARD_SUMMARY_PK (cr=6791398 pr=4725 pw=0 time=28993073 us)(object id 211692)
2419 --- 0 TABLE ACCESS BY INDEX ROWID HXC_TIME_ATTRIBUTES (cr=10187096 pr=354607 pw=0 time=151577213 us)
2420 --- 3395698 INDEX UNIQUE SCAN HXC_TIME_ATTRIBUTES_PK (cr=6791398 pr=79962 pw=0 time=49425962 us)(object id 249844)
2421
2422
2423 -- This was the earlier SQL and plan generated.
2424
2425
2426 ---- SELECT 'Y'
2427 ---- FROM dual
2428 ---- WHERE EXISTS (
2429 ---- SELECT /*+ LEADING(ta1)
2430 ---- INDEX(tbb HXC_LATEST_DETAILS_FK) */
2431 ---- 1
2432 ---- FROM hxc_latest_details tbb
2433 ---- WHERE 1=1 AND (tbb.time_building_block_id,tbb.object_version_number)in (
2434 ---- select /*+ UNNEST
2435 ---- USE_NL(ta1 tau1)
2436 ---- INDEX(ta1 HXC_TIME_ATTRIBUTES_FK4)
2437 ---- INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2438 ---- tau1.time_building_block_id,tau1.time_building_block_ovn from
2439 ---- hxc_time_attribute_usages tau1,
2440 ---- hxc_time_attributes ta1
2441 ---- where tau1.time_building_block_id = tbb.time_building_block_id
2442 ---- AND tau1.time_building_block_ovn = tbb.object_version_number
2443 ---- AND tau1.time_attribute_id = ta1.time_attribute_id
2444 ---- AND ta1.bld_blk_info_type_id = 13
2445 ---- AND ta1.ATTRIBUTE2 = '10076616')
2446 ---- AND exists (
2447 ---- select /*+ NO_UNNEST
2448 ---- LEADING(detbb) */
2449 ---- 'Y'
2450 ---- from hxc_time_building_blocks detbb,
2451 ---- hxc_time_building_blocks daybb,
2452 ---- hxc_timecard_summary time_status
2453 ---- where tbb.time_building_Block_id = detbb.time_building_block_id
2454 ---- and tbb.object_version_number = detbb.object_version_number
2455 ---- and detbb.parent_building_block_id = daybb.time_building_block_id
2456 ---- and detbb.parent_building_block_ovn = daybb.object_version_number
2457 ---- and time_status.timecard_id = daybb.parent_building_block_id
2458 ---- and detbb.date_to = hr_general.end_of_time
2459 ---- and time_status.approval_status IN ('WORKING','SUBMITTED','APPROVED') ) )
2460 ---- ;
2461
2462
2463
2464 --- --------------------------------------------------------------------------
2465 --- | Id | Operation | Name |
2466 --- --------------------------------------------------------------------------
2467 --- | 0 | SELECT STATEMENT | |
2468 --- |* 1 | FILTER | |
2469 --- | 2 | FAST DUAL | |
2470 --- |* 3 | FILTER | |
2471 --- |* 4 | HASH JOIN | |
2472 --- | 5 | TABLE ACCESS BY INDEX ROWID | HXC_TIME_ATTRIBUTE_USAGES |
2473 --- | 6 | NESTED LOOPS | |
2474 --- |* 7 | TABLE ACCESS BY INDEX ROWID| HXC_TIME_ATTRIBUTES |
2475 --- |* 8 | INDEX RANGE SCAN | HXC_TIME_ATTRIBUTES_FK4 |
2476 --- |* 9 | INDEX RANGE SCAN | HXC_TIME_ATTRIBUTE_USAGES_FK1 |
2477 --- | 10 | TABLE ACCESS BY INDEX ROWID | HXC_LATEST_DETAILS |
2478 --- | 11 | INDEX RANGE SCAN | HXC_LATEST_DETAILS_FK |
2479 --- | 12 | NESTED LOOPS | |
2480 --- | 13 | NESTED LOOPS | |
2481 --- |* 14 | TABLE ACCESS BY INDEX ROWID | HXC_TIME_BUILDING_BLOCKS |
2482 --- |* 15 | INDEX UNIQUE SCAN | HXC_TIME_BUILDING_BLOCKS_PK |
2483 --- |* 16 | TABLE ACCESS BY INDEX ROWID | HXC_TIME_BUILDING_BLOCKS |
2484 --- |* 17 | INDEX UNIQUE SCAN | HXC_TIME_BUILDING_BLOCKS_PK |
2485 --- |* 18 | TABLE ACCESS BY INDEX ROWID | HXC_TIMECARD_SUMMARY |
2486 --- |* 19 | INDEX UNIQUE SCAN | HXC_TIMECARD_SUMMARY_PK |
2487 --- --------------------------------------------------------------------------
2488
2489
2490 IF ( p_scope <> 'DETAIL' )
2491 THEN
2492
2493 l_query := '
2494 SELECT ''Y''
2495 FROM dual
2496 WHERE EXISTS (
2497 SELECT 1
2498 FROM hxc_time_building_blocks tbb
2499 WHERE tbb.scope = :p_scope AND
2500 tbb.object_version_number = (
2501 SELECT MAX ( tbb1.object_version_number )
2502 FROM hxc_time_building_blocks tbb1
2503 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )';
2504
2505 ELSE
2506
2507 -- p scope must be DETAIL which means we can use the summary table hxc_latest_details
2508
2509 l_query := '
2510 SELECT ''Y''
2511 FROM dual
2512 WHERE EXISTS (
2513 SELECT /*+ INDEX(tbb HXC_LATEST_DETAILS_FK) */
2514 1
2515 FROM hxc_latest_details tbb
2516 WHERE 1=1';
2517
2518 END IF;
2519
2520
2521
2522 l_index:=t_consolidated_info.first;
2523
2524 loop exit when not t_consolidated_info.exists(l_index);
2525 l_segment_name.DELETE;
2526
2527
2528 l_bld_block_info_id_outer:=t_consolidated_info(l_index).bld_blk_info_type_id;
2529 l_field_name_outer:=t_consolidated_info(l_index).field_name;
2530 l_field_value_outer:=t_consolidated_info(l_index).field_value;
2531 l_segment_outer:=t_consolidated_info(l_index).segment;
2532
2533 if(l_field_value_outer is not null) then
2534
2535
2536
2537
2538 l_field_value:= l_field_value||fnd_global.newline||
2539 ' AND (tbb.time_building_block_id,tbb.object_version_number)in (
2540 select /*+ UNNEST
2541 LEADING(ta1)
2542 USE_NL(ta1 tau1)
2543 INDEX(ta1 HTA_HINT)
2544 INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2545 tau1.time_building_block_id,
2546 tau1.time_building_block_ovn
2547 from hxc_time_attribute_usages tau1,
2548 hxc_time_attributes ta1
2549 where tau1.time_building_block_id = tbb.time_building_block_id
2550 AND tau1.time_building_block_ovn = tbb.object_version_number
2551 AND tau1.time_attribute_id = ta1.time_attribute_id
2552 AND ta1.bld_blk_info_type_id = '||l_bld_block_info_id_outer||fnd_global.newline||
2553 ' AND ta1.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;
2554
2555 -- Copy the column used as segment.
2556 IF UPPER(l_segment_outer) = 'ATTRIBUTE_CATEGORY'
2557 THEN
2558 l_segment_name(0) := l_segment_outer;
2559 ELSE
2560 l_segment_name(FND_NUMBER.canonical_to_number(REPLACE(UPPER(l_segment_outer),'ATTRIBUTE'))) := l_segment_outer;
2561 END IF;
2562
2563
2564 else
2565
2566
2567 l_field_value:= l_field_value||fnd_global.newline||' AND (tbb.time_building_block_id,tbb.object_version_number)in (
2568 select /*+ UNNEST
2569 LEADING(ta1)
2570 USE_NL(ta1 tau1)
2571 INDEX(ta1 HTA_HINT)
2572 INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2573 tau1.time_building_block_id,
2574 tau1.time_building_block_ovn
2575 from hxc_time_attribute_usages tau1,
2576 hxc_time_attributes ta1
2577 where tau1.time_building_block_id = tbb.time_building_block_id
2578 AND tau1.time_building_block_ovn = tbb.object_version_number
2579 AND tau1.time_attribute_id = ta1.time_attribute_id
2580 AND ta1.bld_blk_info_type_id = '||l_bld_block_info_id_outer||fnd_global.newline||
2581 ' AND ta1.'||l_segment_outer||' is null ';
2582
2583 IF upper(l_segment_outer) = 'ATTRIBUTE_CATEGORY'
2584 THEN
2585 l_segment_name(0) := l_segment_outer;
2586 ELSE
2587 l_segment_name(FND_NUMBER.canonical_to_number(replace(upper(l_segment_outer),'ATTRIBUTE'))) := l_segment_outer;
2588 END IF;
2589
2590
2591
2592 end if;
2593
2594 l_index_inner:=t_consolidated_info.next(l_index);
2595
2596
2597 loop exit when not t_consolidated_info.exists(l_index_inner);
2598
2599 if (l_bld_block_info_id_outer=t_consolidated_info(l_index_inner).bld_blk_info_type_id) then
2600
2601 l_bld_block_info_id_inner:=t_consolidated_info(l_index_inner).bld_blk_info_type_id;
2602 l_field_name_inner:=t_consolidated_info(l_index_inner).field_name;
2603 l_field_value_inner:=t_consolidated_info(l_index_inner).field_value;
2604 l_segment_inner:=t_consolidated_info(l_index_inner).segment;
2605
2606 if(l_field_value_inner is not null) then
2607
2608 l_field_value:=l_field_value||fnd_global.newline||
2609 ' AND ta1.'||l_segment_inner||' = '''||l_field_value_inner||'''' ;
2610
2611 else
2612
2613 l_field_value:=l_field_value||fnd_global.newline||
2614 ' AND ta1.'||l_segment_inner||' is null ';
2615
2616 end if;
2617
2618 IF upper(l_segment_inner) = 'ATTRIBUTE_CATEGORY'
2619 THEN
2620 l_segment_name(0) := l_segment_inner;
2621 ELSE
2622 l_segment_name(FND_NUMBER.canonical_to_number(replace(upper(l_segment_inner),'ATTRIBUTE'))) := l_segment_inner;
2623 END IF;
2624
2625
2626 t_consolidated_info.delete(l_index_inner);
2627
2628 end if;
2629
2630
2631 l_index_inner:=t_consolidated_info.next(l_index_inner);
2632
2633 end loop;
2634 l_field_value:=l_field_value||')';
2635
2636 l_index:= t_consolidated_info.next(l_index);
2637
2638 -- Pick the last column in the order (which is also the hierarchy for Indexes' cardinality)
2639 -- and replace the given Hint.
2640
2641 -- Bug 12410558
2642 -- The above idea had a problem with Purchasing, when they are using PO header ID
2643 -- rather than Purchase order number or line. The segment count would be more than 0
2644 -- but the only segment available is Attribute8, for which we do not have an index.
2645 -- If such an attribute for which there is no INDEX is the LAST in segment_name,
2646 -- then loop thru and find out one which has an index. If nothing else is present,
2647 -- set the Hint to NULL, just like there is no Segment_name available.
2648
2649 l_found := FALSE;
2650 IF l_segment_name.COUNT > 0
2651 THEN
2652 IF g_debug
2653 THEN
2654 hr_utility.trace('Looping thru the segement names ');
2655 END IF;
2656 l_counter := l_segment_name.LAST;
2657 LOOP
2658 hr_utility.trace('Counter - attribute'||l_counter);
2659 IF l_index_name.EXISTS(l_counter)
2660 THEN
2661 l_field_value := REPLACE(l_field_value,'HTA_HINT',l_index_name(l_counter));
2662 l_found := TRUE;
2663 IF g_debug
2664 THEN
2665 hr_utility.trace('Replaced the hint here with '||l_index_name(l_counter));
2666 END IF;
2667 EXIT;
2668 END IF;
2669 l_counter := l_segment_name.PRIOR(l_counter);
2670 EXIT WHEN NOT l_segment_name.EXISTS(l_counter);
2671 END LOOP;
2672 IF NOT l_found
2673 THEN
2674 l_field_value := REPLACE(l_field_value,'HTA_HINT');
2675 IF g_debug
2676 THEN
2677 hr_utility.trace('No hint available, replacing with NULL');
2678 END IF;
2679 END IF;
2680 ELSE
2681 l_field_value := REPLACE(l_field_value,'HTA_HINT');
2682 END IF;
2683
2684
2685 end loop;
2686
2687 l_query:=l_query||l_field_value;
2688
2689
2690 --let us add the status check
2691 if p_status = 'WORKING' then
2692 l_status_list := '(''WORKING'''||','||'''SUBMITTED'''||','||'''APPROVED'')';
2693 elsif p_status = 'SUBMITTED' then
2694 l_status_list := '(''SUBMITTED'''||','||'''APPROVED'')';
2695 elsif p_status = 'APPROVED' then
2696 l_status_list := '(''APPROVED'')';
2697 end if;
2698
2699 if p_status in ('WORKING','SUBMITTED','APPROVED') then
2700
2701 l_status := '
2702 AND exists (
2703 select /*+ NO_UNNEST
2704 LEADING(detbb) */
2705 ''Y''
2706 from hxc_time_building_blocks detbb,
2707 hxc_time_building_blocks daybb,
2708 hxc_timecard_summary time_status
2709 where tbb.time_building_Block_id = detbb.time_building_block_id
2710 and tbb.object_version_number = detbb.object_version_number
2711 and detbb.parent_building_block_id = daybb.time_building_block_id
2712 and detbb.parent_building_block_ovn = daybb.object_version_number
2713 and time_status.timecard_id = daybb.parent_building_block_id
2714 and detbb.date_to = hr_general.end_of_time
2715 and time_status.approval_status IN '||l_status_list||' ) ';
2716
2717 l_query := l_query ||l_status;
2718
2719 end if;
2720
2721 IF ( p_scope = 'DETAIL' )
2722 THEN
2723
2724 l_end_date := '
2725 AND tbb.stop_time >= :p_end_date ';
2726
2727 ELSE
2728
2729 l_end_date := '
2730 AND exists (
2731 select 1 from hxc_time_building_blocks daybb1
2732 where tbb.parent_building_block_id = daybb1.time_building_block_id
2733 and tbb.parent_building_block_ovn = daybb1.object_version_number
2734 and daybb1.stop_time >= :p_end_date) ';
2735
2736 END IF;
2737
2738 if (p_end_date is not null) then
2739
2740 l_query := l_query || l_end_date;
2741 end if;
2742
2743
2744
2745
2746 l_query := l_query||')';
2747
2748 if g_debug then
2749 hr_utility.trace(' ');
2750 hr_utility.trace('Now let us print the query');
2751
2752 hr_utility.trace(substr(l_query,1,200));
2753 hr_utility.trace(substr(l_query,201,200));
2754 hr_utility.trace(substr(l_query,401,200));
2755 hr_utility.trace(substr(l_query,601,200));
2756 hr_utility.trace(substr(l_query,801,200));
2757 hr_utility.trace(substr(l_query,1001,200));
2758 hr_utility.trace(substr(l_query,1201,200));
2759 hr_utility.trace(substr(l_query,1401,200));
2760 hr_utility.trace(substr(l_query,1601,200));
2761 hr_utility.trace(substr(l_query,1801,200));
2762 hr_utility.trace(substr(l_query,2001,200));
2763 hr_utility.trace(substr(l_query,2201,200));
2764 hr_utility.trace(substr(l_query,2401,200));
2765 hr_utility.trace(substr(l_query,2601,200));
2766 hr_utility.trace(substr(l_query,2801,200));
2767 hr_utility.trace(substr(l_query,3001,200));
2768 hr_utility.trace(' ');
2769
2770 hr_utility.set_location('Processing '||l_proc, 40);
2771 end if;
2772
2773 if (p_end_date is not null and p_scope = 'DETAIL' ) then
2774
2775 OPEN map_cr FOR l_query USING p_end_date;
2776
2777 elsif (p_end_date is not null ) then
2778
2779 OPEN map_cr FOR l_query USING p_scope,p_end_date;
2780
2781 elsif (p_end_date is null and p_scope = 'DETAIL' ) then
2782
2783 OPEN map_cr FOR l_query ;
2784
2785 else
2786 OPEN map_cr FOR l_query USING p_scope;
2787
2788 end if;
2789
2790 if g_debug then
2791 hr_utility.set_location('Processing '||l_proc, 50);
2792 end if;
2793
2794 FETCH map_cr INTO l_exists;
2795
2796 CLOSE map_cr;
2797
2798 IF ( l_exists = 'Y' )
2799 THEN
2800 if g_debug then
2801 hr_utility.set_location('Processing '||l_proc, 60);
2802 end if;
2803
2804 l_mapping_exists := TRUE;
2805
2806 END IF;
2807
2808 if g_debug then
2809 hr_utility.set_location('Processing '||l_proc, 70);
2810
2811 hr_utility.trace('ret proc name is '||p_retrieval_process_name);
2812 end if;
2813
2814 IF ( ( p_retrieval_process_name = 'None' ) OR ( NOT l_mapping_exists ) )
2815 THEN
2816
2817 if g_debug then
2818 hr_utility.set_location('Processing '||l_proc, 75);
2819 end if;
2820
2821 RETURN l_mapping_exists;
2822
2823 END IF;
2824
2825 if g_debug then
2826 hr_utility.set_location('Processing '||l_proc, 80);
2827 end if;
2828
2829 -- the mapping exists and p_retrieval_process_name <> 'None'
2830 -- continue to check if it has been transferred.
2831
2832 -- check to see if the Retrieval Process is valid
2833
2834 OPEN csr_get_ret_id;
2835 FETCH csr_get_ret_id INTO l_ret_id;
2836
2837 IF csr_get_ret_id%NOTFOUND
2838 THEN
2839
2840 CLOSE csr_get_ret_id;
2841
2842 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2843 fnd_message.set_token('PROCEDURE', l_proc);
2844 fnd_message.set_token('STEP','Invalid Retrieval Process Name');
2845 fnd_message.raise_error;
2846
2847 END IF;
2848
2849 CLOSE csr_get_ret_id;
2850
2851 if g_debug then
2852 hr_utility.set_location('Processing '||l_proc, 90);
2853 end if;
2854
2855 IF ( p_scope <> 'DETAIL' )
2856 THEN
2857
2858 l_query := '
2859 SELECT ''Y''
2860 FROM dual
2861 WHERE EXISTS (
2862 SELECT 1
2863 FROM hxc_time_building_blocks tbb
2864 WHERE tbb.scope = :p_scope AND
2865 tbb.object_version_number = (
2866 SELECT MAX ( tbb1.object_version_number )
2867 FROM hxc_time_building_blocks tbb1
2868 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )
2869 AND NOT EXISTS (
2870 SELECT 1
2871 FROM hxc_transactions tx
2872 , hxc_transaction_details txd
2873 WHERE tx.transaction_process_id = :p_ret_id
2874 AND tx.status = ''SUCCESS''
2875 AND txd.transaction_id = tx.transaction_id
2876 AND txd.status = ''SUCCESS''
2877 AND txd.time_building_block_id = tbb.time_building_block_id
2878 AND txd.time_building_block_ovn = tbb.object_version_number ) ';
2879
2880 ELSE
2881
2882 -- must be DETAIL scope thus use hxc_latest_details
2883
2884 l_query := '
2885 SELECT ''Y''
2886 FROM dual
2887 WHERE EXISTS (
2888 SELECT 1
2889 FROM hxc_latest_details tbb
2890 WHERE NOT EXISTS (
2891 SELECT 1
2892 FROM hxc_transactions tx
2893 , hxc_transaction_details txd
2894 WHERE tx.transaction_process_id = :p_ret_id
2895 AND tx.status = ''SUCCESS''
2896 AND txd.transaction_id = tx.transaction_id
2897 AND txd.status = ''SUCCESS''
2898 AND txd.time_building_block_id = tbb.time_building_block_id
2899 AND txd.time_building_block_ovn = tbb.object_version_number ) ';
2900
2901
2902 END IF;
2903
2904
2905 l_query:=l_query||l_field_value;
2906
2907
2908 if p_status in ('WORKING','SUBMITTED','APPROVED') then
2909
2910 l_query := l_query ||l_status;
2911
2912 end if;
2913
2914 if (p_end_date is not null) then
2915
2916 l_query := l_query || l_end_date;
2917 end if;
2918
2919
2920 l_exists := 'N';
2921 l_mapping_exists := FALSE;
2922
2923 l_query := l_query||')';
2924
2925 if g_debug then
2926 hr_utility.trace(' ');
2927 hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2928
2929 hr_utility.trace(substr(l_query,1,200));
2930 hr_utility.trace(substr(l_query,201,200));
2931 hr_utility.trace(substr(l_query,401,200));
2932 hr_utility.trace(substr(l_query,601,200));
2933 hr_utility.trace(substr(l_query,801,200));
2934 hr_utility.trace(substr(l_query,1001,200));
2935 hr_utility.trace(substr(l_query,1201,200));
2936 hr_utility.trace(substr(l_query,1401,200));
2937 hr_utility.trace(substr(l_query,1601,200));
2938 hr_utility.trace(substr(l_query,1801,200));
2939 hr_utility.trace(substr(l_query,2001,200));
2940 hr_utility.trace(substr(l_query,2201,200));
2941 hr_utility.trace(substr(l_query,2401,200));
2942 hr_utility.trace(substr(l_query,2601,200));
2943 hr_utility.trace(substr(l_query,2801,200));
2944 hr_utility.trace(substr(l_query,3001,200));
2945 hr_utility.trace(' ');
2946 end if;
2947
2948
2949 if (p_end_date is not null and p_scope = 'DETAIL' ) then
2950
2951 OPEN txfrd_cr FOR l_query USING l_ret_id, p_end_date;
2952
2953 elsif (p_end_date is not null ) then
2954
2955 OPEN txfrd_cr FOR l_query USING p_scope, l_ret_id, p_end_date;
2956
2957 elsif (p_end_date is null and p_scope = 'DETAIL' ) then
2958
2959 OPEN txfrd_cr FOR l_query USING l_ret_id;
2960
2961 else
2962 OPEN txfrd_cr FOR l_query USING p_scope, l_ret_id;
2963
2964 end if;
2965
2966 FETCH txfrd_cr INTO l_exists;
2967
2968 CLOSE txfrd_cr;
2969
2970 if g_debug then
2971 hr_utility.set_location('Processing '||l_proc, 100);
2972 end if;
2973
2974 IF ( l_exists = 'Y' )
2975 THEN
2976 l_mapping_exists := TRUE;
2977 END IF;
2978
2979 END IF; -- IF ( csr_chk_otl_installed%FOUND )
2980
2981 CLOSE csr_chk_otl_installed;
2982
2983
2984 RETURN l_mapping_exists;
2985
2986 END chk_mapping_exists;
2987
2988
2989
2990 FUNCTION get_mappingvalue_sum ( p_bld_blk_info_type VARCHAR2
2991 , p_field_name1 VARCHAR2
2992 , p_bld_blk_info_type2 VARCHAR2 default null
2993 , p_field_name2 VARCHAR2
2994 , p_field_value2 VARCHAR2
2995 , p_bld_blk_info_type3 VARCHAR2 default null
2996 , p_field_name3 VARCHAR2 default null
2997 , p_field_value3 VARCHAR2 default null
2998 , p_bld_blk_info_type4 VARCHAR2 default null
2999 , p_field_name4 VARCHAR2 default null
3000 , p_field_value4 VARCHAR2 default null
3001 , p_bld_blk_info_type5 VARCHAR2 default null
3002 , p_field_name5 VARCHAR2 default null
3003 , p_field_value5 VARCHAR2 default null
3004 , p_status VARCHAR2
3005 , p_resource_id VARCHAR2
3006 ) RETURN NUMBER IS
3007
3008 l_proc VARCHAR2(72);
3009
3010 l_sum NUMBER(20,3); -- 12533942 Added add three decimal places to l_sum.
3011
3012 l_bld_blk_info_type_id hxc_mapping_components.bld_blk_info_type_id%TYPE;
3013 l_bld_blk_info_type_id2 hxc_mapping_components.bld_blk_info_type_id%TYPE;
3014 l_bld_blk_info_type_id3 hxc_mapping_components.bld_blk_info_type_id%TYPE;
3015 l_bld_blk_info_type_id4 hxc_mapping_components.bld_blk_info_type_id%TYPE;
3016 l_bld_blk_info_type_id5 hxc_mapping_components.bld_blk_info_type_id%TYPE;
3017 l_segment1 hxc_mapping_components.segment%TYPE;
3018 l_segment2 hxc_mapping_components.segment%TYPE;
3019 l_segment3 hxc_mapping_components.segment%TYPE;
3020 l_segment4 hxc_mapping_components.segment%TYPE;
3021 l_segment5 hxc_mapping_components.segment%TYPE;
3022
3023 t_consolidated_info hxc_mapping_utilities.t_consolidated_info_1;
3024
3025 l_bld_block_info_id_outer hxc_mapping_components.bld_blk_info_type_id%TYPE;
3026 l_field_name_outer varchar2(1000);
3027 l_field_value_outer varchar2(1000);
3028 l_segment_outer hxc_mapping_components.segment%TYPE;
3029
3030 l_bld_block_info_id_inner hxc_mapping_components.bld_blk_info_type_id%TYPE;
3031 l_field_name_inner varchar2(1000);
3032 l_field_value_inner varchar2(1000);
3033 l_segment_inner hxc_mapping_components.segment%TYPE;
3034
3035
3036 l_query VARCHAR2(8000);
3037
3038 l_status_list varchar2(400);
3039
3040 l_status varchar2(1000);
3041
3042 l_installed varchar2(1) := 'N';
3043
3044 l_field_value varchar2(6000);
3045
3046 l_index Number;
3047
3048 l_index_inner Number;
3049
3050 l_cons_index Number;
3051
3052
3053 TYPE MapExistsCur IS REF CURSOR;
3054 map_cr MapExistsCur;
3055
3056 CURSOR csr_chk_otl_installed IS
3057 SELECT 'Y'
3058 FROM fnd_product_installations pi
3059 WHERE pi.application_id = 809
3060 AND pi.status in ( 'S', 'I' );
3061
3062
3063 BEGIN --
3064 g_debug := hr_utility.debug_enabled;
3065
3066 if g_debug then
3067 l_proc := g_package||'.get_mappingvalue_sum';
3068 hr_utility.set_location('Processing '||l_proc, 10);
3069 end if;
3070
3071
3072 -- chk to see if OTL is installed
3073
3074 t_consolidated_info.delete;
3075
3076 OPEN csr_chk_otl_installed;
3077
3078 FETCH csr_chk_otl_installed INTO l_installed;
3079
3080 IF ( csr_chk_otl_installed%FOUND )
3081 THEN
3082
3083
3084 if(p_field_name1 is not null) then
3085
3086 get_mapping_value(p_bld_blk_info_type,p_field_name1,l_segment1,l_bld_blk_info_type_id);
3087
3088 end if;
3089
3090
3091 if(p_field_name2 is not null) then
3092
3093 if(p_bld_blk_info_type2 is not null) then
3094
3095 get_mapping_value(p_bld_blk_info_type2,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
3096
3097 else
3098
3099 get_mapping_value(p_bld_blk_info_type,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
3100 end if;
3101 end if;
3102
3103
3104
3105 if(p_field_name3 is not null) then
3106
3107 get_mapping_value(p_bld_blk_info_type3,p_field_name3,l_segment3,l_bld_blk_info_type_id3);
3108
3109 end if;
3110
3111 if(p_field_name4 is not null) then
3112
3113 get_mapping_value(p_bld_blk_info_type4,p_field_name4,l_segment4,l_bld_blk_info_type_id4);
3114
3115 end if;
3116
3117
3118 if(p_field_name5 is not null) then
3119
3120 get_mapping_value(p_bld_blk_info_type5,p_field_name5,l_segment5,l_bld_blk_info_type_id5);
3121
3122 end if;
3123
3124
3125
3126 if(p_field_name2 is not null) then
3127 if(t_consolidated_info.count>0) then
3128 l_cons_index:=t_consolidated_info.count+1;
3129 else
3130 l_cons_index:=1;
3131 end if;
3132
3133 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id2;
3134 t_consolidated_info(l_cons_index).field_name :=p_field_name2;
3135 t_consolidated_info(l_cons_index).field_value:=p_field_value2 ;
3136 t_consolidated_info(l_cons_index).segment:=l_segment2;
3137
3138 end if;
3139
3140 if(p_bld_blk_info_type3 is not null and p_field_name3 is not null) then
3141 if(t_consolidated_info.count>0) then
3142 l_cons_index:=t_consolidated_info.count+1;
3143 else
3144 l_cons_index:=1;
3145 end if;
3146
3147 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id3;
3148 t_consolidated_info(l_cons_index).field_name :=p_field_name3;
3149 t_consolidated_info(l_cons_index).field_value:=p_field_value3 ;
3150 t_consolidated_info(l_cons_index).segment:=l_segment3;
3151
3152 end if;
3153
3154 if(p_bld_blk_info_type4 is not null and p_field_name4 is not null) then
3155 if(t_consolidated_info.count>0) then
3156 l_cons_index:=t_consolidated_info.count+1;
3157 else
3158 l_cons_index:=1;
3159 end if;
3160
3161 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id4;
3162 t_consolidated_info(l_cons_index).field_name :=p_field_name4;
3163 t_consolidated_info(l_cons_index).field_value:=p_field_value4 ;
3164 t_consolidated_info(l_cons_index).segment:=l_segment4;
3165
3166 end if;
3167
3168 if(p_bld_blk_info_type5 is not null and p_field_name5 is not null) then
3169 if(t_consolidated_info.count>0) then
3170 l_cons_index:=t_consolidated_info.count+1;
3171 else
3172 l_cons_index:=1;
3173 end if;
3174
3175 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id5;
3176 t_consolidated_info(l_cons_index).field_name :=p_field_name5;
3177 t_consolidated_info(l_cons_index).field_value:=p_field_value5 ;
3178 t_consolidated_info(l_cons_index).segment:=l_segment5;
3179
3180 end if;
3181
3182
3183
3184
3185 if g_debug then
3186 hr_utility.set_location('Processing '||l_proc, 30);
3187 end if;
3188
3189 -- build dynamic SQL query
3190
3191 l_query := '
3192 select sum(ta.'||l_segment1||')
3193 from hxc_time_attributes ta,
3194 hxc_time_attribute_usages tau,
3195 hxc_time_building_blocks tbb
3196 where
3197 tbb.scope = :p_scope
3198 AND tbb.resource_id = :p_resource_id
3199 AND tbb.object_version_number = (
3200 SELECT MAX ( tbb1.object_version_number )
3201 FROM hxc_time_building_blocks tbb1
3202 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )
3203 AND tau.time_building_block_id = tbb.time_building_block_id
3204 AND tau.time_building_block_ovn = tbb.object_version_number
3205 AND tau.time_attribute_id = ta.time_attribute_id
3206 AND tbb.date_to = hr_general.end_of_time
3207 And ta.bld_blk_info_type_id = :l_bld_blk_info_type_id';
3208
3209
3210 if g_debug then
3211 hr_utility.set_location('Processing '||l_proc, 30.01);
3212 end if;
3213 l_index:=t_consolidated_info.first;
3214
3215 loop exit when not t_consolidated_info.exists(l_index);
3216
3217
3218 l_bld_block_info_id_outer:=t_consolidated_info(l_index).bld_blk_info_type_id;
3219 l_field_name_outer:=t_consolidated_info(l_index).field_name;
3220 l_field_value_outer:=t_consolidated_info(l_index).field_value;
3221 l_segment_outer:=t_consolidated_info(l_index).segment;
3222
3223 if(l_field_value_outer is not null) then
3224
3225 l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
3226 hxc_time_attributes ta2
3227 where tau2.time_building_block_id = tbb.time_building_block_id
3228 AND tau2.time_building_block_ovn = tbb.object_version_number
3229 AND tau2.time_attribute_id = ta2.time_attribute_id
3230 AND ta2.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
3231 ' AND ta2.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;
3232
3233
3234 else
3235
3236
3237 l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
3238 hxc_time_attributes ta2
3239 where tau2.time_building_block_id = tbb.time_building_block_id
3240 AND tau2.time_building_block_ovn = tbb.object_version_number
3241 AND tau2.time_attribute_id = ta2.time_attribute_id
3242 AND ta2.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
3243 ' AND ta2.'||l_segment_outer||' is null ';
3244
3245
3246 end if;
3247
3248 if g_debug then
3249 hr_utility.set_location('Processing '||l_proc, 30.02);
3250 end if;
3251
3252 l_index_inner:=t_consolidated_info.next(l_index);
3253
3254
3255 loop exit when not t_consolidated_info.exists(l_index_inner);
3256
3257 if (l_bld_block_info_id_outer=t_consolidated_info(l_index_inner).bld_blk_info_type_id) then
3258
3259 l_bld_block_info_id_inner:=t_consolidated_info(l_index_inner).bld_blk_info_type_id;
3260 l_field_name_inner:=t_consolidated_info(l_index_inner).field_name;
3261 l_field_value_inner:=t_consolidated_info(l_index_inner).field_value;
3262 l_segment_inner:=t_consolidated_info(l_index_inner).segment;
3263
3264 if(l_field_value_inner is not null) then
3265
3266 l_field_value:=l_field_value||' AND ta2.'||l_segment_inner||' = '''||l_field_value_inner||'''' ;
3267
3268 else
3269
3270 l_field_value:=l_field_value||' AND ta2.'||l_segment_inner||' is null ';
3271
3272 end if;
3273
3274 t_consolidated_info.delete(l_index_inner);
3275
3276 end if;
3277
3278
3279 l_index_inner:=t_consolidated_info.next(l_index_inner);
3280
3281 end loop;
3282 l_field_value:=l_field_value||')';
3283
3284 l_index:= t_consolidated_info.next(l_index);
3285
3286 end loop;
3287
3288 l_query:=l_query||l_field_value;
3289
3290 if g_debug then
3291 hr_utility.set_location('Processing '||l_proc, 30.1);
3292 end if;
3293
3294 --let us add the status check
3295 if p_status = 'WORKING' then
3296 l_status_list := '(''WORKING'''||','||'''SUBMITTED'''||','||'''APPROVED'')';
3297 elsif p_status = 'SUBMITTED' then
3298 l_status_list := '(''SUBMITTED'''||','||'''APPROVED'')';
3299 elsif p_status = 'APPROVED' then
3300 l_status_list := '(''APPROVED'')';
3301 end if;
3302
3303
3304 if g_debug then
3305 hr_utility.set_location('Processing '||l_proc, 30.2);
3306 end if;
3307 if p_status in ('WORKING','SUBMITTED','APPROVED') then
3308
3309 l_status := '
3310 AND exists (
3311 select ''Y''
3312 from hxc_time_building_blocks daybb,
3313 hxc_time_building_blocks timebb,
3314 hxc_timecard_summary time_status
3315 where tbb.parent_building_block_id = daybb.time_building_block_id
3316 and tbb.parent_building_block_ovn = daybb.object_version_number
3317 and daybb.parent_building_block_id = timebb.time_building_block_id
3318 and daybb.parent_building_block_ovn = timebb.object_version_number
3319 and time_status.timecard_id = timebb.time_building_block_id
3320 and time_status.approval_status IN '||l_status_list||' ) ';
3321
3322 l_query := l_query ||l_status;
3323
3324 end if;
3325
3326
3327 if g_debug then
3328 hr_utility.trace(' ');
3329 hr_utility.trace('Now let us print the query');
3330
3331 hr_utility.trace(substr(l_query,1,200));
3332 hr_utility.trace(substr(l_query,201,200));
3333 hr_utility.trace(substr(l_query,401,200));
3334 hr_utility.trace(substr(l_query,601,200));
3335 hr_utility.trace(substr(l_query,801,200));
3336 hr_utility.trace(substr(l_query,1001,200));
3337 hr_utility.trace(substr(l_query,1201,200));
3338 hr_utility.trace(substr(l_query,1401,200));
3339 hr_utility.trace(substr(l_query,1601,200));
3340 hr_utility.trace(substr(l_query,1801,200));
3341 hr_utility.trace(substr(l_query,2001,200));
3342 hr_utility.trace(substr(l_query,2201,200));
3343 hr_utility.trace(substr(l_query,2401,200));
3344 hr_utility.trace(substr(l_query,2601,200));
3345 hr_utility.trace(substr(l_query,2801,200));
3346 hr_utility.trace(substr(l_query,3001,200));
3347 hr_utility.trace(' ');
3348
3349 hr_utility.set_location('Processing '||l_proc, 40);
3350 end if;
3351
3352 OPEN map_cr FOR l_query USING 'DETAIL', p_resource_id,l_bld_blk_info_type_id;
3353
3354 FETCH map_cr INTO l_sum;
3355
3356 CLOSE map_cr;
3357
3358 if g_debug then
3359 hr_utility.set_location('Processing '||l_proc, 70);
3360 end if;
3361
3362
3363 END IF; -- IF ( csr_chk_otl_installed%FOUND )
3364
3365 CLOSE csr_chk_otl_installed;
3366
3367
3368 RETURN l_sum;
3369
3370 EXCEPTION
3371
3372 WHEN OTHERS then
3373
3374 if g_debug then
3375 hr_utility.trace('Error is '||substr(sqlerrm,1,200));
3376 end if;
3377 hr_utility.set_message(809, sqlerrm);
3378 hr_utility.raise_error;
3379
3380 END get_mappingvalue_sum;
3381
3382
3383 end hxc_mapping_utilities;