Repository: frappe/erpnext
Pull request: feat: Payment Terms Status report
## Payment Terms Status Report
This report aims to provide current status of payment terms based on the associated invoices against the SO.
All invoices associated with Sales Order are fetched and assigned to terms in FIFO method. Statuses shown in the report are calculated at runtime and does not affect the state in the database.
<img width="1552" alt="payment terms status" src="https://user-images.githubusercontent.com/3272205/148046071-e75363c6-8541-4144-a15e-ec63268dc7b2.png">

When Individual SO's are selected, a chart is used to show the payment and paid amount for each terms.
<img width="1552" alt="charts for Payment terms" src="https://user-images.githubusercontent.com/3272205/148046791-8a3d6e39-ebbc-498b-ac1a-a9f1f7b1f567.png">

Documentation can be found [**here**]( https://docs.erpnext.com/docs/v13/user/manual/en/accounts/payment_terms_status_report )

Problem statement:
Generate Invoice based on Payment Terms
Here are the points to be considered or challenges when creating a payment against a Payment Term:
**Challenge:** 
Since the Amount in the Sales Invoice is determined based on Rate and Qty, in order to do 50% billing for an invoice (for eg.) we will have to set either qty to 50% of rate to 50%. Resetting Qty may not be ideal, as it can lead to an issue if “Update Stock” is also checked. We cannot deliver 0.50 TV. Even now, we change the item’s Qty to get the pending invoice amount in the invoice.

**Payment Terms:**
If an invoice is made against a Payment Term, then there should be no other Payment Term fetched from Sales Order. Currently, we fetch all the payment terms of the sales order into the invoice.

**Benefits**
This design will help us extra a reports like:
**Sales Order pending to be billed for a specific term**
**Sales Order outstanding as per the payment terms**

Hints from issue discussion:

Commit message: test: qty and rate changed to remove need for fractional Nos

Patch to review:
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/__init__.py b/erpnext/selling/report/payment_terms_status_for_sales_order/__init__.py
new file mode 100644
index 000000000000..e69de29bb2d1
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js
new file mode 100644
index 000000000000..0450631a3be4
--- /dev/null
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.js
@@ -0,0 +1,84 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+function get_filters() {
+	let filters = [
+		{
+			"fieldname":"company",
+			"label": __("Company"),
+			"fieldtype": "Link",
+			"options": "Company",
+			"default": frappe.defaults.get_user_default("Company"),
+			"reqd": 1
+		},
+		{
+			"fieldname":"period_start_date",
+			"label": __("Start Date"),
+			"fieldtype": "Date",
+			"reqd": 1,
+			"default": frappe.datetime.add_months(frappe.datetime.get_today(), -1)
+		},
+		{
+			"fieldname":"period_end_date",
+			"label": __("End Date"),
+			"fieldtype": "Date",
+			"reqd": 1,
+			"default": frappe.datetime.get_today()
+		},
+		{
+			"fieldname":"sales_order",
+			"label": __("Sales Order"),
+			"fieldtype": "MultiSelectList",
+			"width": 100,
+			"options": "Sales Order",
+			"get_data": function(txt) {
+				return frappe.db.get_link_options("Sales Order", txt, this.filters());
+			},
+			"filters": () => {
+				return {
+					docstatus: 1,
+					payment_terms_template: ['not in', ['']],
+					company: frappe.query_report.get_filter_value("company"),
+					transaction_date: ['between', [frappe.query_report.get_filter_value("period_start_date"), frappe.query_report.get_filter_value("period_end_date")]]
+				}
+			},
+			on_change: function(){
+				frappe.query_report.refresh();
+			}
+		}
+	]
+
+	return filters;
+}
+
+frappe.query_reports["Payment Terms Status for Sales Order"] = {
+	"filters": get_filters(),
+	"formatter": function(value, row, column, data, default_formatter){
+		if(column.fieldname == 'invoices' && value) {
+			invoices = value.split(',');
+			const invoice_formatter = (prev_value, curr_value) => {
+				if(prev_value != "") {
+					return prev_value + ", " + default_formatter(curr_value, row, column, data);
+				}
+				else {
+					return default_formatter(curr_value, row, column, data);
+				}
+			}
+			return invoices.reduce(invoice_formatter, "")
+		}
+		else if (column.fieldname == 'paid_amount' && value){
+			formatted_value = default_formatter(value, row, column, data);
+			if(value > 0) {
+				formatted_value = "<span style='color:green;'>" + formatted_value + "</span>"
+			}
+			return formatted_value;
+		}
+		else if (column.fieldname == 'status' && value == 'Completed'){
+			return "<span style='color:green;'>" + default_formatter(value, row, column, data) + "</span>";
+		}
+
+		return default_formatter(value, row, column, data);
+	},
+
+};
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.json b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.json
new file mode 100644
index 000000000000..850fa4dc47a4
--- /dev/null
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.json
@@ -0,0 +1,38 @@
+{
+ "add_total_row": 1,
+ "columns": [],
+ "creation": "2021-12-28 10:39:34.533964",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-12-30 10:42:06.058457",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Payment Terms Status for Sales Order",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Payment Terms Status for Sales Order",
+ "report_type": "Script Report",
+ "roles": [
+  {
+   "role": "Sales User"
+  },
+  {
+   "role": "Sales Manager"
+  },
+  {
+   "role": "Maintenance User"
+  },
+  {
+   "role": "Accounts User"
+  },
+  {
+   "role": "Stock User"
+  }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
new file mode 100644
index 000000000000..aa2f757218e6
--- /dev/null
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
@@ -0,0 +1,211 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# License: MIT. See LICENSE
+
+import frappe
+from frappe import _, qb, query_builder
+from frappe.query_builder import functions
+
+
+def get_columns():
+	columns = [
+		{
+			"label": _("Sales Order"),
+			"fieldname": "name",
+			"fieldtype": "Link",
+			"options": "Sales Order",
+			"read_only": 1,
+		},
+		{
+			"label": _("Submitted"),
+			"fieldname": "submitted",
+			"fieldtype": "Date",
+			"read_only": 1
+		},
+		{
+			"label": _("Payment Term"),
+			"fieldname": "payment_term",
+			"fieldtype": "Data",
+			"read_only": 1
+		},
+		{
+			"label": _("Description"),
+			"fieldname": "description",
+			"fieldtype": "Data",
+			"read_only": 1
+		},
+		{
+			"label": _("Due Date"),
+			"fieldname": "due_date",
+			"fieldtype": "Date",
+			"read_only": 1
+		},
+		{
+			"label": _("Invoice Portion"),
+			"fieldname": "invoice_portion",
+			"fieldtype": "Percent",
+			"read_only": 1,
+		},
+		{
+			"label": _("Payment Amount"),
+			"fieldname": "payment_amount",
+			"fieldtype": "Currency",
+			"read_only": 1,
+		},
+		{
+			"label": _("Paid Amount"),
+			"fieldname": "paid_amount",
+			"fieldtype": "Currency",
+			"read_only": 1
+		},
+		{
+			"label": _("Invoices"),
+			"fieldname": "invoices",
+			"fieldtype": "Link",
+			"options": "Sales Invoice",
+			"read_only": 1,
+		},
+		{
+			"label": _("Status"),
+			"fieldname": "status",
+			"fieldtype": "Data",
+			"read_only": 1
+		}
+	]
+	return columns
+
+
+def get_conditions(filters):
+	"""
+	Convert filter options to conditions used in query
+	"""
+	filters = frappe._dict(filters) if filters else frappe._dict({})
+	conditions = frappe._dict({})
+
+	conditions.company = filters.company or frappe.defaults.get_user_default("company")
+	conditions.end_date = filters.period_end_date or frappe.utils.today()
+	conditions.start_date = filters.period_start_date or frappe.utils.add_months(
+		conditions.end_date, -1
+	)
+	conditions.sales_order = filters.sales_order or []
+
+	return conditions
+
+
+def get_so_with_invoices(filters):
+	"""
+	Get Sales Order with payment terms template with their associated Invoices
+	"""
+	sorders = []
+
+	so = qb.DocType("Sales Order")
+	ps = qb.DocType("Payment Schedule")
+	datediff = query_builder.CustomFunction("DATEDIFF", ["cur_date", "due_date"])
+	ifelse = query_builder.CustomFunction("IF", ["condition", "then", "else"])
+
+	conditions = get_conditions(filters)
+	query_so = (
+		qb.from_(so)
+		.join(ps)
+		.on(ps.parent == so.name)
+		.select(
+			so.name,
+			so.transaction_date.as_("submitted"),
+			ifelse(datediff(ps.due_date, functions.CurDate()) < 0, "Overdue", "Unpaid").as_("status"),
+			ps.payment_term,
+			ps.description,
+			ps.due_date,
+			ps.invoice_portion,
+			ps.payment_amount,
+			ps.paid_amount,
+		)
+		.where(
+			(so.docstatus == 1)
+			& (so.payment_terms_template != "NULL")
+			& (so.company == conditions.company)
+			& (so.transaction_date[conditions.start_date : conditions.end_date])
+		)
+		.orderby(so.name, so.transaction_date, ps.due_date)
+	)
+
+	if conditions.sales_order != []:
+		query_so = query_so.where(so.name.isin(conditions.sales_order))
+
+	sorders = query_so.run(as_dict=True)
+
+	invoices = []
+	if sorders != []:
+		soi = qb.DocType("Sales Order Item")
+		si = qb.DocType("Sales Invoice")
+		sii = qb.DocType("Sales Invoice Item")
+		query_inv = (
+			qb.from_(sii)
+			.right_join(si)
+			.on(si.name == sii.parent)
+			.inner_join(soi)
+			.on(soi.name == sii.so_detail)
+			.select(sii.sales_order, sii.parent.as_("invoice"), si.base_net_total.as_("invoice_amount"))
+			.where((sii.sales_order.isin([x.name for x in sorders])) & (si.docstatus == 1))
+			.groupby(sii.parent)
+		)
+		invoices = query_inv.run(as_dict=True)
+
+	return sorders, invoices
+
+
+def set_payment_terms_statuses(sales_orders, invoices):
+	"""
+	compute status for payment terms with associated sales invoice using FIFO
+	"""
+
+	for so in sales_orders:
+		for inv in [x for x in invoices if x.sales_order == so.name and x.invoice_amount > 0]:
+			if so.payment_amount - so.paid_amount > 0:
+				amount = so.payment_amount - so.paid_amount
+				if inv.invoice_amount >= amount:
+					inv.invoice_amount -= amount
+					so.paid_amount += amount
+					if so.invoices:
+						so.invoices = so.invoices + "," + inv.invoice
+					else:
+						so.invoices = inv.invoice
+					so.status = "Completed"
+					break
+				else:
+					so.paid_amount += inv.invoice_amount
+					inv.invoice_amount = 0
+					if so.invoices:
+						so.invoices = so.invoices + "," + inv.invoice
+					else:
+						so.invoices = inv.invoice
+					so.status = "Partly Paid"
+
+	return sales_orders, invoices
+
+
+def prepare_chart(s_orders):
+	if len(set([x.name for x in s_orders])) == 1:
+		chart = {
+			"data": {
+				"labels": [term.payment_term for term in s_orders],
+				"datasets": [
+					{"name": "Payment Amount", "values": [x.payment_amount for x in s_orders],},
+					{"name": "Paid Amount", "values": [x.paid_amount for x in s_orders],},
+				],
+			},
+			"type": "bar",
+		}
+		return chart
+
+
+def execute(filters=None):
+	columns = get_columns()
+	sales_orders, so_invoices = get_so_with_invoices(filters)
+	sales_orders, so_invoices = set_payment_terms_statuses(sales_orders, so_invoices)
+
+	prepare_chart(sales_orders)
+
+	data = sales_orders
+	message = []
+	chart = prepare_chart(sales_orders)
+
+	return columns, data, message, chart
diff --git a/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py b/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py
new file mode 100644
index 000000000000..5d6e91e8a50c
--- /dev/null
+++ b/erpnext/selling/report/payment_terms_status_for_sales_order/test_payment_terms_status_for_sales_order.py
@@ -0,0 +1,107 @@
+import datetime
+
+import frappe
+from frappe.utils import add_days
+
+from erpnext.selling.doctype.sales_order.sales_order import make_sales_invoice
+from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+from erpnext.selling.report.payment_terms_status_for_sales_order.payment_terms_status_for_sales_order import (
+	execute,
+)
+from erpnext.stock.doctype.item.test_item import create_item
+from erpnext.tests.utils import ERPNextTestCase
+
+test_dependencies = ["Sales Order", "Item", "Sales Invoice", "Payment Terms Template"]
+
+
+class TestPaymentTermsStatusForSalesOrder(ERPNextTestCase):
+	def test_payment_terms_status(self):
+
+		template = None
+		if frappe.db.exists("Payment Terms Template", "_Test 50-50"):
+			template = frappe.get_doc("Payment Terms Template", "_Test 50-50")
+		else:
+			template = frappe.get_doc(
+				{
+					"doctype": "Payment Terms Template",
+					"template_name": "_Test 50-50",
+					"terms": [
+						{
+							"doctype": "Payment Terms Template Detail",
+							"due_date_based_on": "Day(s) after invoice date",
+							"payment_term_name": "_Test 50% on 15 Days",
+							"description": "_Test 50-50",
+							"invoice_portion": 50,
+							"credit_days": 15,
+						},
+						{
+							"doctype": "Payment Terms Template Detail",
+							"due_date_based_on": "Day(s) after invoice date",
+							"payment_term_name": "_Test 50% on 30 Days",
+							"description": "_Test 50-50",
+							"invoice_portion": 50,
+							"credit_days": 30,
+						},
+					],
+				}
+			)
+			template.insert()
+
+		# item = create_item(item_code="_Test Excavator", is_stock_item=0, valuation_rate=1000000)
+		item = create_item(item_code="_Test Excavator", is_stock_item=0)
+		so = make_sales_order(
+			transaction_date="2021-06-15",
+			delivery_date=add_days("2021-06-15", -30),
+			item=item.item_code,
+			qty=10,
+			rate=100000,
+			do_not_save=True,
+		)
+		so.po_no = ""
+		so.payment_terms_template = template.name
+		so.save()
+		so.submit()
+
+		# make invoice with 60% of the total sales order value
+		sinv = make_sales_invoice(so.name)
+		sinv.items[0].qty = 6
+		sinv.insert()
+		sinv.submit()
+
+		columns, data, message, chart = execute(
+			{
+				"company": "_Test Company",
+				"period_start_date": "2021-06-01",
+				"period_end_date": "2021-06-30",
+				"sales_order": [so.name],
+			}
+		)
+
+		expected_value = [
+			{
+				"name": so.name,
+				"submitted": datetime.date(2021, 6, 15),
+				"status": "Completed",
+				"payment_term": None,
+				"description": "_Test 50-50",
+				"due_date": datetime.date(2021, 6, 30),
+				"invoice_portion": 50.0,
+				"payment_amount": 500000.0,
+				"paid_amount": 500000.0,
+				"invoices": sinv.name,
+			},
+			{
+				"name": so.name,
+				"submitted": datetime.date(2021, 6, 15),
+				"status": "Partly Paid",
+				"payment_term": None,
+				"description": "_Test 50-50",
+				"due_date": datetime.date(2021, 7, 15),
+				"invoice_portion": 50.0,
+				"payment_amount": 500000.0,
+				"paid_amount": 100000.0,
+				"invoices": sinv.name,
+			},
+		]
+
+		self.assertEqual(data, expected_value)


Code review comments:
diff_hunk: @@ -0,0 +1,211 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# License: MIT. See LICENSE
+
+import frappe
+from frappe import _, qb, query_builder
+from frappe.query_builder import functions
+
+
+def get_columns():
+	columns = [
+		{
+			"label": _("Sales Order"),
+			"fieldname": "name",
+			"fieldtype": "Link",
+			"options": "Sales Order",
+			"read_only": 1,
+		},
+		{
+			"label": _("Submitted"),
path: erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
text: @user1:
```suggestion
			"label": _("Posting Date"),
```

diff_hunk: @@ -0,0 +1,211 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# License: MIT. See LICENSE
+
+import frappe
+from frappe import _, qb, query_builder
+from frappe.query_builder import functions
+
+
+def get_columns():
+	columns = [
+		{
+			"label": _("Sales Order"),
+			"fieldname": "name",
+			"fieldtype": "Link",
+			"options": "Sales Order",
+			"read_only": 1,
path: erpnext/selling/report/payment_terms_status_for_sales_order/payment_terms_status_for_sales_order.py
text: @user1:
Is read-only applicable here? I mean here columns are by default read-only or some new feature has been introduced recently

@author:
removed.