1 PACKAGE BODY pay_get_tax_exists_pkg AS
2 /* $Header: pytaxexi.pkb 120.23.12010000.6 2008/10/14 10:07:16 emunisek ship $ */
3
4 /*
5 We are using the contexts JURISDICTION_CODE, DATE_EARNED, TAX_UNIT_ID
6 , ASSIGNMENT_ID Different values on p_type that we can have: SIT_WK,SIT_RS,
7 SDI_EE, SDI_ER, CITY_WK, CITY_RS, COUNTY_WK, COUNTY_RS, SUI taxes. We are
8 also using NR but the Vertex formula doesn't ever call the function with a
9 p_type of NR. We have SDI_EE and SDI_ER because Rhode Island for example has EE
10 SDI but not ER SDI so why keep the ER balances. Only SIT and Local exemption
11 rules can be set from the GRE level. We are not going to touch school tax as
12 that is dependent on Residence location. We are not going to touch head tax
13 the only way we would get a balance for head tax is if we take out any head tax.
14 If the user marks a city as non exempt then we check the jit table and see if
15 the city has a city tax. We are placing the City and County call to this
16 function only if the jurisidiction being calculated is that of a city or county
17 jurisdiction. If it isn't then the Vertex formulae logic will bypass this
18 function. The responsibility of the NR certificate being valid will be held
19 responsible by the customer. So if the user checks the NR flag then we will not
20 withhold SIT. SUI taxes we are calculating based on if their is a value in the j
21 it tables and also if it is a retiree GRE. We are calling a retiree GRE if the GRE has a value for Transmitter Control Code.
22 */
23
24 /*
25 New function that will check for limit tax exempts at the assignment level and
26 accordingly return value to the main function.
27 */
28
29 FUNCTION assignment_tax_exists (p_tax_type IN varchar2,
30 p_assign_id IN number,
31 p_date_earned IN date,
32 p_jurisdiction_code IN varchar2)
33 RETURN VARCHAR2 IS
34
35 l_sdi varchar2(5);
36 l_sui varchar2(5);
37 l_futa varchar2(5);
38 l_medi varchar2(5);
39 l_fica varchar2(5);
40 l_change_date varchar2(20);
41
42 CURSOR sdi_exempt IS
43 SELECT DECODE(pest.sdi_exempt, NULL, 'N', pest.sdi_exempt)
44 FROM pay_us_emp_state_tax_rules_f pest
45 WHERE pest.assignment_id = p_assign_id
46 AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
47 pest.effective_start_date AND pest.effective_end_date
48 AND pest.state_code = SUBSTR(p_jurisdiction_code, 1, 2);
49
50 CURSOR sui_exempt IS
51 SELECT DECODE(pest.sui_exempt, NULL, 'N', pest.sui_exempt)
52 FROM pay_us_emp_state_tax_rules_f pest
53 WHERE pest.assignment_id = p_assign_id
54 AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
55 pest.effective_start_date AND pest.effective_end_date
56 AND pest.state_code = SUBSTR(p_jurisdiction_code, 1, 2);
57
58 CURSOR futa_exempt IS
59 SELECT DECODE(pest.futa_tax_exempt, NULL, 'N', pest.futa_tax_exempt)
60 FROM pay_us_emp_fed_tax_rules_f pest
61 WHERE pest.assignment_id = p_assign_id
62 AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
63 pest.effective_start_date AND pest.effective_end_date;
64
65 CURSOR fica_exempt IS
66 SELECT DECODE(pest.ss_tax_exempt, NULL, 'N', pest.ss_tax_exempt)
67 FROM pay_us_emp_fed_tax_rules_f pest
68 WHERE pest.assignment_id = p_assign_id
69 AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
70 pest.effective_start_date AND pest.effective_end_date;
71
72 CURSOR medi_exempt IS
73 SELECT DECODE(pest.medicare_tax_exempt, NULL, 'N', pest.medicare_tax_exempt)
74 FROM pay_us_emp_fed_tax_rules_f pest
75 WHERE pest.assignment_id = p_assign_id
76 AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
77 pest.effective_start_date AND pest.effective_end_date;
78
79 BEGIN
80 hr_utility.set_location('pay_get_tax_exists_pkg.assignment_tax_exists', 210);
81 hr_utility.trace('The date earned is : ' || p_date_earned);
82 hr_utility.trace('The assign id is : ' || TO_CHAR(p_assign_id));
83 hr_utility.trace('The jurisdiction code is : ' || p_jurisdiction_code);
84
85 l_change_date := TO_CHAR(p_date_earned, 'dd-mm-yyyy');
86
87 hr_utility.trace('The change date earned is : ' || SUBSTR(l_change_date, 7));
88 hr_utility.trace('The tax type passed is : ' || p_tax_type);
89
90 IF p_tax_type = 'SDI' THEN
91
92 OPEN sdi_exempt;
93 FETCH sdi_exempt INTO l_sdi;
94
95 IF sdi_exempt%NOTFOUND THEN
96 l_sdi := 'N';
97 END IF;
98 CLOSE sdi_exempt;
99
100 hr_utility.trace('SDI Exempt : ' || l_sdi);
101
102 RETURN(l_sdi);
103
104 ELSIF p_tax_type = 'SUI' THEN
105
106 OPEN sui_exempt;
107 FETCH sui_exempt INTO l_sui;
108
109 IF sui_exempt%NOTFOUND THEN
110 l_sui := 'N';
111 END IF;
112 CLOSE sui_exempt;
113
114 hr_utility.trace('SUI Exempt : ' || l_sui);
115
116 RETURN(l_sui);
117
118 ELSIF p_tax_type = 'FUTA' THEN
119
120 OPEN futa_exempt;
121 FETCH futa_exempt INTO l_futa;
122
123 IF futa_exempt%NOTFOUND THEN
124 l_futa := 'N';
125 END IF;
126 CLOSE futa_exempt;
127
128 hr_utility.trace('FUTA Exempt : ' || l_futa);
129
130 RETURN(l_futa);
131
132 ELSIF p_tax_type = 'FICA' THEN
133
134 OPEN fica_exempt;
135 FETCH fica_exempt INTO l_fica;
136
137 IF fica_exempt%NOTFOUND THEN
138 l_fica := 'N';
139 END IF;
140 CLOSE fica_exempt;
141
142 hr_utility.trace('FICA Exempt : ' || l_fica);
143
144 RETURN(l_fica);
145
146 ELSIF p_tax_type = 'MEDICARE' THEN
147
148 OPEN medi_exempt;
149 FETCH medi_exempt INTO l_medi;
150
151 IF medi_exempt%NOTFOUND THEN
152 l_medi := 'N';
153 END IF;
154 CLOSE medi_exempt;
155
156 hr_utility.trace('MEDI Exempt : ' || l_medi);
157
158 RETURN(l_medi);
159
160 END IF;
161
162 END assignment_tax_exists;
163
164 FUNCTION GET_RESIDENCE_AS_OF_1ST_JAN (p_assign_id number,
165 l_date_earned varchar2)
166 RETURN varchar2 IS
167
168 CURSOR c_override_state_county IS
169 SELECT puc.state_code || '-' || puc.county_code
170 FROM pay_us_counties puc,
171 pay_us_states pus,
172 per_addresses pa,
173 per_assignments_f paf
174 WHERE paf.assignment_id = p_assign_id
175 AND paf.person_id = pa.person_id
176 AND pa.primary_flag = 'Y'
177 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
178 paf.effective_start_date AND paf.effective_end_date
179 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
180 pa.date_from AND NVL(pa.date_to, TO_DATE('12-31-4712', 'mm-dd-yyyy'))
181 AND pus.state_abbrev = pa.add_information17 --override state
182 AND pus.state_code = '15' --for INDIANA
183 AND puc.state_code = pus.state_code
184 AND puc.county_name = pa.add_information19;
185
186 l_rs_county_code varchar2(15);
187 l_res_adr_date_start date;
188
189 BEGIN
190
191 /*{*/
192 /*
193 * check to see if there is an override if there is then we do not check
194 * for 1st Jan else see if the default county is valid as of 1st Jan.
195 */
196 -- hr_utility.trace_on(null,'TAXEXIST');
197 hr_utility.trace('5000 START of function GET_RESIDENCE_AS_OF_1ST_JAN ');
198
199 OPEN c_override_state_county;
200 FETCH c_override_state_county
201 INTO l_rs_county_code;
202 hr_utility.trace('5010 OVERRIDE County Code : '||l_rs_county_code);
203 IF c_override_state_county%NOTFOUND THEN
204 hr_utility.trace('5020 OVERRIDE County Code NOT found ');
205 /*{*/
206 /*
207 * override does not exists so get the actual address / override as of
208 * 1st Jan .
209 */
210
211 BEGIN
212 /*{*/
213 hr_utility.trace('5030 Fetching County Code for Res Jurisdiction ');
214 SELECT puc.state_code || '-' || puc.county_code,
215 pa.date_from
216 INTO l_rs_county_code, l_res_adr_date_start
217 FROM pay_us_counties puc,
218 pay_us_states pus,
219 per_addresses pa,
220 per_assignments_f paf
221 WHERE paf.assignment_id = p_assign_id
222 AND paf.person_id = pa.person_id
223 AND pa.primary_flag = 'Y'
224 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
225 paf.effective_start_date AND paf.effective_end_date
226 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
227 pa.date_from AND NVL(pa.date_to, TO_DATE('12-31-4712', 'mm-dd-yyyy'))
228 AND pus.state_abbrev = pa.region_2 --actual state
229 AND pus.state_code = '15' --for INDIANA
230 AND puc.state_code = pus.state_code
231 AND puc.county_name = pa.region_1; --actual county
232 hr_utility.trace('5040 Resident County Code Found : '||l_rs_county_code);
233 hr_utility.trace('5050 Resident County Code Eff Start Date : '
234 ||to_char(l_res_adr_date_start,'DD-MON-YYYY'));
235 /* This condition added to fix bug # 3710639 */
236 IF (l_res_adr_date_start <= TRUNC(TO_DATE(l_date_earned, 'DD-MM-YYYY'),'Y'))
237 THEN
238 hr_utility.trace('5055 Resident County Eff Start Date '||
239 to_char(l_res_adr_date_start,'DD-MON-YYYY'));
240 hr_utility.trace('5060 As of 1st Jan Date '||
241 to_char(TRUNC(TO_DATE(l_date_earned, 'DD-MM-YYYY'),'Y'),'DD-MON-YYYY'));
242 hr_utility.trace('5070 County Code Returned '||l_rs_county_code);
243 ELSE
244 hr_utility.trace('5075 Resident County Eff Start Date '||
245 to_char(l_res_adr_date_start,'DD-MON-YYYY'));
246 hr_utility.trace('5076 As of 1st Jan Date '||
247 to_char(TRUNC(TO_DATE(l_date_earned, 'DD-MM-YYYY'),'Y'),'DD-MON-YYYY'));
248 hr_utility.trace('5077 Resident County Code Eff Start Date '||
249 to_char(l_res_adr_date_start,'DD-MON-YYYY'));
250 hr_utility.trace('5080 County Code Returned 00-000 ');
251 l_rs_county_code := '00-000';
252 END IF;
253 CLOSE c_override_state_county;
254 hr_utility.trace('5090 END of function GET_RESIDENCE_AS_OF_1ST_JAN ');
255 RETURN (l_rs_county_code);
256 EXCEPTION --the residence county is not in Indiana
257 WHEN others THEN
258 hr_utility.trace('5100 Exception: '||substr(sqlerrm,1,30));
259 hr_utility.trace('5110 END of function GET_RESIDENCE_AS_OF_1ST_JAN ');
260 CLOSE c_override_state_county;
261 l_rs_county_code := '00-000';
262 RETURN (l_rs_county_code);
263 /*}*/
264 END;
265 /*}*/
266 ELSE
267 hr_utility.trace('5120 OVERRIDE County Code Found ');
268 hr_utility.trace('5130 County Code Retunred : '||l_rs_county_code);
269 END IF;
270 CLOSE c_override_state_county;
271 hr_utility.trace('5140 END of function GET_RESIDENCE_AS_OF_1ST_JAN ');
272 RETURN(l_rs_county_code);
273 /*}*/
274 END; --function GET_RESIDENCE_AS_OF_1ST_JAN
275
276
277 FUNCTION GET_LOCATION_AS_OF_1ST_JAN (p_assign_id number,
278 p_date_earned varchar2, /*Bug#6768746: date earned is required to fetch active assignments attached to the employee*/
279 p_effective_date varchar2,
280 p_juri_code varchar2)
281 RETURN boolean IS
282
283 l_date_earned date;
284 l_is_exist number(2);
285 /*Bug#6768746: IN County Tax not deducted from second assignments created
286 after first of January. County tax should be withheld even when the current
287 assignment's work location is valid for other assignments as of 1st January.
288 */
289 l_is_valid_location boolean ;
290
291 cursor c_multiple_assignments is
292 select assignment_id
293 from per_all_assignments_f
294 where TO_DATE(p_date_earned, 'dd-mm-yyyy') between effective_start_date
295 and effective_end_date
296 and person_id in (select person_id
297 from per_all_assignments_f
298 where assignment_id =p_assign_id);
299 /*Bug#6768746: Changes ends here*/
300
301 CURSOR c_override_location IS
302 SELECT 'Y'
303 FROM pay_us_counties puc,
304 pay_us_states pus,
305 hr_locations hl,
306 hr_soft_coding_keyflex hscf,
307 per_assignments_f paf
308 WHERE paf.assignment_id = p_assign_id
309 AND l_date_earned BETWEEN /*6519715*/
310 paf.effective_start_date AND paf.effective_end_date
311 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
312 AND hscf.segment18 = hl.location_id
313 AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev --actual state
314 AND pus.state_code = '15' --check only for Indiana
315 AND puc.state_code = pus.state_code
316 AND NVL(hl.loc_information19, hl.region_1) = puc.county_name --actual county
317 AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
318 AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
319
320 l_curr_county_code varchar2(2);
321
322 /*
323 * is this the work county as of 1st Jan.
324 */
325 BEGIN
326 /*{*/
327 /*
328 * check for override
329 */
330 /*Bug#6068328: Truncating date earned to 'Y' as location as of
331 1st Jan has to be picked*/
332 l_date_earned := TRUNC(TO_DATE(p_effective_date, 'DD-MM-YYYY'),'Y');
333
334
335 hr_utility.trace('l_date_earned: ' || l_date_earned);
336 OPEN c_override_location;
337
338 FETCH c_override_location
339 INTO l_curr_county_code;
340
341 IF c_override_location%NOTFOUND THEN
342 /*{*/
343 BEGIN
344 /*{*/
345 hr_utility.trace('c_override_location%NOTFOUND ');
346 l_is_valid_location := FALSE ;
347 /*Bug#6768746: Check work location of this current assignment is valid
348 as of 1st January. Check all the active assignments attached with this
349 employee.*/
350 for rec_multiassgn in c_multiple_assignments
351 LOOP
352 hr_utility.trace('rec_multiassgn.assignment_id: '|| to_char(rec_multiassgn.assignment_id));
353 SELECT count(1)
354 INTO l_is_exist
355 FROM pay_us_counties puc,
356 pay_us_states pus,
357 hr_locations hl,
358 hr_soft_coding_keyflex hscf,
359 per_assignments_f paf
360 WHERE paf.assignment_id = rec_multiassgn.assignment_id
361 AND l_date_earned BETWEEN /*6519715*/
362 paf.effective_start_date AND paf.effective_end_date
363 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
364 AND paf.location_id = hl.location_id
365 AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev
366 --actual state
367 AND pus.state_code = '15' --check only for Indiana
368 AND puc.state_code = pus.state_code
369 AND NVL(hl.loc_information19, hl.region_1) = puc.county_name
370 --actual county
371 AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
372 AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
373
374 IF l_is_exist > 0 then
375 hr_utility.trace('l_is_exist >0');
376 l_is_valid_location := TRUE ;
377 end if;
378 end loop;
379 CLOSE c_override_location;
380 RETURN (l_is_valid_location);
381
382 EXCEPTION
383 WHEN others THEN
384 CLOSE c_override_location;
385 hr_utility.trace('5100 Exception: '||substr(sqlerrm,1,30));
386 RETURN (FALSE);
387 /* SELECT count(1)
391 hr_locations hl,
388 INTO l_is_exist
389 FROM pay_us_counties puc,
390 pay_us_states pus,
392 hr_soft_coding_keyflex hscf,
393 per_assignments_f paf
394 WHERE paf.assignment_id = p_assign_id
395 AND l_date_earned BETWEEN /*6519715
396 paf.effective_start_date AND paf.effective_end_date
397 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
398 AND paf.location_id = hl.location_id
399 AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev
400 --actual state
401 AND pus.state_code = '15' --check only for Indiana
402 AND puc.state_code = pus.state_code
403 AND NVL(hl.loc_information19, hl.region_1) = puc.county_name
404 --actual county
405 AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
406 AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
407
408 CLOSE c_override_location;
409 --RETURN (TRUE);
410 hr_utility.trace('query count: ' || to_char(l_is_exist));
411 IF l_is_exist = 0 then
412 RETURN (FALSE);
413 else
414 return(TRUE);
415 end if;
416 EXCEPTION
417 WHEN others THEN
418 CLOSE c_override_location;
419 RETURN (FALSE); */
420 /*}*/
421 END;
422 /*}*/
423 END IF;
424
425 CLOSE c_override_location;
426 RETURN(TRUE);
427 /*}*/
428 END GET_LOCATION_AS_OF_1ST_JAN;
429
430 FUNCTION DOES_TAX_EXISTS (p_juri_code varchar2,
431 l_date_earned varchar2,
432 p_tax_unit_id number,
433 p_assign_id number,
434 p_pact_id number,
435 p_called_from varchar2)
436 RETURN varchar2 IS
437
438 l_county_tax_exists varchar2(10);
439
440 BEGIN
441 /*{*/
442
443 SELECT COUNTYTAX.county_tax
444 INTO l_county_tax_exists
445 FROM pay_us_county_tax_info_f COUNTYTAX
446 WHERE COUNTYTAX.JURISDICTION_CODE = SUBSTR(p_juri_code, 1, 6) || '-0000'
447 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
448 COUNTYTAX.effective_start_date AND COUNTYTAX.effective_end_date;
449
450 /*
451 * If tax exists then check does the customer have tax defined for the state
452 */
453 IF l_county_tax_exists = 'Y' THEN
454 /*{*/
455 IF p_called_from = 'COUNTY_WK' THEN
456 /*{*/
457 l_county_tax_exists := get_tax_exists(p_juri_code,
458 TO_DATE(l_date_earned, 'DD-MM-YYYY'),
459 p_tax_unit_id,
460 p_assign_id,
461 p_pact_id,
462 'SIT_WK',
463 p_called_from);
464 /*}*/
465 ELSE
466 /*{*/
467 l_county_tax_exists := get_tax_exists(p_juri_code,
468 TO_DATE(l_date_earned, 'DD-MM-YYYY'),
469 p_tax_unit_id,
470 p_assign_id,
471 p_pact_id,
472 'SIT_RS',
473 p_called_from);
474 /*}*/
475 END IF;
476 hr_utility.trace('DOES_TAX_EXISTS County Tax Exists : '
477 || l_county_tax_exists);
478 RETURN(l_county_tax_exists);
479 /*}*/
480 ELSE
481 /*{*/
482 hr_utility.trace('DOES_TAX_EXISTS County Tax Exists : '
483 || l_county_tax_exists);
484 RETURN(l_county_tax_exists);
485 /*}*/
486 END IF;
487 EXCEPTION
488 WHEN others THEN
489 hr_utility.trace('DOES_TAX_EXISTS EXception Raised ');
490 hr_utility.trace('DOES_TAX_EXISTS County Tax Exists : N');
491 RETURN('N');
492 /*}*/
493 END DOES_TAX_EXISTS;
494
495
496 FUNCTION STATE_LOCAL_LEVEL_TAX (p_tax_unit_id number,
497 l_state_abbrev varchar2,
498 p_juri_code varchar2 )
499 RETURN varchar2 IS
500
501 l_rs_county_code varchar2(10);
502 l_rs_county_tax_exists varchar2(10);
503 l_state_level_tax_exists varchar2(10);
504 l_local_level_tax_exists varchar2(10);
505 -- This variable will be used to check the local Tax Rules defined
506 -- for the GRE or not.
507 l_wh_work_localities varchar2(100);
508 l_jd_type varchar2(100);
509 BEGIN
510 --{
511 --
512 -- WORK_LOCALITIES Rules added to fix bug # 3953687
513 --
514 SELECT DECODE(hoi.ORG_INFORMATION19, 'ALL', 'Y',
515 'LOCALITIES', 'N',
516 'WORK_LOCALITIES','N',
517 'Y')
518 INTO l_state_level_tax_exists
519 FROM hr_organization_information hoi
520 WHERE hoi.org_information_context = 'State Tax Rules'
521 AND hoi.organization_id = p_tax_unit_id
525 -- Following query added for OH/KY courtesy withholding enhancement
522 AND hoi.org_information1 = l_state_abbrev;
523 BEGIN
524 --
526 -- Bug # 3953687
527 l_wh_work_localities := 'Y';
528 SELECT DECODE(hoi.ORG_INFORMATION19,
529 'WORK_LOCALITIES','N',
530 'Y') -- State Tax Rules level
531 INTO l_wh_work_localities
532 FROM hr_organization_information hoi
533 WHERE hoi.org_information_context = 'State Tax Rules'
534 AND hoi.organization_id = p_tax_unit_id
535 AND hoi.org_information1 = l_state_abbrev;
536 IF l_wh_work_localities = 'Y'
537 THEN
538 --
539 -- Check if there's any locality defined under Local tax rule for a locality
540 --
541 BEGIN -- Check if the locality is exempt
542 --{
543 SELECT DECODE(hoi.ORG_INFORMATION3,'Y','N','N','Y','Y')
544 --local level have to check if exempt
545 INTO l_local_level_tax_exists
546 FROM HR_ORGANIZATION_INFORMATION hoi
547 WHERE hoi.ORG_INFORMATION_CONTEXT = 'Local Tax Rules'
548 AND hoi.organization_id = p_tax_unit_id
549 AND hoi.org_information1 = SUBSTR(p_juri_code,1,6)||'-0000';
550 /* Bug 2934494
551 AND hoi.org_information1 = SUBSTR(p_juri_code,1,6)||' - 0000';
552 */
553 hr_utility.set_location('pay_get_tax_exists_pkg.STATE_LOCAL_LEVEL_TAX', 52);
554 hr_utility.trace('County Income Tax Exists : '||l_local_level_tax_exists);
555 RETURN(l_local_level_tax_exists);
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 -- If there is no value then return l_state_level_tax_exists
560 hr_utility.set_location(
561 'pay_get_tax_exists_pkg.STATE_LOCAL_LEVEL_TAX', 53);
562 hr_utility.trace(
563 'No rexord defined in Local Tax rules for this Locality : '
564 || l_local_level_tax_exists);
565 /*
566 * This will return Y, if ALL Localities are selected under State Tax Rules
567 * and there's no record defined under local tax rules for the given
568 * locality and will return N, if option "LOCALITIES defined under Local Tax
569 * Rules" is selected under State Tax Rules and there's no record defined
570 * under local tax rules for the given locality
571 */
572 RETURN (l_state_level_tax_exists);
573 --}
574 END; -- end check for locality exemption
575 ELSE
576 --{
577 /*
578 If Employer setup for State's Resident Tax is Only Withhold Tax
579 at Work Location return No, so that Resident tax is not withhold
580 for the resident jurisdiction
581 */
582 hr_utility.set_location('py_gt_tax_exists_pkg.STATE_LOCAL_LEVEL_TAX',54);
583 hr_utility.trace('Local Income Tax Exists : ' ||l_wh_work_localities);
584 --
585 -- This is added to tax a jurisdiction if it is tagged OR
586 -- resident jurisdiction is same Work
587 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
588 hr_utility.trace('Jurisdiction Type : '||l_jd_type);
589 if (nvl(l_jd_type,'NL') = 'RT' OR
590 nvl(l_jd_type,'NL') = 'RW' OR
591 nvl(l_jd_type,'NL') = 'HW' -- added to fix bug # 4463475
592 ) then
593 --{
594 hr_utility.trace('COUNTY Tax to be withheld : Y');
595 return('Y');
596 --}
597 else
598 hr_utility.trace('County Tax to be withheld : ' || l_wh_work_localities);
599 RETURN l_wh_work_localities;
600 end if;
601 --}
602 END IF;
603 END;
604 EXCEPTION /* The ct has nothing at the EI level set up */
605 WHEN OTHERS THEN
606 l_local_level_tax_exists := 'Y';
607 hr_utility.set_location('py_gt_tax_exists_pkg.STATE_LOCAL_LEVEL_TAX', 55);
608 hr_utility.trace('Local Income Tax Exists : ' || l_local_level_tax_exists);
609 RETURN l_local_level_tax_exists;
610 --}
611 END STATE_LOCAL_LEVEL_TAX;
612
613 /* This version matches with the forms call */
614 FUNCTION get_tax_exists (p_juri_code IN VARCHAR2,
615 p_date_earned IN DATE,
616 p_tax_unit_id IN NUMBER,
617 p_assign_id IN NUMBER,
618 p_type IN VARCHAR2
619 ) RETURN VARCHAR2 IS
620 -- This function when called from get_wage_accum_rule ,p_date_earned will be given Date Paid for bug 7441418
621 BEGIN
622
623 RETURN get_tax_exists(p_juri_code,
624 p_date_earned,
625 p_tax_unit_id,
626 p_assign_id,
627 NULL,
628 p_type);
629
630 END get_tax_exists;
631
632 /* This version matches with the formula function call */
633 FUNCTION get_tax_exists (p_juri_code IN varchar2,
634 p_date_earned IN date,
635 p_tax_unit_id IN NUMBER,
636 p_assign_id IN NUMBER,
637 p_pact_id IN NUMBER,
638 p_type IN varchar2)
642 /*{*/
639 RETURN VARCHAR2 IS
640
641 BEGIN
643 -- hr_utility.trace_on(null,'EXIST');
644 RETURN get_tax_exists(p_juri_code,
645 p_date_earned,
646 p_tax_unit_id,
647 p_assign_id,
648 p_pact_id,
649 p_type,
650 'F');
651 /*
652 * This additional parameter(for p_call) defaults to 'F' which stands for
653 * formula, meaning this function was called from the vertex formula.
654 * When this function is called from any other place, CITY_WK
655 * or COUNTY_WK, then it will bypass the 'NR' check.
656 */
657 /*}*/
658 END get_tax_exists;
659
660
661 FUNCTION get_tax_exists (p_juri_code IN varchar2,
662 p_date_earned IN date,
663 p_tax_unit_id IN NUMBER,
664 p_assign_id IN NUMBER,
665 p_pact_id IN NUMBER,
666 p_type IN varchar2,
667 p_call IN varchar2)
668 RETURN VARCHAR2 IS
669
670 l_sit_wk_exists varchar2(2);
671 l_sit_rs_exists varchar2(2);
672 l_sdi_ee_exists varchar2(2);
673 l_sdi_er_exists varchar2(2);
674 l_county_wk_exists varchar2(2);
675 l_county_rs_exists varchar2(2);
676 l_city_wk_exists varchar2(2);
677 l_ht_wk_exists varchar2(2);
678 l_city_rs_exists varchar2(2);
679 l_school_exists varchar2(2);
680 l_sui_exists varchar2(2);
681 l_state_abbrev varchar2(2);
682 l_exists varchar2(2);
683 l_step varchar2(10);
684 l_nr_exists varchar2(2);
685 l_date_earned varchar2(20);
686 l_date varchar2(20);
687 l_payroll_installed boolean := FALSE;
688 l_org_info2 varchar2(2);
689 l_org_info19 varchar2(2);
690 l_rs_county_as_of_1st_jan varchar2(15);
691 l_county_tax_exists varchar2(10);
692 l_does_tax_exists varchar2(10);
693 l_state_local_level_tax varchar2(10);
694 l_misc1_state_tax varchar2(2);
695 l_eic_rs_exists varchar2(2);
696 l_eic_wk_exists varchar2(2);
697 l_wc_exists varchar2(2);
698 -- This variable will be used to check the local Tax Rules defined
699 -- for the GRE or not. p_type = CITY_RS
700 l_wh_work_localities varchar2(100);
701 l_jd_type varchar2(100);
702 l_local_tax_rules_type varchar2(100);
703 l_indiana_override varchar2(10);
704 l_across_years varchar2(2);
705
706 cursor override_state is
707 select nvl(ADDR.add_information17,'ZZ')
708 from
709 per_addresses ADDR,
710 per_all_assignments_f ASSIGN
711 where TO_DATE(l_date_earned, 'dd-mm-yyyy')
712 between ASSIGN.effective_start_date
713 and ASSIGN.effective_end_date
714 and ASSIGN.assignment_id = p_assign_id
715 and ADDR.person_id = ASSIGN.person_id
716 and ADDR.primary_flag = 'Y'
717 and TO_DATE(l_date_earned, 'dd-mm-yyyy')
718 between nvl(ADDR.date_from, TO_DATE(l_date_earned, 'dd-mm-yyyy'))
719 and nvl(ADDR.date_to, TO_DATE(l_date_earned, 'dd-mm-yyyy'));
720
721
722 BEGIN
723 /*{*/
724 /*
725 * Check to see if US Payroll is installed
726 */
727
728 l_payroll_installed := hr_utility.chk_product_install(
729 p_product =>'Oracle Payroll',
730 p_legislation => 'US');
731 IF l_payroll_installed THEN
732 /*{*/
733
734 -- hr_utility.trace_on(null,'TAXEXIST');
735 hr_utility.set_location('Entering pay_get_tax_exists_pkg.get_tax_exists',1);
736 hr_utility.trace('We are changing the date format, the before date is: '
737 ||p_date_earned);
738 l_date_earned := TO_CHAR(p_date_earned,'dd-mm-yyyy');
739
740 hr_utility.trace('The tax we are determining is : '|| p_type);
741 hr_utility.trace('The jurisdiction is : ' || p_juri_code);
742 hr_utility.trace('The date earned after change of format is : '
743 || l_date_earned);
744 hr_utility.trace('The tax unit id is : ' || p_tax_unit_id);
745 hr_utility.trace('The assignment id is : ' || p_assign_id);
746
747 /*
748 * Let's take the jurisdiction code and get a state abbrev
749 */
750
751 SELECT DISTINCT pus.state_abbrev
752 INTO l_state_abbrev
753 FROM pay_us_states pus
754 WHERE pus.state_code = SUBSTR(p_juri_code, 1, 2);
755
756 hr_utility.trace('The state abbrev is:' || l_state_abbrev);
757
758
759 /*
760 * Let's only allow the function to work if date_earned is in 1999
761 *
762 * We will remove this code later on in the year of 1999
763 * We only have this so customer do not have to apply this between last
764 * 1998 run and first 1999 run
765 */
766
767 IF SUBSTR(l_date_earned, 7) < 1999 THEN
768 /*{*/
769 BEGIN
770 /*{*/
774 hr_utility.trace('The year is before 1999 so we will say tax exists '
771 hr_utility.trace('The year of the date earned is: '
772 || SUBSTR(l_date_earned, 7));
773 l_exists := 'Y';
775 ||' =Y');
776 RETURN l_exists;
777 /*}*/
778 END;
779 /*}*/
780 ELSE
781 /*{*/
782 hr_utility.trace('The year of the date earned is after 1998 so we will '
783 || ' allow the function to calc');
784 NULL;
785 /*}*/
786 END IF;
787
788 /*
789 * Let's start with the res state
790 */
791
792 IF p_type = 'SIT_RS' THEN /* 1 */
793 /*{*/
794 BEGIN
795 /*{*/
796 SELECT DISTINCT sit_exists
797 INTO l_sit_rs_exists
798 FROM pay_us_state_tax_info_f
799 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
800 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
801 effective_start_date AND effective_end_date;
802
803 /*
804 * If l_sit_rs_exists = Y then the jit table says there is SIT,
805 * if it equals N then the jit table says there is no SIT.
806 * So l_sit_exist would return N regardless of what the ct marks
807 * if the jit table does not have SIT.
808 * If l_sit_rs_exists = Y then we have to see what the ct has set up.
809 * If they put All States at the employer identification level then we
810 * will have to check and see if the state is exempt. Because a customer
811 * can take out from all states but then later on go and mark a single
812 * state exempt. If they put Only States Defined Under State Tax
813 * Rules then we have to see if they have that state set up.
814 * If they do have the state there then we have to check the exempt flag
815 * If the e xempt flag is set to Y,exempt the state, but we have to
816 * decode the Y to a N to pass to l_sit_rs_exists because to exempt,
817 * l_sit_rs_exists has to be N, hence we have to decode the Y and N
818 * when checking for exempt status.
819 * If there is no row for the state then they do not have a place of
820 * business. So the select into will fail going to the exception handler
821 * which will return a l_sit_rs_exists = N for there is not state
822 * income tax.
823 */
824
825 IF l_sit_rs_exists = 'Y' THEN /* 2 */
826 /*{*/
827 SELECT DECODE(hoi.ORG_INFORMATION2, 'ALL', 'Y', 'STATES', 'N', 'Y')
828 /* EI level */
829 INTO l_org_info2
830 FROM HR_ORGANIZATION_INFORMATION hoi
831 WHERE hoi.ORG_INFORMATION_CONTEXT = 'Employer Identification'
832 AND hoi.organization_id = p_tax_unit_id;
833
834 BEGIN
835 /*{*/
836 SELECT DECODE(hoi.ORG_INFORMATION18, 'Y', 'N', 'N', 'Y', 'Y')
837 /* state level have to check if exempt */
838 INTO l_sit_rs_exists
839 FROM HR_ORGANIZATION_INFORMATION hoi
840 WHERE hoi.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
841 AND hoi.organization_id = p_tax_unit_id
842 AND hoi.org_information1 = l_state_abbrev;
843
844 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',11);
845 hr_utility.trace('SIT Exists : ' || l_sit_rs_exists);
846
847 IF l_sit_rs_exists = 'N' THEN
848
849 IF hr_us_ff_udf1.get_work_state(substr(p_juri_code, 1, 2)) = 'Y' THEN
850 RETURN 'Y';
851 END IF;
852
853 END IF;
854
855 RETURN l_sit_rs_exists;
856
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 /*
860 * If there is no value then return the value of l_org_info2
861 */
862 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',12);
863 hr_utility.trace('SIT Exists : ' || l_org_info2);
864
865 IF l_org_info2 = 'N' THEN
866
867 IF hr_us_ff_udf1.get_work_state(substr(p_juri_code, 1, 2)) = 'Y' THEN
868 RETURN 'Y';
869 END IF;
870
871 END IF;
872
873 RETURN l_org_info2;
874 /*}*/
875 END;
876 /*}*/
877 ELSE /* 2 */
878 /*{*/
879 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 15);
880 hr_utility.trace('SIT Exists : ' || l_sit_rs_exists);
881
882 RETURN l_sit_rs_exists;
883 /*
884 * jit level, this will be N, no sit for the state
885 */
886
887 /*}*/
888 END IF; /* 2 */
889 EXCEPTION
890 WHEN others THEN
891
892 l_sit_rs_exists := 'Y';
893 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 16);
894 hr_utility.trace('SIT Exists : ' || l_sit_rs_exists);
895
896 RETURN l_sit_rs_exists;
897
901 ELSIF p_type = 'SIT_WK' THEN /* 1 */
898 /*}*/
899 END; /* SIT_RS */
900 /*}*/
902 /*{*/
903 BEGIN
904 /*{*/
905 SELECT DISTINCT sit_exists
906 INTO l_sit_wk_exists
907 FROM pay_us_state_tax_info_f
908 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
909 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
910 effective_start_date AND effective_end_date;
911
912 IF l_sit_wk_exists = 'Y' THEN /* 2 */
913 /*{*/
914 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 20);
915 hr_utility.trace('SIT_WK Exists at the jit level, '
916 ||' now we check if the NR certificate is filed');
917 IF p_call = 'F' THEN
918 /*{*/
919 l_sit_wk_exists := get_tax_exists(p_juri_code,
920 p_date_earned,
921 p_tax_unit_id,
922 p_assign_id,
923 p_pact_id,
924 'NR');
925
926 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',21);
927 hr_utility.trace('Do we take out sit based on the NR check? '
928 || l_sit_wk_exists);
929 /*}*/
930 END IF;
931 /*
932 * if yes then NR is not checked, if no then NR is checked
933 */
934 RETURN l_sit_wk_exists;
935 /*}*/
936 ELSE /* 2 */
937 /*{*/
938 /*
939 * jit level, this will be N, no sit for the state
940 */
941 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists' ,22);
942 hr_utility.trace('SIT_WK Exists : ' || l_sit_wk_exists);
943 RETURN l_sit_wk_exists;
944
945 /*}*/
946 END IF; /* 2 */
947
948 EXCEPTION
949 WHEN others THEN
950 l_sit_wk_exists := 'Y';
951 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 23);
952 hr_utility.trace('SIT_WK Exists : ' || l_sit_wk_exists);
953 RETURN l_sit_wk_exists;
954 /*}*/
955 END; /* SIT_WK */
956 /*}*/
957
958 ELSIF p_type = 'WC' THEN /* 1 */
959 /*{*/
960 BEGIN
961 /*{*/
962
963 l_wc_exists := 'N';
964
965 select 'Y'
966 into l_wc_exists
967 from hr_organization_information hoi,
968 pay_us_states pus
969 where organization_id = p_tax_unit_id
970 and hoi.org_information_context = 'State Tax Rules'
971 and hoi.org_information1 = pus.state_abbrev
972 and pus.state_code = substr(p_juri_code,1,2)
973 and hoi.org_information8 is not null;
974
975
976 IF l_wc_exists = 'Y' THEN /* 2 */
977 /*{*/
978 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists WC', 20);
979 hr_utility.trace('WC Carrier Exists at the State Tax Levels level, '
980 ||' now check if this assignment is WC exempt');
981 IF p_call = 'F' THEN
982 /*{*/
983
984 SELECT DISTINCT decode( nvl(str.wc_exempt,'N'),
985 'Y','N', -- if wc exemptthe don't take WC
986 'Y')
987 INTO l_wc_exists
988 FROM pay_us_emp_state_tax_rules_f str
989 WHERE str.state_code = SUBSTR(p_juri_code, 1, 2)
990 AND str.assignment_id = p_assign_id /* 5772548 */
991 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
992 str.effective_start_date AND str.effective_end_date;
993
994 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists WC',21);
995 hr_utility.trace(' check for wc exemption ' || l_wc_exists);
996 /*}*/
997 END IF;
998 /*
999 * if yes then NR is not checked, if no then NR is checked
1000 */
1001 RETURN l_wc_exists;
1002
1003 END IF; /* 2 */
1004
1005 EXCEPTION
1006 WHEN others THEN
1007 l_wc_exists := 'Y';
1008 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists wc', 23);
1009 hr_utility.trace('WC Exists : ' || l_wc_exists);
1010 RETURN l_wc_exists;
1011 /*}*/
1012 END; /* WC */
1013 /*}*/
1014
1015 ELSIF p_type = 'MISC1_TAX' THEN /* 1 */
1016 /*{*/
1017 BEGIN
1018 /*{*/
1019 SELECT STA_INFORMATION16
1020 INTO l_misc1_state_tax
1021 FROM pay_us_state_tax_info_f
1022 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
1023 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
1024 effective_start_date AND effective_end_date;
1025
1026 IF l_misc1_state_tax = 'Y' THEN /* 2 */
1027 /*{*/
1028 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 25);
1029 hr_utility.trace('MISC1_TAX Exists at the jit level, '
1033 l_misc1_state_tax := get_tax_exists(p_juri_code,
1030 ||' now we check if the NR certificate is filed');
1031 /*Bug 4344763, For SHI(MA) NR Certificate should not be checked*/
1032 /* IF p_call = 'F' THEN
1034 p_date_earned,
1035 p_tax_unit_id,
1036 p_assign_id,
1037 'NR');
1038
1039 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',26);
1040 hr_utility.trace('Do we take out sit based on the NR check? '
1041 || l_sit_wk_exists);
1042
1043 END IF;*/
1044
1045 /*
1046 * if yes then NR is not checked, if no then NR is checked
1047 */
1048 RETURN l_misc1_state_tax;
1049 /*}*/
1050 ELSE /* 2 */
1051 /*{*/
1052 /*
1053 * jit level, this will be N, no misc1 tax for the state
1054 */
1055 l_misc1_state_tax := 'N';
1056 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists' ,27);
1057 hr_utility.trace('MISC1_TAX Exists : ' || l_misc1_state_tax);
1058 RETURN l_misc1_state_tax;
1059
1060 /*}*/
1061 END IF; /* 2 */
1062
1063 EXCEPTION
1064 WHEN others THEN
1065 l_misc1_state_tax := 'N';
1066 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 28);
1067 hr_utility.trace('MISC1_TAX Exists : ' || l_misc1_state_tax);
1068 RETURN l_misc1_state_tax;
1069 /*}*/
1070 END; /* MISC1_TAX */
1071 /*}*/
1072 ELSIF p_type = 'SDI_EE' THEN /* 1 */
1073 /*{*/
1074 IF assignment_tax_exists('SDI',
1075 p_assign_id,
1076 p_date_earned,
1077 p_juri_code) = 'Y' THEN
1078 /*{*/
1079
1080 /*
1081 * the assignment is exempt from SDI tax
1082 */
1083
1084 l_sdi_ee_exists := 'N';
1085 RETURN(l_sdi_ee_exists);
1086 /*}*/
1087 ELSE
1088 /*{*/
1089 BEGIN
1090 /*{*/
1091 SELECT DISTINCT DECODE(STATETAX.sdi_ee_wage_limit,
1092 NULL, 'N',
1093 0, 'N',
1094 'Y')
1095 INTO l_sdi_ee_exists
1096 FROM pay_us_state_tax_info_f STATETAX,
1097 fnd_sessions SES
1098 WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
1099 AND SES.session_id = USERENV('SESSIONID')
1100 AND SES.effective_date BETWEEN
1101 STATETAX.effective_start_date AND STATETAX.effective_end_date;
1102
1103 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 30);
1104 hr_utility.trace('SDI EE Exists : ' || l_sdi_ee_exists);
1105
1106 IF l_sdi_ee_exists = 'N' THEN /* 2 */
1107 /*
1108 * see if their is a period limit
1109 */
1110 /*{*/
1111 BEGIN
1112 /*{*/
1113 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1114 31);
1115 hr_utility.trace('No SDI wage limit, check for period limit');
1116
1117 SELECT DISTINCT DECODE(STATETAX.STA_INFORMATION1,
1118 NULL, 'N',
1119 0, 'N',
1120 'Y')
1121 INTO l_sdi_ee_exists
1122 FROM pay_us_state_tax_info_f STATETAX,
1123 fnd_sessions SES
1124 WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
1125 AND SES.session_id = USERENV('SESSIONID')
1126 AND SES.effective_date BETWEEN
1127 STATETAX.effective_start_date AND STATETAX.effective_end_date;
1128
1129 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1130 32);
1131 hr_utility.trace('SDI EE Exists: ' || l_sdi_ee_exists);
1132 RETURN l_sdi_ee_exists;
1133
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 l_sdi_ee_exists := 'N';
1137 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1138 33);
1139 hr_utility.trace('SDI EE Exists : ' || l_sdi_ee_exists);
1140 RETURN l_sdi_ee_exists;
1141
1142 /*}*/
1143 END;
1144 /*}*/
1145 ELSE /* 2 */
1146 /*{*/
1147 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1148 34);
1149 hr_utility.trace('SDI EE Exists : ' || l_sdi_ee_exists);
1150 RETURN l_sdi_ee_exists;
1151 /*}*/
1152 END IF; /* 2 */
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 l_sdi_ee_exists := 'Y';
1160 RETURN l_sdi_ee_exists;
1157 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1158 35);
1159 hr_utility.trace('SDI EE Exists : ' || l_sdi_ee_exists);
1161 /*}*/
1162 END; /* p_type = SDI_EE */
1163 /*}*/
1164 END IF; /* assignment_tax_exists */
1165 /*}*/
1166 ELSIF p_type = 'SDI_ER' THEN /* 1 */
1167 /*{*/
1168 IF assignment_tax_exists('SDI',
1169 p_assign_id,
1170 p_date_earned,
1171 p_juri_code) = 'Y' THEN
1172 /*{*/
1173 /*
1174 * the assignment is exempt from SDI tax
1175 */
1176 l_sdi_er_exists := 'N';
1177 RETURN(l_sdi_er_exists);
1178 /*}*/
1179 ELSE /* assignment_tax_exists */
1180 /*{*/
1181 SELECT DECODE(STATETAX.sdi_er_wage_limit, NULL, 'N', 0, 'N', 'Y')
1182 INTO l_sdi_er_exists
1183 FROM pay_us_state_tax_info_f STATETAX,
1184 fnd_sessions SES
1185 WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
1186 AND SES.session_id = USERENV('SESSIONID')
1187 AND SES.effective_date BETWEEN
1188 STATETAX.effective_start_date AND STATETAX.effective_end_date;
1189
1190 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 40);
1191 hr_utility.trace('SDI ER Exists : ' || l_sdi_er_exists);
1192
1193 RETURN l_sdi_er_exists;
1194
1195 /*}*/
1196 END IF; /* assignment_tax_exists */
1197 /*}*/
1198 ELSIF p_type = 'EIC_RS' THEN /* 1 */
1199 /*{*/
1200 BEGIN
1201 /*{*/
1202 SELECT DISTINCT nvl(sta_information17,'N')
1203 INTO l_eic_rs_exists
1204 FROM pay_us_state_tax_info_f
1205 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
1206 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
1207 effective_start_date AND effective_end_date;
1208
1209 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 23);
1210 hr_utility.trace('STEIC Exists : ' || l_eic_rs_exists);
1211
1212 RETURN l_eic_rs_exists;
1213
1214 EXCEPTION
1215 WHEN others THEN
1216 l_eic_rs_exists := 'N';
1217 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 23);
1218 hr_utility.trace('STEIC Exists : ' || l_eic_rs_exists);
1219
1220 RETURN l_eic_rs_exists;
1221
1222 /*}*/
1223 END; /* EIC_RS */
1224 /*}*/
1225 ELSIF p_type = 'EIC_WK' THEN /* 1 */
1226 /*{*/
1227 BEGIN
1228 /*{*/
1229 SELECT DISTINCT nvl(sta_information17,'N')
1230 INTO l_eic_wk_exists
1231 FROM pay_us_state_tax_info_f
1232 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
1233 AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
1234 effective_start_date AND effective_end_date;
1235
1236 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 23);
1237 hr_utility.trace('STEIC Exists : ' || l_eic_wk_exists);
1238
1239 RETURN l_eic_wk_exists;
1240
1241 EXCEPTION
1242 WHEN others THEN
1243 l_eic_wk_exists := 'N';
1244 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 23);
1245 hr_utility.trace('STEIC Exists : ' || l_eic_wk_exists);
1246
1247 RETURN l_eic_wk_exists;
1248
1249 /*}*/
1250 END; /* EIC_WK */
1251 /*}*/
1252 ELSIF p_type = 'COUNTY_RS' THEN /* 1 */
1253
1254 BEGIN
1255
1256 hr_utility.trace('BEGIN TAX TYPE COUNTY_RS');
1257
1258 IF SUBSTR(p_juri_code, 1, 2) = '15' THEN /* check for Indiana state */
1259
1260 if p_pact_id is not null then
1261
1262 open override_state;
1263 fetch override_state into l_indiana_override;
1264
1265 hr_utility.trace('l_indiana_override '||l_indiana_override);
1266 if override_state%found then
1267
1268 l_across_years := hr_us_ff_udf1.across_calendar_years(p_pact_id);
1269
1270 hr_utility.trace('l_across_years '||l_across_years);
1271 if (l_indiana_override = 'IN' and
1272 l_across_years = 'Y') then
1273
1274 select to_char(effective_date,'dd-mm-yyyy')
1275 into l_date
1276 from pay_payroll_actions
1277 where payroll_action_id = p_pact_id;
1278
1279 hr_utility.trace('l_date '||to_char(l_date));
1280 else
1281 l_date := l_date_earned;
1282 end if;
1283
1284 else
1285 l_date := l_date_earned;
1286 end if;
1287
1288 close override_state;
1289
1290 else
1291 l_date := l_date_earned;
1292 end if;
1293
1294 hr_utility.trace('Resident State is Indiana Jurisdiction '||p_juri_code);
1295
1296 l_rs_county_as_of_1st_jan := get_residence_as_of_1st_jan(p_assign_id,
1297 l_date);
1298
1302 /* 1st Jan */
1299 hr_utility.trace('Resident JD Code as of 1st Jan '||
1300 l_rs_county_as_of_1st_jan);
1301 IF l_rs_county_as_of_1st_jan = SUBSTR(p_juri_code, 1, 6) THEN
1303 hr_utility.trace('Resident JD Code as of 1st Jan = Primary JD code as of date');
1304 l_county_tax_exists := does_tax_exists(p_juri_code,
1305 l_date,
1306 p_tax_unit_id,
1307 p_assign_id,
1308 p_pact_id,
1309 'COUNTY_RS');
1310 hr_utility.trace('County Tax exist for JD code '||l_county_tax_exists);
1311 IF l_county_tax_exists = 'Y' THEN /* tax exists */
1312 /*{*/
1313 l_state_local_level_tax := state_local_level_tax(p_tax_unit_id,
1314 l_state_abbrev,
1315 p_juri_code);
1316 RETURN(l_state_local_level_tax);
1317 /*}*/
1318 ELSE /* tax does not exists */
1319 /*{*/
1320 RETURN(l_county_tax_exists);
1321 /*}*/
1322 END IF; /* tax exists */
1323 /*}*/
1324 ELSE /* 1st Jan */
1325 /*{*/
1326 hr_utility.trace('Resident JD Code as of 1st Jan <> Primary JD code as of date');
1327 hr_utility.trace('COUNTY_RS Tax Withheld = NO ');
1328 RETURN('N'); /* the county is not as of 1st Jan */
1329 /*}*/
1330 END IF; /* 1st Jan */
1331 /*} END Indiana State Check */
1332 ELSE /* check for Other State */
1333 /*{*/
1334 l_county_tax_exists := does_tax_exists(p_juri_code,
1335 l_date_earned,
1336 p_tax_unit_id,
1337 p_assign_id,
1338 p_pact_id,
1339 'COUNTY_RS');
1340
1341 IF l_county_tax_exists = 'Y' THEN /* tax exists */
1342 /*{*/
1343 l_state_local_level_tax := state_local_level_tax(p_tax_unit_id,
1344 l_state_abbrev,
1345 p_juri_code);
1346 hr_utility.trace('COUNTY_RS Tax Withheld = '||l_state_local_level_tax);
1347 RETURN(l_state_local_level_tax);
1348
1349 ELSE /* tax does not exists */
1350
1351 hr_utility.trace('COUNTY_RS Tax Withheld = NO ');
1352 RETURN('N');
1353
1354 END IF; /* tax exists */
1355
1356 END IF; /* check for Indiana state */
1357
1358 END; /* COUNTY_RS */
1359
1360 /*
1361 * For Indiana County there are special conditions that we need to check
1362 * For Indiana to check for tax exists or no we always have to check as of
1363 * 1st Jan. If a work county has tax as of 1st Jan then we need to check
1364 * if the residence county is in Indiana as of 1st Jan if yes then check
1365 * if a tax exists for that county if yes then we have to return false to
1366 * the work county that is we will not withhold at work county but at
1367 * residence county.
1368 */
1369 /*}*/
1370 /*
1371 * there are multiple IF THEN ELSE statements so before making any changes
1372 * please sure that all the conditions are satisfied.
1373 */
1374 ELSIF p_type = 'COUNTY_WK' THEN /* 1 */
1375 /*{*/
1376 hr_utility.trace('5300 BEGIN TAX TYPE COUNTY_WK');
1377 BEGIN
1378 /*{*/
1379 hr_utility.trace('5310 COUNTY_WK P_JURI_CODE ' || p_juri_code);
1380 IF SUBSTR(p_juri_code, 1, 2) = '15' THEN /* check for Indiana state */
1381 /*{ Changed for Bug 1366176 */
1382 /*
1383 * get RS county and check taxes exists there or no
1384 */
1385 hr_utility.trace('5320 COUNTY_WK COUNTY IS in State of Indiana ');
1386 l_rs_county_as_of_1st_jan := get_residence_as_of_1st_jan(p_assign_id,
1387 l_date_earned);
1388 hr_utility.trace('5330 COUNTY_WK Residence as of 1st Jan '|| l_rs_county_as_of_1st_jan);
1389 /*
1390 * we need to check if residence state - county is same as
1391 * work state - county. If yes then we calculate taxes at work else we
1392 * need to check the following.
1393 */
1394 IF SUBSTR(p_juri_code, 1, 6) = l_rs_county_as_of_1st_jan THEN
1395 /*{*/
1396 hr_utility.trace('5340 Work County_Code = Resident County_Code as of 1st Jan');
1397 IF l_rs_county_as_of_1st_jan <> '00-000' THEN
1398 /*{*/
1399 hr_utility.trace('5350 COUNTY_WK RS County_Code as of 1st Jan '||l_rs_county_as_of_1st_jan);
1400 l_does_tax_exists := does_tax_exists(l_rs_county_as_of_1st_jan,
1401 l_date_earned,
1402 p_tax_unit_id,
1403 p_assign_id,
1404 p_pact_id,
1405 'COUNTY_RS');
1409 * If residence county has tax, which is same as work county in
1406 hr_utility.trace('5360 COUNTY_WK Does tax exist for COUNTY RS '
1407 || l_does_tax_exists);
1408 /*
1410 * this case. So we will withhold taxes at work county itself
1411 * Old Comment
1412 * if the residence county has tax then return flase to work
1413 * county because we will withhold tax at residence and not at
1414 * work. else if residence county does not have tax then return
1415 * true which means that we have to withhold tax at work.
1416 */
1417 /* /if l_does_tax_exists
1418 = 'N' then
1419 return('Y');
1420 else
1421 return('N');
1422 end if;
1423 */
1424 RETURN(l_does_tax_exists);
1425 /*}*/
1426 ELSE
1427 /*
1428 * l_rs_county_as_of_1st_jan <> '00-000'
1429 * that is it is not an Indiana county as of 1st Jan
1430 * so we have to withhold work county taxes.
1431 */
1432 /*{*/
1433 hr_utility.trace('5370 COUNTY_WK COUNTY CODE as of 1st-Jan 00-000');
1434 hr_utility.trace('5380 COUNTY_WK Does tax exist for COUNTY RS '
1435 || l_does_tax_exists);
1436
1437 RETURN('Y') ;
1438 /*}*/
1439 END IF; /* l_rs_county_as_of_1st_jan < > '00-000' */
1440 /*}*/
1441 ELSE /* if substr(p_juri_code,1,6) <> l_rs_county_as_of_1st_jan */
1442 /*{*/
1443 hr_utility.trace('5390 Work County_Code <> Resident County_Code as of 1st Jan');
1444 /*Bug#6742101: If payroll period ends in one calendar year and
1445 the check date is in the next following year then date paid should be
1446 used. */
1447 if p_pact_id is not null then
1448
1449 l_across_years := hr_us_ff_udf1.across_calendar_years(p_pact_id);
1450
1451 hr_utility.trace('l_across_years '||l_across_years);
1452 if (l_across_years = 'Y') then
1453
1454 select to_char(effective_date,'dd-mm-yyyy')
1455 into l_date
1456 from pay_payroll_actions
1457 where payroll_action_id = p_pact_id;
1458
1459 hr_utility.trace('l_date '||to_char(l_date));
1460 else
1461 l_date := l_date_earned;
1462 end if;
1463
1464 else
1465 l_date := l_date_earned;
1466 end if;
1467
1468 IF get_location_as_of_1st_jan(p_assign_id,
1469 l_date_earned,
1470 l_date, --l_date_earned,
1471 p_juri_code) THEN
1472 /*Bug#6742101: Changes ends here */
1473 hr_utility.trace('5390 COUNTY_WK Work Location as of 1st Jan '|| p_juri_code);
1474 /*{*/
1475 /*
1476 * Indiana county as of 1st Jan
1477 */
1478 l_does_tax_exists := does_tax_exists(p_juri_code,
1479 l_date_earned,
1480 p_tax_unit_id,
1481 p_assign_id,
1482 p_pact_id,
1483 'COUNTY_WK' );
1484
1485 hr_utility.trace('5400 COUNTY_WK <Does_Tax_Exist for COUNTY_WK> returned '
1486 || l_does_tax_exists);
1487 IF l_does_tax_exists = 'Y' THEN
1488 /*{*/
1489 /* get RS county and check taxes exists there or no
1490 * commented as already go it
1491 * l_rs_county_as_of_1st_jan :=
1492 * get_residence_as_of_1st_jan(p_assign_id,l_date_earned);
1493 * we need to check if residence state-county is same as
1494 * work state-county. If yes then we calculate taxes
1495 * at work else we need to check the following.
1496 */
1497 /* commented for bug 1366176
1498 if substr(p_juri_code,1,6) = l_rs_county_as_of_1st_jan then
1499 return('Y');
1500 end if;
1501 */
1502 IF l_rs_county_as_of_1st_jan <> '00-000' THEN
1503 /*{*/
1504 l_does_tax_exists := does_tax_exists(l_rs_county_as_of_1st_jan,
1505 l_date_earned,
1506 p_tax_unit_id,
1507 p_assign_id,
1508 p_pact_id,
1509 'COUNTY_RS');
1510 hr_utility.trace('5410 COUNTY_WK <Does_Tax_Exist for COUNTY_RS> returned '
1511 || l_does_tax_exists);
1512 /*
1513 * if the residence county has tax then return flase to work
1517 * work.
1514 * county because we will withhold tax at residence and not at
1515 * work. else if residence county does not have tax then
1516 * return true which means that we have to withhold tax at
1518 */
1519 IF l_does_tax_exists = 'N' THEN
1520 /*{*/
1521 hr_utility.trace('5420 COUNTY_WK County Tax Exist Returned <Y> ');
1522 RETURN('Y');
1523 /*}*/
1524 ELSE
1525 /*{*/
1526 hr_utility.trace('5430 COUNTY_WK County Tax Exist Returned <N> ');
1527 RETURN('N');
1528 /*}*/
1529 END IF;
1530 /*}*/
1531 ELSE /* l_rs_county_as_of_1st_jan = '00-000' */
1532 /*{*/
1533 /*
1534 * that is it is not an Indiana county as of 1st Jan
1535 * so we have to withhold work county taxes.
1536 */
1537 hr_utility.trace('5440 COUNTY_WK As CountyCode 00-000 Tax Exist returned <Y>');
1538 RETURN('Y') ;
1539 /*}*/
1540 END IF; /* l_rs_county_as_of_1st_jan '00-000' */
1541 /*}*/
1542 ELSE /* l_does_tax_exists = 'Y' */
1543 /*{*/
1544 /*
1545 * there are no work county taxes
1546 */
1547 hr_utility.trace('5450 COUNTY_WK There are No Work County Tax So Tax Exist returned '
1548 ||l_does_tax_exists );
1549 RETURN(l_does_tax_exists);
1550 /*}*/
1551 END IF; /* l_does_tax_exists = 'Y' */
1552 /*}*/
1553 ELSE /* Indiana county as of 1st Jan */
1554 /*{*/
1555 /*
1556 * this is not the county as of 1st Jan so return false to
1557 * COUNTY_WK
1558 */
1559 hr_utility.trace('5460 COUNTY_WK As WorkCounty As of 1st Jan is Not-Taxable Tax Exist returned <N>');
1560 l_county_wk_exists := 'N';
1561 RETURN l_county_wk_exists;
1562 /*}*/
1563 END IF; /* Indiana county as of 1st Jan */
1564 /*}*/
1565 END IF; /* substr(p_juri_code,1,6) = l_rs_county_as_of_1st_jan */
1566 /*}*/
1567 ELSE /* check for state Other than Indiana */
1568 /*{*/
1569 hr_utility.trace('5470 COUNTY_WK County is not in Indiana ');
1570 l_county_wk_exists := does_tax_exists(p_juri_code,
1571 l_date_earned,
1572 p_tax_unit_id,
1573 p_assign_id,
1574 p_pact_id,
1575 'COUNTY_WK');
1576 hr_utility.trace('5480 COUNTY_WK <Does_Tax_Exist for COUNTY_WK> returned '
1577 ||l_county_wk_exists);
1578 RETURN (l_county_wk_exists);
1579 /*}*/
1580 END IF; /* check for Indiana state */
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 l_county_wk_exists := 'N';
1584 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 65);
1585 hr_utility.trace('5500 COUNTY_WK County Income Tax Exists : ' || l_county_wk_exists);
1586 RETURN l_county_wk_exists;
1587 /*}*/
1588 END; /* COUNTY_WK */
1589 /*}*/
1590
1591 ELSIF p_type = 'CITY_RS' THEN /* 1 */
1592 --{
1593 BEGIN
1594 --{
1595 -- Fetch record from JIT table to determine whether City is taxable or
1596 -- or Not
1597 --
1598 SELECT CITYTAX.city_tax
1599 INTO l_city_rs_exists
1600 FROM pay_us_city_tax_info_f CITYTAX,
1601 fnd_sessions SES
1602 WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
1603 AND SES.session_id = USERENV('SESSIONID')
1604 AND SES.effective_date BETWEEN
1605 CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
1606
1607 /*
1608 city taxes: If there is no city tax in the jit table then we return
1609 l_city_rs_exists = N hence we skip the city tax calculation. If the
1610 jit table says yes then we have to check against what the user has
1611 set up.
1612 */
1613
1614 IF l_city_rs_exists = 'Y' THEN /* 2 jit level */
1615 --{
1616 BEGIN /* see if the state has taxes in the ct setup */
1617 --{
1618 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1619 70);
1620 hr_utility.trace('determining if the state is set up by the ct');
1621 l_city_rs_exists := get_tax_exists(p_juri_code,
1622 p_date_earned,
1623 p_tax_unit_id,
1624 p_assign_id,
1625 p_pact_id,
1626 'SIT_RS');
1627 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1628 71);
1632 --
1629 hr_utility.trace('Is the state withholding taxes ? '
1630 ||l_city_rs_exists);
1631 IF l_city_rs_exists = 'Y' THEN /* 3 */
1633 -- The state is taking taxes
1634 --
1635 --{
1636 BEGIN
1637 --{
1638 SELECT DECODE(hoi.ORG_INFORMATION19,
1639 'ALL','Y',
1640 'LOCALITIES','N',
1641 'WORK_LOCALITIES','N',
1642 'Y') /* State Tax Rules level */
1643 , hoi.ORG_INFORMATION19
1644 -- new column added in the fetch for fixing bug 4711572
1645 -- INTO l_org_info19
1646 INTO l_org_info19,
1647 l_local_tax_rules_type
1648 FROM hr_organization_information hoi
1649 WHERE hoi.org_information_context = 'State Tax Rules'
1650 AND hoi.organization_id = p_tax_unit_id
1651 AND hoi.org_information1 = l_state_abbrev;
1652 BEGIN
1653 --
1654 -- Following query added for OH courtesy withholding enhancement
1655 --
1656 l_wh_work_localities := 'Y';
1657 SELECT DECODE(hoi.ORG_INFORMATION19,
1658 'WORK_LOCALITIES','N',
1659 'Y') /* State Tax Rules level */
1660 INTO l_wh_work_localities
1661 FROM hr_organization_information hoi
1662 WHERE hoi.org_information_context = 'State Tax Rules'
1663 AND hoi.organization_id = p_tax_unit_id
1664 AND hoi.org_information1 = l_state_abbrev;
1665
1666 IF l_wh_work_localities = 'Y' /* 3.1 */
1667 THEN
1668 BEGIN /* Check if the locality is exempt */
1669 --{
1670 SELECT DECODE(hoi.ORG_INFORMATION3,'Y','N','N','Y','Y')
1671 /* local level have to check if exempt */
1672 INTO l_city_rs_exists
1673 FROM HR_ORGANIZATION_INFORMATION hoi
1674 WHERE hoi.ORG_INFORMATION_CONTEXT = 'Local Tax Rules'
1675 AND hoi.organization_id = p_tax_unit_id
1676 AND hoi.org_information1 = p_juri_code;
1677 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists'
1678 , 72);
1679 hr_utility.trace('City Income Tax Exists : ' ||
1680 l_city_rs_exists);
1681 RETURN l_city_rs_exists;
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 /*
1685 * If there is no value then return l_city_rs_exists = 'Y'
1686 */
1687 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists'
1688 , 73);
1689 -- added for Bug # 4711572
1690 if l_local_tax_rules_type = 'LOCALITIES'
1691 then
1692 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
1693 hr_utility.trace('CITY_RS Jurisdiction Type : '||l_jd_type);
1694 -- if (nvl(l_jd_type,'NL') = 'RW')
1695 /*Modified for bug 7353397 to include Work at home scenario
1696 for caluclating taxes when 'Only Locatlites Under Local Tax Rules'
1697 is selected at GRE level */
1698 if (nvl(l_jd_type,'NL') = 'RW' or nvl(l_jd_type,'NL') = 'HW' )
1699 then
1700 hr_utility.trace('CITY_RS City Income Tax Exists set to withhold Tax: Y');
1701 return('Y');
1702 else
1703 hr_utility.trace('CITY_RS City Income Tax Exists : ' ||
1704 l_wh_work_localities);
1705 RETURN l_org_info19;
1706 end if;
1707 else
1708 hr_utility.trace('City Income Tax Exists : ' || l_org_info19);
1709 RETURN l_org_info19;
1710 end if;
1711 --}
1712 END; /* end check for locality exemption */
1713 ELSE /* 3.1 */
1714 /* If Employer setup for State's Resident Tax is Only Withhold Tax at Work Location
1715 return No, so that Resident tax is not withhold for the resident jurisdiction
1716 */
1717 hr_utility.set_location(
1718 'py_gt_tax_exists_pkg.get_tax_exists', 75);
1719 -- This is added to tax a jurisdiction if it is tagged OR
1720 -- resident jurisdiction is same Work
1721 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
1722 hr_utility.trace('CITY_RS Jurisdiction Type : '||l_jd_type);
1723 if (nvl(l_jd_type,'NL') = 'RT' OR
1724 nvl(l_jd_type,'NL') = 'RW' OR
1725 nvl(l_jd_type,'NL') = 'HW' )then -- added for Bug # 4463475
1726 hr_utility.trace('CITY_RS City Income Tax Exists set to withhold Tax: Y');
1730 l_wh_work_localities);
1727 return('Y');
1728 else
1729 hr_utility.trace('CITY_RS City Income Tax Exists : ' ||
1731 RETURN l_wh_work_localities;
1732 end if;
1733 END IF; /* 3.1 */
1734 END;
1735 EXCEPTION /* The ct has nothing at the EI level set up */
1736 WHEN OTHERS THEN
1737 l_city_rs_exists := 'Y';
1738 hr_utility.set_location(
1739 'py_gt_tax_exists_pkg.get_tax_exists', 76);
1740 hr_utility.trace(
1741 'City Income Tax Exists : ' || l_city_rs_exists);
1742 RETURN l_city_rs_exists;
1743 --}
1744 END; /* end for city_exists = Y */
1745 --}
1746 ELSE /* 3 */
1747 /*
1748 * The state is not withholding or is not setup no city tax
1749 */
1750 --{
1751 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1752 77);
1753 hr_utility.trace('City Income Tax Exists : ' ||
1754 l_city_rs_exists);
1755 RETURN l_city_rs_exists;
1756 --}
1757 END IF; /* 3 */
1758 --}
1759 END;
1760 --}
1761 ELSE /* 2 jit level, no city tax */
1762 --{
1763 RETURN l_city_rs_exists;
1764 --}
1765 END IF; /* 2 jit level */
1766 EXCEPTION /* No rows in the city jit table */
1767 WHEN OTHERS THEN
1768 l_city_rs_exists := 'N';
1769 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 78);
1770 hr_utility.trace('City Income Tax Exists : ' || l_city_rs_exists);
1771 RETURN l_city_rs_exists;
1772 --}
1773 END; /* CITY_RS */
1774 --}
1775
1776 ELSIF p_type = 'HT_WK' THEN /* 1 */
1777 /*{*/
1778 BEGIN
1779 /*{*/
1780 SELECT CITYTAX.head_tax
1781 INTO l_ht_wk_exists
1782 FROM pay_us_city_tax_info_f CITYTAX,
1783 fnd_sessions SES
1784 WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
1785 AND SES.session_id = USERENV('SESSIONID')
1786 AND SES.effective_date BETWEEN
1787 CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
1788
1789 /*
1790 * Head taxes: If there is no head tax in the jit table then
1791 * we return l_ht_wk_exists = N hence we skip the ht tax calculation.
1792 * If the jit table says yes then we have to check against what
1793 * the user has set up.
1794 */
1795 IF l_ht_wk_exists = 'Y' THEN /* 2 jit level */
1796 /*{*/
1797 BEGIN /* see if the state has taxes in the ct setup */
1798 /*{*/
1799 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1800 180);
1801 hr_utility.trace('determining if the state is set up by the ct');
1802
1803 l_ht_wk_exists := get_tax_exists(p_juri_code,
1804 p_date_earned,
1805 p_tax_unit_id,
1806 p_assign_id,
1807 p_pact_id,
1808 'SIT_WK');
1809
1810 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1811 181);
1812 hr_utility.trace('Is the state withholding taxes ? '||
1813 l_ht_wk_exists);
1814
1815 IF l_ht_wk_exists = 'Y' THEN /* 3 The state is taking taxes */
1816 /*{*/
1817 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1818 182);
1819 hr_utility.trace('Head Tax Exists : ' || l_ht_wk_exists);
1820 RETURN l_ht_wk_exists;
1821 /*}*/
1822 ELSE /* 3 */
1823 /*
1824 * The state is not withholding or is not setup no head tax
1825 */
1826 /*{*/
1827 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1828 183);
1829 hr_utility.trace('Head Tax Exists : ' || l_ht_wk_exists);
1830
1831 RETURN l_ht_wk_exists;
1832 /*}*/
1833 END IF; /* 3 */
1834 /*}*/
1835 END;
1836 /*}*/
1837 ELSE /* 2 jit level, no head tax */
1838 /*{*/
1839 RETURN l_ht_wk_exists;
1840 /*}*/
1841 END IF; /* 2 jit level */
1842 EXCEPTION /* No rows in the city jit table */
1843 WHEN OTHERS THEN
1844 l_ht_wk_exists := 'N';
1848 /*}*/
1845 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 184);
1846 hr_utility.trace('Head Tax Exists : ' || l_ht_wk_exists);
1847 RETURN l_ht_wk_exists;
1849 END; /* HT_WK */
1850 /*}*/
1851 ELSIF p_type = 'CITY_WK' THEN /* 1 */
1852 /*{*/
1853 BEGIN
1854 /*{*/
1855 SELECT CITYTAX.city_tax
1856 INTO l_city_wk_exists
1857 FROM pay_us_city_tax_info_f CITYTAX,
1858 fnd_sessions SES
1859 WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
1860 AND SES.session_id = USERENV('SESSIONID')
1861 AND SES.effective_date BETWEEN
1862 CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
1863
1864 /*
1865 * city taxes: If there is no city tax in the jit table then we return
1866 * l_city_wk_exists = N hence we skip the city tax calculation. If the
1867 * jit table says yes then we have to check against what the user has
1868 * set up.
1869 */
1870 IF l_city_wk_exists = 'Y' THEN /* 2 jit level */
1871 /*{*/
1872 BEGIN /* see if the state has taxes in the ct setup */
1873 /*{*/
1874 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1875 80);
1876 hr_utility.trace('determining if the state is set up by the ct');
1877
1878 l_city_wk_exists := get_tax_exists(p_juri_code,
1879 p_date_earned,
1880 p_tax_unit_id,
1881 p_assign_id,
1882 p_pact_id,
1883 'SIT_WK',
1884 'CITY_WK');
1885
1886 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1887 81);
1888 hr_utility.trace('Is the state withholding taxes ? '
1889 ||l_city_wk_exists);
1890
1891 IF l_city_wk_exists = 'Y' THEN /* 3 */
1892 /*
1893 * The state is taking taxes
1894 */
1895 /*{*/
1896 hr_utility.set_location('py_get_tax_exists_pkg.get_tax_exists',
1897 82);
1898 hr_utility.trace('City Income Tax Exists : ' ||
1899 l_city_wk_exists);
1900 RETURN l_city_wk_exists;
1901
1902 /*}*/
1903 ELSE /* 3 */
1904 /*
1905 * The state is not withholding or is not setup no city tax
1906 */
1907 /*{*/
1908 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists',
1909 83);
1910 hr_utility.trace('City Income Tax Exists : '
1911 || l_city_wk_exists);
1912
1913 RETURN l_city_wk_exists;
1914
1915 /*}*/
1916 END IF; /* 3 */
1917 /*}*/
1918 END;
1919 /*}*/
1920 ELSE /* 2 jit level, no city tax */
1921 /*{*/
1922 RETURN l_city_wk_exists;
1923 /*}*/
1924 END IF; /* 2 jit level */
1925 EXCEPTION /* No rows in the city jit table */
1926 WHEN OTHERS THEN
1927 l_city_wk_exists := 'N';
1928 hr_utility.set_location('py_gt_tax_exists_pkg.get_tax_exists', 84);
1929 hr_utility.trace('City Income Tax Exists : ' || l_city_wk_exists);
1930 RETURN l_city_wk_exists;
1931 /*}*/
1932 END; /* CITY_WK */
1933 /*}*/
1934 ELSIF p_type = 'NR' THEN /* 1 */
1935 /*
1936 * this is the check for the NR flag, this part will never be called by
1937 * Vertex only in the SIT call above
1938 */
1939 /*{*/
1940 BEGIN
1941 /*{*/
1942 SELECT puesrf.STATE_NON_RESIDENT_CERT
1943 INTO l_nr_exists
1944 FROM pay_us_emp_state_tax_rules_f puesrf,
1945 fnd_sessions ses
1946 WHERE puesrf.assignment_id = p_assign_id
1947 AND SUBSTR(puesrf.jurisdiction_code,1,2) = SUBSTR(p_juri_code,1,2)
1948 AND ses.session_id = USERENV('SESSIONID')
1949 AND ses.effective_date BETWEEN
1950 puesrf.effective_start_date AND puesrf.effective_end_date;
1951
1952 IF l_nr_exists = 'Y' THEN /* 2 */
1953 /*
1954 * we do not withhold, have to switch this to N for return to SIT
1955 */
1956 /*{*/
1957 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists',
1958 90);
1959 hr_utility.trace('NR Exists before need to return opp. to SIT : '
1960 ||l_nr_exists);
1961 l_sit_rs_exists := 'N';
1962
1963 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists', 91);
1964 hr_utility.trace('SIT Exists after switched the return from NR : '
1968
1965 ||l_sit_rs_exists);
1966
1967 RETURN l_sit_rs_exists;
1969 /*}*/
1970 ELSE /* 2 */
1971 /*
1972 * the nr cert is not checked, the value stored is N in the table
1973 */
1974 /*{*/
1975 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists', 92);
1976 hr_utility.trace('NR Exist before need to return opp. to SIT: '
1977 ||l_nr_exists);
1978
1979 l_sit_rs_exists := 'Y';
1980
1981 hr_utility.set_location('py_gt_tx_exists_pkg.get_tax_exists', 93);
1982 hr_utility.trace('SIT Exists after we switched the ret. from NR: '
1983 ||l_sit_rs_exists);
1984
1985 RETURN l_sit_rs_exists;
1986
1987 /*}*/
1988 END IF; /* 2 */
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991 l_nr_exists := 'N';
1992 hr_utility.set_location('py_gt_tax_exists_pkg.get_tax_exists', 94);
1993 hr_utility.trace('NR Exists before need to return opp. to SIT: '
1994 ||l_nr_exists);
1995
1996 l_sit_rs_exists := 'Y';
1997
1998 hr_utility.set_location('py_gt_tax_exists_pkg.get_tax_exists', 95);
1999 hr_utility.trace('SIT Exists after we switched the ret. from NR : '
2000 ||l_sit_rs_exists);
2001
2002 RETURN l_sit_rs_exists;
2003 /*}*/
2004 END;
2005 /*}*/
2006 ELSIF p_type = 'SUI' THEN /* 1 */
2007 /*{*/
2008 IF assignment_tax_exists('SUI',
2009 p_assign_id,
2010 p_date_earned,
2011 p_juri_code) = 'Y' THEN
2012 /*{*/
2013 /*
2014 * the assignment is exempt from SUI tax
2015 */
2016
2017 l_sui_exists := 'N';
2018 RETURN(l_sui_exists);
2019 /*}*/
2020 ELSE
2021 /*{*/
2022 BEGIN
2023 /*{*/
2024 SELECT 'N'
2025 INTO l_sui_exists
2026 FROM hr_organization_information hoi
2027 WHERE hoi.org_information_context = '1099R Magnetic Report Rules'
2028 AND hoi.organization_id = p_tax_unit_id
2029 AND hoi.org_information2 IS NOT NULL;
2030
2031 IF l_sui_exists = 'N' THEN /* 2 */
2032 /*{*/
2033 hr_utility.set_location('py_gt_tax_exists_pkg.get_tax_exists',
2034 100);
2035 hr_utility.trace('SUI EE Exists : ' || l_sui_exists);
2036
2037 RETURN l_sui_exists;
2038 /*}*/
2039 ELSE /* 2 */
2040 /*{*/
2041 l_sui_exists := 'Y';
2042 hr_utility.set_location('py_gt_tax_exists_pkg.get_tax_exists',
2043 101);
2044 hr_utility.trace('SUI EE Exists : ' || l_sui_exists);
2045
2046 RETURN l_sui_exists;
2047
2048 /*}*/
2049 END IF; /* 2 */
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 l_sui_exists := 'Y';
2053 hr_utility.set_location('py_get_tax_exists_pkg.get_tax_exists',
2054 102);
2055 hr_utility.trace('SUI EE Exists : ' || l_sui_exists);
2056 RETURN l_sui_exists;
2057 /*}*/
2058 END;
2059 /*}*/
2060 END IF;
2061 /*}*/
2062 ELSE /* 1 */
2063 /*{*/
2064 l_exists := 'Y';
2065 RETURN l_exists;
2066 /*}*/
2067 END IF; /* 1 */
2068 /*}*/
2069 ELSE
2070 /*{*/
2071 l_exists := 'N';
2072 RETURN l_exists;
2073 /*}*/
2074 END IF; /* chk_product_install */
2075 EXCEPTION
2076 WHEN OTHERS THEN
2077 l_exists := 'Y';
2078 hr_utility.set_location('pay_get_tax_exists_pkg.get_tax_exists', 110);
2079 hr_utility.trace('The main Exception handler was called and returns '
2080 ||'tax exists: '||l_exists);
2081 RETURN l_exists;
2082 -- hr_utility.trace_off;
2083 /*}*/
2084 END get_tax_exists;
2085
2086
2087 FUNCTION check_tax_exists
2088 (
2089 p_date_earned IN date,
2090 p_tax_unit_id IN number,
2091 p_assign_id IN number,
2092 p_pact_id IN number,
2093 p_juri_code IN varchar2,
2094 p_type IN varchar2
2095 )
2096 RETURN varchar2 IS
2097
2098 BEGIN
2099
2100 RETURN get_tax_exists(p_juri_code,
2101 p_date_earned,
2102 p_tax_unit_id,
2103 p_assign_id,
2104 p_pact_id,
2105 p_type,
2106 'F');
2107
2108 END check_tax_exists;
2109
2110
2111 FUNCTION get_wa_rule (p_juri_code IN VARCHAR2,
2112 p_date_earned IN DATE,
2113 p_tax_unit_id IN NUMBER,
2117 RETURN VARCHAR2 IS
2114 p_assign_id IN NUMBER,
2115 p_pact_id IN NUMBER,
2116 p_type IN VARCHAR2)
2118 -- This function when called from get_wage_accum_rule ,p_date_earned will be given Date Paid for bug 7441418
2119 l_date_earned varchar2(20);
2120 l_date varchar2(20);
2121 l_state_abbrev varchar2(2);
2122 l_state_employer_info varchar2(2);
2123 l_state_rule varchar2(10);
2124 l_state_rule_for_local varchar2(2);
2125 l_local_rule varchar2(10);
2126 l_default_rule varchar2(10);
2127 l_jit8_rule varchar2(10);
2128 l_jit8_rule_nobypass varchar2(10);
2129 l_jd_type varchar2(100);
2130 l_indiana_override varchar2(10);
2131 l_rs_coty_1st_jan varchar2(15);
2132 l_across_years varchar2(2);
2133 l_sit_rs_exists varchar2(2);
2134 l_city_rs_exists varchar2(2);
2135 l_county_rs_exists varchar2(2);
2136
2137 CURSOR override_state IS
2138 SELECT NVL(addr.add_information17,'ZZ')
2139 FROM per_addresses addr,
2140 per_all_assignments_f asg
2141 WHERE TO_DATE(l_date_earned, 'dd-mm-yyyy')
2142 BETWEEN asg.effective_start_date
2143 AND asg.effective_end_date
2144 AND asg.assignment_id = p_assign_id
2145 AND addr.person_id = asg.person_id
2146 AND addr.primary_flag = 'Y'
2147 AND TO_DATE(l_date_earned, 'dd-mm-yyyy')
2148 BETWEEN NVL(addr.date_from, TO_DATE(l_date_earned, 'dd-mm-yyyy'))
2149 AND NVL(addr.date_to, TO_DATE(l_date_earned, 'dd-mm-yyyy'));
2150
2151
2152 CURSOR state_employer_info IS
2153 SELECT DECODE(hoi.org_information2, 'ALL', 'Y', 'STATES', 'N', 'Y')
2154 FROM hr_organization_information hoi
2155 WHERE hoi.org_information_context = 'Employer Identification'
2156 AND hoi.organization_id = p_tax_unit_id;
2157
2158 CURSOR state_rule IS
2159 SELECT DECODE(NVL(hoi.org_information18,'00'), 'N', '00', NVL(hoi.org_information18,'00'))
2160 FROM hr_organization_information hoi
2161 WHERE hoi.org_information_context = 'State Tax Rules'
2162 AND hoi.organization_id = p_tax_unit_id
2163 AND hoi.org_information1 = l_state_abbrev;
2164
2165 CURSOR state_rule_for_local IS
2166 SELECT DECODE(hoi.org_information19,
2167 'ALL','A',
2168 'LOCALITIES','L',
2169 'WORK_LOCALITIES','W',
2170 'A')
2171 FROM hr_organization_information hoi
2172 WHERE hoi.org_information_context = 'State Tax Rules'
2173 AND hoi.organization_id = p_tax_unit_id
2174 AND hoi.org_information1 = l_state_abbrev;
2175
2176 CURSOR local_rule IS
2177 SELECT DECODE(NVL(hoi.org_information3,'00'),'N','00',NVL(hoi.org_information3,'00'))
2178 FROM hr_organization_information hoi
2179 WHERE hoi.org_information_context = 'Local Tax Rules'
2180 AND hoi.organization_id = p_tax_unit_id
2181 AND hoi.org_information1 = p_juri_code;
2182
2183 CURSOR sit_exists IS
2184 SELECT DISTINCT sit_exists
2185 FROM pay_us_state_tax_info_f
2186 WHERE state_code = SUBSTR(p_juri_code, 1, 2)
2187 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
2188 effective_start_date AND effective_end_date;
2189
2190 CURSOR city_exists IS
2191 SELECT city_tax
2192 FROM pay_us_city_tax_info_f
2193 WHERE jurisdiction_code = p_juri_code
2194 AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
2195 effective_start_date AND effective_end_date;
2196
2197 CURSOR county_exists IS
2198 SELECT county_tax
2199 FROM pay_us_county_tax_info_f
2200 WHERE jurisdiction_code = substr(p_juri_code, 1, 6) || '-0000'
2201 AND to_date(l_date_earned, 'DD-MM-YYYY') BETWEEN
2202 effective_start_date AND effective_end_date;
2203
2204 BEGIN
2205
2206 l_default_rule := '00';
2207 l_jit8_rule := '08';
2208 l_jit8_rule_nobypass := 'NOBYPASS'; /* Do not bypass the tax - pass JIT
2212 hr_utility.trace('The tax type : '|| p_type);
2209 08 along with earnings to Vertex */
2210 l_date_earned := to_char(p_date_earned,'DD-MM-YYYY');
2211
2213 hr_utility.trace('The jurisdiction : '|| p_juri_code);
2214 hr_utility.trace('The date earned : '|| l_date_earned);
2215 hr_utility.trace('The tax unit id : '|| to_char(p_tax_unit_id));
2216 hr_utility.trace('The assignment id : '|| to_char(p_assign_id));
2217
2218 SELECT DISTINCT pus.state_abbrev
2219 INTO l_state_abbrev
2220 FROM pay_us_states pus
2221 WHERE pus.state_code = SUBSTR(p_juri_code, 1, 2);
2222
2223 hr_utility.trace('The state abbrev is:' || l_state_abbrev);
2224
2225 IF p_type = 'SIT_RS' THEN
2226 BEGIN
2227
2228 OPEN sit_exists;
2229 FETCH sit_exists INTO l_sit_rs_exists;
2230 IF sit_exists%NOTFOUND THEN
2231 CLOSE sit_exists;
2232 RETURN l_jit8_rule;
2233 END IF;
2234 CLOSE sit_exists;
2235
2236 IF l_sit_rs_exists = 'Y' THEN
2237
2238 OPEN state_employer_info;
2239 FETCH state_employer_info INTO l_state_employer_info;
2240 IF state_employer_info%NOTFOUND THEN
2241 CLOSE state_employer_info;
2242 RETURN l_default_rule;
2243 END IF;
2244
2245 CLOSE state_employer_info;
2246
2247 OPEN state_rule;
2248 FETCH state_rule INTO l_state_rule;
2249 IF state_rule%NOTFOUND THEN
2250
2251 CLOSE state_rule;
2252 IF l_state_employer_info = 'N' THEN
2253
2254 IF hr_us_ff_udf1.get_work_state(substr(p_juri_code, 1, 2)) = 'Y' THEN
2255 RETURN l_default_rule;
2256 ELSE
2257 RETURN l_jit8_rule;
2258 END IF;
2259 ELSE
2260 RETURN l_default_rule;
2261 END IF;
2262
2263 ELSE
2264
2265 CLOSE state_rule;
2266 IF l_state_rule = 'Y' THEN
2267
2268 IF hr_us_ff_udf1.get_work_state(substr(p_juri_code, 1, 2)) = 'Y' THEN
2269 RETURN l_default_rule;
2270 ELSE
2271 RETURN l_jit8_rule;
2272 END IF;
2273
2274 ELSE
2275 IF l_state_rule = '08' THEN
2276 l_state_rule := l_jit8_rule_nobypass;
2277 END IF;
2278 RETURN l_state_rule;
2279 END IF;
2280
2281 END IF;
2282
2283 ELSE
2284 RETURN l_jit8_rule;
2285 END IF;
2286
2287 END; /* SIT_RS */
2288
2289 ELSIF (p_type = 'CITY_RS' OR
2290 p_type = 'SD_RS') THEN
2291 BEGIN
2292
2293 IF (p_type = 'CITY_RS') THEN
2294 OPEN city_exists;
2295 FETCH city_exists INTO l_city_rs_exists;
2296 IF city_exists%NOTFOUND THEN
2297 CLOSE city_exists;
2298 RETURN l_jit8_rule;
2299 END IF;
2300 CLOSE city_exists;
2301 ELSE
2302 l_city_rs_exists := 'Y';
2303 END IF;
2304
2305 IF l_city_rs_exists = 'Y' THEN
2306
2307 OPEN state_rule_for_local;
2308 FETCH state_rule_for_local INTO l_state_rule_for_local;
2309
2310 IF state_rule_for_local%NOTFOUND THEN
2311 CLOSE state_rule_for_local;
2312 RETURN l_default_rule;
2313 END IF;
2314
2315 CLOSE state_rule_for_local;
2316
2317 OPEN local_rule;
2318 FETCH local_rule INTO l_local_rule;
2319 IF local_rule%NOTFOUND THEN
2320
2321 CLOSE local_rule;
2322 IF l_state_rule_for_local = 'L' THEN
2323
2324 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
2325 hr_utility.trace('Jurisdiction Type : '||l_jd_type);
2326
2327 IF (nvl(l_jd_type,'NL') = 'RT' OR
2328 nvl(l_jd_type,'NL') = 'RW' OR
2329 nvl(l_jd_type,'NL') = 'HW' OR
2330 p_type = 'SD_RS') THEN
2331 RETURN l_default_rule;
2332 ELSE
2333 RETURN l_jit8_rule;
2334 END IF;
2335
2336 ELSIF (l_state_rule_for_local = 'W') THEN
2337
2338 IF (p_type = 'SD_RS') THEN
2339 RETURN l_default_rule;
2340 ELSE
2341 RETURN l_jit8_rule_nobypass;
2342 END IF;
2343
2344 ELSE
2345 RETURN l_default_rule;
2346 END IF;
2347
2348 ELSE
2349
2350 CLOSE local_rule;
2351 IF l_state_rule_for_local = 'W' THEN
2352
2353 IF l_local_rule = 'Y' THEN
2354
2355 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
2356 hr_utility.trace('Jurisdiction Type : '||l_jd_type);
2357
2358 IF (nvl(l_jd_type,'NL') = 'RT' OR
2362 RETURN l_default_rule;
2359 nvl(l_jd_type,'NL') = 'RW' OR
2360 nvl(l_jd_type,'NL') = 'HW' OR
2361 p_type = 'SD_RS') THEN
2363 ELSE
2364 RETURN l_jit8_rule;
2365 END IF;
2366
2367 ELSIF l_local_rule = l_default_rule THEN
2368
2369 IF (p_type = 'SD_RS') THEN
2370 RETURN l_default_rule;
2371 ELSE
2372 RETURN l_jit8_rule_nobypass;
2373 END IF;
2374
2375 ELSE
2376
2377 IF (p_type = 'SD_RS') THEN
2378
2379 IF l_local_rule = l_jit8_rule THEN
2380 RETURN l_default_rule;
2381 ELSE
2382 RETURN l_local_rule;
2383 END IF;
2384
2385 ELSE
2386
2387 IF l_local_rule = l_jit8_rule THEN
2388 l_local_rule := l_jit8_rule_nobypass;
2389 END IF;
2390
2391 RETURN l_local_rule;
2392
2393 END IF;
2394
2395 END IF;
2396
2397 ELSE
2398
2399 IF l_local_rule = 'Y' THEN
2400
2401 l_jd_type := hr_us_ff_udf1.get_jurisdiction_type(p_juri_code);
2402 hr_utility.trace('Jurisdiction Type : '||l_jd_type);
2403
2404 IF (nvl(l_jd_type,'NL') = 'RT' OR
2405 nvl(l_jd_type,'NL') = 'RW' OR
2406 nvl(l_jd_type,'NL') = 'HW' OR
2407 p_type = 'SD_RS') THEN
2408 RETURN l_default_rule;
2409 ELSE
2410 RETURN l_jit8_rule;
2411 END IF;
2412
2413 ELSE
2414
2415 IF (p_type = 'SD_RS') THEN
2416
2417 IF l_local_rule = l_jit8_rule THEN
2418 RETURN l_default_rule;
2419 ELSE
2420 RETURN l_local_rule;
2421 END IF;
2422
2423 ELSE
2424
2425 IF l_local_rule = l_jit8_rule THEN
2426 l_local_rule := l_jit8_rule_nobypass;
2427 END IF;
2428
2429 RETURN l_local_rule;
2430
2431 END IF;
2432
2433 END IF;
2434
2435 END IF;
2436
2437 END IF;
2438
2439 ELSE
2440 RETURN l_jit8_rule;
2441 END IF;
2442
2443 END; /* CITY_RS */
2444 ELSIF p_type = 'COUNTY_RS' THEN
2445 BEGIN
2446
2447 IF SUBSTR(p_juri_code, 1, 2) = '15' THEN /* check for Indiana state */
2448
2449 IF p_pact_id is not null THEN
2450
2451 OPEN override_state;
2452 FETCH override_state INTO l_indiana_override;
2453
2454 hr_utility.trace('l_indiana_override '||l_indiana_override);
2455 IF override_state%found THEN
2456
2457 l_across_years := hr_us_ff_udf1.across_calendar_years(p_pact_id);
2458
2459 hr_utility.trace('l_across_years '||l_across_years);
2460 IF (l_indiana_override = 'IN' AND
2461 l_across_years = 'Y') THEN
2462
2463 SELECT to_char(effective_date,'dd-mm-yyyy')
2464 INTO l_date
2465 FROM pay_payroll_actions
2466 WHERE payroll_action_id = p_pact_id;
2467
2468 hr_utility.trace('l_date '||to_char(l_date));
2469 ELSE
2470 l_date := l_date_earned;
2471 END IF;
2472
2473 ELSE
2474 l_date := l_date_earned;
2475 END IF;
2476
2477 CLOSE override_state;
2478
2479 ELSE
2480 l_date := l_date_earned;
2481 END IF;
2482
2483 l_rs_coty_1st_jan := get_residence_as_of_1st_jan(p_assign_id,
2484 l_date);
2485
2486 hr_utility.trace('Resident State is Indiana Jurisdiction '||p_juri_code);
2487 hr_utility.trace('Resident JD Code as of 1st Jan '|| l_rs_coty_1st_jan);
2488
2489 IF l_rs_coty_1st_jan = SUBSTR(p_juri_code, 1, 6) THEN
2490
2491 OPEN county_exists;
2492 FETCH county_exists INTO l_county_rs_exists;
2493 IF county_exists%NOTFOUND THEN
2494 CLOSE county_exists;
2495 RETURN l_jit8_rule;
2496 END IF;
2497 CLOSE county_exists;
2498
2499 IF (l_county_rs_exists = 'Y') THEN
2500 RETURN get_wa_rule (p_juri_code,
2501 p_date_earned,
2502 p_tax_unit_id,
2503 p_assign_id,
2504 p_pact_id,
2508 END IF;
2505 'SD_RS');
2506 ELSE
2507 RETURN l_jit8_rule;
2509
2510 ELSE /* 1st Jan */
2511
2512 hr_utility.trace('Resident JD Code as of 1st Jan <> Primary JD code as of date');
2513
2514 RETURN l_jit8_rule; /* the county is not as of 1st Jan */
2515
2516 END IF; /* 1st Jan */
2517
2518 ELSE /* check for Other State */
2519
2520 OPEN county_exists;
2521 FETCH county_exists INTO l_county_rs_exists;
2522 IF county_exists%NOTFOUND THEN
2523 CLOSE county_exists;
2524 RETURN l_jit8_rule;
2525 END IF;
2526 CLOSE county_exists;
2527
2528 IF (l_county_rs_exists = 'Y') THEN
2529
2530 RETURN get_wa_rule (p_juri_code,
2531 p_date_earned,
2532 p_tax_unit_id,
2533 p_assign_id,
2534 p_pact_id,
2535 'SD_RS');
2536 ELSE
2537 RETURN l_jit8_rule;
2538 END IF;
2539
2540 END IF;
2541
2542 END; /* COUNTY_RS */
2543
2544 ELSIF (p_type = 'FICA_EE' OR
2545 p_type = 'FICA_ER') THEN
2546
2547 IF assignment_tax_exists('FICA',
2548 p_assign_id,
2549 p_date_earned,
2550 p_juri_code) = 'Y' THEN
2551
2552 RETURN 'N';
2553 ELSE
2554 RETURN 'Y';
2555 END IF;
2556
2557 ELSIF (p_type = 'MEDI_EE' OR
2558 p_type = 'MEDI_ER') THEN
2559
2560 IF assignment_tax_exists('MEDICARE',
2561 p_assign_id,
2562 p_date_earned,
2563 p_juri_code) = 'Y' THEN
2564
2565 RETURN 'N';
2566 ELSE
2567 RETURN 'Y';
2568 END IF;
2569
2570 ELSIF p_type = 'FUTA' THEN
2571
2572 IF assignment_tax_exists('FUTA',
2573 p_assign_id,
2574 p_date_earned,
2575 p_juri_code) = 'Y' THEN
2576
2577 RETURN 'N';
2578 ELSE
2579 RETURN 'Y';
2580 END IF;
2581
2582 END IF; /*p_type*/
2583
2584 RETURN l_default_rule;
2585
2586 END;
2587
2588 FUNCTION get_wage_accum_rule (p_juri_code IN VARCHAR2,
2589 p_date_earned IN DATE,
2590 p_tax_unit_id IN NUMBER,
2591 p_assign_id IN NUMBER,
2592 p_pact_id IN NUMBER,
2593 p_type IN VARCHAR2,
2594 p_wage_accum IN VARCHAR2)
2595 RETURN VARCHAR2 IS
2596 --Added for bug 7441418
2597 l_date_paid date;
2598 CURSOR get_date_paid IS
2599 SELECT ppa.effective_date
2600 FROM
2601 pay_payroll_actions ppa
2602 WHERE ppa.payroll_action_id=p_pact_id;
2603 --Bug 7441418 ends
2604 BEGIN
2605 OPEN get_date_paid; --For bug 7441418
2606 FETCH get_date_paid INTO l_date_paid; --For bug 7441418
2607
2608 IF get_date_paid%FOUND THEN --For bug 7441418
2609 IF p_wage_accum = 'V' THEN
2610
2611 RETURN get_wa_rule (p_juri_code,
2612 l_date_paid,
2613 p_tax_unit_id,
2614 p_assign_id,
2615 p_pact_id,
2616 p_type); --Introduced l_date_paid instead of p_date_earned for bug 7441418
2617 ELSE
2618
2619 RETURN get_tax_exists(p_juri_code,
2620 l_date_paid,
2621 p_tax_unit_id,
2622 p_assign_id,
2623 p_pact_id,
2624 p_type,
2625 'F'); --Introduced l_date_paid instead of p_date_earned for bug 7441418
2626 END IF;
2627 END IF;
2628 CLOSE get_date_paid; --For bug 7441418
2629
2630 END get_wage_accum_rule;
2631
2632 FUNCTION get_wage_accumulation_flag
2633 RETURN varchar2
2634 IS
2635 l_wage_accumulation_flag varchar2(2);
2636
2637 CURSOR get_wage_acc_flag IS
2638 SELECT parameter_value
2639 FROM pay_action_parameters
2640 WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
2641
2642 BEGIN
2643
2644 OPEN get_wage_acc_flag;
2645 FETCH get_wage_acc_flag INTO l_wage_accumulation_flag;
2646
2647 IF get_wage_acc_flag%NOTFOUND THEN
2648 l_wage_accumulation_flag := 'N';
2649 END IF;
2650
2651 CLOSE get_wage_acc_flag;
2652
2653 return l_wage_accumulation_flag;
2654
2655 END get_wage_accumulation_flag;
2656
2657
2658 FUNCTION store_pretax_redns
2659 (p_juri_code IN varchar2,
2660 p_tax_type IN varchar2,
2661 p_mode IN varchar2,
2662 p_125_redns IN OUT NOCOPY number,
2663 p_401_redns IN OUT NOCOPY number,
2667 p_other_pretax_redns IN OUT NOCOPY number,
2664 p_403_redns IN OUT NOCOPY number,
2665 p_457_redns IN OUT NOCOPY number,
2666 p_dep_care_redns IN OUT NOCOPY number,
2668 p_gross IN OUT NOCOPY number,
2669 p_subj_nwhable IN OUT NOCOPY number,
2670 p_location IN varchar2,
2671 p_reduced_subj IN number,
2672 p_subj IN number)
2673 RETURN NUMBER
2674 IS
2675
2676 j NUMBER;
2677 l_ratio NUMBER;
2678 l_exempt NUMBER;
2679 l_total_redns NUMBER;
2680 l_subj_reduced NUMBER;
2681
2682 BEGIN
2683
2684 IF p_mode = 'IN' THEN
2685
2686 IF p_tax_type = 'FIT' THEN
2687 tax_balances.delete;
2688 END IF;
2689
2690 l_exempt := 0;
2691 IF p_location <> 'NA' THEN
2692
2693 l_total_redns := p_125_redns + p_401_redns + p_403_redns + p_457_redns +
2694 p_dep_care_redns + p_other_pretax_redns;
2695 l_exempt := p_gross - (p_reduced_subj + l_total_redns) - p_subj_nwhable;
2696 END IF;
2697
2698 j := tax_balances.COUNT + 1;
2699
2700 tax_balances(j).jurisdiction_code := p_juri_code;
2701 tax_balances(j).location := p_location;
2702 tax_balances(j).balance_name := p_tax_type||'_125_REDNS';
2703 tax_balances(j).amount := p_125_redns;
2704 j := j + 1;
2705
2706 tax_balances(j).jurisdiction_code := p_juri_code;
2707 tax_balances(j).location := p_location;
2708 tax_balances(j).balance_name := p_tax_type||'_401_REDNS';
2709 tax_balances(j).amount := p_401_redns;
2710 j := j + 1;
2711
2712 tax_balances(j).jurisdiction_code := p_juri_code;
2713 tax_balances(j).location := p_location;
2714 tax_balances(j).balance_name := p_tax_type||'_403_REDNS';
2715 tax_balances(j).amount := p_403_redns;
2716 j := j + 1;
2717
2718 tax_balances(j).jurisdiction_code := p_juri_code;
2719 tax_balances(j).location := p_location;
2720 tax_balances(j).balance_name := p_tax_type||'_457_REDNS';
2721 tax_balances(j).amount := p_457_redns;
2722 j := j + 1;
2723
2724 tax_balances(j).jurisdiction_code := p_juri_code;
2725 tax_balances(j).location := p_location;
2726 tax_balances(j).balance_name := p_tax_type||'_DEP_CARE_REDNS';
2727 tax_balances(j).amount := p_dep_care_redns;
2728 j := j + 1;
2729
2730 tax_balances(j).jurisdiction_code := p_juri_code;
2731 tax_balances(j).location := p_location;
2732 tax_balances(j).balance_name := p_tax_type||'_OTHER_PRETAX_REDNS';
2733 tax_balances(j).amount := p_other_pretax_redns;
2734 j := j + 1;
2735
2736 tax_balances(j).jurisdiction_code := p_juri_code;
2737 tax_balances(j).location := p_location;
2738 tax_balances(j).balance_name := p_tax_type||'_GROSS';
2739 tax_balances(j).amount := p_gross;
2740 j := j + 1;
2741
2742 IF (p_location <> 'NA' or p_tax_type = 'FIT') THEN
2743
2744 tax_balances(j).jurisdiction_code := p_juri_code;
2745 tax_balances(j).location := p_location;
2746 tax_balances(j).balance_name := p_tax_type||'_SUBJ_NWHABLE';
2747 tax_balances(j).amount := p_subj_nwhable;
2748 j := j + 1;
2749
2750 END IF;
2751
2752 IF (p_location <> 'NA') THEN
2753
2754 tax_balances(j).jurisdiction_code := p_juri_code;
2755 tax_balances(j).location := p_location;
2756 tax_balances(j).balance_name := p_tax_type||'_EXEMPT';
2757 tax_balances(j).amount := l_exempt;
2758 j := j + 1;
2759
2760 tax_balances(j).jurisdiction_code := p_juri_code;
2761 tax_balances(j).location := p_location;
2762 tax_balances(j).balance_name := p_tax_type||'_SUBJ_REDUCED';
2763 tax_balances(j).amount := p_reduced_subj;
2764
2765 END IF;
2766
2767
2768 ELSIF p_mode = 'OUT' THEN
2769
2770 p_125_redns := 0;
2771 p_401_redns := 0;
2772 p_403_redns := 0;
2773 p_457_redns := 0;
2774 p_dep_care_redns := 0;
2775 p_other_pretax_redns := 0;
2776 p_gross := 0;
2777 p_subj_nwhable := 0;
2778
2779 l_subj_reduced := 0;
2780
2781 IF tax_balances.COUNT > 0 THEN
2782
2783 l_subj_reduced := get_stored_balance(p_juri_code,
2784 p_tax_type||'_SUBJ_REDUCED',
2785 p_location);
2786
2787 FOR i IN tax_balances.FIRST..tax_balances.LAST
2788 LOOP
2789
2790 IF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2791 (tax_balances(i).balance_name = p_tax_type||'_125_REDNS') AND
2792 (tax_balances(i).location = p_location)) THEN
2793
2797 l_ratio := p_subj/l_subj_reduced;
2794 p_125_redns := tax_balances(i).amount;
2795
2796 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2798 p_125_redns := p_125_redns * l_ratio;
2799 END IF;
2800
2801 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2802 (tax_balances(i).balance_name = p_tax_type||'_401_REDNS') AND
2803 (tax_balances(i).location = p_location)) THEN
2804
2805 p_401_redns := tax_balances(i).amount;
2806
2807 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2808 l_ratio := p_subj/l_subj_reduced;
2809 p_401_redns := p_401_redns * l_ratio;
2810 END IF;
2811
2812 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2813 (tax_balances(i).balance_name = p_tax_type||'_403_REDNS') AND
2814 (tax_balances(i).location = p_location)) THEN
2815
2816 p_403_redns := tax_balances(i).amount;
2817
2818 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2819 l_ratio := p_subj/l_subj_reduced;
2820 p_403_redns := p_403_redns * l_ratio;
2821 END IF;
2822
2823 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2824 (tax_balances(i).balance_name = p_tax_type||'_457_REDNS') AND
2825 (tax_balances(i).location = p_location)) THEN
2826
2827 p_457_redns := tax_balances(i).amount;
2828
2829 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2830 l_ratio := p_subj/l_subj_reduced;
2831 p_457_redns := p_457_redns * l_ratio;
2832 END IF;
2833
2834 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2835 (tax_balances(i).balance_name = p_tax_type||'_DEP_CARE_REDNS') AND
2836 (tax_balances(i).location = p_location)) THEN
2837
2838 p_dep_care_redns := tax_balances(i).amount;
2839
2840 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2841 l_ratio := p_subj/l_subj_reduced;
2842 p_dep_care_redns := p_dep_care_redns * l_ratio;
2843 END IF;
2844
2845 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2846 (tax_balances(i).balance_name = p_tax_type||'_OTHER_PRETAX_REDNS') AND
2847 (tax_balances(i).location = p_location)) THEN
2848
2849 p_other_pretax_redns := tax_balances(i).amount;
2850
2851 IF (p_location = 'RS' and (l_subj_reduced > 0)) THEN
2852 l_ratio := p_subj/l_subj_reduced;
2853 p_other_pretax_redns := p_other_pretax_redns * l_ratio;
2854 END IF;
2855
2856 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2857 (tax_balances(i).balance_name = p_tax_type||'_GROSS') AND
2858 (tax_balances(i).location = p_location)) THEN
2859
2860 IF (p_location = 'WK') THEN
2861
2862 IF p_subj = 0 THEN
2863 reset_stored_balance(p_juri_code,
2864 p_tax_type||'_EXEMPT',
2865 p_location);
2866 ELSE
2867 p_gross := get_stored_balance(p_juri_code,
2868 p_tax_type||'_SUBJ_NWHABLE',
2869 p_location) +
2870 p_subj +
2871 get_stored_balance(p_juri_code,
2872 p_tax_type||'_OTHER_PRETAX_REDNS',
2873 p_location) +
2874 get_stored_balance(p_juri_code,
2875 p_tax_type||'_DEP_CARE_REDNS',
2876 p_location) +
2877 get_stored_balance(p_juri_code,
2878 p_tax_type||'_457_REDNS',
2879 p_location) +
2880 get_stored_balance(p_juri_code,
2881 p_tax_type||'_403_REDNS',
2882 p_location) +
2883 get_stored_balance(p_juri_code,
2884 p_tax_type||'_401_REDNS',
2885 p_location) +
2886 get_stored_balance(p_juri_code,
2887 p_tax_type||'_125_REDNS',
2888 p_location) +
2889 get_stored_balance(p_juri_code,
2890 p_tax_type||'_EXEMPT',
2891 p_location);
2892 END IF;
2893
2894 ELSIF (p_tax_type = 'SDI_EE' or
2895 p_tax_type = 'SDI_ER' or
2896 p_tax_type = 'SUI_EE' or
2900
2897 p_tax_type = 'SUI_ER') THEN
2898
2899 p_gross := tax_balances(i).amount;
2901 ELSIF (p_location = 'RS') THEN
2902
2903 hr_utility.trace('p_location ='||p_location);
2904 hr_utility.trace('p_tax_type ='||p_tax_type);
2905 hr_utility.trace('l_subj_reduced ='||l_subj_reduced);
2906 hr_utility.trace('p_subj='||p_subj);
2907
2908 IF l_subj_reduced > 0 THEN
2909
2910 l_ratio := p_subj/l_subj_reduced;
2911
2912 p_gross :=(get_stored_balance(p_juri_code,
2913 p_tax_type||'_SUBJ_NWHABLE',
2914 p_location) -
2915 get_stored_balance(p_juri_code,
2916 p_tax_type||'_SUBJ_NWHABLE',
2917 'WK')) +
2918 p_subj +
2919 (get_stored_balance(p_juri_code,
2920 p_tax_type||'_EXEMPT',
2921 p_location) -
2922 get_stored_balance(p_juri_code,
2923 p_tax_type||'_EXEMPT',
2924 'WK')) +
2925 (l_ratio *
2926 (get_stored_balance(p_juri_code,
2927 p_tax_type||'_OTHER_PRETAX_REDNS',
2928 p_location) +
2929 get_stored_balance(p_juri_code,
2930 p_tax_type||'_DEP_CARE_REDNS',
2931 p_location) +
2932 get_stored_balance(p_juri_code,
2933 p_tax_type||'_457_REDNS',
2934 p_location) +
2935 get_stored_balance(p_juri_code,
2936 p_tax_type||'_403_REDNS',
2937 p_location) +
2938 get_stored_balance(p_juri_code,
2939 p_tax_type||'_401_REDNS',
2940 p_location) +
2941 get_stored_balance(p_juri_code,
2942 p_tax_type||'_125_REDNS',
2943 p_location)) );
2944
2945 hr_utility.trace('p_gross ='||p_gross);
2946 hr_utility.trace('l_ratio ='||l_ratio);
2947
2948 END IF;
2949
2950 END IF;
2951
2952 ELSIF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
2953 (tax_balances(i).balance_name = p_tax_type||'_SUBJ_NWHABLE') AND
2954 (tax_balances(i).location = p_location)) THEN
2955
2956 IF (p_location = 'WK') THEN
2957
2958 IF p_subj = 0 THEN
2959 reset_stored_balance(p_juri_code,
2960 p_tax_type||'_SUBJ_NWHABLE',
2961 p_location);
2962 ELSE
2963 p_subj_nwhable := tax_balances(i).amount;
2964 END IF;
2965
2966 ELSIF (p_tax_type = 'FIT') THEN
2967 p_subj_nwhable := tax_balances(i).amount;
2968
2969 ELSIF (p_location = 'RS') THEN
2970 p_subj_nwhable := tax_balances(i).amount -
2971 get_stored_balance(p_juri_code,
2972 p_tax_type||'_SUBJ_NWHABLE',
2973 'WK');
2974 END IF;
2975
2976 END IF;
2977
2978 END LOOP;
2979
2980 END IF;
2981
2982 END IF;
2983
2984 RETURN(0);
2985
2986 END store_pretax_redns;
2987
2988 FUNCTION get_stored_balance
2989 (p_juri_code IN varchar2,
2990 p_balance_name IN varchar2,
2991 p_location IN varchar2)
2992 RETURN number
2993 IS
2994
2995 BEGIN
2996
2997 IF tax_balances.COUNT > 0 THEN
2998
2999 FOR i IN tax_balances.FIRST..tax_balances.LAST
3000 LOOP
3001 IF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
3002 (tax_balances(i).balance_name = p_balance_name) AND
3003 (tax_balances(i).location = p_location)) THEN
3004
3005 RETURN tax_balances(i).amount;
3006
3007 END IF;
3008 END LOOP;
3009
3010 END IF;
3011
3012 RETURN (0);
3013
3014 END get_stored_balance;
3015
3016 PROCEDURE reset_stored_balance
3017 (p_juri_code IN varchar2,
3018 p_balance_name IN varchar2,
3019 p_location IN varchar2)
3020 IS
3021 BEGIN
3022
3023 IF tax_balances.COUNT > 0 THEN
3024
3025 FOR i IN tax_balances.FIRST..tax_balances.LAST
3026 LOOP
3027 IF ((tax_balances(i).jurisdiction_code = p_juri_code) AND
3028 (tax_balances(i).balance_name = p_balance_name) AND
3029 (tax_balances(i).location = p_location)) THEN
3030
3031 tax_balances(i).amount := 0;
3032 END IF;
3033 END LOOP;
3034
3035 END IF;
3036
3037 END reset_stored_balance;
3038
3039 END pay_get_tax_exists_pkg;
[Home] [Help]