Merge pull request #26558 from rohitwaghchaure/fixed-incorrect-gross-profit-report
fix: incorrect valuation rate calculation in gross profit report
diff --git a/erpnext/accounts/report/gross_profit/gross_profit.py b/erpnext/accounts/report/gross_profit/gross_profit.py
index 84c7454..6d8623c 100644
--- a/erpnext/accounts/report/gross_profit/gross_profit.py
+++ b/erpnext/accounts/report/gross_profit/gross_profit.py
@@ -241,6 +241,7 @@
sle.voucher_detail_no == row.item_row:
previous_stock_value = len(my_sle) > i+1 and \
flt(my_sle[i+1].stock_value) or 0.0
+
if previous_stock_value:
return (previous_stock_value - flt(sle.stock_value)) * flt(row.qty) / abs(flt(sle.qty))
else:
@@ -335,7 +336,7 @@
res = frappe.db.sql("""select item_code, voucher_type, voucher_no,
voucher_detail_no, stock_value, warehouse, actual_qty as qty
from `tabStock Ledger Entry`
- where company=%(company)s
+ where company=%(company)s and is_cancelled = 0
order by
item_code desc, warehouse desc, posting_date desc,
posting_time desc, creation desc""", self.filters, as_dict=True)
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 2803193..21c052a 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -407,6 +407,7 @@
INNER JOIN `tabBatch` batch on sle.batch_no = batch.name
where
batch.disabled = 0
+ and sle.is_cancelled = 0
and sle.item_code = %(item_code)s
and sle.warehouse = %(warehouse)s
and (sle.batch_no like %(txt)s
diff --git a/erpnext/manufacturing/doctype/bom/bom.py b/erpnext/manufacturing/doctype/bom/bom.py
index 9da461f..2fbbca4 100644
--- a/erpnext/manufacturing/doctype/bom/bom.py
+++ b/erpnext/manufacturing/doctype/bom/bom.py
@@ -748,7 +748,7 @@
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
+ 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
diff --git a/erpnext/stock/doctype/batch/batch.py b/erpnext/stock/doctype/batch/batch.py
index b6eef6c..b37ae3f 100644
--- a/erpnext/stock/doctype/batch/batch.py
+++ b/erpnext/stock/doctype/batch/batch.py
@@ -162,19 +162,19 @@
out = float(frappe.db.sql("""select sum(actual_qty)
from `tabStock Ledger Entry`
- where warehouse=%s and batch_no=%s {0}""".format(cond),
+ where is_cancelled = 0 and warehouse=%s and batch_no=%s {0}""".format(cond),
(warehouse, batch_no))[0][0] or 0)
if batch_no and not warehouse:
out = frappe.db.sql('''select warehouse, sum(actual_qty) as qty
from `tabStock Ledger Entry`
- where batch_no=%s
+ where is_cancelled = 0 and batch_no=%s
group by warehouse''', batch_no, as_dict=1)
if not batch_no and item_code and warehouse:
out = frappe.db.sql('''select batch_no, sum(actual_qty) as qty
from `tabStock Ledger Entry`
- where item_code = %s and warehouse=%s
+ where is_cancelled = 0 and item_code = %s and warehouse=%s
group by batch_no''', (item_code, warehouse), as_dict=1)
return out
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index e795742..516ae43 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -239,6 +239,7 @@
and sle.`item_code`=%(item_code)s
and sle.`company` = %(company)s
and batch.disabled = 0
+ and sle.is_cancelled=0
and IFNULL(batch.`expiry_date`, '2200-01-01') > %(today)s
{warehouse_condition}
GROUP BY
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 872b1d0..654755e 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -1789,7 +1789,7 @@
from `tabBatch` b, `tabStock Ledger Entry` sle
where b.expiry_date <= %s
and b.expiry_date is not NULL
- and b.batch_id = sle.batch_no
+ and b.batch_id = sle.batch_no and sle.is_cancelled = 0
group by sle.warehouse, sle.item_code, sle.batch_no""",(nowdate()), as_dict=1)
@frappe.whitelist()
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index 93482e8..b4f4583 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -60,7 +60,7 @@
if self.batch_no and not self.get("allow_negative_stock"):
batch_bal_after_transaction = flt(frappe.db.sql("""select sum(actual_qty)
from `tabStock Ledger Entry`
- where warehouse=%s and item_code=%s and batch_no=%s""",
+ where is_cancelled =0 and warehouse=%s and item_code=%s and batch_no=%s""",
(self.warehouse, self.item_code, self.batch_no))[0][0])
if batch_bal_after_transaction < 0:
@@ -152,7 +152,7 @@
last_transaction_time = frappe.db.sql("""
select MAX(timestamp(posting_date, posting_time)) as posting_time
from `tabStock Ledger Entry`
- where docstatus = 1 and item_code = %s
+ where docstatus = 1 and is_cancelled = 0 and item_code = %s
and warehouse = %s""", (self.item_code, self.warehouse))[0][0]
cur_doc_posting_datetime = "%s %s" % (self.posting_date, self.get("posting_time") or "00:00:00")
diff --git a/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py b/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
index 5873a7a..4108a57 100644
--- a/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
+++ b/erpnext/stock/report/supplier_wise_sales_analytics/supplier_wise_sales_analytics.py
@@ -69,7 +69,7 @@
i.stock_uom, sle.actual_qty, sle.stock_value_difference,
sle.voucher_no, sle.voucher_type
from `tabStock Ledger Entry` sle, `tabItem` i
- where sle.item_code=i.name and sle.actual_qty < 0 %s""" % conditions, values, as_dict=1):
+ where sle.is_cancelled = 0 and sle.item_code=i.name and sle.actual_qty < 0 %s""" % conditions, values, as_dict=1):
consumed_details.setdefault(d.item_code, []).append(d)
return consumed_details