Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Tags:
Not what you were looking for? View more on this topic or Ask a question