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

SELECT "No" AS "No",
	"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 City" AS "Bill-to City",
	"Order Date" AS "Order Date",
	"Posting Date" AS "Posting Date",
	"Location Code" AS "Location Code",
	"Department Code" AS "Department Code",
	"Make Code" AS "Make Code",
	"Salesperson Code" AS "Salesperson Code",
	"Order No_2" AS "Order No_2",
	"Gen Bus Posting Group_2" AS "Gen Bus Posting Group_2",
	"Sell-to Customer Name" AS "Sell-to Customer Name",
	"Sell-to City" AS "Sell-to City",
	"Document Date" AS "Document Date",
	"No Series" AS "No Series",
	"User Id" AS "User Id",
	"Service Order No_alt" AS "Service Order No_alt",
	"Customer Group Code" AS "Customer Group Code",
	"Branch Code" AS "Branch Code",
	"Vin" AS "Vin",
	"Model_ori" AS "Model_ori",
	"Document No" AS "Document No",
	"Line No" AS "Line No",
	"No_Pos." AS "No_Pos.",
	"Description" AS "Description",
	"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",
	"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",
	"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",
	"Service Advisor No_Archiv" AS "Service Advisor No_Archiv",
	"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",
	"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",
	"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",
	"Monteur" AS "Monteur",
	"Umsatz Lohn" AS "Umsatz Lohn",
	"Umsatz Teile Service_ori" AS "Umsatz Teile Service_ori",
	"Umsatz Sonstiges_ori" AS "Umsatz Sonstiges_ori",
	"Anzahl Datens�tze" AS "Anzahl Datens�tze",
	"Umsatz Teile Service" AS "Umsatz Teile Service",
	"Umsatz Sonstiges" AS "Umsatz Sonstiges",
	"verk Std" AS "verk Std",
	"Labor No" AS "Labor No",
	"Invoice Date" AS "Invoice Date",
	"Order Number" AS "Order Number",
	"Order Number_Rg_Ausg" AS "Order Number_Rg_Ausg",
	"DG1" AS "DG1",
	COUNT(c253) OVER (PARTITION BY "No") AS "DG2",
	("DG1" / COUNT(c253) OVER (PARTITION BY "No")) 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",
	"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",
	"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",
	"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",
	"Einsatz Teile Service" AS "Einsatz Teile Service",
	"DB1_><_EK" AS "DB1_><_EK",
	"Code_Salesperson" AS "Code_Salesperson",
	"Name_Salesperson" AS "Name_Salesperson",
	"gepl. AW-Satz" AS "gepl. AW-Satz",
	"Umsatz Lohn Plan" AS "Umsatz Lohn Plan",
	"Tage bis Rechnung_ori" AS "Tage bis Rechnung_ori",
	("Tage bis Rechnung_ori" / COUNT(c253) OVER (PARTITION BY "No")) AS "Tage bis Rechnung",
	"Service Order No_30" AS "Service Order No_30",
	"Auftragsnr." AS "Auftragsnr.",
	"RG/Auftrag/Kunde" AS "RG/Auftrag/Kunde",
	"RG/SB/Kunde/Datum" AS "RG/SB/Kunde/Datum",
	"Monat" AS "Monat",
	"Jahr" AS "Jahr",
	"Artikel / AW-Nr" AS "Artikel / AW-Nr",
	"EW Fremdl." AS "EW Fremdl.",
	"DB 1 Fremdl." AS "DB 1 Fremdl.",
	"DB 1 Fremdl. %" AS "DB 1 Fremdl. %"
FROM (
	SELECT "No",
		"Sell-to Customer No",
		"Bill-to Customer No",
		"Bill-to Name",
		"Bill-to City",
		"Order Date",
		"Posting Date",
		"Location Code",
		"Department Code",
		"Make Code",
		"Salesperson Code",
		"Order No_2",
		"Gen Bus Posting Group_2",
		"Sell-to Customer Name",
		"Sell-to City",
		"Document Date",
		"No Series",
		"User Id",
		"Service Order No_alt",
		"Customer Group Code",
		"Branch Code",
		"Vin",
		"Model_ori",
		"Document No",
		"Line No",
		"No_Pos.",
		"Description",
		"Quantity",
		"Unit Price",
		"Unit Cost (lcy)" AS "Unit Cost (lcy)",
		"Line Discount Amount",
		"Amount",
		"Gen Bus Posting Group",
		"Gen Prod Posting Group",
		"Unit Cost",
		"Order No",
		"Order Line No",
		"Order Type",
		"Item Type",
		"Vehicle Status",
		"Registration Date",
		"Mileage",
		"Service Order No",
		"Service Order Line No",
		"Labor No_2",
		"Item Group Code",
		"Service Advisor No_Archiv",
		"No_f�r_Archiv",
		"First Name_f�r_Archiv",
		"Last Name_f�r_Archiv",
		"Serviceberater",
		'1' AS "Hauptbetrieb",
		"Standort",
		"Umsatzart",
		"Fabrikat",
		"Model_ori" AS "Model",
		"Fahrzeug",
		"Marke",
		"Service Posting Group_f�r_Archiv",
		"Auftragsart",
		"Cust_Gr_Code",
		"Cust_Gr_Description",
		"Kundenart",
		"Cust_No",
		"Cust_Name",
		"Kunde",
		'' AS "Monteur",
		"Umsatz Lohn",
		"Umsatz Teile Service_ori",
		"Umsatz Sonstiges_ori",
		COUNT("No") OVER (PARTITION BY c147) AS "Anzahl Datens�tze",
		("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147)) AS "Umsatz Teile Service",
		("Umsatz Sonstiges_ori") / (COUNT("No") OVER (PARTITION BY c147)) AS "Umsatz Sonstiges",
		"verk Std",
		"Labor No",
		"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",
		"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") OVER (PARTITION BY c147)) AS "Rabatt Teile",
		'1' AS "Hauptbetrieb_ID",
		"Standort" AS "Standort_ID",
		"NL Lohn %",
		CASE 
			WHEN (
					(
						(("NL Lohn %") > 90)
						AND (("Rabatt Lohn") <> 0)
						)
					AND (c179 <> 1)
					)
				THEN ('Nachlass > 90 %')
			WHEN (
					(
						(
							(
								CASE 
									WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147))) + (("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147)))) <> 0)
										THEN ((("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147))) / ((("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147))) + (("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147)))) * 100
												)
									ELSE (0)
									END
								) > 90
							)
						AND ((("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147))) <> 0)
						)
					AND (c179 <> 1)
					)
				THEN ('Nachlass > 90 %')
			ELSE ('Nachlass < 90 %')
			END AS "Nachlass > 90 %",
		CASE 
			WHEN (((("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147))) + (("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147)))) <> 0)
				THEN ((("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147))) / ((("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147))) + (("NL Teile_ori") / (COUNT("No") OVER (PARTITION BY c147)))) * 100)
			ELSE (0)
			END AS "NL Teile %",
		'Serviceberater' AS "Zuordnung_Funktion",
		"Order_Desc_30",
		"Invoice_Desc_30",
		"Order_Desc_100",
		"Invoice_Desc_100",
		"Model_ori" AS "Model_Desc",
		"Vin" AS "Fahrgestellnummer",
		"Gen Bus Posting Group_2" AS "Customer_Group_Owner",
		'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") OVER (PARTITION BY c147))))
			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") OVER (PARTITION BY c147))))
			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") OVER (PARTITION BY c147))))
			ELSE (0)
			END AS "Mietw_Amount",
		(("Umsatz Sonstiges_ori") / (COUNT("No") OVER (PARTITION BY c147))) - (
			CASE 
				WHEN ("Gen Prod Posting Group" IN ('816_SONST', '817_SONST'))
					THEN ((("Umsatz Sonstiges_ori") / (COUNT("No") OVER (PARTITION BY c147))))
				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") OVER (PARTITION BY c147))))
				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") OVER (PARTITION BY c147))))
				ELSE (0)
				END
			) AS "Umsatz_Sonst_Rest",
		CASE 
			WHEN ("Gen Prod Posting Group" LIKE '%ART%')
				THEN ((c150) / (COUNT("No") OVER (PARTITION BY c147)))
			ELSE (0)
			END AS "Einsatz Teile Service",
		CASE 
			WHEN (
					(("Umsatz Teile Service_ori") / (COUNT("No") OVER (PARTITION BY c147))) - (
						CASE 
							WHEN ("Gen Prod Posting Group" LIKE '%ART%')
								THEN ((c150) / (COUNT("No") OVER (PARTITION BY c147)))
							ELSE (0)
							END
						) < 0
					)
				THEN ('VK < EK')
			ELSE ('VK > EK')
			END AS "DB1_><_EK",
		"Code_Salesperson",
		"Name_Salesperson",
		"gepl. AW-Satz",
		"Umsatz Lohn Plan",
		"Tage bis Rechnung_ori",
		"Order_Desc_30" AS "Service Order No_30",
		"Auftragsnr.",
		"RG/Auftrag/Kunde",
		"RG/SB/Kunde/Datum",
		"Monat",
		"Jahr",
		"Artikel / AW-Nr",
		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 ((c150) / (COUNT("No") OVER (PARTITION BY c147)))
			ELSE (0)
			END AS "EW Fremdl.",
		(
			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") OVER (PARTITION BY c147))))
				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 ((c150) / (COUNT("No") OVER (PARTITION BY c147)))
				ELSE (0)
				END
			) AS "DB 1 Fremdl.",
		CASE 
			WHEN (
					(
						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") OVER (PARTITION BY c147))))
							ELSE (0)
							END
						) <> 0
					)
				THEN (
						(
							(
								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") OVER (PARTITION BY c147))))
									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 ((c150) / (COUNT("No") OVER (PARTITION BY c147)))
									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") OVER (PARTITION BY c147))))
								ELSE (0)
								END
							) * 100
						)
			ELSE (0)
			END AS "DB 1 Fremdl. %",
		c252 AS c253
	FROM (
		SELECT T1."No_" AS "No",
			(T3."Document No_" + (((T3."Line No_")))) AS c147,
			T3."Gen_ Prod_ Posting Group" AS "Gen Prod Posting Group",
			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",
			((convert(FLOAT, T3."Quantity"))) * T3."Unit Cost (LCY)" AS c150,
			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
				) AS "Artikel / AW-Nr",
			(year(T1."Posting Date")) AS "Jahr",
			(month(T1."Posting Date")) AS "Monat",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (
										(
											(
												CASE 
													WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
														THEN ('Service')
													WHEN ((left(T1."No_", 2)) IN ('VR'))
														THEN ('Teile')
													ELSE NULL
													END
												) = 'Service'
											)
										AND (T5."Last Name" <> '')
										)
									THEN (T5."First Name" + ' ' + T5."Last Name")
								WHEN (
										(
											(
												CASE 
													WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
														THEN ('Service')
													WHEN ((left(T1."No_", 2)) IN ('VR'))
														THEN ('Teile')
													ELSE NULL
													END
												) = 'Teile'
											)
										AND (T6."Name" IS NOT NULL)
										)
									THEN (T6."Name")
								ELSE ('n.N.')
								END
							) IS NOT NULL
						)
					THEN (
							T1."No_" + ' - ' + (
								CASE 
									WHEN (
											(
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											AND (T5."Last Name" <> '')
											)
										THEN (T5."First Name" + ' ' + T5."Last Name")
									WHEN (
											(
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											AND (T6."Name" IS NOT NULL)
											)
										THEN (T6."Name")
									ELSE ('n.N.')
									END
								) + ' - ' + T8."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), day(T1."Posting Date")))
							)
				WHEN (
						(
							CASE 
								WHEN (
										(
											(
												CASE 
													WHEN ((left(T1."No_")) IN ('I', 'W'))
														THEN ('Service')
													WHEN ((left(T1."No_", 2)) IN ('VR'))
														THEN ('Teile')
													ELSE NULL
													END
												) = 'Service'
											)
										AND (T5."Last Name" <> '')
										)
									THEN (T5."First Name" + ' ' + T5."Last Name")
								WHEN (
										(
											(
												CASE 
													WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
														THEN ('Service')
													WHEN ((left(T1."No_", 2)) IN ('VR'))
														THEN ('Teile')
													ELSE NULL
													END
												) = 'Teile'
											)
										AND (T6."Name" IS NOT NULL)
										)
									THEN (T6."Name")
								ELSE ('n.N.')
								END
							) IS NULL
						)
					THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T8."Name" + ' - ' + (convert(VARCHAR(50), year(T1."Posting Date")) + '-' + convert(VARCHAR(50), month(T1."Posting Date")) + '-' + convert(VARCHAR(50), day(T1."Posting Date"))))
				ELSE NULL
				END AS "RG/SB/Kunde/Datum",
			T1."No_" + ' - ' + (
				CASE 
					WHEN (
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Service'
							)
						THEN (T1."Service Order No_")
					WHEN (
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Teile'
							)
						THEN (T1."Order No_")
					ELSE NULL
					END
				) + ' - ' + T8."Name" AS "RG/Auftrag/Kunde",
			CASE 
				WHEN (
						(
							CASE 
								WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
									THEN ('Service')
								WHEN ((left(T1."No_", 2)) IN ('VR'))
									THEN ('Teile')
								ELSE NULL
								END
							) = 'Service'
						)
					THEN (T1."Service Order No_")
				WHEN (
						(
							CASE 
								WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
									THEN ('Service')
								WHEN ((left(T1."No_", 2)) IN ('VR'))
									THEN ('Teile')
								ELSE NULL
								END
							) = 'Teile'
						)
					THEN (T1."Order No_")
				ELSE NULL
				END AS "Auftragsnr.",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
					THEN (
							(
								CASE 
									WHEN (
											((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											)
										THEN (T1."Service Order No_")
									WHEN (
											((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											)
										THEN (T1."Order No_")
									ELSE ('Auftr�ge �lter 30 Tage')
									END
								)
							)
				ELSE ('Auftr�ge �lter 30 Tage')
				END AS "Order_Desc_30",
			(- 1 * datediff(day, T1."Posting Date", T1."Order Date")) AS "Tage bis Rechnung_ori",
			(
				CASE 
					WHEN (
							(
								(T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
								OR (T9."Labor No_" IS NOT NULL)
								)
							)
						THEN (((convert(FLOAT, T3."Quantity"))))
					ELSE (0)
					END
				) * (
				CASE 
					WHEN (
							(T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
							AND (((convert(FLOAT, T3."Quantity"))) <> 0)
							)
						THEN ((convert(FLOAT, ((convert(FLOAT, T3."Unit Price"))))))
					ELSE (0)
					END
				) AS "Umsatz Lohn Plan",
			CASE 
				WHEN (
						(T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
						AND (((convert(FLOAT, T3."Quantity"))) <> 0)
						)
					THEN ((convert(FLOAT, ((convert(FLOAT, T3."Unit Price"))))))
				ELSE (0)
				END AS "gepl. AW-Satz",
			T6."Name" AS "Name_Salesperson",
			T6."Code" AS "Code_Salesperson",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%ART%')
					THEN (((convert(FLOAT, T3."Amount"))))
				ELSE (0)
				END AS "Umsatz Teile Service_ori",
			T8."Name" AS "Cust_Name",
			T8."Post Code" AS "Post Code",
			(left(T8."Post Code", 4)) AS "PLZ_4_Stelle",
			(left(T8."Post Code", 3)) AS "PLZ_3_Stelle",
			(left(T8."Post Code", 2)) AS "PLZ_2_Stelle",
			(left(T8."Post Code", 1)) AS "PLZ_1_Stelle",
			T1."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group_2",
			T2."VIN" AS "Vin",
			T2."Model" AS "Model_ori",
			CASE 
				WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 100)
					THEN (T1."No_" + ' - ' + T8."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")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											)
										THEN (T1."Service Order No_")
									WHEN (
											((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											)
										THEN (T1."Order No_")
									ELSE ('Auftr�ge �lter 30 Tage')
									END
								) + ' - ' + T8."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 (
											(
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											AND (T5."Last Name" <> '')
											)
										THEN (T5."First Name" + ' ' + T5."Last Name")
									WHEN (
											(
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											AND (T6."Name" IS NOT NULL)
											)
										THEN (T6."Name")
									ELSE ('n.N.')
									END
								) + ' - ' + T8."Name"
							)
				ELSE ('Rechnungen �lter 30 Tage')
				END AS "Invoice_Desc_30",
			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 c179,
			(
				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",
			(
				(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
					)
				) AS "Auftragsposition",
			T10."No_" + ' - ' + T10."Name" AS "Kunde_Verkaufskunde",
			CASE 
				WHEN (T1."Sell-to Customer No_" LIKE 'INT%')
					THEN ('Intern')
				ELSE (T11."Description")
				END AS "Kundenart_Verkaufskunde",
			T11."Description" AS "Cust_Group_Description_Verkaufskunde",
			T10."Name" AS "Cust_Name_Verkaufskunde",
			T10."No_" AS "Cust_No_Verkaufskunde",
			T9."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")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											)
										THEN (T1."Service Order No_")
									WHEN (
											((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											)
										THEN (T1."Order No_")
									ELSE ('Auftr�ge �lter 30 Tage')
									END
								)
							)
				ELSE NULL
				END AS "Order Number_Rg_Ausg_1",
			(
				substring(CASE 
						WHEN (
								((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
								AND (
									(
										CASE 
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) IS NOT NULL
									)
								)
							THEN (
									T1."No_" + ' - ' + (
										CASE 
											WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
												THEN ('Service')
											WHEN ((left(T1."No_", 2)) IN ('VR'))
												THEN ('Teile')
											ELSE NULL
											END
										) + ' - ' + (
										CASE 
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) + ' - ' + T8."Name"
									)
						WHEN (
								((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
								AND (
									(
										CASE 
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) IS NULL
									)
								)
							THEN (
									T1."No_" + ' - ' + (
										CASE 
											WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
												THEN ('Service')
											WHEN ((left(T1."No_", 2)) IN ('VR'))
												THEN ('Teile')
											ELSE NULL
											END
										) + ' - ' + T8."Name"
									)
						ELSE NULL
						END, 1, 100)
				) AS "Order Number_Rg_Ausg_2",
			(
				substring(CASE 
						WHEN (
								((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
								AND (
									(
										CASE 
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) IS NOT NULL
									)
								)
							THEN (
									T1."No_" + ' - ' + (
										CASE 
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) + ' - ' + T8."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 (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Service'
														)
													AND (T5."Last Name" <> '')
													)
												THEN (T5."First Name" + ' ' + T5."Last Name")
											WHEN (
													(
														(
															CASE 
																WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
																	THEN ('Service')
																WHEN ((left(T1."No_", 2)) IN ('VR'))
																	THEN ('Teile')
																ELSE NULL
																END
															) = 'Teile'
														)
													AND (T6."Name" IS NOT NULL)
													)
												THEN (T6."Name")
											ELSE ('n.N.')
											END
										) IS NULL
									)
								)
							THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T8."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, 1, 100)
				) 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")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Service'
												)
											)
										THEN (T1."Service Order No_")
									WHEN (
											((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
											AND (
												(
													CASE 
														WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
															THEN ('Service')
														WHEN ((left(T1."No_", 2)) IN ('VR'))
															THEN ('Teile')
														ELSE NULL
														END
													) = 'Teile'
												)
											)
										THEN (T1."Order No_")
									ELSE ('Auftr�ge �lter 30 Tage')
									END
								) + ' - ' + T8."Name"
							)
				ELSE ('Auftr�ge �lter 180 Tage')
				END AS "Order Number",
			T1."Posting Date" AS "Posting Date",
			T9."Labor No_" AS "Labor No",
			CASE 
				WHEN (
						(
							(T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
							OR (T9."Labor No_" IS NOT NULL)
							)
						)
					THEN (((convert(FLOAT, T3."Quantity"))))
				ELSE (0)
				END AS "verk Std",
			CASE 
				WHEN (T3."Gen_ Prod_ Posting Group" LIKE '%LOHN%')
					THEN (((convert(FLOAT, T3."Amount"))))
				ELSE (0)
				END AS "Umsatz Lohn",
			T8."No_" + ' - ' + T8."Name" AS "Kunde",
			T8."No_" AS "Cust_No",
			CASE 
				WHEN (T1."Bill-to Customer No_" LIKE 'INT%')
					THEN ('Intern')
				ELSE (T7."Description")
				END AS "Kundenart",
			T7."Description" AS "Cust_Gr_Description",
			T7."Code" AS "Cust_Gr_Code",
			CASE 
				WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
					THEN ('Service')
				WHEN ((left(T1."No_", 2)) IN ('VR'))
					THEN ('Teile')
				ELSE NULL
				END AS "Auftragsart",
			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 (
						(
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Service'
							)
						AND (T5."Last Name" <> '')
						)
					THEN (T5."First Name" + ' ' + T5."Last Name")
				WHEN (
						(
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Teile'
							)
						AND (T6."Name" IS NOT NULL)
						)
					THEN (T6."Name")
				ELSE ('n.N.')
				END AS "Serviceberater",
			T5."Last Name" AS "Last Name_f�r_Archiv",
			T5."First Name" AS "First Name_f�r_Archiv",
			T5."No_" AS "No_f�r_Archiv",
			T4."Service Advisor No_" AS "Service Advisor No_Archiv",
			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."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",
			(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_Pos.",
			T3."Line No_" AS "Line No",
			T3."Document No_" AS "Document No",
			T1."Branch Code" AS "Branch Code",
			T1."Customer Group Code" AS "Customer Group Code",
			CASE 
				WHEN (
						((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
						AND (
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Service'
							)
						)
					THEN (T1."Service Order No_")
				WHEN (
						((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
						AND (
							(
								CASE 
									WHEN ((left(T1."No_", 1)) IN ('I', 'W'))
										THEN ('Service')
									WHEN ((left(T1."No_", 2)) IN ('VR'))
										THEN ('Teile')
									ELSE NULL
									END
								) = 'Teile'
							)
						)
					THEN (T1."Order No_")
				ELSE ('Auftr�ge �lter 30 Tage')
				END AS "Service Order No_alt",
			T1."User ID" AS "User Id",
			T1."No_ Series" AS "No Series",
			T1."Document Date" AS "Document Date",
			T1."Sell-to City" AS "Sell-to City",
			T1."Sell-to Customer Name" AS "Sell-to Customer Name",
			T1."Order No_" AS "Order No_2",
			T1."Salesperson Code" AS "Salesperson Code",
			T1."Shortcut Dimension 2 Code" AS "Make Code",
			T1."Shortcut Dimension 1 Code" AS "Department Code",
			T1."Location Code" AS "Location Code",
			T1."Order Date" AS "Order Date",
			T1."Bill-to City" AS "Bill-to City",
			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",
			T1."Service Order Line No_" AS c252
		FROM (
			(
				(
					(
						(
							(
								"Gottstein7x"."dbo"."AH Gottstein$Sales Invoice Header" T1 LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Vehicle" T2 ON T1."Supply VIN" = T2."VIN"
								) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Salesperson_Purchaser" T6 ON T6."Code" = T1."Salesperson Code"
							) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Customer" T8 ON T8."No_" = T1."Bill-to Customer No_"
						) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Customer Group" T7 ON T8."Customer Group Code" = T7."Code"
					) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Customer" T10 ON T10."No_" = T1."Sell-to Customer No_"
				) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Customer Group" T11 ON T10."Customer Group Code" = T11."Code"
			),
			(
				(
					(
						"Gottstein7x"."dbo"."AH Gottstein$Sales Invoice Line" T3 LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Archived Service Header" T4 ON T3."Service Order No_" = T4."No_"
						) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee" T5 ON T4."Service Advisor No_" = T5."No_"
					) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Labor Ledger Entry" T9 ON (
						(
							(T3."Document No_" = T9."Document No_")
							AND (T3."Labor No_" = T9."Labor No_")
							)
						AND (T3."Service Order No_" = T9."Service Order No_")
						)
					AND (T3."Service Order Line No_" = T9."Service Order Line No_")
				)
		WHERE (T1."No_" = T3."Document No_")
			AND (
				(
					(
						(
							((left(T1."No_", 1)) IN ('I', 'W'))
							OR (
								((left(T1."No_", 2)) IN ('VR'))
								AND (T1."Order No_ Series" = 'VKAUF-ET')
								)
							)
						AND (NOT T3."Type" IN (0, 11, 12))
						)
					AND (T1."Posting Date" >= convert(DATETIME, '2022-01-01 00:00:00.000'))
					)
				AND (NOT T1."Service Order No_" IN ('NASISPA'))
				)
		) D1
	) D4
	-- order by "No" asc,"Line No" asc