[Home] [Help]
PACKAGE: APPS.HXC_TIMEKEEPER_PROCESS
Source
1 PACKAGE hxc_timekeeper_process AUTHID CURRENT_USER AS
2 /* $Header: hxctksta.pkh 120.10.12020000.2 2012/07/09 11:31:51 asrajago ship $ */
3
4 TYPE t_time_info IS RECORD (
5 timecard_start_period DATE,
6 timecard_end_period DATE,
7 resource_id NUMBER,
8 employee_number VARCHAR (30),
9 employee_full_name VARCHAR2 (240),
10 timecard_id NUMBER,
11 timecard_ovn NUMBER,
12 check_box VARCHAR2 (5),
13 error_status VARCHAR2 (80),
14 timecard_status VARCHAR2 (80),
15 timecard_status_code VARCHAR2 (80),
16 attr_value_1 VARCHAR2 (150),
17 attr_value_2 VARCHAR2 (150),
18 attr_value_3 VARCHAR2 (150),
19 attr_value_4 VARCHAR2 (150),
20 attr_value_5 VARCHAR2 (150),
21 attr_value_6 VARCHAR2 (150),
22 attr_value_7 VARCHAR2 (150),
23 attr_value_8 VARCHAR2 (150),
24 attr_value_9 VARCHAR2 (150),
25 attr_value_10 VARCHAR2 (150),
26 attr_value_11 VARCHAR2 (150),
27 attr_value_12 VARCHAR2 (150),
28 attr_value_13 VARCHAR2 (150),
29 attr_value_14 VARCHAR2 (150),
30 attr_value_15 VARCHAR2 (150),
31 attr_value_16 VARCHAR2 (150),
32 attr_value_17 VARCHAR2 (150),
33 attr_value_18 VARCHAR2 (150),
34 attr_value_19 VARCHAR2 (150),
35 attr_value_20 VARCHAR2 (150),
36 attr_id_1 VARCHAR2 (150),
37 attr_id_2 VARCHAR2 (150),
38 attr_id_3 VARCHAR2 (150),
39 attr_id_4 VARCHAR2 (150),
40 attr_id_5 VARCHAR2 (150),
41 attr_id_6 VARCHAR2 (150),
42 attr_id_7 VARCHAR2 (150),
43 attr_id_8 VARCHAR2 (150),
44 attr_id_9 VARCHAR2 (150),
45 attr_id_10 VARCHAR2 (150),
46 attr_id_11 VARCHAR2 (150),
47 attr_id_12 VARCHAR2 (150),
48 attr_id_13 VARCHAR2 (150),
49 attr_id_14 VARCHAR2 (150),
50 attr_id_15 VARCHAR2 (150),
51 attr_id_16 VARCHAR2 (150),
52 attr_id_17 VARCHAR2 (150),
53 attr_id_18 VARCHAR2 (150),
54 attr_id_19 VARCHAR2 (150),
55 attr_id_20 VARCHAR2 (150),
56 attr_oldid_1 VARCHAR2 (150),
57 attr_oldid_2 VARCHAR2 (150),
58 attr_oldid_3 VARCHAR2 (150),
59 attr_oldid_4 VARCHAR2 (150),
60 attr_oldid_5 VARCHAR2 (150),
61 attr_oldid_6 VARCHAR2 (150),
62 attr_oldid_7 VARCHAR2 (150),
63 attr_oldid_8 VARCHAR2 (150),
64 attr_oldid_9 VARCHAR2 (150),
65 attr_oldid_10 VARCHAR2 (150),
66 attr_oldid_11 VARCHAR2 (150),
67 attr_oldid_12 VARCHAR2 (150),
68 attr_oldid_13 VARCHAR2 (150),
69 attr_oldid_14 VARCHAR2 (150),
70 attr_oldid_15 VARCHAR2 (150),
71 attr_oldid_16 VARCHAR2 (150),
72 attr_oldid_17 VARCHAR2 (150),
73 attr_oldid_18 VARCHAR2 (150),
74 attr_oldid_19 VARCHAR2 (150),
75 attr_oldid_20 VARCHAR2 (150),
76 timekeeper_action VARCHAR2 (80),
77 detail_id_1 NUMBER,
78 detail_id_2 NUMBER,
79 detail_id_3 NUMBER,
80 detail_id_4 NUMBER,
81 detail_id_5 NUMBER,
82 detail_id_6 NUMBER,
83 detail_id_7 NUMBER,
84 detail_id_8 NUMBER,
85 detail_id_9 NUMBER,
86 detail_id_10 NUMBER,
87 detail_id_11 NUMBER,
88 detail_id_12 NUMBER,
89 detail_id_13 NUMBER,
90 detail_id_14 NUMBER,
91 detail_id_15 NUMBER,
92 detail_id_16 NUMBER,
93 detail_id_17 NUMBER,
94 detail_id_18 NUMBER,
95 detail_id_19 NUMBER,
96 detail_id_20 NUMBER,
97 detail_id_21 NUMBER,
98 detail_id_22 NUMBER,
99 detail_id_23 NUMBER,
100 detail_id_24 NUMBER,
101 detail_id_25 NUMBER,
102 detail_id_26 NUMBER,
103 detail_id_27 NUMBER,
104 detail_id_28 NUMBER,
105 detail_id_29 NUMBER,
106 detail_id_30 NUMBER,
107 detail_id_31 NUMBER,
108 detail_ovn_1 NUMBER,
109 detail_ovn_2 NUMBER,
110 detail_ovn_3 NUMBER,
111 detail_ovn_4 NUMBER,
112 detail_ovn_5 NUMBER,
113 detail_ovn_6 NUMBER,
114 detail_ovn_7 NUMBER,
115 detail_ovn_8 NUMBER,
116 detail_ovn_9 NUMBER,
117 detail_ovn_10 NUMBER,
118 detail_ovn_11 NUMBER,
119 detail_ovn_12 NUMBER,
120 detail_ovn_13 NUMBER,
121 detail_ovn_14 NUMBER,
122 detail_ovn_15 NUMBER,
123 detail_ovn_16 NUMBER,
124 detail_ovn_17 NUMBER,
125 detail_ovn_18 NUMBER,
126 detail_ovn_19 NUMBER,
127 detail_ovn_20 NUMBER,
128 detail_ovn_21 NUMBER,
129 detail_ovn_22 NUMBER,
130 detail_ovn_23 NUMBER,
131 detail_ovn_24 NUMBER,
132 detail_ovn_25 NUMBER,
133 detail_ovn_26 NUMBER,
134 detail_ovn_27 NUMBER,
135 detail_ovn_28 NUMBER,
136 detail_ovn_29 NUMBER,
137 detail_ovn_30 NUMBER,
138 detail_ovn_31 NUMBER,
139 day_1 NUMBER,
140 day_2 NUMBER,
141 day_3 NUMBER,
142 day_4 NUMBER,
143 day_5 NUMBER,
144 day_6 NUMBER,
145 day_7 NUMBER,
146 day_8 NUMBER,
147 day_9 NUMBER,
148 day_10 NUMBER,
149 day_11 NUMBER,
150 day_12 NUMBER,
151 day_13 NUMBER,
152 day_14 NUMBER,
153 day_15 NUMBER,
154 day_16 NUMBER,
155 day_17 NUMBER,
156 day_18 NUMBER,
157 day_19 NUMBER,
158 day_20 NUMBER,
159 day_21 NUMBER,
160 day_22 NUMBER,
161 day_23 NUMBER,
162 day_24 NUMBER,
163 day_25 NUMBER,
164 day_26 NUMBER,
165 day_27 NUMBER,
166 day_28 NUMBER,
167 day_29 NUMBER,
168 day_30 NUMBER,
169 day_31 NUMBER,
170 time_in_1 DATE,
171 time_out_1 DATE,
172 time_in_2 DATE,
173 time_out_2 DATE,
174 time_in_3 DATE,
175 time_out_3 DATE,
176 time_in_4 DATE,
177 time_out_4 DATE,
178 time_in_5 DATE,
179 time_out_5 DATE,
180 time_in_6 DATE,
181 time_out_6 DATE,
182 time_in_7 DATE,
183 time_out_7 DATE,
184 time_in_8 DATE,
185 time_out_8 DATE,
186 time_in_9 DATE,
187 time_out_9 DATE,
188 time_in_10 DATE,
189 time_out_10 DATE,
190 time_in_11 DATE,
191 time_out_11 DATE,
192 time_in_12 DATE,
193 time_out_12 DATE,
194 time_in_13 DATE,
195 time_out_13 DATE,
196 time_in_14 DATE,
197 time_out_14 DATE,
198 time_in_15 DATE,
199 time_out_15 DATE,
200 time_in_16 DATE,
201 time_out_16 DATE,
202 time_in_17 DATE,
203 time_out_17 DATE,
204 time_in_18 DATE,
205 time_out_18 DATE,
206 time_in_19 DATE,
207 time_out_19 DATE,
208 time_in_20 DATE,
209 time_out_20 DATE,
210 time_in_21 DATE,
211 time_out_21 DATE,
212 time_in_22 DATE,
213 time_out_22 DATE,
214 time_in_23 DATE,
215 time_out_23 DATE,
216 time_in_24 DATE,
217 time_out_24 DATE,
218 time_in_25 DATE,
219 time_out_25 DATE,
220 time_in_26 DATE,
221 time_out_26 DATE,
222 time_in_27 DATE,
223 time_out_27 DATE,
224 time_in_28 DATE,
225 time_out_28 DATE,
226 time_in_29 DATE,
227 time_out_29 DATE,
228 time_in_30 DATE,
229 time_out_30 DATE,
230 time_in_31 DATE,
231 time_out_31 DATE,
232 comment_text VARCHAR2 (2000),
233 last_update_date DATE,
234 last_updated_by NUMBER (16),
235 last_update_login NUMBER (16),
236 created_by NUMBER (16),
237 creation_date DATE,
238 row_lock_id VARCHAR2 (200),
239 tc_lock_success VARCHAR2 (30),
240 person_type VARCHAR2 (2000),
241 timecard_message VARCHAR2 (240),
242 timecard_message_code VARCHAR2 (30),
243 audit_enabled VARCHAR2(30)
244 );
245
246 TYPE t_timekeeper_table IS TABLE OF t_time_info
247 INDEX BY BINARY_INTEGER;
248
249 TYPE t_day_id_info IS RECORD (
250 day_id NUMBER,
251 day_ovn NUMBER);
252
253 TYPE t_day_id_info_table IS TABLE OF t_day_id_info
254 INDEX BY BINARY_INTEGER;
255
256 TYPE t_detail_info IS RECORD (
257 detail_id NUMBER,
258 detail_ovn NUMBER,
259 measure NUMBER,
260 start_time DATE,
261 time_in DATE,
262 time_out DATE,
263 detail_comment_text VARCHAR2 (2000));
264
265 TYPE t_detail_info_table IS TABLE OF t_detail_info
266 INDEX BY BINARY_INTEGER;
267
268
269 -- Table to help to populate the timekeeper_table
270 TYPE t_buffer_info IS RECORD (
271 row_table_index NUMBER,
272 attribute1 VARCHAR2 (150),
273 attribute2 VARCHAR2 (150),
274 attribute3 VARCHAR2 (150),
275 attribute4 VARCHAR2 (150),
276 attribute5 VARCHAR2 (150),
277 attribute6 VARCHAR2 (150),
278 attribute7 VARCHAR2 (150),
279 attribute8 VARCHAR2 (150),
280 attribute9 VARCHAR2 (150),
281 attribute10 VARCHAR2 (150),
282 attribute11 VARCHAR2 (150),
283 attribute12 VARCHAR2 (150),
284 attribute13 VARCHAR2 (150),
285 attribute14 VARCHAR2 (150),
286 attribute15 VARCHAR2 (150),
287 attribute16 VARCHAR2 (150),
288 attribute17 VARCHAR2 (150),
289 attribute18 VARCHAR2 (150),
290 attribute19 VARCHAR2 (150),
291 attribute20 VARCHAR2 (150),
292 day_1 BOOLEAN,
293 day_2 BOOLEAN,
294 day_3 BOOLEAN,
295 day_4 BOOLEAN,
296 day_5 BOOLEAN,
297 day_6 BOOLEAN,
298 day_7 BOOLEAN,
299 day_8 BOOLEAN,
300 day_9 BOOLEAN,
301 day_10 BOOLEAN,
302 day_11 BOOLEAN,
303 day_12 BOOLEAN,
304 day_13 BOOLEAN,
305 day_14 BOOLEAN,
306 day_15 BOOLEAN,
307 day_16 BOOLEAN,
308 day_17 BOOLEAN,
309 day_18 BOOLEAN,
310 day_19 BOOLEAN,
311 day_20 BOOLEAN,
312 day_21 BOOLEAN,
313 day_22 BOOLEAN,
314 day_23 BOOLEAN,
315 day_24 BOOLEAN,
316 day_25 BOOLEAN,
317 day_26 BOOLEAN,
318 day_27 BOOLEAN,
319 day_28 BOOLEAN,
320 day_29 BOOLEAN,
321 day_30 BOOLEAN,
322 day_31 BOOLEAN);
323
324 TYPE t_buffer_table IS TABLE OF t_buffer_info
325 INDEX BY BINARY_INTEGER;
326
327
328 -- Use for the insert/Update delete
329 TYPE t_resouce_tc_index IS RECORD (
330 index_string VARCHAR2 (32000),
331 comment_text VARCHAR2 (2000),
332 lockid VARCHAR2 (80),
333 no_rows NUMBER);
334
335 TYPE t_resource_tc_table IS TABLE OF t_resouce_tc_index
336 INDEX BY BINARY_INTEGER;
337
338 g_resource_tc_table t_resource_tc_table;
339
340 TYPE t_base_rec IS RECORD (
341 base_id NUMBER (15),
342 attribute1 VARCHAR2 (150),
343 attribute2 VARCHAR2 (150),
344 attribute3 VARCHAR2 (150),
345 attribute4 VARCHAR2 (150),
346 attribute5 VARCHAR2 (150),
347 attribute6 VARCHAR2 (150),
348 attribute7 VARCHAR2 (150),
349 attribute8 VARCHAR2 (150),
350 attribute9 VARCHAR2 (150),
351 attribute10 VARCHAR2 (150),
352 attribute11 VARCHAR2 (150),
353 attribute12 VARCHAR2 (150),
354 attribute13 VARCHAR2 (150),
355 attribute14 VARCHAR2 (150),
356 attribute15 VARCHAR2 (150),
357 attribute16 VARCHAR2 (150),
358 attribute17 VARCHAR2 (150),
359 attribute18 VARCHAR2 (150),
360 attribute19 VARCHAR2 (150),
361 attribute20 VARCHAR2 (150));
362
363 TYPE t_base_info IS TABLE OF t_base_rec
364 INDEX BY BINARY_INTEGER;
365
366 TYPE det_rec IS RECORD (
367 resource_id NUMBER,
368 timecard_id NUMBER,
369 detailid NUMBER,
370 comment_text VARCHAR2 (2000),
371 dff_catg VARCHAR2 (80),
372 dff_oldcatg VARCHAR2 (80),
373 dff_attr1 VARCHAR2 (150),
374 dff_attr2 VARCHAR2 (150),
375 dff_attr3 VARCHAR2 (150),
376 dff_attr4 VARCHAR2 (150),
377 dff_attr5 VARCHAR2 (150),
378 dff_attr6 VARCHAR2 (150),
379 dff_attr7 VARCHAR2 (150),
380 dff_attr8 VARCHAR2 (150),
381 dff_attr9 VARCHAR2 (150),
382 dff_attr10 VARCHAR2 (150),
383 dff_attr11 VARCHAR2 (150),
384 dff_attr12 VARCHAR2 (150),
385 dff_attr13 VARCHAR2 (150),
386 dff_attr14 VARCHAR2 (150),
387 dff_attr15 VARCHAR2 (150),
388 dff_attr16 VARCHAR2 (150),
389 dff_attr17 VARCHAR2 (150),
390 dff_attr18 VARCHAR2 (150),
391 dff_attr19 VARCHAR2 (150),
392 dff_attr20 VARCHAR2 (150),
393 dff_attr21 VARCHAR2 (150),
394 dff_attr22 VARCHAR2 (150),
395 dff_attr23 VARCHAR2 (150),
396 dff_attr24 VARCHAR2 (150),
397 dff_attr25 VARCHAR2 (150),
398 dff_attr26 VARCHAR2 (150),
399 dff_attr27 VARCHAR2 (150),
400 dff_attr28 VARCHAR2 (150),
401 dff_attr29 VARCHAR2 (150),
402 dff_attr30 VARCHAR2 (150),
403 dff_oldattr1 VARCHAR2 (150),
404 dff_oldattr2 VARCHAR2 (150),
405 dff_oldattr3 VARCHAR2 (150),
406 dff_oldattr4 VARCHAR2 (150),
407 dff_oldattr5 VARCHAR2 (150),
408 dff_oldattr6 VARCHAR2 (150),
409 dff_oldattr7 VARCHAR2 (150),
410 dff_oldattr8 VARCHAR2 (150),
411 dff_oldattr9 VARCHAR2 (150),
412 dff_oldattr10 VARCHAR2 (150),
413 dff_oldattr11 VARCHAR2 (150),
414 dff_oldattr12 VARCHAR2 (150),
415 dff_oldattr13 VARCHAR2 (150),
416 dff_oldattr14 VARCHAR2 (150),
417 dff_oldattr15 VARCHAR2 (150),
418 dff_oldattr16 VARCHAR2 (150),
419 dff_oldattr17 VARCHAR2 (150),
420 dff_oldattr18 VARCHAR2 (150),
421 dff_oldattr19 VARCHAR2 (150),
422 dff_oldattr20 VARCHAR2 (150),
423 dff_oldattr21 VARCHAR2 (150),
424 dff_oldattr22 VARCHAR2 (150),
425 dff_oldattr23 VARCHAR2 (150),
426 dff_oldattr24 VARCHAR2 (150),
427 dff_oldattr25 VARCHAR2 (150),
428 dff_oldattr26 VARCHAR2 (150),
429 dff_oldattr27 VARCHAR2 (150),
430 dff_oldattr28 VARCHAR2 (150),
431 dff_oldattr29 VARCHAR2 (150),
432 dff_oldattr30 VARCHAR2 (150),
433 detail_action VARCHAR2 (80));
434
435 TYPE det_info IS TABLE OF det_rec
436 INDEX BY BINARY_INTEGER;
437
438 TYPE t_timecard_index_info_type IS RECORD (
439 time_block_row_index NUMBER);
440
441 -- Bug 14279724
442 -- Modified Index type from Binary Integer to VARCHAR2
443 TYPE t_timecard_index_info IS TABLE OF t_timecard_index_info_type
444 INDEX BY VARCHAR2(22);
445
446 TYPE t_attribute_index_info_type IS RECORD (
447 attribute_block_row_index NUMBER);
448
449 -- Bug 14279724
450 -- Modified Index type from Binary Integer to VARCHAR2
451 TYPE t_attribute_index_info IS TABLE OF t_attribute_index_info_type
452 INDEX BY VARCHAR2(22);
453
454 g_terminated_list VARCHAR2 (32000);
455
456 /* ADDED FOR 8775740
457 HR OTL ABSENCE INTEGRATION.
458
459 */
460 -- SVG ADDED
461 -- Change start
462 TYPE tk_abs_rec IS RECORD (
463 attr_id_1 VARCHAR2 (150),
464 attr_id_2 VARCHAR2 (150),
465 attr_id_3 VARCHAR2 (150),
466 attr_id_4 VARCHAR2 (150),
467 attr_id_5 VARCHAR2 (150),
468 attr_id_6 VARCHAR2 (150),
469 attr_id_7 VARCHAR2 (150),
470 attr_id_8 VARCHAR2 (150),
471 attr_id_9 VARCHAR2 (150),
472 attr_id_10 VARCHAR2 (150),
473 attr_id_11 VARCHAR2 (150),
474 attr_id_12 VARCHAR2 (150),
475 attr_id_13 VARCHAR2 (150),
476 attr_id_14 VARCHAR2 (150),
477 attr_id_15 VARCHAR2 (150),
478 attr_id_16 VARCHAR2 (150),
479 attr_id_17 VARCHAR2 (150),
480 attr_id_18 VARCHAR2 (150),
481 attr_id_19 VARCHAR2 (150),
482 attr_id_20 VARCHAR2 (150),
483 detail_id_1 NUMBER,
484 detail_id_2 NUMBER,
485 detail_id_3 NUMBER,
486 detail_id_4 NUMBER,
487 detail_id_5 NUMBER,
488 detail_id_6 NUMBER,
489 detail_id_7 NUMBER,
490 detail_id_8 NUMBER,
491 detail_id_9 NUMBER,
492 detail_id_10 NUMBER,
493 detail_id_11 NUMBER,
494 detail_id_12 NUMBER,
495 detail_id_13 NUMBER,
496 detail_id_14 NUMBER,
497 detail_id_15 NUMBER,
498 detail_id_16 NUMBER,
499 detail_id_17 NUMBER,
500 detail_id_18 NUMBER,
501 detail_id_19 NUMBER,
502 detail_id_20 NUMBER,
503 detail_id_21 NUMBER,
504 detail_id_22 NUMBER,
505 detail_id_23 NUMBER,
506 detail_id_24 NUMBER,
507 detail_id_25 NUMBER,
508 detail_id_26 NUMBER,
509 detail_id_27 NUMBER,
510 detail_id_28 NUMBER,
511 detail_id_29 NUMBER,
512 detail_id_30 NUMBER,
513 detail_id_31 NUMBER,
514 detail_ovn_1 NUMBER,
515 detail_ovn_2 NUMBER,
516 detail_ovn_3 NUMBER,
517 detail_ovn_4 NUMBER,
518 detail_ovn_5 NUMBER,
519 detail_ovn_6 NUMBER,
520 detail_ovn_7 NUMBER,
521 detail_ovn_8 NUMBER,
522 detail_ovn_9 NUMBER,
523 detail_ovn_10 NUMBER,
524 detail_ovn_11 NUMBER,
525 detail_ovn_12 NUMBER,
526 detail_ovn_13 NUMBER,
527 detail_ovn_14 NUMBER,
528 detail_ovn_15 NUMBER,
529 detail_ovn_16 NUMBER,
530 detail_ovn_17 NUMBER,
531 detail_ovn_18 NUMBER,
532 detail_ovn_19 NUMBER,
533 detail_ovn_20 NUMBER,
534 detail_ovn_21 NUMBER,
535 detail_ovn_22 NUMBER,
536 detail_ovn_23 NUMBER,
537 detail_ovn_24 NUMBER,
538 detail_ovn_25 NUMBER,
539 detail_ovn_26 NUMBER,
540 detail_ovn_27 NUMBER,
541 detail_ovn_28 NUMBER,
542 detail_ovn_29 NUMBER,
543 detail_ovn_30 NUMBER,
544 detail_ovn_31 NUMBER,
545 day_1 NUMBER,
546 day_2 NUMBER,
547 day_3 NUMBER,
548 day_4 NUMBER,
549 day_5 NUMBER,
550 day_6 NUMBER,
551 day_7 NUMBER,
552 day_8 NUMBER,
553 day_9 NUMBER,
554 day_10 NUMBER,
555 day_11 NUMBER,
556 day_12 NUMBER,
557 day_13 NUMBER,
558 day_14 NUMBER,
559 day_15 NUMBER,
560 day_16 NUMBER,
561 day_17 NUMBER,
562 day_18 NUMBER,
563 day_19 NUMBER,
564 day_20 NUMBER,
565 day_21 NUMBER,
566 day_22 NUMBER,
567 day_23 NUMBER,
568 day_24 NUMBER,
569 day_25 NUMBER,
570 day_26 NUMBER,
571 day_27 NUMBER,
572 day_28 NUMBER,
573 day_29 NUMBER,
574 day_30 NUMBER,
575 day_31 NUMBER,
576 time_in_1 DATE,
577 time_out_1 DATE,
578 time_in_2 DATE,
579 time_out_2 DATE,
580 time_in_3 DATE,
581 time_out_3 DATE,
582 time_in_4 DATE,
583 time_out_4 DATE,
584 time_in_5 DATE,
585 time_out_5 DATE,
586 time_in_6 DATE,
587 time_out_6 DATE,
588 time_in_7 DATE,
589 time_out_7 DATE,
590 time_in_8 DATE,
591 time_out_8 DATE,
592 time_in_9 DATE,
593 time_out_9 DATE,
594 time_in_10 DATE,
595 time_out_10 DATE,
596 time_in_11 DATE,
597 time_out_11 DATE,
598 time_in_12 DATE,
599 time_out_12 DATE,
600 time_in_13 DATE,
601 time_out_13 DATE,
602 time_in_14 DATE,
603 time_out_14 DATE,
604 time_in_15 DATE,
605 time_out_15 DATE,
606 time_in_16 DATE,
607 time_out_16 DATE,
608 time_in_17 DATE,
609 time_out_17 DATE,
610 time_in_18 DATE,
611 time_out_18 DATE,
612 time_in_19 DATE,
613 time_out_19 DATE,
614 time_in_20 DATE,
615 time_out_20 DATE,
616 time_in_21 DATE,
617 time_out_21 DATE,
618 time_in_22 DATE,
619 time_out_22 DATE,
620 time_in_23 DATE,
621 time_out_23 DATE,
622 time_in_24 DATE,
623 time_out_24 DATE,
624 time_in_25 DATE,
625 time_out_25 DATE,
626 time_in_26 DATE,
627 time_out_26 DATE,
628 time_in_27 DATE,
629 time_out_27 DATE,
630 time_in_28 DATE,
631 time_out_28 DATE,
632 time_in_29 DATE,
633 time_out_29 DATE,
634 time_in_30 DATE,
635 time_out_30 DATE,
636 time_in_31 DATE,
637 time_out_31 DATE
638 );
639
640 TYPE t_tk_abs_tab_type IS TABLE OF tk_abs_rec
641 INDEX BY binary_integer;
642
643 TYPE g_tk_prepop_detail_id_tab_type IS TABLE of NUMBER
644 INDEX BY binary_integer;
645
646
647 g_tk_prepop_detail_id_tab g_tk_prepop_detail_id_tab_type;
648
649 g_abs_intg_profile_set VARCHAR2(1):= 'N';
650
651 g_resource_abs_enabled VARCHAR2(1):= 'N';
652
653 g_resource_prepop_count NUMBER:=0;
654
655 TYPE g_query_exception_rec is RECORD
656 (Employee_full_name VARCHAR2(240),
657 Employee_number VARCHAR2(30),
658 Message VARCHAR2(32000)
659 );
660
661 TYPE g_query_exception_type is TABLE OF g_query_exception_rec
662 INDEX BY binary_integer;
663
664 g_query_exception_tab g_query_exception_type;
665
666
667
668 -- change end
669
670 -------------------------------------------------------------------------
671
672 PROCEDURE get_day_totals (
673 p_day_total_1 OUT NOCOPY NUMBER,
674 p_day_total_2 OUT NOCOPY NUMBER,
675 p_day_total_3 OUT NOCOPY NUMBER,
676 p_day_total_4 OUT NOCOPY NUMBER,
677 p_day_total_5 OUT NOCOPY NUMBER,
678 p_day_total_6 OUT NOCOPY NUMBER,
679 p_day_total_7 OUT NOCOPY NUMBER,
680 p_day_total_8 OUT NOCOPY NUMBER,
681 p_day_total_9 OUT NOCOPY NUMBER,
682 p_day_total_10 OUT NOCOPY NUMBER,
683 p_day_total_11 OUT NOCOPY NUMBER,
684 p_day_total_12 OUT NOCOPY NUMBER,
685 p_day_total_13 OUT NOCOPY NUMBER,
686 p_day_total_14 OUT NOCOPY NUMBER,
687 p_day_total_15 OUT NOCOPY NUMBER,
688 p_day_total_16 OUT NOCOPY NUMBER,
689 p_day_total_17 OUT NOCOPY NUMBER,
690 p_day_total_18 OUT NOCOPY NUMBER,
691 p_day_total_19 OUT NOCOPY NUMBER,
692 p_day_total_20 OUT NOCOPY NUMBER,
693 p_day_total_21 OUT NOCOPY NUMBER,
694 p_day_total_22 OUT NOCOPY NUMBER,
695 p_day_total_23 OUT NOCOPY NUMBER,
696 p_day_total_24 OUT NOCOPY NUMBER,
697 p_day_total_25 OUT NOCOPY NUMBER,
698 p_day_total_26 OUT NOCOPY NUMBER,
699 p_day_total_27 OUT NOCOPY NUMBER,
700 p_day_total_28 OUT NOCOPY NUMBER,
701 p_day_total_29 OUT NOCOPY NUMBER,
702 p_day_total_30 OUT NOCOPY NUMBER,
703 p_day_total_31 OUT NOCOPY NUMBER
704 );
705
706
707 ----------------------------------------------------------------------------
708 --
709 ----------------------------------------------------------------------------
710 PROCEDURE timekeeper_query (
711 p_timekeeper_data IN OUT NOCOPY t_timekeeper_table,
712 p_timekeeper_id IN NUMBER,
713 p_start_period IN DATE,
714 p_end_period IN DATE,
715 p_group_id IN NUMBER,
716 p_resource_id IN NUMBER,
717 p_attribute1 IN VARCHAR2,
718 p_attribute2 IN VARCHAR2,
719 p_attribute3 IN VARCHAR2,
720 p_attribute4 IN VARCHAR2,
721 p_attribute5 IN VARCHAR2,
722 p_attribute6 IN VARCHAR2,
723 p_attribute7 IN VARCHAR2,
724 p_attribute8 IN VARCHAR2,
725 p_attribute9 IN VARCHAR2,
726 p_attribute10 IN VARCHAR2,
727 p_attribute11 IN VARCHAR2,
728 p_attribute12 IN VARCHAR2,
729 p_attribute13 IN VARCHAR2,
730 p_attribute14 IN VARCHAR2,
731 p_attribute15 IN VARCHAR2,
732 p_attribute16 IN VARCHAR2,
733 p_attribute17 IN VARCHAR2,
734 p_attribute18 IN VARCHAR2,
735 p_attribute19 IN VARCHAR2,
736 p_attribute20 IN VARCHAR2,
737 p_status_code IN VARCHAR2,
738 p_rec_periodid IN NUMBER,
739 p_superflag IN VARCHAR2,
740 p_reqryflg IN VARCHAR2,
741 p_trx_lock_id IN NUMBER,
742 p_row_lock_id IN VARCHAR2,
743 p_person_type IN VARCHAR2,
744 p_message_type IN VARCHAR2,
745 p_query_type IN VARCHAR2,
746 p_lock_profile IN VARCHAR2,
747 p_message_text IN VARCHAR2,
748 p_late_reason in varchar2,
749 p_change_Reason in varchar2,
750 p_audit_enabled in varchar2,
751 p_audit_history in varchar2
752 );
753
754
755 ----------------------------------------------------------------------------
756 --
757 ----------------------------------------------------------------------------
758 PROCEDURE timekeeper_insert (p_insert_data IN OUT NOCOPY t_timekeeper_table);
759
760
761 ----------------------------------------------------------------------------
762 --
763 ----------------------------------------------------------------------------
764 PROCEDURE timekeeper_update (p_update_data IN OUT NOCOPY t_timekeeper_table);
765
766
767 ----------------------------------------------------------------------------
768 --
769 ----------------------------------------------------------------------------
770 PROCEDURE timekeeper_delete (p_delete_data IN OUT NOCOPY t_timekeeper_table);
771
772
773 ----------------------------------------------------------------------------
774 --
775 ----------------------------------------------------------------------------
776 PROCEDURE timekeeper_lock (p_lock_data IN t_timekeeper_table);
777
778
779 ----------------------------------------------------------------------------
780 PROCEDURE timekeeper_data_delete; --4300948
781 ----------------------------------------------------------------------------
782 PROCEDURE timekeeper_process (
783 p_timekeeper_id IN NUMBER,
784 p_superflag IN VARCHAR2,
785 p_rec_periodid IN NUMBER,
786 p_start_period IN DATE,
787 p_end_period IN DATE,
788 p_mode IN VARCHAR2,
789 p_messages OUT NOCOPY hxc_self_service_time_deposit.message_table,
790 p_trx_lock_id IN NUMBER,
791 p_lock_profile IN VARCHAR2
792 ,p_tk_audit_enabled IN VARCHAR2
793 ,p_tk_notify_to IN VARCHAR2
794 ,p_tk_notify_type IN VARCHAR2
795 );
796
797
798 --
799 ----------------------------------------------------------------------------
800
801 PROCEDURE populate_detail_global_table (
802 p_detail_data IN det_info,
803 p_detail_action IN VARCHAR2
804 );
805
806
807 -------------------------------------------------------------------------------
808 -- call_submit
809 -------------------------------------------------------------------------------
810 PROCEDURE call_submit (
811 p_timekeeper_id IN NUMBER,
812 p_start_period IN DATE,
813 p_end_period IN DATE,
814 p_submission_id IN NUMBER,
815 p_request_id OUT NOCOPY NUMBER
816 );
817
818
819 ----------------------------------------------------------------------------
820 --
821 ----------------------------------------------------------------------------
822 PROCEDURE run_submit (
823 p_errmsg OUT NOCOPY VARCHAR2,
824 p_errcode OUT NOCOPY NUMBER,
825 p_timekeeper_id IN NUMBER,
826 p_start_period IN VARCHAR2,
827 p_end_period IN VARCHAR2,
828 p_submission_id IN NUMBER
829 );
830
831
832 -------------------------------------------------------------------------------
833 -- populate_global_table
834 -------------------------------------------------------------------------------
835
836 PROCEDURE populate_global_table (
837 p_table_data IN t_time_info,
838 p_action IN VARCHAR2
839 );
840
841
842 ----------------------------------------------------------------------------
843 --
844 ----------------------------------------------------------------------------
845 PROCEDURE create_timecard_day_structure (
846 p_resource_id IN NUMBER,
847 p_start_period IN DATE,
848 p_end_period IN DATE,
849 p_tc_frdt IN DATE,
850 p_tc_todt IN DATE,
851 p_timecard IN OUT NOCOPY hxc_block_table_type,
852 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
853 p_day_id_info_table OUT NOCOPY t_day_id_info_table,
854 p_approval_style_id OUT NOCOPY NUMBER,
855 p_approval_status OUT NOCOPY VARCHAR2,
856 p_comment_text IN VARCHAR2,
857 p_timecard_status OUT NOCOPY VARCHAR2,
858 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info,
859 p_timecard_index_info IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info,
860 p_timecard_id OUT NOCOPY NUMBER
861 );
862
863
864 ----------------------------------------------------------------------------
865 --
866 ----------------------------------------------------------------------------
867 PROCEDURE create_detail_structure (
868 p_timekeeper_id IN NUMBER,
869 p_att_tab IN hxc_alias_utility.t_alias_att_info,
870 p_resource_id IN NUMBER,
871 p_start_period IN DATE,
872 p_end_period IN DATE,
873 p_tc_frdt IN DATE,
874 p_tc_todt IN DATE,
875 p_insert_detail IN hxc_timekeeper_process.t_time_info,
876 p_timecard IN OUT NOCOPY hxc_block_table_type,
877 p_attributes IN OUT NOCOPY hxc_attribute_table_type,
878 p_day_id_info_table IN hxc_timekeeper_process.t_day_id_info_table,
879 p_approval_style_id IN NUMBER,
880 p_attribute_index_info IN OUT NOCOPY hxc_timekeeper_process.t_attribute_index_info,
881 p_timecard_index_info IN OUT NOCOPY hxc_timekeeper_process.t_timecard_index_info,
882 p_timecard_id IN NUMBER,
883 p_mid_save IN OUT NOCOPY VARCHAR2,
884 p_comment_made_null IN OUT NOCOPY BOOLEAN,
885 p_row_lock_id OUT NOCOPY ROWID,
886 p_tk_audit_enabled IN VARCHAR2
887 );
888
889
890 -----------------------------------------------------------------------------
891 -- debug procedure
892 -----------------------------------------------------------------------------
893 PROCEDURE add_remove_submit (
894 p_resource_id IN NUMBER,
895 p_start_period IN DATE,
896 p_end_period IN DATE,
897 p_timecard_id IN NUMBER,
898 p_row_lock_id IN ROWID,
899 p_operation IN VARCHAR2,
900 p_number_rows IN NUMBER
901 );
902
903 PROCEDURE add_remove_lock (
904 p_resource_id IN NUMBER,
905 p_start_period IN DATE,
906 p_end_period IN DATE,
907 p_timecard_id IN NUMBER,
908 p_row_lock_id IN ROWID,
909 p_operation IN VARCHAR2
910 );
911
912 FUNCTION check_row_lock (p_resource_id IN NUMBER)
913 RETURN BOOLEAN;
914
915 FUNCTION get_row_lock (p_resource_id IN NUMBER)
916 RETURN VARCHAR2;
917
918
919 ------------------------------------------------------------------
920 --procedure insert_update_detail_temp()
921
922 PROCEDURE submit_resource (
923 p_timekeeper_id IN NUMBER,
924 p_start_time IN DATE,
925 p_stop_time IN DATE,
926 p_trx_id IN NUMBER,
927 p_submit_id OUT NOCOPY NUMBER,
928 p_insert OUT NOCOPY BOOLEAN,
929 p_submit_emp OUT NOCOPY hxc_timekeeper_process.tk_submit_tab,
930 p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
931 );
932
933
934 /* ADDED FOR 8775740
935 HR OTL ABSENCE INTEGRATION.
936
937 */
938 -- SVG ADDED
939 -- Change start
940
941 PROCEDURE get_absence_statuses ( p_resource_id IN NUMBER,
942 p_start_date IN DATE,
943 p_end_date IN DATE,
944 p_abs_status IN OUT NOCOPY HXC_RETRIEVE_ABSENCES.ABS_STATUS_TAB);
945
946
947
948 PROCEDURE get_pending_notif_info ( p_no_data OUT NOCOPY VARCHAR2,
949 p_employee_full_name OUT NOCOPY VARCHAR2,
950 p_employee_number OUT NOCOPY VARCHAR2,
951 p_message OUT NOCOPY VARCHAR2);
952
953 PROCEDURE get_abs_ret_fail_info ( p_no_data OUT NOCOPY VARCHAR2,
954 p_employee_full_name OUT NOCOPY VARCHAR2,
955 p_message OUT NOCOPY VARCHAR2);
956 -- 8916345
957 /*
958 get_pending_notif_info - called in the timekeeper form for showing pending notifications
959 get_abs_ret_fail_info - called in the timekeeper form for showing online ret failures
960
961 */
962
963 -- Change end
964
965
966 -----------------------------------------------------
967 FUNCTION get_det_details
968 RETURN det_info;
969
970 FUNCTION get_terminated_list
971 RETURN VARCHAR2;
972
973 TYPE t_mid_rec IS RECORD (
974 resource_id NUMBER,
975 start_time DATE,
976 mid_comment VARCHAR2 (2000));
977
978 TYPE t_mid_tab IS TABLE OF t_mid_rec
979 INDEX BY BINARY_INTEGER;
980
981 g_mid_data t_mid_tab;
982
983 TYPE tk_submit_record IS RECORD (
984 timecard_id NUMBER,
985 resource_id NUMBER,
986 start_time DATE,
987 stop_time DATE,
988 row_lock_id ROWID,
989 no_rows NUMBER);
990
991 TYPE tk_submit_tab IS TABLE OF tk_submit_record
992 INDEX BY BINARY_INTEGER;
993
994 g_submit_table tk_submit_tab;
995 g_lock_table tk_submit_tab;
996
997 -----------------------------------------------------------------------------
998 -- declare global table
999 -----------------------------------------------------------------------------
1000 g_one_day NUMBER := ( 1
1001 - 1 / 24 / 3600
1002 );
1003 g_timekeeper_data t_timekeeper_table;
1004 g_timekeeper_data_query t_timekeeper_table;
1005 g_tk_data_query_from_process t_timekeeper_table;
1006 g_alias_type VARCHAR2 (80);
1007 g_timecard hxc_block_table_type;
1008 g_attributes hxc_attribute_table_type;
1009 g_from_tk_process BOOLEAN := FALSE;
1010 g_tk_finish_process BOOLEAN := FALSE;
1011 g_negative_index NUMBER := -2;
1012 g_debbug BOOLEAN := FALSE;
1013 g_submit BOOLEAN := FALSE;
1014 g_base_att VARCHAR2 (20) := NULL;
1015 g_detail_data det_info;
1016 END hxc_timekeeper_process;
1017