[Home] [Help]
PACKAGE BODY: APPS.GMD_RESULT_TRANSFER_GRP
Source
1 PACKAGE BODY gmd_result_transfer_grp AS
2 --$Header: GMDGRSTB.pls 120.2 2006/05/02 23:14:43 rlnagara noship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --+==========================================================================+
7 --| Copyright (c) 1998 Oracle Corporation |
8 --| Redwood Shores, CA, USA |
9 --| All rights reserved. |
10 --+==========================================================================+
11 --| File Name : GMDGRSTB.pls |
12 --| Package Name : gmd_result_transfer_grp |
13 --| Type : Group |
14 --| |
15 --| Notes |
16 --| This package contains group layer APIs for Results Assoc. |
17 --| |
18 --| HISTORY |
19 --| Manish Gupta 19-Aug-2003 Created. |
20 --| |
21 --+==========================================================================+
22 -- End of comments
23
24
25 FUNCTION get_test_code(p_test_id IN NUMBER) RETURN VARCHAR2 IS
26 cursor get_test IS
27 SELECT TEST_CODE
28 FROM gmd_qc_tests_b
29 WHERE test_id = p_test_id;
30
31 l_test_code gmd_qc_tests_b.test_code%TYPE;
32 BEGIN
33 OPEN get_test;
34 FETCH get_test INTO l_test_code;
35 CLOSE get_test;
36 RETURN l_test_code;
37 END get_test_code;
38
39 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
40
41 PROCEDURE populate_transfer(p_child_id IN NUMBER,
42 p_parent_id IN NUMBER,
43 p_transfer_type IN VARCHAR2,
44 x_message_count OUT NOCOPY NUMBER,
45 x_message_data OUT NOCOPY VARCHAR2,
46 x_return_status OUT NOCOPY VARCHAR2) IS
47
48 CURSOR c_get_common_test(p_child_sample_id IN NUMBER,
49 p_parent_sample_id IN NUMBER) IS
50 SELECT distinct r1.seq,
51 r1.test_id,
52 r1.test_method_id
53 FROM gmd_results r1
54 WHERE r1.sample_id = p_child_sample_id
55 AND r1.result_value_char IS NULL
56 AND r1.result_value_num IS NULL
57 AND r1.reserve_sample_id IS NULL
58 and r1.delete_mark = 0
59 AND r1.test_id IN (SELECT distinct r2.test_id
60 FROM gmd_results r2,
61 gmd_spec_results sr
62 WHERE r2.sample_id = p_parent_sample_id
63 AND r2.result_id = sr.result_id
64 AND r2.test_method_id = r1.test_method_id
65 AND r2.delete_mark = 0
66 AND sr.evaluation_ind IN ('0A','1V','2R','3E'))
67 ORDER BY r1.seq; --RLNAGARA bug5197746 added the ORDER BY clause
68
69
70 CURSOR c_get_child_test(p_child_sample_id IN NUMBER,
71 p_test_id IN NUMBER,
72 p_test_method_id IN NUMBER) IS
73 SELECT r.result_id,
74 r.TEST_REPLICATE_CNT
75 FROM gmd_results r
76 WHERE r.sample_id = p_child_sample_id
77 AND r.test_id = p_test_id
78 AND r.test_method_id = p_test_method_id
79 AND r.result_value_char IS NULL
80 AND r.result_value_num IS NULL
81 AND r.reserve_sample_id IS NULL
82 AND r.delete_mark = 0;
83
84 CURSOR c_get_parent_test(p_parent_sample_id IN NUMBER,
85 p_test_id IN NUMBER,
86 p_test_method_id IN NUMBER) IS
87 SELECT r.result_id,
88 r.test_replicate_cnt,
89 r.result_value_char,
90 r.result_value_num,
91 r.result_date,
92 r.test_method_id
93 FROM gmd_results r,
94 gmd_spec_results sr,
95 gmd_sample_spec_disp ssd,
96 gmd_event_spec_disp esd
97 WHERE r.sample_id = p_parent_sample_id
98 AND r.test_id = p_test_id
99 AND r.test_method_id = p_test_method_id
100 AND r.result_id = sr.result_id
101 AND sr.evaluation_ind IN ('0A','1V','2R','3E')
102 AND sr.event_spec_disp_id = ssd.event_spec_disp_id
103 AND r.sample_id = ssd.sample_id
104 AND ssd.event_spec_disp_id = esd.event_spec_disp_id
105 AND esd.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
106 AND r.delete_mark = 0
107 ORDER by r.result_date desc;
108
109 CURSOR c_get_composite_test(p_child_id IN NUMBER,
110 p_parent_id IN NUMBER) IS
111 select r.composite_result_id,
112 r.test_id test_id,
113 nvl(to_char(r.mean),r.mode_char) result,
114 r.mean,
115 r.mode_num,
116 r.mode_char,
117 r.low_num,
118 r.high_num,
119 r.range,
120 r.non_validated_result,
121 r.standard_deviation , ges.spec_id,
122 r.creation_date
123 from gmd_composite_results r,
124 gmd_composite_spec_disp sd,
125 gmd_event_spec_disp ges
126 where r.composite_spec_disp_id =sd.composite_spec_disp_id
127 --and sd.event_spec_disp_id = 1
128 and sd.event_spec_disp_id = ges.event_spec_disp_id
129 and nvl(ges.spec_used_for_lot_attrib_ind,'N') = 'Y'
130 and nvl(sd.latest_ind,'N') = 'Y'
131 and ges.sampling_event_id = p_parent_id
132 and r.test_id in (select r1.test_id
133 from gmd_composite_results r1,
134 gmd_composite_spec_disp gcs,
135 gmd_event_spec_disp ges
136 where ges.event_spec_disp_id = gcs.event_spec_disp_id
137 and gcs.latest_ind = 'Y'
138 and gcs.composite_spec_disp_id = r1.composite_spec_disp_id
139 and ges.sampling_event_id = p_child_id
140 and r1.mean IS NULL AND r1.mode_char IS NULL);
141
142 Cursor c_get_child_composite(p_sampling_event_id IN NUMBER,
143 p_test_id IN NUMBER) IS
144 select r1.composite_result_id
145 from gmd_composite_results r1,
146 gmd_composite_spec_disp gcs,
147 gmd_event_spec_disp ges
148 where ges.event_spec_disp_id = gcs.event_spec_disp_id
149 and gcs.latest_ind = 'Y'
150 and gcs.composite_spec_disp_id = r1.composite_spec_disp_id
151 and ges.sampling_event_id = p_sampling_event_id
152 and r1.test_id = p_test_id
153 and r1.mean IS NULL AND r1.mode_char IS NULL;
154
155 l_seq NUMBER; --RLNAGARA Bug5197746
156 l_test_id NUMBER;
157 l_test_method_id NUMBER;
158 l_prev_used VARCHAR2(1);
159 c_get_child_sample_row c_get_child_test%ROWTYPE;
160 c_get_parent_sample_row c_get_parent_test%ROWTYPE;
161 c_get_composite_test_row c_get_composite_test%ROWTYPE;
162 l_common_test_count NUMBER:=0;
163 l_child_composite_result_id NUMBER;
164 l_place NUMBER;
165 l_test_code gmd_qc_tests_b.test_code%TYPE;
166
167
168
169 BEGIN
170 x_return_status :=FND_API.G_RET_STS_SUCCESS;
171 FND_MSG_PUB.initialize;
172 --GMD_API_PUB.Log_Message('Deleting gmd_result_transfer_gt');
173 l_place := 0;
174 DELETE gmd_result_transfer_gt;
175 l_place := 10;
176 IF (p_transfer_type = 'S') THEN
177 l_place := 20;
178
179 IF (l_debug = 'Y') THEN
180 GMD_debug.put_line('Entering populate transfer for samples');
181 END IF;
182 OPEN c_get_common_test(p_child_id,
183 p_parent_id);
184
185 l_place := 30;
186 LOOP
187 FETCH c_get_common_test INTO l_seq,l_test_id,l_test_method_id; --RLNAGARA Bug5197746 Added l_seq
188
189 IF (l_debug = 'Y') THEN
190 GMD_debug.put_line('Common Test Id, test_method_id Is '||l_test_id||' '||l_test_method_id);
191 END IF;
192
193 l_place := 40;
194 IF (c_get_common_test%NOTFOUND) THEN
195 CLOSE c_get_common_test;
196 IF (l_common_test_count = 0) THEN
197 x_return_status := 'N'; --No common test
198 END IF;
199 EXIT;
200 END IF;
201 l_place := 50;
202
203 OPEN c_get_child_test (p_child_id,
204 l_test_id,
205 l_test_method_id);
206 OPEN c_get_parent_test(p_parent_id,
207 l_test_id,
208 l_test_method_id);
209 l_place := 60;
210 LOOP
211 FETCH c_get_child_test INTO c_get_child_sample_row;
212 --lock the child row
213 IF (l_debug = 'Y') THEN
214 GMD_debug.put_line('Child result id, replicate_cnt is '||c_get_child_sample_row.result_id||' '||c_get_child_sample_row.test_replicate_cnt);
215 END IF;
216
217
218 l_place := 70;
219 FETCH c_get_parent_test INTO c_get_parent_sample_row;
220 IF (c_get_child_test%NOTFOUND OR c_get_parent_test%NOTFOUND) THEN
221 CLOSE c_get_child_test;
222 CLOSE c_get_parent_test;
223 EXIT;
224 END IF;
225 l_place := 80;
226 --This parent_result_id should not be used previously for the same sample
227 BEGIN
228 SELECT 'Y'
229 INTO l_prev_used
230 FROM gmd_results
231 WHERE sample_id = p_child_id
232 and parent_result_id = c_get_parent_sample_row.result_id
233 AND rownum =1;
234 EXCEPTION
235 WHEN no_data_found THEN
236 l_prev_used := 'N';
237 END;
238
239 IF (l_debug = 'Y') THEN
240 gmd_debug.put_line('The value of the prev used parameter is '||l_prev_used);
241 END IF;
242
243 l_place := 85;
244 --insert statement for temp table
245 IF (l_prev_used = 'N') THEN
246
247 l_common_test_count := l_common_test_count +1;
248
249 IF NOT GMD_RESULTS_PVT.LOCK_ROW ( p_result_id => c_get_child_sample_row.result_id ) THEN
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252 l_test_code := get_test_code(l_test_id);
253
254 INSERT INTO gmd_result_transfer_gt
255 (parent_result_id,
256 child_result_id,
257 test_code,
258 result,
259 result_date,
260 parent_replicate,
261 child_replicate)
262 VALUES(c_get_parent_sample_row.result_id,
263 c_get_child_sample_row.result_id,
264 l_test_code,
265 nvl(c_get_parent_sample_row.result_value_char,c_get_parent_sample_row.result_value_num),
266 c_get_parent_sample_row.result_date,
267 c_get_parent_sample_row.test_replicate_cnt,
268 c_get_child_sample_row.test_replicate_cnt);
269 END IF;
270 END LOOP;
271 END LOOP;
272 l_place := 90;
273 ELSE
274 OPEN c_get_composite_test(p_child_id,
275 p_parent_id);
276 l_place := 100;
277 LOOP
278 FETCH c_get_composite_test INTO c_get_composite_test_row;
279 l_place := 110;
280 IF (c_get_composite_test%NOTFOUND) THEN
281 CLOSE c_get_composite_test;
282 IF (l_common_test_count = 0) THEN
283 x_return_status := 'N'; --No common test
284 END IF;
285 EXIT;
286 END IF;
287 l_place := 120;
288 l_common_test_count := l_common_test_count +1;
289 OPEN c_get_child_composite(p_child_id,
290 c_get_composite_test_row.test_id);
291 FETCH c_get_child_composite INTO l_child_composite_result_id;
292 CLOSE c_get_child_composite;
293 l_place := 130;
294 --INSERT Statement
295 l_test_code := get_test_code(c_get_composite_test_row.test_id);
296 INSERT INTO gmd_result_transfer_gt
297 (parent_result_id,
298 child_result_id,
299 test_code,
300 result,
301 result_date,
302 parent_replicate,
303 child_replicate)
304 VALUES(c_get_composite_test_row.composite_result_id,
305 l_child_composite_result_id,
306 l_test_code,
307 c_get_composite_test_row.result,
308 c_get_composite_test_row.creation_date,
309 NULL,
310 NULL);
311
312 END LOOP;
313 l_place := 140;
314 END IF;
315 l_place := 150;
316 FND_MSG_PUB.Count_AND_GET
317 (p_count => x_message_count, p_data => x_message_data);
318
319 EXCEPTION
320
321 WHEN OTHERS THEN
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 log_msg('GMD_QC_RESULT_TRANSFER_GRP.POPULATE_TRANSFER AT '||l_place||' '|| SUBSTR(SQLERRM,1,100));
324 FND_MSG_PUB.Count_AND_GET
325 (p_count => x_message_count, p_data => x_message_data);
326
327 END populate_transfer;
328
329 PROCEDURE do_transfer(p_transfer_type IN VARCHAR2,
330 p_copy_edit_text IN VARCHAR2,
331 p_copy_flex_field IN VARCHAR2,
332 p_copy_attachment IN VARCHAR2,
333 p_sampling_event_id IN NUMBER,
334 p_sample_id IN NUMBER,
335 x_sample_disp OUT NOCOPY VARCHAR2,
336 x_message_count OUT NOCOPY NUMBER,
337 x_message_data OUT NOCOPY VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2) IS
339 CURSOR c_temp_table_rslt IS
340 SELECT a.parent_result_id,
341 a.child_result_id,
342 b.result_value_char,
343 b.result_value_num,
344 nvl(b.result_value_char,b.result_value_num) result,
345 b.result_date,
346 a.child_replicate,
347 b.lab_organization_id,
348 b.tester,
349 b.tester_id,
350 b.test_id,
351 b.text_code,
352 b.ATTRIBUTE_CATEGORY,
353 b.ATTRIBUTE1,
354 b.ATTRIBUTE2,
355 b.ATTRIBUTE3,
356 b.ATTRIBUTE4,
357 b.ATTRIBUTE5,
358 b.ATTRIBUTE6,
359 b.ATTRIBUTE7,
360 b.ATTRIBUTE8,
361 b.ATTRIBUTE9,
362 b.ATTRIBUTE10,
363 b.ATTRIBUTE11,
364 b.ATTRIBUTE12,
365 b.ATTRIBUTE13,
366 b.ATTRIBUTE14,
367 b.ATTRIBUTE15,
368 b.ATTRIBUTE16,
369 b.ATTRIBUTE17,
370 b.ATTRIBUTE18,
371 b.ATTRIBUTE19,
372 b.ATTRIBUTE20,
373 b.ATTRIBUTE21,
374 b.ATTRIBUTE22,
375 b.ATTRIBUTE23,
376 b.ATTRIBUTE24,
377 b.ATTRIBUTE25,
378 b.ATTRIBUTE26,
379 b.ATTRIBUTE27,
380 b.ATTRIBUTE28,
381 b.ATTRIBUTE29,
382 b.ATTRIBUTE30
383 FROM GMD_RESULT_TRANSFER_GT A,
384 gmd_results b
385 WHERE b.result_id = a.parent_result_id
386 ;
387
388 CURSOR Cur_get_test(p_test_id IN NUMBER) IS
389 SELECT test_code, test_unit,b.qcunit_desc,
390 test_class, test_type,
391 min_value_num, max_value_num, test_desc,
392 exp_error_type, below_spec_min, above_spec_min,
393 below_spec_max, above_spec_max,
394 below_min_action_code, above_min_action_code,
395 below_max_action_code, above_max_action_code,
396 priority,
397 t.display_precision, t.report_precision,
398 t.expression, tm.resources
399 FROM gmd_qc_tests t,gmd_units b, gmd_test_methods_b tm
400 WHERE t.test_id = p_test_id
401 AND t.test_method_id = tm.test_method_id
402 AND t.test_unit = b.qcunit_code (+) ;
403
404 l_get_test_row Cur_get_test%ROWTYPE;
405
406 --rboddu modified the following cursor to select median_num, median_char bug 3571258
407 CURSOR c_temp_table_cmpt IS
408 select a.parent_result_id,
409 a.child_result_id,
410 r.test_id,
411 nvl(to_char(r.mean),r.mode_char) result,
412 r.mean,
413 r.mode_num,
414 r.mode_char,
415 r.median_num,
416 r.median_char,
417 r.low_num,
418 r.high_num,
419 r.range,
420 r.non_validated_result,
421 r.standard_deviation,
422 r.text_code,
423 r.ATTRIBUTE_CATEGORY,
424 r.ATTRIBUTE1,
425 r.ATTRIBUTE2,
426 r.ATTRIBUTE3,
427 r.ATTRIBUTE4,
428 r.ATTRIBUTE5,
429 r.ATTRIBUTE6,
430 r.ATTRIBUTE7,
431 r.ATTRIBUTE8,
432 r.ATTRIBUTE9,
433 r.ATTRIBUTE10,
434 r.ATTRIBUTE11,
435 r.ATTRIBUTE12,
436 r.ATTRIBUTE13,
437 r.ATTRIBUTE14,
438 r.ATTRIBUTE15,
439 r.ATTRIBUTE16,
440 r.ATTRIBUTE17,
441 r.ATTRIBUTE18,
442 r.ATTRIBUTE19,
443 r.ATTRIBUTE20,
444 r.ATTRIBUTE21,
445 r.ATTRIBUTE22,
446 r.ATTRIBUTE23,
447 r.ATTRIBUTE24,
448 r.ATTRIBUTE25,
449 r.ATTRIBUTE26,
450 r.ATTRIBUTE27,
451 r.ATTRIBUTE28,
452 r.ATTRIBUTE29,
453 r.ATTRIBUTE30
454 FROM gmd_composite_results r,
455 GMD_RESULT_TRANSFER_GT A
456 WHERE r.composite_result_id = a.parent_result_id;
457
458
459 cursor c_action_code (p_sample_id IN NUMBER) IS
460 select retest_action_code
461 , resample_action_code
462 from gmd_quality_config
463 where organization_id = (select organization_id
464 from gmd_samples
465 where sample_id =p_sample_id)
466 order by orgn_code;
467 l_action_code c_action_code%ROWTYPE;
468 l_sample gmd_samples%rowtype;
469 test_ids gmd_api_pub.number_tab;
470 add_rslt_tab_out gmd_api_pub.gmd_results_tab;
471 add_spec_tab_out gmd_api_pub.gmd_spec_results_tab;
472 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
473 l_lot_number mtl_lot_numbers.lot_number%TYPE;
474 l_update_instance_id NUMBER;
475
476 l_validate_res gmd_results_grp.result_data;
477 l_message_data VARCHAR2(100);
478
479 l_return_status VARCHAR2(100);
480 -- l_sample_disp VARCHAR2(3);
481 l_spec_id NUMBER;
482 l_event_spec_disp_id NUMBER;
483 l_spec_tests_in GMD_SPEC_TESTS%ROWTYPE;
484 l_spec_tests GMD_SPEC_TESTS%ROWTYPE;
485 l_tests_rec_in GMD_QC_TESTS%ROWTYPE;
486 l_tests_rec GMD_QC_TESTS%ROWTYPE;
487 l_test_qty GMD_RESULTS.TEST_QTY%TYPE;
488 l_test_qty_uom GMD_RESULTS.TEST_QTY_UOM%TYPE;
489
490 l_in_spec VARCHAR2(1);
491 l_place NUMBER:=0;
492 l_msg VARCHAR2(150);
493 p_copy_flex VARCHAR2(1);
494 l_rslt_tbl gmd_results_grp.rslt_tbl;
495 l_composite_flag VARCHAR2(1) := 'N';
496 l_sample_active_cnt NUMBER;
497
498
499 l_rslt_tbl_expression gmd_results_grp.rslt_tbl;
500 l_return_status_expression VARCHAR2(10);
501
502
503 BEGIN
504 x_return_status :=FND_API.G_RET_STS_SUCCESS;
505 FND_MSG_PUB.initialize;
506 BEGIN
507 SELECT spec_id,
508 event_spec_disp_id
509 INTO l_spec_id,
510 l_event_spec_disp_id
511 FROM gmd_event_spec_disp
512 WHERE sampling_event_id = p_sampling_event_id
513 AND spec_used_for_lot_attrib_ind = 'Y';
514 l_place := 1;
515
516 IF (l_debug = 'Y') THEN
517 gmd_debug.put_line('The value of spec_id and event_spec_disp_id is '||l_spec_id||' '||l_event_spec_disp_id);
518 END IF;
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 FND_MESSAGE.SET_NAME('GMD','GMD_NO_SAMPLING_EVENT');
522 FND_MSG_PUB.ADD;
523 END;
524
525
526
527 l_place := 5;
528 IF (p_transfer_type = 'S') THEN
529 l_place := 7;
530 --Take update instance id for updating all the result rows.
531 select GMD_QC_UPDATE_INST_ID_S.NEXTVAL
532 into l_update_instance_id
533 from dual;
534
535 l_place := 10;
536 -- Make sure flex field can be copied or not
537 IF (fnd_flex_apis.IS_DESCR_SETUP(552,'GMD_QC_RESULTS_FLEX')
538 and p_copy_flex_field = 'Y') THEN
539 p_copy_flex := 'Y';
540 END IF;
541
542 IF (l_debug = 'Y') THEN
543 gmd_debug.put_line('Copy flex field flag is '||p_copy_flex);
544 END IF;
545 FOR temp_table_rslt_row in c_temp_table_rslt LOOP
546 -- Get Test record for results
547 l_tests_rec_in.test_id := temp_table_rslt_row.test_id;
548 l_place := 15;
549 IF NOT GMD_QC_TESTS_PVT.fetch_row
550 ( p_gmd_qc_tests => l_tests_rec_in,
551 x_gmd_qc_tests => l_tests_rec) THEN
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554
555 IF (l_debug = 'Y') THEN
556 gmd_debug.put_line('Test_id, Test_code, test_type To be copied is '||l_tests_rec.test_id||' '||l_tests_rec.test_code||' '||l_tests_rec.test_type);
557 END IF;
558 --set up param_list
559 l_place := 20;
560 IF (l_spec_id IS NOT NULL) THEN
561 l_spec_tests_in.test_id := temp_table_rslt_row.test_id;
562 l_spec_tests_in.spec_id := l_spec_id;
563
564 l_place := 25;
565
566 IF NOT GMD_SPEC_TESTS_PVT.fetch_row
567 ( p_spec_tests => l_spec_tests_in,
568 x_spec_tests => l_spec_tests) THEN
569 -- Assume that this is an additional test
570 -- For this sample.
571 l_validate_res.additional_test_ind := 'Y';
572 END IF;
573 END IF;
574 l_place := 30;
575 IF (l_debug = 'Y') THEN
576 gmd_debug.put_line('The value of additional_test_ind, result is '||l_validate_res.additional_test_ind||' '||temp_table_rslt_row.result);
577 END IF;
578
579 IF (l_validate_res.additional_test_ind IS NULL) THEN
580
581 l_validate_res.spec_id := l_spec_tests.spec_id;
582 l_validate_res.test_id := l_tests_rec.test_id;
583 l_validate_res.result := temp_table_rslt_row.result;
584 l_validate_res.test_type := l_tests_rec.test_type;
585 --l_validate_res.min_num := l_spec_tests.min_value_num;
586 --l_validate_res.max_num := l_spec_tests.max_value_num;
587
588 l_validate_res.spec_min_num := l_spec_tests.min_value_num;
589 l_validate_res.spec_max_num := l_spec_tests.max_value_num;
590 l_validate_res.min_num := l_tests_rec.min_value_num;
591 l_validate_res.max_num := l_tests_rec.max_value_num;
592 l_validate_res.spec_target_char := l_spec_tests.target_value_char;
593 l_validate_res.report_precision := NVL(NVL(l_spec_tests.report_precision,
594 l_tests_rec.report_precision),0);
595 l_validate_res.display_precision := NVL(NVL(l_spec_tests.display_precision,
596 l_tests_rec.display_precision),0);
597
598 l_validate_res.exp_error_type := l_spec_tests.exp_error_type;
599 l_validate_res.below_spec_min := l_spec_tests.below_spec_min;
600 l_validate_res.above_spec_min := l_spec_tests.above_spec_min;
601 l_validate_res.below_spec_max := l_spec_tests.below_spec_max;
602 l_validate_res.above_spec_max := l_spec_tests.above_spec_max;
603 l_validate_res.below_min_action_code := l_spec_tests.below_min_action_code;
604 l_validate_res.above_min_action_code := l_spec_tests.above_min_action_code;
605 l_validate_res.below_max_action_code := l_spec_tests.below_max_action_code;
606 l_validate_res.above_max_action_code := l_spec_tests.above_max_action_code;
607 l_validate_res.out_action_code := l_spec_tests.out_of_spec_action;
608 IF (l_debug = 'Y') THEN
609 gmd_debug.put_line('The value of Exp Error Typ, action code is '||l_validate_res.exp_error_type||' '||l_validate_res.out_action_code);
610 END IF;
611 ELSE
612 OPEN cur_get_test(temp_table_rslt_row.test_id);
613 FETCH cur_get_test INTO l_get_test_row;
614 CLOSE cur_get_test;
615 l_validate_res.spec_id := l_spec_tests.spec_id;
616 l_validate_res.test_id := l_tests_rec.test_id;
617 l_validate_res.result := temp_table_rslt_row.result;
618 l_validate_res.test_type := l_tests_rec.test_type;
619 l_validate_res.min_num := l_get_test_row.min_value_num;
620 l_validate_res.max_num := l_get_test_row.max_value_num;
621
622 l_validate_res.report_precision := NVL(NVL(l_spec_tests.report_precision,
623 l_tests_rec.report_precision),0);
624 l_validate_res.display_precision := NVL(NVL(l_spec_tests.display_precision,
625 l_tests_rec.display_precision),0);
626
627 l_validate_res.exp_error_type := l_get_test_row.exp_error_type;
628 l_validate_res.below_spec_min := l_get_test_row.below_spec_min;
629 l_validate_res.above_spec_min := l_get_test_row.above_spec_min;
630 l_validate_res.below_spec_max := l_get_test_row.below_spec_max;
631 l_validate_res.above_spec_max := l_get_test_row.above_spec_max;
632 l_validate_res.below_min_action_code := l_get_test_row.below_min_action_code;
633 l_validate_res.above_min_action_code := l_get_test_row.above_min_action_code;
634 l_validate_res.below_max_action_code := l_get_test_row.below_max_action_code;
635 l_validate_res.above_max_action_code := l_get_test_row.above_max_action_code;
636 -- l_validate_res.out_action_code := l_get_test_row.out_of_spec_action;
637
638 END IF;
639
640
641
642
643 -- gmd_results_grp.validate_result(l_validate_res,
644 -- l_return_status);
645 GMD_RESULTS_GRP.validate_result
646 ( p_result_rec => l_validate_res,
647 x_return_status => l_return_status
648 );
649 l_place := 35;
650 IF l_return_status<>'S' THEN
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653
654 IF (l_debug = 'Y') THEN
655 gmd_debug.put_line('Evaluation Ind In Spec');
656 gmd_debug.put_line(l_validate_res.evaluation_ind||' '||l_validate_res.in_spec);
657 gmd_debug.put_line('p_copy_edit_text, p_copy_flex, p_copy_attachment '||p_copy_edit_text||' '||p_copy_flex||' '||p_copy_attachment);
658 gmd_debug.put_line('Edit Text Code '||temp_table_rslt_row.text_code);
659 END IF;
660
661
662
663
664 UPDATE gmd_results
665 SET result_value_char = temp_table_rslt_row.result_value_char,
666 result_value_num = temp_table_rslt_row.result_value_num,
667 update_instance_id = l_update_instance_id,
668 result_date = temp_table_rslt_row.result_date,
669 lab_organization_id = temp_table_rslt_row.lab_organization_id,
670 tester_id = temp_table_rslt_row.tester_id,
671 tester = temp_table_rslt_row.tester,
672 parent_result_id = temp_table_rslt_row.parent_result_id,
673 last_update_date = sysdate,
674 last_updated_by = fnd_global.USER_ID,
675 text_code = decode(p_copy_edit_text,'Y',
676 temp_table_rslt_row.text_code,text_code),
677 attribute_category = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute_category, attribute_category),
678 attribute1 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute1, attribute1),
679 attribute2 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute2, attribute2),
680 attribute3 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute3, attribute3),
681 attribute4 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute4, attribute4),
682 attribute5 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute5, attribute5),
683 attribute6 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute6, attribute6),
684 attribute7 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute7, attribute7),
685 attribute8 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute8, attribute8),
686 attribute9 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute9, attribute9),
687 attribute10 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute10, attribute10),
688 attribute11 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute11, attribute11),
689 attribute12 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute12, attribute12),
690 attribute13 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute13, attribute13),
691 attribute14 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute14, attribute14),
692 attribute15 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute15, attribute15),
693 attribute16 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute16, attribute16),
694 attribute17 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute17, attribute17),
695 attribute18 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute18, attribute18),
696 attribute19 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute19, attribute19),
697 attribute20 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute20, attribute20),
698 attribute21 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute21, attribute21),
699 attribute22 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute22, attribute22),
700 attribute23 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute23, attribute23),
701 attribute24 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute24, attribute24),
702 attribute25 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute25, attribute25),
703 attribute26 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute26, attribute26),
704 attribute27 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute27, attribute27),
705 attribute28 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute28, attribute28),
706 attribute29 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute29, attribute29),
707 attribute30 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute30, attribute30)
708 WHERE result_id = temp_table_rslt_row.child_result_id;
709
710
711 --B3356274 Now atleast one result is updated, so need to mark the composite result as invalid...
712 l_composite_flag := 'Y';
713
714 IF (l_validate_res.test_type = 'E') THEN
715 gmd_results_grp.calc_expression
716 ( p_sample_id => p_sample_id
717 , p_event_spec_disp_id => l_event_spec_disp_id
718 , p_spec_id => l_spec_tests.spec_id
719 , x_rslt_tbl => l_rslt_tbl
720 , x_return_status => l_return_status);
721
722 IF l_return_status<>'S' THEN
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725 END IF;
726
727
728 IF (p_copy_attachment = 'Y') THEN
729 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name => 'GMD_RESULTS',
730 X_from_pk1_value => temp_table_rslt_row.parent_result_id,
731 X_to_entity_name => 'GMD_RESULTS',
732 x_to_pk1_value => temp_table_rslt_row.child_result_id);
733 END IF;
734
735
736
737 IF NOT GMD_SPEC_RESULTS_PVT.lock_row
738 ( p_event_spec_disp_id => l_event_spec_disp_id,
739 p_result_id => temp_table_rslt_row.child_result_id
740 ) THEN
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743
744 UPDATE GMD_SPEC_RESULTS
745 SET IN_SPEC_IND = l_validate_res.in_spec,
746 evaluation_ind = l_validate_res.evaluation_ind,
747 action_code = l_validate_res.result_action_code,
748 last_update_date = SYSDATE,
749 last_updated_by = fnd_global.user_id
750 WHERE event_spec_disp_id = l_event_spec_disp_id
751 AND result_id = temp_table_rslt_row.child_result_id;
752
753 --Now if the evaluation is reject make sure that you add a test
754 -- or resample depending on action code.
755 IF (l_validate_res.result_action_code IS NOT NULL) THEN
756 IF (l_debug = 'Y') THEN
757 gmd_debug.put_line('Result Action code for the child result row is '||l_validate_res.result_action_code);
758 END IF;
759 OPEN c_action_code(p_sample_id);
760 FETCH c_action_code into l_action_code.retest_action_code,
761 l_action_code.resample_action_code;
762 CLOSE c_action_code;
763 IF (l_validate_res.result_action_code = l_action_code.retest_action_code) THEN
764 -- Write code for inserting the test row.
765 NULL;
766 l_sample.sample_id := p_sample_id;
767 test_ids(1) := temp_table_rslt_row.test_id;
768 select test_qty,test_qty_uom
769 into l_test_qty, l_test_qty_uom
770 from gmd_results
771 where result_id = temp_table_rslt_row.child_result_id;
772
773 gmd_results_grp.add_tests_to_sample
774 (p_sample => l_sample
775 ,p_test_ids => test_ids
776 ,p_event_spec_disp_id => l_event_spec_disp_id
777 ,x_results_tab => add_rslt_tab_out
778 ,x_spec_results_tab => add_spec_tab_out
779 ,x_return_status => l_return_status
780 ,p_test_qty => l_test_qty
781 ,p_test_qty_uom => l_test_qty_uom
782 ) ;
783
784
785
786
787 IF l_return_status <> 'S' THEN
788 RAISE FND_API.G_EXC_ERROR;
789 ELSE
790 gmd_api_pub.RAISE2(
791 P_event_name =>'oracle.apps.gmd.qm.performtest',
792 P_event_key =>p_sample_id,
793 P_Parameter_name1 =>'TEST_ID',
794 P_Parameter_value1=> temp_table_rslt_row.test_id
795 );
796 END IF;
797 ELSIF (l_validate_res.result_action_code = l_action_code.resample_action_code) THEN
798 -- notification for taking the sample.
799 SELECT inventory_item_id, lot_number
800 INTO l_inventory_item_id, l_lot_number
801 FROM gmd_samples
802 WHERE sample_id = p_sample_id;
803 gmd_api_pub.raise (P_EVENT_NAME => 'oracle.apps.gmi.lotretestdate.update',
804 P_EVENT_KEY => to_char(l_inventory_item_id)
805 ||'-'|| l_lot_number);
806 END IF;
807
808 END IF; -- End if the result action code is not null;
809 END LOOP;
810
811 -- Bug 3892771
812 -- Need to calculate expressions
813 gmd_results_grp.calc_expression
814 ( p_sample_id => p_sample_id
815 , p_event_spec_disp_id => l_event_spec_disp_id
816 , p_spec_id => l_spec_tests.spec_id
817 , x_rslt_tbl => l_rslt_tbl_expression
818 , x_return_status => l_return_status_expression);
819
820 IF (l_return_status_expression <>'S') THEN
821 RAISE FND_API.G_EXC_ERROR;
822 END IF ;
823 -- Bug 3892771
824
825
826 --B3356274, start invalidate composite result on result association
827 IF (l_composite_flag = 'Y') THEN
828 BEGIN
829 SELECT sample_active_cnt
830 INTO l_sample_active_cnt
831 FROM gmd_sampling_events gse
832 WHERE gse.sampling_event_id=p_sampling_event_id;
833 EXCEPTION
834 WHEN OTHERS THEN
835 --This should never happen
836 NULL;
837 END;
838 END IF;
839
840 IF (nvl(l_sample_active_cnt, 0) > 1) THEN
841 GMD_RESULTS_GRP.se_recomposite_required ( p_sampling_event_id => p_sampling_event_id
842 , p_event_spec_disp_id => l_event_spec_disp_id
843 , x_return_status => l_return_status
844 );
845 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848 END IF;
849
850
851 --B3356274, end invalidate composite result on result association
852 GMD_RESULTS_GRP.change_sample_disposition
853 ( p_sample_id => p_sample_id,
854 x_change_disp_to => x_sample_disp,
855 x_return_status => l_return_status,
856 x_message_data => l_message_data
857 );
858 ELSE
859 -- Make sure flex field can be copied or not
860 IF (fnd_flex_apis.IS_DESCR_SETUP(552,'GMD_QC_COMPOSITE_RESULTS_FLEX')
861 and p_copy_flex_field = 'Y') THEN
862 p_copy_flex := 'Y';
863 END IF;
864 FOR c_temp_table_cmpt_row IN c_temp_table_cmpt LOOP
865 l_place := 50;
866 l_in_spec := gmd_results_grp.rslt_is_in_spec(l_spec_id,
867 c_temp_table_cmpt_row.test_id,
868 to_char(c_temp_table_cmpt_row.mean),
869 c_temp_table_cmpt_row.mode_char);
870 l_place := 55;
871
872 --rboddu Modified the following update statement to update gmd_composite_results with median_num, median_char selected from c_temp_table_cmpt. bug 3571258
873 UPDATE gmd_composite_results
874 SET in_spec_ind = l_in_spec,
875 mean= c_temp_table_cmpt_row.mean ,
876 mode_num= c_temp_table_cmpt_row.mode_num,
877 mode_char = c_temp_table_cmpt_row.mode_char,
878 median_char = c_temp_table_cmpt_row.median_char,
879 median_num = c_temp_table_cmpt_row.median_num,
880 low_num= c_temp_table_cmpt_row.low_num,
881 high_num= c_temp_table_cmpt_row.high_num,
882 range= c_temp_table_cmpt_row.range,
883 non_validated_result = c_temp_table_cmpt_row.non_validated_result,
884 standard_deviation = c_temp_table_cmpt_row.standard_deviation,
885 last_update_date = SYSDATE,
886 last_updated_by = fnd_global.USER_ID,
887 text_code = decode(p_copy_edit_text,'Y',
888 c_temp_table_cmpt_row.text_code,text_code),
889 parent_composite_result_id = c_temp_table_cmpt_row.parent_result_id,
890 attribute_category = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute_category, attribute_category),
891 attribute1 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute1, attribute1),
892 attribute2 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute2, attribute2),
893 attribute3 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute3, attribute3),
894 attribute4 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute4, attribute4),
895 attribute5 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute5, attribute5),
896 attribute6 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute6, attribute6),
897 attribute7 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute7, attribute7),
898 attribute8 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute8, attribute8),
899 attribute9 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute9, attribute9),
900 attribute10 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute10, attribute10),
901 attribute11 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute11, attribute11),
902 attribute12 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute12, attribute12),
903 attribute13 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute13, attribute13),
904 attribute14 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute14, attribute14),
905 attribute15 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute15, attribute15),
906 attribute16 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute16, attribute16),
907 attribute17 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute17, attribute17),
908 attribute18 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute18, attribute18),
909 attribute19 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute19, attribute19),
910 attribute20 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute20, attribute20),
911 attribute21 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute21, attribute21),
912 attribute22 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute22, attribute22),
913 attribute23 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute23, attribute23),
914 attribute24 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute24, attribute24),
915 attribute25 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute25, attribute25),
916 attribute26 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute26, attribute26),
917 attribute27 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute27, attribute27),
918 attribute28 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute28, attribute28),
919 attribute29 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute29, attribute29),
920 attribute30 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute30, attribute30)
921 WHERE composite_result_id = c_temp_table_cmpt_row.child_result_id;
922
923 IF (p_copy_attachment = 'Y') THEN
924 fnd_attached_documents2_pkg.copy_attachments(X_from_entity_name => 'GMD_COMPOSITE_RESULTS',
925 X_from_pk1_value => c_temp_table_cmpt_row.parent_result_id,
926 X_to_entity_name => 'GMD_COMPOSITE_RESULTS',
927 x_to_pk1_value => c_temp_table_cmpt_row.child_result_id);
928 END IF;
929
930 END LOOP;
931 END IF;
932 FND_MSG_PUB.Count_AND_GET
933 (p_count => x_message_count, p_data => x_message_data);
934
935
936 COMMIT;
937 EXCEPTION
938
939 WHEN OTHERS THEN
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 log_msg('GMD_QC_RESULT_TRANSFER_GRP.DO_TRANSFER AT '||l_place||' '|| SUBSTR(SQLERRM,1,100));
942 FND_MSG_PUB.Count_AND_GET
943 (p_count => x_message_count, p_data => x_message_data);
944 END do_transfer;
945
946 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
947 BEGIN
948 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
949 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
950 FND_MSG_PUB.Add;
951 END log_msg ;
952
953 PROCEDURE copy_previous_composite_result(p_composite_spec_disp_id IN NUMBER,
954 x_message_count OUT NOCOPY NUMBER,
955 x_message_data OUT NOCOPY VARCHAR2,
956 x_return_status OUT NOCOPY VARCHAR2) IS
957 cursor c_previous_row IS
958 SELECT a.event_spec_disp_id,r.in_spec_ind,r.parent_composite_result_id,
959 r.composite_result_id,
960 r.composite_spec_disp_id,
961 r.test_id,
962 nvl(to_char(r.mean),r.mode_char) result,
963 r.mean,
964 r.mode_num,
965 r.mode_char,
966 r.low_num,
967 r.high_num,
968 r.range,
969 r.non_validated_result,
970 r.standard_deviation,
971 r.text_code,
972 r.ATTRIBUTE_CATEGORY,
973 r.ATTRIBUTE1,
974 r.ATTRIBUTE2,
975 r.ATTRIBUTE3,
976 r.ATTRIBUTE4,
977 r.ATTRIBUTE5,
978 r.ATTRIBUTE6,
979 r.ATTRIBUTE7,
980 r.ATTRIBUTE8,
981 r.ATTRIBUTE9,
982 r.ATTRIBUTE10,
983 r.ATTRIBUTE11,
984 r.ATTRIBUTE12,
985 r.ATTRIBUTE13,
986 r.ATTRIBUTE14,
987 r.ATTRIBUTE15,
988 r.ATTRIBUTE16,
989 r.ATTRIBUTE17,
990 r.ATTRIBUTE18,
991 r.ATTRIBUTE19,
992 r.ATTRIBUTE20,
993 r.ATTRIBUTE21,
994 r.ATTRIBUTE22,
995 r.ATTRIBUTE23,
996 r.ATTRIBUTE24,
997 r.ATTRIBUTE25,
998 r.ATTRIBUTE26,
999 r.ATTRIBUTE27,
1000 r.ATTRIBUTE28,
1001 r.ATTRIBUTE29,
1002 r.ATTRIBUTE30
1003 FROM gmd_composite_results r,
1004 gmd_composite_spec_disp a --Bug 3017743, added to get event spec disp id.
1005 WHERE r.composite_spec_disp_id = p_composite_spec_disp_id
1006 AND r.composite_spec_disp_id = a.composite_spec_disp_id
1007 AND r.parent_composite_result_id IS NOT NULL
1008 ;
1009
1010
1011
1012
1013 CURSOR c_curr_compo_rec(p_event_spec_disp_id IN NUMBER,
1014 p_test_id IN NUMBER) IS
1015 SELECT composite_result_id
1016 FROM gmd_composite_results a, gmd_composite_spec_disp b
1017 WHERE a.composite_spec_disp_id = b.composite_spec_disp_id
1018 AND b.latest_ind = 'Y'
1019 AND b.event_spec_disp_id = p_event_spec_disp_id
1020 AND a.test_id = p_test_id
1021 AND (( a.mode_char IS NULL) OR (a.mean IS NULL));
1022 --AND a.parent_composite_result_id IS NOT NULL; --Bug 3349433, for current composite result, this value will be null
1023
1024 l_place NUMBER;
1025
1026 BEGIN
1027 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1028 FOR l_previous_row IN c_previous_row LOOP
1029 l_place := 20;
1030 gmd_debug.put_line('The value of event_spec_disp_id is '||l_previous_row.event_spec_disp_id);
1031 gmd_debug.put_line('The value of test_id is '||l_previous_row.test_id);
1032 FOR l_curr_compo_rec IN c_curr_compo_rec(l_previous_row.event_spec_disp_id,
1033 l_previous_row.test_id) LOOP
1034 gmd_debug.put_line('The value of composite_result_id is '||l_curr_compo_rec.composite_result_id);
1035 UPDATE gmd_composite_results
1036 SET in_spec_ind = l_previous_row.in_spec_ind,
1037 mean= l_previous_row.mean ,
1038 mode_num= l_previous_row.mode_num,
1039 mode_char = l_previous_row.mode_char,
1040 low_num= l_previous_row.low_num,
1041 high_num= l_previous_row.high_num,
1042 range= l_previous_row.range,
1043 non_validated_result = l_previous_row.non_validated_result,
1044 standard_deviation = l_previous_row.standard_deviation,
1045 last_update_date = SYSDATE,
1046 last_updated_by = fnd_global.USER_ID,
1047 text_code =l_previous_row.text_code,
1048 parent_composite_result_id = l_previous_row.parent_composite_result_id,
1049 attribute_category = l_previous_row.attribute_category,
1050 attribute1 = l_previous_row.attribute1,
1051 attribute2 = l_previous_row.attribute2,
1052 attribute3 = l_previous_row.attribute3,
1053 attribute4 = l_previous_row.attribute4,
1054 attribute5 = l_previous_row.attribute5,
1055 attribute6 = l_previous_row.attribute6,
1056 attribute7 = l_previous_row.attribute7,
1057 attribute8 = l_previous_row.attribute8,
1058 attribute9 = l_previous_row.attribute9,
1059 attribute10 = l_previous_row.attribute10,
1060 attribute11 = l_previous_row.attribute11,
1061 attribute12 = l_previous_row.attribute12,
1062 attribute13 = l_previous_row.attribute13,
1063 attribute14 = l_previous_row.attribute14,
1064 attribute15 = l_previous_row.attribute15,
1065 attribute16 = l_previous_row.attribute16,
1066 attribute17 = l_previous_row.attribute17,
1067 attribute18 = l_previous_row.attribute18,
1068 attribute19 = l_previous_row.attribute19,
1069 attribute20 = l_previous_row.attribute20,
1070 attribute21 = l_previous_row.attribute21,
1071 attribute22 = l_previous_row.attribute22,
1072 attribute23 = l_previous_row.attribute23,
1073 attribute24 = l_previous_row.attribute24,
1074 attribute25 = l_previous_row.attribute25,
1075 attribute26 = l_previous_row.attribute26,
1076 attribute27 = l_previous_row.attribute27,
1077 attribute28 = l_previous_row.attribute28,
1078 attribute29 = l_previous_row.attribute29,
1079 attribute30 = l_previous_row.attribute30
1080 WHERE composite_result_id = l_curr_compo_rec.composite_result_id;
1081
1082
1083 END LOOP;
1084 END LOOP;
1085 EXCEPTION
1086
1087 WHEN OTHERS THEN
1088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1089 log_msg('GMD_QC_RESULT_TRANSFER_GRP.DO_TRANSFER AT '||l_place||' '|| SUBSTR(SQLERRM,1,100));
1090 FND_MSG_PUB.Count_AND_GET
1091 (p_count => x_message_count, p_data => x_message_data);
1092
1093 END copy_previous_composite_result;
1094
1095 PROCEDURE delete_single_composite(p_composite_spec_disp_id NUMBER,
1096 x_message_count OUT NOCOPY NUMBER,
1097 x_message_data OUT NOCOPY VARCHAR2,
1098 x_return_status OUT NOCOPY VARCHAR2) IS
1099 l_place NUMBER;
1100 BEGIN
1101 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1102 -- There can never be any issue with locking as exiting the form should always delete single
1103 -- composites.
1104 l_place := 10;
1105 delete gmd_composite_result_assoc
1106 where composite_result_id IN
1107 (select composite_result_id
1108 from gmd_composite_results
1109 where composite_spec_disp_id = p_composite_spec_disp_id);
1110 IF (l_debug = 'Y') THEN
1111 gmd_debug.put_line('Deleting<'||SQL%ROWCOUNT||'>gmd_composite_result_assoc for single sample sample group for composite_spec_disp_id <'||p_composite_spec_disp_id||'>');
1112 END IF;
1113 l_place := 20;
1114 delete gmd_composite_results
1115 where composite_spec_disp_id = p_composite_spec_disp_id;
1116
1117 IF (l_debug = 'Y') THEN
1118 gmd_debug.put_line('Deleting<'||SQL%ROWCOUNT||'>gmd_composite_results for single sample sample group for composite_spec_disp_id <'||p_composite_spec_disp_id||'>');
1119 END IF;
1120
1121 l_place := 30;
1122 delete gmd_composite_spec_disp
1123 where composite_spec_disp_id = p_composite_spec_disp_id;
1124 IF (l_debug = 'Y') THEN
1125 gmd_debug.put_line('Deleting<'||SQL%ROWCOUNT||'>gmd_composite_spec_disp for single sample sample group for composite_spec_disp_id <'||p_composite_spec_disp_id||'>');
1126 END IF;
1127 --Bug 3334382, single sample group transaction not gettting deleted
1128 COMMIT;
1129 --end bug 3334382
1130 EXCEPTION
1131
1132 WHEN OTHERS THEN
1133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1134 log_msg('GMD_RESULT_TRANSFER_GRP.delete_single_composite AT '||l_place||' '|| SUBSTR(SQLERRM,1,100));
1135 FND_MSG_PUB.Count_AND_GET
1136 (p_count => x_message_count, p_data => x_message_data);
1137 END delete_single_composite;
1138
1139
1140 END gmd_result_transfer_grp;