1 PACKAGE BODY PAY_CALC_HOURS_WORKED as
2 /* $Header: paycalchrswork.pkb 120.0 2005/05/29 11:18 appldev noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1994 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : PAY_CALC_HOURS_WORKED
11 Filename : paycalchrswork.pkh
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 28-APR-2005 sodhingr 115.0 4338404 Package to deliver new
17 functioanlity to calculate
18 hours worked
19 09-MAY-2005 sodhingr 115.1 changed the function calculate_hours_worked
20 to get the legislation code if it's not passed
21 as a parameter. Legislation code is not required
22 parameter for international localization
23
24 */
25
26 g_legislation_code VARCHAR2(10);
27
28 FUNCTION standard_hours_worked(
29 p_std_hrs in NUMBER,
30 p_range_start in DATE,
31 p_range_end in DATE,
32 p_std_freq in VARCHAR2) RETURN NUMBER IS
33
34 c_wkdays_per_week NUMBER(5,2) ;
35 c_wkdays_per_month NUMBER(5,2) ;
36 c_wkdays_per_year NUMBER(5,2) ;
37
38 /* 353434, 368242 : Fixed number width for total hours */
39 v_total_hours NUMBER(15,7) ;
40 v_wrkday_hours NUMBER(15,7) ; -- std hrs/wk divided by 5 workdays/wk
41 v_curr_date DATE;
42 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
43 v_day_no NUMBER;
44
45 BEGIN -- standard_hours_worked
46
47 /* Init */
48 c_wkdays_per_week := 5;
49 c_wkdays_per_month := 20;
50 c_wkdays_per_year := 250;
51 v_total_hours := 0;
52 v_wrkday_hours :=0;
53 v_curr_date := NULL;
54 v_curr_day :=NULL;
55
56 -- Check for valid range
57 hr_utility.trace('Entered standard_hours_worked');
58
59 IF p_range_start > p_range_end THEN
60 hr_utility.trace('p_range_start greater than p_range_end');
61 RETURN v_total_hours;
62 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
63 -- hr_utility.raise_error;
64 END IF;
65 --
66
67 IF UPPER(p_std_freq) = 'WEEK' THEN
68 hr_utility.trace('p_std_freq = WEEK ');
69
70 v_wrkday_hours := p_std_hrs / c_wkdays_per_week;
71
72 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
73 hr_utility.trace('c_wkdays_per_week ='||to_number(c_wkdays_per_week));
74 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
75
76 ELSIF UPPER(p_std_freq) = 'MONTH' THEN
77
78 hr_utility.trace('p_std_freq = MONTH ');
79
80 v_wrkday_hours := p_std_hrs / c_wkdays_per_month;
81
82
83 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
84 hr_utility.trace('c_wkdays_per_month ='||to_number(c_wkdays_per_month));
85 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
86
87 ELSIF UPPER(p_std_freq) = 'YEAR' THEN
88
89 hr_utility.trace('p_std_freq = YEAR ');
90 v_wrkday_hours := p_std_hrs / c_wkdays_per_year;
91
92 hr_utility.trace('p_std_hrs ='||to_number(p_std_hrs));
93 hr_utility.trace('c_wkdays_per_year ='||to_number(c_wkdays_per_year));
94 hr_utility.trace('v_wrkday_hours ='||to_number(v_wrkday_hours));
95
96 ELSE
97 hr_utility.trace('p_std_freq in ELSE ');
98 v_wrkday_hours := p_std_hrs;
99 END IF;
100
101 v_curr_date := p_range_start;
102
103 hr_utility.trace('v_curr_date is range start'||to_char(v_curr_date));
104
105
106 LOOP
107
108 v_day_no := TO_CHAR(v_curr_date, 'D');
109
110
111 IF v_day_no > 1 and v_day_no < 7 then
112
113
114 v_total_hours := nvl(v_total_hours,0) + v_wrkday_hours;
115
116 hr_utility.trace(' v_day_no = '||to_char(v_day_no));
117 hr_utility.trace(' v_total_hours = '||to_char(v_total_hours));
118 END IF;
119
120 v_curr_date := v_curr_date + 1;
121 EXIT WHEN v_curr_date > p_range_end;
122 END LOOP;
123 hr_utility.trace(' Final v_total_hours = '||to_char(v_total_hours));
124 hr_utility.trace(' Leaving standard_hours_worked' );
125 --
126 RETURN v_total_hours;
127 --
128 END standard_hours_worked;
129
130
131 FUNCTION calculate_actual_hours_worked
132 (assignment_action_id IN number --Context
133 ,assignment_id IN number --Context
134 ,business_group_id IN number --Context
135 ,element_entry_id IN number --Context
136 ,date_earned IN date --Context
137 ,p_period_start_date IN date
138 ,p_period_end_date IN date
139 ,p_schedule_category IN varchar2 --Optional
140 ,p_include_exceptions IN varchar2 --Optional
141 ,p_busy_tentative_as IN varchar2 --Optional
142 ,p_legislation_code IN varchar2
143 ,p_schedule_source IN OUT nocopy varchar2
144 ,p_schedule IN OUT nocopy varchar2
145 ,p_return_status OUT nocopy number
146 ,p_return_message OUT nocopy varchar2)
147 RETURN NUMBER IS
148 l_work_schedule_found BOOLEAN;
149 l_total_hours NUMBER;
150 l_normal_hours NUMBER;
151 l_asg_frequency VARCHAR2(20);
152
153 CURSOR get_asg_hours_freq(p_date_earned date,
154 p_assignment_id number)IS
155 SELECT hr_general.decode_lookup('FREQUENCY', ASSIGN.frequency)
156 ,ASSIGN.normal_hours
157 FROM per_all_assignments_f ASSIGN
158 where date_earned
159 BETWEEN ASSIGN.effective_start_date
160 AND ASSIGN.effective_end_date
161 and ASSIGN.assignment_id = p_assignment_id;
162
163 CURSOR get_leg_code(p_business_group_id VARCHAR2) IS
164 select ORG_INFORMATION9
165 from hr_organization_information
166 where org_information_context = 'Business Group Information'
167 and organization_id = p_business_group_id;
168
169
170 BEGIN
171 l_work_schedule_found := FALSE;
172 l_total_hours := 0;
173
174 hr_utility.trace( 'date_earned '||date_earned);
175 hr_utility.trace('assignment_action_id=' || assignment_action_id);
176 hr_utility.trace('assignment_id=' || assignment_id);
177 hr_utility.trace('business_group_id=' || business_group_id);
178 hr_utility.trace('element_entry_id=' || element_entry_id);
179 hr_utility.trace( 'date_earned '||date_earned);
180 hr_utility.trace('p_period_start_date=' || p_period_start_date);
181 hr_utility.trace('p_period_end_date=' || p_period_end_date);
182 hr_utility.trace('p_legislation_code=' || p_legislation_code);
183 hr_utility.trace('p_schedule_category=' || p_schedule_category);
184 hr_utility.trace('p_schedule_source=' || p_schedule_source);
185 hr_utility.trace('p_include_exceptions=' || p_include_exceptions);
186 hr_utility.trace('p_busy_tentative_as=' || p_busy_tentative_as);
187 hr_utility.trace('p_schedule=' || p_schedule);
188
189
190 IF (p_legislation_code IS NULL) AND (g_legislation_code IS NULL) THEN
191 OPEN get_leg_code(business_group_id);
192 FETCH get_leg_code INTO g_legislation_code;
193 CLOSE get_leg_code;
194 END IF;
195
196
197
198 IF length(p_schedule_source) = 0 THEN
199 p_schedule_source := 'PER_ASG';
200 END IF;
201
202 IF length(p_schedule) = 0 THEN
203 /* THis might needs to be changed once the HR API , HR_WRK_SCH_PKG.GET_PER_ASG_SCHEDULE
204 will be available */
205 p_schedule := 'WORK';
206 END IF;
207
208
209 /* Calculate hours worked based on ATG work schedule information using
210 API : HR_WRK_SCH_PKG.GET_PER_ASG_SCHEDULE ()
211 This part will be coded later once this API is available from HR
212 IF p_include_exceptions IS NULL THEN
213 use p_include_exceptions = 'Y';
214
215 */
216
217 IF NOT l_work_schedule_found THEN
218 BEGIN
219 hr_utility.trace( 'getting work schedule from SCL ');
220 EXECUTE IMMEDIATE 'BEGIN :1 := PAY_'||g_legislation_code||
221 '_RULES.Work_Schedule_Total_Hours(:2,:3,:4,:5,:6,:7,:8); END;'
222 USING OUT l_total_hours,
223 IN assignment_action_id,assignment_id,business_group_id,element_entry_id
224 ,date_earned,p_period_start_date,p_period_end_date;
225
226 IF l_total_hours > 0 THEN
227 hr_utility.trace( 'work schedule found from SCL ');
228 l_work_schedule_found := TRUE;
229 return l_total_hours;
230 END IF;
231 EXCEPTION
232 WHEN OTHERS THEN
233 --Raise;
234 null;
235 END;
236 END IF;
237
238 /* Calculate hours worked based on standard conditions if the actual hours
239 worked are not available from either ATG work schedule or work schedule
240 at assignment/org level */
241
242 IF NOT l_work_schedule_found THEN
243 hr_utility.trace('Calculating hours based on Standard conditions ');
244 hr_utility.trace( 'Assignment Id '||assignment_id);
245 hr_utility.trace( 'date_earned '||date_earned);
246 OPEN get_asg_hours_freq(date_earned,assignment_id);
247 FETCH get_asg_hours_freq
248 INTO l_asg_frequency, l_normal_hours;
249 CLOSE get_asg_hours_freq;
250
251 hr_utility.trace( 'l_asg_frequency '||l_asg_frequency);
252 hr_utility.trace( 'l_normal_hours '||l_normal_hours);
253
254 IF l_asg_frequency IS NOT NULL and l_normal_hours IS NOT NULL THEN
255 l_total_hours := standard_hours_worked(l_normal_hours
256 ,p_period_start_date
257 ,p_period_end_date
258 ,l_asg_frequency);
259 return l_total_hours;
260 END IF;
261
262 END IF;
263 return 0;
264 END calculate_actual_hours_worked;
265 END pay_calc_hours_worked;
[Home] [Help]