1 PACKAGE BODY QLTCONTB as
2 /* $Header: qltcontb.plb 120.2 2006/04/10 16:11:20 bso noship $ */
3
4 -- 3/19/96 - CREATED
5 -- Jacqueline Chang
6
7 TYPE numbertable is TABLE OF NUMBER
8 INDEX BY BINARY_INTEGER;
9
10 PROCEDURE X_BAR_R (sql_string VARCHAR2,
11 subgrp_size NUMBER,
12 num_subgroups IN OUT NOCOPY NUMBER,
13 dec_prec NUMBER,
14 grand_mean IN OUT NOCOPY NUMBER,
15 range_average IN OUT NOCOPY NUMBER,
16 UCL OUT NOCOPY NUMBER,
17 LCL OUT NOCOPY NUMBER,
18 R_UCL OUT NOCOPY NUMBER,
19 R_LCL OUT NOCOPY NUMBER,
20 not_enough_data OUT NOCOPY NUMBER,
21 compute_new_limits IN BOOLEAN default FALSE) IS
22
23 results_cursor INTEGER;
24 result NUMBER;
25 ignore INTEGER;
26 i INTEGER;
27 temp_results numbertable;
28 rows_retrieved INTEGER;
29 num_points INTEGER;
30 first_point INTEGER;
31 have_to_round BOOLEAN := FALSE;
32 -- have_to_round is true if we have to perform a MOD operation
33 -- to make sure the number of points we're using is a multiple of
34 -- the subgroup size
35
36 subgroup_number INTEGER;
37 average_table numbertable;
38 range_table numbertable;
39 maximum NUMBER;
40 minimum NUMBER;
41 total NUMBER;
42
43 grand_mean_sum NUMBER := 0;
44 range_sum NUMBER := 0;
45
46 -- variables to hold constants for computing control limits
47 A2 NUMBER;
48 D3 NUMBER;
49 D4 NUMBER;
50
51
52 BEGIN
53 results_cursor := dbms_sql.open_cursor;
54 dbms_sql.parse (results_cursor, sql_string, dbms_sql.v7);
55 dbms_sql.define_column (results_cursor, 1, result);
56
57 ignore := dbms_sql.execute(results_cursor);
58
59 i := 0;
60 LOOP
61 IF dbms_sql.fetch_rows (results_cursor) > 0 THEN
62 i := i + 1;
63 dbms_sql.column_value(results_cursor, 1, result);
64
65 temp_results(i) := result;
66
67 ELSE
68 -- no more results to fetch
69 exit;
70 END IF;
71 END LOOP;
72 dbms_sql.close_cursor(results_cursor);
73
74 rows_retrieved := i;
75 IF rows_retrieved = 0 THEN
76 -- no results entered
77 not_enough_data := 1;
78 RETURN;
79 END IF;
80
81 -- determine the first data point to use
82 IF num_subgroups IS NOT NULL THEN
83 num_points := subgrp_size * num_subgroups;
84 first_point := rows_retrieved - num_points + 1;
85 IF first_point < 1 THEN
86 -- user specified more points than there are data points
87 -- should give user a message here.
88 have_to_round := TRUE;
89 END IF;
90 ELSE
91 -- user didn't specify number of subgroups to use; use all data
92 have_to_round := TRUE;
93 END IF;
94
95 IF have_to_round THEN
96 -- this is only true when using all data points; mod using
97 -- rows_retrieved
98 num_subgroups := TRUNC (rows_retrieved / subgrp_size);
99 IF num_subgroups < 1 THEN
100 not_enough_data := 1;
101 RETURN;
102 END IF;
103
104 first_point := MOD (rows_retrieved, subgrp_size) + 1;
105 END IF;
106
107 i := first_point;
108 subgroup_number := 1;
109 WHILE i <= rows_retrieved LOOP
110 total := 0;
111 maximum := temp_results(i);
112 minimum := temp_results(i);
113
114 FOR j IN 1..subgrp_size LOOP
115 total := total + temp_results(i);
116 IF temp_results(i) > maximum THEN
117 maximum := temp_results(i);
118 END IF;
119 IF temp_results(i) < minimum THEN
120 minimum := temp_results(i);
121 END IF;
122
123 i := i + 1;
124
125 END LOOP; -- for loop
126
127 -- the following computations are performed per subgroup
128 average_table(subgroup_number) := total / subgrp_size;
129 range_table(subgroup_number) := maximum - minimum;
130
131 subgroup_number := subgroup_number + 1;
132
133 END LOOP; -- while loop
134
135
136 IF (NOT compute_new_limits) THEN
137 -- want to populate qa_chart_data with latest data
138 FOR j IN 1..num_subgroups LOOP
139 INSERT INTO QA_CHART_DATA
140 (LAST_UPDATE_DATE,
141 LAST_UPDATED_BY,
142 CREATION_DATE,
143 CREATED_BY,
144 LAST_UPDATE_LOGIN,
145 SUBGROUP_NUMBER,
146 AVERAGE,
147 RANGE,
148 BAR_NUMBER,
149 HIST_RANGE,
150 NUM_OCCURRENCES)
151 VALUES
152 (SYSDATE,
153 1,
154 SYSDATE,
155 1,
156 NULL,
157 j,
158 average_table(j),
159 range_table(j),
160 null,
161 null,
162 null);
163 END LOOP;
164
165 ELSE
166 -- compute new limits.
167
168 FOR j IN 1..num_subgroups LOOP
169 grand_mean_sum := grand_mean_sum + average_table(j);
170 range_sum := range_sum + range_table(j);
171 END LOOP;
172
173 grand_mean := grand_mean_sum / num_subgroups;
174 range_average := range_sum / num_subgroups;
175
176 -- compute the control limits
177 SELECT A2, D3, D4 INTO A2, D3, D4
178 FROM QA_CHART_CONSTANTS
179 WHERE SUBGROUP_SIZE = subgrp_size;
180
181 UCL := round(grand_mean + (A2 * range_average), dec_prec);
182 LCL := round(grand_mean - (A2 * range_average), dec_prec);
183
184 R_UCL := round(D4 * range_average, dec_prec);
185 R_LCL := round(D3 * range_average, dec_prec);
186
187 grand_mean := round(grand_mean, dec_prec);
188 range_average := round(range_average, dec_prec);
189
190 END IF;
191
192 END X_BAR_R;
193
194
195 PROCEDURE X_BAR_S (sql_string VARCHAR2,
196 subgrp_size NUMBER,
197 num_subgroups IN OUT NOCOPY NUMBER,
198 dec_prec NUMBER,
199 grand_mean IN OUT NOCOPY NUMBER,
200 std_dev_average IN OUT NOCOPY NUMBER,
201 UCL OUT NOCOPY NUMBER,
202 LCL OUT NOCOPY NUMBER,
203 R_UCL OUT NOCOPY NUMBER,
204 R_LCL OUT NOCOPY NUMBER,
205 not_enough_data OUT NOCOPY NUMBER,
206 compute_new_limits IN BOOLEAN default FALSE) IS
207
208 results_cursor INTEGER;
209 result NUMBER;
210 ignore INTEGER;
211 i INTEGER;
212 temp_results numbertable;
213 rows_retrieved INTEGER;
214 num_points INTEGER;
215 first_point INTEGER;
216 have_to_round BOOLEAN := FALSE;
217 -- have_to_round is true if we have to perform a MOD operation
218 -- to make sure the number of points we're using is a multiple of
219 -- the subgroup size
220
221 subgroup_number INTEGER;
222 average_table numbertable;
223 std_dev_table numbertable;
224 total NUMBER;
225 sum_of_squares NUMBER;
226
227 grand_mean_sum NUMBER := 0;
228 std_dev_sum NUMBER := 0;
229
230 -- variables to hold constants for computing control limits
231 A1 NUMBER;
232 B3 NUMBER;
233 B4 NUMBER;
234
235
236 BEGIN
237 results_cursor := dbms_sql.open_cursor;
238 dbms_sql.parse (results_cursor, sql_string, dbms_sql.v7);
239 dbms_sql.define_column (results_cursor, 1, result);
240
241 ignore := dbms_sql.execute(results_cursor);
242
243 i := 0;
244 LOOP
245 IF dbms_sql.fetch_rows (results_cursor) > 0 THEN
246 i := i + 1;
247 dbms_sql.column_value(results_cursor, 1, result);
248
249 temp_results(i) := result;
250
251 ELSE
252 -- no more results to fetch
253 exit;
254 END IF;
255 END LOOP;
256 dbms_sql.close_cursor(results_cursor);
257
258 rows_retrieved := i;
259
260 -- determine the first data point to use
261 IF num_subgroups IS NOT NULL THEN
262 num_points := subgrp_size * num_subgroups;
263 first_point := rows_retrieved - num_points + 1;
264 IF first_point < 1 THEN
265 -- user specified more points than there are data points
266 -- should give user a message here.
267 have_to_round := TRUE;
268 END IF;
269 ELSE
270 -- user didn't specify number of subgroups to use; use all data
271 have_to_round := TRUE;
272 END IF;
273
274 IF have_to_round THEN
275 -- this is only true when using all data points; mod using
276 -- rows_retrieved
277 num_subgroups := TRUNC (rows_retrieved / subgrp_size);
278 IF num_subgroups < 1 THEN
279 not_enough_data := 1;
280 RETURN;
281 END IF;
282
283 first_point := MOD (rows_retrieved, subgrp_size) + 1;
284 END IF;
285
286 i := first_point;
287 subgroup_number := 1;
288 WHILE i <= rows_retrieved LOOP
289 total := 0;
290 sum_of_squares := 0;
291
292 FOR j IN 1..subgrp_size LOOP
293 total := total + temp_results(i);
294 sum_of_squares := sum_of_squares + (temp_results(i) * temp_results(i));
295
296 i := i + 1;
297
298 END LOOP; -- for loop
299
300 -- the following computations are performed per subgroup
301 average_table(subgroup_number) := round(total / subgrp_size, dec_prec);
302
303 std_dev_table(subgroup_number) :=
304 round(sqrt(((subgrp_size*sum_of_squares) - (total * total))/
305 (subgrp_size * (subgrp_size - 1))), dec_prec);
306
307 subgroup_number := subgroup_number + 1;
308
309 END LOOP; -- while loop
310
311 IF (NOT compute_new_limits) THEN
312 -- want to populate qa_chart_data with latest data
313 FOR j IN 1..num_subgroups LOOP
314 INSERT INTO QA_CHART_DATA
315 (LAST_UPDATE_DATE,
316 LAST_UPDATED_BY,
317 CREATION_DATE,
318 CREATED_BY,
319 LAST_UPDATE_LOGIN,
320 SUBGROUP_NUMBER,
321 AVERAGE,
322 RANGE,
323 BAR_NUMBER,
324 HIST_RANGE,
325 NUM_OCCURRENCES)
326 VALUES
327 (SYSDATE,
328 1,
329 SYSDATE,
330 1,
331 NULL,
332 j,
333 average_table(j),
334 std_dev_table(j),
335 null,
336 null,
337 null);
338 END LOOP;
339
340 ELSE
341 -- compute new limits.
342
343 FOR j IN 1..num_subgroups LOOP
344 grand_mean_sum := grand_mean_sum + average_table(j);
345 std_dev_sum := std_dev_sum + std_dev_table(j);
346 END LOOP;
347
348 grand_mean := grand_mean_sum / num_subgroups;
349 std_dev_average := std_dev_sum / num_subgroups;
350
351 -- compute the control limits
352 SELECT A1, B3, B4 INTO A1, B3, B4
353 FROM QA_CHART_CONSTANTS
354 WHERE SUBGROUP_SIZE = subgrp_size;
355
356 UCL := round(grand_mean + (A1 * std_dev_average), dec_prec);
357 LCL := round(grand_mean - (A1 * std_dev_average), dec_prec);
358
359 R_UCL := round(B4 * std_dev_average, dec_prec);
360 R_LCL := round(B3 * std_dev_average, dec_prec);
361
362 grand_mean := round(grand_mean, dec_prec);
363 std_dev_average := round(std_dev_average, dec_prec);
364
365 END IF;
366
367 END X_BAR_S;
368
369 PROCEDURE XmR (sql_string VARCHAR2,
370 subgrp_size NUMBER,
371 num_points IN OUT NOCOPY NUMBER,
372 dec_prec NUMBER,
373 grand_mean IN OUT NOCOPY NUMBER,
374 range_average IN OUT NOCOPY NUMBER,
375 UCL OUT NOCOPY NUMBER,
376 LCL OUT NOCOPY NUMBER,
377 R_UCL OUT NOCOPY NUMBER,
378 R_LCL OUT NOCOPY NUMBER,
379 not_enough_data OUT NOCOPY NUMBER,
380 compute_new_limits IN BOOLEAN default FALSE) IS
381
382 results_cursor INTEGER;
383 result NUMBER;
384 ignore INTEGER;
385 i INTEGER;
386 j INTEGER;
387 temp_results numbertable;
388 rows_retrieved INTEGER;
389 first_x_point INTEGER;
390 first_range_point INTEGER;
391
392 min_table numbertable;
393 max_table numbertable;
394 range_table numbertable;
395
396 grand_mean_sum NUMBER := 0;
397 range_sum NUMBER := 0;
398
399 -- variables to hold constants for computing control limits
400 D3 NUMBER;
401 D4 NUMBER;
402 E2 NUMBER;
403
404
405 BEGIN
406
407 results_cursor := dbms_sql.open_cursor;
408 dbms_sql.parse (results_cursor, sql_string, dbms_sql.v7);
409 dbms_sql.define_column (results_cursor, 1, result);
410
411 ignore := dbms_sql.execute(results_cursor);
412
413 i := 0;
414 LOOP
415 IF dbms_sql.fetch_rows (results_cursor) > 0 THEN
416 i := i + 1;
417 dbms_sql.column_value(results_cursor, 1, result);
418
419 temp_results(i) := result;
420
421 ELSE
422 -- no more results to fetch
423 exit;
424 END IF;
425 END LOOP;
426 dbms_sql.close_cursor(results_cursor);
427
428 rows_retrieved := i;
429
430 -- determine the first data point to use
431 IF num_points IS NOT NULL THEN
432 first_x_point := rows_retrieved - num_points + 1;
433 IF first_x_point < 1 THEN
434 -- user specified more points than there are data points
435 -- should give user a message here.
436 first_x_point := 1;
437 END IF;
438 ELSE
439 first_x_point := 1;
440 END IF;
441
442 first_range_point := first_x_point + subgrp_size - 1;
443
444 IF first_range_point > rows_retrieved THEN
445 -- not enough data to chart what the user wants to
446 not_enough_data := 1;
447 return;
448 END IF;
449
450 -- populate the first subgrp_size - 1 points with NULL for range. We need
451 -- to do this so that later we can sum the total properly (see below)
452 FOR k IN first_x_point..first_range_point-1 LOOP
453 range_table(k) := NULL;
454 END LOOP;
455
456 IF subgrp_size = 2 THEN
457 FOR j IN first_range_point.. rows_retrieved LOOP
458 range_table(j) := ABS(temp_results(j) - temp_results(j-1));
459 END LOOP;
460
461 ELSE
462 -- subgrp_size > 2. Have to keep around maxs, mins.
463 i := first_x_point;
464 j := first_range_point;
465 -- the first subgroup will be the points between the first x point
466 -- and the first range point. We will increment each of these every
467 -- time we go through the loop (we have a moving `window' of points
468 -- we use for the moving range).
469
470 -- we need to treat the first subgroup differently than the rest because
471 -- there is no `discarded' point from a previous subgroup
472
473 max_table(j) := temp_results(j);
474 min_table(j) := temp_results(j);
475 FOR k IN i..j LOOP
476 IF temp_results(k) > max_table(j) THEN
477 max_table(j) := temp_results(k);
478 END IF;
479 IF temp_results(k) < min_table(j) THEN
480 min_table(j) := temp_results(k);
481 END IF;
482 END LOOP;
483
484 range_table(j) := max_table(j) - min_table(j);
485
486 i := i + 1;
487 j := j + 1;
488
489 WHILE j <= rows_retrieved LOOP
490 min_table(j) := min_table(j - 1);
491 max_table(j) := max_table(j - 1);
492
493 IF temp_results(i - 1) = min_table(j - 1) THEN
494 min_table(j) := temp_results(i);
495 FOR k IN i+1..j LOOP
496 IF temp_results(k) < min_table(j) THEN
497 min_table(j) := temp_results(k);
498 END IF;
499 END LOOP;
500
501 ELSIF temp_results(i - 1) = max_table(j - 1) THEN
502 max_table(j) := temp_results(i);
503 FOR k IN i+1..j LOOP
504 IF temp_results(k) > max_table(j) THEN
505 max_table(j) := temp_results(k);
506 END IF;
507 END LOOP;
508 END IF;
509
510 -- check out the new point.
511 IF temp_results(j) < min_table(j) THEN
512 min_table(j) := temp_results(j);
513 END IF;
514
515 IF temp_results(j) > max_table(j) THEN
516 max_table(j) := temp_results(j);
517 END IF;
518
519 -- now we have the new maxs and mins. can compute the range.
520 range_table(j) := max_table(j) - min_table(j);
521
522 i := i + 1;
523 j := j + 1;
524
525 END LOOP; -- while loop
526
527 END IF;
528
529 IF (NOT compute_new_limits) THEN
530 -- want to populate qa_chart_data with latest data
531 FOR k IN first_x_point..rows_retrieved LOOP
532
533 INSERT INTO QA_CHART_DATA
534 (LAST_UPDATE_DATE,
535 LAST_UPDATED_BY,
536 CREATION_DATE,
537 CREATED_BY,
538 LAST_UPDATE_LOGIN,
539 SUBGROUP_NUMBER,
540 AVERAGE,
541 RANGE,
542 BAR_NUMBER,
543 HIST_RANGE,
544 NUM_OCCURRENCES)
545 VALUES
546 (SYSDATE,
547 1,
548 SYSDATE,
549 1,
550 NULL,
551 k,
552 temp_results(k),
553 range_table(k),
554 null,
555 null,
556 null);
557 END LOOP;
558
559 ELSE
560 -- we're computing new limits.
561
562 FOR k IN first_x_point..rows_retrieved LOOP
563 grand_mean_sum := grand_mean_sum + temp_results(k);
564 range_sum := range_sum + NVL(range_table(k), 0);
565 END LOOP;
566
567 grand_mean := grand_mean_sum / (rows_retrieved - first_x_point + 1);
568 range_average := range_sum / (rows_retrieved - first_range_point + 1);
569
570 -- compute the control limits
571
572 SELECT D3, D4, E2 INTO D3, D4, E2
573 FROM QA_CHART_CONSTANTS
574 WHERE SUBGROUP_SIZE = subgrp_size;
575
576 UCL := round(grand_mean + (E2 * range_average), dec_prec);
577 LCL := round(grand_mean - (E2 * range_average), dec_prec);
578
579 R_UCL := round(D4 * range_average, dec_prec);
580 R_LCL := round(D3 * range_average, dec_prec);
581
582 grand_mean := round(grand_mean, dec_prec);
583 range_average := round(range_average, dec_prec);
584
585 END IF;
586
587 END XmR;
588
589
590 PROCEDURE mXmR (sql_string VARCHAR2,
591 subgrp_size NUMBER,
592 num_points IN OUT NOCOPY NUMBER,
593 dec_prec NUMBER,
594 grand_mean IN OUT NOCOPY NUMBER,
595 range_average IN OUT NOCOPY NUMBER,
596 UCL OUT NOCOPY NUMBER,
597 LCL OUT NOCOPY NUMBER,
598 R_UCL OUT NOCOPY NUMBER,
599 R_LCL OUT NOCOPY NUMBER,
600 not_enough_data OUT NOCOPY NUMBER,
601 compute_new_limits IN BOOLEAN default FALSE) IS
602
603 results_cursor INTEGER;
604 result NUMBER;
605 ignore INTEGER;
606 i INTEGER;
607 j INTEGER;
608 temp_results numbertable;
609 rows_retrieved INTEGER;
610 first_point INTEGER;
611
612 min_table numbertable;
613 max_table numbertable;
614 average_table numbertable;
615 range_table numbertable;
616
617 temp_total NUMBER := 0;
618 grand_mean_sum NUMBER := 0;
619 range_sum NUMBER := 0;
620
621 -- variables to hold constants for computing control limits
622 A2 NUMBER;
623 D3 NUMBER;
624 D4 NUMBER;
625
626
627 BEGIN
628
629 --delete from jc_temp;
630 --commit;
631
632 --insert into jc_temp values (sql_string);
633 --commit;
634 results_cursor := dbms_sql.open_cursor;
635 dbms_sql.parse (results_cursor, sql_string, dbms_sql.v7);
636 dbms_sql.define_column (results_cursor, 1, result);
637
638 ignore := dbms_sql.execute(results_cursor);
639
640 i := 0;
641 LOOP
642 IF dbms_sql.fetch_rows (results_cursor) > 0 THEN
643 i := i + 1;
644 dbms_sql.column_value(results_cursor, 1, result);
645
646 temp_results(i) := result;
647
648 --insert into jc_temp values ('temp_results(i): ' || to_char(temp_results(i)));
649 --commit;
650
651 ELSE
652 -- no more results to fetch
653 exit;
654 END IF;
655 END LOOP;
656 dbms_sql.close_cursor(results_cursor);
657
658 rows_retrieved := i;
659
660 --insert into jc_temp values ('rows_retrieved: ' || to_char (i));
661 --commit;
662
663 -- determine the first data point to use
664 IF num_points IS NOT NULL THEN
665 first_point := rows_retrieved - num_points + 1;
666 IF first_point < 1 THEN
667 -- user specified more points than there are data points
668 -- should give user a message here.
669 first_point := 1;
670 END IF;
671 ELSE
672 first_point := 1;
673 END IF;
674
675 -- figure out the first point to actually do computations on
676 first_point := first_point + subgrp_size - 1;
677
678 IF first_point > rows_retrieved THEN
679 -- not enough data to chart what the user wants to
680 not_enough_data := 1;
681 RETURN;
682 END IF;
683
684 IF subgrp_size = 2 THEN
685 FOR j IN first_point..rows_retrieved LOOP
686 average_table(j) := ((temp_results(j) + temp_results(j-1)) / 2);
687 range_table(j) := ABS(temp_results(j) - temp_results(j-1));
688 END LOOP;
689
690 ELSE
691 -- subgrp_size > 2. Have to keep around maxs, mins.
692 i := first_point - subgrp_size + 1;
693 j := first_point;
694
695 -- we need to treat the first subgroup differently than the rest because
696 -- there is no `discarded' point from a previous subgroup
697
698 max_table(j) := temp_results(j);
699 min_table(j) := temp_results(j);
700 FOR k IN i..j LOOP
701 temp_total := temp_total + temp_results(k);
702
703 IF temp_results(k) > max_table(j) THEN
704 max_table(j) := temp_results(k);
705 END IF;
706 IF temp_results(k) < min_table(j) THEN
707 min_table(j) := temp_results(k);
708 END IF;
709 END LOOP;
710
711 average_table(j) := round(temp_total/subgrp_size, dec_prec);
712 range_table(j) := max_table(j) - min_table(j);
713
714 i := i + 1;
715 j := j + 1;
716
717 WHILE j <= rows_retrieved LOOP
718 -- update the sum
719 temp_total := temp_total - temp_results(i - 1) + temp_results(j);
720
721 -- now do the range stuff
722 min_table(j) := min_table(j - 1);
723 max_table(j) := max_table(j - 1);
724
725 IF temp_results(i - 1) = min_table(j - 1) THEN
726 min_table(j) := temp_results(i);
727 FOR k IN i+1..j LOOP
728 IF temp_results(k) < min_table(j) THEN
729 min_table(j) := temp_results(k);
730 END IF;
731 END LOOP;
732
733 ELSIF temp_results(i - 1) = max_table(j - 1) THEN
734 max_table(j) := temp_results(i);
735 FOR k IN i+1..j LOOP
736 IF temp_results(k) > max_table(j) THEN
737 max_table(j) := temp_results(k);
738 END IF;
739 END LOOP;
740 END IF;
741
742 -- check out the new point.
743 IF temp_results(j) < min_table(j) THEN
744 min_table(j) := temp_results(j);
745 END IF;
746
747 IF temp_results(j) > max_table(j) THEN
748 max_table(j) := temp_results(j);
749 END IF;
750
751 -- now we have all the new data. can compute average, range.
752 average_table(j) := round(temp_total/subgrp_size, dec_prec);
753 range_table(j) := max_table(j) - min_table(j);
754
755 i := i + 1;
756 j := j + 1;
757
758 END LOOP; -- while loop
759
760
761 /* FOR k IN first_point..rows_retrieved LOOP
762 insert into jc_temp values ('min_table(' || k || '): ' || to_char(min_table(k)));
763 insert into jc_temp values ('max_table(' || k || '): ' || to_char(max_table(k)));
764 insert into jc_temp values ('average_table(' || k || '): ' || to_char(average_table(k)));
765 insert into jc_temp values ('range_table(' || k || '): ' || to_char(range_table(k)));
766 commit;
767 END LOOP;
768 */
769
770 END IF;
771
772 IF (NOT compute_new_limits) THEN
773 -- want to populate qa_chart_data with latest data
774 FOR k IN first_point..rows_retrieved LOOP
775
776 INSERT INTO QA_CHART_DATA
777 (LAST_UPDATE_DATE,
778 LAST_UPDATED_BY,
779 CREATION_DATE,
780 CREATED_BY,
781 LAST_UPDATE_LOGIN,
782 SUBGROUP_NUMBER,
783 AVERAGE,
784 RANGE,
785 BAR_NUMBER,
786 HIST_RANGE,
787 NUM_OCCURRENCES)
788 VALUES
789 (SYSDATE,
790 1,
791 SYSDATE,
792 1,
793 NULL,
794 k,
795 average_table(k),
796 range_table(k),
797 null,
798 null,
799 null);
800 END LOOP;
801
802 ELSE
803
804 -- compute new limits.
805
806 FOR k IN first_point..rows_retrieved LOOP
807 grand_mean_sum := grand_mean_sum + average_table(k);
808 range_sum := range_sum + range_table(k);
809 END LOOP;
810
811 grand_mean :=
812 round(grand_mean_sum / (rows_retrieved - first_point + 1), dec_prec);
813 range_average :=
814 round(range_sum / (rows_retrieved - first_point + 1), dec_prec);
815
816 -- compute the control limits
817
818 SELECT A2, D3, D4 INTO A2, D3, D4
819 FROM QA_CHART_CONSTANTS
820 WHERE SUBGROUP_SIZE = subgrp_size;
821
822 UCL := round(grand_mean + (A2 * range_average), dec_prec);
823 LCL := round(grand_mean - (A2 * range_average), dec_prec);
824
825 R_UCL := round(D4 * range_average, dec_prec);
826 R_LCL := round(D3 * range_average, dec_prec);
827
828 END IF;
829
830 END mXmR;
831
832
833 -- Bug 5130547. Boundary condition issue. If num_points is 1,
834 -- cp and cpk are undefined. Their OUT values will remain the
835 -- same as IN value, presumably NULL, in that case.
836 -- bso Mon Apr 10 15:38:15 PDT 2006
837
838 PROCEDURE HISTOGRAM (sql_string VARCHAR2,
839 num_points IN OUT NOCOPY NUMBER,
840 dec_prec NUMBER,
841 num_bars IN OUT NOCOPY NUMBER,
842 USL IN NUMBER,
843 LSL IN NUMBER,
844 cp IN OUT NOCOPY NUMBER,
845 cpk IN OUT NOCOPY NUMBER,
846 not_enough_data OUT NOCOPY NUMBER) IS
847
848 results_cursor INTEGER;
849 result NUMBER;
850 ignore INTEGER;
851 i INTEGER;
852 j INTEGER;
853 temp_results numbertable;
854 rows_retrieved INTEGER;
855 first_point INTEGER;
856
857 maximum NUMBER;
858 minimum NUMBER;
859 range NUMBER;
860 step_size NUMBER;
861 curr NUMBER;
862
863 num_points_used INTEGER;
864 total NUMBER;
865 mean NUMBER;
866 sum_of_squares NUMBER;
867 s NUMBER;
868
869 boundary numbertable; -- holds boundary values of bars
870 occurrences numbertable;
871
872 BEGIN
873 results_cursor := dbms_sql.open_cursor;
874 dbms_sql.parse (results_cursor, sql_string, dbms_sql.v7);
875 dbms_sql.define_column (results_cursor, 1, result);
876
877 ignore := dbms_sql.execute(results_cursor);
878
879 i := 0;
880 LOOP
881 IF dbms_sql.fetch_rows (results_cursor) > 0 THEN
882 i := i + 1;
883 dbms_sql.column_value(results_cursor, 1, result);
884
885 temp_results(i) := result;
886 ELSE
887 -- no more results to fetch
888 exit;
889 END IF;
890 END LOOP;
891 dbms_sql.close_cursor(results_cursor);
892
893 rows_retrieved := i;
894
895 --
896 -- Bug 5130638 The num_points variable was not correctly
897 -- reset if it was bigger than the no. of rows retrieved.
898 -- IF..THEN now rewritten to properly set its value.
899 -- bso Mon Apr 10 16:08:21 PDT 2006
900 --
901 -- determine the first data point to use
902 IF num_points IS NOT NULL AND num_points <= rows_retrieved THEN
903 first_point := rows_retrieved - num_points + 1;
904 ELSE
905 first_point := 1;
906 num_points := rows_retrieved;
907 END IF;
908
909 IF first_point > rows_retrieved THEN
910 -- not enough data to chart what the user wants to
911 not_enough_data := 1;
912 RETURN;
913 END IF;
914
915 num_points_used := rows_retrieved - first_point + 1;
916
917 maximum := temp_results(first_point);
918 minimum := temp_results(first_point);
919 FOR i IN first_point+1..rows_retrieved LOOP
920 IF temp_results(i) > maximum THEN
921 maximum := temp_results(i);
922 END IF;
923 IF temp_results(i) < minimum THEN
924 minimum := temp_results(i);
925 END IF;
926 END LOOP;
927
928 range := maximum - minimum;
929 IF num_bars IS NULL THEN
930 num_bars := round(sqrt(num_points));
931 END IF;
932 step_size := range/num_bars;
933
934 curr := minimum;
935 FOR i IN 1..num_bars-1 LOOP
936 boundary(i) := round(curr + step_size, dec_prec);
937 curr := curr + step_size;
938 occurrences(i) := 0;
939 END LOOP;
940
941 -- to accommodate any rounding errors
942 boundary(num_bars) := maximum;
943 occurrences(num_bars) := 0;
944
945 total := 0;
946 sum_of_squares := 0;
947 FOR i IN first_point..rows_retrieved LOOP
948 -- keep running totals of sum of results and sum of squares, to be
949 -- used later in computing cp, cpk
950 total := total + temp_results(i);
951 sum_of_squares := sum_of_squares + (temp_results(i) * temp_results(i));
952
953 j := 1;
954 WHILE boundary(j) <= temp_results(i) LOOP
955 -- note that the intervals are inclusive on the lower end and
956 -- exclusive on the upper end. thus, the comparison is the way
957 -- it is (for the while loop).
958
959 -- if j = num_bars, we're looking at the maximum. Don't increment
960 -- j any more.
961 IF j = num_bars THEN
962 EXIT;
963 ELSE
964 j := j + 1;
965 END IF;
966 END LOOP;
967 occurrences(j) := occurrences(j) + 1;
968 END LOOP;
969
970 --
971 -- Bug 5130547. Boundary condition check. If num_points_used = 1
972 -- then standard deviation s is undefined. Nor is cp and cpk.
973 -- Let them remain null and skip computation. Mean can be computed
974 -- so extracted the mean calculation to outside the IF. Side note:
975 -- cp and cpk are discarded as dummies even though they are returned
976 -- to the calling procedure. I assume this is for future use. The
977 -- cp and cpk displayed in Descriptive Stats page is calculated by
978 -- QLTSCMDF.fmb CONTROL_PRIVATE.run_stats program.
979 -- bso Mon Apr 10 15:10:31 PDT 2006
980 --
981
982 mean := round(total/num_points_used, dec_prec);
983 IF num_points_used > 1 THEN
984 -- compute cp, cpk. First compute s, the estimator of sigma.
985 s := sqrt(((num_points_used * sum_of_squares) - (total * total))/
986 ((num_points_used) * (num_points_used - 1)));
987
988 IF (USL IS NULL AND LSL IS NULL) THEN
989 -- cp, cpk don't make sense
990 null;
991 ELSIF (USL IS NULL) THEN
992 -- one-sided spec limit. cp doesn't make sense in this case, so only
993 -- compute cpk.
994 cpk := round((mean - LSL)/(3 * s), 2);
995 ELSIF (LSL IS NULL) THEN
996 cpk := round((USL - mean)/(3 * s), 2);
997 ELSE
998 -- neither USL nor LSL is null
999 cp := round((USL - LSL) / (6 * s), 2);
1000 IF ((USL - mean) <= (mean - LSL)) THEN
1001 cpk := round((USL - mean)/(3 * s), 2);
1002 ELSE
1003 cpk := round((mean - LSL)/(3 * s), 2);
1004 END IF;
1005 END IF;
1006 END IF;
1007
1008 -- insert the first point. has to be treated a bit differently because
1009 -- of the lower boundary
1010 INSERT INTO QA_CHART_DATA
1011 (LAST_UPDATE_DATE,
1012 LAST_UPDATED_BY,
1013 CREATION_DATE,
1014 CREATED_BY,
1015 LAST_UPDATE_LOGIN,
1016 SUBGROUP_NUMBER,
1017 AVERAGE,
1018 RANGE,
1019 BAR_NUMBER,
1020 HIST_RANGE,
1021 NUM_OCCURRENCES)
1022 VALUES
1023 (SYSDATE,
1024 1,
1025 SYSDATE,
1026 1,
1027 NULL,
1028 null,
1029 null,
1030 null,
1031 1,
1032 to_char(minimum) || ' - ' || to_char(boundary(1)),
1033 occurrences(1));
1034
1035 FOR i IN 2..num_bars LOOP
1036 INSERT INTO QA_CHART_DATA
1037 (LAST_UPDATE_DATE,
1038 LAST_UPDATED_BY,
1039 CREATION_DATE,
1040 CREATED_BY,
1041 LAST_UPDATE_LOGIN,
1042 SUBGROUP_NUMBER,
1043 AVERAGE,
1044 RANGE,
1045 BAR_NUMBER,
1046 HIST_RANGE,
1047 NUM_OCCURRENCES)
1048 VALUES
1049 (SYSDATE,
1050 1,
1051 SYSDATE,
1052 1,
1053 NULL,
1054 null,
1055 null,
1056 null,
1057 i,
1058 to_char(boundary(i-1)) || ' - ' || to_char(boundary(i)),
1059 occurrences(i));
1060 END LOOP;
1061
1062 END HISTOGRAM;
1063
1064 FUNCTION validate_query(p_query_str IN VARCHAR2,x_error_num OUT NOCOPY NUMBER)
1065 RETURN VARCHAR2 IS
1066
1067 -- Bug 2459633. This function executes the sql and handles the exception.
1068 -- return true only if the query fetches record.
1069 -- rponnusa Mon Jul 15 04:31:49 PDT 2002
1070
1071 TYPE cur_typ IS REF CURSOR;
1072 c cur_typ;
1073
1074 dummy VARCHAR2(1000):= NULL;
1075 l_status VARCHAR2(1);
1076 BEGIN
1077
1078 BEGIN
1079 -- Bug 3464464 ksoh Tue Mar 2 13:54:21 PST 2004
1080 -- sometimes the query returns 1 column, sometimes 2
1081 -- need kludge SQL to make sure there is only 1 column to be returned
1082 -- 10g throws exception when the cursor return type mismatch
1083 OPEN c FOR 'select 1 from (' || p_query_str || ')';
1084 FETCH c INTO dummy;
1085
1086 IF c%ROWCOUNT = 0 THEN
1087 x_error_num := 1403;
1088 l_status := 'F';
1089 ELSE
1090 l_status := 'T';
1091 END IF;
1092
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 x_error_num := SQLCODE;
1096 l_status := 'F';
1097 END;
1098 CLOSE c;
1099
1100 RETURN l_status;
1101
1102 END validate_query;
1103
1104
1105 END QLTCONTB;
1106
[Home] [Help]