1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
| DECLARE @DaysMinus INT = 0
-- Auswertung immer zum letzten des Vormonats
--1
DECLARE @Monatsbeginn DATE = ('31.07.2019' - @DaysMinus) - DAY((GETDATE() - @DaysMinus))
DECLARE @Jahresbeginn DATE = DATEADD(DAY, -1, DATEFROMPARTS(YEAR(@Monatsbeginn), 1, 1))
SELECT CON.No_ AS Vertrag,
CON.Product Code AS Vertragsartcode,
P.Description AS Vertragsart,
Konten AS Konto,
CAST(GL.SaldoZumJahresbeginn AS MONEY) AS Stand Jahresbeginn SK,
CAST(GL.Zufuehrung AS MONEY) AS Zugang lfd. Jahr,
CAST(GL.Aufloesung AS MONEY) AS Tilgung lfd. Jahr,
StandErrechnet AS Stand aktuell errechnete Summe aller Zugänge,
StandAktuellesSK AS Stand aktuelles SK,
DifferenzSKErrechnet AS Differenz SK vs Errechnet,
--2
Beendet,
CASE WHEN Konten = 'Gebührenabgrenzung'
THEN CAST(ISNULL(PPL.Zinsen,0) AS MONEY)
WHEN (Konten IN ('PRAP 100 Pro', 'PRAP Sachversicherung') AND StandAktuellesSK ! = 0 AND Beendet = 0 )
OR (Konten IN ('PRAP 100 Pro', 'PRAP Sachversicherung') AND StandAktuellesSK != 0 AND Beendet = 1)
THEN CAST(ISNULL(Weiterverrechungsbetrag,0) AS MONEY)
ELSE 0
END AS Stand aktueller Zahlungsplan,
CASE WHEN Konten = 'Gebührenabgrenzung'
THEN CAST(ISNULL(StandAktuellesSK,0) - ISNULL(PPL.Zinsen,0) AS MONEY)
WHEN (Konten IN ('PRAP 100 Pro', 'PRAP Sachversicherung') AND StandAktuellesSK != 0 AND Beendet = 0 )
OR (Konten IN ('PRAP 100 Pro', 'PRAP Sachversicherung') AND StandAktuellesSK != 0 AND Beendet = 1 )
THEN CAST(ISNULL(StandAktuellesSK - Weiterverrechungsbetrag,0) AS MONEY)
ELSE 0 AS Differenz SK vs Zahlungsplan,
--3
CAST(CAL.Begin Date AS DATE) AS BeginnDatum,
CSH.To Status AS Vertragsstatus,
CAL.Duration AS Laufzeit,
PPL.Anzahl AS Restlaufzeit,
RSH.To Status AS Mahnstatus,
CON.Property Insurance Code AS Sachversicherung,
CASE WHEN RPL.Forfaitierung = 1 THEN 'Ja' ELSE 'Nein' END AS Forfaitiert,
RPL.Forfaitierung,
@Monatsbeginn AS Letzter des Vormonats,
CAST (GETDATE() AS DATE) AS Letztes Ausfuehrungsdatum
FROM dbo.abcfinance leasing$NCFB Contract CON WITH (NOLOCK)
JOIN dbo.abcfinance leasing$NCFB Product P WITH (NOLOCK) ON P.Code = CON.Product Code
JOIN dbo.abcfinance leasing$NCFB Contr_ Calc History CAL WITH (NOLOCK) ON CAL.Contract No_ = CON. No_ AND Active = 1
LEFT JOIN
(
SELECT TOP 1 WITH TIES
CSH.Contract No_,
CSH.To Status,
CSH.Created Date,
CSH.Created Time
FROM dbo.abcfinance leasing$NCFB Contr_ Status History CSH WITH (NOLOCK)
LEFT JOIN dbo.abcfinance leasing$NCFB Contr_ Status CS ON CS.Code = CSH.To Status
WHERE CSH.Status Type = 0 AND @Monatsbeginn >= CSH.Created Date
ORDER BY ROW_NUMBER() OVER (PARTITION BY CSH.Contract No_ ORDER BY CSH.Contract No_, CSH.Created Date DESC, CSH.Created Time DESC)
) CSH ON CSH.Contract No_ = CON.No_
-- Add Reminder Status
LEFT JOIN
(SELECT
--4
RSH.Contract No_,
RSH.To Status,
RSH.Created Date,
RSH.Created Time
FROM dbo.abcfinance leasing$NCFB Contr_ Status History RSH WITH (NOLOCK)
WHERE RSH.Status Type = 1 AND @Monatsbeginn >= RSH.Created Date
ORDER BY ROW_NUMBER() OVER (PARTITION BY RSH.Contract No_ ORDER BY RSH.Contract No_, RSH.Created Date DESC, RSH.Created Time DESC)
) RSH ON RSH.Contract No_ = CON.No_
LEFT JOIN (
SELECT
COUNT(*) AS Anzahl,
SUM(PPL.Interest) AS Zinsen,
PPL.Contract No_ AS Contract No_,
PPL.Entry No_ AS Entry No_
FROM dbo.abcfinance leasing$NCFB Contr_ Payment Plan Line PPL
LEFT JOIN
( select top 1 WITH ties
CAL.Contract No_,
CAL.Payment Plan Header Entry No_,
CAL.Valid From,
CAL.Valid to
FROM abcfinance leasing$NCFB Contr_ Calc History CAL
where CAL.Valid From <= @Monatsbeginn AND CAL.Valid to >= @Monatsbeginn
ORDER by ROW_NUMBER() over (PARTITION BY CAL.Contract No_ ORDER BY Payment Plan Header Entry No_ DESC)
) CAL on CAL.Contract No_ = PPL.Contract No_
AND PPL.Entry No_ = CAL.Payment Plan Header Entry No_
WHERE PPL.Date >= @Monatsbeginn
AND Period > 0
GROUP BY PPL.Contract No_,
PPL.Entry No_) PPL ON PPL.Contract No_ = CON.No_ AND PPL.Entry No_ = CAL.Payment Plan Header Entry No_
-- Weiterverrechnungsbetrag hinzufügen
LEFT JOIN (
SELECT Contract No_
,CAST(ISNULL(SUM(Sales Amount),0) AS MONEY) AS Weiterverrechungsbetrag
FROM navilease.dbo.abcfinance leasing$NCFB Contr_ Recurr_ Chg_ Plan WITH (NOLOCK)
--5
WHERE (Recurring Charging Code = '100PRO%' OR Recurring Charging Code LIKE 'SACH%')
AND Active = 1
AND Date >= @Monatsbeginn
GROUP BY Contract No_) CRCP ON CRCP.Contract No_ = CON.No_
LEFT JOIN (
SELECT
NCFB Source No_,
'Gebührenabgrenzung' AS Konten,
SUM(Amount) AS Saldo,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0) AS MONEY) AS SaldoZumJahresbeginn,
CAST(ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413040','413540','413542','413046','413047') THEN Amount END),0) AS MONEY) AS Aufloesung,
CAST(ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413030','413530','413532','413017','413036') THEN Amount END),0) AS MONEY) AS Zufuehrung,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0)
--6
+ SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413040','413540','413542','413046','413047') THEN Amount END)
- ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413030','413530','413532','413017','413036') THEN Amount * -1 END),0) AS Money) AS StandErrechnet,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Monatsbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0) AS MONEY) AS StandAktuellesSK,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Monatsbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0)
- (ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0)
+ ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413040','413540','413542','413046','413047') THEN Amount END),0)
- ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413030','413530','413532','413017','413036') THEN Amount * -1 END),0)) AS MONEY) AS DifferenzSKErrechnet
FROM dbo.abcfinance leasing$G_L Entry WITH (NOLOCK)
WHERE G_L Account No_ IN ('050011', '050016', '050018', '050021','051011', '051014' ,'413030','413530','413040','413540','340100','413090', '51014','413542','413046','413047','413532','413017','413036')
AND Posting Date <= @Monatsbeginn
GROUP BY NCFB Source No_
HAVING ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0) != 0
OR ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413040','413540','413542','413046','413047') THEN Amount END),0) != 0
OR ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413030','413530','413532','413017','413036') THEN Amount END),0) != 0
OR ISNULL(SUM(CASE WHEN Posting Date <= @Monatsbeginn AND G_L Account No_ IN ('050011','051011', '051014', '050016', '050018', '050021', '51014') THEN Amount * -1 END),0) != 0
UNION
SELECT
NCFB Source No_,
'PRAP 100 Pro' AS Konten,
SUM(Amount) AS Saldo,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0) AS MONEY) AS SaldoZumJahresbeginn,
CAST(ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413041','413541') THEN Amount END),0) AS MONEY) AS Aufloesung,
CAST(ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413031','413531') THEN Amount END),0) AS MONEY) AS Zufuehrung,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0)
+ ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413041','413541') THEN Amount END),0)
- ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413031','413531') THEN Amount * -1 END),0) AS Money) AS StandErrechnet,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Monatsbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0) AS MONEY) AS StandAktuellesSK,
CAST(ISNULL(SUM(CASE WHEN Posting Date <= @Monatsbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0)
- (ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0)
+ ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413041','413541') THEN Amount END),0)
- ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413031','413531') THEN Amount * -1 END),0)) AS MONEY) AS DifferenzSKErrechnet,
--7
Posting Date
FROM dbo.abcfinance leasing$G_L Entry WITH (NOLOCK)
--8
WHERE ISNULL(SUM(CASE WHEN Posting Date <= @Jahresbeginn AND G_L Account No_ IN ('050012','051012') THEN Amount * -1 END),0) != 0
OR ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413041','413541') THEN Amount END),0) != 0
OR ISNULL(SUM(CASE WHEN Posting Date > @Jahresbeginn AND G_L Account No_ IN ('413031','413531') THEN Amount END),0) != 0
GROUP BY NCFB Source No_,
Posting Date
) GL ON GL.NCFB Source No_ = CON.No_
LEFT JOIN (
SELECT Contract No_ ,Valid From ,Valid to, 1 AS Forfaitierung FROM dbo.abcfinance leasing$NCFB Refi Post_ Line WITH (NOLOCK) WHERE Type = 2 AND Detached = 0 AND Valid From <= @Monatsbeginn AND Valid to > @Monatsbeginn GROUP BY Contract No_, Valid From, Valid to
) RPL ON RPL.Contract No_ = CON.No_
WHERE Konten IS NOT NULL |
Partager