C11/System/NAVISION/IQD/Belege/belege_mit_abgrenzung_na_flotte.sql

select T1."No_" as "No",
	   T1."Name" as "Name_2",
	   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",
	   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" = 'GÖG') or (T2."Branch Code" = 'AAM')) THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE ((left(T1."Department Code",2))) END as "Betrieb Nr",
	   T2."G_L Account No_" as "Konto Nr_ori",
	   ((convert(float, T2."Amount"))) * -1 as "Betrag",
	   (convert(float, T2."Amount")) as "Amount_1",
	   CASE WHEN (((T1."No_" LIKE '4%') or (T1."No_" LIKE '2%')) 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 (not T1."No_" LIKE '2%')) and ((-1 * datediff(day, (getdate()), T2."Posting Date")) <= 30)) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + T2."User ID") WHEN (((not T1."No_" LIKE '4%') and (not T1."No_" LIKE '2%')) and ((-1 * datediff(day, (getdate()), T2."Posting Date")) > 30)) THEN ('Belege älter 30 Tage') ELSE null END as "Text",
	   CASE WHEN ((T2."G_L Account No_" IN ('88200','88201','88202')) and (T2."Posting Date" <= convert(datetime, '2015-04-30 00:00:00.000'))) THEN (T2."G_L Account No_" + '_NA') WHEN ((T2."G_L Account No_" IN ('80000','80082','80083','80084','80090','87500','87510','80060','80070','80900','80982','80983','80984','80990','87520','87530','80160','80170','80960','80970')) and (T2."Make Code" = 'BMW-MINI')) THEN (T2."G_L Account No_" + '_MINI') ELSE (T2."G_L Account No_") END as "Konto Nr",
	   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') 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" = 'GÖG') or (T2."Branch Code" = 'AAM')) THEN ('60') WHEN (T2."Branch Code" = 'LEH') THEN ('70') WHEN (T2."Branch Code" = 'WTB') THEN ('80') ELSE ((left(T1."Department Code",2))) END) as "Standort_ID",
	   CASE WHEN (T2."Branch Code" IN ('GÖG','AAM')) THEN ('AAM') ELSE (T2."Branch Code") 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 (T2."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and (T2."Source Code" <> 'JAHRABSCH'))