[Home] [Help]
152: x_return_status := FND_API.G_RET_STS_SUCCESS;
153:
154: -- Spec Name
155: IF (ltrim(rtrim(p_spec_header.spec_name)) IS NULL) THEN
156: GMD_API_PUB.Log_Message('GMD_SPEC_NAME_REQD');
157: RAISE FND_API.G_EXC_ERROR;
158: END IF;
159:
160: -- Spec Vers
158: END IF;
159:
160: -- Spec Vers
161: IF (p_spec_header.spec_vers IS NULL) THEN
162: GMD_API_PUB.Log_Message('GMD_SPEC_VERS_REQD');
163: RAISE FND_API.G_EXC_ERROR;
164: ELSIF (p_spec_header.spec_vers < 0) THEN
165: GMD_API_PUB.Log_Message('GMD_SPEC_VERS_INVALID');
166: RAISE FND_API.G_EXC_ERROR;
161: IF (p_spec_header.spec_vers IS NULL) THEN
162: GMD_API_PUB.Log_Message('GMD_SPEC_VERS_REQD');
163: RAISE FND_API.G_EXC_ERROR;
164: ELSIF (p_spec_header.spec_vers < 0) THEN
165: GMD_API_PUB.Log_Message('GMD_SPEC_VERS_INVALID');
166: RAISE FND_API.G_EXC_ERROR;
167: END IF;
168:
169: --Spec Type (Bug 3451973)
169: --Spec Type (Bug 3451973)
170: IF (p_spec_header.spec_type in ('M', 'I')) THEN
171: null ;
172: else
173: GMD_API_PUB.Log_Message('GMD_SPEC_TYPE_NOT_FOUND');
174: RAISE FND_API.G_EXC_ERROR;
175: end if;
176:
177: -- Item ID
177: -- Item ID
178: IF (p_spec_header.inventory_item_id IS NULL)
179: and (p_spec_header.spec_type = 'I') -- Bug 3401368: this is only for item specs
180: THEN
181: GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_REQD');
182: RAISE FND_API.G_EXC_ERROR;
183: ELSE
184: -- Get the Item No
185: OPEN c_item(p_spec_header.inventory_item_id, p_spec_header.owner_organization_id);
186: FETCH c_item INTO x_item_number,l_grade_ctl;
187: IF (c_item%NOTFOUND) and (p_spec_header.spec_type = 'I') -- Bug 3401368: this is only for item specs
188: THEN
189: CLOSE c_item;
190: GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
191: RAISE FND_API.G_EXC_ERROR;
192: END IF;
193: CLOSE c_item;
194: END IF;
205: FETCH c_revision INTO dummy;
206: IF c_revision%NOTFOUND THEN
207: CLOSE c_revision;
208: CLOSE c_rev_ctrl;
209: GMD_API_PUB.Log_Message('GMD_SPEC_REVISION_NOT_FOUND',
210: 'REVISION', p_spec_header.revision);
211: RAISE FND_API.G_EXC_ERROR;
212: END IF; --c_revision%NOTFOUND
213: CLOSE c_revision;
212: END IF; --c_revision%NOTFOUND
213: CLOSE c_revision;
214: ELSIF dummy = 1 THEN --The item is not a revision controlled item
215: CLOSE c_rev_ctrl;
216: GMD_API_PUB.Log_Message('GMD_SPEC_NOT_REVISION_CTRL');
217: RAISE FND_API.G_EXC_ERROR;
218: END IF; --dummy = 2
219: CLOSE c_rev_ctrl;
220: END IF; --(p_spec_header.revision IS NOT NULL)
221: -- End RLNAGARA Bug # 4548546
222:
223: -- Grade
224: IF l_grade_ctl = 'N' and p_spec_header.grade_code IS NOT NULL THEN
225: GMD_API_PUB.Log_Message('GMD_GRADE_NOT_REQD');
226: RAISE FND_API.G_EXC_ERROR;
227: END IF;
228:
229: IF (p_spec_header.grade_code IS NOT NULL) THEN
231: OPEN c_grade(p_spec_header.grade_code);
232: FETCH c_grade INTO dummy;
233: IF c_grade%NOTFOUND THEN
234: CLOSE c_grade;
235: GMD_API_PUB.Log_Message('GMD_SPEC_GRADE_NOT_FOUND',
236: 'GRADE', p_spec_header.grade_code);
237: RAISE FND_API.G_EXC_ERROR;
238: END IF;
239: CLOSE c_grade;
240: END IF;
241:
242: -- Spec Status
243: IF (p_spec_header.spec_status IS NULL) THEN
244: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_REQD');
245: RAISE FND_API.G_EXC_ERROR;
246: ELSE
247: -- Check that Status exist in GMD_QM_STATUS
248: OPEN c_status(p_spec_header.spec_status);
248: OPEN c_status(p_spec_header.spec_status);
249: FETCH c_status INTO dummy;
250: IF c_status%NOTFOUND THEN
251: CLOSE c_status;
252: GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
253: 'STATUS', p_spec_header.spec_status);
254: RAISE FND_API.G_EXC_ERROR;
255: END IF;
256: CLOSE c_status;
257: END IF;
258:
259: -- Owner Orgn Code
260: IF (p_spec_header.owner_organization_id IS NULL) THEN
261: GMD_API_PUB.Log_Message('GMD_SPEC_ORGN_REQD');
262: RAISE FND_API.G_EXC_ERROR;
263: ELSE
264: -- Check that Owner Organization id exist in MTL_PARAMETERS
265: OPEN c_orgn(p_spec_header.owner_organization_id);
265: OPEN c_orgn(p_spec_header.owner_organization_id);
266: FETCH c_orgn INTO dummy;
267: IF c_orgn%NOTFOUND THEN
268: CLOSE c_orgn;
269: GMD_API_PUB.Log_Message('GMD_SPEC_ORGN_ID_NOT_FOUND',
270: 'ORGNID', p_spec_header.owner_organization_id);
271: RAISE FND_API.G_EXC_ERROR;
272: END IF;
273: CLOSE c_orgn;
274: END IF;
275:
276: -- Owner ID
277: IF (p_spec_header.owner_id IS NULL) THEN
278: GMD_API_PUB.Log_Message('GMD_SPEC_OWNER_REQD');
279: RAISE FND_API.G_EXC_ERROR;
280: ELSE
281: -- Get the Owner Name
282: OPEN c_owner(p_spec_header.owner_id);
282: OPEN c_owner(p_spec_header.owner_id);
283: FETCH c_owner INTO x_owner;
284: IF c_owner%NOTFOUND THEN
285: CLOSE c_owner;
286: GMD_API_PUB.Log_Message('GMD_SPEC_OWNER_NOT_FOUND');
287: RAISE FND_API.G_EXC_ERROR;
288: END IF;
289: CLOSE c_owner;
290: END IF;
291:
292:
293: -- Overlay Ind (Bug 3452015)
294: if (nvl(p_spec_header.OVERLAY_IND,'Y') <> 'Y') then
295: GMD_API_PUB.Log_Message('GMD_OVERLAY_NOT_VALID');
296: RAISE FND_API.G_EXC_ERROR;
297: end if ;
298:
299: IF (p_spec_header.OVERLAY_IND is NULL) THEN
297: end if ;
298:
299: IF (p_spec_header.OVERLAY_IND is NULL) THEN
300: IF (p_spec_header.BASE_SPEC_ID IS NOT NULL) THEN
301: GMD_API_PUB.Log_Message('GMD_OVERLAY_NOT_VALID');
302: RAISE FND_API.G_EXC_ERROR;
303: end if;
304: end if;
305:
304: end if;
305:
306: IF (p_spec_header.OVERLAY_IND = 'Y') THEN
307: IF (p_spec_header.BASE_SPEC_ID IS NULL) THEN
308: GMD_API_PUB.Log_Message('GMD_BASE_SPEC_NOT_FOUND',
309: 'BASE_SPEC_ID', p_spec_header.base_spec_id);
310: RAISE FND_API.G_EXC_ERROR;
311: end if;
312: end if;
318: OPEN c_spec(p_spec_header.base_spec_id);
319: FETCH c_spec INTO dummy;
320: IF c_spec%NOTFOUND THEN
321: CLOSE c_spec;
322: GMD_API_PUB.Log_Message('GMD_BASE_SPEC_NOT_FOUND',
323: 'BASE_SPEC_ID', p_spec_header.base_spec_id);
324: RAISE FND_API.G_EXC_ERROR;
325: END IF;
326: CLOSE c_spec;
331: x_return_status := FND_API.G_RET_STS_ERROR ;
332: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
333: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334: WHEN OTHERS THEN
335: GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.check_for_null_and_fks_in_spec',
336: 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
337: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338:
339: END check_for_null_and_fks_in_spec;
410:
411: x_spec_tests := p_spec_tests;
412: -- Test
413: IF x_spec_tests.test_id IS NULL THEN
414: GMD_API_PUB.Log_Message('GMD_TEST_ID_CODE_NULL');
415: RAISE FND_API.G_EXC_ERROR;
416: ELSE
417: OPEN cr_test(x_spec_tests.test_id);
418: FETCH cr_test INTO l_test_code,l_test_method_id,l_test_type,l_test_min_value_num,
418: FETCH cr_test INTO l_test_code,l_test_method_id,l_test_type,l_test_min_value_num,
419: l_test_max_value_num,l_test_priority;
420: IF cr_test%NOTFOUND THEN
421: CLOSE cr_test;
422: GMD_API_PUB.Log_Message('GMD_INVALID_TEST','TEST',x_spec_tests.test_id);
423: RAISE FND_API.G_EXC_ERROR;
424: END IF;
425: CLOSE cr_test ;
426: END IF;
449: -- test method
450: IF x_spec_tests.test_method_id IS NULL THEN
451: x_spec_tests.test_method_id := l_test_method_id;
452: ELSIF x_spec_tests.test_method_id <> l_test_method_id THEN
453: GMD_API_PUB.Log_Message('GMD_SPEC_TST_MTHD_INVALID');
454: RAISE FND_API.G_EXC_ERROR;
455: END IF;
456:
457: OPEN cr_test_method_valid(l_test_method_id);
457: OPEN cr_test_method_valid(l_test_method_id);
458: FETCH cr_test_method_valid INTO l_test_method_id,l_test_method_replicate;
459: IF cr_test_method_valid%NOTFOUND THEN
460: CLOSE cr_test_method_valid;
461: GMD_API_PUB.Log_Message('GMD_TEST_METHOD_DELETED');
462: RAISE FND_API.G_EXC_ERROR;
463: END IF;
464: CLOSE cr_test_method_valid ;
465:
464: CLOSE cr_test_method_valid ;
465:
466: -- test sequence
467: IF x_spec_tests.seq IS NULL THEN
468: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_REQD');
469: RAISE FND_API.G_EXC_ERROR;
470: ELSE
471: IF x_spec_tests.seq <> trunc(x_spec_tests.seq) THEN
472: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_NO');
468: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_REQD');
469: RAISE FND_API.G_EXC_ERROR;
470: ELSE
471: IF x_spec_tests.seq <> trunc(x_spec_tests.seq) THEN
472: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_NO');
473: RAISE FND_API.G_EXC_ERROR;
474: END IF;
475: END IF;
476:
544: RAISE FND_API.G_EXC_ERROR;
545: END IF;
546: ELSE
547: IF (x_spec_tests.display_precision IS NULL OR x_spec_tests.report_precision IS NULL ) THEN
548: GMD_API_PUB.Log_Message('GMD_PRECISION_REQD','TEST',l_test_code);
549: RAISE FND_API.G_EXC_ERROR;
550: END IF;
551:
552: IF (x_spec_tests.display_precision not between 0 and 9) THEN
549: RAISE FND_API.G_EXC_ERROR;
550: END IF;
551:
552: IF (x_spec_tests.display_precision not between 0 and 9) THEN
553: GMD_API_PUB.Log_Message('GMD_INVALID_PRECISION','PRECISION',x_spec_tests.display_precision);
554: RAISE FND_API.G_EXC_ERROR;
555: END IF;
556:
557: IF (x_spec_tests.report_precision not between 0 and 9) THEN
554: RAISE FND_API.G_EXC_ERROR;
555: END IF;
556:
557: IF (x_spec_tests.report_precision not between 0 and 9) THEN
558: GMD_API_PUB.Log_Message('GMD_INVALID_PRECISION','PRECISION',x_spec_tests.report_precision);
559: RAISE FND_API.G_EXC_ERROR;
560: END IF;
561:
562: IF (x_spec_tests.min_value_num IS NULL AND x_spec_tests.max_value_num IS NULL) THEN
614:
615: -- test UOM and Quantity.
616: IF (l_test_type = 'E') THEN
617: IF (x_spec_tests.test_qty_uom IS NOT NULL OR x_spec_tests.test_qty IS NOT NULL) THEN
618: GMD_API_PUB.Log_Message('GMD_TEST_UOM_QTY_NOT_REQD');
619: RAISE FND_API.G_EXC_ERROR;
620: END IF;
621: ELSE
622: IF x_spec_tests.test_qty <= 0 THEN
619: RAISE FND_API.G_EXC_ERROR;
620: END IF;
621: ELSE
622: IF x_spec_tests.test_qty <= 0 THEN
623: GMD_API_PUB.Log_Message('GMD_TEST_QTY_NEG');
624: RAISE FND_API.G_EXC_ERROR;
625: END IF;
626:
627: IF (x_spec_tests.test_qty_uom IS NOT NULL AND x_spec_tests.test_qty IS NULL) OR
625: END IF;
626:
627: IF (x_spec_tests.test_qty_uom IS NOT NULL AND x_spec_tests.test_qty IS NULL) OR
628: (x_spec_tests.test_qty_uom IS NULL AND x_spec_tests.test_qty IS NOT NULL) THEN
629: GMD_API_PUB.Log_Message('GMD_TEST_UOM_QTY_REQD');
630: RAISE FND_API.G_EXC_ERROR;
631: END IF;
632: END IF;
633:
634: IF x_spec_tests.test_priority IS NULL THEN
635: x_spec_tests.test_priority := l_test_priority;
636:
637: ELSIF (NOT GMD_QC_TESTS_GRP.validate_test_priority(p_test_priority => x_spec_tests.test_priority)) THEN
638: GMD_API_PUB.Log_Message('GMD_INVALID_TEST_PRIORITY');
639: RAISE FND_API.G_EXC_ERROR;
640: END IF;
641:
642: -- Replicate Validation
640: END IF;
641:
642: -- Replicate Validation
643: IF x_spec_tests.test_replicate IS NULL THEN
644: GMD_API_PUB.Log_Message('GMD_TEST_REP_REQD');
645: RAISE FND_API.G_EXC_ERROR;
646: ELSIF (l_test_type = 'E' and x_spec_tests.test_replicate <> 1) THEN
647: GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ONE');
648: RAISE FND_API.G_EXC_ERROR;
643: IF x_spec_tests.test_replicate IS NULL THEN
644: GMD_API_PUB.Log_Message('GMD_TEST_REP_REQD');
645: RAISE FND_API.G_EXC_ERROR;
646: ELSIF (l_test_type = 'E' and x_spec_tests.test_replicate <> 1) THEN
647: GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ONE');
648: RAISE FND_API.G_EXC_ERROR;
649: ELSIF (x_spec_tests.test_replicate < l_test_method_replicate) THEN
650: GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ERROR',
651: 'SPEC_TEST', l_test_code);
646: ELSIF (l_test_type = 'E' and x_spec_tests.test_replicate <> 1) THEN
647: GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ONE');
648: RAISE FND_API.G_EXC_ERROR;
649: ELSIF (x_spec_tests.test_replicate < l_test_method_replicate) THEN
650: GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ERROR',
651: 'SPEC_TEST', l_test_code);
652: RAISE FND_API.G_EXC_ERROR;
653: END IF;
654:
658: IF (x_spec_tests.CALC_UOM_CONV_IND IS NULL) or
659: (x_spec_tests.CALC_UOM_CONV_IND = 'Y') then
660: null;
661: else
662: GMD_API_PUB.Log_Message('GMD_UOM_CONV_IND');
663: RAISE FND_API.G_EXC_ERROR;
664: END IF;
665:
666:
669: OPEN cr_action_code(x_spec_tests.below_min_action_code);
670: FETCH cr_action_code INTO l_temp;
671: IF cr_action_code%NOTFOUND THEN
672: CLOSE cr_action_code;
673: GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.below_min_action_code);
674: RAISE FND_API.G_EXC_ERROR;
675: END IF;
676: CLOSE cr_action_code ;
677: END IF;
680: OPEN cr_action_code(x_spec_tests.above_min_action_code);
681: FETCH cr_action_code INTO l_temp;
682: IF cr_action_code%NOTFOUND THEN
683: CLOSE cr_action_code;
684: GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.above_min_action_code);
685: RAISE FND_API.G_EXC_ERROR;
686: END IF;
687: CLOSE cr_action_code ;
688: END IF;
691: OPEN cr_action_code(x_spec_tests.below_max_action_code);
692: FETCH cr_action_code INTO l_temp;
693: IF cr_action_code%NOTFOUND THEN
694: CLOSE cr_action_code;
695: GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.below_max_action_code);
696: RAISE FND_API.G_EXC_ERROR;
697: END IF;
698: CLOSE cr_action_code ;
699: END IF;
702: OPEN cr_action_code(x_spec_tests.above_max_action_code);
703: FETCH cr_action_code INTO l_temp;
704: IF cr_action_code%NOTFOUND THEN
705: CLOSE cr_action_code;
706: GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.above_max_action_code);
707: RAISE FND_API.G_EXC_ERROR;
708: END IF;
709: CLOSE cr_action_code ;
710: END IF;
713: OPEN cr_action_code(x_spec_tests.out_of_spec_action);
714: FETCH cr_action_code INTO l_temp;
715: IF cr_action_code%NOTFOUND THEN
716: CLOSE cr_action_code;
717: GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.out_of_spec_action);
718: RAISE FND_API.G_EXC_ERROR;
719: END IF;
720: CLOSE cr_action_code ;
721: END IF;
722:
723: IF x_spec_tests.use_to_control_step IS NULL OR x_spec_tests.use_to_control_step IN ('N','Y') THEN
724: NULL ;
725: ELSE
726: GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','USE_TO_CONTROL_STEP');
727: RAISE FND_API.G_EXC_ERROR;
728: END IF;
729: IF x_spec_tests.use_to_control_step = 'N' THEN
730: x_spec_tests.use_to_control_step:= NULL;
732:
733: IF x_spec_tests.optional_ind IS NULL OR x_spec_tests.optional_ind IN ('N','Y') THEN
734: NULL ;
735: ELSE
736: GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','OPTIONAL_IND');
737: RAISE FND_API.G_EXC_ERROR;
738: END IF;
739: IF x_spec_tests.optional_ind = 'N' THEN
740: x_spec_tests.optional_ind:= NULL;
742:
743: IF x_spec_tests.print_spec_ind IS NULL OR x_spec_tests.print_spec_ind IN ('N','Y') THEN
744: NULL ;
745: ELSE
746: GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','PRINT_SPEC_IND');
747: RAISE FND_API.G_EXC_ERROR;
748: END IF;
749: IF x_spec_tests.print_spec_ind = 'N' THEN
750: x_spec_tests.print_spec_ind:= NULL;
752:
753: IF x_spec_tests.print_result_ind IS NULL OR x_spec_tests.print_result_ind IN ('N','Y') THEN
754: NULL ;
755: ELSE
756: GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','PRINT_RESULT_IND');
757: RAISE FND_API.G_EXC_ERROR;
758: END IF;
759: IF x_spec_tests.print_result_ind = 'N' THEN
760: x_spec_tests.print_result_ind:= NULL;
762:
763: IF x_spec_tests.retest_lot_expiry_ind IS NULL OR x_spec_tests.retest_lot_expiry_ind IN ('N','Y') THEN
764: NULL ;
765: ELSE
766: GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','RETEST_LOT_EXPIRY_IND');
767: RAISE FND_API.G_EXC_ERROR;
768: END IF;
769: IF x_spec_tests.retest_lot_expiry_ind = 'N' THEN
770: x_spec_tests.retest_lot_expiry_ind:= NULL;
776: x_return_status := FND_API.G_RET_STS_ERROR ;
777: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
779: WHEN OTHERS THEN
780: GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.check_for_null_and_fks_in_stst',
781: 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
782: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
783:
784: END check_for_null_and_fks_in_stst;
850:
851: -- Verify that spec_name and spec_vers are unique
852: IF (p_operation = 'INSERT' AND spec_vers_exist(p_spec_header.spec_name, p_spec_header.spec_vers)) THEN
853: -- Ah...Ha, Spec and Version combination is already used
854: GMD_API_PUB.Log_Message('GMD_SPEC_VERS_EXIST',
855: 'SPEC', p_spec_header.spec_name,
856: 'VERS', p_spec_header.spec_vers);
857: RAISE FND_API.G_EXC_ERROR;
858: END IF;
863: -- Peep...Peep...Security Alert. User does not have access to Owner Organization
864: SELECT organization_code INTO l_owner_organization_code
865: FROM mtl_parameters
866: WHERE organization_id = p_spec_header.owner_organization_id;
867: GMD_API_PUB.Log_Message('GMD_USER_ORGN_NO_ACCESS',
868: 'OWNER', l_owner,
869: 'ORGN', l_owner_organization_code);
870: RAISE FND_API.G_EXC_ERROR;
871: END IF;
877: x_return_status := FND_API.G_RET_STS_ERROR ;
878: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
879: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
880: WHEN OTHERS THEN
881: GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_spec_header',
882: 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
883: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
884:
885:
1176: -- Verify that Seq is unique
1177: IF (spec_test_seq_exist(x_spec_test.spec_id,x_spec_test.seq) )
1178: THEN
1179: -- Seq is already used
1180: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_EXIST', 'SEQ', x_spec_test.seq);
1181: RAISE FND_API.G_EXC_ERROR;
1182: end if ;
1183:
1184:
1184:
1185: -- Verify that Test is unique (added by KYH 01/OCT/02)
1186: IF spec_test_exist(x_spec_test.spec_id,x_spec_test.test_id) THEN
1187: -- Test is already used
1188: GMD_API_PUB.Log_Message('GMD_SPEC_TEST_EXIST', 'TEST_ID', x_spec_test.test_id);
1189: RAISE FND_API.G_EXC_ERROR;
1190: END IF;
1191:
1192: open c_spec_type(x_spec_test.spec_id);
1222: THEN
1223: RAISE error_fetch_item;
1224: END;
1225:
1226: -- GMD_API_PUB.Log_Message('GMD_SPEC_TEST_EXIST', 'TEST_ID', x_spec_test.test_id);
1227: --RAISE FND_API.G_EXC_ERROR;
1228:
1229:
1230: BEGIN
1260: OPEN c_test_value(l_test.test_id, x_spec_test.min_value_char);
1261: FETCH c_test_value INTO x_spec_test.min_value_num;
1262: IF c_test_value%NOTFOUND THEN
1263: CLOSE c_test_value;
1264: GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1265: RAISE FND_API.G_EXC_ERROR;
1266: END IF;
1267: CLOSE c_test_value;
1268: END IF;
1272: OPEN c_test_value(l_test.test_id, x_spec_test.target_value_char);
1273: FETCH c_test_value INTO x_spec_test.target_value_num;
1274: IF c_test_value%NOTFOUND THEN
1275: CLOSE c_test_value;
1276: GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1277: RAISE FND_API.G_EXC_ERROR;
1278: END IF;
1279: CLOSE c_test_value;
1280: END IF;
1283: OPEN c_test_value(l_test.test_id, x_spec_test.max_value_char);
1284: FETCH c_test_value INTO x_spec_test.max_value_num;
1285: IF c_test_value%NOTFOUND THEN
1286: CLOSE c_test_value;
1287: GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1288: RAISE FND_API.G_EXC_ERROR;
1289: END IF;
1290: CLOSE c_test_value;
1291: END IF;
1343:
1344:
1345: -- Lot Retest Indicator
1346: IF ( x_spec_test.retest_lot_expiry_ind = 'Y' and l_item.lot_control_code = 1) THEN
1347: GMD_API_PUB.Log_Message('SPEC_TEST_RETEST_IND_ERROR',
1348: 'SPEC_TEST', l_test.test_code,
1349: 'SPEC_TEST', l_item.concatenated_segments);
1350: RAISE FND_API.G_EXC_ERROR;
1351: END IF;
1373: RAISE FND_API.G_EXC_ERROR;
1374: END IF;
1375:
1376: IF x_spec_test.below_min_action_code IS NOT NULL and x_spec_test.below_spec_min IS NULL THEN
1377: GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1378: RAISE FND_API.G_EXC_ERROR;
1379: END IF;
1380:
1381: IF x_spec_test.above_min_action_code IS NOT NULL and x_spec_test.above_spec_min IS NULL THEN
1378: RAISE FND_API.G_EXC_ERROR;
1379: END IF;
1380:
1381: IF x_spec_test.above_min_action_code IS NOT NULL and x_spec_test.above_spec_min IS NULL THEN
1382: GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1383: RAISE FND_API.G_EXC_ERROR;
1384: END IF;
1385:
1386: IF x_spec_test.below_max_action_code IS NOT NULL and x_spec_test.below_spec_max IS NULL THEN
1383: RAISE FND_API.G_EXC_ERROR;
1384: END IF;
1385:
1386: IF x_spec_test.below_max_action_code IS NOT NULL and x_spec_test.below_spec_max IS NULL THEN
1387: GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1388: RAISE FND_API.G_EXC_ERROR;
1389: END IF;
1390:
1391: IF x_spec_test.above_max_action_code IS NOT NULL and x_spec_test.above_spec_max IS NULL THEN
1388: RAISE FND_API.G_EXC_ERROR;
1389: END IF;
1390:
1391: IF x_spec_test.above_max_action_code IS NOT NULL and x_spec_test.above_spec_max IS NULL THEN
1392: GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1393: RAISE FND_API.G_EXC_ERROR;
1394: END IF;
1395:
1396: END IF;
1432: x_return_status := FND_API.G_RET_STS_ERROR ;
1433: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1435: WHEN OTHERS THEN
1436: GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_spec_test',
1437: 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
1438: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1439:
1440:
1469: BEGIN
1470: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1471:
1472: IF p_spec_id IS NULL THEN
1473: GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1474: RAISE FND_API.G_EXC_ERROR;
1475: END IF;
1476:
1477: -- atleast one test should be present in the spec.
1498: p_spec_id => p_spec_id,
1499: p_exp_test_seq => l_test_seq,
1500: p_exp_test_id => l_test_id ) THEN
1501: CLOSE cr_expression_tests ;
1502: GMD_API_PUB.Log_Message('GMD_SOME_REF_TESTS_MISSING');
1503: RAISE FND_API.G_EXC_ERROR;
1504: END IF;
1505: END LOOP;
1506:
1508: WHEN FND_API.G_EXC_ERROR THEN
1509: x_return_status := FND_API.G_RET_STS_ERROR ;
1510:
1511: WHEN OTHERS THEN
1512: GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_after_insert_all',
1513: 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
1514: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1515:
1516: END validate_after_insert_all;
1920: CLOSE cr_test_values;
1921: RETURN TRUE;
1922: END IF;
1923: CLOSE cr_test_values;
1924: gmd_api_pub.log_message('GMD_VAL_MISSING_NUM_LABEL_TEST','VALUE',p_value);
1925: RETURN FALSE;
1926:
1927: EXCEPTION
1928: WHEN REQ_FIELDS_MISSING THEN
1925: RETURN FALSE;
1926:
1927: EXCEPTION
1928: WHEN REQ_FIELDS_MISSING THEN
1929: gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.VALUE_IN_NUM_RANGE_DISPLAY');
1930: x_return_status := FND_API.G_RET_STS_ERROR ;
1931: RETURN FALSE;
1932: WHEN OTHERS THEN
1933: gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_GRP.VALUE_IN_NUM_RANGE_DISPLAY','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1929: gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.VALUE_IN_NUM_RANGE_DISPLAY');
1930: x_return_status := FND_API.G_RET_STS_ERROR ;
1931: RETURN FALSE;
1932: WHEN OTHERS THEN
1933: gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_GRP.VALUE_IN_NUM_RANGE_DISPLAY','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1934: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1935: RETURN FALSE;
1936: END value_in_num_range_display ;
1937:
2034: RETURN TRUE;
2035:
2036: EXCEPTION
2037: WHEN REQ_FIELDS_MISSING THEN
2038: gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_MIN_TARGET_MAX_VALID');
2039: RETURN FALSE;
2040: WHEN e_min_error THEN
2041: gmd_api_pub.log_message('GMD_SPEC_TEST_MIN_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2042: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2037: WHEN REQ_FIELDS_MISSING THEN
2038: gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_MIN_TARGET_MAX_VALID');
2039: RETURN FALSE;
2040: WHEN e_min_error THEN
2041: gmd_api_pub.log_message('GMD_SPEC_TEST_MIN_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2042: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2043: RETURN FALSE;
2044: WHEN e_max_error THEN
2045: gmd_api_pub.log_message('GMD_SPEC_TEST_MAX_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2041: gmd_api_pub.log_message('GMD_SPEC_TEST_MIN_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2042: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2043: RETURN FALSE;
2044: WHEN e_max_error THEN
2045: gmd_api_pub.log_message('GMD_SPEC_TEST_MAX_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2046: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2047: RETURN FALSE;
2048: WHEN e_target_error THEN
2049: gmd_api_pub.log_message('GMD_SPEC_TEST_TARGET_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2045: gmd_api_pub.log_message('GMD_SPEC_TEST_MAX_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2046: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2047: RETURN FALSE;
2048: WHEN e_target_error THEN
2049: gmd_api_pub.log_message('GMD_SPEC_TEST_TARGET_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2050: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2051: RETURN FALSE;
2052: WHEN OTHERS THEN
2053: gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_MIN_TARGET_MAX_VALID','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
2049: gmd_api_pub.log_message('GMD_SPEC_TEST_TARGET_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
2050: to_char(p_st_max),'SPEC_TEST_TARGET',to_char(p_st_target),'TEST_MIN',to_char(p_t_min),'TEST_MAX',to_char(p_t_max));
2051: RETURN FALSE;
2052: WHEN OTHERS THEN
2053: gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_MIN_TARGET_MAX_VALID','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
2054: RETURN FALSE;
2055: END spec_test_min_target_max_valid;
2056:
2057:
2098: RETURN TRUE;
2099: END IF;
2100:
2101: IF p_exp_error_type NOT IN ( 'N','P') THEN
2102: GMD_API_PUB.Log_Message('GMD_INVALID_EXP_ERROR_TYPE');
2103: RETURN FALSE;
2104: END IF;
2105:
2106: IF p_validation_level IN ('FULL','BELOW_SPEC_MIN') THEN
2109: RETURN TRUE;
2110: END IF;
2111:
2112: IF p_below_spec_min IS NOT NULL AND (p_spec_test_min = p_test_min OR p_test_max = p_test_min) THEN
2113: GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2114: RETURN FALSE;
2115: END IF;
2116:
2117: IF (p_below_spec_min IS NOT NULL AND p_spec_test_min IS NOT NULL and p_test_min IS NOT NULL ) THEN
2126: l_max_value := ABS(p_spec_test_min - p_test_min);
2127: ELSE
2128: l_max_value := (ABS(p_spec_test_min - p_test_min) * 100)/(p_test_max - p_test_min);
2129: END IF;
2130: GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2131: RETURN FALSE;
2132: END IF;
2133: END IF;
2134: END IF;
2141: RETURN TRUE;
2142: END IF;
2143:
2144: IF p_above_spec_max IS NOT NULL AND (p_spec_test_max = p_test_max OR p_test_max = p_test_min) THEN
2145: GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2146: RETURN FALSE;
2147: END IF;
2148:
2149: IF (p_above_spec_max IS NOT NULL AND p_spec_test_max IS NOT NULL and p_test_max IS NOT NULL ) THEN
2158: l_max_value := ABS(p_test_max - p_spec_test_max);
2159: ELSE
2160: l_max_value := (ABS(p_test_max - p_spec_test_max) * 100)/(p_test_max - p_test_min);
2161: END IF;
2162: GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2163: RETURN FALSE;
2164: END IF;
2165: END IF;
2166: END IF;
2173: RETURN TRUE;
2174: END IF;
2175:
2176: IF p_above_spec_min IS NOT NULL AND (p_spec_test_target = p_test_min OR p_test_max = p_test_min) THEN
2177: GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2178: RETURN FALSE;
2179: END IF;
2180:
2181: IF (p_above_spec_min IS NOT NULL AND p_spec_test_min IS NOT NULL and p_spec_test_target IS NOT NULL ) THEN
2190: l_max_value := ABS(p_spec_test_target - p_spec_test_min);
2191: ELSE
2192: l_max_value := (ABS(p_spec_test_target - p_spec_test_min) * 100)/(p_test_max - p_test_min);
2193: END IF;
2194: GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2195: RETURN FALSE;
2196: END IF;
2197: END IF;
2198: END IF;
2205: RETURN TRUE;
2206: END IF;
2207:
2208: IF p_below_spec_max IS NOT NULL AND (p_spec_test_max = p_spec_test_target OR p_test_max = p_test_min) THEN
2209: GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2210: RETURN FALSE;
2211: END IF;
2212:
2213: IF (p_below_spec_max IS NOT NULL AND p_spec_test_max IS NOT NULL and p_spec_test_target IS NOT NULL ) THEN
2222: l_max_value := ABS(p_spec_test_max - p_spec_test_target);
2223: ELSE
2224: l_max_value := (ABS(p_spec_test_max - p_spec_test_target) * 100)/(p_test_max - p_test_min);
2225: END IF;
2226: GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2227: RETURN FALSE;
2228: END IF;
2229: END IF;
2230: END IF;
2232: RETURN TRUE;
2233:
2234: EXCEPTION
2235: WHEN OTHERS THEN
2236: gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_EXP_ERROR_REGION_VAL','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
2237: RETURN FALSE;
2238: END SPEC_TEST_EXP_ERROR_REGION_VAL;
2239:
2240:
2264:
2265: BEGIN
2266:
2267: IF (p_spec_report_precision > p_spec_display_precision) THEN
2268: GMD_API_PUB.Log_Message('GMD_REP_GRTR_DIS_PRCSN');
2269:
2270: RETURN FALSE;
2271: ELSIF (p_spec_display_precision > p_test_display_precision) THEN
2272: GMD_API_PUB.Log_Message('SPEC_TEST_DISPLAY_PREC_ERROR');
2268: GMD_API_PUB.Log_Message('GMD_REP_GRTR_DIS_PRCSN');
2269:
2270: RETURN FALSE;
2271: ELSIF (p_spec_display_precision > p_test_display_precision) THEN
2272: GMD_API_PUB.Log_Message('SPEC_TEST_DISPLAY_PREC_ERROR');
2273:
2274: RETURN FALSE;
2275: ELSIF (p_spec_report_precision > p_test_report_precision) THEN
2276: GMD_API_PUB.Log_Message('SPEC_TEST_REPORT_PREC_ERROR');
2272: GMD_API_PUB.Log_Message('SPEC_TEST_DISPLAY_PREC_ERROR');
2273:
2274: RETURN FALSE;
2275: ELSIF (p_spec_report_precision > p_test_report_precision) THEN
2276: GMD_API_PUB.Log_Message('SPEC_TEST_REPORT_PREC_ERROR');
2277:
2278: RETURN FALSE;
2279: END IF;
2280: