View: PMIFV_BTCH_QCRSLT_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	Batch QC Results
Implementation/DBA Data:	APPS.PMIFV_BTCH_QCRSLT_V

View Text

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

Columns