fix: incorrect active serial nos
diff --git a/erpnext/public/js/utils/serial_no_batch_selector.js b/erpnext/public/js/utils/serial_no_batch_selector.js
index 44a4957..80ade70 100644
--- a/erpnext/public/js/utils/serial_no_batch_selector.js
+++ b/erpnext/public/js/utils/serial_no_batch_selector.js
@@ -71,6 +71,10 @@
let warehouse = this.item?.type_of_transaction === "Outward" ?
(this.item.warehouse || this.item.s_warehouse) : "";
+ if (!warehouse && this.frm.doc.doctype === 'Stock Reconciliation') {
+ warehouse = this.get_warehouse();
+ }
+
return {
'item_code': this.item.item_code,
'warehouse': ["=", warehouse]
diff --git a/erpnext/stock/doctype/serial_and_batch_bundle/serial_and_batch_bundle.py b/erpnext/stock/doctype/serial_and_batch_bundle/serial_and_batch_bundle.py
index 63cc938..9cad8f6 100644
--- a/erpnext/stock/doctype/serial_and_batch_bundle/serial_and_batch_bundle.py
+++ b/erpnext/stock/doctype/serial_and_batch_bundle/serial_and_batch_bundle.py
@@ -250,6 +250,7 @@
for d in self.entries:
available_qty = 0
+
if self.has_serial_no:
d.incoming_rate = abs(sn_obj.serial_no_incoming_rate.get(d.serial_no, 0.0))
else:
@@ -892,6 +893,13 @@
elif batch_nos:
self.set("entries", batch_nos)
+ def delete_serial_batch_entries(self):
+ SBBE = frappe.qb.DocType("Serial and Batch Entry")
+
+ frappe.qb.from_(SBBE).delete().where(SBBE.parent == self.name).run()
+
+ self.set("entries", [])
+
@frappe.whitelist()
def download_blank_csv_template(content):
@@ -1374,10 +1382,12 @@
elif kwargs.based_on == "Expiry":
order_by = "amc_expiry_date asc"
- filters = {"item_code": kwargs.item_code, "warehouse": ("is", "set")}
+ filters = {"item_code": kwargs.item_code}
- if kwargs.warehouse:
- filters["warehouse"] = kwargs.warehouse
+ if not kwargs.get("ignore_warehouse"):
+ filters["warehouse"] = ("is", "set")
+ if kwargs.warehouse:
+ filters["warehouse"] = kwargs.warehouse
# Since SLEs are not present against Reserved Stock [POS invoices, SRE], need to ignore reserved serial nos.
ignore_serial_nos = get_reserved_serial_nos(kwargs)
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 6819968..788ae0d 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -156,6 +156,7 @@
"warehouse": item.warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
+ "ignore_warehouse": 1,
}
)
)
@@ -780,7 +781,20 @@
current_qty = 0.0
if row.current_serial_and_batch_bundle:
- current_qty = self.get_qty_for_serial_and_batch_bundle(row)
+ current_qty = self.get_current_qty_for_serial_or_batch(row)
+ elif row.serial_no:
+ item_dict = get_stock_balance_for(
+ row.item_code,
+ row.warehouse,
+ self.posting_date,
+ self.posting_time,
+ voucher_no=self.name,
+ )
+
+ current_qty = item_dict.get("qty")
+ row.current_serial_no = item_dict.get("serial_nos")
+ row.current_valuation_rate = item_dict.get("rate")
+ val_rate = item_dict.get("rate")
elif row.batch_no:
current_qty = get_batch_qty_for_stock_reco(
row.item_code, row.warehouse, row.batch_no, self.posting_date, self.posting_time, self.name
@@ -788,15 +802,16 @@
precesion = row.precision("current_qty")
if flt(current_qty, precesion) != flt(row.current_qty, precesion):
- val_rate = get_valuation_rate(
- row.item_code,
- row.warehouse,
- self.doctype,
- self.name,
- company=self.company,
- batch_no=row.batch_no,
- serial_and_batch_bundle=row.current_serial_and_batch_bundle,
- )
+ if not row.serial_no:
+ val_rate = get_valuation_rate(
+ row.item_code,
+ row.warehouse,
+ self.doctype,
+ self.name,
+ company=self.company,
+ batch_no=row.batch_no,
+ serial_and_batch_bundle=row.current_serial_and_batch_bundle,
+ )
row.current_valuation_rate = val_rate
row.current_qty = current_qty
@@ -842,11 +857,56 @@
return allow_negative_stock
- def get_qty_for_serial_and_batch_bundle(self, row):
+ def get_current_qty_for_serial_or_batch(self, row):
doc = frappe.get_doc("Serial and Batch Bundle", row.current_serial_and_batch_bundle)
- precision = doc.entries[0].precision("qty")
+ current_qty = 0.0
+ if doc.has_serial_no:
+ current_qty = self.get_current_qty_for_serial_nos(doc)
+ elif doc.has_batch_no:
+ current_qty = self.get_current_qty_for_batch_nos(doc)
- current_qty = 0
+ return abs(current_qty)
+
+ def get_current_qty_for_serial_nos(self, doc):
+ serial_nos_details = get_available_serial_nos(
+ frappe._dict(
+ {
+ "item_code": doc.item_code,
+ "warehouse": doc.warehouse,
+ "posting_date": self.posting_date,
+ "posting_time": self.posting_time,
+ "voucher_no": self.name,
+ "ignore_warehouse": 1,
+ }
+ )
+ )
+
+ if not serial_nos_details:
+ return 0.0
+
+ doc.delete_serial_batch_entries()
+ current_qty = 0.0
+ for serial_no_row in serial_nos_details:
+ current_qty += 1
+ doc.append(
+ "entries",
+ {
+ "serial_no": serial_no_row.serial_no,
+ "qty": -1,
+ "warehouse": doc.warehouse,
+ "batch_no": serial_no_row.batch_no,
+ },
+ )
+
+ doc.set_incoming_rate(save=True)
+ doc.calculate_qty_and_amount(save=True)
+ doc.db_update_all()
+
+ return current_qty
+
+ def get_current_qty_for_batch_nos(self, doc):
+ current_qty = 0.0
+ precision = doc.entries[0].precision("qty")
for d in doc.entries:
qty = (
get_batch_qty(
@@ -864,7 +924,7 @@
current_qty += qty
- return abs(current_qty)
+ return current_qty
def get_batch_qty_for_stock_reco(
diff --git a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index 70e9fb2..0bbfed4 100644
--- a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -925,6 +925,74 @@
self.assertEqual(len(serial_batch_bundle), 0)
+ def test_backdated_purchase_receipt_with_stock_reco(self):
+ item_code = self.make_item(
+ properties={
+ "is_stock_item": 1,
+ "has_serial_no": 1,
+ "serial_no_series": "TEST-SERIAL-.###",
+ }
+ ).name
+
+ warehouse = "_Test Warehouse - _TC"
+
+ # Step - 1: Create a Backdated Purchase Receipt
+
+ pr1 = make_purchase_receipt(
+ item_code=item_code, warehouse=warehouse, qty=10, rate=100, posting_date=add_days(nowdate(), -3)
+ )
+ pr1.reload()
+
+ serial_nos = sorted(get_serial_nos_from_bundle(pr1.items[0].serial_and_batch_bundle))[:5]
+
+ # Step - 2: Create a Stock Reconciliation
+ sr1 = create_stock_reconciliation(
+ item_code=item_code,
+ warehouse=warehouse,
+ qty=5,
+ serial_no=serial_nos,
+ )
+
+ data = frappe.get_all(
+ "Stock Ledger Entry",
+ fields=["serial_no", "actual_qty", "stock_value_difference"],
+ filters={"voucher_no": sr1.name, "is_cancelled": 0},
+ order_by="creation",
+ )
+
+ for d in data:
+ if d.actual_qty < 0:
+ self.assertEqual(d.actual_qty, -10.0)
+ self.assertAlmostEqual(d.stock_value_difference, -1000.0)
+ else:
+ self.assertEqual(d.actual_qty, 5.0)
+ self.assertAlmostEqual(d.stock_value_difference, 500.0)
+
+ # Step - 3: Create a Purchase Receipt before the first Purchase Receipt
+ make_purchase_receipt(
+ item_code=item_code, warehouse=warehouse, qty=10, rate=200, posting_date=add_days(nowdate(), -5)
+ )
+
+ data = frappe.get_all(
+ "Stock Ledger Entry",
+ fields=["serial_no", "actual_qty", "stock_value_difference"],
+ filters={"voucher_no": sr1.name, "is_cancelled": 0},
+ order_by="creation",
+ )
+
+ for d in data:
+ if d.actual_qty < 0:
+ self.assertEqual(d.actual_qty, -20.0)
+ self.assertAlmostEqual(d.stock_value_difference, -3000.0)
+ else:
+ self.assertEqual(d.actual_qty, 5.0)
+ self.assertAlmostEqual(d.stock_value_difference, 500.0)
+
+ active_serial_no = frappe.get_all(
+ "Serial No", filters={"status": "Active", "item_code": item_code}
+ )
+ self.assertEqual(len(active_serial_no), 5)
+
def create_batch_item_with_batch(item_name, batch_id):
batch_item_doc = create_item(item_name, is_stock_item=1)
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 0370666..45764f3 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -9,9 +9,18 @@
import frappe
from frappe import _, scrub
from frappe.model.meta import get_field_precision
-from frappe.query_builder import Case
from frappe.query_builder.functions import CombineDatetime, Sum
-from frappe.utils import cint, flt, get_link_to_form, getdate, now, nowdate, nowtime, parse_json
+from frappe.utils import (
+ cint,
+ cstr,
+ flt,
+ get_link_to_form,
+ getdate,
+ now,
+ nowdate,
+ nowtime,
+ parse_json,
+)
import erpnext
from erpnext.stock.doctype.bin.bin import update_qty as update_bin_qty
@@ -712,11 +721,10 @@
if (
sle.voucher_type == "Stock Reconciliation"
- and (
- sle.batch_no or (sle.has_batch_no and sle.serial_and_batch_bundle and not sle.has_serial_no)
- )
+ and (sle.batch_no or sle.serial_no or sle.serial_and_batch_bundle)
and sle.voucher_detail_no
and not self.args.get("sle_id")
+ and sle.is_cancelled == 0
):
self.reset_actual_qty_for_stock_reco(sle)
@@ -737,6 +745,23 @@
if sle.serial_and_batch_bundle:
self.calculate_valuation_for_serial_batch_bundle(sle)
+ elif sle.serial_no and not self.args.get("sle_id"):
+ # Only run in reposting
+ self.get_serialized_values(sle)
+ self.wh_data.qty_after_transaction += flt(sle.actual_qty)
+ if sle.voucher_type == "Stock Reconciliation" and not sle.batch_no:
+ 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)
+ and not self.args.get("sle_id")
+ ):
+ # Only run in reposting
+ self.update_batched_values(sle)
else:
if sle.voucher_type == "Stock Reconciliation" and not sle.batch_no and not has_dimensions:
# assert
@@ -782,6 +807,45 @@
):
self.update_outgoing_rate_on_transaction(sle)
+ def get_serialized_values(self, sle):
+ incoming_rate = flt(sle.incoming_rate)
+ actual_qty = flt(sle.actual_qty)
+ serial_nos = cstr(sle.serial_no).split("\n")
+
+ if incoming_rate < 0:
+ # wrong incoming rate
+ incoming_rate = self.wh_data.valuation_rate
+
+ stock_value_change = 0
+ if actual_qty > 0:
+ stock_value_change = actual_qty * incoming_rate
+ else:
+ # In case of delivery/stock issue, get average purchase rate
+ # of serial nos of current entry
+ if not sle.is_cancelled:
+ outgoing_value = self.get_incoming_value_for_serial_nos(sle, serial_nos)
+ stock_value_change = -1 * outgoing_value
+ else:
+ stock_value_change = actual_qty * sle.outgoing_rate
+
+ new_stock_qty = self.wh_data.qty_after_transaction + actual_qty
+
+ if new_stock_qty > 0:
+ new_stock_value = (
+ self.wh_data.qty_after_transaction * self.wh_data.valuation_rate
+ ) + stock_value_change
+ if new_stock_value >= 0:
+ # calculate new valuation rate only if stock value is positive
+ # else it remains the same as that of previous entry
+ self.wh_data.valuation_rate = new_stock_value / new_stock_qty
+
+ if not self.wh_data.valuation_rate and sle.voucher_detail_no:
+ allow_zero_rate = self.check_if_allow_zero_valuation_rate(
+ sle.voucher_type, sle.voucher_detail_no
+ )
+ if not allow_zero_rate:
+ self.wh_data.valuation_rate = self.get_fallback_rate(sle)
+
def reset_actual_qty_for_stock_reco(self, sle):
doc = frappe.get_cached_doc("Stock Reconciliation", sle.voucher_no)
doc.recalculate_current_qty(sle.voucher_detail_no, sle.creation, sle.actual_qty > 0)
@@ -795,6 +859,36 @@
if abs(sle.actual_qty) == 0.0:
sle.is_cancelled = 1
+ if sle.serial_and_batch_bundle and frappe.get_cached_value(
+ "Item", sle.item_code, "has_serial_no"
+ ):
+ self.update_serial_no_status(sle)
+
+ def update_serial_no_status(self, sle):
+ from erpnext.stock.serial_batch_bundle import get_serial_nos
+
+ serial_nos = get_serial_nos(sle.serial_and_batch_bundle)
+ if not serial_nos:
+ return
+
+ warehouse = None
+ status = "Inactive"
+
+ if sle.actual_qty > 0:
+ warehouse = sle.warehouse
+ status = "Active"
+
+ sn_table = frappe.qb.DocType("Serial No")
+
+ query = (
+ frappe.qb.update(sn_table)
+ .set(sn_table.warehouse, warehouse)
+ .set(sn_table.status, status)
+ .where(sn_table.name.isin(serial_nos))
+ )
+
+ query.run()
+
def calculate_valuation_for_serial_batch_bundle(self, sle):
doc = frappe.get_cached_doc("Serial and Batch Bundle", sle.serial_and_batch_bundle)
@@ -1171,11 +1265,12 @@
outgoing_rate = get_batch_incoming_rate(
item_code=sle.item_code,
warehouse=sle.warehouse,
- serial_and_batch_bundle=sle.serial_and_batch_bundle,
+ batch_no=sle.batch_no,
posting_date=sle.posting_date,
posting_time=sle.posting_time,
creation=sle.creation,
)
+
if outgoing_rate is None:
# This can *only* happen if qty available for the batch is zero.
# in such case fall back various other rates.
@@ -1449,11 +1544,10 @@
def get_batch_incoming_rate(
- item_code, warehouse, serial_and_batch_bundle, posting_date, posting_time, creation=None
+ item_code, warehouse, batch_no, posting_date, posting_time, creation=None
):
sle = frappe.qb.DocType("Stock Ledger Entry")
- batch_ledger = frappe.qb.DocType("Serial and Batch Entry")
timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime(
posting_date, posting_time
@@ -1464,28 +1558,13 @@
== CombineDatetime(posting_date, posting_time)
) & (sle.creation < creation)
- batches = frappe.get_all(
- "Serial and Batch Entry", fields=["batch_no"], filters={"parent": serial_and_batch_bundle}
- )
-
batch_details = (
frappe.qb.from_(sle)
- .inner_join(batch_ledger)
- .on(sle.serial_and_batch_bundle == batch_ledger.parent)
- .select(
- Sum(
- Case()
- .when(sle.actual_qty > 0, batch_ledger.qty * batch_ledger.incoming_rate)
- .else_(batch_ledger.qty * batch_ledger.outgoing_rate * -1)
- ).as_("batch_value"),
- Sum(Case().when(sle.actual_qty > 0, batch_ledger.qty).else_(batch_ledger.qty * -1)).as_(
- "batch_qty"
- ),
- )
+ .select(Sum(sle.stock_value_difference).as_("batch_value"), Sum(sle.actual_qty).as_("batch_qty"))
.where(
(sle.item_code == item_code)
& (sle.warehouse == warehouse)
- & (batch_ledger.batch_no.isin([row.batch_no for row in batches]))
+ & (sle.batch_no == batch_no)
& (sle.is_cancelled == 0)
)
.where(timestamp_condition)