cancel
Showing results for 
Search instead for 
Did you mean: 

Net Sales Report

Former Member
0 Kudos

Dear Experts,

I want a report (query) in which Sales from OINV & INV1 and Sales Returns from ORIN & RIN1 are to be shown against Items from OITM and Business Partners from OCRD and in the last column I need Net Sales (Sales minus Returns). Please suggest the query.

Regards,

Azeem

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Muhammad.......

Try this.......

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], 
T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], Sum(T2.[Quantity]) 'returnQty', 
Sum(T2.[LineTotal]) 'returnAmt', (T1.[LineTotal]-Sum(T2.[LineTotal])) 'Net Sale' 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN RIN1 T2 
On T2.BaseEntry=T1.DocEntry And T2.BaseLine=T1.LineNum LEFT JOIN ORIN T3 ON 
T2.DocEntry = T3.DocEntry
Group By 
T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], 
T1.[Dscription], T1.[Quantity], T1.[LineTotal]

Regards,

Rahul

Former Member
0 Kudos

Dear,

Appreciate your quick response. But the problem is that we have independent AR Credit Memos i.e. not copy from AR Invoice so the query has to account for those also.

Regards,

Azeem

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Muhammad Azeem,

/*SELECT FROM [dbo].[OPCH] T2*/
Declare @startDate as DATETIME  
/* WHERE */
Set @startDate = /* T2.DocDate */  '[%1]' 
 
/*SELECT FROM [dbo].[OPCH] T3*/
Declare @endDate as  DATETIME 
/* WHERE */ 
Set @endDate = /* T3.DocDate */  '[%2]' 
 
SELECT DISTINCT  T0.[DocNum] as 'Invoice No.', T0.[DOCDATE] as 'Invoice Date', T0.[CardName] as 'Customer Name' ,
 
((T0.DocTotal-T0.[VatSum]) ) as 'Gross Amount',T2.SeriesName,
 
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-90 and PCH4.docentry=T0.Docentry  )BED@10,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-60 and PCH4.docentry=T0.Docentry )ECESS@2,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-55 and PCH4.docentry=T0.Docentry )SHE@1,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  1 and PCH4.docentry=T0.Docentry )VAT@4,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  7 and PCH4.docentry=T0.Docentry )ADDVAT@1,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=  7 and PCH4.docentry=T0.Docentry )CST@2,
(Select distinct isnull(Sum(PCH4.taxsum),0) from PCH4 where PCH4.statype=-80 and PCH4.docentry=T0.Docentry )AED@4,
 
( T0.Doctotal ) as 'Net Amount' , T3.City 
 
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN NNM1 T2 ON T0.Series = T2.Series 
left outer join ocrd T3 on T0.CardCode = T3.CardCode
 
 
Where T0.DocDate >=@startDate 
and T0.DocDate <= @endDate order by T0.DocDate, T0.Docnum

Just change the Statype as per your DB. For Statype Run Query

SELECT distinct T0.[staType], T0.[StcCode], T0.[StaCode] FROM PCH4 T0

Here Name call as Authority Type.

Change the Table INV in place of PCH for Sales Query and RIN for Return

Thanks,

Srujal Patel

Former Member
0 Kudos

Dear Patel,

I run the query you specified but no result. I cant understand Statype.

Regards,

Azeem

Former Member
0 Kudos

Dear Azeem,

Try:

SELECT CardCode,CardName,ItemCode, ItemName, SUM([QTY]) 'QTY', SUM([TOTAL]) 'Sales'

FROM (SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, T1.Quantity [QTY],  T1.LineTotal [TOTAL]

FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode

UNION ALL

SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, -T1.Quantity [QTY], -T1.LineTotal [TOTAL]

FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
) S

GROUP BY CardCode,CardName,ItemCode, ItemName

Thanks,

Gordon

Former Member
0 Kudos

Thanks Gordon

It really works but i require two more things in it: first, Can I get individual figures of Sales and Returns alongwith the Net Sales.

And the second thing the variables for date, cardcode and itemcode i.e these fields are to be filled in by the user according to the requirement.

Regards,

Azeem

Former Member
0 Kudos

Updated based on your need:

Declare @Fromdate as datetime
Declare @Todate as datetime
Declare @BP as varchar(50)
Declare @ITEM as varchar(20)

SET @FROMDATE = /*SELECT MIN(Docdate) FROM OINV T0 WHERE T0.DocDate>= */ [%0]
SET @TODATE = /*SELECT MAX(Docdate) FROM OINV T0.WHERE T0.DocDate<=*/ [%1]
SET @BP = /*SELECT CardName FROM OINV T0 WHERE T0.CardName = */ '[%2]'
SET @ITEM = /*SELECT ItemCode FROM INV1 T1 WHERE T1.ItemCode = */ '[%3]'

SELECT CardCode,CardName,ItemCode, ItemName, SUM([QTY]) 'QTY', SUM([SALES]) 'Sales', SUM([Return]) 'Return', SUM([SALES]) + SUM([Return]) 'Net sales'

FROM (SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, T1.Quantity [QTY],  T1.LineTotal [SALES], 0 [Return]

FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode

WHERE T0.DocDate > @FromDate AND  T0.DocDate < @ToDate AND T0.CardName = @BP AND T1.ItemCode = @ITEM

UNION ALL

SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, -T1.Quantity [QTY], 0 [Sales],-T1.LineTotal [Return]

FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode

WHERE T0.DocDate > @FromDate AND  T0.DocDate < @ToDate AND T0.CardName = @BP AND T1.ItemCode = @ITEM) S

GROUP BY CardCode,CardName,ItemCode, ItemName

Former Member
0 Kudos

Dear Gordon,

I found a little error in this, that the Sales Return Value is also shown in the Sales Column.

There should be separate columns for Qty sold and Qty returned.

The Customer and Item variables should be optional, while Date Range will remain mandatory.

Regards,

Azeem

former_member218051
Active Contributor
0 Kudos

Hi,

Try this

SELECT s.cardcode , s.cardname , s.itemcode , s.itemname , sum(s.saleqty) as saleqty , sum(s.salevalue) as salevalue ,

sum(s.rtnqty) as rtnqty , sum(s.rtnvalue) as rtnvalue , sum(isnull(s.salevalue,0)) + sum(isnull(s.rtnvalue,0)) as netsales

from

(

select ocrd.cardcode , ocrd.cardname , oitm.itemcode , oitm.itemname , sum(inv1.quantity) as saleqty , sum(inv1.linetotal) as salevalue , 0 as rtnqty , 0 as rtnvalue ,

sum(isnull(inv1.linetotal,0)) + 0 as netsales

from oinv inner join inv1 on oinv.docentry = inv1.docentry

inner join oitm on oitm.itemcode = inv1.itemcode

inner join ocrd on ocrd.cardcode = oinv.cardcode

where oinv.docdate between @FromDate and @ToDate

group by ocrd.cardcode , ocrd.cardname , oitm.itemcode , oitm.itemname

union all

select ocrd.cardcode , ocrd.cardname , oitm.itemcode , oitm.itemname , 0 as saleqty , 0 as salevalue , sum(rin1.quantity) as rtnqty , sum(rin1.linetotal) as rtnvalue ,

0 + sum(isnull(rin1.linetotal,0)) as netsales

from orin inner join rin1 on orin.docentry = rin1.docentry

inner join oitm on oitm.itemcode = rin1.itemcode

inner join ocrd on ocrd.cardcode = orin.cardcode

where orin.docdate between @FromDate and @ToDate

group by ocrd.cardcode , ocrd.cardname , oitm.itemcode , oitm.itemname ) s

group by

s.cardcode , s.cardname , s.itemcode , s.itemname , s.netsales

thanks

Malhaar

Former Member
0 Kudos

Try:

Declare @Fromdate as datetime
Declare @Todate as datetime
Declare @BP as varchar(50)
Declare @ITEM as varchar(20)
 
SET @FROMDATE = /*SELECT MIN(Docdate) FROM OINV T0 WHERE T0.DocDate>= */ [%0]
SET @TODATE = /*SELECT MAX(Docdate) FROM OINV T0.WHERE T0.DocDate<=*/ [%1]
SET @BP = /*SELECT CardName FROM OINV T0 WHERE T0.CardName = */ '[%2]'
SET @ITEM = /*SELECT ItemCode FROM INV1 T1 WHERE T1.ItemCode = */ '[%3]'
 
SELECT CardCode,CardName,ItemCode, ItemName, SUM([SaleQTY]) 'QTY Sold', SUM([ReturnQTY]) 'QTY Returned',SUM([SALES]) 'Sales', SUM([Return]) 'Return', SUM([SALES]) + SUM([Return]) 'Net sales'
 
FROM (SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, T1.Quantity [SaleQTY],  0 [ReturnQty],T1.LineTotal [SALES], 0 [Return]
 
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
 
WHERE T0.DocDate > @FromDate AND (T0.CardName = @BP or IsNull(@BP,'')='') AND (T1.ItemCode = @ITEM or ISNULL(@ITEM,'') ='' )
 
UNION ALL
 
SELECT T0.CardCode, T0.CardName, 
T1.ItemCode, T2.ItemName, 0 [SaleQty], -T1.Quantity [QTY], 0 [Sales],-T1.LineTotal [Return]
 
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode
 
WHERE T0.DocDate > @FromDate AND  T0.DocDate < @ToDate AND (T0.CardName = @BP or IsNull(@BP,'')='') AND (T1.ItemCode = @ITEM or ISNULL(@ITEM,'') ='' )) S
 
GROUP BY CardCode,CardName,ItemCode, ItemName

HAVING SUM([SALES]) + SUM([Return])>0