C11/System/ARI/IQD/Service/Service_Ausgangsrechnung_ab_2011.sql

SELECT "No_3" AS "No_3",
	"Sell-to Customer No" AS "Sell-to Customer No",
	"Bill-to Customer No" AS "Bill-to Customer No",
	"Bill-to Name" AS "Bill-to Name",
	"Bill-to Address" AS "Bill-to Address",
	"Bill-to City" AS "Bill-to City",
	"Order Date" AS "Order Date",
	"Posting Date" AS "Posting Date",
	"Payment Terms Code" AS "Payment Terms Code",
	"Location Code" AS "Location Code",
	"Department Code_2" AS "Department Code_2",
	"Make Code_2" AS "Make Code_2",
	"Customer Posting Group" AS "Customer Posting Group",
	"Price Group Code" AS "Price Group Code",
	"Prices Including Vat" AS "Prices Including Vat",
	"Allow Quantity Disc" AS "Allow Quantity Disc",
	"Salesperson Code" AS "Salesperson Code",
	"Order No_2" AS "Order No_2",
	"On Hold" AS "On Hold",
	"Gen Bus Posting Group_2" AS "Gen Bus Posting Group_2",
	"Transaction Type" AS "Transaction Type",
	"Sell-to Customer Name" AS "Sell-to Customer Name",
	"Sell-to Address" AS "Sell-to Address",
	"Sell-to City" AS "Sell-to City",
	"Correction" AS "Correction",
	"Document Date" AS "Document Date",
	"External Document No" AS "External Document No",
	"Area" AS "Area",
	"Shipping Agent Code" AS "Shipping Agent Code",
	"No Series" AS "No Series",
	"Order No Series" AS "Order No Series",
	"User Id" AS "User Id",
	"Order Type_2" AS "Order Type_2",
	"Service Order No_2" AS "Service Order No_2",
	"Customer Group Code_2" AS "Customer Group Code_2",
	"Service Order Line No_2" AS "Service Order Line No_2",
	"Branch Code" AS "Branch Code",
	"Vin_2" AS "Vin_2",
	"Model_ori" AS "Model_ori",
	"Document No" AS "Document No",
	"Line No" AS "Line No",
	"No_2" AS "No_2",
	"Description" AS "Description",
	"Description 2" AS "Description 2",
	"Quantity" AS "Quantity",
	"Unit Price" AS "Unit Price",
	"Unit Cost (lcy)" AS "Unit Cost (lcy)",
	"Line Discount Amount" AS "Line Discount Amount",
	"Amount" AS "Amount",
	"Amount Including Vat" AS "Amount Including Vat",
	"Department Code" AS "Department Code",
	"Make Code" AS "Make Code",
	"Inv Discount Amount" AS "Inv Discount Amount",
	"Gen Bus Posting Group" AS "Gen Bus Posting Group",
	"Gen Prod Posting Group" AS "Gen Prod Posting Group",
	"Unit Cost" AS "Unit Cost",
	"Order No" AS "Order No",
	"Order Line No" AS "Order Line No",
	"Order Type" AS "Order Type",
	"Item Type" AS "Item Type",
	"Vin" AS "Vin",
	"Vehicle Status" AS "Vehicle Status",
	"Registration Date" AS "Registration Date",
	"Mileage" AS "Mileage",
	"Service Order No" AS "Service Order No",
	"Service Order Line No" AS "Service Order Line No",
	"Labor No_2" AS "Labor No_2",
	"Item Group Code" AS "Item Group Code",
	"Customer Group Code" AS "Customer Group Code",
	"Service Advisor No_Archiv" AS "Service Advisor No_Archiv",
	"Service Advisor No_oA" AS "Service Advisor No_oA",
	"No_f�r_Archiv" AS "No_f�r_Archiv",
	"First Name_f�r_Archiv" AS "First Name_f�r_Archiv",
	"Last Name_f�r_Archiv" AS "Last Name_f�r_Archiv",
	"No" AS "No",
	"First Name" AS "First Name",
	"Last Name" AS "Last Name",
	"Serviceberater" AS "Serviceberater",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort" AS "Standort",
	"Umsatzart" AS "Umsatzart",
	"Fabrikat" AS "Fabrikat",
	"Model" AS "Model",
	"Fahrzeug" AS "Fahrzeug",
	"Marke" AS "Marke",
	"Service Posting Group_f�r_Archiv" AS "Service Posting Group_f�r_Archiv",
	"Service Posting Group" AS "Service Posting Group",
	"Auftragsart" AS "Auftragsart",
	"Cust_Gr_Code" AS "Cust_Gr_Code",
	"Cust_Gr_Description" AS "Cust_Gr_Description",
	"Kundenart" AS "Kundenart",
	"Cust_No" AS "Cust_No",
	"Cust_Name" AS "Cust_Name",
	"Kunde" AS "Kunde",
	"Auftragsart_1" AS "Auftragsart_1",
	"Function Code" AS "Function Code",
	"Monteur" AS "Monteur",
	"Umsatz Lohn" AS "Umsatz Lohn",
	"Umsatz Teile Service_ori" AS "Umsatz Teile Service_ori",
	"Umsatz Sonstiges_ori" AS "Umsatz Sonstiges_ori",
	"Document No_Service_ledger" AS "Document No_Service_ledger",
	"No_Service_ledger" AS "No_Service_ledger",
	"Total Cost_Service_ledger" AS "Total Cost_Service_ledger",
	"Anzahl Datens�tze" AS "Anzahl Datens�tze",
	"Umsatz Teile Service" AS "Umsatz Teile Service",
	"Umsatz Sonstiges" AS "Umsatz Sonstiges",
	"Einsatz Teile Service" AS "Einsatz Teile Service",
	"verk Std" AS "verk Std",
	"Labor No" AS "Labor No",
	"Actual Time" AS "Actual Time",
	"benutzte AW" AS "benutzte AW",
	"Invoice Date" AS "Invoice Date",
	"Order Number" AS "Order Number",
	"Order Number_Rg_Ausg" AS "Order Number_Rg_Ausg",
	"DG1" AS "DG1",
	COUNT("Service Order Line No_2") OVER (PARTITION BY "No_3") AS "DG2",
	("DG1" / COUNT("Service Order Line No_2") OVER (PARTITION BY "No_3")) AS "DG",
	"Order Number_Rg_Ausg_2" AS "Order Number_Rg_Ausg_2",
	"Order Number_Rg_Ausg_1" AS "Order Number_Rg_Ausg_1",
	"Charging Group No" AS "Charging Group No",
	"ben  Zeit" AS "ben  Zeit",
	"EW Lohn Basis (Std)" AS "EW Lohn Basis (Std)",
	"EW Lohn" AS "EW Lohn",
	"Cust_No_Verkaufskunde" AS "Cust_No_Verkaufskunde",
	"Cust_Name_Verkaufskunde" AS "Cust_Name_Verkaufskunde",
	"Cust_Group_Description_Verkaufskunde" AS "Cust_Group_Description_Verkaufskunde",
	"Kundenart_Verkaufskunde" AS "Kundenart_Verkaufskunde",
	"Kunde_Verkaufskunde" AS "Kunde_Verkaufskunde",
	"Auftragsposition" AS "Auftragsposition",
	"Rabatt Lohn" AS "Rabatt Lohn",
	"NL Teile_ori" AS "NL Teile_ori",
	"Rabatt Teile" AS "Rabatt Teile",
	"Hauptbetrieb_ID" AS "Hauptbetrieb_ID",
	"Standort_ID" AS "Standort_ID",
	"NL Lohn %" AS "NL Lohn %",
	"Nachlass > 90 %" AS "Nachlass > 90 %",
	"NL Teile %" AS "NL Teile %",
	"Zuordnung_Funktion" AS "Zuordnung_Funktion",
	"Cost_Centre_ID" AS "Cost_Centre_ID",
	"Order_Desc_30" AS "Order_Desc_30",
	"Invoice_Desc_30" AS "Invoice_Desc_30",
	"Order_Desc_100" AS "Order_Desc_100",
	"Invoice_Desc_100" AS "Invoice_Desc_100",
	"Model_Desc" AS "Model_Desc",
	"Fahrgestellnummer" AS "Fahrgestellnummer",
	"Customer_Group_Owner" AS "Customer_Group_Owner",
	"Fahrzeugalter_Tage" AS "Fahrzeugalter_Tage",
	"Fahrzeugalter" AS "Fahrzeugalter",
	"FZG-Altersstaffel" AS "FZG-Altersstaffel",
	"Repair_Group_Desc" AS "Repair_Group_Desc",
	"DB1_><_EK" AS "DB1_><_EK",
	"Rechnung_Gutschrift" AS "Rechnung_Gutschrift",
	"Parts_Group_Desc" AS "Parts_Group_Desc",
	"Parts_Make_Desc" AS "Parts_Make_Desc",
	"Parts_Focus_Group" AS "Parts_Focus_Group",
	"Post Code" AS "Post Code",
	"PLZ_1_Stelle" AS "PLZ_1_Stelle",
	"PLZ_2_Stelle" AS "PLZ_2_Stelle",
	"PLZ_3_Stelle" AS "PLZ_3_Stelle",
	"PLZ_4_Stelle" AS "PLZ_4_Stelle",
	"PLZ" AS "PLZ",
	"Customer_Name_Owner" AS "Customer_Name_Owner",
	"Produktbuchungsgruppe" AS "Produktbuchungsgruppe",
	"T�V_Amount" AS "T�V_Amount",
	"FL_Lack_Amount" AS "FL_Lack_Amount",
	"Mietw_Amount" AS "Mietw_Amount",
	"Umsatz_Sonst_Rest" AS "Umsatz_Sonst_Rest"
FROM (
	SELECT "No_3",
		"Sell-to Customer No",
		"Bill-to Customer No",
		"Bill-to Name",
		"Bill-to Address",
		"Bill-to City",
		"Order Date",
		"Posting Date",
		"Payment Terms Code",
		"Location Code",
		"Department Code_2",
		"Make Code_2",
		"Customer Posting Group",
		"Price Group Code",
		"Prices Including Vat",
		"Allow Quantity Disc",
		"Salesperson Code",
		"Order No_2",
		"On Hold",
		"Gen Bus Posting Group_2",
		"Transaction Type",
		"Sell-to Customer Name",
		"Sell-to Address",
		"Sell-to City",
		"Correction",
		"Document Date",
		"External Document No",
		"Area",
		"Shipping Agent Code",
		"No Series",
		"Order No Series",
		"User Id",
		"Order Type_2",
		"Service Order No_2",
		"Customer Group Code_2",
		"Service Order Line No_2",
		"Branch Code",
		"Vin_2",
		"Model_ori",
		"Document No",
		"Line No",
		"No_2",
		"Description",
		'' AS "Description 2",
		"Quantity",
		"Unit Price",
		"Unit Cost (lcy)" AS "Unit Cost (lcy)",
		"Line Discount Amount",
		"Amount",
		"Amount Including Vat",
		"Department Code",
		"Make Code",
		"Inv Discount Amount",
		"Gen Bus Posting Group",
		"Gen Prod Posting Group",
		"Unit Cost",
		"Order No",
		"Order Line No",
		"Order Type",
		"Item Type",
		"Vin",
		"Vehicle Status",
		"Registration Date",
		"Mileage",
		"Service Order No",
		"Service Order Line No",
		"Labor No_2",
		"Item Group Code",
		"Customer Group Code",
		"Service Advisor No_Archiv",
		"Service Advisor No_oA",
		"No_f�r_Archiv",
		"First Name_f�r_Archiv",
		"Last Name_f�r_Archiv",
		"No",
		"First Name",
		"Last Name",
		"Serviceberater",
		'1' AS "Hauptbetrieb",
		"Standort",
		"Umsatzart",
		"Fabrikat",
		"Model_ori" AS "Model",
		"Fahrzeug",
		"Marke",
		"Service Posting Group_f�r_Archiv",
		"Service Posting Group",
		"Auftragsart",
		"Cust_Gr_Code",
		"Cust_Gr_Description",
		"Kundenart",
		"Cust_No",
		"Cust_Name",
		"Kunde",
		'' AS "Auftragsart_1",
		'' AS "Function Code",
		'' AS "Monteur",
		"Umsatz Lohn",
		"Umsatz Teile Service_ori",
		"Umsatz Sonstiges_ori",
		"Document No_Service_ledger",
		"No_Service_ledger",
		"Total Cost_Service_ledger",
		COUNT("No_3") OVER (PARTITION BY c174) AS "Anzahl Datens�tze",
		("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174)) AS "Umsatz Teile Service",
		("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174)) AS "Umsatz Sonstiges",
		CASE 
			WHEN (("Total Cost_Service_ledger") IS NOT NULL)
				THEN (("Total Cost_Service_ledger") / (COUNT("No_3") OVER (PARTITION BY c174)))
			ELSE (0)
			END AS "Einsatz Teile Service",
		"verk Std",
		"Labor No",
		"Actual Time",
		"benutzte AW",
		"Posting Date" AS "Invoice Date",
		"Order Number",
		"Order Number_Rg_Ausg",
		1 AS "DG1",
		"Order Number_Rg_Ausg_2",
		"Order Number_Rg_Ausg_1",
		"Charging Group No",
		"ben  Zeit",
		"EW Lohn Basis (Std)" AS "EW Lohn Basis (Std)",
		"EW Lohn",
		"Cust_No_Verkaufskunde",
		"Cust_Name_Verkaufskunde",
		"Cust_Group_Description_Verkaufskunde",
		"Kundenart_Verkaufskunde",
		"Kunde_Verkaufskunde",
		"Auftragsposition",
		"Rabatt Lohn",
		"NL Teile_ori",
		("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174)) AS "Rabatt Teile",
		'1' AS "Hauptbetrieb_ID",
		"Standort" AS "Standort_ID",
		"NL Lohn %",
		CASE 
			WHEN (
					(
						(("NL Lohn %") > 90)
						AND (("Rabatt Lohn") <> 0)
						)
					AND ("Prices Including Vat" <> 1)
					)
				THEN ('Nachlass > 90 %')
			WHEN (
					(
						(
							(
								CASE 
									WHEN (((("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) + (("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174)))) <> 0)
										THEN ((("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) / ((("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) + (("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174)))) * 100
												)
									ELSE (0)
									END
								) > 90
							)
						AND ((("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) <> 0)
						)
					AND ("Prices Including Vat" <> 1)
					)
				THEN ('Nachlass > 90 %')
			ELSE ('Nachlass < 90 %')
			END AS "Nachlass > 90 %",
		CASE 
			WHEN (((("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) + (("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174)))) <> 0)
				THEN ((("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) / ((("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) + (("NL Teile_ori") / (COUNT("No_3") OVER (PARTITION BY c174)))) * 100)
			ELSE (0)
			END AS "NL Teile %",
		'Serviceberater' AS "Zuordnung_Funktion",
		"Department Code_2" AS "Cost_Centre_ID",
		"Order_Desc_30",
		"Invoice_Desc_30",
		"Order_Desc_100",
		"Invoice_Desc_100",
		"Model_ori" AS "Model_Desc",
		"Vin_2" AS "Fahrgestellnummer",
		"Gen Bus Posting Group_2" AS "Customer_Group_Owner",
		"Fahrzeugalter_Tage",
		"Fahrzeugalter",
		"FZG-Altersstaffel",
		"Repair_Group_Desc",
		CASE 
			WHEN (
					(("Umsatz Teile Service_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) - (
						CASE 
							WHEN (("Total Cost_Service_ledger") IS NOT NULL)
								THEN (("Total Cost_Service_ledger") / (COUNT("No_3") OVER (PARTITION BY c174)))
							ELSE (0)
							END
						) < 0
					)
				THEN ('VK < EK')
			ELSE ('VK > EK')
			END AS "DB1_><_EK",
		'Rechnung' AS "Rechnung_Gutschrift",
		'' AS "Parts_Group_Desc",
		'' AS "Parts_Make_Desc",
		'' AS "Parts_Focus_Group",
		"Post Code",
		"PLZ_1_Stelle",
		"PLZ_2_Stelle",
		"PLZ_3_Stelle",
		"PLZ_4_Stelle",
		"Post Code" AS "PLZ",
		"Cust_Name" AS "Customer_Name_Owner",
		"Gen Prod Posting Group" AS "Produktbuchungsgruppe",
		CASE 
			WHEN ("Gen Prod Posting Group" IN ('816_SONST', '817_SONST'))
				THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
			ELSE (0)
			END AS "T�V_Amount",
		CASE 
			WHEN ("Gen Prod Posting Group" IN ('821_SONST', '823_SONST', '828_SONST', '822_SONST', '824_SONST', '825_SONST', '826_SONST', '827_SONST', '829_SONST'))
				THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
			ELSE (0)
			END AS "FL_Lack_Amount",
		CASE 
			WHEN ("Gen Prod Posting Group" IN ('881_SONST', '886_SONST', '818_SONST', '819_SONST', '820_SONST'))
				THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
			ELSE (0)
			END AS "Mietw_Amount",
		(("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))) - (
			CASE 
				WHEN ("Gen Prod Posting Group" IN ('816_SONST', '817_SONST'))
					THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
				ELSE (0)
				END
			) - (
			CASE 
				WHEN ("Gen Prod Posting Group" IN ('821_SONST', '823_SONST', '828_SONST', '822_SONST', '824_SONST', '825_SONST', '826_SONST', '827_SONST', '829_SONST'))
					THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
				ELSE (0)
				END
			) - (
			CASE 
				WHEN ("Gen Prod Posting Group" IN ('881_SONST', '886_SONST', '818_SONST', '819_SONST', '820_SONST'))
					THEN ((("Umsatz Sonstiges_ori") / (COUNT("No_3") OVER (PARTITION BY c174))))
				ELSE (0)
				END
			) AS "Umsatz_Sonst_Rest"
	FROM (
		SELECT T1."No_" AS "No_3",
			(T3."Document No_" + (((T3."Line No_")))) AS c174,
			CASE 
				WHEN (
						(T3."Gen_ Prod_ Posting Group" LIKE '%FZG%')
						OR (T3."Gen_ Prod_ Posting Group" LIKE '%SONST%')
						)
					THEN (((convert(FLOAT, T3."Amount"))))
				ELSE (0)
				END AS "Umsatz Sonstiges_ori",
			T3."Gen_ Prod_ Posting Group" AS "Gen Prod Posting Group",
			T9."Name" AS "Cust_Name",
			T9."Post Code" AS "Post Code",
			(left(T9."Post Code", 4)) AS "PLZ_4_Stelle",
			(left(T9."Post Code", 3)) AS "PLZ_3_Stelle",
			(left(T9."Post Code", 2)) AS "PLZ_2_Stelle",
			(left(T9."Post Code", 1)) AS "PLZ_1_Stelle",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%')
					THEN (((convert(FLOAT, T3."Amount"))))
				ELSE (0)
				END AS "Umsatz Teile Service_ori",
			(convert(FLOAT, T10."Total Cost")) AS "Total Cost_Service_ledger",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
					THEN (T3."Gen_ Prod_ Posting Group")
				ELSE NULL
				END AS "Repair_Group_Desc",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 0.01 AND 0.99
						)
					THEN ('1')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 1.00 AND 1.99
						)
					THEN ('2')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 2.00 AND 2.99
						)
					THEN ('3')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 3.00 AND 3.99
						)
					THEN ('4')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 4.00 AND 4.99
						)
					THEN ('5')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 5.00 AND 5.99
						)
					THEN ('6')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 6.00 AND 6.99
						)
					THEN ('7')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 7.00 AND 7.99
						)
					THEN ('8')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 8.00 AND 8.99
						)
					THEN ('9')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 BETWEEN 9.00 AND 9.99
						)
					THEN ('10')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 > 9.99
						)
					THEN ('> 10')
				WHEN (
						(
							CASE 
								WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
								ELSE (0)
								END
							) / 365 = 0
						)
					THEN ('keine Angabe')
				ELSE NULL
				END AS "FZG-Altersstaffel",
			(
				CASE 
					WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
						THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
					ELSE (0)
					END
				) / 365 AS "Fahrzeugalter",
			CASE 
				WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
					THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
				ELSE (0)
				END AS "Fahrzeugalter_Tage",
			T1."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group_2",
			T2."VIN" AS "Vin_2",
			T2."Model" AS "Model_ori",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 100)
					THEN (T1."No_" + ' - ' + T9."Name")
				ELSE ('Rechnungen �lter 100 Tage')
				END AS "Invoice_Desc_100",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 100)
					THEN (
							(
								CASE 
									WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
										THEN (T1."Service Order No_")
									ELSE ('Auftr�ge �lter 60 Tage')
									END
								) + ' - ' + T9."Name"
							)
				ELSE ('Auftr�ge �lter 100 Tage')
				END AS "Order_Desc_100",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) + ' - ' + T9."Name"
							)
				ELSE ('Rechnungen �lter 30 Tage')
				END AS "Invoice_Desc_30",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
					THEN (
							(
								CASE 
									WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
										THEN (T1."Service Order No_")
									ELSE ('Auftr�ge �lter 60 Tage')
									END
								)
							)
				ELSE ('Auftr�ge �lter 30 Tage')
				END AS "Order_Desc_30",
			T1."Shortcut Dimension 1 Code" AS "Department Code_2",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%')
					THEN (((convert(FLOAT, T3."Line Discount Amount"))))
				ELSE (0)
				END AS "NL Teile_ori",
			CASE 
				WHEN (
						(
							(
								CASE 
									WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
										THEN (((convert(FLOAT, T3."Amount"))))
									ELSE (0)
									END
								) + (
								CASE 
									WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
										THEN (((convert(FLOAT, T3."Line Discount Amount"))))
									ELSE (0)
									END
								)
							) <> 0
						)
					THEN (
							(
								CASE 
									WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
										THEN (((convert(FLOAT, T3."Line Discount Amount"))))
									ELSE (0)
									END
								) / (
								(
									CASE 
										WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
											THEN (((convert(FLOAT, T3."Amount"))))
										ELSE (0)
										END
									) + (
									CASE 
										WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
											THEN (((convert(FLOAT, T3."Line Discount Amount"))))
										ELSE (0)
										END
									)
								) * 100
							)
				ELSE (0)
				END AS "NL Lohn %",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
					THEN (((convert(FLOAT, T3."Line Discount Amount"))))
				ELSE (0)
				END AS "Rabatt Lohn",
			T1."Prices Including VAT" AS "Prices Including Vat",
			(
				CASE 
					WHEN (T1."Location Code" IN ('01BSPKW'))
						THEN ('10')
					WHEN (T1."Location Code" IN ('02BSMOT'))
						THEN ('20')
					WHEN (T1."Location Code" IN ('03RHF'))
						THEN ('30')
					WHEN (T1."Location Code" IN ('04SFH'))
						THEN ('40')
					WHEN (T1."Location Code" IN ('05WT'))
						THEN ('50')
					WHEN (T1."Location Code" IN ('06BI'))
						THEN ('60')
					WHEN (T1."Location Code" IN ('07TR'))
						THEN ('70')
					ELSE NULL
					END
				) AS "Standort",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (
										((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
										AND (
											(
												CASE 
													WHEN (T6."No_" IS NULL)
														THEN (T7."First Name" + ' ' + T7."Last Name")
													ELSE (T6."First Name" + ' ' + T6."Last Name")
													END
												) IS NOT NULL
											)
										)
									THEN (
											T1."No_" + ' - ' + (
												CASE 
													WHEN (T6."No_" IS NULL)
														THEN (T7."First Name" + ' ' + T7."Last Name")
													ELSE (T6."First Name" + ' ' + T6."Last Name")
													END
												) + ' - ' + T9."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), - 1 * datediff(day, T1."Posting Date")))
											)
								WHEN (
										((day((getdate()), T1."Posting Date")) <= 30)
										AND (
											(
												CASE 
													WHEN (T6."No_" IS NULL)
														THEN (T7."First Name" + ' ' + T7."Last Name")
													ELSE (T6."First Name" + ' ' + T6."Last Name")
													END
												) IS NULL
											)
										)
									THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), day(T1."Posting Date"))))
								ELSE ('Rechnungen �lter 30 Tage')
								END
							) <> 'Rechnungen �lter 30 Tage'
						)
					THEN (
							(rtrim((((T3."Line No_"))))) + ' - ' + T3."No_" + ' - ' + (
								CASE 
									WHEN (T3."Description" LIKE '%;%')
										THEN ((substring(pack(T3."Description") FROM 1 FOR POSITION(' ' IN pack(T3."Description") + ' ') - 1)))
									ELSE (T3."Description")
									END
								)
							)
				ELSE ('Rechnungen �lter 30 Tage')
				END AS "Auftragsposition",
			T12."No_" + ' - ' + T12."Name" AS "Kunde_Verkaufskunde",
			CASE 
				WHEN (T1."Sell-to Customer No_" LIKE 'INT%')
					THEN ('Intern')
				ELSE (T13."Description")
				END AS "Kundenart_Verkaufskunde",
			T13."Description" AS "Cust_Group_Description_Verkaufskunde",
			T12."Name" AS "Cust_Name_Verkaufskunde",
			T12."No_" AS "Cust_No_Verkaufskunde",
			((((convert(FLOAT, T11."Actual Time"))) * 12) / 12) * (
				CASE 
					WHEN (T11."Charging Group No_" = 'MECH')
						THEN (40)
					WHEN (T11."Charging Group No_" = 'ELEK')
						THEN (40)
					WHEN (T11."Charging Group No_" = 'KARO')
						THEN (42)
					ELSE (0)
					END
				) AS "EW Lohn",
			CASE 
				WHEN (T11."Charging Group No_" = 'MECH')
					THEN (40)
				WHEN (T11."Charging Group No_" = 'ELEK')
					THEN (40)
				WHEN (T11."Charging Group No_" = 'KARO')
					THEN (42)
				ELSE (0)
				END AS "EW Lohn Basis (Std)",
			(((convert(FLOAT, T11."Actual Time"))) * 12) / 12 AS "ben  Zeit",
			T11."Charging Group No_" AS "Charging Group No",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
					THEN (
							(
								CASE 
									WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
										THEN (T1."Service Order No_")
									ELSE ('Auftr�ge �lter 60 Tage')
									END
								)
							)
				ELSE NULL
				END AS "Order Number_Rg_Ausg_1",
			CASE 
				WHEN (
						((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
						AND (
							(
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) IS NOT NULL
							)
						)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN (T4."Service Posting Group" IS NULL)
										THEN (T5."Service Posting Group")
									ELSE (T4."Service Posting Group")
									END
								) + ' - ' + (
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) + ' - ' + T9."Name"
							)
				WHEN (
						((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
						AND (
							(
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) IS NULL
							)
						)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN (T4."Service Posting Group" IS NULL)
										THEN (T5."Service Posting Group")
									ELSE (T4."Service Posting Group")
									END
								) + ' - ' + T9."Name"
							)
				ELSE NULL
				END AS "Order Number_Rg_Ausg_2",
			CASE 
				WHEN (
						((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
						AND (
							(
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) IS NOT NULL
							)
						)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) + ' - ' + T9."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), - 1 * datediff(day, T1."Posting Date")))
							)
				WHEN (
						((day((getdate()), T1."Posting Date")) <= 30)
						AND (
							(
								CASE 
									WHEN (T6."No_" IS NULL)
										THEN (T7."First Name" + ' ' + T7."Last Name")
									ELSE (T6."First Name" + ' ' + T6."Last Name")
									END
								) IS NULL
							)
						)
					THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), day(T1."Posting Date"))))
				ELSE ('Rechnungen �lter 30 Tage')
				END AS "Order Number_Rg_Ausg",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 180)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
										THEN (T1."Service Order No_")
									ELSE ('Auftr�ge �lter 60 Tage')
									END
								) + ' - ' + T9."Name"
							)
				ELSE ('Auftr�ge �lter 180 Tage')
				END AS "Order Number",
			T1."Posting Date" AS "Posting Date",
			((convert(FLOAT, T11."Actual Time"))) * 12 AS "benutzte AW",
			(convert(FLOAT, T11."Actual Time")) AS "Actual Time",
			T11."Labor No_" AS "Labor No",
			CASE 
				WHEN (
						(
							(T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
							OR (T11."Labor No_" IS NOT NULL)
							)
						AND (NOT T3."Document No_" + (((T3."Line No_"))) IN ('WRG2320009840000'))
						)
					THEN (((convert(FLOAT, T3."Quantity"))))
				ELSE (0)
				END AS "verk Std",
			T10."No_" AS "No_Service_ledger",
			T10."Document No_" AS "Document No_Service_ledger",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
					THEN (((convert(FLOAT, T3."Amount"))))
				ELSE (0)
				END AS "Umsatz Lohn",
			T9."No_" + ' - ' + T9."Name" AS "Kunde",
			T9."No_" AS "Cust_No",
			CASE 
				WHEN (T1."Bill-to Customer No_" LIKE 'INT%')
					THEN ('Intern')
				ELSE (T8."Description")
				END AS "Kundenart",
			T8."Description" AS "Cust_Gr_Description",
			T8."Code" AS "Cust_Gr_Code",
			CASE 
				WHEN (T4."Service Posting Group" IS NULL)
					THEN (T5."Service Posting Group")
				ELSE (T4."Service Posting Group")
				END AS "Auftragsart",
			T5."Service Posting Group" AS "Service Posting Group",
			T4."Service Posting Group" AS "Service Posting Group_f�r_Archiv",
			CASE 
				WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW', 'BMW I', 'BMW-C1', 'BMWI', 'BMW-MINI', 'BMW-MOT', 'BMWVW'))
					THEN (T1."Shortcut Dimension 2 Code")
				ELSE ('Fremdfabrikat')
				END AS "Marke",
			T2."VIN" + ' - ' + T2."Model" AS "Fahrzeug",
			CASE 
				WHEN (T1."Shortcut Dimension 2 Code" IN ('BMW', 'BMW I', 'BMW-C1', 'BMWI', 'BMW-MINI', 'BMW-MOT', 'BMW-ALPINA', 'TRIUMPH'))
					THEN (T1."Shortcut Dimension 2 Code")
				ELSE ('Fremdfabrikat')
				END AS "Fabrikat",
			CASE 
				WHEN (T1."Customer Posting Group" IN ('PKW_GWL'))
					THEN ('GWL')
				WHEN (T1."No_ Series" LIKE 'I%')
					THEN ('Intern')
				ELSE ('Extern')
				END AS "Umsatzart",
			CASE 
				WHEN (T6."No_" IS NULL)
					THEN (T7."First Name" + ' ' + T7."Last Name")
				ELSE (T6."First Name" + ' ' + T6."Last Name")
				END AS "Serviceberater",
			T7."Last Name" AS "Last Name",
			T7."First Name" AS "First Name",
			T7."No_" AS "No",
			T6."Last Name" AS "Last Name_f�r_Archiv",
			T6."First Name" AS "First Name_f�r_Archiv",
			T6."No_" AS "No_f�r_Archiv",
			T5."Service Advisor No_" AS "Service Advisor No_oA",
			T4."Service Advisor No_" AS "Service Advisor No_Archiv",
			T3."Customer Group Code" AS "Customer Group Code",
			T3."Item Group Code" AS "Item Group Code",
			T3."Labor No_" AS "Labor No_2",
			T3."Service Order Line No_" AS "Service Order Line No",
			T3."Service Order No_" AS "Service Order No",
			T3."Mileage" AS "Mileage",
			T3."Registration Date" AS "Registration Date",
			T3."Vehicle Status" AS "Vehicle Status",
			T3."VIN" AS "Vin",
			T3."Item Type" AS "Item Type",
			T3."Order Type" AS "Order Type",
			T3."Order Line No_" AS "Order Line No",
			T3."Order No_" AS "Order No",
			T3."Unit Cost" AS "Unit Cost",
			T3."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group",
			T3."Inv_ Discount Amount" AS "Inv Discount Amount",
			T3."Shortcut Dimension 2 Code" AS "Make Code",
			T3."Shortcut Dimension 1 Code" AS "Department Code",
			T3."Amount Including VAT" AS "Amount Including Vat",
			(convert(FLOAT, T3."Amount")) AS "Amount",
			(convert(FLOAT, T3."Line Discount Amount")) AS "Line Discount Amount",
			T3."Unit Cost (LCY)" AS "Unit Cost (lcy)",
			(convert(FLOAT, T3."Unit Price")) AS "Unit Price",
			(convert(FLOAT, T3."Quantity")) AS "Quantity",
			CASE 
				WHEN (T3."Description" LIKE '%;%')
					THEN ((substring(pack(T3."Description") FROM 1 FOR POSITION(' ' IN pack(T3."Description") + ' ') - 1)))
				ELSE (T3."Description")
				END AS "Description",
			T3."No_" AS "No_2",
			T3."Line No_" AS "Line No",
			T3."Document No_" AS "Document No",
			T1."Branch Code" AS "Branch Code",
			T1."Service Order Line No_" AS "Service Order Line No_2",
			T1."Customer Group Code" AS "Customer Group Code_2",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
					THEN (T1."Service Order No_")
				ELSE ('Auftr�ge �lter 60 Tage')
				END AS "Service Order No_2",
			T1."Order Type" AS "Order Type_2",
			T1."User ID" AS "User Id",
			T1."Order No_ Series" AS "Order No Series",
			T1."No_ Series" AS "No Series",
			T1."Shipping Agent Code" AS "Shipping Agent Code",
			T1."Area" AS "Area",
			T1."External Document No_" AS "External Document No",
			T1."Document Date" AS "Document Date",
			T1."Correction" AS "Correction",
			T1."Sell-to City" AS "Sell-to City",
			T1."Sell-to Address" AS "Sell-to Address",
			T1."Sell-to Customer Name" AS "Sell-to Customer Name",
			T1."Transaction Type" AS "Transaction Type",
			T1."On Hold" AS "On Hold",
			T1."Order No_" AS "Order No_2",
			T1."Salesperson Code" AS "Salesperson Code",
			T1."Allow Quantity Disc_" AS "Allow Quantity Disc",
			T1."Price Group Code" AS "Price Group Code",
			T1."Customer Posting Group" AS "Customer Posting Group",
			T1."Shortcut Dimension 2 Code" AS "Make Code_2",
			T1."Location Code" AS "Location Code",
			T1."Payment Terms Code" AS "Payment Terms Code",
			T1."Order Date" AS "Order Date",
			T1."Bill-to City" AS "Bill-to City",
			T1."Bill-to Address" AS "Bill-to Address",
			T1."Bill-to Name" AS "Bill-to Name",
			T1."Bill-to Customer No_" AS "Bill-to Customer No",
			T1."Sell-to Customer No_" AS "Sell-to Customer No"
		FROM (
			(
				(
					(
						(
							"ARI"."import"."Sales Invoice Header" T1 LEFT JOIN "ARI"."import"."Vehicle" T2 ON T1."Supply VIN" = T2."VIN"
							) LEFT JOIN "ARI"."import"."Customer" T9 ON T9."No_" = T1."Bill-to Customer No_"
						) LEFT JOIN "ARI"."import"."Customer Group" T8 ON T9."Customer Group Code" = T8."Code"
					) LEFT JOIN "ARI"."import"."Customer" T12 ON T12."No_" = T1."Sell-to Customer No_"
				) LEFT JOIN "ARI"."import"."Customer Group" T13 ON T12."Customer Group Code" = T13."Code"
			),
			(
				(
					(
						(
							(
								(
									"ARI"."import"."Sales Invoice Line" T3 LEFT JOIN "ARI"."import"."Archived Service Header" T4 ON T3."Service Order No_" = T4."No_"
									) LEFT JOIN "ARI"."import"."Service Header" T5 ON T3."Service Order No_" = T5."No_"
								) LEFT JOIN "ARI"."import"."Employee" T6 ON T4."Service Advisor No_" = T6."No_"
							) LEFT JOIN "ARI"."import"."Employee" T7 ON T5."Service Advisor No_" = T7."No_"
						) LEFT JOIN "ARI"."import"."Service Ledger Entry" T10 ON (
							(T3."Document No_" = T10."Document No_")
							AND (T3."Type" = 2)
							)
						AND (T3."No_" = T10."No_")
					) LEFT JOIN "ARI"."import"."Labor Ledger Entry" T11 ON (
						(
							(T3."Document No_" = T11."Document No_")
							AND (T3."Labor No_" = T11."Labor No_")
							)
						AND (T3."Service Order No_" = T11."Service Order No_")
						)
					AND (T3."Service Order Line No_" = T11."Service Order Line No_")
				)
		WHERE (T1."No_" = T3."Document No_")
			AND (
				(
					(
						((left(T1."No_", 1)) IN ('I', 'W'))
						AND (NOT T3."Type" IN (0, 11, 12))
						)
					AND (T1."Posting Date" >= convert(DATETIME, '2024-01-01 00:00:00.000'))
					)
				AND (
					NOT (
						CASE 
							WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
								THEN (T1."Service Order No_")
							ELSE ('Auftr�ge �lter 60 Tage')
							END
						) IN ('NASISPA')
					)
				)
		) D1
	) D4
	-- order by "No_3" asc,"Line No" asc