cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Analysis Query Report

Former Member

Hi Experts,

Need your help on the below query. I am trying to recreate the SAP Business One Sales Analysis Report by customer as there are additional fields that need to be presented in the report. Amount pulled out was gross of GST/VAT but should be exclusive of GST/VAT.

We are using SAP HANA and SAP B1 version 9.0.

SELECT T0."ShortName" as "Customer Code", T2."CardName" as "Customer Name",T3."IndDesc",T4."GroupName",

Case when T0."FCCurrency" is null then T5."MainCurncy" else "FCCurrency" end as "Currency"

,T0."BaseRef"

,SUM(IFNULL(T0."Debit",0) - IFNULL(T0."Credit",0)) as "Amount(LC)"

,SUM (IFNULL (T0."FCDebit",0)-IFNULL(T0."FCCredit",0)) as "Amount(FC)"

FROM JDT1 T0

LEFT OUTER JOIN OJDT T1 ON T1."TransId" = T0."TransId" and T0."TransType" IN (13,14)

LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."ShortName"

LEFT OUTER JOIN OOND T3 ON T2."IndustryC" = T3."IndCode"

LEFT OUTER JOIN OCRG T4 ON T2."GroupCode" = T4."GroupCode"

,OADM T5

WHERE T1."RefDate" >=[%0\] and T1."RefDate"  <= [%1\] and T2."CardCode" >= [%2\] and T2."CardCode" <= [%3\] and T2."CardType"='C'

GROUP BY T0."ShortName",T2."CardName",T3."IndDesc",T4."GroupName", T0."FCCurrency",T5."MainCurncy",T0."BaseRef"

Very much appreciate your help on this.

Thank you.

Regards,

Rochelle

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor

Hi,

Try this query:

SELECT T0.[CardCode], T0.[CardName], sum(T1.[LineTotal]) FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[CardCode] = [%0] and  T0.[DocDate] between [%1] and [%2] GROUP BY T0.[CardCode], T0.[CardName]

UNION ALL

SELECT T0.[CardCode], T0.[CardName], -sum(T1.[LineTotal]) FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[CardCode] = [%0] and  T0.[DocDate] between [%1] and [%2] GROUP BY T0.[CardCode], T0.[CardName]

Thank you

Former Member
0 Kudos

Hi Nagarajan,

Thank you very much for your query. It is very helpful. Below is the updated query following your logic. Can you help me combine the amount of AR Invoice and ARCM. Current result is breakdown by Document type (AR Invoice or ARCM).

SELECT T0."CardCode", T0."CardName",T3."IndDesc",T4."GroupName",T0."DocCur" as "Currency",

sum(T0."DocTotal")-sum(T0."VatSum")as "Total" , sum(T0."DocTotalFC")-sum(T0."VatSumFC")as "Total FC"

FROM OINV T0  

LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode"

LEFT OUTER JOIN OOND T3 ON T2."IndustryC" = T3."IndCode"

LEFT OUTER JOIN OCRG T4 ON T2."GroupCode" = T4."GroupCode"

,OADM T5

WHERE

T0."CardCode" >= [%0] and T0."CardCode" <=[%1] and  T0."DocDate" between [%2] and [%3]

and T0."CANCELED" ='N'

GROUP BY T0."CardCode", T0."CardName",T3."IndDesc",T4."GroupName",T0."DocCur"

UNION ALL

SELECT T0."CardCode", T0."CardName", T3."IndDesc",T4."GroupName", T0."DocCur" as "Currency",

sum(T0."DocTotal")-sum(T0."VatSum")as "Total" , sum(T0."DocTotalFC")-sum(T0."VatSumFC")as "Total FC"

FROM ORIN T0 

LEFT OUTER JOIN OCRD T2 ON T2."CardCode" = T0."CardCode"

LEFT OUTER JOIN OOND T3 ON T2."IndustryC" = T3."IndCode"

LEFT OUTER JOIN OCRG T4 ON T2."GroupCode" = T4."GroupCode"

,OADM T5

WHERE

T0."CardCode" >= [%0] and T0."CardCode" <=[%1] and  T0."DocDate" between [%2] and [%3]

and T0."CANCELED" = 'N'

GROUP BY T0."CardCode", T0."CardName",T3."IndDesc",T4."GroupName",T0."DocCur"

Thank you very much in advance.

Best regards,

Rochelle

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Rochelle,

Please try out below query if you want to exclude the tax amount from total Invoice amount.

Select T0.CardCode 'Customer Code',

  T0.CardName 'Customer Name',

  T3.GroupName,

  T4.IndDesc,

  Sum((T0.DocTotal - T0.VatSum)) 'Amount(LC)',

  Sum((T0.DocTotalFC - T0.VatSumFC)) 'Amount (FC)'

From OINV T0

INNER JOIN OCRD T2 ON T2.CardCode = T0.CardCode

LEFT OUTER JOIN OCRG T3 ON T3.GroupCode = T2.GroupCode

LEFT OUTER JOIN OOND T4 ON T4.IndCode = T2.IndustryC

Where

T0.DocDate >= [%0]

AND T0.DocDate <= [%1]

AND  T0.CardCode Like '%[%2]%'

AND T2.CardType = 'C'

Group BY T0.CardCode,

  T0.CardName,

  T3.GroupName,

  T4.IndDesc

Message was edited by: chandan lal

Johan_H
Active Contributor
0 Kudos

Hi Chandan Lai,

Your query does not account for Credit Notes.

Regards,

Johan

Former Member
0 Kudos

Hi Chandan,

The query is good but Credit Note was not include. Can you help to combine the amount from Credit Note?

Thank you.

Regards,

Rochelle

Former Member
0 Kudos

Hi Rochelle,

I have query against the solution you want.

You want all the list of AR Invoice only which is not converted to AR Credit Memo ?

Or you want list of all AR Invoice and AR Credit Memo in single report?

Or you want list of AR and AR Credit Memo separately ?

Can you please post excel format of the report you are looking for ?

Former Member
0 Kudos

Hi,

Surely I will assist you in doing so.

but for doing so please clear my queries as how you want the list of AR and ARCN.

1. AR and against same AR if ARCN exist then infront ARCN details

2. All AR and ARCN list with or without AR Base.

Above answer can be best answered if you can attach the sample report format in excel.

After that I can better make the query with correct joins.

Thanking you in the anticipation of same input.

Regards

Chandan Lal

Former Member
0 Kudos

Hi Chandan,

Below is the screen shot of the report. Expected result is one line per Customer consist of net Amount of AR Invoice and ARCM.

Thank you.

Regards,

Rochelle

Johan_H
Active Contributor
0 Kudos

Hi Rochelle,

Recreating the report using the JDT1 and OJDT tables may not be the most efficient way. For example, as you have found, it is hard to distinguish VAT and such. Another big drawback is that these two are the largest tables in the database, which can make your current query slower than necessary.

Have you considered using the document tables (OINV and ORIN) instead ? Looking at your query they offer all the same data and more.

Here is a simplified example:

SELECT c.CardCode

      ,inv.DocTotal /* the total inc. VAT */

      ,inv.VatSum /* the VAT sum */

      ,inv.DiscSum /* the discount sum, for netto values */

      ,inv.BaseAmnt /* the total cost based on rows for margin calculation */

      ,inv.DocCur /* the document currency */

FROM OCRD c

     LEFT OUTER JOIN OINV inv ON c.CardCode = inv.CardCode AND inv.DocDate BETWEEN '' AND ''

     LEFT OUTER JOIN ORIN cre ON c.CardCode = cre.CardCode AND cre.DocDate BETWEEN '' AND ''

The document fields above all have a FC verson (DocTotalFC, etc.)

I don't know if HANA allows the HAVING clause, but if it does, you can use that to show only customers with transactions.

Regards,

Johan

Former Member
0 Kudos

Hi Johan,

Thank you for your response. I run the query but it does not give any value. I have tried to create the query based on Invoices but i was stuck on how to combine the values from OINV and ORIN.

SELECT T1."CardCode", T1."CardName",T2."IndDesc", T3."GroupName", T1."DocCur"

,sum(T4."DocTotalFC")as "Total Invoice Amount (FC)"

,sum(T1."DocTotal") as "Total Invoice Amount (LC)"

FROM OCRD T0 

LEFT OUTER JOIN OINV T1 ON T0."CardCode" = T1."CardCode"

LEFT OUTER JOIN OOND T2 ON T0."IndustryC" = T2."IndCode"

LEFT OUTER JOIN OCRG T3 ON T0."GroupCode" = T3."GroupCode"

LEFT OUTER JOIN ORIN T4 ON T0."CardCode" = T4."CardCode"

WHERE T1."CANCELED" = 'N'

and T1."DocDate" >=[%0] and  T1."DocDate" <=[%1] and    T1."CardCode" >=[%2] and  T1."CardCode" <=[%3]

Group by

T1."CardCode", T1."CardName", T2."IndDesc", T3."GroupName", T1."DocCur"

Regards,

Rochelle

Johan_H
Active Contributor
0 Kudos

Hi Rochelle,

Could you please test your query without the T1."CANCELED" = 'N' bit ?

If you then get results, you can try to adapt T1."CANCELED" = 'N' to IFNULL(T1."CANCELED", "N") = 'N'

By the way, you need to apply the date parameters to both the OINV and ORIN tables. and the CardCode parameter you should apply to the OCRD table.

Regards,

Johan