C11/System/ARI/IQD/Service/Service_Gutschriften_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."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."On Hold" AS "On Hold",
	T1."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group_2",
	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."No_ Series" AS "No Series",
	T1."User ID" AS "User Id",
	T1."Order Type" AS "Order Type",
	CASE 
		WHEN (T1."Service Order No_" = ' ')
			THEN ('Gutschrift ohne Auftrag')
		ELSE (T1."Service Order No_")
		END AS "Service Order No_ohne_Einschr�nkung",
	T1."Branch Code" AS "Branch Code",
	T2."VIN" AS "Vin_2",
	'' AS "Model Code",
	'' AS "Model No",
	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."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group",
	T3."Order No_" AS "Order No",
	T3."VIN" AS "Vin",
	T3."Vehicle Status" AS "Vehicle Status",
	T3."Registration Date" AS "Registration Date",
	T3."Mileage" AS "Mileage",
	CASE 
		WHEN (T3."Service Order No_" IS NULL)
			THEN ('Gutschrift ohne Auftrag')
		ELSE (T3."Service Order No_")
		END AS "Service Order No_",
	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 ('26')
		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 ('BMW I', 'BMWI', 'BMW', 'BMW-MINI', 'BMW-C1', 'BMW-ALPINA', 'BMW-MOT', 'TRIUMPH', 'ALPINA'))
			THEN (T1."Shortcut Dimension 2 Code")
		WHEN (
				(
					CASE 
						WHEN (T1."Service Order No_" = ' ')
							THEN ('Gutschrift ohne Auftrag')
						ELSE (T1."Service Order No_")
						END
					) = 'Gutschrift ohne Auftrag'
				)
			THEN ('GS ohne Auftrag')
		ELSE ('Fremdfabrikat')
		END AS "Fabrikat",
	T2."Model" AS "Model",
	T2."VIN" + ' - ' + T2."Model" AS "Fahrzeug",
	CASE 
		WHEN (T1."Shortcut Dimension 2 Code" IN ('ALPINA', 'TRIUMPH', 'BMW-C1', 'BMW-ALPINA', 'BMWI', 'BMW', 'BMW-MINI', 'BMW-MOT'))
			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",
	T1."Posting Date" AS "Invoice Date",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 180)
			THEN (
					T1."No_" + ' - ' + (
						CASE 
							WHEN (T1."Service Order No_" = ' ')
								THEN ('Gutschrift ohne Auftrag')
							ELSE (T1."Service Order No_")
							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 (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_" + ' - ' + T9."Name")
		ELSE NULL
		END AS "Order Number_Rg_Ausg_2",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 4)
			THEN (
					(
						CASE 
							WHEN (T3."Service Order No_" IS NULL)
								THEN ('Gutschrift ohne Auftrag')
							ELSE (T3."Service Order No_")
							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",
	'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 ('26')
			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 (T1."Service Order No_" = ' ')
								THEN ('Gutschrift ohne Auftrag')
							ELSE (T1."Service Order No_")
							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 ('Auftr�ge �lter 100 Tage')
		END AS "Invoice_Desc_100",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 30)
			THEN (
					(
						CASE 
							WHEN (T1."Service Order No_" = ' ')
								THEN ('Gutschrift ohne Auftrag')
							ELSE (T1."Service Order No_")
							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",
	T3."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",
	'Gutschrift' AS "Rechnung_Gutschrift",
	'' AS "Parts_Focus_Group",
	'' AS "Parts_Make_Desc",
	'' AS "Parts_Group_Desc",
	CASE 
		WHEN ((- 1 * datediff(day, (getdate()), T1."Posting Date")) <= 60)
			THEN (
					(
						CASE 
							WHEN (T1."Service Order No_" = ' ')
								THEN ('Gutschrift ohne Auftrag')
							ELSE (T1."Service Order No_")
							END
						)
					)
		ELSE ('Auftr�ge �lter 60 Tage')
		END AS "Service Order No"
FROM "ims"."Add_Service_ledger_mit_Time_Clock_Entry_fuer_Service_Rg_Ausg_ims" T12,
	(
		(
			(
				(
					(
						"ARI"."import"."Sales Credit Memo Header" T1 LEFT JOIN "ARI"."import"."Vehicle" T2 ON T1."Supply VIN" = T2."VIN"
						) LEFT JOIN "ARI"."import"."Customer" T9 ON T1."Bill-to Customer No_" = T9."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 Credit Memo Line" T3 LEFT JOIN "ARI"."import"."Archived Service Header" T4 ON T4."No_" = T3."Service Order No_"
					) LEFT JOIN "ARI"."import"."Service Header" T5 ON T5."No_" = T3."Service Order 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 (
		(T12."Document No_" = T1."No_")
		AND (T12."Order No_" = T1."Service Order No_")
		)
	AND (
		(
			(
				(T1."No_" LIKE 'W%')
				OR (T1."No_" LIKE '%I%')
				)
			AND (NOT T3."Type" IN (0, 11, 12))
			)
		AND (T1."Posting Date" >= convert(DATETIME, '2022-01-01 00:00:00.000'))
		)
	-- order by "No_2" asc