ETL_DWH_LOCOSOFT/schema/GC/views/transform.Aftersales_Rechnungen_Focus_Group.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE     VIEW [transform].[Aftersales_Rechnungen_Focus_Group] AS

SELECT T1."invoice_type" AS "Invoice Type",
	T1."invoice_number" AS "Invoice Number",
	T1."subsidiary" AS "Subsidiary",
	T1."invoice_date" AS "Invoice Date",
	T1."service_date" AS "Service Date",
	T1."is_canceled" AS "Is Canceled",
	T1."vehicle_number" AS "Vehicle Number",
	(left((((T1."invoice_type"))), 1)) + '_' + (left((((T1."invoice_number"))), 9)) AS "Invoice_Type_Invoice_Number",
	'1' AS "Hauptbetrieb",
	'0' + (((T1."subsidiary"))) AS "Standort",
	T2."Serviceberater" AS "Serviceberater",
	CASE 
		WHEN (T1."invoice_type" = 6)
			THEN ('GWL')
		WHEN (
				(T1."invoice_type" = 4)
				OR (T3."customer_number" = 100001)
				)
			THEN ('intern')
		ELSE ('extern')
		END AS "Umsatzart",
	T4."description" AS "Fabrikat",
	T4."description" AS "Description_Makes",
	T5."description" AS "Description_Models",
	(left((upper(T5."description")), 3)) AS "Model",
	(left((left((((T1."vehicle_number"))), 5)) + ' - ' + T5."description", 100)) AS "Fahrzeug",
	CASE 
		WHEN ((left((((T6."type"))), 1)) IN ('1', '6'))
			THEN ('41 - After Sales Kundendienst eigene Werkstatt')
		WHEN (T6."type" IN (40, 41, 44, 45, 46, 47, 48, 49, 88, 89, 50, 90))
			THEN ('41 - After Sales Kundendienst eigene Werkstatt')
		WHEN ((left((((T6."type"))), 1)) IN ('2'))
			THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
		WHEN (T6."type" IN (42, 92))
			THEN ('44 - After Sales Kundendienst eigene Karosserieabteilung')
		WHEN ((left((((T6."type"))), 1)) IN ('3'))
			THEN ('45 - After Sales Kundendienst eigene Lackiererei')
		WHEN (T6."type" IN (43, 93))
			THEN ('45 - After Sales Kundendienst eigene Lackiererei')
		ELSE NULL
		END AS "Kostenstelle",
	CASE 
		WHEN (T4."description" = 'Ford')
			THEN ('1')
		WHEN (T4."description" = 'Nissan')
			THEN ('3')
		WHEN (T4."description" = 'Suzuki')
			THEN ('2')
		ELSE ('9')
		END AS "Marke",
	'Service' AS "Auftragsart",
	T3."zip_code" AS "Geschäftsart",
	CASE 
		WHEN (T3."family_name" IS NOT NULL)
			THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
		ELSE ((left((((T3."customer_number"))), 7)))
		END AS "Kunde",
	T7."time_units" AS "Time Units",
	(convert(FLOAT, T7."time_units")) AS "Time Units_Zahl",
	10 AS "AW/Std.",
	((convert(FLOAT, T7."time_units"))) / 10 AS "verk. Std. Focus",
	CASE 
		WHEN (T2."Serviceberater" IS NOT NULL)
			THEN (
					(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 8)) + ' - ' + T2."Serviceberater" + ' - ' + (
						CASE 
							WHEN (T3."family_name" IS NOT NULL)
								THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
							ELSE ((left((((T3."customer_number"))), 7)))
							END
						)
					)
		ELSE (
				(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 8)) + ' - ' + (
					CASE 
						WHEN (T3."family_name" IS NOT NULL)
							THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
						ELSE ((left((((T3."customer_number"))), 7)))
						END
					)
				)
		END AS "Order Number",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), (convert(DATETIME, T1."invoice_date")))) <= 4)
			THEN (
					(
						CASE 
							WHEN (T2."Serviceberater" IS NOT NULL)
								THEN (
										(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 8)) + ' - ' + T2."Serviceberater" + ' - ' + (
											CASE 
												WHEN (T3."family_name" IS NOT NULL)
													THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
												ELSE ((left((((T3."customer_number"))), 7)))
												END
											)
										)
							ELSE (
									(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 8)) + ' - ' + (
										CASE 
											WHEN (T3."family_name" IS NOT NULL)
												THEN ((left((((T3."customer_number"))), 7)) + ' - ' + T3."family_name")
											ELSE ((left((((T3."customer_number"))), 7)))
											END
										)
									)
							END
						)
					)
		ELSE NULL
		END AS "Order Number_Rg_Ausg",
	--T2."name" AS "Kundenart",
	'' AS "Kundenart",
	T7."labour_operation_id" AS "Labour Operation Id",
	T7."text_line" AS "Text Line",
	T7."net_price_in_order" AS "Umsatz Focus",
	T7."rebate_percent" AS "Rebate Percent",
	T8."Focus_Group" AS "Focus Group",
	T8."Parts_Number" AS "Parts Number",
	1 AS "Menge Focus",
	((left((((T1."invoice_type"))), 1)) + '_' + (left((((T1."invoice_number"))), 9))) + '_' + T8."Parts_Number" AS "Inv_Type_Number_Parts_Number",
	T8."GMPD_Group" AS "Gmpd Group",
	(convert(FLOAT, T8."Marke_ID")) AS "Provision Betrag",
	1 * ((convert(FLOAT, T8."Marke_ID"))) AS "Provision SB"
FROM "data"."GC_Fokus_Produktgruppen" T8,
	(
		(
			(
				(
					(
						(
							(
								LOCOSOFT."dbo"."invoices" T1 LEFT JOIN LOCOSOFT."dbo"."customers_suppliers" T3 ON T3."customer_number" = T1."paying_customer"
								) LEFT JOIN LOCOSOFT."dbo"."vehicles" T9 ON T1."vehicle_number" = T9."internal_number"
							) LEFT JOIN LOCOSOFT."dbo"."makes" T4 ON T9."make_number" = T4."make_number"
						) LEFT JOIN LOCOSOFT."dbo"."models" T5 ON (T9."make_number" = T5."make_number")
						AND (T9."model_code" = T5."model_code")
					) LEFT JOIN LOCOSOFT."dbo"."labours" T7 ON (T7."invoice_number" = T1."invoice_number")
					AND (T7."invoice_type" = T1."invoice_type")
				) LEFT JOIN LOCOSOFT."dbo"."charge_type_descriptions" T6 ON (T6."type" = T7."charge_type")
				AND (T6."client_db" = T7."client_db")
			) LEFT JOIN LOCOSOFT."ims"."Serviceberater_Rechnung" T2 ON T1."invtype_invnr" = T2."invtype_invnr"
		)
WHERE ((ltrim(T7."labour_operation_id")) = T8."Parts_Number")
	AND (
		(
			(
				(T1."invoice_type" BETWEEN 2 AND 6)
				AND (T1."is_canceled" <> 1)
				)
			AND (T1."invoice_date" >= convert(DATE, '2022-01-01'))
			)
		AND (T7."labour_operation_id" IS NOT NULL)
		)
	-- order by "Invoice_Type_Invoice_Number" asc,"Invoice Number" asc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO