[Home] [Help]
PACKAGE BODY: APPS.HXC_MAPPING_UTILITIES
Source
1 package body hxc_mapping_utilities as
2 /* $Header: hxcmputl.pkb 120.7.12010000.3 2008/08/07 12:59:05 asrajago ship $ */
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 TYPE MapExistsCur IS REF CURSOR;
2180 map_cr MapExistsCur;
2181
2182 TYPE MapTxfrdCur IS REF CURSOR;
2183 txfrd_cr MapTxfrdCur;
2184
2185 CURSOR csr_chk_otl_installed IS
2186 SELECT 'Y'
2187 FROM fnd_product_installations pi
2188 WHERE pi.application_id = 809
2189 AND pi.status in ( 'S', 'I' );
2190
2191
2192 CURSOR csr_get_ret_id IS
2193 SELECT ret.retrieval_process_id
2194 FROM hxc_retrieval_processes ret
2195 WHERE ret.name = p_retrieval_process_name;
2196
2197
2198
2199 BEGIN -- chk_mapping_exists
2200
2201 g_debug := hr_utility.debug_enabled;
2202
2203 if g_debug then
2204 l_proc := g_package||'.chk_mapping_exists';
2205 hr_utility.set_location('Processing '||l_proc, 10);
2206 end if;
2207
2208 -- chk to see if OTL is installed
2209
2210 OPEN csr_chk_otl_installed;
2211
2212 FETCH csr_chk_otl_installed INTO l_installed;
2213
2214 IF ( csr_chk_otl_installed%FOUND )
2215 THEN
2216
2217 IF ( p_scope <> 'DETAIL' AND p_status <> 'None' )
2218 THEN
2219
2220 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2221 fnd_message.set_token('PROCEDURE', l_proc);
2222 fnd_message.set_token('STEP','Scope Status combo not supported');
2223 fnd_message.raise_error;
2224
2225 END IF;
2226
2227
2228
2229
2230 if(p_field_name is not null) then
2231
2232 get_mapping_value(p_bld_blk_info_type,p_field_name,l_segment1,l_bld_blk_info_type_id);
2233
2234 end if;
2235
2236 if(p_field_name2 is not null) then
2237
2238 get_mapping_value(p_bld_blk_info_type2,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
2239
2240 end if;
2241
2242 if(p_field_name3 is not null) then
2243
2244 get_mapping_value(p_bld_blk_info_type3,p_field_name3,l_segment3,l_bld_blk_info_type_id3);
2245
2246 end if;
2247
2248
2249 if(p_field_name4 is not null) then
2250
2251 get_mapping_value(p_bld_blk_info_type4,p_field_name4,l_segment4,l_bld_blk_info_type_id4);
2252
2253 end if;
2254
2255
2256 if(p_field_name5 is not null) then
2257
2258 get_mapping_value(p_bld_blk_info_type5,p_field_name5,l_segment5,l_bld_blk_info_type_id5);
2259
2260 end if;
2261
2262
2263
2264
2265
2266 if(p_bld_blk_info_type is not null and p_field_name is not null) then
2267 if(t_consolidated_info.count>0) then
2268 l_cons_index:=t_consolidated_info.count+1;
2269 else
2270 l_cons_index:=1;
2271 end if;
2272
2273 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id;
2274 t_consolidated_info(l_cons_index).field_name :=p_field_name;
2275 t_consolidated_info(l_cons_index).field_value:=p_field_value ;
2276 t_consolidated_info(l_cons_index).segment:=l_segment1;
2277
2278 end if;
2279
2280 if(p_bld_blk_info_type2 is not null and p_field_name2 is not null) then
2281 if(t_consolidated_info.count>0) then
2282 l_cons_index:=t_consolidated_info.count+1;
2283 else
2284 l_cons_index:=1;
2285 end if;
2286
2287 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id2;
2288 t_consolidated_info(l_cons_index).field_name :=p_field_name2;
2289 t_consolidated_info(l_cons_index).field_value:=p_field_value2 ;
2290 t_consolidated_info(l_cons_index).segment:=l_segment2;
2291
2292
2293 end if;
2294
2295 if(p_bld_blk_info_type3 is not null and p_field_name3 is not null) then
2296 if(t_consolidated_info.count>0) then
2297 l_cons_index:=t_consolidated_info.count+1;
2298 else
2299 l_cons_index:=1;
2300 end if;
2301
2302 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id3;
2303 t_consolidated_info(l_cons_index).field_name :=p_field_name3;
2304 t_consolidated_info(l_cons_index).field_value:=p_field_value3 ;
2305 t_consolidated_info(l_cons_index).segment:=l_segment3;
2306
2307 end if;
2308
2309 if(p_bld_blk_info_type4 is not null and p_field_name4 is not null) then
2310 if(t_consolidated_info.count>0) then
2311 l_cons_index:=t_consolidated_info.count+1;
2312 else
2313 l_cons_index:=1;
2314 end if;
2315
2316 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id4;
2317 t_consolidated_info(l_cons_index).field_name :=p_field_name4;
2318 t_consolidated_info(l_cons_index).field_value:=p_field_value4 ;
2319 t_consolidated_info(l_cons_index).segment:=l_segment4;
2320
2321 end if;
2322
2323 if(p_bld_blk_info_type5 is not null and p_field_name5 is not null) then
2324 if(t_consolidated_info.count>0) then
2325 l_cons_index:=t_consolidated_info.count+1;
2326 else
2327 l_cons_index:=1;
2328 end if;
2329
2330 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id5;
2331 t_consolidated_info(l_cons_index).field_name :=p_field_name5;
2332 t_consolidated_info(l_cons_index).field_value:=p_field_value5 ;
2333 t_consolidated_info(l_cons_index).segment:=l_segment5;
2334
2335 end if;
2336
2337
2338 if g_debug then
2339 hr_utility.set_location('Processing '||l_proc, 30);
2340 end if;
2341
2342 -- build dynamic SQL query
2343
2344 IF ( p_scope <> 'DETAIL' )
2345 THEN
2346
2347 l_query := '
2348 SELECT ''Y''
2349 FROM dual
2350 WHERE EXISTS (
2351 SELECT 1
2352 FROM hxc_time_building_blocks tbb
2353 WHERE tbb.scope = :p_scope AND
2354 tbb.object_version_number = (
2355 SELECT MAX ( tbb1.object_version_number )
2356 FROM hxc_time_building_blocks tbb1
2357 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )';
2358
2359 ELSE
2360
2361 -- p scope must be DETAIL which means we can use the summary table hxc_latest_details
2362
2363 l_query := '
2364 SELECT ''Y''
2365 FROM dual
2366 WHERE EXISTS (
2367 SELECT 1
2368 FROM hxc_latest_details tbb
2369 WHERE 1=1';
2370
2371 END IF;
2372
2373
2374
2375 l_index:=t_consolidated_info.first;
2376
2377 loop exit when not t_consolidated_info.exists(l_index);
2378
2379
2380 l_bld_block_info_id_outer:=t_consolidated_info(l_index).bld_blk_info_type_id;
2381 l_field_name_outer:=t_consolidated_info(l_index).field_name;
2382 l_field_value_outer:=t_consolidated_info(l_index).field_value;
2383 l_segment_outer:=t_consolidated_info(l_index).segment;
2384
2385 if(l_field_value_outer is not null) then
2386
2387
2388
2389
2390 l_field_value:= l_field_value||' AND (tbb.time_building_block_id,tbb.object_version_number)in (
2391 select /*+ LEADING(ta1) INDEX(ta1)
2392 INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2393 tau1.time_building_block_id,tau1.time_building_block_ovn from
2394 hxc_time_attribute_usages tau1,
2395 hxc_time_attributes ta1
2396 where tau1.time_building_block_id = tbb.time_building_block_id
2397 AND tau1.time_building_block_ovn = tbb.object_version_number
2398 AND tau1.time_attribute_id = ta1.time_attribute_id
2399 AND ta1.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
2400 ' AND ta1.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;
2401
2402
2403 else
2404
2405
2406 l_field_value:= l_field_value||' AND (tbb.time_building_block_id,tbb.object_version_number)in (
2407 select /*+ LEADING(ta1) INDEX(ta1)
2408 INDEX(tau1 HXC_TIME_ATTRIBUTE_USAGES_FK1) */
2409 tau1.time_building_block_id,tau1.time_building_block_ovn from
2410 hxc_time_attribute_usages tau1,
2411 hxc_time_attributes ta1
2412 where tau1.time_building_block_id = tbb.time_building_block_id
2413 AND tau1.time_building_block_ovn = tbb.object_version_number
2414 AND tau1.time_attribute_id = ta1.time_attribute_id
2415 AND ta1.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
2416 ' AND ta1.'||l_segment_outer||' is null ';
2417
2418
2419 end if;
2420
2421 l_index_inner:=t_consolidated_info.next(l_index);
2422
2423
2424 loop exit when not t_consolidated_info.exists(l_index_inner);
2425
2426 if (l_bld_block_info_id_outer=t_consolidated_info(l_index_inner).bld_blk_info_type_id) then
2427
2428 l_bld_block_info_id_inner:=t_consolidated_info(l_index_inner).bld_blk_info_type_id;
2429 l_field_name_inner:=t_consolidated_info(l_index_inner).field_name;
2430 l_field_value_inner:=t_consolidated_info(l_index_inner).field_value;
2431 l_segment_inner:=t_consolidated_info(l_index_inner).segment;
2432
2433 if(l_field_value_inner is not null) then
2434
2435 l_field_value:=l_field_value||' AND ta1.'||l_segment_inner||' = '''||l_field_value_inner||'''' ;
2436
2437 else
2438
2439 l_field_value:=l_field_value||' AND ta1.'||l_segment_inner||' is null ';
2440
2441 end if;
2442
2443 t_consolidated_info.delete(l_index_inner);
2444
2445 end if;
2446
2447
2448 l_index_inner:=t_consolidated_info.next(l_index_inner);
2449
2450 end loop;
2451 l_field_value:=l_field_value||')';
2452
2453 l_index:= t_consolidated_info.next(l_index);
2454
2455 end loop;
2456
2457 l_query:=l_query||l_field_value;
2458
2459
2460 --let us add the status check
2461 if p_status = 'WORKING' then
2462 l_status_list := '(''WORKING'''||','||'''SUBMITTED'''||','||'''APPROVED'')';
2463 elsif p_status = 'SUBMITTED' then
2464 l_status_list := '(''SUBMITTED'''||','||'''APPROVED'')';
2465 elsif p_status = 'APPROVED' then
2466 l_status_list := '(''APPROVED'')';
2467 end if;
2468
2469 if p_status in ('WORKING','SUBMITTED','APPROVED') then
2470
2471 l_status := '
2472 AND exists (
2473 select ''Y''
2474 from hxc_time_building_blocks detbb,
2475 hxc_time_building_blocks daybb,
2476 hxc_timecard_summary time_status
2477 where tbb.time_building_Block_id = detbb.time_building_block_id
2478 and tbb.object_version_number = detbb.object_version_number
2479 and detbb.parent_building_block_id = daybb.time_building_block_id
2480 and detbb.parent_building_block_ovn = daybb.object_version_number
2481 and time_status.timecard_id = daybb.parent_building_block_id
2482 and detbb.date_to = hr_general.end_of_time
2483 and time_status.approval_status IN '||l_status_list||' ) ';
2484
2485 l_query := l_query ||l_status;
2486
2487 end if;
2488
2489 IF ( p_scope = 'DETAIL' )
2490 THEN
2491
2492 l_end_date := '
2493 AND tbb.stop_time >= :p_end_date ';
2494
2495 ELSE
2496
2497 l_end_date := '
2498 AND exists (
2499 select 1 from hxc_time_building_blocks daybb1
2500 where tbb.parent_building_block_id = daybb1.time_building_block_id
2501 and tbb.parent_building_block_ovn = daybb1.object_version_number
2502 and daybb1.stop_time >= :p_end_date) ';
2503
2504 END IF;
2505
2506 if (p_end_date is not null) then
2507
2508 l_query := l_query || l_end_date;
2509 end if;
2510
2511
2512
2513
2514 l_query := l_query||')';
2515
2516 if g_debug then
2517 hr_utility.trace(' ');
2518 hr_utility.trace('Now let us print the query');
2519
2520 hr_utility.trace(substr(l_query,1,200));
2521 hr_utility.trace(substr(l_query,201,200));
2522 hr_utility.trace(substr(l_query,401,200));
2523 hr_utility.trace(substr(l_query,601,200));
2524 hr_utility.trace(substr(l_query,801,200));
2525 hr_utility.trace(substr(l_query,1001,200));
2526 hr_utility.trace(substr(l_query,1201,200));
2527 hr_utility.trace(substr(l_query,1401,200));
2528 hr_utility.trace(substr(l_query,1601,200));
2529 hr_utility.trace(substr(l_query,1801,200));
2530 hr_utility.trace(substr(l_query,2001,200));
2531 hr_utility.trace(substr(l_query,2201,200));
2532 hr_utility.trace(substr(l_query,2401,200));
2533 hr_utility.trace(substr(l_query,2601,200));
2534 hr_utility.trace(substr(l_query,2801,200));
2535 hr_utility.trace(substr(l_query,3001,200));
2536 hr_utility.trace(' ');
2537
2538 hr_utility.set_location('Processing '||l_proc, 40);
2539 end if;
2540
2541 if (p_end_date is not null and p_scope = 'DETAIL' ) then
2542
2543 OPEN map_cr FOR l_query USING p_end_date;
2544
2545 elsif (p_end_date is not null ) then
2546
2547 OPEN map_cr FOR l_query USING p_scope,p_end_date;
2548
2549 elsif (p_end_date is null and p_scope = 'DETAIL' ) then
2550
2551 OPEN map_cr FOR l_query ;
2552
2553 else
2554 OPEN map_cr FOR l_query USING p_scope;
2555
2556 end if;
2557
2558 if g_debug then
2559 hr_utility.set_location('Processing '||l_proc, 50);
2560 end if;
2561
2562 FETCH map_cr INTO l_exists;
2563
2564 CLOSE map_cr;
2565
2566 IF ( l_exists = 'Y' )
2567 THEN
2568 if g_debug then
2569 hr_utility.set_location('Processing '||l_proc, 60);
2570 end if;
2571
2572 l_mapping_exists := TRUE;
2573
2574 END IF;
2575
2576 if g_debug then
2577 hr_utility.set_location('Processing '||l_proc, 70);
2578
2579 hr_utility.trace('ret proc name is '||p_retrieval_process_name);
2580 end if;
2581
2582 IF ( ( p_retrieval_process_name = 'None' ) OR ( NOT l_mapping_exists ) )
2583 THEN
2584
2585 if g_debug then
2586 hr_utility.set_location('Processing '||l_proc, 75);
2587 end if;
2588
2589 RETURN l_mapping_exists;
2590
2591 END IF;
2592
2593 if g_debug then
2594 hr_utility.set_location('Processing '||l_proc, 80);
2595 end if;
2596
2597 -- the mapping exists and p_retrieval_process_name <> 'None'
2598 -- continue to check if it has been transferred.
2599
2600 -- check to see if the Retrieval Process is valid
2601
2602 OPEN csr_get_ret_id;
2603 FETCH csr_get_ret_id INTO l_ret_id;
2604
2605 IF csr_get_ret_id%NOTFOUND
2606 THEN
2607
2608 CLOSE csr_get_ret_id;
2609
2610 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2611 fnd_message.set_token('PROCEDURE', l_proc);
2612 fnd_message.set_token('STEP','Invalid Retrieval Process Name');
2613 fnd_message.raise_error;
2614
2615 END IF;
2616
2617 CLOSE csr_get_ret_id;
2618
2619 if g_debug then
2620 hr_utility.set_location('Processing '||l_proc, 90);
2621 end if;
2622
2623 IF ( p_scope <> 'DETAIL' )
2624 THEN
2625
2626 l_query := '
2627 SELECT ''Y''
2628 FROM dual
2629 WHERE EXISTS (
2630 SELECT 1
2631 FROM hxc_time_building_blocks tbb
2632 WHERE tbb.scope = :p_scope AND
2633 tbb.object_version_number = (
2634 SELECT MAX ( tbb1.object_version_number )
2635 FROM hxc_time_building_blocks tbb1
2636 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )
2637 AND NOT EXISTS (
2638 SELECT 1
2639 FROM hxc_transactions tx
2640 , hxc_transaction_details txd
2641 WHERE tx.transaction_process_id = :p_ret_id
2642 AND tx.status = ''SUCCESS''
2643 AND txd.transaction_id = tx.transaction_id
2644 AND txd.status = ''SUCCESS''
2645 AND txd.time_building_block_id = tbb.time_building_block_id
2646 AND txd.time_building_block_ovn = tbb.object_version_number ) ';
2647
2648 ELSE
2649
2650 -- must be DETAIL scope thus use hxc_latest_details
2651
2652 l_query := '
2653 SELECT ''Y''
2654 FROM dual
2655 WHERE EXISTS (
2656 SELECT 1
2657 FROM hxc_latest_details tbb
2658 WHERE NOT EXISTS (
2659 SELECT 1
2660 FROM hxc_transactions tx
2661 , hxc_transaction_details txd
2662 WHERE tx.transaction_process_id = :p_ret_id
2663 AND tx.status = ''SUCCESS''
2664 AND txd.transaction_id = tx.transaction_id
2665 AND txd.status = ''SUCCESS''
2666 AND txd.time_building_block_id = tbb.time_building_block_id
2667 AND txd.time_building_block_ovn = tbb.object_version_number ) ';
2668
2669
2670 END IF;
2671
2672
2673 l_query:=l_query||l_field_value;
2674
2675
2676 if p_status in ('WORKING','SUBMITTED','APPROVED') then
2677
2678 l_query := l_query ||l_status;
2679
2680 end if;
2681
2682 if (p_end_date is not null) then
2683
2684 l_query := l_query || l_end_date;
2685 end if;
2686
2687
2688 l_exists := 'N';
2689 l_mapping_exists := FALSE;
2690
2691 l_query := l_query||')';
2692
2693 if g_debug then
2694 hr_utility.trace(' ');
2695 hr_utility.trace('Now let us print the query that also includes check for retrieval status of timecards');
2696
2697 hr_utility.trace(substr(l_query,1,200));
2698 hr_utility.trace(substr(l_query,201,200));
2699 hr_utility.trace(substr(l_query,401,200));
2700 hr_utility.trace(substr(l_query,601,200));
2701 hr_utility.trace(substr(l_query,801,200));
2702 hr_utility.trace(substr(l_query,1001,200));
2703 hr_utility.trace(substr(l_query,1201,200));
2704 hr_utility.trace(substr(l_query,1401,200));
2705 hr_utility.trace(substr(l_query,1601,200));
2706 hr_utility.trace(substr(l_query,1801,200));
2707 hr_utility.trace(substr(l_query,2001,200));
2708 hr_utility.trace(substr(l_query,2201,200));
2709 hr_utility.trace(substr(l_query,2401,200));
2710 hr_utility.trace(substr(l_query,2601,200));
2711 hr_utility.trace(substr(l_query,2801,200));
2712 hr_utility.trace(substr(l_query,3001,200));
2713 hr_utility.trace(' ');
2714 end if;
2715
2716
2717 if (p_end_date is not null and p_scope = 'DETAIL' ) then
2718
2719 OPEN txfrd_cr FOR l_query USING l_ret_id, p_end_date;
2720
2721 elsif (p_end_date is not null ) then
2722
2723 OPEN txfrd_cr FOR l_query USING p_scope, l_ret_id, p_end_date;
2724
2725 elsif (p_end_date is null and p_scope = 'DETAIL' ) then
2726
2727 OPEN txfrd_cr FOR l_query USING l_ret_id;
2728
2729 else
2730 OPEN txfrd_cr FOR l_query USING p_scope, l_ret_id;
2731
2732 end if;
2733
2734 FETCH txfrd_cr INTO l_exists;
2735
2736 CLOSE txfrd_cr;
2737
2738 if g_debug then
2739 hr_utility.set_location('Processing '||l_proc, 100);
2740 end if;
2741
2742 IF ( l_exists = 'Y' )
2743 THEN
2744 l_mapping_exists := TRUE;
2745 END IF;
2746
2747 END IF; -- IF ( csr_chk_otl_installed%FOUND )
2748
2749 CLOSE csr_chk_otl_installed;
2750
2751
2752 RETURN l_mapping_exists;
2753
2754 END chk_mapping_exists;
2755
2756
2757
2758 FUNCTION get_mappingvalue_sum ( p_bld_blk_info_type VARCHAR2
2759 , p_field_name1 VARCHAR2
2760 , p_bld_blk_info_type2 VARCHAR2 default null
2761 , p_field_name2 VARCHAR2
2762 , p_field_value2 VARCHAR2
2763 , p_bld_blk_info_type3 VARCHAR2 default null
2764 , p_field_name3 VARCHAR2 default null
2765 , p_field_value3 VARCHAR2 default null
2766 , p_bld_blk_info_type4 VARCHAR2 default null
2767 , p_field_name4 VARCHAR2 default null
2768 , p_field_value4 VARCHAR2 default null
2769 , p_bld_blk_info_type5 VARCHAR2 default null
2770 , p_field_name5 VARCHAR2 default null
2771 , p_field_value5 VARCHAR2 default null
2772 , p_status VARCHAR2
2773 , p_resource_id VARCHAR2
2774 ) RETURN NUMBER IS
2775
2776 l_proc VARCHAR2(72);
2777
2778 l_sum NUMBER(20);
2779
2780 l_bld_blk_info_type_id hxc_mapping_components.bld_blk_info_type_id%TYPE;
2781 l_bld_blk_info_type_id2 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2782 l_bld_blk_info_type_id3 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2783 l_bld_blk_info_type_id4 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2784 l_bld_blk_info_type_id5 hxc_mapping_components.bld_blk_info_type_id%TYPE;
2785 l_segment1 hxc_mapping_components.segment%TYPE;
2786 l_segment2 hxc_mapping_components.segment%TYPE;
2787 l_segment3 hxc_mapping_components.segment%TYPE;
2788 l_segment4 hxc_mapping_components.segment%TYPE;
2789 l_segment5 hxc_mapping_components.segment%TYPE;
2790
2791 t_consolidated_info hxc_mapping_utilities.t_consolidated_info_1;
2792
2793 l_bld_block_info_id_outer hxc_mapping_components.bld_blk_info_type_id%TYPE;
2794 l_field_name_outer varchar2(1000);
2795 l_field_value_outer varchar2(1000);
2796 l_segment_outer hxc_mapping_components.segment%TYPE;
2797
2798 l_bld_block_info_id_inner hxc_mapping_components.bld_blk_info_type_id%TYPE;
2799 l_field_name_inner varchar2(1000);
2800 l_field_value_inner varchar2(1000);
2801 l_segment_inner hxc_mapping_components.segment%TYPE;
2802
2803
2804 l_query VARCHAR2(8000);
2805
2806 l_status_list varchar2(400);
2807
2808 l_status varchar2(1000);
2809
2810 l_installed varchar2(1) := 'N';
2811
2812 l_field_value varchar2(6000);
2813
2814 l_index Number;
2815
2816 l_index_inner Number;
2817
2818 l_cons_index Number;
2819
2820
2821 TYPE MapExistsCur IS REF CURSOR;
2822 map_cr MapExistsCur;
2823
2824 CURSOR csr_chk_otl_installed IS
2825 SELECT 'Y'
2826 FROM fnd_product_installations pi
2827 WHERE pi.application_id = 809
2828 AND pi.status in ( 'S', 'I' );
2829
2830
2831 BEGIN --
2832 g_debug := hr_utility.debug_enabled;
2833
2834 if g_debug then
2835 l_proc := g_package||'.get_mappingvalue_sum';
2836 hr_utility.set_location('Processing '||l_proc, 10);
2837 end if;
2838
2839
2840 -- chk to see if OTL is installed
2841
2842 t_consolidated_info.delete;
2843
2844 OPEN csr_chk_otl_installed;
2845
2846 FETCH csr_chk_otl_installed INTO l_installed;
2847
2848 IF ( csr_chk_otl_installed%FOUND )
2849 THEN
2850
2851
2852 if(p_field_name1 is not null) then
2853
2854 get_mapping_value(p_bld_blk_info_type,p_field_name1,l_segment1,l_bld_blk_info_type_id);
2855
2856 end if;
2857
2858
2859 if(p_field_name2 is not null) then
2860
2861 if(p_bld_blk_info_type2 is not null) then
2862
2863 get_mapping_value(p_bld_blk_info_type2,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
2864
2865 else
2866
2867 get_mapping_value(p_bld_blk_info_type,p_field_name2,l_segment2,l_bld_blk_info_type_id2);
2868 end if;
2869 end if;
2870
2871
2872
2873 if(p_field_name3 is not null) then
2874
2875 get_mapping_value(p_bld_blk_info_type3,p_field_name3,l_segment3,l_bld_blk_info_type_id3);
2876
2877 end if;
2878
2879 if(p_field_name4 is not null) then
2880
2881 get_mapping_value(p_bld_blk_info_type4,p_field_name4,l_segment4,l_bld_blk_info_type_id4);
2882
2883 end if;
2884
2885
2886 if(p_field_name5 is not null) then
2887
2888 get_mapping_value(p_bld_blk_info_type5,p_field_name5,l_segment5,l_bld_blk_info_type_id5);
2889
2890 end if;
2891
2892
2893
2894 if(p_field_name2 is not null) then
2895 if(t_consolidated_info.count>0) then
2896 l_cons_index:=t_consolidated_info.count+1;
2897 else
2898 l_cons_index:=1;
2899 end if;
2900
2901 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id2;
2902 t_consolidated_info(l_cons_index).field_name :=p_field_name2;
2903 t_consolidated_info(l_cons_index).field_value:=p_field_value2 ;
2904 t_consolidated_info(l_cons_index).segment:=l_segment2;
2905
2906 end if;
2907
2908 if(p_bld_blk_info_type3 is not null and p_field_name3 is not null) then
2909 if(t_consolidated_info.count>0) then
2910 l_cons_index:=t_consolidated_info.count+1;
2911 else
2912 l_cons_index:=1;
2913 end if;
2914
2915 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id3;
2916 t_consolidated_info(l_cons_index).field_name :=p_field_name3;
2917 t_consolidated_info(l_cons_index).field_value:=p_field_value3 ;
2918 t_consolidated_info(l_cons_index).segment:=l_segment3;
2919
2920 end if;
2921
2922 if(p_bld_blk_info_type4 is not null and p_field_name4 is not null) then
2923 if(t_consolidated_info.count>0) then
2924 l_cons_index:=t_consolidated_info.count+1;
2925 else
2926 l_cons_index:=1;
2927 end if;
2928
2929 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id4;
2930 t_consolidated_info(l_cons_index).field_name :=p_field_name4;
2931 t_consolidated_info(l_cons_index).field_value:=p_field_value4 ;
2932 t_consolidated_info(l_cons_index).segment:=l_segment4;
2933
2934 end if;
2935
2936 if(p_bld_blk_info_type5 is not null and p_field_name5 is not null) then
2937 if(t_consolidated_info.count>0) then
2938 l_cons_index:=t_consolidated_info.count+1;
2939 else
2940 l_cons_index:=1;
2941 end if;
2942
2943 t_consolidated_info(l_cons_index).bld_blk_info_type_id:=l_bld_blk_info_type_id5;
2944 t_consolidated_info(l_cons_index).field_name :=p_field_name5;
2945 t_consolidated_info(l_cons_index).field_value:=p_field_value5 ;
2946 t_consolidated_info(l_cons_index).segment:=l_segment5;
2947
2948 end if;
2949
2950
2951
2952
2953 if g_debug then
2954 hr_utility.set_location('Processing '||l_proc, 30);
2955 end if;
2956
2957 -- build dynamic SQL query
2958
2959 l_query := '
2960 select sum(ta.'||l_segment1||')
2961 from hxc_time_attributes ta,
2962 hxc_time_attribute_usages tau,
2963 hxc_time_building_blocks tbb
2964 where
2965 tbb.scope = :p_scope
2966 AND tbb.resource_id = :p_resource_id
2967 AND tbb.object_version_number = (
2968 SELECT MAX ( tbb1.object_version_number )
2969 FROM hxc_time_building_blocks tbb1
2970 WHERE tbb1.time_building_block_id = tbb.time_building_block_id )
2971 AND tau.time_building_block_id = tbb.time_building_block_id
2972 AND tau.time_building_block_ovn = tbb.object_version_number
2973 AND tau.time_attribute_id = ta.time_attribute_id
2974 AND tbb.date_to = hr_general.end_of_time
2975 And ta.bld_blk_info_type_id = :l_bld_blk_info_type_id';
2976
2977
2978 if g_debug then
2979 hr_utility.set_location('Processing '||l_proc, 30.01);
2980 end if;
2981 l_index:=t_consolidated_info.first;
2982
2983 loop exit when not t_consolidated_info.exists(l_index);
2984
2985
2986 l_bld_block_info_id_outer:=t_consolidated_info(l_index).bld_blk_info_type_id;
2987 l_field_name_outer:=t_consolidated_info(l_index).field_name;
2988 l_field_value_outer:=t_consolidated_info(l_index).field_value;
2989 l_segment_outer:=t_consolidated_info(l_index).segment;
2990
2991 if(l_field_value_outer is not null) then
2992
2993 l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
2994 hxc_time_attributes ta2
2995 where tau2.time_building_block_id = tbb.time_building_block_id
2996 AND tau2.time_building_block_ovn = tbb.object_version_number
2997 AND tau2.time_attribute_id = ta2.time_attribute_id
2998 AND ta2.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
2999 ' AND ta2.'||l_segment_outer||' = '''|| l_field_value_outer||'''' ;
3000
3001
3002 else
3003
3004
3005 l_field_value:= l_field_value||' AND EXISTS ( select 1 from hxc_time_attribute_usages tau2,
3006 hxc_time_attributes ta2
3007 where tau2.time_building_block_id = tbb.time_building_block_id
3008 AND tau2.time_building_block_ovn = tbb.object_version_number
3009 AND tau2.time_attribute_id = ta2.time_attribute_id
3010 AND ta2.bld_blk_info_type_id = '||l_bld_block_info_id_outer||
3011 ' AND ta2.'||l_segment_outer||' is null ';
3012
3013
3014 end if;
3015
3016 if g_debug then
3017 hr_utility.set_location('Processing '||l_proc, 30.02);
3018 end if;
3019
3020 l_index_inner:=t_consolidated_info.next(l_index);
3021
3022
3023 loop exit when not t_consolidated_info.exists(l_index_inner);
3024
3025 if (l_bld_block_info_id_outer=t_consolidated_info(l_index_inner).bld_blk_info_type_id) then
3026
3027 l_bld_block_info_id_inner:=t_consolidated_info(l_index_inner).bld_blk_info_type_id;
3028 l_field_name_inner:=t_consolidated_info(l_index_inner).field_name;
3029 l_field_value_inner:=t_consolidated_info(l_index_inner).field_value;
3030 l_segment_inner:=t_consolidated_info(l_index_inner).segment;
3031
3032 if(l_field_value_inner is not null) then
3033
3034 l_field_value:=l_field_value||' AND ta2.'||l_segment_inner||' = '''||l_field_value_inner||'''' ;
3035
3036 else
3037
3038 l_field_value:=l_field_value||' AND ta2.'||l_segment_inner||' is null ';
3039
3040 end if;
3041
3042 t_consolidated_info.delete(l_index_inner);
3043
3044 end if;
3045
3046
3047 l_index_inner:=t_consolidated_info.next(l_index_inner);
3048
3049 end loop;
3050 l_field_value:=l_field_value||')';
3051
3052 l_index:= t_consolidated_info.next(l_index);
3053
3054 end loop;
3055
3056 l_query:=l_query||l_field_value;
3057
3058 if g_debug then
3059 hr_utility.set_location('Processing '||l_proc, 30.1);
3060 end if;
3061
3062 --let us add the status check
3063 if p_status = 'WORKING' then
3064 l_status_list := '(''WORKING'''||','||'''SUBMITTED'''||','||'''APPROVED'')';
3065 elsif p_status = 'SUBMITTED' then
3066 l_status_list := '(''SUBMITTED'''||','||'''APPROVED'')';
3067 elsif p_status = 'APPROVED' then
3068 l_status_list := '(''APPROVED'')';
3069 end if;
3070
3071
3072 if g_debug then
3073 hr_utility.set_location('Processing '||l_proc, 30.2);
3074 end if;
3075 if p_status in ('WORKING','SUBMITTED','APPROVED') then
3076
3077 l_status := '
3078 AND exists (
3079 select ''Y''
3080 from hxc_time_building_blocks daybb,
3081 hxc_time_building_blocks timebb,
3082 hxc_timecard_summary time_status
3083 where tbb.parent_building_block_id = daybb.time_building_block_id
3084 and tbb.parent_building_block_ovn = daybb.object_version_number
3085 and daybb.parent_building_block_id = timebb.time_building_block_id
3086 and daybb.parent_building_block_ovn = timebb.object_version_number
3087 and time_status.timecard_id = timebb.time_building_block_id
3088 and time_status.approval_status IN '||l_status_list||' ) ';
3089
3090 l_query := l_query ||l_status;
3091
3092 end if;
3093
3094
3095 if g_debug then
3096 hr_utility.trace(' ');
3097 hr_utility.trace('Now let us print the query');
3098
3099 hr_utility.trace(substr(l_query,1,200));
3100 hr_utility.trace(substr(l_query,201,200));
3101 hr_utility.trace(substr(l_query,401,200));
3102 hr_utility.trace(substr(l_query,601,200));
3103 hr_utility.trace(substr(l_query,801,200));
3104 hr_utility.trace(substr(l_query,1001,200));
3105 hr_utility.trace(substr(l_query,1201,200));
3106 hr_utility.trace(substr(l_query,1401,200));
3107 hr_utility.trace(substr(l_query,1601,200));
3108 hr_utility.trace(substr(l_query,1801,200));
3109 hr_utility.trace(substr(l_query,2001,200));
3110 hr_utility.trace(substr(l_query,2201,200));
3111 hr_utility.trace(substr(l_query,2401,200));
3112 hr_utility.trace(substr(l_query,2601,200));
3113 hr_utility.trace(substr(l_query,2801,200));
3114 hr_utility.trace(substr(l_query,3001,200));
3115 hr_utility.trace(' ');
3116
3117 hr_utility.set_location('Processing '||l_proc, 40);
3118 end if;
3119
3120 OPEN map_cr FOR l_query USING 'DETAIL', p_resource_id,l_bld_blk_info_type_id;
3121
3122 FETCH map_cr INTO l_sum;
3123
3124 CLOSE map_cr;
3125
3126 if g_debug then
3127 hr_utility.set_location('Processing '||l_proc, 70);
3128 end if;
3129
3130
3131 END IF; -- IF ( csr_chk_otl_installed%FOUND )
3132
3133 CLOSE csr_chk_otl_installed;
3134
3135
3136 RETURN l_sum;
3137
3138 EXCEPTION
3139
3140 WHEN OTHERS then
3141
3142 if g_debug then
3143 hr_utility.trace('Error is '||substr(sqlerrm,1,200));
3144 end if;
3145 hr_utility.set_message(809, sqlerrm);
3146 hr_utility.raise_error;
3147
3148 END get_mappingvalue_sum;
3149
3150
3151 end hxc_mapping_utilities;