[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_PRORATE_PKG
Source
1 PACKAGE BODY py_za_prorate_pkg as
2 /* $Header: pyzapror.pkb 120.4.12010000.2 2009/04/29 07:00:44 rbabla ship $ */
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 ,assignment_id IN number)
66 RETURN NUMBER IS
67
68 CURSOR c2 (x_payroll_action_id number, x_assignment_id number) is
69 select effective_start_date,effective_end_date
70 from per_assignments_f
71 where payroll_id = (select payroll_id
72 from pay_payroll_actions
73 where payroll_action_id = x_payroll_action_id)
74 and assignment_id = x_assignment_id --Modified for Bug 8464020
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 x_assignment_id NUMBER;
90
91
92 BEGIN
93 pay_fraction := 0;
94 days_worked := 0;
95 total_days_worked := 0;
96 x_payroll_action_id := payroll_action_id;
97 x_assignment_id := assignment_id;
98
99 BEGIN
100 select time_period_id
101 into x_time_period_id
102 from pay_payroll_actions
103 where payroll_action_id = x_payroll_action_id;
104
105 exception
106 when no_data_found then
107 x_time_period_id := 0;
108
109 END;
110 BEGIN
111 SELECT start_date,end_date
112 into start_date,end_date
113 from per_time_periods
114 where payroll_id = (select payroll_id
115 from pay_payroll_actions
116 where payroll_action_id = x_payroll_action_id)
117 and time_period_id = x_time_period_id;
118
119
120
121 select get_workdays(start_date,end_date) into
122 total_days from dual;
123 END;
124
125 BEGIN
126 OPEN C2 (x_payroll_action_id,x_assignment_id);
127 LOOP
128 FETCH c2 into eff_start_date,eff_end_date;
129 exit when c2%NOTFOUND;
130
131 if eff_start_date < start_date and
132 eff_end_date >= start_date and
133 eff_end_date <= end_date then
134
135 select get_workdays(start_date,eff_end_date) into
136 days_worked from dual;
137
138 end if;
139
140 if eff_start_date >= start_date and
141 eff_end_date <= end_date then
142 select get_workdays(eff_start_date,eff_end_date) into
143 days_worked from dual;
144
145 end if;
146
147 if eff_start_date >= start_date and
148 eff_start_date <= end_date and
149 eff_end_date > end_date then
150 select get_workdays(eff_start_date,end_date) into
151 days_worked from dual;
152 end if;
153
154 total_days_worked := total_days_worked + days_worked;
155 days_worked := 0;
156 END LOOP;
157
158 IF total_days < 22 then
159 flactuation := 21.6666 - total_days;
160 total_days_worked := total_days_worked + flactuation;
161 END IF;
162
163 IF total_days = 23 then
164 if total_days_worked = 22 then
165 total_days_worked := 21;
166 end if;
167 if total_days_worked = 21 then
168 total_days_worked := 20.6666;
169 end if;
170 END IF;
171
172 IF total_days = total_days_worked then
173 total_days_worked := 21.6666;
174 end if;
175 pay_fraction := total_days_worked / 21.6666;
176 END;
177 if pay_fraction = 0 then
178 pay_fraction := 1;
179 end if;
180
181 RETURN pay_fraction;
182
183 END ;
184
185 ----------------------------------------------------------------------
186
187
188 -------------------------------------------------------------------------------
189 -- pro_rate_days
190 -- Returns the number of days worked in the pay period
191 -- as a fraction of the total number of days in the period
192 -------------------------------------------------------------------------------
193 FUNCTION pro_rate_days
194 ( PAYROLL_ACTION_ID IN NUMBER
195 , ASSIGNMENT_ID IN NUMBER
196 )
197 RETURN NUMBER AS
198 -------------------------------------------------
199 -- Cursor c_Timeperiod
200 -- Returns the start and end dates of a period
201 -- for a payroll_action_id
202 -------------------------------------------------
203 CURSOR c_Timeperiod(
204 p_ppa_id IN pay_payroll_actions.payroll_action_id%TYPE
205 )
206 IS
207 SELECT ptp.start_date
208 , ptp.end_date
209 FROM per_time_periods ptp
210 , pay_payroll_actions ppa
211 WHERE ppa.payroll_action_id = p_ppa_id
212 AND ptp.time_period_id = ppa.time_period_id;
213 -------------------------------------------
214 -- Cursor c_Assignment
215 -- Returns the start and end dates of an
216 -- assignment
217 -------------------------------------------
218 CURSOR c_Assignment(
219 p_asg_id IN per_all_assignments_f.assignment_id%TYPE
220 )
221 IS
222 SELECT min(asg.effective_start_date) start_date
223 , max(asg.effective_end_date) end_date
224 FROM per_assignment_status_types past
225 , per_all_assignments_f asg
226 WHERE asg.assignment_id = p_asg_id
227 AND asg.assignment_status_type_id = past.assignment_status_type_id
228 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
229
230 ------------
231 -- Variables
232 ------------
233 l_Pdates c_Timeperiod%ROWTYPE;
234 l_Adates c_Assignment%ROWTYPE;
235
236 l_asg_dys_wrkd NUMBER;
237 l_dys_in_prd NUMBER;
238 l_frctn NUMBER;
239
240 -------------------------------------------------------------------------------
241 BEGIN -- MAIN --
242 -------------------------------------------------------------------------------
243 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',1);
244
245 OPEN c_Timeperiod(PAYROLL_ACTION_ID);
246 FETCH c_Timeperiod INTO l_Pdates;
247 IF c_Timeperiod%NOTFOUND THEN
248 raise no_data_found;
249 END IF;
250 CLOSE c_Timeperiod;
251
252 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',2);
253
254 OPEN c_Assignment(ASSIGNMENT_ID);
255 FETCH c_Assignment INTO l_Adates;
256 IF c_Assignment%NOTFOUND THEN
257 raise no_data_found;
258 END IF;
259 CLOSE c_Assignment;
260
261 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',3);
262
263 l_asg_dys_wrkd := LEAST(l_Pdates.end_date,l_Adates.end_date)
264 - GREATEST(l_Pdates.start_date,l_Adates.start_date)
265 + 1;
266 l_dys_in_prd := l_Pdates.end_date - l_Pdates.start_date + 1;
267
268 l_frctn := l_asg_dys_wrkd / l_dys_in_prd;
269
270 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',4);
271 RETURN l_frctn;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 hr_utility.set_location('py_za_prorate_pkg.pro_rate_days',5);
276 hr_utility.set_message(801, 'py_za_prorate_pkg: '||TO_CHAR(SQLCODE));
277 hr_utility.raise_error;
278 -------------------------------------------------------------------------------
279 END pro_rate_days;
280
281
282 END py_za_prorate_pkg;