[Home] [Help]
1: PACKAGE BODY hxt_hxc_retrieval_process AS
2: /* $Header: hxthcrtp.pkb 120.20.12020000.3 2013/04/04 15:14:13 asrajago ship $ */
3: g_debug BOOLEAN := hr_utility.debug_enabled;
4:
5: TYPE t_time_recipient IS TABLE OF VARCHAR2 (1)
6: INDEX BY BINARY_INTEGER;
7:
116: l_session_id NUMBER;
117: l_tim_id NUMBER (15);
118: l_measure_count NUMBER;
119: BEGIN
120: g_debug := hr_utility.debug_enabled;
121: --12567943 On clicking show accrual session's effective date is set
122: -- based on preference evaluation date which hinders validation
123: hr_kflex_utility.unset_session_date (userenv('sessionid'));
124: hr_kflex_utility.set_session_date (p_effective_date => SYSDATE,
146: --
147: IF g_debug
148: THEN
149: l_proc := 'hxt_hxc_retrieval_process.synchronize_deletes_in_otlr';
150: hr_utility.set_location (l_proc, 10);
151: hr_utility.TRACE
152: ('*********** NEW TIME BUILDING BLOCK ************');
153: END IF;
154:
147: IF g_debug
148: THEN
149: l_proc := 'hxt_hxc_retrieval_process.synchronize_deletes_in_otlr';
150: hr_utility.set_location (l_proc, 10);
151: hr_utility.TRACE
152: ('*********** NEW TIME BUILDING BLOCK ************');
153: END IF;
154:
155: --
172:
173: --
174: IF g_debug
175: THEN
176: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
177: hr_utility.TRACE ('Type is : ' || l_type);
178: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
179: hr_utility.TRACE ( 'l_start_time is '
180: || TO_CHAR (l_start_time,
173: --
174: IF g_debug
175: THEN
176: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
177: hr_utility.TRACE ('Type is : ' || l_type);
178: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
179: hr_utility.TRACE ( 'l_start_time is '
180: || TO_CHAR (l_start_time,
181: 'DD-MON-YYYY HH:MI:SS')
174: IF g_debug
175: THEN
176: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
177: hr_utility.TRACE ('Type is : ' || l_type);
178: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
179: hr_utility.TRACE ( 'l_start_time is '
180: || TO_CHAR (l_start_time,
181: 'DD-MON-YYYY HH:MI:SS')
182: );
175: THEN
176: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
177: hr_utility.TRACE ('Type is : ' || l_type);
178: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
179: hr_utility.TRACE ( 'l_start_time is '
180: || TO_CHAR (l_start_time,
181: 'DD-MON-YYYY HH:MI:SS')
182: );
183: hr_utility.TRACE ( 'l_stop_time is '
179: hr_utility.TRACE ( 'l_start_time is '
180: || TO_CHAR (l_start_time,
181: 'DD-MON-YYYY HH:MI:SS')
182: );
183: hr_utility.TRACE ( 'l_stop_time is '
184: || TO_CHAR (l_stop_time, 'DD-MON-YYYY HH:MI:SS')
185: );
186: hr_utility.TRACE ('l_scope is ' || l_scope);
187: hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
182: );
183: hr_utility.TRACE ( 'l_stop_time is '
184: || TO_CHAR (l_stop_time, 'DD-MON-YYYY HH:MI:SS')
185: );
186: hr_utility.TRACE ('l_scope is ' || l_scope);
187: hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
188: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
189: --
190: hr_utility.TRACE ( 'UOM is : '
183: hr_utility.TRACE ( 'l_stop_time is '
184: || TO_CHAR (l_stop_time, 'DD-MON-YYYY HH:MI:SS')
185: );
186: hr_utility.TRACE ('l_scope is ' || l_scope);
187: hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
188: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
189: --
190: hr_utility.TRACE ( 'UOM is : '
191: || l_time_building_blocks (l_cnt).unit_of_measure
184: || TO_CHAR (l_stop_time, 'DD-MON-YYYY HH:MI:SS')
185: );
186: hr_utility.TRACE ('l_scope is ' || l_scope);
187: hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
188: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
189: --
190: hr_utility.TRACE ( 'UOM is : '
191: || l_time_building_blocks (l_cnt).unit_of_measure
192: );
186: hr_utility.TRACE ('l_scope is ' || l_scope);
187: hr_utility.TRACE ('l_resource_id is ' || TO_CHAR (l_resource_id));
188: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
189: --
190: hr_utility.TRACE ( 'UOM is : '
191: || l_time_building_blocks (l_cnt).unit_of_measure
192: );
193: hr_utility.TRACE
194: ( 'Parent BB ID is : '
189: --
190: hr_utility.TRACE ( 'UOM is : '
191: || l_time_building_blocks (l_cnt).unit_of_measure
192: );
193: hr_utility.TRACE
194: ( 'Parent BB ID is : '
195: || TO_CHAR
196: (l_time_building_blocks (l_cnt).parent_building_block_id
197: )
195: || TO_CHAR
196: (l_time_building_blocks (l_cnt).parent_building_block_id
197: )
198: );
199: hr_utility.TRACE ( 'PARENT_IS_NEW is : '
200: || l_time_building_blocks (l_cnt).parent_is_new
201: );
202: hr_utility.TRACE
203: ( 'OVN is : '
198: );
199: hr_utility.TRACE ( 'PARENT_IS_NEW is : '
200: || l_time_building_blocks (l_cnt).parent_is_new
201: );
202: hr_utility.TRACE
203: ( 'OVN is : '
204: || TO_CHAR
205: (l_time_building_blocks (l_cnt).object_version_number
206: )
204: || TO_CHAR
205: (l_time_building_blocks (l_cnt).object_version_number
206: )
207: );
208: hr_utility.TRACE ( 'APPROVAL_STATUS is : '
209: || l_time_building_blocks (l_cnt).approval_status
210: );
211: hr_utility.TRACE
212: ( 'DATE_FROM is : '
207: );
208: hr_utility.TRACE ( 'APPROVAL_STATUS is : '
209: || l_time_building_blocks (l_cnt).approval_status
210: );
211: hr_utility.TRACE
212: ( 'DATE_FROM is : '
213: || TO_CHAR
214: (l_time_building_blocks (l_cnt).date_from,
215: 'DD-MON-YYYY'
214: (l_time_building_blocks (l_cnt).date_from,
215: 'DD-MON-YYYY'
216: )
217: );
218: hr_utility.TRACE ( 'DATE_TO is : '
219: || TO_CHAR
220: (l_time_building_blocks (l_cnt).date_to,
221: 'DD-MON-YYYY'
222: )
220: (l_time_building_blocks (l_cnt).date_to,
221: 'DD-MON-YYYY'
222: )
223: );
224: hr_utility.TRACE ('NEW is : '
225: || l_time_building_blocks (l_cnt).NEW
226: );
227: --
228: hr_utility.set_location (l_proc, 20);
224: hr_utility.TRACE ('NEW is : '
225: || l_time_building_blocks (l_cnt).NEW
226: );
227: --
228: hr_utility.set_location (l_proc, 20);
229: END IF;
230:
231: --
232: IF ( ( (l_type = 'MEASURE' AND l_measure IS NOT NULL)
272: IF l_scope = 'DETAIL'
273: THEN
274: IF g_debug
275: THEN
276: hr_utility.set_location (l_proc, 50);
277: hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
278: hr_utility.TRACE ('l_parent_bb_ovn :' || l_parent_bb_ovn);
279: END IF;
280:
273: THEN
274: IF g_debug
275: THEN
276: hr_utility.set_location (l_proc, 50);
277: hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
278: hr_utility.TRACE ('l_parent_bb_ovn :' || l_parent_bb_ovn);
279: END IF;
280:
281: j := l_time_building_blocks.FIRST;
274: IF g_debug
275: THEN
276: hr_utility.set_location (l_proc, 50);
277: hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
278: hr_utility.TRACE ('l_parent_bb_ovn :' || l_parent_bb_ovn);
279: END IF;
280:
281: j := l_time_building_blocks.FIRST;
282:
282:
283: LOOP
284: IF g_debug
285: THEN
286: hr_utility.set_location (l_proc, 51);
287: END IF;
288:
289: EXIT WHEN NOT l_time_building_blocks.EXISTS (j);
290:
289: EXIT WHEN NOT l_time_building_blocks.EXISTS (j);
290:
291: IF g_debug
292: THEN
293: hr_utility.set_location (l_proc, 52);
294: END IF;
295:
296: --
297: l_check_bb_id :=
300: l_time_building_blocks (j).object_version_number;
301:
302: IF g_debug
303: THEN
304: hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
305: hr_utility.TRACE ('l_parent_bb_ovn:' || l_parent_bb_ovn);
306: END IF;
307:
308: IF l_check_bb_id = l_parent_bb_id
301:
302: IF g_debug
303: THEN
304: hr_utility.TRACE ('l_parent_bb_id :' || l_parent_bb_id);
305: hr_utility.TRACE ('l_parent_bb_ovn:' || l_parent_bb_ovn);
306: END IF;
307:
308: IF l_check_bb_id = l_parent_bb_id
309: AND l_check_bb_ovn = l_parent_bb_ovn
309: AND l_check_bb_ovn = l_parent_bb_ovn
310: THEN
311: IF g_debug
312: THEN
313: hr_utility.set_location (l_proc, 53);
314: END IF;
315:
316: l_parent_start_time :=
317: l_time_building_blocks (j).start_time;
318: EXIT;
319: ELSE
320: IF g_debug
321: THEN
322: hr_utility.set_location (l_proc, 54);
323: END IF;
324:
325: j := l_time_building_blocks.NEXT (j);
326: END IF;
326: END IF;
327:
328: IF g_debug
329: THEN
330: hr_utility.set_location (l_proc, 55);
331: END IF;
332: END LOOP;
333: ELSE
334: l_parent_start_time := l_start_time;
339: where time_building_block_id = l_parent_bb_id
340: and object_version_number = l_parent_bb_ovn; */
341: IF g_debug
342: THEN
343: hr_utility.TRACE ('l_parent_start_time:' || l_parent_start_time
344: );
345: hr_utility.TRACE ('l_resource_id:' || l_resource_id);
346: END IF;
347:
341: IF g_debug
342: THEN
343: hr_utility.TRACE ('l_parent_start_time:' || l_parent_start_time
344: );
345: hr_utility.TRACE ('l_resource_id:' || l_resource_id);
346: END IF;
347:
348: -- Find out if any detail records(siblings) for this day
349: -- deleted or updated in OTL and not yet transferred to OTLR.
356: EXIT WHEN c_get_day_bb_id%NOTFOUND;
357:
358: IF g_debug
359: THEN
360: hr_utility.TRACE ('l_day_bb_id:' || l_day_bb_id);
361: END IF;
362:
363: -- If such records found in OTL, then find the corresponding
364: -- summary record in OTLR, so that the summary and detail rows in HXT
364: -- summary record in OTLR, so that the summary and detail rows in HXT
365: -- tables can also be deleted to reflect the latest changes in OTL.
366: IF g_debug
367: THEN
368: hr_utility.set_location (l_proc, 60);
369: END IF;
370:
371: l_tim_id := NULL;
372:
376: INTO l_time_summary_id, l_tim_id;
377:
378: IF g_debug
379: THEN
380: hr_utility.set_location (l_proc, 70);
381: hr_utility.TRACE ('l_time_summary_id:' || l_time_summary_id);
382: END IF;
383:
384: -- If corresponding summary record found then delete its details
377:
378: IF g_debug
379: THEN
380: hr_utility.set_location (l_proc, 70);
381: hr_utility.TRACE ('l_time_summary_id:' || l_time_summary_id);
382: END IF;
383:
384: -- If corresponding summary record found then delete its details
385: -- and the summary record itself from HXT tables.
386: IF c_get_sum_id%FOUND
387: THEN
388: IF g_debug
389: THEN
390: hr_utility.set_location (l_proc, 80);
391: END IF;
392:
393: hxt_td_util.retro_restrict_edit
394: (p_tim_id => l_tim_id,
463: WHERE parent_id = l_time_summary_id;
464:
465: IF g_debug
466: THEN
467: hr_utility.set_location (l_proc, 90);
468: END IF;
469:
470: --
471: -- Delete the summary row itself.
474: WHERE ID = l_time_summary_id;
475:
476: IF g_debug
477: THEN
478: hr_utility.set_location (l_proc, 100);
479: END IF;
480:
481: CLOSE c_get_sum_id;
482:
487: CLOSE c_get_sum_id;
488:
489: IF g_debug
490: THEN
491: hr_utility.set_location (l_proc, 110);
492: END IF;
493: END LOOP;
494:
495: IF g_debug
493: END LOOP;
494:
495: IF g_debug
496: THEN
497: hr_utility.set_location (l_proc, 110.5);
498: END IF;
499:
500: CLOSE c_get_day_bb_id;
501:
500: CLOSE c_get_day_bb_id;
501:
502: IF g_debug
503: THEN
504: hr_utility.set_location (l_proc, 120);
505: END IF;
506: END IF;
507:
508: IF g_debug
506: END IF;
507:
508: IF g_debug
509: THEN
510: hr_utility.set_location (l_proc, 130);
511: END IF;
512:
513: l_cnt := l_time_building_blocks.NEXT (l_cnt);
514: END LOOP;
528: l_attributes hxc_self_service_time_deposit.app_attributes_info;
529: l_messages hxc_self_service_time_deposit.message_table;
530: l_proc VARCHAR2 (100);
531: BEGIN
532: g_debug := hr_utility.debug_enabled;
533:
534: IF g_debug
535: THEN
536: l_proc := 'hxt_hxc_retrieval_process.OTM_VALIDATE_PROCESS';
533:
534: IF g_debug
535: THEN
536: l_proc := 'hxt_hxc_retrieval_process.OTM_VALIDATE_PROCESS';
537: hr_utility.set_location (l_proc, 10);
538: hr_utility.TRACE ( 'p_time_building_blocks is : '
539: || SUBSTR (p_time_building_blocks, 1, 2000)
540: );
541: END IF;
534: IF g_debug
535: THEN
536: l_proc := 'hxt_hxc_retrieval_process.OTM_VALIDATE_PROCESS';
537: hr_utility.set_location (l_proc, 10);
538: hr_utility.TRACE ( 'p_time_building_blocks is : '
539: || SUBSTR (p_time_building_blocks, 1, 2000)
540: );
541: END IF;
542:
545: (p_time_building_blocks);
546:
547: IF g_debug
548: THEN
549: hr_utility.set_location (l_proc, 30);
550: END IF;
551:
552: l_attributes :=
553: hxc_deposit_wrapper_utilities.string_to_attributes (p_time_attributes);
553: hxc_deposit_wrapper_utilities.string_to_attributes (p_time_attributes);
554:
555: IF g_debug
556: THEN
557: hr_utility.set_location (l_proc, 40);
558: END IF;
559:
560: l_messages :=
561: hxc_deposit_wrapper_utilities.string_to_messages (p_messages);
663: l_city_name hxt_sum_hours_worked_f.city_name%TYPE;
664: l_zip_code hxt_sum_hours_worked_f.zip_code%TYPE;
665: l_tim_id NUMBER;
666: BEGIN
667: g_debug := hr_utility.debug_enabled;
668:
669: IF g_debug
670: THEN
671: l_proc := 'hxt_hxc_retrieval_process.VALIDATE_TIMECARD';
668:
669: IF g_debug
670: THEN
671: l_proc := 'hxt_hxc_retrieval_process.VALIDATE_TIMECARD';
672: hr_utility.set_location (l_proc, 1);
673: END IF;
674:
675: OPEN get_debug;
676:
680: IF get_debug%FOUND
681: THEN
682: IF g_debug
683: THEN
684: hr_utility.set_location (l_proc, 3);
685: END IF;
686: END IF;
687:
688: CLOSE get_debug;
691:
692:
693: IF g_debug
694: THEN
695: hr_utility.set_location (l_proc, 4);
696: END IF;
697:
698: SAVEPOINT otm_validate;
699:
698: SAVEPOINT otm_validate;
699:
700: IF g_debug
701: THEN
702: hr_utility.set_location (l_proc, 5);
703: END IF;
704:
705: -- Loop through all the building blocks and validate the details.
706: -------------------------------------------------------------------------------
720: FOR i IN 1 .. 3
721: LOOP
722: IF g_debug
723: THEN
724: hr_utility.set_location (l_proc, 6);
725: END IF;
726:
727: l_cnt := p_time_building_blocks.FIRST; --Added 2804510
728:
727: l_cnt := p_time_building_blocks.FIRST; --Added 2804510
728:
729: IF g_debug
730: THEN
731: hr_utility.TRACE ('l_cnt :' || l_cnt);
732: END IF;
733:
734: LOOP --Added 2804510
735: EXIT WHEN NOT p_time_building_blocks.EXISTS (l_cnt);
738:
739: --
740: IF g_debug
741: THEN
742: hr_utility.set_location (l_proc, 10);
743: hr_utility.TRACE
744: ('*********** NEW TIME BUILDING BLOCK ************');
745: END IF;
746:
739: --
740: IF g_debug
741: THEN
742: hr_utility.set_location (l_proc, 10);
743: hr_utility.TRACE
744: ('*********** NEW TIME BUILDING BLOCK ************');
745: END IF;
746:
747: --
780:
781: --
782: IF g_debug
783: THEN
784: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
785: hr_utility.TRACE ('Type is : ' || l_type);
786: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
787: hr_utility.TRACE ( 'l_start_time is '
788: || TO_CHAR (l_start_time,
781: --
782: IF g_debug
783: THEN
784: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
785: hr_utility.TRACE ('Type is : ' || l_type);
786: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
787: hr_utility.TRACE ( 'l_start_time is '
788: || TO_CHAR (l_start_time,
789: 'DD-MON-YYYY HH:MI:SS'
782: IF g_debug
783: THEN
784: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
785: hr_utility.TRACE ('Type is : ' || l_type);
786: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
787: hr_utility.TRACE ( 'l_start_time is '
788: || TO_CHAR (l_start_time,
789: 'DD-MON-YYYY HH:MI:SS'
790: )
783: THEN
784: hr_utility.TRACE ('Time BB ID is : ' || TO_CHAR (l_bb_id));
785: hr_utility.TRACE ('Type is : ' || l_type);
786: hr_utility.TRACE ('Measure is : ' || TO_CHAR (l_measure));
787: hr_utility.TRACE ( 'l_start_time is '
788: || TO_CHAR (l_start_time,
789: 'DD-MON-YYYY HH:MI:SS'
790: )
791: );
788: || TO_CHAR (l_start_time,
789: 'DD-MON-YYYY HH:MI:SS'
790: )
791: );
792: hr_utility.TRACE ( 'l_stop_time is '
793: || TO_CHAR (l_stop_time,
794: 'DD-MON-YYYY HH:MI:SS'
795: )
796: );
793: || TO_CHAR (l_stop_time,
794: 'DD-MON-YYYY HH:MI:SS'
795: )
796: );
797: hr_utility.TRACE ('l_scope is ' || l_scope);
798: hr_utility.TRACE ('l_resource_id is '
799: || TO_CHAR (l_resource_id)
800: );
801: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
794: 'DD-MON-YYYY HH:MI:SS'
795: )
796: );
797: hr_utility.TRACE ('l_scope is ' || l_scope);
798: hr_utility.TRACE ('l_resource_id is '
799: || TO_CHAR (l_resource_id)
800: );
801: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
802: --
797: hr_utility.TRACE ('l_scope is ' || l_scope);
798: hr_utility.TRACE ('l_resource_id is '
799: || TO_CHAR (l_resource_id)
800: );
801: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
802: --
803: hr_utility.TRACE ( 'UOM is : '
804: || p_time_building_blocks (l_cnt).unit_of_measure
805: );
799: || TO_CHAR (l_resource_id)
800: );
801: hr_utility.TRACE ('l_resource_type is ' || l_resource_type);
802: --
803: hr_utility.TRACE ( 'UOM is : '
804: || p_time_building_blocks (l_cnt).unit_of_measure
805: );
806: hr_utility.TRACE
807: ( 'Parent BB ID is : '
802: --
803: hr_utility.TRACE ( 'UOM is : '
804: || p_time_building_blocks (l_cnt).unit_of_measure
805: );
806: hr_utility.TRACE
807: ( 'Parent BB ID is : '
808: || TO_CHAR
809: (p_time_building_blocks (l_cnt).parent_building_block_id
810: )
808: || TO_CHAR
809: (p_time_building_blocks (l_cnt).parent_building_block_id
810: )
811: );
812: hr_utility.TRACE ( 'PARENT_IS_NEW is : '
813: || p_time_building_blocks (l_cnt).parent_is_new
814: );
815: hr_utility.TRACE
816: ( 'OVN is : '
811: );
812: hr_utility.TRACE ( 'PARENT_IS_NEW is : '
813: || p_time_building_blocks (l_cnt).parent_is_new
814: );
815: hr_utility.TRACE
816: ( 'OVN is : '
817: || TO_CHAR
818: (p_time_building_blocks (l_cnt).object_version_number
819: )
817: || TO_CHAR
818: (p_time_building_blocks (l_cnt).object_version_number
819: )
820: );
821: hr_utility.TRACE ( 'APPROVAL_STATUS is : '
822: || p_time_building_blocks (l_cnt).approval_status
823: );
824: hr_utility.TRACE
825: ( 'APPROVAL_STYLE_ID is : '
820: );
821: hr_utility.TRACE ( 'APPROVAL_STATUS is : '
822: || p_time_building_blocks (l_cnt).approval_status
823: );
824: hr_utility.TRACE
825: ( 'APPROVAL_STYLE_ID is : '
826: || TO_CHAR
827: (p_time_building_blocks (l_cnt).approval_style_id
828: )
826: || TO_CHAR
827: (p_time_building_blocks (l_cnt).approval_style_id
828: )
829: );
830: hr_utility.TRACE
831: ( 'DATE_FROM is : '
832: || TO_CHAR
833: (p_time_building_blocks (l_cnt).date_from,
834: 'DD-MON-YYYY'
833: (p_time_building_blocks (l_cnt).date_from,
834: 'DD-MON-YYYY'
835: )
836: );
837: hr_utility.TRACE
838: ( 'DATE_TO is : '
839: || TO_CHAR
840: (p_time_building_blocks (l_cnt).date_to,
841: 'DD-MON-YYYY'
840: (p_time_building_blocks (l_cnt).date_to,
841: 'DD-MON-YYYY'
842: )
843: );
844: hr_utility.TRACE ( 'COMMENT_TEXT is : '
845: || p_time_building_blocks (l_cnt).comment_text
846: );
847: hr_utility.TRACE
848: ( 'Parent OVN is : '
843: );
844: hr_utility.TRACE ( 'COMMENT_TEXT is : '
845: || p_time_building_blocks (l_cnt).comment_text
846: );
847: hr_utility.TRACE
848: ( 'Parent OVN is : '
849: || TO_CHAR
850: (p_time_building_blocks (l_cnt).parent_building_block_ovn
851: )
849: || TO_CHAR
850: (p_time_building_blocks (l_cnt).parent_building_block_ovn
851: )
852: );
853: hr_utility.TRACE ( 'NEW is : '
854: || p_time_building_blocks (l_cnt).NEW
855: );
856: --
857: --
854: || p_time_building_blocks (l_cnt).NEW
855: );
856: --
857: --
858: hr_utility.set_location (l_proc, 20);
859: END IF;
860:
861: --Bug 2966729
862: --Description
918:
919: --
920: IF g_debug
921: THEN
922: hr_utility.set_location (l_proc, 21);
923: END IF;
924:
925: l_valid := 'Y';
926: ELSE
925: l_valid := 'Y';
926: ELSE
927: IF g_debug
928: THEN
929: hr_utility.set_location (l_proc, 22);
930: END IF;
931:
932: l_valid := 'N';
933: END IF;
938: IF l_valid = 'Y'
939: THEN
940: IF g_debug
941: THEN
942: hr_utility.set_location (l_proc, 23);
943: END IF;
944:
945: -- Get the start and stop times from the parent DAY block if DETAIL is
946: -- a measure.
957: ) -- end bug 3650967
958: THEN
959: IF g_debug
960: THEN
961: hr_utility.set_location (l_proc, 24);
962: END IF;
963:
964: l_day := p_time_building_blocks.FIRST; --Added 2804510
965:
968:
969: --Added 2804510
970: IF g_debug
971: THEN
972: hr_utility.set_location (l_proc, 30);
973: END IF;
974:
975: IF (p_time_building_blocks (l_day).time_building_block_id =
976: l_parent_bb_id
978: AND (p_time_building_blocks (l_day).SCOPE = 'DAY')
979: THEN
980: IF g_debug
981: THEN
982: hr_utility.set_location (l_proc, 31);
983: END IF;
984:
985: --
986: l_start_time :=
991:
992: --
993: IF g_debug
994: THEN
995: hr_utility.TRACE ( 'l_start_time is '
996: || TO_CHAR
997: (l_start_time,
998: 'DD-MON-YYYY HH:MI:SS'
999: )
997: (l_start_time,
998: 'DD-MON-YYYY HH:MI:SS'
999: )
1000: );
1001: hr_utility.TRACE ( 'l_stop_time is '
1002: || TO_CHAR
1003: (l_stop_time,
1004: 'DD-MON-YYYY HH:MI:SS'
1005: )
1010: END IF;
1011:
1012: IF g_debug
1013: THEN
1014: hr_utility.set_location (l_proc, 32);
1015: END IF;
1016:
1017: l_day := p_time_building_blocks.NEXT (l_day);
1018: --Added 2804510
1019: END LOOP; --Added 2804510
1020:
1021: IF g_debug
1022: THEN
1023: hr_utility.set_location (l_proc, 33);
1024: END IF;
1025: END IF; -- l_type = MEASURE
1026:
1027: IF g_debug
1025: END IF; -- l_type = MEASURE
1026:
1027: IF g_debug
1028: THEN
1029: hr_utility.set_location (l_proc, 34);
1030: END IF;
1031:
1032: l_person_id := NULL;
1033:
1034: IF l_resource_type = 'PERSON'
1035: THEN
1036: IF g_debug
1037: THEN
1038: hr_utility.set_location (l_proc, 35);
1039: END IF;
1040:
1041: l_person_id := l_resource_id;
1042:
1041: l_person_id := l_resource_id;
1042:
1043: IF g_debug
1044: THEN
1045: hr_utility.TRACE ( 'l_person_id is '
1046: || TO_CHAR (l_person_id)
1047: );
1048: END IF;
1049: END IF;
1049: END IF;
1050:
1051: IF g_debug
1052: THEN
1053: hr_utility.set_location (l_proc, 36);
1054: END IF;
1055:
1056: l_effective_date := TRUNC (l_start_time);
1057:
1056: l_effective_date := TRUNC (l_start_time);
1057:
1058: IF g_debug
1059: THEN
1060: hr_utility.TRACE ( 'l_effective_date is :'
1061: || TO_CHAR (l_effective_date,
1062: 'DD-MON-YYYY'
1063: )
1064: );
1066:
1067: BEGIN
1068: IF g_debug
1069: THEN
1070: hr_utility.set_location (l_proc, 37);
1071: END IF;
1072:
1073: SELECT full_name, business_group_id
1074: INTO hxt_otc_retrieval_interface.g_full_name, l_bg_id
1080: WHEN NO_DATA_FOUND
1081: THEN
1082: IF g_debug
1083: THEN
1084: hr_utility.set_location (l_proc, 38);
1085: END IF;
1086:
1087: hxc_time_entry_rules_utils_pkg.add_error_to_table
1088: (p_message_table => p_messages,
1105: -- l_person_id,
1106: -- l_effective_date);
1107: IF g_debug
1108: THEN
1109: hr_utility.set_location (l_proc, 39);
1110: END IF;
1111:
1112: hxt_otc_retrieval_interface.get_assignment_id
1113: (p_person_id => l_person_id,
1118: );
1119:
1120: IF g_debug
1121: THEN
1122: hr_utility.set_location (l_proc, 50);
1123: END IF;
1124:
1125: l_field_name.DELETE;
1126: l_value.DELETE;
1128: l_context.DELETE;
1129:
1130: IF g_debug
1131: THEN
1132: hr_utility.set_location (l_proc, 55);
1133: hr_utility.TRACE ( 'number of attr is : '
1134: || TO_CHAR (p_time_attributes.COUNT)
1135: );
1136: END IF;
1129:
1130: IF g_debug
1131: THEN
1132: hr_utility.set_location (l_proc, 55);
1133: hr_utility.TRACE ( 'number of attr is : '
1134: || TO_CHAR (p_time_attributes.COUNT)
1135: );
1136: END IF;
1137:
1149: p_time_attributes (l_cnt_att).building_block_id
1150: THEN
1151: IF g_debug
1152: THEN
1153: hr_utility.TRACE
1154: ('------ In Attribute Loop ------');
1155: END IF;
1156:
1157: l_field_name (l_att) :=
1158: p_time_attributes (l_cnt_att).attribute_name;
1159:
1160: IF g_debug
1161: THEN
1162: hr_utility.TRACE ( 'l_field_name(l_att) is '
1163: || l_field_name (l_att)
1164: );
1165: END IF;
1166:
1168: p_time_attributes (l_cnt_att).attribute_value;
1169:
1170: IF g_debug
1171: THEN
1172: hr_utility.TRACE ( 'l_value(l_att) is '
1173: || l_value (l_att)
1174: );
1175: END IF;
1176:
1178: p_time_attributes (l_cnt_att).bld_blk_info_type;
1179:
1180: IF g_debug
1181: THEN
1182: hr_utility.TRACE ( 'l_context(l_att) is '
1183: || l_context (l_att)
1184: );
1185: END IF;
1186:
1188: p_time_attributes (l_cnt_att).CATEGORY;
1189:
1190: IF g_debug
1191: THEN
1192: hr_utility.TRACE ( 'l_category(l_att) is '
1193: || l_category (l_att)
1194: );
1195: END IF;
1196:
1203: END IF;
1204:
1205: IF g_debug
1206: THEN
1207: hr_utility.TRACE ('l_att is ' || TO_CHAR (l_att));
1208: hr_utility.set_location (l_proc, 60);
1209: END IF;
1210:
1211: hxt_otc_retrieval_interface.parse_attributes
1204:
1205: IF g_debug
1206: THEN
1207: hr_utility.TRACE ('l_att is ' || TO_CHAR (l_att));
1208: hr_utility.set_location (l_proc, 60);
1209: END IF;
1210:
1211: hxt_otc_retrieval_interface.parse_attributes
1212: (p_category => l_category,
1234: IF (l_no_times = 'Y')
1235: THEN
1236: IF g_debug
1237: THEN
1238: hr_utility.set_location (l_proc, 61);
1239: END IF;
1240:
1241: l_start_time := NULL;
1242: l_stop_time := NULL;
1249: IF l_new = 'N'
1250: THEN
1251: IF g_debug
1252: THEN
1253: hr_utility.set_location (l_proc, 62);
1254: END IF;
1255:
1256: -- Bug 7415291
1257: -- Added new parameter l_tim_id
1268: );
1269:
1270: IF g_debug
1271: THEN
1272: hr_utility.set_location (l_proc, 63);
1273: hr_utility.TRACE ('after find_existing_timecard');
1274: hr_utility.TRACE ( 'l_time_summary_id is: '
1275: || TO_CHAR (l_time_summary_id)
1276: );
1269:
1270: IF g_debug
1271: THEN
1272: hr_utility.set_location (l_proc, 63);
1273: hr_utility.TRACE ('after find_existing_timecard');
1274: hr_utility.TRACE ( 'l_time_summary_id is: '
1275: || TO_CHAR (l_time_summary_id)
1276: );
1277: END IF;
1270: IF g_debug
1271: THEN
1272: hr_utility.set_location (l_proc, 63);
1273: hr_utility.TRACE ('after find_existing_timecard');
1274: hr_utility.TRACE ( 'l_time_summary_id is: '
1275: || TO_CHAR (l_time_summary_id)
1276: );
1277: END IF;
1278:
1279: IF l_time_summary_id IS NOT NULL
1280: THEN
1281: IF g_debug
1282: THEN
1283: hr_utility.set_location (l_proc, 64);
1284: END IF;
1285:
1286: DELETE FROM hxt_det_hours_worked_f
1287: WHERE parent_id = l_time_summary_id;
1302: --l_delete as a value to the parameter delete_yn of the record_time api.
1303: --Bug 2770487 Sonarasi Over
1304: IF g_debug
1305: THEN
1306: hr_utility.set_location (l_proc, 65);
1307: END IF;
1308:
1309: hxt_time_collection.record_time
1310: (timecard_source => 'Time Store',
1382: );
1383:
1384: IF g_debug
1385: THEN
1386: hr_utility.set_location (l_proc, 66);
1387: END IF;
1388:
1389: IF g_otm_messages.COUNT > 0
1390: THEN
1414: IF l_otm_error IS NOT NULL
1415: THEN
1416: IF g_debug
1417: THEN
1418: hr_utility.set_location (l_proc, 1000);
1419: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1420: END IF;
1421:
1422: hxc_time_entry_rules_utils_pkg.add_error_to_table
1415: THEN
1416: IF g_debug
1417: THEN
1418: hr_utility.set_location (l_proc, 1000);
1419: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1420: END IF;
1421:
1422: hxc_time_entry_rules_utils_pkg.add_error_to_table
1423: (p_message_table => p_messages,
1442: IF l_oracle_error IS NOT NULL
1443: THEN
1444: IF g_debug
1445: THEN
1446: hr_utility.set_location (l_proc, 1050);
1447: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
1448: );
1449: END IF;
1450:
1443: THEN
1444: IF g_debug
1445: THEN
1446: hr_utility.set_location (l_proc, 1050);
1447: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
1448: );
1449: END IF;
1450:
1451: hxc_time_entry_rules_utils_pkg.add_error_to_table
1500: IF (get_timecard_id%FOUND)
1501: THEN
1502: IF g_debug
1503: THEN
1504: hr_utility.TRACE ( 'TIM_SUM_ID IS : '
1505: || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
1506: );
1507: hr_utility.TRACE ( 'l_timecard_id is : '
1508: || TO_CHAR (l_timecard_id)
1503: THEN
1504: hr_utility.TRACE ( 'TIM_SUM_ID IS : '
1505: || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
1506: );
1507: hr_utility.TRACE ( 'l_timecard_id is : '
1508: || TO_CHAR (l_timecard_id)
1509: );
1510: hr_utility.TRACE ( 'l_time_period_id is : '
1511: || TO_CHAR (l_time_period_id)
1506: );
1507: hr_utility.TRACE ( 'l_timecard_id is : '
1508: || TO_CHAR (l_timecard_id)
1509: );
1510: hr_utility.TRACE ( 'l_time_period_id is : '
1511: || TO_CHAR (l_time_period_id)
1512: );
1513: END IF;
1514:
1571: IF l_otm_error IS NOT NULL
1572: THEN
1573: IF g_debug
1574: THEN
1575: hr_utility.set_location (l_proc, 2000);
1576: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1577: END IF;
1578:
1579: hxc_time_entry_rules_utils_pkg.add_error_to_table
1572: THEN
1573: IF g_debug
1574: THEN
1575: hr_utility.set_location (l_proc, 2000);
1576: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
1577: END IF;
1578:
1579: hxc_time_entry_rules_utils_pkg.add_error_to_table
1580: (p_message_table => g_messages,
1595: );
1596:
1597: IF g_debug
1598: THEN
1599: hr_utility.TRACE ( 'g_messages.message_name is : '
1600: || g_messages (1).message_name
1601: );
1602: END IF;
1603:
1607: IF l_oracle_error IS NOT NULL
1608: THEN
1609: IF g_debug
1610: THEN
1611: hr_utility.set_location (l_proc, 2050);
1612: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
1613: END IF;
1614:
1615: hxc_time_entry_rules_utils_pkg.add_error_to_table
1608: THEN
1609: IF g_debug
1610: THEN
1611: hr_utility.set_location (l_proc, 2050);
1612: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
1613: END IF;
1614:
1615: hxc_time_entry_rules_utils_pkg.add_error_to_table
1616: (p_message_table => g_messages,
1627: );
1628:
1629: IF g_debug
1630: THEN
1631: hr_utility.TRACE ( 'g_messages.message_name is : '
1632: || g_messages (1).message_name
1633: );
1634: END IF;
1635:
1650: ROLLBACK TO otm_validate;
1651:
1652: IF g_debug
1653: THEN
1654: hr_utility.TRACE ('After RollBack');
1655: END IF;
1656: EXCEPTION
1657: WHEN e_error
1658: THEN
1729: l_time_att_info hxc_self_service_time_deposit.app_attributes_info
1730: := p_time_att_info;
1731: l_proc VARCHAR2 (250);
1732: BEGIN
1733: g_debug := hr_utility.debug_enabled;
1734:
1735: IF (p_otm_explosion = 'Y')
1736: THEN
1737: -- Get the application set time recipients
1801: -- skip this if we are SAVING
1802: IF g_debug
1803: THEN
1804: l_proc := 'hxt_hxc_retrieval_process.otlr_validation_required';
1805: hr_utility.TRACE ('p_operation:' || p_operation);
1806: END IF;
1807:
1808: SAVEPOINT rollback_validation;
1809:
1835:
1836: -- Bug 3321951 fix stop.
1837: IF g_debug
1838: THEN
1839: hr_utility.set_location (l_proc, 20);
1840: END IF;
1841:
1842: -- need to do OTM validation
1843: validate_timecard (p_operation => p_operation,
1970: -- Bug 16598207
1971: l_earn_policy VARCHAR2(50);
1972:
1973: BEGIN
1974: g_debug := hr_utility.debug_enabled;
1975:
1976: OPEN get_debug;
1977:
1978: FETCH get_debug
1979: INTO l_debug;
1980:
1981: IF get_debug%FOUND
1982: THEN
1983: hr_utility.trace_on (NULL, 'OTLR');
1984: END IF;
1985:
1986: hxt_time_collection.set_cache (FALSE);
1987:
2010:
2011: IF g_debug
2012: THEN
2013: l_proc := 'hxt_hxc_retrieval_process.otlr_review_details';
2014: hr_utility.TRACE ('******** IN POPULATE DETAILS **********');
2015: END IF;
2016:
2017: IF l_timecard_detail.COUNT > 0
2018: THEN
2075: l_min_bb_id := -1;
2076:
2077: IF g_debug
2078: THEN
2079: hr_utility.TRACE ('*********** FIND MIN BB ID ************');
2080: END IF;
2081:
2082: FOR l_cnt IN p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2083: LOOP
2082: FOR l_cnt IN p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2083: LOOP
2084: IF g_debug
2085: THEN
2086: hr_utility.set_location (l_proc, 5);
2087: END IF;
2088:
2089: IF p_time_building_blocks (l_cnt).time_building_block_id <
2090: l_min_bb_id
2095: END LOOP;
2096:
2097: IF g_debug
2098: THEN
2099: hr_utility.TRACE ('MIN BB ID IS : ' || TO_CHAR (l_min_bb_id));
2100: END IF;
2101:
2102: l_time_building_block_id := l_min_bb_id;
2103:
2110: FOR i IN 1 .. 3
2111: LOOP
2112: IF g_debug
2113: THEN
2114: hr_utility.set_location (l_proc, 6);
2115: END IF;
2116:
2117: FOR l_cnt IN
2118: p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2118: p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2119: LOOP
2120: IF g_debug
2121: THEN
2122: hr_utility.set_location (l_proc, 10);
2123: hr_utility.TRACE
2124: ('*********** NEW TIME BUILDING BLOCK ************');
2125: END IF;
2126:
2119: LOOP
2120: IF g_debug
2121: THEN
2122: hr_utility.set_location (l_proc, 10);
2123: hr_utility.TRACE
2124: ('*********** NEW TIME BUILDING BLOCK ************');
2125: END IF;
2126:
2127: l_bb_id := p_time_building_blocks (l_cnt).time_building_block_id;
2149: l_date_to := p_time_building_blocks (l_cnt).date_to;
2150:
2151: IF g_debug
2152: THEN
2153: hr_utility.TRACE ('Time Bld Blk ID is :' || TO_CHAR (l_bb_id)
2154: );
2155: hr_utility.TRACE ('Type is :' || l_type);
2156: hr_utility.TRACE ( 'Measure is :'
2157: || TO_CHAR (l_measure)
2151: IF g_debug
2152: THEN
2153: hr_utility.TRACE ('Time Bld Blk ID is :' || TO_CHAR (l_bb_id)
2154: );
2155: hr_utility.TRACE ('Type is :' || l_type);
2156: hr_utility.TRACE ( 'Measure is :'
2157: || TO_CHAR (l_measure)
2158: );
2159: hr_utility.TRACE ( 'Start time is :'
2152: THEN
2153: hr_utility.TRACE ('Time Bld Blk ID is :' || TO_CHAR (l_bb_id)
2154: );
2155: hr_utility.TRACE ('Type is :' || l_type);
2156: hr_utility.TRACE ( 'Measure is :'
2157: || TO_CHAR (l_measure)
2158: );
2159: hr_utility.TRACE ( 'Start time is :'
2160: || TO_CHAR (l_start_time,
2155: hr_utility.TRACE ('Type is :' || l_type);
2156: hr_utility.TRACE ( 'Measure is :'
2157: || TO_CHAR (l_measure)
2158: );
2159: hr_utility.TRACE ( 'Start time is :'
2160: || TO_CHAR (l_start_time,
2161: 'DD-MON-YYYY HH:MI:SS'
2162: )
2163: );
2160: || TO_CHAR (l_start_time,
2161: 'DD-MON-YYYY HH:MI:SS'
2162: )
2163: );
2164: hr_utility.TRACE ( 'Stop time is :'
2165: || TO_CHAR (l_stop_time,
2166: 'DD-MON-YYYY HH:MI:SS'
2167: )
2168: );
2165: || TO_CHAR (l_stop_time,
2166: 'DD-MON-YYYY HH:MI:SS'
2167: )
2168: );
2169: hr_utility.TRACE ('Scope is :' || l_scope);
2170: hr_utility.TRACE ( 'Resource id is :'
2171: || TO_CHAR (l_resource_id)
2172: );
2173: hr_utility.TRACE ('Resource type is :' || l_resource_type);
2166: 'DD-MON-YYYY HH:MI:SS'
2167: )
2168: );
2169: hr_utility.TRACE ('Scope is :' || l_scope);
2170: hr_utility.TRACE ( 'Resource id is :'
2171: || TO_CHAR (l_resource_id)
2172: );
2173: hr_utility.TRACE ('Resource type is :' || l_resource_type);
2174: --
2169: hr_utility.TRACE ('Scope is :' || l_scope);
2170: hr_utility.TRACE ( 'Resource id is :'
2171: || TO_CHAR (l_resource_id)
2172: );
2173: hr_utility.TRACE ('Resource type is :' || l_resource_type);
2174: --
2175: hr_utility.TRACE ( 'Unit of Measure is :'
2176: || p_time_building_blocks (l_cnt).unit_of_measure
2177: );
2171: || TO_CHAR (l_resource_id)
2172: );
2173: hr_utility.TRACE ('Resource type is :' || l_resource_type);
2174: --
2175: hr_utility.TRACE ( 'Unit of Measure is :'
2176: || p_time_building_blocks (l_cnt).unit_of_measure
2177: );
2178: hr_utility.TRACE
2179: ( 'Parent Bld Blk ID is :'
2174: --
2175: hr_utility.TRACE ( 'Unit of Measure is :'
2176: || p_time_building_blocks (l_cnt).unit_of_measure
2177: );
2178: hr_utility.TRACE
2179: ( 'Parent Bld Blk ID is :'
2180: || TO_CHAR
2181: (p_time_building_blocks (l_cnt).parent_building_block_id
2182: )
2180: || TO_CHAR
2181: (p_time_building_blocks (l_cnt).parent_building_block_id
2182: )
2183: );
2184: hr_utility.TRACE ( 'Parent is new ? :'
2185: || p_time_building_blocks (l_cnt).parent_is_new
2186: );
2187: hr_utility.TRACE
2188: ( 'OVN is :'
2183: );
2184: hr_utility.TRACE ( 'Parent is new ? :'
2185: || p_time_building_blocks (l_cnt).parent_is_new
2186: );
2187: hr_utility.TRACE
2188: ( 'OVN is :'
2189: || TO_CHAR
2190: (p_time_building_blocks (l_cnt).object_version_number
2191: )
2189: || TO_CHAR
2190: (p_time_building_blocks (l_cnt).object_version_number
2191: )
2192: );
2193: hr_utility.TRACE ( 'Approval Status is :'
2194: || p_time_building_blocks (l_cnt).approval_status
2195: );
2196: hr_utility.TRACE
2197: ( 'Approval Style ID is :'
2192: );
2193: hr_utility.TRACE ( 'Approval Status is :'
2194: || p_time_building_blocks (l_cnt).approval_status
2195: );
2196: hr_utility.TRACE
2197: ( 'Approval Style ID is :'
2198: || TO_CHAR
2199: (p_time_building_blocks (l_cnt).approval_style_id
2200: )
2198: || TO_CHAR
2199: (p_time_building_blocks (l_cnt).approval_style_id
2200: )
2201: );
2202: hr_utility.TRACE
2203: ( 'Date From is :'
2204: || TO_CHAR
2205: (p_time_building_blocks (l_cnt).date_from,
2206: 'DD-MON-YYYY'
2205: (p_time_building_blocks (l_cnt).date_from,
2206: 'DD-MON-YYYY'
2207: )
2208: );
2209: hr_utility.TRACE
2210: ( 'Date To is :'
2211: || TO_CHAR
2212: (p_time_building_blocks (l_cnt).date_to,
2213: 'DD-MON-YYYY'
2212: (p_time_building_blocks (l_cnt).date_to,
2213: 'DD-MON-YYYY'
2214: )
2215: );
2216: hr_utility.TRACE ( 'Comment Text is :'
2217: || p_time_building_blocks (l_cnt).comment_text
2218: );
2219: hr_utility.TRACE
2220: ( 'Parent OVN is :'
2215: );
2216: hr_utility.TRACE ( 'Comment Text is :'
2217: || p_time_building_blocks (l_cnt).comment_text
2218: );
2219: hr_utility.TRACE
2220: ( 'Parent OVN is :'
2221: || TO_CHAR
2222: (p_time_building_blocks (l_cnt).parent_building_block_ovn
2223: )
2221: || TO_CHAR
2222: (p_time_building_blocks (l_cnt).parent_building_block_ovn
2223: )
2224: );
2225: hr_utility.TRACE ( 'NEW is :'
2226: || p_time_building_blocks (l_cnt).NEW
2227: );
2228: --
2229: hr_utility.set_location (l_proc, 20);
2225: hr_utility.TRACE ( 'NEW is :'
2226: || p_time_building_blocks (l_cnt).NEW
2227: );
2228: --
2229: hr_utility.set_location (l_proc, 20);
2230: END IF;
2231:
2232:
2233: -- Bug 8655664
2329: p_time_building_blocks.FIRST .. p_time_building_blocks.LAST
2330: LOOP
2331: IF g_debug
2332: THEN
2333: hr_utility.set_location (l_proc, 30);
2334: END IF;
2335:
2336: IF (p_time_building_blocks (l_day).time_building_block_id =
2337: l_parent_bb_id
2345: l_no_times := 'Y';
2346:
2347: IF g_debug
2348: THEN
2349: hr_utility.TRACE ( 'l_start_time is '
2350: || TO_CHAR
2351: (l_start_time,
2352: 'DD-MON-YYYY HH:MI:SS'
2353: )
2351: (l_start_time,
2352: 'DD-MON-YYYY HH:MI:SS'
2353: )
2354: );
2355: hr_utility.TRACE ( 'l_stop_time is '
2356: || TO_CHAR
2357: (l_stop_time,
2358: 'DD-MON-YYYY HH:MI:SS'
2359: )
2372: l_person_id := l_resource_id;
2373:
2374: IF g_debug
2375: THEN
2376: hr_utility.TRACE ( 'l_person_id is '
2377: || TO_CHAR (l_person_id)
2378: );
2379: END IF;
2380: END IF;
2382: l_effective_date := TRUNC (l_start_time);
2383:
2384: IF g_debug
2385: THEN
2386: hr_utility.TRACE ( 'l_effective_date is :'
2387: || TO_CHAR (l_effective_date,
2388: 'DD-MON-YYYY'
2389: )
2390: );
2426: );
2427:
2428: IF g_debug
2429: THEN
2430: hr_utility.set_location (l_proc, 50);
2431: END IF;
2432:
2433: l_field_name.DELETE;
2434: l_value.DELETE;
2436: l_context.DELETE;
2437:
2438: IF g_debug
2439: THEN
2440: hr_utility.set_location (l_proc, 55);
2441: hr_utility.TRACE ( 'number of attr is :'
2442: || TO_CHAR (p_time_attributes.COUNT)
2443: );
2444: hr_utility.set_location (l_proc, 56);
2437:
2438: IF g_debug
2439: THEN
2440: hr_utility.set_location (l_proc, 55);
2441: hr_utility.TRACE ( 'number of attr is :'
2442: || TO_CHAR (p_time_attributes.COUNT)
2443: );
2444: hr_utility.set_location (l_proc, 56);
2445: hr_utility.TRACE ('l_person_id :' || l_person_id);
2440: hr_utility.set_location (l_proc, 55);
2441: hr_utility.TRACE ( 'number of attr is :'
2442: || TO_CHAR (p_time_attributes.COUNT)
2443: );
2444: hr_utility.set_location (l_proc, 56);
2445: hr_utility.TRACE ('l_person_id :' || l_person_id);
2446: END IF;
2447:
2448: --
2441: hr_utility.TRACE ( 'number of attr is :'
2442: || TO_CHAR (p_time_attributes.COUNT)
2443: );
2444: hr_utility.set_location (l_proc, 56);
2445: hr_utility.TRACE ('l_person_id :' || l_person_id);
2446: END IF;
2447:
2448: --
2449: -- Get the attributes for this detail building block.
2456: p_time_attributes.FIRST .. p_time_attributes.LAST
2457: LOOP
2458: IF g_debug
2459: THEN
2460: hr_utility.TRACE ('l_bb_id:' || l_bb_id);
2461: hr_utility.TRACE
2462: ( 'p_time_attributes'
2463: || (l_cnt_att)
2464: || '.building_block_id:'
2457: LOOP
2458: IF g_debug
2459: THEN
2460: hr_utility.TRACE ('l_bb_id:' || l_bb_id);
2461: hr_utility.TRACE
2462: ( 'p_time_attributes'
2463: || (l_cnt_att)
2464: || '.building_block_id:'
2465: || p_time_attributes (l_cnt_att).building_block_id
2470: p_time_attributes (l_cnt_att).building_block_id
2471: THEN
2472: IF g_debug
2473: THEN
2474: hr_utility.TRACE
2475: ('----------- In Attribute Loop ----------');
2476: END IF;
2477:
2478: l_field_name (l_att) :=
2479: p_time_attributes (l_cnt_att).attribute_name;
2480:
2481: IF g_debug
2482: THEN
2483: hr_utility.TRACE ( 'field name('
2484: || l_att
2485: || ') is :'
2486: || l_field_name (l_att)
2487: );
2491: p_time_attributes (l_cnt_att).attribute_value;
2492:
2493: IF g_debug
2494: THEN
2495: hr_utility.TRACE ( 'value('
2496: || l_att
2497: || ') is : '
2498: || l_value (l_att)
2499: );
2503: p_time_attributes (l_cnt_att).bld_blk_info_type;
2504:
2505: IF g_debug
2506: THEN
2507: hr_utility.TRACE ( 'context('
2508: || l_att
2509: || ') is :'
2510: || l_context (l_att)
2511: );
2515: p_time_attributes (l_cnt_att).CATEGORY;
2516:
2517: IF g_debug
2518: THEN
2519: hr_utility.TRACE ( 'category('
2520: || l_att
2521: || ') is :'
2522: || l_category (l_att)
2523: );
2527: -- Start Bug 2930933
2528: --
2529: IF g_debug
2530: THEN
2531: hr_utility.set_location (l_proc, 56.5);
2532: END IF;
2533:
2534: IF l_field_name (l_att) = 'Dummy Element Context'
2535: AND l_context (l_att) = 'Dummy Element Context'
2536: AND l_category (l_att) = 'ELEMENT'
2537: THEN
2538: IF g_debug
2539: THEN
2540: hr_utility.set_location (l_proc, 56.6);
2541: END IF;
2542:
2543: IF l_value (l_att) IS NOT NULL
2544: THEN
2543: IF l_value (l_att) IS NOT NULL
2544: THEN
2545: IF g_debug
2546: THEN
2547: hr_utility.set_location (l_proc, 57);
2548: END IF;
2549:
2550: l_hrstype_entered := 'Y';
2551: ELSE
2550: l_hrstype_entered := 'Y';
2551: ELSE
2552: IF g_debug
2553: THEN
2554: hr_utility.set_location (l_proc, 58);
2555: END IF;
2556:
2557: l_hrstype_entered := 'N';
2558: END IF;
2558: END IF;
2559:
2560: IF g_debug
2561: THEN
2562: hr_utility.set_location (l_proc, 58.5);
2563: END IF;
2564: END IF;
2565:
2566: IF g_debug
2564: END IF;
2565:
2566: IF g_debug
2567: THEN
2568: hr_utility.set_location (l_proc, 59);
2569: END IF;
2570:
2571: -- End Bug 2930933
2572: l_att := l_att + 1;
2578: END IF;
2579:
2580: IF g_debug
2581: THEN
2582: hr_utility.TRACE ('l_att is: ' || TO_CHAR (l_att));
2583: END IF;
2584:
2585: -- Bug 2930933
2586: IF g_debug
2584:
2585: -- Bug 2930933
2586: IF g_debug
2587: THEN
2588: hr_utility.TRACE ( 'l_hrstype_entered :'
2589: || l_hrstype_entered
2590: );
2591: hr_utility.set_location (l_proc, 60);
2592: END IF;
2587: THEN
2588: hr_utility.TRACE ( 'l_hrstype_entered :'
2589: || l_hrstype_entered
2590: );
2591: hr_utility.set_location (l_proc, 60);
2592: END IF;
2593:
2594: IF l_hrstype_entered = 'N' AND i <> 1 -- Check for Bug 4548871
2595: THEN
2594: IF l_hrstype_entered = 'N' AND i <> 1 -- Check for Bug 4548871
2595: THEN
2596: IF g_debug
2597: THEN
2598: hr_utility.set_location (l_proc, 61);
2599: END IF;
2600:
2601: -- Raise an error
2602: hxc_time_entry_rules_utils_pkg.add_error_to_table
2617: END IF;
2618:
2619: IF g_debug
2620: THEN
2621: hr_utility.set_location (l_proc, 62);
2622: END IF;
2623:
2624: hxt_otc_retrieval_interface.parse_attributes
2625: (p_category => l_category,
2645: );
2646:
2647: IF g_debug
2648: THEN
2649: hr_utility.set_location (l_proc, 63);
2650: END IF;
2651:
2652: --
2653: -- Pass in Person ID for employee number - issue with going
2657: IF (l_no_times = 'Y')
2658: THEN
2659: IF g_debug
2660: THEN
2661: hr_utility.set_location (l_proc, 64);
2662: END IF;
2663:
2664: l_start_time := NULL;
2665: l_stop_time := NULL;
2666: END IF;
2667:
2668: IF g_debug
2669: THEN
2670: hr_utility.set_location (l_proc, 65);
2671: END IF;
2672:
2673: l_time_summary_id := NULL;
2674: l_time_sum_start_date := NULL;
2677: IF l_new = 'N'
2678: THEN
2679: IF g_debug
2680: THEN
2681: hr_utility.set_location (l_proc, 66);
2682: END IF;
2683:
2684: -- Bug 7415291
2685: -- Added new parameter l_tim_id
2696: );
2697:
2698: IF g_debug
2699: THEN
2700: hr_utility.TRACE ('after find_existing_timecard');
2701: hr_utility.TRACE ( 'l_time_summary_id is: '
2702: || TO_CHAR (l_time_summary_id)
2703: );
2704: END IF;
2697:
2698: IF g_debug
2699: THEN
2700: hr_utility.TRACE ('after find_existing_timecard');
2701: hr_utility.TRACE ( 'l_time_summary_id is: '
2702: || TO_CHAR (l_time_summary_id)
2703: );
2704: END IF;
2705:
2706: IF l_time_summary_id IS NOT NULL
2707: THEN
2708: IF g_debug
2709: THEN
2710: hr_utility.set_location (l_proc, 67);
2711: END IF;
2712:
2713: DELETE FROM hxt_det_hours_worked_f
2714: WHERE parent_id = l_time_summary_id;
2715: END IF;
2716:
2717: IF g_debug
2718: THEN
2719: hr_utility.set_location (l_proc, 68);
2720: END IF;
2721: END IF;
2722:
2723: IF g_debug
2721: END IF;
2722:
2723: IF g_debug
2724: THEN
2725: hr_utility.set_location (l_proc, 69);
2726: END IF;
2727:
2728: --Bug 2770487 Sonarasi 04-Apr-2003
2729: IF (l_date_to = hr_general.end_of_time)
2729: IF (l_date_to = hr_general.end_of_time)
2730: THEN
2731: IF g_debug
2732: THEN
2733: hr_utility.set_location (l_proc, 70);
2734: END IF;
2735:
2736: l_delete := 'N';
2737: ELSE
2736: l_delete := 'N';
2737: ELSE
2738: IF g_debug
2739: THEN
2740: hr_utility.set_location (l_proc, 71);
2741: END IF;
2742:
2743: l_delete := 'Y';
2744: END IF;
2744: END IF;
2745:
2746: IF g_debug
2747: THEN
2748: hr_utility.set_location (l_proc, 72);
2749: END IF;
2750:
2751: --Here We are setting the delete flag based on whether we would like to
2752: --delete the blocks or retail them.Therefore those blocks which are end
2829: );
2830:
2831: IF g_debug
2832: THEN
2833: hr_utility.set_location (l_proc, 73);
2834: END IF;
2835:
2836: IF g_otm_messages.COUNT > 0
2837: THEN
2861: IF l_otm_error IS NOT NULL
2862: THEN
2863: IF g_debug
2864: THEN
2865: hr_utility.set_location (l_proc, 74);
2866: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
2867: END IF;
2868:
2869: hxc_time_entry_rules_utils_pkg.add_error_to_table
2862: THEN
2863: IF g_debug
2864: THEN
2865: hr_utility.set_location (l_proc, 74);
2866: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
2867: END IF;
2868:
2869: hxc_time_entry_rules_utils_pkg.add_error_to_table
2870: (p_message_table => g_messages,
2885: );
2886:
2887: IF g_debug
2888: THEN
2889: hr_utility.TRACE ( 'g_messages.message_name is : '
2890: || g_messages (1).message_name
2891: );
2892: hr_utility.set_location (l_proc, 75);
2893: END IF;
2888: THEN
2889: hr_utility.TRACE ( 'g_messages.message_name is : '
2890: || g_messages (1).message_name
2891: );
2892: hr_utility.set_location (l_proc, 75);
2893: END IF;
2894:
2895: -- Bug 3012684
2896: l_error_flag := 'Y';
2898: END IF;
2899:
2900: IF g_debug
2901: THEN
2902: hr_utility.set_location (l_proc, 76);
2903: END IF;
2904:
2905: IF l_oracle_error IS NOT NULL
2906: THEN
2905: IF l_oracle_error IS NOT NULL
2906: THEN
2907: IF g_debug
2908: THEN
2909: hr_utility.set_location (l_proc, 77);
2910: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
2911: );
2912: END IF;
2913:
2906: THEN
2907: IF g_debug
2908: THEN
2909: hr_utility.set_location (l_proc, 77);
2910: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error
2911: );
2912: END IF;
2913:
2914: hxc_time_entry_rules_utils_pkg.add_error_to_table
2926: );
2927:
2928: IF g_debug
2929: THEN
2930: hr_utility.TRACE ( 'g_messages.message_name is : '
2931: || g_messages (1).message_name
2932: );
2933: hr_utility.set_location (l_proc, 78);
2934: END IF;
2929: THEN
2930: hr_utility.TRACE ( 'g_messages.message_name is : '
2931: || g_messages (1).message_name
2932: );
2933: hr_utility.set_location (l_proc, 78);
2934: END IF;
2935:
2936: -- Bug 3012684
2937: l_error_flag := 'Y';
2940: END IF;
2941:
2942: IF g_debug
2943: THEN
2944: hr_utility.set_location (l_proc, 79);
2945: END IF;
2946:
2947: l_next_index := l_timecard_detail.COUNT + 1;
2948: l_time_building_block_id := l_time_building_block_id - 1;
2987: --
2988: -- p_time_building_blocks.delete(l_cnt);
2989: IF g_debug
2990: THEN
2991: hr_utility.set_location (l_proc, 80);
2992: END IF;
2993: END IF; -- l_valid = Y
2994:
2995: IF g_debug
2993: END IF; -- l_valid = Y
2994:
2995: IF g_debug
2996: THEN
2997: hr_utility.set_location (l_proc, 81);
2998: END IF;
2999:
3000: l_hrstype_entered := 'N';
3001:
3000: l_hrstype_entered := 'N';
3001:
3002: IF g_debug
3003: THEN
3004: hr_utility.TRACE ('l_hrstype_entered :' || l_hrstype_entered);
3005: hr_utility.set_location (l_proc, 81);
3006: END IF;
3007: END LOOP;
3008: END LOOP;
3001:
3002: IF g_debug
3003: THEN
3004: hr_utility.TRACE ('l_hrstype_entered :' || l_hrstype_entered);
3005: hr_utility.set_location (l_proc, 81);
3006: END IF;
3007: END LOOP;
3008: END LOOP;
3009:
3040: IF (get_timecard_id%FOUND)
3041: THEN
3042: IF g_debug
3043: THEN
3044: hr_utility.TRACE ( 'TIM_SUM_ID IS : '
3045: || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
3046: );
3047: hr_utility.TRACE ( 'l_timecard_id is : '
3048: || TO_CHAR (l_timecard_id)
3043: THEN
3044: hr_utility.TRACE ( 'TIM_SUM_ID IS : '
3045: || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
3046: );
3047: hr_utility.TRACE ( 'l_timecard_id is : '
3048: || TO_CHAR (l_timecard_id)
3049: );
3050: hr_utility.TRACE ( 'l_time_period_id is : '
3051: || TO_CHAR (l_time_period_id)
3046: );
3047: hr_utility.TRACE ( 'l_timecard_id is : '
3048: || TO_CHAR (l_timecard_id)
3049: );
3050: hr_utility.TRACE ( 'l_time_period_id is : '
3051: || TO_CHAR (l_time_period_id)
3052: );
3053: END IF;
3054:
3111: IF l_otm_error IS NOT NULL
3112: THEN
3113: IF g_debug
3114: THEN
3115: hr_utility.set_location (l_proc, 2000);
3116: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
3117: END IF;
3118:
3119: hxc_time_entry_rules_utils_pkg.add_error_to_table
3112: THEN
3113: IF g_debug
3114: THEN
3115: hr_utility.set_location (l_proc, 2000);
3116: hr_utility.TRACE ('l_otm_error :' || l_otm_error);
3117: END IF;
3118:
3119: hxc_time_entry_rules_utils_pkg.add_error_to_table
3120: (p_message_table => g_messages,
3135: );
3136:
3137: IF g_debug
3138: THEN
3139: hr_utility.TRACE ( 'g_messages.message_name is : '
3140: || g_messages (1).message_name
3141: );
3142: END IF;
3143:
3147: IF l_oracle_error IS NOT NULL
3148: THEN
3149: IF g_debug
3150: THEN
3151: hr_utility.set_location (l_proc, 2050);
3152: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
3153: END IF;
3154:
3155: hxc_time_entry_rules_utils_pkg.add_error_to_table
3148: THEN
3149: IF g_debug
3150: THEN
3151: hr_utility.set_location (l_proc, 2050);
3152: hr_utility.TRACE ('l_oracle_error :' || l_oracle_error);
3153: END IF;
3154:
3155: hxc_time_entry_rules_utils_pkg.add_error_to_table
3156: (p_message_table => g_messages,
3167: );
3168:
3169: IF g_debug
3170: THEN
3171: hr_utility.TRACE ( 'g_messages.message_name is : '
3172: || g_messages (1).message_name
3173: );
3174: END IF;
3175:
3178: END IF;
3179:
3180: IF g_debug
3181: THEN
3182: hr_utility.set_location (l_proc, 2055);
3183: END IF;
3184:
3185: i := l_timecards.NEXT (i);
3186:
3187: IF i IS NULL
3188: THEN
3189: IF g_debug
3190: THEN
3191: hr_utility.set_location (l_proc, 2060);
3192: END IF;
3193:
3194: loop_ok := FALSE;
3195: END IF;
3195: END IF;
3196:
3197: IF g_debug
3198: THEN
3199: hr_utility.set_location (l_proc, 2065);
3200: END IF;
3201: END LOOP;
3202:
3203: IF g_debug
3201: END LOOP;
3202:
3203: IF g_debug
3204: THEN
3205: hr_utility.set_location (l_proc, 2070);
3206: END IF;
3207: END IF;
3208:
3209: IF g_debug
3207: END IF;
3208:
3209: IF g_debug
3210: THEN
3211: hr_utility.set_location (l_proc, 2075);
3212: END IF;
3213:
3214: l_next_index := 0;
3215: l_tim_sum := l_tim_sum_id_tab.FIRST;
3226: IF l_tim_sum_id_tab (l_tim_sum) IS NOT NULL
3227: THEN
3228: IF g_debug
3229: THEN
3230: hr_utility.set_location (l_proc, 2080);
3231: END IF;
3232:
3233: l_master_index := l_tim_sum;
3234: l_num_rec := 0;
3234: l_num_rec := 0;
3235:
3236: IF g_debug
3237: THEN
3238: hr_utility.TRACE ( 'TIM_SUM_ID IS : '
3239: || TO_CHAR (l_tim_sum_id_tab (l_tim_sum))
3240: );
3241: END IF;
3242:
3245: -- into l_det_cnt
3246: -- from hxt_det_hours_worked_f
3247: -- where parent_id = l_tim_sum_id_tab(l_tim_sum);
3248: --if g_debug then
3249: -- hr_utility.trace('l_det_cnt IS : ' || to_char(l_det_cnt));
3250: --end if;
3251: --
3252: OPEN get_otm_records (p_tim_sum_id => l_tim_sum_id_tab
3253: (l_tim_sum));
3254:
3255: LOOP
3256: IF g_debug
3257: THEN
3258: hr_utility.set_location (l_proc, 2085);
3259: END IF;
3260:
3261: FETCH get_otm_records
3262: INTO detail_date_worked, detail_hours, detail_time_in,
3263: detail_time_out, detail_hours_type;
3264:
3265: IF g_debug
3266: THEN
3267: hr_utility.TRACE ( 'detail_date_worked :'
3268: || detail_date_worked
3269: );
3270: hr_utility.TRACE ('detail_hours :' || detail_hours);
3271: hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3266: THEN
3267: hr_utility.TRACE ( 'detail_date_worked :'
3268: || detail_date_worked
3269: );
3270: hr_utility.TRACE ('detail_hours :' || detail_hours);
3271: hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3272: hr_utility.TRACE ('detail_time_out :' || detail_time_out);
3273: hr_utility.TRACE ('detail_hours_type :'
3274: || detail_hours_type
3267: hr_utility.TRACE ( 'detail_date_worked :'
3268: || detail_date_worked
3269: );
3270: hr_utility.TRACE ('detail_hours :' || detail_hours);
3271: hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3272: hr_utility.TRACE ('detail_time_out :' || detail_time_out);
3273: hr_utility.TRACE ('detail_hours_type :'
3274: || detail_hours_type
3275: );
3268: || detail_date_worked
3269: );
3270: hr_utility.TRACE ('detail_hours :' || detail_hours);
3271: hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3272: hr_utility.TRACE ('detail_time_out :' || detail_time_out);
3273: hr_utility.TRACE ('detail_hours_type :'
3274: || detail_hours_type
3275: );
3276: END IF;
3269: );
3270: hr_utility.TRACE ('detail_hours :' || detail_hours);
3271: hr_utility.TRACE ('detail_time_in :' || detail_time_in);
3272: hr_utility.TRACE ('detail_time_out :' || detail_time_out);
3273: hr_utility.TRACE ('detail_hours_type :'
3274: || detail_hours_type
3275: );
3276: END IF;
3277:
3279:
3280: --
3281: IF g_debug
3282: THEN
3283: hr_utility.set_location (l_proc, 2085);
3284: END IF;
3285:
3286: l_num_rec := l_num_rec + 1;
3287:
3286: l_num_rec := l_num_rec + 1;
3287:
3288: IF g_debug
3289: THEN
3290: hr_utility.TRACE ('l_num_rec :' || l_num_rec);
3291: hr_utility.TRACE ( 'detail_date_worked is : '
3292: || TO_CHAR (detail_date_worked,
3293: 'DD-MON-YYYY HH:MI:SS'
3294: )
3287:
3288: IF g_debug
3289: THEN
3290: hr_utility.TRACE ('l_num_rec :' || l_num_rec);
3291: hr_utility.TRACE ( 'detail_date_worked is : '
3292: || TO_CHAR (detail_date_worked,
3293: 'DD-MON-YYYY HH:MI:SS'
3294: )
3295: );
3292: || TO_CHAR (detail_date_worked,
3293: 'DD-MON-YYYY HH:MI:SS'
3294: )
3295: );
3296: hr_utility.TRACE ( 'detail_hours is : '
3297: || TO_CHAR (detail_hours)
3298: );
3299: hr_utility.TRACE ( 'detail_time_in is : '
3300: || TO_CHAR (detail_time_in,
3295: );
3296: hr_utility.TRACE ( 'detail_hours is : '
3297: || TO_CHAR (detail_hours)
3298: );
3299: hr_utility.TRACE ( 'detail_time_in is : '
3300: || TO_CHAR (detail_time_in,
3301: 'DD-MON-YYYY HH:MI:SS'
3302: )
3303: );
3300: || TO_CHAR (detail_time_in,
3301: 'DD-MON-YYYY HH:MI:SS'
3302: )
3303: );
3304: hr_utility.TRACE ( 'detail_time_out is : '
3305: || TO_CHAR (detail_time_out,
3306: 'DD-MON-YYYY HH:MI:SS'
3307: )
3308: );
3305: || TO_CHAR (detail_time_out,
3306: 'DD-MON-YYYY HH:MI:SS'
3307: )
3308: );
3309: hr_utility.TRACE ( 'detail_hours_type : '
3310: || TO_CHAR (detail_hours_type)
3311: );
3312: END IF;
3313:
3315: THEN
3316: --
3317: IF g_debug
3318: THEN
3319: hr_utility.set_location (l_proc, 2090);
3320: hr_utility.TRACE ('l_num_rec is 1');
3321: END IF;
3322:
3323: --
3316: --
3317: IF g_debug
3318: THEN
3319: hr_utility.set_location (l_proc, 2090);
3320: hr_utility.TRACE ('l_num_rec is 1');
3321: END IF;
3322:
3323: --
3324: l_next_index := l_tim_sum;
3324: l_next_index := l_tim_sum;
3325:
3326: IF g_debug
3327: THEN
3328: hr_utility.TRACE ('l_next_index :' || l_next_index);
3329: END IF;
3330: ELSE
3331: IF g_debug
3332: THEN
3329: END IF;
3330: ELSE
3331: IF g_debug
3332: THEN
3333: hr_utility.set_location (l_proc, 2095);
3334: hr_utility.TRACE ('l_num_rec is NOT 1');
3335: END IF;
3336:
3337: l_next_index := l_timecard_detail.COUNT + 1;
3330: ELSE
3331: IF g_debug
3332: THEN
3333: hr_utility.set_location (l_proc, 2095);
3334: hr_utility.TRACE ('l_num_rec is NOT 1');
3335: END IF;
3336:
3337: l_next_index := l_timecard_detail.COUNT + 1;
3338: l_time_building_block_id := l_time_building_block_id - 1;
3380: IF detail_hours IS NULL
3381: THEN
3382: IF g_debug
3383: THEN
3384: hr_utility.set_location (l_proc, 3000);
3385: END IF;
3386:
3387: detail_type := 'RANGE';
3388: ELSE
3387: detail_type := 'RANGE';
3388: ELSE
3389: IF g_debug
3390: THEN
3391: hr_utility.set_location (l_proc, 3005);
3392: END IF;
3393:
3394: detail_type := 'MEASURE';
3395: END IF;
3396:
3397: -- Populate the pl/sql tables before rolling back to the savepoint.
3398: IF g_debug
3399: THEN
3400: hr_utility.set_location (l_proc, 3010);
3401: hr_utility.TRACE ( 'l_next_index is : '
3402: || TO_CHAR (l_next_index)
3403: );
3404: END IF;
3397: -- Populate the pl/sql tables before rolling back to the savepoint.
3398: IF g_debug
3399: THEN
3400: hr_utility.set_location (l_proc, 3010);
3401: hr_utility.TRACE ( 'l_next_index is : '
3402: || TO_CHAR (l_next_index)
3403: );
3404: END IF;
3405:
3416: save_an_ids (l_detail_attributes(l_next_index).attribute_value);
3417:
3418: IF g_debug
3419: THEN
3420: hr_utility.TRACE ('done');
3421: hr_utility.set_location (l_proc, 3015);
3422: END IF;
3423: END LOOP;
3424:
3417:
3418: IF g_debug
3419: THEN
3420: hr_utility.TRACE ('done');
3421: hr_utility.set_location (l_proc, 3015);
3422: END IF;
3423: END LOOP;
3424:
3425: IF g_debug
3423: END LOOP;
3424:
3425: IF g_debug
3426: THEN
3427: hr_utility.set_location (l_proc, 3020);
3428: hr_utility.TRACE ('After End Loop');
3429: END IF;
3430:
3431: CLOSE get_otm_records;
3424:
3425: IF g_debug
3426: THEN
3427: hr_utility.set_location (l_proc, 3020);
3428: hr_utility.TRACE ('After End Loop');
3429: END IF;
3430:
3431: CLOSE get_otm_records;
3432: END IF; --if l_tim_sum_id_tab(l_tim_sum) is not null then
3432: END IF; --if l_tim_sum_id_tab(l_tim_sum) is not null then
3433:
3434: IF g_debug
3435: THEN
3436: hr_utility.set_location (l_proc, 3025);
3437: END IF;
3438:
3439: l_tim_sum := l_tim_sum_id_tab.NEXT (l_tim_sum);
3440: END LOOP;
3440: END LOOP;
3441:
3442: IF g_debug
3443: THEN
3444: hr_utility.set_location (l_proc, 3030);
3445: hr_utility.set_location (l_proc, 3035);
3446: END IF;
3447:
3448: ROLLBACK TO review_details;
3441:
3442: IF g_debug
3443: THEN
3444: hr_utility.set_location (l_proc, 3030);
3445: hr_utility.set_location (l_proc, 3035);
3446: END IF;
3447:
3448: ROLLBACK TO review_details;
3449:
3448: ROLLBACK TO review_details;
3449:
3450: IF g_debug
3451: THEN
3452: hr_utility.set_location (l_proc, 3040);
3453: hr_utility.TRACE ('After RollBack');
3454: hr_utility.set_location (l_proc, 3045);
3455: END IF;
3456:
3449:
3450: IF g_debug
3451: THEN
3452: hr_utility.set_location (l_proc, 3040);
3453: hr_utility.TRACE ('After RollBack');
3454: hr_utility.set_location (l_proc, 3045);
3455: END IF;
3456:
3457: IF l_timecard_detail.COUNT <> 0
3450: IF g_debug
3451: THEN
3452: hr_utility.set_location (l_proc, 3040);
3453: hr_utility.TRACE ('After RollBack');
3454: hr_utility.set_location (l_proc, 3045);
3455: END IF;
3456:
3457: IF l_timecard_detail.COUNT <> 0
3458: THEN
3457: IF l_timecard_detail.COUNT <> 0
3458: THEN
3459: IF g_debug
3460: THEN
3461: hr_utility.set_location (l_proc, 3050);
3462: END IF;
3463:
3464: FOR l_cnt IN l_timecard_detail.FIRST .. l_timecard_detail.LAST
3465: LOOP
3464: FOR l_cnt IN l_timecard_detail.FIRST .. l_timecard_detail.LAST
3465: LOOP
3466: IF g_debug
3467: THEN
3468: hr_utility.TRACE
3469: ( 'l_timecard_detail BB ID is : '
3470: || TO_CHAR
3471: (l_timecard_detail (l_cnt).time_building_block_id
3472: )
3477: FOR l_cnt IN l_detail_attributes.FIRST .. l_detail_attributes.LAST
3478: LOOP
3479: IF g_debug
3480: THEN
3481: hr_utility.TRACE
3482: ( 'l_detail_attributes BB ID is : '
3483: || TO_CHAR
3484: (l_detail_attributes (l_cnt).building_block_id
3485: )
3488: END LOOP;
3489:
3490: IF g_debug
3491: THEN
3492: hr_utility.set_location (l_proc, 3055);
3493: END IF;
3494: END IF;
3495:
3496: IF g_debug
3494: END IF;
3495:
3496: IF g_debug
3497: THEN
3498: hr_utility.set_location (l_proc, 3060);
3499: hr_utility.TRACE ('END FYI');
3500: END IF;
3501:
3502: p_messages := g_messages;
3495:
3496: IF g_debug
3497: THEN
3498: hr_utility.set_location (l_proc, 3060);
3499: hr_utility.TRACE ('END FYI');
3500: END IF;
3501:
3502: p_messages := g_messages;
3503: p_detail_build_blocks := l_timecard_detail;
3506: IF p_detail_build_blocks.COUNT <> 0
3507: THEN
3508: IF g_debug
3509: THEN
3510: hr_utility.set_location (l_proc, 3065);
3511:
3512: -- Bug 8486310
3513: -- Nothing relevant to the bug, but the below construct is illogical.
3514: -- You actually need to loop only if you have the trace set.
3516: -- And why do we need these successive IFs ?
3517: FOR l_cnt IN
3518: p_detail_build_blocks.FIRST .. p_detail_build_blocks.LAST
3519: LOOP
3520: hr_utility.TRACE
3521: ( 'p_detail_build_blocks BB ID is : '
3522: || TO_CHAR
3523: (p_detail_build_blocks (l_cnt).time_building_block_id
3524: )
3522: || TO_CHAR
3523: (p_detail_build_blocks (l_cnt).time_building_block_id
3524: )
3525: );
3526: hr_utility.TRACE ( 'p_detail_build_blocks Hours is : '
3527: || TO_CHAR
3528: (p_detail_build_blocks (l_cnt).measure
3529: )
3530: );
3531: END LOOP;
3532:
3533: FOR l_cnt IN p_detail_attributes.FIRST .. p_detail_attributes.LAST
3534: LOOP
3535: hr_utility.TRACE
3536: ( 'p_detail_attributes BB ID is : '
3537: || TO_CHAR
3538: (p_detail_attributes (l_cnt).building_block_id
3539: )
3537: || TO_CHAR
3538: (p_detail_attributes (l_cnt).building_block_id
3539: )
3540: );
3541: hr_utility.TRACE ( 'p_detail_attributes ATTR category is : '
3542: || p_detail_attributes (l_cnt).attribute_category
3543: );
3544: END LOOP;
3545:
3542: || p_detail_attributes (l_cnt).attribute_category
3543: );
3544: END LOOP;
3545:
3546: hr_utility.set_location (l_proc, 3070);
3547: END IF;
3548: END IF;
3549:
3550: hr_kflex_utility.unset_session_date (p_session_id => l_session_id);
3552: WHEN e_error
3553: THEN
3554: IF g_debug
3555: THEN
3556: hr_utility.set_location (l_proc, 3075);
3557: END IF;
3558:
3559: p_messages := g_messages;
3560: -- Rollback to the savepoint
3562: RETURN;
3563:
3564: IF g_debug
3565: THEN
3566: hr_utility.set_location (l_proc, 3080);
3567: hr_utility.TRACE ('THE END');
3568: END IF;
3569: WHEN OTHERS
3570: THEN
3563:
3564: IF g_debug
3565: THEN
3566: hr_utility.set_location (l_proc, 3080);
3567: hr_utility.TRACE ('THE END');
3568: END IF;
3569: WHEN OTHERS
3570: THEN
3571: IF g_debug
3569: WHEN OTHERS
3570: THEN
3571: IF g_debug
3572: THEN
3573: hr_utility.set_location (l_proc, 4000);
3574: END IF;
3575:
3576: p_messages := g_messages;
3577: -- Rollback to the savepoint
3922: EXIT WHEN NOT p_time_building_blocks.EXISTS(l_cnt);
3923:
3924: IF g_debug
3925: THEN
3926: hr_utility.trace('Detail '||l_cnt);
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3923:
3924: IF g_debug
3925: THEN
3926: hr_utility.trace('Detail '||l_cnt);
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3924: IF g_debug
3925: THEN
3926: hr_utility.trace('Detail '||l_cnt);
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3925: THEN
3926: hr_utility.trace('Detail '||l_cnt);
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933: hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3926: hr_utility.trace('Detail '||l_cnt);
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933: hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3934: END IF;
3927: hr_utility.trace('scope '||p_time_building_blocks(l_cnt).scope);
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933: hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3934: END IF;
3935: -- If its a DELETE or stop_time is missing
3928: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933: hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3934: END IF;
3935: -- If its a DELETE or stop_time is missing
3936: IF (p_time_building_blocks(l_cnt).SCOPE = 'DETAIL' )
3929: hr_utility.trace('detail '||p_time_building_blocks(l_cnt).time_building_block_id);
3930: hr_utility.trace('date_to '||p_time_building_blocks(l_cnt).date_to);
3931: hr_utility.trace('detail_bb_ovn '||p_time_building_blocks(l_cnt).object_version_number);
3932: hr_utility.trace('start_time '||p_time_building_blocks(l_cnt).start_time);
3933: hr_utility.trace('stop_time '||p_time_building_blocks(l_cnt).stop_time);
3934: END IF;
3935: -- If its a DELETE or stop_time is missing
3936: IF (p_time_building_blocks(l_cnt).SCOPE = 'DETAIL' )
3937: AND ( (p_time_building_blocks(l_cnt).DATE_TO <> hr_general.end_of_time)
3942: )
3943: THEN
3944: IF g_debug
3945: THEN
3946: hr_utility.trace('It is a deleted detail');
3947: END IF;
3948: l_detail_id := p_time_building_blocks(l_cnt).time_building_block_id||'-'||
3949: p_time_building_blocks(l_cnt).object_version_number;
3950: g_detail_tab(l_detail_id).detail_bb_id := p_time_building_blocks(l_cnt).time_building_block_id;
3964: IF g_detail_tab.COUNT > 0
3965: THEN
3966: IF g_debug
3967: THEN
3968: hr_utility.trace('There are deleted details, need to process ');
3969: END IF;
3970: i:= g_detail_tab.FIRST;
3971: LOOP
3972: EXIT WHEN i IS NULL;
4012: );
4013:
4014: IF g_debug
4015: THEN
4016: hr_utility.trace('update mode '||l_dt_update_mode);
4017: END IF;
4018: IF l_dt_update_mode = 'UPDATE'
4019: THEN
4020: hxc_time_entry_rules_utils_pkg.add_error_to_table