Toad Data Point 3.8
Windows 7
I have an automation script that contains 2 export templates. The first template runs SQL and export the results to a specific tab within a macro enabled workbook. I have it set to run macro after exporting data to Excel. The next template runs SQL and exports to same file different tab. The automation failed and now the Excel application will not open any files at all. I have had to reboot in order to get Excel to function again. I have also added a macro to save & close the Excel workbook and set to run after each export step. Any solutions would be appreciated. Thanks!
See log file:
6:38:06 PM Thread (3) Export Started [6/26/2018 6:38:06 PM]
6:38:06 PM Thread (3) Export using connection: BHEDWPROD (ai_rpt)
6:38:06 PM Thread (3) Export Wizard: Building object list
6:38:06 PM Thread (3) Exporting Data (1 of 1)
6:38:06 PM Thread (3) Object SQL Query started.
6:38:06 PM Thread (24) InternalReadBackground - start
6:39:14 PM Thread (24) InternalReadBackground - after ExecuteReader - command SELECT TOP 10 DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID MBR_CK, SUM(FCT.CLM_NET_PD_AMT) PAID_AMT INTO #MBRS FROM BHEDW_PROD.DM.TB_DIM_CLM_NET_DTL DIM INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_NET_DTL FCT ON (DIM.CLM_NET_SYS_ID = FCT.CLM_NET_SYS_ID) WHERE DATEPART(qq,DIM.CLM_NET_DT_SRVC_FROM_DT)||’-’||DATEPART(yy,DIM.CLM_NET_DT_SRVC_FROM_DT) = DATEPART(qq,DATEADD(qq,-1,GETDATE()))||’-’|| CASE WHEN (DATEPART(qq,DATEADD(qq,-1,GETDATE())))=4 THEN DATEPART(yy,DATEADD(yy,-1,GETDATE())) ELSE DATEPART(yy,GETDATE()) END AND DIM.CLM_NET_PRI_PAYE_CD_DESC NOT IN (‘PROVIDER’,‘VENDOR’) AND CASE WHEN (DIM.CLM_SRC_COSM_DIV_CD) = ‘MPC’ AND DIM.CLM_SRC_LEG_ENTY_NBR||’-’||DIM.CLM_SRC_MBR_PRDCT_CD NOT IN (‘01358-01209’, ‘01358-01210’, ‘01358-01211’, ‘01358-01212’, ‘01358-00413’, ‘01358-00603’, ‘01417-01209’, ‘01417-01210’, ‘01417-01211’, ‘01417-01212’, ‘01417-00413’, ‘01417-00603’) THEN ‘MEDICAL’ ELSE ‘BH’ END = ‘BH’ GROUP BY DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID ORDER BY SUM(FCT.CLM_NET_PD_AMT)DESC SELECT DIM.CLM_NET_CLM_FM_TYP_CD, FCT.PROV_SYS_ID , PROV.SRC_PROV_ID, DIM.CLM_SRC_PROV_TAX_ID_NBR TIN, DIM.CLM_SRC_PROV_CLM_NPI_ID NPI, DIM.CLM_SRC_PROV_SRVC_CTY_NM, DIM.CLM_SRC_PROV_SRVC_ST_CD, DIM.CLM_SRC_PROV_SRVC_ZIP_CD, PROV.SRC_PROV_ID_SRC_CD, PROV.PROV_NM, CASE WHEN DIM.CLM_SRC_PROV_PAYE_NM =‘U’ THEN PROV.PROV_NM ELSE DIM.CLM_SRC_PROV_PAYE_NM END AS PAYEE_NM, DIM.CLM_NET_PRI_PAYE_CD_DESC, CASE WHEN (DWP.PROV_REIM_CATGY_NM) IS NULL OR (DWP.PROV_REIM_CATGY_NM) = ‘Unknown’ THEN DWP.PROV_FACL_TYP_DESC ELSE DWP.PROV_REIM_CATGY_NM END PROV_REIM_CATGY_NM, PROV.PROV_DEG_CRDNTL_DESC, DIM.CLM_NET_PROV_NTWK_DERIV_CD, DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID MBR_CK, MEM.MBR_SYS_ID, DIM.CLM_SRC_MBR_SBSCR_ID, GRP.GRP_NM, MEM.MBR_NM_FST_TXT, MEM.MBR_NM_LST_TXT, MEM.BTH_DT, DIM.CLM_SRC_MBR_GDR_CD, MEM.SSN, CAST(DATEDIFF(DD, MEM.BTH_DT, DIM.CLM_NET_DT_SRVC_FROM_DT)/365.25 AS DECIMAL(10,0)) AS AGE_DOS, MEM.ELIG_ADR_LN_1_TXT MBR_ELIG_ADDR1, MEM.ELIG_ADR_LN_2_TXT MBR_ELIG_ADDR2, MEM.ELIG_ADR_CITY_NM MBR_ELIG_CITY, MEM.ELIG_ST_ABBR_CD MBR_ELIG_ST, LEFT(MEM.ELIG_ZIP_CD,5) MBR_ELIG_ZIP, MEM.MAIL_ADR_LN_1_TXT MBR_MAIL_ADDR1, MEM.MAIL_ADR_LN_2_TXT MBR_MAIL_ADDR2, MEM.MAIL_ADR_CITY_NM MBR_MAIL_CITY, MEM.MAIL_ST_ABBR_CD MBR_MAIL_ST, LEFT(MEM.MAIL_ZIP_CD,5) MBR_MAIL_ZIP, DIM.CLM_NET_DIAG_MH_SA_CD, PKG.PRDCT_CMPNT_GRP_DESC, PKG.BH_PLN_TYP_DESC, GRP.ACCT_NM, PKG.BH_UBH_FUND_DESC, SALE.SALE_CHNL_NM, GRP.CUST_NM, GRP.GRP_ALT_ID, GRP.CBMS_ACCT_ID, DIM.CLM_SRC_MBR_PRDCT_CD, DIM.CLM_SRC_LEG_ENTY_NBR, DIM.CLM_AUD_SRC_SYS_CD, DIM.CLM_SRC_COSM_DIV_CD, DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_NBR, DIM.CLM_NET_PROC_TYP_CD, MAX(CASE WHEN LEN(RTRIM(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ') AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ') AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END))= 4 AND LEFT(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END,1) = ‘0’ THEN RIGHT(RTRIM(CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END),3) ELSE (CASE WHEN(RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD != ’ ’ THEN (CASE WHEN (LEFT(DIM.BIL_RVNU_CD,1)) = ‘0’ THEN RIGHT(DIM.BIL_RVNU_CD,3) ELSE DIM.BIL_RVNU_CD END) WHEN (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END))IN (‘240’,‘90899’,‘89240’) AND DIM.BIL_RVNU_CD = ’ ’ THEN (CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) ELSE (RTRIM(CASE WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND (STU.BIL_PROC_CD) IS NOT NULL AND (STU.BIL_PROC_CD) != ’ ’ THEN STU.BIL_PROC_CD WHEN (DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’)) AND ((STU.BIL_PROC_CD) IS NULL OR (STU.BIL_PROC_CD) = ’ ‘) AND (DIM.CLM_NET_SEC_PROC_CD) != ’ ’ THEN DIM.CLM_NET_SEC_PROC_CD ELSE (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)=4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END) END)) END) END) SVC_CODE, CAST(NULL as varchar(250)) AS SVC_CODE_DESC, CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END PROC_CODE, DIM.CLM_NET_PROC_MOD_CD, DIM.CLM_NET_PROC_MOD_2_CD, DIM.CLM_NET_PROC_MOD_3_CD, DIM.CLM_NET_PROC_MOD_4_CD, DIM.CLM_NET_PROC_DESC, CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘REV’,‘U’) THEN DIM.CLM_NET_SEC_PROC_CD ELSE ‘’ END CLM_NET_SEC_PROC_CD, CASE WHEN (DIM.CLM_NET_SEC_PROC_DESC) = ‘Unknown’ THEN ‘’ WHEN (DIM.CLM_NET_PROC_DESC = DIM.CLM_NET_SEC_PROC_DESC) THEN ‘’ ELSE DIM.CLM_NET_SEC_PROC_DESC END CLM_NET_SEC_PROC_DESC , MAX(DIM.BIL_RVNU_CD) BIL_RVNU_CD, MAX(STU.BIL_PROC_CD) BIL_PROC_CD, MAX(DIM.CLM_NET_DLOC_TXT)CLM_NET_DLOC_TXT , MAX(DIM.CLM_SRC_AUTH_NBR) CLM_SRC_AUTH_NBR, MAX(I.BILLED_UNITS) BILLED_UNITS, CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DIM.CLM_NET_DT_SRVC_FROM_DT)-1),DIM.CLM_NET_DT_SRVC_FROM_DT),101) DOS_MONTH, TRANS.CLM_ETL_DW_FROM_DT, DIM.CLM_NET_DT_SRVC_FROM_DT, DIM.CLM_NET_DT_SRVC_THRU_DT, DIM.CLM_NET_DT_RCVD_DT , DIM.CLM_NET_PL_OF_SRVC_AMA_CD as POS, DIM.CLM_NET_DIAG_1_CD AS DX1, DIM.CLM_NET_DIAG_1_CD_DESC AS DX1_DESC, DIM.CLM_NET_DIAG_2_CD AS DX2, DIM.CLM_NET_DIAG_2_CD_DESC AS DX2_DESC, DIM.CLM_NET_DIAG_3_CD AS DX3, DIM.CLM_NET_DIAG_3_CD_DESC AS DX3_DESC, SUM(FCT.CLM_NET_SBMT_AMT) AS BILLED_AMT, SUM(FCT.CLM_NET_ALLW_DERIV_AMT) AS ALLW_DERIV_AMT, SUM(FCT.CLM_NET_DSALLW_AMT) AS DSALLW_AMT, SUM(FCT.CLM_NET_DSCNT_AMT) AS DISCOUNT_AMT, SUM(FCT.CLM_NET_MBR_RESP_AMT) AS MBR_RESP_AMT, SUM(FCT.CLM_NET_COB_AMT) AS COB_AMT, SUM(FCT.CLM_NET_PD_AMT) AS PAID_AMT INTO #CLAIMS FROM #MBRS M INNER JOIN BHEDW_PROD.DM.TB_DIM_CLM_NET_DTL DIM ON M.MBR_CK = DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_NET_DTL FCT ON (DIM.CLM_NET_SYS_ID = FCT.CLM_NET_SYS_ID) INNER JOIN (SELECT DISTINCT DIM.CLM_AUD_SRC_ID, DIM.CLM_TRANS_SYS_ID, DIM.CLM_AUD_LN_NBR, CASE WHEN DIM.CLM_TRANS_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_TRANS_PROC_CD)= 4 AND LEFT(DIM.CLM_TRANS_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_TRANS_PROC_CD,3) ELSE DIM.CLM_TRANS_PROC_CD END PROC_CODE, DIM.CLM_ETL_DW_FROM_DT, DIM.CLM_TRANS_DT_SRVC_FROM_DT FROM BHEDW_PROD.DM.TB_DIM_CLM_TRANS DIM) TRANS ON (DIM.CLM_AUD_SRC_ID = TRANS.CLM_AUD_SRC_ID) AND (DIM.CLM_AUD_LN_NBR = TRANS.CLM_AUD_LN_NBR) AND (CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END = TRANS.PROC_CODE) AND (DIM.CLM_NET_DT_SRVC_FROM_DT = TRANS.CLM_TRANS_DT_SRVC_FROM_DT) AND (DIM.CLM_NET_SYS_ID = TRANS.CLM_TRANS_SYS_ID) LEFT OUTER JOIN (SELECT DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_LN_NBR, DIM.CLM_TRANS_DT_SRVC_FROM_DT, MAX(FCT.CLM_TRANS_UNIT_CNT) BILLED_UNITS FROM DM.TB_DIM_CLM_TRANS DIM INNER JOIN BHEDW_PROD.DM.TB_FCT_CLM_TRANS FCT ON (DIM.CLM_TRANS_SYS_ID = FCT.CLM_TRANS_SYS_ID) GROUP BY DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_LN_NBR, DIM.CLM_TRANS_DT_SRVC_FROM_DT) I ON (DIM.CLM_AUD_SRC_ID = I.CLM_AUD_SRC_ID AND DIM.CLM_AUD_LN_NBR = I.CLM_AUD_LN_NBR AND DIM.CLM_NET_DT_SRVC_FROM_DT = I.CLM_TRANS_DT_SRVC_FROM_DT) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_CUS_GRP GRP ON (FCT.GRP_SYS_ID=GRP.GRP_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_MBR MEM ON (FCT.MBR_SYS_ID = MEM.MBR_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_PROV PROV ON (FCT.PROV_SYS_ID = PROV.PROV_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_PRD_PKG PKG ON (FCT.PKG_SYS_ID = PKG.PKG_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_ORG_SALE_CHNL SALE ON (FCT.SALE_CHNL_SYS_ID = SALE.SALE_CHNL_SYS_ID) LEFT OUTER JOIN BHEDW_PROD.DM.TB_DIM_RPT_CUST RPT_CUST ON (FCT.RPT_CUST_SYS_ID = RPT_CUST.RPT_CUST_SYS_ID) LEFT OUTER JOIN (SELECT DISTINCT CLM_AUD_NBR, CASE WHEN (LEN(STU.RVNU_CD_DTL_LN_NBR))=2 AND LEFT(STU.RVNU_CD_DTL_LN_NBR,1)=‘0’ THEN RIGHT(STU.RVNU_CD_DTL_LN_NBR,1) ELSE STU.RVNU_CD_DTL_LN_NBR END CLM_AUD_LN_NBR , SBSCR_NBR CLM_SRC_MBR_SBSCR_ID, MAX(BIL_PROC_CD) BIL_PROC_CD, MAX(BIL_RVNU_CD)BIL_RVNU_CD FROM BHEDW_PROD.STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE STU GROUP BY CLM_AUD_NBR, CASE WHEN (LEN(STU.RVNU_CD_DTL_LN_NBR))=2 AND LEFT(STU.RVNU_CD_DTL_LN_NBR,1)=‘0’ THEN RIGHT(STU.RVNU_CD_DTL_LN_NBR,1) ELSE STU.RVNU_CD_DTL_LN_NBR END , SBSCR_NBR ) STU ON (DIM.CLM_AUD_NBR = RTRIM(STU.CLM_AUD_NBR) ) AND (DIM.CLM_AUD_LN_NBR = STU.CLM_AUD_LN_NBR) AND (DIM.CLM_SRC_MBR_SBSCR_ID = STU.CLM_SRC_MBR_SBSCR_ID) AND (DIM.BIL_RVNU_CD = STU.BIL_RVNU_CD OR ‘0’||DIM.CLM_NET_PROC_CD = STU.BIL_RVNU_CD OR DIM.CLM_NET_PROC_CD = STU.BIL_RVNU_CD) LEFT OUTER JOIN BHEDW_PROD.DW.TB_PRV_PROVIDER DWP ON (FCT.PROV_SYS_ID = DWP.PROV_SYS_ID) WHERE DATEPART(qq,DIM.CLM_NET_DT_SRVC_FROM_DT)||’-’||DATEPART(yy,DIM.CLM_NET_DT_SRVC_FROM_DT) = DATEPART(qq,DATEADD(qq,-1,GETDATE()))||’-’|| CASE WHEN (DATEPART(qq,DATEADD(qq,-1,GETDATE()))) =4 THEN DATEPART(yy,DATEADD(yy,-1,GETDATE())) ELSE DATEPART(yy,GETDATE()) END AND DIM.CLM_NET_PRI_PAYE_CD_DESC NOT IN(‘PROVIDER’,‘VENDOR’) AND DIM.CLM_SRC_LEG_ENTY_NBR NOT IN (‘TRNA’,‘TRNS’) AND DIM.CLM_SRC_MBR_SYS_ID != ‘0’ AND CASE WHEN (DIM.CLM_SRC_COSM_DIV_CD) = ‘MPC’ AND DIM.CLM_SRC_LEG_ENTY_NBR||’-’||DIM.CLM_SRC_MBR_PRDCT_CD NOT IN (‘01358-01209’, ‘01358-01210’, ‘01358-01211’, ‘01358-01212’, ‘01358-00413’, ‘01358-00603’, ‘01417-01209’, ‘01417-01210’, ‘01417-01211’, ‘01417-01212’, ‘01417-00413’, ‘01417-00603’) THEN ‘MEDICAL’ ELSE ‘BH’ END = ‘BH’ GROUP BY DIM.CLM_NET_CLM_FM_TYP_CD, FCT.PROV_SYS_ID , PROV.SRC_PROV_ID, DIM.CLM_SRC_PROV_TAX_ID_NBR , DIM.CLM_SRC_PROV_CLM_NPI_ID , DIM.CLM_SRC_PROV_SRVC_CTY_NM, DIM.CLM_SRC_PROV_SRVC_ST_CD, DIM.CLM_SRC_PROV_SRVC_ZIP_CD, PROV.SRC_PROV_ID_SRC_CD, PROV.PROV_NM, CASE WHEN DIM.CLM_SRC_PROV_PAYE_NM =‘U’ THEN PROV.PROV_NM ELSE DIM.CLM_SRC_PROV_PAYE_NM END , DIM.CLM_NET_PRI_PAYE_CD_DESC, CASE WHEN (DWP.PROV_REIM_CATGY_NM) IS NULL OR (DWP.PROV_REIM_CATGY_NM) = ‘Unknown’ THEN DWP.PROV_FACL_TYP_DESC ELSE DWP.PROV_REIM_CATGY_NM END, PROV.PROV_DEG_CRDNTL_DESC, DIM.CLM_NET_PROV_NTWK_DERIV_CD, DIM.CLM_SRC_MBR_SYS_ID||’-’||DIM.CLM_SRC_MBR_SBSCR_ID , MEM.MBR_SYS_ID, DIM.CLM_SRC_MBR_SBSCR_ID, GRP.GRP_NM, MEM.MBR_NM_FST_TXT, MEM.MBR_NM_LST_TXT, MEM.BTH_DT, DIM.CLM_SRC_MBR_GDR_CD, MEM.SSN, CAST(DATEDIFF(DD, MEM.BTH_DT, DIM.CLM_NET_DT_SRVC_FROM_DT)/365.25 AS DECIMAL(10,0)) , MEM.ELIG_ADR_LN_1_TXT , MEM.ELIG_ADR_LN_2_TXT , MEM.ELIG_ADR_CITY_NM , MEM.ELIG_ST_ABBR_CD , LEFT(MEM.ELIG_ZIP_CD,5) , MEM.MAIL_ADR_LN_1_TXT , MEM.MAIL_ADR_LN_2_TXT , MEM.MAIL_ADR_CITY_NM , MEM.MAIL_ST_ABBR_CD , LEFT(MEM.MAIL_ZIP_CD,5) , DIM.CLM_NET_DIAG_MH_SA_CD, PKG.PRDCT_CMPNT_GRP_DESC, PKG.BH_PLN_TYP_DESC, GRP.ACCT_NM, PKG.BH_UBH_FUND_DESC, SALE.SALE_CHNL_NM, GRP.CUST_NM, GRP.GRP_ALT_ID, GRP.CBMS_ACCT_ID, DIM.CLM_SRC_MBR_PRDCT_CD, DIM.CLM_SRC_LEG_ENTY_NBR, DIM.CLM_AUD_SRC_SYS_CD, DIM.CLM_SRC_COSM_DIV_CD, DIM.CLM_AUD_SRC_ID, DIM.CLM_AUD_NBR, DIM.CLM_NET_PROC_TYP_CD, CASE WHEN DIM.CLM_NET_PROC_TYP_CD IN (‘U’,‘REV’) AND LEN(DIM.CLM_NET_PROC_CD)= 4 AND LEFT(DIM.CLM_NET_PROC_CD,1)=‘0’ THEN RIGHT(DIM.CLM_NET_PROC_CD,3) ELSE DIM.CLM_NET_PROC_CD END , DIM.CLM_NET_PROC_MOD_CD, DIM.CLM_NET_PROC_MOD_2_CD, DIM.CLM_NET_PROC_MOD_3_CD, DIM.CLM_NET_PROC_MOD_4_CD, DIM.CLM_NET_PROC_DESC, CASE WHEN (DIM.CLM_NET_PROC_TYP_CD) IN (‘REV’,‘U’) THEN DIM.CLM_NET_SEC_PROC_CD ELSE ‘’ END , CASE WHEN (DIM.CLM_NET_SEC_PROC_DESC) = ‘Unknown’ THEN ‘’ WHEN (DIM.CLM_NET_PROC_DESC = DIM.CLM_NET_SEC_PROC_DESC) THEN ‘’ ELSE DIM.CLM_NET_SEC_PROC_DESC END , DIM.CLM_NET_ENT_DIAG_REL_GRP_CD, DIM.CLM_NET_SYS_DIAG_REL_GRP_CD, CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DIM.CLM_NET_DT_SRVC_FROM_DT)-1),DIM.CLM_NET_DT_SRVC_FROM_DT),101), TRANS.CLM_ETL_DW_FROM_DT, DIM.CLM_NET_DT_SRVC_FROM_DT, DIM.CLM_NET_DT_SRVC_THRU_DT, DIM.CLM_NET_DT_RCVD_DT , DIM.CLM_NET_PL_OF_SRVC_AMA_CD , DIM.CLM_NET_DIAG_1_CD, DIM.CLM_NET_DIAG_1_CD_DESC , DIM.CLM_NET_DIAG_2_CD , DIM.CLM_NET_DIAG_2_CD_DESC , DIM.CLM_NET_DIAG_3_CD , DIM.CLM_NET_DIAG_3_CD_DESC HAVING SUM(FCT.CLM_NET_PD_AMT) > 0 UPDATE #CLAIMS F SET F.BIL_PROC_CD = S.BIL_PROC_CD, F.BIL_RVNU_CD = S.BIL_RVNU_CD FROM (SELECT F.CLM_AUD_NBR, S.BIL_PROC_CD, S.BIL_RVNU_CD INTO #STG FROM (SELECT CLM_AUD_NBR, SUM(PAID_AMT) PAID_AMT FROM #CLAIMS GROUP BY CLM_AUD_NBR) F INNER JOIN (SELECT CLM_AUD_NBR, MAX(BIL_PROC_CD)BIL_PROC_CD, MAX(BIL_RVNU_CD)BIL_RVNU_CD, SUM(ALLOC_SRC_PD_AMT) PAID_AMT FROM STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE GROUP BY CLM_AUD_NBR) S ON (F.CLM_AUD_NBR = S.CLM_AUD_NBR) AND (F.PAID_AMT = S.PAID_AMT)) S WHERE F.CLM_AUD_NBR = S.CLM_AUD_NBR UPDATE #CLAIMS F SET F.BIL_PROC_CD = S.BIL_PROC_CD, F.BIL_RVNU_CD = S.BIL_RVNU_CD FROM (SELECT F.CLM_AUD_NBR, S.BIL_PROC_CD, S.BIL_RVNU_CD FROM (SELECT CLM_AUD_NBR, SUM(PAID_AMT) PAID_AMT FROM #CLAIMS GROUP BY CLM_AUD_NBR) F INNER JOIN (SELECT DISTINCT CLM_AUD_NBR, BIL_PROC_CD, BIL_RVNU_CD, ALLOC_SRC_PD_AMT PAID_AMT FROM STG.STG_GALA_UNET_UB92_REVENUE_CODE_STATISTICAL_SERVICE ) S ON (F.CLM_AUD_NBR = S.CLM_AUD_NBR) AND (F.PAID_AMT = S.PAID_AMT)) S WHERE F.CLM_AUD_NBR = S.CLM_AUD_NBR AND ((F.BIL_RVNU_CD IS NULL AND F.BIL_PROC_CD IS NULL) OR (F.BIL_RVNU_CD = ’ ’ AND F.BIL_PROC_CD = ’ ')) SELECT [MBR_CK], [CLM_SRC_MBR_SBSCR_ID], [MBR_NM_FST_TXT], [MBR_NM_LST_TXT], [BTH_DT], COUNT(DISTINCT [CLM_AUD_NBR]) AS DISTINCT_CLMS, SUM([BILLED_AMT]) AS BILLED, SUM([PAID_AMT]) AS PAID INTO #SUMMARY FROM #CLAIMS GROUP BY [MBR_CK], [CLM_SRC_MBR_SBSCR_ID], [MBR_NM_FST_TXT], [MBR_NM_LST_TXT], [BTH_DT] ORDER BY SUM([PAID_AMT]) DESC SELECT * FROM #SUMMARY
6:39:14 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:19 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:24 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:29 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:34 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:39 PM Thread (20) Excel file currently in use, waiting 5 seconds for retry, up to 30 seconds.
6:39:44 PM Thread (20) User cancelled
6:39:44 PM Thread (19) DoneReadTreadWriter
6:39:44 PM Thread (19) DoneReadThreadWriter finally - adapter.RowsRead 10
6:39:44 PM Thread (3) Object SQL Query finished with error: The process cannot access the file ‘C:~Data\Reporting\HCQAI\2_Periodic_Reports\Top_10_MbrsPaid\Qtrly_Top 10 Mbr_Claims - TEST.xlsm’ because it is being used by another process.
6:39:44 PM Thread (3) at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream…ctor(String path, FileMode mode, FileAccess access, FileShare share)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType, ExcelVersion version, ExcelParseOptions options)
at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType)
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.TryOpenExcelFile()
at Quest.Toad.ImportExport.Writers.WriterExcel.InitializeFileLocation()
at Quest.Toad.ImportExport.Writers.WriterExcel.WriteHeader(Object tableObject)
at Quest.Toad.ImportExport.ExportObjectData.InitializeSchema()
6:39:44 PM Thread (3) Export Finished [6/26/2018 6:39:44 PM]