[Home] [Help]
PACKAGE BODY: APPS.PAY_US_GEOCODE_ENGINE_PKG
Source
1 package body PAY_US_GEOCODE_ENGINE_PKG as
2 /* $Header: pyusgeom.pkb 120.9.12020000.3 2012/07/17 03:57:20 emunisek ship $ */
3
4 procedure GEOCODE_UPGRADE(errbuf OUT nocopy VARCHAR2,
5 retcode OUT nocopy NUMBER,
6 p_mode in VARCHAR2 )
7 is
8 --
9 --
10 cursor c_max_geocode_patch_name is
11 select patch_name,
12 Substr( patch_name,
13 greatest(instr(patch_name,'_1'),instr(patch_name,'_2')) + 1, 4)
14 from pay_patch_status
15 where patch_name like '%GEO%'
16 and Substr( patch_name,
17 greatest(instr(patch_name,'_1'),instr(patch_name,'_2')) + 1, 4) >= '2001'
18 group by patch_name
19 order by patch_name desc;
20
21
22 cursor c_geocode_patch_name is
23 select patch_name,
24 Substr( patch_name,
25 greatest(instr(patch_name,'_1'),instr(patch_name,'_2')) + 1, 4)
26 from pay_us_modified_geocodes
27 where patch_name like '%GEO%'
28 and Substr( patch_name,
29 greatest(instr(patch_name,'_1'),instr(patch_name,'_2')) + 1, 4) >= '2001'
30 group by patch_name
31 order by patch_name asc;
32
33 cursor c_geocode_patch_status (p_patch_name in varchar)
34 is
35 select status
36 from pay_patch_status
37 where patch_name = p_patch_name
38 order by patch_name asc;
39
40 cursor c_geo_patch_comp_stat (p_patch_name in varchar)
41 is
42 select status
43 from pay_patch_status
44 where patch_name = p_patch_name;
45
46 /* Modified below cursor to make sure that the City and County Name changes
47 are not considering while checking for the Geocode Changes to be processed.
48 This will save unnecessary execution of complete Geocode procedures in
49 presence of only City Name and County Name changes. If only City Name
50 and County Name changes are present, then the "Geocode Upgrade Concurrent Program"
51 Request will be skipped for that year and individual procedures
52 pay_us_geo_upd_pkg.update_county_name and pay_us_geo_upd_pkg.update_city_name
53 will take care of the County Name and City Name updates */
54
55 cursor c_geo_check_us_assignments (p_patch_name in varchar)
56 is
57 select count (tr.assignment_id)
58 from pay_us_modified_geocodes mg,
59 pay_us_emp_city_tax_rules_f tr,
60 per_all_assignments_f paf,
61 pay_us_states pus
62 where mg.patch_name = p_patch_name
63 and mg.state_code = pus.state_code
64 and mg.state_code = tr.state_code
65 and mg.county_code = tr.county_code
66 and mg.old_city_code = tr.city_code
67 and mg.process_type not in ('CN','CY','NC')
68 and tr.assignment_id = paf.assignment_id;
69
70
71 cursor c_geo_check_ca_assignments (p_patch_name in varchar)
72 is
73 select count(pac.assignment_id)
74 from per_all_assignments_f paf,
75 pay_action_contexts pac,
76 pay_us_modified_geocodes pmod
77 where pmod.state_code = '70'
78 and pmod.county_code = pac.context_value
79 and pac.context_id in (select context_id
80 from ff_contexts
81 where context_name = 'jurisdiction_code')
82 and pmod.patch_name = p_patch_name
83 and pac.assignment_id = paf.assignment_id;
84
85 cursor c_geo_county_change_adds (p_patch_name in varchar)
86 is
87 select count(person_id)
88 from per_addresses pa,
89 pay_us_modified_geocodes pmod,
90 pay_us_states pus
91 where pmod.state_code = pus.state_code
92 and (( pa.country = decode(pus.state_code,70,'CA','US')
93 and pus.state_abbrev = pa.region_2
94 and pmod.city_name = pa.region_1 )
95 or
96 ( pus.state_abbrev = pa.add_information17
97 and pmod.city_name = pa.add_information19 ))
98 and pmod.process_type = 'CN'
99 and pmod.patch_name = p_patch_name;
100
101 cursor c_geo_county_change_locs (p_patch_name in varchar)
102 is
103 select count(location_id)
104 from hr_locations_all hl,
105 pay_us_modified_geocodes pmod,
106 pay_us_states pus
107 where pmod.state_code = pus.state_code
108 and (( hl.country = decode(pus.state_code,70,'CA','US')
109 and pus.state_abbrev = hl.region_2
110 and pmod.city_name = hl.region_1 )
111 or
112 ( pus.state_abbrev = hl.loc_information17
113 and pmod.city_name = hl.loc_information19 ))
114 and pmod.process_type = 'CN'
115 and pmod.patch_name = p_patch_name;
116
117 cursor c_geo_city_changes (p_patch_name in varchar)
118 is
119 select count(*)
120 from pay_us_modified_geocodes pmod
121 where pmod.patch_name = p_patch_name
122 and pmod.process_type = 'CY';
123
124 list_index number:=1;
125 start_index number:=1;
126 end_index number:=1;
127 l_req_id number;
128 copies_buffer varchar2(80) := null;
129 print_buffer varchar2(80) := null;
130 printer_buffer varchar2(80) := null;
131 style_buffer varchar2(80) := null;
132 save_buffer boolean := null;
133 save_result varchar2(1) := null;
134 req_id varchar2(80) := null;
135 x boolean;
136 x1 boolean;
137
138 l_patch_name Pay_Patch_status.patch_name%type;
139 l_patch_status Pay_Patch_status.status%type;
140 l_max_patch_name Pay_Patch_status.patch_name%type;
141 l_completion_patch_status Pay_Patch_status.status%type;
142 l_year varchar2(4);
143 l_max_year varchar2(4);
144
145 l_phase varchar2(10);
146 l_status varchar2(10);
147 l_dev_phase varchar2(10);
148 l_dev_status varchar2(10);
149 l_message varchar2(240);
150 l_return_val boolean;
151 l_us_count varchar2(10);
152 l_ca_count varchar2(10);
153 l_county_changes number;
154 l_city_changes number;
155 l_id number;
156 concurrent_program_failure EXCEPTION;
157 --
158 --
159
160
161 begin
162
163 hr_utility.trace('Call User Defined City Upgrade Process ');
164
165 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
166 retcode := 0;
167
168 -- get printing info
169
170 req_id:=fnd_profile.value('CONC_REQUEST_ID');
171
172 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
173
174 if (print_buffer is NULL)
175 then
176 print_buffer:='N';
177 end if;
178
179 select number_of_copies,
180 printer,
181 print_style,
182 save_output_flag
183 into copies_buffer,
184 printer_buffer,
185 style_buffer,
186 save_result
187 from fnd_concurrent_requests
188 where request_id = fnd_number.canonical_to_number(req_id);
189
190 if (save_result='Y') then
191 save_buffer:=true;
192 elsif (save_result='N') then
193 save_buffer:=false;
194 else
195 save_buffer:=NULL;
196 end if;
197
198 hr_utility.trace('Calling set print options');
199
200 -- loop round from both ends working inwards
201 -- set print options
202 x:=FND_REQUEST.set_print_options(
203 printer => printer_buffer,
204 style => style_buffer,
205 copies => copies_buffer,
206 save_output => save_buffer,
207 print_together => print_buffer);
208
209 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
210
211 BEGIN
212
213 l_req_id:=fnd_request.submit_request(
214 application => 'PAY',
215 program => 'PAYUSUSR',
216 argument1 => 'ARCHIVE',
217 argument2 => 'US_USER_CITY_UPGRADE',
218 argument3 => 'DEFAULT',
219 argument4 => NULL,
220 argument5 => sysdate,
221 argument6 => 'REPORT',
222 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
223 argument8 => 'DUMMY',
224 argument9 => p_mode,
225 argument10 => 'MODE=' || p_mode,
226 argument11 => 'MODE=' || p_mode);
227
228 EXCEPTION
229
230 WHEN OTHERS THEN
231 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
232
233 END ;
234
235 COMMIT;
236
237 l_return_val := fnd_concurrent.wait_for_request(request_id => l_req_id,
238 phase => l_phase,
239 status => l_status,
240 dev_phase => l_dev_phase,
241 dev_status => l_dev_status,
242 message => l_message);
243
244 hr_utility.trace('Done with User Defined City Upgrade Process ');
245
246 open c_max_geocode_patch_name;
247 fetch c_max_geocode_patch_name into
248 l_max_patch_name,
249 l_max_year;
250
251 if c_max_geocode_patch_name%FOUND THEN
252
253 open c_geocode_patch_name;
254
255 loop
256
257 --hr_utility.trace_on(null,'ORACLE');
258
259 fetch c_geocode_patch_name
260 into l_patch_name,
261 l_year;
262
263 exit when c_geocode_patch_name%NOTFOUND;
264
265 hr_utility.trace('l_patch_name = ' || l_patch_name);
266 hr_utility.trace('l_year = ' || l_year);
267
268 --place the log info into the concurrent log file generated by application
269 fnd_file.put_line(fnd_file.log, l_patch_name||' '|| l_year);
270
271 -- check patch-status
272
273 l_patch_status := 'N';
274
275 open c_geocode_patch_status (l_patch_name);
276 fetch c_geocode_patch_status
277 into l_patch_status;
278
279 hr_utility.trace('l_patch_status = ' || l_patch_status);
280
281
282 --place the log info into the concurrent log file generated by application
283 --fnd_file.put_line(fnd_file.log, 'status = '|| nvl(l_patch_status,'null'));
284
285 if c_geocode_patch_status%NOTFOUND THEN
286 if l_year <= l_max_year THEN
287 l_patch_status := 'C';
288 else
289 l_patch_status := 'N';
290 end if;
291 end if;
292
293 close c_geocode_patch_status;
294
295 IF l_patch_status <> 'C' THEN
296
297
298 open c_geo_check_us_assignments(l_patch_name);
299
300 fetch c_geo_check_us_assignments
301 into l_us_count;
302
303 close c_geo_check_us_assignments;
304
305 open c_geo_check_ca_assignments(l_patch_name);
306
307 fetch c_geo_check_ca_assignments
308 into l_ca_count;
309
310 close c_geo_check_ca_assignments;
311
312 IF l_ca_count >0 or l_us_count > 0 THEN -- Bug 5599273
313
314 hr_utility.trace('Inside ');
315 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
316 retcode := 0;
317 -- get printing info
318 req_id:=fnd_profile.value('CONC_REQUEST_ID');
319 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
320 if (print_buffer is NULL)
321 then print_buffer:='N';
322 end if;
323
324 select number_of_copies,
325 printer,
326 print_style,
327 save_output_flag
328 into copies_buffer,
329 printer_buffer,
330 style_buffer,
331 save_result
332 from fnd_concurrent_requests
333 where request_id = fnd_number.canonical_to_number(req_id);
334
335
336 if (save_result='Y') then
337 save_buffer:=true;
338 elsif (save_result='N') then
339 save_buffer:=false;
340 else
341 save_buffer:=NULL;
342 end if;
343
344 hr_utility.trace(' call set print options');
345
346 -- get start of list
347 start_index:=1;
348 -- get end of list
349 end_index:=list_index-1;
350 -- loop round from both ends working inwards
351 -- set print options
352 x:=FND_REQUEST.set_print_options(
353 printer => printer_buffer,
354 style => style_buffer,
355 copies => copies_buffer,
356 save_output => save_buffer,
357 print_together => print_buffer);
358
359 -- Bug 3487186 Added by ssmukher
360 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
361
362 BEGIN
363
364 -- submit requests for report
365 l_req_id:=fnd_request.submit_request(
366 application => 'PAY',
367 program => 'PAYUSGEO',
368 argument1 => 'ARCHIVE',
369 argument2 => 'GEO_UPDATE',
370 argument3 => 'DEFAULT',
371 argument4 => to_date('01/01/'||l_year, 'dd/mm/yyyy'),
372 argument5 => sysdate,
373 argument6 => 'REPORT',
374 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
375 argument8 => 'DUMMY',
376 argument9 => p_mode, -- l_mode
377 argument10 => l_patch_name, -- patch_name
378 argument11 => 'PATCH_NAME=' || l_patch_name,
379 argument12 => 'MODE=' || p_mode );
380
381 EXCEPTION
382
383 WHEN OTHERS THEN
384
385 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
386
387 END ;
388
389 commit;
390
391 l_return_val := fnd_concurrent.wait_for_request(request_id => l_req_id,
392 -- max_wait => 300,
393 phase => l_phase,
394 status => l_status,
395 dev_phase => l_dev_phase,
396 dev_status => l_dev_status,
397 message => l_message);
398
399
400
401 ELSE
402 hr_utility.trace(' in ELSE');
403
404 select PAY_PATCH_STATUS_S.nextval into l_id
405 from dual;
406
407 insert into pay_patch_status
408 (ID,
409 PATCH_NUMBER,
410 PATCH_NAME,
411 PHASE,
412 PROCESS_TYPE,
413 APPLIED_DATE,
414 STATUS,
415 DESCRIPTION,
416 UPDATE_DATE,
417 LEGISLATION_CODE,
418 APPLICATION_RELEASE,
419 PREREQ_PATCH_NAME)
420 values
421 (l_id,
422 '1111111',
423 l_patch_name, --p_patch_name,
424 null,
425 null,
426 sysdate,
427 'C',
428 null, -- lv_patch_desc,
429 null,
430 'US',
431 '115',
432 'Q2' );
433
434 l_county_changes := 0;
435
436 open c_geo_county_change_locs(l_patch_name);
437 fetch c_geo_county_change_locs into l_county_changes;
438 close c_geo_county_change_locs;
439
440 IF l_county_changes = 0 THEN
441
442 open c_geo_county_change_adds(l_patch_name);
443 fetch c_geo_county_change_adds into l_county_changes;
444 close c_geo_county_change_adds;
445
446 END IF;
447
448 IF l_county_changes > 0 THEN
449
450 pay_us_geo_upd_pkg.update_county_name(l_id,p_mode,l_patch_name,'EXTERNAL');
451
452 END IF;
453
454 l_city_changes := 0;
455
456 open c_geo_city_changes(l_patch_name);
457 fetch c_geo_city_changes into l_city_changes;
458 close c_geo_city_changes;
459
460 IF l_city_changes > 0 THEN
461
462 pay_us_geo_upd_pkg.update_city_name(l_id,p_mode,l_patch_name,'EXTERNAL');
463
464 END IF;
465
466 END IF; --l_us_count IF
467 hr_utility.trace(' status complete patch_name = ' || l_patch_name );
468 open c_geo_patch_comp_stat(l_patch_name);
469
470 fetch c_geo_patch_comp_stat
471 into l_completion_patch_status;
472
473 hr_utility.trace('l_completion_patch_status = ' || l_completion_patch_status);
474
475
476 --place the log info into the concurrent log file generated by application
477 fnd_file.put_line(fnd_file.log, 'completion status = '|| l_completion_patch_status);
478
479 if c_geo_patch_comp_stat%NOTFOUND THEN
480 l_completion_patch_status := NULL;
481 end if;
482
483 close c_geo_patch_comp_stat;
484
485 IF nvl(l_completion_patch_status, 'E') <> 'C' THEN
486 RAISE concurrent_program_failure;
487 END IF;
488
489 if p_mode = 'DEBUG' then
490 update pay_patch_status
491 set patch_name = 'DEBUG_' || l_patch_name
492 where patch_name = l_patch_name;
493 end if;
494
495 END IF;
496 end loop;
497
498
499 close c_geocode_patch_name;
500
501 else -- max_geocode_patch_name%FOUND
502 -- Skip processing as the PREREQUSITE JIT patch has not been applied
503 hr_utility.trace('JIT patch has not been applied prior to running upgrade');
504
505 end if;
506
507 close c_max_geocode_patch_name;
508 --Modified for bug no 7419559
509 /*if p_mode = 'DEBUG' then
510 update pay_patch_status
511 set patch_name = 'DEBUG_' || l_patch_name
512 where patch_name = l_patch_name;
513 end if;*/
514
515 EXCEPTION
516 --
517 WHEN concurrent_program_failure THEN
518
519
520
521 fnd_message.set_name('PAY', 'HR_75258_GEOCODE_PATCH_ERROR');
522 fnd_message.set_token('PATCH_NAME', l_patch_name);
523 fnd_message.set_token('REQ_ID', ('o' || l_req_id || '.out') );
524
525 errbuf := hr_utility.get_message;
526 retcode := 2;
527 RAISE;
528 --
529
530
531 WHEN others THEN
532 --
533 -- Set up error message and return code.
534 --
535 hr_utility.trace('sqlerrm = ' || sqlerrm);
536 errbuf := sqlerrm;
537 retcode := 2;
538
539 end GEOCODE_UPGRADE;
540
541 end PAY_US_GEOCODE_ENGINE_PKG;
542