[Home] [Help]
PACKAGE: APPS.GMD_RESULTS_GRP
Source
1 PACKAGE gmd_results_grp AS
2 --$Header: GMDGRESS.pls 120.0.12010000.1 2008/07/24 09:54:20 appldev ship $
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDGRESS.pls |
11 --| Package Name : GMD_RESULTS_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Results Entity |
16 --| |
17 --| TYPEs: result_data |
18 --| |
19 --| FUNCTIONs: is_value_numeric |
20 --| PROCEDUREs: check_experimental_error |
21 --| validate_result |
22 --| |
23 --| HISTORY |
24 --| Chetan Nagar 08-Aug-2002 Created. |
25 --| Ger Kelly 10-Sep-2002 Added in get_rslt_and_spec_rslt changes |
26 --| Ger Kelly 17-Sep-2002 Added in get_composite_rslt procedure|
27 --| GK 19-Sep-2002 Added test_type to rec, changed the rec to tbl, |
28 --| Added event_spec_disp_id as a parameter |
29 --| LeAta Jackson 01-Oct-2002 Added migration parameter to |
30 --| create_rslt_and_spec_result_row |
31 --| LeAta Jackson 16-Oct-2002 Added check_disp, check_event and |
32 --| change_lot_status. Bug 2628427 |
33 --| GK 17-Oct-2002 Bug 2621648 - changed min and max char |
34 --| to varchar2(16) in the data records |
35 --| LeAta Jackson 14-Nov-2002 Added validate_result, is_value_numeric |
36 --| validate_eval_list, |
37 --| check_experimental_errror |
38 --+==========================================================================+
39 -- End of comments
40
41 TYPE result_data IS RECORD
42 (test_id NUMBER,
43 test_code VARCHAR2(80),
44 test_type VARCHAR2(1),
45 result_num NUMBER,
46 result_char VARCHAR2(80),
47 target_num NUMBER,
48 target_char VARCHAR2(16),
49 min_num NUMBER,
50 min_char VARCHAR2(16),
51 max_num NUMBER,
52 max_char VARCHAR2(16),
53 in_spec VARCHAR2(1),
54 spec_target_num NUMBER,
55 spec_target_char VARCHAR2(16),
56 spec_min_num NUMBER,
57 spec_min_char VARCHAR2(16),
58 spec_max_num NUMBER,
59 spec_max_char VARCHAR2(16),
60 spec_in_spec VARCHAR2(1),
61 spec_test_id NUMBER,
62 unit VARCHAR2(25),
63 method VARCHAR2(80),
64 expression VARCHAR2(1000),
65 display_label VARCHAR2(80),
66 spec_id NUMBER,
67 result VARCHAR2(80),
68 result_date DATE,
69 display_precision NUMBER,
70 report_precision NUMBER,
71 additional_test_ind VARCHAR2(1),
72 exp_error_type gmd_spec_tests_b.exp_error_type%TYPE,
73 below_spec_min NUMBER,
74 above_spec_min NUMBER,
75 below_spec_max NUMBER,
76 above_spec_max NUMBER,
77 below_min_action_code VARCHAR(32),
78 above_min_action_code VARCHAR(32),
79 below_max_action_code VARCHAR(32),
80 above_max_action_code VARCHAR(32),
81 out_action_code VARCHAR(32),
82 evaluation_ind VARCHAR2(3),
83 value_in_report_prec NUMBER,
84 in_fuzzy_zone VARCHAR2(5),
85 out_of_spec VARCHAR2(5),
86 result_action_code VARCHAR2(32),
87 called_from_form VARCHAR2(1),
88 lab_organization_id NUMBER,
89 planned_resource VARCHAR2(30),
90 planned_resource_instance NUMBER,
91 actual_resource VARCHAR2(30),
92 actual_resource_instance NUMBER,
93 --BEGIN BUG#2871126 Rameshwar
94 --Display precision for comparision spec.
95 spec_display_precision NUMBER
96 --END BUG#2871126
97 );
98
99
100 --GK B2621648
101
102 TYPE gmd_results_rec_tbl IS TABLE OF result_data
103 INDEX BY BINARY_INTEGER;
104
105 TYPE comres_rec IS RECORD
106 (test_id NUMBER,
107 test_code VARCHAR2(80),
108 result_num NUMBER,
109 result_char VARCHAR2(16),
110 target_num NUMBER,
111 target_char VARCHAR2(16),
112 min_num NUMBER,
113 min_char VARCHAR2(16),
114 max_num NUMBER,
115 max_char VARCHAR2(16),
116 in_spec VARCHAR2(1),
117 spec_target_num NUMBER,
118 spec_target_char VARCHAR2(16),
119 spec_min_num NUMBER,
120 spec_min_char VARCHAR2(16),
121 spec_max_num NUMBER,
122 spec_max_char VARCHAR2(16),
123 spec_in_spec VARCHAR2(1));
124
125 TYPE gmd_comres_tab IS TABLE OF comres_rec
126 INDEX BY BINARY_INTEGER;
127
128
129 -- The following types are used in the Calculate Expression code.
130 TYPE rslt_rec IS RECORD
131 ( test_id NUMBER
132 , value NUMBER
133 );
134
135 TYPE rslt_tbl IS TABLE OF rslt_rec
136 INDEX BY BINARY_INTEGER;
137
138 PROCEDURE create_rslt_and_spec_rslt_rows
139 (
140 p_sample IN GMD_SAMPLES%ROWTYPE
141 , p_migration IN VARCHAR2 DEFAULT NULL
142 , x_event_spec_disp OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
143 , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
144 , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
145 , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
146 , x_return_status OUT NOCOPY VARCHAR2
147 );
148
149
150 PROCEDURE delete_rslt_and_spec_rslt_rows
151 (
152 p_sample_id IN NUMBER
153 , x_return_status OUT NOCOPY VARCHAR2
154 );
155
156
157 FUNCTION get_current_event_spec_disp_id
158 (
159 p_sampling_event_id IN NUMBER
160 ) RETURN NUMBER;
161
162
163 PROCEDURE compare_rslt_and_spec
164 (
165 p_sample_id IN NUMBER
166 , p_spec_id IN NUMBER
167 , x_test_ids OUT NOCOPY GMD_API_PUB.number_tab
168 , x_return_status OUT NOCOPY VARCHAR2
169 );
170
171
172 PROCEDURE compare_cmpst_rslt_and_spec
173 (
174 p_composite_spec_disp_id IN NUMBER
175 , p_spec_id IN NUMBER
176 , x_test_ids OUT NOCOPY GMD_API_PUB.number_tab
177 , x_return_status OUT NOCOPY VARCHAR2
178 );
179
180
181 FUNCTION rslt_is_in_spec
182 (
183 p_spec_id IN NUMBER
184 , p_test_id IN NUMBER
185 , p_rslt_value_num IN NUMBER DEFAULT NULL
186 , p_rslt_value_char IN VARCHAR2 DEFAULT NULL
187 )
188 RETURN VARCHAR2;
189
190
191 PROCEDURE add_tests_to_sample
192 (
193 p_sample IN GMD_SAMPLES%ROWTYPE
194 , p_test_ids IN GMD_API_PUB.number_tab
195 , p_event_spec_disp_id IN NUMBER
196 , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
197 , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
198 , x_return_status OUT NOCOPY VARCHAR2
199 , p_test_qty IN NUMBER DEFAULT NULL
200 , p_test_qty_uom IN VARCHAR2 DEFAULT NULL
201 );
202
203
204 FUNCTION next_seq_in_result
205 (
206 p_sample_id NUMBER
207 , p_test_id NUMBER
208 )
209 RETURN NUMBER;
210
211
212 FUNCTION all_ref_tests_exist_in_sample
213 (
214 p_sample_id IN NUMBER
215 , p_test_id IN NUMBER
216 ) RETURN BOOLEAN;
217
218
219 PROCEDURE add_test_to_samples
220 (
221 p_sample_ids IN GMD_API_PUB.number_tab
222 , p_test_id IN NUMBER
223 , p_event_spec_disp_id IN NUMBER
224 , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
225 , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
226 , x_return_status OUT NOCOPY VARCHAR2
227 , p_test_qty IN NUMBER DEFAULT NULL
228 , p_test_qty_uom IN VARCHAR2 DEFAULT NULL
229 );
230
231
232 PROCEDURE make_target_spec_the_base_spec
233 (
234 p_sample_id IN NUMBER
235 , p_target_spec_id IN NUMBER
236 , p_target_spec_vr_id IN NUMBER
237 , x_return_status OUT NOCOPY VARCHAR2
238 );
239
240
241 PROCEDURE use_target_spec_for_cmpst_rslt
242 (
243 p_composite_spec_disp_id IN NUMBER
244 , p_target_spec_id IN NUMBER
245 , p_target_spec_vr_id IN NUMBER
246 , x_return_status OUT NOCOPY VARCHAR2
247 );
248
249
250 PROCEDURE get_rslt_and_spec_rslt
251 (p_sample_id IN NUMBER,
252 p_source_spec_id IN NUMBER,
253 p_target_spec_id IN NUMBER,
254 p_event_spec_disp_id IN NUMBER,
255 x_results_rec_tbl OUT NOCOPY GMD_RESULTS_GRP.gmd_results_rec_tbl,
256 x_return_status OUT NOCOPY VARCHAR2);
257
258
259 PROCEDURE composite_exist
260 (
261 p_sampling_event_id IN NUMBER
262 , p_event_spec_disp_id IN NUMBER DEFAULT NULL
263 , x_composite_exist OUT NOCOPY VARCHAR2
264 , x_composite_valid OUT NOCOPY VARCHAR2
265 , x_return_status OUT NOCOPY VARCHAR2
266 );
267
268
269 PROCEDURE se_recomposite_required
270 (
271 p_sampling_event_id IN NUMBER
272 , p_event_spec_disp_id IN NUMBER DEFAULT NULL
273 , x_return_status OUT NOCOPY VARCHAR2
274 );
275
276
277
278 PROCEDURE result_recomposite_required
279 (
280 p_result_id IN NUMBER
281 , p_event_spec_disp_id IN NUMBER
282 , x_return_status OUT NOCOPY VARCHAR2
283 );
284
285
286
287 PROCEDURE get_sample_ids_for_se
288 (
289 p_sampling_event_id IN NUMBER
290 , x_sample_ids OUT NOCOPY GMD_API_PUB.number_tab
291 , x_return_status OUT NOCOPY VARCHAR2
292 );
293
294
295 PROCEDURE populate_result_data_points
296 (
297 p_sample_ids IN GMD_API_PUB.number_tab
298 , p_event_spec_disp_id IN NUMBER
299 , x_return_status OUT NOCOPY VARCHAR2
300 );
301
302
303 PROCEDURE create_composite_rows
304 (
305 p_event_spec_disp_id IN NUMBER
306 , x_return_status OUT NOCOPY VARCHAR2
307 );
308
309
310 PROCEDURE qc_mean
311 (
312 p_test_id IN NUMBER
313 , x_mean_num OUT NOCOPY NUMBER
314 , x_return_status OUT NOCOPY VARCHAR2
315 );
316
317
318 PROCEDURE qc_median
319 (
320 p_test_id IN NUMBER
321 , x_median_num OUT NOCOPY NUMBER
322 , x_median_char OUT NOCOPY VARCHAR2
323 , x_return_status OUT NOCOPY VARCHAR2
324 );
325
326
327 PROCEDURE qc_mode
328 (
329 p_test_id IN NUMBER
330 , x_mode_num OUT NOCOPY NUMBER
331 , x_mode_char OUT NOCOPY VARCHAR2
332 , x_return_status OUT NOCOPY VARCHAR2
333 );
334
335
336 PROCEDURE qc_high
337 (
338 p_test_id IN NUMBER
339 , x_high_num OUT NOCOPY NUMBER
340 , x_high_char OUT NOCOPY VARCHAR2
341 , x_return_status OUT NOCOPY VARCHAR2
342 );
343
344
345 PROCEDURE qc_low
346 (
347 p_test_id IN NUMBER
348 , x_low_num OUT NOCOPY NUMBER
349 , x_low_char OUT NOCOPY VARCHAR2
350 , x_return_status OUT NOCOPY VARCHAR2
351 );
352
353
354 PROCEDURE qc_standard_deviation
355 (
356 p_test_id IN NUMBER
357 , x_stddev OUT NOCOPY NUMBER
358 , x_return_status OUT NOCOPY VARCHAR2
359 );
360
361 PROCEDURE get_composite_rslt
362 (
363 p_composite_spec_disp_id IN NUMBER,
364 p_source_spec_id IN NUMBER,
365 p_target_spec_id IN NUMBER,
366 x_comresults_tab OUT NOCOPY GMD_RESULTS_GRP.gmd_comres_tab,
367 x_return_status OUT NOCOPY VARCHAR2);
368
369
370 PROCEDURE change_lot_status
371 ( p_sample_id IN NUMBER
372 , p_organization_id IN NUMBER
373 , p_lot_status IN VARCHAR2
374 , x_return_status OUT NOCOPY VARCHAR2
375 );
376
377
378 FUNCTION is_value_numeric (p_char_number VARCHAR2)
379 RETURN BOOLEAN;
380
381
382 PROCEDURE check_experimental_error
383 ( p_result_rec IN OUT NOCOPY RESULT_DATA
384 , x_return_status OUT NOCOPY VARCHAR2
385 );
386
387
388 PROCEDURE validate_result
389 ( p_result_rec IN OUT NOCOPY result_data
390 , x_return_status OUT NOCOPY VARCHAR2
391 );
392
393
394 PROCEDURE validate_evaluation_ind
395 ( p_evaluation_ind IN VARCHAR2
396 , p_in_spec_ind IN VARCHAR2
397 , p_result_value IN VARCHAR2
398 , x_return_status OUT NOCOPY VARCHAR2
399 );
400
401
402 PROCEDURE calc_expression
403 ( p_sample_id IN NUMBER
404 , p_event_spec_disp_id IN NUMBER
405 , p_spec_id IN NUMBER
406 , x_rslt_tbl OUT NOCOPY rslt_tbl
407 , x_return_status OUT NOCOPY VARCHAR2
408 );
409
410
411 PROCEDURE change_sample_disposition
412 ( p_sample_id IN NUMBER
413 , x_change_disp_to OUT NOCOPY VARCHAR2
414 , x_return_status OUT NOCOPY VARCHAR2
415 , x_message_data OUT NOCOPY VARCHAR2
416 );
417
418
419 PROCEDURE change_disp_for_auto_lot
420 ( p_sample_id IN NUMBER
421 , x_change_disp_to OUT NOCOPY VARCHAR2
422 , x_return_status OUT NOCOPY VARCHAR2
423 );
424
425
426 PROCEDURE composite_and_change_lot
427 ( p_sampling_event_id IN NUMBER
428 , p_commit IN VARCHAR2
429 , x_return_status OUT NOCOPY VARCHAR2
430 );
431
432
433 PROCEDURE dump_data_points;
434
435
436 PROCEDURE update_exptest_value_null
437 (p_exp_ref_test_id IN gmd_qc_tests_b.test_id%TYPE
438 , p_sample_id IN gmd_samples.sample_id%TYPE
439 , p_event_spec_disp_id IN gmd_sample_spec_disp.event_spec_disp_id%TYPE
440 , x_return_status OUT NOCOPY VARCHAR2
441 );
442
443 END gmd_results_grp;
444