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