1 PACKAGE BODY hxt_tc_util AS
2 /* $Header: hxttcutl.pkb 115.1 2002/06/10 00:38:38 pkm ship $ */
3
4 FUNCTION get_tc_hrs_total(p_tim_id IN NUMBER) RETURN NUMBER IS
5
6 CURSOR tot_hrs IS
7 select sum(thw.hours)
8 from hxt_sum_hours_worked thw
9 where thw.tim_id = p_tim_id
10 and thw.element_type_id is null;
11
12 CURSOR tot_hrs2 IS
13 select sum(thw.hours)
14 from hxt_sum_hours_worked thw
15 ,pay_element_types_f elt
16 ,hxt_pay_element_types_f_ddf_v eltv
17 where thw.tim_id = p_tim_id
18 and thw.element_type_id is not null
19 and thw.element_type_id = elt.element_type_id
20 and elt.element_type_id = eltv.element_type_id
21 and thw.date_worked between eltv.effective_start_date
22 and eltv.effective_end_date
23 and eltv.hxt_earning_category in ('ABS','REG','OVT');
24
25 current_number_hours number;
26 hrs_type_hrs NUMBER;
27
28 BEGIN
29
30 if p_tim_id is null then
31 return 0;
32 end if;
33
34 OPEN tot_hrs;
35 FETCH tot_hrs into current_number_hours;
36 IF tot_hrs%NOTFOUND then
37 current_number_hours := 0;
38 END IF;
39 CLOSE tot_hrs;
40
41 IF current_number_hours IS NULL THEN
42 current_number_hours := 0;
43 END IF;
44
45 OPEN tot_hrs2;
46 FETCH tot_hrs2 into hrs_type_hrs;
47 IF tot_hrs2%NOTFOUND then
48 hrs_type_hrs := 0;
49 END IF;
50 CLOSE tot_hrs2;
51
52 IF hrs_type_hrs IS NULL THEN
53 hrs_type_hrs := 0;
54 END IF;
55
56 RETURN (current_number_hours + hrs_type_hrs);
57 END; -- get_tc_hrs_total
58
59 PROCEDURE update_approver(p_tim_row_id IN VARCHAR2,
60 p_approv_person_id NUMBER,
61 p_approved_timestamp DATE,
62 p_last_updated_by NUMBER,
63 p_last_update_date DATE,
64 p_last_update_login NUMBER
65 ) is
66
67 begin
68
69 update HXT_TIMECARDS_F
70 set
71 approv_person_id = p_approv_person_id,
72 approved_timestamp = p_approved_timestamp,
73 last_updated_by = p_last_updated_by,
74 last_update_date = p_last_update_date,
75 last_update_login = p_last_update_login
76 where rowid = p_tim_row_id;
77
78 end update_approver;
79
80 END hxt_tc_util;
[Home] [Help]