Merge pull request #29549 from frappe/mergify/bp/develop/pr-29290
fix: billed amount in delivery note items (backport #29290)
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index bc44358..4fa7718 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -1249,14 +1249,14 @@
def update_billing_status_in_dn(self, update_modified=True):
updated_delivery_notes = []
for d in self.get("items"):
- if d.dn_detail:
+ if d.so_detail:
+ updated_delivery_notes += update_billed_amount_based_on_so(d.so_detail, update_modified)
+ elif d.dn_detail:
billed_amt = frappe.db.sql("""select sum(amount) from `tabSales Invoice Item`
where dn_detail=%s and docstatus=1""", d.dn_detail)
billed_amt = billed_amt and billed_amt[0][0] or 0
frappe.db.set_value("Delivery Note Item", d.dn_detail, "billed_amt", billed_amt, update_modified=update_modified)
updated_delivery_notes.append(d.delivery_note)
- elif d.so_detail:
- updated_delivery_notes += update_billed_amount_based_on_so(d.so_detail, update_modified)
for dn in set(updated_delivery_notes):
frappe.get_doc("Delivery Note", dn).update_billing_percentage(update_modified=update_modified)
diff --git a/erpnext/stock/doctype/delivery_note/delivery_note.py b/erpnext/stock/doctype/delivery_note/delivery_note.py
index d1e2244..c3247fb 100644
--- a/erpnext/stock/doctype/delivery_note/delivery_note.py
+++ b/erpnext/stock/doctype/delivery_note/delivery_note.py
@@ -339,17 +339,35 @@
frappe.throw(_("Could not create Credit Note automatically, please uncheck 'Issue Credit Note' and submit again"))
def update_billed_amount_based_on_so(so_detail, update_modified=True):
+ from frappe.query_builder.functions import Sum
+
# Billed against Sales Order directly
- billed_against_so = frappe.db.sql("""select sum(amount) from `tabSales Invoice Item`
- where so_detail=%s and (dn_detail is null or dn_detail = '') and docstatus=1""", so_detail)
+ si = frappe.qb.DocType("Sales Invoice").as_("si")
+ si_item = frappe.qb.DocType("Sales Invoice Item").as_("si_item")
+ sum_amount = Sum(si_item.amount).as_("amount")
+
+ billed_against_so = frappe.qb.from_(si).from_(si_item).select(sum_amount).where(
+ (si_item.parent == si.name) &
+ (si_item.so_detail == so_detail) &
+ ((si_item.dn_detail.isnull()) | (si_item.dn_detail == '')) &
+ (si_item.docstatus == 1) &
+ (si.update_stock == 0)
+ ).run()
billed_against_so = billed_against_so and billed_against_so[0][0] or 0
# Get all Delivery Note Item rows against the Sales Order Item row
- dn_details = frappe.db.sql("""select dn_item.name, dn_item.amount, dn_item.si_detail, dn_item.parent
- from `tabDelivery Note Item` dn_item, `tabDelivery Note` dn
- where dn.name=dn_item.parent and dn_item.so_detail=%s
- and dn.docstatus=1 and dn.is_return = 0
- order by dn.posting_date asc, dn.posting_time asc, dn.name asc""", so_detail, as_dict=1)
+
+ dn = frappe.qb.DocType("Delivery Note").as_("dn")
+ dn_item = frappe.qb.DocType("Delivery Note Item").as_("dn_item")
+
+ dn_details = frappe.qb.from_(dn).from_(dn_item).select(dn_item.name, dn_item.amount, dn_item.si_detail, dn_item.parent, dn_item.stock_qty, dn_item.returned_qty).where(
+ (dn.name == dn_item.parent) &
+ (dn_item.so_detail == so_detail) &
+ (dn.docstatus == 1) &
+ (dn.is_return == 0)
+ ).orderby(
+ dn.posting_date, dn.posting_time, dn.name
+ ).run(as_dict=True)
updated_dn = []
for dnd in dn_details:
@@ -367,7 +385,11 @@
# Distribute billed amount directly against SO between DNs based on FIFO
if billed_against_so and billed_amt_agianst_dn < dnd.amount:
- pending_to_bill = flt(dnd.amount) - billed_amt_agianst_dn
+ if dnd.returned_qty:
+ pending_to_bill = flt(dnd.amount) * (dnd.stock_qty - dnd.returned_qty) / dnd.stock_qty
+ else:
+ pending_to_bill = flt(dnd.amount)
+ pending_to_bill -= billed_amt_agianst_dn
if pending_to_bill <= billed_against_so:
billed_amt_agianst_dn += pending_to_bill
billed_against_so -= pending_to_bill