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

select T1."No_" as "No",
	   T1."Name" as "Name_2",
	   T1."Search Description" as "Search Description",
	   T1."Account Type" as "Account Type",
	   T1."Department Code" as "Department Code_2",
	   T1."Make Code" as "Make Code_2",
	   T1."Income_Balance" as "Income Balance",
	   T1."Last Date Modified" as "Last Date Modified",
	   T2."Entry No_" as "Entry No",
	   T2."G_L Account No_" as "G L Account No",
	   T2."Posting Date" as "Posting Date",
	   T2."Document Type" as "Document Type",
	   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."Source Code" as "Source Code",
	   CASE WHEN ((T2."Document No_" LIKE 'VRGGFZ%') or (T2."Document No_" LIKE 'VRGF%')) THEN (T2."Veh_ Source Code") WHEN (((T2."Document No_" LIKE 'VGUGFZ%') or (T2."Document No_" LIKE 'VGGF%')) and (T2."Veh_ Source Code" <> 0)) THEN (-1) ELSE (0) END as "Quantity",
	   T2."Reason Code" as "Reason Code",
	   T2."Gen_ Posting Type" as "Gen Posting Type",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Bal_ Account Type" as "Bal Account Type",
	   T2."Transaction No_" as "Transaction No",
	   T2."Document Date" as "Document Date",
	   T2."External Document No_" as "External Document No",
	   T2."Source Type" as "Source Type",
	   T2."Source No_" as "Source No",
	   T2."No_ Series" as "No Series",
	   T2."Branch Code" as "Branch Code",
	   T2."Main Area" as "Main Area",
	   T2."VIN" as "Vin",
	   T2."Book No_" as "Book No",
	   T2."Veh_ Source Code" as "Veh Source Code",
	   T2."Reposted" as "Reposted",
	   T2."Corrected" as "Corrected",
	   T2."Correction to curr_ No_" as "Correction To Curr No",
	   T2."Reposting to curr_ No_" as "Reposting To Curr No",
	   T3."Code" as "Code",
	   T3."Name" as "Name",
	   T2."Posting Date" as "Jahr",
	   CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END as "Betrieb Nr",
	   T2."G_L Account No_" as "Konto Nr",
	   ((convert(float, T2."Amount"))) * -1 as "Betrag",
	   (convert(float, T2."Amount")) as "Amount_1",
	   CASE WHEN ((T1."No_" LIKE '4%') and ((-1 * datediff(day, (getdate()), T2."Posting Date")) <= 365)) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + T2."User ID") WHEN ((not T1."No_" LIKE '4%') and ((-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",
	   T2."Branch Code" as "Sales Branch Code",
	   'Konto 46050 - LACK Allgemein' as "Description 2",
	   '' as "Order Number",
	   '' as "No+Description",
	   T2."Posting Date" as "Invoice Date",
	   'Intern' as "Umsatzart",
	   '' as "Serviceberater",
	   '' as "Monteur",
	   '' as "Function Code",
	   (((convert(float, T2."Amount"))) * -1) * -1 as "Umsatz Teile Service_Lack",
	   T1."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T1."Client_DB" = '1') THEN ('AHR') WHEN (T1."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   (CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END) as "Standort_ID",
	   CASE WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('10')) THEN ('MM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('30')) THEN ('KRU') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('40')) THEN ('ULM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('50')) THEN ('LL') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('55')) THEN ('GZ') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('60')) THEN ('AAM') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."Department Code",2))) END)) IN ('70')) THEN ('LEH') WHEN (((CASE WHEN ((T2."Branch Code" = 'MM') or (T2."Branch Code" = '')) 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') WHEN (T2."Branch Code" = 'AAM') THEN ('60') ELSE ((left(T1."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."No_" = '46050') and (T1."Income_Balance" = 0)) and (not T1."No_" IN ('42400','43150','43250','43350','43450','44220','44230','46700','47610','48020','49860','48800','45250'))) 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')))