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