4 procedure initialize_qualification_ind(
1 package body qp_qualification_ind_upg_util as
2 /* $Header: QPXUQUAB.pls 120.0 2005/06/02 00:41:44 appldev noship $ */
3
5 p_batchsize IN NUMBER := 5000,
6 l_worker IN NUMBER := 1)
7 IS
8 err_msg VARCHAR2(240);
9 v_min_line number := 0;
10 v_max_line number := 0;
11
12 BEGIN
13
14 begin
15
16 select start_line_id,
17 end_line_id
18 into v_min_line,
19 v_max_line
20 from qp_upg_lines_distribution
21 where worker = l_worker
22 and line_type = 'QUA';
23
24 exception
25
26 when no_data_found then
27
28 /* log the error */
29 v_min_line := 0;
30 v_max_line := 0;
31 commit;
32 return;
33 end;
34
35 --dbms_output.put_line(v_min_line);
36 --dbms_output.put_line(v_max_line);
37 loop
38 update qp_list_lines qpl set qpl.qualification_ind=3
39 where list_line_id between v_min_line and v_max_line
40 and (qualification_ind is null or qualification_ind <> 3)
41 and rownum<=p_batchsize;
42
43 --dbms_output.put_line(p_batchsize);
44 --dbms_output.put_line(SQL%ROWCOUNT);
45
46 IF (SQL%ROWCOUNT<p_batchsize) THEN
47 exit;
48 END IF;
49
50 commit;
51
52 end loop;
53
54 commit;
55
56 EXCEPTION
57
58 WHEN OTHERS THEN
59
60 err_msg := SUBSTR(SQLERRM, 1, 240);
61 rollback;
62 QP_UTIL.Log_Error (
63 p_id1 => v_min_line,
64 p_id2 => v_max_line,
65 p_error_type => 'QUALIFICATION_IND',
66 p_error_desc => err_msg,
67 p_error_module => 'INITIALIZE_QUALIFICATION_IND');
68 raise;
69
70 END initialize_qualification_ind;
71
72 procedure reinit_qualification_ind(
73 p_batchsize IN NUMBER := 5000,
74 l_worker IN NUMBER := 1)
75 IS
76 err_msg VARCHAR2(240);
77 v_min_line number := 0;
78 v_max_line number := 0;
79
80 BEGIN
81
82 begin
83
84 select start_line_id,
85 end_line_id
86 into v_min_line,
87 v_max_line
88 from qp_upg_lines_distribution
89 where worker = l_worker
90 and line_type = 'QUA';
91
92 exception
93
94 when no_data_found then
95
96 /* log the error */
97 v_min_line := 0;
98 v_max_line := 0;
99 commit;
100 return;
101 end;
102
103 loop
104 update qp_list_lines qpl set qpl.qualification_ind=1
105 where list_line_id between v_min_line and v_max_line
106 and qualification_ind = 3
107 and (exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
108 or exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
109 or not exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id))
110 and rownum<=p_batchsize;
111
112 --dbms_output.put_line(SQL%ROWCOUNT);
113
114 IF (SQL%ROWCOUNT<p_batchsize) THEN
115 exit;
116 END IF;
117
118 commit;
119
120 end loop;
121
122 commit;
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127
128 err_msg := SUBSTR(SQLERRM, 1, 240);
129 rollback;
130 QP_UTIL.Log_Error (
131 p_id1 => v_min_line,
132 p_id2 => v_max_line,
133 p_error_type => 'QUALIFICATION_IND',
134 p_error_desc => err_msg,
135 p_error_module => 'REINIT_QUALIFICATION_IND');
136 raise;
137
138 END reinit_qualification_ind;
139
140 procedure set_qualification_ind(
141 p_batchsize IN NUMBER := 5000,
142 l_worker IN NUMBER := 1)
143 IS
144 err_msg VARCHAR2(240);
145 v_min_line number := 0;
146 v_max_line number := 0;
147
148 BEGIN
149
150 begin
151
152 select start_line_id,
153 end_line_id
154 into v_min_line,
155 v_max_line
156 from qp_upg_lines_distribution
157 where worker = l_worker
158 and line_type = 'QUA';
159
160 exception
161
162 when no_data_found then
163
164 /* log the error */
165 v_min_line := 0;
166 v_max_line := 0;
167 commit;
168 return;
169 end;
170
171 --dbms_output.put_line('v_min_line : ' || v_min_line);
172 --dbms_output.put_line('v_max_line : ' || v_max_line);
173
174 loop
175 update qp_list_lines qpl set qpl.qualification_ind=2
176 where qpl.list_line_id between v_min_line and v_max_line
177 and exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
178 and not exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
182
179 and exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
180 and qpl.qualification_ind=1
181 and rownum<=p_batchsize;
183 --dbms_output.put_line(SQL%ROWCOUNT);
184
185 IF (SQL%ROWCOUNT<p_batchsize) THEN
186 exit;
187 END IF;
188
189 commit;
190
191 end loop;
192
193 commit;
194
195 loop
196 update qp_list_lines qpl set qpl.qualification_ind=4
197 where qpl.list_line_id between v_min_line and v_max_line
198 and exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
199 and exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
200 and not exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
201 and qpl.qualification_ind=1
202 and rownum<=p_batchsize;
203
204 --dbms_output.put_line(SQL%ROWCOUNT);
205
206 IF (SQL%ROWCOUNT<p_batchsize) THEN
207 exit;
208 END IF;
209
210 commit;
211
212 end loop;
213
214 commit;
215
216 loop
217 update qp_list_lines qpl set qpl.qualification_ind=5
218 where qpl.list_line_id between v_min_line and v_max_line
219 and not exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
220 and exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
221 and not exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
222 and qpl.qualification_ind=1
223 and rownum<=p_batchsize;
224
225 --dbms_output.put_line(SQL%ROWCOUNT);
226
227 IF (SQL%ROWCOUNT<p_batchsize) THEN
228 exit;
229 END IF;
230
231 commit;
232
233 end loop;
234
235 commit;
236
237 loop
238 update qp_list_lines qpl set qpl.qualification_ind=6
239 where qpl.list_line_id between v_min_line and v_max_line
240 and exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
241 and not exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
242 and not exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
243 and qpl.qualification_ind=1
244 and rownum<=p_batchsize;
245
246 --dbms_output.put_line(SQL%ROWCOUNT);
247
248 IF (SQL%ROWCOUNT<p_batchsize) THEN
249 exit;
250 END IF;
251
252 commit;
253
254 end loop;
255
256 commit;
257
258 loop
259 update qp_list_lines qpl set qpl.qualification_ind=7
260 where qpl.list_line_id between v_min_line and v_max_line
261 and not exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
262 and not exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
263 and not exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
264 and qpl.qualification_ind=1
265 and rownum<=p_batchsize;
266
267 --dbms_output.put_line(SQL%ROWCOUNT);
268
269 IF (SQL%ROWCOUNT<p_batchsize) THEN
270 exit;
271 END IF;
272
273 commit;
274
275 end loop;
276
277 commit;
278
279 loop
280 update qp_list_lines qpl set qpl.qualification_ind=NULL
281 where qpl.list_line_id between v_min_line and v_max_line
282 and exists (select 'X' from qp_rltd_modifiers qprltd where qprltd.to_rltd_modifier_id=qpl.list_line_id and rltd_modifier_grp_type<>'COUPON')
283 and exists (select 'X' from qp_qualifiers q where q.list_header_id=qpl.list_header_id and (q.list_line_id=qpl.list_line_id or q.list_line_id IS NULL))
284 and exists (select 'X' from qp_pricing_attributes qpprod where qpprod.list_line_id = qpl.list_line_id)
285 and qpl.qualification_ind=1
286 and rownum<=p_batchsize;
287
288 --dbms_output.put_line(SQL%ROWCOUNT);
289
290 IF (SQL%ROWCOUNT<p_batchsize) THEN
291 exit;
292 END IF;
293
294 commit;
295
296 end loop;
297
298 commit;
299
300 EXCEPTION
301
302 WHEN OTHERS THEN
303
304 err_msg := SUBSTR(SQLERRM, 1, 240);
305 rollback;
306 QP_UTIL.Log_Error (
307 p_id1 => v_min_line,
308 p_id2 => v_max_line,
309 p_error_type => 'QUALIFICATION_IND',
310 p_error_desc => err_msg,
311 p_error_module => 'SET_QUALIFICATION_IND');
312 raise;
313
314 END set_qualification_ind;
315
316 PROCEDURE create_parallel_slabs
317 ( l_workers IN NUMBER := 5,
318 l_type IN VARCHAR2 := 'QUA')
319 IS
320 V_TYPE VARCHAR2(1);
321
322
323 L_TOTAL_LINES NUMBER;
324 L_MIN_LINE NUMBER;
325 L_MAX_LINE NUMBER;
326 L_COUNTER NUMBER;
327 L_GAP NUMBER;
328 L_WORKER_COUNT NUMBER;
329 L_WORKER_START NUMBER;
330 L_WORKER_END NUMBER;
331 L_PRICE_LIST_LINE_ID NUMBER;
332 L_START_FLAG NUMBER;
333 L_TOTAL_WORKERS NUMBER;
334
335 BEGIN
336
337 DELETE FROM qp_upg_lines_distribution
338 WHERE line_type = l_type;
339 COMMIT;
340
341 BEGIN
342 IF l_type = 'QUA' THEN
343 select nvl(min(list_line_id),0), nvl(max(list_line_id),0)
344 into l_min_line, l_max_line
345 from qp_list_lines;
346
347 ELSIF l_type = 'DEN' THEN -- Related to Denormalization code
348 select nvl(min(list_header_id),0), nvl(max(list_header_id),0)
349 into l_min_line, l_max_line
350 from qp_list_headers_b;
351
352 END IF;
353
354 EXCEPTION
355 when others then
356 l_min_line := 0;
357 l_max_line := 0;
358 END;
359
360
361 FOR I in 1..l_workers loop
362
363 l_worker_start := l_min_line + trunc( (i-1) * (l_max_line-l_min_line)/l_workers);
364
365 l_worker_end := l_min_line + trunc(i*(l_max_line-l_min_line)/l_workers);
366
367 if i <> l_workers then
368
369 l_worker_end := l_worker_end - 1;
370
371 end if;
372
373 qp_modifier_upgrade_util_pvt.insert_line_distribution
374 (
375 l_worker => i,
376 l_start_line => l_worker_start,
377 l_end_line => l_worker_end,
378 l_type_var =>l_type
379 );
380
381 END LOOP;
382
383 commit;
384
385
386 end create_parallel_slabs;
387
388
389 PROCEDURE create_parallel_count_slabs(l_workers IN NUMBER := 5,
390 l_type IN VARCHAR2 := 'QIN')
391 IS
392
393 l_max NUMBER := 0;
394 l_min NUMBER := 0;
395 l_slab NUMBER := 0;
396
397 l_count NUMBER;
398
399 begin
400
401 --Check if any re-runnable slab already exists. Create new slabs only if
402 --there isn't even 1 such slab existing.
403 begin
404 select 1
405 into l_count
406 from qp_upg_lines_distribution
407 where line_type = l_type
408 and last_proc_line is not null
409 and rownum = 1;
410 exception
411 when no_data_found then
412 l_count := 0;
413 end;
414
415
416 --Re-runnable slabs do not already exist.
417 if l_count = 0 then
418
419 --So that even non-rerunnable slabs, if any, may be purged.
420 delete from qp_upg_lines_distribution
421 where line_type = l_type;
422 commit;
423
424 select round(count(*)/l_workers) + 1
425 into l_slab
426 from qp_list_lines;
427
428 for i in 1..l_workers
429 loop
430 begin
431 select max(list_line_id), min(list_line_id)
432 into l_max, l_min
433 from (select list_line_id
434 from (select list_line_id
435 from qp_list_lines
436 where list_line_id > l_max
437 order by list_line_id
438 )
439 where rownum < l_slab + 1
440 );
441
442 qp_modifier_upgrade_util_pvt.insert_line_distribution
443 (l_worker => i,
444 l_start_line => l_min,
445 l_end_line => l_max,
446 l_type_var => 'QIN');
447
448 commit;
449
450 exception
451 when others then
452 l_min := 0;
453 l_max := 0;
454 end;
455 end loop;
456
457 end if; --If l_count = 0, i.e.,slabs do not already exist.
458
459 end create_parallel_count_slabs;
460
461
462 end qp_qualification_ind_upg_util;
[Home] [Help]