SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_imr].[offene_auftraege_teile]
AS
SELECT DISTINCT c55 AS "Order Number_ori",
	c97 AS "Order Position",
	'' AS "Labour Type",
	c96 AS "Subsidiary",
	c95 AS "Is Invoiced",
	c94 AS "Invoice Type",
	c93 AS "Invoice Number",
	c92 AS "Order Date",
	c91 AS "Estimated Inbound Time",
	c90 AS "Estimated Outbound Time",
	c89 AS "Order Print Date",
	c88 AS "Order Taking Employee No",
	c87 AS "Order Delivery Employee No",
	c86 AS "Vehicle Number",
	c85 AS "Order Mileage",
	c84 AS "Order Customer",
	c83 AS "Paying Customer",
	c82 AS "Holder Number",
	c81 AS "Parts Rebate Group Sell",
	c80 AS "Clearing Delay Type",
	'' AS "Code_Labour_Type",
	'' AS "Description_Labour_Type",
	c79 AS "Hauptbetrieb",
	c78 AS "Standort",
	c77 AS "Name_Serviceberater",
	c76 AS "Serviceberater",
	'Teile' AS "Umsatzart",
	c75 AS "Customer Number_Cust",
	c74 AS "First Name_Cust",
	c73 AS "Family Name_Cust",
	c72 AS "Kunde",
	c71 AS "Order Number",
	c70 AS "Internal Number_Vehicle",
	c69 AS "Vin_Vehicle",
	c68 AS "License Plate_Vehicle",
	c67 AS "Make Number_Vehicle",
	'Service' AS "Auftragsart",
	c66 AS "Make Number_Makes",
	c65 AS "Description_Makes",
	c65 AS "Fabrikat",
	c64 AS "Description_Models",
	c63 AS "Model",
	1 AS "DG_1",
	COUNT('') OVER (PARTITION BY c55) AS "DG_2",
	c62 AS "Part Number",
	c61 AS "Stock No",
	c60 AS "Stock Removal Date",
	c59 AS "Amount",
	c56 AS "Sum",
	c58 AS "Parts Type",
	c57 AS "Text Line",
	c56 AS "Teile"
FROM (
	SELECT order_positions."order_number" AS c55,
		parts."sum" AS c56,
		parts."text_line" AS c57,
		parts."parts_type" AS c58,
		parts."amount" AS c59,
		parts."stock_removal_date" AS c60,
		parts."stock_no" AS c61,
		parts."part_number" AS c62,
		(substring((upper(models."description")), 1, 3)) AS c63,
		models."description" AS c64,
		makes."description" AS c65,
		makes."make_number" AS c66,
		vehicles."make_number" AS c67,
		vehicles."license_plate" AS c68,
		vehicles."vin" AS c69,
		vehicles."internal_number" AS c70,
		CASE 
			WHEN (vehicles."license_plate" IS NOT NULL)
				THEN (
						(substring((convert(VARCHAR(50), order_positions."order_number")), 1, 6)) + ' - ' + (
							CASE 
								WHEN (order_positions."order_taking_employee_no" = 0)
									THEN ('SB fehlt')
								ELSE ((substring((convert(VARCHAR(50), order_positions."order_taking_employee_no")), 1, 4)) + ' - ' + employees."name")
								END
							) + ' - ' + customers_suppliers."family_name" + ' - ' + (convert(VARCHAR(50), year(order_positions."order_date")) + '-' + convert(VARCHAR(50), month(order_positions."order_date")) + '-' + convert(VARCHAR(50), day(order_positions."order_date"))) + ' - ' + vehicles."license_plate"
						)
			ELSE (
					(substring((convert(VARCHAR(50), order_positions."order_number")), 1, 6)) + ' - ' + (
						CASE 
							WHEN (order_positions."order_taking_employee_no" = 0)
								THEN ('SB fehlt')
							ELSE ((substring((convert(VARCHAR(50), order_positions."order_taking_employee_no")), 1, 4)) + ' - ' + employees."name")
							END
						) + ' - ' + customers_suppliers."family_name" + ' - ' + (convert(VARCHAR(50), year(order_positions."order_date")) + '-' + convert(VARCHAR(50), month(order_positions."order_date")) + '-' + convert(VARCHAR(50), day(order_positions."order_date")))
					)
			END AS c71,
		(substring((convert(VARCHAR(50), customers_suppliers."customer_number")), 1, 7)) + ' - ' + customers_suppliers."first_name" + ' ' + customers_suppliers."family_name" AS c72,
		customers_suppliers."family_name" AS c73,
		customers_suppliers."first_name" AS c74,
		customers_suppliers."customer_number" AS c75,
		CASE 
			WHEN (order_positions."order_taking_employee_no" = 0)
				THEN ('SB fehlt')
			ELSE ((substring((convert(VARCHAR(50), order_positions."order_taking_employee_no")), 1, 4)) + ' - ' + employees."name")
			END AS c76,
		employees."name" AS c77,
		'' + (convert(VARCHAR(50), order_positions."subsidiary")) AS c78,
		CASE 
			WHEN (('0' + (convert(VARCHAR(50), order_positions."subsidiary"))) IN ('015                                                                                                                                                                                                                                                            '))
				THEN ('15')
			ELSE ('1')
			END AS c79,
		order_positions."clearing_delay_type" AS c80,
		order_positions."parts_rebate_group_sell" AS c81,
		order_positions."holder_number" AS c82,
		order_positions."paying_customer" AS c83,
		order_positions."order_customer" AS c84,
		order_positions."order_mileage" AS c85,
		order_positions."vehicle_number" AS c86,
		order_positions."order_delivery_employee_no" AS c87,
		order_positions."order_taking_employee_no" AS c88,
		order_positions."order_print_date" AS c89,
		order_positions."estimated_outbound_time" AS c90,
		order_positions."estimated_inbound_time" AS c91,
		order_positions."order_date" AS c92,
		parts."invoice_number" AS c93,
		parts."invoice_type" AS c94,
		parts."is_invoiced" AS c95,
		order_positions."subsidiary" AS c96,
		parts."order_position" AS c97
	FROM (
		(
			(
				(
					(
						(
							LOCOSOFT."dbo"."order_positions" order_positions LEFT OUTER JOIN LOCOSOFT."dbo"."parts" parts ON order_positions."order_number" = parts."order_number"
							) LEFT OUTER JOIN LOCOSOFT."dbo"."employees" employees ON order_positions."order_taking_employee_no" = employees."employee_number"
						) LEFT OUTER JOIN LOCOSOFT."dbo"."customers_suppliers" customers_suppliers ON order_positions."order_customer" = customers_suppliers."customer_number"
					) LEFT OUTER JOIN LOCOSOFT."dbo"."vehicles" vehicles ON order_positions."vehicle_number" = vehicles."internal_number"
				) LEFT OUTER JOIN LOCOSOFT."dbo"."makes" makes ON vehicles."make_number" = makes."make_number"
			) LEFT OUTER JOIN LOCOSOFT."dbo"."models" models ON (vehicles."make_number" = models."make_number")
			AND (vehicles."model_code" = models."model_code")
		)
	WHERE (
			(
				(
					(parts."invoice_number" IS NULL)
					AND (order_positions."order_date" >= convert(DATETIME, '2017-01-01 00:00:00.000'))
					)
				AND (parts."sum" IS NOT NULL)
				)
			AND ('' <> 'F')
			)
	) D1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
