1 PACKAGE BODY pay_us_web_w4
2 /* $Header: pyuswbw4.pkb 120.30.12020000.3 2013/03/20 09:39:29 emunisek ship $ *
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2000 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_web_w4
21
22 Description : Contains utility and back end procedures for the
23 online W4 Form.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Description
30 ---- ---- ---- -----------
31 3-MAR-2000 dscully 110.0 Created.
32
33 24-MAR-2000 dscully 115.0 Created.
34 10-APR-2000 dscully 115.1 Added itemtype and process parameters
35 01-JUN-2000 dscully 115.2 Changed calls from row handlers to bpi.
36 22-JUN-2000 dscully 115.6 Fixed bug caused by fed and states having
37 different codes to mean the same filing status
38 28-JUN-2000 dscully 115.7 Fixed bug caused by Arizona defaulting not
39 by law but by the needs of our system
40 Fixed bug caused by benefits assignments being
41 marked primary.
42 Added GRE to audit table so when audit
43 report is run it uses the real GRE value
44 at that date, not the current one.
45 19-FEB-2001 meshah 115.8 Changed update_tax_records to insert one
46 record for Federal and State each.
47 02-MAR-2001 meshah 115.9 now inserting source3 in pay_stat_trans_audit
48 and attribute_category = W4 State for state
49 records.
50 05-MAR-2001 meshah 115.10 Bug 1668926 is fixed now. We need to check the
51 filing status and exemptions between federal and
52 state records before updating state information.
53 Now inserting source3 for federal also.
54 15-MAR-2001 meshah 115.11 Now truncating transaction_date before inserting
55 in the pay_stat_trans_audit table.
56 27-APR-2001 meshah 115.12 Made changes to the package so that the same
57 package works with the new techstack.
58 1. Added procedure check_update_status.
59 2. Removed the checking from the cursors to get
60 retiree employees also.
61 3. New parameters and additional logic in procedure
62 validate_submission and update_tax_records.
63 4. New procedure get_transaction_values.
64 5. New procedure update_w4_info.
65 6. Removed the old calls to review and confirmation
66 page also the old workflow is removed.
67 03-MAY-2001 meshah 115.13 Commented the code where we check if the field id
68 displayed or not in validate_submission procedure.
69 07-MAY-2001 meshah 115.14 Now getting the business group id for the person
70 changes made in update_tax_record.
71 11-MAY-2001 meshah 115.15 new parameter to validate_submission and
72 new function GET_STATE_LIST.
73 11-MAY-2001 meshah 115.16 new source4 and source4_type for State record
74 in update_tax_record.Also removed item_type and
75 added transaction_id.
76 23-MAY-2001 meshah 115.17 added a order by clause in get_transaction_values.
77 25-MAY-2001 meshah 115.18 New parameter in validate_submission, inserting
78 filing_status_code into transaction table.
79 In get_transaction_values fetching one more
80 value of filing_status_code.
81 02-AUG-2001 meshah 115.19 setting the value of l_exempt_status_code to
82 Yes or No in validate_submission.
83 setting the value of l_exempt to Y or N in
84 update_w4_info.
85 Updated update_tax_record, cursor c_fed_tax_rows
86 now selecting paf.primary_flag and depending
87 on the value of the primary flag inserting
88 parent_transaction_id.
89 20-AUG-2001 meshah 115.20 Added two more parameter to validate_submission.
90 Removed procedure check_update_status.
91 04-SEP-2001 meshah 115.01 adding p_original_exempt. showing message if
92 there is a change in filing status or allowances
93 or exempt status. for this we are now saving
94 the actual Filing status, allowances and exempt
95 satus in transaction tables. changed update_tax_records
96 also.
97 10-SEP-2001 meshah 115.02 changed the field name from FitAdditionalTax to
98 TaxString and AgreementFlag to Agreementflag
99 because the names changed in AK.
100 bugs 1986371 and 1983167.
101 20-SEP-2001 meshah 115.03 setting l_state_count = 1 when the cursor fetched
102 a record. Depending on the value of l_state_count
103 we print the state message. bug#2004478.
104 01-OCT-2001 meshah 115.04 Bug 2006653. Now selecting sit_additional_amount
105 in c_state_tax_record cursor in update_tax_record
106 procedure and updating with the same value.
107 Bug 2015129. In update_tax_record we are now
108 opening the state cursor within the Fed cursor
109 and passing the assignment_id from fed to state.
110 Bug 2015300. Now checking for all the values that
111 are returned from the profile value. (None,null,
112 Primary and All).
113 15-OCT-2001 meshah 115.05 bug 2027211, now while validating the record we are
114 calling the update_tax_records procedure to insert
115 the record and also to check if the state will be
116 changed, if yes then set the global variable g_state_list.
117 bug 2038691. Commented the checking for the start
118 and end date with sysdate and the 31-dec-4712.
119 20-DEC-2002 meshah 115.06 added index hints for c_excess_over_state and
120 c_future_state_recs cursors for 1159.
121 also added nocopy and dbdrv.
122 28-OCT-2003 meshah 115.08 p_exempt_state_list parameters has been
123 added to validate_submission. Function
124 get_state_list has been modified.
125 update_tax_records and validate_submission
126 have been changed to get the states that
127 do not default the exempt status from federal.
128 Bug 3151569. Also now we are updating the
129 transaction values if transaction exists.
130 24-Nov-2003 meshah We insert only those states that are affected
131 by the W4 change.
132 04-DEC-2003 meshah 115.09 defaulting cu_sit_exempt to l_state_exempt when
133 the state does not default from federal for
134 exempt status.
135 09-DEC-2003 meshah 115.10 made changes to the code since the filing status
136 have been changed for certain states. Example
137 AZ, LA, MA, WV these states don't have a
138 equvivalent code for Married at federal.
139 PROCEDURE update_tax_records has been updated.
140 09-APR-2004 meshah 115.11 p_original_aa parameter has been added to procedure
141 validate_submission. Also now we are storing
142 original additional amount in the transaction
143 since we are using a VO to display before and after
144 values in the Review Page.
145 07-MAR-2005 meshah 115.12 for bug 4225569 removed the checking for
146 SUI_WAGEBASE_OVERRIDE from function
147 check_update_status.
148 Also added SUI_WAGEBASE_OVERRIDE in
149 update_tax_records so that the same amount
150 gets updated when update the state records.
151 23-MAY-2005 rsethupa 115.13 Bug 4070034: Changes to insert new field
152 P_LAST_NAME_DIFF in Audit Table
153 16-JUN-2005 rsethupa 115.14 Bug 4204103: Added check for comparing the no. of
154 Tax Exemptions at the Fed and State level
155 26-sep-2005 jgoswami 115.15 Bug 4599982 - Added update_alien_tax_records
156 to support pqp calls to old w4 packages.
157 26-oct-2005 jgoswami 115.16 Bug 4671389 modified update_tax_records.
158 03-nov-2005 jgoswami 115.17 Bug 4671389 modified update_tax_records.
159 03-dec-2005 jgoswami 115.18 Bug 4707873 when SUPPLEMENTAL rate for FIT/SIT
160 override is enetred then error message should
161 not be received.
162 17-jan-2006 jgoswami 115.19 Bug 4956850 - added new parameter
163 p_transaction_type and p_source_name to
164 procedure update_tax_records
165
166 30-jun-2006 jgoswami 115.21 Bug 5334081 - Not changing state Filing Status
167 to Federal Filing Status when State Does Not
168 Follow Federal. Arizona does NOT use marital
169 status in calculating SIT , they have % of FIT
170 which the employee elects.
171
172 11-aug-2006 jgoswami 115.22 Bug 5198005 - Suppress W4 Notifications for the
173 W4 forms that are exempt or at a level above 10
174 allowances as IRS does not require Employer to
175 Send it. Based on the value of the DFF the
176 Notification will be sent or suppressed. Default
177 the Notification will be Suppressed.
178 Similarly Information message on Review page is
179 also suppressed.
180 Created function get_org_context.
181
182 06-sep-2006 jgoswami 115.23 Bug 3852021 - Modified validate_submission
183 changed data type for p_additional_amount,
184 p_original_aa from varchar2 to Number .
185
186 05-dec-2006 vaprakas 115.24 Bug 5607135 - Modified the procedure
187 validate submission to implement check for NRAs.
188 13-AUG-2007 vaprakas Bug 6200677 modified
189 17-Nov-2007 sudedas SS W4 Added Function Fed_State_Filing_Status_Match
190 Modified update_tax_records,
191 update_w4_info,
192 validate_submission.
193 19-Nov-2007 sudedas 6333947 Included Changes for this bug.
194 21-Nov-2007 sudedas Changed update_tax_records, Addl Tax Defaulted.
195 28-Nov-2007 sudedas Fixed some issues identified during QA
196 115.30 Fixed Informational Message Display Issue.
197 19-May-2008 Pannapur 115.31 Modified update_tax_records to Fix Bug no 7005814
198 28-may-2008 asgugupt 115.32 Modified for bug no 7121877
199 04-Nov-2008 Pannapur 115.33 Modified for Bug no 7521930
200 20-Apr-2009 kagangul 115.34 Bug# 7524676 : Online W4 should not change the Filing
201 Status for Arkansas. Employee need to submit AR4EC for the same.
202 29-May-2009 kagangul 115.35 Bug# 8518956 : Kansas employee need to submit K-4 State Tax Form,
203 shouldn't follow Federal.
204 Also changing the way solution was provided for Bug# 7524676
205 in the previous version 115.34.
206 22-Jun-2009 kagangul 115.36 Bug# 6346579
207 Changing the line
208 "nvl(stif.sta_information9,'N') exmpt_status_state_as_fed" to
209 "nvl(stif.sta_information9,'Y') exmpt_status_state_as_fed"
210 as we don't deliver this inforamtion. However this change
211 shouldn't introduce any unwanted side effects as we use
212 "Fed_State_Filing_Status_Match" function to check whether
213 state should follow the federal before updating state records.
214 03-Aug-2009 kagangul 115.37 Added code to check if FIT Exempt is being enabled by a NRA
215 employee in which case 'Fed_State_Filing_Status_Match' should
216 return False irrespective of the State the employee belongs to.
217 Also, for other employees making sure it displays the proper message.
218
219 15-oct-2010 RNESTOR 115.39 bug fix 9546309 FUNCTION Fed_State_Filing_Status_Match
220 for State that may or may not follow fed.
221 115.40 GSCC errors
222 28-oct-2010 RNESTOR 115.41 Closed open pipe
223 12-APR-2011 TCLEWIS 115.42 Bug 11928328. Restructured all If statements with
224 OR conditions to ensure all conditions are met.
225 27-AUG-2012 SBACHU 115.43 14109584.The length of the variable that holds the message
226 in get_state_list function has been properly set
227 and cursor c_state_tax_rows has been changed
228 to loop correctly as per the number of records fetched.
229 13-MAR-2013 EMUNISEK 115.44 Bug#16465177. Made changes so that the "Additional Amount Withheld"
230 specified on Employee Self-Service "Tax Form" is updated only to the
231 Federal Tax Record. State Tax Records should not be updated with this
232 Additional Amount as State Additional Tax amounts will be different.
233 Only in the event of Federal Exempt selection getting propagated to
234 States. since we are clearing Allowances at State level along with
235 Federal Level, we can clear Additional Tax Amount at State Level also
236 as we are doing at Federal level.
237 *******************************************************************/
238 AS
239
240 /******************************************************************
241 ** private package global declarations
242 ******************************************************************/
243 gv_package_name VARCHAR2(50) := 'pay_us_web_w4';
244 gv_exempt_change VARCHAR2(2) := 'N';
245
246 -- The following Function has been Added to correct inconsistent behaviour of State W-4
247 -- For the States that should follow Federal W-4, Filing Status, Allowances etc. should be
248 -- defaulted from Federal Information for them. Whereas for States that May OR May NOT
249 -- follow Federal W-4, if Filing Status does not match with State W-4 the information will
250 -- NOT be copied and an Informational Message will be displayed to Customer.
251 --
252
253 FUNCTION Fed_State_Filing_Status_Match(
254 p_state_code IN pay_us_states.state_code%TYPE
255 ,p_state_org_filing_status_code IN pay_us_emp_state_tax_rules_f.filing_status_code%TYPE
256 ,p_fed_org_filing_status_code IN pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
257 ,p_fed_org_wa IN pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
258 ,p_state_org_wa IN pay_us_emp_state_tax_rules_f.withholding_allowances%TYPE
259 ,p_fed_exmpt_cnt IN NUMBER
260 ,p_state_empt_cnt IN NUMBER
261 ,p_new_filing_status_code IN OUT NOCOPY pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
262 )
263 RETURN BOOLEAN IS
264
265 CURSOR cur_state_name(p_st_cd IN VARCHAR2) IS
266 SELECT state_name
267 FROM pay_us_states
268 WHERE state_code = p_st_cd;
269
270 CURSOR cur_filing_status_meaning(p_state_code VARCHAR2
271 ,p_filing_status_code VARCHAR2) IS
272 SELECT meaning
273 FROM hr_lookups
274 WHERE lookup_type = 'US_FS_'||p_state_code
275 AND lpad(lookup_code,2,'0') = p_filing_status_code
276 AND application_id = 800
277 AND enabled_flag = 'Y';
278
279 lv_state_name pay_us_states.state_name%TYPE;
280 lv_fed_03_meaning hr_lookups.meaning%TYPE;
281 lv_state_fs_meaning hr_lookups.meaning%TYPE;
282 lv_state_04_fs_meaning hr_lookups.meaning%TYPE;
283
284 BEGIN
285 hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286 hr_utility.trace('p_state_code := '||p_state_code);
287 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288 hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289 hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290 hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291 hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292 hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293 hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
294
295 lv_fed_03_meaning := REPLACE(UPPER('Married, but Withhold at Higher Single Rate'), ',');
296
297 OPEN cur_state_name(p_state_code);
298 FETCH cur_state_name INTO lv_state_name;
299 CLOSE cur_state_name;
300
301 hr_utility.trace('lv_state_name := '||lv_state_name);
302
303 OPEN cur_filing_status_meaning(p_state_code
304 ,p_state_org_filing_status_code) ;
305 FETCH cur_filing_status_meaning INTO lv_state_fs_meaning;
306 CLOSE cur_filing_status_meaning;
307
308 hr_utility.trace('lv_state_fs_meaning := '||lv_state_fs_meaning);
309
310 OPEN cur_filing_status_meaning(p_state_code
311 ,'04') ;
312 FETCH cur_filing_status_meaning INTO lv_state_04_fs_meaning;
313 CLOSE cur_filing_status_meaning;
314
315 hr_utility.trace('lv_state_04_fs_meaning := '||lv_state_04_fs_meaning);
316
317 /* Bug 6346579 : Start. NRA employee trying to enable FIT Exempt. No need to proceed. */
318 IF (g_NRA_flag = 'Y') THEN
319 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
320 IF g_not_matching_state_list IS NOT NULL THEN
321 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
322 ELSE
323 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
324 END IF;
325 g_nonmatch_cntr := g_nonmatch_cntr + 1;
326 END IF;
327 hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
328 hr_utility.trace('Not Going to Update State Info From Federal.');
329 RETURN FALSE;
330 END IF;
331 /* Bug 6346579 : End */
332
333 /* Bug # 8518956 : Kansas employee need to submit K-4 State Tax Form, shouldn't follow Federal.
334 Hence removing State Code '17' from the below condition */
335 /* IF p_state_code IN ('06','08','13','17', '24', '27', '28', '32', '35', '37', '38', '40', '41', '45') THEN */
336 IF p_state_code IN ('06','08','13', '24', '27', '28', '32', '35', '37', '38', '40', '41', '45') THEN
337 -- For States that should Follow Federal
338 IF p_fed_org_filing_status_code <> p_new_filing_status_code THEN
339 -- Filing Status Changed during Transaction
340
341 IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
342 AND p_state_org_wa = p_fed_org_wa
343 AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
344
345 IF p_new_filing_status_code = '03' THEN
346 /* Modified for bug no 7521930*/
347 IF p_state_code NOT IN ('27','38','41','45') THEN
348 p_new_filing_status_code := '04';
349 hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
350 RETURN TRUE;
351 ELSE
352 hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
353 RETURN TRUE;
354 END IF ;
355
356 ELSE
357 hr_utility.trace('p_new_filing_status_code 2 := ' ||p_new_filing_status_code);
358 RETURN TRUE;
359 END IF;
360
361 ELSIF (p_fed_org_filing_status_code = '03' AND p_state_org_filing_status_code = '04'
362 AND p_state_org_wa = p_fed_org_wa
363 AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
364 hr_utility.trace('p_new_filing_status_code 2 := ' ||p_new_filing_status_code);
365 RETURN TRUE;
366 ELSE
367 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
368
369 IF g_not_matching_state_list IS NOT NULL THEN
370 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
371 ELSE
372 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
373 END IF;
374 g_nonmatch_cntr := g_nonmatch_cntr + 1;
375 END IF;
376
377 p_new_filing_status_code := p_state_org_filing_status_code ;
378 hr_utility.trace('p_new_filing_status_code 3 := ' || p_new_filing_status_code);
379 hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
380 hr_utility.trace('Not Going to Update State Info From Federal.');
381 RETURN FALSE;
382 END IF;
383
384 ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
385
386 hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
387 hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
388 hr_utility.trace('above should match here');
389 IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
390 AND p_state_org_wa = p_fed_org_wa
391 AND p_state_empt_cnt = p_fed_exmpt_cnt) OR
392
393 (p_fed_org_filing_status_code = '03' AND p_state_org_filing_status_code = '04'
394 AND p_state_org_wa = p_fed_org_wa
395 AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
396
397 p_new_filing_status_code := p_state_org_filing_status_code;
398 hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
399 hr_utility.trace('RETURNING TRUE HERE 2 ' ||p_new_filing_status_code);
400 RETURN TRUE;
401 ELSE
402 hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
403 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
404
405 IF g_not_matching_state_list IS NOT NULL THEN
406 hr_utility.trace('g_not_matchingIS NOT NULL THEN := '||g_not_matching_state_list);
407 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
408 ELSE
409 hr_utility.trace('g_not_matchingIS NULL THEN := '||g_not_matching_state_list);
410 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
411 END IF;
412 g_nonmatch_cntr := g_nonmatch_cntr + 1;
413 END IF;
414
415 p_new_filing_status_code := p_state_org_filing_status_code ;
416 hr_utility.trace('p_new_filing_status_code 4 := ' || p_new_filing_status_code);
417 hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
418 hr_utility.trace('Not Going to Update State Info From Federal.');
419
420 RETURN FALSE;
421 END IF;
422 END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
423
424 /* Bug # 7524676
425 Online W4 should not change the Filing Status for Arkansas.
426 Employee need to submit AR4EC for the same. Hence removed state code '04'
427 from the following condition
428 ELSIF p_state_code IN ('04', '05', '19', '22', '31', '46', '49', '50') THEN */
429
430 ELSIF p_state_code IN ('05', '19', '22', '31', '46', '49', '50') THEN
431 -- For States that May or May Not Follow Federal
432 hr_utility.trace('States that May or May Not Follow Federal ');
433 IF p_fed_org_filing_status_code <> p_new_filing_status_code THEN
434 -- Filing Status Changed during Transaction
435
436 IF p_state_code NOT IN ( '19', '22' ) THEN
437 -- LA and MA are Exceptions (Different Filing Status Codes for "Single (01)"/"Married (02)"
438
439 IF (( p_state_org_filing_status_code = p_fed_org_filing_status_code
440 AND p_state_org_wa = p_fed_org_wa
441 AND p_state_empt_cnt = p_fed_exmpt_cnt
442 )
443 OR
444 (p_fed_org_filing_status_code = '03'
445 AND REPLACE(UPPER(lv_state_fs_meaning), ',') = lv_fed_03_meaning
446 AND p_state_org_wa = p_fed_org_wa
447 AND p_state_empt_cnt = p_fed_exmpt_cnt
448 )
449 ) THEN
450
451 IF p_new_filing_status_code IN ('01', '02') THEN
452 hr_utility.trace('Returning True Here 01 or 02');
453 hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
454 RETURN TRUE;
455 ELSIF p_new_filing_status_code = '03' AND
456 REPLACE(UPPER(lv_state_04_fs_meaning), ',') = lv_fed_03_meaning THEN
457 p_new_filing_status_code := '04';
458 RETURN TRUE;
459 ELSIF p_new_filing_status_code = '03' THEN
460 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
461
462 IF g_not_matching_state_list IS NOT NULL THEN
463 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
464 ELSE
465 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
466 END IF;
467 g_nonmatch_cntr := g_nonmatch_cntr + 1;
468 END IF;
469 p_new_filing_status_code := p_state_org_filing_status_code ;
470 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
471 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
472 hr_utility.trace('Not Going to Update State Info From Federal.');
473 RETURN FALSE;
474 END IF;
475 ELSE
476 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
477
478 IF g_not_matching_state_list IS NOT NULL THEN
479 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
480 ELSE
481 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
482 END IF;
483 g_nonmatch_cntr := g_nonmatch_cntr + 1;
484 END IF;
485 p_new_filing_status_code := p_state_org_filing_status_code ;
486 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
487 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
488 hr_utility.trace('Not Going to Update State Info From Federal.');
489 RETURN FALSE;
490
491 END IF;
492
493 ELSIF p_state_code = '19' THEN -- For LA, Single --> '02', Married --> '03'
494
495 IF (( p_state_org_filing_status_code = p_fed_org_filing_status_code
496 AND p_state_org_wa = p_fed_org_wa
497 AND p_state_empt_cnt = p_fed_exmpt_cnt
498 )
499 OR ( p_fed_org_filing_status_code = '01'
500 AND p_state_org_filing_status_code = '02'
501 AND p_state_org_wa = p_fed_org_wa
502 AND p_state_empt_cnt = p_fed_exmpt_cnt
503 )
504 OR ( p_fed_org_filing_status_code = '02'
505 AND p_state_org_filing_status_code = '03'
506 AND p_state_org_wa = p_fed_org_wa
507 AND p_state_empt_cnt = p_fed_exmpt_cnt
508 )
509 ) THEN
510
511
512 IF p_new_filing_status_code = '01' THEN
513 p_new_filing_status_code := '02';
514 RETURN TRUE;
515 ELSIF p_new_filing_status_code = '02' THEN
516 p_new_filing_status_code := '03';
517 RETURN TRUE;
518
519 ELSIF p_new_filing_status_code = '03' THEN
520 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
521
522 IF g_not_matching_state_list IS NOT NULL THEN
523 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
524 ELSE
525 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
526 END IF;
527 g_nonmatch_cntr := g_nonmatch_cntr + 1;
528 END IF;
529 p_new_filing_status_code := p_state_org_filing_status_code ;
530 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
531 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
532 hr_utility.trace('Not Going to Update State Info From Federal.');
533 RETURN FALSE;
534 END IF;
535 ELSE
536 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
537
538 IF g_not_matching_state_list IS NOT NULL THEN
539 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
540 ELSE
541 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
542 END IF;
543 g_nonmatch_cntr := g_nonmatch_cntr + 1;
544 END IF;
545 p_new_filing_status_code := p_state_org_filing_status_code ;
546 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
547 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
548 hr_utility.trace('Not Going to Update State Info From Federal.');
549 RETURN FALSE;
550 END IF;
551
552 ELSIF p_state_code = '22' THEN -- For MA, Single (04), Married (04) --> Other than Head of Household
553
554 IF ( ( p_state_org_filing_status_code = p_fed_org_filing_status_code
555 AND p_state_org_wa = p_fed_org_wa
556 AND p_state_empt_cnt = p_fed_exmpt_cnt
557 )
558 OR
559 ( p_fed_org_filing_status_code = '01'
560 AND p_state_org_filing_status_code = '04'
561 AND p_state_org_wa = p_fed_org_wa
562 AND p_state_empt_cnt = p_fed_exmpt_cnt
563 )
564 OR
565 ( p_fed_org_filing_status_code = '02'
566 AND p_state_org_filing_status_code = '04'
567 AND p_state_org_wa = p_fed_org_wa
568 AND p_state_empt_cnt = p_fed_exmpt_cnt
569 ) ) THEN
570
571
572 IF p_new_filing_status_code = '01' THEN
573 p_new_filing_status_code := '04';
574 RETURN TRUE;
575 ELSIF p_new_filing_status_code = '02' THEN
576 p_new_filing_status_code := '04';
577 RETURN TRUE;
578
579 ELSIF p_new_filing_status_code = '03' THEN
580 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
581
582 IF g_not_matching_state_list IS NOT NULL THEN
583 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
584 ELSE
585 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
586 END IF;
587 g_nonmatch_cntr := g_nonmatch_cntr + 1;
588 END IF;
589 p_new_filing_status_code := p_state_org_filing_status_code ;
590 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
591 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
592 hr_utility.trace('Not Going to Update State Info From Federal.');
593 RETURN FALSE;
594 END IF;
595 ELSE
596 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
597
598 IF g_not_matching_state_list IS NOT NULL THEN
599 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
600 ELSE
601 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
602 END IF;
603 g_nonmatch_cntr := g_nonmatch_cntr + 1;
604 END IF;
605 p_new_filing_status_code := p_state_org_filing_status_code ;
606 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
607 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
608 RETURN FALSE;
609 END IF;
610
611 END IF; --- p_state_code NOT IN ('19', '22)
612
613 ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
614 --RLN 115.39 bug fix 9546309
615 hr_utility.trace('p_state_code := '||p_state_code);
616 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617 hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618 hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619 hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620 hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621 hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622 hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
623
624 IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
625 AND REPLACE(UPPER(lv_state_fs_meaning), ',') = lv_fed_03_meaning
626 AND p_state_org_wa = p_fed_org_wa
627 AND p_state_empt_cnt = p_fed_exmpt_cnt
628 )
629 OR
630 (p_fed_org_filing_status_code = '03'
631 AND REPLACE(UPPER(lv_state_fs_meaning), ',') = lv_fed_03_meaning
632 AND p_state_org_wa = p_fed_org_wa
633 AND p_state_empt_cnt = p_fed_exmpt_cnt
634 )
635 OR
636 ( p_fed_org_filing_status_code = '01'
637 AND p_state_org_filing_status_code = '02'
638 AND p_state_org_wa = p_fed_org_wa
639 AND p_state_empt_cnt = p_fed_exmpt_cnt
640 AND p_state_code = '19'
641 )
642 OR
643 (p_fed_org_filing_status_code = '02' AND p_state_org_filing_status_code = '03'
644 AND p_state_org_wa = p_fed_org_wa
645 AND p_state_empt_cnt = p_fed_exmpt_cnt
646 AND p_state_code = '19'
647 )
648 OR
649 ( p_fed_org_filing_status_code = '01'
650 AND p_state_org_filing_status_code = '04'
651 AND p_state_org_wa = p_fed_org_wa
652 AND p_state_empt_cnt = p_fed_exmpt_cnt
653 AND p_state_code = '22'
654 )
655 OR
656 ( p_fed_org_filing_status_code = '02' AND p_state_org_filing_status_code = '04'
657 AND p_state_org_wa = p_fed_org_wa
658 AND p_state_empt_cnt = p_fed_exmpt_cnt
659 AND p_state_code = '22'
660 )
661 THEN
662
663 p_new_filing_status_code := p_state_org_filing_status_code;
664 hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
665 hr_utility.trace('RETURNING TRUE HERE ' ||p_new_filing_status_code);
666 RETURN TRUE;
667 ELSE
668
669 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
670
671 IF g_not_matching_state_list IS NOT NULL THEN
672 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
673 ELSE
674 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
675 END IF;
676 g_nonmatch_cntr := g_nonmatch_cntr + 1;
677 END IF;
678 p_new_filing_status_code := p_state_org_filing_status_code ;
679 hr_utility.trace('g_nonmatch_cntr 3 := '||g_nonmatch_cntr);
680 hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
681 RETURN FALSE;
682
683 END IF;
684 END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
685 ELSE
686 hr_utility.trace('here 2');
687 hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
688 hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
689 hr_utility.trace('above should match here');
690
691 IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
692
693 hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
694
695 IF g_not_matching_state_list IS NOT NULL THEN
696 g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
697 ELSE
698 g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
699 END IF;
700 g_nonmatch_cntr := g_nonmatch_cntr + 1;
701 END IF;
702 p_new_filing_status_code := p_state_org_filing_status_code ;
703
704 hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
705 RETURN FALSE;
706 END IF; -- State Following Federal Or NOT
707 hr_utility.trace('g_nonmatch_cntr := '||g_nonmatch_cntr);
708 END Fed_State_Filing_Status_Match;
709
710 FUNCTION check_update_status(p_person_id IN per_people_f.person_id%TYPE)
711 RETURN VARCHAR2
712 /******************************************************************
713 **
714 ** Description:
715 ** checks whether person meets update allowed status
716 **
717 ** Access Status:
718 ** Public
719 **
720 ******************************************************************/
721 IS
722
723 l_primary_only VARCHAR2(1);
724
725 CURSOR c_excess_over_fed IS
726 select 'x'
727 from per_assignments_f paf,
728 pay_us_emp_fed_tax_rules_f ftr
729 where paf.person_id = p_person_id
730 and ftr.assignment_id = paf.assignment_id
731 and paf.assignment_type = 'E'
732 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
733 and trunc(sysdate) between ftr.effective_start_date and
734 ftr.effective_end_date
735 and trunc(sysdate) between paf.effective_start_date and
736 paf.effective_end_date
737 and (ftr.excessive_wa_reject_date is not null
738 or nvl(ftr.fit_override_rate,0) <> 0
739 -- bug 4707873 --or nvl(ftr.supp_tax_override_rate,0) <> 0
740 or nvl(ftr.fit_override_amount,0) <> 0);
741
742 CURSOR c_excess_over_state IS
743 select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
744 from per_assignments_f paf,
745 pay_us_emp_state_tax_rules_f str,
746 pay_us_state_tax_info_f stif
747 where paf.person_id = p_person_id
748 and paf.assignment_type = 'E'
749 and str.assignment_id = paf.assignment_id
750 and stif.state_code = str.state_code
751 and stif.sta_information7 like 'Y%'
752 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
753 and trunc(sysdate) between str.effective_start_date and
754 str.effective_end_date
755 and trunc(sysdate) between paf.effective_start_date and
756 paf.effective_end_date
757 and (str.excessive_wa_reject_date is not null
758 or nvl(str.sit_override_amount,0) <> 0
759 or nvl(str.sit_override_rate,0) <> 0
760 --or nvl(str.sui_wage_base_override_amount,0) <> 0
761 -- bug 4707873 --or nvl(str.supp_tax_override_rate,0) <> 0
762 );
763
764 CURSOR c_future_fed_recs IS
765 select 'x'
766 from per_assignments_f paf,
767 pay_us_emp_fed_tax_rules_f ftr
768 where paf.person_id = p_person_id
769 and paf.assignment_type = 'E'
770 and ftr.assignment_id = paf.assignment_id
771 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
772 and ftr.effective_start_date > trunc(sysdate)
773 and trunc(sysdate) between paf.effective_start_date and
774 paf.effective_end_date;
775
776 CURSOR c_future_state_recs IS
777 select /*+ INDEX (stif pay_us_state_tax_info_f_n1) */ 'x'
778 from per_assignments_f paf,
779 pay_us_emp_state_tax_rules_f str,
780 pay_us_state_tax_info_f stif
781 where paf.person_id = p_person_id
782 and str.assignment_id = paf.assignment_id
783 and paf.assignment_type = 'E'
784 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
785 and stif.state_code = str.state_code
786 and stif.sta_information7 like 'Y%'
787 and str.effective_start_date > trunc(sysdate)
788 and trunc(sysdate) between paf.effective_start_date and
789 paf.effective_end_date
790 and trunc(sysdate) between stif.effective_start_date and
791 stif.effective_end_date;
792
793 curs_dummy VARCHAR2(1);
794 lv_update_method VARCHAR2(30);
795
796 BEGIN
797
798 hr_utility.trace('Entering ' || gv_package_name || '.check_update_status');
799
800
801 lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
802 hr_utility.trace('OTF Update Method = ' || lv_update_method);
803
804 -- check for update method set to NONE
805 hr_utility.trace('Testing PROFILE HR_OTF_UPDATE_METHOD');
806
807 if lv_update_method = 'PRIMARY' then
808 l_primary_only := 'Y';
809
810 elsif lv_update_method = 'ALL' then
811 l_primary_only := 'N';
812
813 else -- update_method = NONE or null
814 -- we always default the value to primary
815 l_primary_only := 'Y';
816
817 end if;
818
819 hr_utility.trace('Passed PROFILE HR_OTF_UPDATE_METHOD');
820
821 -- check for excessive wa reject date or override amounts
822 -- Note: we don't actually check the date of the reject, just
823 -- it's existence shuts the employee out
824
825 hr_utility.trace('Testing FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
826 open c_excess_over_fed;
827 fetch c_excess_over_fed into curs_dummy;
828
829 if c_excess_over_fed%FOUND then
830 hr_utility.trace('Failed on FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
831 close c_excess_over_fed;
832 return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
833 end if;
834
835 close c_excess_over_fed;
836 hr_utility.trace('Passed FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
837
838 hr_utility.trace('Testing STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
839 open c_excess_over_state;
840 fetch c_excess_over_state into curs_dummy;
841
842 if c_excess_over_state%FOUND then
843 hr_utility.trace('Failed on STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
844 close c_excess_over_state;
845 return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
846 end if;
847 close c_excess_over_state;
848 hr_utility.trace('Passed STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
849
850
851 -- check for any future dated changes in non-retiree asgs for both state and fed
852
853 hr_utility.trace('Testing FED_FUTURE_DATED_CHANGES');
854 open c_future_fed_recs;
855 fetch c_future_fed_recs into curs_dummy;
856 if c_future_fed_recs%FOUND then
857 hr_utility.trace('Failed on FED_FUTURE_DATED_CHANGES');
858 return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
859 end if;
860
861 close c_future_fed_recs;
862 hr_utility.trace('Passed FED_FUTURE_DATED_CHANGES');
863
864 hr_utility.trace('Testing STATE_FUTURE_DATED_CHANGES');
865 open c_future_state_recs;
866 fetch c_future_state_recs into curs_dummy;
867 if c_future_state_recs%FOUND then
868 hr_utility.trace('Failed on STATE_FUTURE_DATED_CHANGES');
869 return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
870 end if;
871
872 close c_future_state_recs;
873 hr_utility.trace('Passed STATE_FUTURE_DATED_CHANGES');
874
875 -- if we've reached this point, then allow update
876 hr_utility.trace('Leaving ' || gv_package_name || '.check_update_status');
877 return null;
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 return null;
882
883 end check_update_status;
884
885
886 PROCEDURE validate_submission(p_filing_status_code IN VARCHAR2 DEFAULT null,
887 --p_additional_amount IN VARCHAR2 DEFAULT null,
888 p_additional_amount IN NUMBER DEFAULT null,
889 p_allowances IN VARCHAR2 DEFAULT null,
890 p_exempt_status_code IN VARCHAR2 DEFAULT null,
891 p_agreement IN VARCHAR2 DEFAULT 'N',
892 p_person_id IN VARCHAR2,
893 p_error OUT nocopy VARCHAR2,
894 p_errorcnt OUT nocopy INTEGER,
895 p_itemtype IN VARCHAR2,
896 p_itemkey IN VARCHAR2,
897 p_activity_id IN NUMBER,
898 p_state_list OUT nocopy VARCHAR2,
899 p_over_allowance OUT nocopy VARCHAR2,
900 p_exempt_exception OUT nocopy VARCHAR2,
901 p_original_fs IN VARCHAR2,
902 p_original_wa IN VARCHAR2,
903 p_original_exempt IN VARCHAR2,
904 p_exempt_state_list OUT nocopy VARCHAR2,
905 --p_original_aa IN VARCHAR2,
906 p_original_aa IN NUMBER,
907 p_last_name_diff IN VARCHAR2 DEFAULT 'N',
908 p_fit_exempt OUT nocopy VARCHAR2
909 )
910
911 /******************************************************************
912 **
913 ** Description:
914 ** validates the submitted values using the API chk_ procedures
915 ** for fed and state tax rules
916 **
917 ** Access Status:
918 ** Public
919 **
920 ******************************************************************/
921 AS
922 ln_add_tax pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE;
923 l_additional_tax pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE;
924 l_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
925 l_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
926 l_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE;
927 ln_person_id per_people_f.person_id%TYPE;
928 ln_business_group_id per_people_f.business_group_id%TYPE;
929 ln_organization_id hr_organization_information.organization_id%TYPE;
930 lr_org_info_rec hr_organization_information%ROWTYPE;
931 -- lrr_item_rec pay_us_misc_web.item_attr_rec;
932 lv_has_errors VARCHAR2(1) := 'N';
933 lv_update_error_msg VARCHAR2(10000);
934 lv_state_list VARCHAR2(10000);
935 ln_state_count INTEGER;
936 l_primary_only VARCHAR2(1);
937 l_agreement VARCHAR2(1);
938 l_last_name_diff_flag VARCHAR2(1);
939 l_error VARCHAR2(10000) := null;
940 l_num INTEGER := 0;
941 lv_trans_type VARCHAR2(50);
942 lv_source_name VARCHAR2(50);
943 lv_context VARCHAR2(50);
944 lv_level VARCHAR2(50);
945 lv_notify VARCHAR2(100);
946
947 l_review_region VARCHAR2(80);
948 common_exception EXCEPTION;
949
950 lv_update_method VARCHAR2(30) := 'PRIMARY' ;
951 l_transaction_id hr_api_transactions.transaction_id%type;
952 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
953 l_transaction_value_id hr_api_transaction_values.transaction_value_id%type;
954 l_step_obj_version_number hr_api_transaction_steps.object_version_number%type;
955 transaction_value_fs VARCHAR2(80);
956
957 CURSOR c_fed_allowance_limit is
958 select fed_information1
959 from pay_us_federal_tax_info_f
960 where fed_information_category = 'ALLOWANCES LIMIT'
961 and trunc(sysdate) between effective_start_date and effective_end_date;
962
963 l_fed_allowance_limit pay_us_federal_tax_info_f.fed_information1%type;
964
965 cursor get_function_info ( p_item_type HR_API_TRANSACTION_STEPS.item_type%TYPE
966 ,p_item_key HR_API_TRANSACTION_STEPS.item_key%TYPE ) is
967 select fff.function_id, fff.function_name
968 from fnd_form_functions_vl fff
969 where fff.function_name = ( select iav.text_value
970 from wf_item_attribute_values iav
971 where iav.item_type = p_item_type
972 and iav.item_key = p_item_key
973 and iav.name = 'P_CALLED_FROM') ;
974
975 lv_process_name hr_api_transactions.process_name%TYPE;
976 l_function_id hr_api_transactions.function_id%TYPE;
977 l_function_name fnd_form_functions_vl.function_name%TYPE default null;
978 lv_transaction_type varchar2(20);
979
980 --added by vaprakas 11/21/06 Bug 5607135
981 cursor csr_chk_NRA_status
982 is
983 select information_type,pei_information_category,pei_information5,pei_information9
984 from per_people_extra_info where person_id=p_person_id
985 and information_type like 'PER_US_ADDITIONAL_DETAILS'
986 and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
987 and pei_information5 like 'N'
988 and pei_information9 not in ('US');
989
990 cursor csr_chk_student_status
991 is
992 select pei_information1,pei_information2
993 from per_people_extra_info where person_id=p_person_id
994 and information_type like 'PER_US_ADDITIONAL_DETAILS'
995 and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
996 and (pei_information1 = 'Y'
997 or pei_information2 = 'Y');
998
999
1000 l_information_type per_people_extra_info.information_type%TYPE;
1001 l_pei_information_category per_people_extra_info.pei_information_category%TYPE;
1002 l_pei_information5 per_people_extra_info.pei_information5%TYPE;
1003 l_pei_information9 per_people_extra_info.pei_information9%TYPE;
1004 l_student_flag varchar2(3);
1005 l_student per_people_extra_info.pei_information1%TYPE;
1006 l_business_apprentice per_people_extra_info.pei_information2%TYPE;
1007
1008 ln_prev_comma_position NUMBER;
1009 ln_comma_position NUMBER;
1010 lv_notmatch_state pay_us_states.state_name%TYPE;
1011
1012
1013 BEGIN
1014
1015 hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016 hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017 hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018 hr_utility.trace('p_allowances = ' || p_allowances);
1019 hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020 hr_utility.trace('p_itemtype = ' || p_itemtype);
1021 hr_utility.trace('p_itemkey = ' || p_itemkey);
1022 hr_utility.trace('p_activity_id = ' || p_activity_id);
1023 hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024 hr_utility.trace('p_agreement = ' || p_agreement);
1025 hr_utility.trace('p_person_id = ' || p_person_id);
1026 hr_utility.trace('p_original_fs = ' || p_original_fs);
1027 hr_utility.trace('p_original_wa = ' || p_original_wa);
1028 hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029 hr_utility.trace('p_original_aa = ' || p_original_aa);
1030
1031 -- first this is we clear the global value of g_state_list before we continue
1032 -- this will ensure we get the fresh list everytime a user comes back from the
1033 -- review page.
1034
1035 g_state_list := null;
1036 g_state_exempt_list := null;
1037
1038 g_not_matching_state_list := NULL;
1039 g_nonmatch_cntr := 0;
1040
1041
1042 ln_person_id := to_number(p_person_id);
1043 p_fit_exempt:= null;
1044
1045 -- validate filing status
1046
1047 BEGIN
1048 hr_utility.trace('Checking filing status');
1049 -- next call the api chk_procedure
1050 -- we call it with a null id to insure that it is validated
1051 -- withhout having to worry about calling api_updating
1052 -- we also provide the sysdate and end-of-time as the validation
1053 -- dates since we do updates on the sysdate and only if there are
1054 -- no future dated records.
1055 pay_fed_bus.chk_filing_status_code(
1056 p_emp_fed_tax_rule_id => null
1057 ,p_filing_status_code => p_filing_status_code
1058 ,p_effective_date => trunc(sysdate)
1059 ,p_validation_start_date => trunc(sysdate)
1060 ,p_validation_end_date => to_date('31/12/4712','DD/MM/YYYY')
1061 );
1062 EXCEPTION
1063 WHEN OTHERS then
1064 hr_utility.trace('Rejecting Filing Status');
1065
1066 if l_error is null then
1067 l_error := 'PAY-PAY_FILING_STATUS_ERROR-FilingStatusCode';
1068 else
1069 l_error := l_error||';'||'PAY-PAY_FILING_STATUS_ERROR-FilingStatusCode';
1070 end if;
1071
1072 l_num := l_num + 1;
1073 lv_has_errors := 'Y';
1074 END;
1075
1076 -- validate allowances
1077 BEGIN
1078 hr_utility.trace('Checking total allowances');
1079 -- First we convert it into a NUMBER
1080
1081 l_allowances := to_number(nvl(p_allowances,0),'999');
1082
1083 hr_utility.trace('l_allowances = ' || to_char(l_allowances));
1084
1085 if l_allowances < 0 then
1086 raise VALUE_ERROR;
1087 end if;
1088
1089 -- next call the api chk_procedure
1090 pay_fed_bus.chk_withholding_allowances(
1091 p_emp_fed_tax_rule_id => null
1092 ,p_withholding_allowances => l_allowances
1093 );
1094 EXCEPTION
1095 WHEN VALUE_ERROR then
1096 hr_utility.trace('Rejecting Total Allowances - number err');
1097
1098 if l_error is null then
1099 l_error := 'PAY-PAY_US_OTF_FED_WA_NUMBER-WithholdingAllowances';
1100 else
1101 l_error := l_error||';'||'PAY-PAY_US_OTF_FED_WA_NUMBER-WithholdingAllowances';
1102 end if;
1103
1104 l_num := l_num + 1;
1105 lv_has_errors := 'Y';
1106
1107 WHEN OTHERS then
1108 hr_utility.trace('Reject Total Allowances - api err');
1109
1110 if l_error is null then
1111 l_error := 'PAY-PAY_PLSQL_ERROR-WithholdingAllowances';
1112 else
1113 l_error := l_error||';'||'PAY-PAY_PLSQL_ERROR-WithholdingAllowances';
1114 end if;
1115
1116 l_num := l_num + 1;
1117 lv_has_errors := 'Y';
1118 END;
1119
1120 -- Check the Organization Level and/or Business Group Level Context
1121 --is Set for W4 Notifications.
1122 -- Organization Level Context
1123 lv_context := 'US_ORG_REP_PREFERENCES';
1124 lv_level := 'ORG';
1125
1126 lv_notify := get_org_context(ln_person_id, lv_context,lv_level);
1127
1128 hr_utility.trace('ORG get_org_context lv_notify = '||lv_notify);
1129 if lv_notify = 'NOTFOUND' then
1130 -- Business Group Level Context
1131 lv_context := 'US_BG_REP_PREFERENCES';
1132 lv_level := 'BG';
1133 lv_notify := get_org_context(ln_person_id, lv_context,lv_level);
1134 hr_utility.trace('BG get_org_context lv_notify = '||lv_notify);
1135 end if;
1136
1137
1138 -- Also check if the allowance entered by the user is > to the federal allowance limit.
1139
1140 open c_fed_allowance_limit;
1141 fetch c_fed_allowance_limit into l_fed_allowance_limit;
1142 close c_fed_allowance_limit;
1143
1144 hr_utility.trace('b4 checking lv_notify = '||lv_notify);
1145
1146 --------------- added by vaprakas bug 5601735
1147 l_student_flag :='No';
1148
1149 open csr_chk_student_status;
1150 fetch csr_chk_student_status into l_student,l_business_apprentice;
1151 if csr_chk_student_status%FOUND
1152 then l_student_flag :='Yes';
1153 end if;
1154 close csr_chk_student_status;
1155
1156
1157 open csr_chk_NRA_status;
1158 fetch csr_chk_NRA_status into l_information_type,l_pei_information_category,l_pei_information5,l_pei_information9;
1159 if csr_chk_NRA_status%FOUND
1160 then
1161 if to_number(l_allowances)>1 and not
1162 (l_pei_information9 in ('CA','MX','KS') or (l_student_flag ='Yes' and l_pei_information9 = 'IN'))
1163 then
1164 if l_error is null
1165 then
1166 l_error := 'PAY-PAY_US_CHK_NRA_W4_ALLOWANCES-WithholdingAllowances';
1167 else
1168 l_error := l_error||';'||'PAY-PAY_US_CHK_NRA_W4_ALLOWANCES-WithholdingAllowances';
1169 end if;
1170 l_num := l_num + 1;
1171 lv_has_errors := 'Y';
1172 end if;
1173
1174 if p_filing_status_code <> '01'
1175 then
1176 if l_error is null
1177 then
1178 l_error := 'PAY-PAY_US_CHK_NRA_FILING_STATUS-FilingStatusCode';
1179 else
1180 l_error := l_error||';'||'PAY-PAY_US_CHK_NRA_FILING_STATUS-FilingStatusCode';
1181 end if;
1182 l_num := l_num + 1;
1183 lv_has_errors := 'Y';
1184 end if;
1185
1186 if p_exempt_status_code ='on'
1187 then
1188 --modified for bug 7121877
1189 p_fit_exempt := hr_util_misc_web.return_msg_text('PAY_US_CHK_NRA_FIT_EXEMPTIONS','PAY');
1190 --modified for bug 7121877
1191 /* Bug 6346579 : Added the following to track the NRA employee trying to
1192 enable FIT Exempt.*/
1193 g_NRA_flag := 'Y';
1194 end if;
1195 end if;
1196 close csr_chk_NRA_status;
1197 ---------------- Added by vaprakas Bug 5607135
1198
1199 if lv_notify <> 'Y' then
1200 p_over_allowance := null;
1201 else
1202 if (l_fed_allowance_limit is not null) and
1203 (to_number(l_allowances) > to_number(l_fed_allowance_limit) ) then
1204
1205 p_over_allowance := hr_util_misc_web.return_msg_text('PAY_US_OTF_W4_FED_OVERALLOW','PAY');
1206
1207 end if;
1208 end if;
1209
1210 -- validate additional tax
1211 BEGIN
1212 hr_utility.trace('Checking Additional Tax');
1213
1214 ln_add_tax := nvl(p_additional_amount,0);
1215
1216 hr_utility.trace('ln_add_tax value is : '||ln_add_tax);
1217 l_additional_tax := ln_add_tax;
1218 hr_utility.trace('l_additional_tax value is : '|| l_additional_tax);
1219
1220 if l_additional_tax < 0 then
1221 raise value_error;
1222 end if;
1223
1224 -- next call the api chk_procedure
1225 pay_fed_bus.chk_fit_additional_tax(
1226 p_emp_fed_tax_rule_id => null
1227 ,p_fit_additional_tax => l_additional_tax
1228 );
1229 EXCEPTION
1230 WHEN VALUE_ERROR then
1231 hr_utility.trace('Rejecting add. tax - number err');
1232
1233 if l_error is null then
1234 l_error := 'PAY-PAY_US_OTF_FED_ADD_TAX_NUMBER-TaxString';
1235 else
1236 l_error := l_error||';'||'PAY-PAY_US_OTF_FED_ADD_TAX_NUMBER-TaxString';
1237 end if;
1238
1239 l_num := l_num + 1;
1240 lv_has_errors := 'Y';
1241
1242 WHEN OTHERS then
1243 hr_utility.trace('Rejecting add. tax - api err');
1244
1245 if l_error is null then
1246 l_error := 'PAY-PAY_PLSQL_ERROR-TaxString';
1247 else
1248 l_error := l_error||';'||'PAY-PAY_PLSQL_ERROR-TaxString';
1249 end if;
1250
1251 l_num := l_num + 1;
1252 lv_has_errors := 'Y';
1253 END;
1254
1255 -- validate exempt status code
1256
1257 hr_utility.trace('Checking exempt status');
1258 -- the only validation we do is replace a 'N' for null
1259 if p_exempt_status_code = 'on' then
1260 l_exempt_status_code := 'Yes';
1261
1262 if lv_notify <> 'Y' then
1263 p_exempt_exception := null;
1264 else
1265 p_exempt_exception := hr_util_misc_web.return_msg_text('PAYSSW4_EXEMPT_MSG','PAY');
1266 end if;
1267 else
1268 l_exempt_status_code := 'No';
1269 end if;
1270
1271 -- validate the agreement prompt
1272 hr_utility.trace('Checking agreement flag');
1273
1274 if p_agreement = 'on' then
1275 l_agreement := 'Y';
1276 else
1277 hr_utility.trace('Rejecting agreement flag');
1278
1279 if l_error is null then
1280 l_error := 'PAY-PAY_US_OTF_AGREE_ERROR-Agreementflag';
1281 else
1282 l_error := l_error||';'||'PAY-PAY_US_OTF_AGREE_ERROR-Agreementflag';
1283 end if;
1284
1285 l_num := l_num + 1;
1286 lv_has_errors := 'Y';
1287 end if;
1288
1289 -- validate the Last name Different Flag
1290 hr_utility.trace('Checking Last Name Different flag');
1291
1292 if p_last_name_diff = 'on' then
1293 l_last_name_diff_flag := 'Y';
1294 else
1295 l_last_name_diff_flag := 'N';
1296 end if;
1297
1298
1299 -- If we have errors at this point, we go back to the update page
1300 if lv_has_errors = 'Y' then
1301
1302 p_errorcnt := l_num ;
1303 p_error := l_error;
1304
1305 hr_utility.trace('Error string : '|| p_error);
1306 hr_utility.trace('Number of Errors : '|| to_char(p_errorcnt));
1307 hr_utility.trace('Validation Error - returning to Update');
1308
1309 raise common_exception;
1310
1311 end if;
1312
1313 --
1314 lv_trans_type := 'ONLINE_TAX_FORMS';
1315 lv_source_name := 'ONLINE W4 FORM';
1316
1317 -- at this point we try to insert record and if sucessful then rollback the record
1318 -- if not then we will get en error. We pass in the validate_flag as TRUE so we
1319 -- will not commit any data in the table now.
1320
1321 update_tax_records(p_filing_status_code => p_filing_status_code,
1322 p_org_filing_status_code => p_original_fs,
1323 p_allowances => l_allowances,
1324 p_org_allowances => p_original_wa,
1325 p_additional_amount => l_additional_tax,
1326 p_last_name_diff => l_last_name_diff_flag,
1327 p_exempt_status_code => substr(l_exempt_status_code,1,1),
1328 p_org_exempt_status_code => substr(p_original_exempt,1,1),
1329 p_transaction_id => null,
1330 p_person_id => ln_person_id,
1331 p_transaction_type => lv_trans_type,
1332 p_source_name => lv_source_name,
1333 --p_update_method => lv_update_method,
1334 p_validate => TRUE );
1335
1336 hr_utility.trace('The global state list is : '|| g_state_list);
1337 hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1338
1339 -- Forming Proper Message Text
1340
1341 IF g_nonmatch_cntr > 0 then
1342
1343 hr_utility.trace('The global Not Matching list is : '|| g_not_matching_state_list);
1344 hr_utility.trace('Final g_nonmatch_cntr := ' || g_nonmatch_cntr);
1345
1346 ln_prev_comma_position := 0;
1347 FOR i IN 1..g_nonmatch_cntr
1348 LOOP
1349 ln_comma_position := INSTR(g_not_matching_state_list, ',', 1, i);
1350 hr_utility.trace('i := ' || i);
1351 hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1352
1353 IF ln_comma_position = 0 THEN
1354 ln_comma_position := LENGTH(g_not_matching_state_list) + 1;
1355 END IF;
1356 hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1357 hr_utility.trace('ln_prev_comma_position := ' || ln_prev_comma_position);
1358
1359 lv_notmatch_state := LTRIM(RTRIM(SUBSTR(g_not_matching_state_list, (ln_prev_comma_position + 1), (ln_comma_position - ln_prev_comma_position - 1))));
1360 hr_utility.trace('lv_notmatch_state := ' || lv_notmatch_state);
1361
1362 IF INSTR(g_state_list, lv_notmatch_state) <> 0 THEN
1363 IF INSTR(g_state_list, lv_notmatch_state || ',') <> 0 THEN
1364 g_state_list := REPLACE(g_state_list, lv_notmatch_state || ',');
1365 ELSE
1366 g_state_list := REPLACE(g_state_list, lv_notmatch_state );
1367 END IF;
1368 END IF;
1369 IF INSTR(g_state_exempt_list, lv_notmatch_state) <> 0 THEN
1370 IF INSTR(g_state_exempt_list, lv_notmatch_state || ',') <> 0 THEN
1371 g_state_exempt_list := REPLACE(g_state_exempt_list, lv_notmatch_state || ',');
1372 ELSE
1373 g_state_exempt_list := REPLACE(g_state_exempt_list, lv_notmatch_state );
1374 END IF;
1375 END IF;
1376 hr_utility.trace('Success ' || i);
1377 ln_prev_comma_position := ln_comma_position;
1378 END LOOP;
1379 g_not_matching_state_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_NOTMATCHING_STATES','PAY')
1380 || g_not_matching_state_list;
1381
1382 END IF;
1383
1384 IF g_state_list IS NOT NULL THEN
1385 g_state_list := g_state_list || g_not_matching_state_list;
1386 END IF;
1387 IF g_state_exempt_list IS NOT NULL THEN
1388 g_state_exempt_list := g_state_exempt_list || g_not_matching_state_list;
1389 END IF;
1390 IF (g_state_list IS NULL
1391 AND g_state_exempt_list IS NULL
1392 AND g_not_matching_state_list IS NOT NULL) THEN
1393 g_state_list := g_not_matching_state_list;
1394 END IF;
1395
1396 p_state_list := g_state_list;
1397
1398 if substr(l_exempt_status_code,1,1) <> substr(p_original_exempt,1,1) and
1399 p_filing_status_code = p_original_fs and
1400 l_allowances = p_original_wa then
1401
1402 p_exempt_state_list := null;
1403 else
1404 p_exempt_state_list := g_state_exempt_list;
1405 end if;
1406
1407 hr_utility.trace('The global state list is : '|| g_state_list);
1408 hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1409
1410
1411 /* If we have come so far that means we have no errors. So insert data
1412 into hr_api_transactions, hr_api_transaction_steps and hr_api_transaction_values.
1413 Need to insert data into these tables to store OLD and NEW values for W4. These
1414 OLD and NEW values are used in the review page. */
1415
1416 -- First the hr_api_transactions table
1417
1418 l_transaction_id := hr_transaction_ss.get_transaction_id(p_itemtype, p_itemkey);
1419
1420 if l_transaction_id is null then /* transaction does not exists */
1421
1422 hr_utility.trace('l_transaction_id is null INSERTING');
1423
1424 /*
1425 hr_transaction_api.create_transaction( p_creator_person_id => ln_person_id ,
1426 p_transaction_privilege => 'PRIVATE',
1427 p_transaction_id => l_transaction_id);
1428 */
1429 If p_itemtype is not null and p_itemkey is not null then
1430
1431 OPEN get_function_info(p_item_type => p_itemtype,
1432 p_item_key => p_itemkey);
1433
1434 FETCH get_function_info into l_function_id, l_function_name;
1435 IF(get_function_info%notfound) then
1436 CLOSE get_function_info;
1437 END if;
1438 close get_function_info;
1439 end if; /* item_type , Item key */
1440
1441 If p_itemtype is not null and p_itemkey is not null then
1442 lv_transaction_type := 'WF';
1443 else
1444 lv_transaction_type := 'NWF';
1445 end if;
1446
1447 lv_process_name := wf_engine.GetItemAttrText(p_itemtype
1448 ,p_itemkey
1449 ,'PROCESS_NAME');
1450
1451 /* create transaction */
1452
1453 hr_transaction_api.create_transaction(
1454 p_creator_person_id => ln_person_id
1455 ,p_transaction_privilege => 'PRIVATE'
1456 ,p_transaction_id => l_transaction_id
1457 ,p_function_id =>l_function_id
1458 ,p_transaction_ref_table => 'HR_API_TRANSACTIONS'
1459 ,p_transaction_type =>lv_transaction_type
1460 ,p_selected_person_id =>ln_person_id
1461 ,p_item_type =>p_itemtype
1462 ,p_item_key =>p_itemkey
1463 ,p_transaction_effective_date=>sysdate
1464 ,p_process_name =>lv_process_name
1465 );
1466
1467 -- hr_api_transaction_steps
1468
1469 hr_transaction_api.create_transaction_step(
1470 p_creator_person_id => ln_person_id,
1471 p_transaction_id => l_transaction_id,
1472 p_api_name => gv_package_name||'.update_w4_info',
1473 p_item_type => p_itemtype,
1474 p_item_key => p_itemkey,
1475 p_activity_id => p_activity_id,
1476 p_transaction_step_id => l_transaction_step_id,
1477 p_object_version_number => l_step_obj_version_number);
1478
1479 -- set the transaction_id attribute value in the workflow.
1480 wf_engine.setitemattrtext ( itemtype => p_itemtype,
1481 itemkey => p_itemkey,
1482 aname => 'TRANSACTION_ID',
1483 avalue => l_transaction_id);
1484
1485 else /* transaction exists */
1486
1487 hr_utility.trace('l_transaction_id : '|| l_transaction_id);
1488 select transaction_step_id into l_transaction_step_id
1489 from HR_API_TRANSACTION_STEPS
1490 where transaction_id = l_transaction_id;
1491
1492 end if;
1493
1494 -- hr_api_transaction_values
1495
1496 -- Filing Status Meaning
1497 -- get the filing status meaning to store in the transaction_value table
1498
1499 hr_utility.trace('After END IF');
1500
1501 select fcl.meaning Meaning into transaction_value_fs
1502 from fnd_common_lookups fcl
1503 where fcl.lookup_type = 'US_FIT_FILING_STATUS'
1504 and fcl.lookup_code = p_filing_status_code ;
1505
1506 hr_transaction_api.set_varchar2_value (
1507 p_transaction_step_id => l_transaction_step_id
1508 ,p_person_id => ln_person_id
1509 ,p_name => 'P_FILING_STATUS'
1510 ,p_value => transaction_value_fs ) ;
1511
1512 -- Filing Status Code
1513 hr_transaction_api.set_varchar2_value (
1514 p_transaction_step_id => l_transaction_step_id
1515 ,p_person_id => ln_person_id
1516 ,p_name => 'P_FS_CODE'
1517 ,p_value => p_filing_status_code ) ;
1518
1519 -- Original Filing Status Code
1520 hr_transaction_api.set_varchar2_value (
1521 p_transaction_step_id => l_transaction_step_id
1522 ,p_person_id => ln_person_id
1523 ,p_name => 'P_ORG_FS_CODE'
1524 ,p_value => p_original_fs ) ;
1525
1526 select fcl.meaning Meaning into transaction_value_fs
1527 from fnd_common_lookups fcl
1528 where fcl.lookup_type = 'US_FIT_FILING_STATUS'
1529 and fcl.lookup_code = p_original_fs ;
1530 hr_utility.trace('transaction_step_id = ' || l_transaction_step_id);
1531 hr_transaction_api.set_varchar2_value (
1532 p_transaction_step_id => l_transaction_step_id
1533 ,p_person_id => ln_person_id
1534 ,p_name => 'P_ORG_FILING_STATUS'
1535 ,p_value => transaction_value_fs ) ;
1536
1537 -- Allowances
1538 hr_transaction_api.set_number_value (
1539 p_transaction_step_id => l_transaction_step_id
1540 ,p_person_id => ln_person_id
1541 ,p_name => 'P_ALLOWANCES'
1542 ,p_value => l_allowances ) ;
1543
1544 -- Original Allowances
1545 hr_transaction_api.set_number_value (
1546 p_transaction_step_id => l_transaction_step_id
1547 ,p_person_id => ln_person_id
1548 ,p_name => 'P_ORG_ALLOWANCES'
1549 ,p_value => p_original_wa ) ;
1550
1551 -- Additional Tax
1552 hr_transaction_api.set_number_value (
1553 p_transaction_step_id => l_transaction_step_id
1554 ,p_person_id => ln_person_id
1555 ,p_name => 'P_ADDITIONAL_TAX'
1556 ,p_value => ln_add_tax);
1557
1558 -- Original Additional Tax
1559 hr_transaction_api.set_number_value (
1560 p_transaction_step_id => l_transaction_step_id
1561 ,p_person_id => ln_person_id
1562 ,p_name => 'P_ORG_ADDITIONAL_TAX'
1563 ,p_value => p_original_aa);
1564
1565
1566 -- Exempt
1567 hr_transaction_api.set_varchar2_value (
1568 p_transaction_step_id => l_transaction_step_id
1569 ,p_person_id => ln_person_id
1570 ,p_name => 'P_EXEMPT'
1571 ,p_value => l_exempt_status_code ) ;
1572
1573 -- Original Exempt
1574 hr_transaction_api.set_varchar2_value (
1575 p_transaction_step_id => l_transaction_step_id
1576 ,p_person_id => ln_person_id
1577 ,p_name => 'P_ORG_EXEMPT'
1578 ,p_value => p_original_exempt ) ;
1579
1580 -- P_REVIEW_PROC_CALL
1581 l_review_region := WF_ENGINE.GetActivityAttrText( itemtype => p_itemtype,
1582 itemkey => p_itemkey,
1583 actid => p_activity_id,
1584 aname => 'HR_REVIEW_REGION_ITEM');
1585
1586
1587 hr_transaction_api.set_varchar2_value (
1588 p_transaction_step_id => l_transaction_step_id
1589 ,p_person_id => ln_person_id
1590 ,p_name => 'P_REVIEW_PROC_CALL'
1591 ,p_value => l_review_region ) ;
1592
1593 -- P_REVIEW_ACTID
1594 hr_transaction_api.set_varchar2_value (
1595 p_transaction_step_id => l_transaction_step_id
1596 ,p_person_id => ln_person_id
1597 ,p_name => 'P_REVIEW_ACTID'
1598 ,p_value => p_activity_id ) ;
1599
1600 -- P_LAST_NAME_DIFF
1601 hr_transaction_api.set_varchar2_value (
1602 p_transaction_step_id => l_transaction_step_id
1603 ,p_person_id => ln_person_id
1604 ,p_name => 'P_LAST_NAME_DIFF'
1605 ,p_value => l_last_name_diff_flag ) ;
1606
1607 hr_utility.trace('B4 Commit');
1608 commit;
1609
1610 hr_utility.trace('Leaving ' || gv_package_name || '.validate_submission');
1611
1612 EXCEPTION
1613
1614 When common_exception then
1615 hr_utility.trace('In exception common_exception');
1616 return;
1617
1618 When no_data_found then
1619 hr_utility.trace('In exception no_data_found');
1620 return;
1621
1622
1623 WHEN OTHERS THEN
1624 hr_utility.trace(gv_package_name || '.validate_submission FATAL ERROR');
1625 hr_utility.trace(SQLERRM || ' ' || SQLCODE);
1626
1627 l_error := l_error ||';'||'PAY-'||gv_package_name || '.validate_submission'||
1628 SQLERRM || ' ' ||SQLCODE||'-Dummy';
1629
1630 return;
1631
1632
1633 END validate_submission;
1634
1635 /*************************************************************************************
1636
1637 ** get_transaction_values procedure gets the transaction_values from
1638 ** hr_transaction_values.
1639 ** This procedure accepts transaction_id, transaction_step_id as IN variables.
1640 ** First check is made to see if Transaction Id is not null, if not null then get
1641 ** step id and fetch values else check for step id and fetch values on step id.
1642 **
1643 ** this we use it in the review page to show the old and the new value. We get the
1644 ** values by passing the transaction_step_id. The output is concatenated and passed
1645 ** out as a string.
1646 ************************************************************************************/
1647
1648 PROCEDURE get_transaction_values(
1649 p_trans_id IN VARCHAR2 Default null,
1650 p_step_id IN VARCHAR2 Default null,
1651 p_out_values OUT nocopy VARCHAR2 ) IS
1652
1653 l_step_id hr_api_transaction_steps.transaction_step_id%type;
1654
1655 CURSOR c_trans_values IS
1656
1657 select v.datatype,v.name,v.varchar2_value,number_value
1658 from hr_api_transaction_values v
1659 where v.transaction_step_id = l_step_id
1660 order by transaction_value_id;
1661
1662 l_datatype hr_api_transaction_values.datatype%type;
1663 l_name hr_api_transaction_values.name%type;
1664 l_varchar2_value hr_api_transaction_values.varchar2_value%type;
1665 l_number_value hr_api_transaction_values.number_value%type;
1666
1667 common_exception EXCEPTION;
1668 BEGIN
1669
1670 hr_utility.trace('Transaction Id is : '|| p_trans_id);
1671 hr_utility.trace('Transaction Step Id is : '|| p_step_id);
1672
1673 if (p_trans_id is null and p_step_id is null) then
1674 p_out_values := 'Error: Please enter Transaction Id or Transaction Step Id';
1675 raise common_exception;
1676 end if;
1677
1678 if (p_trans_id is not null and p_step_id is null) then
1679
1680 select transaction_step_id into l_step_id
1681 from hr_api_transaction_steps
1682 where transaction_id = to_number(p_trans_id);
1683
1684 else
1685 l_step_id := to_number(p_step_id);
1686
1687 end if;
1688
1689 open c_trans_values;
1690 loop
1691 exit when c_trans_values%NOTFOUND;
1692 fetch c_trans_values into l_datatype,l_name,l_varchar2_value,l_number_value;
1693
1694 if l_datatype = 'VARCHAR2' and l_name = 'P_FILING_STATUS' then
1695 p_out_values := l_varchar2_value;
1696 elsif l_datatype = 'VARCHAR2' and l_name = 'P_FS_CODE' then
1697 p_out_values := p_out_values||';'||l_varchar2_value;
1698 elsif l_datatype = 'NUMBER' and l_name = 'P_ALLOWANCES' then
1699 p_out_values := p_out_values||';'||l_number_value;
1700 elsif l_datatype = 'NUMBER' and l_name = 'P_ADDITIONAL_TAX' then
1701 p_out_values := p_out_values||';'||l_number_value;
1702 elsif l_datatype = 'VARCHAR2' and l_name = 'P_EXEMPT' then
1703 p_out_values := p_out_values||';'||l_varchar2_value;
1704 end if;
1705
1706 end loop;
1707 close c_trans_values;
1708
1709 hr_utility.trace('The out value is : '|| p_out_values);
1710
1711 exception when common_exception then
1712
1713 hr_utility.trace('Error: Please enter Transaction Id or Transaction Step Id');
1714 return;
1715
1716
1717 END;
1718
1719
1720 PROCEDURE update_alien_tax_records(
1721 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
1722 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
1723 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
1724 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
1725 ,p_process VARCHAR2
1726 ,p_itemtype VARCHAR2
1727 ,p_person_id per_people_f.person_id%TYPE default null
1728 ,p_effective_date date default null
1729 ,p_source_name VARCHAR2 default null
1730 )
1731 /******************************************************************
1732 **
1733 ** Description: OTF Fed W4 update procedure
1734 ** 1. locks all applicable rows
1735 ** 2. update each fed row using fed api
1736 ** 3. update each state row using state api
1737 ** 4. archive the submission
1738 **
1739 ** Access Status:
1740 ** Public
1741 **
1742 ******************************************************************/
1743 IS
1744 ln_person_id per_people_f.person_id%TYPE;
1745 ln_business_group_id per_people_f.business_group_id%TYPE;
1746 ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1747 ln_assignment_id per_assignments_f.assignment_id%TYPE;
1748 ln_gre_id hr_organization_units.organization_id%TYPE;
1749 ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1750 ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1751 ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1752 ld_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1753 ld_end_date pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
1754 lv_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
1755 lv_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
1756 lv_additional_tax pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE;
1757 lv_org_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
1758 lv_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
1759 lv_org_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE;
1760 lv_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE;
1761 lv_last_name_diff_flag VARCHAR2(1);
1762 lv_state_name pay_us_states.state_name%TYPE;
1763 lv_state_code pay_us_states.state_code%TYPE;
1764 lv_state_default_code VARCHAR2(30);
1765 lv_context pay_stat_trans_audit.audit_information_category%TYPE;
1766 ln_dummy NUMBER(15);
1767 lb_comma_flag boolean := false;
1768 lv_datetrack_mode VARCHAR2(30);
1769 lv_update_method VARCHAR2(30);
1770 l_primary_only VARCHAR2(1);
1771 lv_state_list VARCHAR2(1000);
1772 lv_update_error_msg VARCHAR2(10000);
1773 e_no_records EXCEPTION;
1774 e_no_update_allowed EXCEPTION;
1775 e_date_error EXCEPTION;
1776 --
1777 ld_effective_date date;
1778 lv_trans_type VARCHAR2(50);
1779 lv_source_name VARCHAR2(50);
1780 --
1781 BEGIN
1782
1783 lv_filing_status_code := p_filing_status_code;
1784 lv_org_filing_status_code := NULL;
1785 ln_person_id := p_person_id;
1786 lv_allowances := p_allowances;
1787 lv_org_allowances := NULL;
1788 lv_additional_tax := p_additional_amount;
1789 lv_last_name_diff_flag := 'Y';
1790 lv_exempt_status_code := p_exempt_status_code;
1791 lv_org_exempt_status_code := NULL;
1792
1793 -- set a savepoint before we do anything
1794 SAVEPOINT update_alien_tax_records;
1795
1796 -- jatin
1797 if p_source_name = 'PQP_US_ALIEN_WINDSTAR' then
1798
1799 ln_person_id := p_person_id;
1800 ln_business_group_id := hr_util_misc_web.get_business_group_id(ln_person_id);
1801 lv_update_method := 'PRIMARY';
1802 ld_effective_date := trunc(p_effective_date);
1803 lv_trans_type := 'US_TAX_FORMS';
1804 lv_source_name := p_source_name;
1805
1806 end if;
1807
1808
1809 update_tax_records(p_filing_status_code => p_filing_status_code,
1810 p_org_filing_status_code => lv_org_filing_status_code,
1811 p_allowances => lv_allowances,
1812 p_org_allowances => lv_org_allowances,
1813 p_additional_amount => lv_additional_tax,
1814 p_last_name_diff => lv_last_name_diff_flag,
1815 p_exempt_status_code => lv_exempt_status_code,
1816 p_org_exempt_status_code => lv_org_exempt_status_code,
1817 p_transaction_id => null,
1818 p_person_id => ln_person_id,
1819 p_transaction_type => lv_trans_type,
1820 p_source_name => lv_source_name,
1821 --p_update_method => lv_update_method,
1822 p_validate => FALSE );
1823
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 rollback to update_alien_tax_records;
1827
1828 raise_application_error(-20001,'Fatal Error while commit');
1829
1830 END update_alien_tax_records;
1831
1832
1833 PROCEDURE update_tax_records(
1834 p_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
1835 ,p_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE
1836 ,p_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
1837 ,p_org_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE
1838 ,p_additional_amount pay_us_emp_fed_tax_rules_f.fit_additional_tax%TYPE
1839 ,p_last_name_diff VARCHAR2 DEFAULT 'N'
1840 ,p_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
1841 ,p_org_exempt_status_code pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE
1842 ,p_transaction_id hr_api_transactions.transaction_id%type
1843 ,p_person_id VARCHAR2
1844 ,p_transaction_type VARCHAR2
1845 ,p_source_name VARCHAR2
1846 --,p_update_method VARCHAR2
1847 ,p_validate boolean default false
1848 )
1849 /******************************************************************
1850 **
1851 ** Description: OTF Fed W4 update procedure
1852 ** 1. locks all applicable rows
1853 ** 2. update each fed row using fed api
1854 ** 3. update each state row using state api
1855 ** 4. archive the submission
1856 **
1857 ** Access Status:
1858 ** Public
1859 **
1860 ******************************************************************/
1861 IS
1862 ln_person_id per_people_f.person_id%TYPE;
1863 ln_business_group_id per_people_f.business_group_id%TYPE;
1864 ln_parent_audit_id pay_stat_trans_audit.stat_trans_audit_id%TYPE;
1865 ln_assignment_id per_assignments_f.assignment_id%TYPE;
1866 ln_gre_id hr_organization_units.organization_id%TYPE;
1867 ln_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%TYPE;
1868 ln_state_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%TYPE;
1869 ld_old_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1870 ld_start_date pay_us_emp_fed_tax_rules_f.effective_start_date%TYPE;
1871 ld_end_date pay_us_emp_fed_tax_rules_f.effective_end_date%TYPE;
1872 lv_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
1873 lv_state_filing_status_code pay_us_emp_state_tax_rules_f.filing_status_code%TYPE;
1874 cu_state_filing_status_code pay_us_emp_state_tax_rules_f.filing_status_code%TYPE;
1875 cu_state_wa pay_us_emp_state_tax_rules_f.withholding_allowances%TYPE;
1876 cu_sit_exempt pay_us_emp_state_tax_rules_f.sit_exempt%TYPE;
1877 ln_state_addtional_tax pay_us_emp_state_tax_rules_f.sit_additional_tax%TYPE;
1878
1879 cu_sui_wage_base_override_amt pay_us_emp_state_tax_rules_f.sui_wage_base_override_amount%TYPE;
1880 lv_state_name pay_us_states.state_name%TYPE;
1881 lv_state_code pay_us_states.state_code%TYPE;
1882 lv_state_default_code VARCHAR2(30);
1883 lv_state_exempt_code pay_us_state_tax_info_f.sta_information9%TYPE;
1884 lv_context pay_stat_trans_audit.audit_information_category%TYPE;
1885 ln_dummy NUMBER(15);
1886 lb_comma_flag boolean := false;
1887 lv_datetrack_mode VARCHAR2(30);
1888 lv_update_method VARCHAR2(30) := 'PRIMARY';
1889 l_exempt_status_code VARCHAR2(2);
1890 l_primary_only VARCHAR2(1);
1891 lv_state_list VARCHAR2(1000);
1892 lv_update_error_msg VARCHAR2(10000);
1893 e_no_records EXCEPTION;
1894 e_no_update_allowed EXCEPTION;
1895 e_date_error EXCEPTION;
1896 l_primary_flag per_assignments_f.primary_flag%TYPE;
1897
1898 l_state_exempt varchar2(15);
1899
1900 lv_filing_status_changed boolean := false;
1901 lv_state_fs_changed boolean := false;
1902 lv_allowance_changed boolean := false;
1903 lv_exempt_changed boolean := false;
1904 lv_insert_flag boolean := false;
1905
1906 lv_fit_exempt NUMBER(15);
1907 lv_futa_tax_exempt NUMBER(15);
1908 lv_medicare_tax_exempt NUMBER(15);
1909 lv_ss_tax_exempt NUMBER(15);
1910
1911 lv_sit_exempt NUMBER(15);
1912 lv_sui_exempt NUMBER(15);
1913 lv_sdi_exempt NUMBER(15);
1914 lv_wc_exempt NUMBER(15);
1915
1916 lv_fed_exemptions_count NUMBER(15);
1917 lv_state_exemptions_count NUMBER(15);
1918 lv_trans_type VARCHAR2(50);
1919 lv_source_name VARCHAR2(50);
1920 lv_filing_status_code_o pay_us_emp_state_tax_rules_f.filing_status_code%TYPE;
1921
1922 /*
1923 CURSOR c_fed_tax_rows IS
1924 select ftr.emp_fed_tax_rule_id,
1925 ftr.filing_status_code,
1926 ftr.withholding_allowances,
1927 ftr.object_version_number,
1928 ftr.effective_start_date,
1929 paf.assignment_id,
1930 hsck.segment1,
1931 paf.primary_flag,
1932 decode(ftr.fit_exempt,'Y',1,0),
1933 decode(ftr.futa_tax_exempt,'Y',1,0),
1934 decode(ftr.medicare_tax_exempt,'Y',1,0),
1935 decode(ftr.ss_tax_exempt,'Y',1,0)
1936 from pay_us_emp_fed_tax_rules_f ftr, per_assignments_f paf,
1937 hr_soft_coding_keyflex hsck
1938 where paf.person_id = ln_person_id
1939 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1940 and paf.assignment_id = ftr.assignment_id
1941 and paf.assignment_type = 'E'
1942 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
1943 and trunc(sysdate) between paf.effective_start_date and
1944 paf.effective_end_date
1945 and trunc(sysdate) between ftr.effective_start_date and
1946 ftr.effective_end_date
1947 order by paf.assignment_id
1948 for update nowait;
1949 */
1950
1951 CURSOR c_fed_tax_rows IS
1952 select ftr.*,
1953 hsck.segment1 gre_id,
1954 paf.primary_flag primary_flag,
1955 trunc(sysdate) cur_sysdate,
1956 decode(ftr.fit_exempt,'Y',1,0) fit_exempt_count,
1957 decode(ftr.futa_tax_exempt,'Y',1,0) futa_tax_exempt_count,
1958 decode(ftr.medicare_tax_exempt,'Y',1,0) medicare_tax_exempt_count,
1959 decode(ftr.ss_tax_exempt,'Y',1,0) ss_tax_exempt_count
1960 from pay_us_emp_fed_tax_rules_f ftr
1961 ,per_assignments_f paf
1962 ,hr_soft_coding_keyflex hsck
1963 where paf.person_id = ln_person_id
1964 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1965 and paf.assignment_id = ftr.assignment_id
1966 and paf.assignment_type = 'E'
1967 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
1968 and trunc(sysdate) between paf.effective_start_date and
1969 paf.effective_end_date
1970 and trunc(sysdate) between ftr.effective_start_date and
1971 ftr.effective_end_date
1972 order by paf.assignment_id
1973 for update nowait;
1974
1975 l_fed_tax_rec c_fed_tax_rows%rowtype;
1976
1977 /*
1978 CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
1979 select str.emp_state_tax_rule_id,
1980 str.object_version_number,
1981 str.effective_start_date,
1982 str.filing_status_code,
1983 str.withholding_allowances,
1984 str.sit_additional_tax,
1985 pus.state_name,
1986 pus.state_code,
1987 paf.assignment_id,
1988 stif.sta_information7,
1989 hsck.segment1
1990 ,str.sit_exempt
1991 ,nvl(stif.sta_information9,'N') -- does the exempt status default from federal
1992 ,str.sui_wage_base_override_amount
1993 ,decode(str.sit_exempt,'Y',1,0)
1994 ,decode(str.sui_exempt,'Y',1,0)
1995 ,decode(str.sdi_exempt,'Y',1,0)
1996 ,decode(str.wc_exempt,'Y',1,0)
1997 from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
1998 pay_us_state_tax_info_f stif, pay_us_states pus,
1999 hr_soft_coding_keyflex hsck
2000 where paf.person_id = ln_person_id
2001 and paf.assignment_id = curvar_assignment_id
2002 and paf.assignment_id = str.assignment_id
2003 and paf.assignment_type = 'E'
2004 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2005 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
2006 and str.state_code = stif.state_code
2007 and str.state_code = pus.state_code
2008 and stif.sta_information7 like 'Y%'
2009 and trunc(sysdate) between stif.effective_start_date and
2010 stif.effective_end_date
2011 and trunc(sysdate) between paf.effective_start_date and
2012 paf.effective_end_date
2013 and trunc(sysdate) between str.effective_start_date and
2014 str.effective_end_date
2015 for update nowait;
2016 */
2017
2018 CURSOR c_state_tax_rows(curvar_assignment_id per_assignments_f.assignment_id%TYPE) IS
2019 select str.*
2020 ,pus.state_name
2021 ,stif.sta_information7 state_as_fed
2022 ,hsck.segment1 gre_id
2023 /* Bug# 6346579 : Changing the default value to 'Y' */
2024 --,nvl(stif.sta_information9,'N') exmpt_status_state_as_fed -- does the exempt status default from federal
2025 ,nvl(stif.sta_information9,'Y') exmpt_status_state_as_fed -- does the exempt status default from federal
2026 ,trunc(sysdate) cur_sysdate
2027 ,decode(str.sit_exempt,'Y',1,0) sit_exempt_count
2028 ,decode(str.sui_exempt,'Y',1,0) sui_exempt_count
2029 ,decode(str.sdi_exempt,'Y',1,0) sdi_exempt_count
2030 ,decode(str.wc_exempt,'Y',1,0) wc_exempt_count
2031 from pay_us_emp_state_tax_rules_f str, per_assignments_f paf,
2032 pay_us_state_tax_info_f stif, pay_us_states pus,
2033 hr_soft_coding_keyflex hsck
2034 where paf.person_id = ln_person_id
2035 and paf.assignment_id = curvar_assignment_id
2036 and paf.assignment_id = str.assignment_id
2037 and paf.assignment_type = 'E'
2038 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2039 and decode(l_primary_only,'Y',paf.primary_flag,'Y') = 'Y'
2040 and str.state_code = stif.state_code
2041 and str.state_code = pus.state_code
2042 and stif.sta_information7 like 'Y%'
2043 and trunc(sysdate) between stif.effective_start_date and
2044 stif.effective_end_date
2045 and trunc(sysdate) between paf.effective_start_date and
2046 paf.effective_end_date
2047 and trunc(sysdate) between str.effective_start_date and
2048 str.effective_end_date
2049 for update nowait;
2050
2051 l_state_tax_rec c_state_tax_rows%rowtype;
2052
2053 BEGIN
2054
2055 hr_utility.trace('Entering ' || gv_package_name || '.update_tax_records');
2056
2057 -- set a savepoint before we do anything
2058 SAVEPOINT update_tax_records;
2059
2060 gv_exempt_change := 'N';
2061
2062 -- get transaction type and source name
2063 lv_trans_type := p_transaction_type;
2064 lv_source_name := p_source_name;
2065
2066 -- validate session and get person id
2067
2068 ln_person_id := p_person_id;
2069 ln_business_group_id := hr_util_misc_web.get_business_group_id(ln_person_id);
2070
2071 -- get the update method
2072 if lv_source_name = 'PQP_US_ALIEN_WINDSTAR' then
2073 lv_update_method := 'PRIMARY';
2074 else
2075 lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
2076 end if;
2077
2078 if lv_update_method = 'PRIMARY' then
2079 l_primary_only := 'Y';
2080
2081 elsif lv_update_method = 'ALL' then
2082 l_primary_only := 'N';
2083
2084 else -- update_method = NONE or null
2085 -- we always default the value to primary
2086 l_primary_only := 'Y';
2087
2088 end if;
2089
2090 lv_fed_exemptions_count := 0;
2091 lv_state_exemptions_count := 0;
2092
2093 hr_utility.trace('Update Method = ' || lv_update_method);
2094
2095 lv_filing_status_code := p_filing_status_code;
2096
2097 -- lock records
2098 hr_utility.trace(gv_package_name||'.update_tax_records - Locking Employee Tax Records');
2099 open c_fed_tax_rows;
2100
2101 loop /* Federal cursor loop */
2102
2103 -- start by updating the fed tax records
2104
2105 /*
2106 FETCH c_fed_tax_rows INTO ln_fed_tax_rule_id,cu_fed_filing_status_code,
2107 cu_fed_withholding_allowances,
2108 ln_ovn,ld_old_start_date, ln_assignment_id,
2109 ln_gre_id,l_primary_flag,
2110 lv_fit_exempt, lv_futa_tax_exempt,
2111 lv_medicare_tax_exempt, lv_ss_tax_exempt;
2112 */
2113 l_fed_tax_rec := null;
2114
2115 FETCH c_fed_tax_rows INTO l_fed_tax_rec;
2116
2117 exit when c_fed_tax_rows%NOTFOUND;
2118
2119 -- count the no. of Federal Tax Exemptions
2120 lv_fed_exemptions_count := l_fed_tax_rec.fit_exempt_count
2121 + l_fed_tax_rec.futa_tax_exempt_count
2122 + l_fed_tax_rec.medicare_tax_exempt_count
2123 + l_fed_tax_rec.ss_tax_exempt_count ;
2124
2125 hr_utility.trace(gv_package_name ||'.update_tax_records - BEFORE FED UPDATE');
2126
2127 -- We insert using datetrack mode of UPDATE
2128 -- future dated records will cause an error
2129 -- if the old start date = sysdate, we perform a correction instead
2130
2131 --if ld_old_start_date = trunc(sysdate) then
2132 if l_fed_tax_rec.effective_start_date = trunc(sysdate) then
2133 lv_datetrack_mode := 'CORRECTION';
2134 else
2135 lv_datetrack_mode := 'UPDATE';
2136 end if;
2137
2138 hr_utility.trace('Updating Fed Record ID = ' || to_char(ln_fed_tax_rule_id));
2139
2140 /*
2141 pay_federal_tax_rule_api.update_fed_tax_rule
2142 (p_emp_fed_tax_rule_id => ln_fed_tax_rule_id
2143 ,p_withholding_allowances => p_allowances
2144 ,p_fit_additional_tax => p_additional_amount
2145 ,p_filing_status_code => lv_filing_status_code
2146 ,p_fit_exempt => p_exempt_status_code
2147 ,p_object_version_number => ln_ovn
2148 ,p_effective_start_date => ld_start_date
2149 ,p_effective_end_date => ld_end_date
2150 ,p_effective_date => trunc(sysdate)
2151 ,p_datetrack_update_mode => lv_datetrack_mode
2152 ,p_validate => p_validate
2153 );
2154
2155 */
2156 -- Calling api for all parameters.
2157
2158 pay_federal_tax_rule_api.update_fed_tax_rule(
2159 p_validate =>p_validate
2160 ,p_effective_date =>l_fed_tax_rec.cur_sysdate
2161 ,p_datetrack_update_mode =>lv_datetrack_mode
2162 ,p_emp_fed_tax_rule_id =>l_fed_tax_rec.emp_fed_tax_rule_id
2163 ,p_object_version_number =>l_fed_tax_rec.object_version_number
2164 ,p_sui_state_code =>l_fed_tax_rec.sui_state_code
2165 ,p_additional_wa_amount =>l_fed_tax_rec.additional_wa_amount
2166 ,p_filing_status_code =>lv_filing_status_code
2167 ,p_fit_override_amount =>l_fed_tax_rec.fit_override_amount
2168 ,p_fit_override_rate =>l_fed_tax_rec.fit_override_rate
2169 ,p_withholding_allowances =>p_allowances
2170 ,p_cumulative_taxation =>l_fed_tax_rec.cumulative_taxation
2171 ,p_eic_filing_status_code =>l_fed_tax_rec.eic_filing_status_code
2172 ,p_fit_additional_tax =>p_additional_amount
2173 ,p_fit_exempt =>p_exempt_status_code
2174 ,p_futa_tax_exempt =>l_fed_tax_rec.futa_tax_exempt
2175 ,p_medicare_tax_exempt =>l_fed_tax_rec.medicare_tax_exempt
2176 ,p_ss_tax_exempt =>l_fed_tax_rec.ss_tax_exempt
2177 ,p_statutory_employee =>l_fed_tax_rec.statutory_employee
2178 ,p_w2_filed_year =>l_fed_tax_rec.w2_filed_year
2179 ,p_supp_tax_override_rate =>l_fed_tax_rec.supp_tax_override_rate
2180 ,p_excessive_wa_reject_date =>l_fed_tax_rec.excessive_wa_reject_date
2181 ,p_attribute_category =>l_fed_tax_rec.attribute_category
2182 ,p_attribute1 =>l_fed_tax_rec.attribute1
2183 ,p_attribute2 =>l_fed_tax_rec.attribute2
2184 ,p_attribute3 =>l_fed_tax_rec.attribute3
2185 ,p_attribute4 =>l_fed_tax_rec.attribute4
2186 ,p_attribute5 =>l_fed_tax_rec.attribute5
2187 ,p_attribute6 =>l_fed_tax_rec.attribute6
2188 ,p_attribute7 =>l_fed_tax_rec.attribute7
2189 ,p_attribute8 =>l_fed_tax_rec.attribute8
2190 ,p_attribute9 =>l_fed_tax_rec.attribute9
2191 ,p_attribute10 =>l_fed_tax_rec.attribute10
2192 ,p_attribute11 =>l_fed_tax_rec.attribute11
2193 ,p_attribute12 =>l_fed_tax_rec.attribute12
2194 ,p_attribute13 =>l_fed_tax_rec.attribute13
2195 ,p_attribute14 =>l_fed_tax_rec.attribute14
2196 ,p_attribute15 =>l_fed_tax_rec.attribute15
2197 ,p_attribute16 =>l_fed_tax_rec.attribute16
2198 ,p_attribute17 =>l_fed_tax_rec.attribute17
2199 ,p_attribute18 =>l_fed_tax_rec.attribute18
2200 ,p_attribute19 =>l_fed_tax_rec.attribute19
2201 ,p_attribute20 =>l_fed_tax_rec.attribute20
2202 ,p_attribute21 =>l_fed_tax_rec.attribute21
2203 ,p_attribute22 =>l_fed_tax_rec.attribute22
2204 ,p_attribute23 =>l_fed_tax_rec.attribute23
2205 ,p_attribute24 =>l_fed_tax_rec.attribute24
2206 ,p_attribute25 =>l_fed_tax_rec.attribute25
2207 ,p_attribute26 =>l_fed_tax_rec.attribute26
2208 ,p_attribute27 =>l_fed_tax_rec.attribute27
2209 ,p_attribute28 =>l_fed_tax_rec.attribute28
2210 ,p_attribute29 =>l_fed_tax_rec.attribute29
2211 ,p_attribute30 =>l_fed_tax_rec.attribute30
2212 ,p_fed_information_category =>l_fed_tax_rec.fed_information_category
2213 ,p_fed_information1 =>l_fed_tax_rec.fed_information1
2214 ,p_fed_information2 =>l_fed_tax_rec.fed_information2
2215 ,p_fed_information3 =>l_fed_tax_rec.fed_information3
2216 ,p_fed_information4 =>l_fed_tax_rec.fed_information4
2217 ,p_fed_information5 =>l_fed_tax_rec.fed_information5
2218 ,p_fed_information6 =>l_fed_tax_rec.fed_information6
2219 ,p_fed_information7 =>l_fed_tax_rec.fed_information7
2220 ,p_fed_information8 =>l_fed_tax_rec.fed_information8
2221 ,p_fed_information9 =>l_fed_tax_rec.fed_information9
2222 ,p_fed_information10 =>l_fed_tax_rec.fed_information10
2223 ,p_fed_information11 =>l_fed_tax_rec.fed_information11
2224 ,p_fed_information12 =>l_fed_tax_rec.fed_information12
2225 ,p_fed_information13 =>l_fed_tax_rec.fed_information13
2226 ,p_fed_information14 =>l_fed_tax_rec.fed_information14
2227 ,p_fed_information15 =>l_fed_tax_rec.fed_information15
2228 ,p_fed_information16 =>l_fed_tax_rec.fed_information16
2229 ,p_fed_information17 =>l_fed_tax_rec.fed_information17
2230 ,p_fed_information18 =>l_fed_tax_rec.fed_information18
2231 ,p_fed_information19 =>l_fed_tax_rec.fed_information19
2232 ,p_fed_information20 =>l_fed_tax_rec.fed_information20
2233 ,p_fed_information21 =>l_fed_tax_rec.fed_information21
2234 ,p_fed_information22 =>l_fed_tax_rec.fed_information22
2235 ,p_fed_information23 =>l_fed_tax_rec.fed_information23
2236 ,p_fed_information24 =>l_fed_tax_rec.fed_information24
2237 ,p_fed_information25 =>l_fed_tax_rec.fed_information25
2238 ,p_fed_information26 =>l_fed_tax_rec.fed_information26
2239 ,p_fed_information27 =>l_fed_tax_rec.fed_information27
2240 ,p_fed_information28 =>l_fed_tax_rec.fed_information28
2241 ,p_fed_information29 =>l_fed_tax_rec.fed_information29
2242 ,p_fed_information30 =>l_fed_tax_rec.fed_information30
2243 ,p_effective_start_date =>ld_start_date
2244 ,p_effective_end_date =>ld_end_date
2245 );
2246 -- we insert a row into the transaction table to show the change
2247 -- to this assignment
2248
2249
2250 /* we want to get the transaction id of the primary assignment and update
2251 that id for all the corresponding transactions hence we need to check
2252 for the primary flag. */
2253
2254 if l_fed_tax_rec.primary_flag = 'Y' then
2255
2256 hr_utility.trace('Primary Flag is = ' || l_fed_tax_rec.primary_flag);
2257 pay_aud_ins.ins(
2258 p_effective_date => l_fed_tax_rec.cur_sysdate
2259 ,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
2260 ,p_transaction_date => l_fed_tax_rec.cur_sysdate
2261 ,p_transaction_effective_date => l_fed_tax_rec.cur_sysdate
2262 ,p_business_group_id => ln_business_group_id
2263 ,p_transaction_subtype => 'W4'
2264 ,p_person_id => ln_person_id
2265 ,p_assignment_id => l_fed_tax_rec.assignment_id
2266 ,p_source1 => '00-000-0000'
2267 ,p_source1_type => 'JURISDICTION'
2268 ,p_source2 => fnd_number.number_to_canonical(l_fed_tax_rec.gre_id)
2269 ,p_source2_type => 'GRE'
2270 ,p_source3 => lv_source_name --'ONLINE W4 FORM'
2271 ,p_source3_type => 'SOURCE_NAME'
2272 ,p_source4 => p_transaction_id
2273 ,p_source4_type => 'TRANSACTION_ID'
2274 ,p_audit_information_category => 'W4 FED'
2275 ,p_audit_information1 => lv_filing_status_code
2276 ,p_audit_information2 => fnd_number.number_to_canonical(p_allowances)
2277 ,p_audit_information3 => fnd_number.number_to_canonical(p_additional_amount)
2278 ,p_audit_information4 => p_exempt_status_code
2279 ,p_audit_information5 => p_last_name_diff
2280 ,p_transaction_parent_id => ln_dummy
2281 ,p_stat_trans_audit_id => ln_parent_audit_id
2282 ,p_object_version_number => l_fed_tax_rec.object_version_number
2283 );
2284
2285 hr_utility.trace('Executed pay_aud_ins.ins ' );
2286 else /* l_primary_flag */
2287
2288 /* reversing ln_dummy and ln_parent_audit_id */
2289
2290 hr_utility.trace('Primary Flag is = ' || l_fed_tax_rec.primary_flag);
2291 pay_aud_ins.ins(
2292 p_effective_date => l_fed_tax_rec.cur_sysdate
2293 ,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
2294 ,p_transaction_date => l_fed_tax_rec.cur_sysdate
2295 ,p_transaction_effective_date => l_fed_tax_rec.cur_sysdate
2296 ,p_business_group_id => ln_business_group_id
2297 ,p_transaction_subtype => 'W4'
2298 ,p_person_id => ln_person_id
2299 ,p_assignment_id => l_fed_tax_rec.assignment_id
2300 ,p_source1 => '00-000-0000'
2301 ,p_source1_type => 'JURISDICTION'
2302 ,p_source2 => fnd_number.number_to_canonical(l_fed_tax_rec.gre_id)
2303 ,p_source2_type => 'GRE'
2304 ,p_source3 => lv_source_name --'ONLINE W4 FORM'
2305 ,p_source3_type => 'SOURCE_NAME'
2306 ,p_source4 => p_transaction_id
2307 ,p_source4_type => 'TRANSACTION_ID'
2308 ,p_audit_information_category => 'W4 FED'
2309 ,p_audit_information1 => lv_filing_status_code
2310 ,p_audit_information2 => fnd_number.number_to_canonical(p_allowances)
2311 ,p_audit_information3 => fnd_number.number_to_canonical(p_additional_amount)
2312 ,p_audit_information4 => p_exempt_status_code
2313 ,p_audit_information5 => p_last_name_diff
2314 ,p_transaction_parent_id => ln_parent_audit_id
2315 ,p_stat_trans_audit_id => ln_dummy
2316 ,p_object_version_number => l_fed_tax_rec.object_version_number
2317 );
2318
2319 end if; /* l_primary_flag */
2320
2321 /* Commenting this for bug 2038691
2322 -- as a sanity check we make sure that the dates are right
2323 if (ld_start_date <> trunc(sysdate)) or
2324 (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2325 hr_utility.trace('ld_start_date is : '|| ld_start_date);
2326 hr_utility.trace('trunc(sysdate) is : '|| trunc(sysdate));
2327 hr_utility.trace('ld_end_date is : '|| ld_end_date);
2328 hr_utility.trace('Date sanity checking');
2329 raise e_date_error;
2330 end if;
2331 Commenting this for bug 2038691*/
2332
2333 -- next we update all state tax records for this assignment id.
2334 -- we don't update the amount withheld, because it is probably of a different magnitude
2335 -- then the state taxes.
2336
2337 -- We will update state record only if the one of the following has changed.
2338
2339 /* to update state tax Yes or No */
2340
2341 hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
2342 hr_utility.trace('p_org_filing_status_code = ' || p_org_filing_status_code);
2343
2344 hr_utility.trace('p_allowances = ' || p_allowances);
2345 hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346
2347 hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
2348 hr_utility.trace('p_org_exempt_status_code = ' || p_org_exempt_status_code);
2349
2350 if ((p_filing_status_code <> p_org_filing_status_code ) OR
2351 (p_allowances <> p_org_allowances) OR
2352 (p_exempt_status_code <> p_org_exempt_status_code) ) then
2353
2354 hr_utility.trace('*** Updating State Record *** ' );
2355
2356 if p_filing_status_code <> p_org_filing_status_code then
2357
2358 lv_filing_status_changed := TRUE;
2359
2360 end if;
2361
2362 if p_allowances <> p_org_allowances then
2363
2364 lv_allowance_changed := TRUE;
2365
2366 end if;
2367
2368 if p_exempt_status_code <> p_org_exempt_status_code then
2369
2370 lv_exempt_changed := TRUE;
2371
2372 end if;
2373
2374 open c_state_tax_rows(l_fed_tax_rec.assignment_id);
2375
2376 loop /* State tax cursor */
2377
2378 /*
2379 FETCH c_state_tax_rows INTO ln_state_tax_rule_id,
2380 ln_ovn,ld_old_start_date,
2381 cu_state_filing_status_code,
2382 cu_state_wa,ln_state_addtional_tax,
2383 lv_state_name, lv_state_code,
2384 ln_assignment_id,
2385 lv_state_default_code, ln_gre_id
2386 ,cu_sit_exempt, lv_state_exempt_code,
2387 cu_sui_wage_base_override_amt,
2388 lv_sit_exempt, lv_sui_exempt,
2389 lv_sdi_exempt, lv_wc_exempt;
2390 */
2391 FETCH c_state_tax_rows INTO l_state_tax_rec;
2392
2393
2394 exit when c_state_tax_rows%NOTFOUND;
2395
2396 hr_utility.trace('Fetched state : '|| l_state_tax_rec.state_code );
2397 hr_utility.trace(' FS : '|| l_state_tax_rec.filing_status_code ||
2398 ' WA : '|| to_char(l_state_tax_rec.withholding_allowances) );
2399
2400 -- count the no. of State Tax Exemptions
2401 lv_state_exemptions_count := l_state_tax_rec.sit_exempt_count
2402 + l_state_tax_rec.sui_exempt_count
2403 + l_state_tax_rec.sdi_exempt_count
2404 + l_state_tax_rec.wc_exempt_count;
2405
2406 hr_utility.trace(gv_package_name||'.update_tax_records-BEFORE STATE UPDATE');
2407
2408 if l_state_tax_rec.effective_start_date = l_state_tax_rec.cur_sysdate then
2409 lv_datetrack_mode := 'CORRECTION';
2410 else
2411 lv_datetrack_mode := 'UPDATE';
2412 end if;
2413
2414
2415 /* Before we update the state tax records, need to check if the
2416 filing status and withholding allowances are same at federal
2417 and state level, if not then we should not update the state
2418 tax records. If this is the first time then we will update
2419 the state records also. Bug 1668926.
2420
2421 Check fed = state FS and WA */
2422
2423 hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
2424 hr_utility.trace('cu_fed_fs_code = ' || l_fed_tax_rec.filing_status_code);
2425
2426 hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427 hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428
2429 hr_utility.trace('lv_fed_exemptions_count = ' || lv_fed_exemptions_count);
2430 hr_utility.trace('lv_state_exemptions_count = ' || lv_state_exemptions_count);
2431
2432 lv_filing_status_code_o := p_filing_status_code;
2433
2434 /*if (l_state_tax_rec.filing_status_code = l_fed_tax_rec.filing_status_code and
2435 l_state_tax_rec.withholding_allowances = l_fed_tax_rec.withholding_allowances and
2436 lv_fed_exemptions_count = lv_state_exemptions_count) OR
2437 (l_state_tax_rec.filing_status_code = '01' and
2438 l_fed_tax_rec.filing_status_code = '03' and
2439 l_state_tax_rec.withholding_allowances = l_fed_tax_rec.withholding_allowances and
2440 lv_fed_exemptions_count = lv_state_exemptions_count) then*/
2441
2442 -- Replacing by Function Call
2443 IF Fed_State_Filing_Status_Match(l_state_tax_rec.state_code
2444 ,l_state_tax_rec.filing_status_code
2445 ,l_fed_tax_rec.filing_status_code
2446 ,l_fed_tax_rec.withholding_allowances
2447 ,l_state_tax_rec.withholding_allowances
2448 ,lv_fed_exemptions_count
2449 ,lv_state_exemptions_count
2450 ,lv_filing_status_code_o) THEN
2451
2452 if p_validate then
2453 hr_utility.trace('B4 g_state_list = ' || g_state_list);
2454
2455 if p_exempt_status_code = 'Y' and lv_exempt_changed = TRUE
2456 then
2457
2458 gv_exempt_change := 'Y';
2459
2460 end if;
2461
2462 g_state_list := get_state_list(ln_person_id,l_primary_only);
2463 hr_utility.trace('g_state_list = ' || g_state_list);
2464 hr_utility.trace('g_state_exempt_list = ' || g_state_exempt_list);
2465
2466 end if;
2467
2468 /* allow update of state tax record(s).
2469 We pass null as the id because if we passed the
2470 id we would need to call some other functions to set
2471 up certain globals, etc. This will be done when the
2472 update procedure does its validation, so we take the
2473 quick way here. */
2474
2475 lv_state_filing_status_code := lv_filing_status_code_o;
2476
2477 -- Following Check NO MORE required
2478 /*
2479 if lv_state_filing_status_code = '03' then
2480
2481 lv_state_filing_status_code := '01';
2482 -- fed '03' maps to state '01'
2483
2484 else
2485 */
2486 BEGIN
2487
2488 pay_sta_bus.chk_filing_status_code(
2489 p_emp_state_tax_rule_id => null
2490 ,p_state_code => l_state_tax_rec.state_code
2491 ,p_filing_status_code => lv_state_filing_status_code
2492 ,p_effective_date => l_state_tax_rec.cur_sysdate
2493 ,p_validation_start_date => l_state_tax_rec.cur_sysdate
2494 ,p_validation_end_date => to_date('31/12/4712','DD/MM/YYYY')
2495 );
2496 EXCEPTION WHEN OTHERS THEN
2497 -- if the federal filing status is not valid for state then
2498 -- do not change the filing status code.
2499 lv_state_filing_status_code := l_state_tax_rec.filing_status_code;
2500 END;
2501
2502 -- end if; /* lv_state_filing_status_code = '03' */
2503
2504 if lv_state_filing_status_code <> l_state_tax_rec.filing_status_code then
2505
2506 lv_state_fs_changed := TRUE;
2507
2508 end if;
2509
2510 hr_utility.trace('Updating State Record ID = ' || to_char(l_state_tax_rec.emp_state_tax_rule_id));
2511
2512 /* Check the state exempt code here -- If the code Y then default the exempt code from federal
2513 else do not replace the exempt code */
2514
2515 /* We check the state exempt code ie. can we default the federal exempt code to the
2516 state */
2517
2518 -- Bug # 6333947
2519 lv_state_exempt_code := l_state_tax_rec.exmpt_status_state_as_fed;
2520
2521 if lv_state_exempt_code = 'Y' then
2522 -- we can default the federal code
2523 l_state_exempt := p_exempt_status_code;
2524 else
2525 -- we cannot default
2526 l_state_exempt := NVL(l_state_tax_rec.sit_exempt, 'N');
2527 end if;
2528
2529 hr_utility.trace('*** l_state_exempt = ' || l_state_exempt);
2530 hr_utility.trace('*** p_exempt_status_code = ' || p_exempt_status_code);
2531 hr_utility.trace('*** cu_sit_exempt = ' || l_state_tax_rec.sit_exempt);
2532
2533
2534 --If lv_filing_status_changed or lv_allowance_changed then
2535 /* If lv_state_fs_changed or lv_allowance_changed then
2536
2537 lv_insert_flag := TRUE;
2538
2539 elsif lv_exempt_changed and lv_state_exempt_code = 'Y' then
2540
2541 lv_insert_flag := TRUE;
2542
2543 end if;*/
2544
2545
2546 /* Modified for bug no 7005814 */
2547 --If lv_filing_status_changed or lv_allowance_changed then
2548
2549
2550 If lv_state_fs_changed then
2551
2552 lv_insert_flag := TRUE;
2553 hr_utility.trace('RLN lv_state_fs_changed TRUE' );
2554 elsif lv_exempt_changed and lv_state_exempt_code = 'Y' then
2555
2556 lv_insert_flag := TRUE;
2557 hr_utility.trace('*RLN** lv_exempt_changed TRUE');
2558 elsif lv_allowance_changed then
2559 if (l_state_exempt <> p_exempt_status_code and p_allowances = 0) then
2560 lv_insert_flag := FALSE;
2561 else
2562 lv_insert_flag := TRUE;
2563 hr_utility.trace('*RLN** lv_allowance_changed TRUE');
2564 end if;
2565 end if;
2566
2567
2568 /* Modified for bug no 7005814 */
2569
2570 if lv_insert_flag then /* mehul */
2571
2572 hr_utility.trace('Update state info : '|| l_state_tax_rec.emp_state_tax_rule_id);
2573 /*
2574 pay_state_tax_rule_api.update_state_tax_rule
2575 (p_emp_state_tax_rule_id => l_state_tax_rec.emp_state_tax_rule_id
2576 ,p_withholding_allowances => p_allowances
2577 ,p_sit_additional_tax => ln_state_addtional_tax
2578 ,p_filing_status_code => lv_state_filing_status_code
2579 --,p_sit_exempt => p_exempt_status_code
2580 ,p_sit_exempt => l_state_exempt
2581 ,p_object_version_number => ln_ovn
2582 ,p_effective_start_date => ld_start_date
2583 ,p_effective_end_date => ld_end_date
2584 ,p_effective_date => trunc(sysdate)
2585 ,p_datetrack_update_mode => lv_datetrack_mode
2586 ,p_validate => p_validate
2587 ,p_sui_wage_base_override_amoun => l_state_tax_rec.sui_wage_base_override_amt
2588 );
2589 */
2590
2591 IF l_state_tax_rec.sit_exempt <> l_state_exempt
2592 AND l_state_exempt = 'Y' THEN
2593
2594 ln_state_addtional_tax := p_additional_amount;
2595
2596 ELSE
2597
2598 ln_state_addtional_tax := l_state_tax_rec.sit_additional_tax;
2599
2600 END IF;
2601
2602 pay_state_tax_rule_api.update_state_tax_rule(
2603 p_validate =>p_validate
2604 ,p_effective_date =>l_state_tax_rec.cur_sysdate
2605 ,p_datetrack_update_mode =>lv_datetrack_mode
2606 ,p_emp_state_tax_rule_id =>l_state_tax_rec.emp_state_tax_rule_id
2607 ,p_object_version_number =>l_state_tax_rec.object_version_number
2608 ,p_additional_wa_amount =>l_state_tax_rec.additional_wa_amount
2609 ,p_filing_status_code =>lv_state_filing_status_code
2610 ,p_remainder_percent =>l_state_tax_rec.remainder_percent
2611 ,p_secondary_wa =>l_state_tax_rec.secondary_wa
2612 ,p_sit_additional_tax =>ln_state_addtional_tax /*Modified for Bug#16465177 fix*/
2613 ,p_sit_override_amount =>l_state_tax_rec.sit_override_amount
2614 ,p_sit_override_rate =>l_state_tax_rec.sit_override_rate
2615 ,p_withholding_allowances =>p_allowances
2616 ,p_excessive_wa_reject_date =>l_state_tax_rec.excessive_wa_reject_date
2617 ,p_sdi_exempt =>l_state_tax_rec.sdi_exempt
2618 ,p_sit_exempt =>l_state_exempt
2619 ,p_sit_optional_calc_ind =>l_state_tax_rec.sit_optional_calc_ind
2620 ,p_state_non_resident_cert =>l_state_tax_rec.state_non_resident_cert
2621 ,p_sui_exempt =>l_state_tax_rec.sui_exempt
2622 ,p_wc_exempt =>l_state_tax_rec.wc_exempt
2623 ,p_sui_wage_base_override_amoun =>l_state_tax_rec.sui_wage_base_override_amount
2624 ,p_supp_tax_override_rate =>l_state_tax_rec.supp_tax_override_rate
2625 ,p_attribute_category =>l_state_tax_rec.attribute_category
2626 ,p_attribute1 =>l_state_tax_rec.attribute1
2627 ,p_attribute2 =>l_state_tax_rec.attribute2
2628 ,p_attribute3 =>l_state_tax_rec.attribute3
2629 ,p_attribute4 =>l_state_tax_rec.attribute4
2630 ,p_attribute5 =>l_state_tax_rec.attribute5
2631 ,p_attribute6 =>l_state_tax_rec.attribute6
2632 ,p_attribute7 =>l_state_tax_rec.attribute7
2633 ,p_attribute8 =>l_state_tax_rec.attribute8
2634 ,p_attribute9 =>l_state_tax_rec.attribute9
2635 ,p_attribute10 =>l_state_tax_rec.attribute10
2636 ,p_attribute11 =>l_state_tax_rec.attribute11
2637 ,p_attribute12 =>l_state_tax_rec.attribute12
2638 ,p_attribute13 =>l_state_tax_rec.attribute13
2639 ,p_attribute14 =>l_state_tax_rec.attribute14
2640 ,p_attribute15 =>l_state_tax_rec.attribute15
2641 ,p_attribute16 =>l_state_tax_rec.attribute16
2642 ,p_attribute17 =>l_state_tax_rec.attribute17
2643 ,p_attribute18 =>l_state_tax_rec.attribute18
2644 ,p_attribute19 =>l_state_tax_rec.attribute19
2645 ,p_attribute20 =>l_state_tax_rec.attribute20
2646 ,p_attribute21 =>l_state_tax_rec.attribute21
2647 ,p_attribute22 =>l_state_tax_rec.attribute22
2648 ,p_attribute23 =>l_state_tax_rec.attribute23
2649 ,p_attribute24 =>l_state_tax_rec.attribute24
2650 ,p_attribute25 =>l_state_tax_rec.attribute25
2651 ,p_attribute26 =>l_state_tax_rec.attribute26
2652 ,p_attribute27 =>l_state_tax_rec.attribute27
2653 ,p_attribute28 =>l_state_tax_rec.attribute28
2654 ,p_attribute29 =>l_state_tax_rec.attribute29
2655 ,p_attribute30 =>l_state_tax_rec.attribute30
2656 ,p_sta_information_category =>l_state_tax_rec.sta_information_category
2657 ,p_sta_information1 =>l_state_tax_rec.sta_information1
2658 ,p_sta_information2 =>l_state_tax_rec.sta_information2
2659 ,p_sta_information3 =>l_state_tax_rec.sta_information3
2660 ,p_sta_information4 =>l_state_tax_rec.sta_information4
2661 ,p_sta_information5 =>l_state_tax_rec.sta_information5
2662 ,p_sta_information6 =>l_state_tax_rec.sta_information6
2663 ,p_sta_information7 =>l_state_tax_rec.sta_information7
2664 ,p_sta_information8 =>l_state_tax_rec.sta_information8
2665 ,p_sta_information9 =>l_state_tax_rec.sta_information9
2666 ,p_sta_information10 =>l_state_tax_rec.sta_information10
2667 ,p_sta_information11 =>l_state_tax_rec.sta_information11
2668 ,p_sta_information12 =>l_state_tax_rec.sta_information12
2669 ,p_sta_information13 =>l_state_tax_rec.sta_information13
2670 ,p_sta_information14 =>l_state_tax_rec.sta_information14
2671 ,p_sta_information15 =>l_state_tax_rec.sta_information15
2672 ,p_sta_information16 =>l_state_tax_rec.sta_information16
2673 ,p_sta_information17 =>l_state_tax_rec.sta_information17
2674 ,p_sta_information18 =>l_state_tax_rec.sta_information18
2675 ,p_sta_information19 =>l_state_tax_rec.sta_information19
2676 ,p_sta_information20 =>l_state_tax_rec.sta_information20
2677 ,p_sta_information21 =>l_state_tax_rec.sta_information21
2678 ,p_sta_information22 =>l_state_tax_rec.sta_information22
2679 ,p_sta_information23 =>l_state_tax_rec.sta_information23
2680 ,p_sta_information24 =>l_state_tax_rec.sta_information24
2681 ,p_sta_information25 =>l_state_tax_rec.sta_information25
2682 ,p_sta_information26 =>l_state_tax_rec.sta_information26
2683 ,p_sta_information27 =>l_state_tax_rec.sta_information27
2684 ,p_sta_information28 =>l_state_tax_rec.sta_information28
2685 ,p_sta_information29 =>l_state_tax_rec.sta_information29
2686 ,p_sta_information30 =>l_state_tax_rec.sta_information30
2687 ,p_effective_start_date =>ld_start_date
2688 ,p_effective_end_date =>ld_end_date
2689 );
2690
2691 -- when we insert into the transaction audit table, we only show
2692 -- where the child record is different from the parent record
2693 -- therefore, if state filing status <> fed filing status we
2694 -- store it, otherwise there is nothing stored except the child
2695 -- record info
2696
2697 lv_context := 'W4 State';
2698 hr_utility.trace('State Context is : ' || lv_context);
2699
2700 -- insert a row in the transaction table
2701 pay_aud_ins.ins(
2702 p_effective_date => l_state_tax_rec.cur_sysdate
2703 ,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
2704 ,p_transaction_date => l_state_tax_rec.cur_sysdate
2705 ,p_transaction_effective_date => l_state_tax_rec.cur_sysdate
2706 ,p_business_group_id => ln_business_group_id
2707 ,p_transaction_subtype => 'W4'
2708 ,p_person_id => ln_person_id
2709 ,p_assignment_id => l_state_tax_rec.assignment_id
2710 ,p_source1 => l_state_tax_rec.state_code || '-000-0000'
2711 ,p_source1_type => 'JURISDICTION'
2712 ,p_source2 => fnd_number.number_to_canonical(l_state_tax_rec.gre_id)
2713 ,p_source2_type => 'GRE'
2714 ,p_source3 => lv_source_name --'ONLINE W4 FORM'
2715 ,p_source3_type => 'SOURCE_NAME'
2716 ,p_source4 => p_transaction_id
2717 ,p_source4_type => 'TRANSACTION_ID'
2718 ,p_audit_information_category => lv_context
2719 ,p_audit_information1 => lv_state_filing_status_code
2720 ,p_audit_information2 => fnd_number.number_to_canonical(p_allowances)
2721 ,p_audit_information3 => fnd_number.number_to_canonical(NVL(l_state_tax_rec.sit_additional_tax, 0))
2722 --,p_audit_information4 => p_exempt_status_code
2723 ,p_audit_information4 => NVL(l_state_exempt, 'N') -- Bug# 6333947
2724 ,p_transaction_parent_id => ln_parent_audit_id
2725 ,p_stat_trans_audit_id => ln_dummy
2726 ,p_object_version_number => l_state_tax_rec.object_version_number
2727 );
2728
2729 hr_utility.trace('State Context is : ' || lv_context ||' after insert ');
2730
2731 end if; /* mehul */
2732 /* Commenting this for bug 2038691
2733 -- as a sanity check we make sure that the dates are right
2734 if (ld_start_date <> trunc(sysdate)) or
2735 (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2736
2737 raise e_date_error;
2738 end if;
2739 Commenting this for bug 2038691*/
2740
2741 end if; -- Check fed = state FS and WA
2742 end LOOP; /* State tax cursor */
2743
2744 close c_state_tax_rows;
2745
2746 end if; /* to update state tax Yes or No */
2747
2748 end LOOP; /* Federal cursor loop */
2749
2750 close c_fed_tax_rows;
2751
2752 -- all updates and processes have been successful if we are here
2753 -- so we commit
2754 if p_validate then
2755 rollback to update_tax_records;
2756 else
2757 commit;
2758 end if;
2759
2760 hr_utility.trace('Leaving ' || gv_package_name || '.update_tax_records');
2761
2762
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 rollback to update_tax_records;
2766
2767 raise_application_error(-20001,'Fatal Error while commit');
2768
2769
2770 end update_tax_records;
2771
2772 PROCEDURE update_w4_info(
2773 p_validate in boolean default false ,
2774 p_transaction_step_id in number) IS
2775
2776 Cursor c_trans_value is
2777
2778 select datatype,name,varchar2_value,number_value
2779 from hr_api_transaction_values
2780 where transaction_step_id = p_transaction_step_id
2781 order by transaction_value_id;
2782
2783 Cursor c_trans_step is
2784
2785 select transaction_id, creator_person_id
2786 from hr_api_transaction_steps
2787 where transaction_step_id = p_transaction_step_id;
2788
2789 l_datatype hr_api_transaction_values.datatype%type;
2790 l_name hr_api_transaction_values.name%type;
2791 l_varchar2_value hr_api_transaction_values.varchar2_value%type;
2792 l_number_value hr_api_transaction_values.number_value%type;
2793
2794 l_filing_status hr_api_transaction_values.varchar2_value%type;
2795
2796 l_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
2797
2798 l_allowances hr_api_transaction_values.number_value%type;
2799 l_add_tax hr_api_transaction_values.number_value%type;
2800 l_exempt hr_api_transaction_values.varchar2_value%type;
2801
2802 l_org_filing_status_code pay_us_emp_fed_tax_rules_f.filing_status_code%TYPE;
2803 l_org_allowances hr_api_transaction_values.number_value%type;
2804 l_org_exempt hr_api_transaction_values.varchar2_value%type;
2805 l_last_name_diff hr_api_transaction_values.varchar2_value%TYPE;
2806
2807 l_item_type hr_api_transaction_steps.item_type%type;
2808 l_item_key hr_api_transaction_steps.item_key%type;
2809 l_activity_id hr_api_transaction_steps.activity_id%type;
2810 l_person_id hr_api_transaction_steps.creator_person_id%type;
2811
2812 l_transaction_id hr_api_transaction_steps.transaction_id%type;
2813
2814 lv_trans_type VARCHAR2(50);
2815 lv_source_name VARCHAR2(50);
2816
2817 begin
2818 hr_utility.trace('Entering package update_w4_info ');
2819 hr_utility.trace('p_transaction_step_id is : ' || p_transaction_step_id );
2820
2821 open c_trans_step;
2822 fetch c_trans_step into l_transaction_id,l_person_id;
2823 close c_trans_step;
2824
2825 open c_trans_value;
2826 loop
2827 exit when c_trans_value%NOTFOUND;
2828 fetch c_trans_value into l_datatype, l_name, l_varchar2_value, l_number_value;
2829 if l_datatype = 'VARCHAR2' and l_name = 'P_FS_CODE' then
2830 l_filing_status_code := l_varchar2_value;
2831
2832 elsif l_datatype = 'VARCHAR2' and l_name = 'P_ORG_FS_CODE' then
2833 l_org_filing_status_code := l_varchar2_value;
2834
2835 elsif l_datatype = 'NUMBER' and l_name = 'P_ALLOWANCES' then
2836 l_allowances := l_number_value;
2837
2838 elsif l_datatype = 'NUMBER' and l_name = 'P_ORG_ALLOWANCES' then
2839 l_org_allowances := l_number_value;
2840
2841 elsif l_datatype = 'NUMBER' and l_name = 'P_ADDITIONAL_TAX' then
2842 l_add_tax := l_number_value;
2843
2844 elsif l_datatype = 'VARCHAR2' and l_name = 'P_EXEMPT' then
2845 l_exempt := l_varchar2_value;
2846 if l_exempt = 'No' then
2847 l_exempt := 'N';
2848 else
2849 l_exempt := 'Y';
2850 end if;
2851
2852 elsif l_datatype = 'VARCHAR2' and l_name = 'P_ORG_EXEMPT' then
2853 l_org_exempt := l_varchar2_value;
2854 if l_org_exempt = 'No' then
2855 l_org_exempt := 'N';
2856 else
2857 l_org_exempt := 'Y';
2858 end if;
2859 -- Bug# 6468114
2860 elsif l_datatype = 'VARCHAR2' and l_name = 'P_LAST_NAME_DIFF' then
2861 l_last_name_diff := l_varchar2_value;
2862 end if;
2863
2864 end loop;
2865 close c_trans_value;
2866
2867 hr_utility.trace(' Transaction Id is : '|| to_char(l_transaction_id));
2868 hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869
2870 hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871 hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872 hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2873 hr_utility.trace(' Exempt Status is : '|| l_exempt);
2874
2875 lv_trans_type := 'ONLINE_TAX_FORMS';
2876 lv_source_name := 'ONLINE W4 FORM';
2877
2878 update_tax_records(p_filing_status_code => l_filing_status_code,
2879 p_org_filing_status_code => l_org_filing_status_code,
2880 p_allowances => l_allowances,
2881 p_org_allowances => l_org_allowances,
2882 p_additional_amount => l_add_tax,
2883 p_last_name_diff => l_last_name_diff, --'N',
2884 p_exempt_status_code => l_exempt,
2885 p_org_exempt_status_code => l_org_exempt,
2886 p_transaction_id => l_transaction_id,
2887 p_person_id => l_person_id,
2888 p_transaction_type => lv_trans_type,
2889 p_source_name => lv_source_name,
2890 p_validate => p_validate);
2891
2892 hr_utility.trace('Leaving package update_w4_info ');
2893 End;
2894
2895 FUNCTION get_state_list(p_person_id IN per_people_f.person_id%TYPE,
2896 p_primary_flag IN varchar2 )
2897
2898 RETURN VARCHAR2 IS
2899
2900 CURSOR c_state_tax_rows IS
2901 select pus.state_name,
2902 stif.sta_information7,
2903 /*nvl(stif.sta_information9,'N') : Bug 6346579 */
2904 nvl(stif.sta_information9,'Y')
2905 from pay_us_emp_state_tax_rules_f str,
2906 per_assignments_f paf,
2907 pay_us_state_tax_info_f stif,
2908 pay_us_states pus,
2909 hr_soft_coding_keyflex hsck
2910 where paf.person_id = p_person_id
2911 and paf.assignment_id = str.assignment_id
2912 and paf.assignment_type = 'E'
2913 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
2914 and decode(p_primary_flag,'Y',paf.primary_flag,'Y') = 'Y'
2915 and str.state_code = stif.state_code
2916 and str.state_code = pus.state_code
2917 and stif.sta_information7 like 'Y%'
2918 and trunc(sysdate) between stif.effective_start_date and
2919 stif.effective_end_date
2920 and trunc(sysdate) between paf.effective_start_date and
2921 paf.effective_end_date
2922 and trunc(sysdate) between str.effective_start_date and
2923 str.effective_end_date;
2924
2925 lv_state_list VARCHAR2(10000); /* Bug 14109584*/
2926 --lv_state_exempt_list VARCHAR2(300);
2927
2928 lb_comma_flag BOOLEAN := false;
2929 lb_ex_comma_flag BOOLEAN := false;
2930
2931 LV_STATE_NAME varchar2(50);
2932
2933 lv_state_default_code varchar2(10);
2934 lv_state_exempt_code varchar2(10);
2935
2936 l_state_count number(3) := 0 ;
2937 l_state_exempt_count number(3) := 0 ;
2938
2939 Begin
2940 hr_utility.trace('Entering get_state_list');
2941
2942 IF gv_exempt_change = 'Y' THEN
2943 lv_state_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_ADDTAX_STATE_LIST','PAY');
2944 ELSE
2945 lv_state_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_STATE_LIST','PAY');
2946 END IF;
2947
2948 g_state_exempt_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_EXEMPT_STATE_LIST','PAY');
2949
2950 OPEN c_state_tax_rows;
2951 LOOP
2952 --Bug 14109584 exit when c_state_tax_rows%NOTFOUND;
2953
2954 FETCH c_state_tax_rows INTO lv_state_name, lv_state_default_code,lv_state_exempt_code;
2955
2956 exit when c_state_tax_rows%NOTFOUND; --Bug 14109584
2957
2958 hr_utility.trace('state is --> '|| lv_state_name ||' Code is --> '|| lv_state_default_code||' Exempt Code is --> '|| lv_state_exempt_code);
2959 hr_utility.trace('looking at c_state_tax_rows');
2960
2961 -- append the name to the state list to the message
2962 -- we do not append it if the code is 'Y QUIET'
2963 -- instr(lv_state_list,lv_state_name) will ensure that the state
2964 -- is appended only once to the list.
2965
2966 if instr(lv_state_list,lv_state_name) = 0 and lv_state_default_code = 'Y'
2967 and lv_state_exempt_code = 'Y' then
2968
2969 if lb_comma_flag then
2970 lv_state_list := lv_state_list || ', ' ||lv_state_name;
2971 else
2972 lb_comma_flag := true;
2973 lv_state_list := lv_state_list || ' '||lv_state_name;
2974 end if;
2975 l_state_count := 1;
2976
2977 end if;
2978
2979 if instr(g_state_exempt_list,lv_state_name) = 0 and lv_state_exempt_code = 'N' then
2980 if lb_ex_comma_flag then
2981 g_state_exempt_list := g_state_exempt_list || ', ' ||lv_state_name;
2982 else
2983 lb_ex_comma_flag := true;
2984 g_state_exempt_list := g_state_exempt_list || ' '||lv_state_name;
2985 end if;
2986 l_state_exempt_count := 1;
2987
2988 end if;
2989 END LOOP;
2990 close c_state_tax_rows;
2991
2992 if l_state_count = 0 then
2993
2994 lv_state_list := null;
2995 end if;
2996
2997 if l_state_exempt_count = 0 then
2998
2999 g_state_exempt_list := null;
3000 end if;
3001
3002 hr_utility.trace('Leaving get_state_list');
3003 return lv_state_list;
3004
3005 End;
3006
3007 FUNCTION get_org_context(p_person_id IN per_people_f.person_id%TYPE,
3008 p_context hr_organization_information.org_information_context%TYPE,
3009 p_level IN VARCHAR2)
3010
3011 RETURN VARCHAR2 IS
3012
3013 CURSOR c_person_info IS
3014 select ppf.business_group_id,
3015 paf.organization_id,
3016 paf.assignment_id,
3017 ppf.person_id,
3018 ppf.employee_number,
3019 ppf.national_identifier,
3020 ppf.full_name
3021 from per_people_f ppf,
3022 per_assignments_f paf
3023 where ppf.person_id = p_person_id
3024 and paf.person_id = ppf.person_id
3025 and paf.assignment_type = 'E'
3026 and paf.primary_flag = 'Y'
3027 and trunc(sysdate) between paf.effective_start_date and
3028 paf.effective_end_date
3029 and trunc(sysdate) between ppf.effective_start_date and
3030 ppf.effective_end_date;
3031
3032 CURSOR c_org_context(ln_organization_id hr_organization_information.organization_id%TYPE) IS
3033 select nvl(hoi.ORG_INFORMATION2,'N') ORG_INFORMATION2
3034 from hr_organization_information hoi
3035 where hoi.org_information_context = p_context
3036 and hoi.organization_id = ln_organization_id;
3037
3038 lv_proc varchar2(80);
3039 lr_person_info_rec c_person_info%ROWTYPE;
3040 lr_org_info_rec c_org_context%rowtype;
3041 lv_result varchar2(80);
3042
3043 Begin
3044 lv_proc := gv_package_name || '.get_org_context';
3045
3046 hr_utility.trace('Entering '||lv_proc);
3047
3048 OPEN c_person_info;
3049 FETCH c_person_info INTO lr_person_info_rec;
3050
3051 if c_person_info%notfound then
3052 CLOSE c_person_info;
3053 raise no_data_found;
3054 ELSE
3055 close c_person_info;
3056 end if;
3057
3058 hr_utility.trace('lr_person_info_rec.organization_id = '||lr_person_info_rec.organization_id);
3059 hr_utility.trace('lr_person_info_rec.business_group_id = '||lr_person_info_rec.business_group_id);
3060
3061 if p_level = 'BG' then
3062 OPEN c_org_context(lr_person_info_rec.business_group_id);
3063 FETCH c_org_context INTO lr_org_info_rec;
3064 if c_org_context%notfound then
3065 lv_result := 'NOTFOUND';
3066 else
3067 lv_result := lr_org_info_rec.org_information2;
3068 end if;
3069 CLOSE c_org_context;
3070 elsif p_level ='ORG'then
3071 OPEN c_org_context(lr_person_info_rec.organization_id);
3072 FETCH c_org_context INTO lr_org_info_rec;
3073 if c_org_context%notfound then
3074 lv_result := 'NOTFOUND';
3075 else
3076 lv_result := lr_org_info_rec.org_information2;
3077 end if;
3078 CLOSE c_org_context;
3079 end if;
3080
3081 hr_utility.trace('lr_org_info_rec.org_information2 = '||lr_org_info_rec.org_information2);
3082 hr_utility.trace('lv_result = '||lv_result);
3083
3084 hr_utility.trace('Leaving '||lv_proc);
3085 return lv_result;
3086
3087 exception
3088 when no_data_found then
3089 hr_utility.trace('Person Data not Found');
3090 hr_utility.trace('In Exception '||lv_proc);
3091
3092 End get_org_context;
3093
3094 /* Uncomment following two lines for debug */
3095 -- begin
3096 -- hr_utility.trace_on(null,'ORACLETEST');
3097
3098 END pay_us_web_w4;
[Home] [Help]