Umsatzauswertung

Diese Abfrage wertet die Umsätze (=alle Rechnungen und Gutschriften) analog der Variante aus, wie SAP in Ihren eigenen Standardberichten.

Insbesondere berücksichtigt die Abfrage folgende Sonderfälle:

Stornobelege in zeitlich anderen Perioden wie der Basisbeleg (=Minusumsatz in einer anderen Periode)
Gutschriftsbelege auf Anzahlungsrechnungen (=nicht umsatzrelevant)
Geschlossene Gutschriftsbelege

Die Abfrage lässt sich im Abfragemanager einbauen und kann mit weiteren Felder beliebig ergänzt werden.

Abfrage MS-SQL-Format

SELECT 'Ausgangsrechnung' AS 'Dokument', 
	T0.CardCode, 
	T0.CardName,
	T0.DocNum, 
	T0.DocDate, 
	T2.Country, 
	T3.SlpName, 
	(SUM(T5.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100)) AS Total

	FROM OINV T0 
	LEFT JOIN INV1 T5 ON T0.DocEntry = T5.DocEntry
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T0.CANCELED = 'N'
	
	GROUP BY 
	T0.DocNum, T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt

	UNION ALL
	SELECT 'Ausgangsrechnung Canceled' AS 'Dokument', 
	T0.CardCode, 
	T0.CardName,
	T0.DocNum, 
	T0.DocDate, 
	T2.Country, 
	T3.SlpName, 
	(SUM(T5.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100)) AS Total

	FROM OINV T0 
	LEFT JOIN INV1 T5 ON T0.DocEntry = T5.DocEntry
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T0.CANCELED = 'Y'
	
	GROUP BY 
	T0.DocNum, T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt

	UNION ALL

	SELECT 'Ausgangsrechnung Storno' AS 'Dokument', 
	T0.CardCode, 
	T0.CardName,
	T0.DocNum, 
	T0.DocDate, 
	T2.Country, 
	T3.SlpName, 
	((SUM(T5.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100))*-1) AS Total

	FROM OINV T0 
	LEFT JOIN INV1 T5 ON T0.DocEntry = T5.DocEntry
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T0.CANCELED = 'C'
	
	GROUP BY 
	T0.DocNum, T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt
	
	UNION ALL

	SELECT 'Gutschrift' AS 'Dokument',
	T0.CardCode, 
	T0.CardName, 
	T0.DocNum, 
	T0.DocDate, 
	T2.Country,
	T3.SlpName, 
	((SUM(T4.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100)) *-1) AS Total

	FROM ORIN T0 
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	LEFT JOIN RIN1 T4 ON T4.DocEntry = T0.DocEntry
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T4.BaseType <> '203' AND T0.CANCELED = 'N'
	
	GROUP BY 
	T0.DocNum,T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt

	UNION ALL
	
	

	SELECT 'Gutschrift Canceled' AS 'Dokument',
	T0.CardCode, 
	T0.CardName, 
	T0.DocNum, 
	T0.DocDate, 
	T2.Country,
	T3.SlpName, 
	((SUM(T4.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100)) *-1) AS Total

	FROM ORIN T0 
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	LEFT JOIN RIN1 T4 ON T4.DocEntry = T0.DocEntry
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T4.BaseType <> '203' AND T0.CANCELED = 'Y'
	
	GROUP BY 
	T0.DocNum,T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt	

	UNION ALL

	SELECT 'Gutschrift Storno' AS 'Dokument',
	T0.CardCode, 
	T0.CardName, 
	T0.DocNum, 
	T0.DocDate, 
	T2.Country,
	T3.SlpName, 
	((SUM(T4.LineTotal) *((100-ISNULL(T0.DiscPrcnt,0))/100))) AS Total

	FROM ORIN T0 
	INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
	LEFT JOIN CRD1 T2 ON T2.CardCode = T1.CardCode AND T1.BillToDef = T2.Address AND T2.AdresType = 'B'
	LEFT JOIN OSLP T3 ON T3.SlpCode = T0.SlpCode
	LEFT JOIN RIN1 T4 ON T4.DocEntry = T0.DocEntry
	
	WHERE T0.DocDate >= '[%FromDate]' AND T0.DocDate <= '[%ToDate]' AND T4.BaseType <> '203' AND T0.CANCELED = 'C'
	
	GROUP BY 
	T0.DocNum,T2.Country,T0.DocDate, T3.SlpName,T0.CardCode, T0.CardName, T0.DiscPrcnt

DATA UNIT Products & FAQ

Umsatzauswertung

Analytics

Abfrage MS-SQL-Format

Related content