[Home] [Help]
PACKAGE BODY: APPS.HXT_BATCH_VAL
Source
1 PACKAGE BODY hxt_batch_val AS
2 /* $Header: hxtbtval.pkb 120.11.12010000.4 2008/09/13 13:37:04 asrajago ship $ */
3 -- Global package name
4 g_package CONSTANT VARCHAR2 (33) := 'hxt_batch_val.';
5 -- Global Error Level - used to store worst error that occured
6 g_error_level NUMBER (1) := 0;
7 g_tc_error_level NUMBER (1) := 0;
8 g_debug boolean := hr_utility.debug_enabled;
9
10 -- Bug 6785744
11 -- Global variable to record the date inserted into fnd_sessions when
12 -- the process starts. In case the process crosses over a midnight, a new
13 -- date is put in.
14
15 g_start_sysdate DATE := TRUNC(SYSDATE);
16
17
18 FUNCTION error_level
19 RETURN NUMBER
20 IS
21 l_proc VARCHAR2 (72) ;
22 BEGIN
23 g_debug :=hr_utility.debug_enabled;
24 if g_debug then
25 l_proc := g_package
26 || 'error_level';
27 hr_utility.set_location ( 'Entering:'
28 || l_proc, 10);
29 hr_utility.set_location (
30 ' returning error level '
31 || g_error_level,
32 20
33 );
34 hr_utility.set_location ( 'Leaving:'
35 || l_proc, 30);
36 end if;
37 RETURN g_error_level;
38 END error_level;
39
40 FUNCTION tc_error_level
41 RETURN NUMBER
42 IS
43 l_proc VARCHAR2 (72) ;
44 BEGIN
45 g_debug :=hr_utility.debug_enabled;
46 if g_debug then
47 l_proc := g_package
48 || 'tc_error_level';
49 hr_utility.set_location ( 'Entering:'
50 || l_proc, 10);
51 hr_utility.set_location (
52 ' returning timecard error level '
53 || g_tc_error_level,
54 20
55 );
56 hr_utility.set_location ( 'Leaving:'
57 || l_proc, 30);
58 end if;
59 RETURN g_tc_error_level;
60 END tc_error_level;
61
62 PROCEDURE set_tc_error_level (p_tc_error_level IN NUMBER)
63 IS
64 l_proc VARCHAR2 (72) ;
65 BEGIN
66 g_debug :=hr_utility.debug_enabled;
67 if g_debug then
68 l_proc := g_package
69 || 'set_tc_error_level';
70 hr_utility.set_location ( 'Entering:'
71 || l_proc, 10);
72 end if;
73 g_tc_error_level := p_tc_error_level;
74 if g_debug then
75 hr_utility.set_location ( 'Leaving:'
76 || l_proc, 30);
77 end if;
78 END set_tc_error_level;
79
80 PROCEDURE set_tc_error_level (p_valid IN VARCHAR, p_msg_level IN VARCHAR2)
81 IS
82 l_proc VARCHAR2 (72) ;
83 BEGIN
84 g_debug :=hr_utility.debug_enabled;
85 if g_debug then
86 l_proc := g_package
87 || 'set_tc_error_level';
88 hr_utility.set_location ( 'Entering:'
89 || l_proc, 10);
90 end if;
91 IF p_valid = 'N' THEN
92 IF (p_msg_level = 'S') THEN
93 set_tc_error_level (p_tc_error_level => 3);
94 ELSIF (p_msg_level = 'E') THEN
95 set_tc_error_level (p_tc_error_level => 2);
96 ELSIF (p_msg_level = 'W') THEN
97 set_tc_error_level (p_tc_error_level => 1);
98 END IF;
99 END IF;
100
101 if g_debug then
102 hr_utility.set_location ( 'Leaving:'
103 || l_proc, 30);
104 end if;
105 END set_tc_error_level;
106
107 PROCEDURE set_error_level (p_error_level IN NUMBER)
108 IS
109 l_proc VARCHAR2 (72) ;
110 BEGIN
111 g_debug :=hr_utility.debug_enabled;
112 if g_debug then
113 l_proc := g_package
114 || 'set_error_level';
115 hr_utility.set_location ( 'Entering:'
116 || l_proc, 10);
117 end if;
118 g_error_level := p_error_level;
119 if g_debug then
120 hr_utility.set_location ( 'Leaving:'
121 || l_proc, 30);
122 end if;
123 END set_error_level;
124
125 PROCEDURE reset_error_level
126 IS
127 l_proc VARCHAR2 (72) ;
128 BEGIN
129 g_debug :=hr_utility.debug_enabled;
130 if g_debug then
131 l_proc := g_package
132 || 'reset_error_level';
133 hr_utility.set_location ( 'Entering:'
134 || l_proc, 10);
135 end if;
136 g_error_level := 0;
137 if g_debug then
138 hr_utility.set_location ( 'Leaving:'
139 || l_proc, 30);
140 end if;
141 END reset_error_level;
142
143 PROCEDURE set_error_level (p_valid IN VARCHAR, p_msg_level IN VARCHAR2)
144 IS
145 l_proc VARCHAR2 (72) ;
146 BEGIN
147 g_debug :=hr_utility.debug_enabled;
148 if g_debug then
149 l_proc := g_package
150 || 'set_error_level';
151 hr_utility.set_location ( 'Entering:'
152 || l_proc, 10);
153 end if;
154 IF p_valid = 'N'
155 THEN
156 IF (p_msg_level = 'S')
157 THEN
158 set_error_level (p_error_level => 3);
159 ELSIF ((p_msg_level = 'E') AND (NVL (error_level, 0) < 3))
160 THEN
161 set_error_level (p_error_level => 2);
162 ELSIF ((p_msg_level = 'W') AND (NVL (error_level, 0) < 2))
163 THEN
164 set_error_level (p_error_level => 1);
165 END IF;
166 END IF;
167
168 if g_debug then
169 hr_utility.set_location ( 'Leaving:'
170 || l_proc, 30);
171 end if;
172 END set_error_level;
173
174 PROCEDURE delete_prev_val_errors (
175 p_batch_id IN hxt_timecards_f.batch_id%TYPE
176 )
177 AS
178 l_proc VARCHAR2 (72) ;
179 BEGIN
180 g_debug :=hr_utility.debug_enabled;
181 if g_debug then
182 l_proc := g_package
183 || 'delete_prev_val_errors';
184 hr_utility.set_location ( 'Entering:'
185 || l_proc, 10);
186 end if;
187 DELETE FROM hxt_errors_x
188 WHERE ppb_id = p_batch_id AND location LIKE 'Validate%';
189
190 if g_debug then
191 hr_utility.set_location ( 'Leaving:'
192 || l_proc, 20);
193 end if;
194 END delete_prev_val_errors;
195
196 PROCEDURE delete_prev_val_errors (p_tim_id IN hxt_timecards_f.id%TYPE)
197 AS
198 l_proc VARCHAR2 (72) ;
199 BEGIN
200 g_debug :=hr_utility.debug_enabled;
201 if g_debug then
202 l_proc := g_package
203 || 'delete_prev_val_errors';
204 hr_utility.set_location ( 'Entering:'
205 || l_proc, 10);
206 end if;
207 DELETE FROM hxt_errors_x
208 WHERE tim_id = p_tim_id AND location LIKE 'Validate%';
209
210 if g_debug then
211 hr_utility.set_location ( 'Leaving:'
212 || l_proc, 20);
213 end if;
214 END delete_prev_val_errors;
215
216 FUNCTION errors_exist (p_tim_id IN hxt_timecards.id%TYPE)
217 RETURN BOOLEAN
218 AS
219 l_proc VARCHAR2 (72) ;
220
221 CURSOR find_errors (p_tim_id hxt_timecards.id%TYPE)
222 IS
223 SELECT 1
224 FROM hxt_errors_x
225 WHERE tim_id = p_tim_id;
226
227 l_find_error NUMBER;
228 l_error_exist BOOLEAN;
229 BEGIN
230 g_debug :=hr_utility.debug_enabled;
231 if g_debug then
232 l_proc := g_package
233 || 'errors_exist';
234 hr_utility.set_location ( 'Entering:'
235 || l_proc, 10);
236 end if;
237 OPEN find_errors (p_tim_id);
238 FETCH find_errors INTO l_find_error;
239
240 IF find_errors%FOUND
241 THEN
242 if g_debug then
243 hr_utility.set_location (
244 ' Errors exist for TC '
245 || p_tim_id,
246 20
247 );
248 end if;
249 l_error_exist := TRUE;
250 ELSE
251 l_error_exist := FALSE;
252 END IF;
253
254 CLOSE find_errors;
255 if g_debug then
256 hr_utility.set_location ( 'Leaving:'
257 || l_proc, 100);
258 end if;
259 RETURN l_error_exist;
260 END errors_exist;
261
262 FUNCTION timecard_end_date (p_tim_id IN hxt_timecards_f.id%TYPE)
263 RETURN per_time_periods.end_date%TYPE
264 AS
265 l_proc VARCHAR2 (72) ;
266
267 CURSOR csr_timecard_end_date (p_tim_id hxt_timecards_f.id%TYPE)
268 IS
269 SELECT ptp.end_date
270 FROM per_time_periods ptp, hxt_timecards_x htx
271 WHERE ptp.time_period_id = htx.time_period_id AND htx.id = p_tim_id;
272
273 l_tc_end_dt per_time_periods.end_date%TYPE;
274 BEGIN
275 g_debug :=hr_utility.debug_enabled;
276 if g_debug then
277 l_proc := g_package
278 || 'timecard_end_date';
279 hr_utility.set_location ( 'Entering:'
280 || l_proc, 10);
281 end if;
282 OPEN csr_timecard_end_date (p_tim_id);
283 FETCH csr_timecard_end_date INTO l_tc_end_dt;
284 CLOSE csr_timecard_end_date;
285 if g_debug then
286 hr_utility.set_location ( ' returning '
287 || l_tc_end_dt, 30);
288 hr_utility.set_location ( 'Leaving:'
289 || l_proc, 20);
290 end if;
291 RETURN l_tc_end_dt;
292 END timecard_end_date;
293
294 FUNCTION person_effective_at_tc_end (
295 p_person_id IN per_people_f.person_id%TYPE,
296 p_tim_id IN hxt_timecards.id%TYPE
297 )
298 RETURN BOOLEAN
299 IS
300 l_proc VARCHAR2 (72);
301
302
303 CURSOR csr_person_effective (
304 p_effective_date per_people_f.effective_end_date%TYPE,
305 p_person_id per_people_f.person_id%TYPE
306 )
307 IS
308 SELECT 1
309 FROM per_people_f ppf
310 WHERE person_id = p_person_id
311 AND p_effective_date BETWEEN ppf.effective_start_date
312 AND ppf.effective_end_date;
313
314 l_person_effective BOOLEAN;
315 l_found_person NUMBER;
316 l_eff_dt per_people_f.effective_end_date%TYPE;
317 BEGIN
318 g_debug :=hr_utility.debug_enabled;
319 if g_debug then
320 l_proc := g_package
321 || 'person_effective_at_tc_end';
322 hr_utility.set_location ( 'Entering:'
323 || l_proc, 10);
324 end if;
325 l_eff_dt := timecard_end_date (p_tim_id => p_tim_id);
326 OPEN csr_person_effective (l_eff_dt, p_person_id);
327 FETCH csr_person_effective INTO l_found_person;
328
329 IF (csr_person_effective%FOUND)
330 THEN
331 if g_debug then
332 hr_utility.set_location (
333 ' Person '
334 || p_person_id
335 || ' is effective on '
336 || l_eff_dt
337 || '.',
338 20
339 );
340 end if;
341 l_person_effective := TRUE;
342 ELSE
343 l_person_effective := FALSE;
344 END IF;
345
346 CLOSE csr_person_effective;
347 if g_debug then
348 hr_utility.set_location ( 'Leaving:'
349 || l_proc, 20);
350 end if;
351 RETURN l_person_effective;
352 END person_effective_at_tc_end;
353
354 PROCEDURE record_error (
355 p_batch_id IN NUMBER,
356 p_period_id IN hxt_timecards.time_period_id%TYPE,
357 p_tim_id IN hxt_timecards.id%TYPE,
358 p_error_code IN VARCHAR2
359 )
360 AS
361 l_proc VARCHAR2 (72) ;
362 l_validate_time CONSTANT VARCHAR2 (72) := 'Validate Time';
363 l_error_msg VARCHAR2 (255);
364 l_valid VARCHAR2 (1);
365 l_msg_level VARCHAR2 (1);
366 BEGIN
367 g_debug :=hr_utility.debug_enabled;
368 if g_debug then
369 l_proc := g_package
370 || 'record_error';
371 hr_utility.set_location ( 'Entering:'
372 || l_proc, 10);
373 end if;
374 hxt_util.set_timecard_error (
375 p_batch_id,
376 p_tim_id,
377 NULL,
378 p_period_id,
379 l_error_msg,
380 l_validate_time,
381 SQLERRM,
382 p_error_code,
383 l_valid,
384 l_msg_level
385 );
386 set_error_level (p_valid => l_valid, p_msg_level => l_msg_level);
387 set_tc_error_level (p_valid => l_valid, p_msg_level => l_msg_level);
388 if g_debug then
389 hr_utility.set_location ( 'Leaving:'
390 || l_proc, 100);
391 end if;
392 END record_error;
393
394 PROCEDURE person_validation (
395 p_batch_id IN NUMBER,
396 p_person_id IN hxt_timecards.for_person_id%TYPE,
397 p_period_id IN hxt_timecards.time_period_id%TYPE,
398 p_tim_id IN hxt_timecards.id%TYPE
399 )
400 AS
401 l_proc VARCHAR2 (72) ;
402 l_error_msg VARCHAR2 (255);
403 l_valid VARCHAR2 (1);
404 l_msg_level VARCHAR2 (1);
405 BEGIN
406 g_debug :=hr_utility.debug_enabled;
407 if g_debug then
408 l_proc := g_package
409 || 'person_validation';
410 hr_utility.set_location ( 'Entering:'
411 || l_proc, 10);
412 end if;
413 IF NOT (person_effective_at_tc_end (
414 p_person_id=> p_person_id,
415 p_tim_id=> p_tim_id
416 )
417 )
418 THEN
419 record_error (
420 p_batch_id=> p_batch_id,
421 p_tim_id=> p_tim_id,
422 p_period_id=> p_period_id,
423 p_error_code=> c_39316_person_nf
424 );
425 END IF;
426
427 if g_debug then
428 hr_utility.set_location ( 'Leaving:'
429 || l_proc, 100);
430 end if;
431 END person_validation;
432
433 PROCEDURE excess_pto (
434 p_batch_id IN NUMBER,
435 p_calculation_date IN hxt_sum_hours_worked_x.date_worked%TYPE,
436 p_person_id IN hxt_timecards.for_person_id%TYPE,
437 p_period_id IN hxt_timecards.time_period_id%TYPE,
438 p_tim_id IN hxt_timecards.id%TYPE
439 )
440 IS
441 l_proc VARCHAR2 (72) ;
442 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
443 l_charged_hrs NUMBER;
444 l_accrued_hrs NUMBER;
445 BEGIN
446 g_debug :=hr_utility.debug_enabled;
447 if g_debug then
448 l_proc := g_package
449 || 'excess_pto';
450 hr_utility.set_location ( 'Entering:'
451 || l_proc, 10);
452 end if;
453
454 -- M.Bhammar - Bug:5107665
455 -- hr_session_utilities.insert_session_row (TO_DATE (SYSDATE,'YYYY/MM/DD'));
456 -- hr_session_utilities.insert_session_row (SYSDATE); /* Bug 6024976 */
460 -- M.Bhammar - bug 5214727
457
458 IF (hxt_util.accrual_exceeded (
459 p_tim_id,
461 -- timecard_end_date (p_tim_id => p_tim_id),
462 p_calculation_date,
463 l_accrual_plan_name,
464 l_charged_hrs,
465 l_accrued_hrs
466 )
467 )
468 THEN
469 record_error (
470 p_batch_id=> p_batch_id,
471 p_tim_id=> p_tim_id,
472 p_period_id=> p_period_id,
473 p_error_code=> c_39335_exceeded_accrued_hrs
474 );
475 END IF;
476
477 -- hr_session_utilities.remove_session_row; /* Bug 6024976 */
478 if g_debug then
479 hr_utility.set_location ( 'Leaving:'
480 || l_proc, 100);
481 end if;
482 END excess_pto;
483
484 FUNCTION primary_assignment_id (
485 p_person_id IN per_people_f.person_id%TYPE,
486 p_effective_date IN DATE
487 )
488 RETURN per_all_assignments_f.assignment_id%TYPE
489 IS
490 l_proc VARCHAR2 (72) ;
491
492 CURSOR csr_prim_asg_id (
493 p_person_id per_people_f.person_id%TYPE,
494 p_effective_date DATE
495 )
496 IS
497
498 SELECT /*+ ORDERED
499 INDEX(paf PER_ASSIGNMENTS_F_N12)
500 INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
501 /* Hints supplied to always force the correct execution plan */
502 paf.assignment_id
503 FROM
504 per_assignments_f paf,
505 per_assignment_status_types past
506 WHERE
507 paf.person_id = p_person_id AND
508 p_effective_date BETWEEN
509 paf.effective_start_date AND paf.effective_end_date AND
510 paf.primary_flag = 'Y'
511 AND
512 past.assignment_status_type_id = paf.assignment_status_type_id AND
513 past.per_system_status = 'ACTIVE_ASSIGN';
514
515 l_prim_asg_id per_all_assignments_f.assignment_id%TYPE;
516 BEGIN
517 g_debug :=hr_utility.debug_enabled;
518 if g_debug then
519 l_proc := g_package
520 || 'primary_assignment_id';
521 hr_utility.set_location ( 'Entering:'
522 || l_proc, 10);
523 end if;
524 OPEN csr_prim_asg_id (p_person_id, p_effective_date);
525 FETCH csr_prim_asg_id INTO l_prim_asg_id;
526 CLOSE csr_prim_asg_id;
527 if g_debug then
528 hr_utility.set_location ( ' returning '
529 || l_prim_asg_id, 20);
530 hr_utility.set_location ( 'Leaving:'
531 || l_proc, 30);
532 end if;
533 RETURN l_prim_asg_id;
534 END primary_assignment_id;
535
536 FUNCTION holiday_calendar_id (
537 p_person_id IN per_people_f.person_id%TYPE,
538 p_effective_date IN DATE
539 )
540 RETURN hxt_holiday_calendars.id%TYPE
541 IS
542 l_proc VARCHAR2 (72) ;
543 l_holiday_calendar_id hxt_holiday_calendars.id%TYPE;
544 BEGIN
545 g_debug :=hr_utility.debug_enabled;
546 if g_debug then
547 l_proc := g_package
548 || 'holiday_calendar_id';
549 hr_utility.set_location ( 'Entering:'
550 || l_proc, 10);
551 end if;
552 l_holiday_calendar_id :=
553 holiday_calendar_id (
554 p_assignment_id=> primary_assignment_id (
555 p_person_id=> p_person_id,
556 p_effective_date=> p_effective_date
557 ),
558 p_effective_date=> p_effective_date
559 );
560 if g_debug then
561 hr_utility.set_location (
562 ' returning '
563 || l_holiday_calendar_id,
564 20
565 );
566 hr_utility.set_location ( 'Leaving:'
567 || l_proc, 30);
568 end if;
569 RETURN l_holiday_calendar_id;
570 END holiday_calendar_id;
571
572 FUNCTION holiday_calendar_id (
573 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
574 p_effective_date IN DATE
575 )
576 RETURN hxt_holiday_calendars.id%TYPE
577 IS
578 l_proc VARCHAR2 (72) ;
579
580 CURSOR csr_holiday_calendar_id (
581 p_assignment_id per_all_assignments_f.assignment_id%TYPE,
582 p_effective_date DATE
583 )
584 IS
585 SELECT hhc.id
586 FROM hxt_holiday_calendars hhc,
587 hxt_earning_policies hep,
588 hxt_add_assign_info_f haaif
589 WHERE p_effective_date BETWEEN hhc.effective_start_date
590 AND hhc.effective_end_date
591 AND hep.hcl_id = hhc.id
592 AND p_effective_date BETWEEN hep.effective_start_date
593 AND hep.effective_end_date
594 AND haaif.earning_policy = hep.id
595 AND p_effective_date BETWEEN haaif.effective_start_date
596 AND haaif.effective_end_date
597 AND haaif.assignment_id = p_assignment_id;
598
602 if g_debug then
599 l_holiday_calendar_id hxt_holiday_calendars.id%TYPE;
600 BEGIN
601 g_debug :=hr_utility.debug_enabled;
603 l_proc := g_package
604 || 'holiday_calendar_id';
605 hr_utility.set_location ( 'Entering:'
606 || l_proc, 10);
607 end if;
608 OPEN csr_holiday_calendar_id (p_assignment_id, p_effective_date);
609 FETCH csr_holiday_calendar_id INTO l_holiday_calendar_id;
610 CLOSE csr_holiday_calendar_id;
611 if g_debug then
612 hr_utility.set_location (
613 ' returning '
614 || l_holiday_calendar_id,
615 20
616 );
617 hr_utility.set_location ( 'Leaving:'
618 || l_proc, 30);
619 end if;
620 RETURN l_holiday_calendar_id;
621 END holiday_calendar_id;
622
623 FUNCTION holiday_element_id (p_hol_cal_id IN hxt_holiday_calendars.id%TYPE)
624 RETURN hxt_holiday_calendars.element_type_id%TYPE
625 IS
626 l_proc VARCHAR2 (72);
627
628 CURSOR csr_holiday_element_id (
629 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
630 )
631 IS
632 SELECT hhc.element_type_id
633 FROM hxt_holiday_calendars hhc
634 WHERE hhc.id = p_hol_cal_id;
635
636 l_holiday_element_id hxt_holiday_calendars.element_type_id%TYPE;
637 BEGIN
638 g_debug :=hr_utility.debug_enabled;
639 if g_debug then
640 l_proc := g_package
641 || 'holiday_element_id';
642 hr_utility.set_location ( 'Entering:'
643 || l_proc, 10);
644 hr_utility.set_location ( ' p_hol_cal_id IN = '
645 || p_hol_cal_id, 20);
646 end if;
647 OPEN csr_holiday_element_id (p_hol_cal_id);
648 FETCH csr_holiday_element_id INTO l_holiday_element_id;
649 CLOSE csr_holiday_element_id;
650 if g_debug then
651 hr_utility.set_location ( ' returning '
652 || l_holiday_element_id, 30);
653 hr_utility.set_location ( 'Leaving:'
654 || l_proc, 40);
655 end if;
656 RETURN l_holiday_element_id;
657 END holiday_element_id;
658
659 FUNCTION day_is_holiday (
660 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
661 p_day IN DATE
662 )
663 RETURN BOOLEAN
664 IS
665 l_proc VARCHAR2 (72);
666
667 CURSOR csr_holiday_today (
668 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
669 p_day DATE
670 )
671 IS
672 SELECT 1
673 FROM hxt_holiday_calendars hhc, hxt_holiday_days hhd
674 WHERE hhc.id = p_hol_cal_id
675 AND hhc.id = hhd.hcl_id
676 AND hhd.holiday_date = p_day
677 AND hhd.hours >= 0;
678
679 l_rec_holiday_today csr_holiday_today%ROWTYPE;
680 l_holiday_today BOOLEAN;
681 BEGIN
682 g_debug :=hr_utility.debug_enabled;
683 if g_debug then
684 l_proc := g_package
685 || 'day_is_holiday';
686 hr_utility.set_location ( 'Entering:'
687 || l_proc, 10);
688 end if;
689 OPEN csr_holiday_today (p_hol_cal_id, p_day);
690 FETCH csr_holiday_today INTO l_rec_holiday_today;
691
692 IF csr_holiday_today%FOUND
693 THEN
694 if g_debug then
695 hr_utility.set_location ( ' '
696 || p_day
697 || ' is a holiday', 20);
698 end if;
699 l_holiday_today := TRUE;
700 ELSE
701 l_holiday_today := FALSE;
702 END IF;
703
704 CLOSE csr_holiday_today;
705 if g_debug then
706 hr_utility.set_location ( 'Leaving:'
707 || l_proc, 30);
708 end if;
709 RETURN l_holiday_today;
710 END day_is_holiday;
711
712 FUNCTION timecard_approved (
713 p_tim_id IN hxt_holiday_calendars.id%TYPE,
714 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
715 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
716 )
717 RETURN BOOLEAN
718 IS
719 l_proc VARCHAR2 (72) ;
720 l_approved BOOLEAN;
721 BEGIN
722 g_debug :=hr_utility.debug_enabled;
723 if g_debug then
724 l_proc := g_package
725 || 'timecard_approved';
726 hr_utility.set_location ( 'Entering:'
727 || l_proc, 10);
728 end if;
729 IF (NVL (p_source_flag, 'NOT S') = 'S')
730 THEN
731 if g_debug then
732 hr_utility.set_location ( ' '
733 || p_tim_id
734 || ' is approved', 20);
735 end if;
736 l_approved := TRUE;
737 ELSE
738 IF (p_approver_id IS NOT NULL)
739 THEN
740 if g_debug then
741 hr_utility.set_location ( ' '
742 || p_tim_id
743 || ' is approved', 30);
744 end if;
745 l_approved := TRUE;
746 ELSE
747 l_approved := FALSE;
751 if g_debug then
748 END IF;
749 END IF;
750
752 hr_utility.set_location ( 'Leaving:'
753 || l_proc, 100);
754 end if;
755 RETURN l_approved;
756 END timecard_approved;
757
758 PROCEDURE tcard_approved (
759 p_batch_id IN NUMBER,
760 p_person_id IN hxt_timecards.for_person_id%TYPE,
761 p_period_id IN hxt_timecards.time_period_id%TYPE,
762 p_tim_id IN hxt_timecards.id%TYPE,
763 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
764 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
765 )
766 IS
767 l_proc VARCHAR2 (72) ;
768 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
769 l_charged_hrs NUMBER;
770 l_accrued_hrs NUMBER;
771 BEGIN
772 g_debug :=hr_utility.debug_enabled;
773 if g_debug then
774 l_proc := g_package
775 || 'tcard_approved';
776 hr_utility.set_location ( 'Entering:'
777 || l_proc, 10);
778 end if;
779 IF NOT (timecard_approved (
780 p_tim_id=> p_tim_id,
781 p_approver_id=> p_approver_id,
782 p_source_flag=> p_source_flag
783 )
784 )
785 THEN
786 record_error (
787 p_batch_id=> p_batch_id,
788 p_tim_id=> p_tim_id,
789 p_period_id=> p_period_id,
790 p_error_code=> c_39337_timecard_not_apprved
791 );
792 END IF;
793
794 if g_debug then
795 hr_utility.set_location ( 'Leaving:'
796 || l_proc, 100);
797 end if;
798 END tcard_approved;
799
800 FUNCTION legislation_code (
801 p_bg_id IN per_business_groups.business_group_id%TYPE
802 )
803 RETURN VARCHAR2 -- per_business_groups.legislation_code%TYPE
804 IS
805 l_proc VARCHAR2 (72) ;
806
807 CURSOR csr_legislation_code (
808 p_bg_id IN hr_all_organization_units.business_group_id%TYPE
809 )
810 IS
811 SELECT pbg.legislation_code
812 FROM per_business_groups pbg
813 WHERE pbg.business_group_id = p_bg_id;
814
815 l_legislation_code per_business_groups.legislation_code%TYPE;
816 BEGIN
817 g_debug :=hr_utility.debug_enabled;
818 if g_debug then
819 l_proc := g_package
820 || 'legislation_code';
821 hr_utility.set_location ( 'Entering:'
822 || l_proc, 10);
823 end if;
824 OPEN csr_legislation_code (p_bg_id);
825 FETCH csr_legislation_code INTO l_legislation_code;
826 CLOSE csr_legislation_code;
827 if g_debug then
828 hr_utility.set_location ( ' returning '
829 || l_legislation_code, 20);
830 hr_utility.set_location ( 'Leaving:'
831 || l_proc, 30);
832 end if;
833 RETURN l_legislation_code;
834 END legislation_code;
835
836 FUNCTION legislation_code (
837 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
838 p_effective_date IN DATE
839 )
840 RETURN per_business_groups.legislation_code%TYPE
841 IS
842 l_proc VARCHAR2 (72) ;
843
844 CURSOR csr_business_group (
845 p_asg_id per_all_assignments_f.assignment_id%TYPE,
846 p_effective_date DATE
847 )
848 IS
849 SELECT paf.business_group_id
850 FROM per_assignments_f paf
851 WHERE paf.assignment_id = p_asg_id
852 AND p_effective_date BETWEEN paf.effective_start_date
853 AND paf.effective_end_date;
854
855 l_business_group_id per_assignments_f.business_group_id%TYPE;
856 l_legislation_code per_business_groups.legislation_code%TYPE;
857 BEGIN
858 g_debug :=hr_utility.debug_enabled;
859 if g_debug then
860 l_proc := g_package
861 || 'legislation_code';
862 hr_utility.set_location ( 'Entering:'
863 || l_proc, 10);
864 end if;
865 OPEN csr_business_group (p_asg_id, p_effective_date);
866 FETCH csr_business_group INTO l_business_group_id;
867 CLOSE csr_business_group;
868 l_legislation_code := legislation_code (p_bg_id => l_business_group_id);
869 if g_debug then
870 hr_utility.set_location ( ' returning '
871 || l_legislation_code, 20);
872 hr_utility.set_location ( 'Leaving:'
873 || l_proc, 30);
874 end if;
875 RETURN l_legislation_code;
876 END legislation_code;
877
878 FUNCTION assignment_is_active (
879 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
880 p_effective_date IN DATE
881 )
882 RETURN BOOLEAN
883 IS
884 l_proc VARCHAR2 (72) ;
885
886 CURSOR csr_active_assignment (
887 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
888 p_effective_date DATE
892 INDEX(paf PER_ASSIGNMENTS_F_PK)
889 )
890 IS
891 SELECT /*+ ORDERED
893 INDEX(past PER_ASSIGNMENT_STATUS_TYPE_PK) */
894 /* Hints supplied to always force the correct execution plan */
895 1
896 FROM
897 per_assignments_f paf,
898 per_assignment_status_types past
899 WHERE
900 paf.assignment_id = p_asg_id AND
901 p_effective_date BETWEEN
902 paf.effective_start_date AND paf.effective_end_date
903 AND
904 past.assignment_status_type_id = paf.assignment_status_type_id AND
905 ( past.business_group_id = paf.business_group_id OR
906 past.business_group_id IS NULL) AND
907 ( past.legislation_code IS NULL OR
908 past.legislation_code = hxt_batch_val.legislation_code(paf.business_group_id)) AND
909 past.per_system_status = 'ACTIVE_ASSIGN';
910
911 l_rec_active_assignment csr_active_assignment%ROWTYPE;
912 l_active_assignment BOOLEAN;
913 BEGIN
914 g_debug :=hr_utility.debug_enabled;
915 if g_debug then
916 l_proc := g_package
917 || 'assignment_is_active';
918 hr_utility.set_location ( 'Entering:'
919 || l_proc, 10);
920 end if;
921 OPEN csr_active_assignment (p_asg_id, p_effective_date);
922 FETCH csr_active_assignment INTO l_rec_active_assignment;
923
924 IF csr_active_assignment%FOUND
925 THEN
926 if g_debug then
927 hr_utility.set_location (
928 ' assignment '
929 || p_asg_id
930 || ' is active on '
931 || p_effective_date,
932 20
933 );
934 end if;
935 l_active_assignment := TRUE;
936 ELSE
937 l_active_assignment := FALSE;
938 END IF;
939
940 CLOSE csr_active_assignment;
941 if g_debug then
942 hr_utility.set_location ( 'Leaving:'
943 || l_proc, 30);
944 end if;
945 RETURN l_active_assignment;
946 END assignment_is_active;
947
948 PROCEDURE inactive_emp_tcard (
949 p_batch_id IN NUMBER,
950 p_person_id IN hxt_timecards.for_person_id%TYPE,
951 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
952 p_period_id IN hxt_timecards.time_period_id%TYPE,
953 p_tim_id IN hxt_timecards.id%TYPE,
954 p_day IN DATE
955 )
956 IS
957 l_proc VARCHAR2 (72) ;
958 BEGIN
959 g_debug :=hr_utility.debug_enabled;
960 if g_debug then
961 l_proc := g_package
962 || 'inactive_emp_tcard';
963 hr_utility.set_location ( 'Entering:'
964 || l_proc, 10);
965 end if;
966 IF NOT (assignment_is_active (
967 p_asg_id=> p_assignment_id,
968 p_effective_date=> p_day
969 )
970 )
971 THEN
972 record_error (
973 p_batch_id=> p_batch_id,
974 p_tim_id=> p_tim_id,
975 p_period_id=> p_period_id,
976 p_error_code=> c_39317_empl_inactive
977 );
978 END IF;
979
980 if g_debug then
981 hr_utility.set_location ( 'Leaving:'
982 || l_proc, 100);
983 end if;
984 END inactive_emp_tcard;
985
986 PROCEDURE get_holiday_info (
987 p_day IN DATE,
988 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
989 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
990 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
991 )
992 IS
993 l_proc VARCHAR2 (72) := g_package
994 || 'get_holiday_info';
995
996 CURSOR csr_holiday_info (
997 p_hol_cal_id hxt_holiday_calendars.id%TYPE,
998 p_day DATE
999 )
1000 IS
1001 SELECT hhd.hours, hhc.element_type_id
1002 FROM hxt_holiday_days hhd, hxt_holiday_calendars hhc
1003 WHERE hhd.holiday_date = p_day
1004 AND hhc.id = p_hol_cal_id
1005 AND hhd.hcl_id = hhc.id;
1006 BEGIN
1007 g_debug :=hr_utility.debug_enabled;
1008 if g_debug then
1009 hr_utility.set_location ( 'Entering:'
1010 || l_proc, 10);
1011 hr_utility.set_location ( ' p_hol_cal_id = '
1012 || p_hol_cal_id, 20);
1013 hr_utility.set_location ( ' p_day = '
1014 || p_day, 30);
1015 end if;
1016 OPEN csr_holiday_info (p_hol_cal_id, p_day);
1017 FETCH csr_holiday_info INTO p_hol_hours, p_hol_element_type_id;
1018 CLOSE csr_holiday_info;
1019 if g_debug then
1020 hr_utility.set_location (
1021 ' Found p_hol_hours = '
1022 || p_hol_hours,
1023 40
1024 );
1025 hr_utility.set_location (
1026 ' p_hol_element_type_id = '
1027 || p_hol_element_type_id,
1028 50
1029 );
1030 hr_utility.set_location ( 'Leaving:'
1031 || l_proc, 100);
1035 PROCEDURE get_holiday_info (
1032 end if;
1033 END get_holiday_info;
1034
1036 p_person_id IN per_people_f.person_id%TYPE,
1037 p_day IN DATE,
1038 p_effective_date IN DATE,
1039 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
1040 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
1041 )
1042 IS
1043 l_proc VARCHAR2 (72) ;
1044 BEGIN
1045 g_debug :=hr_utility.debug_enabled;
1046 if g_debug then
1047 l_proc := g_package
1048 || 'get_holiday_info';
1049 hr_utility.set_location ( 'Entering:'
1050 || l_proc, 10);
1051 end if;
1052 get_holiday_info (
1053 p_day => p_day,
1054 p_hol_cal_id=> holiday_calendar_id (
1055 p_person_id=> p_person_id,
1056 p_effective_date=> p_effective_date
1057 ),
1058 p_hol_hours=> p_hol_hours,
1059 p_hol_element_type_id=> p_hol_element_type_id
1060 );
1061 if g_debug then
1062 hr_utility.set_location ( 'Leaving:'
1063 || l_proc, 20);
1064 end if;
1065 END get_holiday_info;
1066
1067 FUNCTION sum_unexploded_hours (
1068 p_tim_id IN hxt_timecards.id%TYPE,
1069 p_day IN hxt_det_hours_worked_f.date_worked%TYPE,
1070 p_hours_type IN hxt_det_hours_worked_f.hours%TYPE DEFAULT NULL
1071 )
1072 RETURN NUMBER
1073 AS
1074 l_proc VARCHAR2 (72) ;
1075
1076 -- Sum the hours of one day
1077 -- .If an hours type is passed in we only sum those hours
1078 -- .If no hours type is passed in we sum all hours, with our without
1079 -- hours override
1080 CURSOR csr_sum_hours (
1081 p_tim_id IN hxt_timecards.id%TYPE,
1082 p_day hxt_det_hours_worked_f.date_worked%TYPE,
1083 p_hours_type hxt_det_hours_worked_f.hours%TYPE
1084 )
1085 IS
1086 SELECT SUM (hshwx.hours)
1087 FROM hxt_sum_hours_worked_x hshwx
1088 WHERE hshwx.tim_id = p_tim_id
1089 AND hshwx.date_worked = p_day
1090 AND ( (p_hours_type IS NULL)
1091 OR ( (p_hours_type IS NOT NULL)
1092 AND (p_hours_type = hshwx.element_type_id)
1093 )
1094 );
1095
1096 l_sum_hours NUMBER;
1097 BEGIN
1098 g_debug :=hr_utility.debug_enabled;
1099 if g_debug then
1100 l_proc := g_package
1101 || 'sum_unexploded_hours';
1102 hr_utility.set_location ( 'Entering:'
1103 || l_proc, 10);
1104 end if;
1105 OPEN csr_sum_hours (p_tim_id, p_day, p_hours_type);
1106 FETCH csr_sum_hours INTO l_sum_hours;
1107 CLOSE csr_sum_hours;
1108 if g_debug then
1109 hr_utility.set_location ( ' returning '
1110 || NVL (l_sum_hours, 0), 30);
1111 hr_utility.set_location ( 'Leaving:'
1112 || l_proc, 20);
1113 end if;
1114 RETURN NVL (l_sum_hours, 0);
1115 END sum_unexploded_hours;
1116
1117 PROCEDURE holiday_mismatch (
1118 p_batch_id IN NUMBER,
1119 p_person_id IN hxt_timecards.for_person_id%TYPE,
1120 p_period_id IN hxt_timecards.time_period_id%TYPE,
1121 p_tim_id IN hxt_timecards.id%TYPE,
1122 p_day IN DATE,
1123 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1124 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1125 )
1126 IS
1127 l_proc VARCHAR2 (72) ;
1128 l_hol_hours hxt_holiday_days.hours%TYPE;
1129 l_hol_element hxt_holiday_calendars.element_type_id%TYPE;
1130 BEGIN
1131 g_debug :=hr_utility.debug_enabled;
1132 if g_debug then
1133 l_proc := g_package
1134 || 'holiday_mismatch';
1135 hr_utility.set_location ( 'Entering:'
1136 || l_proc, 10);
1137 end if;
1138 get_holiday_info (
1139 p_day => p_day,
1140 p_hol_cal_id=> p_hol_cal_id,
1141 p_hol_hours=> l_hol_hours,
1142 p_hol_element_type_id=> l_hol_element
1143 );
1144
1145 IF ( (l_hol_hours <>
1146 sum_unexploded_hours (
1147 p_tim_id=> p_tim_id,
1148 p_day => p_day,
1149 p_hours_type=> l_hol_element
1150 )
1151 )
1152 AND (NVL (p_hours_type, -1) = l_hol_element)
1153 )
1154 THEN
1155 record_error (
1156 p_batch_id=> p_batch_id,
1157 p_tim_id=> p_tim_id,
1158 p_period_id=> p_period_id,
1159 p_error_code=> c_39334_hrs_chged_ne_cal_hrs
1160 );
1161 END IF;
1162
1163 if g_debug then
1164 hr_utility.set_location ( 'Leaving:'
1165 || l_proc, 100);
1166 end if;
1167 END holiday_mismatch;
1168
1169 PROCEDURE holiday_valid (
1173 p_tim_id IN hxt_timecards.id%TYPE,
1170 p_batch_id IN NUMBER,
1171 p_person_id IN hxt_timecards.for_person_id%TYPE,
1172 p_period_id IN hxt_timecards.time_period_id%TYPE,
1174 p_day IN DATE,
1175 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1176 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1177 )
1178 IS
1179 l_proc VARCHAR2 (72) ;
1180 BEGIN
1181 g_debug :=hr_utility.debug_enabled;
1182 if g_debug then
1183 l_proc := g_package
1184 || 'holiday_valid';
1185 hr_utility.set_location ( 'Entering:'
1186 || l_proc, 10);
1187 end if;
1188 IF (NVL (p_hours_type, -1) =
1189 holiday_element_id (p_hol_cal_id => p_hol_cal_id)
1190 )
1191 AND NOT (day_is_holiday (
1192 p_day => p_day,
1193 p_hol_cal_id=> p_hol_cal_id
1194 )
1195 )
1196 THEN
1197 record_error (
1198 p_batch_id=> p_batch_id,
1199 p_tim_id=> p_tim_id,
1200 p_period_id=> p_period_id,
1201 p_error_code=> c_39333_hol_not_valid_on_cal
1202 );
1203 END IF;
1204
1205 if g_debug then
1206 hr_utility.set_location ( 'Leaving:'
1207 || l_proc, 20);
1208 end if;
1209 END holiday_valid;
1210
1211 FUNCTION element_link (
1212 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
1213 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1214 p_effective_date IN DATE
1215 )
1216 RETURN pay_element_links_f.element_link_id%TYPE
1217 IS
1218 l_proc VARCHAR2 (72) ;
1219 l_element_link_id NUMBER;
1220 BEGIN
1221 g_debug :=hr_utility.debug_enabled;
1222 if g_debug then
1223 l_proc := g_package
1224 || 'element_link';
1225 hr_utility.set_location ( 'Entering:'
1226 || l_proc, 10);
1227 end if;
1228 BEGIN
1229 SELECT el.element_link_id
1230 INTO l_element_link_id
1231 FROM per_assignments_f asg, pay_element_links_f el
1232 WHERE asg.assignment_id = p_assignment_id
1233 AND el.business_group_id
1234 + 0 = asg.business_group_id
1235 + 0
1236 AND el.element_type_id = p_element_type_id
1237 AND p_effective_date BETWEEN asg.effective_start_date
1238 AND asg.effective_end_date
1239 AND p_effective_date BETWEEN el.effective_start_date
1240 AND el.effective_end_date
1241 AND ( ( el.payroll_id IS NOT NULL
1242 AND el.payroll_id = asg.payroll_id
1243 )
1244 OR ( el.link_to_all_payrolls_flag = 'Y'
1245 AND asg.payroll_id IS NOT NULL
1246 )
1247 OR ( el.payroll_id IS NULL
1248 AND el.link_to_all_payrolls_flag = 'N'
1249 )
1250 )
1251 AND ( el.job_id IS NULL
1252 OR el.job_id = asg.job_id
1253 )
1254 AND ( el.grade_id IS NULL
1255 OR el.grade_id = asg.grade_id
1256 )
1257 AND ( el.position_id IS NULL
1258 OR el.position_id = asg.position_id
1259 )
1260 AND ( el.organization_id IS NULL
1261 OR el.organization_id = asg.organization_id
1262 )
1263 AND ( el.location_id IS NULL
1264 OR el.location_id = asg.location_id
1265 )
1266 AND ( el.pay_basis_id IS NULL
1267 OR el.pay_basis_id = asg.pay_basis_id
1268 )
1269 AND ( el.employment_category IS NULL
1270 OR el.employment_category = asg.employment_category
1271 )
1272 AND ( el.people_group_id IS NULL
1273 OR EXISTS (
1274 SELECT NULL
1275 FROM pay_assignment_link_usages_f alu
1276 WHERE alu.assignment_id = asg.assignment_id
1277 AND alu.element_link_id = el.element_link_id
1278 AND p_effective_date
1279 BETWEEN alu.effective_start_date
1280 AND alu.effective_end_date)
1281 );
1282
1283 if g_debug then
1284 hr_utility.set_location ( 'Leaving:'
1285 || l_proc, 20);
1286 end if;
1287 EXCEPTION
1288 WHEN NO_DATA_FOUND
1289 THEN
1290 l_element_link_id := NULL;
1291 END;
1292
1293 if g_debug then
1294 hr_utility.set_location (
1295 ' returning l_element_link_id = '
1296 || l_element_link_id,
1297 20
1298 );
1299 hr_utility.set_location ( 'Leaving:'
1300 || l_proc, 30);
1304
1301 end if;
1302 RETURN l_element_link_id;
1303 END element_link;
1305 FUNCTION element_linked (
1306 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
1307 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1308 p_effective_date IN DATE
1309 )
1310 RETURN BOOLEAN
1311 IS
1312 l_proc VARCHAR2 (72);
1313 l_element_linked BOOLEAN;
1314 l_element_link_id pay_element_links_f.element_link_id%TYPE;
1315 BEGIN
1316 g_debug :=hr_utility.debug_enabled;
1317 if g_debug then
1318 l_proc := g_package
1319 || 'element_linked';
1320 hr_utility.set_location ( 'Entering:'
1321 || l_proc, 10);
1322 end if;
1323 l_element_link_id :=
1324 element_link (
1325 p_element_type_id=> p_element_type_id,
1326 p_assignment_id=> p_assignment_id,
1327 p_effective_date=> p_effective_date
1328 );
1329
1330 IF (l_element_link_id IS NOT NULL)
1331 THEN
1332 if g_debug then
1333 hr_utility.set_location (
1334 ' '
1335 || p_element_type_id
1336 || ' is a valid element for asg '
1337 || p_assignment_id
1338 || ' on '
1339 || p_effective_date,
1340 20
1341 );
1342 end if;
1343 l_element_linked := TRUE;
1344 ELSE
1345 l_element_linked := FALSE;
1346 END IF;
1347
1348 if g_debug then
1349 hr_utility.set_location ( 'Leaving:'
1350 || l_proc, 30);
1351 end if;
1352 RETURN l_element_linked;
1353 END element_linked;
1354
1355 FUNCTION valid_for_summing (
1356 p_element_id IN pay_element_types_f.element_type_id%TYPE,
1357 p_earnings_category IN hxt_add_elem_info_f.earning_category%TYPE,
1358 p_day IN DATE,
1359 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1360 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
1361 p_valid_earn_cat1 IN hxt_add_elem_info_f.earning_category%TYPE
1362 DEFAULT NULL,
1363 p_valid_earn_cat2 IN hxt_add_elem_info_f.earning_category%TYPE
1364 DEFAULT NULL,
1365 p_valid_earn_cat3 IN hxt_add_elem_info_f.earning_category%TYPE
1366 DEFAULT NULL,
1367 p_valid_earn_cat4 IN hxt_add_elem_info_f.earning_category%TYPE
1368 DEFAULT NULL,
1369 p_valid_earn_cat5 IN hxt_add_elem_info_f.earning_category%TYPE
1370 DEFAULT NULL,
1371 p_valid_earn_cat6 IN hxt_add_elem_info_f.earning_category%TYPE
1372 DEFAULT NULL,
1373 p_valid_earn_cat7 IN hxt_add_elem_info_f.earning_category%TYPE
1374 DEFAULT NULL
1375 )
1376 RETURN BOOLEAN
1377 IS
1378 l_proc VARCHAR2 (72) ;
1379 l_valid_for_summing BOOLEAN;
1380 BEGIN
1381 g_debug :=hr_utility.debug_enabled;
1382 if g_debug then
1383 l_proc := g_package
1384 || 'valid_for_summing';
1385 hr_utility.set_location ( 'Entering:'
1386 || l_proc, 10);
1387 end if;
1388 IF ( (element_linked (
1389 p_element_type_id=> p_element_id,
1390 p_assignment_id=> p_assignment_id,
1391 p_effective_date=> p_day
1392 )
1393 )
1394 AND (p_element_id <>
1395 NVL (holiday_element_id (p_hol_cal_id => p_hol_cal_id), -1)
1396 )
1397 AND (p_earnings_category IN (p_valid_earn_cat1,
1398 p_valid_earn_cat2,
1399 p_valid_earn_cat3,
1400 p_valid_earn_cat4,
1401 p_valid_earn_cat5,
1402 p_valid_earn_cat6,
1403 p_valid_earn_cat7
1404 )
1405 )
1406 )
1407 THEN
1408 if g_debug then
1409 hr_utility.set_location (
1410 ' element '
1411 || p_element_id
1412 || ' is valid for summing',
1413 20
1414 );
1415 end if;
1416 l_valid_for_summing := TRUE;
1417 ELSE
1418 l_valid_for_summing := FALSE;
1419 END IF;
1420
1421 if g_debug then
1422 hr_utility.set_location ( 'Leaving:'
1423 || l_proc, 30);
1424 end if;
1425 RETURN l_valid_for_summing;
1426 END valid_for_summing;
1427
1428 FUNCTION sum_valid_det_hours (
1429 p_tim_id IN hxt_timecards.id%TYPE,
1430 p_day IN DATE,
1431 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1432 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1433 )
1434 RETURN NUMBER
1435 IS
1436 l_proc VARCHAR2 (72) ;
1437
1438 CURSOR csr_det_hours (
1439 p_tim_id hxt_timecards.id%TYPE,
1440 p_date_worked hxt_det_hours_worked_f.date_worked%TYPE
1441 )
1442 IS
1446 WHERE hdhwx.tim_id = p_tim_id
1443 SELECT SUM (hdhwx.hours) hours, hdhwx.element_type_id,
1444 haeif.earning_category
1445 FROM hxt_det_hours_worked_x hdhwx, hxt_add_elem_info_f haeif
1447 AND hdhwx.date_worked = p_date_worked
1448 AND hdhwx.element_type_id = haeif.element_type_id
1449 AND hdhwx.date_worked BETWEEN haeif.effective_start_date
1450 AND haeif.effective_end_date
1451 GROUP BY hdhwx.date_worked,
1452 hdhwx.element_type_id,
1453 haeif.earning_category;
1454
1455 l_summed_hours NUMBER := 0;
1456 BEGIN
1457 g_debug :=hr_utility.debug_enabled;
1458 if g_debug then
1459 l_proc := g_package
1460 || 'sum_valid_det_hours';
1461 hr_utility.set_location ( 'Entering:'
1462 || l_proc, 10);
1463 end if;
1464 FOR rec_det_hours IN csr_det_hours (p_tim_id, p_day)
1465 LOOP
1466 IF (valid_for_summing (
1467 p_element_id=> rec_det_hours.element_type_id,
1468 p_earnings_category=> rec_det_hours.earning_category,
1469 p_assignment_id=> p_assignment_id,
1470 p_day => p_day,
1471 p_hol_cal_id=> p_hol_cal_id,
1472 p_valid_earn_cat1=> 'REG',
1473 p_valid_earn_cat2=> 'OVT',
1474 p_valid_earn_cat3=> 'ABS'
1475 )
1476 )
1477 THEN
1478 l_summed_hours := l_summed_hours
1479 + rec_det_hours.hours;
1480 END IF;
1481 END LOOP;
1482 if g_debug then
1483 hr_utility.set_location (
1484 ' returning l_summed_hours = '
1485 || l_summed_hours,
1486 20
1487 );
1488 hr_utility.set_location ( 'Leaving:'
1489 || l_proc, 30);
1490 end if;
1491 RETURN NVL (l_summed_hours, 0);
1492 END sum_valid_det_hours;
1493
1494 PROCEDURE day_over_24 (
1495 p_batch_id IN NUMBER,
1496 p_person_id IN hxt_timecards.for_person_id%TYPE,
1497 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1498 p_period_id IN hxt_timecards.time_period_id%TYPE,
1499 p_tim_id IN hxt_timecards.id%TYPE,
1500 p_day IN DATE,
1501 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1502 )
1503 IS
1504 l_proc VARCHAR2 (72) ;
1505 l_hol_hours hxt_holiday_days.hours%TYPE;
1506 l_hol_element hxt_holiday_calendars.element_type_id%TYPE;
1507 BEGIN
1508 g_debug :=hr_utility.debug_enabled;
1509 if g_debug then
1510 l_proc := g_package
1511 || 'day_over_24';
1512 hr_utility.set_location ( 'Entering:'
1513 || l_proc, 10);
1514 end if;
1515 IF (sum_valid_det_hours (
1516 p_tim_id=> p_tim_id,
1517 p_day => p_day,
1518 p_assignment_id=> p_assignment_id,
1519 p_hol_cal_id=> p_hol_cal_id
1520 ) > 24
1521 )
1522 THEN
1523 record_error (
1524 p_batch_id=> p_batch_id,
1525 p_tim_id=> p_tim_id,
1526 p_period_id=> p_period_id,
1527 p_error_code=> c_39315_max_hrs_exceeded
1528 );
1529 END IF;
1530
1531 if g_debug then
1532 hr_utility.set_location ( 'Leaving:'
1533 || l_proc, 100);
1534 end if;
1535 END day_over_24;
1536
1537 PROCEDURE holiday_as_reg (
1538 p_batch_id IN NUMBER,
1539 p_person_id IN hxt_timecards.for_person_id%TYPE,
1540 p_period_id IN hxt_timecards.time_period_id%TYPE,
1541 p_tim_id IN hxt_timecards.id%TYPE,
1542 p_day IN DATE,
1543 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1544 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
1545 )
1546 IS
1547 l_proc VARCHAR2 (72) ;
1548 BEGIN
1549 g_debug :=hr_utility.debug_enabled;
1550 if g_debug then
1551 l_proc := g_package
1552 || 'holiday_as_reg';
1553 hr_utility.set_location ( 'Entering:'
1554 || l_proc, 10);
1555 hr_utility.set_location ( ' p_hours_type = '
1556 || p_hours_type, 20);
1557 end if;
1558 IF ( (day_is_holiday (p_day => p_day, p_hol_cal_id => p_hol_cal_id))
1559 AND (NVL (p_hours_type, -1) <>
1560 holiday_element_id (p_hol_cal_id => p_hol_cal_id)
1561 )
1562 )
1563 THEN
1564 record_error (
1565 p_batch_id=> p_batch_id,
1566 p_tim_id=> p_tim_id,
1567 p_period_id=> p_period_id,
1568 p_error_code=> c_39332_day_on_hol_cal
1569 );
1570 END IF;
1571
1572 if g_debug then
1573 hr_utility.set_location ( 'Leaving:'
1574 || l_proc, 100);
1575 end if;
1576 END holiday_as_reg;
1577
1578 PROCEDURE perform_holiday_validations (
1582 p_tim_id IN hxt_timecards.id%TYPE,
1579 p_batch_id IN NUMBER,
1580 p_person_id IN hxt_timecards.for_person_id%TYPE,
1581 p_period_id IN hxt_timecards.time_period_id%TYPE,
1583 p_day IN DATE,
1584 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
1585 p_hol_cal_id OUT NOCOPY hxt_holiday_calendars.id%TYPE
1586 )
1587 AS
1588 l_proc VARCHAR2 (72);
1589
1590 l_hol_cal_id hxt_holiday_calendars.id%TYPE;
1591 BEGIN
1592
1593 if g_debug then
1594 l_proc := g_package
1595 || 'perform_holiday_validations';
1596 hr_utility.set_location ( 'Entering:'
1597 || l_proc, 10);
1598 end if;
1599 l_hol_cal_id := holiday_calendar_id (
1600 p_person_id=> p_person_id,
1601 p_effective_date=> p_day
1602 );
1603 holiday_mismatch (
1604 p_batch_id=> p_batch_id,
1605 p_person_id=> p_person_id,
1606 p_period_id=> p_period_id,
1607 p_tim_id=> p_tim_id,
1608 p_day => p_day,
1609 p_hours_type=> p_hours_type,
1610 p_hol_cal_id=> l_hol_cal_id
1611 );
1612 holiday_valid (
1613 p_batch_id=> p_batch_id,
1614 p_person_id=> p_person_id,
1615 p_period_id=> p_period_id,
1616 p_tim_id=> p_tim_id,
1617 p_day => p_day,
1618 p_hours_type=> p_hours_type,
1619 p_hol_cal_id=> l_hol_cal_id
1620 );
1621 holiday_as_reg (
1622 p_batch_id=> p_batch_id,
1623 p_person_id=> p_person_id,
1624 p_period_id=> p_period_id,
1625 p_tim_id=> p_tim_id,
1626 p_day => p_day,
1627 p_hours_type=> p_hours_type,
1628 p_hol_cal_id=> l_hol_cal_id
1629 );
1630 p_hol_cal_id := l_hol_cal_id;
1631 if g_debug then
1632 hr_utility.set_location ( 'Leaving:'
1633 || l_proc, 100);
1634 end if;
1635 END perform_holiday_validations;
1636
1637 PROCEDURE perform_day_validations (
1638 p_batch_id IN NUMBER,
1639 p_person_id IN hxt_timecards.for_person_id%TYPE,
1640 p_period_id IN hxt_timecards.time_period_id%TYPE,
1641 p_tim_id IN hxt_timecards.id%TYPE
1642 )
1643 AS
1644 l_proc VARCHAR2 (72) ;
1645
1646 CURSOR csr_days (p_tim_id hxt_timecards.id%TYPE)
1647 IS
1648 SELECT DISTINCT hshwx.date_worked, hshwx.element_type_id,
1649 hshwx.assignment_id
1650 FROM hxt_sum_hours_worked_x hshwx
1651 WHERE tim_id = p_tim_id
1652 AND hshwx.effective_end_date = hr_general.end_of_time;
1653
1654 CURSOR c_no_mid_period_change (p_person_id in number)
1655 is
1656 select 'Y'
1657 from per_all_assignments_f p1,
1658 hxt_timecards_x tim
1659 where tim.effective_start_date between p1.effective_start_date and p1.effective_end_date
1660 and tim.effective_end_date between p1.effective_start_date and p1.effective_end_date
1661 and p1.person_id = p_person_id
1662 and p1.primary_flag = 'Y'
1663 and tim.id = p_tim_id;
1664
1665
1666 l_hol_cal_id hxt_holiday_calendars.id%TYPE;
1667 l_no_mid_period_change varchar2(1);
1668
1669 BEGIN
1670
1671 if g_debug then
1672 l_proc := g_package
1673 || 'perform_day_validations';
1674 hr_utility.set_location ( 'Entering:'
1675 || l_proc, 10);
1676 end if;
1677
1678 open c_no_mid_period_change(p_person_id );
1679 fetch c_no_mid_period_change into l_no_mid_period_change;
1680
1681 if c_no_mid_period_change%notfound then
1682 l_no_mid_period_change := 'N';
1683 end if;
1684
1685 close c_no_mid_period_change;
1686
1687 if g_debug then
1688 hr_utility.trace ('l_no_mid_period_change - '||l_no_mid_period_change);
1689 end if;
1690
1691 FOR rec_days IN csr_days (p_tim_id)
1692 LOOP
1693 perform_holiday_validations (
1694 p_batch_id=> p_batch_id,
1695 p_person_id=> p_person_id,
1696 p_period_id=> p_period_id,
1697 p_tim_id=> p_tim_id,
1698 p_day => rec_days.date_worked,
1699 p_hours_type=> rec_days.element_type_id,
1700 p_hol_cal_id=> l_hol_cal_id
1701 );
1702 inactive_emp_tcard (
1703 p_batch_id=> p_batch_id,
1704 p_person_id=> p_person_id,
1705 p_assignment_id=> rec_days.assignment_id,
1706 p_period_id=> p_period_id,
1707 p_tim_id=> p_tim_id,
1708 p_day => rec_days.date_worked
1709 );
1710 day_over_24 (
1711 p_batch_id=> p_batch_id,
1712 p_person_id=> p_person_id,
1713 p_assignment_id=> rec_days.assignment_id,
1714 p_period_id=> p_period_id,
1715 p_tim_id=> p_tim_id,
1716 p_day => rec_days.date_worked,
1717 p_hol_cal_id=> l_hol_cal_id
1718 );
1719
1720 if l_no_mid_period_change = 'N' then
1724 p_person_id=> p_person_id,
1721 excess_pto (
1722 p_batch_id=> p_batch_id,
1723 p_calculation_date=>rec_days.date_worked,
1725 p_period_id=> p_period_id,
1726 p_tim_id=> p_tim_id
1727 );
1728 end if;
1729 END LOOP;
1730
1731 if l_no_mid_period_change = 'Y' then
1732 excess_pto (
1733 p_batch_id=> p_batch_id,
1734 p_calculation_date=> timecard_end_date (p_tim_id => p_tim_id),
1735 p_person_id=> p_person_id,
1736 p_period_id=> p_period_id,
1737 p_tim_id=> p_tim_id
1738 );
1739 end if;
1740
1741 if g_debug then
1742 hr_utility.set_location ( 'Leaving:'
1743 || l_proc, 100);
1744 end if;
1745 END perform_day_validations;
1746
1747 PROCEDURE validate_tc (
1748 p_batch_id IN NUMBER,
1749 p_tim_id IN hxt_timecards.id%TYPE,
1750 p_person_id IN hxt_timecards.for_person_id%TYPE,
1751 p_period_id IN hxt_timecards.time_period_id%TYPE,
1752 p_approv_person_id IN hxt_timecards.approv_person_id%TYPE,
1753 p_auto_gen_flag IN hxt_timecards.auto_gen_flag%TYPE,
1754 p_error_level IN OUT NOCOPY NUMBER
1755 )
1756 AS
1757 l_proc VARCHAR2 (72) ;
1758 BEGIN
1759 g_debug :=hr_utility.debug_enabled;
1760 if g_debug then
1761 l_proc := g_package
1762 || 'validate_tc';
1763 hr_utility.set_location ( 'Entering:'
1764 || l_proc, 10);
1765 end if;
1766
1767
1768 -- Bug 6785744
1769 -- Process started its run on g_start_sysdate.
1770 -- If the current sysdate is not equal to the start date
1771 -- ie. in case the process ran thru a midnight, update the date
1772 -- and reassign g_start_sysdate.
1773
1774 IF g_start_sysdate <> TRUNC(SYSDATE)
1775 THEN
1776 hr_session_utilities.insert_session_row (SYSDATE);
1777 g_start_sysdate := TRUNC(SYSDATE);
1778 END IF;
1779
1780 perform_day_validations (
1781 p_batch_id=> p_batch_id,
1782 p_person_id=> p_person_id,
1783 p_period_id=> p_period_id,
1784 p_tim_id=> p_tim_id
1785 );
1786
1787 -- Bug 6785744
1788 -- No need to remove the session row after each timecard.
1789 -- hr_session_utilities.remove_session_row; /* Bug 6024976 */
1790
1791 /* M.Bhammar - bug 5214727
1792 excess_pto (
1793 p_batch_id=> p_batch_id,
1794 p_person_id=> p_person_id,
1795 p_period_id=> p_period_id,
1796 p_tim_id=> p_tim_id
1797 );
1798 */
1799 tcard_approved (
1800 p_batch_id=> p_batch_id,
1801 p_person_id=> p_person_id,
1802 p_period_id=> p_period_id,
1803 p_tim_id=> p_tim_id,
1804 p_approver_id=> p_approv_person_id,
1805 p_source_flag=> p_auto_gen_flag
1806 );
1807 person_validation (
1808 p_batch_id=> p_batch_id,
1809 p_person_id=> p_person_id,
1810 p_period_id=> p_period_id,
1811 p_tim_id=> p_tim_id
1812 );
1813 p_error_level := error_level;
1814 if g_debug then
1815 hr_utility.set_location ( 'Leaving:'
1816 || l_proc, 100);
1817 end if;
1818 END validate_tc;
1819
1820 PROCEDURE val_batch (
1821 p_batch_id IN NUMBER,
1822 p_time_period_id IN NUMBER,
1823 p_valid_retcode IN OUT NOCOPY NUMBER,
1824 p_merge_flag IN VARCHAR2 DEFAULT '0',
1825 p_merge_batches OUT NOCOPY HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE
1826 )
1827 IS
1828 l_proc VARCHAR2 (72) ;
1829 l_msg_level VARCHAR2 (1) := 'E';
1830 l_valid VARCHAR2 (1) := 'Y';
1831 l_ret NUMBER;
1832 l_msg VARCHAR2 (240);
1833 l_loc VARCHAR2 (70);
1834 l_sql_error VARCHAR2 (80);
1835 l_id NUMBER;
1836 l_cnt BINARY_INTEGER;
1837
1838 CURSOR csr_tcs_in_batch (p_batch_id hxt_timecards_f.batch_id%TYPE)
1839 IS
1840 SELECT tim.id tim_id, tim.for_person_id, tim.time_period_id,
1841 tim.approv_person_id, tim.auto_gen_flag,
1842 tim.approved_timestamp , tim.created_by,
1843 tim.creation_date , tim.last_updated_by,
1844 tim.last_update_date , tim.last_update_login,
1845 tim.payroll_id , tim.status,
1846 tim.effective_start_date , tim.effective_end_date,
1847 tim.object_version_number, tim.rowid
1848 FROM hxt_timecards_x tim
1849 WHERE tim.batch_id = p_batch_id;
1850 BEGIN
1851 g_debug :=hr_utility.debug_enabled;
1852 if g_debug then
1853 l_proc := g_package
1854 || 'val_batch';
1855 hr_utility.set_location ( 'Entering:'
1856 || l_proc, 10);
1857 end if;
1858 reset_error_level;
1862 -- Insert a new row in fnd_sessions before processing this batch.
1859 delete_prev_val_errors (p_batch_id => p_batch_id);
1860
1861 -- Bug 6785744
1863
1864 hr_session_utilities.insert_session_row (SYSDATE);
1865 <<process_all_tcs_in_batch>>
1866 FOR rec_tcs_in_batch IN csr_tcs_in_batch (p_batch_id)
1867 LOOP
1868 set_tc_error_level(p_tc_error_level => 0);
1869
1870 /********Bug: 5037996 **********/
1871
1872 -- IF p_merge_flag = 'Y' THEN /* commented for bug: 5112412 */
1873 delete_prev_val_errors (p_tim_id => rec_tcs_in_batch.tim_id);
1874 -- END IF;
1875
1876 /********Bug: 5037996 **********/
1877
1878 if g_debug then
1879 hr_utility.set_location (
1880 ' process timecard '
1881 || rec_tcs_in_batch.tim_id,
1882 20
1883 );
1884 hr_utility.set_location (
1885 ' Error Level at start = '
1886 || error_level,
1887 30
1888 );
1889 end if;
1890 IF (errors_exist (p_tim_id => rec_tcs_in_batch.tim_id))
1891 THEN
1892 p_valid_retcode := 2;
1893 ELSE
1894 validate_tc (
1895 p_batch_id=> p_batch_id,
1896 p_tim_id=> rec_tcs_in_batch.tim_id,
1897 p_person_id=> rec_tcs_in_batch.for_person_id,
1898 p_period_id=> rec_tcs_in_batch.time_period_id,
1899 p_approv_person_id=> rec_tcs_in_batch.approv_person_id,
1900 p_auto_gen_flag=> rec_tcs_in_batch.auto_gen_flag,
1901 p_error_level=> p_valid_retcode
1902 );
1903 END IF;
1904
1905 -- Bug 6795140
1906
1907 COMMIT;
1908
1909 /********Bug: 5037996 **********/
1910 /*** To record the validated timecards details ***/
1911
1912 IF p_merge_flag = '1' THEN
1913
1914 if g_debug then
1915 hr_utility.trace('Populating merge_batches record'||
1916 ' batch_id: '||p_batch_id||' tc_id '||rec_tcs_in_batch.tim_id);
1917 end if;
1918
1919 l_cnt := NVL(p_merge_batches.LAST,0) +1;
1920 p_merge_batches(l_cnt).batch_id := p_batch_id;
1921 p_merge_batches(l_cnt).tc_id := rec_tcs_in_batch.tim_id;
1922 p_merge_batches(l_cnt).valid_tc_retcode := tc_error_level;
1923 p_merge_batches(l_cnt).tc_rowid := rec_tcs_in_batch.rowid;
1924 p_merge_batches(l_cnt).for_person_id := rec_tcs_in_batch.for_person_id;
1925 p_merge_batches(l_cnt).time_period_id := rec_tcs_in_batch.time_period_id;
1926 p_merge_batches(l_cnt).auto_gen_flag := rec_tcs_in_batch.auto_gen_flag;
1927 p_merge_batches(l_cnt).approv_person_id := rec_tcs_in_batch.approv_person_id;
1928 p_merge_batches(l_cnt).approved_timestamp := rec_tcs_in_batch.approved_timestamp;
1929 p_merge_batches(l_cnt).created_by := rec_tcs_in_batch.created_by;
1930 p_merge_batches(l_cnt).creation_date := rec_tcs_in_batch.creation_date;
1931 p_merge_batches(l_cnt).last_updated_by := rec_tcs_in_batch.last_updated_by;
1932 p_merge_batches(l_cnt).last_update_date := rec_tcs_in_batch.last_update_date;
1933 p_merge_batches(l_cnt).last_update_login := rec_tcs_in_batch.last_update_login;
1934 p_merge_batches(l_cnt).payroll_id := rec_tcs_in_batch.payroll_id;
1935 p_merge_batches(l_cnt).status := rec_tcs_in_batch.status;
1936 p_merge_batches(l_cnt).effective_start_date := rec_tcs_in_batch.effective_start_date;
1937 p_merge_batches(l_cnt).effective_end_date := rec_tcs_in_batch.effective_end_date;
1938 p_merge_batches(l_cnt).object_version_number := rec_tcs_in_batch.object_version_number;
1939
1940 END IF;
1941
1942 /********Bug: 5037996 **********/
1943
1944 END LOOP process_all_tcs_in_batch;
1945
1946 -- Bug 6785744
1947 -- Remove the session row after processing this batch.
1948
1949 hr_session_utilities.remove_session_row;
1950
1951 if g_debug then
1952 hr_utility.set_location ( 'Leaving:'
1953 || l_proc, 100);
1954 end if;
1955 END val_batch;
1956 END hxt_batch_val;