Skip to Content
0
Former Member
Dec 03, 2009 at 08:03 AM

Sales Order Query for in SAP B1

1607 Views

Hi,

I Need to generate a report to Calculate the net sales orders for a particular customer in a given period. i can able to calculate the total sales order details itemwise but my query not works when the customer returns the particular products.( ie) I need to subtract the credit memo value from the order value of the particular product when the customer returns the product.

Here is my Query Please any one Help me... what mistake in this query

SELECT A.CardName AS 'Customer Name', A.Address, A.DocNum AS 'Ord.No', CONVERT(varchar, A.DocDate, 3) AS 'Date', B.Dscription AS 'Item Name',

CASE WHEN B.itemcode = F .itemcode and A.CardCode = G.CardCode and A.Slpcode = F.Slpcode THEN B.quantity - F .quantity ELSE B.quantity END AS Quantity,

CASE WHEN B.itemcode = F .itemcode and A.CardCode = G.CardCode and A.slpcode = F.slpcode THEN B.price - F .price ELSE B.price END AS Price, B.DiscPrcnt,

CASE WHEN B.itemcode = F .itemcode and A.CardCode = G.CardCode THEN B.linetotal - F .linetotal ELSE B.linetotal END AS Amount, B.VatPrcnt,F.ItemCode,E.Itemcode,D.Itemcode,C.Itemcode

FROM

ORDR A INNER JOIN

RDR1 B ON A.Docentry = B.Docentry LEFT JOIN

INV1 C ON A.Docentry = C.Baseref and C.Basetype=17 LEFT JOIN

DLN1 D ON A.Docentry = D.BaseRef and D.Basetype=17 or C.Docentry=D.BaseRef and D.Basetype =13 LEFT JOIN

RDN1 E ON D.Docentry = E.BaseRef and E.Basetype =15 LEFT JOIN

RIN1 F ON C.Docentry =F.BaseEntry and F.Basetype = 13 or E.Docentry = F.BaseRef and F.BaseType = 16 or E.TrgetEntry = F.Docentry and F.Targettype= 14 LEFT JOIN

ORIN G ON G.Docentry =F.Docentry Left join

OSLP H ON A.Slpcode = H.Slpcode

WHERE (A.CardName = 'SHASUN CHEMICAL AND DRUGS') AND (A.DocDate >= '2000/9/30') AND (A.DocDate <= '2009/10/10')

group by A.CardName , A.Address, A.DocNum , A.DocDate, B.Dscription,B.Itemcode,

F.Itemcode,A.cardcode,G.cardcode,A.slpcode,

F.Slpcode,B.quantity,F.quantity,B.Price,F.price,B.vatprcnt,B.discprcnt,B.Linetotal,F.linetotal,F.ItemCode,E.Itemcode,D.Itemcode,C.Itemcode

ORDER BY 'Date'

Thanks,

vasanthapraba.