1 package BODY pay_ie_bik as
2 /* $Header: pyiebik.pkb 120.2.12010000.3 2009/04/03 08:05:07 abraghun ship $ */
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;
68 l_max_start_date := 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
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
215 p_date_earned date,
212
213 FUNCTION get_balance_values(p_assignment_action_id number,
214 p_source_id number,
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
364 elsif l_address_type= 'postal_code' then
361 where hou.organization_id=l_address_id
362 and hou.location_id=hl.location_id;
363
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 OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
412 AND prr.run_result_id=prrv.run_result_id
413 AND piv.name='Days Unavailable in Period'
414 AND prrv.input_value_id = piv.input_value_id
415 AND paas.assignment_action_id=prr.assignment_action_id
416 AND paas.payroll_action_id=ppa.payroll_action_id
417 AND paas.assignment_id=pee.assignment_id
418 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
419 from pay_payroll_actions ppa1,
420 pay_assignment_actions paas1,
421 pay_run_results prr1,
422 pay_element_entries_f pee1
423 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
424 AND pee1.element_entry_id=p_element_entry_id
425 AND prr1.element_entry_id = pee1.element_entry_id
426 AND paas1.assignment_action_id=prr1.assignment_action_id
427 AND paas1.payroll_action_id=ppa1.payroll_action_id
428 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
429 -- query is now based on date earned (5070091)
430 AND ppa.date_earned<=p_curr_period_end_date
431 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
432 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
433 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
434 inv_days number(10);
435
436 begin
437 OPEN get_invalid_days(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
438 FETCH get_invalid_days into inv_days;
439 CLOSE get_invalid_days;
440
441 return inv_days;
442 end GET_INV_UNA_DAYS;
443
444 function GET_INV_TOT_MLGE(p_element_entry_id in number,
445 p_vehicle_alloc_end_date in DATE,
446 p_curr_period_end_date in DATE) return number is
447 -- cursor invalid_total_mileage is changed as it returns incorrect value for Offset Payrolls (5070091)
448 cursor invalid_total_mileage(p_element_entry_id in number,
449 p_vehicle_alloc_end_date in DATE,
450 p_curr_period_start_date in DATE) is
451 select nvl(sum(to_number(prrv.result_value)),0)
452 from pay_element_types_f pet,
453 pay_input_values_f piv,
454 pay_element_entries_f pee,
455 pay_payroll_actions ppa,
456 pay_assignment_actions paas,
457 pay_run_results prr,
458 pay_run_result_values prrv
459 where pee.element_entry_id=p_element_entry_id
460 AND prr.source_id = pee.element_entry_id
461 AND prr.element_type_id=pet.element_type_id
462 AND (pet.element_name='IE BIK Company Vehicle Details'
463 OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
464 AND prr.run_result_id=prrv.run_result_id
465 AND piv.name='Total Mileage for Run'
466 AND prrv.input_value_id = piv.input_value_id
467 AND paas.assignment_action_id=prr.assignment_action_id
468 AND paas.payroll_action_id=ppa.payroll_action_id
469 AND paas.assignment_id=pee.assignment_id
470 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
471 from pay_payroll_actions ppa1,
472 pay_assignment_actions paas1,
473 pay_run_results prr1,
474 pay_element_entries_f pee1
478 AND paas1.assignment_action_id=prr1.assignment_action_id
475 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
476 AND pee1.element_entry_id=p_element_entry_id
477 AND prr1.element_entry_id = pee1.element_entry_id
479 AND paas1.payroll_action_id=ppa1.payroll_action_id
480 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
481 -- query is now based on date earned (5070091)
482 AND ppa.date_earned<=p_curr_period_end_date
483 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
484 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
485 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
486 inv_days number(10);
487
488 begin
489 OPEN invalid_total_mileage(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
490 FETCH invalid_total_mileage into inv_days;
491 CLOSE invalid_total_mileage;
492
493 return inv_days;
494 end GET_INV_TOT_MLGE;
495
496 function GET_INV_BUS_MLGE(p_element_entry_id in number,
497 p_vehicle_alloc_end_date in DATE,
498 p_curr_period_end_date in DATE) return number is
499 -- cursor invalid_bsg_mileage is changed as it returns incorrect value for Offset Payrolls (5070091)
500 cursor invalid_bsg_mileage(p_element_entry_id in number,
501 p_vehicle_alloc_end_date in DATE,
502 p_curr_period_start_date in DATE) is
503 select nvl(sum(to_number(prrv.result_value)),0)
504 from pay_element_types_f pet,
505 pay_input_values_f piv,
506 pay_element_entries_f pee,
507 pay_payroll_actions ppa,
508 pay_assignment_actions paas,
509 pay_run_results prr,
510 pay_run_result_values prrv
511 where pee.element_entry_id=p_element_entry_id
512 AND prr.source_id = pee.element_entry_id
513 AND prr.element_type_id=pet.element_type_id
514 AND (pet.element_name='IE BIK Company Vehicle Details'
515 OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
516 AND prr.run_result_id=prrv.run_result_id
517 AND piv.name='Business Mileage for Run'
518 AND prrv.input_value_id = piv.input_value_id
519 AND paas.assignment_action_id=prr.assignment_action_id
520 AND paas.payroll_action_id=ppa.payroll_action_id
521 AND paas.assignment_id=pee.assignment_id
522 AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
523 from pay_payroll_actions ppa1,
524 pay_assignment_actions paas1,
525 pay_run_results prr1,
526 pay_element_entries_f pee1
527 where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
528 AND pee1.element_entry_id=p_element_entry_id
529 AND prr1.element_entry_id = pee1.element_entry_id
530 AND paas1.assignment_action_id=prr1.assignment_action_id
531 AND paas1.payroll_action_id=ppa1.payroll_action_id
532 AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
533 -- query is now based on date earned (5070091)
534 AND ppa.date_earned<=p_curr_period_end_date
535 AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
536 AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
537 AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
538 inv_days number(10);
539
540 begin
541 OPEN invalid_bsg_mileage(p_element_entry_id,p_vehicle_alloc_end_date,p_curr_period_end_date);
542 FETCH invalid_bsg_mileage into inv_days;
543 CLOSE invalid_bsg_mileage;
544
545 return inv_days;
546 end GET_INV_BUS_MLGE;
547
548
549 -- 8236523 -- 2009 Changes --
550
551 function get_fiscal_rating(p_allocation_id in number)
552 return number is
553 --to get the Emission Rating for computation of IE BIK Company Vehicle based on CO2 Emission
554 cursor fiscal_rating(c_allocation_id in number) is
555 SELECT
556 fiscal_ratings
557 FROM
558 pqp_vehicle_repository_f pvr,
559 pqp_vehicle_allocations_f pva
560 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
561 AND pva.vehicle_allocation_id = c_allocation_id
562 -- AND to_char(pvr.EFFECTIVE_END_DATE,'DDMMYYYY')='31124712';
563 AND pvr.EFFECTIVE_END_DATE =
564 (SELECT
565 MAX(pvr.EFFECTIVE_END_DATE)
566 FROM
567 pqp_vehicle_repository_f pvr,
568 pqp_vehicle_allocations_f pva
569 WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
570 AND pva.vehicle_allocation_id = c_allocation_id);
571
572 rating number;
573 begin
574 OPEN fiscal_rating(p_allocation_id);
575 FETCH fiscal_rating into rating;
576 CLOSE fiscal_rating;
577 RETURN rating;
578 end get_fiscal_rating;
579
580
581 end pay_ie_bik;
[Home] [Help]