/******** Master Convert record *******/

DECLARE @MH_BASEBILLNO  INT  = 250000
DECLARE @HDE_BASEBILLNO INT  = 500000
DECLARE @TIMB_BASEBILLNO INT  = 400000

DECLARE @TAXYEAR INT = 2022
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

/************* CATEGORIES - EACH GSI COUNTY CAN BE DIFFERENT - GET SCREEN PRINT SHOWING CATEGORY NUMBERS AND LABEL ************/
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

/************* CATEGORIES - EACH GSI COUNTY CAN BE DIFFERENT - GET SCREEN PRINT SHOWING CATEGORY NUMBERS AND LABEL ************/

-- TIMB --
set @CAT_ADVTAX				= 1
set @CAT_INTEREST 			= 3	
set @CAT_PENALTY 			= 4 -- 6,7,8,9,10,11
set @CAT_ADMIN 				= 88
set @CAT_FIFA 				= 88
set @CAT_CREDITCARDFEE 		= 88 -- LATE FILTING >> ADV_TAX
--set @CAT_DUPFEE 			= 88 -- "COPY FEE >> GOES INTO ADMIN
set @CAT_GED 				= 88
set @CAT_BREACH 			= 88
set @CAT_COLLECTFEES 		= 88
set @CAT_ADVERTISING 		= 88
set @CAT_TITLESEARCH 		= 88
set @CAT_MAILFEES 			= 88
set @CAT_ABATEMENT 			= 88



SET @BILLKEY = (SELECT MAX(BILLKEY) FROM DY2022.DBO.BILL)
/************* CATEGORIES - EACH GSI COUNTY CAN BE DIFFERENT - GET SCREEN PRINT SHOWING CATEGORY NUMBERS AND LABEL ************/
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

declare @PK as INT = 11130

-- search for "year's support" in comments and flag property
-- "RETURNED CHECK"

PRINT 'TAX DETAILS';
--INSERT INTO DY2022.DBO.TAXDETAIL (REALKEY, PERSKEY, TAXTYPE, TT_DESC, ASMTPCT, COVEXMPT, HSEXEMPT, MILLAGE, ESTTAX, PROPERTYKEY, ACCKEY)
with tc as 
(
SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 1 AS TAXTYPE
	, 'STATE' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_1 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_1) taxamt_1 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO)
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=1 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=1 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=1 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 1 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR 
	--and p.propertykey=@PK

UNION 

SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 2 AS TAXTYPE
	, 'COUNTY' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_2 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_2) taxamt_2 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO)
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR

LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=2 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=2 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=2 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 2 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR 
	--and p.propertykey=@PK

UNION 

SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 3 AS TAXTYPE
	, 'SCHOOL' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_3 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_3) taxamt_3 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO)
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR

LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=3 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=3 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=3 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 3 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR 
	--and p.propertykey=@PK

UNION 

SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 5 AS TAXTYPE
	, 'Street Light' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_5 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_5) taxamt_5 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO) and C.TAXAMT_5 > 0
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=5 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=5 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=5 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 5 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR  AND C.TAXAMT_5 > 0
	--and p.propertykey=@PK


UNION 

SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 4 AS TAXTYPE
	, 'THOMSON' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_6 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_6) taxamt_6 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO)
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR

LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=4 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=4 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=4 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 4 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR 
	--and p.propertykey=@PK

UNION 

SELECT 
	P.REALKEY 
	, P.PERSKEY 
	, 11 AS TAXTYPE
	, 'Fire Fee' AS TT_DESC
	, .40 AS ASMTPCT
	,  ROUND(
		(( 
		CASE WHEN LEFT(G.ST_CODE1,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE1 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE2,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE2 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE3,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE3 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE4,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE4 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE5,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE5 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE6,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE6 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE7,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE7 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE8,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE8 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE9,1)  IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE9 ) ELSE 0 END +
		CASE WHEN LEFT(G.ST_CODE10,1) IN ('V','W','T','P','J','H','B') THEN CONVERT(INT,G.ST_VALUE10) ELSE 0 END 
		) 
		- CONVERT(INT,G.COV_VALUE)) * 0.4,0)
		AS COVEXMPT 
	, ISNULL(SC.MAXEXEMPT,0) + ISNULL(TTES.MAXEXEMPT,0) + ISNULL(TTEL.MAXEXEMPT ,0) + CASE WHEN TTES.HSFREEZE=1 THEN ROUND(CONVERT(INT,G.HS_VAL1)*.4,0)-ROUND(CONVERT(INT,G.HS_BASE1) *.4,0) ELSE 0 END
		AS HSEXEMPT
	, ISNULL(M.MILLS,0) AS MILLAGE
	, C.TAXAMT_7 AS ESTTAX
	, P.PROPERTYKEY
	, 0 AS ACCKEY
FROM (select appcode, category, bill_year, bill_no, 1 as rectype, sum(taxamt_7) taxamt_7 from vCASH where bill_year=@TAXYEAR and appcode=1 and category=1 and rectype in (1,2,3,4) group by appcode, category, bill_year, bill_no, category)  C
INNER JOIN DY2022.DBO.BILL B ON CONVERT(INT,B.BILL_NO)=CONVERT(INT,C.BILL_NO) and C.TAXAMT_7 > 0
INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY
INNER JOIN MCDUFFIE_GSI.DBO.PARCEL G ON G.ACCT_NO+G.LINE_NO=P.LEGACY_ACCT AND G.BILL_YEAR= @TAXYEAR

LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTES ON TTES.TAXTYPE=11 AND TTES.EXCODE=P.HOMEEXEMPT
LEFT JOIN (
			SELECT EC.EXCODE, EC.STATECODE, TTE.TAXTYPE, TTE.MAXEXEMPT 
			FROM DY2022.DBO.EXEMPTCODE EC 
			INNER JOIN DY2022.DBO.TAXTYPE_EXEMPT TTE ON TTE.EXCODE=EC.STATECODE AND LEFT(EC.EXCODE,1)<>'S'

			) SC ON SC.TAXTYPE=11 AND SC.EXCODE=P.HOMEEXEMPT
LEFT JOIN DY2022.DBO.TAXTYPE_EXEMPT TTEL ON TTEL.TAXTYPE=11 AND TTES.EXCODE=P.HOMEEXEMPT+'L'+RIGHT(P.HOMEEXEMPT,1)
LEFT JOIN DY2022.DBO.MILLRATE M ON M.TAXTYPE = 11 AND M.TAXDISTRIC=P.TAXDISTRIC AND USE_REAL=1

WHERE C.APPCODE=1 AND C.CATEGORY=1 AND C.RECTYPE=1 AND C.BILL_YEAR= @TAXYEAR  AND C.TAXAMT_7 > 0
	--and p.propertykey=@PK

)

update td set td.hsexempt=tc.hsexempt
from dy2022.dbo.TAXDETAIL td 
inner join tc on tc.realkey=td.realkey and tc.REALKEY=td.REALKEY and tc.TAXTYPE=td.TAXTYPE
inner join dy2022.dbo.property p on p.realkey=td.realkey and p.realkey > 0 and p.HOMEEXEMPT <> 's0' and tc.HSEXEMPT <> td.HSEXEMPT and p.BR_CASENO='7'

update dy2022.dbo.property set BR_CASENO='' where BR_CASENO='7' and HOMEEXEMPT in ('H65','H65C')