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" IN ('01BSPKW'))
			THEN ('10')
		WHEN (T2."Branch Code" IN ('02BSMOT'))
			THEN ('20')
		WHEN (T2."Branch Code" IN ('03RHF'))
			THEN ('30')
		WHEN (T2."Branch Code" IN ('04SFH'))
			THEN ('40')
		WHEN (T2."Branch Code" IN ('05WT'))
			THEN ('50')
		WHEN (T2."Branch Code" IN ('06BI'))
			THEN ('60')
		WHEN (T2."Branch Code" IN ('07TR'))
			THEN ('70')
		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 "Gottstein7x"."dbo"."AH Gottstein$G_L Account" T1,
	(
		"Gottstein7x"."dbo"."AH Gottstein$G_L Entry" T2 LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$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()))))
		)