C11/System/OPTIMA/IQD/Serv_Teile/auftraege_spp_split_csv.sql

SELECT "Order Number_ori",
	"Register Number",
	"Status",
	"State Key Date",
	"Debit Account",
	"Invoice Number",
	"Workshop Model",
	"State Code",
	"Transact Date",
	"Handler",
	"Delivery Account",
	"Department_2",
	"Debet Department",
	"Salesman_2",
	"Debit Perm",
	"Order Date",
	"Delivery Date",
	"Invoice Date",
	"Pmt Term",
	"Duedate 1",
	"Payment Text",
	"Costs",
	"Purch Tax",
	"Orders Grossvalue",
	"Tax Share",
	"Discount Amount",
	"Price Code",
	"Misc Adds",
	"Stock",
	"Place Code",
	"Internal Code",
	"Customer Group_2",
	"Basis Number",
	"Mileage",
	"Prev Status",
	"Sales Class Number",
	"Invoice Disc Perc",
	"Sales Tax Free",
	"Title",
	"Name",
	"Street Addr",
	"Addr 2",
	"Zipcode",
	"Mail Addr",
	"Discount Limit",
	"Reference Number",
	"Expected Order Tim",
	'' AS "Model Text",
	"Workshop Pricecode",
	"Split Counter",
	"Arrival Time",
	"Arrival Date",
	"End Date",
	"End Time",
	"Fac Model Code S",
	"Make Cd_2",
	"Year Model",
	"Transfer Make Cd",
	"Chassis Number",
	"Workshop Team",
	"Commission Salesman",
	"Actual Inv Date Time",
	"Unique Ident_2",
	"Order Number_ori2",
	"Line Number",
	"Order Linetype",
	"Reduction Code",
	"Reduction Amount",
	"Mechanic Code",
	"Salesman",
	"Discount",
	"Stdprice",
	"Lines Net Value",
	"Prod Code",
	"Make Cd_Position",
	"Product Group",
	"Prod Name",
	"Order Quantity",
	"Delivery Quantity",
	"Line Costs",
	"Repair Code",
	"Repair Group_2",
	"Repair Name",
	"Used Time",
	"Est Time",
	"Inv Time",
	"Used Time Int",
	"Est Time Int",
	"Inv Time Int",
	"Make Time Unit",
	"Unique Ident",
	"Stat Code",
	"Stat Specify",
	"Department Type Id",
	"Description_2",
	"Seller Code_2",
	"Sel Name",
	"Sel Department_2",
	"Sel First Name",
	"Sel Family Name",
	"Customer Group",
	"Cust Group Specify",
	"Seller Code",
	"Sel Name_Monteur",
	"Sel Department",
	"Sel First Name_Monteur",
	"Sel Family Name_Monteur",
	"Repair Group",
	"Make Cd",
	"Repair Grp Specify",
	"Hauptbetrieb",
	"Standort",
	"Marke",
	"Kostenstelle",
	"Kundenart",
	"Umsatzart",
	"Gesch�ftsart",
	"Serviceberater",
	"Licence Id",
	"Sel Name_Monteur" AS "Monteur",
	"Auftragsart",
	"Auftragsstatus",
	"Umsatz Teile Service",
	"Umsatz Lohn",
	"Umsatz Teile (nur Teile)" AS "Umsatz Teile (nur Teile)",
	"Status_1",
	"verk. Stunden_vor_Split",
	"Est Time Int" AS "Soll-Stunden (Auftrag)",
	"Used Time Int" AS "benutzte Zeit (Auftrag)_vor_Split",
	"Umsatz Sonstiges",
	"verk. AW_vor_Split",
	"Kunde",
	"Soll AW",
	"benutzte AW_vor_Split",
	"verk. Stunden",
	"benutzte Zeit (Auftrag)" AS "benutzte Zeit (Auftrag)",
	12 AS "AW_Faktor",
	"Global Make Cd",
	"Einsatz Teile Service",
	"Einsatz Teile (nur Teile)" AS "Einsatz Teile (nur Teile)",
	"Description",
	"Fabrikat_ori",
	"Model",
	"Fahrzeug",
	"Einsatz Teile Service_vor_Split",
	"Einsatz Teile (nur Teile)_vor_Split" AS "Einsatz Teile (nur Teile)_vor_Split",
	"Rechnung/Gutschrift",
	"DB",
	"DB 1 Teile SC",
	"DB 1 Teile T",
	"VK < EK",
	"Mandant",
	"Order Number",
	"Order Number Rg_Ausgang",
	"verk. AW",
	"benutzte AW",
	"Fabrikat",
	"First Reg Date",
	"Fahrzeugalter_Tage",
	"Fahrzeugalter_Jahr",
	"FZG-Altersstaffel",
	"Nachlass",
	"Rabatt Teile",
	"Rabatt Lohn",
	"Preiscode",
	1 AS "DG1",
	COUNT("Status") OVER (PARTITION BY "Order Number_ori") AS "Anzahl_Datens�tze",
	1 / (COUNT("Status") OVER (PARTITION BY "Order Number_ori")) AS "DG",
	"Order_Desc_100",
	"Order_Desc_30_alt",
	"Invoice_Desc",
	"Invoice_Desc_30",
	"Repair Grp Specify" AS "Repair_Group_Desc",
	"PLZ_1_Stelle",
	"PLZ_2_Stelle",
	"PLZ_3_Stelle",
	"PLZ_4_Stelle",
	"Zipcode" AS "PLZ",
	"Order_Desc_30",
	"Auftragsposition",
	"Hauptbetrieb_ID",
	"Hauptbetrieb_Name",
	"Standort_ID",
	"Standort_Name",
	"Department_2" AS "Department"
FROM (
	SELECT T1."ORDER_NUMBER" AS "Order Number_ori",
		T1."DEPARTMENT" AS "Department_2",
		T14."Standort_Name" AS "Standort_Name",
		T14."Standort_ID" AS "Standort_ID",
		T14."Hauptbetrieb_Name" AS "Hauptbetrieb_Name",
		CASE 
			WHEN (T14."Hauptbetrieb_ID" IS NULL)
				THEN ('1')
			ELSE (T14."Hauptbetrieb_ID")
			END AS "Hauptbetrieb_ID",
		CASE 
			WHEN (
					((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
					AND (T3."ORDER_LINETYPE" = '4')
					)
				THEN (
						(rtrim((((T3."LINE_NUMBER"))))) + ' - ' + T3."REPAIR_CODE" + ' ' + (
							CASE 
								WHEN (T3."REPAIR_NAME" LIKE '%;%')
									THEN ('ung�ltiger Repair Name')
								ELSE (T3."REPAIR_NAME")
								END
							)
						)
			WHEN (
					((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
					AND (T3."ORDER_LINETYPE" <> '4')
					)
				THEN ((rtrim((((T3."LINE_NUMBER"))))) + ' - ' + T3."PROD_CODE" + ' ' + T3."PROD_NAME")
			ELSE ('Auftr�ge �lter 30 Tage')
			END AS "Auftragsposition",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
				THEN (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (
														(T2."SEL_FAMILY_NAME" IS NULL)
														OR (T2."SEL_FAMILY_NAME" = '                              ')
														)
													THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
												ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
												END
											) IS NOT NULL
										)
									THEN ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
								ELSE ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
								END
							)
						)
			ELSE ('Auftr�ge �lter 30 Tage')
			END AS "Order_Desc_30",
		T1."ZIPCODE" AS "Zipcode",
		(left(T1."ZIPCODE", 4)) AS "PLZ_4_Stelle",
		(left(T1."ZIPCODE", 3)) AS "PLZ_3_Stelle",
		(left(T1."ZIPCODE", 2)) AS "PLZ_2_Stelle",
		(left(T1."ZIPCODE", 1)) AS "PLZ_1_Stelle",
		T8."REPAIR_GRP_SPECIFY" AS "Repair Grp Specify",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
				THEN (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (
														(T2."SEL_FAMILY_NAME" IS NULL)
														OR (T2."SEL_FAMILY_NAME" = '                              ')
														)
													THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
												ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
												END
											) IS NOT NULL
										)
									THEN ((left((((T1."INVOICE_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
								ELSE ((left((((T1."INVOICE_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
								END
							)
						)
			ELSE ('Rechnungen �lter 30 Tage')
			END AS "Invoice_Desc_30",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (
									(T2."SEL_FAMILY_NAME" IS NULL)
									OR (T2."SEL_FAMILY_NAME" = '                              ')
									)
								THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
							ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
							END
						) IS NOT NULL
					)
				THEN ((left((((T1."INVOICE_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
			ELSE ((left((((T1."INVOICE_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
			END AS "Invoice_Desc",
		CASE 
			WHEN (
					((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
					AND (
						(
							CASE 
								WHEN (
										(T1."CUSTOMER_GROUP" = '00')
										AND (T1."PMT_TERM" = 'IN')
										)
									THEN ('Intern')
								ELSE (T9."Zuordnung")
								END
							) <> 'Intern'
						)
					)
				THEN ((((T1."ORDER_NUMBER"))))
			WHEN (
					((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 30)
					AND (
						(
							CASE 
								WHEN (
										(T1."CUSTOMER_GROUP" = '00')
										AND (T1."PMT_TERM" = 'IN')
										)
									THEN ('Intern')
								ELSE (T9."Zuordnung")
								END
							) = 'Intern'
						)
					)
				THEN ('Intern')
			ELSE ('Auftr�ge �lter 30 Tage')
			END AS "Order_Desc_30_alt",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 100)
				THEN (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (
														(T2."SEL_FAMILY_NAME" IS NULL)
														OR (T2."SEL_FAMILY_NAME" = '                              ')
														)
													THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
												ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
												END
											) IS NOT NULL
										)
									THEN ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
								ELSE ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
								END
							)
						)
			ELSE ('Auftr�ge �lter 100 Tage')
			END AS "Order_Desc_100",
		(rtrim(T1."PRICE_CODE")) + ' - ' + (rtrim(T1."WORKSHOP_PRICECODE")) AS "Preiscode",
		CASE 
			WHEN (T3."ORDER_LINETYPE" <> '1')
				THEN (T3."DISCOUNT")
			ELSE (0)
			END AS "Rabatt Lohn",
		CASE 
			WHEN (T3."ORDER_LINETYPE" = '1')
				THEN (T3."DISCOUNT")
			ELSE (0)
			END AS "Rabatt Teile",
		CASE 
			WHEN (
					(T3."INV_TIME" <> 0)
					AND (T3."LINES_NET_VALUE" = .00)
					)
				THEN (T3."REDUCTION_AMOUNT" * - 1)
			WHEN (
					(T3."ORDER_LINETYPE" = '3')
					AND (T3."PROD_CODE" = 'EP')
					)
				THEN (T3."LINES_NET_VALUE")
			ELSE NULL
			END AS "Nachlass",
		CASE 
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 0.01 AND 0.99
					)
				THEN ('1')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 1.00 AND 1.99
					)
				THEN ('2')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 2.00 AND 2.99
					)
				THEN ('3')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 3.00 AND 3.99
					)
				THEN ('4')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 4.00 AND 4.99
					)
				THEN ('5')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 5.00 AND 5.99
					)
				THEN ('6')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 6.00 AND 6.99
					)
				THEN ('7')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 7.00 AND 7.99
					)
				THEN ('8')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 8.00 AND 8.99
					)
				THEN ('9')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) BETWEEN 9.00 AND 9.99
					)
				THEN ('10')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) > 9.99
					)
				THEN ('> 10')
			WHEN (
					(
						(
							CASE 
								WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
									THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
								ELSE (0)
								END
							) / 365
						) = 0
					)
				THEN ('keine Angabe')
			ELSE NULL
			END AS "FZG-Altersstaffel",
		(
			CASE 
				WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
					THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
				ELSE (0)
				END
			) / 365 AS "Fahrzeugalter_Jahr",
		CASE 
			WHEN (T13."FIRST_REG_DATE" <> convert(DATETIME, '1800-01-01 00:00:00.000'))
				THEN ((- 1 * datediff(day, T1."INVOICE_DATE", T13."FIRST_REG_DATE")))
			ELSE (0)
			END AS "Fahrzeugalter_Tage",
		T13."FIRST_REG_DATE" AS "First Reg Date",
		CASE 
			WHEN (T11."Fabrikat" IS NULL)
				THEN ('Fremd')
			ELSE (T11."Fabrikat")
			END AS "Fabrikat",
		(
			CASE 
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
					THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100))
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
					THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100))
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
					THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100))
				ELSE (T3."USED_TIME_INT")
				END
			) * 12 AS "benutzte AW",
		(
			CASE 
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(
												(T1."CLIENT_DB" IN ('dese01'))
												AND (
													(
														CASE 
															WHEN (
																	(
																		(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																		AND (T1."CLIENT_DB" = 'deop01')
																		)
																	AND ((left(T1."DEPARTMENT", 2)) = '11')
																	)
																THEN ('111')
															ELSE ((left(T1."DEPARTMENT", 2)))
															END
														) IN ('65', 'S5')
													)
												)
											AND (T3."MAKE_TIME_UNIT" = 'S100')
											)
										THEN (T3."INV_TIME_INT" / 100)
									ELSE (T3."INV_TIME_INT")
									END
								) * (T1."SPLIT_PCT_MAIN" / 100)
							)
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(
												(T1."CLIENT_DB" IN ('dese01'))
												AND (
													(
														CASE 
															WHEN (
																	(
																		(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																		AND (T1."CLIENT_DB" = 'deop01')
																		)
																	AND ((left(T1."DEPARTMENT", 2)) = '11')
																	)
																THEN ('111')
															ELSE ((left(T1."DEPARTMENT", 2)))
															END
														) IN ('65', 'S5')
													)
												)
											AND (T3."MAKE_TIME_UNIT" = 'S100')
											)
										THEN (T3."INV_TIME_INT" / 100)
									ELSE (T3."INV_TIME_INT")
									END
								) * (T1."SPLIT_PCT_SUB1" / 100)
							)
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(
												(T1."CLIENT_DB" IN ('dese01'))
												AND (
													(
														CASE 
															WHEN (
																	(
																		(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																		AND (T1."CLIENT_DB" = 'deop01')
																		)
																	AND ((left(T1."DEPARTMENT", 2)) = '11')
																	)
																THEN ('111')
															ELSE ((left(T1."DEPARTMENT", 2)))
															END
														) IN ('65', 'S5')
													)
												)
											AND (T3."MAKE_TIME_UNIT" = 'S100')
											)
										THEN (T3."INV_TIME_INT" / 100)
									ELSE (T3."INV_TIME_INT")
									END
								) * (T1."SPLIT_PCT_SUB2" / 100)
							)
				ELSE (
						(
							CASE 
								WHEN (
										(
											(T1."CLIENT_DB" IN ('dese01'))
											AND (
												(
													CASE 
														WHEN (
																(
																	(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																	AND (T1."CLIENT_DB" = 'deop01')
																	)
																AND ((left(T1."DEPARTMENT", 2)) = '11')
																)
															THEN ('111')
														ELSE ((left(T1."DEPARTMENT", 2)))
														END
													) IN ('65', 'S5')
												)
											)
										AND (T3."MAKE_TIME_UNIT" = 'S100')
										)
									THEN (T3."INV_TIME_INT" / 100)
								ELSE (T3."INV_TIME_INT")
								END
							)
						)
				END
			) * 12 AS "verk. AW",
		CASE 
			WHEN ((- 1 * datediff(day, (getdate()), T1."INVOICE_DATE")) <= 7)
				THEN (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (
														(T2."SEL_FAMILY_NAME" IS NULL)
														OR (T2."SEL_FAMILY_NAME" = '                              ')
														)
													THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
												ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
												END
											) IS NOT NULL
										)
									THEN ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
								ELSE ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
								END
							) + ' - ' + (convert(VARCHAR(50), year(T1."INVOICE_DATE")) + '-' + convert(VARCHAR(50), month(T1."INVOICE_DATE")) + '-' + convert(VARCHAR(50), day(T1."INVOICE_DATE")))
						)
			ELSE NULL
			END AS "Order Number Rg_Ausgang",
		CASE 
			WHEN (
					(
						CASE 
							WHEN (
									(T2."SEL_FAMILY_NAME" IS NULL)
									OR (T2."SEL_FAMILY_NAME" = '                              ')
									)
								THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
							ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
							END
						) IS NOT NULL
					)
				THEN ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + (rtrim(T2."SEL_FAMILY_NAME")))
			ELSE ((left((((T1."ORDER_NUMBER"))), 7)) + ' - ' + ((rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME") + ' - ' + T1."SALESMAN")
			END AS "Order Number",
		(db_name()) AS "Mandant",
		CASE 
			WHEN (
					(
						(
							(
								(
									(
										CASE 
											WHEN (
													(T3."ORDER_LINETYPE" = '1')
													AND (T1."STATUS" BETWEEN '40' AND '51')
													)
												THEN (T3."LINES_NET_VALUE")
											ELSE NULL
											END
										) - (
										CASE 
											WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
												THEN (
														(
															CASE 
																WHEN (
																		(T3."ORDER_LINETYPE" = '1')
																		AND (T1."STATUS" BETWEEN '40' AND '59')
																		)
																	THEN (T3."LINE_COSTS")
																ELSE NULL
																END
															) * (T1."SPLIT_PCT_MAIN" / 100)
														)
											WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
												THEN (
														(
															CASE 
																WHEN (
																		(T3."ORDER_LINETYPE" = '1')
																		AND (T1."STATUS" BETWEEN '40' AND '59')
																		)
																	THEN (T3."LINE_COSTS")
																ELSE NULL
																END
															) * (T1."SPLIT_PCT_SUB1" / 100)
														)
											WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
												THEN (
														(
															CASE 
																WHEN (
																		(T3."ORDER_LINETYPE" = '1')
																		AND (T1."STATUS" BETWEEN '40' AND '59')
																		)
																	THEN (T3."LINE_COSTS")
																ELSE NULL
																END
															) * (T1."SPLIT_PCT_SUB2" / 100)
														)
											ELSE (
													(
														CASE 
															WHEN (
																	(T3."ORDER_LINETYPE" = '1')
																	AND (T1."STATUS" BETWEEN '40' AND '59')
																	)
																THEN (T3."LINE_COSTS")
															ELSE NULL
															END
														)
													)
											END
										)
									)
								) < 0
							)
						OR (
							(
								(
									CASE 
										WHEN (
												(T3."ORDER_LINETYPE" = '1')
												AND (T1."STATUS" BETWEEN '30' AND '39')
												)
											THEN (T3."LINES_NET_VALUE")
										ELSE NULL
										END
									) - (
									CASE 
										WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
											THEN (
													(
														CASE 
															WHEN (
																	(T3."ORDER_LINETYPE" = '1')
																	AND (T1."STATUS" BETWEEN '30' AND '39')
																	)
																THEN (T3."LINE_COSTS")
															ELSE NULL
															END
														) * (T1."SPLIT_PCT_MAIN" / 100)
													)
										WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
											THEN (
													(
														CASE 
															WHEN (
																	(T3."ORDER_LINETYPE" = '1')
																	AND (T1."STATUS" BETWEEN '30' AND '39')
																	)
																THEN (T3."LINE_COSTS")
															ELSE NULL
															END
														) * (T1."SPLIT_PCT_SUB1" / 100)
													)
										WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
											THEN (
													(
														CASE 
															WHEN (
																	(T3."ORDER_LINETYPE" = '1')
																	AND (T1."STATUS" BETWEEN '30' AND '39')
																	)
																THEN (T3."LINE_COSTS")
															ELSE NULL
															END
														) * (T1."SPLIT_PCT_SUB2" / 100)
													)
										ELSE (
												(
													CASE 
														WHEN (
																(T3."ORDER_LINETYPE" = '1')
																AND (T1."STATUS" BETWEEN '30' AND '39')
																)
															THEN (T3."LINE_COSTS")
														ELSE NULL
														END
													)
												)
										END
									)
								) < 0
							)
						)
					AND (
						(
							CASE 
								WHEN (T1."STATUS" IN ('35', '37', '47', '50'))
									THEN ('Rechnung')
								WHEN (T1."STATUS" IN ('36', '39', '49', '51'))
									THEN ('Gutschrift')
								ELSE NULL
								END
							) = 'Rechnung'
						)
					)
				THEN ('VK < EK')
			ELSE ('VK > EK')
			END AS "VK < EK",
		(
			CASE 
				WHEN (
						(T3."ORDER_LINETYPE" = '1')
						AND (T1."STATUS" BETWEEN '30' AND '39')
						)
					THEN (T3."LINES_NET_VALUE")
				ELSE NULL
				END
			) - (
			CASE 
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(T3."ORDER_LINETYPE" = '1')
											AND (T1."STATUS" BETWEEN '30' AND '39')
											)
										THEN (T3."LINE_COSTS")
									ELSE NULL
									END
								) * (T1."SPLIT_PCT_MAIN" / 100)
							)
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(T3."ORDER_LINETYPE" = '1')
											AND (T1."STATUS" BETWEEN '30' AND '39')
											)
										THEN (T3."LINE_COSTS")
									ELSE NULL
									END
								) * (T1."SPLIT_PCT_SUB1" / 100)
							)
				WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
					THEN (
							(
								CASE 
									WHEN (
											(T3."ORDER_LINETYPE" = '1')
											AND (T1."STATUS" BETWEEN '30' AND '39')
											)
										THEN (T3."LINE_COSTS")
									ELSE NULL
									END
								) * (T1."SPLIT_PCT_SUB2" / 100)
							)
				ELSE (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '30' AND '39')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							)
						)
				END
			) AS "DB 1 Teile T",
		(
			(
				CASE 
					WHEN (
							(T3."ORDER_LINETYPE" = '1')
							AND (T1."STATUS" BETWEEN '40' AND '51')
							)
						THEN (T3."LINES_NET_VALUE")
					ELSE NULL
					END
				) - (
				CASE 
					WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
						THEN (
								(
									CASE 
										WHEN (
												(T3."ORDER_LINETYPE" = '1')
												AND (T1."STATUS" BETWEEN '40' AND '59')
												)
											THEN (T3."LINE_COSTS")
										ELSE NULL
										END
									) * (T1."SPLIT_PCT_MAIN" / 100)
								)
					WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
						THEN (
								(
									CASE 
										WHEN (
												(T3."ORDER_LINETYPE" = '1')
												AND (T1."STATUS" BETWEEN '40' AND '59')
												)
											THEN (T3."LINE_COSTS")
										ELSE NULL
										END
									) * (T1."SPLIT_PCT_SUB1" / 100)
								)
					WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
						THEN (
								(
									CASE 
										WHEN (
												(T3."ORDER_LINETYPE" = '1')
												AND (T1."STATUS" BETWEEN '40' AND '59')
												)
											THEN (T3."LINE_COSTS")
										ELSE NULL
										END
									) * (T1."SPLIT_PCT_SUB2" / 100)
								)
					ELSE (
							(
								CASE 
									WHEN (
											(T3."ORDER_LINETYPE" = '1')
											AND (T1."STATUS" BETWEEN '40' AND '59')
											)
										THEN (T3."LINE_COSTS")
									ELSE NULL
									END
								)
							)
					END
				)
			) AS "DB 1 Teile SC",
		T3."LINES_NET_VALUE" - T3."LINE_COSTS" AS "DB",
		CASE 
			WHEN (T1."STATUS" IN ('35', '37', '47', '50'))
				THEN ('Rechnung')
			WHEN (T1."STATUS" IN ('36', '39', '49', '51'))
				THEN ('Gutschrift')
			ELSE NULL
			END AS "Rechnung/Gutschrift",
		CASE 
			WHEN (
					(T3."ORDER_LINETYPE" = '1')
					AND (T1."STATUS" BETWEEN '30' AND '39')
					)
				THEN (T3."LINE_COSTS")
			ELSE NULL
			END AS "Einsatz Teile (nur Teile)_vor_Split",
		CASE 
			WHEN (
					(T3."ORDER_LINETYPE" = '1')
					AND (T1."STATUS" BETWEEN '40' AND '59')
					)
				THEN (T3."LINE_COSTS")
			ELSE NULL
			END AS "Einsatz Teile Service_vor_Split",
		T1."BASIS_NUMBER" + ' - ' + T1."REGISTER_NUMBER" AS "Fahrzeug",
		T12."MOD_LIN_SPECIFY" AS "Model",
		T11."Fabrikat" AS "Fabrikat_ori",
		T10."DESCRIPTION" AS "Description",
		CASE 
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '30' AND '39')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_MAIN" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '30' AND '39')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_SUB1" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '30' AND '39')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_SUB2" / 100)
						)
			ELSE (
					(
						CASE 
							WHEN (
									(T3."ORDER_LINETYPE" = '1')
									AND (T1."STATUS" BETWEEN '30' AND '39')
									)
								THEN (T3."LINE_COSTS")
							ELSE NULL
							END
						)
					)
			END AS "Einsatz Teile (nur Teile)",
		CASE 
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '40' AND '59')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_MAIN" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '40' AND '59')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_SUB1" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(T3."ORDER_LINETYPE" = '1')
										AND (T1."STATUS" BETWEEN '40' AND '59')
										)
									THEN (T3."LINE_COSTS")
								ELSE NULL
								END
							) * (T1."SPLIT_PCT_SUB2" / 100)
						)
			ELSE (
					(
						CASE 
							WHEN (
									(T3."ORDER_LINETYPE" = '1')
									AND (T1."STATUS" BETWEEN '40' AND '59')
									)
								THEN (T3."LINE_COSTS")
							ELSE NULL
							END
						)
					)
			END AS "Einsatz Teile Service",
		T10."GLOBAL_MAKE_CD" AS "Global Make Cd",
		CASE 
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
				THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_MAIN" / 100))
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
				THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB1" / 100))
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
				THEN (T3."USED_TIME_INT" * (T1."SPLIT_PCT_SUB2" / 100))
			ELSE (T3."USED_TIME_INT")
			END AS "benutzte Zeit (Auftrag)",
		CASE 
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_MAIN_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(
											(T1."CLIENT_DB" IN ('dese01'))
											AND (
												(
													CASE 
														WHEN (
																(
																	(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																	AND (T1."CLIENT_DB" = 'deop01')
																	)
																AND ((left(T1."DEPARTMENT", 2)) = '11')
																)
															THEN ('111')
														ELSE ((left(T1."DEPARTMENT", 2)))
														END
													) IN ('65', 'S5')
												)
											)
										AND (T3."MAKE_TIME_UNIT" = 'S100')
										)
									THEN (T3."INV_TIME_INT" / 100)
								ELSE (T3."INV_TIME_INT")
								END
							) * (T1."SPLIT_PCT_MAIN" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB1_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(
											(T1."CLIENT_DB" IN ('dese01'))
											AND (
												(
													CASE 
														WHEN (
																(
																	(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																	AND (T1."CLIENT_DB" = 'deop01')
																	)
																AND ((left(T1."DEPARTMENT", 2)) = '11')
																)
															THEN ('111')
														ELSE ((left(T1."DEPARTMENT", 2)))
														END
													) IN ('65', 'S5')
												)
											)
										AND (T3."MAKE_TIME_UNIT" = 'S100')
										)
									THEN (T3."INV_TIME_INT" / 100)
								ELSE (T3."INV_TIME_INT")
								END
							) * (T1."SPLIT_PCT_SUB1" / 100)
						)
			WHEN (T1."ORDER_NUMBER" = T1."SPLIT_SUB2_ORDERNO")
				THEN (
						(
							CASE 
								WHEN (
										(
											(T1."CLIENT_DB" IN ('dese01'))
											AND (
												(
													CASE 
														WHEN (
																(
																	(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																	AND (T1."CLIENT_DB" = 'deop01')
																	)
																AND ((left(T1."DEPARTMENT", 2)) = '11')
																)
															THEN ('111')
														ELSE ((left(T1."DEPARTMENT", 2)))
														END
													) IN ('65', 'S5')
												)
											)
										AND (T3."MAKE_TIME_UNIT" = 'S100')
										)
									THEN (T3."INV_TIME_INT" / 100)
								ELSE (T3."INV_TIME_INT")
								END
							) * (T1."SPLIT_PCT_SUB2" / 100)
						)
			ELSE (
					(
						CASE 
							WHEN (
									(
										(T1."CLIENT_DB" IN ('dese01'))
										AND (
											(
												CASE 
													WHEN (
															(
																(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
																AND (T1."CLIENT_DB" = 'deop01')
																)
															AND ((left(T1."DEPARTMENT", 2)) = '11')
															)
														THEN ('111')
													ELSE ((left(T1."DEPARTMENT", 2)))
													END
												) IN ('65', 'S5')
											)
										)
									AND (T3."MAKE_TIME_UNIT" = 'S100')
									)
								THEN (T3."INV_TIME_INT" / 100)
							ELSE (T3."INV_TIME_INT")
							END
						)
					)
			END AS "verk. Stunden",
		CASE 
			WHEN (T3."MAKE_TIME_UNIT" = 'AW12')
				THEN (T3."USED_TIME")
			WHEN (T3."MAKE_TIME_UNIT" IN ('S100', 'CV'))
				THEN (T3."USED_TIME" * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('A100'))
				THEN (T3."USED_TIME" / 100 * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('A120'))
				THEN (T3."USED_TIME" / 120 * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('AW10'))
				THEN (T3."USED_TIME" / 10 * 12)
			ELSE NULL
			END AS "benutzte AW_vor_Split",
		T3."EST_TIME_INT" * 12 AS "Soll AW",
		(rtrim(T1."DELIVERY_ACCOUNT")) + ' - ' + T1."NAME" AS "Kunde",
		CASE 
			WHEN (T3."MAKE_TIME_UNIT" = 'AW12')
				THEN (T3."INV_TIME")
			WHEN (T3."MAKE_TIME_UNIT" IN ('S100', 'CV'))
				THEN (T3."INV_TIME" * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('A100'))
				THEN (T3."INV_TIME" / 100 * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('A120'))
				THEN (T3."INV_TIME" / 120 * 12)
			WHEN (T3."MAKE_TIME_UNIT" IN ('AW10'))
				THEN (T3."INV_TIME" / 10 * 12)
			ELSE NULL
			END AS "verk. AW_vor_Split",
		CASE 
			WHEN (
					(T3."ORDER_LINETYPE" = '3')
					AND (T3."PROD_CODE" <> 'TU')
					)
				THEN (T3."LINES_NET_VALUE")
			ELSE NULL
			END AS "Umsatz Sonstiges",
		T3."USED_TIME_INT" AS "Used Time Int",
		T3."EST_TIME_INT" AS "Est Time Int",
		CASE 
			WHEN (
					(
						(T1."CLIENT_DB" IN ('dese01'))
						AND (
							(
								CASE 
									WHEN (
											(
												(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
												AND (T1."CLIENT_DB" = 'deop01')
												)
											AND ((left(T1."DEPARTMENT", 2)) = '11')
											)
										THEN ('111')
									ELSE ((left(T1."DEPARTMENT", 2)))
									END
								) IN ('65', 'S5')
							)
						)
					AND (T3."MAKE_TIME_UNIT" = 'S100')
					)
				THEN (T3."INV_TIME_INT" / 100)
			ELSE (T3."INV_TIME_INT")
			END AS "verk. Stunden_vor_Split",
		CASE 
			WHEN (T1."STATUS" IN ('35', '37', '39', '47', '49', '34', '36'))
				THEN ('Rechnung/Gutschrift')
			WHEN (T1."STATUS" IN ('30', '40', '32', '36'))
				THEN ('offen')
			WHEN (T1."STATUS" IN ('41'))
				THEN ('Kostenvoranschlag')
			ELSE NULL
			END AS "Status_1",
		CASE 
			WHEN (
					(T3."ORDER_LINETYPE" = '1')
					AND (T1."STATUS" BETWEEN '30' AND '39')
					)
				THEN (T3."LINES_NET_VALUE")
			ELSE NULL
			END AS "Umsatz Teile (nur Teile)",
		CASE 
			WHEN (
					(T3."INV_TIME" <> 0)
					AND (T3."LINES_NET_VALUE" <> .00)
					)
				THEN (T3."LINES_NET_VALUE")
			ELSE NULL
			END AS "Umsatz Lohn",
		CASE 
			WHEN (
					(T3."ORDER_LINETYPE" = '1')
					AND (T1."STATUS" BETWEEN '40' AND '51')
					)
				THEN (T3."LINES_NET_VALUE")
			ELSE NULL
			END AS "Umsatz Teile Service",
		T1."STATUS" + ' - ' + T4."STAT_SPECIFY" AS "Auftragsstatus",
		CASE 
			WHEN (T1."STATUS" BETWEEN '30' AND '39')
				THEN ('Teile')
			WHEN (T1."STATUS" BETWEEN '40' AND '59')
				THEN ('Service')
			WHEN (T1."STATUS" = '70')
				THEN ('sonst. Auftrag')
			WHEN (T1."STATUS" = '91')
				THEN ('Anfrage')
			ELSE NULL
			END AS "Auftragsart",
		T7."SEL_NAME" AS "Sel Name_Monteur",
		T2."LICENCE_ID" AS "Licence Id",
		CASE 
			WHEN (
					(T2."SEL_FAMILY_NAME" IS NULL)
					OR (T2."SEL_FAMILY_NAME" = '                              ')
					)
				THEN (T1."SALESMAN" + ' - ' + T2."SEL_NAME")
			ELSE ((rtrim(T2."SEL_FAMILY_NAME")) + ', ' + T2."SEL_FIRST_NAME")
			END AS "Serviceberater",
		T1."PMT_TERM" + ' - ' + (
			CASE 
				WHEN (T2."SEL_NAME" IN ('Dietmar Brehmer', '�mer Cokbilir', 'Carsten G�belt', 'Carsten Goebelt', 'Silke Liebe', 'Rene Sch�fer', 'Thomas Kienbaum', 'Marcos Pazos Nieto', 'Michael Zeller', 'Thomas Barnebeck', 'Christian Libowski', 'B�nyamin Tanrikulu', 'Andre Biller', 'Felice Mastrandrea', 'Matthias Molter', 'Alexander Lapp�hn', 'Sinan Cetin', 'Jeremias Perske', 'Andre Scheurich', 'Janek Van Der Lucht', 'Alexander Englert', 'Christian Hartig', 'Holger Conrad', 'Markus Gawlik', 'Ibrahim K�yner', 'Michael Christ', 'Manfred Peter', 'Christian Heim', 'Andreas Podlipny    ', 'Nadine Wierschin', 'Andreas M�ller      ', 'Arsenio Rosso       ', 'Roberto Bombardieri', 'Christian Jung', 'Marcus Schwarz', 'Thomas Schulz', 'Michael Staudinger', 'Dennis Rapp', 'Gerhard Janetzko', 'Christian Agneskirchner', 'Nico Amend', 'Francesco Galoppo', 'Bernd Fahm', 'Hagen Borth', 'Frank Lieske', 'Matthias Ernst', 'Stefan Bohn', 'Guenter Lewold', 'Reinhard Zucker', 'Juergen Klug'))
					THEN ('Serviceberater')
				ELSE ('Sonstige')
				END
			) AS "Gesch�ftsart",
		CASE 
			WHEN (
					(T1."CUSTOMER_GROUP" = '00')
					AND (T1."PMT_TERM" = 'IN')
					)
				THEN ('Intern')
			ELSE (T9."Zuordnung")
			END AS "Umsatzart",
		T6."CUSTOMER_GROUP" + ' - ' + T6."CUST_GROUP_SPECIFY" AS "Kundenart",
		T5."DEPARTMENT_TYPE_ID" + ' - ' + T5."DESCRIPTION" AS "Kostenstelle",
		(substring(T1."DEPARTMENT", 2, 1)) AS "Marke",
		CASE 
			WHEN (
					(
						(T1."INVOICE_DATE" <= convert(DATETIME, '2019-11-22 00:00:00.000'))
						AND (T1."CLIENT_DB" = 'deop01')
						)
					AND ((left(T1."DEPARTMENT", 2)) = '11')
					)
				THEN ('111')
			ELSE ((left(T1."DEPARTMENT", 2)))
			END AS "Standort",
		T1."CLIENT_DB" AS "Hauptbetrieb",
		T8."MAKE_CD" AS "Make Cd",
		T8."REPAIR_GROUP" AS "Repair Group",
		T7."SEL_FAMILY_NAME" AS "Sel Family Name_Monteur",
		T7."SEL_FIRST_NAME" AS "Sel First Name_Monteur",
		T7."SEL_DEPARTMENT" AS "Sel Department",
		T7."SELLER_CODE" AS "Seller Code",
		T6."CUST_GROUP_SPECIFY" AS "Cust Group Specify",
		T6."CUSTOMER_GROUP" AS "Customer Group",
		T2."SEL_FAMILY_NAME" AS "Sel Family Name",
		T2."SEL_FIRST_NAME" AS "Sel First Name",
		T2."SEL_DEPARTMENT" AS "Sel Department_2",
		T2."SEL_NAME" AS "Sel Name",
		T2."SELLER_CODE" AS "Seller Code_2",
		T5."DESCRIPTION" AS "Description_2",
		T5."DEPARTMENT_TYPE_ID" AS "Department Type Id",
		T4."STAT_SPECIFY" AS "Stat Specify",
		T4."STAT_CODE" AS "Stat Code",
		T3."UNIQUE_IDENT" AS "Unique Ident",
		T3."MAKE_TIME_UNIT" AS "Make Time Unit",
		T3."INV_TIME_INT" AS "Inv Time Int",
		T3."INV_TIME" AS "Inv Time",
		T3."EST_TIME" AS "Est Time",
		T3."USED_TIME" AS "Used Time",
		CASE 
			WHEN (T3."REPAIR_NAME" LIKE '%;%')
				THEN ('ung�ltiger Repair Name')
			ELSE (T3."REPAIR_NAME")
			END AS "Repair Name",
		T3."REPAIR_GROUP" AS "Repair Group_2",
		T3."REPAIR_CODE" AS "Repair Code",
		T3."LINE_COSTS" AS "Line Costs",
		T3."DELIVERY_QUANTITY" AS "Delivery Quantity",
		T3."ORDER_QUANTITY" AS "Order Quantity",
		T3."PROD_NAME" AS "Prod Name",
		T3."PRODUCT_GROUP" AS "Product Group",
		T3."MAKE_CD" AS "Make Cd_Position",
		T3."PROD_CODE" AS "Prod Code",
		T3."LINES_NET_VALUE" AS "Lines Net Value",
		T3."STDPRICE" AS "Stdprice",
		T3."DISCOUNT" AS "Discount",
		T3."SALESMAN" AS "Salesman",
		T3."MECHANIC_CODE" AS "Mechanic Code",
		T3."REDUCTION_AMOUNT" AS "Reduction Amount",
		T3."REDUCTION_CODE" AS "Reduction Code",
		T3."ORDER_LINETYPE" AS "Order Linetype",
		T3."LINE_NUMBER" AS "Line Number",
		T3."ORDER_NUMBER" AS "Order Number_ori2",
		T1."UNIQUE_IDENT" AS "Unique Ident_2",
		T1."ACTUAL_INV_DATE_TIME" AS "Actual Inv Date Time",
		T1."COMMISSION_SALESMAN" AS "Commission Salesman",
		T1."WORKSHOP_TEAM" AS "Workshop Team",
		T1."CHASSIS_NUMBER" AS "Chassis Number",
		T1."TRANSFER_MAKE_CD" AS "Transfer Make Cd",
		T1."YEAR_MODEL" AS "Year Model",
		T1."MAKE_CD" AS "Make Cd_2",
		T1."FAC_MODEL_CODE_S" AS "Fac Model Code S",
		T1."END_TIME" AS "End Time",
		T1."END_DATE" AS "End Date",
		T1."ARRIVAL_DATE" AS "Arrival Date",
		T1."ARRIVAL_TIME" AS "Arrival Time",
		T1."SPLIT_COUNTER" AS "Split Counter",
		T1."WORKSHOP_PRICECODE" AS "Workshop Pricecode",
		T1."EXPECTED_ORDER_TIM" AS "Expected Order Tim",
		T1."REFERENCE_NUMBER" AS "Reference Number",
		T1."DISCOUNT_LIMIT" AS "Discount Limit",
		T1."MAIL_ADDR" AS "Mail Addr",
		T1."ADDR_2" AS "Addr 2",
		T1."STREET_ADDR" AS "Street Addr",
		T1."NAME" AS "Name",
		T1."TITLE" AS "Title",
		T1."SALES_TAX_FREE" AS "Sales Tax Free",
		T1."INVOICE_DISC_PERC" AS "Invoice Disc Perc",
		T1."SALES_CLASS_NUMBER" AS "Sales Class Number",
		T1."PREV_STATUS" AS "Prev Status",
		T1."MILEAGE" AS "Mileage",
		T1."BASIS_NUMBER" AS "Basis Number",
		T1."CUSTOMER_GROUP" AS "Customer Group_2",
		T1."INTERNAL_CODE" AS "Internal Code",
		T1."PLACE_CODE" AS "Place Code",
		T1."STOCK" AS "Stock",
		T1."MISC_ADDS" AS "Misc Adds",
		T1."PRICE_CODE" AS "Price Code",
		T1."DISCOUNT_AMOUNT" AS "Discount Amount",
		T1."TAX_SHARE" AS "Tax Share",
		T1."ORDERS_GROSSVALUE" AS "Orders Grossvalue",
		T1."PURCH_TAX" AS "Purch Tax",
		T1."COSTS" AS "Costs",
		CASE 
			WHEN (T2."SEL_NAME" IN ('Dietmar Brehmer', '�mer Cokbilir', 'Carsten G�belt', 'Carsten Goebelt', 'Silke Liebe', 'Rene Sch�fer', 'Thomas Kienbaum', 'Marcos Pazos Nieto', 'Michael Zeller', 'Thomas Barnebeck', 'Christian Libowski', 'B�nyamin Tanrikulu', 'Andre Biller', 'Felice Mastrandrea', 'Matthias Molter', 'Alexander Lapp�hn', 'Sinan Cetin', 'Jeremias Perske', 'Andre Scheurich', 'Janek Van Der Lucht', 'Alexander Englert', 'Christian Hartig', 'Holger Conrad', 'Markus Gawlik', 'Ibrahim K�yner', 'Michael Christ', 'Manfred Peter', 'Christian Heim', 'Andreas Podlipny    ', 'Nadine Wierschin', 'Andreas M�ller      ', 'Arsenio Rosso       ', 'Roberto Bombardieri', 'Christian Jung', 'Marcus Schwarz', 'Thomas Schulz', 'Michael Staudinger', 'Dennis Rapp', 'Gerhard Janetzko', 'Christian Agneskirchner', 'Nico Amend', 'Francesco Galoppo', 'Bernd Fahm', 'Hagen Borth', 'Frank Lieske', 'Matthias Ernst', 'Stefan Bohn', 'Guenter Lewold', 'Reinhard Zucker', 'Juergen Klug'))
				THEN ('Serviceberater')
			ELSE ('Sonstige')
			END AS "Payment Text",
		T1."DUEDATE_1" AS "Duedate 1",
		T1."PMT_TERM" AS "Pmt Term",
		T1."INVOICE_DATE" AS "Invoice Date",
		T1."DELIVERY_DATE" AS "Delivery Date",
		T1."ORDER_DATE" AS "Order Date",
		T1."DEBIT_PERM" AS "Debit Perm",
		T1."SALESMAN" AS "Salesman_2",
		T1."DEBET_DEPARTMENT" AS "Debet Department",
		T1."DELIVERY_ACCOUNT" AS "Delivery Account",
		T1."HANDLER" AS "Handler",
		T1."TRANSACT_DATE" AS "Transact Date",
		T1."STATE_CODE" AS "State Code",
		T1."WORKSHOP_MODEL" AS "Workshop Model",
		T1."INVOICE_NUMBER" AS "Invoice Number",
		T1."DEBIT_ACCOUNT" AS "Debit Account",
		T1."STATE_KEY_DATE" AS "State Key Date",
		T1."STATUS" AS "Status",
		T1."REGISTER_NUMBER" AS "Register Number"
	FROM (
		(
			(
				(
					(
						(
							(
								(
									(
										(
											(
												(
													(
														"OPTIMA"."import"."ORDER_HEADER" T1 LEFT JOIN "OPTIMA"."import"."VPP43" T2 ON (T1."SALESMAN" = T2."SELLER_CODE")
															AND (T1."CLIENT_DB" = T2."CLIENT_DB")
														) LEFT JOIN "OPTIMA"."import"."ORDER_LINE" T3 ON (T1."ORDER_NUMBER" = T3."ORDER_NUMBER")
														AND (T1."CLIENT_DB" = T3."CLIENT_DB")
													) LEFT JOIN "OPTIMA"."import"."VPP25" T4 ON (T1."STATUS" = T4."STAT_CODE")
													AND (T1."CLIENT_DB" = T4."CLIENT_DB")
												) LEFT JOIN "OPTIMA"."import"."DEPARTMENT_TYPE" T5 ON (T1."DEPARTMENT" = T5."DEPARTMENT_TYPE_ID")
												AND (T1."CLIENT_DB" = T5."CLIENT_DB")
											) LEFT JOIN "OPTIMA"."import"."VPP48" T6 ON (T1."CUSTOMER_GROUP" = T6."CUSTOMER_GROUP")
											AND (T1."CLIENT_DB" = T6."CLIENT_DB")
										) LEFT JOIN "OPTIMA"."import"."VPP43" T7 ON (T3."MECHANIC_CODE" = T7."SELLER_CODE")
										AND (T3."CLIENT_DB" = T7."CLIENT_DB")
									) LEFT JOIN "OPTIMA"."import"."VPP73" T8 ON (
										(T3."REPAIR_GROUP" = T8."REPAIR_GROUP")
										AND (T3."MAKE_CD" = T8."MAKE_CD")
										)
									AND (T3."CLIENT_DB" = T8."CLIENT_DB")
								) LEFT JOIN "OPTIMA"."data"."GC_Umsatzart" T9 ON (T1."CUSTOMER_GROUP" = T9."Geschaeftsbuchungsgruppe")
								AND (T1."CLIENT_DB" = T9."Client_DB")
							) LEFT JOIN "OPTIMA"."import"."GLOBAL_MAKE" T10 ON (T1."MAKE_CD" = T10."GLOBAL_MAKE_CD")
							AND (T1."CLIENT_DB" = T10."CLIENT_DB")
						) LEFT JOIN "OPTIMA"."data"."GC_Marken" T11 ON (T1."MAKE_CD" = T11."Make")
						AND (T1."CLIENT_DB" = T11."Client_DB")
					) LEFT JOIN "OPTIMA"."import"."VEHICLE" T13 ON (
						(T1."CHASSIS_NUMBER" = T13."CHASSIS_NUMBER")
						AND (T1."BASIS_NUMBER" = T13."BASIS_NUMBER")
						)
					AND (T1."CLIENT_DB" = T13."CLIENT_DB")
				) LEFT JOIN "OPTIMA"."import"."VPP5Q" T12 ON (
					(T13."MODEL_LINE" = T12."MODEL_LINE")
					AND (T13."MAKE_CD" = T12."MAKE_CD")
					)
				AND (T13."CLIENT_DB" = T12."CLIENT_DB")
			) LEFT JOIN "OPTIMA"."data"."GC_Department" T14 ON (
				((left(T1."DEPARTMENT", 2)) = T14."Standort")
				AND (T1."CLIENT_DB" = T14."Hauptbetrieb")
				)
			OR (
				((left(T1."DEPARTMENT", 1)) = T14."Standort")
				AND (T1."CLIENT_DB" = T14."Hauptbetrieb")
				)
		)
	WHERE (
			(
				(T1."STATUS" IN ('35', '37', '39', '47', '48', '49', '50', '51', '52', '36', '34'))
				AND (T1."INVOICE_DATE" >= convert(DATETIME, '2020-01-01 00:00:00.000'))
				)
			AND (T3."ORDER_LINETYPE" <> '2')
			)
		-- order by "Order Number_ori" asc,"Debit Account" asc,"Name" asc,"Price Code" asc,"Salesman_2" asc,"Hauptbetrieb" asc,"Sel Family Name" asc,"Order Number" asc,"Kunde" asc
	) D1