1 PACKAGE BODY pay_us_emp_dt_tax_val AS
2 /* $Header: pyusdtvl.pkb 120.4 2011/09/29 20:57:41 tclewis ship $ */
3 /*
4
5 +======================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10
11 Package Body Name : pay_us_emp_dt_tax_val
12
13 Package File Name : pyusdtvl.pkb
14
15 Description : This package declares functions which are used for creating
16 and manipulating tax records for an assignment.
17
18 Change List:
19 ------------
20
21 Name Date Version Bug Text
22
23 Amita Chauhan 07-AUG-97 40.0 Created.
24
25 Amita Chauhan 15-JUL-98 40.1 Merging the branch version
26 into the 10.7 code tree.
27 Amita Chauhan 15-APR-99 40.2 Added the check for 'US Payroll'
28 install before checking for
29 future dated change in location.
30 - Bug# 863905.
31 achauhan 16-JUN-99 110.11 Changed the null to exit if
32 csr_get_next_locations not found
33 in case of FUTURE_CHANGE.
34 djoshi 24-dec-99 Added a funciton that returns
35 whether over-ride exits or
36 not.Modified the script to also
37 check for the over-ride.
38 achauhan 07-feb-2000 40.8 Made changes in get_all_work_codes
39 for Bug# 1186065 and 1179274.
40 dscully 21-jun-2000 40.9 Modified check_delete_row to
41 prevent deletion of work, tax, and
42 live locations in all situations
43 ahanda 22-JUL-2000 40.10 Changed the procedure
44 get_all_work_codes for performance
45 reasons.
46 dscully 9-AUG-2000 40.11/110.15 Changed reference to sysdate
47 to p_session_date in
48 get_all_work_codes
49 tclewis 26-JAN-2004 115.9 11.5.10 performance changes on the
50 following cursors:
54 and NOCOPY hint for GSCC compliance.
51 csr_check_state_purge, csr_check_county_purge,
52 csr_check_city_purge, csr_chk_payroll
53 ardsouza 29-JAN-2004 115.10 Added dbdrv: command, SET VERIFY OFF
55 sudedas 20-APR-2006 115.11 4563092 One message added to check_payroll_run
56 sudedas 11-Sep-2006 115.12 5486281 Turning Off SUI Wage Base Override Functionality .
57 ========================================================================
58 */
59
60 /* Check override state,county,city */
61
62 FUNCTION check_resi_override ( p_assignment_id in number,
63 p_session_date date,
64 p_state_code in varchar2,
65 p_county_code in varchar2,
66 p_city_code in varchar2
67 )
68 RETURN varchar2
69 IS
70
71 CURSOR csr_chk_res_state
72 IS
73 SELECT pus1.state_code,
74 pus.state_code
75 FROM pay_us_states pus,
76 pay_us_states pus1,
77 per_addresses pa,
78 per_assignments_f paf
79 WHERE paf.assignment_id = p_assignment_id
80 AND p_session_date BETWEEN paf.effective_start_date
81 AND paf.effective_end_date
82 AND paf.person_id = pa.person_id
83 AND p_session_date BETWEEN pa.date_from
84 AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
85 AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
86 AND pus1.state_abbrev = pa.region_2
87 AND pa.primary_flag = 'Y';
88
89 CURSOR csr_chk_res_county
90 IS
91 SELECT pus1.state_code,
92 puc1.county_code,
93 pus.state_code,
94 puc.county_code
95 FROM pay_us_states pus,
96 pay_us_states pus1,
97 pay_us_counties puc,
98 pay_us_counties puc1,
99 per_addresses pa,
100 per_assignments_f paf
101 WHERE paf.assignment_id = p_assignment_id
102 AND p_session_date BETWEEN paf.effective_start_date
103 AND paf.effective_end_date
104 AND paf.person_id = pa.person_id
105 AND p_session_date BETWEEN pa.date_from
106 AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
107 AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
108 AND pus.state_code = puc.state_code
109 AND puc.county_name = nvl(pa.add_information19,pa.region_1)
110 AND pus1.state_abbrev = pa.region_2
111 AND pus1.state_code = puc1.state_code
112 AND puc1.county_name = pa.region_1
113 AND pa.primary_flag = 'Y';
114
115
116
117 CURSOR csr_chk_res_city
118 IS SELECT pus1.state_code,
119 puc1.county_code,
120 pucy1.city_code,
121 pus.state_code,
122 puc.county_code,
123 pucy.city_code
124 FROM pay_us_states pus,
125 pay_us_states pus1,
126 pay_us_counties puc,
127 pay_us_counties puc1,
128 pay_us_city_names pucy,
129 pay_us_city_names pucy1,
130 per_addresses pa,
131 per_assignments_f paf
132 WHERE paf.assignment_id = p_assignment_id
133 AND p_session_date BETWEEN paf.effective_start_date
134 AND paf.effective_end_date
135 AND paf.person_id = pa.person_id
136 AND p_session_date BETWEEN pa.date_from
137 AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
138 AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
139 AND pus.state_code = puc.state_code
140 AND puc.county_name = nvl(pa.add_information19,pa.region_1)
141 AND pus1.state_abbrev = pa.region_2
142 AND pus1.state_code = puc1.state_code
143 AND puc1.county_name = pa.region_1
144 AND pucy.state_code = pus.state_code
145 AND pucy.state_code = puc.state_code
146 AND pucy.county_code = puc.county_code
147 AND pucy1.state_code = puc1.state_code
148 AND pucy1.county_code = puc1.county_code
149 AND pucy.city_name = nvl(pa.add_information18,pa.town_or_city)
150 AND pucy1.city_name = pa.town_or_city
151 AND pa.primary_flag = 'Y';
152
153
154
155 l_nml_res_state varchar2(2);
156 l_ovr_res_state varchar2(2);
157 l_nml_res_county_code varchar2(3);
158 l_ovr_res_county_code varchar2(3);
159 l_nml_res_city_code varchar2(4);
160 l_ovr_res_city_code varchar2(4);
161 l_cb_ovr_res varchar2(1);
162
163 BEGIN
164
165 /*Check if there is res. override state */
166 IF p_state_code IS NOT NULL
167 AND p_county_code IS NULL
168 AND p_city_code IS NULL
169 THEN
170
171 OPEN csr_chk_res_state;
172
173 FETCH csr_chk_res_state INTO l_nml_res_state,l_ovr_res_state;
174
175 IF csr_chk_res_state%NOTFOUND THEN
176 l_cb_ovr_res := 'N';
177 END IF;
178
179 CLOSE csr_chk_res_state;
180
181 IF (p_state_code = l_ovr_res_state
182 and l_nml_res_state <> l_ovr_res_state
183 ) THEN
184 l_cb_ovr_res := 'Y';
185 ELSE
186 l_cb_ovr_res := 'N';
187 END IF;
188 END IF;
189 /* check for res. override county */
190
191 IF p_state_code IS NOT NULL
192 AND p_county_code IS NOT NULL
193 AND p_city_code IS NULL THEN
194
195 OPEN csr_chk_res_county;
196 FETCH csr_chk_res_county
197 INTO l_nml_res_state,
198 l_nml_res_county_code,
199 l_ovr_res_state,
200 l_ovr_res_county_code;
201
202 IF csr_chk_res_county%NOTFOUND THEN
203 l_cb_ovr_res := 'N';
204 END IF;
205
206 CLOSE csr_chk_res_county;
207
208 /* check if the over-ride is same as the normal check box */
209
210 IF ( p_state_code = l_ovr_res_state
211 and p_county_code = l_ovr_res_county_code)
212 and (NOT( (l_nml_res_state = l_ovr_res_state) and
213 (l_nml_res_county_code = l_ovr_res_county_code)))
214 THEN
215 l_cb_ovr_res := 'Y';
216 ELSE
217 l_cb_ovr_res := 'N';
218 END IF;
219
220 END IF;
221
222 /* check if there is any res. override city */
223
224 IF p_state_code IS NOT NULL
225 AND p_county_code IS NOT NULL
226 AND p_city_code IS NOT NULL THEN
227
228
229 /* Check for the override resident city */
230
231 OPEN csr_chk_res_city;
232 FETCH csr_chk_res_city
233 INTO l_nml_res_state,
234 l_nml_res_county_code,
235 l_nml_res_city_code,
236 l_ovr_res_state,
237 l_ovr_res_county_code,
238 l_ovr_res_city_code;
239
240 IF csr_chk_res_city%NOTFOUND THEN
241 l_cb_ovr_res := 'N';
242 END IF;
243
244 CLOSE csr_chk_res_city;
245
246
247 IF p_state_code = l_ovr_res_state
248 and p_county_code = l_ovr_res_county_code
249 and p_city_code = l_ovr_res_city_code
250 and (NOT( (l_nml_res_state = l_ovr_res_state)
251 and (l_nml_res_county_code = l_ovr_res_county_code)
252 and (l_nml_res_city_code = l_ovr_res_city_code)
253 )
254 )
255 THEN
256 l_cb_ovr_res := 'Y';
257 ELSE
258 l_cb_ovr_res := 'N';
259 END IF;
260 END IF;
261 return l_cb_ovr_res;
262
263 END check_resi_override ;
264
265
266 FUNCTION check_work_override ( p_assignment_id in number,
267 p_session_date date,
268 p_state_code in varchar2,
269 p_county_code in varchar2,
270 p_city_code in varchar2
271 )
272 RETURN varchar2
273 IS CURSOR csr_work_state
274 IS SELECT pus1.state_code,
275 pus.state_code,
276 paf.location_id,
277 hscf.segment18
278 FROM pay_us_states pus,
279 pay_us_states pus1,
280 hr_locations hl,
281 hr_locations hl1,
282 hr_soft_coding_keyflex hscf,
283 per_assignments_f paf
284 WHERE paf.assignment_id = p_assignment_id
285 AND p_session_date BETWEEN paf.effective_start_date
286 AND paf.effective_end_date
287 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
288 AND hl.location_id = nvl(hscf.segment18,paf.location_id)
289 AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
290 AND hl1.location_id = paf.location_id
291 AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2);
292
293
294 CURSOR csr_work_county
295 IS SELECT pus1.state_code,
296 puc1.county_code,
297 pus.state_code,
298 puc.county_code,
299 paf.location_id,
300 hscf.segment18
301 FROM pay_us_states pus,
302 pay_us_states pus1,
303 pay_us_counties puc,
304 pay_us_counties puc1,
305 hr_locations hl,
306 hr_locations hl1,
307 hr_soft_coding_keyflex hscf,
308 per_assignments_f paf
309 WHERE paf.assignment_id = p_assignment_id
310 AND p_session_date BETWEEN paf.effective_start_date
311 AND paf.effective_end_date
312 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
313 AND hl.location_id = nvl(hscf.segment18,paf.location_id)
314 AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
315 AND hl1.location_id = paf.location_id
316 AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2)
317 AND pus1.state_code = puc1.state_code
318 AND pus.state_code = puc.state_code
319 AND puc.county_name = nvl(hl.loc_information19,hl.region_1)
320 and puc1.county_name = nvl(hl1.loc_information19,hl1.region_1);
321
322
323 CURSOR csr_work_city
324 IS SELECT pus1.state_code,
325 puc1.county_code,
326 pucy1.city_code,
327 pus.state_code,
328 puc.county_code,
329 pucy.city_code,
330 paf.location_id,
331 hscf.segment18
332 FROM pay_us_states pus,
333 pay_us_states pus1,
334 pay_us_counties puc,
335 pay_us_counties puc1,
336 pay_us_city_names pucy,
337 pay_us_city_names pucy1,
338 hr_locations hl,
339 hr_locations hl1,
340 hr_soft_coding_keyflex hscf,
341 per_assignments_f paf
342 WHERE paf.assignment_id = p_assignment_id
343 AND p_session_date BETWEEN paf.effective_start_date
344 AND paf.effective_end_date
345 AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
346 AND hl.location_id = nvl(hscf.segment18,paf.location_id)
347 AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
348 AND hl1.location_id = paf.location_id
349 AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2)
350 AND pus1.state_code = puc1.state_code
351 AND pus.state_code = puc.state_code
352 AND puc.county_name = nvl(hl.loc_information19,hl.region_1)
353 and puc1.county_name = nvl(hl1.loc_information19,hl1.region_1)
354 AND pucy.state_code = pus.state_code
355 AND pucy.county_code = puc.county_code
356 AND pucy.city_name = nvl(hl.loc_information18,hl.town_or_city)
357 AND pucy1.state_code = puc1.state_code
358 AND pucy1.county_code = puc1.county_code
359 AND pucy1.city_name = nvl(hl1.loc_information18,hl1.town_or_city);
360
361
362
363 l_nml_work_state varchar2(2);
364 l_ovr_work_state varchar2(2);
365 l_nml_work_county_code varchar2(3);
366 l_ovr_work_county_code varchar2(3);
367 l_nml_work_city_code varchar2(4);
368 l_ovr_work_city_code varchar2(4);
369 l_nml_work_location_id varchar2(60);
370 l_ovr_work_location_id varchar2(60);
371 l_cb_ovr_work varchar2(1);
372
373 BEGIN
374 /* Initialize the value of parameter to be 'N' */
375
376 IF p_state_code IS NOT NULL
377 AND p_county_code IS NULL
378 AND p_city_code IS NULL
379 THEN
380
381 OPEN csr_work_state;
382
383 FETCH csr_work_state
384 INTO l_nml_work_state,
385 l_ovr_work_state,
386 l_nml_work_location_id,
387 l_ovr_work_location_id;
388
389 IF csr_work_state%NOTFOUND THEN
390 l_cb_ovr_work := 'N';
391 END IF;
392
393 CLOSE csr_work_state;
394 IF (p_state_code = l_ovr_work_state
395 and l_nml_work_state <> l_ovr_work_state
396 ) THEN
397 l_cb_ovr_work := 'Y';
398 ELSE
399 l_cb_ovr_work := 'N';
400 END IF;
401 END IF;
402 /* county Work Checking */
403
404 IF p_state_code IS NOT NULL
405 AND p_county_code IS NOT NULL
406 AND p_city_code IS NULL
407 THEN
408
409 OPEN csr_work_county;
410
411 FETCH csr_work_county
412 INTO l_nml_work_state,
413 l_nml_work_county_code,
414 l_ovr_work_state,
415 l_ovr_work_county_code,
416 l_nml_work_location_id,
417 l_ovr_work_location_id;
418
419 IF csr_work_county%NOTFOUND THEN
420 l_cb_ovr_work := 'N';
421 END IF;
422
423 CLOSE csr_work_county;
424 IF ( p_state_code = l_ovr_work_state
428 )
425 and p_county_code = l_ovr_work_county_code
426 and (NOT( (l_nml_work_state = l_ovr_work_state)
427 and (l_nml_work_county_code = l_ovr_work_county_code)
429 )
430 )
431 THEN
432 l_cb_ovr_work := 'Y';
433 ELSE
434 l_cb_ovr_work := 'N';
435 END IF;
436 END IF;
437 /* checking city */
438
439 IF ( p_state_code IS NOT NULL
440 AND p_county_code IS NOT NULL
441 AND p_city_code IS NOT NULL )
442 THEN
443
444 OPEN csr_work_city;
445
446 FETCH csr_work_city
447 INTO l_nml_work_state,
448 l_nml_work_county_code,
449 l_nml_work_city_code,
450 l_ovr_work_state,
451 l_ovr_work_county_code,
452 l_ovr_work_city_code,
453 l_nml_work_location_id,
454 l_ovr_work_location_id;
455
456 IF csr_work_city%NOTFOUND THEN
457 l_cb_ovr_work := 'N';
458 END IF;
459
460 CLOSE csr_work_city;
461
462 IF ( p_state_code = l_ovr_work_state
463 AND p_county_code = l_ovr_work_county_code
464 AND p_city_code = l_ovr_work_city_code
465 AND (NOT( (l_nml_work_state = l_ovr_work_state)
466 and(l_nml_work_county_code = l_ovr_work_county_code)
467 and(l_nml_work_city_code = l_ovr_work_city_code)
468 )
469 )
470 ) THEN
471 l_cb_ovr_work := 'Y';
472 ELSE
473 l_cb_ovr_work := 'N';
474 END IF;
475 END IF;
476
477
478
479
480 return l_cb_ovr_work;
481
482 END check_work_override;
483
484
485
486 /* Name : check_payroll_run
487 Purpose : If datetrack mode is 'DELETE_NEXT_CHANGE' or 'FUTURE_CHANGE'
488 the procedure will check whether there is any future record
489 with different location id. If there is it will give and
490 error message to the user.
491 For all modes if location id is changed it will check
492 if a payroll has been run for the assignment,
493 within a given time period. The return value will be set
494 accordingly which will be used to raise warning message
495 */
496
497 function check_payroll_run ( p_assignment_id in number,
498 p_new_location_code in varchar2,
499 p_new_location_id in number,
500 p_session_date in date,
501 p_effective_start_date in date,
502 p_effective_end_date in date,
503 p_mode in varchar2) return varchar2 is
504
505
506 l_code number;
507 l_location_id number;
508 l_location_id_changed number := 0;
509 l_returned_warning varchar2(240) := NULL;
510 check_payroll_enabled number := 1;
511 l_fed_row_found varchar2(1) := 'N';
512 l_payroll_installed boolean := FALSE;
513
514 cursor csr_chk_loc_change is
515 select paf.location_id
516 from PER_ASSIGNMENTS_F paf
517 where paf.assignment_id = p_assignment_id
518 and p_session_date between paf.effective_start_date
519 and paf.effective_end_date;
520
521 cursor csr_get_next_locations is
522 select paf1.location_id
523 from per_assignments_f paf1
524 where paf1.assignment_id = p_assignment_id
525 and paf1.effective_start_date > p_effective_end_date
526 order by paf1.effective_start_date;
527
528 cursor csr_chk_payroll is
529 /* 11.5.10 changes performance modification. original code
530 commented out below. */
531 select 1
532 from per_assignments_f paf
533 ,pay_payroll_actions ppa
534 ,pay_assignment_actions paa
535 where paf.assignment_id = p_assignment_id
536 and ppa.payroll_id = paf.payroll_id
537 and ppa.action_type in ('E', 'Q','R')
538 and ppa.payroll_action_id = paa.payroll_action_id
539 and paa.assignment_id = paf.assignment_id
540 and trunc(ppa.date_earned) between p_effective_start_date
541 and p_effective_end_date;
542
543 /*
544
545 select 1
546 from PAY_ASSIGNMENT_ACTIONS paa
547 where paa.assignment_id = p_assignment_id
548 and exists(
549 select 1
550 from PAY_PAYROLL_ACTIONS ppa
551 where ppa.payroll_action_id = paa.payroll_action_id
552 and ppa.action_type in ('E','Q','R')
553 and trunc(ppa.date_earned) between p_effective_start_date
554 and p_effective_end_date );
555 */
556
557 cursor csr_chk_fed_row is
558 select 'Y'
559 from dual
560 where exists (select null
561 from PAY_US_EMP_FED_TAX_RULES_F pef
562 where pef.assignment_id = p_assignment_id);
563
567 hr_locations hl
564 cursor c_get_state_code(p_location_id in number) is
565 select pus.state_code
566 from pay_us_states pus,
568 where hl.location_id = p_location_id
569 and pus.state_abbrev = nvl(loc_information17,region_2);
570
571
572 l_work_state_code pay_us_states.state_code%type ;
573 l_new_work_state_code pay_us_states.state_code%type ;
574 l_work_state_name pay_us_states.state_name%type ;
575 l_work_county_code pay_us_counties.county_code%type ;
576 l_work_county_name pay_us_counties.county_name%type ;
577 l_work_city_code pay_us_city_names.city_code%type ;
578 l_work_city_name pay_us_city_names.city_name%type ;
579
580 l_returned_warning1 varchar2(240) := NULL;
581
582 begin
583
584 l_payroll_installed := hr_utility.chk_product_install(
585 p_product => 'Oracle Payroll',
586 p_legislation => 'US');
587
588 -- Added for SUI Wage Base enh
589 -- Turning Off SUI Wage Base Override Functionality due to Bug# 5486281
590 /*
591 get_work_codes(p_assignment_id,
592 p_session_date,
593 l_work_state_code,
594 l_work_county_code,
595 l_work_city_code,
596 l_work_state_name,
597 l_work_county_name,
598 l_work_city_name) ;
599
600 open c_get_state_code(p_new_location_id) ;
601 fetch c_get_state_code into l_new_work_state_code ;
602 if c_get_state_code%notfound then
603 close c_get_state_code ;
604 end if ;
605 */
606 if p_mode = 'DELETE_NEXT_CHANGE' and l_payroll_installed then
607
608 hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - opening cursor ',1);
609 open csr_get_next_locations;
610 fetch csr_get_next_locations into l_location_id;
611
612 if csr_get_next_locations%NOTFOUND then
613 NULL;
614 else
615 if l_location_id <> p_new_location_id then
616 check_payroll_enabled := 0;
617 fnd_message.set_name ('PAY', 'PAY_52282_FUTR_LOC_CHNG_EXISTS');
618 fnd_message.set_token('current_location_id',p_new_location_code);
619 app_exception.raise_exception; /* raise error message */
620 else
621 check_payroll_enabled := 1;
622 end if;
623 close csr_get_next_locations;
624 end if;
625 end if; /* end checking of DELETE_NEXT_CHANGE */
626
627 if p_mode = 'FUTURE_CHANGE' and l_payroll_installed then
628
629 hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - opening cursor ',2);
630 open csr_get_next_locations;
631 loop
632 check_payroll_enabled := 1;
633 fetch csr_get_next_locations into l_location_id;
634 if csr_get_next_locations%NOTFOUND then
635 exit;
636 else
637 if l_location_id <> p_new_location_id then
638 check_payroll_enabled := 0;
639 fnd_message.set_name ('PAY', 'PAY_52282_FUTR_LOC_CHNG_EXISTS');
640 fnd_message.set_token('current_location_id',p_new_location_code);
641 app_exception.raise_exception; /* raise error message */
642 close csr_get_next_locations;
643 exit;
644 end if;
645 end if;
646 end loop;
647 close csr_get_next_locations;
648 end if; /* end checking of FUTURE_CHANGE */
649
650 /* Payroll Run will be checked only if Payroll is installed and legislation is US */
651
652 if l_payroll_installed then
653 hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - opening cursor csr_loc_change',3);
654 open csr_chk_loc_change;
655 hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - opening cursor',4);
656 fetch csr_chk_loc_change into l_location_id;
657
658 if csr_chk_loc_change%FOUND then
659
660 /* Existence of payroll run will be checked only if location_id has
661 changed. Before checking for change in location see if any tax records
662 exist at all or not. This is needed to be done because a default
663 location is assigned to the assignment that is created for a new
664 person */
665
666 -- hr_utility.set_location('pay_us_emp_tax_rules.check_payroll_run...location changed',31) ;
667 -- This is being added as part of SUI Wage Base Override enh
668 -- Turning Off SUI Wage Base Override Functionality due to Bug# 5486281
669 /*
670 if l_work_state_code <> l_new_work_state_code then
671 fnd_message.set_name('PAY', 'PAY_52388_SUI_TAX_LOC_CHNG') ;
672 l_returned_warning1 := fnd_message.get ;
673 end if ;
674 */
675
676 open csr_chk_fed_row;
677 fetch csr_chk_fed_row into l_fed_row_found;
678 if csr_chk_fed_row%NOTFOUND then
679 l_fed_row_found := 'N';
680 end if;
681 close csr_chk_fed_row;
682
683 if l_fed_row_found = 'Y' then
684
685 if l_location_id <> p_new_location_id then
686 l_location_id_changed := 1;
687 if check_payroll_enabled=1 then
688 /* This is checked because if mode is DELETE-NEXT-CHANGE */
689 /* or DELETE-FUTURE-CHANGE and location_id is different */
690 /* in any of the next records user will get an error */
691 /* and there is no need to check for the payroll run */
692
693 open csr_chk_payroll;
694 hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - fetching cursor',4);
695 fetch csr_chk_payroll into l_code;
696
697 if csr_chk_payroll%FOUND then
698 hr_utility.set_location('pay_us_emp_tax_rules.check_payroll_run - payroll found',5);
699 fnd_message.set_name ('PAY', 'PAY_52232_TAX_LOC_CHNG');
700 l_returned_warning := fnd_message.get;
701 close csr_chk_payroll;
702 else
703 hr_utility.set_location('pay_us_emp_tax_rules.check_payroll_run - No payroll run',6);
704 fnd_message.set_name ('PAY', 'PAY_52233_TAX_LOC_CHNG_OVRD');
705 l_returned_warning := fnd_message.get;
706 close csr_chk_payroll;
707 end if; /* End csr_chk_payroll%found */
708 end if; /* End check_payroll_run_enabled=1 */
709 else
710 l_location_id_changed := 0;
711 end if; /* End l_location_id <> p_new_location_id */
712 end if; /* Federal record found */
713 end if; /* End csr_chk_loc_change%NOTFOUND */
714 end if; /* hr_utility.chk_product_install */
715
716 return l_returned_warning1 || l_returned_warning;
717
718 end check_payroll_run;
719
720
721 /* Name : check_in_work_location
722
723 Purpose : To check if the state has ever been assigned as the state of
724 a work location for the assignment.
725
726 Parameter : ret_code = 1 -> State/County/City has been assigned
727 as work location
728 of the assignment.
729 ret_code = 0 -> State/County/City has never been
730 assigned as the work location of
731 the assignment.
732 ret_code = 2 -> State/County/City has been assigned as the
733 taxation location of assignment.
734 */
735
736 procedure check_in_work_location ( p_assignment_id in number,
737 p_state_code in varchar2,
738 p_county_code in varchar2,
739 p_city_code in varchar2,
740 p_ret_code in out NOCOPY number,
741 p_ret_text in out NOCOPY varchar2) is
742
743 l_code number;
744
745 /* Cursor to check if the state has been assigned as the state of a work
746 location of the assignment. */
747
748 /* begin modifications - dscully 21-JUN-2000 */
749 /* modified cursors to check _both_ mailing and taxation address, instead of one or the other */
750 /* created three new cursors to check taxation location jurisdictions */
751
752 CURSOR csr_check_state is
753 select 1
754 from HR_LOCATIONS hrl,
755 PER_ASSIGNMENTS_F paf
756 where paf.assignment_id = p_assignment_id
757 and hrl.location_id = paf.location_id
758 and exists (select null
759 from PAY_US_STATES pus
760 where pus.state_abbrev in (hrl.loc_information17,hrl.region_2)
761 and pus.state_code = p_state_code);
762
763 CURSOR csr_check_ovrd_state is
764 select 1
765 from HR_LOCATIONS hrl,
766 HR_SOFT_CODING_KEYFLEX hscf,
767 PER_ASSIGNMENTS_F paf
768 where paf.assignment_id = p_assignment_id
769 and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
770 and hrl.location_id = hscf.segment18
771 and exists (select null
772 from PAY_US_STATES pus
773 where pus.state_abbrev in (hrl.loc_information17,hrl.region_2)
774 and pus.state_code = p_state_code);
775
776 /* Cursor to check if the county has been assigned as the county of a work
777 location of the assignment. */
778
779 CURSOR csr_check_county is
780 select 1
781 from HR_LOCATIONS hrl,
782 PER_ASSIGNMENTS_F paf
783 where paf.assignment_id = p_assignment_id
784 and hrl.location_id = paf.location_id
785 and exists (select null
786 from PAY_US_COUNTIES puc,
787 PAY_US_STATES pus
788 where ((pus.state_abbrev = hrl.loc_information17
789 and puc.county_name = hrl.loc_information19)
790 or
791 (pus.state_abbrev = hrl.region_2
792 and puc.county_name = hrl.region_1))
793 and pus.state_code = p_state_code
794 and puc.state_code = pus.state_code
795 and puc.county_code = p_county_code);
796
797 CURSOR csr_check_ovrd_county is
798 select 1
799 from HR_LOCATIONS hrl,
800 HR_SOFT_CODING_KEYFleX hscf,
801 PER_ASSIGNMENTS_F paf
802 where paf.assignment_id = p_assignment_id
803 and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
804 and hrl.location_id = hscf.segment18
805 and exists (select null
806 from PAY_US_COUNTIES puc,
807 PAY_US_STATES pus
808 where ((pus.state_abbrev = hrl.loc_information17
809 and puc.county_name = hrl.loc_information19)
810 or
811 (pus.state_abbrev = hrl.region_2
812 and puc.county_name = hrl.region_1))
813 and pus.state_code = p_state_code
814 and puc.state_code = pus.state_code
815 and puc.county_code = p_county_code);
816
817 /* Cursor to check if the city has been assigned as the city of a work
818 location of the assignment. */
819
820 CURSOR csr_check_city is
821 select 1
822 from HR_LOCATIONS hrl,
823 PER_ASSIGNMENTS_F paf
824 where paf.assignment_id = p_assignment_id
825 and hrl.location_id = paf.location_id
826 and exists (select null
827 from PAY_US_CITY_NAMES puci,
828 PAY_US_COUNTIES puco,
829 PAY_US_STATES pus
830 where ((pus.state_abbrev = hrl.loc_information17
831 and puco.county_name = hrl.loc_information19
832 and puci.city_name = hrl.loc_information18)
833 or
834 (pus.state_abbrev = hrl.region_2
835 and puco.county_name = hrl.region_1
836 and puci.city_name = hrl.town_or_city))
837 and pus.state_code = p_state_code
838 and puco.state_code = pus.state_code
839 and puco.county_code = p_county_code
840 and puci.state_code = pus.state_code
841 and puci.county_code = puco.county_code
842 and puci.city_code = p_city_code);
843
844 CURSOR csr_check_ovrd_city is
845 select 1
846 from HR_LOCATIONS hrl,
847 HR_SOFT_CODING_KEYFLEX hscf,
848 PER_ASSIGNMENTS_F paf
849 where paf.assignment_id = p_assignment_id
850 and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
851 and hrl.location_id = hscf.segment18
852 and exists (select null
853 from PAY_US_CITY_NAMES puci,
854 PAY_US_COUNTIES puco,
855 PAY_US_STATES pus
856 where ((pus.state_abbrev = hrl.loc_information17
857 and puco.county_name = hrl.loc_information19
858 and puci.city_name = hrl.loc_information18)
859 or
860 (pus.state_abbrev = hrl.region_2
861 and puco.county_name = hrl.region_1
862 and puci.city_name = hrl.town_or_city))
863 and pus.state_code = p_state_code
867 and puci.county_code = puco.county_code
864 and puco.state_code = pus.state_code
865 and puco.county_code = p_county_code
866 and puci.state_code = pus.state_code
868 and puci.city_code = p_city_code);
869
870 begin
871
872 if (p_state_code is not null and p_county_code is null
873 and p_city_code is null)
874 then
875
876 /* Check if the state has been assigned to the work location */
877
878 open csr_check_state;
879
880 fetch csr_check_state into l_code;
881
882 if csr_check_state%FOUND then
883
884 hr_utility.set_location(
885 'pay_us_emp_tax_rules.check_in_work_location' ||
886 ' - found work location',1);
887 p_ret_code := 1;
888 p_ret_text := 'State assigned to work location';
889 else
890
891 open csr_check_ovrd_state;
892 fetch csr_check_ovrd_state into l_code;
893 if csr_check_ovrd_state%FOUND then
894 hr_utility.set_location(
895 'pay_us_emp_tax_rules.check_in_work_location' ||
896 ' - found taxation location',1);
897 p_ret_code := 2;
898 p_ret_text := 'State assigned to taxation location';
899 else
900 hr_utility.set_location(
901 'pay_us_emp_tax_rules.check_in_work_location' ||
902 ' - not in work or taxation location',1);
903 p_ret_code := 0;
904 p_ret_text := 'State not assigned to work or taxation location';
905
906 end if;
907
908 close csr_check_ovrd_state;
909 end if;
910
911 close csr_check_state;
912
913 elsif (p_state_code is not null and p_county_code is not null
914 and p_city_code is null)
915 then
916
917 /* Check if the county has been assigned to the work location */
918
919 open csr_check_county;
920
921 fetch csr_check_county into l_code;
922
923 if csr_check_county%FOUND then
924
925 hr_utility.set_location(
926 'pay_us_emp_tax_rules.check_in_work_location' ||
927 ' - found work location',2);
928 p_ret_code := 1;
929 p_ret_text := 'County assigned to work location';
930 else
931 open csr_check_ovrd_county;
932
933 fetch csr_check_ovrd_county into l_code;
934
935 if csr_check_ovrd_county%FOUND then
936
937 hr_utility.set_location(
938 'pay_us_emp_tax_rules.check_in_work_location' ||
939 ' - found taxation location',2);
940 p_ret_code := 2;
941 p_ret_text := 'County assigned to taxation location';
942
943 else
944
945 hr_utility.set_location(
946 'pay_us_emp_tax_rules.check_in_work_location' ||
947 ' - not in work location',2);
948 p_ret_code := 0;
949 p_ret_text := 'County not assigned to work or taxation location';
950
951 end if;
952
953 close csr_check_ovrd_county;
954 end if;
955
956 close csr_check_county;
957
958 elsif (p_state_code is not null and p_county_code is not null
959 and p_city_code is not null)
960 then
961
962 /* Check if the city has been assigned to the work location */
963
964 open csr_check_city;
965
966 fetch csr_check_city into l_code;
967
968 if csr_check_city%FOUND then
969
970 hr_utility.set_location(
971 'pay_us_emp_tax_rules.check_in_work_location' ||
972 ' - found work location',3);
973 p_ret_code := 1;
974 p_ret_text := 'City assigned to work location';
975
976 else
977
978 open csr_check_ovrd_city;
979
980 fetch csr_check_ovrd_city into l_code;
981
982 if csr_check_ovrd_city%FOUND then
983
984 hr_utility.set_location(
985 'pay_us_emp_tax_rules.check_in_work_location' ||
986 ' - found taxationk location',3);
987 p_ret_code := 2;
988 p_ret_text := 'City assigned to taxation location';
989
990 else
991 hr_utility.set_location(
992 'pay_us_emp_tax_rules.check_in_work_location' ||
993 ' - not in work location',7);
994 p_ret_code := 0;
995 p_ret_text := 'City not assigned to work or taxation location';
996 end if;
997
998 close csr_check_ovrd_city;
999
1000 end if;
1001
1002 close csr_check_city;
1003
1004 end if;
1005
1006 /* end modifications - dscully 21-JUN-2000 */
1007
1008 end check_in_work_location;
1009
1010
1011 /* Name : check_in_res_addr
1012
1013 Purpose : To check if the state has ever been assigned as the state
1014 of a resident address for the assignment.
1015
1016 Parameter : ret_code = 1 -> State/County/City has been assigned
1017 as resident address of the assignment.
1018 ret_code = 0 -> State/County/City has never been
1019 assigned as resident address of
1020 the assignment.
1024 procedure check_in_res_addr ( p_assignment_id in number,
1021 */
1022
1023
1025 p_state_code in varchar2,
1026 p_county_code in varchar2,
1027 p_city_code in varchar2,
1028 p_ret_code in out NOCOPY number,
1029 p_ret_text in out NOCOPY varchar2) is
1030
1031 l_code number;
1032
1033 /* Cursor to check if the state has been assigned as the state of a
1034 resident address of the assignment. */
1035
1036 /* begin modifications - dscully 21-JUN-2000 */
1037 /* modified cursors to check _both_ mailing and taxation address, instead of one or the other */
1038
1039 CURSOR csr_check_state is
1040 select 1
1041 from PER_ADDRESSES pa,
1042 PER_ASSIGNMENTS_F paf
1043 where paf.assignment_id = p_assignment_id
1044 and pa.person_id = paf.person_id
1045 and exists (select null
1046 from PAY_US_STATES pus
1047 where pus.state_abbrev in (pa.add_information17,pa.region_2)
1048 and pus.state_code = p_state_code);
1049
1050 /* Cursor to check if the county has been assigned as the county of a
1051 resident address of the assignment. */
1052
1053 CURSOR csr_check_county is
1054 select 1
1055 from PER_ADDRESSES pa,
1056 PER_ASSIGNMENTS_F paf
1057 where paf.assignment_id = p_assignment_id
1058 and pa.person_id = paf.person_id
1059 and exists (select null
1060 from PAY_US_COUNTIES puc,
1061 PAY_US_STATES pus
1062 where ((pus.state_abbrev = pa.add_information17
1063 and puc.county_name = pa.add_information19)
1064 or
1065 (pus.state_abbrev = pa.region_2
1066 and puc.county_name = pa.region_1))
1067 and pus.state_code = p_state_code
1068 and puc.state_code = pus.state_code
1069 and puc.county_code = p_county_code);
1070
1071 /* Cursor to check if the city has been assigned as the city of a
1072 resident address of the assignment. */
1073
1074 CURSOR csr_check_city is
1075 select 1
1076 from PER_ADDRESSES pa,
1077 PER_ASSIGNMENTS_F paf
1078 where paf.assignment_id = p_assignment_id
1079 and pa.person_id = paf.person_id
1080 and exists (select null
1081 from PAY_US_CITY_NAMES puci,
1082 PAY_US_COUNTIES puco,
1083 PAY_US_STATES pus
1084 where ((pus.state_abbrev = pa.add_information17
1085 and puco.county_name = pa.add_information19
1086 and puci.city_name = pa.add_information18)
1087 or
1088 (pus.state_abbrev = pa.region_2
1089 and puco.county_name = pa.region_1
1090 and puci.city_name = pa.town_or_city))
1091 and pus.state_code = p_state_code
1092 and puco.state_code = pus.state_code
1093 and puco.county_code = p_county_code
1094 and puci.state_code = pus.state_code
1095 and puci.county_code = puco.county_code
1096 and puci.city_code = p_city_code);
1097
1098 /* end modifications - dscully 21-JUN-2000 */
1099
1100 begin
1101
1102 if (p_state_code is not null and p_county_code is null
1103 and p_city_code is null)
1104 then
1105
1106 /* Check if the state has been assigned to the resident addr.*/
1107
1108 hr_utility.set_location(
1109 'pay_us_emp_tax_rules.check_in_res_addr'||
1110 ' - opening cursor',1);
1111
1112 open csr_check_state;
1113
1114 hr_utility.set_location(
1115 'pay_us_emp_tax_rules.check_in_res_addr'||
1116 ' - fetching cursor',2);
1117
1118 fetch csr_check_state into l_code;
1119
1120 if csr_check_state%FOUND then
1121
1122 hr_utility.set_location(
1123 'pay_us_emp_tax_rules.check_in_res_addr' ||
1124 ' - found work location',3);
1125
1126 p_ret_code := 1;
1127 p_ret_text := 'State assigned to resident address.';
1128
1129 else
1130
1131 hr_utility.set_location(
1132 'pay_us_emp_tax_rules.check_in_res_addr'||
1133 ' - did not find work location',4);
1134
1135 p_ret_code := 0;
1136 p_ret_text := 'State not assigned to resident address.';
1137
1138 end if;
1139
1140 close csr_check_state;
1141
1142 elsif (p_state_code is not null and p_county_code is not null
1143 and p_city_code is null)
1144 then
1145
1146 /* Check if the county has been assigned to resident addr. */
1147
1148 hr_utility.set_location(
1149 'pay_us_emp_tax_rules.check_in_res_addr'||
1150 ' - opening cursor',5);
1151
1152 open csr_check_county;
1153
1154 hr_utility.set_location(
1155 'pay_us_emp_tax_rules.check_in_res_addr'||
1156 ' - fetching cursor',6);
1157
1158 fetch csr_check_county into l_code;
1159
1160 if csr_check_county%FOUND then
1161
1165
1162 hr_utility.set_location(
1163 'pay_us_emp_tax_rules.check_in_res_addr' ||
1164 ' - found work location',7);
1166 p_ret_code := 1;
1167 p_ret_text := 'County assigned to resident address.';
1168
1169 else
1170
1171 hr_utility.set_location(
1172 'pay_us_emp_tax_rules.check_in_res_addr'||
1173 ' - did not find work location',8);
1174
1175 p_ret_code := 0;
1176 p_ret_text := 'County not assigned to resident address.';
1177
1178 end if;
1179
1180 close csr_check_county;
1181
1182 elsif (p_state_code is not null and p_county_code is not null
1183 and p_city_code is not null)
1184 then
1185
1186 /* Check if the city has been assigned to resident addr. */
1187
1188 hr_utility.set_location(
1189 'pay_us_emp_tax_rules.check_in_res_addr'||
1190 ' - opening cursor',9);
1191
1192 open csr_check_city;
1193
1194 hr_utility.set_location(
1195 'pay_us_emp_tax_rules.check_in_res_addr'||
1196 ' - fetching cursor',10);
1197
1198 fetch csr_check_city into l_code;
1199
1200 if csr_check_city%FOUND then
1201 hr_utility.set_location(
1202 'pay_us_emp_tax_rules.check_in_res_addr' ||
1203 ' - found work location',11);
1204
1205 p_ret_code := 1;
1206 p_ret_text := 'City assigned to resident address.';
1207
1208 else
1209
1210 hr_utility.set_location(
1211 'pay_us_emp_tax_rules.check_in_res_addr'||
1212 ' - did not find work location',12);
1213
1214 p_ret_code := 0;
1215 p_ret_text := 'City not assigned to resident address.';
1216
1217 end if;
1218
1219 close csr_check_city;
1220
1221 end if;
1222
1223 end check_in_res_addr;
1224
1225
1226 /* Name : payroll_check_for_purge
1227
1228 Purpose : Before a state tax rules record is allowed to be purged,
1229 a call will be made to this procedure to check if a
1230 payroll has been run for that state.
1231 Similary before a county/city tax rules record is
1232 allowed to be purged, a check will be made by calling
1233 this procedure to check if it has been used in a
1234 payroll run or not.
1235 Refer Business Rule#12 under 'New Business Rules'
1236 section of the W4 date track HLD.
1237
1238 Parameter : ret_code = 1 -> Payroll has been run for the
1239 State/County/City.
1240 ret_code = 0 -> Payroll has not been run for the
1241 State/County/City.
1242 */
1243
1244 procedure payroll_check_for_purge ( p_assignment_id in number,
1245 p_state_code in varchar2,
1246 p_county_code in varchar2,
1247 p_city_code in varchar2,
1248 p_ret_code in out NOCOPY number,
1249 p_ret_text in out NOCOPY varchar2) is
1250
1251 l_code number;
1252
1253 /* Cursor to check if a payroll has been run for the state. */
1254
1255 CURSOR csr_check_state_purge is
1256 /* 11.5.10 changes performance modification. original code
1257 commented out below. */
1258
1259 select 1
1260 from per_assignments_f paf
1261 ,pay_payroll_actions ppa
1262 ,pay_assignment_actions paa
1263 ,pay_run_results prr
1264 where substr(prr.jurisdiction_code,1,2) = p_state_code
1265 and paf.assignment_id = p_assignment_id
1266 and ppa.payroll_id = paf.payroll_id
1267 and ppa.action_type in ('E', 'Q','R')
1268 and ppa.payroll_action_id = paa.payroll_action_id
1269 and paa.assignment_id = paf.assignment_id
1270 and paa.assignment_action_id = prr.assignment_action_id ;
1271
1272 /* select 1
1273 from pay_run_results prr,
1274 pay_assignment_actions paa
1275 where substr(prr.jurisdiction_code,1,2) = p_state_code
1276 and paa.assignment_action_id = prr.assignment_action_id
1277 and paa.assignment_id = p_assignment_id
1278 and exists ( select null
1279 from pay_payroll_actions ppa
1280 where ppa.payroll_action_id = paa.payroll_action_id
1281 and ppa.action_type in ('E','Q','R'));
1282 */
1283 /* Cursor to check if a payroll has been run for the county. */
1284
1285 CURSOR csr_check_county_purge is
1286 /* 11.5.10 changes performance modification. original code
1287 commented out below. */
1288 select 1
1289 from per_assignments_f paf
1290 ,pay_payroll_actions ppa
1291 ,pay_assignment_actions paa
1292 ,pay_run_results prr
1293 where substr(prr.jurisdiction_code,1,6) = p_state_code || '-'||
1294 p_county_code
1295 and paf.assignment_id = p_assignment_id
1296 and ppa.payroll_id = paf.payroll_id
1297 and ppa.action_type in ('E', 'Q','R')
1298 and ppa.payroll_action_id = paa.payroll_action_id
1299 and paa.assignment_id = paf.assignment_id
1300 and paa.assignment_action_id = prr.assignment_action_id ;
1301
1302 /*
1303 select 1
1307 p_county_code
1304 from pay_run_results prr,
1305 pay_assignment_actions paa
1306 where substr(prr.jurisdiction_code,1,6) = p_state_code || '-'||
1308 and paa.assignment_action_id = prr.assignment_action_id
1309 and paa.assignment_id = p_assignment_id
1310 and exists ( select null
1311 from pay_payroll_actions ppa
1312 where ppa.payroll_action_id = paa.payroll_action_id
1313 and ppa.action_type in ('E','Q','R'));
1314 */
1315
1316 /* Cursor to check if a payroll has been run for the city. */
1317
1318 CURSOR csr_check_city_purge is
1319
1320 /* 11.5.10 changes performance modification. original code
1321 commented out below. */
1322 select 1
1323 from per_assignments_f paf
1324 ,pay_payroll_actions ppa
1325 ,pay_assignment_actions paa
1326 ,pay_run_results prr
1327 where prr.jurisdiction_code = p_state_code || '-'||
1328 p_county_code ||'-'||p_city_code
1329 and paf.assignment_id = p_assignment_id
1330 and ppa.payroll_id = paf.payroll_id
1331 and ppa.action_type in ('E', 'Q','R')
1332 and ppa.payroll_action_id = paa.payroll_action_id
1333 and paa.assignment_id = paf.assignment_id
1334 and paa.assignment_action_id = prr.assignment_action_id ;
1335
1336 /* select 1
1337 from pay_run_results prr,
1338 pay_assignment_actions paa
1339 where prr.jurisdiction_code = p_state_code || '-'||
1340 p_county_code ||'-'||p_city_code
1341 and paa.assignment_action_id = prr.assignment_action_id
1342 and paa.assignment_id = p_assignment_id
1343 and exists ( select null
1344 from pay_payroll_actions ppa
1345 where ppa.payroll_action_id = paa.payroll_action_id
1346 and ppa.action_type in ('E','Q','R'));
1347 */
1348
1349 begin
1350
1351 if (p_state_code is not null and p_county_code is null
1352 and p_city_code is null)
1353 then
1354
1355 /* Check if payroll has been run the state tax rule record */
1356
1357 hr_utility.set_location(
1358 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1359 ' - opening cursor',1);
1360
1361 open csr_check_state_purge;
1362
1363 hr_utility.set_location(
1364 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1365 ' - fetching cursor',2);
1366
1367 fetch csr_check_state_purge into l_code;
1368
1369 if csr_check_state_purge%FOUND then
1370
1371 hr_utility.set_location(
1372 'pay_us_emp_tax_rules.payroll_check_for_purge' ||
1373 ' - found work location',3);
1374
1375 p_ret_code := 1;
1376 p_ret_text := 'Payroll has been run for the state.';
1377
1378 else
1379
1380 hr_utility.set_location(
1381 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1382 ' - did not find work location',4);
1383
1384 p_ret_code := 0;
1385 p_ret_text := 'Payroll has not been run for the state.';
1386
1387 end if;
1388
1389 close csr_check_state_purge;
1390
1391 elsif (p_state_code is not null and p_county_code is not null
1392 and p_city_code is null)
1393 then
1394
1395 /* Check if the county has been assigned to the work location */
1396
1397 hr_utility.set_location(
1398 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1399 ' - opening cursor',5);
1400
1401 open csr_check_county_purge;
1402
1403 hr_utility.set_location(
1404 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1405 ' - fetching cursor',6);
1406
1407 fetch csr_check_county_purge into l_code;
1408
1409 if csr_check_county_purge%FOUND then
1410
1411 hr_utility.set_location(
1412 'pay_us_emp_tax_rules.payroll_check_for_purge' ||
1413 ' - found work location',7);
1414
1415 p_ret_code := 1;
1416 p_ret_text := 'Payroll has been run for the county.';
1417
1418 else
1419
1420 hr_utility.set_location(
1421 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1422 ' - did not find work location',8);
1423
1424 p_ret_code := 0;
1425 p_ret_text := 'Payroll has not been run for the county.';
1426
1427 end if;
1428
1429 close csr_check_county_purge;
1430
1431 elsif (p_state_code is not null and p_county_code is not null
1432 and p_city_code is not null)
1433 then
1434
1435 /* Check if the city has been assigned to the work location */
1436
1437 hr_utility.set_location(
1438 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1439 ' - opening cursor',9);
1440
1441 open csr_check_city_purge;
1442
1443 hr_utility.set_location(
1444 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1445 ' - fetching cursor',10);
1446
1447 fetch csr_check_city_purge into l_code;
1448
1449 if csr_check_city_purge%FOUND then
1450
1451 hr_utility.set_location(
1455 p_ret_code := 1;
1452 'pay_us_emp_tax_rules.payroll_check_for_purge' ||
1453 ' - found work location',11);
1454
1456 p_ret_text := 'Payroll has been run for the city.';
1457
1458 else
1459
1460 hr_utility.set_location(
1461 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1462 ' - did not find work location',12);
1463
1464 p_ret_code := 0;
1465 p_ret_text := 'Payroll has not been run for the city.';
1466
1467 end if;
1468
1469 close csr_check_city_purge;
1470
1471 end if;
1472
1473 end payroll_check_for_purge;
1474
1475 procedure check_school_district (p_assignment in number,
1476 p_start_date in date,
1477 p_end_date in date,
1478 p_mode in varchar2,
1479 p_rowid in varchar2) is
1480
1481 l_code number;
1482
1483 cursor chk_ins_sd is
1484 select 1
1485 from PAY_US_EMP_COUNTY_TAX_RULES_F pcf
1486 where pcf.assignment_id = p_assignment
1487 and pcf.school_district_code is not null
1488 and pcf.effective_end_date >= p_start_date
1489 and pcf.effective_start_date <= p_end_date
1490 UNION ALL
1491 select 1
1492 from PAY_US_EMP_CITY_TAX_RULES_F pcif
1493 where pcif.assignment_id = p_assignment
1494 and pcif.school_district_code is not null
1495 and pcif.effective_end_date >= p_start_date
1496 and pcif.effective_start_date <= p_end_date
1497 and pcif.state_code <> '39';
1498
1499 cursor chk_upd_sd is
1500 select 1
1501 from PAY_US_EMP_COUNTY_TAX_RULES_F pcf
1502 where pcf.assignment_id = p_assignment
1503 and pcf.school_district_code is not null
1504 and pcf.effective_end_date >= p_start_date
1505 and pcf.effective_start_date <= p_end_date
1506 and rowid <> chartorowid(p_rowid)
1507 UNION ALL
1508 select 1
1509 from PAY_US_EMP_CITY_TAX_RULES_F pcif
1510 where pcif.assignment_id = p_assignment
1511 and pcif.school_district_code is not null
1512 and pcif.effective_end_date >= p_start_date
1513 and pcif.effective_start_date <= p_end_date
1514 and rowid <> chartorowid(p_rowid)
1515 and pcif.state_code <> '39';
1516 begin
1517
1518 if p_mode = 'I' then
1519
1520 open chk_ins_sd;
1521 fetch chk_ins_sd into l_code;
1522
1523 if chk_ins_sd%FOUND then
1524 fnd_message.set_name ('PAY', 'PAY_52300_TAX_SD_ASGN');
1525 fnd_message.raise_error;
1526 end if;
1527
1528 close chk_ins_sd;
1529
1530 elsif p_mode = 'U' then
1531
1532 open chk_upd_sd;
1533 fetch chk_upd_sd into l_code;
1534
1535 if chk_upd_sd%FOUND then
1536 fnd_message.set_name ('PAY', 'PAY_52300_TAX_SD_ASGN');
1537 fnd_message.raise_error;
1538 end if;
1539
1540 close chk_upd_sd;
1541
1542 end if;
1543
1544
1545 end check_school_district;
1546
1547 function check_locations ( p_assignment_id in number,
1548 p_effective_start_date in date,
1549 p_business_group_id in number) return boolean is
1550
1551 cursor csr_get_curr_loc is
1552 select location_id,
1553 effective_start_date,
1554 effective_end_date
1555 from per_assignments_f
1556 where assignment_id = p_assignment_id
1557 and business_group_id + 0 = p_business_group_id
1558 and p_effective_start_date between effective_start_date
1559 and effective_end_date;
1560
1561 cursor csr_get_future_locations (p_validation_date date)is
1562 select location_id
1563 from per_assignments_f
1564 where assignment_id = p_assignment_id
1565 and business_group_id + 0 = p_business_group_id
1566 and effective_start_date > p_validation_date;
1567
1568 l_curr_loc_id number;
1569 l_next_loc_id number;
1570 l_curr_eff_start_dt date;
1571 l_curr_eff_end_dt date;
1572 l_found boolean;
1573
1574 begin
1575
1576 open csr_get_curr_loc;
1577
1578 fetch csr_get_curr_loc into l_curr_loc_id,
1579 l_curr_eff_start_dt,
1580 l_curr_eff_end_dt;
1581
1582 if csr_get_curr_loc%NOTFOUND then
1583 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1584 fnd_message.set_token('PROCEDURE',
1585 'pay_us_emp_dt_tax_rules.check_locations');
1586 fnd_message.set_token('STEP','1');
1587 fnd_message.raise_error;
1588 close csr_get_curr_loc;
1589
1590 end if;
1591
1592 close csr_get_curr_loc;
1593
1594 l_found := FALSE;
1595
1596 open csr_get_future_locations(l_curr_eff_end_dt);
1597
1598 loop
1599 fetch csr_get_future_locations into l_next_loc_id;
1600 exit when csr_get_future_locations%NOTFOUND;
1601 if l_next_loc_id <> l_curr_loc_id then
1602 l_found := TRUE;
1603 exit;
1604 end if;
1605 end loop;
1606
1607 close csr_get_future_locations;
1608
1609 return l_found;
1610
1614 procedure get_res_codes (p_assignment_id in number,
1611 end check_locations;
1612
1613
1615 p_session_date in date,
1616 p_res_state_code out NOCOPY varchar2,
1617 p_res_county_code out NOCOPY varchar2,
1618 p_res_city_code out NOCOPY varchar2,
1619 p_res_state_name out NOCOPY varchar2,
1620 p_res_county_name out NOCOPY varchar2,
1621 p_res_city_name out NOCOPY varchar2) is
1622
1623 /* Cursor to get the resident state, county and city codes */
1624 cursor csr_get_res is
1625 select pus.state_code,
1626 puc.county_code,
1627 puci.city_code,
1628 pus.state_name,
1629 puc.county_name,
1630 puci.city_name
1631 from PAY_US_CITY_NAMES puci,
1632 PAY_US_COUNTIES puc,
1633 PAY_US_STATES pus,
1634 PER_ADDRESSES pa,
1635 PER_ASSIGNMENTS_F paf
1636 where paf.assignment_id = p_assignment_id
1637 and p_session_date between paf.effective_start_date and
1638 paf.effective_end_date
1639 and pa.person_id = paf.person_id
1640 and pa.primary_flag = 'Y'
1641 and p_session_date between pa.date_from and
1642 nvl(pa.date_to,p_session_date)
1643 and pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
1644 and puc.state_code = pus.state_code
1645 and puc.county_name = nvl(pa.add_information19,pa.region_1)
1646 and puci.state_code = pus.state_code
1647 and puci.county_code = puc.county_code
1648 and puci.city_name = nvl(pa.add_information18,pa.town_or_city);
1649
1650 begin
1651
1652 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_res_work',1);
1653
1654 /* Get the resident address details */
1655
1656 open csr_get_res;
1657
1658 fetch csr_get_res into p_res_state_code,
1659 p_res_county_code,
1660 p_res_city_code,
1661 p_res_state_name,
1662 p_res_county_name,
1663 p_res_city_name;
1664
1665 if csr_get_res%NOTFOUND then
1666
1667 p_res_state_code := null;
1668 p_res_county_code := null;
1669 p_res_city_code := null;
1670 p_res_state_name := null;
1671 p_res_county_name := null;
1672 p_res_city_name := null;
1673
1674 end if;
1675
1676 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_res_work',2);
1677
1678 close csr_get_res;
1679
1680 end get_res_codes;
1681
1682
1683 /* Name : get_work_codes
1684 Purpose : To get the work state code, work state name, work city code,
1685 work city name, work county code and work county name. This
1686 procedure will also be called by the tax form PAYEETAX.fmb,
1687 to get the names of the wok state and localities */
1688
1689 procedure get_work_codes (p_assignment_id in number,
1690 p_session_date in date,
1691 p_work_state_code out NOCOPY varchar2,
1692 p_work_county_code out NOCOPY varchar2,
1693 p_work_city_code out NOCOPY varchar2,
1694 p_work_state_name out NOCOPY varchar2,
1695 p_work_county_name out NOCOPY varchar2,
1696 p_work_city_name out NOCOPY varchar2) is
1697
1698 /* Cursor to get the work state, county and city */
1699
1700 cursor csr_get_work is
1701 select pus.state_code,
1702 puc.county_code,
1703 puci.city_code,
1704 pus.state_name,
1705 puc.county_name,
1706 puci.city_name
1707 from PAY_US_CITY_NAMES puci,
1708 PAY_US_COUNTIES puc,
1709 PAY_US_STATES pus,
1710 HR_LOCATIONS hrl,
1711 HR_SOFT_CODING_KEYFLEX hscf,
1712 PER_ASSIGNMENTS_F paf
1713 where paf.assignment_id = p_assignment_id
1714 and p_session_date between paf.effective_start_date and
1715 paf.effective_end_date
1716 and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1717 and nvl(hscf.segment18,paf.location_id) = hrl.location_id
1718 and pus.state_abbrev = nvl(hrl.loc_information17,hrl.region_2)
1719 and puc.state_code = pus.state_code
1720 and puc.county_name = nvl(hrl.loc_information19,hrl.region_1)
1721 and puci.state_code = pus.state_code
1722 and puci.county_code = puc.county_code
1723 and puci.city_name = nvl(hrl.loc_information18,hrl.town_or_city);
1724
1725 begin
1726
1727 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',1);
1728
1729 /* Get the work location details */
1730
1731 open csr_get_work;
1732
1733 fetch csr_get_work into p_work_state_code,
1734 p_work_county_code,
1735 p_work_city_code,
1736 p_work_state_name,
1737 p_work_county_name,
1738 p_work_city_name;
1739
1740 if csr_get_work%NOTFOUND then
1741
1742 p_work_state_code := null;
1743 p_work_county_code := null;
1744 p_work_city_code := null;
1745 p_work_state_name := null;
1746 p_work_county_name := null;
1747 p_work_city_name := null;
1748
1749 end if;
1750
1751 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',3);
1752
1753 close csr_get_work;
1754
1755 end get_work_codes;
1756
1757
1758 /* Name : check_jurisdiction_exists
1759 Purpose : To check if the tax record already exists for a jurisdcition
1760 or not
1761 */
1762
1763 procedure check_jurisdiction_exists (p_assignment_id in number,
1764 p_jurisdiction_code in varchar2,
1765 p_ret_code in out NOCOPY number,
1766 p_ret_text in out NOCOPY varchar2) is
1767
1768
1769 l_code number;
1770
1771
1772 /* Cursor to check if age record already exists for a jurisdiction.
1773 Since a %age tax record cannot exist without a tax rule record,
1774 doing a check on the tax rule tables will will be same as doing
1775 a check on the element entries table. */
1776
1777 cursor csr_check_state (p_jurisdiction varchar2) is
1778 select 1
1779 from PAY_US_EMP_STATE_TAX_RULES_F str
1780 where str.assignment_id = p_assignment_id
1781 and str.jurisdiction_code = p_jurisdiction;
1782
1783 cursor csr_check_county (p_jurisdiction varchar2) is
1784 select 1
1785 from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
1786 where ctr.assignment_id = p_assignment_id
1787 and ctr.jurisdiction_code = p_jurisdiction;
1788
1789 cursor csr_check_city (p_jurisdiction varchar2) is
1790 select 1
1791 from PAY_US_EMP_CITY_TAX_RULES_F ctr
1792 where ctr.assignment_id = p_assignment_id
1793 and ctr.jurisdiction_code = p_jurisdiction;
1794
1795 begin
1796
1797 /* state */
1798 if substr(p_jurisdiction_code,3,9) = '-000-0000' then
1799
1800 open csr_check_state(p_jurisdiction_code);
1801 fetch csr_check_state into l_code ;
1802 if csr_check_state%NOTFOUND then
1803 p_ret_code := 1;
1804 p_ret_text := '%age record not found ';
1805 else
1806 p_ret_code := 0;
1807 p_ret_text := '%age record found ';
1808 end if;
1809 close csr_check_state;
1810
1811 /* County */
1812 elsif substr(p_jurisdiction_code,3,4) <> '-000' and
1813 substr(p_jurisdiction_code,7,5) = '-0000' then
1814
1815 open csr_check_county(p_jurisdiction_code);
1816 fetch csr_check_county into l_code;
1817 if csr_check_county%NOTFOUND then
1818 p_ret_code := 1;
1819 p_ret_text := '%age record not found ';
1820 else
1821 p_ret_code := 0;
1822 p_ret_text := '%age record found ';
1823 end if;
1824 close csr_check_county;
1825
1826 /* City */
1827 else
1828
1829 open csr_check_city(p_jurisdiction_code);
1830 fetch csr_check_city into l_code;
1831 if csr_check_city%NOTFOUND then
1832 p_ret_code := 1;
1833 p_ret_text := '%age record not found ';
1834 else
1835 p_ret_code := 0;
1836 p_ret_text := '%age record found ';
1837 end if;
1838 close csr_check_city;
1839
1840 end if;
1841
1842 end check_jurisdiction_exists;
1843
1844
1845 procedure check_delete_tax_row ( p_assignment_id in number,
1846 p_state_code in varchar2,
1847 p_county_code in varchar2,
1848 p_city_code in varchar2) is
1849
1850 l_ret_code number;
1851 l_ret_text varchar2(240);
1852 l_jurisdiction_code varchar2(11);
1853 l_effective_start_date date;
1854 l_payroll_installed boolean := FALSE;
1855
1856 begin
1857
1858 /* Check if payroll has been installed or not */
1859
1860 l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1861 p_legislation => 'US');
1862
1863 /* Check if the state/county/city has been assigned to a
1864 work location */
1865
1866 l_ret_code := 0;
1867 l_ret_text := null;
1868
1869 pay_us_emp_dt_tax_val.check_in_work_location (
1870 p_assignment_id => p_assignment_id,
1871 p_state_code => p_state_code,
1872 p_county_code => p_county_code,
1873 p_city_code => p_city_code,
1874 p_ret_code => l_ret_code,
1875 p_ret_text => l_ret_text);
1876
1877 /* begin modifications - dscully 21-JUN-2000 */
1878
1879 if l_ret_code = 1 then
1880
1881 if p_state_code is not null and p_county_code is null
1882 and p_city_code is null then
1883
1884 fnd_message.set_name('PAY', 'PAY_52293_TAX_STDEL_LOC');
1885 fnd_message.raise_error;
1886
1887 elsif p_state_code is not null and p_county_code is not null
1888 and p_city_code is null then
1889
1890 fnd_message.set_name('PAY', 'PAY_52294_TAX_CODEL_LOC');
1891 fnd_message.raise_error;
1892
1893 elsif p_state_code is not null and p_county_code is not null
1894 and p_city_code is not null then
1895
1896 fnd_message.set_name('PAY', 'PAY_52295_TAX_CIEL_LOC');
1897 fnd_message.raise_error;
1898
1899 end if;
1900
1901 elsif l_ret_code = 2 then
1902
1903 if p_state_code is not null and p_county_code is null
1904 and p_city_code is null then
1905
1906 fnd_message.set_name('PAY', 'PAY_76860_TAX_STDEL_TAX_LOC');
1907 fnd_message.raise_error;
1908
1909 elsif p_state_code is not null and p_county_code is not null
1910 and p_city_code is null then
1911
1912 fnd_message.set_name('PAY', 'PAY_76861_TAX_CODEL_TAX_LOC');
1913 fnd_message.raise_error;
1914
1915 elsif p_state_code is not null and p_county_code is not null
1916 and p_city_code is not null then
1917
1918 fnd_message.set_name('PAY', 'PAY_76862_TAX_CIDEL_TAX_LOC');
1919 fnd_message.raise_error;
1920
1921 end if;
1922
1923 end if;
1924
1925
1926 /* end modifications - dscully 21-JUN-2000 */
1927
1928 /* Check if the state/county/city has been assigned to
1929 the resident address */
1930
1931 l_ret_code := 0;
1932 l_ret_text := null;
1933
1934 pay_us_emp_dt_tax_val.check_in_res_addr (
1935 p_assignment_id => p_assignment_id,
1936 p_state_code => p_state_code,
1937 p_county_code => p_county_code,
1938 p_city_code => p_city_code,
1939 p_ret_code => l_ret_code,
1940 p_ret_text => l_ret_text);
1941
1942 if l_ret_code <> 0 then
1943
1944 if p_state_code is not null and p_county_code is null
1945 and p_city_code is null then
1946
1947 /* fnd_message.set_name('Cannot delete. State assigned to resident address') */
1948 fnd_message.set_name('PAY', 'PAY_52296_TAX_STDEL_RES');
1949 fnd_message.raise_error;
1950
1951 elsif p_state_code is not null and p_county_code is not null
1952 and p_city_code is null then
1953
1954 /* fnd_message.set_name('Cannot delete. County assigned to resident address') */
1955 fnd_message.set_name('PAY', 'PAY_52297_TAX_CODEL_RES');
1956 fnd_message.raise_error;
1957
1958 elsif p_state_code is not null and p_county_code is not null
1959 and p_city_code is not null then
1960
1961 /* fnd_message.set_name('Cannot delete. City assigned to resident address') */
1962 fnd_message.set_name('PAY', 'PAY_52298_TAX_CIDEL_RES');
1963 fnd_message.raise_error;
1964
1965 end if;
1966
1967 end if;
1968
1969 /* Check if payroll has been run for the state/county/city */
1970
1971 if l_payroll_installed then
1972
1973 l_ret_code := 0;
1974 l_ret_text := null;
1975
1976 pay_us_emp_dt_tax_val.payroll_check_for_purge (
1977 p_assignment_id => p_assignment_id,
1978 p_state_code => p_state_code,
1979 p_county_code => p_county_code,
1980 p_city_code => p_city_code,
1981 p_ret_code => l_ret_code,
1982 p_ret_text => l_ret_text);
1983
1984 if l_ret_code <> 0 then
1985
1986 if p_state_code is not null and p_county_code is null
1987 and p_city_code is null then
1988
1989 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
1990 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
1991 fnd_message.raise_error;
1992
1993 elsif p_state_code is not null and p_county_code is not null
1994 and p_city_code is null then
1995
1996 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
1997 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
1998 fnd_message.raise_error;
1999
2000 elsif p_state_code is not null and p_county_code is not null
2001 and p_city_code is not null then
2002
2003 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
2004 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
2005 fnd_message.raise_error;
2006
2007 end if;
2008
2009 end if;
2010
2011 end if;
2012
2013 end check_delete_tax_row;
2014
2015 /* Name : get_all_work_codes
2016 Purpose : To get the work state code, work state name, work city code,
2017 work city name, work county code,work county name, override work
2018 state code, override work state name, override work city code,
2019 override work city name, override work county code and override
2020 work county name. */
2021
2022 procedure get_all_work_codes (p_assignment_id in number,
2023 p_session_date in date,
2024 p_work_state_code in out NOCOPY varchar2,
2025 p_work_county_code in out NOCOPY varchar2,
2026 p_work_city_code in out NOCOPY varchar2,
2027 p_work_state_name in out NOCOPY varchar2,
2028 p_work_county_name in out NOCOPY varchar2,
2029 p_work_city_name in out NOCOPY varchar2,
2030 p_work1_state_code in out NOCOPY varchar2,
2031 p_work1_county_code in out NOCOPY varchar2,
2032 p_work1_city_code in out NOCOPY varchar2,
2033 p_work1_state_name in out NOCOPY varchar2,
2034 p_work1_county_name in out NOCOPY varchar2,
2035 p_work1_city_name in out NOCOPY varchar2,
2036 p_work2_state_code in out NOCOPY varchar2,
2037 p_work2_county_code in out NOCOPY varchar2,
2038 p_work2_city_code in out NOCOPY varchar2,
2039 p_work2_state_name in out NOCOPY varchar2,
2040 p_work2_county_name in out NOCOPY varchar2,
2041 p_work2_city_name in out NOCOPY varchar2,
2042 p_work3_state_code in out NOCOPY varchar2,
2043 p_work3_county_code in out NOCOPY varchar2,
2044 p_work3_city_code in out NOCOPY varchar2,
2045 p_work3_state_name in out NOCOPY varchar2,
2046 p_work3_county_name in out NOCOPY varchar2,
2047 p_work3_city_name in out NOCOPY varchar2,
2048 p_sui_state_code in out NOCOPY varchar2,
2049 p_loc_city in out NOCOPY varchar2) is
2050
2051
2052
2053 /* Override Assignment Data */
2054 cursor csr_get_asgn_locations is
2055 select paf.location_id, hsc.segment18
2056 from HR_SOFT_CODING_KEYFLEX hsc,
2057 PER_ASSIGNMENTS_F paf
2058 where paf.assignment_id = p_assignment_id
2059 and p_session_date between paf.effective_start_date and
2060 paf.effective_end_date
2061 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
2062
2063 cursor csr_get_work_location(cp_location_id number) is
2064 select pus.state_code,
2065 puc.county_code,
2066 puci.city_code,
2067 pus.state_name,
2068 puc.county_name,
2069 puci.city_name
2070 from PAY_US_CITY_NAMES puci,
2071 PAY_US_COUNTIES puc,
2072 PAY_US_STATES pus,
2073 HR_LOCATIONS hrl
2074 where hrl.location_id = cp_location_id
2075 and pus.state_abbrev = hrl.region_2
2076 and puc.state_code = pus.state_code
2077 and puc.county_name = hrl.region_1
2078 and puci.state_code = pus.state_code
2079 and puci.county_code = puc.county_code
2080 and puci.city_name = hrl.town_or_city;
2081
2082 cursor csr_get_override_work_location(cp_location_id number) is
2083 select pus.state_code,
2084 puc.county_code,
2085 puci.city_code,
2086 pus.state_name,
2087 puc.county_name,
2088 puci.city_name
2089 from PAY_US_CITY_NAMES puci,
2090 PAY_US_COUNTIES puc,
2091 PAY_US_STATES pus,
2092 HR_LOCATIONS hrl
2093 where hrl.location_id = cp_location_id
2094 and pus.state_abbrev = hrl.loc_information17
2095 and puc.state_code = pus.state_code
2096 and puc.county_name = hrl.loc_information19
2097 and puci.state_code = pus.state_code
2098 and puci.county_code = puc.county_code
2099 and puci.city_name = hrl.loc_information18;
2100
2101 l_work_location_id number;
2102 l_ovrd_location_id number;
2103
2104 begin
2105
2106 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',1);
2107 /* Primary Work Address */
2108 p_work_state_code := null;
2109 p_work_county_code := null;
2110 p_work_city_code := null;
2111 p_work_state_name := null;
2112 p_work_county_name := null;
2113 p_work_city_name := null;
2114
2115 /* Primary Work Override Address */
2116 p_work1_state_code := null;
2117 p_work1_county_code := null;
2118 p_work1_city_code := null;
2119 p_work1_state_name := null;
2120 p_work1_county_name := null;
2121 p_work1_city_name := null;
2122
2123 /* Override Work Address */
2124 p_work2_state_code := null;
2125 p_work2_county_code := null;
2126 p_work2_city_code := null;
2127 p_work2_state_name := null;
2128 p_work2_county_name := null;
2129 p_work2_city_name := null;
2130
2131 /* Override Override Work Address */
2132 p_work3_state_code := null;
2133 p_work3_county_code := null;
2134 p_work3_city_code := null;
2135 p_work3_state_name := null;
2136 p_work3_county_name := null;
2137 p_work3_city_name := null;
2138
2139 /* Get Assignment Locations */
2140 open csr_get_asgn_locations;
2141 fetch csr_get_asgn_locations into
2142 l_work_location_id,
2143 l_ovrd_location_id;
2144
2145 /* Get the work location details */
2146 if csr_get_asgn_locations%found then
2147
2148 /* Primary Location is found, so get the Primary Work Details
2149 and Override details for that location (if any). */
2150 if l_work_location_id is not null then
2151
2152 open csr_get_work_location(l_work_location_id);
2153 fetch csr_get_work_location into
2154 /* Primary Work Address */
2155 p_work_state_code,
2156 p_work_county_code,
2157 p_work_city_code,
2158 p_work_state_name,
2159 p_work_county_name,
2160 p_work_city_name;
2161 close csr_get_work_location;
2162
2163 open csr_get_override_work_location(l_work_location_id);
2164 fetch csr_get_override_work_location into
2165 /* Primary Work Override Address */
2166 p_work1_state_code,
2167 p_work1_county_code,
2168 p_work1_city_code,
2169 p_work1_state_name,
2170 p_work1_county_name,
2171 p_work1_city_name;
2172 close csr_get_override_work_location;
2173
2174 /* Checking if the Work and Override are same. In Case they are
2175 setting the paramaters for the Overide Location to NULL */
2176 if p_work_state_code = p_work1_state_code and
2177 p_work_county_code = p_work1_county_code and
2178 p_work_city_code = p_work1_city_code then
2179 p_work1_state_code := null;
2180 p_work1_state_name := null;
2181 p_work1_county_code := null;
2182 p_work1_county_name := null;
2183 p_work1_city_code := null;
2184 p_work1_city_name := null;
2185 end if;
2186
2187 end if; /* Primary Location was found for the Assignment */
2188
2189
2190 /* Override Location is found, so get the Override Work Details
2191 and Override details for that Override Location (if any). */
2192 if l_ovrd_location_id is not null then
2193
2194 open csr_get_work_location(l_ovrd_location_id);
2195 fetch csr_get_work_location into
2196 /* Override Work Address */
2197 p_work2_state_code,
2201 p_work2_county_name,
2198 p_work2_county_code,
2199 p_work2_city_code,
2200 p_work2_state_name,
2202 p_work2_city_name;
2203
2204 close csr_get_work_location;
2205
2206 open csr_get_override_work_location(l_ovrd_location_id);
2207 fetch csr_get_override_work_location into
2208 /* Override Work Override Address */
2209 p_work3_state_code,
2210 p_work3_county_code,
2211 p_work3_city_code,
2212 p_work3_state_name,
2213 p_work3_county_name,
2214 p_work3_city_name;
2215
2216 close csr_get_override_work_location;
2217
2218 /* Checking if the Work Override and Override Override are same.
2219 In Case they are setting the paramaters for the Overide
2220 Overide Location to NULL */
2221 if p_work2_state_code = p_work3_state_code and
2222 p_work2_county_code = p_work3_county_code and
2223 p_work2_city_code = p_work3_city_code then
2224 p_work3_state_code := null;
2225 p_work3_state_name := null;
2226 p_work3_county_code := null;
2227 p_work3_county_name := null;
2228 p_work3_city_code := null;
2229 p_work3_city_name := null;
2230 end if;
2231
2232 end if; /* Primary Location was found for the Assignment */
2233
2234 end if; /* Assignment Record Found */
2235 close csr_get_asgn_locations;
2236
2237 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',3);
2238 if p_work3_state_code is not null then
2239 p_sui_state_code := p_work3_state_code;
2240 elsif p_work2_state_code is not null then
2241 p_sui_state_code := p_work2_state_code;
2242 elsif p_work1_state_code is not null then
2243 p_sui_state_code := p_work1_state_code;
2244 elsif p_work_state_code is not null then
2245 p_sui_state_code := p_work_state_code;
2246 end if;
2247
2248 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',4);
2249
2250 if p_work3_city_code is not null then
2251 p_loc_city := p_work3_state_code || '-' || p_work3_county_code || '-'||p_work3_city_code;
2252 elsif p_work2_city_code is not null then
2253 p_loc_city := p_work2_state_code || '-' || p_work2_county_code || '-'||p_work2_city_code;
2254 elsif p_work1_city_code is not null then
2255 p_loc_city := p_work1_state_code || '-' || p_work1_county_code || '-'||p_work1_city_code;
2256 elsif p_work_city_code is not null then
2257 p_loc_city := p_work_state_code || '-' || p_work_county_code || '-'||p_work_city_code;
2258 end if;
2259
2260 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',5);
2261 end get_all_work_codes;
2262
2263 procedure get_orig_res_codes (p_assignment_id in number,
2264 p_session_date in date,
2265 p_res_state_code out NOCOPY varchar2,
2266 p_res_county_code out NOCOPY varchar2,
2267 p_res_city_code out NOCOPY varchar2,
2268 p_res_state_name out NOCOPY varchar2,
2269 p_res_county_name out NOCOPY varchar2,
2270 p_res_city_name out NOCOPY varchar2) is
2271
2272 /* Cursor to get the resident state, county and city codes */
2273 cursor csr_get_res is
2274 select pus.state_code,
2275 puc.county_code,
2276 puci.city_code,
2277 pus.state_name,
2278 puc.county_name,
2279 puci.city_name
2280 from PAY_US_CITY_NAMES puci,
2281 PAY_US_COUNTIES puc,
2282 PAY_US_STATES pus,
2283 PER_ADDRESSES pa,
2284 PER_ASSIGNMENTS_F paf
2285 where paf.assignment_id = p_assignment_id
2286 and p_session_date between paf.effective_start_date and
2287 paf.effective_end_date
2288 and pa.person_id = paf.person_id
2289 and pa.primary_flag = 'Y'
2290 and p_session_date between pa.date_from and
2291 nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
2292 and pus.state_abbrev = pa.region_2
2293 and puc.state_code = pus.state_code
2294 and puc.county_name = pa.region_1
2295 and puci.state_code = pus.state_code
2296 and puci.county_code = puc.county_code
2297 and puci.city_name = pa.town_or_city;
2298
2299 begin
2300
2301 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_orig_res_codes',1);
2302
2303 /* Get the resident address details */
2304
2305 open csr_get_res;
2306
2307 fetch csr_get_res into p_res_state_code,
2308 p_res_county_code,
2309 p_res_city_code,
2310 p_res_state_name,
2311 p_res_county_name,
2312 p_res_city_name;
2313
2314 if csr_get_res%NOTFOUND then
2315
2316 p_res_state_code := null;
2317 p_res_county_code := null;
2318 p_res_city_code := null;
2319 p_res_state_name := null;
2320 p_res_county_name := null;
2321 p_res_city_name := null;
2322
2323 end if;
2324
2325 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_orig_res_codes',2);
2326
2327 close csr_get_res;
2328
2329 end get_orig_res_codes;
2330 end pay_us_emp_dt_tax_val;
[Home] [Help]