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

SELECT DISTINCT T1."No_" AS "No_2",
	T1."Sell-to Customer No_" AS "Sell-to Customer No",
	T1."Bill-to Customer No_" AS "Bill-to Customer No",
	T1."Bill-to Name" AS "Bill-to Name",
	T1."Bill-to Address" AS "Bill-to Address",
	T1."Bill-to City" AS "Bill-to City",
	T1."Order Date" AS "Order Date",
	T1."Posting Date" AS "Posting Date",
	T1."Payment Terms Code" AS "Payment Terms Code",
	T1."Location Code" AS "Location Code",
	T1."Shortcut Dimension 1 Code" AS "Department Code_2",
	T1."Shortcut Dimension 2 Code" AS "Make Code_2",
	T1."Customer Posting Group" AS "Customer Posting Group",
	T1."Price Group Code" AS "Price Group Code",
	T1."Prices Including VAT" AS "Prices Including Vat",
	T1."Allow Quantity Disc_" AS "Allow Quantity Disc",
	T1."Salesperson Code" AS "Salesperson Code",
	T1."Order No_" AS "Order No_2",
	T1."On Hold" AS "On Hold",
	T1."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group",
	T1."Transaction Type" AS "Transaction Type",
	T1."Sell-to Customer Name" AS "Sell-to Customer Name",
	T1."Sell-to Address" AS "Sell-to Address",
	T1."Sell-to City" AS "Sell-to City",
	T1."Correction" AS "Correction",
	T1."Document Date" AS "Document Date",
	T1."External Document No_" AS "External Document No",
	T1."Area" AS "Area",
	T1."Shipping Agent Code" AS "Shipping Agent Code",
	T1."No_ Series" AS "No Series",
	T1."Order No_ Series" AS "Order No Series",
	T1."User ID" AS "User Id",
	T1."Order Type" AS "Order Type_2",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
			THEN (T1."Service Order No_")
		ELSE ('Auftr�ge �lter 60 Tage')
		END AS "Service Order No",
	T1."Customer Group Code" AS "Customer Group Code_2",
	T1."Branch Code" AS "Branch Code",
	T2."VIN" AS "Vin_2",
	T2."Model" AS "Model_ori",
	T3."Document No_" AS "Document No",
	T3."Shortcut Dimension 1 Code" AS "Department Code",
	T3."Shortcut Dimension 2 Code" AS "Make Code",
	T3."Order No_" AS "Order No",
	T3."Order Type" AS "Order Type",
	T3."VIN" AS "Vin",
	T3."Vehicle Status" AS "Vehicle Status",
	T3."Registration Date" AS "Registration Date",
	T3."Mileage" AS "Mileage",
	T3."Customer Group Code" AS "Customer Group Code",
	T4."Service Advisor No_" AS "Service Advisor No_Archiv",
	T5."Service Advisor No_" AS "Service Advisor No_oA",
	T6."No_" AS "No_f�r_Archiv",
	T6."First Name" AS "First Name_f�r_Archiv",
	T6."Last Name" AS "Last Name_f�r_Archiv",
	T7."No_" AS "No",
	T7."First Name" AS "First Name",
	T7."Last Name" AS "Last Name",
	CASE 
		WHEN (T6."No_" IS NULL)
			THEN (T7."First Name" + ' ' + T7."Last Name")
		ELSE (T6."First Name" + ' ' + T6."Last Name")
		END AS "Serviceberater",
	'1' AS "Hauptbetrieb",
	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",
	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 (T1."Shortcut Dimension 2 Code" IN ('ALPINA', 'BMW', 'BMW-ALPINA', 'BMW-C1', 'BMWI', 'BMW-MINI', 'BMW-MOT', 'TRIUMPH'))
			THEN (T1."Shortcut Dimension 2 Code")
		ELSE ('Fremdfabrikat')
		END AS "Fabrikat",
	T2."Model" AS "Model",
	T2."VIN" + ' - ' + T2."Model" AS "Fahrzeug",
	CASE 
		WHEN (T1."Shortcut Dimension 2 Code" IN ('ALPINA', 'BMW', 'BMW-ALPINA', 'BMW-C1', 'BMWI', 'BMW-MINI', 'BMW-MOT', 'TRIUMPH'))
			THEN (T1."Shortcut Dimension 2 Code")
		ELSE ('Fremdfabrikat')
		END AS "Marke",
	T4."Service Posting Group" AS "Service Posting Group_f�r_Archiv",
	T5."Service Posting Group" AS "Service Posting Group",
	CASE 
		WHEN (T4."Service Posting Group" IS NULL)
			THEN (T5."Service Posting Group")
		ELSE (T4."Service Posting Group")
		END AS "Auftragsart",
	T8."Code" AS "Cust_Gr_Code",
	T8."Description" AS "Cust_Gr_Description",
	CASE 
		WHEN (T1."Bill-to Customer No_" LIKE 'INT%')
			THEN ('Intern')
		ELSE (T8."Description")
		END AS "Kundenart",
	T9."No_" AS "Cust_No",
	T9."Name" AS "Cust_Name",
	T9."No_" + ' - ' + T9."Name" AS "Kunde",
	'' AS "Auftragsart_1",
	'' AS "Function Code",
	'' AS "Monteur_2",
	T1."Posting Date" AS "Invoice Date",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 180)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
								THEN (T1."Service Order No_")
							ELSE ('Auftr�ge �lter 60 Tage')
							END
						) + ' - ' + T9."Name"
					)
		ELSE ('Auftr�ge �lter 180 Tage')
		END AS "Order Number",
	CASE 
		WHEN (
				((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
				AND (
					(
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) IS NOT NULL
					)
				)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) + ' - ' + T9."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 (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) IS NULL
					)
				)
			THEN (T1."No_" + ' - ' + 'SB fehlt' + ' - ' + T9."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 AS "Order Number_Rg_Ausg",
	CASE 
		WHEN (
				((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
				AND (
					(
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) IS NOT NULL
					)
				)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN (T4."Service Posting Group" IS NULL)
								THEN (T5."Service Posting Group")
							ELSE (T4."Service Posting Group")
							END
						) + ' - ' + (
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) + ' - ' + T9."Name"
					)
		WHEN (
				((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
				AND (
					(
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) IS NULL
					)
				)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN (T4."Service Posting Group" IS NULL)
								THEN (T5."Service Posting Group")
							ELSE (T4."Service Posting Group")
							END
						) + ' - ' + T9."Name"
					)
		ELSE NULL
		END AS "Order Number_Rg_Ausg_2",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
			THEN (
					(
						CASE 
							WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
								THEN (T1."Service Order No_")
							ELSE ('Auftr�ge �lter 60 Tage')
							END
						)
					)
		ELSE NULL
		END AS "Order Number_Rg_Ausg_1",
	T10."No_" AS "Cust_No_Verkaufskunde",
	T10."Name" AS "Cust_Name_Verkaufskunde",
	T11."Description" AS "Cust_Group_Description_Verkaufskunde",
	CASE 
		WHEN (T1."Sell-to Customer No_" LIKE 'INT%')
			THEN ('Intern')
		ELSE (T11."Description")
		END AS "Kundenart_Verkaufskunde",
	T10."No_" + ' - ' + T10."Name" AS "Kunde_Verkaufskunde",
	T12."Duration_Time_Clock" AS "Duration Time Clock_Add_Service_Time_Clock_ims",
	T12."Monteur" AS "Monteur",
	T12."Monteur" AS "Auftragsposition",
	T12."Duration_Time_Clock" * 12 AS "ben. AW_Time_Clock",
	(T12."Duration_Time_Clock" * 12) AS "ben Zeit",
	'1' AS "Rechtseinheit_ID",
	(
		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_ID",
	'Serviceberater' AS "Zuordnung_Funktion",
	T1."Shortcut Dimension 1 Code" AS "Cost_Centre_ID",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 100)
			THEN (
					(
						CASE 
							WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
								THEN (T1."Service Order No_")
							ELSE ('Auftr�ge �lter 60 Tage')
							END
						) + ' - ' + T9."Name"
					)
		ELSE ('Auftr�ge �lter 100 Tage')
		END AS "Order_Desc_100",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 100)
			THEN (T1."No_" + ' - ' + T9."Name")
		ELSE ('Rechnungen �lter 100 Tage')
		END AS "Invoice_Desc_100",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
			THEN (
					(
						CASE 
							WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
								THEN (T1."Service Order No_")
							ELSE ('Auftr�ge �lter 60 Tage')
							END
						)
					)
		ELSE ('Auftr�ge �lter 30 Tage')
		END AS "Order_Desc_30",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN (T6."No_" IS NULL)
								THEN (T7."First Name" + ' ' + T7."Last Name")
							ELSE (T6."First Name" + ' ' + T6."Last Name")
							END
						) + ' - ' + T9."Name"
					)
		ELSE ('Rechnungen �lter 30 Tage')
		END AS "Invoice_Desc_30",
	T1."Gen_ Bus_ Posting Group" AS "Customer_Group_Owner",
	T9."Name" AS "Customer_Name_Owner",
	T2."VIN" AS "Fahrgestellnummer",
	T2."Model" AS "Model_Desc",
	T1."Gen_ Bus_ Posting Group" AS "Produktbuchungsgruppe",
	CASE 
		WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
			THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
		ELSE (0)
		END AS "Fahrzeugalter_Tage",
	(
		CASE 
			WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
				THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
			ELSE (0)
			END
		) / 365 AS "Fahrzeugalter",
	CASE 
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 0.01 AND 0.99
				)
			THEN ('1')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 1.00 AND 1.99
				)
			THEN ('2')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 2.00 AND 2.99
				)
			THEN ('3')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 3.00 AND 3.99
				)
			THEN ('4')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 4.00 AND 4.99
				)
			THEN ('5')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 5.00 AND 5.99
				)
			THEN ('6')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 6.00 AND 6.99
				)
			THEN ('7')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 7.00 AND 7.99
				)
			THEN ('8')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 8.00 AND 8.99
				)
			THEN ('9')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 BETWEEN 9.00 AND 9.99
				)
			THEN ('10')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 > 9.99
				)
			THEN ('> 10')
		WHEN (
				(
					CASE 
						WHEN (T3."Registration Date" <> convert(DATETIME, '1753-01-01 00:00:00.000'))
							THEN ((- 1 * datediff(day, T1."Posting Date", T3."Registration Date")))
						ELSE (0)
						END
					) / 365 = 0
				)
			THEN ('keine Angabe')
		ELSE NULL
		END AS "FZG-Altersstaffel",
	CASE 
		WHEN (T1."Gen_ Bus_ Posting Group" LIKE '%LOHN%')
			THEN (T1."Gen_ Bus_ Posting Group")
		ELSE NULL
		END AS "Repair_Group_Desc",
	'Rechnung' AS "Rechnung_Gutschrift",
	'' AS "Parts_Focus_Group",
	'' AS "Parts_Make_Desc",
	'' AS "Parts_Group_Desc"
FROM "ims"."Add_Service_ledger_mit_Time_Clock_Entry_fuer_Service_Rg_Ausg_ims" T12,
	(
		(
			(
				(
					(
						"ARI"."import"."Sales Invoice Header" T1 LEFT JOIN "ARI"."import"."Vehicle" T2 ON T1."Supply VIN" = T2."VIN"
						) LEFT JOIN "ARI"."import"."Customer" T9 ON T9."No_" = T1."Bill-to Customer No_"
					) LEFT JOIN "ARI"."import"."Customer Group" T8 ON T9."Customer Group Code" = T8."Code"
				) LEFT JOIN "ARI"."import"."Customer" T10 ON T10."No_" = T1."Sell-to Customer No_"
			) LEFT JOIN "ARI"."import"."Customer Group" T11 ON T10."Customer Group Code" = T11."Code"
		),
	(
		(
			(
				(
					"ARI"."import"."Sales Invoice Line" T3 LEFT JOIN "ARI"."import"."Archived Service Header" T4 ON T3."Service Order No_" = T4."No_"
					) LEFT JOIN "ARI"."import"."Service Header" T5 ON T3."Service Order No_" = T5."No_"
				) LEFT JOIN "ARI"."import"."Employee" T6 ON T4."Service Advisor No_" = T6."No_"
			) LEFT JOIN "ARI"."import"."Employee" T7 ON T5."Service Advisor No_" = T7."No_"
		)
WHERE (T1."No_" = T3."Document No_")
	AND (
		(T1."No_" = T12."Document No_")
		AND (T1."Service Order No_" = T12."Order No_")
		)
	AND (
		(
			(
				((left(T1."No_", 1)) IN ('I', 'W'))
				AND (NOT T3."Type" IN (0, 11, 12))
				)
			AND (T1."Posting Date" >= convert(DATETIME, '2024-03-01 00:00:00.000'))
			)
		AND (
			NOT (
				CASE 
					WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
						THEN (T1."Service Order No_")
					ELSE ('Auftr�ge �lter 60 Tage')
					END
				) IN ('NASISPA')
			)
		)
	-- order by "No_2" asc