1 package body PAY_NO_HIGH_RATE_TAX as
2 /* $Header: pynohtax.pkb 120.3.12000000.2 2007/03/09 13:22:52 sugarg noship $ */
3 --
4
5 /* commenting FUNCTION get_tax_values_high_rate and creating new function to remove
6 hard-coded user table names for high rate tax tables. */
7
8 /*
9
10 FUNCTION get_tax_values_high_rate (
11 p_business_group_id IN number
12 ,p_Date_Earned IN DATE
13 ,p_table_name IN VARCHAR2
14 ,p_freq IN VARCHAR2
15 ,p_ptd_amount IN VARCHAR2
16 ,p_high_tax OUT NOCOPY VARCHAR2
17 ,p_high_tax_base OUT NOCOPY VARCHAR2
18 ,p_high_rate_tax OUT NOCOPY VARCHAR2) return varchar2 IS
19 l_high_tax number;
20 l_high_tax_base number;
21 l_high_rate_tax number;
22 l_test_table_name varchar2(100);
23 l_default_table_name varchar2(100);
24 l_test_flag varchar2(1);
25 l_user_table_id number;
26 l_user_row_id number;
27 l_user_colunm_id number;
28 l_pc_value number;
29 l_low_L varchar2(100);
30 l_high_L varchar2(100);
31 l_low_user_row_id number;
32 l_low_pc varchar2(100);
33 l_high_pc varchar2(100);
34 l_test_L varchar2(100);
35 l_test_H varchar2(100);
36 l_sub_high_band_low varchar2(100);
37 l_high_band_high varchar2(100);
38 l_tax_base number;
39 l_low_range_tax number;
40 l_high_range_tax number;
41 l_total_deduction number;
42 cursor c_get_name(l_table_name VARCHAR2) is
43 select 'Y'
44 from PAY_USER_TABLES
45 where USER_TABLE_NAME = l_table_name
46 and legislation_code = 'NO' ;
47 cursor c_get_table_id(l_table_name VARCHAR2) is
48 select user_table_id
49 from PAY_USER_TABLES
50 where USER_TABLE_NAME = l_table_name
51 and legislation_code = 'NO';
52 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
53 select user_column_id
54 from PAY_USER_COLUMNS
55 where USER_TABLE_ID = l_table_id
56 AND user_column_name = l_freq;
57 cursor c_get_pc_value (l_column_id number , l_date date , l_table_id number , l_ptd_amount varchar2) is
58 select VALUE , user_row_id
59 from pay_user_column_instances_f
60 where user_column_id = l_column_id
61 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
62 AND user_row_id in (select user_row_id from pay_user_rows_f where
63 user_table_id = l_table_id and to_number(l_ptd_amount) between to_number(row_low_range_or_name) and to_number(row_high_range)
64 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE);
65 cursor c_get_row_details (l_row_id number , l_date date) is
66 select ROW_LOW_RANGE_OR_NAME , ROW_HIGH_RANGE
67 from PAY_USER_ROWS_F
68 where USER_ROW_ID = l_row_id
69 AND l_date BETWEEN EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
70 cursor c_get_low_details (l_high_row_id number , l_date date , l_column_id number ) is
71 select VALUE , user_row_id
72 from pay_user_column_instances_f
73 where user_column_id = l_column_id
74 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
75 AND user_row_id <> l_high_row_id;
76 begin
77 l_test_flag := 'N';
78 l_default_table_name := 'Norway_high_rate_table_7100_to_7231';
79 OPEN c_get_name(p_table_name);
80 FETCH c_get_name into l_test_flag;
81 CLOSE c_get_name;
82 IF l_test_flag <> 'Y' THEN
83 l_test_table_name := l_default_table_name;
84 ELSE
85 l_test_table_name := p_table_name;
86 END IF;
87 OPEN c_get_table_id(l_test_table_name);
88 FETCH c_get_table_id into l_user_table_id;
89 CLOSE c_get_table_id;
90 OPEN c_get_column_id(p_freq , l_user_table_id);
91 FETCH c_get_column_id into l_user_colunm_id;
92 CLOSE c_get_column_id;
93 OPEN c_get_pc_value(l_user_colunm_id , p_Date_Earned , l_user_table_id , p_ptd_amount);
94 FETCH c_get_pc_value into l_pc_value , l_user_row_id;
95 CLOSE c_get_pc_value;
96 OPEN c_get_row_details(l_user_row_id , p_Date_Earned );
97 FETCH c_get_row_details into l_test_L , l_test_H;
98 CLOSE c_get_row_details;
99 If l_test_table_name in ('Norway_high_rate_table_7500' ,'Norway_high_rate_table_7350','Norway_high_rate_table_7600','Norway_high_rate_table_7650') then
100 l_test_H := 999999;
101 end if;
102 IF to_number(l_test_H) = to_number('999999') THEN
103 l_high_pc := l_pc_value;
104 If p_freq = '1' and l_test_table_name <> 'Norway_high_rate_table_7100P_to_7231P' then
105 if p_freq = '1' and l_test_table_name = 'Norway_high_rate_table_7500' then
106 l_high_range_tax := (to_number(p_ptd_amount) - to_number('90001')) * to_number('39') / 100;
107 l_low_range_tax := 0;
108 l_high_tax := l_low_range_tax + l_high_range_tax;
109 l_high_rate_tax := to_number('90000') ;
110 l_high_rate_tax := to_number('90000') ;
111 elsif p_freq = '1' and l_test_table_name = 'Norway_high_rate_table_7350' then
112 l_high_range_tax := (to_number(p_ptd_amount) - to_number('90001')) * to_number('47') / 100;
113 l_low_range_tax := 0;
114 l_high_tax := l_low_range_tax + l_high_range_tax;
115 l_high_rate_tax := to_number('90000') ;
116 l_high_rate_tax := to_number('90000') ;
117 elsif p_freq = '1' and l_test_table_name = 'Norway_high_rate_table_7600' then
118 l_high_range_tax := (to_number(p_ptd_amount) - to_number('90001')) * to_number('39') / 100;
119 l_low_range_tax := 0;
120 l_high_tax := l_low_range_tax + l_high_range_tax;
121 l_high_rate_tax := to_number('90000') ;
122 l_high_rate_tax := to_number('90000') ;
123 elsif p_freq = '1' and l_test_table_name = 'Norway_high_rate_table_7650' then
124 l_high_range_tax := (to_number(p_ptd_amount) - to_number('90001')) * to_number('45') / 100;
125 l_low_range_tax := 0;
126 l_high_tax := l_low_range_tax + l_high_range_tax;
127 l_high_rate_tax := to_number('90000') ;
128 l_high_rate_tax := to_number('90000') ;
129 else
130 OPEN c_get_row_details(l_user_row_id, p_Date_Earned );
131 FETCH c_get_row_details into l_sub_high_band_low, l_high_band_high;
132 CLOSE c_get_row_details;
133 l_high_range_tax := (to_number(p_ptd_amount) - to_number(l_sub_high_band_low)+1) * to_number(l_high_pc) / 100;
134 l_low_range_tax := 0;
135 l_high_tax := l_low_range_tax + l_high_range_tax;
136 l_high_rate_tax := to_number(l_sub_high_band_low);
137 end if;
138 elsif p_freq = '7' and l_test_table_name <> 'Norway_high_rate_table_7100_to_7231' then
139 OPEN c_get_row_details(l_user_row_id, p_Date_Earned );
140 FETCH c_get_row_details into l_sub_high_band_low, l_high_band_high;
141 CLOSE c_get_row_details;
142 l_high_range_tax := (to_number(p_ptd_amount) - to_number(l_sub_high_band_low)+1) * to_number(l_high_pc) / 100;
143 l_low_range_tax := 0;
144 l_high_tax := l_low_range_tax + l_high_range_tax;
145 l_high_rate_tax := to_number(l_sub_high_band_low) ;
146 else
147 OPEN c_get_low_details(l_user_row_id,p_Date_Earned,l_user_colunm_id);
148 FETCH c_get_low_details into l_low_pc, l_low_user_row_id;
149 CLOSE c_get_low_details;
150 OPEN c_get_row_details(l_low_user_row_id , p_Date_Earned );
151 FETCH c_get_row_details into l_low_L , l_high_L;
152 CLOSE c_get_row_details;
153 OPEN c_get_row_details(l_user_row_id, p_Date_Earned );
154 FETCH c_get_row_details into l_sub_high_band_low, l_high_band_high;
155 CLOSE c_get_row_details;
156 l_tax_base := to_number(p_ptd_amount) - to_number(l_low_L);
157 l_low_range_tax := ((to_number(l_sub_high_band_low)-1) - to_number(l_low_L)) * to_number(l_low_pc) / 100;
158 l_high_range_tax := (to_number(p_ptd_amount) - to_number(l_sub_high_band_low)+1) * to_number(l_high_pc) / 100;
159 l_high_tax := l_low_range_tax + l_high_range_tax;
160 l_high_rate_tax := l_low_L;
161 end if;
162 ELSE
163 l_low_pc := l_pc_value;
164 l_tax_base := to_number(p_ptd_amount) - to_number(l_test_L);
165 l_high_tax := (to_number(p_ptd_amount) - to_number(l_test_L)) * to_number(l_low_pc) / 100 ;
166 l_high_rate_tax := l_test_L;
167 END IF;
168 p_high_rate_tax := l_high_rate_tax;
169 p_high_tax := l_high_tax;
170 p_high_tax_base := l_high_tax_base;
171 RETURN '1';
172 END get_tax_values_high_rate;
173
174 */
175
176 /* commented above function get_tax_values_high_rate and creating new function to remove
177 hard-coded user table names for high rate tax tables. */
178
179 ---------------------------------------------------------------------------------------------------------
180 FUNCTION get_tax_values_high_rate (
181 p_business_group_id IN number
182 ,p_Date_Earned IN DATE
183 ,p_table_name IN VARCHAR2
184 ,p_freq IN VARCHAR2
185 ,p_ptd_amount IN VARCHAR2
186 ,p_high_tax OUT NOCOPY VARCHAR2
187 ,p_high_tax_base OUT NOCOPY VARCHAR2
188 ,p_high_rate_tax OUT NOCOPY VARCHAR2) return varchar2 IS
189
190
191 l_high_tax number;
192 l_high_tax_base number;
193 l_high_rate_tax number;
194 l_test_table_name varchar2(100);
195 l_default_table_name varchar2(100);
196 l_test_flag varchar2(10);
197 l_user_table_id number;
198 l_user_row_id number;
199 l_user_colunm_id number;
200 l_pc_value number;
201 l_low_L varchar2(100);
202 l_high_L varchar2(100);
203 l_low_user_row_id number;
204 l_low_pc varchar2(100);
205 l_high_pc varchar2(100);
206 l_test_L varchar2(100);
207 l_test_H varchar2(100);
208 l_sub_high_band_low varchar2(100);
209 l_high_band_high varchar2(100);
210 l_tax_base number;
211 l_low_range_tax number;
212 l_high_range_tax number;
213 l_total_deduction number;
214
215
216 cursor c_get_name(l_table_name VARCHAR2) is
217 select 'Y'
218 from PAY_USER_TABLES
219 where USER_TABLE_NAME = l_table_name
220 and legislation_code = 'NO' ;
221
222
223 cursor c_get_table_id(l_table_name VARCHAR2) is
224 select user_table_id
225 from PAY_USER_TABLES
226 where USER_TABLE_NAME = l_table_name
227 and legislation_code = 'NO';
228
229
230 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
231 select user_column_id
232 from PAY_USER_COLUMNS
233 where USER_TABLE_ID = l_table_id
234 AND user_column_name = l_freq;
235
236
237 cursor c_get_pc_value (l_column_id number , l_date date , l_table_id number , l_ptd_amount varchar2) is
238 select VALUE , user_row_id
239 from pay_user_column_instances_f
240 where user_column_id = l_column_id
241 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
242 AND user_row_id in (select user_row_id
243 from pay_user_rows_f
244 where user_table_id = l_table_id
245 and to_number(l_ptd_amount) between to_number(row_low_range_or_name) and to_number(row_high_range)
246 and l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE);
247
248
249 cursor c_get_row_details (l_row_id number , l_date date) is
250 select ROW_LOW_RANGE_OR_NAME , ROW_HIGH_RANGE
251 from PAY_USER_ROWS_F
252 where USER_ROW_ID = l_row_id
253 AND l_date BETWEEN EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
254
255
256 cursor c_get_low_details (l_high_row_id number , l_date date , l_column_id number ) is
257 select VALUE , user_row_id
258 from pay_user_column_instances_f
259 where user_column_id = l_column_id
260 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
261 AND user_row_id <> l_high_row_id;
262
263 begin
264
265 l_test_flag := 'N';
266 l_default_table_name := 'Norway_high_rate_table_7100_to_7231';
267
268 OPEN c_get_name(p_table_name);
269 FETCH c_get_name into l_test_flag;
270 CLOSE c_get_name;
271
272 IF l_test_flag <> 'Y' THEN
273 l_test_table_name := l_default_table_name;
274 ELSE
275 l_test_table_name := p_table_name;
276 END IF;
277
278 OPEN c_get_table_id(l_test_table_name);
279 FETCH c_get_table_id into l_user_table_id;
280 CLOSE c_get_table_id;
281
282 OPEN c_get_column_id(p_freq , l_user_table_id);
283 FETCH c_get_column_id into l_user_colunm_id;
284 CLOSE c_get_column_id;
285
286 OPEN c_get_pc_value(l_user_colunm_id , p_Date_Earned , l_user_table_id , p_ptd_amount);
287 FETCH c_get_pc_value into l_pc_value , l_user_row_id;
288 CLOSE c_get_pc_value;
289
290 OPEN c_get_row_details(l_user_row_id, p_Date_Earned );
291 FETCH c_get_row_details into l_test_L , l_test_H;
292 CLOSE c_get_row_details;
293
294 IF to_number(l_test_H) = to_number('999999') THEN
295
296 l_high_pc := l_pc_value;
297
298 /* since l_test_H = 999999, we are in the high band; find out if there is a low band also */
299
300 OPEN c_get_low_details(l_user_row_id,p_Date_Earned,l_user_colunm_id);
301 FETCH c_get_low_details into l_low_pc, l_low_user_row_id;
302 CLOSE c_get_low_details;
303
304 IF (l_low_user_row_id IS NOT NULL) THEN
305
306 /* low band exists */
307 /* get row limits of the low band */
308
309 OPEN c_get_row_details(l_low_user_row_id , p_Date_Earned );
310 FETCH c_get_row_details into l_low_L , l_high_L;
311 CLOSE c_get_row_details;
312
313 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_low_L) - 1 );
314 l_low_range_tax := ( to_number(l_test_L) - to_number(l_low_L) ) * to_number(l_low_pc) / 100 ;
315 l_high_range_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_high_pc) / 100 ;
316 l_high_tax := l_low_range_tax + l_high_range_tax ;
317 l_high_rate_tax := to_char(( to_number(l_low_L) - 1 )) ;
318
319 ELSE
320 /* low band does not exists */
321 /* do only high band calculation */
322 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_low_L) - 1 );
323 l_low_range_tax := 0 ;
324 l_high_range_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_high_pc) / 100 ;
325 l_high_tax := l_low_range_tax + l_high_range_tax ;
326 l_high_rate_tax := to_char(( to_number(l_low_L) - 1 )) ;
327 END IF;
328
329 ELSE
330 /* we are already in the low band , there is no high band; so calculate only low band tax */
331 l_low_pc := l_pc_value;
332 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 );
333 l_high_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_low_pc) / 100 ;
334 l_high_rate_tax := to_char(( to_number(l_test_L) - 1 )) ;
335
336 END IF;
337
338 p_high_rate_tax := l_high_rate_tax;
339 p_high_tax := l_high_tax;
340 p_high_tax_base := l_high_tax_base;
341
342 RETURN '1';
343
344 END get_tax_values_high_rate;
345
346 ---------------------------------------------------------------------------------------------------------
347
348 --
349 --
350 FUNCTION get_start_range (
351 p_Date_Earned IN DATE
352 ,p_business_group_id IN number
353 ,p_table_name IN VARCHAR2
354 ,p_freq IN VARCHAR2
355 ,p_ptd_amount IN VARCHAR2
356 ,p_start_range OUT NOCOPY VARCHAR2) return varchar2 IS
357
358 l_test_flag varchar2(1);
359 l_user_table_id number;
360 l_user_row_id number;
361 l_user_colunm_id number;
362 l_test_table_name varchar2(100);
363 l_default_table_name varchar2(100);
364 l_start_value varchar2(100);
365 l_column_name varchar2(100);
366
367 cursor c_get_name(l_table_name VARCHAR2) is
368 select 'Y'
369 from PAY_USER_TABLES
370 where USER_TABLE_NAME = l_table_name
371 and legislation_code = 'NO';
372
373 cursor c_get_table_id(l_table_name VARCHAR2) is
374 select user_table_id
375 from PAY_USER_TABLES
376 where USER_TABLE_NAME = l_table_name
377 and legislation_code = 'NO';
378
379 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
380 select user_column_id
381 from PAY_USER_COLUMNS
382 where USER_TABLE_ID = l_table_id
383 AND user_column_name = l_freq;
384
385 /*
389 where user_column_id = l_column_id
386 cursor c_get_start_value (l_table_id number , l_column_id number , l_ptd_amount varchar2 , l_date date) is
387 select value
388 from pay_user_column_instances_f
390 AND user_row_id in (select user_row_id from pay_user_rows_f
391 where user_table_id = l_table_id AND l_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
392 AND to_number(l_ptd_amount) between to_number(ROW_LOW_RANGE_OR_NAME) and to_number(ROW_HIGH_RANGE));
393 */
394
395 /* Modified cursor c_get_start_value to include effective date check */
396
397 cursor c_get_start_value (l_table_id number , l_column_id number , l_ptd_amount varchar2 , l_date date) is
398 select value
399 from pay_user_column_instances_f
400 where user_column_id = l_column_id
401 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
402 AND user_row_id in (select user_row_id from pay_user_rows_f
403 where user_table_id = l_table_id AND l_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
404 AND to_number(l_ptd_amount) between to_number(ROW_LOW_RANGE_OR_NAME) and to_number(ROW_HIGH_RANGE));
405
406
407 begin
408
409 l_test_flag := 'N';
410 l_default_table_name := 'Norway_high_rate_table_7100_to_7231';
411 l_column_name := 'START_RANGE_' || p_freq;
412
413 OPEN c_get_name(p_table_name);
414 FETCH c_get_name into l_test_flag;
415 CLOSE c_get_name;
416
417 /* IF l_test_flag = 'Y' THEN
418 l_test_table_name := p_table_name;
419 ELSE
420 l_test_table_name := l_default_table_name;
421 END IF;
422 */
423
424 If l_test_flag <>'Y' then
425 l_test_table_name := l_default_table_name;
426 Else
427 l_test_table_name := p_table_name;
428 end if;
429
430 OPEN c_get_table_id(l_test_table_name);
431 FETCH c_get_table_id into l_user_table_id;
432 CLOSE c_get_table_id;
433
434 OPEN c_get_column_id(l_column_name , l_user_table_id);
435 FETCH c_get_column_id into l_user_colunm_id;
436 CLOSE c_get_column_id;
437
438 OPEN c_get_start_value(l_user_table_id , l_user_colunm_id , p_ptd_amount , p_Date_Earned );
439 FETCH c_get_start_value into l_start_value;
440 CLOSE c_get_start_value;
441
442 p_start_range := l_start_value;
443
444 /*
445 If p_freq = '1' and l_test_table_name in ('Norway_high_rate_table_7350','Norway_high_rate_table_7500','Norway_high_rate_table_7600','Norway_high_rate_table_7650') then
446 p_start_range := '90000';
447 end if;
448 */
449
450 RETURN '1';
451
452 END get_start_range;
453
454 ---------------------------------------------------------------------------------------------------------
455
456 /* Commented to use the Tax table upload logic on 19th May 2005
457 --
458 FUNCTION get_normal_tax (
459 p_Date_Earned IN DATE
460 ,p_business_group_id IN number
461 ,p_table_name IN VARCHAR2
462 ,p_freq IN VARCHAR2
463 ,p_type IN VARCHAR2
464 ,p_ptd_amount IN VARCHAR2
465 ,p_normal_tax OUT NOCOPY VARCHAR2) return varchar2 IS
466 l_high_tax number;
467 l_high_tax_base number;
468 l_high_rate_tax number;
469 l_test_table_name varchar2(100);
470 l_default_table_name varchar2(100);
471 l_test_flag varchar2(1);
472 l_user_table_id number;
473 l_user_row_id number;
474 l_user_colunm_id number;
475 l_pc_value number;
476 l_normal_tax varchar2(100);
477 l_tax_value varchar2(100);
478 cursor c_get_name(l_table_name VARCHAR2) is
479 select 'Y'
480 from PAY_USER_TABLES
481 where USER_TABLE_NAME = l_table_name
482 and legislation_code = 'NO';
483 cursor c_get_table_id(l_table_name VARCHAR2) is
484 select user_table_id
485 from PAY_USER_TABLES
486 where USER_TABLE_NAME = l_table_name
487 and legislation_code = 'NO';
488 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
489 select user_column_id
490 from PAY_USER_COLUMNS
491 where USER_TABLE_ID = l_table_id
492 AND user_column_name = l_freq;
493 cursor c_get_tax_value (l_column_id number , l_date date , l_table_id number , l_ptd_amount varchar2) is
494 select VALUE
495 from pay_user_column_instances_f
496 where user_column_id = l_column_id
497 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
498 AND user_row_id in (select user_row_id from pay_user_rows_f where
499 user_table_id = l_table_id and to_number(l_ptd_amount) between to_number(row_low_range_or_name) and to_number(row_high_range)
500 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE);
501 begin
502 l_test_flag := 'N';
503 l_default_table_name := 'NORWAY_NORMAL_TAX_TABLE_7100_'||p_type;
504 OPEN c_get_name(p_table_name);
505 FETCH c_get_name into l_test_flag;
506 CLOSE c_get_name;
507 IF l_test_flag <> 'Y' THEN
508 l_test_table_name := l_default_table_name;
509 ELSE
510 l_test_table_name := p_table_name;
511 END IF;
512 OPEN c_get_table_id(l_test_table_name);
513 FETCH c_get_table_id into l_user_table_id;
514 CLOSE c_get_table_id;
515 OPEN c_get_column_id(p_freq , l_user_table_id);
516 FETCH c_get_column_id into l_user_colunm_id;
517 CLOSE c_get_column_id;
521 If l_tax_value is null then
518 OPEN c_get_tax_value(l_user_colunm_id , p_Date_Earned , l_user_table_id , p_ptd_amount);
519 FETCH c_get_tax_value into l_tax_value;
520 CLOSE c_get_tax_value;
522 l_tax_value := '0';
523 End if;
524 p_normal_tax := l_tax_value;
525 RETURN '1';
526 END get_normal_tax;
527 --
528 End of commented on 19th May 2005.*/
529 --
530 FUNCTION get_normal_tax ( p_Date_Earned IN DATE
531 ,p_business_group_id IN number
532 ,p_table_name IN VARCHAR2
533 ,p_freq IN VARCHAR2
534 ,p_type IN VARCHAR2
535 ,p_ptd_amount IN VARCHAR2
536 ,p_normal_tax OUT NOCOPY VARCHAR2) return varchar2 IS
537 l_normal_tax number;
538
539 /*
540 cursor csr_get_normal_tax (l_table_number varchar2 , l_freq varchar2 , l_type varchar2 , l_bg_id number ,l_date date,l_amount varchar2) is
541 Select Amount1
542 from PAY_RANGE_TABLES_F PRTF , PAY_RANGES_F PRF
543 where PRTF.range_table_id = PRF.range_table_id
544 and PRTF.range_table_number = TO_NUMBER(l_table_number)
545 and PRTF.period_frequency = l_freq
546 and PRTF.earnings_type = l_type
547 and PRTF.business_group_id = l_bg_id
548 and PRTF.legislation_code = 'NO'
549 and l_date between PRTF.effective_start_date and PRTF.effective_end_date
550 and to_number(l_amount) between PRF.low_band and PRF.high_band
551 and l_date between PRF.effective_start_date and PRF.effective_end_date;
552 */
553
554 -- Bug Fix 5533206, Norwegian Tax Tables will now be uploaded without any Business Group
555 -- Modifying cursor to check for business_gorup_id IS NULL
556
557 cursor csr_get_normal_tax (l_table_number varchar2 , l_freq varchar2 , l_type varchar2 ,l_date date,l_amount varchar2) is
558 Select Amount1
559 from PAY_RANGE_TABLES_F PRTF , PAY_RANGES_F PRF
560 where PRTF.range_table_id = PRF.range_table_id
561 and PRTF.range_table_number = TO_NUMBER(l_table_number)
562 and PRTF.period_frequency = l_freq
563 and PRTF.earnings_type = l_type
564 and PRTF.business_group_id IS NULL
565 and PRTF.legislation_code = 'NO'
566 and l_date between PRTF.effective_start_date and PRTF.effective_end_date
567 and to_number(l_amount) between PRF.low_band and PRF.high_band
568 and l_date between PRF.effective_start_date and PRF.effective_end_date;
569
570
571
572 begin
573
574 -- open csr_get_normal_tax (p_table_name,p_freq,p_type,p_business_group_id,p_date_earned,p_ptd_amount);
575
576 -- Bug Fix 5533206, Norwegian Tax Tables will now be uploaded without any Business Group
577 -- Modifying cursor call
578
579 open csr_get_normal_tax (p_table_name,p_freq,p_type,p_date_earned,p_ptd_amount);
580 fetch csr_get_normal_tax into l_normal_tax;
581 close csr_get_normal_tax;
582
583 If l_normal_tax is null then
584 l_normal_tax := 0;
585 End if;
586 p_normal_tax := to_char(l_normal_tax);
587 RETURN '1';
588 END get_normal_tax;
589 --
590 FUNCTION get_reduced_rule (
591 p_payroll_action_id IN number
592 ,p_payroll_id IN VARCHAR2
593 ,p_reduced_rule OUT NOCOPY VARCHAR2) return varchar2 IS
594 l_reduced_rule varchar2(80);
595 cursor c_get_rule (l_pact_id number , l_payroll_id number) IS
596 SELECT ptp.prd_information1
597 FROM per_time_periods ptp , pay_payroll_actions ppa
598 WHERE ppa.payroll_action_id = l_pact_id
599 AND ppa.action_type in ('Q','R')
600 AND ppa.time_period_id = ptp.time_period_id
601 AND ptp.payroll_id = l_payroll_id;
602 begin
603 OPEN c_get_rule(p_payroll_action_id , p_payroll_id);
604 FETCH c_get_rule into l_reduced_rule;
605 CLOSE c_get_rule;
606 if l_reduced_rule is null then
607 l_reduced_rule := 'N';
608 end if;
609 p_reduced_rule := l_reduced_rule;
610 RETURN '1';
611 END get_reduced_rule;
612 --
613 -- Function GET_MESSAGE
614 -- This function is used to obtain a message.
615 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
616 -- If you want to set the value of a token called ELEMENT to Tax
617 -- the token parameter would be 'ELEMENT:Tax.'
618 ------------------------------------------------------------------------
619 function get_message
620 (p_product in varchar2
621 ,p_message_name in varchar2
622 ,p_token1 in varchar2 default null
623 ,p_token2 in varchar2 default null
624 ,p_token3 in varchar2 default null) return varchar2
625 is
626 l_message varchar2(2000);
627 l_token_name varchar2(20);
628 l_token_value varchar2(80);
629 l_colon_position number;
630 l_proc varchar2(72) ;
631 --
632 begin
633 --
634 hr_utility.set_location('Entered '||l_proc,5);
635 hr_utility.set_location('. Message Name: '||p_message_name,40);
636 fnd_message.set_name(p_product, p_message_name);
637 if p_token1 is not null then
638 /* Obtain token 1 name and value */
639 l_colon_position := instr(p_token1,':');
640 l_token_name := substr(p_token1,1,l_colon_position-1);
641 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
642 fnd_message.set_token(l_token_name, l_token_value);
643 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
644 end if;
648 l_token_name := substr(p_token2,1,l_colon_position-1);
645 if p_token2 is not null then
646 /* Obtain token 2 name and value */
647 l_colon_position := instr(p_token2,':');
649 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
650 fnd_message.set_token(l_token_name, l_token_value);
651 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
652 end if;
653 if p_token3 is not null then
654 /* Obtain token 3 name and value */
655 l_colon_position := instr(p_token3,':');
656 l_token_name := substr(p_token3,1,l_colon_position-1);
657 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
658 fnd_message.set_token(l_token_name, l_token_value);
659 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
660 end if;
661 l_message := substr(fnd_message.get,1,254);
662 hr_utility.set_location('leaving '||l_proc,100);
663 return l_message;
664 end get_message;
665 ------------------------------------------------------------------------
666
667 -- Modified function get_prim_tax_card for Legislative changes 2007.
668
669 /*
670 function get_prim_tax_card (
671 p_assignment_id IN NUMBER
672 ,p_date_earned IN DATE
673 ,p_tax_card_type OUT NOCOPY VARCHAR2
674 ,p_tax_municipality OUT NOCOPY VARCHAR2
675 ,p_tax_percentage OUT NOCOPY VARCHAR2
676 ,p_tax_table_number OUT NOCOPY VARCHAR2
677 ,p_tax_table_type OUT NOCOPY VARCHAR2
678 ,p_tft_value OUT NOCOPY VARCHAR2
679 ,p_tax_card_msg OUT NOCOPY VARCHAR2 ) return varchar2
680 */
681
682 function get_prim_tax_card (
683 p_assignment_id IN NUMBER
684 ,p_date_earned IN DATE
685 ,p_assignment_action_id IN NUMBER
686 ,p_payroll_action_id IN NUMBER
687 ,p_tax_card_type OUT NOCOPY VARCHAR2
688 ,p_tax_municipality OUT NOCOPY VARCHAR2
689 ,p_tax_percentage OUT NOCOPY VARCHAR2
690 ,p_tax_table_number OUT NOCOPY VARCHAR2
691 ,p_tax_table_type OUT NOCOPY VARCHAR2
692 ,p_tft_value OUT NOCOPY VARCHAR2
693 ,p_tax_card_msg OUT NOCOPY VARCHAR2 ) return varchar2
694 is
695 l_tax_card_type varchar2(80);
696 l_tax_municipality varchar2(80);
697 l_tax_municipality_num number;
698 l_tax_percentage varchar2(80);
699 l_tax_percentage_num number := null;
700 l_tax_table_number varchar2(80) ;
701 l_tax_table_type varchar2(80) ;
702 l_tft_value varchar2(80) ;
703 l_tft_value_num number(13,2);
704 l_main_person_id number;
705 l_prim_asg_id number;
706
707 -- BUG 4774784 fix start
708
709 l_eeid NUMBER;
710 l_full_name VARCHAR2(240);
711 l_emp_num VARCHAR2(30);
712 l_no_tax_card_msg VARCHAR2(1000);
713 l_return_val VARCHAR2(20);
714
715 -- BUG 4774784 fix end
716
717 CURSOR get_person_id (l_assignment_id number,l_date_earned date) is
718 SELECT person_id
719 FROM per_all_assignments_f paf
720 WHERE paf.assignment_id = l_assignment_id
721 AND l_date_earned between paf.effective_start_date and paf.effective_end_date;
722
723 CURSOR get_prim_assignment_id( l_person_id number,l_date date) is
724 SELECT assignment_id
725 FROM per_all_assignments_f paf
726 WHERE paf.person_id = l_person_id
727 AND primary_flag = 'Y'
728 AND l_date between paf.effective_start_date and paf.effective_end_date;
729
730 CURSOR get_tax_card_details( l_assignment_id number,l_date date , l_input_name varchar2) is
731 SELECT screen_entry_value
732 FROM pay_element_entry_values_f eev,
733 pay_element_entries_f ee,
734 pay_element_types_f et,
735 pay_input_values_f iv
736 WHERE eev.element_entry_id = ee.element_entry_id
737 And l_date between eev.effective_start_date and ee.effective_end_date
738 And ee.assignment_id = l_assignment_id
739 AND l_date between ee.effective_start_date and ee.effective_end_date
740 And et.element_name = 'Tax Card'
741 And et.legislation_code = 'NO'
742 And l_date between et.effective_start_date and et.effective_end_date
743 And et.element_type_id = iv.element_type_id
744 And iv.name = l_input_name
745 And l_date between iv.effective_start_date and iv.effective_end_date
746 And eev.input_value_id = iv.input_value_id;
747
748 -- BUG 4774784 fix start
749
750 -- cursor to get employee full name and employee number
751 CURSOR get_person_details (l_person_id number,l_date_earned date) is
752 SELECT full_name , employee_number
753 FROM per_all_people_f
754 WHERE person_id = l_person_id
755 AND l_date_earned between effective_start_date and effective_end_date ;
756
757 -- cursor to check if element TAX CARD exists on the primary assignment of the employee
758 CURSOR csr_chk_tax_card( l_assignment_id number,l_date date ) is
759 SELECT ee.ELEMENT_ENTRY_ID
760 FROM pay_element_entries_f ee,
761 pay_element_types_f et
762 WHERE et.element_name = 'Tax Card'
763 And et.legislation_code = 'NO'
764 And l_date between et.effective_start_date and et.effective_end_date
765 And et.element_type_id = ee.element_type_id
766 AND ee.assignment_id = l_assignment_id
767 AND l_date between ee.effective_start_date and ee.effective_end_date ;
768
769 -- BUG 4774784 fix end
770
771 -------- Adding new cursors for Legislative changes 2007
772
773 -- Legislative changes 2007 : cursor to get the tax municipality for Ambulatory operations
774
775 CURSOR csr_get_amb_op_tax_mun (p_asg_id NUMBER , pay_act_id NUMBER ) IS
776 SELECT distinct eev.screen_entry_value Tax_Municipality
777 FROM pay_element_entries_f pee
778 ,pay_element_entry_values_f eev
779 ,pay_input_values_f piv
780 ,pay_element_types_f pet
781 ,pay_payroll_actions ppa
782 WHERE ppa.payroll_action_id = pay_act_id
783 AND pee.assignment_id = p_asg_id
784 AND pet.element_name = 'Employer Contribution Information'
785 AND pet.legislation_code = 'NO'
786 AND piv.name = 'Tax Municipality'
787 AND pee.element_entry_id = eev.element_entry_id
788 AND eev.input_value_id + 0 = piv.input_value_id
789 AND piv.element_type_id = pet.element_type_id
790 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
791 AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
792 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
793 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date ;
794
795
796 -- Legislative changes 2007 : cursor to fetch the Tax Municipality at Local Unit
797
798 CURSOR csr_get_lu_tax_mun (p_assignment_action_id NUMBER) IS
799 SELECT ORG_INFORMATION6 lu_tax_mun
800 FROM pay_assignment_actions assact ,
801 per_all_assignments_f paa ,
802 pay_payroll_actions ppa ,
803 hr_soft_coding_keyflex scl ,
804 hr_organization_information hoi
808 AND ppa.effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
805 WHERE assact.assignment_action_id = p_assignment_action_id
806 AND ppa.payroll_action_id = assact.payroll_action_id
807 AND paa.assignment_id = assact.assignment_id
809 AND paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
810 AND hoi.organization_id = scl.segment2
811 AND hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS' ;
812
813 -------- End Adding new cursors for Legislative changes 2007
814
815 --
816 begin
817
818 open get_person_id (p_assignment_id,p_date_earned);
819 fetch get_person_id into l_main_person_id;
820 close get_person_id;
821
822 open get_prim_assignment_id (l_main_person_id,p_date_earned);
823 fetch get_prim_assignment_id into l_prim_asg_id;
824 close get_prim_assignment_id;
825
826
827 -- BUG 4774784 fix start
828
829 OPEN csr_chk_tax_card( l_prim_asg_id ,p_date_earned );
830 FETCH csr_chk_tax_card into l_eeid ;
831
832 IF csr_chk_tax_card%NOTFOUND
833 THEN
834 -- TAX CARD element not attached to emp's primary assignment
835 OPEN get_person_details (l_main_person_id ,p_date_earned ) ;
836 FETCH get_person_details into l_full_name , l_emp_num ;
837 CLOSE get_person_details;
838
839 hr_utility.set_message (801, 'PAY_376863_NO_TAX_CARD_ELE_ERR');
840 hr_utility.set_message_token (801, 'EMP_NAME', l_full_name);
841 hr_utility.set_message_token (801, 'EMP_NUM', l_emp_num);
842
843 l_no_tax_card_msg := hr_utility.get_message ;
844
845 -- Put the meassage in the log file
846 fnd_file.put_line (fnd_file.LOG, l_no_tax_card_msg );
847
848 l_return_val := '0' ;
849
850 ELSE
851 -- TAX CARD element found
852 l_return_val := '1' ;
853 l_no_tax_card_msg := '';
854
855
856 -- BUG 4774784 fix end
857
858
859 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Municipality');
860 fetch get_tax_card_details into l_tax_municipality;
861 close get_tax_card_details;
862
863 -- Changes for Legislative changes 2007
864
865 -- the above l_tax_municipality is the Tax Municipality from Tax Card
866 -- the value for Tax Municipality returned from this procedure will be used for
867 -- JURISRICTION_CODE context which will be used in Employer Contribution calculations.
868
869 -- For calculations before 2007, the Tax Municipality for this will be fetched from
870 -- the input value of the element 'Tax Card'.
871 -- For calculations in and after 2007, the Tax Municipality for this will be fetched from
872 -- the input value of the element 'Employer Contribution Information' (Ambulatory Operations)
873 -- or the Tax Municipality at the Local Unit attached to the assignment.
874
875
876 IF (to_number(to_char(p_date_earned,'RRRR')) >= 2007)
877
878 THEN
879 OPEN csr_get_amb_op_tax_mun (p_assignment_id , p_payroll_action_id );
880 FETCH csr_get_amb_op_tax_mun INTO l_tax_municipality;
881 CLOSE csr_get_amb_op_tax_mun;
882
883 IF ( l_tax_municipality IS NULL )
884 THEN
885 OPEN csr_get_lu_tax_mun ( p_assignment_action_id );
886 FETCH csr_get_lu_tax_mun INTO l_tax_municipality;
887 CLOSE csr_get_lu_tax_mun ;
888
889 END IF ;
890
891 END IF ;
892
893 -- End Changes for Legislative changes 2007
894
895 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Card Type');
896 fetch get_tax_card_details into l_tax_card_type;
897 close get_tax_card_details;
898
899 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Percentage');
900 fetch get_tax_card_details into l_tax_percentage;
901 close get_tax_card_details;
902
903 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Table Number');
904 fetch get_tax_card_details into l_tax_table_number;
905 close get_tax_card_details;
906
907 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Table Type');
908 fetch get_tax_card_details into l_tax_table_type;
909 close get_tax_card_details;
910
911 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Free Threshold');
912 fetch get_tax_card_details into l_tft_value;
913 close get_tax_card_details;
914
915 END IF;
916
917 CLOSE csr_chk_tax_card ;
918
919
920 If l_tax_card_type is null then
921 l_tax_card_type := 'PB';
922 End If;
923
924 If l_tax_municipality is null then
925 l_tax_municipality := 0;
926 End If;
927
928 If l_tax_percentage is null then
929 l_tax_percentage := '50';
930 End If;
931
932 If l_tax_table_number is null then
933 l_tax_table_number := '9999';
934 End If;
935
936 If l_tax_table_type is null then
937 l_tax_table_type := 'O';
938 End If;
939
940 If l_tft_value is null then
941 l_tft_value := '0';
942 End If;
943
944 p_tax_card_type := l_tax_card_type;
945 p_tax_municipality := l_tax_municipality;
946 p_tax_percentage := l_tax_percentage;
947 p_tax_table_number := l_tax_table_number;
948 p_tax_table_type := l_tax_table_type;
949 p_tft_value := l_tft_value;
950 p_tax_card_msg := l_no_tax_card_msg ;
951
952 -- RETURN '1';
953 RETURN l_return_val ;
954
955 end get_prim_tax_card;
956
957 ------------------------------------------------------------------------
958
959 FUNCTION get_pay_holiday_rule ( p_payroll_action_id IN NUMBER
960 ,p_payroll_id IN VARCHAR2
961 ,p_pay_holiday_rule OUT nocopy VARCHAR2)
962
963 RETURN VARCHAR2 IS
964
965 l_pay_holiday_rule VARCHAR2(80);
966
967 CURSOR csr_get_pay_holiday_rule (l_pact_id NUMBER, l_payroll_id NUMBER) IS
968 SELECT ptp.prd_information2
969 FROM per_time_periods ptp,
970 pay_payroll_actions ppa
971 WHERE ppa.payroll_action_id = l_pact_id
972 AND ppa.action_type IN('Q','R')
973 AND ppa.time_period_id = ptp.time_period_id
974 AND ptp.payroll_id = l_payroll_id ;
975
976 BEGIN
977
978 OPEN csr_get_pay_holiday_rule (p_payroll_action_id , p_payroll_id) ;
979 FETCH csr_get_pay_holiday_rule INTO l_pay_holiday_rule ;
980 CLOSE csr_get_pay_holiday_rule ;
981
982 IF l_pay_holiday_rule IS NULL THEN
983 l_pay_holiday_rule := 'N';
984 END IF;
985
986 p_pay_holiday_rule := l_pay_holiday_rule;
987 RETURN '1';
988
989 END get_pay_holiday_rule ;
990
991
992 ------------------------------------------------------------------------
993 END PAY_NO_HIGH_RATE_TAX;
[Home] [Help]