1 PACKAGE BODY GMD_RESULTS_PUB AS
2 /* $Header: GMDPRESB.pls 120.6.12020000.6 2012/12/20 20:46:40 plowe ship $
3 *****************************************************************
4 * *
5 * Package GMD_RESULTS_PUB *
6 * *
7 * Contents RECORD_RESULTS *
8 * ADD_TESTS_TO_SAMPLE *
9 * DELETE_RESULTS *
10 * *
11 * Use This is the public layer for the QC RESULTS *
12 * *
13 * History *
14 * Written by H Verdding, OPM Development (EMEA) *
15 * *
16 * magupta B2752102: Added the parameters for validation of *
17 * resource for passing it to results group API*
18 * HVerddin B2711643: Added call to set user_context *
19 * odaboval 2709353: Added call to create composite results *
20 * P.Raghu 3467531: Commented the check for Disposition in *
21 * Record_Results procedure. *
22 * magupta 3492836: Reserve Sample Id Validation *
23 * *
24 * Sulipta Tripathy Bug # 3848483 Added new fields in update *
25 * statement. TESTER_ID in GMD_RESULTS *
26 * VALUE_IN_REPORT_PRECISION in GMD_SPEC_RESULTS*
27 * Modified cond. to update GMD_SPEC_RESULTS *
28 * even if eval_ind is NULL. *
29 * B.Stone 9-Sep-2004 Bug 3763419; Added Guaranteed by *
30 * Manufacturer evaluation with the same *
31 * business rules asApproved with Variance;*
32 * Result value not allowed with this *
33 * evaluation. *
34 * RLNAGARA 16-Mar-2006 Bug 5076736
35 * Modified the procedure Record_results so that it calculates *
36 * the results of tests which are of expression type. *
37 * M. Grosser 20-Apr-2006: Bug 5141976 : FP of bug 5123379 *
38 * If the result value is NULL for an expression, *
39 * set the result date and tester id to NULL. *
40 * M. Grosser 20-Apr-2006: Bug 5141976 : FP of bug 5123379 *
41 * P Lowe 03-OCT-2008 Bug 7420373 allow update of numeric *
42 * test if one has already been recorded just as in *
43 * form. *
44 * Because API needs to reflect what is in the form, *
45 * results cannot be updated if the disposition is *
46 * changed to one of these below *
47 * ('0RT', '4A', '5AV', '6RJ', '7CN') *
48 * P Lowe 12-NOV-2008 Bug 7524393 *
49 * P Lowe 12-NOV-2008 Bug 13566337 *
50 * comment out call to GMD_RESULTS_GRP.change_disp_for_auto_lot *
51 * as form does not behave like this i.e sample automatically *
52 * going from complete to accept (in record_results proc ) *
53 * P Lowe 07-JUL-2012 Bug 14239488 *
54 * Added call to the procedure *
55 * calc_expression after updating the gmd_results table. *
56 * P Lowe 14-DEC-2012 Bug 14635099 *
57 * Changed procedure RECORD_RESULTS *
58 * 1. The Action Code is now populating on the result record *
59 * when result value is an out of specification and not passed *
60 * explicitly *
61 * 2. A new line is now being created for the test which is *
62 * out of spec. *
63 * 3. If user passes the evaluation status Accept with variance *
64 * (1V), Reject (2R) , Cancel (4C) , Void (50) then this takes *
65 * precedence over everything even if results is in spec l *
66 * *
67 *****************************************************************
68 */
69
70 -- Global variables
71
72 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_RESULTS_PUB';
73
74 PROCEDURE RECORD_RESULTS
75 ( p_api_version IN NUMBER
76 , p_init_msg_list IN VARCHAR2
77 , p_commit IN VARCHAR2
78 , p_validation_level IN NUMBER
79 , p_results_rec IN GMD_RESULTS_PUB.RESULTS_REC
80 , p_user_name IN VARCHAR2
81 , x_results_rec OUT NOCOPY GMD_RESULTS%ROWTYPE
82 , x_spec_results_rec OUT NOCOPY GMD_SPEC_RESULTS%ROWTYPE
83 , x_return_status OUT NOCOPY VARCHAR2
84 , x_msg_count OUT NOCOPY NUMBER
85 , x_msg_data OUT NOCOPY VARCHAR2
86 , p_user_responsibility_id IN NUMBER DEFAULT NULL /*NSRIVAST, INVCONV*/
87 )
88 IS
89 l_api_name CONSTANT VARCHAR2 (30) := 'RECORD_RESULTS';
90 l_api_version CONSTANT NUMBER := 3.0;
91 l_msg_count NUMBER :=0;
92 l_msg_data VARCHAR2(2000);
93 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
94 l_results_rec GMD_RESULTS_PUB.RESULTS_REC;
95 l_tests_rec GMD_QC_TESTS%ROWTYPE;
96 l_samples_rec GMD_SAMPLES%ROWTYPE;
97 l_spec_results_row_in GMD_SPEC_RESULTS%ROWTYPE;
98 l_spec_results_row GMD_SPEC_RESULTS%ROWTYPE;
99 l_spec_tests_in GMD_SPEC_TESTS%ROWTYPE;
100 l_spec_tests GMD_SPEC_TESTS%ROWTYPE;
101 l_results_row_in GMD_RESULTS%ROWTYPE;
102 l_results_row GMD_RESULTS%ROWTYPE;
103 l_user_id NUMBER(15);
104 l_assign_type NUMBER;
105 l_date DATE := SYSDATE;
106 l_result_id NUMBER;
107 l_test_type VARCHAR2(1);
108 l_result_value_char VARCHAR2(80) := NULL;
109 l_result_value_num NUMBER := NULL;
110 l_in_spec VARCHAR2(1) := NULL;
111 l_event_spec_disp_id NUMBER;
112 l_samples_req NUMBER;
113 l_samples_act NUMBER;
114 l_spec_id NUMBER;
115 l_sample_disp VARCHAR2(3);
116 l_sample_event_disp VARCHAR2(3);
117 l_validate_res GMD_RESULTS_GRP.result_data;
118 --Bug 3492836
119 l_reserve_sampling_event_id NUMBER;
120 --Bug 3492836
121
122 l_rslt_tbl gmd_results_grp.rslt_tbl; --RLNAGARA BUG#5076736
123 l_retest_action_code VARCHAR2(32) := NULL; -- 14635099
124
125 CURSOR c_get_event_spec (p_sampling_event_id NUMBER, p_sample_id NUMBER)
126 IS
127 SELECT e.event_spec_disp_id, s.disposition , e.spec_id,
128 se.sample_req_cnt, se.sample_active_cnt
129 FROM gmd_event_spec_disp e , gmd_sample_spec_disp s , gmd_sampling_events se
130 WHERE s.event_spec_disp_id = e.event_spec_disp_id
131 AND se.sampling_event_id = e.sampling_event_id
132 AND se.sampling_event_id = p_sampling_event_id
133 AND s.sample_id = p_sample_id
134 AND e.spec_used_for_lot_attrib_ind = 'Y'
135 AND e.delete_mark = 0
136 AND s.delete_mark = 0;
137
138 CURSOR c_get_result_num ( p_result_char VARCHAR, p_test_id NUMBER)
139 IS
140 SELECT text_range_seq
141 FROM GMD_QC_TEST_VALUES_B
142 WHERE test_id = p_test_id
143 AND value_char = p_result_char;
144
145 -- 14635099 start
146 -- new cursor
147 CURSOR c_spec_test (p_spec_id NUMBER, p_test_id NUMBER) IS
148 SELECT out_of_spec_action
149 FROM gmd_spec_tests_b
150 WHERE spec_id = p_spec_id
151 AND test_id = p_test_id;
152 -- 14635099 new cursor
153 CURSOR get_config IS
154 select retest_action_code
155 from gmd_quality_config
156 where organization_id = l_samples_rec.organization_id;
157
158 l_sample gmd_samples%rowtype;
159 test_ids gmd_api_pub.number_tab;
160 add_rslt_tab_out gmd_api_pub.gmd_results_tab;
161 add_spec_tab_out gmd_api_pub.gmd_spec_results_tab;
162
163 -- 14635099 end
164
165
166 -- bug 2709353, odaboval added the cursor in order to get the S.E. disposition
167 CURSOR c_sample_event ( p_event_spec_disp NUMBER)
168 IS
169 SELECT disposition
170 FROM GMD_EVENT_SPEC_DISP
171 WHERE event_spec_disp_id = p_event_spec_disp
172 AND delete_mark = 0;
173
174 /*NSRIVAST, INVCONV*/
175
176 CURSOR cur_get_appl_id IS
177 SELECT application_id
178 FROM fnd_application
179 WHERE application_short_name = 'GMD';
180
181 CURSOR check_resp_lab_access(cp_lab_organization_id NUMBER,cp_application_id NUMBER) IS
182 SELECT 1
183 FROM gmd_parameters_hdr gmd, org_access_view org
184 WHERE org.organization_id = gmd.organization_id
185 AND org.organization_id = cp_lab_organization_id
186 AND org.responsibility_id = p_user_responsibility_id
187 AND org.resp_application_id = cp_application_id
188 AND gmd.lab_ind = 1
189 AND org.inventory_enabled_flag = 'Y' ;
190
191 l_application_id NUMBER;
192 /*NSRIVAST, INVCONV*/
193
194 BEGIN
195
196
197 -- Standard Start OF API savepoint
198
199 SAVEPOINT RECORD_RESULTS;
200
201
202 /* Standard call to get for call compatibility. */
203
204 IF NOT FND_API.Compatible_API_CALL
205 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
206 THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209
210 /* Initialize message list if p_int_msg_list is set TRUE. */
211 IF FND_API.to_boolean(p_init_msg_list)
212 THEN
213 FND_MSG_PUB.Initialize;
214 END IF;
215
216 -- Initialize API return Parameters
217
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219
220 -- Validate User Name Parameter
221
222 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
223 ,x_user_id => l_user_id);
224
225 IF NVL(l_user_id, -1) < 0
226 THEN
227 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
228 'l_user_name', p_user_name);
229
230 RAISE FND_API.G_EXC_ERROR;
231 ELSE
232 -- Added below for BUG 2711643. Hverddin
233 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
234 x_return_status => l_return_status);
235
236 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239
240 END IF;
241
242 /*NSRIVAST, INVCONV*/
243
244 OPEN cur_get_appl_id;
245 FETCH cur_get_appl_id INTO l_application_id;
246 CLOSE cur_get_appl_id;
247
248 OPEN check_resp_lab_access(l_results_row.organization_id,l_application_id);
249 IF check_resp_lab_access%NOTFOUND THEN
250 GMD_API_PUB.Log_Message('GMD_RESP_LAB_NOACCESS');
251 CLOSE check_resp_lab_access;
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254 CLOSE check_resp_lab_access;
255 /*NSRIVAST, INVCONV*/
256
257 VALIDATE_INPUT
258 ( p_results_rec => p_results_rec,
259 x_return_status => l_return_status
260 );
261
262 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266
267 IF p_results_rec.result_id IS NULL THEN
268
269 GET_RESULT_INFO
270 ( p_results_rec => p_results_rec,
271 x_tests_rec => l_tests_rec,
272 x_samples_rec => l_samples_rec,
273 x_return_status => l_return_status
274 );
275
276 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
277 RAISE FND_API.G_EXC_ERROR;
278 END IF;
279
280 -- Fetch the Result Record
281 l_results_row_in.test_id := l_tests_rec.test_id;
282 l_results_row_in.sample_id := l_samples_rec.sample_id;
283 l_results_row_in.test_replicate_cnt := p_results_rec.test_replicate_cnt;
284
285 IF NOT GMD_RESULTS_PVT.fetch_row
286 ( p_results => l_results_row_in,
287 x_results => l_results_row
288 ) THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 ELSE
293 -- Fetch Result Row
294 l_results_row_in.result_id := p_results_rec.result_id;
295
296 IF NOT GMD_RESULTS_PVT.fetch_row
297 ( p_results => l_results_row_in,
298 x_results => l_results_row
299 ) THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 -- Get Result Info
304 l_results_rec.test_id := l_results_row.test_id;
305 l_results_rec.sample_id := l_results_row.sample_id;
306 l_results_rec.test_replicate_cnt := l_results_row.test_replicate_cnt;
307
308 GET_RESULT_INFO
309 ( p_results_rec => l_results_rec,
310 x_tests_rec => l_tests_rec,
311 x_samples_rec => l_samples_rec,
312 x_return_status => l_return_status
313 );
314
315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318
319 END IF;
320
321
322 -- Validate that Result Record is Not deleted
323 IF l_results_row.delete_mark = 1 THEN
324 GMD_API_PUB.Log_Message('GMD_RESULT_DELETED');
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327
328 -- If values have been populated for this result then
329 -- Return an ERROR.
330
331
332 -- Bug 7420373 -- allow update of numeric
333 -- test if one has already been recorded just as in the form
334 /* IF l_results_row.result_value_num IS NOT NULL OR
335 l_results_row.result_value_char IS NOT NULL THEN
336 -- Result values have been recorded for this Result
337 GMD_API_PUB.Log_Message('GMD_RESULT_RECORDED',
338 'l_ORGN_CODE', l_samples_rec.orgn_code,
339 'l_SAMPLE_NO', l_samples_rec.sample_no,
340 'l_TEST', l_tests_rec.test_code);
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343 */
344
345 -- Validate that the values in the RESULT REC i.e RESULT_CHAR and NUM
346 -- Are populated for the correct test_type
347 -- Bug 3763419; Added condition to only check for test_type if not
348 -- Guaranteed by Manufacturer (1X) evaluation.
349
350
351 -- 14635099
352 OPEN get_config;
353 FETCH get_config into l_retest_action_code;
354 CLOSE get_config;
355
356
357 -- RLNAGARA 16-Jan-2006 Added the OR condition
358 IF p_results_rec.eval_ind <> '1Z' OR p_results_rec.eval_ind IS NULL THEN
359
360 IF l_tests_rec.test_type = 'E' THEN
361
362 --RLNAGARA Begin BUG#5076736 Calling gmd_results_grp.calc_expression
363
364 OPEN c_get_event_spec(l_samples_rec.sampling_event_id,
365 l_samples_rec.sample_id);
366 FETCH c_get_event_spec INTO l_event_spec_disp_id, l_sample_disp, l_spec_id,
367 l_samples_req, l_samples_act;
368 IF c_get_event_spec%NOTFOUND THEN
369 GMD_API_PUB.Log_Message('GMD_NO_SPEC_EVENT_FOUND',
370 'SPEC_ID', l_spec_id,
371 'SAMP_EVENT', l_samples_rec.sampling_event_id);
372 RAISE FND_API.G_EXC_ERROR;
373 CLOSE c_get_event_spec;
374 END IF;
375 CLOSE c_get_event_spec;
376
377 gmd_results_grp.calc_expression
378 ( p_sample_id => l_samples_rec.sample_id --:gmdqsmpl.sample_id
379 , p_event_spec_disp_id => l_event_spec_disp_id --:gmdqsmpl.event_spec_disp_id
380 , p_spec_id => l_spec_id --:gmdqsmpl.spec_id
381 , x_rslt_tbl => l_rslt_tbl
382 , x_return_status => l_return_status);
383
384 IF (l_return_Status <> 'S') THEN
385 GMD_API_PUB.Log_Message('GMD_EXP_RES_DISALLOWED');
386 RAISE FND_API.G_EXC_ERROR;
387 END IF;
388 --RLNAGARA Bug5076736 end
389
390
391 ELSIF l_tests_rec.test_type in ('N','L') THEN
392
393 -- Check that the result_value is Numeric
394 IF NOT GMD_RESULTS_GRP.is_value_numeric
395 ( p_char_number => p_results_rec.result_value) THEN
396 GMD_API_PUB.Log_Message('GMD_RESULT_VAL_NUM_REQD');
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399
400 -- l_result_value_num := p_results_rec.result_value;
401 l_validate_res.result := p_results_rec.result_value;
402
403 ELSIF l_tests_rec.test_type in ( 'T','V','U') THEN
404
405 IF p_results_rec.result_value is NULL THEN
406 GMD_API_PUB.Log_Message('GMD_RESULT_VAL_CHAR_REQD');
407 RAISE FND_API.G_EXC_ERROR;
408 END IF;
409
410 l_validate_res.result := p_results_rec.result_value;
411 l_result_value_char := p_results_rec.result_value;
412 END IF;
413 END IF;
414
415 --=======================================================
416 -- Get event spec disp Value, and the sample dispostion
417 --=======================================================
418
419 OPEN c_get_event_spec(l_samples_rec.sampling_event_id,
420 l_samples_rec.sample_id);
421 FETCH c_get_event_spec INTO l_event_spec_disp_id, l_sample_disp, l_spec_id,
422 l_samples_req, l_samples_act;
423 IF c_get_event_spec%NOTFOUND THEN
424 GMD_API_PUB.Log_Message('GMD_NO_SPEC_EVENT_FOUND',
425 'SPEC_ID', l_spec_id,
426 'SAMP_EVENT', l_samples_rec.sampling_event_id);
427 RAISE FND_API.G_EXC_ERROR;
428 CLOSE c_get_event_spec;
429 END IF;
430 CLOSE c_get_event_spec;
431
432
433
434 -- part of bug 7420373 as api needs to reflect what is in the form
435
436 -- Results cannot be updated if the disposition is changed to one of these below
437 IF l_sample_disp in ('0RT', '4A', '5AV', '6RJ', '7CN')
438 THEN
439 GMD_API_PUB.Log_Message('GMD_SAMPLE_DISP_INVALID',
440 'l_SAMPLE_DISP', l_sample_disp);
441 RAISE FND_API.G_EXC_ERROR;
442 END IF;
443
444
445 --Begin Bug#3467531, port bug 3494551 P.Raghu
446 --Commented the following code. No need to check the sample disposition.
447 /*
448 --== SHOULD THIS BE THE SAMPLE OR SAMPLE EVENT DISP
449 IF l_sample_disp NOT IN ('1P','2I') THEN
450 GMD_API_PUB.Log_Message('GMD_SAMPLE_DISP_INVALID',
451 'l_SAMPLE_DISP', l_sample_disp);
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454 */
455 --End Bug#3467531, port bug 3494551
456
457
458 -- IF specification record exists for this sample then
459 -- Get specification test details
460
461 IF l_spec_id IS NOT NULL THEN
462
463 l_spec_tests_in.test_id := l_tests_rec.test_id;
464 l_spec_tests_in.spec_id := l_spec_id;
465
466 IF NOT GMD_SPEC_TESTS_PVT.fetch_row
467 ( p_spec_tests => l_spec_tests_in,
468 x_spec_tests => l_spec_tests
469 ) THEN
470 -- Assume that this is an additional test
471 -- For this sample.
472 l_validate_res.additional_test_ind := 'Y';
473 END IF;
474 END IF;
475
476
477
478 --=================================
479 -- We need to populate Validate_res
480 -- record type.
481 --=================================
482
483 l_validate_res.spec_id := l_spec_tests.spec_id;
484 l_validate_res.test_id := l_tests_rec.test_id;
485 l_validate_res.test_type := l_tests_rec.test_type;
486 l_validate_res.min_num := l_tests_rec.min_value_num;
487 l_validate_res.max_num := l_tests_rec.max_value_num;
488 l_validate_res.report_precision := NVL(NVL(l_spec_tests.report_precision,
489 l_tests_rec.report_precision),0);
490 l_validate_res.display_precision := NVL(NVL(l_spec_tests.display_precision,
491 l_tests_rec.display_precision),0);
492
493 l_validate_res.lab_organization_id := l_results_row.lab_organization_id; /*NSRIVAST, INVCONV*/
494 l_validate_res.planned_resource := NVL(p_results_rec.planned_resource,
495 l_results_row.planned_resource);
496 l_validate_res.planned_resource_instance := NVL(p_results_rec.planned_resource_instance,
497 l_results_row.planned_resource_instance);
498 l_validate_res.actual_resource := NVL(p_results_rec.actual_resource,
499 l_results_row.actual_resource);
500 l_validate_res.actual_resource_instance := NVL(p_results_rec.actual_resource_instance,
501 l_results_row.actual_resource_instance);
502
503 -- bug; 14239488 plowe test value calculated is not passed onto the procedure validate_result
504 -- So added the following code.
505 FOR i in 1..l_rslt_tbl.count
506 LOOP
507 IF ( l_tests_rec.test_id =l_rslt_tbl(i).test_id ) then
508 l_validate_res.result :=l_rslt_tbl(i).value ;
509 END IF;
510 END LOOP;
511
512
513 --=================================
514 -- Now Validate result record values
515 --=================================
516
517 GMD_RESULTS_GRP.validate_result
518 ( p_result_rec => l_validate_res,
519 x_return_status => l_return_status
520 );
521
522 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
523 RAISE FND_API.G_EXC_ERROR;
524 END IF;
525
526
527 IF NOT GMD_RESULTS_PVT.LOCK_ROW ( p_result_id => l_results_row.result_id )
528 THEN
529 RAISE FND_API.G_EXC_ERROR;
530 ELSE
531 -- IF test_type is T then convert result char to Num
532 IF ( l_tests_rec.test_type = 'T') THEN
533
534 OPEN c_get_result_num ( p_results_rec.result_value,
535 l_tests_rec.test_id);
536 FETCH c_get_result_num INTO l_validate_res.result_num;
537 IF c_get_result_num%NOTFOUND THEN
538 CLOSE c_get_result_num;
539 GMD_API_PUB.Log_Message('GMD_RESULT_CHAR_NOTFOUND');
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542 CLOSE c_get_result_num;
543 END IF;
544
545
546 --==================================================
547 -- Bug 3492836 Start Validate Reserve Sample
548 --==================================================
549 --dbms_output.put_line('Sampling even id is '||l_samples_rec.sampling_event_id);
550 IF (p_results_rec.reserve_sample_id IS NOT NULL) THEN
551 BEGIN
552 SELECT sampling_event_id
553 INTO l_reserve_sampling_event_id
554 FROM gmd_samples
555 WHERE sample_id = p_results_rec.reserve_sample_id;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 GMD_API_PUB.Log_Message('GMD_INVALID_RESERVE_SMPL');
559 RAISE FND_API.G_EXC_ERROR;
560 END;
561
562 IF (l_samples_rec.sampling_event_id <> l_reserve_sampling_event_id) THEN
563 GMD_API_PUB.Log_Message('GMD_INVALID_RESERVE_SMPL');
564 RAISE FND_API.G_EXC_ERROR;
565 END IF;
566 END IF;
567 --==================================================
568 -- End Bug 3492836 Validate Reserve Sample
569 --==================================================
570
571 --=================================
572 -- Quantity MANAGEMENT
573 --=================================
574 -- Bug 3468300: if test qty exists on the result record, there must be a consumed qty on the result.
575 -- If consumed qty is not input to API, then take the test qty.
576 -- and corrected the value taken from the remaining qty
577 -- l_results_row.test_qty := p_results_rec.test_qty;
578 IF (( p_results_rec.consumed_qty IS NULL )
579 AND (l_results_row.test_qty IS NOT NULL )) THEN
580 -- consumed qty becomes test qty if not specified
581 l_results_row.consumed_qty := l_results_row.test_qty;
582 ELSIF ( p_results_rec.consumed_qty IS NOT NULL ) THEN
583 -- if consumed_qty exists it is taken from remaining qty
584 l_results_row.consumed_qty := p_results_rec.consumed_qty;
585 END IF;
586
587 IF (NVL(l_results_row.consumed_qty, 0) < 0) THEN
588 GMD_API_PUB.Log_Message('GMD_QM_NEGATIVE_QTY');
589 RAISE FND_API.G_EXC_ERROR;
590 --END IF; Bug 3468300: added else statement
591 ELSIF (( NVL(l_results_row.consumed_qty, 0) <> 0 )
592 AND (NVL(l_results_row.test_uom, '0') = '0' )) THEN
593 -- If test uom does not exist in result and consumed qty is specified, test uom must be in input file
594 IF ( NVL(p_results_rec.test_qty_uom, '0') <> '0' ) THEN /*NSRIVAST, INVCONV*/
595 l_results_row.test_uom := p_results_rec.test_qty_uom; /*NSRIVAST, INVCONV*/
596 END IF;
597
598 IF ( NVL(l_results_row.test_uom, '0') = '0' ) THEN
599 GMD_API_PUB.Log_Message('GMD_QM_TEST_UOM');
600 RAISE FND_API.G_EXC_ERROR;
601 END IF;
602 END IF;
603
604 --=================================
605 -- Update Results Record
606 --=================================
607
608 IF l_tests_rec.test_type = 'E' THEN --RLNAGARA BUG#5076736
609 select result_value_num into l_validate_res.result_num from gmd_results WHERE result_id = l_results_row.result_id;
610 select in_spec_ind into l_result_value_char from gmd_spec_results WHERE result_id = l_results_row.result_id;
611 END IF;
612
613 -- Bug 3468300: added consumed qty, test uom and reserve sample id
614 UPDATE GMD_RESULTS
615 SET result_date = NVL(p_results_rec.result_date,l_date), -- 3559127 (use of p_results_rec rather than l_results_rec)
616 last_update_date = l_date,
617 last_updated_by = l_user_id,
618 result_value_num = l_validate_res.result_num,
619 result_value_char = l_result_value_char,
620 planned_resource = l_results_rec.planned_resource,
621 planned_resource_instance = l_results_rec.planned_resource_instance,
622 actual_resource = l_results_rec.actual_resource,
623 actual_resource_instance = l_results_rec.actual_resource_instance,
624 planned_result_date = l_results_rec.planned_result_date,
625 -- test_qty = l_results_row.test_qty, -- bug 3468300: test qty does not change in this API
626 test_uom = l_results_row.test_uom,
627 consumed_qty = l_results_row.consumed_qty,
628 test_by_date = l_results_rec.test_by_date,
629 tester_id = NVL(p_results_rec.tester_id,l_user_id), /* Bug # 3848483 Added this line */
630 reserve_sample_id = p_results_rec.reserve_sample_id,
631 attribute_category = p_results_rec.attribute_category, -- added dffs for Bug 13976016 - HOW TO UPDATE DFF IN QUALITY RESULT FORM, USING API
632 attribute1 = p_results_rec.attribute1,
633 attribute2 = p_results_rec.attribute2,
634 attribute3 = p_results_rec.attribute3,
635 attribute4 = p_results_rec.attribute4,
636 attribute5 = p_results_rec.attribute5,
637 attribute6 = p_results_rec.attribute6,
638 attribute7 = p_results_rec.attribute7,
639 attribute8 = p_results_rec.attribute8,
640 attribute9 = p_results_rec.attribute9,
641 attribute10 = p_results_rec.attribute10,
642 attribute11 = p_results_rec.attribute11,
643 attribute12 = p_results_rec.attribute12,
644 attribute13 = p_results_rec.attribute13,
645 attribute14 = p_results_rec.attribute14,
646 attribute15 = p_results_rec.attribute15,
647 attribute16 = p_results_rec.attribute16,
648 attribute17 = p_results_rec.attribute17,
649 attribute18 = p_results_rec.attribute18,
650 attribute19 = p_results_rec.attribute19,
651 attribute20 = p_results_rec.attribute20,
652 attribute21 = p_results_rec.attribute21,
653 attribute22 = p_results_rec.attribute22,
654 attribute23 = p_results_rec.attribute23,
655 attribute24 = p_results_rec.attribute24,
656 attribute25 = p_results_rec.attribute25,
657 attribute26 = p_results_rec.attribute26,
658 attribute27 = p_results_rec.attribute27,
659 attribute28 = p_results_rec.attribute28,
660 attribute29 = p_results_rec.attribute29,
661 attribute30 = p_results_rec.attribute30
662 WHERE result_id = l_results_row.result_id;
663
664 l_results_row_in.result_id := l_results_row.result_id;
665
666
667 -- M. Grosser 20-Apr-2006: Bug 5141976 : FP of bug 5123379
668 -- If the result value is NULL for an expression,
669 -- set the result date and tester id to NULL.
670 --
671 IF l_tests_rec.test_type = 'E' THEN
672 UPDATE gmd_results
673 SET result_date = NULL ,
674 tester_id = NULL
675 WHERE result_id = l_results_row.result_id
676 AND result_value_num IS NULL;
677 END IF;
678 -- M. Grosser 20-Apr-2006: Bug 5141976 : End of changes
679
680
681 IF NOT GMD_RESULTS_PVT.FETCH_ROW( p_results => l_results_row_in,
682 x_results => l_results_row ) THEN
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686 END IF;
687
688 -- bug: 14239488 plowe added the below code to calculate expression
689 IF l_tests_rec.test_type <> 'E' THEN
690
691 OPEN c_get_event_spec(l_samples_rec.sampling_event_id,
692 l_samples_rec.sample_id);
693 FETCH c_get_event_spec INTO l_event_spec_disp_id, l_sample_disp, l_spec_id,
694 l_samples_req, l_samples_act;
695 IF c_get_event_spec%NOTFOUND THEN
696 GMD_API_PUB.Log_Message('GMD_NO_SPEC_EVENT_FOUND',
697 'SPEC_ID', l_spec_id,
698 'SAMP_EVENT', l_samples_rec.sampling_event_id);
699 RAISE FND_API.G_EXC_ERROR;
700 CLOSE c_get_event_spec;
701 END IF;
702 CLOSE c_get_event_spec;
703
704 gmd_results_grp.calc_expression
705 ( p_sample_id => l_samples_rec.sample_id --:gmdqsmpl.sample_id
706 , p_event_spec_disp_id => l_event_spec_disp_id --:gmdqsmpl.event_spec_disp_id
707 , p_spec_id => l_spec_id --:gmdqsmpl.spec_id
708 , x_rslt_tbl => l_rslt_tbl
709 , x_return_status => l_return_status);
710 END IF;
711 -- 14239488 end
712
713
714
715
716 --=================================
717 -- Update Remaining Qty on Sample Record
718 --=================================
719 IF ( NVL(p_results_rec.reserve_sample_id, 0) <> 0 ) THEN
720 -- Bug 3468300: added API/FORM field and test uom to update remaining qty call
721 -- B3600012 ST: RECORD RESULTS API ISSUES AMBIGUOUS ERROR
722 gmd_samples_grp.update_remaining_qty
723 (l_results_row.result_id, -- B3600012 changed from l_results_rec
724 p_results_rec.reserve_sample_id ,
725 l_results_row.consumed_qty ,
726 l_return_status
727 );
728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732 ELSE
733 gmd_samples_grp.update_remaining_qty
734 (l_results_row.result_id, -- B3600012 changed from l_results_rec
735 l_results_row.sample_id ,
736 l_results_row.consumed_qty ,
737 l_return_status
738 );
739 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
740 RAISE FND_API.G_EXC_ERROR;
741 END IF;
742 END IF;
743
744
745 --=================================
746 -- Now Validate the Evaluation_ind
747 -- If user has specified a value
748 --=================================
749
750 IF p_results_rec.eval_ind IS NOT NULL THEN
751
752 GMD_RESULTS_GRP.validate_evaluation_ind
753 ( p_evaluation_ind => p_results_rec.eval_ind,
754 p_in_spec_ind => l_validate_res.in_spec,
755 p_result_value => p_results_rec.result_value,
756 x_return_status => l_return_status
757 );
758
759 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
760 RAISE FND_API.G_EXC_ERROR;
761 END IF;
762
763 END IF;
764
765
766 --=================================
767 -- Now Update the Spec Results
768 -- Only if there is a Valid Evaluation
769 --=================================
770 /* Bug # 3848483 Update the spec results even if eval_ind is NULL - Commenting the condition */
771 -- IF NVL(l_validate_res.evaluation_ind, p_results_rec.eval_ind) is NOT NULL THEN
772
773
774 IF NOT GMD_SPEC_RESULTS_PVT.lock_row
775 ( p_event_spec_disp_id => l_event_spec_disp_id,
776 p_result_id => l_results_row.result_id
777 ) THEN
778 RAISE FND_API.G_EXC_ERROR;
779
780 END IF;
781
782 IF l_tests_rec.test_type <> 'E' THEN --RLNAGARA BUG#5076736
783 -- 14635099 start
784 IF p_results_rec.action_code is NULL and nvl(l_validate_res.in_spec,'N') <> 'Y' then
785
786 OPEN c_spec_test(l_validate_res.spec_id, l_validate_res.test_id );
787 FETCH c_spec_test INTO l_validate_res.result_action_code;
788 CLOSE c_spec_test;
789 END IF; -- IF l_validate_res.in_spec <> 'Y' and p_results_rec.action_code is NULL then
790
791 -- if user passes Accept with variance (1V), Reject (2R) , Cancel (4C) , Void (50) then this tales precedence over everything even if results is in spec -- pal
792 IF p_results_rec.eval_ind in ('1V','2R','4C','5O') then
793 l_validate_res.evaluation_ind := p_results_rec.eval_ind;
794 END IF; -- IF p_results_rec.eval_ind = '4C' then
795
796 /*IF p_results_rec.eval_ind is NULL then
797 null;
798
799 -- l_validate_res.evaluation_ind := NULL;
800 END IF; --IF p_results_rec.eval_ind is NULL then
801 */
802
803 IF ( NVL(p_results_rec.action_code, l_validate_res.result_action_code) = l_retest_action_code) and l_retest_action_code is not null THEN -- 14635099
804
805 l_sample.sample_id := l_samples_rec.sample_id;
806 test_ids(1) := l_tests_rec.test_id;
807
808
809 gmd_results_grp.add_tests_to_sample
810 (p_sample => l_sample
811 ,p_test_ids => test_ids
812 ,p_event_spec_disp_id => l_event_spec_disp_id
813 ,x_results_tab => add_rslt_tab_out
814 ,x_spec_results_tab => add_spec_tab_out
815 ,x_return_status => l_return_status
816 ,p_test_qty => l_results_row.test_qty
817 ,p_test_qty_uom => l_results_row.test_qty_uom
818 ) ;
819
820 IF l_return_status <> 'S' THEN
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824 END IF; -- IF ( NVL(p_results_rec.action_code, l_validate_res.result_action_code) = l_retest_action_code) THEN 14635099
825
826
827 -- 14635099 end
828
829
830
831
832 UPDATE GMD_SPEC_RESULTS
833 SET IN_SPEC_IND = l_validate_res.in_spec,
834 evaluation_ind = l_validate_res.evaluation_ind,
835 -- NVL(l_validate_res.evaluation_ind,p_results_rec.eval_ind), -- pal CT wants to be able to pass null as in the UI BUT wants system to update /* Bug # 3848483 Swapped args. l_validate_res takes precedence BUT wait - see above */
836 action_code = NVL(p_results_rec.action_code,
837 l_validate_res.result_action_code), -- 14635099 l_validate_res.result_action_code was not being automatically filled in.
838 last_update_date = l_date,
839 last_updated_by = l_user_id,
840 --value_in_report_precision = ROUND(p_results_rec.result_value,l_validate_res.report_precision)/* Bug # 3848483 Added this line */
841 value_in_report_precision = -- replaced above line with this -- bug 7524393
842 DECODE(l_tests_rec.test_type, 'T', NULL, 'V', NULL, 'U', NULL,
843 ROUND(p_results_rec.result_value,l_validate_res.report_precision))
844 WHERE event_spec_disp_id = l_event_spec_disp_id
845 AND result_id = l_results_row.result_id;
846 END IF;
847
848 -- Populate OUT parameter.
849
850 l_spec_results_row_in.event_spec_disp_id := l_event_spec_disp_id;
851 l_spec_results_row_in.result_id := l_results_row.result_id;
852
853 IF NOT GMD_SPEC_RESULTS_PVT.fetch_row
854 ( p_spec_results => l_spec_results_row_in,
855 x_spec_results => l_spec_results_row
856 ) THEN
857
858 RAISE FND_API.G_EXC_ERROR;
859
860 END IF;
861
862 -- END IF; /*Bug # 3848483 Commented if eval_ind is NULL */
863
864
865 --=================================
866 -- Now Attempt to Update the
867 -- Sample/ Samp Event Dispositions
868 --=================================
869
870 -- 14635099 added check for Cancel - if cancel then do not change disposition
871
872
873 IF p_results_rec.eval_ind = '4C' then -- 14635099
874 null;
875 ELSE
876
877 GMD_RESULTS_GRP.change_sample_disposition
878 ( p_sample_id => l_samples_rec.sample_id,
879 x_change_disp_to => l_sample_disp,
880 x_return_status => l_return_status,
881 x_message_data => x_msg_data
882 );
883
884 IF l_return_status <> 'S' THEN
885 RAISE FND_API.G_EXC_ERROR;
886 END IF;
887
888 END IF; -- IF p_results_rec.eval_ind <> '4C' then -- 14635099
889
890
891 -- commenting out the below code for bug 13566337 as form does not behave like this
892 --=================================
893 -- Now determine if we can change
894 -- the Dispostion to ''4A','5AV', '6RJ'
895 -- And in turn set the Lot Status
896 --=================================
897
898 /* IF l_spec_id IS NOT NULL AND l_sample_disp = '3C' THEN
899
900 -- There was only 1 sample Req for this Sampling Event
901 -- SHOULD THIS BE = 1 OR AS LONG AS THEY EQUAL EACH OTHER !!!!!!!
902 IF l_samples_req = 1 and l_samples_act = 1 THEN
903
904 GMD_RESULTS_GRP.change_disp_for_auto_lot
905 ( p_sample_id => l_samples_rec.sample_id,
906 x_change_disp_to => l_sample_disp,
907 x_return_status => l_return_status
908 );
909
910 IF l_return_status <> 'S' THEN
911 RAISE FND_API.G_EXC_ERROR;
912 END IF;
913
914 END IF;
915 END IF; */
916
917 --==================================================
918 -- Now determine if we can create a composite result
919 -- There is no need to create a composite result if samples_act count <= 1
920 --==================================================
921 OPEN c_sample_event ( l_event_spec_disp_id);
922 FETCH c_sample_event
923 INTO l_sample_event_disp;
924
925 IF (c_sample_event%NOTFOUND)
926 THEN
927 CLOSE c_sample_event;
928 GMD_API_PUB.Log_Message('GMD_NO_SPEC_EVENT_FOUND',
929 'EVENT_SPEC_DISP_ID', l_event_spec_disp_id);
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932 CLOSE c_sample_event;
933
934 -- dbms_output.put_line('spec_id='||l_spec_id||', sample_event_disp='||l_sample_event_disp||', samples_act='||l_samples_act);
935 IF (l_spec_id IS NOT NULL
936 AND l_sample_event_disp = '3C'
937 AND l_samples_act > 1)
938 THEN
939 -- bug 2709353, 9-Apr-2003, odaboval, Create a composite result :
940 -- At this stage, the Sampling Event is completed and
941 -- its sample_cnt > = required_cnt
942 -- Therefore, the composite result is only created when more than 1 sample.
943
944 GMD_RESULTS_GRP.composite_and_change_lot(
945 p_sampling_event_id => l_samples_rec.sampling_event_id
946 , p_commit => p_commit
947 , x_return_status => l_return_status);
948
949 IF l_return_status <> 'S' THEN
950 RAISE FND_API.G_EXC_ERROR;
951 END IF;
952 END IF;
953
954
955
956 -- Standard Check of p_commit.
957 IF FND_API.to_boolean(p_commit)
958 THEN
959 COMMIT WORK;
960 END IF;
961
962 -- Set return Parameters
963
964 x_return_status := l_return_status;
965 x_results_rec := l_results_row;
966 x_spec_results_rec := l_spec_results_row;
967
968
969 EXCEPTION
970 WHEN FND_API.G_EXC_ERROR THEN
971 ROLLBACK TO RECORD_RESULTS;
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
974 , p_count => x_msg_count
975 , p_data => x_msg_data
976 );
977
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
979 ROLLBACK TO RECORD_RESULTS;
980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
981 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
982 , p_count => x_msg_count
983 , p_data => x_msg_data
984 );
985
986
987
988 WHEN OTHERS THEN
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 ROLLBACK TO RECORD_RESULTS;
991 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
992 , l_api_name
993 );
994
995 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
996 , p_count => x_msg_count
997 , p_data => x_msg_data
998 );
999
1000 END RECORD_RESULTS;
1001
1002 PROCEDURE ADD_TESTS_TO_SAMPLE
1003 (
1004 p_api_version IN NUMBER
1005 , p_init_msg_list IN VARCHAR2
1006 , p_commit IN VARCHAR2
1007 , p_validation_level IN NUMBER
1008 , p_user_name IN VARCHAR2
1009 , p_sample_rec IN GMD_SAMPLES%ROWTYPE
1010 , p_test_id_tab IN GMD_API_PUB.number_tab
1011 , p_event_spec_disp_id IN NUMBER
1012 , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
1013 , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
1014 , x_return_status OUT NOCOPY VARCHAR2
1015 , x_msg_count OUT NOCOPY NUMBER
1016 , x_msg_data OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019 l_api_name CONSTANT VARCHAR2 (30) := 'ADD_TESTS_TO_SAMPLE';
1020 l_api_version CONSTANT NUMBER := 2.0;
1021 l_msg_count NUMBER :=0;
1022 l_msg_data VARCHAR2(2000);
1023 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1024 l_results_tab GMD_API_PUB.gmd_results_tab;
1025 l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
1026 l_spec_results_row GMD_SPEC_RESULTS%ROWTYPE;
1027 l_user_id NUMBER(15);
1028 l_date DATE := SYSDATE;
1029 l_event_spec_disp_id NUMBER;
1030 l_event_spec_exists NUMBER;
1031
1032 CURSOR c_check_event_spec ( p_event_spec_disp NUMBER)
1033 IS
1034 SELECT 1
1035 FROM GMD_EVENT_SPEC_DISP
1036 WHERE EVENT_SPEC_DISP_ID = p_event_spec_disp
1037 AND DELETE_MARK = 0;
1038
1039
1040 BEGIN
1041
1042
1043 -- Standard Start OF API savepoint
1044
1045 SAVEPOINT ADD_TESTS_TO_SAMPLE;
1046
1047
1048 /* Standard call to get for call compatibility. */
1049
1050 IF NOT FND_API.Compatible_API_CALL
1051 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1052 THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 /* Initialize message list if p_int_msg_list is set TRUE. */
1057 IF FND_API.to_boolean(p_init_msg_list)
1058 THEN
1059 FND_MSG_PUB.Initialize;
1060 END IF;
1061
1062 -- Initialize API return Parameters
1063
1064 x_return_status := FND_API.G_RET_STS_SUCCESS;
1065 l_results_tab := x_results_tab;
1066 l_spec_results_tab := x_spec_results_tab;
1067
1068 -- Validate User Name Parameter
1069
1070
1071 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
1072 ,x_user_id => l_user_id);
1073
1074 IF NVL(l_user_id, -1) < 0
1075 THEN
1076 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1077 'l_user_name', p_user_name);
1078
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081
1082 -- Validate Input Parematers
1083
1084 -- Validate Sample Record
1085
1086
1087 IF ( p_sample_rec.sample_id is NULL) THEN
1088 -- Validate that composite keys are present
1089
1090 IF ( p_sample_rec.sample_no is NULL) THEN
1091 GMD_API_PUB.Log_Message('GMD_SAMPLE_NUMBER_REQD');
1092 RAISE FND_API.G_EXC_ERROR;
1093 END IF;
1094
1095 IF ( p_sample_rec.orgn_code is NULL) THEN
1096 GMD_API_PUB.Log_Message('GMD_SAMPLE_ORGN_CODE_REQD');
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099
1100 END IF; -- Key Sample values Present
1101
1102
1103 -- Validate that test id's are present
1104
1105 IF ( p_test_id_tab.COUNT < 1 ) THEN
1106 GMD_API_PUB.Log_Message('GMD_TEST_ID_TABLE_EMPTY');
1107 RAISE FND_API.G_EXC_ERROR;
1108 END IF;
1109
1110 -- Validate event spec disp id is present and valid
1111
1112 IF ( p_event_spec_disp_id is NULL) THEN
1113 GMD_API_PUB.Log_Message('GMD_EVENT_SPEC_DISP_NULL');
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116
1117 -- Validate that the event_spec_disp_id is Valid
1118
1119 OPEN c_check_event_spec(p_event_spec_disp_id);
1120 FETCH c_check_event_spec INTO l_event_spec_exists;
1121 IF c_check_event_spec%NOTFOUND THEN
1122 GMD_API_PUB.Log_Message('GMD_EVENT_SPEC_NOTFOUND',
1123 'event_disp', p_event_spec_disp_id);
1124 RAISE FND_API.G_EXC_ERROR;
1125 CLOSE c_check_event_spec;
1126 END IF;
1127 CLOSE c_check_event_spec;
1128
1129
1130 -- Now Start Business Processing
1131
1132 -- Call Grp Layer API to Process Records
1133
1134 GMD_RESULTS_GRP.ADD_TESTS_TO_SAMPLE
1135 ( p_sample => p_sample_rec
1136 , p_test_ids => p_test_id_tab
1137 , p_event_spec_disp_id => p_event_spec_disp_id
1138 , x_results_tab => l_results_tab
1139 , x_spec_results_tab => l_spec_results_tab
1140 , x_return_status => l_return_status);
1141
1142 IF l_return_status <> 'S' THEN
1143 RAISE FND_API.G_EXC_ERROR;
1144 END IF;
1145
1146
1147 -- Standard Check of p_commit.
1148 IF FND_API.to_boolean(p_commit)
1149 THEN
1150 COMMIT WORK;
1151 END IF;
1152
1153 -- Set return Parameters
1154
1155 x_return_status := l_return_status;
1156 x_results_tab := l_results_tab;
1157 x_spec_results_tab := l_spec_results_tab;
1158
1159
1160 EXCEPTION
1161 WHEN FND_API.G_EXC_ERROR THEN
1162 ROLLBACK TO ADD_TESTS_TO_SAMPLE;
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1165 , p_count => x_msg_count
1166 , p_data => x_msg_data
1167 );
1168
1169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1170 ROLLBACK TO ADD_TESTS_TO_SAMPLE;
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1173 , p_count => x_msg_count
1174 , p_data => x_msg_data
1175 );
1176
1177
1178
1179 WHEN OTHERS THEN
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 ROLLBACK TO ADD_TESTS_TO_SAMPLE;
1182 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1183 , l_api_name
1184 );
1185
1186 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1187 , p_count => x_msg_count
1188 , p_data => x_msg_data
1189 );
1190
1191 END ADD_TESTS_TO_SAMPLE;
1192
1193 PROCEDURE VALIDATE_INPUT
1194 ( p_results_rec IN GMD_RESULTS_PUB.RESULTS_REC,
1195 x_return_status OUT NOCOPY VARCHAR2
1196 )
1197 IS
1198 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1199 BEGIN
1200
1201 -- Set out Variable
1202 x_return_status := l_return_status;
1203
1204 -- Validate Results Record For Result Table Key Fields
1205 -- Bug 3763419 ; add Guaranteed by Manufacturer
1206 IF p_results_rec.result_value IS NULL AND
1207 p_results_rec.eval_ind NOT IN ('4C', '5O', '1Z') THEN
1208 GMD_API_PUB.Log_Message('GMD_RESULT_VALUE_REQ');
1209 RAISE FND_API.G_EXC_ERROR;
1210 END IF;
1211
1212 --============================
1213 -- In Spec Value Should either
1214 -- be NULL or 'Y'
1215 --============================
1216 IF ( p_results_rec.in_spec IS NOT NULL AND
1217 UPPER(p_results_rec.in_spec) <> 'Y') THEN
1218 GMD_API_PUB.Log_Message('GMD_INVALID_INSPEC_VALUE');
1219 RAISE FND_API.G_EXC_ERROR;
1220 END IF;
1221
1222 IF p_results_rec.result_id IS NULL THEN
1223
1224 --============================
1225 -- Valdate Sample Definition.
1226 --============================
1227 IF ( p_results_rec.sample_id is NULL) THEN
1228 IF ( p_results_rec.sample_no is NULL) THEN
1229 GMD_API_PUB.Log_Message('GMD_SAMPLE_NUMBER_REQD');
1230 RAISE FND_API.G_EXC_ERROR;
1231 END IF;
1232
1233 IF ( p_results_rec.organization_id is NULL) THEN /*NSRIVAST, INVCONV*/
1234 GMD_API_PUB.Log_Message('GMD_SAMPLE_ORGN_CODE_REQD');
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237 END IF;
1238
1239 --============================
1240 -- Validate test_code
1241 --============================
1242 IF ( p_results_rec.test_id is NULL) THEN
1243 IF ( p_results_rec.test_code is NULL) THEN
1244 GMD_API_PUB.Log_Message('GMD_TEST_ID_CODE_NULL');
1245 RAISE FND_API.G_EXC_ERROR;
1246 END IF;
1247 END IF;
1248
1249 --============================
1250 -- Test Replicate Cnt
1251 --============================
1252 IF ( p_results_rec.test_replicate_cnt is NULL) THEN
1253 GMD_API_PUB.Log_Message('GMD_TEST_REP_CNT_REQD');
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 END IF;
1258
1259 EXCEPTION
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 WHEN OTHERS THEN
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1264 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE',
1265 'GMD_RESULTS_PUB.VALIDATE_INPUT',
1266 'ERROR',SUBSTR(SQLERRM,1,100),
1267 'POSITION','010');
1268
1269
1270 END VALIDATE_INPUT;
1271
1272 PROCEDURE GET_RESULT_INFO
1273 ( p_results_rec IN GMD_RESULTS_PUB.RESULTS_REC,
1274 x_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
1275 x_samples_rec OUT NOCOPY GMD_SAMPLES%ROWTYPE,
1276 x_return_status OUT NOCOPY VARCHAR2
1277 )
1278 IS
1279 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1280 l_tests_rec GMD_QC_TESTS%ROWTYPE;
1281 l_samples_rec GMD_SAMPLES%ROWTYPE;
1282
1283 BEGIN
1284
1285 -- Set out Variable
1286 x_return_status := l_return_status;
1287
1288 -- Get Test record for results
1289 l_tests_rec.test_id := p_results_rec.test_id;
1290 l_tests_rec.test_code := p_results_rec.test_code;
1291
1292 IF NOT GMD_QC_TESTS_PVT.fetch_row
1293 ( p_gmd_qc_tests => l_tests_rec,
1294 x_gmd_qc_tests => x_tests_rec
1295 ) THEN
1296 RAISE FND_API.G_EXC_ERROR;
1297 END IF;
1298
1299 -- Get Sample Record
1300 l_samples_rec.sample_id := p_results_rec.sample_id;
1301 l_samples_rec.sample_no := p_results_rec.sample_no;
1302 l_samples_rec.organization_id := p_results_rec.organization_id; /*NSRIVAST, INVCONV*/
1303
1304 IF NOT GMD_SAMPLES_PVT.fetch_row
1305 (
1306 p_samples => l_samples_rec,
1307 x_samples => x_samples_rec
1308 ) THEN
1309 RAISE FND_API.G_EXC_ERROR;
1310 END IF;
1311
1312 IF x_samples_rec.delete_mark = 1 THEN
1313 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
1314 'l_table_name', 'GMD_SAMPLES',
1315 'l_column_name', 'SAMPLE_ID',
1316 'l_key_value', x_samples_rec.sample_id);
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319
1320 EXCEPTION
1321 WHEN FND_API.G_EXC_ERROR THEN
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 WHEN OTHERS THEN
1324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1325 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE',
1326 'GMD_RESULTS_PUB.GET_RESULT_INFO',
1327 'ERROR',SUBSTR(SQLERRM,1,100),
1328 'POSITION','010');
1329
1330 END GET_RESULT_INFO;
1331
1332 END GMD_RESULTS_PUB;
[Home] [Help]