Showing the Primary Currency in Query Results

[RahF]

Hi Experts

I have written a simple query to show me all components in a BOM up to one level, this works fine

Now what I want is show the currency that the component was received in, it shows in Item Master Data, but in the Query Results, it converts to local value

SELECT CASE WHEN T1.[Type] = '4' THEN 'Item' WHEN T1.[Type] = '290' THEN 'Resource' WHEN T1.[Type] = '-18' THEN 'Text' END AS 'Type', T1.Code as 'Product Code', T0.ItemName as 'Product Description', T1.Quantity as 'Qty', T1.Price as 'Unit Price', T1.Quantity*T1.Price as 'Total Price'
FROM ITT1 T1
INNER JOIN OITM T0 ON T0.ItemCode = T1.Code
WHERE T1.Father ='[%0\]' AND T1.Code Not In (SELECT CODE FROM OITT)
UNION ALL
SELECT CASE WHEN T0.[Type] = '4' THEN 'Item' WHEN T0.[Type] = '290' THEN 'Resource' WHEN T0.[Type] = '-18' THEN 'Text' END AS 'Type', T1.Code, T2.ItemName, T1.Quantity, T1.Price, T1.Quantity*T1.Price as 'Total Price'
FROM ITT1 T0
LEFT JOIN ITT1 T1 ON T1.Father=T0.Code
INNER JOIN OITM T2 ON T2.ItemCode = T1.Code
WHERE T0.Father ='[%0\]'
ORDER BY T1.[Code]

Is it possible to show the Unit Price in different columns? So

1. if one component was purchased in AUD then the query result has a AUD column and the component shows in that Unit Price column
2. if a component was purchased in EUR then the query result has a EUR column and the component shows in that Unit Price column and
3. if a component was purchased in USD then the query result has a USD column and the component shows in that Unit Price column

and so on...

If that cannot be achieved, how can I get the query result to show the actual primary currency and value?

Please help if possible

Regards

Rahul

[Johan_H]

Hi Rahul,

Please check if the columns ITT1.OrigPrice, and ITT1.OrigCurr hold the information you need.

Regards,

Johan

[RahF]

Hi Johan

Yes I have tried both OrigPrice & OrigCurr, but it still shows the local currency, not the USD or EUR that shows in the Item Master Data

Regards

Rahul