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: Monthly Special

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL05. Although the SAP B1 Sales Analysis function provides similar information, the Customer did not want to double click on each and every Customer under Sales Analysis. Instead the Customer wanted just one view of all invoices and credit memos together by whatever date range needed with “problems” sorted to the top; those having a low gross profit percentage. There is a very important CASE usage in this SQL you might want to review; namely one way on how to avoid the SQL error message of “divide by zero”.  The section of the SQL covering this point is highlighted; notice it is first checking to see if the total is zero. If the total is zero, then it does not do any calculation but just posts “0.000” in the appropriate fields and goes to the next record in the table. If the total is not zero, then it does the calculation and continues forward. Notice the CASE is also in the ORDER BY section. The Customer wanted “Non-Product” and “Product”, but you can change that to “Item” and “Service” if you want to be consistent with SAP B1 terminology. 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 Inv and CM By Gross Profit Screen Print.png

Copy and Paste this SQL:

--D-SL AR Inv and CM List by Gross Profit Ver 1 ZP 2010 07 27

--DESCRIPTION:  SQL lists AR Invoices and Credit Memos within a specific date range with a sort of lowest to highest Gross Profit percentage.

--AUTHOR(s):
--Version 1 Zal Parchem 2010 07 27

SELECT DISTINCT

T0.TaxDate AS 'Posted',

CASE
WHEN T0.ObjType = 13 THEN 'Inv'
WHEN T0.ObjType = 14 THEN 'CM'
ELSE 'Error'
END AS 'Inv/CM',

T0.DocNum AS 'Num',
T0.CardCode AS 'Cust',
T0.CardName AS 'Cust Name',

CASE
WHEN T0.DocType = 'S' THEN 'Non-Product'
WHEN T0.DocType = 'I' THEN 'Product'
ELSE 'Error'
END AS 'Sales Type',

(T0.DocTotal - T0.VatSum) - T0.TotalExpns AS 'Line Sales',
T0.GrosProfit,

CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END AS 'Gross Prof %'

FROM OINV T0

WHERE

T0.TaxDate >= '[%0]'
AND T0.TaxDate <= '[%1]'

UNION ALL

SELECT DISTINCT

T0.TaxDate AS 'Posted',

CASE
WHEN T0.ObjType = 13 THEN 'Inv'
WHEN T0.ObjType = 14 THEN 'CM'
ELSE 'Error'
END AS 'Inv/CM',

T0.DocNum AS 'Num',
T0.CardCode AS 'Cust',
T0.CardName AS 'Cust Name',

CASE
WHEN T0.DocType = 'S' THEN 'Non-Product'
WHEN T0.DocType = 'I' THEN 'Product'
ELSE 'Error'
END AS 'Sales Type',

((T0.DocTotal - T0.VatSum) - T0.TotalExpns ) * -1 AS 'Line Sales',
T0.GrosProfit * -1,

CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * -100
END AS 'Gross Prof %'

FROM ORIN T0

WHERE

T0.TaxDate >= '[%0]'
AND T0.TaxDate <= '[%1]'

ORDER BY

CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END,

T0.GrosProfit,
T0.CardName,
T0.DocNum

  • No labels

1 Comment

  1. Guest

    This is very helpful and can serves as customized Sales Analysis report. This is great, Zal..