Help Please!!! Supplier Cost Local Cost Issues

HQ has issues when it comes to item costs.

It sounds like you need to report this problem to MS.

I reported a bug months ago, but have not yet seen a resolution. In my case, I noticed that if I create an item at HQ with a cost/supplier cost of $100, issue a WS250 to get the item to the store databases, then create a PO at the store level and change the cost to $110... When the item is received, the store database changes the item cost but does not pass this information to HQ. Item value reports at HQ are not correct. Transaction-based reports at HQ are correct, as they query the transaction table(s) and not the item table.

Good luck, Tom

Hi Tara - sorry for the delay on a followup...

I've only been working with RMS for about a year and a half but never had a chance to work with foreign currencies so I had to kick the tires a little bit...having to learn RMS also meant learning SQL on my spare time...so here's my two cents.

It appears that when you set up a supplier with a foreign currency, the supplier tab will add the "local cost" field but you're right, its a calculated field, - I too was unable to find either a field in a table or a separate table from supplierlist where this is tracked, so I think RMS performs an internal calculation to come up with "local cost" - here's what I did to test your scenario on a test db

First create a view that will track supplier info and conversion rate, your syntax is

create view supplierconversion as SELECT Supplier.ID AS Supplier_ID, Supplier.SupplierName, (case supplier.currencyid when 0 then '1' else (1/Currency.ExchangeRate) END) as Conversion FROM Supplier LEFT OUTER JOIN Currency ON Supplier.CurrencyID = Currency.ID

A view is basically a way to show data from one or multiple tables; run the following after you've created the view to see the "contents" of your view select * from supplierconversion

You will see ALL your suppliers, where those that work with the native currency will have a "1" in the conversion field, whereas those that work with US dollars or another foreign currency will display the conversion rate (the multiplier you will use to convert from foreign to local currency)

Before you run your update item query, remember to backup the database and do this after hours- also, try this on a test database before applying to your production (live) database -

Your update query is update item set item.cost = (supplierlist.cost * supplierconversion.conversion) from item inner join supplierlist on item.id = supplierlist.itemid inner join supplierconversion on supplierlist.supplierid supplierconversion.supplier_id

For those suppliers where the conversion is 1 to 1 (local currency) then the supplierlist.cost will be copied into item.cost; for those suppliers where the conversion is not 1 to 1, then the supplierlist.cost will be multiplied by the conversion to calculate the local cost which will then be inserted in item.cost

D> Hi, I don't know if I'm overlooking something totally obvious but I seem to

Hi! That worked perfectly! Thank you so much! That's pretty much what I envisioned would need doing but I didn't have a clue where to start as I've only got the basics of SQL myself. Any tips on how I can get to the level that you're at? A good beginner's guide would be great if you could recommend something!

You saved my bacon- thanks again!

T.

"c> Hi Tara - sorry for the delay on a followup...