1 PACKAGE BODY hxt_td_util AS
2 /* $Header: hxttdutl.pkb 120.3.12000000.2 2007/07/18 11:12:08 asrajago noship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 -------------------------------------
5 -- PROCEDURE retro_restrict_edit --
6 -------------------------------------
7 -- The purpose of this procedure is to control the datetrack mode that can
8 -- be used to update a detail record.
9 --
10 -- Once data has been transferred to BEE, we should not allow DT Corrections
11 -- anymore. This correction would overwrite the record in the database and we
12 -- would not know anymore which data was transferred to BEE (and we do need to
13 -- know this as you will see later). So only data that has not been transferred
14 -- yet to BEE can be DT CORRECTED.
15 --
16 -- Data that has been Transferred already can only be DT UPDATED. This will
17 -- retain the record that was transferred and create a new DT record with the
18 -- updated information.
19 --
20 -- The problem with datetrack however is that it does not allow DT UPDATES on
21 -- records that are created on the current session date. E.g. a record that
22 -- was created on 01-JAN-2003 cannot be UPDATED on 01-JAN-2003 because the
23 -- datetrack granularity is on day level so we cannot have a DT record active
24 -- from 01-JAN-2003 00:01 till 01-JAN-2003 12:00 and another one from
25 -- 01-JAN-2003 12:01 till 31-DEC-4712. Because of this DT restriction, we cannot
26 -- allow ANY updates on detail records if they have been transferred already
27 -- and were updated today. The fact that it has already been transferred means
28 -- we need to do a DT UPDATE, but we can't because it was already update today.
29 -- This procedure will raise an error for such a situation.
30 --
31 -- The reason we need to keep records that already have been transferred to BEE
32 -- intact is because any update after that is send as a delta between the old
33 -- (already send record) and the update (new DT) record. A correction would wipe
34 -- out the old record and we would not be able to to a delta anymore.
35
36 -- Added for Bug 6067007
37 -- There was one corner scenario, when the timecard is transferred to BEE
38 -- and the next day a change is made to it. This time, the code allows only
39 -- an update. But once this is transferred to PUI, the status of the detail
40 -- records changes to R. ( for retro ). If you try to update again, the time
41 -- card will let you CORRECT the timecard ( even delete a day's entry and add
42 -- another entry.) Now when this data moves to BEE, it would create overpayment
43 -- because the day is already having an entry for a given attribute, and a newer
44 -- entry is brought in with retro ( this time a different attribute, so the
45 -- existing entry wont be reversed). This can be avoided only by switching to
46 -- UPDATE only allowed while doing a retro change. So after this change, any
47 -- timecard which has history of transfer to BEE can be updated only, no delete
48 -- is allowed.
49
50 PROCEDURE retro_restrict_edit (
51 p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE,
52 p_session_date IN DATE,
53 o_dt_update_mod OUT NOCOPY VARCHAR2,
54 o_error_message OUT NOCOPY VARCHAR2,
55 o_return_code OUT NOCOPY NUMBER,
56 p_parent_id IN hxt_det_hours_worked_f.parent_id%TYPE
57 )
58 IS
59 CURSOR csr_not_transferred (
60 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
61 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
62 )
63 IS
64 SELECT 1
65 FROM hxt_det_hours_worked hdhw
66 WHERE hdhw.tim_id = v_tim_id
67 AND hdhw.pay_status = 'P' -- Bug 6067007
68 AND hdhw.pa_status = 'P' -- Bug 6067007
69 AND NOT EXISTS ( SELECT 1
70 FROM hxt_det_hours_worked_f hdhw2
71 WHERE hdhw.tim_id = hdhw2.tim_id
72 AND hdhw2.effective_start_date >
73 hdhw.effective_start_date);
74
75
76 -- Bug 6067007
77 CURSOR csr_transferred_retro (
78 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
79 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
80 )
81 IS
82 SELECT 1
83 FROM hxt_det_hours_worked_f hdhw
84 WHERE hdhw.tim_id = v_tim_id
85 AND ( hdhw.pay_status = 'R'
86 OR hdhw.pa_status = 'R'
87 );
88 -- Bug 6067007
89
90
91 CURSOR csr_transferred (
92 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
93 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE
94 )
95 IS
96 SELECT 1
97 FROM hxt_det_hours_worked_f hdhw
98 WHERE hdhw.tim_id = v_tim_id
99 AND ( hdhw.pay_status = 'C'
100 OR hdhw.pa_status = 'C'
101 );
102
103 CURSOR csr_changed_today (
104 v_tim_id hxt_det_hours_worked_f.tim_id%TYPE,
105 v_parent_id hxt_det_hours_worked_f.parent_id%TYPE,
106 v_session_dt DATE
107 )
108 IS
109 SELECT 1
110 FROM hxt_det_hours_worked_f hdhw
111 WHERE hdhw.tim_id = v_tim_id
112 AND trunc(hdhw.effective_start_date)
113 = trunc(v_session_dt);
114
115
116 l_transferred csr_transferred%ROWTYPE := NULL;
117 l_transfered_retro csr_transferred_retro%ROWTYPE := NULL; -- Bug 6067007
118 l_not_transferred csr_not_transferred%ROWTYPE := NULL;
119 l_changed_today csr_changed_today%ROWTYPE := NULL;
120 l_proc VARCHAR2 (30) ;
121
122 FUNCTION details (p_tim_id IN hxt_det_hours_worked_f.tim_id%TYPE)
123 RETURN BOOLEAN
124 IS
125 CURSOR csr_debug (v_tim_id hxt_det_hours_worked_f.tim_id%TYPE)
126 IS
127 SELECT hdhw.id, hdhw.parent_id, hdhw.date_worked, hdhw.pay_status,
128 hdhw.effective_start_date
129 FROM hxt_det_hours_worked hdhw
130 WHERE hdhw.tim_id = v_tim_id;
131
132 l_details BOOLEAN := FALSE;
133 BEGIN
134
135 FOR rec_debug IN csr_debug (p_tim_id)
136 LOOP
137 l_details := TRUE;
138 if g_debug then
139
140 hr_utility.TRACE (
141 LPAD (rec_debug.id, 10)
142 || ' '
143 || LPAD (rec_debug.parent_id, 10)
144 || ' '
145 || LPAD (rec_debug.date_worked, 10)
146 || ' '
147 || LPAD (rec_debug.pay_status, 10)
148 || ' '
149 || LPAD (rec_debug.effective_start_date, 10)
150 );
151 end if;
152 END LOOP;
153
154 RETURN l_details;
155 END details;
156 BEGIN
157 g_debug :=hr_utility.debug_enabled;
158 o_return_code := 0; -- indicates all is OK
159 if g_debug then
160 l_proc := 'retro_restrict_edit';
161 hr_utility.set_location ( 'Entering '
162 || l_proc, 10);
163 hr_utility.set_location ('Parameters In: ', 20);
164 hr_utility.set_location ( ' p_tim_id = '
165 || p_tim_id, 30);
166 hr_utility.set_location (
167 ' p_session_date = '
168 || p_session_date,
169 40
170 );
171 hr_utility.set_location ( ' p_parent_id = '
172 || p_parent_id, 50);
173 end if;
174
175 IF (details (p_tim_id))
176 THEN
177 -- First we check if the records have not been transferred yet
178 OPEN csr_not_transferred (p_tim_id, p_parent_id);
179 FETCH csr_not_transferred INTO l_not_transferred;
180
181 IF csr_not_transferred%FOUND
182 THEN -- we can do a correction
183 if g_debug then
184 hr_utility.set_location ('Do CORRECTION', 60);
185 end if;
186
187 o_dt_update_mod := 'CORRECTION';
188 ELSE -- look for records that have been transferred and undergoing RETRO
189
190
191 -- Bug 6067007 - Addition
192 OPEN csr_transferred_retro (p_tim_id, p_parent_id);
193 FETCH csr_transferred_retro INTO l_transfered_retro;
194
195 IF csr_transferred_retro%FOUND
196 THEN -- TC undergoing RETRO now, only UPDATE allowed.
197
198 if g_debug then
199 hr_utility.set_location ('Do UPDATE', 70);
200 end if;
201 o_dt_update_mod := 'UPDATE';
202 -- Bug 6067007
203
204
205 ELSE -- look for records that have been transferred
206
207 OPEN csr_transferred (p_tim_id, p_parent_id);
208 FETCH csr_transferred INTO l_transferred;
209
210 IF csr_transferred%FOUND
211 THEN -- Was it already updated today?
212 OPEN csr_changed_today (p_tim_id, p_parent_id, p_session_date);
213 FETCH csr_changed_today INTO l_changed_today;
214
215 IF csr_changed_today%FOUND
216 THEN -- DT will not allow an update so error
217 o_return_code := 1; -- this means that an error should be raised
218 o_dt_update_mod := NULL;
219 ELSE -- We can allow an DT UPDATE because that will keep the history
220 if g_debug then
221 hr_utility.set_location ('Do UPDATE', 70);
222 end if;
223 o_dt_update_mod := 'UPDATE';
224 END IF; -- IF csr_changed_today%FOUND
225
226 CLOSE csr_changed_today;
227 ELSE -- We can allow an DT UPDATE because that will keep the history
228 if g_debug then
229 hr_utility.set_location ('Do UPDATE', 80);
230 end if;
231 o_dt_update_mod := 'UPDATE';
232 END IF; -- IF csr_transferred%FOUND
233 CLOSE csr_transferred;
234
235 END IF; -- IF csr_transferred_retro%FOUND
236 CLOSE csr_transferred_retro;
237
238 END IF; -- IF csr_not_transferred%FOUND
239 CLOSE csr_not_transferred;
240
241 ELSE -- if details
242 if g_debug then
243 hr_utility.set_location ('Do CORRECTION', 90);
244 end if;
245 o_dt_update_mod := 'CORRECTION';
246 END IF;
247
248 RETURN;
249 EXCEPTION
250 WHEN OTHERS
251 THEN
252 o_return_code := 4;
253 o_error_message := 'Error('
254 || SQLERRM
255 || ') occured in Retro_restrict_edit procedure';
256 RETURN;
257 END retro_restrict_edit;
258
259 FUNCTION get_weekly_total (
260 a_location IN VARCHAR2,
261 a_date_worked IN DATE,
262 a_start_day_of_week IN VARCHAR2,
263 a_tim_id IN NUMBER,
264 a_base_element_type_id IN NUMBER,
265 a_ep_id IN NUMBER,
266
267 -- Added the following parameter for
268 -- OTLR Recurring Period Preference Support.
269 a_for_person_id IN NUMBER
270 )
271 RETURN NUMBER
272 IS
273 l_weekly_total NUMBER;
274
275
276 -- MHANDA changed this cursor to get total weekly hours between
277 -- start_day_of_week and (g_date_worked - 1) to support 3tier weekly rules for
278 -- SPECIAL Earning Policy.
279
280 CURSOR weekly_total
281 IS
282 SELECT NVL (SUM (hrw.hours), 0)
283 FROM hxt_det_hours_worked hrw, --C421
284 hxt_timecards tim,
285 hxt_earn_groups erg,
286 hxt_earn_group_types egt,
287 hxt_earning_policies erp
288 -- WHERE tim.id = a_tim_id
289 -- AND hrw.tim_id = a_tim_id
290
291 -- Changed the above where clause as follows for supporting the
292 -- OTLR Recurring Period Preference.
293 WHERE tim.for_person_id = a_for_person_id
294 AND hrw.tim_id = tim.id
295
296 -- This has been changed back to get total weekly hours between
297 -- start_day_of_week and (g_date_worked) as it was not calculating the totals
298 -- correctly when entering hours on more than one summary row for the same day
299 -- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
300 -- and (a_date_worked - 1)
301
302 AND hrw.date_worked BETWEEN NEXT_DAY (
303 a_date_worked
304 - 7,
305 a_start_day_of_week
306 )
307 AND a_date_worked
308
309 -- only include earnings to be counted toward
310 -- hours to be worked before being eligible for overtime.
311 AND erp.id = a_ep_id
312 AND egt.id = erp.egt_id
313 AND erg.egt_id = egt.id
314 AND erg.element_type_id = hrw.element_type_id
315 AND hrw.date_worked BETWEEN erp.effective_start_date
316 AND erp.effective_end_date;
317
318 l_proc VARCHAR2 (200) ;
319 BEGIN
320 g_debug :=hr_utility.debug_enabled;
321 if g_debug then
322 l_proc := 'hxt_td_util.GET_WEEKLY_TOTAL';
323 hr_utility.set_location (l_proc, 10);
324 hr_utility.TRACE (
325 'a_date_worked :'
326 || TO_CHAR (a_date_worked, 'dd-mon-yyyy hh24:mi:ss')
327 );
328 hr_utility.TRACE ( 'a_start_day_of_week :'
329 || a_start_day_of_week);
330 hr_utility.TRACE ( 'a_tim_id :'
331 || a_tim_id);
332 hr_utility.TRACE (
333 'a_base_element_type_id :'
334 || a_base_element_type_id
335 );
336 hr_utility.TRACE ( 'a_ep_id :'
337 || a_ep_id);
338 hr_utility.TRACE ( 'a_for_person_id :'
339 || a_for_person_id);
340 end if;
341 OPEN weekly_total;
342 FETCH weekly_total INTO l_weekly_total;
343 if g_debug then
344 hr_utility.TRACE ( 'l_weekly_total :'
345 || l_weekly_total);
346 end if;
347 CLOSE weekly_total;
348 if g_debug then
349 hr_utility.set_location (l_proc, 20);
350 end if;
351 RETURN l_weekly_total;
352 END;
353
354 FUNCTION get_weekly_total_prev_days (
355 a_location IN VARCHAR2,
356 a_date_worked IN DATE,
357 a_start_day_of_week IN VARCHAR2,
358 a_tim_id IN NUMBER,
359 a_base_element_type_id IN NUMBER,
360 a_ep_id IN NUMBER,
361
362 -- Added the following parameter for
363 -- OTLR Recurring Period Preference Support.
364 a_for_person_id IN NUMBER
365 )
366 RETURN NUMBER
367 IS
368 l_weekly_total NUMBER;
369
370
371 -- MHANDA changed this cursor to get total weekly hours between
372 -- start_day_of_week and (g_date_worked - 1) to support 3tier weekly rules for
373 -- SPECIAL Earning Policy.
374
375 CURSOR weekly_total
376 IS
377 SELECT NVL (SUM (hrw.hours), 0)
378 FROM hxt_det_hours_worked hrw, --C421
379 hxt_timecards tim,
380 hxt_earn_groups erg,
381 hxt_earn_group_types egt,
385
382 hxt_earning_policies erp
383 -- WHERE tim.id = a_tim_id
384 -- AND hrw.tim_id = a_tim_id
386 -- Changed the above where clause as follows for supporting the
387 -- OTLR Recurring Period Preference.
388 WHERE tim.for_person_id = a_for_person_id
389 AND hrw.tim_id = tim.id
390
391 -- This has been changed back to get total weekly hours between
392 -- start_day_of_week and (g_date_worked) as it was not calculating the totals
393 -- correctly when entering hours on more than one summary row for the same day
394 -- AND hrw.date_worked between NEXT_DAY(a_date_worked-7,a_start_day_of_week)
395 -- and (a_date_worked - 1)
396
397 AND hrw.date_worked BETWEEN NEXT_DAY (
398 a_date_worked
399 - 7,
400 a_start_day_of_week
401 )
402 AND a_date_worked
403 - 1
404
405 -- only include earnings to be counted toward
406 -- hours to be worked before being eligible for overtime.
407 AND erp.id = a_ep_id
408 AND egt.id = erp.egt_id
409 AND erg.egt_id = egt.id
410 AND erg.element_type_id = hrw.element_type_id
411 AND hrw.date_worked BETWEEN erp.effective_start_date
412 AND erp.effective_end_date;
413
414 l_proc VARCHAR2 (200) ;
415 BEGIN
416 g_debug :=hr_utility.debug_enabled;
417 if g_debug then
418 l_proc := 'hxt_td_util.GET_WEEKLY_TOTAL';
419 hr_utility.set_location (l_proc, 10);
420 hr_utility.TRACE (
421 'a_date_worked :'
422 || TO_CHAR (a_date_worked, 'dd-mon-yyyy hh24:mi:ss')
423 );
424 hr_utility.TRACE ( 'a_start_day_of_week :'
425 || a_start_day_of_week);
426 hr_utility.TRACE ( 'a_tim_id :'
427 || a_tim_id);
428 hr_utility.TRACE (
429 'a_base_element_type_id :'
430 || a_base_element_type_id
431 );
432 hr_utility.TRACE ( 'a_ep_id :'
433 || a_ep_id);
434 hr_utility.TRACE ( 'a_for_person_id :'
435 || a_for_person_id);
436 end if;
437 OPEN weekly_total;
438 FETCH weekly_total INTO l_weekly_total;
439 if g_debug then
440 hr_utility.TRACE ( 'l_weekly_total :'
441 || l_weekly_total);
442 end if;
443 CLOSE weekly_total;
444 if g_debug then
445 hr_utility.set_location (l_proc, 20);
446 end if;
447 RETURN l_weekly_total;
448 END get_weekly_total_prev_days;
449
450
451 --
452 ----------------- Include For ot Cap ---------------------------------
453 FUNCTION include_for_ot_cap (
454 a_earn_group IN NUMBER,
455 a_element_type IN NUMBER,
456 a_base_element IN NUMBER,
457 a_date_worked IN DATE
458 )
459 RETURN BOOLEAN
460 IS
461
462 -- returns true if a particular earning is counted toward the weekly overtime cap
463 returned_element NUMBER;
464 BEGIN
465 IF a_element_type = a_base_element
466 THEN
467 RETURN TRUE;
468 END IF;
469
470 SELECT 1
471 INTO returned_element
472 FROM hxt_earn_group_types egt
473 WHERE egt.fcl_eg_type = 'INCLUDE'
474 AND a_date_worked BETWEEN egt.effective_start_date
475 AND egt.effective_end_date
476 AND egt.id = a_earn_group
477 AND EXISTS ( SELECT 'x'
478 FROM hxt_earn_groups egr
479 WHERE egr.egt_id = egt.id
480 AND egr.element_type_id = a_element_type);
481
482 IF returned_element = 1
483 THEN
484 RETURN TRUE;
485 ELSE
486 RETURN FALSE;
487 END IF;
488 EXCEPTION
489 WHEN NO_DATA_FOUND
490 THEN
491 RETURN FALSE;
492 END;
493
494
495 --
496 ------------------ Load Changed Status -------------------------------
497 FUNCTION load_changed_status (a_hrw_id IN NUMBER)
498 RETURN VARCHAR2
499 IS
500 v_status CHAR (1) := ''; --HXT11i1 Return null instead of space
501
502 CURSOR changed_cur
503 IS
504 SELECT 'C'
505 FROM hxt_sum_hours_worked hrwp
506 WHERE hrwp.id = a_hrw_id
507 AND ( creation_date <> last_update_date
508 OR EXISTS ( SELECT '1'
509 FROM hxt_sum_hours_worked_f hrw
510 WHERE hrw.id = a_hrw_id
511 AND hrw.effective_start_date <>
512 hrwp.effective_start_date)
513 );
514 BEGIN
515 OPEN changed_cur;
516 FETCH changed_cur INTO v_status;
517 CLOSE changed_cur;
518 RETURN v_status;
519 EXCEPTION
523 END;
520 WHEN OTHERS
521 THEN
522 RETURN 'X';
524
525
526 --
527 ------------------- Load Error Status -------------------------------
528 --BEGIN HXT11i1
529 /***************************************************************************
530 ** Modify Load_Error_Status to select the Value stored in ERR_TYPE from **
531 ** HXT_ERRORS rather then selecting a hard coded 'E' if the record exists. **
532 ***************************************************************************/
533 FUNCTION load_error_status (a_hrw_id IN NUMBER)
534 RETURN VARCHAR2
535 IS
536 returned_status VARCHAR2 (03) := '';
537
538 CURSOR error_status
539 IS
540 SELECT err_type
541 FROM hxt_errors err
542 WHERE err.hrw_id = a_hrw_id;
543 BEGIN
544 OPEN error_status;
545 FETCH error_status INTO returned_status;
546 CLOSE error_status;
547 RETURN returned_status;
548 END load_error_status;
549
550
551 ---------------------- Determine Fixed Premium ------------------------
552 /* Begin OHM104 RJT; put into TA35 May27,97 RTF */
553 /* ******************************************************************
554 * FUNCTION determine_fixed_premium *
555 * *
556 * Purpose *
557 * To determine if a element is fixed premium and the of the *
558 * fixed premium. The amount of the fixed premium is place in *
559 * amount field on the time card. *
560 * rate *
561 * *
562 * Returns *
563 * p_return_code - 0 - no errors 2 - errors occured. *
564 * p_amount - Null if the element is not a fixed premium *
565 * - The amount of the fixed premium *
566 * *
567 * Arguments *
568 * p_element_type_id - The hours type. *
569 * *
570 *****************************************************************/
571 FUNCTION determine_fixed_premium (
572 p_tim_id NUMBER,
573 p_id NUMBER,
574 p_hours NUMBER,
575 p_element_type_id NUMBER,
576 p_effective_start_date DATE,
577 p_effective_end_date DATE,
578 p_return_code OUT NOCOPY NUMBER
579 )
580 RETURN NUMBER
581 IS
582 CURSOR fixed_amount_cur (c_element_type_id NUMBER)
583 IS
584 SELECT petv.hxt_premium_amount
585 FROM hxt_pay_element_types_f_ddf_v petv
586 WHERE petv.hxt_earning_category = 'OTH'
587 AND petv.hxt_premium_type = 'FIXED'
588 AND petv.element_type_id = c_element_type_id
589 AND petv.effective_start_date <= p_effective_start_date
590 AND petv.effective_end_date >= p_effective_end_date;
591
592 v_amount hxt_det_hours_worked_f.amount%TYPE;
593 BEGIN
594
595 -- Determine if the element is a fixed amount and what the fixed amount is.
596
597 OPEN fixed_amount_cur (p_element_type_id);
598 FETCH fixed_amount_cur INTO v_amount;
599
600 IF fixed_amount_cur%NOTFOUND
601 THEN
602 CLOSE fixed_amount_cur;
603 RETURN NULL;
604 END IF;
605
606 CLOSE fixed_amount_cur;
607
608
609 -- Ensure that the hours field is null.
610
611 IF p_hours > 0
612 THEN
613 p_return_code := 2;
614 fnd_message.set_name ('HXT', 'HXT_39467_NO_HRS_4_FIX_PREM');
615 hxt_util.gen_error (
616 p_tim_id,
617 p_id,
618 NULL,
619 NULL,
620 '',
621 'tdutilbd.determine_fixed_premium ',
622 NULL,
623 p_effective_start_date,
624 p_effective_end_date,
625 'ERR'
626 );
627 RETURN NULL;
628 END IF;
629
630 p_return_code := 0;
631 RETURN v_amount;
632 EXCEPTION
633 WHEN OTHERS
634 THEN
635 p_return_code := 2;
636 fnd_message.set_name ('HXT', 'HXT_39468_OR_ERR_SEL_PREM_AMT');
637 hxt_util.gen_error (
638 p_tim_id,
639 p_id,
640 NULL,
641 NULL,
642 '',
643 'tdutilbd.fixed_amount. ',
644 SQLERRM,
645 p_effective_start_date,
646 p_effective_end_date,
647 'ERR'
648 );
649 RETURN NULL;
650 END determine_fixed_premium;
651
652
653 --
654 --------------------- Get Hourly Rate ------------------------
655 -- begin CERTPAY
656 FUNCTION get_hourly_rate (
660 p_hourly_rate OUT NOCOPY NUMBER
657 p_eff_date DATE,
658 p_ptp_id NUMBER,
659 p_assignment_id NUMBER,
661 )
662 RETURN NUMBER
663 IS
664 CURSOR sal_cur
665 IS
666 SELECT pro.proposed_salary, ppb.pay_basis
667 FROM per_pay_proposals pro,
668 per_pay_bases ppb,
669 per_assignments_f asg
670 WHERE pro.assignment_id = p_assignment_id
671 AND ppb.pay_basis_id = asg.pay_basis_id
672 AND asg.assignment_id = pro.assignment_id
673 AND p_eff_date BETWEEN asg.effective_start_date
674 AND asg.effective_end_date
675 AND pro.approved = 'Y'
676 AND pro.change_date = (SELECT MAX (pro2.change_date)
677 FROM per_pay_proposals pro2
678 WHERE pro2.assignment_id =
679 p_assignment_id
680 AND pro2.approved = 'Y'
681 AND p_eff_date >= pro2.change_date);
682
683 CURSOR num_periods_cur
684 IS
685 SELECT ptpt.number_per_fiscal_year
686 FROM per_time_periods ptp, per_time_period_types ptpt
687 WHERE p_ptp_id = ptp.time_period_id
688 AND ptp.period_type = ptpt.period_type;
689
690 v_proposed_salary per_pay_proposals_v.proposed_salary%TYPE;
691 v_pay_basis per_pay_proposals_v.pay_basis%TYPE;
692 l_hours_per_year NUMBER (22, 5)
693 := TO_NUMBER (fnd_profile.VALUE ('HXT_HOURS_PER_YEAR'));
694 v_annual_pay_periods per_time_period_types.number_per_fiscal_year%TYPE;
695 BEGIN
696 OPEN sal_cur;
697 FETCH sal_cur INTO v_proposed_salary, v_pay_basis;
698
699 IF sal_cur%NOTFOUND
700 THEN
701 RETURN 1; -- 'No salary information for the employee'
702 END IF;
703
704 OPEN num_periods_cur;
705 FETCH num_periods_cur INTO v_annual_pay_periods;
706
707 IF num_periods_cur%NOTFOUND
708 THEN
709 RETURN 2; -- 'Unable to determine number of pay periods.'
710 END IF;
711
712
713 -- Calculate an hourly rate for the salary basis
714 IF v_pay_basis = 'ANNUAL'
715 THEN
716 p_hourly_rate := v_proposed_salary / l_hours_per_year;
717 ELSIF v_pay_basis = 'MONTHLY'
718 THEN
719 p_hourly_rate := (v_proposed_salary * 12)
720 / l_hours_per_year;
721 ELSIF v_pay_basis = 'PERIOD'
722 THEN
723 p_hourly_rate :=
724 (v_proposed_salary * v_annual_pay_periods)
725 / l_hours_per_year;
726 ELSE -- 'HOURLY'
727 p_hourly_rate := v_proposed_salary;
728 END IF;
729
730 RETURN 0;
731 END;
732
733
734 -- end CERTPAY
735 --BEGIN HXT11i1
736 /*****************************************************************
737 * FUNCTION Load_Tim_Error_Status -- ER177 SDM 09-03-98 *
738 * *
739 * Purpose *
740 * Select and return the value ERR_TYPE from the Table *
741 * HXT_ERRORS where the tim_id passed in = HXT_ERRORS.TIM_ID. *
742 * *
743 * Returns *
744 * returned_status - Value of ERR_TYPE where tim_id passed in *
745 * equals HXT_ERRORS.TIM_ID.0 *
746 * *
747 * Arguments *
748 * p_tim_id - Time Card ID *
749 * *
750 *****************************************************************/
751 FUNCTION load_tim_error_status (p_tim_id IN NUMBER)
752 RETURN VARCHAR2
753 IS
754 returned_status VARCHAR2 (3) := '';
755
756 CURSOR error_status
757 IS
758 SELECT err_type
759 FROM hxt_errors err
760 WHERE err.tim_id = p_tim_id
761 ORDER BY err_type; -- So that 'ERR' has precedence over 'WRN'
762 BEGIN
763 OPEN error_status;
764 FETCH error_status INTO returned_status;
765 CLOSE error_status;
766 RETURN returned_status;
767 END load_tim_error_status;
768
769
770 /*****************************************************************
771 * FUNCTION Load_HRW_Error_Change_Status -- ER177 SDM 09-03-98 *
772 * *
773 * Purpose *
774 * Retrieve Error Type and Change Type for HRW row and retrun *
775 * Concatenated value. *
776 * *
777 * Returns *
778 * returned_status - Value of Concatenated value of Error type *
782 * p_hrw_id - ID of summary Row *
779 * and changed type *
780 * *
781 * Arguments *
783 * p_tim_status - Error type of the time card record *
784 * *
785 *****************************************************************/
786 FUNCTION load_hrw_error_change_status (
787 p_hrw_id IN NUMBER,
788 p_tim_status IN VARCHAR2
789 )
790 RETURN VARCHAR2
791 IS
792 hrw_status VARCHAR2 (01);
793 change_status VARCHAR2 (01);
794 BEGIN
795
796 -- Bug Fix : 2538832
797 -- Added return statement to ensure that the function returns
798 -- only the error status, if timecard status is Error.
799 -- i.e. ignore Change status.
800
801 IF p_tim_status = 'E'
802 THEN
803 hrw_status := SUBSTR (load_error_status (p_hrw_id), 1, 1);
804 RETURN hrw_status;
805 END IF;
806
807 change_status := load_changed_status (p_hrw_id);
808 RETURN hrw_status
809 || change_status;
810 END load_hrw_error_change_status;
811
812
813 /*****************************************************************
814 * FUNCTION Get_Sum_Hours_Worked -- ER183 SDM 09-03-98 *
815 * *
816 * Purpose *
817 * Get the sum of the hours from hxt_sum_hours_worked based on *
818 * parameters passed in. Return this value to the calling *
819 * module *
820 * *
821 * Returns *
822 * total_hrs - Total hrs employee worked based on the date *
823 * passed in *
824 * *
825 * Arguments *
826 * p_tim_id - ID of Timecard Row *
827 * p_hrw_id - ID of summary Row *
828 * p_date_worked - Date the hours are to be summed upon *
829 * *
830 *****************************************************************/
831 FUNCTION get_sum_hours_worked (
832 p_tim_id IN NUMBER, -- p_hrw_group_id IN NUMBER,
833 p_date_worked IN DATE
834 )
835 RETURN NUMBER
836 IS
837 CURSOR edit24_cur
838 IS
839 SELECT SUM (hours)
840 FROM hxt_sum_hours_worked
841 WHERE tim_id = p_tim_id -- AND GROUP_ID <> NVL(p_hrw_group_id, 0)
842 AND date_worked = p_date_worked;
843
844 l_total_hours NUMBER;
845 BEGIN
846 OPEN edit24_cur;
847 FETCH edit24_cur INTO l_total_hours;
848
849 IF edit24_cur%NOTFOUND
850 THEN
851 l_total_hours := 0;
852 END IF;
853
854 CLOSE edit24_cur;
855 RETURN l_total_hours;
856 END get_sum_hours_worked;
857
858
859 -----------------------------------------------------------------------------
860 -- This procedure cannot be used for OVT calculation. That's because
861 -- it doesn't check the Include for OT group in the case of Absences.
862 -- See package TIMDETBD.PLS get_contig_hours for an example of how
863 -- to do that.
864 -- This procedure is intended to be used for shift differential calculations.
865 -----------------------------------------------------------------------------
866 PROCEDURE get_contig_hrs_and_start (
867 p_date_worked IN DATE,
868 p_person_id IN NUMBER,
869 p_current_time_in IN DATE,
870 p_egt_id IN NUMBER,
871 p_tim_id IN NUMBER,
872 o_first_time_in OUT NOCOPY DATE,
873 o_contig_hrs OUT NOCOPY NUMBER
874 )
875 IS
876
877 -- We do not want to get records where the time_out is the exact time and day
878 -- as the time_in of a previous record. This is an error condition that should
879 -- never happen, but did due to an autogen error. This code prevents endless
880 -- looping. SIR282
881 CURSOR contig_hrs (
882 c_date_worked DATE,
883 c_current_time_in DATE,
884 c_tim_id NUMBER
885 )
886 IS
887 SELECT hrw.time_in, hrw.time_out, hrw.hours, hrw.element_type_id,
888 hrw.date_worked, eltv.hxt_earning_category
889 FROM hxt_det_hours_worked hrw,
890 hxt_pay_element_types_f_ddf_v eltv,
891 pay_element_types_f elt
892 WHERE elt.element_type_id = hrw.element_type_id
893 AND eltv.hxt_earning_category IN ('REG', 'OVT', 'ABS')
894 AND hrw.date_worked BETWEEN elt.effective_start_date
895 AND elt.effective_end_date
896 AND eltv.element_type_id = elt.element_type_id
897 AND hrw.date_worked BETWEEN eltv.effective_start_date
898 AND eltv.effective_end_date
899 AND hrw.tim_id = c_tim_id
900 AND hrw.time_out = c_current_time_in
901 AND hrw.time_in <> hrw.time_out; --FIX endless loop PWM 01/28/99 SIR282
902
903 l_rec contig_hrs%ROWTYPE;
904 l_contig_hrs NUMBER (7, 3) := 0;
905 l_continue BOOLEAN := TRUE;
906 l_current_time_in DATE := p_current_time_in;
907 l_date_worked DATE := p_date_worked;
908 loop_counter NUMBER := 0; -- counts loop iterations for checking SIR282
909 error_code NUMBER := 0; -- PWM Fix for endless loop 01/28/99 SIR282
910 BEGIN
911 hxt_util.DEBUG (
912 'Top of get_contig_hrs. time_in = '
913 || TO_CHAR (l_current_time_in, 'HH24:MI')
914 );
915
916 WHILE l_continue = TRUE
917 LOOP
918 OPEN contig_hrs (l_date_worked, l_current_time_in, p_tim_id);
919 FETCH contig_hrs INTO l_rec;
920
921 IF contig_hrs%FOUND
922 THEN
923 hxt_util.DEBUG (
924 ' previous summary found. time_in = '
925 || TO_CHAR (l_rec.time_in, 'HH24:MI')
926 );
927 l_contig_hrs := l_contig_hrs
928 + l_rec.hours;
929 l_current_time_in := l_rec.time_in;
930 l_date_worked := l_rec.date_worked;
931 CLOSE contig_hrs;
932 ELSE
933 CLOSE contig_hrs;
934 l_continue := FALSE;
935 END IF;
936
937 loop_counter := loop_counter
938 + 1; --PWM 01/28/99 Fix for endless loop SIR282
939
940 IF loop_counter > 50
941 THEN
942 l_continue := FALSE;
943 fnd_message.set_name ('HXT', 'HXT_39506_LOOP_COUNT_EXCEEDED');
944 hxt_util.gen_error (
945 p_tim_id,
946 NULL,
947 NULL,
948 '',
949 'hxttdutl.get_contig_hrs_and_start ',
950 SQLERRM,
951 p_date_worked,
952 p_date_worked,
953 'ERR'
954 );
955 END IF;
956 END LOOP;
957
958 hxt_util.DEBUG ('');
959 hxt_util.DEBUG ( 'Done. hours = '
960 || TO_CHAR (l_contig_hrs));
961 o_contig_hrs := l_contig_hrs;
962 o_first_time_in := l_current_time_in;
963 END;
964 --END HXT11i1
965 END;
[Home] [Help]