Query that show total discount on A/R Invoice

[Former Member]

Dear all,

I would like to have query that show summary of each invoice number that show 'Total of discount' ( 'Total of discount' = total of discount in line item + discount of total invoice ). Data that I would like to show on this query is as follow :

Invoice No, Invoice Date, Customer Code, Total before discount, 'Total of discount', Total Amount, Vat amount

Please kindly suggest the way to get this data.

Thanks you in advanced.

Angnam

[mk3]

Hii,

The query below is similar to above but it can display vat groups separately.

SELECT T0.[DocNum], T0.[TaxDate], T0.[CardCode], T0.[CardName],case T0.[DocType] when 'i' then sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]) when 's' then sum( T1.[PriceBefDi]*T0.[DocRate]) end as 'total before discount',case T0.[DocType] when 'i' then sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]*T1.[DiscPrcnt]/100) when 's' then sum( T1.[PriceBefDi]*T0.[DocRate]*T1.[DiscPrcnt]/100) end as 'item discount', T0.[DiscSumSy],case T0.[DocType] when 'i' then ((sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]*T1.[DiscPrcnt]/100)+T0.[DiscSumSy])) when 's' then ((sum( T1.[PriceBefDi]*T0.[DocRate]*T1.[DiscPrcnt]/100)+T0.[DiscSumSy])) end as 'total of discount',sum( T1.[LineVatS]) as 'vat', case T0.[DocType] when 'i' then (((sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]))-( sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]*T1.[DiscPrcnt]/100))+T0.[DiscSumSy]))+(sum(T1.[LineVatS])) when 's' then ((sum( T1.[PriceBefDi]*T0.[DocRate]))-( sum( T1.[PriceBefDi]*T0.[DocRate]*T1.[DiscPrcnt]/100)+T0.[DiscSumSy]))+(sum(T1.[LineVatS])) end as 'TOTAL',T0.[DocTotalSy],T1.[VatGroup]

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate]>=[%0] AND T0.[TaxDate]<=[%1]

GROUP BY T0.[DocNum], T0.[TaxDate], T0.[CardCode], T0.[CardName], T0.[DiscSumSy], T0.[VatSum], T0.[DocTotalSy], T0.[DocType],T1.[VatGroup]

Please note that "TOTAL" column is the amount for that vat group while "Document Total (SC)" column is for total of that whole document. The "Document Total (SC)" column will therefore have duplicated values depending on number of vat group being used in that document.

Caveat.

This query may not work in all cases where foreign currency is involved.

You may consider using the [TotalSumSy] field in INV1 table which gives the amount after item discount but before document discount. The [TotalSumSy] field will display the amount in system currency.

(SAP B1 v 9.2)

Regards

[Former Member]

Hi Angnam K,

Try This one...hope this will serve your purpose.

SELECT  DocEntry, DocNum,   CardCode, CardName, DocDate, [Total Before Discount] , [Totaldiscount]+  [OinvDiscount] as [Total of discount], VatSum [Total Tax]      FROM (
SELECT T0.DocEntry,T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], SUM(T1.[LineTotal])[Total Before Discount], 
SUM(T1.[PriceBefDi] * T1.Quantity *T1.[DiscPrcnt])/100 as [Totaldiscount]
,Case When T0.[DiscPrcnt] <> 0 Then  (SUM(T1.[LineTotal])/ T0.[DiscPrcnt]) Else 0 end [OinvDiscount] ,T0.VatSum 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
Group By T0.DocEntry,T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DiscSum] ,T0.[DiscPrcnt],T0.VatSum )Sales

thanks,

Kumar

Edited by: itskumaramit on Jul 13, 2011 12:43 PM

[mk3]

hii,

If by "total before discount" you mean the amount before discount is applied to each item, then this is a calculated field as pointed out by the Seniors.

Please try the below query:

SELECT T0.[DocNum], T0.[TaxDate], T0.[CardCode], T0.[CardName],case T0.[DocType] when 'i' then sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]) when 's' then sum( T1.[PriceBefDi]*T0.[DocRate]) end as 'total before discount',case T0.[DocType] when 'i' then sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]*T1.[DiscPrcnt]/100) when 's' then sum( T1.[PriceBefDi]*T0.[DocRate]*T1.[DiscPrcnt]/100) end as 'item discount', T0.[DiscSumSy], case T0.[DocType] when 'i' then ((sum( T1.[PriceBefDi]*T0.[DocRate]* T1.[Quantity]*T1.[DiscPrcnt]/100)+T0.[DiscSumSy])) when 's' then ((sum( T1.[PriceBefDi]*T0.[DocRate]*T1.[DiscPrcnt]/100)+T0.[DiscSumSy])) end as 'total of discount',T0.[VatSum], T0.[DocTotalSy]

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate]>=[%0] AND T0.[TaxDate]<=[%1]

GROUP BY T0.[DocNum], T0.[TaxDate], T0.[CardCode], T0.[CardName], T0.[DiscSumSy], T0.[VatSum], T0.[DocTotalSy], T0.[DocType]

Caveat.

The above query may not work in all cases where foreign currency is involved.

You may consider using the [TotalSumSy] field in INV1 table which gives the amount after item discount but before document discount. The [TotalSumSy] field will display the amount in system currency.

(SAP B1 v9.2)

With regards

[Former Member]

Dear Angnam,

Try:

SELECT T0.DocNum 'Invoice No.', T0.DocDate, T0.CardCode 'Customer Code', T0.DocTotal 'Total Before Discount',

SUM(T1.PriceBefDi * T1.Quantity *T1.DiscPrcnt)/100 + T0.DiscSum 'Total Discount',T0.VatSum

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

Group By T0.DocNum, T0.DocDate, T0.CardCode, T0.DocTotal,T0.DiscSum ,T0.VatSum

Thanks,

Gordon