[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PAYROLL_BALANCE
Source
1 Package Body ben_ext_payroll_balance as
2 /* $Header: benxpybl.pkb 120.4.12010000.2 2008/08/05 14:59:29 ubhat ship $ */
3 --
4 --
5 function Get_us_Balance_Value
6 (p_business_group_id in number
7 ,p_assignment_id in number
8 ,p_effective_date in date
9 ,p_legislation_code in varchar2
10 ,p_defined_balance_id in number) return number as
11
12 cursor c_tax_id (c_assignment_id in number
13 ,c_effective_date in date) is
14 select to_number(sft.segment1)
15 from hr_soft_coding_keyflex sft,
16 per_assignments_f asg
17 where sft.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
18 and asg.assignment_id = c_assignment_id
19 and c_effective_date between asg.effective_start_date
20 and asg.effective_end_date;
21
22 cursor c_jur_code(c_assignment_id in number
23 ,c_business_group_id in number
24 ,c_effective_date in date) is
25 select str.jurisdiction_code
26 from pay_us_emp_state_tax_rules_f str
27 where c_effective_date between str.effective_start_date
28 and str.effective_end_date
29 and str.assignment_id = c_assignment_id
30 and str.business_group_id = c_business_group_id;
31
32
33 cursor c_fr_tax_id (c_assignment_id in number
34 ,c_effective_date in date) is
35 select asg.ESTABLISHMENT_ID
36 from per_all_assignments_f asg
37 where asg.assignment_id = c_assignment_id
38 and c_effective_date between asg.effective_start_date
39 and asg.effective_end_date
40 ;
41
42 -- check the dimension has tax_unit_id context
43 cursor c_tax_context is
44 select pbd.DIMENSION_NAME
45 from ff_contexts ffc
46 ,ff_route_context_usages frc
47 ,pay_defined_balances pdb
48 ,pay_balance_dimensions pbd
49 where pdb.defined_balance_id = p_defined_balance_id
50 and pdb.balance_dimension_id = pbd.balance_dimension_id
51 and pbd.route_id = frc.route_id
52 and frc.context_id = ffc.context_id
53 and ffc.context_name = 'TAX_UNIT_ID'
54 ;
55
56 l_balance_amount number;
57 l_gre_id number;
58 l_jurisdiction_code varchar2(150);
59 l_DIMENSION_NAME pay_balance_dimensions.DIMENSION_NAME%type ;
60
61 l_proc_name constant varchar2(150) := g_package ||'Get_us_Balance_Value';
62
63 begin
64 hr_utility.set_location('Entering: '||l_proc_name, 5);
65
66 --
67 open c_tax_id(c_assignment_id => p_assignment_id
68 ,c_effective_date => p_effective_date);
69 fetch c_tax_id into l_gre_id;
70 close c_tax_id;
71 pay_balance_pkg.set_context('tax_unit_id', l_gre_id);
72 pay_balance_pkg.set_context('date_earned', p_effective_date);
73
74 if p_legislation_code = 'US' then
75 open c_jur_code(c_assignment_id => p_assignment_id
76 ,c_business_group_id => p_business_group_id
77 ,c_effective_date => p_effective_date);
78 fetch c_jur_code into l_jurisdiction_code;
79 close c_jur_code;
80 pay_balance_pkg.set_context('jurisdiction_code', l_jurisdiction_code);
81 End if ;
82
83 if p_legislation_code = 'FR' then
84
85 -- set the context for france
86 open c_tax_id(c_assignment_id => p_assignment_id
87 ,c_effective_date => p_effective_date);
88 fetch c_tax_id into l_gre_id;
89 close c_tax_id;
90 pay_balance_pkg.set_context('tax_unit_id', l_gre_id);
91
92 End if ;
93
94 /*
95 -- validate context, if the context has tax unit and gre is null then
96 -- log a message and return null
97 open c_tax_context ;
98 fetch c_tax_context into l_DIMENSION_NAME ;
99 if c_tax_context%found then
100 if l_gre_id is null then
101 close c_tax_context
102 -- context tax unit id found but no tax unit id
103 -- log warning
104 return null ;
105 end if ;
106 end if ;
107 close c_tax_context ;
108 */
109
110 -- Call the pay balance pkg in date mode
111 l_balance_amount := pay_balance_pkg.get_value
112 (p_defined_balance_id
113 ,p_assignment_id
114 ,p_effective_date);
115
116
117 hr_utility.set_location('Leaving: '||l_proc_name, 80);
118 return l_balance_amount;
119 exception
120 when others then
121 hr_utility.set_location('Leaving: '||l_proc_name, 90);
122 l_balance_amount := null ;
123 raise;
124 end Get_us_Balance_Value;
125
126
127
128 function Get_fn_Balance_Value
129 (p_business_group_id in number
130 ,p_assignment_id in number
131 ,p_effective_date in date
132 ,p_legislation_code in varchar2
133 ,p_defined_balance_id in number) return number as
134
135 l_balance_amount number;
136 l_gre_id number;
137 l_proc_name constant varchar2(150) := g_package ||'Get_fn_Balance_Value';
138
139 begin
140 hr_utility.set_location('Entering: '||l_proc_name, 5);
141
142 --
143 if p_legislation_code = 'FI' then
144
145 pay_FI_rules.get_main_tax_unit_id
146 (p_assignment_id => p_assignment_id
147 ,p_effective_date => p_effective_date
148 ,p_tax_unit_id => l_gre_id
149 ) ;
150
151 elsif p_legislation_code = 'IE' then
152
153 pay_IE_rules.get_main_tax_unit_id
154 (p_assignment_id => p_assignment_id
155 ,p_effective_date => p_effective_date
156 ,p_tax_unit_id => l_gre_id
157 ) ;
158
159 elsif p_legislation_code = 'MX' then
160
161 pay_MX_rules.get_main_tax_unit_id
162 (p_assignment_id => p_assignment_id
163 ,p_effective_date => p_effective_date
164 ,p_tax_unit_id => l_gre_id
165 ) ;
166
167 elsif p_legislation_code = 'ES' then
168
169 pay_ES_rules.get_main_tax_unit_id
170 (p_assignment_id => p_assignment_id
171 ,p_effective_date => p_effective_date
172 ,p_tax_unit_id => l_gre_id
173 ) ;
174
175 elsif p_legislation_code = 'NL' then
176
177 pay_NL_rules.get_main_tax_unit_id
178 (p_assignment_id => p_assignment_id
179 ,p_effective_date => p_effective_date
180 ,p_tax_unit_id => l_gre_id
181 ) ;
182
183 elsif p_legislation_code = 'SE' then
184
185 pay_SE_rules.get_main_tax_unit_id
186 (p_assignment_id => p_assignment_id
187 ,p_effective_date => p_effective_date
188 ,p_tax_unit_id => l_gre_id
189 ) ;
190
191 else
192 l_gre_id := null ;
193 end if ;
194
195 pay_balance_pkg.set_context('tax_unit_id', l_gre_id);
196 pay_balance_pkg.set_context('date_earned', p_effective_date);
197
198 -- Call the pay balance pkg in date mode
199 l_balance_amount := pay_balance_pkg.get_value
200 (p_defined_balance_id
201 ,p_assignment_id
202 ,p_effective_date);
203
204
205 hr_utility.set_location('Leaving: '||l_proc_name, 80);
206 return l_balance_amount;
207 exception
208 when others then
209 hr_utility.set_location('Leaving: '||l_proc_name, 90);
210 l_balance_amount := null ;
211 raise;
212 end Get_fn_Balance_Value;
213
214
215
216
217
218
219 function Get_Balance_Value
220 (p_business_group_id in number
221 ,p_assignment_id in number
222 ,p_effective_date in date
223 ,p_legislation_code in varchar2
224 ,p_defined_balance_id in number) return number as
225
226 l_balance_amount number;
227 l_proc_name constant varchar2(150) := g_package ||'Get_Balance_Value';
228
229
230 begin
231
232 hr_utility.set_location('Entering: '||l_proc_name, 5);
233
234 if p_legislation_code in ( 'US','CA','AU','CN','DK','HK','IN','KW','SA','SG','AE','FR') then
235 l_balance_amount := Get_US_Balance_Value
236 (p_business_group_id => p_business_group_id
237 ,p_assignment_id => p_assignment_id
238 ,p_effective_date => p_effective_date
239 ,p_legislation_code => p_LEGISLATION_CODE
240 ,p_defined_balance_id => p_defined_balance_id
241 ) ;
242
243 elsif p_legislation_code in ('FI','IE','MX','ES','NL','SE') then
244
245 l_balance_amount := Get_fn_Balance_Value
246 (p_business_group_id => p_business_group_id
247 ,p_assignment_id => p_assignment_id
248 ,p_effective_date => p_effective_date
249 ,p_legislation_code => p_LEGISLATION_CODE
250 ,p_defined_balance_id => p_defined_balance_id
251 ) ;
252
253 else
254 l_balance_amount := pay_balance_pkg.get_value
255 (p_defined_balance_id
256 ,p_assignment_id
257 ,p_effective_date);
258 end if ;
259
260 hr_utility.set_location('Leaving: '||l_proc_name, 80);
261 return l_balance_amount;
262
263 exception
264 when others then
265 hr_utility.set_location('Leaving: '||l_proc_name, 90);
266 l_balance_amount := null;
267 return l_balance_amount ;
268 end Get_Balance_Value ;
269
270 --
271
272 PROCEDURE sort_payroll_events
273 (p_pay_events_tab IN ben_ext_person.t_detailed_output_table)
274 IS
275
276 l_pay_events_tab ben_ext_person.t_detailed_output_table;
277 l_sortrec ben_ext_person.t_detailed_output_tab_rec;
278 l_next_event_date date ;
279 l_proc varchar2(75) ;
280 BEGIN
281
282 l_proc := g_package ||'sort_payroll_events';
283 hr_utility.set_location('Entering: '||l_proc, 5);
284 -- nullify the global table
285 ben_ext_person.g_pay_proc_evt_tab := l_pay_events_tab ;
286 --intiali
287 l_pay_events_tab := p_pay_events_tab;
288
289 hr_utility.set_location('Before Looping ', 10);
290 hr_utility.set_location('Count:'||to_char(l_pay_events_tab.COUNT), 10 );
291
292 IF (l_pay_events_tab.COUNT > 0) THEN
293
294 --
295 -- Bubble sort the events by effective_date
296 --
297 FOR i IN l_pay_events_tab.FIRST .. l_pay_events_tab.LAST
298 LOOP
299 hr_utility.trace('Inside Loop1');
300 l_next_event_date := null ;
301 FOR j IN l_pay_events_tab.FIRST + i .. l_pay_events_tab.LAST
302 LOOP
303 hr_utility.trace('Inside Loop2');
304 IF (l_pay_events_tab (i).effective_date > l_pay_events_tab (j).effective_date)
305 THEN
306 hr_utility.trace('Inside IF');
307 l_sortrec := l_pay_events_tab (i);
308 l_pay_events_tab (i) := l_pay_events_tab (j);
309 l_pay_events_tab (j) := l_sortrec;
310 END IF;
311 END LOOP;
312 l_next_event_date := l_pay_events_tab(i).effective_date ;
313 -- update the previous rows next event start date
314 if l_next_event_date is not null and i > 1 and l_pay_events_tab.exists(i-1) then
315 l_pay_events_tab(i-1).next_evt_start_date := l_next_event_date ;
316 hr_utility.set_location('previous date ' || l_next_event_date , 99 ) ;
317 end if ;
318
319 END LOOP;
320
321 hr_utility.set_location('Finished Looping', 10 );
322 --
323 END IF;
324
325 -- Assign back the sorted collection
326 ben_ext_person.g_pay_proc_evt_tab := l_pay_events_tab;
327
328 hr_utility.set_location('Leaving: '||l_proc, 80);
329 EXCEPTION
330 WHEN OTHERS THEN
331 ben_ext_person.g_pay_proc_evt_tab := p_pay_events_tab ;
332 hr_utility.set_location('error ' || substr(sqlerrm,1,200) , 99 ) ;
333 RAISE;
334 END;
335
336
337 end ben_ext_payroll_balance;