VIEW: APPS.GMD_QC_E_COMPOSITE_RESULTS_V

Source

View Text - Preformatted

SELECT gcsd.event_spec_disp_id, gcsd.composite_spec_disp_id, gcsd.disposition ,gqts.expression, gcsd.latest_ind, gcr.composite_result_id, gcr.test_id ,gcr.mean ,DECODE (gqts.test_type ,'T', gcr.mode_char ,'E', gcr.mode_num ,'N', gcr.mode_num ,'L', gcr.mode_num ,NULL ) mode_value ,DECODE (gqts.test_type ,'T', gcr.low_char ,'E', gcr.low_num ,'N', gcr.low_num ,'L', gcr.low_num ,NULL ) low_value ,DECODE (gqts.test_type ,'T', gcr.high_char ,'E', gcr.high_num ,'N', gcr.high_num ,'L', gcr.high_num ,NULL ) high_value ,gcr.RANGE, gcr.standard_deviation, gcr.sample_total ,gcr.sample_cnt_used, gcr.non_validated_result ,DECODE (gqts.test_type ,'T', gcr.median_char ,'E', gcr.median_num ,'N', gcr.median_num ,'L', gcr.median_num ,NULL ) median_value ,DECODE (gcr.in_spec_ind ,'Y', fly.meaning ,fln.meaning ) in_spec_ind_disp ,gcr.value_in_report_precision, gqts.test_code, gqts.test_desc ,gqts.test_unit, gqts.test_class, test_types.meaning test_type_desc ,test_priority.meaning test_priority_desc, gtms.test_method_code ,gtms.test_method_desc ,DECODE (gqts.test_type ,'T', gsts.min_value_char ,'E', gsts.min_value_num ,'N', gsts.min_value_num ,'L', gsts.min_value_num ,NULL ) spec_min_value ,DECODE (gqts.test_type ,'T', gsts.target_value_char ,'E', gsts.target_value_num ,'N', gsts.target_value_num ,'L', gsts.target_value_num ,gsts.target_value_char ) spec_target_value ,DECODE (gqts.test_type ,'T', gsts.max_value_char ,'E', gsts.max_value_num ,'N', gsts.max_value_num ,'L', gsts.max_value_num ,NULL ) spec_max_value ,gmd_qc_test_values_grp.get_test_value_desc (gsts.test_id ,gsts.target_value_num ,gsts.target_value_char ) spec_target_meaning ,gmd_qc_test_values_grp.get_test_value_desc (gsts.test_id ,gsts.min_value_num ,gsts.min_value_char ) spec_min_meaning ,gmd_qc_test_values_grp.get_test_value_desc (gsts.test_id ,gsts.max_value_num ,gsts.max_value_char ) spec_max_meaning FROM gmd_composite_spec_disp gcsd ,gmd_event_spec_disp gesd ,gmd_composite_results gcr ,gmd_qc_tests gqts ,gmd_test_methods gtms ,gmd_spec_tests gsts ,FND_LOOKUP_VALUES_VL fly ,FND_LOOKUP_VALUES_VL fln ,FND_LOOKUP_VALUES_VL test_types ,FND_LOOKUP_VALUES_VL test_priority WHERE gesd.event_spec_disp_id = gcsd.event_spec_disp_id AND gesd.spec_used_for_lot_attrib_ind = 'Y' AND gcsd.latest_ind = 'Y' AND gcsd.composite_spec_disp_id = gcr.composite_spec_disp_id AND gcr.test_id = gqts.test_id AND gsts.spec_id = gesd.spec_id AND gcr.test_id = gsts.test_id AND gsts.test_method_id = gtms.test_method_id AND test_types.lookup_type = 'GMD_QC_TEST_TYPE' AND gqts.test_type = test_types.lookup_code AND test_priority.lookup_type = 'GMD_QC_TEST_PRIORITY' AND gsts.test_priority = test_priority.lookup_code AND fly.lookup_type = 'GMD_QC_YES_NO' AND fly.lookup_code = 'Y' AND fln.lookup_type = 'GMD_QC_YES_NO' AND fln.lookup_code = 'N'

View Text - HTML Formatted

SELECT GCSD.EVENT_SPEC_DISP_ID
, GCSD.COMPOSITE_SPEC_DISP_ID
, GCSD.DISPOSITION
, GQTS.EXPRESSION
, GCSD.LATEST_IND
, GCR.COMPOSITE_RESULT_ID
, GCR.TEST_ID
, GCR.MEAN
, DECODE (GQTS.TEST_TYPE
, 'T'
, GCR.MODE_CHAR
, 'E'
, GCR.MODE_NUM
, 'N'
, GCR.MODE_NUM
, 'L'
, GCR.MODE_NUM
, NULL ) MODE_VALUE
, DECODE (GQTS.TEST_TYPE
, 'T'
, GCR.LOW_CHAR
, 'E'
, GCR.LOW_NUM
, 'N'
, GCR.LOW_NUM
, 'L'
, GCR.LOW_NUM
, NULL ) LOW_VALUE
, DECODE (GQTS.TEST_TYPE
, 'T'
, GCR.HIGH_CHAR
, 'E'
, GCR.HIGH_NUM
, 'N'
, GCR.HIGH_NUM
, 'L'
, GCR.HIGH_NUM
, NULL ) HIGH_VALUE
, GCR.RANGE
, GCR.STANDARD_DEVIATION
, GCR.SAMPLE_TOTAL
, GCR.SAMPLE_CNT_USED
, GCR.NON_VALIDATED_RESULT
, DECODE (GQTS.TEST_TYPE
, 'T'
, GCR.MEDIAN_CHAR
, 'E'
, GCR.MEDIAN_NUM
, 'N'
, GCR.MEDIAN_NUM
, 'L'
, GCR.MEDIAN_NUM
, NULL ) MEDIAN_VALUE
, DECODE (GCR.IN_SPEC_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) IN_SPEC_IND_DISP
, GCR.VALUE_IN_REPORT_PRECISION
, GQTS.TEST_CODE
, GQTS.TEST_DESC
, GQTS.TEST_UNIT
, GQTS.TEST_CLASS
, TEST_TYPES.MEANING TEST_TYPE_DESC
, TEST_PRIORITY.MEANING TEST_PRIORITY_DESC
, GTMS.TEST_METHOD_CODE
, GTMS.TEST_METHOD_DESC
, DECODE (GQTS.TEST_TYPE
, 'T'
, GSTS.MIN_VALUE_CHAR
, 'E'
, GSTS.MIN_VALUE_NUM
, 'N'
, GSTS.MIN_VALUE_NUM
, 'L'
, GSTS.MIN_VALUE_NUM
, NULL ) SPEC_MIN_VALUE
, DECODE (GQTS.TEST_TYPE
, 'T'
, GSTS.TARGET_VALUE_CHAR
, 'E'
, GSTS.TARGET_VALUE_NUM
, 'N'
, GSTS.TARGET_VALUE_NUM
, 'L'
, GSTS.TARGET_VALUE_NUM
, GSTS.TARGET_VALUE_CHAR ) SPEC_TARGET_VALUE
, DECODE (GQTS.TEST_TYPE
, 'T'
, GSTS.MAX_VALUE_CHAR
, 'E'
, GSTS.MAX_VALUE_NUM
, 'N'
, GSTS.MAX_VALUE_NUM
, 'L'
, GSTS.MAX_VALUE_NUM
, NULL ) SPEC_MAX_VALUE
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GSTS.TEST_ID
, GSTS.TARGET_VALUE_NUM
, GSTS.TARGET_VALUE_CHAR ) SPEC_TARGET_MEANING
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GSTS.TEST_ID
, GSTS.MIN_VALUE_NUM
, GSTS.MIN_VALUE_CHAR ) SPEC_MIN_MEANING
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GSTS.TEST_ID
, GSTS.MAX_VALUE_NUM
, GSTS.MAX_VALUE_CHAR ) SPEC_MAX_MEANING
FROM GMD_COMPOSITE_SPEC_DISP GCSD
, GMD_EVENT_SPEC_DISP GESD
, GMD_COMPOSITE_RESULTS GCR
, GMD_QC_TESTS GQTS
, GMD_TEST_METHODS GTMS
, GMD_SPEC_TESTS GSTS
, FND_LOOKUP_VALUES_VL FLY
, FND_LOOKUP_VALUES_VL FLN
, FND_LOOKUP_VALUES_VL TEST_TYPES
, FND_LOOKUP_VALUES_VL TEST_PRIORITY
WHERE GESD.EVENT_SPEC_DISP_ID = GCSD.EVENT_SPEC_DISP_ID
AND GESD.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND GCSD.LATEST_IND = 'Y'
AND GCSD.COMPOSITE_SPEC_DISP_ID = GCR.COMPOSITE_SPEC_DISP_ID
AND GCR.TEST_ID = GQTS.TEST_ID
AND GSTS.SPEC_ID = GESD.SPEC_ID
AND GCR.TEST_ID = GSTS.TEST_ID
AND GSTS.TEST_METHOD_ID = GTMS.TEST_METHOD_ID
AND TEST_TYPES.LOOKUP_TYPE = 'GMD_QC_TEST_TYPE'
AND GQTS.TEST_TYPE = TEST_TYPES.LOOKUP_CODE
AND TEST_PRIORITY.LOOKUP_TYPE = 'GMD_QC_TEST_PRIORITY'
AND GSTS.TEST_PRIORITY = TEST_PRIORITY.LOOKUP_CODE
AND FLY.LOOKUP_TYPE = 'GMD_QC_YES_NO'
AND FLY.LOOKUP_CODE = 'Y'
AND FLN.LOOKUP_TYPE = 'GMD_QC_YES_NO'
AND FLN.LOOKUP_CODE = 'N'