DECLARE @MH_BASEBILLNO INT = 250000 DECLARE @TAXYEAR INT = 2021 print '***********************' print '***********************' print @taxyear print '***********************' print '***********************' DECLARE @TAX_YEAR INT = @TAXYEAR DECLARE @PROPKEY INT = 0 DECLARE @BILLKEY INT = 0 DECLARE @OWNKEY INT = 0 DECLARE @PAYMENTKEY INT = 0 DECLARE @CAT_ADVTAX AS INT = 1 DECLARE @CAT_PENALTY AS INT = 6 DECLARE @CAT_ADMIN AS INT = 8 DECLARE @CAT_INTEREST AS INT = 4 DECLARE @CAT_FIFA AS INT = 5 DECLARE @CAT_CREDITCARDFEE AS INT = 2 -- LATE FILTING >> ADV_TAX --DECLARE @CAT_DUPFEE AS INT = 7 -- "COPY FEE >> GOES INTO ADMIN DECLARE @CAT_GED AS INT = 3 DECLARE @CAT_BREACH AS INT = 9 DECLARE @CAT_COLLECTFEES AS INT = 10 DECLARE @CAT_ADVERTISING AS INT = 11 DECLARE @CAT_TITLESEARCH AS INT = 12 -- McDuffie 12 & 13 DECLARE @CAT_MAILFEES AS INT = 7 DECLARE @CAT_ABATEMENT AS INT = 14 set @BILLKEY = (select max(billkey) from DY2021.dbo.bill) set @CAT_ADVTAX = 1 set @CAT_PENALTY = 4 set @CAT_ADMIN = 5 set @CAT_INTEREST = 8 set @CAT_FIFA = 6 set @CAT_CREDITCARDFEE = 2 -- LATE FILTING >> ADV_TAX --set @CAT_DUPFEE = 88 -- "COPY FEE >> GOES INTO ADMIN set @CAT_GED = 7 set @CAT_BREACH = 88 set @CAT_COLLECTFEES = 88 set @CAT_ADVERTISING = 88 set @CAT_TITLESEARCH = 88 set @CAT_MAILFEES = 3 set @CAT_ABATEMENT = 88 /**** ORIGINAL BILLING - MH - TAX ONLY ****/ PRINT 'MH BILLS' INSERT INTO DY2021.[dbo].[BILL] ([BILLKEY] ,[PROPERTYKEY] ,[BILL_NO] ,[BILL_YEAR] ,[BILL_TYPE] ,[TIMBER_VALUE] ,[TIMBER_ACRES] ,[PROPERTY_ACRES] ,[ADV_TAX] ,[INTEREST] ,[PENALTY] ,[FIFA] ,[GED] ,[BREACH] ,[TITLE_SEARCH] ,[MAIL_FEES] ,[ADMINISTRATION] ,[PRIOR_YEARS] ,[ADVERTISING] ,[CREDIT_CARD_FEES] ,[TOTAL] ,[BALANCE_DUE] ,[DUE_DATE] ,[EXCESS_FUNDS] ,[OVERAGES] ,[FEES] ,[FEES_OTHER] ,[COMMENTS] ,[COLLECT_FEES] ,[CHANGE] ,[INSTALL_1] ,[INSTALL_2] ,[FIFA_DATE] ,[UTILITY] ,[ORG_ADVTAX] ,[ORG_TOTAL] ,[ORG_FEES] ,[ORG_FEES_OTHER]) SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY P.ACCOUNT_NO) AS BILLKEY , PP.PROPERTYKEY , CONVERT(INT,P.BILL_NO)+@MH_BASEBILLNO , @TAXYEAR+1 BILL_YEAR , 'M' BILL_TYPE , 7 TIMBER_VALUE , 0.0 TIMBER_ACRES , 0 TOTALACRES , z.ADV_TAX , z.INTEREST , z.PENALTY , z.FIFA , z.GED , 0 BREACH , 0 TITLE_SEARCH , 0 MAIL_FEES , 0 ADMINISTRATION , 0 PRIOR_YEARS , 0 ADVERTISING , 0 CREDIT_CARD_FEES , ISNULL(Z.ADV_TAX,0) + ISNULL(Z.INTEREST,0) + ISNULL(Z.PENALTY,0) + ISNULL(Z.FIFA,0) + ISNULL(Z.GED,0) + ISNULL(Z.FEES,0) AS TOTAL , 0 AS BALANCE_DUE , RIGHT(DUE_DATE,4)+LEFT(DUE_DATE,2)+SUBSTRING(DUE_DATE,3,2) DUE_DATE , 0 EXCESS_FUNDS , 0 OVERAGES , 0 FEES , Z.FEES FEES_OTHER , REPLACE(COMM.COMMENTS,';',CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)) COMMENTS , Z.COLLECT_FEES , 0 CHANGE , 0 INSTALL_1 , 0 INSTALL_2 , NULL FIFA_DATE , 0 UTILITY , Z.ADV_TAX AS ORG_ADVTAX , Z.ADV_TAX AS ORG_TOTAL , Z.FEES ORG_FEES , 0 ORG_FEES_OTHER FROM MCDUFFIE_GSI.DBO.MOBIL P LEFT JOIN DY2021.DBO.PROPERTY PP ON PP.LEGACY_ACCT=P.ACCOUNT_NO+P.LINE_NO AND PP.DIGYR=P.TAX_YEAR+1 AND PP.PROPTYPE='M' INNER JOIN MCDUFFIE_GSI.DBO.CASH C ON C.ACCT_NO=P.ACCOUNT_NO AND C.ACCT_LINENO=P.LINE_NO AND CONVERT(INT,C.BILL_NO)=CONVERT(INT,P.BILL_NO) AND C.BILL_YEAR=P.TAX_YEAR AND C.RECTYPE=1 AND CATEGORY=1 AND APPCODE=4 and REGISTER IN (00) LEFT JOIN ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (1) AND CATEGORY=1 and REGISTER IN (00) AND BILL_YEAR=2021 GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Z ON CONVERT(INT,Z.BILL_NO)=CONVERT(INT,P.BILL_NO) AND Z.BILL_YEAR=P.TAX_YEAR LEFT JOIN ( SELECT TAX_YEAR, ACCT_NO, STUFF((SELECT ';'+rtrim(COMMENT1)+rtrim(COMMENT2)+rtrim(COMMENT3)+rtrim(COMMENT4)+rtrim(COMMENT5)+rtrim(COMMENT6)+rtrim(COMMENT7) FROM MHCMTS WHERE TAX_YEAR=@TAXYEAR AND ACCT_NO= P.ACCT_NO FOR XML PATH('')),1,1,'') AS COMMENTS FROM MHCMTS P union SELECT TAX_YEAR, ACCT_NO, STUFF((SELECT ';'+rtrim(COMMENT1)+rtrim(COMMENT2)+rtrim(COMMENT3)+rtrim(COMMENT4)+rtrim(COMMENT5)+rtrim(COMMENT6)+rtrim(COMMENT7) FROM MHCMTS WHERE TAX_YEAR=@TAXYEAR AND ACCT_NO= P.ACCT_NO FOR XML PATH('')),1,1,'') AS COMMENTS FROM MHCMTS P ) COMM ON COMM.ACCT_NO=P.ACCOUNT_NO AND COMM.TAX_YEAR=P.TAX_YEAR WHERE P.TAX_YEAR=@TAXYEAR /**** UPDATE BILLING - MH - INT/PEN/FIFA/GED (ORIGINAL BILLINGS - CATEGORY 1) ****/ UPDATE BU SET BU.ADV_TAX = BU.ADV_TAX + Z.ADV_TAX , BU.INTEREST = BU.INTEREST + Z.INTEREST , BU.PENALTY = BU.PENALTY + Z.PENALTY , BU.FIFA = BU.FIFA + Z.FIFA , BU.GED = BU.GED + Z.GED , BU.FEES = BU.FEES + Z.FEES , BU.ADMINISTRATION = ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END , BU.ADVERTISING = ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) , BU.FEES_OTHER = ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) , BU.CREDIT_CARD_FEES = ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) , BU.TITLE_SEARCH = ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) , BU.COLLECT_FEES = ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) , BU.TOTAL = BU.ADV_TAX + ISNULL(Z.ADV_TAX ,0) + BU.INTEREST + ISNULL(Z.INTEREST ,0) + BU.PENALTY + ISNULL(Z.PENALTY ,0) + BU.FIFA + ISNULL(Z.FIFA ,0) + BU.GED + ISNULL(Z.GED ,0) + BU.FEES + ISNULL(Z.FEES ,0) + ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END + ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) + ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) + ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) FROM DY2021.DBO.BILL BU INNER JOIN ( SELECT BILL_NO, BILL_YEAR, SUM(ADV_TAX) AS ADV_TAX, SUM(INTEREST) INTEREST, SUM(PENALTY) PENALTY, SUM(FIFA) FIFA, SUM(GED) GED, SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER, SUM(ADMINISTRATION) ADMINISTRATION, SUM(ADVERTISING) ADVERTISING, SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , SUM(TITLE_SEARCH) TITLE_SEARCH , SUM(COLLECT_FEES) COLLECT_FEES FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END TITLE_SEARCH , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (1) AND CATEGORY NOT IN (1) --(3,4,5,6,7,8) AND BILL_YEAR=@TAXYEAR GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Q GROUP BY BILL_NO, BILL_YEAR ) Z ON CONVERT(INT,Z.BILL_NO)+@MH_BASEBILLNO=BU.BILL_NO AND Z.BILL_YEAR+1=BU.BILL_YEAR WHERE BU.BILL_YEAR=@TAXYEAR+1 AND BU.BILL_TYPE='M' and bu.TIMBER_VALUE=7 /**** UPDATE BILLING - MH - INT/PEN/FIFA/GED (ADDITIONS - CATEGORY 2) ****/ UPDATE BU SET BU.ADV_TAX = BU.ADV_TAX + Z.ADV_TAX , BU.INTEREST = BU.INTEREST + Z.INTEREST , BU.PENALTY = BU.PENALTY + Z.PENALTY , BU.FIFA = BU.FIFA + Z.FIFA , BU.GED = BU.GED + Z.GED , BU.FEES = BU.FEES + Z.FEES , BU.ADMINISTRATION = ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END , BU.ADVERTISING = ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) , BU.FEES_OTHER = ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) , BU.CREDIT_CARD_FEES = ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) , BU.TITLE_SEARCH = ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) , BU.COLLECT_FEES = ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) , BU.TOTAL = BU.ADV_TAX + ISNULL(Z.ADV_TAX ,0) + BU.INTEREST + ISNULL(Z.INTEREST ,0) + BU.PENALTY + ISNULL(Z.PENALTY ,0) + BU.FIFA + ISNULL(Z.FIFA ,0) + BU.GED + ISNULL(Z.GED ,0) + BU.FEES + ISNULL(Z.FEES ,0) + ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END + ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) + ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) + ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) FROM DY2021.DBO.BILL BU INNER JOIN ( SELECT BILL_NO, BILL_YEAR, SUM(ADV_TAX) AS ADV_TAX, SUM(INTEREST) INTEREST, SUM(PENALTY) PENALTY, SUM(FIFA) FIFA, SUM(GED) GED, SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER, SUM(ADMINISTRATION) ADMINISTRATION, SUM(ADVERTISING) ADVERTISING, SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , SUM(TITLE_SEARCH) TITLE_SEARCH , SUM(COLLECT_FEES) COLLECT_FEES FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END TITLE_SEARCH , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (2) --AND CATEGORY IN (3,4,5,6,8) AND BILL_YEAR=2021 GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Q GROUP BY BILL_NO, BILL_YEAR ) Z ON CONVERT(INT,Z.BILL_NO)+@MH_BASEBILLNO=BU.BILL_NO AND Z.BILL_YEAR+1=BU.BILL_YEAR WHERE BU.BILL_YEAR=@TAXYEAR+1 AND BU.BILL_TYPE='M' and bu.TIMBER_VALUE=7 /**** UPDATE BILLING - MH - INT/PEN/FIFA/GED (ADJUSTMENT - CATEGORY 4 - INCREASES) ****/ UPDATE BU SET BU.ADV_TAX = BU.ADV_TAX + Z.ADV_TAX , BU.INTEREST = BU.INTEREST + Z.INTEREST , BU.PENALTY = BU.PENALTY + Z.PENALTY , BU.FIFA = BU.FIFA + Z.FIFA , BU.GED = BU.GED + Z.GED , BU.FEES = BU.FEES + Z.FEES , BU.ADMINISTRATION = ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END , BU.ADVERTISING = ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) , BU.FEES_OTHER = ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) , BU.CREDIT_CARD_FEES = ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) , BU.TITLE_SEARCH = ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) , BU.COLLECT_FEES = ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) , BU.TOTAL = BU.ADV_TAX + ISNULL(Z.ADV_TAX,0) + BU.INTEREST + ISNULL(Z.INTEREST,0) + BU.PENALTY + ISNULL(Z.PENALTY,0) + BU.FIFA + ISNULL(Z.FIFA ,0) + BU.GED + ISNULL(Z.GED ,0) + BU.FEES + ISNULL(Z.FEES ,0) + ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END + ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) + ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) + ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) FROM DY2021.DBO.BILL BU INNER JOIN ( SELECT BILL_NO, BILL_YEAR, SUM(ADV_TAX) AS ADV_TAX, SUM(INTEREST) INTEREST, SUM(PENALTY) PENALTY, SUM(FIFA) FIFA, SUM(GED) GED, SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER, SUM(ADMINISTRATION) ADMINISTRATION, SUM(ADVERTISING) ADVERTISING, SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , SUM(TITLE_SEARCH) TITLE_SEARCH , SUM(COLLECT_FEES) COLLECT_FEES FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END TITLE_SEARCH , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (4) --AND CATEGORY NOT IN (1) AND BILL_YEAR=2021 AND TAXAMT_1+TAXAMT_2+TAXAMT_3+TAXAMT_4+TAXAMT_5+TAXAMT_6+TAXAMT_7+TAXAMT_8+TAXAMT_9+TAXAMT_10 NOT LIKE '%-%' GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Q GROUP BY BILL_NO, BILL_YEAR ) Z ON CONVERT(INT,Z.BILL_NO)+@MH_BASEBILLNO=BU.BILL_NO AND Z.BILL_YEAR+1=BU.BILL_YEAR WHERE BU.BILL_YEAR=@TAXYEAR+1 AND BU.BILL_TYPE='M' and bu.TIMBER_VALUE=7 /**** UPDATE BILLING - MH - INT/PEN/FIFA/GED (ADJUSTMENT - CATEGORY 4 - DECREASES) ****/ UPDATE BU SET BU.ADV_TAX = BU.ADV_TAX + 0--Z.ADV_TAX , BU.INTEREST = BU.INTEREST + Z.INTEREST , BU.PENALTY = BU.PENALTY + Z.PENALTY , BU.FIFA = BU.FIFA + Z.FIFA , BU.GED = BU.GED + Z.GED , BU.FEES = BU.FEES + Z.FEES , BU.ADMINISTRATION = ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END , BU.ADVERTISING = ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) , BU.FEES_OTHER = ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) , BU.CREDIT_CARD_FEES = ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) , BU.TITLE_SEARCH = ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) , BU.COLLECT_FEES = ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) , BU.TOTAL = BU.ADV_TAX + 0--ISNULL(Z.ADV_TAX,0) + BU.INTEREST + ISNULL(Z.INTEREST ,0) + BU.PENALTY + ISNULL(Z.PENALTY,0) + BU.FIFA + ISNULL(Z.FIFA,0) + BU.GED + ISNULL(Z.GED ,0) + BU.FEES + ISNULL(Z.FEES ,0) + ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END + ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) + ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) + ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) FROM DY2021.DBO.BILL BU INNER JOIN ( SELECT BILL_NO, BILL_YEAR, SUM(ADV_TAX) AS ADV_TAX, SUM(INTEREST) INTEREST, SUM(PENALTY) PENALTY, SUM(FIFA) FIFA, SUM(GED) GED, SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER, SUM(ADMINISTRATION) ADMINISTRATION, SUM(ADVERTISING) ADVERTISING, SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , SUM(TITLE_SEARCH) TITLE_SEARCH , SUM(COLLECT_FEES) COLLECT_FEES FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 * -1.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END * -1.0 INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END * -1.0 PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 TITLE_SEARCH , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (4) --AND CATEGORY NOT IN (1) AND BILL_YEAR=2021 AND TAXAMT_1+TAXAMT_2+TAXAMT_3+TAXAMT_4+TAXAMT_5+TAXAMT_6+TAXAMT_7+TAXAMT_8+TAXAMT_9+TAXAMT_10 LIKE '%-%' GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Q GROUP BY BILL_NO, BILL_YEAR ) Z ON CONVERT(INT,Z.BILL_NO)+@MH_BASEBILLNO=BU.BILL_NO AND Z.BILL_YEAR+1=BU.BILL_YEAR WHERE BU.BILL_YEAR=@TAXYEAR+1 AND BU.BILL_TYPE='M' and bu.TIMBER_VALUE=7 /**** UPDATE BILLING - MH - INT/PEN/FIFA/GED (RELEASE - CATEGORY 3 - DECREASES) ****/ UPDATE BU SET BU.ADV_TAX = BU.ADV_TAX + 0--Z.ADV_TAX , BU.INTEREST = BU.INTEREST + Z.INTEREST , BU.PENALTY = BU.PENALTY + Z.PENALTY , BU.FIFA = BU.FIFA + Z.FIFA , BU.GED = BU.GED + Z.GED , BU.FEES = BU.FEES + Z.FEES , BU.ADMINISTRATION = ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END , BU.ADVERTISING = ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) , BU.FEES_OTHER = ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) , BU.CREDIT_CARD_FEES = ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) , BU.TITLE_SEARCH = ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) , BU.COLLECT_FEES = ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) , BU.TOTAL = BU.ADV_TAX + 0--ISNULL(Z.ADV_TAX,0) + BU.INTEREST + ISNULL(Z.INTEREST,0) + BU.PENALTY + ISNULL(Z.PENALTY ,0) + BU.FIFA + ISNULL(Z.FIFA ,0) + BU.GED + ISNULL(Z.GED ,0) + BU.FEES + ISNULL(Z.FEES ,0) + ISNULL(BU.ADMINISTRATION ,0) + CASE WHEN ISNULL(Z.ADMINISTRATION,0) > 0 THEN ISNULL(Z.ADMINISTRATION,0) ELSE 0 END + ISNULL(BU.ADVERTISING ,0) + ISNULL(Z.ADVERTISING,0) + ISNULL(BU.FEES_OTHER ,0) + ISNULL(Z.FEES_OTHER,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(Z.CREDIT_CARD_FEES,0) + ISNULL(BU.TITLE_SEARCH ,0) + ISNULL(Z.TITLE_SEARCH,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(Z.COLLECT_FEES,0) FROM DY2021.DBO.BILL BU INNER JOIN ( SELECT BILL_NO, BILL_YEAR, SUM(ADV_TAX) AS ADV_TAX, SUM(INTEREST) INTEREST, SUM(PENALTY) PENALTY, SUM(FIFA) FIFA, SUM(GED) GED, SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER, SUM(ADMINISTRATION) ADMINISTRATION, SUM(ADVERTISING) ADVERTISING, SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , SUM(TITLE_SEARCH) TITLE_SEARCH , SUM(COLLECT_FEES) COLLECT_FEES FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO AS ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 * -1.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END * -1.0 INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END * -1.0 PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 TITLE_SEARCH , CASE WHEN CATEGORY = @CAT_COLLECTFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END * -1.0 COLLECT_FEES FROM CASH WHERE APPCODE=4 AND RECTYPE IN (3) --AND CATEGORY NOT IN (1) AND BILL_YEAR=2021 AND TAXAMT_1+TAXAMT_2+TAXAMT_3+TAXAMT_4+TAXAMT_5+TAXAMT_6+TAXAMT_7+TAXAMT_8+TAXAMT_9+TAXAMT_10 LIKE '%-%' GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , RECTYPE ) Q GROUP BY BILL_NO, BILL_YEAR ) Z ON CONVERT(INT,Z.BILL_NO)+@MH_BASEBILLNO=BU.BILL_NO AND Z.BILL_YEAR+1=BU.BILL_YEAR WHERE BU.BILL_YEAR=@TAXYEAR+1 AND BU.BILL_TYPE='M' and bu.TIMBER_VALUE=7 print 'payments' SET @PAYMENTKEY = (SELECT MAX(PAYMENTKEY) FROM DY2021.DBO.PAYMENT) INSERT INTO DY2021.[dbo].[PAYMENT] ([PAYMENTKEY] ,[BILLKEY] ,[ADV_TAX] ,[INTEREST] ,[PENALTY] ,[FIFA] ,[GED] ,[BREACH] ,[TITLE_SEARCH] ,[MAIL_FEES] ,[ADMINISTRATION] ,[PRIOR_YEARS] ,[ADVERTISING] ,[CREDIT_CARD_FEES] ,[TOTAL] ,[PAYMENT_TYPE] ,[PAID_BY] ,[BALANCE_DUE] ,[CARD_TYPE] ,[AUTH_CODE] ,[PAYMENTDATE] ,[CNUM] ,[COMPNAME] ,[LOGINID] ,[REGISTER] ,[EXCESS_FUNDS] ,[OVERAGES] ,[FEES] ,[FEES_OTHER] ,[COMMENTS] ,[COLLECT_FEES] ,[BILL_YEAR] ,[REVERSAL] ,[CHANGE] ,[PAID_AMT] ,[BILL_NO] ,[BILL_TYPE] ,[REFUND] ,[UNPAID] ,[ACCTING] ,[ACCTING_DATE] ) select @PAYMENTKEY + ROW_NUMBER() OVER( order by x.posting_date, x.trans_time ) AS PAYMENTKEY , B.BILLKEY , X.ADVTAX , X.INTEREST , X.PEN , X.FIFA , X.GED , 0.0 BREACH , X.TITLE_SEARCH , 0.0 MAIL_FEES , X.ADMINISTRATION , 0.0 PRIOR_YEARS , X.ADVERTISING , X.CREDIT_CARD_FEES , X.ADVTAX + X.INTEREST + X.PEN + X.FIFA + X.GED + X.FEES + X.FEES_OTHER + X.TITLE_SEARCH + X.ADVERTISING + X.ADMINISTRATION + X.CREDIT_CARD_FEES AS TOTAL , X.PAYMENT_TYPE , X.PAID_BY , 0.0 AS BALANCE_DUE , '' AS CARD_TYPE , '' AS AUTH_CODE , TRY_CONVERT(DATETIME,RIGHT(X.POSTING_DATE,4)+LEFT(X.POSTING_DATE,2)+SUBSTRING(X.POSTING_DATE,3,2) + ' ' + LEFT(X.TRANS_TIME,2)+':'+SUBSTRING(X.TRANS_TIME,3,2)+':'+SUBSTRING(X.TRANS_TIME,5,2)) AS PAYMENT_DATE , TRY_CONVERT(BIGINT,X.CHECK_NO) AS CNUM , X.COMPNAME , X.COMPNAME AS LOGINID , X.REGISTER , 0.0 AS EXCESS_FUNDS , 0.0 AS OVERAGES , X.FEES , X.FEES_OTHER FEES_OTHER , 'Convert Note: timber value=7' AS COMMENTS , 0.0 AS COLLECT_FEES , B.BILL_YEAR , 0 AS REVERSAL , 0.0 AS CHANGE , X.ADVTAX + X.INTEREST + X.PEN + X.FIFA + X.GED + X.FEES + X.FEES_OTHER + X.TITLE_SEARCH + X.ADVERTISING + X.ADMINISTRATION + X.CREDIT_CARD_FEES AS PAID_AMT , B.BILL_NO , B.BILL_TYPE , 0 AS REFUND , 0 AS UNPAID , NULL AS ACCTING , NULL AS ACCTING_DATE from dy2021.dbo.bill b inner join ( SELECT BILL_NO , BILL_YEAR , POSTING_DATE , TRANS_TIME , SUM(ADV_TAX) ADVTAX , SUM(INTEREST) INTEREST , SUM(PENALTY) PEN , SUM(FIFA) FIFA , SUM(GED) GED , SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER , 0.0 BREACH , SUM(TITLE_SEARCH) TITLE_SEARCH , 0.0 MAIL_FEES , SUM(ADMINISTRATION) ADMINISTRATION , 0.0 PRIOR_YEARS , SUM(ADVERTISING) ADVERTISING , SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , 'COLLECTION' AS PAYMENT_TYPE , PROPNAME AS PAID_BY , COMPNAME , REGISTER , CHECK_NO FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END TITLE_SEARCH FROM CASH WHERE APPCODE=4 AND RECTYPE IN (5) --AND CATEGORY=1 AND BILL_YEAR=2021 --and BILL_NO=90 GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO ) Q GROUP BY BILL_NO, BILL_YEAR, POSTING_DATE, TRANS_TIME, PROPNAME , COMPNAME , REGISTER , CHECK_NO UNION ALL SELECT BILL_NO , BILL_YEAR , POSTING_DATE , TRANS_TIME , SUM(ADV_TAX) ADVTAX , SUM(INTEREST) INTEREST , SUM(PENALTY) PEN , SUM(FIFA) FIFA , SUM(GED) GED , SUM(FEES) FEES , SUM(FEES_OTHER) FEES_OTHER , 0.0 BREACH , SUM(TITLE_SEARCH) TITLE_SEARCH , 0.0 MAIL_FEES , SUM(ADMINISTRATION) ADMINISTRATION , 0.0 PRIOR_YEARS , SUM(ADVERTISING) ADVERTISING , SUM(CREDIT_CARD_FEES) CREDIT_CARD_FEES , 'UNPAID-REVERSAL' AS PAYMENT_TYPE , PROPNAME AS PAID_BY , COMPNAME , REGISTER , CHECK_NO FROM ( SELECT BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO , (SUM(CONVERT(INT,REPLACE(TOTALTAXAMT ,'-','')) ) ) / 100.0 * -1.0 TOTALTAXAMT , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) --+ SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END ADV_TAX , CASE WHEN CATEGORY = 1 THEN ( SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END FEES , CASE WHEN CATEGORY = @CAT_INTEREST THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0.0 END INTEREST , CASE WHEN CATEGORY = @CAT_PENALTY THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0.0 END PENALTY , CASE WHEN CATEGORY = @CAT_FIFA THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END FIFA , CASE WHEN CATEGORY = @CAT_GED THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END GED , CASE WHEN CATEGORY = @CAT_MAILFEES THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 ELSE 0 END ADMINISTRATION , CASE WHEN CATEGORY = @CAT_ADMIN THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END FEES_OTHER , CASE WHEN CATEGORY = @CAT_ADVERTISING THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END ADVERTISING , CASE WHEN CATEGORY = @CAT_BREACH THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END CREDIT_CARD_FEES , CASE WHEN CATEGORY = @CAT_CREDITCARDFEE THEN (SUM(CONVERT(INT,REPLACE(TAXAMT_1 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_2 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_3 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_4 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_5 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_6 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_7 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_8 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_9 ,'-','')) ) + SUM(CONVERT(INT,REPLACE(TAXAMT_10 ,'-','')) ) ) / 100.0 * -1.0 ELSE 0 END TITLE_SEARCH FROM CASH WHERE APPCODE=4 AND RECTYPE IN (6) --AND CATEGORY=1 AND BILL_YEAR=2021 --and BILL_NO=90 GROUP BY BILL_NO , BILL_YEAR , CATEGORY , POSTING_DATE , TRANS_TIME , REGISTER , PROPNAME , ACCT_NO , TAXDISTRIC , COMPNAME , CHECK_NO ) Q GROUP BY BILL_NO, BILL_YEAR, POSTING_DATE, TRANS_TIME, PROPNAME , COMPNAME , REGISTER , CHECK_NO ) x on x.BILL_NO+@MH_BASEBILLNO=b.BILL_NO and x.BILL_YEAR+1=b.BILL_YEAR and b.TIMBER_VALUE=7 PRINT 'UPDATE BALANCE DUE' UPDATE BU SET BU.BALANCE_DUE=BU.TOTAL-ISNULL(P.PAID_AMT,0) --SELECT BU.TOTAL, BU.BALANCE_DUE, BU.DUE_DATE, P.PAID_AMT FROM DY2021.DBO.BILL BU LEFT JOIN (SELECT BILL_NO, BILL_YEAR, SUM(PAID_AMT) PAID_AMT FROM DY2021.DBO.PAYMENT GROUP BY BILL_NO, BILL_YEAR) P ON CONVERT(INT,P.BILL_NO)=CONVERT(INT,BU.BILL_NO) AND P.BILL_YEAR=BU.BILL_YEAR where bu.TIMBER_VALUE=7