C11/System/CARLO/IQD/Belege/belege_eds_skr_stk.sql

select T1."Nr_" as "Nr",
	   T1."Name" as "Name",
	   T1."Kontoart" as "Kontoart",
	   T1."GuV_Bilanz" as "Guv Bilanz",
	   T1."Soll_Haben" as "Soll Haben",
	   T1."Kostenstellen Buchung" as "Kostenstellen Buchung",
	   T2."Lfd_ Nr_" as "Lfd Nr",
	   T2."Sachkontonr_" as "Sachkontonr",
	   T2."Buchungsdatum" as "Buchungsdatum",
	   T2."Belegart" as "Belegart",
	   T2."Belegnr_" as "Belegnr",
	   T2."Beschreibung" as "Beschreibung",
	   T2."Gegenkontonr_" as "Gegenkontonr",
	   0 as "Betrag_ori",
	   T2."Kostenstellencode" as "Kostenstellencode",
	   T2."Markencode" as "Markencode",
	   T2."Benutzer ID" as "Benutzer Id",
	   T2."Herkunftscode" as "Herkunftscode",
	   T2."Nachbuchung" as "Nachbuchung",
	   T2."Menge" as "Menge_ori",
	   T2."MWSt Betrag" as "Mwst Betrag",
	   T2."Buchungsart" as "Buchungsart",
	   T2."Geschäftsbuchungsgruppe" as "Geschäftsbuchungsgruppe",
	   T3."Beschreibung" as "Geschäftsbuchungsgruppe_Beschreibung",
	   T2."Produktbuchungsgruppe" as "Produktbuchungsgruppe",
	   T4."Beschreibung" as "Produktbuchungsgruppe_Beschreibung",
	   T2."Gegenkontoart" as "Gegenkontoart",
	   T2."Transaktionsnr_" as "Transaktionsnr",
	   (T2."Sollbetrag") as "Sollbetrag",
	   (T2."Habenbetrag") as "Habenbetrag",
	   T2."Belegdatum" as "Belegdatum",
	   T2."Externe Belegnummer" as "Externe Belegnummer",
	   T2."Herkunftsart" as "Herkunftsart",
	   T2."Herkunftsnr_" as "Herkunftsnr",
	   T2."Filialcode" as "Filialcode",
	   T2."Hauptbereich" as "Hauptbereich",
	   T2."Fahrgestellnummer" as "Fahrgestellnummer",
	   T2."Buchnummer" as "Buchnummer",
	   T2."Fahrzeug-Kz" as "Fahrzeug-kz",
	   T2."Umgebucht" as "Umgebucht",
	   T2."Storniert" as "Storniert",
	   T2."Fahrzeugklassecode" as "Fahrzeugklassecode",
	   T5."Code" as "Code",
	   T5."Name" as "Name",
	   T6."Code" as "Code",
	   T6."Bezeichnung" as "Bezeichnung",
	   (0) as "Betrag_1",
	   T2."Client_DB" as "Rechtseinheit",
	   CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as "Betrieb_Nr",
	   T6."Bezeichnung" as "Betrieb_1",
	   CASE WHEN (T2."Markencode" = '') THEN ('00 - ohne Marke') ELSE (T2."Markencode") END as "Marke_ori",
	   (database()) + ' - ' + 'Kandel' as "Mandant",
	   (cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0)) as "Menge_1",
	   T2."Buchungsdatum" as "Bookkeep Date",
	   CASE WHEN (((day((now()) - T2."Buchungsdatum")) <= 90) and (T1."Nr_" <> '630000')) THEN (T2."Belegnr_" + ' - ' + T2."Beschreibung" + ' - ' + T2."Benutzer ID") ELSE null END as "Text",
	   CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END as "KST_aus_Code",
	   CASE WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '1%') THEN ('1') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '2%') THEN ('2') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '3%') THEN ('6') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '41') THEN ('3') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '44') THEN ('4') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) = '45') THEN ('5') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '5%') THEN ('7') WHEN ((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) LIKE '9%') THEN ('0') ELSE null END as "Kostenstelle_ori",
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) as "KST_1",
	   (CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END) + ' - ' + T5."Name" as "KST_2",
	   T2."Sachkontonr_" as "Acct Nr_ori",
	   ((CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END)) as "Betrag",
	   CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END as "Menge",
	   CASE WHEN (T2."Client_DB" = '2') THEN ('20') ELSE (T2."Filialcode") END as "Betrieb",
	   T2."Benutzer ID" as "Benutzer",
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Fahrgestellnummer") ELSE null END as "Fahrgestellnummer",
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Modell") ELSE null END as "Modell_neu",
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Markencode") ELSE null END as "Markencode",
	   CASE WHEN ((T1."Nr_" BETWEEN '700000' AND '719999') or (T1."Nr_" BETWEEN '800000' AND '819999')) THEN (T7."Verkäufer Nr_ Verkauf") ELSE null END as "Verkäufer Nr Verkauf",
	   CASE WHEN ((left(T1."Nr_",1)) IN ('5','6','7','8')) THEN ('TEK') ELSE ('Kosten/Neutral') END as "Konto KZ",
	   CASE WHEN (((left(T1."Nr_",2)) IN ('70','71','80','81')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '1')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('880','881','890','891')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '1')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('72','82')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '2')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('882','892')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '2')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('73','83')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '3')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('883','893')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '3')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('74','75','84','85')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '4')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('884','885','894','899')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '4')) THEN ('nein') WHEN (((left(T1."Nr_",2)) IN ('76','86')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '5')) THEN ('nein') WHEN (((left(T1."Nr_",3)) IN ('896')) and ((substring((CASE WHEN (T2."Kostenstellencode" = '') THEN ('99') ELSE ((substring(T2."Kostenstellencode", 3, 2))) END), 1, 1)) <> '5')) THEN ('nein') ELSE ('ja') END as "Plausibilität",
	   (left(T2."Kostenstellencode",2)) as "Marke aus KST",
	   (T2."MB_Makecode") as "Marke",
	   (substring(T1."Nr_", 1, 1)) as "Susa",
	   T1."GuV_Bilanz" as "GuV_Bilanz",
	   T2."MB_Distributionchannelcode" as "Absatzkanal_Code",
	   CASE WHEN (T2."Kostenstellencode" <> '') THEN (T2."Kostenstellencode") ELSE ('00') END as "KST_ori",
	   T2."MB_Costcentercode" as "KST",
	   T2."MB_Makecode" as "Mb Makecode",
	   T2."MB_Locationcode" as "Mb Locationcode",
	   T2."MB_Costcentercode" as "Mb Costcentercode",
	   T2."MB_Distributionchannelcode" as "Mb Distributionchannelcode",
	   T2."MB_Costunitcode" as "Mb Costunitcode",
	   T2."MB_Taxationcode" as "Mb Taxationcode",
	   T2."MB_Costunitcode" as "Kostenträger",
	   T2."Sachkontonr_" + '_' + T2."Kostenstellencode" + '_STK' as "Acct Nr",
	   T8."MB Makecode" as "Mb Makecode",
	   T8."Code" as "Code_MB_Cost_Unit",
	   T8."Description" as "Description_MB_Cost_Unit",
	   CASE WHEN (T2."MB_Costunitcode" BETWEEN '01' AND '49') THEN (T2."MB_Makecode" + '-' + T2."MB_Costunitcode") ELSE (T2."MB_Costunitcode") END as "Kostenträger_FIBU"
from "CARLO"."import"."Sachkonto" T1,
	(((((("CARLO"."import"."Sachposten" T2 left outer join "CARLO"."import"."Geschaeftsbuchungsgrp" T3 on (T2."Geschäftsbuchungsgruppe" = T3."Code") and (T2."Client_DB" = T3."Client_DB")) left outer join "CARLO"."import"."Produktbuchungsgrp" T4 on (T2."Produktbuchungsgruppe" = T4."Code") and (T2."Client_DB" = T4."Client_DB")) left outer join "CARLO"."import"."Kostenstelle" T5 on (T5."Code" = T2."Kostenstellencode") and (T5."Client_DB" = T2."Client_DB")) left outer join "CARLO"."import"."Filialbezeichnung" T6 on (T2."Filialcode" = T6."Code") and (T2."Client_DB" = T6."Client_DB")) left outer join "CARLO"."import"."Fahrzeug" T7 on (T2."Fahrgestellnummer" = T7."Fahrgestellnummer") and (T2."Client_DB" = T7."Client_DB")) left outer join "CARLO"."import"."MB_Costunit" T8 on ((T2."MB_Costunitcode" = T8."Code") and (T2."Client_DB" = T8."Client_DB")) and (T2."MB_Makecode" = T8."MB Makecode"))
where ((T1."Nr_" = T2."Sachkontonr_") and (T1."Client_DB" = T2."Client_DB"))
and (((((not T2."Beschreibung" LIKE '%Nullstellung%') and (T2."Buchungsdatum" >= convert(datetime, '2018-01-01 00:00:00.000'))) and (T1."GuV_Bilanz" = 0)) and ((T1."Nr_" BETWEEN '800000' AND '829900') or (T1."Nr_" BETWEEN '851000' AND '851007'))) and ((((CASE WHEN ((((left(T2."Sachkontonr_",2)) IN ('80','81','82','89','85')) and (not T2."Sachkontonr_" IN ('811100'))) and (T2."Fahrzeug-Kz" IN (1,2))) THEN (((cinteger(nconvert((cast_numberToString(cast_integer(T2."Menge")))),0))) * -1) ELSE (0) END))) <> 0))
-- order by "Fahrgestellnummer" asc,"Lfd Nr" asc