Merge pull request #30913 from ankush/against_voucher_processing
fix: double future qty updates
diff --git a/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
index 5850ec7..4e2fc83 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/test_stock_ledger_entry.py
@@ -1183,6 +1183,42 @@
backdated.cancel()
self.assertEqual([1], ordered_qty_after_transaction())
+ def test_timestamp_clash(self):
+
+ item = make_item().name
+ warehouse = "_Test Warehouse - _TC"
+
+ reciept = make_stock_entry(
+ item_code=item,
+ to_warehouse=warehouse,
+ qty=100,
+ rate=10,
+ posting_date="2021-01-01",
+ posting_time="01:00:00",
+ )
+
+ consumption = make_stock_entry(
+ item_code=item,
+ from_warehouse=warehouse,
+ qty=50,
+ posting_date="2021-01-01",
+ posting_time="02:00:00.1234", # ms are possible when submitted without editing posting time
+ )
+
+ backdated_receipt = make_stock_entry(
+ item_code=item,
+ to_warehouse=warehouse,
+ qty=100,
+ posting_date="2021-01-01",
+ rate=10,
+ posting_time="02:00:00", # same posting time as consumption but ms part stripped
+ )
+
+ try:
+ backdated_receipt.cancel()
+ except Exception as e:
+ self.fail("Double processing of qty for clashing timestamp.")
+
def create_repack_entry(**args):
args = frappe._dict(args)
diff --git a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index e7b89b1..9088eb8 100644
--- a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -31,6 +31,7 @@
def tearDown(self):
frappe.local.future_sle = {}
+ frappe.flags.pop("dont_execute_stock_reposts", None)
def test_reco_for_fifo(self):
self._test_reco_sle_gle("FIFO")
@@ -384,6 +385,7 @@
-------------------------------------------
Var | Doc | Qty | Balance
-------------------------------------------
+ PR5 | PR | 10 | 10 (posting date: today-4) [backdated]
SR5 | Reco | 0 | 8 (posting date: today-4) [backdated]
PR1 | PR | 10 | 18 (posting date: today-3)
PR2 | PR | 1 | 19 (posting date: today-2)
@@ -393,6 +395,14 @@
item_code = make_item().name
warehouse = "_Test Warehouse - _TC"
+ frappe.flags.dont_execute_stock_reposts = True
+
+ def assertBalance(doc, qty_after_transaction):
+ sle_balance = frappe.db.get_value(
+ "Stock Ledger Entry", {"voucher_no": doc.name, "is_cancelled": 0}, "qty_after_transaction"
+ )
+ self.assertEqual(sle_balance, qty_after_transaction)
+
pr1 = make_purchase_receipt(
item_code=item_code, warehouse=warehouse, qty=10, rate=100, posting_date=add_days(nowdate(), -3)
)
@@ -402,62 +412,37 @@
pr3 = make_purchase_receipt(
item_code=item_code, warehouse=warehouse, qty=1, rate=100, posting_date=nowdate()
)
-
- pr1_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr1.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- pr3_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr3.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- self.assertEqual(pr1_balance, 10)
- self.assertEqual(pr3_balance, 12)
+ assertBalance(pr1, 10)
+ assertBalance(pr3, 12)
# post backdated stock reco in between
sr4 = create_stock_reconciliation(
item_code=item_code, warehouse=warehouse, qty=6, rate=100, posting_date=add_days(nowdate(), -1)
)
- pr3_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr3.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- self.assertEqual(pr3_balance, 7)
+ assertBalance(pr3, 7)
# post backdated stock reco at the start
sr5 = create_stock_reconciliation(
item_code=item_code, warehouse=warehouse, qty=8, rate=100, posting_date=add_days(nowdate(), -4)
)
- pr1_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr1.name, "is_cancelled": 0}, "qty_after_transaction"
+ assertBalance(pr1, 18)
+ assertBalance(pr2, 19)
+ assertBalance(sr4, 6) # check if future stock reco is unaffected
+
+ # Make a backdated receipt and check only entries till first SR are affected
+ pr5 = make_purchase_receipt(
+ item_code=item_code, warehouse=warehouse, qty=10, rate=100, posting_date=add_days(nowdate(), -5)
)
- pr2_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr2.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- sr4_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": sr4.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- self.assertEqual(pr1_balance, 18)
- self.assertEqual(pr2_balance, 19)
- self.assertEqual(sr4_balance, 6) # check if future stock reco is unaffected
+ assertBalance(pr5, 10)
+ # check if future stock reco is unaffected
+ assertBalance(sr4, 6)
+ assertBalance(sr5, 8)
# cancel backdated stock reco and check future impact
sr5.cancel()
- pr1_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr1.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- pr2_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": pr2.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- sr4_balance = frappe.db.get_value(
- "Stock Ledger Entry", {"voucher_no": sr4.name, "is_cancelled": 0}, "qty_after_transaction"
- )
- self.assertEqual(pr1_balance, 10)
- self.assertEqual(pr2_balance, 11)
- self.assertEqual(sr4_balance, 6) # check if future stock reco is unaffected
-
- # teardown
- sr4.cancel()
- pr3.cancel()
- pr2.cancel()
- pr1.cancel()
+ assertBalance(pr1, 10)
+ assertBalance(pr2, 11)
+ assertBalance(sr4, 6) # check if future stock reco is unaffected
@change_settings("Stock Settings", {"allow_negative_stock": 0})
def test_backdated_stock_reco_future_negative_stock(self):
@@ -563,7 +548,6 @@
# repost will make this test useless, qty should update in realtime without reposts
frappe.flags.dont_execute_stock_reposts = True
- self.addCleanup(frappe.flags.pop, "dont_execute_stock_reposts")
item_code = make_item().name
warehouse = "_Test Warehouse - _TC"
diff --git a/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
index 837c4a6..ed0e2fc 100644
--- a/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
+++ b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
@@ -111,17 +111,17 @@
},
{
"fieldname": "posting_date",
- "fieldtype": "Date",
+ "fieldtype": "Data",
"label": _("Posting Date"),
},
{
"fieldname": "posting_time",
- "fieldtype": "Time",
+ "fieldtype": "Data",
"label": _("Posting Time"),
},
{
"fieldname": "creation",
- "fieldtype": "Datetime",
+ "fieldtype": "Data",
"label": _("Creation"),
},
{
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 7e5c231..4789b52 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -1303,6 +1303,8 @@
datetime_limit_condition = ""
qty_shift = args.actual_qty
+ args["time_format"] = "%H:%i:%s"
+
# find difference/shift in qty caused by stock reconciliation
if args.voucher_type == "Stock Reconciliation":
qty_shift = get_stock_reco_qty_shift(args)
@@ -1315,7 +1317,7 @@
datetime_limit_condition = get_datetime_limit_condition(detail)
frappe.db.sql(
- """
+ f"""
update `tabStock Ledger Entry`
set qty_after_transaction = qty_after_transaction + {qty_shift}
where
@@ -1323,16 +1325,10 @@
and warehouse = %(warehouse)s
and voucher_no != %(voucher_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
- )
- )
+ and timestamp(posting_date, time_format(posting_time, %(time_format)s))
+ > timestamp(%(posting_date)s, time_format(%(posting_time)s, %(time_format)s))
{datetime_limit_condition}
- """.format(
- qty_shift=qty_shift, datetime_limit_condition=datetime_limit_condition
- ),
+ """,
args,
)
@@ -1383,6 +1379,7 @@
and creation > %(creation)s
)
)
+ order by timestamp(posting_date, posting_time) asc, creation asc
limit 1
""",
args,