IF EXISTS (	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBO' AND TABLE_NAME='TXMBF2' ) DROP TABLE TXMBF2

SELECT * 
INTO TXMBF2 
FROM 
(
	select *, 1 x from txmbf where MBTYR=2022 and MBFIL1 in ('r','p') and mbact not in (select mbact from txmbf where MBTYR=2022 and MBFIL1 in ('r','p') and MBNOD!= ' ')
	union
	select * from (select *, ROW_NUMBER() over(partition by mbact order by mbbil desc) as rowid from txmbf 
	where MBTYR=2022 and MBFIL1 in ('r','p') and mbnod!= ' ' and mbact in (select mbact from txmbf where MBTYR=2022 and MBFIL1 in ('r','p') and MBNOD!= ' ' )) x where rowid=1			
			) Q

/*	CLEANUP BEFORE CONVERT
update TXMBF set mbact='20346R22' where mbtyr=2022 and mbbil=29613 --YEAR = 22
update TXMBF set mbact='11295R22' WHERE MBTYR=2022 AND MBBIL=9282 --YEAR = 17

update txmbf set mbact= '   3786R17' where mbtyr=2000+19 and mbbil=2110
update txmbf set mbact= '     6620R' where mbtyr=2000+18 and mbbil=5259
update txmbf set mbact= '      524R' where mbtyr=2000+17 and mbbil=6370
update txmbf set mbact= '     38R22' where mbtyr=2000+20 and mbbil=3093
update txmbf set mbact= '   5977R22' where mbtyr=2000+20 and mbbil=250
update txmbf set mbact= '   5814R22' where mbtyr=2000+22 and mbbil=1189
update txmbf set mbact= '   7095R22' where mbtyr=2000+20 and mbbil=1137
update txmbf set mbact= '    817R22' where mbtyr=2000+20 and mbbil=1166
update txmbf set mbact= '   6995R22' where mbtyr=2000+20 and mbbil=5631

*/

TRUNCATE TABLE DY2022.DBO.GENERAL_LEDGER
TRUNCATE TABLE DY2022.DBO.INTEREST_MH
TRUNCATE TABLE DY2022.DBO.INTEREST_RP
TRUNCATE TABLE DY2022.DBO.BILL_TRANSACTIONS
TRUNCATE TABLE DY2022.DBO.TAXMASTER 
TRUNCATE TABLE DY2022.DBO.TAXDETAIL 
TRUNCATE TABLE DY2022.DBO.PROPERTY_ITEMS
TRUNCATE TABLE DY2022.DBO.D_REAL 
TRUNCATE TABLE DY2022.DBO.D_PERS 
TRUNCATE TABLE DY2022.DBO.M_MOBILE 
TRUNCATE TABLE DY2022.DBO.OWNER 
TRUNCATE TABLE DY2022.DBO.PAYMENT 
TRUNCATE TABLE DY2022.DBO.PAYMENT_ALLOCATIONS
TRUNCATE TABLE DY2022.DBO.TAXFEE_ITEMS -- POPULATED FROM TAXDETAIL 
TRUNCATE TABLE DY2022.DBO.TAXFEE_TYPES -- POPULATED FROM TAXFEE
DELETE FROM DY2022.DBO.BILL 
DELETE FROM DY2022.DBO.PROPERTY 



DECLARE @PROPKEY AS INT = 0
DECLARE @OWNKEY AS INT = 0
DECLARE @BILLKEY AS INT = 0
DECLARE @DIGYR AS INT = 2022

PRINT @DIGYR
PRINT 'If you have not manually populated TAXFEE - you must. Penalty and Interest are separate scripts.'

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


-- REAL
PRINT 'REAL PROPERTY'
INSERT INTO DY2022.[dbo].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   )

 SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, CASE 
		WHEN M.MBACT LIKE '%R22' THEN REPLACE(M.MBACT,'R22','') 
		ELSE 0
		END
		AS REALKEY 
	, 0	AS PERSKEY
	, 0 AS MOBILEKEY
	, CASE 
		WHEN M.MBACT LIKE '%R22' THEN 'R' 
		ELSE 'X' END AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, '' AS MHYEAR 
	, 0 AS MHVALUE 
	, '' AS MHMFG 
	, '' AS MHMODEL 
	, '' AS MHSIZE
	, '' AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, CASE WHEN M.MBNOD IN ('N') THEN 1 ELSE 0 END AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	FROM TXMBF2 M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF2 ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR AND M.MBLOC NOT LIKE '%BREACH%' and M.MBFIL1 = 'R' AND M.MBACT LIKE '%R22'
	ORDER BY M.MBTYR
;

--DELETE FROM DY2022.DBO.PROPERTY WHERE REALKEY > 0 AND REALKEY IN (SELECT REALKEY FROM DY2022.DBO.PROPERTY WHERE NOD=1) AND NOD=0

PRINT 'REAL OWNERS'
INSERT INTO DY2022.[dbo].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, LEFT(M.MBCIT,20) AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	FROM TXMBF2 M
	WHERE M.MBTYR= @DIGYR AND M.MBACT LIKE '%R22'
	;

PRINT 'REAL PROPERTY UPDATE OWNKEY'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0


SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

-- REAL BREACH
--PRINT 'REAL PROPERTY - BREACH'
--INSERT INTO DY2022.[dbo].[PROPERTY]
--           ([PROPERTYKEY]
--           ,[REALKEY]
--           ,[PERSKEY]
--           ,[MOBILEKEY]
--           ,[PROPTYPE]
--           ,[DIGYR]
--           ,[PARCEL_NO]
--           ,[HOMEEXEMPT]
--           ,[DEEDBOOK]
--           ,[PLATBOOK]
--           ,[LEGAL_DESC]
--           ,[APPEAL]
--           ,[BANKRUPT]
--           ,[GUARANTEE]
--           ,[TOTALACRES]
--           ,[CURR_VAL]
--           ,[ASMT_VAL]
--           ,[PREV_VAL]
--           ,[SALE_VAL]
--           ,[SALELAND1]
--           ,[SALELAND2]
--           ,[SALEIMP1]
--           ,[SALEIMP2]
--           ,[A_VALUE]
--           ,[P_VALUE]
--           ,[FMVRES]
--           ,[FMVCOM]
--           ,[FMVACC]
--           ,[MILLAGE]
--           ,[MILLAGE_YEAR]
--           ,[HOUSE_NO]
--           ,[EXTENSION]
--           ,[STDIRECT]
--           ,[STTYPE]
--           ,[STREET_NAM]
--           ,[UNIT]
--           ,[QUAD]
--           ,[UNITTYPE]
--           ,[PCY]
--           ,[CCY]
--           ,[HCY]
--           ,[FLCY]
--           ,[EZY]
--           ,[EZ_EXEMPT]
--           ,[HISTVAL]
--           ,[ORIGHOMVAL]
--           ,[CURRHOMVAL]
--           ,[ORIGHSVAL2]
--           ,[CURRHSVAL2]
--           ,[PREVHOMVAL]
--           ,[PREVHOMVAL2]
--           ,[MHYEAR]
--           ,[MHVALUE]
--           ,[MHMFG]
--           ,[MHMODEL]
--           ,[MHSIZE]
--           ,[MHVIN]
--           ,[COMMENTS]
--           ,[NOD]
--           ,[E_R]
--           ,[ADD_DATE]
--           ,[TAXDISTRIC]
--           ,[PURCHASE_PRICE]
--           ,[DATE_PURCHASED]
--           ,[LEVY]
--           ,[HOLD]
--           ,[PAY_PLAN]
--           ,[OWNKEY]
--           ,[BR_CASENO]
--           ,[FIFA_DATE]
--           ,[FIFA]
--           ,[MHDECALNUM]
--		   ,[PURCHASE_PRICE_HT]
--		   ,[YEARS_SUPPORT]
--		   ,[APPEAL100]
--		   ,[REFUND]
--		   ,[EXEMPT]
--		   ,[LEGACY_ACCT]
--		   ,[HARVEST_TYPE]
--		   ,[HARVEST_QTR] 
--		   )

-- SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
--	, CASE 
--		WHEN M.MBACT LIKE '%BOC%' THEN REPLACE(M.MBACT,'BOC','') 
--		ELSE 0
--		END
--		AS REALKEY 
--	, 0	AS PERSKEY
--	, 0 AS MOBILEKEY
--	, 'R' AS PROPTYPE
--	, M.MBTYR AS DIGYR
--	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
--	, M.MBHSC AS HOMEEXEMPT
--	, '' AS DEEDBOOK
--	, '' AS PLATBOOK
--	, M.MBLOC AS LEGAL_DESC
--	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL 
--	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
--	, 0 AS GUARANTEE
--	, CS.ACRES AS TOTALACRES
--	, M.MBFMV AS CURR_VAL 
--	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
--	, PV.PREV_VAL
--	, 0 AS SALE_VAL
--	, 0 AS SALELAND1 
--	, 0 AS SALELAND2
--	, 0 AS SALEIMP1
--	, 0 AS SALEIMP2
--	, 0 AS A_VALUE 
--	, 0 AS P_VALUE 
--	, 0 AS FMVRES 
--	, 0 AS FMVCOM 
--	, 0 AS FMVACC 
--	, 0 AS MILLAGE 
--	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
--	, 0 AS HOUSE_NO
--	, '' AS EXTENSION
--	, '' AS STDIRECT 
--	, '' AS STTYPE 
--	, '' AS STREET_NAM 
--	, '' AS UNIT 
--	, '' AS QUAD 
--	, '' AS UNITTYPE
--	, '' AS PCY 
--	, '' AS CCY 
--	, '' AS HCY 
--	, '' AS FLCY 
--	, '' AS EZY 
--	, '' AS EZ_EXEMPT 
--	, 0 AS HISTVAL 
--	, 0 AS ORIGHOMVAL 
--	, 0 AS CURRHOMVAL 
--	, 0 AS ORIGHSVAL2 
--	, 0 AS CURRHSVAL2
--	, 0 AS PREVHOMVAL 
--	, 0 AS PREVHOMVAL2
--	, '' AS MHYEAR 
--	, 0 AS MHVALUE 
--	, '' AS MHMFG 
--	, '' AS MHMODEL 
--	, '' AS MHSIZE
--	, '' AS MHVIN
--	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
--	, CASE WHEN M.MBNOD IN ('N') THEN 1 ELSE 0 END AS NOD
--	, 0 AS E_R
--	, NULL AS ADD_DATE 
--	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
--	, 0 AS PURCHASE_PRICE
--	, 0 AS DATE_PURCHASED 
--	, 0 AS LEVY 
--	, 0 AS HOLD 
--	, 0 AS PAY_PLAN 
--	, 0 AS OWNKEY 
--	, '' AS BR_CASENO
--	, NULL AS FIFA_DATE 
--	, 0 AS FIFA 
--	, 0 AS MHDECALNUM 
--	, 0 AS PURCHASE_PRICE_HT
--	, 0 AS YEARS_SUPPORT
--	, 0 AS APPEAL100
--	, 0 AS REFUND
--	, 0 AS EXEMPT
--	, M.MBACT AS LEGACY_ACCT
--	, NULL AS HARVEST_TYPE
--	, 0 AS HARVEST_QTR
--	FROM TXMBF M
--	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
--	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF WHERE MBTYR=@DIGYR AND MBACT LIKE '%BOC%') PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
--	LEFT JOIN 
--		(
--		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
--		UNION
--		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
--		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
--	WHERE M.MBTYR= @DIGYR AND M.MBACT LIKE '%boc%'
--	ORDER BY M.MBTYR
--;

----DELETE FROM DY2022.DBO.PROPERTY WHERE REALKEY > 0 AND REALKEY IN (SELECT REALKEY FROM DY2022.DBO.PROPERTY WHERE NOD=1) AND NOD=0

--PRINT 'REAL OWNERS'
--INSERT INTO DY2022.[dbo].[OWNER]
--           ([OWNKEY]
--           ,[LASTNAME]
--           ,[ADDRESS1]
--           ,[ADDRESS2]
--           ,[ADDRESS3]
--           ,[CITY]
--           ,[STATE]
--           ,[ZIP]
--		   ,[SITEADDID]
--		   ,[FEI]
--	)
--SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
--	, M.MBNM1 AS LASTNAME 
--	, M.MBNM2 AS ADDRESS1 
--	, M.MBAD1 AS ADDRESS2 
--	, M.MBAD2 AS ADDRESS3 
--	, LEFT(M.MBCIT,20) AS CITY 
--	, M.MBSTE AS STATE 
--	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
--	, M.MBACT
--	, M.MBFIL1
--	FROM TXMBF2 M
--	WHERE M.MBTYR= @DIGYR AND M.MBACT LIKE '%boc%'
--	;

--PRINT 'REAL PROPERTY UPDATE OWNKEY'
--UPDATE PU SET PU.OWNKEY=O.OWNKEY 
--FROM DY2022.[dbo].[PROPERTY] PU 
--INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0

-- PERSONAL 
PRINT 'PERS PROPERTY'
SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

INSERT INTO DY2022.[DBO].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   )

 SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, 0	AS REALKEY 
	, CASE 
		WHEN M.MBACT LIKE '%P22' THEN REPLACE(M.MBACT,'P22','') 
		ELSE TRY_CONVERT(INT,M.MBACT)
		END
		AS PERSKEY
	, 0 AS MOBILEKEY
	, 'P' AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, '' AS MHYEAR 
	, 0 AS MHVALUE 
	, '' AS MHMFG 
	, '' AS MHMODEL 
	, '' AS MHSIZE
	, '' AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, CASE WHEN M.MBNOD IN ('N') THEN 1 ELSE 0 END AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	FROM TXMBF2 M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF2 ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('P') AND M.MBACT LIKE '%P22'
	ORDER BY M.MBTYR
	;


--DELETE FROM DY2022.DBO.PROPERTY WHERE PERSKEY > 0 AND PERSKEY IN (SELECT PERSKEY FROM DY2022.DBO.PROPERTY WHERE NOD=1) AND NOD=0

PRINT 'PERS OWNERS'
INSERT INTO DY2022.[DBO].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, M.MBCIT AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	FROM TXMBF2 M
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('P') AND M.MBACT LIKE '%P22'--AND M.MBFIL1='P' AND MBMAP='0029d' and mbpar='058'
	;


PRINT 'PERS PROPERTY OWNKEYS'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0


SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

-- HDE
 PRINT 'HDE PROPERTY'
INSERT INTO DY2022.[DBO].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   )

SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, 0	AS REALKEY 
	, 0 AS PERSKEY
	, 0 AS MOBILEKEY
	, M.MBFIL1 AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, '' AS MHYEAR 
	, 0 AS MHVALUE 
	, '' AS MHMFG 
	, '' AS MHMODEL 
	, '' AS MHSIZE
	, '' AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, 0 AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	FROM TXMBF M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1='H'
	ORDER BY M.MBTYR
;



 PRINT 'HDE OWNERS'
INSERT INTO DY2022.[DBO].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, M.MBCIT AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	FROM TXMBF M
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('H') 
	;


 PRINT 'HDE PROPERTY OWNKEYS'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0



SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

-- PU
 PRINT 'PU PROPERTY'
INSERT INTO DY2022.[DBO].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   )

SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, CASE 
		WHEN M.MBACT LIKE '%r22' THEN REPLACE(M.MBACT,'R22','') 
		ELSE 0
		END
		AS REALKEY 
	, 0 AS PERSKEY
	, 0 AS MOBILEKEY
	, 'R' AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, '' AS MHYEAR 
	, 0 AS MHVALUE 
	, '' AS MHMFG 
	, '' AS MHMODEL 
	, '' AS MHSIZE
	, '' AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, 0 AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	FROM TXMBF M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1='U'
	ORDER BY M.MBTYR
;



 PRINT 'HDE OWNERS'
INSERT INTO DY2022.[DBO].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, M.MBCIT AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	FROM TXMBF M
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('U') 
	;


 PRINT 'HDE PROPERTY OWNKEYS'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0



SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

-- TIMBER
 PRINT 'TIMBER PROPERTY'
INSERT INTO DY2022.[DBO].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   )

SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, 0	AS REALKEY 
	, 0 AS PERSKEY
	, 0 AS MOBILEKEY
	, M.MBFIL1 AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, '' AS MHYEAR 
	, 0 AS MHVALUE 
	, '' AS MHMFG 
	, '' AS MHMODEL 
	, '' AS MHSIZE
	, '' AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, 0 AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	FROM TXMBF M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('T') AND M.MBNOD <> 'O'
	ORDER BY M.MBTYR
;



 PRINT 'TIMBER OWNERS'
INSERT INTO DY2022.[DBO].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, M.MBCIT AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	FROM TXMBF M
	WHERE M.MBTYR= @DIGYR AND M.MBFIL1 IN ('T') --AND M.MBNOD='' AND MBMAP='0029d' and mbpar='058'
	;

	
 PRINT 'TIMBER PROPERTY OWNKEYS'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0


SET @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2022.DBO.PROPERTY) 
SET @OWNKEY = (SELECT MAX(OWNKEY) FROM DY2022.DBO.OWNER) 

-- MH
 PRINT 'MH PROPERTY'
INSERT INTO DY2022.[DBO].[PROPERTY]
           ([PROPERTYKEY]
           ,[REALKEY]
           ,[PERSKEY]
           ,[MOBILEKEY]
           ,[PROPTYPE]
           ,[DIGYR]
           ,[PARCEL_NO]
           ,[HOMEEXEMPT]
           ,[DEEDBOOK]
           ,[PLATBOOK]
           ,[LEGAL_DESC]
           ,[APPEAL]
           ,[BANKRUPT]
           ,[GUARANTEE]
           ,[TOTALACRES]
           ,[CURR_VAL]
           ,[ASMT_VAL]
           ,[PREV_VAL]
           ,[SALE_VAL]
           ,[SALELAND1]
           ,[SALELAND2]
           ,[SALEIMP1]
           ,[SALEIMP2]
           ,[A_VALUE]
           ,[P_VALUE]
           ,[FMVRES]
           ,[FMVCOM]
           ,[FMVACC]
           ,[MILLAGE]
           ,[MILLAGE_YEAR]
           ,[HOUSE_NO]
           ,[EXTENSION]
           ,[STDIRECT]
           ,[STTYPE]
           ,[STREET_NAM]
           ,[UNIT]
           ,[QUAD]
           ,[UNITTYPE]
           ,[PCY]
           ,[CCY]
           ,[HCY]
           ,[FLCY]
           ,[EZY]
           ,[EZ_EXEMPT]
           ,[HISTVAL]
           ,[ORIGHOMVAL]
           ,[CURRHOMVAL]
           ,[ORIGHSVAL2]
           ,[CURRHSVAL2]
           ,[PREVHOMVAL]
           ,[PREVHOMVAL2]
           ,[MHYEAR]
           ,[MHVALUE]
           ,[MHMFG]
           ,[MHMODEL]
           ,[MHSIZE]
           ,[MHVIN]
           ,[COMMENTS]
           ,[NOD]
           ,[E_R]
           ,[ADD_DATE]
           ,[TAXDISTRIC]
           ,[PURCHASE_PRICE]
           ,[DATE_PURCHASED]
           ,[LEVY]
           ,[HOLD]
           ,[PAY_PLAN]
           ,[OWNKEY]
           ,[BR_CASENO]
           ,[FIFA_DATE]
           ,[FIFA]
           ,[MHDECALNUM]
		   ,[PURCHASE_PRICE_HT]
		   ,[YEARS_SUPPORT]
		   ,[APPEAL100]
		   ,[REFUND]
		   ,[EXEMPT]
		   ,[LEGACY_ACCT]
		   ,[HARVEST_TYPE]
		   ,[HARVEST_QTR] 
		   ,[HS_MH]
		   )

SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, 0	AS REALKEY 
	, 0 AS PERSKEY
	, M.MBACT AS MOBILEKEY
	, 'M' AS PROPTYPE
	, M.MBTYR AS DIGYR
	, M.MBMAP+MBBLK+MBPAR+MBSUB AS PARCEL_NO 
	, M.MBHSC AS HOMEEXEMPT
	, '' AS DEEDBOOK
	, '' AS PLATBOOK
	, M.MBLOC AS LEGAL_DESC
	, CASE WHEN M.MBAPL='Y' THEN 1 ELSE 0 END AS APPEAL -- Sarae says they don't which properties are under appeal ???
	, CASE WHEN M.MBKRP='Y' THEN 1 ELSE 0 END AS BANKRUPT
	, 0 AS GUARANTEE
	, 0--CS.ACRES AS TOTALACRES
	, M.MBFMV AS CURR_VAL 
	, ROUND(M.MBFMV * .4,0) AS ASMT_VAL 
	, 0 -- PV.PREV_VAL
	, 0 AS SALE_VAL
	, 0 AS SALELAND1 
	, 0 AS SALELAND2
	, 0 AS SALEIMP1
	, 0 AS SALEIMP2
	, 0 AS A_VALUE 
	, 0 AS P_VALUE 
	, 0 AS FMVRES 
	, 0 AS FMVCOM 
	, 0 AS FMVACC 
	, 0 AS MILLAGE 
	, M.MBTYR AS MILLAGE_YEAR -- REMEMBER TO CHANGE FOR H/T/M TO PREVIOUS YEAR
	, 0 AS HOUSE_NO
	, '' AS EXTENSION
	, '' AS STDIRECT 
	, '' AS STTYPE 
	, '' AS STREET_NAM 
	, '' AS UNIT 
	, '' AS QUAD 
	, '' AS UNITTYPE
	, '' AS PCY 
	, '' AS CCY 
	, '' AS HCY 
	, '' AS FLCY 
	, '' AS EZY 
	, '' AS EZ_EXEMPT 
	, 0 AS HISTVAL 
	, 0 AS ORIGHOMVAL 
	, 0 AS CURRHOMVAL 
	, 0 AS ORIGHSVAL2 
	, 0 AS CURRHSVAL2
	, 0 AS PREVHOMVAL 
	, 0 AS PREVHOMVAL2
	, (SELECT TOP 1 MSYRM FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHYEAR 
	, (SELECT TOP 1 MSFMVO FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHVALUE 
	, (SELECT TOP 1 MSMFG FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHMFG 
	, (SELECT TOP 1 MSMDL FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHMODEL 
	, (SELECT TOP 1 CONCAT(MSWID,'X',MSLEN) FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHSIZE
	, (SELECT TOP 1 MSMID FROM MHMSTR WHERE MSACT=M.MBACT AND MSDGYR=M.MBTYR ORDER BY MSACT) AS MHVIN
	, REPLACE(COMM.TXT,';',CHAR(13)+CHAR(10)) COMMENTS
	, CASE WHEN M.MBNOD IN ('O') THEN 1 ELSE 0 END AS NOD
	, 0 AS E_R
	, NULL AS ADD_DATE 
	, RIGHT('0'+RTRIM(LTRIM(M.MBDST)),2) AS TAXDISTRIC
	, 0 AS PURCHASE_PRICE
	, 0 AS DATE_PURCHASED 
	, 0 AS LEVY 
	, 0 AS HOLD 
	, 0 AS PAY_PLAN 
	, 0 AS OWNKEY 
	, '' AS BR_CASENO
	, NULL AS FIFA_DATE 
	, 0 AS FIFA 
	, 0 AS MHDECALNUM 
	, 0 AS PURCHASE_PRICE_HT
	, 0 AS YEARS_SUPPORT
	, 0 AS APPEAL100
	, 0 AS REFUND
	, 0 AS EXEMPT
	, M.MBACT AS LEGACY_ACCT
	, NULL AS HARVEST_TYPE
	, 0 AS HARVEST_QTR
	, CASE WHEN M.MBTAX = 0 THEN 1 WHEN M.MBTAX+M.MBERT=0 THEN 1 ELSE 0 END AS HS_MH
	FROM TXMBF M
	LEFT JOIN ( SELECT PDACT, SUM(PDACR) AS ACRES FROM TXPDF GROUP BY PDACT ) CS ON CS.PDACT = M.MBACT
	LEFT JOIN ( SELECT MBACT, MBFMV  AS PREV_VAL, MBTYR, MBFIL1 FROM TXMBF ) PV ON PV.MBACT=M.MBACT AND PV.MBTYR=M.MBTYR-1 AND PV.MBTYR=M.MBTYR-1 AND PV.MBFIL1=M.MBFIL1
	LEFT JOIN 
		(
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		UNION
		SELECT BNTYR, BNBIL, STUFF((SELECT ';'+CONCAT(BNNYY,'-',BNNMM,'-', BNNDD,':',BNTXT,'(',BNUSR,')') FROM TXBNF WHERE BNTYR=2022 AND BNBIL= P.BNBIL FOR XML PATH('')),1,1,'') AS TXT FROM TXBNF P
		) COMM ON COMM.BNBIL=M.MBBIL AND COMM.BNTYR=M.MBTYR
	WHERE M.MBTYR= @DIGYR+1 AND 
			(
				M.MBFIL1 IN ('N','M','MN') AND M.MBACT NOT IN (SELECT MBACT FROM TXMBF WHERE MBNOD='O' AND MBTYR=@DIGYR+1)
				OR
				M.MBNOD='O'
				)
	ORDER BY M.MBTYR
;


DELETE FROM DY2022.DBO.PROPERTY WHERE MOBILEKEY > 0 AND MOBILEKEY IN (SELECT MOBILEKEY FROM DY2022.DBO.PROPERTY WHERE NOD=1) AND NOD=0

 PRINT 'MH OWNERS'
INSERT INTO DY2022.[DBO].[OWNER]
           ([OWNKEY]
           ,[LASTNAME]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[ZIP]
		   ,[SITEADDID]
		   ,[FEI]
		   ,[PROPTYPE]
	)
SELECT @OWNKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS OWNKEY
	, M.MBNM1 AS LASTNAME 
	, M.MBNM2 AS ADDRESS1 
	, M.MBAD1 AS ADDRESS2 
	, M.MBAD2 AS ADDRESS3 
	, M.MBCIT AS CITY 
	, M.MBSTE AS STATE 
	, M.MBZIP + CASE WHEN M.MBZP4 <> '0' THEN M.MBZP4 ELSE '' END AS ZIP 
	, M.MBACT
	, M.MBFIL1
	, 'M'
	FROM TXMBF M
	WHERE M.MBTYR= @DIGYR+1 AND M.MBFIL1 IN ('N','M','MN') 
	;


 PRINT 'MH PROPERTY OWNKEYS'
UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2022.[dbo].[PROPERTY] PU 
INNER JOIN DY2022.DBO.OWNER O ON PU.LEGACY_ACCT=O.SITEADDID AND PU.OWNKEY=0

 PRINT 'BILLS R&P'
INSERT INTO DY2022.[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]
		   ,[ORG_ADVTAX]
		   ,[ORG_FEES]
		   ,[ORG_FEES_OTHER]
		   ,[ORG_TOTAL]
		   )

SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
	, P.PROPERTYKEY AS PROPERTYKEY
	, CASE 
			WHEN M.MBFIL1='R' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='0' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='P' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END 
			WHEN M.MBFIL1='H' THEN M.MBBIL+ @HDE_BASEBILLNO --+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='T' AND M.MBNOD='T' THEN M.MBBIL+ @TIMB_BASEBILLNO 	--+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='T' AND M.MBNOD='O' THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1 IN ('M','MN') THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
		ELSE M.MBBIL
		END
		AS BILL_NO
	, M.MBTYR AS BILL_YEAR 
	, CASE 
		WHEN M.MBACT LIKE '%R%' THEN 'R'
		WHEN M.MBACT LIKE '%P%' THEN 'P'
		WHEN M.MBACT LIKE '%CG%' THEN 'T'
		WHEN M.MBFIL1 = 'MN' THEN 'M'
		ELSE M.MBFIL1
		END
		AS BILL_TYPE 
	, 0 AS TIMBER_VALUE 
	, 0.0 AS TIMBER_ACRES 
	, 0.0 AS PROPERTY_ACRES
	, M.MBTAX + M.MBERT AS ADV_TAX 
	, M.MBINT AS INTEREST
	, M.MBP10+M.MBP5P AS PENALTY 
	, ISNULL(FIFA.DCDCC,0) AS FIFA
	, ISNULL(GED.DCDCC,0) AS GED 
	, 0 --M.MBERT AS BREACH 
	, ISNULL(TS.DCDCC,0) AS TITLE_SEARCH
	, ISNULL(MF.DCDCC,0) AS MAIL_FEES
	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
	, 0 AS PRIOR_YEARS 
	, ISNULL(ADV.DCDCC,0) AS ADVERTISING 
	, ISNULL(CCF.DCDCC,0) AS CREDIT_CARD_FEES
	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS TOTAL 
	, M.MBTAX+M.MBERT- CASE WHEN M.MBTXP > M.MBTAX+M.MBERT THEN M.MBTAX+M.MBERT ELSE M.MBTXP END AS BALANCE_DUE -- BEST BALANCE RIGHT NOW (BEFORE PAYMENTS HAVE BEEN PROCESSED
	, CONVERT(DATE,CONVERT(CHAR(4),M.MBDDY) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDM),2) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDD),2)) AS DUE_DATE 
	, 0 AS EXCESS_FUNDS
	, 0 AS OVERAGES
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, M.MBFIL1 AS COMMENTS 
	, ISNULL(CF.DCDCC,0) AS COLLECT_FEES
	, 0 AS CHANGE 
	, ROUND(M.MBTAX/2.0,2) AS INSTALL_1
	, M.MBTAX - ROUND(M.MBTAX/2.0,2) AS INSTALL_2
	, M.MBTAX AS ORG_ADVTAX 
	, 0 AS ORG_FEES
	, 0 AS ORG_FEES_OTHER
	, M.MBTAX + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS ORG_TOTAL 
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBFIL1) AS ROWID, * FROM TXMBF2) M 
INNER JOIN DY2022.DBO.PROPERTY P ON P.LEGACY_ACCT=M.MBACT AND P.PROPTYPE = M.MBFIL1
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=M.MBTYR AND CCF.DCBIL=M.MBBIL AND CCF.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=M.MBTYR AND ADV.DCBIL=M.MBBIL AND ADV.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=M.MBTYR AND MF.DCBIL=M.MBBIL AND MF.DCNOD=M.MBNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=M.MBTYR AND GED.DCBIL=M.MBBIL AND GED.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=M.MBTYR AND TS.DCBIL=M.MBBIL AND TS.DCNOD=M.MBNOD	 
WHERE (M.MBTYR = @DIGYR AND M.MBFIL1 IN ('R','P')) AND M.MBACT NOT LIKE '%BOC%'

-- PRINT 'BILLS BREACH'
--SET @BILLKEY = (SELECT MAX(BILLKEY) FROM DY2022.DBO.BILL)
--INSERT INTO DY2022.[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]
--		   )

--SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
--	, P.PROPERTYKEY AS PROPERTYKEY
--	, M.MBBIL+ 600000
--		AS BILL_NO
--	, M.MBTYR AS BILL_YEAR 
--	, 'R' AS BILL_TYPE 
--	, 0 AS TIMBER_VALUE 
--	, 0.0 AS TIMBER_ACRES 
--	, 0.0 AS PROPERTY_ACRES
--	, M.MBTAX + M.MBERT AS ADV_TAX 
--	, M.MBINT AS INTEREST
--	, M.MBP10+M.MBP5P AS PENALTY 
--	, ISNULL(FIFA.DCDCC,0) AS FIFA
--	, ISNULL(GED.DCDCC,0) AS GED 
--	, 0 --M.MBERT AS BREACH 
--	, ISNULL(TS.DCDCC,0) AS TITLE_SEARCH
--	, ISNULL(MF.DCDCC,0) AS MAIL_FEES
--	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
--	, 0 AS PRIOR_YEARS 
--	, ISNULL(ADV.DCDCC,0) AS ADVERTISING 
--	, ISNULL(CCF.DCDCC,0) AS CREDIT_CARD_FEES
--	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS TOTAL 
--	, M.MBTAX+M.MBERT- CASE WHEN M.MBTXP > M.MBTAX THEN M.MBTAX ELSE M.MBTXP END AS BALANCE_DUE -- BEST BALANCE RIGHT NOW (BEFORE PAYMENTS HAVE BEEN PROCESSED
--	, CONVERT(DATE,CONVERT(CHAR(4),M.MBDDY) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDM),2) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDD),2)) AS DUE_DATE 
--	, 0 AS EXCESS_FUNDS
--	, 0 AS OVERAGES
--	, 0 AS FEES
--	, 0 AS FEES_OTHER 
--	, M.MBFIL1 AS COMMENTS 
--	, ISNULL(CF.DCDCC,0) AS COLLECT_FEES
--	, 0 AS CHANGE 
--	, ROUND(M.MBTAX/2.0,2) AS INSTALL_1
--	, M.MBTAX - ROUND(M.MBTAX/2.0,2) AS INSTALL_2
--FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBFIL1) AS ROWID, * FROM TXMBF WHERE MBTYR=@DIGYR AND MBACT LIKE '%BOC%') M 
--INNER JOIN DY2022.DBO.PROPERTY P ON P.LEGACY_ACCT=M.MBACT AND P.PROPTYPE = M.MBFIL1
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (99) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (56,57,58,75, 60) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (50,51,52,53) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD	
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=M.MBTYR AND CCF.DCBIL=M.MBBIL AND CCF.DCNOD=M.MBNOD 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (55) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=M.MBTYR AND ADV.DCBIL=M.MBBIL AND ADV.DCNOD=M.MBNOD 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=M.MBTYR AND MF.DCBIL=M.MBBIL AND MF.DCNOD=M.MBNOD	 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (54) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=M.MBTYR AND GED.DCBIL=M.MBBIL AND GED.DCNOD=M.MBNOD 
--LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (64,65) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=M.MBTYR AND TS.DCBIL=M.MBBIL AND TS.DCNOD=M.MBNOD	 
--WHERE (M.MBTYR = @DIGYR AND M.MBACT LIKE '%BOC%')

SET @BILLKEY = (SELECT MAX(BILLKEY)+1 FROM DY2022.DBO.BILL)
 PRINT 'BILLS M'
INSERT INTO DY2022.[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]
		   ,[ORG_ADVTAX]
		   ,[ORG_FEES]
		   ,[ORG_FEES_OTHER]
		   ,[ORG_TOTAL]
		   )

SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
	, P.PROPERTYKEY AS PROPERTYKEY
	, CASE 
			WHEN M.MBFIL1='R' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='0' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='P' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END 
			WHEN M.MBFIL1='H' THEN M.MBBIL+ @HDE_BASEBILLNO --+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='T' AND M.MBNOD='T' THEN M.MBBIL+ @TIMB_BASEBILLNO 	--+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBNOD='O' THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1 IN ('N','M','MN') THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD in ('O') THEN 100000 ELSE 0 END
		ELSE M.MBBIL
		END
		AS BILL_NO
	, M.MBTYR AS BILL_YEAR 
	, CASE 
		WHEN M.MBACT LIKE '%R%' THEN 'R'
		WHEN M.MBACT LIKE '%P%' THEN 'P'
		WHEN M.MBACT LIKE '%CG%' THEN 'T'
		WHEN M.MBNOD IN ('O') THEN 'M'
		WHEN M.MBFIL1 = 'N' THEN 'M'
		WHEN M.MBFIL1 = 'MN' THEN 'M'
		ELSE M.MBFIL1
		END
		AS BILL_TYPE 
	, 0 AS TIMBER_VALUE 
	, 0.0 AS TIMBER_ACRES 
	, 0.0 AS PROPERTY_ACRES
	, M.MBTAX + M.MBERT AS ADV_TAX 
	, M.MBINT AS INTEREST
	, M.MBP10+M.MBP5P AS PENALTY 
	, ISNULL(FIFA.DCDCC,0) AS FIFA
	, ISNULL(GED.DCDCC,0) AS GED 
	, 0 --M.MBERT AS BREACH 
	, ISNULL(TS.DCDCC,0) AS TITLE_SEARCH
	, ISNULL(MF.DCDCC,0) AS MAIL_FEES
	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
	, 0 AS PRIOR_YEARS 
	, ISNULL(ADV.DCDCC,0) AS ADVERTISING 
	, ISNULL(CCF.DCDCC,0) AS CREDIT_CARD_FEES
	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS TOTAL 
	, M.MBTAX+M.MBERT- CASE WHEN M.MBTXP > M.MBTAX+M.MBERT THEN M.MBTAX+M.MBERT ELSE M.MBTXP END AS BALANCE_DUE -- BEST BALANCE RIGHT NOW (BEFORE PAYMENTS HAVE BEEN PROCESSED
	, CONVERT(DATE,CONVERT(CHAR(4),M.MBDDY) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDM),2) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDD),2)) AS DUE_DATE 
	, 0 AS EXCESS_FUNDS
	, 0 AS OVERAGES
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, M.MBFIL1 AS COMMENTS 
	, ISNULL(CF.DCDCC,0) AS COLLECT_FEES
	, 0 AS CHANGE 
	, ROUND(M.MBTAX/2.0,2) AS INSTALL_1
	, M.MBTAX - ROUND(M.MBTAX/2.0,2) AS INSTALL_2
	, M.MBTAX AS ORG_ADVTAX 
	, 0 AS ORG_FEES
	, 0 AS ORG_FEES_OTHER
	, M.MBTAX + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS ORG_TOTAL 
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBFIL1) AS ROWID, * FROM TXMBF) M 
INNER JOIN DY2022.DBO.PROPERTY P ON P.LEGACY_ACCT=M.MBACT AND P.PROPTYPE = 'M'
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=M.MBTYR AND CCF.DCBIL=M.MBBIL AND CCF.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=M.MBTYR AND ADV.DCBIL=M.MBBIL AND ADV.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=M.MBTYR AND MF.DCBIL=M.MBBIL AND MF.DCNOD=M.MBNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=M.MBTYR AND GED.DCBIL=M.MBBIL AND GED.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=M.MBTYR AND TS.DCBIL=M.MBBIL AND TS.DCNOD=M.MBNOD	 
WHERE M.MBTYR = @DIGYR+1 AND ( M.MBFIL1 IN ('M','N','MY', 'MN') OR M.MBNOD='O')

SET @BILLKEY = (SELECT MAX(BILLKEY) FROM DY2022.DBO.BILL)

 PRINT 'BILLS H/T'
INSERT INTO DY2022.[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]
		   ,[ORG_ADVTAX]
		   ,[ORG_FEES]
		   ,[ORG_FEES_OTHER]
		   ,[ORG_TOTAL]
		   )

SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
	, P.PROPERTYKEY AS PROPERTYKEY
	, CASE 
			WHEN M.MBFIL1='R' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='0' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
			WHEN M.MBFIL1='P' THEN M.MBBIL+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END 
			WHEN M.MBFIL1='H' THEN M.MBBIL+ @HDE_BASEBILLNO --+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='T' AND M.MBNOD='T' THEN M.MBBIL+ @TIMB_BASEBILLNO 	--+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='T' AND M.MBNOD='O' THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END
			WHEN M.MBFIL1='M' THEN M.MBBIL+ @MH_BASEBILLNO 	+ CASE WHEN M.MBNOD='0' THEN 100000 ELSE 0 END
		ELSE M.MBBIL
		END
		AS BILL_NO
	, M.MBTYR AS BILL_YEAR 
	, CASE 
		WHEN M.MBACT LIKE '%R%' THEN 'R'
		WHEN M.MBACT LIKE '%P%' THEN 'P'
		WHEN M.MBACT LIKE '%CG%' THEN 'T'
		WHEN M.MBNOD='O' THEN 'M'
		ELSE M.MBFIL1
		END
		AS BILL_TYPE 
	, 0 AS TIMBER_VALUE 
	, 0.0 AS TIMBER_ACRES 
	, 0.0 AS PROPERTY_ACRES
	, M.MBTAX + M.MBERT AS ADV_TAX 
	, M.MBINT AS INTEREST
	, M.MBP10+M.MBP5P AS PENALTY 
	, ISNULL(FIFA.DCDCC,0) AS FIFA
	, ISNULL(GED.DCDCC,0) AS GED 
	, 0 --M.MBERT AS BREACH 
	, ISNULL(TS.DCDCC,0) AS TITLE_SEARCH
	, ISNULL(MF.DCDCC,0) AS MAIL_FEES
	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
	, 0 AS PRIOR_YEARS 
	, ISNULL(ADV.DCDCC,0) AS ADVERTISING 
	, ISNULL(CCF.DCDCC,0) AS CREDIT_CARD_FEES
	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS TOTAL 
	, M.MBTAX+M.MBERT- CASE WHEN M.MBTXP > M.MBTAX+M.MBERT THEN M.MBTAX+M.MBERT ELSE M.MBTXP END AS BALANCE_DUE -- BEST BALANCE RIGHT NOW (BEFORE PAYMENTS HAVE BEEN PROCESSED
	, CONVERT(DATE,CONVERT(CHAR(4),M.MBDDY) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDM),2) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDD),2)) AS DUE_DATE 
	, 0 AS EXCESS_FUNDS
	, 0 AS OVERAGES
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, M.MBFIL1 AS COMMENTS 
	, ISNULL(CF.DCDCC,0) AS COLLECT_FEES
	, 0 AS CHANGE 
	, ROUND(M.MBTAX/2.0,2) AS INSTALL_1
	, M.MBTAX - ROUND(M.MBTAX/2.0,2) AS INSTALL_2
	, M.MBTAX AS ORG_ADVTAX 
	, 0 AS ORG_FEES
	, 0 AS ORG_FEES_OTHER
	, M.MBTAX + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS ORG_TOTAL 
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBFIL1) AS ROWID, * FROM TXMBF) M 
INNER JOIN DY2022.DBO.PROPERTY P ON P.LEGACY_ACCT=M.MBACT AND P.PROPTYPE = M.MBFIL1
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=M.MBTYR AND CCF.DCBIL=M.MBBIL AND CCF.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=M.MBTYR AND ADV.DCBIL=M.MBBIL AND ADV.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=M.MBTYR AND MF.DCBIL=M.MBBIL AND MF.DCNOD=M.MBNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=M.MBTYR AND GED.DCBIL=M.MBBIL AND GED.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=M.MBTYR AND TS.DCBIL=M.MBBIL AND TS.DCNOD=M.MBNOD	 
WHERE (M.MBTYR = @DIGYR AND M.MBFIL1 IN ('H','T')) AND M.MBNOD <> 'O'

SET @BILLKEY = (SELECT MAX(BILLKEY) FROM DY2022.DBO.BILL)

 PRINT 'BILLS PU'
INSERT INTO DY2022.[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]
		   ,[UTILITY]
		   ,[ORG_ADVTAX]
		   ,[ORG_FEES]
		   ,[ORG_FEES_OTHER]
		   ,[ORG_TOTAL]
		   )

SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
	, P.PROPERTYKEY AS PROPERTYKEY
	, CASE 
			WHEN M.MBFIL1='U' THEN M.MBBIL+ 700000 + CASE WHEN M.MBNOD='' THEN 0 ELSE 100000 END  
		END
		AS BILL_NO
	, M.MBTYR AS BILL_YEAR 
	, 'R' AS BILL_TYPE 
	, 0 AS TIMBER_VALUE 
	, 0.0 AS TIMBER_ACRES 
	, 0.0 AS PROPERTY_ACRES
	, M.MBTAX + M.MBERT AS ADV_TAX 
	, M.MBINT AS INTEREST
	, M.MBP10+M.MBP5P AS PENALTY 
	, ISNULL(FIFA.DCDCC,0) AS FIFA
	, ISNULL(GED.DCDCC,0) AS GED 
	, 0 --M.MBERT AS BREACH 
	, ISNULL(TS.DCDCC,0) AS TITLE_SEARCH
	, ISNULL(MF.DCDCC,0) AS MAIL_FEES
	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
	, 0 AS PRIOR_YEARS 
	, ISNULL(ADV.DCDCC,0) AS ADVERTISING 
	, ISNULL(CCF.DCDCC,0) AS CREDIT_CARD_FEES
	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS TOTAL 
	, M.MBTAX+M.MBERT- CASE WHEN M.MBTXP > M.MBTAX+M.MBERT THEN M.MBTAX+M.MBERT ELSE M.MBTXP END AS BALANCE_DUE -- BEST BALANCE RIGHT NOW (BEFORE PAYMENTS HAVE BEEN PROCESSED
	, CONVERT(DATE,CONVERT(CHAR(4),M.MBDDY) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDM),2) + RIGHT('0'+CONVERT(VARCHAR(2),M.MBDDD),2)) AS DUE_DATE 
	, 0 AS EXCESS_FUNDS
	, 0 AS OVERAGES
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, M.MBFIL1 AS COMMENTS 
	, ISNULL(CF.DCDCC,0) AS COLLECT_FEES
	, 0 AS CHANGE 
	, ROUND(M.MBTAX/2.0,2) AS INSTALL_1
	, M.MBTAX - ROUND(M.MBTAX/2.0,2) AS INSTALL_2
	, 1 AS UTILITY
	, M.MBTAX AS ORG_ADVTAX 
	, 0 AS ORG_FEES
	, 0 AS ORG_FEES_OTHER
	, M.MBTAX + M.MBP5P + M.MBP10 + M.MBINT + ISNULL(FIFA.DCDCC,0)+ISNULL(ADM.DCDCC,0)+ISNULL(CF.DCDCC,0)+ISNULL(CCF.DCDCC,0)+ISNULL(ADV.DCDCC,0)+ISNULL(MF.DCDCC,0)+ISNULL(GED.DCDCC,0)+ISNULL(TS.DCDCC,0) AS ORG_TOTAL 
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBFIL1) AS ROWID, * FROM TXMBF) M 
INNER JOIN DY2022.DBO.PROPERTY P ON P.LEGACY_ACCT=M.MBACT AND P.PROPTYPE = 'R' --M.MBFIL1
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=M.MBTYR AND CCF.DCBIL=M.MBBIL AND CCF.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=M.MBTYR AND ADV.DCBIL=M.MBBIL AND ADV.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=M.MBTYR AND MF.DCBIL=M.MBBIL AND MF.DCNOD=M.MBNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=M.MBTYR AND GED.DCBIL=M.MBBIL AND GED.DCNOD=M.MBNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCC) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=M.MBTYR AND TS.DCBIL=M.MBBIL AND TS.DCNOD=M.MBNOD	 
WHERE (M.MBTYR = @DIGYR AND M.MBFIL1 IN ('U'))

 PRINT 'PAYMENTS - NON MH'
INSERT INTO DY2022.[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]
		   )

SELECT ROW_NUMBER() OVER(ORDER BY B.BILLKEY) AS PAYMENTKEY
	, B.BILLKEY AS BILLKEY
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END AS ADV_TAX
	, AMINP AS INTEREST
	, AMP5P + AMP1P AS PENALTY 
	, FIFA.DCDCC AS FIFA
	, GED.DCDCC AS GED
	, 0 AS BREACH
	, TS.DCDCC AS TITLE_SEARCH
	, MF.DCDCC MAIL_FEES
	, ADM.DCDCC AS ADMINISTRATION
	, 0 AS PRIOR_YEARS
	, ADV.DCDCC AS ADVERTISING
	, CCF.DCDCC AS CREDIT_CARD_FEES
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, (SELECT TOP 1 ASPBY FROM TXASF WHERE ASTRY=T.AMTRY AND ASTRM=T.AMTRM AND ASTRD=T.AMTRD AND ASHMS=T.AMHMS) AS PAID_BY
	, CASE WHEN B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP) < 0 
		THEN 0
		ELSE B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP)
		END AS BALANCE_DUE
	, '' AS CARD_TYPE
	, '' AS AUTH_CODE
	, CONVERT(CHAR(4),AMTRY)+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRM),2))+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRD),2)) AS PAYMENTDATE
	, 0 AS CNUM
	, 'CONVERSION' AS COMPNAME
	, AMCLK AS LOGINID
	, 100 AS REGISTER
	, 0 AS EXCESS_FUNDS
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXP-AMTXD ELSE 0 END ELSE 0 END AS OVERAGES 
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, 'CONVERSION' AS COMMENTS
	, CF.DCDCC AS COLLECT_FEES
	, AMTYR AS BILL_YEAR
	, 0 AS REVERSAL
	, 0 AS CHANGE
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS PAID_AMT
	, CASE 
			--WHEN B.LEGACY_ACCT LIKE '%BOC%' THEN T.AMBIL+600000
			WHEN T.AMFIL1='R' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
			WHEN T.AMFIL1='0' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
			WHEN T.AMFIL1='P' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
			WHEN T.AMFIL1='H' THEN T.AMBIL+ @HDE_BASEBILLNO --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
			WHEN T.AMFIL1='T' AND T.AMNOD='T' THEN T.AMBIL+ @TIMB_BASEBILLNO  --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
			WHEN T.AMFIL1='T' AND T.AMNOD='O' THEN T.AMBIL+ @MH_BASEBILLNO  + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
			WHEN T.AMFIL1='M' THEN T.AMBIL+ @MH_BASEBILLNO 	 + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END   
		ELSE T.AMBIL
		END
		AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO, P.LEGACY_ACCT FROM DY2022.DBO.BILL INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON isnull(B.BILL_TYPE,'') =  CASE WHEN T.AMFIL1='0' THEN 'R' ELSE ISNULL(T.AMFIL1,'') END
	AND B.BILL_YEAR=T.AMTYR AND CASE 
									--WHEN B.LEGACY_ACCT LIKE '%BOC%' THEN T.AMBIL+600000
									WHEN T.AMFIL1='R' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='0' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='P' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='H' THEN T.AMBIL+ @HDE_BASEBILLNO --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='T' THEN T.AMBIL+ @TIMB_BASEBILLNO  --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='O' THEN T.AMBIL+ @MH_BASEBILLNO  + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='M' THEN T.AMBIL+ @MH_BASEBILLNO 	 + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
								ELSE T.AMBIL
								END =B.BILL_NO
--LEFT JOIN TXASF P ON P.ASHMS=T.AMHMS AND P.ASTRY=T.AMTYR
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=T.AMTYR AND FIFA.DCBIL=T.AMBIL AND FIFA.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=T.AMTYR AND ADM.DCBIL=T.AMBIL AND ADM.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=T.AMTYR AND CF.DCBIL=T.AMBIL AND CF.DCNOD=T.AMNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=T.AMTYR AND CCF.DCBIL=T.AMBIL AND CCF.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=T.AMTYR AND ADV.DCBIL=T.AMBIL AND ADV.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=T.AMTYR AND MF.DCBIL=T.AMBIL AND MF.DCNOD=T.AMNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=T.AMTYR AND GED.DCBIL=T.AMBIL AND GED.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=T.AMTYR AND TS.DCBIL=T.AMBIL AND TS.DCNOD=T.AMNOD	 
  WHERE t.AMTYR=@DIGYR and b.bill_type in ('R','P') and (t.ampoa='p' OR T.AMPOA='A' AND T.AMTXP < 0)

DECLARE @PAYKEY AS INT = (SELECT MAX(PAYMENTKEY) FROM DY2022.DBO.PAYMENT)

 PRINT 'PAYMENTS - MH'
INSERT INTO DY2022.[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]
		   )

SELECT @PAYKEY + ROW_NUMBER() OVER(ORDER BY B.BILLKEY) AS PAYMENTKEY
	, B.BILLKEY AS BILLKEY
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END AS ADV_TAX
	, AMINP AS INTEREST
	, AMP5P + AMP1P AS PENALTY 
	, FIFA.DCDCC AS FIFA
	, GED.DCDCC AS GED
	, 0 AS BREACH
	, TS.DCDCC AS TITLE_SEARCH
	, MF.DCDCC AS MAIL_FEES
	, AMCSP AS ADMINISTRATION
	, 0 AS PRIOR_YEARS
	, ADV.DCDCC AS ADVERTISING
	, CCF.DCDCC AS CREDIT_CARD_FEES
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, (SELECT TOP 1 ASPBY FROM TXASF WHERE ASTRY=T.AMTRY AND ASTRM=T.AMTRM AND ASTRD=T.AMTRD AND ASHMS=T.AMHMS) AS PAID_BY
	, CASE WHEN B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP) < 0 
		THEN 0
		ELSE B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP)
		END AS BALANCE_DUE
	, '' AS CARD_TYPE
	, '' AS AUTH_CODE
	, CONVERT(CHAR(4),AMTRY)+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRM),2))+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRD),2)) AS PAYMENTDATE
	, 0 AS CNUM
	, 'CONVERSION' AS COMPNAME
	, AMCLK AS LOGINID
	, 100 AS REGISTER
	, 0 AS EXCESS_FUNDS
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXP-AMTXD ELSE 0 END ELSE 0 END AS OVERAGES 
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, 'CONVERSION' AS COMMENTS
	, CF.DCDCC AS COLLECT_FEES
	, AMTYR AS BILL_YEAR
	, 0 AS REVERSAL
	, 0 AS CHANGE
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS PAID_AMT
	, b.BILL_NO AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO FROM DY2022.DBO.BILL INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON LEFT(isnull(B.COMMENTS,''),1) =  isnull(T.AMFIL1,'') 
	AND B.BILL_YEAR=T.AMTYR AND CASE 
									WHEN T.AMFIL1='R' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='0' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='P' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='H' THEN T.AMBIL+ @HDE_BASEBILLNO --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='T' THEN T.AMBIL+ @TIMB_BASEBILLNO  --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='O' THEN T.AMBIL+ @MH_BASEBILLNO  + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1 IN ('M','MN') THEN T.AMBIL+ @MH_BASEBILLNO + CASE WHEN T.AMNOD='O' THEN 100000 ELSE 0 END  
								ELSE T.AMBIL
								END =B.BILL_NO
--LEFT JOIN TXASF P ON P.ASHMS=T.AMHMS AND P.ASTRY=T.AMTYR
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (01) GROUP BY DCTYR, DCBIL, DCNOD) FIFA ON FIFA.DCTYR=T.AMTYR AND FIFA.DCBIL=T.AMBIL AND FIFA.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (09) GROUP BY DCTYR, DCBIL, DCNOD) ADM ON ADM.DCTYR=T.AMTYR AND ADM.DCBIL=T.AMBIL AND ADM.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (05,08) GROUP BY DCTYR, DCBIL, DCNOD) CF ON CF.DCTYR=T.AMTYR AND CF.DCBIL=T.AMBIL AND CF.DCNOD=T.AMNOD	
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (59) GROUP BY DCTYR, DCBIL, DCNOD) CCF ON CCF.DCTYR=T.AMTYR AND CCF.DCBIL=T.AMBIL AND CCF.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (04) GROUP BY DCTYR, DCBIL, DCNOD) ADV ON ADV.DCTYR=T.AMTYR AND ADV.DCBIL=T.AMBIL AND ADV.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (97) GROUP BY DCTYR, DCBIL, DCNOD) MF ON MF.DCTYR=T.AMTYR AND MF.DCBIL=T.AMBIL AND MF.DCNOD=T.AMNOD	 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (02,03,06,07) GROUP BY DCTYR, DCBIL, DCNOD) GED ON GED.DCTYR=T.AMTYR AND GED.DCBIL=T.AMBIL AND GED.DCNOD=T.AMNOD 
LEFT JOIN (SELECT DCTYR, DCBIL, DCNOD, SUM(DCDCP) DCDCC FROM TXDCF WHERE DCCTC IN (10) GROUP BY DCTYR, DCBIL, DCNOD) TS ON TS.DCTYR=T.AMTYR AND TS.DCBIL=T.AMBIL AND TS.DCNOD=T.AMNOD	 
  WHERE t.AMTYR=@DIGYR and b.bill_type in ('M') and (t.ampoa='p' OR T.AMPOA='A' AND T.AMTXP < 0)

SET @PAYKEY = (SELECT MAX(PAYMENTKEY) FROM DY2022.DBO.PAYMENT)


 PRINT 'PAYMENTS - HDE'
INSERT INTO DY2022.[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]
		   )

SELECT @PAYKEY + ROW_NUMBER() OVER(ORDER BY B.BILLKEY) AS PAYMENTKEY
	, B.BILLKEY AS BILLKEY
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END AS ADV_TAX
	, AMINP AS INTEREST
	, AMP5P + AMP1P AS PENALTY 
	, 0 AS FIFA
	, 0 AS GED
	, 0 AS BREACH
	, 0 AS TITLE_SEARCH
	, 0 AS MAIL_FEES
	, AMCSP AS ADMINISTRATION
	, 0 AS PRIOR_YEARS
	, 0 AS ADVERTISING
	, 0 AS CREDIT_CARD_FEES
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, '' AS PAID_BY
	, CASE WHEN B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP) < 0 
		THEN 0
		ELSE B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP)
		END AS BALANCE_DUE
	, '' AS CARD_TYPE
	, '' AS AUTH_CODE
	, CONVERT(CHAR(4),AMTRY)+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRM),2))+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRD),2)) AS PAYMENTDATE
	, 0 AS CNUM
	, 'CONVERSION' AS COMPNAME
	, AMCLK AS LOGINID
	, 100 AS REGISTER
	, 0 AS EXCESS_FUNDS
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXP-AMTXD ELSE 0 END ELSE 0 END AS OVERAGES 
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, 'CONVERSION' AS COMMENTS
	, 0 AS COLLECT_FEES
	, AMTYR AS BILL_YEAR
	, 0 AS REVERSAL
	, 0 AS CHANGE
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS PAID_AMT
	, B.BILL_NO AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO FROM DY2022.DBO.BILL INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON isnull(B.COMMENTS,'') =  isnull(T.AMFIL1,'') 
	AND B.BILL_YEAR=T.AMTYR AND CASE 
									WHEN T.AMFIL1='R' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='0' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='P' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='H' THEN T.AMBIL+ @HDE_BASEBILLNO --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='T' THEN T.AMBIL+ @TIMB_BASEBILLNO  --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='T' AND T.AMNOD='O' THEN T.AMBIL+ @MH_BASEBILLNO  + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
									WHEN T.AMFIL1='M' THEN T.AMBIL+ @MH_BASEBILLNO 	 + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END  
								ELSE T.AMBIL
								END =B.BILL_NO
  WHERE t.AMTYR=@DIGYR and b.bill_type in ('H') and (t.ampoa='p' OR T.AMPOA='A' AND T.AMTXP < 0)

SET @PAYKEY = (SELECT MAX(PAYMENTKEY) FROM DY2022.DBO.PAYMENT)

 PRINT 'PAYMENTS - PU'
INSERT INTO DY2022.[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]
		   )

SELECT @PAYKEY + ROW_NUMBER() OVER(ORDER BY B.BILLKEY) AS PAYMENTKEY
	, B.BILLKEY AS BILLKEY
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END AS ADV_TAX
	, AMINP AS INTEREST
	, AMP5P + AMP1P AS PENALTY 
	, 0 AS FIFA
	, 0 AS GED
	, 0 AS BREACH
	, 0 AS TITLE_SEARCH
	, 0 AS MAIL_FEES
	, AMCSP AS ADMINISTRATION
	, 0 AS PRIOR_YEARS
	, 0 AS ADVERTISING
	, 0 AS CREDIT_CARD_FEES
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, '' AS PAID_BY
	, CASE WHEN B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP) < 0 
		THEN 0
		ELSE B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP)
		END AS BALANCE_DUE
	, '' AS CARD_TYPE
	, '' AS AUTH_CODE
	, CONVERT(CHAR(4),AMTRY)+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRM),2))+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRD),2)) AS PAYMENTDATE
	, 0 AS CNUM
	, 'CONVERSION' AS COMPNAME
	, AMCLK AS LOGINID
	, 100 AS REGISTER
	, 0 AS EXCESS_FUNDS
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXP-AMTXD ELSE 0 END ELSE 0 END AS OVERAGES 
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, 'CONVERSION' AS COMMENTS
	, 0 AS COLLECT_FEES
	, AMTYR AS BILL_YEAR
	, 0 AS REVERSAL
	, 0 AS CHANGE
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS PAID_AMT
	, B.BILL_NO AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO FROM DY2022.DBO.BILL INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON isnull(B.COMMENTS,'') =  isnull(T.AMFIL1,'') 
	AND B.BILL_YEAR=T.AMTYR AND CASE 
									WHEN T.AMFIL1='U' THEN T.AMBIL+ 700000 + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
								END =B.BILL_NO
  WHERE t.AMTYR=@DIGYR and b.bill_type in ('R') and (t.ampoa='p' OR T.AMPOA='A' AND T.AMTXP < 0)

SET @PAYKEY = (SELECT MAX(PAYMENTKEY) FROM DY2022.DBO.PAYMENT)

 PRINT 'PAYMENTS - TIMBER'
INSERT INTO DY2022.[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]
		   )

SELECT @PAYKEY + ROW_NUMBER() OVER(ORDER BY B.BILLKEY) AS PAYMENTKEY
	, B.BILLKEY AS BILLKEY
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END AS ADV_TAX
	, AMINP AS INTEREST
	, AMP5P + AMP1P AS PENALTY 
	, 0 AS FIFA
	, 0 AS GED
	, 0 AS BREACH
	, 0 AS TITLE_SEARCH
	, 0 AS MAIL_FEES
	, AMCSP AS ADMINISTRATION
	, 0 AS PRIOR_YEARS
	, 0 AS ADVERTISING
	, 0 AS CREDIT_CARD_FEES
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, '' AS PAID_BY
	, CASE WHEN B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP) < 0 
		THEN 0
		ELSE B.TOTAL - (CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXD ELSE AMTXP END ELSE AMTXP END +AMINP+AMP5P+AMP1P+AMCSP)
		END AS BALANCE_DUE
	, '' AS CARD_TYPE
	, '' AS AUTH_CODE
	, CONVERT(CHAR(4),AMTRY)+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRM),2))+CONVERT(CHAR(2),RIGHT('0'+LTRIM(AMTRD),2)) AS PAYMENTDATE
	, 0 AS CNUM
	, 'CONVERSION' AS COMPNAME
	, AMCLK AS LOGINID
	, 100 AS REGISTER
	, 0 AS EXCESS_FUNDS
	, CASE WHEN AMPOA='P' THEN CASE WHEN AMTXP > AMTXD THEN AMTXP-AMTXD ELSE 0 END ELSE 0 END AS OVERAGES 
	, 0 AS FEES
	, 0 AS FEES_OTHER 
	, 'CONVERSION' AS COMMENTS
	, 0 AS COLLECT_FEES
	, AMTYR AS BILL_YEAR
	, 0 AS REVERSAL
	, 0 AS CHANGE
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS PAID_AMT
	, B.BILL_NO AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO FROM DY2022.DBO.BILL INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON isnull(B.COMMENTS,'') =  isnull(T.AMFIL1,'') 
	AND B.BILL_YEAR=T.AMTYR AND CASE 
									WHEN T.AMFIL1='R' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='0' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='P' THEN T.AMBIL+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='H' THEN T.AMBIL+ @HDE_BASEBILLNO --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='T' AND T.AMNOD='T' THEN T.AMBIL+ @TIMB_BASEBILLNO  --+ CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='T' AND T.AMNOD='O' THEN T.AMBIL+ @MH_BASEBILLNO  + CASE WHEN T.AMNOD='' THEN 0 ELSE 100000 END 
									WHEN T.AMFIL1='M' THEN T.AMBIL+ @MH_BASEBILLNO 	 + CASE WHEN T.AMNOD='0' THEN 100000 ELSE 0 END 
								ELSE T.AMBIL
								END =B.BILL_NO
  WHERE t.AMTYR=@DIGYR and b.bill_type in ('T') and (t.ampoa='p' OR T.AMPOA='A' AND T.AMTXP < 0)

 PRINT 'D_PERS'
INSERT INTO DY2022.[dbo].D_PERS (PERSKEY, OWNKEY, PARCEL_NO)
SELECT P.PERSKEY
	, O.OWNKEY
	, P.PARCEL_NO
FROM DY2022.[dbo].PROPERTY P 
INNER JOIN DY2022.[dbo].OWNER O ON O.SITEADDID=P.LEGACY_ACCT
WHERE P.PERSKEY>0

 PRINT 'D_REAL'
INSERT INTO DY2022.[dbo].D_REAL (REALKEY, OWNKEY, PARCEL_NO)
SELECT P.REALKEY
	, O.OWNKEY
	, P.PARCEL_NO
FROM DY2022.[dbo].PROPERTY P 
INNER JOIN DY2022.[dbo].OWNER O ON O.SITEADDID=P.LEGACY_ACCT
WHERE P.REALKEY>0

 PRINT 'M_MOBILE'
INSERT INTO DY2022.[dbo].M_MOBILE (MOBILEKEY, OWNKEY, PREBMAPID, MOBTYPE, YEARBUILT, WIDTH, LENGTH, MFG, MODEL, SERIALNUM)
SELECT P.MOBILEKEY
	, O.OWNKEY
	, PARCEL_NO
	, 3 MOBTYPE
	, MHYEAR
	, REPLACE(LEFT(MHSIZE,2),'X','')
	, RIGHT(MHSIZE,2)
	, MHMFG
	, MHMODEL
	, MHVIN
FROM DY2022.[dbo].PROPERTY P 
INNER JOIN DY2022.[dbo].OWNER O ON O.SITEADDID=P.LEGACY_ACCT
WHERE P.MOBILEKEY>0



 PRINT 'TAXDETAIL - NON-MH'
 ;

WITH GC AS (
SELECT '01' AS TAXTYPE, 'STATE' AS DESCRIP UNION
SELECT '02' AS TAXTYPE, 'COUNTY M & O' AS DESCRIP UNION
SELECT '03' AS TAXTYPE, 'SCHOOL M & O' AS DESCRIP UNION
SELECT '04' AS TAXTYPE, 'SCHOOL M & O' AS DESCRIP UNION
SELECT '05' AS TAXTYPE, 'SCHOOL BOND' AS DESCRIP UNION
SELECT '06' AS TAXTYPE, 'WASHINGTON' AS DESCRIP UNION
SELECT '07' AS TAXTYPE, 'TIGNALL' AS DESCRIP UNION
SELECT '08' AS TAXTYPE, 'FIXED ASSESSMENT' AS DESCRIP UNION
SELECT '09' AS TAXTYPE, 'FIRE DIST' AS DESCRIP UNION
SELECT '10' AS TAXTYPE, 'SCHOOL BOND' AS DESCRIP UNION
SELECT '11' AS TAXTYPE, 'IRONGATE' AS DESCRIP UNION
SELECT '12' AS TAXTYPE, 'GROVE LAKE' AS DESCRIP UNION
SELECT '13' AS TAXTYPE, 'TURKEY TRL' AS DESCRIP UNION
SELECT '14' AS TAXTYPE, 'PRETORIA' AS DESCRIP UNION
SELECT '15' AS TAXTYPE, 'WESTOVER' AS DESCRIP UNION
SELECT '16' AS TAXTYPE, 'TMBR RIDGE' AS DESCRIP UNION
SELECT '17' AS TAXTYPE, 'SADDLE CRK' AS DESCRIP UNION
SELECT '18' AS TAXTYPE, 'PLANTATION' AS DESCRIP UNION
SELECT '19' AS TAXTYPE, 'CYPRESS CR' AS DESCRIP UNION
SELECT '20' AS TAXTYPE, 'RURAL FIRE' AS DESCRIP UNION
SELECT '21' AS TAXTYPE, 'STONEBROOK' AS DESCRIP UNION
SELECT '22' AS TAXTYPE, 'REGISTER' AS DESCRIP UNION
SELECT '23' AS TAXTYPE, 'HAMMOCKS' AS DESCRIP UNION
SELECT '24' AS TAXTYPE, 'COTTAGES' AS DESCRIP UNION
SELECT '25' AS TAXTYPE, 'AMBERWOOD' AS DESCRIP UNION
SELECT '26' AS TAXTYPE, 'WESTRIDGE' AS DESCRIP UNION
SELECT '27' AS TAXTYPE, 'PINENEEDLE' AS DESCRIP UNION
SELECT '28' AS TAXTYPE, 'PAVING FEE' AS DESCRIP UNION
SELECT '30' AS TAXTYPE, 'BIRDIE CT' AS DESCRIP UNION
SELECT '31' AS TAXTYPE, 'TAD7 CTYMO' AS DESCRIP UNION
SELECT '32' AS TAXTYPE, 'TAD7 SCHMO' AS DESCRIP UNION
SELECT '33' AS TAXTYPE, 'TAD7 SCHBD' AS DESCRIP UNION
SELECT '34' AS TAXTYPE, 'TAD8 CTYMO' AS DESCRIP UNION
SELECT '35' AS TAXTYPE, 'TAD8 SCHMO' AS DESCRIP UNION
SELECT '36' AS TAXTYPE, 'TAD8 SCHBD' AS DESCRIP UNION
SELECT '37' AS TAXTYPE, 'WETHRSTONE' AS DESCRIP UNION
SELECT '38' AS TAXTYPE, 'WETHERSTO2' AS DESCRIP UNION
SELECT '40' AS TAXTYPE, 'COM FIREFE' AS DESCRIP UNION
SELECT '42' AS TAXTYPE, 'FHCC 1947' AS DESCRIP UNION
SELECT '47' AS TAXTYPE, 'TAD9 CTYMO' AS DESCRIP UNION
SELECT '48' AS TAXTYPE, 'TAD9 SCHMO' AS DESCRIP UNION
SELECT '49' AS TAXTYPE, 'TAD9 SCHBN' AS DESCRIP UNION
SELECT '50' AS TAXTYPE, 'CHATHAM PL' AS DESCRIP UNION
SELECT '51' AS TAXTYPE, 'RIVER BLUF' AS DESCRIP UNION
SELECT '52' AS TAXTYPE, 'COLFAX FD' AS DESCRIP UNION
SELECT '53' AS TAXTYPE, 'RIVER BLU2' AS DESCRIP UNION
SELECT '54' AS TAXTYPE, 'RIVER BLU3' AS DESCRIP UNION
SELECT '55' AS TAXTYPE, 'CHATHAM 29' AS DESCRIP UNION
SELECT '56' AS TAXTYPE, 'FRAZIER FD' AS DESCRIP UNION
SELECT '57' AS TAXTYPE, 'CHATHAM 28' AS DESCRIP UNION
SELECT '58' AS TAXTYPE, 'SUNFIELD' AS DESCRIP UNION
SELECT '45' AS TAXTYPE, 'FIRE FEE' AS DESCRIP

)

INSERT INTO DY2022.DBO.TAXDETAIL (REALKEY, PERSKEY, TAXTYPE, TT_DESC, ASMTPCT, COVEXMPT, HSEXEMPT, MILLAGE, ESTTAX, PROPERTYKEY, ACCKEY)
SELECT B.REALKEY, B.PERSKEY, T.DBTTC, GC.DESCRIP, .4, 0, 0, 0, T.DBDTB, B.PROPERTYKEY, 0 
FROM DBO.TXDBF T
INNER JOIN GC ON GC.TAXTYPE=T.DBTTC
  INNER JOIN 
	(
		SELECT B.*, TM.MBTYR, TM.MBACT, TM.MBBIL, P.REALKEY, P.PERSKEY  
		FROM DY2022.DBO.BILL B 
		INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.PROPTYPE!='M' 
		INNER JOIN TXMBF TM ON TM.MBACT=P.LEGACY_ACCT AND TM.MBFIL1 <> 'M'
	) B ON B.MBTYR=T.DBTYR AND CASE 
									WHEN T.DBNOD=' ' THEN T.DBBIL 
									WHEN T.DBNOD='N' THEN T.DBBIL + 100000
									WHEN T.DBNOD='P' THEN T.DBBIL + 100000
									WHEN T.DBNOD='H' THEN T.DBBIL+ @HDE_BASEBILLNO --+ 100000
									WHEN T.DBNOD='T' THEN T.DBBIL+ @TIMB_BASEBILLNO --+ 100000 
									WHEN T.DBNOD='M' THEN T.DBBIL+ @MH_BASEBILLNO + 100000 
									WHEN T.DBNOD='O' THEN T.DBBIL+ @MH_BASEBILLNO + 100000 
								ELSE T.DBBIL
								END=B.BILL_NO
							AND CASE WHEN B.BILL_TYPE IN ('R','P') THEN ' ' ELSE B.BILL_TYPE END = T.DBNOD

WHERE DBTYR=@DIGYR 

 PRINT 'TAXDETAIL - MH';

WITH GC AS (
SELECT '01' AS TAXTYPE, 'STATE' AS DESCRIP UNION
SELECT '02' AS TAXTYPE, 'COUNTY M & O' AS DESCRIP UNION
SELECT '03' AS TAXTYPE, 'SCHOOL M&O' AS DESCRIP UNION
SELECT '04' AS TAXTYPE, 'SCHOOL M & O' AS DESCRIP UNION
SELECT '05' AS TAXTYPE, 'SCHOOL BOND' AS DESCRIP UNION
SELECT '06' AS TAXTYPE, 'WASHINGTON' AS DESCRIP UNION
SELECT '07' AS TAXTYPE, 'TIGNALL' AS DESCRIP UNION
SELECT '08' AS TAXTYPE, 'FIXED ASSESSMENT' AS DESCRIP UNION
SELECT '09' AS TAXTYPE, 'FIRE DIST' AS DESCRIP UNION
SELECT '10' AS TAXTYPE, 'SCHOOL BOND' AS DESCRIP UNION
SELECT '11' AS TAXTYPE, 'IRONGATE' AS DESCRIP UNION
SELECT '12' AS TAXTYPE, 'GROVE LAKE' AS DESCRIP UNION
SELECT '13' AS TAXTYPE, 'TURKEY TRL' AS DESCRIP UNION
SELECT '14' AS TAXTYPE, 'PRETORIA' AS DESCRIP UNION
SELECT '15' AS TAXTYPE, 'WESTOVER' AS DESCRIP UNION
SELECT '16' AS TAXTYPE, 'TMBR RIDGE' AS DESCRIP UNION
SELECT '17' AS TAXTYPE, 'SADDLE CRK' AS DESCRIP UNION
SELECT '18' AS TAXTYPE, 'PLANTATION' AS DESCRIP UNION
SELECT '19' AS TAXTYPE, 'CYPRESS CR' AS DESCRIP UNION
SELECT '20' AS TAXTYPE, 'RURAL FIRE' AS DESCRIP UNION
SELECT '21' AS TAXTYPE, 'STONEBROOK' AS DESCRIP UNION
SELECT '22' AS TAXTYPE, 'REGISTER' AS DESCRIP UNION
SELECT '23' AS TAXTYPE, 'HAMMOCKS' AS DESCRIP UNION
SELECT '24' AS TAXTYPE, 'COTTAGES' AS DESCRIP UNION
SELECT '25' AS TAXTYPE, 'AMBERWOOD' AS DESCRIP UNION
SELECT '26' AS TAXTYPE, 'WESTRIDGE' AS DESCRIP UNION
SELECT '27' AS TAXTYPE, 'PINENEEDLE' AS DESCRIP UNION
SELECT '28' AS TAXTYPE, 'PAVING FEE' AS DESCRIP UNION
SELECT '30' AS TAXTYPE, 'BIRDIE CT' AS DESCRIP UNION
SELECT '31' AS TAXTYPE, 'TAD7 CTYMO' AS DESCRIP UNION
SELECT '32' AS TAXTYPE, 'TAD7 SCHMO' AS DESCRIP UNION
SELECT '33' AS TAXTYPE, 'TAD7 SCHBD' AS DESCRIP UNION
SELECT '34' AS TAXTYPE, 'TAD8 CTYMO' AS DESCRIP UNION
SELECT '35' AS TAXTYPE, 'TAD8 SCHMO' AS DESCRIP UNION
SELECT '36' AS TAXTYPE, 'TAD8 SCHBD' AS DESCRIP UNION
SELECT '37' AS TAXTYPE, 'WETHRSTONE' AS DESCRIP UNION
SELECT '38' AS TAXTYPE, 'WETHERSTO2' AS DESCRIP UNION
SELECT '40' AS TAXTYPE, 'COM FIREFE' AS DESCRIP UNION
SELECT '42' AS TAXTYPE, 'FHCC 1947' AS DESCRIP UNION
SELECT '47' AS TAXTYPE, 'TAD9 CTYMO' AS DESCRIP UNION
SELECT '48' AS TAXTYPE, 'TAD9 SCHMO' AS DESCRIP UNION
SELECT '49' AS TAXTYPE, 'TAD9 SCHBN' AS DESCRIP UNION
SELECT '50' AS TAXTYPE, 'CHATHAM PL' AS DESCRIP UNION
SELECT '51' AS TAXTYPE, 'RIVER BLUF' AS DESCRIP UNION
SELECT '52' AS TAXTYPE, 'COLFAX FD' AS DESCRIP UNION
SELECT '53' AS TAXTYPE, 'RIVER BLU2' AS DESCRIP UNION
SELECT '54' AS TAXTYPE, 'RIVER BLU3' AS DESCRIP UNION
SELECT '55' AS TAXTYPE, 'CHATHAM 29' AS DESCRIP UNION
SELECT '56' AS TAXTYPE, 'FRAZIER FD' AS DESCRIP UNION
SELECT '57' AS TAXTYPE, 'CHATHAM 28' AS DESCRIP UNION
SELECT '58' AS TAXTYPE, 'SUNFIELD' AS DESCRIP UNION
SELECT '45' AS TAXTYPE, 'FIRE FEE' AS DESCRIP

)

INSERT INTO DY2022.DBO.TAXDETAIL (REALKEY, PERSKEY, TAXTYPE, TT_DESC, ASMTPCT, COVEXMPT, HSEXEMPT, MILLAGE, ESTTAX, PROPERTYKEY, ACCKEY)
SELECT B.REALKEY, B.PERSKEY, T.DBTTC, GC.DESCRIP, .4, 0, 0, 0, T.DBDTB, B.PROPERTYKEY, 0 
FROM DBO.TXDBF T
INNER JOIN GC ON GC.TAXTYPE=T.DBTTC
  INNER JOIN 
	(
		SELECT B.*, TM.MBTYR, TM.MBACT, TM.MBBIL, P.REALKEY, P.PERSKEY  
		FROM DY2022.DBO.BILL B 
		INNER JOIN DY2022.DBO.PROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.PROPTYPE='M' 
		INNER JOIN TXMBF TM ON TM.MBACT=P.LEGACY_ACCT AND TM.MBFIL1 IN ('N','M','MY') AND TM.MBTYR=B.BILL_YEAR --AND B.PROPERTYKEY=33084
	) B ON B.MBTYR=T.DBTYR AND CASE 
									WHEN T.DBNOD IN ('O') THEN T.DBBIL+ @MH_BASEBILLNO + 100000 
								ELSE T.DBBIL+ @MH_BASEBILLNO
								END=B.BILL_NO AND T.DBNOD=CASE WHEN B.BILL_NO > 350000 THEN 'O' ELSE 'M' END

WHERE DBTYR=@DIGYR+1 

 PRINT 'TAXMASTER'
INSERT INTO DY2022.DBO.TAXMASTER (REALKEY,PERSKEY, MILLAGE, ESTTAX, PROPERTYKEY)
SELECT REALKEY, PERSKEY, SUM(MILLAGE), SUM(ESTTAX), PROPERTYKEY FROM DY2022.DBO.TAXDETAIL GROUP BY REALKEY, PERSKEY, PROPERTYKEY 

update DY2022.DBO.property set e_r=1 where propertykey in (select propertykey from DY2022.DBO.bill where billkey in (select billkey from DY2022.DBO.payment where PAYMENT_TYPE='adjustment' and ADV_TAX<0)) 

INSERT INTO DY2022.DBO.BILL_TRANSACTIONS (BILL_NO, T_AMOUNT, T_DATE, T_TYPE , COMMENTS , LOGINID, COMPNAME, BILL_YEAR)
SELECT BILL_NO, ADV_TAX, PAYMENTDATE, 'ADJUSTMENT', 'E&R ADJUSTMENT - CONVERSION', 'SYSGEN', HOST_NAME(), BILL_YEAR FROM DY2022.DBO.PAYMENT WHERE PAYMENT_TYPE='ADJUSTMENT' AND ADV_TAX<0

delete from DY2022.DBO.payment where PAYMENT_TYPE='adjustment' and ADV_TAX<0
update DY2022.DBO.bill set ADV_TAX = BREACH + ADV_TAX, BREACH=0 WHERE BREACH < 0
--update b set b.ADMINISTRATION = x.administration_amt
--from DY2022.DBO.bill b 
--inner join 
--	(
--	select bill_no, bill_year, sum(administration) as administration_amt 
--	from DY2022.DBO.payment 
--	group by bill_no, bill_year 
--	having sum(administration) > 0
--	) x on x.BILL_NO = b.BILL_NO and x.BILL_YEAR = b.BILL_YEAR 
--	and b.ADMINISTRATION <> x.administration_amt

INSERT INTO DY2022.DBO.PROPERTY_ITEMS (ITEMKEY, PROPERTYKEY, DIGCLASS, DIGSTRAT, ACRES, ITEMVALUE, VALUE40RAW)
SELECT ROW_NUMBER() OVER( ORDER BY PDACT ) AS ITEMKEY 
	, P.PROPERTYKEY
	, LEFT(CASE
				when 1=1 then PDCSC 
				WHEN PDCSC = '-97' THEN 'IP'
				WHEN PDCSC = '-37' THEN 'CP'
				WHEN PDCSC = '0' THEN 'SF'
				WHEN PDCSC = '11' THEN 'A1'
				WHEN PDCSC = '13' THEN 'A3'
				WHEN PDCSC = '14' THEN 'A4'
				WHEN PDCSC = '15' THEN 'A5'
				WHEN PDCSC = '16' THEN 'A6'
				WHEN PDCSC = '31' THEN 'C1'
				WHEN PDCSC = '33' THEN 'C3'
				WHEN PDCSC = '34' THEN 'C4'
				WHEN PDCSC = '35' THEN 'C5'
				WHEN PDCSC = '36' THEN 'CF'
				WHEN PDCSC = '39' THEN 'CI'
				WHEN PDCSC = '41' THEN 'U1'
				WHEN PDCSC = '42' THEN 'U2'
				WHEN PDCSC = '43' THEN 'U3'
				WHEN PDCSC = '44' THEN 'U4'
				WHEN PDCSC = '45' THEN 'U5'
				WHEN PDCSC = '50' THEN 'E0'
				WHEN PDCSC = '51' THEN 'E1'
				WHEN PDCSC = '52' THEN 'E2'
				WHEN PDCSC = '53' THEN 'V3'
				WHEN PDCSC = '54' THEN 'V4'
				WHEN PDCSC = '55' THEN 'V5'
				WHEN PDCSC = '56' THEN 'V6'
				WHEN PDCSC = '59' THEN 'E9'
				WHEN PDCSC = '64' THEN 'F4'
				WHEN PDCSC = '65' THEN 'J5'
				WHEN PDCSC = '91' THEN 'R1'
				WHEN PDCSC = '92' THEN 'RB'
				WHEN PDCSC = '93' THEN 'R3'
				WHEN PDCSC = '94' THEN 'R4'
				WHEN PDCSC = '95' THEN 'R5'
				WHEN PDCSC = '96' THEN 'IF'
				WHEN PDCSC = '99' THEN 'II'
				END 
		,1) AS DIGCLASS
	, RIGHT(CASE 
				when 1=1 then PDCSC
				WHEN PDCSC = '-97' THEN 'IP'
				WHEN PDCSC = '-37' THEN 'CP'
				WHEN PDCSC = '0' THEN 'SF'
				WHEN PDCSC = '11' THEN 'A1'
				WHEN PDCSC = '13' THEN 'A3'
				WHEN PDCSC = '14' THEN 'A4'
				WHEN PDCSC = '15' THEN 'A5'
				WHEN PDCSC = '16' THEN 'A6'
				WHEN PDCSC = '31' THEN 'C1'
				WHEN PDCSC = '33' THEN 'C3'
				WHEN PDCSC = '34' THEN 'C4'
				WHEN PDCSC = '35' THEN 'C5'
				WHEN PDCSC = '36' THEN 'CF'
				WHEN PDCSC = '39' THEN 'CI'
				WHEN PDCSC = '41' THEN 'U1'
				WHEN PDCSC = '42' THEN 'U2'
				WHEN PDCSC = '43' THEN 'U3'
				WHEN PDCSC = '44' THEN 'U4'
				WHEN PDCSC = '45' THEN 'U5'
				WHEN PDCSC = '50' THEN 'E0'
				WHEN PDCSC = '51' THEN 'E1'
				WHEN PDCSC = '52' THEN 'E2'
				WHEN PDCSC = '53' THEN 'V3'
				WHEN PDCSC = '54' THEN 'V4'
				WHEN PDCSC = '55' THEN 'V5'
				WHEN PDCSC = '56' THEN 'V6'
				WHEN PDCSC = '59' THEN 'E9'
				WHEN PDCSC = '64' THEN 'F4'
				WHEN PDCSC = '65' THEN 'J5'
				WHEN PDCSC = '91' THEN 'R1'
				WHEN PDCSC = '92' THEN 'RB'
				WHEN PDCSC = '93' THEN 'R3'
				WHEN PDCSC = '94' THEN 'R4'
				WHEN PDCSC = '95' THEN 'R5'
				WHEN PDCSC = '96' THEN 'IF'
				WHEN PDCSC = '99' THEN 'II'
				END 
		,1) AS DIGSTRAT
		, T.PDACR
		, ROUND(T.PDFMV * .4,0) AS ITEMVALUE 
		, ROUND(T.PDFMV * .4,0) AS VALUE40RAW

FROM TXPDF T
INNER JOIN DY2022.DBO.PROPERTY P ON LEGACY_ACCT=T.PDACT 
WHERE T.PDCSC NOT LIKE 'F%' AND T.PDCSC > ''

UPDATE DY2022.DBO.PROPERTY SET EXEMPT=1 WHERE PROPERTYKEY IN (SELECT PROPERTYKEY FROM DY2022.DBO.PROPERTY_ITEMS WHERE DIGCLASS='E')


UPDATE BU SET BU.FEES_OTHER=Q.ESTTAX, ADV_TAX=ADV_TAX-Q.ESTTAX 
FROM DY2022.DBO.BILL BU 
INNER JOIN (SELECT PROPERTYKEY, SUM(ESTTAX) ESTTAX FROM DY2022.DBO.TAXDETAIL WHERE TAXTYPE IN (SELECT TAXTYPE FROM DY2022.DBO.TAXFEE) GROUP BY PROPERTYKEY HAVING SUM(ESTTAX) > 0 
			) Q ON Q.PROPERTYKEY=BU.PROPERTYKEY
			AND BU.ADV_TAX>0 AND BU.ADV_TAX > Q.ESTTAX

INSERT INTO DY2022.DBO.TAXFEE_ITEMS (REALKEY, DESCRIP, FEE_AMT, MOBILEKEY)
SELECT REALKEY, TT_DESC, ESTTAX, 0 FROM DY2022.DBO.TAXDETAIL WHERE TAXTYPE IN (SELECT TAXTYPE FROM DY2022.DBO.TAXFEE) AND REALKEY=14154

insert into dy2022.dbo.TAXFEE_TYPES (DESCRIP, sw)
select TT_DESC, SW from dy2022.dbo.TAXFEE


DECLARE @SQL_MILLS AS VARCHAR(1000) 

SET @SQL_MILLS = 'UPDATE TDU SET TDU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.TAXDETAIL TDU ') +
	CONCAT('INNER JOIN DY',@DIGYR,'.DBO.MILLRATE M ON TDU.TAXTYPE= M.TAXTYPE ') + 
	CONCAT('INNER JOIN DY',@DIGYR,'.DBO.PROPERTY P ON P.PROPERTYKEY=TDU.PROPERTYKEY AND P.PROPTYPE IN (''R'',''P'')' )

PRINT @SQL_MILLS

EXEC (@SQL_MILLS)

SET @SQL_MILLS = 'UPDATE TDU SET TDU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.TAXDETAIL TDU ') +
	CONCAT('INNER JOIN DY',@DIGYR-1,'.DBO.MILLRATE M ON TDU.TAXTYPE= M.TAXTYPE ') + 
	CONCAT('INNER JOIN DY',@DIGYR,'.DBO.PROPERTY P ON P.PROPERTYKEY=TDU.PROPERTYKEY AND P.PROPTYPE NOT IN (''R'',''P'')' )

PRINT @SQL_MILLS

EXEC (@SQL_MILLS)				

UPDATE DY2022.DBO.PROPERTY SET 
	A_VALUE = (SELECT SUM(ITEMVALUE) FROM DY2022.DBO.PROPERTY_ITEMS WHERE PROPERTYKEY=PROPERTY.PROPERTYKEY AND DIGSTRAT IN ('3','4','5') AND DIGCLASS NOT IN ('V','W','T','J','P','F'))
	, P_VALUE = (SELECT SUM(ITEMVALUE) FROM DY2022.DBO.PROPERTY_ITEMS WHERE PROPERTYKEY=PROPERTY.PROPERTYKEY AND DIGSTRAT IN ('3','4','5') AND DIGCLASS IN ('V','W','T','J','P','F'))


SET @SQL_MILLS = 'UPDATE PU SET PU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.PROPERTY PU ') +
	CONCAT('INNER JOIN (SELECT TAXDISTRIC, SUM(MILLS) AS MILLS FROM DY',@DIGYR,'.DBO.MILLRATE M WHERE USE_REAL=1 GROUP BY TAXDISTRIC) M ON M.TAXDISTRIC=PU.TAXDISTRIC AND PU.PROPTYPE IN (''R'')' )

PRINT @SQL_MILLS

EXEC (@SQL_MILLS)				

SET @SQL_MILLS = 'UPDATE PU SET PU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.PROPERTY PU ') +
	CONCAT('INNER JOIN (SELECT TAXDISTRIC, SUM(MILLS) AS MILLS FROM DY',@DIGYR-1,'.DBO.MILLRATE M WHERE USE_TIMB=1 GROUP BY TAXDISTRIC) M ON M.TAXDISTRIC=PU.TAXDISTRIC AND PU.PROPTYPE IN (''T'')' )

EXEC (@SQL_MILLS)				

SET @SQL_MILLS = 'UPDATE PU SET PU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.PROPERTY PU ') +
	CONCAT('INNER JOIN (SELECT TAXDISTRIC, SUM(MILLS) AS MILLS FROM DY',@DIGYR-1,'.DBO.MILLRATE M WHERE USE_HDE=1 GROUP BY TAXDISTRIC) M ON M.TAXDISTRIC=PU.TAXDISTRIC AND PU.PROPTYPE IN (''H'')' )

EXEC (@SQL_MILLS)				

SET @SQL_MILLS = 'UPDATE PU SET PU.MILLAGE=M.MILLS ' +
	CONCAT('FROM DY',@DIGYR,'.DBO.PROPERTY PU ') +
	CONCAT('INNER JOIN (SELECT TAXDISTRIC, SUM(MILLS) AS MILLS FROM DY',@DIGYR-1,'.DBO.MILLRATE M WHERE USE_MH=1 GROUP BY TAXDISTRIC) M ON M.TAXDISTRIC=PU.TAXDISTRIC AND PU.PROPTYPE IN (''M'')' )

EXEC (@SQL_MILLS)				
