SBO / ITK: Problem with query for legal consolidation

61 Views

Hello,

I just discovered a strange behaviour with a query, which is described in the SAP Configuration Guide for Legal Consolidation (part of ITK Vers. 1.0).

This is the Query (R3_EC_CS_EXCEL_RESULT_FINAL):

*********************************************************

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3) AS CURRENCY,

T2.U_PACCT, 'C1000' AS CCODE, LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) +

SUBSTRING(T2.U_PPCODE,2,5)),5) AS PPCODE, SUBSTRING(T2.U_FCODE,2,4) AS FUNC_AREA,

LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3) AS COUNTRY, LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) +

SUBSTRING (T2.U_MVTP,2,3)), 3) AS TTY, SUM (T0.DEBIT - T0.CREDIT) AS VAL_LOC,

SUM (T0.FCDEBIT - T0.FCCREDIT) AS VAL_FRG

FROM (JDT1 T0 LEFT OUTER JOIN OCRD T1 ON T0.ShortName = T1.CardCode)

INNER JOIN OACT T2 ON T0.Account = T2.AcctCode

LEFT OUTER JOIN OCRN T3

ON T0.FcCurrency = T3.CurrCode

WHERE t0.RefDate >= '2004-01-01' and t0.RefDate <= '2004-12-31'

GROUP BY LEFT (LTRIM(T3.DocCurrCod + ' ' + 'PLN'),3), T2.U_PACCT,

LEFT((SUBSTRING(T0.U_PPCODE,2,5) + SUBSTRING(T1.U_PPCODE,2,5) +

SUBSTRING(T2.U_PPCODE,2,5)),5),

SUBSTRING(T2.U_FCODE,2,4), LEFT((SUBSTRING(T1.U_REGION,2,3) + SUBSTRING(T2.U_REGION,2,3)),3), LEFT(LTRIM(SUBSTRING (T0.U_MVTP,2,3) +

SUBSTRING (T2.U_MVTP,2,3)), 3)

*********************************************************

I found it quite inflexible to type in the date for the where clause direct into the query and modified it to

***********************

WHERE T0.RefDate >= CONVERT(DATETIME, '[%0]', 112) AND T0.RefDate <= CONVERT(DATETIME, '[%1]', 112)

***********************

In every other query I've made, this works, but it seems that in this query I'm not able to use ANY parameter (e.g. [%0]). I tested this for different variables and also without the set clause.

As a solution, I think of creating an UDT, where I store the values for the date, but this behaviour is quite strange, anyway...

Anyone got an idea, why this happens? Or is it just a bug in the SQL interpreter?