[Home] [Help]
39: --| Chetan Nagar 08-Aug-2002 Created. |
40: --| |
41: --| Vipul Vaish 23-Jul-2003 Bug#3063671 |
42: --| Added a call to the Validate_Result procedure in calc_expression |
43: --| which is used to update the gmd_spec_results table with proper values |
44: --| for IN_SPEC_IND,ACTION_CODE and EVALUATION_IND columns when the test |
45: --| type is of Expression.
46: --| Rameshwar 13-APR-2004 Bug#3545701 |
47: --| Added test type 'U' in the get_rslt_and_spec_rslt procedure |
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:
186: -- Cursors
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;
258: l_out_event_spec_disp gmd_event_spec_disp%ROWTYPE;
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.
686: l_results.RESULT_ID := l_out_results.RESULT_ID;
687:
688: x_results_tab(dummy) := l_results;
689:
690: -- Now, Construct GMD_SPEC_RESULTS record
691:
692: l_spec_results.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
693: l_spec_results.result_id := l_results.result_id;
694: l_spec_results.delete_mark := 0;
696: l_spec_results.created_by := l_user_id;
697: l_spec_results.last_updated_by := l_user_id;
698: l_spec_results.last_update_date := l_date;
699:
700: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
701: IF NOT(gmd_spec_results_pvt.Insert_Row(p_spec_results => l_spec_results))
702: THEN
703: -- Insert Error
704: RAISE e_spec_results_insert_error;
697: l_spec_results.last_updated_by := l_user_id;
698: l_spec_results.last_update_date := l_date;
699:
700: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
701: IF NOT(gmd_spec_results_pvt.Insert_Row(p_spec_results => l_spec_results))
702: THEN
703: -- Insert Error
704: RAISE e_spec_results_insert_error;
705: END IF;
812:
813: -- Initialize API return status to success
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: ;
819: WHERE sample_id = p_sample_id)
820: ;
821:
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
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: --| |
1367: --| 1. p_sample - Sample record for which tests are added to results |
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
1402: )
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;
1453: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1704: IF (nvl(l_test_duration,0) > 0 ) THEN
1705: l_results.planned_result_date := l_sample.date_drawn
1706: + l_test_duration/(60*60*24);
1707: END IF;
1708: -- Now, Construct GMD_SPEC_RESULTS record
1709: l_spec_results.event_spec_disp_id := p_event_spec_disp_id;
1710: l_spec_results.additional_test_ind := l_additional_test_ind;
1711: l_spec_results.delete_mark := 0;
1712: l_spec_results.creation_date := l_date;
1742:
1743: -- Assign the result_id to Spec Result record
1744: l_spec_results.RESULT_ID := l_results.RESULT_ID;
1745:
1746: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
1747: IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_results))
1748: THEN
1749: -- Insert Error
1750: RAISE e_spec_results_insert_error;
1743: -- Assign the result_id to Spec Result record
1744: l_spec_results.RESULT_ID := l_results.RESULT_ID;
1745:
1746: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
1747: IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_results))
1748: THEN
1749: -- Insert Error
1750: RAISE e_spec_results_insert_error;
1751: END IF;
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. |
1974: --| |
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
1998: )
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:
2009: BEGIN
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: --| |
2089: --| |
2090: --| PARAMETERS : |
2091: --| |
2160: l_test_id GMD_QC_TESTS.test_id%TYPE;
2161: l_spec_id gmd_spec_tests_b.spec_id%TYPE;
2162:
2163: CURSOR c_evaluation_ind (p_result_id NUMBER, p_event_spec_disp_id NUMBER) IS
2164: SELECT evaluation_ind from gmd_spec_results gsr, gmd_event_spec_disp esd --, gmd_sample_spec_disp ssd
2165: WHERE gsr.result_id=p_result_id
2166: AND esd.event_spec_disp_id=p_event_spec_disp_id
2167: AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
2168:
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;
2247: l_spec_result GMD_SPEC_RESULTS%ROWTYPE;
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;
2247: l_spec_result GMD_SPEC_RESULTS%ROWTYPE;
2248: l_sample GMD_SAMPLES%ROWTYPE;
2249:
2250: l_in_sample GMD_SAMPLES%ROWTYPE;
2251: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2420: END IF;
2421:
2422: IF (l_debug = 'Y') THEN
2423: gmd_debug.put_line(' A new record is created in GMD_SAMPLE_SPEC_DISP.');
2424: gmd_debug.put_line(' Now duplicate all current GMD_SPEC_RESULTS under new Spec.'); -- up to here
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
2423: gmd_debug.put_line(' A new record is created in GMD_SAMPLE_SPEC_DISP.');
2424: gmd_debug.put_line(' Now duplicate all current GMD_SPEC_RESULTS under new Spec.'); -- up to here
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
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;
2435: l_spec_result.RESULT_ID := l_res.result_id;
2436:
2437: --Bug#5220513
2621: l_spec_result.CREATED_BY := l_user_id;
2622: l_spec_result.LAST_UPDATED_BY := l_user_id;
2623: l_spec_result.LAST_UPDATE_DATE := l_date;
2624:
2625: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
2626: IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_result))
2627: THEN
2628: -- Insert Error
2629:
2622: l_spec_result.LAST_UPDATED_BY := l_user_id;
2623: l_spec_result.LAST_UPDATE_DATE := l_date;
2624:
2625: -- We are ready for insert in GMD_SPEC_RESULTS, so then lets do it.
2626: IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_result))
2627: THEN
2628: -- Insert Error
2629:
2630: IF (l_debug = 'Y') THEN
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;
2634: RAISE e_spec_results_insert_error;
2635: END IF;
2636:
2637: IF (l_debug = 'Y') THEN
2638: gmd_debug.put_line(' A duplicate record is created in GMD_SPEC_RESULTS for Result ID - ' || l_res.result_id);
2639: END IF;
2640:
2641: END LOOP; -- For all the existing results
2642:
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)
2718: RAISE FND_API.G_EXC_ERROR;
2719: END IF;
2720:
2721: IF (l_debug = 'Y') THEN
2722: gmd_debug.put_line(' Test template record added to both GMD_RESULTD and GMD_SPEC_RESULTS.');
2723: END IF;
2724:
2725: -- We ignore other out parameters
2726:
3019: WHERE s.spec_id = l_spec_id
3020: AND s.test_id = l_test_id
3021: AND s.exclude_ind IS NULL;
3022:
3023: X_additional_test gmd_spec_results.additional_test_ind%TYPE;
3024: X_display_precision gmd_qc_tests_b.display_precision%TYPE;
3025: --END BUG#2871126.
3026:
3027: LocalNumRec c_get_spec_test_num%ROWTYPE;
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
3180:
3181: --BEGIN BUG#2871126 Rameshwar
3182: SELECT sr.additional_test_ind
3183: INTO X_additional_test
3184: FROM gmd_spec_results sr
3185: WHERE sr.event_spec_disp_id = p_event_spec_disp_id
3186: AND sr.result_id = LocalResRec.result_id
3187: ;
3188:
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
5919: --| in select for table - gmd_spec_tests_b |
5920: --| |
5921: --| Vipul Vaish 23-Jul-2003 Bug#3063671 |
5922: --| Added a call to the Validate_Result procedure which is used |
5923: --| to update the gmd_spec_results table with proper values when the |
5924: --| test type is of Expression. |
5925: --| RajaSekhar 12-Nov-2003 BUG#3243631 Frontport for 3199585 |
5926: --| Removed checking for the NULL results since expression can be |
5927: --| calculated without Test Result if the expression is written with |
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
5995: AND s.retain_as IS NULL
6016: ;
6017:
6018: CURSOR c_add_test_id (p_event_spec_id NUMBER, p_result_id NUMBER) IS
6019: SELECT ADDITIONAL_TEST_IND
6020: FROM gmd_spec_results
6021: WHERE event_spec_disp_id = p_event_spec_id
6022: AND result_id = p_result_id
6023: ;
6024:
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
6284: --which returned during the above call.
6285: UPDATE gmd_spec_results
6286: SET value_in_report_precision = l_report_value,
6287: in_spec_ind = x_rec.in_spec,
6281: RAISE FND_API.G_EXC_ERROR;
6282: END IF;
6283: --Updating the gmd_spec_results table, with proper values
6284: --which returned during the above call.
6285: UPDATE gmd_spec_results
6286: SET value_in_report_precision = l_report_value,
6287: in_spec_ind = x_rec.in_spec,
6288: action_code = x_rec.result_action_code,
6289: evaluation_ind = x_rec.evaluation_ind
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'
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;
7288: l_out_results GMD_RESULTS%ROWTYPE;
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')
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