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

SELECT T1."No_" AS "No",
	T1."Name" AS "Name_2",
	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 ((- 1 * datediff(day, (getdate()), 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 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 ((year(T2."Posting Date")) <= (year((getdate()))))
		)