1 PACKAGE BODY GMD_SPEC_GRP AS
2 --$Header: GMDGSPCB.pls 120.3 2006/05/31 14:50:07 ragsriva noship $ */
3
4 -- Global variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_Spec_GRP';
6 --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
7 --forward decl.
8 function set_debug_flag return varchar2;
9 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10 l_debug VARCHAR2(1) := set_debug_flag;
11
12 FUNCTION set_debug_flag RETURN VARCHAR2 IS
13 l_debug VARCHAR2(1):= 'N';
14 BEGIN
15 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
16 l_debug := 'Y';
17 END IF;
18 RETURN l_debug;
19 END set_debug_flag;
20 -- Start of comments
21 --+==========================================================================+
22 --| Copyright (c) 1998 Oracle Corporation |
23 --| Redwood Shores, CA, USA |
24 --| All rights reserved. |
25 --+==========================================================================+
26 --| File Name : GMDGSPCB.pls |
27 --| Package Name : GMD_Spec_GRP |
28 --| Type : Group |
29 --| |
30 --| Notes |
31 --| This package contains group layer APIs for Specification Entity |
32 --| |
33 --| HISTORY |
34 --| Chetan Nagar 26-Jul-2002 Created. |
35 --| Rameshwar 13-APR-2004 BUG#3545701 |
36 --| Commented the code for non-validated test |
37 --| in the check_for_null_and_fks_in_stst procedure |
38 --+==========================================================================+
39 -- End of comments
40
41
42
43 --Start of comments
44 --+========================================================================+
45 --| API Name : check_for_null_and_fks_in_spec |
46 --| TYPE : Group |
47 --| Notes : This procedure checks for NULL and Foreign Key |
48 --| constraints for the required filed in the Spec |
49 --| Header record. |
50 --| |
51 --| If everything is fine then 'S' is returned in the |
52 --| parameter - x_return_status otherwise error message |
53 --| is logged and error status - E or U returned |
54 --| |
55 --| HISTORY |
56 --| Chetan Nagar 26-Jul-2002 Created. |
57 --| |
58 --| Saikiran Vankadari 07-Feb-2005 Changed as part of Convergence |
59 --| RLNAGARA 10-Oct-2005 Bug # 4546546 - Included revision in the inbound criteria |
60 --| |
61 --+========================================================================+
62 -- End of comments
63
64 PROCEDURE check_for_null_and_fks_in_spec
65 ( p_spec_header IN gmd_specifications%ROWTYPE
66 , x_item_number OUT NOCOPY VARCHAR2
67 , x_owner OUT NOCOPY VARCHAR2
68 , x_return_status OUT NOCOPY VARCHAR2
69 ) IS
70
71 -- Bug# 5251612
72 -- Added additional where clause to check process_quality_enabled_flag
73 CURSOR c_item(p_inventory_item_id NUMBER, p_organization_id NUMBER) IS
74 SELECT concatenated_segments,grade_control_flag
75 FROM mtl_system_items_kfv
76 WHERE inventory_item_id = p_inventory_item_id
77 AND organization_id = p_organization_id
78 AND process_quality_enabled_flag = 'Y';
79
80 --RLNAGARA Bug # 4548546 For Revision
81 CURSOR c_rev_ctrl(p_inventory_item_id NUMBER, p_organization_id NUMBER) IS
82 SELECT revision_qty_control_code
83 FROM mtl_system_items_b
84 WHERE inventory_item_id = p_inventory_item_id
85 AND organization_id = p_organization_id;
86
87 CURSOR c_revision(p_inventory_item_id NUMBER, p_organization_id NUMBER,p_revision VARCHAR2) IS
88 SELECT 1
89 FROM mtl_item_revisions
90 WHERE inventory_item_id = p_inventory_item_id
91 AND organization_id = p_organization_id
92 AND revision = p_revision;
93 --RLNAGARA Bug # 4548546 For Revision
94
95 CURSOR c_grade(p_grade VARCHAR2) IS
96 SELECT 1
97 FROM mtl_grades_b
98 WHERE grade_code = p_grade
99 AND disable_flag = 'N';
100
101
102 CURSOR c_status (p_spec_status NUMBER) IS
103 SELECT 1
104 FROM gmd_qc_status
105 WHERE status_code = p_spec_status
106 AND delete_mark = 0
107 and entity_type = 'S';
108
109 CURSOR c_orgn (p_organization_id NUMBER) IS
110 SELECT 1
111 FROM mtl_parameters
112 WHERE organization_id = p_Organization_id;
113
114
115 CURSOR c_owner(p_owner_id NUMBER) IS
116 SELECT user_name
117 FROM fnd_user
118 WHERE user_id = p_owner_id
119 AND start_date <= SYSDATE
120 AND nvl(end_date, SYSDATE + 1) >= SYSDATE;
121
122
123 -- Check for Approved Base Spec (Bug 3401368)
124 CURSOR c_spec (p_spec_id NUMBER) IS
125 SELECT 1
126 FROM gmd_specifications_b
127 WHERE spec_id = p_spec_id
128 AND spec_status = 700 ;
129
130
131 dummy NUMBER;
132 l_grade_ctl VARCHAR2(1);
133
134 BEGIN
135
136 -- Initialize API return status to success
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 -- Spec Name
140 IF (ltrim(rtrim(p_spec_header.spec_name)) IS NULL) THEN
141 GMD_API_PUB.Log_Message('GMD_SPEC_NAME_REQD');
142 RAISE FND_API.G_EXC_ERROR;
143 END IF;
144
145 -- Spec Vers
146 IF (p_spec_header.spec_vers IS NULL) THEN
147 GMD_API_PUB.Log_Message('GMD_SPEC_VERS_REQD');
148 RAISE FND_API.G_EXC_ERROR;
149 ELSIF (p_spec_header.spec_vers < 0) THEN
150 GMD_API_PUB.Log_Message('GMD_SPEC_VERS_INVALID');
151 RAISE FND_API.G_EXC_ERROR;
152 END IF;
153
154 --Spec Type (Bug 3451973)
155 IF (p_spec_header.spec_type in ('M', 'I')) THEN
156 null ;
157 else
158 GMD_API_PUB.Log_Message('GMD_SPEC_TYPE_NOT_FOUND');
159 RAISE FND_API.G_EXC_ERROR;
160 end if;
161
162 -- Item ID
163 IF (p_spec_header.inventory_item_id IS NULL)
164 and (p_spec_header.spec_type = 'I') -- Bug 3401368: this is only for item specs
165 THEN
166 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_REQD');
167 RAISE FND_API.G_EXC_ERROR;
168 ELSE
169 -- Get the Item No
170 OPEN c_item(p_spec_header.inventory_item_id, p_spec_header.owner_organization_id);
171 FETCH c_item INTO x_item_number,l_grade_ctl;
172 IF (c_item%NOTFOUND) and (p_spec_header.spec_type = 'I') -- Bug 3401368: this is only for item specs
173 THEN
174 CLOSE c_item;
175 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 CLOSE c_item;
179 END IF;
180
181 -- Start RLNAGARA Bug # 4548546
182 --For Revision
183 IF (p_spec_header.revision IS NOT NULL) THEN
184 --Check whether it is a revision controlled item in MTL_SYSTEM_ITEMS_B
185 OPEN c_rev_ctrl(p_spec_header.inventory_item_id, p_spec_header.owner_organization_id);
186 FETCH c_rev_ctrl into dummy;
187 IF dummy = 2 THEN --The item is a revision controlled item
188 -- Check that Revision exist in MTL_ITEM_REVISIONS
189 OPEN c_revision(p_spec_header.inventory_item_id, p_spec_header.owner_organization_id,p_spec_header.revision);
190 FETCH c_revision INTO dummy;
191 IF c_revision%NOTFOUND THEN
192 CLOSE c_revision;
193 CLOSE c_rev_ctrl;
194 GMD_API_PUB.Log_Message('GMD_SPEC_REVISION_NOT_FOUND',
195 'REVISION', p_spec_header.revision);
196 RAISE FND_API.G_EXC_ERROR;
197 END IF; --c_revision%NOTFOUND
198 CLOSE c_revision;
199 ELSIF dummy = 1 THEN --The item is not a revision controlled item
200 CLOSE c_rev_ctrl;
201 GMD_API_PUB.Log_Message('GMD_SPEC_NOT_REVISION_CTRL');
202 RAISE FND_API.G_EXC_ERROR;
203 END IF; --dummy = 2
204 CLOSE c_rev_ctrl;
205 END IF; --(p_spec_header.revision IS NOT NULL)
206 -- End RLNAGARA Bug # 4548546
207
208 -- Grade
209 IF l_grade_ctl = 'N' and p_spec_header.grade_code IS NOT NULL THEN
210 GMD_API_PUB.Log_Message('GMD_GRADE_NOT_REQD');
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 IF (p_spec_header.grade_code IS NOT NULL) THEN
215 -- Check that Grade exist in QC_GRAD_MST
216 OPEN c_grade(p_spec_header.grade_code);
217 FETCH c_grade INTO dummy;
218 IF c_grade%NOTFOUND THEN
219 CLOSE c_grade;
220 GMD_API_PUB.Log_Message('GMD_SPEC_GRADE_NOT_FOUND',
221 'GRADE', p_spec_header.grade_code);
222 RAISE FND_API.G_EXC_ERROR;
223 END IF;
224 CLOSE c_grade;
225 END IF;
226
227 -- Spec Status
228 IF (p_spec_header.spec_status IS NULL) THEN
229 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_REQD');
230 RAISE FND_API.G_EXC_ERROR;
231 ELSE
232 -- Check that Status exist in GMD_QM_STATUS
233 OPEN c_status(p_spec_header.spec_status);
234 FETCH c_status INTO dummy;
235 IF c_status%NOTFOUND THEN
236 CLOSE c_status;
237 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
238 'STATUS', p_spec_header.spec_status);
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241 CLOSE c_status;
242 END IF;
243
244 -- Owner Orgn Code
245 IF (p_spec_header.owner_organization_id IS NULL) THEN
246 GMD_API_PUB.Log_Message('GMD_SPEC_ORGN_REQD');
247 RAISE FND_API.G_EXC_ERROR;
248 ELSE
249 -- Check that Owner Organization id exist in MTL_PARAMETERS
250 OPEN c_orgn(p_spec_header.owner_organization_id);
251 FETCH c_orgn INTO dummy;
252 IF c_orgn%NOTFOUND THEN
253 CLOSE c_orgn;
254 GMD_API_PUB.Log_Message('GMD_SPEC_ORGN_ID_NOT_FOUND',
255 'ORGNID', p_spec_header.owner_organization_id);
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258 CLOSE c_orgn;
259 END IF;
260
261 -- Owner ID
262 IF (p_spec_header.owner_id IS NULL) THEN
263 GMD_API_PUB.Log_Message('GMD_SPEC_OWNER_REQD');
264 RAISE FND_API.G_EXC_ERROR;
265 ELSE
266 -- Get the Owner Name
267 OPEN c_owner(p_spec_header.owner_id);
268 FETCH c_owner INTO x_owner;
269 IF c_owner%NOTFOUND THEN
270 CLOSE c_owner;
271 GMD_API_PUB.Log_Message('GMD_SPEC_OWNER_NOT_FOUND');
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274 CLOSE c_owner;
275 END IF;
276
277
278 -- Overlay Ind (Bug 3452015)
279 if (nvl(p_spec_header.OVERLAY_IND,'Y') <> 'Y') then
280 GMD_API_PUB.Log_Message('GMD_OVERLAY_NOT_VALID');
281 RAISE FND_API.G_EXC_ERROR;
282 end if ;
283
284 IF (p_spec_header.OVERLAY_IND is NULL) THEN
285 IF (p_spec_header.BASE_SPEC_ID IS NOT NULL) THEN
286 GMD_API_PUB.Log_Message('GMD_OVERLAY_NOT_VALID');
287 RAISE FND_API.G_EXC_ERROR;
288 end if;
289 end if;
290
291 IF (p_spec_header.OVERLAY_IND = 'Y') THEN
292 IF (p_spec_header.BASE_SPEC_ID IS NULL) THEN
293 GMD_API_PUB.Log_Message('GMD_BASE_SPEC_NOT_FOUND',
294 'BASE_SPEC_ID', p_spec_header.base_spec_id);
295 RAISE FND_API.G_EXC_ERROR;
296 end if;
297 end if;
298
299
300 -- Base Spec ID (Bug 3401368)
301 IF (p_spec_header.BASE_SPEC_ID IS NOT NULL) THEN
302 -- Check to make sure that the base spec is valid
303 OPEN c_spec(p_spec_header.base_spec_id);
304 FETCH c_spec INTO dummy;
305 IF c_spec%NOTFOUND THEN
306 CLOSE c_spec;
307 GMD_API_PUB.Log_Message('GMD_BASE_SPEC_NOT_FOUND',
308 'BASE_SPEC_ID', p_spec_header.base_spec_id);
309 RAISE FND_API.G_EXC_ERROR;
310 END IF;
311 CLOSE c_spec;
312 END IF;
313
314 EXCEPTION
315 WHEN FND_API.G_EXC_ERROR THEN
316 x_return_status := FND_API.G_RET_STS_ERROR ;
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
319 WHEN OTHERS THEN
320 GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.check_for_null_and_fks_in_spec',
321 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
323
324 END check_for_null_and_fks_in_spec;
325
326
327 --Start of comments
328 --+========================================================================+
329 --| API Name : check_for_null_and_fks_in_stst |
330 --| TYPE : Group |
331 --| Notes : This procedure checks for NULL and Foreign Key |
332 --| constraints for the required filed in the Spec |
333 --| Test record. |
334 --| |
335 --| If everything is fine then 'S' is returned in the |
336 --| parameter - x_return_status otherwise error message |
337 --| is logged and error status - E or U returned |
338 --| |
339 --| HISTORY |
340 --| Mahesh Chandak 14-Nov-2002 Created. |
341 --| Rameshwar 12-APR-2004 BUG#3545701 |
342 --| Commented the code for non-validated tests |
343 --| |
344 --| Saikiran Vankadari 07-Feb-2005 Changed as part of Convergence |
345 --| |
346 --+========================================================================+
347 -- End of comments
348
349 PROCEDURE check_for_null_and_fks_in_stst
350 (
351 p_spec_tests IN gmd_spec_tests%ROWTYPE
352 , x_spec_tests OUT NOCOPY gmd_spec_tests%ROWTYPE
353 , x_return_status OUT NOCOPY VARCHAR2
354 ) IS
355
356
357 CURSOR cr_test(p_test_id NUMBER) IS
358 SELECT test_code,test_method_id,test_type,min_value_num,max_value_num,priority
359 FROM gmd_qc_tests_b
360 WHERE test_id = p_test_id
361 AND delete_mark = 0 ;
362
363 CURSOR cr_test_method_valid(p_test_method_id NUMBER) IS
364 SELECT test_method_id,test_replicate
365 FROM gmd_test_methods_b
366 WHERE test_method_id = p_test_method_id
367 AND delete_mark = 0 ;
368
369 CURSOR cr_action_code(p_action_code VARCHAR2) IS
370 SELECT 'x' FROM MTL_ACTIONS_B
371 WHERE action_code = p_action_code
372 AND disable_flag = 'N';
373
374
375 l_temp VARCHAR2(1);
376 l_grade_ctl NUMBER(1);
377 l_test_type VARCHAR2(1);
378 l_test_code GMD_QC_TESTS_B.TEST_CODE%TYPE;
379 l_test_min_value_num NUMBER;
380 l_test_max_value_num NUMBER;
381 l_test_method_id NUMBER;
382 l_test_priority GMD_SPEC_TESTS_B.TEST_PRIORITY%TYPE;
383 l_test_method_replicate NUMBER;
384
385 BEGIN
386
387 -- Initialize API return status to success
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389
390 x_spec_tests := p_spec_tests;
391 -- Test
392 IF x_spec_tests.test_id IS NULL THEN
393 GMD_API_PUB.Log_Message('GMD_TEST_ID_CODE_NULL');
394 RAISE FND_API.G_EXC_ERROR;
395 ELSE
396 OPEN cr_test(x_spec_tests.test_id);
397 FETCH cr_test INTO l_test_code,l_test_method_id,l_test_type,l_test_min_value_num,
398 l_test_max_value_num,l_test_priority;
399 IF cr_test%NOTFOUND THEN
400 CLOSE cr_test;
401 GMD_API_PUB.Log_Message('GMD_INVALID_TEST','TEST',x_spec_tests.test_id);
402 RAISE FND_API.G_EXC_ERROR;
403 END IF;
404 CLOSE cr_test ;
405 END IF;
406
407 -- test method
408 IF x_spec_tests.test_method_id IS NULL THEN
409 x_spec_tests.test_method_id := l_test_method_id;
410 ELSIF x_spec_tests.test_method_id <> l_test_method_id THEN
411 GMD_API_PUB.Log_Message('GMD_SPEC_TST_MTHD_INVALID');
412 RAISE FND_API.G_EXC_ERROR;
413 END IF;
414
415 OPEN cr_test_method_valid(l_test_method_id);
416 FETCH cr_test_method_valid INTO l_test_method_id,l_test_method_replicate;
417 IF cr_test_method_valid%NOTFOUND THEN
418 CLOSE cr_test_method_valid;
419 GMD_API_PUB.Log_Message('GMD_TEST_METHOD_DELETED');
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422 CLOSE cr_test_method_valid ;
423
424 -- test sequence
425 IF x_spec_tests.seq IS NULL THEN
426 GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_REQD');
427 RAISE FND_API.G_EXC_ERROR;
428 ELSE
429 IF x_spec_tests.seq <> trunc(x_spec_tests.seq) THEN
430 GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_NO');
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433 END IF;
434
435 IF l_test_type IN ('U','T','V') THEN
436
437 IF (x_spec_tests.display_precision IS NOT NULL OR x_spec_tests.report_precision IS NOT NULL) THEN
438 FND_MESSAGE.SET_NAME('GMD','GMD_PRECISION_NOT_REQD');
439 FND_MSG_PUB.ADD;
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443 IF (x_spec_tests.min_value_num IS NOT NULL OR x_spec_tests.max_value_num IS NOT NULL) THEN
444 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_NUM_RANGE_NOT_REQD');
445 FND_MSG_PUB.ADD;
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 IF x_spec_tests.target_value_num IS NOT NULL THEN
450 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_NUM_TARGET_NOT_REQD');
451 FND_MSG_PUB.ADD;
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454 --BEGIN BUG#3545701
455 --Commented the code for Non-validated tests.
456 /* IF l_test_type = 'U' and x_spec_tests.target_value_char IS NOT NULL THEN
457 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_CHAR_TARGET_NOT_REQD');
458 FND_MSG_PUB.ADD;
459 RAISE FND_API.G_EXC_ERROR; */
460 --END BUG#3545701
461 IF l_test_type = 'V' and x_spec_tests.target_value_char IS NULL THEN
462 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_CHAR_TARGET_REQD');
463 FND_MSG_PUB.ADD;
464 RAISE FND_API.G_EXC_ERROR;
465 END IF;
466
467 IF (l_test_type = 'T') THEN
468 IF (x_spec_tests.min_value_char IS NULL OR x_spec_tests.max_value_char IS NULL) THEN
469 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_RANGE_REQ');
470 FND_MSG_PUB.ADD;
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473 ELSE
474 IF (x_spec_tests.min_value_char IS NOT NULL OR x_spec_tests.max_value_char IS NOT NULL) THEN
475 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_CHAR_RANGE_NOT_REQD');
476 FND_MSG_PUB.ADD;
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479 END IF;
480
481 --BEGIN BUG#3545701
482 --Commented the code for Non-validated tests.
483 /* IF l_test_type = 'U' and x_spec_tests.out_of_spec_action IS NOT NULL THEN
484 FND_MESSAGE.SET_NAME('GMD','GMD_ACTION_CODE_NOT_REQD');
485 FND_MSG_PUB.ADD;
486 RAISE FND_API.G_EXC_ERROR;
487 END IF; */
488 --END BUG#3545701
489
490 IF x_spec_tests.exp_error_type IS NOT NULL THEN
491 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXP_ERROR_TYPE');
492 FND_MSG_PUB.ADD;
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 IF (x_spec_tests.below_spec_min IS NOT NULL OR x_spec_tests.below_min_action_code IS NOT NULL )
497 OR (x_spec_tests.above_spec_min IS NOT NULL OR x_spec_tests.above_min_action_code IS NOT NULL )
498 OR (x_spec_tests.below_spec_max IS NOT NULL OR x_spec_tests.below_max_action_code IS NOT NULL )
499 OR (x_spec_tests.above_spec_max IS NOT NULL OR x_spec_tests.above_max_action_code IS NOT NULL ) THEN
500 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERROR_NOT_REQD');
501 FND_MSG_PUB.ADD;
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504 ELSE
505 IF (x_spec_tests.display_precision IS NULL OR x_spec_tests.report_precision IS NULL ) THEN
506 GMD_API_PUB.Log_Message('GMD_PRECISION_REQD','TEST',l_test_code);
507 RAISE FND_API.G_EXC_ERROR;
508 END IF;
509
510 IF (x_spec_tests.display_precision not between 0 and 9) THEN
511 GMD_API_PUB.Log_Message('GMD_INVALID_PRECISION','PRECISION',x_spec_tests.display_precision);
512 RAISE FND_API.G_EXC_ERROR;
513 END IF;
514
515 IF (x_spec_tests.report_precision not between 0 and 9) THEN
516 GMD_API_PUB.Log_Message('GMD_INVALID_PRECISION','PRECISION',x_spec_tests.report_precision);
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519
520 IF (x_spec_tests.min_value_num IS NULL AND x_spec_tests.max_value_num IS NULL) THEN
521 FND_MESSAGE.SET_NAME('GMD','GMD_MIN_MAX_REQ');
522 FND_MSG_PUB.ADD;
523 RAISE FND_API.G_EXC_ERROR;
524 END IF;
525
526 IF ((x_spec_tests.min_value_num IS NULL AND l_test_min_value_num IS NOT NULL)
527 OR (x_spec_tests.max_value_num IS NULL AND l_test_max_value_num IS NOT NULL)) THEN
528 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_RANGE_REQ');
529 FND_MSG_PUB.ADD;
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533 IF (x_spec_tests.min_value_char IS NOT NULL OR x_spec_tests.max_value_char IS NOT NULL) THEN
534 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_CHAR_RANGE_NOT_REQD');
535 FND_MSG_PUB.ADD;
536 RAISE FND_API.G_EXC_ERROR;
537 END IF;
538
539 IF x_spec_tests.target_value_char IS NOT NULL THEN
540 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_CHAR_TARGET_NOT_REQD');
541 FND_MSG_PUB.ADD;
542 RAISE FND_API.G_EXC_ERROR;
543 END IF;
544
545 IF ((x_spec_tests.exp_error_type IN ('N','P')) OR (x_spec_tests.exp_error_type IS NULL)) THEN
546 NULL ;
547 ELSE
548 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_EXP_ERROR_TYPE');
549 FND_MSG_PUB.ADD;
550 RAISE FND_API.G_EXC_ERROR;
551 END IF;
552
553 IF x_spec_tests.exp_error_type IS NULL AND
554 (x_spec_tests.below_spec_min IS NOT NULL OR x_spec_tests.above_spec_min IS NOT NULL
555 OR x_spec_tests.below_spec_max IS NOT NULL OR x_spec_tests.above_spec_max IS NOT NULL)
556 THEN
557 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERROR_TYPE_REQ');
558 FND_MSG_PUB.ADD;
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561
562 IF x_spec_tests.exp_error_type IS NOT NULL AND
563 (x_spec_tests.below_spec_min IS NULL AND x_spec_tests.above_spec_min IS NULL
564 AND x_spec_tests.below_spec_max IS NULL AND x_spec_tests.above_spec_max IS NULL)
565 THEN
566 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXP_ERR_TYPE_NULL');
567 FND_MSG_PUB.ADD;
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570
571 END IF;
572
573 -- test UOM and Quantity.
574 IF (l_test_type = 'E') THEN
575 IF (x_spec_tests.test_qty_uom IS NOT NULL OR x_spec_tests.test_qty IS NOT NULL) THEN
576 GMD_API_PUB.Log_Message('GMD_TEST_UOM_QTY_NOT_REQD');
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579 ELSE
580 IF x_spec_tests.test_qty <= 0 THEN
581 GMD_API_PUB.Log_Message('GMD_TEST_QTY_NEG');
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584
585 IF (x_spec_tests.test_qty_uom IS NOT NULL AND x_spec_tests.test_qty IS NULL) OR
586 (x_spec_tests.test_qty_uom IS NULL AND x_spec_tests.test_qty IS NOT NULL) THEN
587 GMD_API_PUB.Log_Message('GMD_TEST_UOM_QTY_REQD');
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590 END IF;
591
592 IF x_spec_tests.test_priority IS NULL THEN
593 x_spec_tests.test_priority := l_test_priority;
594
595 ELSIF (NOT GMD_QC_TESTS_GRP.validate_test_priority(p_test_priority => x_spec_tests.test_priority)) THEN
596 GMD_API_PUB.Log_Message('GMD_INVALID_TEST_PRIORITY');
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600 -- Replicate Validation
601 IF x_spec_tests.test_replicate IS NULL THEN
602 GMD_API_PUB.Log_Message('GMD_TEST_REP_REQD');
603 RAISE FND_API.G_EXC_ERROR;
604 ELSIF (l_test_type = 'E' and x_spec_tests.test_replicate <> 1) THEN
605 GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ONE');
606 RAISE FND_API.G_EXC_ERROR;
607 ELSIF (x_spec_tests.test_replicate < l_test_method_replicate) THEN
608 GMD_API_PUB.Log_Message('SPEC_TEST_REPLICATE_ERROR',
609 'SPEC_TEST', l_test_code);
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613
614 -- Bug 3437091
615 -- Check on CALC_UOM_CONV_IND
616 IF (x_spec_tests.CALC_UOM_CONV_IND IS NULL) or
617 (x_spec_tests.CALC_UOM_CONV_IND = 'Y') then
618 null;
619 else
620 GMD_API_PUB.Log_Message('GMD_UOM_CONV_IND');
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623
624
625 -- action code foreign key validation.
626 IF x_spec_tests.BELOW_MIN_ACTION_CODE IS NOT NULL THEN
627 OPEN cr_action_code(x_spec_tests.below_min_action_code);
628 FETCH cr_action_code INTO l_temp;
629 IF cr_action_code%NOTFOUND THEN
630 CLOSE cr_action_code;
631 GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.below_min_action_code);
632 RAISE FND_API.G_EXC_ERROR;
633 END IF;
634 CLOSE cr_action_code ;
635 END IF;
636
637 IF x_spec_tests.ABOVE_MIN_ACTION_CODE IS NOT NULL THEN
638 OPEN cr_action_code(x_spec_tests.above_min_action_code);
639 FETCH cr_action_code INTO l_temp;
640 IF cr_action_code%NOTFOUND THEN
641 CLOSE cr_action_code;
642 GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.above_min_action_code);
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645 CLOSE cr_action_code ;
646 END IF;
647
648 IF x_spec_tests.BELOW_MAX_ACTION_CODE IS NOT NULL THEN
649 OPEN cr_action_code(x_spec_tests.below_max_action_code);
650 FETCH cr_action_code INTO l_temp;
651 IF cr_action_code%NOTFOUND THEN
652 CLOSE cr_action_code;
653 GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.below_max_action_code);
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656 CLOSE cr_action_code ;
657 END IF;
658
659 IF x_spec_tests.ABOVE_MAX_ACTION_CODE IS NOT NULL THEN
660 OPEN cr_action_code(x_spec_tests.above_max_action_code);
661 FETCH cr_action_code INTO l_temp;
662 IF cr_action_code%NOTFOUND THEN
663 CLOSE cr_action_code;
664 GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.above_max_action_code);
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667 CLOSE cr_action_code ;
668 END IF;
669
670 IF x_spec_tests.out_of_spec_action IS NOT NULL THEN
671 OPEN cr_action_code(x_spec_tests.out_of_spec_action);
672 FETCH cr_action_code INTO l_temp;
673 IF cr_action_code%NOTFOUND THEN
674 CLOSE cr_action_code;
675 GMD_API_PUB.Log_Message('GMD_INVALID_ACTION_CODE','ACTION',x_spec_tests.out_of_spec_action);
676 RAISE FND_API.G_EXC_ERROR;
677 END IF;
678 CLOSE cr_action_code ;
679 END IF;
680
681 IF x_spec_tests.use_to_control_step IS NULL OR x_spec_tests.use_to_control_step IN ('N','Y') THEN
682 NULL ;
683 ELSE
684 GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','USE_TO_CONTROL_STEP');
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687 IF x_spec_tests.use_to_control_step = 'N' THEN
688 x_spec_tests.use_to_control_step:= NULL;
689 END IF;
690
691 IF x_spec_tests.optional_ind IS NULL OR x_spec_tests.optional_ind IN ('N','Y') THEN
692 NULL ;
693 ELSE
694 GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','OPTIONAL_IND');
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697 IF x_spec_tests.optional_ind = 'N' THEN
698 x_spec_tests.optional_ind:= NULL;
699 END IF;
700
701 IF x_spec_tests.print_spec_ind IS NULL OR x_spec_tests.print_spec_ind IN ('N','Y') THEN
702 NULL ;
703 ELSE
704 GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','PRINT_SPEC_IND');
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707 IF x_spec_tests.print_spec_ind = 'N' THEN
708 x_spec_tests.print_spec_ind:= NULL;
709 END IF;
710
711 IF x_spec_tests.print_result_ind IS NULL OR x_spec_tests.print_result_ind IN ('N','Y') THEN
712 NULL ;
713 ELSE
714 GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','PRINT_RESULT_IND');
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717 IF x_spec_tests.print_result_ind = 'N' THEN
718 x_spec_tests.print_result_ind:= NULL;
719 END IF;
720
721 IF x_spec_tests.retest_lot_expiry_ind IS NULL OR x_spec_tests.retest_lot_expiry_ind IN ('N','Y') THEN
722 NULL ;
723 ELSE
724 GMD_API_PUB.Log_Message('GMD_SPEC_INVALID_IND','COLUMN','RETEST_LOT_EXPIRY_IND');
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727 IF x_spec_tests.retest_lot_expiry_ind = 'N' THEN
728 x_spec_tests.retest_lot_expiry_ind:= NULL;
729 END IF;
730
731
732 EXCEPTION
733 WHEN FND_API.G_EXC_ERROR THEN
734 x_return_status := FND_API.G_RET_STS_ERROR ;
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
737 WHEN OTHERS THEN
738 GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.check_for_null_and_fks_in_stst',
739 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
741
742 END check_for_null_and_fks_in_stst;
743
744 --Start of comments
745 --+========================================================================+
746 --| API Name : validate_spec_header |
747 --| TYPE : Group |
748 --| Notes : This procedure validates all the fields of |
749 --| specification header. This procedure can be |
750 --| called from FORM or API and the caller need |
751 --| to specify this in p_called_from parameter |
752 --| while calling this procedure. Based on where |
753 --| it is called from certain validations will |
754 --| either be performed or skipped. |
755 --| |
756 --| If everything is fine then OUT parameter |
757 --| x_return_status is set to 'S' else appropriate |
758 --| error message is put on the stack and error |
759 --| is returned. |
760 --| |
761 --| HISTORY |
762 --| Chetan Nagar 26-Jul-2002 Created. |
763 --| |
764 --| |
765 --| Saikiran Vankadari 07-Feb-2005 Changed as part of Convergence |
766 --| | |
767 --+========================================================================+
768 -- End of comments
769
770 PROCEDURE validate_spec_header
771 (
772 p_spec_header IN gmd_specifications%ROWTYPE
773 , p_called_from IN VARCHAR2
774 , p_operation IN VARCHAR2
775 , x_return_status OUT NOCOPY VARCHAR2
776 ) IS
777
778 -- Local Variables
779 l_item_number VARCHAR2(80);
780 l_owner VARCHAR2(30);
781 l_return_status VARCHAR2(1);
782 l_owner_organization_code VARCHAR2(3);
783
784 BEGIN
785 -- Initialize API return status to success
786 x_return_status := FND_API.G_RET_STS_SUCCESS;
787
788 IF (p_called_from = 'API') THEN
789 -- Check for NULLs and Valid Foreign Keys in the input parameter
790 GMD_Spec_GRP.check_for_null_and_fks_in_spec
791 (
792 p_spec_header => p_spec_header
793 , x_item_number => l_item_number
794 , x_owner => l_owner
795 , x_return_status => l_return_status
796 );
797 -- No need if called from FORM since it is already
798 -- done in the form
799
800 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
801 -- Message is alrady logged by check_for_null procedure
802 RAISE FND_API.G_EXC_ERROR;
803 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
804 -- Message is alrady logged by check_for_null procedure
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END IF;
807 END IF;
808
809 -- Verify that spec_name and spec_vers are unique
810 IF (p_operation = 'INSERT' AND spec_vers_exist(p_spec_header.spec_name, p_spec_header.spec_vers)) THEN
811 -- Ah...Ha, Spec and Version combination is already used
812 GMD_API_PUB.Log_Message('GMD_SPEC_VERS_EXIST',
813 'SPEC', p_spec_header.spec_name,
814 'VERS', p_spec_header.spec_vers);
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817
818 -- Verify that owner_id has access to owner_orgn_code
819 IF NOT spec_owner_orgn_valid(fnd_global.resp_id,
820 p_spec_header.owner_organization_id) THEN
821 -- Peep...Peep...Security Alert. User does not have access to Owner Organization
822 SELECT organization_code INTO l_owner_organization_code
823 FROM mtl_parameters
824 WHERE organization_id = p_spec_header.owner_organization_id;
825 GMD_API_PUB.Log_Message('GMD_USER_ORGN_NO_ACCESS',
826 'OWNER', l_owner,
827 'ORGN', l_owner_organization_code);
828 RAISE FND_API.G_EXC_ERROR;
829 END IF;
830
831 -- All systems GO...
832
833 EXCEPTION
834 WHEN FND_API.G_EXC_ERROR THEN
835 x_return_status := FND_API.G_RET_STS_ERROR ;
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
838 WHEN OTHERS THEN
839 GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_spec_header',
840 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
842
843
844 END validate_spec_header;
845
846
847 --Start of comments
848 --+========================================================================+
849 --| API Name : spec_vers_exist |
850 --| TYPE : Group |
851 --| Notes : This function returns TRUE if the Spec and Spec Version |
852 --| combination already exist in the database, FALSE |
853 --| otherwise. |
854 --| |
855 --| HISTORY |
856 --| Chetan Nagar 26-Jul-2002 Created. |
857 --| |
858 --+========================================================================+
859 -- End of comments
860
861 FUNCTION spec_vers_exist(p_spec_name VARCHAR2, p_spec_vers NUMBER)
862 RETURN BOOLEAN IS
863
864 CURSOR c_spec (p_spec_name VARCHAR2, p_spec_vers NUMBER) IS
865 SELECT 1
866 FROM gmd_specifications_b
867 WHERE spec_name = p_spec_name
868 AND spec_vers = p_spec_vers;
869
870 dummy PLS_INTEGER;
871
872 BEGIN
873
874 OPEN c_spec(p_spec_name, p_spec_vers);
875 FETCH c_spec INTO dummy;
876 IF c_spec%FOUND THEN
877 CLOSE c_spec;
878 RETURN TRUE;
879 ELSE
880 CLOSE c_spec;
881 RETURN FALSE;
882 END IF;
883
884 EXCEPTION
885 -- Though there is no reason the program can reach
886 -- here, this is coded just for the reasons we can
887 -- not think of!
888 WHEN OTHERS THEN
889 RETURN TRUE;
890
891 END spec_vers_exist;
892
893
894
895
896 --Start of comments
897 --+========================================================================+
898 --| API Name : spec_owner_orgn_valid |
899 --| TYPE : Group |
900 --| Notes : This function returns TRUE if the Owner has access |
901 --| to the Organization specified, FALSE otherwise. |
902 --| |
903 --| |
904 --| HISTORY |
905 --| Chetan Nagar 26-Jul-2002 Created. |
906 --| |
907 --| Saikiran Vankadari 07-Feb-2005 Changed as part of Convergence. |
908 --| Taking responsibility_id as input parameter instead of |
909 --| Owner id and also changed the validation logic |
910 --| |
911 --+========================================================================+
912 -- End of comments
913
914 FUNCTION spec_owner_orgn_valid(p_responsibility_id NUMBER,
915 p_owner_organization_id NUMBER)
916 RETURN BOOLEAN IS
917
918
919 CURSOR c_user_orgn (p_responsibility_id NUMBER,
920 p_owner_organization_id NUMBER) IS
921 SELECT 1
922 FROM org_access_view
923 WHERE responsibility_id = p_responsibility_id
924 AND organization_id = p_owner_organization_id;
925
926 dummy PLS_INTEGER;
927
928 BEGIN
929
930 OPEN c_user_orgn(p_responsibility_id, p_owner_organization_id);
931 FETCH c_user_orgn INTO dummy;
932 IF c_user_orgn%FOUND THEN
933 CLOSE c_user_orgn;
934 RETURN TRUE;
935 ELSE
936 CLOSE c_user_orgn;
937 RETURN FALSE;
938 END IF;
939
940 EXCEPTION
941 -- Though there is no reason the program can reach
942 -- here, this is coded just for the reasons we can
943 -- not think of!
944 WHEN OTHERS THEN
945 RETURN FALSE;
946
947 END spec_owner_orgn_valid;
948
949 -- KYH BUG 2904004 BEGIN
950 --Start of comments
951 --+========================================================================+
952 --| API Name : uom_class_combo_exist |
953 --| TYPE : Group |
954 --| Notes : This function returns TRUE if the |
955 --| to UOM class already exists on another |
956 --| test line belonging to the spec |
957 --| Otherwise returns FALSE |
958 --| |
959 --| HISTORY |
960 --| KYH 16-APR-200 KYH Created for BUG 2904004 |
961 --| |
962 --+========================================================================+
963 -- End of comments
964
965 FUNCTION uom_class_combo_exist(p_spec_id NUMBER, p_test_id NUMBER, p_to_uom VARCHAR2)
966 RETURN BOOLEAN IS
967
968 CURSOR c_class_combo (p_spec_name VARCHAR2, p_spec_vers NUMBER, p_to_uom VARCHAR2) IS
969 SELECT 1
970 FROM gmd_spec_tests_b st, mtl_units_of_measure um
971 WHERE st.spec_id = p_spec_id
972 AND st.test_id <> p_test_id
973 AND st.to_qty_uom = um.uom_code
974 AND um.uom_class =
975 (select uom_class from mtl_units_of_measure where uom_code = p_to_uom);
976
977 dummy PLS_INTEGER;
978
979 BEGIN
980
981 OPEN c_class_combo(p_spec_id, p_test_id, p_to_uom);
982 FETCH c_class_combo INTO dummy;
983 IF c_class_combo%FOUND THEN
984 CLOSE c_class_combo;
985 RETURN TRUE;
986 ELSE
987 CLOSE c_class_combo;
988 RETURN FALSE;
989 END IF;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 RETURN TRUE;
994
995 END uom_class_combo_exist;
996 -- KYH BUG 2904004 END
997
998 --Start of comments
999 --+========================================================================+
1000 --| API Name : validate_spec_test |
1001 --| TYPE : Group |
1002 --| Notes : This procedure validates all the fields of |
1003 --| Specification Test. This procedure can be |
1004 --| called from FORM or API and the caller need |
1005 --| to specify this in p_called_from parameter |
1006 --| while calling this procedure. Based on where |
1007 --| it is called from certain validations will |
1008 --| either be performed or skipped. |
1009 --| |
1010 --| If everything is fine then OUT parameter |
1011 --| x_return_status is set to 'S' else appropriate |
1012 --| error message is put on the stack and error |
1013 --| is returned. |
1014 --| |
1015 --| HISTORY |
1016 --| Chetan Nagar 26-Jul-2002 Created. |
1017 --| |
1018 --+========================================================================+
1019 -- End of comments
1020 PROCEDURE validate_spec_test
1021 (
1022 p_spec_test IN gmd_spec_tests%ROWTYPE
1023 , p_called_from IN VARCHAR2
1024 , p_operation IN VARCHAR2
1025 , x_spec_test OUT NOCOPY gmd_spec_tests%ROWTYPE
1026 , x_return_status OUT NOCOPY VARCHAR2
1027 ) IS
1028
1029 CURSOR c_spec (p_spec_name VARCHAR2, p_spec_vers NUMBER) IS
1030 SELECT 1
1031 FROM gmd_specifications_b
1032 WHERE spec_name = p_spec_name
1033 AND spec_vers = p_spec_vers;
1034
1035 CURSOR c_test_value (p_test_id NUMBER, p_value_char VARCHAR2) IS
1036 SELECT text_range_seq
1037 FROM gmd_qc_test_values_b
1038 WHERE test_id = p_test_id
1039 AND value_char = p_value_char ;
1040
1041 CURSOR c_spec_type (p_spec_id NUMBER) IS
1042 SELECT spec_type
1043 FROM gmd_specifications_b
1044 WHERE spec_id = p_spec_id ;
1045
1046 -- Local Variables
1047 l_dummy NUMBER;
1048 l_item_number VARCHAR2(80);
1049 l_owner VARCHAR2(30);
1050 l_return_status VARCHAR2(1);
1051
1052 l_st_min NUMBER;
1053 l_st_target NUMBER;
1054 l_st_max NUMBER;
1055
1056
1057 l_specification GMD_SPECIFICATIONS%ROWTYPE;
1058 l_specification_out GMD_SPECIFICATIONS%ROWTYPE;
1059 l_test GMD_QC_TESTS%ROWTYPE;
1060 l_test_out GMD_QC_TESTS%ROWTYPE;
1061 l_item MTL_SYSTEM_ITEMS_KFV%ROWTYPE;
1062 -- Bug 3401368
1063 x_viability_time NUMBER;
1064 x_viability_status varchar2(100);
1065
1066 -- Exceptions
1067 e_spec_fetch_error EXCEPTION;
1068 e_test_fetch_error EXCEPTION;
1069 e_test_method_fetch_error EXCEPTION;
1070 error_fetch_item EXCEPTION;
1071 x_spec_type varchar2(10);
1072
1073 BEGIN
1074 -- Initialize API return status to success
1075 x_return_status := FND_API.G_RET_STS_SUCCESS;
1076
1077 -- Fetch Specification Record. Spec must exists for Spec Test.
1078 l_specification.spec_id := p_spec_test.spec_id;
1079 -- Introduce l_specification_out as part of NOCOPY changes.
1080 IF NOT ( GMD_Specifications_PVT.Fetch_Row(
1081 p_specifications => l_specification,
1082 x_specifications => l_specification_out)
1083 ) THEN
1084 -- Fetch Error
1085 RAISE e_spec_fetch_error;
1086 END IF;
1087 l_specification := l_specification_out ;
1088
1089 IF (p_called_from = 'API') THEN
1090 -- Check for NULLs and Valid Foreign Keys in the input parameter
1091 -- No need if called from FORM since it is already
1092 -- done in the form
1093
1094 GMD_Spec_GRP.check_for_null_and_fks_in_stst
1095 (
1096 p_spec_tests => p_spec_test
1097 , x_spec_tests => x_spec_test
1098 , x_return_status => l_return_status
1099 );
1100
1101 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1102 -- Message is alrady logged by check_for_null procedure
1103 RAISE FND_API.G_EXC_ERROR;
1104 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1105 -- Message is alrady logged by check_for_null procedure
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107 END IF;
1108 END IF;
1109
1110
1111 -- Fetch Test Record.
1112 l_test.test_id := x_spec_test.test_id;
1113 IF NOT ( GMD_QC_TESTS_PVT.Fetch_Row(
1114 p_gmd_qc_tests => l_test,
1115 x_gmd_qc_tests => l_test_out)
1116 ) THEN
1117 -- Fetch Error
1118 RAISE e_test_fetch_error;
1119 END IF;
1120
1121 l_test := l_test_out ;
1122
1123
1124 -- Verify that Seq is unique
1125 IF (spec_test_seq_exist(x_spec_test.spec_id,x_spec_test.seq) )
1126 THEN
1127 -- Seq is already used
1128 GMD_API_PUB.Log_Message('GMD_SPEC_TEST_SEQ_EXIST', 'SEQ', x_spec_test.seq);
1129 RAISE FND_API.G_EXC_ERROR;
1130 end if ;
1131
1132
1133 -- Verify that Test is unique (added by KYH 01/OCT/02)
1134 IF spec_test_exist(x_spec_test.spec_id,x_spec_test.test_id) THEN
1135 -- Test is already used
1136 GMD_API_PUB.Log_Message('GMD_SPEC_TEST_EXIST', 'TEST_ID', x_spec_test.test_id);
1137 RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139
1140 open c_spec_type(x_spec_test.spec_id);
1141 fetch c_spec_type into x_spec_type ;
1142 close c_spec_type ;
1143
1144 -- Test UOM must be convertible to Item's UOM
1145 IF (x_spec_test.test_qty_uom IS NOT NULL) and
1146 (x_spec_type = 'I') THEN
1147 BEGIN
1148
1149 -- bug 4924529 sql id 14686748
1150 -- fields needed from mtl_system_items_kfv
1151 -- are l_item.primary_uom_code, l_item.lot_control_code and l_item.concatenated_segments.
1152 -- 155714 memory reduced
1153 -- cost is 3
1154
1155 -- SELECT * INTO l_item
1156 -- FROM mtl_system_items_kfv
1157 -- WHERE organization_id = l_specification.owner_organization_id
1158 -- AND inventory_item_id = l_specification.inventory_item_id;
1159
1160 SELECT primary_uom_code,
1161 lot_control_code,
1162 concatenated_segments
1163 INTO l_item.primary_uom_code,
1164 l_item.lot_control_code,
1165 l_item.concatenated_segments
1166 FROM mtl_system_items_kfv
1167 WHERE organization_id = l_specification.owner_organization_id
1168 AND inventory_item_id = l_specification.inventory_item_id;
1169 EXCEPTION WHEN OTHERS
1170 THEN
1171 RAISE error_fetch_item;
1172 END;
1173
1174 -- GMD_API_PUB.Log_Message('GMD_SPEC_TEST_EXIST', 'TEST_ID', x_spec_test.test_id);
1175 --RAISE FND_API.G_EXC_ERROR;
1176
1177
1178 BEGIN
1179 /*GMICUOM.icuomcv(pitem_id => l_item_mst.item_id,
1180 plot_id => 0,
1181 pcur_qty => 1,
1182 pcur_uom => x_spec_test.test_uom,
1183 pnew_uom => l_item_mst.item_um,
1184 onew_qty => dummy);*/
1185 --As part of Convergence, call to GMICUOM.icuomcv() is replaced with call to inv_convert.inv_um_conversion()
1186 inv_convert.inv_um_conversion (
1187 from_unit => x_spec_test.test_qty_uom,
1188 to_unit => l_item.primary_uom_code,
1189 item_id => l_specification.inventory_item_id,
1190 lot_number => NULL,
1191 organization_id => l_specification.owner_organization_id,
1192 uom_rate => l_dummy );
1193
1194 EXCEPTION WHEN OTHERS
1195 THEN
1196 FND_MSG_PUB.ADD;
1197 RAISE FND_API.G_EXC_ERROR;
1198 END ;
1199 END IF;
1200
1201
1202 -- Target, Min and Max validation
1203 IF (l_test.test_type NOT IN ('U')) THEN
1204
1205 -- Validate min,target and max for character based tests.
1206
1207 IF x_spec_test.min_value_char IS NOT NULL THEN
1208 OPEN c_test_value(l_test.test_id, x_spec_test.min_value_char);
1209 FETCH c_test_value INTO x_spec_test.min_value_num;
1210 IF c_test_value%NOTFOUND THEN
1211 CLOSE c_test_value;
1212 GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1213 RAISE FND_API.G_EXC_ERROR;
1214 END IF;
1215 CLOSE c_test_value;
1216 END IF;
1217
1218 IF x_spec_test.target_value_char IS NOT NULL THEN
1219
1220 OPEN c_test_value(l_test.test_id, x_spec_test.target_value_char);
1221 FETCH c_test_value INTO x_spec_test.target_value_num;
1222 IF c_test_value%NOTFOUND THEN
1223 CLOSE c_test_value;
1224 GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1225 RAISE FND_API.G_EXC_ERROR;
1226 END IF;
1227 CLOSE c_test_value;
1228 END IF;
1229
1230 IF x_spec_test.max_value_char IS NOT NULL THEN
1231 OPEN c_test_value(l_test.test_id, x_spec_test.max_value_char);
1232 FETCH c_test_value INTO x_spec_test.max_value_num;
1233 IF c_test_value%NOTFOUND THEN
1234 CLOSE c_test_value;
1235 GMD_API_PUB.Log_Message('TEST_VALUES_NOT_FOUND');
1236 RAISE FND_API.G_EXC_ERROR;
1237 END IF;
1238 CLOSE c_test_value;
1239 END IF;
1240
1241
1242 IF (l_test.test_type NOT IN ('V')) THEN
1243
1244 IF l_test.test_type IN ('L','E','N') THEN
1245
1246 x_spec_test.min_value_num := ROUND(x_spec_test.min_value_num,x_spec_test.display_precision);
1247 x_spec_test.max_value_num := ROUND(x_spec_test.max_value_num,x_spec_test.display_precision);
1248 x_spec_test.target_value_num := ROUND(x_spec_test.target_value_num,x_spec_test.display_precision);
1249 END IF;
1250
1251 l_st_min := x_spec_test.min_value_num;
1252 l_st_target := x_spec_test.target_value_num;
1253 l_st_max := x_spec_test.max_value_num;
1254
1255 -- Now we all the min, max,and target values in NUMERIC format.
1256 IF NOT (spec_test_min_target_max_valid
1257 (p_validation_level => 'FULL'
1258 ,p_test_id => l_test.test_id
1259 ,p_test_type => l_test.test_type
1260 ,p_st_min => l_st_min
1261 ,p_st_target => l_st_target
1262 ,p_st_max => l_st_max
1263 ,p_t_min => l_test.min_value_num
1264 ,p_t_max => l_test.max_value_num)
1265 ) THEN
1266 RAISE FND_API.G_EXC_ERROR ;
1267 END IF;
1268 END IF; -- l_test.test_type NOT IN ('V')
1269
1270 END IF; -- l_test.test_type NOT IN ('U')
1271
1272
1273 -- Lot Retest Indicator
1274 IF ( x_spec_test.retest_lot_expiry_ind = 'Y' and l_item.lot_control_code = 1) THEN
1275 GMD_API_PUB.Log_Message('SPEC_TEST_RETEST_IND_ERROR',
1276 'SPEC_TEST', l_test.test_code,
1277 'SPEC_TEST', l_item.concatenated_segments);
1278 RAISE FND_API.G_EXC_ERROR;
1279 END IF;
1280
1281 -- Experimental Error Min and Max validation
1282 IF (l_test.test_type IN ('N', 'L', 'E') AND x_spec_test.exp_error_type IS NOT NULL ) THEN
1283 IF x_spec_test.exp_error_type = 'N' THEN
1284 x_spec_test.below_spec_min := ROUND(x_spec_test.below_spec_min,x_spec_test.display_precision);
1285 x_spec_test.above_spec_min := ROUND(x_spec_test.above_spec_min,x_spec_test.display_precision);
1286 x_spec_test.below_spec_max := ROUND(x_spec_test.below_spec_max,x_spec_test.display_precision);
1287 x_spec_test.above_spec_max := ROUND(x_spec_test.above_spec_max,x_spec_test.display_precision);
1288 END IF;
1289 IF NOT spec_test_exp_error_region_val
1290 (p_validation_level => 'FULL',
1291 p_exp_error_type => x_spec_test.exp_error_type,
1292 p_test_min => l_test.min_value_num,
1293 p_below_spec_min => x_spec_test.below_spec_min,
1294 p_spec_test_min => x_spec_test.min_value_num,
1295 p_above_spec_min => x_spec_test.above_spec_min,
1296 p_spec_test_target => x_spec_test.target_value_num,
1297 p_below_spec_max => p_spec_test.below_spec_max,
1298 p_spec_test_max => x_spec_test.max_value_num,
1299 p_above_spec_max => x_spec_test.above_spec_max,
1300 p_test_max => l_test.max_value_num) THEN
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303
1304 IF x_spec_test.below_min_action_code IS NOT NULL and x_spec_test.below_spec_min IS NULL THEN
1305 GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1306 RAISE FND_API.G_EXC_ERROR;
1307 END IF;
1308
1309 IF x_spec_test.above_min_action_code IS NOT NULL and x_spec_test.above_spec_min IS NULL THEN
1310 GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1311 RAISE FND_API.G_EXC_ERROR;
1312 END IF;
1313
1314 IF x_spec_test.below_max_action_code IS NOT NULL and x_spec_test.below_spec_max IS NULL THEN
1315 GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1316 RAISE FND_API.G_EXC_ERROR;
1317 END IF;
1318
1319 IF x_spec_test.above_max_action_code IS NOT NULL and x_spec_test.above_spec_max IS NULL THEN
1320 GMD_API_PUB.Log_Message('GMD_EXP_ERR_VAL_REQ_ACTION');
1321 RAISE FND_API.G_EXC_ERROR;
1322 END IF;
1323
1324 END IF;
1325
1326 IF NOT spec_test_precisions_valid(
1327 p_spec_display_precision => x_spec_test.display_precision,
1328 p_spec_report_precision => x_spec_test.report_precision,
1329 p_test_display_precision => l_test.display_precision,
1330 p_test_report_precision => l_test.display_precision ) THEN
1331 -- Messages are already logged.
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334
1335 --Update the Viability Period ( Bug 3401368)
1336 if (x_spec_test.days is not null) OR
1337 (x_spec_test.hours is not null) OR
1338 (x_spec_test.minutes is not null) OR
1339 (x_spec_test.seconds is not null) THEN
1340
1341 GMD_TEST_METHODS_GRP.GET_TEST_DURATION(
1342 P_DAYS => x_spec_test.DAYS,
1343 P_HOURS => x_spec_test.HOURS,
1344 P_MINS => x_spec_test.MINUTES,
1345 P_SECS => x_spec_test.SECONDS,
1346 X_DURATION_SECS => x_viability_time,
1347 X_RETURN_STATUS => x_viability_status );
1348
1349 if (x_viability_status = 'S') then
1350 x_spec_test.VIABILITY_DURATION := x_viability_time;
1351 end if ;
1352
1353 end if ;
1354
1355
1356 -- All systems GO...
1357
1358 EXCEPTION
1359 WHEN FND_API.G_EXC_ERROR THEN
1360 x_return_status := FND_API.G_RET_STS_ERROR ;
1361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1363 WHEN OTHERS THEN
1364 GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_spec_test',
1365 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1367
1368
1369 END validate_spec_test;
1370
1371 /*===========================================================================
1372
1373 PROCEDURE NAME: validate_after_insert_all
1374 DESCRIPTION: This procedure validates that atleast one test
1375 should be attached to the spec.
1376 It
1377
1378 ===========================================================================*/
1379
1380 PROCEDURE validate_after_insert_all(
1381 p_spec_id IN NUMBER,
1382 x_return_status OUT NOCOPY VARCHAR2) IS
1383
1384 CURSOR cr_expression_tests IS
1385 SELECT a.test_id,a.seq
1386 FROM GMD_SPEC_TESTS_B a , GMD_QC_TESTS_B b
1387 WHERE
1388 a.spec_id = p_spec_id
1389 AND a.test_id = b.test_id
1390 AND b.test_type = 'E' ;
1391
1392 l_test_count BINARY_INTEGER;
1393 l_test_id NUMBER;
1394 l_test_seq BINARY_INTEGER;
1395
1396
1397 BEGIN
1398 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1399
1400 IF p_spec_id IS NULL THEN
1401 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
1402 RAISE FND_API.G_EXC_ERROR;
1403 END IF;
1404
1405 -- atleast one test should be present in the spec.
1406 SELECT NVL(COUNT(1),0) INTO l_test_count
1407 FROM GMD_SPEC_TESTS_B
1408 WHERE spec_id = p_spec_id ;
1409
1410 IF l_test_count = 0 THEN
1411 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_NO_TEST');
1412 FND_MSG_PUB.ADD;
1413 RAISE FND_API.G_EXC_ERROR;
1414 END IF;
1415
1416 -- validate expression based tests.
1417 -- all the reference tests must be present.
1418
1419 OPEN cr_expression_tests;
1420 LOOP
1421 FETCH cr_expression_tests INTO l_test_id,l_test_seq;
1422 IF cr_expression_tests%NOTFOUND THEN
1423 EXIT;
1424 END IF;
1425 IF NOT GMD_SPEC_GRP.spec_reference_tests_exist(
1426 p_spec_id => p_spec_id,
1427 p_exp_test_seq => l_test_seq,
1428 p_exp_test_id => l_test_id ) THEN
1429 CLOSE cr_expression_tests ;
1430 GMD_API_PUB.Log_Message('GMD_SOME_REF_TESTS_MISSING');
1431 RAISE FND_API.G_EXC_ERROR;
1432 END IF;
1433 END LOOP;
1434
1435 EXCEPTION
1436 WHEN FND_API.G_EXC_ERROR THEN
1437 x_return_status := FND_API.G_RET_STS_ERROR ;
1438
1439 WHEN OTHERS THEN
1440 GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','gmd_spec_grp.validate_after_insert_all',
1441 'ERROR',substr(sqlerrm,1,100),'POSITION','010');
1442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1443
1444 END validate_after_insert_all;
1445
1446 /*===========================================================================
1447 PROCEDURE NAME: validate_before_delete
1448
1449 DESCRIPTION: This procedure validates GMD_SPECIFICATIONS:
1450 a) Primary key supplied
1451 b) Spec is not already delete_marked
1452 c) Status permits update
1453
1454 PARAMETERS:
1455
1456 CHANGE HISTORY: Created 09-JUL-02 KYH
1457 ===========================================================================*/
1458
1459 PROCEDURE VALIDATE_BEFORE_DELETE(
1460 p_spec_id IN NUMBER,
1461 x_return_status OUT NOCOPY VARCHAR2,
1462 x_message_data OUT NOCOPY VARCHAR2) IS
1463
1464 l_progress VARCHAR2(3);
1465 l_temp VARCHAR2(1);
1466 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1467 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
1468
1469 BEGIN
1470 l_progress := '010';
1471 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1472
1473 -- validate for primary key
1474 -- ========================
1475 IF p_spec_id IS NULL THEN
1476 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_ID_REQUIRED'); -- New Message
1477 FND_MSG_PUB.ADD;
1478 RAISE FND_API.G_EXC_ERROR;
1479 ELSE
1480 l_spec.spec_id := p_spec_id;
1481 END IF;
1482
1483 -- Fetch the row
1484 -- =============
1485 IF NOT GMD_Specifications_PVT.Fetch_Row(l_spec,l_spec_out)
1486 THEN
1487 fnd_message.set_name('GMD','GMD_FAILED_TO_FETCH_ROW');
1488 fnd_message.set_token('L_TABLE_NAME','GMD_SPECIFICATIONS');
1489 fnd_message.set_token('L_COLUMN_NAME','SPEC_ID');
1490 fnd_message.set_token('L_KEY_VALUE',l_spec.spec_id);
1491 fnd_msg_pub.ADD;
1492 RAISE FND_API.G_EXC_ERROR;
1493 END IF;
1494
1495 l_spec := l_spec_out ;
1496
1497 -- Terminate if the row is already delete marked
1498 -- =============================================
1499 IF l_spec.delete_mark <> 0
1500 THEN
1501 fnd_message.set_name('GMD','GMD_RECORD_DELETE_MARKED');
1502 fnd_message.set_token('L_TABLE_NAME','GMD_SPECIFICATIONS');
1503 fnd_message.set_token('L_COLUMN_NAME','SPEC_ID');
1504 fnd_message.set_token('L_KEY_VALUE',l_spec.spec_id);
1505 fnd_msg_pub.ADD;
1506 RAISE FND_API.G_EXC_ERROR;
1507 END IF;
1508
1509 -- BUG 2698311
1510 -- Block deletes if the status is 400 (Approved for Lab Use) or
1511 -- ============================== 700 (Approved for General Use)
1512 -- ============================================================
1513 IF l_spec.spec_status in (400,700)
1514 THEN
1515 fnd_message.set_name('GMD','GMD_SPEC_STATUS_BLOCKS_DELETE');
1516 fnd_msg_pub.ADD;
1517 RAISE FND_API.G_EXC_ERROR;
1518 END IF;
1519
1520 -- Ensure that the status permits updates
1521 -- ======================================
1522 IF NOT GMD_SPEC_GRP.Record_Updateable_With_Status(l_spec.spec_status)
1523 THEN
1524 fnd_message.set_name('GMD','GMD_SPEC_STATUS_BLOCKS_UPDATE');
1525 fnd_msg_pub.ADD;
1526 RAISE FND_API.G_EXC_ERROR;
1527 END IF;
1528
1529 EXCEPTION
1530 WHEN FND_API.G_EXC_ERROR THEN
1531 x_return_status := FND_API.G_RET_STS_ERROR ;
1532 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1533
1534 WHEN OTHERS THEN
1535 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1536 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
1537 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1538 FND_MESSAGE.Set_Token('POSITION',l_progress );
1539 FND_MSG_PUB.ADD;
1540 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1542
1543 END VALIDATE_BEFORE_DELETE ;
1544
1545 /*===========================================================================
1546 PROCEDURE NAME: validate_before_delete
1547
1548 DESCRIPTION: This procedure validates GMD_SPEC_TEST:
1549 a) Primary key supplied
1550 b) Spec is not already delete_marked
1551
1552 PARAMETERS:
1553
1554 CHANGE HISTORY: Created 09-JUL-02 KYH
1555 ===========================================================================*/
1556
1557 PROCEDURE VALIDATE_BEFORE_DELETE(
1558 p_spec_id IN NUMBER,
1559 p_test_id IN NUMBER,
1560 x_return_status OUT NOCOPY VARCHAR2,
1561 x_message_data OUT NOCOPY VARCHAR2) IS
1562
1563 l_progress VARCHAR2(3);
1564 l_temp VARCHAR2(1);
1565 l_spec_tests GMD_SPEC_TESTS%ROWTYPE;
1566 l_spec_tests_out GMD_SPEC_TESTS%ROWTYPE;
1567 l_spec_delete_mark BINARY_INTEGER;
1568
1569 BEGIN
1570 l_progress := '010';
1571 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1572
1573 -- validate for primary key
1574 -- ========================
1575 IF p_spec_id IS NULL THEN
1576 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_ID_REQUIRED');
1577 FND_MSG_PUB.ADD;
1578 RAISE FND_API.G_EXC_ERROR;
1579 ELSE
1580 l_spec_tests.spec_id := p_spec_id;
1581 END IF;
1582
1583 IF p_test_id IS NULL THEN
1584 FND_MESSAGE.SET_NAME('GMD','GMD_TEST_ID_CODE_NULL');
1585 FND_MSG_PUB.ADD;
1586 RAISE FND_API.G_EXC_ERROR;
1587 ELSE
1588 l_spec_tests.test_id := p_test_id;
1589 END IF;
1590
1591 -- Fetch the row
1592 -- =============
1593 IF NOT GMD_Spec_Tests_PVT.Fetch_Row(l_spec_tests,l_spec_tests_out)
1594 THEN
1595 fnd_message.set_name('GMD','GMD_FAILED_TO_FETCH_ROW');
1596 fnd_message.set_token('L_TABLE_NAME','GMD_SPEC_TESTS');
1597 fnd_message.set_token('L_COLUMN_NAME','TEST_ID');
1598 fnd_message.set_token('L_KEY_VALUE',l_spec_tests.test_id);
1599 fnd_msg_pub.ADD;
1600 RAISE FND_API.G_EXC_ERROR;
1601 END IF;
1602
1603 l_spec_tests := l_spec_tests_out ;
1604
1605 SELECT delete_mark into l_spec_delete_mark
1606 FROM GMD_SPECIFICATIONS_B
1607 WHERE spec_id = p_spec_id ;
1608
1609 IF l_spec_delete_mark <> 0
1610 THEN
1611 fnd_message.set_name('GMD','GMD_RECORD_DELETE_MARKED');
1612 fnd_message.set_token('L_TABLE_NAME','GMD_SPECIFICATIONS');
1613 fnd_message.set_token('L_COLUMN_NAME','SPEC_ID');
1614 fnd_message.set_token('L_KEY_VALUE',p_spec_id);
1615 fnd_msg_pub.ADD;
1616 RAISE FND_API.G_EXC_ERROR;
1617 END IF;
1618
1619 EXCEPTION
1620 WHEN FND_API.G_EXC_ERROR THEN
1621 x_return_status := FND_API.G_RET_STS_ERROR ;
1622 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1623
1624 WHEN OTHERS THEN
1625 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
1626 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
1627 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
1628 FND_MESSAGE.Set_Token('POSITION',l_progress );
1629 FND_MSG_PUB.ADD;
1630 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632
1633 END VALIDATE_BEFORE_DELETE ;
1634
1635 PROCEDURE validate_after_delete_test(
1636 p_spec_id IN NUMBER,
1637 x_return_status OUT NOCOPY VARCHAR2) IS
1638
1639 BEGIN
1640 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1641
1642 validate_after_insert_all(
1643 p_spec_id => p_spec_id,
1644 x_return_status => x_return_status) ;
1645
1646 END validate_after_delete_test;
1647
1648 --Start of comments
1649 --+========================================================================+
1650 --| API Name : spec_test_seq_exist |
1651 --| TYPE : Group |
1652 --| Notes : This function returns TRUE if the Spec Test Seq |
1653 --| already exist in the database, FALSE |
1654 --| otherwise. |
1655 --| |
1656 --| HISTORY |
1657 --| Chetan Nagar 26-Jul-2002 Created. |
1658 --| |
1659 --+========================================================================+
1660 -- End of comments
1661
1662 FUNCTION spec_test_seq_exist(p_spec_id IN NUMBER ,
1663 p_seq IN NUMBER ,
1664 p_exclude_test_id IN NUMBER )
1665 RETURN BOOLEAN IS
1666
1667 dummy PLS_INTEGER;
1668
1669 BEGIN
1670
1671 IF p_exclude_test_id IS NULL THEN
1672 SELECT 1 INTO dummy
1673 FROM GMD_SPEC_TESTS_B
1674 WHERE spec_id = p_spec_id
1675 AND seq = p_seq ;
1676 ELSE
1677 SELECT 1 INTO dummy
1678 FROM GMD_SPEC_TESTS_B
1679 WHERE spec_id = p_spec_id
1680 AND seq = p_seq
1681 AND test_id <> p_exclude_test_id ;
1682 END IF;
1683 RETURN TRUE;
1684
1685 EXCEPTION
1686 WHEN NO_DATA_FOUND THEN
1687 RETURN FALSE;
1688
1689 -- Though there is no reason the program can reach
1690 -- here, this is coded just for the reasons we can
1691 -- not think of!
1692 WHEN OTHERS THEN
1693 RETURN TRUE;
1694
1695 END spec_test_seq_exist;
1696
1697 --Start of comments
1698 --+========================================================================+
1699 --| API Name : spec_test_exist |
1700 --| TYPE : Group |
1701 --| Notes : This function returns TRUE if the test_id already |
1702 --| exists against the owning spec, otherwise it returns |
1703 --| FALSE. |
1704 --| |
1705 --| HISTORY |
1706 --| Karen Y. Hunt 01-OCT-2002 Created. |
1707 --| |
1708 --+========================================================================+
1709 -- End of comments
1710
1711 FUNCTION spec_test_exist(p_spec_id IN NUMBER ,
1712 p_test_id IN NUMBER )
1713 RETURN BOOLEAN IS
1714
1715 dummy PLS_INTEGER;
1716
1717 BEGIN
1718
1719 SELECT 1 INTO dummy
1720 FROM GMD_SPEC_TESTS_B
1721 WHERE spec_id = p_spec_id
1722 AND test_id = p_test_id;
1723
1724 RETURN TRUE;
1725
1726 EXCEPTION
1727 WHEN NO_DATA_FOUND THEN
1728 RETURN FALSE;
1729
1730 -- Though there is no reason the program can reach
1731 -- here, this is coded just for the reasons we can
1732 -- not think of!
1733 WHEN OTHERS THEN
1734 RETURN TRUE;
1735
1736 END spec_test_exist;
1737
1738
1739 --Start of comments
1740 --+========================================================================+
1741 --| API Name : spec_reference_tests_exist |
1742 --| TYPE : Group |
1743 --| Notes : This function returns TRUE if all the reference tests |
1744 --| which are part of the current expression are already |
1745 --| entered on the specification, FALSE otherwise. |
1746 --| |
1747 --| HISTORY |
1748 --| Chetan Nagar 26-Jul-2002 Created. |
1749 --| |
1750 --+========================================================================+
1751 -- End of comments
1752
1753 FUNCTION spec_reference_tests_exist(p_spec_id NUMBER, p_exp_test_seq NUMBER, p_exp_test_id NUMBER)
1754 RETURN BOOLEAN IS
1755
1756 CURSOR c_test_values (p_exp_test_id NUMBER) IS
1757 SELECT EXPRESSION_REF_TEST_ID
1758 FROM gmd_qc_test_values_b
1759 WHERE test_id = p_exp_test_id;
1760
1761 CURSOR c_spec_test (p_spec_id NUMBER, p_exp_test_seq NUMBER, p_ref_test_id NUMBER) IS
1762 SELECT 1
1763 FROM GMD_SPEC_TESTS_B
1764 WHERE spec_id = p_spec_id
1765 AND test_id = p_ref_test_id
1766 AND seq < p_exp_test_seq;
1767
1768 -- Local Variables
1769 dummy PLS_INTEGER;
1770
1771 -- Exceptions
1772 e_ref_test_missing EXCEPTION;
1773
1774 BEGIN
1775
1776 -- Get all the reference tests for the expression test
1777 FOR i in c_test_values(p_exp_test_id)
1778 LOOP
1779 -- See if the reference test is part of the spec
1780 -- with sequence lower then that of expression test.
1781 OPEN c_spec_test(p_spec_id, p_exp_test_seq, i.EXPRESSION_REF_TEST_ID);
1782 FETCH c_spec_test INTO dummy;
1783 IF c_spec_test%NOTFOUND THEN
1784 RAISE e_ref_test_missing;
1785 END IF;
1786 CLOSE c_spec_test;
1787 END LOOP;
1788
1789 RETURN TRUE;
1790
1791 EXCEPTION
1792 WHEN e_ref_test_missing THEN
1793 IF c_spec_test%ISOPEN THEN CLOSE c_spec_test; END IF;
1794 IF c_test_values%ISOPEN THEN CLOSE c_test_values; END IF;
1795 RETURN FALSE;
1796
1797 -- Though there is no reason the program can reach
1798 -- here, this is coded just for the reasons we can
1799 -- not think of!
1800 WHEN OTHERS THEN
1801 RETURN FALSE;
1802
1803 END spec_reference_tests_exist;
1804
1805 --Start of comments
1806 --+========================================================================+
1807 --| API Name : value_in_num_range_display |
1808 --| TYPE : Group |
1809 --| Notes : This function checks if the given value |
1810 --| is between the test range or not.If the value is between |
1811 --| the test range ,it returns TRUE else it returns FALSE |
1812 --| |
1813 --| HISTORY |
1814 --| Mahesh Chandak 09-Oct-2002 Created. |
1815 --| |
1816 --+========================================================================+
1817 -- End of comments
1818
1819 FUNCTION value_in_num_range_display(p_test_id IN NUMBER,
1820 p_value IN NUMBER,
1821 x_return_status OUT NOCOPY VARCHAR2 )
1822 RETURN BOOLEAN IS
1823
1824 CURSOR cr_test_values IS
1825 SELECT '1'
1826 FROM gmd_qc_test_values_b
1827 WHERE test_id = p_test_id
1828 AND p_value >= nvl(min_num,p_value)
1829 AND p_value <= nvl(max_num,p_value);
1830
1831 l_position VARCHAR2(3);
1832 l_temp VARCHAR2(1);
1833 REQ_FIELDS_MISSING EXCEPTION;
1834
1835 BEGIN
1836
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838 FND_MSG_PUB.initialize;
1839 l_position := '010';
1840
1841 IF p_test_id IS NULL OR p_value IS NULL THEN
1842 RAISE REQ_FIELDS_MISSING;
1843 END IF;
1844
1845 OPEN cr_test_values;
1846 FETCH cr_test_values INTO l_temp;
1847 IF cr_test_values%FOUND THEN
1848 CLOSE cr_test_values;
1849 RETURN TRUE;
1850 END IF;
1851 CLOSE cr_test_values;
1852 gmd_api_pub.log_message('GMD_VAL_MISSING_NUM_LABEL_TEST','VALUE',p_value);
1853 RETURN FALSE;
1854
1855 EXCEPTION
1856 WHEN REQ_FIELDS_MISSING THEN
1857 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.VALUE_IN_NUM_RANGE_DISPLAY');
1858 x_return_status := FND_API.G_RET_STS_ERROR ;
1859 RETURN FALSE;
1860 WHEN OTHERS THEN
1861 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);
1862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1863 RETURN FALSE;
1864 END value_in_num_range_display ;
1865
1866 --Start of comments
1867 --+========================================================================+
1868 --| API Name : spec_test_min_target_max_valid |
1869 --| TYPE : Group |
1870 --| Notes : This function returns TRUE if the Spec Test Min, Target, |
1871 --| and Max values are alphanumrecically in correct order, |
1872 --| FALSE otherwise. |
1873 --| |
1874 --| HISTORY |
1875 --| Chetan Nagar 26-Jul-2002 Created. |
1876 --+========================================================================+
1877 -- End of comments
1878
1879 FUNCTION spec_test_min_target_max_valid(p_test_id IN NUMBER,
1880 p_test_type IN VARCHAR2,
1881 p_validation_level IN VARCHAR2,
1882 p_st_min IN NUMBER,
1883 p_st_target IN NUMBER,
1884 p_st_max IN NUMBER,
1885 p_t_min IN NUMBER,
1886 p_t_max IN NUMBER)
1887 RETURN BOOLEAN IS
1888
1889 e_min_error EXCEPTION;
1890 e_max_error EXCEPTION;
1891 e_target_error EXCEPTION;
1892 l_position VARCHAR2(3);
1893 l_return_status VARCHAR2(1);
1894 e_num_range_label_hole EXCEPTION;
1895 REQ_FIELDS_MISSING EXCEPTION;
1896 l_val_missing NUMBER;
1897
1898 BEGIN
1899
1900 FND_MSG_PUB.initialize;
1901 l_position := '010';
1902
1903
1904 IF p_test_id IS NULL OR p_test_type IS NULL OR p_test_type IN ('U','V') THEN
1905 RAISE REQ_FIELDS_MISSING;
1906 END IF;
1907
1908 -- check spec min is >= target and <= spec max. Also spec min is between test min and test max.
1909 IF p_validation_level IN ('ST_MIN','FULL') THEN
1910 IF p_st_min IS NOT NULL THEN
1911 IF (p_st_min > p_st_target OR p_st_min > p_st_max OR p_st_min < p_t_min OR p_st_min > p_t_max) THEN
1912 RAISE e_min_error;
1913 END IF;
1914
1915 -- num range with display can have holes in the subranges
1916 -- check that the value does not fall into one of those holes
1917 IF p_test_type = 'L' THEN
1918 IF NOT value_in_num_range_display(p_test_id => p_test_id,
1919 p_value => p_st_min,
1920 x_return_status => l_return_status) THEN
1921 RETURN FALSE;
1922 END IF;
1923 END IF;
1924 END IF; -- IF p_st_min IS NOT NULL
1925 END IF;
1926
1927 l_position := '020';
1928
1929 IF p_validation_level IN ('ST_TARGET','FULL') THEN
1930 IF p_st_target IS NOT NULL THEN
1931 IF (p_st_min > p_st_target OR p_st_target > p_st_max OR p_st_target < p_t_min OR p_st_target > p_t_max) THEN
1932 RAISE e_target_error;
1933 END IF;
1934
1935 IF p_test_type = 'L' THEN
1936 IF NOT value_in_num_range_display(p_test_id => p_test_id,
1937 p_value => p_st_target,
1938 x_return_status => l_return_status) THEN
1939 RETURN FALSE;
1940 END IF;
1941 END IF;
1942 END IF; -- IF p_st_target IS NOT NULL THEN
1943 END IF;
1944
1945 l_position := '030';
1946
1947 IF p_validation_level IN ('ST_MAX','FULL') THEN
1948 IF p_st_max IS NOT NULL THEN
1949 IF (p_st_min > p_st_max OR p_st_target > p_st_max OR p_st_max < p_t_min OR p_st_max > p_t_max) THEN
1950 RAISE e_max_error;
1951 END IF;
1952 IF p_test_type = 'L' THEN
1953 IF NOT value_in_num_range_display(p_test_id => p_test_id,
1954 p_value => p_st_max,
1955 x_return_status => l_return_status) THEN
1956 RETURN FALSE;
1957 END IF;
1958 END IF;
1959 END IF; -- IF p_st_max IS NOT NULL THEN
1960 END IF;
1961
1962 RETURN TRUE;
1963
1964 EXCEPTION
1965 WHEN REQ_FIELDS_MISSING THEN
1966 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_GRP.SPEC_TEST_MIN_TARGET_MAX_VALID');
1967 RETURN FALSE;
1968 WHEN e_min_error THEN
1969 gmd_api_pub.log_message('GMD_SPEC_TEST_MIN_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
1970 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));
1971 RETURN FALSE;
1972 WHEN e_max_error THEN
1973 gmd_api_pub.log_message('GMD_SPEC_TEST_MAX_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
1974 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));
1975 RETURN FALSE;
1976 WHEN e_target_error THEN
1977 gmd_api_pub.log_message('GMD_SPEC_TEST_TARGET_ERROR','SPEC_TEST_MIN',to_char(p_st_min),'SPEC_TEST_MAX',
1978 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));
1979 RETURN FALSE;
1980 WHEN OTHERS THEN
1981 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);
1982 RETURN FALSE;
1983 END spec_test_min_target_max_valid;
1984
1985
1986 --Start of comments
1987 --+========================================================================+
1988 --| API Name : SPEC_TEST_EXP_ERROR_REGION_VAL |
1989 --| TYPE : Group |
1990 --| Notes : This function returns TRUE if the Spec Test experimental |
1991 --| errors values for Below Min, Above Min, Below Max, and |
1992 --| Above Max are alphanumrecically in correct order, |
1993 --| FALSE otherwise. |
1994 --| |
1995 --| |
1996 --| |
1997 --| HISTORY |
1998 --| Chetan Nagar 26-Jul-2002 Created. |
1999 --| |
2000 --+========================================================================+
2001 -- End of comments
2002
2003
2004 FUNCTION SPEC_TEST_EXP_ERROR_REGION_VAL( p_validation_level VARCHAR2,
2005 p_exp_error_type VARCHAR2,
2006 p_test_min NUMBER,
2007 p_below_spec_min NUMBER,
2008 p_spec_test_min NUMBER,
2009 p_above_spec_min NUMBER,
2010 p_spec_test_target NUMBER,
2011 p_below_spec_max NUMBER,
2012 p_spec_test_max NUMBER,
2013 p_above_spec_max NUMBER,
2014 p_test_max NUMBER)
2015 RETURN BOOLEAN IS
2016
2017 e_range_error EXCEPTION;
2018 l_spec_num NUMBER;
2019 l_max_value NUMBER;
2020 l_position NUMBER;
2021 BEGIN
2022 FND_MSG_PUB.initialize;
2023 l_position := '010';
2024
2025 IF p_exp_error_type IS NULL THEN
2026 RETURN TRUE;
2027 END IF;
2028
2029 IF p_exp_error_type NOT IN ( 'N','P') THEN
2030 GMD_API_PUB.Log_Message('GMD_INVALID_EXP_ERROR_TYPE');
2031 RETURN FALSE;
2032 END IF;
2033
2034 IF p_validation_level IN ('FULL','BELOW_SPEC_MIN') THEN
2035
2036 IF p_below_spec_min = 0 OR p_below_spec_min IS NULL THEN
2037 RETURN TRUE;
2038 END IF;
2039
2040 IF p_below_spec_min IS NOT NULL AND (p_spec_test_min = p_test_min OR p_test_max = p_test_min) THEN
2041 GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2042 RETURN FALSE;
2043 END IF;
2044
2045 IF (p_below_spec_min IS NOT NULL AND p_spec_test_min IS NOT NULL and p_test_min IS NOT NULL ) THEN
2046 IF p_exp_error_type = 'N' THEN
2047 l_spec_num := p_below_spec_min ;
2048 ELSE
2049 l_spec_num := ( p_below_spec_min * ( p_test_max - p_test_min )) /100 ;
2050 END IF;
2051
2052 IF ABS(l_spec_num) > ( p_spec_test_min - p_test_min) THEN
2053 IF p_exp_error_type = 'N' THEN
2054 l_max_value := ABS(p_spec_test_min - p_test_min);
2055 ELSE
2056 l_max_value := (ABS(p_spec_test_min - p_test_min) * 100)/(p_test_max - p_test_min);
2057 END IF;
2058 GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2059 RETURN FALSE;
2060 END IF;
2061 END IF;
2062 END IF;
2063
2064 l_position := '020';
2065
2066 IF p_validation_level IN ('FULL','ABOVE_SPEC_MAX') THEN
2067
2068 IF p_above_spec_max = 0 OR p_above_spec_max IS NULL THEN
2069 RETURN TRUE;
2070 END IF;
2071
2072 IF p_above_spec_max IS NOT NULL AND (p_spec_test_max = p_test_max OR p_test_max = p_test_min) THEN
2073 GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2074 RETURN FALSE;
2075 END IF;
2076
2077 IF (p_above_spec_max IS NOT NULL AND p_spec_test_max IS NOT NULL and p_test_max IS NOT NULL ) THEN
2078 IF p_exp_error_type = 'N' THEN
2079 l_spec_num := p_above_spec_max ;
2080 ELSE
2081 l_spec_num := ( p_above_spec_max * ( p_test_max - p_test_min )) /100 ;
2082 END IF;
2083
2084 IF ABS(l_spec_num) > ( p_test_max - p_spec_test_max) THEN
2085 IF p_exp_error_type = 'N' THEN
2086 l_max_value := ABS(p_test_max - p_spec_test_max);
2087 ELSE
2088 l_max_value := (ABS(p_test_max - p_spec_test_max) * 100)/(p_test_max - p_test_min);
2089 END IF;
2090 GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2091 RETURN FALSE;
2092 END IF;
2093 END IF;
2094 END IF;
2095
2096 l_position := '030';
2097
2098 IF p_validation_level IN ('FULL','ABOVE_SPEC_MIN') THEN
2099
2100 IF p_above_spec_min = 0 OR p_above_spec_min IS NULL THEN
2101 RETURN TRUE;
2102 END IF;
2103
2104 IF p_above_spec_min IS NOT NULL AND (p_spec_test_target = p_test_min OR p_test_max = p_test_min) THEN
2105 GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2106 RETURN FALSE;
2107 END IF;
2108
2109 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
2110 IF p_exp_error_type = 'N' THEN
2111 l_spec_num := p_above_spec_min ;
2112 ELSE
2113 l_spec_num := ( p_above_spec_min * ( p_test_max - p_test_min )) /100 ;
2114 END IF;
2115
2116 IF ABS(l_spec_num) > ( p_spec_test_target - p_spec_test_min) THEN
2117 IF p_exp_error_type = 'N' THEN
2118 l_max_value := ABS(p_spec_test_target - p_spec_test_min);
2119 ELSE
2120 l_max_value := (ABS(p_spec_test_target - p_spec_test_min) * 100)/(p_test_max - p_test_min);
2121 END IF;
2122 GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2123 RETURN FALSE;
2124 END IF;
2125 END IF;
2126 END IF;
2127
2128 l_position := '040';
2129
2130 IF p_validation_level IN ('FULL','BELOW_SPEC_MAX') THEN
2131
2132 IF p_below_spec_max = 0 OR p_below_spec_max IS NULL THEN
2133 RETURN TRUE;
2134 END IF;
2135
2136 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
2137 GMD_API_PUB.Log_Message('GMD_SPEC_ERROR_REG_NOT_APPL');
2138 RETURN FALSE;
2139 END IF;
2140
2141 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
2142 IF p_exp_error_type = 'N' THEN
2143 l_spec_num := p_below_spec_max ;
2144 ELSE
2145 l_spec_num := ( p_below_spec_max * ( p_test_max - p_test_min )) /100 ;
2146 END IF;
2147
2148 IF ABS(l_spec_num) > (p_spec_test_max - p_spec_test_target ) THEN
2149 IF p_exp_error_type = 'N' THEN
2150 l_max_value := ABS(p_spec_test_max - p_spec_test_target);
2151 ELSE
2152 l_max_value := (ABS(p_spec_test_max - p_spec_test_target) * 100)/(p_test_max - p_test_min);
2153 END IF;
2154 GMD_API_PUB.Log_Message('GMD_INVALID_SPEC_VAL_NUM','MAX_VAL',to_char(l_max_value));
2155 RETURN FALSE;
2156 END IF;
2157 END IF;
2158 END IF;
2159
2160 RETURN TRUE;
2161
2162 EXCEPTION
2163 WHEN OTHERS THEN
2164 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);
2165 RETURN FALSE;
2166 END SPEC_TEST_EXP_ERROR_REGION_VAL;
2167
2168
2169
2170 --Start of comments
2171 --+========================================================================+
2172 --| API Name : spec_test_precisions_valid |
2173 --| TYPE : Group |
2174 --| Notes : This function returns TRUE if the Spec Test Display and |
2175 --| Report precisions are valid, FALSE otherwise. |
2176 --| |
2177 --| |
2178 --| |
2179 --| HISTORY |
2180 --| Chetan Nagar 26-Jul-2002 Created. |
2181 --| |
2182 --+========================================================================+
2183 -- End of comments
2184
2185 FUNCTION spec_test_precisions_valid(p_spec_display_precision IN NUMBER,
2186 p_spec_report_precision IN NUMBER,
2187 p_test_display_precision IN NUMBER,
2188 p_test_report_precision IN NUMBER)
2189 RETURN BOOLEAN IS
2190
2191 e_range_error EXCEPTION;
2192
2193 BEGIN
2194
2195 IF (p_spec_report_precision > p_spec_display_precision) THEN
2196 GMD_API_PUB.Log_Message('GMD_REP_GRTR_DIS_PRCSN');
2197
2198 RETURN FALSE;
2199 ELSIF (p_spec_display_precision > p_test_display_precision) THEN
2200 GMD_API_PUB.Log_Message('SPEC_TEST_DISPLAY_PREC_ERROR');
2201
2202 RETURN FALSE;
2203 ELSIF (p_spec_report_precision > p_test_report_precision) THEN
2204 GMD_API_PUB.Log_Message('SPEC_TEST_REPORT_PREC_ERROR');
2205
2206 RETURN FALSE;
2207 END IF;
2208
2209 RETURN TRUE;
2210
2211 EXCEPTION
2212 -- Though there is no reason the program can reach
2213 -- here, this is coded just for the reasons we can
2214 -- not think of!
2215 WHEN OTHERS THEN
2216 RETURN FALSE;
2217
2218 END spec_test_precisions_valid;
2219
2220
2221
2222 --Start of comments
2223 --+========================================================================+
2224 --| API Name : status_record_updateable |
2225 --| TYPE : Group |
2226 --| Notes : This function returns FALSE if the transaction record |
2227 --| with the supplied status can not be updated, else TRUE. |
2228 --| |
2229 --| |
2230 --| |
2231 --| HISTORY |
2232 --| Chetan Nagar 26-Jul-2002 Created. |
2233 --| |
2234 --+========================================================================+
2235 -- End of comments
2236
2237 FUNCTION record_updateable_with_status(p_status NUMBER)
2238 RETURN BOOLEAN IS
2239
2240 CURSOR c_status (p_status_code NUMBER) IS
2241 SELECT a.updateable
2242 FROM gmd_qc_status a
2243 WHERE a.status_type =
2244 (SELECT status_type
2245 FROM gmd_qc_status b
2246 WHERE b.status_code = p_status_code
2247 and b.entity_type = 'S')
2248 and a.entity_type = 'S'
2249 ;
2250
2251 -- Local Variables
2252 upd_flag VARCHAR2(1);
2253
2254 BEGIN
2255 OPEN c_status(p_status);
2256 FETCH c_status INTO upd_flag;
2257 IF c_status%NOTFOUND THEN
2258 upd_flag:= 'N';
2259 END IF;
2260 CLOSE c_status ;
2261 IF upd_flag = 'N' THEN
2262 RETURN FALSE;
2263 ELSE
2264 RETURN TRUE;
2265 END IF;
2266
2267 EXCEPTION
2268 WHEN OTHERS THEN
2269 RETURN FALSE;
2270
2271 END record_updateable_with_status;
2272
2273 --Start of comments
2274 --+========================================================================+
2275 --| API Name : spec_used_in_sample |
2276 --| TYPE : Group |
2277 --| Notes : This function returns TRUE if the specification is used |
2278 --| in any sample else FALSE |
2279 --| |
2280 --| |
2281 --| HISTORY |
2282 --| Chetan Nagar 26-Jul-2002 Created. |
2283 --| |
2284 --+========================================================================+
2285 -- End of comments
2286
2287 FUNCTION spec_used_in_sample(p_spec_id NUMBER) RETURN BOOLEAN IS
2288
2289 -- perf bug 4924529 sql id 14687024 (FTS and MJC)
2290
2291 CURSOR cr_spec_exist_in_sample IS
2292 /*
2293 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_ALL_SPEC_VRS b
2294 WHERE
2295 b.spec_id = p_spec_id
2296 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID ; */
2297
2298 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_INVENTORY_SPEC_VRS b,
2299 gmd_qc_status_tl t
2300 WHERE
2301 b.spec_id = p_spec_id
2302 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2303 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'
2304 UNION
2305 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_WIP_SPEC_VRS b,
2306 gmd_qc_status_tl t
2307 WHERE
2308 b.spec_id = p_spec_id
2309 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2310 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'
2311 UNION
2312 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_CUSTOMER_SPEC_VRS b,
2313 gmd_qc_status_tl t
2314 WHERE
2315 b.spec_id = p_spec_id
2316 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2317 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'
2318 UNION
2319 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_SUPPLIER_SPEC_VRS b,
2320 gmd_qc_status_tl t
2321 WHERE
2322 b.spec_id = p_spec_id
2323 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2324 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'
2325 UNION
2326 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_MONITORING_SPEC_VRS b,
2327 gmd_qc_status_tl t
2328 WHERE
2329 b.spec_id = p_spec_id
2330 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2331 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'
2332 UNION
2333 SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_STABILITY_SPEC_VRS b,
2334 gmd_qc_status_tl t
2335 WHERE
2336 b.spec_id = p_spec_id
2337 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2338 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S';
2339
2340 /*SELECT '1' FROM GMD_SAMPLING_EVENTS a , GMD_COM_SPEC_VRS_VL b,
2341 gmd_qc_status_tl t
2342 WHERE
2343 b.spec_id = p_spec_id
2344 AND b.SPEC_VR_ID = a.ORIGINAL_SPEC_VR_ID
2345 AND b.spec_vr_status = t.status_code AND t.entity_type = 'S'; */
2346
2347
2348 dummy VARCHAR2(1);
2349 BEGIN
2350 IF p_spec_id IS NULL THEN
2351 RETURN FALSE;
2352 END IF;
2353
2354 OPEN cr_spec_exist_in_sample;
2355 FETCH cr_spec_exist_in_sample INTO dummy;
2356 IF cr_spec_exist_in_sample%FOUND THEN
2357 CLOSE cr_spec_exist_in_sample ;
2358 RETURN TRUE;
2359 END IF;
2360 CLOSE cr_spec_exist_in_sample;
2361 RETURN FALSE;
2362
2363 EXCEPTION
2364 WHEN OTHERS THEN
2365 RETURN TRUE;
2366
2367 END spec_used_in_sample ;
2368
2369 FUNCTION VERSION_CONTROL_STATE(p_entity VARCHAR2, p_entity_id NUMBER)
2370 RETURN VARCHAR2 IS
2371 l_state VARCHAR2(32) := 'N';
2372 l_version_enabled VARCHAR2(1) := 'N';
2373
2374 TYPE Status_ref_cur IS REF CURSOR;
2375 Status_cur Status_ref_cur;
2376
2377 BEGIN
2378
2379 -- Check for status that allow the version control
2380 -- e.g normally version control is set beyond
2381 -- status = 'Approved for gen use'
2382 -- p_entity = FND_PROFILE.VALUE('GMD_SPEC_VERSION_CONTROL')
2383
2384 IF (p_entity IS NULL OR p_entity = 'N') THEN
2385 return 'N';
2386 END IF;
2387
2388 OPEN Status_cur FOR
2389 Select b.version_enabled
2390 From gmd_specifications_b a, gmd_qc_status b
2391 Where a.spec_id = p_entity_id
2392 And a.spec_status = b.status_code
2393 and b.entity_type = 'S';
2394 FETCH Status_cur INTO l_version_enabled;
2395 ClOSE Status_cur;
2396
2397 IF ((p_entity = 'Y') AND (l_version_enabled = 'Y')) THEN
2398 l_state := 'Y';
2399 ELSIF ((p_entity = 'O') AND (l_version_enabled = 'Y')) THEN
2400 l_state := 'O';
2401 ELSE
2402 l_state := 'N';
2403 END IF;
2404
2405 return l_state;
2406
2407 EXCEPTION WHEN OTHERS THEN
2408 return 'N';
2409 END VERSION_CONTROL_STATE;
2410
2411 /*======================================================================
2412 -- PROCEDURE :
2413 -- create_specification
2414 --
2415 -- DESCRIPTION:
2416 -- This PL/SQL procedure is responsible for saving the
2417 -- new specification while versioning.
2418 --
2419 -- REQUIREMENTS
2420 --
2421 -- SYNOPSIS:
2422 -- create_specification(P_spec_id, X_spec_id);
2423 --
2424 -- HVERDDIN - Added References to new columns in SPEC HDR and SPEC TESTS
2425 --
2426 -- Saikiran Vankadari 07-Feb-2005 Pvt API calls changed as part of Convergence
2427 --
2428 --===================================================================== */
2429
2430 PROCEDURE create_specification(p_spec_id IN NUMBER,
2431 x_spec_id OUT NOCOPY NUMBER,
2432 x_return_status OUT NOCOPY VARCHAR2) IS
2433 X_spec_vers NUMBER;
2434 X_row NUMBER := 0;
2435 l_rowid ROWID;
2436
2437
2438 CURSOR Cur_get_hdr IS
2439 SELECT *
2440 FROM gmd_specifications
2441 WHERE spec_id = p_spec_id ;
2442 X_hdr_rec Cur_get_hdr%ROWTYPE;
2443
2444 CURSOR Cur_get_dtl IS
2445 SELECT *
2446 FROM gmd_spec_tests
2447 WHERE spec_id = p_spec_id;
2448 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
2449 X_dtl_tbl detail_tab;
2450
2451
2452 -- perf bug 4924529 sql id 14686617
2453 CURSOR Cur_spec_id IS
2454 -- SELECT GMD_QC_SPEC_ID_S.NEXTVAL FROM FND_DUAL;
2455 SELECT GMD_QC_SPEC_ID_S.NEXTVAL FROM sys.dual;
2456
2457
2458 CURSOR Cur_spec_vers IS
2459 SELECT MAX(spec_vers) + 1
2460 FROM gmd_specifications_b
2461 WHERE spec_name = X_hdr_rec.spec_name;
2462
2463 l_progress VARCHAR2(3);
2464
2465 BEGIN
2466
2467
2468 -- Initialize API return status to success
2469 x_return_status := FND_API.G_RET_STS_SUCCESS;
2470
2471 FND_MSG_PUB.initialize; -- clear the message stack.
2472
2473 l_progress := '010';
2474
2475 OPEN Cur_get_hdr;
2476 FETCH Cur_get_hdr INTO X_hdr_rec;
2477 CLOSE Cur_get_hdr;
2478
2479 FOR get_rec IN Cur_get_dtl LOOP
2480 X_row := X_row + 1;
2481 X_dtl_tbl(X_row) := get_rec;
2482 END LOOP;
2483
2484
2485 -- this will rollback the update made in the form for the current spec
2486 -- ( for which we are creating a new version)
2487 ROLLBACK;
2488
2489 l_progress := '015';
2490
2491 OPEN Cur_spec_vers;
2492 FETCH Cur_spec_vers INTO X_spec_vers;
2493 CLOSE Cur_spec_vers;
2494
2495 OPEN Cur_spec_id;
2496 FETCH Cur_spec_id INTO x_spec_id;
2497 CLOSE Cur_spec_id;
2498
2499 l_progress := '020';
2500 /* Insert spec header record */
2501
2502 GMD_SPECIFICATIONS_PVT.INSERT_ROW(
2503 X_ROWID => l_rowid,
2504 X_SPEC_ID => x_spec_id,
2505 X_SPEC_NAME => X_hdr_rec.SPEC_NAME,
2506 X_SPEC_VERS => x_spec_vers,
2507 X_SPEC_TYPE => x_hdr_rec.SPEC_TYPE,
2508 X_OVERLAY_IND => x_hdr_rec.OVERLAY_IND,
2509 X_BASE_SPEC_ID => x_hdr_rec.base_spec_id,
2510 X_INVENTORY_ITEM_ID => X_hdr_rec.INVENTORY_ITEM_ID,
2511 X_REVISION => X_hdr_rec.REVISION,
2512 X_GRADE_CODE => X_hdr_rec.GRADE_CODE,
2513 X_SPEC_STATUS => 100,
2514 X_OWNER_ORGANIZATION_ID => X_hdr_rec.OWNER_ORGANIZATION_ID,
2515 X_OWNER_ID => X_hdr_rec.OWNER_ID,
2516 X_SAMPLE_INV_TRANS_IND => X_hdr_rec.SAMPLE_INV_TRANS_IND,
2517 X_DELETE_MARK => X_hdr_rec.DELETE_MARK,
2518 X_TEXT_CODE => X_hdr_rec.TEXT_CODE,
2519 X_ATTRIBUTE_CATEGORY => X_hdr_rec.ATTRIBUTE_CATEGORY,
2520 X_ATTRIBUTE1 => X_hdr_rec.ATTRIBUTE1,
2521 X_ATTRIBUTE2 => X_hdr_rec.ATTRIBUTE2,
2522 X_ATTRIBUTE3 => X_hdr_rec.ATTRIBUTE3,
2523 X_ATTRIBUTE4 => X_hdr_rec.ATTRIBUTE4,
2524 X_ATTRIBUTE5 => X_hdr_rec.ATTRIBUTE5,
2525 X_ATTRIBUTE6 => X_hdr_rec.ATTRIBUTE6,
2526 X_ATTRIBUTE7 => X_hdr_rec.ATTRIBUTE7,
2527 X_ATTRIBUTE8 => X_hdr_rec.ATTRIBUTE8,
2528 X_ATTRIBUTE9 => X_hdr_rec.ATTRIBUTE9,
2529 X_ATTRIBUTE10 => X_hdr_rec.ATTRIBUTE10,
2530 X_ATTRIBUTE11 => X_hdr_rec.ATTRIBUTE11,
2531 X_ATTRIBUTE12 => X_hdr_rec.ATTRIBUTE12,
2532 X_ATTRIBUTE13 => X_hdr_rec.ATTRIBUTE13,
2533 X_ATTRIBUTE14 => X_hdr_rec.ATTRIBUTE14,
2534 X_ATTRIBUTE15 => X_hdr_rec.ATTRIBUTE15,
2535 X_ATTRIBUTE16 => X_hdr_rec.ATTRIBUTE16,
2536 X_ATTRIBUTE17 => X_hdr_rec.ATTRIBUTE17,
2537 X_ATTRIBUTE18 => X_hdr_rec.ATTRIBUTE18,
2538 X_ATTRIBUTE19 => X_hdr_rec.ATTRIBUTE19,
2539 X_ATTRIBUTE20 => X_hdr_rec.ATTRIBUTE20,
2540 X_ATTRIBUTE21 => X_hdr_rec.ATTRIBUTE21,
2541 X_ATTRIBUTE22 => X_hdr_rec.ATTRIBUTE22,
2542 X_ATTRIBUTE23 => X_hdr_rec.ATTRIBUTE23,
2543 X_ATTRIBUTE24 => X_hdr_rec.ATTRIBUTE24,
2544 X_ATTRIBUTE25 => X_hdr_rec.ATTRIBUTE25,
2545 X_ATTRIBUTE26 => X_hdr_rec.ATTRIBUTE26,
2546 X_ATTRIBUTE27 => X_hdr_rec.ATTRIBUTE27,
2547 X_ATTRIBUTE28 => X_hdr_rec.ATTRIBUTE28,
2548 X_ATTRIBUTE29 => X_hdr_rec.ATTRIBUTE29,
2549 X_ATTRIBUTE30 => X_hdr_rec.ATTRIBUTE30,
2550 X_SPEC_DESC => X_hdr_rec.SPEC_DESC,
2551 X_CREATION_DATE => SYSDATE,
2552 X_CREATED_BY => FND_GLOBAL.USER_ID,
2553 X_LAST_UPDATE_DATE => SYSDATE,
2554 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2555 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
2556
2557 l_progress := '030';
2558
2559 FOR i IN 1..X_dtl_tbl.count LOOP
2560 GMD_SPEC_TESTS_PVT.INSERT_ROW(
2561 X_ROWID => l_rowid,
2562 X_SPEC_ID => x_spec_id,
2563 X_FROM_BASE_IND => x_dtl_tbl(i).FROM_BASE_IND,
2564 X_EXCLUDE_IND => x_dtl_tbl(i).EXCLUDE_IND,
2565 X_MODIFIED_IND => x_dtl_tbl(i).MODIFIED_IND,
2566 X_TEST_ID => X_dtl_tbl(i).TEST_ID,
2567 X_ATTRIBUTE1 => X_dtl_tbl(i).ATTRIBUTE1,
2568 X_ATTRIBUTE2 => X_dtl_tbl(i).ATTRIBUTE2,
2569 X_MIN_VALUE_CHAR => X_dtl_tbl(i).MIN_VALUE_CHAR,
2570 X_TEST_METHOD_ID => X_dtl_tbl(i).TEST_METHOD_ID,
2571 X_SEQ => X_dtl_tbl(i).SEQ,
2572 X_TEST_QTY => X_dtl_tbl(i).TEST_QTY,
2573 X_TEST_QTY_UOM => X_dtl_tbl(i).TEST_QTY_UOM,
2574 X_MIN_VALUE_NUM => X_dtl_tbl(i).MIN_VALUE_NUM,
2575 X_TARGET_VALUE_NUM => X_dtl_tbl(i).TARGET_VALUE_NUM,
2576 X_MAX_VALUE_NUM => X_dtl_tbl(i).MAX_VALUE_NUM,
2577 X_ATTRIBUTE5 => X_dtl_tbl(i).ATTRIBUTE5,
2578 X_ATTRIBUTE6 => X_dtl_tbl(i).ATTRIBUTE6,
2579 X_ATTRIBUTE7 => X_dtl_tbl(i).ATTRIBUTE7,
2580 X_ATTRIBUTE8 => X_dtl_tbl(i).ATTRIBUTE8,
2581 X_ATTRIBUTE9 => X_dtl_tbl(i).ATTRIBUTE9,
2582 X_ATTRIBUTE10 => X_dtl_tbl(i).ATTRIBUTE10,
2583 X_ATTRIBUTE11 => X_dtl_tbl(i).ATTRIBUTE11,
2584 X_ATTRIBUTE12 => X_dtl_tbl(i).ATTRIBUTE12,
2585 X_ATTRIBUTE13 => X_dtl_tbl(i).ATTRIBUTE13,
2586 X_ATTRIBUTE14 => X_dtl_tbl(i).ATTRIBUTE14,
2587 X_ATTRIBUTE15 => X_dtl_tbl(i).ATTRIBUTE15,
2588 X_ATTRIBUTE16 => X_dtl_tbl(i).ATTRIBUTE16,
2589 X_ATTRIBUTE17 => X_dtl_tbl(i).ATTRIBUTE17,
2590 X_ATTRIBUTE18 => X_dtl_tbl(i).ATTRIBUTE18,
2591 X_USE_TO_CONTROL_STEP => X_dtl_tbl(i).USE_TO_CONTROL_STEP,
2592 X_PRINT_SPEC_IND => X_dtl_tbl(i).PRINT_SPEC_IND,
2593 X_PRINT_RESULT_IND => X_dtl_tbl(i).PRINT_RESULT_IND,
2594 X_TEXT_CODE => X_dtl_tbl(i).TEXT_CODE,
2595 X_ATTRIBUTE_CATEGORY => X_dtl_tbl(i).ATTRIBUTE_CATEGORY,
2596 X_ATTRIBUTE3 => X_dtl_tbl(i).ATTRIBUTE3,
2597 X_RETEST_LOT_EXPIRY_IND => X_dtl_tbl(i).RETEST_LOT_EXPIRY_IND,
2598 X_ATTRIBUTE19 => X_dtl_tbl(i).ATTRIBUTE19,
2599 X_ATTRIBUTE20 => X_dtl_tbl(i).ATTRIBUTE20,
2600 X_MAX_VALUE_CHAR => X_dtl_tbl(i).MAX_VALUE_CHAR,
2601 X_TEST_REPLICATE => X_dtl_tbl(i).TEST_REPLICATE,
2602 X_CHECK_RESULT_INTERVAL => X_dtl_tbl(i).CHECK_RESULT_INTERVAL,
2603 X_OUT_OF_SPEC_ACTION => X_dtl_tbl(i).OUT_OF_SPEC_ACTION,
2604 X_EXP_ERROR_TYPE => X_dtl_tbl(i).EXP_ERROR_TYPE,
2605 X_BELOW_SPEC_MIN => X_dtl_tbl(i).BELOW_SPEC_MIN,
2606 X_ABOVE_SPEC_MIN => X_dtl_tbl(i).ABOVE_SPEC_MIN,
2607 X_BELOW_SPEC_MAX => X_dtl_tbl(i).BELOW_SPEC_MAX,
2608 X_ABOVE_SPEC_MAX => X_dtl_tbl(i).ABOVE_SPEC_MAX,
2609 X_BELOW_MIN_ACTION_CODE => X_dtl_tbl(i).BELOW_MIN_ACTION_CODE,
2610 X_ABOVE_MIN_ACTION_CODE => X_dtl_tbl(i).ABOVE_MIN_ACTION_CODE,
2611 X_BELOW_MAX_ACTION_CODE => X_dtl_tbl(i).BELOW_MAX_ACTION_CODE,
2612 X_ABOVE_MAX_ACTION_CODE => X_dtl_tbl(i).ABOVE_MAX_ACTION_CODE,
2613 X_OPTIONAL_IND => X_dtl_tbl(i).OPTIONAL_IND,
2614 X_DISPLAY_PRECISION => X_dtl_tbl(i).DISPLAY_PRECISION,
2615 X_REPORT_PRECISION => X_dtl_tbl(i).REPORT_PRECISION,
2616 X_TEST_PRIORITY => X_dtl_tbl(i).TEST_PRIORITY,
2617 X_PRINT_ON_COA_IND => X_dtl_tbl(i).PRINT_ON_COA_IND,
2618 X_TARGET_VALUE_CHAR => X_dtl_tbl(i).TARGET_VALUE_CHAR,
2619 X_ATTRIBUTE4 => X_dtl_tbl(i).ATTRIBUTE4,
2620 X_ATTRIBUTE21 => X_dtl_tbl(i).ATTRIBUTE21,
2621 X_ATTRIBUTE22 => X_dtl_tbl(i).ATTRIBUTE22,
2622 X_ATTRIBUTE23 => X_dtl_tbl(i).ATTRIBUTE23,
2623 X_ATTRIBUTE24 => X_dtl_tbl(i).ATTRIBUTE24,
2624 X_ATTRIBUTE25 => X_dtl_tbl(i).ATTRIBUTE25,
2625 X_ATTRIBUTE26 => X_dtl_tbl(i).ATTRIBUTE26,
2626 X_ATTRIBUTE27 => X_dtl_tbl(i).ATTRIBUTE27,
2627 X_ATTRIBUTE28 => X_dtl_tbl(i).ATTRIBUTE28,
2628 X_ATTRIBUTE29 => X_dtl_tbl(i).ATTRIBUTE29,
2629 X_ATTRIBUTE30 => X_dtl_tbl(i).ATTRIBUTE30,
2630 X_TEST_DISPLAY => X_dtl_tbl(i).TEST_DISPLAY,
2631 X_CREATION_DATE => SYSDATE,
2632 X_CREATED_BY => FND_GLOBAL.USER_ID,
2633 X_LAST_UPDATE_DATE => SYSDATE,
2634 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
2635 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
2636 X_VIABILITY_DURATION => X_dtl_tbl(i).VIABILITY_DURATION,
2637 X_TEST_EXPIRATION_DAYS => X_dtl_tbl(i).DAYS,
2638 X_TEST_EXPIRATION_HOURS => X_dtl_tbl(i).HOURS,
2639 X_TEST_EXPIRATION_MINUTES => X_dtl_tbl(i).MINUTES,
2640 X_TEST_EXPIRATION_SECONDS => X_dtl_tbl(i).SECONDS,
2641 X_CALC_UOM_CONV_IND => X_dtl_tbl(i).CALC_UOM_CONV_IND,
2642 X_TO_QTY_UOM => X_dtl_tbl(i).TO_QTY_UOM
2643 );
2644
2645 END LOOP ;
2646
2647 l_progress := '040';
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2652 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
2653 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.CREATE_SPECIFICATION');
2654 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
2655 FND_MESSAGE.Set_Token('POSITION',l_progress );
2656 FND_MSG_PUB.ADD;
2657 END create_specification ;
2658
2659
2660
2661
2662
2663 --Start of comments
2664 --+========================================================================+
2665 --| API Name : change_status |
2666 --| TYPE : Group |
2667 --| Notes : |
2668 --| |
2669 --| HISTORY |
2670 --| Chetan Nagar 05-Oct-2002 Created. |
2671 --| Mahesh Chandak 16-apr-2003 Modified to support stability study|
2672 --| Chetan Nagar 06-May-2003 B2943737 SQL Bind Variable Project.|
2673 --+========================================================================+
2674 -- End of comments
2675
2676 PROCEDURE change_status
2677 (
2678 p_table_name IN VARCHAR2
2679 , p_id IN NUMBER
2680 , p_source_status IN NUMBER
2681 , p_target_status IN NUMBER
2682 , p_mode IN VARCHAR2
2683 , p_entity_type IN VARCHAR2 DEFAULT 'S'
2684 , x_return_status OUT NOCOPY VARCHAR2
2685 , x_message OUT NOCOPY VARCHAR2
2686 ) IS
2687
2688 -- Cursors
2689 CURSOR c_all_status (p_mode VARCHAR2,
2690 p_current_status NUMBER,
2691 p_target_status NUMBER) IS
2692 SELECT decode(p_mode, 'S', current_status,
2693 'P', pending_status,
2694 'R', rework_status,
2695 'A', target_status)
2696 FROM gmd_qc_status_next
2697 WHERE current_status = p_current_status
2698 AND target_status = p_target_status
2699 AND entity_type = p_entity_type
2700 ;
2701
2702 -- Local Variables
2703 l_status NUMBER;
2704 l_sql_stmt VARCHAR2(1000);
2705
2706 BEGIN
2707
2708
2709 IF (l_debug = 'Y') THEN
2710 NULL;
2711 --Commented because of GSCC violation
2712 /* dbms_output.put_line('Entering Procedure CHANGE_STATUS');
2713 dbms_output.put_line('Input Parameters.');
2714 dbms_output.put_line('p_table_name: '|| p_table_name ||
2715 'p_id: '|| p_id ||
2716 'p_source_status: '|| p_source_status ||
2717 'p_target_status: '|| p_target_status ||
2718 'p_mode: '|| p_mode); */
2719 END IF;
2720
2721 -- Set Success status
2722 x_return_status := 'S';
2723
2724 -- Validate Input Parameters for NULLs
2725 IF (p_table_name IS NULL OR p_id IS NULL OR p_source_status IS NULL OR
2726 p_target_status IS NULL OR p_mode IS NULL) THEN
2727 x_return_status := 'E';
2728 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_PARAMETERS');
2729 x_message := FND_MESSAGE.GET;
2730 RETURN;
2731 END IF;
2732
2733
2734 IF NOT (p_mode in ('P', 'R', 'A', 'S')) THEN
2735 x_return_status := 'E';
2736 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_PARAMETERS');
2737 x_message := FND_MESSAGE.GET;
2738 RETURN;
2739 END IF;
2740
2741 IF (l_debug = 'Y') THEN
2742 NULL;
2743 --Commented because of GSCC violation
2744 /* dbms_output.put_line('Input parameters are valid.'); */
2745 END IF;
2746
2747
2748 -- Get the status to be updated
2749 OPEN c_all_status(p_mode, p_source_status, p_target_status);
2750 FETCH c_all_status INTO l_status;
2751 IF c_all_status%NOTFOUND THEN
2752 CLOSE c_all_status;
2753 x_return_status := 'E';
2754 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS_NOT_FOUND');
2755 x_message := FND_MESSAGE.GET;
2756 RETURN;
2757 END IF;
2758 CLOSE c_all_status;
2759
2760 IF (l_debug = 'Y') THEN
2761 NULL;
2762 --Commented because of GSCC violation
2763 /* dbms_output.put_line('Set the status to: '|| l_status); */
2764 END IF;
2765
2766 -- Now construct the SQL Stmt.
2767 -- B2943737 SQL Bind Variable Project.
2768 IF (upper(p_table_name) = 'GMD_SPECIFICATIONS_B' ) THEN
2769 l_sql_stmt := 'UPDATE GMD_SPECIFICATIONS_B' ||
2770 ' SET spec_status = :l_status' ||
2771 ' WHERE spec_id = :p_id';
2772 -- added by mahesh to support stability study
2773 ELSIF (upper(p_table_name) = 'GMD_STABILITY_STUDIES_B' ) THEN
2774 l_sql_stmt := 'UPDATE GMD_STABILITY_STUDIES_B' ||
2775 ' SET status = :l_status' ||
2776 ' WHERE ss_id = :p_id';
2777 ELSE
2778 l_sql_stmt := 'UPDATE ' || p_table_name ||
2779 ' SET spec_vr_status = :l_status' ||
2780 ' WHERE spec_vr_id = :p_id';
2781 END IF;
2782
2783 IF (l_debug = 'Y') THEN
2784 NULL;
2785 --Commented because of GSCC violation
2786 /* dbms_output.put_line('SQL Statement: ' || l_sql_stmt); */
2787 END IF;
2788
2789
2790 EXECUTE IMMEDIATE l_sql_stmt USING l_status, p_id;
2791
2792
2793 IF (l_debug = 'Y') THEN
2794 NULL;
2795 --Commented because of GSCC violation
2796 /* dbms_output.put_line('SQL Statement executed.');
2797 dbms_output.put_line('Leaving Procedure CHANGE_STATUS'); */
2798 END IF;
2799
2800 RETURN;
2801
2802 EXCEPTION
2803 WHEN OTHERS THEN
2804 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
2805 FND_MESSAGE.SET_TOKEN('PACKAGE','GMD_SPEC_GRP.change_status');
2806 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,100));
2807 x_message := FND_MESSAGE.GET;
2808 x_return_status := 'E';
2809 RETURN;
2810
2811 END change_status;
2812
2813 --+=========================================================================+
2814 --| PROCEDURE NAME |
2815 --| Get_Who |
2816 --| |
2817 --| USAGE |
2818 --| Used to retrieve WHO information |
2819 --| |
2820 --| DESCRIPTION |
2821 --| This procedure is used to retrieve the who field information |
2822 --| |
2823 --| PARAMETERS |
2824 --| p_user_name IN VARCHAR2 - User name |
2825 --| x_user_id OUT NUMBER - user id of the user |
2826 --| |
2827 --| HISTORY |
2828 --| Saikiran Vankadari 02-May-2005 Created as part of Convergence changes
2829 --+=========================================================================+
2830 PROCEDURE Get_Who
2831 ( p_user_name IN fnd_user.user_name%TYPE
2832 , x_user_id OUT NOCOPY fnd_user.user_id%TYPE
2833 )
2834 IS
2835 CURSOR fnd_user_c1 IS
2836 SELECT
2837 user_id
2838 FROM
2839 fnd_user
2840 WHERE
2841 user_name = p_user_name;
2842
2843 BEGIN
2844
2845 OPEN fnd_user_c1;
2846
2847 FETCH fnd_user_c1 INTO x_user_id;
2848
2849 -- TKW B2476518 7/23/2002
2850 -- If user not found, return -1 instead of 0.
2851 IF (fnd_user_c1%NOTFOUND)
2852 THEN
2853 x_user_id := -1;
2854 END IF;
2855
2856 CLOSE fnd_user_c1;
2857
2858 EXCEPTION
2859 WHEN OTHERS THEN
2860 RAISE;
2861
2862 END Get_Who;
2863
2864 END GMD_SPEC_GRP;
[Home] [Help]