FND Design Data [Home] [Help]

View: PMIFV_QCRESULT_V

Product: PMI - Process Manufacturing Intelligence (Obsolete)
Description: QC Results View
Implementation/DBA Data: ViewAPPS.PMIFV_QCRESULT_V
View Text

SELECT 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 ) TEST_RESULT
, QCTESTS.TEST_UNIT
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0') ACCEPT_ANYWAY
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, 'Y'
, '1V'
, 'Y'
, '2R'
, 'Y'
, 'N') FINAL_MARK
, QCRESULT.TEST_PROVIDER_CODE
, QCRESULT.TESTER
, GSRSLT.WF_RESPONSE
, QCRESULT.ASSAY_RETEST
, QCRESULT.TEST_ID
, QCRESULT.RESULT_ID
, EVT_SPEC_DISP.SPEC_ID
, QCSMPLMST.ORGN_CODE
, QCSMPLMST.SAMPLE_ID
, QCSMPLMST.SAMPLE_NO
, QCSMPLMST.SAMPLE_DESC
, SMP_DISP.LOOKUP_CODE
, SMP_DISP.MEANING
, QCTESTS.TEST_CODE
, QCTESTS.TEST_DESC
, QCSMPLMST.ITEM_ID
, QCSMPLMST.LOT_ID
, QCSMPLMST.WHSE_CODE
, QCSMPLMST.LOCATION
, QCSMPLMST.FORMULA_ID
, QCSMPLMST.FORMULALINE_ID
, QCSMPLMST.ROUTING_ID
, QCSMPLMST.STEP_ID
, QCSMPLMST.OPRN_ID
, QCSMPLMST.CUST_ID
, QCSMPLMST.SHIP_TO_SITE_ID
, QCSMPLMST.SUPPLIER_ID
, QCSMPLMST.BATCH_ID
, QCSMPLMST.STEP_NO
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, 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
, SPEC_VRS.START_DATE
, SPEC_VRS.END_DATE
, SPEC_TESTS.OUT_OF_SPEC_ACTION
, SPEC_TESTS.CHECK_RESULT_INTERVAL
, TO_CHAR(NULL)
, SPEC_VRS.COA_AT_SHIP_IND
, SPEC_VRS.COA_AT_INVOICE_IND
, SPEC_VRS.COA_REQ_FROM_SUPL_IND
, QCTESTS.TEST_OPRN_ID
, QCTESTS.TEST_OPRN_LINE_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, VENDOR.VENDOR_NO
, VENDOR.VENDOR_NAME
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0')
, LOT.LOT_NO
, LOT.SUBLOT_NO
, QCRESULT.CREATED_BY
, QCRESULT.CREATION_DATE
, QCRESULT.LAST_UPDATE_DATE
, QCRESULT.LAST_UPDATED_BY
FROM GMD_SAMPLES QCSMPLMST
, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP
, GMD_ALL_SPEC_VRS SPEC_VRS
, GMD_RESULTS QCRESULT
, GMD_SPEC_RESULTS GSRSLT
, GMD_SPEC_TESTS SPEC_TESTS
, GMD_QC_TESTS_VL QCTESTS
, GEM_LOOKUPS SMP_DISP
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, PO_VEND_MST VENDOR
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
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 EVT_SPEC_DISP.SPEC_VR_ID = SPEC_VRS.SPEC_VR_ID(+)
AND SMP_DISP.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND SMP_DISP.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 EVT_SPEC_DISP.SPEC_ID = SPEC_TESTS.SPEC_ID
AND QCRESULT.TEST_ID = SPEC_TESTS.TEST_ID
AND QCRESULT.TEST_ID = QCTESTS.TEST_ID
AND QCSMPLMST.ITEM_ID = ITEM.ITEM_ID
AND QCSMPLMST.ITEM_ID = LOT.ITEM_ID(+)
AND QCSMPLMST.LOT_ID = LOT.LOT_ID(+)
AND QCSMPLMST.SUPPLIER_ID = VENDOR.VENDOR_ID(+)
AND PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND QCSMPLMST.CUST_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND GSRSLT.ADDITIONAL_TEST_IND IS NULL
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND QCRESULT.DELETE_MARK = 0
AND (QCSMPLMST.ORGN_CODE IS NULL OR QCSMPLMST.ORGN_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) UNION ALL SELECT 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 ) TEST_RESULT
, QCTESTS.TEST_UNIT
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0') ACCEPT_ANYWAY
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, 'Y'
, '1V'
, 'Y'
, '2R'
, 'Y'
, 'N') FINAL_MARK
, QCRESULT.TEST_PROVIDER_CODE
, QCRESULT.TESTER
, GSRSLT.WF_RESPONSE
, QCRESULT.ASSAY_RETEST
, QCRESULT.TEST_ID
, QCRESULT.RESULT_ID
, EVT_SPEC_DISP.SPEC_ID
, QCSMPLMST.ORGN_CODE
, QCSMPLMST.SAMPLE_ID
, QCSMPLMST.SAMPLE_NO
, QCSMPLMST.SAMPLE_DESC
, SMP_DISP.LOOKUP_CODE
, SMP_DISP.MEANING
, QCTESTS.TEST_CODE
, QCTESTS.TEST_DESC
, QCSMPLMST.ITEM_ID
, QCSMPLMST.LOT_ID
, QCSMPLMST.WHSE_CODE
, QCSMPLMST.LOCATION
, QCSMPLMST.FORMULA_ID
, QCSMPLMST.FORMULALINE_ID
, QCSMPLMST.ROUTING_ID
, QCSMPLMST.STEP_ID
, QCSMPLMST.OPRN_ID
, QCSMPLMST.CUST_ID
, QCSMPLMST.SHIP_TO_SITE_ID
, QCSMPLMST.SUPPLIER_ID
, QCSMPLMST.BATCH_ID
, QCSMPLMST.STEP_NO
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL) TEXT_VALUE
, TO_CHAR(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
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, QCTESTS.TEST_OPRN_ID
, QCTESTS.TEST_OPRN_LINE_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, VENDOR.VENDOR_NO
, VENDOR.VENDOR_NAME
, DECODE(GSRSLT.EVALUATION_IND
, '0A'
, '1'
, '0')
, LOT.LOT_NO
, LOT.SUBLOT_NO
, QCRESULT.CREATED_BY
, QCRESULT.CREATION_DATE
, QCRESULT.LAST_UPDATE_DATE
, QCRESULT.LAST_UPDATED_BY
FROM GMD_SAMPLES QCSMPLMST
, GMD_SAMPLE_SPEC_DISP SMP_SPEC_DISP
, GMD_EVENT_SPEC_DISP EVT_SPEC_DISP
, GMD_RESULTS QCRESULT
, GMD_SPEC_RESULTS GSRSLT
, GMD_QC_TESTS_VL QCTESTS
, GEM_LOOKUPS SMP_DISP
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, PO_VEND_MST VENDOR
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
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_DISP.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND SMP_DISP.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 = ITEM.ITEM_ID
AND QCSMPLMST.ITEM_ID = LOT.ITEM_ID(+)
AND QCSMPLMST.LOT_ID = LOT.LOT_ID(+)
AND QCSMPLMST.SUPPLIER_ID = VENDOR.VENDOR_ID(+)
AND PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND QCSMPLMST.CUST_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND QCRESULT.DELETE_MARK = 0
AND GSRSLT.ADDITIONAL_TEST_IND = 'Y'
AND EVT_SPEC_DISP.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND (QCSMPLMST.ORGN_CODE IS NULL OR QCSMPLMST.ORGN_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) WITH READ ONLY

Columns

Name
RESULT_DATE
RESULT
RESULTS_QCUNIT_CODE
ACCEPT
FINAL_MARK
TEST_PROVIDER_CODE
ASSAY_TESTER
WF_RESPONSE
ASSAY_RETEST_FLAG
QCASSY_TYP_ID
QC_RESULT_ID
QC_SPEC_ID
ORGN_CODE
SAMPLE_ID
SAMPLE_NO
SAMPLE_DESC
SAMPLE_STATUS
SAMPLE_STATUS_MEANING
ASSAY_CODE
ASSAY_DESC
ITEM_ID
LOT_ID
WHSE_CODE
LOCATION
FORMULA_ID
FORMULALINE_ID
ROUTING_ID
ROUTINGSTEP_ID
OPRN_ID
CUST_ID
SHIP_TO_SITE_ID
VENDOR_ID
BATCH_ID
ROUTINGSTEP_NO
ITEM_NUMBER
ITEM_DESCRIPTION
ITEM_UOM
ITEM_UOM2
DOC_TYPE
DOC_ID
DOCLINE_ID
CUST_SPECIFICATION
CUST_CERTIFICATION
VENDOR_SPECIFICATION
VENDOR_CERTIFICATION
TEXT_SPEC
TARGET_SPEC
MIN_SPEC
MAX_SPEC
SPEC_QCUNIT_CODE
FROM_DATE
TO_DATE
OUTACTION_CODE
OUTACTION_INTERVAL
PREFERENCE
PRINT_COA_SHIPPED
PRINT_COA_INVOICED
VENDOR_COA_REQUIRED
TEST_OPRN_ID
TEST_OPRN_LINE_ID
CUSTOMER_NO
CUSTOMER_NAME
VENDOR_NO
VENDOR_NAME
ACCEPT_ANYWAY
LOT_NO
SUBLOT_NO
CREATED_BY
CREATION_DATE
LAST_UPDATE_DATE
LAST_UPDATED_BY