1 PACKAGE BODY hxc_timecard_block_utils AS
2 /* $Header: hxctcbkut.pkb 120.5 2006/03/29 12:54:44 jdupont noship $ */
3
4 g_package varchar2(25) := 'hxc_timecard_block_utils.';
5 g_timecard_index number;
6
7 e_no_existing_block exception;
8
9 Function any_new_blocks
10 (p_blocks in hxc_block_table_type)
11 return varchar2 is
12
13 l_new varchar2(3) := 'NO';
14 l_index number;
15 l_found boolean := false;
16
17 Begin
18
19 l_index := p_blocks.first;
20 Loop
21 Exit when ((not p_blocks.exists(l_index)) OR (l_found));
22
23 if(
24 (p_blocks(l_index).new = 'Y')
25 AND
26 (is_active_block(p_blocks(l_index)))
27 AND
28 (p_blocks(l_index).time_building_block_id > 0)
29 ) then
30
31 l_new := 'YES';
32 l_found := true;
33
34 end if;
35
36 l_index := p_blocks.next(l_index);
37 End Loop;
38
39 return l_new;
40
41 End;
42
43 Procedure initialize_timecard_index is
44
45 Begin
46
47 g_timecard_index := null;
48
49 End initialize_timecard_index;
50
51 FUNCTION find_active_timecard_index
52 (p_blocks in hxc_block_table_type)
53 RETURN number is
54
55 l_tc_index NUMBER := null;
56 l_index NUMBER;
57
58 l_proc varchar2(55) := g_package||'find_active_timecard_index';
59
60 BEGIN
61
62 if(g_timecard_index is not null) then
63 if(p_blocks.exists(g_timecard_index)) then
64 if(p_blocks(g_timecard_index).scope in (hxc_timecard.c_timecard_scope, hxc_timecard.c_template_scope))then
65 l_tc_index := g_timecard_index;
66 else
67 g_timecard_index := null;
68 end if;
69 else
70 g_timecard_index := null;
71 end if;
72 end if;
73
74 if(g_timecard_index is null) then
75
76 l_index := p_blocks.first;
77
78 LOOP
79 EXIT WHEN ((NOT p_blocks.exists(l_index)) OR (l_tc_index is NOT NULL));
80
81 if(
82 (p_blocks(l_index).scope in (hxc_timecard.c_timecard_scope, hxc_timecard.c_template_scope))
83 AND
84 (date_value(p_blocks(l_index).date_to) = hr_general.end_of_time)
85 ) then
86
87 l_tc_index := l_index;
88
89 end if;
90
91 l_index := p_blocks.next(l_index);
92
93 END LOOP;
94
95 g_timecard_index := l_tc_index;
96
97 end if; -- can we use the cached value?
98
99 if(l_tc_index is null) then
100 --
101 -- Most likely we are deleting the timecard.
102 -- just find the index corresponding to the
103 -- timecard scope or timecard template scope
104 -- block
105
106 l_index := p_blocks.first;
107
108 LOOP
109 EXIT WHEN ((NOT p_blocks.exists(l_index)) OR (l_tc_index is NOT NULL));
110
111 if(p_blocks(l_index).scope in (hxc_timecard.c_timecard_scope, hxc_timecard.c_template_scope))then
112
113 l_tc_index := l_index;
114
115 end if;
116
117 l_index := p_blocks.next(l_index);
118
119 END LOOP;
120
121 g_timecard_index := l_tc_index;
122
123 end if;
124
125 return l_tc_index;
126
127 END find_active_timecard_index;
128
129 FUNCTION convert_to_dpwr_blocks
130 (p_blocks in hxc_block_table_type
131 ) return hxc_self_service_time_deposit.timecard_info is
132
133 l_blocks hxc_self_service_time_deposit.timecard_info;
134 l_index NUMBER;
135
136 BEGIN
137
138 l_index := p_blocks.first;
139
140 LOOP
141
142 EXIT WHEN NOT p_blocks.exists(l_index);
143
144 l_blocks(l_index).TIME_BUILDING_BLOCK_ID := p_blocks(l_index).TIME_BUILDING_BLOCK_ID;
145 l_blocks(l_index).TYPE := p_blocks(l_index).TYPE;
146 l_blocks(l_index).MEASURE := p_blocks(l_index).MEASURE;
147 l_blocks(l_index).UNIT_OF_MEASURE := p_blocks(l_index).UNIT_OF_MEASURE;
148 l_blocks(l_index).START_TIME := date_value(p_blocks(l_index).START_TIME);
149 l_blocks(l_index).STOP_TIME := date_value(p_blocks(l_index).STOP_TIME);
150 l_blocks(l_index).PARENT_BUILDING_BLOCK_ID := p_blocks(l_index).PARENT_BUILDING_BLOCK_ID;
151 l_blocks(l_index).PARENT_IS_NEW := p_blocks(l_index).PARENT_IS_NEW;
152 l_blocks(l_index).SCOPE := p_blocks(l_index).SCOPE;
153 l_blocks(l_index).OBJECT_VERSION_NUMBER := p_blocks(l_index).OBJECT_VERSION_NUMBER;
154 l_blocks(l_index).APPROVAL_STATUS := p_blocks(l_index).APPROVAL_STATUS;
155 l_blocks(l_index).RESOURCE_ID := p_blocks(l_index).RESOURCE_ID;
156 l_blocks(l_index).RESOURCE_TYPE := p_blocks(l_index).RESOURCE_TYPE;
157 l_blocks(l_index).APPROVAL_STYLE_ID := p_blocks(l_index).APPROVAL_STYLE_ID;
158 l_blocks(l_index).DATE_FROM := date_value(p_blocks(l_index).DATE_FROM);
159 l_blocks(l_index).DATE_TO := date_value(p_blocks(l_index).DATE_TO);
160 l_blocks(l_index).COMMENT_TEXT := p_blocks(l_index).COMMENT_TEXT;
161 l_blocks(l_index).PARENT_BUILDING_BLOCK_OVN := p_blocks(l_index).PARENT_BUILDING_BLOCK_OVN;
162 l_blocks(l_index).NEW := p_blocks(l_index).NEW;
163 l_blocks(l_index).CHANGED := p_blocks(l_index).CHANGED;
164 l_blocks(l_index).PROCESS := p_blocks(l_index).PROCESS;
165
166 l_index := p_blocks.next(l_index);
167
168 END LOOP;
169
170 return l_blocks;
171
172 END convert_to_dpwr_blocks;
173
174 Function is_new_block
175 (p_block in HXC_BLOCK_TYPE)
176 return BOOLEAN is
177
178 Begin
179
180 if(p_block.new = 'Y') then
181 return true;
182 else
183 return false;
184 end if;
185
186 End is_new_block;
187
188 Function is_active_block
189 (p_block in HXC_BLOCK_TYPE)
190 return BOOLEAN is
191
192 Begin
193
194 if(fnd_date.canonical_to_date(p_block.date_to) = hr_general.end_of_time) then
195 return true;
196 else
197 return false;
198 end if;
199
200 End is_active_block;
201
202 Function is_timecard_block
203 (p_block in HXC_BLOCK_TYPE)
204 return BOOLEAN is
205 Begin
206
207 if(
208 (p_block.scope = hxc_timecard.c_timecard_scope)
209 OR
210 (p_block.scope = hxc_timecard.c_template_scope)
211 )then
212 return true;
213 else
214 return false;
215 end if;
216
217 End is_timecard_block;
218
219 Function is_day_block
220 (p_block in HXC_BLOCK_TYPE)
221 return BOOLEAN is
222 Begin
223
224 if(p_block.scope = hxc_timecard.c_day_scope) then
225 return true;
226 else
227 return false;
228 end if;
229
230 End is_day_block;
231
232 Function is_existing_block
233 (p_block in HXC_BLOCK_TYPE)
234 return BOOLEAN is
235
236 cursor c_existing
237 (p_block_id in hxc_time_building_blocks.time_building_block_id%type
238 ,p_block_ovn in hxc_time_building_blocks.object_version_number%type
239 ) is
240 select 'Y'
241 from hxc_time_building_blocks tbb
242 where tbb.time_building_block_id = p_block_id
243 and tbb.object_version_number = p_block_ovn;
244
245 l_dummy varchar2(1);
246
247 Begin
248
249 open c_existing(p_block.time_building_block_id,p_block.object_version_number);
250 fetch c_existing into l_dummy;
251 if(c_existing%notfound) then
252 close c_existing;
253 return false;
254 else
255 close c_existing;
256 return true;
257 end if;
258
259 End is_existing_block;
260
261 Function is_detail_block
262 (p_block in HXC_BLOCK_TYPE)
263 return BOOLEAN is
264 Begin
265
266 if(p_block.scope = hxc_timecard.c_detail_scope)then
267 return true;
268 else
269 return false;
270 end if;
271
272 End is_detail_block;
273
274 Function is_parent_block
275 (p_block in HXC_BLOCK_TYPE
276 ,p_parent_id in hxc_time_building_blocks.time_building_block_id%type
277 ,p_parent_ovn in hxc_time_building_blocks.object_version_number%type
278 ,p_check_id in boolean
279 ) return pls_integer is
280 Begin
281
282 if(
283 (p_block.parent_building_block_id = p_parent_id)
284 AND
285 (p_block.parent_building_block_ovn = p_parent_ovn)
286 ) then
287 return 0;
288 elsif(p_block.parent_building_block_id = p_parent_id) then
289 return 1;
290 else
291 return 2;
292 end if;
293
294 End is_parent_block;
295
296 Function is_parent_block
297 (p_block in HXC_BLOCK_TYPE
298 ,p_parent_id in hxc_time_building_blocks.time_building_block_id%type
299 ,p_parent_ovn in hxc_time_building_blocks.object_version_number%type
300 ) return BOOLEAN is
301
302 Begin
303 if(is_parent_block(p_block,p_parent_id,p_parent_ovn,true)=0) then
304 return true;
305 else
306 return false;
307 end if;
308 End is_parent_block;
309
310 Function is_updated_block
311 (p_block in HXC_BLOCK_TYPE)
312 return BOOLEAN is
313
314 l_prev_block hxc_block_type;
315
316 Begin
317
318 if(is_new_block(p_block)) then
319 if(p_block.changed = 'Y') then
320 return true;
321 else
322 return false;
323 end if;
324 else
325 l_prev_block := build_block(p_block.time_building_block_id,p_block.object_version_number);
326 if(blocks_are_different(p_block,l_prev_block)) then
327 return true;
328 else
329 -- 115.5 Change
330 -- We might be processing this block due to a change in the
331 -- attributes, so we should check the process flag as well
332 if(process_block(p_block)) then
333 return true;
334 else
335 return false;
336 end if;
337 -- end 115.5 Change
338 end if;
339 end if;
340
341 End is_updated_block;
342
343 Function parent_has_changed
344 (p_blocks in HXC_BLOCK_TABLE_TYPE
345 ,p_parent_block_id in hxc_time_building_blocks.time_building_block_id%type
346 ) return BOOLEAN is
347
348 l_index number;
349 l_parent_changed boolean := false;
350 l_parent_found boolean := false;
351
352 Begin
353
354 l_index := p_blocks.first;
355
356 Loop
357 Exit when ((not p_blocks.exists(l_index)) or (l_parent_found));
358 if(p_blocks(l_index).time_building_block_id = p_parent_block_id) then
359 if((p_blocks(l_index).changed = 'Y')OR(process_block(p_blocks(l_index)))) then
360 l_parent_changed := true;
361 end if;
362 l_parent_found := true;
363 end if;
364 l_index := p_blocks.next(l_index);
365 End loop;
366
367 return l_parent_changed;
368
369 End parent_has_changed;
370
371 Function process_block
372 (p_block in HXC_BLOCK_TYPE
373 ) return BOOLEAN is
374
375 Begin
376
377 if(p_block.process = hxc_timecard.c_process) then
378 return true;
379 else
380 return false;
381 end if;
382
383 End process_block;
384
385 Function can_process_block
386 (p_block in hxc_block_type
387 ) return boolean is
388
389 Begin
390
391 if(process_block(p_block)) then
392 return true;
393 else
394 if(is_new_block(p_block)) then
395 return false;
396 else
397 return true;
398 end if;
399 end if;
400
401 End can_process_block;
402
403 Function date_value
404 (p_block_value in varchar2
405 ) return date is
406
407 Begin
408
409 return fnd_date.canonical_to_date(p_block_value);
410
411 end date_value;
412
413 Function build_block
414 (p_time_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
415 ,p_time_building_block_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
416 ) return HXC_BLOCK_TYPE is
417
418 cursor c_block
419 (p_time_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
420 ,p_time_building_block_ovn in HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
421 ) is
422 select *
423 from hxc_time_building_blocks
424 where time_building_block_id = p_time_building_block_id
425 and object_version_number = p_time_building_block_ovn;
426
427
428 l_block c_block%ROWTYPE;
429 l_new_block HXC_BLOCK_TYPE;
430
431 BEGIN
432
433 open c_block(p_time_building_block_id,p_time_building_block_ovn);
434 fetch c_block into l_block;
435
436 if(c_block%FOUND) then
437
438 close c_block;
439
440 -- Convert to the type
441
442 l_new_block :=
443 HXC_BLOCK_TYPE
444 (l_block.TIME_BUILDING_BLOCK_ID
445 ,l_block.TYPE
446 ,l_block.MEASURE
447 ,l_block.UNIT_OF_MEASURE
448 ,fnd_date.date_to_canonical(l_block.START_TIME)
449 ,fnd_date.date_to_canonical(l_block.STOP_TIME)
450 ,l_block.PARENT_BUILDING_BLOCK_ID
451 ,'N' --l_block.PARENT_IS_NEW
452 ,l_block.SCOPE
453 ,l_block.OBJECT_VERSION_NUMBER
454 ,l_block.APPROVAL_STATUS
455 ,l_block.RESOURCE_ID
456 ,l_block.RESOURCE_TYPE
457 ,l_block.APPROVAL_STYLE_ID
458 ,fnd_date.date_to_canonical(l_block.DATE_FROM)
459 ,fnd_date.date_to_canonical(l_block.DATE_TO)
460 ,l_block.COMMENT_TEXT
461 ,l_block.PARENT_BUILDING_BLOCK_OVN
462 ,'N' --l_block.NEW
463 ,'N' --l_block.CHANGED
464 ,'N' --l_block.process
465 ,l_block.application_set_id
466 ,l_block.translation_display_key
467 );
468
469 else
470 --
471 -- No block with this id and ovn
472 --
473 close c_block;
474 raise e_no_existing_block;
475
476 end if;
477
478
479 return l_new_block;
480
481 END build_block;
482
483 Function blocks_are_different
484 (p_block1 in HXC_BLOCK_TYPE
485 ,p_block2 in HXC_BLOCK_TYPE
486 ) return boolean is
487
488 l_proc varchar2(70) := 'block_utils.blocks_are_different';
489
490 Begin
491
492 if(p_block1.scope = 'DETAIL') then
493
494 --
495 -- There is only a subset of things that
496 -- can be changed in the block, we
497 -- look for these things
498 --
499 -- 1. Measure
500 -- 2029550 Implementation
501 -- We need to consider 3 cases
502 -- compare a none null measure with a none null measure
503 -- compare a null measure with a none null measure
504 -- compare a none null measure with a null measure
505 if(p_block1.type='MEASURE' AND p_block2.type='MEASURE') then
506 if p_block1.measure <> p_block2.measure then
507 return true;
508 end if;
509 if nvl(p_block1.measure,0) <> p_block2.measure then
510 return true;
511 end if;
512 if p_block1.measure <> nvl(p_block2.measure,0) then
513 return true;
514 end if;
515 end if;
516 if(p_block1.type='RANGE' AND p_block2.type = 'RANGE') then
517 -- 2. Start Time
518 if(nvl(p_block1.start_time,'NULL') <> nvl(p_block2.start_time,'NULL')) then
519 return true;
520 end if;
521 -- 3. Stop Time
522 if(nvl(p_block1.stop_time,'NULL') <> nvl(p_block2.stop_time,'NULL')) then
523 return true;
524 end if;
525 end if;
526 -- 4. Comment
527 if(nvl(p_block1.comment_text,'NULL') <> nvl(p_block2.comment_text,'NULL')) then
528 return true;
529 end if;
530 -- 5. Approval Status
531 if(nvl(p_block1.approval_status,'NULL') <> nvl(p_block2.approval_status,'NULL')) then
532 return true;
533 end if;
534 -- 6. Unit of measure
535 if(nvl(p_block1.unit_of_measure,'NULL') <> nvl(p_block2.unit_of_measure,'NULL')) then
536 return true;
537 end if;
538 -- 7. Parent Building block OVN
539 -- Actually, this one won't work, since the parent OVN isn't updated yet.
540 -- Is this a problem?
541 if(nvl(p_block1.parent_building_block_ovn,0) <> nvl(p_block2.parent_building_block_ovn,0)) then
542 return true;
543 end if;
544 -- 8. Date to
545 if(nvl(p_block1.date_to,hr_general.end_of_time) <> nvl(p_block2.date_to,hr_general.end_of_time)) then
546 return true;
547 end if;
548 -- 9. Type
549 if(nvl(p_block1.type,'RANGE') <> nvl(p_block2.type,'RANGE')) then
550 return true;
551 end if;
552 -- 10. Approval style id
553 if(nvl(p_block1.approval_style_id,1) <> nvl(p_block2.approval_style_id,1)) then
554 return true;
555 end if;
556 -- 11. Translation Display Key
557 if(nvl(p_block1.translation_display_key,'NULL') <> nvl(p_block2.translation_display_key,'NULL')) then
558 return true;
559 end if;
560 elsif((p_block1.scope=hxc_timecard.c_timecard_scope)OR(p_block1.scope=hxc_timecard.c_template_scope)) then
561 -- 1. Comment
562 if(nvl(p_block1.comment_text,'NULL') <> nvl(p_block2.comment_text,'NULL')) then
563 return true;
564 end if;
565 -- 2. Approval Status
566 if(nvl(p_block1.approval_status,'NULL') <> nvl(p_block2.approval_status,'NULL')) then
567 return true;
568 end if;
569 -- 3. Date to
570 if(nvl(p_block1.date_to,hr_general.end_of_time) <> nvl(p_block2.date_to,hr_general.end_of_time)) then
571 return true;
572 end if;
573 -- 4. Approval style id
574 if(nvl(p_block1.approval_style_id,1) <> nvl(p_block2.approval_style_id,1)) then
575 return true;
576 end if;
577 elsif(p_block1.scope='DAY') then
578 -- 1. Approval Status
579 if(nvl(p_block1.approval_status,'NULL') <> nvl(p_block2.approval_status,'NULL')) then
580 return true;
581 end if;
582 -- 2. Date to
583 if(nvl(p_block1.date_to,hr_general.end_of_time) <> nvl(p_block2.date_to,hr_general.end_of_time)) then
584 return true;
585 end if;
586 -- 3. Approval style id
587 if(nvl(p_block1.approval_style_id,1) <> nvl(p_block2.approval_style_id,1)) then
588 return true;
589 end if;
590 end if;
591
592 --
593 -- If we get here, the blocks are (at least in terms of the user) the same
594 --
595 return false;
596
597 End blocks_are_different;
598
599 Procedure sort_blocks
600 (p_blocks in HXC_BLOCK_TABLE_TYPE
601 ,p_timecard_blocks out nocopy HXC_TIMECARD.BLOCK_LIST
602 ,p_day_blocks out nocopy HXC_TIMECARD.BLOCK_LIST
603 ,p_detail_blocks out nocopy HXC_TIMECARD.BLOCK_LIST
604 ) is
605
606 l_block HXC_BLOCK_TYPE;
607 l_index NUMBER;
608
609 Begin
610
611 l_index := p_blocks.first;
612 LOOP
613 EXIT WHEN NOT p_blocks.exists(l_index);
614 l_block := p_blocks(l_index);
615
616 if(is_timecard_block(l_block)) then
617 --
618 -- 115.3 Change. In cases where we have more than one
619 -- timecard block, one will always be end dated, the other
620 -- new. In this case we must process the deleted one first
621 -- which means we must place the timecard blocks in the
622 -- sorted arrays in reverse order of id.
623 --
624 p_timecard_blocks((-1*l_block.time_building_block_id)) := l_index;
625
626 elsif(is_day_block(l_block)) then
627
628 p_day_blocks(l_block.time_building_block_id) := l_index;
629
630 elsif(is_detail_block(l_block)) then
631
632 p_detail_blocks(l_block.time_building_block_id) := l_index;
633
634 end if;
635 l_index := p_blocks.next(l_index);
636 END LOOP;
637
638 End sort_blocks;
639
640 Function next_block_id
641 (p_blocks in HXC_BLOCK_TABLE_TYPE
642 ) return number is
643
644 l_index number;
645 l_bb_id number := -2;
646
647 Begin
648
649 l_index := p_blocks.first;
650
651 Loop
652 Exit when not p_blocks.exists(l_index);
653
654 if(p_blocks(l_index).time_building_block_id < l_bb_id) then
655 l_bb_id := p_blocks(l_index).time_building_block_id;
656 end if;
657 l_index := p_blocks.next(l_index);
658 End Loop;
659
660 -- return large negative number to work round strange Santos
661 -- issue.
662
663 return (l_bb_id-100);
664
665 End next_block_id;
666
667 END hxc_timecard_block_utils;
[Home] [Help]