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