diff --git a/.github/stale.yml b/.github/stale.yml
index fbf6447..cdce0ae 100644
--- a/.github/stale.yml
+++ b/.github/stale.yml
@@ -23,15 +23,3 @@
     activity occurs, but it only takes a comment to keep a contribution alive
     :) Also, even if it is closed, you can always reopen the PR when you're
     ready. Thank you for contributing.
-
-issues:
-  daysUntilStale: 90
-  daysUntilClose: 7
-  exemptLabels:
-    - valid
-    - to-validate
-    - QA
-  markComment: >
-    This issue has been automatically marked as inactive because it has not had
-    recent activity and it wasn't validated by maintainer team. It will be
-    closed within a week if no further activity occurs.
diff --git a/erpnext/hr/doctype/leave_application/leave_application.py b/erpnext/hr/doctype/leave_application/leave_application.py
index cd6b168..53c5df4 100755
--- a/erpnext/hr/doctype/leave_application/leave_application.py
+++ b/erpnext/hr/doctype/leave_application/leave_application.py
@@ -88,7 +88,7 @@
 		share_doc_with_approver(self, self.leave_approver)
 
 	def on_submit(self):
-		if self.status == "Open":
+		if self.status in ["Open", "Cancelled"]:
 			frappe.throw(
 				_("Only Leave Applications with status 'Approved' and 'Rejected' can be submitted")
 			)
@@ -1117,7 +1117,7 @@
 	WHERE
 		from_date <= %(end)s AND to_date >= %(start)s <= to_date
 		AND docstatus < 2
-		AND status != 'Rejected'
+		AND status in ('Approved', 'Open')
 	"""
 
 	if conditions:
@@ -1201,24 +1201,33 @@
 
 
 def get_approved_leaves_for_period(employee, leave_type, from_date, to_date):
-	query = """
-		select employee, leave_type, from_date, to_date, total_leave_days
-		from `tabLeave Application`
-		where employee=%(employee)s
-			and docstatus=1
-			and (from_date between %(from_date)s and %(to_date)s
-				or to_date between %(from_date)s and %(to_date)s
-				or (from_date < %(from_date)s and to_date > %(to_date)s))
-	"""
-	if leave_type:
-		query += "and leave_type=%(leave_type)s"
-
-	leave_applications = frappe.db.sql(
-		query,
-		{"from_date": from_date, "to_date": to_date, "employee": employee, "leave_type": leave_type},
-		as_dict=1,
+	LeaveApplication = frappe.qb.DocType("Leave Application")
+	query = (
+		frappe.qb.from_(LeaveApplication)
+		.select(
+			LeaveApplication.employee,
+			LeaveApplication.leave_type,
+			LeaveApplication.from_date,
+			LeaveApplication.to_date,
+			LeaveApplication.total_leave_days,
+		)
+		.where(
+			(LeaveApplication.employee == employee)
+			& (LeaveApplication.docstatus == 1)
+			& (LeaveApplication.status == "Approved")
+			& (
+				(LeaveApplication.from_date.between(from_date, to_date))
+				| (LeaveApplication.to_date.between(from_date, to_date))
+				| ((LeaveApplication.from_date < from_date) & (LeaveApplication.to_date > to_date))
+			)
+		)
 	)
 
+	if leave_type:
+		query = query.where(LeaveApplication.leave_type == leave_type)
+
+	leave_applications = query.run(as_dict=True)
+
 	leave_days = 0
 	for leave_app in leave_applications:
 		if leave_app.from_date >= getdate(from_date) and leave_app.to_date <= getdate(to_date):
diff --git a/erpnext/hr/doctype/leave_application/leave_application_list.js b/erpnext/hr/doctype/leave_application/leave_application_list.js
index a3c03b1..157271a 100644
--- a/erpnext/hr/doctype/leave_application/leave_application_list.js
+++ b/erpnext/hr/doctype/leave_application/leave_application_list.js
@@ -1,13 +1,14 @@
-frappe.listview_settings['Leave Application'] = {
+frappe.listview_settings["Leave Application"] = {
 	add_fields: ["leave_type", "employee", "employee_name", "total_leave_days", "from_date", "to_date"],
 	has_indicator_for_draft: 1,
 	get_indicator: function (doc) {
-		if (doc.status === "Approved") {
-			return [__("Approved"), "green", "status,=,Approved"];
-		} else if (doc.status === "Rejected") {
-			return [__("Rejected"), "red", "status,=,Rejected"];
-		} else {
-			return [__("Open"), "red", "status,=,Open"];
-		}
+		let status_color = {
+			"Approved": "green",
+			"Rejected": "red",
+			"Open": "orange",
+			"Cancelled": "red",
+			"Submitted": "blue"
+		};
+		return [__(doc.status), status_color[doc.status], "status,=," + doc.status];
 	}
 };
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.py b/erpnext/manufacturing/doctype/job_card/job_card.py
index 0a9fd8a..0199a5c 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/job_card.py
@@ -621,7 +621,7 @@
 		self.set_status(update_status)
 
 	def set_status(self, update_status=False):
-		if self.status == "On Hold":
+		if self.status == "On Hold" and self.docstatus == 0:
 			return
 
 		self.status = {0: "Open", 1: "Submitted", 2: "Cancelled"}[self.docstatus or 0]
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 8594ebb..5a98463 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -373,3 +373,4 @@
 erpnext.patches.v13_0.set_per_billed_in_return_delivery_note
 execute:frappe.delete_doc("DocType", "Naming Series")
 erpnext.patches.v13_0.set_payroll_entry_status
+erpnext.patches.v13_0.job_card_status_on_hold
diff --git a/erpnext/patches/v13_0/job_card_status_on_hold.py b/erpnext/patches/v13_0/job_card_status_on_hold.py
new file mode 100644
index 0000000..8c67c3c
--- /dev/null
+++ b/erpnext/patches/v13_0/job_card_status_on_hold.py
@@ -0,0 +1,19 @@
+import frappe
+
+
+def execute():
+	job_cards = frappe.get_all(
+		"Job Card",
+		{"status": "On Hold", "docstatus": ("!=", 0)},
+		pluck="name",
+	)
+
+	for idx, job_card in enumerate(job_cards):
+		try:
+			doc = frappe.get_doc("Job Card", job_card)
+			doc.set_status()
+			doc.db_set("status", doc.status, update_modified=False)
+			if idx % 100 == 0:
+				frappe.db.commit()
+		except Exception:
+			continue
diff --git a/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py b/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
index 0acd447..8df1bb6 100644
--- a/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
+++ b/erpnext/payroll/doctype/employee_benefit_application/employee_benefit_application.py
@@ -5,7 +5,7 @@
 import frappe
 from frappe import _
 from frappe.model.document import Document
-from frappe.utils import add_days, cint, cstr, date_diff, getdate, rounded
+from frappe.utils import add_days, cstr, date_diff, flt, getdate, rounded
 
 from erpnext.hr.utils import (
 	get_holiday_dates_for_employee,
@@ -27,11 +27,14 @@
 		validate_active_employee(self.employee)
 		self.validate_duplicate_on_payroll_period()
 		if not self.max_benefits:
-			self.max_benefits = get_max_benefits_remaining(self.employee, self.date, self.payroll_period)
+			self.max_benefits = flt(
+				get_max_benefits_remaining(self.employee, self.date, self.payroll_period),
+				self.precision("max_benefits"),
+			)
 		if self.max_benefits and self.max_benefits > 0:
 			self.validate_max_benefit_for_component()
 			self.validate_prev_benefit_claim()
-			if self.remaining_benefit > 0:
+			if self.remaining_benefit and self.remaining_benefit > 0:
 				self.validate_remaining_benefit_amount()
 		else:
 			frappe.throw(
@@ -110,7 +113,7 @@
 			max_benefit_amount = 0
 			for employee_benefit in self.employee_benefits:
 				self.validate_max_benefit(employee_benefit.earning_component)
-				max_benefit_amount += employee_benefit.amount
+				max_benefit_amount += flt(employee_benefit.amount)
 			if max_benefit_amount > self.max_benefits:
 				frappe.throw(
 					_("Maximum benefit amount of employee {0} exceeds {1}").format(
@@ -125,7 +128,8 @@
 		benefit_amount = 0
 		for employee_benefit in self.employee_benefits:
 			if employee_benefit.earning_component == earning_component_name:
-				benefit_amount += employee_benefit.amount
+				benefit_amount += flt(employee_benefit.amount)
+
 		prev_sal_slip_flexi_amount = get_sal_slip_total_benefit_given(
 			self.employee, frappe.get_doc("Payroll Period", self.payroll_period), earning_component_name
 		)
@@ -207,26 +211,47 @@
 def calculate_lwp(employee, start_date, holidays, working_days):
 	lwp = 0
 	holidays = "','".join(holidays)
+
 	for d in range(working_days):
-		dt = add_days(cstr(getdate(start_date)), d)
-		leave = frappe.db.sql(
-			"""
-			select t1.name, t1.half_day
-			from `tabLeave Application` t1, `tabLeave Type` t2
-			where t2.name = t1.leave_type
-			and t2.is_lwp = 1
-			and t1.docstatus = 1
-			and t1.employee = %(employee)s
-			and CASE WHEN t2.include_holiday != 1 THEN %(dt)s not in ('{0}') and %(dt)s between from_date and to_date
-			WHEN t2.include_holiday THEN %(dt)s between from_date and to_date
-			END
-			""".format(
-				holidays
-			),
-			{"employee": employee, "dt": dt},
+		date = add_days(cstr(getdate(start_date)), d)
+
+		LeaveApplication = frappe.qb.DocType("Leave Application")
+		LeaveType = frappe.qb.DocType("Leave Type")
+
+		is_half_day = (
+			frappe.qb.terms.Case()
+			.when(
+				(
+					(LeaveApplication.half_day_date == date)
+					| (LeaveApplication.from_date == LeaveApplication.to_date)
+				),
+				LeaveApplication.half_day,
+			)
+			.else_(0)
+		).as_("is_half_day")
+
+		query = (
+			frappe.qb.from_(LeaveApplication)
+			.inner_join(LeaveType)
+			.on((LeaveType.name == LeaveApplication.leave_type))
+			.select(LeaveApplication.name, is_half_day)
+			.where(
+				(LeaveType.is_lwp == 1)
+				& (LeaveApplication.docstatus == 1)
+				& (LeaveApplication.status == "Approved")
+				& (LeaveApplication.employee == employee)
+				& ((LeaveApplication.from_date <= date) & (date <= LeaveApplication.to_date))
+			)
 		)
-		if leave:
-			lwp = cint(leave[0][1]) and (lwp + 0.5) or (lwp + 1)
+
+		# if it's a holiday only include if leave type has "include holiday" enabled
+		if date in holidays:
+			query = query.where((LeaveType.include_holiday == "1"))
+		leaves = query.run(as_dict=True)
+
+		if leaves:
+			lwp += 0.5 if leaves[0].is_half_day else 1
+
 	return lwp
 
 
diff --git a/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py b/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
index 02149ad..de8f9b6 100644
--- a/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
+++ b/erpnext/payroll/doctype/employee_benefit_application/test_employee_benefit_application.py
@@ -3,6 +3,82 @@
 
 import unittest
 
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days, date_diff, get_year_ending, get_year_start, getdate
 
-class TestEmployeeBenefitApplication(unittest.TestCase):
-	pass
+from erpnext.hr.doctype.employee.test_employee import make_employee
+from erpnext.hr.doctype.holiday_list.test_holiday_list import set_holiday_list
+from erpnext.hr.doctype.leave_application.test_leave_application import get_first_sunday
+from erpnext.hr.utils import get_holiday_dates_for_employee
+from erpnext.payroll.doctype.employee_benefit_application.employee_benefit_application import (
+	calculate_lwp,
+)
+from erpnext.payroll.doctype.employee_tax_exemption_declaration.test_employee_tax_exemption_declaration import (
+	create_payroll_period,
+)
+from erpnext.payroll.doctype.salary_slip.test_salary_slip import (
+	make_holiday_list,
+	make_leave_application,
+)
+from erpnext.payroll.doctype.salary_structure.salary_structure import make_salary_slip
+from erpnext.payroll.doctype.salary_structure.test_salary_structure import make_salary_structure
+
+
+class TestEmployeeBenefitApplication(FrappeTestCase):
+	def setUp(self):
+		date = getdate()
+		make_holiday_list(from_date=get_year_start(date), to_date=get_year_ending(date))
+
+	@set_holiday_list("Salary Slip Test Holiday List", "_Test Company")
+	def test_employee_benefit_application(self):
+		payroll_period = create_payroll_period(name="_Test Payroll Period 1", company="_Test Company")
+		employee = make_employee("test_employee_benefits@salary.com", company="_Test Company")
+		first_sunday = get_first_sunday("Salary Slip Test Holiday List")
+
+		leave_application = make_leave_application(
+			employee,
+			add_days(first_sunday, 1),
+			add_days(first_sunday, 3),
+			"Leave Without Pay",
+			half_day=1,
+			half_day_date=add_days(first_sunday, 1),
+			submit=True,
+		)
+
+		frappe.db.set_value("Leave Type", "Leave Without Pay", "include_holiday", 0)
+		salary_structure = make_salary_structure(
+			"Test Employee Benefits",
+			"Monthly",
+			other_details={"max_benefits": 100000},
+			include_flexi_benefits=True,
+			employee=employee,
+			payroll_period=payroll_period,
+		)
+		salary_slip = make_salary_slip(salary_structure.name, employee=employee, posting_date=getdate())
+		salary_slip.insert()
+		salary_slip.submit()
+
+		application = make_employee_benefit_application(
+			employee, payroll_period.name, date=leave_application.to_date
+		)
+		self.assertEqual(application.employee_benefits[0].max_benefit_amount, 15000)
+
+		holidays = get_holiday_dates_for_employee(employee, payroll_period.start_date, application.date)
+		working_days = date_diff(application.date, payroll_period.start_date) + 1
+		lwp = calculate_lwp(employee, payroll_period.start_date, holidays, working_days)
+		self.assertEqual(lwp, 2.5)
+
+
+def make_employee_benefit_application(employee, payroll_period, date):
+	frappe.db.delete("Employee Benefit Application")
+
+	return frappe.get_doc(
+		{
+			"doctype": "Employee Benefit Application",
+			"employee": employee,
+			"date": date,
+			"payroll_period": payroll_period,
+			"employee_benefits": [{"earning_component": "Medical Allowance", "amount": 1500}],
+		}
+	).insert()
diff --git a/erpnext/payroll/doctype/salary_slip/salary_slip.py b/erpnext/payroll/doctype/salary_slip/salary_slip.py
index 4c5fea1..6a35985 100644
--- a/erpnext/payroll/doctype/salary_slip/salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/salary_slip.py
@@ -465,37 +465,14 @@
 		)
 
 		for d in range(working_days):
-			dt = add_days(cstr(getdate(self.start_date)), d)
-			leave = frappe.db.sql(
-				"""
-				SELECT t1.name,
-					CASE WHEN (t1.half_day_date = %(dt)s or t1.to_date = t1.from_date)
-					THEN t1.half_day else 0 END,
-					t2.is_ppl,
-					t2.fraction_of_daily_salary_per_leave
-				FROM `tabLeave Application` t1, `tabLeave Type` t2
-				WHERE t2.name = t1.leave_type
-				AND (t2.is_lwp = 1 or t2.is_ppl = 1)
-				AND t1.docstatus = 1
-				AND t1.employee = %(employee)s
-				AND ifnull(t1.salary_slip, '') = ''
-				AND CASE
-					WHEN t2.include_holiday != 1
-						THEN %(dt)s not in ('{0}') and %(dt)s between from_date and to_date
-					WHEN t2.include_holiday
-						THEN %(dt)s between from_date and to_date
-					END
-				""".format(
-					holidays
-				),
-				{"employee": self.employee, "dt": dt},
-			)
+			date = add_days(cstr(getdate(self.start_date)), d)
+			leave = get_lwp_or_ppl_for_date(date, self.employee, holidays)
 
 			if leave:
 				equivalent_lwp_count = 0
-				is_half_day_leave = cint(leave[0][1])
-				is_partially_paid_leave = cint(leave[0][2])
-				fraction_of_daily_salary_per_leave = flt(leave[0][3])
+				is_half_day_leave = cint(leave[0].is_half_day)
+				is_partially_paid_leave = cint(leave[0].is_ppl)
+				fraction_of_daily_salary_per_leave = flt(leave[0].fraction_of_daily_salary_per_leave)
 
 				equivalent_lwp_count = (1 - daily_wages_fraction_for_half_day) if is_half_day_leave else 1
 
@@ -1742,3 +1719,46 @@
 	except Exception as e:
 		frappe.throw(_("Error in formula or condition: {0} in Income Tax Slab").format(e))
 		raise
+
+
+def get_lwp_or_ppl_for_date(date, employee, holidays):
+	LeaveApplication = frappe.qb.DocType("Leave Application")
+	LeaveType = frappe.qb.DocType("Leave Type")
+
+	is_half_day = (
+		frappe.qb.terms.Case()
+		.when(
+			(
+				(LeaveApplication.half_day_date == date)
+				| (LeaveApplication.from_date == LeaveApplication.to_date)
+			),
+			LeaveApplication.half_day,
+		)
+		.else_(0)
+	).as_("is_half_day")
+
+	query = (
+		frappe.qb.from_(LeaveApplication)
+		.inner_join(LeaveType)
+		.on((LeaveType.name == LeaveApplication.leave_type))
+		.select(
+			LeaveApplication.name,
+			LeaveType.is_ppl,
+			LeaveType.fraction_of_daily_salary_per_leave,
+			(is_half_day),
+		)
+		.where(
+			(((LeaveType.is_lwp == 1) | (LeaveType.is_ppl == 1)))
+			& (LeaveApplication.docstatus == 1)
+			& (LeaveApplication.status == "Approved")
+			& (LeaveApplication.employee == employee)
+			& ((LeaveApplication.salary_slip.isnull()) | (LeaveApplication.salary_slip == ""))
+			& ((LeaveApplication.from_date <= date) & (date <= LeaveApplication.to_date))
+		)
+	)
+
+	# if it's a holiday only include if leave type has "include holiday" enabled
+	if date in holidays:
+		query = query.where((LeaveType.include_holiday == "1"))
+
+	return query.run(as_dict=True)
diff --git a/erpnext/payroll/doctype/salary_slip/test_salary_slip.py b/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
index 5e3814b..a8b6bb5 100644
--- a/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
+++ b/erpnext/payroll/doctype/salary_slip/test_salary_slip.py
@@ -49,7 +49,7 @@
 		"Payroll Settings", {"payroll_based_on": "Attendance", "daily_wages_fraction_for_half_day": 0.75}
 	)
 	def test_payment_days_based_on_attendance(self):
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 
 		emp_id = make_employee("test_payment_days_based_on_attendance@salary.com")
 		frappe.db.set_value("Employee", emp_id, {"relieving_date": None, "status": "Active"})
@@ -128,7 +128,7 @@
 		},
 	)
 	def test_payment_days_for_mid_joinee_including_holidays(self):
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
 
 		new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -196,7 +196,7 @@
 		# tests mid month joining and relieving along with unmarked days
 		from erpnext.hr.doctype.holiday_list.holiday_list import is_holiday
 
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
 
 		new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -236,7 +236,7 @@
 	def test_payment_days_for_mid_joinee_excluding_holidays(self):
 		from erpnext.hr.doctype.holiday_list.holiday_list import is_holiday
 
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		month_start_date, month_end_date = get_first_day(nowdate()), get_last_day(nowdate())
 
 		new_emp_id = make_employee("test_payment_days_based_on_joining_date@salary.com")
@@ -267,7 +267,7 @@
 
 	@change_settings("Payroll Settings", {"payroll_based_on": "Leave"})
 	def test_payment_days_based_on_leave_application(self):
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 
 		emp_id = make_employee("test_payment_days_based_on_leave_application@salary.com")
 		frappe.db.set_value("Employee", emp_id, {"relieving_date": None, "status": "Active"})
@@ -366,7 +366,7 @@
 		salary_slip.submit()
 		salary_slip.reload()
 
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		days_in_month = no_of_days[0]
 		no_of_holidays = no_of_days[1]
 
@@ -441,7 +441,7 @@
 
 	@change_settings("Payroll Settings", {"include_holidays_in_total_working_days": 1})
 	def test_salary_slip_with_holidays_included(self):
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		make_employee("test_salary_slip_with_holidays_included@salary.com")
 		frappe.db.set_value(
 			"Employee",
@@ -473,7 +473,7 @@
 
 	@change_settings("Payroll Settings", {"include_holidays_in_total_working_days": 0})
 	def test_salary_slip_with_holidays_excluded(self):
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 		make_employee("test_salary_slip_with_holidays_excluded@salary.com")
 		frappe.db.set_value(
 			"Employee",
@@ -510,7 +510,7 @@
 			create_salary_structure_assignment,
 		)
 
-		no_of_days = self.get_no_of_days()
+		no_of_days = get_no_of_days()
 
 		# set joinng date in the same month
 		employee = make_employee("test_payment_days@salary.com")
@@ -984,17 +984,18 @@
 		activity_type.wage_rate = 25
 		activity_type.save()
 
-	def get_no_of_days(self):
-		no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month)
-		no_of_holidays_in_month = len(
-			[
-				1
-				for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month)
-				if i[6] != 0
-			]
-		)
 
-		return [no_of_days_in_month[1], no_of_holidays_in_month]
+def get_no_of_days():
+	no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month)
+	no_of_holidays_in_month = len(
+		[
+			1
+			for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month)
+			if i[6] != 0
+		]
+	)
+
+	return [no_of_days_in_month[1], no_of_holidays_in_month]
 
 
 def make_employee_salary_slip(user, payroll_frequency, salary_structure=None, posting_date=None):
@@ -1136,6 +1137,7 @@
 					"pay_against_benefit_claim": 0,
 					"type": "Earning",
 					"max_benefit_amount": 15000,
+					"depends_on_payment_days": 1,
 				},
 			]
 		)
diff --git a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
index dcfb10a..cc61594 100644
--- a/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/sales_order_analysis.py
@@ -1,11 +1,13 @@
 # Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
 # For license information, please see license.txt
 
-
 import copy
+from collections import OrderedDict
 
 import frappe
-from frappe import _
+from frappe import _, qb
+from frappe.query_builder import CustomFunction
+from frappe.query_builder.functions import Max
 from frappe.utils import date_diff, flt, getdate
 
 
@@ -18,11 +20,12 @@
 	columns = get_columns(filters)
 	conditions = get_conditions(filters)
 	data = get_data(conditions, filters)
+	so_elapsed_time = get_so_elapsed_time(data)
 
 	if not data:
 		return [], [], None, []
 
-	data, chart_data = prepare_data(data, filters)
+	data, chart_data = prepare_data(data, so_elapsed_time, filters)
 
 	return columns, data, None, chart_data
 
@@ -65,7 +68,6 @@
 			IF(so.status in ('Completed','To Bill'), 0, (SELECT delay_days)) as delay,
 			soi.qty, soi.delivered_qty,
 			(soi.qty - soi.delivered_qty) AS pending_qty,
-			IF((SELECT pending_qty) = 0, (TO_SECONDS(Max(dn.posting_date))-TO_SECONDS(so.transaction_date)), 0) as time_taken_to_deliver,
 			IFNULL(SUM(sii.qty), 0) as billed_qty,
 			soi.base_amount as amount,
 			(soi.delivered_qty * soi.base_rate) as delivered_qty_amount,
@@ -76,13 +78,9 @@
 			soi.description as description
 		FROM
 			`tabSales Order` so,
-			(`tabSales Order Item` soi
+			`tabSales Order Item` soi
 		LEFT JOIN `tabSales Invoice Item` sii
-			ON sii.so_detail = soi.name and sii.docstatus = 1)
-		LEFT JOIN `tabDelivery Note Item` dni
-			on dni.so_detail = soi.name
-		LEFT JOIN `tabDelivery Note` dn
-			on dni.parent = dn.name and dn.docstatus = 1
+			ON sii.so_detail = soi.name and sii.docstatus = 1
 		WHERE
 			soi.parent = so.name
 			and so.status not in ('Stopped', 'Closed', 'On Hold')
@@ -100,7 +98,48 @@
 	return data
 
 
-def prepare_data(data, filters):
+def get_so_elapsed_time(data):
+	"""
+	query SO's elapsed time till latest delivery note
+	"""
+	so_elapsed_time = OrderedDict()
+	if data:
+		sales_orders = [x.sales_order for x in data]
+
+		so = qb.DocType("Sales Order")
+		soi = qb.DocType("Sales Order Item")
+		dn = qb.DocType("Delivery Note")
+		dni = qb.DocType("Delivery Note Item")
+
+		to_seconds = CustomFunction("TO_SECONDS", ["date"])
+
+		query = (
+			qb.from_(so)
+			.inner_join(soi)
+			.on(soi.parent == so.name)
+			.left_join(dni)
+			.on(dni.so_detail == soi.name)
+			.left_join(dn)
+			.on(dni.parent == dn.name)
+			.select(
+				so.name.as_("sales_order"),
+				soi.item_code.as_("so_item_code"),
+				(to_seconds(Max(dn.posting_date)) - to_seconds(so.transaction_date)).as_("elapsed_seconds"),
+			)
+			.where((so.name.isin(sales_orders)) & (dn.docstatus == 1))
+			.orderby(so.name, soi.name)
+			.groupby(soi.name)
+		)
+		dn_elapsed_time = query.run(as_dict=True)
+
+		for e in dn_elapsed_time:
+			key = (e.sales_order, e.so_item_code)
+			so_elapsed_time[key] = e.elapsed_seconds
+
+	return so_elapsed_time
+
+
+def prepare_data(data, so_elapsed_time, filters):
 	completed, pending = 0, 0
 
 	if filters.get("group_by_so"):
@@ -115,6 +154,13 @@
 		row["qty_to_bill"] = flt(row["qty"]) - flt(row["billed_qty"])
 
 		row["delay"] = 0 if row["delay"] and row["delay"] < 0 else row["delay"]
+
+		row["time_taken_to_deliver"] = (
+			so_elapsed_time.get((row.sales_order, row.item_code))
+			if row["status"] in ("To Bill", "Completed")
+			else 0
+		)
+
 		if filters.get("group_by_so"):
 			so_name = row["sales_order"]
 
diff --git a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
index 25cbb73..241f435 100644
--- a/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
+++ b/erpnext/selling/report/sales_order_analysis/test_sales_order_analysis.py
@@ -11,7 +11,7 @@
 
 
 class TestSalesOrderAnalysis(FrappeTestCase):
-	def create_sales_order(self, transaction_date):
+	def create_sales_order(self, transaction_date, do_not_save=False, do_not_submit=False):
 		item = create_item(item_code="_Test Excavator", is_stock_item=0)
 		so = make_sales_order(
 			transaction_date=transaction_date,
@@ -24,25 +24,31 @@
 		so.taxes_and_charges = ""
 		so.taxes = ""
 		so.items[0].delivery_date = add_days(transaction_date, 15)
-		so.save()
-		so.submit()
+		if not do_not_save:
+			so.save()
+			if not do_not_submit:
+				so.submit()
 		return item, so
 
-	def create_sales_invoice(self, so):
+	def create_sales_invoice(self, so, do_not_save=False, do_not_submit=False):
 		sinv = make_sales_invoice(so.name)
 		sinv.posting_date = so.transaction_date
 		sinv.taxes_and_charges = ""
 		sinv.taxes = ""
-		sinv.insert()
-		sinv.submit()
+		if not do_not_save:
+			sinv.save()
+			if not do_not_submit:
+				sinv.submit()
 		return sinv
 
-	def create_delivery_note(self, so):
+	def create_delivery_note(self, so, do_not_save=False, do_not_submit=False):
 		dn = make_delivery_note(so.name)
 		dn.set_posting_time = True
 		dn.posting_date = add_days(so.transaction_date, 1)
-		dn.save()
-		dn.submit()
+		if not do_not_save:
+			dn.save()
+			if not do_not_submit:
+				dn.submit()
 		return dn
 
 	def test_01_so_to_deliver_and_bill(self):
@@ -164,3 +170,85 @@
 		)
 		# SO's from first 4 test cases should be in output
 		self.assertEqual(len(data), 4)
+
+	def test_06_so_pending_delivery_with_multiple_delivery_notes(self):
+		transaction_date = "2021-06-01"
+		item, so = self.create_sales_order(transaction_date)
+
+		# bill 2 items
+		sinv1 = self.create_sales_invoice(so, do_not_save=True)
+		sinv1.items[0].qty = 2
+		sinv1 = sinv1.save().submit()
+		# deliver 2 items
+		dn1 = self.create_delivery_note(so, do_not_save=True)
+		dn1.items[0].qty = 2
+		dn1 = dn1.save().submit()
+
+		# bill 2 items
+		sinv2 = self.create_sales_invoice(so, do_not_save=True)
+		sinv2.items[0].qty = 2
+		sinv2 = sinv2.save().submit()
+		# deliver 1 item
+		dn2 = self.create_delivery_note(so, do_not_save=True)
+		dn2.items[0].qty = 1
+		dn2 = dn2.save().submit()
+
+		columns, data, message, chart = execute(
+			{
+				"company": "_Test Company",
+				"from_date": "2021-06-01",
+				"to_date": "2021-06-30",
+				"sales_order": [so.name],
+			}
+		)
+		expected_value = {
+			"status": "To Deliver and Bill",
+			"sales_order": so.name,
+			"delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+			"qty": 10,
+			"delivered_qty": 3,
+			"pending_qty": 7,
+			"qty_to_bill": 6,
+			"billed_qty": 4,
+			"time_taken_to_deliver": 0,
+		}
+		self.assertEqual(len(data), 1)
+		for key, val in expected_value.items():
+			with self.subTest(key=key, val=val):
+				self.assertEqual(data[0][key], val)
+
+	def test_07_so_delivered_with_multiple_delivery_notes(self):
+		transaction_date = "2021-06-01"
+		item, so = self.create_sales_order(transaction_date)
+
+		dn1 = self.create_delivery_note(so, do_not_save=True)
+		dn1.items[0].qty = 5
+		dn1 = dn1.save().submit()
+
+		dn2 = self.create_delivery_note(so, do_not_save=True)
+		dn2.items[0].qty = 5
+		dn2 = dn2.save().submit()
+
+		columns, data, message, chart = execute(
+			{
+				"company": "_Test Company",
+				"from_date": "2021-06-01",
+				"to_date": "2021-06-30",
+				"sales_order": [so.name],
+			}
+		)
+		expected_value = {
+			"status": "To Bill",
+			"sales_order": so.name,
+			"delay_days": frappe.utils.date_diff(frappe.utils.datetime.date.today(), so.delivery_date),
+			"qty": 10,
+			"delivered_qty": 10,
+			"pending_qty": 0,
+			"qty_to_bill": 10,
+			"billed_qty": 0,
+			"time_taken_to_deliver": 86400,
+		}
+		self.assertEqual(len(data), 1)
+		for key, val in expected_value.items():
+			with self.subTest(key=key, val=val):
+				self.assertEqual(data[0][key], val)