[Home] [Help]
PACKAGE BODY: APPS.HXC_BUILDING_BLOCK_API
Source
1 package body HXC_BUILDING_BLOCK_API as
2 /* $Header: hxctbbapi.pkb 120.5.12000000.2 2007/05/16 10:19:04 rchennur noship $ */
3
4 g_package varchar2(33) := ' hxc_building_block_api.';
5
6 g_debug boolean := hr_utility.debug_enabled;
7
8 -- ---------------------------------------------------------------------------
9 -- |-------------------< copyAttributesToBlock >-----------------------------|
10 -- ---------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This procedure copies the set of attributes used by one block, specified
15 -- by the time building block and object version number, to another block.
16 -- New attributes are not generated, since after the consolidation of
17 -- attributes work, this is not necessary. This procedure is currently used
18 -- by the delete interface, to copy the attributes from the block being
19 -- deleted to the deleted entry.
20 --
21 -- Prerequisites:
22 -- Valid block id and object version number references must be passed.
23 --
24 -- In Parameters:
25 -- Name Reqd Type Description
26 -- p_from_block_id Y Number Time building block id
27 -- p_from_block_ovn Y Number Time building block ovn
28 -- p_to_block_id Y Number Time building block id
29 -- p_to_block_ovn Y Number Time building block ovn
30 --
31 -- Out Parameters:
32 -- Name Reqd Type Description
33 --
34 -- Post Success:
35 -- All non-REASON (CLA) attributes are copied to the 'to' block.
36 --
37 -- Post Failure:
38 -- This function does not fail, however the attributes will not be copied
39 -- if any of the parameters are invalid.
40 --
41 -- Access Status:
42 -- Private.
43 --
44 -- {End Of Comments}
45 --
46 procedure copyAttributesToBlock
47 (p_from_block_id hxc_time_building_blocks.time_building_block_id%type,
48 p_from_block_ovn hxc_time_building_blocks.object_version_number%type,
49 p_to_block_id hxc_time_building_blocks.time_building_block_id%type,
50 p_to_block_ovn hxc_time_building_blocks.object_version_number%type
51 ) is
52
53 cursor c_attributes
54 (p_block_id hxc_time_building_blocks.time_building_block_id%type,
55 p_block_ovn hxc_time_building_blocks.object_version_number%type) is
56 select tau.time_attribute_id,
57 tbb.data_set_id
58 from hxc_time_attribute_usages tau,
59 hxc_time_attributes ta,
60 hxc_time_building_blocks tbb
61 where tau.time_building_block_id = p_block_id
62 and tau.time_building_block_ovn = p_block_ovn
63 and tau.time_building_block_id = tbb.time_building_block_id
64 and tau.time_building_block_ovn = tbb.object_version_number
65 and tau.time_attribute_id = ta.time_attribute_id
66 and ta.attribute_category <> 'REASON';
67
68 begin
69 for attribute_record in c_attributes(p_from_block_id,p_from_block_ovn) loop
70 -- Create the usage for the new block for this attribute
71 insert into hxc_time_attribute_usages
72 (time_attribute_usage_id,
73 time_attribute_id,
74 time_building_block_id,
75 created_by,
76 creation_date,
77 last_updated_by,
78 last_update_date,
79 last_update_login,
80 object_version_number,
81 time_building_block_ovn,
82 data_set_id
83 ) values (hxc_time_attribute_usages_s.nextval,
84 attribute_record.time_attribute_id,
85 p_to_block_id,
86 null, -- WHO trigger sets
87 null, -- WHO trigger sets
88 null, -- WHO trigger sets
89 null, -- WHO trigger sets
90 null, -- WHO trigger sets
91 1, -- Insert!
92 p_to_block_ovn,
93 attribute_record.data_set_id
94 );
95 end loop; -- Attribute loop
96
97 end copyAttributesToBlock;
98
99 -- ---------------------------------------------------------------------------
100 -- |-------------------< create_building_block >-----------------------------|
101 -- ---------------------------------------------------------------------------
102
103 procedure create_building_block
104 (p_validate in boolean default false
105 ,p_effective_date in date
106 ,p_type in varchar2
107 ,p_measure in number
108 ,p_unit_of_measure in varchar2
109 ,p_start_time in date
110 ,p_stop_time in date
111 ,p_parent_building_block_id in number
112 ,p_parent_building_block_ovn in number
113 ,p_scope in varchar2
114 ,p_approval_style_id in number
115 ,p_approval_status in varchar2
116 ,p_resource_id in number
117 ,p_resource_type in varchar2
118 ,p_comment_text in varchar2
119 ,p_application_set_id in number
120 ,p_translation_display_key in varchar2
121 ,p_time_building_block_id in out nocopy number
122 ,p_object_version_number in out nocopy number
123 ) is
124
125 cursor c_latest_version is
126 select max(object_version_number)
127 from hxc_time_building_blocks
128 where time_building_block_id = p_time_building_block_id
129 group by time_building_block_id;
130
131 cursor c_get_tc_data_set_id (p_time_building_block_id number) is
132 select data_set_id
133 from hxc_time_building_blocks
134 where time_building_block_id = p_time_building_block_id
135 and date_to = hr_general.end_of_time;
136
137 cursor c_get_tc_range_data_set_id (p_stop_time date) is
138 select data_set_id
139 from hxc_data_sets
140 where p_stop_time between start_date and end_date
141 and status = 'ON_LINE';
142
143 cursor c_day_det_range_data_set_id (p_parent_building_block_id number
144 ,p_parent_building_block_ovn number
145 ) is
146 select tbb.data_set_id
147 from hxc_time_building_blocks tbb
148 where tbb.time_building_block_id = p_parent_building_block_id
149 and tbb.object_version_number = p_parent_building_block_ovn;
150
151
152
153 l_data_set_id hxc_time_building_blocks.data_set_id%type;
154 l_proc varchar2(72);
155 l_object_version_number hxc_time_building_blocks.object_version_number%type;
156 l_time_building_block_id hxc_time_building_blocks.time_building_block_id%type;
157 l_max_ovn hxc_time_building_blocks.object_version_number%type;
158
159 e_no_ovn exception;
160
161 begin
162
163 g_debug := hr_utility.debug_enabled;
164
165 if g_debug then
166 l_proc := g_package||'create_building_block';
167 hr_utility.set_location('Entering:'|| l_proc, 10);
168 end if;
169
170 -- issue a savepoint
171 savepoint create_building_block;
172
173 if p_approval_status = 'SUBMITTED' then
174 -- the user is submitting the timecard for approval, so call the
175 -- additional validation from the recipient applications
176
177 begin
178
179 hxc_building_block_api_bk1.create_building_block_b
180 (p_effective_date => p_effective_date
181 ,p_type => p_type
182 ,p_measure => p_measure
183 ,p_unit_of_measure => p_unit_of_measure
184 ,p_start_time => p_start_time
185 ,p_stop_time => p_stop_time
186 ,p_parent_building_block_id => p_parent_building_block_id
187 ,p_parent_building_block_ovn => p_parent_building_block_ovn
188 ,p_scope => p_scope
189 ,p_approval_style_id => p_approval_style_id
190 ,p_approval_status => p_approval_status
191 ,p_resource_id => p_resource_id
192 ,p_resource_type => p_resource_type
193 ,p_comment_text => p_comment_text
194 ,p_application_set_id => p_application_set_id
195 ,p_translation_display_key => p_translation_display_key
196 );
197
198 exception
199 when hr_api.cannot_find_prog_unit then
200 hr_api.cannot_find_prog_unit_error
201 (p_module_name => 'create_building_block'
202 ,p_hook_type => 'BP'
203 );
204 end;
205
206 end if;
207
208 if p_time_building_block_id is not null then
209
210 -- we are date-effectively updating, so end date the previous
211 -- row before inserting a new one
212 hxc_tbb_shd.lck
213 (p_time_building_block_id => p_time_building_block_id
214 ,p_object_version_number => p_object_version_number
215 );
216
217 open c_get_tc_data_set_id(p_time_building_block_id);
218 fetch c_get_tc_data_set_id into l_data_set_id;
219 close c_get_tc_data_set_id;
220
221 update hxc_time_building_blocks
222 set date_to = decode(trunc(date_from)
223 ,trunc(p_effective_date)
224 ,p_effective_date
225 ,p_effective_date - 1)
226 where time_building_block_id = p_time_building_block_id
227 and date_to = hr_general.end_of_time;
228
229 hxc_tbb_ins.ins
230 (p_effective_date => p_effective_date
231 ,p_type => p_type
232 ,p_scope => p_scope
233 ,p_approval_status => p_approval_status
234 ,p_measure => p_measure
235 ,p_unit_of_measure => p_unit_of_measure
236 ,p_start_time => p_start_time
237 ,p_stop_time => p_stop_time
238 ,p_parent_building_block_id => p_parent_building_block_id
239 ,p_parent_building_block_ovn => p_parent_building_block_ovn
240 ,p_resource_id => p_resource_id
241 ,p_resource_type => p_resource_type
242 ,p_approval_style_id => p_approval_style_id
243 ,p_date_from => p_effective_date
244 ,p_date_to => hr_general.end_of_time
245 ,p_comment_text => p_comment_text
246 ,p_application_set_id => p_application_set_id
247 ,p_data_set_id => l_data_set_id
248 ,p_translation_display_key => p_translation_display_key
249 ,p_time_building_block_id => l_time_building_block_id
250 ,p_object_version_number => l_object_version_number
251 );
252
253 -- get the most recent object_version_number of this building block
254 open c_latest_version;
255 fetch c_latest_version into l_max_ovn;
256 if c_latest_version%found then
257
258 -- set the true object_version_number and time_building_block_id
259 -- for the row we just inserted
260 update hxc_time_building_blocks
261 set object_version_number = l_max_ovn + 1,
262 time_building_block_id = p_time_building_block_id
263 where time_building_block_id = l_time_building_block_id
264 and date_from = p_effective_date
265 and date_to = hr_general.end_of_time;
266
267 l_object_version_number := l_max_ovn + 1;
268 --
269 -- ARR 115.6
270 -- Must not only set object version number, but the id as well
271 -- since we overwrite the id we first created.
272 --
273 l_time_building_block_id := p_time_building_block_id;
274
275 -- set the parent_building_block_ovn to l_object_version_number for
276 -- the child records of the row we just inserted.
277
278 /*
279 We shouldn't be doing this!!
280
281 update hxc_time_building_blocks
282 set parent_building_block_ovn = l_object_version_number
283 where parent_building_block_id = p_time_building_block_id;
284
285 */
286 else
287
288 raise e_no_ovn;
289
290 end if;
291
292 else
293
294 --new bb
295 --let us check the scope
296
297 if p_scope ='TIMECARD' then
298
299 open c_get_tc_range_data_set_id(p_stop_time);
300 fetch c_get_tc_range_data_set_id into l_data_set_id;
301 close c_get_tc_range_data_set_id;
302 elsif p_scope in ('DAY','DETAIL') then
303
304 open c_day_det_range_data_set_id(p_parent_building_block_id
305 ,p_parent_building_block_ovn
306 );
307 fetch c_day_det_range_data_set_id into l_data_set_id;
308 close c_day_det_range_data_set_id;
309 elsif p_scope='APPLICATION_PERIOD' then
310 l_data_set_id:=null;
311 end if;
312
313
314 -- call the row handler
315 hxc_tbb_ins.ins
316 (p_effective_date => p_effective_date
317 ,p_type => p_type
318 ,p_scope => p_scope
319 ,p_approval_status => p_approval_status
320 ,p_measure => p_measure
321 ,p_unit_of_measure => p_unit_of_measure
322 ,p_start_time => p_start_time
323 ,p_stop_time => p_stop_time
324 ,p_parent_building_block_id => p_parent_building_block_id
325 ,p_parent_building_block_ovn => p_parent_building_block_ovn
326 ,p_resource_id => p_resource_id
327 ,p_resource_type => p_resource_type
328 ,p_approval_style_id => p_approval_style_id
329 ,p_date_from => p_effective_date
330 ,p_date_to => hr_general.end_of_time
331 ,p_comment_text => p_comment_text
332 ,p_application_set_id => p_application_set_id
333 ,p_data_set_id => l_data_set_id
334 ,p_translation_display_key => p_translation_display_key
335 ,p_time_building_block_id => l_time_building_block_id
336 ,p_object_version_number => l_object_version_number
337 );
338
339 end if;
340
341 begin
342
343 -- call after process user hook
344 hxc_building_block_api_bk1.create_building_block_a
345 (p_effective_date => p_effective_date
346 ,p_type => p_type
347 ,p_measure => p_measure
348 ,p_unit_of_measure => p_unit_of_measure
349 ,p_start_time => p_start_time
350 ,p_stop_time => p_stop_time
351 ,p_parent_building_block_id => p_parent_building_block_id
352 ,p_parent_building_block_ovn => p_parent_building_block_ovn
353 ,p_scope => p_scope
354 ,p_approval_style_id => p_approval_style_id
355 ,p_approval_status => p_approval_status
356 ,p_resource_id => p_resource_id
357 ,p_resource_type => p_resource_type
358 ,p_comment_text => p_comment_text
359 ,p_time_building_block_id => l_time_building_block_id
360 ,p_object_version_number => l_object_version_number
361 ,p_application_set_id => p_application_set_id
362 ,p_translation_display_key => p_translation_display_key
366 when hr_api.cannot_find_prog_unit then
363 );
364
365 exception
367 hr_api.cannot_find_prog_unit_error
368 (p_module_name => 'create_building_block'
369 ,p_hook_type => 'AP'
370 );
371 end;
372
373 if p_validate then
374 raise hr_api.validate_enabled;
375 end if;
376
377 -- set out parameters
378 p_object_version_number := l_object_version_number;
379 p_time_building_block_id := l_time_building_block_id;
380
381 if g_debug then
382 hr_utility.set_location(' Leaving:'||l_proc, 20);
383 end if;
384
385 exception
386 when hr_api.validate_enabled then
387 rollback to create_building_block;
388 when others then
389 raise;
390
391 end create_building_block;
392
393 -- 115.16 start kSethi
394 -- New proc create_reversing_entry, used only for ELP
395 -- reversing entry.
396 --
397 -- ---------------------------------------------------------------------------
398 -- |-------------------< create_reversing_entry >-----------------------------|
399 -- ---------------------------------------------------------------------------
400
401 procedure create_reversing_entry
402 (p_validate in boolean default false
403 ,p_effective_date in date
404 ,p_type in varchar2
405 ,p_measure in number
406 ,p_unit_of_measure in varchar2
407 ,p_start_time in date
408 ,p_stop_time in date
409 ,p_parent_building_block_id in number
410 ,p_parent_building_block_ovn in number
411 ,p_scope in varchar2
412 ,p_approval_style_id in number
413 ,p_approval_status in varchar2
414 ,p_resource_id in number
415 ,p_resource_type in varchar2
416 ,p_comment_text in varchar2
417 ,p_application_set_id in number
418 ,p_date_to in date
419 ,p_translation_display_key in varchar2
420 ,p_time_building_block_id in out nocopy number
421 ,p_object_version_number in out nocopy number
422 ) is
423
424 cursor c_latest_version is
425 select max(object_version_number)
426 from hxc_time_building_blocks
427 where time_building_block_id = p_time_building_block_id
428 group by time_building_block_id;
429
430 cursor c_get_parent_data_set_id (p_parent_building_block_id number
431 ,p_parent_building_block_ovn number
432 ) is
433 select tbb.data_set_id
434 from hxc_time_building_blocks tbb
435 where tbb.time_building_block_id = p_parent_building_block_id
436 and tbb.object_version_number = p_parent_building_block_ovn;
437
438
439
440 l_data_set_id hxc_time_building_blocks.data_set_id%type;
441
442 l_proc varchar2(72);
443 l_object_version_number hxc_time_building_blocks.object_version_number%type;
444 l_time_building_block_id hxc_time_building_blocks.time_building_block_id%type;
445 l_max_ovn hxc_time_building_blocks.object_version_number%type;
446
447 e_no_ovn exception;
448
449 begin
450
451 g_debug := hr_utility.debug_enabled;
452
453 if g_debug then
454 l_proc := g_package||'create_reversing_entry';
455 hr_utility.set_location('Entering:'|| l_proc, 10);
456 end if;
457
458 -- issue a savepoint
459 savepoint create_reversing_entry;
460
461 if p_approval_status = 'SUBMITTED' then
462 -- the user is submitting the timecard for approval, so call the
463 -- additional validation from the recipient applications
464
465 --new bb
466 --let us check the scope
467
468 open c_get_parent_data_set_id(p_parent_building_block_id
469 ,p_parent_building_block_ovn
470 );
471 fetch c_get_parent_data_set_id into l_data_set_id;
472 close c_get_parent_data_set_id;
473
474 begin
475
476 hxc_building_block_api_bk1.create_building_block_b
477 (p_effective_date => p_effective_date
478 ,p_type => p_type
479 ,p_measure => p_measure
480 ,p_unit_of_measure => p_unit_of_measure
481 ,p_start_time => p_start_time
482 ,p_stop_time => p_stop_time
483 ,p_parent_building_block_id => p_parent_building_block_id
484 ,p_parent_building_block_ovn => p_parent_building_block_ovn
485 ,p_scope => p_scope
486 ,p_approval_style_id => p_approval_style_id
487 ,p_approval_status => p_approval_status
488 ,p_resource_id => p_resource_id
489 ,p_resource_type => p_resource_type
490 ,p_comment_text => p_comment_text
491 ,p_application_set_id => p_application_set_id
492 ,p_translation_display_key => p_translation_display_key
493 );
494
495 exception
496 when hr_api.cannot_find_prog_unit then
497 hr_api.cannot_find_prog_unit_error
498 (p_module_name => 'create_building_block'
502
499 ,p_hook_type => 'BP'
500 );
501 end;
503 end if;
504
505 -- call the row handler
506 hxc_tbb_ins.ins
507 (p_effective_date => p_effective_date
508 ,p_type => p_type
509 ,p_scope => p_scope
510 ,p_approval_status => p_approval_status
511 ,p_measure => p_measure
512 ,p_unit_of_measure => p_unit_of_measure
513 ,p_start_time => p_start_time
514 ,p_stop_time => p_stop_time
515 ,p_parent_building_block_id => p_parent_building_block_id
516 ,p_parent_building_block_ovn => p_parent_building_block_ovn
517 ,p_resource_id => p_resource_id
518 ,p_resource_type => p_resource_type
519 ,p_approval_style_id => p_approval_style_id
520 ,p_date_from => p_effective_date
521 ,p_date_to => p_date_to
522 ,p_comment_text => p_comment_text
523 ,p_application_set_id => p_application_set_id
524 ,p_data_set_id => l_data_set_id
525 ,p_translation_display_key => p_translation_display_key
526 ,p_time_building_block_id => l_time_building_block_id
527 ,p_object_version_number => l_object_version_number
528 );
529
530
531 begin
532
533 -- call after process user hook
534 hxc_building_block_api_bk1.create_building_block_a
535 (p_effective_date => p_effective_date
536 ,p_type => p_type
537 ,p_measure => p_measure
538 ,p_unit_of_measure => p_unit_of_measure
539 ,p_start_time => p_start_time
540 ,p_stop_time => p_stop_time
541 ,p_parent_building_block_id => p_parent_building_block_id
542 ,p_parent_building_block_ovn => p_parent_building_block_ovn
543 ,p_scope => p_scope
544 ,p_approval_style_id => p_approval_style_id
545 ,p_approval_status => p_approval_status
546 ,p_resource_id => p_resource_id
547 ,p_resource_type => p_resource_type
548 ,p_comment_text => p_comment_text
549 ,p_time_building_block_id => l_time_building_block_id
550 ,p_object_version_number => l_object_version_number
551 ,p_application_set_id => p_application_set_id
552 ,p_translation_display_key => p_translation_display_key
553 );
554
555 exception
556 when hr_api.cannot_find_prog_unit then
557 hr_api.cannot_find_prog_unit_error
558 (p_module_name => 'create_building_block'
559 ,p_hook_type => 'AP'
560 );
561 end;
562
563 if p_validate then
564 raise hr_api.validate_enabled;
565 end if;
566
567 -- set out parameters
568 p_object_version_number := l_object_version_number;
569 p_time_building_block_id := l_time_building_block_id;
570
571 if g_debug then
572 hr_utility.set_location(' Leaving:'||l_proc, 20);
573 end if;
574
575 exception
576 when hr_api.validate_enabled then
577 rollback to create_reversing_entry;
578 when others then
579 raise;
580
581 end create_reversing_entry;
582 --
583 --
584
585 -- ---------------------------------------------------------------------------
586 -- |-------------------< update_building_block >-----------------------------|
587 -- ---------------------------------------------------------------------------
588
589 procedure update_building_block
590 (p_validate in boolean default false
591 ,p_effective_date in date
592 ,p_type in varchar2
593 ,p_measure in number
594 ,p_unit_of_measure in varchar2
595 ,p_start_time in date
596 ,p_stop_time in date
597 ,p_parent_building_block_id in number
598 ,p_parent_building_block_ovn in number
599 ,p_scope in varchar2
600 ,p_approval_style_id in number
601 ,p_approval_status in varchar2
602 ,p_resource_id in number
603 ,p_resource_type in varchar2
604 ,p_comment_text in varchar2
605 ,p_time_building_block_id in number
606 ,p_application_set_id in number
607 ,p_translation_display_key in varchar2
608 ,p_object_version_number in out nocopy number
609 ) is
610
611 l_proc varchar2(72);
612 l_object_version_number hxc_time_building_blocks.object_version_number%type;
613
614 cursor c_get_data_set_id(p_tbb_id number,p_tbb_ovn number)
615 is
616 select tbb.data_set_id
617 from hxc_time_building_blocks tbb
618 where tbb.time_building_block_id = p_tbb_id
619 and tbb.object_version_number = p_tbb_ovn;
620
621 l_data_set_id hxc_data_sets.data_set_id%TYPE;
622
623 begin
624
625 g_debug := hr_utility.debug_enabled;
626
627 if g_debug then
628 l_proc := g_package||'update_building_block';
629 hr_utility.set_location('Entering:'|| l_proc, 10);
630 end if;
631
635 open c_get_data_set_id(p_time_building_block_id,p_object_version_number);
632 -- issue a savepoint
633 savepoint update_building_block;
634
636 fetch c_get_data_set_id into l_data_set_id;
637 close c_get_data_set_id;
638
639
640 -- call the row handler
641 hxc_tbb_upd.upd
642 (p_effective_date => p_effective_date
643 ,p_approval_status => p_approval_status
644 ,p_measure => p_measure
645 ,p_unit_of_measure => p_unit_of_measure
646 ,p_start_time => p_start_time
647 ,p_stop_time => p_stop_time
648 ,p_parent_building_block_id => p_parent_building_block_id
649 ,p_parent_building_block_ovn => p_parent_building_block_ovn
650 ,p_approval_style_id => p_approval_style_id
651 ,p_date_from => null
652 ,p_date_to => null
653 ,p_comment_text => p_comment_text
654 ,p_data_set_id => l_data_set_id
655 ,p_time_building_block_id => p_time_building_block_id
656 ,p_application_set_id => p_application_set_id
657 ,p_translation_display_key => p_translation_display_key
658 ,p_object_version_number => l_object_version_number
659 );
660
661 if p_validate then
662 raise hr_api.validate_enabled;
663 end if;
664
665 -- set out parameters
666 p_object_version_number := l_object_version_number;
667
668 if g_debug then
669 hr_utility.set_location(' Leaving:'||l_proc, 20);
670 end if;
671
672 exception
673 when hr_api.validate_enabled then
674 rollback to update_building_block;
675 when others then
676 raise;
677
678 end update_building_block;
679
680
681 -- ---------------------------------------------------------------------------
682 -- |-------------------< delete_building_block >-----------------------------|
683 -- ---------------------------------------------------------------------------
684
685 procedure delete_building_block
686 (p_validate in boolean default false,
687 p_object_version_number in out nocopy number,
688 p_time_building_block_id in number,
689 p_effective_date in date,
690 p_application_set_id in number
691 ) is
692
693 cursor c_old_rec is
694 select *
695 from hxc_time_building_blocks
696 where time_building_block_id = p_time_building_block_id
697 and date_to = hr_general.end_of_time;
698
699 cursor c_latest_bb_version is
700 select max(object_version_number)
701 from hxc_time_building_blocks
702 where time_building_block_id = p_time_building_block_id
703 group by time_building_block_id;
704
705 cursor c_time_attribute_usage(p_ovn number, p_building_block_id number) is
706 SELECT htau.time_attribute_id time_attribute_id
710 AND htau.time_building_block_id = p_building_block_id
707 FROM hxc_time_attribute_usages htau ,
708 hxc_time_attributes hta
709 WHERE htau.time_building_block_ovn = p_ovn
711 AND hta.time_attribute_id = htau.time_attribute_id
712 AND hta.ATTRIBUTE_CATEGORY <> 'REASON';
713
714 l_proc varchar2(72);
715 l_object_version_number hxc_time_building_blocks.object_version_number%type;
716 l_time_building_block_id number;
717 l_time_record hxc_time_building_blocks%rowtype;
718 l_max_ovn number;
719 l_time_attribute_usage_id number;
720 l_time_attribute_id number;
721 l_status hxc_time_building_blocks.approval_status%TYPE;
722
723 begin
724
725 g_debug := hr_utility.debug_enabled;
726
727 if g_debug then
728 l_proc := g_package||'delete_building_block';
729 hr_utility.set_location('Entering:'|| l_proc, 10);
730 end if;
731
732 -- issue a savepoint
733 savepoint delete_building_block;
734
735 -- call before process user hook
736 begin
737
738 hxc_building_block_api_bk3.delete_building_block_b
739 (p_effective_date => p_effective_date);
740
741 exception
742 when hr_api.cannot_find_prog_unit then
743 hr_api.cannot_find_prog_unit_error
744 (p_module_name => 'delete_building_block',
745 p_hook_type => 'BP'
746 );
747 end;
748
749 open c_old_rec;
750 fetch c_old_rec into l_time_record;
751 if c_old_rec%found then
752 close c_old_rec;
753 update hxc_time_building_blocks
754 set date_to = p_effective_date
755 where time_building_block_id = p_time_building_block_id
756 and date_to = hr_general.end_of_time;
757
758 l_time_building_block_id := null;
759
760 if(l_time_record.scope = 'DETAIL' )then
761 l_status := 'SUBMITTED';
762 else
763 l_status := l_time_record.approval_status;
764 end if;
765
766 hxc_tbb_ins.ins
767 (p_effective_date => p_effective_date,
768 p_type => l_time_record.type,
769 p_scope => l_time_record.scope,
770 p_approval_status => l_status,
771 p_measure => l_time_record.measure,
772 p_unit_of_measure => l_time_record.unit_of_measure,
773 p_start_time => l_time_record.start_time,
774 p_stop_time => l_time_record.stop_time,
775 p_parent_building_block_id => l_time_record.parent_building_block_id,
776 p_parent_building_block_ovn => l_time_record.parent_building_block_ovn,
777 p_resource_id => l_time_record.resource_id,
778 p_resource_type => l_time_record.resource_type,
779 p_approval_style_id => l_time_record.approval_style_id,
780 p_date_from => p_effective_date,
781 p_date_to => hr_general.end_of_time,
782 p_comment_text => l_time_record.comment_text,
783 p_application_set_id => NVL(p_application_set_id, l_time_record.application_set_id),
784 p_data_set_id => l_time_record.data_set_id,
785 p_translation_display_key => l_time_record.translation_display_key,
786 p_time_building_block_id => l_time_building_block_id,
787 p_object_version_number => l_object_version_number
788 );
789
790 open c_latest_bb_version;
791 fetch c_latest_bb_version into l_max_ovn;
792 close c_latest_bb_version;
793
794 -- set the true object_version_number, time_building_block_id
795 -- and date_to for the row we just inserted
796 update hxc_time_building_blocks
797 set object_version_number = l_max_ovn + 1,
798 time_building_block_id = p_time_building_block_id,
799 date_to = p_effective_date
800 where time_building_block_id = l_time_building_block_id
801 and date_from = p_effective_date
802 and date_to = hr_general.end_of_time;
803
804 --
805 -- Ensure the attributes associated with the deleted block
806 -- are the same as the ones from the previous block.
807 --
808 copyAttributesToBlock
809 (p_from_block_id => p_time_building_block_id,
810 p_from_block_ovn => l_max_ovn,
811 p_to_block_id => p_time_building_block_id,
812 p_to_block_ovn => (l_max_ovn)+1
813 );
814
815 else
816 close c_old_rec;
817 end if;
818
819 -- call after process user hook
820 begin
821
822 hxc_building_block_api_bk3.delete_building_block_a
823 (p_effective_date => p_effective_date,
824 p_time_building_block_id => l_time_building_block_id,
825 p_object_version_number => l_object_version_number
826 );
827
828 exception
829 when hr_api.cannot_find_prog_unit then
830 hr_api.cannot_find_prog_unit_error
831 (p_module_name => 'delete_building_block',
832 p_hook_type => 'AP'
833 );
834 end;
835
836 if p_validate then
837 raise hr_api.validate_enabled;
838 end if;
839
840 p_object_version_number := (l_max_ovn +1);
841
842 exception
843 when hr_api.validate_enabled then
844 rollback to delete_building_block;
845 when others then
846 raise;
847
848 end delete_building_block;
849
850
851 end hxc_building_block_api;