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
