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