View: GMD_QC_E_RESULTS_V

Product:	GMD - Process Manufacturing Product Development
Description:
Implementation/DBA Data:	APPS.GMD_QC_E_RESULTS_V

View Text

SELECT RSLT.UPDATE_INSTANCE_ID RSLT_UPD_INST_ID
, GSRSLT.UPDATE_INSTANCE_ID SPEC_RSLT_UPD_INST_ID
, RSLT.RESULT_ID
, RSLT.SAMPLE_ID
, RSLT.TEST_ID
, RSLT.TEST_REPLICATE_CNT
, RSLT.LAB_ORGANIZATION_ID
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_DATE
, RSLT.TEST_KIT_INV_ITEM_ID
, RSLT.TEST_KIT_LOT_NUMBER
, DECODE (GSRSLT.ADDITIONAL_TEST_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) ADDITIONAL_TEST_IND
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (RSLT.TEST_ID
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_VALUE_CHAR ) RESULT_MEANING
, RSLT.TESTER
, RSLT.TESTER_ID
, RSLT.TEST_PROVIDER_ID
, RSLT.AD_HOC_PRINT_ON_COA_IND
, RSLT.SEQ
, RSLT.RESULT_VALUE_CHAR
, DECODE (GQTS.TEST_TYPE
, 'T'
, RSLT.RESULT_VALUE_CHAR
, 'E'
, RSLT.RESULT_VALUE_NUM
, 'N'
, RSLT.RESULT_VALUE_NUM
, 'L'
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_VALUE_CHAR ) TEST_RESULT
, RSLT.TEST_PROVIDER_CODE
, RSLT.ASSAY_RETEST
, DECODE (RSLT.DELETE_MARK
, 1
, FLY.MEANING
, FLN.MEANING ) RSLT_DELETE_MARK
, RSLT.TEXT_CODE
, GSTS.SPEC_ID
, GSTS.TEST_METHOD_ID
, 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.TEST_REPLICATE
, GSTS.CHECK_RESULT_INTERVAL
, GSTS.PRINT_ON_COA_IND
, GSTS.USE_TO_CONTROL_STEP
, GSTS.OUT_OF_SPEC_ACTION
, GSTS.EXP_ERROR_TYPE
, 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
, DECODE (GSTS.OPTIONAL_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) OPTIONAL_IND
, GSTS.DISPLAY_PRECISION
, GSTS.REPORT_PRECISION
, TEST_PRIORITY.MEANING TEST_PRIORITY
, DECODE (GSTS.RETEST_LOT_EXPIRY_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) RETEST_LOT_EXPIRY_IND
, 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.BELOW_MAX_ACTION_CODE
, GSTS.ABOVE_MAX_ACTION_CODE
, GSTS.TEST_DISPLAY
, GQTS.TEST_CODE
, GQTS.TEST_DESC
, GQTS.TEST_OPRN_LINE_ID
, GQTS.TEST_CLASS
, TEST_TYPES.MEANING TEST_TYPE_DESC
, GQTS.TEST_UNIT TEST_UNIT
, GQTS.MIN_VALUE_NUM TEST_MIN_VALUE
, GQTS.MAX_VALUE_NUM TEST_MAX_VALUE
, GQTS.EXPRESSION EXPRESSION
, GTMS.TEST_METHOD_CODE TEST_METHOD_CODE
, GTMS.TEST_METHOD_DESC TEST_METHOD_DESC
, GTMS.TEST_DURATION TEST_DURATION
, GTMS.RESOURCES RESOURCES
, GSRSLT.EVALUATION_IND EVALUATION_IND
, GSRSLT.ACTION_CODE SPEC_RSLT_ACT_CODE
, DECODE (GSRSLT.IN_SPEC_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) IN_SPEC_IND_DISP
, GSRSLT.VALUE_IN_REPORT_PRECISION VALUE_IN_REPORT_PRECISION
, GESD.EVENT_SPEC_DISP_ID EVENT_SPEC_DISP_ID
, GESD.SAMPLING_EVENT_ID EVENT_SPEC_DISP_SMP_EVT_ID
, GESD.SPEC_VR_ID EVENT_SPEC_VR_ID
, GESD.DISPOSITION EVENT_SPEC_DISP
, DECODE (GESD.SPEC_USED_FOR_LOT_ATTRIB_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) EVT_SPEC_LOT_ATTR_IND_DISP
, DECODE (GESD.DELETE_MARK
, 'Y'
, FLY.MEANING
, FLN.MEANING ) EVT_SPEC_DISP_DEL_MARK
, 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
, RSLT.CONSUMED_QTY CONSUMED_QTY
, RSLT.ACTUAL_RESOURCE ACTUAL_RESOURCE
, RSLT.ACTUAL_RESOURCE_INSTANCE ACTUAL_RESOURCE_INSTANCE
, RSLT.PLANNED_RESOURCE PLANNED_RESOURCE
, RSLT.PLANNED_RESOURCE_INSTANCE PLANNED_RESOURCE_INSTANCE
, RSLT.PLANNED_RESULT_DATE PLANNED_RESULT_DATE
, RSLT.TEST_QTY TEST_QTY
, RSLT.TEST_QTY_UOM TEST_UOM
, RSLT.PARENT_RESULT_ID PARENT_RESULT_ID
, RSLT.RESERVE_SAMPLE_ID RESERVE_SAMPLE_ID
, DECODE((MP.ORGANIZATION_CODE||'-'||SAMPLE1.SAMPLE_NO)
, '-'
, NULL
, (MP.ORGANIZATION_CODE||'-'||SAMPLE1.SAMPLE_NO)) PARENT_SAMPLE_NO
, SAMPLE2.SAMPLE_NO RESERVE_SAMPLE_NO
, GSTS.TARGET_VALUE_NUM TEST_TARGET_VALUE
FROM GMD_RESULTS RSLT
, GMD_RESULTS RSLT1
, GMD_SPEC_TESTS GSTS
, GMD_QC_TESTS GQTS
, GMD_TEST_METHODS GTMS
, GMD_SPEC_RESULTS GSRSLT
, GMD_EVENT_SPEC_DISP GESD
, FND_LOOKUP_VALUES_VL TEST_TYPES
, FND_LOOKUP_VALUES_VL TEST_PRIORITY
, FND_LOOKUP_VALUES_VL FLY
, FND_LOOKUP_VALUES_VL FLN
, GMD_SAMPLES SAMPLE1
, GMD_SAMPLES SAMPLE2
, MTL_PARAMETERS MP
WHERE GESD.EVENT_SPEC_DISP_ID = GSRSLT.EVENT_SPEC_DISP_ID
AND GSRSLT.RESULT_ID = RSLT.RESULT_ID
AND RSLT.TEST_ID = GQTS.TEST_ID
AND RSLT.TEST_ID = GSTS.TEST_ID
AND GSTS.SPEC_ID = GESD.SPEC_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 TEST_PRIORITY.LOOKUP_CODE = GSTS.TEST_PRIORITY
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 GESD.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND RSLT.PARENT_RESULT_ID = RSLT1.RESULT_ID(+)
AND RSLT1.SAMPLE_ID = SAMPLE1.SAMPLE_ID(+)
AND SAMPLE1.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND RSLT.RESERVE_SAMPLE_ID = SAMPLE2.SAMPLE_ID(+) UNION SELECT RSLT.UPDATE_INSTANCE_ID RSLT_UPD_INST_ID
, GSRSLT.UPDATE_INSTANCE_ID SPEC_RSLT_UPD_INST_ID
, RSLT.RESULT_ID
, RSLT.SAMPLE_ID
, RSLT.TEST_ID
, RSLT.TEST_REPLICATE_CNT
, RSLT.LAB_ORGANIZATION_ID
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_DATE
, RSLT.TEST_KIT_INV_ITEM_ID
, RSLT.TEST_KIT_LOT_NUMBER
, DECODE (GSRSLT.ADDITIONAL_TEST_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) ADDITIONAL_TEST_IND
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (RSLT.TEST_ID
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_VALUE_CHAR ) RESULT_MEANING
, RSLT.TESTER
, RSLT.TESTER_ID
, RSLT.TEST_PROVIDER_ID
, RSLT.AD_HOC_PRINT_ON_COA_IND
, RSLT.SEQ
, RSLT.RESULT_VALUE_CHAR
, DECODE (GQTS.TEST_TYPE
, 'T'
, RSLT.RESULT_VALUE_CHAR
, 'E'
, RSLT.RESULT_VALUE_NUM
, 'N'
, RSLT.RESULT_VALUE_NUM
, 'L'
, RSLT.RESULT_VALUE_NUM
, RSLT.RESULT_VALUE_CHAR ) TEST_RESULT
, RSLT.TEST_PROVIDER_CODE
, RSLT.ASSAY_RETEST
, DECODE (RSLT.DELETE_MARK
, 1
, FLY.MEANING
, FLN.MEANING ) RSLT_DELETE_MARK
, RSLT.TEXT_CODE
, 0
, GQTS.TEST_METHOD_ID
, GTMS.TEST_QTY_UOM
, NULL SPEC_MIN_VALUE
, NULL SPEC_TARGET_VALUE
, NULL SPEC_MAX_VALUE
, GTMS.TEST_REPLICATE
, 0
, RSLT.AD_HOC_PRINT_ON_COA_IND
, NULL
, NULL
, GQTS.EXP_ERROR_TYPE
, GQTS.BELOW_SPEC_MIN
, GQTS.ABOVE_SPEC_MIN
, GQTS.BELOW_SPEC_MAX
, GQTS.ABOVE_SPEC_MAX
, GQTS.BELOW_MIN_ACTION_CODE
, GQTS.ABOVE_MIN_ACTION_CODE
, NULL OPTIONAL_IND
, GQTS.DISPLAY_PRECISION
, GQTS.REPORT_PRECISION
, TEST_PRIORITY.MEANING TEST_PRIORITY
, NULL RETEST_LOT_EXPIRY_IND
, NULL PRINT_SPEC_IND
, NULL PRINT_RESULT_IND
, GQTS.BELOW_MAX_ACTION_CODE
, GQTS.ABOVE_MAX_ACTION_CODE
, NULL
, GQTS.TEST_CODE
, GQTS.TEST_DESC
, GQTS.TEST_OPRN_LINE_ID
, GQTS.TEST_CLASS
, TEST_TYPES.MEANING TEST_TYPE_DESC
, GQTS.TEST_UNIT TEST_UNIT
, GQTS.MIN_VALUE_NUM TEST_MIN_VALUE
, GQTS.MAX_VALUE_NUM TEST_MAX_VALUE
, GQTS.EXPRESSION EXPRESSION
, GTMS.TEST_METHOD_CODE TEST_METHOD_CODE
, GTMS.TEST_METHOD_DESC TEST_METHOD_DESC
, GTMS.TEST_DURATION TEST_DURATION
, GTMS.RESOURCES RESOURCES
, GSRSLT.EVALUATION_IND EVALUATION_IND
, GSRSLT.ACTION_CODE SPEC_RSLT_ACT_CODE
, DECODE (GSRSLT.IN_SPEC_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) IN_SPEC_IND_DISP
, GSRSLT.VALUE_IN_REPORT_PRECISION VALUE_IN_REPORT_PRECISION
, GESD.EVENT_SPEC_DISP_ID EVENT_SPEC_DISP_ID
, GESD.SAMPLING_EVENT_ID EVENT_SPEC_DISP_SMP_EVT_ID
, GESD.SPEC_VR_ID EVENT_SPEC_VR_ID
, GESD.DISPOSITION EVENT_SPEC_DISP
, DECODE (GESD.SPEC_USED_FOR_LOT_ATTRIB_IND
, 'Y'
, FLY.MEANING
, FLN.MEANING ) EVT_SPEC_LOT_ATTR_IND_DISP
, DECODE (GESD.DELETE_MARK
, 'Y'
, FLY.MEANING
, FLN.MEANING ) EVT_SPEC_DISP_DEL_MARK
, NULL SPEC_TARGET_MEANING
, NULL SPEC_MIN_MEANING
, NULL SPEC_MAX_MEANING
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GQTS.TEST_ID
, GQTS.MIN_VALUE_NUM
, NULL ) TEST_MIN_MEANING
, GMD_QC_TEST_VALUES_GRP.GET_TEST_VALUE_DESC (GQTS.TEST_ID
, GQTS.MAX_VALUE_NUM
, NULL ) TEST_MAX_MEANING
, RSLT.CONSUMED_QTY CONSUMED_QTY
, RSLT.ACTUAL_RESOURCE ACTUAL_RESOURCE
, RSLT.ACTUAL_RESOURCE_INSTANCE ACTUAL_RESOURCE_INSTANCE
, RSLT.PLANNED_RESOURCE PLANNED_RESOURCE
, RSLT.PLANNED_RESOURCE_INSTANCE PLANNED_RESOURCE_INSTANCE
, RSLT.PLANNED_RESULT_DATE PLANNED_RESULT_DATE
, RSLT.TEST_QTY TEST_QTY
, RSLT.TEST_QTY_UOM TEST_UOM
, RSLT.PARENT_RESULT_ID PARENT_RESULT_ID
, RSLT.RESERVE_SAMPLE_ID RESERVE_SAMPLE_ID
, DECODE((MP.ORGANIZATION_CODE||'-'||SAMPLE1.SAMPLE_NO)
, '-'
, NULL
, (MP.ORGANIZATION_CODE||'-'||SAMPLE1.SAMPLE_NO)) PARENT_SAMPLE_NO
, SAMPLE2.SAMPLE_NO RESERVE_SAMPLE_NO
, NULL TEST_TARGET_VALUE
FROM GMD_RESULTS RSLT
, GMD_RESULTS RSLT1
, GMD_QC_TESTS GQTS
, GMD_TEST_METHODS GTMS
, GMD_SPEC_RESULTS GSRSLT
, GMD_EVENT_SPEC_DISP GESD
, FND_LOOKUP_VALUES_VL TEST_TYPES
, FND_LOOKUP_VALUES_VL TEST_PRIORITY
, FND_LOOKUP_VALUES_VL FLY
, FND_LOOKUP_VALUES_VL FLN
, GMD_SAMPLES SAMPLE1
, GMD_SAMPLES SAMPLE2
, MTL_PARAMETERS MP
WHERE GESD.EVENT_SPEC_DISP_ID = GSRSLT.EVENT_SPEC_DISP_ID
AND GSRSLT.RESULT_ID = RSLT.RESULT_ID
AND RSLT.TEST_ID = GQTS.TEST_ID
AND GSRSLT.ADDITIONAL_TEST_IND = 'Y'
AND GQTS.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 TEST_PRIORITY.LOOKUP_CODE = GQTS.PRIORITY
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 GESD.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND RSLT.PARENT_RESULT_ID = RSLT1.RESULT_ID(+)
AND RSLT1.SAMPLE_ID = SAMPLE1.SAMPLE_ID(+)
AND SAMPLE1.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND RSLT.RESERVE_SAMPLE_ID = SAMPLE2.SAMPLE_ID(+) ORDER BY 18

Columns