Sales Revenue Report which includes canceled documents cross month booking by Country and Sales Rep.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
| Codeblock | ||||
|---|---|---|---|---|
| ||||
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 |