VIEW: APPS.PMIFV_BTCH_QCRSLT_V

Source

View Text - Preformatted

SELECT OrgnMst.CO_CODE, BtchHdr.PLANT_CODE, BtchHdr.WIP_WHSE_CODE, BtchHdr.ACTUAL_CMPLT_DATE, QCresult.SAMPLE_ID, QCSMPLMST.SAMPLE_NO, QCSMPLMST.SAMPLE_DESC, SMP_SPEC_DISP.DISPOSITION, SMP_SPEC_DISP_MEANING.Meaning, QCTESTS.TEST_CODE, QCresult.RESULT_DATE, DECODE(QCTESTS.TEST_TYPE, 'T',QCresult.RESULT_VALUE_CHAR, 'E',QCresult.RESULT_VALUE_NUM, 'N',QCresult.RESULT_VALUE_NUM, 'L',QCresult.RESULT_VALUE_NUM , QCresult.RESULT_VALUE_CHAR ) RESULT , DECODE(QCTESTS.TEST_TYPE, 'T', SPEC_TESTS.TARGET_VALUE_CHAR, 'E', NULL, 'N', NULL, 'L', NULL, SPEC_TESTS.TARGET_VALUE_CHAR ) TEXT_VALUE , DECODE(QCTESTS.TEST_TYPE, 'T', NULL, 'E', SPEC_TESTS.TARGET_VALUE_NUM, 'N', SPEC_TESTS.TARGET_VALUE_NUM, 'L', SPEC_TESTS.TARGET_VALUE_NUM, NULL) TARGET_VALUE , DECODE(QCTESTS.TEST_TYPE, 'T', SPEC_TESTS.MIN_VALUE_CHAR, 'E', SPEC_TESTS.MIN_VALUE_NUM, 'N', SPEC_TESTS.MIN_VALUE_NUM, 'L', SPEC_TESTS.MIN_VALUE_NUM, NULL) MIN_VALUE , DECODE(QCTESTS.TEST_TYPE, 'T', SPEC_TESTS.MAX_VALUE_CHAR, 'E', SPEC_TESTS.MAX_VALUE_NUM, 'N', SPEC_TESTS.MAX_VALUE_NUM, 'L', SPEC_TESTS.MAX_VALUE_NUM, NULL) MAX_VALUE , QCTESTS.TEST_UNIT, decode(gsrslt.evaluation_ind,'0A','1','0') accept_anyway, QCSMPLMST.ITEM_ID, ItemMst.ITEM_NO, ItemMst.item_desc1, QCSMPLMST.BATCH_ID, BtchHdr.BATCH_NO, PMI_PRODUCTION_SUM.NO_OF_TIMES_ADJUSTED(BtchHdr.BATCH_ID), QCSMPLMST.ROUTING_ID, RoutHdr.Routing_NO, RoutHdr.ROUTING_VERS, QCSMPLMST.STEP_ID, QCSMPLMST.STEP_NO, QCSMPLMST.OPRN_ID, OprnMst.OPRN_NO, Calendr.PERIOD_YEAR, Calendr.PERIOD_NUM, Calendr.PERIOD_NAME FROM GMD_RESULTS QCresult, GMD_SAMPLES QCSMPLMST, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP, gmd_spec_results gsrslt, GMD_SPEC_TESTS SPEC_TESTS, GMD_QC_TESTS_VL QCTESTS, GEM_LOOKUPS SMP_SPEC_DISP_MEANING, GME_BATCH_HEADER BtchHdr, ic_item_mst itemMst, FM_ROUT_HDR RoutHdr, FM_OPRN_MST OprnMst, SY_ORGN_MST OrgnMst, FM_FORM_MST_B Formula, PMI_GL_CALENDAR_V Calendr WHERE QCSMPLMST.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID AND QCSMPLMST.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE= 'GMD_QC_SAMPLE_DISP' AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE= SMP_SPEC_DISP.DISPOSITION AND QCSMPLMST.SAMPLE_ID = QCresult.SAMPLE_ID AND gsrslt.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID AND gsrslt.RESULT_ID = QCresult.RESULT_ID AND QCresult.TEST_ID = QCTESTS.TEST_ID AND QCSMPLMST.item_id = itemMst.item_id AND EVT_SPEC_DISP.SPEC_ID = SPEC_TESTS.SPEC_ID AND QCresult.TEST_ID = SPEC_TESTS.TEST_ID AND QCSMPLMST.batch_id = BtchHdr.batch_id AND QCSMPLMST.ROUTING_ID = RoutHdr.routing_id(+) AND QCSMPLMST.OPRN_ID = OprnMst.oprn_id(+) AND BtchHdr.PLANT_CODE = OrgnMst.orgn_code AND OrgnMst.CO_CODE = calendr.CO_CODE AND gsrslt.additional_test_ind IS NULL AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND trunc(BtchHdr.ACTUAL_CMPLT_DATE) between Calendr.start_date and Calendr.end_date AND QCresult.delete_mark = 0 and (BtchHdr.PLANT_CODE IN (SELECT secorg.orgn_code FROM sy_orgn_mst secorg WHERE PMI_SECURITY_PKG.show_record(secorg.orgn_code) = 'TRUE')) AND Formula.formula_id = BtchHdr.formula_id UNION ALL SELECT OrgnMst.CO_CODE, BtchHdr.PLANT_CODE, BtchHdr.WIP_WHSE_CODE, BtchHdr.ACTUAL_CMPLT_DATE, QCresult.SAMPLE_ID, QCSMPLMST.SAMPLE_NO, QCSMPLMST.SAMPLE_DESC, SMP_SPEC_DISP.DISPOSITION, SMP_SPEC_DISP_MEANING.Meaning, QCTESTS.TEST_CODE, QCresult.RESULT_DATE, DECODE(QCTESTS.TEST_TYPE, 'T',QCresult.RESULT_VALUE_CHAR, 'E',QCresult.RESULT_VALUE_NUM, 'N',QCresult.RESULT_VALUE_NUM, 'L',QCresult.RESULT_VALUE_NUM , QCresult.RESULT_VALUE_CHAR ) RESULT , null TEXT_VALUE , NULL TARGET_VALUE , DECODE(QCTESTS.TEST_TYPE, 'T', NULL, 'E', QCTESTS.MIN_VALUE_NUM, 'N', QCTESTS.MIN_VALUE_NUM, 'L', QCTESTS.MIN_VALUE_NUM, NULL) MIN_VALUE , DECODE(QCTESTS.TEST_TYPE, 'T', NULL, 'E', QCTESTS.MAX_VALUE_NUM, 'N', QCTESTS.MAX_VALUE_NUM, 'L', QCTESTS.MAX_VALUE_NUM, NULL) MAX_VALUE , QCTESTS.TEST_UNIT, decode(gsrslt.evaluation_ind,'0A','1','0') accept_anyway, QCSMPLMST.ITEM_ID, ItemMst.ITEM_NO, ItemMst.item_desc1, QCSMPLMST.BATCH_ID, BtchHdr.BATCH_NO, PMI_PRODUCTION_SUM.NO_OF_TIMES_ADJUSTED(BtchHdr.BATCH_ID), QCSMPLMST.ROUTING_ID, RoutHdr.Routing_NO, RoutHdr.ROUTING_VERS, QCSMPLMST.STEP_ID, QCSMPLMST.STEP_NO, QCSMPLMST.OPRN_ID, OprnMst.OPRN_NO, Calendr.PERIOD_YEAR, Calendr.PERIOD_NUM, Calendr.PERIOD_NAME FROM GMD_RESULTS QCresult, GMD_SAMPLES QCSMPLMST, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP, gmd_spec_results gsrslt, GMD_QC_TESTS_VL QCTESTS, GEM_LOOKUPS SMP_SPEC_DISP_MEANING, GME_BATCH_HEADER BtchHdr, ic_item_mst itemMst, FM_ROUT_HDR RoutHdr, FM_OPRN_MST OprnMst, SY_ORGN_MST OrgnMst, FM_FORM_MST_B Formula, PMI_GL_CALENDAR_V Calendr WHERE QCSMPLMST.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID AND QCSMPLMST.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE= 'GMD_QC_SAMPLE_DISP' AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE= SMP_SPEC_DISP.DISPOSITION AND QCSMPLMST.SAMPLE_ID = QCresult.SAMPLE_ID AND gsrslt.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID AND gsrslt.RESULT_ID = QCresult.RESULT_ID AND QCresult.TEST_ID = QCTESTS.TEST_ID AND QCSMPLMST.item_id = itemMst.item_id AND QCSMPLMST.batch_id = BtchHdr.batch_id AND QCSMPLMST.ROUTING_ID = RoutHdr.routing_id(+) AND QCSMPLMST.OPRN_ID = OprnMst.oprn_id(+) AND BtchHdr.PLANT_CODE = OrgnMst.orgn_code AND OrgnMst.CO_CODE = calendr.CO_CODE AND gsrslt.additional_test_ind = 'Y' AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y' AND trunc(BtchHdr.ACTUAL_CMPLT_DATE) between Calendr.start_date and Calendr.end_date AND QCresult.delete_mark = 0 and (BtchHdr.PLANT_CODE IN (SELECT secorg.orgn_code FROM sy_orgn_mst secorg WHERE PMI_SECURITY_PKG.show_record(secorg.orgn_code) = 'TRUE')) AND Formula.formula_id = BtchHdr.formula_id with read only

View Text - HTML Formatted

SELECT ORGNMST.CO_CODE
, BTCHHDR.PLANT_CODE
, BTCHHDR.WIP_WHSE_CODE
, BTCHHDR.ACTUAL_CMPLT_DATE
, QCRESULT.SAMPLE_ID
, QCSMPLMST.SAMPLE_NO
, QCSMPLMST.SAMPLE_DESC
, SMP_SPEC_DISP.DISPOSITION
, SMP_SPEC_DISP_MEANING.MEANING
, QCTESTS.TEST_CODE
, QCRESULT.RESULT_DATE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, QCRESULT.RESULT_VALUE_CHAR
, 'E'
, QCRESULT.RESULT_VALUE_NUM
, 'N'
, QCRESULT.RESULT_VALUE_NUM
, 'L'
, QCRESULT.RESULT_VALUE_NUM
, QCRESULT.RESULT_VALUE_CHAR ) RESULT
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, SPEC_TESTS.TARGET_VALUE_CHAR
, 'E'
, NULL
, 'N'
, NULL
, 'L'
, NULL
, SPEC_TESTS.TARGET_VALUE_CHAR ) TEXT_VALUE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, NULL
, 'E'
, SPEC_TESTS.TARGET_VALUE_NUM
, 'N'
, SPEC_TESTS.TARGET_VALUE_NUM
, 'L'
, SPEC_TESTS.TARGET_VALUE_NUM
, NULL) TARGET_VALUE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, SPEC_TESTS.MIN_VALUE_CHAR
, 'E'
, SPEC_TESTS.MIN_VALUE_NUM
, 'N'
, SPEC_TESTS.MIN_VALUE_NUM
, 'L'
, SPEC_TESTS.MIN_VALUE_NUM
, NULL) MIN_VALUE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, SPEC_TESTS.MAX_VALUE_CHAR
, 'E'
, SPEC_TESTS.MAX_VALUE_NUM
, 'N'
, SPEC_TESTS.MAX_VALUE_NUM
, 'L'
, SPEC_TESTS.MAX_VALUE_NUM
, NULL) MAX_VALUE
, QCTESTS.TEST_UNIT
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0') ACCEPT_ANYWAY
, QCSMPLMST.ITEM_ID
, ITEMMST.ITEM_NO
, ITEMMST.ITEM_DESC1
, QCSMPLMST.BATCH_ID
, BTCHHDR.BATCH_NO
, PMI_PRODUCTION_SUM.NO_OF_TIMES_ADJUSTED(BTCHHDR.BATCH_ID)
, QCSMPLMST.ROUTING_ID
, ROUTHDR.ROUTING_NO
, ROUTHDR.ROUTING_VERS
, QCSMPLMST.STEP_ID
, QCSMPLMST.STEP_NO
, QCSMPLMST.OPRN_ID
, OPRNMST.OPRN_NO
, CALENDR.PERIOD_YEAR
, CALENDR.PERIOD_NUM
, CALENDR.PERIOD_NAME
FROM GMD_RESULTS QCRESULT
, GMD_SAMPLES QCSMPLMST
, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP
, GMD_SPEC_RESULTS GSRSLT
, GMD_SPEC_TESTS SPEC_TESTS
, GMD_QC_TESTS_VL QCTESTS
, GEM_LOOKUPS SMP_SPEC_DISP_MEANING
, GME_BATCH_HEADER BTCHHDR
, IC_ITEM_MST ITEMMST
, FM_ROUT_HDR ROUTHDR
, FM_OPRN_MST OPRNMST
, SY_ORGN_MST ORGNMST
, FM_FORM_MST_B FORMULA
, PMI_GL_CALENDAR_V CALENDR
WHERE QCSMPLMST.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID
AND QCSMPLMST.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID
AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID
AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE= 'GMD_QC_SAMPLE_DISP'
AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE= SMP_SPEC_DISP.DISPOSITION
AND QCSMPLMST.SAMPLE_ID = QCRESULT.SAMPLE_ID
AND GSRSLT.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID
AND GSRSLT.RESULT_ID = QCRESULT.RESULT_ID
AND QCRESULT.TEST_ID = QCTESTS.TEST_ID
AND QCSMPLMST.ITEM_ID = ITEMMST.ITEM_ID
AND EVT_SPEC_DISP.SPEC_ID = SPEC_TESTS.SPEC_ID
AND QCRESULT.TEST_ID = SPEC_TESTS.TEST_ID
AND QCSMPLMST.BATCH_ID = BTCHHDR.BATCH_ID
AND QCSMPLMST.ROUTING_ID = ROUTHDR.ROUTING_ID(+)
AND QCSMPLMST.OPRN_ID = OPRNMST.OPRN_ID(+)
AND BTCHHDR.PLANT_CODE = ORGNMST.ORGN_CODE
AND ORGNMST.CO_CODE = CALENDR.CO_CODE
AND GSRSLT.ADDITIONAL_TEST_IND IS NULL
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND TRUNC(BTCHHDR.ACTUAL_CMPLT_DATE) BETWEEN CALENDR.START_DATE
AND CALENDR.END_DATE
AND QCRESULT.DELETE_MARK = 0
AND (BTCHHDR.PLANT_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE'))
AND FORMULA.FORMULA_ID = BTCHHDR.FORMULA_ID UNION ALL SELECT ORGNMST.CO_CODE
, BTCHHDR.PLANT_CODE
, BTCHHDR.WIP_WHSE_CODE
, BTCHHDR.ACTUAL_CMPLT_DATE
, QCRESULT.SAMPLE_ID
, QCSMPLMST.SAMPLE_NO
, QCSMPLMST.SAMPLE_DESC
, SMP_SPEC_DISP.DISPOSITION
, SMP_SPEC_DISP_MEANING.MEANING
, QCTESTS.TEST_CODE
, QCRESULT.RESULT_DATE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, QCRESULT.RESULT_VALUE_CHAR
, 'E'
, QCRESULT.RESULT_VALUE_NUM
, 'N'
, QCRESULT.RESULT_VALUE_NUM
, 'L'
, QCRESULT.RESULT_VALUE_NUM
, QCRESULT.RESULT_VALUE_CHAR ) RESULT
, NULL TEXT_VALUE
, NULL TARGET_VALUE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, NULL
, 'E'
, QCTESTS.MIN_VALUE_NUM
, 'N'
, QCTESTS.MIN_VALUE_NUM
, 'L'
, QCTESTS.MIN_VALUE_NUM
, NULL) MIN_VALUE
, DECODE(QCTESTS.TEST_TYPE
, 'T'
, NULL
, 'E'
, QCTESTS.MAX_VALUE_NUM
, 'N'
, QCTESTS.MAX_VALUE_NUM
, 'L'
, QCTESTS.MAX_VALUE_NUM
, NULL) MAX_VALUE
, QCTESTS.TEST_UNIT
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0') ACCEPT_ANYWAY
, QCSMPLMST.ITEM_ID
, ITEMMST.ITEM_NO
, ITEMMST.ITEM_DESC1
, QCSMPLMST.BATCH_ID
, BTCHHDR.BATCH_NO
, PMI_PRODUCTION_SUM.NO_OF_TIMES_ADJUSTED(BTCHHDR.BATCH_ID)
, QCSMPLMST.ROUTING_ID
, ROUTHDR.ROUTING_NO
, ROUTHDR.ROUTING_VERS
, QCSMPLMST.STEP_ID
, QCSMPLMST.STEP_NO
, QCSMPLMST.OPRN_ID
, OPRNMST.OPRN_NO
, CALENDR.PERIOD_YEAR
, CALENDR.PERIOD_NUM
, CALENDR.PERIOD_NAME
FROM GMD_RESULTS QCRESULT
, GMD_SAMPLES QCSMPLMST
, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP
, GMD_SPEC_RESULTS GSRSLT
, GMD_QC_TESTS_VL QCTESTS
, GEM_LOOKUPS SMP_SPEC_DISP_MEANING
, GME_BATCH_HEADER BTCHHDR
, IC_ITEM_MST ITEMMST
, FM_ROUT_HDR ROUTHDR
, FM_OPRN_MST OPRNMST
, SY_ORGN_MST ORGNMST
, FM_FORM_MST_B FORMULA
, PMI_GL_CALENDAR_V CALENDR
WHERE QCSMPLMST.SAMPLE_ID = SMP_SPEC_DISP.SAMPLE_ID
AND QCSMPLMST.SAMPLING_EVENT_ID = EVT_SPEC_DISP.SAMPLING_EVENT_ID
AND SMP_SPEC_DISP.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID
AND SMP_SPEC_DISP_MEANING.LOOKUP_TYPE= 'GMD_QC_SAMPLE_DISP'
AND SMP_SPEC_DISP_MEANING.LOOKUP_CODE= SMP_SPEC_DISP.DISPOSITION
AND QCSMPLMST.SAMPLE_ID = QCRESULT.SAMPLE_ID
AND GSRSLT.EVENT_SPEC_DISP_ID = EVT_SPEC_DISP.EVENT_SPEC_DISP_ID
AND GSRSLT.RESULT_ID = QCRESULT.RESULT_ID
AND QCRESULT.TEST_ID = QCTESTS.TEST_ID
AND QCSMPLMST.ITEM_ID = ITEMMST.ITEM_ID
AND QCSMPLMST.BATCH_ID = BTCHHDR.BATCH_ID
AND QCSMPLMST.ROUTING_ID = ROUTHDR.ROUTING_ID(+)
AND QCSMPLMST.OPRN_ID = OPRNMST.OPRN_ID(+)
AND BTCHHDR.PLANT_CODE = ORGNMST.ORGN_CODE
AND ORGNMST.CO_CODE = CALENDR.CO_CODE
AND GSRSLT.ADDITIONAL_TEST_IND = 'Y'
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND TRUNC(BTCHHDR.ACTUAL_CMPLT_DATE) BETWEEN CALENDR.START_DATE
AND CALENDR.END_DATE
AND QCRESULT.DELETE_MARK = 0
AND (BTCHHDR.PLANT_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE'))
AND FORMULA.FORMULA_ID = BTCHHDR.FORMULA_ID WITH READ ONLY