1 PACKAGE BODY pay_us_emp_dt_tax_val AS
2 /* $Header: pyusdtvl.pkb 120.2.12000000.1 2007/01/18 02:19:43 appldev noship $ */
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:
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
54 and NOCOPY hint for GSCC compliance.
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
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)
428 )
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.
494 accordingly which will be used to raise warning message
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
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
564 cursor c_get_state_code(p_location_id in number) is
565 select pus.state_code
566 from pay_us_states pus,
567 hr_locations hl
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;
625 end if; /* end checking of DELETE_NEXT_CHANGE */
622 end if;
623 close csr_get_next_locations;
624 end if;
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,
742
739 p_city_code in varchar2,
740 p_ret_code in out NOCOPY number,
741 p_ret_text in out NOCOPY varchar2) is
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,
858 and puci.city_name = hrl.loc_information18)
855 PAY_US_STATES pus
856 where ((pus.state_abbrev = hrl.loc_information17
857 and puco.county_name = hrl.loc_information19
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
864 and puco.state_code = pus.state_code
865 and puco.county_code = p_county_code
866 and puci.state_code = pus.state_code
867 and puci.county_code = puco.county_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
1001
998 close csr_check_ovrd_city;
999
1000 end if;
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.
1021 */
1022
1023
1024 procedure check_in_res_addr ( p_assignment_id in number,
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;
1130
1127 p_ret_text := 'State assigned to resident address.';
1128
1129 else
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
1162 hr_utility.set_location(
1163 'pay_us_emp_tax_rules.check_in_res_addr' ||
1164 ' - found work location',7);
1165
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 ;
1274 pay_assignment_actions paa
1271
1272 /* select 1
1273 from pay_run_results prr,
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
1304 from pay_run_results prr,
1305 pay_assignment_actions paa
1306 where substr(prr.jurisdiction_code,1,6) = p_state_code || '-'||
1307 p_county_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
1404 'pay_us_emp_tax_rules.payroll_check_for_purge'||
1401 open csr_check_county_purge;
1402
1403 hr_utility.set_location(
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(
1452 'pay_us_emp_tax_rules.payroll_check_for_purge' ||
1453 ' - found work location',11);
1454
1455 p_ret_code := 1;
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
1498 cursor chk_upd_sd is
1499 select 1
1500 from PAY_US_EMP_COUNTY_TAX_RULES_F pcf
1501 where pcf.assignment_id = p_assignment
1502 and pcf.school_district_code is not null
1503 and pcf.effective_end_date >= p_start_date
1504 and pcf.effective_start_date <= p_end_date
1505 and rowid <> chartorowid(p_rowid)
1506 UNION ALL
1507 select 1
1508 from PAY_US_EMP_CITY_TAX_RULES_F pcif
1509 where pcif.assignment_id = p_assignment
1510 and pcif.school_district_code is not null
1511 and pcif.effective_end_date >= p_start_date
1512 and pcif.effective_start_date <= p_end_date
1513 and rowid <> chartorowid(p_rowid);
1514 begin
1515
1516 if p_mode = 'I' then
1517
1518 open chk_ins_sd;
1519 fetch chk_ins_sd into l_code;
1520
1521 if chk_ins_sd%FOUND then
1522 fnd_message.set_name ('PAY', 'PAY_52300_TAX_SD_ASGN');
1523 fnd_message.raise_error;
1524 end if;
1525
1526 close chk_ins_sd;
1527
1528 elsif p_mode = 'U' then
1529
1530 open chk_upd_sd;
1531 fetch chk_upd_sd into l_code;
1532
1533 if chk_upd_sd%FOUND then
1534 fnd_message.set_name ('PAY', 'PAY_52300_TAX_SD_ASGN');
1535 fnd_message.raise_error;
1536 end if;
1537
1538 close chk_upd_sd;
1539
1540 end if;
1541
1542
1543 end check_school_district;
1544
1545 function check_locations ( p_assignment_id in number,
1546 p_effective_start_date in date,
1547 p_business_group_id in number) return boolean is
1548
1549 cursor csr_get_curr_loc is
1550 select location_id,
1551 effective_start_date,
1552 effective_end_date
1553 from per_assignments_f
1554 where assignment_id = p_assignment_id
1555 and business_group_id + 0 = p_business_group_id
1559 cursor csr_get_future_locations (p_validation_date date)is
1556 and p_effective_start_date between effective_start_date
1557 and effective_end_date;
1558
1560 select location_id
1561 from per_assignments_f
1562 where assignment_id = p_assignment_id
1563 and business_group_id + 0 = p_business_group_id
1564 and effective_start_date > p_validation_date;
1565
1566 l_curr_loc_id number;
1567 l_next_loc_id number;
1568 l_curr_eff_start_dt date;
1569 l_curr_eff_end_dt date;
1570 l_found boolean;
1571
1572 begin
1573
1574 open csr_get_curr_loc;
1575
1576 fetch csr_get_curr_loc into l_curr_loc_id,
1577 l_curr_eff_start_dt,
1578 l_curr_eff_end_dt;
1579
1580 if csr_get_curr_loc%NOTFOUND then
1581 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1582 fnd_message.set_token('PROCEDURE',
1583 'pay_us_emp_dt_tax_rules.check_locations');
1584 fnd_message.set_token('STEP','1');
1585 fnd_message.raise_error;
1586 close csr_get_curr_loc;
1587
1588 end if;
1589
1590 close csr_get_curr_loc;
1591
1592 l_found := FALSE;
1593
1594 open csr_get_future_locations(l_curr_eff_end_dt);
1595
1596 loop
1597 fetch csr_get_future_locations into l_next_loc_id;
1598 exit when csr_get_future_locations%NOTFOUND;
1599 if l_next_loc_id <> l_curr_loc_id then
1600 l_found := TRUE;
1601 exit;
1602 end if;
1603 end loop;
1604
1605 close csr_get_future_locations;
1606
1607 return l_found;
1608
1609 end check_locations;
1610
1611
1612 procedure get_res_codes (p_assignment_id in number,
1613 p_session_date in date,
1614 p_res_state_code out NOCOPY varchar2,
1615 p_res_county_code out NOCOPY varchar2,
1616 p_res_city_code out NOCOPY varchar2,
1617 p_res_state_name out NOCOPY varchar2,
1618 p_res_county_name out NOCOPY varchar2,
1619 p_res_city_name out NOCOPY varchar2) is
1620
1621 /* Cursor to get the resident state, county and city codes */
1622 cursor csr_get_res is
1623 select pus.state_code,
1624 puc.county_code,
1625 puci.city_code,
1626 pus.state_name,
1627 puc.county_name,
1628 puci.city_name
1629 from PAY_US_CITY_NAMES puci,
1630 PAY_US_COUNTIES puc,
1631 PAY_US_STATES pus,
1632 PER_ADDRESSES pa,
1633 PER_ASSIGNMENTS_F paf
1634 where paf.assignment_id = p_assignment_id
1635 and p_session_date between paf.effective_start_date and
1636 paf.effective_end_date
1637 and pa.person_id = paf.person_id
1638 and pa.primary_flag = 'Y'
1639 and p_session_date between pa.date_from and
1640 nvl(pa.date_to,p_session_date)
1641 and pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
1642 and puc.state_code = pus.state_code
1643 and puc.county_name = nvl(pa.add_information19,pa.region_1)
1644 and puci.state_code = pus.state_code
1645 and puci.county_code = puc.county_code
1646 and puci.city_name = nvl(pa.add_information18,pa.town_or_city);
1647
1648 begin
1649
1650 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_res_work',1);
1651
1652 /* Get the resident address details */
1653
1654 open csr_get_res;
1655
1656 fetch csr_get_res into p_res_state_code,
1657 p_res_county_code,
1658 p_res_city_code,
1659 p_res_state_name,
1660 p_res_county_name,
1661 p_res_city_name;
1662
1663 if csr_get_res%NOTFOUND then
1664
1665 p_res_state_code := null;
1666 p_res_county_code := null;
1667 p_res_city_code := null;
1668 p_res_state_name := null;
1669 p_res_county_name := null;
1670 p_res_city_name := null;
1671
1672 end if;
1673
1674 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_res_work',2);
1675
1676 close csr_get_res;
1677
1678 end get_res_codes;
1679
1680
1681 /* Name : get_work_codes
1682 Purpose : To get the work state code, work state name, work city code,
1683 work city name, work county code and work county name. This
1684 procedure will also be called by the tax form PAYEETAX.fmb,
1685 to get the names of the wok state and localities */
1686
1687 procedure get_work_codes (p_assignment_id in number,
1688 p_session_date in date,
1689 p_work_state_code out NOCOPY varchar2,
1690 p_work_county_code out NOCOPY varchar2,
1691 p_work_city_code out NOCOPY varchar2,
1695
1692 p_work_state_name out NOCOPY varchar2,
1693 p_work_county_name out NOCOPY varchar2,
1694 p_work_city_name out NOCOPY varchar2) is
1696 /* Cursor to get the work state, county and city */
1697
1698 cursor csr_get_work is
1699 select pus.state_code,
1700 puc.county_code,
1701 puci.city_code,
1702 pus.state_name,
1703 puc.county_name,
1704 puci.city_name
1705 from PAY_US_CITY_NAMES puci,
1706 PAY_US_COUNTIES puc,
1707 PAY_US_STATES pus,
1708 HR_LOCATIONS hrl,
1709 HR_SOFT_CODING_KEYFLEX hscf,
1710 PER_ASSIGNMENTS_F paf
1711 where paf.assignment_id = p_assignment_id
1712 and p_session_date between paf.effective_start_date and
1713 paf.effective_end_date
1714 and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
1715 and nvl(hscf.segment18,paf.location_id) = hrl.location_id
1716 and pus.state_abbrev = nvl(hrl.loc_information17,hrl.region_2)
1717 and puc.state_code = pus.state_code
1718 and puc.county_name = nvl(hrl.loc_information19,hrl.region_1)
1719 and puci.state_code = pus.state_code
1720 and puci.county_code = puc.county_code
1721 and puci.city_name = nvl(hrl.loc_information18,hrl.town_or_city);
1722
1723 begin
1724
1725 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',1);
1726
1727 /* Get the work location details */
1728
1729 open csr_get_work;
1730
1731 fetch csr_get_work into p_work_state_code,
1732 p_work_county_code,
1733 p_work_city_code,
1734 p_work_state_name,
1735 p_work_county_name,
1736 p_work_city_name;
1737
1738 if csr_get_work%NOTFOUND then
1739
1740 p_work_state_code := null;
1741 p_work_county_code := null;
1742 p_work_city_code := null;
1743 p_work_state_name := null;
1744 p_work_county_name := null;
1745 p_work_city_name := null;
1746
1747 end if;
1748
1749 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',3);
1750
1751 close csr_get_work;
1752
1753 end get_work_codes;
1754
1755
1756 /* Name : check_jurisdiction_exists
1757 Purpose : To check if the tax record already exists for a jurisdcition
1758 or not
1759 */
1760
1761 procedure check_jurisdiction_exists (p_assignment_id in number,
1762 p_jurisdiction_code in varchar2,
1763 p_ret_code in out NOCOPY number,
1764 p_ret_text in out NOCOPY varchar2) is
1765
1766
1767 l_code number;
1768
1769
1770 /* Cursor to check if age record already exists for a jurisdiction.
1771 Since a %age tax record cannot exist without a tax rule record,
1772 doing a check on the tax rule tables will will be same as doing
1773 a check on the element entries table. */
1774
1775 cursor csr_check_state (p_jurisdiction varchar2) is
1776 select 1
1777 from PAY_US_EMP_STATE_TAX_RULES_F str
1778 where str.assignment_id = p_assignment_id
1779 and str.jurisdiction_code = p_jurisdiction;
1780
1781 cursor csr_check_county (p_jurisdiction varchar2) is
1782 select 1
1783 from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
1784 where ctr.assignment_id = p_assignment_id
1785 and ctr.jurisdiction_code = p_jurisdiction;
1786
1787 cursor csr_check_city (p_jurisdiction varchar2) is
1788 select 1
1789 from PAY_US_EMP_CITY_TAX_RULES_F ctr
1790 where ctr.assignment_id = p_assignment_id
1791 and ctr.jurisdiction_code = p_jurisdiction;
1792
1793 begin
1794
1795 /* state */
1796 if substr(p_jurisdiction_code,3,9) = '-000-0000' then
1797
1798 open csr_check_state(p_jurisdiction_code);
1799 fetch csr_check_state into l_code ;
1800 if csr_check_state%NOTFOUND then
1801 p_ret_code := 1;
1802 p_ret_text := '%age record not found ';
1803 else
1804 p_ret_code := 0;
1805 p_ret_text := '%age record found ';
1806 end if;
1807 close csr_check_state;
1808
1809 /* County */
1810 elsif substr(p_jurisdiction_code,3,4) <> '-000' and
1811 substr(p_jurisdiction_code,7,5) = '-0000' then
1812
1813 open csr_check_county(p_jurisdiction_code);
1814 fetch csr_check_county into l_code;
1815 if csr_check_county%NOTFOUND then
1816 p_ret_code := 1;
1817 p_ret_text := '%age record not found ';
1818 else
1819 p_ret_code := 0;
1820 p_ret_text := '%age record found ';
1821 end if;
1822 close csr_check_county;
1823
1824 /* City */
1825 else
1826
1827 open csr_check_city(p_jurisdiction_code);
1828 fetch csr_check_city into l_code;
1829 if csr_check_city%NOTFOUND then
1833 p_ret_code := 0;
1830 p_ret_code := 1;
1831 p_ret_text := '%age record not found ';
1832 else
1834 p_ret_text := '%age record found ';
1835 end if;
1836 close csr_check_city;
1837
1838 end if;
1839
1840 end check_jurisdiction_exists;
1841
1842
1843 procedure check_delete_tax_row ( p_assignment_id in number,
1844 p_state_code in varchar2,
1845 p_county_code in varchar2,
1846 p_city_code in varchar2) is
1847
1848 l_ret_code number;
1849 l_ret_text varchar2(240);
1850 l_jurisdiction_code varchar2(11);
1851 l_effective_start_date date;
1852 l_payroll_installed boolean := FALSE;
1853
1854 begin
1855
1856 /* Check if payroll has been installed or not */
1857
1858 l_payroll_installed := hr_utility.chk_product_install(p_product =>'Oracle Payroll',
1859 p_legislation => 'US');
1860
1861 /* Check if the state/county/city has been assigned to a
1862 work location */
1863
1864 l_ret_code := 0;
1865 l_ret_text := null;
1866
1867 pay_us_emp_dt_tax_val.check_in_work_location (
1868 p_assignment_id => p_assignment_id,
1869 p_state_code => p_state_code,
1870 p_county_code => p_county_code,
1871 p_city_code => p_city_code,
1872 p_ret_code => l_ret_code,
1873 p_ret_text => l_ret_text);
1874
1875 /* begin modifications - dscully 21-JUN-2000 */
1876
1877 if l_ret_code = 1 then
1878
1879 if p_state_code is not null and p_county_code is null
1880 and p_city_code is null then
1881
1882 fnd_message.set_name('PAY', 'PAY_52293_TAX_STDEL_LOC');
1883 fnd_message.raise_error;
1884
1885 elsif p_state_code is not null and p_county_code is not null
1886 and p_city_code is null then
1887
1888 fnd_message.set_name('PAY', 'PAY_52294_TAX_CODEL_LOC');
1889 fnd_message.raise_error;
1890
1891 elsif p_state_code is not null and p_county_code is not null
1892 and p_city_code is not null then
1893
1894 fnd_message.set_name('PAY', 'PAY_52295_TAX_CIEL_LOC');
1895 fnd_message.raise_error;
1896
1897 end if;
1898
1899 elsif l_ret_code = 2 then
1900
1901 if p_state_code is not null and p_county_code is null
1902 and p_city_code is null then
1903
1904 fnd_message.set_name('PAY', 'PAY_76860_TAX_STDEL_TAX_LOC');
1905 fnd_message.raise_error;
1906
1907 elsif p_state_code is not null and p_county_code is not null
1908 and p_city_code is null then
1909
1910 fnd_message.set_name('PAY', 'PAY_76861_TAX_CODEL_TAX_LOC');
1911 fnd_message.raise_error;
1912
1913 elsif p_state_code is not null and p_county_code is not null
1914 and p_city_code is not null then
1915
1916 fnd_message.set_name('PAY', 'PAY_76862_TAX_CIDEL_TAX_LOC');
1917 fnd_message.raise_error;
1918
1919 end if;
1920
1921 end if;
1922
1923
1924 /* end modifications - dscully 21-JUN-2000 */
1925
1926 /* Check if the state/county/city has been assigned to
1927 the resident address */
1928
1929 l_ret_code := 0;
1930 l_ret_text := null;
1931
1932 pay_us_emp_dt_tax_val.check_in_res_addr (
1933 p_assignment_id => p_assignment_id,
1934 p_state_code => p_state_code,
1935 p_county_code => p_county_code,
1936 p_city_code => p_city_code,
1937 p_ret_code => l_ret_code,
1938 p_ret_text => l_ret_text);
1939
1940 if l_ret_code <> 0 then
1941
1942 if p_state_code is not null and p_county_code is null
1943 and p_city_code is null then
1944
1945 /* fnd_message.set_name('Cannot delete. State assigned to resident address') */
1946 fnd_message.set_name('PAY', 'PAY_52296_TAX_STDEL_RES');
1947 fnd_message.raise_error;
1948
1949 elsif p_state_code is not null and p_county_code is not null
1950 and p_city_code is null then
1951
1952 /* fnd_message.set_name('Cannot delete. County assigned to resident address') */
1953 fnd_message.set_name('PAY', 'PAY_52297_TAX_CODEL_RES');
1954 fnd_message.raise_error;
1955
1956 elsif p_state_code is not null and p_county_code is not null
1957 and p_city_code is not null then
1958
1959 /* fnd_message.set_name('Cannot delete. City assigned to resident address') */
1960 fnd_message.set_name('PAY', 'PAY_52298_TAX_CIDEL_RES');
1964
1961 fnd_message.raise_error;
1962
1963 end if;
1965 end if;
1966
1967 /* Check if payroll has been run for the state/county/city */
1968
1969 if l_payroll_installed then
1970
1971 l_ret_code := 0;
1972 l_ret_text := null;
1973
1974 pay_us_emp_dt_tax_val.payroll_check_for_purge (
1975 p_assignment_id => p_assignment_id,
1976 p_state_code => p_state_code,
1977 p_county_code => p_county_code,
1978 p_city_code => p_city_code,
1979 p_ret_code => l_ret_code,
1980 p_ret_text => l_ret_text);
1981
1982 if l_ret_code <> 0 then
1983
1984 if p_state_code is not null and p_county_code is null
1985 and p_city_code is null then
1986
1987 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
1988 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
1989 fnd_message.raise_error;
1990
1991 elsif p_state_code is not null and p_county_code is not null
1992 and p_city_code is null then
1993
1994 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
1995 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
1996 fnd_message.raise_error;
1997
1998 elsif p_state_code is not null and p_county_code is not null
1999 and p_city_code is not null then
2000
2001 /* fnd_message.set_name('Cannot delete. Payroll has been run ') */
2002 fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
2003 fnd_message.raise_error;
2004
2005 end if;
2006
2007 end if;
2008
2009 end if;
2010
2011 end check_delete_tax_row;
2012
2013 /* Name : get_all_work_codes
2014 Purpose : To get the work state code, work state name, work city code,
2015 work city name, work county code,work county name, override work
2016 state code, override work state name, override work city code,
2017 override work city name, override work county code and override
2018 work county name. */
2019
2020 procedure get_all_work_codes (p_assignment_id in number,
2021 p_session_date in date,
2022 p_work_state_code in out NOCOPY varchar2,
2023 p_work_county_code in out NOCOPY varchar2,
2024 p_work_city_code in out NOCOPY varchar2,
2025 p_work_state_name in out NOCOPY varchar2,
2026 p_work_county_name in out NOCOPY varchar2,
2027 p_work_city_name in out NOCOPY varchar2,
2028 p_work1_state_code in out NOCOPY varchar2,
2029 p_work1_county_code in out NOCOPY varchar2,
2030 p_work1_city_code in out NOCOPY varchar2,
2031 p_work1_state_name in out NOCOPY varchar2,
2032 p_work1_county_name in out NOCOPY varchar2,
2033 p_work1_city_name in out NOCOPY varchar2,
2034 p_work2_state_code in out NOCOPY varchar2,
2035 p_work2_county_code in out NOCOPY varchar2,
2036 p_work2_city_code in out NOCOPY varchar2,
2037 p_work2_state_name in out NOCOPY varchar2,
2038 p_work2_county_name in out NOCOPY varchar2,
2039 p_work2_city_name in out NOCOPY varchar2,
2040 p_work3_state_code in out NOCOPY varchar2,
2041 p_work3_county_code in out NOCOPY varchar2,
2042 p_work3_city_code in out NOCOPY varchar2,
2043 p_work3_state_name in out NOCOPY varchar2,
2044 p_work3_county_name in out NOCOPY varchar2,
2045 p_work3_city_name in out NOCOPY varchar2,
2046 p_sui_state_code in out NOCOPY varchar2,
2047 p_loc_city in out NOCOPY varchar2) is
2048
2049
2050
2051 /* Override Assignment Data */
2052 cursor csr_get_asgn_locations is
2053 select paf.location_id, hsc.segment18
2054 from HR_SOFT_CODING_KEYFLEX hsc,
2055 PER_ASSIGNMENTS_F paf
2056 where paf.assignment_id = p_assignment_id
2057 and p_session_date between paf.effective_start_date and
2058 paf.effective_end_date
2059 and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
2060
2061 cursor csr_get_work_location(cp_location_id number) is
2062 select pus.state_code,
2063 puc.county_code,
2064 puci.city_code,
2065 pus.state_name,
2066 puc.county_name,
2067 puci.city_name
2068 from PAY_US_CITY_NAMES puci,
2069 PAY_US_COUNTIES puc,
2070 PAY_US_STATES pus,
2071 HR_LOCATIONS hrl
2075 and puc.county_name = hrl.region_1
2072 where hrl.location_id = cp_location_id
2073 and pus.state_abbrev = hrl.region_2
2074 and puc.state_code = pus.state_code
2076 and puci.state_code = pus.state_code
2077 and puci.county_code = puc.county_code
2078 and puci.city_name = hrl.town_or_city;
2079
2080 cursor csr_get_override_work_location(cp_location_id number) is
2081 select pus.state_code,
2082 puc.county_code,
2083 puci.city_code,
2084 pus.state_name,
2085 puc.county_name,
2086 puci.city_name
2087 from PAY_US_CITY_NAMES puci,
2088 PAY_US_COUNTIES puc,
2089 PAY_US_STATES pus,
2090 HR_LOCATIONS hrl
2091 where hrl.location_id = cp_location_id
2092 and pus.state_abbrev = hrl.loc_information17
2093 and puc.state_code = pus.state_code
2094 and puc.county_name = hrl.loc_information19
2095 and puci.state_code = pus.state_code
2096 and puci.county_code = puc.county_code
2097 and puci.city_name = hrl.loc_information18;
2098
2099 l_work_location_id number;
2100 l_ovrd_location_id number;
2101
2102 begin
2103
2104 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',1);
2105 /* Primary Work Address */
2106 p_work_state_code := null;
2107 p_work_county_code := null;
2108 p_work_city_code := null;
2109 p_work_state_name := null;
2110 p_work_county_name := null;
2111 p_work_city_name := null;
2112
2113 /* Primary Work Override Address */
2114 p_work1_state_code := null;
2115 p_work1_county_code := null;
2116 p_work1_city_code := null;
2117 p_work1_state_name := null;
2118 p_work1_county_name := null;
2119 p_work1_city_name := null;
2120
2121 /* Override Work Address */
2122 p_work2_state_code := null;
2123 p_work2_county_code := null;
2124 p_work2_city_code := null;
2125 p_work2_state_name := null;
2126 p_work2_county_name := null;
2127 p_work2_city_name := null;
2128
2129 /* Override Override Work Address */
2130 p_work3_state_code := null;
2131 p_work3_county_code := null;
2132 p_work3_city_code := null;
2133 p_work3_state_name := null;
2134 p_work3_county_name := null;
2135 p_work3_city_name := null;
2136
2137 /* Get Assignment Locations */
2138 open csr_get_asgn_locations;
2139 fetch csr_get_asgn_locations into
2140 l_work_location_id,
2141 l_ovrd_location_id;
2142
2143 /* Get the work location details */
2144 if csr_get_asgn_locations%found then
2145
2146 /* Primary Location is found, so get the Primary Work Details
2147 and Override details for that location (if any). */
2148 if l_work_location_id is not null then
2149
2150 open csr_get_work_location(l_work_location_id);
2151 fetch csr_get_work_location into
2152 /* Primary Work Address */
2153 p_work_state_code,
2154 p_work_county_code,
2155 p_work_city_code,
2156 p_work_state_name,
2157 p_work_county_name,
2158 p_work_city_name;
2159 close csr_get_work_location;
2160
2161 open csr_get_override_work_location(l_work_location_id);
2162 fetch csr_get_override_work_location into
2163 /* Primary Work Override Address */
2164 p_work1_state_code,
2165 p_work1_county_code,
2166 p_work1_city_code,
2167 p_work1_state_name,
2168 p_work1_county_name,
2169 p_work1_city_name;
2170 close csr_get_override_work_location;
2171
2172 /* Checking if the Work and Override are same. In Case they are
2173 setting the paramaters for the Overide Location to NULL */
2174 if p_work_state_code = p_work1_state_code and
2175 p_work_county_code = p_work1_county_code and
2176 p_work_city_code = p_work1_city_code then
2177 p_work1_state_code := null;
2178 p_work1_state_name := null;
2179 p_work1_county_code := null;
2180 p_work1_county_name := null;
2181 p_work1_city_code := null;
2182 p_work1_city_name := null;
2183 end if;
2184
2185 end if; /* Primary Location was found for the Assignment */
2186
2187
2188 /* Override Location is found, so get the Override Work Details
2189 and Override details for that Override Location (if any). */
2190 if l_ovrd_location_id is not null then
2191
2192 open csr_get_work_location(l_ovrd_location_id);
2193 fetch csr_get_work_location into
2194 /* Override Work Address */
2195 p_work2_state_code,
2196 p_work2_county_code,
2197 p_work2_city_code,
2198 p_work2_state_name,
2199 p_work2_county_name,
2203
2200 p_work2_city_name;
2201
2202 close csr_get_work_location;
2204 open csr_get_override_work_location(l_ovrd_location_id);
2205 fetch csr_get_override_work_location into
2206 /* Override Work Override Address */
2207 p_work3_state_code,
2208 p_work3_county_code,
2209 p_work3_city_code,
2210 p_work3_state_name,
2211 p_work3_county_name,
2212 p_work3_city_name;
2213
2214 close csr_get_override_work_location;
2215
2216 /* Checking if the Work Override and Override Override are same.
2217 In Case they are setting the paramaters for the Overide
2218 Overide Location to NULL */
2219 if p_work2_state_code = p_work3_state_code and
2220 p_work2_county_code = p_work3_county_code and
2221 p_work2_city_code = p_work3_city_code then
2222 p_work3_state_code := null;
2223 p_work3_state_name := null;
2224 p_work3_county_code := null;
2225 p_work3_county_name := null;
2226 p_work3_city_code := null;
2227 p_work3_city_name := null;
2228 end if;
2229
2230 end if; /* Primary Location was found for the Assignment */
2231
2232 end if; /* Assignment Record Found */
2233 close csr_get_asgn_locations;
2234
2235 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',3);
2236 if p_work3_state_code is not null then
2237 p_sui_state_code := p_work3_state_code;
2238 elsif p_work2_state_code is not null then
2239 p_sui_state_code := p_work2_state_code;
2240 elsif p_work1_state_code is not null then
2241 p_sui_state_code := p_work1_state_code;
2242 elsif p_work_state_code is not null then
2243 p_sui_state_code := p_work_state_code;
2244 end if;
2245
2246 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',4);
2247
2248 if p_work3_city_code is not null then
2249 p_loc_city := p_work3_state_code || '-' || p_work3_county_code || '-'||p_work3_city_code;
2250 elsif p_work2_city_code is not null then
2251 p_loc_city := p_work2_state_code || '-' || p_work2_county_code || '-'||p_work2_city_code;
2252 elsif p_work1_city_code is not null then
2253 p_loc_city := p_work1_state_code || '-' || p_work1_county_code || '-'||p_work1_city_code;
2254 elsif p_work_city_code is not null then
2255 p_loc_city := p_work_state_code || '-' || p_work_county_code || '-'||p_work_city_code;
2256 end if;
2257
2258 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_work_codes',5);
2259 end get_all_work_codes;
2260
2261 procedure get_orig_res_codes (p_assignment_id in number,
2262 p_session_date in date,
2263 p_res_state_code out NOCOPY varchar2,
2264 p_res_county_code out NOCOPY varchar2,
2265 p_res_city_code out NOCOPY varchar2,
2266 p_res_state_name out NOCOPY varchar2,
2267 p_res_county_name out NOCOPY varchar2,
2268 p_res_city_name out NOCOPY varchar2) is
2269
2270 /* Cursor to get the resident state, county and city codes */
2271 cursor csr_get_res is
2272 select pus.state_code,
2273 puc.county_code,
2274 puci.city_code,
2275 pus.state_name,
2276 puc.county_name,
2277 puci.city_name
2278 from PAY_US_CITY_NAMES puci,
2279 PAY_US_COUNTIES puc,
2280 PAY_US_STATES pus,
2281 PER_ADDRESSES pa,
2282 PER_ASSIGNMENTS_F paf
2283 where paf.assignment_id = p_assignment_id
2284 and p_session_date between paf.effective_start_date and
2285 paf.effective_end_date
2286 and pa.person_id = paf.person_id
2287 and pa.primary_flag = 'Y'
2288 and p_session_date between pa.date_from and
2289 nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
2290 and pus.state_abbrev = pa.region_2
2291 and puc.state_code = pus.state_code
2292 and puc.county_name = pa.region_1
2293 and puci.state_code = pus.state_code
2294 and puci.county_code = puc.county_code
2295 and puci.city_name = pa.town_or_city;
2296
2297 begin
2298
2299 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_orig_res_codes',1);
2300
2301 /* Get the resident address details */
2302
2303 open csr_get_res;
2304
2305 fetch csr_get_res into p_res_state_code,
2306 p_res_county_code,
2307 p_res_city_code,
2308 p_res_state_name,
2309 p_res_county_name,
2310 p_res_city_name;
2311
2312 if csr_get_res%NOTFOUND then
2313
2314 p_res_state_code := null;
2315 p_res_county_code := null;
2316 p_res_city_code := null;
2317 p_res_state_name := null;
2318 p_res_county_name := null;
2319 p_res_city_name := null;
2320
2321 end if;
2322
2323 hr_utility.set_location('pay_us_emp_dt_tax_rules.get_orig_res_codes',2);
2324
2325 close csr_get_res;
2326
2327 end get_orig_res_codes;
2328 end pay_us_emp_dt_tax_val;
[Home] [Help]