[Home] [Help]
1: PACKAGE BODY gmd_results_grp AS
2: --$Header: GMDGRESB.pls 120.20.12010000.1 2008/07/24 09:54:19 appldev 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.20.12010000.1 2008/07/24 09:54:19 appldev 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 |
131: p_sample IN GMD_SAMPLES%ROWTYPE
132: , p_migration IN VARCHAR2
133: , x_event_spec_disp OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
134: , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
135: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
136: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
137: , x_return_status OUT NOCOPY VARCHAR2
138: ) IS
139:
201:
202: l_sampling_event gmd_sampling_events%ROWTYPE;
203: l_event_spec_disp gmd_event_spec_disp%ROWTYPE;
204: l_sample_spec_disp gmd_sample_spec_disp%ROWTYPE;
205: l_results gmd_results%ROWTYPE;
206: l_spec_results gmd_spec_results%ROWTYPE;
207:
208: l_in_sampling_event gmd_sampling_events%ROWTYPE;
209: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
207:
208: l_in_sampling_event gmd_sampling_events%ROWTYPE;
209: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
210: l_out_event_spec_disp gmd_event_spec_disp%ROWTYPE;
211: l_out_results gmd_results%ROWTYPE;
212:
213: -- Exceptions
214: e_sampling_event_fetch_error EXCEPTION;
215: e_results_insert_error EXCEPTION;
446: END IF; -- end if migration is calling this procedure
447:
448: -- By now the Event Spec Disp record is either created or fetched.
449: -- Now if the Event Spec Disp record has Spec then create
450: -- rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
451:
452:
453: -- Bug 2790099: Retained samples still need an entry in
454: -- GMD_SAMPLE_SPEC_DISP table.
524: --END IF; -- (temp_qty < 0)
525: --END IF; -- l_spec_test.test_uom <> p_sample.sample_uom
526: -- end bug 3088216
527:
528: -- Construct GMD_RESULTS record
529:
530: l_results.sample_id := p_sample.sample_id;
531: l_results.test_id := l_spec_test.test_id;
532: l_results.test_replicate_cnt := i;
620: -- DESC FLEX Enhancement
621:
622:
623:
624: -- We are ready for insert in GMD_RESULTS, so then lets do it.
625: IF NOT(GMD_RESULTS_PVT.Insert_Row(
626: p_results => l_results,
627: x_results => l_out_results)
628: )
621:
622:
623:
624: -- We are ready for insert in GMD_RESULTS, so then lets do it.
625: IF NOT(GMD_RESULTS_PVT.Insert_Row(
626: p_results => l_results,
627: x_results => l_out_results)
628: )
629: THEN
761: x_return_status := FND_API.G_RET_STS_SUCCESS;
762:
763: DELETE gmd_spec_results
764: WHERE result_id IN (SELECT result_id
765: FROM gmd_results
766: WHERE sample_id = p_sample_id)
767: ;
768:
769: IF (l_debug = 'Y') THEN
769: IF (l_debug = 'Y') THEN
770: gmd_debug.put_line('Rows deleted from gmd_spec_results: '|| SQL%ROWCOUNT);
771: END IF;
772:
773: DELETE gmd_results
774: WHERE sample_id = p_sample_id
775: ;
776:
777: IF (l_debug = 'Y') THEN
774: WHERE sample_id = p_sample_id
775: ;
776:
777: IF (l_debug = 'Y') THEN
778: gmd_debug.put_line('Rows deleted from gmd_results: '|| SQL%ROWCOUNT);
779: END IF;
780:
781: IF (l_debug = 'Y') THEN
782: gmd_debug.put_line('Leaving procedure DELETE_RSLT_AND_SPEC_RSLT_ROWS');
908: WHERE st.spec_id = p_spec_id
909: AND st.exclude_ind IS NULL
910: AND st.test_id NOT IN
911: (SELECT r.test_id
912: FROM gmd_results r
913: WHERE r.sample_id = p_sample_id)
914: ORDER BY st.seq
915: ;
916:
1286: --| TYPE : Group |
1287: --| Notes : This function received as input table of test IDs that |
1288: --| are to be added to a given sample. |
1289: --| |
1290: --| The function will insert rows into GMD_RESULTS and |
1291: --| GMD_SPEC_RESULTS. |
1292: --| |
1293: --| PARAMETERS : |
1294: --| |
1319: (
1320: p_sample IN GMD_SAMPLES%ROWTYPE
1321: , p_test_ids IN GMD_API_PUB.number_tab
1322: , p_event_spec_disp_id IN NUMBER
1323: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
1324: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
1325: , x_return_status OUT NOCOPY VARCHAR2
1326: , p_test_qty IN NUMBER default NULL
1327: , p_test_qty_uom IN VARCHAR2 default NULL
1369: l_resources GMD_TEST_METHODS_B.RESOURCES%TYPE;
1370:
1371: l_sample GMD_SAMPLES%ROWTYPE;
1372: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1373: l_results GMD_RESULTS%ROWTYPE;
1374: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
1375: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1376:
1377: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1375: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1376:
1377: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1378: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1379: l_out_results GMD_RESULTS%ROWTYPE;
1380:
1381: -- Exceptions
1382: e_results_insert_error EXCEPTION;
1383: e_spec_results_insert_error EXCEPTION;
1531: gmd_debug.put_line('The additional test indicator is (Y/NULL): ' || l_additional_test_ind);
1532: END IF;
1533:
1534:
1535: -- Now Construct GMD_RESULTS record
1536:
1537: -- For this, gather required information in local varaibles
1538:
1539: -- 1. Get the next test_replicate_cnt
1538:
1539: -- 1. Get the next test_replicate_cnt
1540: SELECT NVL(MAX(test_replicate_cnt), 0) + 1
1541: INTO l_next_test_replicate_cnt
1542: FROM gmd_results
1543: WHERE sample_id = l_sample.sample_id
1544: AND test_id = p_test_ids(i)
1545: ;
1546:
1638: LOOP
1639: IF (l_debug = 'Y') THEN
1640: gmd_debug.put_line('Inserting test for replicate: ' || l_results.TEST_REPLICATE_CNT);
1641: END IF;
1642: -- We are ready for insert in GMD_RESULTS, so then lets do it.
1643: IF NOT(GMD_RESULTS_PVT.Insert_Row(
1644: p_results => l_results,
1645: x_results => l_out_results)
1646: )
1639: IF (l_debug = 'Y') THEN
1640: gmd_debug.put_line('Inserting test for replicate: ' || l_results.TEST_REPLICATE_CNT);
1641: END IF;
1642: -- We are ready for insert in GMD_RESULTS, so then lets do it.
1643: IF NOT(GMD_RESULTS_PVT.Insert_Row(
1644: p_results => l_results,
1645: x_results => l_out_results)
1646: )
1647: THEN
1790: RETURN NUMBER IS
1791:
1792: CURSOR c_seq IS
1793: SELECT seq
1794: FROM gmd_results
1795: WHERE sample_id = p_sample_id
1796: AND test_id = p_test_id
1797: ;
1798:
1806:
1807: -- The test is not part of result set, so get the new seq
1808: SELECT (floor(nvl(max(seq),0) / 10) * 10) + 10
1809: INTO l_seq
1810: FROM gmd_results
1811: WHERE sample_id = p_sample_id
1812: ;
1813:
1814: END IF;
1850: FROM gmd_qc_test_values_b tv
1851: WHERE tv.test_id = p_test_id
1852: AND tv.expression_ref_test_id NOT IN
1853: (SELECT test_id
1854: FROM gmd_results
1855: WHERE sample_id = p_sample_id)
1856: ;
1857:
1858: -- Local Variables
1883: --| TYPE : Group |
1884: --| Notes : This function received as input table of sample IDs to |
1885: --| which a given test is to be added. |
1886: --| |
1887: --| The function will insert rows into GMD_RESULTS and |
1888: --| GMD_SPEC_RESULTS with the specified test for each Sample.|
1889: --| |
1890: --| THIS ROUTINE RE-USES THE ROUTINE ADD_TESTS_TO_SAMPLE |
1891: --| BY ADDING ONE TEST TO ONE SAMPLE AT A TIME. |
1907: (
1908: p_sample_ids IN GMD_API_PUB.number_tab
1909: , p_test_id IN NUMBER
1910: , p_event_spec_disp_id IN NUMBER
1911: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
1912: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
1913: , x_return_status OUT NOCOPY VARCHAR2
1914: , p_test_qty IN NUMBER DEFAULT NULL
1915: , p_test_qty_uom IN VARCHAR2 DEFAULT NULL
1918:
1919: -- Local Variables
1920: l_sample GMD_SAMPLES%ROWTYPE;
1921: l_test_ids GMD_API_PUB.number_tab;
1922: l_results_tab GMD_API_PUB.gmd_results_tab;
1923: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
1924:
1925: l_return_status VARCHAR2(1);
1926:
1998: --| Notes : This procedure is to make the target spec as the base |
1999: --| spec for a given sample. |
2000: --| |
2001: --| This procedure will first create any missing replicate |
2002: --| of the tests for the target spec in gmd_results. Then |
2003: --| create a row in GMD_EVENT_SPEC_DISP and a row in |
2004: --| GMD_SAMPLE_SPEC_DISP and a set of rows in |
2005: --| GMD_SPEC_RESULTS |
2006: --| |
2033: )
2034: IS
2035:
2036: -- Cursors
2037: CURSOR c_gmd_results (p_sample_id NUMBER) IS
2038: SELECT result_id,
2039: test_id,
2040: result_value_num,
2041: result_value_char,
2039: test_id,
2040: result_value_num,
2041: result_value_char,
2042: result_date --Bug 5220513
2043: FROM gmd_results
2044: WHERE sample_id = p_sample_id
2045: ;
2046:
2047: CURSOR c_spec_tests (p_spec_id NUMBER) IS
2151: l_change_disp_to VARCHAR2(3);
2152: l_message_data VARCHAR2(2000);
2153:
2154: l_test_ids GMD_API_PUB.number_tab;
2155: l_results_tab GMD_API_PUB.gmd_results_tab;
2156: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
2157:
2158: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2159: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
2320: END IF;
2321:
2322: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
2323: -- In this run we are just creating records for all the results
2324: -- that are there in GMD_RESULTS
2325: FOR l_res IN c_gmd_results(l_sample.sample_id)
2326: LOOP
2327:
2328: -- Construct GMD_SPEC_RESULTS Record
2321:
2322: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
2323: -- In this run we are just creating records for all the results
2324: -- that are there in GMD_RESULTS
2325: FOR l_res IN c_gmd_results(l_sample.sample_id)
2326: LOOP
2327:
2328: -- Construct GMD_SPEC_RESULTS Record
2329: l_spec_result.EVENT_SPEC_DISP_ID := l_event_spec_disp.event_spec_disp_id;
2442: x_rec.result_num := ROUND(to_number(x_rec.result) ,x_rec.display_precision);
2443: x_rec.result := ROUND(to_number(x_rec.result) ,x_rec.display_precision);
2444:
2445: IF x_rec.test_type in ('N', 'L', 'E') THEN
2446: GMD_RESULTS_GRP.check_experimental_error ( x_rec
2447: , l_return_status );
2448: IF (l_return_status <> 'S') THEN
2449: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2450: END IF;
2500: END IF;
2501:
2502: END LOOP; -- For all the existing results
2503:
2504: -- Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for
2505: -- the tests that are missing and are part of the Target Spec
2506:
2507: IF (l_debug = 'Y') THEN
2508: 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');
2504: -- Now create rows in GMD_RESULTS and GMD_SPEC_RESULTS for
2505: -- the tests that are missing and are part of the Target Spec
2506:
2507: IF (l_debug = 'Y') THEN
2508: 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');
2509: END IF;
2510:
2511: -- Go throug all the tests that are part of the Spec
2512: FOR l_spec_test IN c_spec_tests(p_target_spec_id)
2517: -- Find out how many times the test from the target spec is carried
2518: -- out in the exisitng result set.
2519: SELECT nvl(max(test_replicate_cnt), 0)
2520: INTO l_curr_replicate_cnt
2521: FROM GMD_RESULTS
2522: WHERE sample_id = l_sample.sample_id
2523: AND test_id = l_spec_test.test_id
2524: ;
2525:
2578: END LOOP; -- Spec Tests Loop
2579:
2580: -- Now all the test adding business is over. So let's see it the sample disposition
2581: -- can be changed to Complete
2582: gmd_results_grp.change_sample_disposition
2583: ( p_sample_id => l_sample.sample_id
2584: , x_change_disp_to => l_change_disp_to
2585: , x_return_status => l_return_status
2586: , x_message_data => l_message_data
2778: --| HISTORY |
2779: --| Ger Kelly 10 Sep 2002 Created. |
2780: --| GK 11 Sep 2002 Made changes to the results cursor for replicates |
2781: --| GK 19 Sep 2002 Added event_spec_disp_id as parameter, |
2782: --| changed results_rec to gmd_results_rec_tbl |
2783: --| GK 24 Sep 2002 Changed the cursor c_res to incorporate analytical fns |
2784: --| GK 17 Oct 2002 B2621648 - Changed the IF, ELSIF to accomodate for
2785: --| chars ,e.g. text_range that have both num and chars
2786: --| Sukarna Reddy 29 Oct 2002. B2620851. Added code to fetch test results
2801: p_sample_id IN NUMBER,
2802: p_source_spec_id IN NUMBER,
2803: p_target_spec_id IN NUMBER,
2804: p_event_spec_disp_id IN NUMBER,
2805: x_results_rec_tbl OUT NOCOPY GMD_RESULTS_GRP.gmd_results_rec_tbl,
2806: x_return_status OUT NOCOPY VARCHAR2) IS
2807:
2808:
2809: /* Local Variables */
2816: l_display_label VARCHAR2(80);
2817: l_spec_ind VARCHAR2(1);
2818: l_test_id NUMBER;
2819:
2820: l_results_rec_tbl GMD_RESULTS_GRP.gmd_results_rec_tbl;
2821:
2822: x_test_ids GMD_API_PUB.number_tab;
2823: l_sample_id GMD_API_PUB.number_tab;
2824: return_status VARCHAR2(1);
2869: LocalCharRec c_get_spec_test_char%ROWTYPE;
2870:
2871: CURSOR c_get_results(p_sample_id NUMBER) IS
2872: SELECT *
2873: FROM gmd_results
2874: WHERE sample_id = p_sample_id;
2875:
2876: l_qc_test gmd_qc_tests%rowtype;
2877: l_test_mthd gmd_test_methods%rowtype;
2897:
2898: -- begin Bug 2620851 29 Oct 2002
2899: i := 1;
2900:
2901: -- IF there is no specification associated with sample then get the information from GMD_RESULTS directly.
2902: IF (p_source_spec_id IS NULL) THEN
2903:
2904: IF (l_debug = 'Y') THEN
2905: gmd_debug.put_line('Source Spec ID is NULL. So fetch data from GMD_RESULTS.');
2901: -- IF there is no specification associated with sample then get the information from GMD_RESULTS directly.
2902: IF (p_source_spec_id IS NULL) THEN
2903:
2904: IF (l_debug = 'Y') THEN
2905: gmd_debug.put_line('Source Spec ID is NULL. So fetch data from GMD_RESULTS.');
2906: END IF;
2907:
2908: FOR c_result_rec IN C_get_results(p_sample_id) LOOP
2909: l_results_rec_tbl(i).test_id := c_result_rec.test_id;
2942: gmd_debug.put_line('We have the source spec, fetch data using POPULATE_RESULT_DATA_POINTS');
2943: END IF;
2944:
2945: --Since we have a specification
2946: --retrieve rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
2947: l_results_rec_tbl.DELETE;
2948: i := 0;
2949:
2950: -- Added Sep24 for getting tests without results
2948: i := 0;
2949:
2950: -- Added Sep24 for getting tests without results
2951: l_sample_id(1) := p_sample_id;
2952: GMD_RESULTS_GRP.populate_result_data_points(p_sample_ids => l_sample_id,
2953: p_event_spec_disp_id => p_event_spec_disp_id,
2954: x_return_status => x_return_status);
2955:
2956: -- Get the results for each sample and spec
3088: IF (l_debug = 'Y') THEN
3089: gmd_debug.put_line('Got the Test and Spec info');
3090: END IF;
3091:
3092: l_results_rec_tbl(i).in_spec := GMD_RESULTS_GRP.rslt_is_in_spec(p_source_spec_id,
3093: l_results_rec_tbl(i).test_id,
3094: l_results_rec_tbl(i).result_num,
3095: l_results_rec_tbl(i).result_char);
3096: IF (l_debug = 'Y') THEN
3095: l_results_rec_tbl(i).result_char);
3096: IF (l_debug = 'Y') THEN
3097: gmd_debug.put_line('in spec res '||l_results_rec_tbl(i).in_spec);
3098: END IF;
3099: l_results_rec_tbl(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec( p_target_spec_id,
3100: l_results_rec_tbl(i).test_id,
3101: l_results_rec_tbl(i).result_num,
3102: l_results_rec_tbl(i).result_char);
3103: IF (l_debug = 'Y') THEN
3109:
3110: j := i;
3111: l_spec_id := p_target_spec_id;
3112:
3113: GMD_RESULTS_GRP.compare_rslt_and_spec(p_sample_id,
3114: l_spec_id,
3115: x_test_ids,
3116: return_status);
3117:
3123: CLOSE c_get_type;
3124: l_results_rec_tbl(i).test_code := LocalTypeRec.test_code;
3125: l_results_rec_tbl(i).test_type := LocalTypeRec.test_type;
3126: l_results_rec_tbl(i).spec_test_id := l_test_id;
3127: l_results_rec_tbl(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec( p_target_spec_id,
3128: l_test_id,
3129: l_results_rec_tbl(i).result_num,
3130: l_results_rec_tbl(i).result_char);
3131: IF (l_debug = 'Y') THEN
3636: ||' last_value(r.result_id)'
3637: ||' over (partition by r.test_id order by NVL(r.result_date, :l_start_date),'
3638: ||' r.test_replicate_cnt'
3639: ||' range between unbounded preceding and unbounded following) rmax_id'
3640: ||' FROM gmd_results r, gmd_spec_results sr, gmd_samples s'
3641: ||' WHERE r.result_id = sr.result_id'
3642: ||' AND r.sample_id = :l_sample_id'
3643: ||' AND sr.event_spec_disp_id = :l_event_spec_disp_id'
3644: ||' AND nvl(sr.evaluation_ind, ' || '''' || 'XX' || '''' || ') not in ('
3652: ||')'
3653: ;
3654:
3655: -- The code below is now removed/changed from the SQL Above
3656: --||' FROM gmd_results r, gmd_spec_results sr, gmd_qc_tests_b t'
3657: --||' AND r.test_id = t.test_id'
3658: --||' AND t.test_type <> ' || '''' || 'U' || ''''
3659:
3660: -- GO through all the sample_ids and populate _GTMP table
3741: -- Curosrs
3742: -- RLNAGARA B5396610 Modified below cursor to select tests in order of seq .
3743: CURSOR c_test_data IS
3744: SELECT gt.test_id
3745: FROM gmd_result_data_points_gt gt, gmd_results r
3746: WHERE gt.result_id = r.result_id
3747: GROUP BY gt.test_id
3748: ORDER BY min(r.seq)
3749: ;
4834: (
4835: p_composite_spec_disp_id IN NUMBER,
4836: p_source_spec_id IN NUMBER,
4837: p_target_spec_id IN NUMBER,
4838: x_comresults_tab OUT NOCOPY GMD_RESULTS_GRP.gmd_comres_tab,
4839: x_return_status OUT NOCOPY VARCHAR2) IS
4840:
4841: -- Local Variables
4842:
4852: l_mode_char VARCHAR2(80);
4853: l_median_num NUMBER;
4854: l_median_char VARCHAR2(80);
4855:
4856: l_comres_tab GMD_RESULTS_GRP.gmd_comres_tab;
4857: return_status VARCHAR2(1);
4858: x_test_ids GMD_API_PUB.number_tab;
4859:
4860:
4989: END IF;
4990: CLOSE c_get_spec_test_char;
4991:
4992: END IF;
4993: l_comres_tab(i).in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
4994: ( p_source_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
4995: IF (l_debug = 'Y') THEN
4996: gmd_debug.put_line('in speca '||l_comres_tab(i).in_spec);
4997: END IF;
4994: ( p_source_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
4995: IF (l_debug = 'Y') THEN
4996: gmd_debug.put_line('in speca '||l_comres_tab(i).in_spec);
4997: END IF;
4998: l_comres_tab(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
4999: ( p_target_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5000: IF (l_debug = 'Y') THEN
5001: gmd_debug.put_line('in spec '||l_comres_tab(i).spec_in_spec);
5002: END IF;
5006: END LOOP; -- Composite Results test Loop
5007: j := i;
5008: l_spec_id := p_target_spec_id;
5009:
5010: GMD_RESULTS_GRP.compare_cmpst_rslt_and_spec
5011: (p_composite_spec_disp_id, l_spec_id, x_test_ids, return_status);
5012:
5013: FOR k in 1..x_test_ids.COUNT LOOP
5014: i := i + k;
5044: END IF;
5045: CLOSE c_get_spec_test_char;
5046: END IF;
5047:
5048: l_comres_tab(i).spec_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5049: ( p_target_spec_id, l_test_id, l_comres_tab(i).result_num, l_comres_tab(i).result_char);
5050: IF (l_debug = 'Y') THEN
5051: gmd_debug.put_line('in spec1 '||l_comres_tab(i).spec_in_spec);
5052: END IF;
5547: --#. be a spec. So we can use the canned in_spec function.
5548: --#. With additional tests, there is not a spec.
5549: --#. ===================================================
5550:
5551: p_result_rec.in_spec := GMD_RESULTS_GRP.rslt_is_in_spec
5552: (p_spec_id => p_result_rec.spec_id,
5553: p_test_id => p_result_rec.test_id,
5554: p_rslt_value_num => p_result_rec.result_num,
5555: p_rslt_value_char => p_result_rec.result_char);
5563: END IF;
5564:
5565: IF p_result_rec.test_type in ('N', 'L', 'E') THEN
5566:
5567: GMD_RESULTS_GRP.check_experimental_error ( p_result_rec
5568: , x_return_status );
5569: IF (x_return_status <> 'S') THEN
5570: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5571: END IF;
5786: CURSOR c_exp_test IS
5787: SELECT t.test_id, t.test_code, t.expression, t.display_precision,
5788: t.report_precision, r.result_id, r.result_value_num
5789: FROM gmd_samples s,
5790: gmd_results r,
5791: gmd_spec_results sr,
5792: gmd_qc_tests_b t
5793: WHERE s.sample_id = r.sample_id
5794: AND s.sample_id = p_sample_id
5836: CURSOR c_all_ref_test (p_exp_test_id NUMBER) IS
5837: SELECT gtmp.data_num,
5838: t.test_code
5839: FROM gmd_result_data_points_gt gtmp,
5840: gmd_results r,
5841: gmd_qc_tests_b t,
5842: gmd_qc_test_values_b tv
5843: WHERE gtmp.result_id = r.result_id
5844: AND r.test_id = t.test_id
5989:
5990:
5991: -- No need to update the WHO columns as they are either set in the
5992: -- FORM or the API
5993: UPDATE gmd_results
5994: SET result_value_num = l_display_value,
5995: result_date = SYSDATE
5996: WHERE result_id = l_exp_test.result_id
5997: ;
5996: WHERE result_id = l_exp_test.result_id
5997: ;
5998:
5999: --RLNAGARA Bug5076736 Updating the result date of results properly depending on the result value.
6000: UPDATE gmd_results
6001: SET result_date = NULL
6002: WHERE result_id = l_exp_test.result_id
6003: AND result_value_num IS NULL;
6004:
6038: x_rec.additional_test_ind :=l_additional_test_ind;
6039: x_rec.min_num := l_min_value_num;
6040: x_rec.max_num := l_max_value_num;
6041:
6042: GMD_RESULTS_GRP.validate_result(x_rec,l_return_status);
6043: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6044: RAISE FND_API.G_EXC_ERROR;
6045: END IF;
6046: --Updating the gmd_spec_results table, with proper values
6194:
6195: -- Select COUNT of tests that have result with valid evaluation
6196: SELECT count(1)
6197: INTO l_count
6198: FROM gmd_spec_results sr, gmd_results r
6199: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6200: AND sr.result_id = r.result_id
6201: AND r.sample_id = l_sample.sample_id
6202: AND (((r.result_value_num IS NOT NULL OR r.result_value_char IS NOT NULL) AND
6247:
6248: -- Select COUNT of Incomplete Tests in Sample WITH Spec
6249: SELECT count(1)
6250: INTO l_incomplete_count_from_spec
6251: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr
6252: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6253: AND sr.result_id = r.result_id
6254: AND r.sample_id = l_sample.sample_id
6255: AND st.spec_id = l_event_spec_disp.spec_id
6267: ;
6268: 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
6269: SELECT count(1)
6270: INTO l_incomplete_count_from_spec
6271: FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
6272: WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6273: AND sr.result_id = r.result_id
6274: AND r.sample_id = l_sample.sample_id
6275: AND st.spec_id = l_event_spec_disp.spec_id
6297: IF l_incomplete_count_from_spec = 0 THEN
6298: -- Try to select COUNT of Incomplete Tests in Sample WITHOUT Spec
6299: SELECT count(1)
6300: INTO l_incomplete_count_additional
6301: FROM gmd_results r, gmd_spec_results sr
6302: WHERE sr.result_id = r.result_id
6303: AND sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
6304: AND r.sample_id = l_sample.sample_id
6305: AND sr.additional_test_ind = 'Y'
6542:
6543: -- B2820787 CHANGED FOLLOWING SELECT
6544: -- SELECT count(1)
6545: -- INTO l_count
6546: -- FROM gmd_results r, gmd_spec_results sr
6547: -- WHERE sr.result_id = r.result_id
6548: -- AND sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6549: -- AND r.sample_id = p_sample_id
6550: -- AND (sr.evaluation_ind IS NULL OR
6564:
6565: -- Select COUNT of Tests with Evaluation other then ACCEPT
6566: SELECT count(1)
6567: INTO l_count_with_spec
6568: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6569: gmd_spec_tests_b st
6570: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6571: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6572: AND sr.result_id = r.result_id
6591:
6592: -- Select COUNT of Tests with Evaluation other then ACCEPT or ACCEPT W/ VAR
6593: SELECT count(1)
6594: INTO l_count_wo_spec
6595: FROM gmd_results r, gmd_spec_results sr
6596: WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6597: AND sr.result_id = r.result_id
6598: AND r.sample_id = p_sample_id
6599: AND sr.additional_test_ind = 'Y'
6631:
6632: -- B2820787 REPLACED QUERY WITH
6633: -- SELECT COUNT(1)
6634: -- INTO l_count
6635: -- FROM gmd_results r, gmd_spec_results sr
6636: -- WHERE sr.result_id = r.result_id
6637: -- AND sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6638: -- AND r.sample_id = p_sample_id
6639: -- AND (sr.evaluation_ind IS NULL OR
6653:
6654: -- Select COUNT of Tests with Evaluation other then ACCEPT
6655: SELECT count(1)
6656: INTO l_count_with_spec
6657: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6658: gmd_spec_tests_b st
6659: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6660: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6661: AND sr.result_id = r.result_id
6681: -- Select COUNT of Tests with Evaluation other then ACCEPT or ACCEPT W/ VAR
6682: -- Bug 3763419 - Added Guaranteed by Manufacturer - 1Z
6683: SELECT count(1)
6684: INTO l_count_wo_spec
6685: FROM gmd_results r, gmd_spec_results sr
6686: WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6687: AND sr.result_id = r.result_id
6688: AND r.sample_id = p_sample_id
6689: AND sr.additional_test_ind = 'Y'
6902:
6903: /*
6904: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6905: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6906: l_results GMD_RESULTS%ROWTYPE;
6907: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
6908:
6909: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6910: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6907: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
6908:
6909: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6910: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6911: l_out_results GMD_RESULTS%ROWTYPE;
6912: */
6913:
6914: -- Exception
6915: e_sampling_event_fetch_error EXCEPTION;
6957:
6958: l_composite_exist := 'N';
6959: l_composite_valid := 'N';
6960:
6961: gmd_results_grp.composite_exist(
6962: p_sampling_event_id => l_sampling_event.sampling_event_id
6963: , p_event_spec_disp_id => NULL
6964: , x_composite_exist => l_composite_exist
6965: , x_composite_valid => l_composite_valid
6971:
6972: IF ((l_composite_exist = 'N') OR
6973: (l_composite_exist = 'Y' AND l_composite_valid = 'N')) THEN
6974: -- Get all the sample IDs for this Sampling Event
6975: GMD_RESULTS_GRP.get_sample_ids_for_se(l_sampling_event.sampling_event_id,
6976: l_sample_ids,
6977: l_return_status);
6978: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6979: RAISE FND_API.G_EXC_ERROR;
6979: RAISE FND_API.G_EXC_ERROR;
6980: END IF;
6981:
6982: -- Populate Session Temp Table with Data Points
6983: GMD_RESULTS_GRP.populate_result_data_points
6984: (p_sample_ids => l_sample_ids,
6985: p_event_spec_disp_id => l_event_spec_disp_id,
6986: x_return_status => l_return_status);
6987: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6988: RAISE FND_API.G_EXC_ERROR;
6989: END IF;
6990:
6991: -- Create new Composite Rows
6992: GMD_RESULTS_GRP.create_composite_rows
6993: (p_event_spec_disp_id => l_event_spec_disp_id,
6994: x_return_Status => l_return_status);
6995: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
6996: RAISE FND_API.G_EXC_ERROR;
7213: --| |
7214: --+========================================================================+
7215: -- End of comments
7216: IS
7217: l_rslt_tbl gmd_results_grp.rslt_tbl;
7218: l_return_status VARCHAR2(10);
7219: l_sts BOOLEAN;
7220: l_start_date DATE := GMA_CORE_PKG.get_date_constant_d('SY$MIN_DATE');
7221:
7228: l_test_id gmd_qc_tests_b.test_id%TYPE,
7229: l_event_spec_disp_id gmd_sample_spec_disp.event_spec_disp_id%TYPE)
7230: IS
7231: SELECT r.result_id
7232: FROM gmd_results r, gmd_spec_results sr
7233: WHERE r.result_id = sr.result_id
7234: AND r.sample_id = l_sample_id
7235: AND sr.event_spec_disp_id = l_event_spec_disp_id
7236: AND NVL(sr.evaluation_ind, 'XX') NOT IN ('50' ,'4C')
7252: OPEN exp_tests_need_calc(p_sample_id, test.test_id,p_event_spec_disp_id);
7253: FETCH exp_tests_need_calc INTO l_result_id;
7254: CLOSE exp_tests_need_calc;
7255: IF l_result_id IS NOT NULL THEN
7256: UPDATE gmd_results SET result_value_num = NULL WHERE result_id = l_result_id;
7257: END IF;
7258: END LOOP;
7259:
7260: EXCEPTION
7263: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7264:
7265: END update_exptest_value_null;
7266:
7267: END gmd_results_grp;