VIEW: APPS.GMD_QC_RESULTS_TREE_V

Source

View Text - Preformatted

SELECT s.organization_id, org.organization_code Org_code, s.sample_id, s.sample_no, s.sample_desc, s.date_drawn, s.sampling_event_id, s.source, sd.disposition, smpl_disp_lkup.meaning sample_disposition, r.result_id, r.seq, r.test_id, t.test_code, t.test_type, test_types_lkup.meaning test_type_meaning, t.priority, test_priority_lkup.meaning test_priority, t.test_method_id, tm.test_method_code, r.test_replicate_cnt, r.result_date, r.result_value_num, r.result_value_char, DECODE (t.test_type, 'T', r.result_value_char, 'E',r.result_value_num, 'N', r.result_value_num, 'L', r.result_value_num, r.result_value_char) test_result, r.parent_result_id, r.reserve_sample_id, r.tester_id, sr.evaluation_ind, rslt_eval_lkup.meaning result_evaluation, ed.spec_id, sr.additional_test_ind, t.delete_mark tests_delete_mark, r.delete_mark results_delete_mark, (SELECT optional_ind FROM gmd_spec_tests WHERE spec_id = ed.spec_id AND test_id = r.test_id ) optional_ind, (SELECT test_priority FROM gmd_spec_tests WHERE spec_id = ed.spec_id AND test_id = r.test_id ) spec_test_priority, s.lab_organization_id, lab.organization_code lab_orgn_code FROM gmd_samples s, gmd_sample_spec_disp sd, gmd_event_spec_disp ed, gmd_results r, gmd_spec_results sr, gmd_qc_tests t, gmd_test_methods tm, gem_lookups rslt_eval_lkup, gem_lookups smpl_disp_lkup, gem_lookups test_types_lkup, gem_lookups test_priority_lkup, mtl_parameters org, mtl_parameters lab WHERE s.delete_mark = 0 AND s.sample_id = sd.sample_id AND sd.event_spec_disp_id = ed.event_spec_disp_id AND s.sampling_event_id = ed.sampling_event_id AND ed.spec_used_for_lot_attrib_ind = 'Y' AND smpl_disp_lkup.lookup_code = sd.disposition AND smpl_disp_lkup.lookup_type = 'GMD_QC_SAMPLE_DISP' AND r.sample_id = s.sample_id AND r.result_id = sr.result_id AND sr.event_spec_disp_id = ed.event_spec_disp_id AND rslt_eval_lkup.lookup_code(+) = sr.evaluation_ind AND rslt_eval_lkup.lookup_type(+) = 'GMD_QC_EVALUATION' AND r.test_id = t.test_id AND test_types_lkup.lookup_code = t.test_type AND test_types_lkup.lookup_type = 'GMD_QC_TEST_TYPE' AND test_priority_lkup.lookup_code = t.priority AND test_priority_lkup.lookup_type = 'GMD_QC_TEST_PRIORITY' AND t.test_method_id = tm.test_method_id AND s.organization_id = org.organization_id AND s.lab_organization_id = lab.organization_id(+)

View Text - HTML Formatted

SELECT S.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE ORG_CODE
, S.SAMPLE_ID
, S.SAMPLE_NO
, S.SAMPLE_DESC
, S.DATE_DRAWN
, S.SAMPLING_EVENT_ID
, S.SOURCE
, SD.DISPOSITION
, SMPL_DISP_LKUP.MEANING SAMPLE_DISPOSITION
, R.RESULT_ID
, R.SEQ
, R.TEST_ID
, T.TEST_CODE
, T.TEST_TYPE
, TEST_TYPES_LKUP.MEANING TEST_TYPE_MEANING
, T.PRIORITY
, TEST_PRIORITY_LKUP.MEANING TEST_PRIORITY
, T.TEST_METHOD_ID
, TM.TEST_METHOD_CODE
, R.TEST_REPLICATE_CNT
, R.RESULT_DATE
, R.RESULT_VALUE_NUM
, R.RESULT_VALUE_CHAR
, DECODE (T.TEST_TYPE
, 'T'
, R.RESULT_VALUE_CHAR
, 'E'
, R.RESULT_VALUE_NUM
, 'N'
, R.RESULT_VALUE_NUM
, 'L'
, R.RESULT_VALUE_NUM
, R.RESULT_VALUE_CHAR) TEST_RESULT
, R.PARENT_RESULT_ID
, R.RESERVE_SAMPLE_ID
, R.TESTER_ID
, SR.EVALUATION_IND
, RSLT_EVAL_LKUP.MEANING RESULT_EVALUATION
, ED.SPEC_ID
, SR.ADDITIONAL_TEST_IND
, T.DELETE_MARK TESTS_DELETE_MARK
, R.DELETE_MARK RESULTS_DELETE_MARK
, (SELECT OPTIONAL_IND
FROM GMD_SPEC_TESTS
WHERE SPEC_ID = ED.SPEC_ID
AND TEST_ID = R.TEST_ID ) OPTIONAL_IND
, (SELECT TEST_PRIORITY
FROM GMD_SPEC_TESTS
WHERE SPEC_ID = ED.SPEC_ID
AND TEST_ID = R.TEST_ID ) SPEC_TEST_PRIORITY
, S.LAB_ORGANIZATION_ID
, LAB.ORGANIZATION_CODE LAB_ORGN_CODE
FROM GMD_SAMPLES S
, GMD_SAMPLE_SPEC_DISP SD
, GMD_EVENT_SPEC_DISP ED
, GMD_RESULTS R
, GMD_SPEC_RESULTS SR
, GMD_QC_TESTS T
, GMD_TEST_METHODS TM
, GEM_LOOKUPS RSLT_EVAL_LKUP
, GEM_LOOKUPS SMPL_DISP_LKUP
, GEM_LOOKUPS TEST_TYPES_LKUP
, GEM_LOOKUPS TEST_PRIORITY_LKUP
, MTL_PARAMETERS ORG
, MTL_PARAMETERS LAB
WHERE S.DELETE_MARK = 0
AND S.SAMPLE_ID = SD.SAMPLE_ID
AND SD.EVENT_SPEC_DISP_ID = ED.EVENT_SPEC_DISP_ID
AND S.SAMPLING_EVENT_ID = ED.SAMPLING_EVENT_ID
AND ED.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND SMPL_DISP_LKUP.LOOKUP_CODE = SD.DISPOSITION
AND SMPL_DISP_LKUP.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND R.SAMPLE_ID = S.SAMPLE_ID
AND R.RESULT_ID = SR.RESULT_ID
AND SR.EVENT_SPEC_DISP_ID = ED.EVENT_SPEC_DISP_ID
AND RSLT_EVAL_LKUP.LOOKUP_CODE(+) = SR.EVALUATION_IND
AND RSLT_EVAL_LKUP.LOOKUP_TYPE(+) = 'GMD_QC_EVALUATION'
AND R.TEST_ID = T.TEST_ID
AND TEST_TYPES_LKUP.LOOKUP_CODE = T.TEST_TYPE
AND TEST_TYPES_LKUP.LOOKUP_TYPE = 'GMD_QC_TEST_TYPE'
AND TEST_PRIORITY_LKUP.LOOKUP_CODE = T.PRIORITY
AND TEST_PRIORITY_LKUP.LOOKUP_TYPE = 'GMD_QC_TEST_PRIORITY'
AND T.TEST_METHOD_ID = TM.TEST_METHOD_ID
AND S.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND S.LAB_ORGANIZATION_ID = LAB.ORGANIZATION_ID(+)