[Home] [Help]
PACKAGE BODY: APPS.PAY_PYEPFREQ_PKG
Source
1 PACKAGE BODY pay_pyepfreq_pkg AS
2 /* $Header: pyepf01t.pkb 120.2 2005/06/15 06:36:21 susivasu noship $ */
3 --
4 PROCEDURE hr_ele_pay_freq_rules (
5 p_context IN VARCHAR2,
6 p_eletype_id IN NUMBER,
7 p_payroll_id IN NUMBER,
8 p_period_type IN VARCHAR2,
9 p_bg_id IN NUMBER,
10 p_period_1 IN OUT NOCOPY VARCHAR2,
11 p_period_2 IN OUT NOCOPY VARCHAR2,
12 p_period_3 IN OUT NOCOPY VARCHAR2,
13 p_period_4 IN OUT NOCOPY VARCHAR2,
14 p_period_5 IN OUT NOCOPY VARCHAR2,
15 p_period_6 IN OUT NOCOPY VARCHAR2,
16 p_eff_date IN DATE DEFAULT NULL,
17 p_rule_date_code IN VARCHAR2 DEFAULT NULL,
18 p_leg_code IN VARCHAR2 DEFAULT NULL) IS
19 -- local constants
20 c_months_per_fiscal_yr NUMBER(3) := 12;
21 c_max_freq_periods NUMBER(3) := 5;
22 -- local vars
23 v_ele_pay_freq_rule_id NUMBER(9);
24 v_freq_rule_period_id NUMBER(9);
25 v_freq_rule_start_date DATE := TO_DATE('01-01-1900', 'DD-MM-YYYY');
26 v_reset_periods NUMBER(3) := 1;
27 v_reset_period_type VARCHAR2(30);
28 v_number_per_fy NUMBER(3);
29 v_eff_start_date DATE;
30 --
31 -- Local procedure
32 --
33 PROCEDURE ins_freq_rule_period(
34 p_ele_freqrule_id IN NUMBER,
35 p_period_no_in_reset_period IN NUMBER,
36 p_bus_grp_id IN NUMBER,
37 p_eff_start_date IN DATE) IS
38 -- local proc local vars
39 v_freq_rule_pd_id NUMBER(9);
40
41 BEGIN
42 --
43 SELECT pay_freq_rule_periods_s.nextval
44 INTO v_freq_rule_pd_id
45 FROM sys.dual;
46 --
47 INSERT INTO pay_freq_rule_periods (
48 freq_rule_period_id,
49 ele_payroll_freq_rule_id,
50 business_group_id,
51 period_no_in_reset_period,
52 creation_date,
53 created_by,
54 last_update_date,
55 last_updated_by,
56 last_update_login)
57 VALUES (
58 v_freq_rule_pd_id,
59 p_ele_freqrule_id,
60 p_bus_grp_id,
61 p_period_no_in_reset_period,
62 p_eff_start_date,
63 -1,
64 NULL,
65 NULL,
66 NULL);
67 --
68 END ins_freq_rule_period;
69 --
70 -- Local Function
71 --
72 FUNCTION chk_freq_rule_exists ( p_ele_id IN NUMBER,
73 p_pay_id IN NUMBER,
74 p_bus_grp_id IN NUMBER,
75 p_period_num IN NUMBER)
76 RETURN VARCHAR2 IS
77 -- local fn vars
78 v_freq_rule_exists VARCHAR2(1);
79 --
80 BEGIN
81 --
82 v_freq_rule_exists := 'N';
83 begin
84 --
85 SELECT 'Y'
86 INTO v_freq_rule_exists
87 FROM pay_ele_payroll_freq_rules EPF,
88 pay_freq_rule_periods FRP
89 WHERE FRP.period_no_in_reset_period = p_period_num
90 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
91 AND EPF.business_group_id + 0 = p_bus_grp_id
92 AND EPF.payroll_id = p_pay_id
93 AND EPF.element_type_id = p_ele_id;
94 --
95 RETURN v_freq_rule_exists;
96 --
97 exception
98 WHEN NO_DATA_FOUND THEN
99 RETURN v_freq_rule_exists;
100 end;
101 --
102 END chk_freq_rule_exists;
103 --
104 BEGIN -- main procedure, hr_ele_freq_rules
105 --
106 IF UPPER(p_context) = 'ON-UPDATE' THEN
107 v_eff_start_date := nvl(p_eff_date, sysdate);
108 --
109 -- Clear the cache.
110 --
111 remove_freq_rule_period(p_ele_type_id => p_eletype_id
112 ,p_payroll_id => p_payroll_id);
113 --
114 --
115 -- Delete existing frequency rules:
116 --
117 begin
118
119 hr_utility.set_location('pay_pyepfreq_pkg', 10);
120
121 SELECT ele_payroll_freq_rule_id
122 INTO v_ele_pay_freq_rule_id
123 FROM pay_ele_payroll_freq_rules
124 WHERE element_type_id = p_eletype_id
125 AND payroll_id = p_payroll_id
126 AND business_group_id + 0 = p_bg_id;
127 --
128 hr_utility.set_location('pay_pyepfreq_pkg', 20);
129 DELETE FROM pay_ele_payroll_freq_rules
130 WHERE ele_payroll_freq_rule_id = v_ele_pay_freq_rule_id;
131 --
132 hr_utility.set_location('pay_pyepfreq_pkg', 30);
133 DELETE FROM pay_freq_rule_periods
134 WHERE ele_payroll_freq_rule_id = v_ele_pay_freq_rule_id;
135 --
136 -- If either of the above fail, then it's ok, we're just cleaning up
137 -- before insertion of "new" rule.
138 --
139 exception
140 WHEN NO_DATA_FOUND THEN NULL;
141 end;
142 --
143 -- Insert new frequency rules:
144 --
145 -- set reset period type: if pay period is <= Month, then reset period = Month;
146 -- if pay pd > Month then reset period = Year;
147 -- Note, if pay pd = Month, then freq rules defined by Deductions form do not
148 -- really make sense; would need a new interface to define a deduction freq
149 -- of, say, every other month or every third month.
150 --
151 begin
152
153 hr_utility.set_location('pay_pyepfreq_pkg', 40);
154 SELECT number_per_fiscal_year
155 INTO v_number_per_fy
156 FROM per_time_period_types
157 WHERE period_type = p_period_type;
158 --
159 IF v_number_per_fy >= c_months_per_fiscal_yr THEN
160 v_reset_period_type := 'Calendar Month';
161 ELSE
162 v_reset_period_type := 'Year';
163 END IF;
164 --
165 exception
166 WHEN NO_DATA_FOUND THEN
167 hr_utility.set_message('PAY', 'HR_COULD_NOT_FIND_PERIOD_TYPE');
168 hr_utility.raise_error;
169 end;
170 --
171 -- Now, insertion:
172 --
173 if (UPPER(p_period_1) = 'Y' or
174 UPPER(p_period_2) = 'Y' or
175 UPPER(p_period_3) = 'Y' or
176 UPPER(p_period_4) = 'Y' or
177 UPPER(p_period_5) = 'Y' )
178 then
179 hr_utility.set_location('pay_pyepfreq_pkg', 50);
180 SELECT pay_ele_payroll_freq_rules_s.nextval
181 INTO v_ele_pay_freq_rule_id
182 FROM sys.dual;
183 --
184 hr_utility.set_location('pay_pyepfreq_pkg', 60);
185 INSERT INTO pay_ele_payroll_freq_rules (
186 ele_payroll_freq_rule_id,
187 element_type_id,
188 payroll_id,
189 business_group_id,
190 start_date,
191 reset_no_of_periods,
192 reset_period_type,
193 rule_date_code,
194 creation_date,
195 created_by,
196 last_update_date,
197 last_updated_by,
198 last_update_login)
199 VALUES (
200 v_ele_pay_freq_rule_id,
201 p_eletype_id,
202 p_payroll_id,
203 p_bg_id,
204 v_freq_rule_start_date,
205 v_reset_periods,
206 v_reset_period_type,
207 p_rule_date_code,
208 v_eff_start_date,
209 -1,
210 NULL,
211 NULL,
212 NULL
213 );
214 end if;
215 --
216 -- insert freq rule period where period_n = 'Y'
217 --
218 IF UPPER(p_period_1) = 'Y' THEN
219 hr_utility.set_location('pay_pyepfreq_pkg', 70);
220 ins_freq_rule_period( v_ele_pay_freq_rule_id,
221 1,
222 p_bg_id,
223 v_eff_start_date);
224 END IF;
225 IF UPPER(p_period_2) = 'Y' THEN
226 hr_utility.set_location('pay_pyepfreq_pkg', 80);
227 ins_freq_rule_period( v_ele_pay_freq_rule_id,
228 2,
229 p_bg_id,
230 v_eff_start_date);
231 END IF;
232 IF UPPER(p_period_3) = 'Y' THEN
233 hr_utility.set_location('pay_pyepfreq_pkg', 90);
234 ins_freq_rule_period( v_ele_pay_freq_rule_id,
235 3,
236 p_bg_id,
237 v_eff_start_date);
238 END IF;
239 IF UPPER(p_period_4) = 'Y' THEN
240 hr_utility.set_location('pay_pyepfreq_pkg', 100);
241 ins_freq_rule_period( v_ele_pay_freq_rule_id,
242 4,
243 p_bg_id,
244 v_eff_start_date);
245 END IF;
246 IF UPPER(p_period_5) = 'Y' THEN
247 hr_utility.set_location('pay_pyepfreq_pkg', 110);
248 ins_freq_rule_period( v_ele_pay_freq_rule_id,
249 5,
250 p_bg_id,
251 v_eff_start_date);
252 END IF;
253 IF UPPER(p_period_6) = 'Y' THEN
254 hr_utility.set_location('pay_pyepfreq_pkg', 120);
255 ins_freq_rule_period( v_ele_pay_freq_rule_id,
256 6,
257 p_bg_id,
258 v_eff_start_date);
259 END IF;
260
261 --
262 ELSIF upper(p_context) = 'POST-QUERY' THEN
263 -- We need to populate the 5 period params and pass them back.
264 p_period_1 := get_freq_rule_period( p_eletype_id,
265 p_payroll_id,
266 p_bg_id,
267 1);
268
269 p_period_2 := get_freq_rule_period( p_eletype_id,
270 p_payroll_id,
271 p_bg_id,
272 2);
273
274 p_period_3 := get_freq_rule_period( p_eletype_id,
275 p_payroll_id,
276 p_bg_id,
277 3);
278
279 p_period_4 := get_freq_rule_period( p_eletype_id,
280 p_payroll_id,
281 p_bg_id,
282 4);
283
284 p_period_5 := get_freq_rule_period( p_eletype_id,
285 p_payroll_id,
286 p_bg_id,
287 5);
288
289 p_period_6 := get_freq_rule_period( p_eletype_id,
290 p_payroll_id,
291 p_bg_id,
292 6);
293
294 --
295 END IF; -- context
296 --
297 END hr_ele_pay_freq_rules;
298 --
299
300 /*
301 Name : get_freq_rule_period
302 Purpose : This function will populate the frequency rule data for all
303 periods in the first call and stores in plsql table.
304 Arguments : p_ele_type_id, p_payroll_id, p_bus_grp_id, p_period_num.
305 Notes :
306 */
307
308 FUNCTION get_freq_rule_period( p_ele_type_id IN NUMBER,
309 p_payroll_id IN NUMBER,
310 p_bus_grp_id IN NUMBER,
311 p_period_num IN NUMBER)
312 RETURN VARCHAR2 IS
313
314 ln_rec_index NUMBER;
315
316 -- local procedure
317 FUNCTION populate_freq_rule_table(p_element_type_id NUMBER,
318 p_payroll_id NUMBER,
319 p_bg_id NUMBER)
320 RETURN NUMBER IS
321
322 CURSOR c_get_freq_rule_period(cp_ele_type_id number,
323 cp_payroll_id number,
324 cp_bg_id number,
325 cp_period_num number)
326 IS
327 SELECT 'Y', EPF.rule_date_code
328 FROM pay_ele_payroll_freq_rules EPF,
329 pay_freq_rule_periods FRP
330 WHERE FRP.period_no_in_reset_period = cp_period_num
331 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
332 AND EPF.business_group_id + 0 = cp_bg_id
333 AND EPF.payroll_id = cp_payroll_id
334 AND EPF.element_type_id = cp_ele_type_id;
335
336 -- local populate_freq_rule_table procedure vars
337
338 lv_freq_rule_exists VARCHAR2(1);
339 lv_rule_date_code VARCHAR2(1);
340 ln_index2 number;
341 lv_record_found VARCHAR2(1);
342
343 -- start of populate_freq_rule_table procedure
344 BEGIN
345 lv_record_found := 'N';
346 hr_utility.trace('start of populate_freq_rule_table ');
347 hr_utility.trace('table rec ln_index2 = '||to_char(ln_index2));
348 hr_utility.trace('element_type_id = '||to_char(p_element_type_id));
349 hr_utility.trace('payroll_id = '||to_char(p_payroll_id));
350 hr_utility.trace('business_group_id = '||to_char(p_bg_id));
351
352 if pay_pyepfreq_pkg.g_freq_rule_table.count > 0 then
353 hr_utility.trace('g_freq_rule_table.count > 0 satisfied ');
354 for i in g_freq_rule_table.first..g_freq_rule_table.last
355 loop
356 hr_utility.trace('record i = '||to_char(i));
357 if g_freq_rule_table(i).element_type_id = p_ele_type_id and
358 g_freq_rule_table(i).payroll_id = p_payroll_id then
359
360 hr_utility.trace('record found in g_freq_rule_table ');
361 lv_record_found := 'Y';
362 ln_index2 := i;
363 exit;
364
365 end if;
366
367 end loop; -- g_freq_rule_table.first..g_freq_rule_table.last
368 end if; --pay_pyepfreq_pkg.g_freq_rule_table.count > 0
369
370 if lv_record_found = 'N' then
371 ln_index2 := pay_pyepfreq_pkg.g_freq_rule_table.count;
372 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).element_type_id :=
373 p_element_type_id;
374 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).payroll_id :=
375 p_payroll_id;
376 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).business_group_id :=
377 p_bg_id;
378
379 for i in 1..6 loop
380 lv_freq_rule_exists := null;
381 hr_utility.trace('for loop to get all period values i = '||
382 to_char(i));
383 open c_get_freq_rule_period(p_element_type_id, p_payroll_id,
384 p_bg_id, i);
385 fetch c_get_freq_rule_period into lv_freq_rule_exists,
386 lv_rule_date_code;
387 close c_get_freq_rule_period;
388
389 hr_utility.trace('period i value = '||lv_freq_rule_exists);
390 if lv_freq_rule_exists is null then
391 hr_utility.trace('period i values is null satisfied ');
392 lv_freq_rule_exists := 'N';
393 end if;
394
395 if i = 1 then
396 hr_utility.trace('period 1 = '||lv_freq_rule_exists);
397 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_1 :=
398 lv_freq_rule_exists;
399 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).rule_date_code :=
400 lv_rule_date_code;
401 elsif i = 2 then
402 hr_utility.trace('period 2 = '||lv_freq_rule_exists);
403 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_2 :=
404 lv_freq_rule_exists;
405 elsif i = 3 then
406 hr_utility.trace('period 3 = '||lv_freq_rule_exists);
407 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_3 :=
408 lv_freq_rule_exists;
409 elsif i = 4 then
410 hr_utility.trace('period 4 = '||lv_freq_rule_exists);
411 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_4 :=
412 lv_freq_rule_exists;
413 elsif i = 5 then
414 hr_utility.trace('period 5 = '||lv_freq_rule_exists);
415 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_5 :=
416 lv_freq_rule_exists;
417 elsif i = 6 then
418 hr_utility.trace('period 6 = '||lv_freq_rule_exists);
419 pay_pyepfreq_pkg.g_freq_rule_table(ln_index2).period_6 :=
420 lv_freq_rule_exists;
421 end if;
422
423 end loop;
424 hr_utility.trace('End of populate_freq_rule_table function');
425
426 end if; -- lv_record_found = 'N'
427
428 return ln_index2;
429 END populate_freq_rule_table;
430 -- end of populate_freq_rule_table procedure
431
432 -- start of get_freq_rule_period function
433 BEGIN
434 hr_utility.trace('Start of get_freq_rule_period function');
435 hr_utility.trace('Element type id: '||to_char(p_ele_type_id));
436 hr_utility.trace('Payroll id: '||to_char(p_payroll_id));
437 hr_utility.trace('Business Group id: '||to_char(p_bus_grp_id));
438 hr_utility.trace('Period Number: '||to_char(p_period_num));
439
440 ln_rec_index := populate_freq_rule_table(p_ele_type_id,
441 p_payroll_id,
442 p_bus_grp_id);
443 if p_period_num = 1 then
444 return g_freq_rule_table(ln_rec_index).period_1;
445 elsif p_period_num = 2 then
446 return g_freq_rule_table(ln_rec_index).period_2;
447 elsif p_period_num = 3 then
448 return g_freq_rule_table(ln_rec_index).period_3;
449 elsif p_period_num = 4 then
450 return g_freq_rule_table(ln_rec_index).period_4;
451 elsif p_period_num = 5 then
452 return g_freq_rule_table(ln_rec_index).period_5;
453 elsif p_period_num = 6 then
454 return g_freq_rule_table(ln_rec_index).period_6;
455 elsif p_period_num = 0 then
456 return g_freq_rule_table(ln_rec_index).rule_date_code;
457 end if;
458
459 hr_utility.trace('End of get_freq_rule_period function');
460
461 END get_freq_rule_period;
462 --
463 PROCEDURE remove_freq_rule_period(p_ele_type_id IN NUMBER,
464 p_payroll_id IN NUMBER) is
465
466 begin
467 if pay_pyepfreq_pkg.g_freq_rule_table.count > 0 then
468 for i in g_freq_rule_table.first..g_freq_rule_table.last
469 loop
470 if (g_freq_rule_table(i).element_type_id = p_ele_type_id
471 and g_freq_rule_table(i).payroll_id = p_payroll_id) then
472 --
473 g_freq_rule_table(i).element_type_id := NULL;
474 g_freq_rule_table(i).payroll_id := NULL;
475 exit;
476 --
477 end if;
478 end loop;
479 end if;
480 end remove_freq_rule_period;
481 --
482
483 /*
484 Name : initialise_freqrule_table
485 Purpose : This procedure will delete the plsql tables used for
486 frequency rule data.
487 Arguments :
488 Notes :
489 */
490
491 procedure initialise_freqrule_table is
492
493 BEGIN
494
495 hr_utility.trace('deleting g_freq_rule_table plsql table');
496
497 pay_pyepfreq_pkg.g_freq_rule_table.delete;
498
499 END initialise_freqrule_table;
500
501 END pay_pyepfreq_pkg;