[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD
Source
1 Package Body hxc_timecard AS
2 /* $Header: hxctimecard.pkb 120.39 2012/01/03 13:24:49 bbayragi 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_debug boolean := hr_utility.debug_enabled;
10 g_deposit_blocks hxc_block_table_type;
11 g_deposit_attributes hxc_attribute_table_type;
12 g_audit_messages hxc_message_table_type;
13
14 Procedure alias_translation
15 (p_blocks in HXC_BLOCK_TABLE_TYPE
16 ,p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
17 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
18 ) is
19
20 l_old_style_attr HXC_SELF_SERVICE_TIME_DEPOSIT.building_block_attribute_info;
21 l_old_style_blks HXC_SELF_SERVICE_TIME_DEPOSIT.timecard_info;
22
23 l_proc varchar2(31) := g_package||'.alias_translation';
24
25 Begin
26
27 hxc_timecard_attribute_utils.set_bld_blk_info_type_id(p_attributes);
28
29 HXC_ALIAS_TRANSLATOR.DO_DEPOSIT_TRANSLATION
30 (p_attributes => p_attributes
31 ,p_messages => p_messages
32 );
33
34 End alias_translation;
35
36 PROCEDURE remove_null_attribute_category( p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
37 )IS
38
39 l_proc varchar2(50) := g_package||'.REMOVE_NULL_ATTRIBUTE_CATEGORY';
40 l_index NUMBER;
41
42 BEGIN
43
44 IF g_debug THEN
45 hr_utility.trace('Entering '||l_proc);
46 END IF;
47
48 -- Bug 12370302
49 -- start
50 l_index := p_attributes.first;
51 LOOP
52 EXIT WHEN NOT p_attributes.exists(l_index);
53
54 IF (p_attributes(l_index).ATTRIBUTE_CATEGORY = 'PROJ-PAY-ATTR'
55 OR
56 p_attributes(l_index).ATTRIBUTE_CATEGORY = 'PROJECTS'
57 )
58 AND
59 (
60 p_attributes(l_index).ATTRIBUTE1 IS NULL
61 AND
62 p_attributes(l_index).ATTRIBUTE2 IS NULL
63 AND
64 p_attributes(l_index).ATTRIBUTE3 IS NULL
65 AND
66 p_attributes(l_index).ATTRIBUTE4 IS NULL
67 AND
68 p_attributes(l_index).ATTRIBUTE5 IS NULL
69 AND
70 p_attributes(l_index).ATTRIBUTE6 IS NULL
71 AND
72 p_attributes(l_index).ATTRIBUTE7 IS NULL
73 AND
74 p_attributes(l_index).ATTRIBUTE8 IS NULL
75 AND
76 p_attributes(l_index).ATTRIBUTE9 IS NULL
77 AND
78 p_attributes(l_index).ATTRIBUTE10 IS NULL
79 AND
80 p_attributes(l_index).ATTRIBUTE11 IS NULL
81 AND
82 p_attributes(l_index).ATTRIBUTE12 IS NULL
83 AND
84 p_attributes(l_index).ATTRIBUTE13 IS NULL
85 AND
86 p_attributes(l_index).ATTRIBUTE14 IS NULL
87 AND
88 p_attributes(l_index).ATTRIBUTE15 IS NULL
89 AND
90 p_attributes(l_index).ATTRIBUTE16 IS NULL
91 AND
92 p_attributes(l_index).ATTRIBUTE17 IS NULL
93 AND
94 p_attributes(l_index).ATTRIBUTE18 IS NULL
95 AND
96 p_attributes(l_index).ATTRIBUTE19 IS NULL
97 AND
98 p_attributes(l_index).ATTRIBUTE20 IS NULL
99 AND
100 p_attributes(l_index).ATTRIBUTE21 IS NULL
101 AND
102 p_attributes(l_index).ATTRIBUTE22 IS NULL
103 AND
104 p_attributes(l_index).ATTRIBUTE23 IS NULL
105 AND
106 p_attributes(l_index).ATTRIBUTE24 IS NULL
107 AND
108 p_attributes(l_index).ATTRIBUTE25 IS NULL
109 AND
110 p_attributes(l_index).ATTRIBUTE26 IS NULL
111 AND
112 p_attributes(l_index).ATTRIBUTE27 IS NULL
113 AND
114 p_attributes(l_index).ATTRIBUTE28 IS NULL
115 AND
116 p_attributes(l_index).ATTRIBUTE29 IS NULL
117 AND
118 p_attributes(l_index).ATTRIBUTE30 IS NULL
119 )
120 THEN
121 IF g_debug THEN
122 hr_utility.trace('PA-PAY> '||p_attributes(l_index).ATTRIBUTE_CATEGORY||'is deleted.');
123 END IF;
124 p_attributes.delete(l_index);
125 END IF;
126 l_index := p_attributes.next(l_index);
127 END LOOP;
128
129 IF g_debug THEN
130 hr_utility.trace('Leaving '||l_proc);
131 END IF;
132
133 END remove_null_attribute_category;
134
135 Procedure save_timecard
136 (p_blocks in out nocopy HXC_BLOCK_TABLE_TYPE
137 ,p_attributes in out nocopy HXC_ATTRIBUTE_TABLE_TYPE
138 ,p_timecard_props in HXC_TIMECARD_PROP_TABLE_TYPE
139 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
140 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
141 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
142 ,p_resubmit in varchar2
143 ) is
144
145 l_timecard_blocks hxc_timecard.block_list;
146 l_day_blocks hxc_timecard.block_list;
147 l_detail_blocks hxc_timecard.block_list;
148 l_transaction_info hxc_timecard.transaction_info;
149 l_proc varchar2(33) := g_package||'.save_timecard';
150 l_old_style_blks hxc_self_service_time_deposit.timecard_info;
151 l_old_style_attrs hxc_self_service_time_deposit.building_block_attribute_info;
152 l_old_messages hxc_self_service_time_deposit.message_table;
153 l_timecard_index number;
154
155 l_resource_id number;
156 l_start_date date;
157 l_stop_date date;
158 l_tc_status varchar2(20);
159
160 TC_SAVE_EXCEPTION EXCEPTION;
161
162 Begin
163
164 savepoint TC_SAVE_SAVEPOINT;
165
166 /*
167 Sort blocks
168 */
169 hxc_timecard_block_utils.sort_blocks
170 (p_blocks => p_blocks
171 ,p_timecard_blocks => l_timecard_blocks
172 ,p_day_blocks => l_day_blocks
173 ,p_detail_blocks => l_detail_blocks
174 );
175
176 /*
177 Perform basic checks
178 */
179
180 hxc_deposit_checks.perform_checks
181 (p_blocks => p_blocks
182 ,p_attributes => p_attributes
183 ,p_timecard_props => p_timecard_props
184 ,p_days => l_day_blocks
185 ,p_details => l_detail_blocks
186 ,p_messages => p_messages
187 );
188
189
190
191 hxc_timecard_message_helper.processerrors
192 (p_messages => p_messages);
193
194 /*
195 Add the security attributes
196 ARR: 115.52 Change - add message structure
197 */
198 hxc_security.add_security_attribute
199 (p_blocks => p_blocks,
200 p_attributes => p_attributes,
201 p_timecard_props => p_timecard_props,
202 p_messages => p_messages
203 );
204
205 hxc_timecard_message_helper.processerrors
206 (p_messages => p_messages);
207
208 /*
209 Translate any aliases
210 */
211 alias_translation
212 (p_blocks => p_blocks
213 ,p_attributes => p_attributes
214 ,p_messages => p_messages
215 );
216
217 hxc_timecard_message_helper.processerrors
218 (p_messages => p_messages);
219
220 -- OTL - ABS Integration
221 hxc_retrieve_absences.verify_view_only_absences(p_blocks => p_blocks,
222 p_attributes => p_attributes,
223 p_lock_rowid => hxc_retrieve_absences.g_lock_row_id,
224 p_messages => p_messages);
225 -- Added for Bug 10326049
226 -- OTL - Projects Payroll Integration
227
228 IF g_debug THEN
229 hr_utility.trace('PA-PAY> In hxc_timecard.save_timecard');
230 hr_utility.trace('PA-PAY> calling hxc_proj_pay_dep_validation.verify_proj_pay_setup');
231 END IF;
232
233 hxc_proj_pay_dep_validation.verify_proj_pay_setup(p_blocks => p_blocks,
234 p_attributes => p_attributes,
235 p_messages => p_messages);
236
237
238
239 /*
240 Set the block and attribute update process flags
241 Based on the data sent and in the db
242 */
243 hxc_block_attribute_update.set_process_flags
244 (p_blocks => p_blocks
245 ,p_attributes => p_attributes
246 );
247
248 /*
249 Removed any deleted attributes
250 */
251
252 hxc_timecard_attribute_utils.remove_deleted_attributes
253 (p_attributes => p_attributes);
254
255 /* Fix for bug 6489820 */
256
257 l_timecard_blocks.delete;
258 l_day_blocks.delete;
259 l_detail_blocks.delete;
260
261 /* End of fix for bug 6489820 */
262
263 /* fix by senthil for bug 5099360*/
264 hxc_timecard_block_utils.sort_blocks
265 (p_blocks => p_blocks
266 ,p_timecard_blocks => l_timecard_blocks
267 ,p_day_blocks => l_day_blocks
268 ,p_detail_blocks => l_detail_blocks
269 );
270 /* end of fix for bug 5099360*/
271
272 /*
273 Process Checks
274 */
275
276 hxc_deposit_checks.perform_process_checks
277 (p_blocks => p_blocks
278 ,p_attributes => p_attributes
279 ,p_timecard_props => p_timecard_props
280 ,p_days => l_day_blocks
281 ,p_details => l_detail_blocks
282 ,p_template => hxc_timecard.c_no
283 ,p_deposit_mode => hxc_timecard.c_save
284 ,p_messages => p_messages
285 );
286
287 hxc_timecard_message_helper.processerrors
288 (p_messages => p_messages);
289
290 /*
291 Validate blocks, attributes - including TERs.
292 */
293
294 hxc_timecard_validation.recipients_update_validation
295 (p_blocks => p_blocks
296 ,p_attributes => p_attributes
297 ,p_messages => p_messages
298 ,p_props => p_timecard_props
299 ,p_deposit_mode => hxc_timecard.c_save
300 ,p_resubmit => p_resubmit
301 );
302
303 hxc_timecard_message_helper.processerrors
304 (p_messages => p_messages);
305
306 hxc_timecard_validation.data_set_validation
307 (p_blocks => p_blocks
308 ,p_messages => p_messages
309 );
310
311 hxc_timecard_message_helper.processerrors
312 (p_messages => p_messages);
313
314 --svg start Bug 9181950
315 if NOT hxc_timecard_message_helper.noErrors then
316 --HXC_SVG_LOG.record_log('Reached here:svg');
317 if g_debug then
318 hr_utility.trace('SVG: Rollingback to savepoint TC_SAVE_SAVEPOINT for any Projects related updates');
319 end if;
320 rollback to TC_SAVE_SAVEPOINT;
321 end if;
322 -- svg end
323
324
325
326 /*
327 Store blocks and attributes
328 */
329
330 if hxc_timecard_message_helper.noErrors then
331
332 hxc_timecard_deposit.execute
333 (p_blocks => p_blocks
334 ,p_attributes => p_attributes
335 ,p_timecard_blocks => l_timecard_blocks
336 ,p_day_blocks => l_day_blocks
337 ,p_detail_blocks => l_detail_blocks
338 ,p_messages => p_messages
339 ,p_transaction_info => l_transaction_info
340 );
341
342 hxc_timecard_message_helper.processerrors
343 (p_messages => p_messages);
344
345 end if;
346
347 if hxc_timecard_message_helper.noErrors then
348
349 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
350
351 /*
352 Bug 3345143 - this is done in the summary apis now.
353
354 hxc_find_notify_aprs_pkg.cancel_previous_notifications(p_blocks(l_timecard_index).time_building_block_id);
355 */
356 --
357 -- Maintain summary table
358 --
359 hxc_timecard_summary_api.timecard_deposit
360 (p_blocks => p_blocks
361 ,p_approval_item_type => NULL
362 ,p_approval_process_name => NULL
363 ,p_approval_item_key => NULL
364 ,p_tk_audit_item_type => NULL
365 ,p_tk_audit_process_name => NULL
366 ,p_tk_audit_item_key => NULL
367 );
368
369 hxc_timecard_audit.maintain_latest_details
370 (p_blocks => p_blocks );
371
372
373 /* Bug 8888904 */
374 hxc_timecard_audit.maintain_rdb_snapshot
375 (p_blocks => p_blocks,
376 p_attributes => p_attributes);
377
378
379
380 hxc_timecard_message_helper.processerrors
381 (p_messages => p_messages);
382
383 end if;
384
385 p_timecard_id := p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).time_building_block_id;
386 p_timecard_ovn:= p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).object_version_number;
387
388
389 -- OTL-Absences Integration (Bug 8779478)
390 -- Moved the following code inside a BEGIN-EXCEPTION-END block to handle exceptions effectively
391 -- for Bug 8888138
392 BEGIN
393 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
394 IF (p_timecard_id > 0 and hxc_timecard_message_helper.noerrors
395 and p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).SCOPE <> hxc_timecard.c_template_scope) THEN
396
397 IF g_debug THEN
398 hr_utility.trace('ABS:Initiated Online Retrieval from HXC_TIMECARD.SAVE_TIMECARD');
399 END IF;
400
401 l_start_date := hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time);
402 l_stop_date := hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time);
403 l_resource_id := p_blocks(l_timecard_index).resource_id;
404 l_tc_status := p_blocks(l_timecard_index).approval_status;
405
406 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
407 l_start_date,
408 l_stop_date,
409 l_tc_status,
410 p_messages);
411
412
413 IF g_debug THEN
414 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
415 END IF;
416
417 IF p_messages.COUNT > 0 THEN
418 IF g_debug THEN
419 hr_utility.trace('ABS:Error in POST_ABSENCES');
420 END IF;
421 raise TC_SAVE_EXCEPTION;
422 END IF;
423
424
425 END IF;
426 END IF;
427
428 EXCEPTION
429
430 WHEN TC_SAVE_EXCEPTION THEN
431 IF g_debug THEN
432 hr_utility.trace('ABS: Exception TC_SAVE_EXCEPTION');
433 END IF;
434
435 rollback to TC_SAVE_SAVEPOINT;
436 hxc_timecard_message_helper.processerrors
437 (p_messages => p_messages);
438
439
440 END ; -- Absences end
441
442 IF g_debug THEN
443 hr_utility.trace('Leaving SAVE_TIMECARD');
444 END IF;
445
446 End save_timecard;
447
448 Procedure deposit_controller
449 (p_validate in varchar2
450 ,p_blocks in HXC_BLOCK_TABLE_TYPE
451 ,p_attributes in HXC_ATTRIBUTE_TABLE_TYPE
452 ,p_messages in out nocopy HXC_MESSAGE_TABLE_TYPE
453 ,p_deposit_mode in VARCHAR2
454 ,p_template in VARCHAR2
455 ,p_item_type in VARCHAR2
456 ,p_approval_prc in VARCHAR2
457 ,p_cla_save in varchar2 default 'NO'
458 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
459 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
460 ) IS
461
462 l_timecard_blocks hxc_timecard.block_list;
463 l_day_blocks hxc_timecard.block_list;
464 l_detail_blocks hxc_timecard.block_list;
465 l_attributes hxc_attribute_table_type;
466 l_blocks hxc_block_table_type;
467 l_transaction_info hxc_timecard.transaction_info;
468 l_timecard_props hxc_timecard_prop_table_type;
469 l_proc varchar2(33) := g_package||'.DEPOSIT_CONTROLLER';
470 l_can_deposit boolean := true;
471 l_resubmit varchar2(10) := c_no;
472 l_timecard_index number;
473 l_item_key WF_ITEMS.ITEM_KEY%TYPE :=NULL;
474 l_tbb_id hxc_time_building_blocks.time_building_block_id%type; -- declare two variables for ID and OVN
475 l_tbb_ovn hxc_time_building_blocks.object_version_number%type;
476 l_attribute_index number;
477 l_audit_layout number;
478
479 l_restrict_blank_rows_on_save varchar2(10) := 'Y';
480 l_pref_table hxc_preference_evaluation.t_pref_table;
481 p_master_pref_table hxc_preference_evaluation.t_pref_table;
482 l_start_date date;
483 l_stop_date date;
484 l_resource_id number;
485 l_tc_status varchar2(20);
486 l_active_index number;
487 l_index number;
488 l_idx number;
489 bb_id_exists VARCHAR2(2) := 'N';
490 l_abs_ix number;
491
492 TC_SUB_EXCEPTION EXCEPTION;
493
494 BEGIN
495
496 IF g_debug THEN
497 hr_utility.trace('Entering '||l_proc);
498 END IF;
499
500 savepoint TC_SUB_SAVEPOINT ;
501
502 l_blocks := p_blocks;
503 l_attributes := p_attributes;
504
505 IF g_debug THEN
506 l_index := l_attributes.first;
507 LOOP
508 EXIT WHEN NOT l_attributes.exists(l_index);
509 hr_utility.trace('PA-PAY> '||
510 'BB id : '||l_attributes(l_index).BUILDING_BLOCK_ID
511 ||'['||l_attributes(l_index).BUILDING_BLOCK_OVN||']'
512 ||' ATT CAT: '||l_attributes(l_index).ATTRIBUTE_CATEGORY
513 ||' ATTRIBUTE1: '||l_attributes(l_index).ATTRIBUTE1
514 ||' ATTRIBUTE2: '||l_attributes(l_index).ATTRIBUTE2
515 ||' ATTRIBUTE3: '||l_attributes(l_index).ATTRIBUTE3
516 ||' ATTRIBUTE4: '||l_attributes(l_index).ATTRIBUTE4
517 ||' ATTRIBUTE5: '||l_attributes(l_index).ATTRIBUTE5
518 ||' ATTRIBUTE6: '||l_attributes(l_index).ATTRIBUTE6
519 ||' ATTRIBUTE7: '||l_attributes(l_index).ATTRIBUTE7
520 ||' ATTRIBUTE8: '||l_attributes(l_index).ATTRIBUTE8
521 ||' ATTRIBUTE9: '||l_attributes(l_index).ATTRIBUTE9
522 ||' ATT ID '||l_attributes(l_index).TIME_ATTRIBUTE_ID
523 );
524 l_index := l_attributes.next(l_index);
525 END LOOP;
526 hr_utility.trace('PA-PAY> call to remove_null_attribute_category');
527 END IF;
528
529 -- Added for Bug 12370302
530 remove_null_attribute_category(l_attributes);
531
532 IF g_debug THEN
533 l_index := l_attributes.first;
534 LOOP
535 EXIT WHEN NOT l_attributes.exists(l_index);
536 hr_utility.trace('PA-PAY>'||
537 'BB id : '||l_attributes(l_index).BUILDING_BLOCK_ID
538 ||'['||l_attributes(l_index).BUILDING_BLOCK_OVN||']'
539 ||' ATT CAT: '||l_attributes(l_index).ATTRIBUTE_CATEGORY
540 ||' ATTRIBUTE1: '||l_attributes(l_index).ATTRIBUTE1
541 ||' ATTRIBUTE2: '||l_attributes(l_index).ATTRIBUTE2
542 ||' ATTRIBUTE3: '||l_attributes(l_index).ATTRIBUTE3
543 ||' ATTRIBUTE4: '||l_attributes(l_index).ATTRIBUTE4
544 ||' ATTRIBUTE5: '||l_attributes(l_index).ATTRIBUTE5
545 ||' ATTRIBUTE6: '||l_attributes(l_index).ATTRIBUTE6
546 ||' ATTRIBUTE7: '||l_attributes(l_index).ATTRIBUTE7
547 ||' ATTRIBUTE8: '||l_attributes(l_index).ATTRIBUTE8
548 ||' ATTRIBUTE9: '||l_attributes(l_index).ATTRIBUTE9
549 ||' ATT ID '||l_attributes(l_index).TIME_ATTRIBUTE_ID
550 );
551 l_index := l_attributes.next(l_index);
552 END LOOP;
553 END IF;
554
555 hxc_timecard_block_utils.initialize_timecard_index;
556
557 ----------------- Default Attributes -------------------------------------------------------
558 l_active_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
559 l_start_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).start_time);
560 l_stop_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).stop_time);
561 l_resource_id := p_blocks(l_active_index).resource_id;
562
563
564 hxc_preference_evaluation.resource_preferences(p_resource_id => l_resource_id,
565 p_preference_code => 'TS_PER_VALIDATE_ON_SAVE',
566 p_start_evaluation_date => l_start_date,
567 p_end_evaluation_date => l_stop_date,
568 p_sorted_pref_table => l_pref_table,
569 p_master_pref_table => p_master_pref_table );
570
571 IF l_pref_table.count > 0 THEN
572 l_restrict_blank_rows_on_save := l_pref_table(1).attribute2; --Restrict Blank Rows on Save.
573 END IF;
574 --------------------- Default Attributes -----------------------------------
575 /*
576 Check input parameters
577 */
578
579 hxc_deposit_checks.check_inputs
580 (p_blocks => p_blocks
581 ,p_attributes => l_attributes
582 ,p_deposit_mode => p_deposit_mode
583 ,p_template => p_template
584 ,p_messages => p_messages
585 );
586
587 hxc_timecard_message_helper.processerrors
588 (p_messages => p_messages);
589
590 /*
591 Determine if this is a resubmitted timecard
592 */
593
594 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
595
596 if(hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).date_to) = hr_general.end_of_time) then
597 l_resubmit := hxc_timecard_approval.is_timecard_resubmitted
598 (p_blocks(l_timecard_index).time_building_block_id
599 ,p_blocks(l_timecard_index).object_version_number
600 ,p_blocks(l_timecard_index).resource_id
601 ,hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time)
602 ,hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time)
603 );
604 else
605 l_resubmit := c_delete;
606 end if;
607
608 /*
609 Obtain the timecard properties
610 This might be changed to send
611 this information in from the
612 middle tier, to avoid another
613 pref evaluation
614 */
615 l_tbb_id :=p_blocks(hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).time_building_block_id;
616 l_tbb_ovn :=p_blocks(hxc_timecard_block_utils.find_active_timecard_index (p_blocks)).object_version_number;
617
618 if (l_tbb_id <0 ) then -- when we are creating tmecard/template the id will be -ve value,so in this case we pass
619 l_tbb_id := null; -- NULL instead of -ve value
620 l_tbb_ovn :=null;
621 end if;
622
623 hxc_timecard_properties.get_preference_properties
624 (p_validate => c_yes
625 ,p_resource_id => p_blocks(l_timecard_index).resource_id
626 ,p_timecard_start_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time)
627 ,p_timecard_stop_time => hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time)
628 ,p_for_timecard => false
629 ,p_messages => p_messages
630 ,p_property_table => l_timecard_props
631 ,p_timecard_bb_id => l_tbb_id -- passs the extra parameter timecard ID
632 ,p_timecard_bb_ovn => l_tbb_ovn -- pass the extra parameter timecard OVN
633 );
634
635 hxc_timecard_message_helper.processerrors
636 (p_messages => p_messages);
637
638 p_messages.delete;
639
640 /*
641 Sort the blocks - needed for deposit
642 and all sorts of short cuts!
643 */
644
645 if(p_deposit_mode = c_save) then
646
647 save_timecard
648 (p_blocks => l_blocks
649 ,p_attributes => l_attributes
650 ,p_timecard_props => l_timecard_props
651 ,p_messages => p_messages
652 ,p_timecard_id => p_timecard_id
653 ,p_timecard_ovn => p_timecard_ovn
654 ,p_resubmit => l_resubmit
655 );
656
657 elsif (p_deposit_mode = c_audit) then
658
659
660 l_blocks := g_deposit_blocks;
661 hxc_timecard_attribute_utils.append_additional_reasons
662 (g_deposit_attributes
663 ,p_attributes);
664 l_attributes := g_deposit_attributes;
665
666 p_messages := g_audit_messages;
667 l_attribute_index:=p_attributes.first;
668 LOOP EXIT WHEN NOT p_attributes.exists(l_attribute_index);
669 if(p_attributes(l_attribute_index).attribute_category = 'LAYOUT')
670 then
671 l_audit_layout := p_attributes(l_attribute_index).attribute6;
672 exit;
673 end if;
674 l_attribute_index := p_attributes.next(l_attribute_index);
675
676 end loop;
677 IF ( l_audit_layout IS NOT NULL )
678 THEN
679 hxc_deposit_checks.audit_checks
680 (p_blocks => l_blocks
681 ,p_attributes => l_attributes
682 ,p_messages => p_messages
683 );
684 END IF;
685 hxc_deposit_checks.audit_checks
686 (p_blocks => l_blocks
687 ,p_attributes => l_attributes
688 ,p_messages => p_messages
689 );
690
691 hxc_timecard_message_helper.processerrors
692 (p_messages => p_messages);
693
694 /*
695 Hold the completed blocks and attributes
696 for immeadiate deposit if required if
697 there were no error messages from audit
698 validation
699 */
700
701 if(hxc_timecard_message_helper.noerrors) then
702 g_deposit_blocks := l_blocks;
703 g_deposit_attributes := l_attributes;
704 end if;
705
706 else
707 if((p_validate = c_yes)OR(p_template=c_yes)) then
708
709 hxc_timecard_block_utils.sort_blocks
710 (p_blocks => p_blocks
711 ,p_timecard_blocks => l_timecard_blocks
712 ,p_day_blocks => l_day_blocks
713 ,p_detail_blocks => l_detail_blocks
714 );
715
716 /*
717 Main deposit controls
718 ^^^^^^^^^^^^^^^^^^^^^
719 Reform time data, if required
720 e.g Denormalize time data
721 */
722
723 hxc_block_attribute_update.denormalize_time
724 (p_blocks => l_blocks
725 ,p_mode => 'ADD'
726 );
727
728 /*
729 Perform basic checks, e.g.
730 Are there any other timecards for this period?
731 */
732
733 if(p_template=c_no) then
734
735 hxc_deposit_checks.perform_checks
736 (p_blocks => p_blocks
737 ,p_attributes => l_attributes
738 ,p_timecard_props => l_timecard_props
739 ,p_days => l_day_blocks
740 ,p_details => l_detail_blocks
741 ,p_messages => p_messages
742 );
743
744
745 hxc_timecard_message_helper.processerrors
746 (p_messages => p_messages);
747
748 p_messages.delete;
749
750 end if;
751
752 /*
753 Add the security attributes
754 ARR: 115.52 Change - add message structure
755 */
756 hxc_security.add_security_attribute
757 (p_blocks => p_blocks,
758 p_attributes => l_attributes,
759 p_timecard_props => l_timecard_props,
760 p_messages => p_messages
761 );
762
763 hxc_timecard_message_helper.processerrors
764 (p_messages => p_messages);
765
766 /*
767 Translate any aliases
768 */
769
770
771 alias_translation
772 (p_blocks => p_blocks
773 ,p_attributes => l_attributes
774 ,p_messages => p_messages
775 );
776
777
778 hxc_timecard_message_helper.processerrors
779 (p_messages => p_messages);
780
781 /*
782 Set the block and attribute update process flags
783 Based on the data sent and in the db
784 */
785
786
787 hxc_block_attribute_update.set_process_flags
788 (p_blocks => l_blocks
789 ,p_attributes => l_attributes
790 );
791
792 /*
793 Removed any effectively deleted attributes
794 */
795
796 if p_cla_save = 'NO' then ------------- CLA change
797
798
799 hxc_timecard_attribute_utils.remove_deleted_attributes
800 (p_attributes => l_attributes);
801
802 /*
803 Perform process checks
804 */
805
806 hxc_deposit_checks.perform_process_checks
807 (p_blocks => l_blocks
808 ,p_attributes => l_attributes
809 ,p_timecard_props => l_timecard_props
810 ,p_days => l_day_blocks
811 ,p_details => l_detail_blocks
812 ,p_template => p_template
813 ,p_deposit_mode => p_deposit_mode
814 ,p_messages => p_messages
815 );
816
817 hxc_timecard_message_helper.processerrors
818 (p_messages => p_messages);
819 /*
820 Validate blocks, attributes
821 */
822 -- OTL - ABS Integration
823 hxc_retrieve_absences.verify_view_only_absences(p_blocks => p_blocks,
824 p_attributes => l_attributes,
825 p_lock_rowid => hxc_retrieve_absences.g_lock_row_id,
826 p_messages => p_messages);
827 -- Added for Bug 10326049
828 -- OTL - Projects Payroll Integration
829
830 IF g_debug THEN
831 hr_utility.trace('PA-PAY> In hxc_timecard.deposit_controller');
832 hr_utility.trace('PA-PAY> calling hxc_proj_pay_dep_validation.verify_proj_pay_setup');
833 END IF;
834
835 hxc_proj_pay_dep_validation.verify_proj_pay_setup(p_blocks => p_blocks,
836 p_attributes => l_attributes,
837 p_messages => p_messages);
838
839
840 hxc_timecard_validation.deposit_validation
841 (p_blocks => l_blocks
842 ,p_attributes => l_attributes
843 ,p_messages => p_messages
844 ,p_props => l_timecard_props
845 ,p_deposit_mode => p_deposit_mode
846 ,p_template => p_template
847 ,p_resubmit => l_resubmit
848 ,p_can_deposit => l_can_deposit
849 );
850
851 hxc_timecard_message_helper.processerrors
852 (p_messages => p_messages);
853
854 --
855 -- Validate the set up for the user
856 -- Do this only for timecards, and not
857 -- for templates.
858 --
859 /*
860 if(p_template = c_no) then
861
862 validate_setup
863 (p_deposit_mode => p_deposit_mode
864 ,p_blocks => l_blocks
865 ,p_attributes => l_attributes
866 ,p_messages => p_messages
867 );
868
869 end if;
870
871 hxc_timecard_message_helper.processerrors
872 (p_messages => p_messages);
873
874 */
875 /*
876 Reform time data, if required
877 e.g Denormalize time data
878 */
879
880
881 elsif p_cla_save = 'YES' then ------- CLA change
882
883 hxc_timecard_validation.timecard_validation
884 (p_blocks => l_blocks,
885 p_attributes => l_attributes,
886 p_messages => p_messages,
887 p_props => l_timecard_props,
888 p_deposit_mode => 'SAVE',
889 p_resubmit => l_resubmit
890 );
891 END IF;
892
893 hxc_block_attribute_update.denormalize_time
894 (p_blocks => l_blocks
895 ,p_mode => 'REMOVE'
896 );
897
898 /*
899 Hold the completed blocks and attributes
900 for immeadiate deposit if required
901 */
902 g_deposit_blocks := l_blocks;
903 g_deposit_attributes := l_attributes;
904 g_audit_messages := hxc_timecard_message_helper.getMessages;
905
906 end if;
907
908 if(((p_validate=c_no)OR(p_template=c_yes))AND(l_can_deposit))then
909
910 if(l_day_blocks.count = 0) then
911 --
912 -- Only bother to resort if we have to
913 --
914 hxc_timecard_block_utils.sort_blocks
915 (p_blocks => g_deposit_blocks
916 ,p_timecard_blocks => l_timecard_blocks
917 ,p_day_blocks => l_day_blocks
918 ,p_detail_blocks => l_detail_blocks
919 );
920
921 end if;
922
923 if(p_template = c_no) then
924 /*
925 Perform basic checks
926 */
927 hxc_deposit_checks.perform_checks
928 (p_blocks => g_deposit_blocks
929 ,p_attributes => g_deposit_attributes
930 ,p_timecard_props => l_timecard_props
931 ,p_days => l_day_blocks
932 ,p_details => l_detail_blocks
933 ,p_messages => p_messages
934 );
935
936 hxc_timecard_message_helper.processerrors
937 (p_messages => p_messages);
938
939 p_messages.delete;
940
941 end if;
942
943 --svg start Bug 9181950
944 if NOT hxc_timecard_message_helper.noErrors then
945 --HXC_SVG_LOG.record_log('Reached here:svg');
946 if g_debug then
947 hr_utility.trace('SVG: Rollingback to savepoint TC_SUB_SAVEPOINT for any Projects related updates');
948 end if;
949 rollback to TC_SUB_SAVEPOINT;
950 end if;
951 -- svg end
952
953
954 /*
955 Store blocks and attributes
956 */
957 if(hxc_timecard_message_helper.noErrors) then
958
959 /*
960 At this point, if we're saving a template,
961 we should look to see if the id corresponding
962 to the template block is actually a timecard
963 in which case, we'll need to replace the ids
964 to ensure the template is saved properly
965 */
966
967 if(p_template = hxc_timecard.c_yes) then
968
969 hxc_block_attribute_update.replace_ids
970 (p_blocks => g_deposit_blocks
971 ,p_attributes => g_deposit_attributes
972 , p_duplicate_template => FALSE
973 );
974
975 end if;
976
977 --------------------------- Default Attributes -----------------------------------------
978 IF l_restrict_blank_rows_on_save = 'N'
979 OR l_restrict_blank_rows_on_save = 'No' THEN
980
981 IF g_debug THEN
982
983 hr_utility.trace('>deposit_controller g_deposit_blocks 1');
984 l_index := g_deposit_blocks.first;
985 LOOP
986 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
987 hr_utility.trace(
988 'RESOURCE_ID :'||g_deposit_blocks(l_index).RESOURCE_ID
989 ||'BB id : '||g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
990 ||'['||g_deposit_blocks(l_index).OBJECT_VERSION_NUMBER||']'
991 ||' PARENT_BUILDING_BLOCK_ID: '||g_deposit_blocks(l_index).PARENT_BUILDING_BLOCK_ID
992 ||' DATE_TO: '||g_deposit_blocks(l_index).DATE_TO
993 ||' SCOPE: '||g_deposit_blocks(l_index).SCOPE
994 ||' MEASURE : '||g_deposit_blocks(l_index).MEASURE
995 ||' START_TIME : '||g_deposit_blocks(l_index).START_TIME
996 ||' STOP_TIME : '||g_deposit_blocks(l_index).STOP_TIME
997 ||' TRANSLATION_DISPLAY_KEY : '||g_deposit_blocks(l_index).TRANSLATION_DISPLAY_KEY
998 ||' APPROVAL_STATUS: '||g_deposit_blocks(l_index).APPROVAL_STATUS
999 ||' APPROVAL_STYLE_ID: '||g_deposit_blocks(l_index).APPROVAL_STYLE_ID
1000 );
1001
1002 l_index := g_deposit_blocks.next(l_index);
1003 END LOOP;
1004
1005
1006 hr_utility.trace('>deposit_controller l_blocks 1.1 ');
1007 l_index := l_blocks.first;
1008 LOOP
1009 EXIT WHEN NOT l_blocks.exists(l_index);
1010 hr_utility.trace(
1011 'RESOURCE_ID :'||l_blocks(l_index).RESOURCE_ID
1012 ||'BB id : '||l_blocks(l_index).TIME_BUILDING_BLOCK_ID
1013 ||'['||l_blocks(l_index).OBJECT_VERSION_NUMBER||']'
1014 ||' PARENT_BUILDING_BLOCK_ID: '||l_blocks(l_index).PARENT_BUILDING_BLOCK_ID
1015 ||' DATE_TO: '||l_blocks(l_index).DATE_TO
1016 ||' SCOPE: '||l_blocks(l_index).SCOPE
1017 ||' MEASURE : '||l_blocks(l_index).MEASURE
1018 ||' START_TIME : '||l_blocks(l_index).START_TIME
1019 ||' STOP_TIME : '||l_blocks(l_index).STOP_TIME
1020 ||' TRANSLATION_DISPLAY_KEY : '||l_blocks(l_index).TRANSLATION_DISPLAY_KEY
1021 ||' APPROVAL_STATUS: '||l_blocks(l_index).APPROVAL_STATUS
1022 ||' APPROVAL_STYLE_ID: '||l_blocks(l_index).APPROVAL_STYLE_ID
1023 );
1024 l_index := l_blocks.next(l_index);
1025 END LOOP;
1026 END IF;
1027
1028
1029 hr_utility.trace('Restrict Blank Rows on Save : '||l_restrict_blank_rows_on_save);
1030 -- Added for DA
1031 IF((p_template <> c_yes) and (hxc_timecard_message_helper.noErrors)) THEN
1032 l_index := g_deposit_blocks.first;
1033 LOOP
1034 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
1035
1036 bb_id_exists := 'N';
1037
1038 l_idx := l_blocks.first;
1039 LOOP
1040 EXIT WHEN NOT l_blocks.exists(l_idx);
1041 IF g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
1042 = l_blocks(l_idx).TIME_BUILDING_BLOCK_ID THEN
1043 bb_id_exists := 'Y';
1044 EXIT;
1045 END IF;
1046
1047 l_idx := l_blocks.next(l_idx);
1048 END LOOP;
1049
1050 hr_utility.trace('g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID : '||
1051 g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID || '- sysdate :'||to_char(SYSDATE,'yyyy/mm/dd'));
1052 hr_utility.trace('bb_id_exists :'|| bb_id_exists);
1053
1054 IF bb_id_exists = 'N' THEN
1055 g_deposit_blocks(l_index).DATE_TO := to_char(SYSDATE,'yyyy/mm/dd');
1056 END IF;
1057
1058 l_index := g_deposit_blocks.next(l_index);
1059 END LOOP;
1060
1061 hxc_trans_display_key_utils.alter_translation_key
1062 (p_g_deposit_blocks => g_deposit_blocks
1063 ,p_actual_blocks => l_blocks
1064 );
1065
1066 END IF;
1067 -- End of DA
1068
1069 IF g_debug THEN
1070
1071 hr_utility.trace('>deposit_controller 2');
1072 l_index := g_deposit_blocks.first;
1073 LOOP
1074 EXIT WHEN NOT g_deposit_blocks.exists(l_index);
1075 hr_utility.trace(
1076 'RESOURCE_ID :'||g_deposit_blocks(l_index).RESOURCE_ID
1077 ||'BB id : '||g_deposit_blocks(l_index).TIME_BUILDING_BLOCK_ID
1078 ||'['||g_deposit_blocks(l_index).OBJECT_VERSION_NUMBER||']'
1079 ||' PARENT_BUILDING_BLOCK_ID: '||g_deposit_blocks(l_index).PARENT_BUILDING_BLOCK_ID
1080 ||' DATE_TO: '||g_deposit_blocks(l_index).DATE_TO
1081 ||' SCOPE: '||g_deposit_blocks(l_index).SCOPE
1082 ||' MEASURE : '||g_deposit_blocks(l_index).MEASURE
1083 ||' START_TIME : '||g_deposit_blocks(l_index).START_TIME
1084 ||' STOP_TIME : '||g_deposit_blocks(l_index).STOP_TIME
1085 ||' TRANSLATION_DISPLAY_KEY : '||g_deposit_blocks(l_index).TRANSLATION_DISPLAY_KEY
1086 ||' APPROVAL_STATUS: '||g_deposit_blocks(l_index).APPROVAL_STATUS
1087 ||' APPROVAL_STYLE_ID: '||g_deposit_blocks(l_index).APPROVAL_STYLE_ID
1088 );
1089
1090 l_index := g_deposit_blocks.next(l_index);
1091 END LOOP;
1092
1093 hr_utility.trace('>deposit_controller 2.1');
1094 l_index := l_blocks.first;
1095 LOOP
1096 EXIT WHEN NOT l_blocks.exists(l_index);
1097 hr_utility.trace(
1098 'RESOURCE_ID :'||l_blocks(l_index).RESOURCE_ID
1099 ||'BB id : '||l_blocks(l_index).TIME_BUILDING_BLOCK_ID
1100 ||'['||l_blocks(l_index).OBJECT_VERSION_NUMBER||']'
1101 ||' PARENT_BUILDING_BLOCK_ID: '||l_blocks(l_index).PARENT_BUILDING_BLOCK_ID
1102 ||' DATE_TO: '||l_blocks(l_index).DATE_TO
1103 ||' SCOPE: '||l_blocks(l_index).SCOPE
1104 ||' MEASURE : '||l_blocks(l_index).MEASURE
1105 ||' START_TIME : '||l_blocks(l_index).START_TIME
1106 ||' STOP_TIME : '||l_blocks(l_index).STOP_TIME
1107 ||' TRANSLATION_DISPLAY_KEY : '||l_blocks(l_index).TRANSLATION_DISPLAY_KEY
1108 ||' APPROVAL_STATUS: '||l_blocks(l_index).APPROVAL_STATUS
1109 ||' APPROVAL_STYLE_ID: '||l_blocks(l_index).APPROVAL_STYLE_ID
1110 );
1111
1112 l_index := l_blocks.next(l_index);
1113 END LOOP;
1114 END IF;
1115
1116
1117 END IF; -- IF l_restrict_blank_rows_on_save = 'N'
1118
1119 --------------------------- Default Attributes -----------------------------------------
1120
1121 hxc_timecard_deposit.execute
1122 (p_blocks => g_deposit_blocks
1123 ,p_attributes => g_deposit_attributes
1124 ,p_timecard_blocks => l_timecard_blocks
1125 ,p_day_blocks => l_day_blocks
1126 ,p_detail_blocks => l_detail_blocks
1127 ,p_messages => p_messages
1128 ,p_transaction_info => l_transaction_info
1129 );
1130
1131 hxc_timecard_message_helper.processerrors
1132 (p_messages => p_messages);
1133
1134 p_timecard_id := g_deposit_blocks
1135 (hxc_timecard_block_utils.find_active_timecard_index(g_deposit_blocks)).time_building_block_id;
1136
1137 p_timecard_ovn := g_deposit_blocks
1138 (hxc_timecard_block_utils.find_active_timecard_index(g_deposit_blocks)).object_version_number;
1139
1140 if((p_template <> c_yes) and (hxc_timecard_message_helper.noErrors)) then
1141
1142
1143 -- Maintain summary table
1144
1145 hxc_timecard_summary_api.timecard_deposit
1146 (p_blocks => g_deposit_blocks
1147 ,p_approval_item_type => NULL
1148 ,p_approval_process_name => NULL
1149 ,p_approval_item_key => NULL
1150 ,p_tk_audit_item_type => NULL
1151 ,p_tk_audit_process_name => NULL
1152 ,p_tk_audit_item_key => NULL
1153 );
1154
1155 hxc_timecard_audit.maintain_latest_details
1156 (p_blocks => g_deposit_blocks );
1157
1158
1159 /*Bug 8888904 */
1160 hxc_timecard_audit.maintain_rdb_snapshot
1161 (p_blocks => g_deposit_blocks,
1162 p_attributes => g_deposit_attributes);
1163
1164
1165 hxc_timecard_message_helper.processerrors
1166 (p_messages => p_messages);
1167
1168 -- OTL-Absences Integration (Bug 8779478)
1169 -- Moved the following code inside a BEGIN-EXCEPTION-END block to handle exceptions effectively
1170 -- for Bug 8888138
1171 BEGIN
1172 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y')
1173 THEN
1174 IF (p_timecard_id > 0 and hxc_timecard_message_helper.noerrors
1175 and p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).SCOPE <> hxc_timecard.c_template_scope)
1176 THEN
1177
1178 IF g_debug THEN
1179 hr_utility.trace('ABS:Initiated Online Retrieval from HXC_TIMECARD.DEPOSIT_CONTROLLER');
1180 END IF;
1181
1182 l_resource_id := p_blocks(l_timecard_index).resource_id ;
1183 l_start_date := hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).start_time);
1184 l_stop_date := hxc_timecard_block_utils.date_value(p_blocks(l_timecard_index).stop_time) ;
1185 l_tc_status := p_blocks(l_timecard_index).approval_status ;
1186
1187 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
1188 l_start_date,
1189 l_stop_date,
1190 l_tc_status,
1191 p_messages);
1192
1193 IF g_debug THEN
1194 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
1195 END IF;
1196
1197 if p_messages.COUNT > 0
1198 then
1199 IF g_debug THEN
1200 hr_utility.trace('ABS:Error in POST_ABSENCES');
1201 END IF;
1202 raise TC_SUB_EXCEPTION;
1203 end if;
1204
1205 END IF;
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN TC_SUB_EXCEPTION THEN
1210 IF g_debug THEN
1211 hr_utility.trace('ABS: Exception TC_SUB_EXCEPTION');
1212 END IF;
1213
1214 rollback to TC_SUB_SAVEPOINT;
1215 hxc_timecard_message_helper.processerrors
1216 (p_messages => p_messages);
1217
1218
1219 END;
1220 -- Absences end
1221
1222 hxc_timecard_message_helper.processerrors
1223 (p_messages => p_messages);
1224
1225 l_item_key :=
1226 hxc_timecard_approval.begin_approval
1227 (p_blocks => g_deposit_blocks,
1228 p_item_type => p_item_type,
1229 p_process_name => p_approval_prc,
1230 p_resubmitted => l_resubmit,
1231 p_timecard_props => l_timecard_props,
1232 p_messages => p_messages
1233 );
1234
1235 -- Absences start
1236 -- Moved the following code inside a BEGIN-EXCEPTION-END block to handle exceptions effectively
1237 -- for Bug 8888138
1238 BEGIN
1239 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
1240 IF g_debug THEN
1241 hr_utility.trace('ABS:Checking status of BEGIN_APPROVAL');
1242 hr_utility.trace('ABS:p_messages.COUNT = '||p_messages.COUNT);
1243 END IF;
1244
1245 if p_messages.COUNT > 0
1246 then
1247 IF g_debug THEN
1248 hr_utility.trace('ABS:Error in POST_ABSENCES');
1249 END IF;
1250 raise TC_SUB_EXCEPTION;
1251 end if;
1252 END IF;
1253 EXCEPTION
1254 WHEN TC_SUB_EXCEPTION THEN
1255 IF g_debug THEN
1256 hr_utility.trace('ABS: Exception TC_SUB_EXCEPTION');
1257 END IF;
1258 rollback to TC_SUB_SAVEPOINT;
1259 hxc_timecard_message_helper.processerrors
1260 (p_messages => p_messages);
1261
1262
1263 END;
1264 -- Absences end
1265
1266
1267 hxc_timecard_message_helper.processerrors
1268 (p_messages => p_messages);
1269
1270 hxc_timecard_summary_pkg.update_summary_row
1271 (p_timecard_id => p_timecard_id
1272 ,p_approval_item_type => p_item_type
1273 ,p_approval_process_name => p_approval_prc
1274 ,p_approval_item_key => l_item_key
1275 );
1276
1277 hr_utility.trace('Restrict Blank Rows on Save : '||l_restrict_blank_rows_on_save);
1278
1279 IF l_restrict_blank_rows_on_save = 'N' OR l_restrict_blank_rows_on_save = 'No' THEN
1280 -- Added for DA
1281 delete_null_entries(p_timecard_id => p_timecard_id
1282 ,p_timecard_ovn => p_timecard_ovn);
1283 -- end
1284 END IF;
1285 --
1286 --115.43 Change Note: The delete on the structures are
1287 --in both the if and elsif clauses here, since we must only
1288 --delete the structures if there has been a successful deposit,
1289 --namely the hxc_timecard_message_helper.noErrors function
1290 --returns true. Customer tested this configuration.
1291 --
1292 g_deposit_blocks.delete;
1293 g_deposit_attributes.delete;
1294 g_audit_messages.delete;
1295 elsif((p_template = c_yes) and (hxc_timecard_message_helper.noErrors)) then
1296 hxc_template_summary_api.template_deposit
1297 (p_blocks => g_deposit_blocks,
1298 p_attributes =>g_deposit_attributes,
1299 p_template_id =>p_timecard_id);
1300
1301 g_deposit_blocks.delete;
1302 g_deposit_attributes.delete;
1303 g_audit_messages.delete;
1304 end if;
1305
1306 end if;
1307 end if;
1308
1309 /*
1310 Audit this transaction
1311 */
1312
1313 hxc_timecard_audit.audit_deposit
1314 (p_transaction_info => l_transaction_info
1315 ,p_messages => p_messages
1316 );
1317
1318 hxc_timecard_message_helper.processerrors
1319 (p_messages => p_messages);
1320
1321 end if;
1322
1323 /*
1324 Finally, deal with the errors
1325 */
1326
1327 p_messages := hxc_timecard_message_helper.prepareMessages;
1328
1329 IF g_debug THEN
1330 hr_utility.trace('Leaving '||l_proc);
1331 END IF;
1332
1333 END deposit_controller;
1334
1335 Procedure create_timecard
1336 (p_validate in varchar2
1337 ,p_blocks in hxc_block_table_type
1338 ,p_attributes in hxc_attribute_table_type
1339 ,p_deposit_mode in varchar2
1340 ,p_template in varchar2
1341 ,p_item_type in wf_items.item_type%type
1342 ,p_approval_prc in wf_process_activities.process_name%type
1343 ,p_lock_rowid in rowid
1344 ,p_cla_save in varchar2 default 'NO'
1345 ,p_timecard_id out nocopy hxc_time_building_blocks.time_building_block_id%type
1346 ,p_timecard_ovn out nocopy hxc_time_building_blocks.object_version_number%type
1347 ,p_messages out nocopy hxc_message_table_type
1348 ) is
1349
1350 l_proc varchar2(30) := g_package||'.CREATE_TIMECARD';
1351 l_index number;
1352 l_process_locker_type varchar2(80) := hxc_lock_util.c_ss_timecard_action;
1353 l_released_success boolean := false;
1354
1355 l_valid_lock boolean := false;
1356 l_lock_rowid rowid;
1357 l_timecard_index number;
1358
1359 l_resource_id hxc_time_building_blocks.resource_id%type;
1360 l_timecard_start_time hxc_time_building_blocks.start_time%type;
1361 l_timecard_stop_time hxc_time_building_blocks.stop_time%type;
1362
1363 d_lock_rowid varchar2(100);
1364
1365 -- Bug 10196997
1366 -- Added the below cursor and variable to check the locker type id
1367 d_exists NUMBER := 0;
1368
1369 cursor c_locker_type_requestor (p_locker_type VARCHAR2
1370 ,p_process_type VARCHAR2) is
1371 select locker_type_id
1372 from hxc_locker_types
1373 where locker_type = p_locker_type
1374 and process_type = p_process_type;
1375
1376 l_locker_type_requestor_id NUMBER;
1377
1378
1379
1380 Begin
1381
1382 g_debug := hr_utility.debug_enabled;
1383
1384 IF g_debug THEN
1385 hr_utility.trace('ABS> In hxc_timecard.create_timecard procedure');
1386 l_index := p_attributes.first;
1387 LOOP
1388 EXIT WHEN NOT p_attributes.exists(l_index);
1389 -- l_attribute := p_attributes(l_index);
1390
1391 hr_utility.trace('ABS> '||
1392 'BB id : '||p_attributes(l_index).BUILDING_BLOCK_ID
1393 ||'['||p_attributes(l_index).BUILDING_BLOCK_OVN||']'
1394 ||' ATT CAT: '||p_attributes(l_index).ATTRIBUTE_CATEGORY
1395 ||' ATTRIBUTE1: '||p_attributes(l_index).ATTRIBUTE1
1396 ||' ATTRIBUTE2: '||p_attributes(l_index).ATTRIBUTE2
1397 ||' ATTRIBUTE3: '||p_attributes(l_index).ATTRIBUTE3
1398 ||' ATTRIBUTE4: '||p_attributes(l_index).ATTRIBUTE4
1399 ||' ATTRIBUTE5: '||p_attributes(l_index).ATTRIBUTE5
1400 ||' ATTRIBUTE6: '||p_attributes(l_index).ATTRIBUTE6
1401 ||' ATTRIBUTE7: '||p_attributes(l_index).ATTRIBUTE7
1402 ||' ATTRIBUTE8: '||p_attributes(l_index).ATTRIBUTE8
1403 ||' ATTRIBUTE9: '||p_attributes(l_index).ATTRIBUTE9
1404 ||' ATT ID '||p_attributes(l_index).TIME_ATTRIBUTE_ID
1405 );
1406
1407 l_index := p_attributes.next(l_index);
1408 END LOOP;
1409
1410 l_index := p_blocks.first;
1411 LOOP
1412 EXIT WHEN NOT p_blocks.exists(l_index);
1413 hr_utility.trace('ABS> '||
1414 'RESOURCE_ID :'||p_blocks(l_index).RESOURCE_ID
1415 ||'BB id : '||p_blocks(l_index).TIME_BUILDING_BLOCK_ID
1416 ||'['||p_blocks(l_index).OBJECT_VERSION_NUMBER||']'
1417 ||' PARENT_BUILDING_BLOCK_ID: '||p_blocks(l_index).PARENT_BUILDING_BLOCK_ID
1418 ||' DATE_TO: '||p_blocks(l_index).DATE_TO
1419 ||' SCOPE: '||p_blocks(l_index).SCOPE
1420 ||' MEASURE : '||p_blocks(l_index).MEASURE
1421 ||' START_TIME : '||p_blocks(l_index).START_TIME
1422 ||' STOP_TIME : '||p_blocks(l_index).STOP_TIME
1423 ||' TRANSLATION_DISPLAY_KEY : '||p_blocks(l_index).TRANSLATION_DISPLAY_KEY
1424 ||' APPROVAL_STATUS: '||p_blocks(l_index).APPROVAL_STATUS
1425 ||' APPROVAL_STYLE_ID: '||p_blocks(l_index).APPROVAL_STYLE_ID
1426 );
1427
1428 l_index := p_blocks.next(l_index);
1429 END LOOP;
1430 END IF;
1431
1432
1433 /*
1434 For bug 3220588, we need to rollback in case there have been any
1435 changes in the PA code, which require us to revert. E.g. the
1436 transaction reference has been updated, but the user has clicked
1437 the back button. No code should be updated in the validate phase
1438 in OTL
1439 */
1440 if(p_validate=hxc_timecard.c_yes) then
1441 rollback;
1442 end if;
1443
1444 l_index := p_blocks.FIRST;
1445 LOOP
1446 EXIT
1447 WHEN NOT p_blocks.EXISTS(l_index);
1448
1449 IF p_blocks(l_index).scope LIKE 'TIMECARD%' THEN
1450 l_resource_id := p_blocks(l_index).resource_id;
1451 l_timecard_start_time := to_date(p_blocks(l_index).start_time, 'yyyy/MM/dd HH24:mi:ss');
1452 l_timecard_stop_time := to_date(p_blocks(l_index).stop_time, 'yyyy/MM/dd HH24:mi:ss');
1453 EXIT;
1454 END IF;
1455
1456 l_index := p_blocks.NEXT(l_index);
1457 END LOOP;
1458
1459 fnd_msg_pub.initialize;
1460
1461 hxc_timecard_message_helper.initializeErrors;
1462
1463 p_messages := hxc_message_table_type();
1464
1465 -- Bug 10196997
1466 -- Checking the lock rowid to avoid duplicate sessions.
1467
1468 IF p_lock_rowid IS NOT NULL
1469 THEN
1470 OPEN c_locker_type_requestor (hxc_lock_util.c_self_service,
1471 hxc_lock_util.c_timecard_action);
1472 FETCH c_locker_type_requestor
1473 into l_locker_type_requestor_id;
1474 CLOSE c_locker_type_requestor;
1475
1476
1477 BEGIN
1478
1479 -- Commented out the initial query that was written for bug 8422744
1480 /*
1481 SELECT rowidtochar(loc.rowid)
1482 INTO d_lock_rowid
1483 FROM hxc_locks loc
1484 WHERE loc.resource_id = l_resource_id
1485 AND TRUNC(loc.start_time) = TRUNC(l_timecard_start_time)
1486 AND TRUNC(loc.stop_time) = TRUNC(l_timecard_stop_time);
1487 */
1488
1489 SELECT 1
1490 INTO d_exists
1491 FROM hxc_locks loc
1492 WHERE loc.resource_id = l_resource_id
1493 AND TRUNC(loc.start_time) = TRUNC(l_timecard_start_time)
1494 AND TRUNC(loc.stop_time) = TRUNC(l_timecard_stop_time)
1495 AND lock_date >= SYSDATE - (1/(48))
1496 AND loc.rowid <> p_lock_rowid
1497 AND loc.locker_type_id = l_locker_type_requestor_id
1498 AND ROWNUM < 2 ;
1499
1500
1501 EXCEPTION
1502
1503 WHEN no_data_found THEN
1504 NULL;
1505 END;
1506
1507 IF g_debug
1508 THEN
1509 hr_utility.trace('p_template ::' || p_template);
1510 hr_utility.trace('hxc_timecard.c_yes ::' || hxc_timecard.c_yes);
1511
1512 hr_utility.trace('p_lock_rowid ::' || p_lock_rowid);
1513 hr_utility.trace('d_lock_rowid ::' || d_lock_rowid);
1514 hr_utility.trace('d_exists ::' || d_exists);
1515 END IF;
1516
1517 IF(p_template <> hxc_timecard.c_yes
1518 AND d_exists = 1 ) THEN
1519
1520 hr_utility.trace('THROW HXC_MULTIPLE_SESSION ');
1521
1522 hxc_timecard_message_helper.adderrortocollection(p_messages,
1523 'HXC_MULTIPLE_SESSION',
1524 hxc_timecard.c_error,
1525 NULL, NULL,
1526 hxc_timecard.c_hxc,
1527 NULL,
1528 NULL,
1529 NULL,
1530 NULL);
1531
1532 END IF;
1533
1534 END IF;
1535
1536 if(p_template = hxc_timecard.c_no) then
1537 if(hxc_lock_api.check_lock(p_lock_rowid)) then
1538 l_valid_lock := true;
1539 else
1540 --
1541 -- For bug
1542 -- If the lock is invalid, it might just have timed out, therefore
1543 -- request a new lock.
1544 --
1545 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
1546
1547 if(hxc_timecard_block_utils.is_new_block(p_blocks(l_timecard_index))) then
1548
1549 hxc_lock_api.request_lock
1550 (p_process_locker_type=> hxc_lock_util.c_ss_timecard_action
1551 ,p_resource_id=> p_blocks(l_timecard_index).resource_id
1552 ,p_start_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1553 ,p_stop_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1554 ,p_time_building_block_id=> null
1555 ,p_time_building_block_ovn=> null
1556 ,p_transaction_lock_id=> null
1557 ,p_expiration_time=> 10
1558 ,p_messages=> p_messages
1559 ,p_row_lock_id=> l_lock_rowid
1560 ,p_locked_success=> l_valid_lock
1561 );
1562 else
1563
1564 hxc_lock_api.request_lock
1565 (p_process_locker_type=> hxc_lock_util.c_ss_timecard_action
1566 ,p_resource_id=> p_blocks(l_timecard_index).resource_id
1567 ,p_start_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1568 ,p_stop_time=> fnd_date.canonical_to_date(p_blocks(l_timecard_index).start_time)
1569 ,p_time_building_block_id=> p_blocks(l_timecard_index).time_building_block_id
1570 ,p_time_building_block_ovn=> p_blocks(l_timecard_index).object_version_number
1571 ,p_transaction_lock_id=> null
1572 ,p_expiration_time=> 10
1573 ,p_messages=> p_messages
1574 ,p_row_lock_id=> l_lock_rowid
1575 ,p_locked_success=> l_valid_lock
1576 );
1577 end if;
1578 end if;
1579 end if;
1580
1581 -- OTL - ABS Integration
1582
1583 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
1584
1585 hxc_retrieve_absences.g_lock_row_id := p_lock_rowid;
1586 hxc_retrieve_absences.g_person_id := p_blocks(l_timecard_index).resource_id;
1587 hxc_retrieve_absences.g_start_time := FND_DATE.canonical_to_date(p_blocks(l_timecard_index).start_time);
1588 hxc_retrieve_absences.g_stop_time := FND_DATE.canonical_to_date(p_blocks(l_timecard_index).stop_time);
1589
1590 -- Added for OTL ABS Integration 8888902
1591 -- OTL-ABS START
1592 IF g_debug THEN
1593 hr_utility.trace('ABS> In hxc_timecard.create_timeecard');
1594 hr_utility.trace('ABS> call hxc_retrieve_absences.insert_absence_summary_row');
1595 END IF;
1596
1597 IF (NVL(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
1598 hxc_retrieve_absences.insert_absence_summary_row;
1599 END IF;
1600 -- OTL-ABS END
1601
1602 if((l_valid_lock) OR (p_template = hxc_timecard.c_yes)) then
1603
1604 deposit_controller
1605 (p_validate => p_validate
1606 ,p_blocks => p_blocks
1607 ,p_attributes => p_attributes
1608 ,p_messages => p_messages
1609 ,p_deposit_mode => p_deposit_mode
1610 ,p_template => p_template
1611 ,p_item_type => p_item_type
1612 ,p_approval_prc => p_approval_prc
1613 ,p_cla_save => p_cla_save
1614 ,p_timecard_id => p_timecard_id
1615 ,p_timecard_ovn => p_timecard_ovn
1616 );
1617
1618 if(
1619 ((p_deposit_mode = hxc_timecard.c_save) AND (hxc_timecard_message_helper.noerrors))
1620 OR
1621 ((p_deposit_mode = hxc_timecard.c_submit) AND (p_validate = hxc_timecard.c_no) AND (p_template = hxc_timecard.c_no))
1622 ) then
1623
1624 hxc_lock_api.release_lock
1625 (P_ROW_LOCK_ID => p_lock_rowid
1626 ,P_PROCESS_LOCKER_TYPE => l_process_locker_type
1627 ,P_TRANSACTION_LOCK_ID => null
1628 ,P_RESOURCE_ID => null
1629 ,P_START_TIME => null
1630 ,P_STOP_TIME => null
1631 ,P_TIME_BUILDING_BLOCK_ID => null
1632 ,P_TIME_BUILDING_BLOCK_OVN => null
1633 ,P_MESSAGES => p_messages
1634 ,P_RELEASED_SUCCESS => l_released_success
1635 );
1636
1637 elsif ((p_template = hxc_timecard.c_yes) AND (hxc_timecard_message_helper.noerrors))then
1638 --
1639 -- This is important, because we might have saved
1640 -- a timecard as a template, in which case, we should
1641 -- release the lock that we might have
1642 --
1643 hxc_lock_api.release_lock
1644 (p_row_lock_id => p_lock_rowid);
1645
1646 end if;
1647
1648 else
1649
1650 hxc_timecard_message_helper.addErrorToCollection
1651 (p_messages
1652 ,'HXC_TIMECARD_LOCK_FAILED'
1653 ,hxc_timecard.c_error
1654 ,null
1655 ,null
1656 ,hxc_timecard.c_hxc
1657 ,null
1658 ,null
1659 ,null
1660 ,null
1661 );
1662
1663
1664 end if;
1665
1666 End create_timecard;
1667
1668 Function load_blocks
1669 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1670 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
1671 ,p_load_mode in varchar2 default c_nondelete
1672 ) return hxc_block_table_type is
1673
1674 cursor c_blocks
1675 (p_tc_id in hxc_time_building_blocks.time_building_block_id%type
1676 ) is
1677 select tbb.time_building_block_id
1678 ,tbb.object_version_number
1679 from hxc_time_building_blocks tbb
1680 where tbb.date_to = hr_general.end_of_time
1681 start with (tbb.time_building_block_id = p_tc_id)
1682 connect by prior tbb.time_building_block_id = tbb.parent_building_block_id
1683 and prior tbb.object_version_number = tbb.parent_building_block_ovn;
1684
1685 l_blocks hxc_block_table_type := hxc_block_table_type();
1686 l_block_count number := 1;
1687
1688
1689 Begin
1690
1691 for block_rec in c_blocks(p_timecard_id) loop
1692 l_blocks.extend;
1693 l_blocks(l_block_count) := hxc_timecard_block_utils.build_block
1694 (block_rec.time_building_block_id
1695 ,block_rec.object_version_number);
1696 if(p_load_mode = c_delete) then
1697 --
1698 -- Date effectively end date the block
1699 --
1700 l_blocks(l_block_count).date_to := fnd_date.date_to_canonical(sysdate);
1701 end if;
1702
1703 l_block_count := l_block_count +1;
1704
1705 end loop;
1706
1707 return l_blocks;
1708
1709 End load_blocks;
1710
1711 Function load_attributes
1712 (p_blocks in hxc_block_table_type)
1713 return hxc_attribute_table_type is
1714
1715 cursor c_attributes
1716 (p_building_block_id in hxc_time_building_blocks.time_building_block_id%type
1717 ,p_building_block_ovn in hxc_time_building_blocks.object_version_number%type
1718 ) is
1719 select ta.time_attribute_id
1720 ,ta.attribute_category
1721 ,ta.attribute1
1722 ,ta.attribute2
1723 ,ta.attribute3
1724 ,ta.attribute4
1725 ,ta.attribute5
1726 ,ta.attribute6
1727 ,ta.attribute7
1728 ,ta.attribute8
1729 ,ta.attribute9
1730 ,ta.attribute10
1731 ,ta.attribute11
1732 ,ta.attribute12
1733 ,ta.attribute13
1734 ,ta.attribute14
1735 ,ta.attribute15
1736 ,ta.attribute16
1737 ,ta.attribute17
1738 ,ta.attribute18
1739 ,ta.attribute19
1740 ,ta.attribute20
1741 ,ta.attribute21
1742 ,ta.attribute22
1743 ,ta.attribute23
1744 ,ta.attribute24
1745 ,ta.attribute25
1746 ,ta.attribute26
1747 ,ta.attribute27
1748 ,ta.attribute28
1749 ,ta.attribute29
1750 ,ta.attribute30
1751 ,ta.bld_blk_info_type_id
1752 ,ta.object_version_number
1753 ,bbit.bld_blk_info_type
1754 from hxc_time_attribute_usages tau, hxc_time_attributes ta, hxc_bld_blk_info_types bbit
1755 where tau.time_building_block_id = p_building_block_id
1756 and tau.time_building_block_ovn = p_building_block_ovn
1757 and ta.time_attribute_id = tau.time_attribute_id
1758 and ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
1759
1760 l_attributes hxc_attribute_table_type := hxc_attribute_table_type();
1761
1762 l_block_index number;
1763 l_attribute_index number := 1;
1764
1765 Begin
1766
1767 l_block_index := p_blocks.first;
1768
1769 loop
1770 exit when not p_blocks.exists(l_block_index);
1771
1772 for attribute_rec in
1773 c_attributes(p_blocks(l_block_index).time_building_block_id,p_blocks(l_block_index).object_version_number)
1774 loop
1775
1776 l_attributes.extend;
1777 l_attributes(l_attribute_index) := HXC_ATTRIBUTE_TYPE
1778 (attribute_rec.TIME_ATTRIBUTE_ID
1779 ,p_blocks(l_block_index).time_building_block_id
1780 ,attribute_rec.ATTRIBUTE_CATEGORY
1781 ,attribute_rec.ATTRIBUTE1
1782 ,attribute_rec.ATTRIBUTE2
1783 ,attribute_rec.ATTRIBUTE3
1784 ,attribute_rec.ATTRIBUTE4
1785 ,attribute_rec.ATTRIBUTE5
1786 ,attribute_rec.ATTRIBUTE6
1787 ,attribute_rec.ATTRIBUTE7
1788 ,attribute_rec.ATTRIBUTE8
1789 ,attribute_rec.ATTRIBUTE9
1790 ,attribute_rec.ATTRIBUTE10
1791 ,attribute_rec.ATTRIBUTE11
1792 ,attribute_rec.ATTRIBUTE12
1793 ,attribute_rec.ATTRIBUTE13
1794 ,attribute_rec.ATTRIBUTE14
1795 ,attribute_rec.ATTRIBUTE15
1796 ,attribute_rec.ATTRIBUTE16
1797 ,attribute_rec.ATTRIBUTE17
1798 ,attribute_rec.ATTRIBUTE18
1799 ,attribute_rec.ATTRIBUTE19
1800 ,attribute_rec.ATTRIBUTE20
1801 ,attribute_rec.ATTRIBUTE21
1802 ,attribute_rec.ATTRIBUTE22
1803 ,attribute_rec.ATTRIBUTE23
1804 ,attribute_rec.ATTRIBUTE24
1805 ,attribute_rec.ATTRIBUTE25
1806 ,attribute_rec.ATTRIBUTE26
1807 ,attribute_rec.ATTRIBUTE27
1808 ,attribute_rec.ATTRIBUTE28
1809 ,attribute_rec.ATTRIBUTE29
1810 ,attribute_rec.ATTRIBUTE30
1811 ,attribute_rec.BLD_BLK_INFO_TYPE_ID
1812 ,attribute_rec.OBJECT_VERSION_NUMBER
1813 ,'N'
1814 ,'N'
1815 ,attribute_rec.bld_blk_info_type
1816 ,'N'
1817 ,p_blocks(l_block_index).object_version_number
1818 );
1819
1820 l_attribute_index := l_attribute_index +1;
1821 end loop;
1822 l_block_index := p_blocks.next(l_block_index);
1823 end loop;
1824
1825 return l_attributes;
1826
1827 End load_attributes;
1828
1829 Procedure delete_timecard
1830 (p_mode in varchar2
1831 ,p_template in varchar2
1832 ,p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
1833 ,p_timecard_ok in out nocopy varchar2
1834 ) is
1835
1836 cursor c_timecard_ovn
1837 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
1838 select tbb.object_version_number, tbb.resource_id, tbb.start_time, tbb.stop_time
1839 from hxc_time_building_blocks tbb
1840 where tbb.time_building_block_id = p_timecard_id
1841 and tbb.date_to = hr_general.end_of_time;
1842
1843
1844 -- Bug 13416941
1845 --
1846 cursor c_timecard_attachment_exists
1847 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is
1848 select distinct fad.pk1_value,fad.pk2_value ,fad.pk3_value
1849 from hxc_timecard_summary hts,
1850 fnd_attached_documents fad
1851 where fad.entity_name = 'HXC_TIMECARD_SUMMARY'
1852 and hts.timecard_id = p_timecard_id
1853 and hts.resource_id = fad.pk1_value
1854 and regexp_replace(fnd_date.date_to_canonical(trunc(hts.start_time)),' .*') = fad.pk2_value
1855 and regexp_replace(fnd_date.date_to_canonical(trunc(hts.stop_time)),' .*') = fad.pk3_value
1856 ;
1857
1858 --l_timecard_attachment_exists varchar2(5) := 'N';
1859 l_pk1_value fnd_attached_documents.pk1_value%type;
1860 l_pk2_value fnd_attached_documents.pk2_value%type;
1861 l_pk3_value fnd_attached_documents.pk3_value%type;
1862
1863 l_blocks hxc_block_table_type := hxc_block_table_type();
1864 l_attributes hxc_attribute_table_type := hxc_attribute_table_type();
1865 l_messages hxc_message_table_type := hxc_message_table_type();
1866
1867 l_timecard_blocks hxc_timecard.block_list;
1868 l_day_blocks hxc_timecard.block_list;
1869 l_detail_blocks hxc_timecard.block_list;
1870
1871 l_transaction_info hxc_timecard.transaction_info;
1872 l_timecard_props hxc_timecard_prop_table_type;
1873
1874 l_dummy boolean := true;
1875
1876 l_timecard_ovn hxc_time_building_blocks.object_version_number%type;
1877 l_resource_id hxc_time_building_blocks.resource_id%type;
1878 l_start_time hxc_time_building_blocks.start_time%type;
1879 l_stop_time hxc_time_building_blocks.stop_time%type;
1880 l_timecard_index number;
1881 l_lock_rowid rowid;
1882 l_lock_success boolean;
1883
1884 l_delete_allowed varchar2(5) := 'FALSE';
1885
1886 TC_DEL_EXCEPTION EXCEPTION;
1887
1888 Begin
1889 --
1890 -- Find the corresponding ovn of the timecard
1891 --
1892
1893 open c_timecard_ovn(p_timecard_id);
1894 fetch c_timecard_ovn into l_timecard_ovn, l_resource_id, l_start_time, l_stop_time;
1895 if(c_timecard_ovn%notfound) then
1896 close c_timecard_ovn;
1897 p_timecard_ok := hxc_timecard.c_no;
1898 else
1899
1900 savepoint TC_DEL_SAVEPOINT;
1901 --
1902 -- Timecard is ok, continue.
1903 --
1904 p_timecard_ok := hxc_timecard.c_yes;
1905 close c_timecard_ovn;
1906
1907 --
1908 -- Initialize the message stack
1909 --
1910
1911 fnd_msg_pub.initialize;
1912 hxc_timecard_message_helper.initializeErrors;
1913 --
1914 -- Get the timecard or timecard template blocks and attributes
1915 --
1916
1917 l_blocks := load_blocks(p_timecard_id, l_timecard_ovn, c_delete);
1918 l_attributes := load_attributes(l_blocks);
1919 --
1920 -- Main delete processing
1921 --
1922
1923 l_timecard_index := hxc_timecard_block_utils.find_active_timecard_index(l_blocks);
1924
1925 hxc_timecard_properties.get_preference_properties
1926 (p_validate => hxc_timecard.c_yes
1927 ,p_resource_id => l_blocks(l_timecard_index).resource_id
1928 ,p_timecard_start_time => hxc_timecard_block_utils.date_value(l_blocks(l_timecard_index).start_time)
1929 ,p_timecard_stop_time => hxc_timecard_block_utils.date_value(l_blocks(l_timecard_index).stop_time)
1930 ,p_for_timecard => false
1931 ,p_messages => l_messages
1932 ,p_property_table => l_timecard_props
1933 ,p_timecard_bb_id => p_timecard_id --passs the extra parameter timecard ID
1934 ,p_timecard_bb_ovn => l_timecard_ovn -- pass the extra parameter timecard OVN
1935 );
1936
1937 hxc_timecard_message_helper.processerrors
1938 (p_messages => l_messages);
1939
1940 l_messages.delete;
1941
1942 hxc_timecard_block_utils.sort_blocks
1943 (p_blocks => l_blocks
1944 ,p_timecard_blocks => l_timecard_blocks
1945 ,p_day_blocks => l_day_blocks
1946 ,p_detail_blocks => l_detail_blocks
1947 );
1948
1949 hxc_block_attribute_update.set_process_flags
1950 (p_blocks => l_blocks
1951 ,p_attributes => l_attributes
1952 );
1953
1954 --
1955 -- Don't want to issue this validation for
1956 -- timecard scope. The PA validation will
1957 -- fail.
1958 --
1959
1960 if(l_blocks(l_timecard_index).scope = c_timecard_scope) then
1961
1962 hxc_deposit_checks.perform_process_checks
1963 (p_blocks => l_blocks,
1964 p_attributes => l_attributes,
1965 p_timecard_props => l_timecard_props,
1966 p_days => l_day_blocks,
1967 p_details => l_detail_blocks,
1968 p_template => p_template,
1969 p_deposit_mode => p_mode,
1970 p_messages => l_messages
1971 );
1972
1973 hxc_timecard_message_helper.processerrors
1974 (p_messages => l_messages);
1975
1976 hxc_timecard_validation.deposit_validation
1977 (p_blocks => l_blocks
1978 ,p_attributes => l_attributes
1979 ,p_messages => l_messages
1980 ,p_props => l_timecard_props
1981 ,p_deposit_mode => hxc_timecard.c_submit
1982 ,p_template => p_template
1983 ,p_resubmit => c_delete
1984 ,p_can_deposit => l_dummy
1985 );
1986
1987 hxc_timecard_message_helper.processerrors
1988 (p_messages => l_messages);
1989
1990 elsif(l_blocks(l_timecard_index).scope = c_template_scope) then
1991
1992 hxc_deposit_checks.can_delete_template
1993 (l_blocks(l_timecard_index).time_building_block_id
1994 ,l_messages
1995 );
1996
1997 hxc_timecard_message_helper.processerrors
1998 (p_messages => l_messages);
1999
2000 end if;
2001
2002 if(hxc_timecard_message_helper.noErrors) then
2003
2004 hxc_timecard_deposit.execute
2005 (p_blocks => l_blocks
2006 ,p_attributes => l_attributes
2007 ,p_timecard_blocks => l_timecard_blocks
2008 ,p_day_blocks => l_day_blocks
2009 ,p_detail_blocks => l_detail_blocks
2010 ,p_messages => l_messages
2011 ,p_transaction_info => l_transaction_info
2012 );
2013 hxc_timecard_message_helper.processerrors
2014 (p_messages => l_messages);
2015
2016 /*
2017 Bug 3345143
2018 Removed following call. This is done inside delete_timecard on the
2019 summary api.
2020 hxc_find_notify_aprs_pkg.cancel_previous_notifications(p_timecard_id);
2021 */
2022
2023 if(hxc_timecard_message_helper.noErrors) then
2024 if(l_blocks(l_timecard_index).scope = c_template_scope) then --Only for templates.
2025 hxc_template_summary_api.DELETE_TEMPLATE(l_blocks(l_timecard_index).time_building_block_id);
2026 else --For Timecard.
2027
2028 -- Bug 13416941
2029 -- Delete Attachments if any exists
2030 hr_utility.trace('Start of delete attachments');
2031 OPEN c_timecard_attachment_exists(p_timecard_id);
2032 FETCH c_timecard_attachment_exists INTO l_pk1_value,l_pk2_value,l_pk3_value;
2033 CLOSE c_timecard_attachment_exists;
2034
2035 IF g_debug THEN
2036 hr_utility.trace('Resounce Id ::'||l_pk1_value);
2037 hr_utility.trace('Start Time ::'||l_pk2_value);
2038 hr_utility.trace('Stop Time ::'||l_pk3_value);
2039 END IF;
2040
2041 IF l_pk1_value is not null
2042 THEN
2043 hr_utility.trace('call to delete attachments...fnd_attached_documents2_pkg.delete_attachments');
2044 fnd_attached_documents2_pkg.delete_attachments
2045 ( X_entity_name => 'HXC_TIMECARD_SUMMARY'
2046 , X_pk1_value => l_pk1_value
2047 , X_pk2_value => l_pk2_value
2048 , X_pk3_value => l_pk3_value
2049 , X_delete_document_flag => 'Y');
2050 END IF;
2051 hr_utility.trace('End of delete attachments');
2052 -- End of Delete Attachments
2053
2054
2055 hxc_timecard_summary_api.delete_timecard
2056 (p_blocks => l_blocks
2057 ,p_timecard_id => p_timecard_id
2058 );
2059
2060 hxc_timecard_audit.audit_deposit
2061 (p_transaction_info => l_transaction_info
2062 ,p_messages => l_messages
2063 );
2064
2065 hxc_timecard_audit.maintain_latest_details
2066 (p_blocks => l_blocks );
2067
2068 /* Bug 8888904 */
2069 hxc_timecard_audit.maintain_rdb_snapshot
2070 (p_blocks => l_blocks,
2071 p_attributes => l_attributes);
2072
2073
2074 hxc_timecard_message_helper.processerrors
2075 (p_messages => l_messages);
2076
2077
2078
2079 -- OTL-Absences Integration (Bug 8779478)
2080 -- Moved the following code inside a BEGIN-EXCEPTION-END block to handle exceptions effectively
2081 -- for Bug 8888138
2082 BEGIN
2083 IF (nvl(fnd_profile.value('HR_ABS_OTL_INTEGRATION'), 'N') = 'Y') THEN
2084 IF (p_timecard_id > 0 and hxc_timecard_message_helper.noerrors) THEN
2085 IF g_debug THEN
2086 hr_utility.trace('ABS:Initiated Online Retrieval from HXC_TIMECARD.DELETE_TIMECARD');
2087 END IF;
2088
2089 HXC_ABS_RETRIEVAL_PKG.POST_ABSENCES(l_resource_id,
2090 l_start_time,
2091 l_stop_time,
2092 'DELETED',
2093 l_messages);
2094
2095
2096 IF g_debug THEN
2097 hr_utility.trace('ABS:l_messages.COUNT = '||l_messages.COUNT);
2098 END IF;
2099
2100 if l_messages.COUNT > 0 then
2101 IF g_debug THEN
2102 hr_utility.trace('ABS:Error in POST_ABSENCES');
2103 END IF;
2104 raise TC_DEL_EXCEPTION;
2105 end if;
2106
2107 END IF;
2108 END IF;
2109
2110 EXCEPTION
2111 WHEN TC_DEL_EXCEPTION THEN
2112 IF g_debug THEN
2113 hr_utility.trace('ABS: Exception TC_DEL_EXCEPTION');
2114 END IF;
2115
2116 rollback to TC_DEL_SAVEPOINT;
2117 hxc_timecard_message_helper.processerrors
2118 (p_messages => l_messages);
2119
2120
2121 END; -- Absences end
2122
2123 end if;
2124 end if;
2125
2126
2127
2128 end if;
2129
2130 hxc_lock_api.release_lock
2131 (p_row_lock_id => l_lock_rowid);
2132
2133 hxc_timecard_message_helper.prepareErrors;
2134
2135 end if; -- Is the timecard ok?
2136
2137 End delete_timecard;
2138
2139 -- Added for DA Enhancement
2140 Procedure delete_null_entries
2141 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
2142 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
2143 )is
2144
2145 cursor c_null_blocks
2146 (p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
2147 ) is
2148
2149 SELECT det.time_building_block_id ,
2150 det.object_version_number
2151 FROM hxc_time_building_blocks DET,
2152 HXC_TIME_BUILDING_BLOCKS DAY
2153 WHERE day.parent_building_block_id = p_timecard_id
2154 AND det.parent_building_block_ovn = day.object_version_number
2155 AND det.parent_building_block_id = day.time_building_block_id
2156 AND det.scope = 'DETAIL'
2157 AND det.measure IS NULL
2158 AND det.start_time IS NULL
2159 AND det.stop_time IS NULL;
2160
2161 TYPE building_blocks_tab IS TABLE of NUMBER ;
2162
2163 bb_id_tab building_blocks_tab;
2164 bb_ovn_tab building_blocks_tab;
2165
2166
2167
2168 Begin
2169
2170
2171 OPEN c_null_blocks(p_timecard_id);
2172
2173 FETCH c_null_blocks
2174 BULK COLLECT INTO bb_id_tab,bb_ovn_tab;
2175
2176 FORALL i IN bb_id_tab.FIRST..bb_id_tab.LAST
2177
2178 DELETE
2179 FROM hxc_time_building_blocks
2180 WHERE time_building_block_id = bb_id_tab(i)
2181 AND object_version_number = bb_ovn_tab(i);
2182
2183
2184 CLOSE c_null_blocks;
2185
2186 End delete_null_entries;
2187
2188 END hxc_timecard;
2189