Skip to Content

Query Amendment - Any Assistance Appreciated!

Hi,

I need to make a few amends to an existing query that I have:

--INCLUDES PRE-SAP HISTORY, DISCOUNTS & CREDITS

Declare @CURRENCY Nvarchar(50) = /* SELECT FROM OINV Q0 WHERE Q0.DocCur = */ '[%0]'

Declare @FRDT DateTime = /* SELECT FROM OINV Q1 WHERE Q1.DocDate >= */ '[%1]'

Declare @TODT DateTime = /* SELECT FROM OINV Q2 WHERE Q2.DocDate <= */ '[%2]'

SELECT X.[DOCNUM], X.[DATE], SUM(QTY) [QTY], X.[CODE], X.[DESC], SUM(SALES) as [SALES], X.[ORIGIN], X.[MATERIAL]

FROM

(

SELECT Y.[DOCNUM], Y.[DATE], SUM(Y.QTY) [QTY], Y.[CODE], Y.[DESC], Y.[SALES], Y.[ORIGIN], Y.[MATERIAL]

FROM (

SELECT T0.[DocNum] as [DOCNUM],

CONVERT(VARCHAR(10),T0.[DocDate],103) as [DATE],

T1.[Quantity] as [QTY],

T1.[ItemCode] as [CODE],

T1.[Dscription] as [DESC],

Case T0.[DocCur] When 'AUD' then T1.[LineTotal] else T1.[TotalFrgn] end as [SALES],

T2.[U_Origin] as [ORIGIN],

T2.[U_NZTariff] as [MATERIAL]

FROM OINV T0

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

INNER JOIN OITM T2 on T1.ItemCOde = T2.ItemCode

LEFT JOIN RDR1 T3 ON T1.BaseEntry = T3.DocEntry AND T1.BaseLine = T3.LineNum AND T1.BaseType='17'

WHERE T0.[DocCur] = @CURRENCY AND

T1.[OpenQty] <> 0 AND

T0.[DocDate] BETWEEN @FRDT AND @TODT AND

ISNULL(T3.OpenQty,0)=0

UNION ALL

SELECT T0.[DocNum] as [DOCNUM],

CONVERT(VARCHAR(10),T0.[U_Date],103) as [DATE],

T1.[U_Qty] as [QTY],

T1.[U_ItemNo] as [CODE],

T1.[U_Description] as [DESC],

Case T0.[U_Currency] When 'AUD' then T1.[U_RowTotal] else T1.[U_RowTotalFC] end as [SALES],

T2.[U_Origin] as [ORIGIN],

T2.[U_NZTariff] as [MATERIAL]

FROM [@OINV] T0

INNER JOIN [@INV1] T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 on T1.U_ItemNo = T2.ItemCode

WHERE T0.[U_Currency] = @CURRENCY AND

T1.[U_Qty] <> 0 AND

T0.[U_Date] BETWEEN @FRDT AND @TODT

UNION ALL

SELECT T0.[DocNum] as [DOCNUM],

CONVERT(VARCHAR(10),T0.[DocDate],103) as [DATE],

T1.[Quantity]*-1 as [QTY],

T1.[ItemCode] as [CODE],

T1.[Dscription] as [DESC],

Case T0.[DocCur] when 'AUD' then T1.[LineTotal]*-1 else T1.[TotalFrgn]*-1 end as [SALES],

T2.[U_Origin] as [ORIGIN],

T2.[U_NZTariff] as [MATERIAL]

FROM ORIN T0

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

INNER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

WHERE T0.[DocCur] = @CURRENCY AND

T0.[DocDate] BETWEEN @FRDT AND @TODT

) Y

GROUP BY Y.[DOCNUM], Y.[DATE], Y.[CODE], Y.[DESC], Y.[SALES], Y.[ORIGIN], Y.[MATERIAL]

) X

GROUP BY X.[DOCNUM],X.[DATE], X.[CODE], X.[DESC], X.[ORIGIN], X.[MATERIAL]

This query will ask the user for the document currency as well as the posting date range.

This works fine for what it is - but I need to add to it.

I need to add the following columns:

Gross Profit

Gross Profit %

Sales Tax

COGS within selected currency

I am having difficulties with this - as I know how to find out the gross profit with the system currency - but not the user selected currency. For example - our system currency is AUD and I need to do a report for NZD.

Any help with this would be appreciated.

Regards

Rick

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 14, 2016 at 07:46 AM

    Hi Rick,

    If you wrote this query yourself, I have to say I am proud of you. That is a good looking and complicated query.

    For the Gross profit and percentage, you can use the GrossBuyPr field from INV1 and RIN1

    For the sales tax (VAT I take it ?), you will have to elaborate a little, because you can go through INV1 / RIN1 or through OINV / ORIN, depending on whether you just need a bulk sum, or say an item-wise percentage.

    Sorry, COGS means what ? Cognitively and Overtly Gallivanting Scientists ?

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Okay, that is fortunately an easy one. You have joined INV1 and RIN1, which are the lines tables. However, you do not need them, so give it a try without:

      SELECT T0.[DocNum]

      , T0.[DocDate]

      , T0.[CardCode]

      , T0.[CardName]

      , T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC] as 'Total Sales ex GST'

      , T0.[VatSumFC] as 'Total Tax'

      , T0.[DocTotalFC]

      , T0.[GrosProfFC]

      , T0.[DocTotalFC]- T0.[DiscSumFC]- T0.[VatSumFC]-T0.[GrosProfFC] as 'COGS NZD'

      FROM OINV T0

      WHERE T0.[DocCur] = 'EUR' and (T0.[DocDate] between [%0] and [%1])

      UNION ALL

      SELECT T3.[DocNum]

      , T3.[DocDate]

      , T3.[CardCode]

      , T3.[CardName]

      , (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC]) * -1 as 'Total Sales ex GST'

      , T3.[VatSumFC] * -1 as 'Total Tax'

      , T3.[DocTotalFC] * -1

      , T3.[GrosProfFC] * -1

      , (T3.[DocTotalFC]- T3.[DiscSumFC]- T3.[VatSumFC]-T3.[GrosProfFC]) as 'COGS NZD'

      FROM ORIN T3

      WHERE T3.[DocCur] = 'EUR'

      and (T3.[DocDate] between [%0] and [%1])

      Regards,

      Johan