C11/System/NAVISION/IQD/Serv_Teile/belege_ker_umsatz_tuz.sql

select T1."No_" as "No",
	   T1."Name" as "Name",
	   T1."Account Type" as "Account Type",
	   T1."Income_Balance" as "Income Balance",
	   T2."G_L Account No_" as "G L Account No",
	   T2."Posting Date" as "Posting Date",
	   T2."Document No_" as "Document No",
	   T2."Description" as "Description",
	   T2."Bal_ Account No_" as "Bal Account No",
	   T2."Department Code" as "Department Code",
	   T2."Make Code" as "Make Code",
	   T2."User ID" as "User Id",
	   T2."Reason Code" as "Reason Code",
	   T2."Document Date" as "Document Date",
	   T2."Branch Code" as "Branch Code",
	   T2."Main Area" as "Main Area",
	   T2."VIN" as "Vin",
	   T2."Book No_" as "Book No",
	   T3."Code" as "Code",
	   T3."Name" as "Department_Name",
	   T2."Posting Date" as "Jahr",
	   CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END as "Betrieb Nr",
	   T2."G_L Account No_" as "Konto Nr",
	   ((convert(float, T2."Amount"))) as "Betrag",
	   CASE WHEN ((left(T2."G_L Account No_",1)) IN ('8','6')) THEN ((((convert(float, T2."Amount")))) * -1) ELSE (0) END as "Umsatzerlöse",
	   CASE WHEN (((left(T2."G_L Account No_",1)) IN ('7','5')) or (T2."G_L Account No_" = '44020')) THEN ((((convert(float, T2."Amount")))) * -1) ELSE (0) END as "VAK",
	   (convert(float, T2."Amount")) as "Amount_1",
	   CASE WHEN ((-1 * datediff(day, (getdate()), T2."Posting Date")) <= 60) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + T2."User ID") ELSE null END as "Text",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   (CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END) as "Standort_ID",
	   CASE WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN (T2."Branch Code" = 'MM') THEN ('10') WHEN (T2."Branch Code" = 'VÖH') THEN ('20') WHEN (T2."Branch Code" = 'KRU') THEN ('30') WHEN (T2."Branch Code" = 'ULM') THEN ('40') WHEN (T2."Branch Code" = 'LL') THEN ('50') WHEN (T2."Branch Code" = 'GZ') THEN ('55') ELSE ((left(T2."Department Code",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_Name"
from "NAVISION"."import"."G_L_Account" T1,
	("NAVISION"."import"."G_L_Entry" T2 left outer join "NAVISION"."import"."Department" T3 on (T2."Department Code" = T3."Code") and (T2."Client_DB" = T3."Client_DB"))
where ((T1."No_" = T2."G_L Account No_") and (T1."Client_DB" = T2."Client_DB"))
and (((((((T1."Income_Balance" = 0) and ((left(T2."G_L Account No_",1)) IN ('8'))) and (not T2."Description" IN ('V-ZAHL'))) and (T2."Document No_" <> 'ABSCHLUSS2008_1')) and (not T2."Document No_" IN ('ABSCHLUSS2009_1','ABSCHLUSS2009_2','ABSCHLUSS2009_3','ABSCHLUSS_1','ABSCHLUSS2011','ABSCHLUSS2012','ABSCHLUSS2013','ABSCHLUSS2014','ABSCHLUSS2015','ABSCHLUSS2016','ABSCHLUSS2017','ABSCHLUSS2018','ABSCHLUSS2019'))) and (T2."Source Code" <> 'JAHRABSCH')) and (T2."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000')))