1 PACKAGE BODY pay_us_geo_upd_pkg as
2 /* $Header: pyusgeou.pkb 120.27.12020000.8 2012/10/25 04:28:37 emunisek ship $ */
3
4 --Bug 2996546 declare pl/sql table variables in order to load
5 --input_value_id from pay_input_values_f
6
7
8 TYPE piv_type is table of pay_input_values_f.input_value_id%type
9 index by binary_integer ;
10 l_counter number := 0 ;
11 l_total number := 0 ;
12 l_number number := 0 ;
13
14
15 input_val_cur piv_type ;
16 piv_rec pay_input_values_f%rowtype;
17
18
19 function get_parameter(name in varchar2,
20 parameter_list varchar2) return varchar2
21 is
22 start_ptr number;
23 end_ptr number;
24 token_val pay_payroll_actions.legislative_parameters%type;
25 par_value pay_payroll_actions.legislative_parameters%type;
26 begin
27 --
28 token_val := name||'=';
29 --
30 start_ptr := instr(parameter_list, token_val) + length(token_val);
31 end_ptr := instr(parameter_list, ' ',start_ptr);
32 --
33 /* if there is no spaces use then length of the string */
34 if end_ptr = 0 then
35 end_ptr := length(parameter_list)+1;
36 end if;
37 --
38 /* Did we find the token */
39 if instr(parameter_list, token_val) = 0 then
40 par_value := NULL;
41 else
42 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
43 end if;
44 --
45 return par_value;
46 --
47 end get_parameter;
48
49 function get_city(p_person_id number,
50 p_location_id number,
51 p_state_code varchar2,
52 p_county_code varchar2,
53 p_city_code varchar2,
54 p_city_name varchar2,
55 p_patch_name varchar2,
56 p_process_type varchar2
57 )
58 return varchar2 is
59
60 l_emp_city_name pay_us_city_names.city_name%TYPE;
61 l_city_name_found pay_us_city_names.city_name%TYPE;
62 l_city_found VARCHAR2(1):= 'N';
63 l_found_city pay_us_city_names.city_name%TYPE;
64 p_county_name pay_us_counties.county_name%TYPE;
65
66 cursor c_county_name is
67 select city_name
68 from pay_us_modified_geocodes
69 where patch_name = p_patch_name
70 and process_type = 'CN'
71 and state_code = p_state_code
72 and county_code = p_county_code;
73
74 cursor c_get_emp_cities is
75 select town_or_city
76 from per_addresses pa,
77 pay_us_states pus,
78 pay_us_counties puc
79 where pus.state_abbrev = pa.region_2
80 and pus.state_code = puc.state_code
81 and nvl(p_county_name,puc.county_name) = pa.region_1
82 and pa.person_id = p_person_id
83 and pus.state_code = p_state_code
84 and puc.county_code = p_county_code
85 union
86 select pa.add_information18
87 from per_addresses pa,
88 pay_us_states pus,
89 pay_us_counties puc
90 where pus.state_abbrev = pa.add_information17
91 and pus.state_code = puc.state_code
92 and nvl(p_county_name,puc.county_name) = pa.add_information19
93 and pa.person_id = p_person_id
94 and pus.state_code = p_state_code
95 and puc.county_code = p_county_code
96 union
97 select hl.town_or_city
98 from hr_locations_all hl,
99 pay_us_states pus,
100 pay_us_counties puc
101 where pus.state_abbrev = hl.region_2
102 and pus.state_code = puc.state_code
103 and nvl(p_county_name,puc.county_name) = hl.region_1
104 and hl.location_id = p_location_id
105 and pus.state_code = p_state_code
106 and puc.county_code = p_county_code
107 union
108 select loc_information18
109 from hr_locations_all hl,
110 pay_us_states pus,
111 pay_us_counties puc
112 where pus.state_abbrev = hl.loc_information17
113 and pus.state_code = puc.state_code
114 and nvl(p_county_name,puc.county_name) = hl.loc_information19
115 and pus.state_code = p_state_code
116 and puc.county_code = p_county_code
117 and hl.location_id = p_location_id;
118
119 cursor c_get_city_names(p_emp_city_name pay_us_city_names.city_name%TYPE) is
120 select pucn.city_name
121 from pay_us_city_names pucn
122 where pucn.state_code = p_state_code
123 and pucn.county_code = p_county_code
124 and pucn.city_code = p_city_code
125 and pucn.city_name = p_emp_city_name
126 and not exists
127 (select null
128 from pay_us_modified_geocodes
129 where patch_name = p_patch_name
130 and new_city_code = p_city_code
131 and old_city_code <> p_city_code)
132 union
133 select pucn.city_name
134 from pay_us_city_names pucn,
135 pay_us_modified_geocodes pumg
136 where pucn.state_code = p_state_code
137 and pucn.county_code = p_county_code
138 and pucn.city_name = p_emp_city_name
139 and pumg.state_code = pucn.state_code
140 and pumg.county_code = pucn.county_code
141 and pumg.old_city_code = p_city_code
142 and pumg.new_city_code <> p_city_code
143 and pumg.patch_name = p_patch_name;
144
145 begin
146
147 open c_county_name;
148 fetch c_county_name into p_county_name;
149
150 if c_county_name%NOTFOUND then
151 p_county_name := NULL;
152 end if;
153
154 close c_county_name;
155
156 open c_get_emp_cities;
157 fetch c_get_emp_cities into l_emp_city_name;
158
159 if c_get_emp_cities%NOTFOUND then
160
161 close c_get_emp_cities;
162 if p_process_type in ('PU','UP') then
163 return p_city_name;
164 else
165 return NULL;
166 end if;
167
168 end if;
169
170 while(c_get_emp_cities%FOUND)
171 loop
172
173 if l_emp_city_name = p_city_name
174 then
175
176 close c_get_emp_cities;
177 return p_city_name;
178
179 end if;
180
181 open c_get_city_names(l_emp_city_name);
182 fetch c_get_city_names into l_city_name_found;
183
184 if c_get_city_names%FOUND then
185 l_city_found := 'Y';
186 l_found_city := l_city_name_found;
187 end if;
188
189 close c_get_city_names;
190
191 fetch c_get_emp_cities into l_emp_city_name;
192
193 end loop;
194
195 close c_get_emp_cities;
196
197 if l_city_found = 'Y' then
198 return l_found_city;
199 end if;
200
201 if p_process_type in ('PU','UP') then
202 return p_city_name;
203 else
204 return null;
205 end if;
206
207 end;
208
209
210
211 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
212
213 --
214
215 leg_param pay_payroll_actions.legislative_parameters%type;
216 l_year varchar2(4);
217
218 ln_upgrade_patch pay_patch_status.patch_name%TYPE;
219 --
220 begin
221
222 hr_utility.trace('reached range_cursor');
223
224 select ppa.legislative_parameters,
225 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters)
226 into leg_param,
227 ln_upgrade_patch
228 from pay_payroll_actions ppa
229 where ppa.payroll_action_id = pactid;
230
231 sqlstr := ' select distinct paf.person_id
232 from pay_us_modified_geocodes mg,
233 pay_us_emp_city_tax_rules_f tr,
234 per_all_assignments_f paf,
235 pay_us_states pus
236 where mg.patch_name = '''||ln_upgrade_patch||'''
237 and mg.state_code = pus.state_code
238 and mg.state_code = tr.state_code
239 and mg.county_code = tr.county_code
240 and mg.old_city_code = tr.city_code
241 and tr.assignment_id = paf.assignment_id
242 and :pactid is not null
243 order by paf.person_id';
244
245 hr_utility.trace(sqlstr);
246
247 hr_utility.trace('leaving range_cursor');
248
249 end range_cursor;
250
251
252 ---------------------------------- action_creation ----------------------------------
253 --
254 procedure action_creation (pactid in number,
255 stperson in number,
256 endperson in number,
257 chunk in number) is
258
259 leg_param pay_payroll_actions.legislative_parameters%type;
260 l_year varchar2(4);
261 l_geo_phase_id number;
262 l_mode Pay_Payroll_actions.legislative_parameters%type;
263
264 l_patch_name pay_patch_status.patch_name%TYPE;
265
266
267
268
269 cursor c_parameters ( pactid number) is
270 select ppa.legislative_parameters,
271 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
272 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
273 from pay_payroll_actions ppa
274 where ppa.payroll_action_id = pactid;
275
276
277 CURSOR c_actions_assignment
278 (
279 pactid number,
280 stperson number,
281 endperson number
282 ) is
283
284 SELECT distinct ectr.assignment_id
285 FROM per_all_assignments_f paf,
286 pay_us_emp_city_tax_rules_f ectr,
287 pay_us_modified_geocodes pmod
288 WHERE pmod.state_code = ectr.state_code
289 AND pmod.county_code = ectr.county_code
290 AND pmod.new_county_code is null
291 AND pmod.old_city_code = ectr.city_code
292 AND pmod.process_type in ('UP','US','PU','D','SU')
293 AND pmod.patch_name = l_patch_name
294 AND ectr.assignment_id = paf.assignment_id
295 AND paf.person_id between stperson and endperson
296 AND get_city(paf.person_id, paf.location_id, ectr.state_code,
297 ectr.county_code,ectr.city_code,pmod.city_name,l_patch_name,pmod.process_type) = pmod.city_name
298 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
299 where pugu.assignment_id = ectr.assignment_id
300 and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
301 and pugu.old_juri_code = ectr.jurisdiction_code
302 and pugu.table_value_id is null
303 and pugu.table_name is null
304 and pugu.process_type = pmod.process_type
305 and pugu.process_mode = l_mode
306 and pugu.id = l_geo_phase_id)
307 UNION ALL
308
309 SELECT distinct pac.assignment_id
310 FROM per_all_assignments_f paf,
311 pay_action_contexts pac,
312 pay_us_modified_geocodes pmod
313 WHERE pmod.state_code = 'CA'
314 AND pmod.county_code = pac.context_value
315 AND pac.context_id in (select context_id
316 from ff_contexts
317 where context_name = 'JURISDICTION_CODE')
318 AND pmod.patch_name = l_patch_name
319 AND pac.assignment_id = paf.assignment_id
320 AND paf.person_id between stperson and endperson ;
321
322
323 /* Changing the hint to use index PAY_US_MODIFIED_GEOCODES_N1 */
324 CURSOR c_actions_run_bal
325 (
326 pactid number,
327 p_balance_load_date date
328 ) is
329 select ppa.payroll_action_id
330 from per_business_groups pbg, pay_payroll_actions ppa
331 Where ppa.action_type in ('R', 'Q', 'I', 'B', 'V')
332 and ppa.effective_date >= p_balance_load_date
333 and pbg.business_group_id = ppa.business_group_id
334 and pbg.legislation_code in ( 'US', 'CA');
335
336 cursor c_get_phase_id (p_patch_name varchar2)
337 is
338 select ID
339 from pay_patch_status
340 where patch_name = p_patch_name
341 and status in ('P','E');
342
343 Cursor c_geo_check (p_patch_name in varchar2) is
344 select phase, status from pay_patch_status
345 where patch_name like p_patch_name || '%'
346 and legislation_code = 'US';
347
348 l_assignment_id number;
349 l_payact_id number;
350 lockingactid number;
351
352 lv_phase varchar2(30);
353 lv_status varchar2(2);
354
355 l_balance_load_date pay_balance_validation.balance_load_date%type;
356 --
357 /* Bug#7240914: New variables */
358 lv_no_of_chunks number;
359 lv_count number;
360 lv_curr_chunk number;
361 /* Bug#7240914: Changes end*/
362 begin
363
364 -- hr_utility.trace_on('','TCL');
365
366 hr_utility.trace('entering action_creation');
367 hr_utility.set_location('geocode_action_creation',1);
368
369 open c_parameters(pactid);
370
371 fetch c_parameters into leg_param,
372 l_patch_name,
373 l_mode;
374
375 close c_parameters;
376
377 hr_utility.trace('l_patch_name is '|| l_patch_name );
378
379
380 hr_utility.trace('before open c_geo_check ');
381 open c_geo_check (l_patch_name);
382
383 fetch c_geo_check into lv_phase, lv_status;
384
385 if c_geo_check%notfound or lv_status <> 'C' then
386 hr_utility.trace('c_geo_check not found ');
387
388 if c_geo_check%notfound and chunk=1 then
389 hr_utility.trace('c_geo_check not found chunk = 1');
390 /*
391 If both conditions above are true, there is a geocode update
392 underway and a row for this process needs to be added to the
393 pay_patch_status table.
394 */
395 hr_utility.trace('inserting into pay_patch_status ');
396 insert into pay_patch_status
397 (ID,
398 PATCH_NUMBER,
399 PATCH_NAME,
400 PHASE,
401 PROCESS_TYPE,
402 APPLIED_DATE,
403 STATUS,
404 DESCRIPTION,
405 UPDATE_DATE,
406 LEGISLATION_CODE,
407 APPLICATION_RELEASE,
408 PREREQ_PATCH_NAME)
409 values
410 (PAY_PATCH_STATUS_S.nextval,
411 '1111111',
412 l_patch_name, --p_patch_name,
413 'START',
414 null,
415 sysdate,
416 'P',
417 'CURRENT GEOCODE PATCH', -- lv_patch_desc,
418 null,
419 'US',
420 '115',
421 'Q2' );
422
423 end if; -- end if for the chunk=1
424
425 hr_utility.trace('opening c_get_phase_id ');
426
427 open c_get_phase_id(l_patch_name);
428
429 fetch c_get_phase_id into l_geo_phase_id;
430
431
432 hr_utility.trace('value of l_geo_phase id is '|| to_char(l_geo_phase_id ));
433
434
435 hr_utility.set_location('geocode_action_creation',2);
436 open c_actions_assignment(pactid,stperson,endperson);
437
438 loop
439 hr_utility.set_location('geocode_action_creation',3);
440 fetch c_actions_assignment into l_assignment_id;
441
442 exit when c_actions_assignment%notfound;
443
444 hr_utility.set_location('geocode_action_creation',4);
445 select pay_assignment_actions_s.nextval
446 into lockingactid
447 from dual;
448
449 -- insert the action record.
450
451 hr_nonrun_asact.insact(lockingactid => lockingactid,
452 Object_Id => l_assignment_id,
453 pactid => pactid,
454 chunk => chunk,
455 object_type => 'ASG');
456 --
457 end loop; -- loop 1
458 close c_actions_assignment;
459
460
461 -- Create actions for GRE level Run balances
462
463 hr_utility.set_location('geocode_action_creation',5);
464
465
466 hr_utility.trace('before update_taxability_rules value of l_geo_phase_Id is '|| to_char(l_geo_phase_Id));
467
468 IF chunk=1 THEN
469
470 select min(balance_load_date)
471 into l_balance_load_date
472 from pay_balance_validation;
473 open c_actions_run_bal(pactid,l_balance_load_date);
474
475 /* Bug#7240914: Fetch number of chunks created for the current run
476 from table pay_population_ranges */
477 hr_utility.trace( 'Fetching Number of chunks created for this process from pay_population_ranges.');
478 select max(chunk_number)
479 into lv_no_of_chunks
480 from pay_population_ranges
481 where payroll_action_id = pactid;
482
483 hr_utility.trace( 'Number of chunks: ' || to_char(lv_no_of_chunks));
484 lv_no_of_chunks := NVL(lv_no_of_chunks,0);
485 lv_count := 1 ;
486 /*Bug#7240914: Changes end here*/
487 loop
488 hr_utility.set_location('gocode_action_creation',6);
489 fetch c_actions_run_bal into l_payact_id;
490
491 exit when c_actions_run_bal%notfound;
492 --
493
494 hr_utility.set_location('gocode_action_creation',7);
495 select pay_assignment_actions_s.nextval
496 into lockingactid
497 from dual;
498 --
499 /*Bug#7240914: if lv_no_of_chunks < 1 then assign all to chunk 1
500 else use the iterator lv_count to distribute records among
501 all chunks. */
502 IF lv_no_of_chunks < 1 THEN
503 lv_curr_chunk := 1 ;
504 ELSE
505 select decode (mod(lv_count,lv_no_of_chunks),0,lv_no_of_chunks,mod(lv_count,lv_no_of_chunks))
506 into lv_curr_chunk
507 from dual;
508 END IF;
509
510 hr_utility.trace( 'lv_count: ' || to_char(lv_count));
511 hr_utility.trace( 'lv_current_chunk: ' || to_char(lv_curr_chunk));
512 /*Bug#7240914: changes end here */
513 hr_nonrun_asact.insact(lockingactid => lockingactid,
514 Object_id => l_payact_id,
515 pactid => pactid,
516 -- chunk => chunk,
517 chunk => lv_curr_chunk, /*Bug#7240914 */
518 object_type => 'PER');
519 --
520 lv_count := lv_count + 1 ; /*Bug#7240914 */
521 end loop; -- loop 1
522 close c_actions_run_bal;
523 --Added for Annual GEO 2010 Bug#9541247
524 pay_us_geo_upd_pkg.update_county_name(l_geo_phase_id,l_mode,l_patch_name,'INTERNAL'); --l_patch_name);
525 --End Bug#9541247
526
527 --Added for Annual GEO 2012 Bug#14314081
528 pay_us_geo_upd_pkg.update_city_name(l_geo_phase_id,l_mode,l_patch_name,'INTERNAL'); --l_patch_name);
529 --End Bug#9541247
530
531 pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
532
533 pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
534
535 pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
536
537 END IF;
538
539
540 hr_utility.trace('leaving action_creation');
541
542 if c_get_phase_id%isopen then
543 close c_get_phase_id;
544 end if;
545
546 END IF; /* lv_status patch is 'C' and no actions were created */
547
548 if c_geo_check%isopen then
549 close c_geo_check;
550 end if;
551
552 end action_creation;
553
554
555 procedure sort_action
556 (
557 payactid in varchar2, /* payroll action id */
558 sqlstr in out nocopy varchar2, /* string holding the sql statement */
559 len out nocopy number /* length of the sql string */
560 ) is
561 begin
562
563 sqlstr := 'select paa1.rowid
564 from pay_assignment_actions paa1, -- PYUGEN assignment action
565 pay_payroll_actions ppa1 -- PYUGEN payroll action id
566 where ppa1.payroll_action_id = :pactid
567 and paa1.payroll_action_id = ppa1.payroll_action_id
568 order by paa1.assignment_action_id
569 for update of paa1.assignment_id';
570
571 len := length(sqlstr); -- return the length of the string.
572 end sort_action;
573
574
575 PROCEDURE archive_code(p_xfr_action_id in number
576 ,p_effective_date in date)
577 IS
578
579 cursor c_xfr_info (cp_assignment_action in number) is
580 select ptoa.payroll_action_id,
581 ptoa.object_id,
582 ptoa.object_type
583 from PAY_TEMP_OBJECT_ACTIONS ptoa
584 where ptoa.object_action_id = cp_assignment_action;
585
586 cursor c_parameters ( pactid number) is
587 select ppa.legislative_parameters,
588 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
589 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
590 from pay_payroll_actions ppa
591 where ppa.payroll_action_id = pactid;
592
593 cursor c_get_phase_id (p_patch_name varchar2)
594 is
595 select ID
596 from pay_patch_status
597 where patch_name = p_patch_name
598 and status in ('P','E');
599
600 l_payroll_action_id number;
601 l_object_id number;
602 l_object_type PAY_TEMP_OBJECT_ACTIONS.object_type%TYPE;
603
604 l_geo_phase_id number;
605 l_year varchar2(4);
606 l_mode varchar2(7);
607 leg_param pay_payroll_actions.legislative_parameters%type;
608 l_patch_name pay_patch_status.patch_name%type;
609
610 BEGIN
611
612 hr_utility.set_location ('pay_us_geo_update.action_code', 1);
613
614 open c_xfr_info (p_xfr_action_id);
615
616 fetch c_xfr_info into l_payroll_action_id,
617 l_object_id,
618 l_object_type;
619
620 close c_xfr_info;
621
622 open c_parameters(l_payroll_action_id);
623
624 fetch c_parameters into leg_param,
625 l_patch_name,
626 l_mode;
627 close c_parameters;
628
629 open c_get_phase_id(l_patch_name);
630 fetch c_get_phase_id into l_geo_phase_id;
631 close c_get_phase_id;
632
633
634 if l_object_type = 'ASG' THEN
635
636 pay_us_geo_upd_pkg.upgrade_geocodes (p_assign_start => l_object_id,
637 p_assign_end => l_object_id,
638 p_geo_phase_id => l_geo_phase_id,
639 p_mode => l_mode,
640 p_patch_name => l_patch_name);
641
642
643 elsif l_object_type = 'PER' Then
644
645 pay_us_geo_upd_pkg.group_level_balance (P_START_PAYROLL_ACTION => l_object_id,
646 P_END_PAYROLL_ACTION => l_object_id,
647 P_GEO_PHASE_ID => l_geo_phase_id,
648 P_MODE => l_mode,
649 P_PATCH_NAME => l_patch_name);
650
651 END IF;
652
653 END archive_code;
654
655
656
657 procedure archive_deinit( p_payroll_action_id in number)
658 is
659 --
660 --
661 Cursor c_get_params is
662 select patch_name, patch_number
663 from pay_patch_status
664 where description = 'CURRENT GEOCODE PATCH';
665
666 Cursor c_geo_check (p_patch_name in varchar2,
667 p_patch_number in number ) is
668 select id from pay_patch_status
669 where patch_name = p_patch_name
670 and patch_number = p_patch_number
671 and legislation_code = 'US';
672
673
674 -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
675 Cursor c_geo_upd (p_patch_id in number,
676 p_patch_status in varchar2) is
677 select 'x' from dual
678 where exists(select 'x' from pay_us_geo_update
679 where id = p_patch_id
680 and status = p_patch_status
681 and rownum < 2);
682
683 cursor c_parameters ( pactid number) is
684 select ppa.legislative_parameters,
685 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
686 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
687 from pay_payroll_actions ppa
688 where ppa.payroll_action_id = pactid;
689
690
691
692 lv_cur_geo_patch varchar2(240);
693 ln_patch_number number;
694 ln_patch_id number;
695 lc_error varchar2(10);
696 lc_status varchar2(1);
697 --
698 ln_upgrade_patch pay_patch_status.patch_name%TYPE;
699 ln_upgrade_patch_id pay_patch_status.id%TYPE;
700 leg_param pay_payroll_actions.legislative_parameters%type;
701 l_year varchar2(4);
702 l_mode pay_payroll_actions.legislative_parameters%type;
703 l_geo_phase_id number;
704
705 l_patch_name pay_patch_status.patch_name%TYPE;
706
707 l_req_id number;
708 copies_buffer varchar2(80) := null;
709 print_buffer varchar2(80) := null;
710 printer_buffer varchar2(80) := null;
711 style_buffer varchar2(80) := null;
712 save_buffer boolean := null;
713 save_result varchar2(1) := null;
714 req_id varchar2(80) := null;
715 x boolean;
716 x1 boolean;
717
718 l_valid_status varchar2(5);
719 l_program varchar2(100);
720 retcode number;
721 errbuf varchar2(80);
722
723 --
724 --
725
726 begin
727 fnd_file.put_line(fnd_file.log, 'Inside Archive_deinit procedure');
728 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
729 retcode := 0;
730 fnd_file.put_line(fnd_file.log, 'p_payroll_action_id: ' || to_char(p_payroll_action_id));
731 open c_parameters(p_payroll_action_id);
732
733 fetch c_parameters into leg_param,
734 l_patch_name,
735 l_mode;
736 close c_parameters;
737 fnd_file.put_line(fnd_file.log, 'leg_param: ' || to_char(leg_param));
738 fnd_file.put_line(fnd_file.log, 'l_patch_name: ' || l_patch_name);
739 fnd_file.put_line(fnd_file.log, 'l_mode: ' || l_mode);
740 /***** submit the geocode reports ****/
741
742 pay_us_geocode_report_pkg.extract_data( errbuf
743 ,retcode
744 ,p_process_mode => l_mode
745 ,p_geocode_patch_name => l_patch_name );
746
747 /* Wrap up the geocode process */
748
749 lc_status := 'C';
750
751 open c_get_params;
752 fetch c_get_params into lv_cur_geo_patch, ln_patch_number;
753 close c_get_params;
754
755 --hr_utility.trace(' lv_cur_geo_patch = ' || lv_cur_geo_patch);
756 --hr_utility.trace('archive deinit ln_patch_number = ' || ln_patch_number);
757 fnd_file.put_line(fnd_file.log, ' lv_cur_geo_patch = ' || lv_cur_geo_patch);
758 fnd_file.put_line(fnd_file.log, 'archive deinit ln_patch_number = ' || ln_patch_number);
759 --
760 open c_geo_check(lv_cur_geo_patch, ln_patch_number);
761 fetch c_geo_check into ln_patch_id;
762 if c_geo_check%found then
763
764 fnd_file.put_line(fnd_file.log, 'c_geo_check%found');
765 open c_geo_upd(ln_patch_id, 'P');
766 fetch c_geo_upd into lc_error;
767 if c_geo_upd%found then
768 fnd_file.put_line(fnd_file.log, 'c_geo_upd%found');
769 update pay_patch_status
770 set status = 'E'
771 where id = ln_patch_id;
772
773 else
774 fnd_file.put_line(fnd_file.log, 'c_geo_upd%notfound');
775 update pay_patch_status
776 set status = 'C',
777 phase = null,
778 process_type = null,
779 description = null
780 where id = ln_patch_id;
781
782 end if;
783 close c_geo_upd;
784 else /* c_geo_check%found */
785 fnd_file.put_line(fnd_file.log, 'c_geo_check%notfound');
786 end if;
787 close c_geo_check;
788
789 /* EXCEPTION
790 --
791 WHEN hr_utility.hr_error THEN
792 --
793 -- Set up error message and error return code.
794 --
795
796 hr_utility.trace('in the exception 1');
797
798 errbuf := hr_utility.get_message;
799 retcode := 2;
800 --
801 --
802 WHEN others THEN
803 --
804 -- Set up error message and return code.
805 --
806
807 hr_utility.trace('in the exception 2 sqlerrm = ' || sqlerrm);
808
809 errbuf := sqlerrm;
810 retcode := 2; */
811 end archive_deinit;
812
813 /*Added parameter p_description for Bug#10060041*/
814
815 PROCEDURE write_message(
816 p_proc_type IN VARCHAR2,
817 p_person_id IN NUMBER,
818 p_assign_id IN NUMBER,
819 p_old_juri_code IN VARCHAR2,
820 p_new_juri_code IN VARCHAR2,
821 p_location IN VARCHAR2,
822 p_id IN NUMBER,
823 p_status IN VARCHAR2 DEFAULT NULL,
824 p_description IN VARCHAR2 DEFAULT NULL)
825
826 IS
827
828 BEGIN
829
830 hr_utility.trace('Entering pay_us_geo_upd_pkg.write message');
831 /*Added field p_description for Bug#10060041*/
832 IF G_MODE = 'UPGRADE' THEN
833 insert into PAY_US_GEO_UPDATE (ID,
834 ASSIGNMENT_ID,
835 PERSON_ID,
836 TABLE_NAME,
837 TABLE_VALUE_ID,
838 OLD_JURI_CODE,
839 NEW_JURI_CODE,
840 PROCESS_TYPE,
841 PROCESS_DATE,
842 PROCESS_MODE,
843 STATUS,
844 DESCRIPTION)
845 VALUES(g_geo_phase_id,
846 p_assign_id,
847 p_person_id,
848 p_location,
849 p_id,
850 p_old_juri_code,
851 p_new_juri_code,
852 p_proc_type,
853 sysdate,
854 'UPGRADE',
855 p_status,
856 p_description);
857
858 ELSE
859 /*Added field p_description for Bug#10060041*/
860 insert into PAY_US_GEO_UPDATE (ID,
861 ASSIGNMENT_ID,
862 PERSON_ID,
863 TABLE_NAME,
864 TABLE_VALUE_ID,
865 OLD_JURI_CODE,
866 NEW_JURI_CODE,
867 PROCESS_TYPE,
868 PROCESS_DATE,
869 PROCESS_MODE,
870 STATUS,
871 DESCRIPTION)
872 VALUES(g_geo_phase_id,
873 p_assign_id,
874 p_person_id,
875 p_location,
876 p_id,
877 p_old_juri_code,
878 p_new_juri_code,
879 p_proc_type,
880 sysdate,
881 g_mode,
882 p_status,
883 p_description);
884
885
886 END IF;
887 hr_utility.trace('Exiting pay_us_geo_upd_pkg.write message');
888
889 END write_message;
890
891
892 -- We can call upgrade_geocodes in a DEBUG mode also.
893 -- DEBUG mode will not do any updates in the tables. It will
894 -- Create the city tax records and vertex element entries though.
895 -- But it only creates them if they are missing in the first place.
896 -- We are defaulting to NULL, in our update statements we check for DEBUG
897
898 PROCEDURE upgrade_geocodes(P_ASSIGN_START NUMBER,
899 P_ASSIGN_END NUMBER,
900 P_GEO_PHASE_ID NUMBER,
901 P_MODE VARCHAR2,
902 P_PATCH_NAME VARCHAR2,
903 P_CITY_NAME VARCHAR2 DEFAULT NULL,
904 P_API_MODE VARCHAR2 DEFAULT 'N')
905
906 IS
907 --Retrieve all changed geocodes on per_assignment_extra_info table. This will
908 --be our main 'driving' table
909 /* CURSOR paei_cur IS
910 SELECT distinct paei.aei_information2, paei.aei_information13,
911 paei.assignment_id,
912 pmod.state_code||'-'||pmod.county_code||'-'
913 ||pmod.new_city_code jd_code,
914 paf.person_id
915 FROM per_assignments_f paf,
916 pay_us_modified_geocodes pmod,
917 per_assignment_extra_info paei
918 WHERE paei.information_type = 'LOCALITY'
919 AND substr(paei.aei_information2,8,4) <> '0000'
920 AND pmod.city_name = paei.aei_information13
921 AND pmod.state_code = substr(paei.aei_information2,1,2)
922 AND pmod.county_code = substr(paei.aei_information2,4,3)
923 AND pmod.old_city_code = substr(paei.aei_information2,8,4)
924 AND pmod.process_type in ('UP','US','PU','D','SU','RP','RS')
925 AND paf.assignment_id = paei.assignment_id;
926
927 paei_rec paei_cur%ROWTYPE; */
928
929 --Retrieve all changed geocodes on pay_us_emp_city_tax_rules_f table.
930 --This will be our main 'driving' table.
931 --Added the ASSIGN START and ASSIGN END so that we can multi-thread the
932 --driving cursor
933 --
934 --
935 --Per bug 2996546 added another select statement with UNION ALL
936 --to the CURSOR main_driving_cur in order to process Canadian
937 --Legislation data
938 --
939 --
940
941 CURSOR main_driving_cur(P_ASSIGN_START NUMBER, P_ASSIGN_END NUMBER, P_CITY_NAME VARCHAR2, P_API_MODE VARCHAR2) IS
942 SELECT distinct ectr.jurisdiction_code, ectr.assignment_id,
943 pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code jd_code,
944 paf.person_id, pmod.new_city_code, pmod.process_type, ectr.emp_city_tax_rule_id
945 FROM per_all_assignments_f paf,
946 pay_us_emp_city_tax_rules_f ectr,
947 pay_us_modified_geocodes pmod
948 WHERE pmod.state_code = ectr.state_code
949 AND pmod.county_code = ectr.county_code
950 AND pmod.new_county_code is null
951 AND pmod.old_city_code = ectr.city_code
952 AND pmod.process_type in ('UP','US','PU','D','SU')
953 AND pmod.patch_name = p_patch_name
954 AND ectr.assignment_id = paf.assignment_id
955 AND pmod.city_name = nvl(p_city_name, pmod.city_name)
956 AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END
957 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
958 where pugu.assignment_id = ectr.assignment_id
959 and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
960 and pugu.old_juri_code = ectr.jurisdiction_code
961 and pugu.table_value_id is null
962 and pugu.table_name is null
963 and pugu.process_type = pmod.process_type
964 and pugu.process_mode = g_mode
965 and pugu.id = g_geo_phase_id
966 and ((p_api_mode = 'Y' and pugu.status = 'C') or
967 (p_api_mode = 'N' and pugu.status in ('A','C'))))
968 UNION ALL
969 SELECT distinct pac.context_value, pac.assignment_id,
970 pmod.new_county_code jd_code,
971 paf.person_id, pmod.new_city_code, pmod.process_type,
972 pac.context_id
973 FROM per_all_assignments_f paf,
974 pay_action_contexts pac,
975 pay_us_modified_geocodes pmod
976 WHERE pmod.state_code = 'CA'
977 AND pmod.county_code = pac.context_value
978 AND pac.context_id in (select context_id
979 from ff_contexts
980 where context_name = 'JURISDICTION_CODE')
981 AND pmod.patch_name = p_patch_name
982 AND pac.assignment_id = paf.assignment_id
983 AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
984
985
986 main_old_juri_code varchar2(11);
987 main_assign_id number;
988 main_new_juri_code varchar2(11);
989 main_person_id number;
990 main_new_city_code varchar2(4);
991 main_proc_type varchar2(3);
992 main_city_tax_rule_id number;
993 lv_update_prr varchar2(1);
994
995 -- main_ ectr_cur%ROWTYPE;
996
997 --Retrieve all affected rows in PAY_US_EMP_CITY_TAX_RULES_F
998 --This is decoupled from above because we still want the level of
999 --of granularity for city tax records that are changed.
1000 --We already have this information we just need to verify if it has
1001 --been processed already.
1002 --Since we have this cursor we do not need this in the city_tax_records
1003 --procedure. We could have put it there but there is no need.
1004
1005 cursor city_rec_cur (p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2,
1006 p_assign_id NUMBER, p_city_tax_record_id NUMBER)
1007 IS
1008 SELECT distinct 'Y'
1009 FROM pay_us_emp_city_tax_rules_f puecf
1010 WHERE puecf.jurisdiction_code = p_old_juri_code
1011 AND puecf.assignment_id = p_assign_id
1012 AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
1013 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1014 where pugu.assignment_id = p_assign_id
1015 and pugu.table_value_id = puecf.emp_city_tax_rule_id
1016 and pugu.old_juri_code = p_old_juri_code
1017 and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1018 and pugu.process_mode = g_mode
1019 and pugu.process_type = g_process_type
1020 and pugu.id = g_geo_phase_id);
1021
1022 l_city_tax_exists varchar2(2);
1023
1024 --Retrieve all affected rows in the pay_element_entry_values_f table.
1025 --Since we can join on the main part of the pk of the table we do not
1026 --need the logic in the element_entries procedure.
1027
1028 --
1029 --Per bug 2996546 changed the where clause for
1030 --piv.legislation_code = 'US' to use the function
1031 --IS_US_OR_CA_LEGISLATION and compare input value id
1032 --stored in pl/sql table to improve performance
1033 --
1034 --
1035
1036
1037 CURSOR pev_cur(geocode VARCHAR2, assign_id NUMBER) IS
1038 SELECT /*+ ORDERED */ distinct pev.screen_entry_value, pev.element_entry_id,
1039 pev.input_value_id
1040 FROM pay_element_entries_f pee,
1041 pay_element_entry_values_f pev,
1042 pay_input_values_f piv
1043 WHERE pee.assignment_id = assign_id
1044 AND pee.element_entry_id = pev.element_entry_id
1045 AND pev.screen_entry_value = geocode
1046 AND pev.input_value_id = piv.input_value_id
1047 AND piv.name = 'Jurisdiction'
1048 -- AND piv.legislation_code = 'US'
1049 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id
1050 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1051 where pugu.assignment_id = assign_id
1052 and pugu.table_value_id = pev.element_entry_id
1053 and pugu.old_juri_code = geocode
1054 and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
1055 and pugu.process_mode = g_mode
1056 and pugu.process_type = g_process_type
1057 and pugu.id = g_geo_phase_id);
1058
1059 pev_rec pev_cur%ROWTYPE;
1060
1061 --Retrieve all affected rows in the pay_run_results table.
1062 --The run_result_id's from this cursor will then be
1063 --used to dertermine the rows to update in the pay_run_result_values
1064 --table note since run_result_id is driving for the value table we have to pick
1065 --up all regardless if the geocode has changed because they may have run result values
1066 --that are tagged to a different jurisdiction.
1067 --Right now if the patch is reran this cursor will still pick up assignments that have
1068 --already been processed but for run result ids that do not have a modified geocode, but
1069 --when it goes through the procedure it WILL NOT update wrong geocodes because the
1070 --jurisdictions will not match. So this can be changed in the future to add the logic
1071 --here versus in the procedure: run_results.
1072
1073
1074 -- Bug 3319878 -- Breaked the query into two cursors i.e paa_cur and prr_cur.
1075 CURSOR paa_cur(assign_id NUMBER) IS
1076 SELECT assignment_action_id
1077 FROM pay_assignment_actions
1078 WHERE assignment_id = assign_id;
1079
1080 CURSOR prr_cur(assign_action_id NUMBER,assign_id NUMBER) IS
1081 SELECT distinct prr.run_result_id,
1082 prr.assignment_action_id, prr.jurisdiction_code
1083 FROM pay_run_results prr
1084 WHERE prr.assignment_action_id = assign_action_id
1085 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1086 where pugu.assignment_id = assign_id
1087 and pugu.table_value_id = prr.run_result_id
1088 and pugu.old_juri_code = prr.jurisdiction_code
1089 and pugu.table_name = 'PAY_RUN_RESULTS'
1090 and pugu.process_mode = g_mode
1091 and pugu.process_type = g_process_type
1092 and pugu.id = g_geo_phase_id);
1093
1094 paa_rec NUMBER;
1095
1096 prr_rec prr_cur%ROWTYPE;
1097
1098 --Per bug 2996546
1099 --Retrieves all affected rows in the table pay_action_contexts
1100 --
1101 --
1102 CURSOR pac_cur(assign_id NUMBER, context_id NUMBER) IS
1103 SELECT pac.context_id,
1104 pac.assignment_action_id
1105 FROM pay_action_contexts pac,
1106 pay_assignment_actions paa
1107 WHERE paa.assignment_id = assign_id
1108 AND pac.assignment_id = paa.assignment_id -- Bug# 3679984 added this to where clause
1109 AND paa.assignment_action_id = pac.assignment_action_id
1110 AND pac.context_id = context_id ;
1111
1112 pac_rec pac_cur%ROWTYPE;
1113
1114
1115
1116 --Retrieve all affected rows in the ff_archive_item_contexts table.
1117 --This cursor will check for a specific geocode that is passed in.
1118 --The passed in geocode will be the old one from pay_us_modified_geocodes.
1119 --We are joining with the archive item id, so we don't need this logic
1120 --in the procedure archive_items.
1121
1122 /* CURSOR fac_cur(assign_id NUMBER, geocode VARCHAR2) IS
1123 SELECT distinct paa.assignment_action_id,
1124 faic.context old_juri_code, faic.archive_item_id, ffc.context_id
1125 FROM ff_archive_items fai,
1126 ff_archive_item_contexts faic,
1127 pay_assignment_actions paa,
1128 pay_payroll_actions ppa,
1129 ff_contexts ffc
1130 WHERE ppa.report_type = 'YREND'
1131 AND ppa.report_category = 'RT'
1132 AND ppa.report_qualifier = 'FED'
1133 AND ppa.payroll_action_id = paa.payroll_action_id
1134 AND paa.assignment_id = assign_id
1135 AND fai.context1 = paa.assignment_action_id
1136 AND fai.archive_item_id = faic.archive_item_id
1137 AND faic.context = geocode
1138 AND ffc.context_id = faic.context_id
1139 AND ffc.context_name = 'JURISDICTION_CODE'
1140 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1141 where pugu.assignment_id = assign_id
1142 and pugu.table_value_id = faic.archive_item_id
1143 and pugu.old_juri_code = faic.context
1144 and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
1145 and pugu.process_mode = g_mode
1146 and pugu.process_type = g_process_type
1147 and pugu.id = g_geo_phase_id);
1148 */
1149 --Bug 3126437 hrglobal performance fix
1150 --
1151 CURSOR fac_cur(assign_id NUMBER, geocode VARCHAR2) IS
1152 SELECT distinct paa.assignment_action_id,
1153 faic.context old_juri_code,
1154 faic.archive_item_id,
1155 ffc.context_id
1156 FROM ff_archive_items fai,
1157 ff_archive_item_contexts faic,
1158 pay_assignment_actions paa,
1159 pay_payroll_actions ppa,
1160 ff_contexts ffc
1161 WHERE ppa.report_type in ('T4', 'T4A', 'RL1', 'RL2', 'YREND')
1162 and ppa.report_category in ('RT', 'CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
1163 and report_qualifier in ('FED','CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
1164 and ppa.payroll_action_id = paa.payroll_action_id
1165 and paa.assignment_id = assign_id
1166 and fai.context1 = paa.assignment_action_id
1167 and fai.archive_item_id = faic.archive_item_id
1168 and faic.context = geocode
1169 and ffc.context_id = faic.context_id
1170 and ffc.context_name = 'JURISDICTION_CODE'
1171 and not exists (select 'Y' from PAY_US_GEO_UPDATE pugu
1172 where pugu.assignment_id = assign_id
1173 and pugu.table_value_id = faic.archive_item_id
1174 and pugu.old_juri_code = faic.context
1175 and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
1176 and pugu.process_mode = g_mode
1177 and pugu.process_type = g_process_type
1178 and pugu.id = g_geo_phase_id);
1179 fac_rec fac_cur%ROWTYPE;
1180
1181 --Retrieve affected rows in the pay_balance_context_values table
1182 --using the latest_balance_id's from the pay_person_latest_balances
1183 --table.
1184 --Since we can join by the pk of the table we do not need any more logic
1185 --in the balance_contexts procedure.
1186
1187 CURSOR pbcv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
1188 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
1189 plb.assignment_action_id
1190 FROM pay_assignment_actions paa,
1191 pay_balance_context_values pbcv,
1192 pay_person_latest_balances plb,
1193 ff_contexts fcon
1194 WHERE paa.assignment_id = assign_id
1195 AND paa.assignment_action_id = plb.assignment_action_id
1196 AND plb.person_id = personid
1197 AND pbcv.latest_balance_id = plb.latest_balance_id
1198 AND pbcv.value = geocode
1199 AND fcon.context_id = pbcv.context_id
1200 AND fcon.context_name = 'JURISDICTION_CODE'
1201 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1202 where pugu.assignment_id = assign_id
1203 and pugu.table_value_id = plb.latest_balance_id
1204 and pugu.old_juri_code = geocode
1205 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1206 and pugu.process_mode = g_mode
1207 and pugu.process_type = g_process_type
1208 and pugu.id = g_geo_phase_id);
1209
1210 pbcv_rec pbcv_cur%ROWTYPE;
1211
1212 --Retrieve affected rows in the pay_balance_context_values table
1213 --using the latest_balance_id's from the pay_assignment_latest_balances
1214 --table.
1215 CURSOR pacv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
1216 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
1217 plb.assignment_action_id
1218 FROM ff_contexts fcon,
1219 pay_balance_context_values pbcv,
1220 pay_assignment_latest_balances plb
1221 WHERE plb.assignment_id = assign_id
1222 AND pbcv.latest_balance_id = plb.latest_balance_id
1223 AND pbcv.value = geocode
1224 AND fcon.context_id = pbcv.context_id
1225 AND fcon.context_name = 'JURISDICTION_CODE'
1226 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1227 where pugu.assignment_id = assign_id
1228 and pugu.table_value_id = plb.latest_balance_id
1229 and pugu.old_juri_code = geocode
1230 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1231 and pugu.process_mode = g_mode
1232 and pugu.process_type = g_process_type
1233 and pugu.id = g_geo_phase_id);
1234
1235
1236 pacv_rec pacv_cur%ROWTYPE;
1237
1238 -- Rosie Monge 10/17/2005 Bug 4602222
1239 --Retrieve affected rows in the pay_balance_context_values table
1240 --using the latest_balance_id's from the pay_latest_balances
1241 --table.
1242
1243 CURSOR plbcv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
1244 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
1245 plb.assignment_action_id
1246 FROM ff_contexts fcon,
1247 pay_balance_context_values pbcv,
1248 pay_latest_balances plb
1249 WHERE plb.assignment_id = assign_id
1250 AND pbcv.latest_balance_id = plb.latest_balance_id
1251 AND pbcv.value = geocode
1252 AND fcon.context_id = pbcv.context_id
1253 AND fcon.context_name = 'JURISDICTION_CODE'
1254 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1255 where pugu.assignment_id = assign_id
1256 and pugu.table_value_id = plb.latest_balance_id
1257 and pugu.old_juri_code = geocode
1258 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1259 and pugu.process_mode = g_mode
1260 and pugu.process_type = g_process_type
1261 and pugu.id = g_geo_phase_id);
1262
1263
1264 plbcv_rec pacv_cur%ROWTYPE;
1265
1266
1267 -- This cursor will check if a particular assignment is errored.
1268 CURSOR chk_assign_error_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1269 IS
1270 SELECT 'Y' from PAY_US_GEO_UPDATE pugu
1271 WHERE pugu.assignment_id = p_assign_id
1272 AND pugu.process_mode = g_mode
1273 AND pugu.id = g_geo_phase_id
1274 AND pugu.table_name is null
1275 AND pugu.status = 'P'
1276 AND pugu.new_juri_code = p_new_juri_code
1277 AND pugu.old_juri_code = p_old_juri_code;
1278
1279 l_chk_assign_error varchar2(4);
1280 l_error_text varchar2(1000);
1281
1282 -- This cursor will check if a particular assignment needs to be upgraded via the api.
1283 -- If it does then we will update the status to 'A' in the main procedure.
1284
1285 CURSOR chk_assign_api_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1286 IS select distinct 'Y'
1287 from pay_us_geo_update pugu
1288 where pugu.process_type in ('SU','US')
1289 and pugu.table_name is null
1290 and pugu.process_mode = g_mode
1291 and pugu.id = g_geo_phase_id
1292 and pugu.assignment_id = p_assign_id
1293 and pugu.table_name is null
1294 and pugu.table_value_id is null
1295 and pugu.old_juri_code = p_old_juri_code
1296 and pugu.new_juri_code = p_new_juri_code
1297 and NOT EXISTS (select 'Y' from pay_us_modified_geocodes pmod
1298 where pmod.state_code = substr(pugu.new_juri_code,1,2)
1299 and pmod.county_code = substr(pugu.new_juri_code,4,3)
1300 and pmod.old_city_code = substr(pugu.old_juri_code,8)
1301 and pmod.new_city_code = substr(pugu.new_juri_code,8)
1302 and pmod.process_type not in ('SU','US')
1303 and pmod.patch_name = p_patch_name);
1304
1305 l_chk_assign_api varchar2(4);
1306
1307
1308 sql_cursor INTEGER;
1309 ret INTEGER;
1310 table_exist NUMBER(1) := 0;
1311 tab_name VARCHAR2(30) := 'PAY_US_ASG_REPORTING';
1312 l_text VARCHAR2(2000);
1313 l_proc_stage VARCHAR2(240);
1314
1315 --
1316 --
1317 --
1318 --Bug 2996546 PROCEDURE load_input_values loads input_value_id
1319 --from pay_input_values_f into a pl/sql table input_val_cur
1320 -- for both seeded and non-seeded in US and Canada Legislations
1321 --
1322 --
1323 --
1324 PROCEDURE load_input_values IS
1325 Begin
1326
1327 for piv_rec in (
1328 select piv.input_value_id
1329 from pay_input_values_f piv
1330 where piv.name in ('Jurisdiction', 'jd_rs', 'jd_wk')
1331 and ( (piv.legislation_code in( 'US', 'CA')
1332 )
1333 OR (piv.legislation_code is null
1334 and piv.business_group_id is not null
1335 and exists (select 'Y'
1336 from hr_organization_information hoi
1337 where hoi.organization_id = piv.business_group_id
1338 and hoi.org_information_context = 'Business Group Information'
1339 and hoi.org_information9 in ('US','CA')
1340 )
1341 )
1342 )
1343 )
1344 Loop
1345 l_counter := l_counter+1;
1346 input_val_cur(l_counter):= piv_rec.input_value_id;
1347
1348 end Loop;
1349 l_total := l_counter;
1350
1351
1352 END load_input_values;
1353
1354 -- This procedure will update pay balance batch lines for a particular assignment id
1355 -- Note the cursor use here. Where we have the cursor in before both write messages
1356 -- this is because if we want to run in debug mode and the type is not SU and US the
1357 -- sql rowcount wont work because we are bypassing the update.
1358
1359 PROCEDURE balance_batch_lines(p_proc_type IN VARCHAR2,
1360 p_person_id IN NUMBER,
1361 p_assign_id IN NUMBER,
1362 p_old_juri_code IN VARCHAR2,
1363 p_new_juri_code IN VARCHAR2)
1364
1365 IS
1366
1367 CURSOR bal_batch_cur(p_new_juri_code varchar2, p_old_juri_code varchar2, p_assign_id number)
1368 IS select 'Y'
1369 FROM pay_balance_batch_lines
1370 WHERE jurisdiction_code = p_old_juri_code
1371 AND assignment_id = p_assign_id
1372 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1373 where pugu.assignment_id = p_assign_id
1374 and pugu.old_juri_code = p_old_juri_code
1375 and pugu.new_juri_code = p_new_juri_code
1376 and pugu.table_name = 'PAY_BALANCE_BATCH_LINES'
1377 and pugu.process_mode = g_mode
1378 and pugu.process_type = g_process_type
1379 and pugu.id = g_geo_phase_id);
1380
1381 l_bal_batch_exist varchar2(2);
1382
1383
1384 BEGIN
1385
1386 hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_batch_lines');
1387
1388 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1389
1390 hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
1391
1392 IF G_MODE = 'UPGRADE' THEN
1393
1394 UPDATE pay_balance_batch_lines
1395 SET jurisdiction_code = p_new_juri_code
1396 WHERE jurisdiction_code = p_old_juri_code
1397 AND assignment_id = p_assign_id;
1398
1399 END IF;
1400
1401 -- Write a message to the table to be later spooled to a report
1402
1403 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1404
1405 OPEN bal_batch_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1406 FETCH bal_batch_cur into l_bal_batch_exist;
1407 IF bal_batch_cur%FOUND THEN
1408
1409 write_message(
1410 p_proc_type => p_proc_type,
1411 p_person_id => p_person_id,
1412 p_assign_id => p_assign_id,
1413 p_old_juri_code => p_old_juri_code,
1414 p_new_juri_code => p_new_juri_code,
1415 p_location => 'PAY_BALANCE_BATCH_LINES',
1416 p_id => p_assign_id);
1417
1418 END IF;
1419 CLOSE bal_batch_cur;
1420
1421 /*END IF;*/
1422
1423 ELSE
1424
1425 -- Write a message to the table to be later spooled to a report
1426
1427 OPEN bal_batch_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1428 FETCH bal_batch_cur into l_bal_batch_exist;
1429 IF bal_batch_cur%FOUND THEN
1430
1431 write_message(
1432 p_proc_type => p_proc_type,
1433 p_person_id => p_person_id,
1434 p_assign_id => p_assign_id,
1435 p_old_juri_code => p_old_juri_code,
1436 p_new_juri_code => p_new_juri_code,
1437 p_location => 'PAY_BALANCE_BATCH_LINES',
1438 p_id => p_assign_id);
1439
1440
1441 END IF;
1442 CLOSE bal_batch_cur;
1443
1444 END IF;
1445
1446 END balance_batch_lines;
1447 ---
1448 ---
1449 ---
1450 --Per bug 2738574
1451 -- This procedure will update pay_run_balances for a particular assignment id
1452
1453 PROCEDURE pay_run_balances (p_proc_type IN VARCHAR2,
1454 p_person_id IN NUMBER,
1455 p_assign_id IN NUMBER,
1456 p_new_city_code IN VARCHAR2,
1457 p_old_juri_code IN VARCHAR2,
1458 p_new_juri_code IN VARCHAR2)
1459
1460 IS
1461
1462 CURSOR run_balance_cur(p_new_juri_code varchar2, p_old_juri_code varchar2, p_assign_id number)
1463 IS select 'Y'
1464 FROM pay_run_balances
1465 WHERE jurisdiction_code = p_old_juri_code
1466 AND assignment_id = p_assign_id
1467 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1468 where pugu.assignment_id = p_assign_id
1469 and pugu.old_juri_code = p_old_juri_code
1470 and pugu.new_juri_code = p_new_juri_code
1471 and pugu.table_name = 'PAY_RUN_BALANCES'
1472 and pugu.process_mode = g_mode
1473 and pugu.process_type = g_process_type
1474 and pugu.id = g_geo_phase_id);
1475
1476 l_run_balance_exist varchar2(2);
1477 l_run_balance_count number;
1478
1479 BEGIN
1480
1481 hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_run_balances');
1482
1483 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1484
1485 hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
1486
1487 IF G_MODE = 'UPGRADE' THEN
1488
1489 UPDATE pay_run_balances
1490 SET jurisdiction_code = p_new_juri_code,
1491 jurisdiction_comp3 = p_new_city_code
1492 WHERE assignment_id = p_assign_id
1493 AND jurisdiction_code = p_old_juri_code ;
1494
1495 IF SQL%ROWCOUNT > 0 THEN
1496
1497 write_message(
1498 p_proc_type => p_proc_type,
1499 p_person_id => p_person_id,
1500 p_assign_id => p_assign_id,
1501 p_old_juri_code => p_old_juri_code,
1502 p_new_juri_code => p_new_juri_code,
1503 p_location => 'PAY_RUN_BALANCES',
1504 p_id => p_assign_id);
1505
1506 END IF;
1507
1508 ELSE
1509
1510 -- Write a message to the table to be later spooled to a report
1511
1512 l_run_balance_count := 0;
1513
1514 SELECT count(*)
1515 INTO l_run_balance_count
1516 FROM pay_run_balances prb,
1517 pay_assignment_actions paa
1518 WHERE paa.assignment_id = p_assign_id
1519 AND paa.assignment_action_id = prb.assignment_action_id
1520 AND paa.assignment_id = prb.assignment_id
1521 AND prb.jurisdiction_code = p_old_juri_code
1522 AND rownum = 1;
1523
1524 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1525
1526 IF l_run_balance_count > 0 THEN
1527
1528 write_message(
1529 p_proc_type => p_proc_type,
1530 p_person_id => p_person_id,
1531 p_assign_id => p_assign_id,
1532 p_old_juri_code => p_old_juri_code,
1533 p_new_juri_code => p_new_juri_code,
1534 p_location => 'PAY_RUN_BALANCES',
1535 p_id => p_assign_id);
1536
1537
1538 END IF;
1539
1540 END IF;
1541
1542 ELSE
1543
1544 -- Write a message to the table to be later spooled to a report
1545
1546 OPEN run_balance_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1547 FETCH run_balance_cur into l_run_balance_exist;
1548 IF run_balance_cur%FOUND THEN
1549
1550 write_message(
1551 p_proc_type => p_proc_type,
1552 p_person_id => p_person_id,
1553 p_assign_id => p_assign_id,
1554 p_old_juri_code => p_old_juri_code,
1555 p_new_juri_code => p_new_juri_code,
1556 p_location => 'PAY_RUN_BALANCES',
1557 p_id => p_assign_id);
1558
1559
1560 END IF;
1561 CLOSE run_balance_cur;
1562
1563 END IF;
1564
1565 END pay_run_balances ;
1566
1567 ---
1568 ---
1569 ---
1570
1571
1572 -- This procedure will update the city tax records for a particular assignment id
1573 PROCEDURE city_tax_records (p_proc_type IN VARCHAR2,
1574 p_person_id IN NUMBER,
1575 p_assign_id IN NUMBER,
1576 p_old_juri_code IN VARCHAR2,
1577 p_new_juri_code IN VARCHAR2,
1578 p_new_city_code IN VARCHAR2,
1579 p_city_tax_record_id IN NUMBER)
1580
1581
1582 IS
1583
1584 BEGIN
1585
1586 hr_utility.trace('Entering pay_us_geo_upd_pkg.city_tax_records');
1587
1588 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1589
1590 hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1591
1592 IF G_MODE = 'UPGRADE' THEN
1593
1594 UPDATE pay_us_emp_city_tax_rules_f
1595 SET jurisdiction_code = p_new_juri_code,
1596 city_code = p_new_city_code
1597 WHERE jurisdiction_code = p_old_juri_code
1598 AND assignment_id = p_assign_id
1599 AND emp_city_tax_rule_id = p_city_tax_record_id
1600 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1601 where pugu.assignment_id = p_assign_id
1602 and pugu.table_value_id = p_city_tax_record_id
1603 and pugu.old_juri_code = p_old_juri_code
1604 and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1605 and pugu.process_mode = g_mode
1606 and pugu.process_type = g_process_type
1607 and pugu.id = g_geo_phase_id);
1608
1609
1610 END IF;
1611 -- Write a message to the table to be later spooled to a report
1612
1613 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1614
1615 write_message(
1616 p_proc_type => p_proc_type,
1617 p_person_id => p_person_id,
1618 p_assign_id => p_assign_id,
1619 p_old_juri_code => p_old_juri_code,
1620 p_new_juri_code => p_new_juri_code,
1621 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1622 p_id => p_city_tax_record_id);
1623
1624 /*END IF;*/
1625 ELSE
1626
1627 -- Write a message to the table to be later spooled to a report
1628
1629
1630 write_message(
1631 p_proc_type => p_proc_type,
1632 p_person_id => p_person_id,
1633 p_assign_id => p_assign_id,
1634 p_old_juri_code => p_old_juri_code,
1635 p_new_juri_code => p_new_juri_code,
1636 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1637 p_id => p_city_tax_record_id);
1638
1639 END IF;
1640
1641 END city_tax_records;
1642
1643 PROCEDURE del_dup_city_tax_recs IS
1644
1645 -- This cursor identifies assignment id/jurisdiction pairs that have multiple
1646 -- rows in the pay_us_emp_city_tax_rules_f table created by geocode updates.
1647 -- For example, prior to geocode patch 1105095, these geocodes were in place:
1648 -- Van Nuys, CA: 05-037-3880
1649 -- Woodland Hills, CA: 05-037-6080
1650 -- Los Angeles, CA: 05-037-1900
1651 -- Patch 1105095 updated the first two geocodes to 05-037-1900, so a person that
1652 -- previously had distinct city tax records for any two or all three of the above
1653 -- geocodes would now have multiple tax records for geocode 05-037-1900, each
1654 -- with a different city_tax_rule_id.
1655
1656
1657 -- Bug 3319878 -- Changed the query to reduce the cost of the query
1658 CURSOR dup_city_tax_rows is
1659 select distinct pect1.assignment_id, pect1.jurisdiction_code
1660 from pay_us_emp_city_tax_rules_f pect1,
1661 pay_us_emp_city_tax_rules_f pect2
1662 where pect1.assignment_id = pect2.assignment_id
1663 and pect1.jurisdiction_code = pect2.jurisdiction_code
1664 and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id
1665 and pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
1666
1667 /* select distinct pect1.assignment_id, pect1.jurisdiction_code
1668 from pay_us_emp_city_tax_rules_f pect1
1669 where pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END
1670 and pect1.emp_city_tax_rule_id <
1671 (select pect2.emp_city_tax_rule_id
1672 from pay_us_emp_city_tax_rules_f pect2
1673 where pect1.assignment_id = pect2.assignment_id
1674 and pect1.jurisdiction_code = pect2.jurisdiction_code
1675 );
1676 */
1677
1678 BEGIN
1679
1680 hr_utility.trace('Entering pay_us_geo_upd_pkg.del_dup_city_tax_recs');
1681
1682 IF G_MODE = 'UPGRADE' THEN
1683
1684 FOR dup_rec IN dup_city_tax_rows
1685
1686 LOOP
1687
1688 hr_utility.trace('Deleting dups for Assign ID: ' || to_char(dup_rec.assignment_id) ||
1689 ' Geocode: ' || dup_rec.jurisdiction_code);
1690
1691 DELETE FROM pay_us_emp_city_tax_rules_f pecto
1692 WHERE pecto.rowid < (SELECT max(pecti.rowid)
1693 FROM pay_us_emp_city_tax_rules_f pecti
1694 WHERE pecti.assignment_id = pecto.assignment_id
1695 AND pecti.assignment_id = dup_rec.assignment_id
1696 AND pecti.jurisdiction_code = pecto.jurisdiction_code
1697 AND pecti.jurisdiction_code = dup_rec.jurisdiction_code
1698 AND pecti.emp_city_tax_rule_id <> pecto.emp_city_tax_rule_id)
1699 AND pecto.assignment_id = dup_rec.assignment_id;
1700
1701 END LOOP;
1702
1703 END IF;
1704
1705 END del_dup_city_tax_recs;
1706
1707 -- This procedure will update the run results for a particular assignment action id and
1708 -- a particulare run result id.
1709 -- NOTE where we have the cursor in before both write messages
1710 -- this is because if we want to run in debug mode and the type is not SU and US the
1711 -- sql rowcount wont work because we are bypassing the update.
1712
1713 PROCEDURE run_results(p_proc_type IN VARCHAR2,
1714 p_person_id IN NUMBER,
1715 p_assign_id IN NUMBER,
1716 p_assign_act_id IN NUMBER,
1717 p_run_result_id IN NUMBER,
1718 p_old_juri_code IN VARCHAR2,
1719 p_new_juri_code IN VARCHAR2)
1720
1721
1722 IS
1723
1724 --
1725 --Per bug 2996546 changed the where clause in
1726 --cursor ele_run_result_val for piv.legislation_code = 'US'
1727 --to use the function IS_US_OR_CA_LEGISLATION and compare
1728 --input value id stored in pl/sql table to improve performance
1729 --
1730
1731 cursor ele_run_result_val(p_new_juri_code VARCHAR2, p_run_result_id VARCHAR2, p_assign_act_id NUMBER,p_assign_id NUMBER)
1732 IS select distinct 'Y'
1733 FROM pay_run_result_values prv
1734 WHERE prv.result_value = p_new_juri_code
1735 AND prv.run_result_id = p_run_result_id
1736 AND EXISTS (SELECT 0
1737 FROM pay_input_values_f piv
1738 WHERE piv.input_value_id = prv.input_value_id
1739 AND (piv.name = 'Jurisdiction' OR
1740 piv.name = 'jd_rs' OR
1741 piv.name = 'jd_wk')
1742 -- AND piv.legislation_code = 'US')
1743 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id )
1744 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1745 where pugu.assignment_id = p_assign_id
1746 and pugu.table_value_id = prv.run_result_id
1747 and pugu.old_juri_code = p_old_juri_code
1748 and pugu.table_name = 'PAY_RUN_RESULT_VALUES'
1749 and pugu.process_mode = g_mode
1750 and pugu.process_type = g_process_type
1751 and pugu.id = g_geo_phase_id);
1752
1753
1754 cursor ele_run_results(p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2, p_run_result_id VARCHAR2,
1755 p_assign_act_id NUMBER)
1756 IS select distinct 'Y'
1757 FROM pay_run_results prr
1758 WHERE prr.jurisdiction_code = p_new_juri_code
1759 AND prr.run_result_id = p_run_result_id
1760 AND prr.assignment_action_id = p_assign_act_id
1761 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1762 where pugu.assignment_id = p_assign_id
1763 and pugu.table_value_id = prr.run_result_id
1764 and pugu.old_juri_code = p_old_juri_code
1765 and pugu.table_name = 'PAY_RUN_RESULTS'
1766 and pugu.process_mode = g_mode
1767 and pugu.process_type = g_process_type
1768 and pugu.id = g_geo_phase_id);
1769
1770
1771 l_flag varchar2(2);
1772
1773 BEGIN
1774
1775 hr_utility.trace('Entering pay_us_geo_upd_pkg.run_results');
1776
1777 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1778
1779 hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1780
1781 --
1782 --Per bug 2996546 changed the where clause in
1783 --in the update of pay_run_result_values
1784 --for piv.legislation_code = 'US'
1785 --to use the function IS_US_OR_CA_LEGISLATION and compare
1786 --input value id stored in pl/sql table to improve performance
1787 --
1788
1789
1790 IF G_MODE = 'UPGRADE' THEN
1791
1792 UPDATE pay_run_result_values prv
1793 SET prv.result_value = p_new_juri_code
1794 WHERE prv.run_result_id = p_run_result_id
1795 AND prv.result_value = p_old_juri_code
1796 AND EXISTS (SELECT 0
1797 FROM pay_input_values_f piv
1798 WHERE piv.input_value_id = prv.input_value_id
1799 AND (piv.name = 'Jurisdiction' OR
1800 piv.name = 'jd_rs' OR
1801 piv.name = 'jd_wk')
1802 -- AND piv.legislation_code = 'US');
1803 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id );
1804
1805 -- Write a message to the table to be later spooled to a report
1806 -- only if a row was updated
1807
1808 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1809
1810 OPEN ele_run_result_val(p_new_juri_code, p_run_result_id, p_assign_act_id, p_assign_id);
1811 FETCH ele_run_result_val INTO l_flag;
1812
1813 IF ele_run_result_val%FOUND THEN
1814
1815
1816 write_message(
1817 p_proc_type => p_proc_type,
1818 p_person_id => p_person_id,
1819 p_assign_id => p_assign_id,
1820 p_old_juri_code => p_old_juri_code,
1821 p_new_juri_code => p_new_juri_code,
1822 p_location => 'PAY_RUN_RESULT_VALUES',
1823 p_id => p_run_result_id);
1824
1825 CLOSE ele_run_result_val;
1826
1827 END IF;
1828
1829 /*END IF;*/
1830 ELSE
1831
1832 --For the Debug Mode Since we will not be updating the run_result_values records, the records will still
1833 --be having old jurisdiction_code only.So we need to look for old jurisdiction code to show records in
1834 --output under Debug Mode
1835
1836 OPEN ele_run_result_val(p_old_juri_code, p_run_result_id, p_assign_act_id, p_assign_id);
1837 FETCH ele_run_result_val INTO l_flag;
1838
1839 IF ele_run_result_val%FOUND THEN
1840
1841
1842 write_message(
1843 p_proc_type => p_proc_type,
1844 p_person_id => p_person_id,
1845 p_assign_id => p_assign_id,
1846 p_old_juri_code => p_old_juri_code,
1847 p_new_juri_code => p_new_juri_code,
1848 p_location => 'PAY_RUN_RESULT_VALUES',
1849 p_id => p_run_result_id);
1850
1851 CLOSE ele_run_result_val;
1852
1853 END IF;
1854
1855 END IF;
1856
1857 hr_utility.trace('After update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1858
1859 ELSE
1860
1861 -- Write a message to the table to be later spooled to a report
1862 OPEN ele_run_result_val(p_new_juri_code, p_run_result_id, p_assign_act_id, p_assign_id);
1863 FETCH ele_run_result_val INTO l_flag;
1864
1865 IF ele_run_result_val%FOUND THEN
1866
1867 write_message(
1868 p_proc_type => p_proc_type,
1869 p_person_id => p_person_id,
1870 p_assign_id => p_assign_id,
1871 p_old_juri_code => p_old_juri_code,
1872 p_new_juri_code => p_new_juri_code,
1873 p_location => 'PAY_RUN_RESULT_VALUES',
1874 p_id => p_run_result_id);
1875
1876 END IF;
1877
1878 CLOSE ele_run_result_val;
1879
1880 END IF;
1881
1882
1883 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1884
1885 hr_utility.trace('Before update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1886
1887 IF G_MODE = 'UPGRADE' THEN
1888
1889 UPDATE pay_run_results
1890 SET jurisdiction_code = p_new_juri_code
1891 WHERE jurisdiction_code = p_old_juri_code
1892 AND run_result_id = p_run_result_id
1893 AND assignment_action_id = p_assign_act_id;
1894
1895 -- Write a message to the table to be later spooled to a report
1896
1897 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1898
1899 OPEN ele_run_results(p_new_juri_code, p_old_juri_code, p_run_result_id, p_assign_act_id);
1900 FETCH ele_run_results INTO l_flag;
1901
1902 IF ele_run_results%FOUND THEN
1903
1904 write_message(
1905 p_proc_type => p_proc_type,
1906 p_person_id => p_person_id,
1907 p_assign_id => p_assign_id,
1908 p_old_juri_code => p_old_juri_code,
1909 p_new_juri_code => p_new_juri_code,
1910 p_location => 'PAY_RUN_RESULTS',
1911 p_id => p_run_result_id);
1912
1913 CLOSE ele_run_results;
1914
1915 END IF;
1916
1917 /*END IF;*/
1918
1919 ELSE
1920
1921 --For the Debug Mode Since we will not be updating the run_results records, the records will still
1922 --be having old jurisdiction_code only.So we need to look for old jurisdiction code to show records in
1923 --output under Debug Mode
1924
1925 OPEN ele_run_results(p_old_juri_code, p_old_juri_code, p_run_result_id, p_assign_act_id);
1926 FETCH ele_run_results INTO l_flag;
1927
1928 IF ele_run_results%FOUND THEN
1929
1930 write_message(
1931 p_proc_type => p_proc_type,
1932 p_person_id => p_person_id,
1933 p_assign_id => p_assign_id,
1934 p_old_juri_code => p_old_juri_code,
1935 p_new_juri_code => p_new_juri_code,
1936 p_location => 'PAY_RUN_RESULTS',
1937 p_id => p_run_result_id);
1938
1939 CLOSE ele_run_results;
1940
1941 END IF;
1942
1943 END IF;
1944
1945
1946
1947 ELSE
1948
1949 OPEN ele_run_results(p_new_juri_code, p_old_juri_code, p_run_result_id, p_assign_act_id);
1950 FETCH ele_run_results INTO l_flag;
1951
1952 IF ele_run_results%FOUND THEN
1953
1954 write_message(
1955 p_proc_type => p_proc_type,
1956 p_person_id => p_person_id,
1957 p_assign_id => p_assign_id,
1958 p_old_juri_code => p_old_juri_code,
1959 p_new_juri_code => p_new_juri_code,
1960 p_location => 'PAY_RUN_RESULTS',
1961 p_id => p_run_result_id);
1962
1963 END IF;
1964
1965 CLOSE ele_run_results;
1966 END IF;
1967
1968
1969 hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1970
1971 hr_utility.trace('Exiting pay_us_geo_upd_pkg.run_results');
1972
1973 END run_results;
1974 --
1975 --
1976 --Per bug 2996546
1977 -- This procedure, PROCEDURE pay_action_contexts, will update the context values
1978 --based on assignment_action_id
1979 --
1980 --
1981 PROCEDURE pay_action_contexts
1982 (p_proc_type IN VARCHAR2,
1983 p_person_id IN NUMBER,
1984 p_assign_id IN NUMBER,
1985 p_assign_act_id IN NUMBER,
1986 p_context_id IN NUMBER,
1987 p_old_juri_code IN VARCHAR2,
1988 p_new_juri_code IN VARCHAR2)
1989 IS
1990
1991 CURSOR pac_inside_cur(p_assign_act_id number, p_assign_id number,
1992 p_context_id number, p_old_juri_code varchar2)
1993 IS select 'Y'
1994 FROM pay_action_contexts
1995 WHERE assignment_action_id = p_assign_act_id
1996 AND assignment_id = p_assign_id
1997 AND context_id = p_context_id
1998 AND context_value = p_old_juri_code ;
1999
2000 l_pac_inside_exist varchar2(2);
2001
2002 BEGIN
2003
2004 hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_action_contexts');
2005
2006 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2007
2008 hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
2009
2010 IF G_MODE = 'UPGRADE' THEN
2011
2012 UPDATE pay_action_contexts
2013 SET context_value = p_new_juri_code
2014 WHERE context_value = p_old_juri_code
2015 AND assignment_action_id = p_assign_act_id
2016 AND context_id = p_context_id ;
2017 END IF;
2018
2019 -- Write a message to the table to be later spooled to a report
2020
2021 /* IF SQL%ROWCOUNT > 0 THEN 6864396 */
2022
2023 OPEN pac_inside_cur(p_assign_act_id , p_assign_id,
2024 p_context_id, p_old_juri_code ) ;
2025 FETCH pac_inside_cur into l_pac_inside_exist;
2026 IF pac_inside_cur%FOUND THEN
2027 write_message(
2028 p_proc_type => p_proc_type,
2029 p_person_id => p_person_id,
2030 p_assign_id => p_assign_id,
2031 p_old_juri_code => p_old_juri_code,
2032 p_new_juri_code => p_new_juri_code,
2033 p_location => 'PAY_ACTION_CONTEXTS',
2034 p_id => p_assign_id);
2035
2036 END IF;
2037 CLOSE pac_inside_cur;
2038 /*END IF ;*/
2039 ELSE
2040
2041 -- Write a message to the table to be later spooled to a report
2042
2043 OPEN pac_inside_cur(p_assign_act_id, p_assign_id ,
2044 p_context_id , p_old_juri_code ) ;
2045 FETCH pac_inside_cur into l_pac_inside_exist;
2046 IF pac_inside_cur%FOUND THEN
2047 write_message(
2048 p_proc_type => p_proc_type,
2049 p_person_id => p_person_id,
2050 p_assign_id => p_assign_id,
2051 p_old_juri_code => p_old_juri_code,
2052 p_new_juri_code => p_new_juri_code,
2053 p_location => 'PAY_ACTION_CONTEXTS',
2054 p_id => p_assign_id);
2055
2056 END IF;
2057 CLOSE pac_inside_cur;
2058
2059 END IF;
2060
2061 END pay_action_contexts;
2062 ---
2063 ---
2064 ---
2065
2066 -- This procedure will update the archive item contexts based on assignment_action_id
2067 -- We do not do a mass update on the contexts here because we want to be certain that
2068 -- the geocodes updated have a corresponding tax record for the assignment.
2069 -- Not every old juri code will have an archived item against it for that particular
2070 -- assignment action. But we still have to check to make sure.
2071
2072 PROCEDURE archive_item_contexts (p_proc_type IN VARCHAR2,
2073 p_person_id IN NUMBER,
2074 p_assign_id IN NUMBER,
2075 p_archive_item_id IN NUMBER,
2076 p_context_id IN NUMBER,
2077 p_old_juri_code IN VARCHAR2,
2078 p_new_juri_code IN VARCHAR2)
2079
2080 IS
2081
2082 BEGIN
2083
2084 hr_utility.trace('Entering pay_us_geo_upd_pkg.archive_item_contexts');
2085
2086 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2087
2088 hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
2089
2090 IF G_MODE = 'UPGRADE' THEN
2091
2092 UPDATE ff_archive_item_contexts ffaic
2093 SET ffaic.context = p_new_juri_code
2094 WHERE ffaic.context = p_old_juri_code
2095 AND ffaic.context_id = p_context_id
2096 AND ffaic.archive_item_id = p_archive_item_id;
2097
2098 END IF;
2099
2100 hr_utility.trace('After update of archive item contexts for assignment_id: '||to_char(p_assign_id));
2101
2102 -- Write a message to the table to be later spooled to a report
2103
2104 write_message(
2105 p_proc_type => p_proc_type,
2106 p_person_id => p_person_id,
2107 p_assign_id => p_assign_id,
2108 p_old_juri_code => p_old_juri_code,
2109 p_new_juri_code => p_new_juri_code,
2110 p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
2111 p_id => p_archive_item_id);
2112
2113
2114 ELSE
2115
2116 -- Write a message to the table to be later spooled to a report
2117
2118 write_message(
2119 p_proc_type => p_proc_type,
2120 p_person_id => p_person_id,
2121 p_assign_id => p_assign_id,
2122 p_old_juri_code => p_old_juri_code,
2123 p_new_juri_code => p_new_juri_code,
2124 p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
2125 p_id => p_archive_item_id);
2126
2127 END IF;
2128
2129 hr_utility.trace('Exiting pay_us_geo_upd_pkg.archive_item_contexts');
2130
2131
2132
2133 END archive_item_contexts;
2134
2135
2136 -- This procedure will upgrade the element entry geocodes.
2137
2138 PROCEDURE element_entries(
2139 p_proc_type IN VARCHAR2,
2140 p_person_id IN NUMBER,
2141 p_assign_id IN NUMBER,
2142 p_input_value_id IN NUMBER,
2143 p_ele_ent_id IN NUMBER,
2144 p_old_juri_code IN VARCHAR2,
2145 p_new_juri_code IN VARCHAR2)
2146
2147
2148 IS
2149
2150 BEGIN
2151 hr_utility.trace('Entering pay_us_geo_upd_pkg.element_entries');
2152
2153 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2154
2155 hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
2156
2157 IF G_MODE = 'UPGRADE' THEN
2158
2159 UPDATE pay_element_entry_values_f
2160 SET screen_entry_value = p_new_juri_code
2161 WHERE screen_entry_value = p_old_juri_code
2162 AND input_value_id+0 = p_input_value_id
2163 AND element_entry_id = p_ele_ent_id;
2164
2165 END IF;
2166 hr_utility.trace('After update of element entry values for assignment_id: '||to_char(p_assign_id));
2167
2168 -- Write a message to the table to be later spooled to a report
2169
2170 write_message(
2171 p_proc_type => p_proc_type,
2172 p_person_id => p_person_id,
2173 p_assign_id => p_assign_id,
2174 p_old_juri_code => p_old_juri_code,
2175 p_new_juri_code => p_new_juri_code,
2176 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2177 p_id => p_ele_ent_id);
2178
2179 ELSE
2180
2181 -- Write a message to the table to be later spooled to a report
2182
2183 write_message(
2184 p_proc_type => p_proc_type,
2185 p_person_id => p_person_id,
2186 p_assign_id => p_assign_id,
2187 p_old_juri_code => p_old_juri_code,
2188 p_new_juri_code => p_new_juri_code,
2189 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2190 p_id => p_ele_ent_id);
2191
2192 END IF;
2193
2194 hr_utility.trace('Exiting pay_us_geo_upd_pkg.element_entries');
2195
2196
2197
2198 END element_entries;
2199
2200
2201 -- This procedure will upgrade the latest balance contexts.
2202
2203 PROCEDURE balance_contexts(p_proc_type IN VARCHAR2,
2204 p_person_id IN NUMBER,
2205 p_assign_id IN NUMBER,
2206 p_assign_act_id IN NUMBER,
2207 p_context_id IN NUMBER,
2208 p_lat_bal_id IN NUMBER,
2209 p_old_juri_code IN VARCHAR2,
2210 p_new_juri_code IN VARCHAR2)
2211
2212
2213 IS
2214
2215 BEGIN
2216
2217 hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_contexts');
2218
2219 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
2220
2221 hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2222
2223 IF G_MODE = 'UPGRADE' THEN
2224
2225 UPDATE pay_balance_context_values
2226 SET value = p_new_juri_code
2227 WHERE value = p_old_juri_code
2228 AND context_id = p_context_id
2229 AND latest_balance_id = p_lat_bal_id;
2230
2231 END IF;
2232 -- Write a message to the table to be later spooled to a report
2233
2234 write_message(
2235 p_proc_type => p_proc_type,
2236 p_person_id => p_person_id,
2237 p_assign_id => p_assign_id,
2238 p_old_juri_code => p_old_juri_code,
2239 p_new_juri_code => p_new_juri_code,
2240 p_location => 'PAY_BALANCE_CONTEXT_VALUES',
2241 p_id => p_lat_bal_id);
2242
2243 ELSE
2244
2245 -- Write a message to the table to be later spooled to a report
2246
2247 write_message(
2248 p_proc_type => p_proc_type,
2249 p_person_id => p_person_id,
2250 p_assign_id => p_assign_id,
2251 p_old_juri_code => p_old_juri_code,
2252 p_new_juri_code => p_new_juri_code,
2253 p_location => 'PAY_BALANCE_CONTEXT_VALUES',
2254 p_id => p_lat_bal_id);
2255
2256 END IF;
2257
2258 hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
2259
2260
2261 hr_utility.trace('Exiting pay_us_geo_upd_pkg.balance_contexts');
2262
2263 END balance_contexts;
2264
2265
2266 -- This procedure will take out duplicate VERTEX element entries and add the percentages
2267 -- of the previously duplicated element entries togethor
2268 -- This used to be script pydeldup.sql earlier
2269
2270 PROCEDURE duplicate_vertex_ee(p_assignment_id IN NUMBER)
2271
2272 IS
2273
2274 -- This cursor will get us the element entries of the assignments processed
2275 cursor csr_get_dup (p_assignment number) is
2276 select pev.screen_entry_value sev, pev.element_entry_id eei
2277 from pay_element_entry_values_f pev,
2278 pay_input_values_f piv,
2279 pay_element_types_f pet,
2280 pay_element_links_f pel,
2281 pay_element_entries_f pee
2282 where pee.assignment_id = p_assignment
2283 and pel.element_link_id = pee.element_link_id
2284 and pet.element_type_id = pel.element_type_id
2285 and pet.element_name = 'VERTEX'
2286 and pev.element_entry_id = pee.element_entry_id
2287 and pev.screen_entry_value is not null
2288 and piv.input_value_id = pev.input_value_id+0
2289 and piv.element_type_id = pet.element_type_id
2290 and piv.name = 'Jurisdiction'
2291 and piv.legislation_code = 'US'
2292 and pet.legislation_code = 'US'
2293 order by 1,2;
2294
2295 cursor csr_get_percentage (p_element_entry_id NUMBER) is
2296 select /*Bug 7592909*/distinct pev.screen_entry_value , pev.effective_start_date,
2297 pev.effective_end_date
2298 from pay_element_entry_values_f pev,
2299 pay_input_values_f piv,
2300 pay_element_types_f pet,
2301 pay_element_links_f pel,
2302 pay_element_entries_f pef
2303 where pef.element_entry_id = p_element_entry_id
2304 and pel.element_link_id = pef.element_link_id
2305 and pet.element_type_id = pel.element_type_id
2306 and pet.element_name = 'VERTEX'
2307 and pev.element_entry_id = pef.element_entry_id
2308 and pev.screen_entry_value is not null
2309 and piv.input_value_id = pev.input_value_id+0
2310 and piv.element_type_id = pet.element_type_id
2311 and piv.name = 'Percentage'
2312 and piv.legislation_code = 'US'
2313 and pet.legislation_code = 'US';
2314
2315 l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2316 l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2317 l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2318 l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2319 l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2320
2321 BEGIN
2322
2323 hr_utility.trace('Entering pay_us_geo_upd_pkg.duplicate_vertex_ee');
2324
2325 l_prev_screen := null;
2326 l_prev_eleid := null;
2327
2328 for j in csr_get_dup(p_assignment_id) loop
2329
2330 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 1);
2331
2332 if j.sev = l_prev_screen and j.eei <> l_prev_eleid then
2333 hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2334 ||' is a duplicate of : ' || to_char(l_prev_eleid)
2335 ||' for assignment_id : ' || to_char(p_assignment_id));
2336
2337 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
2338
2339 /* get the percentages for the record to be deleted */
2340 open csr_get_percentage(j.eei);
2341 loop
2342
2343 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 3);
2344
2345 /* Get the %age for each datetracked record */
2346
2347 fetch csr_get_percentage into l_percent,
2348 l_effective_start_date,
2349 l_effective_end_date;
2350 exit when csr_get_percentage%NOTFOUND;
2351
2352 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 4);
2353
2354 /* Add the %age of the current element entry to the earlier
2355 entry */
2356
2357 IF G_MODE = 'UPGRADE' THEN
2358
2359 hr_utility.trace('The previous element entry id is: '||to_char(l_prev_eleid));
2360
2361 update pay_element_entry_values_f pev
2362 set pev.screen_entry_value = pev.screen_entry_value + l_percent
2363 where pev.element_entry_id = l_prev_eleid
2364 and pev.screen_entry_value is not null
2365 and pev.input_value_id = (select distinct piv.input_value_id
2366 from pay_input_values_f piv,
2367 pay_element_types_f pet,
2368 pay_element_links_f pel,
2369 pay_element_entries_f pef
2370 where pef.element_entry_id =
2371 l_prev_eleid
2372 and pel.element_link_id =
2373 pef.element_link_id
2374 and pet.element_type_id =
2375 pel.element_type_id
2376 and pet.element_name = 'VERTEX'
2377 and piv.element_type_id =
2378 pet.element_type_id
2379 and piv.name = 'Percentage'
2380 and piv.legislation_code = 'US'
2381 and pet.legislation_code = 'US')
2382 /*Bug 7592909*/
2383 and pev.effective_start_date=l_effective_start_date
2384 and pev.effective_end_date=l_effective_end_date;
2385
2386 END IF;
2387 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 5);
2388
2389 end loop;
2390 close csr_get_percentage;
2391
2392 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 6);
2393
2394 /* Now delete the current entry */
2395
2396 delete pay_element_entries_f
2397 where element_entry_id = j.eei
2398 and assignment_id = p_assignment_id;
2399
2400 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2401
2402 else
2403
2404 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2405
2406 l_prev_screen := j.sev;
2407 l_prev_eleid := j.eei;
2408
2409 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2410
2411 end if;
2412
2413 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2414
2415 end loop;
2416
2417 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2418
2419 hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2420
2421 end duplicate_vertex_ee;
2422
2423
2424 -- This procedure will create element entries for assignments that have geocodes
2425 -- which have split from the upgrade.
2426
2427 PROCEDURE insert_ele_entries
2428 (p_proc_type in varchar2,
2429 p_assign_id in number,
2430 p_person_id in number,
2431 p_new_juri_code in varchar2,
2432 p_old_juri_code in varchar2)
2433
2434 IS
2435
2436 -- Finds out if County Tax Record exists for this ASSIGNMENT_ID
2437 cursor c_county_rec (p_assignment_id in number,
2438 p_state_code in varchar2,
2439 p_county_code in varchar2) is
2440 select business_group_id
2441 from pay_us_emp_county_tax_rules_f pecot
2442 where pecot.assignment_id = p_assignment_id
2443 and pecot.state_code = p_state_code
2444 and pecot.county_code = p_county_code;
2445
2446 -- Finds out if City Tax Record exists for this ASSIGNMENT_ID
2447 cursor c_tax_rec (p_assignment_id in number,
2448 p_state_code in varchar2,
2449 p_county_code in varchar2,
2450 p_city_code in varchar2) is
2451 select business_group_id
2452 from pay_us_emp_city_tax_rules_f pect
2453 where pect.assignment_id = p_assignment_id
2454 and pect.state_code = p_state_code
2455 and pect.county_code = p_county_code
2456 and pect.city_code = p_city_code;
2457
2458 -- Gets the date when the eligiblity criteria was met for this assignment.
2459 cursor c_elig_date (p_assignment_id in number) is
2460 select min(peft.effective_start_date),
2461 max(peft.effective_end_date),
2462 peft.business_group_id
2463 from pay_us_emp_city_tax_rules_f peft
2464 where peft.assignment_id = p_assignment_id
2465 group by peft.business_group_id;
2466
2467 ld_eff_start_date date;
2468 ld_eff_end_date date;
2469 ln_state_code pay_us_emp_state_tax_rules_f.state_code%TYPE;
2470 ln_county_code pay_us_emp_county_tax_rules_f.county_code%TYPE;
2471 ln_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
2472 ln_old_city_code pay_us_modified_geocodes.old_city_code%TYPE;
2473
2474 ln_business_group_id number;
2475 ln_check number;
2476 ln_emp_city_tax_rule_id number;
2477
2478 lc_exists number;
2479 lc_insert_rec varchar2(1);
2480 l_profile_value varchar2(1):='N';
2481 BEGIN
2482
2483 hr_utility.trace('Entering pay_us_geo_upd_pkg.insert_ele_entries');
2484
2485 lc_insert_rec := 'N';
2486
2487 IF ((p_proc_type = 'SU' or p_proc_type = 'US') and p_api_mode = 'N') THEN
2488
2489 ln_state_code := substr(p_new_juri_code,1,2);
2490 ln_county_code := substr(p_new_juri_code,4,3);
2491 ln_city_code := substr(p_new_juri_code,8);
2492 ln_old_city_code := substr(p_old_juri_code,8);
2493
2494 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',1);
2495
2496 open c_county_rec(p_assign_id,
2497 ln_state_code,
2498 ln_county_code);
2499
2500 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',2);
2501
2502 fetch c_county_rec into lc_exists;
2503 if c_county_rec%notfound then
2504
2505 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',3);
2506
2507 -- Call write message to store information that their is no county record for this assignment
2508 write_message(
2509 p_proc_type => 'MISSING_COUNTY_RECORDS',
2510 p_person_id => p_person_id,
2511 p_assign_id => p_assign_id,
2512 p_old_juri_code => ln_state_code||'-'||ln_county_code,
2513 p_new_juri_code => p_new_juri_code,
2514 p_location => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2515 p_id => null);
2516
2517 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
2518
2519
2520 ELSE
2521
2522 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',5);
2523
2524 open c_tax_rec(p_assign_id,
2525 ln_state_code,
2526 ln_county_code,
2527 ln_old_city_code);
2528
2529 fetch c_tax_rec into ln_check;
2530
2531 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',6);
2532
2533 if c_tax_rec%found then -- changed notfound to found
2534 close c_tax_rec;
2535 open c_tax_rec(p_assign_id,
2536 ln_state_code,
2537 ln_county_code,
2538 ln_city_code); -- Check with new city code.
2539 fetch c_tax_rec into ln_check;
2540 if c_tax_rec%notfound then
2541 lc_insert_rec := 'Y';
2542 end if;
2543 end if;
2544
2545 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',7);
2546
2547 close c_tax_rec;
2548
2549 if lc_insert_rec = 'Y' then
2550
2551 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',8);
2552
2553 open c_elig_date(p_assign_id);
2554
2555 fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2556
2557 if c_elig_date%notfound then
2558 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2559 --Exiting if there are no city Tax Records.
2560 end if;
2561
2562 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2563 close c_elig_date;
2564
2565 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2566
2567 hr_utility.trace('The business group id is: '||to_char(ln_business_group_id));
2568
2569 IF G_MODE = 'UPGRADE' THEN
2570
2571 /* changes for 7240905*/
2572 if(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'))<> 'Y' then
2573 fnd_profile.put('HR_CROSS_BUSINESS_GROUP','Y');
2574 l_profile_value := 'Y';
2575 hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2576 end if;
2577
2578 ln_emp_city_tax_rule_id :=
2579 pay_us_emp_dt_tax_rules.insert_def_city_rec(p_assign_id,
2580 ld_eff_start_date,
2581 ld_eff_end_date,
2582 ln_state_code,
2583 ln_county_code,
2584 ln_city_code,
2585 ln_business_group_id,
2586 0);
2587
2588 /* changes for 7240905*/
2589 if(l_profile_value ='Y') then
2590 fnd_profile.put('HR_CROSS_BUSINESS_GROUP','N');
2591 hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2592 end if;
2593
2594 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2595
2596 -- Write to the table with the new city information
2597
2598 write_message(
2599 p_proc_type => 'NEW_CITY_RECORDS',
2600 p_person_id => p_person_id,
2601 p_assign_id => p_assign_id,
2602 p_old_juri_code => null,
2603 p_new_juri_code => p_new_juri_code,
2604 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2605 p_id => ln_emp_city_tax_rule_id);
2606
2607 else /* Modified for bug 6864396*/
2608
2609 write_message(
2610 p_proc_type => 'NEW_CITY_RECORDS',
2611 p_person_id => p_person_id,
2612 p_assign_id => p_assign_id,
2613 p_old_juri_code => null,
2614 p_new_juri_code => p_new_juri_code,
2615 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2616 p_id => null);
2617
2618 END IF;
2619
2620 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2621
2622 /*
2623 pay_us_emp_dt_tax_rules.maintain_element_entry
2624 (p_assignment_id => p_assign_id,
2625 p_effective_start_date => ld_eff_start_date,
2626 p_effective_end_date => ld_eff_end_date,
2627 p_session_date => ld_eff_start_date,
2628 p_jurisdiction_code => p_new_juri_code,
2629 p_percentage_time => 0,
2630 p_mode => 'INSERT');
2631 */
2632
2633 -- Write to the table the new vertex information of the new vertex record
2634
2635 write_message(
2636 p_proc_type => 'NEW_VERTEX_RECORDS',
2637 p_person_id => p_person_id,
2638 p_assign_id => p_assign_id,
2639 p_old_juri_code => null,
2640 p_new_juri_code => p_new_juri_code,
2641 p_location => 'PAY_ELEMENT_ENTRIES_F',
2642 p_id => null);
2643
2644 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
2645
2646 /* END IF; County 6864396*/
2647 END IF;
2648
2649 end if;
2650
2651 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',14);
2652
2653 ELSE -- p_proc_type != 'SU' and p_proc_type != 'US'
2654
2655 write_message(
2656 p_proc_type => 'ELE_ENTRY_INSERT_NOT_REQD',
2657 p_person_id => p_person_id,
2658 p_assign_id => p_assign_id,
2659 p_old_juri_code => null,
2660 p_new_juri_code => p_new_juri_code,
2661 p_location => 'PAY_ELEMENT_ENTRIES_F',
2662 p_id => null);
2663
2664 END IF;
2665 END insert_ele_entries;
2666
2667
2668 -- This procedure will check the percentage time in state for a particular jurisdiction
2669 -- and make sure that percent time is not more than 100%
2670
2671 PROCEDURE check_time(p_assign_id IN NUMBER)
2672
2673 IS
2674
2675 --Retrieve all states for the assignments that have changed from per_assignments_f
2676
2677 CURSOR state_cur(p_assign_id NUMBER) IS
2678 SELECT pus.state_code,
2679 pus.state_name,
2680 pusf.effective_start_date,
2681 pusf.effective_end_date
2682 FROM pay_us_states pus,
2683 pay_us_emp_state_tax_rules_f pusf
2684 WHERE pusf.assignment_id = p_assign_id
2685 AND pusf.state_code = pus.state_abbrev
2686 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
2687 where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2688 and pugu.process_type = 'PERCENTAGE_OVER_100'
2689 and pugu.assignment_id = p_assign_id
2690 and pugu.process_mode = g_mode
2691 and pugu.process_type = g_process_type
2692 and pugu.id = g_geo_phase_id);
2693
2694
2695
2696 state_rec state_cur%ROWTYPE;
2697
2698 --Sum the percentage for all Vertex entries falling within the state
2699 --jurisdiction for the effective dates of the assignment.
2700 CURSOR sum_cur(p_assign_id NUMBER, start_date DATE,
2701 end_date DATE, state_code CHAR) IS
2702 SELECT sum(nvl(to_number(pev2.screen_entry_value),0))
2703 FROM pay_input_values_f piv2,
2704 pay_element_entry_values_f pev2,
2705 pay_input_values_f piv1,
2706 pay_element_entry_values_f pev1,
2707 pay_element_types_f pet,
2708 pay_element_links_f pel,
2709 pay_element_entries_f pef
2710 WHERE pef.assignment_id = p_assign_id
2711 AND pef.creator_type = 'UT'
2712 AND pef.element_link_id = pel.element_link_id
2713 AND pel.element_type_id = pet.element_type_id
2714 AND pet.element_name = 'VERTEX'
2715 AND (
2716 (start_date >= pef.effective_start_date AND
2717 end_date <= pef.effective_end_date)
2718 OR (start_date = pef.effective_end_date)
2719 OR (end_date = pef.effective_start_date)
2720 )
2721 AND (pef.element_entry_id = pev1.element_entry_id
2722 AND pef.effective_start_date = pev1.effective_start_date
2723 AND pef.effective_end_date = pev1.effective_end_date
2724 AND state_code = substr(pev1.screen_entry_value,1,2)
2725 AND pev1.input_value_id = piv1.input_value_id
2726 AND piv1.name = 'Jurisdiction'
2727 AND piv1.legislation_code = 'US')
2728 AND (pev2.element_entry_id = pev1.element_entry_id
2729 AND pev2.effective_start_date = pev1.effective_start_date
2730 AND pev2.effective_end_date = pev1.effective_end_date
2731 AND pev2.screen_entry_value is not null
2732 AND piv2.input_value_id = pev2.input_value_id
2733 AND piv2.name = 'Percentage'
2734 AND piv2.legislation_code = 'US');
2735
2736 sum_rec sum_cur%ROWTYPE;
2737
2738 l_person_id per_people_f.person_id%TYPE;
2739 tot_percentage NUMBER;
2740 percentage NUMBER;
2741
2742 BEGIN
2743
2744 hr_utility.trace('Entering pay_us_geo_upd_pkg.check_time');
2745
2746 tot_percentage := 0;
2747
2748 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',1);
2749
2750 -- Get each state for the assignment.
2751 FOR state_rec IN state_cur(p_assign_id) LOOP
2752
2753 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',2);
2754
2755 -- Get the percentage of time worked in that state.
2756
2757 OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2758 state_rec.effective_end_date, state_rec.state_code);
2759 FETCH sum_cur INTO percentage;
2760 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2761
2762 IF sum_cur%FOUND THEN
2763 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2764
2765 tot_percentage := tot_percentage + percentage;
2766 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2767
2768 END IF;
2769 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2770
2771 CLOSE sum_cur;
2772 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2773
2774 END LOOP; -- state_cur
2775 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2776
2777 IF (tot_percentage > 100) THEN
2778
2779 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2780
2781 SELECT ppf.person_id
2782 INTO l_person_id
2783 FROM per_all_people_f ppf,
2784 per_all_assignments_f paf
2785 WHERE ppf.person_id = paf.person_id
2786 AND paf.assignment_id = p_assign_id
2787 AND ppf.effective_start_date = (SELECT max(ppf2.effective_start_date)
2788 FROM per_all_people_f ppf2
2789 WHERE ppf2.person_id = ppf.person_id);
2790
2791
2792 write_message(
2793 p_proc_type => 'PERCENTAGE_OVER_100',
2794 p_person_id => l_person_id,
2795 p_assign_id => p_assign_id,
2796 p_old_juri_code => null,
2797 p_new_juri_code => null,
2798 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2799 p_id => null);
2800
2801
2802 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2803
2804 END IF;
2805 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2806
2807 -- Taking out the exception here because if the procedure errors let it go to the calling block and
2808 -- use that exception handler as that errors to the savepoint and continues with the assignment.
2809
2810 /*
2811 EXCEPTION
2812 WHEN OTHERS THEN
2813 hr_utility.trace(SQLCODE||SQLERRM||'Program error contact support');
2814 hr_utility.raise_error;
2815 */
2816 END check_time;
2817
2818
2819
2820
2821 -- THE BEGINNING OF THE MAIN PROCEDURE: UPGRADE_GEOCODES
2822
2823 BEGIN
2824
2825 -- Initialize the global variables here
2826
2827 g_geo_phase_id := null;
2828 g_mode := null;
2829 g_process_type := null;
2830
2831 -- hr_utility.trace_on(null,'oracle');
2832
2833 hr_utility.trace('Entering pay_us_geo_upd_pkg.upgrade_geocodes');
2834
2835 -- Set the global phase id for the geo update
2836
2837 g_geo_phase_id := p_geo_phase_id;
2838
2839 hr_utility.trace('The pay patch status id for this upgrade is: '||to_char(g_geo_phase_id));
2840
2841 -- Set the global mode for the upgrade
2842
2843 g_mode := p_mode;
2844
2845 hr_utility.trace('The mode for this upgrade is: '||g_mode);
2846
2847
2848
2849 --Check if pay_us_asg_reporting table exists as some clients may
2850 --not have this table on their database.
2851 SELECT count(*)
2852 INTO table_exist
2853 FROM cat
2854 WHERE table_name = tab_name;
2855
2856 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2857
2858 --Bug 2996546 call procedure load_input_values
2859 load_input_values;
2860 --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2861
2862
2863 OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2864
2865 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',5);
2866
2867 LOOP
2868
2869 BEGIN
2870
2871 FETCH main_driving_cur into main_old_juri_code, main_assign_id, main_new_juri_code, main_person_id,
2872 main_new_city_code, main_proc_type, main_city_tax_rule_id;
2873 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',10);
2874
2875 EXIT when main_driving_cur%NOTFOUND;
2876
2877
2878 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2879
2880
2881 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2882
2883 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2884
2885 -- Set the global variable for g_process_type
2886
2887 g_process_type := main_proc_type;
2888
2889 hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2890 hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2891 hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2892 hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2893 hr_utility.trace('The main person id is: '||to_char(main_person_id));
2894 hr_utility.trace('The city name is: '||p_city_name);
2895
2896 -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2897 -- Our concern is how do we track an assignment that has errored. So we will start by creating
2898 -- a row for the assignment with a p_status of 'U'. Then at the end of the loop we will
2899 -- change the p_status to 'C' before commiting
2900
2901 -- If a person has errored and this upgrade is rerun we must not re-write the message
2902
2903 l_proc_stage := 'START';
2904
2905 OPEN chk_assign_error_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
2906
2907 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2908
2909 FETCH chk_assign_error_cur INTO l_chk_assign_error;
2910 IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2911 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2912
2913 NULL; /* We do nothing here because we want the assignment to re-processed
2914 but do not create another row in the pay_us_geo_update table */
2915
2916 ELSE
2917 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2918
2919 -- We need to store a process type because the same geocode can have two records for different city names
2920 -- Thus we would get two rows in PAY_US_GEO_UPDATE for the same assignment id.
2921
2922 write_message(
2923 p_proc_type => main_proc_type,
2924 p_person_id => main_person_id,
2925 p_assign_id => main_assign_id,
2926 p_old_juri_code => main_old_juri_code,
2927 p_new_juri_code => main_new_juri_code,
2928 p_location => null,
2929 p_id => null,
2930 p_status => 'P');
2931 hr_utility.set_location('before commit',1);
2932 -- commit;
2933
2934 END IF;
2935
2936 CLOSE chk_assign_error_cur;
2937 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',45);
2938
2939 -- Create element entries and a new city record for new jusrisdictions for the assignment. We do this first
2940 -- because we want to commit based on an assignment.
2941
2942 l_proc_stage := 'INSERT_ELEMENT_ENTRIES';
2943
2944
2945 insert_ele_entries (
2946 p_proc_type => main_proc_type,
2947 p_assign_id => main_assign_id,
2948 p_person_id => main_person_id,
2949 p_new_juri_code => main_new_juri_code,
2950 p_old_juri_code => main_old_juri_code);
2951
2952
2953 -- Here is the savepoint so if an assignment fails during the upgrade it will rollback to here and continue with the
2954 -- next assignment.
2955
2956 SAVEPOINT GEO_UPDATE_SAVEPOINT;
2957
2958 --Update pay_us_asg_reporting table using dynamic sql. We
2959 --must build and execute a new update statement each time.
2960 --This used to point to non-dt w4 tables, changing.
2961
2962 IF (table_exist <> '0') THEN
2963 l_text := 'UPDATE '||tab_name||
2964 ' SET jurisdiction_code = '''||main_new_juri_code||
2965 ''' WHERE assignment_id = '''||to_char(main_assign_id)||
2966 ''' AND jurisdiction_code = '''||main_old_juri_code||
2967 '''';
2968 sql_cursor := dbms_sql.open_cursor;
2969 dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7);
2970 ret := dbms_sql.execute(sql_cursor);
2971 dbms_sql.close_cursor(sql_cursor);
2972 END IF;
2973
2974
2975 --Update element entry values
2976
2977 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',50);
2978
2979 OPEN pev_cur(main_old_juri_code, main_assign_id);
2980 LOOP
2981 FETCH pev_cur INTO pev_rec;
2982 EXIT WHEN pev_cur%NOTFOUND;
2983
2984 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',55);
2985
2986 l_proc_stage := 'ELEMENT_ENTRIES';
2987
2988 element_entries(
2989 p_proc_type => main_proc_type,
2990 p_person_id => main_person_id,
2991 p_assign_id => main_assign_id,
2992 p_input_value_id => pev_rec.input_value_id,
2993 p_ele_ent_id => pev_rec.element_entry_id,
2994 p_old_juri_code => main_old_juri_code,
2995 p_new_juri_code => main_new_juri_code);
2996
2997 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',60);
2998
2999
3000 END LOOP;
3001 CLOSE pev_cur;
3002
3003 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',65);
3004
3005
3006 -- Conditionally Update run results and run result values
3007 --Per bug 2996546 included another condition
3008 --where clause in OR length(main_old_juri_code) = 2
3009 --to include Canada legislation
3010 --
3011
3012 BEGIN
3013
3014 SELECT 'Y'
3015 INTO lv_update_prr
3016 FROM dual
3017 WHERE EXISTS (SELECT 0
3018 FROM pay_us_city_tax_info_f
3019 WHERE jurisdiction_code = main_old_juri_code)
3020 OR EXISTS (SELECT 0
3021 FROM pay_us_city_tax_info_f
3022 WHERE jurisdiction_code = main_new_juri_code)
3023 OR length(main_old_juri_code) = 2 ;
3024 EXCEPTION
3025 WHEN NO_DATA_FOUND THEN
3026 lv_update_prr := 'N';
3027
3028 END;
3029
3030 IF lv_update_prr = 'Y' THEN
3031
3032 -- Bug 3319878 -- Opening cursor
3033
3034 OPEN paa_cur(main_assign_id);
3035 LOOP
3036 FETCH paa_cur INTO paa_rec;
3037 EXIT WHEN paa_cur%NOTFOUND;
3038
3039 OPEN prr_cur(paa_rec,main_assign_id);
3040 LOOP
3041 FETCH prr_cur INTO prr_rec;
3042 EXIT WHEN prr_cur%NOTFOUND;
3043
3044 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',70);
3045
3046 l_proc_stage := 'RUN_RESULTS';
3047
3048 run_results(
3049 p_proc_type => main_proc_type,
3050 p_person_id => main_person_id,
3051 p_assign_id => main_assign_id,
3052 p_assign_act_id => prr_rec.assignment_action_id,
3053 p_run_result_id => prr_rec.run_result_id,
3054 p_old_juri_code => main_old_juri_code,
3055 p_new_juri_code => main_new_juri_code);
3056
3057
3058 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',75);
3059 END LOOP;
3060 CLOSE prr_cur;
3061 END LOOP;
3062 CLOSE paa_cur;
3063
3064 END IF;
3065 --
3066 --
3067 --
3068
3069 --Per bug 2996546
3070 -- Update pay_action_contexts . context value
3071 --
3072 --
3073
3074 OPEN pac_cur(main_assign_id, main_city_tax_rule_id);
3075 LOOP
3076 FETCH pac_cur INTO pac_rec;
3077 EXIT WHEN pac_cur%NOTFOUND;
3078
3079
3080 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',240);
3081
3082 l_proc_stage := 'PAY_ACTION_CONTEXTS';
3083
3084 pay_action_contexts(
3085 p_proc_type => main_proc_type,
3086 p_person_id => main_person_id,
3087 p_assign_id => main_assign_id,
3088 p_assign_act_id => pac_rec.assignment_action_id,
3089 p_context_id => pac_rec.context_id,
3090 p_old_juri_code => main_old_juri_code,
3091 p_new_juri_code => main_new_juri_code);
3092
3093
3094 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',250);
3095
3096 END LOOP;
3097 CLOSE pac_cur;
3098
3099 --
3100 --
3101
3102 -- Update ff archive item contexts
3103
3104
3105 OPEN fac_cur(main_assign_id, main_old_juri_code);
3106 LOOP
3107 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',80);
3108
3109 FETCH fac_cur INTO fac_rec;
3110 EXIT WHEN fac_cur%NOTFOUND;
3111
3112 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',85);
3113
3114 l_proc_stage := 'ARCHIVE_ITEM_CONTEXTS';
3115
3116 archive_item_contexts(
3117 p_proc_type => main_proc_type,
3118 p_person_id => main_person_id,
3119 p_assign_id => main_assign_id,
3120 p_archive_item_id => fac_rec.archive_item_id,
3121 p_context_id => fac_rec.context_id,
3122 P_OLD_JURi_code => main_old_juri_code,
3123 p_new_juri_code => main_new_juri_code);
3124
3125
3126 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',90);
3127
3128 END LOOP;
3129 CLOSE fac_cur;
3130
3131
3132 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',95);
3133
3134
3135 -- Update person balance context values.
3136
3137
3138 OPEN pbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
3139 LOOP
3140 FETCH pbcv_cur INTO pbcv_rec;
3141 EXIT WHEN pbcv_cur%NOTFOUND;
3142
3143 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',100);
3144
3145 l_proc_stage := 'PERSON_BALANCE_CONTEXTS';
3146
3147 balance_contexts(
3148 p_proc_type => main_proc_type,
3149 p_person_id => main_person_id,
3150 p_assign_id => main_assign_id,
3151 p_assign_act_id => pbcv_rec.assignment_action_id,
3152 p_context_id => pbcv_rec.context_id ,
3153 p_lat_bal_id => pbcv_rec.latest_balance_id,
3154 p_old_juri_code => main_old_juri_code,
3155 p_new_juri_code => main_new_juri_code);
3156
3157 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',105);
3158
3159
3160 END LOOP;
3161 CLOSE pbcv_cur;
3162
3163 -- Update assignment balance context values.
3164
3165 OPEN pacv_cur(main_old_juri_code, main_assign_id, main_person_id);
3166 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',110);
3167
3168 LOOP
3169 FETCH pacv_cur INTO pacv_rec;
3170 EXIT WHEN pacv_cur%NOTFOUND;
3171
3172 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',115);
3173
3174 l_proc_stage := 'ASSIGNMENT_BALANCE_CONTEXTS';
3175
3176 balance_contexts(
3177 p_proc_type => main_proc_type,
3178 p_person_id => main_person_id,
3179 p_assign_id => main_assign_id,
3180 p_assign_act_id => pacv_rec.assignment_action_id,
3181 p_context_id => pacv_rec.context_id ,
3182 p_lat_bal_id => pacv_rec.latest_balance_id,
3183 p_old_juri_code => main_old_juri_code,
3184 p_new_juri_code => main_new_juri_code);
3185
3186
3187 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',120);
3188
3189
3190 END LOOP;
3191 CLOSE pacv_cur;
3192
3193 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',125);
3194
3195 -- Rosie Monge 10/17/2005
3196 -- Update Pay_Latest_balances context values.
3197
3198 OPEN plbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
3199 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',126 );
3200 LOOP
3201 FETCH plbcv_cur INTO plbcv_rec;
3202
3203 EXIT WHEN plbcv_cur%NOTFOUND;
3204
3205 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',127);
3206
3207 l_proc_stage := 'PAY_LATEST_BALANCES_CONTEXT';
3208
3209 balance_contexts(
3210 p_proc_type => main_proc_type,
3211 p_person_id => main_person_id,
3212 p_assign_id => main_assign_id,
3213 p_assign_act_id => plbcv_rec.assignment_action_id,
3214 p_context_id => plbcv_rec.context_id ,
3215 p_lat_bal_id => plbcv_rec.latest_balance_id,
3216 p_old_juri_code => main_old_juri_code,
3217 p_new_juri_code => main_new_juri_code);
3218
3219
3220 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',128);
3221
3222 END LOOP;
3223 CLOSE plbcv_cur;
3224 -- End Rosie Monge addition for bug 4602222
3225
3226 --Update the pay_balance_batch_lines table.
3227
3228 l_proc_stage := 'BALANCE_BATCH_LINES';
3229
3230 balance_batch_lines(
3231 p_proc_type => main_proc_type,
3232 p_person_id => main_person_id,
3233 p_assign_id => main_assign_id,
3234 p_old_juri_code => main_old_juri_code,
3235 p_new_juri_code => main_new_juri_code);
3236
3237 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',130);
3238
3239 ---
3240 ---
3241 ---
3242 --Per bug 2738574
3243 --Update the pay_run_balances table.
3244
3245 l_proc_stage := 'PAY_RUN_BALANCES';
3246
3247 pay_run_balances(
3248 p_proc_type => main_proc_type,
3249 p_person_id => main_person_id,
3250 p_assign_id => main_assign_id,
3251 p_new_city_code => main_new_city_code,
3252 p_old_juri_code => main_old_juri_code,
3253 p_new_juri_code => main_new_juri_code);
3254
3255 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',131);
3256 ---
3257 ---
3258 ---
3259
3260
3261 -- Check for and delete any duplicate Vertex element entries
3262 -- This can be caused by two geocodes combining.
3263 -- We will then add the percentages togethor before deleting.
3264
3265 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
3266
3267 l_proc_stage := 'DUPLICATE_VERTEX_EE';
3268
3269 duplicate_vertex_ee(main_assign_id);
3270
3271 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
3272
3273
3274 --Update the pay_us_emp_city_tax_rules_f table.
3275
3276 OPEN city_rec_cur(main_new_juri_code, main_old_juri_code, main_assign_id, main_city_tax_rule_id);
3277 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',145);
3278
3279 FETCH city_rec_cur INTO l_city_tax_exists;
3280 CLOSE city_rec_cur;
3281
3282 l_proc_stage := 'CITY_TAX_RECORDS';
3283
3284 IF l_city_tax_exists = 'Y' THEN
3285 city_tax_records (
3286 p_proc_type => main_proc_type,
3287 p_person_id => main_person_id,
3288 p_assign_id => main_assign_id,
3289 p_old_juri_code => main_old_juri_code,
3290 p_new_juri_code => main_new_juri_code,
3291 p_new_city_code => main_new_city_code,
3292 p_city_tax_record_id => main_city_tax_rule_id);
3293
3294 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',150);
3295
3296 END IF;
3297
3298
3299
3300
3301 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',155);
3302
3303
3304 -- Now we check for assignments with more than 100% time in jurisdiction
3305
3306 l_proc_stage := 'CHECK_TIME';
3307
3308 check_time(p_assign_id => main_assign_id);
3309
3310 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',160);
3311
3312 -- Now we update the SU and US cases to a status of 'A' for assignments that need to be updated
3313 -- via the API. If the cursor is found then we will update the status to 'A', only if the assignment
3314 -- was not updated because the same jurisdiction also had another type.
3315
3316 l_proc_stage := 'SET API';
3317
3318 OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3319 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3320
3321 FETCH chk_assign_api_cur into l_chk_assign_api;
3322 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3323
3324 CLOSE chk_assign_api_cur;
3325
3326 IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3327
3328 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',175);
3329
3330 UPDATE PAY_US_GEO_UPDATE
3331 SET status = 'A', description = null
3332 WHERE assignment_id = main_assign_id
3333 AND old_juri_code = main_old_juri_code
3334 AND new_juri_code = main_new_juri_code
3335 AND table_name is null
3336 AND table_value_id is null
3337 AND status = 'P'
3338 AND process_type = main_proc_type;
3339
3340 ELSE
3341 -- Now we update the assignment that has just processed to a status of 'C' in PAY_US_GEO_UPDATE
3342
3343 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',180);
3344
3345 l_proc_stage := 'END';
3346
3347 UPDATE PAY_US_GEO_UPDATE
3348 SET status = 'C', description = null
3349 WHERE assignment_id = main_assign_id
3350 AND old_juri_code = main_old_juri_code
3351 AND new_juri_code = main_new_juri_code
3352 AND table_name is null
3353 AND table_value_id is null
3354 AND status in ('P','A')
3355 AND process_type = main_proc_type;
3356
3357 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',185);
3358
3359 END IF;
3360
3361 hr_utility.set_location('before commit',2);
3362 -- commit; /* We commit at this point so if it fails at any point let it rollback to the savepoint and continue */
3363
3364
3365 hr_utility.trace('Exiting pay_us_geo_upd_pkg.upgrade_geocodes');
3366
3367
3368 EXCEPTION
3369 WHEN OTHERS THEN
3370 l_error_text := 'An error occurred in step: '||l_proc_stage||' The sql error message is: '||SQLERRM|| ' The error code is: '||to_char(SQLCODE);
3371
3372 hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3373 hr_utility.trace('Entered the main program exception handler');
3374 hr_utility.trace('The code failed at process type of: '||l_proc_stage);
3375
3376 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Person id = ' || to_char(main_person_id));
3377 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Assignment id = ' || to_char(main_assign_id));
3378 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Old Jurisdiction Code = ' || main_old_juri_code);
3379 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' New Jurisdiction Code = ' || main_new_juri_code);
3380 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3381
3382 rollback to GEO_UPDATE_SAVEPOINT;
3383
3384 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3385
3386 UPDATE PAY_US_GEO_UPDATE
3387 SET description = l_error_text
3388 WHERE assignment_id = main_assign_id
3389 AND old_juri_code = main_old_juri_code
3390 AND new_juri_code = main_new_juri_code
3391 AND table_name is null
3392 AND table_value_id is null
3393 AND old_juri_code = main_old_juri_code
3394 AND new_juri_code = main_new_juri_code
3395 AND status = 'P'
3396 AND process_type = main_proc_type;
3397
3398 -- Close all the cursors that are within the main loop, otherwise they will remain open.
3399
3400 IF chk_assign_error_cur%ISOPEN THEN
3401 CLOSE chk_assign_error_cur;
3402 END IF;
3403
3404 IF pev_cur%ISOPEN THEN
3405 CLOSE pev_cur;
3406 END IF;
3407
3408 IF prr_cur%ISOPEN THEN
3409 CLOSE prr_cur;
3410 END IF;
3411
3412 --Bug 3319878
3413 IF paa_cur%ISOPEN THEN
3414 CLOSE paa_cur;
3415 END IF;
3416
3417 IF fac_cur%ISOPEN THEN
3418 CLOSE fac_cur;
3419 END IF;
3420
3421
3422 IF pbcv_cur%ISOPEN THEN
3423 CLOSE pbcv_cur;
3424 END IF;
3425
3426 IF pacv_cur%ISOPEN THEN
3427 CLOSE pacv_cur;
3428 END IF;
3429
3430 IF city_rec_cur%ISOPEN THEN
3431 CLOSE city_rec_cur;
3432 END IF;
3433
3434 IF chk_assign_api_cur%ISOPEN THEN
3435 CLOSE chk_assign_api_cur;
3436 END IF;
3437
3438 hr_utility.set_location('before commit',3);
3439 -- commit;
3440
3441 END;
3442
3443 END LOOP;
3444
3445 CLOSE main_driving_cur;
3446
3447 -- Remove duplicate city tax records created
3448 -- by geocode updates for all assignment ids
3449 -- in the range processed.
3450
3451 del_dup_city_tax_recs;
3452
3453 END upgrade_geocodes;
3454
3455 -- END OF THE MAIN UPGRADE GEOCODES PROCEDURE
3456
3457
3458 -- This procedure is seperate from the above main upgrade_geocodes
3459 -- This procedure will update all the taxability rules
3460 -- By taking in a parameter of P_GEO_PHASE_ID we can determine if the taxability rules
3461 -- have been upgraded already.
3462 -- This procedure will only be run by one process, NOT MULTIPLE TIMES
3463 -- This procedure taxes the place of pyrulupd.sql from previous versions
3464
3465 PROCEDURE update_taxability_rules(P_GEO_PHASE_ID IN NUMBER,
3466 P_MODE IN VARCHAR2,
3467 P_PATCH_NAME IN VARCHAR2)
3468
3469 IS
3470
3471 --Retrieve all changed geocodes on pay_taxability_rules table.
3472
3473
3474 --Bug 3319878 -- Changed the cursor query to reduce cost.
3475 --Bug 5042715 -- Added hints to reduce cost.
3476 CURSOR ptax_cur IS
3477 SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3478 ptax PAY_TAXABILITY_RULES_UK)
3479 use_nl(pmod ptax)*/
3480 distinct ptax.jurisdiction_code
3481 FROM pay_us_modified_geocodes pmod,
3482 pay_taxability_rules ptax
3483 WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3484 AND pmod.process_type in ('UP','RP')
3485 AND pmod.patch_name = p_patch_name
3486 AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3487 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3488 where pugu.table_name = 'PAY_TAXABILITY_RULES'
3489 and pugu.new_juri_code = ptax.jurisdiction_code
3490 and pugu.process_mode = g_mode
3491 and pugu.process_type = g_process_type
3492 and pugu.id = g_geo_phase_id
3493 and rownum <2);
3494
3495
3496 ptax_rec ptax_cur%ROWTYPE;
3497
3498 --Per bug 2996546
3499 --Added a cursor ptax_ca_cur to the procedure update_taxability_rules
3500 --Retrieve all changed jurisdiction_code on pay_taxability_rules table.
3501 --and update (for Canadian Legislation)
3502 --
3503
3504 --Bug 3319878 -- Changed the query to improve performance
3505
3506 CURSOR ptax_ca_cur IS
3507 SELECT distinct ptax.jurisdiction_code
3508 FROM pay_us_modified_geocodes pmod,
3509 pay_taxability_rules ptax
3510 WHERE pmod.state_code = 'CA'
3511 AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
3512 AND pmod.patch_name = p_patch_name
3513 AND ptax.legislation_code = 'CA' ;
3514
3515
3516
3517
3518 ptax_ca_rec ptax_ca_cur%ROWTYPE;
3519
3520
3521
3522 jd_code pay_taxability_rules.jurisdiction_code%TYPE;
3523 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3524 l_error_message_text varchar2(240);
3525 l_count number;
3526 BEGIN
3527
3528 g_geo_phase_id := p_geo_phase_id;
3529 g_mode := p_mode;
3530
3531 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
3532 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3533
3534 FOR ptax_rec IN ptax_cur LOOP
3535
3536 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',1);
3537
3538 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3539 process_type
3540 INTO jd_code, l_proc_type
3541 FROM pay_us_modified_geocodes pmod
3542 WHERE pmod.state_code = substr(ptax_rec.jurisdiction_code,1,2)
3543 AND pmod.old_city_code = substr(ptax_rec.jurisdiction_code,8,4)
3544 AND pmod.process_type in ('UP','RP')
3545 AND pmod.patch_name = p_patch_name
3546 --city taxability rules don't carry a county-code so we have to pull the first
3547 -- row in the case of a city that spans a county.
3548 and rownum = 1;
3549
3550 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3551
3552 select count(*) into l_count
3553 from pay_taxability_rules ptax
3554 where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3555
3556 IF l_count = 0 THEN
3557
3558 IF G_MODE = 'UPGRADE' THEN
3559
3560 UPDATE pay_taxability_rules ptax
3561 SET ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||
3562 substr(jd_code,8,4)
3563 WHERE ptax.jurisdiction_code = ptax_rec.jurisdiction_code;
3564
3565 -- COMMIT;
3566
3567
3568 END IF;
3569
3570 END IF;
3571 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',3);
3572
3573 -- write to the message table so that if this fails unexpectedly we can track which taxability
3574 -- rules have been upgraded already.
3575
3576 write_message(
3577 p_proc_type => l_proc_type,
3578 p_person_id => null,
3579 p_assign_id => null,
3580 p_old_juri_code => ptax_rec.jurisdiction_code,
3581 p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3582 p_location => 'PAY_TAXABILITY_RULES',
3583 p_id => null);
3584
3585 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3586
3587 END LOOP;
3588 --
3589 --
3590 --
3591 --Per bug 2996546
3592 --Update of pay_taxability_rules . jurisdiction_code
3593 --(Canadian Legislation)
3594
3595 OPEN ptax_ca_cur ;
3596 LOOP
3597 FETCH ptax_ca_cur INTO ptax_ca_rec;
3598 EXIT WHEN ptax_ca_cur%NOTFOUND;
3599
3600 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',15);
3601
3602 SELECT pmod.new_county_code,
3603 process_type
3604 INTO jd_code, l_proc_type
3605 FROM pay_us_modified_geocodes pmod
3606 WHERE pmod.state_code = 'CA'
3607 AND pmod.county_code = substr(ptax_ca_rec.jurisdiction_code,1,2)
3608 AND pmod.patch_name = p_patch_name;
3609
3610 IF G_MODE = 'UPGRADE' THEN
3611
3612 UPDATE pay_taxability_rules ptax
3613 SET ptax.jurisdiction_code = jd_code||'-000-'||'0000'
3614 WHERE ptax.jurisdiction_code = ptax_ca_rec.jurisdiction_code;
3615
3616 -- COMMIT;
3617
3618 END IF;
3619 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',20);
3620
3621 -- write to the message table so that if this fails unexpectedly we can track which taxability
3622 -- rules have been upgraded already.
3623
3624 write_message(
3625 p_proc_type => l_proc_type,
3626 p_person_id => null,
3627 p_assign_id => null,
3628 p_old_juri_code => ptax_ca_rec.jurisdiction_code,
3629 p_new_juri_code => jd_code||'-000-'||'0000',
3630 p_location => 'PAY_TAXABILITY_RULES',
3631 p_id => null);
3632
3633 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3634
3635
3636 END LOOP;
3637 CLOSE ptax_ca_cur ;
3638 --
3639 --
3640 --
3641 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',5);
3642
3643 EXCEPTION
3644 WHEN OTHERS THEN
3645
3646 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3647 rollback;
3648 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',6);
3649
3650 fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
3651 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3652
3653 raise_application_error(-20001,l_error_message_text);
3654
3655
3656 END update_taxability_rules;
3657
3658 --Added for Annual GEO 2010 for Bug#9541247
3659 --This Procedure is added to take care of the County Name changes delivered.The
3660 --Name of the county which was changed, had been hard coded in this procedure
3661 --Since this is a very rare scenario with respect to the data we maitain in our system.
3662
3663 --Modified the function for Bug#10060041.
3664 --Since we saw more changes and requests coming up for County Name Change/Correction,
3665 --the process of County Name Change was standardized. A Record will now be inserted
3666 --into pay_us_modified_geocodes table with process_type as 'CN'. It has Old County Name
3667 --stored in city_name field. old_city_code and new_city_code will be shown as '0000'
3668 --which will differentiate it from regular city_code changes stored in that table.
3669 --Process_Type will be 'CN'. For every year, the County_Name changes will be found
3670 --from pay_us_modified_geocodes table and corresponding Address and Location details
3671 --are updated with new county name.
3672
3673 /*This procedure is called from pay_us_geo_upd_pkg.action_creation. For an Year, if there
3674 are no assignments impacted by the city_name changes delivered the Submission of
3675 Geocode Upgrade Concurrent Program will be skipped. So the intended County Name
3676 changes update does not happen in a given year. In order to overcome this limitation
3677 if there are no assignments, we will be calling update_county_name procedure
3678 from Pay_us_geocode_engine_pkg.geocode_upgrade procedure
3679
3680 Parameter P_CALL is used to differentiate the call from pay_us_geo_upd_pkg.action_creation
3681 (Pass 'INTERNAL' to P_CALL) or pay_us_geocode_engine_pkg.geocode_upgrade (Pass 'EXTERNAL'
3682 to P_CALL)*/
3683
3684 PROCEDURE update_county_name(P_GEO_PHASE_ID IN NUMBER,
3685 P_MODE IN VARCHAR2,
3686 P_PATCH_NAME IN VARCHAR2,
3687 P_CALL IN VARCHAR2)
3688
3689 IS
3690
3691 l_error_message_text varchar2(240);
3692 l_count number;
3693 l_override_count number;
3694 l_description varchar2(500);
3695
3696 CURSOR get_county_name_changes (P_PATCH_NAME IN VARCHAR2)
3697 IS
3698 SELECT decode(pumg.state_code,'70','CA','US') country,
3699 pumg.state_code,
3700 pus.state_abbrev,
3701 puc.county_code,
3702 pumg.city_name old_county_name,
3703 puc.county_name new_county_name
3704 FROM pay_us_modified_geocodes pumg,
3705 pay_us_states pus,
3706 pay_us_counties puc
3707 WHERE pus.state_code = puc.state_code
3708 AND pus.state_code = pumg.state_code
3709 AND puc.county_code = pumg.county_code
3710 AND pumg.patch_name = P_PATCH_NAME
3711 AND pumg.process_type = 'CN'
3712 ORDER BY pus.state_code,puc.county_code;
3713
3714 l_county_name_change get_county_name_changes%ROWTYPE;
3715
3716 BEGIN
3717
3718 g_geo_phase_id := p_geo_phase_id;
3719 g_mode := p_mode;
3720
3721 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
3722 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3723 hr_utility.trace('The Patch Name is : '||P_PATCH_NAME);
3724 hr_utility.trace('Call type to procedure: '||P_CALL);
3725
3726 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',1);
3727
3728 OPEN get_county_name_changes(p_patch_name);
3729 FETCH get_county_name_changes INTO l_county_name_change;
3730
3731 WHILE (get_county_name_changes%FOUND) LOOP
3732
3733 IF G_MODE = 'UPGRADE' THEN
3734
3735 l_count := 0;
3736
3737 UPDATE per_addresses
3738 SET region_1 = l_county_name_change.new_county_name
3739 WHERE region_1 = l_county_name_change.old_county_name
3740 AND region_2 = l_county_name_change.state_abbrev
3741 AND country = l_county_name_change.country;
3742
3743 l_count := SQL%ROWCOUNT;
3744
3745 UPDATE per_addresses
3746 SET add_information19 = l_county_name_change.new_county_name
3747 WHERE add_information19 = l_county_name_change.old_county_name
3748 AND add_information17 = l_county_name_change.state_abbrev;
3749
3750 l_count := l_count + SQL%ROWCOUNT;
3751
3752 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',2);
3753
3754 ELSE
3755
3756 l_count := 0;
3757 l_override_count := 0;
3758
3759 SELECT count(*) INTO l_count
3760 FROM per_addresses
3761 WHERE region_1 = l_county_name_change.old_county_name
3762 AND region_2 = l_county_name_change.state_abbrev
3763 AND country = l_county_name_change.country;
3764
3765 SELECT count(*) INTO l_override_count
3766 FROM per_addresses
3767 WHERE add_information19 = l_county_name_change.old_county_name
3768 AND add_information17 = l_county_name_change.state_abbrev;
3769
3770 l_count := l_count + l_override_count;
3771
3772 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',3);
3773
3774 END IF; /*G_MODE = 'UPGRADE' if*/
3775
3776 IF l_count > 0 THEN
3777
3778 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',4);
3779
3780 l_description := 'County '||l_county_name_change.old_county_name||', '||
3781 l_county_name_change.state_abbrev||' renamed to '||
3782 l_county_name_change.new_county_name||'. Corresponding Person Address Records Updated.';
3783
3784 IF P_CALL = 'EXTERNAL' THEN
3785
3786 fnd_file.put_line(fnd_file.log, l_description);
3787
3788 END IF;
3789
3790 write_message(
3791 p_proc_type => 'COUNTY_NAME_CHANGE',
3792 p_person_id => null,
3793 p_assign_id => null,
3794 p_old_juri_code => null,
3795 p_new_juri_code => l_count,
3796 p_location => 'PER_ADDRESSES',
3797 p_id => p_geo_phase_id,
3798 p_description => l_description);
3799
3800 END IF; /* l_count > 0 if*/
3801
3802 IF G_MODE = 'UPGRADE' THEN
3803
3804 l_count := 0;
3805
3806 UPDATE hr_locations_all
3807 SET region_1 = l_county_name_change.new_county_name
3808 WHERE region_1 = l_county_name_change.old_county_name
3809 AND region_2 = l_county_name_change.state_abbrev
3810 AND country = l_county_name_change.country;
3811
3812 l_count := SQL%ROWCOUNT;
3813
3814 UPDATE hr_locations_all
3815 SET loc_information19 = l_county_name_change.new_county_name
3816 WHERE loc_information19 = l_county_name_change.old_county_name
3817 AND loc_information17 = l_county_name_change.state_abbrev;
3818
3819 l_count := l_count + SQL%ROWCOUNT;
3820
3821 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',5);
3822
3823 ELSE
3824
3825 l_count := 0;
3826 l_override_count := 0;
3827
3828 SELECT count(*) INTO l_count
3829 FROM hr_locations_all
3830 WHERE region_1 = l_county_name_change.old_county_name
3831 AND region_2 = l_county_name_change.state_abbrev
3832 AND country = l_county_name_change.country;
3833
3834 SELECT count(*) INTO l_override_count
3835 FROM hr_locations_all
3836 WHERE LOC_INFORMATION19 = l_county_name_change.old_county_name
3837 AND LOC_INFORMATION17 = l_county_name_change.state_abbrev;
3838
3839 l_count := l_count + l_override_count;
3840
3841 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',6);
3842
3843 END IF; /*G_MODE = 'UPGRADE' if*/
3844
3845 IF l_count > 0 THEN
3846
3847 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',7);
3848
3849 l_description := 'County '||l_county_name_change.old_county_name||', '||
3850 l_county_name_change.state_abbrev||' renamed to '||
3851 l_county_name_change.new_county_name||'. Corresponding Location Address Records Updated.';
3852
3853 IF P_CALL = 'EXTERNAL' THEN
3854
3855 fnd_file.put_line(fnd_file.log, l_description);
3856
3857 END IF;
3858
3859 write_message(
3860 p_proc_type => 'COUNTY_NAME_CHANGE',
3861 p_person_id => null,
3862 p_assign_id => null,
3863 p_old_juri_code => null,
3864 p_new_juri_code => l_count,
3865 p_location => 'HR_LOCATIONS_ALL',
3866 p_id => p_geo_phase_id,
3867 p_description => l_description);
3868
3869 END IF; /* l_count > 0 if*/
3870
3871 FETCH get_county_name_changes INTO l_county_name_change;
3872
3873 END LOOP;
3874
3875 CLOSE get_county_name_changes;
3876
3877 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',8);
3878
3879 EXCEPTION
3880 WHEN OTHERS THEN
3881
3882 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3883 rollback;
3884 hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',11);
3885
3886 fnd_file.put_line(fnd_file.log, 'Exception update_county_name' );
3887 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3888
3889 raise_application_error(-20001,l_error_message_text);
3890
3891 END update_county_name;
3892
3893 --End Bug#9541247
3894
3895 /* Added for Annual GEO 2012 for Bug#14314081
3896
3897 This Procedure is added to take care of the City Name changes delivered. The
3898 City Name delivered in PAY_US_CITY_NAMES gets copied into other tables as
3899 it is used in Person Address or Location Address etc. Since we are changing
3900 the City Name we delivered earlier, it is necessary to update the City Name
3901 details stored in other tables.
3902
3903 For each of the City Name that got modified, an entry will be created in table
3904 PAY_US_MODIFIED_GEOCODES with process_type as 'CY'. It has Old City Name stored
3905 in city_name field. The Jurisdiction Code details corresponding to the City
3906 Name is also stored in PAY_US_MODIFIED_GEOCODES table.
3907
3908 Since for a given Jurisdiction Code we can have multiple cities (i.e Primary
3909 city and Secondary Cities), it is necessary to have a mechanism to distinguish
3910 for which City Name entry the name actually got modified.
3911
3912 For this purpose, another entry will be created in PAY_US_MODIFIED_GEOCODES
3913 table with process_type as 'NC'. It has the New City corresponding to the
3914 Old City that got updated. In case if there are multiple city name changes for
3915 a same city code, in order to make it easy to identify which city name got
3916 changed to which city name, the old city name will be saved in the field
3917 PATCH_NAME. This PATCH_NAME will have the entry CITY_NAME_CHANGE_XXXX followed
3918 by Colon (:) followed by the Old City Name.
3919
3920 So if it is necessary to identify what all City names got modified below Query
3921 can be used to identify the list.
3922
3923 SELECT pumg1.patch_name,
3924 pumg1.city_name old_city_name,
3925 pumg2.city_name new_city_name
3926 FROM pay_us_modified_geocodes pumg1,
3927 pay_us_modified_geocodes pumg2
3928 WHERE pumg1.process_type = 'CY'
3929 AND pumg2.process_type = 'NC'
3930 AND pumg1.state_code = pumg2.state_code
3931 AND pumg1.county_code = pumg2.county_code
3932 AND pumg1.old_city_code = pumg2.old_city_code
3933 AND pumg1.new_city_code = pumg2.new_city_code
3934 AND pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
3935 AND REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
3936 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
3937 ORDER BY pumg1.patch_name
3938
3939 This procedure is called from pay_us_geo_upd_pkg.action_creation. For an Year, if there
3940 are no assignments impacted by the city_name changes delivered the Submission of
3941 Geocode Upgrade Concurrent Program will be skipped. So the intended City Name
3942 changes update does not happen in a given year. In order to overcome this limitation
3943 if there are no assignments, we will be calling update_city_name procedure
3944 from Pay_us_geocode_engine_pkg.geocode_upgrade procedure
3945
3946 Parameter P_CALL is used to differentiate the call from pay_us_geo_upd_pkg.action_creation
3947 (Pass 'INTERNAL' to P_CALL) or pay_us_geocode_engine_pkg.geocode_upgrade (Pass 'EXTERNAL'
3948 to P_CALL)*/
3949
3950 PROCEDURE update_city_name(P_GEO_PHASE_ID IN NUMBER,
3951 P_MODE IN VARCHAR2,
3952 P_PATCH_NAME IN VARCHAR2,
3953 P_CALL IN VARCHAR2)
3954
3955 IS
3956
3957 l_error_message_text varchar2(240);
3958 l_count number;
3959 l_override_count number;
3960 l_description varchar2(500);
3961
3962 CURSOR get_city_name_changes (P_PATCH_NAME pay_us_modified_geocodes.patch_name%TYPE) IS
3963 SELECT decode(pumg1.state_code,'70','CA','US') country,
3964 pus.state_code,
3965 pus.state_abbrev,
3966 puc.county_code,
3967 pumg1.old_city_code,
3968 pumg1.city_name old_city_name,
3969 pumg2.city_name new_city_name
3970 FROM pay_us_modified_geocodes pumg1,
3971 pay_us_modified_geocodes pumg2,
3972 pay_us_states pus,
3973 pay_us_counties puc
3974 WHERE pus.state_code = puc.state_code
3975 AND pus.state_code = pumg1.state_code
3976 AND pus.state_code = pumg2.state_code
3977 AND puc.county_code = pumg1.county_code
3978 AND puc.county_code = pumg2.county_code
3979 AND pumg1.patch_name = P_PATCH_NAME
3980 AND pumg1.process_type = 'CY'
3981 AND pumg1.state_code = pumg2.state_code
3982 AND pumg1.county_code = pumg2.county_code
3983 AND pumg1.old_city_code = pumg2.old_city_code
3984 AND pumg1.new_city_code = pumg2.new_city_code
3985 AND pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
3986 AND REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
3987 = REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
3988 ORDER BY country DESC,
3989 pus.state_code,
3990 puc.county_code,
3991 pumg1.old_city_code;
3992
3993 CURSOR get_county_name (P_STATE_CODE pay_us_counties.state_code%TYPE,
3994 P_COUNTY_CODE pay_us_counties.county_code%TYPE) IS
3995 SELECT decode(state_code,'70',county_abbrev,county_name)
3996 FROM pay_us_counties
3997 WHERE state_code = p_state_code
3998 AND county_code = p_county_code;
3999
4000 CURSOR get_new_county_name (P_STATE_CODE pay_us_modified_geocodes.state_code%TYPE,
4001 P_COUNTY_CODE pay_us_modified_geocodes.county_code%TYPE) IS
4002 SELECT city_name county_name
4003 FROM pay_us_modified_geocodes
4004 WHERE process_type = 'CN'
4005 AND state_code = p_state_code
4006 AND county_code = p_county_code
4007 ORDER BY patch_name;
4008
4009 l_city_name_change get_city_name_changes%ROWTYPE;
4010 l_county_name pay_us_counties.county_name%TYPE;
4011 l_jurisdiction_code pay_us_geo_update.old_juri_code%TYPE;
4012
4013 BEGIN
4014
4015 g_geo_phase_id := p_geo_phase_id;
4016 g_mode := p_mode;
4017
4018 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
4019 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4020 hr_utility.trace('The Patch Name is : '||P_PATCH_NAME);
4021 hr_utility.trace('Call type to procedure: '||P_CALL);
4022
4023 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',1);
4024
4025 OPEN get_city_name_changes(p_patch_name);
4026 FETCH get_city_name_changes INTO l_city_name_change;
4027
4028 WHILE (get_city_name_changes%FOUND) LOOP
4029
4030 l_jurisdiction_code := l_city_name_change.state_code||'-'||
4031 l_city_name_change.county_code||'-'||
4032 l_city_name_change.old_city_code;
4033
4034 IF G_MODE = 'UPGRADE' THEN
4035
4036 OPEN get_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4037 FETCH get_county_name INTO l_county_name;
4038 CLOSE get_county_name;
4039
4040 INSERT INTO pay_us_geo_update
4041 (
4042 id,
4043 assignment_id,
4044 person_id,
4045 table_name,
4046 table_value_id,
4047 old_juri_code,
4048 new_juri_code,
4049 process_type,
4050 process_date,
4051 process_mode,
4052 status,
4053 description
4054 )
4055 SELECT DISTINCT
4056 p_geo_phase_id,
4057 NULL,
4058 pa.person_id,
4059 'PER_ADDRESSES',
4060 pa.address_id,
4061 l_jurisdiction_code,
4062 l_jurisdiction_code,
4063 'CY',
4064 sysdate,
4065 p_mode,
4066 NULL,
4067 'Address'||':'||l_city_name_change.old_city_name
4068 FROM per_addresses pa
4069 WHERE town_or_city = l_city_name_change.old_city_name
4070 AND region_1 = l_county_name
4071 AND NVL(region_2,l_city_name_change.state_abbrev) =
4072 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4073 AND country = l_city_name_change.country;
4074
4075 UPDATE per_addresses
4076 SET town_or_city = l_city_name_change.new_city_name,
4077 derived_locale = replace(derived_locale,
4078 l_city_name_change.old_city_name,
4079 l_city_name_change.new_city_name)
4080 WHERE town_or_city = l_city_name_change.old_city_name
4081 AND region_1 = l_county_name
4082 AND NVL(region_2,l_city_name_change.state_abbrev) =
4083 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4084 AND country = l_city_name_change.country;
4085
4086 IF l_city_name_change.country = 'US' THEN
4087
4088 INSERT INTO pay_us_geo_update
4089 (
4090 id,
4091 assignment_id,
4092 person_id,
4093 table_name,
4094 table_value_id,
4095 old_juri_code,
4096 new_juri_code,
4097 process_type,
4098 process_date,
4099 process_mode,
4100 status,
4101 description
4102 )
4103 SELECT DISTINCT
4104 p_geo_phase_id,
4105 NULL,
4106 pa.person_id,
4107 'PER_ADDRESSES',
4108 pa.address_id,
4109 l_jurisdiction_code,
4110 l_jurisdiction_code,
4111 'CY',
4112 sysdate,
4113 p_mode,
4114 NULL,
4115 'Taxation Address'||':'||l_city_name_change.old_city_name
4116 FROM per_addresses pa
4117 WHERE add_information18 = l_city_name_change.old_city_name
4118 AND add_information19 = l_county_name
4119 AND add_information17 = l_city_name_change.state_abbrev;
4120
4121 UPDATE per_addresses
4122 SET add_information18 = l_city_name_change.new_city_name
4123 WHERE add_information18 = l_city_name_change.old_city_name
4124 AND add_information19 = l_county_name
4125 AND add_information17 = l_city_name_change.state_abbrev;
4126
4127 END IF;
4128
4129 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',2);
4130
4131 INSERT INTO pay_us_geo_update
4132 (
4133 id,
4134 assignment_id,
4135 person_id,
4136 table_name,
4137 table_value_id,
4138 old_juri_code,
4139 new_juri_code,
4140 process_type,
4141 process_date,
4142 process_mode,
4143 status,
4144 description
4145 )
4146 SELECT DISTINCT
4147 p_geo_phase_id,
4148 NULL,
4149 NULL,
4150 'HR_LOCATIONS_ALL',
4151 hl.location_id,
4152 l_jurisdiction_code,
4153 l_jurisdiction_code,
4154 'CY',
4155 sysdate,
4156 p_mode,
4157 NULL,
4158 'Address'||':'||l_city_name_change.old_city_name
4159 FROM hr_locations_all hl
4160 WHERE town_or_city = l_city_name_change.old_city_name
4161 AND region_1 = l_county_name
4162 AND NVL(region_2,l_city_name_change.state_abbrev) =
4163 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4164 AND country = l_city_name_change.country;
4165
4166 UPDATE hr_locations_all
4167 SET town_or_city = l_city_name_change.new_city_name,
4168 derived_locale = replace(derived_locale,
4169 l_city_name_change.old_city_name,
4170 l_city_name_change.new_city_name)
4171 WHERE town_or_city = l_city_name_change.old_city_name
4172 AND region_1 = l_county_name
4173 AND NVL(region_2,l_city_name_change.state_abbrev) =
4174 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4175 AND country = l_city_name_change.country;
4176
4177 IF l_city_name_change.country = 'US' THEN
4178
4179 INSERT INTO pay_us_geo_update
4180 (
4181 id,
4182 assignment_id,
4183 person_id,
4184 table_name,
4185 table_value_id,
4186 old_juri_code,
4187 new_juri_code,
4188 process_type,
4189 process_date,
4190 process_mode,
4191 status,
4192 description
4193 )
4194 SELECT DISTINCT
4195 p_geo_phase_id,
4196 NULL,
4197 NULL,
4198 'HR_LOCATIONS_ALL',
4199 hl.location_id,
4200 l_jurisdiction_code,
4201 l_jurisdiction_code,
4202 'CY',
4203 sysdate,
4204 p_mode,
4205 NULL,
4206 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4207 FROM hr_locations_all hl
4208 WHERE loc_information18 = l_city_name_change.old_city_name
4209 AND loc_information19 = l_county_name
4210 AND loc_information17 = l_city_name_change.state_abbrev;
4211
4212 UPDATE hr_locations_all
4213 SET loc_information18 = l_city_name_change.new_city_name
4214 WHERE loc_information18 = l_city_name_change.old_city_name
4215 AND loc_information19 = l_county_name
4216 AND loc_information17 = l_city_name_change.state_abbrev;
4217
4218 INSERT INTO pay_us_geo_update
4219 (
4220 id,
4221 assignment_id,
4222 person_id,
4223 table_name,
4224 table_value_id,
4225 old_juri_code,
4226 new_juri_code,
4227 process_type,
4228 process_date,
4229 process_mode,
4230 status,
4231 description
4232 )
4233 SELECT DISTINCT
4234 p_geo_phase_id,
4235 NULL,
4236 NULL,
4237 'HR_ORGANIZATION_INFORMATION',
4238 hoi.org_information_id,
4239 l_jurisdiction_code,
4240 l_jurisdiction_code,
4241 'CY',
4242 sysdate,
4243 p_mode,
4244 NULL,
4245 'EEO_REPORT'||':'||'ORG_INFORMATION7'
4246 FROM hr_organization_information hoi
4247 WHERE org_information7 = l_city_name_change.old_city_name
4248 AND org_information8 = l_city_name_change.state_abbrev
4249 AND org_information_context = 'EEO_REPORT';
4250
4251 UPDATE hr_organization_information
4252 SET org_information7 = l_city_name_change.new_city_name
4253 WHERE org_information7 = l_city_name_change.old_city_name
4254 AND org_information8 = l_city_name_change.state_abbrev
4255 AND org_information_context = 'EEO_REPORT';
4256
4257 END IF;
4258
4259 ELSE
4260
4261 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',3);
4262
4263 OPEN get_new_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4264 FETCH get_new_county_name INTO l_county_name;
4265
4266 WHILE ( get_new_county_name%FOUND )
4267
4268 LOOP
4269
4270 INSERT INTO pay_us_geo_update
4271 (
4272 id,
4273 assignment_id,
4274 person_id,
4275 table_name,
4276 table_value_id,
4277 old_juri_code,
4278 new_juri_code,
4279 process_type,
4280 process_date,
4281 process_mode,
4282 status,
4283 description
4284 )
4285 SELECT DISTINCT
4286 p_geo_phase_id,
4287 NULL,
4288 pa.person_id,
4289 'PER_ADDRESSES',
4290 pa.address_id,
4291 l_jurisdiction_code,
4292 l_jurisdiction_code,
4293 'CY',
4294 sysdate,
4295 p_mode,
4296 NULL,
4297 'Address'||':'||l_city_name_change.old_city_name
4298 FROM per_addresses pa
4299 WHERE town_or_city = l_city_name_change.old_city_name
4300 AND region_1 = l_county_name
4301 AND NVL(region_2,l_city_name_change.state_abbrev) =
4302 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4303 AND country = l_city_name_change.country;
4304
4305 IF l_city_name_change.country = 'US' THEN
4306
4307 INSERT INTO pay_us_geo_update
4308 (
4309 id,
4310 assignment_id,
4311 person_id,
4312 table_name,
4313 table_value_id,
4314 old_juri_code,
4315 new_juri_code,
4316 process_type,
4317 process_date,
4318 process_mode,
4319 status,
4320 description
4321 )
4322 SELECT DISTINCT
4323 p_geo_phase_id,
4324 NULL,
4325 pa.person_id,
4326 'PER_ADDRESSES',
4327 pa.address_id,
4328 l_jurisdiction_code,
4329 l_jurisdiction_code,
4330 'CY',
4331 sysdate,
4332 p_mode,
4333 NULL,
4334 'Taxation Address'||':'||l_city_name_change.old_city_name
4335 FROM per_addresses pa
4336 WHERE add_information18 = l_city_name_change.old_city_name
4337 AND add_information19 = l_county_name
4338 AND add_information17 = l_city_name_change.state_abbrev;
4339
4340 END IF;
4341
4342 INSERT INTO pay_us_geo_update
4343 (
4344 id,
4345 assignment_id,
4346 person_id,
4347 table_name,
4348 table_value_id,
4349 old_juri_code,
4350 new_juri_code,
4351 process_type,
4352 process_date,
4353 process_mode,
4354 status,
4355 description
4356 )
4357 SELECT DISTINCT
4358 p_geo_phase_id,
4359 NULL,
4360 NULL,
4361 'HR_LOCATIONS_ALL',
4362 hl.location_id,
4363 l_jurisdiction_code,
4364 l_jurisdiction_code,
4365 'CY',
4366 sysdate,
4367 p_mode,
4368 NULL,
4369 'Address'||':'||l_city_name_change.old_city_name
4370 FROM hr_locations_all hl
4371 WHERE town_or_city = l_city_name_change.old_city_name
4372 AND region_1 = l_county_name
4373 AND NVL(region_2,l_city_name_change.state_abbrev) =
4374 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4375 AND country = l_city_name_change.country;
4376
4377 IF l_city_name_change.country = 'US' THEN
4378
4379 INSERT INTO pay_us_geo_update
4380 (
4381 id,
4382 assignment_id,
4383 person_id,
4384 table_name,
4385 table_value_id,
4386 old_juri_code,
4387 new_juri_code,
4388 process_type,
4389 process_date,
4390 process_mode,
4391 status,
4392 description
4393 )
4394 SELECT DISTINCT
4395 p_geo_phase_id,
4396 NULL,
4397 NULL,
4398 'HR_LOCATIONS_ALL',
4399 hl.location_id,
4400 l_jurisdiction_code,
4401 l_jurisdiction_code,
4402 'CY',
4403 sysdate,
4404 p_mode,
4405 NULL,
4406 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4407 FROM hr_locations_all hl
4408 WHERE loc_information18 = l_city_name_change.old_city_name
4409 AND loc_information19 = l_county_name
4410 AND loc_information17 = l_city_name_change.state_abbrev;
4411
4412 END IF;
4413
4414 FETCH get_new_county_name INTO l_county_name;
4415
4416 END LOOP;
4417
4418 CLOSE get_new_county_name;
4419
4420 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',4);
4421
4422 OPEN get_county_name(l_city_name_change.state_code,l_city_name_change.county_code);
4423 FETCH get_county_name INTO l_county_name;
4424 CLOSE get_county_name;
4425
4426 INSERT INTO pay_us_geo_update
4427 (
4428 id,
4429 assignment_id,
4430 person_id,
4431 table_name,
4432 table_value_id,
4433 old_juri_code,
4434 new_juri_code,
4435 process_type,
4436 process_date,
4437 process_mode,
4438 status,
4439 description
4440 )
4441 SELECT DISTINCT
4442 p_geo_phase_id,
4443 NULL,
4444 pa.person_id,
4445 'PER_ADDRESSES',
4446 pa.address_id,
4447 l_jurisdiction_code,
4448 l_jurisdiction_code,
4449 'CY',
4450 sysdate,
4451 p_mode,
4452 NULL,
4453 'Address'||':'||l_city_name_change.old_city_name
4454 FROM per_addresses pa
4455 WHERE town_or_city = l_city_name_change.old_city_name
4456 AND region_1 = l_county_name
4457 AND NVL(region_2,l_city_name_change.state_abbrev) =
4458 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4459 AND country = l_city_name_change.country;
4460
4461 IF l_city_name_change.country = 'US' THEN
4462
4463 INSERT INTO pay_us_geo_update
4464 (
4465 id,
4466 assignment_id,
4467 person_id,
4468 table_name,
4469 table_value_id,
4470 old_juri_code,
4471 new_juri_code,
4472 process_type,
4473 process_date,
4474 process_mode,
4475 status,
4476 description
4477 )
4478 SELECT DISTINCT
4479 p_geo_phase_id,
4480 NULL,
4481 pa.person_id,
4482 'PER_ADDRESSES',
4483 pa.address_id,
4484 l_jurisdiction_code,
4485 l_jurisdiction_code,
4486 'CY',
4487 sysdate,
4488 p_mode,
4489 NULL,
4490 'Taxation Address'||':'||l_city_name_change.old_city_name
4491 FROM per_addresses pa
4492 WHERE add_information18 = l_city_name_change.old_city_name
4493 AND add_information19 = l_county_name
4494 AND add_information17 = l_city_name_change.state_abbrev;
4495
4496 END IF;
4497
4498 INSERT INTO pay_us_geo_update
4499 (
4500 id,
4501 assignment_id,
4502 person_id,
4503 table_name,
4504 table_value_id,
4505 old_juri_code,
4506 new_juri_code,
4507 process_type,
4508 process_date,
4509 process_mode,
4510 status,
4511 description
4512 )
4513 SELECT DISTINCT
4514 p_geo_phase_id,
4515 NULL,
4516 NULL,
4517 'HR_LOCATIONS_ALL',
4518 hl.location_id,
4519 l_jurisdiction_code,
4520 l_jurisdiction_code,
4521 'CY',
4522 sysdate,
4523 p_mode,
4524 NULL,
4525 'Address'||':'||l_city_name_change.old_city_name
4526 FROM hr_locations_all hl
4527 WHERE town_or_city = l_city_name_change.old_city_name
4528 AND region_1 = l_county_name
4529 AND NVL(region_2,l_city_name_change.state_abbrev) =
4530 DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
4531 AND country = l_city_name_change.country;
4532
4533 IF l_city_name_change.country = 'US' THEN
4534
4535 INSERT INTO pay_us_geo_update
4536 (
4537 id,
4538 assignment_id,
4539 person_id,
4540 table_name,
4541 table_value_id,
4542 old_juri_code,
4543 new_juri_code,
4544 process_type,
4545 process_date,
4546 process_mode,
4547 status,
4548 description
4549 )
4550 SELECT DISTINCT
4551 p_geo_phase_id,
4552 NULL,
4553 NULL,
4554 'HR_LOCATIONS_ALL',
4555 hl.location_id,
4556 l_jurisdiction_code,
4557 l_jurisdiction_code,
4558 'CY',
4559 sysdate,
4560 p_mode,
4561 NULL,
4562 'Payroll Tax Address'||':'||l_city_name_change.old_city_name
4563 FROM hr_locations_all hl
4564 WHERE loc_information18 = l_city_name_change.old_city_name
4565 AND loc_information19 = l_county_name
4566 AND loc_information17 = l_city_name_change.state_abbrev;
4567
4568 INSERT INTO pay_us_geo_update
4569 (
4570 id,
4571 assignment_id,
4572 person_id,
4573 table_name,
4574 table_value_id,
4575 old_juri_code,
4576 new_juri_code,
4577 process_type,
4578 process_date,
4579 process_mode,
4580 status,
4581 description
4582 )
4583 SELECT DISTINCT
4584 p_geo_phase_id,
4585 NULL,
4586 NULL,
4587 'HR_ORGANIZATION_INFORMATION',
4588 hoi.org_information_id,
4589 l_jurisdiction_code,
4590 l_jurisdiction_code,
4591 'CY',
4592 sysdate,
4593 p_mode,
4594 NULL,
4595 'EEO_REPORT'||':'||'ORG_INFORMATION7'
4596 FROM hr_organization_information hoi
4597 WHERE org_information7 = l_city_name_change.old_city_name
4598 AND org_information8 = l_city_name_change.state_abbrev
4599 AND org_information_context = 'EEO_REPORT';
4600
4601 END IF;
4602
4603 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',5);
4604
4605 END IF; /*G_MODE = 'UPGRADE' if*/
4606
4607 FETCH get_city_name_changes INTO l_city_name_change;
4608
4609 END LOOP;
4610
4611 CLOSE get_city_name_changes;
4612
4613 IF P_CALL = 'EXTERNAL' THEN
4614
4615 pay_us_geocode_report_pkg.city_name_change_report('EXTERNAL',P_PATCH_NAME,G_MODE,p_geo_phase_id);
4616
4617 END IF;
4618
4619 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',6);
4620
4621 EXCEPTION
4622 WHEN OTHERS THEN
4623
4624 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
4625 ROLLBACK;
4626 hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',99);
4627
4628 fnd_file.put_line(fnd_file.log, 'Exception update_city_name' );
4629 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4630
4631 raise_application_error(-20001,l_error_message_text);
4632
4633 END update_city_name;
4634
4635 /* End of Changes for Bug#14314081 */
4636
4637 -- This procedure is separate from the above main upgrade_geocodes
4638 -- This procedure will update the org_information1 column in the
4639 -- hr_organization_information table where the org_information_context
4640 -- is 'Local Tax Rules'
4641 -- This procedure will only be run by one process, NOT MULTIPLE TIMES
4642
4643 PROCEDURE update_org_info(P_GEO_PHASE_ID IN NUMBER,
4644 P_MODE IN VARCHAR2,
4645 P_PATCH_NAME IN VARCHAR2)
4646
4647 IS
4648
4649 --Retrieve all changed geocodes in the hr_organization_information table
4650
4651 CURSOR org_info_cur IS
4652 SELECT distinct org_information1
4653 FROM pay_us_modified_geocodes pmod,
4654 hr_organization_information hoi
4655 WHERE pmod.state_code = substr(hoi.org_information1,1,2)
4656 AND pmod.county_code = substr(hoi.org_information1,4,3)
4657 AND pmod.old_city_code = substr(hoi.org_information1,8,4)
4658 AND pmod.process_type in ('UP','PU','RP')
4659 AND pmod.patch_name = p_patch_name
4660 AND hoi.org_information_context = 'Local Tax Rules'
4661 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
4662 where pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
4663 and pugu.new_juri_code = hoi.org_information1
4664 and pugu.process_mode = g_mode
4665 and pugu.process_type = g_process_type
4666 and pugu.id = g_geo_phase_id);
4667
4668 org_info_rec org_info_cur%ROWTYPE;
4669
4670 --
4671 --
4672 --Per bug 2996546
4673 --Added a cursor org_info_ca_cur to the procedure update_org_info
4674 --Retrieve all changed org_information1 on hr_organization_information table.
4675 --and update (for Canadian Legislation)
4676 --
4677
4678 CURSOR org_info_ca_cur IS
4679 SELECT distinct hoi.org_information1, hoi.org_information_id
4680 FROM pay_us_modified_geocodes pmod,
4681 hr_organization_information hoi
4682 WHERE pmod.state_code = 'CA'
4683 AND pmod.county_code = substr(hoi.org_information1,1,2)
4684 AND pmod.patch_name = p_patch_name
4685 AND hoi.org_information_context in
4686 (
4687 'Prov Reporting Est',
4688 'Provincial Information',
4689 'Provincial Reporting Info.',
4690 'Provincial Employment Standard',
4691 'Workers Comp Info.'
4692 ) ;
4693 org_info_ca_rec org_info_ca_cur%ROWTYPE;
4694 --
4695 --
4696 new_geocode hr_organization_information.org_information1%TYPE;
4697 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
4698 l_error_message_text varchar2(240);
4699
4700 BEGIN
4701
4702 g_geo_phase_id := p_geo_phase_id;
4703 g_mode := p_mode;
4704
4705 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
4706 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4707
4708 FOR org_info_rec IN org_info_cur LOOP
4709
4710 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',1);
4711
4712 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
4713 process_type
4714 INTO new_geocode, l_proc_type
4715 FROM pay_us_modified_geocodes pmod
4716 WHERE pmod.state_code = substr(org_info_rec.org_information1,1,2)
4717 AND pmod.county_code = substr(org_info_rec.org_information1,4,3)
4718 AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
4719 AND pmod.process_type in ('UP','PU','RP','U')
4720 AND pmod.patch_name = p_patch_name;
4721
4722 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',2);
4723
4724 IF G_MODE = 'UPGRADE' THEN
4725
4726 UPDATE hr_organization_information
4727 SET org_information1 = new_geocode
4728 WHERE org_information1 = org_info_rec.org_information1
4729 AND org_information_context = 'Local Tax Rules';
4730
4731 -- COMMIT;
4732
4733 END IF;
4734
4735 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',3);
4736
4737 -- write to the message table so that if this fails unexpectedly we can track which taxability
4738 -- rules have been upgraded already.
4739
4740 write_message(
4741 p_proc_type => l_proc_type,
4742 p_person_id => null,
4743 p_assign_id => null,
4744 p_old_juri_code => org_info_rec.org_information1,
4745 p_new_juri_code => new_geocode,
4746 p_location => 'HR_ORGANIZATION_INFORMATION',
4747 p_id => null);
4748
4749 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
4750
4751 END LOOP;
4752
4753 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
4754 --
4755 --
4756 --
4757
4758 --Per bug 2996546
4759 --Update of hr_organization_information . org_information1
4760 --(Canadian Legislation)
4761
4762 OPEN org_info_ca_cur;
4763 LOOP
4764 FETCH org_info_ca_cur into org_info_ca_rec;
4765 EXIT WHEN org_info_ca_cur%NOTFOUND;
4766
4767 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
4768
4769
4770 SELECT pmod.new_county_code,
4771 process_type
4772 INTO new_geocode, l_proc_type
4773 FROM pay_us_modified_geocodes pmod
4774 WHERE pmod.state_code = 'CA'
4775 AND pmod.county_code = substr(org_info_ca_rec.org_information1,1,2)
4776 AND pmod.patch_name = p_patch_name;
4777
4778 IF G_MODE = 'UPGRADE' THEN
4779
4780 UPDATE hr_organization_information
4781 SET org_information1 = new_geocode
4782 WHERE org_information1 = org_info_ca_rec.org_information1
4783 AND org_information_id = org_info_ca_rec.org_information_id
4784 AND org_information_context in
4785 (
4786 'Prov Reporting Est',
4787 'Provincial Information',
4788 'Provincial Reporting Info.',
4789 'Provincial Employment Standard',
4790 'Workers Comp Info.'
4791 ) ;
4792
4793 -- COMMIT;
4794
4795
4796 END IF;
4797 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
4798
4799 -- write to the message table so that if this fails unexpectedly
4800 --
4801
4802 write_message(
4803 p_proc_type => l_proc_type,
4804 p_person_id => null,
4805 p_assign_id => null,
4806 p_old_juri_code => org_info_rec.org_information1,
4807 p_new_juri_code => new_geocode,
4808 p_location => 'HR_ORGANIZATION_INFORMATION',
4809 p_id => null);
4810
4811 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
4812 END LOOP ;
4813 CLOSE org_info_ca_cur;
4814 --
4815 --
4816 --
4817
4818 EXCEPTION
4819 WHEN OTHERS THEN
4820 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
4821 rollback;
4822 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',6);
4823
4824 fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
4825 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4826
4827 raise_application_error(-20001,l_error_message_text);
4828
4829 END update_org_info;
4830
4831
4832 -- This api is used to upgrade assignments with a process type of US or SU
4833
4834 PROCEDURE upgrade_geo_api(P_ASSIGN_ID NUMBER,
4835 P_PATCH_NAME VARCHAR2,
4836 P_MODE VARCHAR2,
4837 P_CITY_NAME VARCHAR2)
4838 IS
4839
4840 -- Bug 3319878 -- Changed the query to remove FTS from PAY_US_GEO_UPDATE
4841
4842 CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
4843 SELECT 'Y'
4844 FROM dual
4845 WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
4846 FROM PAY_US_GEO_UPDATE pugu
4847 WHERE pugu.id = p_geo_phase_id
4848 AND pugu.process_mode = p_mode
4849 AND pugu.table_name is null
4850 AND pugu.table_value_id is null
4851 AND pugu.status <> 'C'
4852 AND rownum < 2 );
4853
4854
4855
4856 l_chk_last_api varchar2(2);
4857
4858 CURSOR pay_patch_id(p_patch_name VARCHAR2) IS
4859 SELECT ID
4860 FROM pay_patch_status
4861 WHERE patch_name = p_patch_name;
4862
4863 l_id number;
4864
4865 BEGIN
4866
4867 hr_utility.trace('Entering the Geocode Upgrade API');
4868
4869
4870 OPEN pay_patch_id(p_patch_name);
4871 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
4872
4873 FETCH pay_patch_id INTO l_id;
4874 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
4875
4876 CLOSE pay_patch_id;
4877
4878 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
4879
4880 IF p_mode = 'UPGRADE' THEN
4881
4882 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
4883
4884 upgrade_geocodes(P_ASSIGN_START => p_assign_id,
4885 P_ASSIGN_END => p_assign_id,
4886 P_GEO_PHASE_ID => l_id,
4887 P_MODE => 'UPGRADE',
4888 P_PATCH_NAME => p_patch_name,
4889 P_CITY_NAME => p_city_name,
4890 P_API_MODE => 'Y');
4891
4892 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
4893
4894 ELSE
4895
4896 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
4897
4898 upgrade_geocodes(P_ASSIGN_START => p_assign_id,
4899 P_ASSIGN_END => p_assign_id,
4900 P_GEO_PHASE_ID => l_id,
4901 P_MODE => 'DEBUG',
4902 P_PATCH_NAME => p_patch_name,
4903 P_CITY_NAME => p_city_name,
4904 P_API_MODE => 'Y');
4905
4906 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
4907
4908 END IF;
4909
4910 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
4911
4912 OPEN chk_last_api(l_id, p_mode);
4913
4914 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
4915
4916 FETCH chk_last_api INTO l_chk_last_api;
4917
4918 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
4919
4920 IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
4921
4922 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
4923
4924 UPDATE pay_patch_status
4925 SET status = 'C', phase = null
4926 WHERE id = l_id;
4927 hr_utility.set_location('before commit ',4);
4928
4929 -- commit;
4930
4931 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
4932
4933 END IF;
4934
4935 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
4936
4937 CLOSE chk_last_api;
4938
4939 hr_utility.trace('Exiting the Geocode Upgrade API');
4940
4941 EXCEPTION
4942 WHEN OTHERS THEN
4943 hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
4944
4945
4946 END upgrade_geo_api;
4947
4948 --
4949 --Per bug 2996546 created a public function
4950 --to return pay_input_values_f.input_value_id
4951 --after comparing values stored in a pl/sql table
4952 --
4953
4954 Function IS_US_OR_CA_LEGISLATION
4955 (p_input_value_id in pay_input_values_f.input_value_id%TYPE)
4956 Return pay_input_values_f.input_value_id%TYPE Is
4957
4958 Begin
4959 for l_number in 1..l_total
4960 loop
4961 If (input_val_cur(l_number) = p_input_value_id) THEN
4962 Return (p_input_value_id);
4963 End If;
4964 End loop;
4965 Return (0);
4966 End IS_US_OR_CA_LEGISLATION ;
4967
4968 --
4969 --
4970 --
4971 --Per bug 2996546,Added a new procedure update_ca_emp_info
4972 --to update pay_ca_emp_fed_tax_info_f.employment_province,
4973 --pay_ca_emp_prov_tax_info_f.province_code,
4974 --pay_ca_legislation_info.jurisdiction_code
4975 --
4976 --
4977 PROCEDURE update_ca_emp_info (P_GEO_PHASE_ID IN NUMBER,
4978 P_MODE IN VARCHAR2,
4979 P_PATCH_NAME IN VARCHAR2)
4980
4981 IS
4982 CURSOR canada_emp_fed_tax_cur IS
4983 SELECT distinct cafed.employment_province, cafed.assignment_id
4984 FROM pay_ca_emp_fed_tax_info_f cafed,
4985 pay_us_modified_geocodes pmod
4986 WHERE pmod.state_code = 'CA'
4987 AND pmod.county_code = cafed.employment_province
4988 AND pmod.patch_name = p_patch_name;
4989
4990 canada_emp_fed_rec canada_emp_fed_tax_cur%ROWTYPE;
4991
4992 CURSOR canada_emp_prov_tax_cur IS
4993 SELECT distinct caprov.province_code, caprov.assignment_id
4994 FROM pay_ca_emp_prov_tax_info_f caprov,
4995 pay_us_modified_geocodes pmod
4996 WHERE pmod.state_code = 'CA'
4997 AND pmod.county_code = caprov.province_code
4998 AND pmod.patch_name = p_patch_name;
4999
5000 canada_emp_prov_rec canada_emp_prov_tax_cur%ROWTYPE;
5001
5002 CURSOR canada_leg_info_cur IS
5003 SELECT distinct caleg.jurisdiction_code
5004 FROM pay_ca_legislation_info caleg,
5005 pay_us_modified_geocodes pmod
5006 WHERE pmod.state_code = 'CA'
5007 AND pmod.county_code = caleg.jurisdiction_code
5008 AND pmod.patch_name = p_patch_name ;
5009
5010 canada_leg_info_rec canada_leg_info_cur%ROWTYPE;
5011
5012
5013
5014 new_geocode pay_ca_emp_fed_tax_info_f .employment_province%TYPE;
5015 new_geocode1 pay_ca_emp_prov_tax_info_f.province_code%TYPE;
5016 new_geocode2 pay_ca_legislation_info. jurisdiction_code%TYPE;
5017 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
5018 l_error_message_text varchar2(240);
5019
5020 BEGIN
5021
5022 g_geo_phase_id := p_geo_phase_id;
5023 g_mode := p_mode;
5024
5025
5026 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
5027 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5028
5029 OPEN canada_emp_fed_tax_cur ;
5030 LOOP
5031 FETCH canada_emp_fed_tax_cur into canada_emp_fed_rec;
5032 EXIT WHEN canada_emp_fed_tax_cur%NOTFOUND;
5033
5034 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',1);
5035 SELECT pmod.new_county_code,
5036 pmod.process_type
5037 INTO new_geocode, l_proc_type
5038 FROM pay_us_modified_geocodes pmod
5039 WHERE pmod.state_code = 'CA'
5040 AND pmod.county_code = canada_emp_fed_rec.employment_province
5041 AND pmod.patch_name = p_patch_name;
5042
5043 IF G_MODE = 'UPGRADE' THEN
5044
5045
5046 UPDATE pay_ca_emp_fed_tax_info_f
5047 SET employment_province = new_geocode
5048 WHERE employment_province = canada_emp_fed_rec.employment_province
5049 AND assignment_id = canada_emp_fed_rec.assignment_id ;
5050
5051 -- COMMIT;
5052
5053
5054 END IF;
5055
5056
5057 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',2);
5058 -- write to the message table so that if this fails unexpectedly
5059 write_message(
5060 p_proc_type => l_proc_type,
5061 p_person_id => null,
5062 p_assign_id => canada_emp_fed_rec.assignment_id,
5063 p_old_juri_code => canada_emp_fed_rec.employment_province,
5064 p_new_juri_code => new_geocode,
5065 p_location => 'PAY_CA_EMP_FED_TAX_INFO_F',
5066 p_id => null);
5067
5068 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
5069
5070 END LOOP ;
5071 CLOSE canada_emp_fed_tax_cur ;
5072
5073 OPEN canada_emp_prov_tax_cur ;
5074 LOOP
5075 FETCH canada_emp_prov_tax_cur into canada_emp_prov_rec;
5076 EXIT WHEN canada_emp_prov_tax_cur%NOTFOUND;
5077
5078 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',4);
5079
5080 SELECT pmod.new_county_code,
5081 pmod.process_type
5082 INTO new_geocode1, l_proc_type
5083 FROM pay_us_modified_geocodes pmod
5084 WHERE pmod.state_code = 'CA'
5085 AND pmod.county_code = canada_emp_prov_rec.province_code
5086 AND pmod.patch_name = p_patch_name;
5087
5088
5089 IF G_MODE = 'UPGRADE' THEN
5090
5091
5092 UPDATE pay_ca_emp_prov_tax_info_f
5093 SET province_code = new_geocode1
5094 WHERE province_code = canada_emp_prov_rec.province_code
5095 AND assignment_id = canada_emp_prov_rec.assignment_id ;
5096
5097 -- COMMIT;
5098
5099
5100 END IF;
5101
5102
5103 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',5);
5104 -- write to the message table so that if this fails unexpectedly
5105 write_message(
5106 p_proc_type => l_proc_type,
5107 p_person_id => null,
5108 p_assign_id => canada_emp_prov_rec.assignment_id,
5109 p_old_juri_code => canada_emp_prov_rec.province_code,
5110 p_new_juri_code => new_geocode1,
5111 p_location => 'PAY_CA_EMP_PROV_TAX_INFO_F',
5112 p_id => null);
5113
5114 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
5115 END LOOP ;
5116 CLOSE canada_emp_prov_tax_cur ;
5117
5118
5119
5120 OPEN canada_leg_info_cur;
5121 LOOP
5122 FETCH canada_leg_info_cur into canada_leg_info_rec ;
5123 EXIT WHEN canada_leg_info_cur%NOTFOUND;
5124
5125 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',7);
5126 SELECT pmod.new_county_code,
5127 pmod.process_type
5128 INTO new_geocode2, l_proc_type
5129 FROM pay_us_modified_geocodes pmod
5130 WHERE pmod.state_code = 'CA'
5131 AND pmod.county_code = canada_leg_info_rec.jurisdiction_code
5132 AND pmod.patch_name = p_patch_name;
5133
5134 IF G_MODE = 'UPGRADE' THEN
5135
5136 UPDATE pay_ca_legislation_info
5137 SET jurisdiction_code = new_geocode2
5138 WHERE jurisdiction_code = canada_leg_info_rec.jurisdiction_code ;
5139 -- COMMIT;
5140
5141
5142 END IF;
5143
5144 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',8);
5145 -- write to the message table so that if this fails unexpectedly
5146 write_message(
5147 p_proc_type => l_proc_type,
5148 p_person_id => null,
5149 p_assign_id => null,
5150 p_old_juri_code => canada_leg_info_rec.jurisdiction_code,
5151 p_new_juri_code => new_geocode2,
5152 p_location => 'PAY_CA_LEGISLATION_INFO',
5153 p_id => null);
5154
5155 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
5156 END LOOP ;
5157
5158 CLOSE canada_leg_info_cur;
5159
5160 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',10);
5161 EXCEPTION
5162 WHEN OTHERS THEN
5163 l_error_message_text := to_char(SQLCODE)||SQLERRM||
5164 ' Program error contact support';
5165 rollback;
5166 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',11);
5167
5168 fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5169 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
5170
5171 raise_application_error(-20001,l_error_message_text);
5172
5173 hr_utility.set_location('before commit ',5);
5174 -- commit;
5175 END update_ca_emp_info ;
5176 --
5177 --
5178 --
5179 --
5180 --
5181 --
5182 --Per bug 2996546,Created a new procedure group_level_balance to
5183 --update pay_run_balances.jurisdiction_code
5184 --for group level balances (both US and Canadian
5185 --legislation)
5186 --
5187 --
5188 --
5189 PROCEDURE group_level_balance (P_START_PAYROLL_ACTION IN NUMBER,
5190 P_END_PAYROLL_ACTION IN NUMBER,
5191 P_GEO_PHASE_ID IN NUMBER,
5192 P_MODE IN VARCHAR2,
5193 P_PATCH_NAME IN VARCHAR2)
5194 IS
5195
5196 /* Bug# 3679984 Forced the index PAY_US_MODIFIED_GEOCODES_PK on pay_us_modified_geocodes
5197 and rearranged the order of the where clause in the subquery */
5198
5199 /* Bug 4773276 Changing the hint to PAY_US_MODIFIED_GEOCODES_N1 */
5200
5201 CURSOR group_level_bal_us (c_payroll_action_id number) IS
5202 select
5203 prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
5204 from pay_run_balances prb, pay_us_modified_geocodes pmod
5205 Where prb.payroll_action_id = c_payroll_action_id
5206 --between p_start_payroll_action and p_end_payroll_action
5207 and prb.assignment_id is null
5208 and pmod.state_code = substr(prb.jurisdiction_code,1,2)
5209 and pmod.county_code = substr(prb.jurisdiction_code,4,3)
5210 and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
5211 and pmod.process_type in ('PU', 'UP')
5212 and pmod.patch_name = p_patch_name;
5213
5214 /* and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5215 where pugu.process_type = g_process_type
5216 and pugu.process_mode = g_mode
5217 and pugu.assignment_id is null
5218 and pugu.old_juri_code = prb.jurisdiction_code
5219 and pugu.person_id = prb.run_balance_id
5220 and pugu.table_name = 'PAY_RUN_BALANCES'
5221 and pugu.id = g_geo_phase_id);*/
5222
5223 /* select /*+ ORDERED
5224 index(pmod PAY_US_MODIFIED_GEOCODES_N1)
5225 USE_NL(prb pdb pbd pmod) */
5226 /* prb.run_balance_id,
5227 prb.jurisdiction_code,
5228 prb.jurisdiction_comp3
5229 from pay_run_balances prb,
5230 pay_defined_balances pdb,
5231 pay_balance_dimensions pbd,
5232 pay_us_modified_geocodes pmod
5233 Where prb.payroll_action_id = c_payroll_action_id
5234 --between p_start_payroll_action and p_end_payroll_action
5235 and prb.assignment_id is null
5236 and prb.defined_balance_id = pdb.defined_balance_id
5237 and pdb.balance_dimension_id = pbd.balance_dimension_id
5238 and pbd.dimension_level = 'GRP'
5239 and pdb.legislation_code = 'US'
5240 and pbd.database_item_suffix like '%JD%'
5241 and pmod.state_code = substr(prb.jurisdiction_code,1,2)
5242 and pmod.county_code = substr(prb.jurisdiction_code,4,3)
5243 and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
5244 and pmod.patch_name = p_patch_name
5245 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5246 where pugu.process_type = g_process_type
5247 and pugu.process_mode = g_mode
5248 and pugu.assignment_id is null
5249 and pugu.old_juri_code = pmod.state_code || '-' || pmod.county_code || '-' || pmod.old_city_code --prb.jurisdiction_code
5250 and pugu.person_id = prb.payroll_action_id
5251 and pugu.table_name = 'PAY_RUN_BALANCES'
5252 and pugu.id = g_geo_phase_id);
5253 */
5254 group_level_bal_us_rec group_level_bal_us%ROWTYPE;
5255
5256
5257 CURSOR group_level_bal_ca (c_payroll_action_id number) IS
5258 select
5259 prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
5260 from pay_run_balances prb, pay_us_modified_geocodes pmod
5261 Where prb.payroll_action_id = c_payroll_action_id
5262 --between p_start_payroll_action and p_end_payroll_action
5263 and prb.assignment_id is null
5264 and pmod.state_code = 'CA'
5265 and pmod.county_code = substr(prb.jurisdiction_code,1,2)
5266 and pmod.process_type in ('PU', 'UP')
5267 and pmod.patch_name = p_patch_name;
5268
5269 /*
5270 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5271 where pugu.old_juri_code = prb.jurisdiction_code
5272 and pugu.assignment_id is null
5273 and pugu.person_id = prb.run_balance_id
5274 and pugu.table_name = 'PAY_RUN_BALANCES'
5275 and pugu.process_mode = g_mode
5276 and pugu.process_type = g_process_type
5277 and pugu.id = g_geo_phase_id); */
5278
5279 /*select /*+ ORDERED
5280 index(pmod PAY_US_MODIFIED_GEOCODES_N1)
5281 USE_NL(prb pdb pbd pmod) */
5282 /* prb.run_balance_id,
5283 prb.jurisdiction_code,
5284 prb.jurisdiction_comp3
5285 from pay_run_balances prb,
5286 pay_defined_balances pdb,
5287 pay_balance_dimensions pbd,
5288 pay_us_modified_geocodes pmod
5289 Where prb.payroll_action_id = c_payroll_action_id
5290 --between p_start_payroll_action and p_end_payroll_action
5291 and prb.assignment_id is null
5292 and prb.defined_balance_id = pdb.defined_balance_id
5293 and pdb.balance_dimension_id = pbd.balance_dimension_id
5294 and pbd.dimension_level = 'GRP'
5295 and pdb.legislation_code = 'CA'
5296 and pbd.database_item_suffix like '%JD%'
5297 and pmod.state_code = 'CA'
5298 and pmod.county_code = substr(prb.jurisdiction_code,1,2)
5299 and pmod.patch_name = p_patch_name
5300 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
5301 where pugu.old_juri_code = prb.jurisdiction_code
5302 and pugu.assignment_id is null
5303 and pugu.person_id = prb.payroll_action_id
5304 and pugu.table_name = 'PAY_RUN_BALANCES'
5305 and pugu.process_mode = g_mode
5306 and pugu.process_type = g_process_type
5307 and pugu.id = g_geo_phase_id); */
5308
5309 CURSOR c_legislation_code
5310 (
5311 c_start_pactid number,
5312 c_end_pactid number
5313 ) is
5314 select pbg.legislation_code,
5315 ppa.payroll_action_id
5316 from per_business_groups pbg, pay_payroll_actions ppa
5317 Where ppa.payroll_action_id between c_start_pactid and c_end_pactid
5318 and pbg.business_group_id = ppa.business_group_id;
5319
5320 group_level_bal_ca_rec group_level_bal_ca%ROWTYPE;
5321
5322
5323 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
5324 l_geocode pay_run_balances.jurisdiction_code%TYPE;
5325 l_new_city_code pay_us_modified_geocodes.new_city_code%TYPE;
5326 l_legislation_code per_business_groups.legislation_code%TYPE;
5327 l_pactid pay_payroll_actions.payroll_action_id%TYPE;
5328
5329 l_row_updated varchar2(1);
5330
5331 l_error_message_text varchar2(240);
5332
5333 BEGIN
5334
5335 g_geo_phase_id := p_geo_phase_id;
5336 g_mode := p_mode;
5337
5338 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance');
5339 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
5340
5341 OPEN c_legislation_code ( p_start_payroll_action
5342 ,p_end_payroll_action) ;
5343
5344 LOOP
5345 FETCH c_legislation_code into l_legislation_code,
5346 l_pactid;
5347 EXIT WHEN c_legislation_code%NOTFOUND;
5348
5349
5350
5351 If l_legislation_code = 'US' THEN
5352 OPEN group_level_bal_us (l_pactid);
5353 LOOP
5354 FETCH group_level_bal_us into group_level_bal_us_rec;
5355 EXIT WHEN group_level_bal_us%NOTFOUND;
5356
5357
5358 begin
5359
5360 l_row_updated := 'N';
5361
5362 select 'Y'
5363 into l_row_updated
5364 from PAY_US_GEO_UPDATE pugu
5365 where pugu.old_juri_code = group_level_bal_us_rec.jurisdiction_code
5366 and pugu.assignment_id is null
5367 and pugu.person_id = group_level_bal_us_rec.run_balance_id
5368 and pugu.table_name = 'PAY_RUN_BALANCES'
5369 and pugu.process_mode = g_mode
5370 and pugu.process_type = g_process_type
5371 and pugu.id = g_geo_phase_id;
5372
5373 exception
5374
5375 when no_data_found then
5376
5377 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',1);
5378 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
5379 process_type, pmod.new_city_code
5380 INTO l_geocode, l_proc_type, l_new_city_code
5381 FROM pay_us_modified_geocodes pmod
5382 WHERE pmod.state_code = substr(group_level_bal_us_rec.jurisdiction_code,1,2)
5383 AND pmod.county_code = substr(group_level_bal_us_rec.jurisdiction_code,4,3)
5384 AND pmod.old_city_code = substr(group_level_bal_us_rec.jurisdiction_code,8,4)
5385 AND pmod.process_type in ('UP','PU','RP','U','US','D','SU')
5386 AND pmod.patch_name = p_patch_name;
5387
5388 IF G_MODE = 'UPGRADE' THEN
5389
5390 UPDATE pay_run_balances
5391 SET jurisdiction_code = l_geocode,
5392 jurisdiction_comp3 = l_new_city_code
5393 WHERE payroll_action_id = group_level_bal_us_rec.run_balance_id
5394 -- AND jurisdiction_comp3 = group_level_bal_us_rec.jurisdiction_comp3
5395 AND jurisdiction_code = group_level_bal_us_rec.jurisdiction_code;
5396
5397 -- COMMIT;
5398 END IF;
5399
5400
5401 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',2);
5402 -- write to the message table so that if this fails unexpectedly
5403 write_message(
5404 p_proc_type => l_proc_type,
5405 p_person_id => group_level_bal_us_rec.run_balance_id,
5406 p_assign_id => null,
5407 p_old_juri_code => group_level_bal_us_rec.jurisdiction_code,
5408 p_new_juri_code => l_geocode,
5409 p_location => 'PAY_RUN_BALANCES',
5410 p_id => null);
5411
5412 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',3);
5413
5414 end;
5415
5416 END LOOP ;
5417 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7001');
5418
5419 CLOSE group_level_bal_us ;
5420
5421 else
5422
5423 OPEN group_level_bal_ca (l_pactid);
5424 LOOP
5425 FETCH group_level_bal_ca into group_level_bal_ca_rec;
5426 EXIT WHEN group_level_bal_ca%NOTFOUND;
5427
5428
5429 begin
5430
5431 l_row_updated := 'N';
5432
5433 select 'Y'
5434 into l_row_updated
5435 from PAY_US_GEO_UPDATE pugu
5436 where pugu.old_juri_code = group_level_bal_ca_rec.jurisdiction_code
5437 and pugu.assignment_id is null
5438 and pugu.person_id = group_level_bal_ca_rec.run_balance_id
5439 and pugu.table_name = 'PAY_RUN_BALANCES'
5440 and pugu.process_mode = g_mode
5441 and pugu.process_type = g_process_type
5442 and pugu.id = g_geo_phase_id;
5443
5444 exception
5445
5446 when no_data_found then
5447
5448 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7002');
5449 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',4);
5450 SELECT pmod.new_county_code, pmod.process_type
5451 INTO l_geocode, l_proc_type
5452 FROM pay_us_modified_geocodes pmod
5453 WHERE pmod.state_code = 'CA'
5454 -- AND pmod.county_code = group_level_bal_ca_rec.jurisdiction_code
5455 AND pmod.county_code = substr(group_level_bal_ca_rec.jurisdiction_code,1,2)
5456 AND pmod.patch_name = p_patch_name;
5457
5458 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7003');
5459
5460 IF G_MODE = 'UPGRADE' THEN
5461
5462 UPDATE pay_run_balances
5463 SET jurisdiction_code = l_geocode
5464 WHERE payroll_action_id = group_level_bal_ca_rec.run_balance_id
5465 -- AND jurisdiction_comp3 = group_level_bal_ca_rec.jurisdiction_comp3
5466 -- AND jurisdiction_code = group_level_bal_ca_rec.jurisdiction_code
5467 AND substr(jurisdiction_code,1,2) =
5468 substr(group_level_bal_ca_rec.jurisdiction_code,1,2) ;
5469
5470 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7004');
5471
5472 -- COMMIT;
5473 END IF;
5474
5475 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',5);
5476 -- write to the message table so that if this fails unexpectedly
5477 write_message(
5478 p_proc_type => l_proc_type,
5479 p_person_id => group_level_bal_ca_rec.run_balance_id,
5480 p_assign_id => null,
5481 p_old_juri_code => group_level_bal_ca_rec.jurisdiction_code,
5482 p_new_juri_code => l_geocode,
5483 p_location => 'PAY_RUN_BALANCES',
5484 p_id => null);
5485
5486 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',6);
5487
5488 end;
5489
5490 END LOOP ;
5491
5492 CLOSE group_level_bal_ca;
5493
5494 END IF; --l_legislation_code
5495
5496 END LOOP;
5497
5498 CLOSE c_legislation_code;
5499
5500 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',7);
5501 EXCEPTION
5502 WHEN OTHERS THEN
5503 l_error_message_text := to_char(SQLCODE)||SQLERRM||
5504 ' Program error contact support';
5505
5506 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7005');
5507 hr_utility.trace('l_error_message_text - ' ||l_error_message_text);
5508
5509
5510 fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
5511 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
5512
5513
5514 rollback;
5515
5516
5517
5518 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
5519 raise_application_error(-20001,l_error_message_text);
5520
5521 hr_utility.set_location('before commit ',6);
5522 -- commit;
5523 END group_level_balance ;
5524 --
5525
5526 END pay_us_geo_upd_pkg;
[Home] [Help]