What I need to do is set the current item cost (on the item properties page) for all my items to the highest cost for each item on any PO ever regardless of the PO status (closed, placed, etc) if it's not currently set to this already.
Before I do this I would like to print out a list of changes that will be made.
Can anybody help me with an SQL script for this (yes I will back up before doing anything!)?
Alex, Assuming you are ok with 0 being set for a cost if the item has never been Ordered, here is a script that should work for you.
UPDATE Item
SET Cost = ISNULL(
(SELECT Max(Price)
FROM PurchaseOrderEntry
Where ItemID = Item.ID
), 0)
I don't understand. This negates the reason for having a POS system in the first place. The whole purpose of POS is to track COGS, and changing the cost that is tracked by the system to the highest cost ever paid inflates your COGS. It will also inflate your inventory value so that the numbers you get on any report(sales or value) won't be accurate for any purpose. Craig
Is there anyway the below script can not update the cost if it has never been ordered (in other words just leave the cost alone if it is 0/has not been ordered)? The reason for this is that we have a lot of matrix items components where we have a cost that was originally copied from the matrix (general tab) but have never been ordered because of strange style/color/size combos. We would still like to leave the original/current cost in them just in case we ever do order them instead of overwriting them with a 0 cost.
I understand that this is usually not the way to handle this. But in our case we have used the backoffice/manager part of RMS for over 3 years to create POs (but haven't booked receipt) and to print labels and tags, but we didn't start using the POS part till about 6 months ago. So any product that has sold in the last 6 months has a negative on hand quantity since we never booked receipt on any of the POs. Also we don't have an accurate number on quantities sold prior to using the POS. So since we never/hardly ever have price decreases the most accurate thing would be to use the most current cost, which in our case happens to be the highest cost, which in turn would be the same as the replacement cost. I have thought about this for a while, but couldn't come up with a better solution, but if you or anybody else has a better solution to this problem or some suggestions I would love to hear them
This revised script should now only affect those items that have been ordered before:
UPDATE Item
SET Cost = (
SELECT Max(Price)
FROM PurchaseOrderEntry
Where ItemID = Item.ID
)WHERE (
SELECT COUNT(*)
FROM PurchaseOrderEntry
WHERE ItemID = Item.ID
) > 0
Have something to add? Share your thoughts — no account required.
Ask the community — no account required