[Home] [Help]
PACKAGE BODY: APPS.HXT_HXC_RETRIEVAL_PROCESS
Source
1 PACKAGE BODY hxt_hxc_retrieval_process AS
2 /* $Header: hxthcrtp.pkb 120.20.12020000.3 2013/04/04 15:14:13 asrajago ship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE t_time_recipient IS TABLE OF VARCHAR2 (1)
6 INDEX BY BINARY_INTEGER;
7
8 g_status VARCHAR2 (30);
9 g_exception_description VARCHAR2 (2000);
10 e_record_error EXCEPTION;
11 e_no_details EXCEPTION;
12 e_error EXCEPTION;
13
14 --------------------------- syncronize_deletes_in_otlr ----------------------
15 PROCEDURE synchronize_deletes_in_otlr (
16 p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info,
17 p_time_att_info IN hxc_self_service_time_deposit.app_attributes_info,
18 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
19 p_timecard_source IN VARCHAR -- added for 5137310
20 )
21 IS
22 l_time_building_blocks hxc_self_service_time_deposit.timecard_info
23 := p_time_building_blocks;
24 l_time_att_info hxc_self_service_time_deposit.app_attributes_info
25 := p_time_att_info;
26
27 CURSOR c_get_sum_id (day_bb_id NUMBER)
28 IS
29 SELECT ID, tim_id
30 FROM hxt_sum_hours_worked_f
31 WHERE time_building_block_id = day_bb_id;
32
33 -- Cursor to get those rows(from HXC tables) that have been deleted in OTL
34 -- but this delete not yet been reflected in the OTLR tables because of the
35 -- 'Transfer Time from OTL to BEE' process not run yet.
36
37 -- Bug 8672797
38 -- Forced hints to get rid of perf issue.
39 CURSOR c_get_day_bb_id (l_parent_start_time DATE, l_resource_id NUMBER)
40 IS
41 SELECT tbb.time_building_block_id
42 FROM hxc_time_building_blocks tbb
43 WHERE tbb.parent_building_block_id IN (
44 SELECT /*+ INDEX( tbb1 HXC_TIME_BUILDING_BLOCKS_FK2)*/
45 time_building_block_id
46 FROM hxc_time_building_blocks tbb1
47 WHERE tbb1.resource_id = l_resource_id
48 AND tbb1.resource_type = 'PERSON'
49 AND tbb1.start_time = l_parent_start_time
50 AND tbb1.SCOPE = 'DAY')
51 AND tbb.object_version_number =
52 (SELECT /*+ NO_UNNEST */
53 MAX (dyovn.object_version_number)
54 FROM hxc_time_building_blocks dyovn
55 WHERE dyovn.time_building_block_id =
56 tbb.time_building_block_id
57 AND dyovn.date_to <> hr_general.end_of_time)
58 AND tbb.date_to <> hr_general.end_of_time
59 AND EXISTS (
60 SELECT /*+ LEADING(txd)
61 INDEX(txd HXC_TRANSACTION_DETAILS_FK1)
62 INDEX(tx HXC_TRANSACTIONS_PK) */
63 'x'
64 FROM hxc_transaction_details txd, hxc_transactions tx
65 WHERE tx.transaction_process_id = -1
66 AND tx.TYPE = 'RETRIEVAL'
67 AND tx.status = 'SUCCESS'
68 AND tx.transaction_id = txd.transaction_id
69 AND txd.status = 'SUCCESS'
70 AND txd.time_building_block_id =
71 tbb.time_building_block_id
72 AND txd.time_building_block_ovn <=
73 tbb.object_version_number);
74
75 /*AND NOT EXISTS
76 (select 'x'
77 FROM hxc_transaction_details txd1
78 ,hxc_transactions tx1
79 WHERE tx1.transaction_process_id = -1
80 AND tx1.type = 'RETRIEVAL'
81 AND tx1.status = 'SUCCESS'
82 AND tx1.transaction_id = txd1.transaction_id
83 AND txd1.status = 'SUCCESS'
84 AND txd1.time_building_block_id = tbb.time_building_block_id
85 AND txd1.time_building_block_ovn = tbb.object_version_number
86 );*/
87 l_bb_id NUMBER (15);
88 l_ovn NUMBER (9);
89 l_check_bb_id NUMBER (15);
90 l_check_bb_ovn NUMBER (9);
91 l_parent_bb_ovn NUMBER (9);
92 l_type VARCHAR2 (30);
93 l_measure hxc_time_building_blocks.measure%TYPE;
94 l_start_time DATE;
95 l_parent_start_time DATE;
96 l_stop_time DATE;
97 l_date_to DATE;
98 l_parent_bb_id NUMBER (15);
99 l_scope VARCHAR2 (30);
100 l_resource_id NUMBER (15);
101 l_resource_type VARCHAR2 (30);
102 l_comment_text VARCHAR2 (2000);
103 l_new VARCHAR2 (30);
104 l_cnt NUMBER;
105 j NUMBER;
106 l_valid VARCHAR2 (1) := 'N';
107 l_day_bb_id NUMBER (15);
108 l_time_summary_id NUMBER (15);
109 l_batch_status VARCHAR2 (30);
110 l_batch_id NUMBER (15);
111 l_proc VARCHAR2 (250);
112 l_dt_update_mode VARCHAR2 (30);
113 l_otm_error VARCHAR2 (2000);
114 o_return_code NUMBER (15);
115 e_error EXCEPTION;
116 l_session_id NUMBER;
117 l_tim_id NUMBER (15);
118 l_measure_count NUMBER;
119 BEGIN
120 g_debug := hr_utility.debug_enabled;
121 --12567943 On clicking show accrual session's effective date is set
122 -- based on preference evaluation date which hinders validation
123 hr_kflex_utility.unset_session_date (userenv('sessionid'));
124 hr_kflex_utility.set_session_date (p_effective_date => SYSDATE,
125 p_session_id => l_session_id
126 );
127
128 -- Verify for each valid detail block if any of the detail blocks for the
129 -- day were deleted in HXC and this delete not yet reflected in OTLR.
130 -- This information is required when the details records are deleted
131 -- in OTL and the transfer process hasn't been run before entering a
132 -- new row for the same day. Since the transfer process hasn't been run yet,
133 -- it results in an incorrect OTLR validation taking place between the new
134 -- rows entered in OTL and the old rows in OTLR that have already been
135 -- deleted in OTL.
136 -- In order to fix this , we need to find out, when entering new rows in OTL,
137 -- whether any rows related to this day have been deleted or not.
138 -- If yes, then delete the corresponding row in HXT tables, so that the OTLR
139 -- validation takes place against the latest changes done in OTL and
140 -- reflected in OTLR.
141 l_cnt := l_time_building_blocks.FIRST;
142
143 LOOP
144 EXIT WHEN NOT l_time_building_blocks.EXISTS (l_cnt);
145
146 --
147 IF g_debug
148 THEN
149 l_proc := 'hxt_hxc_retrieval_process.synchronize_deletes_in_otlr';
150 hr_utility.set_location (l_proc, 10);
151 hr_utility.TRACE
152 ('*********** NEW TIME BUILDING BLOCK ************');
153 END IF;
154
155 --
156 l_bb_id := l_time_building_blocks (l_cnt).time_building_block_id;
157 l_ovn := l_time_building_blocks (l_cnt).object_version_number;
158 l_type := l_time_building_blocks (l_cnt).TYPE;
159 l_measure := l_time_building_blocks (l_cnt).measure;
160 l_start_time := l_time_building_blocks (l_cnt).start_time;
161 l_stop_time := l_time_building_blocks (l_cnt).stop_time;
162 l_parent_bb_id :=
163 l_time_building_blocks (l_cnt).parent_building_block_id;
164 l_parent_bb_ovn :=
165 l_time_building_blocks (l_cnt).parent_building_block_ovn;
166 l_scope := l_time_building_blocks (l_cnt).SCOPE;
167 l_resource_id := l_time_building_blocks (l_cnt).resource_id;
168 l_resource_type := l_time_building_blocks (l_cnt).resource_type;
169 l_comment_text := l_time_building_blocks (l_cnt).comment_text;
170 l_new := l_time_building_blocks (l_cnt).NEW;
171 l_date_to := l_time_building_blocks (l_cnt).date_to;
172
173 --
174 IF g_debug
175 THEN
176 hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
177 hr_utility.TRACE ('Type is : ' || l_type);
178 hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
179 hr_utility.TRACE ( 'l_start_time is '
180 || TO_CHAR (l_start_time,
181 'DD-MON-YYYY HH:MI:SS')
182 );
183 hr_utility.TRACE ( 'l_stop_time is '
184 || TO_CHAR (l_stop_time, 'DD-MON-YYYY HH:MI:SS')
185 );
186 hr_utility.TRACE ('l_scope is ' || l_scope);
187 hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
188 hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
189 --
190 hr_utility.TRACE ( 'UOM is : '
191 || l_time_building_blocks (l_cnt).unit_of_measure
192 );
193 hr_utility.TRACE
194 ( 'Parent BB ID is : '
195 || TO_CHAR
196 (l_time_building_blocks (l_cnt).parent_building_block_id
197 )
198 );
199 hr_utility.TRACE ( 'PARENT_IS_NEW is : '
200 || l_time_building_blocks (l_cnt).parent_is_new
201 );
202 hr_utility.TRACE
203 ( 'OVN is : '
204 || TO_CHAR
205 (l_time_building_blocks (l_cnt).object_version_number
206 )
207 );
208 hr_utility.TRACE ( 'APPROVAL_STATUS is : '
209 || l_time_building_blocks (l_cnt).approval_status
210 );
211 hr_utility.TRACE
212 ( 'DATE_FROM is : '
213 || TO_CHAR
214 (l_time_building_blocks (l_cnt).date_from,
215 'DD-MON-YYYY'
216 )
217 );
218 hr_utility.TRACE ( 'DATE_TO is : '
219 || TO_CHAR
220 (l_time_building_blocks (l_cnt).date_to,
221 'DD-MON-YYYY'
222 )
223 );
224 hr_utility.TRACE ('NEW is : '
225 || l_time_building_blocks (l_cnt).NEW
226 );
227 --
228 hr_utility.set_location (l_proc, 20);
229 END IF;
230
231 --
232 IF ( ( (l_type = 'MEASURE' AND l_measure IS NOT NULL)
233 OR ( l_type = 'RANGE'
234 AND l_start_time IS NOT NULL
235 AND l_stop_time IS NOT NULL
236 )
237 )
238 AND ( l_date_to = hr_general.end_of_time
239 OR (l_date_to <> hr_general.end_of_time AND l_new = 'N'
240 )
241 )
242 OR ( ( (l_type = 'MEASURE' AND l_measure IS NULL)
243 OR ( l_type = 'RANGE'
244 AND l_start_time IS NULL
245 AND l_stop_time IS NULL
246 )
247 )
248 AND l_date_to <> hr_general.end_of_time
249 AND l_new = 'N'
250 )
251 )
252 THEN
253 --
254 l_valid := 'Y';
255 ELSE
256 l_valid := 'N';
257 END IF;
258
259 --
260 -- We need to take into consideration, the entire Timecard and not just
261 -- the Detail Blocks, for bug 4676079. Here what happens is that the user
262 -- deletes(i.e., clears up an entry for the day while updating the TC)
263 -- and submits the TC for Approval.
264 -- Now when the Approver clicks on Detail button to review the details
265 -- of the Timecard, at that point the TC structure sent to OTLR code
266 -- doesn't include an entry for the detail record that was cleared off
267 -- by the employee. As such syncronize_deletes_in_otlr wasn't able to
268 -- syncronize the data for the day. That's why we need to consider the
269 -- entire TC structure when performing thsi task.
270 IF l_valid = 'Y'
271 THEN
272 IF l_scope = 'DETAIL'
273 THEN
274 IF g_debug
275 THEN
276 hr_utility.set_location (l_proc, 50);
277 hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
278 hr_utility.TRACE ('l_parent_bb_ovn :' || l_parent_bb_ovn);
279 END IF;
280
281 j := l_time_building_blocks.FIRST;
282
283 LOOP
284 IF g_debug
285 THEN
286 hr_utility.set_location (l_proc, 51);
287 END IF;
288
289 EXIT WHEN NOT l_time_building_blocks.EXISTS (j);
290
291 IF g_debug
292 THEN
293 hr_utility.set_location (l_proc, 52);
294 END IF;
295
296 --
297 l_check_bb_id :=
298 l_time_building_blocks (j).time_building_block_id;
299 l_check_bb_ovn :=
300 l_time_building_blocks (j).object_version_number;
301
302 IF g_debug
303 THEN
304 hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
305 hr_utility.TRACE ('l_parent_bb_ovn:' || l_parent_bb_ovn);
306 END IF;
307
308 IF l_check_bb_id = l_parent_bb_id
309 AND l_check_bb_ovn = l_parent_bb_ovn
310 THEN
311 IF g_debug
312 THEN
313 hr_utility.set_location (l_proc, 53);
314 END IF;
315
316 l_parent_start_time :=
317 l_time_building_blocks (j).start_time;
318 EXIT;
319 ELSE
320 IF g_debug
321 THEN
322 hr_utility.set_location (l_proc, 54);
323 END IF;
324
325 j := l_time_building_blocks.NEXT (j);
326 END IF;
327
328 IF g_debug
329 THEN
330 hr_utility.set_location (l_proc, 55);
331 END IF;
332 END LOOP;
333 ELSE
334 l_parent_start_time := l_start_time;
335 END IF;
336
337 /* select start_time into l_parent_start_time
338 from hxc_time_building_blocks
339 where time_building_block_id = l_parent_bb_id
340 and object_version_number = l_parent_bb_ovn; */
341 IF g_debug
342 THEN
343 hr_utility.TRACE ('l_parent_start_time:' || l_parent_start_time
344 );
345 hr_utility.TRACE ('l_resource_id:' || l_resource_id);
346 END IF;
347
348 -- Find out if any detail records(siblings) for this day
349 -- deleted or updated in OTL and not yet transferred to OTLR.
350 OPEN c_get_day_bb_id (l_parent_start_time, l_resource_id);
351
352 LOOP
353 FETCH c_get_day_bb_id
354 INTO l_day_bb_id;
355
356 EXIT WHEN c_get_day_bb_id%NOTFOUND;
357
358 IF g_debug
359 THEN
360 hr_utility.TRACE ('l_day_bb_id:' || l_day_bb_id);
361 END IF;
362
363 -- If such records found in OTL, then find the corresponding
364 -- summary record in OTLR, so that the summary and detail rows in HXT
365 -- tables can also be deleted to reflect the latest changes in OTL.
366 IF g_debug
367 THEN
368 hr_utility.set_location (l_proc, 60);
369 END IF;
370
371 l_tim_id := NULL;
372
373 OPEN c_get_sum_id (l_day_bb_id);
374
375 FETCH c_get_sum_id
376 INTO l_time_summary_id, l_tim_id;
377
378 IF g_debug
379 THEN
380 hr_utility.set_location (l_proc, 70);
381 hr_utility.TRACE ('l_time_summary_id:' || l_time_summary_id);
382 END IF;
383
384 -- If corresponding summary record found then delete its details
385 -- and the summary record itself from HXT tables.
386 IF c_get_sum_id%FOUND
387 THEN
388 IF g_debug
389 THEN
390 hr_utility.set_location (l_proc, 80);
391 END IF;
392
393 hxt_td_util.retro_restrict_edit
394 (p_tim_id => l_tim_id,
395 p_session_date => SYSDATE,
396 o_dt_update_mod => l_dt_update_mode,
397 o_error_message => l_otm_error,
398 o_return_code => o_return_code,
399 p_parent_id => l_time_summary_id
400 );
401
402 -- p_timecard_source <> 'Timecard Review' - added for 5137310
403 IF (p_timecard_source <> 'Timecard Review' AND o_return_code = 1) OR l_otm_error IS NOT NULL
404 THEN
405 hxc_time_entry_rules_utils_pkg.add_error_to_table
406 (p_message_table => p_messages,
407 p_message_name => 'HXT_TC_CANNOT_BE_CHANGED_TODAY',
408 p_message_token => NULL,
409 p_message_level => 'ERROR',
410 p_message_field => NULL,
411 p_application_short_name => 'HXT',
412 p_timecard_bb_id => NULL,
413 p_time_attribute_id => NULL,
414 p_timecard_bb_ovn => NULL,
415 p_time_attribute_ovn => NULL
416 );
417 RETURN;
418 END IF;
419
420 -- added for 5137310
421 IF(l_dt_update_mode IS NULL)
422 THEN
423 l_dt_update_mode := 'UPDATE';
424 END IF;
425
426
427 -- If this is a Retro change(=> l_dt_update_mode returned as 'UPDATE'
428 -- by retro_restrict_edit) then we dont need to synchronize the deletes in
429 -- otlr since now we raise an error message to let the user know that he needs
430 -- to enter zero hours instead of deleting the row.
431 -- This error message is raised only for Retro changes therefore
432 -- synchronize deletes procedure will still be required for
433 -- non Retro timecard changes.
434 --
435
436 --Bug 4890370 Fix Start
437 IF l_dt_update_mode = 'UPDATE'
438 THEN
439 -- Check if user has entered zero hours while deleting a TC row in which case
440 -- we need to synchronize the deletes in OTLR. We also need to synchronize the deletes
441 -- in case user has replaced straight hours with start/stop time
442 BEGIN
443 SELECT count(*)
444 INTO l_measure_count
445 FROM hxc_time_building_blocks
446 WHERE time_building_block_id = l_day_bb_id
447 AND ((measure = 0) or (start_time is not null and stop_time is not null))
448 AND scope = 'DETAIL'
449 AND date_to = hr_general.end_of_time;
450 END;
451 END IF;
452 --Bug 4890370 Fix Ends
453
454 -- Begin Bug 4590163
455 IF (l_dt_update_mode = 'CORRECTION')
456 OR (l_dt_update_mode = 'UPDATE' AND l_measure_count <> 0) /*** 4890370 ***/
457 THEN
458 -- End Bug 4590163
459 --
460 -- Delete detail rows associated with summary row.
461 --
462 DELETE FROM hxt_det_hours_worked_f
463 WHERE parent_id = l_time_summary_id;
464
465 IF g_debug
466 THEN
467 hr_utility.set_location (l_proc, 90);
468 END IF;
469
470 --
471 -- Delete the summary row itself.
472 --
473 DELETE FROM hxt_sum_hours_worked_f
474 WHERE ID = l_time_summary_id;
475
476 IF g_debug
477 THEN
478 hr_utility.set_location (l_proc, 100);
479 END IF;
480
481 CLOSE c_get_sum_id;
482
483 EXIT;
484 END IF;
485 END IF;
486
487 CLOSE c_get_sum_id;
488
489 IF g_debug
490 THEN
491 hr_utility.set_location (l_proc, 110);
492 END IF;
493 END LOOP;
494
495 IF g_debug
496 THEN
497 hr_utility.set_location (l_proc, 110.5);
498 END IF;
499
500 CLOSE c_get_day_bb_id;
501
502 IF g_debug
503 THEN
504 hr_utility.set_location (l_proc, 120);
505 END IF;
506 END IF;
507
508 IF g_debug
509 THEN
510 hr_utility.set_location (l_proc, 130);
511 END IF;
512
513 l_cnt := l_time_building_blocks.NEXT (l_cnt);
514 END LOOP;
515
516
517 END;
518
519 --------------------------- otm_validate_process ----------------------------
520 PROCEDURE otm_validate_process (
521 p_operation IN VARCHAR2,
522 p_time_building_blocks IN OUT NOCOPY VARCHAR2,
523 p_time_attributes IN OUT NOCOPY VARCHAR2,
524 p_messages IN OUT NOCOPY VARCHAR2
525 )
526 IS
527 l_blocks hxc_self_service_time_deposit.timecard_info;
528 l_attributes hxc_self_service_time_deposit.app_attributes_info;
529 l_messages hxc_self_service_time_deposit.message_table;
530 l_proc VARCHAR2 (100);
531 BEGIN
532 g_debug := hr_utility.debug_enabled;
533
534 IF g_debug
535 THEN
536 l_proc := 'hxt_hxc_retrieval_process.OTM_VALIDATE_PROCESS';
537 hr_utility.set_location (l_proc, 10);
538 hr_utility.TRACE ( 'p_time_building_blocks is : '
539 || SUBSTR (p_time_building_blocks, 1, 2000)
540 );
541 END IF;
542
543 l_blocks :=
544 hxc_deposit_wrapper_utilities.string_to_blocks
545 (p_time_building_blocks);
546
547 IF g_debug
548 THEN
549 hr_utility.set_location (l_proc, 30);
550 END IF;
551
552 l_attributes :=
553 hxc_deposit_wrapper_utilities.string_to_attributes (p_time_attributes);
554
555 IF g_debug
556 THEN
557 hr_utility.set_location (l_proc, 40);
558 END IF;
559
560 l_messages :=
561 hxc_deposit_wrapper_utilities.string_to_messages (p_messages);
562 validate_timecard (p_operation => p_operation,
563 p_time_building_blocks => l_blocks,
564 p_time_attributes => l_attributes,
565 p_messages => l_messages
566 );
567 p_time_building_blocks :=
568 hxc_deposit_wrapper_utilities.blocks_to_string (l_blocks);
569 p_time_attributes :=
570 hxc_deposit_wrapper_utilities.attributes_to_string (l_attributes);
571 p_messages :=
572 hxc_deposit_wrapper_utilities.messages_to_string (l_messages);
573 END otm_validate_process;
574
575 --------------------------- validate_timecard ------------------------------
576 PROCEDURE validate_timecard (
577 p_operation IN VARCHAR2,
578 p_time_building_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
579 p_time_attributes IN OUT NOCOPY hxc_self_service_time_deposit.app_attributes_info,
580 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
581 )
582 IS
583 CURSOR get_timecard_id (p_tim_sum_id NUMBER)
584 IS
585 SELECT hshw.tim_id, ht.time_period_id
586 FROM hxt_sum_hours_worked hshw, hxt_timecards ht
587 WHERE hshw.ID = p_tim_sum_id AND hshw.tim_id = ht.ID;
588
589 CURSOR get_debug
590 IS
591 SELECT 'X'
592 FROM hxc_debug
593 WHERE process = 'otm_validate_timecard'
594 AND TRUNC (debug_date) <= SYSDATE;
595
596 -- local tables
597 TYPE t_tim_sum_id_tab IS TABLE OF NUMBER
598 INDEX BY BINARY_INTEGER;
599
600 l_field_name hxt_otc_retrieval_interface.t_field_name;
601 l_value hxt_otc_retrieval_interface.t_value;
602 l_context hxt_otc_retrieval_interface.t_field_name;
603 l_category hxt_otc_retrieval_interface.t_field_name;
604 l_segment hxt_otc_retrieval_interface.t_segment;
605 l_bb_id NUMBER (15);
606 l_ovn NUMBER (9);
607 l_type VARCHAR2 (30);
608 l_measure hxc_time_building_blocks.measure%TYPE;
609 l_start_time DATE;
610 l_stop_time DATE;
611 l_date_to DATE;
612 l_parent_bb_id NUMBER (15);
613 l_scope VARCHAR2 (30);
614 l_resource_id NUMBER (15);
615 l_resource_type VARCHAR2 (30);
616 l_comment_text VARCHAR2 (2000);
617 l_person_id NUMBER (9);
618 l_date_worked DATE;
619 l_effective_date DATE;
620 l_assignment_id NUMBER (9);
621 l_payroll_id NUMBER (9);
622 l_bg_id NUMBER (9);
623 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
624 l_otm_error VARCHAR2 (240) DEFAULT NULL;
625 l_oracle_error VARCHAR2 (512) DEFAULT NULL;
626 l_time_summary_id NUMBER;
627 l_time_sum_start_date DATE;
628 l_time_sum_end_date DATE;
629 l_earn_policy VARCHAR2 (30);
630 l_task VARCHAR2 (30);
631 l_hours_type VARCHAR2 (80);
632 l_earn_reason_code VARCHAR2 (30);
633 l_project VARCHAR2 (30);
634 l_location VARCHAR2 (30);
635 l_comment VARCHAR2 (30);
636 l_rate_multiple NUMBER;
637 l_hourly_rate NUMBER;
638 l_amount NUMBER;
639 l_sep_check_flag VARCHAR2 (30);
640 l_hours NUMBER;
641 l_valid VARCHAR2 (1) := 'N';
642 l_no_times VARCHAR2 (1) := 'N';
643 l_new VARCHAR2 (30);
644 l_session_id NUMBER;
645 l_att NUMBER;
646 l_debug VARCHAR2 (1);
647 l_next_index BINARY_INTEGER := 0;
648 i BINARY_INTEGER;
649 loop_ok BOOLEAN := TRUE;
650 l_time_period_id NUMBER;
651 l_timecard_id NUMBER;
652 l_tim_sum_id_tab t_tim_sum_id_tab;
653 l_timecards t_tim_sum_id_tab;
654 l_cnt NUMBER; --Added 2804510
655 l_cnt_att NUMBER; --Added 2804510
656 l_day NUMBER; --Added 2804510
657 e_error EXCEPTION;
658 l_proc VARCHAR2 (100);
659 l_delete VARCHAR2 (1);
660 l_tim_sum BINARY_INTEGER;
661 l_state_name hxt_sum_hours_worked_f.state_name%TYPE;
662 l_county_name hxt_sum_hours_worked_f.county_name%TYPE;
663 l_city_name hxt_sum_hours_worked_f.city_name%TYPE;
664 l_zip_code hxt_sum_hours_worked_f.zip_code%TYPE;
665 l_tim_id NUMBER;
666 BEGIN
667 g_debug := hr_utility.debug_enabled;
668
669 IF g_debug
670 THEN
671 l_proc := 'hxt_hxc_retrieval_process.VALIDATE_TIMECARD';
672 hr_utility.set_location (l_proc, 1);
673 END IF;
674
675 OPEN get_debug;
676
677 FETCH get_debug
678 INTO l_debug;
679
680 IF get_debug%FOUND
681 THEN
682 IF g_debug
683 THEN
684 hr_utility.set_location (l_proc, 3);
685 END IF;
686 END IF;
687
688 CLOSE get_debug;
689
690 hxt_time_collection.set_cache (FALSE);
691
692
693 IF g_debug
694 THEN
695 hr_utility.set_location (l_proc, 4);
696 END IF;
697
698 SAVEPOINT otm_validate;
699
700 IF g_debug
701 THEN
702 hr_utility.set_location (l_proc, 5);
703 END IF;
704
705 -- Loop through all the building blocks and validate the details.
706 -------------------------------------------------------------------------------
707 --Bug 2804510
708 --the FOR loop used to loop through p_timecard table has beed removed
709 --as the Timekeeper doesn't have pl/sql table populated in the ordered manner
710 --hence p_time_building_blocks.first..p_time_building_blocks.last was failing
711 --used loop ...end loop control structure for looping
712 -------------------------------------------------------------------------------
713
714 -- Bugs 3384941, 3382457, 3381642 fix
715 -- Added the following FOR LOOP to validate the detail records in the
716 -- following order:
717 -- Deleted detail records processed first i.e., i = 1
718 -- Updated detail records processed next i.e., i = 2
719 -- New Inserted detail records processes last i.e., i = 3
720 FOR i IN 1 .. 3
721 LOOP
722 IF g_debug
723 THEN
724 hr_utility.set_location (l_proc, 6);
725 END IF;
726
727 l_cnt := p_time_building_blocks.FIRST; --Added 2804510
728
729 IF g_debug
730 THEN
731 hr_utility.TRACE ('l_cnt :' || l_cnt);
732 END IF;
733
734 LOOP --Added 2804510
735 EXIT WHEN NOT p_time_building_blocks.EXISTS (l_cnt);
736
737 --Added 2804510
738
739 --
740 IF g_debug
741 THEN
742 hr_utility.set_location (l_proc, 10);
743 hr_utility.TRACE
744 ('*********** NEW TIME BUILDING BLOCK ************');
745 END IF;
746
747 --
748 l_bb_id := p_time_building_blocks (l_cnt).time_building_block_id;
749 l_ovn := p_time_building_blocks (l_cnt).object_version_number;
750 l_type := p_time_building_blocks (l_cnt).TYPE;
751 l_measure := p_time_building_blocks (l_cnt).measure;
752 l_start_time := p_time_building_blocks (l_cnt).start_time;
753 l_stop_time := p_time_building_blocks (l_cnt).stop_time;
754 l_parent_bb_id :=
755 p_time_building_blocks (l_cnt).parent_building_block_id;
756 l_scope := p_time_building_blocks (l_cnt).SCOPE;
757 l_resource_id := p_time_building_blocks (l_cnt).resource_id;
758 l_resource_type := p_time_building_blocks (l_cnt).resource_type;
759 l_comment_text := p_time_building_blocks (l_cnt).comment_text;
760 l_new := p_time_building_blocks (l_cnt).NEW;
761 l_no_times := 'N';
762 l_date_to := p_time_building_blocks (l_cnt).date_to;
763
764
765 -- Bug 8486310
766 -- Save the Alias Defn put up as per preferences if it is not already
767 -- there. This would be done only once per timecard, and would
768 -- use the first block's start_time -- meaning the Timecard Scope's
769 -- start time.
770
771 -- Bug 12689562
772 IF g_alias_id IS NULL
773 THEN
774 g_alias_id := hxc_preference_evaluation.resource_preferences (p_resource_id => l_resource_id,
775 p_pref_code=> 'TC_W_TCRD_ALIASES',
776 p_attribute_n=> 1,
777 p_evaluation_date => l_start_time,
778 p_resp_id => FND_GLOBAL.resp_id);
779 END IF;
780
781 --
782 IF g_debug
783 THEN
784 hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
785 hr_utility.TRACE ('Type is : ' || l_type);
786 hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
787 hr_utility.TRACE ( 'l_start_time is '
788 || TO_CHAR (l_start_time,
789 'DD-MON-YYYY HH:MI:SS'
790 )
791 );
792 hr_utility.TRACE ( 'l_stop_time is '
793 || TO_CHAR (l_stop_time,
794 'DD-MON-YYYY HH:MI:SS'
795 )
796 );
797 hr_utility.TRACE ('l_scope is ' || l_scope);
798 hr_utility.TRACE ('l_resource_id is '
799 || TO_CHAR (l_resource_id)
800 );
801 hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
802 --
803 hr_utility.TRACE ( 'UOM is : '
804 || p_time_building_blocks (l_cnt).unit_of_measure
805 );
806 hr_utility.TRACE
807 ( 'Parent BB ID is : '
808 || TO_CHAR
809 (p_time_building_blocks (l_cnt).parent_building_block_id
810 )
811 );
812 hr_utility.TRACE ( 'PARENT_IS_NEW is : '
813 || p_time_building_blocks (l_cnt).parent_is_new
814 );
815 hr_utility.TRACE
816 ( 'OVN is : '
817 || TO_CHAR
818 (p_time_building_blocks (l_cnt).object_version_number
819 )
820 );
821 hr_utility.TRACE ( 'APPROVAL_STATUS is : '
822 || p_time_building_blocks (l_cnt).approval_status
823 );
824 hr_utility.TRACE
825 ( 'APPROVAL_STYLE_ID is : '
826 || TO_CHAR
827 (p_time_building_blocks (l_cnt).approval_style_id
828 )
829 );
830 hr_utility.TRACE
831 ( 'DATE_FROM is : '
832 || TO_CHAR
833 (p_time_building_blocks (l_cnt).date_from,
834 'DD-MON-YYYY'
835 )
836 );
837 hr_utility.TRACE
838 ( 'DATE_TO is : '
839 || TO_CHAR
840 (p_time_building_blocks (l_cnt).date_to,
841 'DD-MON-YYYY'
842 )
843 );
844 hr_utility.TRACE ( 'COMMENT_TEXT is : '
845 || p_time_building_blocks (l_cnt).comment_text
846 );
847 hr_utility.TRACE
848 ( 'Parent OVN is : '
849 || TO_CHAR
850 (p_time_building_blocks (l_cnt).parent_building_block_ovn
851 )
852 );
853 hr_utility.TRACE ( 'NEW is : '
854 || p_time_building_blocks (l_cnt).NEW
855 );
856 --
857 --
858 hr_utility.set_location (l_proc, 20);
859 END IF;
860
861 --Bug 2966729
862 --Description
863 --We ensure that if the block is deleted then it must be an existing block
864 --and not a new entry. In that case the existing blocks would get deleted from the hxt tables.
865 --If the block is deleted and its a new block then we dont delete them in
866 --the hxt tables as this does not have any meaning.
867 --We send non deleted blocks to hxt tables as usual.
868 --Bug 2966729 over
869
870 --
871
872 -- Bugs 3384941, 3382457, 3381642 fix
873 IF ( ( ( (l_type = 'MEASURE' AND l_measure IS NOT NULL
874 )
875 OR ( l_type = 'RANGE'
876 AND l_start_time IS NOT NULL
877 AND l_stop_time IS NOT NULL
878 )
879 )
880 AND ( -- First process deleted detail records
881 ( l_date_to <> hr_general.end_of_time
882 AND l_new = 'N'
883 AND i = 1
884 )
885 -- Next process the updated detail records
886 OR ( l_date_to = hr_general.end_of_time
887 AND l_new = 'N'
888 AND i = 2
889 )
890 -- And the last to be processed are the Inserts
891 OR ( l_date_to = hr_general.end_of_time
892 AND l_new = 'Y'
893 AND i = 3
894 )
895 ) --2966729
896 )
897 -- bug 3650967
898 OR ( ( (l_type = 'MEASURE' AND l_measure IS NULL)
899 OR ( l_type = 'RANGE'
900 AND l_start_time IS NULL
901 AND l_stop_time IS NULL
902 )
903 )
904 AND l_date_to <> hr_general.end_of_time
905 AND l_new = 'N'
906 AND i = 1
907 )
908 -- bug 3650967
909 )
910 AND l_scope = 'DETAIL'
911 THEN
912 -- (l_date_to = hr_general.end_of_time) THEN
913
914 --Bug 2770487 Sonarasi 04-Apr-2003
915 --Commented the above check l_date_to = hr_general.end_of_time because we need
916 --the deleted blocks also to be considered for explosion.
917 --Bug 2770487 Sonarasi Over
918
919 --
920 IF g_debug
921 THEN
922 hr_utility.set_location (l_proc, 21);
923 END IF;
924
925 l_valid := 'Y';
926 ELSE
927 IF g_debug
928 THEN
929 hr_utility.set_location (l_proc, 22);
930 END IF;
931
932 l_valid := 'N';
933 END IF;
934
935 --
936 -- Only care about valid DETAIL Blocks.
937 --
938 IF l_valid = 'Y'
939 THEN
940 IF g_debug
941 THEN
942 hr_utility.set_location (l_proc, 23);
943 END IF;
944
945 -- Get the start and stop times from the parent DAY block if DETAIL is
946 -- a measure.
947 IF l_type = 'MEASURE' AND l_start_time IS NULL
948 -- start bug 3650967
949 OR ( (l_type = 'MEASURE' AND l_measure IS NULL)
950 OR ( l_type = 'RANGE'
951 AND l_start_time IS NULL
952 AND l_stop_time IS NULL
953 )
954 AND l_date_to <> hr_general.end_of_time
955 AND l_new = 'N'
956 AND i = 1
957 ) -- end bug 3650967
958 THEN
959 IF g_debug
960 THEN
961 hr_utility.set_location (l_proc, 24);
962 END IF;
963
964 l_day := p_time_building_blocks.FIRST; --Added 2804510
965
966 LOOP --Added 2804510
967 EXIT WHEN NOT p_time_building_blocks.EXISTS (l_day);
968
969 --Added 2804510
970 IF g_debug
971 THEN
972 hr_utility.set_location (l_proc, 30);
973 END IF;
974
975 IF (p_time_building_blocks (l_day).time_building_block_id =
976 l_parent_bb_id
977 )
978 AND (p_time_building_blocks (l_day).SCOPE = 'DAY')
979 THEN
980 IF g_debug
981 THEN
982 hr_utility.set_location (l_proc, 31);
983 END IF;
984
985 --
986 l_start_time :=
987 p_time_building_blocks (l_day).start_time;
988 l_stop_time :=
989 p_time_building_blocks (l_day).stop_time;
990 l_no_times := 'Y';
991
992 --
993 IF g_debug
994 THEN
995 hr_utility.TRACE ( 'l_start_time is '
996 || TO_CHAR
997 (l_start_time,
998 'DD-MON-YYYY HH:MI:SS'
999 )
1000 );
1001 hr_utility.TRACE ( 'l_stop_time is '
1002 || TO_CHAR
1003 (l_stop_time,
1004 'DD-MON-YYYY HH:MI:SS'
1005 )
1006 );
1007 END IF;
1008
1009 EXIT;
1010 END IF;
1011
1012 IF g_debug
1013 THEN
1014 hr_utility.set_location (l_proc, 32);
1015 END IF;
1016
1017 l_day := p_time_building_blocks.NEXT (l_day);
1018 --Added 2804510
1019 END LOOP; --Added 2804510
1020
1021 IF g_debug
1022 THEN
1023 hr_utility.set_location (l_proc, 33);
1024 END IF;
1025 END IF; -- l_type = MEASURE
1026
1027 IF g_debug
1028 THEN
1029 hr_utility.set_location (l_proc, 34);
1030 END IF;
1031
1032 l_person_id := NULL;
1033
1034 IF l_resource_type = 'PERSON'
1035 THEN
1036 IF g_debug
1037 THEN
1038 hr_utility.set_location (l_proc, 35);
1039 END IF;
1040
1041 l_person_id := l_resource_id;
1042
1043 IF g_debug
1044 THEN
1045 hr_utility.TRACE ( 'l_person_id is '
1046 || TO_CHAR (l_person_id)
1047 );
1048 END IF;
1049 END IF;
1050
1051 IF g_debug
1052 THEN
1053 hr_utility.set_location (l_proc, 36);
1054 END IF;
1055
1056 l_effective_date := TRUNC (l_start_time);
1057
1058 IF g_debug
1059 THEN
1060 hr_utility.TRACE ( 'l_effective_date is :'
1061 || TO_CHAR (l_effective_date,
1062 'DD-MON-YYYY'
1063 )
1064 );
1065 END IF;
1066
1067 BEGIN
1068 IF g_debug
1069 THEN
1070 hr_utility.set_location (l_proc, 37);
1071 END IF;
1072
1073 SELECT full_name, business_group_id
1074 INTO hxt_otc_retrieval_interface.g_full_name, l_bg_id
1075 FROM per_all_people_f
1076 WHERE person_id = l_person_id
1077 AND l_effective_date BETWEEN effective_start_date
1078 AND effective_end_date;
1079 EXCEPTION
1080 WHEN NO_DATA_FOUND
1081 THEN
1082 IF g_debug
1083 THEN
1084 hr_utility.set_location (l_proc, 38);
1085 END IF;
1086
1087 hxc_time_entry_rules_utils_pkg.add_error_to_table
1088 (p_message_table => p_messages,
1089 p_message_name => 'HR_52365_PTU_NO_PERSON_EXISTS',
1090 p_message_token => NULL,
1091 p_message_level => 'ERROR',
1092 p_message_field => NULL,
1093 p_application_short_name => 'PER',
1094 p_timecard_bb_id => l_bb_id,
1095 p_time_attribute_id => NULL,
1096 p_timecard_bb_ovn => l_ovn,
1097 p_time_attribute_ovn => NULL
1098 );
1099 RAISE e_error;
1100 END;
1101
1102 -- Get Employee Number
1103 --
1104 -- l_employee_number := hxt_otc_retrieval_interface.get_employee_number(
1105 -- l_person_id,
1106 -- l_effective_date);
1107 IF g_debug
1108 THEN
1109 hr_utility.set_location (l_proc, 39);
1110 END IF;
1111
1112 hxt_otc_retrieval_interface.get_assignment_id
1113 (p_person_id => l_person_id,
1114 p_payroll_id => l_payroll_id,
1115 p_bg_id => l_bg_id,
1116 p_assignment_id => l_assignment_id,
1117 p_effective_date => l_effective_date
1118 );
1119
1120 IF g_debug
1121 THEN
1122 hr_utility.set_location (l_proc, 50);
1123 END IF;
1124
1125 l_field_name.DELETE;
1126 l_value.DELETE;
1127 l_category.DELETE;
1128 l_context.DELETE;
1129
1130 IF g_debug
1131 THEN
1132 hr_utility.set_location (l_proc, 55);
1133 hr_utility.TRACE ( 'number of attr is : '
1134 || TO_CHAR (p_time_attributes.COUNT)
1135 );
1136 END IF;
1137
1138 -- Get the attributes for this detail building block.
1139 IF p_time_attributes.COUNT <> 0
1140 THEN
1141 l_att := 1;
1142 l_cnt_att := p_time_attributes.FIRST; --Added 2804510
1143
1144 LOOP --Added 2804510
1145 EXIT WHEN NOT p_time_attributes.EXISTS (l_cnt_att);
1146
1147 --Added 2804510
1148 IF l_bb_id =
1149 p_time_attributes (l_cnt_att).building_block_id
1150 THEN
1151 IF g_debug
1152 THEN
1153 hr_utility.TRACE
1154 ('------ In Attribute Loop ------');
1155 END IF;
1156
1157 l_field_name (l_att) :=
1158 p_time_attributes (l_cnt_att).attribute_name;
1159
1160 IF g_debug
1161 THEN
1162 hr_utility.TRACE ( 'l_field_name(l_att) is '
1163 || l_field_name (l_att)
1164 );
1165 END IF;
1166
1167 l_value (l_att) :=
1168 p_time_attributes (l_cnt_att).attribute_value;
1169
1170 IF g_debug
1171 THEN
1172 hr_utility.TRACE ( 'l_value(l_att) is '
1173 || l_value (l_att)
1174 );
1175 END IF;
1176
1177 l_context (l_att) :=
1178 p_time_attributes (l_cnt_att).bld_blk_info_type;
1179
1180 IF g_debug
1181 THEN
1182 hr_utility.TRACE ( 'l_context(l_att) is '
1183 || l_context (l_att)
1184 );
1185 END IF;
1186
1187 l_category (l_att) :=
1188 p_time_attributes (l_cnt_att).CATEGORY;
1189
1190 IF g_debug
1191 THEN
1192 hr_utility.TRACE ( 'l_category(l_att) is '
1193 || l_category (l_att)
1194 );
1195 END IF;
1196
1197 l_att := l_att + 1;
1198 END IF;
1199
1200 l_cnt_att := p_time_attributes.NEXT (l_cnt_att);
1201 --Added 2804510
1202 END LOOP; --Added 2804510
1203 END IF;
1204
1205 IF g_debug
1206 THEN
1207 hr_utility.TRACE ('l_att is ' || TO_CHAR (l_att));
1208 hr_utility.set_location (l_proc, 60);
1209 END IF;
1210
1211 hxt_otc_retrieval_interface.parse_attributes
1212 (p_category => l_category,
1213 p_field_name => l_field_name,
1214 p_value => l_value,
1215 p_context => l_context,
1216 p_date_worked => l_date_worked,
1217 p_type => l_type,
1218 p_measure => l_measure,
1219 p_start_time => l_start_time,
1220 p_stop_time => l_stop_time,
1221 p_assignment_id => l_assignment_id,
1222 p_earn_policy => l_earn_policy, -- Bug 16598207
1223 p_hours => l_hours,
1224 p_hours_type => l_hours_type,
1225 p_segment => l_segment,
1226 p_project => l_project,
1227 p_task => l_task,
1228 p_state_name => l_state_name,
1229 p_county_name => l_county_name,
1230 p_city_name => l_city_name,
1231 p_zip_code => l_zip_code
1232 );
1233
1234 IF (l_no_times = 'Y')
1235 THEN
1236 IF g_debug
1237 THEN
1238 hr_utility.set_location (l_proc, 61);
1239 END IF;
1240
1241 l_start_time := NULL;
1242 l_stop_time := NULL;
1243 END IF;
1244
1245 l_time_summary_id := NULL;
1246 l_time_sum_start_date := NULL;
1247 l_time_sum_end_date := NULL;
1248
1249 IF l_new = 'N'
1250 THEN
1251 IF g_debug
1252 THEN
1253 hr_utility.set_location (l_proc, 62);
1254 END IF;
1255
1256 -- Bug 7415291
1257 -- Added new parameter l_tim_id
1258 hxt_otc_retrieval_interface.find_existing_timecard
1259 (p_payroll_id => l_payroll_id,
1260 p_date_worked => l_date_worked,
1261 p_person_id => l_person_id,
1262 p_old_ovn => l_ovn,
1263 p_bb_id => l_bb_id,
1264 p_time_summary_id => l_time_summary_id,
1265 p_time_sum_start_date => l_time_sum_start_date,
1266 p_time_sum_end_date => l_time_sum_end_date,
1267 p_tim_id => l_tim_id
1268 );
1269
1270 IF g_debug
1271 THEN
1272 hr_utility.set_location (l_proc, 63);
1273 hr_utility.TRACE ('after find_existing_timecard');
1274 hr_utility.TRACE ( 'l_time_summary_id is: '
1275 || TO_CHAR (l_time_summary_id)
1276 );
1277 END IF;
1278
1279 IF l_time_summary_id IS NOT NULL
1280 THEN
1281 IF g_debug
1282 THEN
1283 hr_utility.set_location (l_proc, 64);
1284 END IF;
1285
1286 DELETE FROM hxt_det_hours_worked_f
1287 WHERE parent_id = l_time_summary_id;
1288 END IF;
1289 END IF;
1290
1291 --Bug 2770487 Sonarasi 04-Apr-2003
1292 IF (l_date_to = hr_general.end_of_time)
1293 THEN
1294 l_delete := 'N';
1295 ELSE
1296 l_delete := 'Y';
1297 END IF;
1298
1299 --Here We are setting the delete flag based on whether we would like to
1300 --delete the blocks or retail them.Therefore those blocks which are end
1301 --dated will have the l_delete flag set to 'Y'. We will be passing the
1302 --l_delete as a value to the parameter delete_yn of the record_time api.
1303 --Bug 2770487 Sonarasi Over
1304 IF g_debug
1305 THEN
1306 hr_utility.set_location (l_proc, 65);
1307 END IF;
1308
1309 hxt_time_collection.record_time
1310 (timecard_source => 'Time Store',
1311 employee_number => TO_CHAR
1312 (l_person_id
1313 )
1314 -- l_employee_number
1315 ,
1316 batch_name => 'OTL_SS_DEP_VAL',
1317 date_worked => l_date_worked,
1318 start_time => l_start_time,
1319 end_time => l_stop_time,
1320 hours => l_hours,
1321 wage_code => NULL,
1322 earning_policy => l_earn_policy,
1323 hours_type => l_hours_type,
1324 earn_reason_code => l_earn_reason_code,
1325 project => l_project,
1326 task_number => l_task,
1327 location_code => l_location,
1328 COMMENT => l_comment,
1329 rate_multiple => l_rate_multiple,
1330 hourly_rate => l_hourly_rate,
1331 amount => l_amount,
1332 separate_check_flag => l_sep_check_flag,
1333 business_group_id => l_bg_id,
1334 cost_segment1 => l_segment (1),
1335 cost_segment2 => l_segment (2),
1336 cost_segment3 => l_segment (3),
1337 cost_segment4 => l_segment (4),
1338 cost_segment5 => l_segment (5),
1339 cost_segment6 => l_segment (6),
1340 cost_segment7 => l_segment (7),
1341 cost_segment8 => l_segment (8),
1342 cost_segment9 => l_segment (9),
1343 cost_segment10 => l_segment (10),
1344 cost_segment11 => l_segment (11),
1345 cost_segment12 => l_segment (12),
1346 cost_segment13 => l_segment (13),
1347 cost_segment14 => l_segment (14),
1348 cost_segment15 => l_segment (15),
1349 cost_segment16 => l_segment (16),
1350 cost_segment17 => l_segment (17),
1351 cost_segment18 => l_segment (18),
1352 cost_segment19 => l_segment (19),
1353 cost_segment20 => l_segment (20),
1354 cost_segment21 => l_segment (21),
1355 cost_segment22 => l_segment (22),
1356 cost_segment23 => l_segment (23),
1357 cost_segment24 => l_segment (24),
1358 cost_segment25 => l_segment (25),
1359 cost_segment26 => l_segment (26),
1360 cost_segment27 => l_segment (27),
1361 cost_segment28 => l_segment (28),
1362 cost_segment29 => l_segment (29),
1363 cost_segment30 => l_segment (30),
1364 time_summary_id => l_time_summary_id,
1365 tim_sum_eff_start_date => l_time_sum_start_date,
1366 tim_sum_eff_end_date => l_time_sum_end_date,
1367 created_by => '-1',
1368 last_updated_by => '-1',
1369 last_update_login => '-1',
1370 dt_update_mode => 'CORRECTION',
1371 created_tim_sum_id => l_created_tim_sum_id,
1372 otm_error => l_otm_error,
1373 oracle_error => l_oracle_error,
1374 p_time_building_block_id => l_bb_id,
1375 p_time_building_block_ovn => l_ovn,
1376 p_validate => FALSE,
1377 delete_yn => l_delete,
1378 p_state_name => l_state_name,
1379 p_county_name => l_county_name,
1380 p_city_name => l_city_name,
1381 p_zip_code => l_zip_code
1382 );
1383
1384 IF g_debug
1385 THEN
1386 hr_utility.set_location (l_proc, 66);
1387 END IF;
1388
1389 IF g_otm_messages.COUNT > 0
1390 THEN
1391 FOR i IN g_otm_messages.FIRST .. g_otm_messages.LAST
1392 LOOP
1393 hxc_time_entry_rules_utils_pkg.add_error_to_table
1394 (p_message_table => p_messages,
1395 p_message_name => g_otm_messages (i).message_name,
1396 p_message_token => g_otm_messages (i).message_tokens,
1397 p_message_level => g_otm_messages (i).message_level,
1398 p_message_field => NULL,
1399 p_application_short_name => g_otm_messages (i).application_short_name,
1400 p_timecard_bb_id => l_bb_id,
1401 p_time_attribute_id => NULL,
1402 p_timecard_bb_ovn => l_ovn,
1403 p_time_attribute_ovn => NULL
1404 );
1405 END LOOP;
1406
1407 g_otm_messages.DELETE;
1408 l_otm_error := NULL;
1409 l_oracle_error := NULL;
1410 RAISE e_error;
1411 ELSE
1412 --to capture any errors which are not added to g_otm_messages table but
1413 --l_otm_error has not null values
1414 IF l_otm_error IS NOT NULL
1415 THEN
1416 IF g_debug
1417 THEN
1418 hr_utility.set_location (l_proc, 1000);
1419 hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1420 END IF;
1421
1422 hxc_time_entry_rules_utils_pkg.add_error_to_table
1423 (p_message_table => p_messages,
1424 p_message_name => 'HXC_HXT_DEP_VAL_OTMERR',
1425 p_message_token => SUBSTR
1426 ( 'ERROR&'
1427 || l_otm_error,
1428 1,
1429 100
1430 ),
1431 p_message_level => 'ERROR',
1432 p_message_field => NULL,
1433 p_application_short_name => 'HXC',
1434 p_timecard_bb_id => l_bb_id,
1435 p_time_attribute_id => NULL,
1436 p_timecard_bb_ovn => l_ovn,
1437 p_time_attribute_ovn => NULL
1438 );
1439 RAISE e_error;
1440 END IF;
1441
1442 IF l_oracle_error IS NOT NULL
1443 THEN
1444 IF g_debug
1445 THEN
1446 hr_utility.set_location (l_proc, 1050);
1447 hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
1448 );
1449 END IF;
1450
1451 hxc_time_entry_rules_utils_pkg.add_error_to_table
1452 (p_message_table => p_messages,
1453 p_message_name => 'HXC_HXT_DEP_VAL_ORAERR',
1454 p_message_token => 'ERROR&'
1455 || l_oracle_error,
1456 p_message_level => 'ERROR',
1457 p_message_field => NULL,
1458 p_application_short_name => 'HXC',
1459 p_timecard_bb_id => l_bb_id,
1460 p_time_attribute_id => NULL,
1461 p_timecard_bb_ovn => l_ovn,
1462 p_time_attribute_ovn => NULL
1463 );
1464 RAISE e_error;
1465 END IF;
1466 END IF; --g_otm_messages
1467
1468 l_next_index := l_next_index + 1;
1469 l_tim_sum_id_tab (l_next_index) := l_created_tim_sum_id;
1470 END IF; -- l_valid = Y and l_scope = DETAIL
1471
1472 l_cnt := p_time_building_blocks.NEXT (l_cnt); --Added 2804510
1473 END LOOP; --Added 2804510
1474 END LOOP;
1475
1476 l_timecards.DELETE;
1477 l_tim_sum := l_tim_sum_id_tab.FIRST;
1478
1479 LOOP
1480 EXIT WHEN NOT l_tim_sum_id_tab.EXISTS (l_tim_sum);
1481
1482 --Bug 2770487 Sonarasi 04-Apr-2003
1483 --the following if condition i.e if l_tim_sum_id_tab(l_tim_sum) is not null then
1484 --is added because incase of deleted blocks we may have null time summary ids
1485 --this may cause problems if the time summary id table returns a null value
1486 --Hence adding a check to prevent that scenario.
1487 --Bug 2770487 Sonarasi Over
1488 IF l_tim_sum_id_tab (l_tim_sum) IS NOT NULL
1489 THEN
1490 DELETE FROM hxt_det_hours_worked_f
1491 WHERE parent_id = l_tim_sum_id_tab (l_tim_sum);
1492
1493 OPEN get_timecard_id (p_tim_sum_id => l_tim_sum_id_tab
1494 (l_tim_sum)
1495 );
1496
1497 FETCH get_timecard_id
1498 INTO l_timecard_id, l_time_period_id;
1499
1500 IF (get_timecard_id%FOUND)
1501 THEN
1502 IF g_debug
1503 THEN
1504 hr_utility.TRACE ( 'TIM_SUM_ID IS : '
1505 || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
1506 );
1507 hr_utility.TRACE ( 'l_timecard_id is : '
1508 || TO_CHAR (l_timecard_id)
1509 );
1510 hr_utility.TRACE ( 'l_time_period_id is : '
1511 || TO_CHAR (l_time_period_id)
1512 );
1513 END IF;
1514
1515 IF (NOT l_timecards.EXISTS (l_time_period_id))
1516 THEN
1517 l_timecards (l_time_period_id) := l_timecard_id;
1518 END IF;
1519 END IF;
1520
1521 CLOSE get_timecard_id;
1522 END IF; --if l_tim_sum_id_tab(l_tim_sum) is not null then
1523
1524 l_tim_sum := l_tim_sum_id_tab.NEXT (l_tim_sum);
1525 END LOOP;
1526
1527 loop_ok := TRUE;
1528 i := l_timecards.FIRST;
1529
1530 IF i IS NOT NULL
1531 THEN
1532 WHILE loop_ok
1533 LOOP
1534 hxt_time_collection.re_explode_timecard
1535 (timecard_id => l_timecards
1536 (i),
1537 tim_eff_start_date => NULL,
1538 -- Not Being Used
1539 tim_eff_end_date => NULL,
1540 -- Not Being Used
1541 dt_update_mode => 'CORRECTION',
1542 otm_error => l_otm_error,
1543 oracle_error => l_oracle_error
1544 );
1545
1546 IF g_otm_messages.COUNT > 0
1547 THEN
1548 FOR i IN g_otm_messages.FIRST .. g_otm_messages.LAST
1549 LOOP
1550 hxc_time_entry_rules_utils_pkg.add_error_to_table
1551 (p_message_table => g_messages,
1552 p_message_name => g_otm_messages (i).message_name,
1553 p_message_token => g_otm_messages (i).message_tokens,
1554 p_message_level => g_otm_messages (i).message_level,
1555 p_message_field => NULL,
1556 p_application_short_name => g_otm_messages (i).application_short_name,
1557 p_timecard_bb_id => l_bb_id,
1558 p_time_attribute_id => NULL,
1559 p_timecard_bb_ovn => l_ovn,
1560 p_time_attribute_ovn => NULL
1561 );
1562 END LOOP;
1563
1564 g_otm_messages.DELETE;
1565 l_otm_error := NULL;
1566 l_oracle_error := NULL;
1567 RAISE e_error;
1568 ELSE
1569 --to capture any errors which are not added to g_otm_messages table but
1570 --l_otm_error has not null values
1571 IF l_otm_error IS NOT NULL
1572 THEN
1573 IF g_debug
1574 THEN
1575 hr_utility.set_location (l_proc, 2000);
1576 hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1577 END IF;
1578
1579 hxc_time_entry_rules_utils_pkg.add_error_to_table
1580 (p_message_table => g_messages,
1581 p_message_name => 'HXC_HXT_DEP_VAL_OTMERR',
1582 p_message_token => SUBSTR
1583 ( 'ERROR&'
1584 || l_otm_error,
1585 1,
1586 100
1587 ),
1588 p_message_level => 'ERROR',
1589 p_message_field => NULL,
1590 p_application_short_name => 'HXC',
1591 p_timecard_bb_id => l_bb_id,
1592 p_time_attribute_id => NULL,
1593 p_timecard_bb_ovn => l_ovn,
1594 p_time_attribute_ovn => NULL
1595 );
1596
1597 IF g_debug
1598 THEN
1599 hr_utility.TRACE ( 'g_messages.message_name is : '
1600 || g_messages (1).message_name
1601 );
1602 END IF;
1603
1604 RAISE e_error;
1605 END IF;
1606
1607 IF l_oracle_error IS NOT NULL
1608 THEN
1609 IF g_debug
1610 THEN
1611 hr_utility.set_location (l_proc, 2050);
1612 hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
1613 END IF;
1614
1615 hxc_time_entry_rules_utils_pkg.add_error_to_table
1616 (p_message_table => g_messages,
1617 p_message_name => 'HXC_HXT_DEP_VAL_ORAERR',
1618 p_message_token => 'ERROR&'
1619 || l_oracle_error,
1620 p_message_level => 'ERROR',
1621 p_message_field => NULL,
1622 p_application_short_name => 'HXC',
1623 p_timecard_bb_id => l_bb_id,
1624 p_time_attribute_id => NULL,
1625 p_timecard_bb_ovn => l_ovn,
1626 p_time_attribute_ovn => NULL
1627 );
1628
1629 IF g_debug
1630 THEN
1631 hr_utility.TRACE ( 'g_messages.message_name is : '
1632 || g_messages (1).message_name
1633 );
1634 END IF;
1635
1636 RAISE e_error;
1637 END IF;
1638 END IF;
1639
1640 i := l_timecards.NEXT (i);
1641
1642 IF i IS NULL
1643 THEN
1644 loop_ok := FALSE;
1645 END IF;
1646 END LOOP;
1647 END IF;
1648
1649
1650 ROLLBACK TO otm_validate;
1651
1652 IF g_debug
1653 THEN
1654 hr_utility.TRACE ('After RollBack');
1655 END IF;
1656 EXCEPTION
1657 WHEN e_error
1658 THEN
1659 ROLLBACK TO otm_validate;
1660 RETURN;
1661 WHEN OTHERS
1662 THEN
1663 ROLLBACK TO otm_validate;
1664 RETURN;
1665 END validate_timecard;
1666
1667 FUNCTION test_aps_vs_rtr (
1668 p_rtr_tr t_time_recipient,
1669 p_aps_tr t_time_recipient
1670 )
1671 RETURN BOOLEAN
1672 IS
1673 l_rtr_index BINARY_INTEGER;
1674 l_return BOOLEAN := FALSE;
1675 BEGIN
1676 l_rtr_index := p_rtr_tr.FIRST;
1677
1678 WHILE (l_rtr_index IS NOT NULL)
1679 LOOP
1680 IF NOT p_aps_tr.EXISTS (l_rtr_index)
1681 THEN
1682 l_return := TRUE;
1683 EXIT;
1684 END IF;
1685
1686 l_rtr_index := p_rtr_tr.NEXT (l_rtr_index);
1687 END LOOP;
1688
1689 RETURN l_return;
1690 END test_aps_vs_rtr;
1691
1692 ---------------------- otlr validation required ---------------------
1693 PROCEDURE otlr_validation_required (
1694 p_operation IN VARCHAR2,
1695 p_otm_explosion IN VARCHAR2,
1696 p_otm_rtr_id IN NUMBER,
1697 p_app_set_id IN NUMBER,
1698 p_timecard_id IN NUMBER,
1699 p_timecard_ovn IN NUMBER,
1700 p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info,
1701 p_time_att_info IN hxc_self_service_time_deposit.app_attributes_info,
1702 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
1703 )
1704 IS
1705 -- retrieves list of time recipients in retrieval rule group
1706 CURSOR csr_get_rtr (p_rtr_id NUMBER)
1707 IS
1708 SELECT DISTINCT (rrc.time_recipient_id)
1709 FROM hxc_retrieval_rule_comps rrc,
1710 hxc_retrieval_rules rr
1711 WHERE rr.retrieval_rule_id = p_rtr_id
1712 AND rrc.retrieval_rule_id = rr.retrieval_rule_id
1713 AND rrc.status <> 'WORKING';
1714
1715 -- retrieves list of time recipients in application set
1716 CURSOR csr_get_app_sets (p_app_set_id NUMBER)
1717 IS
1718 SELECT apsc.time_recipient_id
1719 FROM hxc_application_set_comps_v apsc, hxc_application_sets_v aps
1720 WHERE aps.application_set_id = p_app_set_id
1721 AND apsc.application_set_id = aps.application_set_id;
1722
1723 t_aps_tr t_time_recipient;
1724 t_rtr_tr t_time_recipient;
1725 l_rtr_tr_id hxc_time_recipients.time_recipient_id%TYPE;
1726 l_aps_tr_id hxc_time_recipients.time_recipient_id%TYPE;
1727 l_time_building_blocks hxc_self_service_time_deposit.timecard_info
1728 := p_time_building_blocks;
1729 l_time_att_info hxc_self_service_time_deposit.app_attributes_info
1730 := p_time_att_info;
1731 l_proc VARCHAR2 (250);
1732 BEGIN
1733 g_debug := hr_utility.debug_enabled;
1734
1735 IF (p_otm_explosion = 'Y')
1736 THEN
1737 -- Get the application set time recipients
1738 OPEN csr_get_app_sets (p_app_set_id);
1739
1740 FETCH csr_get_app_sets
1741 INTO l_aps_tr_id;
1742
1743 WHILE csr_get_app_sets%FOUND
1744 LOOP
1745 t_aps_tr (l_aps_tr_id) := 'N';
1746
1747 FETCH csr_get_app_sets
1748 INTO l_aps_tr_id;
1749 END LOOP;
1750
1751 CLOSE csr_get_app_sets;
1752
1753 IF (p_otm_rtr_id IS NULL)
1754 THEN
1755 hxc_time_entry_rules_utils_pkg.add_error_to_table
1756 (p_message_table => p_messages,
1757 p_message_name => 'HR_6153_ALL_PROCEDURE_FAIL',
1758 p_application_short_name => 'PAY',
1759 p_message_token => 'PROCEDURE&no rtr id for rules evaluation&STEP&2',
1760 p_message_level => 'ERROR',
1761 p_message_field => NULL,
1762 p_timecard_bb_id => p_timecard_id,
1763 p_time_attribute_id => NULL,
1764 p_timecard_bb_ovn => p_timecard_ovn,
1765 p_time_attribute_ovn => NULL
1766 );
1767 END IF; -- is otm rtr is null
1768
1769 OPEN csr_get_rtr (p_otm_rtr_id);
1770
1771 FETCH csr_get_rtr
1772 INTO l_rtr_tr_id;
1773
1774 WHILE csr_get_rtr%FOUND
1775 LOOP
1776 t_rtr_tr (l_rtr_tr_id) := 'N';
1777
1778 FETCH csr_get_rtr
1779 INTO l_rtr_tr_id;
1780 END LOOP;
1781
1782 CLOSE csr_get_rtr;
1783
1784 -- Now test to see if the retrieval rule group time recipients
1785 -- is at least a subset of the application set time recipients
1786 IF (test_aps_vs_rtr (t_rtr_tr, t_aps_tr))
1787 THEN
1788 hxc_time_entry_rules_utils_pkg.add_error_to_table
1789 (p_message_table => p_messages,
1790 p_message_name => 'HXC_VLD_APS_VS_RTR_GRP',
1791 p_message_token => NULL,
1792 p_message_level => 'ERROR',
1793 p_message_field => NULL,
1794 p_timecard_bb_id => p_timecard_id,
1795 p_time_attribute_id => NULL,
1796 p_timecard_bb_ovn => p_timecard_ovn,
1797 p_time_attribute_ovn => NULL
1798 );
1799 END IF;
1800
1801 -- skip this if we are SAVING
1802 IF g_debug
1803 THEN
1804 l_proc := 'hxt_hxc_retrieval_process.otlr_validation_required';
1805 hr_utility.TRACE ('p_operation:' || p_operation);
1806 END IF;
1807
1808 SAVEPOINT rollback_validation;
1809
1810 -- Bug 7557568
1811 -- Added call to this function to find out if there was any
1812 -- deleted detail, which came in after a SAVE.
1813 check_restrict_edit(p_time_building_blocks,
1814 p_messages);
1815
1816 IF p_messages.COUNT > 0
1817 THEN
1818 ROLLBACK TO rollback_validation;
1819 RETURN;
1820 END IF;
1821
1822 -- Bug 3321951 fix start.
1823 synchronize_deletes_in_otlr
1824 (p_time_building_blocks => l_time_building_blocks,
1825 p_time_att_info => l_time_att_info,
1826 p_messages => p_messages,
1827 p_timecard_source => NULL
1828 );
1829
1830 IF p_messages.COUNT > 0
1831 THEN
1832 ROLLBACK TO rollback_validation;
1833 RETURN;
1834 END IF;
1835
1836 -- Bug 3321951 fix stop.
1837 IF g_debug
1838 THEN
1839 hr_utility.set_location (l_proc, 20);
1840 END IF;
1841
1842 -- need to do OTM validation
1843 validate_timecard (p_operation => p_operation,
1844 p_time_building_blocks => l_time_building_blocks,
1845 p_time_attributes => l_time_att_info,
1846 p_messages => p_messages
1847 );
1848 ROLLBACK TO rollback_validation;
1849 END IF;
1850 END otlr_validation_required;
1851
1852 --
1853 ------------------------------ otlr_review_details -----------------------------
1854 --
1855 PROCEDURE otlr_review_details (
1856 p_time_building_blocks IN hxc_self_service_time_deposit.timecard_info,
1857 p_time_attributes IN hxc_self_service_time_deposit.app_attributes_info,
1858 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table,
1859 p_detail_build_blocks IN OUT NOCOPY hxc_self_service_time_deposit.timecard_info,
1860 p_detail_attributes IN OUT NOCOPY hxc_self_service_time_deposit.building_block_attribute_info
1861 )
1862 IS
1863 CURSOR get_otm_records (p_tim_sum_id NUMBER)
1864 IS
1865 SELECT date_worked, hours, time_in, time_out, element_type_id
1866 FROM hxt_det_hours_worked
1867 WHERE parent_id = p_tim_sum_id;
1868
1869 CURSOR get_timecard_id (p_tim_sum_id NUMBER)
1870 IS
1871 SELECT hshw.tim_id, ht.time_period_id
1872 FROM hxt_sum_hours_worked hshw, hxt_timecards ht
1873 WHERE hshw.ID = p_tim_sum_id AND hshw.tim_id = ht.ID;
1874
1875 CURSOR get_debug
1876 IS
1877 SELECT 'X'
1878 FROM hxc_debug
1879 WHERE process = 'hxt_hxc_retrieval_process'
1880 AND TRUNC (debug_date) <= SYSDATE;
1881
1882 TYPE t_tim_sum_id_tab IS TABLE OF NUMBER
1883 INDEX BY BINARY_INTEGER;
1884
1885 l_timecard_detail hxc_self_service_time_deposit.timecard_info;
1886 l_detail_attributes hxc_self_service_time_deposit.app_attributes_info;
1887 l_field_name hxt_otc_retrieval_interface.t_field_name;
1888 l_value hxt_otc_retrieval_interface.t_value;
1889 l_context hxt_otc_retrieval_interface.t_field_name;
1890 l_category hxt_otc_retrieval_interface.t_field_name;
1891 l_segment hxt_otc_retrieval_interface.t_segment;
1892 l_bb_id NUMBER (15);
1893 l_bb_ovn NUMBER (15);
1894 l_type VARCHAR2 (30);
1895 l_measure hxc_time_building_blocks.measure%TYPE;
1896 l_uom hxc_time_building_blocks.unit_of_measure%TYPE;
1897 l_start_time DATE;
1898 l_stop_time DATE;
1899 l_parent_bb_id NUMBER (15);
1900 l_parent_bb_ovn NUMBER (15);
1901 l_parent_new VARCHAR2 (1);
1902 l_scope VARCHAR2 (30);
1903 l_resource_id NUMBER (15);
1904 l_resource_type VARCHAR2 (30);
1905 l_comment_text VARCHAR2 (2000);
1906 l_appr_status hxc_time_building_blocks.approval_status%TYPE;
1907 l_appr_style_id hxc_time_building_blocks.approval_style_id%TYPE;
1908 l_date_from hxc_time_building_blocks.date_from%TYPE;
1909 l_date_to hxc_time_building_blocks.date_to%TYPE;
1910 l_person_id NUMBER (9);
1911 l_date_worked DATE;
1912 l_effective_date DATE;
1913 l_assignment_id NUMBER (9);
1914 l_payroll_id NUMBER (9);
1915 l_bg_id NUMBER (9);
1916 l_created_tim_sum_id hxt_sum_hours_worked.ID%TYPE DEFAULT NULL;
1917 l_otm_error VARCHAR2 (240) DEFAULT NULL;
1918 l_oracle_error VARCHAR2 (512) DEFAULT NULL;
1919 l_time_summary_id NUMBER;
1920 l_time_sum_start_date DATE;
1921 l_time_sum_end_date DATE;
1922 l_project VARCHAR2 (30);
1923 l_task VARCHAR2 (30);
1924 l_hours_type VARCHAR2 (80);
1925 l_comment VARCHAR2 (30);
1926 l_hours NUMBER;
1927 l_valid VARCHAR2 (1) := 'N';
1928 l_no_times VARCHAR2 (1) := 'N';
1929 l_new VARCHAR2 (30);
1930 l_session_id NUMBER;
1931 l_att NUMBER;
1932 l_proc VARCHAR2 (100);
1933 detail_date_worked hxt_det_hours_worked_f.date_worked%TYPE;
1934 detail_hours hxt_det_hours_worked_f.hours%TYPE;
1935 detail_time_in hxt_det_hours_worked_f.time_in%TYPE;
1936 detail_time_out hxt_det_hours_worked_f.time_out%TYPE;
1937 detail_hours_type hxt_det_hours_worked_f.element_type_id%TYPE;
1938 detail_type VARCHAR2 (30);
1939 l_det_cnt NUMBER (15);
1940 l_min_bb_id NUMBER (15);
1941 l_next_index BINARY_INTEGER := 0;
1942 l_next_att_index BINARY_INTEGER := 0;
1943 l_master_index BINARY_INTEGER := 0;
1944 l_num_rec NUMBER := 0;
1945 l_time_building_block_id NUMBER := 0;
1946 l_time_attribute_id NUMBER := 0;
1947 i BINARY_INTEGER;
1948 loop_ok BOOLEAN := TRUE;
1949 l_time_period_id NUMBER;
1950 l_timecard_id NUMBER;
1951 l_debug VARCHAR2 (1);
1952 l_tim_sum_id_tab t_tim_sum_id_tab;
1953 l_timecards t_tim_sum_id_tab;
1954 l_delete VARCHAR2 (1);
1955 l_hrstype_entered VARCHAR2 (1) := 'N';
1956 l_tim_sum BINARY_INTEGER;
1957 -- Bug 3012684
1958 l_error_flag VARCHAR2 (1) := 'N';
1959 l_state_name hxt_sum_hours_worked_f.state_name%TYPE;
1960 l_county_name hxt_sum_hours_worked_f.county_name%TYPE;
1961 l_city_name hxt_sum_hours_worked_f.city_name%TYPE;
1962 l_zip_code hxt_sum_hours_worked_f.zip_code%TYPE;
1963 l_time_building_blocks hxc_self_service_time_deposit.timecard_info
1964 := p_time_building_blocks;
1965 l_time_attributes hxc_self_service_time_deposit.app_attributes_info
1966 := p_time_attributes;
1967 l_tim_id NUMBER;
1968
1969 l_error_exists BOOLEAN := FALSE;
1970 -- Bug 16598207
1971 l_earn_policy VARCHAR2(50);
1972
1973 BEGIN
1974 g_debug := hr_utility.debug_enabled;
1975
1976 OPEN get_debug;
1977
1978 FETCH get_debug
1979 INTO l_debug;
1980
1981 IF get_debug%FOUND
1982 THEN
1983 hr_utility.trace_on (NULL, 'OTLR');
1984 END IF;
1985
1986 hxt_time_collection.set_cache (FALSE);
1987
1988 CLOSE get_debug;
1989
1990 SAVEPOINT review_details;
1991
1992 -- Bug 8655664
1993 -- Nulling out the global tables so that the earlier values
1994 -- are lost.
1995 g_alias_id := NULL;
1996 g_an_id.DELETE;
1997
1998 synchronize_deletes_in_otlr
1999 (p_time_building_blocks => l_time_building_blocks,
2000 p_time_att_info => l_time_attributes,
2001 p_messages => p_messages,
2002 p_timecard_source => 'Timecard Review'
2003 );
2004
2005 IF p_messages.COUNT > 0
2006 THEN
2007 ROLLBACK TO review_details;
2008 RETURN;
2009 END IF;
2010
2011 IF g_debug
2012 THEN
2013 l_proc := 'hxt_hxc_retrieval_process.otlr_review_details';
2014 hr_utility.TRACE ('******** IN POPULATE DETAILS **********');
2015 END IF;
2016
2017 IF l_timecard_detail.COUNT > 0
2018 THEN
2019 -- Bug 8486310
2020 -- Alright, when we are Deleting the table, why NULL out first ??
2021 /*
2022 FOR l IN l_timecard_detail.FIRST .. l_timecard_detail.LAST
2023 LOOP
2024 l_timecard_detail (l).time_building_block_id := NULL;
2025 l_timecard_detail (l).TYPE := NULL;
2026 l_timecard_detail (l).measure := NULL;
2027 l_timecard_detail (l).unit_of_measure := NULL;
2028 l_timecard_detail (l).start_time := NULL;
2029 l_timecard_detail (l).stop_time := NULL;
2030 l_timecard_detail (l).parent_building_block_id := NULL;
2031 l_timecard_detail (l).parent_is_new := NULL;
2032 l_timecard_detail (l).SCOPE := NULL;
2033 l_timecard_detail (l).object_version_number := NULL;
2034 l_timecard_detail (l).approval_status := NULL;
2035 l_timecard_detail (l).resource_id := NULL;
2036 l_timecard_detail (l).resource_type := NULL;
2037 l_timecard_detail (l).approval_style_id := NULL;
2038 l_timecard_detail (l).date_from := NULL;
2039 l_timecard_detail (l).date_to := NULL;
2040 l_timecard_detail (l).comment_text := NULL;
2041 l_timecard_detail (l).parent_building_block_ovn := NULL;
2042 l_timecard_detail (l).NEW := NULL;
2043 l_timecard_detail (l).changed := NULL;
2044 END LOOP;
2045 */
2046 l_timecard_detail.DELETE;
2047 END IF;
2048
2049 IF l_detail_attributes.COUNT > 0
2050 THEN
2051 /*
2052 FOR l IN l_detail_attributes.FIRST .. l_detail_attributes.LAST
2053 LOOP
2054 l_detail_attributes (l).time_attribute_id := NULL;
2055 l_detail_attributes (l).building_block_id := NULL;
2056 l_detail_attributes (l).attribute_name := NULL;
2057 l_detail_attributes (l).attribute_value := NULL;
2058 l_detail_attributes (l).bld_blk_info_type := NULL;
2059 l_detail_attributes (l).CATEGORY := NULL;
2060 l_detail_attributes (l).updated := NULL;
2061 l_detail_attributes (l).changed := NULL;
2062 END LOOP;
2063 */
2064 l_detail_attributes.DELETE;
2065 END IF;
2066
2067 g_messages := p_messages;
2068 hr_kflex_utility.set_session_date (p_effective_date => SYSDATE,
2069 p_session_id => l_session_id
2070 );
2071 l_tim_sum_id_tab.DELETE;
2072 -- SAVEPOINT populate_tables;
2073 -- Loop through all the building blocks, which will be of Scope 'DAY'
2074 -- and populate the pl/sql table with the 'DETAIL' records for the Day.
2075 l_min_bb_id := -1;
2076
2077 IF g_debug
2078 THEN
2079 hr_utility.TRACE ('*********** FIND MIN BB ID ************');
2080 END IF;
2081
2082 FOR l_cnt IN p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2083 LOOP
2084 IF g_debug
2085 THEN
2086 hr_utility.set_location (l_proc, 5);
2087 END IF;
2088
2089 IF p_time_building_blocks (l_cnt).time_building_block_id <
2090 l_min_bb_id
2091 THEN
2092 l_min_bb_id :=
2093 p_time_building_blocks (l_cnt).time_building_block_id;
2094 END IF;
2095 END LOOP;
2096
2097 IF g_debug
2098 THEN
2099 hr_utility.TRACE ('MIN BB ID IS : ' || TO_CHAR (l_min_bb_id));
2100 END IF;
2101
2102 l_time_building_block_id := l_min_bb_id;
2103
2104 -- Bugs 3384941, 3382457, 3381642 fix
2105 -- Added the following FOR LOOP to validate the detail records in the
2106 -- following order:
2107 -- Deleted detail records processed first i.e., i = 1
2108 -- Updated detail records processed next i.e., i = 2
2109 -- New Inserted detail records processes last i.e., i = 3
2110 FOR i IN 1 .. 3
2111 LOOP
2112 IF g_debug
2113 THEN
2114 hr_utility.set_location (l_proc, 6);
2115 END IF;
2116
2117 FOR l_cnt IN
2118 p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2119 LOOP
2120 IF g_debug
2121 THEN
2122 hr_utility.set_location (l_proc, 10);
2123 hr_utility.TRACE
2124 ('*********** NEW TIME BUILDING BLOCK ************');
2125 END IF;
2126
2127 l_bb_id := p_time_building_blocks (l_cnt).time_building_block_id;
2128 l_bb_ovn := p_time_building_blocks (l_cnt).object_version_number;
2129 l_type := p_time_building_blocks (l_cnt).TYPE;
2130 l_measure := p_time_building_blocks (l_cnt).measure;
2131 l_uom := p_time_building_blocks (l_cnt).unit_of_measure;
2132 l_start_time := p_time_building_blocks (l_cnt).start_time;
2133 l_stop_time := p_time_building_blocks (l_cnt).stop_time;
2134 l_parent_bb_id :=
2135 p_time_building_blocks (l_cnt).parent_building_block_id;
2136 l_parent_bb_ovn :=
2137 p_time_building_blocks (l_cnt).parent_building_block_ovn;
2138 l_parent_new := p_time_building_blocks (l_cnt).parent_is_new;
2139 l_scope := p_time_building_blocks (l_cnt).SCOPE;
2140 l_resource_id := p_time_building_blocks (l_cnt).resource_id;
2141 l_resource_type := p_time_building_blocks (l_cnt).resource_type;
2142 l_comment_text := p_time_building_blocks (l_cnt).comment_text;
2143 l_new := p_time_building_blocks (l_cnt).NEW;
2144 l_no_times := 'N';
2145 l_appr_status := p_time_building_blocks (l_cnt).approval_status;
2146 l_appr_style_id :=
2147 p_time_building_blocks (l_cnt).approval_style_id;
2148 l_date_from := p_time_building_blocks (l_cnt).date_from;
2149 l_date_to := p_time_building_blocks (l_cnt).date_to;
2150
2151 IF g_debug
2152 THEN
2153 hr_utility.TRACE ('Time Bld Blk ID is :' || TO_CHAR (l_bb_id)
2154 );
2155 hr_utility.TRACE ('Type is :' || l_type);
2156 hr_utility.TRACE ( 'Measure is :'
2157 || TO_CHAR (l_measure)
2158 );
2159 hr_utility.TRACE ( 'Start time is :'
2160 || TO_CHAR (l_start_time,
2161 'DD-MON-YYYY HH:MI:SS'
2162 )
2163 );
2164 hr_utility.TRACE ( 'Stop time is :'
2165 || TO_CHAR (l_stop_time,
2166 'DD-MON-YYYY HH:MI:SS'
2167 )
2168 );
2169 hr_utility.TRACE ('Scope is :' || l_scope);
2170 hr_utility.TRACE ( 'Resource id is :'
2171 || TO_CHAR (l_resource_id)
2172 );
2173 hr_utility.TRACE ('Resource type is :' || l_resource_type);
2174 --
2175 hr_utility.TRACE ( 'Unit of Measure is :'
2176 || p_time_building_blocks (l_cnt).unit_of_measure
2177 );
2178 hr_utility.TRACE
2179 ( 'Parent Bld Blk ID is :'
2180 || TO_CHAR
2181 (p_time_building_blocks (l_cnt).parent_building_block_id
2182 )
2183 );
2184 hr_utility.TRACE ( 'Parent is new ? :'
2185 || p_time_building_blocks (l_cnt).parent_is_new
2186 );
2187 hr_utility.TRACE
2188 ( 'OVN is :'
2189 || TO_CHAR
2190 (p_time_building_blocks (l_cnt).object_version_number
2191 )
2192 );
2193 hr_utility.TRACE ( 'Approval Status is :'
2194 || p_time_building_blocks (l_cnt).approval_status
2195 );
2196 hr_utility.TRACE
2197 ( 'Approval Style ID is :'
2198 || TO_CHAR
2199 (p_time_building_blocks (l_cnt).approval_style_id
2200 )
2201 );
2202 hr_utility.TRACE
2203 ( 'Date From is :'
2204 || TO_CHAR
2205 (p_time_building_blocks (l_cnt).date_from,
2206 'DD-MON-YYYY'
2207 )
2208 );
2209 hr_utility.TRACE
2210 ( 'Date To is :'
2211 || TO_CHAR
2212 (p_time_building_blocks (l_cnt).date_to,
2213 'DD-MON-YYYY'
2214 )
2215 );
2216 hr_utility.TRACE ( 'Comment Text is :'
2217 || p_time_building_blocks (l_cnt).comment_text
2218 );
2219 hr_utility.TRACE
2220 ( 'Parent OVN is :'
2221 || TO_CHAR
2222 (p_time_building_blocks (l_cnt).parent_building_block_ovn
2223 )
2224 );
2225 hr_utility.TRACE ( 'NEW is :'
2226 || p_time_building_blocks (l_cnt).NEW
2227 );
2228 --
2229 hr_utility.set_location (l_proc, 20);
2230 END IF;
2231
2232
2233 -- Bug 8655664
2234 -- Save the Alias Defn put up as per preferences if it is not already
2235 -- there. This would be done only once per timecard, and would
2236 -- use the first block's start_time -- meaning the Timecard Scope's
2237 -- start time.
2238 -- Done for bug 8486310 for validate_timecard.
2239 IF g_alias_id IS NULL
2240 THEN
2241 g_alias_id := hxc_preference_evaluation.resource_preferences ( l_resource_id,
2242 'TC_W_TCRD_ALIASES',
2243 1,
2244 l_start_time
2245 );
2246 END IF;
2247
2248
2249
2250 --Bug 2966729
2251 --Description
2252 --We ensure that if the block is deleted then it must be an existing block
2253 --and not a new entry. In that case the existing blocks would get deleted from the hxt tables.
2254 --If the block is deleted and its a new block then we dont delete them in
2255 --the hxt tables as this does not have any meaning.
2256 --We send non deleted blocks to hxt tables as usual.
2257 --Bug 2966729 over
2258
2259 --
2260 -- Bugs 3384941, 3382457, 3381642 fix
2261 IF ( ( ( (l_type = 'MEASURE' AND l_measure IS NOT NULL
2262 )
2263 OR ( l_type = 'RANGE'
2264 AND l_start_time IS NOT NULL
2265 AND l_stop_time IS NOT NULL
2266 )
2267 )
2268 AND ( -- First process deleted detail records
2269 ( l_date_to <> hr_general.end_of_time
2270 AND l_new = 'N'
2271 AND i = 1
2272 )
2273 -- Next process the updated detail records
2274 OR ( l_date_to = hr_general.end_of_time
2275 AND l_new = 'N'
2276 AND i = 2
2277 )
2278 -- And the last to be processed are the Inserts
2279 OR ( l_date_to = hr_general.end_of_time
2280 AND l_new = 'Y'
2281 AND i = 3
2282 )
2283 ) --2966729
2284 )
2285 -- start bug 3650967
2286 OR ( ( (l_type = 'MEASURE' AND l_measure IS NULL)
2287 OR ( l_type = 'RANGE'
2288 AND l_start_time IS NULL
2289 AND l_stop_time IS NULL
2290 )
2291 )
2292 AND l_date_to <> hr_general.end_of_time
2293 AND l_new = 'N'
2294 AND i = 1
2295 )
2296 -- end bug 3650967
2297 )
2298 AND l_scope = 'DETAIL'
2299 THEN
2300 -- (l_date_to = hr_general.end_of_time) THEN
2301
2302 --Bug 2770487 Sonarasi 04-Apr-2003
2303 --Commented the above check l_date_to = hr_general.end_of_time because we need
2304 --the deleted blocks also to be considered for explosion.
2305 --Bug 2770487 Sonarasi Over
2306 --
2307 l_valid := 'Y';
2308 ELSE
2309 l_valid := 'N';
2310 END IF;
2311
2312 -- Only care about valid DETAIL Blocks
2313 IF l_valid = 'Y'
2314 THEN
2315 -- Get the start and stop times from the DAY block
2316 IF l_type = 'MEASURE' AND l_start_time IS NULL
2317 -- start bug 3650967
2318 OR ( (l_type = 'MEASURE' AND l_measure IS NULL)
2319 OR ( l_type = 'RANGE'
2320 AND l_start_time IS NULL
2321 AND l_stop_time IS NULL
2322 )
2323 AND l_date_to <> hr_general.end_of_time
2324 AND l_new = 'N'
2325 AND i = 1
2326 ) -- end bug 3650967
2327 THEN
2328 FOR l_day IN
2329 p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2330 LOOP
2331 IF g_debug
2332 THEN
2333 hr_utility.set_location (l_proc, 30);
2334 END IF;
2335
2336 IF (p_time_building_blocks (l_day).time_building_block_id =
2337 l_parent_bb_id
2338 )
2339 AND (p_time_building_blocks (l_day).SCOPE = 'DAY')
2340 THEN
2341 l_start_time :=
2342 p_time_building_blocks (l_day).start_time;
2343 l_stop_time :=
2344 p_time_building_blocks (l_day).stop_time;
2345 l_no_times := 'Y';
2346
2347 IF g_debug
2348 THEN
2349 hr_utility.TRACE ( 'l_start_time is '
2350 || TO_CHAR
2351 (l_start_time,
2352 'DD-MON-YYYY HH:MI:SS'
2353 )
2354 );
2355 hr_utility.TRACE ( 'l_stop_time is '
2356 || TO_CHAR
2357 (l_stop_time,
2358 'DD-MON-YYYY HH:MI:SS'
2359 )
2360 );
2361 END IF;
2362
2363 EXIT;
2364 END IF;
2365 END LOOP;
2366 END IF; -- l_type = MEASURE
2367
2368 l_person_id := NULL;
2369
2370 IF l_resource_type = 'PERSON'
2371 THEN
2372 l_person_id := l_resource_id;
2373
2374 IF g_debug
2375 THEN
2376 hr_utility.TRACE ( 'l_person_id is '
2377 || TO_CHAR (l_person_id)
2378 );
2379 END IF;
2380 END IF;
2381
2382 l_effective_date := TRUNC (l_start_time);
2383
2384 IF g_debug
2385 THEN
2386 hr_utility.TRACE ( 'l_effective_date is :'
2387 || TO_CHAR (l_effective_date,
2388 'DD-MON-YYYY'
2389 )
2390 );
2391 END IF;
2392
2393 BEGIN
2394 SELECT full_name, business_group_id
2395 INTO g_full_name, l_bg_id
2396 FROM per_all_people_f
2397 WHERE person_id = l_person_id
2398 AND l_effective_date BETWEEN effective_start_date
2399 AND effective_end_date;
2400 EXCEPTION
2401 WHEN NO_DATA_FOUND
2402 THEN
2403 hxc_time_entry_rules_utils_pkg.add_error_to_table
2404 (p_message_table => g_messages,
2405 p_message_name => 'HR_52365_PTU_NO_PERSON_EXISTS',
2406 p_message_token => NULL,
2407 p_message_level => 'ERROR',
2408 p_message_field => NULL,
2409 p_application_short_name => 'PER',
2410 p_timecard_bb_id => l_bb_id,
2411 p_time_attribute_id => NULL,
2412 p_timecard_bb_ovn => l_bb_ovn,
2413 p_time_attribute_ovn => NULL
2414 );
2415 -- Bug 3012684
2416 --RAISE e_error;
2417 l_error_flag := 'Y';
2418 END;
2419
2420 hxt_otc_retrieval_interface.get_assignment_id
2421 (p_person_id => l_person_id,
2422 p_payroll_id => l_payroll_id,
2423 p_bg_id => l_bg_id,
2424 p_assignment_id => l_assignment_id,
2425 p_effective_date => l_effective_date
2426 );
2427
2428 IF g_debug
2429 THEN
2430 hr_utility.set_location (l_proc, 50);
2431 END IF;
2432
2433 l_field_name.DELETE;
2434 l_value.DELETE;
2435 l_category.DELETE;
2436 l_context.DELETE;
2437
2438 IF g_debug
2439 THEN
2440 hr_utility.set_location (l_proc, 55);
2441 hr_utility.TRACE ( 'number of attr is :'
2442 || TO_CHAR (p_time_attributes.COUNT)
2443 );
2444 hr_utility.set_location (l_proc, 56);
2445 hr_utility.TRACE ('l_person_id :' || l_person_id);
2446 END IF;
2447
2448 --
2449 -- Get the attributes for this detail building block.
2450 --
2451 IF p_time_attributes.COUNT <> 0
2452 THEN
2453 l_att := 1;
2454
2455 FOR l_cnt_att IN
2456 p_time_attributes.FIRST .. p_time_attributes.LAST
2457 LOOP
2458 IF g_debug
2459 THEN
2460 hr_utility.TRACE ('l_bb_id:' || l_bb_id);
2461 hr_utility.TRACE
2462 ( 'p_time_attributes'
2463 || (l_cnt_att)
2464 || '.building_block_id:'
2465 || p_time_attributes (l_cnt_att).building_block_id
2466 );
2467 END IF;
2468
2469 IF l_bb_id =
2470 p_time_attributes (l_cnt_att).building_block_id
2471 THEN
2472 IF g_debug
2473 THEN
2474 hr_utility.TRACE
2475 ('----------- In Attribute Loop ----------');
2476 END IF;
2477
2478 l_field_name (l_att) :=
2479 p_time_attributes (l_cnt_att).attribute_name;
2480
2481 IF g_debug
2482 THEN
2483 hr_utility.TRACE ( 'field name('
2484 || l_att
2485 || ') is :'
2486 || l_field_name (l_att)
2487 );
2488 END IF;
2489
2490 l_value (l_att) :=
2491 p_time_attributes (l_cnt_att).attribute_value;
2492
2493 IF g_debug
2494 THEN
2495 hr_utility.TRACE ( 'value('
2496 || l_att
2497 || ') is : '
2498 || l_value (l_att)
2499 );
2500 END IF;
2501
2502 l_context (l_att) :=
2503 p_time_attributes (l_cnt_att).bld_blk_info_type;
2504
2505 IF g_debug
2506 THEN
2507 hr_utility.TRACE ( 'context('
2508 || l_att
2509 || ') is :'
2510 || l_context (l_att)
2511 );
2512 END IF;
2513
2514 l_category (l_att) :=
2515 p_time_attributes (l_cnt_att).CATEGORY;
2516
2517 IF g_debug
2518 THEN
2519 hr_utility.TRACE ( 'category('
2520 || l_att
2521 || ') is :'
2522 || l_category (l_att)
2523 );
2524 END IF;
2525
2526 --
2527 -- Start Bug 2930933
2528 --
2529 IF g_debug
2530 THEN
2531 hr_utility.set_location (l_proc, 56.5);
2532 END IF;
2533
2534 IF l_field_name (l_att) = 'Dummy Element Context'
2535 AND l_context (l_att) = 'Dummy Element Context'
2536 AND l_category (l_att) = 'ELEMENT'
2537 THEN
2538 IF g_debug
2539 THEN
2540 hr_utility.set_location (l_proc, 56.6);
2541 END IF;
2542
2543 IF l_value (l_att) IS NOT NULL
2544 THEN
2545 IF g_debug
2546 THEN
2547 hr_utility.set_location (l_proc, 57);
2548 END IF;
2549
2550 l_hrstype_entered := 'Y';
2551 ELSE
2552 IF g_debug
2553 THEN
2554 hr_utility.set_location (l_proc, 58);
2555 END IF;
2556
2557 l_hrstype_entered := 'N';
2558 END IF;
2559
2560 IF g_debug
2561 THEN
2562 hr_utility.set_location (l_proc, 58.5);
2563 END IF;
2564 END IF;
2565
2566 IF g_debug
2567 THEN
2568 hr_utility.set_location (l_proc, 59);
2569 END IF;
2570
2571 -- End Bug 2930933
2572 l_att := l_att + 1;
2573 --
2574 -- p_time_attributes.delete(l_cnt_att);
2575 --
2576 END IF;
2577 END LOOP;
2578 END IF;
2579
2580 IF g_debug
2581 THEN
2582 hr_utility.TRACE ('l_att is: ' || TO_CHAR (l_att));
2583 END IF;
2584
2585 -- Bug 2930933
2586 IF g_debug
2587 THEN
2588 hr_utility.TRACE ( 'l_hrstype_entered :'
2589 || l_hrstype_entered
2590 );
2591 hr_utility.set_location (l_proc, 60);
2592 END IF;
2593
2594 IF l_hrstype_entered = 'N' AND i <> 1 -- Check for Bug 4548871
2595 THEN
2596 IF g_debug
2597 THEN
2598 hr_utility.set_location (l_proc, 61);
2599 END IF;
2600
2601 -- Raise an error
2602 hxc_time_entry_rules_utils_pkg.add_error_to_table
2603 (p_message_table => g_messages,
2604 p_message_name => 'HXC_366384_NO_HRS_TYPE_ERR',
2605 p_message_token => NULL,
2606 p_message_level => 'ERROR',
2607 p_message_field => NULL,
2608 p_application_short_name => 'HXC',
2609 p_timecard_bb_id => l_bb_id,
2610 p_time_attribute_id => NULL,
2611 p_timecard_bb_ovn => l_bb_ovn,
2612 p_time_attribute_ovn => NULL
2613 );
2614 -- Bug 3012684
2615 l_error_flag := 'Y';
2616 --RAISE e_error;
2617 END IF;
2618
2619 IF g_debug
2620 THEN
2621 hr_utility.set_location (l_proc, 62);
2622 END IF;
2623
2624 hxt_otc_retrieval_interface.parse_attributes
2625 (p_category => l_category,
2626 p_field_name => l_field_name,
2627 p_value => l_value,
2628 p_context => l_context,
2629 p_date_worked => l_date_worked,
2630 p_type => l_type,
2631 p_measure => l_measure,
2632 p_start_time => l_start_time,
2633 p_stop_time => l_stop_time,
2634 p_assignment_id => l_assignment_id,
2635 p_earn_policy => l_earn_policy, -- Bug 16598207
2636 p_hours => l_hours,
2637 p_hours_type => l_hours_type,
2638 p_segment => l_segment,
2639 p_project => l_project,
2640 p_task => l_task,
2641 p_state_name => l_state_name,
2642 p_county_name => l_county_name,
2643 p_city_name => l_city_name,
2644 p_zip_code => l_zip_code
2645 );
2646
2647 IF g_debug
2648 THEN
2649 hr_utility.set_location (l_proc, 63);
2650 END IF;
2651
2652 --
2653 -- Pass in Person ID for employee number - issue with going
2654 -- from employee number to person ID in OTM API. Hence bypass it
2655 -- and just pass in person ID.
2656 --
2657 IF (l_no_times = 'Y')
2658 THEN
2659 IF g_debug
2660 THEN
2661 hr_utility.set_location (l_proc, 64);
2662 END IF;
2663
2664 l_start_time := NULL;
2665 l_stop_time := NULL;
2666 END IF;
2667
2668 IF g_debug
2669 THEN
2670 hr_utility.set_location (l_proc, 65);
2671 END IF;
2672
2673 l_time_summary_id := NULL;
2674 l_time_sum_start_date := NULL;
2675 l_time_sum_end_date := NULL;
2676
2677 IF l_new = 'N'
2678 THEN
2679 IF g_debug
2680 THEN
2681 hr_utility.set_location (l_proc, 66);
2682 END IF;
2683
2684 -- Bug 7415291
2685 -- Added new parameter l_tim_id
2686 hxt_otc_retrieval_interface.find_existing_timecard
2687 (p_payroll_id => l_payroll_id,
2688 p_date_worked => l_date_worked,
2689 p_person_id => l_person_id,
2690 p_old_ovn => l_bb_ovn,
2691 p_bb_id => l_bb_id,
2692 p_time_summary_id => l_time_summary_id,
2693 p_time_sum_start_date => l_time_sum_start_date,
2694 p_time_sum_end_date => l_time_sum_end_date,
2695 p_tim_id => l_tim_id
2696 );
2697
2698 IF g_debug
2699 THEN
2700 hr_utility.TRACE ('after find_existing_timecard');
2701 hr_utility.TRACE ( 'l_time_summary_id is: '
2702 || TO_CHAR (l_time_summary_id)
2703 );
2704 END IF;
2705
2706 IF l_time_summary_id IS NOT NULL
2707 THEN
2708 IF g_debug
2709 THEN
2710 hr_utility.set_location (l_proc, 67);
2711 END IF;
2712
2713 DELETE FROM hxt_det_hours_worked_f
2714 WHERE parent_id = l_time_summary_id;
2715 END IF;
2716
2717 IF g_debug
2718 THEN
2719 hr_utility.set_location (l_proc, 68);
2720 END IF;
2721 END IF;
2722
2723 IF g_debug
2724 THEN
2725 hr_utility.set_location (l_proc, 69);
2726 END IF;
2727
2728 --Bug 2770487 Sonarasi 04-Apr-2003
2729 IF (l_date_to = hr_general.end_of_time)
2730 THEN
2731 IF g_debug
2732 THEN
2733 hr_utility.set_location (l_proc, 70);
2734 END IF;
2735
2736 l_delete := 'N';
2737 ELSE
2738 IF g_debug
2739 THEN
2740 hr_utility.set_location (l_proc, 71);
2741 END IF;
2742
2743 l_delete := 'Y';
2744 END IF;
2745
2746 IF g_debug
2747 THEN
2748 hr_utility.set_location (l_proc, 72);
2749 END IF;
2750
2751 --Here We are setting the delete flag based on whether we would like to
2752 --delete the blocks or retail them.Therefore those blocks which are end
2753 --dated will have the l_delete flag set to 'Y'. We will be passing the
2754 --l_delete as a value to the parameter delete_yn of the record_time api.
2755 --Bug 2770487 Sonarasi Over
2756 hxt_time_collection.record_time
2757 (timecard_source => 'Time Store',
2758 batch_ref => 'OTL_SS_DEP_VAL',
2759 batch_name => 'OTL_SS_DEP_VAL',
2760 approver_number => NULL,
2761 employee_number => TO_CHAR
2762 (l_person_id
2763 ),
2764 date_worked => l_date_worked,
2765 start_time => l_start_time,
2766 end_time => l_stop_time,
2767 hours => l_hours,
2768 wage_code => NULL,
2769 earning_policy => l_earn_policy, -- Bug 16598207
2770 hours_type => l_hours_type,
2771 earn_reason_code => NULL,
2772 project => NULL,
2773 task_number => NULL,
2774 location_code => NULL,
2775 COMMENT => NULL,
2776 rate_multiple => NULL,
2777 hourly_rate => NULL,
2778 amount => NULL,
2779 separate_check_flag => NULL,
2780 business_group_id => l_bg_id,
2781 cost_segment1 => l_segment (1),
2782 cost_segment2 => l_segment (2),
2783 cost_segment3 => l_segment (3),
2784 cost_segment4 => l_segment (4),
2785 cost_segment5 => l_segment (5),
2786 cost_segment6 => l_segment (6),
2787 cost_segment7 => l_segment (7),
2788 cost_segment8 => l_segment (8),
2789 cost_segment9 => l_segment (9),
2790 cost_segment10 => l_segment (10),
2791 cost_segment11 => l_segment (11),
2792 cost_segment12 => l_segment (12),
2793 cost_segment13 => l_segment (13),
2794 cost_segment14 => l_segment (14),
2795 cost_segment15 => l_segment (15),
2796 cost_segment16 => l_segment (16),
2797 cost_segment17 => l_segment (17),
2798 cost_segment18 => l_segment (18),
2799 cost_segment19 => l_segment (19),
2800 cost_segment20 => l_segment (20),
2801 cost_segment21 => l_segment (21),
2802 cost_segment22 => l_segment (22),
2803 cost_segment23 => l_segment (23),
2804 cost_segment24 => l_segment (24),
2805 cost_segment25 => l_segment (25),
2806 cost_segment26 => l_segment (26),
2807 cost_segment27 => l_segment (27),
2808 cost_segment28 => l_segment (28),
2809 cost_segment29 => l_segment (29),
2810 cost_segment30 => l_segment (30),
2811 time_summary_id => l_time_summary_id,
2812 tim_sum_eff_start_date => l_time_sum_start_date,
2813 tim_sum_eff_end_date => l_time_sum_end_date,
2814 created_by => '-1',
2815 last_updated_by => '-1',
2816 last_update_login => '-1',
2817 dt_update_mode => 'CORRECTION',
2818 created_tim_sum_id => l_created_tim_sum_id,
2819 otm_error => l_otm_error,
2820 oracle_error => l_oracle_error,
2821 p_time_building_block_id => l_bb_id,
2822 p_time_building_block_ovn => l_bb_ovn,
2823 p_validate => FALSE,
2824 delete_yn => l_delete,
2825 p_state_name => l_state_name,
2826 p_county_name => l_county_name,
2827 p_city_name => l_city_name,
2828 p_zip_code => l_zip_code
2829 );
2830
2831 IF g_debug
2832 THEN
2833 hr_utility.set_location (l_proc, 73);
2834 END IF;
2835
2836 IF g_otm_messages.COUNT > 0
2837 THEN
2838 FOR i IN g_otm_messages.FIRST .. g_otm_messages.LAST
2839 LOOP
2840 hxc_time_entry_rules_utils_pkg.add_error_to_table
2841 (p_message_table => g_messages,
2842 p_message_name => g_otm_messages (i).message_name,
2843 p_message_token => g_otm_messages (i).message_tokens,
2844 p_message_level => g_otm_messages (i).message_level,
2845 p_message_field => NULL,
2846 p_application_short_name => g_otm_messages (i).application_short_name,
2847 p_timecard_bb_id => l_bb_id,
2848 p_time_attribute_id => NULL,
2849 p_timecard_bb_ovn => l_bb_ovn,
2850 p_time_attribute_ovn => NULL
2851 );
2852 END LOOP;
2853
2854 l_error_flag := 'Y';
2855 l_otm_error := NULL;
2856 l_oracle_error := NULL;
2857 g_otm_messages.DELETE;
2858 ELSE
2859 --to capture any errors which are not added to g_otm_messages table but
2860 --l_otm_error has not null values
2861 IF l_otm_error IS NOT NULL
2862 THEN
2863 IF g_debug
2864 THEN
2865 hr_utility.set_location (l_proc, 74);
2866 hr_utility.TRACE ('l_otm_error :' || l_otm_error);
2867 END IF;
2868
2869 hxc_time_entry_rules_utils_pkg.add_error_to_table
2870 (p_message_table => g_messages,
2871 p_message_name => 'HXC_HXT_DEP_VAL_OTMERR',
2872 p_message_token => SUBSTR
2873 ( 'ERROR&'
2874 || l_otm_error,
2875 1,
2876 100
2877 ),
2878 p_message_level => 'ERROR',
2879 p_message_field => NULL,
2880 p_application_short_name => 'HXC',
2881 p_timecard_bb_id => l_bb_id,
2882 p_time_attribute_id => NULL,
2883 p_timecard_bb_ovn => l_bb_ovn,
2884 p_time_attribute_ovn => NULL
2885 );
2886
2887 IF g_debug
2888 THEN
2889 hr_utility.TRACE ( 'g_messages.message_name is : '
2890 || g_messages (1).message_name
2891 );
2892 hr_utility.set_location (l_proc, 75);
2893 END IF;
2894
2895 -- Bug 3012684
2896 l_error_flag := 'Y';
2897 --RAISE e_error;
2898 END IF;
2899
2900 IF g_debug
2901 THEN
2902 hr_utility.set_location (l_proc, 76);
2903 END IF;
2904
2905 IF l_oracle_error IS NOT NULL
2906 THEN
2907 IF g_debug
2908 THEN
2909 hr_utility.set_location (l_proc, 77);
2910 hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
2911 );
2912 END IF;
2913
2914 hxc_time_entry_rules_utils_pkg.add_error_to_table
2915 (p_message_table => g_messages,
2916 p_message_name => 'HXC_HXT_DEP_VAL_ORAERR',
2917 p_message_token => 'ERROR&'
2918 || l_oracle_error,
2919 p_message_level => 'ERROR',
2920 p_message_field => NULL,
2921 p_application_short_name => 'HXC',
2922 p_timecard_bb_id => l_bb_id,
2923 p_time_attribute_id => NULL,
2924 p_timecard_bb_ovn => l_bb_ovn,
2925 p_time_attribute_ovn => NULL
2926 );
2927
2928 IF g_debug
2929 THEN
2930 hr_utility.TRACE ( 'g_messages.message_name is : '
2931 || g_messages (1).message_name
2932 );
2933 hr_utility.set_location (l_proc, 78);
2934 END IF;
2935
2936 -- Bug 3012684
2937 l_error_flag := 'Y';
2938 -- RAISE e_error;
2939 END IF;
2940 END IF;
2941
2942 IF g_debug
2943 THEN
2944 hr_utility.set_location (l_proc, 79);
2945 END IF;
2946
2947 l_next_index := l_timecard_detail.COUNT + 1;
2948 l_time_building_block_id := l_time_building_block_id - 1;
2949 l_time_attribute_id := l_time_attribute_id + 1;
2950 l_tim_sum_id_tab (l_next_index) := l_created_tim_sum_id;
2951 l_timecard_detail (l_next_index).time_building_block_id :=
2952 l_time_building_block_id;
2953 l_timecard_detail (l_next_index).unit_of_measure := 'HOURS';
2954 l_timecard_detail (l_next_index).parent_building_block_id :=
2955 l_parent_bb_id;
2956 l_timecard_detail (l_next_index).parent_building_block_ovn :=
2957 l_parent_bb_ovn;
2958 l_timecard_detail (l_next_index).parent_is_new := l_parent_new;
2959 l_timecard_detail (l_next_index).SCOPE := 'DETAIL';
2960 l_timecard_detail (l_next_index).object_version_number := NULL;
2961 l_timecard_detail (l_next_index).approval_status :=
2962 l_appr_status;
2963 l_timecard_detail (l_next_index).resource_id := l_resource_id;
2964 l_timecard_detail (l_next_index).resource_type :=
2965 l_resource_type;
2966 l_timecard_detail (l_next_index).approval_style_id :=
2967 l_appr_style_id;
2968 l_timecard_detail (l_next_index).date_from := l_date_from;
2969 l_timecard_detail (l_next_index).date_to := l_date_to;
2970 l_timecard_detail (l_next_index).comment_text := l_comment_text;
2971 l_timecard_detail (l_next_index).NEW := 'Y';
2972 l_timecard_detail (l_next_index).changed := 'Y';
2973 l_detail_attributes (l_next_index).time_attribute_id :=
2974 l_time_attribute_id;
2975 l_detail_attributes (l_next_index).building_block_id :=
2976 l_time_building_block_id;
2977 l_detail_attributes (l_next_index).attribute_name :=
2978 'Dummy Element Context';
2979 l_detail_attributes (l_next_index).bld_blk_info_type :=
2980 'Dummy Element Context';
2981 l_detail_attributes (l_next_index).CATEGORY := 'ELEMENT';
2982 l_detail_attributes (l_next_index).updated := NULL;
2983 l_detail_attributes (l_next_index).changed := NULL;
2984
2985 -- Get rid of this DETAIL record - it will be copied over
2986 -- in the end.
2987 --
2988 -- p_time_building_blocks.delete(l_cnt);
2989 IF g_debug
2990 THEN
2991 hr_utility.set_location (l_proc, 80);
2992 END IF;
2993 END IF; -- l_valid = Y
2994
2995 IF g_debug
2996 THEN
2997 hr_utility.set_location (l_proc, 81);
2998 END IF;
2999
3000 l_hrstype_entered := 'N';
3001
3002 IF g_debug
3003 THEN
3004 hr_utility.TRACE ('l_hrstype_entered :' || l_hrstype_entered);
3005 hr_utility.set_location (l_proc, 81);
3006 END IF;
3007 END LOOP;
3008 END LOOP;
3009
3010 -- Bug 3012684
3011 IF (l_error_flag = 'Y')
3012 THEN
3013 RAISE e_error;
3014 END IF;
3015
3016 l_timecards.DELETE;
3017 l_tim_sum := l_tim_sum_id_tab.FIRST;
3018
3019 LOOP
3020 EXIT WHEN NOT l_tim_sum_id_tab.EXISTS (l_tim_sum);
3021
3022 --Bug 2770487 Sonarasi 04-Apr-2003
3023 --the following if condition i.e if l_tim_sum_id_tab(l_tim_sum) is not null then
3024 --is added because incase of deleted blocks we may have null time summary ids
3025 --this may cause problems if the time summary id table returns a null value
3026 --Hence adding a check to prevent that scenario.
3027 --Bug 2770487 Sonarasi Over
3028 IF l_tim_sum_id_tab (l_tim_sum) IS NOT NULL
3029 THEN
3030 DELETE FROM hxt_det_hours_worked_f
3031 WHERE parent_id = l_tim_sum_id_tab (l_tim_sum);
3032
3033 OPEN get_timecard_id (p_tim_sum_id => l_tim_sum_id_tab
3034 (l_tim_sum)
3035 );
3036
3037 FETCH get_timecard_id
3038 INTO l_timecard_id, l_time_period_id;
3039
3040 IF (get_timecard_id%FOUND)
3041 THEN
3042 IF g_debug
3043 THEN
3044 hr_utility.TRACE ( 'TIM_SUM_ID IS : '
3045 || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
3046 );
3047 hr_utility.TRACE ( 'l_timecard_id is : '
3048 || TO_CHAR (l_timecard_id)
3049 );
3050 hr_utility.TRACE ( 'l_time_period_id is : '
3051 || TO_CHAR (l_time_period_id)
3052 );
3053 END IF;
3054
3055 IF (NOT l_timecards.EXISTS (l_time_period_id))
3056 THEN
3057 l_timecards (l_time_period_id) := l_timecard_id;
3058 END IF;
3059 END IF;
3060
3061 CLOSE get_timecard_id;
3062 END IF; --if l_tim_sum_id_tab(l_tim_sum) is not null then
3063
3064 l_tim_sum := l_tim_sum_id_tab.NEXT (l_tim_sum);
3065 END LOOP;
3066
3067 loop_ok := TRUE;
3068 i := l_timecards.FIRST;
3069
3070 IF i IS NOT NULL
3071 THEN
3072 WHILE loop_ok
3073 LOOP
3074 hxt_time_collection.re_explode_timecard
3075 (timecard_id => l_timecards
3076 (i),
3077 tim_eff_start_date => NULL,
3078 -- Not Being Used
3079 tim_eff_end_date => NULL,
3080 -- Not Being Used
3081 dt_update_mode => 'CORRECTION',
3082 otm_error => l_otm_error,
3083 oracle_error => l_oracle_error
3084 );
3085
3086 IF g_otm_messages.COUNT > 0
3087 THEN
3088 FOR i IN g_otm_messages.FIRST .. g_otm_messages.LAST
3089 LOOP
3090 hxc_time_entry_rules_utils_pkg.add_error_to_table
3091 (p_message_table => g_messages,
3092 p_message_name => g_otm_messages (i).message_name,
3093 p_message_token => g_otm_messages (i).message_tokens,
3094 p_message_level => g_otm_messages (i).message_level,
3095 p_message_field => NULL,
3096 p_application_short_name => g_otm_messages (i).application_short_name,
3097 p_timecard_bb_id => l_bb_id,
3098 p_time_attribute_id => NULL,
3099 p_timecard_bb_ovn => l_bb_ovn,
3100 p_time_attribute_ovn => NULL
3101 );
3102 END LOOP;
3103
3104 g_otm_messages.DELETE;
3105 l_otm_error := NULL;
3106 l_oracle_error := NULL;
3107 RAISE e_error;
3108 ELSE
3109 --to capture any errors which are not added to g_otm_messages table but
3110 --l_otm_error has not null values
3111 IF l_otm_error IS NOT NULL
3112 THEN
3113 IF g_debug
3114 THEN
3115 hr_utility.set_location (l_proc, 2000);
3116 hr_utility.TRACE ('l_otm_error :' || l_otm_error);
3117 END IF;
3118
3119 hxc_time_entry_rules_utils_pkg.add_error_to_table
3120 (p_message_table => g_messages,
3121 p_message_name => 'HXC_HXT_DEP_VAL_OTMERR',
3122 p_message_token => SUBSTR
3123 ( 'ERROR&'
3124 || l_otm_error,
3125 1,
3126 100
3127 ),
3128 p_message_level => 'ERROR',
3129 p_message_field => NULL,
3130 p_application_short_name => 'HXC',
3131 p_timecard_bb_id => l_bb_id,
3132 p_time_attribute_id => NULL,
3133 p_timecard_bb_ovn => l_bb_ovn,
3134 p_time_attribute_ovn => NULL
3135 );
3136
3137 IF g_debug
3138 THEN
3139 hr_utility.TRACE ( 'g_messages.message_name is : '
3140 || g_messages (1).message_name
3141 );
3142 END IF;
3143
3144 RAISE e_error;
3145 END IF;
3146
3147 IF l_oracle_error IS NOT NULL
3148 THEN
3149 IF g_debug
3150 THEN
3151 hr_utility.set_location (l_proc, 2050);
3152 hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
3153 END IF;
3154
3155 hxc_time_entry_rules_utils_pkg.add_error_to_table
3156 (p_message_table => g_messages,
3157 p_message_name => 'HXC_HXT_DEP_VAL_ORAERR',
3158 p_message_token => 'ERROR&'
3159 || l_oracle_error,
3160 p_message_level => 'ERROR',
3161 p_message_field => NULL,
3162 p_application_short_name => 'HXC',
3163 p_timecard_bb_id => l_bb_id,
3164 p_time_attribute_id => NULL,
3165 p_timecard_bb_ovn => l_bb_ovn,
3166 p_time_attribute_ovn => NULL
3167 );
3168
3169 IF g_debug
3170 THEN
3171 hr_utility.TRACE ( 'g_messages.message_name is : '
3172 || g_messages (1).message_name
3173 );
3174 END IF;
3175
3176 RAISE e_error;
3177 END IF;
3178 END IF;
3179
3180 IF g_debug
3181 THEN
3182 hr_utility.set_location (l_proc, 2055);
3183 END IF;
3184
3185 i := l_timecards.NEXT (i);
3186
3187 IF i IS NULL
3188 THEN
3189 IF g_debug
3190 THEN
3191 hr_utility.set_location (l_proc, 2060);
3192 END IF;
3193
3194 loop_ok := FALSE;
3195 END IF;
3196
3197 IF g_debug
3198 THEN
3199 hr_utility.set_location (l_proc, 2065);
3200 END IF;
3201 END LOOP;
3202
3203 IF g_debug
3204 THEN
3205 hr_utility.set_location (l_proc, 2070);
3206 END IF;
3207 END IF;
3208
3209 IF g_debug
3210 THEN
3211 hr_utility.set_location (l_proc, 2075);
3212 END IF;
3213
3214 l_next_index := 0;
3215 l_tim_sum := l_tim_sum_id_tab.FIRST;
3216
3217 LOOP
3218 EXIT WHEN NOT l_tim_sum_id_tab.EXISTS (l_tim_sum);
3219
3220 --Bug 2770487 Sonarasi 04-Apr-2003
3221 --the following if condition i.e if l_tim_sum_id_tab(l_tim_sum) is not null then
3222 --is added because incase of deleted blocks we may have null time summary ids
3223 --this may cause problems if the time summary id table returns a null value
3224 --Hence adding a check to prevent that scenario.
3225 --Bug 2770487 Sonarasi Over
3226 IF l_tim_sum_id_tab (l_tim_sum) IS NOT NULL
3227 THEN
3228 IF g_debug
3229 THEN
3230 hr_utility.set_location (l_proc, 2080);
3231 END IF;
3232
3233 l_master_index := l_tim_sum;
3234 l_num_rec := 0;
3235
3236 IF g_debug
3237 THEN
3238 hr_utility.TRACE ( 'TIM_SUM_ID IS : '
3239 || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
3240 );
3241 END IF;
3242
3243 --
3244 -- select count(*)
3245 -- into l_det_cnt
3246 -- from hxt_det_hours_worked_f
3247 -- where parent_id = l_tim_sum_id_tab(l_tim_sum);
3248 --if g_debug then
3249 -- hr_utility.trace('l_det_cnt IS : ' || to_char(l_det_cnt));
3250 --end if;
3251 --
3252 OPEN get_otm_records (p_tim_sum_id => l_tim_sum_id_tab
3253 (l_tim_sum));
3254
3255 LOOP
3256 IF g_debug
3257 THEN
3258 hr_utility.set_location (l_proc, 2085);
3259 END IF;
3260
3261 FETCH get_otm_records
3262 INTO detail_date_worked, detail_hours, detail_time_in,
3263 detail_time_out, detail_hours_type;
3264
3265 IF g_debug
3266 THEN
3267 hr_utility.TRACE ( 'detail_date_worked :'
3268 || detail_date_worked
3269 );
3270 hr_utility.TRACE ('detail_hours :' || detail_hours);
3271 hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3272 hr_utility.TRACE ('detail_time_out :' || detail_time_out);
3273 hr_utility.TRACE ('detail_hours_type :'
3274 || detail_hours_type
3275 );
3276 END IF;
3277
3278 EXIT WHEN get_otm_records%NOTFOUND;
3279
3280 --
3281 IF g_debug
3282 THEN
3283 hr_utility.set_location (l_proc, 2085);
3284 END IF;
3285
3286 l_num_rec := l_num_rec + 1;
3287
3288 IF g_debug
3289 THEN
3290 hr_utility.TRACE ('l_num_rec :' || l_num_rec);
3291 hr_utility.TRACE ( 'detail_date_worked is : '
3292 || TO_CHAR (detail_date_worked,
3293 'DD-MON-YYYY HH:MI:SS'
3294 )
3295 );
3296 hr_utility.TRACE ( 'detail_hours is : '
3297 || TO_CHAR (detail_hours)
3298 );
3299 hr_utility.TRACE ( 'detail_time_in is : '
3300 || TO_CHAR (detail_time_in,
3301 'DD-MON-YYYY HH:MI:SS'
3302 )
3303 );
3304 hr_utility.TRACE ( 'detail_time_out is : '
3305 || TO_CHAR (detail_time_out,
3306 'DD-MON-YYYY HH:MI:SS'
3307 )
3308 );
3309 hr_utility.TRACE ( 'detail_hours_type : '
3310 || TO_CHAR (detail_hours_type)
3311 );
3312 END IF;
3313
3314 IF l_num_rec = 1
3315 THEN
3316 --
3317 IF g_debug
3318 THEN
3319 hr_utility.set_location (l_proc, 2090);
3320 hr_utility.TRACE ('l_num_rec is 1');
3321 END IF;
3322
3323 --
3324 l_next_index := l_tim_sum;
3325
3326 IF g_debug
3327 THEN
3328 hr_utility.TRACE ('l_next_index :' || l_next_index);
3329 END IF;
3330 ELSE
3331 IF g_debug
3332 THEN
3333 hr_utility.set_location (l_proc, 2095);
3334 hr_utility.TRACE ('l_num_rec is NOT 1');
3335 END IF;
3336
3337 l_next_index := l_timecard_detail.COUNT + 1;
3338 l_time_building_block_id := l_time_building_block_id - 1;
3339 l_timecard_detail (l_next_index).time_building_block_id :=
3340 l_time_building_block_id;
3341 l_timecard_detail (l_next_index).unit_of_measure := 'HOURS';
3342 l_timecard_detail (l_next_index).parent_building_block_id :=
3343 l_timecard_detail (l_master_index).parent_building_block_id;
3344 l_timecard_detail (l_next_index).parent_building_block_ovn :=
3345 l_timecard_detail (l_master_index).parent_building_block_ovn;
3346 l_timecard_detail (l_next_index).parent_is_new :=
3347 l_timecard_detail (l_master_index).parent_is_new;
3348 l_timecard_detail (l_next_index).SCOPE := 'DETAIL';
3349 l_timecard_detail (l_next_index).object_version_number :=
3350 NULL;
3351 l_timecard_detail (l_next_index).approval_status :=
3352 l_timecard_detail (l_master_index).approval_status;
3353 l_timecard_detail (l_next_index).resource_id :=
3354 l_timecard_detail (l_master_index).resource_id;
3355 l_timecard_detail (l_next_index).resource_type :=
3356 l_timecard_detail (l_master_index).resource_type;
3357 l_timecard_detail (l_next_index).approval_style_id :=
3358 l_timecard_detail (l_master_index).approval_style_id;
3359 l_timecard_detail (l_next_index).date_from :=
3360 l_timecard_detail (l_master_index).date_from;
3361 l_timecard_detail (l_next_index).date_to :=
3362 l_timecard_detail (l_master_index).date_to;
3363 l_timecard_detail (l_next_index).comment_text :=
3364 l_timecard_detail (l_master_index).comment_text;
3365 l_timecard_detail (l_next_index).NEW := 'Y';
3366 l_timecard_detail (l_next_index).changed := 'Y';
3367 l_detail_attributes (l_next_index).time_attribute_id :=
3368 l_detail_attributes (l_master_index).time_attribute_id;
3369 l_detail_attributes (l_next_index).building_block_id :=
3370 l_time_building_block_id;
3371 l_detail_attributes (l_next_index).attribute_name :=
3372 'Dummy Element Context';
3373 l_detail_attributes (l_next_index).bld_blk_info_type :=
3374 'Dummy Element Context';
3375 l_detail_attributes (l_next_index).CATEGORY := 'ELEMENT';
3376 l_detail_attributes (l_next_index).updated := NULL;
3377 l_detail_attributes (l_next_index).changed := NULL;
3378 END IF;
3379
3380 IF detail_hours IS NULL
3381 THEN
3382 IF g_debug
3383 THEN
3384 hr_utility.set_location (l_proc, 3000);
3385 END IF;
3386
3387 detail_type := 'RANGE';
3388 ELSE
3389 IF g_debug
3390 THEN
3391 hr_utility.set_location (l_proc, 3005);
3392 END IF;
3393
3394 detail_type := 'MEASURE';
3395 END IF;
3396
3397 -- Populate the pl/sql tables before rolling back to the savepoint.
3398 IF g_debug
3399 THEN
3400 hr_utility.set_location (l_proc, 3010);
3401 hr_utility.TRACE ( 'l_next_index is : '
3402 || TO_CHAR (l_next_index)
3403 );
3404 END IF;
3405
3406 l_timecard_detail (l_next_index).TYPE := detail_type;
3407 l_timecard_detail (l_next_index).measure := detail_hours;
3408 l_timecard_detail (l_next_index).start_time := detail_time_in;
3409 l_timecard_detail (l_next_index).stop_time := detail_time_out;
3410 l_detail_attributes (l_next_index).attribute_value :=
3411 'ELEMENT' || ' ' || '-' || ' ' || detail_hours_type;
3412
3413 -- Bug 8486310
3414 -- Call the below procedure to store the Alternate Name identifier
3415 -- associated with this element
3416 save_an_ids (l_detail_attributes(l_next_index).attribute_value);
3417
3418 IF g_debug
3419 THEN
3420 hr_utility.TRACE ('done');
3421 hr_utility.set_location (l_proc, 3015);
3422 END IF;
3423 END LOOP;
3424
3425 IF g_debug
3426 THEN
3427 hr_utility.set_location (l_proc, 3020);
3428 hr_utility.TRACE ('After End Loop');
3429 END IF;
3430
3431 CLOSE get_otm_records;
3432 END IF; --if l_tim_sum_id_tab(l_tim_sum) is not null then
3433
3434 IF g_debug
3435 THEN
3436 hr_utility.set_location (l_proc, 3025);
3437 END IF;
3438
3439 l_tim_sum := l_tim_sum_id_tab.NEXT (l_tim_sum);
3440 END LOOP;
3441
3442 IF g_debug
3443 THEN
3444 hr_utility.set_location (l_proc, 3030);
3445 hr_utility.set_location (l_proc, 3035);
3446 END IF;
3447
3448 ROLLBACK TO review_details;
3449
3450 IF g_debug
3451 THEN
3452 hr_utility.set_location (l_proc, 3040);
3453 hr_utility.TRACE ('After RollBack');
3454 hr_utility.set_location (l_proc, 3045);
3455 END IF;
3456
3457 IF l_timecard_detail.COUNT <> 0
3458 THEN
3459 IF g_debug
3460 THEN
3461 hr_utility.set_location (l_proc, 3050);
3462 END IF;
3463
3464 FOR l_cnt IN l_timecard_detail.FIRST .. l_timecard_detail.LAST
3465 LOOP
3466 IF g_debug
3467 THEN
3468 hr_utility.TRACE
3469 ( 'l_timecard_detail BB ID is : '
3470 || TO_CHAR
3471 (l_timecard_detail (l_cnt).time_building_block_id
3472 )
3473 );
3474 END IF;
3475 END LOOP;
3476
3477 FOR l_cnt IN l_detail_attributes.FIRST .. l_detail_attributes.LAST
3478 LOOP
3479 IF g_debug
3480 THEN
3481 hr_utility.TRACE
3482 ( 'l_detail_attributes BB ID is : '
3483 || TO_CHAR
3484 (l_detail_attributes (l_cnt).building_block_id
3485 )
3486 );
3487 END IF;
3488 END LOOP;
3489
3490 IF g_debug
3491 THEN
3492 hr_utility.set_location (l_proc, 3055);
3493 END IF;
3494 END IF;
3495
3496 IF g_debug
3497 THEN
3498 hr_utility.set_location (l_proc, 3060);
3499 hr_utility.TRACE ('END FYI');
3500 END IF;
3501
3502 p_messages := g_messages;
3503 p_detail_build_blocks := l_timecard_detail;
3504 p_detail_attributes := build_attributes (l_detail_attributes);
3505
3506 IF p_detail_build_blocks.COUNT <> 0
3507 THEN
3508 IF g_debug
3509 THEN
3510 hr_utility.set_location (l_proc, 3065);
3511
3512 -- Bug 8486310
3513 -- Nothing relevant to the bug, but the below construct is illogical.
3514 -- You actually need to loop only if you have the trace set.
3515 -- Rewriting it.
3516 -- And why do we need these successive IFs ?
3517 FOR l_cnt IN
3518 p_detail_build_blocks.FIRST .. p_detail_build_blocks.LAST
3519 LOOP
3520 hr_utility.TRACE
3521 ( 'p_detail_build_blocks BB ID is : '
3522 || TO_CHAR
3523 (p_detail_build_blocks (l_cnt).time_building_block_id
3524 )
3525 );
3526 hr_utility.TRACE ( 'p_detail_build_blocks Hours is : '
3527 || TO_CHAR
3528 (p_detail_build_blocks (l_cnt).measure
3529 )
3530 );
3531 END LOOP;
3532
3533 FOR l_cnt IN p_detail_attributes.FIRST .. p_detail_attributes.LAST
3534 LOOP
3535 hr_utility.TRACE
3536 ( 'p_detail_attributes BB ID is : '
3537 || TO_CHAR
3538 (p_detail_attributes (l_cnt).building_block_id
3539 )
3540 );
3541 hr_utility.TRACE ( 'p_detail_attributes ATTR category is : '
3542 || p_detail_attributes (l_cnt).attribute_category
3543 );
3544 END LOOP;
3545
3546 hr_utility.set_location (l_proc, 3070);
3547 END IF;
3548 END IF;
3549
3550 hr_kflex_utility.unset_session_date (p_session_id => l_session_id);
3551 EXCEPTION
3552 WHEN e_error
3553 THEN
3554 IF g_debug
3555 THEN
3556 hr_utility.set_location (l_proc, 3075);
3557 END IF;
3558
3559 p_messages := g_messages;
3560 -- Rollback to the savepoint
3561 ROLLBACK TO review_details;
3562 RETURN;
3563
3564 IF g_debug
3565 THEN
3566 hr_utility.set_location (l_proc, 3080);
3567 hr_utility.TRACE ('THE END');
3568 END IF;
3569 WHEN OTHERS
3570 THEN
3571 IF g_debug
3572 THEN
3573 hr_utility.set_location (l_proc, 4000);
3574 END IF;
3575
3576 p_messages := g_messages;
3577 -- Rollback to the savepoint
3578 ROLLBACK TO review_details;
3579 RETURN;
3580 END otlr_review_details;
3581
3582 --------------------------- build_attributes ---------------------------
3583 FUNCTION build_attributes (
3584 p_detail_attributes IN hxc_self_service_time_deposit.app_attributes_info
3585 )
3586 RETURN hxc_self_service_time_deposit.building_block_attribute_info
3587 IS
3588 CURSOR csr_wtd_components (
3589 p_deposit_process_id NUMBER,
3590 p_attribute_category VARCHAR2,
3591 p_field_name VARCHAR2
3592 )
3593 IS
3594 SELECT mc.SEGMENT, bbit.bld_blk_info_type_id
3595 FROM hxc_mapping_components mc,
3596 hxc_mapping_comp_usages mcu,
3597 hxc_mappings m,
3598 hxc_deposit_processes dp,
3599 hxc_bld_blk_info_types bbit,
3600 hxc_bld_blk_info_type_usages bbui
3601 WHERE dp.mapping_id = m.mapping_id
3602 AND dp.deposit_process_id = p_deposit_process_id --AI3
3603 AND m.mapping_id = mcu.mapping_id
3604 AND mcu.mapping_component_id = mc.mapping_component_id
3605 AND mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
3606 AND mc.field_name = p_field_name
3607 AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
3608 AND bbit.bld_blk_info_type = p_attribute_category;
3609
3610 l_attributes hxc_self_service_time_deposit.building_block_attribute_info;
3611 l_attribute BINARY_INTEGER;
3612 l_attribute_index BINARY_INTEGER := 0;
3613 l_proc VARCHAR2 (70) := 'BUILD_ATTRIBUTES';
3614 l_exception EXCEPTION;
3615 l_deposit_process_id NUMBER;
3616 l_attribute_category hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
3617 l_segment hxc_mapping_components.SEGMENT%TYPE;
3618 l_bld_blk_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
3619 -- Bug 8486310
3620 -- The below variables for AN Id calculation
3621 l_bbit hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
3622 l_hours_type VARCHAR2(50);
3623 BEGIN
3624 SELECT dp.deposit_process_id
3625 INTO l_deposit_process_id
3626 FROM hxc_deposit_processes dp
3627 WHERE dp.NAME = 'OTL Deposit Process';
3628
3629
3630 -- Bug 8486310
3631 -- Pretty sure that this would return only one value, so no need of a cursor.
3632 SELECT bld_blk_info_type_id
3633 INTO l_bbit
3634 FROM hxc_bld_blk_info_types
3635 WHERE bld_blk_info_type = 'ALTERNATE NAME IDENTIFIERS';
3636
3637 l_attributes.DELETE;
3638 l_attribute := p_detail_attributes.FIRST;
3639
3640 LOOP
3641 EXIT WHEN NOT p_detail_attributes.EXISTS (l_attribute);
3642
3643 OPEN csr_wtd_components
3644 (l_deposit_process_id,
3645 p_detail_attributes (l_attribute).bld_blk_info_type,
3646 p_detail_attributes (l_attribute).attribute_name
3647 );
3648
3649 FETCH csr_wtd_components
3650 INTO l_segment, l_bld_blk_info_type_id;
3651
3652 CLOSE csr_wtd_components;
3653
3654 l_attribute_index := l_attribute_index + 1;
3655 l_attributes (l_attribute_index).time_attribute_id :=
3656 p_detail_attributes (l_attribute).time_attribute_id;
3657 l_attributes (l_attribute_index).building_block_id :=
3658 p_detail_attributes (l_attribute).building_block_id;
3659 l_attributes (l_attribute_index).bld_blk_info_type :=
3660 p_detail_attributes (l_attribute).bld_blk_info_type;
3661 l_attributes (l_attribute_index).changed :=
3662 p_detail_attributes (l_attribute).changed;
3663 l_attributes (l_attribute_index).bld_blk_info_type_id :=
3664 l_bld_blk_info_type_id;
3665 l_attributes (l_attribute_index).NEW := 'Y';
3666
3667 IF l_segment = 'ATTRIBUTE1'
3668 THEN
3669 l_attributes (l_attribute_index).attribute1 :=
3670 p_detail_attributes (l_attribute).attribute_value;
3671 ELSIF l_segment = 'ATTRIBUTE2'
3672 THEN
3673 l_attributes (l_attribute_index).attribute2 :=
3674 p_detail_attributes (l_attribute).attribute_value;
3675 ELSIF l_segment = 'ATTRIBUTE3'
3676 THEN
3677 l_attributes (l_attribute_index).attribute3 :=
3678 p_detail_attributes (l_attribute).attribute_value;
3679 ELSIF l_segment = 'ATTRIBUTE4'
3680 THEN
3681 l_attributes (l_attribute_index).attribute4 :=
3682 p_detail_attributes (l_attribute).attribute_value;
3683 ELSIF l_segment = 'ATTRIBUTE5'
3684 THEN
3685 l_attributes (l_attribute_index).attribute5 :=
3686 p_detail_attributes (l_attribute).attribute_value;
3687 ELSIF l_segment = 'ATTRIBUTE6'
3688 THEN
3689 l_attributes (l_attribute_index).attribute6 :=
3690 p_detail_attributes (l_attribute).attribute_value;
3691 ELSIF l_segment = 'ATTRIBUTE7'
3692 THEN
3693 l_attributes (l_attribute_index).attribute7 :=
3694 p_detail_attributes (l_attribute).attribute_value;
3695 ELSIF l_segment = 'ATTRIBUTE8'
3696 THEN
3697 l_attributes (l_attribute_index).attribute8 :=
3698 p_detail_attributes (l_attribute).attribute_value;
3699 ELSIF l_segment = 'ATTRIBUTE9'
3700 THEN
3701 l_attributes (l_attribute_index).attribute9 :=
3702 p_detail_attributes (l_attribute).attribute_value;
3703 ELSIF l_segment = 'ATTRIBUTE10'
3704 THEN
3705 l_attributes (l_attribute_index).attribute10 :=
3706 p_detail_attributes (l_attribute).attribute_value;
3707 ELSIF l_segment = 'ATTRIBUTE11'
3708 THEN
3709 l_attributes (l_attribute_index).attribute11 :=
3710 p_detail_attributes (l_attribute).attribute_value;
3711 ELSIF l_segment = 'ATTRIBUTE12'
3712 THEN
3713 l_attributes (l_attribute_index).attribute12 :=
3714 p_detail_attributes (l_attribute).attribute_value;
3715 ELSIF l_segment = 'ATTRIBUTE13'
3716 THEN
3717 l_attributes (l_attribute_index).attribute13 :=
3718 p_detail_attributes (l_attribute).attribute_value;
3719 ELSIF l_segment = 'ATTRIBUTE14'
3720 THEN
3721 l_attributes (l_attribute_index).attribute14 :=
3722 p_detail_attributes (l_attribute).attribute_value;
3723 ELSIF l_segment = 'ATTRIBUTE15'
3724 THEN
3725 l_attributes (l_attribute_index).attribute15 :=
3726 p_detail_attributes (l_attribute).attribute_value;
3727 ELSIF l_segment = 'ATTRIBUTE16'
3728 THEN
3729 l_attributes (l_attribute_index).attribute16 :=
3730 p_detail_attributes (l_attribute).attribute_value;
3731 ELSIF l_segment = 'ATTRIBUTE17'
3732 THEN
3733 l_attributes (l_attribute_index).attribute17 :=
3734 p_detail_attributes (l_attribute).attribute_value;
3735 ELSIF l_segment = 'ATTRIBUTE18'
3736 THEN
3737 l_attributes (l_attribute_index).attribute18 :=
3738 p_detail_attributes (l_attribute).attribute_value;
3739 ELSIF l_segment = 'ATTRIBUTE19'
3740 THEN
3741 l_attributes (l_attribute_index).attribute19 :=
3742 p_detail_attributes (l_attribute).attribute_value;
3743 ELSIF l_segment = 'ATTRIBUTE20'
3744 THEN
3745 l_attributes (l_attribute_index).attribute20 :=
3746 p_detail_attributes (l_attribute).attribute_value;
3747 ELSIF l_segment = 'ATTRIBUTE21'
3748 THEN
3749 l_attributes (l_attribute_index).attribute21 :=
3750 p_detail_attributes (l_attribute).attribute_value;
3751 ELSIF l_segment = 'ATTRIBUTE22'
3752 THEN
3753 l_attributes (l_attribute_index).attribute22 :=
3754 p_detail_attributes (l_attribute).attribute_value;
3755 ELSIF l_segment = 'ATTRIBUTE23'
3756 THEN
3757 l_attributes (l_attribute_index).attribute23 :=
3758 p_detail_attributes (l_attribute).attribute_value;
3759 ELSIF l_segment = 'ATTRIBUTE24'
3760 THEN
3761 l_attributes (l_attribute_index).attribute24 :=
3762 p_detail_attributes (l_attribute).attribute_value;
3763 ELSIF l_segment = 'ATTRIBUTE25'
3764 THEN
3765 l_attributes (l_attribute_index).attribute25 :=
3766 p_detail_attributes (l_attribute).attribute_value;
3767 ELSIF l_segment = 'ATTRIBUTE26'
3768 THEN
3769 l_attributes (l_attribute_index).attribute26 :=
3770 p_detail_attributes (l_attribute).attribute_value;
3771 ELSIF l_segment = 'ATTRIBUTE27'
3772 THEN
3773 l_attributes (l_attribute_index).attribute27 :=
3774 p_detail_attributes (l_attribute).attribute_value;
3775 ELSIF l_segment = 'ATTRIBUTE28'
3776 THEN
3777 l_attributes (l_attribute_index).attribute28 :=
3778 p_detail_attributes (l_attribute).attribute_value;
3779 ELSIF l_segment = 'ATTRIBUTE29'
3780 THEN
3781 l_attributes (l_attribute_index).attribute29 :=
3782 p_detail_attributes (l_attribute).attribute_value;
3783 ELSIF l_segment = 'ATTRIBUTE30'
3784 THEN
3785 l_attributes (l_attribute_index).attribute30 :=
3786 p_detail_attributes (l_attribute).attribute_value;
3787 ELSIF l_segment = 'ATTRIBUTE_CATEGORY'
3788 THEN
3789 l_attributes (l_attribute_index).attribute_category :=
3790 p_detail_attributes (l_attribute).attribute_value;
3791 ELSE
3792 RAISE l_exception;
3793 END IF;
3794
3795 -- Bug 8486310
3796 -- Added the below construct to check if the element is
3797 -- associated with an alternate name, and add it to the
3798 -- attributes table if it exists.
3799 IF (l_segment = 'ATTRIBUTE_CATEGORY')
3800 AND (p_detail_attributes(l_attribute).attribute_value LIKE 'ELEMENT -%')
3801 THEN
3802 l_hours_type :=p_detail_attributes(l_attribute).attribute_value;
3803 IF g_an_id.EXISTS(l_hours_type)
3804 THEN
3805 IF g_an_id(l_hours_type) IS NOT NULL
3806 THEN
3807 l_attribute_index := l_attribute_index + 1;
3808 l_attributes (l_attribute_index).time_attribute_id :=
3809 p_detail_attributes (l_attribute).time_attribute_id*5;
3810 l_attributes (l_attribute_index).building_block_id :=
3811 p_detail_attributes (l_attribute).building_block_id;
3812 l_attributes (l_attribute_index).bld_blk_info_type :=
3813 'ALTERNATE NAME IDENTIFIERS';
3814 l_attributes (l_attribute_index).changed :=
3815 p_detail_attributes (l_attribute).changed;
3816 l_attributes (l_attribute_index).bld_blk_info_type_id :=
3817 l_bbit;
3818 l_attributes (l_attribute_index).NEW := 'Y';
3819 l_attributes(l_attribute_index).attribute1 :=
3820 g_an_id(l_hours_type);
3821 END IF;
3822 END IF;
3823 END IF;
3824
3825 l_attribute := p_detail_attributes.NEXT (l_attribute);
3826 END LOOP;
3827
3828 -- Delete the id table for this session
3829 g_an_id.DELETE;
3830
3831 RETURN l_attributes;
3832 END build_attributes;
3833
3834
3835 -- Bug 8486310
3836 -- Added the below function to save the Alt Name identifiers
3837 -- associated with the element, if any.
3838
3839 PROCEDURE save_an_ids(p_element IN VARCHAR2)
3840 IS
3841
3842 -- Picking up only the first Alternate name if there are
3843 -- multiple alternate names ie. First one when Alt Name Ids are
3844 -- sorted.
3845 CURSOR get_an_id(p_element_id IN NUMBER)
3846 IS SELECT attribute2
3847 FROM hxc_alias_values
3848 WHERE alias_definition_id = g_alias_id
3849 AND attribute1 = p_element_id
3850 AND attribute2 IS NOT NULL
3851 ORDER BY attribute2 ASC;
3852
3853 l_an_id VARCHAR2(500);
3854
3855 BEGIN
3856
3857 IF NOT g_an_id.EXISTS(p_element)
3858 THEN
3859 -- Pass the element type id
3860 OPEN get_an_id(REPLACE(p_element,'ELEMENT - '));
3861
3862 FETCH get_an_id INTO l_an_id;
3863
3864 IF get_an_id%NOTFOUND
3865 THEN
3866 l_an_id := NULL;
3867 END IF;
3868
3869 CLOSE get_an_id;
3870
3871 -- Store this
3872 g_an_id(p_element) := l_an_id;
3873
3874 END IF;
3875 END save_an_ids;
3876
3877
3878
3879 -- Bug 7557568
3880 -- Added this new function to check if a deletion was done on
3881 -- the building blocks after a SAVE was done.
3882 PROCEDURE check_restrict_edit( p_time_building_blocks IN HXC_SELF_SERVICE_TIME_DEPOSIT.timecard_info,
3883 p_messages IN OUT NOCOPY HXC_SELF_SERVICE_TIME_DEPOSIT.message_table )
3884
3885 IS
3886
3887
3888 CURSOR get_sum_id( p_bb_id IN NUMBER)
3889 IS SELECT tim_id,
3890 id
3891 FROM hxt_sum_hours_worked_f sum,
3892 fnd_sessions fnd
3893 WHERE time_building_block_id = p_bb_id
3894 AND fnd.effective_date BETWEEN sum.effective_start_date
3895 AND sum.effective_end_date
3896 ORDER BY time_building_block_ovn DESC;
3897
3898 l_cnt NUMBER;
3899 l_ind VARCHAR2(50);
3900 l_day_bb_id NUMBER;
3901 l_day_bb_ovn NUMBER;
3902 l_day_id VARCHAR2(50);
3903 l_detail_bb_id NUMBER;
3904 l_detail_bb_ovn NUMBER;
3905 l_detail_id VARCHAR2(50);
3906 l_retrieved NUMBER;
3907
3908 l_tim_id NUMBER;
3909 l_sum_id NUMBER;
3910
3911 l_dt_update_mode VARCHAR2(50);
3912 o_return_code NUMBER;
3913 l_otm_error VARCHAR2(50);
3914
3915 i VARCHAR2(50);
3916
3917
3918 BEGIN
3919 l_cnt := p_time_building_blocks.FIRST;
3920
3921 LOOP
3922 EXIT WHEN NOT p_time_building_blocks.EXISTS(l_cnt);
3923
3924 IF g_debug
3925 THEN
3926 hr_utility.trace('Detail '||l_cnt);
3927 hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928 hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929 hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930 hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931 hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932 hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933 hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3934 END IF;
3935 -- If its a DELETE or stop_time is missing
3936 IF (p_time_building_blocks(l_cnt).SCOPE = 'DETAIL' )
3937 AND ( (p_time_building_blocks(l_cnt).DATE_TO <> hr_general.end_of_time)
3938 OR ( p_time_building_blocks(l_cnt).type = 'RANGE'
3939 AND ( p_time_building_blocks(l_cnt).start_time IS NULL
3940 OR p_time_building_blocks(l_cnt).stop_time IS NULL)
3941 )
3942 )
3943 THEN
3944 IF g_debug
3945 THEN
3946 hr_utility.trace('It is a deleted detail');
3947 END IF;
3948 l_detail_id := p_time_building_blocks(l_cnt).time_building_block_id||'-'||
3949 p_time_building_blocks(l_cnt).object_version_number;
3950 g_detail_tab(l_detail_id).detail_bb_id := p_time_building_blocks(l_cnt).time_building_block_id;
3951 g_detail_tab(l_detail_id).detail_bb_ovn := p_time_building_blocks(l_cnt).object_version_number ;
3952 g_detail_tab(l_detail_id).parent_id := p_time_building_blocks(l_cnt).parent_building_block_id;
3953 g_detail_tab(l_detail_id).parent_ovn := p_time_building_blocks(l_cnt).parent_building_block_ovn;
3954 g_detail_tab(l_detail_id).type := p_time_building_blocks(l_cnt).type;
3955 g_detail_tab(l_detail_id).measure := p_time_building_blocks(l_cnt).measure;
3956 g_detail_tab(l_detail_id).start_time := p_time_building_blocks(l_cnt).start_time;
3957 g_detail_tab(l_detail_id).date_to := p_time_building_blocks(l_cnt).date_to;
3958 g_detail_tab(l_detail_id).new := p_time_building_blocks(l_cnt).new;
3959 END IF;
3960 l_cnt := p_time_building_blocks.NEXT(l_cnt);
3961 END LOOP;
3962
3963 -- If there is a deletion
3964 IF g_detail_tab.COUNT > 0
3965 THEN
3966 IF g_debug
3967 THEN
3968 hr_utility.trace('There are deleted details, need to process ');
3969 END IF;
3970 i:= g_detail_tab.FIRST;
3971 LOOP
3972 EXIT WHEN i IS NULL;
3973 -- Check if this was ever retrieved.
3974
3975 -- Bug 8631355
3976 -- Added an exception block below.
3977 BEGIN
3978
3979 SELECT 1
3980 INTO l_retrieved
3981 FROM hxc_transaction_details td,
3982 hxc_transactions t
3983 WHERE td.time_building_block_id = g_detail_tab(i).detail_bb_id
3984 AND td.time_building_block_ovn <= g_detail_tab(i).detail_bb_ovn
3985 AND td.status = 'SUCCESS'
3986 AND t.transaction_id = td.transaction_id
3987 AND t.type = 'RETRIEVAL'
3988 AND t.transaction_process_id = -1
3989 AND ROWNUM < 2;
3990
3991 EXCEPTION
3992 WHEN NO_DATA_FOUND THEN
3993 l_retrieved := 0;
3994 END;
3995
3996 IF l_retrieved = 1
3997 THEN
3998 OPEN get_sum_id(g_detail_tab(i).detail_bb_id);
3999
4000 FETCH get_sum_id INTO l_tim_id,
4001 l_sum_id;
4002
4003 CLOSE get_sum_id;
4004 -- Check restrict edit and throw errors.
4005 hxt_td_util.retro_restrict_edit
4006 (p_tim_id => l_tim_id,
4007 p_session_date => SYSDATE,
4008 o_dt_update_mod => l_dt_update_mode,
4009 o_error_message => l_otm_error,
4010 o_return_code => o_return_code,
4011 p_parent_id => l_sum_id
4012 );
4013
4014 IF g_debug
4015 THEN
4016 hr_utility.trace('update mode '||l_dt_update_mode);
4017 END IF;
4018 IF l_dt_update_mode = 'UPDATE'
4019 THEN
4020 hxc_time_entry_rules_utils_pkg.add_error_to_table
4021 (p_message_table => p_messages,
4022 p_message_name => 'HXT_TC_CANNOT_BE_DELETED',
4023 p_message_token => NULL,
4024 p_message_level => 'ERROR',
4025 p_message_field => NULL,
4026 p_application_short_name => 'HXT',
4027 p_timecard_bb_id => g_detail_tab(i).detail_bb_id,
4028 p_time_attribute_id => NULL,
4029 p_timecard_bb_ovn => g_detail_tab(i).detail_bb_ovn,
4030 p_time_attribute_ovn => NULL
4031 );
4032 g_detail_tab.DELETE;
4033 RETURN;
4034 ELSIF l_dt_update_mode IS NULL
4035 THEN
4036 hxc_time_entry_rules_utils_pkg.add_error_to_table
4037 (p_message_table => p_messages,
4038 p_message_name => 'HXT_TC_CANNOT_BE_CHANGED_TODAY',
4039 p_message_token => NULL,
4040 p_message_level => 'ERROR',
4041 p_message_field => NULL,
4042 p_application_short_name => 'HXT',
4043 p_timecard_bb_id => g_detail_tab(i).detail_bb_id,
4044 p_time_attribute_id => NULL,
4045 p_timecard_bb_ovn => g_detail_tab(i).detail_bb_ovn,
4046 p_time_attribute_ovn => NULL
4047 );
4048 g_detail_tab.DELETE;
4049 RETURN;
4050 END IF;
4051 END IF;
4052
4053
4054 i:= g_detail_tab.NEXT(i);
4055 END LOOP;
4056 END IF;
4057 -- Bug 13561510
4058 g_detail_tab.DELETE;
4059
4060
4061 END check_restrict_edit;
4062
4063
4064 END hxt_hxc_retrieval_process;