1 PACKAGE BODY pay_us_emp_tax_rules_pkg as
2 /* $Header: pyustaxr.pkb 115.13 2004/01/21 07:18:21 saurgupt ship $ */
3 --
4 --
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1993 Oracle Corporation. *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation, *
18 * 500 Oracle Parkway, Redwood City, CA, 94065. *
19 * *
20 ******************************************************************
21
22 Name : pay_us_emp_tax_rules_pkg
23
24 Description : This package holds building blocks used in maintenace
25 of US employee tax rule using PER_ASSIGNMENT_EXTRA_INFO
26 table.
27
28 Uses : hr_utility
29
30 Change List
31 -----------
32 Date Name Vers Description
33 ---- ---- ---- -----------
34 OCT-22-1993 RMAMGAIN 1.0 Created with following proc.
35 Insert_row
36 Update_row
37 Lock_row
38 Delete_row
39 check_unique
40 get_set_def
41 NOV-09-1993 RMAMGAIN 40.1 Created Procs to call
42 call element entry API.
43 NOV-14-1993 RMAMGAIN 40.2 Changes to create Ele. Ent.
44 for PAY VALUE.
45 NOV-21-1993 RMAMGAIN 40.5 Change Time in state and
46 withholding allow. Columns
47 SEP-28-1994 RMAMGAIN 40.3 BUG 1257, BUG1257
48 MAR-06-1995 GPAYTONM 40.9 Added more user friendly error messages
49 for uniqueness check
50 (HR_7322_TAX_ONE_RULE_ONLY)
51 26-APR-95 gpaytonm 40.11 Added function call to addr_val to get geo code
52 22-JUN-95 gpaytonm 40.12 Changed insert_def_loc to insert default resident county
53 record and default school district where appropriate.
54 Changed references to PAY_US_CITIES to PAY_US_CITY_NAMES
55 + PAY_US_ZIP_CODES
56 12-JUL-95 gpaytonm 40.13 Made sure that vertex tax entry not inserted
57 for default local county tax record.
58 25-JUL-95 AForte Changed tokenised message
59 HR_7322_TAX_ONE_RULE_ONLY
60 to hard coded messages
61 HR_7719_TAX_ONE_RULE_ONLY
62 HR_7720_TAX_ONE_RULE_ONLY and
63 Changed tokenised message
64 HR_13140_TAX_ELEMNT_ERROR where token was
65 'Workers Compensation', to
66 HR_7713_TAX_ELEMENT_ERROR
67 03-AUG-95 gpaytonm 40.14 Changed get_set_Def to default_tax. Added
68 on_insert to be called from form such that
69 defaulting VERTEX entries is easier to
70 control - on_insert always creates one,
71 but when defaulting want may not want
72 a vertex record but do want a tax record.
73 Added code to cater for unknown cities -
74 inserts county record in this case. Altered
75 code to only insert one vertex entry when
76 work and res cities are the same.
77 Renamed create_tax_ele_Entry to create_vertex_entry
78 28-SEP-95 gpaytonm 40.16 Added COMMIT in default_tax if no error
79 - rather than committing on form
80
81 22-AUG-96 lwthomps 40.18 Added 0 default values in defualt tax
82 for state and federal supplemental
83 tax override rates. #316663
84 10-SEP-96 lwthomps 40.19 Now a county tax record is created
85 for both the work and resident
86 locality. #391886
87 16-SEP-96 lwthomps 40.20 #390941 version 40.19skipped for
88 P1 fix. 40.20 is 40.18 with changes.
89 Now it will only create county record
90 after it checks one doesn't already
91 exists.
92
93 17-SEP-96 lwthomps 40.21 #390941 removed unwanted dependency on
94 316663.
95
96 23-SEP-96 lwthomps 40.22 This merges all changes from 40.19
97 and 40.20. Also adds new Procedure called
98 Create_County_Record that is called from
99 form and creates a county record if a
100 city record is created through the form.
101
102 11-NOV-96 lwthomps 40.23 Adds a call to PAY_ASG_GEO_PKG.create_asg_geo_row.
103 This is dependent on the existence of the
104 new table: pay_us_asg_reporting.
105 BUG: 420465.
106
107 13-Jan-97 lwthomps 40.24 School Ditrict Changes:
108 No longer allows the same school district code
109 in multiple cities in the same state. SD codes
110 are only defaulted for Primary residences now,
111 and if that school district exists somewhere else
112 (another locality for this assignment) then it
113 is set to NULL.
114 Added Procedure Update_attribute to maintain
115 SD codes.
116
117 12-Feb-97 lwthomps 40.25 Leap Frog Version
118
119 20-Feb-97 lwthomps 40.26 Merged changes from preivous leap
120 frogged versions.
121 20-Feb-97 lwthomps 40.27 Defaulting of tax rules. Now creates
122 records from the assignment and
123 address forms. 268389
124
125
126 05-Mar-97 jalloun 40.28 Changed all calls to sys.dual to
127 new standard.
128
129 07-Mar-97 lwthomps 40.29 Defaulting of tax rules.
130 Now catches filing status and
131 allowances from the Federal record
132 if it is specified at the GRE level
133 and there is an appropriate filing
134 status for the state:
135 FIT SIT
136 ---------------- -------------
137 Single -> Single
138 Married Joint -> Married
139 Married Sep -> Married
140 Head of House -> Head of House
141
142 07-Mar-97 lwthomps 40.30 Cleaned up change log.
143
144 08-Mar-97 lwthomps 40.31 268389. Added check for when
145 state tax rules had been defined
146 but no default standard chosen.
147
148 31-Mar-97 lwthomps 40.32 268389. Added more messages
149 for the assignment form in default
150 tax with validation. Changed
151 the date effective sections in
152 create_vertex_entry to ensure
153 duplicate records are not created
154 in cases where people move into
155 a jurisdiction earlier than the
156 original startdate of the element
157 entry.
158
159 19-May-97 lwthomps 40.33 495165. Added additional validation
160 to default_tax_with validation such
161 that it will not update the percent time
162 in state if the sum accross existing
163 records is not = 0%.
164
165 05-JUN-97 lwthomps 40.34 Created an overloaded version of
166 default tax such that the package
167 only commits when entered from the
168 Tax Rules Form.
169 This is to fix: 501979
170 16-JUL-97 lwthomps 40.35 Added additional validation in the
171 create_vertex_entry procedure so that
172 it checks that a vertex element entry
173 of the same jurisdiction does not exist
174 prior to inserting. Previously this
175 validation was done on the
176 per_assignment_extra_information records
177 only.
178 07-NOV-97 lwthomps 40.36 Added additional erroring for
179 invalid resident address and
180 existence of tax records. If
181 a tax record has been deleted
182 and the element_entry is modified
183 it will roll back and error.
184 07-NOV-97 lwthomps 40.37 Added one more check for misc
185 error raised by other packages.
186 18-MAY-98 ekim 40.39 Bug #657312. Modified csr_qualify_info
187 to validate against the date.
188 21-Apr-99 scgrant 115.1 Multi-radix changes.
189 14-Feb-00 alogue 115.4 Utf8 support.
190 18-JAN-02 fusman 115.5 Added dbdrv command.
191 24-JUN-02 rsirigir 115.8 Modified checkfile syntax as
192 per bug 2429703
193 09-AUG-02 ahanda 115.12 Changed cursor vtx_info.
194 21-JAN-04 saurgupt 115.13 Bug 3354046: Changed definitions of
195 cursors csr_filing_status, csr_wc_element
196 and csr_fed_or_def to remove FTS and MJC.
197 */
198
199 --
200 PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
201 X_Assignment_Extra_Info_Id IN OUT nocopy NUMBER,
202 X_Assignment_Id NUMBER,
203 X_Information_Type VARCHAR2,
204 X_session_date DATE,
205 X_jurisdiction VARCHAR2,
206 X_Aei_Information_Category VARCHAR2 default null,
207 X_Aei_Information1 VARCHAR2 default null,
208 X_Aei_Information2 VARCHAR2 default null,
209 X_Aei_Information3 VARCHAR2 default null,
210 X_Aei_Information4 VARCHAR2 default null,
211 X_Aei_Information5 VARCHAR2 default null,
212 X_Aei_Information6 VARCHAR2 default null,
213 X_Aei_Information7 VARCHAR2 default null,
214 X_Aei_Information8 VARCHAR2 default null,
215 X_Aei_Information9 VARCHAR2 default null,
216 X_Aei_Information10 VARCHAR2 default null,
217 X_Aei_Information11 VARCHAR2 default null,
218 X_Aei_Information12 VARCHAR2 default null,
219 X_Aei_Information13 VARCHAR2 default null,
220 X_Aei_Information14 VARCHAR2 default null,
221 X_Aei_Information15 VARCHAR2 default null,
222 X_Aei_Information16 VARCHAR2 default null,
223 X_Aei_Information17 VARCHAR2 default null,
224 X_Aei_Information18 VARCHAR2 default null,
225 X_Aei_Information19 VARCHAR2 default null,
226 X_Aei_Information20 VARCHAR2 default null
227 ) IS
228 X_User_Id NUMBER;
229 X_Login_Id NUMBER;
230 X_ret NUMBER;
231 X_time varchar2(20);
232 x_other_sd VARCHAR2(20);
233 X_sd_rowid VARCHAR2(30);
234 --
235 CURSOR C IS SELECT rowid FROM PER_ASSIGNMENT_EXTRA_INFO
236 WHERE assignment_extra_info_id = X_Assignment_Extra_Info_Id;
237 --
238 CURSOR C2 IS SELECT per_assignment_extra_info_s.nextval FROM sys.dual;
239 --
240 -- Because of how we report school tax balances we need to check
241 -- that each local record within a state has a unique SD code
242 --
243 CURSOR check_sd IS
244 SELECT rowid
245 FROM per_assignment_extra_info
246 WHERE assignment_id = X_Assignment_Id
247 AND aei_information9 IS NOT NULL
248 AND Information_type = 'LOCALITY'
249 AND aei_information9 = X_Aei_Information9
250 AND aei_information1 = X_aei_information1
251 AND assignment_extra_info_id <> X_assignment_extra_info_id;
252
253 BEGIN
254 --
255 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',1);
256 X_ret := check_unique(X_Assignment_Id, X_Information_Type,
257 X_Aei_Information1, X_Aei_Information2);
258 --
259 -- if row exist raise error
260 -- otherwise continue
261 --
262 if X_ret = 0
263 then
264 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',2);
265 X_User_Id := FND_PROFILE.Value('USER_ID');
266 X_Login_Id := FND_PROFILE.Value('LOGIN_ID');
267 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',3);
268 if (X_Assignment_Extra_Info_Id is NULL) then
269 OPEN C2;
270 FETCH C2 INTO X_Assignment_Extra_Info_Id;
271 CLOSE C2;
272 end if;
273
274 OPEN check_sd;
275
276 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',4);
277 INSERT INTO PER_ASSIGNMENT_EXTRA_INFO(
278 creation_date,
279 created_by,
280 last_update_date,
281 last_updated_by,
282 last_update_login,
283 assignment_extra_info_id,
284 assignment_id,
285 information_type,
286 aei_information_category,
287 aei_information1,
288 aei_information2,
292 aei_information6,
289 aei_information3,
290 aei_information4,
291 aei_information5,
293 aei_information7,
294 aei_information8,
295 aei_information9,
296 aei_information10,
297 aei_information11,
298 aei_information12,
299 aei_information13,
300 aei_information14,
301 aei_information15,
302 aei_information16,
303 aei_information17,
304 aei_information18,
305 aei_information19,
306 aei_information20)
307 VALUES (
308 SYSDATE,
309 X_User_Id,
310 SYSDATE,
311 X_User_Id,
312 X_Login_Id,
313 X_Assignment_Extra_Info_Id,
314 X_Assignment_Id,
315 X_Information_Type,
316 X_Aei_Information_Category,
317 X_Aei_Information1,
318 X_Aei_Information2,
319 X_Aei_Information3,
320 X_Aei_Information4,
321 X_Aei_Information5,
322 X_Aei_Information6,
323 X_Aei_Information7,
324 X_Aei_Information8,
325 X_Aei_Information9,
326 X_Aei_Information10,
327 X_Aei_Information11,
328 X_Aei_Information12,
329 X_Aei_Information13,
330 X_Aei_Information14,
331 X_Aei_Information15,
332 X_Aei_Information16,
333 X_Aei_Information17,
334 X_Aei_Information18,
335 X_Aei_Information19,
336 X_Aei_Information20
337 );
338 --
339 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',5);
340 --
341 if sql%notfound then
342 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
343 hr_utility.set_message_token('PROCEDURE','US_EMP_TAX.INSERT_ROW');
344 hr_utility.set_message_token('STEP','1');
345 hr_utility.raise_error;
346 end if;
347 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',6);
348 OPEN C;
349 FETCH C INTO X_Rowid;
350 if (C%NOTFOUND) then
351 CLOSE C;
352 RAISE NO_DATA_FOUND;
353 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',7);
354 end if;
355 --
356 if X_information_type = 'FEDERAL' then
357 pay_us_emp_tax_rules_pkg.create_wc_ele_entry(
358 P_assignment_id => X_assignment_id,
359 P_session_date => X_session_date,
360 P_jurisdiction => X_AEI_INFORMATION18);
361 else /* maintain the denormalized table */
362 -- Seed table for TSL
363 PAY_ASG_GEO_PKG.create_asg_geo_row(P_assignment_id => X_assignment_id,
364 P_jurisdiction => X_jurisdiction,
365 P_tax_unit_id => NULL );
366 end if; --Federal
367 if X_information_type = 'LOCALITY' THEN
368 FETCH check_sd into X_sd_rowid;
369
370 if check_sd%FOUND THEN --Need to wipe the school district
371 Update_Attribute(p_rowid => x_sd_rowid,
372 p_attribute_type => 'SCHOOL',
373 p_new_value => NULL,
374 p_jurisdiction => NULL,
375 p_state_abbrev => NULL,
376 p_assignment_id => NULL);
377
378 end if; --wiping of school dst
379 IF (X_aei_information9 IS NOT NULL) THEN -- School district exists
380 -- Seed table for TSL (School if local)
381 PAY_ASG_GEO_PKG.create_asg_geo_row(P_assignment_id => X_assignment_id,
382 P_jurisdiction => substr(X_jurisdiction,1,2)||'-'||X_Aei_Information9,
383 P_tax_unit_id => NULL );
384 END IF; -- School district not null
385
386
387 end if; --Locality
388 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_row',9);
389 CLOSE C;
390 --
391 -- RAISE error of unique check failure
392 -- message set in check_unique
393 else
394 hr_utility.raise_error;
395 end if;
396 CLOSE check_sd;
397 --
398 END Insert_Row;
399 --
400 ------------------------------ Lock_Row ---------------------------------
401 --
402 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
403 X_Assignment_Extra_Info_Id NUMBER,
404 X_Assignment_Id NUMBER,
405 X_Information_Type VARCHAR2,
406 X_Aei_Information1 VARCHAR2 default null,
407 X_Aei_Information2 VARCHAR2 default null,
408 X_Aei_Information3 VARCHAR2 default null,
409 X_Aei_Information4 VARCHAR2 default null,
410 X_Aei_Information5 VARCHAR2 default null,
411 X_Aei_Information6 VARCHAR2 default null,
412 X_Aei_Information7 VARCHAR2 default null,
413 X_Aei_Information8 VARCHAR2 default null,
414 X_Aei_Information9 VARCHAR2 default null,
415 X_Aei_Information10 VARCHAR2 default null,
416 X_Aei_Information11 VARCHAR2 default null,
420 X_Aei_Information15 VARCHAR2 default null,
417 X_Aei_Information12 VARCHAR2 default null,
418 X_Aei_Information13 VARCHAR2 default null,
419 X_Aei_Information14 VARCHAR2 default null,
421 X_Aei_Information16 VARCHAR2 default null,
422 X_Aei_Information17 VARCHAR2 default null,
423 X_Aei_Information18 VARCHAR2 default null,
424 X_Aei_Information19 VARCHAR2 default null,
425 X_Aei_Information20 VARCHAR2 default null
426 ) IS
427 --
428 CURSOR C IS
429 SELECT *
430 FROM PER_ASSIGNMENT_EXTRA_INFO
431 WHERE rowid = X_Rowid
432 FOR UPDATE of Assignment_Extra_Info_Id NOWAIT;
433 --
434 Recinfo C%ROWTYPE;
435 --
436 BEGIN
437 OPEN C;
438 FETCH C INTO Recinfo;
439 if (C%NOTFOUND) then
440 CLOSE C;
441 RAISE NO_DATA_FOUND;
442 end if;
443 CLOSE C;
444 --
445 --
446 Recinfo.aei_information1 := RTRIM(Recinfo.aei_information1);
447 Recinfo.aei_information2 := RTRIM(Recinfo.aei_information2);
448 Recinfo.aei_information3 := RTRIM(Recinfo.aei_information3);
449 Recinfo.aei_information4 := RTRIM(Recinfo.aei_information4);
450 Recinfo.aei_information5 := RTRIM(Recinfo.aei_information5);
451 Recinfo.aei_information6 := RTRIM(Recinfo.aei_information6);
452 Recinfo.aei_information7 := RTRIM(Recinfo.aei_information7);
453 Recinfo.aei_information8 := RTRIM(Recinfo.aei_information8);
454 Recinfo.aei_information9 := RTRIM(Recinfo.aei_information9);
455 Recinfo.aei_information10 := RTRIM(Recinfo.aei_information10);
456 Recinfo.aei_information11 := RTRIM(Recinfo.aei_information11);
457 Recinfo.aei_information12 := RTRIM(Recinfo.aei_information12);
458 Recinfo.aei_information13 := RTRIM(Recinfo.aei_information13);
459 Recinfo.aei_information14 := RTRIM(Recinfo.aei_information14);
460 Recinfo.aei_information15 := RTRIM(Recinfo.aei_information15);
461 Recinfo.aei_information16 := RTRIM(Recinfo.aei_information16);
462 Recinfo.aei_information17 := RTRIM(Recinfo.aei_information17);
463 Recinfo.aei_information18 := RTRIM(Recinfo.aei_information18);
464 Recinfo.aei_information19 := RTRIM(Recinfo.aei_information19);
465 Recinfo.aei_information20 := RTRIM(Recinfo.aei_information20);
466 Recinfo.information_type := RTRIM(Recinfo.information_type);
467 --
468 --
469 if (
470 ( (Recinfo.assignment_extra_info_id = X_Assignment_Extra_Info_Id)
471 OR ( (Recinfo.assignment_extra_info_id IS NULL)
472 AND (X_Assignment_Extra_Info_Id IS NULL)))
473 AND ( (Recinfo.assignment_id = X_Assignment_Id)
474 OR ( (Recinfo.assignment_id IS NULL)
475 AND (X_Assignment_Id IS NULL)))
476 AND ( (Recinfo.information_type = X_Information_Type)
477 OR ( (Recinfo.information_type IS NULL)
478 AND (X_Information_Type IS NULL)))
479 AND ( (Recinfo.aei_information1 = X_Aei_Information1)
480 OR ( (Recinfo.aei_information1 IS NULL)
481 AND (X_Aei_Information1 IS NULL)))
482 AND ( (Recinfo.aei_information2 = X_Aei_Information2)
483 OR ( (Recinfo.aei_information2 IS NULL)
484 AND (X_Aei_Information2 IS NULL)))
485 AND ( (Recinfo.aei_information3 = X_Aei_Information3)
486 OR ( (Recinfo.aei_information3 IS NULL)
487 AND (X_Aei_Information3 IS NULL)))
488 AND ( (Recinfo.aei_information4 = X_Aei_Information4)
489 OR ( (Recinfo.aei_information4 IS NULL)
490 AND (X_Aei_Information4 IS NULL)))
491 AND ( (Recinfo.aei_information5 = X_Aei_Information5)
492 OR ( (Recinfo.aei_information5 IS NULL)
493 AND (X_Aei_Information5 IS NULL)))
494 AND ( (Recinfo.aei_information6 = X_Aei_Information6)
495 OR ( (Recinfo.aei_information6 IS NULL)
496 AND (X_Aei_Information6 IS NULL)))
497 AND ( (Recinfo.aei_information7 = X_Aei_Information7)
498 OR ( (Recinfo.aei_information7 IS NULL)
499 AND (X_Aei_Information7 IS NULL)))
500 AND ( (Recinfo.aei_information8 = X_Aei_Information8)
501 OR ( (Recinfo.aei_information8 IS NULL)
502 AND (X_Aei_Information8 IS NULL)))
503 AND ( (Recinfo.aei_information9 = X_Aei_Information9)
504 OR ( (Recinfo.aei_information9 IS NULL)
505 AND (X_Aei_Information9 IS NULL)))
506 AND ( (Recinfo.aei_information10 = X_Aei_Information10)
507 OR ( (Recinfo.aei_information10 IS NULL)
508 AND (X_Aei_Information10 IS NULL)))
509 AND ( (Recinfo.aei_information11 = X_Aei_Information11)
510 OR ( (Recinfo.aei_information11 IS NULL)
511 AND (X_Aei_Information11 IS NULL)))
512 AND ( (Recinfo.aei_information12 = X_Aei_Information12)
513 OR ( (Recinfo.aei_information12 IS NULL)
514 AND (X_Aei_Information12 IS NULL)))
515 AND ( (Recinfo.aei_information13 = X_Aei_Information13)
516 OR ( (Recinfo.aei_information13 IS NULL)
517 AND (X_Aei_Information13 IS NULL)))
518 AND ( (Recinfo.aei_information14 = X_Aei_Information14)
519 OR ( (Recinfo.aei_information14 IS NULL)
520 AND (X_Aei_Information14 IS NULL)))
521 AND ( (Recinfo.aei_information15 = X_Aei_Information15)
525 OR ( (Recinfo.aei_information16 IS NULL)
522 OR ( (Recinfo.aei_information15 IS NULL)
523 AND (X_Aei_Information15 IS NULL)))
524 AND ( (Recinfo.aei_information16 = X_Aei_Information16)
526 AND (X_Aei_Information16 IS NULL)))
527 AND ( (Recinfo.aei_information17 = X_Aei_Information17)
528 OR ( (Recinfo.aei_information17 IS NULL)
529 AND (X_Aei_Information17 IS NULL)))
530 AND ( (Recinfo.aei_information18 = X_Aei_Information18)
531 OR ( (Recinfo.aei_information18 IS NULL)
532 AND (X_Aei_Information18 IS NULL)))
533 AND ( (Recinfo.aei_information19 = X_Aei_Information19)
534 OR ( (Recinfo.aei_information19 IS NULL)
535 AND (X_Aei_Information19 IS NULL)))
536 AND ( (Recinfo.aei_information20 = X_Aei_Information20)
537 OR ( (Recinfo.aei_information20 IS NULL)
538 AND (X_Aei_Information20 IS NULL)))
539 ) then
540 return;
541 else
542 hr_utility.set_message(801, 'FORM_RECORD_CHANGED');
543 hr_utility.raise_error;
544 end if;
545 END Lock_Row;
546 --
547 ------------------------------- Update_Row -----------------------------
548 --
549 PROCEDURE Update_Row(X_Rowid VARCHAR2,
550 X_assignment_id NUMBER,
551 X_information_type VARCHAR2,
552 X_session_date DATE,
553 X_jurisdiction VARCHAR2,
554 X_Aei_Information1 VARCHAR2 default null,
555 X_Aei_Information2 VARCHAR2 default null,
556 X_Aei_Information3 VARCHAR2 default null,
557 X_Aei_Information4 VARCHAR2 default null,
558 X_Aei_Information5 VARCHAR2 default null,
559 X_Aei_Information6 VARCHAR2 default null,
560 X_Aei_Information7 VARCHAR2 default null,
561 X_Aei_Information8 VARCHAR2 default null,
562 X_Aei_Information9 VARCHAR2 default null,
563 X_Aei_Information10 VARCHAR2 default null,
564 X_Aei_Information11 VARCHAR2 default null,
565 X_Aei_Information12 VARCHAR2 default null,
566 X_Aei_Information13 VARCHAR2 default null,
567 X_Aei_Information14 VARCHAR2 default null,
568 X_Aei_Information15 VARCHAR2 default null,
569 X_Aei_Information16 VARCHAR2 default null,
570 X_Aei_Information17 VARCHAR2 default null,
571 X_Aei_Information18 VARCHAR2 default null,
572 X_Aei_Information19 VARCHAR2 default null,
573 X_Aei_Information20 VARCHAR2 default null
574 ) IS
575 X_User_Id NUMBER;
576 X_Login_Id NUMBER;
577 X_time varchar2(10);
578 x_other_sd VARCHAR2(20);
579 X_sd_rowid VARCHAR2(30);
580 --
581 CURSOR check_sd IS
582 SELECT rowid
583 FROM per_assignment_extra_info
584 WHERE assignment_id = X_Assignment_Id
585 AND X_Aei_Information9 IS NOT NULL
586 AND Information_type = 'LOCALITY'
587 AND aei_information9 = X_Aei_Information9
588 AND aei_information1 = X_aei_information1
589 AND rowid <> X_rowid;
590 --
591 BEGIN
592 --
593 X_User_Id := FND_PROFILE.Value('USER_ID');
594 X_Login_Id := FND_PROFILE.Value('LOGIN_ID');
595 --
596 UPDATE PER_ASSIGNMENT_EXTRA_INFO
597 SET
598 last_updated_by = X_User_Id,
599 last_update_login = X_Login_Id,
600 aei_information1 = X_Aei_Information1,
601 aei_information2 = X_Aei_Information2,
602 aei_information3 = X_Aei_Information3,
603 aei_information4 = X_Aei_Information4,
604 aei_information5 = X_Aei_Information5,
605 aei_information6 = X_Aei_Information6,
606 aei_information7 = X_Aei_Information7,
607 aei_information8 = X_Aei_Information8,
608 aei_information9 = X_Aei_Information9,
609 aei_information10 = X_Aei_Information10,
610 aei_information11 = X_Aei_Information11,
611 aei_information12 = X_Aei_Information12,
612 aei_information13 = X_Aei_Information13,
613 aei_information14 = X_Aei_Information14,
614 aei_information15 = X_Aei_Information15,
615 aei_information16 = X_Aei_Information16,
616 aei_information17 = X_Aei_Information17,
617 aei_information18 = X_Aei_Information18,
618 aei_information19 = X_Aei_Information19,
619 aei_information20 = X_Aei_Information20
620 WHERE rowid = X_rowid;
621 --
622 if (SQL%NOTFOUND) then
623 RAISE NO_DATA_FOUND;
624 else
625 --
629 --
626 If (X_information_type = 'STATE' or
627 X_information_type = 'LOCALITY')
628 then
630 pay_us_emp_tax_rules_pkg.create_vertex_entry(
631 P_mode => 'U',
632 P_assignment_id => X_assignment_id,
633 P_information_type => X_information_type,
634 P_session_date => X_session_date,
635 P_jurisdiction => X_jurisdiction,
636 P_time_in_locality => X_aei_information10,
637 P_remainder_percent => X_aei_information16);
638 --
639 -- Now update other school Record if ness
640 --
641 If X_information_type = 'LOCALITY' THEN
642 OPEN check_sd;
643 FETCH check_sd into X_sd_rowid;
644
645 if check_sd%FOUND THEN --Need to wipe the school district
646 Update_Attribute(p_rowid => x_sd_rowid,
647 p_attribute_type => 'SCHOOL',
648 p_new_value => NULL,
649 p_jurisdiction => NULL,
650 p_state_abbrev => NULL,
651 p_assignment_id => NULL);
652 end if; --wiping of school dst
653 IF (X_aei_information9 IS NOT NULL) THEN -- School district exists
654 -- Seed table for TSL (School if local)
655 PAY_ASG_GEO_PKG.create_asg_geo_row(P_assignment_id => X_assignment_id,
656 P_jurisdiction => substr(X_jurisdiction,1,2)||'-'||X_Aei_Information9,
657 P_tax_unit_id => NULL );
658 END IF; -- School district not null
659 --
660 CLOSE check_sd;
661
662
663 end if; --Locality
664 --
665 elsif X_information_type = 'FEDERAL' then
666 pay_us_emp_tax_rules_pkg.create_wc_ele_entry(
667 P_assignment_id => X_assignment_id,
668 P_session_date => X_session_date,
669 P_jurisdiction => X_AEI_INFORMATION18);
670 --
671 end if;
672 --
673 -- Now update other school Record if ness
674 --
675 end if;
676 --
677 END Update_Row;
678 --
679 ---------------------- Delete_Row --------------------------------------
680 --
681 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
682 BEGIN
683 DELETE FROM PER_ASSIGNMENT_EXTRA_INFO
684 WHERE rowid = X_Rowid;
685 --
686 if (SQL%NOTFOUND) then
687 RAISE NO_DATA_FOUND;
688 end if;
689 END Delete_Row;
690 --
691 ------------------------------- check_unique --------------------------
692 --
693 FUNCTION check_unique(X_assignment_id NUMBER default null,
694 X_information_type VARCHAR2 default null,
695 X_state_code VARCHAR2 default null,
696 X_locality_code VARCHAR2 default null)
697 RETURN NUMBER is
698 ret number :=0;
699 --
700 cursor csr_federal is
701 select 1 from PER_ASSIGNMENT_EXTRA_INFO
702 where assignment_id = X_assignment_id
703 and information_type = X_information_type;
704 --
705 cursor csr_state is
706 select 1 from PER_ASSIGNMENT_EXTRA_INFO
707 where assignment_id = X_assignment_id
708 and information_type = X_information_type
709 and Aei_Information1 = X_state_code;
710 --
711 cursor csr_local is
712 select 1 from PER_ASSIGNMENT_EXTRA_INFO
713 where assignment_id = X_assignment_id
714 and information_type = X_information_type
715 and Aei_Information1 = X_state_code
716 and Aei_Information2 = X_locality_code;
717 --
718 begin
719 hr_utility.set_location('pay_us_emp_tax_rules_pkg.check_unique',1);
720 if X_information_type = 'FEDERAL'
721 then
722 OPEN csr_federal;
723 fetch csr_federal into ret;
724 if csr_federal%FOUND then
725 RETURN 1;
726 else
727 RETURN 0;
728 end if;
729 CLOSE csr_federal;
730 elsif X_information_type = 'STATE'
731 then
732 hr_utility.set_location('pay_us_emp_tax_rules_pkg.check_unique',2);
733 OPEN csr_state;
734 fetch csr_state into ret;
735 if csr_state%FOUND then
736 hr_utility.set_message(801,'HR_7719_TAX_ONE_RULE_ONLY');
737 RETURN 1;
738 else
739 RETURN 0;
740 end if;
741 CLOSE csr_state;
742 elsif X_information_type = 'LOCALITY'
743 then
744 hr_utility.set_location('pay_us_emp_tax_rules_pkg.check_unique',3);
745 OPEN csr_local;
746 fetch csr_local into ret;
747 if csr_local%FOUND then
748 hr_utility.set_message(801,'HR_7720_TAX_ONE_RULE_ONLY');
749 RETURN 1;
750 else
751 RETURN 0;
752 end if;
753 hr_utility.set_location('pay_us_emp_tax_rules_pkg.check_unique',4);
754 CLOSE csr_local;
755 end if;
756 hr_utility.set_location('pay_us_emp_tax_rules_pkg.check_unique',5);
757 --
758 END check_unique;
759
760 --
761 ----------------------------- default_tax ----------------------------
762 ----------------------------- with commit ----------------------------
763
764 --
765 PROCEDURE default_tax( X_assignment_id IN NUMBER,
766 X_session_date IN DATE,
770 X_work_state IN OUT nocopy VARCHAR2,
767 X_business_group_id IN NUMBER,
768 X_resident_state IN OUT nocopy VARCHAR2,
769 X_res_state_code IN OUT nocopy VARCHAR2,
771 X_work_state_code IN OUT nocopy VARCHAR2,
772 X_resident_locality IN OUT nocopy VARCHAR2,
773 X_work_locality IN OUT nocopy VARCHAR2,
774 X_work_jurisdiction IN OUT nocopy VARCHAR2,
775 X_work_loc_name IN OUT nocopy VARCHAR2 ,
776 X_resident_loc_name IN OUT nocopy VARCHAR2 ,
777 X_default_or_get IN OUT nocopy VARCHAR2,
778 X_error IN OUT nocopy VARCHAR2)
779 IS
780 l_from_form VARCHAR2(50) := 'Tax_Form';
781
782 BEGIN
783
784 -- Call the below pacakge and commit
785 pay_us_emp_tax_rules_pkg.default_tax(
786 X_assignment_id => X_assignment_id,
787 X_session_date => X_session_date,
788 X_business_group_id => X_business_group_id,
789 X_resident_state => X_resident_state,
790 X_res_state_code => X_res_state_code,
791 X_work_state => X_work_state,
792 X_work_state_code => X_work_state_code,
793 X_resident_locality => X_resident_locality,
794 X_work_locality => X_work_locality,
795 X_work_jurisdiction => X_work_jurisdiction,
796 X_work_loc_name => X_work_loc_name,
797 X_resident_loc_name => X_resident_loc_name,
798 X_default_or_get => X_default_or_get,
799 X_error => X_error,
800 X_from_form => l_from_form);
801 --
802 COMMIT;
803 --
804 END default_tax;
805 --
806 ----------------------------- default_tax ----------------------------
807 ----------------------------- no commit ----------------------------
808 --
809 PROCEDURE default_tax( X_assignment_id IN NUMBER,
810 X_session_date IN DATE,
811 X_business_group_id IN NUMBER,
812 X_resident_state IN OUT nocopy VARCHAR2,
813 X_res_state_code IN OUT nocopy VARCHAR2,
814 X_work_state IN OUT nocopy VARCHAR2,
815 X_work_state_code IN OUT nocopy VARCHAR2,
816 X_resident_locality IN OUT nocopy VARCHAR2,
817 X_work_locality IN OUT nocopy VARCHAR2,
818 X_work_jurisdiction IN OUT nocopy VARCHAR2,
819 X_work_loc_name IN OUT nocopy VARCHAR2 ,
820 X_resident_loc_name IN OUT nocopy VARCHAR2 ,
821 X_default_or_get IN OUT nocopy VARCHAR2,
822 X_error IN OUT nocopy VARCHAR2,
823 X_from_form IN VARCHAR2)
824 IS
825 l_ret number := 0;
826 l_state VARCHAR2(2) := Null;
827 l_rowid VARCHAR2(30) := Null;
828 l_time_in_state VARCHAR2(3) := '0';
829 l_remainder VARCHAR2(3) := '0';
830 l_resident_county_name VARCHAR2(60);
831 l_work_county_name VARCHAR2(60); /* 391886 */
832 --
833 -- Cursor to check whether tax rules exist
834 --
835 CURSOR csr_check_federal is
836 select 1
837 from PER_ASSIGNMENT_EXTRA_INFO
838 where assignment_id = X_assignment_id
839 and INFORMATION_TYPE = 'FEDERAL';
840 --
841 -- Cursor to check state tax rules exists
842 --
843 CURSOR csr_check_state ( P_state varchar2 ) is
844 select 1
845 from PER_ASSIGNMENT_EXTRA_INFO
846 where assignment_id = X_assignment_id
847 and INFORMATION_TYPE = 'STATE'
848 and AEI_INFORMATION1 = P_state;
849 --
850 -- Cursor to check state tax rules exists with time in state=100
851 --
852 CURSOR csr_check_state_100 is
853 select ROWID, AEI_INFORMATION1
854 from PER_ASSIGNMENT_EXTRA_INFO
855 where assignment_id = X_assignment_id
856 and INFORMATION_TYPE = 'STATE'
857 and fnd_number.canonical_to_number(AEI_INFORMATION13) = 100;
858 --
859 -- Cursor to check Locality tax rules exists
860 --
861 CURSOR csr_check_local( P_state varchar2,
862 P_local varchar2) is
863 select 1
864 from PER_ASSIGNMENT_EXTRA_INFO
865 where assignment_id = X_assignment_id
866 and INFORMATION_TYPE = 'LOCALITY'
867 and AEI_INFORMATION1 = P_state
868 and AEI_INFORMATION2 = P_local;
869 --
870 --
871 PROCEDURE get_def_state_local(P_assignment_id IN NUMBER,
872 P_session_date IN DATE,
873 P_res_state OUT nocopy VARCHAR2,
874 P_res_state_code OUT nocopy VARCHAR2,
875 P_res_locality OUT nocopy VARCHAR2,
876 P_work_state OUT nocopy VARCHAR2,
880 P_work_loc_name OUT nocopy VARCHAR2,
877 P_work_state_code OUT nocopy VARCHAR2,
878 P_work_jurisdiction OUT nocopy VARCHAR2,
879 P_work_locality OUT nocopy VARCHAR2,
881 P_resident_loc_name OUT nocopy VARCHAR2,
882 P_work_county_name OUT nocopy VARCHAR2, /*391886*/
883 P_resident_county_name OUT nocopy VARCHAR2)
884 IS
885 --
886 -- declare local variables
887 --
888 l_zip_code VARCHAR2(30);
889 l_county VARCHAR2(120);
890 l_res_state_code VARCHAR2(2);
891 l_res_loc_name VARCHAR2(30);
892 l_work_state_code VARCHAR2(2);
893 l_work_loc_name VARCHAR2(30);
894 --
895 -- Cursor to get resident state
896 --
897 CURSOR csr_get_res_state is
898 select psr.name,
899 psr.state_code,
900 psr.state_code,
901 pa.town_or_city,
902 pa.town_or_city,
903 pa.region_1,
904 pa.postal_code
905 from PER_ASSIGNMENTS_F paf,
906 PER_ADDRESSES pa,
907 PAY_STATE_RULES psr
908 where paf.assignment_id = P_assignment_id
909 and P_session_date between paf.effective_start_date and
910 paf.effective_end_date
911 and paf.person_id = pa.person_id
912 and pa.primary_flag = 'Y'
913 and P_session_date between pa.date_from and
914 nvl(pa.date_to,P_session_date)
915 and psr.state_code = pa.region_2;
916 --
917 -- Cursor to get Work state
918 --
919 CURSOR csr_get_work_state is
920 select psr.name,
921 psr.state_code,
922 psr.state_code,
923 hl.region_1,
924 hl.postal_code,
925 psr.jurisdiction_code,
926 hl.town_or_city,
927 hl.town_or_city
928 from PER_ASSIGNMENTS_F paf,
929 HR_LOCATIONS hl,
930 PAY_STATE_RULES psr
931 where paf.assignment_id = P_assignment_id
932 and P_session_date between paf.effective_start_date and
933 paf.effective_end_date
934 and paf.location_id = hl.location_id
935 and psr.state_code = hl.region_2;
936
937 begin
938 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_state',1);
939 OPEN csr_get_res_state;
940 FETCH csr_get_res_state into P_res_state,
941 P_res_state_code,
942 l_res_state_code,
943 P_resident_loc_name,
944 l_res_loc_name,
945 l_county,
946 l_zip_code;
947 IF csr_get_res_state%NOTFOUND
948 THEN
949 P_res_state := null;
950 P_res_state_code := null;
951 P_res_locality := null;
952 ELSE
953 --
954 -- get resident locality i.e. geo code
955 --
956 p_resident_county_name := l_county;
957 --
958 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_state',2);
959 P_res_locality := hr_us_ff_udfs.addr_val (
960 p_state_abbrev => l_res_state_code,
961 p_county_name => l_county,
962 p_city_name => l_res_loc_name,
963 p_zip_code => l_zip_code);
964 END IF;
965 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_state',3);
966 CLOSE csr_get_res_state;
967 --
968 OPEN csr_get_work_state;
969 FETCH csr_get_work_state into P_work_state,
970 P_work_state_code,
971 l_work_state_code,
972 l_county,
973 l_zip_code,
974 P_work_jurisdiction,
975 P_work_loc_name,
976 l_work_loc_name;
977 IF csr_get_work_state%NOTFOUND
978 THEN
979 P_work_state := null;
980 P_work_state_code := null;
981 P_work_locality := null;
982 ELSE
983 --
984 -- get work locality i.e. geo code
985 --
986 p_work_county_name := l_county;
987 --
988
989 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_state',4);
990 P_work_locality := hr_us_ff_udfs.addr_val (
991 p_state_abbrev => l_work_state_code,
992 p_county_name => l_county,
993 p_city_name => l_work_loc_name,
994 p_zip_code => l_zip_code);
995 END IF;
996 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_state',5);
997 CLOSE csr_get_work_state;
998 --
999 END get_def_state_local;
1000 --
1001
1002
1003 FUNCTION insert_def_fed_rec(P_assignment_id NUMBER,
1004 p_session_date DATE,
1005 P_sui_state VARCHAR2)
1006 RETURN NUMBER IS
1007 --
1008 p_filing_status varchar2(2);
1009 P_eic_fstatus varchar2(2);
1010 P_temp varchar2(30);
1011 P_id number;
1012 --
1013 CURSOR csr_filing_status is
1014 select lookup_code
1015 from hr_lookups
1016 where lookup_type = 'US_FIT_FILING_STATUS'
1017 and upper(meaning) = 'SINGLE';
1018 --
1019 CURSOR csr_eic_fstatus is
1023 and upper(meaning) = 'NO EIC';
1020 select lookup_code
1021 from hr_lookups
1022 where lookup_type = 'US_EIC_FILING_STATUS'
1024 --
1025
1026 begin
1027 --
1028 OPEN csr_filing_status;
1029 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_feder',1);
1030 FETCH csr_filing_status into p_filing_status;
1031 IF csr_filing_status%NOTFOUND then
1032 hr_utility.set_message(801,'HR_6091_DEF_MISSING_LOOKUPS');
1033 hr_utility.set_message_token('LOOKUP_TYPE ','US_FIT_FILING_STATUS');
1034 hr_utility.raise_error;
1035 end if;
1036 CLOSE csr_filing_status;
1037 --
1038 --
1039 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_feder',1);
1040 OPEN csr_eic_fstatus;
1041 FETCH csr_eic_fstatus into P_eic_fstatus;
1042 IF csr_eic_fstatus%NOTFOUND then
1043 hr_utility.set_message(801,'HR_6091_DEF_MISSING_LOOKUPS');
1044 hr_utility.set_message_token('LOOKUP_TYPE ','US_EIC_FILING_STATUS');
1045 hr_utility.raise_error;
1046 end if;
1047 CLOSE csr_eic_fstatus;
1048 --
1049 -- Insert Federal Tax record
1050 --
1051 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_feder',4);
1052 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
1053 X_Rowid => P_temp,
1054 X_Assignment_Extra_Info_Id => P_id,
1055 X_Assignment_Id => P_assignment_id,
1056 X_Information_Type => 'FEDERAL',
1057 X_session_date => P_session_date,
1058 X_jurisdiction => null,
1059 X_Aei_Information_Category => 'FEDERAL',
1060 X_Aei_Information1 => '0',
1061 X_Aei_Information2 => '0',
1062 X_Aei_Information3 => Null,
1063 X_Aei_Information4 => P_filing_status,
1064 X_Aei_Information5 => '0',
1065 X_Aei_Information6 => 'N',
1066 X_Aei_Information7 => 'N',
1067 X_Aei_Information8 => 'N',
1068 X_Aei_Information9 => 'N',
1069 X_Aei_Information10 => 'N',
1070 X_Aei_Information11 => 'N',
1071 X_Aei_Information12 => P_eic_fstatus,
1072 X_Aei_Information13 => Null,
1073 X_Aei_Information14 => '0',
1074 X_Aei_Information15 => '0',
1075 X_Aei_Information16 => Null,
1076 X_Aei_Information17 => '0',/*316663 default supp override=0*/
1077 X_Aei_Information18 => P_sui_state,
1078 X_Aei_Information19 => Null,
1079 X_Aei_Information20 => Null);
1080 --
1081 hr_utility.set_location('pay_us_emp_tax_rules_pkg.get_def_feder',5);
1082 --
1083 RETURN P_id;
1084 --
1085 END insert_def_fed_rec;
1086 --
1087 -- Insert state record
1088 --
1089
1090 FUNCTION insert_def_state_rec(P_assignment_id NUMBER,
1091 P_state_code VARCHAR2,
1092 P_session_date DATE,
1093 P_time_in_state VARCHAR2,
1094 P_remainder VARCHAR2)
1095 RETURN NUMBER IS
1096 --
1097 P_temp varchar2(30);
1098 P_id number;
1099 P_jurisdiction varchar2(30) :=null;
1100 l_filing_status varchar2(30);
1101 l_def_pref varchar2(30);
1102 l_allowances number;
1103 --
1104 CURSOR csr_get_jurisdiction is
1105 select jurisdiction_code
1106 from pay_state_rules
1107 where state_code = P_state_code;
1108
1109 -- This cursor gets the filing status and exemptions from the federal record
1110 -- if needed
1111
1112 CURSOR csr_filing_status is
1113 select hl.lookup_code, peft.withholding_allowances -- Bug 3354046: Table pay_us_states is added to remove 2 MJC and FTS on table
1114 from hr_lookups hl, -- pay_state_rules(table in def. of view pay_emp_fed_tax_v1)
1115 pay_emp_fed_tax_v1 peft,
1116 pay_us_states pus
1117 where hl.lookup_type = 'US_FS_'||substr(p_jurisdiction,1,2)
1118 and upper(hl.meaning) = decode(
1119 upper(substr(peft.filing_status,1,7)),
1120 'MARRIED',
1121 'MARRIED',
1122 upper(peft.filing_status))
1123 and peft.assignment_id = p_assignment_id
1124 and pus.state_code = substr(peft.sui_jurisdiction_code,1,2)
1125 and pus.state_abbrev = peft.sui_state_code
1126 and pus.state_name = peft.sui_state_name;
1127 --
1128 CURSOR csr_fed_or_def is -- Bug 3354046: Index has been forced on table pay_state_rules to remove FTS and
1129 select /*+ index (sr PAY_STATE_RULES_PK) -- MJC.
1130 ordered */hoi.org_information12
1131 from per_assignments_f paf,
1132 hr_soft_coding_keyflex hsck,
1133 hr_organization_information hoi ,
1134 pay_state_rules sr
1135 where paf.assignment_id = p_assignment_id
1136 and SR.state_code = hoi.org_information1
1137 and hoi.organization_id = hsck.segment1
1138 and hoi.org_information_context = 'State Tax Rules'
1139 and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1140 and sr.jurisdiction_code = substr(p_jurisdiction,1,2)||'-000-0000';
1141
1142 begin
1146 FETCH csr_get_jurisdiction into P_jurisdiction;
1143 --
1144 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1);
1145 OPEN csr_get_jurisdiction;
1147 IF csr_get_jurisdiction%NOTFOUND then
1148 hr_utility.set_message(801,'HR_6091_DEF_MISSING_LOOKUPS');
1149 hr_utility.set_message_token('LOOKUP_TYPE ','pay_state_rules');
1150 hr_utility.raise_error;
1151 end if;
1152 CLOSE csr_get_jurisdiction;
1153 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1.3);
1154
1155 OPEN csr_fed_or_def;
1156 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1.4);
1157 FETCH csr_fed_or_def into l_def_pref;
1158 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1.5);
1159 IF csr_fed_or_def%NOTFOUND
1160 OR l_def_pref = 'SINGLE_ZERO'
1161 OR l_def_pref IS NULL then
1162 l_def_pref := 'SINGLE_ZERO';
1163 l_filing_status := '1';
1164 l_allowances := 0;
1165 end if;
1166 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1.6);
1167 CLOSE csr_fed_or_def;
1168
1169 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',1.7);
1170 IF l_def_pref = 'FED_DEF' then
1171 OPEN csr_filing_status;
1172 FETCH csr_filing_status into l_filing_status, l_allowances;
1173 IF csr_filing_status%NOTFOUND then
1174 l_filing_status := '1';
1175 l_allowances := 0;
1176 end if;
1177 CLOSE csr_filing_status;
1178 end if;
1179 --
1180 -- Insert State Tax record
1181 --
1182 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',2);
1183 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
1184 X_Rowid => P_temp,
1185 X_Assignment_Extra_Info_Id => P_id,
1186 X_Assignment_Id => P_assignment_id,
1187 X_Information_Type => 'STATE',
1188 X_session_date => P_session_date,
1189 X_jurisdiction => P_jurisdiction,
1190 X_Aei_Information_Category => 'STATE',
1191 X_Aei_Information1 => P_state_code,
1192 X_Aei_Information2 => l_filing_status,
1193 X_Aei_Information3 => 'N',
1194 X_Aei_Information4 => 'N',
1195 X_Aei_Information5 => 'N',
1196 X_Aei_Information6 => 'N',
1197 X_Aei_Information7 => 'Y',
1198 X_Aei_Information8 => '0',
1199 X_Aei_Information9 => '0',
1200 X_Aei_Information10 => fnd_number.number_to_canonical(l_allowances),
1201 X_Aei_Information11 => '0',
1202 X_Aei_Information12 => Null,
1203 X_Aei_Information13 => P_time_in_state,
1204 X_Aei_Information14 => '0',
1205 X_Aei_Information15 => '0',
1206 X_Aei_Information16 => P_remainder,
1207 X_Aei_Information17 => Null,
1208 X_Aei_Information18 => '0', /*316663 Supp Override rate =0*/
1209 X_Aei_Information19 => Null,
1210 X_Aei_Information20 => Null);
1211 --
1212 hr_utility.set_location('pay_us_emp_tax_rules_pkg.ins_def_state',3);
1213 --
1214 -- NOTE: do not call create_vertex_entry here since we NEVER want to create
1215 -- an entry for either the resident or work state since this will
1216 -- be covered when we default the associated city record for resident
1217 -- or work location.
1218 --
1219 RETURN P_id;
1220 --
1221 END insert_def_state_rec;
1222 --
1223 --
1224 FUNCTION insert_def_loc_rec(P_assignment_id NUMBER,
1225 P_state_code VARCHAR2,
1226 P_session_date DATE,
1227 P_locality_code VARCHAR2,
1228 P_locality_name VARCHAR2,
1229 P_locality_county VARCHAR2,
1230 P_time_in_local VARCHAR2,
1231 p_resident_flag VARCHAR2 DEFAULT 'N')
1232 RETURN NUMBER IS
1233 --
1234 l_filing_status varchar2(2);
1235 l_temp varchar2(30);
1236 l_id number;
1237 l_test number; /* 390941 */
1238 l_city_sd_code VARCHAR2(5) := NULL;
1239 l_county_sd_code VARCHAR2(5) := NULL;
1240 l_county_locality_code VARCHAR2(11);
1241 --
1242 CURSOR csr_county_exists (Jurisdiction varchar2) is /* 390941 */
1243 select 1
1244 from per_assignment_extra_info
1245 where assignment_id = P_assignment_id
1246 and aei_information2 = Jurisdiction;
1247
1248
1249 CURSOR csr_filing_status is
1250 select lookup_code
1251 from hr_lookups
1252 where lookup_type = 'US_LIT_FILING_STATUS'
1253 and upper(meaning) = 'SINGLE';
1254 --
1255 CURSOR csr_get_county_sd IS
1256 SELECT school_dst_code
1257 FROM pay_us_county_school_dsts
1258 WHERE STATE_CODE = fnd_number.canonical_to_number(substr(P_locality_code,1,2))
1259 AND COUNTY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,4,3))
1260 AND p_resident_flag = 'Y'
1261 ORDER BY fnd_number.canonical_to_number(school_dst_code);
1262 --
1263 CURSOR csr_get_city_sd IS
1264 SELECT school_dst_code
1265 FROM pay_us_city_school_dsts
1266 WHERE STATE_CODE = fnd_number.canonical_to_number(substr(P_locality_code,1,2))
1267 AND COUNTY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,4,3))
1271 --
1268 AND CITY_CODE = fnd_number.canonical_to_number(substr(P_locality_code,8,4))
1269 AND p_resident_flag = 'Y'
1270 ORDER BY fnd_number.canonical_to_number(school_dst_code);
1272 begin
1273 --
1274 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',1);
1275 OPEN csr_filing_status;
1276 FETCH csr_filing_status into l_filing_status;
1277 IF csr_filing_status%NOTFOUND then
1278 hr_utility.set_message(801,'HR_6091_DEF_MISSING_LOOKUPS');
1279 hr_utility.set_message_token('LOOKUP_TYPE ','US_LIT_FILING_STATUS');
1280 hr_utility.raise_error;
1281 end if;
1282 CLOSE csr_filing_status;
1283 --
1284 -- check if entering resident record which will require default
1285 -- city and county records with school districts
1286 -- get county name. city and county sd codes if applicable and insert
1287 -- county resident tax record.
1288 -- However, don't bother if locality being defaulted is for an unknown
1289 -- city (i.e. p_locality_code is a county code - addr_Val returns county
1290 -- geo code for unknown cities) because this will be a county tax record
1291 -- anyway.
1292 --
1293 /* bug391886 Now county record is constructed if this is a resident or
1294 work address*/
1295 /* IF ( (p_resident_flag = 'Y') OR
1296 (SUBSTR(p_locality_code, 8,4) = '0000') OR (1=1))
1297 THEN */
1298
1299 l_county_locality_code := (SUBSTR(P_locality_code,1,7)||'0000');
1300 --
1301 OPEN csr_county_exists(l_county_locality_code); /* 390941 */
1302 FETCH csr_county_exists into l_test; /* 390941 */
1303
1304 --
1305 -- get county school district /* Why? if it is going to be at
1306 -- the city level then there is no point. No longer defaulting.
1307 --
1308 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',2);
1309 --OPEN csr_get_county_sd;
1310 --FETCH csr_get_county_sd INTO l_county_sd_code;
1311 --CLOSE csr_get_county_sd;
1312 --
1313 IF csr_county_exists%NOTFOUND THEN /* 390941 */
1314
1315 --
1316 -- Insert Local County Tax record
1317 --
1318 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',3);
1319 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
1320 X_Rowid => l_temp,
1321 X_Assignment_Extra_Info_Id => l_id,
1322 X_Assignment_Id => P_assignment_id,
1323 X_Information_Type => 'LOCALITY',
1324 X_session_date => P_session_date,
1325 X_jurisdiction => l_county_locality_code,
1326 X_Aei_Information_Category => 'LOCALITY',
1327 X_Aei_Information1 => P_state_code,
1328 X_Aei_Information2 => l_county_locality_code,
1329 X_Aei_Information3 => l_filing_status,
1330 X_Aei_Information4 => '0',
1331 X_Aei_Information5 => '0',
1332 X_Aei_Information6 => '0',
1333 X_Aei_Information7 => 'N',
1334 X_Aei_Information8 => 'Y',
1335 X_Aei_Information9 => l_county_sd_code,
1336 X_Aei_Information10 => '0',
1337 X_Aei_Information11 => '0',
1338 X_Aei_Information12 => '0',
1339 X_Aei_Information13 => P_locality_county,
1340 X_Aei_Information14 => Null,
1341 X_Aei_Information15 => Null,
1342 X_Aei_Information16 => Null,
1343 X_Aei_Information17 => Null,
1344 X_Aei_Information18 => Null,
1345 X_Aei_Information19 => Null,
1346 X_Aei_Information20 => Null);
1347 --
1348 -- don't need assignment_extra_info_id for anything
1349 -- if do not set this to null then the local tax insert
1350 -- does not bother to get a new id - therefore giving
1351 -- duplicate key type error!
1352 --
1353 -- Also, do NOT need to insert VERTEX record for default
1354 -- county record - will insert VERTEX record for unknown city
1355 -- later
1356 --
1357 END IF; /* 390941 */
1358 close csr_county_exists; /* 390941 */
1359 l_id := NULL;
1360 --
1361 /*END IF; -- (p_resident_flag = 'Y') REMOVED FOR BUG 391886*/
1362 --
1363 --
1364 -- check that we are not defaulting for an unknown city. If we are then
1365 -- skip insert of employee tax record as it will have been entered already
1366 -- above as a county tax record and go striaght to inserting the VERTEX
1367 -- entry for the unknown city
1368 --
1369 IF (SUBSTR(p_locality_code, 8,4) <> '0000')
1370 THEN
1371 --
1372 -- get city school district
1373 --
1374 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',4);
1375 /* Commenting out the defaulting of school district to take care of
1376 Bug 643121 */
1377 -- OPEN csr_get_city_sd;
1378 -- FETCH csr_get_city_sd INTO l_city_sd_code;
1379 -- CLOSE csr_get_city_sd;
1380 --
1381 --
1382 -- Insert Local Tax record
1383 --
1384 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',5);
1385 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
1386 X_Rowid => l_temp,
1387 X_Assignment_Extra_Info_Id => l_id,
1388 X_Assignment_Id => P_assignment_id,
1389 X_Information_Type => 'LOCALITY',
1390 X_session_date => P_session_date,
1394 X_Aei_Information2 => P_locality_code,
1391 X_jurisdiction => P_locality_code,
1392 X_Aei_Information_Category => 'LOCALITY',
1393 X_Aei_Information1 => P_state_code,
1395 X_Aei_Information3 => l_filing_status,
1396 X_Aei_Information4 => '0',
1397 X_Aei_Information5 => '0',
1398 X_Aei_Information6 => '0',
1399 X_Aei_Information7 => 'N',
1400 X_Aei_Information8 => 'Y',
1401 X_Aei_Information9 => l_city_sd_code,
1402 X_Aei_Information10 => P_time_in_local,
1403 X_Aei_Information11 => '0',
1404 X_Aei_Information12 => '0',
1405 X_Aei_Information13 => P_locality_name,
1406 X_Aei_Information14 => Null,
1407 X_Aei_Information15 => Null,
1408 X_Aei_Information16 => Null,
1409 X_Aei_Information17 => Null,
1410 X_Aei_Information18 => Null,
1411 X_Aei_Information19 => Null,
1412 X_Aei_Information20 => Null);
1413 --
1414 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',6);
1415 --
1416 END IF; -- IF (SUBSTR(p_locality_code, 8,4) <> '0000')
1417 --
1418 pay_us_emp_tax_rules_pkg.create_vertex_entry(
1419 P_mode => 'I',
1420 P_assignment_id => P_assignment_id,
1421 P_information_type => 'LOCALITY',
1422 P_session_date => X_session_date,
1423 P_jurisdiction => P_locality_code,
1424 P_time_in_locality => P_time_in_local,
1425 P_remainder_percent => NULL);
1426 --
1427 hr_utility.set_location('pay_us_emp_tax_rules_pkg.insert_def_loc_rec',7);
1428 --
1429 RETURN l_id;
1430 --
1431 END insert_def_loc_rec;
1432 --
1433 --
1434 -- MAIN PROCEDURE
1435 --
1436 --
1437 BEGIN
1438 --
1439 -- Get State code and Locality from the Address
1440 --
1441 -- hr_utility.trace_on;
1442 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',1);
1443 X_error := 'Y';
1444 get_def_state_local(P_assignment_id => X_assignment_id,
1445 P_session_date => X_session_date,
1446 P_res_state => X_resident_state,
1447 P_res_state_code => X_res_state_code,
1448 P_res_locality => X_resident_locality,
1449 P_work_state => X_work_state,
1450 P_work_state_code => X_work_state_code,
1451 P_work_jurisdiction => X_work_jurisdiction,
1452 P_work_locality => X_work_locality,
1453 P_work_loc_name => X_work_loc_name,
1454 P_resident_loc_name => X_resident_loc_name,
1455 P_work_county_name => l_work_county_name,/*391886*/
1456 P_resident_county_name => l_resident_county_name);
1457 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',2);
1458 -- Check
1459 --
1460 --
1461 IF (X_res_state_code is not null AND
1462 X_work_state_code is not null)
1463 THEN
1464 --
1465 OPEN csr_check_federal;
1466 FETCH csr_check_federal into l_ret;
1467 --
1468 IF csr_check_federal%NOTFOUND
1469 THEN
1470 --
1471 -- Insert default values
1472 --
1473 -- Federal
1474 --
1475 l_ret := insert_def_fed_rec(X_assignment_id,
1476 X_session_date,
1477 X_work_jurisdiction);
1478 --
1479 -- State
1480 --
1481 IF X_work_locality is null
1482 THEN
1483 l_remainder := '100';
1484 ELSE
1485 l_remainder := '0';
1486 END IF;
1487 --
1488 -- if PWS and PRS are different insert one state record for each
1489 --
1490 IF X_res_state_code <> X_work_state_code
1491 THEN
1492 l_ret := insert_def_state_rec(X_assignment_id,
1493 X_work_state_code,
1494 X_session_date,
1495 '100',
1496 l_remainder);
1497 l_ret := insert_def_state_rec(X_assignment_id,
1498 X_res_state_code,
1499 X_session_date,
1500 '0',
1501 '0');
1502 ELSE
1503 --
1504 -- insert one state record
1505 --
1506 l_ret := insert_def_state_rec(X_assignment_id,
1507 X_res_state_code,
1508 X_session_date,
1509 '100',
1510 l_remainder);
1511 END IF;
1512 --
1513 -- Locality
1514 --
1515 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',3);
1516 --
1517 -- if PW locality and PR locality are different insert one
1518 -- locality record for each
1519 --
1520 IF (X_work_locality <> X_resident_locality)
1521 THEN
1522 l_ret := insert_def_loc_rec(X_assignment_id,
1526 X_work_loc_name,
1523 X_work_state_code,
1524 X_session_date,
1525 X_work_locality,
1527 l_work_county_name, /*391886*/
1528 100);
1529 l_ret := insert_def_loc_rec(X_assignment_id,
1530 X_res_state_code,
1531 X_session_date,
1532 X_resident_locality,
1533 X_resident_loc_name,
1534 l_resident_county_name,
1535 0,
1536 p_resident_flag => 'Y');
1537 ELSE
1538 l_ret := insert_def_loc_rec(X_assignment_id,
1539 X_res_state_code,
1540 X_session_date,
1541 X_resident_locality,
1542 X_resident_loc_name,
1543 l_resident_county_name,
1544 100,
1545 p_resident_flag => 'Y');
1546 END IF;
1547 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',4);
1548 --
1549 ELSE -- (IF csr_check_federal%NOTFOUND)
1550 --
1551 -- Check whether states exist
1552 --
1553 OPEN csr_check_state (X_work_state_code);
1554 FETCH csr_check_state INTO l_ret;
1555 IF csr_check_state%NOTFOUND
1556 THEN
1557 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',5);
1558 l_time_in_state := '0';
1559 l_ret := insert_def_state_rec(X_assignment_id,
1560 X_work_state_code,
1561 X_session_date,
1562 l_time_in_state,
1563 '0');
1564 END IF;
1565 CLOSE csr_check_state;
1566 --
1567 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',6);
1568 IF X_work_state_code <> X_res_state_code
1569 THEN
1570 OPEN csr_check_state (X_res_state_code);
1571 FETCH csr_check_state INTO l_ret;
1572 IF csr_check_state%NOTFOUND
1573 THEN
1574 l_ret := insert_def_state_rec(X_assignment_id,
1575 X_res_state_code,
1576 X_session_date,
1577 '0',
1578 '0');
1579 END IF;
1580 CLOSE csr_check_state;
1581 END IF;
1582 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',7);
1583 --
1584 IF X_resident_locality is not null
1585 THEN
1586 OPEN csr_check_local(X_res_state_code, X_resident_locality);
1587 FETCH csr_check_local into l_ret;
1588 IF csr_check_local%NOTFOUND
1589 THEN
1590 l_ret := insert_def_loc_rec(X_assignment_id,
1591 X_res_state_code,
1592 X_session_date,
1593 X_resident_locality,
1594 X_resident_loc_name,
1595 l_resident_county_name,
1596 '0',
1597 p_resident_flag => 'Y');
1598 END IF;
1599 CLOSE csr_check_local;
1600 END IF;
1601 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',8);
1602 --
1603 IF (X_work_locality is not null AND
1604 (X_work_locality <> X_resident_locality))
1605 THEN
1606 OPEN csr_check_local(X_work_state_code, X_work_locality);
1607 FETCH csr_check_local into l_ret;
1608 IF csr_check_local%NOTFOUND
1609 THEN
1610 l_ret := insert_def_loc_rec(X_assignment_id,
1611 X_work_state_code,
1612 X_session_date,
1613 X_work_locality,
1614 X_work_loc_name,
1615 l_work_county_name,/*391886*/
1616 '0');
1617 END IF;
1618 CLOSE csr_check_local;
1619 END IF;
1620 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',9);
1621 --
1622 END IF;
1623 CLOSE csr_check_federal;
1624 X_error := 'N';
1625 --
1626 -- since there are no erorrs then commit
1627 -- moving commit from form to server now that commits in plsql
1628 -- are kosha
1629 -- Moving because of 268389.
1630 -- COMMIT;
1631 --
1632 ELSE
1633 X_error := 'Y';
1634 END IF;
1635 hr_utility.set_location('pay_us_emp_tax_rules_pkg.main_proc',10);
1636 --
1637 END default_tax;
1638
1639 --
1640 --------------------------- create_vertex_entry --------------------
1641 PROCEDURE create_vertex_entry(
1642 P_mode Varchar2,
1643 P_assignment_id Number,
1644 P_information_type varchar2,
1645 P_session_date date,
1646 P_jurisdiction varchar2,
1647 p_time_in_locality varchar2,
1648 p_remainder_percent varchar2 ) IS
1649 --
1650 -- Declare table for input value Id and Screen Value
1651 inp_value_id_tbl hr_entry.number_table;
1655 --
1652 scr_valuetbl hr_entry.varchar2_table;
1653 --
1654 -- Local Varailbles
1656 P_element_type_id number :=0;
1657 P_element_link_id number :=0;
1658 P_inp_1 number :=0;
1659 P_inp_2 number :=0;
1660 P_inp_3 number :=0;
1661 P_iname varchar2(80) :=null;
1662 P_inp number :=0;
1663 P_element_entry_id number :=0;
1664 P_effective_stdt date :=null;
1665 P_effective_end_date date :=null;
1666 P_ins_or_upd varchar2(1) :='N';
1667 P_time varchar2(60);
1668
1669 -- Cursor to get the vertex element and link
1670 CURSOR csr_tax_element is
1671 select pet.ELEMENT_TYPE_ID,
1672 piv.INPUT_VALUE_ID,
1673 piv.NAME
1674 from PAY_ELEMENT_TYPES_F pet,
1675 PAY_INPUT_VALUES_F piv
1676 where PET.ELEMENT_NAME = 'VERTEX'
1677 and P_session_date between pet.effective_start_date and
1678 pet.effective_end_date
1679 and pet.element_type_id = piv.element_type_id
1680 and P_session_date between piv.effective_start_date and
1681 piv.effective_end_date ;
1682
1683 -- Cursor to find out whether a element entry exists for the jurisdiction
1684 -- The date effectiveness of this is being removed to
1685 -- prevent duplicate vertex element entries. Mar 31, 1997
1686 -- In addition the start date is pulled back if the session date
1687 -- is prior to the start date of the existing entry and entry values
1688
1689 CURSOR csr_chk_entry is
1690 select pee.ELEMENT_ENTRY_ID,
1691 pee.EFFECTIVE_START_DATE
1692 from PAY_ELEMENT_ENTRIES_F pee,
1693 PAY_ELEMENT_ENTRY_VALUES_F peev
1694 where pee.assignment_id = P_assignment_id
1695 --and P_session_date between pee.effective_start_date and
1696 -- pee.effective_end_date
1697 and pee.element_link_id = P_element_link_id
1698 and pee.element_entry_id = peev.element_entry_id
1699 --and P_session_date between peev.effective_start_date and
1700 -- peev.effective_end_date
1701 and peev.input_value_id = P_inp_2
1702 and peev.screen_entry_value = p_jurisdiction;
1703
1704 -- Get Effective_start_Date of assignment
1705 CURSOR csr_get_eff_Date is
1706 select effective_start_date
1707 from PER_ASSIGNMENTS_F
1708 where assignment_id = P_assignment_id
1709 and P_session_date between effective_start_date and
1710 effective_end_date;
1711 --
1712 -- MAIN Procedure
1713 --
1714 begin
1715 --
1716 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',1);
1717 --
1718 -- set p_time
1719 --
1720 IF (p_information_type = 'LOCALITY')
1721 THEN
1722 p_time := p_time_in_locality;
1723 ELSE
1724 p_time := p_remainder_percent;
1725 END IF;
1726 --
1727 OPEN csr_tax_element;
1728 LOOP
1729 FETCH csr_tax_element INTO p_element_type_id,
1730 P_inp, P_iname;
1731 EXIT WHEN csr_tax_element%NOTFOUND;
1732 --
1733 IF upper(P_iname) = 'JURISDICTION'
1734 THEN
1735 P_inp_2 := P_inp;
1736 ELSIF upper(P_iname) = 'PERCENTAGE'
1737 THEN
1738 P_inp_3 := P_inp;
1739 ELSIF upper(P_iname) = 'PAY VALUE'
1740 THEN
1741 P_inp_1 := P_inp;
1742 END IF;
1743 --
1744 END LOOP;
1745 --
1746 CLOSE csr_tax_element;
1747 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',4);
1748 --
1749 IF P_inp_2 is null OR P_inp_3 is null OR P_inp_1 is null OR
1750 P_inp_2 = 0 OR P_inp_3 = 0 OR P_inp_1 = 0
1751 THEN
1752 hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1753 hr_utility.set_message_token('1','VERTEX');
1754 hr_utility.raise_error;
1755 END IF;
1756 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',6);
1757 --
1758 -- Get element link
1759 P_element_link_id := hr_entry_api.get_link(
1760 P_assignment_id => P_assignment_id,
1761 P_element_type_id => P_element_type_id,
1762 P_session_date => P_session_date);
1763 --
1764 IF P_element_link_id is null OR P_element_link_id = 0
1765 THEN
1766 hr_utility.set_message(801, 'HR_13140_TAX_ELEMENT_ERROR');
1767 hr_utility.set_message_token('1','VERTEX');
1768 hr_utility.raise_error;
1769 END IF;
1770 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',7);
1771 --
1772 -- Store Input value ID into table
1773 --
1774 inp_value_id_tbl(1) := P_inp_1;
1775 inp_value_id_tbl(2) := P_inp_2;
1776 inp_value_id_tbl(3) := P_inp_3;
1777 scr_valuetbl(1) := null;
1778 scr_valuetbl(2) := P_jurisdiction;
1779 scr_valuetbl(3) := nvl(P_time,'0');
1780 --
1781 if P_mode = 'I' then
1782 --
1783 -- Get effective Start date of the assignment
1784 --
1785 OPEN csr_get_eff_date;
1786 FETCH csr_get_eff_date into P_effective_stdt;
1787 if csr_get_eff_date%NOTFOUND then
1788 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1789 hr_utility.set_message_token('PROCEDURE','ASG ELE ENTRY CREATION');
1793 CLOSE csr_get_eff_date;
1790 hr_utility.set_message_token('STEP','10');
1791 hr_utility.raise_error;
1792 end if;
1794 --
1795 OPEN csr_chk_entry;
1796 FETCH csr_chk_entry into p_element_entry_id, P_effective_stdt;
1797 IF csr_chk_entry%NOTFOUND THEN
1798 -- Insert the element entry
1799 --
1800 p_ins_or_upd := 'I';
1801 ELSE
1802 p_ins_or_upd := 'U';
1803 END IF;
1804 CLOSE csr_chk_entry;
1805
1806 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',8);
1807 elsif P_mode = 'U' OR p_ins_or_upd = 'U' then
1808 --
1809 -- Get Element entry id
1810 --
1811 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',10);
1812 OPEN csr_chk_entry;
1813 FETCH csr_chk_entry into p_element_entry_id, P_effective_stdt;
1814 if csr_chk_entry%NOTFOUND then
1815 --
1816 OPEN csr_get_eff_date;
1817 FETCH csr_get_eff_date into P_effective_stdt;
1818 if csr_get_eff_date%NOTFOUND then
1819 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1820 hr_utility.set_message_token('PROCEDURE','ASG ELE ENTRY CREATION');
1821 hr_utility.set_message_token('STEP','12');
1822 hr_utility.raise_error;
1823 end if;
1824 CLOSE csr_get_eff_date;
1825 p_ins_or_upd := 'I';
1826 else
1827 p_ins_or_upd := 'U';
1828 IF P_effective_stdt > p_session_date THEN
1829 P_effective_stdt := p_session_date;
1830 -- change effective start date to new date
1831 -- Changed March 31, 1997
1832 -- Entries
1833 UPDATE pay_element_entries_f
1834 SET effective_start_date = P_effective_stdt
1835 WHERE element_entry_id = p_element_entry_id;
1836 -- entry values Values
1837 UPDATE pay_element_entry_values_f
1838 SET effective_start_date = P_effective_stdt
1839 WHERE element_entry_id = p_element_entry_id;
1840 END IF;
1841 end if;
1842 --
1843 CLOSE csr_chk_entry;
1844 end if;
1845 --
1846 -- Check whether to insert or Update
1847 --
1848 if P_ins_or_upd = 'I' then
1849 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',14);
1850 hr_entry_api.insert_element_entry(
1851 p_effective_start_date => P_effective_stdt,
1852 p_effective_end_date => p_effective_end_date,
1853 p_element_entry_id => p_element_entry_id,
1854 p_assignment_id => P_assignment_id,
1855 p_element_link_id => P_element_link_id,
1856 p_creator_type => 'UT',
1857 p_entry_type => 'E',
1858 p_num_entry_values => 3,
1859 p_input_value_id_tbl => inp_value_id_tbl,
1860 p_entry_value_tbl => scr_valuetbl);
1861 elsif P_ins_or_upd = 'U' then
1862 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',16);
1863 hr_entry_api.update_element_entry(
1864 p_dt_update_mode => 'CORRECTION',
1865 p_session_date => P_effective_stdt,
1866 p_element_entry_id => p_element_entry_id,
1867 p_num_entry_values => 3,
1868 p_input_value_id_tbl => inp_value_id_tbl,
1869 p_entry_value_tbl => scr_valuetbl);
1870 end if;
1871 --
1872 END create_vertex_entry;
1873 --
1874 -- Create Workers Compensation Element entries.
1875 --------------------------- create_wc_ele_entry --------------------
1876 --
1877 PROCEDURE create_wc_ele_entry(
1878 P_assignment_id Number,
1879 P_session_date date,
1880 P_jurisdiction varchar2) IS
1881
1882 -- Declare table for input value Id and Screen Value
1883 inp_value_id_tbl hr_entry.number_table;
1884 scr_valuetbl hr_entry.varchar2_table;
1885
1886 -- Local Varailbles
1887 P_element_type_id number :=0;
1888 P_element_link_id number :=0;
1889 P_inp_1 number :=0;
1890 P_inp_2 number :=0;
1891 P_iname varchar2(80) :=null;
1892 P_inp number :=0;
1893 P_element_entry_id number :=0;
1894 P_effective_stdt date :=null;
1895 P_effective_end_date date :=null;
1896 p_ins_or_upd varchar2(1) :=null;
1897
1898 -- Cursor to get the Workers Compensation element and link
1899 CURSOR csr_wc_element is
1900 select pet.ELEMENT_TYPE_ID, -- Bug 3354046: Upper clause is removed around pet.element_name and WORKERS COMPENSATION
1901 piv.INPUT_VALUE_ID, -- changed to Workers Compensation to remove FTS on pay_element_types_f.
1902 piv.NAME
1903 from PAY_ELEMENT_TYPES_F pet,
1904 PAY_INPUT_VALUES_F piv
1905 where PET.ELEMENT_NAME = 'Workers Compensation'
1906 and P_session_date between pet.effective_start_date and
1907 pet.effective_end_date
1908 and pet.element_type_id = piv.element_type_id
1909 and P_session_date between piv.effective_start_date and
1910 piv.effective_end_date ;
1911
1912 -- Cursor to find out whether a element entry exists for the jurisdiction
1913
1914 CURSOR csr_chk_entry is
1915 select pee.ELEMENT_ENTRY_ID,
1916 pee.EFFECTIVE_START_DATE
1917 from PAY_ELEMENT_ENTRIES_F pee
1921 and pee.element_link_id = P_element_link_id;
1918 where pee.assignment_id = P_assignment_id
1919 and P_session_date between pee.effective_start_date and
1920 pee.effective_end_date
1922
1923 -- Get Effective_start_Date of assignment
1924 CURSOR csr_get_eff_Date is
1925 select effective_start_date
1926 from PER_ASSIGNMENTS_F
1927 where assignment_id = P_assignment_id
1928 and P_session_date between effective_start_date and
1929 effective_end_date;
1930 --
1931 -- MAIN Procedure
1932 --
1933 begin
1934 --
1935 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.WC_ele_ent',1);
1936 OPEN csr_wc_element;
1937 LOOP
1938 FETCH csr_wc_element INTO p_element_type_id,
1939 P_inp, P_iname;
1940 EXIT WHEN csr_wc_element%NOTFOUND;
1941 IF upper(P_iname) = 'JURISDICTION'
1942 THEN
1943 P_inp_2 := P_inp;
1944 ELSIF upper(P_iname) = 'PAY VALUE'
1945 THEN
1946 P_inp_1 := P_inp;
1947 end if;
1948 --
1949 END LOOP;
1950 --
1951 CLOSE csr_wc_element;
1952 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.WC_Ele_entry',4);
1953 --
1954 IF P_inp_1 = 0 OR P_inp_2 = 0 OR P_inp_2 is null OR P_inp_1 is null
1955 THEN
1956 hr_utility.set_message(801, 'HR_7713_TAX_ELEMENT_ERROR');
1957 hr_utility.raise_error;
1958 END IF;
1959 --
1960 -- Get element link
1961 --
1962 P_element_link_id := hr_entry_api.get_link(
1963 P_assignment_id => P_assignment_id,
1964 P_element_type_id => P_element_type_id,
1965 P_session_date => P_session_date);
1966 --
1967 IF P_element_link_id is null OR P_element_link_id = 0
1968 THEN
1969 hr_utility.set_message(801, 'HR_7713_TAX_ELEMENT_ERROR');
1970 hr_utility.raise_error;
1971 END IF;
1972 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.Ele_entry',5);
1973 --
1974 -- Store Input value ID into table
1975 --
1976 inp_value_id_tbl(1) := P_inp_1;
1977 inp_value_id_tbl(2) := P_inp_2;
1978 scr_valuetbl(1) := null;
1979 scr_valuetbl(2) := P_jurisdiction;
1980 --
1981 --
1982 -- Get Element entry id
1983 --
1984 OPEN csr_chk_entry;
1985 FETCH csr_chk_entry into p_element_entry_id, P_effective_stdt;
1986 if csr_chk_entry%NOTFOUND then
1987 p_ins_or_upd := 'I';
1988 else
1989 p_ins_or_upd := 'U';
1990 end if;
1991 --
1992 CLOSE csr_chk_entry;
1993 --
1994 -- Get effective Start date of the assignment
1995 --
1996 if P_effective_stdt is null OR p_ins_or_upd = 'I' then
1997 OPEN csr_get_eff_date;
1998 FETCH csr_get_eff_date into P_effective_stdt;
1999 if csr_get_eff_date%NOTFOUND then
2000 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2001 hr_utility.set_message_token('PROCEDURE','ASG WC ELE ENTRY CREATION');
2002 hr_utility.set_message_token('STEP','10');
2003 hr_utility.raise_error;
2004 end if;
2005 CLOSE csr_get_eff_date;
2006 end if;
2007 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.WC_Ele_entry',8);
2008 --
2009 --
2010 -- Check whether to insert or Update
2011 --
2012 if P_ins_or_upd = 'I' then
2013 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.WC_Ele_entry',14);
2014 hr_entry_api.insert_element_entry(
2015 p_effective_start_date => P_effective_stdt,
2016 p_effective_end_date => p_effective_end_date,
2017 p_element_entry_id => p_element_entry_id,
2018 p_assignment_id => P_assignment_id,
2019 p_element_link_id => P_element_link_id,
2020 p_creator_type => 'UT',
2021 p_entry_type => 'E',
2022 p_num_entry_values => 2,
2023 p_input_value_id_tbl => inp_value_id_tbl,
2024 p_entry_value_tbl => scr_valuetbl);
2025 elsif P_ins_or_upd = 'U' then
2026 hr_utility.set_location('PAY_US_EMP_TAX_RULES_PKG.WC_Ele_entry',16);
2027 hr_entry_api.update_element_entry(
2028 p_dt_update_mode => 'CORRECTION',
2029 p_session_date => P_effective_stdt,
2030 p_element_entry_id => p_element_entry_id,
2031 p_num_entry_values => 2,
2032 p_input_value_id_tbl => inp_value_id_tbl,
2033 p_entry_value_tbl => scr_valuetbl);
2034 end if;
2035 --
2036 END create_wc_ele_entry;
2037 --
2038 PROCEDURE on_insert( p_rowid IN OUT nocopy VARCHAR2,
2039 p_Assignment_Extra_Info_Id IN OUT nocopy NUMBER,
2040 p_Assignment_Id NUMBER,
2041 p_Information_Type VARCHAR2,
2042 p_session_date DATE,
2043 p_jurisdiction VARCHAR2,
2044 p_Aei_Information_Category VARCHAR2,
2045 p_Aei_Information1 VARCHAR2,
2046 p_Aei_Information2 VARCHAR2,
2047 p_Aei_Information3 VARCHAR2,
2048 p_Aei_Information4 VARCHAR2,
2049 p_Aei_Information5 VARCHAR2,
2050 p_Aei_Information6 VARCHAR2,
2054 p_Aei_Information10 VARCHAR2,
2051 p_Aei_Information7 VARCHAR2,
2052 p_Aei_Information8 VARCHAR2,
2053 p_Aei_Information9 VARCHAR2,
2055 p_Aei_Information11 VARCHAR2,
2056 p_Aei_Information12 VARCHAR2,
2057 p_Aei_Information13 VARCHAR2,
2058 p_Aei_Information14 VARCHAR2,
2059 p_Aei_Information15 VARCHAR2,
2060 p_Aei_Information16 VARCHAR2,
2061 p_Aei_Information17 VARCHAR2,
2062 p_Aei_Information18 VARCHAR2,
2063 p_Aei_Information19 VARCHAR2,
2064 p_Aei_Information20 VARCHAR2
2065 ) IS
2066 BEGIN
2067 --
2068 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
2069 X_Rowid => p_Rowid ,
2070 X_Assignment_Extra_Info_Id => p_Assignment_Extra_Info_Id,
2071 X_Assignment_Id => p_Assignment_Id ,
2072 X_Information_Type => p_Information_Type ,
2073 X_session_date => p_session_date ,
2074 X_jurisdiction => p_jurisdiction ,
2075 X_Aei_Information_Category => p_Aei_Information_Category,
2076 X_Aei_Information1 => p_Aei_Information1 ,
2077 X_Aei_Information2 => p_Aei_Information2 ,
2078 X_Aei_Information3 => p_Aei_Information3 ,
2079 X_Aei_Information4 => p_Aei_Information4 ,
2080 X_Aei_Information5 => p_Aei_Information5 ,
2081 X_Aei_Information6 => p_Aei_Information6 ,
2082 X_Aei_Information7 => p_Aei_Information7 ,
2083 X_Aei_Information8 => p_Aei_Information8 ,
2084 X_Aei_Information9 => p_Aei_Information9 ,
2085 X_Aei_Information10 => p_Aei_Information10 ,
2086 X_Aei_Information11 => p_Aei_Information11 ,
2087 X_Aei_Information12 => p_Aei_Information12 ,
2088 X_Aei_Information13 => p_Aei_Information13 ,
2089 X_Aei_Information14 => p_Aei_Information14 ,
2090 X_Aei_Information15 => p_Aei_Information15 ,
2091 X_Aei_Information16 => p_Aei_Information16 ,
2092 X_Aei_Information17 => p_Aei_Information17 ,
2093 X_Aei_Information18 => p_Aei_Information18 ,
2094 X_Aei_Information19 => p_Aei_Information19 ,
2095 X_Aei_Information20 => p_Aei_Information20);
2096 --
2097 hr_utility.set_location('pay_us_emp_tax_rules_pkg.on_insert',11);
2098 pay_us_emp_tax_rules_pkg.create_vertex_entry(
2099 P_mode => 'I',
2100 P_assignment_id => p_assignment_id,
2101 P_information_type => p_information_type,
2102 P_session_date => p_session_date,
2103 P_jurisdiction => p_jurisdiction,
2104 P_time_in_locality => p_aei_information10,
2105 P_remainder_percent => p_aei_information16);
2106 --
2107 END on_insert;
2108 --
2109 ------------------------------Insert County Record-----------------------------
2110 /* This function inserts a county record if a city record is inserted from the form */
2111
2112 Procedure create_county_record( P_Jurisdiction varchar2,
2113 P_assignment_id number,
2114 P_filing_status varchar2,
2115 P_session_date date
2116 )
2117 is
2118
2119 l_assignment_id number;
2120 l_session_date date;
2121 l_county_locality_code varchar2(30);
2122 l_filing_status varchar2(30):= 'SINGLE';
2123 l_county_sd_code varchar2(30):= NULL;
2124 l_state_code varchar2(30):= 'U';
2125 l_locality_county varchar2(30):= 'UNKNOWN';
2126 l_locality_code varchar2(30);
2127 l_temp varchar2(30);
2128 l_message varchar2(30);
2129 l_id NUMBER := NULL;
2130 l_count NUMBER := 0;
2131
2132 /* Cursor to get the the county School district */
2133 CURSOR csr_get_county_sd(Jurisdiction varchar2) IS
2134 SELECT school_dst_code
2135 FROM pay_us_county_school_dsts
2136 WHERE STATE_CODE = fnd_number.canonical_to_number(substr(Jurisdiction,1,2))
2137 AND COUNTY_CODE = fnd_number.canonical_to_number(substr(Jurisdiction,4,3))
2138 ORDER BY TO_NUMBER(school_dst_code);
2139
2140 /* Check if county record already exists */
2141 CURSOR csr_county_for_city is
2142 select 1
2143 from per_assignment_extra_info
2144 where assignment_id = P_assignment_id
2145 and aei_information2 = substr(P_jurisdiction,1,6)||'-0000';
2146
2147 /* Get state abbreviation */
2148 CURSOR csr_state_abbrev is
2149 select state_abbrev
2150 from pay_us_states
2151 where state_code = fnd_number.canonical_to_number(substr(P_jurisdiction,1,2));
2152
2153 /* Gets the county name */
2154
2155 CURSOR csr_county_name is
2156 select county_name
2160
2157 from pay_us_counties
2158 where fnd_number.canonical_to_number(substr(P_jurisdiction, 1,2)) = state_code
2159 and fnd_number.canonical_to_number(substr(P_jurisdiction, 4,3)) = county_code;
2161
2162 BEGIN /* create_county_record */
2163
2164 OPEN csr_county_for_city;
2165 FETCH csr_county_for_city INTO l_temp;
2166
2167 /* Check if county record already exists, if not then create one */
2168
2169 IF csr_county_for_city%notfound THEN /* insert county record for new city record */
2170
2171 l_assignment_id := p_assignment_id;
2172 l_session_date := p_session_date;
2173 l_county_locality_code := substr(p_jurisdiction,1,6)||'-0000';
2174 l_filing_status := p_filing_status;
2175
2176
2177 /* Must get school district code */
2178 /* Commenting out for Bug 643121 */
2179 -- OPEN csr_get_county_sd(l_locality_code);
2180 -- FETCH csr_get_county_sd INTO l_county_sd_code;
2181 -- CLOSE csr_get_county_sd;
2182
2183 /* get the State Abbreviation */
2184 OPEN csr_state_abbrev;
2185 FETCH csr_state_abbrev INTO l_state_code;
2186 CLOSE csr_state_abbrev;
2187
2188 /* Get county name */
2189 OPEN csr_county_name;
2190 FETCH csr_county_name INTO l_locality_county;
2191 CLOSE csr_county_name;
2192
2193 /*Insert county record */
2194 PAY_US_EMP_TAX_RULES_PKG.Insert_Row(
2195 X_Rowid => l_temp,
2196 X_Assignment_Extra_Info_Id => l_id,
2197 X_Assignment_Id => l_assignment_id,
2198 X_Information_Type => 'LOCALITY',
2199 X_session_date => l_session_date,
2200 X_jurisdiction => l_county_locality_code,
2201 X_Aei_Information_Category => 'LOCALITY',
2202 X_Aei_Information1 => l_state_code,
2203 X_Aei_Information2 => l_county_locality_code,
2204 X_Aei_Information3 => l_filing_status,
2205 X_Aei_Information4 => '0',
2206 X_Aei_Information5 => '0',
2207 X_Aei_Information6 => '0',
2208 X_Aei_Information7 => 'N',
2209 X_Aei_Information8 => 'Y',
2210 X_Aei_Information9 => l_county_sd_code,
2211 X_Aei_Information10 => '0',
2212 X_Aei_Information11 => '0',
2213 X_Aei_Information12 => '0',
2214 X_Aei_Information13 => l_locality_county,
2215 X_Aei_Information14 => Null,
2216 X_Aei_Information15 => Null,
2217 X_Aei_Information16 => Null,
2218 X_Aei_Information17 => Null,
2219 X_Aei_Information18 => Null,
2220 X_Aei_Information19 => Null,
2221 X_Aei_Information20 => Null);
2222
2223 l_id := NULL;
2224
2225 END IF; /* County record already exists */
2226
2227
2228
2229 close csr_county_for_city;
2230
2231 END; /*create_county_record*/
2232
2233 ------------------------------Update Attribute-----------------------------
2234 /* This procedure is used to update single attributes within tax records.*/
2235 /* This was created because often times attributes need to be updated on */
2236 /* records other then the present record on the form. */
2237 /* Attributes to be supported: Percent time, School district code */
2238 ---------------------------------------------------------------------------
2239
2240 Procedure Update_Attribute( p_rowid VARCHAR2,
2241 p_attribute_type VARCHAR2,
2242 p_new_value VARCHAR2,
2243 p_jurisdiction VARCHAR2,
2244 p_state_abbrev VARCHAR2,
2245 p_assignment_id NUMBER)
2246 IS
2247
2248 BEGIN
2249 --
2250 hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute '||p_assignment_id||' '||p_jurisdiction,0);
2251 IF p_attribute_type = 'SCHOOL' THEN
2252 UPDATE per_assignment_extra_info
2253 SET aei_information9 = p_new_value
2254 where rowid = p_rowid;
2255 hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute',1);
2256 --
2257 ELSIF p_attribute_type = 'PERCENT TIME' THEN
2258 /* for Locality */
2259 UPDATE per_assignment_extra_info
2260 SET aei_information10 = p_new_value
2261 WHERE information_type = 'LOCALITY'
2262 AND aei_information2 = p_jurisdiction
2263 AND assignment_id = p_assignment_id;
2264 --
2265 /* for State */
2266 UPDATE per_assignment_extra_info
2267 SET aei_information13 = p_new_value
2268 WHERE information_type = 'STATE'
2269 AND aei_information1 = p_state_abbrev
2270 AND assignment_id = p_assignment_id;
2271 hr_utility.set_location('pay_us_emp_tax_rules_pkg.update_attribute',2);
2272 --
2273 /* for Federal update the SUI state */
2274 UPDATE per_assignment_extra_info
2275 SET aei_information18 = substr(p_jurisdiction,1,2)||'-000-0000'
2276 WHERE information_type = 'FEDERAL'
2277 AND assignment_id = p_assignment_id;
2278 --
2279 END IF;
2280 --
2281 --
2282 END; /* Update_attribute */
2283 --
2284 --
2285 -- This procedure is for the defaulting of tax records from forms
2286 -- other than the W4(PAYEETAX) form
2287 -- It has more detailed error messages and effects time in state.
2288 --
2289 PROCEDURE default_tax_with_validation(p_assignment_id NUMBER,
2293 p_return_code OUT nocopy VARCHAR2,
2290 p_person_id NUMBER,
2291 p_date DATE,
2292 p_business_group_id NUMBER,
2294 p_from_form VARCHAR2,
2295 p_percent_time NUMBER)
2296 IS
2297 --
2298 l_return_code VARCHAR2(100) := 'Continue';
2299 l_verified NUMBER;
2300 l_time NUMBER;
2301 l_assignment_id NUMBER;
2302 l_exists VARCHAR2(100);
2303 l_res_state VARCHAR2(100);
2304 l_res_state_code VARCHAR2(100);
2305 l_res_locality VARCHAR2(100);
2306 l_work_state VARCHAR2(100);
2307 l_work_state_code VARCHAR2(100);
2308 l_work_jurisdiction VARCHAR2(100);
2309 l_work_locality VARCHAR2(100);
2310 l_work_loc_name VARCHAR2(100);
2311 l_resident_loc_name VARCHAR2(100);
2312 l_work_county_name VARCHAR2(100);
2313 l_resident_county_name VARCHAR2(100);
2314 l_d_or_g VARCHAR2(100) := NULL;
2315 l_error VARCHAR2(100) := NULL;
2316 l_county VARCHAR2(100);
2317 l_zip_code VARCHAR2(100);
2318 --
2319 -- Checks for qaulifying conditions for the assignment to be
2320 -- defaulted.
2321 --
2322 CURSOR csr_qualify_info(per_id NUMBER) IS
2323 SELECT assignment_id
2324 FROM per_assignments_f
2325 WHERE person_id = per_id
2326 AND assignment_type = 'E'
2327 AND location_id IS NOT NULL
2328 AND payroll_id IS NOT NULL
2329 AND pay_basis_id IS NOT NULL
2330 AND primary_flag = 'Y'
2331 AND p_date between effective_start_date
2332 and effective_end_date;
2333 --
2334 --Gets the Primary assignment if called from
2335 --a form other than the assignment form.
2336 --
2337 CURSOR csr_person_info(per_id NUMBER) IS
2338 SELECT paf.assignment_id
2339 FROM per_assignments_f paf,
2340 per_people_f ppf,
2341 per_addresses pa
2342 WHERE paf.person_id = ppf.person_id
2343 AND paf.assignment_type = 'E'
2344 AND ppf.person_id = pa.person_id
2345 AND pa.primary_flag = 'Y'
2346 AND paf.location_id IS NOT NULL
2347 AND paf.payroll_id IS NOT NULL
2348 AND paf.person_id = per_id;
2349 --
2350 -- Gets Work Information
2351 --
2352 CURSOR csr_get_work is
2353 select
2354 psr.state_code,
2355 hl.region_1,
2356 hl.postal_code,
2357 hl.town_or_city
2358 from PER_ASSIGNMENTS_F paf,
2359 HR_LOCATIONS hl,
2360 PAY_STATE_RULES psr
2361 where paf.assignment_id = P_assignment_id
2362 and P_date between paf.effective_start_date and
2363 paf.effective_end_date
2364 and paf.location_id = hl.location_id
2365 and psr.state_code = hl.region_2;
2366 --
2367 CURSOR csr_chk_percent_state IS
2368 SELECT sum(time_in_state)
2369 FROM pay_emp_state_tax_v1
2370 WHERE assignment_id = p_assignment_id;
2371 --
2372 CURSOR csr_check_aei_exists(p_work_jur VARCHAR2) IS
2373 Select jurisdiction_code
2374 From pay_emp_local_tax_v1
2375 where assignment_id = p_assignment_id
2376 and jurisdiction_code =p_work_jur;
2377
2378 --
2379 BEGIN /* BEGIN DEFAULT TAX WITH VALIDATION */
2380 --
2381 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',0);
2382 IF p_from_form = 'Address' THEN
2383 --
2384 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',1);
2385 --
2386 -- Check for primary assignment
2387 --
2388 OPEN csr_qualify_info(p_person_id);
2389 FETCH csr_qualify_info INTO l_assignment_id;
2390
2391 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',1.5);
2392 -- If one doesn't exist, or person is not an employee
2393 -- then quit and return to address form(No message)
2394 IF csr_qualify_info%NOTFOUND THEN
2395 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',2);
2396 l_return_code := 'No Assignment';
2397 END IF;
2398 --
2399 CLOSE csr_qualify_info;
2400 END IF; /* End of address form specific */
2401 --
2402 IF p_from_form = 'Assignment' OR l_return_code = 'Continue' THEN
2403 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',3);
2404 -- Now check for all information: Payroll, home address
2405 OPEN csr_person_info(p_person_id);
2406 FETCH csr_person_info INTO l_verified;
2407 --
2408 IF p_assignment_id IS NOT NULL THEN
2409 l_assignment_id := p_assignment_id;
2410 END IF;
2411 -- If one or more of these things does not exist then do not create
2412 -- tax records
2413 IF csr_person_info%NOTFOUND THEN
2414 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',4);
2415 l_return_code := 'Incomplete';
2416 END IF;
2417 CLOSE csr_person_info;
2418 --
2419 END IF;
2420 --
2421 IF l_return_code = 'Continue' THEN /* NOW default this */
2422 pay_us_emp_tax_rules_pkg.default_tax(
2423 X_assignment_id => l_assignment_id,
2424 X_session_date => p_date,
2425 X_business_group_id => p_business_group_id,
2426 X_resident_state => l_res_state,
2430 X_resident_locality => l_res_locality,
2427 X_res_state_code => l_res_state_code,
2428 X_work_state => l_work_state,
2429 X_work_state_code => l_work_state_code,
2431 X_work_locality => l_work_locality,
2432 X_work_jurisdiction => l_work_jurisdiction,
2433 X_work_loc_name => l_work_loc_name,
2434 X_resident_loc_name => l_resident_loc_name,
2435 X_default_or_get => l_d_or_g,
2436 X_error => l_error,
2437 X_from_form => p_from_form );
2438 --
2439 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',5);
2440 --
2441 -- Check for unvalidated Locations
2442 --
2443 IF l_res_locality is null then
2444 hr_utility.set_message(801,'HR_7556_TAY_NO_RES_ADDRESS');
2445 hr_utility.raise_error;
2446 END IF;
2447
2448 IF l_work_state IS NULL OR l_error = 'Y' THEN
2449 hr_utility.set_message(801, 'HR_7557_TAY_NO_WOK_ADDRESS');
2450 hr_utility.raise_error;
2451 END IF;
2452 --
2453 -- Now if calling form was the assignment then need to set the
2454 -- work state and locality to 100%
2455 IF p_from_form = 'Assignment' THEN
2456 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default',6);
2457 -- First check that time in state is less set to zero
2458 --
2459 OPEN csr_chk_percent_state;
2460 FETCH csr_chk_percent_state INTO l_time;
2461 --
2462 IF l_time = 0 THEN
2463 --Update the tax record for state and locality --
2464 --Need to get Work Geocode--
2465 OPEN csr_get_work;
2466 FETCH csr_get_work into l_work_state_code,
2467 l_county,
2468 l_zip_code,
2469 l_work_loc_name;
2470
2471 CLOSE csr_get_work;
2472 --
2473 l_work_jurisdiction := hr_us_ff_udfs.addr_val (
2474 p_state_abbrev => l_work_state_code,
2475 p_county_name => l_county,
2476 p_city_name => l_work_loc_name,
2477 p_zip_code => l_zip_code);
2478 --
2479 OPEN csr_check_aei_exists(l_work_jurisdiction);
2480 FETCH csr_check_aei_exists INTO l_exists;
2481 IF csr_check_aei_exists%FOUND THEN
2482 update_attribute( p_rowid => NULL,
2483 p_attribute_type => 'PERCENT TIME',
2484 p_new_value => '100',
2485 p_jurisdiction => l_work_jurisdiction,
2486 p_state_abbrev => l_work_state_code,
2487 p_assignment_id => l_assignment_id);
2488 --
2489 hr_utility.set_location('pay_us_emp_tax_rules_pkg.validate_default '||l_work_jurisdiction,7);
2490
2491 --
2492 -- Update the vertex element entry
2493 --
2494 pay_us_emp_tax_rules_pkg.create_vertex_entry(
2495 P_mode => 'U',
2496 P_assignment_id => l_assignment_id,
2497 P_information_type => 'LOCALITY',
2498 P_session_date => p_date,
2499 P_jurisdiction => l_work_jurisdiction,
2500 P_time_in_locality => '100',
2501 P_remainder_percent => NULL);
2502 --
2503 -- Update the Workers Compenstion Element Entry
2504 --
2505 pay_us_emp_tax_rules_pkg.create_wc_ele_entry(
2506 P_assignment_id => l_assignment_id,
2507 P_session_date => p_date,
2508 P_jurisdiction => substr(l_work_jurisdiction,1,2)||'-000-0000');
2509 ELSE /* NO tax records exists */
2510 /* Note: This is the same error raised */
2511 /* if there is not location address */
2512 hr_utility.set_message(801, 'HR_7557_TAY_NO_WOK_ADDRESS');
2513 hr_utility.raise_error;
2514 END IF; /* end confirm tax record exists */
2515 END IF; /*Percent time = 0 */
2516 CLOSE csr_chk_percent_state;
2517 --
2518 END IF; /* Maintenance of the percent time in state */
2519
2520 END IF; /* Records have been defaulted */
2521 --
2522 p_return_code := l_return_code;
2523 END; /*default_tax_with_validation */
2524 --
2525 ---Zero out time in state and localities in preparation for setting---
2526 ---the new work location to 100%---
2527 -- Currently this is called by the assignment form to
2528 -- zero out all element_entries/tax records so if another error
2529 -- is captured we do not end up with the sum of the entries having
2530 -- more than 100% time in jurisdiction.
2531 --
2532 PROCEDURE zero_out_time(p_assignment_id NUMBER)
2533 IS
2534
2535 -- Cursor to retreive the jurisdictions for all existing
2536 -- VERTEX element entries
2537 CURSOR vtx_info IS
2538 SELECT peev.screen_entry_value jurisdiction,
2539 peef.effective_start_date start_date
2540 FROM pay_element_entry_values_f peev,
2541 pay_element_entries_f peef,
2542 pay_element_links_f pel,
2543 pay_input_values_f piv,
2544 pay_element_types_f pet
2545 WHERE pet.element_name = 'VERTEX'
2546 AND pet.element_type_id = piv.element_type_id
2547 AND pel.element_type_id = pet.element_type_id
2548 AND peef.element_link_id = pel.element_link_id
2549 AND piv.input_value_id = 0 + peev.input_value_id
2550 AND peev.element_entry_id = peef.element_entry_id
2551 AND p_assignment_id = peef.assignment_id
2552 AND piv.name = 'Jurisdiction' ;
2553 --
2554 --
2555 l_information_type VARCHAR2(50);
2556
2557 BEGIN /* Zero out time */
2558 -- hr_utility.trace_on('Y');
2559 hr_utility.set_location('pay_us_emp_tax_rules_pkg.zero_out',1);
2560 /* Set all tax records to zero percent time in state */
2561 --
2562 -- Localities
2563 --
2564 UPDATE per_assignment_extra_info
2565 SET aei_information10 = 0
2566 WHERE assignment_id = p_assignment_id
2567 AND INFORMATION_TYPE = 'LOCALITY';
2568 hr_utility.set_location('pay_us_emp_tax_rules_pkg.zero_out',5);
2569 --
2570 -- States
2571 --
2572 UPDATE per_assignment_extra_info
2573 SET aei_information13 = 0,
2574 aei_information16 = 0 /* Remainder percent */
2575 WHERE assignment_id = p_assignment_id
2576 AND INFORMATION_TYPE = 'STATE';
2577 --
2578 --The part below was originaly included for performance but is now
2579 --being changed to an API call.
2580 --******************************************************
2581 --UPDATE pay_element_entry_values
2582 --SET screen_entry_value = '0'
2583 --WHERE element_entry_value_id in
2584 --( SELECT peev.element_entry_value_id
2585 -- FROM pay_element_entry_values_f peev, pay_element_entries_f peef,
2586 -- pay_input_values_f piv,
2587 -- pay_element_types_f pet
2588 -- WHERE pet.element_name = 'VERTEX'
2589 -- AND pet.element_type_id = piv.element_type_id
2590 -- AND piv.input_value_id = 0 + peev.input_value_id
2591 -- AND peev.element_entry_id = peef.element_entry_id
2592 -- AND p_assignment_id = peef.assignment_id
2593 -- AND piv.name = 'Percentage' );
2594 -- *****************************************************
2595 -- Now loop through all VERTEX element entries
2596 -- and set them to zero. This will get much more complicated
2597 -- with date tracking and should be self maintained within
2598 -- the create_vertex_entry package.
2599 --
2600 FOR cur_rec IN vtx_info LOOP
2601 --
2602 IF cur_rec.jurisdiction LIKE '%-000-0000' THEN
2603 l_information_type := 'State';
2604 ELSE
2605 l_information_type := 'Locality';
2606 END IF;
2607 --
2608 pay_us_emp_tax_rules_pkg.create_vertex_entry(
2609 P_mode => 'U',
2610 P_assignment_id => p_assignment_id,
2611 P_information_type => l_information_type,
2612 P_session_date => cur_rec.start_date,
2613 P_jurisdiction => cur_rec.jurisdiction,
2614 P_time_in_locality => '0',
2615 P_remainder_percent => '0');
2616 --
2617 END LOOP;
2618 --
2619 hr_utility.set_location('pay_us_emp_tax_rules_pkg.zero_out',10);
2620 -- hr_utility.trace_off;
2621 END; /* zero out time */
2622 --
2623 END pay_us_emp_tax_rules_pkg;
[Home] [Help]