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
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