1 package BODY pay_ie_bik as
2 /* $Header: pyiebik.pkb 120.2 2006/03/01 00:59:49 sgajula noship $ */
3
4 /* This function is used to fetch the specified interest rates for various type
5 of loans given, which are defined as globals.*/
6
7 function get_global_value(p_name in VARCHAR2,
8 p_effective_date in DATE)
9 return number is
10
11 cursor c_global_value_cursor is
12 select global_value
13 from ff_globals_f
14 Where global_name=p_name
15 and legislation_code='IE'
16 and p_effective_date between effective_start_date and effective_end_date;
17
18 l_global_value ff_globals_f.global_value%type;
19 l_effective_start_date DATE;
20 l_effective_end_date date;
21 l_proc varchar2(72) := 'pay_ie_bik.get_global_value';
22
23 BEGIN
24 hr_utility.set_location('Entering ' || l_proc, 20);
25 open c_global_value_cursor;
26 FETCH c_global_value_cursor into l_global_value;
27 close c_global_value_cursor;
28
29 hr_utility.set_location('Leaving ' || l_proc, 100);
30 return to_number(l_global_value);
31 EXCEPTION
32 when NO_DATA_FOUND then
33 raise_application_error(-20001,l_proc||'- '||sqlerrm);
34 end get_global_value;
35
36 /* This function is used for fetching the Start Date of the First Period in the
37 Financial year.*/
38 FUNCTION get_max_start_date(p_payroll_action_id IN number,
39 p_max_start_date in DATE,
40 p_benefit_start_date IN DATE)
41 return DATE as
42
43 cursor c_first_period_start_date(l_payroll_action_id NUMBER) is
44 select min(TPERIOD.start_date)
45 from pay_payroll_actions PACTION
46 , per_time_periods TPERIOD
47 , per_time_period_types TPTYPE
48 where PACTION.payroll_action_id = l_payroll_action_id
49 and TPERIOD.payroll_id = PACTION.payroll_id
50 and TPTYPE.period_type = TPERIOD.period_type
51 and to_char(PACTION.DATE_EARNED,'YYYY')=to_char(TPERIOD.END_DATE,'YYYY');
52
53 l_max_start_date DATE;
54 l_first_period_start_date DATE;
55 l_proc varchar2(72) := 'pay_ie_bik.get_max_start_date';
56
57 BEGIN
58
59 hr_utility.set_location('Entering ' || l_proc, 20);
60
61 l_max_start_date:=p_max_start_date;
62
63 open c_first_period_start_date(p_payroll_action_id);
64 fetch c_first_period_start_date into l_first_period_start_date;
65 close c_first_period_start_date;
66
67 IF l_first_period_start_date < p_max_start_date then
68 l_max_start_date := l_first_period_start_date;
69 if p_benefit_start_date between l_max_start_date and p_max_start_date then
70 l_max_start_date := p_benefit_start_date;
71 end if;
72 end if;
73 hr_utility.set_location('Leaving ' || l_proc, 100);
74 return l_max_start_date;
75 EXCEPTION
76 when NO_DATA_FOUND then
77 null;
78 end get_max_start_date;
79
80 -- Changed csr_least_date to select end_date of period (5070091)
81 FUNCTION get_least_date(
82 p_payroll_action_id in number,
83 c_end_date in date)
84 return date is
85 cursor csr_least_date is
86 select ptp.end_date
87 from pay_payroll_actions ppa
88 , per_time_periods ptp
89 WHERE ppa.payroll_action_id=p_payroll_action_id
90 AND ptp.payroll_id=ppa.payroll_id
91 AND ppa.DATE_EARNED between ptp.start_date and ptp.end_date;
92
93 l_least_date date;
94 l_least date;
95 l_proc varchar2(72) := 'pay_ie_bik.get_least_date';
96 begin
97 open csr_least_date;
98 fetch csr_least_date into l_least_date;
99 close csr_least_date;
100 l_least := least(l_least_date,c_end_date);
101 return l_least;
102
103 Exception
104 when others then
105 raise_application_error(-20003,l_proc||'- '||sqlerrm);
106 end get_least_date;
107
108
109 /*This function is used for fetching the Number of Pay Periods required to
110 spread Total Taxable Value for the Benefit*/
111
112 Function get_max_no_of_periods
113 (p_payroll_action_id in number,
114 p_maximum_start_date in date,
115 p_minimum_end_date in date,
116 p_formula_context in varchar2)
117 return Number is
118
119 -- Changed to select end_date of period (5070091)
120 cursor get_end_date(c_minimum_end_date in date) is
121 select ptp.end_date end_date
122 from
123 pay_payroll_actions ppa
124 , per_time_periods ptp
125 , per_time_period_types tptype
126 WHERE ppa.payroll_action_id=p_payroll_action_id
127 AND ptp.payroll_id=ppa.payroll_id
128 AND ptp.period_type = tptype.period_type
129 AND c_minimum_end_date between PTP.start_date and PTP.end_date;
130
131 -- Cursor now counts the periods whose end date fall between the dates (5070091)
132 cursor get_periods(c_maximum_start_date in date,
133 c_end_date in date) is
134 select count(ptp.period_num) pay_periods
135 from
136 pay_payroll_actions ppa
137 , per_time_periods ptp
138 , per_time_period_types tptype
139 WHERE ppa.payroll_action_id=p_payroll_action_id
140 AND ptp.payroll_id=ppa.payroll_id
141 AND ptp.period_type = tptype.period_type
142 AND ptp.end_date between
143 c_maximum_start_date and c_end_date;
144
145 -- Cursor now counts the periods whose end date fall between the dates (5070091)
146 cursor get_cumulative_periods(c_maximum_start_date in date,c_end_date in date) is
147 select count(ptp.period_num) pay_periods
148 from
149 pay_payroll_actions ppa
150 , per_time_periods ptp
151 , per_time_period_types tptype
152 WHERE ppa.payroll_action_id=p_payroll_action_id
153 AND ptp.payroll_id=ppa.payroll_id
154 AND ptp.period_type = tptype.period_type
155 AND ptp.end_date between
156 c_maximum_start_date and pay_ie_bik.get_least_date(p_payroll_action_id,
157 c_end_date);
158
159
160 l_proc varchar2(72) := 'pay_ie_bik.get_max_no_of_periods';
161 l_periods get_periods%rowtype;
162 l_cumulative_periods get_cumulative_periods%rowtype;
163 --l_end_date get_end_date%rowtype;
164 l_end_date date;
165
166
167 begin
168 hr_utility.set_location('Entering ' || l_proc, 20);
169 -- hr_utility.trace_on(null,'BIK');
170 hr_utility.trace(' p_maximum_start_date' || to_char(p_maximum_start_date,'dd-mon-yyyy'));
171 hr_utility.trace(' p_minimum_end_date' || to_char(p_minimum_end_date,'dd-mon-yyyy'));
172 hr_utility.trace(' p_formula_context' || p_formula_context);
173
174 open get_end_date(p_minimum_end_date);
175 fetch get_end_date into l_end_date;
176 if to_char(l_end_date,'YYYY') =
177 to_char(p_minimum_end_date,'YYYY') then
178 null;
179 else
180 l_end_date := p_minimum_end_date;
181 end if;
182 close get_end_date;
183 hr_utility.trace('l_end_date ' || to_char(l_end_date,'dd-mon-yyyy'));
184
185 if p_formula_context='f1' then
186 open get_periods(p_maximum_start_date, l_end_date);
187 fetch get_periods into l_periods;
188 close get_periods;
189 hr_utility.trace('l_periods ' || l_periods.pay_periods);
190 hr_utility.set_location('Leaving ' || l_proc, 100);
191 --hr_utility.trace_off;
192 return l_periods.pay_periods;
193 end if;
194
195 if p_formula_context='f2' then
196 open get_cumulative_periods(p_maximum_start_date, l_end_date);
197 fetch get_cumulative_periods into l_cumulative_periods;
198 close get_cumulative_periods;
199 hr_utility.trace('l_cumulative_periods.pay_periods ' || l_cumulative_periods.pay_periods);
200 hr_utility.set_location('Leaving ' || l_proc, 200);
201 -- hr_utility.trace_off;
202 return l_cumulative_periods.pay_periods;
203 end if;
204
205 Exception
206 when others then
207 hr_utility.trace('error ' ||sqlerrm);
208 --hr_utility.trace_off;
209 raise_application_error(-20002,l_proc||'- '||sqlerrm);
210 end get_max_no_of_periods;
211
212
213 FUNCTION get_balance_values(p_assignment_action_id number,
214 p_source_id number,
215 p_date_earned date,
216 p_balance_name varchar2)
217 return number is
218
219 cursor csr_get_values is
220 select pay_balance_pkg.get_value(pdb.defined_balance_id, p_assignment_action_id,
221 null, null,null, null,null, p_source_id,p_date_earned) Cumulative_Taxable_Value
222 from
223 pay_balance_dimensions pbd,
224 pay_balance_types pbt,
225 pay_defined_balances pdb
226 where
227 pbt.balance_type_id = pdb.balance_type_id
228 and pbd.balance_dimension_id = pdb.balance_dimension_id
229 and pbd.legislation_code = pbt.legislation_code
230 and pbt.balance_name= p_balance_name
231 and pbd.dimension_name='_ELEMENT_YTD'
232 and pbd.legislation_code='IE';
233
234 l_get_values csr_get_values%rowtype;
235 l_proc varchar2(72) := 'pay_ie_bik.get_balance_values';
236
237 begin
238 hr_utility.set_location('Entering ' || l_proc, 20);
239
240 open csr_get_values;
241 fetch csr_get_values into l_get_values;
242 close csr_get_values;
243
244 hr_utility.set_location('Leaving ' || l_proc, 200);
245
246 return l_get_values. Cumulative_Taxable_Value;
247
248 Exception
249 when others then
250 hr_utility.trace('error ' ||sqlerrm);
251 raise_application_error(-20004,l_proc||'- '||sqlerrm);
252 end get_balance_values;
253
254
255 FUNCTION get_address(l_address_type varchar2,
256 l_address_id varchar2)
257 return varchar2 is
258
259 TYPE l_address_rec is record (info varchar2(240));
260 l_address l_address_rec;
261
262 type l_address_refcur_type is ref
263
264 cursor return l_address_rec;
265 l_address_refcur l_address_refcur_type;
266 l_proc varchar2(72) := 'pay_ie_bik.get_address';
267 begin
268 hr_utility.set_location('Entering ' || l_proc, 20);
269 if l_address_type='address_line1' then
270 open l_address_refcur for select address_line1
271 from per_addresses
272 where address_id=l_address_id;
273 elsif l_address_type='address_line2' then
274 open l_address_refcur for select address_line2
275 from per_addresses
276 where address_id=l_address_id;
277 elsif l_address_type='address_line3' then
278 open l_address_refcur for select address_line3
279 from per_addresses
280 where address_id=l_address_id;
281 elsif l_address_type= 'town_or_city' then
282 open l_address_refcur for select town_or_city
283 from per_addresses
284 where address_id=l_address_id;
285 elsif l_address_type= 'region_1' then
286 open l_address_refcur for select region_1
287 from per_addresses
288 where address_id=l_address_id;
289 elsif l_address_type= 'postal_code' then
290 open l_address_refcur for select postal_code
291 from per_addresses
292 where address_id=l_address_id;
293 elsif l_address_type= 'country' then
294 open l_address_refcur for select country
295 from per_addresses
296 where address_id=l_address_id;
297 end if;
298
299 fetch l_address_refcur into l_address;
300 CLOSE l_address_refcur;
301 hr_utility.set_location('Leaving ' || l_proc, 200);
302
303 return (l_address.info);
304
305 Exception
306 when others then
307 hr_utility.trace('error ' ||sqlerrm);
308 raise_application_error(-20005,l_proc||'- '||sqlerrm);
309
310 end get_address;
311
312 FUNCTION get_landlord_address(l_address_type varchar2,
313 l_address_id varchar2)
314 return varchar2 is
315
316 type l_landlord_rec is record (info varchar2(240));
317 l_landlord l_landlord_rec;
318
319 type l_landlord_refcur_type is ref cursor return l_landlord_rec;
320 l_landlord_refcur l_landlord_refcur_type;
321
322 l_proc varchar2(72) := 'pay_ie_bik.get_landlord_address';
323 begin
324
325 hr_utility.set_location('Entering ' || l_proc, 20);
326
327 if l_address_type='address_line1' then
328
329 open l_landlord_refcur for select hl.ADDRESS_LINE_1
330 from hr_all_organization_units hou,
331 hr_locations hl
332 where hou.organization_id=l_address_id
333 and hou.location_id=hl.location_id;
334
335 elsif l_address_type='address_line2' then
336
337 open l_landlord_refcur for select hl.ADDRESS_LINE_2
338 from hr_all_organization_units hou,
339 hr_locations hl
340 where hou.organization_id=l_address_id
341 and hou.location_id=hl.location_id;
342
343 elsif l_address_type='address_line3' then
344
345 open l_landlord_refcur for select hl.ADDRESS_LINE_3
346 from hr_all_organization_units hou,hr_locations hl
347 where hou.organization_id=l_address_id
348 and hou.location_id=hl.location_id;
349
350 elsif l_address_type= 'town_or_city' then
351
352 open l_landlord_refcur for select hl.town_or_city
353 from hr_all_organization_units hou,hr_locations hl
354 where hou.organization_id=l_address_id
355 and hou.location_id=hl.location_id;
356
357 elsif l_address_type= 'region_1' then
358
359 open l_landlord_refcur for select hl.region_1
360 from hr_all_organization_units hou,hr_locations hl
361 where hou.organization_id=l_address_id
362 and hou.location_id=hl.location_id;
363
364 elsif l_address_type= 'postal_code' then
365
366 open l_landlord_refcur for select hl.postal_code
367 from hr_all_organization_units hou,hr_locations hl
368 where hou.organization_id=l_address_id
369 and hou.location_id=hl.location_id;
370
371 elsif l_address_type= 'country' then
372
373 open l_landlord_refcur for select hl.country
374 from hr_all_organization_units hou,hr_locations hl
375 where hou.organization_id=l_address_id
376 and hou.location_id=hl.location_id;
377 end if;
378 fetch l_landlord_refcur into l_landlord;
379 CLOSE l_landlord_refcur;
380
381 hr_utility.set_location('Leaving ' || l_proc, 200);
382
383 return (l_landlord.info);
384
385 Exception
386 when others then
387 hr_utility.trace('error ' ||sqlerrm);
388 raise_application_error(-20006,l_proc||'- '||sqlerrm);
389
390 end get_landlord_address;
391
392 -- cursor get_invalid_days is changed as it returns incorrect value for Offset Payrolls (5070091)
393 function GET_INV_UNA_DAYS(p_element_entry_id in number,
394 p_vehicle_alloc_end_date in DATE,
395 p_curr_period_end_date in DATE) return number is
396 cursor get_invalid_days(p_element_entry_id in number,
397 p_vehicle_alloc_end_date in DATE,
398 p_curr_period_start_date in DATE) is
399 select nvl(sum(to_number(prrv.result_value)),0)
400 from pay_element_types_f pet,
401 pay_input_values_f piv,
402 pay_element_entries_f pee,
403 pay_payroll_actions ppa,
404 pay_assignment_actions paas,
405 pay_run_results prr,
406 pay_run_result_values prrv
407 where pee.element_entry_id=p_element_entry_id
408 AND prr.source_id = pee.element_entry_id
409 AND prr.element_type_id=pet.element_type_id
410 AND pet.element_name='IE BIK Company Vehicle Details'
411 AND prr.run_result_id=prrv.run_result_id
412 AND piv.name='Days Unavailable in Period'
413 AND prrv.input_value_id = piv.input_value_id
414 AND paas.assignment_action_id=prr.assignment_action_id
415 AND paas.payroll_action_id=ppa.payroll_action_id
416 AND paas.assignment_id=pee.assignment_id
417 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
418 from pay_payroll_actions ppa1,
419 pay_assignment_actions paas1,
420 pay_run_results prr1,
421 pay_element_entries_f pee1
422 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
423 AND pee1.element_entry_id=p_element_entry_id
424 AND prr1.element_entry_id = pee1.element_entry_id
425 AND paas1.assignment_action_id=prr1.assignment_action_id
426 AND paas1.payroll_action_id=ppa1.payroll_action_id
427 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
428 -- query is now based on date earned (5070091)
429 AND ppa.date_earned<=p_curr_period_end_date
430 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
431 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
432 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
433 inv_days number(10);
434
435 begin
436 OPEN get_invalid_days(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
437 FETCH get_invalid_days into inv_days;
438 CLOSE get_invalid_days;
439
440 return inv_days;
441 end GET_INV_UNA_DAYS;
442
443 function GET_INV_TOT_MLGE(p_element_entry_id in number,
444 p_vehicle_alloc_end_date in DATE,
445 p_curr_period_end_date in DATE) return number is
446 -- cursor invalid_total_mileage is changed as it returns incorrect value for Offset Payrolls (5070091)
447 cursor invalid_total_mileage(p_element_entry_id in number,
448 p_vehicle_alloc_end_date in DATE,
449 p_curr_period_start_date in DATE) is
450 select nvl(sum(to_number(prrv.result_value)),0)
451 from pay_element_types_f pet,
452 pay_input_values_f piv,
453 pay_element_entries_f pee,
454 pay_payroll_actions ppa,
455 pay_assignment_actions paas,
456 pay_run_results prr,
457 pay_run_result_values prrv
458 where pee.element_entry_id=p_element_entry_id
459 AND prr.source_id = pee.element_entry_id
460 AND prr.element_type_id=pet.element_type_id
461 AND pet.element_name='IE BIK Company Vehicle Details'
462 AND prr.run_result_id=prrv.run_result_id
463 AND piv.name='Total Mileage for Run'
464 AND prrv.input_value_id = piv.input_value_id
465 AND paas.assignment_action_id=prr.assignment_action_id
466 AND paas.payroll_action_id=ppa.payroll_action_id
467 AND paas.assignment_id=pee.assignment_id
468 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
469 from pay_payroll_actions ppa1,
470 pay_assignment_actions paas1,
471 pay_run_results prr1,
472 pay_element_entries_f pee1
473 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
474 AND pee1.element_entry_id=p_element_entry_id
475 AND prr1.element_entry_id = pee1.element_entry_id
476 AND paas1.assignment_action_id=prr1.assignment_action_id
477 AND paas1.payroll_action_id=ppa1.payroll_action_id
478 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
479 -- query is now based on date earned (5070091)
480 AND ppa.date_earned<=p_curr_period_end_date
481 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
482 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
483 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
484 inv_days number(10);
485
486 begin
487 OPEN invalid_total_mileage(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
488 FETCH invalid_total_mileage into inv_days;
489 CLOSE invalid_total_mileage;
490
491 return inv_days;
492 end GET_INV_TOT_MLGE;
493
494 function GET_INV_BUS_MLGE(p_element_entry_id in number,
495 p_vehicle_alloc_end_date in DATE,
496 p_curr_period_end_date in DATE) return number is
497 -- cursor invalid_bsg_mileage is changed as it returns incorrect value for Offset Payrolls (5070091)
498 cursor invalid_bsg_mileage(p_element_entry_id in number,
499 p_vehicle_alloc_end_date in DATE,
500 p_curr_period_start_date in DATE) is
501 select nvl(sum(to_number(prrv.result_value)),0)
502 from pay_element_types_f pet,
503 pay_input_values_f piv,
504 pay_element_entries_f pee,
505 pay_payroll_actions ppa,
506 pay_assignment_actions paas,
507 pay_run_results prr,
508 pay_run_result_values prrv
509 where pee.element_entry_id=p_element_entry_id
510 AND prr.source_id = pee.element_entry_id
511 AND prr.element_type_id=pet.element_type_id
512 AND pet.element_name='IE BIK Company Vehicle Details'
513 AND prr.run_result_id=prrv.run_result_id
514 AND piv.name='Business Mileage for Run'
515 AND prrv.input_value_id = piv.input_value_id
516 AND paas.assignment_action_id=prr.assignment_action_id
517 AND paas.payroll_action_id=ppa.payroll_action_id
518 AND paas.assignment_id=pee.assignment_id
519 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
520 from pay_payroll_actions ppa1,
521 pay_assignment_actions paas1,
522 pay_run_results prr1,
523 pay_element_entries_f pee1
524 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
525 AND pee1.element_entry_id=p_element_entry_id
526 AND prr1.element_entry_id = pee1.element_entry_id
527 AND paas1.assignment_action_id=prr1.assignment_action_id
528 AND paas1.payroll_action_id=ppa1.payroll_action_id
529 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
530 -- query is now based on date earned (5070091)
531 AND ppa.date_earned<=p_curr_period_end_date
532 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
533 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
534 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
535 inv_days number(10);
536
537 begin
538 OPEN invalid_bsg_mileage(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
539 FETCH invalid_bsg_mileage into inv_days;
540 CLOSE invalid_bsg_mileage;
541
542 return inv_days;
543 end GET_INV_BUS_MLGE;
544
545 end pay_ie_bik;
[Home] [Help]