[Home] [Help]
PACKAGE BODY: APPS.HXC_SELF_SERVICE_TIME_DEPOSIT
Source
1 PACKAGE BODY hxc_self_service_time_deposit AS
2 /* $Header: hxctcdpwr.pkb 120.6 2006/03/23 23:20:44 sgadipal noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 --AI7
6 TYPE t_mapping_comp IS RECORD
7 (segment hxc_mapping_components.segment%TYPE,
8 field_name hxc_mapping_components.field_name%TYPE,
9 bld_blk_info_type_id hxc_mapping_components.bld_blk_info_type_id%TYPE,
10 bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
11 building_block_category hxc_bld_blk_info_type_usages.building_block_category%TYPE);
12
13 TYPE t_mapping IS TABLE OF
14 t_mapping_comp
15 INDEX BY BINARY_INTEGER;
16 --AI7
17
18 -- Bug 2384849
19
20 TYPE t_bld_blk_info_types IS TABLE OF
21 HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE timecard_block_order IS TABLE OF
25 NUMBER
26 INDEX BY BINARY_INTEGER;
27
28 TYPE retrieval_ref_cursor IS REF CURSOR;
29
30 g_attributes building_block_attribute_info;
31 g_messages message_table;
32 g_timecard timecard_info;
33 g_app_attributes app_attributes_info;
34 g_timecard_block_order timecard_block_order;
35 g_update_phase BOOLEAN := FALSE;
36 g_max_messages_displayed NUMBER:= 30;
37 g_bg_id number; --AI8
38 g_org_id number; --AI8
39 g_allow_error_tc boolean;
40
41 g_deposit_mapping t_mapping; --AI7
42 g_deposit_process_id number; --AI7
43 g_retrieval_mapping t_mapping; --AI7
44 g_retrieval_process_id number; --AI7
45
46 g_debug_count NUMBER :=0;
47 g_workflow workflow_info;
48 g_time_attribute_id NUMBER :=0;
49 g_security_type_id NUMBER := 0;
50
51 g_package VARCHAR2(72) := 'HXC_SELF_SERVICE_TIME_DEPOSIT';
52 g_new_bbs VARCHAR2(2) := 'N';
53
54 -- Bug 2384349
55 g_bld_blk_info_types t_bld_blk_info_types;
56 g_queried_blk_info_types VARCHAR2(32000);
57 -- End Bug 2384349
58
59 e_approval_check EXCEPTION;
60 e_timecard_overlap EXCEPTION;
61 e_template_duplicate_name EXCEPTION;
62
63
64 g_resource_assignment_id NUMBER;
65
66 --AI7 routine to get information about a particular mapping component, given the field name.
67
68 PROCEDURE mapping_comp_info(p_field_name IN VARCHAR2,
69 p_mapping_component OUT NOCOPY t_mapping_comp,
70 p_mapping IN t_mapping)
71 IS
72
73 l_index number;
74
75 BEGIN
76
77
78 l_index := p_mapping.first;
79 LOOP
80 EXIT WHEN NOT p_mapping.exists(l_index);
81 IF(p_mapping(l_index).field_name = p_field_name) THEN
82 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
83 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
84 'hxc_self_service_time_deposit.mapping_comp_info',
85 'Found Mapping:'||p_mapping(l_index).field_name);
86 end if;
87 p_mapping_component.field_name := p_mapping(l_index).field_name;
88 p_mapping_component.segment := p_mapping(l_index).segment;
89 p_mapping_component.bld_blk_info_type_id := p_mapping(l_index).bld_blk_info_type_id;
90 p_mapping_component.bld_blk_info_type := p_mapping(l_index).bld_blk_info_type;
91 p_mapping_component.building_block_category := p_mapping(l_index).building_block_category;
92 return;
93 END IF;
94
95 l_index := p_mapping.next(l_index);
96 END LOOP;
97
98 if(FND_LOG.LEVEL_ERROR>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
99 fnd_log.string(FND_LOG.LEVEL_ERROR,
100 'hxc_self_service_time_deposit.mapping_comp_info',
101 'Failed to find mapping'||p_field_name);
102 end if;
103
104 END mapping_comp_info;
105 -- Bug 2384849
106
107 FUNCTION cache_bld_blk_info_types
108 (p_info_type in HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE) RETURN NUMBER is
109
110 l_bld_blk_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
111 l_count NUMBER;
112 l_found BOOLEAN := FALSE;
113
114 BEGIN
115
116 if(instr(g_queried_blk_info_types,p_info_type)>0) then
117
118 -- We know we've got the id, so look in the cached table
119
120 l_count := g_bld_blk_info_types.first;
121
122 LOOP
123
124 EXIT WHEN ((NOT g_bld_blk_info_types.exists(l_count)) OR (l_found));
125
126 if(g_bld_blk_info_types(l_count) = p_info_type) then
127 l_bld_blk_id := l_count;
128 end if;
129 l_count := g_bld_blk_info_types.next(l_count);
130
131 END LOOP;
132
133 else
134
135 -- Go to the db!
136
137 select bld_blk_info_type_id into l_bld_blk_id
138 from hxc_bld_blk_info_types
139 where bld_blk_info_type = p_info_type;
140
141 -- add this to the queried string!
142 -- (not yet, we'll count the number of times the SQL is executed)
143
144 g_queried_blk_info_types :=
145 g_queried_blk_info_types ||'#'|| p_info_type;
146 g_bld_blk_info_types(l_bld_blk_id) := p_info_type;
147
148 end if;
149
150 return l_bld_blk_id;
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND then
154 FND_MESSAGE.SET_NAME('HXC','HXC_NO_BLD_BLK_INFO_TYPE');
155 FND_MESSAGE.SET_TOKEN('TYPE',p_info_type);
156 FND_MESSAGE.raise_error;
157
158 END cache_bld_blk_info_types;
159
160 -- AI7 Routine make sure that we have the mapping components for the specified mapping
161 -- in memory. If not, it gets them.
162 -- Should be used before an attempt to access mapping structures is made.
163
164 PROCEDURE cache_mappings(p_deposit_process_id in number,
165 p_retrieval_process_id in number)
166 IS
167
168 l_index NUMBER;
169
170 CURSOR csr_retrieval_mapping(p_retrieval_process_id NUMBER)
171 IS
172 select mc.segment,
173 mc.field_name,
174 bbui.building_block_category,
175 bbit.bld_blk_info_type_id,
176 bbit.bld_blk_info_type
177 from hxc_mapping_components mc
178 ,hxc_mapping_comp_usages mcu
179 ,hxc_mappings m
180 ,hxc_retrieval_processes rp
181 ,hxc_bld_blk_info_types bbit
182 ,hxc_bld_blk_info_type_usages bbui
183 where rp.mapping_id = m.mapping_id
184 and rp.retrieval_process_id = p_retrieval_process_id
185 and m.mapping_id = mcu.mapping_id
186 and mcu.mapping_component_id = mc.mapping_component_id
187 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
188 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id;
189
190 CURSOR csr_deposit_mapping(p_deposit_process_id NUMBER)
191 IS
192 select mc.segment,
193 mc.field_name,
194 bbui.building_block_category,
195 bbit.bld_blk_info_type_id,
196 bbit.bld_blk_info_type
197 from hxc_mapping_components mc
198 ,hxc_mapping_comp_usages mcu
199 ,hxc_mappings m
200 ,hxc_deposit_processes dp
201 ,hxc_bld_blk_info_types bbit
202 ,hxc_bld_blk_info_type_usages bbui
203 where dp.mapping_id = m.mapping_id
204 and dp.deposit_process_id = p_deposit_process_id
205 and m.mapping_id = mcu.mapping_id
206 and mcu.mapping_component_id = mc.mapping_component_id
207 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
208 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id;
209
210 BEGIN
211
212 l_index:=0;
213
214 if(p_deposit_process_id is not null) then
215 -- cache if different than mapping already held
216 if(nvl(g_deposit_process_id,-1) <> p_deposit_process_id) then
217 -- load deposit process
218 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
219 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
220 'HXC_SELF_SERVICE_TIME_DEPOSIT',
221 'Loading Deposit Process');
222 end if;
223 g_deposit_process_id := p_deposit_process_id;
224
225 for dep_mapping_comp in csr_deposit_mapping(p_deposit_process_id) loop
226 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
227 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
228 'HXC_SELF_SERVICE_TIME_DEPOSIT.CACHE_MAPPINGS',
229 'Field Name'||dep_mapping_comp.field_name);
230 end if;
231
232 g_deposit_mapping(l_index).segment := dep_mapping_comp.segment;
233 g_deposit_mapping(l_index).field_name := dep_mapping_comp.field_name;
234 g_deposit_mapping(l_index).building_block_category := dep_mapping_comp.segment;
235 g_deposit_mapping(l_index).bld_blk_info_type_id := dep_mapping_comp.bld_blk_info_type_id;
236 g_deposit_mapping(l_index).bld_blk_info_type := dep_mapping_comp.bld_blk_info_type;
237 l_index:=l_index+1;
238 end loop;
239 end if;
240 end if;
241
242 if(p_retrieval_process_id is not null) then
243 -- cache if different than mapping already held
244 if(nvl(g_retrieval_process_id,-1) <> p_retrieval_process_id) then
245 -- load retrieval process
246 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
247 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
248 'HXC_SELF_SERVICE_TIME_DEPOSIT.CACHE_MAPPINGS',
249 'Loading Retrieval Process');
250 end if;
251 g_retrieval_process_id := p_retrieval_process_id;
252
253 for ret_mapping_comp in csr_retrieval_mapping(p_retrieval_process_id) loop
254 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
255 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
256 'HXC_SELF_SERVICE_TIME_DEPOSIT.CACHE_MAPPINGS',
257 'Field Name'||ret_mapping_comp.field_name);
258 end if;
259
260 g_retrieval_mapping(l_index).segment := ret_mapping_comp.segment;
261 g_retrieval_mapping(l_index).field_name := ret_mapping_comp.field_name;
262 g_retrieval_mapping(l_index).building_block_category := ret_mapping_comp.segment;
263 g_retrieval_mapping(l_index).bld_blk_info_type_id := ret_mapping_comp.bld_blk_info_type_id;
264 g_retrieval_mapping(l_index).bld_blk_info_type := ret_mapping_comp.bld_blk_info_type;
265 l_index:=l_index+1;
266 end loop;
267 end if;
268 end if;
269
270 END cache_mappings;
271
272 /*
273 --
274 -- Debug procedure
275 --
276 -- Used in conjuction with a temporary table to locate
277 -- debugging information when there is a problem
278 -- with submission of a timecard
279 --
280
281 PROCEDURE debug
282 (p_procedure IN VARCHAR2
283 ,p_reference IN NUMBER
284 ,p_text IN VARCHAR2
285 ,p_resource_id in NUMBER
286 ) IS
287
288 l_loc VARCHAR2(70) := 'HXC_SELF_SERVICE_TIME_DEPOSIT.'|| p_procedure;
289
290 BEGIN
291
292 g_debug_count := g_debug_count + 1;
293
294 if (p_resource_id = 11999) then
295
296 INSERT INTO hxc_timecard_debug
297 (LINE
298 ,LOCATION
299 ,REFERENCE
300 ,TEXT
301 )
302 VALUES
303 (g_debug_count
304 ,l_loc
305 ,p_reference
306 ,p_text
307 );
308
309 COMMIT;
310
311 end if;
312
313 END debug;
314 */
315 /*
316 PROCEDURE debug
317 (p_procedure IN VARCHAR2
318 ,p_reference IN NUMBER
319 ,p_text IN VARCHAR2
320 ) IS
321
322 l_loc VARCHAR2(70) := 'HXC_SELF_SERVICE_TIME_DEPOSIT.'|| p_procedure;
323
324 BEGIN
325
326 g_debug_count := g_debug_count + 1;
327
328 if (g_timecard(1).resource_id = 11785) then
329
330 INSERT INTO hxc_timecard_debug
331 (LINE
332 ,LOCATION
333 ,REFERENCE
334 ,TEXT
335 )
336 VALUES
337 (g_debug_count
338 ,l_loc
339 ,p_reference
340 ,p_text
341 );
342
343 COMMIT;
344
345 end if;
346
347 END debug;
348 */
349 FUNCTION update_required(p_old IN VARCHAR2
350 ,p_new IN VARCHAR2) RETURN BOOLEAN IS
351
352 BEGIN
353
354 IF ((p_old <> p_new)
355 OR((p_old IS null) AND (p_new IS NOT NULL))
356 OR((p_old IS NOT NULL) AND (p_new IS null))) THEN
357 RETURN TRUE;
358 ELSE
359 RETURN FALSE;
360 END IF;
361
362 END update_required;
363
364 function find_parent_start_date
365 (p_block_number in number) return DATE is
366
367 l_block_count NUMBER;
368 l_found BOOLEAN := FALSE;
369 l_date DATE;
370
371 BEGIN
372
373 l_block_count := g_timecard.first;
374
375 LOOP
376 EXIT WHEN ((NOT g_timecard.exists(l_block_count)) OR (l_found));
377
378 if(g_timecard(l_block_count).time_building_block_id
379 = g_timecard(p_block_number).parent_building_block_id) then
380
381 l_found := true;
382 l_date := g_timecard(l_block_count).start_time;
383
384 end if;
385
386 l_block_count := g_timecard.next(l_block_count);
387
388 END LOOP;
389
390 return l_date;
391
392
393 END find_parent_start_date;
394 --
395 -- ---------------------------------------------------------
396 -- ---------------------------------------------------------
397 -- find_approval_status
398 --
399 -- Called internally only.
400 --
401 -- The function determines the approval status of the latest
402 -- block record associated with this parent.
403 --
404 -- ---------------------------------------------------------
405 -- ---------------------------------------------------------
406
407 FUNCTION find_approval_status
408 (p_resource_id in NUMBER
409 ,p_day_date in DATE
410 ) return VARCHAR2 is
411
412 cursor c_approval_status
413 (p_r_id in NUMBER
414 ,p_date in DATE) is
415 select approval_status
416 from hxc_time_building_blocks
417 where resource_id = p_r_id
418 and scope = 'APPLICATION_PERIOD'
419 and approval_status = 'APPROVED'
420 and p_date between start_time and stop_time
421 and NOT exists
422 (select 'Y'
423 from hxc_time_building_blocks
424 where resource_id = p_r_id
425 and scope = 'APPLICATION_PERIOD'
426 and date_to = hr_general.end_of_time
427 and approval_status = 'REJECTED'
428 and p_date between start_time and stop_time
429 );
430
431 l_return HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE;
432
433 BEGIN
434
435 open c_approval_status(p_resource_id, p_day_date);
436 fetch c_approval_status into l_return;
437
438 if c_approval_status%NOTFOUND then
439 l_return := 'NOT_APPROVED';
440 end if;
441
442 close c_approval_status;
443
444 return l_return;
445
446 END find_approval_status;
447 --
448 -- ----------------------------------------------------------
449 -- ----------------------------------------------------------
450 -- check_details_exist_on_day
451 --
452 -- Called internally only.
453 --
454 -- This function checks to see if there are any details
455 -- bound to an existing day that has been approved. If so,
456 -- and if the preference so forbids it, the user can not
457 -- enter additional details for a day.
458 --
459 -- ----------------------------------------------------------
460 -- ----------------------------------------------------------
461
462 FUNCTION check_details_exist_on_day
463 (p_block_number in NUMBER) return BOOLEAN is
464
465 l_block NUMBER;
466 l_return BOOLEAN := false;
467
468 BEGIN
469
470 l_block := g_timecard.first;
471
472 LOOP
473 EXIT WHEN ((NOT g_timecard.exists(l_block)) OR (l_return));
474 --
475 -- Check that the parent of the detail block we're checking
476 -- is the same as an existing block - i.e. we already have a
477 -- detail attached to this day
478 --
479
480 if (
481 (g_timecard(p_block_number).parent_building_block_id
482 = g_timecard(l_block).parent_building_block_id)
483 AND
484 (l_block <> p_block_number)
485 AND
486 (g_timecard(l_block).new = 'N')
487 ) then
488
489 l_return := true;
490
491 end if;
492
493 l_block := g_timecard.next(l_block);
494
495 END LOOP;
496
497 return l_return;
498
499 END check_details_exist_on_day;
500
501 FUNCTION is_working_status
502 (p_block_number in NUMBER) RETURN BOOLEAN IS
503
504 cursor csr_working
505 (p_time_building_block_id in HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE) is
506 select approval_status
507 from hxc_time_building_blocks
508 where time_building_block_id = p_time_building_block_id
509 and date_to = hr_general.end_of_time
510 and approval_status = 'WORKING';
511
512 l_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE;
513
514 BEGIN
515
516 open csr_working(g_timecard(p_block_number).time_building_block_id);
517 fetch csr_working into l_status;
518
519 if (csr_working%FOUND) then
520 close csr_working;
521 return true;
522 else
523 close csr_working;
524 return false;
525 end if;
526
527 END is_working_status;
528 --
529 -- ----------------------------------------------------------
530 -- ----------------------------------------------------------
531 -- Check active day
532 --
533 -- Called internally only
534 --
535 -- This function makes sure that the detail entry is
536 -- entered on a day for which the user has an active
537 -- assignment.
538 --
539 -- ----------------------------------------------------------
540 -- ----------------------------------------------------------
541
542 FUNCTION check_active_date
543 (p_date in DATE
544 ,p_person_id in NUMBER) return BOOLEAN is
545
546 cursor c_date
547 (p_check_date in DATE
548 ,p_resource_id in NUMBER) is
549 SELECT 'Y'
550 FROM PER_ALL_ASSIGNMENTS_F paa,
551 per_assignment_status_types typ
552 WHERE paa.PERSON_ID = p_resource_id
553 AND paa.ASSIGNMENT_TYPE = 'E'
554 AND paa.PRIMARY_FLAG = 'Y'
555 AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
556 AND typ.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
557 AND p_check_date between paa.effective_start_date and paa.effective_end_date;
558
559 l_dummy VARCHAR(5);
560
561 BEGIN
562
563 open c_date(p_date,p_person_id);
564 fetch c_date into l_dummy;
565
566 if c_date%FOUND then
567
568 close c_date;
569 return true;
570
571 else
572
573 close c_date;
574 return false;
575
576 end if;
577
578 END check_active_date;
579
580 --
581 -- ----------------------------------------------------------
582 -- ----------------------------------------------------------
583 -- check_approval_locked
584 --
585 -- Called internally only
586 --
587 -- This functions looks at the preference that determines
588 -- whether a user is permitted to update a building block
589 -- that has been previously approved.
590 --
591 -- ----------------------------------------------------------
592 -- ----------------------------------------------------------
593
594 FUNCTION check_approval_locked
595 (p_block in number) return BOOLEAN is
596
597 l_return BOOLEAN := false;
598 l_parent_start_date DATE;
599 l_resource_start_date DATE;
600 l_resource_end_date DATE;
601 l_pref_val HXC_PREF_HIERARCHIES.ATTRIBUTE16%TYPE;
602 l_pref_detail_val HXC_PREF_HIERARCHIES.ATTRIBUTE17%TYPE;
603 l_previous_status HXC_TIME_BUILDING_BLOCKS.APPROVAL_STATUS%TYPE;
604 l_proc VARCHAR2(32) := 'CHECK_APPROVAL_LOCKED';
605
606 BEGIN
607
608 if(NOT is_working_status(p_block)) then
609
610 l_parent_start_date := find_parent_start_date(p_block);
611 --
612 -- Check that the resource is allowed to enter time on this
613 -- day. This avoids problems with mid-period terms or hires
614 --
615
616 if(check_active_date(l_parent_start_date,g_timecard(p_block).resource_id)) then
617 --
618 -- Check the day preference value
619 --
620 l_pref_val := hxc_preference_evaluation.resource_preferences
621 (g_timecard(p_block).resource_id
622 ,'TC_W_TCRD_ST_ALW_EDITS'
623 ,16
624 ,l_parent_start_date
625 );
626
627 if( l_pref_val = 'N') then
628 --
629 -- Find the approval status of the parent
630 --
631
632 l_previous_status := find_approval_status
633 (g_timecard(p_block).resource_id
634 ,l_parent_start_date);
635
636 if (l_previous_status = 'APPROVED') then
637
638 if(g_timecard(p_block).new <> 'Y') then
639
640 -- Not allowed to change this block
641 l_return := true;
642
643 else
644
645 if(check_details_exist_on_day(p_block)) then
646
647 l_return := true;
648
649 else
650
651 l_return := false;
652
653 end if;
654
655 end if;
656
657 end if;
658 end if;
659
660 if((NOT l_return) AND (g_timecard(p_block).new <> 'Y'))then
661 --
662 -- Check the detail preference value
663 --
664 l_pref_detail_val := hxc_preference_evaluation.resource_preferences
665 (g_timecard(p_block).resource_id
666 ,'TC_W_TCRD_ST_ALW_EDITS'
667 ,17
668 ,l_parent_start_date
669 );
670
671 if((l_pref_detail_val = 'N') AND (l_pref_val <> 'N')) then
672 --
673 -- Find the approval status of the parent
674 --
675
676 l_previous_status := find_approval_status
677 (g_timecard(p_block).resource_id
678 ,l_parent_start_date);
679
680 if(l_previous_status = 'APPROVED') then
681
682 -- Not allowed to change this block!
683
684 l_return := true;
685
686 end if;
687 end if;
688
689 end if; -- Do we need to check the detail when not new
690 --
691 --
692 else
693
694 hxc_time_entry_rules_utils_pkg.add_error_to_table (
695 p_message_table => g_messages
696 , p_message_name => 'HXC_DETAIL_NON_ACTIVE'
697 , p_message_token => NULL
698 , p_message_level => 'ERROR'
699 , p_message_field => NULL
700 , p_timecard_bb_id => g_timecard(p_block).time_building_block_id
701 , p_timecard_bb_ovn => g_timecard(p_block).object_version_number --added 2822462
702 , p_time_attribute_id => NULL);
703
704 end if;
705
706 end if;
707
708 return l_return;
709
710 End check_approval_locked;
711
712 --
713 -- ----------------------------------------------------------
714 -- ----------------------------------------------------------
715 -- Process Block
716 -- Called privately only
717 --
718 -- This function works out whether a block should be
719 -- passed to the deposit process.
720 -- ----------------------------------------------------------
721 -- ----------------------------------------------------------
722
723 function process_block
724 (p_block_number in number
725 ,p_mode in VARCHAR2
726 ,p_tk BOOLEAN) return BOOLEAN is
727
728 cursor csr_old_block
729 (p_time_building_block_id in number
730 ,p_ovn in number) is
731 select *
732 from hxc_time_building_blocks
733 where time_building_block_id = p_time_building_block_id
734 and object_version_number = p_ovn;
735
736 block_rec HXC_TIME_BUILDING_BLOCKS%ROWTYPE;
737
738 l_process BOOLEAN := false;
739 l_not_just_approval_status BOOLEAN := false;
740 l_pref_val HXC_PREF_HIERARCHIES.ATTRIBUTE16%TYPE;
741 l_proc varchar2(32) := 'process_block';
742 l_parent_date DATE;
743
744
745 BEGIN
746
747 --
748 -- Check end date first!
749 --
750 if (
751 (g_timecard(p_block_number).date_to = hr_general.end_of_time)
752 AND
753 (p_mode = 'UPDATE')
754 ) then
755
756 --
757 -- Check if it is a new block
758 --
759
760 if (g_timecard(p_block_number).new = 'Y') then
761
762 l_not_just_approval_status := true;
763
764 if (g_timecard(p_block_number).scope = 'DETAIL') then
765
766 if (g_timecard(p_block_number).type = 'RANGE') then
767
768 if(
769 (
770 (g_timecard(p_block_number).start_time is NOT NULL)
771 OR
772 (g_timecard(p_block_number).start_time <> '')
773 )
774 AND
775 (
776 (g_timecard(p_block_number).stop_time is NOT NULL)
777 OR
778 (g_timecard(p_block_number).stop_time <> '')
779 )
780 ) then
781
782 l_process := true;
783 else
784 l_process := false;
785 end if;
786 else
787 -- It's a measure, so measure had better not be null!
788 if(
789 (g_timecard(p_block_number).measure is NOT NULL)
790 OR
791 (g_timecard(p_block_number).measure <> -1)
792 ) then
793 l_process := true;
794 else
795 l_process := false;
796 end if;
797
798 end if;
799
800 else
801
802 l_process := true;
803
804 end if;
805
806 else
807
808 --
809 -- Check existing block criteria
810 --
811 open csr_old_block(g_timecard(p_block_number).time_building_block_id
812 ,g_timecard(p_block_number).object_version_number);
813 fetch csr_old_block into block_rec;
814 if csr_old_block%NOTFOUND then
815 --
816 -- Must be a new block, how'd we get here?
817 --
818 l_process := true;
819
820 else
821 --
822 -- Do the checks
823 --
824
825 if (update_required(g_timecard(p_block_number).measure,block_rec.measure))then
826 l_process := true;
827 l_not_just_approval_status := true;
828 end if;
829
830 if (g_timecard(p_block_number).start_time <> block_rec.start_time) then
831 l_process := true;
832 l_not_just_approval_status := true;
833 end if;
834
835 if (g_timecard(p_block_number).stop_time <> block_rec.stop_time) then
836 l_process := true;
837 l_not_just_approval_status := true;
838 end if;
839
840 if (update_required(g_timecard(p_block_number).comment_text,block_rec.comment_text)) then
841 l_process := true;
842 l_not_just_approval_status := true;
843 end if;
844
845 if (update_required(g_timecard(p_block_number).unit_of_measure,block_rec.unit_of_measure)) then
846 l_process := true;
847 l_not_just_approval_status := true;
848 end if;
849
850 if (update_required(g_timecard(p_block_number).approval_status,block_rec.approval_status)) then
851 l_process := true;
852 end if;
853
854 if(update_required(g_timecard(p_block_number).parent_building_block_ovn, block_rec.parent_building_block_ovn)) then
855 l_process := true;
856 end if;
857
858 --
859 -- Check to see if we're deleting!
860 --
861
862 if ((g_timecard(p_block_number).measure is NULL)
863 AND
864 (g_timecard(p_block_number).start_time is NULL)
865 AND
866 (g_timecard(p_block_number).stop_time is NULL)
867 ) then
868
869 -- In this case we don't want to process the block through the deposit process
870 -- But rather through the delete_block API.
871 l_process := false;
872 l_not_just_approval_status := true;
873
874 end if;
875
876 end if;
877 close csr_old_block;
878
879 end if;
880
881 elsif ((g_timecard(p_block_number).date_to= hr_general.end_of_time)
882 AND
883 (p_mode = 'DELETE')) then
884 --
885 -- Here we are asking if a previously existing block
886 -- should be deleted because it's parameters are
887 -- nulled out
888 --
889 if ((g_timecard(p_block_number).measure is NULL)
890 AND
891 (g_timecard(p_block_number).start_time is NULL)
892 AND
893 (g_timecard(p_block_number).stop_time is NULL)
894 AND
895 (g_timecard(p_block_number).new <> 'Y')
896 ) then
897
898 -- Go ahead and delete the block
899
900 l_process := true;
901 l_not_just_approval_status := true;
902
903 end if;
904
905 else
906
907 if(p_mode='DELETE') then
908
909 if (g_timecard(p_block_number).date_to <> hr_general.end_of_time) then
910
911 l_process := true;
912 l_not_just_approval_status := true;
913
914 end if;
915
916 end if;
917
918 end if;
919
920 if (NOT p_tk) then
921 if (l_process) then
922 if(l_not_just_approval_status) then
923 if(g_timecard(p_block_number).scope = 'DETAIL') then
924
925 if(check_approval_locked(p_block_number)) then
926
927 hxc_time_entry_rules_utils_pkg.add_error_to_table (
928 p_message_table => g_messages
929 , p_message_name => 'HXC_NO_MODIFY_APPROVED_DETAIL'
930 , p_message_token => NULL
931 , p_message_level => 'ERROR'
932 , p_message_field => NULL
933 , p_timecard_bb_id => g_timecard(p_block_number).time_building_block_id
934 , p_timecard_bb_ovn => g_timecard(p_block_number).object_version_number --added 2822462
935 , p_time_attribute_id => NULL);
936 /*
937 IF ( hxc_timekeeper_errors.rollback_tc (
938 p_allow_error_tc => g_allow_error_tc
939 , p_message_table => g_messages
940 , p_timecard => g_timecard ) )
941 THEN
942
943 raise e_approval_check;
944
945 END IF;
946 */
947 end if;
948 end if;
949 end if;
950 end if;
951 end if;
952
953 return l_process;
954
955 END process_block;
956
957 -- Procedure to check to see if blocks marked for update
958 -- are actually going to be updated in the database.
959 -- Blocks are marked for update by Time Sources. This
960 -- update may not acurrately reflect what will happen in the
961 -- database. For this reason we check and correct the the update
962 -- flag so that any Time Recipient Application validation will
963 -- get accurate information
964
965 PROCEDURE correct_update_flags(p_tk in BOOLEAN)
966 IS
967 l_block number;
968 --l_max_loop number:=0;
969 x number;
970
971
972 BEGIN
973 if(FND_LOG.LEVEL_PROCEDURE>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
974 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
975 'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
976 'Entering');
977 end if;
978
979 -- loop over blocks in global (non-application) block structure
980 l_block := g_timecard_block_order.first;
981
982 LOOP
983 EXIT WHEN NOT g_timecard_block_order.exists(l_block);
984 x := g_timecard_block_order(l_block);
985
986 -- for each block marked for update,
987 -- use process_block to check to see if the block will actually be updated
988
989 if(g_timecard(x).changed='Y') then
990 if(process_block(x,'UPDATE',p_tk) = FALSE) then
991
992 g_timecard(x).changed:='N';
993
994 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
995 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
996 'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
997 'Updating CHANGED flag for tbbid:'||g_timecard(x).time_building_block_id);
998 end if;
999
1000 end if;
1001 end if;
1002
1003 l_block := g_timecard_block_order.next(l_block);
1004
1005 END LOOP;
1006
1007
1008 -- for each block marked for update,
1009 -- use process_block to check to see if the block will actually be updated
1010
1011
1012 -- if block will not really be updated, set the update flag to N
1013 -- as it must be the same as the orginal block
1014
1015 if(FND_LOG.LEVEL_PROCEDURE>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1016 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1017 'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
1018 'Leaving');
1019 end if;
1020
1021 END correct_update_flags;
1022
1023
1024 FUNCTION template_name_exists
1025 (p_resource_id in NUMBER
1026 ,p_name_to_check in VARCHAR2
1027 ,p_time_building_block_id in number
1028 ) return boolean is
1029
1030 cursor c_is_dynamic_template
1031 (p_name_to_check in varchar2) is
1032 select 'Y'
1033 from hr_lookups
1034 where lookup_type = 'HXC_DYNAMIC_TEMPLATES'
1035 and meaning = p_name_to_check;
1036
1037 cursor c_is_private_template
1038 (p_name_to_check in varchar2
1039 ,p_resoure_id in number
1040 ,p_time_building_block_id in number
1041 ) is
1042 select 'Y'
1043 from hxc_time_building_blocks tbb
1044 ,hxc_time_attribute_usages tau
1045 ,hxc_time_attributes ta
1046 where ta.time_attribute_id = tau.time_attribute_id
1047 and tau.time_building_block_id = tbb.time_building_block_id
1048 and tau.time_building_block_ovn = tbb.object_version_number
1049 and tbb.date_to = hr_general.end_of_time
1050 and ta.attribute_category = 'TEMPLATES'
1051 and ta.attribute2 = 'PRIVATE'
1052 and tbb.scope = 'TIMECARD_TEMPLATE'
1053 and tbb.time_building_block_id <> p_time_building_block_id
1054 and tbb.resource_id = p_resource_id
1055 and ta.attribute1 = p_name_to_check;
1056
1057 cursor c_is_public_template
1058 (p_name_to_check in varchar2
1059 ,p_time_building_block_id in number) is
1060 select 'Y'
1061 from hxc_time_building_blocks tbb
1062 ,hxc_time_attribute_usages tau
1063 ,hxc_time_attributes ta
1064 where ta.time_attribute_id = tau.time_attribute_id
1065 and tau.time_building_block_id = tbb.time_building_block_id
1066 and tau.time_building_block_ovn = tbb.object_version_number
1067 and tbb.date_to = hr_general.end_of_time
1068 and ta.attribute_category = 'TEMPLATES'
1069 and ta.attribute2 = 'PUBLIC'
1070 and tbb.scope = 'TIMECARD_TEMPLATE'
1071 and tbb.time_building_block_id <> p_time_building_block_id
1072 and ta.attribute1 = p_name_to_check;
1073
1074 l_proc VARCHAR2(30) := 'TEMPLATE_NAME_EXISTS';
1075
1076 l_dummy VARCHAR2(2);
1077 value BOOLEAN := FALSE;
1078
1079 BEGIN
1080
1081 open c_is_dynamic_template(p_name_to_check);
1082 fetch c_is_dynamic_template into l_dummy;
1083 if c_is_dynamic_template%NOTFOUND then
1084
1085 close c_is_dynamic_template;
1086 open c_is_private_template(p_name_to_check,p_resource_id,p_time_building_block_id);
1087 fetch c_is_private_template into l_dummy;
1088 if c_is_private_template%NOTFOUND then
1089 close c_is_private_template;
1090 open c_is_public_template(p_name_to_check,p_time_building_block_id);
1091 fetch c_is_public_template into l_dummy;
1092 if c_is_public_template%NOTFOUND then
1093 close c_is_public_template;
1094 else
1095 close c_is_public_template;
1096 value:=true;
1097 end if;
1098 else
1099 close c_is_private_template;
1100 value:=true;
1101 end if;
1102 else
1103 close c_is_dynamic_template;
1104 value := true;
1105 end if;
1106
1107 return value;
1108
1109 end template_name_exists;
1110
1111 PROCEDURE initialize_globals IS
1112
1113 l_counter BINARY_INTEGER;
1114
1115 BEGIN
1116
1117 IF g_attributes.count > 0 THEN
1118
1119 g_attributes.delete;
1120
1121 END IF;
1122
1123 IF g_timecard.count >0 THEN
1124
1125 g_timecard.delete;
1126
1127 END IF;
1128
1129 IF g_messages.count >0 THEN
1130
1131 g_messages.delete;
1132
1133 END IF;
1134
1135 g_new_bbs := 'N';
1136
1137 FND_MSG_PUB.INITIALIZE;
1138
1139 END initialize_globals;
1140
1141 --
1142 -- This procedure is called from the middle tier to
1143 -- initialize the workflow information variables.
1144 --
1145 PROCEDURE set_workflow_info
1146 (p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
1147 ,p_process_name IN WF_ACTIVITIES.NAME%TYPE
1148 ) IS
1149
1150 l_proc VARCHAR2(30) := 'set_workflow_info';
1151
1152 BEGIN
1153
1154 g_workflow.item_type := p_item_type;
1155 g_workflow.process_name := p_process_name;
1156
1157 END set_workflow_info;
1158
1159 PROCEDURE find_app_deposit_process
1160 (p_time_recipient_id IN HXC_TIME_RECIPIENTS.TIME_RECIPIENT_ID%TYPE
1161 ,p_app_function IN HXC_TIME_RECIPIENTS.APPLICATION_RETRIEVAL_FUNCTION%TYPE
1162 ,p_retrieval_process_id OUT NOCOPY NUMBER) IS
1163
1164 c_ret_pro retrieval_ref_cursor;
1165
1166 CURSOR c_retrieval_process
1167 (p_app IN HXC_TIME_RECIPIENTS.TIME_RECIPIENT_ID%TYPE
1168 ,p_process_name IN HXC_RETRIEVAL_PROCESSES.NAME%TYPE)
1169 IS
1170 SELECT retrieval_process_id
1171 FROM HXC_RETRIEVAL_PROCESSES
1172 WHERE name = p_process_name
1173 AND time_recipient_id = p_app;
1174
1175 l_sql VARCHAR2(32000);
1176 l_app_process_name HXC_RETRIEVAL_PROCESSES.NAME%TYPE;
1177 l_proc VARCHAR2(30) := 'find_app_deposit_process';
1178
1179 BEGIN
1180
1181 --
1182 -- Build the SQL we need
1183 --
1184 l_sql := 'select '||p_app_function||' from dual';
1185 --
1186 -- Execute this using native dynamic SQL
1187 --
1188
1189 OPEN c_ret_pro FOR l_sql;
1190 FETCH c_ret_pro INTO l_app_process_name;
1191
1192
1193 IF c_ret_pro%NOTFOUND THEN
1194 CLOSE c_ret_pro;
1195 FND_MESSAGE.SET_NAME('HXC','HXC_XXXXX_NO_APP_RET_PROC_NAME');
1196 FND_MSG_PUB.ADD;
1197
1198 ELSE
1199
1200 CLOSE c_ret_pro;
1201
1202 END IF;
1203 --
1204 -- Next, find the process id we wish to use
1205 --
1206
1207 OPEN c_retrieval_process
1208 (p_time_recipient_id
1209 ,l_app_process_name);
1210
1211 FETCH c_retrieval_process INTO p_retrieval_process_id;
1212
1213 IF c_retrieval_process%NOTFOUND THEN
1214 CLOSE c_retrieval_process;
1215 FND_MESSAGE.SET_NAME('HXC','HXC_XXXXX_NO_RET_PROC_BY_NAME');
1216 FND_MSG_PUB.ADD;
1217
1218 ELSE
1219
1220 CLOSE c_retrieval_process;
1221
1222 END IF;
1223
1224 END find_app_deposit_process;
1225
1226 FUNCTION code_chk
1227 (p_code IN VARCHAR2) RETURN BOOLEAN IS
1228
1229 BEGIN
1230
1231 -- This change is done with respect to bug 4897975 wherein user_source is
1232 -- being used for checking the existence of a package body.
1233 -- Refer to TMSTask12613 for further analysis.
1234
1235 RETURN TRUE;
1236
1237 END code_chk;
1238
1239 PROCEDURE order_building_blocks IS
1240
1241 l_number_timecard_blocks NUMBER :=1;
1242 l_timecard_start NUMBER :=1;
1243 l_day_start NUMBER := l_timecard_start;
1244 l_detail_start NUMBER := g_timecard.count;
1245 l_block_count NUMBER :=0;
1246 l_order_count NUMBER :=0;
1247 l_proc VARCHAR2(70) := 'ORDER_BUILDING_BLOCKS';
1248 l_block NUMBER;
1249
1250 BEGIN
1251
1252 l_block := g_timecard.first;
1253
1254 LOOP
1255
1256 EXIT WHEN NOT g_timecard.exists(l_block);
1257
1258 if ((g_timecard(l_block).scope = 'TIMECARD')
1259 OR (g_timecard(l_block).scope = 'TIMECARD_TEMPLATE')) then
1260
1261 l_day_start := l_day_start +1;
1262
1263 if(g_timecard(l_block).date_to = hr_general.end_of_time) then
1264
1265 if(hxc_deposit_checks.chk_timecard_deposit
1266 (g_timecard
1267 ,l_block
1268 )
1269 ) then
1270
1271 --
1272 -- We can't deposit this timecard, as it already exists!
1273 --
1274
1275 fnd_message.set_name('HXC','HXC_OVERLAPPING_TIMECARDS');
1276 raise e_timecard_overlap;
1277
1278 end if;
1279
1280 end if;
1281
1282 end if;
1283 l_block := g_timecard.next(l_block);
1284
1285 END LOOP;
1286
1287 --
1288 -- Initialize the global record structure
1289 -- if the count is greater than zero
1290 --
1291
1292 if (g_timecard_block_order.count > 0) then
1293
1294
1295 g_timecard_block_order.delete;
1296
1297 end if;
1298
1299
1300 l_block_count := g_timecard.first;
1301
1302 LOOP
1303 EXIT WHEN NOT g_timecard.exists(l_block_count);
1304
1305 IF (g_timecard(l_block_count).scope = 'TIMECARD') THEN
1306 g_timecard_block_order(l_timecard_start) := l_block_count;
1307 l_timecard_start := l_timecard_start +1;
1308 elsif (g_timecard(l_block_count).scope = 'TIMECARD_TEMPLATE') then
1309 g_timecard_block_order(l_timecard_start) := l_block_count;
1310 ELSIF (g_timecard(l_block_count).scope = 'DAY') THEN
1311 g_timecard_block_order(l_day_start) := l_block_count;
1312 l_day_start := l_day_start +1;
1313 ELSIF (g_timecard(l_block_count).scope = 'DETAIL') THEN
1314 g_timecard_block_order(l_detail_start) := l_block_count;
1315 l_detail_start := l_detail_start -1;
1316 END IF;
1317
1318 l_block_count := g_timecard.next(l_block_count);
1319
1320 END LOOP;
1321
1322 l_block := g_timecard_block_order.first;
1323
1324 END order_building_blocks;
1325
1326
1327 FUNCTION find_block_index
1328 (p_attribute_number IN number)
1329 RETURN NUMBER IS
1330 l_block NUMBER;
1331 l_error VARCHAR(300);
1332
1333 BEGIN
1334
1335 l_block := g_timecard.first;
1336
1337 LOOP
1338
1339 EXIT WHEN
1340 ( NOT g_timecard.exists(l_block))
1341 OR
1342 (g_timecard(l_block).time_building_block_id = g_attributes(p_attribute_number).building_block_id);
1343
1344 l_block := g_timecard.next(l_block);
1345
1346 END LOOP;
1347
1348 if l_block is null then
1349 fnd_message.set_name('HXC', 'HXC_XXXXX_NULL_IDX_BLOCK');
1350 fnd_msg_pub.add;
1351 end if;
1352
1353 RETURN l_block;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 fnd_message.set_name('HXC', 'HXC_XXXXX_DEPOSIT_EXCEPTION');
1358 fnd_msg_pub.add;
1359
1360 END find_block_index;
1361
1362 FUNCTION valid_attribute(
1363 p_attribute_number IN NUMBER
1364 ) RETURN BOOLEAN IS
1365
1366 l_valid_attribute BOOLEAN := FALSE;
1367 l_block_index NUMBER := find_block_index(p_attribute_number);
1368 l_proc varchar2(32) := 'valid_attribute';
1369
1370 BEGIN
1371
1372 --dbms_output.put_line('L_BLOCK_INDEX'||l_block_index||'<<');
1373 --dbms_output.put_line('NUMBER OF BLOCKS'||g_timecard.count);
1374
1375 if (l_block_index is null) then
1376
1377 -- This is a serious exception. We must raise the problem.
1378 -- not just add it to the stack!
1379
1380 FND_MESSAGE.raise_error;
1381
1382 else
1383
1384 IF g_timecard(l_block_index).scope <> 'DETAIL' THEN
1385
1386
1387 IF ((g_attributes(p_attribute_number).attribute1 is not NULL)
1388 AND
1389 (NOT l_valid_attribute)) THEN
1390 l_valid_attribute := TRUE;
1391 END IF;
1392
1393 IF (g_attributes(p_attribute_number).attribute2 is not NULL)
1394 AND
1395 (NOT l_valid_attribute)
1396 THEN
1397 l_valid_attribute := TRUE;
1398 END IF;
1399
1400 IF (g_attributes(p_attribute_number).attribute3 is not NULL)
1401 AND
1402 (NOT l_valid_attribute)
1403 THEN
1404 l_valid_attribute := TRUE;
1405 END IF;
1406
1407 IF (g_attributes(p_attribute_number).attribute4 is not NULL)
1408 AND
1409 (NOT l_valid_attribute)
1410 THEN
1411 l_valid_attribute := TRUE;
1412 END IF;
1413
1414 IF (g_attributes(p_attribute_number).attribute5 is not NULL)
1415 AND
1416 (NOT l_valid_attribute)
1417 THEN
1418 l_valid_attribute := TRUE;
1419 END IF;
1420
1421 IF (g_attributes(p_attribute_number).attribute6 is not NULL)
1422 AND
1423 (NOT l_valid_attribute)
1424 THEN
1425 l_valid_attribute := TRUE;
1426 END IF;
1427
1428 IF (g_attributes(p_attribute_number).attribute7 is not NULL)
1429 AND
1430 (NOT l_valid_attribute)
1431 THEN
1432 l_valid_attribute := TRUE;
1433 END IF;
1434
1435 IF (g_attributes(p_attribute_number).attribute8 is not NULL)
1436 AND
1437 (NOT l_valid_attribute)
1438 THEN
1439 l_valid_attribute := TRUE;
1440 END IF;
1441
1442 IF (g_attributes(p_attribute_number).attribute9 is not NULL)
1443 AND
1444 (NOT l_valid_attribute)
1445 THEN
1446 l_valid_attribute := TRUE;
1447 END IF;
1448
1449 IF (g_attributes(p_attribute_number).attribute10 is not NULL)
1450 AND
1451 (NOT l_valid_attribute)
1452 THEN
1453 l_valid_attribute := TRUE;
1454 END IF;
1455
1456 IF (g_attributes(p_attribute_number).attribute11 is not NULL)
1457 AND
1458 (NOT l_valid_attribute)
1459 THEN
1460 l_valid_attribute := TRUE;
1461 END IF;
1462
1463 IF (g_attributes(p_attribute_number).attribute12 is not NULL)
1464 AND
1465 (NOT l_valid_attribute)
1466 THEN
1467 l_valid_attribute := TRUE;
1468 END IF;
1469
1470 IF (g_attributes(p_attribute_number).attribute13 is not NULL)
1471 AND
1472 (NOT l_valid_attribute)
1473 THEN
1474 l_valid_attribute := TRUE;
1475 END IF;
1476
1477 IF (g_attributes(p_attribute_number).attribute14 is not NULL)
1478 AND
1479 (NOT l_valid_attribute)
1480 THEN
1481 l_valid_attribute := TRUE;
1482 END IF;
1483
1484 IF (g_attributes(p_attribute_number).attribute15 is not NULL)
1485 AND
1486 (NOT l_valid_attribute)
1487 THEN
1488 l_valid_attribute := TRUE;
1489 END IF;
1490
1491 IF (g_attributes(p_attribute_number).attribute16 is not NULL)
1492 AND
1493 (NOT l_valid_attribute)
1494 THEN
1495 l_valid_attribute := TRUE;
1496 END IF;
1497
1498 IF (g_attributes(p_attribute_number).attribute17 is not NULL)
1499 AND
1500 (NOT l_valid_attribute)
1501 THEN
1502 l_valid_attribute := TRUE;
1503 END IF;
1504
1505 IF (g_attributes(p_attribute_number).attribute18 is not NULL)
1506 AND
1507 (NOT l_valid_attribute)
1508 THEN
1509 l_valid_attribute := TRUE;
1510 END IF;
1511
1512 IF (g_attributes(p_attribute_number).attribute19 is not NULL)
1513 AND
1514 (NOT l_valid_attribute)
1515 THEN
1516 l_valid_attribute := TRUE;
1517 END IF;
1518
1519 IF (g_attributes(p_attribute_number).attribute20 is not NULL)
1520 AND
1521 (NOT l_valid_attribute)
1522 THEN
1523 l_valid_attribute := TRUE;
1524 END IF;
1525
1526 IF (g_attributes(p_attribute_number).attribute21 is not NULL)
1527 AND
1528 (NOT l_valid_attribute)
1529 THEN
1530 l_valid_attribute := TRUE;
1531 END IF;
1532
1533 IF (g_attributes(p_attribute_number).attribute22 is not NULL)
1534 AND
1535 (NOT l_valid_attribute)
1536 THEN
1537 l_valid_attribute := TRUE;
1538 END IF;
1539
1540 IF (g_attributes(p_attribute_number).attribute23 is not NULL)
1541 AND
1542 (NOT l_valid_attribute)
1543 THEN
1544 l_valid_attribute := TRUE;
1545 END IF;
1546
1547 IF (g_attributes(p_attribute_number).attribute24 is not NULL)
1548 AND
1549 (NOT l_valid_attribute)
1550 THEN
1551 l_valid_attribute := TRUE;
1552 END IF;
1553
1554 IF (g_attributes(p_attribute_number).attribute25 is not NULL)
1555 AND
1556 (NOT l_valid_attribute)
1557 THEN
1558 l_valid_attribute := TRUE;
1559 END IF;
1560
1561 IF (g_attributes(p_attribute_number).attribute26 is not NULL)
1562 AND
1563 (NOT l_valid_attribute)
1564 THEN
1565 l_valid_attribute := TRUE;
1566 END IF;
1567
1568 IF (g_attributes(p_attribute_number).attribute27 is not NULL)
1569 AND
1570 (NOT l_valid_attribute)
1571 THEN
1572 l_valid_attribute := TRUE;
1573 END IF;
1574
1575 IF (g_attributes(p_attribute_number).attribute28 is not NULL)
1576 AND
1577 (NOT l_valid_attribute)
1578 THEN
1579 l_valid_attribute := TRUE;
1580 END IF;
1581
1582 IF (g_attributes(p_attribute_number).attribute29 is not NULL)
1583 AND
1584 (NOT l_valid_attribute)
1585 THEN
1586 l_valid_attribute := TRUE;
1587 END IF;
1588
1589 IF (g_attributes(p_attribute_number).attribute30 is not NULL)
1590 AND
1591 (NOT l_valid_attribute)
1592 THEN
1593 l_valid_attribute := TRUE;
1594 END IF;
1595
1596 ELSE
1597
1598 --
1599 -- Slightly different for detail building blocks
1600 --
1601
1602 IF g_attributes(p_attribute_number).attribute_category IS NOT NULL THEN
1603 IF (g_timecard(l_block_index).date_to <> hr_general.end_of_time) THEN
1604 --
1605 -- We want to make sure that attributes that go with deleted blocks
1606 -- are also sent to the validation procdures
1607 --
1608 l_valid_attribute := TRUE;
1609 ELSE
1610 IF g_timecard(l_block_index).type = 'RANGE' THEN
1611 IF g_timecard(l_block_index).start_time IS NOT NULL THEN
1612 l_valid_attribute := TRUE;
1613 ELSE
1614 l_valid_attribute := FALSE;
1615 END IF;
1616 ELSE
1617 IF g_timecard(l_block_index).measure IS NOT NULL THEN
1618 l_valid_attribute := TRUE;
1619 ELSE
1620 l_valid_attribute := FALSE;
1621 END IF;
1622 END IF;
1623 END IF;
1624 ELSE
1625 l_valid_attribute := FALSE;
1626 END IF;
1627
1628 END IF; -- is the building block associated with this attribute DETAIL?
1629
1630 end if; -- do we have a valid block index
1631
1632 RETURN l_valid_attribute;
1633
1634 END valid_attribute;
1635
1636 FUNCTION build_application_attributes(
1637 p_retrieval_process_id IN NUMBER
1638 ,p_deposit_process_id IN NUMBER --AI3
1639 ,p_for_time_attributes IN BOOLEAN
1640 ) RETURN app_attributes_info IS
1641
1642 l_app_attributes app_attributes_info;
1643
1644 CURSOR csr_mapping_components(p_retrieval_process_id NUMBER
1645 ,p_attribute_category VARCHAR2)
1646 IS
1647 SELECT
1648 mc.segment,
1649 mc.field_name,
1650 bbui.building_block_category
1651 FROM
1652 hxc_mapping_components mc,
1653 hxc_bld_blk_info_type_usages bbui,
1654 hxc_bld_blk_info_types bbit
1655 WHERE
1656 mc.mapping_component_id in (Select mcu.mapping_component_id from
1657 hxc_mappings m ,
1658 hxc_mapping_comp_usages mcu,
1659 hxc_retrieval_processes rp
1660 WHERE
1661 rp.mapping_id = m.mapping_id and
1662 rp.retrieval_process_id = p_retrieval_process_id and
1663 m.mapping_id = mcu.mapping_id )
1664
1665 AND
1666 mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id AND
1667 bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id AND
1668 bbit.bld_blk_info_type = p_attribute_category;
1669
1670 CURSOR csr_wtd_components(p_attribute_category VARCHAR2)
1671 IS
1672 select mc.segment, mc.field_name, bbui.building_block_category
1673 from hxc_mapping_components mc
1674 ,hxc_mapping_comp_usages mcu
1675 ,hxc_mappings m
1676 ,hxc_deposit_processes dp
1677 ,hxc_bld_blk_info_types bbit
1678 ,hxc_bld_blk_info_type_usages bbui
1679 where dp.mapping_id = m.mapping_id
1680 and dp.deposit_process_id = p_deposit_process_id --AI3
1681 and m.mapping_id = mcu.mapping_id
1682 and mcu.mapping_component_id = mc.mapping_component_id
1683 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
1684 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
1685 AND bbit.bld_blk_info_type = p_attribute_category;
1686
1687
1688 l_attribute BINARY_INTEGER;
1689 l_attribute_index BINARY_INTEGER :=0;
1690 l_proc VARCHAR2(70) := 'BUILD_APPLICATION_ATTRIBUTES';
1691 l_exception EXCEPTION;
1692 l_attribute_category HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE;
1693
1694 BEGIN
1695
1696 l_app_attributes.delete;
1697
1698 l_attribute := g_attributes.first;
1699
1700 LOOP
1701 EXIT WHEN NOT g_attributes.exists(l_attribute);
1702 --
1703 -- Check to see if we need this attribute
1704 --
1705 IF valid_attribute(l_attribute) THEN
1706
1707 -- IF g_attributes(l_attribute).attribute_category IS NOT NULL THEN
1708 --
1709 -- Ok, inefficiency here. We should get all the mapping
1710 -- components into a structure for each of the different
1711 -- types of attribute category, and loop through those,
1712 -- rather than opening the cursor each time.
1713 --
1714 IF p_for_time_attributes THEN
1715
1716 FOR map_rec IN csr_wtd_components
1717 (g_attributes(l_attribute).bld_blk_info_type) LOOP
1718
1719 --
1720 -- Think of a way of doing this once, and not here and in build
1721 -- timecard structure. Continue here for the moment.
1722 --
1723 l_attribute_index := l_attribute_index + 1;
1724 --
1725 l_app_attributes(l_attribute_index).time_attribute_id := g_attributes(l_attribute).time_attribute_id;
1726 l_app_attributes(l_attribute_index).building_block_id := g_attributes(l_attribute).building_block_id;
1727 l_app_attributes(l_attribute_index).category := map_rec.building_block_category;
1728 l_app_attributes(l_attribute_index).bld_blk_info_type := g_attributes(l_attribute).bld_blk_info_type;
1729 l_app_attributes(l_attribute_index).changed := g_attributes(l_attribute).changed;
1730 IF map_rec.segment = 'ATTRIBUTE1' THEN
1731 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1732 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute1;
1733 ELSIF map_rec.segment = 'ATTRIBUTE2' THEN
1734 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1735 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute2;
1736 ELSIF map_rec.segment = 'ATTRIBUTE3' THEN
1737 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1738 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute3;
1739 ELSIF map_rec.segment = 'ATTRIBUTE4' THEN
1740 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1741 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute4;
1742 ELSIF map_rec.segment = 'ATTRIBUTE5' THEN
1743 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1744 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute5;
1745 ELSIF map_rec.segment = 'ATTRIBUTE6' THEN
1746 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1747 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute6;
1748 ELSIF map_rec.segment = 'ATTRIBUTE7' THEN
1749 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1750 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute7;
1751 ELSIF map_rec.segment = 'ATTRIBUTE8' THEN
1752 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1753 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute8;
1754 ELSIF map_rec.segment = 'ATTRIBUTE9' THEN
1755 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1756 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute9;
1757 ELSIF map_rec.segment = 'ATTRIBUTE10' THEN
1758 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1759 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute10;
1760 ELSIF map_rec.segment = 'ATTRIBUTE11' THEN
1761 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1762 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute11;
1763 ELSIF map_rec.segment = 'ATTRIBUTE12' THEN
1764 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1765 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute12;
1766 ELSIF map_rec.segment = 'ATTRIBUTE13' THEN
1767 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1768 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute13;
1769 ELSIF map_rec.segment = 'ATTRIBUTE14' THEN
1770 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1771 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute14;
1772 ELSIF map_rec.segment = 'ATTRIBUTE15' THEN
1773 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1774 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute15;
1775 ELSIF map_rec.segment = 'ATTRIBUTE16' THEN
1776 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1777 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute16;
1778 ELSIF map_rec.segment = 'ATTRIBUTE17' THEN
1779 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1780 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute17;
1781 ELSIF map_rec.segment = 'ATTRIBUTE18' THEN
1782 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1783 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute18;
1784 ELSIF map_rec.segment = 'ATTRIBUTE19' THEN
1785 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1786 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute19;
1787 ELSIF map_rec.segment = 'ATTRIBUTE20' THEN
1788 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1789 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute20;
1790 ELSIF map_rec.segment = 'ATTRIBUTE21' THEN
1791 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1792 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute21;
1793 ELSIF map_rec.segment = 'ATTRIBUTE22' THEN
1794 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1795 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute22;
1796 ELSIF map_rec.segment = 'ATTRIBUTE23' THEN
1797 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1798 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute23;
1799 ELSIF map_rec.segment = 'ATTRIBUTE24' THEN
1800 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1801 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute24;
1802 ELSIF map_rec.segment = 'ATTRIBUTE25' THEN
1803 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1804 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute25;
1805 ELSIF map_rec.segment = 'ATTRIBUTE26' THEN
1806 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1807 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute26;
1808 ELSIF map_rec.segment = 'ATTRIBUTE27' THEN
1809 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1810 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute27;
1811 ELSIF map_rec.segment = 'ATTRIBUTE28' THEN
1812 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1813 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute28;
1814 ELSIF map_rec.segment = 'ATTRIBUTE29' THEN
1815 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1816 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute29;
1817 ELSIF map_rec.segment = 'ATTRIBUTE30' THEN
1818 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1819 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute30;
1820 ELSIF map_rec.segment = 'ATTRIBUTE_CATEGORY' THEN
1821 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1822 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute_category;
1823 ELSE
1824 RAISE l_exception;
1825 END IF;
1826
1827 END LOOP; -- mapping loop
1828
1829 ELSE
1830
1831 FOR map_rec IN csr_mapping_components
1832 (p_retrieval_process_id
1833 ,g_attributes(l_attribute).bld_blk_info_type) LOOP
1834
1835 --
1836 -- Think of a way of doing this once, and not here and in build
1837 -- timecard structure. Continue here for the moment.
1838 --
1839 l_attribute_index := l_attribute_index + 1;
1840 --
1841 l_app_attributes(l_attribute_index).time_attribute_id := g_attributes(l_attribute).time_attribute_id;
1842 l_app_attributes(l_attribute_index).building_block_id := g_attributes(l_attribute).building_block_id;
1843 l_app_attributes(l_attribute_index).category := map_rec.building_block_category;
1844 l_app_attributes(l_attribute_index).bld_blk_info_type := g_attributes(l_attribute).bld_blk_info_type;
1845 l_app_attributes(l_attribute_index).changed := g_attributes(l_attribute).changed;
1846 IF map_rec.segment = 'ATTRIBUTE1' THEN
1847 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1848 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute1;
1849 ELSIF map_rec.segment = 'ATTRIBUTE2' THEN
1850 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1851 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute2;
1852 ELSIF map_rec.segment = 'ATTRIBUTE3' THEN
1853 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1854 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute3;
1855 ELSIF map_rec.segment = 'ATTRIBUTE4' THEN
1856 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1857 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute4;
1858 ELSIF map_rec.segment = 'ATTRIBUTE5' THEN
1859 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1860 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute5;
1861 ELSIF map_rec.segment = 'ATTRIBUTE6' THEN
1862 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1863 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute6;
1864 ELSIF map_rec.segment = 'ATTRIBUTE7' THEN
1865 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1866 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute7;
1867 ELSIF map_rec.segment = 'ATTRIBUTE8' THEN
1868 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1869 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute8;
1870 ELSIF map_rec.segment = 'ATTRIBUTE9' THEN
1871 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1872 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute9;
1873 ELSIF map_rec.segment = 'ATTRIBUTE10' THEN
1874 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1875 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute10;
1876 ELSIF map_rec.segment = 'ATTRIBUTE11' THEN
1877 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1878 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute11;
1879 ELSIF map_rec.segment = 'ATTRIBUTE12' THEN
1880 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1881 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute12;
1882 ELSIF map_rec.segment = 'ATTRIBUTE13' THEN
1883 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1884 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute13;
1885 ELSIF map_rec.segment = 'ATTRIBUTE14' THEN
1886 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1887 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute14;
1888 ELSIF map_rec.segment = 'ATTRIBUTE15' THEN
1889 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1890 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute15;
1891 ELSIF map_rec.segment = 'ATTRIBUTE16' THEN
1892 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1893 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute16;
1894 ELSIF map_rec.segment = 'ATTRIBUTE17' THEN
1895 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1896 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute17;
1897 ELSIF map_rec.segment = 'ATTRIBUTE18' THEN
1898 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1899 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute18;
1900 ELSIF map_rec.segment = 'ATTRIBUTE19' THEN
1901 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1902 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute19;
1903 ELSIF map_rec.segment = 'ATTRIBUTE20' THEN
1904 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1905 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute20;
1906 ELSIF map_rec.segment = 'ATTRIBUTE21' THEN
1907 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1908 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute21;
1909 ELSIF map_rec.segment = 'ATTRIBUTE22' THEN
1910 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1911 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute22;
1912 ELSIF map_rec.segment = 'ATTRIBUTE23' THEN
1913 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1914 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute23;
1915 ELSIF map_rec.segment = 'ATTRIBUTE24' THEN
1916 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1917 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute24;
1918 ELSIF map_rec.segment = 'ATTRIBUTE25' THEN
1919 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1920 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute25;
1921 ELSIF map_rec.segment = 'ATTRIBUTE26' THEN
1922 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1923 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute26;
1924 ELSIF map_rec.segment = 'ATTRIBUTE27' THEN
1925 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1926 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute27;
1927 ELSIF map_rec.segment = 'ATTRIBUTE28' THEN
1928 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1929 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute28;
1930 ELSIF map_rec.segment = 'ATTRIBUTE29' THEN
1931 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1932 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute29;
1933 ELSIF map_rec.segment = 'ATTRIBUTE30' THEN
1934 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1935 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute30;
1936 ELSIF map_rec.segment = 'ATTRIBUTE_CATEGORY' THEN
1937 l_app_attributes(l_attribute_index).attribute_name := map_rec.field_name;
1938 l_app_attributes(l_attribute_index).attribute_value := g_attributes(l_attribute).attribute_category;
1939 ELSE
1940 RAISE l_exception;
1941 END IF;
1942
1943 END LOOP; -- mapping loop
1944
1945 END IF; -- Is this for time attributes
1946
1947 END IF; -- Is this a viable attribute.
1948
1949 l_attribute := g_attributes.next(l_attribute);
1950
1951 END LOOP; -- attribute loop
1952
1953 RETURN l_app_attributes;
1954
1955 END build_application_attributes;
1956
1957 FUNCTION get_new_attribute_id RETURN NUMBER IS
1958
1959 l_att_count NUMBER :=0;
1960 l_proc VARCHAR2(30) := 'GET_NEW_ATTRIBUTE_ID';
1961 l_low_value NUMBER :=0;
1962
1963
1964 BEGIN
1965
1966 IF g_time_attribute_id = 0 THEN
1967
1968 --
1969 -- Find the last new time attribute id
1970 --
1971
1972 l_att_count := g_attributes.first;
1973
1974 LOOP
1975 EXIT WHEN NOT g_attributes.exists(l_att_count);
1976
1977 IF l_low_value > g_attributes(l_att_count).time_Attribute_id THEN
1978
1979 l_low_value := g_attributes(l_att_count).time_attribute_id;
1980
1981 END IF;
1982
1983 l_att_count := g_attributes.next(l_att_count);
1984 END LOOP;
1985
1986 g_time_attribute_id := l_low_value-10000000;
1987
1988 ELSE
1989
1990 g_time_attribute_id := g_time_attribute_id -1;
1991
1992 END IF;
1993
1994 RETURN g_time_attribute_id;
1995
1996 END get_new_attribute_id;
1997
1998 FUNCTION get_bld_blk_type_id(p_type IN varchar2) RETURN NUMBER IS
1999
2000 CURSOR csr_bld_blk_id(p_type IN varchar2) IS
2001 SELECT bld_blk_info_type_id
2002 FROM hxc_bld_blk_info_types
2003 WHERE bld_blk_info_type = p_type;
2004
2005 BEGIN
2006
2007 IF g_security_type_id = 0 THEN
2008
2009 OPEN csr_bld_blk_id(p_type);
2010 FETCH csr_bld_blk_id INTO g_security_type_id;
2011
2012 IF csr_bld_blk_id%NOTFOUND THEN
2013 CLOSE csr_bld_blk_id;
2014 FND_MESSAGE.SET_NAME('HXC','HXC_XXXXX_NO_SECURITY_CONTEXT');
2015 fnd_msg_pub.add;
2016
2017 END IF;
2018
2019 CLOSE csr_bld_blk_id;
2020
2021 END IF;
2022
2023 RETURN g_security_type_id;
2024
2025 END get_bld_blk_type_id;
2026
2027 FUNCTION attribute_check
2028 (p_to_check IN VARCHAR2
2029 ,p_time_building_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE
2030 ) RETURN BOOLEAN IS
2031
2032 l_doesnt_exist BOOLEAN := TRUE;
2033 l_attribute_count NUMBER;
2034 l_proc VARCHAR2(30) := 'attribute_check';
2035
2036
2037 BEGIN
2038
2039 l_attribute_count := g_attributes.first;
2040
2041 LOOP
2042 EXIT WHEN ((NOT g_attributes.exists(l_attribute_count)) OR (NOT l_doesnt_exist));
2043
2044 IF (
2045 (g_attributes(l_attribute_count).building_block_id = p_time_building_block_id)
2046 AND
2047 (g_attributes(l_attribute_count).attribute_category = p_to_check)
2048 ) THEN
2049 if(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2050 fnd_log.string(1,'hxc_self_service_time_deposit.attribute_check',
2051 ' Found the expected attribute');
2052 end if;
2053
2054 l_doesnt_exist := FALSE;
2055
2056 END IF;
2057
2058 l_attribute_count := g_attributes.next(l_attribute_count);
2059
2060 END LOOP;
2061
2062 RETURN l_doesnt_exist;
2063
2064 END attribute_check;
2065
2066 --
2067
2068 PROCEDURE denormalize_time_info(p_mode in VARCHAR2) IS
2069
2070 l_index number;
2071
2072
2073 BEGIN
2074
2075 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2076 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_DENORMALIZE_BLOCK_INFO',
2077 'Enter denormalize_block_info, mode ='||p_mode);
2078 end if;
2079
2080 -- note that we denormalise measure for ALL range start_time stop_times (regardless of scope)
2081 -- note also that the UOM for these blocks is HOURS
2082 -- This is done for ALL scopes of building blocks since we dont need to
2083 -- start adding scope specific code.
2084
2085 l_index := g_timecard.first;
2086
2087 WHILE ( l_index IS NOT NULL ) LOOP
2088
2089 IF (p_mode = 'ADD') THEN
2090
2091 IF(g_timecard(l_index).type = 'RANGE' ) THEN
2092 g_timecard(l_index).measure:= (g_timecard(l_index).stop_time-g_timecard(l_index).start_time)*24;
2093 g_timecard(l_index).unit_of_measure:= 'HOURS';
2094 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2095 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_DENORMALIZE_BLOCK_INFO',
2096 'NEW MEASURE VAL '||g_timecard(l_index).measure ||
2097 ' l_timecard_id :'||g_timecard(l_index).time_building_block_id);
2098 end if;
2099 END IF;
2100
2101 ELSIF (p_mode = 'REMOVE') THEN
2102
2103 IF(g_timecard(l_index).type = 'RANGE' ) THEN
2104 g_timecard(l_index).measure:= null;
2105 g_timecard(l_index).unit_of_measure:= null;
2106 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2107 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_DENORMALIZE_BLOCK_INFO',
2108 'NEW MEASURE VAL'||g_timecard(l_index).measure);
2109 end if;
2110 END IF;
2111
2112 END IF;
2113
2114 l_index := g_timecard.next(l_index);
2115
2116 END LOOP;
2117
2118 END denormalize_time_info;
2119
2120
2121 --AI8
2122 PROCEDURE set_security(p_validate_session IN BOOLEAN) IS
2123
2124 -- GPM v115.78
2125
2126 PRAGMA AUTONOMOUS_TRANSACTION;
2127
2128 l_defined BOOLEAN;
2129 l_validate_session BOOLEAN;
2130
2131 BEGIN
2132
2133 -- Determine security information to be added where block has no security attribute
2134 /*
2135 if(p_validate_session) then -- icx_sec.validateSession sets all the fnd profile values
2136 -- l_validate_session := icx_sec.validateSession;
2137 null;
2138 end if;
2139 */
2140 -- Note that the global variables set here will be used by add_security
2141
2142 fnd_profile.get_specific('ORG_ID',null,null,null,g_org_id, l_defined);
2143 g_bg_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
2144
2145 -- Both should be set, so raise an error if there is a problem
2146 -- TO BE COMPLETED
2147
2148 END set_security;
2149 --AI8
2150
2151
2152
2153 PROCEDURE add_security IS
2154
2155 l_last_attribute NUMBER;
2156 l_block_count NUMBER;
2157 l_create BOOLEAN := FALSE;
2158 l_proc VARCHAR2(30) := 'ADD_SECURITY';
2159 l_profile_org NUMBER;
2160
2161
2162 BEGIN
2163
2164 -- 115.110 Change.
2165
2166 if (g_attributes.count >0) then
2167 l_last_attribute := g_attributes.last;
2168 else
2169 l_last_attribute := 1;
2170 end if;
2171
2172 -- End 115.110 Change.
2173
2174 --
2175 -- Now for each building block, we need to add
2176 -- a security attribute, highlighting the business group
2177 -- id and org id
2178 --
2179
2180 l_block_count := g_timecard.first;
2181
2182
2183 LOOP
2184 EXIT WHEN NOT g_timecard.exists(l_block_count);
2185 l_last_attribute := l_last_attribute+1;
2186
2187 --
2188 -- Create the time attribute for security for this block
2189 -- if a security context doesn't already exist
2190 --
2191 if(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2192 fnd_log.string(1,'hxc_self_service_time_deposit.add_security',
2193 ' tbb '||g_timecard(l_block_count).time_building_block_id);
2194 end if;
2195
2196 l_create := attribute_check
2197 (p_to_check => 'SECURITY'
2198 ,p_time_building_block_id => g_timecard(l_block_count).time_building_block_id
2199 );
2200
2201
2202 IF l_create THEN
2203
2204
2205 if(1>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2206 fnd_log.string(1,'hxc_self_service_time_deposit.add_security',
2207 ' Creating a security attribute!');
2208 end if;
2209
2210
2211 g_attributes(l_last_attribute).TIME_ATTRIBUTE_ID := get_new_attribute_id;
2212
2213 g_attributes(l_last_attribute).BUILDING_BLOCK_ID := g_timecard(l_block_count).time_building_block_id;
2214 g_attributes(l_last_attribute).BLD_BLK_INFO_TYPE := 'SECURITY';
2215 g_attributes(l_last_attribute).ATTRIBUTE_CATEGORY := 'SECURITY';
2216 g_attributes(l_last_attribute).ATTRIBUTE1 := g_org_id; -- AI8
2217 g_attributes(l_last_attribute).ATTRIBUTE2 := g_bg_id; -- AI8
2218 g_attributes(l_last_attribute).ATTRIBUTE3 := NULL;
2219 g_attributes(l_last_attribute).ATTRIBUTE4 := NULL;
2220 g_attributes(l_last_attribute).ATTRIBUTE5 := NULL;
2221 g_attributes(l_last_attribute).ATTRIBUTE6 := NULL;
2222 g_attributes(l_last_attribute).ATTRIBUTE7 := NULL;
2223 g_attributes(l_last_attribute).ATTRIBUTE8 := NULL;
2224 g_attributes(l_last_attribute).ATTRIBUTE9 := NULL;
2225 g_attributes(l_last_attribute).ATTRIBUTE10 := NULL;
2226 g_attributes(l_last_attribute).ATTRIBUTE11 := NULL;
2227 g_attributes(l_last_attribute).ATTRIBUTE12 := NULL;
2228 g_attributes(l_last_attribute).ATTRIBUTE13 := NULL;
2229 g_attributes(l_last_attribute).ATTRIBUTE14 := NULL;
2230 g_attributes(l_last_attribute).ATTRIBUTE15 := NULL;
2231 g_attributes(l_last_attribute).ATTRIBUTE16 := NULL;
2232 g_attributes(l_last_attribute).ATTRIBUTE17 := NULL;
2233 g_attributes(l_last_attribute).ATTRIBUTE18 := NULL;
2234 g_attributes(l_last_attribute).ATTRIBUTE19 := NULL;
2235 g_attributes(l_last_attribute).ATTRIBUTE20 := NULL;
2236 g_attributes(l_last_attribute).ATTRIBUTE21 := NULL;
2237 g_attributes(l_last_attribute).ATTRIBUTE22 := NULL;
2238 g_attributes(l_last_attribute).ATTRIBUTE23 := NULL;
2239 g_attributes(l_last_attribute).ATTRIBUTE24 := NULL;
2240 g_attributes(l_last_attribute).ATTRIBUTE25 := NULL;
2241 g_attributes(l_last_attribute).ATTRIBUTE26 := NULL;
2242 g_attributes(l_last_attribute).ATTRIBUTE27 := NULL;
2243 g_attributes(l_last_attribute).ATTRIBUTE28 := NULL;
2244 g_attributes(l_last_attribute).ATTRIBUTE29 := NULL;
2245 g_attributes(l_last_attribute).ATTRIBUTE30 := NULL;
2246 g_attributes(l_last_attribute).BLD_BLK_INFO_TYPE_ID := cache_bld_blk_info_types('SECURITY');
2247 g_attributes(l_last_attribute).OBJECT_VERSION_NUMBER := 1;
2248 g_attributes(l_last_attribute).NEW := 'Y';
2249
2250 END IF;
2251
2252 l_block_count := g_timecard.next(l_block_count);
2253
2254 END LOOP;
2255
2256 EXCEPTION
2257 when others then
2258 -- debug(l_proc,10,substr(SQLERRM,1,2000));
2259 FND_MESSAGE.SET_NAME('HXC','HXC_PROBLEM');
2260 FND_MESSAGE.SET_TOKEN('CLIENT_INFO',userenv('CLIENT_INFO'));
2261 FND_MESSAGE.SET_TOKEN('CLIENT_INFO_START',substr(userenv('CLIENT_INFO'),1,10));
2262 FND_MESSAGE.RAISE_ERROR;
2263 END add_security;
2264
2265 FUNCTION replacement_attribute
2266 (p_block_number in number
2267 ,p_attribute_number in number) RETURN BOOLEAN is
2268
2269 l_return BOOLEAN := false;
2270 l_tbb_id NUMBER := g_attributes(p_attribute_number).building_block_id;
2271 l_bb_info HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE := g_attributes(p_attribute_number).bld_blk_info_type;
2272 l_acount NUMBER;
2273 l_proc VARCHAR2(35) := 'REPLACEMENT_ATTRIBUTE';
2274
2275 BEGIN
2276
2277 l_acount := g_attributes.first;
2278
2279 LOOP
2280 EXIT WHEN ((NOT g_attributes.exists(l_acount)) OR (l_return));
2281
2282 if(
2283 (g_attributes(l_acount).NEW <> 'Y')
2284 AND
2285 (l_acount <> p_attribute_number)
2286 AND
2287 (g_attributes(l_acount).bld_blk_info_type = l_bb_info)
2288 AND
2289 (g_attributes(l_acount).building_block_id = l_tbb_id)
2290 ) then
2291
2292 l_return := true;
2293
2294 end if;
2295
2296 l_acount := g_attributes.next(l_acount);
2297
2298 END LOOP;
2299
2300 return l_return;
2301
2302 END replacement_attribute;
2303
2304 PROCEDURE build_timecard_structure(
2305 p_block IN NUMBER
2306 ,p_deposit_process_id IN NUMBER
2307 ,p_timecard IN OUT NOCOPY hxc_time_attributes_api.timecard
2308 ,p_update_required IN OUT NOCOPY BOOLEAN)
2309 IS
2310 CURSOR csr_bb_attributes(p_time_attribute_id NUMBER,
2311 p_ta_ovn NUMBER) IS
2312 select *
2313 from hxc_time_attributes
2314 where time_attribute_id = p_time_attribute_id
2315 and (
2316 (object_version_number = p_ta_ovn)
2317 OR
2318 (p_ta_ovn IS NULL)
2319 );
2320 --
2321 CURSOR csr_mapping_components(p_deposit_process_id NUMBER
2322 ,p_bbit_id NUMBER)
2323 IS
2324 select mc.segment, mc.field_name,bbit.bld_blk_info_type
2325 from hxc_mapping_components mc
2326 ,hxc_mapping_comp_usages mcu
2327 ,hxc_mappings m
2328 ,hxc_deposit_processes dp
2329 ,hxc_bld_blk_info_types bbit
2330 where dp.mapping_id = m.mapping_id
2331 and dp.deposit_process_id = p_deposit_process_id
2332 and m.mapping_id = mcu.mapping_id
2333 and mcu.mapping_component_id = mc.mapping_component_id
2334 and mc.bld_blk_info_type_id = p_bbit_id
2335 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
2336 --
2337
2338
2339 l_attribute_index NUMBER;
2340 l_bld_blk_info_type_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
2341 l_bld_blk_info_type VARCHAR2(150);
2342 l_proc VARCHAR2(30) := 'build_timecard_structure';
2343 l_old_attribute HXC_TIME_ATTRIBUTES%ROWTYPE;
2344 l_exception EXCEPTION;
2345 l_att_count NUMBER;
2346 y NUMBER;
2347 l_replacement BOOLEAN := false;
2348
2349 BEGIN
2350
2351 l_attribute_index := 0;
2352
2353 p_update_required := FALSE;
2354
2355 -- FOR y IN 1 .. g_attributes.count LOOP
2356
2357 y := g_attributes.first;
2358
2359 LOOP
2360 EXIT WHEN NOT g_attributes.exists(y);
2361 --
2362 -- Check to see if this attribute record
2363 -- corresponds to the building block
2364 -- under current consideration.
2365 --
2366
2367 if (g_attributes(y).building_block_id = g_timecard(p_block).time_building_block_id) then
2368
2369 -- we need to do a reverse lookup on the attribute name...
2370 -- find the building block info type id
2371
2372 l_bld_blk_info_type_id := cache_bld_blk_info_types(g_attributes(y).bld_blk_info_type);
2373
2374 --
2375 -- we need to get the old time attribute id record, to see
2376 -- if anything has changed before we can update the record
2377 --
2378
2379 IF (g_attributes(y).new = 'N') THEN
2380
2381 BEGIN
2382 open csr_bb_attributes(
2383 g_attributes(y).time_attribute_id
2384 ,g_attributes(y).object_version_number
2385 );
2386 fetch csr_bb_attributes into l_old_attribute;
2387 close csr_bb_attributes;
2388 EXCEPTION
2389 WHEN OTHERS THEN
2390 FND_MESSAGE.set_name('HXC','HXC_XXXXX_OLD_ATTRIBUTE');
2391 FND_MESSAGE.set_token('TOKEN',g_attributes(y).time_attribute_id);
2392 fnd_msg_pub.add;
2393 END;
2394 ELSIF (g_attributes(y).new = 'Y') THEN
2395 p_update_required := TRUE;
2396 END IF;
2397 --
2398 -- now for each mapping component we need to loop through the columns to find which one matches
2399 FOR comp IN csr_mapping_components(p_deposit_process_id, l_bld_blk_info_type_id) LOOP
2400 -- increment the timecard structure index
2401 l_attribute_index := l_attribute_index + 1;
2402 --
2403 IF comp.segment = 'ATTRIBUTE1' THEN
2404 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2405 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute1;
2406 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2407 IF p_update_required <> TRUE then
2408 p_update_required:= update_required(l_old_attribute.attribute1, g_attributes(y).attribute1);
2409 END IF;
2410 ELSIF comp.segment = 'ATTRIBUTE2' THEN
2411 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2412 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute2;
2413 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2414 IF p_update_required <> TRUE THEN
2415 p_update_required:= update_required(l_old_attribute.attribute2, g_attributes(y).attribute2);
2416 END IF;
2417 ELSIF comp.segment = 'ATTRIBUTE3' THEN
2418 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2419 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute3;
2420 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2421 IF p_update_required <> TRUE THEN
2422 p_update_required:= update_required(l_old_attribute.attribute3, g_attributes(y).attribute3);
2423 END IF;
2424 ELSIF comp.segment = 'ATTRIBUTE4' THEN
2425 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2426 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute4;
2427 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2428 IF p_update_required <> TRUE THEN
2429 p_update_required:= update_required(l_old_attribute.attribute4, g_attributes(y).attribute4);
2430 END IF;
2431 ELSIF comp.segment = 'ATTRIBUTE5' THEN
2432 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2433 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute5;
2434 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2435 IF p_update_required <> TRUE THEN
2436 p_update_required:= update_required(l_old_attribute.attribute5, g_attributes(y).attribute5);
2437 END IF;
2438 ELSIF comp.segment = 'ATTRIBUTE6' THEN
2439 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2440 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute6;
2441 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2442 IF p_update_required <> TRUE THEN
2443 p_update_required:= update_required(l_old_attribute.attribute6, g_attributes(y).attribute6);
2444 END IF;
2445 ELSIF comp.segment = 'ATTRIBUTE7' THEN
2446 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2447 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute7;
2448 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2449 IF p_update_required <> TRUE THEN
2450 p_update_required:= update_required(l_old_attribute.attribute7, g_attributes(y).attribute7);
2451 END IF;
2452 ELSIF comp.segment = 'ATTRIBUTE8' THEN
2453 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2454 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute8;
2455 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2456 IF p_update_required <> TRUE THEN
2457 p_update_required:= update_required(l_old_attribute.attribute8, g_attributes(y).attribute8);
2458 END IF;
2459 ELSIF comp.segment = 'ATTRIBUTE9' THEN
2460 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2461 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute9;
2462 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2463 IF p_update_required <> TRUE THEN
2464 p_update_required:= update_required(l_old_attribute.attribute9, g_attributes(y).attribute9);
2465 END IF;
2466 ELSIF comp.segment = 'ATTRIBUTE10' THEN
2467 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2468 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute10;
2469 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2470 IF p_update_required <> TRUE THEN
2471 p_update_required:= update_required(l_old_attribute.attribute10, g_attributes(y).attribute10);
2472 END IF;
2473 ELSIF comp.segment = 'ATTRIBUTE11' THEN
2474 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2475 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute11;
2476 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2477 IF p_update_required <> TRUE THEN
2478 p_update_required:= update_required(l_old_attribute.attribute11, g_attributes(y).attribute11);
2479 END IF;
2480 ELSIF comp.segment = 'ATTRIBUTE12' THEN
2481 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2482 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute12;
2483 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2484 IF p_update_required <> TRUE THEN
2485 p_update_required:= update_required(l_old_attribute.attribute12, g_attributes(y).attribute12);
2486 END IF;
2487 ELSIF comp.segment = 'ATTRIBUTE13' THEN
2488 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2489 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute13;
2490 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2491 IF p_update_required <> TRUE THEN
2492 p_update_required:= update_required(l_old_attribute.attribute13, g_attributes(y).attribute13);
2493 END IF;
2494 ELSIF comp.segment = 'ATTRIBUTE14' THEN
2495 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2496 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute14;
2497 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2498 IF p_update_required <> TRUE THEN
2499 p_update_required:= update_required(l_old_attribute.attribute14, g_attributes(y).attribute14);
2500 END IF;
2501 ELSIF comp.segment = 'ATTRIBUTE15' THEN
2502 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2503 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute15;
2504 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2505 IF p_update_required <> TRUE THEN
2506 p_update_required:= update_required(l_old_attribute.attribute15, g_attributes(y).attribute15);
2507 END IF;
2508 ELSIF comp.segment = 'ATTRIBUTE16' THEN
2509 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2510 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute16;
2511 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2512 IF p_update_required <> TRUE THEN
2513 p_update_required:= update_required(l_old_attribute.attribute16, g_attributes(y).attribute16);
2514 END IF;
2515 ELSIF comp.segment = 'ATTRIBUTE17' THEN
2516 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2517 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute17;
2518 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2519 IF p_update_required <> TRUE THEN
2520 p_update_required:= update_required(l_old_attribute.attribute17, g_attributes(y).attribute17);
2521 END IF;
2522 ELSIF comp.segment = 'ATTRIBUTE18' THEN
2523 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2524 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute18;
2525 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2526 IF p_update_required <> TRUE THEN
2527 p_update_required:= update_required(l_old_attribute.attribute18, g_attributes(y).attribute18);
2528 END IF;
2529 ELSIF comp.segment = 'ATTRIBUTE19' THEN
2530 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2531 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute19;
2532 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2533 IF p_update_required <> TRUE THEN
2534 p_update_required:= update_required(l_old_attribute.attribute19, g_attributes(y).attribute19);
2535 END IF;
2536 ELSIF comp.segment = 'ATTRIBUTE20' THEN
2537 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2538 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute20;
2539 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2540 IF p_update_required <> TRUE THEN
2541 p_update_required:= update_required(l_old_attribute.attribute20, g_attributes(y).attribute20);
2542 END IF;
2543 ELSIF comp.segment = 'ATTRIBUTE21' THEN
2544 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2545 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute21;
2546 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2547 IF p_update_required <> TRUE THEN
2548 p_update_required:= update_required(l_old_attribute.attribute21, g_attributes(y).attribute21);
2549 END IF;
2550 ELSIF comp.segment = 'ATTRIBUTE22' THEN
2551 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2552 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute22;
2553 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2554 IF p_update_required <> TRUE THEN
2555 p_update_required:= update_required(l_old_attribute.attribute22, g_attributes(y).attribute22);
2556 END IF;
2557 ELSIF comp.segment = 'ATTRIBUTE23' THEN
2558 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2559 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute23;
2560 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2561 IF p_update_required <> TRUE THEN
2562 p_update_required:= update_required(l_old_attribute.attribute23, g_attributes(y).attribute23);
2563 END IF;
2564 ELSIF comp.segment = 'ATTRIBUTE24' THEN
2565 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2566 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute24;
2567 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2568 IF p_update_required <> TRUE THEN
2569 p_update_required:= update_required(l_old_attribute.attribute24, g_attributes(y).attribute24);
2570 END IF;
2571 ELSIF comp.segment = 'ATTRIBUTE25' THEN
2572 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2573 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute25;
2574 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2575 IF p_update_required <> TRUE THEN
2576 p_update_required:= update_required(l_old_attribute.attribute25, g_attributes(y).attribute25);
2577 END IF;
2578 ELSIF comp.segment = 'ATTRIBUTE26' THEN
2579 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2580 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute26;
2581 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2582 IF p_update_required <> TRUE THEN
2583 p_update_required:= update_required(l_old_attribute.attribute26, g_attributes(y).attribute26);
2584 END IF;
2585 ELSIF comp.segment = 'ATTRIBUTE27' THEN
2586 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2587 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute27;
2588 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2589 IF p_update_required <> TRUE THEN
2590 p_update_required:= update_required(l_old_attribute.attribute27, g_attributes(y).attribute27);
2591 END IF;
2592 ELSIF comp.segment = 'ATTRIBUTE28' THEN
2593 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2594 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute28;
2595 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2596 IF p_update_required <> TRUE THEN
2597 p_update_required:= update_required(l_old_attribute.attribute28, g_attributes(y).attribute28);
2598 END IF;
2599 ELSIF comp.segment = 'ATTRIBUTE29' THEN
2600 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2601 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute29;
2602 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2603 IF p_update_required <> TRUE THEN
2604 p_update_required:= update_required(l_old_attribute.attribute29, g_attributes(y).attribute29);
2605 END IF;
2606 ELSIF comp.segment = 'ATTRIBUTE30' THEN
2607 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2608 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute30;
2609 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2610 IF p_update_required <> TRUE THEN
2611 p_update_required:= update_required(l_old_attribute.attribute30, g_attributes(y).attribute30);
2612 END IF;
2613 ELSIF comp.segment = 'ATTRIBUTE_CATEGORY' THEN
2614 p_timecard(l_attribute_index).attribute_name := comp.field_name;
2615 p_timecard(l_attribute_index).attribute_value := g_attributes(y).attribute_category;
2616 p_timecard(l_attribute_index).information_type := g_attributes(y).bld_blk_info_type;
2617 IF p_update_required <> TRUE THEN
2618 p_update_required:= update_required(l_old_attribute.attribute_category, g_attributes(y).attribute_category);
2619 END IF;
2620 ELSE
2621 RAISE l_exception;
2622 END IF;
2623 -- add the new column name information
2624 p_timecard(l_attribute_index).column_name := comp.segment;
2625 p_timecard(l_attribute_index).info_mapping_type := comp.bld_blk_info_type;
2626
2627 END LOOP;
2628
2629
2630 --
2631 -- Check to see if we're allowed to update this attribute
2632 --
2633 if((g_timecard(p_block).new <> 'Y') AND (g_timecard(p_block).scope = 'DETAIL')) then
2634
2635 if(
2636 ((g_attributes(y).changed = 'Y')AND(p_update_required))
2637 ) then
2638
2639 if(check_approval_locked(p_block))then
2640
2641 hxc_time_entry_rules_utils_pkg.add_error_to_table (
2642 p_message_table => g_messages
2643 , p_message_name => 'HXC_NO_MODIFY_APPROVED_DETAIL'
2644 , p_message_token => NULL
2645 , p_message_level => 'ERROR'
2646 , p_message_field => NULL
2647 , p_timecard_bb_id => g_timecard(p_block).time_building_block_id
2648 , p_timecard_bb_ovn => g_timecard(p_block).object_version_number --added 2822462
2649 , p_time_attribute_id => NULL);
2650 /*
2651 IF ( hxc_timekeeper_errors.rollback_tc (
2652 p_allow_error_tc => g_allow_error_tc
2653 , p_message_table => g_messages
2654 , p_timecard => g_timecard ) )
2655 THEN
2656
2657 raise e_approval_check;
2658
2659 END IF;
2660 */
2661 end if;
2662 end if;
2663 end if;
2664
2665 end if; -- is this attribute record for this building block
2666
2667 y:= g_attributes.next(y);
2668
2669 END LOOP;
2670
2671 END build_timecard_structure;
2672
2673 PROCEDURE update_attribute_record
2674 (p_attribute_number IN BINARY_INTEGER
2675 ,p_app_attribute_count IN NUMBER
2676 ,p_mapping IN t_mapping
2677 ,p_app_attributes IN OUT NOCOPY app_attributes_info
2678 ) IS
2679
2680 CURSOR csr_segment(p_retrieval_process_id NUMBER
2681 ,p_attribute_category VARCHAR2
2682 ,p_field_name VARCHAR2)
2683 IS
2684 select mc.segment
2685 from hxc_mapping_components mc
2686 ,hxc_mapping_comp_usages mcu
2687 ,hxc_mappings m
2688 ,hxc_retrieval_processes rp
2689 ,hxc_bld_blk_info_types bbit
2690 ,hxc_bld_blk_info_type_usages bbui
2691 where rp.mapping_id = m.mapping_id
2692 AND mc.field_name = p_field_name
2693 and rp.retrieval_process_id = p_retrieval_process_id
2694 and m.mapping_id = mcu.mapping_id
2695 and mcu.mapping_component_id = mc.mapping_component_id
2696 and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
2697 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
2698 AND bbit.bld_blk_info_type = p_attribute_category;
2699
2700 l_time_attribute_id hxc_time_attributes.time_attribute_id%TYPE;
2701 l_segment hxc_mapping_components.segment%TYPE;
2702 l_att_count NUMBER;
2703 l_mapping_component t_mapping_comp; --AI7
2704
2705 l_proc varchar2(32);
2706 l_dummy number;
2707 BEGIN
2708
2709
2710
2711 --
2712 -- Obtain the time attribute id process id
2713 --
2714
2715 l_time_attribute_id := p_app_attributes(p_app_attribute_count).time_attribute_id;
2716
2717 --
2718 -- Next loop over the APP attributes, to find the ones with the same
2719 -- time attribute id, and check those against the mapping components
2720 -- to find out which attribute to update.
2721 --
2722
2723 l_att_count := p_app_attributes.first;
2724
2725 LOOP
2726
2727 EXIT WHEN NOT p_app_attributes.exists(l_att_count);
2728
2729 IF p_app_attributes(l_att_count).time_attribute_id = l_time_Attribute_id THEN
2730 --
2731 -- This name value pair belongs to the current attribute record
2732 -- and therefore we should update the record.
2733 -- Fetch the segment associated with this field name, so that we
2734 -- know where to store the modified value.
2735 --
2736 /*AI7 OPEN csr_segment
2737 (p_retrieval_process_id => p_retrieval_process_id
2738 ,p_attribute_category => p_app_attributes(l_att_count).Bld_Blk_Info_Type
2739 ,p_field_name => p_app_attributes(l_att_count).attribute_name
2740 );
2741 FETCH csr_segment INTO l_segment;
2742 IF csr_segment%notfound THEN
2743 --
2744 -- The field specified is not found.
2745 -- We don't know where to put the data
2746 -- Show an error
2747 --
2748 CLOSE csr_segment;
2749 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
2750 */
2751 --AI7 Use cache mapping info to find segment
2752 mapping_comp_info(p_field_name => p_app_attributes(l_att_count).attribute_name,
2753 p_mapping_component => l_mapping_component,
2754 p_mapping => p_mapping);
2755
2756 if(l_mapping_component.field_name is null) then
2757 FND_MESSAGE.set_name('HXC','HXC_NO_MAPPING_COMPONENT');
2758 ELSE
2759 l_segment:=l_mapping_component.segment;
2760 --AI7
2761 --
2762 -- We need to update the appropriate segment in the correct time
2763 -- attribute record.
2764 --
2765 --AI7 CLOSE csr_segment;
2766
2767 IF l_segment = 'ATTRIBUTE1' THEN
2768 g_attributes(p_attribute_number).attribute1 := p_app_attributes(l_att_count).attribute_value;
2769 p_app_attributes(l_att_count).updated := 'Y';
2770 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2771 ELSIF l_segment = 'ATTRIBUTE2' THEN
2772 g_attributes(p_attribute_number).attribute2 := p_app_attributes(l_att_count).attribute_value;
2773 p_app_attributes(l_att_count).updated := 'Y';
2774 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2775 ELSIF l_segment = 'ATTRIBUTE3' THEN
2776 g_attributes(p_attribute_number).attribute3 := p_app_attributes(l_att_count).attribute_value;
2777 p_app_attributes(l_att_count).updated := 'Y';
2778 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2779 ELSIF l_segment = 'ATTRIBUTE4' THEN
2780 g_attributes(p_attribute_number).attribute4 := p_app_attributes(l_att_count).attribute_value;
2781 p_app_attributes(l_att_count).updated := 'Y';
2782 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2783 ELSIF l_segment = 'ATTRIBUTE5' THEN
2784 g_attributes(p_attribute_number).attribute5 := p_app_attributes(l_att_count).attribute_value;
2785 p_app_attributes(l_att_count).updated := 'Y';
2786 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2787 ELSIF l_segment = 'ATTRIBUTE6' THEN
2788 g_attributes(p_attribute_number).attribute6 := p_app_attributes(l_att_count).attribute_value;
2789 p_app_attributes(l_att_count).updated := 'Y';
2790 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2791 ELSIF l_segment = 'ATTRIBUTE7' THEN
2792 g_attributes(p_attribute_number).attribute7 := p_app_attributes(l_att_count).attribute_value;
2793 p_app_attributes(l_att_count).updated := 'Y';
2794 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2795 ELSIF l_segment = 'ATTRIBUTE8' THEN
2796 g_attributes(p_attribute_number).attribute8 := p_app_attributes(l_att_count).attribute_value;
2797 p_app_attributes(l_att_count).updated := 'Y';
2798 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2799 ELSIF l_segment = 'ATTRIBUTE9' THEN
2800 g_attributes(p_attribute_number).attribute9 := p_app_attributes(l_att_count).attribute_value;
2801 p_app_attributes(l_att_count).updated := 'Y';
2802 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2803 ELSIF l_segment = 'ATTRIBUTE10' THEN
2804 g_attributes(p_attribute_number).attribute10 := p_app_attributes(l_att_count).attribute_value;
2805 p_app_attributes(l_att_count).updated := 'Y';
2806 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2807 ELSIF l_segment = 'ATTRIBUTE11' THEN
2808 g_attributes(p_attribute_number).attribute11 := p_app_attributes(l_att_count).attribute_value;
2809 p_app_attributes(l_att_count).updated := 'Y';
2810 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2811 ELSIF l_segment = 'ATTRIBUTE12' THEN
2812 g_attributes(p_attribute_number).attribute12 := p_app_attributes(l_att_count).attribute_value;
2813 p_app_attributes(l_att_count).updated := 'Y';
2814 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2815 ELSIF l_segment = 'ATTRIBUTE13' THEN
2816 g_attributes(p_attribute_number).attribute13 := p_app_attributes(l_att_count).attribute_value;
2817 p_app_attributes(l_att_count).updated := 'Y';
2818 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2819 ELSIF l_segment = 'ATTRIBUTE14' THEN
2820 g_attributes(p_attribute_number).attribute14 := p_app_attributes(l_att_count).attribute_value;
2821 p_app_attributes(l_att_count).updated := 'Y';
2822 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2823 ELSIF l_segment = 'ATTRIBUTE15' THEN
2824 g_attributes(p_attribute_number).attribute15 := p_app_attributes(l_att_count).attribute_value;
2825 p_app_attributes(l_att_count).updated := 'Y';
2826 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2827 ELSIF l_segment = 'ATTRIBUTE16' THEN
2828 g_attributes(p_attribute_number).attribute16 := p_app_attributes(l_att_count).attribute_value;
2829 p_app_attributes(l_att_count).updated := 'Y';
2830 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2831 ELSIF l_segment = 'ATTRIBUTE17' THEN
2832 g_attributes(p_attribute_number).attribute17 := p_app_attributes(l_att_count).attribute_value;
2833 p_app_attributes(l_att_count).updated := 'Y';
2834 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2835 ELSIF l_segment = 'ATTRIBUTE18' THEN
2836 g_attributes(p_attribute_number).attribute18 := p_app_attributes(l_att_count).attribute_value;
2837 p_app_attributes(l_att_count).updated := 'Y';
2838 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2839 ELSIF l_segment = 'ATTRIBUTE19' THEN
2840 g_attributes(p_attribute_number).attribute19 := p_app_attributes(l_att_count).attribute_value;
2841 p_app_attributes(l_att_count).updated := 'Y';
2842 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2843 ELSIF l_segment = 'ATTRIBUTE20' THEN
2844 g_attributes(p_attribute_number).attribute20 := p_app_attributes(l_att_count).attribute_value;
2845 p_app_attributes(l_att_count).updated := 'Y';
2846 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2847 ELSIF l_segment = 'ATTRIBUTE21' THEN
2848 g_attributes(p_attribute_number).attribute21 := p_app_attributes(l_att_count).attribute_value;
2849 p_app_attributes(l_att_count).updated := 'Y';
2850 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2851 ELSIF l_segment = 'ATTRIBUTE22' THEN
2852 g_attributes(p_attribute_number).attribute22 := p_app_attributes(l_att_count).attribute_value;
2853 p_app_attributes(l_att_count).updated := 'Y';
2854 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2855 ELSIF l_segment = 'ATTRIBUTE23' THEN
2856 g_attributes(p_attribute_number).attribute23 := p_app_attributes(l_att_count).attribute_value;
2857 p_app_attributes(l_att_count).updated := 'Y';
2858 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2859 ELSIF l_segment = 'ATTRIBUTE24' THEN
2860 g_attributes(p_attribute_number).attribute24 := p_app_attributes(l_att_count).attribute_value;
2861 p_app_attributes(l_att_count).updated := 'Y';
2862 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2863 ELSIF l_segment = 'ATTRIBUTE25' THEN
2864 g_attributes(p_attribute_number).attribute25 := p_app_attributes(l_att_count).attribute_value;
2865 p_app_attributes(l_att_count).updated := 'Y';
2866 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2867 ELSIF l_segment = 'ATTRIBUTE26' THEN
2868 g_attributes(p_attribute_number).attribute26 := p_app_attributes(l_att_count).attribute_value;
2869 p_app_attributes(l_att_count).updated := 'Y';
2870 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2871 ELSIF l_segment = 'ATTRIBUTE27' THEN
2872 g_attributes(p_attribute_number).attribute27 := p_app_attributes(l_att_count).attribute_value;
2873 p_app_attributes(l_att_count).updated := 'Y';
2874 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2875 ELSIF l_segment = 'ATTRIBUTE28' THEN
2876 g_attributes(p_attribute_number).attribute28 := p_app_attributes(l_att_count).attribute_value;
2877 p_app_attributes(l_att_count).updated := 'Y';
2878 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2879 ELSIF l_segment = 'ATTRIBUTE29' THEN
2880 g_attributes(p_attribute_number).attribute29 := p_app_attributes(l_att_count).attribute_value;
2881 p_app_attributes(l_att_count).updated := 'Y';
2882 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2883 ELSIF l_segment = 'ATTRIBUTE30' THEN
2884 g_attributes(p_attribute_number).attribute30 := p_app_attributes(l_att_count).attribute_value;
2885 p_app_attributes(l_att_count).updated := 'Y';
2886 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2887 -- GPM v115.87
2888 ELSIF l_segment = 'ATTRIBUTE_CATEGORY' THEN
2889 g_attributes(p_attribute_number).attribute_category := p_app_attributes(l_att_count).attribute_value;
2890 p_app_attributes(l_att_count).updated := 'Y';
2891 p_app_attributes(l_att_count).process := 'Y'; --SHIV
2892 END IF;
2893
2894 END IF;
2895
2896
2897 END IF;
2898
2899 l_att_count := p_app_attributes.next(l_att_count);
2900
2901 END LOOP;
2902
2903 IF g_attributes(p_attribute_number).changed IS NULL THEN
2904 begin
2905 select 1 into l_dummy
2906 from hxc_time_attributes
2907 where time_attribute_id = p_app_attributes(p_app_attribute_count).time_attribute_id
2908 and object_version_number = (select max(object_version_number)
2909 from hxc_time_attributes ta2
2910 where ta2.time_attribute_id = time_attribute_id)
2911 and NVL(attribute1,'NULL') = NVL(g_attributes(p_attribute_number).attribute1,'NULL')
2912 and NVL(attribute2,'NULL') = NVL(g_attributes(p_attribute_number).attribute2,'NULL')
2913 and NVL(attribute3,'NULL') = NVL(g_attributes(p_attribute_number).attribute3,'NULL')
2914 and NVL(attribute4,'NULL') = NVL(g_attributes(p_attribute_number).attribute4,'NULL')
2915 and NVL(attribute5,'NULL') = NVL(g_attributes(p_attribute_number).attribute5,'NULL')
2916 and NVL(attribute6,'NULL') = NVL(g_attributes(p_attribute_number).attribute6,'NULL')
2917 and NVL(attribute7,'NULL') = NVL(g_attributes(p_attribute_number).attribute7,'NULL')
2918 and NVL(attribute8,'NULL') = NVL(g_attributes(p_attribute_number).attribute8,'NULL')
2919 and NVL(attribute9,'NULL') = NVL(g_attributes(p_attribute_number).attribute9,'NULL')
2920 and NVL(attribute10,'NULL') = NVL(g_attributes(p_attribute_number).attribute10,'NULL')
2921 and NVL(attribute11,'NULL') = NVL(g_attributes(p_attribute_number).attribute11,'NULL')
2922 and NVL(attribute12,'NULL') = NVL(g_attributes(p_attribute_number).attribute12,'NULL')
2923 and NVL(attribute13,'NULL') = NVL(g_attributes(p_attribute_number).attribute13,'NULL')
2924 and NVL(attribute14,'NULL') = NVL(g_attributes(p_attribute_number).attribute14,'NULL')
2925 and NVL(attribute15,'NULL') = NVL(g_attributes(p_attribute_number).attribute15,'NULL')
2926 and NVL(attribute16,'NULL') = NVL(g_attributes(p_attribute_number).attribute16,'NULL')
2927 and NVL(attribute17,'NULL') = NVL(g_attributes(p_attribute_number).attribute17,'NULL')
2928 and NVL(attribute18,'NULL') = NVL(g_attributes(p_attribute_number).attribute18,'NULL')
2929 and NVL(attribute19,'NULL') = NVL(g_attributes(p_attribute_number).attribute19,'NULL')
2930 and NVL(attribute20,'NULL') = NVL(g_attributes(p_attribute_number).attribute20,'NULL')
2931 and NVL(attribute21,'NULL') = NVL(g_attributes(p_attribute_number).attribute21,'NULL')
2932 and NVL(attribute22,'NULL') = NVL(g_attributes(p_attribute_number).attribute22,'NULL')
2933 and NVL(attribute23,'NULL') = NVL(g_attributes(p_attribute_number).attribute23,'NULL')
2934 and NVL(attribute24,'NULL') = NVL(g_attributes(p_attribute_number).attribute24,'NULL')
2935 and NVL(attribute25,'NULL') = NVL(g_attributes(p_attribute_number).attribute25,'NULL')
2936 and NVL(attribute26,'NULL') = NVL(g_attributes(p_attribute_number).attribute26,'NULL')
2937 and NVL(attribute27,'NULL') = NVL(g_attributes(p_attribute_number).attribute27,'NULL')
2938 and NVL(attribute28,'NULL') = NVL(g_attributes(p_attribute_number).attribute28,'NULL')
2939 and NVL(attribute29,'NULL') = NVL(g_attributes(p_attribute_number).attribute29,'NULL')
2940 and NVL(attribute30,'NULL') = NVL(g_attributes(p_attribute_number).attribute30,'NULL')
2941 and attribute_category = g_attributes(p_attribute_number).attribute_category;
2942 g_attributes(p_attribute_number).changed := 'N';
2943 if g_debug then
2944 l_proc := 'UpdateAttributeRecord';
2945 hr_utility.trace('Same Attribute found');
2946 end if;
2947 exception
2948 when no_data_found then
2949 g_attributes(p_attribute_number).changed := 'Y';
2950 if g_debug then
2951 hr_utility.trace('Same Attribute NOT found');
2952 end if;
2953 end;
2954 END IF;
2955 END update_attribute_record;
2956
2957 PROCEDURE create_attribute_record
2958 (p_app_attribute_count IN NUMBER
2959 ,p_mapping IN t_mapping
2960 ,p_app_attributes IN OUT NOCOPY app_attributes_info
2961 ) IS
2962
2963 l_next_attribute_id NUMBER;
2964 l_bld_blk_info_type_id NUMBER;
2965 l_attribute_category HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE;
2966 l_att_count NUMBER;
2967
2968 cursor csr_attribute
2969 (p_time_attribute_id in HXC_TIME_ATTRIBUTES.TIME_ATTRIBUTE_ID%TYPE
2970 ) is
2971 select 1
2972 from hxc_time_attributes ta
2973 where ta.time_attribute_id = p_time_attribute_id;
2974
2975 l_attribute_row csr_attribute%ROWTYPE;
2976 l_new varchar2(30);
2977 l_changed varchar2(30);
2978
2979 BEGIN
2980
2981
2982
2983 --
2984 -- Fetch the bld blk information type id
2985 --
2986 l_bld_blk_info_type_id := cache_bld_blk_info_types(p_app_attributes(p_app_attribute_count).bld_blk_info_type);
2987 --
2988 -- Trick here is to create a new attribute by depositing a blank one,
2989 -- then calling the update attribute record procedure to create it
2990 -- properly.
2991 --
2992
2993 -- GPM v115.58
2994
2995 if (p_app_attributes(p_app_attribute_count).time_attribute_id is NOT NULL) then
2996
2997 l_next_attribute_id := p_app_attributes(p_app_attribute_count).time_attribute_id;
2998
2999 else
3000
3001
3002 IF ( g_attributes.LAST IS NULL )
3003 THEN
3004 l_next_attribute_id := 1;
3005 ELSE
3006 l_next_attribute_id := g_attributes(g_attributes.LAST).time_attribute_id+1;
3007 END IF;
3008
3009 end if;
3010
3011 IF ( p_app_attributes(p_app_attribute_count).bld_blk_info_type like 'Dummy%' )
3012 THEN
3013 l_attribute_category := NULL;
3014 ELSE
3015 l_attribute_category := p_app_attributes(p_app_attribute_count).bld_blk_info_type;
3016 END IF;
3017
3018 if g_debug then
3019 hr_utility.trace('Before API Check');
3020 end if;
3021 -- check if this code was called from the Timestore Deposit API
3022 if p_app_attributes(p_app_attribute_count).changed is null then
3023 if g_debug then
3024 hr_utility.trace('API Call');
3025 end if;
3026 -- Determine if this is a new attribute or an existing one
3027 open csr_attribute
3028 ( p_app_attributes(p_app_attribute_count).time_attribute_id
3029 );
3030
3031 fetch csr_attribute into l_attribute_row;
3032 if(csr_attribute%FOUND) then
3033 if g_debug then
3034 hr_utility.trace('attribute found');
3035 end if;
3036 l_new := 'N';
3037 l_changed := NULL;
3038 else
3039 if g_debug then
3040 hr_utility.trace('attribute NOT found');
3041 end if;
3042 l_new := 'Y';
3043 l_changed := 'Y';
3044 end if;
3045 close csr_attribute;
3046 else
3047 l_new := 'Y';
3048 l_changed := 'Y';
3049 end if;
3050
3051 call_attribute_deposit(
3052 p_TIME_ATTRIBUTE_ID => l_next_attribute_id
3053 ,p_BUILDING_BLOCK_ID => p_app_attributes(p_app_attribute_count).building_block_id
3054 ,p_BLD_BLK_INFO_TYPE => p_app_attributes(p_app_attribute_count).bld_blk_info_type
3055 ,p_ATTRIBUTE_CATEGORY => l_attribute_category -- GPM v115.100
3056 ,p_ATTRIBUTE1 => NULL
3057 ,p_ATTRIBUTE2 => NULL
3058 ,p_ATTRIBUTE3 => NULL
3059 ,p_ATTRIBUTE4 => NULL
3060 ,p_ATTRIBUTE5 => NULL
3061 ,p_ATTRIBUTE6 => NULL
3062 ,p_ATTRIBUTE7 => NULL
3063 ,p_ATTRIBUTE8 => NULL
3064 ,p_ATTRIBUTE9 => NULL
3065 ,p_ATTRIBUTE10 => NULL
3066 ,p_ATTRIBUTE11 => NULL
3067 ,p_ATTRIBUTE12 => NULL
3068 ,p_ATTRIBUTE13 => NULL
3069 ,p_ATTRIBUTE14 => NULL
3070 ,p_ATTRIBUTE15 => NULL
3071 ,p_ATTRIBUTE16 => NULL
3072 ,p_ATTRIBUTE17 => NULL
3073 ,p_ATTRIBUTE18 => NULL
3074 ,p_ATTRIBUTE19 => NULL
3075 ,p_ATTRIBUTE20 => NULL
3076 ,p_ATTRIBUTE21 => NULL
3077 ,p_ATTRIBUTE22 => NULL
3078 ,p_ATTRIBUTE23 => NULL
3079 ,p_ATTRIBUTE24 => NULL
3080 ,p_ATTRIBUTE25 => NULL
3081 ,p_ATTRIBUTE26 => NULL
3082 ,p_ATTRIBUTE27 => NULL
3083 ,p_ATTRIBUTE28 => NULL
3084 ,p_ATTRIBUTE29 => NULL
3085 ,p_ATTRIBUTE30 => NULL
3086 ,p_BLD_BLK_INFO_TYPE_ID => l_bld_blk_info_type_id
3087 ,p_OBJECT_VERSION_NUMBER => 1
3088 ,p_NEW => l_new -- 'Y'
3089 ,P_CHANGED => l_changed -- 'Y'
3090 );
3091
3092 --
3093 -- Next call the update procedure
3094 -- to add the attribute values.
3095 --
3096
3097 update_attribute_record
3098 (p_attribute_number => g_attributes.last
3099 ,p_app_attribute_count => p_app_attribute_count
3100 ,p_mapping => p_mapping
3101 ,p_app_attributes => p_app_attributes
3102 );
3103
3104 END create_attribute_record;
3105 /*
3106 procedure dump_attributes is
3107
3108 l_proc varchar2(30) := 'dump_attributes';
3109
3110 l_attribute number;
3111
3112 begin
3113
3114 l_attribute := g_attributes.first;
3115
3116 loop
3117
3118 exit when not g_attributes.exists(l_attribute);
3119
3120 debug(l_proc,5,'Time Attribute Id:'||g_attributes(l_attribute).time_attribute_id);
3121 debug(l_proc,5,'Time BB Id:'||g_attributes(l_attribute).building_block_id);
3122 debug(l_proc,5,'Bld Blk Info Type:'||g_attributes(l_attribute).bld_blk_info_type);
3123 debug(l_proc,5,'Attribute Category:'||g_attributes(l_attribute).attribute_category);
3124 debug(l_proc,5,'Attribute 1:'||g_attributes(l_attribute).attribute1);
3125 debug(l_proc,5,'Attribute 2:'||g_attributes(l_attribute).attribute2);
3126 debug(l_proc,5,'Attribute 3:'||g_attributes(l_attribute).attribute3);
3127 debug(l_proc,5,'Attribute 4:'||g_attributes(l_attribute).attribute4);
3128 debug(l_proc,5,'Attribute 5:'||g_attributes(l_attribute).attribute5);
3129
3130 l_attribute := g_attributes.next(l_attribute);
3131
3132 end loop;
3133
3134 end dump_attributes;
3135 */
3136 /*
3137 procedure dump_app_attributes is
3138
3139 l_proc varchar2(30) := 'dump_app_attributes';
3140
3141 l_attribute number;
3142
3143 begin
3144
3145 l_attribute := g_app_attributes.first;
3146
3147 loop
3148
3149 exit when not g_app_attributes.exists(l_attribute);
3150
3151 debug(l_proc,10,'Time_Attribute_Id:'||g_app_attributes(l_attribute).time_attribute_id);
3152 debug(l_proc,15,'Building_Block_Id:'||g_app_attributes(l_attribute).building_block_id);
3153 debug(l_proc,20,'Attribute_Name :'||g_app_attributes(l_attribute).attribute_name);
3154 debug(l_proc,25,'Attribute_Value :'||g_app_attributes(l_attribute).attribute_value);
3155 debug(l_proc,30,'Bld_Blk_Info_Type:'||g_app_attributes(l_attribute).bld_blk_info_type);
3156 debug(l_proc,35,'Category :'||g_app_attributes(l_attribute).category);
3157 debug(l_proc,40,'Updated :'||g_app_attributes(l_attribute).updated);
3158 debug(l_proc,45,'Changed :'||g_app_attributes(l_attribute).changed);
3159 debug(l_proc,50,'Process :'||g_app_attributes(l_attribute).process);
3160
3161 debug(l_proc,55,'');
3162
3163 l_attribute := g_app_attributes.next(l_attribute);
3164
3165
3166 end loop;
3167
3168 end dump_app_attributes;
3169 */
3170
3171 PROCEDURE update_deposit_globals(
3172 p_retrieval_process_id IN NUMBER default null,
3173 p_deposit_process_id IN NUMBER default null
3174 ) IS
3175
3176 l_blocks timecard_info;
3177 l_attributes app_attributes_info;
3178 l_count BINARY_INTEGER;
3179 l_blk_count BINARY_INTEGER;
3180 l_att_count BINARY_INTEGER;
3181 l_new_attribute BOOLEAN := TRUE;
3182 l_mapping t_mapping; --AI7
3183 l_proc VARCHAR2(30) := 'UPDATE_DEPOSIT_GLOBALS';
3184
3185
3186 BEGIN
3187
3188 --
3189 -- Update the attributes
3190 --
3191 --AI7 will use mapping information, so make sure its available
3192
3193 cache_mappings(p_retrieval_process_id => p_retrieval_process_id,
3194 p_deposit_process_id => p_deposit_process_id);
3195
3196 if(p_retrieval_process_id is not null) then
3197 -- dbms_output.put_line('Using Retrieval Process Mapping');
3198 l_mapping:=g_retrieval_mapping;
3199 else
3200 -- dbms_output.put_line('Using Deposit Process Mapping');
3201 l_mapping:=g_deposit_mapping;
3202 end if;
3203 --AI7
3204
3205
3206 l_count := g_app_attributes.first;
3207
3208 LOOP
3209
3210 EXIT WHEN NOT g_app_attributes.exists(l_count);
3211
3212 --
3213 -- Loop over the global attributes
3214 --
3215
3216 l_new_attribute := TRUE;
3217 l_att_count := g_attributes.first;
3218
3219 LOOP
3220
3221 EXIT WHEN NOT g_attributes.exists(l_att_count);
3222
3223 IF (
3224 (g_app_attributes(l_count).time_attribute_id =
3225 g_attributes(l_att_count).time_attribute_id)
3226 AND
3227 (NVL(g_app_attributes(l_count).updated,'N') <> 'Y')
3228 ) THEN
3229 --
3230 -- This can't be a new attribute
3231 --
3232 l_new_attribute := FALSE;
3233 --
3234 -- Now we need to check whether there are any differences in the attribute records
3235 --
3236 -- Use the mapping components to work out whether there are any changes
3237 --
3238
3239 update_attribute_record
3240 (p_attribute_number => l_att_count
3241 ,p_app_attribute_count => l_count
3242 ,p_mapping => l_mapping
3243 ,p_app_attributes => g_app_attributes
3244 );
3245
3246 END IF;
3247
3248 l_att_count := g_attributes.next(l_att_count);
3249
3250 END LOOP;
3251
3252 IF (
3253 (l_new_attribute)
3254 AND
3255 (g_app_attributes(l_count).updated <> 'Y')
3256 ) THEN
3257 --
3258 -- Create the new attribute record
3259 --
3260 create_attribute_record
3261 (p_app_attribute_count => l_count
3262 ,p_mapping => l_mapping
3263 ,p_app_attributes => g_app_attributes
3264 );
3265
3266 END IF;
3267
3268 l_count := g_app_attributes.next(l_count);
3269
3270 END LOOP;
3271
3272 END update_deposit_globals;
3273
3274 PROCEDURE show_errors(p_messages IN OUT NOCOPY message_table) IS
3275
3276 l_message_count NUMBER;
3277 l_error_message VARCHAR2(4000);
3278 l_fnd_separator VARCHAR2(5) := FND_GLOBAL.LOCAL_CHR(0);
3279 l_token_table hxc_deposit_wrapper_utilities.t_simple_table;
3280
3281 BEGIN
3282 --
3283 -- loop over the error msgs. return immediately if the message_table is blank
3284 --
3285 if(p_messages is null) then
3286 return;
3287 end if;
3288
3289 l_message_count:=p_messages.first;
3290
3291 LOOP
3292
3293 EXIT WHEN NOT p_messages.exists(l_message_count);
3294 EXIT WHEN l_message_count > g_max_messages_displayed;
3295
3296 if (((p_messages(l_message_count).message_level) is NULL)
3297 OR
3298 (instr(upper(p_messages(l_message_count).message_level),'ERR') > 0)) then
3299
3300 if(p_messages(l_message_count).on_oa_msg_stack = FALSE ) then
3301 --AI5 message hasnt been processed yet
3302 --
3303 -- Set on 'stack'
3304 --
3305 FND_MESSAGE.SET_NAME
3306 (p_messages(l_message_count).application_short_name
3307 ,p_messages(l_message_count).message_name
3308 );
3309
3310 IF p_messages(l_message_count).message_tokens IS NOT NULL THEN
3311 --
3312 -- parse string into a more accessible form
3313 --
3314 hxc_deposit_wrapper_utilities.string_to_table('&',
3315 '&'||p_messages(l_message_count).message_tokens,
3316 l_token_table);
3317
3318 -- table should be full of TOKEN, VALUE pairs. The number of TOKEN, VALUE pairs is l_token_table/2
3319
3320 FOR l_token in 0..(l_token_table.count/2)-1 LOOP
3321
3322 FND_MESSAGE.SET_TOKEN
3323 (TOKEN => l_token_table(2*l_token)
3324 ,VALUE => l_token_table(2*l_token+1)
3325 );
3326
3327 END LOOP;
3328 END IF; -- end tokens
3329
3330 --
3331 -- Add this message to the message list
3332 --
3333 fnd_msg_pub.add;
3334 --AI5 set flag to say this has been stacked
3335 p_messages(l_message_count).on_oa_msg_stack := TRUE;
3336
3337 END IF; -- is this msg already stacked?
3338 END IF; -- is this really an error message?
3339
3340 l_message_count:=p_messages.next(l_message_count);
3341
3342 END LOOP; -- loop over msg table
3343
3344 --AI5 no longer delete contents of msg table
3345
3346 END show_errors;
3347
3348 FUNCTION info_type_id_exists(p_id in NUMBER) return boolean
3349 is
3350
3351 l_dummy VARCHAR2(10);
3352
3353 BEGIN
3354
3355 select 'Y' into l_dummy
3356 from hxc_bld_blk_info_types
3357 where bld_blk_info_type_id = p_id;
3358
3359 return true;
3360
3361 EXCEPTION
3362 when others then
3363 return false;
3364
3365 END info_type_id_exists;
3366
3367 procedure deposit_attribute_info(
3368 p_TIME_ATTRIBUTE_ID in NUMBER
3369 ,p_BUILDING_BLOCK_ID in NUMBER
3370 ,p_BLD_BLK_INFO_TYPE in VARCHAR2
3371 ,p_ATTRIBUTE_CATEGORY in VARCHAR2
3372 ,p_ATTRIBUTE1 in VARCHAR2
3373 ,p_ATTRIBUTE2 in VARCHAR2
3374 ,p_ATTRIBUTE3 in VARCHAR2
3375 ,p_ATTRIBUTE4 in VARCHAR2
3376 ,p_ATTRIBUTE5 in VARCHAR2
3377 ,p_ATTRIBUTE6 in VARCHAR2
3378 ,p_ATTRIBUTE7 in VARCHAR2
3379 ,p_ATTRIBUTE8 in VARCHAR2
3380 ,p_ATTRIBUTE9 in VARCHAR2
3381 ,p_ATTRIBUTE10 in VARCHAR2
3382 ,p_ATTRIBUTE11 in VARCHAR2
3383 ,p_ATTRIBUTE12 in VARCHAR2
3384 ,p_ATTRIBUTE13 in VARCHAR2
3385 ,p_ATTRIBUTE14 in VARCHAR2
3386 ,p_ATTRIBUTE15 in VARCHAR2
3387 ,p_ATTRIBUTE16 in VARCHAR2
3388 ,p_ATTRIBUTE17 in VARCHAR2
3389 ,p_ATTRIBUTE18 in VARCHAR2
3390 ,p_ATTRIBUTE19 in VARCHAR2
3391 ,p_ATTRIBUTE20 in VARCHAR2
3392 ,p_ATTRIBUTE21 in VARCHAR2
3393 ,p_ATTRIBUTE22 in VARCHAR2
3394 ,p_ATTRIBUTE23 in VARCHAR2
3395 ,p_ATTRIBUTE24 in VARCHAR2
3396 ,p_ATTRIBUTE25 in VARCHAR2
3397 ,p_ATTRIBUTE26 in VARCHAR2
3398 ,p_ATTRIBUTE27 in VARCHAR2
3399 ,p_ATTRIBUTE28 in VARCHAR2
3400 ,p_ATTRIBUTE29 in VARCHAR2
3401 ,p_ATTRIBUTE30 in VARCHAR2
3402 ,p_BLD_BLK_INFO_TYPE_ID in NUMBER
3403 ,p_OBJECT_VERSION_NUMBER in NUMBER
3404 ,p_NEW in VARCHAR2
3405 ,p_changed in VARCHAR2
3406 ) IS
3407
3408 cursor c_bld_blk_info_id
3409 (p_info_type in varchar2) is
3410 select bld_blk_info_type_id
3411 from hxc_bld_blk_info_types
3412 where bld_blk_info_type = p_info_type;
3413
3414 l_last_index BINARY_INTEGER;
3415 l_next_index BINARY_INTEGER;
3416 l_proc VARCHAR2(30) := 'DEPOSIT ATTRIBUTE INFO';
3417 l_attribute1 HXC_TIME_ATTRIBUTES.ATTRIBUTE1%TYPE := P_ATTRIBUTE1;
3418 l_attribute_category HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE%TYPE;
3419 l_bld_blk_info_type_id HXC_BLD_BLK_INFO_TYPES.BLD_BLK_INFO_TYPE_ID%TYPE;
3420
3421 BEGIN
3422 --
3423 -- Find the next index
3424 --
3425 l_last_index := g_attributes.last;
3426
3427 if (l_last_index is null) then
3428
3429 l_next_index := 1;
3430
3431 else
3432
3433 l_next_index := l_last_index +1;
3434
3435 end if;
3436
3437 if (info_type_id_exists(p_bld_blk_info_type_id)) then
3438
3439 l_bld_blk_info_type_id := p_bld_blk_info_Type_id;
3440
3441 else
3442 open c_bld_blk_info_id(p_BLD_BLK_INFO_TYPE);
3443 fetch c_bld_blk_info_id into l_bld_blk_info_type_id;
3444 close c_bld_blk_info_id;
3445
3446 end if;
3447
3448 --
3449 -- Check the element context!
3450 --
3451
3452 if (p_bld_blk_info_type = 'Dummy Element Context') THEN
3453
3454 if (INSTR(p_attribute_category,'ELEMENT') =0) then
3455 l_attribute_category := 'ELEMENT - '||p_attribute_category;
3456 else
3457 l_attribute_category := p_attribute_category;
3458 end if;
3459
3460 else
3461
3462 l_attribute_category := p_attribute_category;
3463
3464 end if;
3465
3466 --
3467 -- Nasty Hack for Oracle Internal
3468 --
3469
3470 IF (p_attribute_category = 'ORACLE_INTERNAL') THEN
3471 IF (INSTR(p_attribute1,'ELEMENT') = 0) THEN
3472 l_attribute1 := 'ELEMENT - '||p_attribute1;
3473 END IF;
3474 ELSE
3475 l_attribute1 := p_attribute1;
3476 END IF;
3477
3478 g_attributes(l_next_index).TIME_ATTRIBUTE_ID := p_time_attribute_id;
3479 g_attributes(l_next_index).BUILDING_BLOCK_ID := p_BUILDING_BLOCK_ID ;
3480 g_attributes(l_next_index).BLD_BLK_INFO_TYPE := p_BLD_BLK_INFO_TYPE ;
3481 g_attributes(l_next_index).ATTRIBUTE_CATEGORY := l_ATTRIBUTE_CATEGORY ;
3482 g_attributes(l_next_index).ATTRIBUTE1 := l_ATTRIBUTE1 ;
3483 g_attributes(l_next_index).ATTRIBUTE2 := p_ATTRIBUTE2 ;
3484 g_attributes(l_next_index).ATTRIBUTE3 := p_ATTRIBUTE3 ;
3485 g_attributes(l_next_index).ATTRIBUTE4 := p_ATTRIBUTE4 ;
3486 g_attributes(l_next_index).ATTRIBUTE5 := p_ATTRIBUTE5 ;
3487 g_attributes(l_next_index).ATTRIBUTE6 := p_ATTRIBUTE6 ;
3488 g_attributes(l_next_index).ATTRIBUTE7 := p_ATTRIBUTE7 ;
3489 g_attributes(l_next_index).ATTRIBUTE8 := p_ATTRIBUTE8 ;
3490 g_attributes(l_next_index).ATTRIBUTE9 := p_ATTRIBUTE9 ;
3491 g_attributes(l_next_index).ATTRIBUTE10 := p_ATTRIBUTE10 ;
3492 g_attributes(l_next_index).ATTRIBUTE11 := p_ATTRIBUTE11 ;
3493 g_attributes(l_next_index).ATTRIBUTE12 := p_ATTRIBUTE12 ;
3494 g_attributes(l_next_index).ATTRIBUTE13 := p_ATTRIBUTE13 ;
3495 g_attributes(l_next_index).ATTRIBUTE14 := p_ATTRIBUTE14 ;
3496 g_attributes(l_next_index).ATTRIBUTE15 := p_ATTRIBUTE15 ;
3497 g_attributes(l_next_index).ATTRIBUTE16 := p_ATTRIBUTE16 ;
3498 g_attributes(l_next_index).ATTRIBUTE17 := p_ATTRIBUTE17 ;
3499 g_attributes(l_next_index).ATTRIBUTE18 := p_ATTRIBUTE18 ;
3500 g_attributes(l_next_index).ATTRIBUTE19 := p_ATTRIBUTE19 ;
3501 g_attributes(l_next_index).ATTRIBUTE20 := p_ATTRIBUTE20 ;
3502 g_attributes(l_next_index).ATTRIBUTE21 := p_ATTRIBUTE21 ;
3503 g_attributes(l_next_index).ATTRIBUTE22 := p_ATTRIBUTE22 ;
3504 g_attributes(l_next_index).ATTRIBUTE23 := p_ATTRIBUTE23 ;
3505 g_attributes(l_next_index).ATTRIBUTE24 := p_ATTRIBUTE24 ;
3506 g_attributes(l_next_index).ATTRIBUTE25 := p_ATTRIBUTE25 ;
3507 g_attributes(l_next_index).ATTRIBUTE26 := p_ATTRIBUTE26 ;
3508 g_attributes(l_next_index).ATTRIBUTE27 := p_ATTRIBUTE27 ;
3509 g_attributes(l_next_index).ATTRIBUTE28 := p_ATTRIBUTE28 ;
3510 g_attributes(l_next_index).ATTRIBUTE29 := p_ATTRIBUTE29 ;
3511 g_attributes(l_next_index).ATTRIBUTE30 := p_ATTRIBUTE30 ;
3512 g_attributes(l_next_index).BLD_BLK_INFO_TYPE_ID := l_bld_blk_info_type_id ;
3513 g_attributes(l_next_index).OBJECT_VERSION_NUMBER := p_OBJECT_VERSION_NUMBER ;
3514 g_attributes(l_next_index).NEW := p_NEW ;
3515 g_attributes(l_next_index).changed := p_changed ;
3516
3517 exception
3518 when others then
3519 FND_MESSAGE.SET_NAME('HXC','HXC_deposit_ATTRIBUTE_info');
3520 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM||' : '||length(l_attribute_category));
3521 FND_MESSAGE.RAISE_ERROR;
3522
3523 END deposit_attribute_info;
3524
3525 procedure call_attribute_deposit(
3526 p_TIME_ATTRIBUTE_ID in VARCHAR2
3527 ,p_BUILDING_BLOCK_ID in VARCHAR2
3528 ,p_BLD_BLK_INFO_TYPE in VARCHAR2
3529 ,p_ATTRIBUTE_CATEGORY in VARCHAR2
3530 ,p_ATTRIBUTE1 in VARCHAR2
3531 ,p_ATTRIBUTE2 in VARCHAR2
3532 ,p_ATTRIBUTE3 in VARCHAR2
3533 ,p_ATTRIBUTE4 in VARCHAR2
3534 ,p_ATTRIBUTE5 in VARCHAR2
3535 ,p_ATTRIBUTE6 in VARCHAR2
3536 ,p_ATTRIBUTE7 in VARCHAR2
3537 ,p_ATTRIBUTE8 in VARCHAR2
3538 ,p_ATTRIBUTE9 in VARCHAR2
3539 ,p_ATTRIBUTE10 in VARCHAR2
3540 ,p_ATTRIBUTE11 in VARCHAR2
3541 ,p_ATTRIBUTE12 in VARCHAR2
3542 ,p_ATTRIBUTE13 in VARCHAR2
3543 ,p_ATTRIBUTE14 in VARCHAR2
3544 ,p_ATTRIBUTE15 in VARCHAR2
3545 ,p_ATTRIBUTE16 in VARCHAR2
3546 ,p_ATTRIBUTE17 in VARCHAR2
3547 ,p_ATTRIBUTE18 in VARCHAR2
3548 ,p_ATTRIBUTE19 in VARCHAR2
3549 ,p_ATTRIBUTE20 in VARCHAR2
3550 ,p_ATTRIBUTE21 in VARCHAR2
3551 ,p_ATTRIBUTE22 in VARCHAR2
3552 ,p_ATTRIBUTE23 in VARCHAR2
3553 ,p_ATTRIBUTE24 in VARCHAR2
3554 ,p_ATTRIBUTE25 in VARCHAR2
3555 ,p_ATTRIBUTE26 in VARCHAR2
3556 ,p_ATTRIBUTE27 in VARCHAR2
3557 ,p_ATTRIBUTE28 in VARCHAR2
3558 ,p_ATTRIBUTE29 in VARCHAR2
3559 ,p_ATTRIBUTE30 in VARCHAR2
3560 ,p_BLD_BLK_INFO_TYPE_ID in VARCHAR2
3561 ,p_OBJECT_VERSION_NUMBER in VARCHAR2
3562 ,p_NEW in VARCHAR2
3563 ,p_changed in varchar2
3564 ) is
3565
3566 l_time_attribute_id NUMBER := to_number(p_TIME_ATTRIBUTE_ID);
3567 l_ovn NUMBER := to_number(p_OBJECT_VERSION_NUMBER);
3568 l_building_block_id NUMBER := to_number(p_BUILDING_BLOCK_ID);
3569 l_bld_blk_info_type_id NUMBER := TO_NUMBER(P_BLD_BLK_INFO_TYPE_ID);
3570
3571 begin
3572
3573
3574 deposit_attribute_info(
3575 p_TIME_ATTRIBUTE_ID => l_time_attribute_id
3576 ,p_BUILDING_BLOCK_ID => l_building_block_id
3577 ,p_BLD_BLK_INFO_TYPE => p_BLD_BLK_INFO_TYPE
3578 ,p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY
3579 ,p_ATTRIBUTE1 => p_ATTRIBUTE1
3580 ,p_ATTRIBUTE2 => p_ATTRIBUTE2
3581 ,p_ATTRIBUTE3 => p_ATTRIBUTE3
3582 ,p_ATTRIBUTE4 => p_ATTRIBUTE4
3583 ,p_ATTRIBUTE5 => p_ATTRIBUTE5
3584 ,p_ATTRIBUTE6 => p_ATTRIBUTE6
3585 ,p_ATTRIBUTE7 => p_ATTRIBUTE7
3586 ,p_ATTRIBUTE8 => p_ATTRIBUTE8
3587 ,p_ATTRIBUTE9 => p_ATTRIBUTE9
3588 ,p_ATTRIBUTE10 => p_ATTRIBUTE10
3589 ,p_ATTRIBUTE11 => p_ATTRIBUTE11
3590 ,p_ATTRIBUTE12 => p_ATTRIBUTE12
3591 ,p_ATTRIBUTE13 => p_ATTRIBUTE13
3592 ,p_ATTRIBUTE14 => p_ATTRIBUTE14
3593 ,p_ATTRIBUTE15 => p_ATTRIBUTE15
3594 ,p_ATTRIBUTE16 => p_ATTRIBUTE16
3595 ,p_ATTRIBUTE17 => p_ATTRIBUTE17
3596 ,p_ATTRIBUTE18 => p_ATTRIBUTE18
3597 ,p_ATTRIBUTE19 => p_ATTRIBUTE19
3598 ,p_ATTRIBUTE20 => p_ATTRIBUTE20
3599 ,p_ATTRIBUTE21 => p_ATTRIBUTE21
3600 ,p_ATTRIBUTE22 => p_ATTRIBUTE22
3601 ,p_ATTRIBUTE23 => p_ATTRIBUTE23
3602 ,p_ATTRIBUTE24 => p_ATTRIBUTE24
3603 ,p_ATTRIBUTE25 => p_ATTRIBUTE25
3604 ,p_ATTRIBUTE26 => p_ATTRIBUTE26
3605 ,p_ATTRIBUTE27 => p_ATTRIBUTE27
3606 ,p_ATTRIBUTE28 => p_ATTRIBUTE28
3607 ,p_ATTRIBUTE29 => p_ATTRIBUTE29
3608 ,p_ATTRIBUTE30 => p_ATTRIBUTE30
3609 ,p_BLD_BLK_INFO_TYPE_ID => p_BLD_BLK_INFO_TYPE_ID
3610 ,p_OBJECT_VERSION_NUMBER => l_ovn
3611 ,p_NEW => p_NEW
3612 ,p_changed => p_changed);
3613
3614 end call_attribute_deposit;
3615
3616 procedure call_block_deposit(
3617 P_TIME_BUILDING_BLOCK_ID in VARCHAR2
3618 ,P_TYPE in VARCHAR2
3619 ,P_MEASURE in VARCHAR2
3620 ,P_UNIT_OF_MEASURE in VARCHAR2
3621 ,P_START_TIME in VARCHAR2
3622 ,P_STOP_TIME in VARCHAR2
3623 ,P_PARENT_BUILDING_BLOCK_ID in VARCHAR2
3624 ,P_PARENT_IS_NEW IN VARCHAR2
3625 ,P_SCOPE in VARCHAR2
3626 ,P_OBJECT_VERSION_NUMBER in VARCHAR2
3627 ,P_APPROVAL_STATUS in VARCHAR2
3628 ,P_RESOURCE_ID in VARCHAR2
3629 ,P_RESOURCE_TYPE in VARCHAR2
3630 ,P_APPROVAL_STYLE_ID in VARCHAR2
3631 ,P_DATE_FROM in VARCHAR2
3632 ,P_DATE_TO in VARCHAR2
3633 ,P_COMMENT_TEXT in VARCHAR2
3634 ,P_PARENT_BUILDING_BLOCK_OVN in VARCHAR2
3635 ,P_NEW in VARCHAR2
3636 ,P_CHANGED in VARCHAR2
3637 ) is
3638
3639
3640 l_building_block_id NUMBER := to_number(P_TIME_BUILDING_BLOCK_ID);
3641 l_measure NUMBER := to_number(P_MEASURE);
3642 l_start_time DATE := fnd_date.canonical_to_date(P_START_TIME);
3643 l_stop_time DATE := fnd_date.canonical_to_date(P_STOP_TIME);
3644 l_parent_building_block_id NUMBER := to_number(P_PARENT_BUILDING_BLOCK_ID);
3645 l_object_version_number NUMBER := to_number(P_OBJECT_VERSION_NUMBER);
3646 l_resource_id NUMBER := to_number(P_RESOURCE_ID);
3647 l_approval_style_id NUMBER := to_number(P_APPROVAL_STYLE_ID);
3648 l_date_from DATE := fnd_date.canonical_to_date(P_DATE_FROM);
3649 l_date_to DATE := fnd_date.canonical_to_date(P_DATE_TO);
3650 l_parent_building_block_ovn NUMBER := to_number(P_PARENT_BUILDING_BLOCK_OVN);
3651 l_proc VARCHAR2(30):= 'CALL_BLOCK_DEPOSIT';
3652
3653
3654 BEGIN
3655
3656 --
3657 -- Check the Java null values, and see if we need to
3658 -- reset any of the passed values.
3659 --
3660
3661 IF (l_parent_building_block_id = -1) then
3662 l_parent_building_block_id := NULL;
3663 end if;
3664
3665 -- Bug 2336662
3666 -- This if condition is commented out for the Enhancement for the Performance
3667 -- of Template view
3668 -- It is to ensure that we actually save a public template with resource id -1
3669
3670 --if (l_resource_id = -1) then
3671 -- l_resource_id := NULL;
3672 --end if;
3673
3674 -- End of change
3675
3676 if (l_approval_style_id = -1) then
3677 l_approval_style_id := NULL;
3678 end if;
3679
3680 if (l_parent_building_block_ovn = -1) then
3681 l_parent_building_block_ovn := NULL;
3682 end if;
3683
3684 --
3685 -- Now that the type conversion has been performed,
3686 -- call the deposit block information procedure
3687 -- to store this set of block information.
3688 --
3689
3690 deposit_block_info(
3691 P_TIME_BUILDING_BLOCK_ID => l_building_block_id
3692 ,P_TYPE => P_TYPE
3693 ,P_MEASURE => l_measure
3694 ,P_UNIT_OF_MEASURE => P_UNIT_OF_MEASURE
3695 ,P_START_TIME => l_start_time
3696 ,P_STOP_TIME => l_stop_time
3697 ,P_PARENT_BUILDING_BLOCK_ID => l_parent_building_block_id
3698 ,P_PARENT_IS_NEW => p_parent_is_new
3699 ,P_SCOPE => P_SCOPE
3700 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3701 ,P_APPROVAL_STATUS => P_APPROVAL_STATUS
3702 ,P_RESOURCE_ID => l_resource_id
3703 ,P_RESOURCE_TYPE => P_RESOURCE_TYPE
3704 ,P_APPROVAL_STYLE_ID => l_approval_style_id
3705 ,P_DATE_FROM => l_date_from
3706 ,P_DATE_TO => l_date_to
3707 ,P_COMMENT_TEXT => P_COMMENT_TEXT
3708 ,P_PARENT_BUILDING_BLOCK_OVN => l_parent_building_block_ovn
3709 ,P_NEW => P_NEW
3710 ,P_CHANGED => P_CHANGED
3711 );
3712
3713
3714 end call_block_deposit;
3715
3716 procedure deposit_block_info(
3717 P_TIME_BUILDING_BLOCK_ID in NUMBER
3718 ,P_TYPE in VARCHAR2
3719 ,P_MEASURE in NUMBER
3720 ,P_UNIT_OF_MEASURE in VARCHAR2
3721 ,P_START_TIME in DATE
3722 ,P_STOP_TIME in DATE
3723 ,P_PARENT_BUILDING_BLOCK_ID in NUMBER
3724 ,P_PARENT_IS_NEW IN VARCHAR2
3725 ,P_SCOPE in VARCHAR2
3726 ,P_OBJECT_VERSION_NUMBER in NUMBER
3727 ,P_APPROVAL_STATUS in VARCHAR2
3728 ,P_RESOURCE_ID in NUMBER
3729 ,P_RESOURCE_TYPE in VARCHAR2
3730 ,P_APPROVAL_STYLE_ID in NUMBER
3731 ,P_DATE_FROM in DATE
3732 ,P_DATE_TO in DATE
3733 ,P_COMMENT_TEXT in VARCHAR2
3734 ,P_PARENT_BUILDING_BLOCK_OVN in NUMBER
3735 ,P_NEW in VARCHAR2
3736 ,P_CHANGED in VARCHAR2
3737 ) is
3738
3739
3740 l_last_index BINARY_INTEGER;
3741 l_next_index BINARY_INTEGER;
3742 l_proc VARCHAR2(32) := 'DEPOSIT_BLOCK_INFO';
3743 BEGIN
3744
3745 --
3746 -- Add the information to the timecard record
3747 --
3748
3749 l_last_index := g_timecard.last;
3750
3751 if (l_last_index is null) then
3752 l_next_index := 1;
3753 else
3754 l_next_index := l_last_index +1;
3755 end if;
3756
3757 --
3758 -- Insert the new building block record
3759 --
3760 g_timecard(l_next_index).TIME_BUILDING_BLOCK_ID := P_TIME_BUILDING_BLOCK_ID;
3761 g_timecard(l_next_index).TYPE := P_TYPE;
3762 g_timecard(l_next_index).MEASURE := P_MEASURE;
3763 g_timecard(l_next_index).UNIT_OF_MEASURE := P_UNIT_OF_MEASURE;
3764 g_timecard(l_next_index).START_TIME := P_START_TIME;
3765 g_timecard(l_next_index).STOP_TIME := P_STOP_TIME;
3766 g_timecard(l_next_index).PARENT_BUILDING_BLOCK_ID := P_PARENT_BUILDING_BLOCK_ID;
3767 g_timecard(l_next_index).PARENT_IS_NEW := P_PARENT_IS_NEW;
3768 g_timecard(l_next_index).SCOPE := P_SCOPE;
3769 g_timecard(l_next_index).OBJECT_VERSION_NUMBER := P_OBJECT_VERSION_NUMBER;
3770 g_timecard(l_next_index).APPROVAL_STATUS := P_APPROVAL_STATUS;
3771 g_timecard(l_next_index).RESOURCE_ID := P_RESOURCE_ID;
3772 g_timecard(l_next_index).RESOURCE_TYPE := P_RESOURCE_TYPE;
3773 g_timecard(l_next_index).APPROVAL_STYLE_ID := P_APPROVAL_STYLE_ID;
3774 g_timecard(l_next_index).DATE_FROM := P_DATE_FROM;
3775 g_timecard(l_next_index).DATE_TO := P_DATE_TO;
3776 g_timecard(l_next_index).COMMENT_TEXT := P_COMMENT_TEXT;
3777 g_timecard(l_next_index).PARENT_BUILDING_BLOCK_OVN := P_PARENT_BUILDING_BLOCK_OVN;
3778 g_timecard(l_next_index).NEW := P_NEW;
3779 g_timecard(l_next_index).CHANGED := P_CHANGED;
3780
3781 if ((p_new='Y') and (g_new_bbs='N')) then
3782
3783 g_new_bbs := 'Y';
3784
3785 end if;
3786
3787 END deposit_block_info;
3788
3789
3790 procedure zdeposit_blocks
3791 is
3792 begin
3793 null;
3794 end zdeposit_blocks;
3795
3796 procedure update_ovns
3797 (p_block_id in NUMBER
3798 ,p_new_parent_ovn in NUMBER
3799 ) is
3800
3801 l_block number;
3802
3803 BEGIN
3804
3805 --
3806 -- Make sure that the newly inserted parents ovn is updated in
3807 -- in the blocks structure
3808 --
3809
3810 l_block := g_timecard.first;
3811
3812 LOOP
3813 EXIT WHEN NOT g_timecard.exists(l_block);
3814
3815 if (g_timecard(l_block).parent_building_block_id = p_block_id) then
3816
3817 g_timecard(l_block).parent_building_block_ovn := p_new_parent_ovn;
3818
3819 end if;
3820
3821 l_block := g_timecard.next(l_block);
3822
3823 END LOOP;
3824
3825
3826 end update_ovns;
3827
3828 function public_private return varchar2 is
3829
3830 l_public_private HXC_TIME_ATTRIBUTES.ATTRIBUTE2%TYPE;
3831
3832 begin
3833
3834 if (fnd_profile.value('HXC_CREATE_PUBLIC_TEMPLATES') = 'Y') then
3835
3836 l_public_private := 'PUBLIC';
3837
3838 else
3839
3840 l_public_private := 'PRIVATE';
3841
3842 end if;
3843
3844 return l_public_private;
3845
3846 end public_private;
3847
3848 procedure alias_translation is
3849
3850 l_attribute_array HXC_ATTRIBUTE_TABLE_TYPE;
3851 l_block_array HXC_BLOCK_TABLE_TYPE;
3852 l_messages HXC_MESSAGE_TABLE_TYPE;
3853
3854 begin
3855
3856 l_attribute_array := hxc_deposit_wrapper_utilities.attributes_to_array(
3857 p_attributes => g_attributes);
3858
3859 HXC_ALIAS_TRANSLATOR.DO_DEPOSIT_TRANSLATION
3860 (p_attributes => l_attribute_array,
3861 p_messages => l_messages);
3862
3863 g_attributes := hxc_deposit_wrapper_utilities.array_to_attributes(
3864 p_attribute_array => l_attribute_array);
3865
3866
3867 exception
3868 when others then
3869 FND_MESSAGE.set_name('HXC','HXC_ALIAS_TRANSLATION');
3870 FND_MESSAGE.RAISE_ERROR;
3871
3872 end;
3873
3874 procedure deposit_blocks
3875 (p_timecard_id out nocopy HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
3876 ,p_timecard_ovn out nocopy HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
3877 ,p_mode varchar2
3878 ,p_deposit_process varchar2
3879 ,p_retrieval_process varchar2 default null
3880 ,p_validate_session boolean default TRUE --AI8
3881 ,p_add_security boolean default TRUE --AI8
3882 ,p_allow_error_tc boolean default FALSE -- GPM v115.87
3883 ) IS
3884 --
3885 l_old_block HXC_TIME_BUILDING_BLOCKS%ROWTYPE;
3886 l_old_attribute HXC_TIME_ATTRIBUTES%ROWTYPE;
3887 l_time_building_block_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE;
3888 l_object_version_number HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE;
3889 l_max_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE;
3890 l_deposit_process_id NUMBER;
3891 l_time_source_id hxc_time_sources.time_source_id%TYPE;
3892 l_time_source_name hxc_time_sources.name%TYPE;
3893 l_timecard hxc_time_attributes_api.timecard;
3894 l_attribute_index NUMBER;
3895 l_bld_blk_info_type_id NUMBER;
3896 l_mapping_comp_usage_id NUMBER;
3897 l_component_row hxc_mapping_components%ROWTYPE;
3898 l_exception EXCEPTION;
3899 l_update_required BOOLEAN := FALSE;
3900 l_attribute_update_req BOOLEAN := FALSE;
3901 l_date DATE := SYSDATE;
3902 l_block NUMBER;
3903 x NUMBER;
3904 l_proc VARCHAR2(30):='deposit_blocks';
3905 l_app_valid_proc_id HXC_RETRIEVAL_PROCESSES.RETRIEVAL_PROCESS_ID%TYPE;
3906 l_retrieval_id HXC_RETRIEVAL_PROCESSES.RETRIEVAL_PROCESS_ID%TYPE;
3907 --l_app_attributes app_attributes_info;
3908 l_upd_sql VARCHAR2(2000);
3909 l_val_sql VARCHAR2(2000);
3910 l_block_string VARCHAR2(32767);
3911 l_attribute_string VARCHAR2(32767);
3912 l_bld_blk_attribute_string VARCHAR2(32767);
3913 --l_message_string VARCHAR2(32767);
3914 l_message_count NUMBER;
3915 l_mode VARCHAR2(30) := 'UPDATE';
3916 l_item_key WF_ITEMS.ITEM_KEY%TYPE;
3917 l_resubmitted VARCHAR2(3);
3918 l_fnd_separator VARCHAR2(5) := FND_GLOBAL.LOCAL_CHR(0);
3919 l_error_message VARCHAR2(4000);
3920 l_temp_temp_name hxc_time_Attributes.attribute1%type;
3921 l_transaction_tab hxc_deposit_wrapper_utilities.t_transaction;
3922 l_overall_status varchar2(7) := 'SUCCESS';
3923 l_approval_style_id HXC_APPROVAL_STYLES.APPROVAL_STYLE_ID%TYPE;
3924
3925 l_approval_timecard_id HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE := null;
3926 l_approval_timecard_ovn HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE := null;
3927
3928 l_block_processed boolean :=false;
3929
3930 l_attributes_cp building_block_attribute_info;
3931 l_timecard_cp timecard_info;
3932 l_app_attributes_cp app_attributes_info;
3933 --
3934 l_submitted BOOLEAN;
3935
3936 --
3937 CURSOR csr_time_building_block(p_building_block_id NUMBER
3938 ,p_bb_ovn NUMBER) IS
3939 select *
3940 from hxc_time_building_blocks
3941 where time_building_block_id = p_building_block_id
3942 and object_version_number = p_bb_ovn;
3943 --
3944 CURSOR csr_time_recipients(p_application_set_id IN NUMBER) IS
3945 SELECT tr.TIME_RECIPIENT_ID
3946 ,tr.NAME
3947 ,tr.APPLICATION_ID
3948 ,tr.OBJECT_VERSION_NUMBER
3949 ,tr.APPLICATION_RETRIEVAL_FUNCTION
3950 ,tr.APPLICATION_UPDATE_PROCESS
3951 ,tr.APPL_VALIDATION_PROCESS
3952 FROM hxc_time_recipients tr
3953 ,hxc_application_set_comps_v asc1
3954 where p_application_set_id = asc1.application_set_id
3955 and asc1.time_recipient_id = tr.time_recipient_id;
3956 --
3957 -- Removed cursor c_code_chk - No usage reference found
3958 -- Refer to TMSTask12613 for further analysis.
3959 --
3960 CURSOR csr_resubmitted
3961 (p_timecard_id IN HXC_TIME_BUILDING_BLOCKS.TIME_BUILDING_BLOCK_ID%TYPE
3962 ,p_max_ovn IN HXC_TIME_BUILDING_BLOCKS.OBJECT_VERSION_NUMBER%TYPE
3963 ) IS
3964 SELECT 'YES'
3965 FROM hxc_time_building_blocks
3966 WHERE time_building_block_id = p_timecard_id
3967 AND object_version_number < p_max_ovn
3968 AND approval_status = 'SUBMITTED';
3969 --
3970 /*
3971 CURSOR csr_item_key
3972 (p_item_type IN wf_item_types.name%TYPE
3973 ) IS
3974 SELECT NVL(MAX(TO_NUMBER(item_key)),1)
3975 FROM wf_item_attribute_values
3976 WHERE item_type = p_item_type;
3977 */
3978
3979 cursor csr_item_key is
3980 select hxc_approval_item_key_s.nextval
3981 from dual;
3982
3983 -- GPM v115.42
3984
3985 CURSOR csr_get_auto_approve IS
3986 SELECT has.approval_style_id
3987 FROM hxc_approval_styles has
3988 WHERE has.name = 'OTL Auto Approve';
3989
3990 -- GPM v115.42
3991
3992 CURSOR csr_get_retrieval IS
3993 SELECT rp.retrieval_process_id
3994 FROM hxc_retrieval_processes rp
3995 WHERE name = p_retrieval_process;
3996
3997
3998 l_template_name hxc_time_attributes.attribute1%type;
3999
4000 l_temp_att_count NUMBER := 0;
4001 l_found_name BOOLEAN := FALSE;
4002 l_index BINARY_INTEGER;
4003 l_index_tc NUMBER :=0;
4004
4005 l_translated_bb_tab hxc_self_service_time_deposit.translate_bb_ids_tab;
4006 l_translated_ta_tab hxc_self_service_time_deposit.translate_ta_ids_tab;
4007
4008 l_application_set_id NUMBER;
4009
4010 l_ind BINARY_INTEGER;
4011
4012 --
4013 begin
4014 --
4015 --
4016
4017
4018 savepoint start_deposit_wrapper; --AI20 was rolling back beyond deposit wrapper
4019
4020 g_allow_error_tc := p_allow_error_tc;
4021
4022 l_submitted := FALSE; -- Used for Santos error check
4023
4024 l_max_ovn := 1;
4025
4026 l_mode := p_mode;
4027 --
4028 -- loop through the arrays for building blocks and attributes
4029 -- and assign sequence numbers to any rows with the new flag
4030 -- set to 'Y'
4031 -- get the deposit process id
4032
4033 SELECT dp.deposit_process_id, dp.time_source_id, ts.name
4034 INTO l_deposit_process_id, l_time_source_id, l_time_source_name
4035 FROM hxc_time_sources ts
4036 , hxc_deposit_processes dp
4037 WHERE dp.name = p_deposit_process -- GPM v115.42
4038 AND ts.time_source_id = dp.time_source_id;
4039
4040 -- Order the time building blocks
4041 order_building_blocks;
4042
4043 -- Denormalization of hours for RANGE type building blocks
4044 denormalize_time_info(p_mode=>'ADD');
4045
4046 -- Set security information from profiles AI8
4047 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4048 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4049 'hxc_self_service_time_deposit.start_deposit_wrapper',
4050 'start the set_security');
4051 end if;
4052
4053 set_security(p_validate_session);
4054
4055 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4056 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4057 'hxc_self_service_time_deposit.start_deposit_wrapper',
4058 'end the set_security');
4059 end if;
4060
4061 -- Add the security context if required
4062 if(p_add_security) then --AI8
4063 add_security;
4064 end if;
4065
4066 -- Correct update flags to reflect database inserts
4067 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4068 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4069 'hxc_self_service_time_deposit.start_deposit_wrapper',
4070 'start the correct_update_flags');
4071 end if;
4072
4073 correct_update_flags(p_allow_error_tc);
4074
4075 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4076 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4077 'hxc_self_service_time_deposit.start_deposit_wrapper',
4078 'end the correct_update_flags');
4079 end if;
4080
4081 -- Next translate the entered attributes into the real attributes, in case
4082 -- there were any aliased fields on the timecard.
4083 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4084 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4085 'hxc_self_service_time_deposit.start_deposit_wrapper',
4086 'start the alias_translation');
4087 end if;
4088
4089 alias_translation;
4090
4091 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4092 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4093 'hxc_self_service_time_deposit.start_deposit_wrapper',
4094 'end the alias_translation');
4095 end if;
4096
4097 --
4098 -- Don't do any validation for templates!
4099 --
4100 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4101 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4102 'hxc_self_service_time_deposit.start_deposit_wrapper',
4103 'g_timecard_block_oder(1)).scope :');
4104 end if;
4105
4106 if (instr(g_timecard(
4107 g_timecard_block_order(1)).scope,'TIMECARD_TEMPLATE') < 1) then
4108
4109 -- Don't do the validation for save!
4110 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4111 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4112 'hxc_self_service_time_deposit.start_deposit_wrapper',
4113 'p_mode :'||p_mode);
4114 end if;
4115
4116 if(p_mode <> 'SAVE') then
4117
4118 -- Find the application validation process
4119
4120
4121 l_application_set_id:=
4122 hxc_preference_evaluation.resource_preferences(
4123 g_timecard(g_timecard_block_order(1)).resource_id,'TS_PER_APPLICATION_SET|1|');
4124
4125
4126 -- Start change version 115.130
4127 -- Added by ksethi for support to ELP changes
4128 --
4129 -- Call to the ELP UTILS Procedure to update the
4130 -- Time Building Blocks with the Application Set
4131 -- information, once this proc is executed
4132 -- all TBB with SCOPE = ('TIMECARD','DAY', n 'DETAIL')
4133 -- have the application set id set
4134 --
4135 hxc_elp_utils.set_time_bb_appl_set_tk
4136 (P_TIME_BUILDING_BLOCKS => g_timecard
4137 ,P_APPLICATION_SET_ID => l_application_set_id
4138 );
4139
4140 -- End change version 115.130
4141
4142 -- allow global tables to be updated from recipient application code
4143 set_update_phase(TRUE); --AI2.5
4144
4145 FOR recipt_rec IN csr_time_recipients(l_application_set_id) LOOP
4146
4147 --
4148 -- Make a call to get the application retrieval process id
4149 -- to use to build the structures we're going to send to the
4150 -- recipient applications for validation purposes
4151 --
4152
4153
4154 IF recipt_rec.application_retrieval_function IS NOT NULL THEN
4155 IF code_chk(recipt_rec.application_retrieval_function) THEN
4156 find_app_deposit_process
4157 (p_time_recipient_id => recipt_rec.time_recipient_id
4158 ,p_app_function => recipt_rec.application_retrieval_function
4159 ,p_retrieval_process_id => l_app_valid_proc_id
4160 );
4161
4162 g_app_attributes := build_application_attributes(
4163 p_retrieval_process_id => l_app_valid_proc_id
4164 ,p_deposit_process_id => NULL --AI3
4165 ,p_for_time_attributes => FALSE);
4166
4167
4168 END IF;
4169
4170 IF recipt_rec.application_update_process IS NOT NULL THEN
4171 IF code_chk(recipt_rec.application_update_process) THEN
4172
4173 l_upd_sql := 'BEGIN '||fnd_global.newline
4174 ||recipt_rec.application_update_process ||fnd_global.newline
4175 ||'(p_operation => :1);'||fnd_global.newline
4176 ||'END;';
4177
4178 EXECUTE IMMEDIATE l_upd_sql
4179 using IN OUT l_mode;
4180
4181 -- Update the g_attributes table from the g_app_attributes table
4182
4183 update_deposit_globals (p_retrieval_process_id => l_app_valid_proc_id);
4184 END IF;
4185 END IF;
4186
4187 END IF; -- If the application retrieval function is NULL
4188
4189 END LOOP;
4190
4191 -- disallow global tables to be updated from recipient application code
4192 set_update_phase(FALSE); --AI2.5
4193
4194 --
4195 -- Do the validation rather than update.
4196 --
4197 FOR recipt_rec IN csr_time_recipients
4198 (l_application_set_id) LOOP
4199
4200 --
4201 -- Make a call to get the application retrieval process id
4202 -- to use to build the structures we're going to send to the
4203 -- recipient applications for validation purposes
4204 --
4205
4206 IF recipt_rec.application_retrieval_function IS NOT NULL THEN
4207 IF code_chk(recipt_rec.application_retrieval_function) THEN
4208 find_app_deposit_process
4209 (p_time_recipient_id => recipt_rec.time_recipient_id
4210 ,p_app_function => recipt_rec.application_retrieval_function
4211 ,p_retrieval_process_id => l_app_valid_proc_id
4212 );
4213 g_app_attributes.delete;
4214 g_app_attributes := build_application_attributes(
4215 p_retrieval_process_id => l_app_valid_proc_id
4216 ,p_deposit_process_id => NULL --AI3
4217 ,p_for_time_attributes => FALSE);
4218 END IF;
4219
4220 IF recipt_rec.appl_validation_process IS NOT NULL THEN
4221 IF code_chk(recipt_rec.appl_validation_process) THEN
4222
4223 l_val_sql := 'BEGIN '||fnd_global.newline
4224 ||recipt_rec.appl_validation_process ||fnd_global.newline
4225 ||'(p_operation => :1);'||fnd_global.newline
4226 ||'END;';
4227
4228 EXECUTE IMMEDIATE l_val_sql
4229 using IN OUT l_mode;
4230
4231 END IF;
4232 END IF;
4233
4234 END IF; -- application retrieval function is null?
4235
4236
4237 END LOOP;
4238
4239 --
4240 -- Execute the time entry rules stuff - working time directive
4241 -- Call this package directly, as it is required for all locales,
4242 -- for all businesses - GPM 3/28/2001
4243 --
4244
4245 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4246 fnd_message.set_token('PROCEDURE', l_proc);
4247 fnd_message.set_token('STEP','This Deposit Wrapper no longer supported');
4248 FND_MSG_PUB.add;
4249
4250 end if; --p_mode <> 'Save'
4251
4252 --
4253 -- Call OTL Specific validation, to make sure everything is set up
4254 -- to use for the time store. We do this on SAVE as well as
4255 -- submit.
4256 --
4257 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4258 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4259 'hxc_self_service_time_deposit.start_deposit_wrapper',
4260 'g_app_attributes step ');
4261 end if;
4262
4263 g_app_attributes := build_application_attributes(
4264 p_retrieval_process_id => NULL
4265 ,p_deposit_process_id => l_deposit_process_id --AI3
4266 ,p_for_time_attributes => TRUE);
4267
4268
4269 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4270 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4271 'hxc_self_service_time_deposit.start_deposit_wrapper',
4272 'HXC_SETUP_VALIDATION_PKG.EXECUTE_OTC_VALIDATION step ');
4273 end if;
4274 /*
4275 HXC_SETUP_VALIDATION_PKG.EXECUTE_OTC_VALIDATION
4276 (P_OPERATION => l_mode
4277 ,P_TIME_BUILDING_BLOCKS => g_timecard
4278 ,P_TIME_ATTRIBUTES => g_attributes
4279 ,P_TIME_ATT_INFO => g_app_attributes
4280 ,P_MESSAGES => g_messages
4281 );
4282 */
4283 else -- then it is a template
4284 --
4285 -- if this is a template, then we should check the name
4286 -- but only if we're creating a template, otherwise
4287 -- the name can be updated, and we should raise the
4288 -- same error, but if the name is the same, then we're ok
4289 --
4290
4291 l_temp_att_count := g_attributes.first;
4292
4293 loop
4294
4295 exit when not (
4296 (g_attributes.exists(l_temp_att_count))
4297 OR
4298 (NOT l_found_name)
4299 );
4300
4301 if (g_attributes(l_temp_att_count).building_block_id
4302 = g_timecard(g_timecard_block_order(1)).time_building_block_id) then
4303
4304
4305 -- ok have a timecard template attribute
4306
4307 if (g_Attributes(l_temp_att_count).attribute_category = 'TEMPLATES') then
4308
4309 l_template_name := g_attributes(l_temp_att_count).attribute1;
4310
4311 --
4312 -- Check the value of the public/private flag based on the profile
4313 -- option value
4314 --
4315 g_attributes(l_temp_att_count).attribute2 := public_private;
4316
4317 -- Bug 2336662
4318 -- This code segment is added for the Enhancement to the
4319 -- Performance of Template view.
4320 -- Here Resource Id of all Public Templates is made -1
4321
4322 if g_attributes(l_temp_att_count).attribute2='PUBLIC' then
4323 g_timecard(g_timecard_block_order(1)).resource_id := -1;
4324 g_timecard(g_timecard_block_order(1)).resource_type := -1;
4325 end if;
4326
4327 -- End of change
4328
4329 l_found_name := TRUE;
4330
4331 end if;
4332
4333 end if;
4334
4335 l_temp_att_count := g_attributes.next(l_temp_att_count);
4336
4337 end loop;
4338
4339
4340 -- now check the template name
4341
4342 if (template_name_exists(g_timecard(g_timecard_block_order(1)).resource_id
4343 ,l_template_name
4344 ,g_timecard(g_timecard_block_order(1)).time_building_block_id)) then
4345
4346 --FND_MESSAGE.set_name('HXC','HXC_366204_TEMPLATE_NAME');
4347 --FND_MESSAGE.set_token('TEMPLATE_NAME',l_template_name);
4348 --FND_MSG_PUB.add;
4349 raise e_template_duplicate_name;
4350 end if;
4351
4352 end if; -- only do the validation if this isn't a timecard template
4353
4354
4355 -- now for each building block... do some stuff
4356
4357 -- Check that we've got no messages on the stack first, because
4358 -- that would indicate an error
4359 /*
4360 IF ( hxc_timekeeper_errors.rollback_tc (
4361 p_allow_error_tc => p_allow_error_tc
4362 , p_message_table => g_messages
4363 , p_timecard => g_timecard ) )
4364 THEN
4365
4366 -- remove any changes
4367 rollback to start_deposit_wrapper; --AI20 was rolling back beyond deposit wrapper
4368
4369 ELSE
4370 */
4371 -- deposit as normal ( errored TC manipulation already performed
4372 -- in roolabck_tc )
4373
4374 -- GPM v115.42
4375
4376 IF ( p_mode = 'MIGRATION' AND g_messages.count = 0 )
4377 THEN
4378
4379 IF ( p_retrieval_process IS NOT NULL )
4380 THEN
4381 OPEN csr_get_retrieval;
4382 FETCH csr_get_retrieval INTO l_retrieval_id;
4383 CLOSE csr_get_retrieval;
4384 ELSE
4385 FND_MESSAGE.set_name('HXC','HXC_0075_HDP_DPROC_NAME_MAND');
4386 FND_MSG_PUB.add;
4387 END IF;
4388
4389 IF ( l_retrieval_id IS NULL )
4390 THEN
4391 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4392 fnd_message.set_token('PROCEDURE', l_proc);
4393 fnd_message.set_token('STEP','1');
4394 FND_MSG_PUB.add;
4395 END IF;
4396
4397 -- if the mode is MIGRATION then this data needs
4398 -- also to be submitted as APPROVED.
4399 -- Override the person approval style to be
4400 -- OTL Auto Approve
4401
4402 OPEN csr_get_auto_approve;
4403 FETCH csr_get_auto_approve INTO l_approval_style_id;
4404 CLOSE csr_get_auto_approve;
4405
4406 -- GPM v115.58
4407
4408 HXC_SELF_SERVICE_TIME_DEPOSIT.SET_WORKFLOW_INFO(P_ITEM_TYPE =>'HXCEMP',
4409 P_PROCESS_NAME => 'HXC_APPROVAL');
4410
4411 -- loop through the global table to override the
4412 -- approval style id
4413
4414 l_index := g_timecard.FIRST;
4415
4416 WHILE ( l_index IS NOT NULL )
4417 LOOP
4418 g_timecard(l_index).approval_style_id := l_approval_style_id;
4419 g_timecard(l_index).approval_status := 'SUBMITTED';
4420
4421 l_index := g_timecard.NEXT(l_index);
4422
4423 END LOOP;
4424
4425 END IF; -- IF p_mode = 'MIGRATION'
4426
4427 -- Clear out denormalization of start/stop times so that we dont store this information
4428 denormalize_time_info(p_mode => 'REMOVE');
4429
4430 l_block := g_timecard_block_order.first;
4431
4432
4433 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4434 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4435 'hxc_self_service_time_deposit.start_deposit_wrapper',
4436 'l_block :'||l_block);
4437 end if;
4438
4439 LOOP
4440
4441 EXIT WHEN NOT g_timecard_block_order.exists(l_block);
4442
4443 --
4444 -- Obtain the current parent information,
4445 -- we shouldn't have to do this because
4446 -- the API really ought not to allow us to update
4447 -- this, but it does at the moment.
4448 --
4449 x := g_timecard_block_order(l_block);
4450
4451 IF g_timecard(x).NEW = 'Y' THEN
4452 --
4453 -- This building block doesn't exist
4454 -- Therefore it must be new
4455 -- We must still deposit it in the same
4456 -- way as before.
4457 -- The attached attributes must also be connected
4458 -- to this building block.
4459 --
4460 -- The end of time check is there to support
4461 -- performance advantage in the middle tier
4462 -- this avoids us having to regenerate the page
4463 -- if the user creates, and then immeadiately
4464 -- deletes rows in the timecard. DO NOT REMOVE!
4465
4466
4467 IF(process_block(x,'UPDATE',p_allow_error_tc))
4468 THEN
4469
4470 build_timecard_structure(
4471 p_block => x
4472 ,p_deposit_process_id => l_deposit_process_id
4473 ,p_timecard => l_timecard
4474 ,p_update_required => l_attribute_update_req);
4475 --
4476 -- Next we perform the deposit of this block and
4477 -- attribute record.
4478 --
4479 l_time_building_block_id := NULL;
4480 l_object_version_number := NULL;
4481 --
4482 -- Put this into it's own PL/SQL block to ensure that
4483 -- we catch any PL/SQL errors, and show them in the
4484 -- correct way. Here we won't be using multiple
4485 -- error messages, rather just one message, because
4486 -- the user should never see an error from this API
4487
4488 BEGIN
4489 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4490 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4491 'hxc_self_service_time_deposit.start_deposit_wrapper',
4492 'process 2 ');
4493 end if;
4494
4495
4496
4497 hxc_deposit_process_pkg.execute_deposit_process
4498 (p_time_building_block_id => l_time_building_block_id
4499 ,p_process_name => p_deposit_process
4500 ,p_source_name => l_time_source_name
4501 ,p_effective_date => sysdate
4502 ,p_type => g_timecard(x).type
4503 ,p_measure => g_timecard(x).measure
4504 ,p_unit_of_measure => g_timecard(x).unit_of_measure
4505 ,p_start_time => g_timecard(x).start_time
4506 ,p_stop_time => g_timecard(x).stop_time
4507 ,p_parent_building_block_id => g_timecard(x).parent_building_block_id
4508 ,p_parent_building_block_ovn => g_timecard(x).parent_building_block_ovn
4509 ,p_scope => g_timecard(x).scope
4510 ,p_approval_style_id => g_timecard(x).approval_style_id
4511 ,p_approval_status => g_timecard(x).approval_status
4512 ,p_resource_id => g_timecard(x).resource_id
4513 ,p_resource_type => g_timecard(x).resource_type
4514 ,p_comment_text => g_timecard(x).comment_text
4515 ,p_application_set_id => g_timecard(x).application_set_id
4516 ,p_timecard => l_timecard
4517 ,p_object_version_number => l_object_version_number
4518 );
4519
4520 l_block_processed := true;
4521
4522 if(g_timecard(x).scope = 'TIMECARD') then
4523 if(g_timecard(x).approval_status = 'SUBMITTED') then
4524
4525 l_approval_timecard_id := l_time_building_block_id;
4526 l_approval_timecard_ovn := l_object_version_number;
4527
4528 end if;
4529 end if;
4530
4531 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4532 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4533 'hxc_self_service_time_deposit.start_deposit_wrapper',
4534 'l_time_building_block_id :'||l_time_building_block_id||
4535 ' l_object_version_number :'||l_object_version_number);
4536 end if;
4537
4538 l_transaction_tab(x).tbb_id := l_time_building_block_id;
4539 l_transaction_tab(x).tbb_ovn := l_object_version_number;
4540 l_transaction_tab(x).status := 'SUCCESS';
4541
4542 EXCEPTION
4543 when others then
4544 l_transaction_tab(x).tbb_id := NVL(l_time_building_block_id, -1);
4545 l_transaction_tab(x).tbb_ovn := NVL(l_object_version_number, -1);
4546 l_transaction_tab(x).status := 'ERRORS';
4547 l_transaction_tab(x).exception_desc := SUBSTR(SQLERRM,1,2000);
4548 l_overall_status := 'ERRORS';
4549
4550 raise;
4551 END;
4552
4553 -- Keep track of max OVN of all the building blocks in this timecard
4554 -- so that approvals process knows whether it is a resubmit or not.
4555 --
4556 IF l_max_ovn < l_object_version_number THEN
4557 l_max_ovn := l_object_version_number;
4558 END IF;
4559 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4560 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4561 'hxc_self_service_time_deposit.start_deposit_wrapper',
4562 'l_max_ovn :'||l_max_ovn);
4563 end if;
4564 --
4565 -- Once the building block has been deposited, we can find all
4566 -- instances of other building blocks which pointed to this one
4567 -- as the parent, and update those records to point to the newly
4568 -- db created building block
4569 --
4570 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4571 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4572 'hxc_self_service_time_deposit.start_deposit_wrapper',
4573 'z :');
4574 end if;
4575
4576 l_index_tc := g_timecard.first;
4577 LOOP
4578 EXIT WHEN NOT g_timecard.exists(l_index_tc);
4579 IF (
4580 (g_timecard(l_index_tc).parent_building_block_id
4581 = g_timecard(x).time_building_block_id)
4582 AND
4583 (g_timecard(l_index_tc).parent_is_new = 'Y')
4584 ) THEN
4585 g_timecard(l_index_tc).parent_building_block_id := l_time_building_block_id;
4586 g_timecard(l_index_tc).parent_building_block_ovn := 1;
4587 END IF;
4588
4589 l_index_tc := g_timecard.next(l_index_tc);
4590 END LOOP;
4591
4592
4593 -- Next we must update the actual values of the building block id etc.
4594 -- Held in our internal structure, in case we need these later, e.g.
4595 -- for the approvals process.
4596
4597 l_translated_bb_tab(g_timecard(x).time_building_block_id).actual_bb_id := l_time_building_block_id;
4598
4599 g_timecard(x).time_building_block_id := l_time_building_block_id;
4600 g_timecard(x).object_version_number := 1;
4601
4602 END IF;
4603 ELSE
4604 --
4605 -- This is an existing building block, therefore
4606 -- we can check to see if anything has changed,
4607 -- to see whether we need to update this block
4608 --
4609 l_update_required := FALSE;
4610 --
4611 -- If the block corresponds to a NULLED out
4612 -- detail, we don't want to update it, if
4613 -- any of the other things have changed.
4614 --
4615 l_attribute_update_req := FALSE;
4616
4617 open csr_time_building_block
4618 (g_timecard(x).time_building_block_id
4619 ,g_timecard(x).object_version_number);
4620 fetch csr_time_building_block into l_old_block;
4621 close csr_time_building_block;
4622
4623 l_update_required := process_block(x,'UPDATE',p_allow_error_tc);
4624
4625 build_timecard_structure(
4626 p_block => x
4627 ,p_deposit_process_id => l_deposit_process_id
4628 ,p_timecard => l_timecard
4629 ,p_update_required => l_attribute_update_req);
4630
4631 --
4632 -- execute the deposit process, passing the derived
4633 -- parent information, and only if something has
4634 -- change which requires the deposit.
4635 --
4636 if (
4637 (l_update_required OR l_attribute_update_req)
4638 AND
4639 (NOT process_block(x,'DELETE',p_allow_error_tc))
4640 ) THEN
4641 BEGIN
4642 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4643 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
4644 'hxc_self_service_time_deposit.start_deposit_wrapper',
4645 'process :'||g_timecard(x).time_building_block_id);
4646 end if;
4647
4648 hxc_deposit_process_pkg.execute_deposit_process
4649 (p_time_building_block_id => g_timecard(x).time_building_block_id
4650 ,p_process_name => p_deposit_process
4651 ,p_source_name => l_time_source_name
4652 ,p_effective_date => sysdate
4653 ,p_type => g_timecard(x).type
4654 ,p_measure => g_timecard(x).measure
4655 ,p_unit_of_measure => g_timecard(x).unit_of_measure
4656 ,p_start_time => g_timecard(x).start_time
4657 ,p_stop_time => g_timecard(x).stop_time
4658 ,p_parent_building_block_id => g_timecard(x).parent_building_block_id
4659 ,p_parent_building_block_ovn => g_timecard(x).parent_building_block_ovn
4660 ,p_scope => g_timecard(x).scope
4661 ,p_approval_style_id => g_timecard(x).approval_style_id
4662 ,p_approval_status => g_timecard(x).approval_status
4663 ,p_resource_id => g_timecard(x).resource_id
4664 ,p_resource_type => g_timecard(x).resource_type
4665 ,p_comment_text => g_timecard(x).comment_text
4666 ,p_application_set_id => g_timecard(x).application_set_id
4667 ,p_timecard => l_timecard
4668 ,p_object_version_number => g_timecard(x).object_version_number
4669 );
4670
4671
4672 l_block_processed := true;
4673
4674 if(g_timecard(x).scope = 'TIMECARD') then
4675 if(g_timecard(x).approval_status = 'SUBMITTED') then
4676
4677 l_approval_timecard_id := g_timecard(x).time_building_block_id;
4678 l_approval_timecard_ovn := g_timecard(x).object_version_number;
4679
4680 end if;
4681 end if;
4682
4683 l_transaction_tab(x).tbb_id := g_timecard(x).time_building_block_id;
4684 l_transaction_tab(x).tbb_ovn := g_timecard(x).object_version_number;
4685 l_transaction_tab(x).status := 'SUCCESS';
4686
4687 EXCEPTION
4688 when others then
4689 l_transaction_tab(x).tbb_id := g_timecard(x).time_building_block_id;
4690 l_transaction_tab(x).tbb_ovn := g_timecard(x).object_version_number;
4691 l_transaction_tab(x).status := 'ERRORS';
4692 l_transaction_tab(x).exception_desc := SUBSTR(SQLERRM, 1,2000);
4693 l_overall_status := 'ERRORS';
4694
4695 raise;
4696 END;
4697 --
4698 -- Keep track of max OVN of all the building blocks in this timecard
4699 -- so that approvals process knows whether it is a resubmit or not.
4700 --
4701 IF l_max_ovn < g_timecard(x).object_version_number THEN
4702 l_max_ovn := g_timecard(x).object_version_number;
4703 END IF;
4704 --
4705 -- Keep object version numbers in sync
4706 --
4707 update_ovns(g_timecard(x).time_building_block_id,
4708 g_timecard(x).object_version_number);
4709 --
4710 ELSIF ( process_block(x,'DELETE',p_allow_error_tc)
4711 ) THEN
4712 --
4713 -- In this case, we've deleted a block in the timecard
4714 -- typically this will happen when the user clicks on the
4715 -- delete attribute row icon of the timecard
4716 --
4717 -- In this case, call the building block API directly, to
4718 -- date effectively end date the building block.
4719 -- FIX: Will this cause inconsistencies with the deposit API?
4720 --
4721 IF (g_timecard(x).measure IS NULL) THEN
4722 l_date := SYSDATE;
4723 ELSE
4724 l_date := g_timecard(x).date_to;
4725 END IF;
4726
4727 l_block_processed := true;
4728
4729 hxc_building_block_api.delete_building_block
4730 (p_validate => FALSE
4731 ,p_object_version_number => g_timecard(x).object_version_number
4732 ,p_time_building_block_id => g_timecard(x).time_building_block_id
4733 ,p_effective_date => l_date
4734 );
4735 -- A. Rundell 115.118 Change.
4736 -- Added to ensure the parent OVN of deleted parents
4737 -- is set correctly. note: this means that the
4738 -- children of a deleted parent are implicitly also
4739 -- deleted.
4740
4741 update_ovns(g_timecard(x).time_building_block_id,
4742 g_timecard(x).object_version_number);
4743
4744 --
4745 -- End 115.118 Change
4746 --
4747
4748 end if;
4749
4750 END IF;-- Is this a new or existing building block
4751
4752 if (g_timecard(x).scope = 'TIMECARD') then
4753
4754 p_timecard_id := g_timecard(x).time_building_block_id;
4755 p_timecard_ovn := g_timecard(x).object_version_number;
4756
4757 end if;
4758
4759 l_timecard.delete;
4760
4761 l_block := g_timecard_block_order.next(l_block);
4762
4763 END LOOP;
4764
4765 -- GPM v115.87
4766 -- GPM v115.89 - added .FIRST
4767
4768 IF ( g_timecard((g_timecard.FIRST)).approval_status = 'ERROR' )
4769 THEN
4770 l_overall_status := 'ERRORS';
4771 END IF;
4772
4773 hxc_deposit_wrapper_utilities.audit_transaction (
4774 p_effective_date => sysdate
4775 , p_transaction_type => 'DEPOSIT'
4776 , p_transaction_process_id => l_deposit_process_id
4777 , p_overall_status => l_overall_status
4778 , p_transaction_tab => l_transaction_tab );
4779
4780 -- GPM v115.42
4781
4782 IF ( p_mode = 'MIGRATION' AND l_overall_status = 'SUCCESS' )
4783 THEN
4784
4785 hxc_deposit_wrapper_utilities.audit_transaction (
4786 p_effective_date => sysdate
4787 , p_transaction_type => 'RETRIEVAL'
4788 , p_transaction_process_id => l_app_valid_proc_id
4789 , p_overall_status => l_overall_status
4790 , p_transaction_tab => l_transaction_tab );
4791
4792 END IF;
4793
4794 -- COMMIT;
4795
4796 -- Add denormalization again. This is for those applications that use the approval
4797 -- extensions
4798 denormalize_time_info(p_mode=>'ADD');
4799
4800 --
4801 -- Now check to see if we have to resubmit
4802 --
4803 if((l_approval_timecard_id is null) and (l_block_processed)) then
4804
4805 -- find the timecard id!
4806 l_block := g_timecard.first;
4807 l_block_processed :=false;
4808
4809 LOOP
4810 EXIT WHEN ((not g_timecard.exists(l_block)) OR (l_block_processed));
4811
4812 if((g_timecard(l_block).time_building_block_id > 0)
4813 AND
4814 (g_timecard(l_block).scope='TIMECARD')
4815 AND
4816 (g_timecard(l_block).approval_status = 'SUBMITTED')
4817 AND
4818 (g_timecard(l_block).date_to = hr_general.end_of_time)
4819 ) then
4820
4821 l_approval_timecard_id := g_timecard(l_block).time_building_block_id;
4822 l_approval_timecard_ovn := g_timecard(l_block).object_version_number;
4823 l_block_processed := true;
4824
4825 end if;
4826
4827 l_block := g_timecard.next(l_block);
4828
4829 END LOOP;
4830
4831 end if;
4832
4833 --
4834 -- Don't bother to check approval for templates!
4835 --
4836
4837 if((l_approval_timecard_id is not null) AND ((p_mode <> 'DELETE') or (p_mode <> 'SAVE'))) THEN
4838 --
4839 -- Next include the approvals process
4840 --
4841 -- Until we workflow the timecard, we need to start the
4842 -- approvals process here.
4843 -- Get the item key
4844 --
4845 OPEN csr_item_key;
4846 FETCH csr_item_key into l_item_key;
4847 close csr_item_key;
4848
4849 OPEN csr_resubmitted
4850 (p_timecard_id => g_timecard(g_timecard_block_order(1)).time_building_block_id
4851 ,p_max_ovn => l_max_ovn
4852 );
4853 FETCH csr_resubmitted INTO l_resubmitted;
4854
4855 IF csr_resubmitted%NOTFOUND THEN
4856
4857 l_resubmitted := 'NO';
4858
4859 END IF;
4860
4861 CLOSE csr_resubmitted;
4862
4863 HXC_APPROVAL_WF_PKG.start_approval_wf_process
4864 (p_item_type => g_workflow.item_type
4865 ,p_item_key => l_item_key
4866 ,p_tc_bb_id => l_approval_timecard_id
4867 ,p_tc_ovn => l_approval_timecard_ovn
4868 ,p_tc_resubmitted => l_resubmitted
4869 ,p_error_table => g_messages
4870 ,p_time_building_blocks => g_timecard
4871 ,p_time_attributes => g_attributes
4872 ,p_bb_new => g_new_bbs
4873 );
4874
4875 l_submitted := true;
4876
4877 end if;
4878
4879 --
4880 --end if; -- were any errors in the update, validate or set up checks?
4881
4882 --
4883 -- Check that if the mode was submit, the timecard
4884 -- did actually get submitted and the workflow started
4885 -- if not, throw an error. This functionality was
4886 -- added for Santos. It will be removed in future
4887 -- when the time entry rule functions are used for this
4888 -- purpose.
4889
4890
4891 if(g_messages.count = 0) then
4892 if((p_mode = 'SUBMIT') AND (NOT l_submitted)) then
4893
4894
4895 hxc_time_entry_rules_utils_pkg.add_error_to_table (
4896 p_message_table => g_messages
4897 , p_message_name => 'HXC_TIMECARD_NOT_SUBMITTED'
4898 , p_message_token => NULL
4899 , p_message_level => 'ERROR'
4900 , p_message_field => NULL
4901 , p_timecard_bb_id => g_timecard(g_timecard.FIRST).time_building_block_id
4902 , p_timecard_bb_ovn => g_timecard(g_timecard.FIRST).object_version_number --added 2822462
4903 , p_time_attribute_id => NULL);
4904 /*
4905 IF ( hxc_timekeeper_errors.rollback_tc (
4906 p_allow_error_tc => p_allow_error_tc
4907 , p_message_table => g_messages
4908 , p_timecard => g_timecard ) )
4909 THEN
4910
4911 raise e_approval_check;
4912
4913 END IF;
4914 */
4915 end if;
4916 end if;
4917
4918 -- now maintain the error table
4919 /*
4920 IF ( g_messages.COUNT <> 0 AND l_transaction_tab.COUNT <> 0 )
4921 THEN
4922
4923 hxc_timekeeper_errors.maintain_errors (
4924 p_translated_bb_ids_tab => l_translated_bb_tab
4925 , p_translated_ta_ids_tab => l_translated_ta_tab -- GAZ - need to get TA trans tab
4926 , p_messages => g_messages
4927 , p_transactions => l_transaction_tab );
4928
4929 END IF;
4930 */
4931
4932 show_errors(p_messages => g_messages );
4933
4934 EXCEPTION
4935 when e_timecard_overlap then
4936 rollback to start_deposit_wrapper;
4937 fnd_msg_pub.add;
4938 fnd_message.clear;
4939
4940 when e_approval_check then
4941 rollback to start_deposit_wrapper;
4942 show_errors ( p_messages => g_messages );
4943
4944 when e_template_duplicate_name then
4945 hxc_time_entry_rules_utils_pkg.add_error_to_table (
4946 p_message_table => g_messages
4947 , p_message_name => 'HXC_366204_TEMPLATE_NAME'
4948 , p_message_token => 'TEMPLATE_NAME&'||l_template_name
4949 , p_message_level => 'ERROR'
4950 , p_message_field => NULL
4951 , p_timecard_bb_id => NULL
4952 , p_time_attribute_id => NULL
4953 , p_timecard_bb_ovn => NULL
4954 , p_time_attribute_ovn => NULL
4955 );
4956
4957
4958 show_errors( p_messages => g_messages );
4959
4960 when others then
4961
4962 hxc_time_entry_rules_utils_pkg.add_error_to_table (
4963 p_message_table => g_messages
4964 , p_message_name => 'EXCEPTION' -- GPM v115.116
4965 , p_message_token => NULL
4966 , p_message_level => 'ERROR'
4967 , p_message_field => NULL
4968 , p_timecard_bb_id => g_timecard(g_timecard.FIRST).time_building_block_id
4969 , p_time_attribute_id => NULL
4970 , p_timecard_bb_ovn => g_timecard(g_timecard.FIRST).object_Version_number
4971 , p_time_attribute_ovn => NULL );
4972
4973 show_errors( p_messages => g_messages );
4974
4975 IF ( l_transaction_tab.count <> 0 )
4976 THEN
4977
4978 l_overall_status := 'ERROR';
4979
4980 hxc_deposit_wrapper_utilities.audit_transaction (
4981 p_effective_date => sysdate
4982 , p_transaction_type => 'DEPOSIT'
4983 , p_transaction_process_id => l_deposit_process_id
4984 , p_overall_status => l_overall_status
4985 , p_transaction_tab => l_transaction_tab );
4986
4987 -- GPM V115.116
4988 /*
4989 IF ( hxc_timekeeper_errors.rollback_tc (
4990 p_allow_error_tc => p_allow_error_tc
4991 , p_message_table => g_messages
4992 , p_timecard => g_timecard ) )
4993 THEN
4994 rollback to start_deposit_wrapper;
4995 raise;
4996 ELSE
4997 hxc_timekeeper_errors.maintain_errors (
4998 p_translated_bb_ids_tab => l_translated_bb_tab
4999 , p_translated_ta_ids_tab => l_translated_ta_tab -- GAZ - need to get TA trans tab
5000 , p_messages => g_messages
5001 , p_transactions => l_transaction_tab );
5002
5003 END IF;
5004 */
5005 END IF;
5006
5007 --
5008 END deposit_blocks;
5009
5010 -- procedure
5011 -- delete_timecard
5012 --
5013 -- description
5014 -- deletes (effectively end-dates) an entire timecard in the time store
5015 -- The method used to delete the timecard is to read back all the blocks
5016 -- involved and use block deposits to end_date each block and update the
5017 -- ovns. The deleted timecard is then deposited using deposit blocks which
5018 -- makes sure that applications have the chance to validate the deleted timecard
5019 -- and also that if a submitted timecard is deleted then the empty timecard
5020 -- gets submitted. The deletion of details that this routine carries out should
5021 -- be indentical to that which happens if you use the timecard to individually
5022 -- delete each of those entries
5023 --
5024 -- parameters
5025 -- p_time_building_block_id - time building block id
5026 -- p_effective_date - effective date
5027
5028 procedure delete_timecard
5029 (p_time_building_block_id in number
5030 ,p_effective_date in date
5031 ,p_mode varchar2
5032 ,p_deposit_process varchar2
5033 ,p_retrieval_process varchar2
5034 ) is
5035
5036 cursor get_attributes(p_tbb_id in number,p_tbb_ovn in number)
5037 is
5038 select
5039 ta.TIME_ATTRIBUTE_ID ta_id
5040 ,bbit.BLD_BLK_INFO_TYPE ta_BLD_BLK_INFO_TYPE
5041 ,ta.ATTRIBUTE_CATEGORY ta_ATTRIBUTE_CATEGORY
5042 ,ta.ATTRIBUTE1 ta_ATTRIBUTE1
5043 ,ta.ATTRIBUTE2 ta_ATTRIBUTE2
5044 ,ta.ATTRIBUTE3 ta_ATTRIBUTE3
5045 ,ta.ATTRIBUTE4 ta_ATTRIBUTE4
5046 ,ta.ATTRIBUTE5 ta_ATTRIBUTE5
5047 ,ta.ATTRIBUTE6 ta_ATTRIBUTE6
5048 ,ta.ATTRIBUTE7 ta_ATTRIBUTE7
5049 ,ta.ATTRIBUTE8 ta_ATTRIBUTE8
5050 ,ta.ATTRIBUTE9 ta_ATTRIBUTE9
5051 ,ta.ATTRIBUTE10 ta_ATTRIBUTE10
5052 ,ta.ATTRIBUTE11 ta_ATTRIBUTE11
5053 ,ta.ATTRIBUTE12 ta_ATTRIBUTE12
5054 ,ta.ATTRIBUTE13 ta_ATTRIBUTE13
5055 ,ta.ATTRIBUTE14 ta_ATTRIBUTE14
5056 ,ta.ATTRIBUTE15 ta_ATTRIBUTE15
5057 ,ta.ATTRIBUTE16 ta_ATTRIBUTE16
5058 ,ta.ATTRIBUTE17 ta_ATTRIBUTE17
5059 ,ta.ATTRIBUTE18 ta_ATTRIBUTE18
5060 ,ta.ATTRIBUTE19 ta_ATTRIBUTE19
5061 ,ta.ATTRIBUTE20 ta_ATTRIBUTE20
5062 ,ta.ATTRIBUTE21 ta_ATTRIBUTE21
5063 ,ta.ATTRIBUTE22 ta_ATTRIBUTE22
5064 ,ta.ATTRIBUTE23 ta_ATTRIBUTE23
5065 ,ta.ATTRIBUTE24 ta_ATTRIBUTE24
5066 ,ta.ATTRIBUTE25 ta_ATTRIBUTE25
5067 ,ta.ATTRIBUTE26 ta_ATTRIBUTE26
5068 ,ta.ATTRIBUTE27 ta_ATTRIBUTE27
5069 ,ta.ATTRIBUTE28 ta_ATTRIBUTE28
5070 ,ta.ATTRIBUTE29 ta_ATTRIBUTE29
5071 ,ta.ATTRIBUTE30 ta_ATTRIBUTE30
5072 ,ta.BLD_BLK_INFO_TYPE_ID ta_INFO_TYPE
5073 ,ta.OBJECT_VERSION_NUMBER ta_OVN
5074 from hxc_time_attribute_usages tau,
5075 hxc_time_attributes ta,
5076 hxc_bld_blk_info_types bbit
5077 where tau.time_building_block_id = p_tbb_id
5078 and tau.time_building_block_ovn = p_tbb_ovn
5079 and ta.time_attribute_id = tau.time_attribute_id
5080 and ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
5081
5082 cursor get_timecard_building_block(p_timecard_block in number)
5083 is
5084 select
5085 tbb_tim.time_building_block_id tbb_tim_id,
5086 tbb_tim.object_version_number tbb_tim_ovn,
5087 tbb_tim.type tbb_tim_type,
5088 tbb_tim.measure tbb_tim_measure,
5089 tbb_tim.unit_of_measure tbb_tim_unit_of_measure,
5090 tbb_tim.start_time tbb_tim_start_time,
5091 tbb_tim.stop_time tbb_tim_stop_time,
5092 tbb_tim.scope tbb_tim_scope,
5093 tbb_tim.approval_status tbb_tim_approval_status,
5094 tbb_tim.resource_id tbb_tim_resource_id,
5095 tbb_tim.resource_type tbb_tim_resource_type,
5096 tbb_tim.approval_style_id tbb_tim_approval_style,
5097 tbb_tim.date_from tbb_tim_date_from,
5098 tbb_tim.date_to tbb_tim_date_to,
5099 tbb_tim.comment_text tbb_tim_comment_text
5100 from hxc_time_building_blocks tbb_tim
5101 where tbb_tim.time_building_block_id = p_timecard_block
5102 and tbb_tim.date_to = hr_general.end_of_time
5103 and (tbb_tim.scope='TIMECARD' or tbb_tim.scope='TIMECARD_TEMPLATE');
5104
5105 cursor get_day_building_blocks(p_timecard_block in number,p_timecard_ovn in number)
5106 is
5107 select
5108 tbb_day.time_building_block_id tbb_day_id,
5109 tbb_day.object_version_number tbb_day_ovn,
5110 tbb_day.type tbb_day_type,
5111 tbb_day.measure tbb_day_measure,
5112 tbb_day.unit_of_measure tbb_day_unit_of_measure,
5113 tbb_day.start_time tbb_day_start_time,
5114 tbb_day.stop_time tbb_day_stop_time,
5115 tbb_day.scope tbb_day_scope,
5116 tbb_day.approval_status tbb_day_approval_status,
5117 tbb_day.resource_id tbb_day_resource_id,
5118 tbb_day.resource_type tbb_day_resource_type,
5119 tbb_day.approval_style_id tbb_day_approval_style,
5120 tbb_day.date_from tbb_day_date_from,
5121 tbb_day.date_to tbb_day_date_to,
5122 tbb_day.comment_text tbb_day_comment_text
5123 from hxc_time_building_blocks tbb_day
5124 where tbb_day.parent_building_block_ovn = p_timecard_ovn
5125 and tbb_day.parent_building_block_id = p_timecard_block
5126 and tbb_day.date_to = hr_general.end_of_time
5127 and tbb_day.scope='DAY';
5128
5129 cursor get_detail_building_blocks(p_timecard_block in number,p_timecard_ovn in number)
5130 is
5131 select
5132 tbb_det.time_building_block_id tbb_det_id,
5133 tbb_det.object_version_number tbb_det_ovn,
5134 tbb_day.time_building_block_id tbb_day_id,
5135 tbb_day.object_version_number tbb_day_ovn,
5136 tbb_det.type tbb_det_type,
5137 tbb_det.measure tbb_det_measure,
5138 tbb_det.unit_of_measure tbb_det_unit_of_measure,
5139 tbb_det.start_time tbb_det_start_time,
5140 tbb_det.stop_time tbb_det_stop_time,
5141 tbb_det.scope tbb_det_scope,
5142 tbb_det.approval_status tbb_det_approval_status,
5143 tbb_det.resource_id tbb_det_resource_id,
5144 tbb_det.resource_type tbb_det_resource_type,
5145 tbb_det.approval_style_id tbb_det_approval_style,
5146 tbb_det.date_from tbb_det_date_from,
5147 tbb_det.date_to tbb_det_date_to,
5148 tbb_det.comment_text tbb_det_comment_text
5149 from hxc_time_building_blocks tbb_day,
5150 hxc_time_building_blocks tbb_det
5151 where tbb_day.parent_building_block_ovn = p_timecard_ovn
5152 and tbb_day.parent_building_block_id = p_timecard_block
5153 and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
5154 and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
5155 and tbb_det.date_to = hr_general.end_of_time
5156 -- Why this condition is not met for a submitted timecard but is for saved timecards?
5157 -- and tbb_day.date_to = hr_general.end_of_time
5158 and tbb_det.scope='DETAIL';
5159
5160 -- Bug Fix for 2388621
5161 -- Cursor to fetch the template details
5162 cursor get_template_details(p_time_building_block_id in number)
5163 is
5164 select attribute2||'|'||tau.time_building_block_id
5165 from hxc_time_attributes ta,
5166 hxc_time_attribute_usages tau
5167 where ta.time_attribute_id=tau.time_attribute_id
5168 and ta.attribute_category='TEMPLATES'
5169 and tau.time_building_block_id=p_time_building_block_id;
5170
5171 l_timecard_ovn number;
5172 l_timecard_block number;
5173 l_returned_timecard_id number;
5174 l_returned_timecard_ovn number;
5175 l_scope hxc_time_building_blocks.scope%TYPE;
5176
5177 e_no_such_timecard exception;
5178 e_not_timecard_scope exception;
5179
5180 l_exists VARCHAR2(6) := NULL;
5181 l_pref_value VARCHAR2(170) := NULL;
5182
5183 begin
5184
5185 HXC_SELF_SERVICE_TIME_DEPOSIT.SET_WORKFLOW_INFO(P_ITEM_TYPE =>'HXCEMP',
5186 P_PROCESS_NAME => 'HXC_APPROVAL');
5187 HXC_SELF_SERVICE_TIME_DEPOSIT.INITIALIZE_GLOBALS;
5188
5189 -- Pick up the timecard block. Trap the no_data_found exeption for this
5190 -- pl/sql block as it means that p_timecard_block is not a valid building_block
5191
5192 begin
5193 for l_tc in get_timecard_building_block(p_time_building_block_id) loop
5194
5195 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_block_info(
5196 P_TIME_BUILDING_BLOCK_ID => l_tc.tbb_tim_id
5197 ,P_TYPE => l_tc.tbb_tim_type
5198 ,P_MEASURE => l_tc.tbb_tim_measure
5199 ,P_UNIT_OF_MEASURE => l_tc.tbb_tim_unit_of_measure
5200 ,P_START_TIME => l_tc.tbb_tim_start_time
5201 ,P_STOP_TIME => l_tc.tbb_tim_stop_time
5202 ,P_PARENT_BUILDING_BLOCK_ID => null
5203 ,P_PARENT_IS_NEW => 'N'
5204 ,P_SCOPE => l_tc.tbb_tim_scope
5205 ,P_OBJECT_VERSION_NUMBER => l_tc.tbb_tim_ovn
5206 ,P_APPROVAL_STATUS => l_tc.tbb_tim_approval_status
5207 ,P_RESOURCE_ID => l_tc.tbb_tim_resource_id
5208 ,P_RESOURCE_TYPE => l_tc.tbb_tim_resource_type
5209 ,P_APPROVAL_STYLE_ID => l_tc.tbb_tim_approval_style
5210 ,P_DATE_FROM => l_tc.tbb_tim_date_from
5211 ,P_DATE_TO => p_effective_date
5212 ,P_COMMENT_TEXT => l_tc.tbb_tim_comment_text
5213 ,P_PARENT_BUILDING_BLOCK_OVN => null
5214 ,P_NEW => 'N'
5215 ,P_CHANGED => 'Y'
5216 );
5217
5218 for l_tc_attribute in get_attributes(l_tc.tbb_tim_id, l_tc.tbb_tim_ovn) loop
5219
5220 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_attribute_info(
5221 p_TIME_ATTRIBUTE_ID => l_tc_attribute.ta_id
5222 ,p_BUILDING_BLOCK_ID => l_tc.tbb_tim_id
5223 ,p_BLD_BLK_INFO_TYPE => l_tc_attribute.ta_BLD_BLK_INFO_TYPE
5224 ,p_ATTRIBUTE_CATEGORY => l_tc_attribute.ta_ATTRIBUTE_CATEGORY
5225 ,p_ATTRIBUTE1 => l_tc_attribute.ta_ATTRIBUTE1
5226 ,p_ATTRIBUTE2 => l_tc_attribute.ta_ATTRIBUTE2
5227 ,p_ATTRIBUTE3 => l_tc_attribute.ta_ATTRIBUTE3
5228 ,p_ATTRIBUTE4 => l_tc_attribute.ta_ATTRIBUTE4
5229 ,p_ATTRIBUTE5 => l_tc_attribute.ta_ATTRIBUTE5
5230 ,p_ATTRIBUTE6 => l_tc_attribute.ta_ATTRIBUTE6
5231 ,p_ATTRIBUTE7 => l_tc_attribute.ta_ATTRIBUTE7
5232 ,p_ATTRIBUTE8 => l_tc_attribute.ta_ATTRIBUTE8
5233 ,p_ATTRIBUTE9 => l_tc_attribute.ta_ATTRIBUTE9
5234 ,p_ATTRIBUTE10 => l_tc_attribute.ta_ATTRIBUTE10
5235 ,p_ATTRIBUTE11 => l_tc_attribute.ta_ATTRIBUTE11
5236 ,p_ATTRIBUTE12 => l_tc_attribute.ta_ATTRIBUTE12
5237 ,p_ATTRIBUTE13 => l_tc_attribute.ta_ATTRIBUTE13
5238 ,p_ATTRIBUTE14 => l_tc_attribute.ta_ATTRIBUTE14
5239 ,p_ATTRIBUTE15 => l_tc_attribute.ta_ATTRIBUTE15
5240 ,p_ATTRIBUTE16 => l_tc_attribute.ta_ATTRIBUTE16
5241 ,p_ATTRIBUTE17 => l_tc_attribute.ta_ATTRIBUTE17
5242 ,p_ATTRIBUTE18 => l_tc_attribute.ta_ATTRIBUTE18
5243 ,p_ATTRIBUTE19 => l_tc_attribute.ta_ATTRIBUTE19
5244 ,p_ATTRIBUTE20 => l_tc_attribute.ta_ATTRIBUTE20
5245 ,p_ATTRIBUTE21 => l_tc_attribute.ta_ATTRIBUTE21
5246 ,p_ATTRIBUTE22 => l_tc_attribute.ta_ATTRIBUTE22
5247 ,p_ATTRIBUTE23 => l_tc_attribute.ta_ATTRIBUTE23
5248 ,p_ATTRIBUTE24 => l_tc_attribute.ta_ATTRIBUTE24
5249 ,p_ATTRIBUTE25 => l_tc_attribute.ta_ATTRIBUTE25
5250 ,p_ATTRIBUTE26 => l_tc_attribute.ta_ATTRIBUTE26
5251 ,p_ATTRIBUTE27 => l_tc_attribute.ta_ATTRIBUTE27
5252 ,p_ATTRIBUTE28 => l_tc_attribute.ta_ATTRIBUTE28
5253 ,p_ATTRIBUTE29 => l_tc_attribute.ta_ATTRIBUTE29
5254 ,p_ATTRIBUTE30 => l_tc_attribute.ta_ATTRIBUTE30
5255 ,p_BLD_BLK_INFO_TYPE_ID => l_tc_attribute.ta_info_type
5256 ,p_OBJECT_VERSION_NUMBER => l_tc_attribute.ta_ovn
5257 ,p_NEW => 'N'
5258 ,p_changed => 'N');
5259
5260 -- keep some information about the timecard block for use later
5261 l_timecard_block := l_tc.tbb_tim_id;
5262 l_timecard_ovn := l_tc.tbb_tim_ovn;
5263 l_scope := l_tc.tbb_tim_scope;
5264
5265 end loop;
5266 end loop;
5267 exception
5268 when no_data_found then
5269 raise e_no_such_timecard;
5270 end;
5271
5272 -- double check that p_timecard_block is of the right scope
5273 if l_scope not in ('TIMECARD', 'TIMECARD_TEMPLATE') then
5274 raise e_not_timecard_scope;
5275 end if;
5276
5277
5278 -- Bug Fix for 2388621 - Start
5279 -- Check whether the template is used in 'Self-Service Default
5280 -- Template Selected by User' and 'Self-Service Default Template
5281 -- assigned by Sys Admin' Preferences. If so, the template can't be
5282 -- deleted and raise the exception.
5283 -- This exception will be handled by the TemplatesVOImpl.java file.
5284
5285 if l_scope = 'TIMECARD_TEMPLATE' then
5286 open get_template_details(p_time_building_block_id);
5287 fetch get_template_details into l_pref_value;
5288 if get_template_details%found then
5289 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
5290 ('TC_W_TMPLT_DFLT_VAL_USR'
5291 ,1
5292 ,l_pref_value);
5293 if ( l_exists <> 0 ) THEN
5294 close get_template_details;
5295 FND_MESSAGE.SET_NAME('HXC','HXC_CANT_DEL_TEMPL');
5296 FND_MESSAGE.RAISE_ERROR;
5297 END if;
5298
5299 l_exists := HXC_PREFERENCE_EVALUATION.num_hierarchy_occurances
5300 ('TC_W_TMPLT_DFLT_VAL_ADMIN'
5301 ,1
5302 ,l_pref_value);
5303 if ( l_exists <> 0 ) THEN
5304 close get_template_details;
5305 FND_MESSAGE.SET_NAME('HXC','HXC_CANT_DEL_TEMPL');
5306 FND_MESSAGE.RAISE_ERROR;
5307 END if;
5308 END IF;
5309 close get_template_details;
5310 END IF;
5311
5312 -- Bug Fix for 2388621 - End
5313
5314 -- now for the DETAILs
5315
5316 for l_detail in get_detail_building_blocks(l_timecard_block,l_timecard_ovn) loop
5317
5318 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_block_info(
5319 P_TIME_BUILDING_BLOCK_ID => l_detail.tbb_det_id
5320 ,P_TYPE => l_detail.tbb_det_type
5321 ,P_MEASURE => l_detail.tbb_det_measure
5322 ,P_UNIT_OF_MEASURE => l_detail.tbb_det_unit_of_measure
5323 ,P_START_TIME => l_detail.tbb_det_start_time
5324 ,P_STOP_TIME => l_detail.tbb_det_stop_time
5325 ,P_PARENT_BUILDING_BLOCK_ID => l_detail.tbb_day_id
5326 ,P_PARENT_IS_NEW => 'N'
5327 ,P_SCOPE => l_detail.tbb_det_scope
5328 ,P_OBJECT_VERSION_NUMBER => l_detail.tbb_det_ovn
5329 ,P_APPROVAL_STATUS => l_detail.tbb_det_approval_status
5330 ,P_RESOURCE_ID => l_detail.tbb_det_resource_id
5331 ,P_RESOURCE_TYPE => l_detail.tbb_det_resource_type
5332 ,P_APPROVAL_STYLE_ID => l_detail.tbb_det_approval_style
5333 ,P_DATE_FROM => l_detail.tbb_det_date_from
5334 ,P_DATE_TO => p_effective_date
5335 ,P_COMMENT_TEXT => l_detail.tbb_det_comment_text
5336 ,P_PARENT_BUILDING_BLOCK_OVN => l_detail.tbb_day_ovn+1
5337 ,P_NEW => 'N'
5338 ,P_CHANGED => 'Y'
5339 );
5340
5341 for l_detail_attribute in get_attributes(l_detail.tbb_det_id, l_detail.tbb_det_ovn) loop
5342
5343 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_attribute_info(
5344 p_TIME_ATTRIBUTE_ID => l_detail_attribute.ta_id
5345 ,p_BUILDING_BLOCK_ID => l_detail.tbb_det_id
5346 ,p_BLD_BLK_INFO_TYPE => l_detail_attribute.ta_BLD_BLK_INFO_TYPE
5347 ,p_ATTRIBUTE_CATEGORY => l_detail_attribute.ta_ATTRIBUTE_CATEGORY
5348 ,p_ATTRIBUTE1 => l_detail_attribute.ta_ATTRIBUTE1
5349 ,p_ATTRIBUTE2 => l_detail_attribute.ta_ATTRIBUTE2
5350 ,p_ATTRIBUTE3 => l_detail_attribute.ta_ATTRIBUTE3
5351 ,p_ATTRIBUTE4 => l_detail_attribute.ta_ATTRIBUTE4
5352 ,p_ATTRIBUTE5 => l_detail_attribute.ta_ATTRIBUTE5
5353 ,p_ATTRIBUTE6 => l_detail_attribute.ta_ATTRIBUTE6
5354 ,p_ATTRIBUTE7 => l_detail_attribute.ta_ATTRIBUTE7
5355 ,p_ATTRIBUTE8 => l_detail_attribute.ta_ATTRIBUTE8
5356 ,p_ATTRIBUTE9 => l_detail_attribute.ta_ATTRIBUTE9
5357 ,p_ATTRIBUTE10 => l_detail_attribute.ta_ATTRIBUTE10
5358 ,p_ATTRIBUTE11 => l_detail_attribute.ta_ATTRIBUTE11
5359 ,p_ATTRIBUTE12 => l_detail_attribute.ta_ATTRIBUTE12
5360 ,p_ATTRIBUTE13 => l_detail_attribute.ta_ATTRIBUTE13
5361 ,p_ATTRIBUTE14 => l_detail_attribute.ta_ATTRIBUTE14
5362 ,p_ATTRIBUTE15 => l_detail_attribute.ta_ATTRIBUTE15
5363 ,p_ATTRIBUTE16 => l_detail_attribute.ta_ATTRIBUTE16
5364 ,p_ATTRIBUTE17 => l_detail_attribute.ta_ATTRIBUTE17
5365 ,p_ATTRIBUTE18 => l_detail_attribute.ta_ATTRIBUTE18
5366 ,p_ATTRIBUTE19 => l_detail_attribute.ta_ATTRIBUTE19
5367 ,p_ATTRIBUTE20 => l_detail_attribute.ta_ATTRIBUTE20
5368 ,p_ATTRIBUTE21 => l_detail_attribute.ta_ATTRIBUTE21
5369 ,p_ATTRIBUTE22 => l_detail_attribute.ta_ATTRIBUTE22
5370 ,p_ATTRIBUTE23 => l_detail_attribute.ta_ATTRIBUTE23
5371 ,p_ATTRIBUTE24 => l_detail_attribute.ta_ATTRIBUTE24
5372 ,p_ATTRIBUTE25 => l_detail_attribute.ta_ATTRIBUTE25
5373 ,p_ATTRIBUTE26 => l_detail_attribute.ta_ATTRIBUTE26
5374 ,p_ATTRIBUTE27 => l_detail_attribute.ta_ATTRIBUTE27
5375 ,p_ATTRIBUTE28 => l_detail_attribute.ta_ATTRIBUTE28
5376 ,p_ATTRIBUTE29 => l_detail_attribute.ta_ATTRIBUTE29
5377 ,p_ATTRIBUTE30 => l_detail_attribute.ta_ATTRIBUTE30
5378 ,p_BLD_BLK_INFO_TYPE_ID => l_detail_attribute.ta_info_type
5379 ,p_OBJECT_VERSION_NUMBER => l_detail_attribute.ta_ovn
5380 ,p_NEW => 'N'
5381 ,p_changed => 'N');
5382 end loop;
5383 end loop;
5384
5385 -- now for the DAYs
5386
5387 for l_day in get_day_building_blocks(l_timecard_block,l_timecard_ovn) loop
5388
5389 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_block_info(
5390 P_TIME_BUILDING_BLOCK_ID => l_day.tbb_day_id
5391 ,P_TYPE => l_day.tbb_day_type
5392 ,P_MEASURE => l_day.tbb_day_measure
5393 ,P_UNIT_OF_MEASURE => l_day.tbb_day_unit_of_measure
5394 ,P_START_TIME => l_day.tbb_day_start_time
5395 ,P_STOP_TIME => l_day.tbb_day_stop_time
5396 ,P_PARENT_BUILDING_BLOCK_ID => l_timecard_block
5397 ,P_PARENT_IS_NEW => 'N'
5398 ,P_SCOPE => l_day.tbb_day_scope
5399 ,P_OBJECT_VERSION_NUMBER => l_day.tbb_day_ovn
5400 ,P_APPROVAL_STATUS => l_day.tbb_day_approval_status
5401 ,P_RESOURCE_ID => l_day.tbb_day_resource_id
5402 ,P_RESOURCE_TYPE => l_day.tbb_day_resource_type
5403 ,P_APPROVAL_STYLE_ID => l_day.tbb_day_approval_style
5404 ,P_DATE_FROM => l_day.tbb_day_date_from
5405 --,P_DATE_TO => l_day.tbb_day_date_to
5406 ,P_DATE_TO => p_effective_date
5407 ,P_COMMENT_TEXT => l_day.tbb_day_comment_text
5408 ,P_PARENT_BUILDING_BLOCK_OVN => l_timecard_ovn+1
5409 ,P_NEW => 'N'
5410 ,P_CHANGED => 'Y'
5411 );
5412
5413 for l_day_attribute in get_attributes(l_day.tbb_day_id, l_day.tbb_day_ovn) loop
5414
5415 HXC_SELF_SERVICE_TIME_DEPOSIT.deposit_attribute_info(
5416 p_TIME_ATTRIBUTE_ID => l_day_attribute.ta_id
5417 ,p_BUILDING_BLOCK_ID => l_day.tbb_day_id
5418 ,p_BLD_BLK_INFO_TYPE => l_day_attribute.ta_BLD_BLK_INFO_TYPE
5419 ,p_ATTRIBUTE_CATEGORY => l_day_attribute.ta_ATTRIBUTE_CATEGORY
5420 ,p_ATTRIBUTE1 => l_day_attribute.ta_ATTRIBUTE1
5421 ,p_ATTRIBUTE2 => l_day_attribute.ta_ATTRIBUTE2
5422 ,p_ATTRIBUTE3 => l_day_attribute.ta_ATTRIBUTE3
5423 ,p_ATTRIBUTE4 => l_day_attribute.ta_ATTRIBUTE4
5424 ,p_ATTRIBUTE5 => l_day_attribute.ta_ATTRIBUTE5
5425 ,p_ATTRIBUTE6 => l_day_attribute.ta_ATTRIBUTE6
5426 ,p_ATTRIBUTE7 => l_day_attribute.ta_ATTRIBUTE7
5427 ,p_ATTRIBUTE8 => l_day_attribute.ta_ATTRIBUTE8
5428 ,p_ATTRIBUTE9 => l_day_attribute.ta_ATTRIBUTE9
5429 ,p_ATTRIBUTE10 => l_day_attribute.ta_ATTRIBUTE10
5430 ,p_ATTRIBUTE11 => l_day_attribute.ta_ATTRIBUTE11
5431 ,p_ATTRIBUTE12 => l_day_attribute.ta_ATTRIBUTE12
5432 ,p_ATTRIBUTE13 => l_day_attribute.ta_ATTRIBUTE13
5433 ,p_ATTRIBUTE14 => l_day_attribute.ta_ATTRIBUTE14
5434 ,p_ATTRIBUTE15 => l_day_attribute.ta_ATTRIBUTE15
5435 ,p_ATTRIBUTE16 => l_day_attribute.ta_ATTRIBUTE16
5436 ,p_ATTRIBUTE17 => l_day_attribute.ta_ATTRIBUTE17
5437 ,p_ATTRIBUTE18 => l_day_attribute.ta_ATTRIBUTE18
5438 ,p_ATTRIBUTE19 => l_day_attribute.ta_ATTRIBUTE19
5439 ,p_ATTRIBUTE20 => l_day_attribute.ta_ATTRIBUTE20
5440 ,p_ATTRIBUTE21 => l_day_attribute.ta_ATTRIBUTE21
5441 ,p_ATTRIBUTE22 => l_day_attribute.ta_ATTRIBUTE22
5442 ,p_ATTRIBUTE23 => l_day_attribute.ta_ATTRIBUTE23
5443 ,p_ATTRIBUTE24 => l_day_attribute.ta_ATTRIBUTE24
5444 ,p_ATTRIBUTE25 => l_day_attribute.ta_ATTRIBUTE25
5445 ,p_ATTRIBUTE26 => l_day_attribute.ta_ATTRIBUTE26
5446 ,p_ATTRIBUTE27 => l_day_attribute.ta_ATTRIBUTE27
5447 ,p_ATTRIBUTE28 => l_day_attribute.ta_ATTRIBUTE28
5448 ,p_ATTRIBUTE29 => l_day_attribute.ta_ATTRIBUTE29
5449 ,p_ATTRIBUTE30 => l_day_attribute.ta_ATTRIBUTE30
5450 ,p_BLD_BLK_INFO_TYPE_ID => l_day_attribute.ta_info_type
5451 ,p_OBJECT_VERSION_NUMBER => l_day_attribute.ta_ovn
5452 ,p_NEW => 'N'
5453 ,p_changed => 'N');
5454
5455 end loop;
5456 end loop;
5457
5458 -- now deposit the delete timecard
5459
5460 HXC_SELF_SERVICE_TIME_DEPOSIT.DEPOSIT_BLOCKS
5461 (p_timecard_id => l_returned_timecard_id
5462 ,p_timecard_ovn => l_returned_timecard_ovn
5463 ,p_mode => p_mode
5464 ,p_deposit_process => p_deposit_process
5465 ,p_retrieval_process => p_retrieval_process
5466 );
5467
5468 exception
5469 when e_no_such_timecard then
5470 raise;
5471 when e_not_timecard_scope then
5472 raise;
5473 when others then
5474 raise;
5475
5476 end delete_timecard;
5477
5478 ----
5479 -- procedures supporting argument passing where dynamic sql is required
5480 ----
5481
5482 function get_building_blocks return timecard_info is
5483 begin
5484 return g_timecard;
5485 end get_building_blocks;
5486
5487 function get_block_attributes return building_block_attribute_info is
5488 begin
5489 return g_attributes;
5490 end get_block_attributes;
5491
5492 function get_app_attributes return app_attributes_info is
5493 begin
5494 return g_app_attributes;
5495 end get_app_attributes;
5496
5497 function get_messages return message_table is
5498 begin
5499 return g_messages;
5500 end get_messages;
5501
5502
5503 procedure get_app_hook_params(
5504 p_building_blocks OUT NOCOPY timecard_info,
5505 p_app_attributes OUT NOCOPY app_attributes_info,
5506 p_messages OUT NOCOPY message_table)
5507 is
5508 begin
5509
5510 p_building_blocks := g_timecard;
5511 p_app_attributes := g_app_attributes;
5512 p_messages := g_messages;
5513
5514 end;
5515
5516
5517 procedure set_app_hook_params(
5518 p_building_blocks IN timecard_info,
5519 p_app_attributes IN app_attributes_info,
5520 p_messages IN message_table)
5521 is
5522 begin
5523
5524 -- only allow blocks and attributes to be updated if in an update phase
5525 if(g_update_phase) then --AI2.5
5526 g_timecard := p_building_blocks;
5527 g_app_attributes := p_app_attributes;
5528 end if; --AI2.5
5529
5530 g_messages := p_messages;
5531
5532 end;
5533
5534
5535
5536 procedure set_global_table(
5537 p_building_blocks IN timecard_info,
5538 p_attributes IN building_block_attribute_info)
5539 is
5540 begin
5541
5542 g_timecard := p_building_blocks;
5543 g_attributes := p_attributes;
5544
5545 end;
5546
5547
5548 --AI2.5
5549 PROCEDURE set_update_phase(p_mode in BOOLEAN)
5550 IS
5551 BEGIN
5552
5553 g_update_phase := p_mode;
5554
5555 END set_update_phase;
5556 --AI2.5
5557
5558
5559 --
5560 PROCEDURE get_timecard_tables (
5561 p_timecard_id IN NUMBER,
5562 p_timecard_ovn IN NUMBER,
5563 p_timecard_blocks OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
5564 p_timecard_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
5565 p_time_recipient_id IN NUMBER
5566 )
5567 IS
5568 CURSOR csr_time_recipients (p_time_recipient_id IN NUMBER)
5569 IS
5570 SELECT tr.application_retrieval_function
5571 FROM hxc_time_recipients tr
5572 WHERE tr.time_recipient_id = p_time_recipient_id;
5573
5574 l_retrieval_process_id NUMBER := NULL;
5575 l_retrieval_function VARCHAR2 (2000);
5576 BEGIN
5577 OPEN csr_time_recipients (p_time_recipient_id);
5578 FETCH csr_time_recipients INTO l_retrieval_function;
5579 CLOSE csr_time_recipients;
5580
5581 IF l_retrieval_function IS NOT NULL
5582 THEN
5583 IF code_chk (l_retrieval_function)
5584 THEN
5585 find_app_deposit_process (
5586 p_time_recipient_id=> p_time_recipient_id,
5587 p_app_function=> l_retrieval_function,
5588 p_retrieval_process_id=> l_retrieval_process_id
5589 );
5590 END IF;
5591 END IF;
5592
5593 get_timecard_tables (
5594 p_timecard_id=> p_timecard_id,
5595 p_timecard_ovn=> p_timecard_ovn,
5596 p_timecard_blocks=> p_timecard_blocks,
5597 p_timecard_app_attributes=> p_timecard_app_attributes,
5598 p_deposit_process_id=> NULL,
5599 p_retrieval_process_id=> l_retrieval_process_id
5600 );
5601 END;
5602
5603 PROCEDURE get_timecard_tables (
5604 p_timecard_id IN NUMBER,
5605 p_timecard_ovn IN NUMBER,
5606 p_timecard_blocks OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
5607 p_timecard_app_attributes OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
5608 p_deposit_process_id IN NUMBER,
5609 p_retrieval_process_id IN NUMBER
5610 )
5611 IS
5612 CURSOR c_time_building_blocks (
5613 p_timecard_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
5614 p_timecard_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5615 )
5616 IS
5617 SELECT htbb.time_building_block_id, htbb.TYPE, htbb.measure,
5618 htbb.unit_of_measure, htbb.start_time, htbb.stop_time,
5619 htbb.parent_building_block_id, 'N' parent_is_new, htbb.SCOPE,
5620 htbb.object_version_number, htbb.approval_status,
5621 htbb.resource_id, htbb.resource_type, htbb.approval_style_id,
5622 htbb.date_from, htbb.date_to, htbb.comment_text,
5623 htbb.parent_building_block_ovn, 'N' NEW, 'N' changed, 'N' process,
5624 htbb.application_set_id, htbb.translation_display_key
5625 FROM hxc_time_building_blocks htbb
5626 WHERE SYSDATE BETWEEN htbb.date_from AND htbb.date_to
5627 START WITH ( htbb.time_building_block_id = p_timecard_id
5628 AND htbb.object_version_number = p_timecard_ovn
5629 )
5630 CONNECT BY PRIOR htbb.time_building_block_id =
5631 htbb.parent_building_block_id
5632 AND PRIOR htbb.object_version_number =
5633 htbb.parent_building_block_ovn
5634 ORDER BY htbb.time_building_block_id ASC;
5635
5636 CURSOR c_block_attributes (
5637 p_build_block_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
5638 p_build_block_ovn IN hxc_time_building_blocks.object_version_number%TYPE
5639 )
5640 IS
5641 SELECT hta.time_attribute_id, htau.time_building_block_id,
5642 hbbit.bld_blk_info_type, hta.attribute_category, hta.attribute1,
5643 hta.attribute2, hta.attribute3, hta.attribute4, hta.attribute5,
5644 hta.attribute6, hta.attribute7, hta.attribute8, hta.attribute9,
5645 hta.attribute10, hta.attribute11, hta.attribute12,
5646 hta.attribute13, hta.attribute14, hta.attribute15,
5647 hta.attribute16, hta.attribute17, hta.attribute18,
5648 hta.attribute19, hta.attribute20, hta.attribute21,
5649 hta.attribute22, hta.attribute23, hta.attribute24,
5650 hta.attribute25, hta.attribute26, hta.attribute27,
5651 hta.attribute28, hta.attribute29, hta.attribute30,
5652 hta.bld_blk_info_type_id, hta.object_version_number, 'N' NEW,
5653 'N' changed, 'N' process
5654 FROM hxc_time_attributes hta,
5655 hxc_time_attribute_usages htau,
5656 hxc_bld_blk_info_types hbbit
5657 WHERE htau.time_building_block_id = p_build_block_id
5658 AND htau.time_building_block_ovn = p_build_block_ovn
5659 AND htau.time_attribute_id = hta.time_attribute_id
5660 AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id;
5661
5662 l_block_index NUMBER := 1;
5663 l_attribute_index NUMBER := 1;
5664 l_retrieval_process_id NUMBER := NULL;
5665 l_retrieval_function VARCHAR2 (2000);
5666 BEGIN
5667 g_timecard.DELETE;
5668 g_attributes.DELETE;
5669 OPEN c_time_building_blocks (
5670 p_timecard_id=> p_timecard_id,
5671 p_timecard_ovn=> p_timecard_ovn
5672 );
5673
5674 LOOP
5675 FETCH c_time_building_blocks INTO g_timecard (l_block_index);
5676 EXIT WHEN c_time_building_blocks%NOTFOUND;
5677 -- get the attributes associated to this block
5678 OPEN c_block_attributes (
5679 p_build_block_id=> g_timecard (l_block_index).time_building_block_id,
5680 p_build_block_ovn=> g_timecard (l_block_index).object_version_number
5681 );
5682
5683 LOOP
5684 FETCH c_block_attributes INTO g_attributes (l_attribute_index);
5685 EXIT WHEN c_block_attributes%NOTFOUND;
5686 l_attribute_index := l_attribute_index
5687 + 1;
5688 END LOOP;
5689
5690 CLOSE c_block_attributes;
5691 l_block_index := l_block_index
5692 + 1;
5693 END LOOP;
5694
5695 CLOSE c_time_building_blocks;
5696 g_app_attributes.DELETE;
5697
5698 IF (p_retrieval_process_id IS NOT NULL)
5699 THEN
5700 g_app_attributes :=
5701 build_application_attributes (
5702 p_retrieval_process_id=> p_retrieval_process_id,
5703 p_deposit_process_id=> NULL,
5704 p_for_time_attributes=> FALSE
5705 );
5706 ELSIF (p_deposit_process_id IS NOT NULL)
5707 THEN
5708 g_app_attributes :=
5709 build_application_attributes (
5710 p_retrieval_process_id=> NULL,
5711 p_deposit_process_id=> p_deposit_process_id,
5712 p_for_time_attributes=> TRUE
5713 );
5714 ELSE -- ERROR
5715 NULL;
5716 END IF;
5717
5718 p_timecard_blocks := g_timecard;
5719 p_timecard_app_attributes := g_app_attributes;
5720
5721 IF p_timecard_blocks.COUNT = 0
5722 THEN
5723 --
5724 hr_utility.set_message (809, 'HXC_APR_NO_TIMECARD_INFO');
5725 hr_utility.raise_error;
5726 END IF;
5727 END get_timecard_tables;
5728
5729
5730 PROCEDURE set_g_attributes ( p_attributes building_block_attribute_info ) IS
5731
5732 BEGIN
5733
5734 g_attributes := p_attributes;
5735
5736 END set_g_attributes;
5737 --
5738 end hxc_self_service_time_deposit;