Worst case scenario I have a lot of back dated transactions and it’ll be costly to redo them. How can I change the UOM for a given item from SQL?
Should I expect any serious issues if I take this route?
Someone else thought of it but it they did not paste the solution back here.
we faced the same issue in one implementation and we ended up by writing our own SQL script change it.
but this script will not change the price or valuation rate.
let me know if you need that script
Yes please Adnan. Valuation and Price matter less to our needs!
set @UOM = ‘PKT 100 PCS’, @item_code=‘5426’;
update tabItem set stock_uom=@UOM where name in (@item_code);
update tabUOM Conversion Detail set uom=@UOM where parent in (@item_code) and conversion_factor=1;
update tabBin set stock_uom=@UOM where item_code in (@item_code);
update tabStock Ledger Entry set stock_uom=@UOM where item_code in (@item_code);
before you run this script, test it on a development server first.
Many thanks Adnan. I’ll revert with results.
This is not enough. You have to set “Stock UOM” field as well in all the transactions.
yes Nabin you are right, we are skipping , purchase, receipt , sales, order etc intentionally and the above script will fix the Stock realted entries and will work fine for all future transactions.
What’s the risk of not doing this?
Step 1: first update items in purchase order or another doctype
Step 2: update price list of item
Step 3: update Bin stock uom
Step 4: item UOM update
This is query of update BIN stock UOM in system console
item_code = “any” # Replace with your item code
new_stock_uom = “any” # Replace with your desired Stock UOM
bins = frappe.get_all(“Bin”, filters={“item_code”: item_code}, fields=[“name”])
for bin_record in bins:
# Load the Bin document
bin_doc = frappe.get_doc(“Bin”, bin_record.name)
# Update the stock_uom field
bin_doc.stock_uom = new_stock_uom
# Save the changes
bin_doc.save()
frappe.db.commit()
frappe.msgprint(f"Stock UOM for item {item_code} updated to {new_stock_uom} in all related Bin records.")