VIEW: APPS.GMD_QC_E_RESULTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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