[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAXABILITY_RULES_PKG
Source
1 PACKAGE BODY pay_us_taxability_rules_pkg as
2 /* $Header: paysuetw.pkb 120.1 2005/09/27 00:36:53 sackumar noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20
21 Name : pay_us_taxability_rules_pkg
22
23 Description : This package holds building blocks used in maintenace
24 of US taxability rule using PAY_TAXABILITY_RULES
25 table.
26
27 Uses : hr_utility
28
29 Change List
30 -----------
31 Date Name Vers Bug No Description
32 ---- ---- ---- ------ -----------
33 NOV-11-1993 RMAMGAIN 1.0 Created with following proc.
34 get_or_update
35 05-OCT-1994 RFINE 40.1 Added 'PAY_' to package name.
36 05-OCT-1994 RFINE 40.2 ... and suffix '_PKG'
37 03-APR-1995 gpaytonm 40.3 Modified to handle populating
38 classification id.
39 18-APR-1995 gpaytonm 40.4 Changed occurences of
40 'W_SIT/FIT'
41 to 'NW_SIT/FIT'.
42 19-APR-1995 gpaytonm 40.5 Modified to consider
43 classification id
44 on query and delete.
45 28-SEP-1995 gpaytonm 40.6 Added chk_mutually_exclusive
46 to insure
47 that only one overtime
48 category at a
49 time can be added
50 included in WC
51 25-JUN-1996 D JENG added handler for CITY, COUNTY
52 and SCHOOL
53 09-MAR-1999 A. Rundell 115.2 Removed unnecessary MLS change.
54 03-JUN-1999 A Handa 115.4 Added legislation_code
55 check in select
56 from
57 pay_element_classifications and
58 pay_taxability_rules.
59 09-JUL-1999 R. Murthy 115.5 Modified selects and inserts
60 from pay_taxability_rules
61 to include the new not-null
62 column taxability_rules_date_id
63 and hard-coded legislation_code
64 as US, since Canada has it's
65 own package.
66
67 02-JUN-2003 asasthan 115.6 2904628 New column has been added to
68 pay_taxability_rules.
69 The status column now carries
70 a value of 'D' if the rule
71 is DELETED(D) by either
72 Oracle or by customer.
73 22-sep-2003 asasthan 115.7 3152061 Check has been added
74 to trash those balances
75 that have been fed.
76 23-sep-2003 asasthan 115.8 3152061 Changes to date joins
77 in get_balance_type
78 23-sep-2003 asasthan 115.9 3152061 added chk for legislation
79 on pay_element_types_f
80 23-sep-2003 asasthan 115.10 3152061 removed chk for legislation
81 on pay_element_types_f
82 03-nov-2003 tclewis 115.11 2845480 Added code to handle AEIC.
83 state level box 6.
84 10-DEC-2003 tclewis 115.14 Changed tax type for AEIC to
85 STEIC from EIC.
86 26-SEP-2005 tclewis 115.15 4537348 Modified conditions in get_or_update procedure
87 to handle the t_box6 at state level.
88
89 ************************************************************************/
90
91 PROCEDURE get_or_update(X_MODE VARCHAR2,
92 X_CONTEXT VARCHAR2,
93 X_JURISDICTION VARCHAR2,
94 X_TAX_CAT VARCHAR2,
95 X_classification_id NUMBER,
96 X_BOX1 IN OUT NOCOPY VARCHAR2,
97 X_BOX2 IN OUT NOCOPY VARCHAR2,
98 X_BOX3 IN OUT NOCOPY VARCHAR2,
99 X_BOX4 IN OUT NOCOPY VARCHAR2,
100 X_BOX5 IN OUT NOCOPY VARCHAR2,
101 X_BOX6 IN OUT NOCOPY VARCHAR2) IS
102 -- Local Variables
103 P_ret VARCHAR2(1) := 'N';
104 P_User_Id Number := FND_PROFILE.Value('USER_ID');
105 P_login_id Number := FND_PROFILE.Value('LOGIN_ID');
106 P_i Number := 0;
107 p_taxability_rules_date_id number;
108 p_valid_date_from date;
109 p_valid_date_to date;
110 p_legislation_code VARCHAR2(2) := 'US';
111
112 procedure get_date_info(P_legislation_code VARCHAR2 default 'US',
113 P_taxability_rules_date_id out nocopy number,
114 P_valid_date_from out nocopy date,
115 P_valid_date_to out nocopy date,
116 P_date DATE default sysdate) is
117 CURSOR csr_get_info is
118 select TRD.TAXABILITY_RULES_DATE_ID,
119 TRD.VALID_DATE_FROM, TRD.VALID_DATE_TO
120 from PAY_TAXABILITY_RULES_DATES TRD
121 where p_date between TRD.VALID_DATE_FROM and
122 TRD.VALID_DATE_TO
123 and TRD.LEGISLATION_CODE = p_legislation_code;
124 begin
125 OPEN csr_get_info;
126 FETCH csr_get_info INTO P_taxability_rules_date_id, P_valid_date_from,
127 P_valid_date_to;
128 CLOSE csr_get_info;
129 --
130 END get_date_info;
131
132 FUNCTION check_row_exist(P_jurisdiction VARCHAR2,
133 P_tax_type VARCHAR2,
134 P_category VARCHAR2,
135 P_classification_id NUMBER,
136 P_taxability_rules_date_id NUMBER)
137 RETURN VARCHAR2 is
138 --
139 ret VARCHAR2(1) := 'N';
140 --
141 CURSOR csr_check is
142 select 'Y'
143 from PAY_TAXABILITY_RULES
144 where JURISDICTION_CODE = P_jurisdiction
145 and TAX_TYPE = P_tax_type
146 and TAX_CATEGORY = P_category
147 and CLASSIFICATION_ID = p_classification_id
148 and TAXABILITY_RULES_DATE_ID = p_taxability_rules_date_id
149 and nvl(STATUS,'VALID') <> 'D'
150 and LEGISLATION_CODE = 'US';
151 begin
152 OPEN csr_check;
153 FETCH csr_check INTO ret;
154 IF csr_check%NOTFOUND then
155 ret := 'N';
156 else
157 ret := 'Y';
158 end if;
159 CLOSE csr_check;
160 --
161 RETURN ret;
162 --
163 END check_row_exist;
164
165 /********************************************************************
166 ** This function is called when a new taxability rule in inserted.
167 ** The function checks if the row is there, if it exists the status
168 ** needs to be changed from DELETED(D) to Valid(Null) row. If the
169 ** row is not there, insert a new row.
170 ********************************************************************/
171 FUNCTION insert_rules(P_jurisdiction VARCHAR2,
172 P_tax_type VARCHAR2,
173 P_category VARCHAR2,
174 P_classification_id NUMBER,
175 P_taxability_rules_date_id NUMBER)
176 RETURN NUMBER IS
177
178 ret number := 0;
179 begin
180
181 update pay_taxability_rules
182 set status = null
183 where jurisdiction_code = P_jurisdiction
184 and tax_type = P_tax_type
185 and tax_category = P_category
186 and classification_id = p_classification_id
187 and taxability_rules_date_id = P_taxability_rules_date_id;
188
189 if sql%notfound then
190 INSERT INTO pay_taxability_rules(
191 JURISDICTION_CODE, TAX_TYPE, TAX_CATEGORY,
192 classification_id, TAXABILITY_RULES_DATE_ID,
193 LEGISLATION_CODE,
194 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
195 CREATED_BY, CREATION_DATE)
196 VALUES (
197 P_jurisdiction, P_tax_type, P_category,
198 P_classification_id, P_taxability_rules_date_id,
199 'US',
200 SYSDATE, P_user_id, P_Login_Id,
201 P_user_id, SYSDATE);
202
203 IF SQL%NOTFOUND then
204 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
205 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.insert');
206 hr_utility.set_message_token('STEP','1');
207 hr_utility.raise_error;
208 end if;
209 end if;
210
211 RETURN ret;
212 END insert_rules;
213
214
215 /********************************************************************
216 ** This function is called when a taxability rule has to deleted.
217 ** Now instead of physically deleting the row from the database
218 ** we will update the status of the row to DELETED(D).
219 ********************************************************************/
220 FUNCTION delete_rules(P_jurisdiction VARCHAR2,
221 P_tax_type VARCHAR2,
222 P_category VARCHAR2,
223 p_classification_id NUMBER,
224 P_taxability_rules_date_id NUMBER)
225 RETURN NUMBER IS
226 --
227 ret number := 0;
228 begin
229
230 update pay_taxability_rules
231 set status = 'D'
232 where jurisdiction_code = P_jurisdiction
233 and tax_type = P_tax_type
234 and tax_category = P_category
235 and classification_id = p_classification_id
236 and taxability_rules_date_id = P_taxability_rules_date_id;
237
238 IF SQL%NOTFOUND then
239 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
240 hr_utility.set_message_token('PROCEDURE','pay_us_taxability_rules_pkg.delete');
241 hr_utility.set_message_token('STEP','1');
242 hr_utility.raise_error;
243 end if;
244 --
245 RETURN ret;
246 --
247 END delete_rules;
248 --
249 --
250 -- This function checks whether another row for a given tax type,
251 -- jursidiction and classification id exist. This is primarily for tax type
252 -- of WC - as only one row can exist at any time for category of OT or S
253 -- however, if in the future for any reason this rules is extended to other
254 -- tax types this functin is stil applicable.
255 --
256 -- RETURN TRUE if rules are not mutually exclusive
257 --
258 FUNCTION chk_mutually_exclusive ( p_jurisdiction_code VARCHAR2,
259 p_tax_category VARCHAR2,
260 p_tax_type VARCHAR2,
261 p_classification_id NUMBER )
262 RETURN BOOLEAN IS
263 --
264 -- declare local cursor
265 --
266 CURSOR get_other_rule IS
267 SELECT 'Y'
268 FROM pay_taxability_rules
269 WHERE jurisdiction_code = p_jurisdiction_code
270 AND tax_type = p_tax_type
271 AND classification_id = p_classification_id
272 AND tax_category <> p_tax_category
273 AND legislation_code = 'US';
274 --
275 l_exists VARCHAR2(1) := 'N';
276 --
277 BEGIN
278 --
279 OPEN get_other_rule;
280 FETCH get_other_rule INTO l_exists;
281 CLOSE get_other_rule;
282 --
283 IF l_exists = 'N'
284 THEN
285 RETURN FALSE;
286 ELSE
287 RETURN TRUE;
288 END IF;
289 --
290 END chk_mutually_exclusive;
291 --
292 --
293 -- MAIN PROCEDURE
294 begin
295 IF X_MODE = 'QUERY' then
296 get_date_info(p_legislation_code, p_taxability_rules_date_id,
297 p_valid_date_from, p_valid_date_to);
298 if X_CONTEXT = 'FEDERAL' then
299 X_BOX1 := check_row_exist(X_jurisdiction,
300 'EIC',
301 X_tax_cat,
302 X_classification_id,
303 p_taxability_rules_date_id);
304 X_BOX2 := check_row_exist(X_jurisdiction,
305 'FIT',
306 X_tax_cat,
307 X_classification_id,
308 p_taxability_rules_date_id);
309 X_BOX3 := check_row_exist(X_jurisdiction,
310 'FUTA',
311 X_tax_cat,
312 X_classification_id,
313 p_taxability_rules_date_id);
314 X_BOX4 := check_row_exist(X_jurisdiction,
315 'MEDICARE',
316 X_tax_cat,
317 X_classification_id,
318 p_taxability_rules_date_id);
319 X_BOX5 := check_row_exist(X_jurisdiction,
320 'SS',
321 X_tax_cat,
322 X_classification_id,
323 p_taxability_rules_date_id);
324 X_BOX6 := check_row_exist(X_jurisdiction,
325 'NW_FIT',
326 X_tax_cat,
327 X_classification_id,
328 p_taxability_rules_date_id);
329 elsif X_CONTEXT = 'STATE' then
330 X_BOX1 := check_row_exist(X_jurisdiction,
331 'WC',
332 X_tax_cat,
333 X_classification_id,
334 p_taxability_rules_date_id);
335 X_BOX2 := check_row_exist(X_jurisdiction,
336 'SIT',
337 X_tax_cat,
338 X_classification_id,
339 p_taxability_rules_date_id);
340 X_BOX3 := check_row_exist(X_jurisdiction,
341 'SUI',
342 X_tax_cat,
343 X_classification_id,
344 p_taxability_rules_date_id);
345 X_BOX4 := check_row_exist(X_jurisdiction,
346 'SDI',
347 X_tax_cat,
348 X_classification_id,
349 p_taxability_rules_date_id);
350 X_BOX5 := check_row_exist(X_jurisdiction,
351 'NW_SIT',
352 X_tax_cat,
353 X_classification_id,
354 p_taxability_rules_date_id);
355 X_BOX6 := check_row_exist(X_jurisdiction,
356 'STEIC',
357 X_tax_cat,
358 X_classification_id,
359 p_taxability_rules_date_id);
360 elsif X_CONTEXT = 'WC' then
361 X_BOX1 := check_row_exist(X_jurisdiction,
362 'WC',
363 X_tax_cat,
364 X_classification_id,
365 p_taxability_rules_date_id);
366
367 --==========================
368 --
369 -- CITY, COUNTY and SCHOOL
370 --
371 --==========================
372 elsif X_CONTEXT = 'COUNTY'
373 then
374 X_BOX6 := check_row_exist(X_jurisdiction,
375 'COUNTY',
376 X_tax_cat,
377 X_classification_id,
378 p_taxability_rules_date_id);
379 X_BOX5 := check_row_exist(X_jurisdiction,
380 'NW_COUNTY',
381 X_tax_cat,
382 X_classification_id,
383 p_taxability_rules_date_id);
384 elsif (X_CONTEXT = 'CITY')
385 then
386 X_BOX6 := check_row_exist(X_jurisdiction,
387 'CITY',
391 X_BOX5 := check_row_exist(X_jurisdiction,
388 X_tax_cat,
389 X_classification_id,
390 p_taxability_rules_date_id);
392 'NW_CITY',
393 X_tax_cat,
394 X_classification_id,
395 p_taxability_rules_date_id);
396 elsif (X_CONTEXT = 'SCHOOL')
397 then
398 X_BOX6 := check_row_exist(X_jurisdiction,
399 'SCHOOL',
400 X_tax_cat,
401 X_classification_id,
402 p_taxability_rules_date_id);
403 X_BOX5 := check_row_exist(X_jurisdiction,
404 'NW_SCHOOL',
405 X_tax_cat,
406 X_classification_id,
407 p_taxability_rules_date_id);
408
409 end if;
410 --
411 elsif X_MODE = 'UPDATE' then
412 P_User_Id := FND_PROFILE.Value('USER_ID');
413 P_Login_Id := FND_PROFILE.Value('LOGIN_ID');
414 select taxability_rules_date_id
415 into p_taxability_rules_date_id
416 from pay_taxability_rules_dates
417 where sysdate between valid_date_from and valid_date_to
418 and legislation_code = p_legislation_code;
419
420 if X_CONTEXT = 'FEDERAL' then
421 P_ret := check_row_exist(X_jurisdiction,
422 'EIC',
423 X_tax_cat,
424 X_classification_id,
425 p_taxability_rules_date_id);
426 if P_ret = X_box1 then
427 null;
428 elsif P_ret = 'Y' and X_box1 = 'N' then
429 P_i := delete_rules(X_jurisdiction,'EIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
430 elsif P_ret = 'N' and X_box1 = 'Y' then
431 P_i := insert_rules(X_jurisdiction,'EIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
432 end if;
433 --
434 P_ret := check_row_exist(X_jurisdiction,
435 'FIT',
436 X_tax_cat,
437 X_classification_id, p_taxability_rules_date_id);
438 if P_ret = X_box2 then
439 null;
440 elsif P_ret = 'Y' and X_box2 = 'N' then
441 P_i := delete_rules(X_jurisdiction,'FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
442 elsif P_ret = 'N' and X_box2 = 'Y' then
443 P_i := insert_rules(X_jurisdiction,'FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
444 end if;
445 --
446 P_ret := check_row_exist(X_jurisdiction,
447 'FUTA',
448 X_tax_cat,
449 X_classification_id, p_taxability_rules_date_id);
450 if P_ret = X_box3 then
451 null;
452 elsif P_ret = 'Y' and X_box3 = 'N' then
453 P_i := delete_rules(X_jurisdiction,'FUTA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
454 elsif P_ret = 'N' and X_box3 = 'Y' then
455 P_i := insert_rules(X_jurisdiction,'FUTA',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
456 end if;
457 --
458 P_ret := check_row_exist(X_jurisdiction,
459 'MEDICARE',
460 X_tax_cat,
461 X_classification_id, p_taxability_rules_date_id);
462 if P_ret = X_box4 then
463 null;
464 elsif P_ret = 'Y' and X_box4 = 'N' then
465 P_i := delete_rules(X_jurisdiction,'MEDICARE',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
466 elsif P_ret = 'N' and X_box4 = 'Y' then
467 P_i := insert_rules(X_jurisdiction,'MEDICARE',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
468 end if;
469 --
470 P_ret := check_row_exist(X_jurisdiction,
471 'SS',
472 X_tax_cat,
473 X_classification_id, p_taxability_rules_date_id);
474 if P_ret = X_box5 then
475 null;
476 elsif P_ret = 'Y' and X_box5 = 'N' then
477 P_i := delete_rules(X_jurisdiction,'SS',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
478 elsif P_ret = 'N' and X_box5 = 'Y' then
479 P_i := insert_rules(X_jurisdiction,'SS',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
480 end if;
481 --
482 P_ret := check_row_exist(X_jurisdiction,
483 'NW_FIT',
484 X_tax_cat,
485 X_classification_id, p_taxability_rules_date_id);
486 if P_ret = X_box6 then
487 null;
488 elsif P_ret = 'Y' and X_box6 = 'N' then
489 P_i := delete_rules(X_jurisdiction,'NW_FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
490 elsif P_ret = 'N' and X_box6 = 'Y' then
491 P_i := insert_rules(X_jurisdiction,'NW_FIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
492 end if;
493 --
494 elsif X_CONTEXT = 'STATE' then
495 P_ret := check_row_exist(X_jurisdiction,
496 'WC',
497 X_tax_cat,
498 X_classification_id, p_taxability_rules_date_id);
499 if P_ret = X_box1 then
500 null;
501 elsif P_ret = 'Y' and X_box1 = 'N' then
502 P_i := delete_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
503 elsif P_ret = 'N' and X_box1 = 'Y' then
504 P_i := insert_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
505 end if;
506 --
510 X_classification_id, p_taxability_rules_date_id);
507 P_ret := check_row_exist(X_jurisdiction,
508 'STEIC',
509 X_tax_cat,
511 if P_ret = X_box6 then
512 null;
513 elsif P_ret = 'Y' and X_box6 = 'N' then
514 P_i := delete_rules(X_jurisdiction,'STEIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
515 elsif P_ret = 'N' and X_box6 = 'Y' then
516 P_i := insert_rules(X_jurisdiction,'STEIC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
517 end if;
518 --
519 P_ret := check_row_exist(X_jurisdiction,
520 'SIT',
521 X_tax_cat,
522 X_classification_id, p_taxability_rules_date_id);
523 if P_ret = X_box2 then
524 null;
525 elsif P_ret = 'Y' and X_box2 = 'N' then
526 P_i := delete_rules(X_jurisdiction,'SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
527 elsif P_ret = 'N' and X_box2 = 'Y' then
528 P_i := insert_rules(X_jurisdiction,'SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
529 end if;
530 --
531 P_ret := check_row_exist(X_jurisdiction,
532 'SUI',
533 X_tax_cat,
534 X_classification_id, p_taxability_rules_date_id);
535 if P_ret = X_box3 then
536 null;
537 elsif P_ret = 'Y' and X_box3 = 'N' then
538 P_i := delete_rules(X_jurisdiction,'SUI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
539 elsif P_ret = 'N' and X_box3 = 'Y' then
540 P_i := insert_rules(X_jurisdiction,'SUI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
541 end if;
542 --
543 P_ret := check_row_exist(X_jurisdiction,
544 'SDI',
545 X_tax_cat,
546 X_classification_id, p_taxability_rules_date_id);
547 if P_ret = X_box4 then
548 null;
549 elsif P_ret = 'Y' and X_box4 = 'N' then
550 P_i := delete_rules(X_jurisdiction,'SDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
551 elsif P_ret = 'N' and X_box4 = 'Y' then
552 P_i := insert_rules(X_jurisdiction,'SDI',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
553 end if;
554 --
555 P_ret := check_row_exist(X_jurisdiction,
556 'NW_SIT',
557 X_tax_cat,
558 X_classification_id, p_taxability_rules_date_id);
559 if P_ret = X_box5 then
560 null;
561 elsif P_ret = 'Y' and X_box5 = 'N' then
562 P_i := delete_rules(X_jurisdiction,'NW_SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
563 elsif P_ret = 'N' and X_box5 = 'Y' then
564 P_i := insert_rules(X_jurisdiction,'NW_SIT',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
565 end if;
566 --
567 elsif X_CONTEXT = 'WC' then
568 P_ret := check_row_exist(X_jurisdiction,
569 'WC',
570 X_tax_cat,
571 X_classification_id, p_taxability_rules_date_id);
572 if P_ret = X_box1 then
573 null;
574 elsif P_ret = 'Y' and X_box1 = 'N' then
575 P_i := delete_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
576 elsif P_ret = 'N' and X_box1 = 'Y' then
577 --
578 -- check whether OT Categor row is mutually exclusive
579 --
580 IF ( chk_mutually_exclusive ( p_jurisdiction_code => X_jurisdiction,
581 p_tax_category => X_tax_cat,
582 p_tax_type => 'WC',
583 p_classification_id => X_classification_id ))
584 THEN
585 hr_utility.set_message(801, 'HR_50000_WC_ONLY_INC_ONE_OT');
586 hr_utility.raise_error;
587 END IF;
588 --
589 P_i := insert_rules(X_jurisdiction,'WC',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
590 end if;
591 --
592
593 --==========================
594 --
595 -- CITY, COUNTY and SCHOOL
596 --
597 --==========================
598 --COUNTY
599
600 elsif X_CONTEXT = 'COUNTY' then
601
602 P_ret := check_row_exist(X_jurisdiction,
603 'COUNTY',
604 X_tax_cat,
605 X_classification_id, p_taxability_rules_date_id);
606 if P_ret = X_box6 then
607 null;
608 elsif P_ret = 'Y' and X_box6 = 'N' then
609 P_i := delete_rules(X_jurisdiction,'COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
610 elsif P_ret = 'N' and X_box6 = 'Y' then
611
612 P_i := insert_rules(X_jurisdiction,'COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
613 end if;
614
615 P_ret := check_row_exist(X_jurisdiction,
616 'NW_COUNTY',
617 X_tax_cat,
618 X_classification_id, p_taxability_rules_date_id);
619 if P_ret = X_box5 then
620 null;
621 elsif P_ret = 'Y' and X_box5 = 'N' then
622 P_i := delete_rules(X_jurisdiction,'NW_COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
623 elsif P_ret = 'N' and X_box5 = 'Y' then
624
625 P_i := insert_rules(X_jurisdiction,'NW_COUNTY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
626 end if;
627
628 --CITY
629
630 elsif X_CONTEXT = 'CITY' then
631
632 P_ret := check_row_exist(X_jurisdiction,
633 'CITY',
637 null;
634 X_tax_cat,
635 X_classification_id, p_taxability_rules_date_id);
636 if P_ret = X_box6 then
638 elsif P_ret = 'Y' and X_box6 = 'N' then
639 P_i := delete_rules(X_jurisdiction,'CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
640 elsif P_ret = 'N' and X_box6 = 'Y' then
641
642 P_i := insert_rules(X_jurisdiction,'CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
643 end if;
644
645 P_ret := check_row_exist(X_jurisdiction,
646 'NW_CITY',
647 X_tax_cat,
648 X_classification_id, p_taxability_rules_date_id);
649 if P_ret = X_box5 then
650 null;
651 elsif P_ret = 'Y' and X_box5 = 'N' then
652 P_i := delete_rules(X_jurisdiction,'NW_CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
653 elsif P_ret = 'N' and X_box5 = 'Y' then
654
655 P_i := insert_rules(X_jurisdiction,'NW_CITY',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
656 end if;
657
658 --SCHOOL
659
660 elsif X_CONTEXT = 'SCHOOL' then
661
662 P_ret := check_row_exist(X_jurisdiction,
663 'SCHOOL',
664 X_tax_cat,
665 X_classification_id, p_taxability_rules_date_id);
666 if P_ret = X_box6 then
667 null;
668 elsif P_ret = 'Y' and X_box6 = 'N' then
669 P_i := delete_rules(X_jurisdiction,'SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
670 elsif P_ret = 'N' and X_box6 = 'Y' then
671
672 P_i := insert_rules(X_jurisdiction,'SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
673 end if;
674
675 P_ret := check_row_exist(X_jurisdiction,
676 'NW_SCHOOL',
677 X_tax_cat,
678 X_classification_id, p_taxability_rules_date_id);
679 if P_ret = X_box5 then
680 null;
681 elsif P_ret = 'Y' and X_box5 = 'N' then
682 P_i := delete_rules(X_jurisdiction,'NW_SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
683 elsif P_ret = 'N' and X_box5 = 'Y' then
684
685 P_i := insert_rules(X_jurisdiction,'NW_SCHOOL',X_tax_cat, X_classification_id, p_taxability_rules_date_id);
686 end if;
687
688
689
690 end if;
691 --
692 end if;
693 --
694 END get_or_update;
695 --
696 --
697 -- function to get the classification id fro 'Earnings'.
698 -- This is the default classification id for the WC OT category
699 -- tax rules from the WC form (DJC).
700 --
701 FUNCTION get_classification_id (p_classification_name VARCHAR2) RETURN NUMBER IS
702 --
703 -- declare cursor
704 --
705 CURSOR get_class_id IS
706 SELECT pec.classification_id
707 FROM pay_element_classifications PEC
708 WHERE PEC.classification_name = p_classification_name
709 AND PEC.legislation_code = 'US';
710 --
711 l_classification_id NUMBER(9);
712 --
713 BEGIN
714 --
715 OPEN get_class_id;
716 FETCH get_class_id INTO l_classification_id;
717 CLOSE get_class_id;
718 --
719 RETURN l_classification_id;
720 --
721 END get_classification_id;
722
723 /*****************************************************************************
724 Name : get_balance_type
725 Purpose : This procedure gets balance_type_id for the tax_type
726 and calls core functions to trash latest balances
727 and associated run balances when taxability rules are changed.
728 Arguments : p_tax_type - Only federal level tax types
729 p_tax_category - ensures correct lookup_types are touched
730 p_taxability_rules_date_id
731 p_legislation_code
732
733 *****************************************************************************/
734
735 PROCEDURE get_balance_type(p_tax_type in varchar2,
736 p_tax_category in varchar2,
737 p_taxability_rules_date_id in number,
738 p_legislation_code in varchar2,
739 p_classification_id in number) is
740
741 cursor c_chk_element_taxcat(cp_classification_id in number,
742 cp_tax_category in varchar2,
743 cp_trash_date in date) is
744 select 'Y' from dual
745 where exists (
746 select element_type_id
747 from pay_element_types_f pet
748 where pet.classification_id = cp_classification_id
749 and pet.element_information1 = cp_tax_category
750 and effective_end_date >= cp_trash_date
751 ) ;
752
753
754 cursor c_get_balance_type(cp_tax_type in varchar2,
755 cp_tax_category in varchar2,
756 cp_trash_date in date,
757 cp_classification_id in number ) is
758 select balance_type_id,balance_name
759 from pay_balance_types pbt
760 where pbt.tax_type = cp_tax_type
761 and pbt.legislation_code = 'US'
762 and exists
763 ( select 1
764 from pay_balance_feeds_f pbf,
765 pay_input_values_f piv,
766 pay_element_types_f pet
767 where pbf.balance_type_id = pbt.balance_type_id
768 and pbf.effective_end_date >= cp_trash_date
769 and piv.input_value_id = pbf.input_value_id
770 and pbf.effective_start_date between piv.effective_start_date
771 and piv.effective_end_date
775 and pet.classification_id = cp_classification_id
772 and pet.element_type_id = piv.element_type_id
773 and pbf.effective_start_date between pet.effective_start_date
774 and pet.effective_end_date
776 and pet.element_information1 = cp_tax_category
777 );
778
779
780 cursor c_session is
781 select trunc(effective_date,'Y')
782 from fnd_sessions
783 where session_id = userenv('sessionid');
784
785
786 lv_exist_flag varchar2(1) :='N';
787 ln_balance_type_id pay_balance_types.balance_type_id%TYPE := 0;
788 lv_balance_name pay_balance_types.balance_name%TYPE;
789 ln_business_group_id pay_balance_types.business_group_id%TYPE;
790 lv_legislation_code pay_balance_types.legislation_code%TYPE;
791 ld_effective_date date;
792
793
794 Begin
795 hr_utility.trace('tax_type :'||p_tax_type);
796 hr_utility.trace('tax_category :'||p_tax_category);
797 hr_utility.trace('p_legislation_code :'||p_legislation_code);
798 hr_utility.trace('p_taxability_rules_date_id :'||to_char(p_taxability_rules_date_id));
799 hr_utility.trace('p_classification_id :'||to_char(p_classification_id));
800
801
802 if p_legislation_code = 'US' then
803
804 if p_tax_type in ('CSDI',
805 'EIC',
806 'FIT',
807 'FUTA',
808 'GDI',
809 'MEDICARE',
810 'NW_FIT',
811 'SS') then
812
813 open c_session ;
814 fetch c_session into ld_effective_date ;
815 if c_session%notfound then ld_effective_date := trunc(sysdate,'Y') ;
816 end if;
817 close c_session ;
818 hr_utility.trace('ld_effective_date :'||to_char(ld_effective_date));
819
820 open c_chk_element_taxcat(p_classification_id,
821 p_tax_category,
822 ld_effective_date);
823
824 fetch c_chk_element_taxcat into lv_exist_flag;
825 close c_chk_element_taxcat;
826
827 if lv_exist_flag = 'Y' then
828
829
830 open c_get_balance_type(p_tax_type,
831 p_tax_category,
832 ld_effective_date,
833 p_classification_id);
834 loop
835 fetch c_get_balance_type into ln_balance_type_id
836 ,lv_balance_name;
837 exit when c_get_balance_type%notfound;
838 hr_utility.trace('balance_type_id :'||to_char(ln_balance_type_id));
839 hr_utility.trace('balance_name :'||lv_balance_name);
840
841
842
843 /* for each of balance fetched call the core
844 procedure to trash all person and assignment
845 latest balances */
846
847 hrassact.trash_latest_balances(ln_balance_type_id,
848 ld_effective_date);
849
850
851 pay_balance_pkg.invalidate_run_balances(ln_balance_type_id,
852 ld_effective_date);
853
854
855 end loop;
856 close c_get_balance_type;
857
858 end if ; /*element exists */
859
860 end if; /* p_tax_type */
861
862
863 end if; /* p_legislation_code = 'US' */
864 END;
865 --begin
866 --hr_utility.trace_on (null, 'XTR');
867
868 END pay_us_taxability_rules_pkg;