[Home] [Help]
PACKAGE BODY: APPS.HXC_TIMECARD_DEPOSIT
Source
1 PACKAGE BODY hxc_timecard_deposit AS
2 /* $Header: hxctimedp.pkb 120.19.12020000.5 2013/02/08 09:18:03 pravesk ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 Procedure add_transaction_info
7 (p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
8 ,p_object_version_number in hxc_time_building_blocks.object_version_number%type
9 ,p_exception_desc in varchar2
10 ,p_transaction_info in out nocopy hxc_timecard.transaction_info
11 ,p_messages in out nocopy hxc_message_table_type
12 ) is
13
14 l_index number;
15 l_status varchar2(20);
16
17 Begin
18
19 if(p_time_building_block_id is null) then
20
21 hxc_timecard_message_helper.addErrorToCollection
22 (p_messages
23 ,'HXC_NULL_TRANS_BLOCK'
24 ,hxc_timecard.c_error
25 ,null
26 ,null
27 ,'HXC'
28 ,null
29 ,null
30 ,null
31 ,null
32 );
33
34 else
35
36
37 l_index := p_transaction_info.count + 1;
38
39 if(p_exception_desc is not null) then
40 l_status := hxc_timecard.c_trans_error;
41 else
42 l_status := hxc_timecard.c_trans_success;
43 end if;
44
45 p_transaction_info(l_index).time_building_block_id := p_time_building_block_id;
46 p_transaction_info(l_index).object_version_number := p_object_version_number;
47 p_transaction_info(l_index).exception_desc := p_exception_desc;
48 p_transaction_info(l_index).status := l_status;
49
50 end if;
51
52 End add_transaction_info;
53
54 Procedure deposit_error
55 (p_messages in out nocopy hxc_message_table_type
56 ,p_transaction_info in out nocopy hxc_timecard.transaction_info
57 ,p_time_building_block_id in hxc_time_building_blocks.time_building_block_id%type
58 ,p_time_building_block_ovn in hxc_time_building_blocks.object_version_number%type
59 ) is
60
61 l_exception_desc varchar2(2000);
62
63 Begin
64
65 hr_message.provide_error;
66
67
68 if(hr_message.last_message_name is not null) then
69
70 --
71 -- If we have a specific error, then I think
72 -- we shouldn't associate the blocks in this case
73 -- the reason is that we have no corresponding web beans
74 -- on the page at this point to set the item level errors
75 -- on.
76
77 hxc_timecard_message_helper.addErrorToCollection
78 (p_messages
79 ,hr_message.last_message_name
80 ,hxc_timecard.c_error
81 ,null
82 ,null
83 ,hr_message.last_message_app
84 ,null
85 ,null
86 ,null
87 ,null
88 );
89
90 l_exception_desc := hr_message.get_message_text;
91
92 else
93
94 if(SQLERRM is not null) then
95 -- fix for 3266231, v115.6
96 -- adding 'BLK_AND_CHILDREN' for "MESSAGE_EXTENT"
97 hxc_timecard_message_helper.addErrorToCollection
98 (p_messages
99 ,'HXC_HXT_DEP_VAL_ORAERR'
100 ,hxc_timecard.c_error
101 ,null
102 ,substr('ERROR&' || SQLERRM,1,240)
103 ,'HXC'
104 ,null
105 ,null
106 ,null
107 ,null
108 ,'BLK_AND_CHILDREN'
109 );
110
111 l_exception_desc := substr(SQLERRM,1,2000);
112
113 else
114 -- Unable to determine error from stack or SQLERRM,
115 -- set to internal block deposit error.
116
117 hxc_timecard_message_helper.addErrorToCollection
118 (p_messages
119 ,'HXC_XXXXXX_UNKN_BLOCK_DEP'
120 ,hxc_timecard.c_error
121 ,null
122 ,null
123 ,hxc_timecard.c_hxc
124 ,p_time_building_block_id
125 ,p_time_building_block_ovn
126 ,null
127 ,null
128 );
129
130 l_exception_desc := 'An Unknown error has occurred.'
131 ||' HXC_TIMECARD_DEPOSIT.DEPOSIT_ERROR';
132 end if;
133
134 end if;
135
136 --
137 -- Create some new transaction info for this building block
138 --
139
140 add_transaction_info
141 (p_time_building_block_id => p_time_building_block_id
142 ,p_object_version_number => p_time_building_block_ovn
143 ,p_exception_desc => l_exception_desc
144 ,p_transaction_info => p_transaction_info
145 ,p_messages => p_messages
146 );
147
148 End deposit_error;
149
150 Procedure update_dependent_attributes
151 (p_attributes in out nocopy hxc_attribute_table_type
152 ,p_building_block_id in number
153 ) is
154
155 l_index number;
156 l_attribute hxc_attribute_type;
157
158 Begin
159
160 --
161 -- now do the attributes
162 -- These aren't sorted, so we go through
163 -- them all.
164 --
165 l_index := p_attributes.first;
166 LOOP
167 EXIT WHEN NOT p_attributes.exists(l_index);
168
169 l_attribute := p_attributes(l_index);
170
171 if(hxc_timecard_attribute_utils.is_corresponding_block
172 (p_attribute => l_attribute
173 ,p_block_id => p_building_block_id
174 )
175 ) then
176
177 p_attributes(l_index).process := 'Y';
178
179 end if;
180
181 l_index := p_attributes.next(l_index);
182
183 END LOOP;
184
185 End update_dependent_attributes;
186
187 Function set_child_process
188 (p_block in hxc_block_type)
189 return varchar2 is
190
191 Begin
192
193 if(hxc_timecard_block_utils.is_new_block(p_block)) then
194 --
195 -- Process flag is set to No for new block.
196 -- User probably entered it, then deleted it
197 -- without commit to db inbetween. Don't
198 -- process
199 return 'N';
200 else
201 return 'Y';
202 end if;
203
204 End set_child_process;
205
206 Procedure maintain_error_table
207 (p_messages in out nocopy hxc_message_table_type
208 ,p_old_ta_id in hxc_time_building_blocks.time_building_block_id%type
209 ,p_old_ta_ovn in hxc_time_building_blocks.object_version_number%type
210 ,p_new_ta_id in hxc_time_building_blocks.time_building_block_id%type
211 ,p_new_ta_ovn in hxc_time_building_blocks.object_version_number%type
212 ,p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
213 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
214 ) is
215
216 l_index number;
217
218 Begin
219
220 l_index := p_messages.first;
221
222 Loop
223 Exit when not p_messages.exists(l_index);
224 if((p_messages(l_index).time_attribute_id = p_old_ta_id)
225 AND
226 (p_messages(l_index).time_attribute_ovn = p_old_ta_ovn)) then
227 p_messages(l_index).time_attribute_id := p_new_ta_id;
228 p_messages(l_index).time_attribute_ovn := p_new_ta_ovn;
229 end if;
230 if((p_messages(l_index).time_attribute_id is null)
231 AND
232 (p_messages(l_index).time_building_block_id is null)) then
233 p_messages(l_index).time_building_block_id := p_timecard_id;
234 p_messages(l_index).time_building_block_ovn := p_timecard_ovn;
235 end if;
236 l_index := p_messages.next(l_index);
237 End Loop;
238
239 End maintain_error_table;
240
241 Procedure maintain_error_table
242 (p_messages in out nocopy hxc_message_table_type
243 ,p_old_bb_id in hxc_time_building_blocks.time_building_block_id%type
244 ,p_old_bb_ovn in hxc_time_building_blocks.object_version_number%type
245 ,p_new_bb_id in hxc_time_building_blocks.time_building_block_id%type
246 ,p_new_bb_ovn in hxc_time_building_blocks.object_version_number%type
247 ,p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
248 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
249 ) is
250
251 l_index number;
252
253 Begin
254
255 l_index := p_messages.first;
256
257 Loop
258 Exit when not p_messages.exists(l_index);
259 if((p_messages(l_index).time_building_block_id = p_old_bb_id)
260 AND
261 (nvl(p_messages(l_index).time_building_block_ovn,p_old_bb_ovn) = p_old_bb_ovn)) then
262 p_messages(l_index).time_building_block_id := p_new_bb_id;
263 p_messages(l_index).time_building_block_ovn := p_new_bb_ovn;
264 end if;
265 if(p_messages(l_index).time_building_block_id is null) then
266 p_messages(l_index).time_building_block_id := p_timecard_id;
267 p_messages(l_index).time_building_block_ovn := p_timecard_ovn;
268 end if;
269 l_index := p_messages.next(l_index);
270 End Loop;
271
272 End maintain_error_table;
273
274 Procedure maintain_dependents
275 (p_blocks in out nocopy hxc_block_table_type
276 ,p_attributes in out nocopy hxc_attribute_table_type
277 ,p_messages in out nocopy hxc_message_table_type
278 ,p_block_list in hxc_timecard.block_list
279 ,l_old_bb_id in hxc_time_building_blocks.time_building_block_id%type
280 ,l_old_ovn in hxc_time_building_blocks.object_version_number%type
281 ,l_new_bb_id in hxc_time_building_blocks.time_building_block_id%type
282 ,l_new_ovn in hxc_time_building_blocks.object_version_number%type
283 ,p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
284 ,p_timecard_ovn in hxc_time_building_blocks.object_version_number%type
285 ) is
286
287 l_parent_chk pls_integer;
288 l_index number;
289 l_block hxc_block_type;
290 l_attribute hxc_attribute_type;
291
292 Begin
293 --
294 -- Loop through the specified blocks, looking for the parents
295 --
296 l_index := p_block_list.first;
297 LOOP
298 EXIT WHEN NOT p_block_list.exists(l_index);
299
300 l_block := p_blocks(p_block_list(l_index));
301
302 l_parent_chk := hxc_timecard_block_utils.is_parent_block
303 (p_block => l_block,
304 p_parent_id => l_old_bb_id,
305 p_parent_ovn => l_old_ovn,
306 p_check_id => true
307 );
308
309 if(l_parent_chk = 0)then
310
311 p_blocks(p_block_list(l_index)).parent_building_block_id := l_new_bb_id;
312 p_blocks(p_block_list(l_index)).parent_building_block_ovn := l_new_ovn;
313 if(p_blocks(p_block_list(l_index)).process <> 'Y') then
314 p_blocks(p_block_list(l_index)).process := set_child_process(p_blocks(p_block_list(l_index)));
315 update_dependent_attributes(p_attributes,p_blocks(p_block_list(l_index)).time_building_block_id);
316 end if;
317 elsif(l_parent_chk = 1) then
318 --
319 -- This means the id matched, but the ovn did not.
320 -- This should never happen when depositing a timecard
321 -- Some likely corruption going on, raise error.
322 --
323 hxc_timecard_message_helper.addErrorToCollection
324 (p_messages
325 ,'HXC_366502_INVALID_PARENT_DEP'
326 ,hxc_timecard.c_error
327 ,null
328 ,null
329 ,'HXC'
330 ,l_block.time_building_block_id
331 ,l_block.object_version_number
332 ,null
333 ,null
334 );
335
336 end if;
337
338 l_index := p_block_list.next(l_index);
339 END LOOP;
340 --
341 -- now do the attributes
342 -- These aren't sorted, so we go through
343 -- them all.
344 --
345 l_index := p_attributes.first;
346 LOOP
347 EXIT WHEN NOT p_attributes.exists(l_index);
348
349 --l_attribute := p_attributes(l_index);
350
351 if(hxc_timecard_attribute_utils.is_corresponding_block
352 (p_attribute => p_attributes(l_index)
353 ,p_block_id => l_old_bb_id
354 )
355 ) then
356
357 p_attributes(l_index).building_block_id := l_new_bb_id;
358 p_attributes(l_index).building_block_ovn := l_new_ovn;
359
360 end if;
361
362 l_index := p_attributes.next(l_index);
363
364 END LOOP;
365
366 --
367 -- Lastly the messages. This is done for
368 -- timekeeper, there shouldn't be any messages
369 -- at this point for self service.
370 --
371 maintain_error_table
372 (p_messages => p_messages
373 ,p_old_bb_id => l_old_bb_id
374 ,p_old_bb_ovn => l_old_ovn
375 ,p_new_bb_id => l_new_bb_id
376 ,p_new_bb_ovn => l_new_ovn
377 ,p_timecard_id => p_timecard_id
378 ,p_timecard_ovn => p_timecard_ovn
379 );
380
381 End maintain_dependents;
382
383 Procedure deposit_new_block
384 (p_block in out nocopy HXC_BLOCK_TYPE
385 ,p_old_bb_id out nocopy NUMBER
386 ,p_new_bb_id out nocopy NUMBER
387 ,p_transaction_info in out nocopy hxc_timecard.transaction_info
388 ,p_messages in out nocopy hxc_message_table_type
389 ) is
390
391 l_object_version_number HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE;
392 l_time_building_block_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE := null;
393
394 Begin
395
396 p_old_bb_id := p_block.time_building_block_id;
397
398 --
399 -- Call the block Api to create the new block
400 --
401 hxc_building_block_api.create_building_block
402 (p_effective_date => sysdate
403 ,p_type => p_block.type
404 ,p_measure => p_block.measure
405 ,p_unit_of_measure => p_block.unit_of_measure
406 ,p_start_time => hxc_timecard_block_utils.date_value(p_block.start_time)
407 ,p_stop_time => hxc_timecard_block_utils.date_value(p_block.stop_time)
408 ,p_parent_building_block_id => p_block.parent_building_block_id
409 ,p_parent_building_block_ovn => p_block.parent_building_block_ovn
410 ,p_scope => p_block.scope
411 ,p_approval_style_id => p_block.approval_style_id
412 ,p_approval_status => p_block.approval_status
413 ,p_resource_id => p_block.resource_id
414 ,p_resource_type => p_block.resource_type
415 ,p_comment_text => p_block.comment_text
416 ,p_application_set_id => p_block.application_set_id
417 ,p_translation_display_key => p_block.translation_display_key
418 ,p_time_building_block_id => l_time_building_block_id
419 ,p_object_version_number => l_object_version_number
420 );
421 --
422 -- Keep for the out parameter
423 --
424 p_new_bb_id := l_time_building_block_id;
425 --
426 -- Set the values in the structure
427 --
428 p_block.time_building_block_id := p_new_bb_id;
429 p_block.object_version_number := 1;
430
431 Exception
432 when others then
433 --
434 -- Here the save of the building block
435 -- has failed, so we should maintain
436 -- the error. However, we don't reraise
437 -- the error, since that will be handled
438 -- by timekeeper, or in the case of self
439 -- service, the commit will fail, the inserts
440 -- rolledback, and all the errors for all the
441 -- blocks shown to the user.
442
443 deposit_error
444 (p_messages => p_messages
445 ,p_transaction_info => p_transaction_info
446 ,p_time_building_block_id => p_block.time_building_block_id
447 ,p_time_building_block_ovn => p_block.object_version_number
448 );
449
450 End deposit_new_block;
451
452 Procedure deposit_old_block
453 (p_block in out nocopy hxc_block_type,
454 p_old_ovn out nocopy number,
455 p_new_ovn out nocopy number,
456 p_deleted_blocks in out nocopy hxc_timecard.block_list,
457 p_transaction_info in out nocopy hxc_timecard.transaction_info,
458 p_messages in out nocopy hxc_message_table_type
459 ) is
460
461 l_object_version_number hxc_time_building_blocks.object_version_number%type;
462
463 Begin
464 --
465 -- Keep the old ovn for future use
466 --
467 p_old_ovn := p_block.object_version_number;
468 --
469 -- Since we want a history of the timecard, we
470 -- don't update the row, rather create a new
471 -- block, then update it with the old
472 -- id and ovn +1 - currently the block API
473 -- handles this for us, based on the value
474 -- passed in the time_building_block_id
475 -- parameter!
476 -- Call the API, with the real values!
477 --
478
479
480 if(hxc_timecard_block_utils.is_active_block(p_block)) then
481
482 hxc_building_block_api.create_building_block
483 (p_effective_date => sysdate
484 ,p_type => p_block.type
485 ,p_measure => p_block.measure
486 ,p_unit_of_measure => p_block.unit_of_measure
487 ,p_start_time => hxc_timecard_block_utils.date_value(p_block.start_time)
488 ,p_stop_time => hxc_timecard_block_utils.date_value(p_block.stop_time)
489 ,p_parent_building_block_id => p_block.parent_building_block_id
490 ,p_parent_building_block_ovn => p_block.parent_building_block_ovn
491 ,p_scope => p_block.scope
492 ,p_approval_style_id => p_block.approval_style_id
493 ,p_approval_status => p_block.approval_status
494 ,p_resource_id => p_block.resource_id
495 ,p_resource_type => p_block.resource_type
496 ,p_comment_text => p_block.comment_text
497 ,p_application_set_id => p_block.application_set_id
498 ,p_translation_display_key => p_block.translation_display_key
499 ,p_time_building_block_id => p_block.time_building_block_id
500 ,p_object_version_number => l_object_version_number
501 );
502
503 else
504
505 hxc_building_block_api.delete_building_block
506 (p_object_version_number => l_object_version_number
507 ,p_time_building_block_id => p_block.time_building_block_id
508 ,p_effective_date => sysdate
509 ,p_application_set_id => p_block.application_set_id
510 );
511 --
512 -- Record this as a deleted block
513 --
514 p_deleted_blocks(p_block.time_building_block_id) := p_block.object_version_number;
515
516 end if;
517 --
518 -- Keep for the out parameter
519 --
520 p_new_ovn := l_object_version_number;
521 --
522 -- Set the values in the structure
523 --
524 p_block.object_version_number := l_object_version_number;
525
526 Exception
527 when others then
528 --
529 -- Here the save of the building block
530 -- has failed, so we should maintain
531 -- the error. However, we don't reraise
532 -- the error, since that will be handled
533 -- by timekeeper, or in the case of self
534 -- service, the commit will fail, the inserts
535 -- rolledback, and all the errors for all the
536 -- blocks shown to the user.
537
538 deposit_error
539 (p_messages => p_messages
540 ,p_transaction_info => p_transaction_info
541 ,p_time_building_block_id => p_block.time_building_block_id
542 ,p_time_building_block_ovn => p_block.object_version_number
543 );
544
545 End deposit_old_block;
546
547 Procedure deposit_timecard_blocks
548 (p_blocks in out nocopy hxc_block_table_type,
549 p_attributes in out nocopy hxc_attribute_table_type,
550 p_timecard_blocks in hxc_timecard.block_list,
551 p_day_blocks in hxc_timecard.block_list,
552 p_deleted_blocks in out nocopy hxc_timecard.block_list,
553 p_transaction_info in out nocopy hxc_timecard.transaction_info,
554 p_messages in out nocopy hxc_message_table_type
555 ) is
556
557 l_index NUMBER;
558 l_block HXC_BLOCK_TYPE;
559
560 l_new_bb_id hxc_time_building_blocks.time_building_block_id%type;
561 l_old_bb_id hxc_time_building_blocks.time_building_block_id%type;
562 l_new_ovn hxc_time_building_blocks.object_version_number%type;
563 l_old_ovn hxc_time_building_blocks.object_version_number%type;
564
565 Begin
566
567 l_index := p_timecard_blocks.first;
568
569 LOOP
570 EXIT WHEN NOT p_timecard_blocks.exists(l_index);
571
572 l_block := p_blocks(p_timecard_blocks(l_index));
573 -- if(p_messages.count < 1) then
574 if(hxc_timecard_block_utils.process_block(l_block)) then
575 if(hxc_timecard_block_utils.is_timecard_block(l_block)) then
576
577 if(hxc_timecard_block_utils.is_new_block(l_block)) then
578 deposit_new_block(l_block,l_old_bb_id,l_new_bb_id,p_transaction_info,p_messages);
579 if(l_new_bb_id is not null) then
580 add_transaction_info(l_new_bb_id,1,null,p_transaction_info,p_messages);
581 end if;
582 p_blocks(p_timecard_blocks(l_index)) := l_block;
583 maintain_dependents
584 (p_blocks
585 ,p_attributes
586 ,p_messages
587 ,p_day_blocks
588 ,l_old_bb_id
589 ,1
590 ,l_new_bb_id
591 ,1
592 ,l_new_bb_id
593 ,1
594 );
595 else
596 deposit_old_block(l_block,l_old_ovn,l_new_ovn,p_deleted_blocks,p_transaction_info,p_messages);
597 add_transaction_info(l_block.time_building_block_id,l_new_ovn,null,p_transaction_info,p_messages);
598 p_blocks(p_timecard_blocks(l_index)) := l_block;
599 maintain_dependents
600 (p_blocks
601 ,p_attributes
602 ,p_messages
603 ,p_day_blocks
604 ,l_block.time_building_block_id
605 ,l_old_ovn
606 ,l_block.time_building_block_id
607 ,l_new_ovn
608 ,l_block.time_building_block_id
609 ,l_new_ovn
610 );
611 end if;
612
613 end if;
614 end if;
615 -- end if; -- only want to continue processing while there are no errors.
616 l_index := p_timecard_blocks.next(l_index);
617
618 END LOOP;
619
620 End deposit_timecard_blocks;
621
622 Procedure deposit_day_blocks
623 (p_blocks in out nocopy hxc_block_table_type,
624 p_attributes in out nocopy hxc_attribute_table_type,
625 p_day_blocks in hxc_timecard.block_list,
626 p_detail_blocks in hxc_timecard.block_list,
627 p_deleted_blocks in out nocopy hxc_timecard.block_list,
628 p_transaction_info in out nocopy hxc_timecard.transaction_info,
629 p_messages in out nocopy hxc_message_table_type,
630 p_timecard_id in number,
631 p_timecard_ovn in number
632 ) is
633
634 l_index NUMBER;
635 l_block HXC_BLOCK_TYPE;
636
637 l_new_bb_id hxc_time_building_blocks.time_building_block_id%type;
638 l_old_bb_id hxc_time_building_blocks.time_building_block_id%type;
639 l_new_ovn hxc_time_building_blocks.object_version_number%type;
640 l_old_ovn hxc_time_building_blocks.object_version_number%type;
641
642 Begin
643
644 l_index := p_day_blocks.first;
645
646 LOOP
647 EXIT WHEN NOT p_day_blocks.exists(l_index);
648
649 l_block := p_blocks(p_day_blocks(l_index));
650
651 if(hxc_timecard_block_utils.process_block(l_block)) then
652 if(hxc_timecard_block_utils.is_day_block(l_block)) then
653
654 if(hxc_timecard_block_utils.is_new_block(l_block)) then
655 deposit_new_block(l_block,l_old_bb_id,l_new_bb_id,p_transaction_info,p_messages);
656 add_transaction_info(l_new_bb_id,1,null,p_transaction_info,p_messages);
657 p_blocks(p_day_blocks(l_index)) := l_block;
658 maintain_dependents
659 (p_blocks
660 ,p_attributes
661 ,p_messages
662 ,p_detail_blocks
663 ,l_old_bb_id
664 ,1
665 ,l_new_bb_id
666 ,1
667 ,p_timecard_id
668 ,p_timecard_ovn
669 );
670 else
671 deposit_old_block(l_block,l_old_ovn,l_new_ovn,p_deleted_blocks,p_transaction_info,p_messages);
672 add_transaction_info(l_block.time_building_block_id,l_new_ovn,null,p_transaction_info,p_messages);
673 p_blocks(p_day_blocks(l_index)) := l_block;
674 maintain_dependents
675 (p_blocks
676 ,p_attributes
677 ,p_messages
678 ,p_detail_blocks
679 ,l_block.time_building_block_id
680 ,l_old_ovn
681 ,l_block.time_building_block_id
682 ,l_new_ovn
683 ,p_timecard_id
684 ,p_timecard_ovn
685 );
686 end if;
687
688 end if;
689 end if;
690 l_index := p_day_blocks.next(l_index);
691
692 END LOOP;
693
694 End deposit_day_blocks;
695
696 Function is_duplicate_block(p_blocks in HXC_BLOCK_TABLE_TYPE,
697 p_block_new in HXC_BLOCK_TYPE)
698 return boolean
699 IS
700
701 l_index number;
702 isDuplicate boolean := false;
703
704 Begin
705
706 IF g_debug
707 THEN
708 hr_utility.trace(' Entering is_duplicate_block .. ');
709 hr_utility.trace(' p_block_new.time_building_block_id is : ' || p_block_new.time_building_block_id);
710 hr_utility.trace(' In is_duplicate_block p_block_new.translation_display_key is : ' || p_block_new.translation_display_key);
711 END IF;
712
713 l_index := p_blocks.first;
714
715 LOOP
716 EXIT WHEN not p_blocks.exists(l_index) or isDuplicate;
717
718 IF g_debug
719 THEN
720 hr_utility.trace(' l_index is : ' || l_index);
721 hr_utility.trace(' time_building_block_id is : ' || p_blocks(l_index).time_building_block_id);
722 hr_utility.trace(' scope is : ' || p_blocks(l_index).scope);
723 hr_utility.trace(' translation_display_key is : ' || p_blocks(l_index).translation_display_key);
724 hr_utility.trace(' parent_building_block_id is : ' || p_blocks(l_index).parent_building_block_id);
725 hr_utility.trace(' parent_building_block_ovn is : ' || p_blocks(l_index).parent_building_block_ovn);
726 hr_utility.trace(' date_to is : ' || p_blocks(l_index).date_to);
727 END IF;
728
729
730 if(p_blocks(l_index).scope = 'DETAIL'
731 AND p_blocks(l_index).time_building_block_id <> p_block_new.time_building_block_id
732 AND p_blocks(l_index).date_to = fnd_date.date_to_canonical(hr_general.end_of_time)
733 AND p_blocks(l_index).parent_building_block_id = p_block_new.parent_building_block_id
734 AND p_blocks(l_index).translation_display_key is NOT NULL
735 AND p_block_new.translation_display_key is NOT NULL
736 AND p_blocks(l_index).translation_display_key = p_block_new.translation_display_key
737 )
738 then
739 hr_utility.trace(' DUPLICATE is TRUE ');
740 isDuplicate := true;
741 end if;
742
743 l_index := p_blocks.next(l_index);
744 END LOOP;
745
746 if(NOT isDuplicate) then
747 hr_utility.trace(' DUPLICATE is FALSE ');
748 end if;
749
750 return isDuplicate;
751
752 End is_duplicate_block;
753
754 /*
755
756 Added for Bug 14478207.
757 Added functions check_for_wrong_line and check_for_wrong_bb to caught the corruption
758 in translation display key.
759 ie., detail building block is mapped to wrong attribute.
760 */
761
762 FUNCTION check_for_wrong_line(p_blocks in hxc_block_table_type,
763 p_attributes in hxc_attribute_table_type)
764 RETURN BOOLEAN
765 IS
766 l_wrong_line BOOLEAN := FALSE;
767
768 TYPE r_row IS RECORD
769 (
770 det_row number,
771 processed varchar2(2)
772
773 );
774 TYPE t_row IS TABLE OF r_row INDEX BY BINARY_INTEGER;
775 l_det_tab_row t_row;
776
777 TYPE r_bb IS RECORD
778 (
779 det_bb number,
780 det_ovn number
781 );
782 TYPE t_bb IS TABLE OF r_bb INDEX BY BINARY_INTEGER;
783 l_det_tab_bb t_bb;
784
785 l_index_row NUMBER;
786 l_index_bb NUMBER;
787
788 l_det_row_mark_processed NUMBER;
789 l_det_row NUMBER;
790
791 l_tdk VARCHAR2(50);
792
793 FUNCTION check_for_wrong_bb(p_bb_table in t_bb,
794 p_attributes in hxc_attribute_table_type)
795 RETURN BOOLEAN
796 IS
797
798 l_wrong_bb BOOLEAN := FALSE;
799 l_project_id VARCHAR2(250);
800 l_index_attribute NUMBER;
801
802 BEGIN
803
804 IF g_debug THEN
805 hr_utility.trace('In function check_for_wrong_bb');
806 END IF;
807
808 l_index_attribute := p_attributes.first;
809 LOOP -- itterate attributes and get project attached to bb
810 EXIT WHEN NOT p_attributes.exists(l_index_attribute);
811
812 IF g_debug THEN
813 hr_utility.trace('p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY ::'
814 ||p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY);
815 hr_utility.trace('p_attributes(l_index_attribute).BUILDING_BLOCK_ID ::'
816 ||p_attributes(l_index_attribute).BUILDING_BLOCK_ID);
817 hr_utility.trace('p_attributes(l_index_attribute).BUILDING_BLOCK_OVN ::'
818 ||p_attributes(l_index_attribute).BUILDING_BLOCK_OVN);
819 END IF;
820
821 IF p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY = 'PROJECTS'
822 AND p_bb_table.exists(p_attributes(l_index_attribute).BUILDING_BLOCK_ID)
823 AND p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_bb
824 = p_attributes(l_index_attribute).BUILDING_BLOCK_ID
825 /*AND p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_ovn
826 = p_attributes(l_index_attribute).BUILDING_BLOCK_OVN*/
827 THEN
828
829 l_project_id := p_attributes(l_index_attribute).attribute1;
830
831 IF g_debug THEN
832 hr_utility.trace('Project ::'||l_project_id
833 ||' is attached to one of the building block ::'||p_attributes(l_index_attribute).BUILDING_BLOCK_ID);
834 END IF;
835
836 EXIT;
837
838 END IF;
839 l_index_attribute := p_attributes.next(l_index_attribute);
840 END LOOP; -- itterate attributes and get project
841
842 IF g_debug THEN
843 hr_utility.trace('Check whether the same project ::'||l_project_id
844 ||' is attached to all building blocks on the row');
845 END IF;
846
847 l_index_attribute := p_attributes.first;
848 LOOP -- itterate attributes and get project attached to bb
849 EXIT WHEN NOT p_attributes.exists(l_index_attribute) or l_wrong_bb;
850
851 IF g_debug THEN
852 hr_utility.trace('p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY ::'
853 ||p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY);
854
855 hr_utility.trace('p_attributes(l_index_attribute).BUILDING_BLOCK_ID ::'
856 ||p_attributes(l_index_attribute).BUILDING_BLOCK_ID);
857 hr_utility.trace('p_attributes(l_index_attribute).BUILDING_BLOCK_OVN ::'
858 ||p_attributes(l_index_attribute).BUILDING_BLOCK_OVN);
859 END IF;
860
861 IF p_bb_table.exists(p_attributes(l_index_attribute).BUILDING_BLOCK_ID)
862 THEN
863 hr_utility.trace('p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_ovn ::'
864 ||p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_ovn);
865 hr_utility.trace('p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_bb ::'
866 ||p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_bb);
867 END IF;
868
869 IF p_attributes(l_index_attribute).ATTRIBUTE_CATEGORY = 'PROJECTS'
870 AND p_bb_table.exists(p_attributes(l_index_attribute).BUILDING_BLOCK_ID)
871 AND p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_bb
872 = p_attributes(l_index_attribute).BUILDING_BLOCK_ID
873 /*AND p_bb_table(p_attributes(l_index_attribute).BUILDING_BLOCK_ID).det_ovn
874 = p_attributes(l_index_attribute).BUILDING_BLOCK_OVN*/
875 THEN
876 IF l_project_id <> p_attributes(l_index_attribute).attribute1
877 THEN
878 IF g_debug THEN
879 hr_utility.trace('Found missmatch for attribute ::'||p_attributes(l_index_attribute).TIME_ATTRIBUTE_ID);
880 hr_utility.trace('Project attached to attribute is ::'||p_attributes(l_index_attribute).attribute1);
881 END IF;
882 l_wrong_bb := TRUE;
883 END IF;
884 END IF;
885
886 l_index_attribute := p_attributes.next(l_index_attribute);
887 END LOOP; -- itterate attributes and get project
888
889 RETURN l_wrong_bb;
890
891 End check_for_wrong_bb;
892
893
894 BEGIN
895 IF g_debug THEN
896 hr_utility.trace('In function check_for_wrong_line');
897 END IF;
898
899 l_index_row := p_blocks.first;
900 LOOP -- itterate blocks and get unporcessed detail row
901 EXIT WHEN NOT p_blocks.exists(l_index_row) or l_wrong_line;
902
903 IF p_blocks(l_index_row).SCOPE = 'DETAIL'
904 AND instr(p_blocks(l_index_row).date_to,'4712',1,1) > 0
905 THEN
906 l_tdk := p_blocks(l_index_row).TRANSLATION_DISPLAY_KEY;
907
908 l_det_row := Substr (l_tdk,
909 Instr (l_tdk, '|', 1,
910 1)
911 + 1,
912 Instr (l_tdk, '|', 1, 2) - (
913 Instr (l_tdk, '|', 1, 1)
914 + 1 ));
915 IF l_det_tab_row.exists(l_det_row)
916 THEN
917 NULL;
918 hr_utility.trace('l_det_row ::'||l_det_row||'is processed..');
919 ELSE
920
921 l_det_tab_row(l_det_row).det_row := l_det_row;
922 l_det_tab_row(l_det_row).processed := 'N';
923
924 IF g_debug THEN
925 hr_utility.trace('l_det_tab_row(l_det_row).det_row ::'||l_det_tab_row(l_det_row).det_row);
926 hr_utility.trace('l_det_tab_row(l_det_row).processed ::'||l_det_tab_row(l_det_row).processed);
927 END IF;
928
929
930 -- reset det bb table of each row
931 l_det_tab_bb.delete;
932
933 -- start get bb's of unprocessed row
934 l_index_bb := p_blocks.first;
935 LOOP -- itterate attributes and get bb of det_row
936 EXIT WHEN NOT p_blocks.exists(l_index_bb);
937
938 IF p_blocks(l_index_bb).SCOPE = 'DETAIL'
939 AND instr(p_blocks(l_index_bb).date_to,'4712',1,1) > 0
940 THEN
941 l_tdk := p_blocks(l_index_bb).TRANSLATION_DISPLAY_KEY;
942
943 l_det_row := Substr (l_tdk,
944 Instr (l_tdk, '|', 1,
945 1)
946 + 1,
947 Instr (l_tdk, '|', 1, 2) - (
948 Instr (l_tdk, '|', 1, 1)
949 + 1 ));
950
951 IF l_det_tab_row.exists(l_det_row)
952 THEN
953
954 IF l_det_tab_row(l_det_row).det_row = l_det_row
955 AND l_det_tab_row(l_det_row).processed = 'N'
956 THEN
957
958 l_det_row_mark_processed := l_det_row;
959 l_det_tab_bb(p_blocks(l_index_bb).TIME_BUILDING_BLOCK_ID).det_bb
960 := p_blocks(l_index_bb).TIME_BUILDING_BLOCK_ID;
961 l_det_tab_bb(p_blocks(l_index_bb).TIME_BUILDING_BLOCK_ID).det_ovn
962 := p_blocks(l_index_bb).OBJECT_VERSION_NUMBER;
963 IF g_debug THEN
964 hr_utility.trace('l_det_row_mark_processed ::'||l_det_row_mark_processed);
965 hr_utility.trace('p_blocks(l_index_bb).TIME_BUILDING_BLOCK_ID ::'
966 || p_blocks(l_index_bb).TIME_BUILDING_BLOCK_ID);
967 hr_utility.trace('p_blocks(l_index_bb).OBJECT_VERSION_NUMBER ::'
968 ||p_blocks(l_index_bb).OBJECT_VERSION_NUMBER);
969 END IF;
970
971 END IF; -- IF l_det_tab_row(l_det_row).det_row = l_det_row
972 END IF;
973 END IF; -- IF p_blocks(l_index_bb).SCOPE = 'DETAIL'
974 l_index_bb := p_blocks.next(l_index_bb);
975 END LOOP; -- itterate blocks and get bb of det_row
976 -- end get bb's of unprocessed row
977
978 IF g_debug THEN
979 hr_utility.trace('call to check_for_wrong_bb');
980 END IF;
981
982 -- call to check bb of a row is mapped to wrong line attributes
983 l_wrong_line := check_for_wrong_bb(l_det_tab_bb,p_attributes);
984
985 IF g_debug THEN
986 IF l_wrong_line
987 THEN
988 hr_utility.trace('Corruption in Line ::'||l_det_row_mark_processed);
989 hr_utility.trace('Stop timecard submittion and raise an error');
990 ELSE
991 hr_utility.trace('NO Corruption in Line ::'||l_det_row_mark_processed||' check next row');
992 END IF;
993 hr_utility.trace('Mark Line ::'||l_det_row_mark_processed||' is processed');
994 END IF;
995
996
997 -- mark processed for row l_det_row_mark_processed
998 l_det_tab_row(l_det_row_mark_processed ).processed := 'Y';
999
1000 END IF; -- IF l_det_tab_row.exists(l_det_row)
1001
1002 END IF; -- IF p_blocks(l_index_row).SCOPE = 'DETAIL'
1003 --
1004 l_index_row := p_blocks.next(l_index_row);
1005 END LOOP; -- itterate attributes and get unporcessed detail row
1006
1007
1008 RETURN l_wrong_line;
1009
1010 End check_for_wrong_line;
1011
1012 Procedure deposit_detail_blocks
1013 (p_blocks in out nocopy hxc_block_table_type,
1014 p_attributes in out nocopy hxc_attribute_table_type,
1015 p_detail_blocks in hxc_timecard.block_list,
1016 p_deleted_blocks in out nocopy hxc_timecard.block_list,
1017 p_transaction_info in out nocopy hxc_timecard.transaction_info,
1018 p_messages in out nocopy hxc_message_table_type,
1019 p_timecard_id in number,
1020 p_timecard_ovn in number
1021 ) is
1022
1023 l_index NUMBER;
1024 l_block HXC_BLOCK_TYPE;
1025 l_list hxc_timecard.block_list;
1026
1027 l_new_bb_id hxc_time_building_blocks.time_building_block_id%type;
1028 l_old_bb_id hxc_time_building_blocks.time_building_block_id%type;
1029 l_new_ovn hxc_time_building_blocks.object_version_number%type;
1030 l_old_ovn hxc_time_building_blocks.object_version_number%type;
1031
1032 l_duplicate_block BOOLEAN := false;
1033 l_overlapping_block BOOLEAN := false;
1034
1035 -- OTL - ABS Integration
1036 l_element NUMBER;
1037
1038 -- Added for Bug 14478207
1039 l_wrong_line BOOLEAN := FALSE;
1040
1041 FUNCTION find_element( p_attributes IN HXC_ATTRIBUTE_TABLE_TYPE,
1042 p_bb_id IN NUMBER)
1043 RETURN NUMBER
1044 IS
1045
1046 l_element NUMBER := 0;
1047 l_ind BINARY_INTEGER;
1048
1049 BEGIN
1050 IF p_attributes.COUNT > 0
1051 THEN
1052 l_ind := p_attributes.FIRST;
1053 LOOP
1054 hr_utility.trace('Ash : Time building_block '||p_attributes(l_ind).building_block_id);
1055 IF p_attributes(l_ind).building_block_id = p_bb_id
1056 AND p_attributes(l_ind).attribute_category LIKE 'ELEMENT%'
1057 THEN
1058 l_element := REPLACE(p_attributes(l_ind).attribute_category,'ELEMENT - ');
1059 EXIT;
1060 END IF;
1061 l_ind := p_attributes.NEXT(l_ind);
1062 EXIT WHEN NOT p_attributes.EXISTS(l_ind);
1063 END LOOP;
1064 END IF;
1065
1066 RETURN l_element;
1067 END find_element;
1068
1069
1070 Begin
1071
1072 IF (nvl(fnd_profile.value('HXC_DEBUG_CHECK_ENABLED'), 'N') = 'Y') THEN -- 8888138
1073
1074 -- Checking for duplicate records for detail blocks
1075 l_index := p_detail_blocks.first;
1076
1077 LOOP
1078 EXIT WHEN NOT p_detail_blocks.exists(l_index) OR l_duplicate_block;
1079
1080 l_block := p_blocks(p_detail_blocks(l_index));
1081
1082 if(hxc_timecard_block_utils.is_active_block(l_block) AND is_duplicate_block(p_blocks, l_block)) then
1083 hr_utility.trace(' DUPLICATE BLOCK is TRUE ');
1084 l_duplicate_block := true;
1085 end if;
1086
1087 l_index := p_detail_blocks.next(l_index);
1088
1089 END LOOP;
1090
1091 -- Checking detail building blocks mapped to wrong line on a timecard for Bug 14478207
1092 IF g_debug THEN
1093 hr_utility.trace('Checking detail building blocks mapped to wrong line .. calling function check_for_wrong_line');
1094 END IF;
1095
1096 l_wrong_line := check_for_wrong_line(p_blocks,p_attributes);
1097
1098 END IF; -- end of IF (nvl(fnd_profile.value('HXC_DEBUG_CHECK_ENABLED'), 'N') = 'Y') THEN
1099
1100 -- Added for Bug 14478207
1101 IF l_wrong_line THEN
1102
1103 IF g_debug THEN
1104 hr_utility.trace('Found Detail Building Block mapped to wrong line in the timecard .. raise an error');
1105 END IF;
1106
1107 hxc_timecard_message_helper.addErrorToCollection
1108 (p_messages
1109 ,'HXC_TBB_WRONG_LINE'
1110 ,hxc_timecard.c_error
1111 ,null
1112 ,null
1113 ,'HXC'
1114 ,null
1115 ,null
1116 ,null
1117 ,null
1118 ,null);
1119
1120 ELSIF(l_duplicate_block) THEN
1121
1122 hr_utility.trace(' Adding error to table for block : ' || l_block.time_building_block_id);
1123 hxc_timecard_message_helper.addErrorToCollection
1124 (p_messages
1125 ,'HXC_DUP_TIME_BUILDING_BLOCKS'
1126 ,hxc_timecard.c_error
1127 ,null
1128 ,null
1129 ,'HXC'
1130 ,null
1131 ,null
1132 ,l_block.time_building_block_id
1133 ,l_block.object_version_number
1134 ,null);
1135
1136 ELSE
1137
1138 -- Depositing detail blocks
1139
1140 l_index := p_detail_blocks.first;
1141
1142 LOOP
1143 EXIT WHEN NOT p_detail_blocks.exists(l_index);
1144
1145 l_block := p_blocks(p_detail_blocks(l_index));
1146
1147 if(hxc_timecard_block_utils.process_block(l_block)) then
1148 if(hxc_timecard_block_utils.is_detail_block(l_block)) then
1149
1150 if(hxc_timecard_block_utils.is_new_block(l_block)) then
1151 if(hxc_timecard_block_utils.is_active_block(l_block)) then
1152 deposit_new_block(l_block,l_old_bb_id,l_new_bb_id,p_transaction_info,p_messages);
1153 -- OTL - ABS Integration
1154 l_element := find_element(p_attributes,l_old_bb_id);
1155 IF g_debug
1156 THEN
1157 hr_utility.trace('ABS : l_element '||l_element);
1158 hr_utility.trace('ABS : time_building_block_id '||l_block.time_building_block_id);
1159 hr_utility.trace('ABS : old time_building_block_id '||l_old_bb_id);
1160 hr_utility.trace('ABS : new time_building_block_id '||l_new_bb_id);
1161 END IF;
1162 hxc_retrieve_absences.update_co_absences(p_old_bb_id => l_old_bb_id,
1163 p_new_bb_id => l_new_bb_id,
1164 p_start_time=> FND_DATE.CANONICAL_TO_DATE(l_block.start_time),
1165 p_stop_time => FND_DATE.CANONICAL_TO_DATE(l_block.stop_time),
1166 p_element_id => l_element);
1167 add_transaction_info(l_new_bb_id,1,null,p_transaction_info,p_messages);
1168 p_blocks(p_detail_blocks(l_index)) := l_block;
1169 maintain_dependents
1170 (p_blocks
1171 ,p_attributes
1172 ,p_messages
1173 ,l_list
1174 ,l_old_bb_id
1175 ,1
1176 ,l_new_bb_id
1177 ,1
1178 ,p_timecard_id
1179 ,p_timecard_ovn
1180 );
1181 end if;
1182 else
1183 deposit_old_block(l_block,l_old_ovn,l_new_ovn,p_deleted_blocks,p_transaction_info,p_messages);
1184 -- OTL - ABS Integration
1185 IF hxc_timecard_block_utils.is_active_block(l_block)
1186 then
1187 l_element := find_element(p_attributes,l_block.time_building_block_id);
1188 IF g_debug
1189 THEN
1190 hr_utility.trace('ABS : l_element '||l_element);
1191 hr_utility.trace('ABS : time_building_block_id '||l_block.time_building_block_id);
1192 hr_utility.trace('ABS l_old_ovn '||l_old_ovn);
1193 hr_utility.trace('ABS l_old_ovn '||l_new_ovn);
1194 END IF;
1195 hxc_retrieve_absences.update_co_absences_ovn(p_old_bb_id => l_block.time_building_block_id,
1196 p_new_ovn => l_new_ovn,
1197 p_start_time => FND_DATE.CANONICAL_TO_DATE(l_block.start_time),
1198 p_stop_time => FND_DATE.CANONICAL_TO_DATE(l_block.stop_time),
1199 p_element_id => l_element);
1200 END IF;
1201 add_transaction_info(l_block.time_building_block_id,l_new_ovn,null,p_transaction_info,p_messages);
1202 p_blocks(p_detail_blocks(l_index)) := l_block;
1203 maintain_dependents
1204 (p_blocks
1205 ,p_attributes
1206 ,p_messages
1207 ,l_list
1208 ,l_block.time_building_block_id
1209 ,l_old_ovn
1210 ,l_block.time_building_block_id
1211 ,l_new_ovn
1212 ,p_timecard_id
1213 ,p_timecard_ovn
1214 );
1215 end if;
1216
1217 end if;
1218 end if;
1219 l_index := p_detail_blocks.next(l_index);
1220
1221 END LOOP;
1222
1223 END IF;
1224
1225 End deposit_detail_blocks;
1226
1227
1228
1229 FUNCTION get_days_to_hours_factor(p_resource_id IN number,
1230 p_evaluation_date IN date,
1231 p_dividing_factor in varchar2)
1232 RETURN NUMBER
1233 is
1234
1235 l_hours NUMBER;
1236 l_day_hours NUMBER;
1237 l_dividing_factor number;
1238 l_pref_frequency varchar2(10);
1239 l_asg_frequency varchar2(10);
1240
1241 CURSOR get_hours(p_reource_id IN number,
1242 p_evaluation_date IN date,
1243 p_dividing_factor IN number)
1244 IS
1245 SELECT normal_hours/p_dividing_factor
1246 FROM per_all_assignments_f
1247 WHERE person_id =p_reource_id
1248 AND assignment_type in ('E','C')
1249 AND primary_flag = 'Y'
1250 AND TRUNC(p_evaluation_date) BETWEEN effective_start_date AND effective_end_Date;
1251
1252 BEGIN
1253
1254 OPEN get_hours(p_resource_id, p_evaluation_date, p_dividing_factor);
1255 FETCH get_hours INTO l_hours;
1256 CLOSE get_hours;
1257
1258 return nvl(round(l_hours,2),1);
1259
1260 END get_days_to_hours_factor;
1261
1262
1263 FUNCTION get_block_index(p_blocks in hxc_block_table_type, p_tbb_id in number)
1264 return number
1265 IS
1266
1267 l_index number;
1268 l_found boolean;
1269 l_block number;
1270 BEGIN
1271
1272 l_index := p_blocks.first;
1273
1274 Loop
1275 Exit when ((not p_blocks.exists(l_index)) or (l_found));
1276
1277 if(p_tbb_id = p_blocks(l_index).time_building_block_id) then
1278 l_found := true;
1279 l_block := l_index;
1280 end if;
1281
1282
1283 l_index := p_blocks.next(l_index);
1284 End Loop;
1285
1286 return l_block;
1287
1288 END get_block_index;
1289
1290 Procedure deposit_attributes
1291 (p_attributes in out nocopy hxc_attribute_table_type,
1292 p_messages in out nocopy hxc_message_table_type,
1293 p_timecard_id in hxc_time_building_blocks.time_building_block_id%type,
1294 p_timecard_ovn in hxc_time_building_blocks.object_version_number%type,
1295 p_deleted_blocks in out nocopy hxc_timecard.block_list,
1296 p_blocks in out nocopy hxc_block_table_type,
1297 p_transaction_info in out nocopy hxc_timecard.transaction_info
1298 ) is
1299
1300 l_index number;
1301 l_attribute hxc_attribute_type;
1302
1303 l_time_attribute_id hxc_time_attributes.time_attribute_id%type;
1304 l_object_version_number hxc_time_attributes.object_version_number%type;
1305
1306 l_conversion_factor number;
1307 p_tco_att hxc_self_service_time_deposit.building_block_attribute_info;
1308 l_tc_id number;
1309 l_pref_table hxc_preference_evaluation.t_pref_table;
1310 l_start_date date;
1311 l_stop_date date;
1312 l_resource_id number;
1313 l_dividing_factor number;
1314 l_active_index number;
1315 p_master_pref_table hxc_preference_evaluation.t_pref_table;
1316 l_update boolean := false;
1317 l_count number;
1318 l_block_index number;
1319 l_old_ovn number;
1320 l_new_ovn number;
1321 l_block HXC_BLOCK_TYPE;
1322 l_list hxc_timecard.block_list;
1323 l_timecard_id number;
1324 l_timecard_ovn number;
1325 l_block_updated number := -999;
1326
1327 l_new_bb_id hxc_time_building_blocks.time_building_block_id%type;
1328 l_old_bb_id hxc_time_building_blocks.time_building_block_id%type;
1329 l_updated_blocks hxc_timecard.block_list;
1330 l_local_index number;
1331
1332 Begin
1333
1334 --***********DAYS Vs HOURS - Start ************
1335 l_active_index := hxc_timecard_block_utils.find_active_timecard_index(p_blocks);
1336 l_timecard_id := p_blocks(l_active_index).time_building_block_id;
1337 l_timecard_ovn := p_blocks(l_active_index).object_version_number;
1338 l_start_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).start_time);
1339 l_stop_date := hxc_timecard_block_utils.date_value(p_blocks(l_active_index).stop_time);
1340 l_resource_id := p_blocks(l_active_index).resource_id;
1341
1342 --Get the Preference value - Time Store Days to Hour Conversion
1343 hxc_preference_evaluation.resource_preferences(p_resource_id => l_resource_id,
1344 p_preference_code => 'TS_PER_DAYS_TO_HOURS',
1345 p_start_evaluation_date => l_start_date,
1346 p_end_evaluation_date => l_stop_date,
1347 p_sorted_pref_table => l_pref_table,
1348 p_master_pref_table => p_master_pref_table );
1349
1350 IF l_pref_table.count > 0 THEN
1351 l_tc_id:=l_pref_table(1).attribute1; --Time Category Identifying Day Elements
1352 l_dividing_factor := l_pref_table(1).attribute2; --Number of Days in Assignment Frequency
1353 END IF;
1354
1355 IF l_tc_id IS NOT NULL THEN
1356
1357 hxc_time_category_utils_pkg.initialise_time_category (
1358 p_time_category_id => l_tc_id
1359 ,p_tco_att => p_tco_att );
1360
1361 l_conversion_factor:= get_days_to_hours_factor(l_resource_id,l_stop_date,l_dividing_factor );
1362
1363 END IF;
1364
1365 --***********DAYS Vs HOURS - End ************
1366
1367 -- Loop over all attributes, and deposit the ones
1368 -- that need it.
1369 --
1370
1371 l_index := p_attributes.first;
1372
1373 LOOP
1374 EXIT WHEN NOT p_attributes.exists(l_index);
1375
1376
1377 --
1378 -- If the attribute should be processed - deposit
1379 -- it.
1380 --
1381
1382 l_attribute := p_attributes(l_index);
1383
1384 --***********DAYS Vs HOURS - Start ************
1385
1386 IF l_tc_id IS NOT NULL THEN
1387
1388 IF (l_attribute.ATTRIBUTE_CATEGORY LIKE 'ELEMENT%' OR
1389 l_attribute.ATTRIBUTE_CATEGORY = 'PROJECTS')THEN -- Process only Payroll and Projects attributes
1390
1391 if l_attribute.process <> hxc_timecard.c_process
1392 and not l_updated_blocks.exists(l_attribute.BUILDING_BLOCK_ID) then
1393
1394 --When the block/atribute not touched, but the value of the Preference changed like
1395 --Time Category chnaged from NULL => TC1 or TC1=> NULL or TC1=>TC2, so in this case
1396 --we need to process the block/attribute to take the new value of preference.
1397
1398 IF hxc_time_category_utils_pkg.chk_tc_bb_ok ( l_attribute.BUILDING_BLOCK_ID )
1399 AND nvl(l_attribute.ATTRIBUTE26,1) <> l_conversion_factor then
1400 l_update := true;
1401 l_attribute.ATTRIBUTE26 :=l_conversion_factor;
1402 elsif NOT hxc_time_category_utils_pkg.chk_tc_bb_ok ( l_attribute.BUILDING_BLOCK_ID )
1403 and nvl(l_attribute.ATTRIBUTE26,1) <> 1 THEN
1404 l_update := true;
1405 l_attribute.ATTRIBUTE26 :=null;
1406
1407 END IF;
1408
1409 end if;
1410
1411 if ( hxc_time_category_utils_pkg.chk_tc_bb_ok ( l_attribute.BUILDING_BLOCK_ID ) ) then
1412 l_attribute.ATTRIBUTE26 :=l_conversion_factor;
1413 else
1414 l_attribute.ATTRIBUTE26:=1;
1415 end if;
1416 END IF;
1417
1418 ELSE
1419 IF (l_attribute.ATTRIBUTE_CATEGORY = 'PROJECTS')THEN
1420 --l_attribute.ATTRIBUTE_CATEGORY LIKE 'ELEMENT%' OR
1421
1422 if l_attribute.process <> hxc_timecard.c_process
1423 and nvl(l_attribute.ATTRIBUTE26,1) <> 1
1424 and not l_updated_blocks.exists(l_attribute.BUILDING_BLOCK_ID)
1425 then
1426 --When the Time category is changed from TC1 => NULL, although the block is not touched
1427 --we must resubmit the block.
1428 l_update := true;
1429
1430 end if;
1431
1432 l_attribute.ATTRIBUTE26 :=null;
1433
1434 END IF;
1435 END IF;
1436
1437 IF l_update THEN
1438
1439 l_update:= false;
1440
1441 l_updated_blocks(l_attribute.BUILDING_BLOCK_ID) := 1;
1442
1443 l_block_index:= get_block_index(p_blocks, l_attribute.BUILDING_BLOCK_ID);
1444 l_block := p_blocks(l_block_index);
1445
1446 deposit_old_block(l_block,l_old_ovn,l_new_ovn,p_deleted_blocks,p_transaction_info,p_messages);
1447
1448 add_transaction_info(l_block.time_building_block_id
1449 ,l_new_ovn,null,p_transaction_info,p_messages);
1450
1451 p_blocks(l_block_index) := l_block;
1452 p_blocks(l_block_index).process := 'Y';
1453
1454 l_local_index := p_attributes.first;
1455
1456 LOOP
1457 EXIT WHEN NOT p_attributes.exists(l_local_index);
1458
1459 if(hxc_timecard_attribute_utils.is_corresponding_block
1460 (p_attribute => p_attributes(l_local_index)
1461 ,p_block_id => l_block.time_building_block_id
1462 )
1463 ) then
1464
1465 p_attributes(l_local_index).building_block_id := l_block.time_building_block_id;
1466 p_attributes(l_local_index).building_block_ovn := l_new_ovn;
1467 p_attributes(l_local_index).process := hxc_timecard.c_process;
1468 end if;
1469
1470 l_local_index := p_attributes.next(l_local_index);
1471
1472 END LOOP;
1473
1474 l_attribute.building_block_id := p_attributes(l_index).building_block_id;
1475 l_attribute.building_block_ovn := p_attributes(l_index).building_block_ovn;
1476 l_attribute.process := p_attributes(l_index).process;
1477
1478 END IF;
1479
1480 --***********DAYS Vs HOURS - End ************
1481
1482 if((hxc_timecard_attribute_utils.process_attribute(p_attribute => l_attribute))
1483 AND
1484 (not p_deleted_blocks.exists(l_attribute.building_block_id))
1485 ) then
1486
1487 hxc_time_attributes_api.create_attribute
1488 (P_ATTRIBUTE_CATEGORY => l_attribute.ATTRIBUTE_CATEGORY,
1489 P_ATTRIBUTE1 => l_attribute.ATTRIBUTE1,
1490 P_ATTRIBUTE2 => l_attribute.ATTRIBUTE2,
1491 P_ATTRIBUTE3 => l_attribute.ATTRIBUTE3,
1492 P_ATTRIBUTE4 => l_attribute.ATTRIBUTE4,
1493 P_ATTRIBUTE5 => l_attribute.ATTRIBUTE5,
1494 P_ATTRIBUTE6 => l_attribute.ATTRIBUTE6,
1495 P_ATTRIBUTE7 => l_attribute.ATTRIBUTE7,
1496 P_ATTRIBUTE8 => l_attribute.ATTRIBUTE8,
1497 P_ATTRIBUTE9 => l_attribute.ATTRIBUTE9,
1498 P_ATTRIBUTE10 => l_attribute.ATTRIBUTE10,
1499 P_ATTRIBUTE11 => l_attribute.ATTRIBUTE11,
1500 P_ATTRIBUTE12 => l_attribute.ATTRIBUTE12,
1501 P_ATTRIBUTE13 => l_attribute.ATTRIBUTE13,
1502 P_ATTRIBUTE14 => l_attribute.ATTRIBUTE14,
1503 P_ATTRIBUTE15 => l_attribute.ATTRIBUTE15,
1504 P_ATTRIBUTE16 => l_attribute.ATTRIBUTE16,
1505 P_ATTRIBUTE17 => l_attribute.ATTRIBUTE17,
1506 P_ATTRIBUTE18 => l_attribute.ATTRIBUTE18,
1507 P_ATTRIBUTE19 => l_attribute.ATTRIBUTE19,
1508 P_ATTRIBUTE20 => l_attribute.ATTRIBUTE20,
1509 P_ATTRIBUTE21 => l_attribute.ATTRIBUTE21,
1510 P_ATTRIBUTE22 => l_attribute.ATTRIBUTE22,
1511 P_ATTRIBUTE23 => l_attribute.ATTRIBUTE23,
1512 P_ATTRIBUTE24 => l_attribute.ATTRIBUTE24,
1513 P_ATTRIBUTE25 => l_attribute.ATTRIBUTE25,
1514 P_ATTRIBUTE26 => l_attribute.ATTRIBUTE26,
1515 P_ATTRIBUTE27 => l_attribute.ATTRIBUTE27,
1516 P_ATTRIBUTE28 => l_attribute.ATTRIBUTE28,
1517 P_ATTRIBUTE29 => l_attribute.ATTRIBUTE29,
1518 P_ATTRIBUTE30 => l_attribute.ATTRIBUTE30,
1519 P_TIME_BUILDING_BLOCK_ID => l_attribute.BUILDING_BLOCK_ID,
1520 P_TBB_OVN => l_attribute.BUILDING_BLOCK_OVN,
1521 P_BLD_BLK_INFO_TYPE_ID => nvl(l_attribute.BLD_BLK_INFO_TYPE_ID,
1522 hxc_timecard_attribute_utils.get_bld_blk_info_type_id(l_attribute.bld_blk_info_type)),
1523 P_TIME_ATTRIBUTE_ID => l_time_attribute_id,
1524 P_OBJECT_VERSION_NUMBER => l_object_version_number
1525 );
1526 --
1527 -- Maintain the errors structure
1528 --
1529 maintain_error_table
1530 (p_messages => p_messages,
1531 p_old_ta_id => p_attributes(l_index).time_attribute_id,
1532 p_old_ta_ovn => p_attributes(l_index).object_version_number,
1533 p_new_ta_id => l_time_attribute_id,
1534 p_new_ta_ovn => l_object_version_number,
1535 p_timecard_id => p_timecard_id,
1536 p_timecard_ovn => p_timecard_ovn
1537 );
1538
1539 --
1540 -- Maintain the structure
1541 --
1542 p_attributes(l_index).time_attribute_id := l_time_attribute_id;
1543 p_attributes(l_index).object_version_number := l_object_version_number;
1544
1545 end if;
1546
1547 l_index := p_attributes.next(l_index);
1548 END LOOP;
1549
1550 End deposit_attributes;
1551
1552
1553 procedure populate_transaction_data_set(p_transaction_info in out nocopy hxc_timecard.transaction_info)
1554 is
1555
1556 cursor c_get_data_set_id(p_tbb_id number,p_tbb_ovn number) is
1557 select data_set_id
1558 from hxc_time_building_blocks
1559 where time_building_block_id = p_tbb_id
1560 and object_version_number = p_tbb_ovn;
1561
1562 l_data_set_id hxc_transaction_details.data_set_id%TYPE;
1563 l_index BINARY_INTEGER;
1564 begin
1565
1566
1567
1568 l_index := p_transaction_info.first;
1569 if l_index is not null then
1570 open c_get_data_set_id(p_transaction_info(l_index).time_building_block_id,
1571 p_transaction_info(l_index).object_version_number);
1572 fetch c_get_data_set_id into l_data_set_id;
1573 close c_get_data_set_id;
1574 end if;
1575
1576 While l_index is not null loop
1577 p_transaction_info(l_index).data_set_id := l_data_set_id;
1578 if g_debug then
1579 hr_utility.trace(p_transaction_info(l_index).time_building_block_id||'-'||p_transaction_info(l_index).data_set_id);
1580 end if;
1581 l_index := p_transaction_info.next(l_index);
1582 End loop;
1583
1584 end populate_transaction_data_set;
1585
1586
1587 Procedure execute
1588 (p_blocks in out nocopy hxc_block_table_type,
1589 p_attributes in out nocopy hxc_attribute_table_type,
1590 p_timecard_blocks in hxc_timecard.block_list,
1591 p_day_blocks in hxc_timecard.block_list,
1592 p_detail_blocks in hxc_timecard.block_list,
1593 p_messages in out nocopy hxc_message_table_type,
1594 p_transaction_info in out nocopy hxc_timecard.transaction_info
1595 ) is
1596
1597 l_timecard_id hxc_time_building_blocks.time_building_block_id%type;
1598 l_timecard_ovn hxc_time_building_blocks.object_version_number%type;
1599
1600 l_deleted_blocks hxc_timecard.block_list;
1601 l_dummy boolean;
1602
1603 cursor c_check_bussiness_group_id(p_person_id number, p_timecard_start_date date) is
1604 SELECT business_group_id,
1605 organization_id
1606 FROM per_all_assignments_f
1607 WHERE person_id = p_person_id
1608 AND assignment_type IN('E', 'C')
1609 AND primary_flag = 'Y'
1610 AND p_timecard_start_date BETWEEN effective_start_date AND effective_end_date;
1611
1612 l_business_group_id per_all_assignments_f.business_group_id%type;
1613 l_organization_id per_all_assignments_f.organization_id%type;
1614
1615
1616 Begin
1617
1618 g_debug := hr_utility.debug_enabled;
1619
1620 -- A new Savepoint has been introduced to makesure we rollback the
1621 -- block transaction incase of any exception being thrown.
1622 savepoint deposit_timecard;
1623
1624
1625 hr_utility.trace('*********************InvalidSecurityContext Trace Start**********************************');
1626 hr_utility.trace('InvalidSecurityContext > PER_BUSINESS_GROUP_ID from fnd profile : '||fnd_profile.value('PER_BUSINESS_GROUP_ID'));
1627 hr_utility.trace('InvalidSecurityContext > ORG_ID from fnd profile : '||fnd_profile.value('ORG_ID'));
1628 hr_utility.trace('InvalidSecurityContext > Resource_id : '|| p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).resource_id);
1629 hr_utility.trace('InvalidSecurityContext > Timecard Start Time :'|| p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).start_time);
1630
1631 open c_check_bussiness_group_id(p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).resource_id,
1632 hxc_timecard_block_utils.date_value(p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).start_time));
1633 fetch c_check_bussiness_group_id into l_business_group_id,l_organization_id;
1634 close c_check_bussiness_group_id;
1635
1636 hr_utility.trace('InvalidSecurityContext > BUSINESS_GROUP_ID of the person : '||l_business_group_id);
1637 hr_utility.trace('InvalidSecurityContext > ORG_ID of the person : '||l_organization_id);
1638
1639 IF l_business_group_id <> fnd_profile.value('PER_BUSINESS_GROUP_ID') THEN
1640
1641 hxc_timecard_message_helper.addErrorToCollection
1642 (p_messages
1643 ,'HXC_366551_INVALID_SEC_CONTEXT' -- You cannot submit this timecard because of invalid security context. Please logout and try again or contact your system administrator.
1644 ,hxc_timecard.c_error
1645 ,null
1646 ,null
1647 ,'HXC'
1648 ,null
1649 ,null
1650 ,null
1651 ,null
1652 );
1653 hr_utility.trace('InvalidSecurityContext >l_business_group_id <> fnd_profile.value(PER_BUSINESS_GROUP_ID) : True');
1654
1655 ELSE
1656 hr_utility.trace('InvalidSecurityContext >l_business_group_id <> fnd_profile.value(PER_BUSINESS_GROUP_ID) : False');
1657 l_deleted_blocks.delete;
1658
1659 -- OTL - ABS Integration
1660 IF hxc_retrieve_absences.g_detail_trans_tab.COUNT > 0
1661 THEN
1662 hxc_retrieve_absences.g_detail_trans_tab.DELETE;
1663 END IF;
1664
1665
1666 -- Blocks have to be in order, to ensure
1667 -- self referential integrity
1668
1669 deposit_timecard_blocks(p_blocks,p_attributes,p_timecard_blocks,p_day_blocks,l_deleted_blocks,p_transaction_info,p_messages);
1670 l_timecard_id := p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).time_building_block_id;
1671 l_timecard_ovn := p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).object_version_number;
1672 deposit_day_blocks(p_blocks,p_attributes,p_day_blocks,p_detail_blocks,l_deleted_blocks,p_transaction_info,p_messages,l_timecard_id,l_timecard_ovn);
1673 deposit_detail_blocks(p_blocks,p_attributes,p_detail_blocks,l_deleted_blocks,p_transaction_info,p_messages,l_timecard_id,l_timecard_ovn);
1674 --
1675 -- And now corresponding attributes
1676 --
1677 hxc_time_category_utils_pkg.push_timecard(p_blocks, p_attributes);
1678
1679 deposit_attributes(p_attributes,p_messages,l_timecard_id,l_timecard_ovn,l_deleted_blocks,
1680 p_blocks, p_transaction_info);
1681 --
1682 -- Maintain the timecard summary structures
1683 populate_transaction_data_set(p_transaction_info);
1684
1685 -- OTL - ABS Integration
1686 IF p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).SCOPE <> hxc_timecard.c_template_scope THEN
1687
1688 hxc_retrieve_absences.manage_retrieval_audit (p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).resource_id,
1689 FND_DATE.canonical_to_date(p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).start_time),
1690 TRUNC(FND_DATE.canonical_to_date(p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).stop_time)));
1691 END IF;
1692
1693
1694
1695 END IF;
1696 hr_utility.trace('*********************InvalidSecurityContext Trace End*************************************');
1697
1698
1699 Exception
1700 When Others then
1701 rollback to deposit_timecard;
1702
1703 --Pickup the last message(HXC_USAGE_DATA_MISSING) that has been set.
1704 hr_message.provide_error;
1705 if (hr_message.last_message_name is not null) then
1706 hxc_timecard_message_helper.addErrorToCollection
1707 (p_messages,
1708 hr_message.last_message_name,
1709 hxc_timecard.c_error,
1710 null,
1711 null,
1712 'HXC',
1713 null,
1714 null,
1715 null,
1716 null
1717 );
1718 else
1719 raise; --If any other exception occurs without errormsg being set.
1720 end if;
1721
1722 End execute;
1723
1724 End hxc_timecard_deposit;