1 PACKAGE BODY hxc_deposit_checks AS
2 /* $Header: hxcdpwrck.pkb 120.10 2011/08/23 13:06:49 bbayragi ship $ */
3 --
4
5 g_debug boolean := hr_utility.debug_enabled;
6
7 -- Types
8 --
9 TYPE asg_dates is RECORD
10 (start_date date
11 ,end_date date
12 );
13
14 Type asg_info is table of asg_dates index by binary_integer;
15
16 Type block_list is table of number index by binary_integer;
17
18 -- Package Variables
19 --
20 g_package varchar2(33) := ' hxc_deposit_checks.';
21
22 --
23 -- ----------------------------------------------------------------------------
24 -- |-------------------------< can_delete_template >--------------------------|
25 -- ----------------------------------------------------------------------------
26 -- {Start Of Comments}
27 --
28 -- Description:
29 -- This function is called by the deposit controller for self service
30 -- timecard deposit to ensure that the template the user is requesting to
31 -- delete is not set as a default template for any user. This should only
32 -- be an issue if the user has set one of their own templates as a default
33 -- or if an administrator has created a public template.
34 --
35 -- Prerequisites:
36 -- The template must exist.
37 --
38 -- In Parameters:
39 -- Name Reqd Type Description
40 -- p_template_id Yes Number The id of the timecard
41 -- template
42 --
43 -- Access Status:
44 -- Internal use only.
45 --
46 -- {End Of Comments}
47 --
48 --
49 Procedure can_delete_template
50 (p_template_id in hxc_time_building_blocks.time_building_block_id%type
51 ,p_messages in out nocopy hxc_message_table_type) is
52
53 cursor c_template_details
54 (p_id in hxc_time_building_blocks.time_building_block_id%type) is
55 select TEMPLATE_TYPE from hxc_template_summary
56 where template_id = p_id;
57
58 l_exists VARCHAR2(6);
59 l_template_type hxc_template_summary.template_type%type;
60 l_attached_public_temp_grps varchar2(1500);
61 l_pref_value VARCHAR2(170);
62
63 Begin
64
65 l_attached_public_temp_grps := NULL;
66
67 open c_template_details(p_template_id);
68 fetch c_template_details into l_template_type;
69 if c_template_details%found then
70
71 if(l_template_type='PUBLIC') then
72 --Check whether the public template can be deleted.
73 l_attached_public_temp_grps := hxc_public_temp_group_comp_api.can_delete_public_template(p_template_id=>p_template_id);
74 end if;
75
76 if(l_attached_public_temp_grps is NULL) then --Carry out the normal operations for private/public templates.
77
78 l_pref_value := l_template_type||'|'||p_template_id;
79
80 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
81 ('TC_W_TMPLT_DFLT_VAL_USR'
82 ,1
83 ,l_pref_value);
84 if ( l_exists <> 0 ) THEN
85 hxc_timecard_message_helper.addErrorToCollection
86 (p_messages
87 ,'HXC_CANT_DEL_TEMPL'
88 ,hxc_timecard.c_error
89 ,null
90 ,null
91 ,hxc_timecard.c_hxc
92 ,null
93 ,null
94 ,null
95 ,null
96 );
97 else
98 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
99 ('TC_W_TMPLT_DFLT_VAL_ADMIN'
100 ,1
101 ,l_pref_value);
102 if ( l_exists <> 0 ) THEN
103 hxc_timecard_message_helper.addErrorToCollection
104 (p_messages
105 ,'HXC_CANT_DEL_TEMPL'
106 ,hxc_timecard.c_error
107 ,null
108 ,null
109 ,hxc_timecard.c_hxc
110 ,null
111 ,null
112 ,null
113 ,null
114 );
115 END if;
116 END IF;
117 else
118
119 -- Public Template Specific
120
121 hxc_timecard_message_helper.addErrorToCollection
122 (p_messages
123 ,'HXC_CANT_DEL_PUB_TEMPL'
124 ,hxc_timecard.c_error
125 ,null
126 ,'GROUPS&'||l_attached_public_temp_grps
127 ,hxc_timecard.c_hxc
128 ,null
129 ,null
130 ,null
131 ,null
132 );
133 end if;
134 end if;
135 close c_template_details;
136
137 End can_delete_template;
138 --
139 -- ----------------------------------------------------------------------------
140 -- |----------------------------< check_inputs >------------------------------|
141 -- ----------------------------------------------------------------------------
142 -- {Start Of Comments}
143 --
144 -- Description:
145 -- This function is called by the deposit controller for self service
146 -- timecard deposit to ensure that the calling process has sent parameters
147 -- that make sense in terms of deposit.
148 --
149 -- Prerequisites:
150 -- None.
151 --
152 -- In Parameters:
153 -- Name Reqd Type Description
154 -- p_blocks Yes BLOCKS The blocks to deposit
155 -- p_atttributes Yes ATTRS The attributes to deposit
156 -- p_deposit_mode Yes Varchar2 The mode of deposit
157 -- p_template Yes Varchar2 Is this a template?
158 -- p_messages Yes MESSAGES The application messages
159 --
160 --
161 -- Access Status:
162 -- Internal use only.
163 --
164 -- {End Of Comments}
165 --
166 --
167 PROCEDURE check_inputs
168 (p_blocks in hxc_block_table_type
169 ,p_attributes in hxc_attribute_table_type
170 ,p_deposit_mode in varchar2
171 ,p_template in varchar2
172 ,p_messages in out nocopy hxc_message_table_type
173 ) is
174
175 l_proc varchar2(70) := g_package||'check_inputs';
176
177 BEGIN
178
179 --
180 -- We must have at least one block to deposit
181 --
182 if(p_blocks.count <1) then
183
184 hxc_timecard_message_helper.addErrorToCollection
185 (p_messages
186 ,'HXC_XXXXX_NO_BLOCKS'
187 ,hxc_timecard.c_error
188 ,null
189 ,null
190 ,hxc_timecard.c_hxc
191 ,null
192 ,null
193 ,null
194 ,null
195 );
196
197 end if;
198
199 --
200 -- The deposit mode must be SUBMIT, SAVE or AUDIT
201 -- (others not currently supported)
202 --
203 if (NOT ((p_deposit_mode=hxc_timecard.c_submit) OR (p_deposit_mode=hxc_timecard.c_save) OR p_deposit_mode=hxc_timecard.c_audit)) then
204
205 hxc_timecard_message_helper.addErrorToCollection
206 (p_messages
207 ,'HXC_XXXXX_INVALID_DEP_MODE'
208 ,hxc_timecard.c_error
209 ,null
210 ,'MODE&'||p_deposit_mode
211 ,hxc_timecard.c_hxc
212 ,null
213 ,null
214 ,null
215 ,null
216 );
217
218 end if;
219
220 --
221 -- We must have a Y or N value for the template
222 --
223 if (NOT ((p_template='Y') OR (p_template='N'))) then
224
225 hxc_timecard_message_helper.addErrorToCollection
226 (p_messages
227 ,'HXC_XXXXX_INVALID_TEMPLATE'
228 ,hxc_timecard.c_error
229 ,null
230 ,'VALUE&'||p_template
231 ,hxc_timecard.c_hxc
232 ,null
233 ,null
234 ,null
235 ,null
236 );
237
238 end if;
239
240 if ((p_template='Y') AND (p_deposit_mode = hxc_timecard.c_save)) then
241
242 hxc_timecard_message_helper.addErrorToCollection
243 (p_messages
244 ,'HXC_XXXXX_INVALID_DEP_MODE'
245 ,hxc_timecard.c_error
246 ,null
247 ,'MODE&'||p_deposit_mode
248 ,hxc_timecard.c_hxc
249 ,null
250 ,null
251 ,null
252 ,null
253 );
254
255 end if;
256
257 END check_inputs;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |----------------------------< audit_checks >------------------------------|
261 -- ----------------------------------------------------------------------------
262 -- {Start Of Comments}
263 --
264 -- Description:
265 -- This procedure ensures that the user has entered all reasons required
266 -- by the instance audit requirements.
267 --
268 -- Prerequisites:
269 -- A table of audit reasons exists
270 --
271 -- In Parameters:
272 -- Name Reqd Type Description
273 -- p_blocks Yes BLOCKS The blocks to deposit
274 -- p_attributes Yes ATTRIBUTES The attributes
275 -- p_messages Yes MESSAGES The failure messages
276 -- /Reason messages
277 --
278 --
279 -- Access Status:
280 -- Internal use only.
281 --
282 -- {End Of Comments}
283 --
284 PROCEDURE audit_checks
285 (p_blocks in hxc_block_table_type
286 ,p_attributes in hxc_attribute_table_type
287 ,p_messages in out nocopy hxc_message_table_type
288 ) is
289
290 l_index number;
291 l_found BOOLEAN;
292 l_att_index number;
293 l_missing_reasons hxc_message_table_type;
294
295 BEGIN
296
297 l_missing_reasons := hxc_message_table_type();
298
299 l_index := p_messages.first;
300
301 LOOP
302 EXIT WHEN NOT p_messages.exists(l_index);
303 IF(p_messages(l_index).message_level = 'REASON') THEN
304 l_found:=TRUE;
305 l_att_index:=p_attributes.first;
306 LOOP
307 EXIT WHEN ((NOT p_attributes.exists(l_att_index)) OR (NOT l_found));
308 if (
309 ( p_attributes(l_att_index).building_block_id=p_messages(l_index).time_building_block_id)
310 and (p_attributes(l_att_index).ATTRIBUTE_CATEGORY = 'REASON')
311 AND (p_messages(l_index).MESSAGE_TOKENS=p_attributes(l_att_index).attribute3)
312 AND (p_attributes(l_att_index).NEW ='Y')
313 AND (p_attributes(l_att_index).attribute1 is not null)
314 ) then
315 l_found:=FALSE;
316 end if;
317 l_att_index:=p_attributes.next(l_att_index);
318 END LOOP;
319
320 IF l_found THEN
321 hxc_timecard_message_helper.addErrorToCollection
322 (p_messages => l_missing_reasons
323 ,p_message_name => 'HXC_REASON_NOT_ENTERED'
324 ,p_message_tokens => NULL
325 ,p_message_level => hxc_timecard.c_error
326 ,p_message_field => NULL
327 ,p_application_short_name => hxc_timecard.c_hxc
328 ,p_time_building_block_id => NULL
329 ,p_time_building_block_ovn => NULL
330 ,p_time_attribute_id => NULL
331 ,p_time_attribute_ovn => NULL
332 );
333 End if;
334 End if;
335 l_index := p_messages.next(l_index);
336 END LOOP;
337
338 p_messages := l_missing_reasons;
339
340 End audit_checks;
341 --
342 -- ----------------------------------------------------------------------------
343 -- |--------------------------< timecard_deleted >----------------------------|
344 -- ----------------------------------------------------------------------------
345 -- {Start Of Comments}
346 --
347 -- Description:
348 -- This function checks that if we have an existing timecard in the same
349 -- period as the one we are attempting to deposit, that the blocks we're
350 -- depositing also cause the existing time store timecard to be date effectively
351 -- deleted. In that case, we can permit deposit.
352 --
353 -- Prerequisites:
354 -- We have found an existing timecard for this period in the time store.
355 --
356 -- In Parameters:
357 -- Name Reqd Type Description
358 -- p_blocks Yes BLOCKS The blocks to deposit
359 -- p_timecard_id Yes NUMBER The id of the existing
360 -- timecard, to check that
361 -- is being deleted with the
362 -- deposit.
363 --
364 -- Return Values:
365 -- FALSE : The existing timecard is NOT being deleted with this submission.
366 -- TRUE : The existing timecard is being deleted with this submission.
367 --
368 -- Access Status:
369 -- Internal use only.
370 --
371 -- {End Of Comments}
372 --
373 --
374 FUNCTION timecard_deleted
375 (p_blocks in HXC_BLOCK_TABLE_TYPE
376 ,p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
377 ) return BOOLEAN is
378
379 l_deleted_tc BOOLEAN := false;
380 l_block_count NUMBER;
381
382 BEGIN
383
384 l_block_count := p_blocks.first;
385
386 LOOP
387 EXIT WHEN l_deleted_tc;
388 EXIT WHEN NOT p_blocks.exists(l_block_count);
389
390 if(p_blocks(l_block_count).time_building_block_id = p_timecard_id) then
391 if(fnd_date.canonical_to_date(p_blocks(l_block_count).date_to) <> hr_general.end_of_time) then
392 l_deleted_tc := true;
393 end if;
394 end if;
395
396 l_block_count := p_blocks.next(l_block_count);
397
398 END LOOP;
399
400 RETURN l_deleted_tc;
401
402 END timecard_deleted;
403 --
404 -- Overloaded version, to call from deposit wrapper.
405 --
406 FUNCTION timecard_deleted
407 (p_blocks in hxc_self_service_time_deposit.timecard_info
408 ,p_timecard_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
409 ) return BOOLEAN is
410
411 l_deleted_tc BOOLEAN := false;
412 l_block_count NUMBER;
413
414 BEGIN
415
416 l_block_count := p_blocks.first;
417
418 LOOP
419 EXIT WHEN l_deleted_tc;
420 EXIT WHEN NOT p_blocks.exists(l_block_count);
421
422 if(p_blocks(l_block_count).time_building_block_id = p_timecard_id) then
423 if(p_blocks(l_block_count).date_to <> hr_general.end_of_time) then
424 l_deleted_tc := true;
425 end if;
426 end if;
427
428 l_block_count := p_blocks.next(l_block_count);
429
430 END LOOP;
431
432 RETURN l_deleted_tc;
433
434 END timecard_deleted;
435 --
439 -- {Start Of Comments}
436 -- ----------------------------------------------------------------------------
437 -- |------------------------< chk_timecard_exists >--------------------------|
438 -- ----------------------------------------------------------------------------
440 --
441 -- Description:
442 -- This function checks that no current timecards exist within the time
443 -- span of the timecard we're attempting to deposit.
444 --
445 -- Prerequisites:
446 -- We have the timecard resource id, start time, stop time and id which
447 -- we are attempting to deposit.
448 --
449 -- In Parameters:
450 -- Name Reqd Type Description
451 -- p_resource_id Yes Number The resource id
452 -- p_start_time Yes Date The proposed start time
453 -- p_stop_time Yes Date The proposed stop time
454 -- p_time_building_block_id Yes Number The id of this timecard
455 --
456 -- Return Values:
457 -- NULL : A timecard does not already exist for this period.
458 -- NON-NULL: The value of the time building block id of the timecard
459 -- that already exists, for use in further checks.
460 --
461 -- Access Status:
462 -- Internal use only.
463 --
464 -- {End Of Comments}
465 --
466 --
467 FUNCTION chk_timecard_exists
468 (p_resource_id in HXC_TIME_BUILDING_BLOCKS.RESOURCE_ID%TYPE
469 ,p_start_time in HXC_TIME_BUILDING_BLOCKS.START_TIME%TYPE
470 ,p_stop_time in HXC_TIME_BUILDING_BLOCKS.STOP_TIME%TYPE
471 ,p_time_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
472 ) RETURN NUMBER is
473
474 l_existing_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE;
475
476 begin
477
478 select tbb.time_building_block_id into l_existing_timecard_id
479 from hxc_time_building_blocks tbb
480 where tbb.resource_id = p_resource_id
481 and tbb.scope = 'TIMECARD'
482 and tbb.date_to = hr_general.end_of_time
483 and tbb.start_time <= p_stop_time
484 and tbb.stop_time >= p_start_time
485 and tbb.time_building_block_id <> p_time_building_block_id;
486
487
488 RETURN l_existing_timecard_id;
489
490 EXCEPTION
491 WHEN others then
492 --
493 -- No data found error, so continue with deposit.
494 --
495 RETURN null;
496
497 END chk_timecard_exists;
498
499 --
500 -- ----------------------------------------------------------------------------
501 -- |------------------------< chk_timecard_deposit >--------------------------|
502 -- ----------------------------------------------------------------------------
503 -- {Start Of Comments}
504 --
505 -- Description:
506 -- This function checks that no current timecards exist within the time
507 -- span of the timecard we're attempting to deposit.
508 --
509 -- Prerequisites:
510 -- The blocks we're attempting to deposit are populated.
511 --
512 -- In Parameters:
513 -- Name Reqd Type Description
514 -- p_blocks Yes BLOCKS The blocks to deposit
515 -- p_block_number Yes number The number of the block
516 -- record to check.
517 --
518 -- Return Values:
519 -- FALSE : No timecard exists within this time period, and deposit can
520 -- continue
521 -- TRUE : A timecard exists, we should raise an error.
522 --
523 -- Access Status:
524 -- Internal use only.
525 --
526 -- {End Of Comments}
527 --
528 --
529 FUNCTION chk_timecard_deposit
530 (p_blocks in HXC_BLOCK_TABLE_TYPE
531 ,p_block_number in NUMBER
532 ) return BOOLEAN is
533
534 l_test_tc_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE;
535
536 l_proc varchar2(72) := g_package||'chk_timceard_deposit';
537
538 BEGIN
539
540 --
541 -- Check that this timecard scope building block is still
542 -- active, and has the right scope - it should do since we
543 -- checked elsewhere, but double check.
544 --
545 if (p_blocks(p_block_number).scope = 'TIMECARD') then
546
547 if (fnd_date.canonical_to_date(p_blocks(p_block_number).date_to) = hr_general.end_of_time) then
548 --
549 -- Run the check
550 --
551 l_test_tc_id := chk_timecard_exists
552 (p_blocks(p_block_number).resource_id
553 ,fnd_date.canonical_to_date(p_blocks(p_block_number).start_time)
554 ,fnd_date.canonical_to_date(p_blocks(p_block_number).stop_time)
555 ,p_blocks(p_block_number).time_building_block_id
556 );
557
558 if(l_test_tc_id is null) then
559 --
560 -- No existing timecard, therefore we can
561 -- continue with the deposit
562 --
563 RETURN FALSE;
564 else
565 --
566 -- There is another timecard out there that overlaps this
567 -- period. Now we check whether this timecard is included
568 -- in the blocks that we are depositing as a deleted block
569 -- in which case this is ok, otherwise, we must stop
570 -- submission.
571 --
572 if(timecard_deleted
573 (p_blocks
574 ,l_test_tc_id
575 )) then
576 --
577 -- We are deleting the existing timecard as part of this
578 -- submission. This is ok, and submission can continue.
579 --
580 RETURN FALSE;
581
582 else
583 --
584 -- We aren't deleting the existing timecard
585 -- as part of this submission. Abort this
586 -- deposit.
587 --
588 RETURN TRUE;
589
590 end if;
591
592 end if;
596 end if; -- Is this a timecard scope block?
593
594 end if; -- is the end date the end of time?
595
597
598 --
599 -- If we get here, then we should deposit this block!
600 --
601
602 RETURN FALSE;
603
604 END chk_timecard_deposit;
605 --
606 -- Overloaded version for calling from deposit wrapper
607 --
608 FUNCTION chk_timecard_deposit
609 (p_blocks in hxc_self_service_time_deposit.timecard_info
610 ,p_block_number in NUMBER
611 ) return BOOLEAN is
612
613 l_test_tc_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE;
614
615 BEGIN
616
617 --
618 -- Check that this timecard scope building block is still
619 -- active, and has the right scope - it should do since we
620 -- checked elsewhere, but double check.
621 --
622 if (p_blocks(p_block_number).scope = 'TIMECARD') then
623
624 if (p_blocks(p_block_number).date_to = hr_general.end_of_time) then
625 --
626 -- Run the check
627 --
628 l_test_tc_id := chk_timecard_exists
629 (p_blocks(p_block_number).resource_id
630 ,p_blocks(p_block_number).start_time
631 ,p_blocks(p_block_number).stop_time
632 ,p_blocks(p_block_number).time_building_block_id
633 );
634
635 if(l_test_tc_id is null) then
636 --
637 -- No existing timecard, therefore we can
638 -- continue with the deposit
639 --
640 RETURN FALSE;
641 else
642 --
643 -- There is another timecard out there that overlaps this
644 -- period. Now we check whether this timecard is included
645 -- in the blocks that we are depositing as a deleted block
646 -- in which case this is ok, otherwise, we must stop
647 -- submission.
648 --
649 if(timecard_deleted
650 (p_blocks
651 ,l_test_tc_id
652 )) then
653 --
654 -- We are deleting the existing timecard as part of this
655 -- submission. This is ok, and submission can continue.
656 --
657 RETURN FALSE;
658
659 else
660 --
661 -- We aren't deleting the existing timecard
662 -- as part of this submission. Abort this
663 -- deposit.
664 --
665 RETURN TRUE;
666
667 end if;
668
669 end if;
670
671 end if; -- is the end date the end of time?
672
673 end if; -- Is this a timecard scope block?
674
675 --
676 -- If we get here, then we should deposit this block!
677 --
678
679 RETURN FALSE;
680
681 END chk_timecard_deposit;
682
683 Function inactive_detail
684 (p_dates in asg_info
685 ,p_date in date
686 ) return BOOLEAN is
687
688 l_inactive boolean := true;
689 l_index number;
690
691 Begin
692
693 l_index := p_dates.first;
694 Loop
695 Exit when ((NOT p_dates.exists(l_index)) OR (NOT l_inactive));
696
697 if(p_date between p_dates(l_index).start_date and p_dates(l_index).end_date) then
698
699 l_inactive := false;
700
701 end if;
702
703 l_index := p_dates.next(l_index);
704 End Loop;
705
706 return l_inactive;
707
708 End inactive_detail;
709
710 Procedure check_start_stop_time
711 (p_blocks in hxc_block_table_type
712 ,p_details in hxc_timecard.block_list
713 ,p_messages in out nocopy hxc_message_table_type
714 ,p_validate_on_save in hxc_pref_hierarchies.attribute1%type
715 ) is
716 l_index number;
717
718 BEGIN
719
720 l_index := p_details.first;
721 Loop
722 Exit When Not p_details.exists(l_index);
723 if(hxc_timecard_block_utils.is_active_block(p_blocks(p_details(l_index)))) then
724
725 IF p_blocks(p_details(l_index)).start_time is not null and
726 p_blocks(p_details(l_index)).stop_time is null and
727 (p_blocks(p_details(l_index)).approval_status = hxc_timecard.c_submitted_status
728 or
729 p_validate_on_save = hxc_timecard.c_yes
730 )
731 THEN
732
733 hxc_timecard_message_helper.addErrorToCollection
734 (p_messages => p_messages
735 ,p_message_name => 'HXC_NULL_STOP_SUBMIT'
736 ,p_message_tokens => NULL
737 ,p_message_level => hxc_timecard.c_error
738 ,p_message_field => NULL
739 ,p_application_short_name => hxc_timecard.c_hxc
740 ,p_time_building_block_id => p_blocks(p_details(l_index)).time_building_block_id
741 ,p_time_building_block_ovn => NULL
742 ,p_time_attribute_id => NULL
743 ,p_time_attribute_ovn => NULL
744 );
745 END IF;
746 end if; -- is this an active detail
747 l_index := p_details.next(l_index);
748 End Loop;
749
750 END check_start_stop_time;
751
752
753 Procedure find_detail_date_extremes
754 (p_blocks in hxc_block_table_type
755 ,p_days in hxc_timecard.block_list
756 ,p_details in hxc_timecard.block_list
757 ,p_early_detail in out nocopy date
758 ,p_late_detail in out nocopy date
759 ) is
760 l_index number;
761 l_detail_date date;
762
763 Begin
764
765 l_index := p_details.first;
766
767 Loop
768 Exit When Not p_details.exists(l_index);
769 if(hxc_timecard_block_utils.is_active_block(p_blocks(p_details(l_index)))) then
770 l_detail_date := trunc(
771 hxc_timecard_block_utils.date_value(
775 )
772 p_blocks(
773 p_days(
774 p_blocks(p_details(l_index)).parent_building_block_id
776 ).start_time
777 ));
778
779 if(l_detail_date < p_early_detail) then
780 p_early_detail := l_detail_date;
781 end if;
782 if(l_detail_date > p_late_detail) then
783 p_late_detail := l_detail_date;
784 end if;
785 end if; -- is this an active detail
786 l_index := p_details.next(l_index);
787 End Loop;
788
789 End find_detail_date_extremes;
790
791 Procedure chk_inactive_details
792 (p_blocks in hxc_block_table_type
793 ,p_props in hxc_timecard_prop_table_type
794 ,p_days in hxc_timecard.block_list
795 ,p_details in hxc_timecard.block_list
796 ,p_block_idxs out nocopy block_list
797 ) is
798
799 l_early_detail date := hr_general.end_of_time;
800 l_late_detail date := hr_general.start_of_time;
801 l_detail_date date;
802 l_asg_info asg_info;
803 l_index number;
804 l_block_index number;
805
806 l_all_active boolean;
807
808 l_proc varchar2(70) := g_package||'chk_inactive_details';
809
810 Begin
811 --
812 -- Calculate earliest and latest detail dates
813 --
814
815 find_detail_date_extremes
816 (p_blocks => p_blocks
817 ,p_days => p_days
818 ,p_details => p_details
819 ,p_early_detail => l_early_detail
820 ,p_late_detail => l_late_detail
821 );
822
823 --
824 -- So 99% Case, check to see if the earliest detail
825 -- and latest detail lie within an active assignment
826 -- period. Loop over the
827 --
828
829 l_all_active := false;
830
831 l_index := p_props.first;
832
833 Loop
834 Exit When ((Not p_props.exists(l_index)) or l_all_active);
835
836 if(p_props(l_index).property_name = 'ResourceAssignmentId') then
837 --
838 -- Check to see if the details are contained within this assignment
839 --
840 if((p_props(l_index).date_from <= l_early_detail)
841 AND
842 (p_props(l_index).date_to >= l_late_detail)) then
843
844 l_all_active := true;
845
846 end if;
847 --
848 -- Keep a record of the dates, in case we need to do the other
849 -- check
850 --
851 l_asg_info(l_index).start_date := p_props(l_index).date_from;
852 l_asg_info(l_index).end_date := p_props(l_index).date_to;
853
854 end if;
855
856 l_index := p_props.next(l_index);
857 End Loop;
858
859 if(NOT l_all_active) then
860 --
861 -- Ok, not all the details corresponded to one
862 -- active assignment. Now look across
863 -- assignments. This is much less likely.
864 --
865 l_all_active := true;
866
867 l_block_index := p_details.first;
868
869 Loop
870 Exit When Not p_details.exists(l_block_index);
871 if(hxc_timecard_block_utils.is_active_block(p_blocks(p_details(l_block_index)))) then
872 l_detail_date := trunc(
873 hxc_timecard_block_utils.date_value(
874 p_blocks(
875 p_days(
876 p_blocks(p_details(l_block_index)).parent_building_block_id
877 )
878 ).start_time
879 ));
880
881 if(inactive_detail(l_asg_info,l_detail_date)) then
882
883 l_all_active := false;
884 p_block_idxs(l_block_index) := l_block_index;
885
886 end if;
887 end if;
888
889 l_block_index := p_details.next(l_block_index);
890 End Loop;
891
892 end if;
893
894 End chk_inactive_details;
895
896 --
897 -- ----------------------------------------------------------------------------
898 -- |---------------------------< perform_checks >-----------------------------|
899 -- ----------------------------------------------------------------------------
900 -- {Start Of Comments}
901 --
902 -- Description:
903 -- This procedure performs all the basic checks on the timecard information
904 -- pre-deposit.
905 --
906 -- Prerequisites:
907 -- Middle tier has sent some blocks, attributes. I.e. we've passed
908 -- check inputs.
909 --
910 -- In Parameters:
911 -- Name Reqd Type Description
912 -- p_blocks Yes BLOCKS The blocks to deposit
913 -- p_atttributes Yes ATTRS The attributes to deposit
914 -- p_messages Yes MESSAGES The application messages
915 --
916 -- Access Status:
917 -- Internal use only.
918 --
919 -- {End Of Comments}
920 --
921 --
922 PROCEDURE perform_checks
923 (p_blocks in hxc_block_table_type
924 ,p_attributes in hxc_attribute_table_type
925 ,p_timecard_props in hxc_timecard_prop_table_type
926 ,p_days in hxc_timecard.block_list
927 ,p_details in hxc_timecard.block_list
928 ,p_messages in out nocopy hxc_message_table_type
929 ) IS
930
931 l_tc_index NUMBER;
932 l_index NUMBER;
933 l_idx NUMBER;
934
935 l_inactive_detail_id hxc_time_building_blocks.time_building_block_id%type;
936 l_inactive_detail_ovn hxc_time_building_blocks.object_version_number%type;
937 l_block_idxs block_list;
938
939 l_proc varchar2(72) := g_package||'perform_checks';
940
941 l_validate_on_save hxc_pref_hierarchies.attribute1%type := hxc_timecard.c_no;
945
942
943
944 BEGIN
946 --
947 -- First find the "active" timecard scope building block
948 -- index.
949 --
950
951 l_tc_index := hxc_timecard_block_utils.find_active_timecard_index
952 (p_blocks => p_blocks);
953
954
955 l_validate_on_save := hxc_timecard_properties.find_property_value
956 (p_timecard_props
957 ,'TsPerValidateOnSaveValidateOnSave'
958 ,null
959 ,null
960 ,fnd_date.canonical_to_date(p_blocks(l_tc_index).start_time)
961 ,fnd_date.canonical_to_date(p_blocks(l_tc_index).stop_time)
962 );
963
964
965 --
966 -- 1. Check that we're allowed to deposit this timecard
967 --
968
969 if (chk_timecard_deposit
970 (p_blocks
971 ,l_tc_index
972 )
973 ) then
974 --
975 -- There is an existing timecard, stop processing.
976 --
977 hxc_timecard_message_helper.addErrorToCollection
978 (p_messages
979 ,'HXC_366333_TIMECARD_EXISTS'
980 ,hxc_timecard.c_error
981 ,null
982 ,null
983 ,hxc_timecard.c_hxc
984 ,null
985 ,null
986 ,null
987 ,null
988 );
989
990 end if;
991
992 --
993 -- 2. Check that all the details have start_time and stop_time in submission
994 --
995 check_start_stop_time
996 (p_blocks => p_blocks
997 ,p_details => p_details
998 ,p_messages => p_messages
999 ,p_validate_on_save => l_validate_on_save
1000 );
1001
1002 --
1003 -- 3. Check that all the details lie within a valid primary
1004 -- assignment range.
1005 --
1006
1007 chk_inactive_details(p_blocks,p_timecard_props,p_days,p_details,l_block_idxs);
1008
1009 if(l_block_idxs.count > 0) then
1010
1011 l_idx := l_block_idxs.first;
1012 Loop
1013 Exit When not l_block_idxs.exists(l_idx);
1014
1015 hxc_timecard_message_helper.addErrorToCollection
1016 (p_messages
1017 ,'HXC_DETAIL_NON_ACTIVE'
1018 ,hxc_timecard.c_error
1019 ,null
1020 ,null
1021 ,hxc_timecard.c_hxc
1022 ,p_blocks(p_details(l_idx)).time_building_block_id
1023 ,p_blocks(p_details(l_idx)).object_version_number
1024 ,null
1025 ,null
1026 );
1027 l_idx := l_block_idxs.next(l_idx);
1028 End Loop;
1029
1030 end if;
1031
1032 END perform_checks;
1033
1034 Function prohibitedAttributeChange
1035 (p_block in hxc_block_type,
1036 p_attributes in hxc_attribute_table_type)
1037 Return Boolean is
1038
1039 cursor c_old_attribute(p_id in hxc_time_attributes.time_attribute_id%type) is
1040 select *
1041 from hxc_time_attributes
1042 where time_attribute_id = p_id;
1043
1044 l_old_attribute c_old_attribute%rowtype;
1045
1046 l_prohibited boolean;
1047 l_index pls_integer;
1048
1049 Begin
1050
1051 l_prohibited := false;
1052 l_index := p_attributes.first;
1053 Loop
1054 Exit when not p_attributes.exists(l_index);
1055
1056
1057 Exit when (
1058 (not p_attributes.exists(l_index))
1059 OR
1060 (l_prohibited)
1061 );
1062 if(p_attributes(l_index).building_block_id = p_block.time_building_block_id) then
1063
1064 open c_old_attribute(p_attributes(l_index).time_attribute_id);
1065 fetch c_old_attribute into l_old_attribute;
1066 close c_old_attribute;
1067
1068 if(l_old_attribute.time_attribute_id is null) then
1069 -- A new attribute. Not allowed.
1070 l_prohibited := true;
1071 else
1072 -- Check for changes.
1073 if(l_old_attribute.attribute_category <> p_attributes(l_index).attribute_category) then
1074 l_prohibited := true;
1075 elsif(nvl(l_old_attribute.attribute1,'A') <> nvl(p_attributes(l_index).attribute1,'A')) then
1076 l_prohibited := true;
1077 elsif(nvl(l_old_attribute.attribute2,'A') <> nvl(p_attributes(l_index).attribute2,'A')) then
1078 l_prohibited := true;
1079 elsif(nvl(l_old_attribute.attribute3,'A') <> nvl(p_attributes(l_index).attribute3,'A')) then
1080 l_prohibited := true;
1081 elsif(nvl(l_old_attribute.attribute4,'A') <> nvl(p_attributes(l_index).attribute4,'A')) then
1082 l_prohibited := true;
1083 elsif(nvl(l_old_attribute.attribute5,'A') <> nvl(p_attributes(l_index).attribute5,'A')) then
1084 l_prohibited := true;
1085 elsif(nvl(l_old_attribute.attribute6,'A') <> nvl(p_attributes(l_index).attribute6,'A')) then
1086 l_prohibited := true;
1087 elsif(nvl(l_old_attribute.attribute7,'A') <> nvl(p_attributes(l_index).attribute7,'A')) then
1088 l_prohibited := true;
1089 elsif(nvl(l_old_attribute.attribute8,'A') <> nvl(p_attributes(l_index).attribute8,'A')) then
1090 l_prohibited := true;
1091 elsif(nvl(l_old_attribute.attribute9,'A') <> nvl(p_attributes(l_index).attribute9,'A')) then
1092 l_prohibited := true;
1093 elsif(nvl(l_old_attribute.attribute10,'A') <> nvl(p_attributes(l_index).attribute10,'A')) then
1094 l_prohibited := true;
1095 elsif(nvl(l_old_attribute.attribute11,'A') <> nvl(p_attributes(l_index).attribute11,'A')) then
1096 l_prohibited := true;
1097 elsif(nvl(l_old_attribute.attribute12,'A') <> nvl(p_attributes(l_index).attribute12,'A')) then
1098 l_prohibited := true;
1102 l_prohibited := true;
1099 elsif(nvl(l_old_attribute.attribute13,'A') <> nvl(p_attributes(l_index).attribute13,'A')) then
1100 l_prohibited := true;
1101 elsif(nvl(l_old_attribute.attribute14,'A') <> nvl(p_attributes(l_index).attribute14,'A')) then
1103 elsif(nvl(l_old_attribute.attribute15,'A') <> nvl(p_attributes(l_index).attribute15,'A')) then
1104 l_prohibited := true;
1105 elsif(nvl(l_old_attribute.attribute16,'A') <> nvl(p_attributes(l_index).attribute16,'A')) then
1106 l_prohibited := true;
1107 elsif(nvl(l_old_attribute.attribute17,'A') <> nvl(p_attributes(l_index).attribute17,'A')) then
1108 l_prohibited := true;
1109 elsif(nvl(l_old_attribute.attribute18,'A') <> nvl(p_attributes(l_index).attribute18,'A')) then
1110 l_prohibited := true;
1111 elsif(nvl(l_old_attribute.attribute19,'A') <> nvl(p_attributes(l_index).attribute19,'A')) then
1112 l_prohibited := true;
1113 elsif(nvl(l_old_attribute.attribute20,'A') <> nvl(p_attributes(l_index).attribute20,'A')) then
1114 l_prohibited := true;
1115 elsif(nvl(l_old_attribute.attribute21,'A') <> nvl(p_attributes(l_index).attribute21,'A')) then
1116 l_prohibited := true;
1117 elsif(nvl(l_old_attribute.attribute22,'A') <> nvl(p_attributes(l_index).attribute22,'A')) then
1118 l_prohibited := true;
1119 elsif(nvl(l_old_attribute.attribute23,'A') <> nvl(p_attributes(l_index).attribute23,'A')) then
1120 l_prohibited := true;
1121 elsif(nvl(l_old_attribute.attribute24,'A') <> nvl(p_attributes(l_index).attribute24,'A')) then
1122 l_prohibited := true;
1123 elsif(nvl(l_old_attribute.attribute25,'A') <> nvl(p_attributes(l_index).attribute25,'A')) then
1124 l_prohibited := true;
1125 elsif(nvl(l_old_attribute.attribute26,'A') <> nvl(p_attributes(l_index).attribute26,'A')) then
1126 l_prohibited := true;
1127 elsif(nvl(l_old_attribute.attribute27,'A') <> nvl(p_attributes(l_index).attribute27,'A')) then
1128 l_prohibited := true;
1129 elsif(nvl(l_old_attribute.attribute28,'A') <> nvl(p_attributes(l_index).attribute28,'A')) then
1130 l_prohibited := true;
1131 elsif(nvl(l_old_attribute.attribute29,'A') <> nvl(p_attributes(l_index).attribute29,'A')) then
1132 l_prohibited := true;
1133 elsif(nvl(l_old_attribute.attribute30,'A') <> nvl(p_attributes(l_index).attribute30,'A')) then
1134 l_prohibited := true;
1135 end if;
1136 end if;
1137 end if;
1138 l_index := p_attributes.next(l_index);
1139 End Loop;
1140
1141 return l_prohibited;
1142
1143 End prohibitedAttributeChange;
1144
1145 Function prohibitedBlockChange
1146 (p_block in hxc_block_type,
1147 p_attributes in hxc_attribute_table_type)
1148 Return Boolean is
1149
1150 cursor c_old_block
1151 (p_id in hxc_time_building_blocks.time_building_block_id%type,
1152 p_ovn in hxc_time_building_blocks.object_version_number%type) is
1153 select *
1154 from hxc_time_building_blocks
1155 where time_building_block_id = p_id
1156 and object_version_number = p_ovn;
1157
1158 l_old_block c_old_block%rowtype;
1159 l_prohibited boolean;
1160
1161 Begin
1162 if(p_block.process = 'N') then
1163 l_prohibited := false;
1164 else
1165 open c_old_block
1166 (p_block.time_building_block_id,
1167 p_block.object_version_number);
1168 fetch c_old_block into l_old_block;
1169 close c_old_block;
1170
1171 if(l_old_block.time_building_block_id is null) then
1172 -- How did we get here, must be a new block!
1173 l_prohibited := false;
1174 else
1175 -- Check all user updateable attributes, except
1176 -- approval style id, which Santos do not want
1177 -- included in this check.
1178 if(l_old_block.TYPE <> p_block.TYPE) then
1179 l_prohibited := true;
1180 else
1181 if(round(l_old_block.MEASURE,3) <> round(p_block.MEASURE,3)) then
1182 l_prohibited := true;
1183 else
1184 if(l_old_block.UNIT_OF_MEASURE <> p_block.UNIT_OF_MEASURE) then
1185 l_prohibited := true;
1186 else
1187 if(l_old_block.START_TIME <> fnd_date.canonical_to_date(p_block.START_TIME)) then
1188 l_prohibited := true;
1189 else
1190 if(l_old_block.STOP_TIME <> fnd_date.canonical_to_date(p_block.STOP_TIME)) then
1191 l_prohibited := true;
1192 else
1193 if(l_old_block.APPROVAL_STATUS <> p_block.APPROVAL_STATUS) then
1194 l_prohibited := true;
1195 else
1196 if(l_old_block.DATE_TO <> fnd_date.canonical_to_date(p_block.DATE_TO)) then
1197 l_prohibited := true;
1198 else
1199 if(nvl(l_old_block.COMMENT_TEXT,'COMMENT') <> nvl(p_block.COMMENT_TEXT,'COMMENT')) then
1200 l_prohibited := true;
1201 --
1202 -- Ok, all the block attributes are the same
1203 -- Are the attributes the same?
1204 else
1205 if(prohibitedAttributeChange(p_block,p_attributes)) then
1206 l_prohibited := true;
1207 else
1208 l_prohibited := false;
1209 end if;
1210 end if;
1211 end if;
1212 end if;
1216 end if;
1213 end if;
1214 end if;
1215 end if;
1217 end if;
1218 end if;
1219 end if;
1220
1221 return l_prohibited;
1222
1223 End prohibitedBlockChange;
1224
1225 Function never_locked
1226 (p_timecard_props in hxc_timecard_prop_table_type
1227 ,p_early_date in date
1228 ,p_late_date in date
1229 ) return boolean is
1230
1231 l_index number;
1232 l_found boolean := false;
1233 l_day_found boolean := false;
1234 l_detail_found boolean := false;
1235 l_day boolean := false;
1236 l_detail boolean := false;
1237 l_result boolean := false;
1238 l_proc varchar2(30) := 'never_locked';
1239 Begin
1240
1241
1242 l_index := p_timecard_props.first;
1243 Loop
1244 Exit when ((not p_timecard_props.exists(l_index)) or (l_found));
1245
1246 if(p_timecard_props(l_index).property_name = 'TcWTcrdStAlwEditsModifyApprovedTcDetails') then
1247 if(p_timecard_props(l_index).date_from < p_early_date) then
1248 if(p_timecard_props(l_index).date_to > p_late_date) then
1249 l_detail_found := true;
1250 if((p_timecard_props(l_index).property_value <> 'N') OR (p_timecard_props(l_index).property_value is null))then
1251 l_detail := true;
1252 end if;
1253 end if;
1254 end if;
1255 end if;
1256
1257 if(p_timecard_props(l_index).property_name = 'TcWTcrdStAlwEditsModifyApprovedTcDays') then
1258 if(p_timecard_props(l_index).date_from < p_early_date) then
1259 if(p_timecard_props(l_index).date_to > p_late_date) then
1260 l_day_found := true;
1261 if((p_timecard_props(l_index).property_value <> 'N') OR (p_timecard_props(l_index).property_value is null))then
1262 l_day := true;
1263 end if;
1264 end if;
1265 end if;
1266 end if;
1267
1268 if(l_day_found) AND (l_detail_found) then
1269 l_found := true;
1270 if(l_day) and (l_detail) then
1271 l_result := true;
1272 end if;
1273 end if;
1274
1275 l_index := p_timecard_props.next(l_index);
1276 End Loop;
1277
1278 --
1279 -- 115.6 Change: Bug 2888528
1280 --
1281
1282 if(not l_found) then
1283
1284 if((l_day_found) AND (NOT l_day)) then
1285 l_result := false;
1286 elsif((l_detail_found) AND (NOT l_detail)) then
1287 l_result := false;
1288 else
1289 l_result := true;
1290 end if;
1291
1292 end if;
1293
1294 return l_result;
1295
1296 End never_locked;
1297
1298 Function find_locking_preference
1299 (p_timecard_props in hxc_timecard_prop_table_type
1300 ,p_date in date
1301 ,p_preference in varchar2
1302 ) return varchar2 is
1303
1304 l_preference varchar2(2) := 'Y';
1305 l_index number;
1306 l_found boolean := false;
1307 Begin
1308
1309 l_index := p_timecard_props.first;
1310 Loop
1311 Exit when ((not p_timecard_props.exists(l_index)) or (l_found));
1312 if(p_preference = 'DAY') then
1313 if(p_timecard_props(l_index).property_name = 'TcWTcrdStAlwEditsModifyApprovedTcDays') then
1314 if(p_date between p_timecard_props(l_index).date_from and p_timecard_props(l_index).date_to) then
1315 l_preference := p_timecard_props(l_index).property_value;
1316 l_found := true;
1317 end if;
1318 end if;
1319 else
1320 if(p_timecard_props(l_index).property_name = 'TcWTcrdStAlwEditsModifyApprovedTcDetails') then
1321 if(p_date between p_timecard_props(l_index).date_from and p_timecard_props(l_index).date_to) then
1322 l_preference := p_timecard_props(l_index).property_value;
1323 l_found := true;
1324 end if;
1325 end if;
1326 end if;
1327 l_index := p_timecard_props.next(l_index);
1328 End Loop;
1329
1330 return l_preference;
1331
1332 End find_locking_preference;
1333
1334 Function day_approved
1335 (p_resource_id in hxc_time_building_blocks.resource_id%type
1336 ,p_date in date
1337 ) return boolean is
1338
1339 cursor c_approved_day
1340 (p_resource_id in hxc_time_building_blocks.resource_id%type
1341 ,p_date in date) is
1342 select ap.approval_status
1343 from hxc_time_building_blocks ap, hxc_time_building_blocks tc
1344 where ap.resource_id = p_resource_id
1345 and ap.scope = 'APPLICATION_PERIOD'
1346 and ap.approval_status = 'APPROVED'
1347 and p_date between ap.start_time and ap.stop_time
1348 and ap.resource_id = tc.resource_id
1349 and ap.creation_date >= tc.creation_date
1350 and tc.date_to = hr_general.end_of_time
1351 and tc.scope = 'TIMECARD'
1352 and tc.start_time <= ap.stop_time
1353 and tc.stop_time >= ap.start_time
1354 and NOT exists
1355 (select 'Y'
1356 from hxc_time_building_blocks
1357 where resource_id = p_resource_id
1358 and scope = 'APPLICATION_PERIOD'
1359 and date_to = hr_general.end_of_time
1360 and approval_status = 'REJECTED'
1361 and p_date between start_time and stop_time
1362 );
1363
1364 l_dummy hxc_time_building_blocks.approval_status%type;
1365
1366 Begin
1367
1368 open c_approved_day(p_resource_id,p_date);
1369 fetch c_approved_day into l_dummy;
1370 if(c_approved_day%found) then
1371 close c_approved_day;
1372 return true;
1373 else
1374 close c_approved_day;
1375 return false;
1376 end if;
1377
1378 End day_approved;
1379 --
1380 -- Added detail_with_day in version 115.115. This is for the
1381 -- santos locking of approved days functionality.
1382 --
1386 ) RETURN BOOLEAN IS
1383 FUNCTION detail_with_day
1384 (p_date IN DATE,
1385 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1387
1388 CURSOR c_detail_exists
1389 (p_date IN DATE,
1390 p_resource_id IN hxc_time_building_blocks.resource_id%TYPE
1391 ) IS
1392 SELECT 'Y'
1393 FROM hxc_time_building_blocks detail,
1394 hxc_time_building_blocks day,
1395 hxc_time_building_blocks parent
1396 WHERE detail.parent_building_block_id = day.time_building_block_id
1397 AND detail.parent_building_block_ovn = day.object_version_number
1398 AND detail.date_to = hr_general.end_of_time
1399 AND detail.resource_id = p_resource_id
1400 AND detail.scope = 'DETAIL'
1401 AND detail.creation_date <=(select max(ap.creation_date)
1402 from hxc_time_building_blocks ap
1403 where ap.resource_id = p_resource_id
1404 and ap.scope = 'APPLICATION_PERIOD'
1405 and ap.approval_status = 'APPROVED'
1406 and p_date between ap.start_time and ap.stop_time
1407 and ap.resource_id = parent.resource_id
1408 and ap.creation_date >= parent.creation_date
1409 and parent.date_to = hr_general.end_of_time
1410 and parent.start_time <= ap.stop_time
1411 and parent.stop_time >= ap.start_time)
1412 AND day.scope = 'DAY'
1413 AND Trunc(day.start_time) = Trunc(p_date)
1414 AND day.date_to = hr_general.end_of_time
1415 AND day.resource_id = p_resource_id
1416 and parent.time_building_block_id=day.parent_building_block_id
1417 and parent.object_version_number=day.parent_building_block_ovn
1418 and parent.scope='TIMECARD'
1419 AND parent.date_to = hr_general.end_of_time
1420 AND parent.resource_id = p_resource_id;
1421
1422 l_dummy varchar2(1);
1423
1424 BEGIN
1425 OPEN c_detail_exists(p_date,p_resource_id);
1426 FETCH c_detail_exists INTO l_dummy;
1427 IF(c_detail_exists%found) THEN
1428 CLOSE c_detail_exists;
1429 RETURN TRUE;
1430 ELSE
1431 CLOSE c_detail_exists;
1432 RETURN FALSE;
1433 END IF;
1434 END detail_with_day;
1435
1436 Function day_locked
1437 (p_timecard_props in hxc_timecard_prop_table_type
1438 ,p_resource_id in hxc_time_building_blocks.resource_id%type
1439 ,p_date in date
1440 ) return boolean is
1441
1442 l_locked boolean := false;
1443 l_pref varchar2(2);
1444
1445 Begin
1446
1447 l_pref := find_locking_preference
1448 (p_timecard_props
1449 ,p_date
1450 ,'DAY'
1451 );
1452 if(l_pref = 'N') then
1453 if(day_approved(p_resource_id,p_date)) then
1454 IF(detail_with_day(p_date,p_resource_id)) then
1455 l_locked := true;
1456 ELSE
1457 l_locked := FALSE;
1458 END IF;
1459 else
1460 l_locked := false;
1461 end if;
1462 else
1463 l_locked := false;
1464 end if;
1465
1466 return l_locked;
1467
1468 End day_locked;
1469
1470 Function detail_locked
1471 (p_timecard_props in hxc_timecard_prop_table_type,
1472 p_resource_id in hxc_time_building_blocks.resource_id%type,
1473 p_date in date,
1474 p_detail_id in hxc_ap_detail_links.time_building_block_id%type,
1475 p_detail_ovn in hxc_ap_detail_links.time_building_block_ovn%type
1476 ) return boolean is
1477
1478 cursor c_detail_approved
1479 (p_detail_id in hxc_ap_detail_links.time_building_block_id%type,
1480 p_detail_ovn in hxc_ap_detail_links.time_building_block_ovn%type) is
1481 select 'Y'
1482 from hxc_ap_detail_links adl,
1483 hxc_app_period_summary aps
1484 where adl.time_building_block_id = p_detail_id
1485 and adl.time_building_block_ovn = p_detail_ovn
1486 and adl.application_period_id = aps.application_period_id
1487 and aps.approval_status = hxc_timecard.c_approved_status
1488 and not exists(select 1
1489 from hxc_ap_detail_links adl2,
1490 hxc_app_period_summary aps2
1491 where adl2.time_building_block_id = adl.time_building_block_id
1492 and adl2.time_building_block_ovn = adl.time_building_block_ovn
1493 and adl2.application_period_id <> adl.application_period_id
1494 and adl2.application_period_id = aps2.application_period_id
1495 and aps2.approval_status <> hxc_timecard.c_approved_status);
1496
1497 l_locked boolean := false;
1498 l_pref varchar2(2);
1499 l_dummy varchar2(1);
1500
1501 Begin
1502
1503 l_pref := find_locking_preference
1504 (p_timecard_props,
1505 p_date,
1506 'DETAIL'
1507 );
1508
1509 if(l_pref = 'N') then
1510 open c_detail_approved(p_detail_id,p_detail_ovn);
1511 fetch c_detail_approved into l_dummy;
1512 if(c_detail_approved%notfound) then
1513 l_locked := false;
1514 else
1515 l_locked := true;
1516 end if;
1517 close c_detail_approved;
1518 else
1519 l_locked := false;
1520 end if;
1521
1522 return l_locked;
1523
1524 End detail_locked;
1525 --
1526 -- ----------------------------------------------------------------------------
1527 -- |----------------------< chk_approved_locked_days >------------------------|
1528 -- ----------------------------------------------------------------------------
1529 -- {Start Of Comments}
1530 --
1531 -- Description:
1532 -- This procedure performs all the basic checks on the timecard information
1536 --
1533 -- pre-deposit, but once the processing state of the blocks in known. At the
1534 -- the moment, this is simply the implementation of the Santos-built approval
1535 -- locking of days.
1537 -- Prerequisites:
1538 -- Middle tier has sent some blocks, attributes. I.e. we've passed
1539 -- check inputs.
1540 --
1541 -- In Parameters:
1542 -- Name Reqd Type Description
1543 -- p_blocks Yes BLOCKS The blocks to deposit
1544 -- p_timecard_props Yes PROPS The preferences
1545 -- p_days Yes BlockList The Day building blocks
1546 -- p_details Yes Blocklist The detail building blocks
1547 -- p_messages Yes MESSAGES The application messages
1548 --
1549 -- Access Status:
1550 -- Internal use only.
1551 --
1552 -- {End Of Comments}
1553 --
1554 --
1555 PROCEDURE chk_approved_locked_days
1556 (p_blocks in hxc_block_table_type,
1557 p_attributes in hxc_attribute_table_type
1558 ,p_timecard_props in hxc_timecard_prop_table_type
1559 ,p_days in hxc_timecard.block_list
1560 ,p_details in hxc_timecard.block_list
1561 ,p_messages in out nocopy hxc_message_table_type
1562 ) IS
1563
1564 l_modify_days varchar2(2) := 'N';
1565 l_modify_details varchar2(2) := 'N';
1566 l_detail_date date;
1567 l_detail_index number;
1568 l_early_detail date := hr_general.end_of_time;
1569 l_late_detail date := hr_general.start_of_time;
1570 l_proc varchar2(72) := g_package||'chk_approved_locked_days';
1571 Begin
1572
1573 find_detail_date_extremes
1574 (p_blocks => p_blocks
1575 ,p_days => p_days
1576 ,p_details => p_details
1577 ,p_early_detail => l_early_detail
1578 ,p_late_detail => l_late_detail
1579 );
1580
1581 if(never_locked(p_timecard_props, l_early_detail, l_late_detail)) then
1582 --
1583 -- No need to check, since the preferences are not set.
1584 --
1585 null;
1586 else
1587 --
1588 -- Check each detail to see if allowed to change.
1589 --
1590 l_detail_index := p_details.first;
1591 Loop
1592 Exit when not p_details.exists(l_detail_index);
1593
1594 if(((prohibitedBlockChange
1595 (p_blocks(p_details(l_detail_index)),
1596 p_attributes
1597 )
1598 )
1599 OR
1600 (hxc_timecard_block_utils.is_new_block(p_blocks(p_details(l_detail_index)))))
1601 --AND
1602 --(hxc_timecard_block_utils.is_active_block(p_blocks(p_details(l_detail_index))))
1603 )then
1604
1605 l_detail_date := trunc(hxc_timecard_block_utils.date_value
1606 (p_blocks(p_days(p_blocks(p_details(l_detail_index)).parent_building_block_id)).start_time
1607 ));
1608 if(day_locked(p_timecard_props, p_blocks(p_details(l_detail_index)).resource_id,l_detail_date)) then
1609 hxc_timecard_message_helper.addErrorToCollection
1610 (p_messages
1611 ,'HXC_NO_MODIFY_APPROVED_DETAIL'
1612 ,hxc_timecard.c_error
1613 ,null
1614 ,null
1615 ,hxc_timecard.c_hxc
1616 ,p_blocks(p_details(l_detail_index)).time_building_block_id
1617 ,null
1618 ,null
1619 ,null
1620 );
1621 else
1622 if(hxc_timecard_block_utils.is_new_block(p_blocks(p_details(l_detail_index)))) then
1623 --
1624 -- It is ok, this entry is allowed, the day is not locked, and it is a new
1625 -- building block
1626 --
1627 null;
1628 else
1629 if(detail_locked
1630 (p_timecard_props,
1631 p_blocks(p_details(l_detail_index)).resource_id,
1632 l_detail_date,
1633 p_blocks(p_details(l_detail_index)).time_building_block_id,
1634 p_blocks(p_details(l_detail_index)).object_version_number
1635 )) then
1636 hxc_timecard_message_helper.addErrorToCollection
1637 (p_messages
1638 ,'HXC_NO_MODIFY_APPROVED_DETAIL'
1639 ,hxc_timecard.c_error
1640 ,null
1641 ,null
1642 ,hxc_timecard.c_hxc
1643 ,p_blocks(p_details(l_detail_index)).time_building_block_id
1644 ,null
1645 ,null
1646 ,null
1647 );
1648 end if;
1649 end if;
1650 end if;
1651 end if; -- is the block new or changed?
1652 l_detail_index := p_details.next(l_detail_index);
1653 End Loop;
1654 end if;
1655
1656 End chk_approved_locked_days;
1657 --
1658 -- ----------------------------------------------------------------------------
1659 -- |-----------------------< perform_process_checks >-------------------------|
1660 -- ----------------------------------------------------------------------------
1661 -- {Start Of Comments}
1662 --
1663 -- Description:
1664 -- This procedure checks that if the deposit mode is submit, then we're
1665 -- going to process at least one or more block or attribute. If not, then the
1666 -- approval workflow will not be started, and we should not show the
1667 -- confirmation page to the user.
1668 --
1669 -- Prerequisites:
1670 -- Middle tier has sent some blocks, attributes. I.e. we've passed
1671 -- check inputs.
1672 --
1673 -- In Parameters:
1674 -- Name Reqd Type Description
1675 -- p_blocks Yes BLOCKS The blocks to deposit
1676 -- p_atttributes Yes ATTRS The attributes to deposit
1680 -- Internal use only.
1677 -- p_messages Yes MESSAGES The application messages
1678 --
1679 -- Access Status:
1681 --
1682 -- {End Of Comments}
1683 --
1684 --
1685 PROCEDURE chk_some_processing
1686 (p_blocks in hxc_block_table_type
1687 ,p_attributes in hxc_attribute_table_type
1688 ,p_messages in out nocopy hxc_message_table_type
1689 ) is
1690
1691 l_index number;
1692 l_found boolean := false;
1693
1694 l_proc varchar2(70) := g_package||'chk_some_processing';
1695
1696 Begin
1697
1698 IF g_debug THEN
1699 hr_utility.trace(' In HXC_DEPOSIT_CHECKS.chk_some_processing');
1700 END IF;
1701
1702
1703 l_index := p_blocks.first;
1704 LOOP
1705 Exit When ((Not p_blocks.exists(l_index)) or (l_found));
1706 if(hxc_timecard_block_utils.process_block(p_blocks(l_index))) then
1707 l_found := true;
1708 end if;
1709 l_index := p_blocks.next(l_index);
1710 END LOOP;
1711
1712 if (not l_found) then
1713 l_index := p_attributes.first;
1714 LOOP
1715 Exit When ((Not p_attributes.exists(l_index)) or (l_found));
1716 if(hxc_timecard_attribute_utils.process_attribute(p_attributes(l_index))) then
1717 l_found := true;
1718 end if;
1719 l_index := p_attributes.next(l_index);
1720 END LOOP;
1721 end if;
1722
1723 IF g_debug THEN
1724 if l_found then
1725 hr_utility.trace(' l_found is true');
1726 hr_utility.trace(' g_tc_api_flag ::'||hxc_block_attribute_update.g_tc_api_flag);
1727 else
1728 hr_utility.trace(' l_found is false');
1729 hr_utility.trace(' g_tc_api_flag ::'||hxc_block_attribute_update.g_tc_api_flag);
1730 end if;
1731 END IF;
1732
1733
1734 if (not l_found) then
1735 hxc_timecard_message_helper.addErrorToCollection
1736 (p_messages
1737 ,'HXC_TIMECARD_NOT_SUBMITTED'
1738 ,hxc_timecard.c_error
1739 ,null
1740 ,null
1741 ,hxc_timecard.c_hxc
1742 ,null
1743 ,null
1744 ,null
1745 ,null
1746 );
1747 end if;
1748
1749 -- Added for
1750 if hxc_block_attribute_update.g_tc_api_flag = 'Y' then
1751 hxc_timecard_message_helper.addErrorToCollection
1752 (p_messages
1753 ,'HXC_TIMECARD_NOT_SUBMITTED'
1754 ,hxc_timecard.c_error
1755 ,null
1756 ,null
1757 ,hxc_timecard.c_hxc
1758 ,null
1759 ,null
1760 ,null
1761 ,null
1762 );
1763 end if;
1764
1765 End chk_some_processing;
1766 --
1767 -- ----------------------------------------------------------------------------
1768 -- |-----------------------< perform_process_checks >-------------------------|
1769 -- ----------------------------------------------------------------------------
1770 -- {Start Of Comments}
1771 --
1772 -- Description:
1773 -- This procedure performs all the basic checks on the timecard information
1774 -- pre-deposit, but once the processing state of the blocks in known. At the
1775 -- the moment, this is simply the implementation of the Santos-built approval
1776 -- locking of days.
1777 --
1778 -- Prerequisites:
1779 -- Middle tier has sent some blocks, attributes. I.e. we've passed
1780 -- check inputs.
1781 --
1782 -- In Parameters:
1783 -- Name Reqd Type Description
1784 -- p_blocks Yes BLOCKS The blocks to deposit
1785 -- p_atttributes Yes ATTRS The attributes to deposit
1786 -- p_timecard_props Yes PROPS The preferences
1787 -- p_days Yes BlockList The Day building blocks
1788 -- p_details Yes Blocklist The detail building blocks
1789 -- p_template Yes VARCHAR2 Is this a template?
1790 -- p_deposit_mode Yes VARCHAR2 The current deposit mode
1791 -- p_messages Yes MESSAGES The application messages
1792 --
1793 -- Access Status:
1794 -- Internal use only.
1795 --
1796 -- {End Of Comments}
1797 --
1798 --
1799 PROCEDURE perform_process_checks
1800 (p_blocks in hxc_block_table_type
1801 ,p_attributes in hxc_attribute_table_type
1802 ,p_timecard_props in hxc_timecard_prop_table_type
1803 ,p_days in hxc_timecard.block_list
1804 ,p_details in hxc_timecard.block_list
1805 ,p_template in varchar2
1806 ,p_deposit_mode in varchar2
1807 ,p_messages in out nocopy hxc_message_table_type
1808 ) IS
1809
1810 l_modify_days varchar2(2) := 'N';
1811 l_modify_details varchar2(2) := 'N';
1812 l_detail_date date;
1813 l_detail_index number;
1814
1815 Begin
1816
1817 if(p_template <> 'Y') then
1818
1819 chk_approved_locked_days
1820 (p_blocks => p_blocks
1821 ,p_attributes => p_attributes
1822 ,p_timecard_props => p_timecard_props
1823 ,p_days => p_days
1824 ,p_details => p_details
1825 ,p_messages => p_messages
1826 );
1827
1828 if(p_deposit_mode = 'SUBMIT') then
1829
1830 hr_utility.trace('In perform_process_checks .. calling chk_some_process proc..');
1831 hr_utility.trace('p_deposit_mode ::'||p_deposit_mode);
1832 chk_some_processing
1833 (p_blocks => p_blocks
1834 ,p_attributes => p_attributes
1835 ,p_messages => p_messages
1836 );
1837 end if;
1838 end if;
1839
1840 End perform_process_checks;
1841
1842 END hxc_deposit_checks;
[Home] [Help]