[Home] [Help]
PACKAGE BODY: APPS.PER_DATA_UPGRADER_UTIL
Source
1 PACKAGE BODY per_data_upgrader_util AS
2 /* $Header: pedatupgutl.pkb 115.6 2003/09/26 03:32:53 mbocutt 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 -- |----------------------------< upgradeChunk >------------------------------|
75 -- ----------------------------------------------------------------------------
76 --
77 procedure upgradeChunk
78 (p_this_worker_num number
79 ,p_total_num_workers number
80 ,p_process_ctrl varchar2
81 ,p_table_owner varchar2
82 ,p_table_name varchar2
83 ,p_pkid_column varchar2
84 ,p_update_name varchar2
85 ,p_batch_size number
86 ,p_upg_proc varchar2)
87 is
88
89 l_start_pkid number;
90 l_end_pkid number;
91 l_rows_processed number;
92 l_any_rows_to_process boolean;
93 l_restart boolean;
94
95 l_table_rowcount number;
96 l_min_id number;
97 l_max_id number;
98
99 l_plsql varchar2(1000);
100 begin
101
102 l_plsql := 'select count(distinct '||p_pkid_column||'),
103 min('||p_pkid_column||'),
104 max('||p_pkid_column||')
105 from '||p_table_name;
106
107
108 execute immediate l_plsql into l_table_rowcount,
109 l_min_id,
110 l_max_id;
111
112 if l_table_rowcount > 2 * p_total_num_workers then
113 /*
114 ** Prepare the upgrade....
115 */
116 ad_parallel_updates_pkg.initialize_id_range(
117 ad_parallel_updates_pkg.ID_RANGE,
118 p_table_owner,
119 p_table_name,
120 p_update_name,
121 p_pkid_column,
122 p_this_worker_num,
123 p_total_num_workers,
124 p_batch_size,
125 0 -- debug level
126 );
127 /*
128 ** Get the initial range of IDs to process.
129 */
130 ad_parallel_updates_pkg.get_id_range(
131 l_start_pkid,
132 l_end_pkid,
133 l_any_rows_to_process,
134 p_batch_size,
135 TRUE -- Restart flag
136 );
137 if l_any_rows_to_process then
138 writeLog('Have got rows to process', 'G', FALSE, 10);
139 else
140 writeLog('Have got no rows to process', 'G', FALSE, 20);
141 end if;
142 /*
143 ** Process the rows in the batch....
144 */
145 while (l_any_rows_to_process = TRUE)
146 loop
147
148 writeLog(p_this_worker_num||' processing range '||
149 l_start_pkid||'-'||l_end_pkid, 'G', FALSE, 30);
150 /*
151 ** Use dynamic SQL to process the batch of records.
152 ** The procedure to call is identified in the parameter p_upg_proc.
153 */
154 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT); end;';
155 execute immediate l_plsql
156 using p_process_ctrl,
157 l_start_pkid,
158 l_end_pkid,
159 OUT l_rows_processed;
160 /*
161 ** Mark the batch of IDs as processed...
162 */
163 ad_parallel_updates_pkg.processed_id_range(
164 l_rows_processed,
165 l_end_pkid);
166
167 /*
168 ** Commit the updates....
169 */
170 commit;
171
172 /*
173 ** Get the next range of IDs
174 */
175 ad_parallel_updates_pkg.get_id_range(
176 l_start_pkid,
177 l_end_pkid,
178 l_any_rows_to_process,
179 p_batch_size,
180 FALSE -- Restart flag
181 );
182
183 end loop;
184
185 elsif l_min_id is not null and
186 l_max_id is not null then
187 /*
188 ** The rowcount is less than twice the number of threads doing the
189 ** work so don't use the AD large table update utilities instead
190 ** do the call directly but only if this is the first worker. This means
191 ** the other threads should exit without doing anything.
192 */
193 if p_this_worker_num = 1 then
194 l_plsql := 'begin '||p_upg_proc||'(:CTRL, :START, :END, :ROWCOUNT); end;';
195 execute immediate l_plsql
196 using p_process_ctrl,
197 l_min_id,
198 l_max_id,
199 OUT l_rows_processed;
200 end if;
201
202 end if;
203
204 end upgradeChunk;
205
206 procedure submitUpgradeProcessControl(
207 errbuf out nocopy varchar2,
208 retcode out nocopy number,
209 p_process_to_call in varchar2,
210 p_upgrade_type in varchar2,
211 p_action_parameter_group_id in varchar2,
212 p_process_ctrl in varchar2,
213 p_param1 in varchar2,
214 p_param2 in varchar2,
215 p_param3 in varchar2,
216 p_param4 in varchar2,
217 p_param5 in varchar2,
218 p_param6 in varchar2,
219 p_param7 in varchar2,
220 p_param8 in varchar2,
221 p_param9 in varchar2,
222 p_param10 in varchar2
223 )
224
225 is
226
227 l_action_parameter_group_id number := to_number(p_action_parameter_group_id);
228 l_request_data varchar2(100);
229 l_number_of_threads number;
230 l_request_id number;
231 user_exception exception;
232
233 begin
234 --hr_utility.trace_on('F','LGEUPG');
235 writeLog('Starting process', 'G', FALSE, 0);
236
237 -- raise user_exception;
238
239 /*
240 ** Get restart token....
241 */
242 writeLog('Step 1', 'G', FALSE, 0);
243 l_request_data := fnd_conc_global.request_data;
244 if l_request_data is not null then
245 /*
246 ** Performe restart processing.
247 */
248 writeLog('Performing Restart', 'G', FALSE, 0);
249 return;
250 end if;
251 writeLog('Step 2', 'G', FALSE, 0);
252 /*
253 ** Obtain the number of THREADS to be used using the
254 ** action group ID. If this is not set then use the default
255 ** number of THREADS.
256 */
257 writeLog('Step 3', 'G', FALSE, 0);
258 if l_action_parameter_group_Id is not null then
259 writeLog('Step 4', 'G', FALSE, 0);
260 pay_core_utils.set_pap_group_id(l_action_parameter_group_id);
261 end if;
262 writeLog('Step 5', 'G', FALSE, 0);
263
264 begin
265 select parameter_value
266 into l_number_of_threads
267 from pay_action_parameters
268 where parameter_name = 'THREADS';
269 exception
270 when no_data_found then
271 l_number_of_threads := 1;
272 when others then
273 raise;
274 end;
275
276 writeLog('Threads : '||to_char(l_number_of_threads), 'G', FALSE, 0);
277 /*
278 ** Submit 'l_number_of_threads' sub-requests to perform
279 ** the process specified.
280 */
281 for counter in 1..l_number_of_threads loop
282 writeLog('Submitting thread '||to_char(counter), 'G', FALSE, 0);
283 l_request_id := fnd_request.submit_request(
284 application => 'PER',
285 program => 'PERMTUPGWKR',
286 sub_request => TRUE,
287 argument1 => counter,
288 argument2 => l_number_of_threads,
289 argument3 => p_process_to_call,
290 argument4 => p_upgrade_type,
291 argument5 => p_process_ctrl,
292 argument6 => p_param1,
293 argument7 => p_param2,
294 argument8 => p_param3,
295 argument9 => p_param4,
296 argument10 => p_param5,
297 argument12 => p_param6,
298 argument13 => p_param7,
299 argument14 => p_param8,
300 argument15 => p_param9,
301 argument16 => p_param10,
302 argument17 => chr(0));
303 writeLog('submitted request '||to_char(l_request_id), 'G', FALSE, 0);
304 end loop;
305 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
306 request_data => 'PERMTUPGCTL');
307 end;
308
309 procedure submitUpgradeProcessSingle(
310 errbuf out nocopy varchar2,
311 retcode out nocopy number,
312 p_process_number in varchar2,
313 p_max_number_proc in varchar2,
314 p_process_to_call in varchar2,
315 p_upgrade_type in varchar2,
316 p_process_ctrl in varchar2,
317 p_param1 in varchar2,
318 p_param2 in varchar2,
319 p_param3 in varchar2,
320 p_param4 in varchar2,
321 p_param5 in varchar2,
322 p_param6 in varchar2,
323 p_param7 in varchar2,
324 p_param8 in varchar2,
325 p_param9 in varchar2,
326 p_param10 in varchar2
327 )
328
329 is
330 l_plsql varchar2(1000);
331 begin
332
333 writeLog('Starting process', 'G', FALSE, 10);
334 /*
335 ** Determine the type of upgrade script to call and call it.
336 */
337 if p_upgrade_type = 'AD_LGE_TBL_UPG' then
338 /*
339 ** Upgrade using the AD large table upgrade infrastructure.
340 */
341 writeLog('Doing large table update.', 'G', FALSE, 20);
342 upgradeChunk(
343 p_this_worker_num => p_process_number,
344 p_total_num_workers => p_max_number_proc,
345 p_process_ctrl => p_process_ctrl,
346 p_table_owner => p_param1,
347 p_table_name => p_param2,
348 p_pkid_column => p_param3,
349 p_update_name => p_param4,
350 p_batch_size => p_param5,
351 p_upg_proc => p_process_to_call);
352 elsif p_upgrade_type = 'GEN_SCRIPT' then
353 /*
354 ** Upgrade using a generaic pacakge procedure call. Note called procedure
355 ** must accept 12 varchar2 parameters as noted below in the call.
356 */
357 l_plsql := 'begin '||p_process_to_call||'(:proc_num, :max_num_proc,
358 :param1, :param2, :param3, :param4, :param5, :param6,
359 :param7, :param8, :param9, :param10 ); end;';
360 execute immediate l_plsql
361 using p_process_number, p_max_number_proc,
362 p_param1, p_param2, p_param3, p_param4, p_param5,
363 p_param6, p_param7, p_param8, p_param9, p_param10;
364
365 end if;
366 end;
367
368 end per_data_upgrader_util;