[Home] [Help]
PACKAGE BODY: APPS.PAY_US_TAX_BALS_ADJ_PKG
Source
1 PACKAGE BODY pay_us_tax_bals_adj_pkg AS
2 /* $Header: pyustxba.pkb 120.1 2005/10/05 03:57:22 sackumar noship $ */
3 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
4 /* --------------------------------------------------------------------------
5 NAME
6 pyustxba.pkb
7 DESCRIPTION
8 This package is used to create tax balance adjustments for the US
9 localization of Oracle Payroll.
10 NOTES
11
12 The balances affected depend on whether Gross Amount has been entered
13 or not:
14
15 Gross Taxes Subject Balances Withheld Balances
16 ----- ----- ---------------- -----------------
17 NULL <> 0 N/A Yes
18 <>0 NULL Yes N/A
19 <>0 <> 0 Yes Yes.
20
21 Subject balances are adjusted depending on the taxability rules
22 for the work state.
23
24 ----------------------------------------------------------------------------
25 Version Modified Date Description
26 ------- -------- -------- ------------------------------
27 0 S Panwar 23-OCT-1995 Created
28 40.0 S Desai 17-Nov-1995 Initial arcs version.
29 40.1 S Desai 20-Nov-1995 Use various user keys as
30 parameters rather than the
31 SYSTEM.keys.
32 40.2 S Desai 22-Nov-1995 derive_jd_geocode added.
33 populate Supp Tax input
34 for FIT and SIT if earnings
35 is Supplemental.
36 40.3 S Desai 29-Nov-1995 Use input value's default
37 value if available.
38 Defined messages used instead
39 of generic one. Check location
40 provided - i.e. it is a valid
41 work location.
42 40.4 S Desai 08-Dec-1995 Check that the state is subject
43 to SIT/SUI_EE/SUI_ER/SDI_EE/SDI_ER.
44 40.5 S Desai 11-Jan-1996 Removed extraneous underscore in
45 message name. Bug 327502: SDI_ER and EE
46 for HI, NJ, PR; SDI_EE for CA, NY, RI
47 40.6 S Desai 23-May-1996 Bug 331022: Taxable wages needed to be
48 adjusted.
49 Also changed:
50 - SUI taxes are adjusted in the SUI
51 jurisdiction, regardless of the the
52 jurisdiction passed.
53 - SDI taxes can only be withheld in the
54 primary work location.
55 These changes were necessary because
56 subsequent in payroll runs, VERTEX calcs.
57 are only in SUI jd for SUI taxes AND
58 primary work jd for SDI taxes.
59 40.7 gpaytonm 01-JUL-96 Uncommented EXIT !!!!
60 40.8 ramurthy 10-SEP-96 Added code to adjust the FIT
61 Withheld by Third Party
62 balance, in addition to
63 feeding the FIT Withheld
64 balance, if the p_FIT_THIRD
65 flag is set.
66 40.9 ramurthy 02-OCT-96 Fixed bug 405844. Removed
67 "or l_gross_amount <> 0" in
68 steps 4 and 5 of procedure
69 create_tax_balance_adjustment.
70
71 40.15 ramurthy 14-OCT-96 Handled FIT 3rd Party
72 different from FIT.
73
74 40.16 ramurthy 14-OCT-96 Major changes.
75
76 40.17 ramurthy Removed trace info.
77
78 40.18 lwthomps 27-MAY-97 Arcsed in the wrong file.
79
80 40.19 lwthomps 27-MAY-97 Arcsed in Version 40.17
81 to fix the above mistake.
82
83 40.20/110.1 lwthomps 27-AUG-97 W4 Datetrack. Changed all
84 references to tax records
85 to use new datatracked table.
86
87
88 110.2 sbilling 28-Apr-98 Added extra parameter p_cost to:
89 - create_tax_balance_adjustment(),
90 - process_element(),
91 - create_adjustment()
92 p_cost is used to pass the cost checkbox
93 value to create_adjustment() so that
94 pay_element_entries_f can be updated
95 after the insert_element_entry() api
96
97 110.3 sbilling 15-Jul-98 Major changes. Added new function
98 process_limits() to do limit
99 processing on limit based taxes
100 (eg. Medicare_EE/Medicare_ER). The limits
101 for taxes are fetched from the tables
102 PAY_US_FEDERAL_TAX_INFO_F/
103 PAY_US_STATE_TAX_INFO_F for federal
104 and state taxes respectively. Also
105 added the extra fields:
106 - futa_er
107 - sui_er
108 - sdi_er
109 - sch_dist_wh_ee
110 - sch_dist_jur
111 to the corresponding form PAYWSTBA.
112 These are used to handle the ER components
113 of the adjustments and to allow school
114 district adjustments to be made. NB. The
115 chosen school district's jurisdiction is
116 passed down to
117 create_tax_balance_adjustment() via the
118 p_sch_dist_jur parameter.
119 Also note, the taxable balances for all
120 taxes where limit processing may apply are
121 fetched before any limit processing is
122 done. The values are stored in global
123 parameters.
124 08-apr99 djoshi Verfied and converted for Canonical
125 Complience of Date
126 19-apr99 alogue Fix to previous change.
127
128 115.1 21-apr-99 scgrant Multi-radix changes.
129 115.7 07-JUL-99 RAMURTHY Incorporated functional fixes
130 from 10.7.
131 115.8 19-AUG-99 KKAWOL Support for date UOM 'D'. 'D_DDMONYY','D_DDMONYYYY'
132 'D_DDMMYY','D_DDMMYYYY','D_MMDDYY','D_MMDDYYYY' do
133 not exist any more.
134 115.9 22-NOV-1999 MHANDA Added fed_information_category = 401K
135 in the where clause for cursor for
136 pay_us_federal_tax_info_f.
137 115.10 27-DEC-1999 tclewis modifed csr_chk_taxability to accept
138 jurisdiction code as a parameter. This
139 fixes a problem with checkin the taxability
140 rules for city and county records.
141 I also added code the check state level
142 taxablility rules if no rows are returned
143 on the city or county level.
144 115.11 15-feb-2000 tclewis bugs 983727 and 1151395. Modified csr_sui_geocode
145 to check business_group_id on assignment record
146 so that only one row is returned (in the case of
147 multiple business groups). Removed check of
148 gross_pay <> 0 when validating jurisdiction level
149 needed. Added a check for a valid city jurisdiction
150 code before making an adjustment to the
151 city_subject_wk balance.
152 112.12 24-MAY-2000 tclewis Implemented the tax_exists functionality for city
153 and county. Also added a check for tax_exists
154 before processing elements city_subject_wk and
155 count_subbject_wk, when gross pay is greated than
156 0.
157
158 115.16 13-sep-2000 irgonzal Bug Fix 1398865. Modified csr_sdi_check cursor
159 to check business_group_id on assignment record
160 to ensure only one row is returned when same
161 assignment number exist in different business groups.
162
163 115.17 11-jan-2001 tclewis bug fix 1569312. SUI and SDI taxable were only
164 being adjusted when an adjustment abount was
165 entered for SUI / SDI liablity. I removed the
166 code (if statements) where we check if l_sui_er /
167 l_sdi_er (or ee) were eneterd before we process
168 the adjustment.
169 115.18 05-OCT-2005 sackumar 4650486 Removed GSCC Errors and Warnings
170
171 -------------------------------------------------------------------------- */
172
173 -- global variables
174 g_classification VARCHAR2(80);
175 g_earnings_category VARCHAR2(30);
176 g_classification_id NUMBER;
177 g_fed_jd VARCHAR2(11) := '00-000-0000';
178 g_state_jd VARCHAR2(11) := '00-000-0000';
179 g_sui_jd VARCHAR2(11) := '00-000-0000';
180 g_sui_state_code VARCHAR2(2);
181 g_county_jd VARCHAR2(11) := '00-000-0000';
182 g_city_jd VARCHAR2(11) := '00-000-0000';
183 g_dummy_varchar_tbl hr_entry.varchar2_table;
184 g_dummy_number_tbl hr_entry.number_table;
185 g_tax_type_tbl hr_entry.varchar2_table;
186 g_tax_adj_pactid_tbl hr_entry.number_table;
187 g_pact_cntr NUMBER := 1;
188
189 /* federal level 'balances' */
190 g_medicare_ee_taxable NUMBER := 0;
191 g_medicare_er_taxable NUMBER := 0;
192 g_futa_taxable NUMBER := 0;
193 g_ss_ee_taxable NUMBER := 0;
194 g_ss_er_taxable NUMBER := 0;
195
196 /* state level 'balances' */
197 g_sdi_ee_taxable NUMBER := 0;
198 g_sdi_er_taxable NUMBER := 0;
199 g_sui_ee_taxable NUMBER := 0;
200 g_sui_er_taxable NUMBER := 0;
201
202 /* federal level 'limits' */
203 g_futa_wage_limit NUMBER := 0;
204 g_ss_ee_wage_limit NUMBER := 0;
205 g_ss_er_wage_limit NUMBER := 0;
206
207 /* state level 'limits' */
208 g_sdi_ee_wage_limit NUMBER := 0;
209 g_sdi_er_wage_limit NUMBER := 0;
210 g_sui_ee_wage_limit NUMBER := 0;
211 g_sui_er_wage_limit NUMBER := 0;
212
213
214
215 PROCEDURE create_adjustment(
216 p_adjustmnt_date IN DATE,
217 p_assignment_id IN NUMBER,
218 p_element_link_id IN NUMBER,
219 p_consolidation_set_id IN NUMBER,
220 p_num_entry_values IN OUT nocopy NUMBER,
221 p_entry_value_tbl IN OUT nocopy hr_entry.varchar2_table,
222 p_input_value_id_tbl IN OUT nocopy hr_entry.number_table,
223 p_original_entry_id IN NUMBER,
224 p_payroll_action_id IN OUT nocopy NUMBER,
225 p_cost IN VARCHAR2
226 ) IS
227
228 c_proc VARCHAR2(100) := 'pay_us_tax_bals_adj_pkg.create_adjustment';
229
230 -- variables used during the creation of a balance adjustment
231 l_adjustment_date DATE;
232 l_dummy_date DATE;
233 l_dummy_number NUMBER;
234 l_element_entry_id NUMBER;
235
236 BEGIN
237
238 Hr_Utility.Trace('Entering '|| c_proc);
239
240 -- set up adjustment date
241 l_adjustment_date := p_adjustmnt_date;
242
243 -- create balance adjustment element entry
244 hr_entry_api.insert_element_entry(
245 p_effective_start_date => l_adjustment_date,
246 p_effective_end_date => l_dummy_date,
247 p_element_entry_id => l_element_entry_id,
248 p_assignment_id => p_assignment_id,
249 p_element_link_id => p_element_link_id,
250 p_creator_type => 'B', -- (B)alance Adjustment
251 p_entry_type => 'B', -- (B)alance Adjustment
252 p_num_entry_values => p_num_entry_values,
253 p_input_value_id_tbl => p_input_value_id_tbl,
254 p_entry_value_tbl => p_entry_value_tbl );
255
256
257 UPDATE PAY_ELEMENT_ENTRIES_F
258 SET balance_adj_cost_flag = p_cost
259 WHERE element_entry_id = l_element_entry_id
260 and effective_start_date = l_adjustment_date
261 and effective_end_date = l_dummy_date
262 ;
263
264
265 -- reset the adjustment date
266 -- NB. the elemnt entry API sets the adjustment
267 -- date to be the first day of the period in which the adjustment was made
268 l_adjustment_date := p_adjustmnt_date;
269
270
271 -- apply the balance adjustment ie. create payroll action, create assignment
272 -- action and resequence it as necessary
273 hrassact.bal_adjust_actions(
274 consetid => p_consolidation_set_id,
275 eentryid => l_element_entry_id,
276 effdate => l_adjustment_date,
277 act_type => 'B',
278 pyactid => p_payroll_action_id,
279 asactid => l_dummy_number);
280
281 IF (p_original_entry_id IS NOT NULL) THEN
282 UPDATE PAY_RUN_RESULTS
283 SET source_id = p_original_entry_id
284 WHERE source_id = l_element_entry_id
285 and source_type = 'E';
286 END IF;
287
288 Hr_Utility.Trace('Leaving pay_us_tax_bals_adj_pkg.create_adjustment');
289
290 END create_adjustment;
291
292
293
294 PROCEDURE private_trace(
295 p_procedure_name IN VARCHAR2,
296 p_msg_txt IN VARCHAR2) IS
297
298 BEGIN
299
300 Hr_Utility.Trace('|' || p_procedure_name || '() : ' || p_msg_txt);
301
302 END private_trace;
303
304
305
306 PROCEDURE process_input(
307 p_element_type IN VARCHAR2,
308 p_element_type_id NUMBER,
309 p_iv_tbl IN OUT nocopy hr_entry.number_table,
310 p_iv_names_tbl IN OUT nocopy hr_entry.varchar2_table,
311 p_ev_tbl IN OUT nocopy hr_entry.varchar2_table,
312 p_bg_id NUMBER,
313 p_adj_date DATE,
314 p_input_name VARCHAR2,
315 p_entry_value VARCHAR2,
316 p_row IN OUT nocopy NUMBER) IS
317
318 CURSOR csr_inputs(v_element_type_id NUMBER,
319 v_input_name VARCHAR2) IS
320 SELECT i.input_value_id
321 FROM PAY_INPUT_VALUES_F i
322 WHERE i.element_type_id = v_element_type_id
323 and (i.business_group_id = p_bg_id
324 or i.business_group_id IS NULL
325 )
326 and i.name = v_input_name
327 and p_adj_date BETWEEN
328 i.effective_start_date AND i.effective_end_date
329 ;
330
331 CURSOR csr_chk_taxability(v_tax_type VARCHAR2,
332 v_jurisdiction_code VARCHAR2) IS
333 SELECT 'Y'
334 FROM PAY_TAXABILITY_RULES
335 WHERE jurisdiction_code = v_jurisdiction_code
336 and tax_category = g_earnings_category
337 and tax_type = v_tax_type
338 and classification_id = g_classification_id
339 ;
340
341 CURSOR csr_chk_fed_taxability(v_tax_type VARCHAR2) IS
342 SELECT 'Y'
343 FROM PAY_TAXABILITY_RULES
344 WHERE jurisdiction_code = g_fed_jd
345 and tax_category = g_earnings_category
346 and tax_type = v_tax_type
347 and classification_id = g_classification_id
348 ;
349
350 l_input_value_id NUMBER;
351 l_taxable VARCHAR2(1) := 'N';
352 c_proc VARCHAR2(100) := 'pay_us_tax_bals_adj_pkg.process_input';
353 l_jurisdiction_code VARCHAR2(11);
354
355 BEGIN
356 Hr_Utility.Set_Location(c_proc, 10);
357
358 OPEN csr_inputs (p_element_type_id, p_input_name);
359 FETCH csr_inputs INTO l_input_value_id;
360 CLOSE csr_inputs;
361
362 IF (l_input_value_id IS NULL) THEN
363 Hr_Utility.Set_Location(c_proc, 20);
364 Hr_Utility.Trace('input_value_id not found for ' ||
365 p_input_name ||
366 ' for ele_type_id ' ||
367 To_Char(p_element_type_id));
368 Hr_Utility.Set_Message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
369 Hr_Utility.Raise_Error;
370 END IF;
371
372 -- check taxability of the tax balance element
373 Hr_Utility.Set_Location(c_proc, 30);
374
375 IF (g_classification IN ('Imputed Earnings', 'Supplemental Earnings')) THEN
376
377 /** sbilling **/
378 /*
379 ** no RRVs were being generated for Medicare_EE's TAXABLE EV as p_element_type
380 ** (Medicare_EE) didn't satisfy any if conditions in the inner block,
381 ** l_taxable was not set to Y,
382 ** therefore the table structure was not populated,
383 ** at a later stage Medicare_EE's TAXABLE EV would be defaulted to 0,
384 ** causing the taxable amount to appear in Excess,
385 */
386 IF (p_input_name = 'Subj Whable' OR p_input_name = 'TAXABLE') THEN
387
388 Hr_Utility.Set_Location(c_proc, 40);
389
390 IF (p_element_type IN ('SUI_EE', 'SUI_SUBJECT_EE',
391 'SUI_ER', 'SUI_SUBJECT_ER')) THEN
392 Hr_Utility.Set_Location(c_proc, 41);
393 OPEN csr_chk_taxability ('SUI', g_state_jd );
394 FETCH csr_chk_taxability INTO l_taxable;
395 CLOSE csr_chk_taxability;
396
397 ELSIF (p_element_type IN ('Medicare_EE', 'Medicare_ER')) THEN
398 Hr_Utility.Set_Location(c_proc, 42);
399 OPEN csr_chk_fed_taxability ('MEDICARE');
400 FETCH csr_chk_fed_taxability INTO l_taxable;
401 CLOSE csr_chk_fed_taxability;
402
403 ELSIF (p_element_type IN ('SS_EE', 'SS_ER')) THEN
404 Hr_Utility.Set_Location(c_proc, 43);
405 OPEN csr_chk_fed_taxability ('SS');
406 FETCH csr_chk_fed_taxability INTO l_taxable;
407 CLOSE csr_chk_fed_taxability;
408
409 ELSIF (p_element_type IN ('FUTA')) THEN
410 Hr_Utility.Set_Location(c_proc, 43);
411 OPEN csr_chk_fed_taxability ('FUTA');
412 FETCH csr_chk_fed_taxability INTO l_taxable;
413 CLOSE csr_chk_fed_taxability;
414
415 ELSIF (p_element_type IN ('SDI_EE', 'SDI_SUBJECT_EE',
416 'SDI_ER', 'SDI_SUBJECT_ER')) THEN
417 Hr_Utility.Set_Location(c_proc, 42);
418 OPEN csr_chk_taxability ('SDI', g_state_jd );
419 FETCH csr_chk_taxability into l_taxable;
420 CLOSE csr_chk_taxability;
421
422 ELSIF (p_element_type IN ('SIT_SUBJECT_WK')) THEN
423 Hr_Utility.Set_Location(c_proc, 43);
424 OPEN csr_chk_taxability ('SIT', g_state_jd );
425 FETCH csr_chk_taxability INTO l_taxable;
426 CLOSE csr_chk_taxability;
427
428 ELSIF (p_element_type IN ('City_SUBJECT_WK')) THEN
429 Hr_Utility.Set_Location(c_proc, 44);
430 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
431 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
432 FETCH csr_chk_taxability INTO l_taxable;
433 -- If the above query returns no rows then check the state level taxablility rule
434 IF csr_chk_taxability%NOTFOUND THEN
435 CLOSE csr_chk_taxability;
436 OPEN csr_chk_taxability ('SIT', g_state_jd);
437 FETCH csr_chk_taxability INTO l_taxable;
438 CLOSE csr_chk_taxability;
439 ELSE
440 CLOSE csr_chk_taxability;
441 END IF;
442
443 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
444 Hr_Utility.Set_Location(c_proc, 45);
445 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
446 FETCH csr_chk_taxability INTO l_taxable;
447 -- If the above query returns no rows then check the state level taxablility rule
448 IF csr_chk_taxability%NOTFOUND THEN
449 CLOSE csr_chk_taxability;
450 OPEN csr_chk_taxability ('SIT', g_state_jd);
451 FETCH csr_chk_taxability INTO l_taxable;
452 CLOSE csr_chk_taxability;
453 ELSE
454 CLOSE csr_chk_taxability;
455 END IF;
456
457 END IF;
458
459 ELSIF (p_input_name = 'Subj NWhable') THEN
460 Hr_Utility.Set_Location(c_proc, 50);
461
462 IF (p_element_type IN ('SIT_SUBJECT_WK')) THEN
463 Hr_Utility.Set_Location(c_proc, 51);
464 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
465 FETCH csr_chk_taxability INTO l_taxable;
466 CLOSE csr_chk_taxability;
467
468 ELSIF (p_element_type IN ('City_SUBJECT_WK')) THEN
469 Hr_Utility.Set_Location(c_proc, 52);
470 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
471 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
472 FETCH csr_chk_taxability INTO l_taxable;
473 -- If the above query returns no rows then check the state level taxablility rule
474 IF csr_chk_taxability%NOTFOUND THEN
475 CLOSE csr_chk_taxability;
476 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
477 FETCH csr_chk_taxability INTO l_taxable;
478 CLOSE csr_chk_taxability;
479 ELSE
480 CLOSE csr_chk_taxability;
481 END IF;
482
483 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
484 Hr_Utility.Set_Location(c_proc, 53);
485 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
486 FETCH csr_chk_taxability INTO l_taxable;
487 -- If the above query returns no rows then check the state level taxablility rule
488 IF csr_chk_taxability%NOTFOUND THEN
489 CLOSE csr_chk_taxability;
490 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
491 FETCH csr_chk_taxability INTO l_taxable;
492 CLOSE csr_chk_taxability;
493 ELSE
494 CLOSE csr_chk_taxability;
495 END IF;
496
497 END IF;
498
499 ELSE
500 Hr_Utility.Set_Location(c_proc, 60);
501 -- otherwise we do not need to check taxability_rules
502 -- in order to set the value of the input value,
503 -- NB. that this step gets executed for tax elements like FIT, Medicare
504 -- as well as Tax balance elements like SUI_SUBJECT_EE
505 l_taxable := 'Y';
506 END IF;
507
508 ELSE
509 -- an Earnings Element so no taxability rules
510 Hr_Utility.Set_Location(c_proc, 70);
511
512 l_taxable := 'Y';
513
514 END IF;
515
516
517 IF (l_taxable = 'Y') THEN
518 Hr_Utility.Set_Location (c_proc, 200);
519 Hr_Utility.Trace('row ' ||
520 To_Char(p_row) ||
521 ' inpvl_id>' ||
522 To_Char(l_input_value_id) ||
523 '< ' ||
524 p_input_name ||
525 ' ' ||
526 p_entry_value);
527
528 p_iv_tbl(p_row) := l_input_value_id;
529 p_iv_names_tbl(p_row) := p_input_name;
530 p_ev_tbl(p_row) := p_entry_value;
531 p_row := p_row + 1; -- next row in plsql table
532 END IF;
533
534 END process_input;
535
536
537
538 PROCEDURE fetch_wage_limits(
539 p_effective_date IN DATE DEFAULT NULL,
540 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
541 p_futa_wage_limit OUT nocopy NUMBER,
542 p_ss_ee_wage_limit OUT nocopy NUMBER,
543 p_ss_er_wage_limit OUT nocopy NUMBER,
544 p_sdi_ee_wage_limit OUT nocopy NUMBER,
545 p_sdi_er_wage_limit OUT nocopy NUMBER,
546 p_sui_ee_wage_limit OUT nocopy NUMBER,
547 p_sui_er_wage_limit OUT nocopy NUMBER) IS
548
549 c_proc VARCHAR2(100) := 'fetch_wage_limits';
550
551 l_futa_wage_limit NUMBER;
552 l_ss_ee_wage_limit NUMBER;
553 l_ss_er_wage_limit NUMBER;
554 l_sdi_ee_wage_limit NUMBER;
555 l_sdi_er_wage_limit NUMBER;
556 l_sui_ee_wage_limit NUMBER;
557 l_sui_er_wage_limit NUMBER;
558
559
560 CURSOR csr_get_fed_wage_limits(v_effective_date DATE) IS
561 SELECT ftax.futa_wage_limit,
562 ftax.ss_ee_wage_limit,
563 ftax.ss_er_wage_limit
564 FROM PAY_US_FEDERAL_TAX_INFO_F ftax
565 WHERE v_effective_date BETWEEN ftax.effective_start_date
566 AND ftax.effective_end_date
567 AND ftax.fed_information_category = '401K LIMITS';
568
569
570 CURSOR csr_get_state_wage_limits(v_effective_date DATE,
571 v_state_abbrev VARCHAR2) IS
572 SELECT ti.sdi_ee_wage_limit,
573 ti.sdi_er_wage_limit,
574 ti.sui_ee_wage_limit,
575 ti.sui_er_wage_limit
576 FROM PAY_US_STATES st,
577 PAY_US_STATE_TAX_INFO_F ti
578 WHERE v_effective_date BETWEEN
579 ti.effective_start_date AND ti.effective_end_date
580 and st.state_code =
581 ti.state_code
582 and st.state_abbrev = v_state_abbrev
583 ;
584
585
586
587 BEGIN
588 /*
589 ** fetch state level wage limits,
590 ** not all states have sdi/sui ee/er wage limits,
591 ** therefore do not check for success
592 */
593 OPEN csr_get_state_wage_limits(p_effective_date, p_state_abbrev);
594 FETCH csr_get_state_wage_limits INTO
595 l_sdi_ee_wage_limit,
596 l_sdi_er_wage_limit,
597 l_sui_ee_wage_limit,
598 l_sui_er_wage_limit;
599 CLOSE csr_get_state_wage_limits;
600
601
602 /*
603 ** fetch federal level wage limits
604 */
605 OPEN csr_get_fed_wage_limits(p_effective_date);
606 FETCH csr_get_fed_wage_limits INTO
607 l_futa_wage_limit,
608 l_ss_ee_wage_limit,
609 l_ss_er_wage_limit;
610 CLOSE csr_get_fed_wage_limits;
611
612
613 /*
614 ** always expect federal level wage limits,
615 ** if fetch failed then error, inform user
616 */
617 /** stub - find an apppriate error message **/
618 IF (l_futa_wage_limit IS NULL OR
619 l_ss_ee_wage_limit IS NULL OR
620 l_ss_er_wage_limit IS NULL) THEN
621 Hr_Utility.Set_Location(c_proc, 10);
622 Hr_Utility.Set_Message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
623 Hr_Utility.Raise_Error;
624 END IF;
625
626
627 /*
628 ** copy limits into return parameters
629 */
630 p_futa_wage_limit := l_futa_wage_limit;
631 p_ss_ee_wage_limit := l_ss_ee_wage_limit;
632 p_ss_er_wage_limit := l_ss_er_wage_limit;
633 p_sdi_ee_wage_limit := l_sdi_ee_wage_limit;
634 p_sdi_er_wage_limit := l_sdi_er_wage_limit;
635 p_sui_ee_wage_limit := l_sui_ee_wage_limit;
636 p_sui_er_wage_limit := l_sui_er_wage_limit;
637
638 END fetch_wage_limits;
639
640
641
642 PROCEDURE process_limits(
643 p_element_type IN VARCHAR2,
644 p_earn_amount IN NUMBER,
645 p_iv_tbl IN Hr_Entry.number_table,
646 p_iv_names_tbl IN Hr_Entry.varchar2_table,
647 p_ev_tbl IN OUT nocopy Hr_Entry.varchar2_table,
648 p_num_ev IN NUMBER) IS
649
650 c_proc VARCHAR2(100) := 'process_limits';
651
652 l_return_bal VARCHAR2(30);
653 l_adj_amt NUMBER;
654 l_excess NUMBER;
655 l_taxable_iv_pos NUMBER := 0;
656 l_old_taxable_bal NUMBER;
657 l_limit NUMBER;
658
659 BEGIN
660
661 Hr_Utility.Trace('|');
662 private_trace(c_proc, p_element_type);
663 Hr_Utility.Trace('| ***** Start Dump *****');
664 FOR l_i IN 1..(p_num_ev - 1) LOOP
665
666 Hr_Utility.Trace('| ' ||
667 To_Char(l_i) ||
668 ' ' ||
669 p_iv_names_tbl(l_i) ||
670 ' ' ||
671 To_Char(p_iv_tbl(l_i)) ||
672 ' ' ||
673 p_ev_tbl(l_i));
674
675 FOR l_j IN 1..1000 LOOP
676 NULL;
677 END LOOP;
678
679 END LOOP;
680 Hr_Utility.Trace('| ***** End Dump *****');
681
682
683 /*
684 ** find position of TAXABLE IV in tbl structure
685 */
686 FOR l_i IN 1..(p_num_ev - 1) LOOP
687
688 IF (p_iv_names_tbl(l_i) = 'TAXABLE') THEN
689 l_taxable_iv_pos := l_i;
690 END IF;
691
692 END LOOP;
693
694
695 /*
696 ** set up taxable balance and limit for limit processing
697 */
698 IF (p_element_type = 'Medicare_EE') THEN
699 l_old_taxable_bal := g_medicare_ee_taxable;
700 /*
701 ** Medicare EE and ER should have an infinite limit,
702 ** at a later stage a legislative limit may be defined,
703 ** therefore set to an arbitary value (99,999,999),
704 ** as used in PAY_US_STATE_TAX_INFO_F for NY
705 */
706 l_limit := 99999999;
707
708 ELSIF (p_element_type = 'Medicare_ER') THEN
709 l_old_taxable_bal := g_medicare_er_taxable;
710 l_limit := 99999999;
711
712 ELSIF (p_element_type = 'FUTA') THEN
713 l_old_taxable_bal := g_futa_taxable;
714 l_limit := g_futa_wage_limit;
715
716 ELSIF (p_element_type = 'SS_EE') THEN
717 l_old_taxable_bal := g_ss_ee_taxable;
718 l_limit := g_ss_ee_wage_limit;
719
720 ELSIF (p_element_type = 'SS_ER') THEN
721 l_old_taxable_bal := g_ss_er_taxable;
722 l_limit := g_ss_er_wage_limit;
723
724 ELSIF (p_element_type = 'SDI_EE') THEN
725 l_old_taxable_bal := g_sdi_ee_taxable;
726 l_limit := g_sdi_ee_wage_limit;
727
728 ELSIF (p_element_type = 'SDI_ER') THEN
729 l_old_taxable_bal := g_sdi_er_taxable;
730 l_limit := g_sdi_er_wage_limit;
731
732 ELSIF (p_element_type = 'SUI_EE') THEN
733 l_old_taxable_bal := g_sui_ee_taxable;
734 l_limit := g_sui_ee_wage_limit;
735
736 ELSIF (p_element_type = 'SUI_ER') THEN
737 l_old_taxable_bal := g_sui_er_taxable;
738 l_limit := g_sui_er_wage_limit;
739 ELSE
740 /** stub - find appropriate message **/
741 Hr_Utility.Set_Location(c_proc, 10);
742 Hr_Utility.Set_Message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
743 Hr_Utility.Raise_Error;
744
745 END IF;
746
747
748 /*
749 ** generic block, applies to all limit processing
750 */
751 IF ((l_old_taxable_bal + p_earn_amount) < l_limit) THEN
752 /*
753 ** no limit exceeded,
754 ** ok to make the balance adjustment,
755 ** do nothing with EV amount of TAXABLE IV
756 */
757 private_trace(c_proc, 'OK to make BA without altering EV amount of TAXABLE IV');
758
759 ELSIF (l_old_taxable_bal > l_limit) THEN
760 /*
761 ** taxable balance already exceeds limit,
762 ** set EV amount of TAXABLE IV to 0,
763 ** therefore the EV amount feeds Excess
764 */
765 private_trace(c_proc, 'limit exceeded, put EV amount of TAXABLE IV into excess');
766 p_ev_tbl(l_taxable_iv_pos) := 0;
767
768 ELSIF (l_old_taxable_bal + p_earn_amount > l_limit) THEN
769 /*
770 ** EV amount of TAXABLE IV will cause limit to be exceeded,
771 ** set EV amount up to limit
772 */
773 l_adj_amt := l_limit - l_old_taxable_bal;
774
775 private_trace(c_proc, 'EV amount of TAXABLE IV up to limit>' ||
776 To_Char(l_adj_amt) || '<');
777
778 l_excess := (p_earn_amount + l_old_taxable_bal) - l_limit;
779
780
781 /*
782 ** excess displayed for information only
783 */
784 private_trace(c_proc, 'excess>' || To_Char(l_excess) || '<');
785
786
787 /*
788 ** modify EV amount of TAXABLE IV before BA processing,
789 ** set EV amount up to limit, remainder goes into excess
790 */
791 p_ev_tbl(l_taxable_iv_pos) := fnd_number.number_to_canonical(l_adj_amt);
792
793 END IF;
794
795 END process_limits;
796
797
798
799 PROCEDURE process_element(
800 p_assignment_id NUMBER,
801 p_consolidation_set_id NUMBER,
802 p_element_type VARCHAR2,
803 p_abbrev_element_type VARCHAR2,
804 p_bg_id NUMBER,
805 p_adjustment_date DATE,
806 p_earn_amount NUMBER,
807 p_adj_amount NUMBER,
808 p_jurisdiction VARCHAR2,
809 p_cost VARCHAR2) IS
810
811 c_proc VARCHAR2(100) := 'process_element';
812
813 -- p_abbrev_element_type - shorter name for the element,
814 -- used to ensure that the group key for all the adjustments
815 -- does not exceed 240 chars (assuming that the
816 -- length of payroll_action_id <= 7
817 -- p_earn_amount - gross earnings. i.e. p_gross_amount
818 -- p_adj_amount - amount of the tax withheld
819 -- p_jurisdiction - jd where the tax was withheld
820
821 CURSOR csr_element IS
822 SELECT e.element_type_id,
823 c.classification_name,
824 e.element_information_category earnings_lookup_type,
825 e.classification_id,
826 e.element_information1 earnings_category
827 FROM PAY_ELEMENT_CLASSIFICATIONS c,
828 PAY_ELEMENT_TYPES_F e
829 WHERE e.element_name = p_element_type
830 and (e.business_group_id = p_bg_id
831 or e.business_group_id IS NULL
832 )
833 and e.classification_id = c.classification_id
834 and p_adjustment_date BETWEEN
835 effective_start_date AND effective_end_date
836 ;
837
838 CURSOR csr_set_mandatory_inputs (v_element_type_id NUMBER) IS
839 SELECT i.name INPUT_NAME,
840 i.input_value_id,
841 Nvl(hr.meaning, NVL(i.default_value,
842 DECODE(i.uom,
843 'I', '0',
844 'M', '0',
845 'N', '0',
846 'T', '0',
847 'C', 'Unknown - US_TAX_BAL_ADJ',
848 'H_DECIMAL1', '0.0',
849 'H_DECIMAL2', '0.00',
850 'H_DECIMAL3', '0.000',
851 'H_HH', '12',
852 'H_HHMM', '12:00',
853 'H_HHMMSS', '12:00:00',
854 'D', fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_adjustment_date)),
855 'ND', To_Char(p_adjustment_date, 'Day')))
856 ) default_value
857 FROM HR_LOOKUPS hr,
858 PAY_INPUT_VALUES_F i
859 WHERE i.element_type_id = v_element_type_id
860 and i.mandatory_flag = 'Y'
861 and i.default_value = hr.lookup_code (+)
862 and i.lookup_type = hr.lookup_type (+)
863 and i.name NOT IN ('Pay Value')
864 ;
865
866 l_iv_tbl hr_entry.number_table;
867 l_iv_names_tbl hr_entry.varchar2_table;
868 l_ev_tbl hr_entry.varchar2_table;
869 l_num_ev NUMBER;
870 l_element csr_element%ROWTYPE;
871 l_ele_link_id NUMBER;
872 l_counter NUMBER;
873 l_payroll_action_id NUMBER;
874
875 BEGIN
876
877 Hr_Utility.Trace('Entering pay_us_tax_bals_adj_pkg.process_element');
878 Hr_Utility.Set_Location(c_proc, 10);
879 OPEN csr_element;
880 FETCH csr_element INTO l_element;
881 CLOSE csr_element;
882
883 IF (l_element.element_type_id IS NULL) THEN
884 Hr_Utility.Set_Location(c_proc, 20);
885 Hr_Utility.Trace('Element does not exist: '||p_element_type);
886 Hr_Utility.Set_Message(801, 'HR_6884_ELE_ENTRY_NO_ELEMENT');
887 Hr_Utility.Raise_Error;
888 END IF;
889
890 Hr_Utility.Set_Location(c_proc, 30);
891 l_ele_link_id := hr_entry_api.get_link(
892 p_assignment_id => p_assignment_id,
893 p_element_type_id => l_element.element_type_id,
894 p_session_date => p_adjustment_date);
895
896 IF (l_ele_link_id IS NULL) THEN
897 Hr_Utility.Set_Location(c_proc, 40);
898 Hr_Utility.Trace('Link does not exist for element: '||p_element_type);
899 Hr_Utility.Set_Message(801, 'PY_51132_TXADJ_LINK_MISSING');
900 Hr_Utility.Set_Message_token ('ELEMENT', p_element_type);
901 Hr_Utility.Raise_Error;
902 END IF;
903
904
905 -- initialize tables
906 l_iv_names_tbl := g_dummy_varchar_tbl;
907 l_iv_tbl := g_dummy_number_tbl;
908 l_ev_tbl := g_dummy_varchar_tbl;
909 l_num_ev := 1;
910
911
912 -- explicitly set the various input values,
913 -- this clearly identifies which input values are expected and will cause failure
914 -- if the input value has been deleted somehow
915 Hr_Utility.Set_Location(c_proc, 50);
916
917 IF (l_element.classification_name IN ('Earnings', 'Imputed Earnings',
918 'Supplemental Earnings')) THEN
919 -- element is an Earnings element,
920 -- populate the global tables to be used later for taxability checking for
921 -- subject withholdable, not-withholdable input values of tax balance elements
922 g_classification_id := l_element.classification_id;
923 g_earnings_category := l_element.earnings_category;
924 g_classification := l_element.classification_name;
925
926 process_input(p_element_type, l_element.element_type_id,
927 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
928 p_bg_id, p_adjustment_date,
929 'Pay Value', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
930
931 ELSIF (p_element_type IN ('FIT')) THEN
932 Hr_Utility.Set_Location (c_proc, 60);
933 process_input(p_element_type, l_element.element_type_id,
934 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
935 p_bg_id, p_adjustment_date,
936 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
937
938 IF (g_classification = 'Supplemental Earnings') THEN
939 process_input(p_element_type, l_element.element_type_id,
940 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
941 p_bg_id, p_adjustment_date,
942 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
943 END IF;
944
945 ELSIF (p_element_type IN ('FIT 3rd Party')) THEN
946 Hr_Utility.Set_Location (c_proc, 65);
947 process_input(p_element_type, l_element.element_type_id,
948 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
949 p_bg_id, p_adjustment_date,
950 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
951
952 ELSIF (p_element_type IN ('SS_EE', 'Medicare_EE')) THEN
953 Hr_Utility.Set_Location(c_proc, 71);
954 IF (p_adj_amount <> 0) THEN
955 process_input(p_element_type, l_element.element_type_id,
956 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
957 p_bg_id, p_adjustment_date,
958 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
959 END IF;
960
961 Hr_Utility.Set_Location(c_proc, 72);
962 process_input(p_element_type, l_element.element_type_id,
963 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
964 p_bg_id, p_adjustment_date,
965 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
966
967 /*
968 ** cap the EV amount for the TAXABLE IV if necessary
969 */
970 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
971 l_iv_names_tbl, l_ev_tbl, l_num_ev);
972
973
974
975
976
977 -- SD1
978 ELSIF (p_element_type IN ('Medicare_ER', 'SS_ER', 'FUTA')) THEN
979 /** sbilling **/
980 /*
981 ** only if processing Medicare_ER, SS_ER or FUTA, the Pay Value should be set
982 ** to the corresponding field on the TBA form (Medicare, FUTA_ER or SS),
983 */
984 IF (p_adj_amount <> 0) THEN
985 process_input(p_element_type, l_element.element_type_id,
986 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
987 p_bg_id, p_adjustment_date,
988 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
989 END IF;
990
991 process_input(p_element_type, l_element.element_type_id,
992 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
993 p_bg_id, p_adjustment_date,
994 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
995
996 /*
997 ** cap the EV amount for the TAXABLE IV if necessary
998 */
999 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1000 l_iv_names_tbl, l_ev_tbl, l_num_ev);
1001
1002
1003
1004
1005
1006
1007 ELSIF (p_element_type IN ('SIT_WK')) THEN
1008 Hr_Utility.Set_Location(c_proc, 81);
1009
1010 process_input(p_element_type, l_element.element_type_id,
1011 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1012 p_bg_id, p_adjustment_date,
1013 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1014 Hr_Utility.Set_Location(c_proc, 82);
1015
1016 process_input(p_element_type, l_element.element_type_id,
1017 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1018 p_bg_id, p_adjustment_date,
1019 'Jurisdiction', p_jurisdiction, l_num_ev);
1020
1021 IF (g_classification = 'Supplemental Earnings') THEN
1022 process_input(p_element_type, l_element.element_type_id,
1023 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1024 p_bg_id, p_adjustment_date,
1025 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1026 END IF;
1027
1028
1029
1030
1031
1032 /** sbilling **/
1033 /*
1034 ** new tax element to be processed, use SIT_WK as a template
1035 */
1036 ELSIF (p_element_type IN ('County_SC_WK')) THEN
1037 Hr_Utility.Set_Location(c_proc, 81);
1038
1039 process_input(p_element_type, l_element.element_type_id,
1040 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1041 p_bg_id, p_adjustment_date,
1042 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1043 Hr_Utility.Set_Location(c_proc, 82);
1044
1045
1046 /*
1047 ** can't put the Gross for the BA into the Gross for the school district tax,
1048 ** County_SC_WK has no TAXABLE input
1049 */
1050 process_input(p_element_type, l_element.element_type_id,
1051 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1052 p_bg_id, p_adjustment_date,
1053 'Jurisdiction', p_jurisdiction, l_num_ev);
1054
1055
1056
1057
1058
1059
1060 ELSIF (p_element_type IN ('SUI_EE', 'SDI_EE')) THEN
1061 Hr_Utility.Set_Location(c_proc, 91);
1062
1063 IF (p_adj_amount <> 0) THEN
1064 process_input(p_element_type, l_element.element_type_id,
1065 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1066 p_bg_id, p_adjustment_date,
1067 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1068 Hr_Utility.Set_Location(c_proc, 915);
1069 END IF;
1070
1071 Hr_Utility.Set_Location(c_proc, 92);
1072
1073 process_input(p_element_type, l_element.element_type_id,
1074 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1075 p_bg_id, p_adjustment_date,
1076 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1077 Hr_Utility.Set_Location(c_proc, 93);
1078
1079 process_input(p_element_type, l_element.element_type_id,
1080 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1081 p_bg_id, p_adjustment_date,
1082 'Jurisdiction', p_jurisdiction, l_num_ev);
1083
1084 /*
1085 ** cap the EV amount for the TAXABLE EV if necessary
1086 */
1087 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1088 l_iv_names_tbl, l_ev_tbl, l_num_ev);
1089
1090
1091
1092
1093
1094
1095
1096 ELSIF (p_element_type IN ('City_WK', 'County_WK')) THEN
1097 Hr_Utility.Set_Location(c_proc, 101);
1098
1099 process_input(p_element_type, l_element.element_type_id,
1100 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1101 p_bg_id, p_adjustment_date,
1102 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1103 Hr_Utility.Set_Location(c_proc, 102);
1104
1105 process_input(p_element_type, l_element.element_type_id,
1106 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1107 p_bg_id, p_adjustment_date,
1108 'Jurisdiction', p_jurisdiction, l_num_ev);
1109
1110 ELSIF (p_element_type IN ('SIT_SUBJECT_WK', 'City_SUBJECT_WK',
1111 'County_SUBJECT_WK')) THEN
1112 Hr_Utility.Set_Location(c_proc, 111);
1113
1114 process_input(p_element_type, l_element.element_type_id,
1115 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1116 p_bg_id, p_adjustment_date,
1117 'Jurisdiction', p_jurisdiction, l_num_ev);
1118 Hr_Utility.Set_Location(c_proc, 112);
1119
1120 process_input(p_element_type, l_element.element_type_id,
1121 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1122 p_bg_id, p_adjustment_date,
1123 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1124 Hr_Utility.Set_Location(c_proc, 113);
1125
1126 process_input(p_element_type, l_element.element_type_id,
1127 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1128 p_bg_id, p_adjustment_date,
1129 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1130 Hr_Utility.Set_Location(c_proc, 114);
1131
1132 IF (g_classification IN ('Imputed Earnings',
1133 'Supplemental Earnings')) THEN
1134 Hr_Utility.Set_Location(c_proc, 115);
1135
1136 process_input (p_element_type, l_element.element_type_id,
1137 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1138 p_bg_id, p_adjustment_date,
1139 'Subj NWhable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1140 END IF;
1141
1142 ELSIF (p_element_type IN ('SDI_SUBJECT_EE', 'SDI_SUBJECT_ER',
1143 'SUI_SUBJECT_EE', 'SUI_SUBJECT_ER')) THEN
1144 Hr_Utility.Set_Location(c_proc, 121);
1145
1146 process_input(p_element_type, l_element.element_type_id,
1147 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1148 p_bg_id, p_adjustment_date,
1149 'Jurisdiction', p_jurisdiction, l_num_ev);
1150 Hr_Utility.Set_Location(c_proc, 122);
1151
1152 process_input(p_element_type, l_element.element_type_id,
1153 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1154 p_bg_id, p_adjustment_date,
1155 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1156 Hr_Utility.Set_Location(c_proc, 123);
1157
1158 process_input(p_element_type, l_element.element_type_id,
1159 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1160 p_bg_id, p_adjustment_date,
1161 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1162
1163 ELSIF (p_element_type IN ('SUI_ER', 'SDI_ER')) THEN
1164 Hr_Utility.Set_Location (c_proc, 124);
1165
1166
1167 /** sbilling **/
1168 /*
1169 ** for SUI_ER and SDI_ER set the amount to be paid for tax equal
1170 ** to the amount entered on the corresponding ER field
1171 */
1172 IF (p_adj_amount <> 0) THEN
1173 process_input(p_element_type, l_element.element_type_id,
1174 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1175 p_bg_id, p_adjustment_date,
1176 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1177 END IF;
1178
1179 process_input(p_element_type, l_element.element_type_id,
1180 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1181 p_bg_id, p_adjustment_date,
1182 'Jurisdiction', p_jurisdiction, l_num_ev);
1183
1184 process_input(p_element_type, l_element.element_type_id,
1185 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1186 p_bg_id, p_adjustment_date,
1187 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1188
1189 /*
1190 ** cap the EV amount for the TAXABLE IV if necessary
1191 */
1192 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1193 l_iv_names_tbl, l_ev_tbl, l_num_ev);
1194 END IF;
1195
1196 -- because process_input will increment l_num_ev if it is successful
1197 l_num_ev := l_num_ev - 1;
1198
1199
1200 -- set mandatory input values,
1201 -- cannot set these to null, core package expects mandatory values to be entered
1202 Hr_Utility.Set_Location(c_proc, 130);
1203
1204 FOR l_req_input IN csr_set_mandatory_inputs (l_element.element_type_id) LOOP
1205 -- first, check if the mandatory input value was explicitly
1206 -- set above, do nothing in this case
1207 Hr_Utility.Set_Location(c_proc, 140);
1208
1209 FOR l_counter IN 1..l_num_ev LOOP
1210
1211 IF (l_req_input.input_name = l_iv_names_tbl(l_counter)) THEN
1212 NULL;
1213 ELSE
1214 -- then the input value was not previously set by one of the
1215 -- process_inputs called in process_elements
1216 Hr_Utility.Set_Location(c_proc, 150);
1217 l_num_ev := l_num_ev + 1;
1218
1219 l_iv_tbl(l_num_ev) := l_req_input.input_value_id;
1220 l_iv_names_tbl(l_num_ev) := l_req_input.input_name;
1221 l_ev_tbl(l_num_ev) := l_req_input.default_value;
1222 END IF;
1223
1224 END LOOP;
1225
1226 END LOOP;
1227
1228 Hr_Utility.Set_Location(c_proc, 160);
1229 create_adjustment(
1230 p_adjustmnt_date => p_adjustment_date,
1231 p_assignment_id => p_assignment_id,
1232 p_element_link_id => l_ele_link_id,
1233 p_consolidation_set_id => p_consolidation_set_id,
1234 p_num_entry_values => l_num_ev,
1235 p_entry_value_tbl => l_ev_tbl,
1236 p_input_value_id_tbl => l_iv_tbl,
1237 p_original_entry_id => NULL,
1238 p_payroll_action_id => l_payroll_action_id,
1239 p_cost => p_cost);
1240
1241 -- populate the payroll_actions table with the adjustment
1242 -- payroll_action_id
1243 Hr_Utility.Set_Location(c_proc, 200);
1244 Hr_Utility.Trace('Tax type= '||p_element_type ||
1245 ' pactid = '||To_Char(l_payroll_action_id));
1246 g_tax_type_tbl(g_pact_cntr) := p_abbrev_element_type;
1247 g_tax_adj_pactid_tbl(g_pact_cntr) := l_payroll_action_id;
1248 g_pact_cntr := g_pact_cntr + 1;
1249
1250 Hr_Utility.Trace('Leaving pay_us_tax_bals_adj_pkg.process_element');
1251
1252 END process_element;
1253
1254
1255
1256 FUNCTION derive_jd_geocode(
1257 p_assignment_id IN NUMBER,
1258 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
1259 p_county_name IN VARCHAR2 DEFAULT NULL,
1260 p_city_name IN VARCHAR2 DEFAULT NULL,
1261 p_zip_code IN VARCHAR2 DEFAULT NULL)
1262 RETURN VARCHAR2 IS
1263
1264 c_proc VARCHAR2(100) := 'derive_jd_geocode';
1265
1266 CURSOR csr_state_code IS
1267 SELECT state_code
1268 FROM PAY_US_STATES
1269 WHERE state_abbrev = p_state_abbrev
1270 ;
1271
1272 CURSOR csr_county_code IS
1273 SELECT cn.state_code,
1274 cn.county_code
1275 FROM PAY_US_COUNTIES cn,
1276 PAY_US_STATES s
1277 WHERE cn.county_name = p_county_name
1278 and cn.state_code = s.state_code
1279 and s.state_abbrev = p_state_abbrev
1280 ;
1281
1282
1283 -- cursors to compare the location provided with the location of
1284 -- the employee's assignment
1285 CURSOR csr_chk_state IS
1286 SELECT 'PASS'
1287 FROM PAY_US_EMP_STATE_TAX_RULES st,
1288 PAY_US_STATES pus
1289 WHERE st.assignment_id = p_assignment_id
1290 and st.state_code = pus.state_code
1291 and pus.state_abbrev = p_state_abbrev
1292 ;
1293
1294 CURSOR csr_chk_local (x_jd VARCHAR2) IS
1295 SELECT 'PASS'
1296 FROM PAY_US_EMP_CITY_TAX_RULES
1297 WHERE assignment_id = p_assignment_id
1298 and jurisdiction_code = x_jd
1299 UNION
1300 SELECT 'PASS'
1301 FROM PAY_US_EMP_COUNTY_TAX_RULES
1302 WHERE assignment_id = p_assignment_id
1303 and jurisdiction_code = x_jd
1304 ;
1305
1306 l_geocode VARCHAR2(11) := '00-000-0000';
1307 l_county_code VARCHAR2(4) := '000' ;
1308 l_state_code VARCHAR2(2) := '00' ;
1309 l_valid_for_asg VARCHAR2(4) := 'FAIL' ;
1310
1311 BEGIN
1312
1313 IF (p_city_name IS NOT NULL AND p_zip_code IS NOT NULL) THEN
1314 Hr_Utility.Set_Location(c_proc, 10);
1315 l_geocode := hr_us_ff_udfs.addr_val(
1316 p_state_abbrev => p_state_abbrev,
1317 p_county_name => p_county_name,
1318 p_city_name => p_city_name,
1319 p_zip_code => p_zip_code );
1320
1321 OPEN csr_chk_local(l_geocode);
1322 FETCH csr_chk_local INTO l_valid_for_asg;
1323 CLOSE csr_chk_local;
1324
1325 IF (l_valid_for_asg = 'FAIL') THEN
1326 Hr_Utility.Set_Location(c_proc, 15);
1327 Hr_Utility.Trace('The city is not valid for the assignment');
1328 Hr_Utility.Set_Message(801, 'PY_51133_TXADJ_INVALID_CITY');
1329 Hr_Utility.Raise_Error;
1330 END IF;
1331
1332 ELSIF (p_county_name IS NOT NULL AND p_state_abbrev IS NOT NULL) THEN
1333 Hr_Utility.Set_Location(c_proc, 20);
1334 OPEN csr_county_code;
1335 FETCH csr_county_code INTO l_state_code, l_county_code;
1336 CLOSE csr_county_code;
1337 l_geocode := l_state_code||'-'||l_county_code||'-0000';
1338
1339 OPEN csr_chk_local(l_geocode);
1340 FETCH csr_chk_local INTO l_valid_for_asg;
1341 CLOSE csr_chk_local;
1342
1343 IF (l_valid_for_asg = 'FAIL') THEN
1344 Hr_Utility.Set_Location(c_proc, 25);
1345 Hr_Utility.Trace('The county is not valid for the assignment');
1346 Hr_Utility.Set_Message(801, 'PY_51133_TXADJ_INVALID_CITY');
1347 Hr_Utility.Raise_Error;
1348 END IF;
1349
1350 ELSIF (p_county_name IS NULL AND p_state_abbrev IS NOT NULL) THEN
1351 Hr_Utility.Set_Location(c_proc, 30);
1352 OPEN csr_state_code;
1353 FETCH csr_state_code INTO l_state_code;
1354 CLOSE csr_state_code;
1355 l_geocode := l_state_code||'-000-0000';
1356
1357 OPEN csr_chk_state;
1358 FETCH csr_chk_state INTO l_valid_for_asg;
1359 CLOSE csr_chk_state;
1360
1361 IF (l_valid_for_asg = 'FAIL') THEN
1362 Hr_Utility.Set_Location(c_proc, 25);
1363 Hr_Utility.Trace('The state is not valid for the assignment');
1364 Hr_Utility.Set_Message(801, 'PY_51133_TXADJ_INVALID_CITY');
1365 Hr_Utility.Raise_Error;
1366 END IF;
1367
1368 ELSE
1369 l_geocode := '00-000-0000';
1370
1371 END IF;
1372
1373 Hr_Utility.Trace('|derived geocode>' || l_geocode || '<');
1374 Return (l_geocode);
1375
1376 END derive_jd_geocode;
1377
1378
1379
1380 FUNCTION taxable_balance(
1381 p_tax_bal_name IN VARCHAR2,
1382 p_ee_or_er IN VARCHAR2,
1383 p_tax_unit_id IN NUMBER,
1384 p_assignment_id IN NUMBER,
1385 p_adjustment_date IN DATE,
1386 p_geocode IN VARCHAR2 DEFAULT NULL)
1387 RETURN NUMBER IS
1388
1389 c_proc VARCHAR2(100) := 'taxable_balance';
1390
1391 l_return_bal NUMBER;
1392 l_date DATE;
1393
1394 CURSOR csr_get_endofyear IS
1395 SELECT to_date('31/12/' || TO_CHAR(p_adjustment_date, 'YYYY'), 'DD/MM/YYYY')
1396 FROM SYS.DUAL
1397 ;
1398
1399 BEGIN
1400 /*
1401 ** find current balance for tax,
1402 ** assignment_id is used to find balance specific to a person,
1403 ** when calculating the adjustment amount up to the limit,
1404 ** the old TAXABLE balance is required
1405 */
1406
1407 /*
1408 ** fetch last day of year, require end of year balance, not date effective balance
1409 */
1410 OPEN csr_get_endofyear;
1411 FETCH csr_get_endofyear INTO l_date;
1412 CLOSE csr_get_endofyear;
1413
1414 l_return_bal := pay_us_tax_bals_pkg.us_tax_balance(
1415 p_tax_balance_category => 'TAXABLE',
1416 p_tax_type => p_tax_bal_name,
1417 p_ee_or_er => p_ee_or_er,
1418 p_time_type => 'YTD',
1419 p_asg_type => 'PER',
1420 p_gre_id_context => p_tax_unit_id,
1421 p_jd_context => p_geocode,
1422 p_assignment_action_id => NULL,
1423 p_assignment_id => p_assignment_id,
1424 p_virtual_date => l_date);
1425
1426 private_trace(c_proc, p_tax_bal_name || ' ' || p_ee_or_er ||
1427 ' TAXABLE>' || To_Char(l_return_bal) || '<');
1428 Return(l_return_bal);
1429
1430 END taxable_balance;
1431
1432 FUNCTION tax_exists (p_jd_code VARCHAR2, p_tax_type VARCHAR2,
1433 p_adj_date DATE)
1434 RETURN VARCHAR2 IS
1435
1436 l_exists VARCHAR2(1) := 'N';
1437
1438 cursor sdi_er_exists is
1439 select 'Y'
1440 from pay_us_state_tax_info_f
1441 where state_code = substr(p_jd_code, 1, 2)
1442 and sdi_er_wage_limit IS NOT NULL
1443 and p_adj_date between effective_start_date and effective_end_date;
1444
1445 cursor sdi_ee_exists is
1446 select 'Y'
1447 from pay_us_state_tax_info_f
1448 where state_code = substr(p_jd_code, 1, 2)
1449 and sdi_ee_wage_limit IS NOT NULL
1450 and p_adj_date between effective_start_date and effective_end_date;
1451
1452 cursor sui_er_exists is
1453 select 'Y'
1454 from pay_us_state_tax_info_f
1455 where state_code = substr(p_jd_code, 1, 2)
1456 and sui_er_wage_limit IS NOT NULL
1457 and p_adj_date between effective_start_date and effective_end_date;
1458
1459 cursor sui_ee_exists is
1460 select 'Y'
1461 from pay_us_state_tax_info_f
1462 where state_code = substr(p_jd_code, 1, 2)
1463 and sui_ee_wage_limit IS NOT NULL
1464 and p_adj_date between effective_start_date and effective_end_date;
1465
1466 cursor sit_exists is
1467 select sit_exists
1468 from pay_us_state_tax_info_f
1469 where state_code = substr(p_jd_code, 1, 2)
1470 and p_adj_date between effective_start_date and effective_end_date;
1471
1472 cursor county_exists is
1473 select county_tax
1474 from pay_us_county_tax_info_f
1475 where jurisdiction_code = substr(p_jd_code, 1, 7)||'0000'
1476 and p_adj_date between effective_start_date and effective_end_date;
1477
1478 cursor city_exists is
1479 select city_tax
1480 from pay_us_city_tax_info_f
1481 where jurisdiction_code = p_jd_code
1482 and p_adj_date between effective_start_date and effective_end_date;
1483
1484 BEGIN
1485
1486 IF (p_tax_type = 'SUI_ER') THEN
1487 open sui_er_exists;
1488 fetch sui_er_exists into l_exists;
1489 close sui_er_exists;
1490
1491 ELSIF (p_tax_type = 'SUI_EE') THEN
1492 open sui_ee_exists;
1493 fetch sui_ee_exists into l_exists;
1494 close sui_ee_exists;
1495
1496 ELSIF (p_tax_type = 'SDI_ER') THEN
1497 open sdi_er_exists;
1498 fetch sdi_er_exists into l_exists;
1499 close sdi_er_exists;
1500
1501 ELSIF (p_tax_type = 'SDI_EE') THEN
1502 open sdi_ee_exists;
1503 fetch sdi_ee_exists into l_exists;
1504 close sdi_ee_exists;
1505
1506 ELSIF (p_tax_type = 'SIT') THEN
1507 open sit_exists;
1508 fetch sit_exists into l_exists;
1509 close sit_exists;
1510
1511 ELSIF (p_tax_type = 'CITY') THEN
1512 open city_exists;
1513 fetch city_exists into l_exists;
1514 close city_exists;
1515
1516 ELSIF (p_tax_type = 'COUNTY') THEN
1517 open county_exists;
1518 fetch county_exists into l_exists;
1519 close county_exists;
1520
1521 ELSE
1522 null;
1523 END IF;
1524
1525 RETURN l_exists;
1526 END tax_exists;
1527
1528 PROCEDURE create_tax_balance_adjustment(
1529 p_adjustment_date DATE,
1530 p_business_group_name VARCHAR2,
1531 p_assignment_number VARCHAR2,
1532 p_tax_unit_id NUMBER,
1533 p_consolidation_set VARCHAR2,
1534 p_earning_element_type VARCHAR2 DEFAULT NULL,
1535 p_gross_amount NUMBER DEFAULT 0,
1536 p_net_amount NUMBER DEFAULT 0,
1537 p_fit NUMBER DEFAULT 0,
1538 p_fit_third VARCHAR2 DEFAULT NULL,
1539 p_ss NUMBER DEFAULT 0,
1540 p_medicare NUMBER DEFAULT 0,
1541 p_sit NUMBER DEFAULT 0,
1542 p_sui NUMBER DEFAULT 0,
1543 p_sdi NUMBER DEFAULT 0,
1544 p_county NUMBER DEFAULT 0,
1545 p_city NUMBER DEFAULT 0,
1546 p_city_name VARCHAR2 DEFAULT NULL,
1547 p_state_abbrev VARCHAR2 DEFAULT NULL,
1548 p_county_name VARCHAR2 DEFAULT NULL,
1549 p_zip_code VARCHAR2 DEFAULT NULL,
1550 p_cost VARCHAR2 DEFAULT NULL,
1551 /** sbilling **/
1552 p_futa_er NUMBER DEFAULT 0,
1553 p_sui_er NUMBER DEFAULT 0,
1554 p_sdi_er NUMBER DEFAULT 0,
1555 p_sch_dist_wh_ee NUMBER DEFAULT 0,
1556 p_sch_dist_jur VARCHAR2 DEFAULT NULL) IS
1557
1558 c_proc VARCHAR2(100) := 'create_tax_balance_adjustment';
1559
1560 l_bg_id NUMBER;
1561 l_consolidation_set_id NUMBER;
1562 l_assignment_id NUMBER;
1563
1564 l_jd_entered VARCHAR2(11) := '00-000-0000';
1565 l_jd_level_entered NUMBER := 1;
1566 l_jd_level_needed NUMBER;
1567
1568 l_primary_asg_state VARCHAR2(2);
1569
1570 l_counter NUMBER;
1571 l_grp_key pay_payroll_actions.legislative_parameters%TYPE;
1572
1573 CURSOR csr_sdi_check IS
1574 SELECT region_2 primary_asg_state
1575 FROM HR_LOCATIONS loc,
1576 PER_ASSIGNMENTS_F asg,
1577 PER_BUSINESS_GROUPS bg -- Bug fix 1398865. Ensures one row is returned
1578 WHERE asg.assignment_number = p_assignment_number
1579 and asg.business_group_id = bg.business_group_id
1580 and bg.name ||'' = p_business_group_name
1581 and p_adjustment_date BETWEEN
1582 asg.effective_start_date AND asg.effective_end_date
1583 and asg.primary_flag = 'Y'
1584 and asg.location_id = loc.location_id
1585 ;
1586
1587 CURSOR csr_sui_geocode IS
1588 SELECT sui_jurisdiction_code,
1589 pus.state_abbrev
1590 FROM PAY_US_EMP_FED_TAX_RULES_F fed,
1591 PER_ASSIGNMENTS_F a,
1592 PER_BUSINESS_GROUPS bg,
1593 pay_us_states pus
1594 WHERE fed.assignment_id = a.assignment_id
1595 and a.assignment_number = p_assignment_number
1596 and a.business_group_id = bg.business_group_id
1597 and bg.name ||'' = p_business_group_name
1598 and p_adjustment_date BETWEEN
1599 fed.effective_start_date AND fed.effective_end_date
1600 and p_adjustment_date BETWEEN
1601 a.effective_start_date AND a.effective_end_date
1602 and fed.sui_state_code = pus.state_code
1603 ;
1604
1605 -- local copy of the tax withhelds,
1606 -- by copying the values to local variables,
1607 -- we avoid defining parameters as IN/OUT variables
1608 l_gross_amount NUMBER;
1609 l_net_amount NUMBER;
1610 l_fit NUMBER;
1611 l_ss NUMBER;
1612 l_medicare NUMBER;
1613 l_sit NUMBER;
1614 l_sui_ee NUMBER;
1615 l_sdi_ee NUMBER;
1616 l_city NUMBER;
1617 l_county NUMBER;
1618 l_total_taxes_withheld NUMBER;
1619 l_fit_third VARCHAR2(5);
1620
1621 /** sbilling **/
1622 l_futa_er NUMBER;
1623 l_sui_er NUMBER;
1624 l_sdi_er NUMBER;
1625 l_sch_dist_wh_ee NUMBER;
1626 l_sch_dist_jur VARCHAR2(10);
1627
1628
1629 BEGIN
1630 --Hr_Utility.Trace_on(NULL, 'RANJANA');
1631
1632 -- copy parameters to local variables and set to 0 if null
1633 l_gross_amount := Nvl(p_gross_amount, 0);
1634 l_net_amount := Nvl(p_net_amount, 0);
1635 l_fit := Nvl(p_fit, 0);
1636 l_fit_third := Nvl(p_FIT_THIRD, 'NO');
1637 l_ss := Nvl(p_ss, 0);
1638 l_medicare := Nvl(p_medicare, 0);
1639 l_sit := Nvl(p_sit, 0);
1640 l_sdi_ee := Nvl(p_sdi, 0);
1641 l_sui_ee := Nvl(p_sui, 0);
1642 l_city := Nvl(p_city, 0);
1643 l_county := Nvl(p_county, 0);
1644
1645 l_futa_er := Nvl(p_futa_er, 0);
1646 l_sui_er := Nvl(p_sui_er, 0);
1647 l_sdi_er := Nvl(p_sdi_er, 0);
1648 l_sch_dist_wh_ee := Nvl(p_sch_dist_wh_ee, 0);
1649 l_sch_dist_jur := Nvl(p_sch_dist_jur, '');
1650
1651 BEGIN
1652 Hr_Utility.Set_Location(c_proc, 5);
1653 SELECT a.assignment_id,
1654 a.business_group_id
1655 INTO l_assignment_id,
1656 l_bg_id
1657 FROM PER_BUSINESS_GROUPS bg,
1658 PER_ASSIGNMENTS_F a
1659 WHERE a.assignment_number = p_assignment_number
1660 and a.business_group_id = bg.business_group_id
1661 and bg.name ||'' = p_business_group_name
1662 and p_adjustment_date BETWEEN
1663 a.effective_start_date AND a.effective_end_date
1664 ;
1665 EXCEPTION
1666 WHEN NO_DATA_FOUND OR too_many_rows THEN
1667 Hr_Utility.Set_Message(801, 'PY_51135_TXADJ_ASG_NOT_FOUND');
1668 Hr_Utility.Raise_Error;
1669 END;
1670
1671
1672 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
1673 p_state_abbrev => p_state_abbrev,
1674 p_county_name => p_county_name,
1675 p_city_name => p_city_name,
1676 p_zip_code => p_zip_code );
1677
1678 /** sbilling **/
1679 /*
1680 ** get limits for tax, should fire once, copy variables into globals
1681 */
1682 IF (g_futa_wage_limit = 0) THEN
1683 fetch_wage_limits(p_adjustment_date,
1684 p_state_abbrev,
1685 g_futa_wage_limit,
1686 g_ss_ee_wage_limit, g_ss_er_wage_limit,
1687 g_sdi_ee_wage_limit, g_sdi_er_wage_limit,
1688 g_sui_ee_wage_limit, g_sui_er_wage_limit);
1689
1690 private_trace(c_proc, 'g_futa_wage_limit>' || g_futa_wage_limit || '<');
1691 private_trace(c_proc, 'g_ss_ee_wage_limit>' || g_ss_ee_wage_limit || '<');
1692 private_trace(c_proc, 'g_ss_er_wage_limit>' || g_ss_er_wage_limit || '<');
1693 private_trace(c_proc, 'g_sdi_ee_wage_limit>' || g_sdi_ee_wage_limit || '<');
1694 private_trace(c_proc, 'g_sdi_er_wage_limit>' || g_sdi_er_wage_limit || '<');
1695 private_trace(c_proc, 'g_sui_ee_wage_limit>' || g_sui_ee_wage_limit || '<');
1696 private_trace(c_proc, 'g_sui_er_wage_limit>' || g_sui_er_wage_limit || '<');
1697 END IF;
1698
1699
1700 -- basic error checking
1701 -- 1. check that Gross = Net + Taxes
1702
1703 IF (l_gross_amount <> 0) THEN
1704 /*
1705 ** stub - do the ER components require validation,
1706 ** l_futa_er + l_sui_er + l_sdi_er + l_sch_dist_wh_ee
1707 */
1708 l_total_taxes_withheld := l_fit + l_ss + l_medicare + l_sit +
1709 l_sui_ee + l_sdi_ee + l_county + l_city +
1710 l_sch_dist_wh_ee;
1711
1712 IF (l_gross_amount <> l_net_amount + l_total_taxes_withheld) THEN
1713 Hr_Utility.Set_Message(801, 'PY_51134_TXADJ_TAX_NET_TOT');
1714 Hr_Utility.Raise_Error;
1715 END IF;
1716
1717 END IF;
1718
1719
1720 -- 2. check that if an earnings element is provided if Gross is non-zero
1721
1722 IF (l_gross_amount <> 0 AND p_earning_element_type IS NULL) THEN
1723 Hr_Utility.Set_Message(801, 'PY_51140_TXADJ_EARN_ELE_REQ');
1724 Hr_Utility.Raise_Error;
1725 END IF;
1726
1727
1728 -- 3. check that SIT = 0 for Alaska, Florida, Nevada, New Hampshire, South Dakota,
1729 -- Tennessee, Texas, Washington, Wyoming, and the Virgin Islands
1730
1731 IF ((l_sit <> 0 OR l_city <> 0 OR l_county <> 0) AND
1732 --p_state_abbrev IN ('AK', 'FL', 'NV', 'NH', 'SD', 'TN', 'TX', 'WA', 'WY', 'VI')) THEN
1733 (tax_exists(l_jd_entered, 'SIT', p_adjustment_date) = 'N')) THEN
1734 Hr_Utility.Set_Message(801, 'PY_51141_TXADJ_SIT_EXEMPT');
1735 Hr_Utility.Raise_Error;
1736 END IF;
1737
1738 /* **** NOT USING JIT TABLES TO CHECK FOR CITY AND COUNTY TAXES YET **** */
1739 /* Wait until the payroll run stops maintaining those balances, and users
1740 are able to clean up their data, before enforcing this through the
1741 Tax Balance Adjustment form. Otherwise they will not be able to zero
1742 out corrupt balances. */
1743
1744 IF ((l_county <> 0) AND
1745 (tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date) = 'N')) THEN
1746 Hr_Utility.Set_Message(801, 'PY_50980_TXADJ_COUNTY_EXEMPT');
1747 Hr_Utility.Raise_Error;
1748 END IF;
1749
1750 IF ((l_city <> 0) AND
1751 (tax_exists(l_jd_entered, 'CITY', p_adjustment_date) = 'N')) THEN
1752 Hr_Utility.Set_Message(801, 'PY_50981_TXADJ_CITY_EXEMPT');
1753 Hr_Utility.Raise_Error;
1754 END IF;
1755
1756
1757 -- 4. check that SDI = 0 for all states but California, Hawaii, New Jersey, New York,
1758 -- Puerto Rico, Rhode Island
1759 --
1760 -- first, need to ensure that the JD passed in is/was the primary assignment state at the
1761 -- time of the adjustment,
1762 -- this is because VERTEX calculations for SDI only occur for the primary work location,
1763 -- if the JD passed in is not the primary work location,
1764 -- then ensuing VERTEX calculations will not reflect the balance adjustments
1765
1766 IF ( l_sdi_ee <> 0 or l_sdi_er <> 0) THEN
1767 OPEN csr_sdi_check;
1768 FETCH csr_sdi_check INTO l_primary_asg_state;
1769 CLOSE csr_sdi_check;
1770
1771 IF (l_primary_asg_state <> p_state_abbrev) THEN
1772 Hr_Utility.Set_Message(801, 'PY_51327_TXADJ_SDI_JD');
1773 Hr_Utility.Raise_Error;
1774 END IF;
1775
1776 END IF;
1777
1778 IF ( l_sdi_ee <> 0) THEN
1779 --IF (p_state_abbrev NOT IN ('CA', 'HI', 'NJ', 'NY', 'RI')) THEN
1780 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date) = 'N') THEN
1781 Hr_Utility.Set_Message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
1782 Hr_Utility.Raise_Error;
1783 END IF;
1784
1785 END IF;
1786
1787 IF ( l_sdi_er <> 0) THEN
1788 --IF (p_state_abbrev NOT IN ('NJ', 'NY')) THEN
1789 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date) = 'N') THEN
1790 Hr_Utility.Set_Message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
1791 Hr_Utility.Raise_Error;
1792 END IF;
1793
1794 END IF;
1795
1796 -- 5. check SUI (EE) Withheld = 0 for all states unless the SUI state is
1797 -- in ('AK', 'NJ', 'PA')
1798
1799 OPEN csr_sui_geocode;
1800 FETCH csr_sui_geocode INTO g_sui_jd, g_sui_state_code;
1801 CLOSE csr_sui_geocode;
1802
1803 private_trace(c_proc, 'g_sui_jd>' || g_sui_jd || '< ' ||
1804 'g_sui_state_code>' || g_sui_state_code || '<');
1805
1806 IF (l_sui_ee <> 0) THEN
1807
1808 /*
1809 ** if the assignment is not in 'AK', 'NJ', 'PA' then SUI_EE does not apply,
1810 ** if the state found for the assignment (CA) <> the state from the
1811 ** assignment (NJ) then SUI_EE does not apply
1812 */
1813 --IF (p_state_abbrev NOT IN ('AK', 'NJ')) OR
1814
1815 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date) = 'N') OR
1816 (g_sui_state_code <> p_state_abbrev) THEN
1817 Hr_Utility.Set_Message(801, 'PY_51328_TXADJ_SUI_EXEMPT');
1818 Hr_Utility.Raise_Error;
1819 END IF;
1820
1821 END IF;
1822
1823
1824 -- determine system keys
1825 /*
1826 BEGIN
1827 Hr_Utility.Set_Location(c_proc, 5);
1828 SELECT a.assignment_id,
1829 a.business_group_id
1830 INTO l_assignment_id,
1831 l_bg_id
1832 FROM PER_BUSINESS_GROUPS bg,
1833 PER_ASSIGNMENTS_F a
1834 WHERE a.assignment_number = p_assignment_number
1835 and a.business_group_id = bg.business_group_id
1836 and bg.name ||'' = p_business_group_name
1837 and p_adjustment_date BETWEEN
1838 a.effective_start_date AND a.effective_end_date
1839 ;
1840 EXCEPTION
1841 WHEN NO_DATA_FOUND OR too_many_rows THEN
1842 Hr_Utility.Set_Message(801, 'PY_51135_TXADJ_ASG_NOT_FOUND');
1843 Hr_Utility.Raise_Error;
1844 END;
1845 */
1846
1847 Hr_Utility.Trace('|');
1848 private_trace(c_proc, 'taxable balances before any BAs');
1849
1850 BEGIN
1851 Hr_Utility.Set_Location(c_proc, 10);
1852 SELECT consolidation_set_id
1853 INTO l_consolidation_set_id
1854 FROM PAY_CONSOLIDATION_SETS
1855 WHERE consolidation_set_name = p_consolidation_set
1856 and business_group_id = l_bg_id
1857 ;
1858 EXCEPTION
1859 WHEN NO_DATA_FOUND OR too_many_rows THEN
1860 Hr_Utility.Set_Message(801, 'PY_51136_TXADJ_CONSET_NOT_FND');
1861 Hr_Utility.Raise_Error;
1862 END;
1863
1864 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
1865 p_state_abbrev => p_state_abbrev,
1866 p_county_name => p_county_name,
1867 p_city_name => p_city_name,
1868 p_zip_code => p_zip_code );
1869
1870 private_trace(c_proc, 'l_jd_entered>' || l_jd_entered || '<');
1871
1872 /** sbilling */
1873 /*
1874 ** put the old taxable balances (before any BA processing) into globals,
1875 ** required for subsequent excess processing
1876 */
1877 g_medicare_ee_taxable := taxable_balance('MEDICARE', 'EE', p_tax_unit_id, l_assignment_id,
1878 p_adjustment_date, NULL);
1879
1880 g_medicare_er_taxable := taxable_balance('MEDICARE', 'ER', p_tax_unit_id, l_assignment_id,
1881 p_adjustment_date, NULL);
1882
1883 g_futa_taxable := taxable_balance('FUTA', 'ER', p_tax_unit_id, l_assignment_id,
1884 p_adjustment_date, NULL);
1885
1886 g_ss_ee_taxable := taxable_balance('SS', 'EE', p_tax_unit_id, l_assignment_id,
1887 p_adjustment_date, NULL);
1888
1889 g_ss_er_taxable := taxable_balance('SS', 'ER', p_tax_unit_id, l_assignment_id,
1890 p_adjustment_date, NULL);
1891
1892 /*
1893 ** the SUI/SDI balances require a JD code to derive the balance for a
1894 ** particular state
1895 */
1896 g_sdi_ee_taxable := taxable_balance('SDI', 'EE', p_tax_unit_id, l_assignment_id,
1897 p_adjustment_date, l_jd_entered);
1898
1899 g_sdi_er_taxable := taxable_balance('SDI', 'ER', p_tax_unit_id, l_assignment_id,
1900 p_adjustment_date, l_jd_entered);
1901
1902 g_sui_ee_taxable := taxable_balance('SUI', 'EE', p_tax_unit_id, l_assignment_id,
1903 p_adjustment_date, l_jd_entered);
1904
1905 g_sui_er_taxable := taxable_balance('SUI', 'ER', p_tax_unit_id, l_assignment_id,
1906 p_adjustment_date, l_jd_entered);
1907
1908
1909 -- set global
1910 g_city_jd := l_jd_entered;
1911 g_state_jd := Substr(l_jd_entered, 1, 2) || '-000-0000';
1912 g_county_jd := Substr(l_jd_entered, 1, 6) || '-0000';
1913 g_classification_id := null;
1914 g_earnings_category := null;
1915 g_classification := null;
1916 g_pact_cntr := 1;
1917 g_tax_type_tbl := g_dummy_varchar_tbl;
1918 g_tax_adj_pactid_tbl := g_dummy_number_tbl;
1919
1920
1921 -- more error checking
1922
1923 -- check the level of l_jd_entered to see if all taxes entered
1924 -- are applicable for the jurisdiction entered
1925 Hr_Utility.Set_Location(c_proc, 15);
1926
1927 IF (l_city <> 0 ) THEN -- jd level needed is for a city
1928 l_jd_level_needed := 4;
1929
1930 ELSIF (l_county <> 0) THEN
1931 l_jd_level_needed := 3;
1932
1933 ELSIF (l_sit <> 0 OR l_sui_ee <> 0 OR l_sdi_ee <> 0) THEN
1934 l_jd_level_needed := 2;
1935
1936 ELSIF (l_fit <> 0 OR l_ss <> 0 OR l_medicare <> 0) THEN
1937 l_jd_level_needed := 1;
1938
1939 END IF;
1940
1941
1942 IF (l_jd_entered = g_fed_jd) THEN
1943 l_jd_level_entered := 1;
1944
1945 ELSIF (l_jd_entered = g_state_jd) THEN
1946 l_jd_level_entered := 2;
1947
1948 ELSIF (l_jd_entered = g_county_jd) THEN
1949 l_jd_level_entered := 3;
1950
1951 ELSE -- jd level entered is for a city
1952 l_jd_level_entered := 4;
1953
1954 END IF;
1955
1956
1957 -- now compare the level of jd entered against the level required
1958 IF (l_jd_level_needed > l_jd_level_entered) THEN
1959 Hr_Utility.Set_Location(c_proc, 20);
1960 Hr_Utility.Trace('Jursidiction entered is insufficient for all taxes');
1961 Hr_Utility.Set_Message(801, 'PY_50015_TXADJ_JD_INSUFF');
1962 Hr_Utility.Raise_Error;
1963 END IF;
1964
1965
1966 -- main processing
1967 Hr_Utility.Set_Location(c_proc, 30);
1968
1969 IF (l_gross_amount <> 0) THEN
1970 process_element(p_assignment_id => l_assignment_id,
1971 p_consolidation_set_id => l_consolidation_set_id,
1972 p_element_type => p_earning_element_type,
1973 p_abbrev_element_type => Substr(p_earning_element_type, 1, 11),
1974 p_bg_id => l_bg_id,
1975 p_adjustment_date => p_adjustment_date,
1976 p_earn_amount => l_gross_amount,
1977 p_adj_amount => l_gross_amount,
1978 p_jurisdiction => l_jd_entered,
1979 p_cost => p_cost);
1980 END IF;
1981
1982 IF (l_fit <> 0) THEN
1983 process_element(p_assignment_id => l_assignment_id,
1984 p_consolidation_set_id => l_consolidation_set_id,
1985 p_element_type => 'FIT',
1986 p_abbrev_element_type => 'FIT',
1987 p_bg_id => l_bg_id,
1988 p_adjustment_date => p_adjustment_date,
1989 p_earn_amount => l_gross_amount,
1990 p_adj_amount => l_fit,
1991 p_jurisdiction => g_fed_jd,
1992 p_cost => p_cost);
1993
1994 IF (l_fit_third = 'YES') THEN
1995 process_element(p_assignment_id => l_assignment_id,
1996 p_consolidation_set_id => l_consolidation_set_id,
1997 p_element_type => 'FIT 3rd Party',
1998 p_abbrev_element_type => '3F',
1999 p_bg_id => l_bg_id,
2000 p_adjustment_date => p_adjustment_date,
2001 p_earn_amount => l_gross_amount,
2002 p_adj_amount => l_fit,
2003 p_jurisdiction => g_fed_jd,
2004 p_cost => p_cost);
2005 END IF;
2006 END IF;
2007
2008 IF (l_ss <> 0) THEN
2009 process_element(p_assignment_id => l_assignment_id,
2010 p_consolidation_set_id => l_consolidation_set_id,
2011 p_element_type => 'SS_EE',
2012 p_abbrev_element_type => 'SS',
2013 p_bg_id => l_bg_id,
2014 p_adjustment_date => p_adjustment_date,
2015 p_earn_amount => NULL,
2016 p_adj_amount => l_ss,
2017 p_jurisdiction => g_fed_jd,
2018 p_cost => p_cost);
2019
2020 process_element(p_assignment_id => l_assignment_id,
2021 p_consolidation_set_id => l_consolidation_set_id,
2022 p_element_type => 'SS_ER',
2023 p_abbrev_element_type => 'SER',
2024 p_bg_id => l_bg_id,
2025 p_adjustment_date => p_adjustment_date,
2026 p_earn_amount => NULL,
2027 p_adj_amount => l_ss,
2028 p_jurisdiction => g_fed_jd,
2029 p_cost => p_cost);
2030 END IF;
2031
2032 IF (l_medicare <> 0) THEN
2033 process_element(p_assignment_id => l_assignment_id,
2034 p_consolidation_set_id => l_consolidation_set_id,
2035 p_element_type => 'Medicare_EE',
2036 p_abbrev_element_type => 'Med',
2037 p_bg_id => l_bg_id,
2038 p_adjustment_date => p_adjustment_date,
2039 p_earn_amount => 0,
2040 p_adj_amount => l_medicare,
2041 p_jurisdiction => g_fed_jd,
2042 p_cost => p_cost);
2043
2044 process_element(p_assignment_id => l_assignment_id,
2045 p_consolidation_set_id => l_consolidation_set_id,
2046 p_element_type => 'Medicare_ER',
2047 p_abbrev_element_type => 'MER',
2048 p_bg_id => l_bg_id,
2049 p_adjustment_date => p_adjustment_date,
2050 p_earn_amount => 0,
2051 p_adj_amount => l_medicare,
2052 p_jurisdiction => g_fed_jd,
2053 p_cost => p_cost);
2054 END IF;
2055
2056 IF (l_futa_er <> 0) THEN
2057 process_element(p_assignment_id => l_assignment_id,
2058 p_consolidation_set_id => l_consolidation_set_id,
2059 p_element_type => 'FUTA',
2060 p_abbrev_element_type => 'FTA',
2061 p_bg_id => l_bg_id,
2062 p_adjustment_date => p_adjustment_date,
2063 p_earn_amount => 0,
2064 p_adj_amount => l_futa_er,
2065 p_jurisdiction => g_fed_jd,
2066 p_cost => p_cost);
2067 END IF;
2068
2069 IF (l_sit <> 0) THEN
2070 process_element(p_assignment_id => l_assignment_id,
2071 p_consolidation_set_id => l_consolidation_set_id,
2072 p_element_type => 'SIT_WK',
2073 p_abbrev_element_type => 'SITK',
2074 p_bg_id => l_bg_id,
2075 p_adjustment_date => p_adjustment_date,
2076 p_earn_amount => l_gross_amount,
2077 p_adj_amount => l_sit,
2078 p_jurisdiction => g_state_jd,
2079 p_cost => p_cost);
2080 END IF;
2081
2082
2083 /** sbilling **/
2084 /*
2085 ** new tax element to be processed, use SIT_WK as a template
2086 */
2087 IF (l_sch_dist_wh_ee <> 0) THEN
2088 private_trace(c_proc, ' l_sch_dist_wh_ee>' || l_sch_dist_wh_ee ||
2089 '< l_sch_dist_jur>' || l_sch_dist_jur || '<');
2090
2091 process_element(p_assignment_id => l_assignment_id,
2092 p_consolidation_set_id => l_consolidation_set_id,
2093 p_element_type => 'County_SC_WK',
2094 p_abbrev_element_type => 'CsWK',
2095 p_bg_id => l_bg_id,
2096 p_adjustment_date => p_adjustment_date,
2097 p_earn_amount => l_gross_amount,
2098 p_adj_amount => l_sch_dist_wh_ee,
2099 p_jurisdiction => l_sch_dist_jur,
2100 p_cost => p_cost);
2101 END IF;
2102
2103
2104
2105 IF (l_city <> 0) THEN
2106 process_element(p_assignment_id => l_assignment_id,
2107 p_consolidation_set_id => l_consolidation_set_id,
2108 p_element_type => 'City_WK',
2109 p_abbrev_element_type => 'CtyK',
2110 p_bg_id => l_bg_id,
2111 p_adjustment_date => p_adjustment_date,
2112 p_earn_amount => l_gross_amount,
2113 p_adj_amount => l_city,
2114 p_jurisdiction => g_city_jd,
2115 p_cost => p_cost);
2116 END IF;
2117
2118 IF (l_county <> 0) THEN
2119 process_element(p_assignment_id => l_assignment_id,
2120 p_consolidation_set_id => l_consolidation_set_id,
2121 p_element_type => 'County_WK',
2122 p_abbrev_element_type => 'CntyK',
2123 p_bg_id => l_bg_id,
2124 p_adjustment_date => p_adjustment_date,
2125 p_earn_amount => l_gross_amount,
2126 p_adj_amount => l_county,
2127 p_jurisdiction => g_county_jd,
2128 p_cost => p_cost);
2129 END IF;
2130
2131 -- subject balances are adjusted if there were any earnings
2132 IF (l_gross_amount <> 0) THEN
2133 -- SD1
2134
2135 /*
2136 ** for Medicare_ER and SS_ER the ER adjustments amounts should equal the EE
2137 ** adjustment amounts, thus l_medicare and l_ss can be used
2138 */
2139 process_element(p_assignment_id => l_assignment_id,
2140 p_consolidation_set_id => l_consolidation_set_id,
2141 p_element_type => 'Medicare_ER',
2142 p_abbrev_element_type => 'MER',
2143 p_bg_id => l_bg_id,
2144 p_adjustment_date => p_adjustment_date,
2145 p_earn_amount => l_gross_amount,
2146 p_adj_amount => 0,
2147 p_jurisdiction => g_fed_jd,
2148 p_cost => p_cost);
2149
2150 process_element(p_assignment_id => l_assignment_id,
2151 p_consolidation_set_id => l_consolidation_set_id,
2152 p_element_type => 'Medicare_EE',
2153 p_abbrev_element_type => 'Med',
2154 p_bg_id => l_bg_id,
2155 p_adjustment_date => p_adjustment_date,
2156 p_earn_amount => l_gross_amount,
2157 p_adj_amount => 0,
2158 p_jurisdiction => g_fed_jd,
2159 p_cost => p_cost);
2160
2161 process_element(p_assignment_id => l_assignment_id,
2162 p_consolidation_set_id => l_consolidation_set_id,
2163 p_element_type => 'SS_ER',
2164 p_abbrev_element_type => 'SER',
2165 p_bg_id => l_bg_id,
2166 p_adjustment_date => p_adjustment_date,
2167 p_earn_amount => l_gross_amount,
2168 p_adj_amount => 0,
2169 p_jurisdiction => g_fed_jd,
2170 p_cost => p_cost);
2171
2172 process_element(p_assignment_id => l_assignment_id,
2173 p_consolidation_set_id => l_consolidation_set_id,
2174 p_element_type => 'SS_EE',
2175 p_abbrev_element_type => 'SS',
2176 p_bg_id => l_bg_id,
2177 p_adjustment_date => p_adjustment_date,
2178 p_earn_amount => l_gross_amount,
2179 p_adj_amount => 0,
2180 p_jurisdiction => g_fed_jd,
2181 p_cost => p_cost);
2182
2183 process_element(p_assignment_id => l_assignment_id,
2184 p_consolidation_set_id => l_consolidation_set_id,
2185 p_element_type => 'FUTA',
2186 p_abbrev_element_type => 'FTA',
2187 p_bg_id => l_bg_id,
2188 p_adjustment_date => p_adjustment_date,
2189 p_earn_amount => l_gross_amount,
2190 p_adj_amount => 0,
2191 p_jurisdiction => g_fed_jd,
2192 p_cost => p_cost);
2193
2194 -- sd 15/5
2195 -- IF (p_state_abbrev NOT IN ('AK', 'FL', 'NV', 'NH', 'SD', 'TN',
2196 -- 'TX', 'WA', 'WY', 'VI')) THEN
2197 IF (tax_exists(l_jd_entered, 'SIT', p_adjustment_date) = 'Y') THEN
2198 process_element(p_assignment_id => l_assignment_id,
2199 p_consolidation_set_id => l_consolidation_set_id,
2200 p_element_type => 'SIT_SUBJECT_WK',
2201 p_abbrev_element_type => 'SITSubK',
2202 p_bg_id => l_bg_id,
2203 p_adjustment_date => p_adjustment_date,
2204 p_earn_amount => l_gross_amount,
2205 p_adj_amount => l_sit,
2206 p_jurisdiction => g_state_jd,
2207 p_cost => p_cost);
2208
2209 END IF;
2210
2211 IF (NVL(tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date),'N') = 'Y') THEN
2212
2213 process_element(p_assignment_id => l_assignment_id,
2214 p_consolidation_set_id => l_consolidation_set_id,
2215 p_element_type => 'County_SUBJECT_WK',
2216 p_abbrev_element_type => 'CntySubK',
2217 p_bg_id => l_bg_id,
2218 p_adjustment_date => p_adjustment_date,
2219 p_earn_amount => l_gross_amount,
2220 p_adj_amount => l_county,
2221 p_jurisdiction => g_county_jd,
2222 p_cost => p_cost);
2223 END IF;
2224
2225 --
2226 -- Check to see if we have a vaild geo-code for the city. This code
2227 -- was added to fix a problem with user defined cities.
2228 --
2229 IF substr(g_city_jd,8,4) <> '0000' THEN
2230 IF (NVL(tax_exists(l_jd_entered, 'CITY', p_adjustment_date),'N') = 'Y') THEN
2231 process_element(p_assignment_id => l_assignment_id,
2232 p_consolidation_set_id => l_consolidation_set_id,
2233 p_element_type => 'City_SUBJECT_WK',
2234 p_abbrev_element_type => 'CtySubK',
2235 p_bg_id => l_bg_id,
2236 p_adjustment_date => p_adjustment_date,
2237 p_earn_amount => l_gross_amount,
2238 p_adj_amount => l_city,
2239 p_jurisdiction => g_city_jd,
2240 p_cost => p_cost);
2241 END IF;
2242 END IF;
2243 END IF; -- (l_gross_amount <> 0)
2244
2245 -- only Alaska, New Jersey and Pennsylvania have SUI_EE in addition
2246 -- to SUI_ER,
2247 -- may also want to check that if the jurisdiction is the SUI jurisdiction,
2248 -- only then create the SUI SUBJECT EE and ER
2249
2250 -- sd 15/5
2251 --IF (p_state_abbrev IN ('AK', 'NJ')) THEN
2252 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date) = 'Y') THEN
2253
2254 IF (p_state_abbrev = g_sui_state_code) THEN
2255
2256 IF (l_gross_amount <> 0) THEN
2257
2258 process_element(p_assignment_id => l_assignment_id,
2259 p_consolidation_set_id => l_consolidation_set_id,
2260 p_element_type => 'SUI_SUBJECT_EE',
2261 p_abbrev_element_type => 'SUISubE',
2262 p_bg_id => l_bg_id,
2263 p_adjustment_date => p_adjustment_date,
2264 p_earn_amount => l_gross_amount,
2265 p_adj_amount => l_sui_ee,
2266 p_jurisdiction => g_sui_jd,
2267 p_cost => p_cost);
2268
2269 process_element(p_assignment_id => l_assignment_id,
2270 p_consolidation_set_id => l_consolidation_set_id,
2271 p_element_type => 'SUI_EE',
2272 p_abbrev_element_type => 'SUIE',
2273 p_bg_id => l_bg_id,
2274 p_adjustment_date => p_adjustment_date,
2275 p_earn_amount => l_gross_amount,
2276 p_adj_amount => l_sui_ee,
2277 p_jurisdiction => g_sui_jd,
2278 p_cost => p_cost);
2279 END IF;
2280 END IF;
2281 END IF;
2282
2283 private_trace(c_proc, 'p_state_abbrev>' || p_state_abbrev || '< ' ||
2284 'g_sui_state_code>' || g_sui_state_code || '<');
2285
2286 -- all states have SUI_ER
2287 IF (p_state_abbrev = g_sui_state_code) THEN
2288
2289 IF (l_gross_amount <> 0) THEN
2290
2291 process_element(p_assignment_id => l_assignment_id,
2292 p_consolidation_set_id => l_consolidation_set_id,
2293 p_element_type => 'SUI_SUBJECT_ER',
2294 p_abbrev_element_type => 'SUISubR',
2295 p_bg_id => l_bg_id,
2296 p_adjustment_date => p_adjustment_date,
2297 p_earn_amount => l_gross_amount,
2298 p_adj_amount => l_sui_ee,
2299 p_jurisdiction => g_sui_jd,
2300 p_cost => p_cost);
2301
2302 process_element(p_assignment_id => l_assignment_id,
2303 p_consolidation_set_id => l_consolidation_set_id,
2304 p_element_type => 'SUI_ER',
2305 p_abbrev_element_type => 'SUIR',
2306 p_bg_id => l_bg_id,
2307 p_adjustment_date => p_adjustment_date,
2308 p_earn_amount => l_gross_amount,
2309 --p_adj_amount => l_sui_ee,
2310 /** sbilling **/
2311 p_adj_amount => l_sui_er,
2312 p_jurisdiction => g_sui_jd,
2313 p_cost => p_cost);
2314 END IF;
2315 END IF;
2316
2317 -- only Hawaii, New Jersey, Puerto Rico have SDI_ER
2318 --IF (p_state_abbrev IN ('NY', 'NJ')) THEN
2319 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date) = 'Y') THEN
2320
2321 IF (l_gross_amount <> 0) THEN
2322
2323 process_element(p_assignment_id => l_assignment_id,
2324 p_consolidation_set_id => l_consolidation_set_id,
2325 p_element_type => 'SDI_SUBJECT_ER',
2326 p_abbrev_element_type => 'SDISubR',
2327 p_bg_id => l_bg_id,
2328 p_adjustment_date => p_adjustment_date,
2329 p_earn_amount => l_gross_amount,
2330 p_adj_amount => l_sdi_ee,
2331 p_jurisdiction => g_state_jd,
2332 p_cost => p_cost);
2333
2334 process_element(p_assignment_id => l_assignment_id,
2335 p_consolidation_set_id => l_consolidation_set_id,
2336 p_element_type => 'SDI_ER',
2337 p_abbrev_element_type => 'SDIR',
2338 p_bg_id => l_bg_id,
2339 p_adjustment_date => p_adjustment_date,
2340 p_earn_amount => l_gross_amount,
2341 --p_adj_amount => l_sdi_ee,
2342 /** sbilling **/
2343 p_adj_amount => l_sdi_er,
2344 p_jurisdiction => g_state_jd,
2345 p_cost => p_cost);
2346 END IF;
2347 END IF;
2348
2349 -- only California, Hawaii, New Jersey, New York, Rhode Island,
2350 -- and Puerto Rico have SDI_EE
2351
2352 --IF (p_state_abbrev IN ('CA', 'NY', 'RI', 'HI', 'NJ')) THEN
2353 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date) = 'Y') THEN
2354
2355 IF (l_gross_amount <> 0) THEN
2356 process_element(p_assignment_id => l_assignment_id,
2357 p_consolidation_set_id => l_consolidation_set_id,
2358 p_element_type => 'SDI_SUBJECT_EE',
2359 p_abbrev_element_type => 'SDISubE',
2360 p_bg_id => l_bg_id,
2361 p_adjustment_date => p_adjustment_date,
2362 p_earn_amount => l_gross_amount,
2363 p_adj_amount => l_sdi_ee,
2364 p_jurisdiction => g_state_jd,
2365 p_cost => p_cost);
2366
2367 process_element(p_assignment_id => l_assignment_id,
2368 p_consolidation_set_id => l_consolidation_set_id,
2369 p_element_type => 'SDI_EE',
2370 p_abbrev_element_type => 'SDIE',
2371 p_bg_id => l_bg_id,
2372 p_adjustment_date => p_adjustment_date,
2373 p_earn_amount => l_gross_amount,
2374 p_adj_amount => l_sdi_ee,
2375 p_jurisdiction => g_state_jd,
2376 p_cost => p_cost);
2377 END IF;
2378 END IF;
2379
2380
2381 -- finally, group the payroll_actions by concatenating the tax type with
2382 -- the payroll_action
2383 g_pact_cntr := g_pact_cntr - 1;
2384
2385 Hr_Utility.Set_Location (c_proc, 100);
2386
2387 FOR l_counter in 1..g_pact_cntr LOOP
2388
2389 /* l_grp_key := l_grp_key || g_tax_type_tbl(l_counter) ||
2390 To_Char(g_tax_adj_pactid_tbl(l_counter)) || ':'; */
2391
2392 l_grp_key := g_tax_type_tbl(l_counter) ||
2393 To_Char(g_tax_adj_pactid_tbl(l_counter)) || ':';
2394
2395 UPDATE pay_payroll_actions
2396 SET legislative_parameters = l_grp_key
2397 WHERE payroll_action_id = g_tax_adj_pactid_tbl(l_counter);
2398
2399 END LOOP;
2400
2401 Hr_Utility.Set_Location (c_proc, 120);
2402
2403 /* FOR l_counter IN 1..g_pact_cntr LOOP
2404
2405 UPDATE pay_payroll_actions
2406 SET legislative_parameters = l_grp_key
2407 WHERE payroll_action_id = g_tax_adj_pactid_tbl(l_counter);
2408
2409 END LOOP;
2410 */
2411
2412 END create_tax_balance_adjustment;
2413
2414 END pay_us_tax_bals_adj_pkg;