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_2" AS "Document Type_2",
	"Document No_2" AS "Document No_2",
	"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_2" AS "User Id_2",
	"Source Code_2" AS "Source Code_2",
	"On Hold" AS "On Hold",
	"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" AS "Document Type",
	"Document No" AS "Document No",
	"Amount" AS "Amount",
	"Customer No" AS "Customer No",
	"User Id" AS "User Id",
	"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" AS "Sel Name",
	"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"
FROM (
	SELECT "Entry No_2",
		"Customer No_2",
		"Posting Date_2",
		"Document Type_2",
		"Document No_2",
		"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_2",
		"Source Code_2",
		"On Hold",
		"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",
		"Document No",
		"Amount",
		"Customer No",
		"User Id",
		"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_2" AS "Sel Name",
		"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"
	FROM (
		SELECT T1."Entry No_" AS "Entry No_2",
			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
								)
							)
				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_2",
			CASE 
				WHEN (T1."Branch Code" = 'BUR')
					THEN ('10')
				WHEN (T1."Branch Code" = 'MUE')
					THEN ('20')
				ELSE NULL
				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",
			T2."Customer No_" AS "Customer No",
			(convert(FLOAT, T2."Amount")) AS "Amount",
			T2."Document No_" AS "Document No",
			T2."Document Type" AS "Document Type",
			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",
			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)",
			(convert(FLOAT, T1."Sales (LCY)")) AS "Sales (lcy)",
			T1."Description" AS "Description",
			T1."Document No_" AS "Document No_2",
			T1."Document Type" AS "Document Type_2",
			T1."Posting Date" AS "Posting Date_2",
			T1."Customer No_" AS "Customer No_2"
		FROM "Vogl7x"."dbo"."BMW AH Vogl$Detailed Cust_ Ledg_ Entry" T2,
			(
				"Vogl7x"."dbo"."BMW AH Vogl$Cust_ Ledger Entry" T1 LEFT JOIN "Vogl7x"."dbo"."BMW AH Vogl$Customer" T3 ON T1."Customer No_" = T3."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