[Home] [Help]
PACKAGE BODY: APPS.OTA_DATA_UPGRADER_UTIL
Source
1 PACKAGE BODY ota_data_upgrader_util AS
2 /* $Header: otdatupg.pkb 120.1 2005/08/01 00:57:07 jbharath noship $ */
3
4 g_logging_level varchar2(80);
5 g_general_logging_level boolean;
6
7 -- ----------------------------------------------------------------------------
8 -- |----------------------------< getDebugState >-----------------------------|
9 -- ----------------------------------------------------------------------------
10 --
11 Function getLoggingState return varchar2 is
12
13 cursor c_get_debug is
14 select parameter_value
15 from pay_action_parameters
16 where parameter_name = 'LOGGING';
17 l_logging_level varchar2(80);
18
19 BEGIN
20 l_logging_level := g_logging_level;
21 if l_logging_level is null then
22 open c_get_debug;
23 fetch c_get_debug into l_logging_level;
24 if c_get_debug%NOTFOUND then
25 g_logging_level := 'NONE';
26 l_logging_level := g_logging_level;
27 close c_get_debug;
28 elsif c_get_debug%FOUND
29 and l_logging_level = 'G' then
30 g_logging_level := l_logging_level;
31 g_general_logging_level := TRUE;
32 close c_get_debug;
33 end if;
34 end if;
35 return l_logging_level;
36 END;
37
38 Procedure writeLog(p_text in VARCHAR2
39 ,p_logging_type in VARCHAR2 default null
40 ,p_error in BOOLEAN default FALSE
41 ,p_location in NUMBER default 0) IS
42
43 l_logging_level varchar2(80);
44
45 BEGIN
46
47 l_logging_level := getLoggingState();
48
49 if p_error then
50 fnd_file.put_line(FND_FILE.log, p_text);
51 hr_utility.raise_error;
52 end if;
53
54 if p_logging_type = 'G'
55 and g_general_logging_level
56 then
57 fnd_file.put_line(FND_FILE.log, p_text);
58 -- else
59 -- fnd_file.put_line(FND_FILE.log, p_text);
60 end if;
61
62
63 hr_utility.set_location(p_text,10);
64 exception
65 when others then
66 if SQLCODE = -20100 then
67 hr_utility.set_location(p_text,p_location);
68 else
69 raise;
70 end if;
71 END;
72
73
74 -- ----------------------------------------------------------------------------
75 -- |-------------------------< upgradeUsingRowid >-----------------------------|
76 -- ----------------------------------------------------------------------------
77 --
78 procedure upgradeUsingRowid
79 (p_this_worker_num number
80 ,p_total_num_workers number
81 ,p_process_ctrl varchar2
82 ,p_table_owner varchar2
83 ,p_table_name varchar2
84 ,p_pkid_column varchar2
85 ,p_update_name varchar2
86 ,p_batch_size number
87 ,p_upg_proc varchar2
88 ,p_upgrade_id varchar2)
89
90 Is
91 l_start_rowid rowid;
92 l_end_rowid rowid;
93 l_rows_processed number;
94 l_any_rows_to_process boolean;
95 l_plsql varchar2(2000);
96 begin
97
98 ad_parallel_updates_pkg.initialize_rowid_range(
99 ad_parallel_updates_pkg.ROWID_RANGE,
100 p_table_owner,
101 p_table_name,
102 p_update_name,
103 p_this_worker_num,
104 p_total_num_workers,
105 p_batch_size, 0);
106
107 ad_parallel_updates_pkg.get_rowid_range(
108 l_start_rowid,
109 l_end_rowid,
110 l_any_rows_to_process,
111 p_batch_size,
112 TRUE); --Restart Flag
113
114 while (l_any_rows_to_process = TRUE)
115 loop
116
117 --
118 --
119 -- **************************************************
120 -- *** Use ROWID hint to ensure ROWID access path ***
121 -- **************************************************
122 --
123
124 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT,:UPGRADEID); end;';
125 execute immediate l_plsql
126 using p_process_ctrl,
127 l_start_rowid,
128 l_end_rowid,
129 OUT l_rows_processed,
130 to_number(p_upgrade_id) ;
131
132 -----------------------------------------------------
133
134 ad_parallel_updates_pkg.processed_rowid_range(
135 l_rows_processed,
136 l_end_rowid);
137
138 --
139 -- commit transaction here
140 --
141
142 commit;
143
144 --
145 -- get new range of rowids
146 --
147 ad_parallel_updates_pkg.get_rowid_range(
148 l_start_rowid,
149 l_end_rowid,
150 l_any_rows_to_process,
151 p_batch_size,
152 FALSE); --Restart Flag
153
154 end loop;
155
156 end upgradeUsingRowid ;
157
158
159 -- ----------------------------------------------------------------------------
160 -- |----------------------------< upgradeChunk >------------------------------|
161 -- ----------------------------------------------------------------------------
162 --
163 procedure upgradeChunk
164 (p_this_worker_num number
165 ,p_total_num_workers number
166 ,p_process_ctrl varchar2
167 ,p_table_owner varchar2
168 ,p_table_name varchar2
169 ,p_pkid_column varchar2
170 ,p_update_name varchar2
171 ,p_batch_size number
172 ,p_upg_proc varchar2
173 ,p_upgrade_id varchar2
174 ,p_use_rowid varchar2)
175 is
176
177 l_start_pkid number;
178 l_end_pkid number;
179 l_rows_processed number;
180 l_any_rows_to_process boolean;
181 l_restart boolean;
182
183 l_table_rowcount number;
184 l_min_id number;
185 l_max_id number;
186 l_plsql varchar2(1000);
187 l_min_rowid rowid;
188 l_max_rowid rowid;
189
190 begin
191 l_plsql := 'select count(distinct '||p_pkid_column||'),
192 min('||p_pkid_column||'),
193 max('||p_pkid_column||'),
194 min(rowid),
195 max(rowid)
196 from '||p_table_name;
197
198
199 execute immediate l_plsql into l_table_rowcount,
200 l_min_id,
201 l_max_id,
202 l_min_rowid,
203 l_max_rowid;
204
205 if l_table_rowcount > 2 * p_total_num_workers then
206 if p_use_rowid = 'Y' then
207 /*
208 ** Process upgrade using rowid....
209 */
210 upgradeUsingRowid
211 (p_this_worker_num
212 ,p_total_num_workers
213 ,p_process_ctrl
214 ,p_table_owner
215 ,p_table_name
216 ,p_pkid_column
217 ,p_update_name
218 ,p_batch_size
219 ,p_upg_proc
220 ,p_upgrade_id
221 ) ;
222 else
223 /*
224 ** Prepare the upgrade....
225 */
226 ad_parallel_updates_pkg.initialize_id_range(
227 ad_parallel_updates_pkg.ID_RANGE,
228 p_table_owner,
229 p_table_name,
230 p_update_name,
231 p_pkid_column,
232 p_this_worker_num,
233 p_total_num_workers,
234 p_batch_size,
235 0 -- debug level
236 );
237 /*
238 ** Get the initial range of IDs to process.
239 */
240 ad_parallel_updates_pkg.get_id_range(
241 l_start_pkid,
242 l_end_pkid,
243 l_any_rows_to_process,
244 p_batch_size,
245 TRUE -- Restart flag
246 );
247 if l_any_rows_to_process then
248 writeLog('Have got rows to process', 'G', FALSE, 10);
249 else
250 writeLog('Have got no rows to process', 'G', FALSE, 20);
251 end if;
252 /*
253 ** Process the rows in the batch....
254 */
255 while (l_any_rows_to_process = TRUE)
256 loop
257
258 writeLog(p_this_worker_num||' processing range '||
259 l_start_pkid||'-'||l_end_pkid, 'G', FALSE, 30);
260 /*
261 ** Use dynamic SQL to process the batch of records.
262 ** The procedure to call is identified in the parameter p_upg_proc.
263 */
264 if p_upgrade_id is null then
265 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT); end;';
266 execute immediate l_plsql
267 using p_process_ctrl,
268 l_start_pkid,
269 l_end_pkid,
270 OUT l_rows_processed;
271 else
272 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT,:UPGRADEID); end;';
273 execute immediate l_plsql
274 using p_process_ctrl,
275 l_start_pkid,
276 l_end_pkid,
277 OUT l_rows_processed,
278 to_number(p_upgrade_id) ;
279 end if;
280 /*
281 ** Mark the batch of IDs as processed...
282 */
283 ad_parallel_updates_pkg.processed_id_range(
284 l_rows_processed,
285 l_end_pkid);
286
287 /*
288 ** Commit the updates....
289 */
290 commit;
291
292 /*
293 ** Get the next range of IDs
294 */
295 ad_parallel_updates_pkg.get_id_range(
296 l_start_pkid,
297 l_end_pkid,
298 l_any_rows_to_process,
299 p_batch_size,
300 FALSE -- Restart flag
301 );
302
303 end loop;
304 end if;
305 elsif l_min_id is not null and l_max_id is not null then
306 /*
307 ** The rowcount is less than twice the number of threads doing the
308 ** work so don't use the AD large table update utilities instead
309 ** do the call directly but only if this is the first worker. This means
310 ** the other threads should exit without doing anything.
311 */
312 if p_this_worker_num = 1 then
313 if p_upgrade_id is null then
314 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT); end;';
315 execute immediate l_plsql
316 using p_process_ctrl,
317 l_min_id,
318 l_max_id,
319 OUT l_rows_processed;
320 elsif p_use_rowid = 'Y' then
321 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT,:UPGRADEID); end;';
322 execute immediate l_plsql
323 using p_process_ctrl,
324 l_min_rowid,
325 l_max_rowid,
326 OUT l_rows_processed,
327 to_number(p_upgrade_id) ;
328
329 else
330 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT,:UPGRADEID); end;';
331 execute immediate l_plsql
332 using p_process_ctrl,
333 l_min_id,
334 l_max_id,
335 OUT l_rows_processed,
336 to_number(p_upgrade_id) ;
337 end if;
338 end if;
339
340 end if;
341
342 end upgradeChunk;
343
344 procedure submitUpgradeProcessControl(
345 errbuf out nocopy varchar2,
346 retcode out nocopy number,
347 p_process_to_call in varchar2,
348 p_upgrade_type in varchar2,
349 p_action_parameter_group_id in varchar2,
350 p_process_ctrl in varchar2,
351 p_param1 in varchar2,
352 p_param2 in varchar2,
353 p_param3 in varchar2,
354 p_param4 in varchar2,
355 p_param5 in varchar2,
356 p_param6 in varchar2,
357 p_param7 in varchar2,
358 p_param8 in varchar2,
359 p_param9 in varchar2,
360 p_param10 in varchar2
361 )
362
363 is
364
365 l_action_parameter_group_id number := to_number(p_action_parameter_group_id);
366 l_request_data varchar2(100);
367 l_number_of_threads number;
368 l_request_id number;
369 user_exception exception;
370
371 begin
372 --hr_utility.trace_on('F','LGEUPG');
373 writeLog('Starting process', 'G', FALSE, 0);
374
375 -- raise user_exception;
376
377 /*
378 ** Get restart token....
379 */
380 writeLog('Step 1', 'G', FALSE, 0);
381 l_request_data := fnd_conc_global.request_data;
382 if l_request_data is not null then
383 /*
384 ** Performe restart processing.
385 */
386 writeLog('Performing Restart', 'G', FALSE, 0);
387 return;
388 end if;
389 writeLog('Step 2', 'G', FALSE, 0);
390 /*
391 ** Obtain the number of THREADS to be used using the
392 ** action group ID. If this is not set then use the default
393 ** number of THREADS.
394 */
395 writeLog('Step 3', 'G', FALSE, 0);
396 if l_action_parameter_group_Id is not null then
397 writeLog('Step 4', 'G', FALSE, 0);
398 -- pay_core_utils.set_pap_group_id(l_action_parameter_group_id);
399 end if;
400 writeLog('Step 5', 'G', FALSE, 0);
401
402 begin
403 select parameter_value
404 into l_number_of_threads
405 from pay_action_parameters
406 where parameter_name = 'THREADS';
407 exception
408 when no_data_found then
409 l_number_of_threads := 1;
410 when others then
411 raise;
412 end;
413
414 writeLog('Threads : '||to_char(l_number_of_threads), 'G', FALSE, 0);
415 /*
416 ** Submit 'l_number_of_threads' sub-requests to perform
417 ** the process specified.
418 */
419 for counter in 1..l_number_of_threads loop
420 writeLog('Submitting thread '||to_char(counter), 'G', FALSE, 0);
421 l_request_id := fnd_request.submit_request(
422 application => 'OTA',
423 program => 'OTAMTUPGWKR',
424 --program => 'PERMTUPGWKR',
425 sub_request => TRUE,
426 argument1 => counter,
427 argument2 => l_number_of_threads,
428 argument3 => p_process_to_call,
429 argument4 => p_upgrade_type,
430 argument5 => p_process_ctrl,
431 argument6 => p_param1,
432 argument7 => p_param2,
433 argument8 => p_param3,
434 argument9 => p_param4,
435 argument10 => p_param5,
436 argument12 => p_param6,
437 argument13 => p_param7,
438 argument14 => p_param8,
439 argument15 => p_param9,
440 argument16 => p_param10,
441 argument17 => chr(0));
442 writeLog('submitted request '||to_char(l_request_id), 'G', FALSE, 0);
443 end loop;
444 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
445 request_data => 'OTAMTUPGCTL');
446 --request_data => 'PERMTUPGCTL');
447 end;
448
449 procedure submitUpgradeProcessSingle(
450 errbuf out nocopy varchar2,
451 retcode out nocopy number,
452 p_process_number in varchar2,
453 p_max_number_proc in varchar2,
454 p_process_to_call in varchar2,
455 p_upgrade_type in varchar2,
456 p_process_ctrl in varchar2,
457 p_param1 in varchar2,
458 p_param2 in varchar2,
459 p_param3 in varchar2,
460 p_param4 in varchar2,
461 p_param5 in varchar2,
462 p_param6 in varchar2,
463 p_param7 in varchar2,
464 p_param8 in varchar2,
465 p_param9 in varchar2,
466 p_param10 in varchar2
467 )
468
469 is
470 l_plsql varchar2(1000);
471 begin
472
473 writeLog('Starting process', 'G', FALSE, 10);
474 /*
475 ** Determine the type of upgrade script to call and call it.
476 */
477 if p_upgrade_type = 'AD_LGE_TBL_UPG' then
478 /*
479 ** Upgrade using the AD large table upgrade infrastructure.
480 */
481 writeLog('Doing large table update.', 'G', FALSE, 20);
482 upgradeChunk(
483 p_this_worker_num => p_process_number,
484 p_total_num_workers => p_max_number_proc,
485 p_process_ctrl => p_process_ctrl,
486 p_table_owner => p_param1,
487 p_table_name => p_param2,
488 p_pkid_column => p_param3,
489 p_update_name => p_param4,
490 p_batch_size => p_param5,
491 p_upg_proc => p_process_to_call,
492 p_upgrade_id => p_param6,
493 p_use_rowid => p_param10);
494 elsif p_upgrade_type = 'GEN_SCRIPT' then
495 /*
496 ** Upgrade using a generaic pacakge procedure call. Note called procedure
497 ** must accept 12 varchar2 parameters as noted below in the call.
498 */
499 l_plsql := 'begin '||p_process_to_call||'(:proc_num, :max_num_proc,
500 :param1, :param2, :param3, :param4, :param5, :param6,
501 :param7, :param8, :param9, :param10 ); end;';
502 execute immediate l_plsql
503 using p_process_number, p_max_number_proc,
504 p_param1, p_param2, p_param3, p_param4, p_param5,
505 p_param6, p_param7, p_param8, p_param9, p_param10;
506
507 end if;
508 end;
509
510 end ota_data_upgrader_util;