[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.5.12010000.2 2008/09/19 07:20:22 pannapur 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 cursor c_geo_check_us_assignments (p_patch_name in varchar)
47 is
48 select count (tr.assignment_id)
49 from pay_us_modified_geocodes mg,
50 pay_us_emp_city_tax_rules_f tr,
51 per_all_assignments_f paf,
52 pay_us_states pus
53 where mg.patch_name = p_patch_name
54 and mg.state_code = pus.state_code
55 and mg.state_code = tr.state_code
56 and mg.county_code = tr.county_code
57 and mg.old_city_code = tr.city_code
58 and tr.assignment_id = paf.assignment_id;
59
60
61 cursor c_geo_check_ca_assignments (p_patch_name in varchar)
62 is
63 select count(pac.assignment_id)
64 from per_all_assignments_f paf,
65 pay_action_contexts pac,
66 pay_us_modified_geocodes pmod
67 where pmod.state_code = '70'
68 and pmod.county_code = pac.context_value
69 and pac.context_id in (select context_id
70 from ff_contexts
71 where context_name = 'jurisdiction_code')
72 and pmod.patch_name = p_patch_name
73 and pac.assignment_id = paf.assignment_id;
74
75
76 list_index number:=1;
77 start_index number:=1;
78 end_index number:=1;
79 l_req_id number;
80 copies_buffer varchar2(80) := null;
81 print_buffer varchar2(80) := null;
82 printer_buffer varchar2(80) := null;
83 style_buffer varchar2(80) := null;
84 save_buffer boolean := null;
85 save_result varchar2(1) := null;
86 req_id varchar2(80) := null;
87 x boolean;
88 x1 boolean;
89
90 l_patch_name Pay_Patch_status.patch_name%type;
91 l_patch_status Pay_Patch_status.status%type;
92 l_max_patch_name Pay_Patch_status.patch_name%type;
93 l_completion_patch_status Pay_Patch_status.status%type;
94 l_year varchar2(4);
95 l_max_year varchar2(4);
96
97 l_phase varchar2(10);
98 l_status varchar2(10);
99 l_dev_phase varchar2(10);
100 l_dev_status varchar2(10);
101 l_message varchar2(240);
102 l_return_val boolean;
103 l_us_count varchar2(10);
104 l_ca_count varchar2(10);
105 concurrent_program_failure EXCEPTION;
106 --
107 --
108
109
110 begin
111
112 open c_max_geocode_patch_name;
113 fetch c_max_geocode_patch_name into
114 l_max_patch_name,
115 l_max_year;
116
117 if c_max_geocode_patch_name%FOUND THEN
118
119 open c_geocode_patch_name;
120
121 loop
122
123 --hr_utility.trace_on(null,'ORACLE');
124
125 fetch c_geocode_patch_name
126 into l_patch_name,
127 l_year;
128
129 exit when c_geocode_patch_name%NOTFOUND;
130
131 hr_utility.trace('l_patch_name = ' || l_patch_name);
132 hr_utility.trace('l_year = ' || l_year);
133
134 --place the log info into the concurrent log file generated by application
135 fnd_file.put_line(fnd_file.log, l_patch_name||' '|| l_year);
136
137 -- check patch-status
138
139 l_patch_status := 'N';
140
141 open c_geocode_patch_status (l_patch_name);
142 fetch c_geocode_patch_status
143 into l_patch_status;
144
145 hr_utility.trace('l_patch_status = ' || l_patch_status);
146
147
148 --place the log info into the concurrent log file generated by application
149 --fnd_file.put_line(fnd_file.log, 'status = '|| nvl(l_patch_status,'null'));
150
151 if c_geocode_patch_status%NOTFOUND THEN
152 if l_year <= l_max_year THEN
153 l_patch_status := 'C';
154 else
155 l_patch_status := 'N';
156 end if;
157 end if;
158
159 close c_geocode_patch_status;
160
161 IF l_patch_status <> 'C' THEN
162
163
164 open c_geo_check_us_assignments(l_patch_name);
165
166 fetch c_geo_check_us_assignments
167 into l_us_count;
168
169 close c_geo_check_us_assignments;
170
171 open c_geo_check_ca_assignments(l_patch_name);
172
173 fetch c_geo_check_ca_assignments
174 into l_ca_count;
175
176 close c_geo_check_ca_assignments;
177
178 IF l_ca_count >0 or l_us_count > 0 THEN -- Bug 5599273
179
180 hr_utility.trace('Inside ');
181 -- initialise variable - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
182 retcode := 0;
183 -- get printing info
184 req_id:=fnd_profile.value('CONC_REQUEST_ID');
185 print_buffer:=fnd_profile.value('CONC_PRINT_TOGETHER');
186 if (print_buffer is NULL)
187 then print_buffer:='N';
188 end if;
189
190 select number_of_copies,
191 printer,
192 print_style,
193 save_output_flag
194 into copies_buffer,
195 printer_buffer,
196 style_buffer,
197 save_result
198 from fnd_concurrent_requests
199 where request_id = fnd_number.canonical_to_number(req_id);
200
201
202 if (save_result='Y') then
203 save_buffer:=true;
204 elsif (save_result='N') then
205 save_buffer:=false;
206 else
207 save_buffer:=NULL;
208 end if;
209
210 hr_utility.trace(' call set print options');
211
212 -- get start of list
213 start_index:=1;
214 -- get end of list
215 end_index:=list_index-1;
216 -- loop round from both ends working inwards
217 -- set print options
218 x:=FND_REQUEST.set_print_options(
219 printer => printer_buffer,
220 style => style_buffer,
221 copies => copies_buffer,
222 save_output => save_buffer,
223 print_together => print_buffer);
224
225 -- Bug 3487186 Added by ssmukher
226 x1 := fnd_Request.USE_CURRENT_NOTIFICATION;
227
228 BEGIN
229
230 -- submit requests for report
231 l_req_id:=fnd_request.submit_request(
232 application => 'PAY',
233 program => 'PAYUSGEO',
234 argument1 => 'ARCHIVE',
235 argument2 => 'GEO_UPDATE',
236 argument3 => 'DEFAULT',
237 argument4 => to_date('01/01/'||l_year, 'dd/mm/yyyy'),
238 argument5 => sysdate,
239 argument6 => 'REPORT',
240 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
241 argument8 => 'DUMMY',
242 argument9 => p_mode, -- l_mode
243 argument10 => l_patch_name, -- patch_name
244 argument11 => 'PATCH_NAME=' || l_patch_name,
245 argument12 => 'MODE=' || p_mode );
246
247 EXCEPTION
248
249 WHEN OTHERS THEN
250
251 HR_UTILITY.TRACE('SQLERRM = ' || SQLERRM);
252
253 END ;
254
255 commit;
256
257 l_return_val := fnd_concurrent.wait_for_request(request_id => l_req_id,
258 -- max_wait => 300,
259 phase => l_phase,
260 status => l_status,
261 dev_phase => l_dev_phase,
262 dev_status => l_dev_status,
263 message => l_message);
264
265
266
267 ELSE
268 hr_utility.trace(' in ELSE');
269 insert into pay_patch_status
270 (ID,
271 PATCH_NUMBER,
272 PATCH_NAME,
273 PHASE,
274 PROCESS_TYPE,
275 APPLIED_DATE,
276 STATUS,
277 DESCRIPTION,
278 UPDATE_DATE,
279 LEGISLATION_CODE,
280 APPLICATION_RELEASE,
281 PREREQ_PATCH_NAME)
282 values
283 (PAY_PATCH_STATUS_S.nextval,
284 '1111111',
285 l_patch_name, --p_patch_name,
286 null,
287 null,
288 sysdate,
289 'C',
290 null, -- lv_patch_desc,
291 null,
292 'US',
293 '115',
294 'Q2' );
295 END IF; --l_us_count IF
296 hr_utility.trace(' status complete patch_name = ' || l_patch_name );
297 open c_geo_patch_comp_stat(l_patch_name);
298
299 fetch c_geo_patch_comp_stat
300 into l_completion_patch_status;
301
302 hr_utility.trace('l_completion_patch_status = ' || l_completion_patch_status);
303
304
305 --place the log info into the concurrent log file generated by application
306 fnd_file.put_line(fnd_file.log, 'completion status = '|| l_completion_patch_status);
307
308 if c_geo_patch_comp_stat%NOTFOUND THEN
309 l_completion_patch_status := NULL;
310 end if;
311
312 close c_geo_patch_comp_stat;
313
314 IF nvl(l_completion_patch_status, 'E') <> 'C' THEN
315 RAISE concurrent_program_failure;
316 END IF;
317
318 if p_mode = 'DEBUG' then
319 update pay_patch_status
320 set patch_name = 'DEBUG_' || l_patch_name
321 where patch_name = l_patch_name;
322 end if;
323
324 END IF;
325 end loop;
326
327
328 close c_geocode_patch_name;
329
330 else -- max_geocode_patch_name%FOUND
331 -- Skip processing as the PREREQUSITE JIT patch has not been applied
332 hr_utility.trace('JIT patch has not been applied prior to running upgrade');
333
334 end if;
335
336 close c_max_geocode_patch_name;
337
338 /*Modified for bug no 7421642
339 if p_mode = 'DEBUG' then
340 update pay_patch_status
341 set patch_name = 'DEBUG_' || l_patch_name
342 where patch_name = l_patch_name;
343 end if; */
344
345 EXCEPTION
346 --
347 WHEN concurrent_program_failure THEN
348
349
350
351 fnd_message.set_name('PAY', 'HR_75258_GEOCODE_PATCH_ERROR');
352 fnd_message.set_token('PATCH_NAME', l_patch_name);
353 fnd_message.set_token('REQ_ID', ('o' || l_req_id || '.out') );
354
355 errbuf := hr_utility.get_message;
356 retcode := 2;
357 RAISE;
358 --
359
360
361 WHEN others THEN
362 --
363 -- Set up error message and return code.
364 --
365 hr_utility.trace('sqlerrm = ' || sqlerrm);
366 errbuf := sqlerrm;
367 retcode := 2;
368
369 end GEOCODE_UPGRADE;
370
371 end PAY_US_GEOCODE_ENGINE_PKG;
372