C11/System/NAVISION/IQD/Belege/belege_sc_ersatz_fuer_reduzierung_sb.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') 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_SC_Ersatz",
	   (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_",
	   'SC Ersatzfahrzeuge' as "Bereich",
	   'SC Ersatzfahrzeug intern' as "Vstufe 1",
	   CASE WHEN ((((((convert(float, T2."Amount"))) * -1) IN (15.97,-15.97,15.98,-15.98,31.94,-31.94,47.91,-47.91,63.88,-63.88,79.85,-79.85)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN 47.90 AND 47.92)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN -47.92 AND -47.90)) THEN ('Reduzierung SB') ELSE ('KD Serviceersatzfahrzeuge') END as "Zeile mit Bez",
	   CASE WHEN ((CASE WHEN ((((((convert(float, T2."Amount"))) * -1) IN (15.97,-15.97,15.98,-15.98,31.94,-31.94,47.91,-47.91,63.88,-63.88,79.85,-79.85)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN 47.90 AND 47.92)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN -47.92 AND -47.90)) THEN ('Reduzierung SB') ELSE ('KD Serviceersatzfahrzeuge') END) = 'Reduzierung SB') THEN ('Reduz. SB' + ' - ' + T1."No_" + ' - ' + T1."Name") ELSE ('Erlös' + ' - ' + T1."No_" + ' - ' + T1."Name") END as "Konto",
	   'SEW' as "Ebene61",
	   'Umsatz SC' as "Ebene62",
	   CASE WHEN ((((((convert(float, T2."Amount"))) * -1) IN (15.97,-15.97,15.98,-15.98,31.94,-31.94,47.91,-47.91,63.88,-63.88,79.85,-79.85)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN 47.90 AND 47.92)) or ((((convert(float, T2."Amount"))) * -1) BETWEEN -47.92 AND -47.90)) THEN ('- Reduzierung SB') ELSE ('- KD Serviceersatzfahrzeuge') END as "Ebene63",
	   '' as "Ebene64",
	   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') 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 (T1."No_" IN ('88350'))) 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')))