1 PACKAGE BODY GMD_QC_TESTS_GRP as
2 /* $Header: GMDGTSTB.pls 120.1 2006/03/22 23:07:10 rlnagara noship $*/
3
4 /*===========================================================================
5 FUNCTION NAME: check_test_exist
6
7 DESCRIPTION: This procedure checks whether the test_code/test_id
8 already exists or not.
9
10 PARAMETERS: In : p_init_msg_list - Valid values are 'T' and 'F'
11 p_test_code/p_test_id to validate
12
13 Out: x_test_exist returns TRUE if test exist else FALSE.
14
15 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
16 Ravi Boddu 29-APR-2004 Added the function test_group_order_exist Bug: 3447472
17 ===========================================================================*/
18 FUNCTION CHECK_TEST_EXIST(
19 p_init_msg_list IN VARCHAR2 ,
20 p_test_code IN VARCHAR2 ,
21 p_test_id IN NUMBER )
22 RETURN BOOLEAN IS
23
24 l_progress VARCHAR2(3);
25 l_exists VARCHAR2(1);
26 BEGIN
27 l_progress := '010';
28
29 IF FND_API.to_Boolean( p_init_msg_list ) THEN
30 FND_MSG_PUB.initialize; -- clear the message stack.
31 END IF;
32
33 IF p_test_id IS NULL AND p_test_code IS NULL THEN
34 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_CODE_NULL');
35 FND_MSG_PUB.ADD;
36 RETURN FALSE;
37 END IF;
38
39 l_progress := '020';
40
41 BEGIN
42 IF p_test_id IS NOT NULL THEN
43 SELECT 'X' INTO l_exists
44 FROM GMD_QC_TESTS_B
45 WHERE test_id = p_test_id ;
46 ELSE
47 SELECT 'X' INTO l_exists
48 FROM GMD_QC_TESTS_B
49 WHERE test_code = p_test_code ;
50 END IF;
51
52 RETURN TRUE;
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RETURN FALSE;
57 END;
58
59 EXCEPTION WHEN OTHERS
60 THEN
61 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
62 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.CHECK_TEST_EXIST' );
63 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
64 FND_MESSAGE.Set_Token('POSITION',l_progress );
65 FND_MSG_PUB.ADD;
66 RETURN FALSE;
67 END CHECK_TEST_EXIST;
68
69
70 /*===========================================================================
71
72 FUNCTION NAME: test_exist_in_spec
73
74 ===========================================================================*/
75
76 FUNCTION test_exist_in_spec(
77 p_init_msg_list IN VARCHAR2 ,
78 p_test_id IN NUMBER) RETURN BOOLEAN IS
79
80 CURSOR cr_test_exist IS
81 SELECT 'Y' FROM GMD_SPEC_TESTS_B gst ,GMD_SPECIFICATIONS_B gs
82 WHERE gst.test_id = p_test_id
83 AND gst.spec_id = gs.spec_id
84 AND gs.delete_mark = 0 ;
85
86 l_progress VARCHAR2(3);
87 l_temp VARCHAR2(1);
88
89 BEGIN
90 l_progress := '010';
91
92 IF FND_API.to_Boolean( p_init_msg_list ) THEN
93 FND_MSG_PUB.initialize; -- clear the message stack.
94 END IF;
95
96 IF p_test_id IS NULL THEN
97 FND_MESSAGE.Set_Name('GMD','GMD_TEST_ID_CODE_NULL');
98 FND_MSG_PUB.ADD;
99 RETURN FALSE;
100 END IF;
101
102 l_progress := '020';
103
104 OPEN cr_test_exist;
105 FETCH cr_test_exist into l_temp;
106 IF cr_test_exist%FOUND THEN
107 CLOSE cr_test_exist;
108 RETURN TRUE;
109 ELSE
110 CLOSE cr_test_exist;
111 RETURN FALSE;
112 END IF;
113
114
115 EXCEPTION WHEN OTHERS
116 THEN
117 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
118 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.TEST_EXIST_IN_SPEC' );
119 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
120 FND_MESSAGE.Set_Token('POSITION',l_progress );
121 FND_MSG_PUB.ADD;
122 RETURN FALSE;
123 END TEST_EXIST_IN_SPEC;
124
125 /*===========================================================================
126 FUNCTION NAME: get_test_id_tab
127
128 DESCRIPTION: This procedure returns table of type EXP_TEST_ID_TAB_TYPE
129
130 PARAMETERS:
131
132 CHANGE HISTORY: Created 09-JUL-02 MCHANDAK
133 ===========================================================================*/
134
135 FUNCTION GET_TEST_ID_TAB RETURN exp_test_id_tab_type IS
136 l_test_id_tab EXP_TEST_ID_TAB_TYPE;
137 BEGIN
138 return(l_test_id_tab);
139 END;
140
141 /*===========================================================================
142
143 PROCEDURE NAME: validate_expression
144
145 ===========================================================================*/
146 PROCEDURE validate_expression(
147 p_init_msg_list IN VARCHAR2 ,
148 p_expression IN VARCHAR2,
149 x_test_tab OUT NOCOPY exp_test_id_tab_type,
150 x_return_status OUT NOCOPY VARCHAR2,
151 x_message_data OUT NOCOPY VARCHAR2) IS
152
153 l_progress VARCHAR2(3);
154 l_exptab GMD_UTILITY_PKG.exptab ;
155 l_test_in_rec GMD_QC_TESTS%ROWTYPE ;
156 l_test_rec GMD_QC_TESTS%ROWTYPE ;
157 BEGIN
158 l_progress := '010';
159
160 x_return_status := FND_API.G_RET_STS_SUCCESS ;
161 IF FND_API.to_Boolean( p_init_msg_list ) THEN
162 FND_MSG_PUB.initialize; -- clear the message stack.
163 END IF;
164
165 IF p_expression IS NULL THEN
166 FND_MESSAGE.Set_Name('GMD','GMD_NO_EXPRESSION');
167 FND_MSG_PUB.ADD;
168 RAISE FND_API.G_EXC_ERROR ;
169 END IF;
170
171 l_progress := '020';
172
173 GMD_UTILITY_PKG.parse(x_exp => p_expression,
174 x_exptab => l_exptab,
175 x_return_status => x_return_status);
176
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
179 RETURN;
180 END IF;
181
182 l_progress := '030';
183
184 FOR i IN 1..l_exptab.COUNT
185 LOOP
186 IF l_exptab(i).pvalue_type = 'O' THEN
187 l_test_in_rec.test_code := l_exptab(i).poperand ;
188 l_test_in_rec.test_id := NULL;
189
190 IF NOT ( GMD_QC_TESTS_PVT.Fetch_Row(
191 p_gmd_qc_tests => l_test_in_rec,
192 x_gmd_qc_tests => l_test_rec)) THEN
193 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_TEST_EXP');
194 FND_MESSAGE.SET_TOKEN('TEST',l_exptab(i).poperand);
195 FND_MSG_PUB.ADD;
196 RAISE FND_API.G_EXC_ERROR ;
197 ELSIF l_test_rec.delete_mark = 1 OR l_test_rec.test_type IN ('U','V','T','E') THEN
198 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_TEST_EXP');
199 FND_MESSAGE.SET_TOKEN('TEST',l_exptab(i).poperand);
200 FND_MSG_PUB.ADD;
201 RAISE FND_API.G_EXC_ERROR ;
202 END IF;
203
204 -- valid test
205
206 IF x_test_tab.EXISTS(l_test_rec.test_id) THEN
207 NULL; -- if test already exist don't add again
208 ELSE
209 x_test_tab(l_test_rec.test_id):= l_test_rec.test_id ;
210 END IF;
211 END IF;
212 END LOOP ;
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_TESTS_GRP.VALIDATE_EXPRESSION' );
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 END VALIDATE_EXPRESSION;
228
229
230
231 /*===========================================================================
232
233 PROCEDURE NAME: insert_exp_test_values
234
235 ===========================================================================*/
236 PROCEDURE insert_exp_test_values(
237 p_init_msg_list IN VARCHAR2 ,
238 p_test_id IN NUMBER,
239 p_test_id_tab IN exp_test_id_tab_type,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_message_data OUT NOCOPY VARCHAR2) IS
242
243 l_progress VARCHAR2(3);
244 i BINARY_INTEGER;
245 l_rowid ROWID;
246 l_test_value_id NUMBER;
247
248 BEGIN
249 l_progress := '010';
250
251 x_return_status := FND_API.G_RET_STS_SUCCESS ;
252 IF FND_API.to_Boolean( p_init_msg_list ) THEN
253 FND_MSG_PUB.initialize; -- clear the message stack.
254 END IF;
255
256 IF p_test_id IS NULL THEN
257 FND_MESSAGE.Set_Name('GMD','GMD_TEST_ID_CODE_NULL');
258 FND_MSG_PUB.ADD;
259 RAISE FND_API.G_EXC_ERROR ;
260 END IF;
261
262 l_progress := '020';
263
264 DELETE from GMD_QC_TEST_VALUES_TL
265 WHERE test_value_id IN
266 (SELECT test_value_id FROM gmd_qc_test_values_b
267 WHERE test_id = p_test_id );
268
269 DELETE from GMD_QC_TEST_VALUES_B
270 WHERE test_id = p_test_id;
271
272 l_progress := '030';
273
274 IF p_test_id_tab.COUNT > 0 THEN
275 i := p_test_id_tab.FIRST;
276 WHILE i IS NOT NULL
277 LOOP
278 l_test_value_id := NULL;
279 GMD_QC_TEST_VALUES_PVT.INSERT_ROW(
280 X_ROWID => l_rowid,
281 X_TEST_ID => p_test_id,
282 X_TEST_VALUE_ID => l_test_value_id,
283 X_EXPRESSION_REF_TEST_ID => p_test_id_tab(i),
284 X_DISPLAY_LABEL_NUMERIC_RANGE => 'EXPRESSION');
285
286 i := p_test_id_tab.NEXT(i);
287 END LOOP ;
288 END IF;
289
290
291 EXCEPTION
292 WHEN FND_API.G_EXC_ERROR THEN
293 x_return_status := FND_API.G_RET_STS_ERROR ;
294 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
295 WHEN OTHERS THEN
296 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
297 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.INSERT_EXP_TEST_VALUES' );
298 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
299 FND_MESSAGE.Set_Token('POSITION',l_progress );
300 FND_MSG_PUB.ADD;
301 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 END insert_exp_test_values;
304
305
306 /*===========================================================================
307
308 PROCEDURE NAME: DISPLAY_REPORT_PRECISION
309 Parameters
310 p_validation_level - DISPLAY_PRECISION to validate DISPLAY_PRECISION from FORM only
311 - REPORT_PRECISION to validate REPORT_PRECISION from FORM only
312 - FULL to validate both display and test precision columns
313 p_test_method_id - Test method associated with the Test.
314 p_test_id - Test id
315
316 ===========================================================================*/
317
318 PROCEDURE DISPLAY_REPORT_PRECISION
319 (p_validation_level IN VARCHAR2,
320 p_init_msg_list IN VARCHAR2 ,
321 p_test_method_id IN NUMBER,
322 p_test_id IN NUMBER,
323 p_new_display_precision IN OUT NOCOPY NUMBER,
324 p_new_report_precision IN OUT NOCOPY NUMBER,
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_message_data OUT NOCOPY VARCHAR2) IS
327
328 l_progress VARCHAR2(3);
329 l_test_method_precision NUMBER(2);
330 l_test_in_rec GMD_QC_TESTS%ROWTYPE ;
331 l_test_rec GMD_QC_TESTS%ROWTYPE ;
332 l_old_display_precision NUMBER(2);
333 l_old_report_precision NUMBER(2);
334
335 BEGIN
336 l_progress := '010';
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS ;
339 IF FND_API.to_Boolean( p_init_msg_list ) THEN
340 FND_MSG_PUB.initialize; -- clear the message stack.
341 END IF;
342
343 IF (p_new_report_precision IS NOT NULL AND p_new_report_precision not between 0 and 9)
344 THEN
345 FND_MESSAGE.Set_Name('GMD','GMD_INVALID_PRECISION');
346 FND_MESSAGE.Set_Token('PRECISION',p_new_report_precision);
347 FND_MSG_PUB.ADD;
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350
351 IF (p_new_display_precision IS NOT NULL AND p_new_display_precision not between 0 and 9)
352 THEN
353 FND_MESSAGE.Set_Name('GMD','GMD_INVALID_PRECISION');
354 FND_MESSAGE.Set_Token('PRECISION',p_new_display_precision);
355 FND_MSG_PUB.ADD;
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358
359 IF (p_validation_level = 'DISPLAY_PRECISION') AND (p_new_display_precision IS NULL) THEN
360 RETURN;
361 END IF;
362
363 IF (p_validation_level = 'REPORT_PRECISION') AND (p_new_report_precision IS NULL) THEN
364 RETURN;
365 END IF;
366
367
368 l_progress := '020';
369
370 IF p_test_method_id IS NOT NULL THEN
371 BEGIN
372 SELECT display_precision INTO l_test_method_precision
373 FROM GMD_TEST_METHODS_B
374 WHERE test_method_id = p_test_method_id ;
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 FND_MESSAGE.Set_Name('GMD','GMD_INVALID_TEST_METHOD');
379 FND_MESSAGE.Set_Token('TEST_METHOD',to_char(p_test_method_id));
380 FND_MSG_PUB.ADD;
381 RAISE FND_API.G_EXC_ERROR;
382 END ;
383 END IF;
384
385 l_progress := '030';
386
387 IF p_test_id IS NOT NULL THEN
388 l_test_in_rec.test_id := p_test_id ;
389
390 IF (GMD_QC_TESTS_PVT.Fetch_Row(p_gmd_qc_tests => l_test_in_rec,
391 x_gmd_qc_tests => l_test_rec)) THEN
392 l_old_display_precision := l_test_rec.display_precision;
393 l_old_report_precision := l_test_rec.report_precision;
394
395 IF l_old_display_precision IS NULL OR l_old_report_precision IS NULL THEN
396 FND_MESSAGE.Set_Name('GMD','GMD_INVALID_PRECISION');
397 FND_MSG_PUB.ADD;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400 ELSE
404 RAISE FND_API.G_EXC_ERROR;
401 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_TEST');
402 FND_MESSAGE.SET_TOKEN('TEST',to_char(p_test_id));
403 FND_MSG_PUB.ADD;
405 END IF;
406 END IF;
407
408 l_progress := '040';
409
410 IF (p_validation_level = 'FULL') AND (p_new_display_precision IS NULL) AND (p_new_report_precision IS NULL) THEN
411 p_new_display_precision := NVL(l_test_method_precision,9);
412 p_new_report_precision := p_new_display_precision;
413 RETURN;
414 END IF ;
415
416 l_progress := '050';
417
418 IF p_validation_level IN ('FULL','DISPLAY_PRECISION') THEN
419 IF l_test_method_precision IS NOT NULL AND p_new_display_precision > l_test_method_precision THEN
420 FND_MESSAGE.Set_Name('GMD','GMD_TST_PRCSN_GRTR_TSTMTHD');
421 FND_MESSAGE.Set_Token('TEST_METHOD_PRECISION',TO_CHAR(l_test_method_precision));
422 FND_MSG_PUB.ADD;
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425
426 IF p_new_report_precision > p_new_display_precision THEN
427 FND_MESSAGE.Set_Name('GMD','GMD_REP_GRTR_DIS_PRCSN');
428 FND_MSG_PUB.ADD;
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432 END IF;
433
434 l_progress := '060';
435
436 IF p_test_id IS NOT NULL THEN
437 IF gmd_qc_tests_grp.test_exist_in_spec(p_test_id => p_test_id) THEN
438 IF p_validation_level IN ('FULL','REPORT_PRECISION') THEN
439 IF p_new_report_precision < l_old_report_precision THEN
440 FND_MESSAGE.Set_Name('GMD','GMD_NEW_PRCSN_LESS_OLD');
441 FND_MESSAGE.Set_Token('OLD_PRECISION',to_char(l_old_report_precision));
442 FND_MSG_PUB.ADD;
443 RAISE FND_API.G_EXC_ERROR;
444 END IF;
445 END IF;
446
447 IF p_validation_level IN ('FULL','DISPLAY_PRECISION') THEN
448 IF p_new_display_precision < l_old_display_precision THEN
449 FND_MESSAGE.Set_Name('GMD','GMD_NEW_PRCSN_LESS_OLD');
450 FND_MESSAGE.Set_Token('OLD_PRECISION',to_char(l_old_display_precision));
451 FND_MSG_PUB.ADD;
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454 END IF;
455 END IF;
456 END IF;
457
458 EXCEPTION
459 WHEN FND_API.G_EXC_ERROR THEN
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
462 WHEN OTHERS THEN
463 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
464 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.DISPLAY_REPORT_PRECISION' );
465 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
466 FND_MESSAGE.Set_Token('POSITION',l_progress );
467 FND_MSG_PUB.ADD;
468 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 END DISPLAY_REPORT_PRECISION;
471
472
473 PROCEDURE MIN_MAX_VALUE_NUM(
474 p_init_msg_list IN VARCHAR2 ,
475 p_test_type IN VARCHAR2,
476 p_min_value_num IN NUMBER,
477 p_max_value_num IN NUMBER,
478 x_return_status OUT NOCOPY VARCHAR2,
479 x_message_data OUT NOCOPY VARCHAR2) IS
480
481 l_progress VARCHAR2(3);
482
483 BEGIN
484 l_progress := '010';
485
486 IF p_test_type NOT IN ('N','E') THEN
487 RETURN;
488 END IF;
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS ;
491 IF FND_API.to_Boolean( p_init_msg_list ) THEN
492 FND_MSG_PUB.initialize; -- clear the message stack.
493 END IF;
494
495 l_progress := '020';
496
497 IF p_min_value_num IS NOT NULL AND p_max_value_num IS NOT NULL THEN
498 IF p_min_value_num > p_max_value_num THEN
499 FND_MESSAGE.Set_Name('GMD','QC_MIN_MAX_SPEC');
500 FND_MSG_PUB.ADD;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503 END IF;
504
505 EXCEPTION
506 WHEN FND_API.G_EXC_ERROR THEN
507 x_return_status := FND_API.G_RET_STS_ERROR ;
508 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
509 WHEN OTHERS THEN
510 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
511 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.MIN_MAX_VALUE_NUM');
512 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
513 FND_MESSAGE.Set_Token('POSITION',l_progress );
514 FND_MSG_PUB.ADD;
515 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 END MIN_MAX_VALUE_NUM ;
518
519 /*===========================================================================
520
521 PROCEDURE NAME: validate_experimental_error
522
523 ===========================================================================*/
524 PROCEDURE validate_experimental_error(
525 p_validation_level IN VARCHAR2 ,
526 p_init_msg_list IN VARCHAR2 ,
527 p_exp_error_type IN VARCHAR2,
528 p_spec_value IN NUMBER ,
529 p_action_code IN VARCHAR2 ,
530 p_test_min IN NUMBER,
534
531 p_test_max IN NUMBER,
532 x_return_status OUT NOCOPY VARCHAR2,
533 x_message_data OUT NOCOPY VARCHAR2) IS
535 l_progress VARCHAR2(3);
536
537 BEGIN
538 l_progress := '010';
539
540 x_return_status := FND_API.G_RET_STS_SUCCESS ;
541 IF (p_test_min IS NULL OR p_test_max IS NULL ) AND (p_validation_level = 'SPEC')
542 AND (p_exp_error_type = 'N') THEN
543 RETURN;
544 END IF;
545
546 IF FND_API.to_Boolean( p_init_msg_list ) THEN
547 FND_MSG_PUB.initialize; -- clear the message stack.
548 END IF;
549
550 IF p_exp_error_type IS NOT NULL AND p_exp_error_type NOT IN ('N','P') THEN
551 FND_MESSAGE.Set_Name('GMD','GMD_EXP_ERROR_TYPE_REQ');
552 FND_MSG_PUB.ADD;
553 RAISE FND_API.G_EXC_ERROR;
554 END IF;
555
556 l_progress := '020';
557
558 IF p_exp_error_type IS NOT NULL AND p_spec_value is NOT NULL AND p_validation_level IN ('SPEC','FULL') THEN
559 IF p_exp_error_type = 'N' AND p_test_min IS NOT NULL AND p_test_max IS NOT NULL THEN
560 IF ABS(p_spec_value) > ABS(p_test_max - p_test_min) THEN
561 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_SPEC_VAL_NUM');
562 FND_MESSAGE.SET_TOKEN('MAX_VAL',to_char(ABS(p_test_max - p_test_min)));
563 FND_MSG_PUB.ADD;
564 RAISE FND_API.G_EXC_ERROR;
565 END IF;
566 ELSE
567 IF ABS(p_spec_value) > 100 THEN
568 FND_MESSAGE.Set_Name('GMD','GMD_INVALID_SPEC_VAL_NUM');
569 FND_MESSAGE.SET_TOKEN('MAX_VAL',100);
570 FND_MSG_PUB.ADD;
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573 END IF;
574 END IF;
575
576 IF p_action_code IS NOT NULL and p_spec_value IS NULL AND
577 p_validation_level IN ('ACTION','FULL') THEN
578 FND_MESSAGE.Set_Name('GMD','GMD_EXP_ERR_VAL_REQ_ACTION');
579 FND_MSG_PUB.ADD;
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583
584 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
585 x_return_status := FND_API.G_RET_STS_ERROR ;
586 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
587 WHEN OTHERS THEN
588 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
589 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.validate_experimental_error');
590 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
591 FND_MESSAGE.Set_Token('POSITION',l_progress );
592 FND_MSG_PUB.ADD;
593 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595 END validate_experimental_error;
596
597 --+========================================================================+
598 --| API Name : validate_all_exp_error |
599 --| TYPE : Group |
600 --| Notes : This procedure checks for experimental error region |
601 --| for all the four action codes and spec value |
602 --| HISTORY |
603 --| |
604 --+========================================================================+
605
606 PROCEDURE validate_all_exp_error(
607 p_validation_level IN VARCHAR2 ,
608 p_init_msg_list IN VARCHAR2 ,
609 p_exp_error_type IN VARCHAR2,
610 p_below_spec_min IN NUMBER ,
611 p_below_min_action_code IN VARCHAR2,
612 p_above_spec_min IN NUMBER ,
613 p_above_min_action_code IN VARCHAR2 ,
614 p_below_spec_max IN NUMBER ,
615 p_below_max_action_code IN VARCHAR2 ,
616 p_above_spec_max IN NUMBER ,
617 p_above_max_action_code IN VARCHAR2 ,
618 p_test_min IN NUMBER,
619 p_test_max IN NUMBER,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_message_data OUT NOCOPY VARCHAR2) IS
622
623 l_progress VARCHAR2(3);
624
625 BEGIN
626 l_progress := '010';
627
628 x_return_status := FND_API.G_RET_STS_SUCCESS ;
629
630 IF FND_API.to_Boolean( p_init_msg_list ) THEN
631 FND_MSG_PUB.initialize; -- clear the message stack.
632 END IF;
633
634 IF p_exp_error_type IS NOT NULL AND p_exp_error_type NOT IN ('N','P') THEN
635 FND_MESSAGE.Set_Name('GMD','GMD_EXP_ERROR_TYPE_REQ');
636 FND_MSG_PUB.ADD;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 l_progress := '020';
641
642 validate_experimental_error(
643 p_validation_level => p_validation_level,
644 p_exp_error_type => p_exp_error_type,
645 p_spec_value => p_below_spec_min,
646 p_action_code => p_below_min_action_code,
647 p_test_min => p_test_min,
648 p_test_max => p_test_max,
649 x_return_status => x_return_status,
650 x_message_data => x_message_data );
651
652 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
653 RETURN;
654 END IF;
658 validate_experimental_error(
655
656 l_progress := '030';
657
659 p_validation_level => p_validation_level,
660 p_exp_error_type => p_exp_error_type,
661 p_spec_value => p_above_spec_min,
662 p_action_code => p_above_min_action_code,
663 p_test_min => p_test_min,
664 p_test_max => p_test_max,
665 x_return_status => x_return_status,
666 x_message_data => x_message_data );
667
668 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
669 RETURN;
670 END IF;
671
672 l_progress := '030';
673
674 validate_experimental_error(
675 p_validation_level => p_validation_level,
676 p_exp_error_type => p_exp_error_type,
677 p_spec_value => p_below_spec_max,
678 p_action_code => p_below_max_action_code,
679 p_test_min => p_test_min,
680 p_test_max => p_test_max,
681 x_return_status => x_return_status,
682 x_message_data => x_message_data );
683
684 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
685 RETURN;
686 END IF;
687
688 l_progress := '040';
689
690 validate_experimental_error(
691 p_validation_level => p_validation_level,
692 p_exp_error_type => p_exp_error_type,
693 p_spec_value => p_above_spec_max,
694 p_action_code => p_above_max_action_code,
695 p_test_min => p_test_min,
696 p_test_max => p_test_max,
697 x_return_status => x_return_status,
698 x_message_data => x_message_data );
699
700 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
701 RETURN;
702 END IF;
703
704 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
705 x_return_status := FND_API.G_RET_STS_ERROR ;
706 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
707 WHEN OTHERS THEN
708 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
709 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.validate_all_exp_error');
710 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
711 FND_MESSAGE.Set_Token('POSITION',l_progress );
712 FND_MSG_PUB.ADD;
713 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 END validate_all_exp_error;
716
717
718 /*===========================================================================
719
720 FUNCTION NAME: validate_test_priority
721 DESCRIPTION: This function returns TRUE if test priority is VALID
722 else it returns FALSE.
723 ===========================================================================*/
724
725 FUNCTION validate_test_priority(p_test_priority IN VARCHAR2) RETURN BOOLEAN
726 IS
727 l_temp VARCHAR2(1);
728 BEGIN
729 IF p_test_priority IS NULL THEN
730 RETURN FALSE;
731 END IF;
732
733 SELECT 'X' INTO l_temp
734 FROM fnd_lookup_values
735 WHERE lookup_type = 'GMD_QC_TEST_PRIORITY'
736 AND lookup_code = p_test_priority
737 AND language = userenv('LANG') ;
738
739 RETURN TRUE;
740
741 EXCEPTION WHEN OTHERS THEN
742 RETURN FALSE;
743
744 END validate_test_priority;
745
746
747
748 --+========================================================================+
749 --| API Name : check_for_null_and_fks |
750 --| TYPE : Group |
751 --| Notes : This procedure checks for NULL and Foreign Key |
752 --| constraints for the required filed in the Test |
753 --| Header record. |
754 --| |
755 --| If everything is fine then 'S' is returned in the |
756 --| parameter - x_return_status otherwise return error |
757 --| |
758 --| HISTORY |
759 --| Rameshwar 14-APR-2004 BUG#3545701 |
760 --| Commented the code for non-validated tests |
761 --+========================================================================+
762
763
764 PROCEDURE CHECK_FOR_NULL_AND_FKS(
765 p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
766 x_gmd_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
767 x_return_status OUT NOCOPY VARCHAR2,
768 x_message_data OUT NOCOPY VARCHAR2) IS
769
770 CURSOR cr_check_valid_test_method(p_test_method_id NUMBER) IS
771 SELECT 'x' FROM GMD_TEST_METHODS_B
772 WHERE test_method_id = p_test_method_id
773 AND delete_mark = 0;
774
775 CURSOR cr_check_valid_test_class(p_test_class VARCHAR2) IS
776 SELECT 'x' FROM GMD_TEST_CLASSES_B
777 WHERE test_class = p_test_class
778 AND delete_mark = 0;
779
780
781 CURSOR cr_check_valid_test_unit(p_qcunit_code VARCHAR2) IS
782 SELECT 'x' FROM GMD_UNITS_B
783 WHERE qcunit_code = p_qcunit_code
784 AND delete_mark = 0;
785
789 AND delete_mark = 0;
786 CURSOR cr_check_valid_action_code(p_action_code VARCHAR2) IS
787 SELECT 'x' FROM GMD_ACTIONS_B
788 WHERE action_code = p_action_code
790
791
792 l_progress VARCHAR2(3);
793 l_temp VARCHAR2(1);
794 BEGIN
795
796 l_progress := '010';
797
798 x_return_status := FND_API.G_RET_STS_SUCCESS ;
799
800 x_gmd_qc_tests_rec := p_gmd_qc_tests_rec ;
801
802 -- Test Code
803
804 IF (LTRIM(RTRIM(x_gmd_qc_tests_rec.test_code)) IS NULL) THEN
805 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_ID_CODE_NULL');
806 FND_MSG_PUB.ADD;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 -- Test Description
811
812 IF (LTRIM(RTRIM(x_gmd_qc_tests_rec.test_desc)) IS NULL) THEN
813 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_DESC_REQD');
814 FND_MSG_PUB.ADD;
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817
818 -- Test Method
819
820 IF x_gmd_qc_tests_rec.test_method_id IS NULL THEN
821 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_METHOD_REQD');
822 FND_MSG_PUB.ADD;
823 RAISE FND_API.G_EXC_ERROR;
824 ELSE
825 OPEN cr_check_valid_test_method(x_gmd_qc_tests_rec.test_method_id);
826 FETCH cr_check_valid_test_method INTO l_temp;
827 IF cr_check_valid_test_method%NOTFOUND THEN
828 CLOSE cr_check_valid_test_method;
829 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_TEST_METHOD');
830 FND_MESSAGE.SET_TOKEN('TEST_METHOD', TO_CHAR(x_gmd_qc_tests_rec.test_method_id));
831 FND_MSG_PUB.ADD;
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834 CLOSE cr_check_valid_test_method ;
835
836 END IF;
837
838 l_progress := '020';
839 -- Test Data Type
840
841 IF x_gmd_qc_tests_rec.test_type IS NULL THEN
842 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_TYPE_REQD');
843 FND_MSG_PUB.ADD;
844 RAISE FND_API.G_EXC_ERROR;
845 ELSIF x_gmd_qc_tests_rec.test_type NOT IN ('U','N','E','L','V','T') THEN
846 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_TEST_TYPE');
847 FND_MSG_PUB.ADD;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850
851 -- Test Priority
852 IF x_gmd_qc_tests_rec.priority IS NULL THEN
853 x_gmd_qc_tests_rec.priority := '5N';
854
855 ELSIF (NOT validate_test_priority(p_test_priority => x_gmd_qc_tests_rec.priority)) THEN
856 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_TEST_PRIORITY');
857 FND_MSG_PUB.ADD;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 x_gmd_qc_tests_rec.delete_mark := 0;
862
863 IF x_gmd_qc_tests_rec.test_type NOT IN ('N','E') THEN
864 IF (x_gmd_qc_tests_rec.min_value_num IS NOT NULL OR x_gmd_qc_tests_rec.max_value_num IS NOT NULL) THEN
865 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_RANGE_NOT_REQD');
866 FND_MSG_PUB.ADD;
867 RAISE FND_API.G_EXC_ERROR;
868 END IF;
869
870 ELSE
871 IF (x_gmd_qc_tests_rec.min_value_num IS NULL OR x_gmd_qc_tests_rec.max_value_num IS NULL) THEN
872 FND_MESSAGE.SET_NAME('GMD', 'GMD_TEST_RANGE_REQ');
873 FND_MSG_PUB.ADD;
874 RAISE FND_API.G_EXC_ERROR;
875 END IF;
876 END IF;
877
878 IF x_gmd_qc_tests_rec.test_type = 'E' THEN
879 IF x_gmd_qc_tests_rec.expression IS NULL THEN
880 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXPRESSION_REQD');
881 FND_MSG_PUB.ADD;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884 ELSIF x_gmd_qc_tests_rec.expression IS NOT NULL THEN
885 FND_MESSAGE.SET_NAME('GMD','GMD_EXPRESSION_NOT_REQD');
886 FND_MSG_PUB.ADD;
887 RAISE FND_API.G_EXC_ERROR;
888 END IF;
889
890 IF x_gmd_qc_tests_rec.test_type IN ('U','T','V') THEN
891 --BEGIN BUG#3545701
892 --Commented the code for Non-validated tests.
893 /* IF x_gmd_qc_tests_rec.test_type = 'U' and x_gmd_qc_tests_rec.test_unit IS NOT NULL THEN
894 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_UNIT_NOT_REQD');
895 FND_MSG_PUB.ADD;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF; */
898 --END BUG#3545701
899 IF (x_gmd_qc_tests_rec.display_precision IS NOT NULL OR x_gmd_qc_tests_rec.report_precision IS NOT NULL) THEN
900 FND_MESSAGE.SET_NAME('GMD','GMD_PRECISION_NOT_REQD');
901 FND_MSG_PUB.ADD;
902 RAISE FND_API.G_EXC_ERROR;
903 END IF;
904
905 IF x_gmd_qc_tests_rec.exp_error_type IS NOT NULL THEN
906 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXP_ERROR_TYPE');
907 FND_MSG_PUB.ADD;
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 IF (x_gmd_qc_tests_rec.below_spec_min IS NOT NULL OR x_gmd_qc_tests_rec.below_min_action_code IS NOT NULL )
912 OR (x_gmd_qc_tests_rec.above_spec_min IS NOT NULL OR x_gmd_qc_tests_rec.above_min_action_code IS NOT NULL )
913 OR (x_gmd_qc_tests_rec.below_spec_max IS NOT NULL OR x_gmd_qc_tests_rec.below_max_action_code IS NOT NULL )
914 OR (x_gmd_qc_tests_rec.above_spec_max IS NOT NULL OR x_gmd_qc_tests_rec.above_max_action_code IS NOT NULL ) THEN
915 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERROR_NOT_REQD');
916 FND_MSG_PUB.ADD;
920 IF x_gmd_qc_tests_rec.test_unit IS NULL THEN
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919 ELSE
921 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_UNIT_REQD');
922 FND_MESSAGE.SET_TOKEN('TEST',x_gmd_qc_tests_rec.test_code);
923 FND_MSG_PUB.ADD;
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927 IF (x_gmd_qc_tests_rec.display_precision IS NULL OR x_gmd_qc_tests_rec.report_precision IS NULL ) THEN
928 FND_MESSAGE.SET_NAME('GMD', 'GMD_PRECISION_REQD');
929 FND_MESSAGE.SET_TOKEN('TEST',x_gmd_qc_tests_rec.test_code);
930 FND_MSG_PUB.ADD;
931 RAISE FND_API.G_EXC_ERROR;
932 END IF;
933
934 IF ((x_gmd_qc_tests_rec.exp_error_type IN ('N','P')) OR (x_gmd_qc_tests_rec.exp_error_type IS NULL)) THEN
935 NULL ;
936 ELSE
937 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXP_ERROR_TYPE');
938 FND_MSG_PUB.ADD;
939 RAISE FND_API.G_EXC_ERROR;
940 END IF;
941
942 END IF;
943
944 l_progress := '030';
945
946 IF x_gmd_qc_tests_rec.exp_error_type IS NULL AND
947 (x_gmd_qc_tests_rec.below_spec_min IS NOT NULL OR x_gmd_qc_tests_rec.above_spec_min IS NOT NULL
948 OR x_gmd_qc_tests_rec.below_spec_max IS NOT NULL OR x_gmd_qc_tests_rec.above_spec_max IS NOT NULL)
949 THEN
950 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERROR_TYPE_REQ');
951 FND_MSG_PUB.ADD;
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954
955 IF x_gmd_qc_tests_rec.exp_error_type IS NOT NULL AND
956 (x_gmd_qc_tests_rec.below_spec_min IS NULL AND x_gmd_qc_tests_rec.above_spec_min IS NULL
957 AND x_gmd_qc_tests_rec.below_spec_max IS NULL AND x_gmd_qc_tests_rec.above_spec_max IS NULL)
958 THEN
959 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERR_TYPE_NULL');
960 FND_MSG_PUB.ADD;
961 RAISE FND_API.G_EXC_ERROR;
962 END IF;
963
964 IF x_gmd_qc_tests_rec.TEST_UNIT IS NOT NULL THEN
965 OPEN cr_check_valid_test_unit(x_gmd_qc_tests_rec.test_unit);
966 FETCH cr_check_valid_test_unit INTO l_temp;
967 IF cr_check_valid_test_unit%NOTFOUND THEN
968 CLOSE cr_check_valid_test_unit;
969 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_TEST_UNIT');
970 FND_MESSAGE.SET_TOKEN('TEST_UNIT',x_gmd_qc_tests_rec.test_unit);
971 FND_MSG_PUB.ADD;
972 RAISE FND_API.G_EXC_ERROR;
973 END IF;
974 CLOSE cr_check_valid_test_unit ;
975 END IF;
976
977 IF x_gmd_qc_tests_rec.TEST_CLASS IS NOT NULL THEN
978 OPEN cr_check_valid_test_class(x_gmd_qc_tests_rec.test_class);
979 FETCH cr_check_valid_test_class INTO l_temp;
980 IF cr_check_valid_test_class%NOTFOUND THEN
981 CLOSE cr_check_valid_test_class;
982 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_TEST_CLASS');
983 FND_MESSAGE.SET_TOKEN('TEST_CLASS', x_gmd_qc_tests_rec.test_class);
984 FND_MSG_PUB.ADD;
985 RAISE FND_API.G_EXC_ERROR;
986 END IF;
987 CLOSE cr_check_valid_test_class ;
988 END IF;
989
990 l_progress := '040';
991
992 IF x_gmd_qc_tests_rec.BELOW_MIN_ACTION_CODE IS NOT NULL THEN
993 OPEN cr_check_valid_action_code(x_gmd_qc_tests_rec.below_min_action_code);
994 FETCH cr_check_valid_action_code INTO l_temp;
995 IF cr_check_valid_action_code%NOTFOUND THEN
996 CLOSE cr_check_valid_action_code;
997 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ACTION_CODE');
998 FND_MESSAGE.SET_TOKEN('ACTION', x_gmd_qc_tests_rec.below_min_action_code);
999 FND_MSG_PUB.ADD;
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002 CLOSE cr_check_valid_action_code ;
1003 END IF;
1004
1005 IF x_gmd_qc_tests_rec.ABOVE_MIN_ACTION_CODE IS NOT NULL THEN
1006 OPEN cr_check_valid_action_code(x_gmd_qc_tests_rec.above_min_action_code);
1007 FETCH cr_check_valid_action_code INTO l_temp;
1008 IF cr_check_valid_action_code%NOTFOUND THEN
1009 CLOSE cr_check_valid_action_code;
1010 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ACTION_CODE');
1011 FND_MESSAGE.SET_TOKEN('ACTION', x_gmd_qc_tests_rec.above_min_action_code);
1012 FND_MSG_PUB.ADD;
1013 RAISE FND_API.G_EXC_ERROR;
1014 END IF;
1015 CLOSE cr_check_valid_action_code ;
1016 END IF;
1017
1018 IF x_gmd_qc_tests_rec.BELOW_MAX_ACTION_CODE IS NOT NULL THEN
1019 OPEN cr_check_valid_action_code(x_gmd_qc_tests_rec.below_max_action_code);
1020 FETCH cr_check_valid_action_code INTO l_temp;
1021 IF cr_check_valid_action_code%NOTFOUND THEN
1022 CLOSE cr_check_valid_action_code;
1023 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ACTION_CODE');
1024 FND_MESSAGE.SET_TOKEN('ACTION', x_gmd_qc_tests_rec.below_max_action_code);
1025 FND_MSG_PUB.ADD;
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028 CLOSE cr_check_valid_action_code ;
1029 END IF;
1030
1031 IF x_gmd_qc_tests_rec.ABOVE_MAX_ACTION_CODE IS NOT NULL THEN
1032 OPEN cr_check_valid_action_code(x_gmd_qc_tests_rec.above_max_action_code);
1033 FETCH cr_check_valid_action_code INTO l_temp;
1034 IF cr_check_valid_action_code%NOTFOUND THEN
1035 CLOSE cr_check_valid_action_code;
1039 RAISE FND_API.G_EXC_ERROR;
1036 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ACTION_CODE');
1037 FND_MESSAGE.SET_TOKEN('ACTION', x_gmd_qc_tests_rec.above_max_action_code);
1038 FND_MSG_PUB.ADD;
1040 END IF;
1041 CLOSE cr_check_valid_action_code ;
1042 END IF;
1043
1044 -- make test_id null. generate test id with sequence.
1045 x_gmd_qc_tests_rec.test_id := NULL;
1046
1047 EXCEPTION
1048 WHEN FND_API.G_EXC_ERROR THEN
1049 x_return_status := FND_API.G_RET_STS_ERROR ;
1050 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1051
1052 WHEN OTHERS THEN
1053 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1054 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.check_for_null_and_fks');
1055 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1056 FND_MESSAGE.Set_Token('POSITION',l_progress );
1057 FND_MSG_PUB.ADD;
1058 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060
1061 END check_for_null_and_fks;
1062
1063
1064 PROCEDURE VALIDATE_BEFORE_INSERT(
1065 p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
1066 x_gmd_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_message_data OUT NOCOPY VARCHAR2) IS
1069
1070 l_progress VARCHAR2(3);
1071 l_exp_test_id_tab exp_test_id_tab_type;
1072
1073 BEGIN
1074 l_progress := '010';
1075
1076 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1077
1078 CHECK_FOR_NULL_AND_FKS(
1079 p_gmd_qc_tests_rec => p_gmd_qc_tests_rec,
1080 x_gmd_qc_tests_rec => x_gmd_qc_tests_rec,
1081 x_return_status => x_return_status,
1082 x_message_data => x_message_data );
1083
1084 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1085 RETURN;
1086 END IF;
1087
1088 -- check for duplicate test_code.
1089 IF CHECK_TEST_EXIST(p_test_code => x_gmd_qc_tests_rec.test_code) THEN
1090 FND_MESSAGE.SET_NAME('GMD','SY_WFDUPLICATE');
1091 FND_MSG_PUB.ADD;
1092 RAISE FND_API.G_EXC_ERROR;
1093 END IF;
1094
1095 IF x_gmd_qc_tests_rec.test_type = 'E' THEN
1096 validate_expression(
1097 p_expression => x_gmd_qc_tests_rec.expression,
1098 x_test_tab => l_exp_test_id_tab,
1099 x_return_status => x_return_status,
1100 x_message_data => x_message_data );
1101
1102 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1103 RETURN;
1104 END IF;
1105 END IF;
1106
1107 IF x_gmd_qc_tests_rec.test_type in ('N','L','E') THEN
1108 DISPLAY_REPORT_PRECISION
1109 (p_validation_level => 'FULL',
1110 p_test_method_id => x_gmd_qc_tests_rec.test_method_id,
1111 p_test_id => null,
1112 p_new_display_precision => x_gmd_qc_tests_rec.display_precision,
1113 p_new_report_precision => x_gmd_qc_tests_rec.report_precision,
1114 x_return_status => x_return_status,
1115 x_message_data => x_message_data );
1116
1117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1118 RETURN;
1119 END IF;
1120 END IF;
1121
1122 -- In case of numeric range with label,min_value_num and max_value_num is derived
1123 -- after test values are insert. so do this validation after insertion of test values.
1124
1125 IF x_gmd_qc_tests_rec.test_type in ('N','L','E') THEN
1126
1127 x_gmd_qc_tests_rec.min_value_num := ROUND(x_gmd_qc_tests_rec.min_value_num,x_gmd_qc_tests_rec.display_precision);
1128 x_gmd_qc_tests_rec.max_value_num := ROUND(x_gmd_qc_tests_rec.max_value_num,x_gmd_qc_tests_rec.display_precision);
1129
1130
1131 MIN_MAX_VALUE_NUM(
1132 p_test_type => x_gmd_qc_tests_rec.test_type,
1133 p_min_value_num => x_gmd_qc_tests_rec.min_value_num,
1134 p_max_value_num => x_gmd_qc_tests_rec.max_value_num,
1135 x_return_status => x_return_status,
1136 x_message_data => x_message_data );
1137
1138 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1139 RETURN;
1140 END IF;
1141
1142 IF x_gmd_qc_tests_rec.exp_error_type IS NOT NULL THEN
1143 IF x_gmd_qc_tests_rec.exp_error_type = 'N' THEN
1144 x_gmd_qc_tests_rec.below_spec_min := ROUND(x_gmd_qc_tests_rec.below_spec_min,x_gmd_qc_tests_rec.display_precision);
1145 x_gmd_qc_tests_rec.above_spec_min := ROUND(x_gmd_qc_tests_rec.above_spec_min,x_gmd_qc_tests_rec.display_precision);
1146 x_gmd_qc_tests_rec.below_spec_max := ROUND(x_gmd_qc_tests_rec.below_spec_max,x_gmd_qc_tests_rec.display_precision);
1147 x_gmd_qc_tests_rec.above_spec_max := ROUND(x_gmd_qc_tests_rec.above_spec_max,x_gmd_qc_tests_rec.display_precision);
1148 END IF;
1149
1150 validate_all_exp_error(
1151 p_exp_error_type => x_gmd_qc_tests_rec.exp_error_type,
1152 p_below_spec_min => x_gmd_qc_tests_rec.below_spec_min,
1153 p_below_min_action_code => x_gmd_qc_tests_rec.below_min_action_code,
1157 p_below_max_action_code => x_gmd_qc_tests_rec.below_max_action_code,
1154 p_above_spec_min => x_gmd_qc_tests_rec.above_spec_min,
1155 p_above_min_action_code => x_gmd_qc_tests_rec.above_min_action_code,
1156 p_below_spec_max => x_gmd_qc_tests_rec.below_spec_max,
1158 p_above_spec_max => x_gmd_qc_tests_rec.above_spec_max,
1159 p_above_max_action_code => x_gmd_qc_tests_rec.above_max_action_code,
1160 p_test_min => x_gmd_qc_tests_rec.min_value_num,
1161 p_test_max => x_gmd_qc_tests_rec.max_value_num,
1162 x_return_status => x_return_status,
1163 x_message_data => x_message_data );
1164
1165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1166 RETURN;
1167 END IF;
1168
1169 END IF;
1170
1171 END IF; -- IF x_gmd_qc_tests_rec.test_type in ('N','L','E') THEN
1172
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 x_return_status := FND_API.G_RET_STS_ERROR ;
1176 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1177
1178 WHEN OTHERS THEN
1179 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1180 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.validate_before_insert');
1181 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1182 FND_MESSAGE.Set_Token('POSITION',l_progress );
1183 FND_MSG_PUB.ADD;
1184 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 END validate_before_insert ;
1187
1188 /*===========================================================================
1189
1190 PROCEDURE NAME: process_after_insert
1191 DESCRIPTION: This procedure inserts records into test values for expression
1192 test data type.
1193 ===========================================================================*/
1194
1195 PROCEDURE PROCESS_AFTER_INSERT (
1196 p_init_msg_list IN VARCHAR2 ,
1197 p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
1198 x_return_status OUT NOCOPY VARCHAR2,
1199 x_message_data OUT NOCOPY VARCHAR2) IS
1200
1201 l_progress VARCHAR2(3);
1202 l_exp_test_id_tab exp_test_id_tab_type;
1203
1204 BEGIN
1205 l_progress := '010';
1206
1207 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1208
1209 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1210 FND_MSG_PUB.initialize; -- clear the message stack.
1211 END IF;
1212
1213 IF p_gmd_qc_tests_rec.test_id IS NULL THEN
1214 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_CODE_NULL');
1215 FND_MSG_PUB.ADD;
1216 RAISE FND_API.G_EXC_ERROR;
1217 END IF;
1218
1219 IF p_gmd_qc_tests_rec.test_type = 'E' THEN
1220 validate_expression(
1221 p_expression => p_gmd_qc_tests_rec.expression,
1222 x_test_tab => l_exp_test_id_tab,
1223 x_return_status => x_return_status,
1224 x_message_data => x_message_data );
1225
1226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1227 RETURN;
1228 END IF;
1229
1230 IF l_exp_test_id_tab.COUNT > 0 THEN
1231 GMD_QC_TESTS_GRP.INSERT_EXP_TEST_VALUES(
1232 p_test_id => p_gmd_qc_tests_rec.test_id,
1233 p_test_id_tab => l_exp_test_id_tab,
1234 x_return_status => x_return_status,
1235 x_message_data => x_message_data);
1236 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1237 RETURN;
1238 END IF;
1239 END IF;
1240
1241 END IF; -- IF p_gmd_qc_tests_rec.test_type = 'E' THEN
1242
1243 EXCEPTION
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 x_return_status := FND_API.G_RET_STS_ERROR ;
1246 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1247
1248 WHEN OTHERS THEN
1249 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1250 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.process_after_insert');
1251 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1252 FND_MESSAGE.Set_Token('POSITION',l_progress );
1253 FND_MSG_PUB.ADD;
1254 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1256 END process_after_insert ;
1257
1258 /*===========================================================================
1259 FUNCTION NAME: test_group_order_exist
1260 DESCRIPTION: This function checks if there is already an existing test group order
1261 present, matching the given test group order, for a given test class.
1262 Added as part of Test Groups Enh Bug: 3447472
1263 ===========================================================================*/
1264 FUNCTION test_group_order_exist(
1265 p_init_msg_list IN VARCHAR2 ,
1266 p_test_class IN VARCHAR2 ,
1267 p_test_group_order IN NUMBER )
1268 RETURN BOOLEAN IS
1269 l_progress VARCHAR2(3);
1270 l_exists VARCHAR2(1);
1271 BEGIN
1272 l_progress := '010';
1273
1274 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1275 FND_MSG_PUB.initialize; -- clear the message stack.
1276 END IF;
1277
1278
1279 l_progress := '020';
1280
1281 BEGIN
1282 IF p_test_class IS NOT NULL AND
1283 p_test_group_order IS NOT NULL THEN
1284 SELECT 'X' INTO l_exists
1285 FROM GMD_QC_TESTS_B
1286 WHERE test_class = p_test_class
1287 AND test_group_order = p_test_group_order
1288 AND rownum =1 ;
1289 RETURN TRUE;
1290 END IF;
1291 RETURN FALSE;
1292 EXCEPTION
1293 WHEN NO_DATA_FOUND THEN
1294 RETURN FALSE;
1295 END;
1296 EXCEPTION WHEN OTHERS
1297 THEN
1298 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1299 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.test_group_order_exist' );
1300 FND_MESSAGE.Set_Token('ERROR', SUBSTR(SQLERRM,1,100));
1301 FND_MESSAGE.Set_Token('POSITION',l_progress );
1302 FND_MSG_PUB.ADD;
1303 RETURN FALSE;
1304 END test_group_order_exist;
1305
1306 /*===========================================================================
1307 FUNCTION NAME: POPULATE_TEST_GRP_GT
1308 Change History
1309 Manish Gupta Created 05-04-2004
1310 Added as part of Test Groups Enh Bug: 3447472
1311 RLNAGARA B5099998 23-Mar-2006 Added the order by clause in the insert statement so that the
1312 tests are inserted in the table according to the given order.
1313 ===========================================================================*/
1314 PROCEDURE POPULATE_TEST_GRP_GT(p_test_class IN varchar2,
1315 p_spec_id IN NUMBER default NULL,
1316 p_sample_id IN NUMBER default NULL,
1317 x_return_status OUT NOCOPY VARCHAR2) IS
1318
1319 CURSOR c_get_spec_id(p_sample_id NUMBER) IS
1320 select c.spec_id
1321 from gmd_samples a,
1322 gmd_sampling_events b,
1323 gmd_event_spec_disp c
1324 where a.sample_id = p_sample_id
1325 and a.sampling_event_id = b.sampling_event_id
1326 and a.sampling_event_id = c.sampling_event_id
1327 and nvl(c.spec_used_for_lot_attrib_ind,'N') ='Y';
1328
1329 CURSOR c_get_test_id IS
1330 select test_id
1331 from gmd_test_group_gt;
1332
1333
1334 l_spec_id NUMBER;
1335 l_test_qty_uom VARCHAR2(80);
1336 l_test_qty NUMBER;
1337
1338 BEGIN
1339
1340 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1341
1342 delete gmd_test_group_gt;
1343 INSERT INTO GMD_TEST_GROUP_GT
1344 (TEST_GROUP_ORDER,
1345 TEST_CODE,
1346 TEST_ID,
1347 TEST_DESC,
1348 TEST_TYPE,
1349 TEST_QTY,
1350 TEST_QTY_UOM, --rconv
1351 USED_IN_SPEC,
1352 INCLUDE)
1353 SELECT a.TEST_GROUP_ORDER test_group_order,
1354 a.TEST_CODE test_code,
1355 a.TEST_ID,
1356 a.TEST_DESC test_desc,
1357 b.meaning test_type,
1358 c.test_qty,
1359 c.test_qty_uom, --rconv
1360 'N',
1361 'Y'
1362 FROM GMD_QC_TESTS a, gem_lookups b , gmd_test_methods c
1363 where a.test_class = p_test_class
1364 AND b.lookup_type = 'GMD_QC_TEST_TYPE'
1365 AND a.test_type = b.lookup_code
1366 AND a.test_method_id = c.test_method_id
1367 ORDER BY test_group_order; --RLNAGARA B5099998 Added this ORDER BY Clause.
1368
1369 IF (p_spec_id IS NOT NULL) THEN
1370 UPDATE gmd_test_group_gt
1371 SET used_in_spec = 'Y', include = 'N'
1372 WHERE test_id IN (SELECT test_id
1373 FROM gmd_spec_tests
1374 WHERE spec_id = p_spec_id);
1375 ELSIF (p_sample_id IS NOT NULL) THEN
1376 OPEN c_get_spec_id(p_sample_id);
1377 FETCH c_get_spec_id INTO l_spec_id;
1378 CLOSE c_get_spec_id;
1379
1380 FOR l_test_id IN c_get_test_id LOOP
1381 BEGIN
1382 SELECT nvl(a.test_qty_uom, c.test_qty_uom) test_qty_uom,
1383 nvl(a.test_qty, c.test_qty) test_qty
1384 INTO l_test_qty_uom, l_test_qty
1385 FROM gmd_spec_tests a,
1386 gmd_qc_tests b,
1387 gmd_test_methods_b c
1388 WHERE a.spec_id = l_spec_id
1389 AND a.test_id = b.test_id
1390 AND b.test_id = l_test_id.test_id
1391 AND b.test_method_id = c.test_method_id;
1392 EXCEPTION
1393 WHEN NO_DATA_FOUND THEN
1394 SELECT a.test_qty_uom, a.test_qty
1395 INTO l_test_qty_uom, l_test_qty
1396 FROM gmd_test_methods_b a, gmd_qc_tests b
1397 WHERE b.test_id = l_test_id.test_id
1398 AND a.test_method_id = b.test_method_id;
1399 END;
1400
1401
1402
1403 IF (l_test_qty_uom is NOT NULL) THEN
1404 UPDATE gmd_test_group_gt
1405 SET test_qty = l_test_qty,
1406 test_qty_uom = l_test_qty_uom
1407 WHERE test_id = l_test_id.test_id;
1408 END IF;
1409 END LOOP; --for all the test in temp table
1410 END IF; --If sample id is not null
1411 COMMIT;
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1415 FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.POPULATE_TEST_GRP_GT' );
1416 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1417 FND_MSG_PUB.ADD;
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 END POPULATE_TEST_GRP_GT;
1420
1421 /*===========================================================================
1422 FUNCTION NAME: update_test_grp
1423 DESCRIPTION: This procedure updates the Include flag in the Global
1424 temporary table for a given test_id
1425 PARAMETERS: In : p_test_id, p_include
1426 CHANGE HISTORY: Created 24-MAY-04 RBODDU
1427 ===========================================================================*/
1428
1429 PROCEDURE update_test_grp(
1430 p_test_id IN NUMBER ,
1431 p_include IN VARCHAR2,
1432 p_test_qty IN NUMBER,
1433 p_test_uom IN VARCHAR2) IS
1434 BEGIN
1435 UPDATE gmd_test_group_gt SET
1436 include = p_include,
1437 test_qty = p_test_qty,
1438 test_qty_uom = p_test_uom
1439 WHERE test_id = p_test_id;
1440 COMMIT;
1441 END update_test_grp;
1442
1443 END gmd_qc_tests_grp ;
[Home] [Help]