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