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

set @PROPKEY = (SELECT MAX(PROPERTYKEY) FROM DY2020.DBO.PROPERTY)

-- REAL
INSERT INTO DY2020.[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])

 SELECT @PROPKEY + ROW_NUMBER() OVER( ORDER BY M.MBACT ) AS PROPERTYKEY 
	, CASE 
		WHEN M.MBACT = '3   14984R' THEN '    14984'
		WHEN M.MBACT = '1R      19' THEN '    30831'
		WHEN M.MBACT = ' 10645R 19' THEN '    10645'
		WHEN M.MBACT LIKE '%R%' 
		THEN
			CASE 
			WHEN M.MBTYR = @DIGYR THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(M.MBACT,' 10645R 19','10645'),'R19',''),'R20',''),'.',''),'R',''),'P','')
			ELSE 0 
			END 
		ELSE 0
		END
		AS REALKEY 
	, 0	AS PERSKEY
	, 0 AS MOBILEKEY
	, CASE WHEN M.MBFIL1 = 'U' THEN 'R' ELSE M.MBFIL1 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 
	, 'PU' 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
	, M.MBACT AS COMMENTS 
	, CASE WHEN M.MBNOD IS NULL THEN 0 ELSE 1 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 
	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 dy2020.dbo.property p on p.COMMENTS=m.MBACT
	WHERE M.MBTYR= @DIGYR AND isnull(M.MBFIL1,'') IN ('U') AND isnull(M.MBLOC,'') NOT LIKE '%BREACH%' and mbnod is null and p.PROPERTYKEY is null 
	ORDER BY M.MBTYR


UPDATE PU SET PU.OWNKEY=O.OWNKEY 
FROM DY2020.[dbo].[PROPERTY] PU 
INNER JOIN DY2020.DBO.OWNER O ON PU.COMMENTS=O.SITEADDID AND PU.OWNKEY=0

INSERT INTO DY2020.[dbo].D_REAL (REALKEY, OWNKEY)
SELECT P.REALKEY
	, O.OWNKEY
FROM DY2020.[dbo].PROPERTY P 
INNER JOIN DY2020.[dbo].OWNER O ON O.SITEADDID=P.COMMENTS
WHERE P.REALKEY>0 AND P.REALKEY NOT IN (SELECT REALKEY FROM DY2020.DBO.D_REAL)

set @BILLKEY = (SELECT MAX(BILLKEY) FROM DY2020.DBO.BILL)

INSERT INTO DY2020.[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]
		   )

SELECT @BILLKEY + ROW_NUMBER() OVER( ORDER BY M.MBBIL ) AS BILLKEY 
	, P.PROPERTYKEY AS PROPERTYKEY
	, CASE 
		when m.MBFIL1='mn' then M.MBBIL+400000 
		WHEN M.ROWID > 1 THEN 
			CASE 
			WHEN M.MBFIL1='R' THEN M.MBBIL+100000 
			WHEN M.MBFIL1='P' THEN M.MBBIL+100000 
			WHEN M.MBFIL1='H' THEN M.MBBIL+200000 
			WHEN M.MBFIL1='T' THEN M.MBBIL+300000 
			WHEN M.MBFIL1='MN' THEN M.MBBIL+400000 
			WHEN M.MBFIL1='U' THEN M.MBBIL+500000 
			ELSE M.MBBIL 
			END 
		WHEN M.MBFIL1 <> 'R' AND M.MBBIL IN (SELECT MBBIL FROM TXMBF WHERE MBTYR=@DIGYR AND MBFIL1 IN ('R')) THEN 
			CASE 
			WHEN M.MBFIL1='R' THEN M.MBBIL+100000 
			WHEN M.MBFIL1='P' THEN M.MBBIL+100000 
			WHEN M.MBFIL1='H' THEN M.MBBIL+200000 
			WHEN M.MBFIL1='T' THEN M.MBBIL+300000 
			WHEN M.MBFIL1='MN' THEN M.MBBIL+400000 
			WHEN M.MBFIL1='U' THEN M.MBBIL+500000 
			ELSE M.MBBIL 
			END 
		ELSE M.MBBIL
		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 
	, case 
		when m.mbtyr=2020 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		when m.mbtyr=2019 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		when m.mbtyr=2018 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		when m.mbtyr=2017 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		when m.mbtyr=2016 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005626),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		when m.mbtyr=2015 then 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005417),2)*11 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005626),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
			round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
		else 0
		end
		AS INTEREST
	, M.MBP10+M.MBP5P+M.MBP05+round(((M.MBTAX+M.MBERT-M.MBTXP)*.20),2) AS PENALTY 
	, ISNULL(FIFA.DCDCC,0) AS FIFA
	, 0 AS GED 
	, 0 AS BREACH 
	, 0 AS TITLE_SEARCH
	, 0 AS MAIL_FEES
	, ISNULL(ADM.DCDCC,0) AS ADMINISTRATION
	, 0 AS PRIOR_YEARS 
	, 0 AS ADVERTISING 
	, 0 AS CREDIT_CARD_FEES
	, M.MBTAX+M.MBERT + M.MBP5P + M.MBP10 + M.MBINT + M.MBCST 
		+ case 
			when m.mbtyr=2020 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			when m.mbtyr=2019 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			when m.mbtyr=2018 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			when m.mbtyr=2017 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			when m.mbtyr=2016 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005626),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			when m.mbtyr=2015 then 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005417),2)*11 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005626),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0063),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.007083),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.006458),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.0052),2)*12 + 
				round((M.MBTAX+M.MBERT+M.MBP05-M.MBTXP)*(.005208),2)*5 
			else 0
			end
		+ M.MBP10+M.MBP5P+M.MBP05+round(((M.MBTAX+M.MBERT-M.MBTXP)*.20),2)

		AS TOTAL -- MBCST ALREADY INCLUDES FIFA, COLLECTION, AND PEACHSTATE (COLLECT FEES?)
	, M.MBTAX+M.MBERT-M.MBTXP 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.MBNOD 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
FROM (SELECT ROW_NUMBER() OVER( PARTITION BY MBTYR, MBBIL ORDER BY MBNOD) AS ROWID, * FROM TXMBF) M 
left JOIN DY2020.DBO.PROPERTY P ON P.COMMENTS=M.MBACT AND CASE  WHEN M.MBFIL1='U' THEN 'U' WHEN P.PROPTYPE = 'M' THEN 'MN' ELSE P.PROPTYPE END =M.MBFIL1
LEFT JOIN TXDCF FIFA ON FIFA.DCTYR=M.MBTYR AND FIFA.DCBIL=M.MBBIL AND FIFA.DCNOD=M.MBNOD AND FIFA.DCCTC=1
LEFT JOIN TXDCF ADM ON ADM.DCTYR=M.MBTYR AND ADM.DCBIL=M.MBBIL AND ADM.DCNOD=M.MBNOD AND ADM.DCCTC=4
LEFT JOIN TXDCF CF ON CF.DCTYR=M.MBTYR AND CF.DCBIL=M.MBBIL AND CF.DCNOD=M.MBNOD AND CF.DCCTC=10
WHERE ((M.MBTYR = @DIGYR AND isnull(M.MBFIL1,'') IN ('U'))  )
	and m.mbbil=45739
order by m.MBBIL

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

INSERT INTO DY2020.[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
	, AMTXP 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
	, AMTXP+AMINP+AMP5P+AMP1P+AMCSP AS TOTAL
	, CASE WHEN AMPOA='P' THEN 'PAYMENT' ELSE 'ADJUSTMENT' END AS PAYMENT_TYPE
	, '' AS PAID_BY
	, B.TOTAL - (AMTXP+AMINP+AMP5P+AMP1P+AMCSP) 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
	, 0 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
	, AMBIL AS BILL_NO
	, LEFT(ISNULL(AMFIL1,''),1) AS BILL_TYPE 

  FROM [NEWTONTBS].[DBO].[TXAMF] T 
  INNER JOIN (SELECT BILL.*, P.COMMENTS AS TBSBILL_NO FROM DY2020.DBO.BILL INNER JOIN DY2020.DBO.PROPERTY P ON P.PROPERTYKEY=BILL.PROPERTYKEY) B ON isnull(B.COMMENTS,'') =  isnull(T.AMNOD,'') AND B.BILL_YEAR=T.AMTYR AND T.AMBIL=B.BILL_NO
  WHERE t.AMPOA='P' and t.AMTYR=@DIGYR and ISNULL(B.UTILITY,'')=1


UPDATE DY2020.DBO.PROPERTY SET E_R=1 WHERE PROPERTYKEY IN (SELECT PROPERTYKEY FROM DY2020.DBO.BILL WHERE ISNULL(UTILITY,0)=1 AND BILLKEY IN (SELECT BILLKEY FROM DY2020.DBO.PAYMENT WHERE PAYMENT_TYPE='ADJUSTMENT' AND ADV_TAX<0)) 

INSERT INTO DY2020.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 DY2020.DBO.PAYMENT WHERE PAYMENT_TYPE='ADJUSTMENT' AND ADV_TAX<0 AND BILL_NO IN (SELECT BILL_NO FROM DY2020.DBO.BILL WHERE ISNULL(UTILITY,0)=1)

DELETE FROM DY2020.DBO.PAYMENT 
WHERE PAYMENT_TYPE='ADJUSTMENT' AND ADV_TAX<0 AND BILL_NO IN (SELECT BILL_NO FROM DY2020.DBO.BILL WHERE ISNULL(UTILITY,0)=1)

UPDATE DY2020.DBO.BILL SET ADV_TAX = BREACH + ADV_TAX, BREACH=0 WHERE ISNULL(UTILITY,0)=1 AND BREACH < 0

UPDATE B SET B.ADMINISTRATION = X.ADMINISTRATION_AMT
FROM DY2020.DBO.BILL B 
INNER JOIN 
	(
	SELECT BILL_NO, BILL_YEAR, SUM(ADMINISTRATION) AS ADMINISTRATION_AMT 
	FROM DY2020.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
WHERE ISNULL(b.UTILITY,0)=1

DECLARE @ITEMKEY AS INT = (SELECT MAX(ITEMKEY)+1 FROM DY2020.DBO.PROPERTY_ITEMS)

INSERT INTO DY2020.DBO.PROPERTY_ITEMS (ITEMKEY, PROPERTYKEY, DIGCLASS, DIGSTRAT, ACRES, ITEMVALUE, VALUE40RAW)
SELECT @ITEMKEY + ROW_NUMBER() OVER( ORDER BY PDACT ) AS ITEMKEY 
	, P.PROPERTYKEY
	, LEFT(CASE 
				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 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 DY2020.DBO.PROPERTY P ON COMMENTS=T.PDACT 
INNER JOIN DY2020.DBO.BILL B ON B.PROPERTYKEY=P.PROPERTYKEY AND ISNULL(B.UTILITY,0)=1 

