1 PACKAGE BODY GMD_QC_TEST_VALUES_GRP as
2 /* $Header: GMDGTVLB.pls 115.9 2002/11/21 18:34:22 mchandak noship $*/
3
4 /*===========================================================================
5 PROCEDURE NAME: check_range_overlap
6
7 DESCRIPTION: This procedure checks for test type 'L' - numeric
8 range with label whether the subrange overlaps
9 with any other subrange within a test.
10 This procedure should be called after insert/update
11 of GMD_QC_TEST_VALUES_TL for test type 'L' but BEFORE
12 COMMIT.
13
14 PARAMETERS: In : p_test_id
15 OUT : x_min_range - minimum value of the whole range.
16 x_max_range - maximum value of the whole range.
17
18 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
19 ===========================================================================*/
20 PROCEDURE CHECK_RANGE_OVERLAP(
21 p_test_id IN VARCHAR2,
22 x_min_range OUT NOCOPY NUMBER,
23 x_max_range OUT NOCOPY NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_message_data OUT NOCOPY VARCHAR2) IS
26
27 l_progress VARCHAR2(3);
28 l_exists VARCHAR2(1);
29 l_min NUMBER;
30 l_max NUMBER;
31 l_counter NUMBER(4):= 0;
32 l_prev_max NUMBER;
33
34 CURSOR CR_TEST_VALUES IS
35 SELECT min_num,max_num
36 FROM gmd_qc_test_values_b
37 WHERE test_id = p_test_id
38 ORDER BY NVL(min_num, -999999999.999999999) ;
39
40 BEGIN
41 l_progress := '010';
42
43 x_return_status := FND_API.G_RET_STS_SUCCESS ;
44
45 IF p_test_id IS NULL THEN
46 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_CODE_NULL');
47 FND_MSG_PUB.ADD;
48 RAISE FND_API.G_EXC_ERROR;
49 END IF;
50
51 l_progress := '020';
52
53 -- this cursor also takes care of multiple NULLS in min_num AND multiple NULLS in max_num in addition
54 -- to checking whether a subrange overlaps with other subrange.
55 -- Selects each record ordered by min values and compares the min value in the current record to the
56 -- max value in the previous record.If they are the same, gives error.
57
58 OPEN CR_TEST_VALUES;
59 LOOP
60 FETCH CR_TEST_VALUES INTO l_min,l_max;
61 IF CR_TEST_VALUES%NOTFOUND THEN
62 CLOSE CR_TEST_VALUES ;
63 EXIT;
64 END IF;
65 l_counter := l_counter + 1 ;
66 x_max_range := l_max ;
67 IF l_counter > 1 THEN
68 IF NVL(l_min,-999999999.999999999) <= NVL(l_prev_max,999999999.999999999) THEN
69 CLOSE CR_TEST_VALUES ;
70 FND_MESSAGE.SET_NAME('GMD','GMD_RANGES_MAY_NOT_OVERLAP');
71 FND_MSG_PUB.ADD;
72 RAISE FND_API.G_EXC_ERROR;
73 END IF;
74 ELSIF l_counter = 1 THEN
75 x_min_range := l_min;
76 END IF;
77 l_prev_max := l_max;
78 END LOOP;
79
80 EXCEPTION
81 WHEN FND_API.G_EXC_ERROR THEN
82 x_return_status := FND_API.G_RET_STS_ERROR ;
83 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
84 WHEN OTHERS
85 THEN
86 IF CR_TEST_VALUES%ISOPEN THEN
87 CLOSE CR_TEST_VALUES ;
88 END IF;
89 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
90 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.CHECK_RANGE_OVERLAP' );
91 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
92 FND_MESSAGE.Set_Token('POSITION',l_progress );
93 FND_MSG_PUB.ADD;
94 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
95 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
96 END CHECK_RANGE_OVERLAP;
97
98
99 FUNCTION get_test_value_desc (
100 p_test_id IN NUMBER,
101 p_test_value_num IN NUMBER ,
102 p_test_value_char IN VARCHAR2 ) RETURN VARCHAR2 IS
103
104 l_test_value_desc VARCHAR2(240) ;
105 l_test_type VARCHAR2(1);
106 BEGIN
107
108 IF p_test_id IS NULL OR ( p_test_value_num IS NULL AND p_test_value_char IS NULL) THEN
109 return(null);
110 END IF;
111
112 SELECT test_type INTO l_test_type
113 FROM GMD_QC_TESTS_B
114 WHERE test_id = p_test_id ;
115
116 IF l_test_type = 'L' THEN
117 SELECT DISPLAY_LABEL_NUMERIC_RANGE INTO l_test_value_desc
118 FROM GMD_QC_TEST_VALUES
119 WHERE test_id = p_test_id
120 AND p_test_value_num >= nvl(min_num,p_test_value_num)
121 AND p_test_value_num <= nvl(max_num,p_test_value_num);
122 ELSIF l_test_type = 'T' THEN
123 IF p_test_value_num IS NOT NULL THEN
124 SELECT test_value_desc INTO l_test_value_desc
125 FROM GMD_QC_TEST_VALUES
126 WHERE test_id = p_test_id
127 AND text_range_seq = p_test_value_num ;
128 ELSE
129 SELECT test_value_desc INTO l_test_value_desc
130 FROM GMD_QC_TEST_VALUES
131 WHERE test_id = p_test_id
132 AND value_char = p_test_value_char ;
133 END IF;
134 ELSIF l_test_type = 'V' THEN
135 SELECT test_value_desc INTO l_test_value_desc
136 FROM GMD_QC_TEST_VALUES
137 WHERE test_id = p_test_id
138 AND value_char = p_test_value_char ;
139
140 END IF;
141
142 RETURN(l_test_value_desc);
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN(NULL);
147 END GET_TEST_VALUE_DESC;
148
149 /*===========================================================================
150 PROCEDURE NAME: check_valid_test
151
152 DESCRIPTION: This procedure checks whether the test is valid or
153 not before inserting/deleting into test values table.
154
155 PARAMETERS: In : p_test_id
156 OUT : test_type - test type
157 display_precision - display precision of the test.
158
159 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
160 ===========================================================================*/
161
162 PROCEDURE CHECK_VALID_TEST(
163 p_test_id IN NUMBER,
164 x_test_type OUT NOCOPY VARCHAR2,
165 x_display_precision OUT NOCOPY NUMBER,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_message_data OUT NOCOPY VARCHAR2) IS
168
169 l_progress VARCHAR2(3);
170 l_delete_mark NUMBER(5);
171
172 CURSOR cr_get_test_type(l_test_id NUMBER) IS
173 SELECT test_type,display_precision,delete_mark FROM GMD_QC_TESTS_B
174 WHERE test_id = p_test_id ;
175
176
177 BEGIN
178 l_progress := '010';
179
180 x_return_status := FND_API.G_RET_STS_SUCCESS ;
181
182 IF p_test_id IS NULL THEN
183 FND_MESSAGE.SET_NAME('GMD','TEST_ID_CODE_NULL');
184 FND_MSG_PUB.ADD;
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 OPEN cr_get_test_type(p_test_id);
189 FETCH cr_get_test_type INTO x_test_type,x_display_precision,l_delete_mark;
190 IF cr_get_test_type%NOTFOUND THEN
191 CLOSE cr_get_test_type;
192 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_TEST');
193 FND_MESSAGE.SET_TOKEN('TEST', TO_CHAR(p_test_id));
194 FND_MSG_PUB.ADD;
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197 CLOSE cr_get_test_type ;
198
199 l_progress := '020';
200
201 IF l_delete_mark = 1 THEN
202 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_DELETED');
203 FND_MESSAGE.SET_TOKEN('TEST',TO_CHAR(p_test_id));
204 FND_MSG_PUB.ADD;
205 RAISE FND_API.G_EXC_ERROR;
206 END IF;
207 IF GMD_QC_TESTS_GRP.test_exist_in_spec(p_test_id => p_test_id) THEN
208 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_USED_IN_SPEC');
209 FND_MESSAGE.SET_TOKEN('TEST',TO_CHAR(p_test_id));
210 FND_MSG_PUB.ADD;
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 EXCEPTION
215 WHEN FND_API.G_EXC_ERROR THEN
216 x_return_status := FND_API.G_RET_STS_ERROR ;
217 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
218
219 WHEN OTHERS THEN
220 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
221 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.CHECK_VALID_TEST');
222 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
223 FND_MESSAGE.Set_Token('POSITION',l_progress );
224 FND_MSG_PUB.ADD;
225 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227
228 END CHECK_VALID_TEST;
229
230
231 PROCEDURE CHECK_FOR_NULL_AND_FKS(
232 p_test_type IN VARCHAR,
233 p_display_precision IN NUMBER,
234 p_qc_test_values_rec IN GMD_QC_TEST_VALUES%ROWTYPE,
235 x_qc_test_values_rec OUT NOCOPY GMD_QC_TEST_VALUES%ROWTYPE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_message_data OUT NOCOPY VARCHAR2) IS
238
239 l_progress VARCHAR2(3);
240
241 BEGIN
242 l_progress := '010';
243
244 x_return_status := FND_API.G_RET_STS_SUCCESS ;
245
246 x_qc_test_values_rec := p_qc_test_values_rec ;
247
248 -- There should be no test values for p_test_type IN ('U','N','E').. Do that validation in PUBLIC API's
249
250 IF x_qc_test_values_rec.EXPRESSION_REF_TEST_ID IS NOT NULL THEN
251 FND_MESSAGE.SET_NAME('GMD','GMD_EXP_TEST_ID_NOT_REQD');
252 FND_MSG_PUB.ADD;
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255
256 IF p_test_type = 'L' THEN -- numeric range with label.
257 IF x_qc_test_values_rec.min_num IS NULL AND x_qc_test_values_rec.max_num IS NULL
258 THEN
259 FND_MESSAGE.SET_NAME('GMD', 'GMD_MIN_MAX_REQ');
260 FND_MSG_PUB.ADD;
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263
264 IF LTRIM(RTRIM(x_qc_test_values_rec.display_label_numeric_range)) IS NULL THEN
265 FND_MESSAGE.SET_NAME('GMD', 'GMD_DISPLAY_LABEL_REQ');
266 FND_MSG_PUB.ADD;
267 RAISE FND_API.G_EXC_ERROR;
268 END IF;
269
270 IF x_qc_test_values_rec.value_char IS NOT NULL THEN
271 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_VALUE_CHAR_NOT_REQD');
272 FND_MSG_PUB.ADD;
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275
276 IF x_qc_test_values_rec.test_value_desc IS NOT NULL THEN
277 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_VALUE_DESC_NOT_REQD');
278 FND_MSG_PUB.ADD;
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 x_qc_test_values_rec.min_num := ROUND(x_qc_test_values_rec.min_num,p_display_precision);
283 x_qc_test_values_rec.max_num := ROUND(x_qc_test_values_rec.max_num,p_display_precision);
284
285 IF x_qc_test_values_rec.min_num IS NOT NULL AND x_qc_test_values_rec.max_num IS NOT NULL
286 THEN
287 IF x_qc_test_values_rec.min_num > x_qc_test_values_rec.max_num THEN
288 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_MIN_MAX_ERROR');
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_ERROR;
291 END IF;
292 END IF;
293 ELSE
294 IF x_qc_test_values_rec.min_num IS NOT NULL OR x_qc_test_values_rec.max_num IS NOT NULL THEN
295 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_RANGE_NOT_REQD');
296 FND_MSG_PUB.ADD;
297 RAISE FND_API.G_EXC_ERROR;
298 END IF;
299
300 IF x_qc_test_values_rec.display_label_numeric_range IS NOT NULL THEN
301 FND_MESSAGE.SET_NAME('GMD', 'GMD_DISPLAY_LABEL_NOT_REQD');
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305 END IF;
306
307 IF p_test_type = 'V' THEN -- List Of Values
308 IF LTRIM(RTRIM(x_qc_test_values_rec.value_char)) IS NULL THEN
309 FND_MESSAGE.SET_NAME('GMD', 'GMD_VALUE_CHAR_REQ');
310 FND_MSG_PUB.ADD;
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 IF LTRIM(RTRIM(x_qc_test_values_rec.test_value_desc)) IS NULL THEN
315 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_VALUE_DESC_REQ');
316 FND_MSG_PUB.ADD;
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319 END IF;
320
321 IF p_test_type = 'T' THEN -- Text Range
322 IF LTRIM(RTRIM(x_qc_test_values_rec.value_char)) IS NULL THEN
323 FND_MESSAGE.SET_NAME('GMD', 'GMD_VALUE_CHAR_REQ');
324 FND_MSG_PUB.ADD;
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327
328 IF LTRIM(RTRIM(x_qc_test_values_rec.test_value_desc)) IS NULL THEN
329 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_VALUE_DESC_REQ');
330 FND_MSG_PUB.ADD;
331 RAISE FND_API.G_EXC_ERROR;
332 END IF;
333
334 SELECT NVL(MAX(text_range_seq),0) + 1 INTO x_qc_test_values_rec.text_range_seq
335 FROM GMD_QC_TEST_VALUES_B
336 WHERE test_id = x_qc_test_values_rec.test_id ;
337
338 ELSE
339
340 IF x_qc_test_values_rec.text_range_seq IS NOT NULL THEN
341 FND_MESSAGE.SET_NAME('GMD', 'GMD_SEQ_NOT_REQD');
342 FND_MSG_PUB.ADD;
343 RAISE FND_API.G_EXC_ERROR;
344 END IF;
345 END IF;
346
347 EXCEPTION
348 WHEN FND_API.G_EXC_ERROR THEN
349 x_return_status := FND_API.G_RET_STS_ERROR ;
350 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
351
352 WHEN OTHERS THEN
353 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
354 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.CHECK_FOR_NULL_AND_FKS');
355 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
356 FND_MESSAGE.Set_Token('POSITION',l_progress );
357 FND_MSG_PUB.ADD;
358 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360
361 END CHECK_FOR_NULL_AND_FKS;
362
363
364 /*===========================================================================
365 PROCEDURE NAME: validate_before_insert
366
367 DESCRIPTION: This procedure validates test values before insert.
368
369 PARAMETERS:
370
371 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
372 ===========================================================================*/
373
374 PROCEDURE VALIDATE_BEFORE_INSERT(
375 p_qc_test_values_rec IN GMD_QC_TEST_VALUES%ROWTYPE,
376 x_qc_test_values_rec OUT NOCOPY GMD_QC_TEST_VALUES%ROWTYPE,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_message_data OUT NOCOPY VARCHAR2) IS
379
380 l_progress VARCHAR2(3);
381 l_temp VARCHAR2(1);
382 l_test_type VARCHAR2(1);
383 l_display_precision NUMBER(1);
384
385 CURSOR cr_test_value_exist IS
386 SELECT 'x' FROM GMD_QC_TEST_VALUES_B
387 WHERE test_id = p_qc_test_values_rec.test_id
388 AND value_char = p_qc_test_values_rec.value_char ;
389
390 BEGIN
391 l_progress := '010';
392
393 x_return_status := FND_API.G_RET_STS_SUCCESS ;
394
395 IF p_qc_test_values_rec.test_id IS NULL THEN
396 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_REQ');
397 FND_MSG_PUB.ADD;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 check_valid_test(p_test_id => p_qc_test_values_rec.test_id,
402 x_test_type => l_test_type,
403 x_display_precision => l_display_precision,
404 x_return_status => x_return_status,
405 x_message_data => x_message_data );
406
407 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408 RETURN;
409 END IF;
410
411 IF l_test_type IN ('N','E','U') THEN
412 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_VALUE_REC_NOT_REQ');
413 FND_MSG_PUB.ADD;
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417 CHECK_FOR_NULL_AND_FKS(
418 p_test_type => l_test_type,
419 p_display_precision => l_display_precision,
420 p_qc_test_values_rec => p_qc_test_values_rec,
421 x_qc_test_values_rec => x_qc_test_values_rec,
422 x_return_status => x_return_status,
423 x_message_data => x_message_data );
424
425 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
426 return;
427 END IF;
428
429 -- check for duplicate test values.
430 IF l_test_type IN ('V','T') THEN
431 OPEN cr_test_value_exist;
432 FETCH cr_test_value_exist INTO l_temp;
433 IF cr_test_value_exist%FOUND THEN
434 CLOSE cr_test_value_exist;
435 FND_MESSAGE.SET_NAME('GMD','GMD_DUP_TEST_VALUE');
436 FND_MESSAGE.SET_TOKEN('TEST',to_char(x_qc_test_values_rec.test_id));
437 FND_MSG_PUB.ADD;
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440 CLOSE cr_test_value_exist;
441 END IF;
442
443 EXCEPTION
444 WHEN FND_API.G_EXC_ERROR THEN
445 x_return_status := FND_API.G_RET_STS_ERROR ;
446 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
447
448 WHEN OTHERS THEN
449 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
450 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.VALIDATE_BEFORE_INSERT');
451 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
452 FND_MESSAGE.Set_Token('POSITION',l_progress );
453 FND_MSG_PUB.ADD;
454 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456
457 END validate_before_insert;
458
459 /*===========================================================================
460
461 PROCEDURE NAME: validate_after_insert_all
462 DESCRIPTION: This procedure updates min_value_num and max_value_num
463 in test header table and also validates if the range
464 doesnt overlap.
465 NOTE : Call after all test values are inserted.
466
467 ===========================================================================*/
468
469 PROCEDURE VALIDATE_AFTER_INSERT_ALL(
470 p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
471 x_gmd_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
472 x_return_status OUT NOCOPY VARCHAR2,
473 x_message_data OUT NOCOPY VARCHAR2) IS
474
475
476 l_progress VARCHAR2(3);
477 l_test_values_count BINARY_INTEGER;
478 l_min_range NUMBER;
479 l_max_range NUMBER;
480
481 BEGIN
482 l_progress := '010';
483
484 x_return_status := FND_API.G_RET_STS_SUCCESS ;
485
486 x_gmd_qc_tests_rec := p_gmd_qc_tests_rec;
487
488
489 IF x_gmd_qc_tests_rec.test_id IS NULL THEN
490 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_CODE_NULL');
491 FND_MSG_PUB.ADD;
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494
495 -- atleast one test value record must be there.
496 IF x_gmd_qc_tests_rec.test_type in ('V','T','L') THEN
497 SELECT NVL(COUNT(1),0) INTO l_test_values_count
498 FROM GMD_QC_TEST_VALUES_B
499 WHERE test_id = x_gmd_qc_tests_rec.test_id ;
500
501 IF l_test_values_count = 0 THEN
502 FND_MESSAGE.SET_NAME('GMD','GMD_NO_TEST_VALUES');
503 FND_MESSAGE.SET_TOKEN('TEST',x_gmd_qc_tests_rec.test_code);
504 FND_MSG_PUB.ADD;
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507 END IF;
508
509 l_progress := '020';
510
511 IF x_gmd_qc_tests_rec.test_type = 'L' THEN
512 GMD_QC_TEST_VALUES_GRP.CHECK_RANGE_OVERLAP(
513 p_test_id => x_gmd_qc_tests_rec.test_id,
514 x_min_range => l_min_range,
515 x_max_range => l_max_range,
516 x_return_status => x_return_status,
517 x_message_data => x_message_data);
518
519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
520 RETURN;
521 END IF;
522
523 IF x_gmd_qc_tests_rec.exp_error_type = 'N' AND l_min_range IS NOT NULL AND l_max_range IS NOT NULL THEN
524 gmd_qc_tests_grp.validate_all_exp_error(
525 p_validation_level => 'SPEC',
526 p_exp_error_type => x_gmd_qc_tests_rec.exp_error_type,
527 p_below_spec_min => x_gmd_qc_tests_rec.below_spec_min,
528 p_below_min_action_code => x_gmd_qc_tests_rec.below_min_action_code,
529 p_above_spec_min => x_gmd_qc_tests_rec.above_spec_min,
530 p_above_min_action_code => x_gmd_qc_tests_rec.above_min_action_code,
531 p_below_spec_max => x_gmd_qc_tests_rec.below_spec_max,
532 p_below_max_action_code => x_gmd_qc_tests_rec.below_max_action_code,
533 p_above_spec_max => x_gmd_qc_tests_rec.above_spec_max,
534 p_above_max_action_code => x_gmd_qc_tests_rec.above_max_action_code,
535 p_test_min => l_min_range,
536 p_test_max => l_max_range,
537 x_return_status => x_return_status,
538 x_message_data => x_message_data );
539
540 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
541 RETURN;
542 END IF;
543 END IF;
544
545 UPDATE GMD_QC_TESTS_B
546 SET min_value_num = l_min_range,
547 max_value_num = l_max_range
548 WHERE
549 test_id = x_gmd_qc_tests_rec.test_id ;
550
551 x_gmd_qc_tests_rec.min_value_num := l_min_range;
552 x_gmd_qc_tests_rec.max_value_num := l_max_range;
553
554 END IF; -- :gmd_qc_tests.test_type = 'L'
555
556 l_progress := '030';
557
558 IF x_gmd_qc_tests_rec.test_type = 'T' THEN
559 SELECT MIN(text_range_seq),MAX(text_range_seq)
560 INTO l_min_range,l_max_range
561 FROM GMD_QC_TEST_VALUES_B
562 WHERE test_id = x_gmd_qc_tests_rec.test_id;
563
564 UPDATE GMD_QC_TESTS_B
565 SET min_value_num = l_min_range,
566 max_value_num = l_max_range
567 WHERE
568 test_id = x_gmd_qc_tests_rec.test_id ;
569
570 x_gmd_qc_tests_rec.min_value_num := l_min_range;
571 x_gmd_qc_tests_rec.max_value_num := l_max_range;
572
573 END IF; -- end of :gmd_qc_tests.test_type = 'T'
574
575 EXCEPTION
576 WHEN FND_API.G_EXC_ERROR THEN
577 x_return_status := FND_API.G_RET_STS_ERROR ;
578 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
579
580 WHEN OTHERS THEN
581 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
582 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.VALIDATE_AFTER_INSERT_ALL');
583 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
584 FND_MESSAGE.Set_Token('POSITION',l_progress );
585 FND_MSG_PUB.ADD;
586 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588
589 END validate_after_insert_all;
590
591 /*===========================================================================
592 PROCEDURE NAME: validate_before_delete
593
594 DESCRIPTION: This procedure checks whether test header is not marked
595 for purge.
596
597 PARAMETERS:
598
599 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
600 ===========================================================================*/
601
602 PROCEDURE VALIDATE_BEFORE_DELETE(
603 p_test_value_id IN NUMBER,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_message_data OUT NOCOPY VARCHAR2) IS
606
607 l_progress VARCHAR2(3);
608 l_temp VARCHAR2(1);
609 l_test_type VARCHAR2(1);
610 l_test_id NUMBER;
611 l_display_precision NUMBER(1);
612
613 CURSOR cr_get_test_id IS
614 SELECT test_id FROM GMD_QC_TEST_VALUES_B
615 WHERE test_value_id = p_test_value_id ;
616
617 BEGIN
618 l_progress := '010';
619 x_return_status := FND_API.G_RET_STS_SUCCESS ;
620
621 IF p_test_value_id IS NULL THEN
622 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_VALUE_ID_REQ');
623 FND_MSG_PUB.ADD;
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627 OPEN cr_get_test_id;
628 FETCH cr_get_test_id INTO l_test_id;
629 IF cr_get_test_id%NOTFOUND THEN
630 CLOSE cr_get_test_id;
631 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_VALUE_INVALID');
632 FND_MESSAGE.SET_TOKEN('TEST_VALUE',to_char(p_test_value_id));
633 FND_MSG_PUB.ADD;
634 RAISE FND_API.G_EXC_ERROR;
635 END IF;
636 CLOSE cr_get_test_id;
637
638 check_valid_test(p_test_id => l_test_id,
639 x_test_type => l_test_type,
640 x_display_precision => l_display_precision,
641 x_return_status => x_return_status,
642 x_message_data => x_message_data );
643
644 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
645 RETURN;
646 END IF;
647
648 EXCEPTION
649 WHEN FND_API.G_EXC_ERROR THEN
650 x_return_status := FND_API.G_RET_STS_ERROR ;
651 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
652
653 WHEN OTHERS THEN
654 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
655 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.VALIDATE_BEFORE_DELETE');
656 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
657 FND_MESSAGE.Set_Token('POSITION',l_progress );
658 FND_MSG_PUB.ADD;
659 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661
662 END VALIDATE_BEFORE_DELETE ;
663
664
665 PROCEDURE VALIDATE_AFTER_DELETE_ALL(
666 p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
667 x_gmd_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_message_data OUT NOCOPY VARCHAR2) IS
670
671 l_progress VARCHAR2(3);
672
673 BEGIN
674 VALIDATE_AFTER_INSERT_ALL(
675 p_gmd_qc_tests_rec => p_gmd_qc_tests_rec,
676 x_gmd_qc_tests_rec => x_gmd_qc_tests_rec,
677 x_return_status => x_return_status,
678 x_message_data => x_message_data ) ;
679
680 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
681 return;
682 END IF;
683
684 EXCEPTION
685 WHEN FND_API.G_EXC_ERROR THEN
686 x_return_status := FND_API.G_RET_STS_ERROR ;
687 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
688
689 WHEN OTHERS THEN
690 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
691 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TEST_VALUES_GRP.VALIDATE_AFTER_DELETE_ALL');
692 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
693 FND_MESSAGE.Set_Token('POSITION',l_progress );
694 FND_MSG_PUB.ADD;
695 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697
698 END VALIDATE_AFTER_DELETE_ALL ;
699
700 FUNCTION text_range_char_to_seq ( p_test_id IN NUMBER,
701 p_value_char IN VARCHAR2)
702
703 RETURN NUMBER IS
704 l_seq BINARY_INTEGER;
705 BEGIN
706
707 SELECT text_range_seq INTO l_seq
708 FROM GMD_QC_TEST_VALUES_B
709 WHERE test_id = p_test_id
710 AND value_char = p_value_char ;
711
712 RETURN (l_seq);
713
714 EXCEPTION WHEN OTHERS THEN
715 FND_MESSAGE.Set_Name('GMD','GMD_TEXT_RANGE_SEQ_INVALID');
716 FND_MESSAGE.Set_Token('TEST',to_char(p_test_id));
717 FND_MESSAGE.Set_Token('VALUE',p_value_char);
718 FND_MSG_PUB.ADD;
719 RETURN(-1);
720 END text_range_char_to_seq;
721
722 END GMD_QC_TEST_VALUES_GRP ;
[Home] [Help]