Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to D-SL Sales AR Module SQL Posted to Wiki Listing - D-SL Sales AR Module SQL Posted to Wiki  )

TYPE: Frequently Requested SQL

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL05. This SQL allows Sales Management to see the AR Invoices and Credit Memos split into two separate lines so they can see not only what was sold, but what was returned by the Customer within Sales Person and Date Range.  This SQL also demonstrates how to use a special WHERE clause to see individual Sales Persons or all Sales Personnel.  All of the dollar amounts will agree with the Sales Analysis Report except the "Total Open IN" column on the Sales Analysis Report.  For some reason SAP does not really show what the TOTAL Open IN dollar amounts are against the Customer's Invoices and Credit Memos, but calculates it differently.  I am not sure if this is a bug or they are just using a different way to calculate that dollar amount.  Go ahead and compare the "Total Open IN" with the results of the SQL and you will see the difference.  If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation.

Here is a screen print of the SQL results:

 D-SL AR Invoives and Credit Memos by Salesperson and Date Range.png

Copy and Paste this SQL:

--D-SL AR Invoices and Credit Memos by Salesperson and Date Range Ver 1 ZP 2011 03 27

--DESCRIPTION:  SQL adds all invoices into one total and all credit memos into one total connected with each specific salesperson.  SQL product total and gross profit should agree with Sales Analysis Report.  However, open items total does NOT match since SAP calculates Total Open IN column incorrectly.

--AUTHOR(s):
--Version 1 Zal Parchem 2011 03 27

SELECT

T1.SlpName AS 'Salesperson',
--T0.SlpCode AS 'SP Code',
COUNT(T0.DocNum) AS 'Total Count',
'Credit Memos' AS 'Doc Type',
SUM ((T0.DocTotal) * -1) AS 'Total Docs',
SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Product Sales',
SUM ((T0.GrosProfit) * -1) AS 'Gross Profit',

CASE
WHEN (SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) = 0.00 THEN 0.00
ELSE (SUM ((T0.GrosProfit) * -1)/SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100
END AS 'Gross Profit %',

SUM ((T0.PaidToDate) * -1) AS 'Paid To Date',
SUM ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Open on Docs'
 
FROM ORIN T0 

INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')
AND T0.TaxDate >= '[%1]'
AND T0.TaxDate <= '[%2]'

GROUP BY T0.SlpCode, T1.SlpName

UNION

SELECT

T1.SlpName AS 'Salesperson',
--T0.SlpCode AS 'SP Code',
COUNT(T0.DocNum) AS 'Total Count',
'Invoices' AS 'Doc Type',
SUM (T0.DocTotal) AS 'Total Docs',
SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) AS 'Product Sales',
SUM (T0.GrosProfit) AS 'Gross Profit',

CASE
WHEN (SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) = 0.00 THEN 0.00
ELSE (SUM (T0.GrosProfit)/SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END AS 'Gross Profit %',

SUM (T0.PaidToDate) AS 'Paid To Date',
SUM (T0.DocTotal - T0.PaidToDate) AS 'Open on Docs'
 
FROM OINV T0 

INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCode

WHERE

(T1.SlpName LIKE '%%[%0]%%' OR '[%0]' = ' ')
AND T0.TaxDate >= '[%1]'
AND T0.TaxDate <= '[%2]'

GROUP BY T0.SlpCode, T1.SlpName

  • No labels