TRUNCATE TABLE DY2022.DBO.PENALTY_RP

DROP TABLE DY2022.DBO.#PEN_RP ;
;
        WITH VDEF2 AS 
        ( 
        SELECT 2022 AS DIGYR, * FROM DY2022.DBO.DEFAULTS2 
        UNION 
        SELECT 2021 AS DIGYR, * FROM DY2021.DBO.DEFAULTS2 
        UNION 
        SELECT 2020 AS DIGYR, * FROM DY2020.DBO.DEFAULTS2 
        UNION 
        SELECT 2019 AS DIGYR, * FROM DY2019.DBO.DEFAULTS2 
        UNION 
        SELECT 2018 AS DIGYR, * FROM DY2018.DBO.DEFAULTS2 
        UNION 
        SELECT 2017 AS DIGYR, * FROM DY2017.DBO.DEFAULTS2 
        UNION 
        SELECT 2016 AS DIGYR, * FROM DY2016.DBO.DEFAULTS2 
        UNION 
        SELECT 2015 AS DIGYR, * FROM DY2015.DBO.DEFAULTS2 
        UNION 
        SELECT 2014 AS DIGYR, * FROM DY2014.DBO.DEFAULTS2 
        ) 
 
 		SELECT *
        INTO DY2022.DBO.#PEN_RP 
 		FROM (
        SELECT B.BILL_NO 
            , B.BILL_YEAR 
            , B.DUE_DATE + 120 AS P_DATE 
            , D.PENALTY_RATE1 AS P_RATE 
            , CASE WHEN B.DUE_DATE + 120 < CONVERT(DATE,GETDATE()) 
                THEN ROUND((V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) * D.PENALTY_RATE1,2) 
                ELSE 0 
                END AS P_AMT 
            , CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS P_APPLYDATE 
 			, B.BILL_TYPE 
 
        FROM DY2022.DBO.VBILL_BALANCE_7 V7 
        INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY  and b.PENALTY=0 and b.bill_type!='M'
        INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE AND P.PROPTYPE <>'M'   
        LEFT JOIN VDEF2 D ON D.DIGYR=B.BILL_YEAR 
        WHERE ISNULL(V7.ADV_TAX_BALANCE,0) + ISNULL(V7.FEES_BALANCE,0) + ISNULL(V7.FEES_OTHER_BALANCE,0) > ISNULL(D.WRITEOFF_MAX,0) 
            AND B.DUE_DATE + 120 < CONVERT(DATE,GETDATE()) 
            AND B.DUE_DATE + 120 > ISNULL((SELECT MAX(P_DATE) FROM DY2022.DBO.PENALTY_RP),0) 
            AND D.PENALTY_RATE1 > 0.0 
			--AND V7.BILL_NO=61217
        UNION 
 
        SELECT B.BILL_NO 
            , B.BILL_YEAR 
            , B.DUE_DATE + 240 AS P_DATE 
            , D.PENALTY_RATE2 AS P_RATE 
            , CASE WHEN B.DUE_DATE + 240 < CONVERT(DATE,GETDATE()) 
                THEN ROUND((V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) * D.PENALTY_RATE2,2) 
                ELSE 0 
                END AS P_AMT 
            , CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS P_APPLYDATE 
 			, B.BILL_TYPE 
        FROM DY2022.DBO.VBILL_BALANCE_7 V7 
        INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY  and b.PENALTY=0 and b.bill_type!='M'
        INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE AND P.PROPTYPE <>'M'  
        LEFT JOIN VDEF2 D ON D.DIGYR=B.BILL_YEAR 
        WHERE (V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) > ISNULL(D.WRITEOFF_MAX,0) 
            AND B.DUE_DATE + 240 < CONVERT(DATE,GETDATE()) 
            AND B.DUE_DATE + 240 > ISNULL((SELECT MAX(P_DATE) FROM DY2022.DBO.PENALTY_RP),0) 
            AND D.PENALTY_RATE2 > 0.0 
			--AND V7.BILL_NO=61217
 
        UNION 
 
        SELECT B.BILL_NO 
            , B.BILL_YEAR 
            , B.DUE_DATE + 360 AS P_DATE 
            , D.PENALTY_RATE3 AS P_RATE 
            , CASE WHEN B.DUE_DATE + 360 < CONVERT(DATE,GETDATE()) 
                THEN ROUND((V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) * D.PENALTY_RATE3,2) 
                ELSE 0 
                END AS P_AMT 
            , CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS P_APPLYDATE 
 			, B.BILL_TYPE 
        FROM DY2022.DBO.VBILL_BALANCE_7 V7 
        INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY and b.PENALTY=0 and b.bill_type!='M' 
        INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE AND P.PROPTYPE <>'M'  
        LEFT JOIN VDEF2 D ON D.DIGYR=B.BILL_YEAR 
        WHERE (V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) > ISNULL(D.WRITEOFF_MAX,0) 
            AND B.DUE_DATE + 360 < CONVERT(DATE,GETDATE()) 
            AND B.DUE_DATE + 360 > ISNULL((SELECT MAX(P_DATE) FROM DY2022.DBO.PENALTY_RP),0) 
            AND D.PENALTY_RATE3 > 0.0 
			--AND V7.BILL_NO=61217

        UNION 
 
        SELECT B.BILL_NO 
            , B.BILL_YEAR 
            , B.DUE_DATE + 480 AS P_DATE 
            , D.PENALTY_RATE4 AS P_RATE 
            , CASE WHEN B.DUE_DATE + 480 < CONVERT(DATE,GETDATE()) 
                THEN ROUND((V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) * D.PENALTY_RATE4,2) 
                ELSE 0 
                END AS P_AMT 
            , CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS P_APPLYDATE 
 			, B.BILL_TYPE 
        FROM DY2022.DBO.VBILL_BALANCE_7 V7 
        INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY  and b.PENALTY=0 and b.bill_type!='M'
        INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE AND P.PROPTYPE <>'M'  
        LEFT JOIN VDEF2 D ON D.DIGYR=B.BILL_YEAR 
        WHERE (V7.ADV_TAX_BALANCE + V7.FEES_BALANCE + V7.FEES_OTHER_BALANCE) > ISNULL(D.WRITEOFF_MAX,0) 
            AND B.DUE_DATE + 480 < CONVERT(DATE,GETDATE()) 
            AND B.DUE_DATE + 480 > ISNULL((SELECT MAX(P_DATE) FROM DY2022.DBO.PENALTY_RP),0) 
            AND D.PENALTY_RATE4 > 0.0 
			--AND V7.BILL_NO=61217
        ) X 

 INSERT INTO DY2022.DBO.PENALTY_RP ( 
        BILL_NO 
        , BILL_YEAR 
        , P_DATE 
        , P_RATE 
        , P_AMT 
        , P_APPLYDATE 
        ) 
 
        SELECT 
            I.BILL_NO 
            , I.BILL_YEAR 
            , I.P_DATE 
            , I.P_RATE 
            , I.P_AMT 
            , I.P_APPLYDATE 
        FROM DY2022.DBO.#PEN_RP I 
        INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE=B.BILL_TYPE 
 
     ; 
-- UPDATE BILL TO SHOW PENALTY AMOUNTS 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2022.DBO.BILL BU 
    INNER JOIN  (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2022  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2021.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2021  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2020.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2020  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2019.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2019  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2018.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2018  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2017.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2017  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2016.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2016  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2015.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2015  AND BU.BILL_TYPE NOT IN ('M') ; 
 
    UPDATE BU SET BU.PENALTY= BU.PENALTY + I.P_AMT, BU.TOTAL= BU.TOTAL + I.P_AMT, BU.BALANCE_DUE= BU.BALANCE_DUE + I.P_AMT 
    FROM DY2014.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(P_AMT) AS P_AMT FROM DY2022.DBO.#PEN_RP WHERE P_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2014  AND BU.BILL_TYPE NOT IN ('M') ; 
 
 
 
INSERT INTO DY2022.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2022.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2021.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2021.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2020.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2020.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2019.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2019.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2018.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2018.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2017.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2017.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2016.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2016.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2015.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2015.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2014.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT P.BILL_NO, P.BILL_YEAR, P_AMT, P_DATE, 'PENALTY_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),P_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#PEN_RP P 
INNER JOIN DY2014.DBO.BILL B ON B.BILL_NO=P.BILL_NO AND B.BILL_YEAR=P.BILL_YEAR AND P.BILL_TYPE <> 'M' ;