ECC Process Quality, Quality Information

Description

Categories: Enterprise Command Center

Imported from Enterprise Command Center
This data set holds information about process manufacturing quality samples and tests.
Dataset Key: gmd-quality
Query Procedure: GMD_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: gmd_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

SQL 102 lines

select
x.*
from
(
SELECT * FROM ( SELECT ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION,
    SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME,
    ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM,
    PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO,
    MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO,
    ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID,
    SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID,
    SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE,
    LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME,
    STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE,
    EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION, IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM,
    TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD,
    RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER,
    TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE,
    IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE,
    IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING,
    IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE,CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE  FROM (SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(cus) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION,
    SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME,
    ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM,
    PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO,
    MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO,
    ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID,
    SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID,
    SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE,
    LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME,
    STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE,
    EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM,
    TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD,
    RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER,
    TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE,
    IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE,
    IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING,
    IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE
    FROM OPM_ECC_CUS_QUALITIES_V cus, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE cus.sample_id = tmp.sample_id
        UNION ALL
    SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(inv) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION,
    SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME,
    ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM,
    PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO,
    MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO,
    ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID,
    SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID,
    SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE,
    LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME,
    STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE,
    EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM,
    TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD,
    RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER,
    TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE,
    IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE,
    IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING,
    IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE
    FROM OPM_ECC_INV_QUALITIES_V inv, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE inv.sample_id = tmp.sample_id
        UNION ALL
    SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(sup) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION,
    SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME,
    ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM,
    PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO,
    MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO,
    ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID,
    SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID,
    SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE,
    LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME,
    STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE,
    EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM,
    TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD,
    RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER,
    TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE,
    IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE,
    IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING,
    IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE
    FROM OPM_ECC_SUP_QUALITIES_V sup, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE sup.sample_id = tmp.sample_id
        UNION ALL
    SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(wip) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION,
    SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME,
    ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM,
    PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO,
    MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO,
    ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID,
    SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID,
    SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE,
    LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME,
    STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE,
    EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM,
    TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD,
    RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER,
    TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE,
    IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE,
    IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING,
    IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE
    FROM OPM_ECC_WIP_QUALITIES_V wip, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE wip.sample_id = tmp.sample_id
    )  where 1=1 and language in ('US') )PIVOT (max(LAB_ORG_NAME) AS LAB_ORG_NAME  ,max(LAB_ORG_CODE) AS LAB_ORG_CODE,max(ORGANIZATION_NAME) AS ORGANIZATION_NAME,max(SAMPLE_DISPOSITION_DESC) AS SAMPLE_DISPOSITION_DESC,
         max(SAMPLE_SOURCE_DESC) AS SAMPLE_SOURCE_DESC,max(SAMPLE_PRIORITY_DESC) AS SAMPLE_PRIORITY_DESC,
         max(SAMPLE_GROUP_DIS_DESC) as SAMPLE_GROUP_DIS_DESC ,max(MATERIAL_LINE_TYPE_DESC) as MATERIAL_LINE_TYPE_DESC, max(ORDER_TYPE) as ORDER_TYPE,max(TEST_PRIORITY_DESC) as TEST_PRIORITY_DESC,
         max(TEST_LAB_ORG_NAME) as TEST_LAB_ORG_NAME,max(EVALUATION_DESC) as EVALUATION_DESC,max(ACTION) as ACTION,max(IN_SPEC_IND_DESC) as IN_SPEC_IND_DESC for LANGUAGE in ('US' "US"))
) x
where
2=2

select x.* from ( SELECT * FROM ( SELECT ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION, SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME, ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM, PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO, MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO, ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID, SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID, SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE, EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION, IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM, TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD, RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER, TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE, IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE, IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING, IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE,CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE FROM (SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(cus) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION, SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME, ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM, PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO, MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO, ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID, SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID, SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE, EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM, TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD, RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER, TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE, IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE, IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING, IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE FROM OPM_ECC_CUS_QUALITIES_V cus, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE cus.sample_id = tmp.sample_id UNION ALL SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(inv) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION, SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME, ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM, PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO, MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO, ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID, SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID, SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE, EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM, TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD, RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER, TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE, IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE, IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING, IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE FROM OPM_ECC_INV_QUALITIES_V inv, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE inv.sample_id = tmp.sample_id UNION ALL SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(sup) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION, SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME, ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM, PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO, MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO, ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID, SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID, SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE, EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM, TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD, RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER, TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE, IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE, IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING, IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE FROM OPM_ECC_SUP_QUALITIES_V sup, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE sup.sample_id = tmp.sample_id UNION ALL SELECT /*+ leading(tmp) cardinality(tmp 10) push_pred(wip) */ ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, TMP.SAMPLE_ID, SAMPLE_NO, SAMPLE_DESC, ORGANIZATION_ID, ORGANIZATION_CODE, ORGANIZATION_NAME, SAMPLE_DISPOSITION, SAMPLE_DISPOSITION_DESC, SAMPLE_GROUP_DIS, SAMPLE_GROUP_DIS_DESC, SAMPLE_DISP, IS_REJECTED_SAMPLE, SAMPLE_EXPIRED_STRING, LOT_EXPIRED_STRING, SAMPLE_DELAYED_STRING,CUST_ID, CUST_NAME, ORDER_ID, ORDER_NUMBER, ORDER_TYPE_ID, ORDER_TYPE, ORDER_LINE_ID, ORDER_LINE_NUMBER, ORG_ID, SUPPLIER_OPERATING_UNIT, SUPPLIER_ID, SUPPLIER_NAME, PO_HEADER_ID, PO_HEADER_NUM, PO_LINE_ID, PO_LINE_NUM, RECEIPT_ID, RECEIPT_NUM, RECEIPT_LINE_ID, RECEIPT_LINE_NUM,CUSTOMER_OPERATING_UNIT, CUSTOMER_LOCATION, BATCH_ID, BATCH_NO, STEP_ID, STEP_NO, MATERIAL_DETAIL_ID, MATERIAL_LINE_NO, MATERIAL_LINE_TYPE, MATERIAL_LINE_TYPE_DESC, RECIPE_ID, RECIPE_NO, RECIPE_VERS, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULALINE_ID, ROUTING_ID, ROUTING_NO, ROUTING_VERS, OPRN_ID, OPRN_NO, OPRN_VERS, CHARGE, SAMPLE_INV_TRANS_IND, SAMPLE_QTY, SAMPLE_QTY_UOM, REMAINING_QTY, SAMPLE_SOURCE, SAMPLE_SOURCE_DESC, SAMPLE_APPROVER_ID, SAMPLE_APPROVER, INV_APPROVER_ID, INV_APPROVER, SAMPLER_ID, SAMPLER, DATE_DRAWN, EXPIRATION_DATE, RETRIEVAL_DATE, DATE_RECEIVED, DATE_REQUIRED, SHIP_TO_SITE_ID, SHIP_TO_SITE, LOT_RETEST_IND, SAMPLE_TYPE, SAMPLE_TYPE_DESC, ITEM_ID, ITEM_NO, REVISION, LAB_ORG_ID, LAB_ORG_CODE, LAB_ORG_NAME, PARENT_LOT_NUMBER, LOT_NUMBER, LOT_EXPIRATION_DATE, LOT_STATUS_ID, LOT_STATUS, SAMPLE_PRIORITY, SAMPLE_PRIORITY_DESC, LPN_ID, LPN_CODE, EXTERNAL_ID,SOURCE_ORGANIZATION, STORAGE_ORGANIZATION_ID, STORAGE_ORGANIZATION_CODE, STORAGE_ORGANIZATION_NAME, STORAGE_SUBINVENTORY, STORAGE_LOCATOR_ID, STORAGE_LOCATOR, SAMPLE_INSTANCE, SAMPLE_REQ_CNT, SAMPLE_TAKEN_CNT, SOURCE_COMMENT, SUPPLIER_LOT_NO, SUPPLIER_SITE, SPEC_ID, SPEC_NAME, SPEC_VERS, SPEC_GRADE, EVALUATION_IND, EVALUATION_DESC, ACTION_CODE, ACTION,IN_SPEC_IND,IN_SPEC_IND_DESC, OUT_SPEC_IND, IS_OUT_SPEC_IND, RESULT_ID, TEST_REPLICATE_CNT, TEST_TYPE, TEST_RESULT, TEST_RESULT_NUM, TEST_UNIT, MIN_VALUE, MIN_VALUE_NUM, MAX_VALUE, MAX_VALUE_NUM, TARGET_VALUE, TARGET_VALUE_NUM, TEST_PRIORITY, TEST_PRIORITY_DESC, TESTER_ID, TESTER, RESULT_SEQ, PLANNED_RESULT_DATE, RESULT_DATE, RESULT_DELAYED_STRING, TEST_LOAD, RESULT_DUE_STRING, DELAYED_RESULT_IND, TEST_QTY, CONSUMED_QTY, TEST_QTY_UOM,TEST_BY_DATE, TEST_LAB_ORG_ID, TEST_LAB_ORG_CODE, TEST_LAB_ORG_NAME, TEST_ID, TEST_CODE, TEST_METHOD_ID, TEST_METHOD, TEST_PROVIDER_ID, TEST_PROVIDER, TEST_PROVIDER_DESC, ACTUAL_RESOURCE, PLANNED_RESOURCE, TEST_KIT_INV_ITEM_ID, TEST_KIT_INV_ITEM_NO, EID_LAST_UPDATE_DATE, IS_SAMPLE_WITH_FINAL_DISP, IS_CUS_REJ_SAMPLE, IS_CUS_SAMPLE_WITH_FINAL_DISP, IS_INV_REJ_SAMPLE, IS_INV_SAMPLE_WITH_FINAL_DISP, IS_SUP_REJ_SAMPLE, IS_SUP_SAMPLE_WITH_FINAL_DISP, IS_WIP_REJ_SAMPLE, IS_WIP_SAMPLE_WITH_FINAL_DISP,SAMPLE_DISP_DATE, SAM_ROWID, RSLT_ROWID, IS_SAMPLE_PLANNED, IS_ANALYSIS_PENDING, IS_APPROVAL_PENDING, IS_SAMPLE_ACCEPTED, IS_SAMPLE_REJECTED, IS_ACCEPTED_VARIANCE, CASE WHEN SAMPLE_DISPOSITION IN('1P','2I') THEN 'YES' ELSE null END AS ASSIGN_RES_IND, LANGUAGE FROM OPM_ECC_WIP_QUALITIES_V wip, GMD.OPM_ECC_GMD_QM_TMP tmp WHERE wip.sample_id = tmp.sample_id ) where 1=1 and language in ('US') )PIVOT (max(LAB_ORG_NAME) AS LAB_ORG_NAME ,max(LAB_ORG_CODE) AS LAB_ORG_CODE,max(ORGANIZATION_NAME) AS ORGANIZATION_NAME,max(SAMPLE_DISPOSITION_DESC) AS SAMPLE_DISPOSITION_DESC, max(SAMPLE_SOURCE_DESC) AS SAMPLE_SOURCE_DESC,max(SAMPLE_PRIORITY_DESC) AS SAMPLE_PRIORITY_DESC, max(SAMPLE_GROUP_DIS_DESC) as SAMPLE_GROUP_DIS_DESC ,max(MATERIAL_LINE_TYPE_DESC) as MATERIAL_LINE_TYPE_DESC, max(ORDER_TYPE) as ORDER_TYPE,max(TEST_PRIORITY_DESC) as TEST_PRIORITY_DESC, max(TEST_LAB_ORG_NAME) as TEST_LAB_ORG_NAME,max(EVALUATION_DESC) as EVALUATION_DESC,max(ACTION) as ACTION,max(IN_SPEC_IND_DESC) as IN_SPEC_IND_DESC for LANGUAGE in ('US' "US")) ) x where 2=2

Parameters 1

Parameter

Type

List of values SQL

Organization Code

LOV

x.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code)

Description

SQL 102 lines

Parameters 1

Find out more

Features

Download

FAQ