[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_PRORATE_PKG
Source
1 PACKAGE BODY py_za_prorate_pkg as
2 /* $Header: pyzapror.pkb 120.4 2005/08/25 02:47:45 kapalani noship $ */
3 /* +=======================================================================+
4 | Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +=======================================================================+
7
8 PRODUCT
9 Oracle Payroll - ZA Localisation
10
11 NAME
12 py_za_prorate_pkg.pkb
13
14 DESCRIPTION
15 This package contains functions that can be used in proration
16 functionality.
17
18 PUBLIC FUNCTIONS
19 Descriptions in package header
20 get_workdays
21 pro_rate
22 pro_rate_days
23
24 NOTES
25 .
26
27 MODIFICATION HISTORY
28 Person Date(DD-MM-YYYY) Version Comments
29 --------- ---------------- ------- -----------------------------
30 J.N. Louw 07-09-2001 115.2 Updated pro_rate_days
31 A.Stander 19-11-1998 110.0 Initial version
32 */
33
34 -------------------------------------------------------------------
35 function get_workdays(period_1 IN date,
36 period_2 IN date)
37 return number is
38 working_days number;
39 days varchar2(10);
40 next_date date;
41
42 BEGIN
43 working_days := 0;
44 next_date := period_1;
45 BEGIN
46 loop
47
48 exit when next_date > period_2;
49
50 select to_char(next_date, 'DAY') into days from dual;
51
52
53
54 If rtrim(days) in ('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then
55 working_days := working_days + 1;
56 end if;
57 next_date := next_date + 1;
58 end loop;
59 return(working_days);
60 -- pragma restrict_references(get_workdays,WNDS); -- Bug 4543522
61 END;
62 END;
63 -----------------------------------------------------------------------
64 FUNCTION pro_rate(payroll_action_id IN number)
65 RETURN NUMBER IS
66
67 CURSOR c2 (x_payroll_action_id number) is select effective_start_date,effective_end_date
68 from per_assignments_f
69 where payroll_id = (select payroll_id
70 from pay_payroll_actions
71 where payroll_action_id = x_payroll_action_id)
72 and assignment_id = (select assignment_id
73 from pay_assignment_actions
74 where payroll_action_id = x_payroll_action_id)
75 order by effective_start_date;
76
77
78 eff_start_date DATE;
79 start_date DATE;
80 end_date DATE;
81 eff_end_date DATE;
82 total_days NUMBER;
83 days_worked NUMBER;
84 total_days_worked NUMBER;
85 pay_fraction NUMBER;
86 flactuation NUMBER;
87 x_time_period_id NUMBER;
88 x_payroll_action_id NUMBER;
89
90 BEGIN
91 pay_fraction := 0;
92 days_worked := 0;
93 total_days_worked := 0;
94 x_payroll_action_id := payroll_action_id;
95 BEGIN
96 select time_period_id
97 into x_time_period_id
98 from pay_payroll_actions
99 where payroll_action_id = x_payroll_action_id;
100
101 exception
102 when no_data_found then
103 x_time_period_id := 0;
104
105 END;
106 BEGIN
107 SELECT start_date,end_date
108 into start_date,end_date
109 from per_time_periods
110 where payroll_id = (select payroll_id
111 from pay_payroll_actions
112 where payroll_action_id = x_payroll_action_id)
113 and time_period_id = x_time_period_id;
114
115
116
117 select get_workdays(start_date,end_date) into
118 total_days from dual;
119 END;
120
121 BEGIN
122 OPEN C2 (x_payroll_action_id);
123 LOOP
124 FETCH c2 into eff_start_date,eff_end_date;
125 exit when c2%NOTFOUND;
126
127 if eff_start_date < start_date and
128 eff_end_date >= start_date and
129 eff_end_date <= end_date then
130
131 select get_workdays(start_date,eff_end_date) into
132 days_worked from dual;
133
134 end if;
135
136 if eff_start_date >= start_date and
137 eff_end_date <= end_date then
138 select get_workdays(eff_start_date,eff_end_date) into
139 days_worked from dual;
140
141 end if;
142
143 if eff_start_date >= start_date and
144 eff_start_date <= end_date and
145 eff_end_date > end_date then
146 select get_workdays(eff_start_date,end_date) into
147 days_worked from dual;
148 end if;
149
150 total_days_worked := total_days_worked + days_worked;
151 days_worked := 0;
152 END LOOP;
153
154 IF total_days < 22 then
155 flactuation := 21.6666 - total_days;
156 total_days_worked := total_days_worked + flactuation;
157 END IF;
158
159 IF total_days = 23 then
160 if total_days_worked = 22 then
161 total_days_worked := 21;
162 end if;
163 if total_days_worked = 21 then
164 total_days_worked := 20.6666;
165 end if;
166 END IF;
167
168 IF total_days = total_days_worked then
169 total_days_worked := 21.6666;
170 end if;
171 pay_fraction := total_days_worked / 21.6666;
172 END;
173 if pay_fraction = 0 then
174 pay_fraction := 1;
175 end if;
176
177 RETURN pay_fraction;
178
179 END ;
180
181 ----------------------------------------------------------------------
182
183
184 -------------------------------------------------------------------------------
185 -- pro_rate_days
186 -- Returns the number of days worked in the pay period
187 -- as a fraction of the total number of days in the period
188 -------------------------------------------------------------------------------
189 FUNCTION pro_rate_days
190 ( PAYROLL_ACTION_ID IN NUMBER
191 , ASSIGNMENT_ID IN NUMBER
192 )
193 RETURN NUMBER AS
194 -------------------------------------------------
195 -- Cursor c_Timeperiod
196 -- Returns the start and end dates of a period
197 -- for a payroll_action_id
198 -------------------------------------------------
199 CURSOR c_Timeperiod(
200 p_ppa_id IN pay_payroll_actions.payroll_action_id%TYPE
201 )
202 IS
203 SELECT ptp.start_date
204 , ptp.end_date
205 FROM per_time_periods ptp
206 , pay_payroll_actions ppa
207 WHERE ppa.payroll_action_id = p_ppa_id
208 AND ptp.time_period_id = ppa.time_period_id;
209 -------------------------------------------
210 -- Cursor c_Assignment
211 -- Returns the start and end dates of an
212 -- assignment
213 -------------------------------------------
214 CURSOR c_Assignment(
215 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
216 )
217 IS
218 SELECT min(asg.effective_start_date) start_date
219 , max(asg.effective_end_date) end_date
220 FROM per_assignment_status_types past
221 , per_all_assignments_f asg
222 WHERE asg.assignment_id = p_asg_id
223 AND asg.assignment_status_type_id = past.assignment_status_type_id
224 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
225
226 ------------
227 -- Variables
228 ------------
229 l_Pdates c_Timeperiod%ROWTYPE;
230 l_Adates c_Assignment%ROWTYPE;
231
232 l_asg_dys_wrkd NUMBER;
233 l_dys_in_prd NUMBER;
234 l_frctn NUMBER;
235
236 -------------------------------------------------------------------------------
237 BEGIN -- MAIN --
238 -------------------------------------------------------------------------------
239 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',1);
240
241 OPEN c_Timeperiod(PAYROLL_ACTION_ID);
242 FETCH c_Timeperiod INTO l_Pdates;
243 IF c_Timeperiod%NOTFOUND THEN
244 raise no_data_found;
245 END IF;
246 CLOSE c_Timeperiod;
247
248 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',2);
249
250 OPEN c_Assignment(ASSIGNMENT_ID);
251 FETCH c_Assignment INTO l_Adates;
252 IF c_Assignment%NOTFOUND THEN
253 raise no_data_found;
254 END IF;
255 CLOSE c_Assignment;
256
257 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',3);
258
259 l_asg_dys_wrkd := LEAST(l_Pdates.end_date,l_Adates.end_date)
260 - GREATEST(l_Pdates.start_date,l_Adates.start_date)
261 + 1;
262 l_dys_in_prd := l_Pdates.end_date - l_Pdates.start_date + 1;
263
264 l_frctn := l_asg_dys_wrkd / l_dys_in_prd;
265
266 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',4);
267 RETURN l_frctn;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',5);
272 hr_utility.set_message(801, 'py_za_prorate_pkg: '||TO_CHAR(SQLCODE));
273 hr_utility.raise_error;
274 -------------------------------------------------------------------------------
275 END pro_rate_days;
276
277
278 END py_za_prorate_pkg;