Hi,
When crating a BOM I found the item rate for one of the raw materials changed. I already have created many BOMs and the item price on those BOMs was as per the valuation rate but suddenly I found the price changed drastically for this BOM.
Example: Item 1 whose rate is Rs 0.276 changed to Rs 18.91 in this BOM. I have checked the valuation rate and the rate is still set to Rs 0.276. In the BOM “Rate Of Materials Based On” is set to Valuation rate and I am not sure where this price is coming from. I have already checked by clicking on the “Update Cost” in the BOM to no good. Also please note this item is in negative stock at the moment but it was negative a couple of days before and the item price was showing the valuation rate on the previous BOMs.
Any idea what could be changing the Item Price?
how and by which screen (field) you checked the valuation rate?
In the item master it’s set to .276
item master valuation rate can be manually maintained and it is used only when first material receipt without incoming rate, valuation rate is recalculated on item warehouse combination for each material receipt. in other word in the item valuation in your BOM cost is the latest valuation for the item and warehouse combination, you got to check the stock ledger to see the valuation rate on the latest material movement.
Here is how the item is setup.
Item list with UOM Nos:
Since the Item is purchased in Kg but used in Nos, here is how the conversion is set:
Here is how the conversion is set in the Purchase Order/Receipt:
Here is the stock ledger for that item showing negative balance (As allow negative balance was set in the Stock Setting):
With the above settings and usage, the price in the BOM is showing like this:
Can you help me with the calculation to see if the item price in he BOM is showing correct based on the above? It’s a huge difference and I am not sure how the negative stock is affecting it by increasing the price so much.
scroll the stock ledger report to the right , there are two columns incoming rate, valuation rate, you can study in detail each stock ledger entry how these 2 values changed(calculated), for more technical details maybe you got to seek help from some developer.
OK, I’ll check the those columns. Thank you, for your help!
program get valuation rate by the following sequence:
- average rate from doctype Bin(table TabBin), i.e total value / total qty
- valuation rate from latest valid stock ledger entry( valuation rate>0 and not cancelled)
- item master valuation rate.
def get_valuation_rate(args):
""" Get weighted average of valuation rate from all warehouses """
total_qty, total_value, valuation_rate = 0.0, 0.0, 0.0
item_bins = frappe.db.sql("""
select
bin.actual_qty, bin.stock_value
from
`tabBin` bin, `tabWarehouse` warehouse
where
bin.item_code=%(item)s
and bin.warehouse = warehouse.name
and warehouse.company=%(company)s""",
{"item": args['item_code'], "company": args['company']}, as_dict=1)
for d in item_bins:
total_qty += flt(d.actual_qty)
total_value += flt(d.stock_value)
if total_qty:
valuation_rate = total_value / total_qty
if valuation_rate <= 0:
last_valuation_rate = frappe.db.sql("""select valuation_rate
from `tabStock Ledger Entry`
where item_code = %s and valuation_rate > 0 and is_cancelled = 0
order by posting_date desc, posting_time desc, creation desc limit 1""", args['item_code'])
valuation_rate = flt(last_valuation_rate[0][0]) if last_valuation_rate else 0
if not valuation_rate:
valuation_rate = frappe.db.get_value("Item", args['item_code'], "valuation_rate")
return flt(valuation_rate)
