C11/System/LOCOSOFT/IQD/Serv_Teile/Aftersales_Rechnungen_neu_Fokus.sql

SELECT T1."invoice_type" AS "Invoice Type_2",
	T2."type" AS "Type_Invoice_Types",
	T2."description" AS "Description_Invoice_Types",
	T1."invoice_number" AS "Invoice Number_2",
	T1."subsidiary" AS "Subsidiary_2",
	T1."paying_customer" AS "Paying Customer",
	T1."order_customer" AS "Order Customer",
	T1."invoice_date" AS "Invoice Date",
	T1."service_date" AS "Service Date",
	T1."is_canceled" AS "Is Canceled",
	T1."cancelation_number" AS "Cancelation Number",
	T1."cancelation_date" AS "Cancelation Date",
	T1."cancelation_employee" AS "Cancelation Employee",
	T1."is_own_vehicle" AS "Is Own Vehicle",
	T1."is_credit" AS "Is Credit",
	T1."credit_invoice_type" AS "Credit Invoice Type",
	T1."credit_invoice_number" AS "Credit Invoice Number",
	T1."odometer_reading" AS "Odometer Reading",
	T1."creating_employee" AS "Creating Employee",
	T1."internal_cost_account" AS "Internal Cost Account",
	T1."vehicle_number" AS "Vehicle Number",
	T1."full_vat_basevalue" AS "Full Vat Basevalue",
	T1."full_vat_percentage" AS "Full Vat Percentage",
	T1."full_vat_value" AS "Full Vat Value",
	T1."reduced_vat_basevalue" AS "Reduced Vat Basevalue",
	T1."reduced_vat_percentage" AS "Reduced Vat Percentage",
	T1."reduced_vat_value" AS "Reduced Vat Value",
	T1."used_part_vat_value" AS "Used Part Vat Value",
	T1."job_amount_net" AS "Job Amount Net",
	T1."job_amount_gross" AS "Job Amount Gross",
	T1."job_rebate" AS "Job Rebate",
	T1."part_amount_net" AS "Part Amount Net",
	T1."part_amount_gross" AS "Part Amount Gross",
	T1."part_rebate" AS "Part Rebate",
	T1."part_disposal" AS "Part Disposal",
	T1."total_gross" AS "Total Gross",
	T1."total_net" AS "Total Net",
	(left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9)) AS "Inv_Type_Invoice_Number",
	'1' AS "Hauptbetrieb",
	(((T1."subsidiary"))) AS "Standort",
	T3."employee_number" AS "Employee Number_Employees",
	T3."name" AS "Name_Employees",
	T3."mechanic_number" AS "Mechanic Number_Employees",
	T3."salesman_number" AS "Salesman Number_Employees",
	T3."is_business_executive" AS "Is Business Executive_Employees",
	T3."is_master_craftsman" AS "Is Master Craftsman_Employees",
	(left((((T3."employee_number"))), 4)) + ' - ' + T3."name" AS "Serviceberater_Rg_Steller",
	T4."customer_number" AS "Customer Number_Customers_Suppliers",
	T4."first_name" AS "First Name_Customers_Suppliers",
	T4."family_name" AS "Family Name_Customers_Suppliers",
	CASE 
		WHEN (T4."first_name" IS NULL)
			THEN ((left((((T4."customer_number"))), 7)) + ' - ' + T4."family_name")
		ELSE ((left((((T4."customer_number"))), 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name")
		END AS "Kunde",
	T5."net_price_in_order" AS "Betrag",
	CASE 
		WHEN (T1."invoice_type" = 6)
			THEN ('GWL')
		WHEN (
				(T1."invoice_type" = 4)
				OR (T4."customer_number" IN (1000000, 1000001, 1000002, 1000003, 1000010))
				)
			THEN ('intern')
		ELSE ('extern')
		END AS "Umsatzart",
	CASE 
		WHEN (T1."invoice_type" = 5)
			THEN ('Teile')
		ELSE ('Service')
		END AS "Auftragsart",
	'' AS "Gesch�ftsart",
	T6."make_number" AS "Make Number",
	T6."free_form_make_text" AS "Free Form Make Text",
	T6."model_code" AS "Model Code",
	T6."free_form_model_text" AS "Free Form Model Text",
	T7."description" AS "Description_Makes",
	T8."description" AS "Description_Models",
	T7."description" AS "Fabrikat",
	T8."description" AS "Model_Detail",
	(left((ucase(T8."description")), 3)) AS "Model",
	(left((left((((T1."vehicle_number"))), 5)) + ' - ' + T8."description", 100)) AS "Fahrzeug",
	CASE 
		WHEN (
				(
					CASE 
						WHEN (T1."invoice_type" = 5)
							THEN (((left((((T3."employee_number"))), 4)) + ' - ' + T3."name"))
						ELSE (T9."Serviceberater")
						END
					) IS NOT NULL
				)
			THEN (
					((left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9))) + ' - ' + (
						CASE 
							WHEN (T1."invoice_type" = 5)
								THEN (((left((((T3."employee_number"))), 4)) + ' - ' + T3."name"))
							ELSE (T9."Serviceberater")
							END
						) + ' - ' + (
						CASE 
							WHEN (T4."first_name" IS NULL)
								THEN ((left((((T4."customer_number"))), 7)) + ' - ' + T4."family_name")
							ELSE ((left((((T4."customer_number"))), 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name")
							END
						)
					)
		ELSE (
				((left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9))) + ' - ' + (
					CASE 
						WHEN (T4."first_name" IS NULL)
							THEN ((left((((T4."customer_number"))), 7)) + ' - ' + T4."family_name")
						ELSE ((left((((T4."customer_number"))), 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name")
						END
					)
				)
		END AS "Order Number_2",
	(convert(DATETIME, T1."invoice_date")) AS "Invoice_Date_Uhrzeit",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), ((convert(DATETIME, T1."invoice_date"))))) <= 4)
			THEN (
					(
						CASE 
							WHEN (
									(
										CASE 
											WHEN (T1."invoice_type" = 5)
												THEN (((left((((T3."employee_number"))), 4)) + ' - ' + T3."name"))
											ELSE (T9."Serviceberater")
											END
										) IS NOT NULL
									)
								THEN (
										((left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9))) + ' - ' + (
											CASE 
												WHEN (T1."invoice_type" = 5)
													THEN (((left((((T3."employee_number"))), 4)) + ' - ' + T3."name"))
												ELSE (T9."Serviceberater")
												END
											) + ' - ' + (
											CASE 
												WHEN (T4."first_name" IS NULL)
													THEN ((left((((T4."customer_number"))), 7)) + ' - ' + T4."family_name")
												ELSE ((left((((T4."customer_number"))), 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name")
												END
											)
										)
							ELSE (
									((left((((T1."invoice_type"))), 1)) + (left((((T1."invoice_number"))), 9))) + ' - ' + (
										CASE 
											WHEN (T4."first_name" IS NULL)
												THEN ((left((((T4."customer_number"))), 7)) + ' - ' + T4."family_name")
											ELSE ((left((((T4."customer_number"))), 7)) + ' - ' + T4."first_name" + ' ' + T4."family_name")
											END
										)
									)
							END
						)
					)
		ELSE NULL
		END AS "Order Number_Rg_Ausg",
	T9."Serviceberater" AS "Serviceberater_order_pos",
	CASE 
		WHEN (T1."invoice_type" = 5)
			THEN (((left((((T3."employee_number"))), 4)) + ' - ' + T3."name"))
		ELSE (T9."Serviceberater")
		END AS "Serviceberater",
	T5."order_number" AS "Order Number",
	T5."order_position" AS "Order Position",
	T5."order_position_line" AS "Order Position Line",
	T5."subsidiary" AS "Subsidiary",
	T5."is_invoiced" AS "Is Invoiced",
	T5."invoice_type" AS "Invoice Type",
	T5."invoice_number" AS "Invoice Number",
	T5."employee_no" AS "Employee No",
	T5."mechanic_no" AS "Mechanic No",
	T5."labour_operation_id" AS "Labour Operation Id",
	T5."is_nominal" AS "Is Nominal",
	T5."net_price_in_order" AS "Net Price In Order",
	T5."rebate_percent" AS "Rebate Percent",
	T5."goodwill_percent" AS "Goodwill Percent",
	T5."charge_type" AS "Charge Type",
	T5."time_units" AS "Time Units",
	T5."text_line" AS "Text Line",
	1 AS "Menge Fokus",
	(ltrim(T5."labour_operation_id")) AS "Service_Fokus_Gruppe"
FROM (
	(
		(
			(
				(
					(
						(
							(
								"dbo"."invoices" T1 LEFT JOIN "dbo"."invoice_types" T2 ON T2."type" = T1."invoice_type"
								) LEFT JOIN "dbo"."employees" T3 ON T1."creating_employee" = T3."employee_number"
							) LEFT JOIN "dbo"."customers_suppliers" T4 ON T4."customer_number" = T1."paying_customer"
						) LEFT JOIN "dbo"."labours" T5 ON (T5."invoice_number" = T1."invoice_number")
						AND (T5."invoice_type" = T1."invoice_type")
					) LEFT JOIN "dbo"."vehicles" T6 ON T1."vehicle_number" = T6."internal_number"
				) LEFT JOIN "dbo"."makes" T7 ON T6."make_number" = T7."make_number"
			) LEFT JOIN "dbo"."models" T8 ON (T6."make_number" = T8."make_number")
			AND (T6."model_code" = T8."model_code")
		) LEFT JOIN "ims"."Serviceberater_Rechnung" T9 ON T1."invtype_invnr" = T9."invtype_invnr"
	)
WHERE (
		(
			(
				(T1."invoice_type" BETWEEN 2 AND 6)
				AND (T1."invoice_date" >= convert(DATE, '2019-01-01'))
				)
			AND (T1."is_canceled" <> 1)
			)
		AND ((ltrim(T5."labour_operation_id")) IN ('KC1', 'KC2', 'BZ', 'KLIMA1', 'KLIMA2'))
		)
	-- order by "Order Number_2" asc,"Order Position" asc,"Inv_Type_Invoice_Number" asc