[Home] [Help]
PACKAGE BODY: APPS.PAY_AC_TAXABILITY_WRAPPER
Source
1 PACKAGE BODY pay_ac_taxability_wrapper as
2 /* $Header: payactxabltywrap.pkb 120.5 2006/11/20 10:49:23 rpasumar noship $ */
3
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 Oracle Corporation. *
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, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Package Body Name : pay_ac_taxability_wrapper
22 Package File Name : payactxabltywrap.pkb
23 Description : This package declares functions and procedures
24 which supports US and CA taxability rules upload
25 via spread sheet loader.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- --------------------------
31 21-JUN-04 fusman 115.0 Created
32 29-JUL-04 fusman 115.1 Added State information.
33 01-AUG-04 fusman 115.2 Added Canada information.
34 11-AUG-04 fusman 115.3 Added Local information.
35 16-AUG-04 fusman 115.4 Added new message for invalid
36 type values.
37
38 18-AUG-04 fusman 115.5 Added tax types for Local
39 Pre-tax deductions.
40 18-AUG-04 fusman 115.6 3840695 Initialized the ltt_tax_type
41 pl/sql table.
42 Cleared the City,County values
43 for Local taxability rules.
44 24-AUG-04 fusman 115.6 Changed the city,county,tax type
45 value names to upper case.
46 25-AUG-04 fusman 115.9 3847970 Changed the code to not insert
47 a row with status of D for Canada.
48 Also, if user enters N in spreadsheet
49 the value is deleted from table.
50 26-AUG-04 fusman 115.10 3855943 Added nvl when checking valid status for CA
51 04-AUG-05 meshah 115.11 Added AEIC for state pretax and earnings
52 27-DEC-05 sudedas 115.12 4591127 Changed create_taxability_rules,
53 transfer_tax_type_values and
54 create_ca_prov_taxability
55 15-NOV-06 rpasumar 115.13 Modified create_taxability_rules for the bug# 5652699.
56 *******************************************************************/
57
58 -- Package Variables
59 g_package VARCHAR2(100);
60 TYPE character_data_table IS TABLE OF VARCHAR2(280)
61 INDEX BY BINARY_INTEGER;
62
63 ltt_tax_types character_data_table;
64 ltt_tax_type_values character_data_table;
65
66
67 PROCEDURE transfer_tax_type_values
68 (p_input_tax_type_value1 IN VARCHAR2
69 ,p_input_tax_type_value2 IN VARCHAR2
70 ,p_input_tax_type_value3 IN VARCHAR2
71 ,p_input_tax_type_value4 IN VARCHAR2
72 ,p_input_tax_type_value5 IN VARCHAR2
73 ,p_input_tax_type_value6 IN VARCHAR2
74 ,p_input_tax_type_value7 IN VARCHAR2
75 ,p_input_tax_type_value8 IN VARCHAR2
76 ,p_input_tax_type_value9 IN VARCHAR2
77 ,p_input_tax_type_value10 IN VARCHAR2
78 ,p_input_tax_type_value11 IN VARCHAR2)
79 IS
80 BEGIN
81
82 pay_ac_taxability_wrapper.ltt_tax_type_values(1) := upper(p_input_tax_type_value1);
83 pay_ac_taxability_wrapper.ltt_tax_type_values(2) := upper(p_input_tax_type_value2);
84 pay_ac_taxability_wrapper.ltt_tax_type_values(3) := upper(p_input_tax_type_value3);
85 pay_ac_taxability_wrapper.ltt_tax_type_values(4) := upper(p_input_tax_type_value4);
86 pay_ac_taxability_wrapper.ltt_tax_type_values(5) := upper(p_input_tax_type_value5);
87 pay_ac_taxability_wrapper.ltt_tax_type_values(6) := upper(p_input_tax_type_value6);
88 pay_ac_taxability_wrapper.ltt_tax_type_values(7) := upper(p_input_tax_type_value7);
89 pay_ac_taxability_wrapper.ltt_tax_type_values(8) := upper(p_input_tax_type_value8);
90 pay_ac_taxability_wrapper.ltt_tax_type_values(9) := upper(p_input_tax_type_value9);
91 pay_ac_taxability_wrapper.ltt_tax_type_values(10):= upper(p_input_tax_type_value10);
92 pay_ac_taxability_wrapper.ltt_tax_type_values(11):= upper(p_input_tax_type_value11);
93
94 hr_utility.trace('ltt_tax_type_values(1) = '||
95 pay_ac_taxability_wrapper.ltt_tax_type_values(1));
96 hr_utility.trace('ltt_tax_type_values(2) = '||
97 pay_ac_taxability_wrapper.ltt_tax_type_values(2));
98 hr_utility.trace('ltt_tax_type_values(3) = '||
99 pay_ac_taxability_wrapper.ltt_tax_type_values(3));
100 hr_utility.trace('ltt_tax_type_values(4) = '||
101 pay_ac_taxability_wrapper.ltt_tax_type_values(4));
102 hr_utility.trace('ltt_tax_type_values(5) = '||
103 pay_ac_taxability_wrapper.ltt_tax_type_values(5));
104 hr_utility.trace('ltt_tax_type_values(6) = '||
105 pay_ac_taxability_wrapper.ltt_tax_type_values(6));
106 hr_utility.trace('ltt_tax_type_values(7) = '||
107 pay_ac_taxability_wrapper.ltt_tax_type_values(7));
108 hr_utility.trace('ltt_tax_type_values(8) = '||
109 pay_ac_taxability_wrapper.ltt_tax_type_values(8));
110 hr_utility.trace('ltt_tax_type_values(9) = '||
111 pay_ac_taxability_wrapper.ltt_tax_type_values(9));
112 hr_utility.trace('ltt_tax_type_values(10) = '||
113 pay_ac_taxability_wrapper.ltt_tax_type_values(10));
114 hr_utility.trace('ltt_tax_type_values(11) = '||
115 pay_ac_taxability_wrapper.ltt_tax_type_values(11));
116
117 END transfer_tax_type_values;
118
119 /************************************************************
120 ** Function called for US Federal Context is passed
121 ************************************************************/
122 FUNCTION get_taxability_rule_date_id
123 (p_legislation_code IN VARCHAR2,
124 p_effective_date IN DATE)
125 RETURN NUMBER
126
127 IS
128
129 cursor c_taxability_rule_date (cp_legislation_code in varchar2
130 ,cp_effective_date in date) is
131 select taxability_rules_date_id
132 from pay_taxability_rules_dates
133 where legislation_code = cp_legislation_code
134 and cp_effective_date between valid_date_from
135 and valid_date_to;
136
137 ln_taxability_rule_date_id NUMBER;
138
139 BEGIN
140 open c_taxability_rule_date(p_legislation_code
141 ,p_effective_date);
142 fetch c_taxability_rule_date into ln_taxability_rule_date_id;
143 if c_taxability_rule_date%notfound then
144 hr_utility.trace('No Taxability Rule Date found');
145 hr_utility.raise_error;
146 end if;
147 close c_taxability_rule_date;
148
149 return (ln_taxability_rule_date_id);
150
151 END get_taxability_rule_date_id;
152
153 PROCEDURE initialize
154 IS
155 BEGIN
156
157 ltt_tax_types(1) := null;
158 ltt_tax_types(2) := null;
159 ltt_tax_types(3) := null;
160 ltt_tax_types(4) := null;
161 ltt_tax_types(5) := null;
162 ltt_tax_types(6) := null;
163 ltt_tax_types(7) := null;
164 ltt_tax_types(8) := null;
165 ltt_tax_types(9) := null;
166 ltt_tax_types(10) := null;
167
168 ltt_tax_types.delete;
169
170 END initialize;
171
172 /************************************************************
173 ** Function called for US Federal Context is passed
174 ** Following values are currently used
175 ** p_input_tax_type_value1 = FIT Not Withheld
176 ** p_input_tax_type_value2 = FIT Withheld
177 ** p_input_tax_type_value3 = EIC
178 ** p_input_tax_type_value4 = FUTA
179 ** p_input_tax_type_value5 = Medicare
180 ** p_input_tax_type_value6 = SS
181 ************************************************************/
182 PROCEDURE create_us_federal_taxability(p_classification in varchar2)
183 IS
184 BEGIN
185
186 if p_classification in ('Supplemental Earnings',
187 'Imputed Earnings') then
188
189 ltt_tax_types(1) := 'NW_FIT';
190 ltt_tax_types(2) := 'FIT';
191 ltt_tax_types(3) := 'EIC';
192 ltt_tax_types(4) := 'FUTA';
193 ltt_tax_types(5) := 'MEDICARE';
194 ltt_tax_types(6) := 'SS';
195
196 elsif p_classification in ('Pre-Tax Deductions') then
197 ltt_tax_types(1) := 'FIT';
198 ltt_tax_types(2) := 'EIC';
199 ltt_tax_types(3) := 'FUTA';
200 ltt_tax_types(4) := 'MEDICARE';
201 ltt_tax_types(5) := 'SS';
202
203 end if;
204
205 hr_utility.trace('In create_us_federal_taxability');
206 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
207 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
208
209 END create_us_federal_taxability;
210
211 /************************************************************
212 ** Function called for US State Context is passed
213 ** Following values are currently used
214 ** Earnings:
215 ** p_input_tax_type_value1 = SIT Not Withheld
216 ** p_input_tax_type_value2 = SIT Withheld
217 ** p_input_tax_type_value3 = SDI
218 ** p_input_tax_type_value4 = SUI
219 ** p_input_tax_type_value5 = WC
220 ** p_input_tax_type_value6 = STEIC
221 ** Pre Tax Deductions:
222 ** p_input_tax_type_value1 = SIT
223 ** p_input_tax_type_value2 = SDI
224 ** p_input_tax_type_value3 = SUI
225 ** p_input_tax_type_value4 = STEIC
226
227 ************************************************************/
228 PROCEDURE create_us_state_taxability(p_classification in varchar2)
229 IS
230 BEGIN
231
232 if p_classification in ('Supplemental Earnings',
233 'Imputed Earnings') then
234 ltt_tax_types(1) := 'NW_SIT';
235 ltt_tax_types(2) := 'SIT';
236 ltt_tax_types(3) := 'SDI';
237 ltt_tax_types(4) := 'SUI';
238 ltt_tax_types(5) := 'WC';
239 ltt_tax_types(6) := 'STEIC';
240
241 elsif p_classification in ('Pre-Tax Deductions') then
242 ltt_tax_types(1) := 'SIT';
243 ltt_tax_types(2) := 'SDI';
244 ltt_tax_types(3) := 'SUI';
245 ltt_tax_types(4) := 'STEIC';
246
247 end if;
248
249 hr_utility.trace('In create_us_state_taxability');
250 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
251 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
252
253 END create_us_state_taxability;
254
255 /************************************************************
256 ** Function called for US Local County is passed
257 ** Following values are currently used for
258 ** Earnings,Pre Tax Deductions and Taxable Benefits
259 ** p_input_tax_type_value1 = COUNTY -- County Withheld
260 ** p_input_tax_type_value2 = NW_COUNTY -- County Not Withheld
261
262 ************************************************************/
263 PROCEDURE create_us_loc_county_tax_rule(p_classification in varchar2)
264 IS
265 BEGIN
266
267 if p_classification in ('Supplemental Earnings',
268 'Imputed Earnings') then
269 ltt_tax_types(1) := 'NW_COUNTY';
270 ltt_tax_types(2) := 'COUNTY';
271 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
272 hr_utility.trace('ltt_tax_types(2) = '||ltt_tax_types(2));
273 elsif p_classification in ('Pre-Tax Deductions') then
274 ltt_tax_types(1) := 'COUNTY';
275 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
276 end if;
277 hr_utility.trace('In create_ca_federal_taxability');
278 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
279
280 END create_us_loc_county_tax_rule;
281
282
283 /************************************************************
284 ** Function called for US Local City is passed
285 ** Following values are currently used for
286 ** Earnings,Pre Tax Deductions and Taxable Benefits
287 ** p_input_tax_type_value1 = CITY -- City Withheld
288 ** p_input_tax_type_value2 = NW_CITY -- City Not Withheld
289
290 ************************************************************/
291 PROCEDURE create_us_loc_city_tax_rule(p_classification in varchar2)
292 IS
293 BEGIN
294
295 if p_classification in ('Supplemental Earnings',
296 'Imputed Earnings') then
297 ltt_tax_types(1) := 'NW_CITY';
298 ltt_tax_types(2) := 'CITY';
299 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
300 hr_utility.trace('ltt_tax_types(2) = '||ltt_tax_types(2));
301 elsif p_classification in ('Pre-Tax Deductions') then
302 hr_utility.trace('In create_ca_federal_taxability');
303 ltt_tax_types(1) := 'CITY';
304 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
305 end if;
306 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
307
308 END create_us_loc_city_tax_rule;
309
310 /************************************************************
311 ** Function called for CA Federal Context is passed
312 ** Following values are currently used for
313 ** Earnings,Pre Tax Deductions and Taxable Benefits
314 ** p_input_tax_type_value1 = FED -- Federal Income Tax
315 ** p_input_tax_type_value2 = CPP -- Canada Pension Plan
316 ** p_input_tax_type_value3 = EIM -- Employment Insurance Money
317
318 ************************************************************/
319 PROCEDURE create_ca_federal_taxability
320 IS
321 BEGIN
322
323 ltt_tax_types(1) := 'FED';
324 ltt_tax_types(2) := 'CPP';
325 ltt_tax_types(3) := 'EIM';
326
327 hr_utility.trace('In create_ca_federal_taxability');
328 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
329 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
330 hr_utility.trace('ltt_tax_types(2) = '||ltt_tax_types(2));
331 hr_utility.trace('ltt_tax_types(3) = '||ltt_tax_types(3));
332
333 END create_ca_federal_taxability;
334
335
336 /************************************************************
337 ** Function called for Canadian Context is passed
338 ** Following values are currently used
339 ** Supplemental Earnings:
340 ** p_input_tax_type_value1 = PRV -- Provincial Income Tax
341 ** p_input_tax_type_value2 = QPP -- Quebec Pension Plan
342 ** p_input_tax_type_value3 = PMED -- Provincial Medical Plan
343 ** p_input_tax_type_value4 = WCB -- Workers Compensation
344 ** p_input_tax_type_value5 = VAC -- Vacationable Earnings
345 ** p_input_tax_type_value6 = PPIP
346 **
347 ** Taxable Benefits
348 ** p_input_tax_type_value1 = PRV -- Provincial Income Tax
349 ** p_input_tax_type_value2 = QPP -- Quebec Pension Plan
350 ** p_input_tax_type_value3 = PMED -- Provincial Medical Plan
351 ** p_input_tax_type_value4 = WCB -- Workers Compensation
352 ** p_input_tax_type_value5 = PST/QST Provincial Sales Tax/Quebec Sales Tax
353 ** p_input_tax_type_value6 = GST -- Goods and Services Tax
354 ** p_input_tax_type_value7 = HST -- Harmonized Sales Tax
355 ** p_input_tax_type_value8 = PPT -- Provincial Premium Tax
356 ** p_input_tax_type_value9 = RSTI -- Retail Sales Tax on Insurance
357 ** p_input_tax_type_value10 =VAC -- Vacationable Earnings
358 ** p_input_tax_type_value11 = PPIP
359 **
360 ** Pre Tax Deductions:
361 ** p_input_tax_type_value1 = PRV -- Provincial Income Tax
362 ** p_input_tax_type_value2 = QPP -- Quebec Pension Plan
363 ** p_input_tax_type_value3 = PMED -- Provincial Medical Plan
364 ** p_input_tax_type_value4 = WCB -- Workers Compensation
365 ** p_input_tax_type_value5 = PST/QST Provincial Sales Tax/Quebec Sales Tax
366 ** p_input_tax_type_value6 = GST -- Goods and Services Tax
367 ** p_input_tax_type_value7 = HST -- Harmonized Sales Tax
368 ** p_input_tax_type_value8 = PPT -- Provincial Premium Tax
369 ** p_input_tax_type_value9 = RSTI -- Retail Sales Tax on Insurance
370 ** p_input_tax_type_value10 = PPIP
371
372 ************************************************************/
373 PROCEDURE create_ca_prov_taxability(p_classification in varchar2
374 ,p_jurisdiction in varchar2)
375 IS
376 BEGIN
377
378 if p_classification = 'Supplemental Earnings' then
379 ltt_tax_types(1) := 'PRV';
380 ltt_tax_types(2) := 'QPP';
381 ltt_tax_types(3) := 'PMED';
382 ltt_tax_types(4) := 'WCB';
383 ltt_tax_types(5) := 'VAC';
384 ltt_tax_types(6) := 'PPIP';
385 hr_utility.trace('Supplemental Earnings');
386
387 elsif p_classification = 'Taxable Benefits' then
388 hr_utility.trace('Taxable Benefits ');
389 ltt_tax_types(1) := 'PRV';
390 ltt_tax_types(2) := 'QPP';
391 ltt_tax_types(3) := 'PMED';
395 ltt_tax_types(5) := 'QST';
392 ltt_tax_types(4) := 'WCB';
393
394 if substr(p_jurisdiction,1,2) = 'QC' then
396 hr_utility.trace('Quebec. ltt_tax_types(5) = '||ltt_tax_types(5));
397
398 else
399 ltt_tax_types(5) := 'PST';
400 end if;
401
402 ltt_tax_types(6) := 'GST';
403 ltt_tax_types(7) := 'HST';
404 ltt_tax_types(8) := 'PPT';
405 ltt_tax_types(9) := 'RSTI';
406 ltt_tax_types(10) := 'VAC';
407 ltt_tax_types(11) := 'PPIP';
408
409 elsif p_classification = 'Pre-Tax Deductions' then
410 ltt_tax_types(1) := 'PRV';
411 ltt_tax_types(2) := 'QPP';
412 ltt_tax_types(3) := 'PMED';
413 ltt_tax_types(4) := 'WCB';
414
415 if substr(p_jurisdiction,1,2) = 'QC' then
416 ltt_tax_types(5) := 'QST';
417 hr_utility.trace('Quebec. ltt_tax_types(5) = '||ltt_tax_types(5));
418 else
419 ltt_tax_types(5) := 'PST';
420 end if;
421
422 ltt_tax_types(6) := 'GST';
423 ltt_tax_types(7) := 'HST';
424 ltt_tax_types(8) := 'PPT';
425 ltt_tax_types(9) := 'RSTI';
426 ltt_tax_types(10) := 'PPIP';
427 end if;
428
429 hr_utility.trace('In create_ca_prov_taxability');
430 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
431 /*
432 hr_utility.trace('ltt_tax_types(1) = '||ltt_tax_types(1));
433 hr_utility.trace('ltt_tax_types(2) = '||ltt_tax_types(2));
434 hr_utility.trace('ltt_tax_types(3) = '||ltt_tax_types(3));
435 hr_utility.trace('ltt_tax_types(4) = '||ltt_tax_types(4));
436 hr_utility.trace('ltt_tax_types(5) = '||ltt_tax_types(5));
437 hr_utility.trace('ltt_tax_types(6) = '||ltt_tax_types(6));
438 hr_utility.trace('ltt_tax_types(7) = '||ltt_tax_types(7));
439 hr_utility.trace('ltt_tax_types(8) = '||ltt_tax_types(8));
440 hr_utility.trace('ltt_tax_types(9) = '||ltt_tax_types(9));
441 hr_utility.trace('ltt_tax_types(10) = '||ltt_tax_types(10)); */
442
443 END create_ca_prov_taxability;
444
445 PROCEDURE call_api_for_taxability_rules
446 (p_classification_id IN NUMBER
447 ,p_jurisdiction IN VARCHAR2
448 ,p_legislation_code IN VARCHAR2
449 ,p_tax_category IN VARCHAR2
450 ,p_taxability_rule_date_id IN NUMBER
451 ,ptt_tax_types IN ltt_tax_types%type
452 ,ptt_tax_type_values IN ltt_tax_type_values%type)
453 IS
454 lv_status VARCHAR2(10);
455 lv_valid_status VARCHAR2(10);
456
457 BEGIN
458
459 hr_utility.trace('In call_api_for_taxability_rules');
460 hr_utility.trace('p_classification_id ' || p_classification_id);
461 hr_utility.trace('p_jurisdiction ' || p_jurisdiction);
462 hr_utility.trace('p_legislation_code ' || p_legislation_code);
463 hr_utility.trace('p_tax_category ' || p_tax_category);
464 hr_utility.trace('p_taxability_rule_date_id ' || p_taxability_rule_date_id);
465 hr_utility.trace('ptt_tax_types.count = ' ||
466 to_char(ptt_tax_types.count));
467 hr_utility.trace('ptt_tax_type_values.count = '||
468 to_char(ptt_tax_type_values.count));
469 hr_utility.trace('ptt_tax_types(1) = '||ptt_tax_types(1));
470
471 for i in ptt_tax_types.first .. ptt_tax_types.last loop
472
473 hr_utility.trace('In Loop. ptt_tax_types = '||ptt_tax_types(i));
474
475 /*****************************************************************
476 ** Only call Taxability Rules API if the use has entered a value
477 *****************************************************************/
478 if ptt_tax_type_values(i) is not null then
479
480 if ptt_tax_type_values(i) = 'Y' OR
481 ptt_tax_type_values(i) = 'N' then
482 lv_status := pay_taxability_rules_api.check_taxability_rule_exists
483 (p_jurisdiction => p_jurisdiction
484 ,p_legislation_code => p_legislation_code
485 ,p_classification_id => p_classification_id
486 ,p_tax_category => p_tax_category
487 ,p_tax_type => ptt_tax_types(i)
488 );
489
490 /* Do not touch the row if its a seeded one. */
491 if lv_status = 'S' then /*Seed Row. */
492 hr_utility.trace('Do not modify Seed Data for Category: '||
493 p_tax_category);
494 hr_utility.set_message(801, 'PAY_DATAPUMP_UPD_TAX_SEED_ROW');
495 hr_utility.set_message_token('COLUMN', ptt_tax_types(i));
496 hr_utility.raise_error;
497
498 else
499 hr_utility.trace('lv_status = '||lv_status);
500 hr_utility.trace('lv_valid_status = '||lv_valid_status);
501
502 /* No row exists. So insert a new row with status Null. */
503 if lv_status = 'N' then /*lv_status Check*/
504 /* No row exists. So insert a new row with status Null. */
505 if ptt_tax_type_values(i) = 'Y' then
506 hr_utility.trace('No row exists and user has passed Y.
507 So insert a new row with status Null.');
508 lv_valid_status := null;
509 elsif ptt_tax_type_values(i) = 'N' then
510
511 hr_utility.trace('No row exists and user has passed N.
512 So insert a new row with status D.');
513 lv_valid_status := 'D';
517
514 end if;
515
516 if p_legislation_code = 'US' or nvl(lv_valid_status,'V') <> 'D' then
518 hr_utility.trace('p_legislation_code = '||p_legislation_code||'
519 ptt_tax_types = '||ptt_tax_types(i));
520 pay_taxability_rules_api.create_taxability_rules
521 (p_validate => FALSE
522 ,p_jurisdiction => p_jurisdiction
523 ,p_tax_type => ptt_tax_types(i)
524 ,p_tax_category => p_tax_category
525 ,p_classification_id => p_classification_id
526 ,p_taxability_rules_date_id => p_taxability_rule_date_id
527 ,p_legislation_code => p_legislation_code
528 ,p_status => lv_valid_status
529 );
530 end if;
531
532 elsif lv_status in ('V') then /*Active row exists. */
533
534 if ptt_tax_type_values(i) = 'N' then
535 /*the user wanted to delete it. So set the status to 'D'. */
536
537 hr_utility.trace('Active row exists. User wanted to delete'||
538 ' it so set the status to D.');
539 lv_valid_status := 'D';
540
541 if p_legislation_code = 'US' then
542 pay_taxability_rules_api.update_taxability_rules
543 (p_validate => FALSE
544 ,p_jurisdiction => p_jurisdiction
545 ,p_tax_type => ptt_tax_types(i)
546 ,p_tax_category => p_tax_category
547 ,p_classification_id => p_classification_id
548 ,p_taxability_rules_date_id => p_taxability_rule_date_id
549 ,p_legislation_code => p_legislation_code
550 ,p_status => lv_valid_status
551 );
552 elsif p_legislation_code = 'CA' then
553
554 delete from pay_taxability_rules
555 where legislation_code = p_legislation_code
556 and tax_type = ptt_tax_types(i)
557 and jurisdiction_code = p_jurisdiction
558 and classification_id = p_classification_id
559 and tax_category = p_tax_category
560 and taxability_rules_date_id = p_taxability_rule_date_id;
561
562 end if;
563 end if;
564
565 elsif lv_status in ('D') then /*In active row exists. */
566
567 if ptt_tax_type_values(i) = 'Y' then
568 /* But the user wanted to insert a row.
569 So set the status to null. */
570
571 hr_utility.trace('In active row exists. User wanted ' ||
572 'to insert a row. Set the status to null.');
573 lv_valid_status := null;
574 pay_taxability_rules_api.update_taxability_rules
575 (p_validate => FALSE
576 ,p_jurisdiction => p_jurisdiction
577 ,p_tax_type => ptt_tax_types(i)
578 ,p_tax_category => p_tax_category
579 ,p_classification_id => p_classification_id
580 ,p_taxability_rules_date_id => p_taxability_rule_date_id
581 ,p_legislation_code => p_legislation_code
582 ,p_status => lv_valid_status
583 );
584
585 end if;
586 end if; /*lv_status Check*/
587 end if; /*Seed Row. */
588 else
589 hr_utility.trace('Invalid Value for Column: '||
590 ptt_tax_types(i)||' = '||ptt_tax_type_values(i));
591 hr_utility.set_message(801, 'PAY_DATAPUMP_INVALID_DATA');
592 hr_utility.set_message_token('COLUMN', ptt_tax_types(i));
593 hr_utility.raise_error;
594 end if;
595 end if;
596 end loop;
597 hr_utility.set_location('outside endloop call_api_for_taxability_rules',100);
598
599 END call_api_for_taxability_rules;
600
601
602 FUNCTION get_tax_category_code(p_classification in varchar2
603 ,p_legislation_code in varchar2
604 ,p_tax_category in varchar2)
605 RETURN VARCHAR2
606 IS
607 -- Bug# 5652699
608 CURSOR c_tax_category_code(cp_lookup_type in varchar2,
609 cp_lookup_code in varchar2) IS
610 SELECT lookup_code
611 FROM hr_lookups
612 WHERE upper(lookup_type) = upper(ltrim(rtrim(cp_lookup_type)))
613 AND (
614 upper(lookup_code) = upper(ltrim(rtrim(cp_lookup_code)))
615 OR
616 upper(meaning) = upper(ltrim(rtrim(cp_lookup_code))));
617
618 l_lookup_type VARCHAR2(50);
619 l_lookup_code VARCHAR2(11);
620
621 BEGIN
622
623 hr_utility.trace('Begin FUNCTION get_tax_category_code');
624 hr_utility.trace('p_classification : ' || p_classification);
625 hr_utility.trace('p_legislation_code : ' || p_legislation_code);
626 hr_utility.trace('p_tax_category : ' || p_tax_category);
630 hr_utility.trace('l_lookup_type : ' || l_lookup_type);
627 l_lookup_type := replace(replace(p_legislation_code || '_' ||
628 upper(p_classification), ' ', '_'),'-', '_');
629
631
632 hr_utility.trace('SELECT lookup_code FROM hr_lookups WHERE upper(lookup_type) = upper(ltrim(rtrim(cp_lookup_type))) AND upper(meaning) = upper(ltrim(rtrim(cp_lookup_code)))');
633
634 OPEN c_tax_category_code(l_lookup_type,p_tax_category);
635 FETCH c_tax_category_code INTO l_lookup_code;
636 if c_tax_category_code%NOTFOUND then
637 hr_utility.trace('Lookup Code not found for lookup_type '
638 || l_lookup_type ||
639 ' and for tax category '
640 || p_tax_category);
641 /* Raise error that needs to be send to the sheet. */
642 -- Bug# 5652699
643 hr_utility.set_message(801, 'PAY_DATAPUMP_INVALID_DATA');
644 hr_utility.set_message_token('COLUMN', p_tax_category);
645 hr_utility.raise_error;
646 end if;
647 CLOSE c_tax_category_code;
648 hr_utility.trace('returning l_lookup_code : ' || l_lookup_code);
649 return(l_lookup_code);
650
651 END get_tax_category_code;
652
653
654 PROCEDURE get_local_jurisdiction(p_state_abbrev in varchar2
655 ,p_county_name in varchar2
656 ,p_city_name in varchar2
657 ,p_local_jd_code out nocopy varchar2)
658 IS
659 cursor c_get_state_code (cp_state_abbrev in varchar2) is
660 select state_code
661 from pay_us_states pus
662 where pus.state_abbrev = upper(cp_state_abbrev);
663
664 cursor c_get_county_code(cp_state_code in varchar2
665 ,cp_county_name in varchar2) is
666 select puc.county_code
667 from pay_us_counties puc
668 where puc.state_code = cp_state_code
669 and upper(puc.county_name) = upper(cp_county_name);
670
671 cursor c_get_city_code(cp_state_code in varchar2
672 ,cp_city_name in varchar2) is
673 select city_code from pay_us_city_names
674 where state_code = cp_state_code
675 and upper(city_name) = upper(cp_city_name);
676
677 lv_state_code VARCHAR2(2);
678 lv_county_code VARCHAR2(3);
679 lv_city_code VARCHAR2(5);
680
681 BEGIN
682 lv_state_code := '00';
683 lv_county_code := '000';
684 lv_city_code := '0000';
685
686 open c_get_state_code(p_state_abbrev);
687 fetch c_get_state_code into lv_state_code;
688 close c_get_state_code;
689
690 if p_county_name is not null then
691 open c_get_county_code(lv_state_code, p_county_name);
692 fetch c_get_county_code into lv_county_code;
693 close c_get_county_code;
694 end if;
695
696 if p_city_name is not null then
697 open c_get_city_code(lv_state_code, p_city_name);
698 fetch c_get_city_code into lv_city_code;
699 close c_get_city_code;
700 end if;
701
702 p_local_jd_code := lv_state_code || '-' ||
703 lv_county_code || '-' ||
704 lv_city_code;
705
706 END get_local_jurisdiction;
707
708 PROCEDURE create_taxability_rules
709 (p_classification_id IN NUMBER
710 ,p_tax_category IN VARCHAR2
711 ,p_jurisdiction IN VARCHAR2 default null
712 ,p_legislation_code IN VARCHAR2 default null
713 ,p_input_tax_type_value1 IN VARCHAR2 default null
714 ,p_input_tax_type_value2 IN VARCHAR2 default null
715 ,p_input_tax_type_value3 IN VARCHAR2 default null
716 ,p_input_tax_type_value4 IN VARCHAR2 default null
717 ,p_input_tax_type_value5 IN VARCHAR2 default null
718 ,p_input_tax_type_value6 IN VARCHAR2 default null
719 ,p_input_tax_type_value7 IN VARCHAR2 default null
720 ,p_input_tax_type_value8 IN VARCHAR2 default null
721 ,p_input_tax_type_value9 IN VARCHAR2 default null
722 ,p_input_tax_type_value10 IN VARCHAR2 default null
723 ,p_input_tax_type_value11 IN VARCHAR2 default null
724 ,p_spreadsheet_identifier IN VARCHAR2 default null
725 )
726 IS
727
728 CURSOR c_classification(cp_classification_id in number
729 ,cp_legislation_code in varchar2) IS
730 select classification_name
731 from pay_element_classifications
732 where classification_id = cp_classification_id
733 and legislation_code = cp_legislation_code;
734
735
736 lv_procedure VARCHAR2(72);
737 ln_taxability_rule_date_id NUMBER;
738 lv_tax_category VARCHAR2(20);
739 lb_is_local BOOLEAN;
740 lv_classification_name VARCHAR2(100);
741 lv_jurisdiction_code VARCHAR2(11);
742
743 BEGIN
744 lv_procedure := g_package||'create_taxability_rules';
745 hr_utility.set_location('Entering:'|| lv_procedure, 10);
746 lb_is_local := FALSE;
747 lv_jurisdiction_code := p_jurisdiction;
748
749 hr_utility.trace('p_classification_id = '||to_char(p_classification_id));
750 hr_utility.trace('p_tax_category = '||p_tax_category);
751 hr_utility.trace('p_jurisdiction = '||p_jurisdiction);
752 hr_utility.trace('p_legislation_code = '||p_legislation_code);
753 hr_utility.trace('p_input_tax_type_value1 = '||p_input_tax_type_value1);
754 hr_utility.trace('p_input_tax_type_value2 = '||p_input_tax_type_value2);
755 hr_utility.trace('p_input_tax_type_value3 = '||p_input_tax_type_value3);
759
756 hr_utility.trace('p_input_tax_type_value4 = '||p_input_tax_type_value4);
757 hr_utility.trace('p_input_tax_type_value5 = '||p_input_tax_type_value5);
758 hr_utility.trace('p_input_tax_type_value6 = '||p_input_tax_type_value6);
760 transfer_tax_type_values
761 (p_input_tax_type_value1 => p_input_tax_type_value1
762 ,p_input_tax_type_value2 => p_input_tax_type_value2
763 ,p_input_tax_type_value3 => p_input_tax_type_value3
764 ,p_input_tax_type_value4 => p_input_tax_type_value4
765 ,p_input_tax_type_value5 => p_input_tax_type_value5
766 ,p_input_tax_type_value6 => p_input_tax_type_value6
767 ,p_input_tax_type_value7 => p_input_tax_type_value7
768 ,p_input_tax_type_value8 => p_input_tax_type_value8
769 ,p_input_tax_type_value9 => p_input_tax_type_value9
770 ,p_input_tax_type_value10 => p_input_tax_type_value10
771 ,p_input_tax_type_value11 => p_input_tax_type_value11);
772
773 if ln_taxability_rule_date_id is null then
774 ln_taxability_rule_date_id
775 := get_taxability_rule_date_id
776 (p_legislation_code => p_legislation_code
777 ,p_effective_date => sysdate);
778 end if;
779
780 hr_utility.trace('Before checking p_jurisdiction = '||p_jurisdiction);
781 hr_utility.trace('p_legislation_code = '||p_legislation_code);
782
783 open c_classification(p_classification_id,
784 p_legislation_code);
785 fetch c_classification INTO lv_classification_name;
786 if c_classification%NOTFOUND then
787 hr_utility.trace('No classification id found.');
788 hr_utility.raise_error;
789 end if;
790 close c_classification;
791
792 hr_utility.trace('lv_classification_name = '|| lv_classification_name);
793 initialize;
794
795 if p_legislation_code = 'US' then
796 if ltrim(rtrim(p_jurisdiction)) = '00-000-0000' then
797 hr_utility.trace('p_jurisdiction is Federal');
798 create_us_federal_taxability(
799 p_classification => lv_classification_name);
800
801 elsif substr(p_jurisdiction,1 ,2) <> '00' and
802 substr(p_jurisdiction,4,3) = '000' and
803 substr(p_jurisdiction,8,4) = '0000' then
804 create_us_state_taxability(
805 p_classification => lv_classification_name);
806
807 elsif length(p_jurisdiction) = 2 and
808 p_input_tax_type_value1 is not null and
809 p_input_tax_type_value2 is null then
810 create_us_loc_county_tax_rule(
811 p_classification => lv_classification_name);
812 lb_is_local:= TRUE;
813
814 elsif length(p_jurisdiction) = 2 and
815 p_input_tax_type_value1 is null and
816 p_input_tax_type_value2 is not null then
817 create_us_loc_city_tax_rule(
818 p_classification => lv_classification_name);
819 lb_is_local:= TRUE;
820
821 end if;
822
823 if lb_is_local then
824 get_local_jurisdiction(p_state_abbrev => p_jurisdiction
825 ,p_county_name => p_input_tax_type_value1
826 ,p_city_name => p_input_tax_type_value2
827 ,p_local_jd_code => lv_jurisdiction_code);
828
829 if lv_classification_name in ('Supplemental Earnings',
830 'Imputed Earnings') then
831 pay_ac_taxability_wrapper.ltt_tax_type_values(1)
832 := upper(p_input_tax_type_value3);
833 pay_ac_taxability_wrapper.ltt_tax_type_values(2)
834 := upper(p_input_tax_type_value4);
835
836 pay_ac_taxability_wrapper.ltt_tax_type_values(3)
837 := null;
838 pay_ac_taxability_wrapper.ltt_tax_type_values(4)
839 := null;
840 hr_utility.trace('Local Earnings. Tax type value1 = '||
841 pay_ac_taxability_wrapper.ltt_tax_type_values(1));
842 hr_utility.trace('Local Earnings. Tax type value 2= '||
843 pay_ac_taxability_wrapper.ltt_tax_type_values(2));
844
845 elsif lv_classification_name in ('Pre-Tax Deductions') then
846 pay_ac_taxability_wrapper.ltt_tax_type_values(1)
847 := upper(p_input_tax_type_value3);
848 pay_ac_taxability_wrapper.ltt_tax_type_values(2)
849 := null;
850 pay_ac_taxability_wrapper.ltt_tax_type_values(3)
851 := null;
852 hr_utility.trace('Local Earnings. Tax type value1 = '||
853 pay_ac_taxability_wrapper.ltt_tax_type_values(1));
854
855 end if;
856 end if;
857
858 elsif p_legislation_code = 'CA' then
859
860 if ltrim(rtrim(p_jurisdiction)) = '00-000-0000' then
861 hr_utility.trace('p_jurisdiction is Federal');
862 create_ca_federal_taxability;
863 elsif substr(p_jurisdiction,4,3) = '000' and
864 substr(p_jurisdiction,8,4) = '0000' then
865
866 create_ca_prov_taxability(
867 p_classification => lv_classification_name
868 ,p_jurisdiction => p_jurisdiction);
869 end if;
870 end if;
871
872 hr_utility.trace('Before call_api_for_taxability_rules');
873 hr_utility.trace('pay_ac_taxability_wrapper.ltt_tax_type_values(1) = '||
874 pay_ac_taxability_wrapper.ltt_tax_type_values(1));
875
876 hr_utility.trace('ltt_tax_type_values(1) = '||
877 ltt_tax_type_values(1));
881
878 hr_utility.trace('ltt_tax_type_values.count = '||
879 to_char(ltt_tax_type_values.count));
880 hr_utility.trace('ltt_tax_types.count = '||to_char(ltt_tax_types.count));
882 lv_tax_category := get_tax_category_code(
883 p_classification => lv_classification_name
884 ,p_legislation_code => p_legislation_code
885 ,p_tax_category => p_tax_category);
886
887
888 call_api_for_taxability_rules
889 (p_classification_id => p_classification_id
890 ,p_jurisdiction => lv_jurisdiction_code
891 ,p_legislation_code => p_legislation_code
892 ,p_tax_category => lv_tax_category
893 ,p_taxability_rule_date_id => ln_taxability_rule_date_id
894 ,ptt_tax_types => ltt_tax_types
895 ,ptt_tax_type_values => ltt_tax_type_values);
896
897 EXCEPTION
898
899 WHEN OTHERS THEN
900 hr_utility.set_location(' Leaving:'||lv_procedure, 80);
901 RAISE;
902
903 END create_taxability_rules;
904
905
906 PROCEDURE update_taxability_rules
907 (p_classification_id IN NUMBER
908 ,p_tax_category IN VARCHAR2
909 ,p_jurisdiction IN VARCHAR2
910 ,p_legislation_code IN VARCHAR2
911 ,p_input_tax_type_value1 IN VARCHAR2
912 ,p_input_tax_type_value2 IN VARCHAR2
913 ,p_input_tax_type_value3 IN VARCHAR2
914 ,p_input_tax_type_value4 IN VARCHAR2
915 ,p_input_tax_type_value5 IN VARCHAR2
916 ,p_input_tax_type_value6 IN VARCHAR2
917 ,p_input_tax_type_value7 IN VARCHAR2
918 ,p_input_tax_type_value8 IN VARCHAR2
919 ,p_input_tax_type_value9 IN VARCHAR2
920 ,p_input_tax_type_value10 IN VARCHAR2
921 ,p_spreadsheet_identifier IN VARCHAR2
922 )
923 IS
924
925 lv_procedure VARCHAR2(72);
926
927 BEGIN
928 lv_procedure := g_package||'update_taxability_rules';
929 savepoint upd_taxability_rule;
930 hr_utility.set_location('Entering:'|| lv_procedure, 10);
931
932 hr_utility.set_location('Leaving:'|| lv_procedure, 20);
933
934 EXCEPTION
935
936 WHEN OTHERS THEN
937 hr_utility.set_location(' Leaving:'||lv_procedure, 80);
938 raise;
939
940 END update_taxability_rules;
941
942 BEGIN
943 g_package := 'pay_ac_taxability_rules_wrapper.';
944 --hr_utility.trace_on(null,'ram');
945 end pay_ac_taxability_wrapper;