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

select "No",
	   "Name_2",
	   "Search Description",
	   "Account Type",
	   "Department Code_2",
	   "Make Code_2",
	   "Income Balance",
	   "Last Date Modified",
	   "Entry No",
	   "G L Account No",
	   "Posting Date",
	   "Document Type",
	   "Document No",
	   "Description",
	   "Bal Account No",
	   "Department Code",
	   "Make Code",
	   "User Id",
	   "Source Code",
	   "Quantity",
	   "Reason Code",
	   "Gen Posting Type",
	   "Gen Bus Posting Group",
	   "Gen Prod Posting Group",
	   "Bal Account Type",
	   "Transaction No",
	   "Document Date",
	   "External Document No",
	   "Source Type",
	   "Source No",
	   "No Series",
	   "Branch Code",
	   "Main Area",
	   "Vin",
	   "Book No",
	   "Veh Source Code",
	   "Reposted",
	   "Corrected",
	   CASE WHEN (("No" IN ('88698','88712')) and (SUM("Betrag_alt") OVER (partition by "Book No") = 0)) THEN (0) WHEN (("No" IN ('88698','88712')) and (SUM("Betrag_alt") OVER (partition by "Book No") <> 0)) THEN (1 / COUNT("No") OVER (partition by "Book No")) ELSE (0) END as "Zulass. Stk",
	   "Reposting To Curr No",
	   "Code",
	   "Name",
	   "Posting Date" as "Jahr",
	   "Betrieb Nr",
	   "G L Account No" as "Konto Nr_ori",
	   "Betrag_alt",
	   "Amount_1",
	   "Text",
	   "Konto Nr",
	   'Selbstzulassungen mit B.' as "Vstufe 1",
	   "Zeile mit Bez",
	   "Bereich",
	   "Hauptbetrieb_ID",
	   "Hauptbetrieb_Name",
	   "Betrieb Nr" as "Standort_ID",
	   "Standort_Name"
from 
(select T2."Book No_" as "Book No",
	   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') 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') 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') 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') 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') 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') 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') 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') ELSE ((left(T1."Department Code",2))) END)) IN ('80')) THEN ('WTB') ELSE null END as "Standort_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') ELSE ((left(T1."Department Code",2))) END) as "Betrieb Nr",
	   CASE WHEN (T2."Client_DB" = '1') THEN ('AHR') WHEN (T2."Client_DB" = '2') THEN ('AAM') ELSE null END as "Hauptbetrieb_Name",
	   T2."Client_DB" as "Hauptbetrieb_ID",
	   CASE WHEN (T1."No_" IN ('88698')) THEN ('NA') ELSE ('GA') END as "Bereich",
	   CASE WHEN (T1."No_" IN ('88698')) THEN ('NA-Selbstzulassung mit B.') ELSE ('GA-Selbstzulassung mit B.') END as "Zeile mit Bez",
	   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') ELSE (T2."G_L Account No_") END as "Konto Nr",
	   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") ELSE null END as "Text",
	   (convert(float, T2."Amount")) as "Amount_1",
	   ((convert(float, T2."Amount"))) * -1 as "Betrag_alt",
	   T2."G_L Account No_" as "G L Account No",
	   T2."Posting Date" as "Posting Date",
	   T3."Name" as "Name",
	   T3."Code" as "Code",
	   T2."Reposting to curr_ No_" as "Reposting To Curr No",
	   T1."No_" as "No",
	   T2."Corrected" as "Corrected",
	   T2."Reposted" as "Reposted",
	   T2."Veh_ Source Code" as "Veh Source Code",
	   T2."VIN" as "Vin",
	   T2."Main Area" as "Main Area",
	   T2."Branch Code" as "Branch Code",
	   T2."No_ Series" as "No Series",
	   T2."Source No_" as "Source No",
	   T2."Source Type" as "Source Type",
	   T2."External Document No_" as "External Document No",
	   T2."Document Date" as "Document Date",
	   T2."Transaction No_" as "Transaction No",
	   T2."Bal_ Account Type" as "Bal Account Type",
	   T2."Gen_ Prod_ Posting Group" as "Gen Prod Posting Group",
	   T2."Gen_ Bus_ Posting Group" as "Gen Bus Posting Group",
	   T2."Gen_ Posting Type" as "Gen Posting Type",
	   T2."Reason Code" as "Reason 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."Source Code" as "Source Code",
	   T2."User ID" as "User Id",
	   T2."Make Code" as "Make Code",
	   T2."Department Code" as "Department Code",
	   T2."Bal_ Account No_" as "Bal Account No",
	   T2."Description" as "Description",
	   T2."Document No_" as "Document No",
	   T2."Document Type" as "Document Type",
	   T2."Entry No_" as "Entry No",
	   T1."Last Date Modified" as "Last Date Modified",
	   T1."Income_Balance" as "Income Balance",
	   T1."Make Code" as "Make Code_2",
	   T1."Department Code" as "Department Code_2",
	   T1."Account Type" as "Account Type",
	   T1."Search Description" as "Search Description",
	   T1."Name" as "Name_2"
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 (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'))) and (T2."Source Code" <> 'JAHRABSCH')) and (T2."Posting Date" >= convert(datetime, '2021-01-01 00:00:00.000'))) and (T1."No_" IN ('88698','88712')))
-- order by "Book No" asc
) D1