1 package body pay_gb_working_tax_credit as
2 /* $Header: pygbwtcp.pkb 115.1 2003/12/17 01:29:18 asengar noship $
3 ******************************************************************
4 * *
5 * Copyright (C) 2002 Oracle Corporation UK Ltd., *
6 * Richmond, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21
22 Name : pay_gb_working_tax_credit
23
24 Description : This package contains calculations for use in processing
25 working tax credits from 06 April 2003 onwards
26
27 Uses :
28
29 Used By : WORKING_TAX_CREDIT fast formula
30
31 Change List :
32
33 Version Date Author Description
34 ------- ----- -------- ----------------
35
36 115.0 4/10/2002 GBUTLER Created
37 115.1 12/12/2003 asengar BUG 3221422 Changed Query of CURSOR
38 csr_daily_amt_iv_id for
39 improving performance.
40 */
41
42 g_package_name VARCHAR2(25) := 'PAY_GB_WORKING_TAX_CREDIT';
43
44 /* Primary function to calculate total amount payable to employee */
45 /* Called by WORKING_TAX_CREDIT fast formula */
46 /* Context parameters: p_assignment_id */
47
48 /* Function parameters: p_start_date */
49 /* p_end_date */
50
51 function calculate_payable
52 (p_assignment_id IN NUMBER,
53 p_start_date IN DATE,
54 p_end_date IN DATE)
55 return number is
56
57 l_function_name VARCHAR2(17) := 'calculate_payable';
58
59 l_daily_amt_iv_id pay_input_values_f.input_value_id%TYPE;
60
61 l_calc_start_date DATE;
62 l_calc_end_date DATE;
63
64 l_days_payable NUMBER := 0;
65
66 l_running_total NUMBER := 0;
67
68 l_total_payable NUMBER := 0;
69
70 /* Get input value id for Daily Amount */
71 -- BUG 3221422 Changed Query for improving performance
72 cursor csr_daily_amt_iv_id is
73 select piv.input_value_id
74 from pay_input_values_f piv,
75 pay_element_types_f petf
76 where petf.element_type_id = piv.element_type_id
77 and petf.element_name = 'Working Tax Credit'
78 and piv.name = 'Daily Amount'
79 and petf.legislation_code = 'GB';
80
81 /* Find Working Tax Credit element entries */
82 /* Only retrieve those entries that begin in or cross over into */
83 /* the period defined by the p_start_date and p_end_date */
84 /* parameters; past and future entries not relevant here */
85 cursor csr_wtc_entries is
86 select peef.effective_start_date as start_date,
87 peef.effective_end_date as end_date,
88 max(decode(peev.input_value_id,l_daily_amt_iv_id,
89 fnd_number.canonical_to_number(peev.Screen_entry_value),null)) as daily_amount
90 from pay_element_entry_values_f peev,
91 pay_element_entries_f peef,
92 pay_element_links_f pelf,
93 pay_element_types_f petf
94 where pelf.element_link_id = peef.element_link_id
95 and peef.element_entry_id = peev.element_entry_id
96 and petf.element_type_id = pelf.element_type_id
97 and peev.effective_start_date = peef.effective_start_date
98 and upper(petf.element_name) = upper('Working Tax Credit')
99 and petf.legislation_code = 'GB'
100 and peef.assignment_id = p_assignment_id
101 and peef.effective_start_date <= p_end_date
102 and ( peef.effective_start_date >= p_start_date
103 OR (peef.effective_start_date <= p_start_date
104 AND peef.effective_end_date >= p_start_date)
105 )
106 and peef.entry_type = 'E'
107 group by peef.effective_start_date, peef.effective_end_date;
108
109 begin
110
111 hr_utility.set_location(g_package_name||'.'||l_function_name,1);
112
113 hr_utility.trace('p_start_date: '||p_start_date);
114 hr_utility.trace('p_end_date: '||p_end_date);
115
116 /* Get Daily Amount input value */
117 open csr_daily_amt_iv_id;
118 fetch csr_daily_amt_iv_id into l_daily_amt_iv_id;
119 close csr_daily_amt_iv_id;
120
121 hr_utility.set_location(g_package_name||'.'||l_function_name,2);
122
123 /* Open cursor to retrieve working tax credit element entries up to p_end_date */
124 /* Loop round until all relevant entries retrieved */
125
126 for current_entry in csr_wtc_entries loop
127
128 hr_utility.set_location(g_package_name||'.'||l_function_name,3);
129
130 hr_utility.trace('current_entry.start_date: '||current_entry.start_date);
131 hr_utility.trace('current_entry.end_date: '||current_entry.end_date);
132
133 /* Initialise calculation start and end dates to null */
134 l_calc_start_date := null;
135 l_calc_end_date := null;
136
137 /* Determine which start date and end date to use when determining days */
138 /* payable for this current element entry */
139
140 /* Start date */
141 if p_start_date between current_entry.start_date
142 and current_entry.end_date
143 then
144 /* an active tax credit entry crosses into this period */
145 /* use period start date as start point for calculation */
146 l_calc_start_date := p_start_date;
147
148 else
149 /* start date of tax credit entry must be greater than */
150 /* period start date, so use start date of entry */
151 l_calc_start_date := current_entry.start_date;
152
153 end if;
154
155 /* End date */
156
157 if p_end_date between current_entry.start_date
158 and current_entry.end_date
159 then
160 /* an active tax credit entry continues beyond this period */
161 /* Use period end date as end point for calculation */
162 /* Tax credit entries where the employee has been terminated */
163 /* or a stop notice has been issued will not have been end-dated */
164 /* by this point, so they will be included in this scenario as */
165 /* p_end_date will be the least of payroll period end date, */
166 /* actual termination date and Stop Date of the tax credit */
167 l_calc_end_date := p_end_date;
168
169 else
170 /* user or another process must have manually end-dated current */
171 /* entry, so use end date of entry */
172 l_calc_end_date := current_entry.end_date;
173
174 end if;
175
176 hr_utility.trace('Calculation start date: '||l_calc_start_date);
177 hr_utility.trace('Calculation end date: '||l_calc_end_date);
178
179 hr_utility.set_location(g_package_name||'.'||l_function_name,4);
180
181 /* Work out number of days between calculation start and end date */
182
183 l_days_payable := pay_gb_working_tax_credit.days_between(l_calc_start_date, l_calc_end_date);
184
185
186 /* Multiply days payable for this loop by daily rate for current entry */
187 /* Add this amount to the running total amount for entries retrieved */
188 hr_utility.trace('Days payable: '||l_days_payable||' * current daily amount: '||
189 current_entry.daily_amount||' = '||(l_days_payable * current_entry.daily_amount));
190
191 l_running_total := l_running_total + (l_days_payable * current_entry.daily_amount);
192
193 hr_utility.trace('l_running_total: '||l_running_total);
194
195 hr_utility.set_location(g_package_name||'.'||l_function_name,5);
196
197 end loop;
198
199 hr_utility.set_location(g_package_name||'.'||l_function_name,6);
200
201 l_total_payable := l_running_total;
202
203 hr_utility.trace('Total amount payable: '||l_total_payable);
204
205 hr_utility.set_location(g_package_name||'.'||l_function_name,7);
206
207 return l_total_payable;
208
209 end calculate_payable;
210
211
212 function days_between
213 (p_start_date date,
214 p_end_date date)
215 return number is
216 --
217
218 v_days_between number := 0;
219 --
220 begin
221
222 v_days_between := p_end_date - p_start_date +1;
223
224 --
225 return v_days_between;
226 --
227 end days_between;
228
229
230 /* end of package */
231 end pay_gb_working_tax_credit;
[Home] [Help]