C11/System/ARI/IQD/Belege/Belege.sql

select T1."No_" as "No",
	   T1."Name" as "Name",
	   T1."Account Type" as "Account Type",
	   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."Global Dimension 1 Code" as "Department Code",
	   T2."Global Dimension 2 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 "Bookkeep Date",
	   CASE WHEN (T2."Branch Code" = 'BUR') THEN ('10') WHEN (T2."Branch Code" = 'MUE') THEN ('20') ELSE ((left(T2."Global Dimension 1 Code",2))) END as "Betrieb",
	   T2."G_L Account No_" as "Acct Nr",
	   ((convert(float, T2."Amount"))) as "Betrag",
	   (convert(float, T2."Amount")) as "Amount_1",
	   CASE WHEN ((day((now()) - T2."Posting Date")) <= 120) THEN (T2."Document No_" + ' - ' + T2."Description" + ' - ' + T2."User ID") ELSE ('Belege älter 120 Tage') END as "Text",
	   '1' as "Hauptbetrieb",
	   CASE WHEN (T2."Global Dimension 2 Code" IN ('BMW','BMW I','BMW-C1','BMWI','BMW-MINI','BMW-MOT','BMWVW')) THEN (T2."Global Dimension 2 Code") ELSE ('Fremdfabrikat') END as "Marke"
from "Vogl7x"."dbo"."BMW AH Vogl$G_L Account" T1,
	("Vogl7x"."dbo"."BMW AH Vogl$G_L Entry" T2 left outer join "Vogl7x"."dbo"."BMW AH Vogl$Department" T3 on T2."Global Dimension 1 Code" = T3."Code")
where (T1."No_" = T2."G_L Account No_")
and ((((T1."Income_Balance" = 0) and (not T2."Description" IN ('GuV-Konten Nullstellung','GuV Konten Nullstellung'))) and (T2."Posting Date" >= convert(datetime, '2020-01-01 00:00:00.000'))) and ((od_year(T2."Posting Date")) <= (od_year((now())))))