C11/System/ARI/IQD/OP/OP_ARI.sql

SELECT "Entry No_2" AS "Entry No_2",
	"Customer No_2" AS "Customer No_2",
	"Posting Date_2" AS "Posting Date_2",
	"Document Type" AS "Document Type",
	"Document No" AS "Document No",
	"Description" AS "Description",
	"Sales (lcy)" AS "Sales (lcy)",
	"Profit (lcy)" AS "Profit (lcy)",
	"Sell-to Customer No" AS "Sell-to Customer No",
	"Customer Posting Group" AS "Customer Posting Group",
	"Global Dimension 1 Code" AS "Global Dimension 1 Code",
	"Global Dimension 2 Code" AS "Global Dimension 2 Code",
	"Salesperson Code" AS "Salesperson Code",
	"User Id" AS "User Id",
	"Source Code_2" AS "Source Code_2",
	"On Hold_ori" AS "On Hold_ori",
	"Applies-to Doc Type" AS "Applies-to Doc Type",
	"Applies-to Doc No" AS "Applies-to Doc No",
	"Open" AS "Open",
	"Due Date" AS "Due Date",
	"Pmt Discount Date" AS "Pmt Discount Date",
	"Positive" AS "Positive",
	"Closed By Entry No" AS "Closed By Entry No",
	"Closed At Date" AS "Closed At Date",
	"Closed By Amount" AS "Closed By Amount",
	"Journal Batch Name" AS "Journal Batch Name",
	"Closed By Amount (lcy)" AS "Closed By Amount (lcy)",
	"Document Date" AS "Document Date",
	"No Series" AS "No Series",
	"Last Issued Reminder Level" AS "Last Issued Reminder Level",
	"Dimension Set Id" AS "Dimension Set Id",
	"Direct Debit Mandate Id" AS "Direct Debit Mandate Id",
	"Customer Group Code" AS "Customer Group Code",
	"Branch Code" AS "Branch Code",
	"Main Area" AS "Main Area",
	"Pmt Disc Base" AS "Pmt Disc Base",
	"Vin" AS "Vin",
	"Factory No" AS "Factory No",
	"Cash Reg Receipt No" AS "Cash Reg Receipt No",
	"Comment" AS "Comment",
	"Service Advisor No" AS "Service Advisor No",
	"Salesperson Code 2" AS "Salesperson Code 2",
	"Entry No" AS "Entry No",
	"Cust Ledger Entry No" AS "Cust Ledger Entry No",
	"Entry Type" AS "Entry Type",
	"Posting Date" AS "Posting Date",
	"Document Type_detail_cust" AS "Document Type_detail_cust",
	"Document No_detail" AS "Document No_detail",
	"Amount" AS "Amount",
	"Customer No" AS "Customer No",
	"User Id_detail_cust" AS "User Id_detail_cust",
	"Source Code" AS "Source Code",
	"Debit Amount" AS "Debit Amount",
	"Credit Amount" AS "Credit Amount",
	"Initial Entry Due Date" AS "Initial Entry Due Date",
	"Initial Entry Global Dim 1" AS "Initial Entry Global Dim 1",
	"Initial Entry Global Dim 2" AS "Initial Entry Global Dim 2",
	"Gen Bus Posting Group" AS "Gen Bus Posting Group",
	"Gen Prod Posting Group" AS "Gen Prod Posting Group",
	"Initial Document Type" AS "Initial Document Type",
	"Saldo_Beleg_1" AS "Saldo_Beleg_1",
	"Anzahl_S�tze_Entry_No" AS "Anzahl_S�tze_Entry_No",
	"offen" AS "offen",
	SUM("offen") OVER (PARTITION BY "Customer No_2") AS "Gesamt offen KD (Info)",
	"Invoice Date" AS "Invoice Date",
	"Hauptbetrieb" AS "Hauptbetrieb",
	"Standort" AS "Standort",
	"Sel Name_alt" AS "Sel Name_alt",
	"No_Customer" AS "No_Customer",
	"Name_Customer" AS "Name_Customer",
	"Last Name_Customer" AS "Last Name_Customer",
	"First Name_Customer" AS "First Name_Customer",
	"Kunde" AS "Kunde",
	"Beleg" AS "Beleg",
	"Bereich" AS "Bereich",
	"Tage" AS "Tage",
	"Staffel" AS "Staffel",
	"Mahnstufe" AS "Mahnstufe",
	"Forderungsart" AS "Forderungsart",
	"Abwarten" AS "Abwarten",
	"Leasing" AS "Leasing",
	"Salesperson Code_Sales_Inv_Header" AS "Salesperson Code_Sales_Inv_Header",
	"Service Advisor No_Sales_Inv_Header" AS "Service Advisor No_Sales_Inv_Header",
	"Code_Salesperson_Purchaser_Verkauf" AS "Code_Salesperson_Purchaser_Verkauf",
	"Name_Salesperson_Purchaser_Verkauf" AS "Name_Salesperson_Purchaser_Verkauf",
	"Code_Salesperson_Purchaser_Service" AS "Code_Salesperson_Purchaser_Service",
	"Name_Salesperson_Purchaser_Service" AS "Name_Salesperson_Purchaser_Service",
	"No_Employee_Service_Advisor" AS "No_Employee_Service_Advisor",
	"First Name_Employee_Service_Advisor" AS "First Name_Employee_Service_Advisor",
	"Last Name_Employee_Service_Advisor" AS "Last Name_Employee_Service_Advisor",
	"No_Employee_Salesperson_Code" AS "No_Employee_Salesperson_Code",
	"First Name_Employee_Salesperson_Code" AS "First Name_Employee_Salesperson_Code",
	"Last Name_Employee_Salesperson_Code" AS "Last Name_Employee_Salesperson_Code",
	"Sel Name_ori" AS "Sel Name_ori",
	"Sel Name" AS "Sel Name",
	"Betrag Rg." AS "Betrag Rg.",
	"Message To Recipient" AS "Message To Recipient",
	"Haft_Kasko" AS "Haft_Kasko",
	"On Hold" AS "On Hold"
FROM (
	SELECT "Entry No_2",
		"Customer No_2",
		"Posting Date_2",
		"Document Type",
		"Document No",
		"Description",
		"Sales (lcy)" AS "Sales (lcy)",
		"Profit (lcy)" AS "Profit (lcy)",
		"Sell-to Customer No",
		"Customer Posting Group",
		"Global Dimension 1 Code",
		"Global Dimension 2 Code",
		"Salesperson Code",
		"User Id",
		"Source Code_2",
		"On Hold_ori",
		"Applies-to Doc Type",
		"Applies-to Doc No",
		"Open",
		"Due Date",
		"Pmt Discount Date",
		"Positive",
		"Closed By Entry No",
		"Closed At Date",
		"Closed By Amount",
		"Journal Batch Name",
		"Closed By Amount (lcy)" AS "Closed By Amount (lcy)",
		"Document Date",
		"No Series",
		"Last Issued Reminder Level",
		"Dimension Set Id",
		"Direct Debit Mandate Id",
		"Customer Group Code",
		"Branch Code",
		"Main Area",
		"Pmt Disc Base",
		"Vin",
		"Factory No",
		"Cash Reg Receipt No",
		"Comment",
		"Service Advisor No",
		"Salesperson Code 2",
		"Entry No",
		"Cust Ledger Entry No",
		"Entry Type",
		"Posting Date",
		"Document Type_detail_cust",
		"Document No_detail",
		"Amount",
		"Customer No",
		"User Id_detail_cust",
		"Source Code",
		"Debit Amount",
		"Credit Amount",
		"Initial Entry Due Date",
		"Initial Entry Global Dim 1",
		"Initial Entry Global Dim 2",
		"Gen Bus Posting Group",
		"Gen Prod Posting Group",
		"Initial Document Type",
		SUM("Amount") OVER (PARTITION BY "Entry No_2") AS "Saldo_Beleg_1",
		COUNT("Customer No_2") OVER (PARTITION BY "Entry No_2") AS "Anzahl_S�tze_Entry_No",
		(SUM("Amount") OVER (PARTITION BY "Entry No_2")) / (COUNT("Customer No_2") OVER (PARTITION BY "Entry No_2")) AS "offen",
		"Due Date" AS "Invoice Date",
		'1' AS "Hauptbetrieb",
		"Standort",
		"User Id" AS "Sel Name_alt",
		"No_Customer",
		"Name_Customer",
		"Last Name_Customer",
		"First Name_Customer",
		"Kunde",
		"Beleg",
		"Bereich",
		"Tage",
		"Staffel",
		"Last Issued Reminder Level" AS "Mahnstufe",
		"Forderungsart",
		"Abwarten",
		'' AS "Leasing",
		"Salesperson Code_Sales_Inv_Header",
		"Service Advisor No_Sales_Inv_Header",
		"Code_Salesperson_Purchaser_Verkauf",
		"Name_Salesperson_Purchaser_Verkauf",
		"Code_Salesperson_Purchaser_Service",
		"Name_Salesperson_Purchaser_Service",
		"No_Employee_Service_Advisor",
		"First Name_Employee_Service_Advisor",
		"Last Name_Employee_Service_Advisor",
		"No_Employee_Salesperson_Code",
		"First Name_Employee_Salesperson_Code",
		"Last Name_Employee_Salesperson_Code",
		"Sel Name_ori",
		"Sel Name",
		("Sales (lcy)") / (COUNT("Customer No_2") OVER (PARTITION BY "Entry No_2")) AS "Betrag Rg.",
		"Message To Recipient",
		"Haft_Kasko",
		"On Hold"
	FROM (
		SELECT T1."Entry No_" AS "Entry No_2",
			CASE 
				WHEN (T1."On Hold" IN ('HAF', 'KAS', 'UNF'))
					THEN ('UNF')
				ELSE (T1."On Hold")
				END AS "On Hold",
			CASE 
				WHEN (T1."On Hold" = 'HAF')
					THEN ('HAFT')
				WHEN (T1."On Hold" = 'KAS')
					THEN ('KASKO')
				WHEN (T1."On Hold" = 'WSS')
					THEN ('WSS')
				ELSE NULL
				END AS "Haft_Kasko",
			T1."Message to Recipient" AS "Message To Recipient",
			(convert(FLOAT, T1."Sales (LCY)")) AS "Sales (lcy)",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (T1."Main Area" = 0)
													THEN ('Sonstige')
												WHEN (T1."Main Area" = 1)
													THEN ('TZ')
												WHEN (T1."Main Area" = 2)
													THEN ('Verkauf')
												WHEN (T1."Main Area" = 3)
													THEN ('Service')
												WHEN (T1."Main Area" = 5)
													THEN ('Kassenbuchung')
												ELSE ('nicht zuzuordnen')
												END
											) IN ('Service')
										)
									THEN (T7."No_" + ' - ' + T7."First Name" + ' ' + T7."Last Name")
								WHEN (
										(
											CASE 
												WHEN (T1."Main Area" = 0)
													THEN ('Sonstige')
												WHEN (T1."Main Area" = 1)
													THEN ('TZ')
												WHEN (T1."Main Area" = 2)
													THEN ('Verkauf')
												WHEN (T1."Main Area" = 3)
													THEN ('Service')
												WHEN (T1."Main Area" = 5)
													THEN ('Kassenbuchung')
												ELSE ('nicht zuzuordnen')
												END
											) IN ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')
										)
									THEN (T5."Code" + ' - ' + T5."Name")
								ELSE ('N.N.')
								END
							) IS NULL
						)
					THEN ('N.N.')
				ELSE (
						(
							CASE 
								WHEN (
										(
											CASE 
												WHEN (T1."Main Area" = 0)
													THEN ('Sonstige')
												WHEN (T1."Main Area" = 1)
													THEN ('TZ')
												WHEN (T1."Main Area" = 2)
													THEN ('Verkauf')
												WHEN (T1."Main Area" = 3)
													THEN ('Service')
												WHEN (T1."Main Area" = 5)
													THEN ('Kassenbuchung')
												ELSE ('nicht zuzuordnen')
												END
											) IN ('Service')
										)
									THEN (T7."No_" + ' - ' + T7."First Name" + ' ' + T7."Last Name")
								WHEN (
										(
											CASE 
												WHEN (T1."Main Area" = 0)
													THEN ('Sonstige')
												WHEN (T1."Main Area" = 1)
													THEN ('TZ')
												WHEN (T1."Main Area" = 2)
													THEN ('Verkauf')
												WHEN (T1."Main Area" = 3)
													THEN ('Service')
												WHEN (T1."Main Area" = 5)
													THEN ('Kassenbuchung')
												ELSE ('nicht zuzuordnen')
												END
											) IN ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')
										)
									THEN (T5."Code" + ' - ' + T5."Name")
								ELSE ('N.N.')
								END
							)
						)
				END AS "Sel Name",
			CASE 
				WHEN (
						(
							CASE 
								WHEN (T1."Main Area" = 0)
									THEN ('Sonstige')
								WHEN (T1."Main Area" = 1)
									THEN ('TZ')
								WHEN (T1."Main Area" = 2)
									THEN ('Verkauf')
								WHEN (T1."Main Area" = 3)
									THEN ('Service')
								WHEN (T1."Main Area" = 5)
									THEN ('Kassenbuchung')
								ELSE ('nicht zuzuordnen')
								END
							) IN ('Service')
						)
					THEN (T7."No_" + ' - ' + T7."First Name" + ' ' + T7."Last Name")
				WHEN (
						(
							CASE 
								WHEN (T1."Main Area" = 0)
									THEN ('Sonstige')
								WHEN (T1."Main Area" = 1)
									THEN ('TZ')
								WHEN (T1."Main Area" = 2)
									THEN ('Verkauf')
								WHEN (T1."Main Area" = 3)
									THEN ('Service')
								WHEN (T1."Main Area" = 5)
									THEN ('Kassenbuchung')
								ELSE ('nicht zuzuordnen')
								END
							) IN ('Verkauf', 'Kassenbuchung', 'Sonstige', 'TZ')
						)
					THEN (T5."Code" + ' - ' + T5."Name")
				ELSE ('N.N.')
				END AS "Sel Name_ori",
			T8."Last Name" AS "Last Name_Employee_Salesperson_Code",
			T8."First Name" AS "First Name_Employee_Salesperson_Code",
			T8."No_" AS "No_Employee_Salesperson_Code",
			T7."Last Name" AS "Last Name_Employee_Service_Advisor",
			T7."First Name" AS "First Name_Employee_Service_Advisor",
			T7."No_" AS "No_Employee_Service_Advisor",
			T6."Name" AS "Name_Salesperson_Purchaser_Service",
			T6."Code" AS "Code_Salesperson_Purchaser_Service",
			T5."Name" AS "Name_Salesperson_Purchaser_Verkauf",
			T5."Code" AS "Code_Salesperson_Purchaser_Verkauf",
			T4."Service Advisor No_" AS "Service Advisor No_Sales_Inv_Header",
			T4."Salesperson Code" AS "Salesperson Code_Sales_Inv_Header",
			CASE 
				WHEN (T1."Customer No_" LIKE '%Garan%')
					THEN ('Garantie')
				WHEN (
						(
							CASE 
								WHEN (T3."First Name" <> '')
									THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_")
								ELSE (T3."Name" + ' - ' + T3."No_")
								END
							) LIKE '%BMW%'
						)
					THEN (
							(
								CASE 
									WHEN (T3."First Name" <> '')
										THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_")
									ELSE (T3."Name" + ' - ' + T3."No_")
									END
								)
							)
				WHEN (T1."Customer No_" LIKE '%ASI%')
					THEN (
							(
								CASE 
									WHEN (T3."First Name" <> '')
										THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_")
									ELSE (T3."Name" + ' - ' + T3."No_")
									END
								)
							)
				WHEN (
						(
							CASE 
								WHEN (T3."First Name" <> '')
									THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_")
								ELSE (T3."Name" + ' - ' + T3."No_")
								END
							) LIKE '%Automag%'
						)
					THEN ('Intern')
				ELSE ('Kundenforderungen')
				END AS "Abwarten",
			CASE 
				WHEN (T1."Customer No_" LIKE '%Garan%')
					THEN ('Garantie')
				ELSE ('Kundenforderungen')
				END AS "Forderungsart",
			T1."Last Issued Reminder Level" AS "Last Issued Reminder Level",
			CASE 
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) BETWEEN 0 AND 14)
					THEN ('< 2 Wochen')
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) BETWEEN 15 AND 28)
					THEN ('2 - 4 Wochen')
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) BETWEEN 29 AND 42)
					THEN ('4 - 6 Wochen')
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) BETWEEN 43 AND 84)
					THEN ('6 - 12 Wochen')
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) > 84)
					THEN ('> 12 Wochen')
				WHEN (((- 1 * datediff(day, (getdate()), T1."Due Date"))) < 0)
					THEN ('noch nicht f�llig')
				ELSE NULL
				END AS "Staffel",
			(- 1 * datediff(day, (getdate()), T1."Due Date")) AS "Tage",
			CASE 
				WHEN (T1."Main Area" = 0)
					THEN ('Sonstige')
				WHEN (T1."Main Area" = 1)
					THEN ('TZ')
				WHEN (T1."Main Area" = 2)
					THEN ('Verkauf')
				WHEN (T1."Main Area" = 3)
					THEN ('Service')
				WHEN (T1."Main Area" = 5)
					THEN ('Kassenbuchung')
				ELSE ('nicht zuzuordnen')
				END AS "Bereich",
			CASE 
				WHEN (T1."Comment" <> '')
					THEN (T1."Document No_" + ' - ' + T1."Description" + ' - ' + T1."Comment" + ' - ' + T1."User ID" + ' - MS:' + (((T1."Last Issued Reminder Level"))))
				ELSE (T1."Document No_" + ' - ' + T1."Description" + ' - ' + T1."User ID" + ' - MS:' + (((T1."Last Issued Reminder Level"))))
				END AS "Beleg",
			CASE 
				WHEN (T3."First Name" <> '')
					THEN (T3."Last Name" + ', ' + T3."First Name" + ' - ' + T3."No_")
				ELSE (T3."Name" + ' - ' + T3."No_")
				END AS "Kunde",
			T3."First Name" AS "First Name_Customer",
			T3."Last Name" AS "Last Name_Customer",
			T3."Name" AS "Name_Customer",
			T3."No_" AS "No_Customer",
			T1."User ID" AS "User Id",
			CASE 
				WHEN (T1."Branch Code" IN ('01BSPKW'))
					THEN ('10')
				WHEN (T1."Branch Code" IN ('02BSMOT'))
					THEN ('20')
				WHEN (T1."Branch Code" IN ('04SFH'))
					THEN ('40')
				WHEN (T1."Branch Code" IN ('05WT'))
					THEN ('50')
				WHEN (T1."Branch Code" IN ('06BI'))
					THEN ('60')
				WHEN (T1."Branch Code" IN ('07TR'))
					THEN ('70')
				ELSE (T1."Branch Code")
				END AS "Standort",
			T1."Due Date" AS "Due Date",
			T2."Initial Document Type" AS "Initial Document Type",
			T2."Gen_ Prod_ Posting Group" AS "Gen Prod Posting Group",
			T2."Gen_ Bus_ Posting Group" AS "Gen Bus Posting Group",
			T2."Initial Entry Global Dim_ 2" AS "Initial Entry Global Dim 2",
			T2."Initial Entry Global Dim_ 1" AS "Initial Entry Global Dim 1",
			T2."Initial Entry Due Date" AS "Initial Entry Due Date",
			(convert(FLOAT, T2."Credit Amount")) AS "Credit Amount",
			(convert(FLOAT, T2."Debit Amount")) AS "Debit Amount",
			T2."Source Code" AS "Source Code",
			T2."User ID" AS "User Id_detail_cust",
			T2."Customer No_" AS "Customer No",
			(convert(FLOAT, T2."Amount")) AS "Amount",
			T2."Document No_" AS "Document No_detail",
			T2."Document Type" AS "Document Type_detail_cust",
			T2."Posting Date" AS "Posting Date",
			T2."Entry Type" AS "Entry Type",
			T2."Cust_ Ledger Entry No_" AS "Cust Ledger Entry No",
			T2."Entry No_" AS "Entry No",
			T1."Salesperson Code 2" AS "Salesperson Code 2",
			T1."Service Advisor No_" AS "Service Advisor No",
			T1."Comment" AS "Comment",
			T1."Cash Reg_ Receipt No_" AS "Cash Reg Receipt No",
			T1."Factory No_" AS "Factory No",
			T1."VIN" AS "Vin",
			T1."Pmt_ Disc_ Base" AS "Pmt Disc Base",
			T1."Main Area" AS "Main Area",
			T1."Branch Code" AS "Branch Code",
			T1."Customer Group Code" AS "Customer Group Code",
			T1."Direct Debit Mandate ID" AS "Direct Debit Mandate Id",
			T1."Dimension Set ID" AS "Dimension Set Id",
			T1."No_ Series" AS "No Series",
			T1."Document Date" AS "Document Date",
			T1."Closed by Amount (LCY)" AS "Closed By Amount (lcy)",
			T1."Journal Batch Name" AS "Journal Batch Name",
			T1."Closed by Amount" AS "Closed By Amount",
			T1."Closed at Date" AS "Closed At Date",
			T1."Closed by Entry No_" AS "Closed By Entry No",
			T1."Positive" AS "Positive",
			T1."Pmt_ Discount Date" AS "Pmt Discount Date",
			T1."Open" AS "Open",
			T1."Applies-to Doc_ No_" AS "Applies-to Doc No",
			T1."Applies-to Doc_ Type" AS "Applies-to Doc Type",
			T1."On Hold" AS "On Hold_ori",
			T1."Source Code" AS "Source Code_2",
			T1."Salesperson Code" AS "Salesperson Code",
			T1."Global Dimension 2 Code" AS "Global Dimension 2 Code",
			T1."Global Dimension 1 Code" AS "Global Dimension 1 Code",
			T1."Customer Posting Group" AS "Customer Posting Group",
			T1."Sell-to Customer No_" AS "Sell-to Customer No",
			(convert(FLOAT, T1."Profit (LCY)")) AS "Profit (lcy)",
			T1."Description" AS "Description",
			T1."Document No_" AS "Document No",
			T1."Document Type" AS "Document Type",
			T1."Posting Date" AS "Posting Date_2",
			T1."Customer No_" AS "Customer No_2"
		FROM "Gottstein7x"."dbo"."AH Gottstein$Detailed Cust_ Ledg_ Entry" T2,
			(
				(
					(
						(
							(
								(
									"Gottstein7x"."dbo"."AH Gottstein$Cust_ Ledger Entry" T1 LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Customer" T3 ON T1."Customer No_" = T3."No_"
									) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Sales Invoice Header" T4 ON T1."Document No_" = T4."No_"
								) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Salesperson_Purchaser" T5 ON T1."Salesperson Code" = T5."Code"
							) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Salesperson_Purchaser" T6 ON T1."Service Advisor No_" = T6."Code"
						) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee" T7 ON T4."Service Advisor No_" = T7."No_"
					) LEFT JOIN "Gottstein7x"."dbo"."AH Gottstein$Employee" T8 ON T4."Salesperson Code" = T8."No_"
				)
		WHERE (T1."Entry No_" = T2."Cust_ Ledger Entry No_")
			AND (T1."Open" = 1)
		) D2
	) D1
	-- order by "Customer No_2" asc,"Entry No_2" asc