feat: batch wise valuation rates
start with most used case: negative inventory isn't enabled
- simple addition of qty and value when new batch qty is added
- fetch outgoing rate from stock movement of specific batch
diff --git a/erpnext/stock/doctype/batch/test_batch.py b/erpnext/stock/doctype/batch/test_batch.py
index 0a663c2..e7d04db 100644
--- a/erpnext/stock/doctype/batch/test_batch.py
+++ b/erpnext/stock/doctype/batch/test_batch.py
@@ -7,6 +7,7 @@
from erpnext.accounts.doctype.purchase_invoice.test_purchase_invoice import make_purchase_invoice
from erpnext.stock.doctype.batch.batch import UnableToSelectBatchError, get_batch_no, get_batch_qty
+from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry
from erpnext.stock.get_item_details import get_item_details
from erpnext.tests.utils import ERPNextTestCase
@@ -300,6 +301,51 @@
details = get_item_details(args)
self.assertEqual(details.get('price_list_rate'), 400)
+
+ def test_basic_batch_wise_valuation(self, batch_qty = 100):
+ item_code = "_TestBatchWiseVal"
+ warehouse = "_Test Warehouse - _TC"
+ self.make_batch_item(item_code)
+
+ rates = [42, 420]
+
+ batches = {}
+ for rate in rates:
+ se = make_stock_entry(item_code=item_code, qty=10, rate=rate, target=warehouse)
+ batches[se.items[0].batch_no] = rate
+
+ LOW, HIGH = list(batches.keys())
+
+ # consume things out of order
+ consumption_plan = [
+ (HIGH, 1),
+ (LOW, 2),
+ (HIGH, 2),
+ (HIGH, 4),
+ (LOW, 6),
+ ]
+
+ stock_value = sum(rates) * 10
+ qty_after_transaction = 20
+ for batch, qty in consumption_plan:
+ # consume out of order
+ se = make_stock_entry(item_code=item_code, source=warehouse, qty=qty, batch_no=batch)
+
+ sle = frappe.get_last_doc("Stock Ledger Entry", {"is_cancelled": 0, "voucher_no": se.name})
+
+ stock_value_difference = sle.actual_qty * batches[sle.batch_no]
+ self.assertAlmostEqual(sle.stock_value_difference, stock_value_difference)
+
+ stock_value += stock_value_difference
+ self.assertAlmostEqual(sle.stock_value, stock_value)
+
+ qty_after_transaction += sle.actual_qty
+ self.assertAlmostEqual(sle.qty_after_transaction, qty_after_transaction)
+ self.assertAlmostEqual(sle.valuation_rate, stock_value / qty_after_transaction)
+
+ self.assertEqual(sle.stock_queue, []) # queues don't apply on batched items
+
+
def create_batch(item_code, rate, create_item_price_for_batch):
pi = make_purchase_invoice(company="_Test Company",
warehouse= "Stores - _TC", cost_center = "Main - _TC", update_stock=1,
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 00ca81f..c33cc12 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -447,6 +447,8 @@
self.wh_data.qty_after_transaction = sle.qty_after_transaction
self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt(self.wh_data.valuation_rate)
+ elif sle.batch_no and frappe.db.get_value("Batch", sle.batch_no, "use_batchwise_valuation", cache=True):
+ self.update_batched_values(sle)
else:
if sle.voucher_type=="Stock Reconciliation" and not sle.batch_no:
# assert
@@ -481,6 +483,7 @@
if not self.args.get("sle_id"):
self.update_outgoing_rate_on_transaction(sle)
+
def validate_negative_stock(self, sle):
"""
validate negative stock for entries current datetime onwards
@@ -736,7 +739,22 @@
if not self.wh_data.stock_queue:
self.wh_data.stock_queue.append([0, sle.incoming_rate or sle.outgoing_rate or self.wh_data.valuation_rate])
+ def update_batched_values(self, sle):
+ incoming_rate = flt(sle.incoming_rate)
+ actual_qty = flt(sle.actual_qty)
+ self.wh_data.qty_after_transaction += actual_qty
+
+ if actual_qty > 0:
+ stock_value_difference = incoming_rate * actual_qty
+ self.wh_data.stock_value += stock_value_difference
+ else:
+ outgoing_rate = _get_batch_outgoing_rate(item_code=sle.item_code, warehouse=sle.warehouse, batch_no=sle.batch_no, posting_date=sle.posting_date, posting_time=sle.posting_time, creation=sle.creation)
+ stock_value_difference = outgoing_rate * actual_qty
+ self.wh_data.stock_value += stock_value_difference
+
+ if self.wh_data.qty_after_transaction:
+ self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction
def check_if_allow_zero_valuation_rate(self, voucher_type, voucher_detail_no):
ref_item_dt = ""
@@ -897,6 +915,40 @@
['item_code', 'warehouse', 'posting_date', 'posting_time', 'timestamp(posting_date, posting_time) as timestamp'],
as_dict=1)
+def _get_batch_outgoing_rate(item_code, warehouse, batch_no, posting_date, posting_time, creation):
+
+ batch_details = frappe.db.sql("""
+ select sum(stock_value_difference) as batch_value, sum(actual_qty) as batch_qty
+ from `tabStock Ledger Entry`
+ where
+ item_code = %(item_code)s
+ and warehouse = %(warehouse)s
+ and batch_no = %(batch_no)s
+ and is_cancelled = 0
+ and (
+ timestamp(posting_date, posting_time) < timestamp(%(posting_date)s, %(posting_time)s)
+ or (
+ timestamp(posting_date, posting_time) = timestamp(%(posting_date)s, %(posting_time)s)
+ and creation < %(creation)s
+ )
+ )
+ """,
+ {
+ "item_code": item_code,
+ "warehouse": warehouse,
+ "batch_no": batch_no,
+ "posting_date": posting_date,
+ "posting_time": posting_time,
+ "creation": creation,
+ },
+ as_dict=True
+ )
+
+ if batch_details and batch_details[0].batch_qty:
+ return batch_details[0].batch_value / batch_details[0].batch_qty
+
+
+
def get_valuation_rate(item_code, warehouse, voucher_type, voucher_no,
allow_zero_rate=False, currency=None, company=None, raise_error_if_no_rate=True):