[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD
Source
1 Package Body hxc_timecard AS
2 /* $Header: hxctimecard.pkb 120.18.12010000.1 2008/07/28 11:23:00 appldev ship $ */
3
4 type attribute_list is table of number index by binary_integer;
5
6 attribute_id_list attribute_list;
7
8 g_package varchar2(12) := 'HXC_TIMECARD';
9 g_deposit_blocks hxc_block_table_type;
10 g_deposit_attributes hxc_attribute_table_type;
11 g_audit_messages hxc_message_table_type;
12
13 Procedure alias_translation
14 (p_blocks in HXC_BLOCK_TABLE_TYPE
15 ,p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
16 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
17 ) is
18
19 l_old_style_attr HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info;
20 l_old_style_blks HXC_SELF_SERVICE_TIME_DEPOSIT.timecard_info;
21
22 l_proc varchar2(31) := g_package||'.alias_translation';
23
24 Begin
25
26 hxc_timecard_attribute_utils.set_bld_blk_info_type_id(p_attributes);
27
28 HXC_ALIAS_TRANSLATOR.DO_DEPOSIT_TRANSLATION
29 (p_attributes => p_attributes
30 ,p_messages => p_messages
31 );
32
33 End alias_translation;
34
35 Procedure save_timecard
36 (p_blocks in out nocopy HXC_BLOCK_TABLE_TYPE
37 ,p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
38 ,p_timecard_props in HXC_TIMECARD_PROP_TABLE_TYPE
39 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
40 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
41 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
42 ,p_resubmit in varchar2
43 ) is
44
45 l_timecard_blocks hxc_timecard.block_list;
46 l_day_blocks hxc_timecard.block_list;
47 l_detail_blocks hxc_timecard.block_list;
48 l_transaction_info hxc_timecard.transaction_info;
49 l_proc varchar2(33) := g_package||'.save_timecard';
50 l_old_style_blks hxc_self_service_time_deposit.timecard_info;
51 l_old_style_attrs hxc_self_service_time_deposit.building_block_attribute_info;
52 l_old_messages hxc_self_service_time_deposit.message_table;
53 l_timecard_index number;
54 Begin
55
56 /*
57 Sort blocks
58 */
59 hxc_timecard_block_utils.sort_blocks
60 (p_blocks => p_blocks
61 ,p_timecard_blocks => l_timecard_blocks
62 ,p_day_blocks => l_day_blocks
63 ,p_detail_blocks => l_detail_blocks
64 );
65
66 /*
67 Perform basic checks
68 */
69
70 hxc_deposit_checks.perform_checks
71 (p_blocks => p_blocks
72 ,p_attributes => p_attributes
73 ,p_timecard_props => p_timecard_props
74 ,p_days => l_day_blocks
75 ,p_details => l_detail_blocks
76 ,p_messages => p_messages
77 );
78
79
80
81 hxc_timecard_message_helper.processerrors
82 (p_messages => p_messages);
83
84 /*
85 Add the security attributes
86 ARR: 115.52 Change - add message structure
87 */
88 hxc_security.add_security_attribute
89 (p_blocks => p_blocks,
90 p_attributes => p_attributes,
91 p_timecard_props => p_timecard_props,
92 p_messages => p_messages
93 );
94
95 hxc_timecard_message_helper.processerrors
96 (p_messages => p_messages);
97
98 /*
99 Translate any aliases
100 */
101 alias_translation
102 (p_blocks => p_blocks
103 ,p_attributes => p_attributes
104 ,p_messages => p_messages
105 );
106
107 hxc_timecard_message_helper.processerrors
108 (p_messages => p_messages);
109
110 /*
111 Set the block and attribute update process flags
112 Based on the data sent and in the db
113 */
114 hxc_block_attribute_update.set_process_flags
115 (p_blocks => p_blocks
116 ,p_attributes => p_attributes
117 );
118
119 /*
120 Removed any deleted attributes
121 */
122
123 hxc_timecard_attribute_utils.remove_deleted_attributes
124 (p_attributes => p_attributes);
125
126 /* Fix for bug 6489820 */
127
128 l_timecard_blocks.delete;
129 l_day_blocks.delete;
130 l_detail_blocks.delete;
131
132 /* End of fix for bug 6489820 */
133
134 /* fix by senthil for bug 5099360*/
135 hxc_timecard_block_utils.sort_blocks
136 (p_blocks => p_blocks
137 ,p_timecard_blocks => l_timecard_blocks
138 ,p_day_blocks => l_day_blocks
139 ,p_detail_blocks => l_detail_blocks
140 );
141 /* end of fix for bug 5099360*/
142
143 /*
144 Process Checks
145 */
146
147 hxc_deposit_checks.perform_process_checks
148 (p_blocks => p_blocks
149 ,p_attributes => p_attributes
150 ,p_timecard_props => p_timecard_props
151 ,p_days => l_day_blocks
152 ,p_details => l_detail_blocks
153 ,p_template => hxc_timecard.c_no
154 ,p_deposit_mode => hxc_timecard.c_save
155 ,p_messages => p_messages
156 );
157
158 hxc_timecard_message_helper.processerrors
159 (p_messages => p_messages);
160
161 /*
162 Validate blocks, attributes - including TERs.
163 */
164
165 hxc_timecard_validation.recipients_update_validation
166 (p_blocks => p_blocks
167 ,p_attributes => p_attributes
168 ,p_messages => p_messages
169 ,p_props => p_timecard_props
170 ,p_deposit_mode => hxc_timecard.c_save
171 ,p_resubmit => p_resubmit
172 );
173
174 hxc_timecard_message_helper.processerrors
175 (p_messages => p_messages);
176
177 hxc_timecard_validation.data_set_validation
178 (p_blocks => p_blocks
179 ,p_messages => p_messages
180 );
181
182 hxc_timecard_message_helper.processerrors
183 (p_messages => p_messages);
184
185 /*
186 Store blocks and attributes
187 */
188
189 if hxc_timecard_message_helper.noErrors then
190
191 hxc_timecard_deposit.execute
192 (p_blocks => p_blocks
193 ,p_attributes => p_attributes
194 ,p_timecard_blocks => l_timecard_blocks
195 ,p_day_blocks => l_day_blocks
196 ,p_detail_blocks => l_detail_blocks
197 ,p_messages => p_messages
198 ,p_transaction_info => l_transaction_info
199 );
200
201 hxc_timecard_message_helper.processerrors
202 (p_messages => p_messages);
203
204 end if;
205 if hxc_timecard_message_helper.noErrors then
206
207 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
208
209 /*
210 Bug 3345143 - this is done in the summary apis now.
211
212 hxc_find_notify_aprs_pkg.cancel_previous_notifications(p_blocks(l_timecard_index).time_building_block_id);
213 */
214 --
215 -- Maintain summary table
216 --
217 hxc_timecard_summary_api.timecard_deposit
218 (p_blocks => p_blocks
219 ,p_approval_item_type => NULL
220 ,p_approval_process_name => NULL
221 ,p_approval_item_key => NULL
222 ,p_tk_audit_item_type => NULL
223 ,p_tk_audit_process_name => NULL
224 ,p_tk_audit_item_key => NULL
225 );
226
227 hxc_timecard_audit.maintain_latest_details
228 (p_blocks => p_blocks );
229
230 hxc_timecard_message_helper.processerrors
231 (p_messages => p_messages);
232
233 end if;
234
235 p_timecard_id := p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).time_building_block_id;
236 p_timecard_ovn:= p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).object_version_number;
237
238 End save_timecard;
239
240 Procedure deposit_controller
241 (p_validate in varchar2
242 ,p_blocks in HXC_BLOCK_TABLE_TYPE
243 ,p_attributes in HXC_ATTRIBUTE_TABLE_TYPE
244 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
245 ,p_deposit_mode in VARCHAR2
246 ,p_template in VARCHAR2
247 ,p_item_type in VARCHAR2
248 ,p_approval_prc in VARCHAR2
249 ,p_cla_save in varchar2 default 'NO'
250 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
251 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
252 ) IS
253
254 l_timecard_blocks hxc_timecard.block_list;
255 l_day_blocks hxc_timecard.block_list;
256 l_detail_blocks hxc_timecard.block_list;
257 l_attributes hxc_attribute_table_type;
258 l_blocks hxc_block_table_type;
259 l_transaction_info hxc_timecard.transaction_info;
260 l_timecard_props hxc_timecard_prop_table_type;
261 l_proc varchar2(33) := g_package||'.DEPOSIT_CONTROLLER';
262 l_can_deposit boolean := true;
263 l_resubmit varchar2(10) := c_no;
264 l_timecard_index number;
265 l_item_key WF_ITEMS.ITEM_KEY%TYPE :=NULL;
266 l_tbb_id hxc_time_building_blocks.time_building_block_id%type; -- declare two variables for ID and OVN
267 l_tbb_ovn hxc_time_building_blocks.object_version_number%type;
268 l_attribute_index number;
269 l_audit_layout number;
270
271 l_restrict_blank_rows_on_save varchar2(10) := 'Y';
272 l_pref_table hxc_preference_evaluation.t_pref_table;
273 p_master_pref_table hxc_preference_evaluation.t_pref_table;
274 l_start_date date;
275 l_stop_date date;
276 l_resource_id number;
277 l_active_index number;
278 l_index number;
279 l_idx number;
280 bb_id_exists VARCHAR2(2) := 'N';
281 BEGIN
282
283 l_blocks := p_blocks;
284 l_attributes := p_attributes;
285
286 hxc_timecard_block_utils.initialize_timecard_index;
287
288 ----------------- Default Attributes -------------------------------------------------------
289 l_active_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
290 l_start_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).start_time);
291 l_stop_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).stop_time);
292 l_resource_id := p_blocks(l_active_index).resource_id;
293
294 --Get the Preference value - Time Store Days to Hour Conversion
295 hxc_preference_evaluation.resource_preferences(p_resource_id => l_resource_id,
296 p_preference_code => 'TS_PER_VALIDATE_ON_SAVE',
297 p_start_evaluation_date => l_start_date,
298 p_end_evaluation_date => l_stop_date,
299 p_sorted_pref_table => l_pref_table,
300 p_master_pref_table => p_master_pref_table );
301
302 IF l_pref_table.count > 0 THEN
303 l_restrict_blank_rows_on_save := l_pref_table(1).attribute2; --Restrict Blank Rows on Save.
304 END IF;
305 --------------------- Default Attributes -----------------------------------
306 /*
307 Check input parameters
308 */
309
310 hxc_deposit_checks.check_inputs
311 (p_blocks => p_blocks
312 ,p_attributes => l_attributes
313 ,p_deposit_mode => p_deposit_mode
314 ,p_template => p_template
315 ,p_messages => p_messages
316 );
317
318 hxc_timecard_message_helper.processerrors
319 (p_messages => p_messages);
320
321 /*
322 Determine if this is a resubmitted timecard
323 */
324
325 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
326
327 if(hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).date_to) = hr_general.end_of_time) then
328 l_resubmit := hxc_timecard_approval.is_timecard_resubmitted
329 (p_blocks(l_timecard_index).time_building_block_id
330 ,p_blocks(l_timecard_index).object_version_number
331 ,p_blocks(l_timecard_index).resource_id
332 ,hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time)
333 ,hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time)
334 );
335 else
336 l_resubmit := c_delete;
337 end if;
338
339 /*
340 Obtain the timecard properties
341 This might be changed to send
342 this information in from the
343 middle tier, to avoid another
344 pref evaluation
345 */
346 l_tbb_id :=p_blocks(hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).time_building_block_id;
347 l_tbb_ovn :=p_blocks(hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).object_version_number;
348
349 if (l_tbb_id <0 ) then -- when we are creating tmecard/template the id will be -ve value,so in this case we pass
350 l_tbb_id := null; -- NULL instead of -ve value
351 l_tbb_ovn :=null;
352 end if;
353
354 hxc_timecard_properties.get_preference_properties
355 (p_validate => c_yes
356 ,p_resource_id => p_blocks(l_timecard_index).resource_id
357 ,p_timecard_start_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time)
358 ,p_timecard_stop_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time)
359 ,p_for_timecard => false
360 ,p_messages => p_messages
361 ,p_property_table => l_timecard_props
362 ,p_timecard_bb_id => l_tbb_id -- passs the extra parameter timecard ID
363 ,p_timecard_bb_ovn => l_tbb_ovn -- pass the extra parameter timecard OVN
364 );
365
366 hxc_timecard_message_helper.processerrors
367 (p_messages => p_messages);
368
369 p_messages.delete;
370
371 /*
372 Sort the blocks - needed for deposit
373 and all sorts of short cuts!
374 */
375
376 if(p_deposit_mode = c_save) then
377
378 save_timecard
379 (p_blocks => l_blocks
380 ,p_attributes => l_attributes
381 ,p_timecard_props => l_timecard_props
382 ,p_messages => p_messages
383 ,p_timecard_id => p_timecard_id
384 ,p_timecard_ovn => p_timecard_ovn
385 ,p_resubmit => l_resubmit
386 );
387
388 elsif (p_deposit_mode = c_audit) then
389
390
391 l_blocks := g_deposit_blocks;
392 hxc_timecard_attribute_utils.append_additional_reasons
393 (g_deposit_attributes
394 ,p_attributes);
395 l_attributes := g_deposit_attributes;
396
397 p_messages := g_audit_messages;
398 l_attribute_index:=p_attributes.first;
399 LOOP EXIT WHEN NOT p_attributes.exists(l_attribute_index);
400 if(p_attributes(l_attribute_index).attribute_category = 'LAYOUT')
401 then
402 l_audit_layout := p_attributes(l_attribute_index).attribute6;
403 exit;
404 end if;
405 l_attribute_index := p_attributes.next(l_attribute_index);
406
407 end loop;
408 IF ( l_audit_layout IS NOT NULL )
409 THEN
410 hxc_deposit_checks.audit_checks
411 (p_blocks => l_blocks
412 ,p_attributes => l_attributes
413 ,p_messages => p_messages
414 );
415 END IF;
416 hxc_deposit_checks.audit_checks
417 (p_blocks => l_blocks
418 ,p_attributes => l_attributes
419 ,p_messages => p_messages
420 );
421
422 hxc_timecard_message_helper.processerrors
423 (p_messages => p_messages);
424
425 /*
426 Hold the completed blocks and attributes
427 for immeadiate deposit if required if
428 there were no error messages from audit
429 validation
430 */
431
432 if(hxc_timecard_message_helper.noerrors) then
433 g_deposit_blocks := l_blocks;
434 g_deposit_attributes := l_attributes;
435 end if;
436
437 else
438 if((p_validate = c_yes)OR(p_template=c_yes)) then
439
440 hxc_timecard_block_utils.sort_blocks
441 (p_blocks => p_blocks
442 ,p_timecard_blocks => l_timecard_blocks
443 ,p_day_blocks => l_day_blocks
444 ,p_detail_blocks => l_detail_blocks
445 );
446
447 /*
448 Main deposit controls
449 ^^^^^^^^^^^^^^^^^^^^^
450 Reform time data, if required
451 e.g Denormalize time data
452 */
453
454 hxc_block_attribute_update.denormalize_time
455 (p_blocks => l_blocks
456 ,p_mode => 'ADD'
457 );
458
459 /*
460 Perform basic checks, e.g.
461 Are there any other timecards for this period?
462 */
463
464 if(p_template=c_no) then
465
466 hxc_deposit_checks.perform_checks
467 (p_blocks => p_blocks
468 ,p_attributes => l_attributes
469 ,p_timecard_props => l_timecard_props
470 ,p_days => l_day_blocks
471 ,p_details => l_detail_blocks
472 ,p_messages => p_messages
473 );
474
475
476 hxc_timecard_message_helper.processerrors
477 (p_messages => p_messages);
478
479 p_messages.delete;
480
481 end if;
482
483 /*
484 Add the security attributes
485 ARR: 115.52 Change - add message structure
486 */
487 hxc_security.add_security_attribute
488 (p_blocks => p_blocks,
489 p_attributes => l_attributes,
490 p_timecard_props => l_timecard_props,
491 p_messages => p_messages
492 );
493
494 hxc_timecard_message_helper.processerrors
495 (p_messages => p_messages);
496
497 /*
498 Translate any aliases
499 */
500
501
502 alias_translation
503 (p_blocks => p_blocks
504 ,p_attributes => l_attributes
505 ,p_messages => p_messages
506 );
507
508
509 hxc_timecard_message_helper.processerrors
510 (p_messages => p_messages);
511
512 /*
513 Set the block and attribute update process flags
514 Based on the data sent and in the db
515 */
516
517
518 hxc_block_attribute_update.set_process_flags
519 (p_blocks => l_blocks
520 ,p_attributes => l_attributes
521 );
522
523 /*
524 Removed any effectively deleted attributes
525 */
526
527 if p_cla_save = 'NO' then ------------- CLA change
528
529
530 hxc_timecard_attribute_utils.remove_deleted_attributes
531 (p_attributes => l_attributes);
532
533 /*
534 Perform process checks
535 */
536
537 hxc_deposit_checks.perform_process_checks
538 (p_blocks => l_blocks
539 ,p_attributes => l_attributes
540 ,p_timecard_props => l_timecard_props
541 ,p_days => l_day_blocks
542 ,p_details => l_detail_blocks
543 ,p_template => p_template
544 ,p_deposit_mode => p_deposit_mode
545 ,p_messages => p_messages
546 );
547
548 hxc_timecard_message_helper.processerrors
549 (p_messages => p_messages);
550 /*
551 Validate blocks, attributes
552 */
553
554
555 hxc_timecard_validation.deposit_validation
556 (p_blocks => l_blocks
557 ,p_attributes => l_attributes
558 ,p_messages => p_messages
559 ,p_props => l_timecard_props
560 ,p_deposit_mode => p_deposit_mode
561 ,p_template => p_template
562 ,p_resubmit => l_resubmit
563 ,p_can_deposit => l_can_deposit
564 );
565
566 hxc_timecard_message_helper.processerrors
567 (p_messages => p_messages);
568
569 --
570 -- Validate the set up for the user
571 -- Do this only for timecards, and not
572 -- for templates.
573 --
574 /*
575 if(p_template = c_no) then
576
577 validate_setup
578 (p_deposit_mode => p_deposit_mode
579 ,p_blocks => l_blocks
580 ,p_attributes => l_attributes
581 ,p_messages => p_messages
582 );
583
584 end if;
585
586 hxc_timecard_message_helper.processerrors
587 (p_messages => p_messages);
588
589 */
590 /*
591 Reform time data, if required
592 e.g Denormalize time data
593 */
594
595
596 elsif p_cla_save = 'YES' then ------- CLA change
597
598 hxc_timecard_validation.timecard_validation
599 (p_blocks => l_blocks,
600 p_attributes => l_attributes,
601 p_messages => p_messages,
602 p_props => l_timecard_props,
603 p_deposit_mode => 'SAVE',
604 p_resubmit => l_resubmit
605 );
606 END IF;
607
608 hxc_block_attribute_update.denormalize_time
609 (p_blocks => l_blocks
610 ,p_mode => 'REMOVE'
611 );
612
613 /*
614 Hold the completed blocks and attributes
615 for immeadiate deposit if required
616 */
617 g_deposit_blocks := l_blocks;
618 g_deposit_attributes := l_attributes;
619 g_audit_messages := hxc_timecard_message_helper.getMessages;
620
621 end if;
622
623 if(((p_validate=c_no)OR(p_template=c_yes))AND(l_can_deposit))then
624
625 if(l_day_blocks.count = 0) then
626 --
627 -- Only bother to resort if we have to
628 --
629 hxc_timecard_block_utils.sort_blocks
630 (p_blocks => g_deposit_blocks
631 ,p_timecard_blocks => l_timecard_blocks
632 ,p_day_blocks => l_day_blocks
633 ,p_detail_blocks => l_detail_blocks
634 );
635
636 end if;
637
638 if(p_template = c_no) then
639 /*
640 Perform basic checks
641 */
642 hxc_deposit_checks.perform_checks
643 (p_blocks => g_deposit_blocks
644 ,p_attributes => g_deposit_attributes
645 ,p_timecard_props => l_timecard_props
646 ,p_days => l_day_blocks
647 ,p_details => l_detail_blocks
648 ,p_messages => p_messages
649 );
650
651 hxc_timecard_message_helper.processerrors
652 (p_messages => p_messages);
653
654 p_messages.delete;
655
656 end if;
657
658 /*
659 Store blocks and attributes
660 */
661 if(hxc_timecard_message_helper.noErrors) then
662
663 /*
664 At this point, if we're saving a template,
665 we should look to see if the id corresponding
666 to the template block is actually a timecard
667 in which case, we'll need to replace the ids
668 to ensure the template is saved properly
669 */
670
671 if(p_template = hxc_timecard.c_yes) then
672
673 hxc_block_attribute_update.replace_ids
674 (p_blocks => g_deposit_blocks
675 ,p_attributes => g_deposit_attributes
676 , p_duplicate_template => FALSE
677 );
678
679 end if;
680
681 --------------------------- Default Attributes -----------------------------------------
682 IF l_restrict_blank_rows_on_save = 'N'
683 OR l_restrict_blank_rows_on_save = 'No' THEN
684
685 hr_utility.trace('>deposit_controller g_deposit_blocks 1');
686 l_index := g_deposit_blocks.first;
687 LOOP
688 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
689 hr_utility.trace(
690 'RESOURCE_ID :'||g_deposit_blocks(l_index).RESOURCE_ID
691 ||'BB id : '||g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
692 ||'['||g_deposit_blocks(l_index).OBJECT_VERSION_NUMBER||']'
693 ||' PARENT_BUILDING_BLOCK_ID: '||g_deposit_blocks(l_index).PARENT_BUILDING_BLOCK_ID
694 ||' DATE_TO: '||g_deposit_blocks(l_index).DATE_TO
695 ||' SCOPE: '||g_deposit_blocks(l_index).SCOPE
696 ||' MEASURE : '||g_deposit_blocks(l_index).MEASURE
697 ||' START_TIME : '||g_deposit_blocks(l_index).START_TIME
698 ||' STOP_TIME : '||g_deposit_blocks(l_index).STOP_TIME
699 ||' TRANSLATION_DISPLAY_KEY : '||g_deposit_blocks(l_index).TRANSLATION_DISPLAY_KEY
700 ||' APPROVAL_STATUS: '||g_deposit_blocks(l_index).APPROVAL_STATUS
701 ||' APPROVAL_STYLE_ID: '||g_deposit_blocks(l_index).APPROVAL_STYLE_ID
702 );
703
704 l_index := g_deposit_blocks.next(l_index);
705 END LOOP;
706 hr_utility.trace('>deposit_controller l_blocks 1.1 ');
707 l_index := l_blocks.first;
708 LOOP
709 EXIT WHEN NOT l_blocks.exists(l_index);
710 hr_utility.trace(
711 'RESOURCE_ID :'||l_blocks(l_index).RESOURCE_ID
712 ||'BB id : '||l_blocks(l_index).TIME_BUILDING_BLOCK_ID
713 ||'['||l_blocks(l_index).OBJECT_VERSION_NUMBER||']'
714 ||' PARENT_BUILDING_BLOCK_ID: '||l_blocks(l_index).PARENT_BUILDING_BLOCK_ID
715 ||' DATE_TO: '||l_blocks(l_index).DATE_TO
716 ||' SCOPE: '||l_blocks(l_index).SCOPE
717 ||' MEASURE : '||l_blocks(l_index).MEASURE
718 ||' START_TIME : '||l_blocks(l_index).START_TIME
719 ||' STOP_TIME : '||l_blocks(l_index).STOP_TIME
720 ||' TRANSLATION_DISPLAY_KEY : '||l_blocks(l_index).TRANSLATION_DISPLAY_KEY
721 ||' APPROVAL_STATUS: '||l_blocks(l_index).APPROVAL_STATUS
722 ||' APPROVAL_STYLE_ID: '||l_blocks(l_index).APPROVAL_STYLE_ID
723 );
724 l_index := l_blocks.next(l_index);
725 END LOOP;
726
727 hr_utility.trace('Restrict Blank Rows on Save : '||l_restrict_blank_rows_on_save);
728 -- Added for DA
729 if((p_template <> c_yes) and (hxc_timecard_message_helper.noErrors)) then
730 l_index := g_deposit_blocks.first;
731 LOOP
732 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
733
734 bb_id_exists := 'N';
735
736 l_idx := l_blocks.first;
737 LOOP
738 EXIT WHEN NOT l_blocks.exists(l_idx);
739 IF g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
740 = l_blocks(l_idx).TIME_BUILDING_BLOCK_ID THEN
741 bb_id_exists := 'Y';
742 EXIT;
743 END IF;
744
745 l_idx := l_blocks.next(l_idx);
746 END LOOP;
747
748 hr_utility.trace('g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID : '||
749 g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID || '- sysdate :'||to_char(SYSDATE,'yyyy/mm/dd'));
750 hr_utility.trace('bb_id_exists :'|| bb_id_exists);
751
752 IF bb_id_exists = 'N' THEN
753 g_deposit_blocks(l_index).DATE_TO := to_char(SYSDATE,'yyyy/mm/dd');
754 END IF;
755
756 l_index := g_deposit_blocks.next(l_index);
757 END LOOP;
758
759 hxc_trans_display_key_utils.alter_translation_key
760 (p_g_deposit_blocks => g_deposit_blocks
761 ,p_actual_blocks => l_blocks
762 );
763
764 end if;
765 -- End of DA
766
767
768 hr_utility.trace('>deposit_controller 2');
769 l_index := g_deposit_blocks.first;
770 LOOP
771 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
772 hr_utility.trace(
773 'RESOURCE_ID :'||g_deposit_blocks(l_index).RESOURCE_ID
774 ||'BB id : '||g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
775 ||'['||g_deposit_blocks(l_index).OBJECT_VERSION_NUMBER||']'
776 ||' PARENT_BUILDING_BLOCK_ID: '||g_deposit_blocks(l_index).PARENT_BUILDING_BLOCK_ID
777 ||' DATE_TO: '||g_deposit_blocks(l_index).DATE_TO
778 ||' SCOPE: '||g_deposit_blocks(l_index).SCOPE
779 ||' MEASURE : '||g_deposit_blocks(l_index).MEASURE
780 ||' START_TIME : '||g_deposit_blocks(l_index).START_TIME
781 ||' STOP_TIME : '||g_deposit_blocks(l_index).STOP_TIME
782 ||' TRANSLATION_DISPLAY_KEY : '||g_deposit_blocks(l_index).TRANSLATION_DISPLAY_KEY
783 ||' APPROVAL_STATUS: '||g_deposit_blocks(l_index).APPROVAL_STATUS
784 ||' APPROVAL_STYLE_ID: '||g_deposit_blocks(l_index).APPROVAL_STYLE_ID
785 );
786
787 l_index := g_deposit_blocks.next(l_index);
788 END LOOP;
789 hr_utility.trace('>deposit_controller 2.1');
790 l_index := l_blocks.first;
791 LOOP
792 EXIT WHEN NOT l_blocks.exists(l_index);
793 hr_utility.trace(
794 'RESOURCE_ID :'||l_blocks(l_index).RESOURCE_ID
795 ||'BB id : '||l_blocks(l_index).TIME_BUILDING_BLOCK_ID
796 ||'['||l_blocks(l_index).OBJECT_VERSION_NUMBER||']'
797 ||' PARENT_BUILDING_BLOCK_ID: '||l_blocks(l_index).PARENT_BUILDING_BLOCK_ID
798 ||' DATE_TO: '||l_blocks(l_index).DATE_TO
799 ||' SCOPE: '||l_blocks(l_index).SCOPE
800 ||' MEASURE : '||l_blocks(l_index).MEASURE
801 ||' START_TIME : '||l_blocks(l_index).START_TIME
802 ||' STOP_TIME : '||l_blocks(l_index).STOP_TIME
803 ||' TRANSLATION_DISPLAY_KEY : '||l_blocks(l_index).TRANSLATION_DISPLAY_KEY
804 ||' APPROVAL_STATUS: '||l_blocks(l_index).APPROVAL_STATUS
805 ||' APPROVAL_STYLE_ID: '||l_blocks(l_index).APPROVAL_STYLE_ID
806 );
807
808 l_index := l_blocks.next(l_index);
809 END LOOP;
810
811 END IF; -- IF l_restrict_blank_rows_on_save = 'N'
812
813 --------------------------- Default Attributes -----------------------------------------
814
815 hxc_timecard_deposit.execute
816 (p_blocks => g_deposit_blocks
817 ,p_attributes => g_deposit_attributes
818 ,p_timecard_blocks => l_timecard_blocks
819 ,p_day_blocks => l_day_blocks
820 ,p_detail_blocks => l_detail_blocks
821 ,p_messages => p_messages
822 ,p_transaction_info => l_transaction_info
823 );
824
825 hxc_timecard_message_helper.processerrors
826 (p_messages => p_messages);
827
828 p_timecard_id := g_deposit_blocks
829 (hxc_timecard_block_utils.find_active_timecard_index(g_deposit_blocks)).time_building_block_id;
830
831 p_timecard_ovn := g_deposit_blocks
832 (hxc_timecard_block_utils.find_active_timecard_index(g_deposit_blocks)).object_version_number;
833
834 if((p_template <> c_yes) and (hxc_timecard_message_helper.noErrors)) then
835
836
837
838 -- Maintain summary table
839
840 hxc_timecard_summary_api.timecard_deposit
841 (p_blocks => g_deposit_blocks
842 ,p_approval_item_type => NULL
843 ,p_approval_process_name => NULL
844 ,p_approval_item_key => NULL
845 ,p_tk_audit_item_type => NULL
846 ,p_tk_audit_process_name => NULL
847 ,p_tk_audit_item_key => NULL
848 );
849
850 hxc_timecard_audit.maintain_latest_details
851 (p_blocks => g_deposit_blocks );
852
853 hxc_timecard_message_helper.processerrors
854 (p_messages => p_messages);
855
856 l_item_key :=
857 hxc_timecard_approval.begin_approval
858 (p_blocks => g_deposit_blocks,
859 p_item_type => p_item_type,
860 p_process_name => p_approval_prc,
861 p_resubmitted => l_resubmit,
862 p_timecard_props => l_timecard_props,
863 p_messages => p_messages
864 );
865
866 hxc_timecard_message_helper.processerrors
867 (p_messages => p_messages);
868
869 hxc_timecard_summary_pkg.update_summary_row
870 (p_timecard_id => p_timecard_id
871 ,p_approval_item_type => p_item_type
872 ,p_approval_process_name => p_approval_prc
873 ,p_approval_item_key => l_item_key
874 );
875
876 hr_utility.trace('Restrict Blank Rows on Save : '||l_restrict_blank_rows_on_save);
877
878 IF l_restrict_blank_rows_on_save = 'N' OR l_restrict_blank_rows_on_save = 'No' THEN
879 -- Added for DA
880 delete_null_entries(p_timecard_id => p_timecard_id
881 ,p_timecard_ovn => p_timecard_ovn);
882 -- end
883 END IF;
884 --
885 --115.43 Change Note: The delete on the structures are
886 --in both the if and elsif clauses here, since we must only
887 --delete the structures if there has been a successful deposit,
888 --namely the hxc_timecard_message_helper.noErrors function
889 --returns true. Customer tested this configuration.
890 --
891 g_deposit_blocks.delete;
892 g_deposit_attributes.delete;
893 g_audit_messages.delete;
894 elsif((p_template = c_yes) and (hxc_timecard_message_helper.noErrors)) then
895 hxc_template_summary_api.template_deposit
896 (p_blocks => g_deposit_blocks,
897 p_attributes =>g_deposit_attributes,
898 p_template_id =>p_timecard_id);
899
900 g_deposit_blocks.delete;
901 g_deposit_attributes.delete;
902 g_audit_messages.delete;
903 end if;
904
905 end if;
906 end if;
907
908 /*
909 Audit this transaction
910 */
911
912 hxc_timecard_audit.audit_deposit
913 (p_transaction_info => l_transaction_info
914 ,p_messages => p_messages
915 );
916
917 hxc_timecard_message_helper.processerrors
918 (p_messages => p_messages);
919
920 end if;
921
922 /*
923 Finally, deal with the errors
924 */
925
926 p_messages := hxc_timecard_message_helper.prepareMessages;
927
928 END deposit_controller;
929
930 Procedure create_timecard
931 (p_validate in varchar2
932 ,p_blocks in hxc_block_table_type
933 ,p_attributes in hxc_attribute_table_type
934 ,p_deposit_mode in varchar2
935 ,p_template in varchar2
936 ,p_item_type in wf_items.item_type%type
937 ,p_approval_prc in wf_process_activities.process_name%type
938 ,p_lock_rowid in rowid
939 ,p_cla_save in varchar2 default 'NO'
940 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
941 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
942 ,p_messages out nocopy hxc_message_table_type
943 ) is
944
945 l_proc varchar2(30) := g_package||'.CREATE_TIMECARD';
946 l_index number;
947 l_process_locker_type varchar2(80) := hxc_lock_util.c_ss_timecard_action;
948 l_released_success boolean := false;
949
950 l_valid_lock boolean := false;
951 l_lock_rowid rowid;
952 l_timecard_index number;
953
954 Begin
955 /*
956 For bug 3220588, we need to rollback in case there have been any
957 changes in the PA code, which require us to revert. E.g. the
958 transaction reference has been updated, but the user has clicked
959 the back button. No code should be updated in the validate phase
960 in OTL
961 */
962
963 if(p_validate=hxc_timecard.c_yes) then
964 rollback;
965 end if;
966
967 fnd_msg_pub.initialize;
968
969 hxc_timecard_message_helper.initializeErrors;
970
971 p_messages := hxc_message_table_type();
972
973 if(p_template = hxc_timecard.c_no) then
974 if(hxc_lock_api.check_lock(p_lock_rowid)) then
975 l_valid_lock := true;
976 else
977 --
978 -- For bug
979 -- If the lock is invalid, it might just have timed out, therefore
980 -- request a new lock.
981 --
982 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
983
984 if(hxc_timecard_block_utils.is_new_block(p_blocks(l_timecard_index))) then
985
986 hxc_lock_api.request_lock
987 (p_process_locker_type=> hxc_lock_util.c_ss_timecard_action
988 ,p_resource_id=> p_blocks(l_timecard_index).resource_id
989 ,p_start_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
990 ,p_stop_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
991 ,p_time_building_block_id=> null
992 ,p_time_building_block_ovn=> null
993 ,p_transaction_lock_id=> null
994 ,p_expiration_time=> 10
995 ,p_messages=> p_messages
996 ,p_row_lock_id=> l_lock_rowid
997 ,p_locked_success=> l_valid_lock
998 );
999 else
1000
1001 hxc_lock_api.request_lock
1002 (p_process_locker_type=> hxc_lock_util.c_ss_timecard_action
1003 ,p_resource_id=> p_blocks(l_timecard_index).resource_id
1004 ,p_start_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1005 ,p_stop_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1006 ,p_time_building_block_id=> p_blocks(l_timecard_index).time_building_block_id
1007 ,p_time_building_block_ovn=> p_blocks(l_timecard_index).object_version_number
1008 ,p_transaction_lock_id=> null
1009 ,p_expiration_time=> 10
1010 ,p_messages=> p_messages
1011 ,p_row_lock_id=> l_lock_rowid
1012 ,p_locked_success=> l_valid_lock
1013 );
1014 end if;
1015 end if;
1016 end if;
1017
1018 if((l_valid_lock) OR (p_template = hxc_timecard.c_yes)) then
1019
1020 deposit_controller
1021 (p_validate => p_validate
1022 ,p_blocks => p_blocks
1023 ,p_attributes => p_attributes
1024 ,p_messages => p_messages
1025 ,p_deposit_mode => p_deposit_mode
1026 ,p_template => p_template
1027 ,p_item_type => p_item_type
1028 ,p_approval_prc => p_approval_prc
1029 ,p_cla_save => p_cla_save
1030 ,p_timecard_id => p_timecard_id
1031 ,p_timecard_ovn => p_timecard_ovn
1032 );
1033
1034 if(
1035 ((p_deposit_mode = hxc_timecard.c_save) AND (hxc_timecard_message_helper.noerrors))
1036 OR
1037 ((p_deposit_mode = hxc_timecard.c_submit) AND (p_validate = hxc_timecard.c_no) AND (p_template = hxc_timecard.c_no))
1038 ) then
1039
1040 hxc_lock_api.release_lock
1041 (P_ROW_LOCK_ID => p_lock_rowid
1042 ,P_PROCESS_LOCKER_TYPE => l_process_locker_type
1043 ,P_TRANSACTION_LOCK_ID => null
1044 ,P_RESOURCE_ID => null
1045 ,P_START_TIME => null
1046 ,P_STOP_TIME => null
1047 ,P_TIME_BUILDING_BLOCK_ID => null
1048 ,P_TIME_BUILDING_BLOCK_OVN => null
1049 ,P_MESSAGES => p_messages
1050 ,P_RELEASED_SUCCESS => l_released_success
1051 );
1052
1053 elsif ((p_template = hxc_timecard.c_yes) AND (hxc_timecard_message_helper.noerrors))then
1054 --
1055 -- This is important, because we might have saved
1056 -- a timecard as a template, in which case, we should
1057 -- release the lock that we might have
1058 --
1059 hxc_lock_api.release_lock
1060 (p_row_lock_id => p_lock_rowid);
1061
1062 end if;
1063
1064 else
1065
1066 hxc_timecard_message_helper.addErrorToCollection
1067 (p_messages
1068 ,'HXC_TIMECARD_LOCK_FAILED'
1069 ,hxc_timecard.c_error
1070 ,null
1071 ,null
1072 ,hxc_timecard.c_hxc
1073 ,null
1074 ,null
1075 ,null
1076 ,null
1077 );
1078
1079
1080 end if;
1081
1082 End create_timecard;
1083
1084 Function load_blocks
1085 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1086 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
1087 ,p_load_mode in varchar2 default c_nondelete
1088 ) return hxc_block_table_type is
1089
1090 cursor c_blocks
1091 (p_tc_id in hxc_time_building_blocks.time_building_block_id%type
1092 ) is
1093 select tbb.time_building_block_id
1094 ,tbb.object_version_number
1095 from hxc_time_building_blocks tbb
1096 where tbb.date_to = hr_general.end_of_time
1097 start with (tbb.time_building_block_id = p_tc_id)
1098 connect by prior tbb.time_building_block_id = tbb.parent_building_block_id
1099 and prior tbb.object_version_number = tbb.parent_building_block_ovn;
1100
1101 l_blocks hxc_block_table_type := hxc_block_table_type();
1102 l_block_count number := 1;
1103
1104
1105 Begin
1106
1107 for block_rec in c_blocks(p_timecard_id) loop
1108 l_blocks.extend;
1109 l_blocks(l_block_count) := hxc_timecard_block_utils.build_block
1110 (block_rec.time_building_block_id
1111 ,block_rec.object_version_number);
1112 if(p_load_mode = c_delete) then
1113 --
1114 -- Date effectively end date the block
1115 --
1116 l_blocks(l_block_count).date_to := fnd_date.date_to_canonical(sysdate);
1117 end if;
1118
1119 l_block_count := l_block_count +1;
1120
1121 end loop;
1122
1123 return l_blocks;
1124
1125 End load_blocks;
1126
1127 Function load_attributes
1128 (p_blocks in hxc_block_table_type)
1129 return hxc_attribute_table_type is
1130
1131 cursor c_attributes
1132 (p_building_block_id in hxc_time_building_blocks.time_building_block_id%type
1133 ,p_building_block_ovn in hxc_time_building_blocks.object_version_number%type
1134 ) is
1135 select ta.time_attribute_id
1136 ,ta.attribute_category
1137 ,ta.attribute1
1138 ,ta.attribute2
1139 ,ta.attribute3
1140 ,ta.attribute4
1141 ,ta.attribute5
1142 ,ta.attribute6
1143 ,ta.attribute7
1144 ,ta.attribute8
1145 ,ta.attribute9
1146 ,ta.attribute10
1147 ,ta.attribute11
1148 ,ta.attribute12
1149 ,ta.attribute13
1150 ,ta.attribute14
1151 ,ta.attribute15
1152 ,ta.attribute16
1153 ,ta.attribute17
1154 ,ta.attribute18
1155 ,ta.attribute19
1156 ,ta.attribute20
1157 ,ta.attribute21
1158 ,ta.attribute22
1159 ,ta.attribute23
1160 ,ta.attribute24
1161 ,ta.attribute25
1162 ,ta.attribute26
1163 ,ta.attribute27
1164 ,ta.attribute28
1165 ,ta.attribute29
1166 ,ta.attribute30
1167 ,ta.bld_blk_info_type_id
1168 ,ta.object_version_number
1169 ,bbit.bld_blk_info_type
1170 from hxc_time_attribute_usages tau, hxc_time_attributes ta, hxc_bld_blk_info_types bbit
1171 where tau.time_building_block_id = p_building_block_id
1172 and tau.time_building_block_ovn = p_building_block_ovn
1173 and ta.time_attribute_id = tau.time_attribute_id
1174 and ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
1175
1176 l_attributes hxc_attribute_table_type := hxc_attribute_table_type();
1177
1178 l_block_index number;
1179 l_attribute_index number := 1;
1180
1181 Begin
1182
1183 l_block_index := p_blocks.first;
1184
1185 loop
1186 exit when not p_blocks.exists(l_block_index);
1187
1188 for attribute_rec in
1189 c_attributes(p_blocks(l_block_index).time_building_block_id,p_blocks(l_block_index).object_version_number)
1190 loop
1191
1192 l_attributes.extend;
1193 l_attributes(l_attribute_index) := HXC_ATTRIBUTE_TYPE
1194 (attribute_rec.TIME_ATTRIBUTE_ID
1195 ,p_blocks(l_block_index).time_building_block_id
1196 ,attribute_rec.ATTRIBUTE_CATEGORY
1197 ,attribute_rec.ATTRIBUTE1
1198 ,attribute_rec.ATTRIBUTE2
1199 ,attribute_rec.ATTRIBUTE3
1200 ,attribute_rec.ATTRIBUTE4
1201 ,attribute_rec.ATTRIBUTE5
1202 ,attribute_rec.ATTRIBUTE6
1203 ,attribute_rec.ATTRIBUTE7
1204 ,attribute_rec.ATTRIBUTE8
1205 ,attribute_rec.ATTRIBUTE9
1206 ,attribute_rec.ATTRIBUTE10
1207 ,attribute_rec.ATTRIBUTE11
1208 ,attribute_rec.ATTRIBUTE12
1209 ,attribute_rec.ATTRIBUTE13
1210 ,attribute_rec.ATTRIBUTE14
1211 ,attribute_rec.ATTRIBUTE15
1212 ,attribute_rec.ATTRIBUTE16
1213 ,attribute_rec.ATTRIBUTE17
1214 ,attribute_rec.ATTRIBUTE18
1215 ,attribute_rec.ATTRIBUTE19
1216 ,attribute_rec.ATTRIBUTE20
1217 ,attribute_rec.ATTRIBUTE21
1218 ,attribute_rec.ATTRIBUTE22
1219 ,attribute_rec.ATTRIBUTE23
1220 ,attribute_rec.ATTRIBUTE24
1221 ,attribute_rec.ATTRIBUTE25
1222 ,attribute_rec.ATTRIBUTE26
1223 ,attribute_rec.ATTRIBUTE27
1224 ,attribute_rec.ATTRIBUTE28
1225 ,attribute_rec.ATTRIBUTE29
1226 ,attribute_rec.ATTRIBUTE30
1227 ,attribute_rec.BLD_BLK_INFO_TYPE_ID
1228 ,attribute_rec.OBJECT_VERSION_NUMBER
1229 ,'N'
1230 ,'N'
1231 ,attribute_rec.bld_blk_info_type
1232 ,'N'
1233 ,p_blocks(l_block_index).object_version_number
1234 );
1235
1236 l_attribute_index := l_attribute_index +1;
1237 end loop;
1238 l_block_index := p_blocks.next(l_block_index);
1239 end loop;
1240
1241 return l_attributes;
1242
1243 End load_attributes;
1244
1245 Procedure delete_timecard
1246 (p_mode in varchar2
1247 ,p_template in varchar2
1248 ,p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1249 ,p_timecard_ok in out nocopy varchar2
1250 ) is
1251
1252 cursor c_timecard_ovn
1253 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
1254 select tbb.object_version_number, tbb.resource_id, tbb.start_time, tbb.stop_time
1255 from hxc_time_building_blocks tbb
1256 where tbb.time_building_block_id = p_timecard_id
1257 and tbb.date_to = hr_general.end_of_time;
1258
1259 l_blocks hxc_block_table_type := hxc_block_table_type();
1260 l_attributes hxc_attribute_table_type := hxc_attribute_table_type();
1261 l_messages hxc_message_table_type := hxc_message_table_type();
1262
1263 l_timecard_blocks hxc_timecard.block_list;
1264 l_day_blocks hxc_timecard.block_list;
1265 l_detail_blocks hxc_timecard.block_list;
1266
1267 l_transaction_info hxc_timecard.transaction_info;
1268 l_timecard_props hxc_timecard_prop_table_type;
1269
1270 l_dummy boolean := true;
1271
1272 l_timecard_ovn hxc_time_building_blocks.object_version_number%type;
1273 l_resource_id hxc_time_building_blocks.resource_id%type;
1274 l_start_time hxc_time_building_blocks.start_time%type;
1275 l_stop_time hxc_time_building_blocks.stop_time%type;
1276 l_timecard_index number;
1277 l_lock_rowid rowid;
1278 l_lock_success boolean;
1279
1280 l_delete_allowed varchar2(5) := 'FALSE';
1281
1282 Begin
1283 --
1284 -- Find the corresponding ovn of the timecard
1285 --
1286
1287 open c_timecard_ovn(p_timecard_id);
1288 fetch c_timecard_ovn into l_timecard_ovn, l_resource_id, l_start_time, l_stop_time;
1289 if(c_timecard_ovn%notfound) then
1290 close c_timecard_ovn;
1291 p_timecard_ok := hxc_timecard.c_no;
1292 else
1293 --
1294 -- Timecard is ok, continue.
1295 --
1296 p_timecard_ok := hxc_timecard.c_yes;
1297 close c_timecard_ovn;
1298
1299 --
1300 -- Initialize the message stack
1301 --
1302
1303 fnd_msg_pub.initialize;
1304 hxc_timecard_message_helper.initializeErrors;
1305 --
1306 -- Get the timecard or timecard template blocks and attributes
1307 --
1308
1309 l_blocks := load_blocks(p_timecard_id, l_timecard_ovn, c_delete);
1310 l_attributes := load_attributes(l_blocks);
1311 --
1312 -- Main delete processing
1313 --
1314
1315 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(l_blocks);
1316
1317 hxc_timecard_properties.get_preference_properties
1318 (p_validate => hxc_timecard.c_yes
1319 ,p_resource_id => l_blocks(l_timecard_index).resource_id
1320 ,p_timecard_start_time => hxc_timecard_block_utils.date_value(l_blocks(l_timecard_index).start_time)
1321 ,p_timecard_stop_time => hxc_timecard_block_utils.date_value(l_blocks(l_timecard_index).stop_time)
1322 ,p_for_timecard => false
1323 ,p_messages => l_messages
1324 ,p_property_table => l_timecard_props
1325 ,p_timecard_bb_id => p_timecard_id --passs the extra parameter timecard ID
1326 ,p_timecard_bb_ovn => l_timecard_ovn -- pass the extra parameter timecard OVN
1327 );
1328
1329 hxc_timecard_message_helper.processerrors
1330 (p_messages => l_messages);
1331
1332 l_messages.delete;
1333
1334 hxc_timecard_block_utils.sort_blocks
1335 (p_blocks => l_blocks
1336 ,p_timecard_blocks => l_timecard_blocks
1337 ,p_day_blocks => l_day_blocks
1338 ,p_detail_blocks => l_detail_blocks
1339 );
1340
1341 hxc_block_attribute_update.set_process_flags
1342 (p_blocks => l_blocks
1343 ,p_attributes => l_attributes
1344 );
1345
1346 --
1347 -- Don't want to issue this validation for
1348 -- timecard scope. The PA validation will
1349 -- fail.
1350 --
1351
1352 if(l_blocks(l_timecard_index).scope = c_timecard_scope) then
1353
1354 hxc_deposit_checks.perform_process_checks
1355 (p_blocks => l_blocks,
1356 p_attributes => l_attributes,
1357 p_timecard_props => l_timecard_props,
1358 p_days => l_day_blocks,
1359 p_details => l_detail_blocks,
1360 p_template => p_template,
1361 p_deposit_mode => p_mode,
1362 p_messages => l_messages
1363 );
1364
1365 hxc_timecard_message_helper.processerrors
1366 (p_messages => l_messages);
1367
1368 hxc_timecard_validation.deposit_validation
1369 (p_blocks => l_blocks
1370 ,p_attributes => l_attributes
1371 ,p_messages => l_messages
1372 ,p_props => l_timecard_props
1373 ,p_deposit_mode => hxc_timecard.c_submit
1374 ,p_template => p_template
1375 ,p_resubmit => c_delete
1376 ,p_can_deposit => l_dummy
1377 );
1378
1379 hxc_timecard_message_helper.processerrors
1380 (p_messages => l_messages);
1381
1382 elsif(l_blocks(l_timecard_index).scope = c_template_scope) then
1383
1384 hxc_deposit_checks.can_delete_template
1385 (l_blocks(l_timecard_index).time_building_block_id
1386 ,l_messages
1387 );
1388
1389 hxc_timecard_message_helper.processerrors
1390 (p_messages => l_messages);
1391
1392 end if;
1393
1394 if(hxc_timecard_message_helper.noErrors) then
1395
1396 hxc_timecard_deposit.execute
1397 (p_blocks => l_blocks
1398 ,p_attributes => l_attributes
1399 ,p_timecard_blocks => l_timecard_blocks
1400 ,p_day_blocks => l_day_blocks
1401 ,p_detail_blocks => l_detail_blocks
1402 ,p_messages => l_messages
1403 ,p_transaction_info => l_transaction_info
1404 );
1405 hxc_timecard_message_helper.processerrors
1406 (p_messages => l_messages);
1407
1408 /*
1409 Bug 3345143
1410 Removed following call. This is done inside delete_timecard on the
1411 summary api.
1412 hxc_find_notify_aprs_pkg.cancel_previous_notifications(p_timecard_id);
1413 */
1414 if(l_blocks(l_timecard_index).scope = c_template_scope) then --Only for templates.
1415 hxc_template_summary_api.DELETE_TEMPLATE(l_blocks(l_timecard_index).time_building_block_id);
1416 else --For Timecard.
1417 hxc_timecard_summary_api.delete_timecard
1418 (p_blocks => l_blocks
1419 ,p_timecard_id => p_timecard_id
1420 );
1421
1422 hxc_timecard_audit.audit_deposit
1423 (p_transaction_info => l_transaction_info
1424 ,p_messages => l_messages
1425 );
1426
1427 hxc_timecard_audit.maintain_latest_details
1428 (p_blocks => l_blocks );
1429 end if;
1430 hxc_timecard_message_helper.processerrors
1431 (p_messages => l_messages);
1432
1433 end if;
1434
1435 hxc_lock_api.release_lock
1436 (p_row_lock_id => l_lock_rowid);
1437
1438 hxc_timecard_message_helper.prepareErrors;
1439
1440 end if; -- Is the timecard ok?
1441
1442 End delete_timecard;
1443
1444 -- Added for DA Enhancement
1445 Procedure delete_null_entries
1446 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1447 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
1448 )is
1449
1450 cursor c_null_blocks
1451 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1452 ) is
1453
1454 SELECT det.time_building_block_id ,
1455 det.object_version_number
1456 FROM hxc_time_building_blocks DET,
1457 HXC_TIME_BUILDING_BLOCKS DAY
1458 WHERE day.parent_building_block_id = p_timecard_id
1459 AND det.parent_building_block_ovn = day.object_version_number
1460 AND det.parent_building_block_id = day.time_building_block_id
1461 AND det.scope = 'DETAIL'
1462 AND det.measure IS NULL
1463 AND det.start_time IS NULL
1464 AND det.stop_time IS NULL;
1465
1466 TYPE building_blocks_tab IS TABLE of NUMBER ;
1467
1468 bb_id_tab building_blocks_tab;
1469 bb_ovn_tab building_blocks_tab;
1470
1471
1472
1473 Begin
1474
1475
1476 OPEN c_null_blocks(p_timecard_id);
1477
1478 FETCH c_null_blocks
1479 BULK COLLECT INTO bb_id_tab,bb_ovn_tab;
1480
1481 FORALL i IN bb_id_tab.FIRST..bb_id_tab.LAST
1482
1483 DELETE
1484 FROM hxc_time_building_blocks
1485 WHERE time_building_block_id = bb_id_tab(i)
1486 AND object_version_number = bb_ovn_tab(i);
1487
1488
1489 CLOSE c_null_blocks;
1490
1491 End delete_null_entries;
1492
1493 END hxc_timecard;