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