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