1 package body PAY_NO_HIGH_RATE_TAX as
2 /* $Header: pynohtax.pkb 120.10.12020000.6 2013/02/01 10:04:40 dakhuran ship $ */
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 -- 2009 Legislative changes renamed user table from 7231 to 7233
267 l_default_table_name := 'Norway_high_rate_table_7100_to_7233';
268
269 OPEN c_get_name(p_table_name);
270 FETCH c_get_name into l_test_flag;
271 CLOSE c_get_name;
272
273 IF l_test_flag <> 'Y' THEN
274 l_test_table_name := l_default_table_name;
275 ELSE
276 l_test_table_name := p_table_name;
277 END IF;
278
279 OPEN c_get_table_id(l_test_table_name);
280 FETCH c_get_table_id into l_user_table_id;
281 CLOSE c_get_table_id;
282
283 OPEN c_get_column_id(p_freq , l_user_table_id);
284 FETCH c_get_column_id into l_user_colunm_id;
285 CLOSE c_get_column_id;
286
287 OPEN c_get_pc_value(l_user_colunm_id , p_Date_Earned , l_user_table_id , p_ptd_amount);
288 FETCH c_get_pc_value into l_pc_value , l_user_row_id;
289 CLOSE c_get_pc_value;
290
291 OPEN c_get_row_details(l_user_row_id, p_Date_Earned );
292 FETCH c_get_row_details into l_test_L , l_test_H;
293 CLOSE c_get_row_details;
294
295 IF to_number(l_test_H) = to_number('999999') THEN
296
297 l_high_pc := l_pc_value;
298
299 /* since l_test_H = 999999, we are in the high band; find out if there is a low band also */
300
301 OPEN c_get_low_details(l_user_row_id,p_Date_Earned,l_user_colunm_id);
302 FETCH c_get_low_details into l_low_pc, l_low_user_row_id;
303 CLOSE c_get_low_details;
304
305 IF (l_low_user_row_id IS NOT NULL) THEN
306
307 /* low band exists */
308 /* get row limits of the low band */
309
310 OPEN c_get_row_details(l_low_user_row_id , p_Date_Earned );
311 FETCH c_get_row_details into l_low_L , l_high_L;
312 CLOSE c_get_row_details;
313
314 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_low_L) - 1 );
315 l_low_range_tax := ( to_number(l_test_L) - to_number(l_low_L) ) * to_number(l_low_pc) / 100 ;
316 l_high_range_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_high_pc) / 100 ;
317 l_high_tax := l_low_range_tax + l_high_range_tax ;
318 l_high_rate_tax := to_char(( to_number(l_low_L) - 1 )) ;
319
320 ELSE
321 /* low band does not exists */
322 /* do only high band calculation */
323 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_low_L) - 1 );
324 l_low_range_tax := 0 ;
325 l_high_range_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_high_pc) / 100 ;
326 l_high_tax := l_low_range_tax + l_high_range_tax ;
327 l_high_rate_tax := to_char(( to_number(l_low_L) - 1 )) ;
328 END IF;
329
330 ELSE
331 /* we are already in the low band , there is no high band; so calculate only low band tax */
332 l_low_pc := l_pc_value;
333 l_high_tax_base := to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 );
334 l_high_tax := ( to_number(p_ptd_amount) - ( to_number(l_test_L) - 1 ) ) * to_number(l_low_pc) / 100 ;
335 l_high_rate_tax := to_char(( to_number(l_test_L) - 1 )) ;
336
337 END IF;
338
339 p_high_rate_tax := l_high_rate_tax;
340 p_high_tax := l_high_tax;
341 p_high_tax_base := l_high_tax_base;
342
343 RETURN '1';
344
345 END get_tax_values_high_rate;
346
347 ---------------------------------------------------------------------------------------------------------
348
349 --
350 --
351 FUNCTION get_start_range (
352 p_Date_Earned IN DATE
353 ,p_business_group_id IN number
354 ,p_table_name IN VARCHAR2
355 ,p_freq IN VARCHAR2
356 ,p_ptd_amount IN VARCHAR2
357 ,p_start_range OUT NOCOPY VARCHAR2) return varchar2 IS
358
359 l_test_flag varchar2(1);
360 l_user_table_id number;
361 l_user_row_id number;
362 l_user_colunm_id number;
363 l_test_table_name varchar2(100);
364 l_default_table_name varchar2(100);
365 l_start_value varchar2(100);
366 l_column_name varchar2(100);
367
368 cursor c_get_name(l_table_name VARCHAR2) is
369 select 'Y'
370 from PAY_USER_TABLES
371 where USER_TABLE_NAME = l_table_name
372 and legislation_code = 'NO';
373
374 cursor c_get_table_id(l_table_name VARCHAR2) is
375 select user_table_id
376 from PAY_USER_TABLES
377 where USER_TABLE_NAME = l_table_name
378 and legislation_code = 'NO';
379
380 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
381 select user_column_id
382 from PAY_USER_COLUMNS
383 where USER_TABLE_ID = l_table_id
384 AND user_column_name = l_freq;
385
386 /*
387 cursor c_get_start_value (l_table_id number , l_column_id number , l_ptd_amount varchar2 , l_date date) is
388 select value
389 from pay_user_column_instances_f
390 where user_column_id = l_column_id
391 AND user_row_id in (select user_row_id from pay_user_rows_f
392 where user_table_id = l_table_id AND l_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
393 AND to_number(l_ptd_amount) between to_number(ROW_LOW_RANGE_OR_NAME) and to_number(ROW_HIGH_RANGE));
394 */
395
396 /* Modified cursor c_get_start_value to include effective date check */
397
398 cursor c_get_start_value (l_table_id number , l_column_id number , l_ptd_amount varchar2 , l_date date) is
399 select value
400 from pay_user_column_instances_f
401 where user_column_id = l_column_id
402 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
403 AND user_row_id in (select user_row_id from pay_user_rows_f
404 where user_table_id = l_table_id AND l_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
405 AND to_number(l_ptd_amount) between to_number(ROW_LOW_RANGE_OR_NAME) and to_number(ROW_HIGH_RANGE));
406
407
408 begin
409
410 l_test_flag := 'N';
411 -- 2009 Legislative changes renamed user table from 7231 to 7233
412 l_default_table_name := 'Norway_high_rate_table_7100_to_7233';
413 l_column_name := 'START_RANGE_' || p_freq;
414
415 OPEN c_get_name(p_table_name);
416 FETCH c_get_name into l_test_flag;
417 CLOSE c_get_name;
418
419 /* IF l_test_flag = 'Y' THEN
420 l_test_table_name := p_table_name;
421 ELSE
422 l_test_table_name := l_default_table_name;
423 END IF;
424 */
425
426 If l_test_flag <>'Y' then
427 l_test_table_name := l_default_table_name;
428 Else
429 l_test_table_name := p_table_name;
430 end if;
431
432 OPEN c_get_table_id(l_test_table_name);
433 FETCH c_get_table_id into l_user_table_id;
434 CLOSE c_get_table_id;
435
436 OPEN c_get_column_id(l_column_name , l_user_table_id);
437 FETCH c_get_column_id into l_user_colunm_id;
438 CLOSE c_get_column_id;
439
440 OPEN c_get_start_value(l_user_table_id , l_user_colunm_id , p_ptd_amount , p_Date_Earned );
441 FETCH c_get_start_value into l_start_value;
442 CLOSE c_get_start_value;
443
444 p_start_range := l_start_value;
445
446 /*
447 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
448 p_start_range := '90000';
449 end if;
450 */
451
452 RETURN '1';
453
454 END get_start_range;
455
456 ---------------------------------------------------------------------------------------------------------
457
458 /* Commented to use the Tax table upload logic on 19th May 2005
459 --
460 FUNCTION get_normal_tax (
461 p_Date_Earned IN DATE
462 ,p_business_group_id IN number
463 ,p_table_name IN VARCHAR2
464 ,p_freq IN VARCHAR2
465 ,p_type IN VARCHAR2
466 ,p_ptd_amount IN VARCHAR2
467 ,p_normal_tax OUT NOCOPY VARCHAR2) return varchar2 IS
468 l_high_tax number;
469 l_high_tax_base number;
470 l_high_rate_tax number;
471 l_test_table_name varchar2(100);
472 l_default_table_name varchar2(100);
473 l_test_flag varchar2(1);
474 l_user_table_id number;
475 l_user_row_id number;
476 l_user_colunm_id number;
477 l_pc_value number;
478 l_normal_tax varchar2(100);
479 l_tax_value varchar2(100);
480 cursor c_get_name(l_table_name VARCHAR2) is
481 select 'Y'
482 from PAY_USER_TABLES
483 where USER_TABLE_NAME = l_table_name
484 and legislation_code = 'NO';
485 cursor c_get_table_id(l_table_name VARCHAR2) is
486 select user_table_id
487 from PAY_USER_TABLES
488 where USER_TABLE_NAME = l_table_name
489 and legislation_code = 'NO';
490 cursor c_get_column_id (l_freq VARCHAR2 , l_table_id number) is
491 select user_column_id
492 from PAY_USER_COLUMNS
493 where USER_TABLE_ID = l_table_id
494 AND user_column_name = l_freq;
495 cursor c_get_tax_value (l_column_id number , l_date date , l_table_id number , l_ptd_amount varchar2) is
496 select VALUE
497 from pay_user_column_instances_f
498 where user_column_id = l_column_id
499 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
500 AND user_row_id in (select user_row_id from pay_user_rows_f where
501 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)
502 AND l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE);
503 begin
504 l_test_flag := 'N';
505 l_default_table_name := 'NORWAY_NORMAL_TAX_TABLE_7100_'||p_type;
506 OPEN c_get_name(p_table_name);
507 FETCH c_get_name into l_test_flag;
508 CLOSE c_get_name;
509 IF l_test_flag <> 'Y' THEN
510 l_test_table_name := l_default_table_name;
511 ELSE
512 l_test_table_name := p_table_name;
513 END IF;
514 OPEN c_get_table_id(l_test_table_name);
515 FETCH c_get_table_id into l_user_table_id;
516 CLOSE c_get_table_id;
517 OPEN c_get_column_id(p_freq , l_user_table_id);
518 FETCH c_get_column_id into l_user_colunm_id;
519 CLOSE c_get_column_id;
520 OPEN c_get_tax_value(l_user_colunm_id , p_Date_Earned , l_user_table_id , p_ptd_amount);
521 FETCH c_get_tax_value into l_tax_value;
522 CLOSE c_get_tax_value;
523 If l_tax_value is null then
524 l_tax_value := '0';
525 End if;
526 p_normal_tax := l_tax_value;
527 RETURN '1';
528 END get_normal_tax;
529 --
530 End of commented on 19th May 2005.*/
531 --
532
533
534
535 FUNCTION get_normal_tax ( p_Date_Earned IN DATE
536 ,p_business_group_id IN number
537 ,p_table_name IN VARCHAR2
538 ,p_freq IN VARCHAR2
539 ,p_type IN VARCHAR2
540 ,p_ptd_amount IN VARCHAR2
541 ,p_normal_tax OUT NOCOPY VARCHAR2
542 ,p_tax_card_msg OUT NOCOPY VARCHAR2) return varchar2 IS
543 l_normal_tax number;
544 l_no_tax_card_msg varchar(1000);
545 l_table_name varchar(10);
546
547 /*
548 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
549 Select Amount1
550 from PAY_RANGE_TABLES_F PRTF , PAY_RANGES_F PRF
551 where PRTF.range_table_id = PRF.range_table_id
552 and PRTF.range_table_number = TO_NUMBER(l_table_number)
553 and PRTF.period_frequency = l_freq
554 and PRTF.earnings_type = l_type
555 and PRTF.business_group_id = l_bg_id
556 and PRTF.legislation_code = 'NO'
557 and l_date between PRTF.effective_start_date and PRTF.effective_end_date
558 and to_number(l_amount) between PRF.low_band and PRF.high_band
559 and l_date between PRF.effective_start_date and PRF.effective_end_date;
560 */
561
562 -- Bug Fix 5533206, Norwegian Tax Tables will now be uploaded without any Business Group
563 -- Modifying cursor to check for business_gorup_id IS NULL
564
565 cursor csr_get_normal_tax (l_table_number varchar2 , l_freq varchar2 , l_type varchar2 ,l_date date,l_amount varchar2) is
566 Select Amount1
567 from PAY_RANGE_TABLES_F PRTF , PAY_RANGES_F PRF
568 where PRTF.range_table_id = PRF.range_table_id
569 and PRTF.range_table_number = TO_NUMBER(l_table_number)
570 and PRTF.period_frequency = l_freq
571 and PRTF.earnings_type = l_type
572 and PRTF.business_group_id IS NULL
573 and PRTF.legislation_code = 'NO'
574 and l_date between PRTF.effective_start_date and PRTF.effective_end_date
575 and to_number(l_amount) between PRF.low_band and PRF.high_band
576 and l_date between PRF.effective_start_date and PRF.effective_end_date;
577
578
579
580 begin
581
582 -- open csr_get_normal_tax (p_table_name,p_freq,p_type,p_business_group_id,p_date_earned,p_ptd_amount);
583
584 -- Bug Fix 5533206, Norwegian Tax Tables will now be uploaded without any Business Group
585 -- Modifying cursor call
586
587 open csr_get_normal_tax (p_table_name,p_freq,p_type,p_date_earned,p_ptd_amount);
588 fetch csr_get_normal_tax into l_normal_tax;
589 close csr_get_normal_tax;
590 l_table_name := '';
591 l_no_tax_card_msg := ' ';
592
593 If l_normal_tax is null then
594 l_normal_tax := 0;
595 -- Error message added, returns when tax table not found bug :16215814
596 if (p_table_name <> '9999') then
597 l_table_name := p_table_name;
598 end if;
599
600 hr_utility.set_message (801, 'PAY_376954_NO_TABLE_NOT_FOUND');
601 hr_utility.set_message_token (801, 'TABLE', l_table_name);
602 l_no_tax_card_msg := hr_utility.get_message;
603
604 fnd_file.put_line (fnd_file.LOG, l_no_tax_card_msg );
605 p_tax_card_msg := l_no_tax_card_msg ;
606 RETURN l_normal_tax;
607 END IF;
608 p_normal_tax := to_char(l_normal_tax);
609 RETURN '1';
610 END get_normal_tax;
611 --
612 FUNCTION get_reduced_rule (
613 p_payroll_action_id IN number
614 ,p_payroll_id IN VARCHAR2
615 ,p_reduced_rule OUT NOCOPY VARCHAR2) return varchar2 IS
616 l_reduced_rule varchar2(80);
617 cursor c_get_rule (l_pact_id number , l_payroll_id number) IS
618 SELECT ptp.prd_information1
619 FROM per_time_periods ptp , pay_payroll_actions ppa
620 WHERE ppa.payroll_action_id = l_pact_id
621 AND ppa.action_type in ('Q','R')
622 AND ppa.time_period_id = ptp.time_period_id
623 AND ptp.payroll_id = l_payroll_id;
624 begin
625 OPEN c_get_rule(p_payroll_action_id , p_payroll_id);
626 FETCH c_get_rule into l_reduced_rule;
627 CLOSE c_get_rule;
628 if l_reduced_rule is null then
629 l_reduced_rule := 'N';
630 end if;
631 p_reduced_rule := l_reduced_rule;
632 RETURN '1';
633 END get_reduced_rule;
634 --
635 -- Function GET_MESSAGE
636 -- This function is used to obtain a message.
637 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
638 -- If you want to set the value of a token called ELEMENT to Tax
639 -- the token parameter would be 'ELEMENT:Tax.'
640 ------------------------------------------------------------------------
641 function get_message
642 (p_product in varchar2
643 ,p_message_name in varchar2
644 ,p_token1 in varchar2 default null
645 ,p_token2 in varchar2 default null
646 ,p_token3 in varchar2 default null) return varchar2
647 is
648 l_message varchar2(2000);
649 l_token_name varchar2(20);
650 l_token_value varchar2(80);
651 l_colon_position number;
652 l_proc varchar2(72) ;
653 --
654 begin
655 --
656 hr_utility.set_location('Entered '||l_proc,5);
657 hr_utility.set_location('. Message Name: '||p_message_name,40);
658 fnd_message.set_name(p_product, p_message_name);
659 if p_token1 is not null then
660 /* Obtain token 1 name and value */
661 l_colon_position := instr(p_token1,':');
662 l_token_name := substr(p_token1,1,l_colon_position-1);
663 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
664 fnd_message.set_token(l_token_name, l_token_value);
665 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
666 end if;
667 if p_token2 is not null then
668 /* Obtain token 2 name and value */
669 l_colon_position := instr(p_token2,':');
670 l_token_name := substr(p_token2,1,l_colon_position-1);
671 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
672 fnd_message.set_token(l_token_name, l_token_value);
673 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
674 end if;
675 if p_token3 is not null then
676 /* Obtain token 3 name and value */
677 l_colon_position := instr(p_token3,':');
678 l_token_name := substr(p_token3,1,l_colon_position-1);
679 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
680 fnd_message.set_token(l_token_name, l_token_value);
681 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
682 end if;
683 l_message := substr(fnd_message.get,1,254);
684 hr_utility.set_location('leaving '||l_proc,100);
685 return l_message;
686 end get_message;
687 ------------------------------------------------------------------------
688
689 -- Modified function get_prim_tax_card for Legislative changes 2007.
690
691 /*
692 function get_prim_tax_card (
693 p_assignment_id IN NUMBER
694 ,p_date_earned IN DATE
695 ,p_tax_card_type OUT NOCOPY VARCHAR2
696 ,p_tax_municipality OUT NOCOPY VARCHAR2
697 ,p_tax_percentage OUT NOCOPY VARCHAR2
698 ,p_tax_table_number OUT NOCOPY VARCHAR2
699 ,p_tax_table_type OUT NOCOPY VARCHAR2
700 ,p_tft_value OUT NOCOPY VARCHAR2
701 ,p_tax_card_msg OUT NOCOPY VARCHAR2 ) return varchar2
702 */
703
704 function get_prim_tax_card (
705 p_assignment_id IN NUMBER
706 ,p_date_earned IN DATE
707 ,p_assignment_action_id IN NUMBER
708 ,p_payroll_action_id IN NUMBER
709 ,p_tax_card_type OUT NOCOPY VARCHAR2
710 ,p_tax_municipality OUT NOCOPY VARCHAR2
711 ,p_tax_percentage OUT NOCOPY VARCHAR2
712 ,p_tax_table_number OUT NOCOPY VARCHAR2
713 ,p_tax_table_type OUT NOCOPY VARCHAR2
714 ,p_tft_value OUT NOCOPY VARCHAR2
715 ,p_tax_card_msg OUT NOCOPY VARCHAR2 ) return varchar2
716 is
717 l_tax_card_type varchar2(80);
718 l_tax_municipality varchar2(80);
719 l_tax_municipality_num number;
720 l_tax_percentage varchar2(80);
721 l_tax_percentage_num number := null;
722 l_tax_table_number varchar2(80) ;
723 l_tax_table_type varchar2(80) ;
724 l_tft_value varchar2(80) ;
725 l_tft_value_num number(13,2);
726 l_main_person_id number;
727 l_prim_asg_id number;
728
729 -- BUG 4774784 fix start
730
731 l_eeid NUMBER;
732 l_full_name VARCHAR2(240);
733 l_emp_num VARCHAR2(30);
734 l_no_tax_card_msg VARCHAR2(1000);
735 l_return_val VARCHAR2(20);
736
737 -- BUG 4774784 fix end
738
739 CURSOR get_person_id (l_assignment_id number,l_date_earned date) is
740 SELECT person_id
741 FROM per_all_assignments_f paf
742 WHERE paf.assignment_id = l_assignment_id
743 AND l_date_earned between paf.effective_start_date and paf.effective_end_date;
744
745 CURSOR get_prim_assignment_id( l_person_id number,l_date date) is
746 SELECT assignment_id
747 FROM per_all_assignments_f paf
748 WHERE paf.person_id = l_person_id
749 AND primary_flag = 'Y'
750 AND l_date between paf.effective_start_date and paf.effective_end_date;
751
752 CURSOR get_tax_card_details( l_assignment_id number,l_date date , l_input_name varchar2) is
753 SELECT screen_entry_value
754 FROM pay_element_entry_values_f eev,
755 pay_element_entries_f ee,
756 pay_element_types_f et,
757 pay_input_values_f iv
758 WHERE eev.element_entry_id = ee.element_entry_id
759 And l_date between eev.effective_start_date and eev.effective_end_date
760 And ee.assignment_id = l_assignment_id
761 AND l_date between ee.effective_start_date and ee.effective_end_date
762 And 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 = iv.element_type_id
766 And iv.name = l_input_name
767 And l_date between iv.effective_start_date and iv.effective_end_date
768 And eev.input_value_id = iv.input_value_id;
769
770 -- BUG 4774784 fix start
771
772 -- cursor to get employee full name and employee number
773 CURSOR get_person_details (l_person_id number,l_date_earned date) is
774 SELECT full_name , employee_number
775 FROM per_all_people_f
776 WHERE person_id = l_person_id
777 AND l_date_earned between effective_start_date and effective_end_date ;
778
779 -- cursor to check if element TAX CARD exists on the primary assignment of the employee
780 CURSOR csr_chk_tax_card( l_assignment_id number,l_date date ) is
781 SELECT ee.ELEMENT_ENTRY_ID
782 FROM pay_element_entries_f ee,
783 pay_element_types_f et
784 WHERE et.element_name = 'Tax Card'
785 And et.legislation_code = 'NO'
786 And l_date between et.effective_start_date and et.effective_end_date
787 And et.element_type_id = ee.element_type_id
788 AND ee.assignment_id = l_assignment_id
789 AND l_date between ee.effective_start_date and ee.effective_end_date ;
790
791 -- BUG 4774784 fix end
792
793 -------- Adding new cursors for Legislative changes 2007
794
795 -- Legislative changes 2007 : cursor to get the tax municipality for Ambulatory operations
796
797 CURSOR csr_get_amb_op_tax_mun (p_asg_id NUMBER , pay_act_id NUMBER ) IS
798 SELECT distinct eev.screen_entry_value Tax_Municipality
799 FROM pay_element_entries_f pee
800 ,pay_element_entry_values_f eev
801 ,pay_input_values_f piv
802 ,pay_element_types_f pet
803 ,pay_payroll_actions ppa
804 WHERE ppa.payroll_action_id = pay_act_id
805 AND pee.assignment_id = p_asg_id
806 AND pet.element_name = 'Employer Contribution Information'
807 AND pet.legislation_code = 'NO'
808 AND piv.name = 'Tax Municipality'
809 AND pee.element_entry_id = eev.element_entry_id
810 AND eev.input_value_id + 0 = piv.input_value_id
811 AND piv.element_type_id = pet.element_type_id
812 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
813 AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
814 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
815 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date ;
816
817
818 -- Legislative changes 2007 : cursor to fetch the Tax Municipality at Local Unit
819
820 CURSOR csr_get_lu_tax_mun (p_assignment_action_id NUMBER) IS
821 SELECT ORG_INFORMATION6 lu_tax_mun
822 FROM pay_assignment_actions assact ,
823 per_all_assignments_f paa ,
824 pay_payroll_actions ppa ,
825 hr_soft_coding_keyflex scl ,
826 hr_organization_information hoi
827 WHERE assact.assignment_action_id = p_assignment_action_id
828 AND ppa.payroll_action_id = assact.payroll_action_id
829 AND paa.assignment_id = assact.assignment_id
830 AND ppa.effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
831 AND paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
832 AND hoi.organization_id = scl.segment2
833 AND hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS' ;
834
835 -------- End Adding new cursors for Legislative changes 2007
836
837 --
838 begin
839
840 open get_person_id (p_assignment_id,p_date_earned);
841 fetch get_person_id into l_main_person_id;
842 close get_person_id;
843
844 open get_prim_assignment_id (l_main_person_id,p_date_earned);
845 fetch get_prim_assignment_id into l_prim_asg_id;
846 close get_prim_assignment_id;
847
848
849 -- BUG 4774784 fix start
850
851 OPEN csr_chk_tax_card( l_prim_asg_id ,p_date_earned );
852 FETCH csr_chk_tax_card into l_eeid ;
853
854 IF csr_chk_tax_card%NOTFOUND
855 THEN
856 -- TAX CARD element not attached to emp's primary assignment
857 OPEN get_person_details (l_main_person_id ,p_date_earned ) ;
858 FETCH get_person_details into l_full_name , l_emp_num ;
859 CLOSE get_person_details;
860
861 hr_utility.set_message (801, 'PAY_376863_NO_TAX_CARD_ELE_ERR');
862 hr_utility.set_message_token (801, 'EMP_NAME', l_full_name);
863 hr_utility.set_message_token (801, 'EMP_NUM', l_emp_num);
864
865 l_no_tax_card_msg := hr_utility.get_message ;
866
867 -- Put the meassage in the log file
868 fnd_file.put_line (fnd_file.LOG, l_no_tax_card_msg );
869
870 l_return_val := '0' ;
871
872 ELSE
873 -- TAX CARD element found
874 l_return_val := '1' ;
875 l_no_tax_card_msg := '';
876
877
878 -- BUG 4774784 fix end
879
880
881 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Municipality');
882 fetch get_tax_card_details into l_tax_municipality;
883 close get_tax_card_details;
884
885 -- Changes for Legislative changes 2007
886
887 -- the above l_tax_municipality is the Tax Municipality from Tax Card
888 -- the value for Tax Municipality returned from this procedure will be used for
889 -- JURISRICTION_CODE context which will be used in Employer Contribution calculations.
890
891 -- For calculations before 2007, the Tax Municipality for this will be fetched from
892 -- the input value of the element 'Tax Card'.
893 -- For calculations in and after 2007, the Tax Municipality for this will be fetched from
894 -- the input value of the element 'Employer Contribution Information' (Ambulatory Operations)
895 -- or the Tax Municipality at the Local Unit attached to the assignment.
896
897
898 IF (to_number(to_char(p_date_earned,'RRRR')) >= 2007)
899
900 THEN
901 OPEN csr_get_amb_op_tax_mun (p_assignment_id , p_payroll_action_id );
902 FETCH csr_get_amb_op_tax_mun INTO l_tax_municipality;
903 CLOSE csr_get_amb_op_tax_mun;
904
905 IF ( l_tax_municipality IS NULL )
906 THEN
907 OPEN csr_get_lu_tax_mun ( p_assignment_action_id );
908 FETCH csr_get_lu_tax_mun INTO l_tax_municipality;
909 CLOSE csr_get_lu_tax_mun ;
910
911 END IF ;
912
913 END IF ;
914
915 -- End Changes for Legislative changes 2007
916
917 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Card Type');
918 fetch get_tax_card_details into l_tax_card_type;
919 close get_tax_card_details;
920
921 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Percentage');
922 fetch get_tax_card_details into l_tax_percentage;
923 close get_tax_card_details;
924
925 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Table Number');
926 fetch get_tax_card_details into l_tax_table_number;
927 close get_tax_card_details;
928
929 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Table Type');
930 fetch get_tax_card_details into l_tax_table_type;
931 close get_tax_card_details;
932
933 open get_tax_card_details (l_prim_asg_id,p_date_earned,'Tax Free Threshold');
934 fetch get_tax_card_details into l_tft_value;
935 close get_tax_card_details;
936
937 END IF;
938
939 CLOSE csr_chk_tax_card ;
940
941
942 If l_tax_card_type is null then
943 l_tax_card_type := 'PB';
944 End If;
945
946 If l_tax_municipality is null then
947 l_tax_municipality := 0;
948 End If;
949
950 If l_tax_percentage is null then
951 l_tax_percentage := '50';
952 End If;
953
954 If l_tax_table_number is null then
955 l_tax_table_number := '9999';
956 End If;
957
958 If l_tax_table_type is null then
959 l_tax_table_type := 'O';
960 End If;
961
962 If l_tft_value is null then
963 l_tft_value := '0';
964 End If;
965
966 p_tax_card_type := l_tax_card_type;
967 p_tax_municipality := l_tax_municipality;
968 p_tax_percentage := l_tax_percentage;
969 p_tax_table_number := l_tax_table_number;
970 p_tax_table_type := l_tax_table_type;
971 p_tft_value := l_tft_value;
972 p_tax_card_msg := l_no_tax_card_msg ;
973
974 -- RETURN '1';
975 RETURN l_return_val ;
976
977 end get_prim_tax_card;
978
979 ------------------------------------------------------------------------
980
981 FUNCTION get_pay_holiday_rule ( p_payroll_action_id IN NUMBER
982 ,p_payroll_id IN VARCHAR2
983 ,p_pay_holiday_rule OUT nocopy VARCHAR2)
984
985 RETURN VARCHAR2 IS
986
987 l_pay_holiday_rule VARCHAR2(80);
988
989 CURSOR csr_get_pay_holiday_rule (l_pact_id NUMBER, l_payroll_id NUMBER) IS
990 SELECT ptp.prd_information2
991 FROM per_time_periods ptp,
992 pay_payroll_actions ppa
993 WHERE ppa.payroll_action_id = l_pact_id
994 AND ppa.action_type IN('Q','R')
995 AND ppa.time_period_id = ptp.time_period_id
996 AND ptp.payroll_id = l_payroll_id ;
997
998 BEGIN
999
1000 OPEN csr_get_pay_holiday_rule (p_payroll_action_id , p_payroll_id) ;
1001 FETCH csr_get_pay_holiday_rule INTO l_pay_holiday_rule ;
1002 CLOSE csr_get_pay_holiday_rule ;
1003
1004 IF l_pay_holiday_rule IS NULL THEN
1005 l_pay_holiday_rule := 'N';
1006 END IF;
1007
1008 p_pay_holiday_rule := l_pay_holiday_rule;
1009 RETURN '1';
1010
1011 END get_pay_holiday_rule ;
1012
1013 FUNCTION get_tax_period_rule ( p_assignment_id IN NUMBER
1014 ,p_effective_date IN DATE
1015 ,p_per_reduced_rule OUT NOCOPY VARCHAR2)
1016 RETURN VARCHAR2 IS
1017
1018 l_tax_period_rule VARCHAR2(80);
1019 cursor csr_people_extra_info is
1020 SELECT pei_information1
1021 FROM per_people_extra_info pei,per_all_assignments_f paf
1022 WHERE information_type = 'NO_FURTHER_PERIOD_DETAILS'
1023 AND paf.person_id = pei.person_id
1024 and paf.ASSIGNMENT_ID = p_assignment_id
1025 AND P_EFFECTIVE_DATE BETWEEN paf.EFFECTIVE_START_DATE
1026 AND paf.EFFECTIVE_END_DATE ;
1027
1028 BEGIN
1029
1030 OPEN csr_people_extra_info ;
1031 FETCH csr_people_extra_info INTO l_tax_period_rule ;
1032 CLOSE csr_people_extra_info ;
1033
1034 p_per_reduced_rule := l_tax_period_rule;
1035 IF l_tax_period_rule is null then
1036 p_per_reduced_rule := 'N' ;
1037 end if;
1038 return '1';
1039
1040 END get_tax_period_rule ;
1041 ------------------------------------------------------------------------
1042 END PAY_NO_HIGH_RATE_TAX;
[Home] [Help]