Skip to Content
author's profile photo Former Member
Former Member

Query to calculate the Sales Analysis on OINV table

Hi All,

I have a requirements that will calculate the sales analysis based on A/R Invoice and also considered the Credit Memo applied to Invoice.

thanks,

vin

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Mar 14, 2011 at 11:34 AM

    Hello Melvin - here is a differrent way to do it in case you want it specifically by date range...it also utilitizes the UNION function so you can have both AR Invoices and AR Credit Memos combined into one listing...

    You can also pick up the original coding here:

    [http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLD-SLARInvandCMListbyGross+Profit]

    Regards - Zal

    --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
    
    

    Add a comment
    10|10000 characters needed characters exceeded

    • Sure Vin - go to my business card and contact me directly at my alternative address shown as "bell". This is a completely different query and the moderators here in SCN do not like to have mutliple questions asked/answers given in a posting.

      I already have a structured SQL outline which does this and you can create another thread when we are completely done so others can comment/learn/see how it goes. Best of both worlds then. OK?

      Regards - Zal

  • author's profile photo Former Member
    Former Member
    Posted on Mar 14, 2011 at 11:21 AM

    Hi Melvin,

    You may try this,

    SELECT P.[CardCode],P.[CardName],

    ISNULL([1],0) as [Jan],

    ISNULL([2],0) as [Feb],

    ISNULL([3],0) as [Mar],

    ISNULL([4],0) as [Apr],

    ISNULL([5],0) as [May],

    ISNULL([6],0) as [Jun],

    ISNULL([7],0) as [Jul],

    ISNULL([8],0) as [Aug],

    ISNULL([9],0) as [Sep],

    ISNULL([10],0) as [Oct],

    ISNULL([11],0) as [Nov],

    ISNULL([12],0) as [Dec]

    FROM (SELECT T0.CARDCODE, T0.CARDNAME, (T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0

    LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2010

    UNION

    SELECT T0.CARDCODE, T0.CARDNAME, -(T1.Doctotal-T1.VatSum-T1.TotalExpns) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0

    LEFT JOIN dbo.ORIN T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2010 ) S

    PIVOT (SUM(S.[BAL]) FOR [month] IN

    ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

    Order By P.CardCode

    Regards,

    RS

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 14, 2011 at 11:29 AM

    Hi,

    Try this:

    SELECT T0.CardCode, T0.CardName,
    	(SUM(T1.Debit) - sum(T1.Credit)) AS 'Total Sales 2010', ((SUM(T1.Debit) - sum(T1.Credit))/12) AS '2010 Monthly Avg', 
    	(SUM(T1.Debit) - sum(T1.Credit)) AS 'Total Sales 2011', ((SUM(T1.Debit) - sum(T1.Credit))/Month(GetDate())) AS '2011 Monthly Avg', 
    	((sum(Case DATENAME(month,T1.DueDate) when 'January' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'January' 
    	 then T1.Debit else 0 end))) Jan,
    	(sum(Case DATENAME(month,T1.DueDate) when 'February' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'February' 
    	 then T1.Debit else 0 end))[Feb],
    	(sum(Case DATENAME(month,T1.DueDate) when 'March' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'March' then T1.Debit else 0 end))Mar,
    	(sum(Case DATENAME(month,T1.DueDate) when 'April' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'April' then T1.Debit else 0 end))Apr,
    	(sum(Case DATENAME(month,T1.DueDate) when 'May' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'May' then T1.Debit else 0 end))May,
    	(sum(Case DATENAME(month,T1.DueDate) when 'June' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'June' then T1.Debit else 0 end))Jun,
    	(sum(Case DATENAME(month,T1.DueDate) when 'July' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'July' then T1.Debit else 0 end))Jul,
    	(sum(Case DATENAME(month,T1.DueDate) when 'August' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'August' 
    	 then T1.Debit else 0 end))Aug,
    	(sum(Case DATENAME(month,T1.DueDate) when 'September' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'September' 
    	 then T1.Debit else 0 end))Sep,
    	(sum(Case DATENAME(month,T1.DueDate) when 'October' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'October' 
    	 then T1.Debit else 0 end))Oct,
    	(sum(Case DATENAME(month,T1.DueDate) when 'November' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'November' 
    	 then T1.Debit else 0 end))Nov,
    	(sum(Case DATENAME(month,T1.DueDate) when 'December' then T1.Credit  else 0 end)-sum(Case DATENAME(month,T1.DueDate) when 'December' 
    	 then T1.Debit else 0 end)) Dec
    FROM  OCRD T0 
    	   LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode 
    	   AND Year(T1.Duedate) = 2011   AND T1.TransType in ('13','14')
    WHERE T0.CardType = 'C' 
    Group By DATENAME(month,T1.DueDate), T0.CardCode, T0.CardName

    Thanks,

    Neetu

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 12, 2011 at 11:46 PM

    Credit memos are in the ORIN table. Basically you would have to connect the two tables either by some sort join depending on the nature of the sales and if you considered credit based on invoices as either cancelled or voiding the document. Also there are some credits that would be used but not based on an invoice.

    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.