WITH sup AS ( SELECT distinct msg_id,msg_status_id,provider_id,EXTERNAL_SYSTEM_ID,updated,USGUSER_ID, ERR_MSG_ID FROM Supplier_upd_log WHERE updated BETWEEN TO_DATE('2016-07-20', 'YYYY-MM-DD') AND TO_DATE('2016-07-21', 'YYYY-MM-DD') ) , sul AS ( SELECT sup.*, ec.error_category_id FROM sup LEFT JOIN DC_ERR_MSG dc ON dc.ERR_MSG_ID = sup.ERR_MSG_ID LEFT JOIN GIO_OWNER.C_ERROR_CATEGORY ec ON dc.error_category_id = ec.error_category_id ) , SD AS (SELECT EXTERNAL_SYSTEM_NAME, COUNT(DISTINCT PROVIDER_ID) PROP_REQ, COUNT(PROVIDER_ID) TOT_REQ , COUNT(DISTINCT ( CASE WHEN MSG_STATUS_ID <> 1 THEN PROVIDER_ID END)) PROP_ERR, COUNT(( CASE WHEN MSG_STATUS_ID <> 1 THEN PROVIDER_ID END)) ERR_REQ, COUNT(( CASE WHEN error_category_id = 2 THEN PROVIDER_ID END)) BUS_ERR_REQ, COUNT(( CASE WHEN error_category_id = 1 THEN PROVIDER_ID END)) SUP_ERR_REQ, COUNT(( CASE WHEN error_category_id = 4 THEN PROVIDER_ID END)) APP_ERR_REQ FROM sul AD, C_EXTERNAL_SYSTEM ES WHERE ES.EXTERNAL_SYSTEM_ID = AD.EXTERNAL_SYSTEM_ID AND ES.EXTSYS_TYPE_ID = 3 AND ES.EXTERNAL_SYSTEM_ID <> 5 GROUP BY EXTERNAL_SYSTEM_NAME ) SELECT EXTERNAL_SYSTEM_NAME AS SUPPLIER_NAME,BUS_ERR_REQ,APP_ERR_REQ,SUP_ERR_REQ, PROP_REQ AS PROPERTIES_REQUESTED, TOT_REQ AS TOTAL_CALL_COUNTS, ERR_REQ AS ERROR_COUNTS, ROUND(ERR_REQ/TOT_REQ * 100, 2) AS ERROR_PERCENT, PROP_ERR AS ERRD_PROPERTIES, ROUND(BUS_ERR_REQ /TOT_REQ * 100, 2) AS BUSINESS_ERROR_PERCENT, ROUND(SUP_ERR_REQ/TOT_REQ * 100, 2) AS SUPPLIER_ERROR_PERCENT, ROUND(APP_ERR_REQ/TOT_REQ * 100, 2) AS APPLICATION_ERROR_PERCENT FROM SD; Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#EXTS#EXTS_ID','NONUNIQUE','N/A','NORMAL','N','YES',null,'NO','EXTERNAL_SYSTEM_ID, UPDATED',null); Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#MSGST#MSGST_ID_S','NONUNIQUE','VALID','NORMAL','N','NO',null,'NO','MSG_STATUS_ID',null); Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#EXTS#EXTS_ID_S','NONUNIQUE','VALID','NORMAL','N','NO',null,'NO','EXTERNAL_SYSTEM_ID',null); Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#MSGST#MSGST_ID','NONUNIQUE','N/A','NORMAL','N','YES',null,'NO','MSG_STATUS_ID, UPDATED',null); Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','PK_SUPPUL#SUPPUL_ID','UNIQUE','VALID','NORMAL','N','NO',null,'NO','SUPPLIER_UPD_LOG_ID',null);