1 PACKAGE BODY pay_us_geo_upd_pkg as
2 /* $Header: pyusgeou.pkb 120.14.12010000.3 2008/08/14 11:14:12 pannapur 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
50
51 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
52
53 --
54
55 leg_param pay_payroll_actions.legislative_parameters%type;
56 l_year varchar2(4);
57
58 ln_upgrade_patch pay_patch_status.patch_name%TYPE;
59 --
60 begin
61
62 hr_utility.trace('reached range_cursor');
63
64 select ppa.legislative_parameters,
65 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters)
66 into leg_param,
67 ln_upgrade_patch
68 from pay_payroll_actions ppa
69 where ppa.payroll_action_id = pactid;
70
71 sqlstr := ' select distinct paf.person_id
72 from pay_us_modified_geocodes mg,
73 pay_us_emp_city_tax_rules_f tr,
74 per_all_assignments_f paf,
75 pay_us_states pus
76 where mg.patch_name = '''||ln_upgrade_patch||'''
77 and mg.state_code = pus.state_code
78 and mg.state_code = tr.state_code
79 and mg.county_code = tr.county_code
80 and mg.old_city_code = tr.city_code
81 and tr.assignment_id = paf.assignment_id
82 and :pactid is not null
83 order by paf.person_id';
84
85 hr_utility.trace(sqlstr);
86
87 hr_utility.trace('leaving range_cursor');
88
89 end range_cursor;
90
91
92 ---------------------------------- action_creation ----------------------------------
93 --
94 procedure action_creation (pactid in number,
95 stperson in number,
96 endperson in number,
97 chunk in number) is
98
99 leg_param pay_payroll_actions.legislative_parameters%type;
100 l_year varchar2(4);
101 l_geo_phase_id number;
102 l_mode Pay_Payroll_actions.legislative_parameters%type;
103
104 l_patch_name pay_patch_status.patch_name%TYPE;
105
106
107
108
109 cursor c_parameters ( pactid number) is
110 select ppa.legislative_parameters,
111 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
112 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
113 from pay_payroll_actions ppa
114 where ppa.payroll_action_id = pactid;
115
116
117 CURSOR c_actions_assignment
118 (
119 pactid number,
120 stperson number,
121 endperson number
122 ) is
123
124 SELECT distinct ectr.assignment_id
125 FROM per_all_assignments_f paf,
126 pay_us_emp_city_tax_rules_f ectr,
127 pay_us_modified_geocodes pmod
128 WHERE pmod.state_code = ectr.state_code
129 AND pmod.county_code = ectr.county_code
130 AND pmod.new_county_code is null
131 AND pmod.old_city_code = ectr.city_code
132 AND pmod.process_type in ('UP','US','PU','D','SU')
133 AND pmod.patch_name = l_patch_name
134 AND ectr.assignment_id = paf.assignment_id
135 AND paf.person_id between stperson and endperson
136 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
137 where pugu.assignment_id = ectr.assignment_id
138 and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
139 and pugu.old_juri_code = ectr.jurisdiction_code
140 and pugu.table_value_id is null
141 and pugu.table_name is null
142 and pugu.process_type = pmod.process_type
143 and pugu.process_mode = l_mode
144 and pugu.id = l_geo_phase_id)
145 UNION ALL
146
147 SELECT distinct pac.assignment_id
148 FROM per_all_assignments_f paf,
149 pay_action_contexts pac,
150 pay_us_modified_geocodes pmod
151 WHERE pmod.state_code = 'CA'
152 AND pmod.county_code = pac.context_value
153 AND pac.context_id in (select context_id
154 from ff_contexts
155 where context_name = 'JURISDICTION_CODE')
156 AND pmod.patch_name = l_patch_name
157 AND pac.assignment_id = paf.assignment_id
158 AND paf.person_id between stperson and endperson ;
159
160
161 /* Changing the hint to use index PAY_US_MODIFIED_GEOCODES_N1 */
162 CURSOR c_actions_run_bal
163 (
164 pactid number,
165 p_balance_load_date date
166 ) is
167 select ppa.payroll_action_id
168 from per_business_groups pbg, pay_payroll_actions ppa
169 Where ppa.action_type in ('R', 'Q', 'I', 'B', 'V')
170 and ppa.effective_date >= p_balance_load_date
171 and pbg.business_group_id = ppa.business_group_id
172 and pbg.legislation_code in ( 'US', 'CA');
173
174 cursor c_get_phase_id (p_patch_name varchar2)
175 is
176 select ID
177 from pay_patch_status
178 where patch_name = p_patch_name
179 and status in ('P','E');
180
181 Cursor c_geo_check (p_patch_name in varchar2) is
182 select phase, status from pay_patch_status
183 where patch_name like p_patch_name || '%'
184 and legislation_code = 'US';
185
186 l_assignment_id number;
187 l_payact_id number;
188 lockingactid number;
189
190 lv_phase varchar2(30);
191 lv_status varchar2(2);
192
193 l_balance_load_date pay_balance_validation.balance_load_date%type;
194 --
195
196 begin
197
198 -- hr_utility.trace_on('','TCL');
199
200 hr_utility.trace('entering action_creation');
201 hr_utility.set_location('geocode_action_creation',1);
202
203 open c_parameters(pactid);
204
205 fetch c_parameters into leg_param,
206 l_patch_name,
207 l_mode;
208
209 close c_parameters;
210
211 hr_utility.trace('l_patch_name is '|| l_patch_name );
212
213
214 hr_utility.trace('before open c_geo_check ');
215 open c_geo_check (l_patch_name);
216
217 fetch c_geo_check into lv_phase, lv_status;
218
219 if c_geo_check%notfound or lv_status <> 'C' then
220 hr_utility.trace('c_geo_check not found ');
221
222 if c_geo_check%notfound and chunk=1 then
223 hr_utility.trace('c_geo_check not found chunk = 1');
224 /*
225 If both conditions above are true, there is a geocode update
226 underway and a row for this process needs to be added to the
227 pay_patch_status table.
228 */
229 hr_utility.trace('inserting into pay_patch_status ');
230 insert into pay_patch_status
231 (ID,
232 PATCH_NUMBER,
233 PATCH_NAME,
234 PHASE,
235 PROCESS_TYPE,
236 APPLIED_DATE,
237 STATUS,
238 DESCRIPTION,
239 UPDATE_DATE,
240 LEGISLATION_CODE,
241 APPLICATION_RELEASE,
242 PREREQ_PATCH_NAME)
243 values
244 (PAY_PATCH_STATUS_S.nextval,
245 '1111111',
246 l_patch_name, --p_patch_name,
247 'START',
248 null,
249 sysdate,
250 'P',
251 'CURRENT GEOCODE PATCH', -- lv_patch_desc,
252 null,
253 'US',
254 '115',
255 'Q2' );
256
257 end if; -- end if for the chunk=1
258
259 hr_utility.trace('opening c_get_phase_id ');
260
261 open c_get_phase_id(l_patch_name);
262
263 fetch c_get_phase_id into l_geo_phase_id;
264
265
266 hr_utility.trace('value of l_geo_phase id is '|| to_char(l_geo_phase_id ));
267
268
269 hr_utility.set_location('geocode_action_creation',2);
270 open c_actions_assignment(pactid,stperson,endperson);
271
272 loop
273 hr_utility.set_location('geocode_action_creation',3);
274 fetch c_actions_assignment into l_assignment_id;
275
276 exit when c_actions_assignment%notfound;
277
278 hr_utility.set_location('geocode_action_creation',4);
279 select pay_assignment_actions_s.nextval
280 into lockingactid
281 from dual;
282
283 -- insert the action record.
284
285 hr_nonrun_asact.insact(lockingactid => lockingactid,
286 Object_Id => l_assignment_id,
287 pactid => pactid,
288 chunk => chunk,
289 object_type => 'ASG');
290 --
291 end loop; -- loop 1
292 close c_actions_assignment;
293
294
295 -- Create actions for GRE level Run balances
296
297 hr_utility.set_location('geocode_action_creation',5);
298
299
300 hr_utility.trace('before update_taxability_rules value of l_geo_phase_Id is '|| to_char(l_geo_phase_Id));
301
302 IF chunk=1 THEN
303
304 select min(balance_load_date)
305 into l_balance_load_date
306 from pay_balance_validation;
307 open c_actions_run_bal(pactid,l_balance_load_date);
308
309 loop
310 hr_utility.set_location('gocode_action_creation',6);
311 fetch c_actions_run_bal into l_payact_id;
312
313 exit when c_actions_run_bal%notfound;
314 --
315
316 hr_utility.set_location('gocode_action_creation',7);
317 select pay_assignment_actions_s.nextval
318 into lockingactid
319 from dual;
320 --
321
322 hr_nonrun_asact.insact(lockingactid => lockingactid,
323 Object_id => l_payact_id,
324 pactid => pactid,
325 chunk => chunk,
326 object_type => 'PER');
327 --
328 end loop; -- loop 1
329 close c_actions_run_bal;
330
331
332 pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
333
334 pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
335
336 pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
337
338 END IF;
339
340
341 hr_utility.trace('leaving action_creation');
342
343 if c_get_phase_id%isopen then
344 close c_get_phase_id;
345 end if;
346
347 END IF; /* lv_status patch is 'C' and no actions were created */
348
349 if c_geo_check%isopen then
350 close c_geo_check;
351 end if;
352
353 end action_creation;
354
355
356 procedure sort_action
357 (
358 payactid in varchar2, /* payroll action id */
359 sqlstr in out nocopy varchar2, /* string holding the sql statement */
360 len out nocopy number /* length of the sql string */
361 ) is
362 begin
363
364 sqlstr := 'select paa1.rowid
365 from pay_assignment_actions paa1, -- PYUGEN assignment action
366 pay_payroll_actions ppa1 -- PYUGEN payroll action id
367 where ppa1.payroll_action_id = :pactid
368 and paa1.payroll_action_id = ppa1.payroll_action_id
369 order by paa1.assignment_action_id
370 for update of paa1.assignment_id';
371
372 len := length(sqlstr); -- return the length of the string.
373 end sort_action;
374
375
376 PROCEDURE archive_code(p_xfr_action_id in number
377 ,p_effective_date in date)
378 IS
379
380 cursor c_xfr_info (cp_assignment_action in number) is
381 select ptoa.payroll_action_id,
382 ptoa.object_id,
383 ptoa.object_type
384 from PAY_TEMP_OBJECT_ACTIONS ptoa
385 where ptoa.object_action_id = cp_assignment_action;
386
387 cursor c_parameters ( pactid number) is
388 select ppa.legislative_parameters,
389 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
390 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
391 from pay_payroll_actions ppa
392 where ppa.payroll_action_id = pactid;
393
394 cursor c_get_phase_id (p_patch_name varchar2)
395 is
396 select ID
397 from pay_patch_status
398 where patch_name = p_patch_name
399 and status in ('P','E');
400
401 l_payroll_action_id number;
402 l_object_id number;
403 l_object_type PAY_TEMP_OBJECT_ACTIONS.object_type%TYPE;
404
405 l_geo_phase_id number;
406 l_year varchar2(4);
407 l_mode varchar2(7);
408 leg_param pay_payroll_actions.legislative_parameters%type;
409 l_patch_name pay_patch_status.patch_name%type;
410
411 BEGIN
412
413 hr_utility.set_location ('pay_us_geo_update.action_code', 1);
414
415 open c_xfr_info (p_xfr_action_id);
416
417 fetch c_xfr_info into l_payroll_action_id,
418 l_object_id,
419 l_object_type;
420
421 close c_xfr_info;
422
423 open c_parameters(l_payroll_action_id);
424
425 fetch c_parameters into leg_param,
426 l_patch_name,
427 l_mode;
428 close c_parameters;
429
430 open c_get_phase_id(l_patch_name);
431 fetch c_get_phase_id into l_geo_phase_id;
432 close c_get_phase_id;
433
434
435 if l_object_type = 'ASG' THEN
436
437 pay_us_geo_upd_pkg.upgrade_geocodes (p_assign_start => l_object_id,
438 p_assign_end => l_object_id,
439 p_geo_phase_id => l_geo_phase_id,
440 p_mode => l_mode,
441 p_patch_name => l_patch_name);
442
443
444 elsif l_object_type = 'PER' Then
445
446 pay_us_geo_upd_pkg.group_level_balance (P_START_PAYROLL_ACTION => l_object_id,
447 P_END_PAYROLL_ACTION => l_object_id,
448 P_GEO_PHASE_ID => l_geo_phase_id,
449 P_MODE => l_mode,
450 P_PATCH_NAME => l_patch_name);
451
452 END IF;
453
454 END archive_code;
455
456
457
458 procedure archive_deinit( p_payroll_action_id in number)
459 is
460 --
461 --
462 Cursor c_get_params is
463 select patch_name, patch_number
464 from pay_patch_status
465 where description = 'CURRENT GEOCODE PATCH';
466
467 Cursor c_geo_check (p_patch_name in varchar2,
468 p_patch_number in number ) is
469 select id from pay_patch_status
470 where patch_name = p_patch_name
471 and patch_number = p_patch_number
472 and legislation_code = 'US';
473
474
475 -- Bug 3354053 -- Changed the cursor query to remove the FTS from pay_us_geo_update.
476 Cursor c_geo_upd (p_patch_id in number,
477 p_patch_status in varchar2) is
478 select 'x' from dual
479 where exists(select 'x' from pay_us_geo_update
480 where id = p_patch_id
481 and status = p_patch_status
482 and rownum < 2);
483
484 cursor c_parameters ( pactid number) is
485 select ppa.legislative_parameters,
486 pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
487 pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
488 from pay_payroll_actions ppa
489 where ppa.payroll_action_id = pactid;
490
491
492
493 lv_cur_geo_patch varchar2(240);
494 ln_patch_number number;
495 ln_patch_id number;
496 lc_error varchar2(10);
497 lc_status varchar2(1);
498 --
499 ln_upgrade_patch pay_patch_status.patch_name%TYPE;
500 ln_upgrade_patch_id pay_patch_status.id%TYPE;
501 leg_param pay_payroll_actions.legislative_parameters%type;
502 l_year varchar2(4);
503 l_mode pay_payroll_actions.legislative_parameters%type;
504 l_geo_phase_id number;
505
506 l_patch_name pay_patch_status.patch_name%TYPE;
507
508 l_req_id number;
509 copies_buffer varchar2(80) := null;
510 print_buffer varchar2(80) := null;
511 printer_buffer varchar2(80) := null;
512 style_buffer varchar2(80) := null;
513 save_buffer boolean := null;
514 save_result varchar2(1) := null;
515 req_id varchar2(80) := null;
516 x boolean;
517 x1 boolean;
518
519 l_valid_status varchar2(5);
520 l_program varchar2(100);
521 retcode number;
522 errbuf varchar2(80);
523
524 --
525 --
526
527 begin
528
529 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
530 retcode := 0;
531
532 open c_parameters(p_payroll_action_id);
533
534 fetch c_parameters into leg_param,
535 l_patch_name,
536 l_mode;
537 close c_parameters;
538
539 /***** submit the geocode reports ****/
540
541 pay_us_geocode_report_pkg.extract_data( errbuf
542 ,retcode
543 ,p_process_mode => l_mode
544 ,p_geocode_patch_name => l_patch_name );
545
546 /* Wrap up the geocode process */
547
548 lc_status := 'C';
549
550 open c_get_params;
551 fetch c_get_params into lv_cur_geo_patch, ln_patch_number;
552 close c_get_params;
553
554 --hr_utility.trace(' lv_cur_geo_patch = ' || lv_cur_geo_patch);
555 --hr_utility.trace('archive deinit ln_patch_number = ' || ln_patch_number);
556
557 --
558 open c_geo_check(lv_cur_geo_patch, ln_patch_number);
559 fetch c_geo_check into ln_patch_id;
560 if c_geo_check%found then
561
562
563 open c_geo_upd(ln_patch_id, 'P');
564 fetch c_geo_upd into lc_error;
565 if c_geo_upd%found then
566
567 update pay_patch_status
568 set status = 'E'
569 where id = ln_patch_id;
570
571 else
572
573 update pay_patch_status
574 set status = 'C',
575 phase = null,
576 process_type = null,
577 description = null
578 where id = ln_patch_id;
579
580 end if;
581 close c_geo_upd;
582
583 end if;
584 close c_geo_check;
585
586 EXCEPTION
587 --
588 WHEN hr_utility.hr_error THEN
589 --
590 -- Set up error message and error return code.
591 --
592
593 hr_utility.trace('in the exception 1');
594
595 errbuf := hr_utility.get_message;
596 retcode := 2;
597 --
598 --
599 WHEN others THEN
600 --
601 -- Set up error message and return code.
602 --
603
604 hr_utility.trace('in the exception 2 sqlerrm = ' || sqlerrm);
605
606 errbuf := sqlerrm;
607 retcode := 2;
608 end archive_deinit;
609
610
611
612 PROCEDURE write_message(
613 p_proc_type IN VARCHAR2,
614 p_person_id IN NUMBER,
615 p_assign_id IN NUMBER,
616 p_old_juri_code IN VARCHAR2,
617 p_new_juri_code IN VARCHAR2,
618 p_location IN VARCHAR2,
619 p_id IN NUMBER,
620 p_status IN VARCHAR2 DEFAULT NULL)
621
622 IS
623
624 BEGIN
625
626 hr_utility.trace('Entering pay_us_geo_upd_pkg.write message');
627
628 IF G_MODE = 'UPGRADE' THEN
629 insert into PAY_US_GEO_UPDATE (ID,
630 ASSIGNMENT_ID,
631 PERSON_ID,
632 TABLE_NAME,
633 TABLE_VALUE_ID,
634 OLD_JURI_CODE,
635 NEW_JURI_CODE,
636 PROCESS_TYPE,
637 PROCESS_DATE,
638 PROCESS_MODE,
639 STATUS)
640 VALUES(g_geo_phase_id,
641 p_assign_id,
642 p_person_id,
643 p_location,
644 p_id,
645 p_old_juri_code,
646 p_new_juri_code,
647 p_proc_type,
648 sysdate,
649 'UPGRADE',
650 p_status);
651
652 ELSE
653 insert into PAY_US_GEO_UPDATE (ID,
654 ASSIGNMENT_ID,
655 PERSON_ID,
656 TABLE_NAME,
657 TABLE_VALUE_ID,
658 OLD_JURI_CODE,
659 NEW_JURI_CODE,
660 PROCESS_TYPE,
661 PROCESS_DATE,
662 PROCESS_MODE,
663 STATUS)
664 VALUES(g_geo_phase_id,
665 p_assign_id,
666 p_person_id,
667 p_location,
668 p_id,
669 p_old_juri_code,
670 p_new_juri_code,
671 p_proc_type,
672 sysdate,
673 g_mode,
674 p_status);
675
676
677 END IF;
678 hr_utility.trace('Exiting pay_us_geo_upd_pkg.write message');
679
680 END write_message;
681
682
683 -- We can call upgrade_geocodes in a DEBUG mode also.
684 -- DEBUG mode will not do any updates in the tables. It will
685 -- Create the city tax records and vertex element entries though.
686 -- But it only creates them if they are missing in the first place.
687 -- We are defaulting to NULL, in our update statements we check for DEBUG
688
689 PROCEDURE upgrade_geocodes(P_ASSIGN_START NUMBER,
690 P_ASSIGN_END NUMBER,
691 P_GEO_PHASE_ID NUMBER,
692 P_MODE VARCHAR2,
693 P_PATCH_NAME VARCHAR2,
694 P_CITY_NAME VARCHAR2 DEFAULT NULL,
695 P_API_MODE VARCHAR2 DEFAULT 'N')
696
697 IS
698 --Retrieve all changed geocodes on per_assignment_extra_info table. This will
699 --be our main 'driving' table
700 /* CURSOR paei_cur IS
701 SELECT distinct paei.aei_information2, paei.aei_information13,
702 paei.assignment_id,
703 pmod.state_code||'-'||pmod.county_code||'-'
704 ||pmod.new_city_code jd_code,
705 paf.person_id
706 FROM per_assignments_f paf,
707 pay_us_modified_geocodes pmod,
708 per_assignment_extra_info paei
709 WHERE paei.information_type = 'LOCALITY'
710 AND substr(paei.aei_information2,8,4) <> '0000'
711 AND pmod.city_name = paei.aei_information13
712 AND pmod.state_code = substr(paei.aei_information2,1,2)
713 AND pmod.county_code = substr(paei.aei_information2,4,3)
714 AND pmod.old_city_code = substr(paei.aei_information2,8,4)
715 AND pmod.process_type in ('UP','US','PU','D','SU','RP','RS')
716 AND paf.assignment_id = paei.assignment_id;
717
718 paei_rec paei_cur%ROWTYPE; */
719
720 --Retrieve all changed geocodes on pay_us_emp_city_tax_rules_f table.
721 --This will be our main 'driving' table.
722 --Added the ASSIGN START and ASSIGN END so that we can multi-thread the
723 --driving cursor
724 --
725 --
726 --Per bug 2996546 added another select statement with UNION ALL
727 --to the CURSOR main_driving_cur in order to process Canadian
728 --Legislation data
729 --
730 --
731
732 CURSOR main_driving_cur(P_ASSIGN_START NUMBER, P_ASSIGN_END NUMBER, P_CITY_NAME VARCHAR2, P_API_MODE VARCHAR2) IS
733 SELECT distinct ectr.jurisdiction_code, ectr.assignment_id,
734 pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code jd_code,
735 paf.person_id, pmod.new_city_code, pmod.process_type, ectr.emp_city_tax_rule_id
736 FROM per_all_assignments_f paf,
737 pay_us_emp_city_tax_rules_f ectr,
738 pay_us_modified_geocodes pmod
739 WHERE pmod.state_code = ectr.state_code
740 AND pmod.county_code = ectr.county_code
741 AND pmod.new_county_code is null
742 AND pmod.old_city_code = ectr.city_code
743 AND pmod.process_type in ('UP','US','PU','D','SU')
744 AND pmod.patch_name = p_patch_name
745 AND ectr.assignment_id = paf.assignment_id
746 AND pmod.city_name = nvl(p_city_name, pmod.city_name)
747 AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END
748 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
749 where pugu.assignment_id = ectr.assignment_id
750 and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
751 and pugu.old_juri_code = ectr.jurisdiction_code
752 and pugu.table_value_id is null
753 and pugu.table_name is null
754 and pugu.process_type = pmod.process_type
755 and pugu.process_mode = g_mode
756 and pugu.id = g_geo_phase_id
757 and ((p_api_mode = 'Y' and pugu.status = 'C') or
758 (p_api_mode = 'N' and pugu.status in ('A','C'))))
759 UNION ALL
760 SELECT distinct pac.context_value, pac.assignment_id,
761 pmod.new_county_code jd_code,
762 paf.person_id, pmod.new_city_code, pmod.process_type,
763 pac.context_id
764 FROM per_all_assignments_f paf,
765 pay_action_contexts pac,
766 pay_us_modified_geocodes pmod
767 WHERE pmod.state_code = 'CA'
768 AND pmod.county_code = pac.context_value
769 AND pac.context_id in (select context_id
770 from ff_contexts
771 where context_name = 'JURISDICTION_CODE')
772 AND pmod.patch_name = p_patch_name
773 AND pac.assignment_id = paf.assignment_id
774 AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
775
776
777 main_old_juri_code varchar2(11);
778 main_assign_id number;
779 main_new_juri_code varchar2(11);
780 main_person_id number;
781 main_new_city_code varchar2(4);
782 main_proc_type varchar2(3);
783 main_city_tax_rule_id number;
784 lv_update_prr varchar2(1);
785
786 -- main_ ectr_cur%ROWTYPE;
787
788 --Retrieve all affected rows in PAY_US_EMP_CITY_TAX_RULES_F
789 --This is decoupled from above because we still want the level of
790 --of granularity for city tax records that are changed.
791 --We already have this information we just need to verify if it has
792 --been processed already.
793 --Since we have this cursor we do not need this in the city_tax_records
794 --procedure. We could have put it there but there is no need.
795
796 cursor city_rec_cur (p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2,
797 p_assign_id NUMBER, p_city_tax_record_id NUMBER)
798 IS
799 SELECT distinct 'Y'
800 FROM pay_us_emp_city_tax_rules_f puecf
801 WHERE puecf.jurisdiction_code = p_old_juri_code
802 AND puecf.assignment_id = p_assign_id
803 AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
804 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
805 where pugu.assignment_id = p_assign_id
806 and pugu.table_value_id = puecf.emp_city_tax_rule_id
807 and pugu.old_juri_code = p_old_juri_code
808 and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
809 and pugu.process_mode = g_mode
810 and pugu.process_type = g_process_type
811 and pugu.id = g_geo_phase_id);
812
813 l_city_tax_exists varchar2(2);
814
815 --Retrieve all affected rows in the pay_element_entry_values_f table.
816 --Since we can join on the main part of the pk of the table we do not
817 --need the logic in the element_entries procedure.
818
819 --
820 --Per bug 2996546 changed the where clause for
821 --piv.legislation_code = 'US' to use the function
822 --IS_US_OR_CA_LEGISLATION and compare input value id
823 --stored in pl/sql table to improve performance
824 --
825 --
826
827
828 CURSOR pev_cur(geocode VARCHAR2, assign_id NUMBER) IS
829 SELECT /*+ ORDERED */ distinct pev.screen_entry_value, pev.element_entry_id,
830 pev.input_value_id
831 FROM pay_element_entries_f pee,
832 pay_element_entry_values_f pev,
833 pay_input_values_f piv
834 WHERE pee.assignment_id = assign_id
835 AND pee.element_entry_id = pev.element_entry_id
836 AND pev.screen_entry_value = geocode
837 AND pev.input_value_id = piv.input_value_id
838 AND piv.name = 'Jurisdiction'
839 -- AND piv.legislation_code = 'US'
840 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id
841 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
842 where pugu.assignment_id = assign_id
843 and pugu.table_value_id = pev.element_entry_id
844 and pugu.old_juri_code = geocode
845 and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
846 and pugu.process_mode = g_mode
847 and pugu.process_type = g_process_type
848 and pugu.id = g_geo_phase_id);
849
850 pev_rec pev_cur%ROWTYPE;
851
852 --Retrieve all affected rows in the pay_run_results table.
853 --The run_result_id's from this cursor will then be
854 --used to dertermine the rows to update in the pay_run_result_values
855 --table note since run_result_id is driving for the value table we have to pick
856 --up all regardless if the geocode has changed because they may have run result values
857 --that are tagged to a different jurisdiction.
858 --Right now if the patch is reran this cursor will still pick up assignments that have
859 --already been processed but for run result ids that do not have a modified geocode, but
860 --when it goes through the procedure it WILL NOT update wrong geocodes because the
861 --jurisdictions will not match. So this can be changed in the future to add the logic
862 --here versus in the procedure: run_results.
863
864
865 -- Bug 3319878 -- Breaked the query into two cursors i.e paa_cur and prr_cur.
866 CURSOR paa_cur(assign_id NUMBER) IS
867 SELECT assignment_action_id
868 FROM pay_assignment_actions
869 WHERE assignment_id = assign_id;
870
871 CURSOR prr_cur(assign_action_id NUMBER,assign_id NUMBER) IS
872 SELECT distinct prr.run_result_id,
873 prr.assignment_action_id, prr.jurisdiction_code
874 FROM pay_run_results prr
875 WHERE prr.assignment_action_id = assign_action_id
876 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
877 where pugu.assignment_id = assign_id
878 and pugu.table_value_id = prr.run_result_id
879 and pugu.old_juri_code = prr.jurisdiction_code
880 and pugu.table_name = 'PAY_RUN_RESULTS'
881 and pugu.process_mode = g_mode
882 and pugu.process_type = g_process_type
883 and pugu.id = g_geo_phase_id);
884
885 paa_rec NUMBER;
886
887 prr_rec prr_cur%ROWTYPE;
888
889 --Per bug 2996546
890 --Retrieves all affected rows in the table pay_action_contexts
891 --
892 --
893 CURSOR pac_cur(assign_id NUMBER, context_id NUMBER) IS
894 SELECT pac.context_id,
895 pac.assignment_action_id
896 FROM pay_action_contexts pac,
897 pay_assignment_actions paa
898 WHERE paa.assignment_id = assign_id
899 AND pac.assignment_id = paa.assignment_id -- Bug# 3679984 added this to where clause
900 AND paa.assignment_action_id = pac.assignment_action_id
901 AND pac.context_id = context_id ;
902
903 pac_rec pac_cur%ROWTYPE;
904
905
906
907 --Retrieve all affected rows in the ff_archive_item_contexts table.
908 --This cursor will check for a specific geocode that is passed in.
909 --The passed in geocode will be the old one from pay_us_modified_geocodes.
910 --We are joining with the archive item id, so we don't need this logic
911 --in the procedure archive_items.
912
913 /* CURSOR fac_cur(assign_id NUMBER, geocode VARCHAR2) IS
914 SELECT distinct paa.assignment_action_id,
915 faic.context old_juri_code, faic.archive_item_id, ffc.context_id
916 FROM ff_archive_items fai,
917 ff_archive_item_contexts faic,
918 pay_assignment_actions paa,
919 pay_payroll_actions ppa,
920 ff_contexts ffc
921 WHERE ppa.report_type = 'YREND'
922 AND ppa.report_category = 'RT'
923 AND ppa.report_qualifier = 'FED'
924 AND ppa.payroll_action_id = paa.payroll_action_id
925 AND paa.assignment_id = assign_id
926 AND fai.context1 = paa.assignment_action_id
927 AND fai.archive_item_id = faic.archive_item_id
928 AND faic.context = geocode
929 AND ffc.context_id = faic.context_id
930 AND ffc.context_name = 'JURISDICTION_CODE'
931 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
932 where pugu.assignment_id = assign_id
933 and pugu.table_value_id = faic.archive_item_id
934 and pugu.old_juri_code = faic.context
935 and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
936 and pugu.process_mode = g_mode
937 and pugu.process_type = g_process_type
938 and pugu.id = g_geo_phase_id);
939 */
940 --Bug 3126437 hrglobal performance fix
941 --
942 CURSOR fac_cur(assign_id NUMBER, geocode VARCHAR2) IS
943 SELECT distinct paa.assignment_action_id,
944 faic.context old_juri_code,
945 faic.archive_item_id,
946 ffc.context_id
947 FROM ff_archive_items fai,
948 ff_archive_item_contexts faic,
949 pay_assignment_actions paa,
950 pay_payroll_actions ppa,
951 ff_contexts ffc
952 WHERE ppa.report_type in ('T4', 'T4A', 'RL1', 'RL2', 'YREND')
953 and ppa.report_category in ('RT', 'CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
954 and report_qualifier in ('FED','CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
955 and ppa.payroll_action_id = paa.payroll_action_id
956 and paa.assignment_id = assign_id
957 and fai.context1 = paa.assignment_action_id
958 and fai.archive_item_id = faic.archive_item_id
959 and faic.context = geocode
960 and ffc.context_id = faic.context_id
961 and ffc.context_name = 'JURISDICTION_CODE'
962 and not exists (select 'Y' from PAY_US_GEO_UPDATE pugu
963 where pugu.assignment_id = assign_id
964 and pugu.table_value_id = faic.archive_item_id
965 and pugu.old_juri_code = faic.context
966 and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
967 and pugu.process_mode = g_mode
968 and pugu.process_type = g_process_type
969 and pugu.id = g_geo_phase_id);
970 fac_rec fac_cur%ROWTYPE;
971
972 --Retrieve affected rows in the pay_balance_context_values table
973 --using the latest_balance_id's from the pay_person_latest_balances
974 --table.
975 --Since we can join by the pk of the table we do not need any more logic
976 --in the balance_contexts procedure.
977
978 CURSOR pbcv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
979 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
980 plb.assignment_action_id
981 FROM pay_assignment_actions paa,
982 pay_balance_context_values pbcv,
983 pay_person_latest_balances plb,
984 ff_contexts fcon
985 WHERE paa.assignment_id = assign_id
986 AND paa.assignment_action_id = plb.assignment_action_id
987 AND plb.person_id = personid
988 AND pbcv.latest_balance_id = plb.latest_balance_id
989 AND pbcv.value = geocode
990 AND fcon.context_id = pbcv.context_id
991 AND fcon.context_name = 'JURISDICTION_CODE'
992 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
993 where pugu.assignment_id = assign_id
994 and pugu.table_value_id = plb.latest_balance_id
995 and pugu.old_juri_code = geocode
996 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
997 and pugu.process_mode = g_mode
998 and pugu.process_type = g_process_type
999 and pugu.id = g_geo_phase_id);
1000
1001 pbcv_rec pbcv_cur%ROWTYPE;
1002
1003 --Retrieve affected rows in the pay_balance_context_values table
1004 --using the latest_balance_id's from the pay_assignment_latest_balances
1005 --table.
1006 CURSOR pacv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
1007 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
1008 plb.assignment_action_id
1009 FROM ff_contexts fcon,
1010 pay_balance_context_values pbcv,
1011 pay_assignment_latest_balances plb
1012 WHERE plb.assignment_id = assign_id
1013 AND pbcv.latest_balance_id = plb.latest_balance_id
1014 AND pbcv.value = geocode
1015 AND fcon.context_id = pbcv.context_id
1016 AND fcon.context_name = 'JURISDICTION_CODE'
1017 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1018 where pugu.assignment_id = assign_id
1019 and pugu.table_value_id = plb.latest_balance_id
1020 and pugu.old_juri_code = geocode
1021 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1022 and pugu.process_mode = g_mode
1023 and pugu.process_type = g_process_type
1024 and pugu.id = g_geo_phase_id);
1025
1026
1027 pacv_rec pacv_cur%ROWTYPE;
1028
1029 -- Rosie Monge 10/17/2005 Bug 4602222
1030 --Retrieve affected rows in the pay_balance_context_values table
1031 --using the latest_balance_id's from the pay_latest_balances
1032 --table.
1033
1034 CURSOR plbcv_cur(geocode VARCHAR2, assign_id NUMBER, personid NUMBER) IS
1035 SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
1036 plb.assignment_action_id
1037 FROM ff_contexts fcon,
1038 pay_balance_context_values pbcv,
1039 pay_latest_balances plb
1040 WHERE plb.assignment_id = assign_id
1041 AND pbcv.latest_balance_id = plb.latest_balance_id
1042 AND pbcv.value = geocode
1043 AND fcon.context_id = pbcv.context_id
1044 AND fcon.context_name = 'JURISDICTION_CODE'
1045 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1046 where pugu.assignment_id = assign_id
1047 and pugu.table_value_id = plb.latest_balance_id
1048 and pugu.old_juri_code = geocode
1049 and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
1050 and pugu.process_mode = g_mode
1051 and pugu.process_type = g_process_type
1052 and pugu.id = g_geo_phase_id);
1053
1054
1055 plbcv_rec pacv_cur%ROWTYPE;
1056
1057
1058 -- This cursor will check if a particular assignment is errored.
1059 CURSOR chk_assign_error_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1060 IS
1061 SELECT 'Y' from PAY_US_GEO_UPDATE pugu
1062 WHERE pugu.assignment_id = p_assign_id
1063 AND pugu.process_mode = g_mode
1064 AND pugu.id = g_geo_phase_id
1065 AND pugu.table_name is null
1066 AND pugu.status = 'P'
1067 AND pugu.new_juri_code = p_new_juri_code
1068 AND pugu.old_juri_code = p_old_juri_code;
1069
1070 l_chk_assign_error varchar2(4);
1071 l_error_text varchar2(1000);
1072
1073 -- This cursor will check if a particular assignment needs to be upgraded via the api.
1074 -- If it does then we will update the status to 'A' in the main procedure.
1075
1076 CURSOR chk_assign_api_cur(p_assign_id NUMBER, p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2)
1077 IS select distinct 'Y'
1078 from pay_us_geo_update pugu
1079 where pugu.process_type in ('SU','US')
1080 and pugu.table_name is null
1081 and pugu.process_mode = g_mode
1082 and pugu.id = g_geo_phase_id
1083 and pugu.assignment_id = p_assign_id
1084 and pugu.table_name is null
1085 and pugu.table_value_id is null
1086 and pugu.old_juri_code = p_old_juri_code
1087 and pugu.new_juri_code = p_new_juri_code
1088 and NOT EXISTS (select 'Y' from pay_us_modified_geocodes pmod
1089 where pmod.state_code = substr(pugu.new_juri_code,1,2)
1090 and pmod.county_code = substr(pugu.new_juri_code,4,3)
1091 and pmod.old_city_code = substr(pugu.old_juri_code,8)
1092 and pmod.new_city_code = substr(pugu.new_juri_code,8)
1093 and pmod.process_type not in ('SU','US')
1094 and pmod.patch_name = p_patch_name);
1095
1096 l_chk_assign_api varchar2(4);
1097
1098
1099 sql_cursor INTEGER;
1100 ret INTEGER;
1101 table_exist NUMBER(1) := 0;
1102 tab_name VARCHAR2(30) := 'PAY_US_ASG_REPORTING';
1103 l_text VARCHAR2(2000);
1104 l_proc_stage VARCHAR2(240);
1105
1106 --
1107 --
1108 --
1109 --Bug 2996546 PROCEDURE load_input_values loads input_value_id
1110 --from pay_input_values_f into a pl/sql table input_val_cur
1111 -- for both seeded and non-seeded in US and Canada Legislations
1112 --
1113 --
1114 --
1115 PROCEDURE load_input_values IS
1116 Begin
1117
1118 for piv_rec in (
1119 select piv.input_value_id
1120 from pay_input_values_f piv
1121 where piv.name in ('Jurisdiction', 'jd_rs', 'jd_wk')
1122 and ( (piv.legislation_code in( 'US', 'CA')
1123 )
1124 OR (piv.legislation_code is null
1125 and piv.business_group_id is not null
1126 and exists (select 'Y'
1127 from hr_organization_information hoi
1128 where hoi.organization_id = piv.business_group_id
1129 and hoi.org_information_context = 'Business Group Information'
1130 and hoi.org_information9 in ('US','CA')
1131 )
1132 )
1133 )
1134 )
1135 Loop
1136 l_counter := l_counter+1;
1137 input_val_cur(l_counter):= piv_rec.input_value_id;
1138
1139 end Loop;
1140 l_total := l_counter;
1141
1142
1143 END load_input_values;
1144
1145 -- This procedure will update pay balance batch lines for a particular assignment id
1146 -- Note the cursor use here. Where we have the cursor in before both write messages
1147 -- this is because if we want to run in debug mode and the type is not SU and US the
1148 -- sql rowcount wont work because we are bypassing the update.
1149
1150 PROCEDURE balance_batch_lines(p_proc_type IN VARCHAR2,
1151 p_person_id IN NUMBER,
1152 p_assign_id IN NUMBER,
1153 p_old_juri_code IN VARCHAR2,
1154 p_new_juri_code IN VARCHAR2)
1155
1156 IS
1157
1158 CURSOR bal_batch_cur(p_new_juri_code varchar2, p_old_juri_code varchar2, p_assign_id number)
1159 IS select 'Y'
1160 FROM pay_balance_batch_lines
1161 WHERE jurisdiction_code = p_old_juri_code
1162 AND assignment_id = p_assign_id
1163 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1164 where pugu.assignment_id = p_assign_id
1165 and pugu.old_juri_code = p_old_juri_code
1166 and pugu.new_juri_code = p_new_juri_code
1167 and pugu.table_name = 'PAY_BALANCE_BATCH_LINES'
1168 and pugu.process_mode = g_mode
1169 and pugu.process_type = g_process_type
1170 and pugu.id = g_geo_phase_id);
1171
1172 l_bal_batch_exist varchar2(2);
1173
1174
1175 BEGIN
1176
1177 hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_batch_lines');
1178
1179 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1180
1181 hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
1182
1183 IF G_MODE = 'UPGRADE' THEN
1184
1185 UPDATE pay_balance_batch_lines
1186 SET jurisdiction_code = p_new_juri_code
1187 WHERE jurisdiction_code = p_old_juri_code
1188 AND assignment_id = p_assign_id;
1189
1190 END IF;
1191
1192 -- Write a message to the table to be later spooled to a report
1193
1194 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1195
1196 OPEN bal_batch_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1197 FETCH bal_batch_cur into l_bal_batch_exist;
1198 IF bal_batch_cur%FOUND THEN
1199
1200 write_message(
1201 p_proc_type => p_proc_type,
1202 p_person_id => p_person_id,
1203 p_assign_id => p_assign_id,
1204 p_old_juri_code => p_old_juri_code,
1205 p_new_juri_code => p_new_juri_code,
1206 p_location => 'PAY_BALANCE_BATCH_LINES',
1207 p_id => p_assign_id);
1208
1209 END IF;
1210 CLOSE bal_batch_cur;
1211
1212 /*END IF;*/
1213
1214 ELSE
1215
1216 -- Write a message to the table to be later spooled to a report
1217
1218 OPEN bal_batch_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1219 FETCH bal_batch_cur into l_bal_batch_exist;
1220 IF bal_batch_cur%FOUND THEN
1221
1222 write_message(
1223 p_proc_type => p_proc_type,
1224 p_person_id => p_person_id,
1225 p_assign_id => p_assign_id,
1226 p_old_juri_code => p_old_juri_code,
1227 p_new_juri_code => p_new_juri_code,
1228 p_location => 'PAY_BALANCE_BATCH_LINES',
1229 p_id => p_assign_id);
1230
1231
1232 END IF;
1233 CLOSE bal_batch_cur;
1234
1235 END IF;
1236
1237 END balance_batch_lines;
1238 ---
1239 ---
1240 ---
1241 --Per bug 2738574
1242 -- This procedure will update pay_run_balances for a particular assignment id
1243
1244 PROCEDURE pay_run_balances (p_proc_type IN VARCHAR2,
1245 p_person_id IN NUMBER,
1246 p_assign_id IN NUMBER,
1247 p_new_city_code IN VARCHAR2,
1248 p_old_juri_code IN VARCHAR2,
1249 p_new_juri_code IN VARCHAR2)
1250
1251 IS
1252
1253 CURSOR run_balance_cur(p_new_juri_code varchar2, p_old_juri_code varchar2, p_assign_id number)
1254 IS select 'Y'
1255 FROM pay_run_balances
1256 WHERE jurisdiction_code = p_old_juri_code
1257 AND assignment_id = p_assign_id
1258 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1259 where pugu.assignment_id = p_assign_id
1260 and pugu.old_juri_code = p_old_juri_code
1261 and pugu.new_juri_code = p_new_juri_code
1262 and pugu.table_name = 'PAY_RUN_BALANCES'
1263 and pugu.process_mode = g_mode
1264 and pugu.process_type = g_process_type
1265 and pugu.id = g_geo_phase_id);
1266
1267 l_run_balance_exist varchar2(2);
1268
1269
1270 BEGIN
1271
1272 hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_run_balances');
1273
1274 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1275
1276 hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
1277
1278 IF G_MODE = 'UPGRADE' THEN
1279
1280 UPDATE pay_run_balances
1281 SET jurisdiction_code = p_new_juri_code,
1282 jurisdiction_comp3 = p_new_city_code
1283 WHERE assignment_id = p_assign_id
1284 AND jurisdiction_code = p_old_juri_code ;
1285
1286 END IF;
1287
1288 -- Write a message to the table to be later spooled to a report
1289
1290 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1291
1292 write_message(
1293 p_proc_type => p_proc_type,
1294 p_person_id => p_person_id,
1295 p_assign_id => p_assign_id,
1296 p_old_juri_code => p_old_juri_code,
1297 p_new_juri_code => p_new_juri_code,
1298 p_location => 'PAY_RUN_BALANCES',
1299 p_id => p_assign_id);
1300
1301
1302 /*END IF;*/
1303
1304 ELSE
1305
1306 -- Write a message to the table to be later spooled to a report
1307
1308 OPEN run_balance_cur(p_new_juri_code, p_old_juri_code, p_assign_id);
1309 FETCH run_balance_cur into l_run_balance_exist;
1310 IF run_balance_cur%FOUND THEN
1311
1312 write_message(
1313 p_proc_type => p_proc_type,
1314 p_person_id => p_person_id,
1315 p_assign_id => p_assign_id,
1316 p_old_juri_code => p_old_juri_code,
1317 p_new_juri_code => p_new_juri_code,
1318 p_location => 'PAY_RUN_BALANCES',
1319 p_id => p_assign_id);
1320
1321
1322 END IF;
1323 CLOSE run_balance_cur;
1324
1325 END IF;
1326
1327 END pay_run_balances ;
1328
1329 ---
1330 ---
1331 ---
1332
1333
1334 -- This procedure will update the city tax records for a particular assignment id
1335 PROCEDURE city_tax_records (p_proc_type IN VARCHAR2,
1336 p_person_id IN NUMBER,
1337 p_assign_id IN NUMBER,
1338 p_old_juri_code IN VARCHAR2,
1339 p_new_juri_code IN VARCHAR2,
1340 p_new_city_code IN VARCHAR2,
1341 p_city_tax_record_id IN NUMBER)
1342
1343
1344 IS
1345
1346 BEGIN
1347
1348 hr_utility.trace('Entering pay_us_geo_upd_pkg.city_tax_records');
1349
1350 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1351
1352 hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
1353
1354 IF G_MODE = 'UPGRADE' THEN
1355
1356 UPDATE pay_us_emp_city_tax_rules_f
1357 SET jurisdiction_code = p_new_juri_code,
1358 city_code = p_new_city_code
1359 WHERE jurisdiction_code = p_old_juri_code
1360 AND assignment_id = p_assign_id
1361 AND emp_city_tax_rule_id = p_city_tax_record_id
1362 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1363 where pugu.assignment_id = p_assign_id
1364 and pugu.table_value_id = p_city_tax_record_id
1365 and pugu.old_juri_code = p_old_juri_code
1366 and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
1367 and pugu.process_mode = g_mode
1368 and pugu.process_type = g_process_type
1369 and pugu.id = g_geo_phase_id);
1370
1371
1372 END IF;
1373 -- Write a message to the table to be later spooled to a report
1374
1375 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1376
1377 write_message(
1378 p_proc_type => p_proc_type,
1379 p_person_id => p_person_id,
1380 p_assign_id => p_assign_id,
1381 p_old_juri_code => p_old_juri_code,
1382 p_new_juri_code => p_new_juri_code,
1383 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1384 p_id => p_city_tax_record_id);
1385
1386 /*END IF;*/
1387 ELSE
1388
1389 -- Write a message to the table to be later spooled to a report
1390
1391
1392 write_message(
1393 p_proc_type => p_proc_type,
1394 p_person_id => p_person_id,
1395 p_assign_id => p_assign_id,
1396 p_old_juri_code => p_old_juri_code,
1397 p_new_juri_code => p_new_juri_code,
1398 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
1399 p_id => p_city_tax_record_id);
1400
1401 END IF;
1402
1403 END city_tax_records;
1404
1405 PROCEDURE del_dup_city_tax_recs IS
1406
1407 -- This cursor identifies assignment id/jurisdiction pairs that have multiple
1408 -- rows in the pay_us_emp_city_tax_rules_f table created by geocode updates.
1409 -- For example, prior to geocode patch 1105095, these geocodes were in place:
1410 -- Van Nuys, CA: 05-037-3880
1411 -- Woodland Hills, CA: 05-037-6080
1412 -- Los Angeles, CA: 05-037-1900
1413 -- Patch 1105095 updated the first two geocodes to 05-037-1900, so a person that
1414 -- previously had distinct city tax records for any two or all three of the above
1415 -- geocodes would now have multiple tax records for geocode 05-037-1900, each
1416 -- with a different city_tax_rule_id.
1417
1418
1419 -- Bug 3319878 -- Changed the query to reduce the cost of the query
1420 CURSOR dup_city_tax_rows is
1421 select distinct pect1.assignment_id, pect1.jurisdiction_code
1422 from pay_us_emp_city_tax_rules_f pect1,
1423 pay_us_emp_city_tax_rules_f pect2
1424 where pect1.assignment_id = pect2.assignment_id
1425 and pect1.jurisdiction_code = pect2.jurisdiction_code
1426 and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id
1427 and pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
1428
1429 /* select distinct pect1.assignment_id, pect1.jurisdiction_code
1430 from pay_us_emp_city_tax_rules_f pect1
1431 where pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END
1432 and pect1.emp_city_tax_rule_id <
1433 (select pect2.emp_city_tax_rule_id
1434 from pay_us_emp_city_tax_rules_f pect2
1435 where pect1.assignment_id = pect2.assignment_id
1436 and pect1.jurisdiction_code = pect2.jurisdiction_code
1437 );
1438 */
1439
1440 BEGIN
1441
1442 hr_utility.trace('Entering pay_us_geo_upd_pkg.del_dup_city_tax_recs');
1443
1444 IF G_MODE = 'UPGRADE' THEN
1445
1446 FOR dup_rec IN dup_city_tax_rows
1447
1448 LOOP
1449
1450 hr_utility.trace('Deleting dups for Assign ID: ' || to_char(dup_rec.assignment_id) ||
1451 ' Geocode: ' || dup_rec.jurisdiction_code);
1452
1453 DELETE FROM pay_us_emp_city_tax_rules_f pecto
1454 WHERE pecto.rowid < (SELECT max(pecti.rowid)
1455 FROM pay_us_emp_city_tax_rules_f pecti
1456 WHERE pecti.assignment_id = pecto.assignment_id
1457 AND pecti.assignment_id = dup_rec.assignment_id
1458 AND pecti.jurisdiction_code = pecto.jurisdiction_code
1459 AND pecti.jurisdiction_code = dup_rec.jurisdiction_code
1460 AND pecti.emp_city_tax_rule_id <> pecto.emp_city_tax_rule_id)
1461 AND pecto.assignment_id = dup_rec.assignment_id;
1462
1463 END LOOP;
1464
1465 END IF;
1466
1467 END del_dup_city_tax_recs;
1468
1469 -- This procedure will update the run results for a particular assignment action id and
1470 -- a particulare run result id.
1471 -- NOTE where we have the cursor in before both write messages
1472 -- this is because if we want to run in debug mode and the type is not SU and US the
1473 -- sql rowcount wont work because we are bypassing the update.
1474
1475 PROCEDURE run_results(p_proc_type IN VARCHAR2,
1476 p_person_id IN NUMBER,
1477 p_assign_id IN NUMBER,
1478 p_assign_act_id IN NUMBER,
1479 p_run_result_id IN NUMBER,
1480 p_old_juri_code IN VARCHAR2,
1481 p_new_juri_code IN VARCHAR2)
1482
1483
1484 IS
1485
1486 --
1487 --Per bug 2996546 changed the where clause in
1488 --cursor ele_run_result_val for piv.legislation_code = 'US'
1489 --to use the function IS_US_OR_CA_LEGISLATION and compare
1490 --input value id stored in pl/sql table to improve performance
1491 --
1492
1493 cursor ele_run_result_val(p_new_juri_code VARCHAR2, p_run_result_id VARCHAR2, p_assign_act_id NUMBER,p_assign_id NUMBER)
1494 IS select distinct 'Y'
1495 FROM pay_run_result_values prv
1496 WHERE prv.result_value = p_new_juri_code
1497 AND prv.run_result_id = p_run_result_id
1498 AND EXISTS (SELECT 0
1499 FROM pay_input_values_f piv
1500 WHERE piv.input_value_id = prv.input_value_id
1501 AND (piv.name = 'Jurisdiction' OR
1502 piv.name = 'jd_rs' OR
1503 piv.name = 'jd_wk')
1504 -- AND piv.legislation_code = 'US')
1505 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id )
1506 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1507 where pugu.assignment_id = p_assign_id
1508 and pugu.table_value_id = prv.run_result_id
1509 and pugu.old_juri_code = p_old_juri_code
1510 and pugu.table_name = 'PAY_RUN_RESULT_VALUES'
1511 and pugu.process_mode = g_mode
1512 and pugu.process_type = g_process_type
1513 and pugu.id = g_geo_phase_id);
1514
1515
1516 cursor ele_run_results(p_new_juri_code VARCHAR2, p_old_juri_code VARCHAR2, p_run_result_id VARCHAR2,
1517 p_assign_act_id NUMBER)
1518 IS select distinct 'Y'
1519 FROM pay_run_results prr
1520 WHERE prr.jurisdiction_code = p_new_juri_code
1521 AND prr.run_result_id = p_run_result_id
1522 AND prr.assignment_action_id = p_assign_act_id
1523 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
1524 where pugu.assignment_id = p_assign_id
1525 and pugu.table_value_id = prr.run_result_id
1526 and pugu.old_juri_code = p_old_juri_code
1527 and pugu.table_name = 'PAY_RUN_RESULTS'
1528 and pugu.process_mode = g_mode
1529 and pugu.process_type = g_process_type
1530 and pugu.id = g_geo_phase_id);
1531
1532
1533 l_flag varchar2(2);
1534
1535 BEGIN
1536
1537 hr_utility.trace('Entering pay_us_geo_upd_pkg.run_results');
1538
1539 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1540
1541 hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1542
1543 --
1544 --Per bug 2996546 changed the where clause in
1545 --in the update of pay_run_result_values
1546 --for piv.legislation_code = 'US'
1547 --to use the function IS_US_OR_CA_LEGISLATION and compare
1548 --input value id stored in pl/sql table to improve performance
1549 --
1550
1551
1552 IF G_MODE = 'UPGRADE' THEN
1553
1554 UPDATE pay_run_result_values prv
1555 SET prv.result_value = p_new_juri_code
1556 WHERE prv.run_result_id = p_run_result_id
1557 AND prv.result_value = p_old_juri_code
1558 AND EXISTS (SELECT 0
1559 FROM pay_input_values_f piv
1560 WHERE piv.input_value_id = prv.input_value_id
1561 AND (piv.name = 'Jurisdiction' OR
1562 piv.name = 'jd_rs' OR
1563 piv.name = 'jd_wk')
1564 -- AND piv.legislation_code = 'US');
1565 AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id );
1566
1567 END IF;
1568 -- Write a message to the table to be later spooled to a report
1569 -- only if a row was updated
1570
1571 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1572
1573 OPEN ele_run_result_val(p_new_juri_code, p_run_result_id, p_assign_act_id, p_assign_id);
1574 FETCH ele_run_result_val INTO l_flag;
1575
1576 IF ele_run_result_val%FOUND THEN
1577
1578
1579 write_message(
1580 p_proc_type => p_proc_type,
1581 p_person_id => p_person_id,
1582 p_assign_id => p_assign_id,
1583 p_old_juri_code => p_old_juri_code,
1584 p_new_juri_code => p_new_juri_code,
1585 p_location => 'PAY_RUN_RESULT_VALUES',
1586 p_id => p_run_result_id);
1587
1588 CLOSE ele_run_result_val;
1589
1590 END IF;
1591
1592 /*END IF;*/
1593 hr_utility.trace('After update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
1594
1595 ELSE
1596
1597 -- Write a message to the table to be later spooled to a report
1598 OPEN ele_run_result_val(p_new_juri_code, p_run_result_id, p_assign_act_id, p_assign_id);
1599 FETCH ele_run_result_val INTO l_flag;
1600
1601 IF ele_run_result_val%FOUND THEN
1602
1603 write_message(
1604 p_proc_type => p_proc_type,
1605 p_person_id => p_person_id,
1606 p_assign_id => p_assign_id,
1607 p_old_juri_code => p_old_juri_code,
1608 p_new_juri_code => p_new_juri_code,
1609 p_location => 'PAY_RUN_RESULT_VALUES',
1610 p_id => p_run_result_id);
1611
1612 END IF;
1613
1614 CLOSE ele_run_result_val;
1615
1616 END IF;
1617
1618
1619 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1620
1621 hr_utility.trace('Before update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1622
1623 IF G_MODE = 'UPGRADE' THEN
1624
1625 UPDATE pay_run_results
1626 SET jurisdiction_code = p_new_juri_code
1627 WHERE jurisdiction_code = p_old_juri_code
1628 AND run_result_id = p_run_result_id
1629 AND assignment_action_id = p_assign_act_id;
1630
1631 END IF;
1632
1633 -- Write a message to the table to be later spooled to a report
1634
1635 /*IF SQL%ROWCOUNT > 0 THEN 6864396 */
1636
1637 OPEN ele_run_results(p_new_juri_code, p_old_juri_code, p_run_result_id, p_assign_act_id);
1638 FETCH ele_run_results INTO l_flag;
1639
1640 IF ele_run_results%FOUND THEN
1641
1642 write_message(
1643 p_proc_type => p_proc_type,
1644 p_person_id => p_person_id,
1645 p_assign_id => p_assign_id,
1646 p_old_juri_code => p_old_juri_code,
1647 p_new_juri_code => p_new_juri_code,
1648 p_location => 'PAY_RUN_RESULTS',
1649 p_id => p_run_result_id);
1650
1651 CLOSE ele_run_results;
1652
1653 END IF;
1654
1655 /*END IF;*/
1656
1657 ELSE
1658
1659 OPEN ele_run_results(p_new_juri_code, p_old_juri_code, p_run_result_id, p_assign_act_id);
1660 FETCH ele_run_results INTO l_flag;
1661
1662 IF ele_run_results%FOUND THEN
1663
1664 write_message(
1665 p_proc_type => p_proc_type,
1666 p_person_id => p_person_id,
1667 p_assign_id => p_assign_id,
1668 p_old_juri_code => p_old_juri_code,
1669 p_new_juri_code => p_new_juri_code,
1670 p_location => 'PAY_RUN_RESULTS',
1671 p_id => p_run_result_id);
1672
1673 END IF;
1674
1675 CLOSE ele_run_results;
1676 END IF;
1677
1678
1679 hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
1680
1681 hr_utility.trace('Exiting pay_us_geo_upd_pkg.run_results');
1682
1683 END run_results;
1684 --
1685 --
1686 --Per bug 2996546
1687 -- This procedure, PROCEDURE pay_action_contexts, will update the context values
1688 --based on assignment_action_id
1689 --
1690 --
1691 PROCEDURE pay_action_contexts
1692 (p_proc_type IN VARCHAR2,
1693 p_person_id IN NUMBER,
1694 p_assign_id IN NUMBER,
1695 p_assign_act_id IN NUMBER,
1696 p_context_id IN NUMBER,
1697 p_old_juri_code IN VARCHAR2,
1698 p_new_juri_code IN VARCHAR2)
1699 IS
1700
1701 CURSOR pac_inside_cur(p_assign_act_id number, p_assign_id number,
1702 p_context_id number, p_old_juri_code varchar2)
1703 IS select 'Y'
1704 FROM pay_action_contexts
1705 WHERE assignment_action_id = p_assign_act_id
1706 AND assignment_id = p_assign_id
1707 AND context_id = p_context_id
1708 AND context_value = p_old_juri_code ;
1709
1710 l_pac_inside_exist varchar2(2);
1711
1712 BEGIN
1713
1714 hr_utility.trace('Entering pay_us_geo_upd_pkg.pay_action_contexts');
1715
1716 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1717
1718 hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
1719
1720 IF G_MODE = 'UPGRADE' THEN
1721
1722 UPDATE pay_action_contexts
1723 SET context_value = p_new_juri_code
1724 WHERE context_value = p_old_juri_code
1725 AND assignment_action_id = p_assign_act_id
1726 AND context_id = p_context_id ;
1727 END IF;
1728
1729 -- Write a message to the table to be later spooled to a report
1730
1731 /* IF SQL%ROWCOUNT > 0 THEN 6864396 */
1732
1733 OPEN pac_inside_cur(p_assign_act_id , p_assign_id,
1734 p_context_id, p_old_juri_code ) ;
1735 FETCH pac_inside_cur into l_pac_inside_exist;
1736 IF pac_inside_cur%FOUND THEN
1737 write_message(
1738 p_proc_type => p_proc_type,
1739 p_person_id => p_person_id,
1740 p_assign_id => p_assign_id,
1741 p_old_juri_code => p_old_juri_code,
1742 p_new_juri_code => p_new_juri_code,
1743 p_location => 'PAY_ACTION_CONTEXTS',
1744 p_id => p_assign_id);
1745
1746 END IF;
1747 CLOSE pac_inside_cur;
1748 /*END IF ;*/
1749 ELSE
1750
1751 -- Write a message to the table to be later spooled to a report
1752
1753 OPEN pac_inside_cur(p_assign_act_id, p_assign_id ,
1754 p_context_id , p_old_juri_code ) ;
1755 FETCH pac_inside_cur into l_pac_inside_exist;
1756 IF pac_inside_cur%FOUND THEN
1757 write_message(
1758 p_proc_type => p_proc_type,
1759 p_person_id => p_person_id,
1760 p_assign_id => p_assign_id,
1761 p_old_juri_code => p_old_juri_code,
1762 p_new_juri_code => p_new_juri_code,
1763 p_location => 'PAY_ACTION_CONTEXTS',
1764 p_id => p_assign_id);
1765
1766 END IF;
1767 CLOSE pac_inside_cur;
1768
1769 END IF;
1770
1771 END pay_action_contexts;
1772 ---
1773 ---
1774 ---
1775
1776 -- This procedure will update the archive item contexts based on assignment_action_id
1777 -- We do not do a mass update on the contexts here because we want to be certain that
1778 -- the geocodes updated have a corresponding tax record for the assignment.
1779 -- Not every old juri code will have an archived item against it for that particular
1780 -- assignment action. But we still have to check to make sure.
1781
1782 PROCEDURE archive_item_contexts (p_proc_type IN VARCHAR2,
1783 p_person_id IN NUMBER,
1784 p_assign_id IN NUMBER,
1785 p_archive_item_id IN NUMBER,
1786 p_context_id IN NUMBER,
1787 p_old_juri_code IN VARCHAR2,
1788 p_new_juri_code IN VARCHAR2)
1789
1790 IS
1791
1792 BEGIN
1793
1794 hr_utility.trace('Entering pay_us_geo_upd_pkg.archive_item_contexts');
1795
1796 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1797
1798 hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
1799
1800 IF G_MODE = 'UPGRADE' THEN
1801
1802 UPDATE ff_archive_item_contexts ffaic
1803 SET ffaic.context = p_new_juri_code
1804 WHERE ffaic.context = p_old_juri_code
1805 AND ffaic.context_id = p_context_id
1806 AND ffaic.archive_item_id = p_archive_item_id;
1807
1808 END IF;
1809
1810 hr_utility.trace('After update of archive item contexts for assignment_id: '||to_char(p_assign_id));
1811
1812 -- Write a message to the table to be later spooled to a report
1813
1814 write_message(
1815 p_proc_type => p_proc_type,
1816 p_person_id => p_person_id,
1817 p_assign_id => p_assign_id,
1818 p_old_juri_code => p_old_juri_code,
1819 p_new_juri_code => p_new_juri_code,
1820 p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
1821 p_id => p_archive_item_id);
1822
1823
1824 ELSE
1825
1826 -- Write a message to the table to be later spooled to a report
1827
1828 write_message(
1829 p_proc_type => p_proc_type,
1830 p_person_id => p_person_id,
1831 p_assign_id => p_assign_id,
1832 p_old_juri_code => p_old_juri_code,
1833 p_new_juri_code => p_new_juri_code,
1834 p_location => 'FF_ARCHIVE_ITEM_CONTEXTS',
1835 p_id => p_archive_item_id);
1836
1837 END IF;
1838
1839 hr_utility.trace('Exiting pay_us_geo_upd_pkg.archive_item_contexts');
1840
1841
1842
1843 END archive_item_contexts;
1844
1845
1846 -- This procedure will upgrade the element entry geocodes.
1847
1848 PROCEDURE element_entries(
1849 p_proc_type IN VARCHAR2,
1850 p_person_id IN NUMBER,
1851 p_assign_id IN NUMBER,
1852 p_input_value_id IN NUMBER,
1853 p_ele_ent_id IN NUMBER,
1854 p_old_juri_code IN VARCHAR2,
1855 p_new_juri_code IN VARCHAR2)
1856
1857
1858 IS
1859
1860 BEGIN
1861 hr_utility.trace('Entering pay_us_geo_upd_pkg.element_entries');
1862
1863 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1864
1865 hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
1866
1867 IF G_MODE = 'UPGRADE' THEN
1868
1869 UPDATE pay_element_entry_values_f
1870 SET screen_entry_value = p_new_juri_code
1871 WHERE screen_entry_value = p_old_juri_code
1872 AND input_value_id+0 = p_input_value_id
1873 AND element_entry_id = p_ele_ent_id;
1874
1875 END IF;
1876 hr_utility.trace('After update of element entry values for assignment_id: '||to_char(p_assign_id));
1877
1878 -- Write a message to the table to be later spooled to a report
1879
1880 write_message(
1881 p_proc_type => p_proc_type,
1882 p_person_id => p_person_id,
1883 p_assign_id => p_assign_id,
1884 p_old_juri_code => p_old_juri_code,
1885 p_new_juri_code => p_new_juri_code,
1886 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
1887 p_id => p_ele_ent_id);
1888
1889 ELSE
1890
1891 -- Write a message to the table to be later spooled to a report
1892
1893 write_message(
1894 p_proc_type => p_proc_type,
1895 p_person_id => p_person_id,
1896 p_assign_id => p_assign_id,
1897 p_old_juri_code => p_old_juri_code,
1898 p_new_juri_code => p_new_juri_code,
1899 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
1900 p_id => p_ele_ent_id);
1901
1902 END IF;
1903
1904 hr_utility.trace('Exiting pay_us_geo_upd_pkg.element_entries');
1905
1906
1907
1908 END element_entries;
1909
1910
1911 -- This procedure will upgrade the latest balance contexts.
1912
1913 PROCEDURE balance_contexts(p_proc_type IN VARCHAR2,
1914 p_person_id IN NUMBER,
1915 p_assign_id IN NUMBER,
1916 p_assign_act_id IN NUMBER,
1917 p_context_id IN NUMBER,
1918 p_lat_bal_id IN NUMBER,
1919 p_old_juri_code IN VARCHAR2,
1920 p_new_juri_code IN VARCHAR2)
1921
1922
1923 IS
1924
1925 BEGIN
1926
1927 hr_utility.trace('Entering pay_us_geo_upd_pkg.balance_contexts');
1928
1929 IF ((p_proc_type <> 'SU' and p_proc_type <> 'US') and p_api_mode = 'N') THEN
1930
1931 hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
1932
1933 IF G_MODE = 'UPGRADE' THEN
1934
1935 UPDATE pay_balance_context_values
1936 SET value = p_new_juri_code
1937 WHERE value = p_old_juri_code
1938 AND context_id = p_context_id
1939 AND latest_balance_id = p_lat_bal_id;
1940
1941 END IF;
1942 -- Write a message to the table to be later spooled to a report
1943
1944 write_message(
1945 p_proc_type => p_proc_type,
1946 p_person_id => p_person_id,
1947 p_assign_id => p_assign_id,
1948 p_old_juri_code => p_old_juri_code,
1949 p_new_juri_code => p_new_juri_code,
1950 p_location => 'PAY_BALANCE_CONTEXT_VALUES',
1951 p_id => p_lat_bal_id);
1952
1953 ELSE
1954
1955 -- Write a message to the table to be later spooled to a report
1956
1957 write_message(
1958 p_proc_type => p_proc_type,
1959 p_person_id => p_person_id,
1960 p_assign_id => p_assign_id,
1961 p_old_juri_code => p_old_juri_code,
1962 p_new_juri_code => p_new_juri_code,
1963 p_location => 'PAY_BALANCE_CONTEXT_VALUES',
1964 p_id => p_lat_bal_id);
1965
1966 END IF;
1967
1968 hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
1969
1970
1971 hr_utility.trace('Exiting pay_us_geo_upd_pkg.balance_contexts');
1972
1973 END balance_contexts;
1974
1975
1976 -- This procedure will take out duplicate VERTEX element entries and add the percentages
1977 -- of the previously duplicated element entries togethor
1978 -- This used to be script pydeldup.sql earlier
1979
1980 PROCEDURE duplicate_vertex_ee(p_assignment_id IN NUMBER)
1981
1982 IS
1983
1984 -- This cursor will get us the element entries of the assignments processed
1985 cursor csr_get_dup (p_assignment number) is
1986 select pev.screen_entry_value sev, pev.element_entry_id eei
1987 from pay_element_entry_values_f pev,
1988 pay_input_values_f piv,
1989 pay_element_types_f pet,
1990 pay_element_links_f pel,
1991 pay_element_entries_f pee
1992 where pee.assignment_id = p_assignment
1993 and pel.element_link_id = pee.element_link_id
1994 and pet.element_type_id = pel.element_type_id
1995 and pet.element_name = 'VERTEX'
1996 and pev.element_entry_id = pee.element_entry_id
1997 and pev.screen_entry_value is not null
1998 and piv.input_value_id = pev.input_value_id+0
1999 and piv.element_type_id = pet.element_type_id
2000 and piv.name = 'Jurisdiction'
2001 and piv.legislation_code = 'US'
2002 and pet.legislation_code = 'US'
2003 order by 1,2;
2004
2005 cursor csr_get_percentage (p_element_entry_id NUMBER) is
2006 select pev.screen_entry_value , pev.effective_start_date,
2007 pev.effective_end_date
2008 from pay_element_entry_values_f pev,
2009 pay_input_values_f piv,
2010 pay_element_types_f pet,
2011 pay_element_links_f pel,
2012 pay_element_entries_f pef
2013 where pef.element_entry_id = p_element_entry_id
2014 and pel.element_link_id = pef.element_link_id
2015 and pet.element_type_id = pel.element_type_id
2016 and pet.element_name = 'VERTEX'
2017 and pev.element_entry_id = pef.element_entry_id
2018 and pev.screen_entry_value is not null
2019 and piv.input_value_id = pev.input_value_id+0
2020 and piv.element_type_id = pet.element_type_id
2021 and piv.name = 'Percentage'
2022 and piv.legislation_code = 'US'
2023 and pet.legislation_code = 'US';
2024
2025 l_prev_screen pay_element_entry_values_f.screen_entry_value%TYPE;
2026 l_prev_eleid pay_element_entry_values_f.element_entry_id%TYPE;
2027 l_percent pay_element_entry_values_f.screen_entry_value%TYPE;
2028 l_effective_start_date pay_element_entry_values_f.effective_start_date%TYPE;
2029 l_effective_end_date pay_element_entry_values_f.effective_end_date%TYPE;
2030
2031 BEGIN
2032
2033 hr_utility.trace('Entering pay_us_geo_upd_pkg.duplicate_vertex_ee');
2034
2035 l_prev_screen := null;
2036 l_prev_eleid := null;
2037
2038 for j in csr_get_dup(p_assignment_id) loop
2039
2040 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 1);
2041
2042 if j.sev = l_prev_screen and j.eei <> l_prev_eleid then
2043 hr_utility.trace('Element Entry Id : '|| to_char(j.eei)
2044 ||' is a duplicate of : ' || to_char(l_prev_eleid)
2045 ||' for assignment_id : ' || to_char(p_assignment_id));
2046
2047 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 2);
2048
2049 /* get the percentages for the record to be deleted */
2050 open csr_get_percentage(j.eei);
2051 loop
2052
2053 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 3);
2054
2055 /* Get the %age for each datetracked record */
2056
2057 fetch csr_get_percentage into l_percent,
2058 l_effective_start_date,
2059 l_effective_end_date;
2060 exit when csr_get_percentage%NOTFOUND;
2061
2062 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 4);
2063
2064 /* Add the %age of the current element entry to the earlier
2065 entry */
2066
2067 IF G_MODE = 'UPGRADE' THEN
2068
2069 hr_utility.trace('The previous element entry id is: '||to_char(l_prev_eleid));
2070
2071 update pay_element_entry_values_f pev
2072 set pev.screen_entry_value = pev.screen_entry_value + l_percent
2073 where pev.element_entry_id = l_prev_eleid
2074 and pev.screen_entry_value is not null
2075 and pev.input_value_id = (select distinct piv.input_value_id
2076 from pay_input_values_f piv,
2077 pay_element_types_f pet,
2078 pay_element_links_f pel,
2079 pay_element_entries_f pef
2080 where pef.element_entry_id =
2081 l_prev_eleid
2082 and pel.element_link_id =
2083 pef.element_link_id
2084 and pet.element_type_id =
2085 pel.element_type_id
2086 and pet.element_name = 'VERTEX'
2087 and piv.element_type_id =
2088 pet.element_type_id
2089 and piv.name = 'Percentage'
2090 and piv.legislation_code = 'US'
2091 and pet.legislation_code = 'US');
2092
2093 END IF;
2094 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 5);
2095
2096 end loop;
2097 close csr_get_percentage;
2098
2099 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 6);
2100
2101 /* Now delete the current entry */
2102
2103 delete pay_element_entries_f
2104 where element_entry_id = j.eei
2105 and assignment_id = p_assignment_id;
2106
2107 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 7);
2108
2109 else
2110
2111 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 8);
2112
2113 l_prev_screen := j.sev;
2114 l_prev_eleid := j.eei;
2115
2116 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 9);
2117
2118 end if;
2119
2120 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 10);
2121
2122 end loop;
2123
2124 hr_utility.set_location('pay_us_geo_upd_pkg.duplicate_vertex_ee', 11);
2125
2126 hr_utility.trace('Exiting pay_us_geo_upd_pkg.duplicate_vertex_ee');
2127
2128 end duplicate_vertex_ee;
2129
2130
2131 -- This procedure will create element entries for assignments that have geocodes
2132 -- which have split from the upgrade.
2133
2134 PROCEDURE insert_ele_entries
2135 (p_proc_type in varchar2,
2136 p_assign_id in number,
2137 p_person_id in number,
2138 p_new_juri_code in varchar2,
2139 p_old_juri_code in varchar2)
2140
2141 IS
2142
2143 -- Finds out if County Tax Record exists for this ASSIGNMENT_ID
2144 cursor c_county_rec (p_assignment_id in number,
2145 p_state_code in varchar2,
2146 p_county_code in varchar2) is
2147 select business_group_id
2148 from pay_us_emp_county_tax_rules_f pecot
2149 where pecot.assignment_id = p_assignment_id
2150 and pecot.state_code = p_state_code
2151 and pecot.county_code = p_county_code;
2152
2153 -- Finds out if City Tax Record exists for this ASSIGNMENT_ID
2154 cursor c_tax_rec (p_assignment_id in number,
2155 p_state_code in varchar2,
2156 p_county_code in varchar2,
2157 p_city_code in varchar2) is
2158 select business_group_id
2159 from pay_us_emp_city_tax_rules_f pect
2160 where pect.assignment_id = p_assignment_id
2161 and pect.state_code = p_state_code
2162 and pect.county_code = p_county_code
2163 and pect.city_code = p_city_code;
2164
2165 -- Gets the date when the eligiblity criteria was met for this assignment.
2166 cursor c_elig_date (p_assignment_id in number) is
2167 select min(peft.effective_start_date),
2168 max(peft.effective_end_date),
2169 peft.business_group_id
2170 from pay_us_emp_city_tax_rules_f peft
2171 where peft.assignment_id = p_assignment_id
2172 group by peft.business_group_id;
2173
2174 ld_eff_start_date date;
2175 ld_eff_end_date date;
2176 ln_state_code pay_us_emp_state_tax_rules_f.state_code%TYPE;
2177 ln_county_code pay_us_emp_county_tax_rules_f.county_code%TYPE;
2178 ln_city_code pay_us_emp_city_tax_rules_f.city_code%TYPE;
2179 ln_old_city_code pay_us_modified_geocodes.old_city_code%TYPE;
2180
2181 ln_business_group_id number;
2182 ln_check number;
2183 ln_emp_city_tax_rule_id number;
2184
2185 lc_exists number;
2186 lc_insert_rec varchar2(1);
2187 l_profile_value varchar2(1):='N';
2188 BEGIN
2189
2190 hr_utility.trace('Entering pay_us_geo_upd_pkg.insert_ele_entries');
2191
2192 lc_insert_rec := 'N';
2193
2194 IF ((p_proc_type = 'SU' or p_proc_type = 'US') and p_api_mode = 'N') THEN
2195
2196 ln_state_code := substr(p_new_juri_code,1,2);
2197 ln_county_code := substr(p_new_juri_code,4,3);
2198 ln_city_code := substr(p_new_juri_code,8);
2199 ln_old_city_code := substr(p_old_juri_code,8);
2200
2201 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',1);
2202
2203 open c_county_rec(p_assign_id,
2204 ln_state_code,
2205 ln_county_code);
2206
2207 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',2);
2208
2209 fetch c_county_rec into lc_exists;
2210 if c_county_rec%notfound then
2211
2212 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',3);
2213
2214 -- Call write message to store information that their is no county record for this assignment
2215 write_message(
2216 p_proc_type => 'MISSING_COUNTY_RECORDS',
2217 p_person_id => p_person_id,
2218 p_assign_id => p_assign_id,
2219 p_old_juri_code => ln_state_code||'-'||ln_county_code,
2220 p_new_juri_code => p_new_juri_code,
2221 p_location => 'PAY_US_EMP_COUNTY_TAX_RULES_F',
2222 p_id => null);
2223
2224 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
2225
2226
2227 ELSE
2228
2229 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',5);
2230
2231 open c_tax_rec(p_assign_id,
2232 ln_state_code,
2233 ln_county_code,
2234 ln_old_city_code);
2235
2236 fetch c_tax_rec into ln_check;
2237
2238 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',6);
2239
2240 if c_tax_rec%found then -- changed notfound to found
2241 close c_tax_rec;
2242 open c_tax_rec(p_assign_id,
2243 ln_state_code,
2244 ln_county_code,
2245 ln_city_code); -- Check with new city code.
2246 fetch c_tax_rec into ln_check;
2247 if c_tax_rec%notfound then
2248 lc_insert_rec := 'Y';
2249 end if;
2250 end if;
2251
2252 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',7);
2253
2254 close c_tax_rec;
2255
2256 if lc_insert_rec = 'Y' then
2257
2258 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',8);
2259
2260 open c_elig_date(p_assign_id);
2261
2262 fetch c_elig_date into ld_eff_start_date, ld_eff_end_date, ln_business_group_id;
2263
2264 if c_elig_date%notfound then
2265 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
2266 --Exiting if there are no city Tax Records.
2267 end if;
2268
2269 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
2270 close c_elig_date;
2271
2272 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
2273
2274 hr_utility.trace('The business group id is: '||to_char(ln_business_group_id));
2275
2276 IF G_MODE = 'UPGRADE' THEN
2277
2278 /* changes for 7240905*/
2279 if(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'))<> 'Y' then
2280 fnd_profile.put('HR_CROSS_BUSINESS_GROUP','Y');
2281 l_profile_value := 'Y';
2282 hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2283 end if;
2284
2285 ln_emp_city_tax_rule_id :=
2286 pay_us_emp_dt_tax_rules.insert_def_city_rec(p_assign_id,
2287 ld_eff_start_date,
2288 ld_eff_end_date,
2289 ln_state_code,
2290 ln_county_code,
2291 ln_city_code,
2292 ln_business_group_id,
2293 0);
2294
2295 /* changes for 7240905*/
2296 if(l_profile_value ='Y') then
2297 fnd_profile.put('HR_CROSS_BUSINESS_GROUP','N');
2298 hr_utility.trace('modifed the profile to'||to_char(fnd_profile.value('HR_CROSS_BUSINESS_GROUP')));
2299 end if;
2300
2301 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2302
2303 -- Write to the table with the new city information
2304
2305 write_message(
2306 p_proc_type => 'NEW_CITY_RECORDS',
2307 p_person_id => p_person_id,
2308 p_assign_id => p_assign_id,
2309 p_old_juri_code => null,
2310 p_new_juri_code => p_new_juri_code,
2311 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2312 p_id => ln_emp_city_tax_rule_id);
2313
2314 else /* Modified for bug 6864396*/
2315
2316 write_message(
2317 p_proc_type => 'NEW_CITY_RECORDS',
2318 p_person_id => p_person_id,
2319 p_assign_id => p_assign_id,
2320 p_old_juri_code => null,
2321 p_new_juri_code => p_new_juri_code,
2322 p_location => 'PAY_US_EMP_CITY_TAX_RULES_F',
2323 p_id => null);
2324
2325 END IF;
2326
2327 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
2328
2329 /*
2330 pay_us_emp_dt_tax_rules.maintain_element_entry
2331 (p_assignment_id => p_assign_id,
2332 p_effective_start_date => ld_eff_start_date,
2333 p_effective_end_date => ld_eff_end_date,
2334 p_session_date => ld_eff_start_date,
2335 p_jurisdiction_code => p_new_juri_code,
2336 p_percentage_time => 0,
2337 p_mode => 'INSERT');
2338 */
2339
2340 -- Write to the table the new vertex information of the new vertex record
2341
2342 write_message(
2343 p_proc_type => 'NEW_VERTEX_RECORDS',
2344 p_person_id => p_person_id,
2345 p_assign_id => p_assign_id,
2346 p_old_juri_code => null,
2347 p_new_juri_code => p_new_juri_code,
2348 p_location => 'PAY_ELEMENT_ENTRIES_F',
2349 p_id => null);
2350
2351 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
2352
2353 /* END IF; County 6864396*/
2354 END IF;
2355
2356 end if;
2357
2358 hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',14);
2359
2360 ELSE -- p_proc_type != 'SU' and p_proc_type != 'US'
2361
2362 write_message(
2363 p_proc_type => 'ELE_ENTRY_INSERT_NOT_REQD',
2364 p_person_id => p_person_id,
2365 p_assign_id => p_assign_id,
2366 p_old_juri_code => null,
2367 p_new_juri_code => p_new_juri_code,
2368 p_location => 'PAY_ELEMENT_ENTRIES_F',
2369 p_id => null);
2370
2371 END IF;
2372 END insert_ele_entries;
2373
2374
2375 -- This procedure will check the percentage time in state for a particular jurisdiction
2376 -- and make sure that percent time is not more than 100%
2377
2378 PROCEDURE check_time(p_assign_id IN NUMBER)
2379
2380 IS
2381
2382 --Retrieve all states for the assignments that have changed from per_assignments_f
2383
2384 CURSOR state_cur(p_assign_id NUMBER) IS
2385 SELECT pus.state_code,
2386 pus.state_name,
2387 pusf.effective_start_date,
2388 pusf.effective_end_date
2389 FROM pay_us_states pus,
2390 pay_us_emp_state_tax_rules_f pusf
2391 WHERE pusf.assignment_id = p_assign_id
2392 AND pusf.state_code = pus.state_abbrev
2393 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
2394 where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
2395 and pugu.process_type = 'PERCENTAGE_OVER_100'
2396 and pugu.assignment_id = p_assign_id
2397 and pugu.process_mode = g_mode
2398 and pugu.process_type = g_process_type
2399 and pugu.id = g_geo_phase_id);
2400
2401
2402
2403 state_rec state_cur%ROWTYPE;
2404
2405 --Sum the percentage for all Vertex entries falling within the state
2406 --jurisdiction for the effective dates of the assignment.
2407 CURSOR sum_cur(p_assign_id NUMBER, start_date DATE,
2408 end_date DATE, state_code CHAR) IS
2409 SELECT sum(nvl(to_number(pev2.screen_entry_value),0))
2410 FROM pay_input_values_f piv2,
2411 pay_element_entry_values_f pev2,
2412 pay_input_values_f piv1,
2413 pay_element_entry_values_f pev1,
2414 pay_element_types_f pet,
2415 pay_element_links_f pel,
2416 pay_element_entries_f pef
2417 WHERE pef.assignment_id = p_assign_id
2418 AND pef.creator_type = 'UT'
2419 AND pef.element_link_id = pel.element_link_id
2420 AND pel.element_type_id = pet.element_type_id
2421 AND pet.element_name = 'VERTEX'
2422 AND (
2423 (start_date >= pef.effective_start_date AND
2424 end_date <= pef.effective_end_date)
2425 OR (start_date = pef.effective_end_date)
2426 OR (end_date = pef.effective_start_date)
2427 )
2428 AND (pef.element_entry_id = pev1.element_entry_id
2429 AND pef.effective_start_date = pev1.effective_start_date
2430 AND pef.effective_end_date = pev1.effective_end_date
2431 AND state_code = substr(pev1.screen_entry_value,1,2)
2432 AND pev1.input_value_id = piv1.input_value_id
2433 AND piv1.name = 'Jurisdiction'
2434 AND piv1.legislation_code = 'US')
2435 AND (pev2.element_entry_id = pev1.element_entry_id
2436 AND pev2.effective_start_date = pev1.effective_start_date
2437 AND pev2.effective_end_date = pev1.effective_end_date
2438 AND pev2.screen_entry_value is not null
2439 AND piv2.input_value_id = pev2.input_value_id
2440 AND piv2.name = 'Percentage'
2441 AND piv2.legislation_code = 'US');
2442
2443 sum_rec sum_cur%ROWTYPE;
2444
2445 l_person_id per_people_f.person_id%TYPE;
2446 tot_percentage NUMBER;
2447 percentage NUMBER;
2448
2449 BEGIN
2450
2451 hr_utility.trace('Entering pay_us_geo_upd_pkg.check_time');
2452
2453 tot_percentage := 0;
2454
2455 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',1);
2456
2457 -- Get each state for the assignment.
2458 FOR state_rec IN state_cur(p_assign_id) LOOP
2459
2460 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',2);
2461
2462 -- Get the percentage of time worked in that state.
2463
2464 OPEN sum_cur(p_assign_id, state_rec.effective_start_date,
2465 state_rec.effective_end_date, state_rec.state_code);
2466 FETCH sum_cur INTO percentage;
2467 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',3);
2468
2469 IF sum_cur%FOUND THEN
2470 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',4);
2471
2472 tot_percentage := tot_percentage + percentage;
2473 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',5);
2474
2475 END IF;
2476 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',6);
2477
2478 CLOSE sum_cur;
2479 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',7);
2480
2481 END LOOP; -- state_cur
2482 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',8);
2483
2484 IF (tot_percentage > 100) THEN
2485
2486 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',9);
2487
2488 SELECT ppf.person_id
2489 INTO l_person_id
2490 FROM per_all_people_f ppf,
2491 per_all_assignments_f paf
2492 WHERE ppf.person_id = paf.person_id
2493 AND paf.assignment_id = p_assign_id
2494 AND ppf.effective_start_date = (SELECT max(ppf2.effective_start_date)
2495 FROM per_all_people_f ppf2
2496 WHERE ppf2.person_id = ppf.person_id);
2497
2498
2499 write_message(
2500 p_proc_type => 'PERCENTAGE_OVER_100',
2501 p_person_id => l_person_id,
2502 p_assign_id => p_assign_id,
2503 p_old_juri_code => null,
2504 p_new_juri_code => null,
2505 p_location => 'PAY_ELEMENT_ENTRY_VALUES_F',
2506 p_id => null);
2507
2508
2509 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',10);
2510
2511 END IF;
2512 hr_utility.set_location('pay_us_geo_upd_pkg.check_time',11);
2513
2514 -- Taking out the exception here because if the procedure errors let it go to the calling block and
2515 -- use that exception handler as that errors to the savepoint and continues with the assignment.
2516
2517 /*
2518 EXCEPTION
2519 WHEN OTHERS THEN
2520 hr_utility.trace(SQLCODE||SQLERRM||'Program error contact support');
2521 hr_utility.raise_error;
2522 */
2523 END check_time;
2524
2525
2526
2527
2528 -- THE BEGINNING OF THE MAIN PROCEDURE: UPGRADE_GEOCODES
2529
2530 BEGIN
2531
2532 -- Initialize the global variables here
2533
2534 g_geo_phase_id := null;
2535 g_mode := null;
2536 g_process_type := null;
2537
2538 -- hr_utility.trace_on(null,'oracle');
2539
2540 hr_utility.trace('Entering pay_us_geo_upd_pkg.upgrade_geocodes');
2541
2542 -- Set the global phase id for the geo update
2543
2544 g_geo_phase_id := p_geo_phase_id;
2545
2546 hr_utility.trace('The pay patch status id for this upgrade is: '||to_char(g_geo_phase_id));
2547
2548 -- Set the global mode for the upgrade
2549
2550 g_mode := p_mode;
2551
2552 hr_utility.trace('The mode for this upgrade is: '||g_mode);
2553
2554
2555
2556 --Check if pay_us_asg_reporting table exists as some clients may
2557 --not have this table on their database.
2558 SELECT count(*)
2559 INTO table_exist
2560 FROM cat
2561 WHERE table_name = tab_name;
2562
2563 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',1);
2564
2565 --Bug 2996546 call procedure load_input_values
2566 load_input_values;
2567 --hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',230);
2568
2569
2570 OPEN main_driving_cur(P_ASSIGN_START, P_ASSIGN_END, P_CITY_NAME, P_API_MODE);
2571
2572 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',5);
2573
2574 LOOP
2575
2576 BEGIN
2577
2578 FETCH main_driving_cur into main_old_juri_code, main_assign_id, main_new_juri_code, main_person_id,
2579 main_new_city_code, main_proc_type, main_city_tax_rule_id;
2580 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',10);
2581
2582 EXIT when main_driving_cur%NOTFOUND;
2583
2584
2585 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',15);
2586
2587
2588 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',20);
2589
2590 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',25);
2591
2592 -- Set the global variable for g_process_type
2593
2594 g_process_type := main_proc_type;
2595
2596 hr_utility.trace('The process type for this pair of geocodes is: '||g_process_type);
2597 hr_utility.trace('The main assignment id is: '||to_char(main_assign_id));
2598 hr_utility.trace('The main old juri code id is: '||main_old_juri_code);
2599 hr_utility.trace('The main new juri code id is: '||main_new_juri_code);
2600 hr_utility.trace('The main person id is: '||to_char(main_person_id));
2601 hr_utility.trace('The city name is: '||p_city_name);
2602
2603 -- We first insert a row into PAY_US_GEO_UPDATE to state that we are processing this assignment
2604 -- Our concern is how do we track an assignment that has errored. So we will start by creating
2605 -- a row for the assignment with a p_status of 'U'. Then at the end of the loop we will
2606 -- change the p_status to 'C' before commiting
2607
2608 -- If a person has errored and this upgrade is rerun we must not re-write the message
2609
2610 l_proc_stage := 'START';
2611
2612 OPEN chk_assign_error_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
2613
2614 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',30);
2615
2616 FETCH chk_assign_error_cur INTO l_chk_assign_error;
2617 IF (chk_assign_error_cur%FOUND or p_api_mode = 'Y') THEN
2618 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',35);
2619
2620 NULL; /* We do nothing here because we want the assignment to re-processed
2621 but do not create another row in the pay_us_geo_update table */
2622
2623 ELSE
2624 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
2625
2626 -- We need to store a process type because the same geocode can have two records for different city names
2627 -- Thus we would get two rows in PAY_US_GEO_UPDATE for the same assignment id.
2628
2629 write_message(
2630 p_proc_type => main_proc_type,
2631 p_person_id => main_person_id,
2632 p_assign_id => main_assign_id,
2633 p_old_juri_code => main_old_juri_code,
2634 p_new_juri_code => main_new_juri_code,
2635 p_location => null,
2636 p_id => null,
2637 p_status => 'P');
2638 hr_utility.set_location('before commit',1);
2639 -- commit;
2640
2641 END IF;
2642
2643 CLOSE chk_assign_error_cur;
2644 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',45);
2645
2646 -- Create element entries and a new city record for new jusrisdictions for the assignment. We do this first
2647 -- because we want to commit based on an assignment.
2648
2649 l_proc_stage := 'INSERT_ELEMENT_ENTRIES';
2650
2651
2652 insert_ele_entries (
2653 p_proc_type => main_proc_type,
2654 p_assign_id => main_assign_id,
2655 p_person_id => main_person_id,
2656 p_new_juri_code => main_new_juri_code,
2657 p_old_juri_code => main_old_juri_code);
2658
2659
2660 -- Here is the savepoint so if an assignment fails during the upgrade it will rollback to here and continue with the
2661 -- next assignment.
2662
2663 SAVEPOINT GEO_UPDATE_SAVEPOINT;
2664
2665 --Update pay_us_asg_reporting table using dynamic sql. We
2666 --must build and execute a new update statement each time.
2667 --This used to point to non-dt w4 tables, changing.
2668
2669 IF (table_exist <> '0') THEN
2670 l_text := 'UPDATE '||tab_name||
2671 ' SET jurisdiction_code = '''||main_new_juri_code||
2672 ''' WHERE assignment_id = '''||to_char(main_assign_id)||
2673 ''' AND jurisdiction_code = '''||main_old_juri_code||
2674 '''';
2675 sql_cursor := dbms_sql.open_cursor;
2676 dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7);
2677 ret := dbms_sql.execute(sql_cursor);
2678 dbms_sql.close_cursor(sql_cursor);
2679 END IF;
2680
2681
2682 --Update element entry values
2683
2684 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',50);
2685
2686 OPEN pev_cur(main_old_juri_code, main_assign_id);
2687 LOOP
2688 FETCH pev_cur INTO pev_rec;
2689 EXIT WHEN pev_cur%NOTFOUND;
2690
2691 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',55);
2692
2693 l_proc_stage := 'ELEMENT_ENTRIES';
2694
2695 element_entries(
2696 p_proc_type => main_proc_type,
2697 p_person_id => main_person_id,
2698 p_assign_id => main_assign_id,
2699 p_input_value_id => pev_rec.input_value_id,
2700 p_ele_ent_id => pev_rec.element_entry_id,
2701 p_old_juri_code => main_old_juri_code,
2702 p_new_juri_code => main_new_juri_code);
2703
2704 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',60);
2705
2706
2707 END LOOP;
2708 CLOSE pev_cur;
2709
2710 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',65);
2711
2712
2713 -- Conditionally Update run results and run result values
2714 --Per bug 2996546 included another condition
2715 --where clause in OR length(main_old_juri_code) = 2
2716 --to include Canada legislation
2717 --
2718
2719 BEGIN
2720
2721 SELECT 'Y'
2722 INTO lv_update_prr
2723 FROM dual
2724 WHERE EXISTS (SELECT 0
2725 FROM pay_us_city_tax_info_f
2726 WHERE jurisdiction_code = main_old_juri_code)
2727 OR EXISTS (SELECT 0
2728 FROM pay_us_city_tax_info_f
2729 WHERE jurisdiction_code = main_new_juri_code)
2730 OR length(main_old_juri_code) = 2 ;
2731 EXCEPTION
2732 WHEN NO_DATA_FOUND THEN
2733 lv_update_prr := 'N';
2734
2735 END;
2736
2737 IF lv_update_prr = 'Y' THEN
2738
2739 -- Bug 3319878 -- Opening cursor
2740
2741 OPEN paa_cur(main_assign_id);
2742 LOOP
2743 FETCH paa_cur INTO paa_rec;
2744 EXIT WHEN paa_cur%NOTFOUND;
2745
2746 OPEN prr_cur(paa_rec,main_assign_id);
2747 LOOP
2748 FETCH prr_cur INTO prr_rec;
2749 EXIT WHEN prr_cur%NOTFOUND;
2750
2751 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',70);
2752
2753 l_proc_stage := 'RUN_RESULTS';
2754
2755 run_results(
2756 p_proc_type => main_proc_type,
2757 p_person_id => main_person_id,
2758 p_assign_id => main_assign_id,
2759 p_assign_act_id => prr_rec.assignment_action_id,
2760 p_run_result_id => prr_rec.run_result_id,
2761 p_old_juri_code => main_old_juri_code,
2762 p_new_juri_code => main_new_juri_code);
2763
2764
2765 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',75);
2766 END LOOP;
2767 CLOSE prr_cur;
2768 END LOOP;
2769 CLOSE paa_cur;
2770
2771 END IF;
2772 --
2773 --
2774 --
2775
2776 --Per bug 2996546
2777 -- Update pay_action_contexts . context value
2778 --
2779 --
2780
2781 OPEN pac_cur(main_assign_id, main_city_tax_rule_id);
2782 LOOP
2783 FETCH pac_cur INTO pac_rec;
2784 EXIT WHEN pac_cur%NOTFOUND;
2785
2786
2787 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',240);
2788
2789 l_proc_stage := 'PAY_ACTION_CONTEXTS';
2790
2791 pay_action_contexts(
2792 p_proc_type => main_proc_type,
2793 p_person_id => main_person_id,
2794 p_assign_id => main_assign_id,
2795 p_assign_act_id => pac_rec.assignment_action_id,
2796 p_context_id => pac_rec.context_id,
2797 p_old_juri_code => main_old_juri_code,
2798 p_new_juri_code => main_new_juri_code);
2799
2800
2801 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',250);
2802
2803 END LOOP;
2804 CLOSE pac_cur;
2805
2806 --
2807 --
2808
2809 -- Update ff archive item contexts
2810
2811
2812 OPEN fac_cur(main_assign_id, main_old_juri_code);
2813 LOOP
2814 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',80);
2815
2816 FETCH fac_cur INTO fac_rec;
2817 EXIT WHEN fac_cur%NOTFOUND;
2818
2819 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',85);
2820
2821 l_proc_stage := 'ARCHIVE_ITEM_CONTEXTS';
2822
2823 archive_item_contexts(
2824 p_proc_type => main_proc_type,
2825 p_person_id => main_person_id,
2826 p_assign_id => main_assign_id,
2827 p_archive_item_id => fac_rec.archive_item_id,
2828 p_context_id => fac_rec.context_id,
2829 P_OLD_JURi_code => main_old_juri_code,
2830 p_new_juri_code => main_new_juri_code);
2831
2832
2833 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',90);
2834
2835 END LOOP;
2836 CLOSE fac_cur;
2837
2838
2839 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',95);
2840
2841
2842 -- Update person balance context values.
2843
2844
2845 OPEN pbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
2846 LOOP
2847 FETCH pbcv_cur INTO pbcv_rec;
2848 EXIT WHEN pbcv_cur%NOTFOUND;
2849
2850 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',100);
2851
2852 l_proc_stage := 'PERSON_BALANCE_CONTEXTS';
2853
2854 balance_contexts(
2855 p_proc_type => main_proc_type,
2856 p_person_id => main_person_id,
2857 p_assign_id => main_assign_id,
2858 p_assign_act_id => pbcv_rec.assignment_action_id,
2859 p_context_id => pbcv_rec.context_id ,
2860 p_lat_bal_id => pbcv_rec.latest_balance_id,
2861 p_old_juri_code => main_old_juri_code,
2862 p_new_juri_code => main_new_juri_code);
2863
2864 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',105);
2865
2866
2867 END LOOP;
2868 CLOSE pbcv_cur;
2869
2870 -- Update assignment balance context values.
2871
2872 OPEN pacv_cur(main_old_juri_code, main_assign_id, main_person_id);
2873 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',110);
2874
2875 LOOP
2876 FETCH pacv_cur INTO pacv_rec;
2877 EXIT WHEN pacv_cur%NOTFOUND;
2878
2879 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',115);
2880
2881 l_proc_stage := 'ASSIGNMENT_BALANCE_CONTEXTS';
2882
2883 balance_contexts(
2884 p_proc_type => main_proc_type,
2885 p_person_id => main_person_id,
2886 p_assign_id => main_assign_id,
2887 p_assign_act_id => pacv_rec.assignment_action_id,
2888 p_context_id => pacv_rec.context_id ,
2889 p_lat_bal_id => pacv_rec.latest_balance_id,
2890 p_old_juri_code => main_old_juri_code,
2891 p_new_juri_code => main_new_juri_code);
2892
2893
2894 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',120);
2895
2896
2897 END LOOP;
2898 CLOSE pacv_cur;
2899
2900 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',125);
2901
2902 -- Rosie Monge 10/17/2005
2903 -- Update Pay_Latest_balances context values.
2904
2905 OPEN plbcv_cur(main_old_juri_code, main_assign_id, main_person_id);
2906 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',126 );
2907 LOOP
2908 FETCH plbcv_cur INTO plbcv_rec;
2909
2910 EXIT WHEN plbcv_cur%NOTFOUND;
2911
2912 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',127);
2913
2914 l_proc_stage := 'PAY_LATEST_BALANCES_CONTEXT';
2915
2916 balance_contexts(
2917 p_proc_type => main_proc_type,
2918 p_person_id => main_person_id,
2919 p_assign_id => main_assign_id,
2920 p_assign_act_id => plbcv_rec.assignment_action_id,
2921 p_context_id => plbcv_rec.context_id ,
2922 p_lat_bal_id => plbcv_rec.latest_balance_id,
2923 p_old_juri_code => main_old_juri_code,
2924 p_new_juri_code => main_new_juri_code);
2925
2926
2927 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',128);
2928
2929 END LOOP;
2930 CLOSE plbcv_cur;
2931 -- End Rosie Monge addition for bug 4602222
2932
2933 --Update the pay_balance_batch_lines table.
2934
2935 l_proc_stage := 'BALANCE_BATCH_LINES';
2936
2937 balance_batch_lines(
2938 p_proc_type => main_proc_type,
2939 p_person_id => main_person_id,
2940 p_assign_id => main_assign_id,
2941 p_old_juri_code => main_old_juri_code,
2942 p_new_juri_code => main_new_juri_code);
2943
2944 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',130);
2945
2946 ---
2947 ---
2948 ---
2949 --Per bug 2738574
2950 --Update the pay_run_balances table.
2951
2952 l_proc_stage := 'PAY_RUN_BALANCES';
2953
2954 pay_run_balances(
2955 p_proc_type => main_proc_type,
2956 p_person_id => main_person_id,
2957 p_assign_id => main_assign_id,
2958 p_new_city_code => main_new_city_code,
2959 p_old_juri_code => main_old_juri_code,
2960 p_new_juri_code => main_new_juri_code);
2961
2962 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',131);
2963 ---
2964 ---
2965 ---
2966
2967
2968 -- Check for and delete any duplicate Vertex element entries
2969 -- This can be caused by two geocodes combining.
2970 -- We will then add the percentages togethor before deleting.
2971
2972 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',135);
2973
2974 l_proc_stage := 'DUPLICATE_VERTEX_EE';
2975
2976 duplicate_vertex_ee(main_assign_id);
2977
2978 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',140);
2979
2980
2981 --Update the pay_us_emp_city_tax_rules_f table.
2982
2983 OPEN city_rec_cur(main_new_juri_code, main_old_juri_code, main_assign_id, main_city_tax_rule_id);
2984 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',145);
2985
2986 FETCH city_rec_cur INTO l_city_tax_exists;
2987 CLOSE city_rec_cur;
2988
2989 l_proc_stage := 'CITY_TAX_RECORDS';
2990
2991 IF l_city_tax_exists = 'Y' THEN
2992 city_tax_records (
2993 p_proc_type => main_proc_type,
2994 p_person_id => main_person_id,
2995 p_assign_id => main_assign_id,
2996 p_old_juri_code => main_old_juri_code,
2997 p_new_juri_code => main_new_juri_code,
2998 p_new_city_code => main_new_city_code,
2999 p_city_tax_record_id => main_city_tax_rule_id);
3000
3001 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',150);
3002
3003 END IF;
3004
3005
3006
3007
3008 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',155);
3009
3010
3011 -- Now we check for assignments with more than 100% time in jurisdiction
3012
3013 l_proc_stage := 'CHECK_TIME';
3014
3015 check_time(p_assign_id => main_assign_id);
3016
3017 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',160);
3018
3019 -- Now we update the SU and US cases to a status of 'A' for assignments that need to be updated
3020 -- via the API. If the cursor is found then we will update the status to 'A', only if the assignment
3021 -- was not updated because the same jurisdiction also had another type.
3022
3023 l_proc_stage := 'SET API';
3024
3025 OPEN chk_assign_api_cur(main_assign_id, main_new_juri_code, main_old_juri_code);
3026 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',165);
3027
3028 FETCH chk_assign_api_cur into l_chk_assign_api;
3029 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3030
3031 CLOSE chk_assign_api_cur;
3032
3033 IF (l_chk_assign_api = 'Y' and p_api_mode = 'N') THEN
3034
3035 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',175);
3036
3037 UPDATE PAY_US_GEO_UPDATE
3038 SET status = 'A', description = null
3039 WHERE assignment_id = main_assign_id
3040 AND old_juri_code = main_old_juri_code
3041 AND new_juri_code = main_new_juri_code
3042 AND table_name is null
3043 AND table_value_id is null
3044 AND status = 'P'
3045 AND process_type = main_proc_type;
3046
3047 ELSE
3048 -- Now we update the assignment that has just processed to a status of 'C' in PAY_US_GEO_UPDATE
3049
3050 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',180);
3051
3052 l_proc_stage := 'END';
3053
3054 UPDATE PAY_US_GEO_UPDATE
3055 SET status = 'C', description = null
3056 WHERE assignment_id = main_assign_id
3057 AND old_juri_code = main_old_juri_code
3058 AND new_juri_code = main_new_juri_code
3059 AND table_name is null
3060 AND table_value_id is null
3061 AND status in ('P','A')
3062 AND process_type = main_proc_type;
3063
3064 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',185);
3065
3066 END IF;
3067
3068 hr_utility.set_location('before commit',2);
3069 -- commit; /* We commit at this point so if it fails at any point let it rollback to the savepoint and continue */
3070
3071
3072 hr_utility.trace('Exiting pay_us_geo_upd_pkg.upgrade_geocodes');
3073
3074
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 l_error_text := 'An error occurred in step: '||l_proc_stage||' The sql error message is: '||SQLERRM|| ' The error code is: '||to_char(SQLCODE);
3078
3079 hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3080 hr_utility.trace('Entered the main program exception handler');
3081 hr_utility.trace('The code failed at process type of: '||l_proc_stage);
3082
3083 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Person id = ' || to_char(main_person_id));
3084 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Assignment id = ' || to_char(main_assign_id));
3085 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' Old Jurisdiction Code = ' || main_old_juri_code);
3086 fnd_file.put_line(fnd_file.log, 'Exception ' || l_proc_stage || ' New Jurisdiction Code = ' || main_new_juri_code);
3087 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3088
3089 rollback to GEO_UPDATE_SAVEPOINT;
3090
3091 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',170);
3092
3093 UPDATE PAY_US_GEO_UPDATE
3094 SET description = l_error_text
3095 WHERE assignment_id = main_assign_id
3096 AND old_juri_code = main_old_juri_code
3097 AND new_juri_code = main_new_juri_code
3098 AND table_name is null
3099 AND table_value_id is null
3100 AND old_juri_code = main_old_juri_code
3101 AND new_juri_code = main_new_juri_code
3102 AND status = 'P'
3103 AND process_type = main_proc_type;
3104
3105 -- Close all the cursors that are within the main loop, otherwise they will remain open.
3106
3107 IF chk_assign_error_cur%ISOPEN THEN
3108 CLOSE chk_assign_error_cur;
3109 END IF;
3110
3111 IF pev_cur%ISOPEN THEN
3112 CLOSE pev_cur;
3113 END IF;
3114
3115 IF prr_cur%ISOPEN THEN
3116 CLOSE prr_cur;
3117 END IF;
3118
3119 --Bug 3319878
3120 IF paa_cur%ISOPEN THEN
3121 CLOSE paa_cur;
3122 END IF;
3123
3124 IF fac_cur%ISOPEN THEN
3125 CLOSE fac_cur;
3126 END IF;
3127
3128
3129 IF pbcv_cur%ISOPEN THEN
3130 CLOSE pbcv_cur;
3131 END IF;
3132
3133 IF pacv_cur%ISOPEN THEN
3134 CLOSE pacv_cur;
3135 END IF;
3136
3137 IF city_rec_cur%ISOPEN THEN
3138 CLOSE city_rec_cur;
3139 END IF;
3140
3141 IF chk_assign_api_cur%ISOPEN THEN
3142 CLOSE chk_assign_api_cur;
3143 END IF;
3144
3145 hr_utility.set_location('before commit',3);
3146 -- commit;
3147
3148 END;
3149
3150 END LOOP;
3151
3152 CLOSE main_driving_cur;
3153
3154 -- Remove duplicate city tax records created
3155 -- by geocode updates for all assignment ids
3156 -- in the range processed.
3157
3158 del_dup_city_tax_recs;
3159
3160 END upgrade_geocodes;
3161
3162 -- END OF THE MAIN UPGRADE GEOCODES PROCEDURE
3163
3164
3165 -- This procedure is seperate from the above main upgrade_geocodes
3166 -- This procedure will update all the taxability rules
3167 -- By taking in a parameter of P_GEO_PHASE_ID we can determine if the taxability rules
3168 -- have been upgraded already.
3169 -- This procedure will only be run by one process, NOT MULTIPLE TIMES
3170 -- This procedure taxes the place of pyrulupd.sql from previous versions
3171
3172 PROCEDURE update_taxability_rules(P_GEO_PHASE_ID IN NUMBER,
3173 P_MODE IN VARCHAR2,
3174 P_PATCH_NAME IN VARCHAR2)
3175
3176 IS
3177
3178 --Retrieve all changed geocodes on pay_taxability_rules table.
3179
3180
3181 --Bug 3319878 -- Changed the cursor query to reduce cost.
3182 --Bug 5042715 -- Added hints to reduce cost.
3183 CURSOR ptax_cur IS
3184 SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
3185 ptax PAY_TAXABILITY_RULES_UK)
3186 use_nl(pmod ptax)*/
3187 distinct ptax.jurisdiction_code
3188 FROM pay_us_modified_geocodes pmod,
3189 pay_taxability_rules ptax
3190 WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
3191 AND pmod.process_type in ('UP','RP')
3192 AND pmod.patch_name = p_patch_name
3193 AND substr(ptax.jurisdiction_code,8,4) <> '0000'
3194 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3195 where pugu.table_name = 'PAY_TAXABILITY_RULES'
3196 and pugu.new_juri_code = ptax.jurisdiction_code
3197 and pugu.process_mode = g_mode
3198 and pugu.process_type = g_process_type
3199 and pugu.id = g_geo_phase_id
3200 and rownum <2);
3201
3202
3203 ptax_rec ptax_cur%ROWTYPE;
3204
3205 --Per bug 2996546
3206 --Added a cursor ptax_ca_cur to the procedure update_taxability_rules
3207 --Retrieve all changed jurisdiction_code on pay_taxability_rules table.
3208 --and update (for Canadian Legislation)
3209 --
3210
3211 --Bug 3319878 -- Changed the query to improve performance
3212
3213 CURSOR ptax_ca_cur IS
3214 SELECT distinct ptax.jurisdiction_code
3215 FROM pay_us_modified_geocodes pmod,
3216 pay_taxability_rules ptax
3217 WHERE pmod.state_code = 'CA'
3218 AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
3219 AND pmod.patch_name = p_patch_name
3220 AND ptax.legislation_code = 'CA' ;
3221
3222
3223
3224
3225 ptax_ca_rec ptax_ca_cur%ROWTYPE;
3226
3227
3228
3229 jd_code pay_taxability_rules.jurisdiction_code%TYPE;
3230 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3231 l_error_message_text varchar2(240);
3232 l_count number;
3233 BEGIN
3234
3235 g_geo_phase_id := p_geo_phase_id;
3236 g_mode := p_mode;
3237
3238 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
3239 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3240
3241 FOR ptax_rec IN ptax_cur LOOP
3242
3243 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',1);
3244
3245 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3246 process_type
3247 INTO jd_code, l_proc_type
3248 FROM pay_us_modified_geocodes pmod
3249 WHERE pmod.state_code = substr(ptax_rec.jurisdiction_code,1,2)
3250 AND pmod.old_city_code = substr(ptax_rec.jurisdiction_code,8,4)
3251 AND pmod.process_type in ('UP','RP')
3252 AND pmod.patch_name = p_patch_name
3253 --city taxability rules don't carry a county-code so we have to pull the first
3254 -- row in the case of a city that spans a county.
3255 and rownum = 1;
3256
3257 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
3258
3259 select count(*) into l_count
3260 from pay_taxability_rules ptax
3261 where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
3262
3263 IF l_count = 0 THEN
3264
3265 IF G_MODE = 'UPGRADE' THEN
3266
3267 UPDATE pay_taxability_rules ptax
3268 SET ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||
3269 substr(jd_code,8,4)
3270 WHERE ptax.jurisdiction_code = ptax_rec.jurisdiction_code;
3271
3272 -- COMMIT;
3273
3274
3275 END IF;
3276
3277 END IF;
3278 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',3);
3279
3280 -- write to the message table so that if this fails unexpectedly we can track which taxability
3281 -- rules have been upgraded already.
3282
3283 write_message(
3284 p_proc_type => l_proc_type,
3285 p_person_id => null,
3286 p_assign_id => null,
3287 p_old_juri_code => ptax_rec.jurisdiction_code,
3288 p_new_juri_code => substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4),
3289 p_location => 'PAY_TAXABILITY_RULES',
3290 p_id => null);
3291
3292 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
3293
3294 END LOOP;
3295 --
3296 --
3297 --
3298 --Per bug 2996546
3299 --Update of pay_taxability_rules . jurisdiction_code
3300 --(Canadian Legislation)
3301
3302 OPEN ptax_ca_cur ;
3303 LOOP
3304 FETCH ptax_ca_cur INTO ptax_ca_rec;
3305 EXIT WHEN ptax_ca_cur%NOTFOUND;
3306
3307 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',15);
3308
3309 SELECT pmod.new_county_code,
3310 process_type
3311 INTO jd_code, l_proc_type
3312 FROM pay_us_modified_geocodes pmod
3313 WHERE pmod.state_code = 'CA'
3314 AND pmod.county_code = substr(ptax_ca_rec.jurisdiction_code,1,2)
3315 AND pmod.patch_name = p_patch_name;
3316
3317 IF G_MODE = 'UPGRADE' THEN
3318
3319 UPDATE pay_taxability_rules ptax
3320 SET ptax.jurisdiction_code = jd_code||'-000-'||'0000'
3321 WHERE ptax.jurisdiction_code = ptax_ca_rec.jurisdiction_code;
3322
3323 -- COMMIT;
3324
3325 END IF;
3326 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',20);
3327
3328 -- write to the message table so that if this fails unexpectedly we can track which taxability
3329 -- rules have been upgraded already.
3330
3331 write_message(
3332 p_proc_type => l_proc_type,
3333 p_person_id => null,
3334 p_assign_id => null,
3335 p_old_juri_code => ptax_ca_rec.jurisdiction_code,
3336 p_new_juri_code => jd_code||'-000-'||'0000',
3337 p_location => 'PAY_TAXABILITY_RULES',
3338 p_id => null);
3339
3340 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
3341
3342
3343 END LOOP;
3344 CLOSE ptax_ca_cur ;
3345 --
3346 --
3347 --
3348 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',5);
3349
3350 EXCEPTION
3351 WHEN OTHERS THEN
3352
3353 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3354 rollback;
3355 hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',6);
3356
3357 fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
3358 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3359
3360 raise_application_error(-20001,l_error_message_text);
3361
3362
3363 END update_taxability_rules;
3364
3365 -- This procedure is separate from the above main upgrade_geocodes
3366 -- This procedure will update the org_information1 column in the
3367 -- hr_organization_information table where the org_information_context
3368 -- is 'Local Tax Rules'
3369 -- This procedure will only be run by one process, NOT MULTIPLE TIMES
3370
3371 PROCEDURE update_org_info(P_GEO_PHASE_ID IN NUMBER,
3372 P_MODE IN VARCHAR2,
3373 P_PATCH_NAME IN VARCHAR2)
3374
3375 IS
3376
3377 --Retrieve all changed geocodes in the hr_organization_information table
3378
3379 CURSOR org_info_cur IS
3380 SELECT distinct org_information1
3381 FROM pay_us_modified_geocodes pmod,
3382 hr_organization_information hoi
3383 WHERE pmod.state_code = substr(hoi.org_information1,1,2)
3384 AND pmod.county_code = substr(hoi.org_information1,4,3)
3385 AND pmod.old_city_code = substr(hoi.org_information1,8,4)
3386 AND pmod.process_type in ('UP','PU','RP')
3387 AND pmod.patch_name = p_patch_name
3388 AND hoi.org_information_context = 'Local Tax Rules'
3389 AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3390 where pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
3391 and pugu.new_juri_code = hoi.org_information1
3392 and pugu.process_mode = g_mode
3393 and pugu.process_type = g_process_type
3394 and pugu.id = g_geo_phase_id);
3395
3396 org_info_rec org_info_cur%ROWTYPE;
3397
3398 --
3399 --
3400 --Per bug 2996546
3401 --Added a cursor org_info_ca_cur to the procedure update_org_info
3402 --Retrieve all changed org_information1 on hr_organization_information table.
3403 --and update (for Canadian Legislation)
3404 --
3405
3406 CURSOR org_info_ca_cur IS
3407 SELECT distinct hoi.org_information1, hoi.org_information_id
3408 FROM pay_us_modified_geocodes pmod,
3409 hr_organization_information hoi
3410 WHERE pmod.state_code = 'CA'
3411 AND pmod.county_code = substr(hoi.org_information1,1,2)
3412 AND pmod.patch_name = p_patch_name
3413 AND hoi.org_information_context in
3414 (
3415 'Prov Reporting Est',
3416 'Provincial Information',
3417 'Provincial Reporting Info.',
3418 'Provincial Employment Standard',
3419 'Workers Comp Info.'
3420 ) ;
3421 org_info_ca_rec org_info_ca_cur%ROWTYPE;
3422 --
3423 --
3424 new_geocode hr_organization_information.org_information1%TYPE;
3425 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3426 l_error_message_text varchar2(240);
3427
3428 BEGIN
3429
3430 g_geo_phase_id := p_geo_phase_id;
3431 g_mode := p_mode;
3432
3433 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
3434 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3435
3436 FOR org_info_rec IN org_info_cur LOOP
3437
3438 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',1);
3439
3440 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
3441 process_type
3442 INTO new_geocode, l_proc_type
3443 FROM pay_us_modified_geocodes pmod
3444 WHERE pmod.state_code = substr(org_info_rec.org_information1,1,2)
3445 AND pmod.county_code = substr(org_info_rec.org_information1,4,3)
3446 AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
3447 AND pmod.process_type in ('UP','PU','RP','U')
3448 AND pmod.patch_name = p_patch_name;
3449
3450 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',2);
3451
3452 IF G_MODE = 'UPGRADE' THEN
3453
3454 UPDATE hr_organization_information
3455 SET org_information1 = new_geocode
3456 WHERE org_information1 = org_info_rec.org_information1
3457 AND org_information_context = 'Local Tax Rules';
3458
3459 -- COMMIT;
3460
3461 END IF;
3462
3463 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',3);
3464
3465 -- write to the message table so that if this fails unexpectedly we can track which taxability
3466 -- rules have been upgraded already.
3467
3468 write_message(
3469 p_proc_type => l_proc_type,
3470 p_person_id => null,
3471 p_assign_id => null,
3472 p_old_juri_code => org_info_rec.org_information1,
3473 p_new_juri_code => new_geocode,
3474 p_location => 'HR_ORGANIZATION_INFORMATION',
3475 p_id => null);
3476
3477 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
3478
3479 END LOOP;
3480
3481 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
3482 --
3483 --
3484 --
3485
3486 --Per bug 2996546
3487 --Update of hr_organization_information . org_information1
3488 --(Canadian Legislation)
3489
3490 OPEN org_info_ca_cur;
3491 LOOP
3492 FETCH org_info_ca_cur into org_info_ca_rec;
3493 EXIT WHEN org_info_ca_cur%NOTFOUND;
3494
3495 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
3496
3497
3498 SELECT pmod.new_county_code,
3499 process_type
3500 INTO new_geocode, l_proc_type
3501 FROM pay_us_modified_geocodes pmod
3502 WHERE pmod.state_code = 'CA'
3503 AND pmod.county_code = substr(org_info_ca_rec.org_information1,1,2)
3504 AND pmod.patch_name = p_patch_name;
3505
3506 IF G_MODE = 'UPGRADE' THEN
3507
3508 UPDATE hr_organization_information
3509 SET org_information1 = new_geocode
3510 WHERE org_information1 = org_info_ca_rec.org_information1
3511 AND org_information_id = org_info_ca_rec.org_information_id
3512 AND org_information_context in
3513 (
3514 'Prov Reporting Est',
3515 'Provincial Information',
3516 'Provincial Reporting Info.',
3517 'Provincial Employment Standard',
3518 'Workers Comp Info.'
3519 ) ;
3520
3521 -- COMMIT;
3522
3523
3524 END IF;
3525 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
3526
3527 -- write to the message table so that if this fails unexpectedly
3528 --
3529
3530 write_message(
3531 p_proc_type => l_proc_type,
3532 p_person_id => null,
3533 p_assign_id => null,
3534 p_old_juri_code => org_info_rec.org_information1,
3535 p_new_juri_code => new_geocode,
3536 p_location => 'HR_ORGANIZATION_INFORMATION',
3537 p_id => null);
3538
3539 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
3540 END LOOP ;
3541 CLOSE org_info_ca_cur;
3542 --
3543 --
3544 --
3545
3546 EXCEPTION
3547 WHEN OTHERS THEN
3548 l_error_message_text := to_char(SQLCODE)||SQLERRM||' Program error contact support';
3549 rollback;
3550 hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',6);
3551
3552 fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
3553 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3554
3555 raise_application_error(-20001,l_error_message_text);
3556
3557 END update_org_info;
3558
3559
3560 -- This api is used to upgrade assignments with a process type of US or SU
3561
3562 PROCEDURE upgrade_geo_api(P_ASSIGN_ID NUMBER,
3563 P_PATCH_NAME VARCHAR2,
3564 P_MODE VARCHAR2,
3565 P_CITY_NAME VARCHAR2)
3566 IS
3567
3568 -- Bug 3319878 -- Changed the query to remove FTS from PAY_US_GEO_UPDATE
3569
3570 CURSOR chk_last_api(p_geo_phase_id NUMBER, p_mode VARCHAR2) IS
3571 SELECT 'Y'
3572 FROM dual
3573 WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
3574 FROM PAY_US_GEO_UPDATE pugu
3575 WHERE pugu.id = p_geo_phase_id
3576 AND pugu.process_mode = p_mode
3577 AND pugu.table_name is null
3578 AND pugu.table_value_id is null
3579 AND pugu.status <> 'C'
3580 AND rownum < 2 );
3581
3582
3583
3584 l_chk_last_api varchar2(2);
3585
3586 CURSOR pay_patch_id(p_patch_name VARCHAR2) IS
3587 SELECT ID
3588 FROM pay_patch_status
3589 WHERE patch_name = p_patch_name;
3590
3591 l_id number;
3592
3593 BEGIN
3594
3595 hr_utility.trace('Entering the Geocode Upgrade API');
3596
3597
3598 OPEN pay_patch_id(p_patch_name);
3599 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',1);
3600
3601 FETCH pay_patch_id INTO l_id;
3602 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',5);
3603
3604 CLOSE pay_patch_id;
3605
3606 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',10);
3607
3608 IF p_mode = 'UPGRADE' THEN
3609
3610 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',15);
3611
3612 upgrade_geocodes(P_ASSIGN_START => p_assign_id,
3613 P_ASSIGN_END => p_assign_id,
3614 P_GEO_PHASE_ID => l_id,
3615 P_MODE => 'UPGRADE',
3616 P_PATCH_NAME => p_patch_name,
3617 P_CITY_NAME => p_city_name,
3618 P_API_MODE => 'Y');
3619
3620 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',20);
3621
3622 ELSE
3623
3624 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',25);
3625
3626 upgrade_geocodes(P_ASSIGN_START => p_assign_id,
3627 P_ASSIGN_END => p_assign_id,
3628 P_GEO_PHASE_ID => l_id,
3629 P_MODE => 'DEBUG',
3630 P_PATCH_NAME => p_patch_name,
3631 P_CITY_NAME => p_city_name,
3632 P_API_MODE => 'Y');
3633
3634 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',30);
3635
3636 END IF;
3637
3638 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',35);
3639
3640 OPEN chk_last_api(l_id, p_mode);
3641
3642 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',40);
3643
3644 FETCH chk_last_api INTO l_chk_last_api;
3645
3646 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',45);
3647
3648 IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
3649
3650 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
3651
3652 UPDATE pay_patch_status
3653 SET status = 'C', phase = null
3654 WHERE id = l_id;
3655 hr_utility.set_location('before commit ',4);
3656
3657 -- commit;
3658
3659 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',55);
3660
3661 END IF;
3662
3663 hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',60);
3664
3665 CLOSE chk_last_api;
3666
3667 hr_utility.trace('Exiting the Geocode Upgrade API');
3668
3669 EXCEPTION
3670 WHEN OTHERS THEN
3671 hr_utility.trace(to_char(SQLCODE)||SQLERRM||'Program error contact support');
3672
3673
3674 END upgrade_geo_api;
3675
3676 --
3677 --Per bug 2996546 created a public function
3678 --to return pay_input_values_f.input_value_id
3679 --after comparing values stored in a pl/sql table
3680 --
3681
3682 Function IS_US_OR_CA_LEGISLATION
3683 (p_input_value_id in pay_input_values_f.input_value_id%TYPE)
3684 Return pay_input_values_f.input_value_id%TYPE Is
3685
3686 Begin
3687 for l_number in 1..l_total
3688 loop
3689 If (input_val_cur(l_number) = p_input_value_id) THEN
3690 Return (p_input_value_id);
3691 End If;
3692 End loop;
3693 Return (0);
3694 End IS_US_OR_CA_LEGISLATION ;
3695
3696 --
3697 --
3698 --
3699 --Per bug 2996546,Added a new procedure update_ca_emp_info
3700 --to update pay_ca_emp_fed_tax_info_f.employment_province,
3701 --pay_ca_emp_prov_tax_info_f.province_code,
3702 --pay_ca_legislation_info.jurisdiction_code
3703 --
3704 --
3705 PROCEDURE update_ca_emp_info (P_GEO_PHASE_ID IN NUMBER,
3706 P_MODE IN VARCHAR2,
3707 P_PATCH_NAME IN VARCHAR2)
3708
3709 IS
3710 CURSOR canada_emp_fed_tax_cur IS
3711 SELECT distinct cafed.employment_province, cafed.assignment_id
3712 FROM pay_ca_emp_fed_tax_info_f cafed,
3713 pay_us_modified_geocodes pmod
3714 WHERE pmod.state_code = 'CA'
3715 AND pmod.county_code = cafed.employment_province
3716 AND pmod.patch_name = p_patch_name;
3717
3718 canada_emp_fed_rec canada_emp_fed_tax_cur%ROWTYPE;
3719
3720 CURSOR canada_emp_prov_tax_cur IS
3721 SELECT distinct caprov.province_code, caprov.assignment_id
3722 FROM pay_ca_emp_prov_tax_info_f caprov,
3723 pay_us_modified_geocodes pmod
3724 WHERE pmod.state_code = 'CA'
3725 AND pmod.county_code = caprov.province_code
3726 AND pmod.patch_name = p_patch_name;
3727
3728 canada_emp_prov_rec canada_emp_prov_tax_cur%ROWTYPE;
3729
3730 CURSOR canada_leg_info_cur IS
3731 SELECT distinct caleg.jurisdiction_code
3732 FROM pay_ca_legislation_info caleg,
3733 pay_us_modified_geocodes pmod
3734 WHERE pmod.state_code = 'CA'
3735 AND pmod.county_code = caleg.jurisdiction_code
3736 AND pmod.patch_name = p_patch_name ;
3737
3738 canada_leg_info_rec canada_leg_info_cur%ROWTYPE;
3739
3740
3741
3742 new_geocode pay_ca_emp_fed_tax_info_f .employment_province%TYPE;
3743 new_geocode1 pay_ca_emp_prov_tax_info_f.province_code%TYPE;
3744 new_geocode2 pay_ca_legislation_info. jurisdiction_code%TYPE;
3745 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
3746 l_error_message_text varchar2(240);
3747
3748 BEGIN
3749
3750 g_geo_phase_id := p_geo_phase_id;
3751 g_mode := p_mode;
3752
3753
3754 hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
3755 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
3756
3757 OPEN canada_emp_fed_tax_cur ;
3758 LOOP
3759 FETCH canada_emp_fed_tax_cur into canada_emp_fed_rec;
3760 EXIT WHEN canada_emp_fed_tax_cur%NOTFOUND;
3761
3762 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',1);
3763 SELECT pmod.new_county_code,
3764 pmod.process_type
3765 INTO new_geocode, l_proc_type
3766 FROM pay_us_modified_geocodes pmod
3767 WHERE pmod.state_code = 'CA'
3768 AND pmod.county_code = canada_emp_fed_rec.employment_province
3769 AND pmod.patch_name = p_patch_name;
3770
3771 IF G_MODE = 'UPGRADE' THEN
3772
3773
3774 UPDATE pay_ca_emp_fed_tax_info_f
3775 SET employment_province = new_geocode
3776 WHERE employment_province = canada_emp_fed_rec.employment_province
3777 AND assignment_id = canada_emp_fed_rec.assignment_id ;
3778
3779 -- COMMIT;
3780
3781
3782 END IF;
3783
3784
3785 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',2);
3786 -- write to the message table so that if this fails unexpectedly
3787 write_message(
3788 p_proc_type => l_proc_type,
3789 p_person_id => null,
3790 p_assign_id => canada_emp_fed_rec.assignment_id,
3791 p_old_juri_code => canada_emp_fed_rec.employment_province,
3792 p_new_juri_code => new_geocode,
3793 p_location => 'PAY_CA_EMP_FED_TAX_INFO_F',
3794 p_id => null);
3795
3796 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
3797
3798 END LOOP ;
3799 CLOSE canada_emp_fed_tax_cur ;
3800
3801 OPEN canada_emp_prov_tax_cur ;
3802 LOOP
3803 FETCH canada_emp_prov_tax_cur into canada_emp_prov_rec;
3804 EXIT WHEN canada_emp_prov_tax_cur%NOTFOUND;
3805
3806 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',4);
3807
3808 SELECT pmod.new_county_code,
3809 pmod.process_type
3810 INTO new_geocode1, l_proc_type
3811 FROM pay_us_modified_geocodes pmod
3812 WHERE pmod.state_code = 'CA'
3813 AND pmod.county_code = canada_emp_prov_rec.province_code
3814 AND pmod.patch_name = p_patch_name;
3815
3816
3817 IF G_MODE = 'UPGRADE' THEN
3818
3819
3820 UPDATE pay_ca_emp_prov_tax_info_f
3821 SET province_code = new_geocode1
3822 WHERE province_code = canada_emp_prov_rec.province_code
3823 AND assignment_id = canada_emp_prov_rec.assignment_id ;
3824
3825 -- COMMIT;
3826
3827
3828 END IF;
3829
3830
3831 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',5);
3832 -- write to the message table so that if this fails unexpectedly
3833 write_message(
3834 p_proc_type => l_proc_type,
3835 p_person_id => null,
3836 p_assign_id => canada_emp_prov_rec.assignment_id,
3837 p_old_juri_code => canada_emp_prov_rec.province_code,
3838 p_new_juri_code => new_geocode1,
3839 p_location => 'PAY_CA_EMP_PROV_TAX_INFO_F',
3840 p_id => null);
3841
3842 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
3843 END LOOP ;
3844 CLOSE canada_emp_prov_tax_cur ;
3845
3846
3847
3848 OPEN canada_leg_info_cur;
3849 LOOP
3850 FETCH canada_leg_info_cur into canada_leg_info_rec ;
3851 EXIT WHEN canada_leg_info_cur%NOTFOUND;
3852
3853 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',7);
3854 SELECT pmod.new_county_code,
3855 pmod.process_type
3856 INTO new_geocode2, l_proc_type
3857 FROM pay_us_modified_geocodes pmod
3858 WHERE pmod.state_code = 'CA'
3859 AND pmod.county_code = canada_leg_info_rec.jurisdiction_code
3860 AND pmod.patch_name = p_patch_name;
3861
3862 IF G_MODE = 'UPGRADE' THEN
3863
3864 UPDATE pay_ca_legislation_info
3865 SET jurisdiction_code = new_geocode2
3866 WHERE jurisdiction_code = canada_leg_info_rec.jurisdiction_code ;
3867 -- COMMIT;
3868
3869
3870 END IF;
3871
3872 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',8);
3873 -- write to the message table so that if this fails unexpectedly
3874 write_message(
3875 p_proc_type => l_proc_type,
3876 p_person_id => null,
3877 p_assign_id => null,
3878 p_old_juri_code => canada_leg_info_rec.jurisdiction_code,
3879 p_new_juri_code => new_geocode2,
3880 p_location => 'PAY_CA_LEGISLATION_INFO',
3881 p_id => null);
3882
3883 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
3884 END LOOP ;
3885
3886 CLOSE canada_leg_info_cur;
3887
3888 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',10);
3889 EXCEPTION
3890 WHEN OTHERS THEN
3891 l_error_message_text := to_char(SQLCODE)||SQLERRM||
3892 ' Program error contact support';
3893 rollback;
3894 hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',11);
3895
3896 fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
3897 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
3898
3899 raise_application_error(-20001,l_error_message_text);
3900
3901 hr_utility.set_location('before commit ',5);
3902 -- commit;
3903 END update_ca_emp_info ;
3904 --
3905 --
3906 --
3907 --
3908 --
3909 --
3910 --Per bug 2996546,Created a new procedure group_level_balance to
3911 --update pay_run_balances.jurisdiction_code
3912 --for group level balances (both US and Canadian
3913 --legislation)
3914 --
3915 --
3916 --
3917 PROCEDURE group_level_balance (P_START_PAYROLL_ACTION IN NUMBER,
3918 P_END_PAYROLL_ACTION IN NUMBER,
3919 P_GEO_PHASE_ID IN NUMBER,
3920 P_MODE IN VARCHAR2,
3921 P_PATCH_NAME IN VARCHAR2)
3922 IS
3923
3924 /* Bug# 3679984 Forced the index PAY_US_MODIFIED_GEOCODES_PK on pay_us_modified_geocodes
3925 and rearranged the order of the where clause in the subquery */
3926
3927 /* Bug 4773276 Changing the hint to PAY_US_MODIFIED_GEOCODES_N1 */
3928
3929 CURSOR group_level_bal_us (c_payroll_action_id number) IS
3930 select
3931 prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
3932 from pay_run_balances prb, pay_us_modified_geocodes pmod
3933 Where prb.payroll_action_id = c_payroll_action_id
3934 --between p_start_payroll_action and p_end_payroll_action
3935 and prb.assignment_id is null
3936 and pmod.state_code = substr(prb.jurisdiction_code,1,2)
3937 and pmod.county_code = substr(prb.jurisdiction_code,4,3)
3938 and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
3939 and pmod.process_type in ('PU', 'UP')
3940 and pmod.patch_name = p_patch_name;
3941
3942 /* and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3943 where pugu.process_type = g_process_type
3944 and pugu.process_mode = g_mode
3945 and pugu.assignment_id is null
3946 and pugu.old_juri_code = prb.jurisdiction_code
3947 and pugu.person_id = prb.run_balance_id
3948 and pugu.table_name = 'PAY_RUN_BALANCES'
3949 and pugu.id = g_geo_phase_id);*/
3950
3951 /* select /*+ ORDERED
3952 index(pmod PAY_US_MODIFIED_GEOCODES_N1)
3953 USE_NL(prb pdb pbd pmod) */
3954 /* prb.run_balance_id,
3955 prb.jurisdiction_code,
3956 prb.jurisdiction_comp3
3957 from pay_run_balances prb,
3958 pay_defined_balances pdb,
3959 pay_balance_dimensions pbd,
3960 pay_us_modified_geocodes pmod
3961 Where prb.payroll_action_id = c_payroll_action_id
3962 --between p_start_payroll_action and p_end_payroll_action
3963 and prb.assignment_id is null
3964 and prb.defined_balance_id = pdb.defined_balance_id
3965 and pdb.balance_dimension_id = pbd.balance_dimension_id
3966 and pbd.dimension_level = 'GRP'
3967 and pdb.legislation_code = 'US'
3968 and pbd.database_item_suffix like '%JD%'
3969 and pmod.state_code = substr(prb.jurisdiction_code,1,2)
3970 and pmod.county_code = substr(prb.jurisdiction_code,4,3)
3971 and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
3972 and pmod.patch_name = p_patch_name
3973 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3974 where pugu.process_type = g_process_type
3975 and pugu.process_mode = g_mode
3976 and pugu.assignment_id is null
3977 and pugu.old_juri_code = pmod.state_code || '-' || pmod.county_code || '-' || pmod.old_city_code --prb.jurisdiction_code
3978 and pugu.person_id = prb.payroll_action_id
3979 and pugu.table_name = 'PAY_RUN_BALANCES'
3980 and pugu.id = g_geo_phase_id);
3981 */
3982 group_level_bal_us_rec group_level_bal_us%ROWTYPE;
3983
3984
3985 CURSOR group_level_bal_ca (c_payroll_action_id number) IS
3986 select
3987 prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
3988 from pay_run_balances prb, pay_us_modified_geocodes pmod
3989 Where prb.payroll_action_id = c_payroll_action_id
3990 --between p_start_payroll_action and p_end_payroll_action
3991 and prb.assignment_id is null
3992 and pmod.state_code = 'CA'
3993 and pmod.county_code = substr(prb.jurisdiction_code,1,2)
3994 and pmod.process_type in ('PU', 'UP')
3995 and pmod.patch_name = p_patch_name;
3996
3997 /*
3998 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
3999 where pugu.old_juri_code = prb.jurisdiction_code
4000 and pugu.assignment_id is null
4001 and pugu.person_id = prb.run_balance_id
4002 and pugu.table_name = 'PAY_RUN_BALANCES'
4003 and pugu.process_mode = g_mode
4004 and pugu.process_type = g_process_type
4005 and pugu.id = g_geo_phase_id); */
4006
4007 /*select /*+ ORDERED
4008 index(pmod PAY_US_MODIFIED_GEOCODES_N1)
4009 USE_NL(prb pdb pbd pmod) */
4010 /* prb.run_balance_id,
4011 prb.jurisdiction_code,
4012 prb.jurisdiction_comp3
4013 from pay_run_balances prb,
4014 pay_defined_balances pdb,
4015 pay_balance_dimensions pbd,
4016 pay_us_modified_geocodes pmod
4017 Where prb.payroll_action_id = c_payroll_action_id
4018 --between p_start_payroll_action and p_end_payroll_action
4019 and prb.assignment_id is null
4020 and prb.defined_balance_id = pdb.defined_balance_id
4021 and pdb.balance_dimension_id = pbd.balance_dimension_id
4022 and pbd.dimension_level = 'GRP'
4023 and pdb.legislation_code = 'CA'
4024 and pbd.database_item_suffix like '%JD%'
4025 and pmod.state_code = 'CA'
4026 and pmod.county_code = substr(prb.jurisdiction_code,1,2)
4027 and pmod.patch_name = p_patch_name
4028 and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
4029 where pugu.old_juri_code = prb.jurisdiction_code
4030 and pugu.assignment_id is null
4031 and pugu.person_id = prb.payroll_action_id
4032 and pugu.table_name = 'PAY_RUN_BALANCES'
4033 and pugu.process_mode = g_mode
4034 and pugu.process_type = g_process_type
4035 and pugu.id = g_geo_phase_id); */
4036
4037 CURSOR c_legislation_code
4038 (
4039 c_start_pactid number,
4040 c_end_pactid number
4041 ) is
4042 select pbg.legislation_code,
4043 ppa.payroll_action_id
4044 from per_business_groups pbg, pay_payroll_actions ppa
4045 Where ppa.payroll_action_id between c_start_pactid and c_end_pactid
4046 and pbg.business_group_id = ppa.business_group_id;
4047
4048 group_level_bal_ca_rec group_level_bal_ca%ROWTYPE;
4049
4050
4051 l_proc_type pay_us_modified_geocodes.process_type%TYPE;
4052 l_geocode pay_run_balances.jurisdiction_code%TYPE;
4053 l_new_city_code pay_us_modified_geocodes.new_city_code%TYPE;
4054 l_legislation_code per_business_groups.legislation_code%TYPE;
4055 l_pactid pay_payroll_actions.payroll_action_id%TYPE;
4056
4057 l_row_updated varchar2(1);
4058
4059 l_error_message_text varchar2(240);
4060
4061 BEGIN
4062
4063 g_geo_phase_id := p_geo_phase_id;
4064 g_mode := p_mode;
4065
4066 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance');
4067 hr_utility.trace('The phase id is: '||to_char(g_geo_phase_id));
4068
4069 OPEN c_legislation_code ( p_start_payroll_action
4070 ,p_end_payroll_action) ;
4071
4072 LOOP
4073 FETCH c_legislation_code into l_legislation_code,
4074 l_pactid;
4075 EXIT WHEN c_legislation_code%NOTFOUND;
4076
4077
4078
4079 If l_legislation_code = 'US' THEN
4080 OPEN group_level_bal_us (l_pactid);
4081 LOOP
4082 FETCH group_level_bal_us into group_level_bal_us_rec;
4083 EXIT WHEN group_level_bal_us%NOTFOUND;
4084
4085
4086 begin
4087
4088 l_row_updated := 'N';
4089
4090 select 'Y'
4091 into l_row_updated
4092 from PAY_US_GEO_UPDATE pugu
4093 where pugu.old_juri_code = group_level_bal_us_rec.jurisdiction_code
4094 and pugu.assignment_id is null
4095 and pugu.person_id = group_level_bal_us_rec.run_balance_id
4096 and pugu.table_name = 'PAY_RUN_BALANCES'
4097 and pugu.process_mode = g_mode
4098 and pugu.process_type = g_process_type
4099 and pugu.id = g_geo_phase_id;
4100
4101 exception
4102
4103 when no_data_found then
4104
4105 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',1);
4106 SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
4107 process_type, pmod.new_city_code
4108 INTO l_geocode, l_proc_type, l_new_city_code
4109 FROM pay_us_modified_geocodes pmod
4110 WHERE pmod.state_code = substr(group_level_bal_us_rec.jurisdiction_code,1,2)
4111 AND pmod.county_code = substr(group_level_bal_us_rec.jurisdiction_code,4,3)
4112 AND pmod.old_city_code = substr(group_level_bal_us_rec.jurisdiction_code,8,4)
4113 -- AND pmod.process_type in ('UP','PU','RP','U','US','D','SU')
4114 AND pmod.patch_name = p_patch_name;
4115
4116 IF G_MODE = 'UPGRADE' THEN
4117
4118 UPDATE pay_run_balances
4119 SET jurisdiction_code = l_geocode,
4120 jurisdiction_comp3 = l_new_city_code
4121 WHERE payroll_action_id = group_level_bal_us_rec.run_balance_id
4122 -- AND jurisdiction_comp3 = group_level_bal_us_rec.jurisdiction_comp3
4123 AND jurisdiction_code = group_level_bal_us_rec.jurisdiction_code;
4124
4125 -- COMMIT;
4126 END IF;
4127
4128
4129 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',2);
4130 -- write to the message table so that if this fails unexpectedly
4131 write_message(
4132 p_proc_type => l_proc_type,
4133 p_person_id => group_level_bal_us_rec.run_balance_id,
4134 p_assign_id => null,
4135 p_old_juri_code => group_level_bal_us_rec.jurisdiction_code,
4136 p_new_juri_code => l_geocode,
4137 p_location => 'PAY_RUN_BALANCES',
4138 p_id => null);
4139
4140 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',3);
4141
4142 end;
4143
4144 END LOOP ;
4145 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7001');
4146
4147 CLOSE group_level_bal_us ;
4148
4149 else
4150
4151 OPEN group_level_bal_ca (l_pactid);
4152 LOOP
4153 FETCH group_level_bal_ca into group_level_bal_ca_rec;
4154 EXIT WHEN group_level_bal_ca%NOTFOUND;
4155
4156
4157 begin
4158
4159 l_row_updated := 'N';
4160
4161 select 'Y'
4162 into l_row_updated
4163 from PAY_US_GEO_UPDATE pugu
4164 where pugu.old_juri_code = group_level_bal_ca_rec.jurisdiction_code
4165 and pugu.assignment_id is null
4166 and pugu.person_id = group_level_bal_ca_rec.run_balance_id
4167 and pugu.table_name = 'PAY_RUN_BALANCES'
4168 and pugu.process_mode = g_mode
4169 and pugu.process_type = g_process_type
4170 and pugu.id = g_geo_phase_id;
4171
4172 exception
4173
4174 when no_data_found then
4175
4176 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7002');
4177 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',4);
4178 SELECT pmod.new_county_code, pmod.process_type
4179 INTO l_geocode, l_proc_type
4180 FROM pay_us_modified_geocodes pmod
4181 WHERE pmod.state_code = 'CA'
4182 -- AND pmod.county_code = group_level_bal_ca_rec.jurisdiction_code
4183 AND pmod.county_code = substr(group_level_bal_ca_rec.jurisdiction_code,1,2)
4184 AND pmod.patch_name = p_patch_name;
4185
4186 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7003');
4187
4188 IF G_MODE = 'UPGRADE' THEN
4189
4190 UPDATE pay_run_balances
4191 SET jurisdiction_code = l_geocode
4192 WHERE payroll_action_id = group_level_bal_ca_rec.run_balance_id
4193 -- AND jurisdiction_comp3 = group_level_bal_ca_rec.jurisdiction_comp3
4194 -- AND jurisdiction_code = group_level_bal_ca_rec.jurisdiction_code
4195 AND substr(jurisdiction_code,1,2) =
4196 substr(group_level_bal_ca_rec.jurisdiction_code,1,2) ;
4197
4198 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7004');
4199
4200 -- COMMIT;
4201 END IF;
4202
4203 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',5);
4204 -- write to the message table so that if this fails unexpectedly
4205 write_message(
4206 p_proc_type => l_proc_type,
4207 p_person_id => group_level_bal_ca_rec.run_balance_id,
4208 p_assign_id => null,
4209 p_old_juri_code => group_level_bal_ca_rec.jurisdiction_code,
4210 p_new_juri_code => l_geocode,
4211 p_location => 'PAY_RUN_BALANCES',
4212 p_id => null);
4213
4214 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',6);
4215
4216 end;
4217
4218 END LOOP ;
4219
4220 CLOSE group_level_bal_ca;
4221
4222 END IF; --l_legislation_code
4223
4224 END LOOP;
4225
4226 CLOSE c_legislation_code;
4227
4228 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',7);
4229 EXCEPTION
4230 WHEN OTHERS THEN
4231 l_error_message_text := to_char(SQLCODE)||SQLERRM||
4232 ' Program error contact support';
4233
4234 hr_utility.trace('Entering pay_us_geo_upd_pkg. group_level_balance - 7005');
4235 hr_utility.trace('l_error_message_text - ' ||l_error_message_text);
4236
4237
4238 fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
4239 fnd_file.put_line(fnd_file.log, 'sql error ' || sqlcode || ' - ' || substr(sqlerrm,1,80));
4240
4241
4242 rollback;
4243
4244
4245
4246 hr_utility.set_location('pay_us_geo_upd_pkg. group_level_balance',8);
4247 raise_application_error(-20001,l_error_message_text);
4248
4249 hr_utility.set_location('before commit ',6);
4250 -- commit;
4251 END group_level_balance ;
4252 --
4253
4254 END pay_us_geo_upd_pkg;
[Home] [Help]