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