Skip to Content

Query for Sales by State and Country

Hello,

Is there a way to create a query in SAP B1 for Sales by State to include Country and also AR Credit Memos and AR Cancelations?

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jun 14, 2019 at 08:14 AM

    Hi Jeanette,

    Could you please check this query:

    SELECT
    	(select State1 from OCRD where Cardcode=v.CardName) as 'State',
    	v.DocNum,
    	v.DocDate,
    	v.CardCode,
    	v.CardName,
    	v1.ItemCode ,v1.Dscription ,v1.Quantity 'Invoice Qty',v1.Price 'Invoice Price',v1.LineTotal 'Invoice Total'
    ,isnull(r1.Quantity,0) 'Credit Memo Qty',isnull(r1.Price,0) 'Credit Memo Price',isnull(r1.LineTotal,0) 'Credit Memo Total'
    ,v1.LineTotal-isnull(r1.LineTotal,0) 'Net Profit'
    FROM oinv v inner join inv1 v1 on v.DocEntry=v1.DocEntry
    left outer join rin1 r1 on r1.BaseLine=v1.LineNum and r1.BaseEntry =v1.DocEntry  and  r1.BaseType =13
    left outer join orin r on r.docentry=r1.docentry
    WHERE v.docdate between '01/01/2019' and '06/06/2019'
    

    Thank you,

    Aziz

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 14, 2019 at 11:36 AM

    My suggestion use table INV12 Instead of table OCRD

    SELECT
     v12.StateS,
     v12.countryS,
     v.DocNum,
     v.DocDate,
     v.CardCode,
     v.CardName,
     v1.ItemCode ,v1.Dscription ,v1.Quantity 'Invoice Qty',v1.Price 'Invoice Price',v1.LineTotal 'Invoice Total'
    ,isnull(r1.Quantity,0) 'Credit Memo Qty',isnull(r1.Price,0) 'Credit Memo Price',isnull(r1.LineTotal,0) 'Credit Memo Total'
    ,v1.LineTotal-isnull(r1.LineTotal,0) 'Net Profit'
    FROM oinv v inner join inv1 v1 on v.DocEntry=v1.DocEntry inner join inv12 v12 on v.DocEntry = v12.DocEntry
    left outer join rin1 r1 on r1.BaseLine=v1.LineNum and r1.BaseEntry =v1.DocEntry  and  r1.BaseType =13
    left outer join orin r on r.docentry=r1.docentry
    WHERE v.docdate between '01/01/2019' and '06/06/2019'
    
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.