1 PACKAGE BODY pay_us_tax_bals_adj_api AS
2 /* $Header: pytbaapi.pkb 120.22.12020000.9 2013/03/08 10:54:32 ybudamal ship $ */
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.
56 if the element to be processed is
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
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
134 adjust that balance regardless of the
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
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 tclewis 09-SEP-2008 115.33 7362837 Added code to create run result for the
169 'SIT_WK_NON_AGGREGATE_RED_SUBJ_WHABLE' Element for supplemental and
170 imputed earnings.
171 tclewis 17-OCT-2008 115.34 7362837 Added code to create run result for the
172 'FIT_NON_AGGREGATE_RED_SUBJ_WHABLE' Element for supplemental and
173 imputed earnings.
174 pannapur 19-Jan-2009 Bug 7692482.
175 tclewis 22-dec-2009 115.35 9110226 modified csr_sdi_check to handle
176 assignment work_at_home.
177 emunisek 08-Apr-2010 115.40 9075526 Made changes such that SUI Automate Credit
178 is considered during Tax Adjustments also.
179 tclewis 25-MAY-2010 115.42 9741140 atted upper on p_input_name in
180 process_input.
181 emunisek 29-Jul-2010 115.43 9872952 Added changes such that, when a Balance Adjustment
182 is made while using Direct US Federal Balances,
183 the Gross and other related Balances are created
184 for Federal Tax Balances
185 tclewis 18-AUG-2010 115.44 9951009 Added 'SS_ER_W11' to the ckeck
186 for tax tax elemetns
187 'SS_EE', 'SS_ER' in the
188 process_inputs prodecure.
189 tclewis 07-OCT-2010 115.45 10150905 IF SUI Automation is Enabled, we fetch the
190 YTD for SUI EE and ER Taxable with a date mode
191 balance call. The Balance calls don't adjust the
192 effective date of the balance call for terminated
193 assignments. Modified the code the check for
194 greater of the max end date end of year or assignment.
195 and use that date for the balance call.
196 tclewis 22-OCT-2010 115.46 9545789 Added an additional join the the code that validates
197 the assignment number as of the adjustment date.
198 Additionally Checks for the tax_unit_id passed
199 into the package.
200 tc;ewos 12-JAN-2011 115.47 11060643 Added parameter p_ss_er to the
201 procedure
202 create_tax_balance_adjustment.
203 Will use that parameter when
204 adjusting
205 FICA (Social Security) ER.
206 emunisek 15-APR-2011 115.48 11652231 Modified Procedure process_input to check for the
207 Taxability Rules when process the Element Types
208 FIT_SUBJECT and EIC_SUBJECT. This change will enable
209 the creation of Run Results for Elements FIT_SUBJECT
210 and EIC_SUBJECT when a Balance Adjustment is done. These
214 2 new parameters p_sui_ee_warning and p_sui_er_warning,
211 Run results will be used by the Direct Federal Balances
212 approach to correctly indicate the FIT and EIC Tax Balances
213 nvelaga 21-JUL-2011 115.49 12694875 Modified create_tax_balance_adjustment procedure to output
215 which will be used to display warning message when SUI
216 taxable reaches the limit.
217 Added the logic to extract SUI Taxable Adjusted amount
218 in process_element procedure.
219 Created a overloaded create_tax_balance_adjustment
220 procedure with out 2 new output parameters.
221 tclewis 07-SEP-2011 115.50 Modified the package to process the PSD Elements for
222 PA Act 30. We will use the School RS element and the
223 WK elements otherwise.
224 ybudamal 13-FEB-2012 115.53 13634961 Modified the package to add the conditions to check tax
225 exemptions for the following element types, 'FIT_SUBJECT',
226 'MEDICARE_SUBJECT_EE','MEDICARE_SUBJECT_ER','SS_SUBJECT_EE',
227 'SS_SUBJECT_ER','FUTA_SUBJECT','FIT','SS_EE','SS_ER',
228 'Medicare_EE','Medicare_ER','FUTA','SIT_WK','County_SC_WK',
229 'City_WK','County_WK','SIT_SUBJECT_WK','City_SUBJECT_WK',
230 'County_SUBJECT_WK','School_SUBJECT_WK'.
231 ybudamal 14-FEB-2012 115.54 13634961 Modified the intialization of local variable 'l_sd_cty_or_cnt_exempt'
232 from 'N' to 'Y' present in the procedure 'create_tax_balance_adjustment'
233 nvelaga 02-SEP-2012 115.55 9796821 Modified the calculation of Medicare ER to use new parameter p_Medicare_ER.
234 Added logic to create run result for Medicare_EE_Over_Limit eff 2013.
235 tclewis 16-NOV-2012 115.56 14512218 Modified code that adjusts PSD balance to check the respective r
236 resident / work PSD component and to ONLY MAKE THE ADJSUTMENT if
237 the resident / work jurisdiction code component <> 880000.
238 14406993 Implemented code to check the NYC boroughs and if the resident
239 /work jurisidictions are in the NYC boroughs set city tax to N.
240 this will occur in the tax_exists function.
241 nvelaga 22-NOV-2012 115.57 15852506 Modified the logic to create run result for Medicare_EE_Over_Limit
242 eff 2013. Subtracted 0.01 from the new Medicare Limit insteadof 1.
243 nvelaga 29-NOV-2012 115.58 14406993 Changed the usage of != to <> for GSCC Failure.
244 tclewis 15-FEB-2013 115.59 16299211 Modified tax exist with respect to
245 city tax. Check to see if the JD
246 endered is one of he 5 NYC boroughs
247 and NOT = to the resident address
248 then set city_tax = N.
249 ybudamal 08-MAR-2013 115.60 16099323 Modified the package to add the conditions for processing the
250 following element types, 'FIT_SUBJECT','MEDICARE_SUBJECT_EE',
251 'MEDICARE_SUBJECT_ER','SS_SUBJECT_EE','SS_SUBJECT_ER','FUTA_SUBJECT',
252 'SIT_SUBJECT_WK','City_SUBJECT_WK','County_SUBJECT_WK' when the
253 corresponding element type exemptions are marked as 'Yes'. To process
254 the 'School_SUBJECT_WK' element type, removed the condition of checking
255 the l_sd_cty_or_cnt_exempt value.
256
257 ========================================================================*/
258
259
260 -- global variables
261 g_classification VARCHAR2(80);
262 g_earnings_category VARCHAR2(30);
263 g_classification_id NUMBER;
264 g_fed_jd VARCHAR2(11) := '00-000-0000';
265 g_state_jd VARCHAR2(11) := '00-000-0000';
266 g_sui_jd VARCHAR2(11) := '00-000-0000';
267 g_sui_state_code VARCHAR2(2);
268 g_county_jd VARCHAR2(11) := '00-000-0000';
269 g_city_jd VARCHAR2(11) := '00-000-0000';
270 g_sch_dist_jur VARCHAR2(10) := '00-00000';
271 g_dummy_varchar_tbl hr_entry.varchar2_table;
272 g_dummy_number_tbl hr_entry.number_table;
273
274 /* federal level 'balances' */
275 g_medicare_ee_taxable NUMBER := 0;
276 g_medicare_er_taxable NUMBER := 0;
277 g_futa_taxable NUMBER := 0;
278 g_ss_ee_taxable NUMBER := 0;
279 g_ss_er_taxable NUMBER := 0;
280
281 /* Federal self adjust methods */
282 g_futa_sa_method varchar2(25);
283 g_ss_sa_method varchar2(25);
284 g_medicare_sa_method varchar2(25);
285
286 /* state level 'balances' */
287 g_sdi_ee_taxable NUMBER := 0;
288 g_sdi1_ee_taxable NUMBER := 0;
289 g_sdi_er_taxable NUMBER := 0;
290 g_sui_ee_taxable NUMBER := 0;
291 g_sui_er_taxable NUMBER := 0;
292
293 /*state Self Adjust method */
294 g_sdi_sa_method varchar2(25);
298 /* federal level 'limits' */
295 g_sdi1_sa_method varchar2(25);
296 g_sui_sa_method varchar2(25);
297
299 g_futa_wage_limit NUMBER := 0;
300 g_ss_ee_wage_limit NUMBER := 0;
301 g_ss_er_wage_limit NUMBER := 0;
302
303 /* state level 'limits' */
304 g_sdi_ee_wage_limit NUMBER := 0;
305 g_sdi1_ee_wage_limit NUMBER := 0;
306 g_sdi_er_wage_limit NUMBER := 0;
307 g_sui_ee_wage_limit NUMBER := 0;
308 g_sui_er_wage_limit NUMBER := 0;
309
310 /* federal level tax group */
311 g_tax_group varchar2(240) := 'NOT_ENTERED';
312
313 /* Bug 12694875 Starts */
314 /* SUI Tax Adjustment Amounts */
315 g_sui_ee_tax_adj_amt NUMBER := 0;
316 g_sui_er_tax_adj_amt NUMBER := 0;
317 /* Bug 12694875 Starts */
318
319 PROCEDURE process_input(
320 p_element_type IN VARCHAR2,
321 p_element_type_id IN NUMBER,
322 p_iv_tbl IN OUT NOCOPY hr_entry.number_table,
323 p_iv_names_tbl IN OUT NOCOPY hr_entry.varchar2_table,
324 p_ev_tbl IN OUT NOCOPY hr_entry.varchar2_table,
325 p_bg_id IN NUMBER,
326 p_adj_date IN DATE,
327 p_input_name IN VARCHAR2,
328 p_entry_value IN VARCHAR2,
329 p_row IN OUT NOCOPY NUMBER) IS
330
331 CURSOR csr_inputs(v_element_type_id IN NUMBER,
332 v_input_name IN VARCHAR2) IS
333 SELECT i.input_value_id
334 FROM pay_input_values_f i
335 WHERE i.element_type_id = v_element_type_id
336 AND (i.business_group_id = p_bg_id
337 OR i.business_group_id IS NULL)
338 AND upper(i.name) = upper(v_input_name)
339 AND p_adj_date BETWEEN
340 i.effective_start_date AND i.effective_end_date
341 ;
342
343 CURSOR csr_chk_taxability(v_tax_type VARCHAR2,
344 v_jurisdiction_code VARCHAR2) IS
345 SELECT 'Y'
346 FROM PAY_TAXABILITY_RULES
347 WHERE jurisdiction_code = v_jurisdiction_code
348 and tax_category = g_earnings_category
349 and tax_type = v_tax_type
350 and classification_id = g_classification_id
351 and nvl(status,'VALID') <> 'D'
352 ;
353
354 CURSOR csr_chk_fed_taxability(v_tax_type VARCHAR2) IS
355 SELECT 'Y'
356 FROM PAY_TAXABILITY_RULES
357 WHERE jurisdiction_code = g_fed_jd
358 and tax_category = g_earnings_category
359 and tax_type = v_tax_type
360 and classification_id = g_classification_id
361 and nvl(status,'VALID') <> 'D'
362 ;
363
364 CURSOR csr_chk_all_taxability(v_jurisdiction_code VARCHAR2) IS
365 SELECT 'N'
366 FROM PAY_TAXABILITY_RULES
367 WHERE jurisdiction_code = v_jurisdiction_code
368 and nvl(status,'VALID') <> 'D'
369
370 ;
371
372 CURSOR csr_get_school_jd_level IS
373 SELECT 'Y'
374 FROM pay_us_county_school_dsts pcsd
375 WHERE pcsd.state_code = substr(g_sch_dist_jur,1,2)
376 AND pcsd.school_dst_code = substr(g_sch_dist_jur,4,5)
377 ;
378
379 l_input_value_id NUMBER;
380 l_taxable VARCHAR2(1) := 'N';
381 c_proc VARCHAR2(100) := 'pay_us_tax_bals_adj_pkg.process_input';
382 l_jurisdiction_code VARCHAR2(11);
383 l_county_sch_dsts VARCHAR2(10) := 'N';
384
385 BEGIN
386 hr_utility.set_location(c_proc, 10);
387
388 OPEN csr_inputs (p_element_type_id, p_input_name);
389 FETCH csr_inputs INTO l_input_value_id;
390 CLOSE csr_inputs;
391
392 IF (l_input_value_id IS NULL) THEN
393 hr_utility.set_location(c_proc, 20);
394 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
395 hr_utility.raise_error;
396 END IF;
397
398 -- check taxability of the tax balance element
399 hr_utility.set_location(c_proc, 30);
400
401 IF (g_classification IN ('Imputed Earnings', 'Supplemental Earnings')) THEN
402
403 /** sbilling **/
404 /*
405 ** no RRVs were being generated for Medicare_EE's TAXABLE EV as p_element_type
406 ** (Medicare_EE) didn't satisfy any if conditions in the inner block,
407 ** l_taxable was not set to Y,
408 ** therefore the table structure was not populated,
409 ** at a later stage Medicare_EE's TAXABLE EV would be defaulted to 0,
410 ** causing the taxable amount to appear in Excess,
411 */
412 IF (p_input_name = 'Subj Whable' OR upper(p_input_name) = 'TAXABLE') THEN
413
414 hr_utility.set_location(c_proc, 40);
415
416 IF (p_element_type IN ('SUI_EE', 'SUI_SUBJECT_EE',
417 'SUI_ER', 'SUI_SUBJECT_ER')) THEN
418 hr_utility.set_location(c_proc, 41);
419 OPEN csr_chk_taxability ('SUI', g_state_jd );
420 FETCH csr_chk_taxability INTO l_taxable;
421 CLOSE csr_chk_taxability;
422 /*Bug#9872952 Added MEDICARE_SUBJECT_EE and MEDICARE_SUBJECT_ER */
423 ELSIF (p_element_type IN ('Medicare_EE', 'Medicare_ER' ,'MEDICARE_SUBJECT_EE','MEDICARE_SUBJECT_ER')) THEN
424 hr_utility.set_location(c_proc, 42);
425 OPEN csr_chk_fed_taxability ('MEDICARE');
426 FETCH csr_chk_fed_taxability INTO l_taxable;
427 CLOSE csr_chk_fed_taxability;
428 /*Bug#9872952 Added SS_SUBJECT_ER and SS_SUBJECT_EE */
429 ELSIF (p_element_type IN ('SS_EE', 'SS_ER','SS_SUBJECT_ER','SS_SUBJECT_EE', 'SS_ER_W11')) THEN
433 CLOSE csr_chk_fed_taxability;
430 hr_utility.set_location(c_proc, 43);
431 OPEN csr_chk_fed_taxability ('SS');
432 FETCH csr_chk_fed_taxability INTO l_taxable;
434 /*Bug#9872952 Added FUTA_SUBJECT*/
435 ELSIF (p_element_type IN ('FUTA','FUTA_SUBJECT')) THEN
436 hr_utility.set_location(c_proc, 43);
437 OPEN csr_chk_fed_taxability ('FUTA');
438 FETCH csr_chk_fed_taxability INTO l_taxable;
439 CLOSE csr_chk_fed_taxability;
440
441 ELSIF (p_element_type IN ('SDI_EE', 'SDI_SUBJECT_EE',
442 'SDI_ER', 'SDI_SUBJECT_ER',
443 'SDI1_EE' )) THEN
444 hr_utility.set_location(c_proc, 42);
445 OPEN csr_chk_taxability ('SDI', g_state_jd );
446 FETCH csr_chk_taxability INTO l_taxable;
447 CLOSE csr_chk_taxability;
448
449 ELSIF (p_element_type = ('SIT_SUBJECT_WK') ) THEN
450 hr_utility.set_location(c_proc, 43);
451 OPEN csr_chk_taxability ('SIT', g_state_jd );
452 FETCH csr_chk_taxability INTO l_taxable;
453 CLOSE csr_chk_taxability;
454
455 ELSIF (p_element_type IN ('City_SUBJECT_WK', 'City_PSD_SUBJECT_WK', 'City_PSD_SUBJECT_RS') ) THEN
456 hr_utility.set_location(c_proc, 44);
457 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
458 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
459 FETCH csr_chk_taxability INTO l_taxable;
460 -- If the above query returns no rows then check the state level taxablility rule
461 -- as we are checking for SUBJ whable here. If we don't find a row for locality
462 -- subj whable, we must check for subj NWhable befor defaulting to state level.
463 -- NOTE currently is does not cover a situation where the specific element type
464 -- is not subject (WHable or NWhable) and the state is Whable.
465 IF csr_chk_taxability%NOTFOUND THEN -- 1
466 CLOSE csr_chk_taxability;
467 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
468 FETCH csr_chk_taxability INTO l_taxable;
469 IF csr_chk_taxability%NOTFOUND THEN -- 2
470 -- check for the existance of any taxability rules at this JD level.
471 -- if we get to this point and the csr_chk_all_taxability returns data
472 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
473 CLOSE csr_chk_taxability;
474 OPEN csr_chk_all_taxability (l_jurisdiction_code);
475 FETCH csr_chk_all_taxability INTO l_taxable;
476 IF csr_chk_all_taxability%NOTFOUND THEN --3
477 CLOSE csr_chk_all_taxability;
478 OPEN csr_chk_taxability ('SIT', g_state_jd);
479 FETCH csr_chk_taxability INTO l_taxable;
480 CLOSE csr_chk_taxability;
481 ELSE -- 3
482 l_taxable := 'N';
483 CLOSE csr_chk_all_taxability;
484 END IF; -- 3
485 ELSE -- 2
486 l_taxable := 'N';
487 CLOSE csr_chk_taxability;
488 END IF; --2
489 ELSE -- 1
490 CLOSE csr_chk_taxability;
491 END IF; --1
492
493 /* NEW code for school district processing */
494
495 ELSIF p_element_type = ('School_SUBJECT_WK') THEN
496 -- IF THE STATE JURISDICTION IS OHIO THEN CHECK TAXABLILITY RULES OF THE STATE LEVEL
497 -- ELESE CHECK THE TAXABLILITY RULES OF THE RESPECTIVE CITY OR COUNTY THE SCHOOL
498 -- DISTRICT BELONGS TO.
499 IF SUBSTR(G_city_jd,1,2) = '36' THEN
500 OPEN csr_chk_taxability ('SIT', g_state_jd);
501 FETCH csr_chk_taxability INTO l_taxable;
502 CLOSE csr_chk_taxability;
503 ELSE -- state code = 36
504 OPEN csr_get_school_jd_level;
505 fetch csr_get_school_jd_level inTO l_county_sch_dsts;
506 if csr_get_school_jd_level%NOTFOUND THEN
507 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
508 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
509 FETCH csr_chk_taxability INTO l_taxable;
510 -- If the above query returns no rows then check the state level taxablility rule
511 -- as we are checking for SUBJ whable here. If we don't find a row for locality
512 -- subj whable, we must check for subj NWhable befor defaulting to state level.
513 -- NOTE currently is does not cover a situation where the specific element type
514 -- is not subject (WHable or NWhable) and the state is Whable.
515 IF csr_chk_taxability%NOTFOUND THEN
516 CLOSE csr_chk_taxability;
517 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
518 FETCH csr_chk_taxability INTO l_taxable;
519 IF csr_chk_taxability%NOTFOUND THEN -- 2
520 -- check for the existance of any taxability rules at this JD level.
521 -- if we get to this point and the csr_chk_all_taxability returns data
522 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
523 CLOSE csr_chk_taxability;
524 OPEN csr_chk_all_taxability (l_jurisdiction_code);
525 FETCH csr_chk_all_taxability INTO l_taxable;
526 IF csr_chk_all_taxability%NOTFOUND THEN --3
527 CLOSE csr_chk_all_taxability;
528 OPEN csr_chk_taxability ('SIT', g_state_jd);
529 FETCH csr_chk_taxability INTO l_taxable;
530 CLOSE csr_chk_taxability;
531 ELSE -- 3
532 l_taxable := 'N';
536 l_taxable := 'N';
533 CLOSE csr_chk_all_taxability;
534 END IF; -- 3
535 ELSE -- 2
537 CLOSE csr_chk_taxability;
538 END IF; --2
539 ELSE
540 CLOSE csr_chk_taxability;
541 END IF;
542
543 ELSE -- csr_get_school_jd_level%NOT_FOUND
544 -- row found in cursor so this is a county school district
545 -- check the county TR
546
547 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
548 FETCH csr_chk_taxability INTO l_taxable;
549 -- If the above query returns no rows then check the state level taxablility rule
550 -- as we are checking for SUBJ whable here. If we don't find a row for locality
551 -- subj whable, we must check for subj NWhable befor defaulting to state level.
552 -- NOTE currently is does not cover a situation where the specific element type
553 -- is not subject (WHable or NWhable) and the state is Whable.
554 IF csr_chk_taxability%NOTFOUND THEN
555 CLOSE csr_chk_taxability;
556 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
557 FETCH csr_chk_taxability INTO l_taxable;
558 IF csr_chk_taxability%NOTFOUND THEN -- 2
559 -- check for the existance of any taxability rules at this JD level.
560 -- if we get to this point and the csr_chk_all_taxability returns data
561 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
562 CLOSE csr_chk_taxability;
563 OPEN csr_chk_all_taxability (g_county_jd);
564 FETCH csr_chk_all_taxability INTO l_taxable;
565 IF csr_chk_all_taxability%NOTFOUND THEN --3
566 CLOSE csr_chk_all_taxability;
567 OPEN csr_chk_taxability ('SIT', g_state_jd);
568 FETCH csr_chk_taxability INTO l_taxable;
569 CLOSE csr_chk_taxability;
570 ELSE -- 3
571 l_taxable := 'N';
572 CLOSE csr_chk_all_taxability;
573 END IF; -- 3
574 ELSE -- 2
575 l_taxable := 'N';
576 CLOSE csr_chk_taxability;
577 END IF; --2
578 ELSE
579 CLOSE csr_chk_taxability;
580 END IF;
581 END IF; -- csr_get_school_jd_level%NOT_FOUND
582
583 CLOSE csr_get_school_jd_level;
584
585 END IF; -- state code = '36'
586
587 /* End of code for school district taxes. */
588
589 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
590 hr_utility.set_location(c_proc, 45);
591 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
592 FETCH csr_chk_taxability INTO l_taxable;
593 -- If the above query returns no rows then check the state level taxablility rule
594 -- as we are checking for SUBJ whable here. If we don't find a row for locality
595 -- subj whable, we must check for subj NWhable befor defaulting to state level.
596 -- NOTE currently is does not cover a situation where the specific element type
597 -- is not subject (WHable or NWhable) and the state is Whable.
598 IF csr_chk_taxability%NOTFOUND THEN
599 CLOSE csr_chk_taxability;
600 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
601 FETCH csr_chk_taxability INTO l_taxable;
602 IF csr_chk_taxability%NOTFOUND THEN -- 2
603 -- check for the existance of any taxability rules at this JD level.
604 -- if we get to this point and the csr_chk_all_taxability returns data
605 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
606 CLOSE csr_chk_taxability;
607 OPEN csr_chk_all_taxability (g_county_jd);
608 FETCH csr_chk_all_taxability INTO l_taxable;
609 IF csr_chk_all_taxability%NOTFOUND THEN --3
610 CLOSE csr_chk_all_taxability;
611 OPEN csr_chk_taxability ('SIT', g_state_jd);
612 FETCH csr_chk_taxability INTO l_taxable;
613 CLOSE csr_chk_taxability;
614 ELSE -- 3
615 l_taxable := 'N';
616 CLOSE csr_chk_all_taxability;
617 END IF; -- 3
618 ELSE -- 2
619 l_taxable := 'N';
620 CLOSE csr_chk_taxability;
621 END IF; --2
622 ELSE
623 CLOSE csr_chk_taxability;
624 END IF;
625
626 /*Added for Bug 11652231*/
627 /*When FIT_SUBJECT and EIC_SUBJECT element input_values are to
628 be created, Taxability Rules are to be verified and based on
629 the l_taxable input_values are to be created. As this taxability
630 check was missing earlier, the input_values were not getting
631 saved for FIT_SUBJECT and EIC_SUBJECT and this resulted in
632 Bug#11652231*/
633
634 ELSIF (p_element_type IN ('FIT_SUBJECT')) THEN
635 hr_utility.set_location(c_proc, 46);
636 OPEN csr_chk_fed_taxability ('FIT');
637 FETCH csr_chk_fed_taxability INTO l_taxable;
638 CLOSE csr_chk_fed_taxability;
639
640 ELSIF (p_element_type IN ('EIC_SUBJECT')) THEN
641 hr_utility.set_location(c_proc, 47);
642 OPEN csr_chk_fed_taxability ('EIC');
643 FETCH csr_chk_fed_taxability INTO l_taxable;
644 CLOSE csr_chk_fed_taxability;
648 ELSIF (p_input_name = 'Subj NWhable') THEN
645 /*Changes for Bug 11652231 Ends*/
646 END IF;
647
649 hr_utility.set_location(c_proc, 50);
650
651 IF (p_element_type = ('SIT_SUBJECT_WK') ) THEN
652 hr_utility.set_location(c_proc, 51);
653 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
654 FETCH csr_chk_taxability INTO l_taxable;
655 CLOSE csr_chk_taxability;
656
657 ELSIF (p_element_type = ('City_SUBJECT_WK') ) THEN
658 hr_utility.set_location(c_proc, 52);
659 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
660 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
661 FETCH csr_chk_taxability INTO l_taxable;
662 -- If the above query returns no rows then check the state level taxablility rule
663 -- as we are checking for SUBJ Nwhable here. If we don't find a row for locality
664 -- subj whable, we must check for SUBJ Whable befor defaulting to state level.
665 -- NOTE currently is does not cover a situation where the specific element type
666 -- is not subject (WHable or NWhable) and the state is Whable.
667 IF csr_chk_taxability%NOTFOUND THEN
668 CLOSE csr_chk_taxability;
669 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
670 FETCH csr_chk_taxability INTO l_taxable;
671 IF csr_chk_taxability%NOTFOUND THEN -- 2
672 -- check for the existance of any taxability rules at this JD level.
673 -- if we get to this point and the csr_chk_all_taxability returns data
674 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
675 CLOSE csr_chk_taxability;
676 OPEN csr_chk_all_taxability (l_jurisdiction_code);
677 FETCH csr_chk_all_taxability INTO l_taxable;
678 IF csr_chk_all_taxability%NOTFOUND THEN --3
679 CLOSE csr_chk_all_taxability;
680 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
681 FETCH csr_chk_taxability INTO l_taxable;
682 CLOSE csr_chk_taxability;
683 ELSE -- 3
684 l_taxable := 'N';
685 CLOSE csr_chk_all_taxability;
686 END IF; -- 3
687 ELSE -- 2
688 l_taxable := 'N';
689 CLOSE csr_chk_taxability;
690 END IF; --2
691 ELSE
692 CLOSE csr_chk_taxability;
693 END IF;
694
695 ELSIF (p_element_type IN ('County_SUBJECT_WK')) THEN
696 hr_utility.set_location(c_proc, 53);
697 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
698 FETCH csr_chk_taxability INTO l_taxable;
699 -- If the above query returns no rows then check the state level taxablility rule
700 -- as we are checking for SUBJ Nwhable here. If we don't find a row for locality
701 -- subj whable, we must check for SUBJ Whable befor defaulting to state level.
702 -- NOTE currently is does not cover a situation where the specific element type
703 -- is not subject (WHable or NWhable) and the state is Whable.
704 IF csr_chk_taxability%NOTFOUND THEN
705 CLOSE csr_chk_taxability;
706 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
707 FETCH csr_chk_taxability INTO l_taxable;
708 IF csr_chk_taxability%NOTFOUND THEN -- 2
709 -- check for the existance of any taxability rules at this JD level.
710 -- if we get to this point and the csr_chk_all_taxability returns data
711 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
712 CLOSE csr_chk_taxability;
713 OPEN csr_chk_all_taxability (g_county_jd);
714 FETCH csr_chk_all_taxability INTO l_taxable;
715 IF csr_chk_all_taxability%NOTFOUND THEN --3
716 CLOSE csr_chk_all_taxability;
717 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
718 FETCH csr_chk_taxability INTO l_taxable;
719 CLOSE csr_chk_taxability;
720 ELSE -- 3
721 l_taxable := 'N';
722 CLOSE csr_chk_all_taxability;
723 END IF; -- 3
724 ELSE -- 2
725 l_taxable := 'N';
726 CLOSE csr_chk_taxability;
727 END IF; --2
728 ELSE
729 CLOSE csr_chk_taxability;
730 END IF;
731
732 /* NEW code for school district processing */
733
734 ELSIF p_element_type = ('School_SUBJECT_WK') THEN
735 -- IF THE STATE JURISDICTION IS OHIO THEN CHECK TAXABLILITY RULES OF THE STATE LEVEL
736 -- ELESE CHECK THE TAXABLILITY RULES OF THE RESPECTIVE CITY OR COUNTY THE SCHOOL
737 -- DISTRICT BELONGS TO.
738 IF SUBSTR(G_city_jd,1,2) = '36' THEN
739 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
740 FETCH csr_chk_taxability INTO l_taxable;
741 CLOSE csr_chk_taxability;
742 ELSE -- state code = 36
743 OPEN csr_get_school_jd_level;
744 fetch csr_get_school_jd_level inTO l_county_sch_dsts;
745 if csr_get_school_jd_level%NOTFOUND THEN
746 l_jurisdiction_code := substr(g_city_jd,1,3) || '000' || substr(g_city_jd,7,5);
747 OPEN csr_chk_taxability ('NW_CITY', l_jurisdiction_code);
748 FETCH csr_chk_taxability INTO l_taxable;
749 -- If the above query returns no rows then check the state level taxablility rule
750 -- as we are checking for SUBJ whable here. If we don't find a row for locality
751 -- subj whable, we must check for subj NWhable befor defaulting to state level.
755 CLOSE csr_chk_taxability;
752 -- NOTE currently is does not cover a situation where the specific element type
753 -- is not subject (WHable or NWhable) and the state is Whable.
754 IF csr_chk_taxability%NOTFOUND THEN
756 OPEN csr_chk_taxability ('CITY', l_jurisdiction_code);
757 FETCH csr_chk_taxability INTO l_taxable;
758 IF csr_chk_taxability%NOTFOUND THEN -- 2
759 -- check for the existance of any taxability rules at this JD level.
760 -- if we get to this point and the csr_chk_all_taxability returns data
761 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
762 CLOSE csr_chk_taxability;
763 OPEN csr_chk_all_taxability (l_jurisdiction_code);
764 FETCH csr_chk_all_taxability INTO l_taxable;
765 IF csr_chk_all_taxability%NOTFOUND THEN --3
766 CLOSE csr_chk_all_taxability;
767 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
768 FETCH csr_chk_taxability INTO l_taxable;
769 CLOSE csr_chk_taxability;
770 ELSE -- 3
771 l_taxable := 'N';
772 CLOSE csr_chk_all_taxability;
773 END IF; -- 3
774 ELSE -- 2
775 l_taxable := 'N';
776 CLOSE csr_chk_taxability;
777 END IF; --2
778 ELSE
779 CLOSE csr_chk_taxability;
780 END IF;
781
782 ELSE -- csr_get_school_jd_level%NOT_FOUND
783 -- row found in cursor so this is a county school district
784 -- check the county TR
785
786 OPEN csr_chk_taxability ('NW_COUNTY', g_county_jd);
787 FETCH csr_chk_taxability INTO l_taxable;
788 -- If the above query returns no rows then check the state level taxablility rule
789 -- as we are checking for SUBJ whable here. If we don't find a row for locality
790 -- subj whable, we must check for subj NWhable befor defaulting to state level.
791 -- NOTE currently is does not cover a situation where the specific element type
792 -- is not subject (WHable or NWhable) and the state is Whable.
793 IF csr_chk_taxability%NOTFOUND THEN
794 CLOSE csr_chk_taxability;
795 OPEN csr_chk_taxability ('COUNTY', g_county_jd);
796 FETCH csr_chk_taxability INTO l_taxable;
797 IF csr_chk_taxability%NOTFOUND THEN -- 2
798 -- check for the existance of any taxability rules at this JD level.
799 -- if we get to this point and the csr_chk_all_taxability returns data
800 -- then we assume that the element is NOT SUBJECT, NOT WITHHELD
801 CLOSE csr_chk_taxability;
802 OPEN csr_chk_all_taxability (g_county_jd);
803 FETCH csr_chk_all_taxability INTO l_taxable;
804 IF csr_chk_all_taxability%NOTFOUND THEN --3
805 CLOSE csr_chk_all_taxability;
806 OPEN csr_chk_taxability ('NW_SIT', g_state_jd);
807 FETCH csr_chk_taxability INTO l_taxable;
808 CLOSE csr_chk_taxability;
809 ELSE -- 3
810 l_taxable := 'N';
811 CLOSE csr_chk_all_taxability;
812 END IF; -- 3
813 ELSE -- 2
814 l_taxable := 'N';
815 CLOSE csr_chk_taxability;
816 END IF; --2
817 ELSE
818 CLOSE csr_chk_taxability;
819 END IF;
820 END IF; -- csr_get_school_jd_level%NOT_FOUND
821
822 CLOSE csr_get_school_jd_level;
823
824 END IF; -- state code = '36'
825
826 /* End of code for school district taxes. */
827
828
829 END IF;
830
831 ELSE
832 hr_utility.set_location(c_proc, 60);
833 -- otherwise we do not need to check taxability_rules
834 -- in order to set the value of the input value,
835 -- NB. that this step gets executed for tax elements like FIT, Medicare
836 -- as well as Tax balance elements like SUI_SUBJECT_EE
837 l_taxable := 'Y';
838 END IF;
839
840 ELSE
841 -- an Earnings Element so no taxability rules
842 hr_utility.set_location(c_proc, 70);
843
844 l_taxable := 'Y';
845
846 END IF;
847
848
849 IF (l_taxable = 'Y') THEN
850 hr_utility.set_location (c_proc, 200);
851
852 p_iv_tbl(p_row) := l_input_value_id;
853 p_iv_names_tbl(p_row) := p_input_name;
854 p_ev_tbl(p_row) := p_entry_value;
855 p_row := p_row + 1; -- next row in plsql table
856 END IF;
857
858 END process_input;
859
860
861
862 PROCEDURE fetch_wage_limits(
863 p_effective_date IN DATE DEFAULT NULL,
864 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
865 p_futa_wage_limit OUT NOCOPY NUMBER,
866 p_ss_ee_wage_limit OUT NOCOPY NUMBER,
867 p_ss_er_wage_limit OUT NOCOPY NUMBER,
868 p_sdi_ee_wage_limit OUT NOCOPY NUMBER,
869 p_sdi1_ee_wage_limit OUT NOCOPY NUMBER,
870 p_sdi_er_wage_limit OUT NOCOPY NUMBER,
871 p_sui_ee_wage_limit OUT NOCOPY NUMBER,
872 p_sui_er_wage_limit OUT NOCOPY NUMBER) IS
876 l_futa_wage_limit NUMBER;
873
874 c_proc VARCHAR2(100) := 'fetch_wage_limits';
875
877 l_ss_ee_wage_limit NUMBER;
878 l_ss_er_wage_limit NUMBER;
879 l_sdi_ee_wage_limit NUMBER;
880 l_sdi1_ee_wage_limit NUMBER;
881 l_sdi_er_wage_limit NUMBER;
882 l_sui_ee_wage_limit NUMBER;
883 l_sui_er_wage_limit NUMBER;
884
885
886 CURSOR csr_get_fed_wage_limits(v_effective_date DATE) IS
887 SELECT ftax.futa_wage_limit,
888 ftax.ss_ee_wage_limit,
889 ftax.ss_er_wage_limit
890 FROM PAY_US_FEDERAL_TAX_INFO_F ftax
891 WHERE v_effective_date BETWEEN ftax.effective_start_date
892 AND ftax.effective_end_date
893 AND ftax.fed_information_category = '401K LIMITS';
894
895
896 CURSOR csr_get_state_wage_limits(v_effective_date DATE,
897 v_state_abbrev VARCHAR2) IS
898 SELECT ti.sdi_ee_wage_limit,
899 ti.sdi_er_wage_limit,
900 ti.sui_ee_wage_limit,
901 ti.sui_er_wage_limit,
902 ti.STA_INFORMATION21
903 FROM PAY_US_STATES st,
904 PAY_US_STATE_TAX_INFO_F ti
905 WHERE v_effective_date BETWEEN
906 ti.effective_start_date AND ti.effective_end_date
907 and st.state_code =
908 ti.state_code
909 and st.state_abbrev = v_state_abbrev
910 ;
911
912
913
914 BEGIN
915 /*
916 ** fetch state level wage limits,
917 ** not all states have sdi/sui ee/er wage limits,
918 ** therefore do not check for success
919 */
920 OPEN csr_get_state_wage_limits(p_effective_date, p_state_abbrev);
921 FETCH csr_get_state_wage_limits INTO
922 l_sdi_ee_wage_limit,
923 l_sdi_er_wage_limit,
924 l_sui_ee_wage_limit,
925 l_sui_er_wage_limit,
926 l_sdi1_ee_wage_limit;
927 CLOSE csr_get_state_wage_limits;
928
929
930
931 /*
932 ** fetch federal level wage limits
933 */
934 OPEN csr_get_fed_wage_limits(p_effective_date);
935 FETCH csr_get_fed_wage_limits INTO
936 l_futa_wage_limit,
937 l_ss_ee_wage_limit,
938 l_ss_er_wage_limit;
939 CLOSE csr_get_fed_wage_limits;
940
941
942 /*
943 ** always expect federal level wage limits,
944 ** if fetch failed then error, inform user
945 */
946 /** stub - find an apppriate error message **/
947 IF (l_futa_wage_limit IS NULL OR
948 l_ss_ee_wage_limit IS NULL OR
949 l_ss_er_wage_limit IS NULL) THEN
950 hr_utility.set_location(c_proc, 10);
951 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
952 hr_utility.raise_error;
953 END IF;
954
955
956 /*
957 ** copy limits INTO return parameters
958 */
959 p_futa_wage_limit := l_futa_wage_limit;
960 p_ss_ee_wage_limit := l_ss_ee_wage_limit;
961 p_ss_er_wage_limit := l_ss_er_wage_limit;
962 p_sdi_ee_wage_limit := l_sdi_ee_wage_limit;
963 p_sdi1_ee_wage_limit := l_sdi1_ee_wage_limit;
964 p_sdi_er_wage_limit := l_sdi_er_wage_limit;
965 p_sui_ee_wage_limit := l_sui_ee_wage_limit;
966 p_sui_er_wage_limit := l_sui_er_wage_limit;
967
968 END fetch_wage_limits;
969
970
971 /* NOTE: Though the code still resides here for MEDICARE EE and
972 MEDICARE ER we will not call the process_limits procedure for
973 those elements
974 */
975
976 PROCEDURE process_limits(
977 p_element_type IN VARCHAR2,
978 p_earn_amount IN NUMBER,
979 p_iv_tbl IN Hr_Entry.number_table,
980 p_iv_names_tbl IN Hr_Entry.varchar2_table,
981 p_ev_tbl IN OUT NOCOPY Hr_Entry.varchar2_table,
982 p_num_ev IN NUMBER,
983 p_assignment_id IN NUMBER,
984 p_jurisdiction IN VARCHAR2,
985 p_tax_unit_id IN VARCHAR2,
986 p_adjustment_date IN DATE) IS
987
988 c_proc VARCHAR2(100) := 'process_limits';
989
990 l_return_bal VARCHAR2(30);
991 l_adj_amt NUMBER;
992 l_excess NUMBER;
993 l_taxable_iv_pos NUMBER := 0;
994 l_old_taxable_bal NUMBER;
995 l_limit NUMBER;
996 l_asg_type VARCHAR2(6) := 'PER';
997
998 l_virtual_adjustment_date date;
999 l_limit_subject_bal number:=0;
1000 BEGIN
1001
1002 FOR l_i IN 1..(p_num_ev - 1) LOOP
1003 FOR l_j IN 1..1000 LOOP
1004 NULL;
1005 END LOOP;
1006 END LOOP;
1007
1008 /*
1009 ** find position of TAXABLE IV in tbl structure
1010 */
1011 FOR l_i IN 1..(p_num_ev - 1) LOOP
1012 if p_element_type = 'SDI1_EE' THEN
1013 IF (p_iv_names_tbl(l_i) = 'Taxable') THEN
1014 l_taxable_iv_pos := l_i;
1015 END IF;
1016 else
1017 IF (p_iv_names_tbl(l_i) = 'TAXABLE') THEN
1018 l_taxable_iv_pos := l_i;
1019 END IF;
1020 end if;
1021 END LOOP;
1022
1023 /*
1024 ** set up taxable balance and limit for limit processing
1025 */
1026
1027 /* Rmonge 17-NOV-2001 */
1028 /* For each IF statment to get the taxable balance, I have added
1029 a call to PAY_US_TAX_BALS_PKG.US_TAX_BALANCE. The package is going to
1033 /* TCLEWIS 02-25-2002
1030 return the Adjusted Subject To Tax Balance for the element being
1031 processed.
1032 */
1034 In our fetches of reduced_subj_whable we must fetch the balance as of
1035 the end of the year.
1036 */
1037
1038 l_virtual_adjustment_date := add_months(trunc(p_adjustment_date,'Y'),12) -1;
1039 /*l_virtual_adjustment_date for bug 4721086*/
1040
1041 IF (p_element_type = 'Medicare_EE') THEN
1042 l_old_taxable_bal := g_medicare_ee_taxable;
1043 /*
1044 ** Medicare EE and ER should have an infinite limit,
1045 ** at a later stage a legislative limit may be defined,
1046 ** therefore set to an arbitary value (99,999,999),
1047 ** as used in PAY_US_STATE_TAX_INFO_F for NY
1048 */
1049 l_limit := 99999999;
1050
1051 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1052 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1053 p_tax_type => 'MEDICARE',
1054 p_ee_or_er => 'EE',
1055 p_time_type => 'YTD',
1056 p_asg_type => l_asg_type,
1057 p_gre_id_context => p_tax_unit_id,
1058 p_jd_context => p_jurisdiction,
1059 p_assignment_action_id => NULL,
1060 p_assignment_id => p_assignment_id,
1061 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1062
1063 ELSIF (p_element_type = 'Medicare_ER') THEN
1064 l_old_taxable_bal := g_medicare_er_taxable;
1065 l_limit := 99999999;
1066
1067 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1068 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1069 p_tax_type => 'MEDICARE',
1070 p_ee_or_er => 'ER',
1071 p_time_type => 'YTD',
1072 p_asg_type => l_asg_type,
1073 p_gre_id_context => p_tax_unit_id,
1074 p_jd_context => p_jurisdiction,
1075 p_assignment_action_id => NULL,
1076 p_assignment_id => p_assignment_id,
1077 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1078
1079 ELSIF (p_element_type = 'FUTA') THEN
1080
1081 l_old_taxable_bal := g_futa_taxable;
1082
1083 l_limit := g_futa_wage_limit;
1084 if g_tax_group <> 'NOT_ENTERED' Then
1085 l_asg_type := 'PER';
1086 -- l_asg_type := 'PER_TG';
1087 else
1088 l_asg_type := 'PER';
1089 end if;
1090
1091 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1092 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1093 p_tax_type => 'FUTA',
1094 p_ee_or_er => 'ER',
1095 p_time_type => 'YTD',
1096 p_asg_type => l_asg_type,
1097 p_gre_id_context => p_tax_unit_id,
1098 p_jd_context => p_jurisdiction,
1099 p_assignment_action_id => NULL,
1100 p_assignment_id => p_assignment_id,
1101 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1102
1103 ELSIF (p_element_type = 'SS_EE') THEN
1104 l_old_taxable_bal := g_ss_ee_taxable;
1105 l_limit := g_ss_ee_wage_limit;
1106
1107 if g_tax_group <> 'NOT_ENTERED' Then
1108 l_asg_type := 'PER';
1109 -- l_asg_type := 'PER_TG';
1110 else
1111 l_asg_type := 'PER';
1112 end if;
1113
1114 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1115 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1116 p_tax_type => 'SS',
1117 p_ee_or_er => 'EE',
1118 p_time_type => 'YTD',
1119 p_asg_type => l_asg_type,
1120 p_gre_id_context => p_tax_unit_id,
1121 p_jd_context => p_jurisdiction,
1122 p_assignment_action_id => NULL,
1123 p_assignment_id => p_assignment_id,
1124 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1125
1126 ELSIF (p_element_type in ( 'SS_ER') ) THEN
1127 l_old_taxable_bal := g_ss_er_taxable;
1128 l_limit := g_ss_er_wage_limit;
1129
1130 if g_tax_group <> 'NOT_ENTERED' Then
1131 l_asg_type := 'PER';
1132 -- l_asg_type := 'PER_TG';
1133 else
1134 l_asg_type := 'PER';
1135 end if;
1136
1137 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1138 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1139 p_tax_type => 'SS',
1140 p_ee_or_er => 'ER',
1141 p_time_type => 'YTD',
1142 p_asg_type => l_asg_type,
1143 p_gre_id_context => p_tax_unit_id,
1144 p_jd_context => p_jurisdiction,
1145 p_assignment_action_id => NULL,
1149 ELSIF (p_element_type = 'SDI_EE') THEN
1146 p_assignment_id => p_assignment_id,
1147 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1148
1150 l_old_taxable_bal := g_sdi_ee_taxable;
1151 l_limit := g_sdi_ee_wage_limit;
1152
1153 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1154 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1155 p_tax_type => 'SDI',
1156 p_ee_or_er => 'EE',
1157 p_time_type => 'YTD',
1158 p_asg_type => 'PER',
1159 p_gre_id_context => p_tax_unit_id,
1160 p_jd_context => p_jurisdiction,
1161 p_assignment_action_id => NULL,
1162 p_assignment_id => p_assignment_id,
1163 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1164
1165 ELSIF (p_element_type = 'SDI1_EE') THEN
1166 l_old_taxable_bal := g_sdi1_ee_taxable;
1167 l_limit := g_sdi1_ee_wage_limit;
1168
1169 -- USE SDI EE Reduced Subject Whable as we don't have a subject balance for SDI1
1170
1171 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1172 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1173 p_tax_type => 'SDI',
1174 p_ee_or_er => 'EE',
1175 p_time_type => 'YTD',
1176 p_asg_type => 'PER',
1177 p_gre_id_context => p_tax_unit_id,
1178 p_jd_context => p_jurisdiction,
1179 p_assignment_action_id => NULL,
1180 p_assignment_id => p_assignment_id,
1181 p_virtual_date => l_virtual_adjustment_date);
1182
1183 ELSIF (p_element_type = 'SDI_ER') THEN
1184 l_old_taxable_bal := g_sdi_er_taxable;
1185 l_limit := g_sdi_er_wage_limit;
1186
1187 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1188 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1189 p_tax_type => 'SDI',
1190 p_ee_or_er => 'ER',
1191 p_time_type => 'YTD',
1192 p_asg_type => 'PER',
1193 p_gre_id_context => p_tax_unit_id,
1194 p_jd_context => p_jurisdiction,
1195 p_assignment_action_id => NULL,
1196 p_assignment_id => p_assignment_id,
1197 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1198
1199 ELSIF (p_element_type = 'SUI_EE') THEN
1200 l_old_taxable_bal := g_sui_ee_taxable;
1201 l_limit := g_sui_ee_wage_limit;
1202
1203 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1204 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1205 p_tax_type => 'SUI',
1206 p_ee_or_er => 'EE',
1207 p_time_type => 'YTD',
1208 p_asg_type => 'PER',
1209 p_gre_id_context => p_tax_unit_id,
1210 p_jd_context => p_jurisdiction,
1211 p_assignment_action_id => NULL,
1212 p_assignment_id => p_assignment_id,
1213 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1214
1215 ELSIF (p_element_type = 'SUI_ER') THEN
1216 l_old_taxable_bal := g_sui_er_taxable;
1217 l_limit := g_sui_er_wage_limit;
1218
1219 l_limit_subject_bal:= pay_us_tax_bals_pkg.us_tax_balance(
1220 p_tax_balance_category => 'REDUCED_SUBJ_WHABLE',
1221 p_tax_type => 'SUI',
1222 p_ee_or_er => 'ER',
1223 p_time_type => 'YTD',
1224 p_asg_type => 'PER',
1225 p_gre_id_context => p_tax_unit_id,
1226 p_jd_context => p_jurisdiction,
1227 p_assignment_action_id => NULL,
1228 p_assignment_id => p_assignment_id,
1229 p_virtual_date => l_virtual_adjustment_date); --Bug3697701
1230 ELSE
1231 /** stub - find appropriate message **/
1232 hr_utility.set_location(c_proc, 10);
1233 hr_utility.set_message(801, 'PY_50014_TXADJ_IV_ID_NOT_FOUND');
1234 hr_utility.raise_error;
1235
1236 END IF;
1237
1238
1239 /*
1240 ** generic block, applies to all limit processing
1241 ** Excess is never passed or adjusted as it is a derived balance
1242 */
1243
1244 hr_utility.trace('P_earn_amount='||to_char(p_earn_amount));
1245 hr_utility.trace('subject balance = ' || to_char(l_limit_subject_bal));
1246
1247 IF ((l_old_taxable_bal + p_earn_amount) <= l_limit) THEN
1248
1249 /*
1250 ** no limit exceeded,
1251 ** ok to make the balance adjustment,
1252 ** do nothing with EV amount of TAXABLE IV
1253 */
1254 /* Rosie Monge 14-NOV-2001 */
1255
1256 /* if the p_earn_amount (adjustment amount made ) is Negative
1257 we need to account for 3 different possibilities.
1258 1) Subject Taxable Balance is grater than the limit (7000)
1259 In this scenario, The balance after the Adjustment is made
1260 is grater than the Limit, so it is not necessary to adjust
1261 the amount, because it is at its maximun already.
1262
1263 2) Subject Taxable Balance is between the limit (0 -7000)
1264 If the Adjusted Subject Balance is between the limit, then,
1265 it is necessary to calculate how much the adjustment will be.
1266 This amount is the Limit_Subject_Balance - limit (7000).
1267 3) Subject Taxable Balance is Negative (less than 0).
1268 If the Subject Taxable Balance is Negative, then, we have to
1269 substract the entire balance, so that we make it 0.
1270 */
1271
1272 /* note the limit subject balance has already been adjusted for
1273 the gross earnings element has been processed.
1274 */
1275
1276
1277 if p_earn_amount < 0 then -- negative adjustment reguires special
1278 -- attentions.
1279
1280 if ( l_limit_subject_bal ) >= l_limit then
1281
1282 l_adj_amt := 0;
1283
1284 elsif (l_limit_subject_bal ) >= 0 and
1285 (l_limit_subject_bal ) < l_limit then
1286
1287
1288 if (l_limit_subject_bal - p_earn_amount) <> l_old_taxable_bal then
1289
1290 if (l_limit_subject_bal - p_earn_amount) < l_limit then
1291 /* subject balance is below the limit and not = to taxable
1292 make adjustment on the taxable balance and ignore the
1293 subject balance
1294 */
1295 if l_old_taxable_bal - l_adj_amt < 0 then
1296 /* if the amount of the adjustment is greater that taxbale
1297 the adjust taxable to 0
1298 */
1299 l_adj_amt := l_old_taxable_bal * -1;
1300 else
1301 /* The taxable balance + the adjustment (which is negative)
1302 will not = 0, to take full amount of the adjustment
1303 */
1304 l_adj_amt := p_earn_amount;
1305 end if;
1306 else
1307 /* subject is over the limit so adjust taxable based on subject
1308 balance
1309 */
1310 l_adj_amt := (l_limit_subject_bal ) - l_limit;
1311
1312 /* check to make sure that the adjustment amount will
1313 not cause taxable to go negative. If this occurs
1314 then adjust taxable to 0 (zero)
1315 */
1316 if l_old_taxable_bal - l_adj_amt < 0 then
1317 /* if the amount of the adjustment is greater that taxbale
1318 the adjust taxable to 0
1319 */
1320 l_adj_amt := l_old_taxable_bal * -1;
1321 end if;
1322 end if;
1323 else
1324 /* is subject is below the limit then the adjustment should be ok
1325 */
1326
1327 l_adj_amt := p_earn_amount;
1328
1329 end if;
1330
1331 elsif (l_limit_subject_bal < 0 ) then
1332
1333 l_adj_amt := l_old_taxable_bal * -1;
1334
1335 end if;
1336 p_ev_tbl(l_taxable_iv_pos) :=
1337 fnd_number.number_to_canonical(l_adj_amt);
1338
1339 end if;
1340
1341 ELSIF ((l_old_taxable_bal > l_limit) or
1342 ((l_old_taxable_bal + p_earn_amount) < 0 )) THEN
1343
1344 /*
1345 ** taxable balance already exceeds limit or if sum of old and
1346 ** adj amount is -ve, set EV amount of TAXABLE IV to 0,
1347 ** therefore the EV amount feeds Excess
1348 ** put EV amount of TAXABLE IV INTO excess
1349 */
1350 p_ev_tbl(l_taxable_iv_pos) := 0;
1351
1352 ELSIF (l_old_taxable_bal + p_earn_amount > l_limit) THEN
1353 /*
1354 ** EV amount of TAXABLE IV will cause limit to be exceeded,
1355 ** set EV amount up to limit
1356 */
1357
1358 hr_utility.trace('in the elsif l_old_tax_amount + p_earn_amount > 0');
1359
1360 l_adj_amt := l_limit - l_old_taxable_bal;
1361 hr_utility.trace('l_adj_amt = '||to_char(l_adj_amt));
1362
1363 l_excess := (p_earn_amount + l_old_taxable_bal) - l_limit;
1364 hr_utility.trace('l_excess ='|| to_char(l_excess));
1365 /*
1366 ** modify EV amount of TAXABLE IV before BA processing,
1367 ** set EV amount up to limit, remainder goes INTO excess
1368 */
1369 p_ev_tbl(l_taxable_iv_pos) := fnd_number.number_to_canonical(l_adj_amt);
1370
1371 END IF;
1372
1373 END process_limits;
1374
1375
1376
1377 PROCEDURE process_element(
1378 p_assignment_id IN NUMBER,
1379 p_consolidation_set_id IN NUMBER,
1380 p_element_type IN VARCHAR2,
1381 p_abbrev_element_type IN VARCHAR2,
1382 p_bg_id IN NUMBER,
1383 p_adjustment_date IN DATE,
1384 p_earn_amount IN NUMBER,
1385 p_adj_amount IN NUMBER,
1386 p_jurisdiction IN VARCHAR2,
1387 p_payroll_action_id IN NUMBER,
1388 p_tax_unit_id IN VARCHAR2,
1389 p_balance_adj_costing_flag IN VARCHAR2
1390 ) IS
1391
1392 c_proc VARCHAR2(100) := 'process_element';
1393
1394 -- p_abbrev_element_type - shorter name for the element,
1395 -- used to ensure that the group key for all the adjustments
1396 -- does not exceed 240 chars (assuming that the
1397 -- length of payroll_action_id <= 7
1398 -- p_earn_amount - gross earnings. i.e. p_gross_amount
1399 -- p_adj_amount - amount of the tax withheld
1400 -- p_jurisdiction - jd where the tax was withheld
1401
1402 CURSOR csr_element IS
1403 SELECT e.element_type_id,
1404 c.classification_name,
1405 e.element_information_category earnings_lookup_type,
1406 e.classification_id,
1407 e.element_information1 earnings_category
1408 FROM PAY_ELEMENT_CLASSIFICATIONS c,
1409 PAY_ELEMENT_TYPES_F e,
1410 hr_organization_information hoi
1411 WHERE e.element_name = p_element_type
1412 AND (e.business_group_id = p_bg_id
1413 OR e.business_group_id IS NULL
1414 )
1415 AND e.classification_id = c.classification_id
1416 AND p_adjustment_date BETWEEN
1417 e.effective_start_date AND e.effective_end_date
1418 AND hoi.organization_id = p_bg_id
1419 AND hoi.org_information_context = 'Business Group Information'
1420 AND c.legislation_code = hoi.org_information9
1421 ;
1422
1423 CURSOR csr_set_mandatory_inputs (v_element_type_id NUMBER) IS
1424 SELECT i.name INPUT_NAME,
1425 i.input_value_id,
1426 NVL(hr.meaning, NVL(i.default_value,
1427 DECODE(i.uom,
1428 'I', '0',
1429 'M', '0',
1430 'N', '0',
1431 'T', '0',
1432 'C', 'Unknown - US_TAX_BAL_ADJ',
1433 'H_DECIMAL1', '0.0',
1434 'H_DECIMAL2', '0.00',
1435 'H_DECIMAL3', '0.000',
1436 'H_HH', '12',
1437 'H_HHMM', '12:00',
1438 'H_HHMMSS', '12:00:00',
1439 'D', fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_adjustment_date)),
1440 'ND', To_Char(p_adjustment_date, 'Day')))
1441 ) default_value
1442 FROM HR_LOOKUPS hr,
1443 PAY_INPUT_VALUES_F i
1444 WHERE i.element_type_id = v_element_type_id
1445 AND i.mandatory_flag = 'Y'
1446 AND i.default_value = hr.lookup_code (+)
1447 AND i.lookup_type = hr.lookup_type (+)
1448 AND i.name NOT IN ('Pay Value')
1449 ;
1450
1451 l_iv_tbl hr_entry.number_table;
1452 l_iv_names_tbl hr_entry.varchar2_table;
1453 l_ev_tbl hr_entry.varchar2_table;
1454 l_num_ev NUMBER;
1455 l_element csr_element%ROWTYPE;
1456 l_ele_link_id NUMBER;
1457 l_counter NUMBER;
1458 l_payroll_action_id NUMBER;
1459
1460 /* Bug#9796821 - Starts */
1461 l_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
1462 l_input_value_id pay_input_values_f.input_value_id%TYPE := NULL;
1463 l_medi_ee_limit1 NUMBER := 0;
1464 l_medicare_taxability VARCHAR2(1) := NULL;
1465 l_excess NUMBER := 0;
1466 l_value NUMBER := 0;
1467 /* Bug#9796821 - Ends */
1468
1469 BEGIN
1470
1471 hr_utility.trace('IN Process_element Element_type ='||p_element_type);
1472 HR_Utility.trace('Abbrev Element Type ='||p_abbrev_element_type);
1473
1474 hr_utility.set_location(c_proc, 10);
1475 OPEN csr_element;
1476 FETCH csr_element INTO l_element;
1477 CLOSE csr_element;
1478
1479 IF (l_element.element_type_id IS NULL) THEN
1480 hr_utility.set_location(c_proc, 20);
1481 hr_utility.set_message(801, 'HR_6884_ELE_ENTRY_NO_ELEMENT');
1482 hr_utility.raise_error;
1483 END IF;
1484
1485 hr_utility.set_location(c_proc, 30);
1486 l_ele_link_id := hr_entry_api.get_link(
1487 p_assignment_id => p_assignment_id,
1488 p_element_type_id => l_element.element_type_id,
1489 p_session_date => p_adjustment_date);
1490
1491 IF (l_ele_link_id IS NULL) THEN
1492 hr_utility.set_location(c_proc, 40);
1493 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1494 hr_utility.set_message_token ('ELEMENT', p_element_type);
1495 hr_utility.raise_error;
1496 END IF;
1497
1498 -- initialize tables
1499 l_iv_names_tbl := g_dummy_varchar_tbl;
1500 l_iv_tbl := g_dummy_number_tbl;
1501 l_ev_tbl := g_dummy_varchar_tbl;
1502 l_num_ev := 1;
1503
1504 -- explicitly set the various input values,
1505 -- this clearly identifies which input values are expected and will cause failure
1506 -- if the input value has been deleted somehow
1507 hr_utility.set_location(c_proc, 50);
1508
1509 IF (l_element.classification_name IN ('Earnings', 'Imputed Earnings',
1510 'Supplemental Earnings')) THEN
1511 -- element is an Earnings element,
1512 -- populate the global tables to be used later for taxability checking for
1513 -- subject withholdable, not-withholdable input values of tax balance elements
1514 g_classification_id := l_element.classification_id;
1515 g_earnings_category := l_element.earnings_category;
1516 g_classification := l_element.classification_name;
1517
1518 process_input(p_element_type, l_element.element_type_id,
1519 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1520 p_bg_id, p_adjustment_date,
1521 'Pay Value', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1522
1523 ELSIF (p_element_type IN ('FIT')) THEN
1524 hr_utility.set_location (c_proc, 60);
1525 process_input(p_element_type, l_element.element_type_id,
1526 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1527 p_bg_id, p_adjustment_date,
1528 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1529
1530 IF (g_classification = 'Supplemental Earnings') THEN
1531 process_input(p_element_type, l_element.element_type_id,
1532 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1533 p_bg_id, p_adjustment_date,
1534 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1535 END IF;
1536 -- 4188782
1537 ELSIF (p_element_type IN ('FSP_SUBJECT')) THEN
1538 hr_utility.set_location (c_proc, 62);
1539 process_input(p_element_type, l_element.element_type_id,
1540 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1541 p_bg_id, p_adjustment_date,
1542 'Reduced Subj Whable',
1543 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1544 /*Added for Bug#9872952*/
1545 ELSIF (p_element_type IN ('FIT_SUBJECT','EIC_SUBJECT','SS_SUBJECT_EE','SS_SUBJECT_ER',
1546 'MEDICARE_SUBJECT_EE','MEDICARE_SUBJECT_ER','FUTA_SUBJECT')) THEN
1547 hr_utility.set_location (c_proc, 63);
1548 process_input(p_element_type, l_element.element_type_id,
1549 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1550 p_bg_id, p_adjustment_date,
1551 'Gross',
1552 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1553
1554 process_input(p_element_type, l_element.element_type_id,
1555 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1556 p_bg_id, p_adjustment_date,
1557 'Subj Whable',
1558 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1559 /*End Bug#9872952*/
1560 ELSIF (p_element_type IN ('FIT 3rd Party')) THEN
1561 hr_utility.set_location (c_proc, 65);
1562 process_input(p_element_type, l_element.element_type_id,
1563 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1564 p_bg_id, p_adjustment_date,
1565 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1566
1567 ELSIF (p_element_type IN ('SS_EE', 'Medicare_EE')) THEN
1568 hr_utility.set_location(c_proc, 71);
1569 IF (p_adj_amount <> 0) THEN
1570 process_input(p_element_type, l_element.element_type_id,
1571 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1572 p_bg_id, p_adjustment_date,
1573 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1574 END IF;
1575
1576 hr_utility.set_location(c_proc, 72);
1577 process_input(p_element_type, l_element.element_type_id,
1578 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1579 p_bg_id, p_adjustment_date,
1580 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1581
1582 /*
1583 ** cap the EV amount for the TAXABLE IV if necessary
1584 */
1585
1586 /* MEDICARE EE has no limit */
1587 IF p_element_type = 'SS_EE' THEN
1588 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1589 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1590 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1591 END IF;
1592
1593
1594 -- SD1
1595 ELSIF (p_element_type IN ('Medicare_ER', 'SS_ER', 'SS_ER_W11', 'FUTA')) THEN
1596 /** sbilling **/
1597 /*
1598 ** only if processing Medicare_ER, SS_ER or FUTA, the Pay Value should be set
1599 ** to the corresponding field on the TBA form (Medicare, FUTA_ER or SS),
1600 */
1601 IF (p_adj_amount <> 0) THEN
1602 process_input(p_element_type, l_element.element_type_id,
1603 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1604 p_bg_id, p_adjustment_date,
1605 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1606 END IF;
1607
1608 process_input(p_element_type, l_element.element_type_id,
1609 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1610 p_bg_id, p_adjustment_date,
1611 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1612
1613 /*
1614 ** cap the EV amount for the TAXABLE IV if necessary
1615 */
1616
1617 /* MEDICARE EE has no limit */
1618 IF (p_element_type IN ( 'SS_ER', 'FUTA')) THEN
1619 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1620 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1621 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1622 END IF;
1623
1624 ELSIF (p_element_type IN ('SIT_WK')) THEN
1625 hr_utility.set_location(c_proc, 81);
1626
1627 process_input(p_element_type, l_element.element_type_id,
1628 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1629 p_bg_id, p_adjustment_date,
1630 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1631 hr_utility.set_location(c_proc, 82);
1632
1633 process_input(p_element_type, l_element.element_type_id,
1634 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1635 p_bg_id, p_adjustment_date,
1636 'Jurisdiction', p_jurisdiction, l_num_ev);
1637
1638 IF (g_classification = 'Supplemental Earnings') THEN
1639 process_input(p_element_type, l_element.element_type_id,
1640 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1641 p_bg_id, p_adjustment_date,
1642 'Supp Tax', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1643 END IF;
1644
1645 ELSIF (p_element_type IN ('SIT_WK_NON_AGGREGATE_RED_SUBJ_WHABLE')) THEN
1646 hr_utility.set_location (c_proc, 84);
1647 process_input(p_element_type, l_element.element_type_id,
1648 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1649 p_bg_id, p_adjustment_date,
1650 'SuppGross',
1651 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1652
1653 process_input(p_element_type, l_element.element_type_id,
1654 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1655 p_bg_id, p_adjustment_date,
1656 'Jurisdiction', p_jurisdiction, l_num_ev);
1657
1658 ELSIF (p_element_type IN ('FIT_NON_AGGREGATE_RED_SUBJ_WHABLE')) THEN
1659 hr_utility.set_location (c_proc, 84);
1660 process_input(p_element_type, l_element.element_type_id,
1661 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1662 p_bg_id, p_adjustment_date,
1663 'SuppGross',
1664 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1665
1666 /** sbilling **/
1667 /*
1668 ** new tax element to be processed, use SIT_WK as a template
1669 */
1670 ELSIF (p_element_type IN ('County_SC_WK', 'City_PSD_SC_RS')) THEN
1671 hr_utility.set_location(c_proc, 86);
1672
1673 process_input(p_element_type, l_element.element_type_id,
1674 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1675 p_bg_id, p_adjustment_date,
1676 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1677 hr_utility.set_location(c_proc, 87);
1678
1679
1680 /*
1681 ** can't put the Gross for the BA INTO the Gross for the school district tax,
1682 ** County_SC_WK has no TAXABLE input
1683 */
1684 process_input(p_element_type, l_element.element_type_id,
1685 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1686 p_bg_id, p_adjustment_date,
1687 'Jurisdiction', p_jurisdiction, l_num_ev);
1688
1689 ELSIF (p_element_type IN ('SUI_EE', 'SDI_EE', 'SDI1_EE')) THEN
1690 hr_utility.set_location(c_proc, 91);
1691
1692 IF (p_adj_amount <> 0) THEN
1693 process_input(p_element_type, l_element.element_type_id,
1694 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1695 p_bg_id, p_adjustment_date,
1696 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1697 hr_utility.set_location(c_proc, 915);
1698 END IF;
1699
1700 hr_utility.set_location(c_proc, 92);
1701
1702 if p_element_type = 'SDI1_EE' then
1703 process_input(p_element_type, l_element.element_type_id,
1704 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1705 p_bg_id, p_adjustment_date,
1706 'Taxable',
1707 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1708 else
1709 process_input(p_element_type, l_element.element_type_id,
1710 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1711 p_bg_id, p_adjustment_date,
1712 'TAXABLE',
1713 fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1714 end if;
1715
1716
1717 hr_utility.set_location(c_proc, 93);
1718
1719 process_input(p_element_type, l_element.element_type_id,
1720 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1721 p_bg_id, p_adjustment_date,
1722 'Jurisdiction', p_jurisdiction, l_num_ev);
1723
1724 /*
1725 ** cap the EV amount for the TAXABLE EV if necessary
1726 */
1727 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1728 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1729 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1730
1731 /* Bug 12694875 starts */
1732 -- Extract the value of SUI EE Taxable amount from Pl/sql table
1733 IF (p_element_type = 'SUI_EE') THEN
1734 hr_utility.set_location (c_proc, 94);
1735 FOR i IN 1..(l_num_ev - 1) LOOP
1736 IF l_iv_names_tbl(i) = 'TAXABLE' THEN
1737 g_sui_ee_tax_adj_amt := l_ev_tbl(i);
1738 END IF;
1739 END LOOP;
1740 END IF;
1741 /* Bug 12694875 ends */
1742
1743 ELSIF (p_element_type IN ('City_WK', 'County_WK', 'City_PSD_WK', 'City_PSD_RS')) THEN
1744 hr_utility.set_location(c_proc, 101);
1745
1746 process_input(p_element_type, l_element.element_type_id,
1747 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1748 p_bg_id, p_adjustment_date,
1749 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1750 hr_utility.set_location(c_proc, 102);
1751
1752 process_input(p_element_type, l_element.element_type_id,
1753 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1754 p_bg_id, p_adjustment_date,
1755 'Jurisdiction', p_jurisdiction, l_num_ev);
1756
1757 ELSIF (p_element_type IN ('SIT_SUBJECT_WK', 'City_SUBJECT_WK',
1758 'County_SUBJECT_WK', 'School_SUBJECT_WK',
1759 'City_PSD_SUBJECT_WK', 'City_PSD_SUBJECT_RS')) THEN
1760 hr_utility.set_location(c_proc, 111);
1761
1762 process_input(p_element_type, l_element.element_type_id,
1763 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1764 p_bg_id, p_adjustment_date,
1765 'Jurisdiction', p_jurisdiction, l_num_ev);
1766 hr_utility.set_location(c_proc, 112);
1767
1768 IF (p_element_type not in ( 'City_PSD_SUBJECT_WK', 'City_PSD_SUBJECT_RS' )) THEN
1769
1770 process_input(p_element_type, l_element.element_type_id,
1771 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1772 p_bg_id, p_adjustment_date,
1773 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1774 hr_utility.set_location(c_proc, 113);
1775 END IF;
1776
1777 process_input(p_element_type, l_element.element_type_id,
1778 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1779 p_bg_id, p_adjustment_date,
1780 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1781 hr_utility.set_location(c_proc, 114);
1782
1783 IF (g_classification IN ('Imputed Earnings',
1784 'Supplemental Earnings')
1785 AND p_element_type NOT IN ('City_PSD_SUBJECT_WK', 'City_PSD_SUBJECT_RS' )) THEN
1786 hr_utility.set_location(c_proc, 115);
1787
1788 process_input (p_element_type, l_element.element_type_id,
1789 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1790 p_bg_id, p_adjustment_date,
1791 'Subj NWhable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1792 END IF;
1793
1794 ELSIF (p_element_type IN ('SDI_SUBJECT_EE', 'SDI_SUBJECT_ER',
1795 'SUI_SUBJECT_EE', 'SUI_SUBJECT_ER')) THEN
1796 hr_utility.set_location(c_proc, 121);
1797
1798 process_input(p_element_type, l_element.element_type_id,
1799 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1800 p_bg_id, p_adjustment_date,
1801 'Jurisdiction', p_jurisdiction, l_num_ev);
1802 hr_utility.set_location(c_proc, 122);
1803
1804 process_input(p_element_type, l_element.element_type_id,
1805 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1806 p_bg_id, p_adjustment_date,
1807 'Gross', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1808 hr_utility.set_location(c_proc, 123);
1809
1810 process_input(p_element_type, l_element.element_type_id,
1811 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1812 p_bg_id, p_adjustment_date,
1813 'Subj Whable', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1814
1815 ELSIF (p_element_type IN ('SUI_ER', 'SDI_ER')) THEN
1816 hr_utility.set_location (c_proc, 124);
1817
1818
1819 /** sbilling **/
1820 /*
1821 ** for SUI_ER and SDI_ER set the amount to be paid for tax equal
1822 ** to the amount entered on the corresponding ER field
1823 */
1824 IF (p_adj_amount <> 0) THEN
1825 process_input(p_element_type, l_element.element_type_id,
1826 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1827 p_bg_id, p_adjustment_date,
1828 'Pay Value', fnd_number.number_to_canonical(p_adj_amount), l_num_ev);
1829 END IF;
1830
1831 process_input(p_element_type, l_element.element_type_id,
1832 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1833 p_bg_id, p_adjustment_date,
1834 'Jurisdiction', p_jurisdiction, l_num_ev);
1835
1836 process_input(p_element_type, l_element.element_type_id,
1837 l_iv_tbl, l_iv_names_tbl, l_ev_tbl,
1838 p_bg_id, p_adjustment_date,
1839 'TAXABLE', fnd_number.number_to_canonical(p_earn_amount), l_num_ev);
1840
1841 /*
1842 ** cap the EV amount for the TAXABLE IV if necessary
1843 */
1844 process_limits(p_element_type, p_earn_amount, l_iv_tbl,
1845 l_iv_names_tbl, l_ev_tbl, l_num_ev,p_assignment_id,
1846 p_jurisdiction,p_tax_unit_id,p_adjustment_date);
1847
1848 /* Bug 12694875 starts */
1849 -- Extract the value of SUI ER Taxable amount from Pl/sql table
1850 IF (p_element_type = 'SUI_ER') THEN
1851 hr_utility.set_location (c_proc, 125);
1852 FOR i IN 1..(l_num_ev - 1) LOOP
1853 IF l_iv_names_tbl(i) = 'TAXABLE' THEN
1854 g_sui_er_tax_adj_amt := l_ev_tbl(i);
1855 END IF;
1856 END LOOP;
1857 END IF;
1858 /* Bug 12694875 ends */
1859
1860 END IF;
1861
1862 -- because process_input will increment l_num_ev if it is successful
1863 l_num_ev := l_num_ev - 1;
1864
1865
1866 -- set mandatory input values,
1867 -- cannot set these to NULL, core package expects mandatory values to be entered
1868 hr_utility.set_location(c_proc, 130);
1869
1870 FOR l_req_input IN csr_set_mandatory_inputs (l_element.element_type_id) LOOP
1871 -- first, check if the mandatory input value was explicitly
1872 -- set above, do nothing in this case
1873 hr_utility.set_location(c_proc, 140);
1874
1875 FOR l_counter IN 1..l_num_ev LOOP
1876
1877 IF (l_req_input.input_name = l_iv_names_tbl(l_counter)) THEN
1878 NULL;
1879 ELSE
1880 -- then the input value was not previously set by one of the
1881 -- process_inputs called in process_elements
1882 hr_utility.set_location(c_proc, 150);
1883 l_num_ev := l_num_ev + 1;
1884
1885 l_iv_tbl(l_num_ev) := l_req_input.input_value_id;
1886 l_iv_names_tbl(l_num_ev) := l_req_input.input_name;
1887 l_ev_tbl(l_num_ev) := l_req_input.default_value;
1888 END IF;
1889
1890 END LOOP;
1891 END LOOP;
1892
1893 hr_utility.set_location(c_proc, 160);
1894
1895 pay_bal_adjust.adjust_balance(p_batch_id => p_payroll_action_id,
1896 p_assignment_id => p_assignment_id,
1897 p_element_link_id => l_ele_link_id,
1898 p_num_entry_values => l_num_ev,
1899 p_entry_value_tbl => l_ev_tbl,
1900 p_input_value_id_tbl => l_iv_tbl,
1901 p_balance_adj_cost_flag => p_balance_adj_costing_flag);
1902
1903 /* Changes for Bug#9796821 - Starts */
1904 /* Checking the Medicare Taxability Rule */
1905 IF p_element_type = 'Medicare_EE' THEN
1906 l_medicare_taxability := 'Y'; /* For Regular Earnings */
1907 IF g_classification IN ('Imputed Earnings', 'Supplemental Earnings') THEN
1908 BEGIN
1909 SELECT 'Y'
1910 INTO l_medicare_taxability
1911 FROM pay_taxability_rules
1912 WHERE jurisdiction_code = g_fed_jd
1913 AND tax_category = g_earnings_category
1914 AND tax_type = 'MEDICARE'
1915 AND classification_id = g_classification_id
1916 AND NVL(status,'VALID') <> 'D';
1917 EXCEPTION
1918 WHEN NO_DATA_FOUND THEN
1919 l_medicare_taxability := 'N';
1920 WHEN OTHERS THEN
1921 hr_utility.trace('Error while getting Medicare Taxability rule');
1922 hr_utility.set_location(c_proc, 210);
1923 hr_utility.raise_error;
1924 END;
1925 END IF;
1926 END IF;
1927
1928 /* Create run result for Medicare_EE_Over_Limit element if Medicare EE Taxable wages has crossed the limit */
1929 IF ( TO_CHAR(p_adjustment_date, 'YYYY') >= '2013'
1930 AND p_element_type = 'Medicare_EE'
1931 AND p_earn_amount <> 0
1932 AND l_medicare_taxability = 'Y' ) THEN
1933
1934 /* Get the Medicare EE new limit */
1938 FROM pay_us_federal_tax_info_f pft
1935 BEGIN
1936 SELECT TO_NUMBER(pft.fed_attribute1) - 0.01 /* Subtract 0.01 from this value as it stores the starting Limit */
1937 INTO l_medi_ee_limit1 /* Bug#15852506 */
1939 WHERE pft.fed_information_category = '401K LIMITS'
1940 AND p_adjustment_date BETWEEN pft.effective_start_date AND pft.effective_end_date;
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 hr_utility.trace('Error while getting Medicare Limit');
1944 hr_utility.set_location(c_proc, 220);
1945 hr_utility.raise_error;
1946 END;
1947
1948 /* If Medicare EE Taxable YTD wages is already greater than the Medi EE limit or
1949 if it crosses the limit with this Balance Adjustment */
1950 hr_utility.trace('g_medicare_ee_taxable: ' || g_medicare_ee_taxable);
1951 hr_utility.trace('l_medi_ee_limit1: ' || l_medi_ee_limit1);
1952 hr_utility.trace('p_earn_amount: ' || p_earn_amount);
1953
1954 IF (g_medicare_ee_taxable > l_medi_ee_limit1 OR
1955 g_medicare_ee_taxable + p_earn_amount > l_medi_ee_limit1) THEN
1956 /* Get the Element ID and TAXABLE Input Value ID of Medicare_EE_Over_Limit */
1957 BEGIN
1958 SELECT pet.element_type_id, piv.input_value_id
1959 INTO l_element_type_id, l_input_value_id
1960 FROM pay_element_types_f pet,
1961 pay_input_values_f piv
1962 WHERE pet.element_name = 'Medicare_EE_Over_Limit'
1963 AND piv.element_type_id = pet.element_type_id
1964 AND piv.name = 'TAXABLE'
1965 AND pet.business_group_id IS NULL
1966 AND pet.legislation_code = 'US'
1967 AND p_adjustment_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1968 AND piv.business_group_id IS NULL
1969 AND piv.legislation_code = 'US'
1970 AND p_adjustment_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
1971 EXCEPTION
1972 WHEN OTHERS THEN
1973 hr_utility.trace('Error While Getting Element details for Medicare_EE_Over_Limit');
1974 hr_utility.set_location(c_proc, 230);
1975 hr_utility.raise_error;
1976 END;
1977
1978 /* Get Element Link ID */
1979 l_ele_link_id := hr_entry_api.get_link(
1980 p_assignment_id => p_assignment_id,
1981 p_element_type_id => l_element_type_id,
1982 p_session_date => p_adjustment_date);
1983
1984 IF (l_ele_link_id IS NULL) THEN
1985 hr_utility.trace('Error While Getting Element Link details for Medicare_EE_Over_Limit');
1986 hr_utility.set_location(c_proc, 240);
1987 hr_utility.set_message(801, 'PY_51132_TXADJ_LINK_MISSING');
1988 hr_utility.set_message_token ('ELEMENT', 'Medicare_EE_Over_Limit');
1989 hr_utility.raise_error;
1990 END IF;
1991
1992 /* If Medicare EE Taxable YTD wages is already greater than the Medi EE limit */
1993 IF g_medicare_ee_taxable > l_medi_ee_limit1 THEN
1994 IF p_earn_amount > 0 THEN
1995 l_value := p_earn_amount;
1996 ELSE /* Balance Adjsutment supports -ve Gross Amount */
1997 l_excess := g_medicare_ee_taxable - l_medi_ee_limit1;
1998 IF l_excess < ABS(p_earn_amount) THEN
1999 l_value := -l_excess;
2000 ELSE
2001 l_value := p_earn_amount;
2002 END IF;
2003 END IF;
2004 /* If Medicare EE Taxable YTD wages crosses the limit with this Balance Adjustment */
2005 ELSIF (g_medicare_ee_taxable + p_earn_amount > l_medi_ee_limit1) THEN
2006 l_value := g_medicare_ee_taxable + p_earn_amount - l_medi_ee_limit1;
2007 END IF;
2008
2009 /* Store the Adjusted value to pl/sql table */
2010 l_num_ev := 1;
2011 l_iv_tbl(l_num_ev) := l_input_value_id;
2012 l_iv_names_tbl(l_num_ev) := 'TAXABLE';
2013 l_ev_tbl(l_num_ev) := l_value;
2014
2015 hr_utility.trace('Element_link_id: ' || l_ele_link_id);
2016 hr_utility.trace('l_iv_tbl(l_num_ev): ' || l_iv_tbl(l_num_ev));
2017 hr_utility.trace('l_iv_names_tbl(l_num_ev): ' || l_iv_names_tbl(l_num_ev));
2018 hr_utility.trace('l_ev_tbl(l_num_ev): ' || l_ev_tbl(l_num_ev));
2019
2020 /* Call the Balance Routine to create Element Entry Values and Run Results */
2021 pay_bal_adjust.adjust_balance(p_batch_id => p_payroll_action_id,
2022 p_assignment_id => p_assignment_id,
2023 p_element_link_id => l_ele_link_id,
2024 p_num_entry_values => l_num_ev,
2025 p_entry_value_tbl => l_ev_tbl,
2026 p_input_value_id_tbl => l_iv_tbl,
2027 p_balance_adj_cost_flag => p_balance_adj_costing_flag);
2028
2029 END IF;
2030 END IF;
2031 /* Changes for Bug#9796821 - Ends */
2032
2033 END process_element;
2034
2035
2036 FUNCTION derive_jd_geocode(
2037 p_assignment_id IN NUMBER,
2038 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
2039 p_county_name IN VARCHAR2 DEFAULT NULL,
2040 p_city_name IN VARCHAR2 DEFAULT NULL,
2041 p_zip_code IN VARCHAR2 DEFAULT NULL)
2045
2042 RETURN VARCHAR2 IS
2043
2044 c_proc VARCHAR2(100) := 'derive_jd_geocode';
2046 CURSOR csr_state_code IS
2047 SELECT state_code
2048 FROM PAY_US_STATES
2049 WHERE state_abbrev = p_state_abbrev
2050 ;
2051
2052 CURSOR csr_county_code IS
2053 SELECT cn.state_code,
2054 cn.county_code
2055 FROM PAY_US_COUNTIES cn,
2056 PAY_US_STATES s
2057 WHERE cn.county_name = p_county_name
2058 and cn.state_code = s.state_code
2059 and s.state_abbrev = p_state_abbrev
2060 ;
2061
2062
2063 -- cursors to compare the location provided with the location of
2064 -- the employee's assignment
2065 CURSOR csr_chk_state IS
2066 SELECT 'PASS'
2067 FROM PAY_US_EMP_STATE_TAX_RULES st,
2068 PAY_US_STATES pus
2069 WHERE st.assignment_id = p_assignment_id
2070 and st.state_code = pus.state_code
2071 and pus.state_abbrev = p_state_abbrev
2072 ;
2073
2074 CURSOR csr_chk_local (x_jd VARCHAR2) IS
2075 SELECT 'PASS'
2076 FROM PAY_US_EMP_CITY_TAX_RULES
2077 WHERE assignment_id = p_assignment_id
2078 and jurisdiction_code = x_jd
2079 UNION
2080 SELECT 'PASS'
2081 FROM PAY_US_EMP_COUNTY_TAX_RULES
2082 WHERE assignment_id = p_assignment_id
2083 and jurisdiction_code = x_jd
2084 ;
2085
2086 l_geocode VARCHAR2(11) := '00-000-0000';
2087 l_county_code VARCHAR2(4) := '000' ;
2088 l_state_code VARCHAR2(2) := '00' ;
2089 l_valid_for_asg VARCHAR2(4) := 'FAIL' ;
2090
2091 BEGIN
2092
2093 IF (p_city_name IS NOT NULL AND p_zip_code IS NOT NULL) THEN
2094 hr_utility.set_location(c_proc, 10);
2095 l_geocode := hr_us_ff_udfs.addr_val(
2096 p_state_abbrev => p_state_abbrev,
2097 p_county_name => p_county_name,
2098 p_city_name => p_city_name,
2099 p_zip_code => p_zip_code );
2100
2101 OPEN csr_chk_local(l_geocode);
2102 FETCH csr_chk_local INTO l_valid_for_asg;
2103 CLOSE csr_chk_local;
2104
2105 IF (l_valid_for_asg = 'FAIL') THEN
2106 hr_utility.set_location(c_proc, 15);
2107 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
2108 hr_utility.raise_error;
2109 END IF;
2110
2111 ELSIF (p_county_name IS NOT NULL AND p_state_abbrev IS NOT NULL) THEN
2112 hr_utility.set_location(c_proc, 20);
2113 OPEN csr_county_code;
2114 FETCH csr_county_code INTO l_state_code, l_county_code;
2115 CLOSE csr_county_code;
2116 l_geocode := l_state_code||'-'||l_county_code||'-0000';
2117
2118 OPEN csr_chk_local(l_geocode);
2119 FETCH csr_chk_local INTO l_valid_for_asg;
2120 CLOSE csr_chk_local;
2121
2122 IF (l_valid_for_asg = 'FAIL') THEN
2123 hr_utility.set_location(c_proc, 25);
2124 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
2125 hr_utility.raise_error;
2126 END IF;
2127
2128 ELSIF (p_county_name IS NULL AND p_state_abbrev IS NOT NULL) THEN
2129 hr_utility.set_location(c_proc, 30);
2130 OPEN csr_state_code;
2131 FETCH csr_state_code INTO l_state_code;
2132 CLOSE csr_state_code;
2133 l_geocode := l_state_code||'-000-0000';
2134
2135 OPEN csr_chk_state;
2136 FETCH csr_chk_state INTO l_valid_for_asg;
2137 CLOSE csr_chk_state;
2138
2139 IF (l_valid_for_asg = 'FAIL') THEN
2140 hr_utility.set_location(c_proc, 25);
2141 hr_utility.set_message(801, 'PY_51133_TXADJ_INVALID_CITY');
2142 hr_utility.raise_error;
2143 END IF;
2144
2145 ELSE
2146 l_geocode := '00-000-0000';
2147
2148 END IF;
2149
2150 Return (l_geocode);
2151
2152 END derive_jd_geocode;
2153
2154
2155
2156 FUNCTION taxable_balance(
2157 p_tax_bal_name IN VARCHAR2,
2158 p_ee_or_er IN VARCHAR2,
2159 p_tax_unit_id IN NUMBER,
2160 p_assignment_id IN NUMBER,
2161 p_adjustment_date IN DATE,
2162 p_geocode IN VARCHAR2 DEFAULT NULL)
2163 RETURN NUMBER IS
2164
2165 c_proc VARCHAR2(100) := 'taxable_balance';
2166
2167 l_return_bal NUMBER;
2168 l_date DATE;
2169 l_asg_type VARCHAR2(6);
2170
2171
2172 CURSOR csr_get_endofyear IS
2173 SELECT to_date('31/12/' || TO_CHAR(p_adjustment_date, 'YYYY'), 'DD/MM/YYYY')
2174 FROM SYS.DUAL
2175 ;
2176
2177 BEGIN
2178 /*
2179 ** find current balance for tax,
2180 ** assignment_id is used to find balance specific to a person,
2181 ** when calculating the adjustment amount up to the limit,
2182 ** the old TAXABLE balance is required
2183 */
2184
2185 /*
2186 ** fetch last day of year, require end of year balance, not date effective balance
2187 */
2188 OPEN csr_get_endofyear;
2189 FETCH csr_get_endofyear INTO l_date;
2190 CLOSE csr_get_endofyear;
2191
2192 IF g_tax_group <> 'NOT_ENTERED' and
2193 ( p_tax_bal_name = 'FUTA' or
2194 p_tax_bal_name = 'SS' ) THEN
2195 l_asg_type := 'PER';
2196 -- l_asg_type := 'PER_TG';
2197 ELSE
2198 l_asg_type := 'PER';
2199 END IF;
2200
2201 l_return_bal := pay_us_tax_bals_pkg.us_tax_balance(
2202 p_tax_balance_category => 'TAXABLE',
2206 p_asg_type => l_asg_type,
2203 p_tax_type => p_tax_bal_name,
2204 p_ee_or_er => p_ee_or_er,
2205 p_time_type => 'YTD',
2207 p_gre_id_context => p_tax_unit_id,
2208 p_jd_context => p_geocode,
2209 p_assignment_action_id => NULL,
2210 p_assignment_id => p_assignment_id,
2211 p_virtual_date => l_date);
2212
2213 Return(l_return_bal);
2214
2215 END taxable_balance;
2216
2217
2218
2219 FUNCTION tax_exists (p_jd_code VARCHAR2,
2220 p_tax_type VARCHAR2,
2221 p_adj_date DATE,
2222 p_per_adr_geocode VARCHAR2,
2223 p_loc_adr_geocode VARCHAR2)
2224 RETURN VARCHAR2 IS
2225
2226 l_exists VARCHAR2(1) := 'N';
2227
2228 CURSOR sdi_er_exists IS
2229 SELECT 'Y'
2230 FROM pay_us_state_tax_info_f
2231 WHERE state_code = SUBSTR(p_jd_code, 1, 2)
2232 AND sdi_er_wage_limit IS NOT NULL
2233 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2234
2235 CURSOR sdi_ee_exists IS
2236 SELECT 'Y'
2237 FROM pay_us_state_tax_info_f
2238 WHERE state_code = SUBSTR(p_jd_code, 1, 2)
2239 AND sdi_ee_wage_limit IS NOT NULL
2240 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2241
2242 CURSOR sdi1_ee_exists IS
2243 SELECT 'Y'
2244 FROM pay_us_state_tax_info_f
2245 WHERE state_code = SUBSTR(p_jd_code, 1, 2)
2246 AND STA_INFORMATION21 IS NOT NULL
2247 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2248
2249 CURSOR sui_er_exists is
2250 SELECT 'Y'
2251 FROM pay_us_state_tax_info_f
2252 WHERE state_code = substr(p_jd_code, 1, 2)
2253 AND sui_er_wage_limit IS NOT NULL
2254 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2255
2256 CURSOR sui_ee_exists is
2257 SELECT 'Y'
2258 FROM pay_us_state_tax_info_f
2259 WHERE state_code = substr(p_jd_code, 1, 2)
2260 AND sui_ee_wage_limit IS NOT NULL
2261 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2262
2263 CURSOR sit_exists is
2264 SELECT sit_exists
2265 FROM pay_us_state_tax_info_f
2266 WHERE state_code = substr(p_jd_code, 1, 2)
2267 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2268
2269 CURSOR county_exists is
2270 SELECT county_tax
2271 FROM pay_us_county_tax_info_f
2272 WHERE jurisdiction_code = substr(p_jd_code, 1, 7)||'0000'
2273 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2274
2275 CURSOR city_exists is
2276 SELECT city_tax
2277 FROM pay_us_city_tax_info_f
2278 WHERE jurisdiction_code = p_jd_code
2279 AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
2280
2281 BEGIN
2282
2283 IF (p_tax_type = 'SUI_ER') THEN
2284 OPEN sui_er_exists;
2285 FETCH sui_er_exists INTO l_exists;
2286 CLOSE sui_er_exists;
2287
2288 ELSIF (p_tax_type = 'SUI_EE') THEN
2289 OPEN sui_ee_exists;
2290 FETCH sui_ee_exists INTO l_exists;
2291 CLOSE sui_ee_exists;
2292
2293 ELSIF (p_tax_type = 'SDI_ER') THEN
2294 OPEN sdi_er_exists;
2295 FETCH sdi_er_exists INTO l_exists;
2296 CLOSE sdi_er_exists;
2297
2298 ELSIF (p_tax_type = 'SDI_EE') THEN
2299 OPEN sdi_ee_exists;
2300 FETCH sdi_ee_exists INTO l_exists;
2301 CLOSE sdi_ee_exists;
2302
2303 ELSIF (p_tax_type = 'SDI1_EE') THEN
2304 OPEN sdi1_ee_exists;
2305 FETCH sdi1_ee_exists INTO l_exists;
2306 CLOSE sdi1_ee_exists;
2307
2308 ELSIF (p_tax_type = 'SIT') THEN
2309 OPEN sit_exists;
2310 FETCH sit_exists INTO l_exists;
2311 CLOSE sit_exists;
2312
2313 ELSIF (p_tax_type = 'CITY') THEN
2314 OPEN city_exists;
2315 FETCH city_exists INTO l_exists;
2316 CLOSE city_exists;
2317
2318 IF p_jd_code in ( '33-005-2010', '33-047-2010',
2319 '33-061-2010', '33-081-2010', '33-085-2010' ) THEN
2320
2321 IF p_jd_code = p_per_adr_geocode THEN
2322
2323 NULL;
2324
2325 ELSE
2326
2327 l_exists := 'N';
2328
2329 END IF;
2330
2331 END IF;
2332
2333
2334 ELSIF (p_tax_type = 'COUNTY') THEN
2335 OPEN county_exists;
2336 FETCH county_exists INTO l_exists;
2337 CLOSE county_exists;
2338
2339 ELSE
2340 NULL;
2341 END IF;
2342
2343 RETURN l_exists;
2344
2345 END tax_exists;
2346 /* Bug 12694875 Starts */
2347 -- Created a overloading procedure without newly added out parameters
2348 -- p_sui_ee_warning and p_sui_er_warning.
2349
2350 PROCEDURE create_tax_balance_adjustment(
2351 p_validate IN BOOLEAN DEFAULT FALSE,
2352 p_adjustment_date IN DATE,
2353 p_business_group_name IN VARCHAR2,
2354 p_assignment_number IN VARCHAR2,
2355 p_tax_unit_id IN VARCHAR2,
2356 p_consolidation_set IN VARCHAR2,
2357 p_earning_element_type IN VARCHAR2 DEFAULT NULL,
2361 p_FIT_THIRD IN VARCHAR2 DEFAULT NULL,
2358 p_gross_amount IN NUMBER DEFAULT 0,
2359 p_net_amount IN NUMBER DEFAULT 0,
2360 p_FIT IN NUMBER DEFAULT 0,
2362 p_SS IN NUMBER DEFAULT 0,
2363 p_SS_ER IN NUMBER DEFAULT 0,
2364 p_Medicare IN NUMBER DEFAULT 0,
2365 p_Medicare_ER IN NUMBER DEFAULT 0,
2366 p_SIT IN NUMBER DEFAULT 0,
2367 p_SUI IN NUMBER DEFAULT 0,
2368 p_SDI IN NUMBER DEFAULT 0,
2369 p_SDI1 IN NUMBER DEFAULT 0,
2370 p_County IN NUMBER DEFAULT 0,
2371 p_City IN NUMBER DEFAULT 0,
2372 p_city_name IN VARCHAR2 DEFAULT NULL,
2373 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
2374 p_county_name IN VARCHAR2 DEFAULT NULL,
2375 p_zip_code IN VARCHAR2 DEFAULT NULL,
2376 p_balance_adj_costing_flag IN VARCHAR2 DEFAULT NULL,
2377 p_balance_adj_prepay_flag IN VARCHAR2 DEFAULT 'N',
2378 p_futa_er IN NUMBER DEFAULT 0,
2379 p_sui_er IN NUMBER DEFAULT 0,
2380 p_sdi_er IN NUMBER DEFAULT 0,
2381 p_sch_dist_wh_ee IN NUMBER DEFAULT 0,
2382 p_sch_dist_jur IN VARCHAR2 DEFAULT NULL,
2383 p_payroll_action_id OUT NOCOPY NUMBER,
2384 p_create_warning OUT NOCOPY BOOLEAN)
2385 IS
2386
2387 l_sui_ee_warning BOOLEAN;
2388 l_sui_er_warning BOOLEAN;
2389
2390 BEGIN
2391
2392 create_tax_balance_adjustment(
2393 p_validate => p_validate,
2394 p_adjustment_date => p_adjustment_date,
2395 p_business_group_name => p_business_group_name,
2396 p_assignment_number => p_assignment_number,
2397 p_tax_unit_id => p_tax_unit_id,
2398 p_consolidation_set => p_consolidation_set,
2399 p_earning_element_type => p_earning_element_type,
2400 p_gross_amount => p_gross_amount,
2401 p_net_amount => p_net_amount,
2402 p_FIT => p_FIT,
2403 p_SS => p_SS,
2404 p_SS_ER => p_ss_ER,
2405 p_Medicare => p_Medicare,
2406 p_Medicare_ER => p_Medicare_ER,
2407 p_balance_adj_costing_flag => p_balance_adj_costing_flag,
2408 p_balance_adj_prepay_flag => p_balance_adj_prepay_flag,
2409 p_city_name => p_city_name,
2410 p_state_abbrev => p_state_abbrev,
2411 p_county_name => p_county_name,
2412 p_zip_code => p_zip_code,
2413 p_FIT_THIRD => p_FIT_THIRD,
2414 p_SIT => p_SIT,
2415 p_SUI => p_SUI,
2416 p_SDI => p_SDI,
2417 p_SDI1 => p_SDI1,
2418 p_County => p_County,
2419 p_City => p_City,
2420 p_futa_er => p_futa_er,
2421 p_sui_er => p_sui_er,
2422 p_sdi_er => p_sdi_er,
2423 p_sch_dist_wh_ee => p_sch_dist_wh_ee,
2424 p_sch_dist_jur => p_sch_dist_jur,
2425 p_payroll_action_id => p_payroll_action_id,
2426 p_create_warning => p_create_warning,
2427 p_sui_ee_warning => l_sui_ee_warning,
2428 p_sui_er_warning => l_sui_er_warning);
2429
2430 END;
2431 /* Bug 12694875 Ends */
2432
2433 PROCEDURE create_tax_balance_adjustment(
2434 p_validate IN BOOLEAN DEFAULT FALSE,
2435 p_adjustment_date IN DATE,
2436 p_business_group_name IN VARCHAR2,
2437 p_assignment_number IN VARCHAR2,
2438 p_tax_unit_id IN VARCHAR2,
2439 p_consolidation_set IN VARCHAR2,
2440 p_earning_element_type IN VARCHAR2 DEFAULT NULL,
2441 p_gross_amount IN NUMBER DEFAULT 0,
2442 p_net_amount IN NUMBER DEFAULT 0,
2443 p_FIT IN NUMBER DEFAULT 0,
2444 p_FIT_THIRD IN VARCHAR2 DEFAULT NULL,
2445 p_SS IN NUMBER DEFAULT 0,
2446 p_SS_ER IN NUMBER DEFAULT 0,
2447 p_Medicare IN NUMBER DEFAULT 0,
2448 p_Medicare_ER IN NUMBER DEFAULT 0,
2449 p_SIT IN NUMBER DEFAULT 0,
2450 p_SUI IN NUMBER DEFAULT 0,
2451 p_SDI IN NUMBER DEFAULT 0,
2452 p_SDI1 IN NUMBER DEFAULT 0,
2453 p_County IN NUMBER DEFAULT 0,
2454 p_City IN NUMBER DEFAULT 0,
2455 p_city_name IN VARCHAR2 DEFAULT NULL,
2456 p_state_abbrev IN VARCHAR2 DEFAULT NULL,
2457 p_county_name IN VARCHAR2 DEFAULT NULL,
2458 p_zip_code IN VARCHAR2 DEFAULT NULL,
2459 p_balance_adj_costing_flag IN VARCHAR2 DEFAULT NULL,
2460 p_balance_adj_prepay_flag IN VARCHAR2 DEFAULT 'N',
2461 p_futa_er IN NUMBER DEFAULT 0,
2462 p_sui_er IN NUMBER DEFAULT 0,
2463 p_sdi_er IN NUMBER DEFAULT 0,
2464 p_sch_dist_wh_ee IN NUMBER DEFAULT 0,
2465 p_sch_dist_jur IN VARCHAR2 DEFAULT NULL,
2466 p_payroll_action_id OUT NOCOPY NUMBER,
2467 p_create_warning OUT NOCOPY BOOLEAN,
2468 p_sui_ee_warning OUT NOCOPY BOOLEAN,
2469 p_sui_er_warning OUT NOCOPY BOOLEAN)
2470 IS
2471
2472 c_proc VARCHAR2(100) := 'create_tax_balance_adjustment';
2473
2477 l_payroll_id NUMBER;
2474 l_bg_id NUMBER;
2475 l_consolidation_set_id NUMBER;
2476 l_assignment_id NUMBER;
2478 l_payroll_action_id NUMBER;
2479
2480 l_jd_entered VARCHAR2(11) := '00-000-0000';
2481 l_jd_level_entered NUMBER := 1;
2482 l_jd_level_needed NUMBER;
2483
2484 l_primary_asg_state VARCHAR2(2);
2485 l_create_warning BOOLEAN;
2486
2487 l_counter NUMBER;
2488 l_grp_key pay_payroll_actions.legislative_parameters%TYPE;
2489
2490 l_effective_start_date DATE;
2491 l_effective_end_date DATE;
2492 l_element_entry_id NUMBER;
2493 l_fed_tax_exempt VARCHAR2(1);
2494 l_futa_tax_exempt VARCHAR2(1);
2495 l_medicare_tax_exempt VARCHAR2(1);
2496 l_ss_tax_exempt VARCHAR2(1);
2497 l_sit_exempt VARCHAR2(1);
2498 l_sdi_exempt VARCHAR2(1);
2499 l_sdi1_exempt VARCHAR2(1);
2500 l_sui_exempt VARCHAR2(1);
2501 l_cnt_exempt VARCHAR2(1);
2502 l_cnt_sd_exempt VARCHAR2(1);
2503 l_cty_exempt VARCHAR2(1);
2504 l_cty_sd_exempt VARCHAR2(1);
2505
2506 l_ss_er_w11_exempt VARCHAR2(1);
2507 l_current_ss_er_taxable_amt NUMBER;
2508 l_net_taxable_amount NUMBER;
2509 l_ss_er_w11_def_bal_id NUMBER;
2510
2511 -- New Variables to derive the PSD Jurisdiction code
2512 l_psd_jd VARCHAR2(16);
2513 l_per_adr_geocode VARCHAR2(11);
2514 l_loc_adr_geocode VARCHAR2(11);
2515 l_per_state_abbrev VARCHAR2(2);
2516 l_per_county VARCHAR2(30);
2517 l_per_city VARCHAR2(30);
2518 l_per_zip_code VARCHAR2(10);
2519 l_loc_state_abbrev VARCHAR2(2);
2520 l_loc_county VARCHAR2(30);
2521 l_loc_city VARCHAR2(30);
2522 l_loc_zip_code VARCHAR2(10);
2523
2524 l_element_type_subj VARCHAR2(80);
2525 l_abbrev_element_type_subj VARCHAR2(10);
2526 l_element_type_wh VARCHAR2(80);
2527 l_abbrev_element_type_wh VARCHAR2(10);
2528
2529
2530 -- Bug 4188782
2531 l_element_classification varchar2(100);
2532
2533
2534 l_sd_cty_status VARCHAR2(1) :='N'; --# Bug13634961 added new variable l_sd_cty_status
2535 l_sd_cnt_status VARCHAR2(1) :='N'; --# Bug13634961 added new variable l_sd_cnt_status
2536 l_sd_cty_or_cnt_exempt VARCHAR2(1) :='Y'; --# Bug13634961 added new variable l_sd_cty_or_cnt_exempt
2537
2538 cursor get_element_details (p_element_type in varchar2,p_bg_id in number) is
2539 SELECT c.classification_name
2540 FROM PAY_ELEMENT_CLASSIFICATIONS c,
2541 PAY_ELEMENT_TYPES_F e,
2542 hr_organization_information hoi
2543 WHERE e.classification_id = c.classification_id
2544 AND hoi.organization_id = p_bg_id
2545 AND e.element_name = p_element_type
2546 AND (e.business_group_id = p_bg_id
2547 OR e.business_group_id IS NULL)
2548 AND hoi.org_information_context = 'Business Group Information'
2549 AND c.legislation_code = hoi.org_information9;
2550 ------------------------
2551
2552
2553 CURSOR csr_sdi_check IS
2554
2555 /* SELECT region_2 primary_asg_state
2556 FROM HR_LOCATIONS loc,
2557 PER_ASSIGNMENTS_F asg,
2558 PER_BUSINESS_GROUPS bg
2559 -- Bug fix 1398865. Ensures one row is returned
2560 WHERE asg.assignment_number = p_assignment_number
2561 and asg.business_group_id = bg.business_group_id
2562 and bg.name ||'' = p_business_group_name
2563 and asg.effective_start_date <= p_adjustment_date
2564 AND asg.effective_end_date >= trunc(p_adjustment_date,'Y')
2565 and asg.primary_flag = 'Y'
2566 and asg.location_id = loc.location_id
2567 and loc.region_2 = p_state_abbrev;
2568 */
2569 SELECT decode(nvl(asg.work_at_home, 'N'),
2570 'N' , loc.region_2,
2571 addr.region_2) primary_asg_state
2572 FROM HR_LOCATIONS loc,
2573 PER_ASSIGNMENTS_F asg,
2574 PER_BUSINESS_GROUPS bg,
2575 PER_ADDRESSES addr
2576 -- Bug fix 1398865. Ensures one row is returned
2577 WHERE asg.assignment_number = p_assignment_number
2578 and asg.business_group_id = bg.business_group_id
2579 and bg.name ||'' = p_business_group_name
2580 and asg.effective_start_date <= p_adjustment_date
2581 AND asg.effective_end_date >= trunc(p_adjustment_date,'Y')
2582 and asg.primary_flag = 'Y'
2583 and asg.location_id = loc.location_id
2584 -- and loc.region_2 = p_state_abbrev,
2585 and asg.person_id = addr.person_id
2586 and addr.primary_flag = 'Y'
2587 and p_adjustment_date between addr.date_from and
2588 nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'));
2589
2590 CURSOR c_get_tax_group IS
2591 select decode(hoi.org_information5,
2592 NULL,'NOT_ENTERED',
2593 hoi.org_information5)
2594 from hr_organization_information hoi
2595 where hoi.organization_id = p_tax_unit_id
2596 and hoi.org_information_context = 'Federal Tax Rules'
2597 ;
2598
2599
2600
2601 CURSOR csr_sui_geocode IS
2605 fed.futa_tax_exempt,
2602 SELECT sui_jurisdiction_code,
2603 pus.state_abbrev,
2604 fed.fit_exempt,
2606 fed.medicare_tax_exempt,
2607 fed.ss_tax_exempt,
2608 nvl(fed.FED_INFORMATION2,'N')
2609 FROM pay_us_emp_fed_tax_rules_f fed,
2610 PER_ASSIGNMENTS_F a,
2611 PER_BUSINESS_GROUPS bg,
2612 pay_us_states pus
2613 WHERE fed.assignment_id = a.assignment_id
2614 and a.assignment_number = p_assignment_number
2615 and a.business_group_id = bg.business_group_id
2616 and bg.name ||'' = p_business_group_name
2617 and p_adjustment_date between fed.effective_start_date
2618 and fed.effective_end_date
2619 and p_adjustment_date BETWEEN
2620 a.effective_start_date and a.effective_end_date
2621 and fed.sui_state_code = pus.state_code
2622 ;
2623
2624 Cursor c_get_futa_self_adjust_method
2625 IS
2626 select hl.meaning
2627 from hr_organization_information hoi,
2628 hr_lookups hl
2629 where hoi.organization_id = p_tax_unit_id
2630 and hoi.org_information_context = 'Federal Tax Rules'
2631 and hoi.org_information3 = hl.LOOKUP_CODE
2632 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2633
2634 Cursor c_get_ss_self_adjust_method
2635 IS
2636 select hl.meaning
2637 from hr_organization_information hoi,
2638 hr_lookups hl
2639 where hoi.organization_id = p_tax_unit_id
2640 and hoi.org_information_context = 'Federal Tax Rules'
2641 and hoi.org_information1 = hl.LOOKUP_CODE
2642 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2643
2644 Cursor c_get_medi_self_adjust_method
2645 IS
2646 select hl.meaning
2647 from hr_organization_information hoi,
2648 hr_lookups hl
2649 where hoi.organization_id = p_tax_unit_id
2650 and hoi.org_information_context = 'Federal Tax Rules'
2651 and hoi.org_information2 = hl.LOOKUP_CODE
2652 and hl.lookup_type = 'MEDI_SELF_ADJ_CALC_METHOD';
2653
2654 Cursor c_get_sdi_self_adjust_method
2655 IS
2656 select hl.meaning
2657 from hr_organization_information hoi,
2658 hr_lookups hl
2659 where hoi.organization_id = p_tax_unit_id
2660 and hoi.org_information_context = 'State Tax Rules'
2661 and hoi.org_information1 = p_state_abbrev
2662 and hoi.org_information5 = hl.LOOKUP_CODE
2663 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2664
2665 Cursor c_get_sdi1_self_adjust_method
2666 IS
2667 select hl.meaning
2668 from hr_organization_information hoi,
2669 hr_lookups hl
2670 where hoi.organization_id = p_tax_unit_id
2671 and hoi.org_information_context = 'State Tax Rules2'
2672 and hoi.org_information1 = p_state_abbrev
2673 and hoi.org_information5 = hl.LOOKUP_CODE
2674 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2675
2676 Cursor c_get_sui_self_adjust_method
2677 IS
2678 select hl.meaning
2679 from hr_organization_information hoi,
2680 hr_lookups hl
2681 where hoi.organization_id = p_tax_unit_id
2682 and hoi.org_information_context = 'State Tax Rules'
2683 and hoi.org_information1 = p_state_abbrev
2684 and hoi.org_information4 = hl.LOOKUP_CODE --bug 3887144
2685 -- and hoi.org_information5 = hl.LOOKUP_CODE
2686 and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
2687
2688
2689 CURSOR csr_sit_exempt (cp_jurisdiction_code IN VARCHAR2)
2690 IS
2691 SELECT sta.sit_exempt,
2692 sta.sdi_exempt,
2693 NVL(sta.STA_INFORMATION5,'N'),
2694 sta.sui_exempt
2695 FROM pay_us_emp_state_tax_rules_f sta,
2696 PER_ASSIGNMENTS_F a,
2697 PER_BUSINESS_GROUPS bg,
2698 pay_us_states pus
2699 WHERE sta.assignment_id = a.assignment_id
2700 and a.assignment_number = p_assignment_number
2701 and a.business_group_id = bg.business_group_id
2702 and bg.name ||'' = p_business_group_name
2703 and p_adjustment_date between sta.effective_start_date
2704 and sta.effective_end_date
2705 and p_adjustment_date BETWEEN
2706 a.effective_start_date and a.effective_end_date
2707 and sta.jurisdiction_code = (substr(cp_jurisdiction_code,0,2) || '-000-0000')
2708 ;
2709
2710 CURSOR csr_county_exempt (cp_jurisdiction_code IN VARCHAR2)
2711 IS
2712 SELECT cnt.lit_exempt,
2713 NVL(cnt.sd_exempt,'N')
2714 FROM pay_us_emp_county_tax_rules_f cnt,
2715 PER_ASSIGNMENTS_F a,
2716 PER_BUSINESS_GROUPS bg,
2717 pay_us_states pus
2718 WHERE cnt.assignment_id = a.assignment_id
2719 and a.assignment_number = p_assignment_number
2720 and a.business_group_id = bg.business_group_id
2721 and bg.name ||'' = p_business_group_name
2722 and p_adjustment_date between cnt.effective_start_date
2723 and cnt.effective_end_date
2724 and p_adjustment_date BETWEEN
2725 a.effective_start_date and a.effective_end_date
2726 and cnt.jurisdiction_code = (substr(cp_jurisdiction_code,0,6) || '-0000')
2727 ;
2728
2729 CURSOR csr_city_exempt (cp_jurisdiction_code IN VARCHAR2)
2730 IS
2731 SELECT cty.lit_exempt,
2732 NVL(cty.sd_exempt,'N')
2733 FROM pay_us_emp_city_tax_rules_f cty,
2734 PER_ASSIGNMENTS_F a,
2735 PER_BUSINESS_GROUPS bg,
2736 pay_us_states pus
2740 and bg.name ||'' = p_business_group_name
2737 WHERE cty.assignment_id = a.assignment_id
2738 and a.assignment_number = p_assignment_number
2739 and a.business_group_id = bg.business_group_id
2741 and p_adjustment_date between cty.effective_start_date
2742 and cty.effective_end_date
2743 and p_adjustment_date BETWEEN
2744 a.effective_start_date and a.effective_end_date
2745 and cty.jurisdiction_code = cp_jurisdiction_code
2746 ;
2747
2748 /*Added for Bug#9075526*/
2749
2750 CURSOR get_automate_sui_wage_credit IS
2751 SELECT nvl(target.automate_sui_wage_credit,'N')
2752 FROM hr_tax_units_v target
2753 WHERE target.tax_unit_id = p_tax_unit_id;
2754
2755 CURSOR get_sui_taxable_def_bal_id IS
2756 SELECT pdb.defined_balance_id
2757 FROM pay_balance_types pbt,
2758 pay_balance_dimensions pbd,
2759 pay_defined_balances pdb
2760 WHERE pbt.balance_name in ('SUI ER Taxable','SUI EE Taxable')
2761 AND pbt.balance_type_id=pdb.balance_type_id
2762 AND pbd.dimension_name = 'Person within Government Reporting Entity Year to Date'
2763 AND pbd.balance_dimension_id=pdb.balance_dimension_id
2764 ORDER BY pbt.balance_name;
2765
2766 /*End Bug#9075526*/
2767
2768 /*Added for Bug#9872952*/
2769 CURSOR c_get_wage_acc_flag IS
2770 SELECT parameter_value
2771 FROM pay_action_parameters
2772 WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
2773
2774 CURSOR c_get_wage_exempt IS
2775 SELECT wage_exempt
2776 FROM pay_us_emp_fed_tax_rules_f fed,
2777 per_assignments_f a,
2778 per_business_groups bg
2779 WHERE fed.assignment_id = a.assignment_id
2780 AND a.assignment_number = p_assignment_number
2781 AND a.business_group_id = bg.business_group_id
2782 AND bg.name ||'' = p_business_group_name
2783 AND p_adjustment_date BETWEEN fed.effective_start_date
2784 AND fed.effective_end_date
2785 AND p_adjustment_date BETWEEN a.effective_start_date
2786 AND a.effective_end_date;
2787 /*End Bug#9872952*/
2788
2789 /*Added for Bug#13634961*/
2790 CURSOR csr_sd_cty_present (cp_school_jur_code IN VARCHAR2)
2791 IS
2792 SELECT 'Y'
2793 FROM pay_us_city_school_dsts psd
2794 WHERE psd.state_code = substr (cp_school_jur_code,1,2)
2795 AND psd.school_dst_code = substr (cp_school_jur_code,4,5)
2796 AND ROWNUM = 1;
2797
2798 CURSOR csr_sd_cnt_present (cp_school_jur_code IN VARCHAR2)
2799 IS
2800 SELECT 'Y'
2801 FROM pay_us_county_school_dsts psd
2802 WHERE psd.state_code = substr (cp_school_jur_code,1,2)
2803 AND psd.school_dst_code = substr (cp_school_jur_code,4,5)
2804 AND ROWNUM = 1;
2805
2806 /*End Bug#13634961*/
2807
2808 -- local copy of the tax withhelds,
2809 -- by copying the values to local variables,
2810 -- we avoid defining parameters as IN/OUT variables
2811 l_gross_amount NUMBER := NVL(p_gross_amount, 0);
2812 l_net_amount NUMBER := NVL(p_net_amount, 0);
2813 l_fit NUMBER := NVL(p_fit, 0);
2814 l_ss NUMBER := NVL(p_ss, 0);
2815 l_ss_er NUMBER := NVL(p_ss_er, 0);
2816 l_medicare NUMBER := NVL(p_medicare, 0);
2817 l_medicare_er NUMBER := NVL(p_medicare_er, 0); /* Bug#9796821 */
2818 l_sit NUMBER := NVL(p_sit, 0);
2819 l_sui_ee NUMBER := NVL(p_sui, 0);
2820 l_sdi_ee NUMBER := NVL(p_sdi, 0);
2821 l_sdi1_ee NUMBER := NVL(p_sdi1, 0);
2822 l_city NUMBER := NVL(p_city, 0);
2823 l_county NUMBER := NVL(p_county, 0);
2824 l_total_taxes_withheld NUMBER;
2825 l_fit_third VARCHAR2(5) := NVL(p_FIT_THIRD, 'NO');
2826
2827 /** sbilling **/
2828 l_futa_er NUMBER := NVL(p_futa_er, 0);
2829 l_sui_er NUMBER := NVL(p_sui_er, 0);
2830 l_sdi_er NUMBER := NVL(p_sdi_er, 0);
2831 l_sch_dist_wh_ee NUMBER := NVL(p_sch_dist_wh_ee, 0);
2832 l_sch_dist_jur VARCHAR2(10) := NVL(p_sch_dist_jur, '');
2833
2834 /*Added for Bug#9075526*/
2835 l_sui_auto_credit VARCHAR2(2) := 'N'; /*Added for Bug#9075526*/
2836 l_run_route VARCHAR2(5);
2837 l_run_route_bool BOOLEAN;
2838 l_sui_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2839 l_end_of_year_date DATE ;
2840 /*End Bug#9075526*/
2841
2842 /*Added for Bug#9872952*/
2843 l_profile_value VARCHAR2(2) := 'N';
2844 l_wage_accum_enabled VARCHAR2(2) := 'N';
2845 l_wage_exempt VARCHAR2(2) := 'N';
2846 /*End Bug#9872952*/
2847
2848 /*Added for Bug#10150905*/
2849 l_asg_exists Number;
2850 l_balance_fetch_date DATE;
2851 /*End Bug#10150905*/
2852
2853 /*Added for Bug#12694875*/
2854 l_sui_ee_jd_taxable NUMBER := 0;
2855 l_sui_er_jd_taxable NUMBER := 0;
2856 l_sui_ee_warning BOOLEAN := FALSE;
2857 l_sui_er_warning BOOLEAN := FALSE;
2858 /*End Bug#12694875*/
2859
2860 BEGIN
2861
2862 SAVEPOINT create_tax_bal_adjustment;
2863
2864 -- insert a row INTO fnd_session if there isn't one
2865 BEGIN
2866 INSERT INTO fnd_sessions(session_id, effective_date)
2867 SELECT USERENV('sessionid'), SYSDATE
2868 FROM DUAL
2872
2869 WHERE NOT EXISTS (SELECT '1'
2870 FROM fnd_sessions
2871 WHERE session_id = USERENV('sessionid'));
2873 END;
2874
2875 -- get assignment_id and business_group_id based on assignment number
2876 -- and business group name.
2877 BEGIN
2878
2879 /* Bug 9545789 Added check for the correct tax_unit_id in the below query */
2880
2881 hr_utility.set_location(c_proc, 5);
2882 SELECT a.assignment_id,
2883 a.business_group_id,
2884 a.payroll_id
2885 INTO l_assignment_id,
2886 l_bg_id,
2887 l_payroll_id
2888 FROM per_business_groups bg,
2889 per_assignments_f a,
2890 hr_soft_coding_keyflex hsk
2891 WHERE a.assignment_number = p_assignment_number
2892 and a.business_group_id = bg.business_group_id
2893 and bg.name ||'' = p_business_group_name
2894 and p_adjustment_date BETWEEN
2895 a.effective_start_date AND a.effective_end_date
2896 /*Added for bug 7692482*/
2897 and a.assignment_type='E'
2898 and a.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
2899 and hsk.segment1 = p_tax_unit_id
2900 ;
2901 EXCEPTION
2902 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
2903 /* THIS ERROR is stating that the assignment does not exist
2904 AS OF the p_adjustment_date IN p_business_group_name AND IN p_tax_unit_id
2905 */
2906 hr_utility.set_message(801, 'PY_51135_TXADJ_ASG_NOT_FOUND');
2907 hr_utility.raise_error;
2908 END;
2909
2910 -- get assignment derived jurisdiction geocode for state,county,city,zip code
2911 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
2912 p_state_abbrev => p_state_abbrev,
2913 p_county_name => p_county_name,
2914 p_city_name => p_city_name,
2915 p_zip_code => p_zip_code );
2916
2917 /* Get the assignments resident address / geocode and Work address / geocode to be used
2918 to build the PSD Jurisdiction code. The PSD Jurisdiction Code is the structure of
2919 SS-RRRRRR-WWWWW where SS is the state code RRRRRR is the Resident Jurisdiction PSD code
2920 and WWWWWW is the Work Jurisdition PSD code
2921 */
2922
2923
2924 -- Resident Address
2925
2926 Select PUS.state_abbrev, -- state
2927 nvl(ADDR.add_information19, ADDR.region_1), -- County
2928 nvl(ADDR.add_information18, ADDR.town_or_city), -- City
2929 nvl(ADDR.add_information20, ADDR.postal_code)
2930 INTO l_per_state_abbrev,
2931 l_per_county,
2932 l_per_city,
2933 l_per_zip_code
2934 FROM per_addresses ADDR
2935 ,per_all_assignments_f ASSIGN
2936 ,pay_us_states PUS
2937 where p_adjustment_date BETWEEN ASSIGN.effective_start_date
2938 AND ASSIGN.effective_end_date
2939 and ASSIGN.assignment_id = l_assignment_id
2940 and ADDR.person_id = ASSIGN.person_id
2941 and ADDR.primary_flag = 'Y'
2942 and p_adjustment_date BETWEEN nvl(ADDR.date_from,p_adjustment_date)
2943 AND nvl(ADDR.date_to, p_adjustment_date)
2944 and PUS.state_abbrev = nvl(ADDR.add_information17, ADDR.region_2);
2945
2946 l_per_adr_geocode := derive_jd_geocode(p_assignment_id => l_assignment_id,
2947 p_state_abbrev => l_per_state_abbrev,
2948 p_county_name => l_per_county,
2949 p_city_name => l_per_city,
2950 p_zip_code => l_per_zip_code );
2951
2952 -- Primary Work Location Address
2953
2954 SELECT PUS.state_abbrev, -- State
2955 nvl(HRLOC.loc_information19,HRLOC.region_1), -- County
2956 nvl(HRLOC.loc_information18,HRLOC.town_or_city), -- City
2957 nvl(HRLOC.loc_information20,HRLOC.postal_code)
2958 INTO l_loc_state_abbrev,
2959 l_loc_county,
2960 l_loc_city,
2961 l_loc_zip_code
2962 FROM hr_locations HRLOC
2963 , hr_soft_coding_keyflex HRSCKF
2964 , per_all_assignments_f ASSIGN
2965 , pay_us_states PUS
2966 where p_adjustment_date BETWEEN ASSIGN.effective_start_date
2967 AND ASSIGN.effective_end_date
2968 and ASSIGN.assignment_id = l_assignment_id
2969 and ASSIGN.soft_coding_keyflex_id = HRSCKF.soft_coding_keyflex_id
2970 and nvl(HRSCKF.segment18,ASSIGN.location_id) = HRLOC.location_id
2971 and PUS.state_abbrev = nvl(HRLOC.loc_information17,HRLOC.region_2);
2972
2973 l_loc_adr_geocode := derive_jd_geocode(p_assignment_id => l_assignment_id,
2974 p_state_abbrev => l_loc_state_abbrev,
2975 p_county_name => l_loc_county,
2976 p_city_name => l_loc_city,
2977 p_zip_code => l_loc_zip_code );
2978
2979 /* Now determine the PSD Jurisdiction Code. IF the l_jd_entered = l_per_adr_geocode
2980 then pass l_jd_entered and l_loc_adr_geocode
2981 else pass l_per_adr_geocode and l_jd_entered
2982 */
2983
2984 IF (substr(l_jd_entered,1,2) = '39' OR
2985 substr(l_per_adr_geocode,1,2) = '39' OR
2986 substr(l_loc_adr_geocode,1,2) = '39') THEN
2987
2988 IF l_jd_entered = l_per_adr_geocode THEN
2989
2993 ,p_loc_addr_geocode => l_loc_adr_geocode
2990 l_psd_jd := hr_us_ff_udf1.GET_PSD_JD_CODE( p_assignment_id => l_assignment_id
2991 ,p_tax_unit_id => p_tax_unit_id
2992 ,p_date_paid => p_adjustment_date
2994 ,p_per_addr_geocode => l_jd_entered);
2995
2996 l_element_type_subj := 'City_PSD_SUBJECT_RS';
2997 l_abbrev_element_type_subj := 'CtyPSubS';
2998 l_element_type_wh := 'City_PSD_RS';
2999 l_abbrev_element_type_wh := 'CtyPSDS';
3000
3001
3002 ELSE
3003
3004 l_psd_jd := hr_us_ff_udf1.GET_PSD_JD_CODE( p_assignment_id => l_assignment_id
3005 ,p_tax_unit_id => p_tax_unit_id
3006 ,p_date_paid => p_adjustment_date
3007 ,p_loc_addr_geocode => l_jd_entered
3008 ,p_per_addr_geocode => l_per_adr_geocode);
3009
3010 l_element_type_subj := 'City_PSD_SUBJECT_WK';
3011 l_abbrev_element_type_subj := 'CtyPSubK';
3012 l_element_type_wh := 'City_PSD_WK';
3013 l_abbrev_element_type_wh := 'CtyPSDK';
3014
3015
3016 END IF;
3017
3018 ELSE
3019
3020 l_psd_jd := '00-000000-000000';
3021
3022 END IF;
3023
3024
3025 /** sbilling **/
3026 /*
3027 ** get limits for tax, should fire once, copy variables INTO globals
3028 */
3029 -- IF (g_futa_wage_limit = 0) THEN
3030 fetch_wage_limits(p_adjustment_date,
3031 p_state_abbrev,
3032 g_futa_wage_limit,
3033 g_ss_ee_wage_limit, g_ss_er_wage_limit,
3034 g_sdi_ee_wage_limit, g_sdi1_ee_wage_limit,
3035 g_sdi_er_wage_limit, g_sui_ee_wage_limit,
3036 g_sui_er_wage_limit);
3037
3038 -- END IF;
3039
3040 -- get tax self adjust menthod for taxes FUTA, SS, MEDICARE, SUI, SDI--
3041 Open c_get_futa_self_adjust_method;
3042 fetch c_get_futa_self_adjust_method
3043 into g_futa_sa_method;
3044 if c_get_futa_self_adjust_method%NOTFOUND THEN
3045 g_futa_sa_method := 'Not Entered';
3046 end if;
3047 close c_get_futa_self_adjust_method;
3048
3049 Open c_get_ss_self_adjust_method;
3050 fetch c_get_ss_self_adjust_method
3051 into g_ss_sa_method;
3052 if c_get_ss_self_adjust_method%NOTFOUND THEN
3053 g_ss_sa_method := 'Not Entered';
3054 end if;
3055 close c_get_ss_self_adjust_method;
3056
3057 Open c_get_medi_self_adjust_method;
3058 fetch c_get_medi_self_adjust_method
3059 into g_medicare_sa_method;
3060 if c_get_medi_self_adjust_method%NOTFOUND THEN
3061 g_medicare_sa_method := 'Not Entered';
3062 end if;
3063 close c_get_medi_self_adjust_method;
3064
3065 Open c_get_sdi_self_adjust_method;
3066 fetch c_get_sdi_self_adjust_method
3067 into g_sdi_sa_method;
3068 if c_get_sdi_self_adjust_method%NOTFOUND THEN
3069 g_sdi_sa_method := 'Not Entered';
3070 end if;
3071 close c_get_sdi_self_adjust_method;
3072
3073 Open c_get_sdi1_self_adjust_method;
3074 fetch c_get_sdi1_self_adjust_method
3075 into g_sdi1_sa_method;
3076 if c_get_sdi1_self_adjust_method%NOTFOUND THEN
3077 g_sdi1_sa_method := 'Not Entered';
3078 end if;
3079 close c_get_sdi1_self_adjust_method;
3080
3081
3082 Open c_get_sui_self_adjust_method;
3083 fetch c_get_sui_self_adjust_method
3084 into g_sui_sa_method;
3085 if c_get_sui_self_adjust_method%NOTFOUND THEN
3086 g_sui_sa_method := 'Not Entered';
3087 end if;
3088 close c_get_sui_self_adjust_method;
3089
3090 open c_get_tax_group;
3091 fetch c_get_tax_group
3092 into g_tax_group;
3093 if c_get_tax_group%NOTFOUND THEN
3094 g_tax_group := 'NOT_ENTERED';
3095 end if;
3096 close c_get_tax_group;
3097
3098 /* GET THE DEFINED BALANCE ID FOR SS ER W11 Taxable used for the
3099 HIRE Act (JOBS BILL) to maintain an shadow exempt balance(s) for
3100 SS ER Liability and SS ER Taxable.
3101 */
3102
3103 BEGIN
3104 hr_utility.set_location(c_proc, 7);
3105 select defined_balance_id
3106 into l_ss_er_w11_def_bal_id
3107 from pay_balance_types bt,
3108 pay_balance_dimensions bd,
3109 pay_defined_balances db
3110 where bt.balance_name = 'SS ER W11 Taxable'
3111 and bd.dimension_name = 'Person within Government Reporting Entity Year to Date'
3112 and bd.legislation_code = 'US'
3113 and db.balance_type_id = bt.balance_type_id
3114 and bd.balance_dimension_id = db.balance_dimension_id;
3115
3116 EXCEPTION
3117 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3118 l_ss_er_w11_def_bal_id := -999;
3119 END;
3120
3121 -- basic error checking
3122 -- 1. check that Gross = Net + Taxes
3123
3124 IF (l_gross_amount <> 0) THEN
3125 /*
3126 ** stub - do the ER components require validation,
3127 ** l_futa_er + l_sui_er + l_sdi_er + l_sch_dist_wh_ee
3128 */
3129 l_total_taxes_withheld := l_fit + l_ss + l_medicare + l_sit +
3130 l_sui_ee + l_sdi_ee + l_sdi1_ee + l_county + l_city +
3134 hr_utility.set_message(801, 'PY_51134_TXADJ_TAX_NET_TOT');
3131 l_sch_dist_wh_ee;
3132
3133 IF (l_gross_amount <> l_net_amount + l_total_taxes_withheld) THEN
3135 hr_utility.raise_error;
3136 END IF;
3137
3138 END IF;
3139
3140
3141 -- 2. check that if an earnings element is provided if Gross is non-zero
3142
3143 IF (l_gross_amount <> 0 AND p_earning_element_type IS NULL) THEN
3144 hr_utility.set_message(801, 'PY_51140_TXADJ_EARN_ELE_REQ');
3145 hr_utility.raise_error;
3146 END IF;
3147
3148
3149 -- 3. check that SIT = 0 for Alaska, Florida, Nevada, New Hampshire, South Dakota,
3150 -- Tennessee, Texas, Washington, Wyoming, and the Virgin Islands
3151
3152 IF ((l_sit <> 0) AND
3153 (tax_exists(l_jd_entered, 'SIT', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N')) THEN
3154 hr_utility.set_message(801, 'PY_51141_TXADJ_SIT_EXEMPT');
3155 hr_utility.raise_error;
3156 END IF;
3157
3158 /* bug 1608907 */
3159 IF ((l_county <> 0) AND
3160 (tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N')) THEN
3161 hr_utility.set_message(801, 'PY_50980_TXADJ_COUNTY_EXEMPT');
3162 hr_utility.raise_error;
3163 END IF;
3164
3165 IF ((l_city <> 0) AND
3166 (tax_exists(l_jd_entered, 'CITY', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N')) THEN
3167 hr_utility.set_message(801, 'PY_50981_TAXADJ_CITY_EXEMPT');
3168 hr_utility.raise_error;
3169 END IF;
3170
3171 /* bug 1608907 */
3172
3173 -- 4. check that SDI = 0 for all states but California, Hawaii, New Jersey, New York,
3174 -- Puerto Rico, Rhode Island
3175 --
3176 -- first, need to ensure that the JD passed in is/was the primary assignment state at the
3177 -- time of the adjustment,
3178 -- this is because VERTEX calculations for SDI only occur for the primary work location,
3179 -- if the JD passed in is not the primary work location,
3180 -- then ensuing VERTEX calculations will not reflect the balance adjustments
3181
3182 IF ( l_sdi_ee <> 0 or l_sdi1_ee <> 0or l_sdi_er <> 0) THEN
3183 OPEN csr_sdi_check;
3184 FETCH csr_sdi_check INTO l_primary_asg_state;
3185
3186 IF csr_sdi_check%NOTFOUND THEN
3187 CLOSE csr_sdi_check;
3188 hr_utility.set_message(801, 'PY_51327_TXADJ_SDI_JD');
3189 hr_utility.raise_error;
3190 END IF;
3191
3192 CLOSE csr_sdi_check;
3193
3194 END IF;
3195
3196 IF ( l_sdi_ee <> 0) THEN
3197 --IF (p_state_abbrev NOT IN ('CA', 'HI', 'NJ', 'NY', 'RI')) THEN
3198 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N') THEN
3199 hr_utility.set_message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
3200 hr_utility.raise_error;
3201 END IF;
3202
3203 END IF;
3204
3205 IF ( l_sdi1_ee <> 0) THEN
3206 --IF (p_state_abbrev NOT IN ('CA', 'HI', 'NJ', 'NY', 'RI')) THEN
3207 IF (tax_exists(l_jd_entered, 'SDI1_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N') THEN
3208 hr_utility.set_message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
3209 hr_utility.raise_error;
3210 END IF;
3211
3212 END IF;
3213
3214 IF ( l_sdi_er <> 0) THEN
3215 --IF (p_state_abbrev NOT IN ('NJ', 'NY')) THEN
3216 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N') THEN
3217 hr_utility.set_message(801, 'PY_51142_TXADJ_SDI_EXEMPT');
3218 hr_utility.raise_error;
3219 END IF;
3220
3221 END IF;
3222
3223 -- 5. check SUI (EE) Withheld = 0 for all states unless the SUI state is
3224 -- in ('AK', 'NJ', 'PA')
3225
3226 OPEN csr_sui_geocode;
3227 FETCH csr_sui_geocode
3228 INTO g_sui_jd,
3229 g_sui_state_code,
3230 l_fed_tax_exempt,
3231 l_futa_tax_exempt,
3232 l_medicare_tax_exempt,
3233 l_ss_tax_exempt,
3234 l_ss_er_w11_exempt;
3235 CLOSE csr_sui_geocode;
3236
3237 OPEN csr_sit_exempt (cp_jurisdiction_code => l_jd_entered);
3238 FETCH csr_sit_exempt
3239 INTO l_sit_exempt,
3240 l_sdi_exempt,
3241 l_sdi1_exempt,
3242 l_sui_exempt;
3243 IF csr_sit_exempt%NOTFOUND THEN
3244 l_sit_exempt := 'N';
3245 l_sdi_exempt := 'N';
3246 l_sdi1_exempt := 'N';
3247 l_sui_exempt := 'N';
3248 END IF;
3249 CLOSE csr_sit_exempt;
3250
3251 OPEN csr_county_exempt (cp_jurisdiction_code => l_jd_entered);
3252 FETCH csr_county_exempt
3253 INTO l_cnt_exempt,
3254 l_cnt_sd_exempt;
3255 IF csr_county_exempt%NOTFOUND THEN
3256 l_cnt_exempt := 'N';
3257 l_cnt_sd_exempt := 'N';
3258 END IF;
3259 CLOSE csr_county_exempt;
3260
3261 OPEN csr_city_exempt (cp_jurisdiction_code => l_jd_entered);
3262 FETCH csr_city_exempt
3263 INTO l_cty_exempt,
3264 l_cty_sd_exempt;
3265 IF csr_city_exempt%NOTFOUND THEN
3266 l_cty_exempt := 'N';
3267 l_cty_sd_exempt := 'N';
3268 END IF;
3269 CLOSE csr_city_exempt;
3270
3271 IF (l_sui_ee <> 0) THEN
3272
3273 /*
3274 ** if the assignment is not in 'AK', 'NJ', 'PA' then SUI_EE does not apply,
3275 ** if the state found for the assignment (CA) <> the state from the
3276 ** assignment (NJ) then SUI_EE does not apply
3277 */
3278 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'N') OR
3279 (g_sui_state_code <> p_state_abbrev) THEN
3283
3280 hr_utility.set_message(801, 'PY_51328_TXADJ_SUI_EXEMPT');
3281 hr_utility.raise_error;
3282 END IF;
3284 END IF;
3285
3286 BEGIN
3287 hr_utility.set_location(c_proc, 10);
3288 SELECT consolidation_set_id
3289 INTO l_consolidation_set_id
3290 FROM PAY_CONSOLIDATION_SETS
3291 WHERE consolidation_set_name = p_consolidation_set
3292 and business_group_id = l_bg_id
3293 ;
3294 EXCEPTION
3295 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3296 hr_utility.set_message(801, 'PY_51136_TXADJ_CONSET_NOT_FND');
3297 hr_utility.raise_error;
3298 END;
3299
3300 l_jd_entered := derive_jd_geocode(p_assignment_id => l_assignment_id,
3301 p_state_abbrev => p_state_abbrev,
3302 p_county_name => p_county_name,
3303 p_city_name => p_city_name,
3304 p_zip_code => p_zip_code );
3305
3306 /** sbilling */
3307 /*
3308 ** put the old taxable balances (before any BA processing) INTO globals,
3309 ** required for subsequent excess processing
3310 */
3311 g_medicare_ee_taxable := taxable_balance('MEDICARE', 'EE', p_tax_unit_id, l_assignment_id,
3312 p_adjustment_date, NULL);
3313
3314 g_medicare_er_taxable := taxable_balance('MEDICARE', 'ER', p_tax_unit_id, l_assignment_id,
3315 p_adjustment_date, NULL);
3316
3317 g_futa_taxable := taxable_balance('FUTA', 'ER', p_tax_unit_id, l_assignment_id,
3318 p_adjustment_date, NULL);
3319
3320 g_ss_ee_taxable := taxable_balance('SS', 'EE', p_tax_unit_id, l_assignment_id,
3321 p_adjustment_date, NULL);
3322
3323 g_ss_er_taxable := taxable_balance('SS', 'ER', p_tax_unit_id, l_assignment_id,
3324 p_adjustment_date, NULL);
3325
3326 /* HIRE ACT Coding need to add the shadow taxable balance to g_ss_er_taxable */
3327 IF l_ss_er_w11_def_bal_id <> -999 THEN
3328 g_ss_er_taxable := g_ss_er_taxable +
3329 pay_balance_pkg.get_value
3330 ( p_defined_balance_id => l_ss_er_w11_def_bal_id, -- SS ER W11 Taxable
3331 p_assignment_id => l_assignment_id,
3332 p_virtual_date => p_adjustment_date -- PER GRE YTD
3333 );
3334 END IF;
3335
3336
3337
3338 /*
3339 ** the SUI/SDI balances require a JD code to derive the balance for a
3340 ** particular state
3341 */
3342 g_sdi_ee_taxable := taxable_balance('SDI', 'EE', p_tax_unit_id, l_assignment_id,
3343 p_adjustment_date, l_jd_entered);
3344
3345 g_sdi1_ee_taxable := taxable_balance('SDI1', 'EE', p_tax_unit_id, l_assignment_id,
3346 p_adjustment_date, l_jd_entered);
3347
3348 g_sdi_er_taxable := taxable_balance('SDI', 'ER', p_tax_unit_id, l_assignment_id,
3349 p_adjustment_date, l_jd_entered);
3350
3351 /*Added for Bug#9075526*/
3352 /*First check if the Automate SUI Wage Credit is selected or not.If Automate SUI
3353 Wage Credit is opted, then use the Person SUI Taxable YTD of all the states.Otherwise
3354 use Person SUI Taxable YTD of current state alone. Since Minnesota does not give
3355 credit to SUI paid in other states, Automate SUI Wage Credit Functionality will not
3356 be considered for Mineesota(State Code - 24)*/
3357
3358 open get_automate_sui_wage_credit;
3359 fetch get_automate_sui_wage_credit into l_sui_auto_credit;
3360 close get_automate_sui_wage_credit;
3361
3362 if l_sui_auto_credit = 'Y' and substr(l_jd_entered,1,2) <> '24' then
3363
3364 begin
3365
3366 select parameter_value
3367 into l_run_route
3368 from PAY_ACTION_PARAMETERS
3369 where parameter_name = 'RUN_ROUTE';
3370
3371 exception
3372 WHEN others then
3373 l_run_route := 'FALSE';
3374 end;
3375
3376 IF l_run_route <> 'TRUE' THEN
3377 l_run_route_bool := false;
3378 ELSE
3379 l_run_route_bool := true;
3380 END IF;
3381
3382 open get_sui_taxable_def_bal_id;
3383 fetch get_sui_taxable_def_bal_id into l_sui_def_bal_id;
3384
3385 /*Last date of year is passed as Virtual Date to the balance function
3386 to get the YTD value effective till the end of the year.This is similar to what
3387 is happening in the taxable_balance function*/
3388
3389 l_end_of_year_date := to_date('31/12/' || TO_CHAR(p_adjustment_date, 'YYYY'), 'DD/MM/YYYY');
3390 /* Bug 10150905 Check if the assignment exists at the end of the year prior to the
3391 balance call. If the assignment does not exist, adjust date
3392 accordingly
3393 */
3394
3395 l_asg_exists := 1;
3396
3397 select count(0)
3398 into l_asg_exists
3399 from per_assignments_f
3400 where assignment_id = l_assignment_id
3401 and l_end_of_year_date between effective_start_date and effective_end_date;
3402 --
3403 -- if assignment doesn't exist ...
3404 --
3405
3406 --
3407 IF l_asg_exists = 0 THEN
3408 --
3409 -- get the termination date ...
3410 --
3411 select max(effective_end_date)
3412 into l_balance_fetch_date
3413 from per_assignments_f
3414 where assignment_id = l_assignment_id;
3415 ELSE
3419 g_sui_ee_taxable := pay_balance_pkg.get_value_lock(l_sui_def_bal_id,
3416 l_balance_fetch_date := l_end_of_year_date;
3417 END IF;
3418
3420 l_assignment_id,
3421 l_balance_fetch_date,
3422 l_run_route_bool,
3423 'Y'
3424 );
3425
3426 fetch get_sui_taxable_def_bal_id into l_sui_def_bal_id;
3427
3428 g_sui_er_taxable := pay_balance_pkg.get_value_lock(l_sui_def_bal_id,
3429 l_assignment_id,
3430 l_balance_fetch_date,
3431 l_run_route_bool,
3432 'Y'
3433 );
3434
3435 close get_sui_taxable_def_bal_id;
3436
3437 /* Bug 12694875 Starts */
3438 -- Get the SUI EE and SUI ER taxable amounts in current state.
3439 l_sui_ee_jd_taxable := taxable_balance('SUI', 'EE', p_tax_unit_id, l_assignment_id,
3440 p_adjustment_date, l_jd_entered);
3441
3442 l_sui_er_jd_taxable := taxable_balance('SUI', 'ER', p_tax_unit_id, l_assignment_id,
3443 p_adjustment_date, l_jd_entered);
3444 /* Bug 12694875 Ends */
3445
3446 else
3447
3448 g_sui_ee_taxable := taxable_balance('SUI', 'EE', p_tax_unit_id, l_assignment_id,
3449 p_adjustment_date, l_jd_entered);
3450
3451 g_sui_er_taxable := taxable_balance('SUI', 'ER', p_tax_unit_id, l_assignment_id,
3452 p_adjustment_date, l_jd_entered);
3453
3454 end if;
3455
3456 /*End Bug#9075526*/
3457
3458 -- set global
3459 g_city_jd := l_jd_entered;
3460 g_state_jd := Substr(l_jd_entered, 1, 2) || '-000-0000';
3461 g_county_jd := Substr(l_jd_entered, 1, 6) || '-0000';
3462 g_sch_dist_jur := l_sch_dist_jur;
3463 g_classification_id := NULL;
3464 g_earnings_category := NULL;
3465 g_classification := NULL;
3466
3467
3468 -- more error checking
3469
3470 -- check the level of l_jd_entered to see if all taxes entered
3471 -- are applicable for the jurisdiction entered
3472 hr_utility.set_location(c_proc, 15);
3473
3474 IF (l_city <> 0) THEN -- jd level needed is for a city --Bug3697701 --Removed the condition
3475 l_jd_level_needed := 4; --OR l_gross_amount <> 0 from IF stmt.
3476
3477 ELSIF (l_county <> 0) THEN
3478 l_jd_level_needed := 3;
3479
3480 ELSIF (l_sit <> 0 OR l_sui_ee <> 0 OR l_sdi_ee <> 0) THEN
3481 l_jd_level_needed := 2;
3482
3483 ELSIF (l_fit <> 0 OR l_ss <> 0 OR l_medicare <> 0) THEN
3484 l_jd_level_needed := 1;
3485
3486 END IF;
3487
3488
3489 IF (l_jd_entered = g_fed_jd) THEN
3490 l_jd_level_entered := 1;
3491
3492 ELSIF (l_jd_entered = g_state_jd) THEN
3493 l_jd_level_entered := 2;
3494
3495 ELSIF (l_jd_entered = g_county_jd) THEN
3496 l_jd_level_entered := 3;
3497
3498 ELSE -- jd level entered is for a city
3499 l_jd_level_entered := 4;
3500
3501 END IF;
3502
3503
3504 -- now compare the level of jd entered against the level required
3505 IF (l_jd_level_needed > l_jd_level_entered) THEN
3506 hr_utility.set_location(c_proc, 20);
3507 hr_utility.set_message(801, 'PY_50015_TXADJ_JD_INSUFF');
3508 hr_utility.raise_error;
3509 END IF;
3510
3511
3512 -- main processing
3513 hr_utility.set_location(c_proc, 30);
3514
3515 -- first call routine to create payroll_action_id, we will only need
3516 -- one for entire tax balance adjustment process
3517 l_payroll_action_id := pay_bal_adjust.init_batch(p_payroll_id => l_payroll_id,
3518 p_batch_mode => 'NO_COMMIT',
3519 p_effective_date => p_adjustment_date,
3520 p_consolidation_set_id => l_consolidation_set_id,
3521 p_prepay_flag => p_balance_adj_prepay_flag);
3522
3523
3524 -- 4188782
3525 open get_element_details (p_earning_element_type,l_bg_id);
3526 fetch get_element_details into l_element_classification;
3527 close get_element_details;
3528
3529 IF (l_gross_amount <> 0)
3530 and (l_element_classification = 'Supplemental Earnings'
3531 or l_element_classification = 'Imputed Earnings') THEN
3532
3533 process_element(p_assignment_id => l_assignment_id,
3534 p_consolidation_set_id => l_consolidation_set_id,
3535 p_element_type => 'FSP_SUBJECT',
3536 p_abbrev_element_type => 'FSP',
3537 p_bg_id => l_bg_id,
3538 p_adjustment_date => p_adjustment_date,
3539 p_earn_amount => l_gross_amount,
3540 p_adj_amount => 0,
3541 p_jurisdiction => g_fed_jd,
3542 p_payroll_action_id => l_payroll_action_id,
3543 p_tax_unit_id => p_tax_unit_id,
3544 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3545
3549 p_consolidation_set_id => l_consolidation_set_id,
3546 /* Bug 7362837 added call to populate the SIT NON AGGREGATE balance */
3547
3548 process_element(p_assignment_id => l_assignment_id,
3550 p_element_type => 'SIT_WK_NON_AGGREGATE_RED_SUBJ_WHABLE',
3551 p_abbrev_element_type => 'SWNAGG',
3552 p_bg_id => l_bg_id,
3553 p_adjustment_date => p_adjustment_date,
3554 p_earn_amount => l_gross_amount,
3555 p_adj_amount => 0,
3556 p_jurisdiction => g_state_jd,
3557 p_payroll_action_id => l_payroll_action_id,
3558 p_tax_unit_id => p_tax_unit_id,
3559 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3560
3561 process_element(p_assignment_id => l_assignment_id,
3562 p_consolidation_set_id => l_consolidation_set_id,
3563 p_element_type => 'FIT_NON_AGGREGATE_RED_SUBJ_WHABLE',
3564 p_abbrev_element_type => 'FNAGG',
3565 p_bg_id => l_bg_id,
3566 p_adjustment_date => p_adjustment_date,
3567 p_earn_amount => l_gross_amount,
3568 p_adj_amount => 0,
3569 p_jurisdiction => g_fed_jd,
3570 p_payroll_action_id => l_payroll_action_id,
3571 p_tax_unit_id => p_tax_unit_id,
3572 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3573
3574 END IF;
3575 ------------------------------
3576
3577 IF (l_gross_amount <> 0) THEN
3578 process_element(p_assignment_id => l_assignment_id,
3579 p_consolidation_set_id => l_consolidation_set_id,
3580 p_element_type => p_earning_element_type,
3581 p_abbrev_element_type => Substr(p_earning_element_type, 1, 11),
3582 p_bg_id => l_bg_id,
3583 p_adjustment_date => p_adjustment_date,
3584 p_earn_amount => l_gross_amount,
3585 p_adj_amount => l_gross_amount,
3586 p_jurisdiction => l_jd_entered,
3587 p_payroll_action_id => l_payroll_action_id,
3588 p_tax_unit_id => p_tax_unit_id,
3589 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3590
3591 /*Added for Bug#9872952*/
3592
3593 l_profile_value := fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES');
3594
3595 IF l_profile_value = 'Y' THEN
3596
3597 OPEN c_get_wage_acc_flag;
3598 FETCH c_get_wage_acc_flag INTO l_wage_accum_enabled;
3599 CLOSE c_get_wage_acc_flag;
3600
3601 IF l_wage_accum_enabled = 'Y' THEN
3602
3603 OPEN c_get_wage_exempt;
3604 FETCH c_get_wage_exempt INTO l_wage_exempt;
3605 CLOSE c_get_wage_exempt;
3606
3607 END IF;
3608
3609 IF l_wage_exempt = 'N' THEN
3610 IF l_fed_tax_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_fed_tax_exempt */
3611
3612 process_element(p_assignment_id => l_assignment_id,
3613 p_consolidation_set_id => l_consolidation_set_id,
3614 p_element_type => 'FIT_SUBJECT',
3615 p_abbrev_element_type => 'FIT_SUBJECT',
3616 p_bg_id => l_bg_id,
3617 p_adjustment_date => p_adjustment_date,
3618 p_earn_amount => l_gross_amount,
3619 p_adj_amount => l_gross_amount,
3620 p_jurisdiction => l_jd_entered,
3621 p_payroll_action_id => l_payroll_action_id,
3622 p_tax_unit_id => p_tax_unit_id,
3623 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3624
3625 ELSIF l_fed_tax_exempt = 'Y' THEN /* Bug#16099323 */
3626
3627 process_element(p_assignment_id => l_assignment_id,
3628 p_consolidation_set_id => l_consolidation_set_id,
3629 p_element_type => 'FIT_SUBJECT',
3630 p_abbrev_element_type => 'FIT_SUBJECT',
3631 p_bg_id => l_bg_id,
3632 p_adjustment_date => p_adjustment_date,
3633 p_earn_amount => l_gross_amount,
3634 p_adj_amount => 0,
3635 p_jurisdiction => l_jd_entered,
3636 p_payroll_action_id => l_payroll_action_id,
3637 p_tax_unit_id => p_tax_unit_id,
3638 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3639
3640 END IF;
3641 END IF;
3642
3643 process_element(p_assignment_id => l_assignment_id,
3644 p_consolidation_set_id => l_consolidation_set_id,
3645 p_element_type => 'EIC_SUBJECT',
3646 p_abbrev_element_type => 'EIC_SUBJECT',
3647 p_bg_id => l_bg_id,
3648 p_adjustment_date => p_adjustment_date,
3649 p_earn_amount => l_gross_amount,
3650 p_adj_amount => l_gross_amount,
3651 p_jurisdiction => l_jd_entered,
3655
3652 p_payroll_action_id => l_payroll_action_id,
3653 p_tax_unit_id => p_tax_unit_id,
3654 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3656 IF g_medicare_sa_method <> 'Bypass Calculations' THEN
3657 IF l_medicare_tax_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_medicare_tax_exempt */
3658
3659 process_element(p_assignment_id => l_assignment_id,
3660 p_consolidation_set_id => l_consolidation_set_id,
3661 p_element_type => 'MEDICARE_SUBJECT_EE',
3662 p_abbrev_element_type => 'MEDICARE_SUBJECT_EE',
3663 p_bg_id => l_bg_id,
3664 p_adjustment_date => p_adjustment_date,
3665 p_earn_amount => l_gross_amount,
3666 p_adj_amount => l_gross_amount,
3667 p_jurisdiction => l_jd_entered,
3668 p_payroll_action_id => l_payroll_action_id,
3669 p_tax_unit_id => p_tax_unit_id,
3670 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3671
3672 process_element(p_assignment_id => l_assignment_id,
3673 p_consolidation_set_id => l_consolidation_set_id,
3674 p_element_type => 'MEDICARE_SUBJECT_ER',
3675 p_abbrev_element_type => 'MEDICARE_SUBJECT_ER',
3676 p_bg_id => l_bg_id,
3677 p_adjustment_date => p_adjustment_date,
3678 p_earn_amount => l_gross_amount,
3679 p_adj_amount => l_gross_amount,
3680 p_jurisdiction => l_jd_entered,
3681 p_payroll_action_id => l_payroll_action_id,
3682 p_tax_unit_id => p_tax_unit_id,
3683 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3684
3685 ELSIF l_medicare_tax_exempt = 'Y' THEN /* Bug#16099323 */
3686
3687 process_element(p_assignment_id => l_assignment_id,
3688 p_consolidation_set_id => l_consolidation_set_id,
3689 p_element_type => 'MEDICARE_SUBJECT_EE',
3690 p_abbrev_element_type => 'MEDICARE_SUBJECT_EE',
3691 p_bg_id => l_bg_id,
3692 p_adjustment_date => p_adjustment_date,
3693 p_earn_amount => l_gross_amount,
3694 p_adj_amount => 0,
3695 p_jurisdiction => l_jd_entered,
3696 p_payroll_action_id => l_payroll_action_id,
3697 p_tax_unit_id => p_tax_unit_id,
3698 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3699
3700 process_element(p_assignment_id => l_assignment_id,
3701 p_consolidation_set_id => l_consolidation_set_id,
3702 p_element_type => 'MEDICARE_SUBJECT_ER',
3703 p_abbrev_element_type => 'MEDICARE_SUBJECT_ER',
3704 p_bg_id => l_bg_id,
3705 p_adjustment_date => p_adjustment_date,
3706 p_earn_amount => l_gross_amount,
3707 p_adj_amount => 0,
3708 p_jurisdiction => l_jd_entered,
3709 p_payroll_action_id => l_payroll_action_id,
3710 p_tax_unit_id => p_tax_unit_id,
3711 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3712
3713 END IF;
3714 END IF;
3715
3716 IF g_ss_sa_method <> 'Bypass Collection' THEN
3717 IF l_ss_tax_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_ss_tax_exempt */
3718
3719 process_element(p_assignment_id => l_assignment_id,
3720 p_consolidation_set_id => l_consolidation_set_id,
3721 p_element_type => 'SS_SUBJECT_EE',
3722 p_abbrev_element_type => 'SS_SUBJECT_EE',
3723 p_bg_id => l_bg_id,
3724 p_adjustment_date => p_adjustment_date,
3725 p_earn_amount => l_gross_amount,
3726 p_adj_amount => l_gross_amount,
3727 p_jurisdiction => l_jd_entered,
3728 p_payroll_action_id => l_payroll_action_id,
3729 p_tax_unit_id => p_tax_unit_id,
3730 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3731
3732 process_element(p_assignment_id => l_assignment_id,
3733 p_consolidation_set_id => l_consolidation_set_id,
3734 p_element_type => 'SS_SUBJECT_ER',
3735 p_abbrev_element_type => 'SS_SUBJECT_ER',
3736 p_bg_id => l_bg_id,
3737 p_adjustment_date => p_adjustment_date,
3738 p_earn_amount => l_gross_amount,
3739 p_adj_amount => l_gross_amount,
3740 p_jurisdiction => l_jd_entered,
3741 p_payroll_action_id => l_payroll_action_id,
3742 p_tax_unit_id => p_tax_unit_id,
3743 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3744
3745 ELSIF l_ss_tax_exempt = 'Y' THEN /* Bug#16099323 */
3746
3747 process_element(p_assignment_id => l_assignment_id,
3751 p_bg_id => l_bg_id,
3748 p_consolidation_set_id => l_consolidation_set_id,
3749 p_element_type => 'SS_SUBJECT_EE',
3750 p_abbrev_element_type => 'SS_SUBJECT_EE',
3752 p_adjustment_date => p_adjustment_date,
3753 p_earn_amount => l_gross_amount,
3754 p_adj_amount => 0,
3755 p_jurisdiction => l_jd_entered,
3756 p_payroll_action_id => l_payroll_action_id,
3757 p_tax_unit_id => p_tax_unit_id,
3758 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3759
3760 process_element(p_assignment_id => l_assignment_id,
3761 p_consolidation_set_id => l_consolidation_set_id,
3762 p_element_type => 'SS_SUBJECT_ER',
3763 p_abbrev_element_type => 'SS_SUBJECT_ER',
3764 p_bg_id => l_bg_id,
3765 p_adjustment_date => p_adjustment_date,
3766 p_earn_amount => l_gross_amount,
3767 p_adj_amount => 0,
3768 p_jurisdiction => l_jd_entered,
3769 p_payroll_action_id => l_payroll_action_id,
3770 p_tax_unit_id => p_tax_unit_id,
3771 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3772 END IF;
3773
3774 END IF;
3775
3776 IF g_futa_sa_method <> 'Bypass Collection' THEN
3777 IF l_futa_tax_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_futa_tax_exempt */
3778
3779 process_element(p_assignment_id => l_assignment_id,
3780 p_consolidation_set_id => l_consolidation_set_id,
3781 p_element_type => 'FUTA_SUBJECT',
3782 p_abbrev_element_type => 'FUTA_SUBJECT',
3783 p_bg_id => l_bg_id,
3784 p_adjustment_date => p_adjustment_date,
3785 p_earn_amount => l_gross_amount,
3786 p_adj_amount => l_gross_amount,
3787 p_jurisdiction => l_jd_entered,
3788 p_payroll_action_id => l_payroll_action_id,
3789 p_tax_unit_id => p_tax_unit_id,
3790 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3791
3792 ELSIF l_futa_tax_exempt = 'Y' THEN /* Bug#16099323 */
3793
3794 process_element(p_assignment_id => l_assignment_id,
3795 p_consolidation_set_id => l_consolidation_set_id,
3796 p_element_type => 'FUTA_SUBJECT',
3797 p_abbrev_element_type => 'FUTA_SUBJECT',
3798 p_bg_id => l_bg_id,
3799 p_adjustment_date => p_adjustment_date,
3800 p_earn_amount => l_gross_amount,
3801 p_adj_amount => 0,
3802 p_jurisdiction => l_jd_entered,
3803 p_payroll_action_id => l_payroll_action_id,
3804 p_tax_unit_id => p_tax_unit_id,
3805 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3806 END IF;
3807 END IF;
3808
3809 END IF; --l_profile_option = 'Y'
3810
3811 /*Added for Bug#9872952*/
3812
3813 END IF; --l_gross_amount <> 0
3814
3815 IF (l_fit <> 0 and l_fed_tax_exempt <> 'Y') THEN --# Bug13634961 added condition to check l_fed_tax_exempt
3816 process_element(p_assignment_id => l_assignment_id,
3817 p_consolidation_set_id => l_consolidation_set_id,
3818 p_element_type => 'FIT',
3819 p_abbrev_element_type => 'FIT',
3820 p_bg_id => l_bg_id,
3821 p_adjustment_date => p_adjustment_date,
3822 p_earn_amount => l_gross_amount,
3823 p_adj_amount => l_fit,
3824 p_jurisdiction => g_fed_jd,
3825 p_payroll_action_id => l_payroll_action_id,
3826 p_tax_unit_id => p_tax_unit_id,
3827 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3828
3829 IF (l_fit_third = 'YES') THEN
3830 process_element(p_assignment_id => l_assignment_id,
3831 p_consolidation_set_id => l_consolidation_set_id,
3832 p_element_type => 'FIT 3rd Party',
3833 p_abbrev_element_type => '3F',
3834 p_bg_id => l_bg_id,
3835 p_adjustment_date => p_adjustment_date,
3836 p_earn_amount => l_gross_amount,
3837 p_adj_amount => l_fit,
3838 p_jurisdiction => g_fed_jd,
3839 p_payroll_action_id => l_payroll_action_id,
3840 p_tax_unit_id => p_tax_unit_id,
3841 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3842 END IF;
3843 END IF;
3844
3845 IF (l_ss <> 0 and l_ss_tax_exempt <> 'Y') and (g_ss_sa_method <> 'Bypass Collection') THEN --# Bug13634961 added condition to check l_ss_tax_exempt
3846 process_element(p_assignment_id => l_assignment_id,
3847 p_consolidation_set_id => l_consolidation_set_id,
3848 p_element_type => 'SS_EE',
3849 p_abbrev_element_type => 'SS',
3853 p_adj_amount => l_ss,
3850 p_bg_id => l_bg_id,
3851 p_adjustment_date => p_adjustment_date,
3852 p_earn_amount => NULL,
3854 p_jurisdiction => g_fed_jd,
3855 p_payroll_action_id => l_payroll_action_id,
3856 p_tax_unit_id => p_tax_unit_id,
3857 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3858 END IF;
3859
3860 IF (l_ss_er <> 0 and l_ss_tax_exempt <> 'Y') and (g_ss_sa_method <> 'Bypass Collection') THEN --# Bug13634961 added condition to check l_ss_tax_exempt
3861 process_element(p_assignment_id => l_assignment_id,
3862 p_consolidation_set_id => l_consolidation_set_id,
3863 p_element_type => 'SS_ER',
3864 p_abbrev_element_type => 'SER',
3865 p_bg_id => l_bg_id,
3866 p_adjustment_date => p_adjustment_date,
3867 p_earn_amount => NULL,
3868 p_adj_amount => l_ss_er,
3869 p_jurisdiction => g_fed_jd,
3870 p_payroll_action_id => l_payroll_action_id,
3871 p_tax_unit_id => p_tax_unit_id,
3872 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3873
3874
3875 /* 2010 HIRE ACT (JOBS BILL) CODE ADDED HERE. IF AN ASSIGNMENT IS MARED AS
3876 SS_ER_W11_EXEMPT THE WE MUST PROCESS THE SHADOW ELEMENT TO STORE THE TAXABLE
3877 AND LIABILITY AMOUNT INSTEAD OF THE THE BASE ELEMENT
3878 */
3879 IF l_ss_er_w11_exempt = 'Y' THEN
3880
3881
3882 process_element(p_assignment_id => l_assignment_id,
3883 p_consolidation_set_id => l_consolidation_set_id,
3884 p_element_type => 'SS_ER_W11',
3885 p_abbrev_element_type => 'SERW11',
3886 p_bg_id => l_bg_id,
3887 p_adjustment_date => p_adjustment_date,
3888 p_earn_amount => NULL,
3889 p_adj_amount => l_ss_er,
3890 p_jurisdiction => g_fed_jd,
3891 p_payroll_action_id => l_payroll_action_id,
3892 p_tax_unit_id => p_tax_unit_id,
3893 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3894
3895 END IF;
3896 END IF;
3897
3898 IF (l_medicare <> 0 and l_medicare_tax_exempt <> 'Y') and (g_medicare_sa_method <> 'Bypass Calculations') THEN --# Bug13634961 added condition to check l_medicare_tax_exempt
3899
3900 process_element(p_assignment_id => l_assignment_id,
3901 p_consolidation_set_id => l_consolidation_set_id,
3902 p_element_type => 'Medicare_EE',
3903 p_abbrev_element_type => 'Med',
3904 p_bg_id => l_bg_id,
3905 p_adjustment_date => p_adjustment_date,
3906 p_earn_amount => 0,
3907 p_adj_amount => l_medicare,
3908 p_jurisdiction => g_fed_jd,
3909 p_payroll_action_id => l_payroll_action_id,
3910 p_tax_unit_id => p_tax_unit_id,
3911 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3912
3913 END IF; /* Bug#9796821 */
3914
3915 IF (l_medicare_er <> 0 and l_medicare_tax_exempt <> 'Y') and (g_medicare_sa_method <> 'Bypass Calculations') THEN /* Bug#9796821 */
3916
3917 process_element(p_assignment_id => l_assignment_id,
3918 p_consolidation_set_id => l_consolidation_set_id,
3919 p_element_type => 'Medicare_ER',
3920 p_abbrev_element_type => 'MER',
3921 p_bg_id => l_bg_id,
3922 p_adjustment_date => p_adjustment_date,
3923 p_earn_amount => 0,
3924 p_adj_amount => l_medicare_er,
3925 p_jurisdiction => g_fed_jd,
3926 p_payroll_action_id => l_payroll_action_id,
3927 p_tax_unit_id => p_tax_unit_id,
3928 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3929 END IF;
3930
3931 IF (l_futa_er <> 0 and l_futa_tax_exempt <> 'Y') and (g_futa_sa_method <> 'Bypass Collection') THEN --# Bug13634961 added condition to check l_futa_tax_exempt
3932 process_element(p_assignment_id => l_assignment_id,
3933 p_consolidation_set_id => l_consolidation_set_id,
3934 p_element_type => 'FUTA',
3935 p_abbrev_element_type => 'FTA',
3936 p_bg_id => l_bg_id,
3937 p_adjustment_date => p_adjustment_date,
3938 p_earn_amount => 0,
3939 p_adj_amount => l_futa_er,
3940 p_jurisdiction => g_fed_jd,
3941 p_payroll_action_id => l_payroll_action_id,
3942 p_tax_unit_id => p_tax_unit_id,
3943 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3944
3945 END IF;
3946
3947 IF (l_sit <> 0 and l_sit_exempt <>'Y' ) THEN --# Bug13634961 added condition to check l_sit_exempt
3948 process_element(p_assignment_id => l_assignment_id,
3949 p_consolidation_set_id => l_consolidation_set_id,
3950 p_element_type => 'SIT_WK',
3954 p_earn_amount => l_gross_amount,
3951 p_abbrev_element_type => 'SITK',
3952 p_bg_id => l_bg_id,
3953 p_adjustment_date => p_adjustment_date,
3955 p_adj_amount => l_sit,
3956 p_jurisdiction => g_state_jd,
3957 p_payroll_action_id => l_payroll_action_id,
3958 p_tax_unit_id => p_tax_unit_id,
3959 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
3960 END IF;
3961
3962
3963 /** sbilling **/
3964 /*
3965 ** new tax element to be processed, use SIT_WK as a template
3966 */
3967
3968 OPEN csr_sd_cty_present (l_sch_dist_jur); --# Bug13634961 added to check for the school dist prsent in city
3969 FETCH csr_sd_cty_present
3970 INTO l_sd_cty_status;
3971 IF csr_sd_cty_present%NOTFOUND THEN
3972 l_sd_cty_status := 'N';
3973 END IF;
3974 CLOSE csr_sd_cty_present;
3975
3976
3977 IF l_sd_cty_status <> 'Y' THEN --# Bug13634961 added to check for the school dist prsent in county if not present in city
3978 OPEN csr_sd_cnt_present (l_sch_dist_jur);
3979 FETCH csr_sd_cnt_present
3980 INTO l_sd_cnt_status;
3981 IF csr_sd_cnt_present%NOTFOUND THEN
3982 l_sd_cnt_status := 'N';
3983 END IF;
3984 CLOSE csr_sd_cnt_present;
3985 END IF;
3986
3987
3988 IF (l_sd_cty_status ='Y' and l_cty_sd_exempt <>'Y') THEN
3989 l_sd_cty_or_cnt_exempt :='N';
3990
3991 ELSIF (l_sd_cnt_status ='Y' and l_cnt_sd_exempt <>'Y') THEN
3992 l_sd_cty_or_cnt_exempt :='N';
3993
3994 END IF;
3995
3996 IF (l_sch_dist_wh_ee <> 0 and l_sd_cty_or_cnt_exempt <>'Y') THEN --# Bug13634961 added condition to check l_sd_cty_or_cnt_exempt
3997
3998 process_element(p_assignment_id => l_assignment_id,
3999 p_consolidation_set_id => l_consolidation_set_id,
4000 p_element_type => 'County_SC_WK',
4001 p_abbrev_element_type => 'CsWK',
4002 p_bg_id => l_bg_id,
4003 p_adjustment_date => p_adjustment_date,
4004 p_earn_amount => l_gross_amount,
4005 p_adj_amount => l_sch_dist_wh_ee,
4006 p_jurisdiction => l_sch_dist_jur,
4007 p_payroll_action_id => l_payroll_action_id,
4008 p_tax_unit_id => p_tax_unit_id,
4009 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4010
4011 -- Process the Associated PSD Element for Wages only PA Jurisdictions
4012
4013 IF (l_psd_jd <> '00-000000-000000'
4014 AND substr(l_psd_jd,3,7) <> '-880000')
4015 THEN
4016
4017 process_element(p_assignment_id => l_assignment_id,
4018 p_consolidation_set_id => l_consolidation_set_id,
4019 p_element_type => 'City_PSD_SC_RS',
4020 p_abbrev_element_type => 'CsPSDWK',
4021 p_bg_id => l_bg_id,
4022 p_adjustment_date => p_adjustment_date,
4023 p_earn_amount => l_gross_amount,
4024 p_adj_amount => l_sch_dist_wh_ee,
4025 p_jurisdiction => l_psd_jd,
4026 p_payroll_action_id => l_payroll_action_id,
4027 p_tax_unit_id => p_tax_unit_id,
4028 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4029
4030 END IF;
4031
4032
4033 END IF;
4034
4035
4036
4037 IF (l_city <> 0 and l_cty_exempt <>'Y') THEN --# Bug13634961 added condition to check l_cty_exempt
4038 process_element(p_assignment_id => l_assignment_id,
4039 p_consolidation_set_id => l_consolidation_set_id,
4040 p_element_type => 'City_WK',
4041 p_abbrev_element_type => 'CtyK',
4042 p_bg_id => l_bg_id,
4043 p_adjustment_date => p_adjustment_date,
4044 p_earn_amount => l_gross_amount,
4045 p_adj_amount => l_city,
4046 p_jurisdiction => g_city_jd,
4047 p_payroll_action_id => l_payroll_action_id,
4048 p_tax_unit_id => p_tax_unit_id,
4049 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4050
4051 -- Process the PSD element / balance associated with the City Balance
4052
4053 IF ((l_psd_jd <> '00-000000-000000'
4054 AND l_element_type_wh = 'City_PSD_RS'
4055 AND substr(l_psd_jd,3,7) <> '-880000' )
4056 OR
4057 (l_psd_jd <> '00-000000-000000'
4058 AND l_element_type_wh = 'City_PSD_WK'
4059 AND substr(l_psd_jd,10,7) <> '-880000' ) )
4060 THEN
4061
4062 process_element(p_assignment_id => l_assignment_id,
4063 p_consolidation_set_id => l_consolidation_set_id,
4064 p_element_type => l_element_type_wh,
4065 p_abbrev_element_type => l_abbrev_element_type_wh,
4066 p_bg_id => l_bg_id,
4067 p_adjustment_date => p_adjustment_date,
4068 p_earn_amount => l_gross_amount,
4069 p_adj_amount => l_city,
4073 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4070 p_jurisdiction => l_psd_jd,
4071 p_payroll_action_id => l_payroll_action_id,
4072 p_tax_unit_id => p_tax_unit_id,
4074
4075 END IF;
4076
4077 END IF;
4078
4079
4080
4081 IF (l_county <> 0 and l_cnt_exempt <>'Y') THEN --# Bug13634961 added condition to check l_cnt_exempt
4082 process_element(p_assignment_id => l_assignment_id,
4083 p_consolidation_set_id => l_consolidation_set_id,
4084 p_element_type => 'County_WK',
4085 p_abbrev_element_type => 'CntyK',
4086 p_bg_id => l_bg_id,
4087 p_adjustment_date => p_adjustment_date,
4088 p_earn_amount => l_gross_amount,
4089 p_adj_amount => l_county,
4090 p_jurisdiction => g_county_jd,
4091 p_payroll_action_id => l_payroll_action_id,
4092 p_tax_unit_id => p_tax_unit_id,
4093 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4094 END IF;
4095
4096 -- subject balances are adjusted if there were any earnings
4097 IF (l_gross_amount <> 0) THEN
4098 -- SD1
4099
4100 /*
4101 ** for Medicare_ER and SS_ER the ER adjustments amounts should equal the EE
4102 ** adjustment amounts, thus l_medicare and l_ss can be used
4103 ** 01-jan-2011. This is no longer true for SS. The rates for SS EE and SS ER
4104 ** changed as of 01-jan-2011 (for 2011 only).
4105 */
4106 if g_medicare_sa_method <> 'Bypass Calculations'
4107 and l_medicare_tax_exempt <> 'Y' then
4108 process_element(p_assignment_id => l_assignment_id,
4109 p_consolidation_set_id => l_consolidation_set_id,
4110 p_element_type => 'Medicare_ER',
4111 p_abbrev_element_type => 'MER',
4112 p_bg_id => l_bg_id,
4113 p_adjustment_date => p_adjustment_date,
4114 p_earn_amount => l_gross_amount,
4115 p_adj_amount => 0,
4116 p_jurisdiction => g_fed_jd,
4117 p_payroll_action_id => l_payroll_action_id,
4118 p_tax_unit_id => p_tax_unit_id,
4119 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4120
4121 process_element(p_assignment_id => l_assignment_id,
4122 p_consolidation_set_id => l_consolidation_set_id,
4123 p_element_type => 'Medicare_EE',
4124 p_abbrev_element_type => 'Med',
4125 p_bg_id => l_bg_id,
4126 p_adjustment_date => p_adjustment_date,
4127 p_earn_amount => l_gross_amount,
4128 p_adj_amount => 0,
4129 p_jurisdiction => g_fed_jd,
4130 p_payroll_action_id => l_payroll_action_id,
4131 p_tax_unit_id => p_tax_unit_id,
4132 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4133
4134 end if;
4135
4136
4137 if g_ss_sa_method <> 'Bypass Collection'
4138 and l_ss_tax_exempt <> 'Y' then
4139
4140 process_element(p_assignment_id => l_assignment_id,
4141 p_consolidation_set_id => l_consolidation_set_id,
4142 p_element_type => 'SS_ER',
4143 p_abbrev_element_type => 'SER',
4144 p_bg_id => l_bg_id,
4145 p_adjustment_date => p_adjustment_date,
4146 p_earn_amount => l_gross_amount,
4147 p_adj_amount => 0,
4148 p_jurisdiction => g_fed_jd,
4149 p_payroll_action_id => l_payroll_action_id,
4150 p_tax_unit_id => p_tax_unit_id,
4151 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4152
4153 process_element(p_assignment_id => l_assignment_id,
4154 p_consolidation_set_id => l_consolidation_set_id,
4155 p_element_type => 'SS_EE',
4156 p_abbrev_element_type => 'SS',
4157 p_bg_id => l_bg_id,
4158 p_adjustment_date => p_adjustment_date,
4159 p_earn_amount => l_gross_amount,
4160 p_adj_amount => 0,
4161 p_jurisdiction => g_fed_jd,
4162 p_payroll_action_id => l_payroll_action_id,
4163 p_tax_unit_id => p_tax_unit_id,
4164 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4165
4166 /* 2010 HIRE ACT (JOBS BILL) CODE ADDED HERE. IF AN ASSIGNMENT IS MARED AS
4167 SS_ER_W11_EXEMPT THE WE MUST PROCESS THE SHADOW ELEMENT TO STORE THE TAXABLE
4168 AND LIABILITY AMOUNT INSTEAD OF THE THE BASE ELEMENT
4169 */
4170 IF l_ss_er_w11_exempt = 'Y' THEN
4171
4172 /* To idenify the adjustment amount we must find out the amount adjusted
4173 to SS ER Taxable and compare that to the g_ss_er_taxable fetched earlier.
4174 the difference will be the amount to the adjustment here.
4175 */
4176
4180
4177 -- 1) get the current taxable balance for SS ER
4178 l_current_ss_er_taxable_amt := 0;
4179 l_net_taxable_amount := 0;
4181 l_current_ss_er_taxable_amt := taxable_balance('SS', 'ER', p_tax_unit_id, l_assignment_id,
4182 p_adjustment_date, NULL);
4183
4184 IF l_ss_er_w11_def_bal_id <> -999 THEN
4185 l_current_ss_er_taxable_amt := l_current_ss_er_taxable_amt +
4186 pay_balance_pkg.get_value
4187 ( p_defined_balance_id => l_ss_er_w11_def_bal_id, -- SS ER W11 Taxable
4188 p_assignment_id => l_assignment_id,
4189 p_virtual_date => p_adjustment_date -- PER GRE YTD
4190 );
4191 END IF;
4192
4193 -- 2) compare current taxable to the original taxable amount to determine
4194 -- the adjustment.
4195
4196 l_net_taxable_amount := l_current_ss_er_taxable_amt - g_ss_er_taxable;
4197
4198
4199 process_element(p_assignment_id => l_assignment_id,
4200 p_consolidation_set_id => l_consolidation_set_id,
4201 p_element_type => 'SS_ER_W11',
4202 p_abbrev_element_type => 'SERW11',
4203 p_bg_id => l_bg_id,
4204 p_adjustment_date => p_adjustment_date,
4205 p_earn_amount => l_net_taxable_amount,
4206 p_adj_amount => 0,
4207 p_jurisdiction => g_fed_jd,
4208 p_payroll_action_id => l_payroll_action_id,
4209 p_tax_unit_id => p_tax_unit_id,
4210 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4211
4212 l_current_ss_er_taxable_amt := 0;
4213 l_net_taxable_amount := 0;
4214
4215 END IF;
4216
4217
4218 end if;
4219
4220 if g_futa_sa_method <> 'Bypass Collection'
4221 and l_futa_tax_exempt <> 'Y' then
4222
4223 process_element(p_assignment_id => l_assignment_id,
4224 p_consolidation_set_id => l_consolidation_set_id,
4225 p_element_type => 'FUTA',
4226 p_abbrev_element_type => 'FTA',
4227 p_bg_id => l_bg_id,
4228 p_adjustment_date => p_adjustment_date,
4229 p_earn_amount => l_gross_amount,
4230 p_adj_amount => 0,
4231 p_jurisdiction => g_fed_jd,
4232 p_payroll_action_id => l_payroll_action_id,
4233 p_tax_unit_id => p_tax_unit_id,
4234 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4235
4236 end if;
4237
4238
4239 IF (tax_exists(l_jd_entered, 'SIT', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4240 IF l_sit_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_sit_exempt */
4241
4242 hr_utility.trace('before process_element with SIT_SUBJECT_WK '||TO_CHAR(l_sit));
4243 process_element(p_assignment_id => l_assignment_id,
4244 p_consolidation_set_id => l_consolidation_set_id,
4245 p_element_type => 'SIT_SUBJECT_WK',
4246 p_abbrev_element_type => 'SITSubK',
4247 p_bg_id => l_bg_id,
4248 p_adjustment_date => p_adjustment_date,
4249 p_earn_amount => l_gross_amount,
4250 p_adj_amount => l_sit,
4251 p_jurisdiction => g_state_jd,
4252 p_payroll_action_id => l_payroll_action_id,
4253 p_tax_unit_id => p_tax_unit_id,
4254 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4255
4256 ELSIF l_sit_exempt = 'Y' THEN /* Bug#16099323 */
4257
4258 process_element(p_assignment_id => l_assignment_id,
4259 p_consolidation_set_id => l_consolidation_set_id,
4260 p_element_type => 'SIT_SUBJECT_WK',
4261 p_abbrev_element_type => 'SITSubK',
4262 p_bg_id => l_bg_id,
4263 p_adjustment_date => p_adjustment_date,
4264 p_earn_amount => l_gross_amount,
4265 p_adj_amount => 0,
4266 p_jurisdiction => g_state_jd,
4267 p_payroll_action_id => l_payroll_action_id,
4268 p_tax_unit_id => p_tax_unit_id,
4269 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4270
4271 END IF;
4272 END IF;
4273
4274 IF (tax_exists(l_jd_entered, 'CITY', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4275 IF l_cty_exempt <> 'Y' THEN /* Bug13634961 added condition to check l_cty_exempt */
4276
4277 process_element(p_assignment_id => l_assignment_id,
4278 p_consolidation_set_id => l_consolidation_set_id,
4279 p_element_type => 'City_SUBJECT_WK',
4280 p_abbrev_element_type => 'CtySubK',
4281 p_bg_id => l_bg_id,
4282 p_adjustment_date => p_adjustment_date,
4283 p_earn_amount => l_gross_amount,
4284 p_adj_amount => l_city,
4288 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4285 p_jurisdiction => g_city_jd,
4286 p_payroll_action_id => l_payroll_action_id,
4287 p_tax_unit_id => p_tax_unit_id,
4289
4290 -- Process the Associated PSD Element for Wages
4291
4292 IF ( (l_psd_jd <> '00-000000-000000'
4293 AND l_element_type_subj = 'City_PSD_SUBJECT_RS'
4294 AND substr(l_psd_jd,3,7) <> '-880000' )
4295 OR
4296 (l_psd_jd <> '00-000000-000000'
4297 AND l_element_type_subj = 'City_PSD_SUBJECT_WK'
4298 AND substr(l_psd_jd,10,7) <> '-880000' ) )
4299 THEN
4300 process_element(p_assignment_id => l_assignment_id,
4301 p_consolidation_set_id => l_consolidation_set_id,
4302 p_element_type => l_element_type_subj,
4303 p_abbrev_element_type => l_abbrev_element_type_subj,
4304 p_bg_id => l_bg_id,
4305 p_adjustment_date => p_adjustment_date,
4306 p_earn_amount => l_gross_amount,
4307 p_adj_amount => l_city,
4308 p_jurisdiction => l_psd_jd,
4309 p_payroll_action_id => l_payroll_action_id,
4310 p_tax_unit_id => p_tax_unit_id,
4311 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4312
4313 END IF;
4314
4315 ELSIF l_cty_exempt = 'Y' THEN /* Bug#16099323 */
4316
4317 process_element(p_assignment_id => l_assignment_id,
4318 p_consolidation_set_id => l_consolidation_set_id,
4319 p_element_type => 'City_SUBJECT_WK',
4320 p_abbrev_element_type => 'CtySubK',
4321 p_bg_id => l_bg_id,
4322 p_adjustment_date => p_adjustment_date,
4323 p_earn_amount => l_gross_amount,
4324 p_adj_amount => 0,
4325 p_jurisdiction => g_city_jd,
4326 p_payroll_action_id => l_payroll_action_id,
4327 p_tax_unit_id => p_tax_unit_id,
4328 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4329
4330 -- Process the Associated PSD Element for Wages
4331
4332 IF ( (l_psd_jd <> '00-000000-000000'
4333 AND l_element_type_subj = 'City_PSD_SUBJECT_RS'
4334 AND substr(l_psd_jd,3,7) <> '-880000' )
4335 OR
4336 (l_psd_jd <> '00-000000-000000'
4337 AND l_element_type_subj = 'City_PSD_SUBJECT_WK'
4338 AND substr(l_psd_jd,10,7) <> '-880000' ) )
4339 THEN
4340 process_element(p_assignment_id => l_assignment_id,
4341 p_consolidation_set_id => l_consolidation_set_id,
4342 p_element_type => l_element_type_subj,
4343 p_abbrev_element_type => l_abbrev_element_type_subj,
4344 p_bg_id => l_bg_id,
4345 p_adjustment_date => p_adjustment_date,
4346 p_earn_amount => l_gross_amount,
4347 p_adj_amount => 0,
4348 p_jurisdiction => l_psd_jd,
4349 p_payroll_action_id => l_payroll_action_id,
4350 p_tax_unit_id => p_tax_unit_id,
4351 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4352
4353 END IF;
4354 END IF;
4355
4356 END IF;
4357
4358 IF (tax_exists(l_jd_entered, 'COUNTY', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4359 IF l_cnt_exempt <> 'Y' THEN /* Bug#13634961 added condition to check l_cnt_exempt */
4360
4361 process_element(p_assignment_id => l_assignment_id,
4362 p_consolidation_set_id => l_consolidation_set_id,
4363 p_element_type => 'County_SUBJECT_WK',
4364 p_abbrev_element_type => 'CntySubK',
4365 p_bg_id => l_bg_id,
4366 p_adjustment_date => p_adjustment_date,
4367 p_earn_amount => l_gross_amount,
4368 p_adj_amount => l_county,
4369 p_jurisdiction => g_county_jd,
4370 p_payroll_action_id => l_payroll_action_id,
4371 p_tax_unit_id => p_tax_unit_id,
4372 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4373
4374 ELSIF l_cnt_exempt = 'Y' THEN /* Bug#16099323 */
4375
4376 process_element(p_assignment_id => l_assignment_id,
4377 p_consolidation_set_id => l_consolidation_set_id,
4378 p_element_type => 'County_SUBJECT_WK',
4379 p_abbrev_element_type => 'CntySubK',
4380 p_bg_id => l_bg_id,
4381 p_adjustment_date => p_adjustment_date,
4382 p_earn_amount => l_gross_amount,
4383 p_adj_amount => 0,
4384 p_jurisdiction => g_county_jd,
4385 p_payroll_action_id => l_payroll_action_id,
4386 p_tax_unit_id => p_tax_unit_id,
4387 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4388
4389 END IF;
4390 END IF;
4394 p_consolidation_set_id => l_consolidation_set_id,
4391
4392 IF nvl(p_sch_dist_jur,NULL) is not NULL THEN /* Bug#16099323 removed the condition to check l_sd_cty_or_cnt_exempt */
4393 process_element(p_assignment_id => l_assignment_id,
4395 p_element_type => 'School_SUBJECT_WK',
4396 p_abbrev_element_type => 'SchlSubK',
4397 p_bg_id => l_bg_id,
4398 p_adjustment_date => p_adjustment_date,
4399 p_earn_amount => l_gross_amount,
4400 p_adj_amount => 0,
4401 p_jurisdiction => l_sch_dist_jur,
4402 p_payroll_action_id => l_payroll_action_id,
4403 p_tax_unit_id => p_tax_unit_id,
4404 p_balance_adj_costing_flag => p_balance_adj_costing_flag);
4405
4406 END IF;
4407
4408 END IF; -- (l_gross_amount <> 0)
4409
4410 -- only Alaska, New Jersey and Pennsylvania have SUI_EE in addition
4411 -- to SUI_ER,
4412 -- may also want to check that if the jurisdiction is the SUI jurisdiction,
4413 -- only then create the SUI SUBJECT EE and ER
4414
4415 -- sd 15/5
4416 IF (tax_exists(l_jd_entered, 'SUI_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4417 IF (p_state_abbrev = g_sui_state_code) THEN
4418
4419 IF (l_gross_amount <> 0) THEN
4420
4421 process_element(p_assignment_id => l_assignment_id,
4422 p_consolidation_set_id => l_consolidation_set_id,
4423 p_element_type => 'SUI_SUBJECT_EE',
4424 p_abbrev_element_type => 'SUISubE',
4425 p_bg_id => l_bg_id,
4426 p_adjustment_date => p_adjustment_date,
4427 p_earn_amount => l_gross_amount,
4428 p_adj_amount => l_sui_ee,
4429 p_jurisdiction => g_sui_jd,
4430 p_payroll_action_id => l_payroll_action_id,
4431 p_tax_unit_id => p_tax_unit_id,
4432 p_balance_adj_costing_flag
4433 => p_balance_adj_costing_flag);
4434
4435 IF l_sui_exempt <> 'Y'
4436 and g_sui_sa_method <> 'Bypass Collection' THEN
4437 process_element(p_assignment_id => l_assignment_id,
4438 p_consolidation_set_id => l_consolidation_set_id,
4439 p_element_type => 'SUI_EE',
4440 p_abbrev_element_type => 'SUIE',
4441 p_bg_id => l_bg_id,
4442 p_adjustment_date => p_adjustment_date,
4443 p_earn_amount => l_gross_amount,
4444 p_adj_amount => 0,
4445 p_jurisdiction => g_sui_jd,
4446 p_payroll_action_id => l_payroll_action_id,
4447 p_tax_unit_id => p_tax_unit_id,
4448 p_balance_adj_costing_flag
4449 => p_balance_adj_costing_flag);
4450
4451 /* Bug 12694875 starts */
4452 /* Set the variable to display SUI EE warning message,
4453 when Employee SUI Taxable reaches the limit. */
4454 IF (l_sui_auto_credit = 'Y'
4455 AND SUBSTR(l_jd_entered,1,2) <> '24'
4456 AND l_sui_ee = 0
4457 AND g_sui_ee_taxable < g_sui_ee_wage_limit
4458 AND (g_sui_ee_taxable + g_sui_ee_tax_adj_amt = g_sui_ee_wage_limit)
4459 AND g_sui_ee_taxable > l_sui_ee_jd_taxable
4460 ) THEN
4461 hr_utility.set_location(c_proc, 50);
4462 l_sui_ee_warning := TRUE;
4463 END IF;
4464 /* Bug 12694875 ends */
4465
4466 END IF; /* l_sui_exempt */
4467
4468 END IF; /* l_gross_amount */
4469 IF ( l_sui_ee <> 0
4470 and g_sui_sa_method <> 'Bypass Collection') THEN
4471
4472 process_element(p_assignment_id => l_assignment_id,
4473 p_consolidation_set_id => l_consolidation_set_id,
4474 p_element_type => 'SUI_EE',
4475 p_abbrev_element_type => 'SUIE',
4476 p_bg_id => l_bg_id,
4477 p_adjustment_date => p_adjustment_date,
4478 p_earn_amount => 0,
4479 p_adj_amount => l_sui_ee,
4480 p_jurisdiction => g_sui_jd,
4481 p_payroll_action_id => l_payroll_action_id,
4482 p_tax_unit_id => p_tax_unit_id,
4483 p_balance_adj_costing_flag
4484 => p_balance_adj_costing_flag);
4485 END IF; /* l_sui_ee */
4486 END IF; /* state_abbrev */
4487 END IF; /* tax exists */
4488
4489 -- all states have SUI_ER
4490 IF (p_state_abbrev = g_sui_state_code) THEN
4491 IF (l_gross_amount <> 0) THEN
4492
4493 process_element(p_assignment_id => l_assignment_id,
4494 p_consolidation_set_id => l_consolidation_set_id,
4495 p_element_type => 'SUI_SUBJECT_ER',
4496 p_abbrev_element_type => 'SUISubR',
4497 p_bg_id => l_bg_id,
4498 p_adjustment_date => p_adjustment_date,
4502 p_payroll_action_id => l_payroll_action_id,
4499 p_earn_amount => l_gross_amount,
4500 p_adj_amount => l_sui_ee,
4501 p_jurisdiction => g_sui_jd,
4503 p_tax_unit_id => p_tax_unit_id,
4504 p_balance_adj_costing_flag
4505 => p_balance_adj_costing_flag);
4506
4507 IF l_sui_exempt <> 'Y'
4508 and g_sui_sa_method <> 'Bypass Collection' THEN
4509 process_element(p_assignment_id => l_assignment_id,
4510 p_consolidation_set_id => l_consolidation_set_id,
4511 p_element_type => 'SUI_ER',
4512 p_abbrev_element_type => 'SUIR',
4513 p_bg_id => l_bg_id,
4514 p_adjustment_date => p_adjustment_date,
4515 p_earn_amount => l_gross_amount,
4516 p_adj_amount => 0,
4517 p_jurisdiction => g_sui_jd,
4518 p_payroll_action_id => l_payroll_action_id,
4519 p_tax_unit_id => p_tax_unit_id,
4520 p_balance_adj_costing_flag
4521 => p_balance_adj_costing_flag);
4522
4523 /* Bug 12694875 starts */
4524 /* Set the variable to display SUI ER warning message,
4525 when Employer SUI Taxable reaches the limit. */
4526 IF (l_sui_auto_credit = 'Y'
4527 AND SUBSTR(l_jd_entered,1,2) <> '24'
4528 AND l_sui_er = 0
4529 AND g_sui_er_taxable < g_sui_er_wage_limit
4530 AND (g_sui_er_taxable + g_sui_er_tax_adj_amt = g_sui_er_wage_limit)
4531 AND g_sui_er_taxable > l_sui_er_jd_taxable
4532 ) THEN
4533 hr_utility.set_location(c_proc, 55);
4534 l_sui_er_warning := TRUE;
4535 END IF;
4536 /* Bug 12694875 ends */
4537
4538 END IF; /* l_sui_exempt */
4539 END IF; /* l_gross_amount */
4540
4541 IF ( l_sui_er <> 0
4542 and g_sui_sa_method <> 'Bypass Collection') THEN
4543 process_element(p_assignment_id => l_assignment_id,
4544 p_consolidation_set_id => l_consolidation_set_id,
4545 p_element_type => 'SUI_ER',
4546 p_abbrev_element_type => 'SUIR',
4547 p_bg_id => l_bg_id,
4548 p_adjustment_date => p_adjustment_date,
4549 p_earn_amount => 0,
4550 /** sbilling **/
4551 p_adj_amount => l_sui_er,
4552 p_jurisdiction => g_sui_jd,
4553 p_payroll_action_id => l_payroll_action_id,
4554 p_tax_unit_id => p_tax_unit_id,
4555 p_balance_adj_costing_flag
4556 => p_balance_adj_costing_flag);
4557 END IF; /* l_sui_er */
4558 END IF; /* state_abrev */
4559
4560 -- only Hawaii, New Jersey, Puerto Rico have SDI_ER
4561 IF (tax_exists(l_jd_entered, 'SDI_ER', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4562
4563 IF (l_gross_amount <> 0) THEN
4564
4565 process_element(p_assignment_id => l_assignment_id,
4566 p_consolidation_set_id => l_consolidation_set_id,
4567 p_element_type => 'SDI_SUBJECT_ER',
4568 p_abbrev_element_type => 'SDISubR',
4569 p_bg_id => l_bg_id,
4570 p_adjustment_date => p_adjustment_date,
4571 p_earn_amount => l_gross_amount,
4572 p_adj_amount => l_sdi_ee,
4573 p_jurisdiction => g_state_jd,
4574 p_payroll_action_id => l_payroll_action_id,
4575 p_tax_unit_id => p_tax_unit_id,
4576 p_balance_adj_costing_flag
4577 => p_balance_adj_costing_flag);
4578
4579 IF l_sdi_exempt <> 'Y'
4580 and g_sdi_sa_method <> 'Bypass Collection' THEN
4581
4582 process_element(p_assignment_id => l_assignment_id,
4583 p_consolidation_set_id => l_consolidation_set_id,
4584 p_element_type => 'SDI_ER',
4585 p_abbrev_element_type => 'SDIR',
4586 p_bg_id => l_bg_id,
4587 p_adjustment_date => p_adjustment_date,
4588 p_earn_amount => l_gross_amount,
4589 p_adj_amount => 0,
4590 p_jurisdiction => g_state_jd,
4591 p_payroll_action_id => l_payroll_action_id,
4592 p_tax_unit_id => p_tax_unit_id,
4593 p_balance_adj_costing_flag
4594 => p_balance_adj_costing_flag);
4595 END IF; /* if l_sdi_exempt */
4596
4597 END IF;
4598
4599 IF ( l_sdi_er <> 0
4600 and g_sdi_sa_method <> 'Bypass Collection') THEN
4601
4602 process_element(p_assignment_id => l_assignment_id,
4603 p_consolidation_set_id => l_consolidation_set_id,
4604 p_element_type => 'SDI_ER',
4605 p_abbrev_element_type => 'SDIR',
4606 p_bg_id => l_bg_id,
4610 p_jurisdiction => g_state_jd,
4607 p_adjustment_date => p_adjustment_date,
4608 p_earn_amount => 0,
4609 p_adj_amount => l_sdi_er,
4611 p_payroll_action_id => l_payroll_action_id,
4612 p_tax_unit_id => p_tax_unit_id,
4613 p_balance_adj_costing_flag
4614 => p_balance_adj_costing_flag);
4615 END IF; /* if l_sdi_er */
4616
4617 END IF; /* if tax exists */
4618
4619 -- only California, Hawaii, New Jersey, New York, Rhode Island,
4620 -- and Puerto Rico have SDI_EE
4621
4622 IF (tax_exists(l_jd_entered, 'SDI_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4623
4624 IF (l_gross_amount <> 0) THEN
4625 process_element(p_assignment_id => l_assignment_id,
4626 p_consolidation_set_id => l_consolidation_set_id,
4627 p_element_type => 'SDI_SUBJECT_EE',
4628 p_abbrev_element_type => 'SDISubE',
4629 p_bg_id => l_bg_id,
4630 p_adjustment_date => p_adjustment_date,
4631 p_earn_amount => l_gross_amount,
4632 p_adj_amount => l_sdi_ee,
4633 p_jurisdiction => g_state_jd,
4634 p_payroll_action_id => l_payroll_action_id,
4635 p_tax_unit_id => p_tax_unit_id,
4636 p_balance_adj_costing_flag
4637 => p_balance_adj_costing_flag);
4638
4639 IF l_sdi_exempt <> 'Y'
4640 AND g_sdi_sa_method <> 'Bypass Collection' THEN
4641
4642 process_element(p_assignment_id => l_assignment_id,
4643 p_consolidation_set_id => l_consolidation_set_id,
4644 p_element_type => 'SDI_EE',
4645 p_abbrev_element_type => 'SDIE',
4646 p_bg_id => l_bg_id,
4647 p_adjustment_date => p_adjustment_date,
4648 p_earn_amount => l_gross_amount,
4649 p_adj_amount => 0,
4650 p_jurisdiction => g_state_jd,
4651 p_payroll_action_id => l_payroll_action_id,
4652 p_tax_unit_id => p_tax_unit_id,
4653 p_balance_adj_costing_flag
4654 => p_balance_adj_costing_flag);
4655
4656 END IF; /* l_sdi_exempt */
4657
4658 END IF; /* l_gross-amount */
4659
4660 IF ( l_sdi_ee <> 0
4661 and g_sdi_sa_method <> 'Bypass Collection') THEN
4662 process_element(p_assignment_id => l_assignment_id,
4663 p_consolidation_set_id => l_consolidation_set_id,
4664 p_element_type => 'SDI_EE',
4665 p_abbrev_element_type => 'SDIE',
4666 p_bg_id => l_bg_id,
4667 p_adjustment_date => p_adjustment_date,
4668 p_earn_amount => 0,
4669 p_adj_amount => l_sdi_ee,
4670 p_jurisdiction => g_state_jd,
4671 p_payroll_action_id => l_payroll_action_id,
4672 p_tax_unit_id => p_tax_unit_id,
4673 p_balance_adj_costing_flag
4674 => p_balance_adj_costing_flag);
4675 END IF;
4676
4677 END IF; /* if tax exists */
4678
4679 IF (tax_exists(l_jd_entered, 'SDI1_EE', p_adjustment_date, l_per_adr_geocode, l_loc_adr_geocode) = 'Y') THEN
4680
4681 IF (l_gross_amount <> 0) THEN
4682
4683 IF l_sdi1_exempt <> 'Y'
4684 AND g_sdi1_sa_method <> 'Bypass Collection' THEN
4685
4686 process_element(p_assignment_id => l_assignment_id,
4687 p_consolidation_set_id => l_consolidation_set_id,
4688 p_element_type => 'SDI1_EE',
4689 p_abbrev_element_type => 'SDI1E',
4690 p_bg_id => l_bg_id,
4691 p_adjustment_date => p_adjustment_date,
4692 p_earn_amount => l_gross_amount,
4693 p_adj_amount => 0,
4694 p_jurisdiction => g_state_jd,
4695 p_payroll_action_id => l_payroll_action_id,
4696 p_tax_unit_id => p_tax_unit_id,
4697 p_balance_adj_costing_flag
4698 => p_balance_adj_costing_flag);
4699
4700 END IF; /* l_sdi_exempt */
4701
4702 END IF; /* l_gross-amount */
4703
4704 IF ( l_sdi1_ee <> 0
4705 and g_sdi1_sa_method <> 'Bypass Collection') THEN
4706 process_element(p_assignment_id => l_assignment_id,
4707 p_consolidation_set_id => l_consolidation_set_id,
4708 p_element_type => 'SDI1_EE',
4709 p_abbrev_element_type => 'SDI1E',
4710 p_bg_id => l_bg_id,
4711 p_adjustment_date => p_adjustment_date,
4712 p_earn_amount => 0,
4713 p_adj_amount => l_sdi1_ee,
4714 p_jurisdiction => g_state_jd,
4715 p_payroll_action_id => l_payroll_action_id,
4716 p_tax_unit_id => p_tax_unit_id,
4717 p_balance_adj_costing_flag
4718 => p_balance_adj_costing_flag);
4719 END IF;
4720
4721 END IF; /* if tax exists */
4722
4723 -- set some of the return out parameters
4724 p_payroll_action_id := l_payroll_action_id;
4725 p_sui_ee_warning := l_sui_ee_warning;
4726 p_sui_er_warning := l_sui_er_warning;
4727
4728 IF hr_utility.check_warning THEN
4729 l_create_warning := TRUE;
4730 hr_utility.clear_warning;
4731 END IF;
4732
4733 IF(p_validate) THEN
4734 RAISE hr_api.validate_enabled;
4735 END IF;
4736
4737 hr_utility.trace('Finished Routine, all adjustments commited');
4738 hr_utility.trace('Payroll_action_id = '||TO_CHAR(l_payroll_action_id));
4739
4740 pay_bal_adjust.process_batch(p_payroll_action_id);
4741
4742
4743 EXCEPTION
4744 WHEN hr_api.validate_enabled THEN
4745 --
4746 -- As the Validate_Enabled exception has been raised
4747 -- we must rollback to the savepoint
4748 --
4749 ROLLBACK TO create_tax_bal_adjustment;
4750 --
4751 -- Only set output warning arguments
4752 -- (Any key or derived arguments must be set to NULL
4753 -- when validation only mode is being used.)
4754 --
4755 p_payroll_action_id := NULL;
4756 p_create_warning := l_create_warning;
4757 hr_utility.trace('Validate Enabled, no commits are made');
4758
4759 WHEN OTHERS THEN
4760 -- Unexpected error detected.
4761 ROLLBACK TO create_tax_bal_adjustment;
4762 RAISE;
4763
4764 END create_tax_balance_adjustment;
4765
4766 END pay_us_tax_bals_adj_api;
[Home] [Help]