[Home] [Help]
PACKAGE BODY: APPS.OKS_INTEGRATION_UTIL_PUB
Source
1 Package Body OKS_INTEGRATION_UTIL_PUB AS
2 /* $Header: OKSRIUTB.pls 120.2 2006/05/30 19:32:02 jvarghes noship $ */
3
4
5 -- Global constant for the maximum allowed sub-requests (parallel workers)
6 MAX_JOBS NUMBER := 20;
7
8 -- Global vars to hold the min and max hdr_id for each sub-request range
9 type range_rec is record (
10 lo number,
11 hi number,
12 jobno number);
13 type rangeArray is VARRAY(50) of range_rec;
14 range_arr rangeArray;
15 g_instance_id integer := 0;
16
17 FUNCTION get_instance_id
18 return integer is
19 cursor inst_csr is
20 select instance_number from v$instance;
21 BEGIN
22 IF g_instance_id = 0 THEN
23 OPEN inst_csr;
24 FETCH inst_csr into g_instance_id;
25 CLOSE inst_csr;
26 END IF;
27 RETURN g_instance_id;
28 END;
29
30
31 /**************************************************************************
32 *** Function checks if a service line already exists in the order details
33 ***************************************************************************/
34
35 Function Get_Order_Line_Id
36 (
37 P_Order_Line_id IN NUMBER
38 )
39 Return Number
40 Is
41
42 Cursor l_order_csr Is
43 Select Order_Line_Id1
44 From Oks_K_Order_Details
45 Where Order_Line_Id1 = to_char(P_Order_Line_id);
46
47 l_order_exists_rec l_order_csr%rowtype;
48 Begin
49 Open l_order_csr;
50 Fetch l_order_csr Into l_order_exists_rec;
51
52 If l_order_csr%Notfound then
53 Close l_order_csr;
54 Return (Null);
55 End If;
56
57 close l_order_csr;
58 Return (l_order_exists_rec.Order_Line_Id1);
59
60 End Get_Order_Line_Id;
61
62
63 /**************************************************************************
64 *** procedure to populate the order details table with all the services
65 *** from okx_order_lines_v
66 ***************************************************************************/
67
68 PROCEDURE Create_K_Order_Details
69 (
70 p_header_id IN NUMBER
71 , x_return_status OUT NOCOPY Varchar2
72 , x_msg_count OUT NOCOPY Number
73 , x_msg_data OUT NOCOPY Varchar2
74 )
75 IS
76
77
78
79 -- Cursor to select the service items from order lines
80
81 Cursor Order_Dtl_cur Is
82 Select Id1, Id2, Service_End_Date
83 From okx_order_lines_v ol
84 Where ol.Header_id = P_header_id
85 And ol.Service_reference_Type_Code IN ('CUSTOMER_PRODUCT','ORDER');
86
87 -- Cursor to check if all other orders has to be linked with
88 -- this order when apply all flag is set to 'Y'
89
90 Cursor line_dtl_cur Is
91 select order_line_id1, order_line_id2, line_renewal_type, renewal_type,
92 po_required_yn, renewal_pricing_type,markup_percent,billing_profile_id,
93 chr_id, cle_id, cod_type, cod_id, end_date,contact_id, email_id, phone_id, fax_id, site_id
94 ,renewal_approval_flag --Bug# 5173373
95 from Oks_K_Order_Details
96 where Link_Order_Header_ID = P_header_id
97 And APPLY_ALL_YN = 'Y';
98
99 -- Cursor to check if all other orders has to be linked with
100 -- this order when service end dates falls on the same day
101
102 Cursor line_enddate_cur (l_ser_end_date date) Is
103 select order_line_id1, order_line_id2, line_renewal_type, renewal_type,
104 po_required_yn, renewal_pricing_type,markup_percent,
105 chr_id, cle_id, cod_type, cod_id, end_date,contact_id, email_id, phone_id, fax_id, site_id
106 from Oks_K_Order_Details
107 where Link_Order_Header_ID = P_header_id
108 And Cod_Type = 'NCT'
109 And trunc(end_date) = trunc(l_ser_end_date);
110
111 -- Cursor to select all the contact lines for the service
112
113 Cursor Order_contacts_cur (order_line_id VARCHAR2) Is
114 select Cro_Code, Jtot_Object_Code, Object1_Id1, Object1_Id2
115 from oks_k_order_contacts_v
116 where cod_id in (select id from Oks_K_Order_Details
117 where Order_Line_Id1 = order_line_id);
118
119 line_dtl_rec line_dtl_cur%rowtype;
120 line_enddate_rec line_enddate_cur%rowtype;
121 order_contacts_rec order_contacts_cur%rowtype;
122
123 l_msg_count Number;
124 l_msg_data Varchar2(2000);
125
126 l_cocv_tbl_in Oks_Coc_Pvt.cocv_tbl_type;
127 l_cocv_tbl_out Oks_Coc_Pvt.cocv_tbl_type;
128
129 l_covd_tbl_in Oks_Cod_Pvt.codv_tbl_type;
130 l_covd_tbl_out Oks_Cod_Pvt.codv_tbl_type;
131
132 l_order_id Oks_K_Order_Details.order_line_id1%type;
133
134
135 l_api_version CONSTANT NUMBER := 1.0;
136 l_init_msg_list CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
138 l_index VARCHAR2(2000);
139
140 l_id1 Number;
141 l_id2 VARCHAR2(40);
142 l_ser_end_date date;
143
144 BEGIN
145
146 Open Order_Dtl_cur;
147
148 Loop
149
150 l_covd_tbl_in.delete;
151 l_covd_tbl_out.delete;
152
153 x_return_status := OKC_API.G_RET_STS_SUCCESS;
154
155 Fetch Order_Dtl_cur into l_id1, l_id2, l_ser_end_date;
156
157 EXIT WHEN Order_Dtl_cur%NOTFOUND;
158
159 l_order_id := Get_Order_Line_Id (
160 P_Order_Line_id => l_id1
161 );
162
163 If l_order_id Is Null Then
164
165
166 -- check if any other service line exists for order header id
167
168
169 l_covd_tbl_in(1).order_line_id1 := TO_CHAR(l_id1);
170 l_covd_tbl_in(1).order_line_id2 := NVL(l_id2,'#');
171 l_covd_tbl_in(1).link_order_header_id := p_header_id;
172 l_covd_tbl_in(1).end_date := l_ser_end_date;
173 l_covd_tbl_in(1).line_renewal_type := 'FUL';
174 l_covd_tbl_in(1).apply_all_yn := 'N';
175
176 Open line_dtl_cur;
177 Fetch line_dtl_cur Into line_dtl_rec;
178
179 If line_dtl_cur%Notfound then
180 l_covd_tbl_in(1).cod_type := 'NCT';
181 l_covd_tbl_in(1).apply_all_yn := 'Y';
182
183 -- End if;
184 -- If the service end dates falls on the same day
185 -- then copy from the existing order line
186
187 /* --commented for Extwarranty consolidation enhancement
188 -- may 29-2002 vigandhi
189 If line_dtl_rec.end_date is NULL or
190 (trunc(line_dtl_rec.end_date) <> trunc(l_ser_end_date)) THEN
191
192 Open line_enddate_cur (l_ser_end_date);
193 Fetch line_enddate_cur Into line_enddate_rec;
194
195 If line_enddate_cur%Notfound THEN
196 l_covd_tbl_in(1).cod_type := 'NCT';
197 ELSE
198 l_covd_tbl_in(1).cod_type := 'LTO';
199 l_covd_tbl_in(1).link_ord_line_id1 := line_enddate_rec.Order_Line_Id1;
200 l_covd_tbl_in(1).link_ord_line_id2 := line_enddate_rec.Order_Line_Id2;
201 l_covd_tbl_in(1).renewal_type := line_enddate_rec.renewal_type;
202 l_covd_tbl_in(1).po_required_yn := line_enddate_rec.po_required_yn;
203 l_covd_tbl_in(1).renewal_pricing_type := line_enddate_rec.renewal_pricing_type;
204 l_covd_tbl_in(1).markup_percent := line_enddate_rec.markup_percent;
205 l_covd_tbl_in(1).cod_id := line_enddate_rec.cod_id;
206 l_covd_tbl_in(1).link_chr_id := line_enddate_rec.chr_id;
207 l_covd_tbl_in(1).link_cle_id := line_enddate_rec.cle_id;
208 l_covd_tbl_in(1).contact_id := line_enddate_rec.contact_id;
209 l_covd_tbl_in(1).email_id := line_enddate_rec.email_id;
210 l_covd_tbl_in(1).phone_id := line_enddate_rec.phone_id;
211 l_covd_tbl_in(1).fax_id := line_enddate_rec.fax_id;
212 l_covd_tbl_in(1).site_id := line_enddate_rec.site_id;
213
214 End if;
215
216 Close line_enddate_cur;
217 */
218
219 Else
220 l_covd_tbl_in(1).cod_type := 'LTO';
221 l_covd_tbl_in(1).line_renewal_type := line_dtl_rec.line_renewal_type; --mmadhavi added for bug 4339533
222 l_covd_tbl_in(1).link_ord_line_id1 := line_dtl_rec.Order_Line_Id1;
223 l_covd_tbl_in(1).link_ord_line_id2 := line_dtl_rec.Order_Line_Id2;
224 l_covd_tbl_in(1).renewal_type := line_dtl_rec.renewal_type;
225 l_covd_tbl_in(1).renewal_approval_flag := line_dtl_rec.renewal_approval_flag; -- Bug# 5173373
226 l_covd_tbl_in(1).po_required_yn := line_dtl_rec.po_required_yn;
227 l_covd_tbl_in(1).renewal_pricing_type := line_dtl_rec.renewal_pricing_type;
228 l_covd_tbl_in(1).markup_percent := line_dtl_rec.markup_percent;
229 l_covd_tbl_in(1).cod_id := line_dtl_rec.cod_id;
230 l_covd_tbl_in(1).link_chr_id := line_dtl_rec.chr_id;
231 l_covd_tbl_in(1).link_cle_id := line_dtl_rec.cle_id;
232 l_covd_tbl_in(1).contact_id := line_dtl_rec.contact_id;
233 l_covd_tbl_in(1).email_id := line_dtl_rec.email_id;
234 l_covd_tbl_in(1).phone_id := line_dtl_rec.phone_id;
235 l_covd_tbl_in(1).fax_id := line_dtl_rec.fax_id;
236 l_covd_tbl_in(1).site_id := line_dtl_rec.site_id;
237 l_covd_tbl_in(1).billing_profile_id := line_dtl_rec.billing_profile_id; -- New parameter added vigandhi(29-May2002)
238
239
240 End If;
241
242 Close line_dtl_cur;
243
244 oks_order_details_pub.Insert_order_Detail
245 (
246 p_api_version => l_api_version,
247 p_init_msg_list => l_init_msg_list,
248 x_return_status => l_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data,
251 p_codv_tbl => l_covd_tbl_in,
252 x_codv_tbl => l_covd_tbl_out
253 );
254
255
256 If NOT l_return_status = 'S' then
257 Raise G_EXCEPTION_HALT_VALIDATION;
258 End If;
259 --Commit;
260
261 If l_covd_tbl_in(1).cod_type = 'LTO' THEN
262
263 For order_contacts_rec in order_contacts_cur (l_covd_tbl_in(1).link_ord_line_id1)
264 Loop
265 l_cocv_tbl_in.delete;
266
267 l_cocv_tbl_in(1).cod_id := l_covd_tbl_out(1).id;
268 l_cocv_tbl_in(1).cro_code := order_contacts_rec.cro_code;
269 l_cocv_tbl_in(1).jtot_object_code := order_contacts_rec.jtot_object_code;
270 l_cocv_tbl_in(1).object1_id1 := order_contacts_rec.object1_id1;
271 l_cocv_tbl_in(1).object1_id2 := order_contacts_rec.object1_id2;
272
273 oks_order_contacts_pub.Insert_order_contact
274 (
275 p_api_version => l_api_version,
276 p_init_msg_list => l_init_msg_list,
277 x_return_status => l_return_status,
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data,
280 p_cocv_tbl => l_cocv_tbl_in,
281 x_cocv_tbl => l_cocv_tbl_out
282 );
283
284 If NOT l_return_status = 'S' then
285 Raise G_EXCEPTION_HALT_VALIDATION;
286 End If;
287 --Commit;
288 End Loop; -- order contact loop
289
290 End if; --- for cod type 'LTO'
291 /*
292 If l_return_status <> 'S' then
293 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'HEADER (HEADER)');
294 Raise G_EXCEPTION_HALT_VALIDATION;
295 end if;
296 */
297 End If;
298
299 End Loop;
300 --Commit;
301 Exception
302 When G_EXCEPTION_HALT_VALIDATION Then
303 x_return_status := l_return_status;
304 Null;
305 When Others Then
306 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
307 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
308
309 END Create_K_Order_Details;
310
311
312
313 /*
314 * Given the wrong date format and the date in that wrong format. This procduere will
315 * update the oks_rules_b table with the correct date format. rule_informationX has to be
316 * updated. X is the value of p_rule_num.
317 */
318
319 PROCEDURE Convert_Dates(p_category_code IN VARCHAR2,
320 p_format IN VARCHAR2,
321 p_date IN VARCHAR2,
322 p_rule_num IN NUMBER,
323 p_rule_id IN NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_data OUT NOCOPY VARCHAR2,
326 x_msg_count OUT NOCOPY NUMBER
327 ) IS
328 l_day VARCHAR2(30);
329 l_month VARCHAR2(30);
330 l_year VARCHAR2(30);
331 l_time VARCHAR2(30) := '00:00:00';
332 l_date VARCHAR2(30);
333 Begin
334 x_return_status := OKC_API.G_RET_STS_SUCCESS;
335 l_day := to_char(to_date(p_date, p_format), 'DD');
336 l_month := to_char(to_date(p_date, p_format), 'MM');
337 l_year := to_char(to_date(p_date, p_format), 'YYYY');
338 l_date := l_year || '/' || l_month || '/' || l_day || ' ' || l_time;
339
340 If p_rule_num = 2 then
341 update okc_rules_b set
342 rule_information2 = l_date
343 ---where rule_information_category = p_category_code and rule_information2 = p_date;
344 where id = p_rule_id;
345 Elsif p_rule_num = 3 then
346 update okc_rules_b set
347 rule_information3 = l_date
348 ---where rule_information_category = p_category_code and rule_information3 = p_date;
349 where id = p_rule_id;
350 Elsif p_rule_num = 4 then
351 update okc_rules_b set
352 rule_information4 = l_date
353 ---where rule_information_category = p_category_code and rule_information4 = p_date;
354 where id = p_rule_id;
355 End if;
356 commit;
357
358 Exception
359 when others then
360 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
361 Debug_Log(p_error_msg => SQLERRM,
362 x_msg_data => x_msg_data,
363 x_msg_count => x_msg_count,
364 x_return_status => x_return_status);
365
366 End Convert_Dates;
367
368
369 PROCEDURE Get_Dates(
370 p_from_id IN Number,
371 p_to_id IN Number
372 )
373 IS
374
375 TYPE V_ARRAY IS VARRAY(20) of VARCHAR2(20);
376 l_category_code1 V_ARRAY := V_ARRAY('CCR', 'CVN', 'IBR', 'RVE', 'SLL');
377 l_category_code2 V_ARRAY := V_ARRAY('REN', 'SBG');
378
379 l_format V_ARRAY := V_ARRAY('DD-MON-YY', 'DD-MON-YYYY', 'DD MON, YYYY', 'DD MON, YY',
380 'MM/DD/YYYY', 'MM/DD/YY', 'YYYY/DD/MM', 'DD MONTH, YYYY',
381 'DD MONTH, YY', 'MM-DD-YY', 'MM-DD-YYYY', 'DD-MM-YY',
382 'DD-MM-YYYY', 'DD-MONTH-YY', 'DD-MONTH-YYYY');
383 l_count_code NUMBER;
384 l_count_format NUMBER;
385 l_date DATE;
386 l_num1 NUMBER;
387 x_return_status varchar2(2);
388 x_msg_data VARCHAR2(1950);
389 x_msg_count NUMBER;
390
391
392 -- category can be: CCR, CVN, IBR, RVE, SLL
393 ----CURSOR rule_info2(category VARCHAR2, format VARCHAR2) IS
394 CURSOR rule_info2(category VARCHAR2) IS
395 select id rul_id, rule_information2 rul2
396 from okc_rules_b
397 where rgp_id between p_from_id and p_to_id
398 and rule_information_category = category;
399
400 -----and length(rule_information2) = length(format);
401
402 -- category can be: REN, SBG
403 ---CURSOR rule_info3_4(category VARCHAR2, format VARCHAR2) IS
404 CURSOR rule_info3_4(category VARCHAR2) IS
405 select id rul_id, rule_information3 rul3, rule_information4 rul4
406 from okc_rules_b
407 where rgp_id between p_from_id and p_to_id
408 and rule_information_category = category;
409
410 -----and length(rule_information3) = length(format) or length(rule_information4) = length(format);
411
412
413 Begin
414 x_return_status := OKC_API.G_RET_STS_SUCCESS;
415 l_count_code := 1;
416 While(l_count_code <= l_category_code1.COUNT) Loop
417 l_count_format := 1;
418 While(l_count_format <= l_format.COUNT) Loop
419 -----for ruleInfo2 in rule_info2(l_category_code1(l_count_code), l_format(l_count_format)) Loop
420 for ruleInfo2 in rule_info2(l_category_code1(l_count_code)) Loop
421 begin
422 l_date := to_date(ruleInfo2.rul2, l_format(l_count_format) );
423 if ( ruleInfo2.rul2 = to_char(l_date, l_format(l_count_format) ) ) then
424 Convert_Dates(p_category_code => l_category_code1(l_count_code),
425 p_format => l_format(l_count_format),
426 p_date => ruleInfo2.rul2,
427 p_rule_num => 2,
428 p_rule_id => ruleInfo2.rul_id,
429 x_return_status => x_return_status,
430 x_msg_data => x_msg_data,
431 x_msg_count => x_msg_count);
432 End If;
433 Exception
434 When Others Then
435 null;
436 -- no need to record any error messages here. This part is when the
437 -- date does not match the date format when calling to_date.
438 End;
439 End Loop;
440 l_count_format := l_count_format + 1;
441 End Loop;
442 l_count_code := l_count_code + 1;
443 End Loop;
444
445 l_count_code := 1;
446 While(l_count_code <= l_category_code2.COUNT) Loop
447 l_count_format := 1;
448 While(l_count_format <= l_format.COUNT) Loop
449 -----for ruleInfo3 in rule_info3_4(l_category_code2(l_count_code), l_format(l_count_format)) Loop
450 for ruleInfo3 in rule_info3_4(l_category_code2(l_count_code)) Loop
451 begin
452 l_date := to_date(ruleInfo3.rul3, l_format(l_count_format));
453 if ( ruleInfo3.rul3 = to_char(l_date, l_format(l_count_format) ) ) then
454 Convert_Dates(p_category_code => l_category_code2(l_count_code),
455 p_format => l_format(l_count_format),
456 p_date => ruleInfo3.rul3,
457 p_rule_num => 3,
458 p_rule_id => ruleInfo3.rul_id,
459 x_return_status => x_return_status,
460 x_msg_data => x_msg_data,
461 x_msg_count => x_msg_count);
462 End If;
463 if ( ruleInfo3.rul4 = to_char(l_date, l_format(l_count_format) ) ) then
464 Convert_Dates(p_category_code => l_category_code2(l_count_code),
465 p_format => l_format(l_count_format),
466 p_date => ruleInfo3.rul4,
467 p_rule_num => 4,
468 p_rule_id => ruleInfo3.rul_id,
469 x_return_status => x_return_status,
470 x_msg_data => x_msg_data,
471 x_msg_count => x_msg_count);
472 End If;
473
474 Exception
475 When Others Then
476 null;
477 -- no need to record any error messages here. This part is when the
478 -- date does not match the date format when calling to_date.
479 End;
480 End Loop;
481 l_count_format := l_count_format + 1;
482 End Loop;
483 l_count_code := l_count_code + 1;
484 End Loop;
485 commit;
486 Exception
487 When others then
488 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
489 Debug_Log(p_error_msg => SQLERRM,
490 x_msg_data => x_msg_data,
491 x_msg_count => x_msg_count,
492 x_return_status => x_return_status);
493
494 End Get_Dates;
495
496
497 Procedure Debug_Log(p_error_msg IN VARCHAR2,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_return_status OUT NOCOPY VARCHAR2) IS
501
502 l_file_name VARCHAR2(200);
503 l_file_loc BFILE;
504 l_file_type utl_file.file_type;
505 l_location VARCHAR2(32000);
506 l_comma_loc NUMBER;
507 l_error_msg VARCHAR2(32000) := p_error_msg;
508
509 cursor get_dir is
510 select value
511 from v$parameter
512 where name = 'utl_file_dir';
513
514 Begin
515 x_return_status := OKC_API.G_RET_STS_SUCCESS;
516
517 --If FND_PROFILE.VALUE('OKS_DEBUG') = 'Y' Then
518
519 l_file_name := 'ERM_' || to_char(sysdate, 'MM/DD/YYYY_HH24:MI:SS') || '_'
520 || '.out';
521
522 Open get_dir;
523 Fetch get_dir into l_location;
524 Close get_dir;
525
526 If l_location is not null Then
527 l_comma_loc := instr(l_location, ',');
528 If l_comma_loc <> 0 Then
529 l_location := substr(l_location, 1, l_comma_loc - 1);
530 End If;
531 End If;
532
533 l_file_type := utl_file.fopen(location => l_location,
534 filename => l_file_name,
535 open_mode => 'a');
536
537 utl_file.put_line(file => l_file_type,
538 buffer => l_error_msg );
539
540 utl_file.fflush(file => l_file_type);
541 utl_file.fclose(l_file_type);
542 -- End If;
543
544 Exception
545 when utl_file.INVALID_PATH then
546 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
547 x_msg_data := l_error_msg || ' Invalid Path';
548 x_msg_count:= 1;
549 OKC_API.set_message
550 (
551 G_APP_NAME,
552 G_UNEXPECTED_ERROR,
553 G_SQLCODE_TOKEN,
554 SQLCODE,
555 G_SQLERRM_TOKEN,
556 'Invalid path'
557 );
558 when utl_file.INVALID_OPERATION then
559 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
560 x_msg_data := l_error_msg || ' Invalid operation';
561 x_msg_count:= 1;
562 OKC_API.set_message
563 (
564 G_APP_NAME,
565 G_UNEXPECTED_ERROR,
566 G_SQLCODE_TOKEN,
567 SQLCODE,
568 G_SQLERRM_TOKEN,
569 'Invalid operation'
570 );
571
572 when others then
573 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
574 x_msg_data := l_error_msg || ' ' || SQLERRM;
575 x_msg_count:= 1;
576 OKC_API.set_message
577 (
578 G_APP_NAME,
579 G_UNEXPECTED_ERROR,
580 G_SQLCODE_TOKEN,
581 SQLCODE,
582 G_SQLERRM_TOKEN,
583 SQLERRM
584 );
585
586 End Debug_Log;
587
588
589 procedure split_range (
590 p_lo number,
591 p_hi number,
592 p_buckets number) is
593 l_lo number := p_lo;
594 l_idx1 number := range_arr.count + 1;
595 l_idx2 number := range_arr.count + p_buckets;
596 l_bucket_width integer;
597 begin
598 if p_buckets = 0 then
599 return;
600 end if;
601 if range_arr.count > 0 then
602 -- so we don't overlap high value of previous range
603 l_lo := p_lo + 1;
604 end if;
605
606 l_bucket_width := (p_hi - l_lo) / p_buckets;
607
608 range_arr.extend(p_buckets);
609 for idx in l_idx1..l_idx2 loop
610 range_arr(idx).lo := l_lo + ((idx - l_idx1) * l_bucket_width);
611 if idx < l_idx2 then
612 range_arr(idx).hi := range_arr(idx).lo + l_bucket_width -1;
613 else
614 range_arr(idx).hi := p_hi;
615 end if;
616 end loop;
617
618 end split_range;
619
620
621 function generate_ranges (
622 p_lo number,
623 p_hi number,
624 p_avg number,
625 p_stddev number,
626 p_total number) return integer is
627 l_total_buckets integer := 0;
628
629 l_stdlo number := greatest(round(p_avg - p_stddev), p_lo);
630 l_stdhi number := least(round(p_avg + p_stddev), p_hi);
631 l_stddev_percent number := 0.66; -- the area covered by +/-1 stddev
632 l_outlier_buckets integer := 0;
633 l_std_buckets integer := 0;
634 l_lo_buckets integer := 0;
635 l_hi_buckets integer := 0;
636 l_outlier_entries_per_bucket number := 0;
637 modidx integer;
638
639 begin
640 range_arr := rangeArray();
641 -- number of buckets is set to 20
642 l_total_buckets := least(MAX_JOBS,p_hi - p_lo);
643
644 l_outlier_buckets := l_total_buckets * (1 - l_stddev_percent);
645 if l_outlier_buckets > 0 then
646 l_outlier_entries_per_bucket := p_total * (1 - l_stddev_percent)
647 / l_outlier_buckets ;
648 end if;
649 for idx in 1..l_outlier_buckets loop
650 modidx := mod(idx,2);
651 -- alternate assignment between hi and lo buckets
652 if modidx = 1
653 AND (p_hi - (l_hi_buckets+1) * l_outlier_entries_per_bucket)
654 > l_stdhi then
655 -- allocate buckets for positive outliers
656 l_hi_buckets := l_hi_buckets + 1;
657 elsif modidx = 0
658 AND (p_lo + (l_lo_buckets+1) * l_outlier_entries_per_bucket)
659 < l_stdlo then
660 -- allocate buckets for negative outliers
661 l_lo_buckets := l_lo_buckets + 1;
662 -- else min or max has been consumed, save bucket for middle
663 end if;
664 end loop;
665
666 -- compute middle buckets
667 l_std_buckets := l_total_buckets - l_lo_buckets - l_hi_buckets;
668
669 -- in case low-high allocations yielded zero buckets.
670 -- i.e., outliers were folded into middle buckets.
671 if l_lo_buckets = 0 then
672 l_stdlo := p_lo;
673 end if;
674 if l_hi_buckets = 0 then
675 l_stdhi := p_hi;
676 end if;
677
678
679 -- ranges for negative outliers
680 split_range(p_lo, l_stdlo, l_lo_buckets);
681 -- ranges for +/-1 stddev from mean
682 split_range(l_stdlo, l_stdhi, l_std_buckets);
683 -- ranges for positive outliers
684 split_range(l_stdhi, p_hi, l_hi_buckets);
685
686 return l_total_buckets;
687 end generate_ranges;
688
689
690 PROCEDURE upgrade_rule_dates
691 (
692 x_return_status OUT NOCOPY VARCHAR2
693 )
694 IS
695
696 cursor l_csp_agg_csr is
697 select min(id) minid, max(id) maxid,
698 count(id) total,
699 avg(id) avgid, stddev(id) stdid
700 from okc_rule_groups_b
701 where rgd_code = 'SVC_K';
702
703 cursor l_jobs_csr(l_job number) is
704 select count(*)
705 from user_jobs
706 where job = l_job;
707
708 l_agg_rec l_csp_agg_csr%ROWTYPE;
709 l_subrequests integer;
710 l_ret integer;
711 l_job_count integer := 0;
712
713 BEGIN
714 X_return_status := 'S';
715
716 open l_csp_agg_csr;
717 fetch l_csp_agg_csr into l_agg_rec;
718 close l_csp_agg_csr;
719
720 -- populate lo,hi varrays
721 l_subrequests :=
722 generate_ranges(l_agg_rec.minid, l_agg_rec.maxid, l_agg_rec.avgid,
723 l_agg_rec.stdid, l_agg_rec.total);
724
725 for idx in 1..l_subrequests loop
726
727 ----errorout('1 range '||range_arr(idx).lo||'hi '||range_arr(idx).hi);
728 dbms_job.submit(range_arr(idx).jobno,
729 'OKS_INTEGRATION_UTIL_PUB.get_dates(' ||
730 range_arr(idx).lo ||','|| range_arr(idx).hi ||');',
731 instance => get_instance_id);
732 commit;
733
734 end loop;
735
736 loop
737 for idx in 1..l_subrequests loop
738 open l_jobs_csr(range_arr(idx).jobno);
739 fetch l_jobs_csr into l_job_count;
740 close l_jobs_csr;
741 if l_job_count > 0 then
742 exit;
743 end if;
744 end loop;
745 if l_job_count > 0 then
746 dbms_lock.sleep(60);
747 else
748 exit;
749 end if;
750 end loop;
751
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 ---errorout('spawn jobs '||sqlerrm);
756 --dbms_output.put_line(SQLERRM);
757 X_return_status := 'E';
758
759 END upgrade_rule_dates;
760
761
762
763
764 END OKS_INTEGRATION_UTIL_PUB;