VIEW: APPS.GMD_QC_E_SPEC_TESTS_V

Source

View Text - Preformatted

SELECT gsts.spec_id, gsts.test_id, gsts.test_method_id, gsts.seq ,gtms.test_method_code, gsts.test_replicate, gsts.test_qty ,gsts.test_qty_uom ,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 ,gsts.min_value_num, gsts.target_value_num, gsts.max_value_num ,gsts.min_value_char, gsts.target_value_char, gsts.max_value_char ,gsts.below_spec_min, gsts.above_spec_min, gsts.below_spec_max ,gsts.above_spec_max, gsts.below_min_action_code ,gsts.above_min_action_code, gsts.below_max_action_code ,gsts.above_max_action_code ,DECODE (gsts.optional_ind ,'Y', fly.meaning ,fln.meaning ) optional_ind, gsts.display_precision, gsts.report_precision ,gsts.out_of_spec_action, test_priority.meaning test_priority ,DECODE (gsts.retest_lot_expiry_ind ,'Y', fly.meaning ,fln.meaning ) retest_lot_expiry_ind ,exp_error_type.meaning exp_error_type ,DECODE (gsts.print_spec_ind ,'Y', fly.meaning ,fln.meaning ) print_spec_ind ,DECODE (gsts.print_result_ind ,'Y', fly.meaning ,fln.meaning ) print_result_ind ,gsts.test_display, gsts.text_code ,gas.description out_of_spec_action_desc ,0 out_of_spec_action_interval ,gqts.expression ,gqts.test_code, gqts.test_desc, gqts.test_unit, gqts.test_type ,test_types.meaning test_type_desc, gqts.test_class ,gqts.min_value_num test_min_value, gqts.max_value_num test_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 ,gmd_qc_test_values_grp.get_test_value_desc (gsts.test_id ,gqts.min_value_num ,NULL ) test_min_meaning ,gmd_qc_test_values_grp.get_test_value_desc (gsts.test_id ,gqts.max_value_num ,NULL ) test_max_meaning ,gsts.from_base_ind, gsts.exclude_ind, gsts.modified_ind ,gsts.calc_uom_conv_ind ,gsts.to_qty_uom FROM gmd_spec_tests gsts ,gmd_qc_tests gqts ,gmd_test_methods gtms ,mtl_actions gas ,FND_LOOKUP_VALUES_VL fly ,FND_LOOKUP_VALUES_VL fln ,FND_LOOKUP_VALUES_VL test_types ,FND_LOOKUP_VALUES_VL exp_error_type ,FND_LOOKUP_VALUES_VL test_priority WHERE gsts.test_id = gqts.test_id AND gsts.test_method_id = gtms.test_method_id AND gsts.out_of_spec_action = gas.action_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' 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 exp_error_type.lookup_type(+) = 'GMD_QC_EXP_ERROR_TYPE' AND exp_error_type.lookup_code(+) = gsts.exp_error_type

View Text - HTML Formatted

SELECT GSTS.SPEC_ID
, GSTS.TEST_ID
, GSTS.TEST_METHOD_ID
, GSTS.SEQ
, GTMS.TEST_METHOD_CODE
, GSTS.TEST_REPLICATE
, GSTS.TEST_QTY
, GSTS.TEST_QTY_UOM
, 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
, GSTS.MIN_VALUE_NUM
, GSTS.TARGET_VALUE_NUM
, GSTS.MAX_VALUE_NUM
, GSTS.MIN_VALUE_CHAR
, GSTS.TARGET_VALUE_CHAR
, GSTS.MAX_VALUE_CHAR
, GSTS.BELOW_SPEC_MIN
, GSTS.ABOVE_SPEC_MIN
, GSTS.BELOW_SPEC_MAX
, GSTS.ABOVE_SPEC_MAX
, GSTS.BELOW_MIN_ACTION_CODE
, GSTS.ABOVE_MIN_ACTION_CODE
, GSTS.BELOW_MAX_ACTION_CODE
, GSTS.ABOVE_MAX_ACTION_CODE
, DECODE (GSTS.OPTIONAL_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) OPTIONAL_IND
, GSTS.DISPLAY_PRECISION
, GSTS.REPORT_PRECISION
, GSTS.OUT_OF_SPEC_ACTION
, TEST_PRIORITY.MEANING TEST_PRIORITY
, DECODE (GSTS.RETEST_LOT_EXPIRY_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) RETEST_LOT_EXPIRY_IND
, EXP_ERROR_TYPE.MEANING EXP_ERROR_TYPE
, DECODE (GSTS.PRINT_SPEC_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) PRINT_SPEC_IND
, DECODE (GSTS.PRINT_RESULT_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) PRINT_RESULT_IND
, GSTS.TEST_DISPLAY
, GSTS.TEXT_CODE
, GAS.DESCRIPTION OUT_OF_SPEC_ACTION_DESC
, 0 OUT_OF_SPEC_ACTION_INTERVAL
, GQTS.EXPRESSION
, GQTS.TEST_CODE
, GQTS.TEST_DESC
, GQTS.TEST_UNIT
, GQTS.TEST_TYPE
, TEST_TYPES.MEANING TEST_TYPE_DESC
, GQTS.TEST_CLASS
, GQTS.MIN_VALUE_NUM TEST_MIN_VALUE
, GQTS.MAX_VALUE_NUM TEST_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
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GSTS.TEST_ID
, GQTS.MIN_VALUE_NUM
, NULL ) TEST_MIN_MEANING
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GSTS.TEST_ID
, GQTS.MAX_VALUE_NUM
, NULL ) TEST_MAX_MEANING
, GSTS.FROM_BASE_IND
, GSTS.EXCLUDE_IND
, GSTS.MODIFIED_IND
, GSTS.CALC_UOM_CONV_IND
, GSTS.TO_QTY_UOM
FROM GMD_SPEC_TESTS GSTS
, GMD_QC_TESTS GQTS
, GMD_TEST_METHODS GTMS
, MTL_ACTIONS GAS
, FND_LOOKUP_VALUES_VL FLY
, FND_LOOKUP_VALUES_VL FLN
, FND_LOOKUP_VALUES_VL TEST_TYPES
, FND_LOOKUP_VALUES_VL EXP_ERROR_TYPE
, FND_LOOKUP_VALUES_VL TEST_PRIORITY
WHERE GSTS.TEST_ID = GQTS.TEST_ID
AND GSTS.TEST_METHOD_ID = GTMS.TEST_METHOD_ID
AND GSTS.OUT_OF_SPEC_ACTION = GAS.ACTION_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'
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 EXP_ERROR_TYPE.LOOKUP_TYPE(+) = 'GMD_QC_EXP_ERROR_TYPE'
AND EXP_ERROR_TYPE.LOOKUP_CODE(+) = GSTS.EXP_ERROR_TYPE