1 PACKAGE BODY pay_us_tax_bals_adj_api AS
2 /* $Header: pytbaapi.pkb 120.2.12000000.1 2007/01/18 01:49:36 appldev noship $ */
3 /*+======================================================================+
4 | Copyright (c) 1997 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Body Name : PAY_US_TAX_BALS_ADJ_API
9 Package File Name : pytbaapi.pkb
10
11 Description : Defines procedures for the main PLSQL execution engine.
12 See package header for details.
13
14 Change List:
15 ------------
16
17 Name Date Version Bug Text
18 ---------- ----------- ------- ------- ----------------------------------
19 mcpham 12-JUN-2000 115.0 Initial Version
20
21 irgonzal 13-SEP-2000 115.1 1398865 Modified csr_sdi_check cursor
22 to check business_group_id on
23 assignment record to ensure only one
24 is returned when same assignment
25 number exist in different business
26 groups.
27 ahanda 04-JAN-2001 115.2 Added commit before exit stmt.
28 meshah 24-JAN-2001 115.3 1608907 Now seperate checking is done for
29 CITY and COUNTY, if tax_exists returns
30 'N' then give error.
31 tclewis 11-JAN-2001 115.4 1569312 SUI and SDI taxable were only
32 being adjusted when an adjustment
33 about was entered for SUI / SDI
34 liablity. I removed the code
35 (if statements) where we check if
36 l_sui_er/l_sdi_er (or ee) were
37 eneterd before we process the
38 adjustment.
39 ahanda 16-OCT-2001 115.5 2060597 Changed process_limit to take care
40 of -ve amounts.
41 rmonge 14-NOV-2001 115.6 2060597 Made modifications to process limit
42 to take care of Negative Adjustments
43 2102153 Added new if then else logic, to
44 execute process_elements for
45 ('SUI_EE', 'SUI_ER','SDI_ER',
46 'SDI_EE') when there is an
47 Adjustment to be made. Before, the
48 code only executed if the gross
49 amount was different than 0. This
50 did not work in the case where the
51 user did not enter a gross amount
52 Adjustment.
53 tclewis 28-DEC-2001 115.9 2164393 Modified code around the calls to
54 process limits to NOT make the call
55 to the process_limits procedure
56 if the element to be processed is
57 MEDICARE_EE or MEDICARE_ER.
58 Modified the logic in process limits
59 as some of the condition checking was
60 not taking into consideration the adjustment
61 amount.
62 tclewis 03-JAN-2002 115.11 2170112 Modified csr_sui_geocode cursor
63 added a check for business group id
64 when getting the sui geo using the
65 "assignment number".
66 jgoswami 04-JAN-2002 115.12 Changed SUBJECT to REDUCED_SUBJ_WHABLE in
67 balance call for limit_subject_bal in
68 process_limits procedure.
69 tclewis 10-JAN-2002 115.13 With in the Process_inputs procedure when
70 checking city / count_subj, if we don't find
71 a taxability rule for city subj, we must first
72 check for the existence city subj NWHable, before
73 defaulting to the state taxablily rule. The
74 opposite goes for checking city subj NWhable.
75 meshah 22-JAN-2002 115.4 Added checkfile entry to the file.
76 tclewis 13-feb-2002 115.15 1) bug 2176643 modified the sdi_check cursor to
77 check for any work location withing the year.
78 Not just as of the effective_date of the balance
79 adjustment.
80 2) bug 2190000 modified the code to handle school
81 district taxes.
82 3) Fixed a bug with the process limits routine.
83 Currently we are fetching the reduced_subj_whable
84 balance as of adjustment date where we should be
85 fetching the balance as to the eoy.
86
87 tclewis 11-feb-2002 115.16 Added parameter p_balance_adj_prepay_flag
88 to the create_tax_balance_adjustment
89 procedure. This new parameter is now
90 passed to the pay_bal_adjust.init_batch
91 function.
92 tclewis 16-apr-2002 115.18 Added new cursor to the process_input procedure
93 csr_chk_all_taxability. This will be used
94 to check for the existance of ANY taxability
95 rules a the local level. We will use this in
96 the following sequence (example will be element)
97 city_subject_wk. check city subject TR. if
98 no data found check city_NW. if no data found
99 check for any TR at local level, if found assume
100 element is NOT SUBJECT, NOT WITHHELD. IF no
101 data found, default to state level.
102 tclewis 17-apr-2002 115.19 Memoved the following code
103 "and
104 (l_old_taxable_bal + p_earn_amount) > 0"
105 from within the negative balance adjustment
106 section, this created an error with large
107 negative adjustment with taxable.
108 tclewis 18-apr-2002 115.20 added a check for tax exists before processing
109 subject balances on County and city taxes
110 tclewis 05-SEP-2002 115.21 Modified the balance calls in the
111 process_limits and taxable_balance
112 procedure / functions to use PER
113 or PER_TG (in the case of tax group)
114 for the p_asg_type parameter. With
115 respect to PER_TG, this parameter
116 will only be used if the GRE on the
117 assignment is in a tax group and
118 the balance type is MEDICARE, SS or
119 FUTA.
120 Also added a check for L_gross_amount
121 <> 0 to the check for l_SUI /l_SDI
122 respectively when making the taxable
123 and withheld balance adjustment.
124 tclewis 12-SPE-2002 115.22 modified the process_limits procedure
125 to handle a condition with subject
126 and taxbable don't match and are below
127 the limit.
128 Also added code the check for tax exemptions
129 for limit taxes (Medicare, SS, futa, sui and
130 SDI). If balance adjustment is made
131 when assignment is exempt, will not adjust
132 the taxable balance. If an value is entered
133 into the withheld or liability filed we will
134 adjust that balance regardless of the
135 exemption.
136 tclewis 12-sep-2002 115.23 Left the foundation for tax group
137 but removed initial implementation
138 as pay_us_Tax_bals_view_pkg is
139 not yet fixed to handel 'PER_TG'
140 asg_type.
141 asasthnna 03-DEC-2002 115.24 added nocopy for gscc compliance
142 tclewis 10-DEC-2002 115.25 removed check for existance on global
143 rate variables. Now will fetch values
144 on each call of create_tax_balance_adjustment.
145 Made performance fix to csr_element in the
146 process_element procedure.
147 asasthan 30-MAY-2003 115.26 changes for 2904628
148 asasthan 02-JUN-2003 115.26 Further changes for 2904628
149 schauhan 29-JUN-2004 115.28 3697701 Removed the condition l_gross_amount<>0
150 from the IF condition when calculating
151 l_jd_level_needed.
152 Also put a format mask DD-MM-YYYY in
153 process_limits for p_adjustment_date
154 for GSCC compliance.
155 trugless 04-Oct-2004 115.29 3887144 Modified cursor c_get_sui_self_adjust_method,
156 changed hoi.org_information5 = h1.LOOKUP_CODE
157 to hoi.org_information4 = h1.LOOKUP_CODE.
158 hoi.org_information5 is SDI,
159 hoi.org_information4 is SUI.
160 sackumar 26-Apr-2005 115.30 4188782 Modified the Procedure create_tax_balance_adjusment
161 and process_element procedure for Supplimental earning.
162 sackumar 26-Apr-2005 115.31 4627851 Modified the Procedure create_tax_balance_adjusment
163 and process_element procedure for Imputed Earning.
164 sackumar 16-Nov-2005 115.32 4721086 Modified the value passed to p_virtual_date parameter in
165 pay_us_tax_bals_pkg.us_tax_balance procedure call from the
166 Procedure process_limits,It is changed now l_virtual_adjustment_date
167 local variable is used to pass the last date of the year.
168 ========================================================================*/
169
170
171 -- global variables
172 g_classification VARCHAR2(80);
173 g_earnings_category VARCHAR2(30);
174 g_classification_id NUMBER;
175 g_fed_jd VARCHAR2(11) := '00-000-0000';
176 g_state_jd VARCHAR2(11) := '00-000-0000';
177 g_sui_jd VARCHAR2(11) := '00-000-0000';
178 g_sui_state_code VARCHAR2(2);
179 g_county_jd VARCHAR2(11) := '00-000-0000';
180 g_city_jd VARCHAR2(11) := '00-000-0000';
181 g_sch_dist_jur VARCHAR2(10) := '00-00000';
182 g_dummy_varchar_tbl hr_entry.varchar2_table;
183 g_dummy_number_tbl hr_entry.number_table;
184
185 /* federal level 'balances' */
186 g_medicare_ee_taxable NUMBER := 0;
187 g_medicare_er_taxable NUMBER := 0;
188 g_futa_taxable NUMBER := 0;
189 g_ss_ee_taxable NUMBER := 0;
190 g_ss_er_taxable NUMBER := 0;
191
192 /* Federal self adjust methods */
193 g_futa_sa_method varchar2(20);
194 g_ss_sa_method varchar2(20);
195 g_medicare_sa_method varchar2(20);
196
197 /* state level 'balances' */
198 g_sdi_ee_taxable NUMBER := 0;
199 g_sdi_er_taxable NUMBER := 0;
200 g_sui_ee_taxable NUMBER := 0;
201 g_sui_er_taxable NUMBER := 0;
202
203 /*state Self Adjust method */
204 g_sdi_sa_method varchar2(20);
205 g_sui_sa_method varchar2(20);
206
207 /* federal level 'limits' */
208 g_futa_wage_limit NUMBER := 0;
209 g_ss_ee_wage_limit NUMBER := 0;
210 g_ss_er_wage_limit NUMBER := 0;
211
212 /* state level 'limits' */
213 g_sdi_ee_wage_limit NUMBER := 0;
214 g_sdi_er_wage_limit NUMBER := 0;
215 g_sui_ee_wage_limit NUMBER := 0;
216 g_sui_er_wage_limit NUMBER := 0;
217
218 /* federal level tax group */
219 g_tax_group varchar2(240) := 'NOT_ENTERED';
220
221 PROCEDURE process_input(
222 p_element_type IN VARCHAR2,
223 p_element_type_id IN NUMBER,
224 p_iv_tbl IN OUT NOCOPY hr_entry.number_table,
225 p_iv_names_tbl IN OUT NOCOPY hr_entry.varchar2_table,
226 p_ev_tbl IN OUT NOCOPY hr_entry.varchar2_table,
227 p_bg_id IN NUMBER,
228 p_adj_date IN DATE,
229 p_input_name IN VARCHAR2,
230 p_entry_value IN VARCHAR2,
231 p_row IN OUT NOCOPY NUMBER) IS
232
233 CURSOR csr_inputs(v_element_type_id IN NUMBER,
234 v_input_name IN VARCHAR2) IS
235 SELECT i.input_value_id
236 FROM pay_input_values_f i
237 WHERE i.element_type_id = v_element_type_id
238 AND (i.business_group_id = p_bg_id
239 OR i.business_group_id IS NULL)
240 AND i.name = v_input_name
241 AND p_adj_date BETWEEN
242 i.effective_start_date AND i.effective_end_date
243 ;
244
245 CURSOR csr_chk_taxability(v_tax_type VARCHAR2,
246 v_jurisdiction_code VARCHAR2) IS
247 SELECT 'Y'
248 FROM PAY_TAXABILITY_RULES
249 WHERE jurisdiction_code = v_jurisdiction_code
250 and tax_category = g_earnings_category
251 and tax_type = v_tax_type
252 and classification_id = g_classification_id
253 and nvl(status,'VALID') <> 'D'
254 ;
255
256 CURSOR csr_chk_fed_taxability(v_tax_type VARCHAR2) IS
257 SELECT 'Y'
258 FROM PAY_TAXABILITY_RULES
259 WHERE jurisdiction_code = g_fed_jd
260 and tax_category = g_earnings_category
261 and tax_type = v_tax_type
262 and classification_id = g_classification_id
263 and nvl(status,'VALID') <> 'D'
264 ;
265
266 CURSOR csr_chk_all_taxability(v_jurisdiction_code VARCHAR2) IS
267 SELECT 'N'
268 FROM PAY_TAXABILITY_RULES
269 WHERE jurisdiction_code = v_jurisdiction_code
270 and nvl(status,'VALID') <> 'D'
271
272 ;
273
274 CURSOR csr_get_school_jd_level IS
275 SELECT 'Y'
276 FROM pay_us_county_school_dsts pcsd
277 WHERE pcsd.state_code = substr(g_sch_dist_jur,1,2)
278 AND pcsd.school_dst_code = substr(g_sch_dist_jur,4,5)
279 ;
280
281 l_input_value_id NUMBER;
282 l_taxable VARCHAR2(1) := 'N';
283 c_proc VARCHAR2(100) := 'pay_us_tax_bals_adj_pkg.process_input';
284 l_jurisdiction_code VARCHAR2(11);
285 l_county_sch_dsts VARCHAR2(10) := 'N';
286
287 BEGIN
288 hr_utility.set_location(c_proc, 10);
289
290 OPEN csr_inputs (p_element_type_id, p_input_name);
291 FETCH csr_inputs INTO l_input_value_id;
292 CLOSE csr_inputs;
293
294 IF (l_input_value_id IS NULL) THEN
295 hr_utility.set_location(c_proc, 20);
296 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
297 hr_utility.raise_error;
298 END IF;
299
300 -- check taxability of the tax balance element
301 hr_utility.set_location(c_proc, 30);
302
303 IF (g_classification IN ('Imputed Earnings', 'Supplemental Earnings')) THEN
304
305 /** sbilling **/
306 /*
307 ** no RRVs were being generated for Medicare_EE's TAXABLE EV as p_element_type
308 ** (Medicare_EE) didn't satisfy any if conditions in the inner block,
309 ** l_taxable was not set to Y,
310 ** therefore the table structure was not populated,
311 ** at a later stage Medicare_EE's TAXABLE EV would be defaulted to 0,
312 ** causing the taxable amount to appear in Excess,
313 */
314 IF (p_input_name = 'Subj Whable' OR p_input_name = 'TAXABLE') THEN
315
316 hr_utility.set_location(c_proc, 40);
317
318 IF (p_element_type IN ('SUI_EE', 'SUI_SUBJECT_EE',
319 'SUI_ER', 'SUI_SUBJECT_ER')) THEN
320 hr_utility.set_location(c_proc, 41);
321 OPEN csr_chk_taxability ('SUI', g_state_jd );
322 FETCH csr_chk_taxability INTO l_taxable;
323 CLOSE csr_chk_taxability;
324
325 ELSIF (p_element_type IN ('Medicare_EE', 'Medicare_ER')) THEN
326 hr_utility.set_location(c_proc, 42);
327 OPEN csr_chk_fed_taxability ('MEDICARE');
328 FETCH csr_chk_fed_taxability INTO l_taxable;
329 CLOSE csr_chk_fed_taxability;
330
331 ELSIF (p_element_type IN ('SS_EE', 'SS_ER')) THEN
332 hr_utility.set_location(c_proc, 43);
333 OPEN csr_chk_fed_taxability ('SS');
334 FETCH csr_chk_fed_taxability INTO l_taxable;
335 CLOSE csr_chk_fed_taxability;
336
337 ELSIF (p_element_type IN ('FUTA')) THEN
338 hr_utility.set_location(c_proc, 43);
339 OPEN csr_chk_fed_taxability ('FUTA');
340 FETCH csr_chk_fed_taxability INTO l_taxable;
341 CLOSE csr_chk_fed_taxability;
342
343 ELSIF (p_element_type IN ('SDI_EE', 'SDI_SUBJECT_EE',
344 'SDI_ER', 'SDI_SUBJECT_ER')) THEN
345 hr_utility.set_location(c_proc, 42);
346 OPEN csr_chk_taxability ('SDI', g_state_jd );
347 FETCH csr_chk_taxability INTO l_taxable;
348 CLOSE csr_chk_taxability;
349
350 ELSIF (p_element_type = ('SIT_SUBJECT_WK') ) THEN
351 hr_utility.set_location(c_proc, 43);
352 OPEN csr_chk_taxability ('SIT', g_state_jd );
353 FETCH csr_chk_taxability INTO l_taxable;
354 CLOSE csr_chk_taxability;
355
356 ELSIF (p_element_type = ('City_SUBJECT_WK') ) THEN
357 hr_utility.set_location(c_proc, 44);
358 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
359 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
360 FETCH csr_chk_taxability INTO l_taxable;
361 -- If the above query returns no rows then check the state level taxablility rule
362 -- as we are checking for SUBJ whable here. If we don't find a row for locality
363 -- subj whable, we must check for subj NWhable befor defaulting to state level.
364 -- NOTE currently is does not cover a situation where the specific element type
365 -- is not subject (WHable or NWhable) and the state is Whable.
366 IF csr_chk_taxability%NOTFOUND THEN -- 1
367 CLOSE csr_chk_taxability;
368 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
369 FETCH csr_chk_taxability INTO l_taxable;
370 IF csr_chk_taxability%NOTFOUND THEN -- 2
371 -- check for the existance of any taxability rules at this JD level.
372 -- if we get to this point and the csr_chk_all_taxability returns data
373 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
374 CLOSE csr_chk_taxability;
375 OPEN csr_chk_all_taxability (l_jurisdiction_code);
376 FETCH csr_chk_all_taxability INTO l_taxable;
377 IF csr_chk_all_taxability%NOTFOUND THEN --3
378 CLOSE csr_chk_all_taxability;
379 OPEN csr_chk_taxability ('SIT', g_state_jd);
380 FETCH csr_chk_taxability INTO l_taxable;
381 CLOSE csr_chk_taxability;
382 ELSE -- 3
383 l_taxable := 'N';
384 CLOSE csr_chk_all_taxability;
385 END IF; -- 3
386 ELSE -- 2
387 l_taxable := 'N';
388 CLOSE csr_chk_taxability;
389 END IF; --2
390 ELSE -- 1
391 CLOSE csr_chk_taxability;
392 END IF; --1
393
394 /* NEW code for school district processing */
395
396 ELSIF p_element_type = ('School_SUBJECT_WK') THEN
397 -- IF THE STATE JURISDICTION IS OHIO THEN CHECK TAXABLILITY RULES OF THE STATE LEVEL
398 -- ELESE CHECK THE TAXABLILITY RULES OF THE RESPECTIVE CITY OR COUNTY THE SCHOOL
399 -- DISTRICT BELONGS TO.
400 IF SUBSTR(G_city_jd,1,2) = '36' THEN
401 OPEN csr_chk_taxability ('SIT', g_state_jd);
402 FETCH csr_chk_taxability INTO l_taxable;
403 CLOSE csr_chk_taxability;
404 ELSE -- state code = 36
405 OPEN csr_get_school_jd_level;
406 fetch csr_get_school_jd_level inTO l_county_sch_dsts;
407 if csr_get_school_jd_level%NOTFOUND THEN
408 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
409 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
410 FETCH csr_chk_taxability INTO l_taxable;
411 -- If the above query returns no rows then check the state level taxablility rule
412 -- as we are checking for SUBJ whable here. If we don't find a row for locality
413 -- subj whable, we must check for subj NWhable befor defaulting to state level.
414 -- NOTE currently is does not cover a situation where the specific element type
415 -- is not subject (WHable or NWhable) and the state is Whable.
416 IF csr_chk_taxability%NOTFOUND THEN
417 CLOSE csr_chk_taxability;
418 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
419 FETCH csr_chk_taxability INTO l_taxable;
420 IF csr_chk_taxability%NOTFOUND THEN -- 2
421 -- check for the existance of any taxability rules at this JD level.
422 -- if we get to this point and the csr_chk_all_taxability returns data
423 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
424 CLOSE csr_chk_taxability;
425 OPEN csr_chk_all_taxability (l_jurisdiction_code);
426 FETCH csr_chk_all_taxability INTO l_taxable;
427 IF csr_chk_all_taxability%NOTFOUND THEN --3
428 CLOSE csr_chk_all_taxability;
429 OPEN csr_chk_taxability ('SIT', g_state_jd);
430 FETCH csr_chk_taxability INTO l_taxable;
431 CLOSE csr_chk_taxability;
432 ELSE -- 3
433 l_taxable := 'N';
434 CLOSE csr_chk_all_taxability;
435 END IF; -- 3
436 ELSE -- 2
437 l_taxable := 'N';
438 CLOSE csr_chk_taxability;
439 END IF; --2
440 ELSE
441 CLOSE csr_chk_taxability;
442 END IF;
443
444 ELSE -- csr_get_school_jd_level%NOT_FOUND
445 -- row found in cursor so this is a county school district
446 -- check the county TR
447
448 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
449 FETCH csr_chk_taxability INTO l_taxable;
450 -- If the above query returns no rows then check the state level taxablility rule
451 -- as we are checking for SUBJ whable here. If we don't find a row for locality
452 -- subj whable, we must check for subj NWhable befor defaulting to state level.
453 -- NOTE currently is does not cover a situation where the specific element type
454 -- is not subject (WHable or NWhable) and the state is Whable.
455 IF csr_chk_taxability%NOTFOUND THEN
456 CLOSE csr_chk_taxability;
457 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
458 FETCH csr_chk_taxability INTO l_taxable;
459 IF csr_chk_taxability%NOTFOUND THEN -- 2
460 -- check for the existance of any taxability rules at this JD level.
461 -- if we get to this point and the csr_chk_all_taxability returns data
462 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
463 CLOSE csr_chk_taxability;
464 OPEN csr_chk_all_taxability (g_county_jd);
465 FETCH csr_chk_all_taxability INTO l_taxable;
466 IF csr_chk_all_taxability%NOTFOUND THEN --3
467 CLOSE csr_chk_all_taxability;
468 OPEN csr_chk_taxability ('SIT', g_state_jd);
469 FETCH csr_chk_taxability INTO l_taxable;
470 CLOSE csr_chk_taxability;
471 ELSE -- 3
472 l_taxable := 'N';
473 CLOSE csr_chk_all_taxability;
474 END IF; -- 3
475 ELSE -- 2
476 l_taxable := 'N';
477 CLOSE csr_chk_taxability;
478 END IF; --2
479 ELSE
480 CLOSE csr_chk_taxability;
481 END IF;
482 END IF; -- csr_get_school_jd_level%NOT_FOUND
483
484 CLOSE csr_get_school_jd_level;
485
486 END IF; -- state code = '36'
487
488 /* End of code for school district taxes. */
489
490 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
491 hr_utility.set_location(c_proc, 45);
492 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
493 FETCH csr_chk_taxability INTO l_taxable;
494 -- If the above query returns no rows then check the state level taxablility rule
495 -- as we are checking for SUBJ whable here. If we don't find a row for locality
496 -- subj whable, we must check for subj NWhable befor defaulting to state level.
497 -- NOTE currently is does not cover a situation where the specific element type
498 -- is not subject (WHable or NWhable) and the state is Whable.
499 IF csr_chk_taxability%NOTFOUND THEN
500 CLOSE csr_chk_taxability;
501 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
502 FETCH csr_chk_taxability INTO l_taxable;
503 IF csr_chk_taxability%NOTFOUND THEN -- 2
504 -- check for the existance of any taxability rules at this JD level.
505 -- if we get to this point and the csr_chk_all_taxability returns data
506 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
507 CLOSE csr_chk_taxability;
508 OPEN csr_chk_all_taxability (g_county_jd);
509 FETCH csr_chk_all_taxability INTO l_taxable;
510 IF csr_chk_all_taxability%NOTFOUND THEN --3
511 CLOSE csr_chk_all_taxability;
512 OPEN csr_chk_taxability ('SIT', g_state_jd);
513 FETCH csr_chk_taxability INTO l_taxable;
514 CLOSE csr_chk_taxability;
515 ELSE -- 3
516 l_taxable := 'N';
517 CLOSE csr_chk_all_taxability;
518 END IF; -- 3
519 ELSE -- 2
520 l_taxable := 'N';
521 CLOSE csr_chk_taxability;
522 END IF; --2
523 ELSE
524 CLOSE csr_chk_taxability;
525 END IF;
526
527 END IF;
528
529 ELSIF (p_input_name = 'Subj NWhable') THEN
530 hr_utility.set_location(c_proc, 50);
531
532 IF (p_element_type = ('SIT_SUBJECT_WK') ) THEN
533 hr_utility.set_location(c_proc, 51);
534 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
535 FETCH csr_chk_taxability INTO l_taxable;
536 CLOSE csr_chk_taxability;
537
538 ELSIF (p_element_type = ('City_SUBJECT_WK') ) THEN
539 hr_utility.set_location(c_proc, 52);
540 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
541 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
542 FETCH csr_chk_taxability INTO l_taxable;
543 -- If the above query returns no rows then check the state level taxablility rule
544 -- as we are checking for SUBJ Nwhable here. If we don't find a row for locality
545 -- subj whable, we must check for SUBJ Whable befor defaulting to state level.
546 -- NOTE currently is does not cover a situation where the specific element type
547 -- is not subject (WHable or NWhable) and the state is Whable.
548 IF csr_chk_taxability%NOTFOUND THEN
549 CLOSE csr_chk_taxability;
550 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
551 FETCH csr_chk_taxability INTO l_taxable;
552 IF csr_chk_taxability%NOTFOUND THEN -- 2
553 -- check for the existance of any taxability rules at this JD level.
554 -- if we get to this point and the csr_chk_all_taxability returns data
555 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
556 CLOSE csr_chk_taxability;
557 OPEN csr_chk_all_taxability (l_jurisdiction_code);
558 FETCH csr_chk_all_taxability INTO l_taxable;
559 IF csr_chk_all_taxability%NOTFOUND THEN --3
560 CLOSE csr_chk_all_taxability;
561 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
562 FETCH csr_chk_taxability INTO l_taxable;
563 CLOSE csr_chk_taxability;
564 ELSE -- 3
565 l_taxable := 'N';
566 CLOSE csr_chk_all_taxability;
567 END IF; -- 3
568 ELSE -- 2
569 l_taxable := 'N';
570 CLOSE csr_chk_taxability;
571 END IF; --2
572 ELSE
573 CLOSE csr_chk_taxability;
574 END IF;
575
576 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
577 hr_utility.set_location(c_proc, 53);
578 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
579 FETCH csr_chk_taxability INTO l_taxable;
580 -- If the above query returns no rows then check the state level taxablility rule
581 -- as we are checking for SUBJ Nwhable here. If we don't find a row for locality
582 -- subj whable, we must check for SUBJ Whable befor defaulting to state level.
583 -- NOTE currently is does not cover a situation where the specific element type
584 -- is not subject (WHable or NWhable) and the state is Whable.
585 IF csr_chk_taxability%NOTFOUND THEN
586 CLOSE csr_chk_taxability;
587 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
588 FETCH csr_chk_taxability INTO l_taxable;
589 IF csr_chk_taxability%NOTFOUND THEN -- 2
590 -- check for the existance of any taxability rules at this JD level.
591 -- if we get to this point and the csr_chk_all_taxability returns data
592 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
593 CLOSE csr_chk_taxability;
594 OPEN csr_chk_all_taxability (g_county_jd);
595 FETCH csr_chk_all_taxability INTO l_taxable;
596 IF csr_chk_all_taxability%NOTFOUND THEN --3
597 CLOSE csr_chk_all_taxability;
598 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
599 FETCH csr_chk_taxability INTO l_taxable;
600 CLOSE csr_chk_taxability;
601 ELSE -- 3
602 l_taxable := 'N';
603 CLOSE csr_chk_all_taxability;
604 END IF; -- 3
605 ELSE -- 2
606 l_taxable := 'N';
607 CLOSE csr_chk_taxability;
608 END IF; --2
609 ELSE
610 CLOSE csr_chk_taxability;
611 END IF;
612
613 /* NEW code for school district processing */
614
615 ELSIF p_element_type = ('School_SUBJECT_WK') THEN
616 -- IF THE STATE JURISDICTION IS OHIO THEN CHECK TAXABLILITY RULES OF THE STATE LEVEL
617 -- ELESE CHECK THE TAXABLILITY RULES OF THE RESPECTIVE CITY OR COUNTY THE SCHOOL
618 -- DISTRICT BELONGS TO.
619 IF SUBSTR(G_city_jd,1,2) = '36' THEN
620 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
621 FETCH csr_chk_taxability INTO l_taxable;
622 CLOSE csr_chk_taxability;
623 ELSE -- state code = 36
624 OPEN csr_get_school_jd_level;
625 fetch csr_get_school_jd_level inTO l_county_sch_dsts;
626 if csr_get_school_jd_level%NOTFOUND THEN
627 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
628 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
629 FETCH csr_chk_taxability INTO l_taxable;
630 -- If the above query returns no rows then check the state level taxablility rule
631 -- as we are checking for SUBJ whable here. If we don't find a row for locality
632 -- subj whable, we must check for subj NWhable befor defaulting to state level.
633 -- NOTE currently is does not cover a situation where the specific element type
634 -- is not subject (WHable or NWhable) and the state is Whable.
635 IF csr_chk_taxability%NOTFOUND THEN
636 CLOSE csr_chk_taxability;
637 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
638 FETCH csr_chk_taxability INTO l_taxable;
639 IF csr_chk_taxability%NOTFOUND THEN -- 2
640 -- check for the existance of any taxability rules at this JD level.
641 -- if we get to this point and the csr_chk_all_taxability returns data
642 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
643 CLOSE csr_chk_taxability;
644 OPEN csr_chk_all_taxability (l_jurisdiction_code);
645 FETCH csr_chk_all_taxability INTO l_taxable;
646 IF csr_chk_all_taxability%NOTFOUND THEN --3
647 CLOSE csr_chk_all_taxability;
648 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
649 FETCH csr_chk_taxability INTO l_taxable;
650 CLOSE csr_chk_taxability;
651 ELSE -- 3
652 l_taxable := 'N';
653 CLOSE csr_chk_all_taxability;
654 END IF; -- 3
655 ELSE -- 2
656 l_taxable := 'N';
657 CLOSE csr_chk_taxability;
658 END IF; --2
659 ELSE
660 CLOSE csr_chk_taxability;
661 END IF;
662
663 ELSE -- csr_get_school_jd_level%NOT_FOUND
664 -- row found in cursor so this is a county school district
665 -- check the county TR
666
667 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
668 FETCH csr_chk_taxability INTO l_taxable;
669 -- If the above query returns no rows then check the state level taxablility rule
670 -- as we are checking for SUBJ whable here. If we don't find a row for locality
671 -- subj whable, we must check for subj NWhable befor defaulting to state level.
672 -- NOTE currently is does not cover a situation where the specific element type
673 -- is not subject (WHable or NWhable) and the state is Whable.
674 IF csr_chk_taxability%NOTFOUND THEN
675 CLOSE csr_chk_taxability;
676 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
677 FETCH csr_chk_taxability INTO l_taxable;
678 IF csr_chk_taxability%NOTFOUND THEN -- 2
679 -- check for the existance of any taxability rules at this JD level.
680 -- if we get to this point and the csr_chk_all_taxability returns data
681 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
682 CLOSE csr_chk_taxability;
683 OPEN csr_chk_all_taxability (g_county_jd);
684 FETCH csr_chk_all_taxability INTO l_taxable;
685 IF csr_chk_all_taxability%NOTFOUND THEN --3
686 CLOSE csr_chk_all_taxability;
687 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
688 FETCH csr_chk_taxability INTO l_taxable;
689 CLOSE csr_chk_taxability;
690 ELSE -- 3
691 l_taxable := 'N';
692 CLOSE csr_chk_all_taxability;
693 END IF; -- 3
694 ELSE -- 2
695 l_taxable := 'N';
696 CLOSE csr_chk_taxability;
697 END IF; --2
698 ELSE
699 CLOSE csr_chk_taxability;
700 END IF;
701 END IF; -- csr_get_school_jd_level%NOT_FOUND
702
703 CLOSE csr_get_school_jd_level;
704
705 END IF; -- state code = '36'
706
707 /* End of code for school district taxes. */
708
709
710 END IF;
711
712 ELSE
713 hr_utility.set_location(c_proc, 60);
714 -- otherwise we do not need to check taxability_rules
715 -- in order to set the value of the input value,
716 -- NB. that this step gets executed for tax elements like FIT, Medicare
717 -- as well as Tax balance elements like SUI_SUBJECT_EE
718 l_taxable := 'Y';
719 END IF;
720
721 ELSE
722 -- an Earnings Element so no taxability rules
723 hr_utility.set_location(c_proc, 70);
724
725 l_taxable := 'Y';
726
727 END IF;
728
729
730 IF (l_taxable = 'Y') THEN
731 hr_utility.set_location (c_proc, 200);
732
733 p_iv_tbl(p_row) := l_input_value_id;
734 p_iv_names_tbl(p_row) := p_input_name;
735 p_ev_tbl(p_row) := p_entry_value;
736 p_row := p_row + 1; -- next row in plsql table
737 END IF;
738
739 END process_input;
740
741
742
743 PROCEDURE fetch_wage_limits(
744 p_effective_date IN DATE DEFAULT NULL,
745 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
746 p_futa_wage_limit OUT NOCOPY NUMBER,
747 p_ss_ee_wage_limit OUT NOCOPY NUMBER,
748 p_ss_er_wage_limit OUT NOCOPY NUMBER,
749 p_sdi_ee_wage_limit OUT NOCOPY NUMBER,
750 p_sdi_er_wage_limit OUT NOCOPY NUMBER,
751 p_sui_ee_wage_limit OUT NOCOPY NUMBER,
752 p_sui_er_wage_limit OUT NOCOPY NUMBER) IS
753
754 c_proc VARCHAR2(100) := 'fetch_wage_limits';
755
756 l_futa_wage_limit NUMBER;
757 l_ss_ee_wage_limit NUMBER;
758 l_ss_er_wage_limit NUMBER;
759 l_sdi_ee_wage_limit NUMBER;
760 l_sdi_er_wage_limit NUMBER;
761 l_sui_ee_wage_limit NUMBER;
762 l_sui_er_wage_limit NUMBER;
763
764
765 CURSOR csr_get_fed_wage_limits(v_effective_date DATE) IS
766 SELECT ftax.futa_wage_limit,
767 ftax.ss_ee_wage_limit,
768 ftax.ss_er_wage_limit
769 FROM PAY_US_FEDERAL_TAX_INFO_F ftax
770 WHERE v_effective_date BETWEEN ftax.effective_start_date
771 AND ftax.effective_end_date
772 AND ftax.fed_information_category = '401K LIMITS';
773
774
775 CURSOR csr_get_state_wage_limits(v_effective_date DATE,
776 v_state_abbrev VARCHAR2) IS
777 SELECT ti.sdi_ee_wage_limit,
778 ti.sdi_er_wage_limit,
779 ti.sui_ee_wage_limit,
780 ti.sui_er_wage_limit
781 FROM PAY_US_STATES st,
782 PAY_US_STATE_TAX_INFO_F ti
783 WHERE v_effective_date BETWEEN
784 ti.effective_start_date AND ti.effective_end_date
785 and st.state_code =
786 ti.state_code
787 and st.state_abbrev = v_state_abbrev
788 ;
789
790
791
792 BEGIN
793 /*
794 ** fetch state level wage limits,
795 ** not all states have sdi/sui ee/er wage limits,
796 ** therefore do not check for success
797 */
798 OPEN csr_get_state_wage_limits(p_effective_date, p_state_abbrev);
799 FETCH csr_get_state_wage_limits INTO
800 l_sdi_ee_wage_limit,
801 l_sdi_er_wage_limit,
802 l_sui_ee_wage_limit,
803 l_sui_er_wage_limit;
804 CLOSE csr_get_state_wage_limits;
805
806
807 /*
808 ** fetch federal level wage limits
809 */
810 OPEN csr_get_fed_wage_limits(p_effective_date);
811 FETCH csr_get_fed_wage_limits INTO
812 l_futa_wage_limit,
813 l_ss_ee_wage_limit,
814 l_ss_er_wage_limit;
815 CLOSE csr_get_fed_wage_limits;
816
817
818 /*
819 ** always expect federal level wage limits,
820 ** if fetch failed then error, inform user
821 */
822 /** stub - find an apppriate error message **/
823 IF (l_futa_wage_limit IS NULL OR
824 l_ss_ee_wage_limit IS NULL OR
825 l_ss_er_wage_limit IS NULL) THEN
826 hr_utility.set_location(c_proc, 10);
827 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
828 hr_utility.raise_error;
829 END IF;
830
831
832 /*
833 ** copy limits INTO return parameters
834 */
835 p_futa_wage_limit := l_futa_wage_limit;
836 p_ss_ee_wage_limit := l_ss_ee_wage_limit;
837 p_ss_er_wage_limit := l_ss_er_wage_limit;
838 p_sdi_ee_wage_limit := l_sdi_ee_wage_limit;
839 p_sdi_er_wage_limit := l_sdi_er_wage_limit;
840 p_sui_ee_wage_limit := l_sui_ee_wage_limit;
841 p_sui_er_wage_limit := l_sui_er_wage_limit;
842
843 END fetch_wage_limits;
844
845
846 /* NOTE: Though the code still resides here for MEDICARE EE and
847 MEDICARE ER we will not call the process_limits procedure for
848 those elements
849 */
850
851 PROCEDURE process_limits(
852 p_element_type IN VARCHAR2,
853 p_earn_amount IN NUMBER,
854 p_iv_tbl IN Hr_Entry.number_table,
855 p_iv_names_tbl IN Hr_Entry.varchar2_table,
856 p_ev_tbl IN OUT NOCOPY Hr_Entry.varchar2_table,
857 p_num_ev IN NUMBER,
858 p_assignment_id IN NUMBER,
859 p_jurisdiction IN VARCHAR2,
860 p_tax_unit_id IN VARCHAR2,
861 p_adjustment_date IN DATE) IS
862
863 c_proc VARCHAR2(100) := 'process_limits';
864
865 l_return_bal VARCHAR2(30);
866 l_adj_amt NUMBER;
867 l_excess NUMBER;
868 l_taxable_iv_pos NUMBER := 0;
869 l_old_taxable_bal NUMBER;
870 l_limit NUMBER;
871 l_asg_type VARCHAR2(6) := 'PER';
872
873 l_virtual_adjustment_date date;
874 l_limit_subject_bal number:=0;
875 BEGIN
876
877 FOR l_i IN 1..(p_num_ev - 1) LOOP
878 FOR l_j IN 1..1000 LOOP
879 NULL;
880 END LOOP;
881 END LOOP;
882
883 /*
884 ** find position of TAXABLE IV in tbl structure
885 */
886 FOR l_i IN 1..(p_num_ev - 1) LOOP
887 IF (p_iv_names_tbl(l_i) = 'TAXABLE') THEN
888 l_taxable_iv_pos := l_i;
889 END IF;
890 END LOOP;
891
892 /*
893 ** set up taxable balance and limit for limit processing
894 */
895
896 /* Rmonge 17-NOV-2001 */
897 /* For each IF statment to get the taxable balance, I have added
898 a call to PAY_US_TAX_BALS_PKG.US_TAX_BALANCE. The package is going to
899 return the Adjusted Subject To Tax Balance for the element being
900 processed.
901 */
902 /* TCLEWIS 02-25-2002
903 In our fetches of reduced_subj_whable we must fetch the balance as of
904 the end of the year.
905 */
906
907 l_virtual_adjustment_date := add_months(trunc(p_adjustment_date,'Y'),12) -1;
908 /*l_virtual_adjustment_date for bug 4721086*/
909
910 IF (p_element_type = 'Medicare_EE') THEN
911 l_old_taxable_bal := g_medicare_ee_taxable;
912 /*
913 ** Medicare EE and ER should have an infinite limit,
914 ** at a later stage a legislative limit may be defined,
915 ** therefore set to an arbitary value (99,999,999),
916 ** as used in PAY_US_STATE_TAX_INFO_F for NY
917 */
918 l_limit := 99999999;
919
920 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
921 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
922 p_tax_type => 'MEDICARE',
923 p_ee_or_er => 'EE',
924 p_time_type => 'YTD',
925 p_asg_type => l_asg_type,
926 p_gre_id_context => p_tax_unit_id,
927 p_jd_context => p_jurisdiction,
928 p_assignment_action_id => NULL,
929 p_assignment_id => p_assignment_id,
930 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
931
932 ELSIF (p_element_type = 'Medicare_ER') THEN
933 l_old_taxable_bal := g_medicare_er_taxable;
934 l_limit := 99999999;
935
936 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
937 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
938 p_tax_type => 'MEDICARE',
939 p_ee_or_er => 'ER',
940 p_time_type => 'YTD',
941 p_asg_type => l_asg_type,
942 p_gre_id_context => p_tax_unit_id,
943 p_jd_context => p_jurisdiction,
944 p_assignment_action_id => NULL,
945 p_assignment_id => p_assignment_id,
946 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
947
948 ELSIF (p_element_type = 'FUTA') THEN
949
950 l_old_taxable_bal := g_futa_taxable;
951
952 l_limit := g_futa_wage_limit;
953 if g_tax_group <> 'NOT_ENTERED' Then
954 l_asg_type := 'PER';
955 -- l_asg_type := 'PER_TG';
956 else
957 l_asg_type := 'PER';
958 end if;
959
960 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
961 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
962 p_tax_type => 'FUTA',
963 p_ee_or_er => 'ER',
964 p_time_type => 'YTD',
965 p_asg_type => l_asg_type,
966 p_gre_id_context => p_tax_unit_id,
967 p_jd_context => p_jurisdiction,
968 p_assignment_action_id => NULL,
969 p_assignment_id => p_assignment_id,
970 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
971
972 ELSIF (p_element_type = 'SS_EE') THEN
973 l_old_taxable_bal := g_ss_ee_taxable;
974 l_limit := g_ss_ee_wage_limit;
975
976 if g_tax_group <> 'NOT_ENTERED' Then
977 l_asg_type := 'PER';
978 -- l_asg_type := 'PER_TG';
979 else
980 l_asg_type := 'PER';
981 end if;
982
983 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
984 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
985 p_tax_type => 'SS',
986 p_ee_or_er => 'EE',
987 p_time_type => 'YTD',
988 p_asg_type => l_asg_type,
989 p_gre_id_context => p_tax_unit_id,
990 p_jd_context => p_jurisdiction,
991 p_assignment_action_id => NULL,
992 p_assignment_id => p_assignment_id,
993 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
994
995 ELSIF (p_element_type = 'SS_ER') THEN
996 l_old_taxable_bal := g_ss_er_taxable;
997 l_limit := g_ss_er_wage_limit;
998
999 if g_tax_group <> 'NOT_ENTERED' Then
1000 l_asg_type := 'PER';
1001 -- l_asg_type := 'PER_TG';
1002 else
1003 l_asg_type := 'PER';
1004 end if;
1005
1006 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1007 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1008 p_tax_type => 'SS',
1009 p_ee_or_er => 'ER',
1010 p_time_type => 'YTD',
1011 p_asg_type => l_asg_type,
1012 p_gre_id_context => p_tax_unit_id,
1013 p_jd_context => p_jurisdiction,
1014 p_assignment_action_id => NULL,
1015 p_assignment_id => p_assignment_id,
1016 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1017
1018 ELSIF (p_element_type = 'SDI_EE') THEN
1019 l_old_taxable_bal := g_sdi_ee_taxable;
1020 l_limit := g_sdi_ee_wage_limit;
1021
1022 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1023 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1024 p_tax_type => 'SDI',
1025 p_ee_or_er => 'EE',
1026 p_time_type => 'YTD',
1027 p_asg_type => 'PER',
1028 p_gre_id_context => p_tax_unit_id,
1029 p_jd_context => p_jurisdiction,
1030 p_assignment_action_id => NULL,
1031 p_assignment_id => p_assignment_id,
1032 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1033
1034 ELSIF (p_element_type = 'SDI_ER') THEN
1035 l_old_taxable_bal := g_sdi_er_taxable;
1036 l_limit := g_sdi_er_wage_limit;
1037
1038 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1039 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1040 p_tax_type => 'SDI',
1041 p_ee_or_er => 'ER',
1042 p_time_type => 'YTD',
1043 p_asg_type => 'PER',
1044 p_gre_id_context => p_tax_unit_id,
1045 p_jd_context => p_jurisdiction,
1046 p_assignment_action_id => NULL,
1047 p_assignment_id => p_assignment_id,
1048 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1049
1050 ELSIF (p_element_type = 'SUI_EE') THEN
1051 l_old_taxable_bal := g_sui_ee_taxable;
1052 l_limit := g_sui_ee_wage_limit;
1053
1054 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1055 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1056 p_tax_type => 'SUI',
1057 p_ee_or_er => 'EE',
1058 p_time_type => 'YTD',
1059 p_asg_type => 'PER',
1060 p_gre_id_context => p_tax_unit_id,
1061 p_jd_context => p_jurisdiction,
1062 p_assignment_action_id => NULL,
1063 p_assignment_id => p_assignment_id,
1064 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1065
1066 ELSIF (p_element_type = 'SUI_ER') THEN
1067 l_old_taxable_bal := g_sui_er_taxable;
1068 l_limit := g_sui_er_wage_limit;
1069
1070 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1071 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1072 p_tax_type => 'SUI',
1073 p_ee_or_er => 'ER',
1074 p_time_type => 'YTD',
1075 p_asg_type => 'PER',
1076 p_gre_id_context => p_tax_unit_id,
1077 p_jd_context => p_jurisdiction,
1078 p_assignment_action_id => NULL,
1079 p_assignment_id => p_assignment_id,
1080 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1081 ELSE
1082 /** stub - find appropriate message **/
1083 hr_utility.set_location(c_proc, 10);
1084 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
1085 hr_utility.raise_error;
1086
1087 END IF;
1088
1089
1090 /*
1091 ** generic block, applies to all limit processing
1092 ** Excess is never passed or adjusted as it is a derived balance
1093 */
1094
1095 hr_utility.trace('P_earn_amount='||to_char(p_earn_amount));
1096 hr_utility.trace('subject balance = ' || to_char(l_limit_subject_bal));
1097
1098 IF ((l_old_taxable_bal + p_earn_amount) <= l_limit) THEN
1099
1100 /*
1101 ** no limit exceeded,
1102 ** ok to make the balance adjustment,
1103 ** do nothing with EV amount of TAXABLE IV
1104 */
1105 /* Rosie Monge 14-NOV-2001 */
1106
1107 /* if the p_earn_amount (adjustment amount made ) is Negative
1108 we need to account for 3 different possibilities.
1109 1) Subject Taxable Balance is grater than the limit (7000)
1110 In this scenario, The balance after the Adjustment is made
1111 is grater than the Limit, so it is not necessary to adjust
1112 the amount, because it is at its maximun already.
1113
1114 2) Subject Taxable Balance is between the limit (0 -7000)
1115 If the Adjusted Subject Balance is between the limit, then,
1116 it is necessary to calculate how much the adjustment will be.
1117 This amount is the Limit_Subject_Balance - limit (7000).
1118 3) Subject Taxable Balance is Negative (less than 0).
1119 If the Subject Taxable Balance is Negative, then, we have to
1120 substract the entire balance, so that we make it 0.
1121 */
1122
1123 /* note the limit subject balance has already been adjusted for
1124 the gross earnings element has been processed.
1125 */
1126
1127
1128 if p_earn_amount < 0 then -- negative adjustment reguires special
1129 -- attentions.
1130
1131 if ( l_limit_subject_bal ) >= l_limit then
1132
1133 l_adj_amt := 0;
1134
1135 elsif (l_limit_subject_bal ) >= 0 and
1136 (l_limit_subject_bal ) < l_limit then
1137
1138
1139 if (l_limit_subject_bal - p_earn_amount) <> l_old_taxable_bal then
1140
1141 if (l_limit_subject_bal - p_earn_amount) < l_limit then
1142 /* subject balance is below the limit and not = to taxable
1143 make adjustment on the taxable balance and ignore the
1144 subject balance
1145 */
1146 if l_old_taxable_bal - l_adj_amt < 0 then
1147 /* if the amount of the adjustment is greater that taxbale
1148 the adjust taxable to 0
1149 */
1150 l_adj_amt := l_old_taxable_bal * -1;
1151 else
1152 /* The taxable balance + the adjustment (which is negative)
1153 will not = 0, to take full amount of the adjustment
1154 */
1155 l_adj_amt := p_earn_amount;
1156 end if;
1157 else
1158 /* subject is over the limit so adjust taxable based on subject
1159 balance
1160 */
1161 l_adj_amt := (l_limit_subject_bal ) - l_limit;
1162
1163 /* check to make sure that the adjustment amount will
1164 not cause taxable to go negative. If this occurs
1165 then adjust taxable to 0 (zero)
1166 */
1167 if l_old_taxable_bal - l_adj_amt < 0 then
1168 /* if the amount of the adjustment is greater that taxbale
1169 the adjust taxable to 0
1170 */
1171 l_adj_amt := l_old_taxable_bal * -1;
1172 end if;
1173 end if;
1174 else
1175 /* is subject is below the limit then the adjustment should be ok
1176 */
1177
1178 l_adj_amt := p_earn_amount;
1179
1180 end if;
1181
1182 elsif (l_limit_subject_bal < 0 ) then
1183
1184 l_adj_amt := l_old_taxable_bal * -1;
1185
1186 end if;
1187 p_ev_tbl(l_taxable_iv_pos) :=
1188 fnd_number.number_to_canonical(l_adj_amt);
1189
1190 end if;
1191
1192 ELSIF ((l_old_taxable_bal > l_limit) or
1193 ((l_old_taxable_bal + p_earn_amount) < 0 )) THEN
1194
1195 /*
1196 ** taxable balance already exceeds limit or if sum of old and
1197 ** adj amount is -ve, set EV amount of TAXABLE IV to 0,
1198 ** therefore the EV amount feeds Excess
1199 ** put EV amount of TAXABLE IV INTO excess
1200 */
1201 p_ev_tbl(l_taxable_iv_pos) := 0;
1202
1203 ELSIF (l_old_taxable_bal + p_earn_amount > l_limit) THEN
1204 /*
1205 ** EV amount of TAXABLE IV will cause limit to be exceeded,
1206 ** set EV amount up to limit
1207 */
1208
1209 hr_utility.trace('in the elsif l_old_tax_amount + p_earn_amount > 0');
1210
1211 l_adj_amt := l_limit - l_old_taxable_bal;
1212 hr_utility.trace('l_adj_amt = '||to_char(l_adj_amt));
1213
1214 l_excess := (p_earn_amount + l_old_taxable_bal) - l_limit;
1215 hr_utility.trace('l_excess ='|| to_char(l_excess));
1216 /*
1217 ** modify EV amount of TAXABLE IV before BA processing,
1218 ** set EV amount up to limit, remainder goes INTO excess
1219 */
1220 p_ev_tbl(l_taxable_iv_pos) := fnd_number.number_to_canonical(l_adj_amt);
1221
1222 END IF;
1223
1224 END process_limits;
1225
1226
1227
1228 PROCEDURE process_element(
1229 p_assignment_id IN NUMBER,
1230 p_consolidation_set_id IN NUMBER,
1231 p_element_type IN VARCHAR2,
1232 p_abbrev_element_type IN VARCHAR2,
1233 p_bg_id IN NUMBER,
1234 p_adjustment_date IN DATE,
1235 p_earn_amount IN NUMBER,
1236 p_adj_amount IN NUMBER,
1237 p_jurisdiction IN VARCHAR2,
1238 p_payroll_action_id IN NUMBER,
1239 p_tax_unit_id IN VARCHAR2,
1240 p_balance_adj_costing_flag IN VARCHAR2
1241 ) IS
1242
1243 c_proc VARCHAR2(100) := 'process_element';
1244
1245 -- p_abbrev_element_type - shorter name for the element,
1246 -- used to ensure that the group key for all the adjustments
1247 -- does not exceed 240 chars (assuming that the
1248 -- length of payroll_action_id <= 7
1249 -- p_earn_amount - gross earnings. i.e. p_gross_amount
1250 -- p_adj_amount - amount of the tax withheld
1251 -- p_jurisdiction - jd where the tax was withheld
1252
1253 CURSOR csr_element IS
1254 SELECT e.element_type_id,
1255 c.classification_name,
1256 e.element_information_category earnings_lookup_type,
1257 e.classification_id,
1258 e.element_information1 earnings_category
1259 FROM PAY_ELEMENT_CLASSIFICATIONS c,
1260 PAY_ELEMENT_TYPES_F e,
1261 hr_organization_information hoi
1262 WHERE e.element_name = p_element_type
1263 AND (e.business_group_id = p_bg_id
1264 OR e.business_group_id IS NULL
1265 )
1266 AND e.classification_id = c.classification_id
1267 AND p_adjustment_date BETWEEN
1268 e.effective_start_date AND e.effective_end_date
1269 AND hoi.organization_id = p_bg_id
1270 AND hoi.org_information_context = 'Business Group Information'
1271 AND c.legislation_code = hoi.org_information9
1272 ;
1273
1274 CURSOR csr_set_mandatory_inputs (v_element_type_id NUMBER) IS
1275 SELECT i.name INPUT_NAME,
1276 i.input_value_id,
1277 NVL(hr.meaning, NVL(i.default_value,
1278 DECODE(i.uom,
1279 'I', '0',
1280 'M', '0',
1281 'N', '0',
1282 'T', '0',
1283 'C', 'Unknown - US_TAX_BAL_ADJ',
1284 'H_DECIMAL1', '0.0',
1285 'H_DECIMAL2', '0.00',
1286 'H_DECIMAL3', '0.000',
1287 'H_HH', '12',
1288 'H_HHMM', '12:00',
1289 'H_HHMMSS', '12:00:00',
1290 'D', fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_adjustment_date)),
1291 'ND', To_Char(p_adjustment_date, 'Day')))
1292 ) default_value
1293 FROM HR_LOOKUPS hr,
1294 PAY_INPUT_VALUES_F i
1295 WHERE i.element_type_id = v_element_type_id
1296 AND i.mandatory_flag = 'Y'
1297 AND i.default_value = hr.lookup_code (+)
1298 AND i.lookup_type = hr.lookup_type (+)
1299 AND i.name NOT IN ('Pay Value')
1300 ;
1301
1302 l_iv_tbl hr_entry.number_table;
1303 l_iv_names_tbl hr_entry.varchar2_table;
1304 l_ev_tbl hr_entry.varchar2_table;
1305 l_num_ev NUMBER;
1306 l_element csr_element%ROWTYPE;
1307 l_ele_link_id NUMBER;
1308 l_counter NUMBER;
1309 l_payroll_action_id NUMBER;
1310
1311 BEGIN
1312
1313 hr_utility.trace('IN Process_element Element_type ='||p_element_type);
1314 HR_Utility.trace('Abbrev Element Type ='||p_abbrev_element_type);
1315
1316 hr_utility.set_location(c_proc, 10);
1317 OPEN csr_element;
1318 FETCH csr_element INTO l_element;
1319 CLOSE csr_element;
1320
1321 IF (l_element.element_type_id IS NULL) THEN
1322 hr_utility.set_location(c_proc, 20);
1323 hr_utility.set_message(801, 'HR_6884_ELE_ENTRY_NO_ELEMENT');
1324 hr_utility.raise_error;
1325 END IF;
1326
1327 hr_utility.set_location(c_proc, 30);
1328 l_ele_link_id := hr_entry_api.get_link(
1329 p_assignment_id => p_assignment_id,
1330 p_element_type_id => l_element.element_type_id,
1331 p_session_date => p_adjustment_date);
1332
1333 IF (l_ele_link_id IS NULL) THEN
1334 hr_utility.set_location(c_proc, 40);
1335 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1336 hr_utility.set_message_token ('ELEMENT', p_element_type);
1337 hr_utility.raise_error;
1338 END IF;
1339
1340 -- initialize tables
1341 l_iv_names_tbl := g_dummy_varchar_tbl;
1342 l_iv_tbl := g_dummy_number_tbl;
1343 l_ev_tbl := g_dummy_varchar_tbl;
1344 l_num_ev := 1;
1345
1346 -- explicitly set the various input values,
1347 -- this clearly identifies which input values are expected and will cause failure
1348 -- if the input value has been deleted somehow
1349 hr_utility.set_location(c_proc, 50);
1350
1351 IF (l_element.classification_name IN ('Earnings', 'Imputed Earnings',
1352 'Supplemental Earnings')) THEN
1353 -- element is an Earnings element,
1354 -- populate the global tables to be used later for taxability checking for
1355 -- subject withholdable, not-withholdable input values of tax balance elements
1356 g_classification_id := l_element.classification_id;
1357 g_earnings_category := l_element.earnings_category;
1358 g_classification := l_element.classification_name;
1359
1360 process_input(p_element_type, l_element.element_type_id,
1361 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1362 p_bg_id, p_adjustment_date,
1363 'Pay Value', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1364
1365 ELSIF (p_element_type IN ('FIT')) THEN
1366 hr_utility.set_location (c_proc, 60);
1367 process_input(p_element_type, l_element.element_type_id,
1368 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1369 p_bg_id, p_adjustment_date,
1370 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1371
1372 IF (g_classification = 'Supplemental Earnings') THEN
1373 process_input(p_element_type, l_element.element_type_id,
1374 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1375 p_bg_id, p_adjustment_date,
1376 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1377 END IF;
1378 -- 4188782
1379 ELSIF (p_element_type IN ('FSP_SUBJECT')) THEN
1380 hr_utility.set_location (c_proc, 62);
1381 process_input(p_element_type, l_element.element_type_id,
1382 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1383 p_bg_id, p_adjustment_date,
1384 'Reduced Subj Whable',
1385 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1386
1387 ELSIF (p_element_type IN ('FIT 3rd Party')) THEN
1388 hr_utility.set_location (c_proc, 65);
1389 process_input(p_element_type, l_element.element_type_id,
1390 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1391 p_bg_id, p_adjustment_date,
1392 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1393
1394 ELSIF (p_element_type IN ('SS_EE', 'Medicare_EE')) THEN
1395 hr_utility.set_location(c_proc, 71);
1396 IF (p_adj_amount <> 0) THEN
1397 process_input(p_element_type, l_element.element_type_id,
1398 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1399 p_bg_id, p_adjustment_date,
1400 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1401 END IF;
1402
1403 hr_utility.set_location(c_proc, 72);
1404 process_input(p_element_type, l_element.element_type_id,
1405 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1406 p_bg_id, p_adjustment_date,
1407 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1408
1409 /*
1410 ** cap the EV amount for the TAXABLE IV if necessary
1411 */
1412
1413 /* MEDICARE EE has no limit */
1414 IF p_element_type = 'SS_EE' THEN
1415 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1416 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1417 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1418 END IF;
1419
1420
1421 -- SD1
1422 ELSIF (p_element_type IN ('Medicare_ER', 'SS_ER', 'FUTA')) THEN
1423 /** sbilling **/
1424 /*
1425 ** only if processing Medicare_ER, SS_ER or FUTA, the Pay Value should be set
1426 ** to the corresponding field on the TBA form (Medicare, FUTA_ER or SS),
1427 */
1428 IF (p_adj_amount <> 0) THEN
1429 process_input(p_element_type, l_element.element_type_id,
1430 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1431 p_bg_id, p_adjustment_date,
1432 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1433 END IF;
1434
1435 process_input(p_element_type, l_element.element_type_id,
1436 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1437 p_bg_id, p_adjustment_date,
1438 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1439
1440 /*
1441 ** cap the EV amount for the TAXABLE IV if necessary
1442 */
1443
1444 /* MEDICARE EE has no limit */
1445 IF (p_element_type IN ( 'SS_ER', 'FUTA')) THEN
1446 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1447 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1448 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1449 END IF;
1450
1451 ELSIF (p_element_type IN ('SIT_WK')) THEN
1452 hr_utility.set_location(c_proc, 81);
1453
1454 process_input(p_element_type, l_element.element_type_id,
1455 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1456 p_bg_id, p_adjustment_date,
1457 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1458 hr_utility.set_location(c_proc, 82);
1459
1460 process_input(p_element_type, l_element.element_type_id,
1461 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1462 p_bg_id, p_adjustment_date,
1463 'Jurisdiction', p_jurisdiction, l_num_ev);
1464
1465 IF (g_classification = 'Supplemental Earnings') THEN
1466 process_input(p_element_type, l_element.element_type_id,
1467 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1468 p_bg_id, p_adjustment_date,
1469 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1470 END IF;
1471
1472 /** sbilling **/
1473 /*
1474 ** new tax element to be processed, use SIT_WK as a template
1475 */
1476 ELSIF (p_element_type IN ('County_SC_WK')) THEN
1477 hr_utility.set_location(c_proc, 81);
1478
1479 process_input(p_element_type, l_element.element_type_id,
1480 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1481 p_bg_id, p_adjustment_date,
1482 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1483 hr_utility.set_location(c_proc, 82);
1484
1485
1486 /*
1487 ** can't put the Gross for the BA INTO the Gross for the school district tax,
1488 ** County_SC_WK has no TAXABLE input
1489 */
1490 process_input(p_element_type, l_element.element_type_id,
1491 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1492 p_bg_id, p_adjustment_date,
1493 'Jurisdiction', p_jurisdiction, l_num_ev);
1494
1495 ELSIF (p_element_type IN ('SUI_EE', 'SDI_EE')) THEN
1496 hr_utility.set_location(c_proc, 91);
1497
1498 IF (p_adj_amount <> 0) THEN
1499 process_input(p_element_type, l_element.element_type_id,
1500 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1501 p_bg_id, p_adjustment_date,
1502 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1503 hr_utility.set_location(c_proc, 915);
1504 END IF;
1505
1506 hr_utility.set_location(c_proc, 92);
1507
1508 process_input(p_element_type, l_element.element_type_id,
1509 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1510 p_bg_id, p_adjustment_date,
1511 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1512 hr_utility.set_location(c_proc, 93);
1513
1514 process_input(p_element_type, l_element.element_type_id,
1515 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1516 p_bg_id, p_adjustment_date,
1517 'Jurisdiction', p_jurisdiction, l_num_ev);
1518
1519 /*
1520 ** cap the EV amount for the TAXABLE EV if necessary
1521 */
1522 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1523 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1524 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1525
1526 ELSIF (p_element_type IN ('City_WK', 'County_WK')) THEN
1527 hr_utility.set_location(c_proc, 101);
1528
1529 process_input(p_element_type, l_element.element_type_id,
1530 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1531 p_bg_id, p_adjustment_date,
1532 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1533 hr_utility.set_location(c_proc, 102);
1534
1535 process_input(p_element_type, l_element.element_type_id,
1536 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1537 p_bg_id, p_adjustment_date,
1538 'Jurisdiction', p_jurisdiction, l_num_ev);
1539
1540 ELSIF (p_element_type IN ('SIT_SUBJECT_WK', 'City_SUBJECT_WK',
1541 'County_SUBJECT_WK', 'School_SUBJECT_WK')) THEN
1542 hr_utility.set_location(c_proc, 111);
1543
1544 process_input(p_element_type, l_element.element_type_id,
1545 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1546 p_bg_id, p_adjustment_date,
1547 'Jurisdiction', p_jurisdiction, l_num_ev);
1548 hr_utility.set_location(c_proc, 112);
1549
1550 process_input(p_element_type, l_element.element_type_id,
1551 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1552 p_bg_id, p_adjustment_date,
1553 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1554 hr_utility.set_location(c_proc, 113);
1555
1556 process_input(p_element_type, l_element.element_type_id,
1557 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1558 p_bg_id, p_adjustment_date,
1559 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1560 hr_utility.set_location(c_proc, 114);
1561
1562 IF (g_classification IN ('Imputed Earnings',
1563 'Supplemental Earnings')) THEN
1564 hr_utility.set_location(c_proc, 115);
1565
1566 process_input (p_element_type, l_element.element_type_id,
1567 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1568 p_bg_id, p_adjustment_date,
1569 'Subj NWhable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1570 END IF;
1571
1572 ELSIF (p_element_type IN ('SDI_SUBJECT_EE', 'SDI_SUBJECT_ER',
1573 'SUI_SUBJECT_EE', 'SUI_SUBJECT_ER')) THEN
1574 hr_utility.set_location(c_proc, 121);
1575
1576 process_input(p_element_type, l_element.element_type_id,
1577 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1578 p_bg_id, p_adjustment_date,
1579 'Jurisdiction', p_jurisdiction, l_num_ev);
1580 hr_utility.set_location(c_proc, 122);
1581
1582 process_input(p_element_type, l_element.element_type_id,
1583 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1584 p_bg_id, p_adjustment_date,
1585 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1586 hr_utility.set_location(c_proc, 123);
1587
1588 process_input(p_element_type, l_element.element_type_id,
1589 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1590 p_bg_id, p_adjustment_date,
1591 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1592
1593 ELSIF (p_element_type IN ('SUI_ER', 'SDI_ER')) THEN
1594 hr_utility.set_location (c_proc, 124);
1595
1596
1597 /** sbilling **/
1598 /*
1599 ** for SUI_ER and SDI_ER set the amount to be paid for tax equal
1600 ** to the amount entered on the corresponding ER field
1601 */
1602 IF (p_adj_amount <> 0) THEN
1603 process_input(p_element_type, l_element.element_type_id,
1604 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1605 p_bg_id, p_adjustment_date,
1606 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1607 END IF;
1608
1609 process_input(p_element_type, l_element.element_type_id,
1610 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1611 p_bg_id, p_adjustment_date,
1612 'Jurisdiction', p_jurisdiction, l_num_ev);
1613
1614 process_input(p_element_type, l_element.element_type_id,
1615 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1616 p_bg_id, p_adjustment_date,
1617 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1618
1619 /*
1620 ** cap the EV amount for the TAXABLE IV if necessary
1621 */
1622 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1623 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1624 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1625 END IF;
1626
1627 -- because process_input will increment l_num_ev if it is successful
1628 l_num_ev := l_num_ev - 1;
1629
1630
1631 -- set mandatory input values,
1632 -- cannot set these to NULL, core package expects mandatory values to be entered
1633 hr_utility.set_location(c_proc, 130);
1634
1635 FOR l_req_input IN csr_set_mandatory_inputs (l_element.element_type_id) LOOP
1636 -- first, check if the mandatory input value was explicitly
1637 -- set above, do nothing in this case
1638 hr_utility.set_location(c_proc, 140);
1639
1640 FOR l_counter IN 1..l_num_ev LOOP
1641
1642 IF (l_req_input.input_name = l_iv_names_tbl(l_counter)) THEN
1643 NULL;
1644 ELSE
1645 -- then the input value was not previously set by one of the
1646 -- process_inputs called in process_elements
1647 hr_utility.set_location(c_proc, 150);
1648 l_num_ev := l_num_ev + 1;
1649
1650 l_iv_tbl(l_num_ev) := l_req_input.input_value_id;
1651 l_iv_names_tbl(l_num_ev) := l_req_input.input_name;
1652 l_ev_tbl(l_num_ev) := l_req_input.default_value;
1653 END IF;
1654
1655 END LOOP;
1656 END LOOP;
1657
1658 hr_utility.set_location(c_proc, 160);
1659
1660 pay_bal_adjust.adjust_balance(p_batch_id => p_payroll_action_id,
1661 p_assignment_id => p_assignment_id,
1662 p_element_link_id => l_ele_link_id,
1663 p_num_entry_values => l_num_ev,
1664 p_entry_value_tbl => l_ev_tbl,
1665 p_input_value_id_tbl => l_iv_tbl,
1666 p_balance_adj_cost_flag => p_balance_adj_costing_flag);
1667
1668 END process_element;
1669
1670
1671 FUNCTION derive_jd_geocode(
1672 p_assignment_id IN NUMBER,
1673 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
1674 p_county_name IN VARCHAR2 DEFAULT NULL,
1675 p_city_name IN VARCHAR2 DEFAULT NULL,
1676 p_zip_code IN VARCHAR2 DEFAULT NULL)
1677 RETURN VARCHAR2 IS
1678
1679 c_proc VARCHAR2(100) := 'derive_jd_geocode';
1680
1681 CURSOR csr_state_code IS
1682 SELECT state_code
1683 FROM PAY_US_STATES
1684 WHERE state_abbrev = p_state_abbrev
1685 ;
1686
1687 CURSOR csr_county_code IS
1688 SELECT cn.state_code,
1689 cn.county_code
1690 FROM PAY_US_COUNTIES cn,
1691 PAY_US_STATES s
1692 WHERE cn.county_name = p_county_name
1693 and cn.state_code = s.state_code
1694 and s.state_abbrev = p_state_abbrev
1695 ;
1696
1697
1698 -- cursors to compare the location provided with the location of
1699 -- the employee's assignment
1700 CURSOR csr_chk_state IS
1701 SELECT 'PASS'
1702 FROM PAY_US_EMP_STATE_TAX_RULES st,
1703 PAY_US_STATES pus
1704 WHERE st.assignment_id = p_assignment_id
1705 and st.state_code = pus.state_code
1706 and pus.state_abbrev = p_state_abbrev
1707 ;
1708
1709 CURSOR csr_chk_local (x_jd VARCHAR2) IS
1710 SELECT 'PASS'
1711 FROM PAY_US_EMP_CITY_TAX_RULES
1712 WHERE assignment_id = p_assignment_id
1713 and jurisdiction_code = x_jd
1714 UNION
1715 SELECT 'PASS'
1716 FROM PAY_US_EMP_COUNTY_TAX_RULES
1717 WHERE assignment_id = p_assignment_id
1718 and jurisdiction_code = x_jd
1719 ;
1720
1721 l_geocode VARCHAR2(11) := '00-000-0000';
1722 l_county_code VARCHAR2(4) := '000' ;
1723 l_state_code VARCHAR2(2) := '00' ;
1724 l_valid_for_asg VARCHAR2(4) := 'FAIL' ;
1725
1726 BEGIN
1727
1728 IF (p_city_name IS NOT NULL AND p_zip_code IS NOT NULL) THEN
1729 hr_utility.set_location(c_proc, 10);
1730 l_geocode := hr_us_ff_udfs.addr_val(
1731 p_state_abbrev => p_state_abbrev,
1732 p_county_name => p_county_name,
1733 p_city_name => p_city_name,
1734 p_zip_code => p_zip_code );
1735
1736 OPEN csr_chk_local(l_geocode);
1737 FETCH csr_chk_local INTO l_valid_for_asg;
1738 CLOSE csr_chk_local;
1739
1740 IF (l_valid_for_asg = 'FAIL') THEN
1741 hr_utility.set_location(c_proc, 15);
1742 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
1743 hr_utility.raise_error;
1744 END IF;
1745
1746 ELSIF (p_county_name IS NOT NULL AND p_state_abbrev IS NOT NULL) THEN
1747 hr_utility.set_location(c_proc, 20);
1748 OPEN csr_county_code;
1749 FETCH csr_county_code INTO l_state_code, l_county_code;
1750 CLOSE csr_county_code;
1751 l_geocode := l_state_code||'-'||l_county_code||'-0000';
1752
1753 OPEN csr_chk_local(l_geocode);
1754 FETCH csr_chk_local INTO l_valid_for_asg;
1755 CLOSE csr_chk_local;
1756
1757 IF (l_valid_for_asg = 'FAIL') THEN
1758 hr_utility.set_location(c_proc, 25);
1759 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
1760 hr_utility.raise_error;
1761 END IF;
1762
1763 ELSIF (p_county_name IS NULL AND p_state_abbrev IS NOT NULL) THEN
1764 hr_utility.set_location(c_proc, 30);
1765 OPEN csr_state_code;
1766 FETCH csr_state_code INTO l_state_code;
1767 CLOSE csr_state_code;
1768 l_geocode := l_state_code||'-000-0000';
1769
1770 OPEN csr_chk_state;
1771 FETCH csr_chk_state INTO l_valid_for_asg;
1772 CLOSE csr_chk_state;
1773
1774 IF (l_valid_for_asg = 'FAIL') THEN
1775 hr_utility.set_location(c_proc, 25);
1776 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
1777 hr_utility.raise_error;
1778 END IF;
1779
1780 ELSE
1781 l_geocode := '00-000-0000';
1782
1783 END IF;
1784
1785 Return (l_geocode);
1786
1787 END derive_jd_geocode;
1788
1789
1790
1791 FUNCTION taxable_balance(
1792 p_tax_bal_name IN VARCHAR2,
1793 p_ee_or_er IN VARCHAR2,
1794 p_tax_unit_id IN NUMBER,
1795 p_assignment_id IN NUMBER,
1796 p_adjustment_date IN DATE,
1797 p_geocode IN VARCHAR2 DEFAULT NULL)
1798 RETURN NUMBER IS
1799
1800 c_proc VARCHAR2(100) := 'taxable_balance';
1801
1802 l_return_bal NUMBER;
1803 l_date DATE;
1804 l_asg_type VARCHAR2(6);
1805
1806
1807 CURSOR csr_get_endofyear IS
1808 SELECT to_date('31/12/' || TO_CHAR(p_adjustment_date, 'YYYY'), 'DD/MM/YYYY')
1809 FROM SYS.DUAL
1810 ;
1811
1812 BEGIN
1813 /*
1814 ** find current balance for tax,
1815 ** assignment_id is used to find balance specific to a person,
1816 ** when calculating the adjustment amount up to the limit,
1817 ** the old TAXABLE balance is required
1818 */
1819
1820 /*
1821 ** fetch last day of year, require end of year balance, not date effective balance
1822 */
1823 OPEN csr_get_endofyear;
1824 FETCH csr_get_endofyear INTO l_date;
1825 CLOSE csr_get_endofyear;
1826
1827 IF g_tax_group <> 'NOT_ENTERED' and
1828 ( p_tax_bal_name = 'FUTA' or
1829 p_tax_bal_name = 'SS' ) THEN
1830 l_asg_type := 'PER';
1831 -- l_asg_type := 'PER_TG';
1832 ELSE
1833 l_asg_type := 'PER';
1834 END IF;
1835
1836 l_return_bal := pay_us_tax_bals_pkg.us_tax_balance(
1837 p_tax_balance_category => 'TAXABLE',
1838 p_tax_type => p_tax_bal_name,
1839 p_ee_or_er => p_ee_or_er,
1840 p_time_type => 'YTD',
1841 p_asg_type => l_asg_type,
1842 p_gre_id_context => p_tax_unit_id,
1843 p_jd_context => p_geocode,
1844 p_assignment_action_id => NULL,
1845 p_assignment_id => p_assignment_id,
1846 p_virtual_date => l_date);
1847
1848 Return(l_return_bal);
1849
1850 END taxable_balance;
1851
1852
1853
1854 FUNCTION tax_exists (p_jd_code VARCHAR2, p_tax_type VARCHAR2,
1855 p_adj_date DATE)
1856 RETURN VARCHAR2 IS
1857
1858 l_exists VARCHAR2(1) := 'N';
1859
1860 CURSOR sdi_er_exists IS
1861 SELECT 'Y'
1862 FROM pay_us_state_tax_info_f
1863 WHERE state_code = SUBSTR(p_jd_code, 1, 2)
1864 AND sdi_er_wage_limit IS NOT NULL
1865 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1866
1867 CURSOR sdi_ee_exists IS
1868 SELECT 'Y'
1869 FROM pay_us_state_tax_info_f
1870 WHERE state_code = SUBSTR(p_jd_code, 1, 2)
1871 AND sdi_ee_wage_limit IS NOT NULL
1872 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1873
1874 CURSOR sui_er_exists is
1875 SELECT 'Y'
1876 FROM pay_us_state_tax_info_f
1877 WHERE state_code = substr(p_jd_code, 1, 2)
1878 AND sui_er_wage_limit IS NOT NULL
1879 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1880
1881 CURSOR sui_ee_exists is
1882 SELECT 'Y'
1883 FROM pay_us_state_tax_info_f
1884 WHERE state_code = substr(p_jd_code, 1, 2)
1885 AND sui_ee_wage_limit IS NOT NULL
1886 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1887
1888 CURSOR sit_exists is
1889 SELECT sit_exists
1890 FROM pay_us_state_tax_info_f
1891 WHERE state_code = substr(p_jd_code, 1, 2)
1892 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1893
1894 CURSOR county_exists is
1895 SELECT county_tax
1896 FROM pay_us_county_tax_info_f
1897 WHERE jurisdiction_code = substr(p_jd_code, 1, 7)||'0000'
1898 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1899
1900 CURSOR city_exists is
1901 SELECT city_tax
1902 FROM pay_us_city_tax_info_f
1903 WHERE jurisdiction_code = p_jd_code
1904 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
1905
1906 BEGIN
1907
1908 IF (p_tax_type = 'SUI_ER') THEN
1909 OPEN sui_er_exists;
1910 FETCH sui_er_exists INTO l_exists;
1911 CLOSE sui_er_exists;
1912
1913 ELSIF (p_tax_type = 'SUI_EE') THEN
1914 OPEN sui_ee_exists;
1915 FETCH sui_ee_exists INTO l_exists;
1916 CLOSE sui_ee_exists;
1917
1918 ELSIF (p_tax_type = 'SDI_ER') THEN
1919 OPEN sdi_er_exists;
1920 FETCH sdi_er_exists INTO l_exists;
1921 CLOSE sdi_er_exists;
1922
1923 ELSIF (p_tax_type = 'SDI_EE') THEN
1924 OPEN sdi_ee_exists;
1925 FETCH sdi_ee_exists INTO l_exists;
1926 CLOSE sdi_ee_exists;
1927
1928 ELSIF (p_tax_type = 'SIT') THEN
1929 OPEN sit_exists;
1930 FETCH sit_exists INTO l_exists;
1931 CLOSE sit_exists;
1932
1933 ELSIF (p_tax_type = 'CITY') THEN
1934 OPEN city_exists;
1935 FETCH city_exists INTO l_exists;
1936 CLOSE city_exists;
1937
1938 ELSIF (p_tax_type = 'COUNTY') THEN
1939 OPEN county_exists;
1940 FETCH county_exists INTO l_exists;
1941 CLOSE county_exists;
1942
1943 ELSE
1944 NULL;
1945 END IF;
1946
1947 RETURN l_exists;
1948 END tax_exists;
1949
1950
1951
1952 PROCEDURE create_tax_balance_adjustment(
1953 p_validate IN BOOLEAN DEFAULT FALSE,
1954 p_adjustment_date IN DATE,
1955 p_business_group_name IN VARCHAR2,
1956 p_assignment_number IN VARCHAR2,
1957 p_tax_unit_id IN VARCHAR2,
1958 p_consolidation_set IN VARCHAR2,
1959 p_earning_element_type IN VARCHAR2 DEFAULT NULL,
1960 p_gross_amount IN NUMBER DEFAULT 0,
1961 p_net_amount IN NUMBER DEFAULT 0,
1962 p_FIT IN NUMBER DEFAULT 0,
1963 p_FIT_THIRD IN VARCHAR2 DEFAULT NULL,
1964 p_SS IN NUMBER DEFAULT 0,
1965 p_Medicare IN NUMBER DEFAULT 0,
1966 p_SIT IN NUMBER DEFAULT 0,
1967 p_SUI IN NUMBER DEFAULT 0,
1968 p_SDI IN NUMBER DEFAULT 0,
1969 p_County IN NUMBER DEFAULT 0,
1970 p_City IN NUMBER DEFAULT 0,
1971 p_city_name IN VARCHAR2 DEFAULT NULL,
1972 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
1973 p_county_name IN VARCHAR2 DEFAULT NULL,
1974 p_zip_code IN VARCHAR2 DEFAULT NULL,
1975 p_balance_adj_costing_flag IN VARCHAR2 DEFAULT NULL,
1976 p_balance_adj_prepay_flag IN VARCHAR2 DEFAULT 'N',
1977 p_futa_er IN NUMBER DEFAULT 0,
1978 p_sui_er IN NUMBER DEFAULT 0,
1979 p_sdi_er IN NUMBER DEFAULT 0,
1980 p_sch_dist_wh_ee IN NUMBER DEFAULT 0,
1981 p_sch_dist_jur IN VARCHAR2 DEFAULT NULL,
1982 p_payroll_action_id OUT NOCOPY NUMBER,
1983 p_create_warning OUT NOCOPY BOOLEAN)
1984 IS
1985
1986 c_proc VARCHAR2(100) := 'create_tax_balance_adjustment';
1987
1988 l_bg_id NUMBER;
1989 l_consolidation_set_id NUMBER;
1990 l_assignment_id NUMBER;
1991 l_payroll_id NUMBER;
1992 l_payroll_action_id NUMBER;
1993
1994 l_jd_entered VARCHAR2(11) := '00-000-0000';
1995 l_jd_level_entered NUMBER := 1;
1996 l_jd_level_needed NUMBER;
1997
1998 l_primary_asg_state VARCHAR2(2);
1999 l_create_warning BOOLEAN;
2000
2001 l_counter NUMBER;
2002 l_grp_key pay_payroll_actions.legislative_parameters%TYPE;
2003
2004 l_effective_start_date DATE;
2005 l_effective_end_date DATE;
2006 l_element_entry_id NUMBER;
2007 l_fed_tax_exempt VARCHAR2(1);
2008 l_futa_tax_exempt VARCHAR2(1);
2009 l_medicare_tax_exempt VARCHAR2(1);
2010 l_ss_tax_exempt VARCHAR2(1);
2011 l_sit_exempt VARCHAR2(1);
2012 l_sdi_exempt VARCHAR2(1);
2013 l_sui_exempt VARCHAR2(1);
2014 l_cnt_exempt VARCHAR2(1);
2015 l_cnt_sd_exempt VARCHAR2(1);
2016 l_cty_exempt VARCHAR2(1);
2017 l_cty_sd_exempt VARCHAR2(1);
2018
2019 -- Bug 4188782
2020 l_element_classification varchar2(100);
2021
2022 cursor get_element_details (p_element_type in varchar2,p_bg_id in number) is
2023 SELECT c.classification_name
2024 FROM PAY_ELEMENT_CLASSIFICATIONS c,
2025 PAY_ELEMENT_TYPES_F e,
2026 hr_organization_information hoi
2027 WHERE e.classification_id = c.classification_id
2028 AND hoi.organization_id = p_bg_id
2029 AND e.element_name = p_element_type
2030 AND (e.business_group_id = p_bg_id
2031 OR e.business_group_id IS NULL)
2032 AND hoi.org_information_context = 'Business Group Information'
2033 AND c.legislation_code = hoi.org_information9;
2034 ------------------------
2035
2036
2037 CURSOR csr_sdi_check IS
2038 SELECT region_2 primary_asg_state
2039 FROM HR_LOCATIONS loc,
2040 PER_ASSIGNMENTS_F asg,
2041 PER_BUSINESS_GROUPS bg
2042 -- Bug fix 1398865. Ensures one row is returned
2043 WHERE asg.assignment_number = p_assignment_number
2044 and asg.business_group_id = bg.business_group_id
2045 and bg.name ||'' = p_business_group_name
2046 and asg.effective_start_date <= p_adjustment_date
2047 AND asg.effective_end_date >= trunc(p_adjustment_date,'Y')
2048 and asg.primary_flag = 'Y'
2049 and asg.location_id = loc.location_id
2050 and loc.region_2 = p_state_abbrev;
2051
2052 CURSOR c_get_tax_group IS
2053 select decode(hoi.org_information5,
2054 NULL,'NOT_ENTERED',
2055 hoi.org_information5)
2056 from hr_organization_information hoi
2057 where hoi.organization_id = p_tax_unit_id
2058 and hoi.org_information_context = 'Federal Tax Rules'
2059 ;
2060
2061
2062
2063 CURSOR csr_sui_geocode IS
2064 SELECT sui_jurisdiction_code,
2065 pus.state_abbrev,
2066 fed.fit_exempt,
2067 fed.futa_tax_exempt,
2068 fed.medicare_tax_exempt,
2069 fed.ss_tax_exempt
2070 FROM pay_us_emp_fed_tax_rules_f fed,
2071 PER_ASSIGNMENTS_F a,
2072 PER_BUSINESS_GROUPS bg,
2073 pay_us_states pus
2074 WHERE fed.assignment_id = a.assignment_id
2075 and a.assignment_number = p_assignment_number
2076 and a.business_group_id = bg.business_group_id
2077 and bg.name ||'' = p_business_group_name
2078 and p_adjustment_date between fed.effective_start_date
2079 and fed.effective_end_date
2080 and p_adjustment_date BETWEEN
2081 a.effective_start_date and a.effective_end_date
2082 and fed.sui_state_code = pus.state_code
2083 ;
2084
2085 Cursor c_get_futa_self_adjust_method
2086 IS
2087 select hl.meaning
2088 from hr_organization_information hoi,
2089 hr_lookups hl
2090 where hoi.organization_id = p_tax_unit_id
2091 and hoi.org_information_context = 'Federal Tax Rules'
2092 and hoi.org_information3 = hl.LOOKUP_CODE
2093 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2094
2095 Cursor c_get_ss_self_adjust_method
2096 IS
2097 select hl.meaning
2098 from hr_organization_information hoi,
2099 hr_lookups hl
2100 where hoi.organization_id = p_tax_unit_id
2101 and hoi.org_information_context = 'Federal Tax Rules'
2102 and hoi.org_information1 = hl.LOOKUP_CODE
2103 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2104
2105 Cursor c_get_medi_self_adjust_method
2106 IS
2107 select hl.meaning
2108 from hr_organization_information hoi,
2109 hr_lookups hl
2110 where hoi.organization_id = p_tax_unit_id
2111 and hoi.org_information_context = 'Federal Tax Rules'
2112 and hoi.org_information2 = hl.LOOKUP_CODE
2113 and hl.lookup_type = 'MEDI_SELF_ADJ_CALC_METHOD';
2114
2115 Cursor c_get_sdi_self_adjust_method
2116 IS
2117 select hl.meaning
2118 from hr_organization_information hoi,
2119 hr_lookups hl
2120 where hoi.organization_id = p_tax_unit_id
2121 and hoi.org_information_context = 'State Tax Rules'
2122 and hoi.org_information1 = p_state_abbrev
2123 and hoi.org_information5 = hl.LOOKUP_CODE
2124 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2125
2126 Cursor c_get_sui_self_adjust_method
2127 IS
2128 select hl.meaning
2129 from hr_organization_information hoi,
2130 hr_lookups hl
2131 where hoi.organization_id = p_tax_unit_id
2132 and hoi.org_information_context = 'State Tax Rules'
2133 and hoi.org_information1 = p_state_abbrev
2134 and hoi.org_information4 = hl.LOOKUP_CODE --bug 3887144
2135 -- and hoi.org_information5 = hl.LOOKUP_CODE
2136 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2137
2138
2139 CURSOR csr_sit_exempt (cp_jurisdiction_code IN VARCHAR2)
2140 IS
2141 SELECT sta.sit_exempt,
2142 sta.sdi_exempt,
2143 sta.sui_exempt
2144 FROM pay_us_emp_state_tax_rules_f sta,
2145 PER_ASSIGNMENTS_F a,
2146 PER_BUSINESS_GROUPS bg,
2147 pay_us_states pus
2148 WHERE sta.assignment_id = a.assignment_id
2149 and a.assignment_number = p_assignment_number
2150 and a.business_group_id = bg.business_group_id
2151 and bg.name ||'' = p_business_group_name
2152 and p_adjustment_date between sta.effective_start_date
2153 and sta.effective_end_date
2154 and p_adjustment_date BETWEEN
2155 a.effective_start_date and a.effective_end_date
2156 and sta.jurisdiction_code = (substr(cp_jurisdiction_code,0,2) || '-000-0000')
2157 ;
2158
2159 CURSOR csr_county_exempt (cp_jurisdiction_code IN VARCHAR2)
2160 IS
2161 SELECT cnt.lit_exempt,
2162 cnt.sd_exempt
2163 FROM pay_us_emp_county_tax_rules_f cnt,
2164 PER_ASSIGNMENTS_F a,
2165 PER_BUSINESS_GROUPS bg,
2166 pay_us_states pus
2167 WHERE cnt.assignment_id = a.assignment_id
2168 and a.assignment_number = p_assignment_number
2169 and a.business_group_id = bg.business_group_id
2170 and bg.name ||'' = p_business_group_name
2171 and p_adjustment_date between cnt.effective_start_date
2172 and cnt.effective_end_date
2173 and p_adjustment_date BETWEEN
2174 a.effective_start_date and a.effective_end_date
2175 and cnt.jurisdiction_code = (substr(cp_jurisdiction_code,0,6) || '-0000')
2176 ;
2177
2178 CURSOR csr_city_exempt (cp_jurisdiction_code IN VARCHAR2)
2179 IS
2180 SELECT cty.lit_exempt,
2181 cty.sd_exempt
2182 FROM pay_us_emp_city_tax_rules_f cty,
2183 PER_ASSIGNMENTS_F a,
2184 PER_BUSINESS_GROUPS bg,
2185 pay_us_states pus
2186 WHERE cty.assignment_id = a.assignment_id
2187 and a.assignment_number = p_assignment_number
2188 and a.business_group_id = bg.business_group_id
2189 and bg.name ||'' = p_business_group_name
2190 and p_adjustment_date between cty.effective_start_date
2191 and cty.effective_end_date
2192 and p_adjustment_date BETWEEN
2193 a.effective_start_date and a.effective_end_date
2194 and cty.jurisdiction_code = cp_jurisdiction_code
2195 ;
2196
2197 -- local copy of the tax withhelds,
2198 -- by copying the values to local variables,
2199 -- we avoid defining parameters as IN/OUT variables
2200 l_gross_amount NUMBER := NVL(p_gross_amount, 0);
2201 l_net_amount NUMBER := NVL(p_net_amount, 0);
2202 l_fit NUMBER := NVL(p_fit, 0);
2203 l_ss NUMBER := NVL(p_ss, 0);
2204 l_medicare NUMBER := NVL(p_medicare, 0);
2205 l_sit NUMBER := NVL(p_sit, 0);
2206 l_sui_ee NUMBER := NVL(p_sui, 0);
2207 l_sdi_ee NUMBER := NVL(p_sdi, 0);
2208 l_city NUMBER := NVL(p_city, 0);
2209 l_county NUMBER := NVL(p_county, 0);
2210 l_total_taxes_withheld NUMBER;
2211 l_fit_third VARCHAR2(5) := NVL(p_FIT_THIRD, 'NO');
2212
2213 /** sbilling **/
2214 l_futa_er NUMBER := NVL(p_futa_er, 0);
2215 l_sui_er NUMBER := NVL(p_sui_er, 0);
2216 l_sdi_er NUMBER := NVL(p_sdi_er, 0);
2217 l_sch_dist_wh_ee NUMBER := NVL(p_sch_dist_wh_ee, 0);
2218 l_sch_dist_jur VARCHAR2(10) := NVL(p_sch_dist_jur, '');
2219
2220
2221 BEGIN
2222
2223 SAVEPOINT create_tax_bal_adjustment;
2224
2225 -- insert a row INTO fnd_session if there isn't one
2226 BEGIN
2227 INSERT INTO fnd_sessions(session_id, effective_date)
2228 SELECT USERENV('sessionid'), SYSDATE
2229 FROM DUAL
2230 WHERE NOT EXISTS (SELECT '1'
2231 FROM fnd_sessions
2232 WHERE session_id = USERENV('sessionid'));
2233
2234 END;
2235
2236 -- get assignment_id and business_group_id based on assignment number
2237 -- and business group name.
2238 BEGIN
2239 hr_utility.set_location(c_proc, 5);
2240 SELECT a.assignment_id,
2241 a.business_group_id,
2242 a.payroll_id
2243 INTO l_assignment_id,
2244 l_bg_id,
2245 l_payroll_id
2246 FROM per_business_groups bg,
2247 per_assignments_f a
2248 WHERE a.assignment_number = p_assignment_number
2249 and a.business_group_id = bg.business_group_id
2250 and bg.name ||'' = p_business_group_name
2251 and p_adjustment_date BETWEEN
2252 a.effective_start_date AND a.effective_end_date
2253 ;
2254 EXCEPTION
2255 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
2256 hr_utility.set_message(801, 'PY_51135_TXADJ_ASG_NOT_FOUND');
2257 hr_utility.raise_error;
2258 END;
2259
2260 -- get assignment derived jurisdiction geocode for state,county,city,zip code
2261 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
2262 p_state_abbrev => p_state_abbrev,
2263 p_county_name => p_county_name,
2264 p_city_name => p_city_name,
2265 p_zip_code => p_zip_code );
2266
2267 /** sbilling **/
2268 /*
2269 ** get limits for tax, should fire once, copy variables INTO globals
2270 */
2271 -- IF (g_futa_wage_limit = 0) THEN
2272 fetch_wage_limits(p_adjustment_date,
2273 p_state_abbrev,
2274 g_futa_wage_limit,
2275 g_ss_ee_wage_limit, g_ss_er_wage_limit,
2276 g_sdi_ee_wage_limit, g_sdi_er_wage_limit,
2277 g_sui_ee_wage_limit, g_sui_er_wage_limit);
2278
2279 -- END IF;
2280
2281 -- get tax self adjust menthod for taxes FUTA, SS, MEDICARE, SUI, SDI--
2282 Open c_get_futa_self_adjust_method;
2283 fetch c_get_futa_self_adjust_method
2284 into g_futa_sa_method;
2285 if c_get_futa_self_adjust_method%NOTFOUND THEN
2286 g_futa_sa_method := 'Not Entered';
2287 end if;
2288 close c_get_futa_self_adjust_method;
2289
2290 Open c_get_ss_self_adjust_method;
2291 fetch c_get_ss_self_adjust_method
2292 into g_ss_sa_method;
2293 if c_get_ss_self_adjust_method%NOTFOUND THEN
2294 g_ss_sa_method := 'Not Entered';
2295 end if;
2296 close c_get_ss_self_adjust_method;
2297
2298 Open c_get_medi_self_adjust_method;
2299 fetch c_get_medi_self_adjust_method
2300 into g_medicare_sa_method;
2301 if c_get_medi_self_adjust_method%NOTFOUND THEN
2302 g_medicare_sa_method := 'Not Entered';
2303 end if;
2304 close c_get_medi_self_adjust_method;
2305
2306 Open c_get_sdi_self_adjust_method;
2307 fetch c_get_sdi_self_adjust_method
2308 into g_sdi_sa_method;
2309 if c_get_sdi_self_adjust_method%NOTFOUND THEN
2310 g_sdi_sa_method := 'Not Entered';
2311 end if;
2312 close c_get_sdi_self_adjust_method;
2313
2314 Open c_get_sui_self_adjust_method;
2315 fetch c_get_sui_self_adjust_method
2316 into g_sui_sa_method;
2317 if c_get_sui_self_adjust_method%NOTFOUND THEN
2318 g_sui_sa_method := 'Not Entered';
2319 end if;
2320 close c_get_sui_self_adjust_method;
2321
2322 open c_get_tax_group;
2323 fetch c_get_tax_group
2324 into g_tax_group;
2325 if c_get_tax_group%NOTFOUND THEN
2326 g_tax_group := 'NOT_ENTERED';
2327 end if;
2328 close c_get_tax_group;
2329
2330 -- basic error checking
2331 -- 1. check that Gross = Net + Taxes
2332
2333 IF (l_gross_amount <> 0) THEN
2334 /*
2335 ** stub - do the ER components require validation,
2336 ** l_futa_er + l_sui_er + l_sdi_er + l_sch_dist_wh_ee
2337 */
2338 l_total_taxes_withheld := l_fit + l_ss + l_medicare + l_sit +
2339 l_sui_ee + l_sdi_ee + l_county + l_city +
2340 l_sch_dist_wh_ee;
2341
2342 IF (l_gross_amount <> l_net_amount + l_total_taxes_withheld) THEN
2343 hr_utility.set_message(801, 'PY_51134_TXADJ_TAX_NET_TOT');
2344 hr_utility.raise_error;
2345 END IF;
2346
2347 END IF;
2348
2349
2350 -- 2. check that if an earnings element is provided if Gross is non-zero
2351
2352 IF (l_gross_amount <> 0 AND p_earning_element_type IS NULL) THEN
2353 hr_utility.set_message(801, 'PY_51140_TXADJ_EARN_ELE_REQ');
2354 hr_utility.raise_error;
2355 END IF;
2356
2357
2358 -- 3. check that SIT = 0 for Alaska, Florida, Nevada, New Hampshire, South Dakota,
2359 -- Tennessee, Texas, Washington, Wyoming, and the Virgin Islands
2360
2361 IF ((l_sit <> 0) AND
2362 (tax_exists(l_jd_entered, 'SIT', p_adjustment_date) = 'N')) THEN
2363 hr_utility.set_message(801, 'PY_51141_TXADJ_SIT_EXEMPT');
2364 hr_utility.raise_error;
2365 END IF;
2366
2367 /* bug 1608907 */
2368 IF ((l_county <> 0) AND
2369 (tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date) = 'N')) THEN
2370 hr_utility.set_message(801, 'PY_50980_TXADJ_COUNTY_EXEMPT');
2371 hr_utility.raise_error;
2372 END IF;
2373
2374 IF ((l_city <> 0) AND
2375 (tax_exists(l_jd_entered, 'CITY', p_adjustment_date) = 'N')) THEN
2376 hr_utility.set_message(801, 'PY_50981_TAXADJ_CITY_EXEMPT');
2377 hr_utility.raise_error;
2378 END IF;
2379
2380 /* bug 1608907 */
2381
2382 -- 4. check that SDI = 0 for all states but California, Hawaii, New Jersey, New York,
2383 -- Puerto Rico, Rhode Island
2384 --
2385 -- first, need to ensure that the JD passed in is/was the primary assignment state at the
2386 -- time of the adjustment,
2387 -- this is because VERTEX calculations for SDI only occur for the primary work location,
2388 -- if the JD passed in is not the primary work location,
2389 -- then ensuing VERTEX calculations will not reflect the balance adjustments
2390
2391 IF ( l_sdi_ee <> 0 or l_sdi_er <> 0) THEN
2392 OPEN csr_sdi_check;
2393 FETCH csr_sdi_check INTO l_primary_asg_state;
2394
2395 IF csr_sdi_check%NOTFOUND THEN
2396 CLOSE csr_sdi_check;
2397 hr_utility.set_message(801, 'PY_51327_TXADJ_SDI_JD');
2398 hr_utility.raise_error;
2399 END IF;
2400
2401 CLOSE csr_sdi_check;
2402
2403 END IF;
2404
2405 IF ( l_sdi_ee <> 0) THEN
2406 --IF (p_state_abbrev NOT IN ('CA', 'HI', 'NJ', 'NY', 'RI')) THEN
2407 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date) = 'N') THEN
2408 hr_utility.set_message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
2409 hr_utility.raise_error;
2410 END IF;
2411
2412 END IF;
2413
2414 IF ( l_sdi_er <> 0) THEN
2415 --IF (p_state_abbrev NOT IN ('NJ', 'NY')) THEN
2416 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date) = 'N') THEN
2417 hr_utility.set_message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
2418 hr_utility.raise_error;
2419 END IF;
2420
2421 END IF;
2422
2423 -- 5. check SUI (EE) Withheld = 0 for all states unless the SUI state is
2424 -- in ('AK', 'NJ', 'PA')
2425
2426 OPEN csr_sui_geocode;
2427 FETCH csr_sui_geocode
2428 INTO g_sui_jd,
2429 g_sui_state_code,
2430 l_fed_tax_exempt,
2431 l_futa_tax_exempt,
2432 l_medicare_tax_exempt,
2433 l_ss_tax_exempt;
2434 CLOSE csr_sui_geocode;
2435
2436 OPEN csr_sit_exempt (cp_jurisdiction_code => l_jd_entered);
2437 FETCH csr_sit_exempt
2438 INTO l_sit_exempt,
2439 l_sdi_exempt,
2440 l_sui_exempt;
2441 IF csr_sit_exempt%NOTFOUND THEN
2442 l_sit_exempt := 'N';
2443 l_sdi_exempt := 'N';
2444 l_sui_exempt := 'N';
2445 END IF;
2446 CLOSE csr_sit_exempt;
2447
2448 OPEN csr_county_exempt (cp_jurisdiction_code => l_jd_entered);
2449 FETCH csr_county_exempt
2450 INTO l_cnt_exempt,
2451 l_cnt_sd_exempt;
2452 IF csr_county_exempt%NOTFOUND THEN
2453 l_cnt_exempt := 'N';
2454 l_cnt_sd_exempt := 'N';
2455 END IF;
2456 CLOSE csr_county_exempt;
2457
2458 OPEN csr_city_exempt (cp_jurisdiction_code => l_jd_entered);
2459 FETCH csr_city_exempt
2460 INTO l_cty_exempt,
2461 l_cty_sd_exempt;
2462 IF csr_city_exempt%NOTFOUND THEN
2463 l_cty_exempt := 'N';
2464 l_cty_sd_exempt := 'N';
2465 END IF;
2466 CLOSE csr_city_exempt;
2467
2468 IF (l_sui_ee <> 0) THEN
2469
2470 /*
2471 ** if the assignment is not in 'AK', 'NJ', 'PA' then SUI_EE does not apply,
2472 ** if the state found for the assignment (CA) <> the state from the
2473 ** assignment (NJ) then SUI_EE does not apply
2474 */
2475 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date) = 'N') OR
2476 (g_sui_state_code <> p_state_abbrev) THEN
2477 hr_utility.set_message(801, 'PY_51328_TXADJ_SUI_EXEMPT');
2478 hr_utility.raise_error;
2479 END IF;
2480
2481 END IF;
2482
2483 BEGIN
2484 hr_utility.set_location(c_proc, 10);
2485 SELECT consolidation_set_id
2486 INTO l_consolidation_set_id
2487 FROM PAY_CONSOLIDATION_SETS
2488 WHERE consolidation_set_name = p_consolidation_set
2489 and business_group_id = l_bg_id
2490 ;
2491 EXCEPTION
2492 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
2493 hr_utility.set_message(801, 'PY_51136_TXADJ_CONSET_NOT_FND');
2494 hr_utility.raise_error;
2495 END;
2496
2497 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
2498 p_state_abbrev => p_state_abbrev,
2499 p_county_name => p_county_name,
2500 p_city_name => p_city_name,
2501 p_zip_code => p_zip_code );
2502
2503 /** sbilling */
2504 /*
2505 ** put the old taxable balances (before any BA processing) INTO globals,
2506 ** required for subsequent excess processing
2507 */
2508 g_medicare_ee_taxable := taxable_balance('MEDICARE', 'EE', p_tax_unit_id, l_assignment_id,
2509 p_adjustment_date, NULL);
2510
2511 g_medicare_er_taxable := taxable_balance('MEDICARE', 'ER', p_tax_unit_id, l_assignment_id,
2512 p_adjustment_date, NULL);
2513
2514 g_futa_taxable := taxable_balance('FUTA', 'ER', p_tax_unit_id, l_assignment_id,
2515 p_adjustment_date, NULL);
2516
2517 g_ss_ee_taxable := taxable_balance('SS', 'EE', p_tax_unit_id, l_assignment_id,
2518 p_adjustment_date, NULL);
2519
2520 g_ss_er_taxable := taxable_balance('SS', 'ER', p_tax_unit_id, l_assignment_id,
2521 p_adjustment_date, NULL);
2522
2523 /*
2524 ** the SUI/SDI balances require a JD code to derive the balance for a
2525 ** particular state
2526 */
2527 g_sdi_ee_taxable := taxable_balance('SDI', 'EE', p_tax_unit_id, l_assignment_id,
2528 p_adjustment_date, l_jd_entered);
2529
2530 g_sdi_er_taxable := taxable_balance('SDI', 'ER', p_tax_unit_id, l_assignment_id,
2531 p_adjustment_date, l_jd_entered);
2532
2533 g_sui_ee_taxable := taxable_balance('SUI', 'EE', p_tax_unit_id, l_assignment_id,
2534 p_adjustment_date, l_jd_entered);
2535
2536 g_sui_er_taxable := taxable_balance('SUI', 'ER', p_tax_unit_id, l_assignment_id,
2537 p_adjustment_date, l_jd_entered);
2538
2539
2540 -- set global
2541 g_city_jd := l_jd_entered;
2542 g_state_jd := Substr(l_jd_entered, 1, 2) || '-000-0000';
2543 g_county_jd := Substr(l_jd_entered, 1, 6) || '-0000';
2544 g_sch_dist_jur := l_sch_dist_jur;
2545 g_classification_id := NULL;
2546 g_earnings_category := NULL;
2547 g_classification := NULL;
2548
2549
2550 -- more error checking
2551
2552 -- check the level of l_jd_entered to see if all taxes entered
2553 -- are applicable for the jurisdiction entered
2554 hr_utility.set_location(c_proc, 15);
2555
2556 IF (l_city <> 0) THEN -- jd level needed is for a city --Bug3697701 --Removed the condition
2557 l_jd_level_needed := 4; --OR l_gross_amount <> 0 from IF stmt.
2558
2559 ELSIF (l_county <> 0) THEN
2560 l_jd_level_needed := 3;
2561
2562 ELSIF (l_sit <> 0 OR l_sui_ee <> 0 OR l_sdi_ee <> 0) THEN
2563 l_jd_level_needed := 2;
2564
2565 ELSIF (l_fit <> 0 OR l_ss <> 0 OR l_medicare <> 0) THEN
2566 l_jd_level_needed := 1;
2567
2568 END IF;
2569
2570
2571 IF (l_jd_entered = g_fed_jd) THEN
2572 l_jd_level_entered := 1;
2573
2574 ELSIF (l_jd_entered = g_state_jd) THEN
2575 l_jd_level_entered := 2;
2576
2577 ELSIF (l_jd_entered = g_county_jd) THEN
2578 l_jd_level_entered := 3;
2579
2580 ELSE -- jd level entered is for a city
2581 l_jd_level_entered := 4;
2582
2583 END IF;
2584
2585
2586 -- now compare the level of jd entered against the level required
2587 IF (l_jd_level_needed > l_jd_level_entered) THEN
2588 hr_utility.set_location(c_proc, 20);
2589 hr_utility.set_message(801, 'PY_50015_TXADJ_JD_INSUFF');
2590 hr_utility.raise_error;
2591 END IF;
2592
2593
2594 -- main processing
2595 hr_utility.set_location(c_proc, 30);
2596
2597 -- first call routine to create payroll_action_id, we will only need
2598 -- one for entire tax balance adjustment process
2599 l_payroll_action_id := pay_bal_adjust.init_batch(p_payroll_id => l_payroll_id,
2600 p_batch_mode => 'NO_COMMIT',
2601 p_effective_date => p_adjustment_date,
2602 p_consolidation_set_id => l_consolidation_set_id,
2603 p_prepay_flag => p_balance_adj_prepay_flag);
2604
2605
2606 -- 4188782
2607 open get_element_details (p_earning_element_type,l_bg_id);
2608 fetch get_element_details into l_element_classification;
2609 close get_element_details;
2610
2611 IF (l_gross_amount <> 0)
2612 and (l_element_classification = 'Supplemental Earnings'
2613 or l_element_classification = 'Imputed Earnings') THEN
2614
2615 process_element(p_assignment_id => l_assignment_id,
2616 p_consolidation_set_id => l_consolidation_set_id,
2617 p_element_type => 'FSP_SUBJECT',
2618 p_abbrev_element_type => 'FSP',
2619 p_bg_id => l_bg_id,
2620 p_adjustment_date => p_adjustment_date,
2621 p_earn_amount => l_gross_amount,
2622 p_adj_amount => 0,
2623 p_jurisdiction => g_fed_jd,
2624 p_payroll_action_id => l_payroll_action_id,
2625 p_tax_unit_id => p_tax_unit_id,
2626 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2627
2628 END IF;
2629 ------------------------------
2630
2631 IF (l_gross_amount <> 0) THEN
2632 process_element(p_assignment_id => l_assignment_id,
2633 p_consolidation_set_id => l_consolidation_set_id,
2634 p_element_type => p_earning_element_type,
2635 p_abbrev_element_type => Substr(p_earning_element_type, 1, 11),
2636 p_bg_id => l_bg_id,
2637 p_adjustment_date => p_adjustment_date,
2638 p_earn_amount => l_gross_amount,
2639 p_adj_amount => l_gross_amount,
2640 p_jurisdiction => l_jd_entered,
2641 p_payroll_action_id => l_payroll_action_id,
2642 p_tax_unit_id => p_tax_unit_id,
2643 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2644 END IF;
2645
2646 IF (l_fit <> 0) THEN
2647 process_element(p_assignment_id => l_assignment_id,
2648 p_consolidation_set_id => l_consolidation_set_id,
2649 p_element_type => 'FIT',
2650 p_abbrev_element_type => 'FIT',
2651 p_bg_id => l_bg_id,
2652 p_adjustment_date => p_adjustment_date,
2653 p_earn_amount => l_gross_amount,
2654 p_adj_amount => l_fit,
2655 p_jurisdiction => g_fed_jd,
2656 p_payroll_action_id => l_payroll_action_id,
2657 p_tax_unit_id => p_tax_unit_id,
2658 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2659
2660 IF (l_fit_third = 'YES') THEN
2661 process_element(p_assignment_id => l_assignment_id,
2662 p_consolidation_set_id => l_consolidation_set_id,
2663 p_element_type => 'FIT 3rd Party',
2664 p_abbrev_element_type => '3F',
2665 p_bg_id => l_bg_id,
2666 p_adjustment_date => p_adjustment_date,
2667 p_earn_amount => l_gross_amount,
2668 p_adj_amount => l_fit,
2669 p_jurisdiction => g_fed_jd,
2670 p_payroll_action_id => l_payroll_action_id,
2671 p_tax_unit_id => p_tax_unit_id,
2672 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2673 END IF;
2674 END IF;
2675
2676 IF (l_ss <> 0) and (g_ss_sa_method <> 'Bypass Collection') THEN
2677 process_element(p_assignment_id => l_assignment_id,
2678 p_consolidation_set_id => l_consolidation_set_id,
2679 p_element_type => 'SS_EE',
2680 p_abbrev_element_type => 'SS',
2681 p_bg_id => l_bg_id,
2682 p_adjustment_date => p_adjustment_date,
2683 p_earn_amount => NULL,
2684 p_adj_amount => l_ss,
2685 p_jurisdiction => g_fed_jd,
2686 p_payroll_action_id => l_payroll_action_id,
2687 p_tax_unit_id => p_tax_unit_id,
2688 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2689
2690 process_element(p_assignment_id => l_assignment_id,
2691 p_consolidation_set_id => l_consolidation_set_id,
2692 p_element_type => 'SS_ER',
2693 p_abbrev_element_type => 'SER',
2694 p_bg_id => l_bg_id,
2695 p_adjustment_date => p_adjustment_date,
2696 p_earn_amount => NULL,
2697 p_adj_amount => l_ss,
2698 p_jurisdiction => g_fed_jd,
2699 p_payroll_action_id => l_payroll_action_id,
2700 p_tax_unit_id => p_tax_unit_id,
2701 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2702 END IF;
2703
2704 IF (l_medicare <> 0) and (g_medicare_sa_method <> 'Bypass Calculations')THEN
2705 process_element(p_assignment_id => l_assignment_id,
2706 p_consolidation_set_id => l_consolidation_set_id,
2707 p_element_type => 'Medicare_EE',
2708 p_abbrev_element_type => 'Med',
2709 p_bg_id => l_bg_id,
2710 p_adjustment_date => p_adjustment_date,
2711 p_earn_amount => 0,
2712 p_adj_amount => l_medicare,
2713 p_jurisdiction => g_fed_jd,
2714 p_payroll_action_id => l_payroll_action_id,
2715 p_tax_unit_id => p_tax_unit_id,
2716 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2717
2718 process_element(p_assignment_id => l_assignment_id,
2719 p_consolidation_set_id => l_consolidation_set_id,
2720 p_element_type => 'Medicare_ER',
2721 p_abbrev_element_type => 'MER',
2722 p_bg_id => l_bg_id,
2723 p_adjustment_date => p_adjustment_date,
2724 p_earn_amount => 0,
2725 p_adj_amount => l_medicare,
2726 p_jurisdiction => g_fed_jd,
2727 p_payroll_action_id => l_payroll_action_id,
2728 p_tax_unit_id => p_tax_unit_id,
2729 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2730 END IF;
2731
2732 IF (l_futa_er <> 0 and g_futa_sa_method <> 'Bypass Collection' ) THEN
2733 process_element(p_assignment_id => l_assignment_id,
2734 p_consolidation_set_id => l_consolidation_set_id,
2735 p_element_type => 'FUTA',
2736 p_abbrev_element_type => 'FTA',
2737 p_bg_id => l_bg_id,
2738 p_adjustment_date => p_adjustment_date,
2739 p_earn_amount => 0,
2740 p_adj_amount => l_futa_er,
2741 p_jurisdiction => g_fed_jd,
2742 p_payroll_action_id => l_payroll_action_id,
2743 p_tax_unit_id => p_tax_unit_id,
2744 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2745 END IF;
2746
2747 IF (l_sit <> 0) THEN
2748 process_element(p_assignment_id => l_assignment_id,
2749 p_consolidation_set_id => l_consolidation_set_id,
2750 p_element_type => 'SIT_WK',
2751 p_abbrev_element_type => 'SITK',
2752 p_bg_id => l_bg_id,
2753 p_adjustment_date => p_adjustment_date,
2754 p_earn_amount => l_gross_amount,
2755 p_adj_amount => l_sit,
2756 p_jurisdiction => g_state_jd,
2757 p_payroll_action_id => l_payroll_action_id,
2758 p_tax_unit_id => p_tax_unit_id,
2759 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2760 END IF;
2761
2762
2763 /** sbilling **/
2764 /*
2765 ** new tax element to be processed, use SIT_WK as a template
2766 */
2767 IF (l_sch_dist_wh_ee <> 0) THEN
2768
2769 process_element(p_assignment_id => l_assignment_id,
2770 p_consolidation_set_id => l_consolidation_set_id,
2771 p_element_type => 'County_SC_WK',
2772 p_abbrev_element_type => 'CsWK',
2773 p_bg_id => l_bg_id,
2774 p_adjustment_date => p_adjustment_date,
2775 p_earn_amount => l_gross_amount,
2776 p_adj_amount => l_sch_dist_wh_ee,
2777 p_jurisdiction => l_sch_dist_jur,
2778 p_payroll_action_id => l_payroll_action_id,
2779 p_tax_unit_id => p_tax_unit_id,
2780 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2781 END IF;
2782
2783
2784
2785 IF (l_city <> 0) THEN
2786 process_element(p_assignment_id => l_assignment_id,
2787 p_consolidation_set_id => l_consolidation_set_id,
2788 p_element_type => 'City_WK',
2789 p_abbrev_element_type => 'CtyK',
2790 p_bg_id => l_bg_id,
2791 p_adjustment_date => p_adjustment_date,
2792 p_earn_amount => l_gross_amount,
2793 p_adj_amount => l_city,
2794 p_jurisdiction => g_city_jd,
2795 p_payroll_action_id => l_payroll_action_id,
2796 p_tax_unit_id => p_tax_unit_id,
2797 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2798 END IF;
2799
2800 IF (l_county <> 0) THEN
2801 process_element(p_assignment_id => l_assignment_id,
2802 p_consolidation_set_id => l_consolidation_set_id,
2803 p_element_type => 'County_WK',
2804 p_abbrev_element_type => 'CntyK',
2805 p_bg_id => l_bg_id,
2806 p_adjustment_date => p_adjustment_date,
2807 p_earn_amount => l_gross_amount,
2808 p_adj_amount => l_county,
2809 p_jurisdiction => g_county_jd,
2810 p_payroll_action_id => l_payroll_action_id,
2811 p_tax_unit_id => p_tax_unit_id,
2812 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2813 END IF;
2814
2815 -- subject balances are adjusted if there were any earnings
2816 IF (l_gross_amount <> 0) THEN
2817 -- SD1
2818
2819 /*
2820 ** for Medicare_ER and SS_ER the ER adjustments amounts should equal the EE
2821 ** adjustment amounts, thus l_medicare and l_ss can be used
2822 */
2823 if g_medicare_sa_method <> 'Bypass Calculations'
2824 and l_medicare_tax_exempt <> 'Y' then
2825 process_element(p_assignment_id => l_assignment_id,
2826 p_consolidation_set_id => l_consolidation_set_id,
2827 p_element_type => 'Medicare_ER',
2828 p_abbrev_element_type => 'MER',
2829 p_bg_id => l_bg_id,
2830 p_adjustment_date => p_adjustment_date,
2831 p_earn_amount => l_gross_amount,
2832 p_adj_amount => 0,
2833 p_jurisdiction => g_fed_jd,
2834 p_payroll_action_id => l_payroll_action_id,
2835 p_tax_unit_id => p_tax_unit_id,
2836 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2837
2838 process_element(p_assignment_id => l_assignment_id,
2839 p_consolidation_set_id => l_consolidation_set_id,
2840 p_element_type => 'Medicare_EE',
2841 p_abbrev_element_type => 'Med',
2842 p_bg_id => l_bg_id,
2843 p_adjustment_date => p_adjustment_date,
2844 p_earn_amount => l_gross_amount,
2845 p_adj_amount => 0,
2846 p_jurisdiction => g_fed_jd,
2847 p_payroll_action_id => l_payroll_action_id,
2848 p_tax_unit_id => p_tax_unit_id,
2849 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2850
2851 end if;
2852
2853
2854 if g_ss_sa_method <> 'Bypass Collection'
2855 and l_ss_tax_exempt <> 'Y' then
2856
2857 process_element(p_assignment_id => l_assignment_id,
2858 p_consolidation_set_id => l_consolidation_set_id,
2859 p_element_type => 'SS_ER',
2860 p_abbrev_element_type => 'SER',
2861 p_bg_id => l_bg_id,
2862 p_adjustment_date => p_adjustment_date,
2863 p_earn_amount => l_gross_amount,
2864 p_adj_amount => 0,
2865 p_jurisdiction => g_fed_jd,
2866 p_payroll_action_id => l_payroll_action_id,
2867 p_tax_unit_id => p_tax_unit_id,
2868 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2869
2870 process_element(p_assignment_id => l_assignment_id,
2871 p_consolidation_set_id => l_consolidation_set_id,
2872 p_element_type => 'SS_EE',
2873 p_abbrev_element_type => 'SS',
2874 p_bg_id => l_bg_id,
2875 p_adjustment_date => p_adjustment_date,
2876 p_earn_amount => l_gross_amount,
2877 p_adj_amount => 0,
2878 p_jurisdiction => g_fed_jd,
2879 p_payroll_action_id => l_payroll_action_id,
2880 p_tax_unit_id => p_tax_unit_id,
2881 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2882
2883 end if;
2884
2885 if g_futa_sa_method <> 'Bypass Collection'
2886 and l_futa_tax_exempt <> 'Y' then
2887
2888 process_element(p_assignment_id => l_assignment_id,
2889 p_consolidation_set_id => l_consolidation_set_id,
2890 p_element_type => 'FUTA',
2891 p_abbrev_element_type => 'FTA',
2892 p_bg_id => l_bg_id,
2893 p_adjustment_date => p_adjustment_date,
2894 p_earn_amount => l_gross_amount,
2895 p_adj_amount => 0,
2896 p_jurisdiction => g_fed_jd,
2897 p_payroll_action_id => l_payroll_action_id,
2898 p_tax_unit_id => p_tax_unit_id,
2899 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2900
2901 end if;
2902
2903
2904 IF (tax_exists(l_jd_entered, 'SIT', p_adjustment_date) = 'Y') THEN
2905 hr_utility.trace('before process_element with SIT_SUBJECT_WK '||TO_CHAR(l_sit));
2906 process_element(p_assignment_id => l_assignment_id,
2907 p_consolidation_set_id => l_consolidation_set_id,
2908 p_element_type => 'SIT_SUBJECT_WK',
2909 p_abbrev_element_type => 'SITSubK',
2910 p_bg_id => l_bg_id,
2911 p_adjustment_date => p_adjustment_date,
2912 p_earn_amount => l_gross_amount,
2913 p_adj_amount => l_sit,
2914 p_jurisdiction => g_state_jd,
2915 p_payroll_action_id => l_payroll_action_id,
2916 p_tax_unit_id => p_tax_unit_id,
2917 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2918 END IF;
2919
2920 IF (tax_exists(l_jd_entered, 'CITY', p_adjustment_date) = 'Y') THEN
2921 process_element(p_assignment_id => l_assignment_id,
2922 p_consolidation_set_id => l_consolidation_set_id,
2923 p_element_type => 'City_SUBJECT_WK',
2924 p_abbrev_element_type => 'CtySubK',
2925 p_bg_id => l_bg_id,
2926 p_adjustment_date => p_adjustment_date,
2927 p_earn_amount => l_gross_amount,
2928 p_adj_amount => l_city,
2929 p_jurisdiction => g_city_jd,
2930 p_payroll_action_id => l_payroll_action_id,
2931 p_tax_unit_id => p_tax_unit_id,
2932 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2933
2934 END IF;
2935
2936 IF (tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date) = 'Y') THEN
2937
2938 process_element(p_assignment_id => l_assignment_id,
2939 p_consolidation_set_id => l_consolidation_set_id,
2940 p_element_type => 'County_SUBJECT_WK',
2941 p_abbrev_element_type => 'CntySubK',
2942 p_bg_id => l_bg_id,
2943 p_adjustment_date => p_adjustment_date,
2944 p_earn_amount => l_gross_amount,
2945 p_adj_amount => l_county,
2946 p_jurisdiction => g_county_jd,
2947 p_payroll_action_id => l_payroll_action_id,
2948 p_tax_unit_id => p_tax_unit_id,
2949 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2950
2951 END IF;
2952
2953 IF nvl(p_sch_dist_jur,NULL) is not NULL THEN
2954 process_element(p_assignment_id => l_assignment_id,
2955 p_consolidation_set_id => l_consolidation_set_id,
2956 p_element_type => 'School_SUBJECT_WK',
2957 p_abbrev_element_type => 'SchlSubK',
2958 p_bg_id => l_bg_id,
2959 p_adjustment_date => p_adjustment_date,
2960 p_earn_amount => l_gross_amount,
2961 p_adj_amount => 0,
2962 p_jurisdiction => l_sch_dist_jur,
2963 p_payroll_action_id => l_payroll_action_id,
2964 p_tax_unit_id => p_tax_unit_id,
2965 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
2966
2967 END IF;
2968
2969 END IF; -- (l_gross_amount <> 0)
2970
2971 -- only Alaska, New Jersey and Pennsylvania have SUI_EE in addition
2972 -- to SUI_ER,
2973 -- may also want to check that if the jurisdiction is the SUI jurisdiction,
2974 -- only then create the SUI SUBJECT EE and ER
2975
2976 -- sd 15/5
2977 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date) = 'Y') THEN
2978 IF (p_state_abbrev = g_sui_state_code) THEN
2979
2980 IF (l_gross_amount <> 0) THEN
2981
2982 process_element(p_assignment_id => l_assignment_id,
2983 p_consolidation_set_id => l_consolidation_set_id,
2984 p_element_type => 'SUI_SUBJECT_EE',
2985 p_abbrev_element_type => 'SUISubE',
2986 p_bg_id => l_bg_id,
2987 p_adjustment_date => p_adjustment_date,
2988 p_earn_amount => l_gross_amount,
2989 p_adj_amount => l_sui_ee,
2990 p_jurisdiction => g_sui_jd,
2991 p_payroll_action_id => l_payroll_action_id,
2992 p_tax_unit_id => p_tax_unit_id,
2993 p_balance_adj_costing_flag
2994 => p_balance_adj_costing_flag);
2995
2996 IF l_sui_exempt <> 'Y'
2997 and g_sui_sa_method <> 'Bypass Collection' THEN
2998 process_element(p_assignment_id => l_assignment_id,
2999 p_consolidation_set_id => l_consolidation_set_id,
3000 p_element_type => 'SUI_EE',
3001 p_abbrev_element_type => 'SUIE',
3002 p_bg_id => l_bg_id,
3003 p_adjustment_date => p_adjustment_date,
3004 p_earn_amount => l_gross_amount,
3005 p_adj_amount => 0,
3006 p_jurisdiction => g_sui_jd,
3007 p_payroll_action_id => l_payroll_action_id,
3008 p_tax_unit_id => p_tax_unit_id,
3009 p_balance_adj_costing_flag
3010 => p_balance_adj_costing_flag);
3011 END IF; /* l_sui_exempt */
3012
3013 END IF; /* l_gross_amount */
3014 IF ( l_sui_ee <> 0
3015 and g_sui_sa_method <> 'Bypass Collection') THEN
3016
3017 process_element(p_assignment_id => l_assignment_id,
3018 p_consolidation_set_id => l_consolidation_set_id,
3019 p_element_type => 'SUI_EE',
3020 p_abbrev_element_type => 'SUIE',
3021 p_bg_id => l_bg_id,
3022 p_adjustment_date => p_adjustment_date,
3023 p_earn_amount => 0,
3024 p_adj_amount => l_sui_ee,
3025 p_jurisdiction => g_sui_jd,
3026 p_payroll_action_id => l_payroll_action_id,
3027 p_tax_unit_id => p_tax_unit_id,
3028 p_balance_adj_costing_flag
3029 => p_balance_adj_costing_flag);
3030 END IF; /* l_sui_ee */
3031 END IF; /* state_abbrev */
3032 END IF; /* tax exists */
3033
3034 -- all states have SUI_ER
3035 IF (p_state_abbrev = g_sui_state_code) THEN
3036 IF (l_gross_amount <> 0) THEN
3037
3038 process_element(p_assignment_id => l_assignment_id,
3039 p_consolidation_set_id => l_consolidation_set_id,
3040 p_element_type => 'SUI_SUBJECT_ER',
3041 p_abbrev_element_type => 'SUISubR',
3042 p_bg_id => l_bg_id,
3043 p_adjustment_date => p_adjustment_date,
3044 p_earn_amount => l_gross_amount,
3045 p_adj_amount => l_sui_ee,
3046 p_jurisdiction => g_sui_jd,
3047 p_payroll_action_id => l_payroll_action_id,
3048 p_tax_unit_id => p_tax_unit_id,
3049 p_balance_adj_costing_flag
3050 => p_balance_adj_costing_flag);
3051
3052 IF l_sui_exempt <> 'Y'
3053 and g_sui_sa_method <> 'Bypass Collection' THEN
3054 process_element(p_assignment_id => l_assignment_id,
3055 p_consolidation_set_id => l_consolidation_set_id,
3056 p_element_type => 'SUI_ER',
3057 p_abbrev_element_type => 'SUIR',
3058 p_bg_id => l_bg_id,
3059 p_adjustment_date => p_adjustment_date,
3060 p_earn_amount => l_gross_amount,
3061 p_adj_amount => 0,
3062 p_jurisdiction => g_sui_jd,
3063 p_payroll_action_id => l_payroll_action_id,
3064 p_tax_unit_id => p_tax_unit_id,
3065 p_balance_adj_costing_flag
3066 => p_balance_adj_costing_flag);
3067 END IF; /* l_sui_exempt */
3068 END IF; /* l_gross_amount */
3069
3070 IF ( l_sui_er <> 0
3071 and g_sui_sa_method <> 'Bypass Collection') THEN
3072 process_element(p_assignment_id => l_assignment_id,
3073 p_consolidation_set_id => l_consolidation_set_id,
3074 p_element_type => 'SUI_ER',
3075 p_abbrev_element_type => 'SUIR',
3076 p_bg_id => l_bg_id,
3077 p_adjustment_date => p_adjustment_date,
3078 p_earn_amount => 0,
3079 /** sbilling **/
3080 p_adj_amount => l_sui_er,
3081 p_jurisdiction => g_sui_jd,
3082 p_payroll_action_id => l_payroll_action_id,
3083 p_tax_unit_id => p_tax_unit_id,
3084 p_balance_adj_costing_flag
3085 => p_balance_adj_costing_flag);
3086 END IF; /* l_sui_er */
3087 END IF; /* state_abrev */
3088
3089 -- only Hawaii, New Jersey, Puerto Rico have SDI_ER
3090 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date) = 'Y') THEN
3091
3092 IF (l_gross_amount <> 0) THEN
3093
3094 process_element(p_assignment_id => l_assignment_id,
3095 p_consolidation_set_id => l_consolidation_set_id,
3096 p_element_type => 'SDI_SUBJECT_ER',
3097 p_abbrev_element_type => 'SDISubR',
3098 p_bg_id => l_bg_id,
3099 p_adjustment_date => p_adjustment_date,
3100 p_earn_amount => l_gross_amount,
3101 p_adj_amount => l_sdi_ee,
3102 p_jurisdiction => g_state_jd,
3103 p_payroll_action_id => l_payroll_action_id,
3104 p_tax_unit_id => p_tax_unit_id,
3105 p_balance_adj_costing_flag
3106 => p_balance_adj_costing_flag);
3107
3108 IF l_sdi_exempt <> 'Y'
3109 and g_sdi_sa_method <> 'Bypass Collection' THEN
3110
3111 process_element(p_assignment_id => l_assignment_id,
3112 p_consolidation_set_id => l_consolidation_set_id,
3113 p_element_type => 'SDI_ER',
3114 p_abbrev_element_type => 'SDIR',
3115 p_bg_id => l_bg_id,
3116 p_adjustment_date => p_adjustment_date,
3117 p_earn_amount => l_gross_amount,
3118 p_adj_amount => 0,
3119 p_jurisdiction => g_state_jd,
3120 p_payroll_action_id => l_payroll_action_id,
3121 p_tax_unit_id => p_tax_unit_id,
3122 p_balance_adj_costing_flag
3123 => p_balance_adj_costing_flag);
3124 END IF; /* if l_sdi_exempt */
3125
3126 END IF;
3127
3128 IF ( l_sdi_er <> 0
3129 and g_sdi_sa_method <> 'Bypass Collection') THEN
3130
3131 process_element(p_assignment_id => l_assignment_id,
3132 p_consolidation_set_id => l_consolidation_set_id,
3133 p_element_type => 'SDI_ER',
3134 p_abbrev_element_type => 'SDIR',
3135 p_bg_id => l_bg_id,
3136 p_adjustment_date => p_adjustment_date,
3137 p_earn_amount => 0,
3138 p_adj_amount => l_sdi_er,
3139 p_jurisdiction => g_state_jd,
3140 p_payroll_action_id => l_payroll_action_id,
3141 p_tax_unit_id => p_tax_unit_id,
3142 p_balance_adj_costing_flag
3143 => p_balance_adj_costing_flag);
3144 END IF; /* if l_sdi_er */
3145
3146 END IF; /* if tax exists */
3147
3148 -- only California, Hawaii, New Jersey, New York, Rhode Island,
3149 -- and Puerto Rico have SDI_EE
3150
3151 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date) = 'Y') THEN
3152
3153 IF (l_gross_amount <> 0) THEN
3154 process_element(p_assignment_id => l_assignment_id,
3155 p_consolidation_set_id => l_consolidation_set_id,
3156 p_element_type => 'SDI_SUBJECT_EE',
3157 p_abbrev_element_type => 'SDISubE',
3158 p_bg_id => l_bg_id,
3159 p_adjustment_date => p_adjustment_date,
3160 p_earn_amount => l_gross_amount,
3161 p_adj_amount => l_sdi_ee,
3162 p_jurisdiction => g_state_jd,
3163 p_payroll_action_id => l_payroll_action_id,
3164 p_tax_unit_id => p_tax_unit_id,
3165 p_balance_adj_costing_flag
3166 => p_balance_adj_costing_flag);
3167
3168 IF l_sdi_exempt <> 'Y'
3169 AND g_sdi_sa_method <> 'Bypass Collection' THEN
3170
3171 process_element(p_assignment_id => l_assignment_id,
3172 p_consolidation_set_id => l_consolidation_set_id,
3173 p_element_type => 'SDI_EE',
3174 p_abbrev_element_type => 'SDIE',
3175 p_bg_id => l_bg_id,
3176 p_adjustment_date => p_adjustment_date,
3177 p_earn_amount => l_gross_amount,
3178 p_adj_amount => 0,
3179 p_jurisdiction => g_state_jd,
3180 p_payroll_action_id => l_payroll_action_id,
3181 p_tax_unit_id => p_tax_unit_id,
3182 p_balance_adj_costing_flag
3183 => p_balance_adj_costing_flag);
3184
3185 END IF; /* l_sdi_exempt */
3186
3187 END IF; /* l_gross-amount */
3188
3189 IF ( l_sdi_ee <> 0
3190 and g_sdi_sa_method <> 'Bypass Collection') THEN
3191 process_element(p_assignment_id => l_assignment_id,
3192 p_consolidation_set_id => l_consolidation_set_id,
3193 p_element_type => 'SDI_EE',
3194 p_abbrev_element_type => 'SDIE',
3195 p_bg_id => l_bg_id,
3196 p_adjustment_date => p_adjustment_date,
3197 p_earn_amount => 0,
3198 p_adj_amount => l_sdi_ee,
3199 p_jurisdiction => g_state_jd,
3200 p_payroll_action_id => l_payroll_action_id,
3201 p_tax_unit_id => p_tax_unit_id,
3202 p_balance_adj_costing_flag
3203 => p_balance_adj_costing_flag);
3204 END IF;
3205
3206 END IF; /* if tax exists */
3207
3208 -- set some of the return out parameters
3209 p_payroll_action_id := l_payroll_action_id;
3210
3211 IF hr_utility.check_warning THEN
3212 l_create_warning := TRUE;
3213 hr_utility.clear_warning;
3214 END IF;
3215
3216 IF(p_validate) THEN
3217 RAISE hr_api.validate_enabled;
3218 END IF;
3219
3220 hr_utility.trace('Finished Routine, all adjustments commited');
3221 hr_utility.trace('Payroll_action_id = '||TO_CHAR(l_payroll_action_id));
3222
3223 pay_bal_adjust.process_batch(p_payroll_action_id);
3224
3225
3226 EXCEPTION
3227 WHEN hr_api.validate_enabled THEN
3228 --
3229 -- As the Validate_Enabled exception has been raised
3230 -- we must rollback to the savepoint
3231 --
3232 ROLLBACK TO create_tax_bal_adjustment;
3233 --
3234 -- Only set output warning arguments
3235 -- (Any key or derived arguments must be set to NULL
3236 -- when validation only mode is being used.)
3237 --
3238 p_payroll_action_id := NULL;
3239 p_create_warning := l_create_warning;
3240 hr_utility.trace('Validate Enabled, no commits are made');
3241
3242 WHEN OTHERS THEN
3243 -- Unexpected error detected.
3244 ROLLBACK TO create_tax_bal_adjustment;
3245 RAISE;
3246
3247 END create_tax_balance_adjustment;
3248
3249 END pay_us_tax_bals_adj_api;
[Home] [Help]