[Home] [Help]
1: PACKAGE BODY gmd_results_grp AS
2: --$Header: GMDGRESB.pls 120.29.12020000.5 2012/12/05 17:59:43 plowe ship $
3:
4: -- Global variables
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RESULTS_GRP';
1: PACKAGE BODY gmd_results_grp AS
2: --$Header: GMDGRESB.pls 120.29.12020000.5 2012/12/05 17:59:43 plowe ship $
3:
4: -- Global variables
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RESULTS_GRP';
6: --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
7: --forward decl.
8: function set_debug_flag return varchar2;
9: --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
28: --| Redwood Shores, CA, USA |
29: --| All rights reserved. |
30: --+==========================================================================+
31: --| File Name : GMDGRESB.pls |
32: --| Package Name : GMD_RESULTS_GRP |
33: --| Type : Group |
34: --| |
35: --| Notes |
36: --| This package contains group layer APIs for Results Entity |
177: p_sample IN GMD_SAMPLES%ROWTYPE
178: , p_migration IN VARCHAR2
179: , x_event_spec_disp OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
180: , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
181: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
182: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
183: , x_return_status OUT NOCOPY VARCHAR2
184: ) IS
185:
249:
250: l_sampling_event gmd_sampling_events%ROWTYPE;
251: l_event_spec_disp gmd_event_spec_disp%ROWTYPE;
252: l_sample_spec_disp gmd_sample_spec_disp%ROWTYPE;
253: l_results gmd_results%ROWTYPE;
254: l_spec_results gmd_spec_results%ROWTYPE;
255:
256: l_in_sampling_event gmd_sampling_events%ROWTYPE;
257: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
255:
256: l_in_sampling_event gmd_sampling_events%ROWTYPE;
257: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
258: l_out_event_spec_disp gmd_event_spec_disp%ROWTYPE;
259: l_out_results gmd_results%ROWTYPE;
260:
261: -- Exceptions
262: e_sampling_event_fetch_error EXCEPTION;
263: e_results_insert_error EXCEPTION;
494: END IF; -- end if migration is calling this procedure
495:
496: -- By now the Event Spec Disp record is either created or fetched.
497: -- Now if the Event Spec Disp record has Spec then create
498: -- rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
499:
500:
501: -- Bug 2790099: Retained samples still need an entry in
502: -- GMD_SAMPLE_SPEC_DISP table.
572: --END IF; -- (temp_qty < 0)
573: --END IF; -- l_spec_test.test_uom <> p_sample.sample_uom
574: -- end bug 3088216
575:
576: -- Construct GMD_RESULTS record
577:
578: l_results.sample_id := p_sample.sample_id;
579: l_results.test_id := l_spec_test.test_id;
580: l_results.test_replicate_cnt := i;
673: -- DESC FLEX Enhancement
674:
675:
676:
677: -- We are ready for insert in GMD_RESULTS, so then lets do it.
678: IF NOT(GMD_RESULTS_PVT.Insert_Row(
679: p_results => l_results,
680: x_results => l_out_results)
681: )
674:
675:
676:
677: -- We are ready for insert in GMD_RESULTS, so then lets do it.
678: IF NOT(GMD_RESULTS_PVT.Insert_Row(
679: p_results => l_results,
680: x_results => l_out_results)
681: )
682: THEN
814: x_return_status := FND_API.G_RET_STS_SUCCESS;
815:
816: DELETE gmd_spec_results
817: WHERE result_id IN (SELECT result_id
818: FROM gmd_results
819: WHERE sample_id = p_sample_id)
820: ;
821:
822: IF (l_debug = 'Y') THEN
822: IF (l_debug = 'Y') THEN
823: gmd_debug.put_line('Rows deleted from gmd_spec_results: '|| SQL%ROWCOUNT);
824: END IF;
825:
826: DELETE gmd_results
827: WHERE sample_id = p_sample_id
828: ;
829:
830: IF (l_debug = 'Y') THEN
827: WHERE sample_id = p_sample_id
828: ;
829:
830: IF (l_debug = 'Y') THEN
831: gmd_debug.put_line('Rows deleted from gmd_results: '|| SQL%ROWCOUNT);
832: END IF;
833:
834: IF (l_debug = 'Y') THEN
835: gmd_debug.put_line('Leaving procedure DELETE_RSLT_AND_SPEC_RSLT_ROWS');
961: WHERE st.spec_id = p_spec_id
962: AND st.exclude_ind IS NULL
963: AND st.test_id NOT IN
964: (SELECT r.test_id
965: FROM gmd_results r
966: WHERE r.sample_id = p_sample_id)
967: ORDER BY st.seq
968: ;
969:
1358: --| TYPE : Group |
1359: --| Notes : This function received as input table of test IDs that |
1360: --| are to be added to a given sample. |
1361: --| |
1362: --| The function will insert rows into GMD_RESULTS and |
1363: --| GMD_SPEC_RESULTS. |
1364: --| |
1365: --| PARAMETERS : |
1366: --| |
1393: (
1394: p_sample IN GMD_SAMPLES%ROWTYPE
1395: , p_test_ids IN GMD_API_PUB.number_tab
1396: , p_event_spec_disp_id IN NUMBER
1397: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
1398: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
1399: , x_return_status OUT NOCOPY VARCHAR2
1400: , p_test_qty IN NUMBER default NULL
1401: , p_test_qty_uom IN VARCHAR2 default NULL
1444: l_resources GMD_TEST_METHODS_B.RESOURCES%TYPE;
1445:
1446: l_sample GMD_SAMPLES%ROWTYPE;
1447: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1448: l_results GMD_RESULTS%ROWTYPE;
1449: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
1450: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1451:
1452: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1450: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1451:
1452: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1453: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1454: l_out_results GMD_RESULTS%ROWTYPE;
1455:
1456: -- Exceptions
1457: e_results_insert_error EXCEPTION;
1458: e_spec_results_insert_error EXCEPTION;
1607: gmd_debug.put_line('The additional test indicator is (Y/NULL): ' || l_additional_test_ind);
1608: END IF;
1609:
1610:
1611: -- Now Construct GMD_RESULTS record
1612:
1613: -- For this, gather required information in local varaibles
1614:
1615: -- 1. Get the next test_replicate_cnt
1614:
1615: -- 1. Get the next test_replicate_cnt
1616: SELECT NVL(MAX(test_replicate_cnt), 0) + 1
1617: INTO l_next_test_replicate_cnt
1618: FROM gmd_results
1619: WHERE sample_id = l_sample.sample_id
1620: AND test_id = p_test_ids(i)
1621: ;
1622:
1720: LOOP
1721: IF (l_debug = 'Y') THEN
1722: gmd_debug.put_line('Inserting test for replicate: ' || l_results.TEST_REPLICATE_CNT);
1723: END IF;
1724: -- We are ready for insert in GMD_RESULTS, so then lets do it.
1725: IF NOT(GMD_RESULTS_PVT.Insert_Row(
1726: p_results => l_results,
1727: x_results => l_out_results)
1728: )
1721: IF (l_debug = 'Y') THEN
1722: gmd_debug.put_line('Inserting test for replicate: ' || l_results.TEST_REPLICATE_CNT);
1723: END IF;
1724: -- We are ready for insert in GMD_RESULTS, so then lets do it.
1725: IF NOT(GMD_RESULTS_PVT.Insert_Row(
1726: p_results => l_results,
1727: x_results => l_out_results)
1728: )
1729: THEN
1872: RETURN NUMBER IS
1873:
1874: CURSOR c_seq IS
1875: SELECT seq
1876: FROM gmd_results
1877: WHERE sample_id = p_sample_id
1878: AND test_id = p_test_id
1879: ;
1880:
1888:
1889: -- The test is not part of result set, so get the new seq
1890: SELECT (floor(nvl(max(seq),0) / 10) * 10) + 10
1891: INTO l_seq
1892: FROM gmd_results
1893: WHERE sample_id = p_sample_id
1894: ;
1895:
1896: END IF;
1932: FROM gmd_qc_test_values_b tv
1933: WHERE tv.test_id = p_test_id
1934: AND tv.expression_ref_test_id NOT IN
1935: (SELECT test_id
1936: FROM gmd_results
1937: WHERE sample_id = p_sample_id)
1938: ;
1939:
1940: -- Local Variables
1965: --| TYPE : Group |
1966: --| Notes : This function received as input table of sample IDs to |
1967: --| which a given test is to be added. |
1968: --| |
1969: --| The function will insert rows into GMD_RESULTS and |
1970: --| GMD_SPEC_RESULTS with the specified test for each Sample.|
1971: --| |
1972: --| THIS ROUTINE RE-USES THE ROUTINE ADD_TESTS_TO_SAMPLE |
1973: --| BY ADDING ONE TEST TO ONE SAMPLE AT A TIME. |
1989: (
1990: p_sample_ids IN GMD_API_PUB.number_tab
1991: , p_test_id IN NUMBER
1992: , p_event_spec_disp_id IN NUMBER
1993: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
1994: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
1995: , x_return_status OUT NOCOPY VARCHAR2
1996: , p_test_qty IN NUMBER DEFAULT NULL
1997: , p_test_qty_uom IN VARCHAR2 DEFAULT NULL
2000:
2001: -- Local Variables
2002: l_sample GMD_SAMPLES%ROWTYPE;
2003: l_test_ids GMD_API_PUB.number_tab;
2004: l_results_tab GMD_API_PUB.gmd_results_tab;
2005: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
2006:
2007: l_return_status VARCHAR2(1);
2008:
2080: --| Notes : This procedure is to make the target spec as the base |
2081: --| spec for a given sample. |
2082: --| |
2083: --| This procedure will first create any missing replicate |
2084: --| of the tests for the target spec in gmd_results. Then |
2085: --| create a row in GMD_EVENT_SPEC_DISP and a row in |
2086: --| GMD_SAMPLE_SPEC_DISP and a set of rows in |
2087: --| GMD_SPEC_RESULTS |
2088: --| |
2120: )
2121: IS
2122:
2123: -- Cursors
2124: CURSOR c_gmd_results (p_sample_id NUMBER) IS
2125: SELECT result_id,
2126: test_id,
2127: result_value_num,
2128: result_value_char,
2126: test_id,
2127: result_value_num,
2128: result_value_char,
2129: result_date --Bug 5220513
2130: FROM gmd_results
2131: WHERE sample_id = p_sample_id
2132: ;
2133:
2134: CURSOR c_spec_tests (p_spec_id NUMBER) IS
2238: l_change_disp_to VARCHAR2(3);
2239: l_message_data VARCHAR2(2000);
2240:
2241: l_test_ids GMD_API_PUB.number_tab;
2242: l_results_tab GMD_API_PUB.gmd_results_tab;
2243: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
2244:
2245: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2246: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
2425: END IF;
2426:
2427: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
2428: -- In this run we are just creating records for all the results
2429: -- that are there in GMD_RESULTS
2430: FOR l_res IN c_gmd_results(l_sample.sample_id)
2431: LOOP
2432:
2433: -- Construct GMD_SPEC_RESULTS Record
2426:
2427: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
2428: -- In this run we are just creating records for all the results
2429: -- that are there in GMD_RESULTS
2430: FOR l_res IN c_gmd_results(l_sample.sample_id)
2431: LOOP
2432:
2433: -- Construct GMD_SPEC_RESULTS Record
2434: l_spec_result.EVENT_SPEC_DISP_ID := l_event_spec_disp.event_spec_disp_id;
2576: END IF;
2577:
2578:
2579: IF x_rec.test_type in ('N', 'L', 'E') THEN
2580: GMD_RESULTS_GRP.check_experimental_error ( x_rec
2581: , l_return_status );
2582: IF (l_return_status <> 'S') THEN
2583: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2584: END IF;
2627: THEN
2628: -- Insert Error
2629:
2630: IF (l_debug = 'Y') THEN
2631: gmd_debug.put_line(' gmd_results_grp.make_target_spec_the_base_spec Insert Error from GMD_SPEC_RESULTS_PVT.Insert_Row RAISE e_spec_results_insert_error ');
2632: END IF;
2633:
2634: RAISE e_spec_results_insert_error;
2635: END IF;
2639: END IF;
2640:
2641: END LOOP; -- For all the existing results
2642:
2643: -- Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for
2644: -- the tests that are missing and are part of the Target Spec
2645:
2646: IF (l_debug = 'Y') THEN
2647: gmd_debug.put_line(' Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for the tests that are missing and are part of the Target Spec');
2643: -- Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for
2644: -- the tests that are missing and are part of the Target Spec
2645:
2646: IF (l_debug = 'Y') THEN
2647: gmd_debug.put_line(' Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for the tests that are missing and are part of the Target Spec');
2648: END IF;
2649:
2650: -- Go throug all the tests that are part of the Spec
2651: FOR l_spec_test IN c_spec_tests(p_target_spec_id)
2662:
2663:
2664: SELECT nvl(max(test_replicate_cnt), 0)
2665: INTO l_curr_replicate_cnt
2666: FROM GMD_RESULTS
2667: WHERE sample_id = l_sample.sample_id
2668: AND test_id = l_spec_test.test_id
2669: ;
2670:
2729: END LOOP; -- Spec Tests Loop
2730:
2731: -- Now all the test adding business is over. So let's see it the sample disposition
2732: -- can be changed to Complete
2733: gmd_results_grp.change_sample_disposition
2734: ( p_sample_id => l_sample.sample_id
2735: , x_change_disp_to => l_change_disp_to
2736: , x_return_status => l_return_status
2737: , x_message_data => l_message_data
2946: --| HISTORY |
2947: --| Ger Kelly 10 Sep 2002 Created. |
2948: --| GK 11 Sep 2002 Made changes to the results cursor for replicates |
2949: --| GK 19 Sep 2002 Added event_spec_disp_id as parameter, |
2950: --| changed results_rec to gmd_results_rec_tbl |
2951: --| GK 24 Sep 2002 Changed the cursor c_res to incorporate analytical fns |
2952: --| GK 17 Oct 2002 B2621648 - Changed the IF, ELSIF to accomodate for
2953: --| chars ,e.g. text_range that have both num and chars
2954: --| Sukarna Reddy 29 Oct 2002. B2620851. Added code to fetch test results
2969: p_sample_id IN NUMBER,
2970: p_source_spec_id IN NUMBER,
2971: p_target_spec_id IN NUMBER,
2972: p_event_spec_disp_id IN NUMBER,
2973: x_results_rec_tbl OUT NOCOPY GMD_RESULTS_GRP.gmd_results_rec_tbl,
2974: x_return_status OUT NOCOPY VARCHAR2) IS
2975:
2976:
2977: /* Local Variables */
2984: l_display_label VARCHAR2(80);
2985: l_spec_ind VARCHAR2(1);
2986: l_test_id NUMBER;
2987:
2988: l_results_rec_tbl GMD_RESULTS_GRP.gmd_results_rec_tbl;
2989:
2990: x_test_ids GMD_API_PUB.number_tab;
2991: l_sample_id GMD_API_PUB.number_tab;
2992: return_status VARCHAR2(1);
3037: LocalCharRec c_get_spec_test_char%ROWTYPE;
3038:
3039: CURSOR c_get_results(p_sample_id NUMBER) IS
3040: SELECT *
3041: FROM gmd_results
3042: WHERE sample_id = p_sample_id;
3043:
3044: l_qc_test gmd_qc_tests%rowtype;
3045: l_test_mthd gmd_test_methods%rowtype;
3065:
3066: -- begin Bug 2620851 29 Oct 2002
3067: i := 1;
3068:
3069: -- IF there is no specification associated with sample then get the information from GMD_RESULTS directly.
3070: IF (p_source_spec_id IS NULL) THEN
3071:
3072: IF (l_debug = 'Y') THEN
3073: gmd_debug.put_line('Source Spec ID is NULL. So fetch data from GMD_RESULTS.');
3069: -- IF there is no specification associated with sample then get the information from GMD_RESULTS directly.
3070: IF (p_source_spec_id IS NULL) THEN
3071:
3072: IF (l_debug = 'Y') THEN
3073: gmd_debug.put_line('Source Spec ID is NULL. So fetch data from GMD_RESULTS.');
3074: END IF;
3075:
3076: FOR c_result_rec IN C_get_results(p_sample_id) LOOP
3077: l_results_rec_tbl(i).test_id := c_result_rec.test_id;
3110: gmd_debug.put_line('We have the source spec, fetch data using POPULATE_RESULT_DATA_POINTS');
3111: END IF;
3112:
3113: --Since we have a specification
3114: --retrieve rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
3115: l_results_rec_tbl.DELETE;
3116: i := 0;
3117:
3118: -- Added Sep24 for getting tests without results
3116: i := 0;
3117:
3118: -- Added Sep24 for getting tests without results
3119: l_sample_id(1) := p_sample_id;
3120: GMD_RESULTS_GRP.populate_result_data_points(p_sample_ids => l_sample_id,
3121: p_event_spec_disp_id => p_event_spec_disp_id,
3122: x_return_status => x_return_status);
3123:
3124: -- Get the results for each sample and spec
3256: IF (l_debug = 'Y') THEN
3257: gmd_debug.put_line('Got the Test and Spec info');
3258: END IF;
3259:
3260: l_results_rec_tbl(i).in_spec := GMD_RESULTS_GRP.rslt_is_in_spec(p_source_spec_id,
3261: l_results_rec_tbl(i).test_id,
3262: l_results_rec_tbl(i).result_num,
3263: l_results_rec_tbl(i).result_char);
3264: IF (l_debug = 'Y') THEN
3263: l_results_rec_tbl(i).result_char);
3264: IF (l_debug = 'Y') THEN
3265: gmd_debug.put_line('in spec res '||l_results_rec_tbl(i).in_spec);
3266: END IF;
3267: l_results_rec_tbl(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec( p_target_spec_id,
3268: l_results_rec_tbl(i).test_id,
3269: l_results_rec_tbl(i).result_num,
3270: l_results_rec_tbl(i).result_char);
3271:
3278:
3279: j := i;
3280: l_spec_id := p_target_spec_id;
3281:
3282: GMD_RESULTS_GRP.compare_rslt_and_spec(p_sample_id,
3283: l_spec_id,
3284: x_test_ids,
3285: return_status);
3286:
3292: CLOSE c_get_type;
3293: l_results_rec_tbl(i).test_code := LocalTypeRec.test_code;
3294: l_results_rec_tbl(i).test_type := LocalTypeRec.test_type;
3295: l_results_rec_tbl(i).spec_test_id := l_test_id;
3296: l_results_rec_tbl(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec( p_target_spec_id,
3297: l_test_id,
3298: l_results_rec_tbl(i).result_num,
3299: l_results_rec_tbl(i).result_char);
3300: IF (l_debug = 'Y') THEN
3805: ||' last_value(r.result_id)'
3806: ||' over (partition by r.test_id order by NVL(r.result_date, :l_start_date),'
3807: ||' r.test_replicate_cnt'
3808: ||' range between unbounded preceding and unbounded following) rmax_id'
3809: ||' FROM gmd_results r, gmd_spec_results sr, gmd_samples s'
3810: ||' WHERE r.result_id = sr.result_id'
3811: ||' AND r.sample_id = :l_sample_id'
3812: ||' AND sr.event_spec_disp_id = :l_event_spec_disp_id'
3813: ||' AND nvl(sr.evaluation_ind, ' || '''' || 'XX' || '''' || ') not in ('
3821: ||')'
3822: ;
3823:
3824: -- The code below is now removed/changed from the SQL Above
3825: --||' FROM gmd_results r, gmd_spec_results sr, gmd_qc_tests_b t'
3826: --||' AND r.test_id = t.test_id'
3827: --||' AND t.test_type <> ' || '''' || 'U' || ''''
3828:
3829: -- GO through all the sample_ids and populate _GTMP table
3910: -- Curosrs
3911: -- RLNAGARA B5396610 Modified below cursor to select tests in order of seq .
3912: CURSOR c_test_data IS
3913: SELECT gt.test_id
3914: FROM gmd_result_data_points_gt gt, gmd_results r
3915: WHERE gt.result_id = r.result_id
3916: GROUP BY gt.test_id
3917: ORDER BY min(r.seq)
3918: ;
5003: (
5004: p_composite_spec_disp_id IN NUMBER,
5005: p_source_spec_id IN NUMBER,
5006: p_target_spec_id IN NUMBER,
5007: x_comresults_tab OUT NOCOPY GMD_RESULTS_GRP.gmd_comres_tab,
5008: x_return_status OUT NOCOPY VARCHAR2) IS
5009:
5010: -- Local Variables
5011:
5021: l_mode_char VARCHAR2(80);
5022: l_median_num NUMBER;
5023: l_median_char VARCHAR2(80);
5024:
5025: l_comres_tab GMD_RESULTS_GRP.gmd_comres_tab;
5026: return_status VARCHAR2(1);
5027: x_test_ids GMD_API_PUB.number_tab;
5028:
5029:
5158: END IF;
5159: CLOSE c_get_spec_test_char;
5160:
5161: END IF;
5162: l_comres_tab(i).in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5163: ( p_source_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5164: IF (l_debug = 'Y') THEN
5165: gmd_debug.put_line('in speca '||l_comres_tab(i).in_spec);
5166: END IF;
5163: ( p_source_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5164: IF (l_debug = 'Y') THEN
5165: gmd_debug.put_line('in speca '||l_comres_tab(i).in_spec);
5166: END IF;
5167: l_comres_tab(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5168: ( p_target_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5169: IF (l_debug = 'Y') THEN
5170: gmd_debug.put_line('in spec '||l_comres_tab(i).spec_in_spec);
5171: END IF;
5175: END LOOP; -- Composite Results test Loop
5176: j := i;
5177: l_spec_id := p_target_spec_id;
5178:
5179: GMD_RESULTS_GRP.compare_cmpst_rslt_and_spec
5180: (p_composite_spec_disp_id, l_spec_id, x_test_ids, return_status);
5181:
5182: FOR k in 1..x_test_ids.COUNT LOOP
5183: i := i + k;
5213: END IF;
5214: CLOSE c_get_spec_test_char;
5215: END IF;
5216:
5217: l_comres_tab(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5218: ( p_target_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5219: IF (l_debug = 'Y') THEN
5220: gmd_debug.put_line('in spec1 '||l_comres_tab(i).spec_in_spec);
5221: END IF;
5718: --#. be a spec. So we can use the canned in_spec function.
5719: --#. With additional tests, there is not a spec.
5720: --#. ===================================================
5721:
5722: p_result_rec.in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5723: (p_spec_id => p_result_rec.spec_id,
5724: p_test_id => p_result_rec.test_id,
5725: p_rslt_value_num => p_result_rec.result_num,
5726: p_rslt_value_char => p_result_rec.result_char);
5738: END IF;
5739:
5740: IF p_result_rec.test_type in ('N', 'L', 'E') THEN
5741:
5742: GMD_RESULTS_GRP.check_experimental_error ( p_result_rec
5743: , x_return_status );
5744: IF (x_return_status <> 'S') THEN
5745: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5746: END IF;
5965: --QZENG Bug 6774613 Start
5966: -- Added the type to save result value for update.
5967: TYPE TEST_RESULT IS RECORD
5968: (
5969: result_id gmd_results.result_id%type,
5970: result_value_num gmd_results.result_value_num%type
5971: );
5972:
5973: TYPE TEST_RESULTS IS TABLE OF TEST_RESULT;
5966: -- Added the type to save result value for update.
5967: TYPE TEST_RESULT IS RECORD
5968: (
5969: result_id gmd_results.result_id%type,
5970: result_value_num gmd_results.result_value_num%type
5971: );
5972:
5973: TYPE TEST_RESULTS IS TABLE OF TEST_RESULT;
5974:
5986: CURSOR c_exp_test IS
5987: SELECT t.test_id, t.test_code, t.expression, t.display_precision,
5988: t.report_precision, r.result_id, r.result_value_num , r.test_replicate_cnt -- 14021970 added r.test_replicate_cnt
5989: FROM gmd_samples s,
5990: gmd_results r,
5991: gmd_spec_results sr,
5992: gmd_qc_tests_b t
5993: WHERE s.sample_id = r.sample_id
5994: AND s.sample_id = p_sample_id
6039: CURSOR c_all_ref_test (p_exp_test_id NUMBER) IS
6040: SELECT gtmp.data_num,
6041: t.test_code
6042: FROM gmd_result_data_points_gt gtmp,
6043: gmd_results r,
6044: gmd_qc_tests_b t,
6045: gmd_qc_test_values_b tv
6046: WHERE gtmp.result_id = r.result_id
6047: AND r.test_id = t.test_id
6222: -- No need to update the WHO columns as they are either set in the
6223: -- FORM or the API
6224: --QZENG Bug 6774613 Start
6225: --Commented out the following update sql, moved it to another LOOP
6226: --UPDATE gmd_results
6227: --SET result_value_num = l_display_value,
6228: -- result_date = SYSDATE
6229: --WHERE result_id = l_exp_test.result_id
6230: --;
6233: l_test_results(i + 1).result_id := l_exp_test.result_id;
6234: l_test_results(i + 1).result_value_num := l_display_value;
6235: --QZENG Bug 6774613 End
6236: --RLNAGARA Bug5076736 Updating the result date of results properly depending on the result value.
6237: UPDATE gmd_results
6238: SET result_date = NULL
6239: WHERE result_id = l_exp_test.result_id
6240: AND result_value_num IS NULL;
6241:
6275: x_rec.additional_test_ind :=l_additional_test_ind;
6276: x_rec.min_num := l_min_value_num;
6277: x_rec.max_num := l_max_value_num;
6278:
6279: GMD_RESULTS_GRP.validate_result(x_rec,l_return_status);
6280: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6281: RAISE FND_API.G_EXC_ERROR;
6282: END IF;
6283: --Updating the gmd_spec_results table, with proper values
6301: --QZENG Bug 6774613 Start
6302: --Update the result here.
6303: FOR m IN 1 .. l_test_results.COUNT
6304: LOOP
6305: UPDATE gmd_results
6306: SET result_value_num = l_test_results(m).result_value_num,
6307: result_date = SYSDATE
6308: WHERE result_id = l_test_results(m).result_id;
6309: END LOOP;
6456:
6457: -- Select COUNT of tests that have result with valid evaluation
6458: SELECT count(1)
6459: INTO l_count
6460: FROM gmd_spec_results sr, gmd_results r
6461: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6462: AND sr.result_id = r.result_id
6463: AND r.sample_id = l_sample.sample_id
6464: AND (((r.result_value_num IS NOT NULL OR r.result_value_char IS NOT NULL) AND
6509:
6510: -- Select COUNT of Incomplete Tests in Sample WITH Spec
6511: SELECT count(1)
6512: INTO l_incomplete_count_from_spec
6513: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr
6514: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6515: AND sr.result_id = r.result_id
6516: AND r.sample_id = l_sample.sample_id
6517: AND st.spec_id = l_event_spec_disp.spec_id
6529: ;
6530: ELSE --IF the process parameters for the sample org is defined then consider the tests based on the vlaue of include_optional_test_rslt_ind checkbox
6531: /* SELECT count(1)
6532: INTO l_incomplete_count_from_spec
6533: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
6534: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6535: AND sr.result_id = r.result_id
6536: AND r.sample_id = l_sample.sample_id
6537: AND st.spec_id = l_event_spec_disp.spec_id
6557:
6558:
6559: SELECT count(1)
6560: INTO l_incomplete_count_from_spec
6561: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
6562: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6563: AND sr.result_id = r.result_id
6564: AND r.sample_id = l_sample.sample_id
6565: AND st.spec_id = l_event_spec_disp.spec_id
6588: -- replace above with below for bug 15885923 rework and bug 15942047
6589:
6590: SELECT count(1)
6591: INTO l_incomplete_count_from_spec
6592: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
6593: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6594: AND sr.result_id = r.result_id
6595: AND r.sample_id = l_sample.sample_id
6596: AND st.spec_id = l_event_spec_disp.spec_id
6618: IF l_incomplete_count_from_spec = 0 THEN
6619: -- Try to select COUNT of Incomplete Tests in Sample WITHOUT Spec
6620: SELECT count(1)
6621: INTO l_incomplete_count_additional
6622: FROM gmd_results r, gmd_spec_results sr
6623: WHERE sr.result_id = r.result_id
6624: AND sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6625: AND r.sample_id = l_sample.sample_id
6626: AND sr.additional_test_ind = 'Y'
6818:
6819: CURSOR count_optional_tests(p_ssample_id number,
6820: p_event_spec_disp_id number ) IS
6821: SELECT st.optional_ind
6822: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6823: gmd_spec_tests_b st
6824: WHERE esd.event_spec_disp_id = p_event_spec_disp_id
6825: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6826: AND sr.result_id = r.result_id
6887:
6888: -- B2820787 CHANGED FOLLOWING SELECT
6889: -- SELECT count(1)
6890: -- INTO l_count
6891: -- FROM gmd_results r, gmd_spec_results sr
6892: -- WHERE sr.result_id = r.result_id
6893: -- AND sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6894: -- AND r.sample_id = p_sample_id
6895: -- AND (sr.evaluation_ind IS NULL OR
6909:
6910: -- Select COUNT of Tests with Evaluation other then ACCEPT
6911: SELECT count(1)
6912: INTO l_count_with_spec
6913: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6914: gmd_spec_tests_b st
6915: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6916: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6917: AND sr.result_id = r.result_id
6936:
6937: -- Select COUNT of Tests with Evaluation other then ACCEPT or ACCEPT W/ VAR
6938: SELECT count(1)
6939: INTO l_count_wo_spec
6940: FROM gmd_results r, gmd_spec_results sr
6941: WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6942: AND sr.result_id = r.result_id
6943: AND r.sample_id = p_sample_id
6944: AND sr.additional_test_ind = 'Y'
7004:
7005: -- B2820787 REPLACED QUERY WITH
7006: -- SELECT COUNT(1)
7007: -- INTO l_count
7008: -- FROM gmd_results r, gmd_spec_results sr
7009: -- WHERE sr.result_id = r.result_id
7010: -- AND sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
7011: -- AND r.sample_id = p_sample_id
7012: -- AND (sr.evaluation_ind IS NULL OR
7026:
7027: -- Select COUNT of Tests with Evaluation other then ACCEPT
7028: SELECT count(1)
7029: INTO l_count_with_spec
7030: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
7031: gmd_spec_tests_b st
7032: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
7033: AND esd.event_spec_disp_id = sr.event_spec_disp_id
7034: AND sr.result_id = r.result_id
7054: -- Select COUNT of Tests with Evaluation other then ACCEPT or ACCEPT W/ VAR
7055: -- Bug 3763419 - Added Guaranteed by Manufacturer - 1Z
7056: SELECT count(1)
7057: INTO l_count_wo_spec
7058: FROM gmd_results r, gmd_spec_results sr
7059: WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
7060: AND sr.result_id = r.result_id
7061: AND r.sample_id = p_sample_id
7062: AND sr.additional_test_ind = 'Y'
7279:
7280: /*
7281: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7282: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
7283: l_results GMD_RESULTS%ROWTYPE;
7284: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
7285:
7286: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7287: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7284: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
7285:
7286: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7287: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7288: l_out_results GMD_RESULTS%ROWTYPE;
7289: */
7290:
7291: -- Exception
7292: e_sampling_event_fetch_error EXCEPTION;
7334:
7335: l_composite_exist := 'N';
7336: l_composite_valid := 'N';
7337:
7338: gmd_results_grp.composite_exist(
7339: p_sampling_event_id => l_sampling_event.sampling_event_id
7340: , p_event_spec_disp_id => NULL
7341: , x_composite_exist => l_composite_exist
7342: , x_composite_valid => l_composite_valid
7348:
7349: IF ((l_composite_exist = 'N') OR
7350: (l_composite_exist = 'Y' AND l_composite_valid = 'N')) THEN
7351: -- Get all the sample IDs for this Sampling Event
7352: GMD_RESULTS_GRP.get_sample_ids_for_se(l_sampling_event.sampling_event_id,
7353: l_sample_ids,
7354: l_return_status);
7355: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
7356: RAISE FND_API.G_EXC_ERROR;
7356: RAISE FND_API.G_EXC_ERROR;
7357: END IF;
7358:
7359: -- Populate Session Temp Table with Data Points
7360: GMD_RESULTS_GRP.populate_result_data_points
7361: (p_sample_ids => l_sample_ids,
7362: p_event_spec_disp_id => l_event_spec_disp_id,
7363: x_return_status => l_return_status);
7364: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
7365: RAISE FND_API.G_EXC_ERROR;
7366: END IF;
7367:
7368: -- Create new Composite Rows
7369: GMD_RESULTS_GRP.create_composite_rows
7370: (p_event_spec_disp_id => l_event_spec_disp_id,
7371: x_return_Status => l_return_status);
7372: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
7373: RAISE FND_API.G_EXC_ERROR;
7590: --| |
7591: --+========================================================================+
7592: -- End of comments
7593: IS
7594: l_rslt_tbl gmd_results_grp.rslt_tbl;
7595: l_return_status VARCHAR2(10);
7596: l_sts BOOLEAN;
7597: l_start_date DATE := GMA_CORE_PKG.get_date_constant_d('SY$MIN_DATE');
7598:
7605: l_test_id gmd_qc_tests_b.test_id%TYPE,
7606: l_event_spec_disp_id gmd_sample_spec_disp.event_spec_disp_id%TYPE)
7607: IS
7608: SELECT r.result_id
7609: FROM gmd_results r, gmd_spec_results sr
7610: WHERE r.result_id = sr.result_id
7611: AND r.sample_id = l_sample_id
7612: AND sr.event_spec_disp_id = l_event_spec_disp_id
7613: AND NVL(sr.evaluation_ind, 'XX') NOT IN ('50' ,'4C')
7629: OPEN exp_tests_need_calc(p_sample_id, test.test_id,p_event_spec_disp_id);
7630: FETCH exp_tests_need_calc INTO l_result_id;
7631: CLOSE exp_tests_need_calc;
7632: IF l_result_id IS NOT NULL THEN
7633: UPDATE gmd_results SET result_value_num = NULL WHERE result_id = l_result_id;
7634: END IF;
7635: END LOOP;
7636:
7637: EXCEPTION
7672: ||' SELECT result_id, test_id, 0, result_value_num, result_value_char FROM'
7673: ||' ('
7674: ||' SELECT r.result_id, r.test_id, r.result_value_num, r.result_value_char,'
7675: ||' r.result_date, r.test_replicate_cnt'
7676: ||' FROM gmd_results r, gmd_spec_results sr, gmd_samples s'
7677: ||' WHERE r.result_id = sr.result_id'
7678: ||' AND r.sample_id = :l_sample_id'
7679: ||' AND r.test_replicate_cnt = :l_test_replicate_cnt'
7680: ||' AND sr.event_spec_disp_id = :l_event_spec_disp_id'
7688: ||')'
7689: ;
7690:
7691: -- The code below is now removed/changed from the SQL Above
7692: --||' FROM gmd_results r, gmd_spec_results sr, gmd_qc_tests_b t'
7693: --||' AND r.test_id = t.test_id'
7694: --||' AND t.test_type <> ' || '''' || 'U' || ''''
7695:
7696: -- GO through all the sample_ids and populate _GTMP table
7722: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7723:
7724: END populate_result_data_points_r;
7725:
7726: END gmd_results_grp;