Merge pull request #36879 from ruthra-kumar/unreconcile_feature
feat: Unreconcile Payments
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.js b/erpnext/accounts/doctype/journal_entry/journal_entry.js
index 35a3788..cdd1203 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.js
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.js
@@ -50,6 +50,8 @@
frm.trigger("make_inter_company_journal_entry");
}, __('Make'));
}
+
+ erpnext.accounts.unreconcile_payments.add_unreconcile_btn(frm);
},
make_inter_company_journal_entry: function(frm) {
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 9a0adf5..794a4ef 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -9,7 +9,7 @@
frappe.ui.form.on('Payment Entry', {
onload: function(frm) {
- frm.ignore_doctypes_on_cancel_all = ['Sales Invoice', 'Purchase Invoice', 'Journal Entry', 'Repost Payment Ledger','Repost Accounting Ledger'];
+ frm.ignore_doctypes_on_cancel_all = ['Sales Invoice', 'Purchase Invoice', 'Journal Entry', 'Repost Payment Ledger','Repost Accounting Ledger', 'Unreconcile Payments', 'Unreconcile Payment Entries'];
if(frm.doc.__islocal) {
if (!frm.doc.paid_from) frm.set_value("paid_from_account_currency", null);
@@ -154,6 +154,7 @@
frm.events.set_dynamic_labels(frm);
frm.events.show_general_ledger(frm);
erpnext.accounts.ledger_preview.show_accounting_ledger_preview(frm);
+ erpnext.accounts.unreconcile_payments.add_unreconcile_btn(frm);
},
validate_company: (frm) => {
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 8a894e2..feacb0f 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -148,6 +148,8 @@
"Repost Payment Ledger Items",
"Repost Accounting Ledger",
"Repost Accounting Ledger Items",
+ "Unreconcile Payments",
+ "Unreconcile Payment Entries",
)
super(PaymentEntry, self).on_cancel()
self.make_gl_entries(cancel=1)
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
index 642e99c..d4d9239 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
@@ -37,7 +37,7 @@
super.onload();
this.frm.ignore_doctypes_on_cancel_all = ['POS Invoice', 'Timesheet', 'POS Invoice Merge Log',
- 'POS Closing Entry', 'Journal Entry', 'Payment Entry', "Repost Payment Ledger", "Repost Accounting Ledger"];
+ 'POS Closing Entry', 'Journal Entry', 'Payment Entry', "Repost Payment Ledger", "Repost Accounting Ledger", "Unreconcile Payments", "Unreconcile Payment Entries"];
if(!this.frm.doc.__islocal && !this.frm.doc.customer && this.frm.doc.debit_to) {
// show debit_to in print format
@@ -183,8 +183,11 @@
}, __('Create'));
}
}
+
+ erpnext.accounts.unreconcile_payments.add_unreconcile_btn(me.frm);
}
+
make_maintenance_schedule() {
frappe.model.open_mapped_doc({
method: "erpnext.accounts.doctype.sales_invoice.sales_invoice.make_maintenance_schedule",
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index fba2fa7..7bdb2b4 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -388,6 +388,8 @@
"Repost Payment Ledger Items",
"Repost Accounting Ledger",
"Repost Accounting Ledger Items",
+ "Unreconcile Payments",
+ "Unreconcile Payment Entries",
"Payment Ledger Entry",
"Serial and Batch Bundle",
)
diff --git a/erpnext/accounts/doctype/unreconcile_payment_entries/__init__.py b/erpnext/accounts/doctype/unreconcile_payment_entries/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payment_entries/__init__.py
diff --git a/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.json b/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.json
new file mode 100644
index 0000000..42da669
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.json
@@ -0,0 +1,83 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "creation": "2023-08-22 10:28:10.196712",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "account",
+ "party_type",
+ "party",
+ "reference_doctype",
+ "reference_name",
+ "allocated_amount",
+ "account_currency",
+ "unlinked"
+ ],
+ "fields": [
+ {
+ "fieldname": "reference_name",
+ "fieldtype": "Dynamic Link",
+ "in_list_view": 1,
+ "label": "Reference Name",
+ "options": "reference_doctype"
+ },
+ {
+ "fieldname": "allocated_amount",
+ "fieldtype": "Currency",
+ "in_list_view": 1,
+ "label": "Allocated Amount",
+ "options": "account_currency"
+ },
+ {
+ "default": "0",
+ "fieldname": "unlinked",
+ "fieldtype": "Check",
+ "in_list_view": 1,
+ "label": "Unlinked",
+ "read_only": 1
+ },
+ {
+ "fieldname": "reference_doctype",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Reference Type",
+ "options": "DocType"
+ },
+ {
+ "fieldname": "account",
+ "fieldtype": "Data",
+ "label": "Account"
+ },
+ {
+ "fieldname": "party_type",
+ "fieldtype": "Data",
+ "label": "Party Type"
+ },
+ {
+ "fieldname": "party",
+ "fieldtype": "Data",
+ "label": "Party"
+ },
+ {
+ "fieldname": "account_currency",
+ "fieldtype": "Link",
+ "label": "Account Currency",
+ "options": "Currency",
+ "read_only": 1
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "istable": 1,
+ "links": [],
+ "modified": "2023-09-05 09:33:28.620149",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Unreconcile Payment Entries",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.py b/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.py
new file mode 100644
index 0000000..c41545c
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payment_entries/unreconcile_payment_entries.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+
+class UnreconcilePaymentEntries(Document):
+ pass
diff --git a/erpnext/accounts/doctype/unreconcile_payments/__init__.py b/erpnext/accounts/doctype/unreconcile_payments/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payments/__init__.py
diff --git a/erpnext/accounts/doctype/unreconcile_payments/test_unreconcile_payments.py b/erpnext/accounts/doctype/unreconcile_payments/test_unreconcile_payments.py
new file mode 100644
index 0000000..78e04bf
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payments/test_unreconcile_payments.py
@@ -0,0 +1,316 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import today
+
+from erpnext.accounts.doctype.payment_entry.test_payment_entry import create_payment_entry
+from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.accounts.test.accounts_mixin import AccountsTestMixin
+
+
+class TestUnreconcilePayments(AccountsTestMixin, FrappeTestCase):
+ def setUp(self):
+ self.create_company()
+ self.create_customer()
+ self.create_usd_receivable_account()
+ self.create_item()
+ self.clear_old_entries()
+
+ def tearDown(self):
+ frappe.db.rollback()
+
+ def create_sales_invoice(self, do_not_submit=False):
+ si = create_sales_invoice(
+ item=self.item,
+ company=self.company,
+ customer=self.customer,
+ debit_to=self.debit_to,
+ posting_date=today(),
+ parent_cost_center=self.cost_center,
+ cost_center=self.cost_center,
+ rate=100,
+ price_list_rate=100,
+ do_not_submit=do_not_submit,
+ )
+ return si
+
+ def create_payment_entry(self):
+ pe = create_payment_entry(
+ company=self.company,
+ payment_type="Receive",
+ party_type="Customer",
+ party=self.customer,
+ paid_from=self.debit_to,
+ paid_to=self.cash,
+ paid_amount=200,
+ save=True,
+ )
+ return pe
+
+ def test_01_unreconcile_invoice(self):
+ si1 = self.create_sales_invoice()
+ si2 = self.create_sales_invoice()
+
+ pe = self.create_payment_entry()
+ pe.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 100},
+ )
+ pe.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 100},
+ )
+ # Allocation payment against both invoices
+ pe.save().submit()
+
+ # Assert outstanding
+ [doc.reload() for doc in [si1, si2, pe]]
+ self.assertEqual(si1.outstanding_amount, 0)
+ self.assertEqual(si2.outstanding_amount, 0)
+ self.assertEqual(pe.unallocated_amount, 0)
+
+ unreconcile = frappe.get_doc(
+ {
+ "doctype": "Unreconcile Payments",
+ "company": self.company,
+ "voucher_type": pe.doctype,
+ "voucher_no": pe.name,
+ }
+ )
+ unreconcile.add_references()
+ self.assertEqual(len(unreconcile.allocations), 2)
+ allocations = [x.reference_name for x in unreconcile.allocations]
+ self.assertEquals([si1.name, si2.name], allocations)
+ # unreconcile si1
+ for x in unreconcile.allocations:
+ if x.reference_name != si1.name:
+ unreconcile.remove(x)
+ unreconcile.save().submit()
+
+ # Assert outstanding
+ [doc.reload() for doc in [si1, si2, pe]]
+ self.assertEqual(si1.outstanding_amount, 100)
+ self.assertEqual(si2.outstanding_amount, 0)
+ self.assertEqual(len(pe.references), 1)
+ self.assertEqual(pe.unallocated_amount, 100)
+
+ def test_02_unreconcile_one_payment_from_multi_payments(self):
+ """
+ Scenario: 2 payments, both split against 2 different invoices
+ Unreconcile only one payment from one invoice
+ """
+ si1 = self.create_sales_invoice()
+ si2 = self.create_sales_invoice()
+ pe1 = self.create_payment_entry()
+ pe1.paid_amount = 100
+ # Allocate payment against both invoices
+ pe1.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 50},
+ )
+ pe1.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 50},
+ )
+ pe1.save().submit()
+
+ pe2 = self.create_payment_entry()
+ pe2.paid_amount = 100
+ # Allocate payment against both invoices
+ pe2.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 50},
+ )
+ pe2.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 50},
+ )
+ pe2.save().submit()
+
+ # Assert outstanding and unallocated
+ [doc.reload() for doc in [si1, si2, pe1, pe2]]
+ self.assertEqual(si1.outstanding_amount, 0.0)
+ self.assertEqual(si2.outstanding_amount, 0.0)
+ self.assertEqual(pe1.unallocated_amount, 0.0)
+ self.assertEqual(pe2.unallocated_amount, 0.0)
+
+ unreconcile = frappe.get_doc(
+ {
+ "doctype": "Unreconcile Payments",
+ "company": self.company,
+ "voucher_type": pe2.doctype,
+ "voucher_no": pe2.name,
+ }
+ )
+ unreconcile.add_references()
+ self.assertEqual(len(unreconcile.allocations), 2)
+ allocations = [x.reference_name for x in unreconcile.allocations]
+ self.assertEquals([si1.name, si2.name], allocations)
+ # unreconcile si1 from pe2
+ for x in unreconcile.allocations:
+ if x.reference_name != si1.name:
+ unreconcile.remove(x)
+ unreconcile.save().submit()
+
+ # Assert outstanding and unallocated
+ [doc.reload() for doc in [si1, si2, pe1, pe2]]
+ self.assertEqual(si1.outstanding_amount, 50)
+ self.assertEqual(si2.outstanding_amount, 0)
+ self.assertEqual(len(pe1.references), 2)
+ self.assertEqual(len(pe2.references), 1)
+ self.assertEqual(pe1.unallocated_amount, 0)
+ self.assertEqual(pe2.unallocated_amount, 50)
+
+ def test_03_unreconciliation_on_multi_currency_invoice(self):
+ self.create_customer("_Test MC Customer USD", "USD")
+ si1 = self.create_sales_invoice(do_not_submit=True)
+ si1.currency = "USD"
+ si1.debit_to = self.debtors_usd
+ si1.conversion_rate = 80
+ si1.save().submit()
+
+ si2 = self.create_sales_invoice(do_not_submit=True)
+ si2.currency = "USD"
+ si2.debit_to = self.debtors_usd
+ si2.conversion_rate = 80
+ si2.save().submit()
+
+ pe = self.create_payment_entry()
+ pe.paid_from = self.debtors_usd
+ pe.paid_from_account_currency = "USD"
+ pe.source_exchange_rate = 75
+ pe.received_amount = 75 * 200
+ pe.save()
+ # Allocate payment against both invoices
+ pe.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 100},
+ )
+ pe.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 100},
+ )
+ pe.save().submit()
+
+ unreconcile = frappe.get_doc(
+ {
+ "doctype": "Unreconcile Payments",
+ "company": self.company,
+ "voucher_type": pe.doctype,
+ "voucher_no": pe.name,
+ }
+ )
+ unreconcile.add_references()
+ self.assertEqual(len(unreconcile.allocations), 2)
+ allocations = [x.reference_name for x in unreconcile.allocations]
+ self.assertEquals([si1.name, si2.name], allocations)
+ # unreconcile si1 from pe
+ for x in unreconcile.allocations:
+ if x.reference_name != si1.name:
+ unreconcile.remove(x)
+ unreconcile.save().submit()
+
+ # Assert outstanding and unallocated
+ [doc.reload() for doc in [si1, si2, pe]]
+ self.assertEqual(si1.outstanding_amount, 100)
+ self.assertEqual(si2.outstanding_amount, 0)
+ self.assertEqual(len(pe.references), 1)
+ self.assertEqual(pe.unallocated_amount, 100)
+
+ # Exc gain/loss JE should've been cancelled as well
+ self.assertEqual(
+ frappe.db.count(
+ "Journal Entry Account",
+ filters={"reference_type": si1.doctype, "reference_name": si1.name, "docstatus": 1},
+ ),
+ 0,
+ )
+
+ def test_04_unreconciliation_on_multi_currency_invoice(self):
+ """
+ 2 payments split against 2 foreign currency invoices
+ """
+ self.create_customer("_Test MC Customer USD", "USD")
+ si1 = self.create_sales_invoice(do_not_submit=True)
+ si1.currency = "USD"
+ si1.debit_to = self.debtors_usd
+ si1.conversion_rate = 80
+ si1.save().submit()
+
+ si2 = self.create_sales_invoice(do_not_submit=True)
+ si2.currency = "USD"
+ si2.debit_to = self.debtors_usd
+ si2.conversion_rate = 80
+ si2.save().submit()
+
+ pe1 = self.create_payment_entry()
+ pe1.paid_from = self.debtors_usd
+ pe1.paid_from_account_currency = "USD"
+ pe1.source_exchange_rate = 75
+ pe1.received_amount = 75 * 100
+ pe1.save()
+ # Allocate payment against both invoices
+ pe1.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 50},
+ )
+ pe1.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 50},
+ )
+ pe1.save().submit()
+
+ pe2 = self.create_payment_entry()
+ pe2.paid_from = self.debtors_usd
+ pe2.paid_from_account_currency = "USD"
+ pe2.source_exchange_rate = 75
+ pe2.received_amount = 75 * 100
+ pe2.save()
+ # Allocate payment against both invoices
+ pe2.append(
+ "references",
+ {"reference_doctype": si1.doctype, "reference_name": si1.name, "allocated_amount": 50},
+ )
+ pe2.append(
+ "references",
+ {"reference_doctype": si2.doctype, "reference_name": si2.name, "allocated_amount": 50},
+ )
+ pe2.save().submit()
+
+ unreconcile = frappe.get_doc(
+ {
+ "doctype": "Unreconcile Payments",
+ "company": self.company,
+ "voucher_type": pe2.doctype,
+ "voucher_no": pe2.name,
+ }
+ )
+ unreconcile.add_references()
+ self.assertEqual(len(unreconcile.allocations), 2)
+ allocations = [x.reference_name for x in unreconcile.allocations]
+ self.assertEquals([si1.name, si2.name], allocations)
+ # unreconcile si1 from pe2
+ for x in unreconcile.allocations:
+ if x.reference_name != si1.name:
+ unreconcile.remove(x)
+ unreconcile.save().submit()
+
+ # Assert outstanding and unallocated
+ [doc.reload() for doc in [si1, si2, pe1, pe2]]
+ self.assertEqual(si1.outstanding_amount, 50)
+ self.assertEqual(si2.outstanding_amount, 0)
+ self.assertEqual(len(pe1.references), 2)
+ self.assertEqual(len(pe2.references), 1)
+ self.assertEqual(pe1.unallocated_amount, 0)
+ self.assertEqual(pe2.unallocated_amount, 50)
+
+ # Exc gain/loss JE from PE1 should be available
+ self.assertEqual(
+ frappe.db.count(
+ "Journal Entry Account",
+ filters={"reference_type": si1.doctype, "reference_name": si1.name, "docstatus": 1},
+ ),
+ 1,
+ )
diff --git a/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.js b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.js
new file mode 100644
index 0000000..c522567
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.js
@@ -0,0 +1,41 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on("Unreconcile Payments", {
+ refresh(frm) {
+ frm.set_query("voucher_type", function() {
+ return {
+ filters: {
+ name: ["in", ["Payment Entry", "Journal Entry"]]
+ }
+ }
+ });
+
+
+ frm.set_query("voucher_no", function(doc) {
+ return {
+ filters: {
+ company: doc.company,
+ docstatus: 1
+ }
+ }
+ });
+
+ },
+ get_allocations: function(frm) {
+ frm.clear_table("allocations");
+ frappe.call({
+ method: "get_allocations_from_payment",
+ doc: frm.doc,
+ callback: function(r) {
+ if (r.message) {
+ r.message.forEach(x => {
+ frm.add_child("allocations", x)
+ })
+ frm.refresh_fields();
+ }
+ }
+ })
+
+ }
+});
diff --git a/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.json b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.json
new file mode 100644
index 0000000..f29e61b
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.json
@@ -0,0 +1,93 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "autoname": "format:UNREC-{#####}",
+ "creation": "2023-08-22 10:26:34.421423",
+ "default_view": "List",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "company",
+ "voucher_type",
+ "voucher_no",
+ "get_allocations",
+ "allocations",
+ "amended_from"
+ ],
+ "fields": [
+ {
+ "fieldname": "amended_from",
+ "fieldtype": "Link",
+ "label": "Amended From",
+ "no_copy": 1,
+ "options": "Unreconcile Payments",
+ "print_hide": 1,
+ "read_only": 1
+ },
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "label": "Company",
+ "options": "Company"
+ },
+ {
+ "fieldname": "voucher_type",
+ "fieldtype": "Link",
+ "label": "Voucher Type",
+ "options": "DocType"
+ },
+ {
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "label": "Voucher No",
+ "options": "voucher_type"
+ },
+ {
+ "fieldname": "get_allocations",
+ "fieldtype": "Button",
+ "label": "Get Allocations"
+ },
+ {
+ "fieldname": "allocations",
+ "fieldtype": "Table",
+ "label": "Allocations",
+ "options": "Unreconcile Payment Entries"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "is_submittable": 1,
+ "links": [],
+ "modified": "2023-08-28 17:42:50.261377",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Unreconcile Payments",
+ "naming_rule": "Expression",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "read": 1,
+ "role": "Accounts Manager",
+ "select": 1,
+ "share": 1,
+ "submit": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "read": 1,
+ "role": "Accounts User",
+ "select": 1,
+ "share": 1,
+ "submit": 1,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": [],
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.py b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.py
new file mode 100644
index 0000000..4f9fb50
--- /dev/null
+++ b/erpnext/accounts/doctype/unreconcile_payments/unreconcile_payments.py
@@ -0,0 +1,158 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe import _, qb
+from frappe.model.document import Document
+from frappe.query_builder import Criterion
+from frappe.query_builder.functions import Abs, Sum
+from frappe.utils.data import comma_and
+
+from erpnext.accounts.utils import (
+ cancel_exchange_gain_loss_journal,
+ unlink_ref_doc_from_payment_entries,
+ update_voucher_outstanding,
+)
+
+
+class UnreconcilePayments(Document):
+ def validate(self):
+ self.supported_types = ["Payment Entry", "Journal Entry"]
+ if not self.voucher_type in self.supported_types:
+ frappe.throw(_("Only {0} are supported").format(comma_and(self.supported_types)))
+
+ @frappe.whitelist()
+ def get_allocations_from_payment(self):
+ allocated_references = []
+ ple = qb.DocType("Payment Ledger Entry")
+ allocated_references = (
+ qb.from_(ple)
+ .select(
+ ple.account,
+ ple.party_type,
+ ple.party,
+ ple.against_voucher_type.as_("reference_doctype"),
+ ple.against_voucher_no.as_("reference_name"),
+ Abs(Sum(ple.amount_in_account_currency)).as_("allocated_amount"),
+ ple.account_currency,
+ )
+ .where(
+ (ple.docstatus == 1)
+ & (ple.voucher_type == self.voucher_type)
+ & (ple.voucher_no == self.voucher_no)
+ & (ple.voucher_no != ple.against_voucher_no)
+ )
+ .groupby(ple.against_voucher_type, ple.against_voucher_no)
+ .run(as_dict=True)
+ )
+
+ return allocated_references
+
+ def add_references(self):
+ allocations = self.get_allocations_from_payment()
+
+ for alloc in allocations:
+ self.append("allocations", alloc)
+
+ def on_submit(self):
+ # todo: more granular unreconciliation
+ for alloc in self.allocations:
+ doc = frappe.get_doc(alloc.reference_doctype, alloc.reference_name)
+ unlink_ref_doc_from_payment_entries(doc, self.voucher_no)
+ cancel_exchange_gain_loss_journal(doc, self.voucher_type, self.voucher_no)
+ update_voucher_outstanding(
+ alloc.reference_doctype, alloc.reference_name, alloc.account, alloc.party_type, alloc.party
+ )
+ frappe.db.set_value("Unreconcile Payment Entries", alloc.name, "unlinked", True)
+
+
+@frappe.whitelist()
+def doc_has_references(doctype: str = None, docname: str = None):
+ if doctype in ["Sales Invoice", "Purchase Invoice"]:
+ return frappe.db.count(
+ "Payment Ledger Entry",
+ filters={"delinked": 0, "against_voucher_no": docname, "amount": ["<", 0]},
+ )
+ else:
+ return frappe.db.count(
+ "Payment Ledger Entry",
+ filters={"delinked": 0, "voucher_no": docname, "against_voucher_no": ["!=", docname]},
+ )
+
+
+@frappe.whitelist()
+def get_linked_payments_for_doc(
+ company: str = None, doctype: str = None, docname: str = None
+) -> list:
+ if company and doctype and docname:
+ _dt = doctype
+ _dn = docname
+ ple = qb.DocType("Payment Ledger Entry")
+ if _dt in ["Sales Invoice", "Purchase Invoice"]:
+ criteria = [
+ (ple.company == company),
+ (ple.delinked == 0),
+ (ple.against_voucher_no == _dn),
+ (ple.amount < 0),
+ ]
+
+ res = (
+ qb.from_(ple)
+ .select(
+ ple.company,
+ ple.voucher_type,
+ ple.voucher_no,
+ Abs(Sum(ple.amount_in_account_currency)).as_("allocated_amount"),
+ ple.account_currency,
+ )
+ .where(Criterion.all(criteria))
+ .groupby(ple.voucher_no, ple.against_voucher_no)
+ .having(qb.Field("allocated_amount") > 0)
+ .run(as_dict=True)
+ )
+ return res
+ else:
+ criteria = [
+ (ple.company == company),
+ (ple.delinked == 0),
+ (ple.voucher_no == _dn),
+ (ple.against_voucher_no != _dn),
+ ]
+
+ query = (
+ qb.from_(ple)
+ .select(
+ ple.company,
+ ple.against_voucher_type.as_("voucher_type"),
+ ple.against_voucher_no.as_("voucher_no"),
+ Abs(Sum(ple.amount_in_account_currency)).as_("allocated_amount"),
+ ple.account_currency,
+ )
+ .where(Criterion.all(criteria))
+ .groupby(ple.against_voucher_no)
+ )
+ res = query.run(as_dict=True)
+ return res
+ return []
+
+
+@frappe.whitelist()
+def create_unreconcile_doc_for_selection(selections=None):
+ if selections:
+ selections = frappe.json.loads(selections)
+ # assuming each row is a unique voucher
+ for row in selections:
+ unrecon = frappe.new_doc("Unreconcile Payments")
+ unrecon.company = row.get("company")
+ unrecon.voucher_type = row.get("voucher_type")
+ unrecon.voucher_no = row.get("voucher_no")
+ unrecon.add_references()
+
+ # remove unselected references
+ unrecon.allocations = [
+ x
+ for x in unrecon.allocations
+ if x.reference_doctype == row.get("against_voucher_type")
+ and x.reference_name == row.get("against_voucher_no")
+ ]
+ unrecon.save().submit()
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 6a80f20..1360f73 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -674,7 +674,9 @@
payment_entry.save(ignore_permissions=True)
-def cancel_exchange_gain_loss_journal(parent_doc: dict | object) -> None:
+def cancel_exchange_gain_loss_journal(
+ parent_doc: dict | object, referenced_dt: str = None, referenced_dn: str = None
+) -> None:
"""
Cancel Exchange Gain/Loss for Sales/Purchase Invoice, if they have any.
"""
@@ -701,76 +703,147 @@
as_list=1,
)
for doc in gain_loss_journals:
- frappe.get_doc("Journal Entry", doc[0]).cancel()
+ gain_loss_je = frappe.get_doc("Journal Entry", doc[0])
+ if referenced_dt and referenced_dn:
+ references = [(x.reference_type, x.reference_name) for x in gain_loss_je.accounts]
+ if (
+ len(references) == 2
+ and (referenced_dt, referenced_dn) in references
+ and (parent_doc.doctype, parent_doc.name) in references
+ ):
+ # only cancel JE generated against parent_doc and referenced_dn
+ gain_loss_je.cancel()
+ else:
+ gain_loss_je.cancel()
-def unlink_ref_doc_from_payment_entries(ref_doc):
- remove_ref_doc_link_from_jv(ref_doc.doctype, ref_doc.name)
- remove_ref_doc_link_from_pe(ref_doc.doctype, ref_doc.name)
-
- frappe.db.sql(
- """update `tabGL Entry`
- set against_voucher_type=null, against_voucher=null,
- modified=%s, modified_by=%s
- where against_voucher_type=%s and against_voucher=%s
- and voucher_no != ifnull(against_voucher, '')""",
- (now(), frappe.session.user, ref_doc.doctype, ref_doc.name),
+def update_accounting_ledgers_after_reference_removal(
+ ref_type: str = None, ref_no: str = None, payment_name: str = None
+):
+ # General Ledger
+ gle = qb.DocType("GL Entry")
+ gle_update_query = (
+ qb.update(gle)
+ .set(gle.against_voucher_type, None)
+ .set(gle.against_voucher, None)
+ .set(gle.modified, now())
+ .set(gle.modified_by, frappe.session.user)
+ .where((gle.against_voucher_type == ref_type) & (gle.against_voucher == ref_no))
)
+ if payment_name:
+ gle_update_query = gle_update_query.where(gle.voucher_no == payment_name)
+ gle_update_query.run()
+
+ # Payment Ledger
ple = qb.DocType("Payment Ledger Entry")
+ ple_update_query = (
+ qb.update(ple)
+ .set(ple.against_voucher_type, ple.voucher_type)
+ .set(ple.against_voucher_no, ple.voucher_no)
+ .set(ple.modified, now())
+ .set(ple.modified_by, frappe.session.user)
+ .where(
+ (ple.against_voucher_type == ref_type)
+ & (ple.against_voucher_no == ref_no)
+ & (ple.delinked == 0)
+ )
+ )
- qb.update(ple).set(ple.against_voucher_type, ple.voucher_type).set(
- ple.against_voucher_no, ple.voucher_no
- ).set(ple.modified, now()).set(ple.modified_by, frappe.session.user).where(
- (ple.against_voucher_type == ref_doc.doctype)
- & (ple.against_voucher_no == ref_doc.name)
- & (ple.delinked == 0)
- ).run()
+ if payment_name:
+ ple_update_query = ple_update_query.where(ple.voucher_no == payment_name)
+ ple_update_query.run()
+
+def remove_ref_from_advance_section(ref_doc: object = None):
+ # TODO: this might need some testing
if ref_doc.doctype in ("Sales Invoice", "Purchase Invoice"):
ref_doc.set("advances", [])
-
- frappe.db.sql(
- """delete from `tab{0} Advance` where parent = %s""".format(ref_doc.doctype), ref_doc.name
- )
+ adv_type = qb.DocType(f"{ref_doc.doctype} Advance")
+ qb.from_(adv_type).delete().where(adv_type.parent == ref_doc.name).run()
-def remove_ref_doc_link_from_jv(ref_type, ref_no):
- linked_jv = frappe.db.sql_list(
- """select parent from `tabJournal Entry Account`
- where reference_type=%s and reference_name=%s and docstatus < 2""",
- (ref_type, ref_no),
+def unlink_ref_doc_from_payment_entries(ref_doc: object = None, payment_name: str = None):
+ remove_ref_doc_link_from_jv(ref_doc.doctype, ref_doc.name, payment_name)
+ remove_ref_doc_link_from_pe(ref_doc.doctype, ref_doc.name, payment_name)
+ update_accounting_ledgers_after_reference_removal(ref_doc.doctype, ref_doc.name, payment_name)
+ remove_ref_from_advance_section(ref_doc)
+
+
+def remove_ref_doc_link_from_jv(
+ ref_type: str = None, ref_no: str = None, payment_name: str = None
+):
+ jea = qb.DocType("Journal Entry Account")
+
+ linked_jv = (
+ qb.from_(jea)
+ .select(jea.parent)
+ .where((jea.reference_type == ref_type) & (jea.reference_name == ref_no) & (jea.docstatus.lt(2)))
+ .run(as_list=1)
)
+ linked_jv = convert_to_list(linked_jv)
+ # remove reference only from specified payment
+ linked_jv = [x for x in linked_jv if x == payment_name] if payment_name else linked_jv
if linked_jv:
- frappe.db.sql(
- """update `tabJournal Entry Account`
- set reference_type=null, reference_name = null,
- modified=%s, modified_by=%s
- where reference_type=%s and reference_name=%s
- and docstatus < 2""",
- (now(), frappe.session.user, ref_type, ref_no),
+ update_query = (
+ qb.update(jea)
+ .set(jea.reference_type, None)
+ .set(jea.reference_name, None)
+ .set(jea.modified, now())
+ .set(jea.modified_by, frappe.session.user)
+ .where((jea.reference_type == ref_type) & (jea.reference_name == ref_no))
)
+ if payment_name:
+ update_query = update_query.where(jea.parent == payment_name)
+
+ update_query.run()
+
frappe.msgprint(_("Journal Entries {0} are un-linked").format("\n".join(linked_jv)))
-def remove_ref_doc_link_from_pe(ref_type, ref_no):
- linked_pe = frappe.db.sql_list(
- """select parent from `tabPayment Entry Reference`
- where reference_doctype=%s and reference_name=%s and docstatus < 2""",
- (ref_type, ref_no),
+def convert_to_list(result):
+ """
+ Convert tuple to list
+ """
+ return [x[0] for x in result]
+
+
+def remove_ref_doc_link_from_pe(
+ ref_type: str = None, ref_no: str = None, payment_name: str = None
+):
+ per = qb.DocType("Payment Entry Reference")
+ pay = qb.DocType("Payment Entry")
+
+ linked_pe = (
+ qb.from_(per)
+ .select(per.parent)
+ .where(
+ (per.reference_doctype == ref_type) & (per.reference_name == ref_no) & (per.docstatus.lt(2))
+ )
+ .run(as_list=1)
)
+ linked_pe = convert_to_list(linked_pe)
+ # remove reference only from specified payment
+ linked_pe = [x for x in linked_pe if x == payment_name] if payment_name else linked_pe
if linked_pe:
- frappe.db.sql(
- """update `tabPayment Entry Reference`
- set allocated_amount=0, modified=%s, modified_by=%s
- where reference_doctype=%s and reference_name=%s
- and docstatus < 2""",
- (now(), frappe.session.user, ref_type, ref_no),
+ update_query = (
+ qb.update(per)
+ .set(per.allocated_amount, 0)
+ .set(per.modified, now())
+ .set(per.modified_by, frappe.session.user)
+ .where(
+ (per.docstatus.lt(2) & (per.reference_doctype == ref_type) & (per.reference_name == ref_no))
+ )
)
+ if payment_name:
+ update_query = update_query.where(per.parent == payment_name)
+
+ update_query.run()
+
for pe in linked_pe:
try:
pe_doc = frappe.get_doc("Payment Entry", pe)
@@ -784,19 +857,13 @@
msg += _("Please cancel payment entry manually first")
frappe.throw(msg, exc=PaymentEntryUnlinkError, title=_("Payment Unlink Error"))
- frappe.db.sql(
- """update `tabPayment Entry` set total_allocated_amount=%s,
- base_total_allocated_amount=%s, unallocated_amount=%s, modified=%s, modified_by=%s
- where name=%s""",
- (
- pe_doc.total_allocated_amount,
- pe_doc.base_total_allocated_amount,
- pe_doc.unallocated_amount,
- now(),
- frappe.session.user,
- pe,
- ),
- )
+ qb.update(pay).set(pay.total_allocated_amount, pe_doc.total_allocated_amount).set(
+ pay.base_total_allocated_amount, pe_doc.base_total_allocated_amount
+ ).set(pay.unallocated_amount, pe_doc.unallocated_amount).set(pay.modified, now()).set(
+ pay.modified_by, frappe.session.user
+ ).where(
+ pay.name == pe
+ ).run()
frappe.msgprint(_("Payment Entries {0} are un-linked").format("\n".join(linked_pe)))
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index 0ca1e94..838fe52 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -211,6 +211,37 @@
def before_cancel(self):
validate_einvoice_fields(self)
+ def _remove_references_in_unreconcile(self):
+ upe = frappe.qb.DocType("Unreconcile Payment Entries")
+ rows = (
+ frappe.qb.from_(upe)
+ .select(upe.name, upe.parent)
+ .where((upe.reference_doctype == self.doctype) & (upe.reference_name == self.name))
+ .run(as_dict=True)
+ )
+
+ if rows:
+ references_map = frappe._dict()
+ for x in rows:
+ references_map.setdefault(x.parent, []).append(x.name)
+
+ for doc, rows in references_map.items():
+ unreconcile_doc = frappe.get_doc("Unreconcile Payments", doc)
+ for row in rows:
+ unreconcile_doc.remove(unreconcile_doc.get("allocations", {"name": row})[0])
+
+ unreconcile_doc.flags.ignore_validate_update_after_submit = True
+ unreconcile_doc.flags.ignore_links = True
+ unreconcile_doc.save(ignore_permissions=True)
+
+ # delete docs upon parent doc deletion
+ unreconcile_docs = frappe.db.get_all("Unreconcile Payments", filters={"voucher_no": self.name})
+ for x in unreconcile_docs:
+ _doc = frappe.get_doc("Unreconcile Payments", x.name)
+ if _doc.docstatus == 1:
+ _doc.cancel()
+ _doc.delete()
+
def on_trash(self):
# delete references in 'Repost Payment Ledger'
rpi = frappe.qb.DocType("Repost Payment Ledger Items")
@@ -218,6 +249,8 @@
(rpi.voucher_type == self.doctype) & (rpi.voucher_no == self.name)
).run()
+ self._remove_references_in_unreconcile()
+
# delete sl and gl entries on deletion of transaction
if frappe.db.get_single_value("Accounts Settings", "delete_linked_ledger_entries"):
ple = frappe.qb.DocType("Payment Ledger Entry")
diff --git a/erpnext/public/js/erpnext.bundle.js b/erpnext/public/js/erpnext.bundle.js
index 966a9e1..0e1b23b 100644
--- a/erpnext/public/js/erpnext.bundle.js
+++ b/erpnext/public/js/erpnext.bundle.js
@@ -16,7 +16,8 @@
import "./utils/supplier_quick_entry";
import "./call_popup/call_popup";
import "./utils/dimension_tree_filter";
-import "./utils/ledger_preview.js"
+import "./utils/ledger_preview.js";
+import "./utils/unreconcile.js";
import "./utils/barcode_scanner";
import "./telephony";
import "./templates/call_link.html";
diff --git a/erpnext/public/js/utils.js b/erpnext/public/js/utils.js
index 89750f8..d435711 100755
--- a/erpnext/public/js/utils.js
+++ b/erpnext/public/js/utils.js
@@ -769,6 +769,9 @@
dialog.show();
}
+
+
+
erpnext.utils.map_current_doc = function(opts) {
function _map() {
if($.isArray(cur_frm.doc.items) && cur_frm.doc.items.length > 0) {
@@ -1097,4 +1100,4 @@
$btn.on("click", function() {
context.show_serial_batch_selector(grid_row.frm, grid_row.doc, "", "", true);
});
-}
\ No newline at end of file
+}
diff --git a/erpnext/public/js/utils/unreconcile.js b/erpnext/public/js/utils/unreconcile.js
new file mode 100644
index 0000000..acc77a6
--- /dev/null
+++ b/erpnext/public/js/utils/unreconcile.js
@@ -0,0 +1,127 @@
+frappe.provide('erpnext.accounts');
+
+erpnext.accounts.unreconcile_payments = {
+ add_unreconcile_btn(frm) {
+ if (frm.doc.docstatus == 1) {
+ if(((frm.doc.doctype == "Journal Entry") && (frm.doc.voucher_type != "Journal Entry"))
+ || !["Purchase Invoice", "Sales Invoice", "Journal Entry", "Payment Entry"].includes(frm.doc.doctype)
+ ) {
+ return;
+ }
+
+ frappe.call({
+ "method": "erpnext.accounts.doctype.unreconcile_payments.unreconcile_payments.doc_has_references",
+ "args": {
+ "doctype": frm.doc.doctype,
+ "docname": frm.doc.name
+ },
+ callback: function(r) {
+ if (r.message) {
+ frm.add_custom_button(__("Un-Reconcile"), function() {
+ erpnext.accounts.unreconcile_payments.build_unreconcile_dialog(frm);
+ });
+ }
+ }
+ });
+ }
+ },
+
+ build_selection_map(frm, selections) {
+ // assuming each row is an individual voucher
+ // pass this to server side method that creates unreconcile doc for each row
+ let selection_map = [];
+ if (['Sales Invoice', 'Purchase Invoice'].includes(frm.doc.doctype)) {
+ selection_map = selections.map(function(elem) {
+ return {
+ company: elem.company,
+ voucher_type: elem.voucher_type,
+ voucher_no: elem.voucher_no,
+ against_voucher_type: frm.doc.doctype,
+ against_voucher_no: frm.doc.name
+ };
+ });
+ } else if (['Payment Entry', 'Journal Entry'].includes(frm.doc.doctype)) {
+ selection_map = selections.map(function(elem) {
+ return {
+ company: elem.company,
+ voucher_type: frm.doc.doctype,
+ voucher_no: frm.doc.name,
+ against_voucher_type: elem.voucher_type,
+ against_voucher_no: elem.voucher_no,
+ };
+ });
+ }
+ return selection_map;
+ },
+
+ build_unreconcile_dialog(frm) {
+ if (['Sales Invoice', 'Purchase Invoice', 'Payment Entry', 'Journal Entry'].includes(frm.doc.doctype)) {
+ let child_table_fields = [
+ { label: __("Voucher Type"), fieldname: "voucher_type", fieldtype: "Dynamic Link", options: "DocType", in_list_view: 1, read_only: 1},
+ { label: __("Voucher No"), fieldname: "voucher_no", fieldtype: "Link", options: "voucher_type", in_list_view: 1, read_only: 1 },
+ { label: __("Allocated Amount"), fieldname: "allocated_amount", fieldtype: "Currency", in_list_view: 1, read_only: 1 , options: "account_currency"},
+ { label: __("Currency"), fieldname: "account_currency", fieldtype: "Currency", read_only: 1},
+ ]
+ let unreconcile_dialog_fields = [
+ {
+ label: __('Allocations'),
+ fieldname: 'allocations',
+ fieldtype: 'Table',
+ read_only: 1,
+ fields: child_table_fields,
+ },
+ ];
+
+ // get linked payments
+ frappe.call({
+ "method": "erpnext.accounts.doctype.unreconcile_payments.unreconcile_payments.get_linked_payments_for_doc",
+ "args": {
+ "company": frm.doc.company,
+ "doctype": frm.doc.doctype,
+ "docname": frm.doc.name
+ },
+ callback: function(r) {
+ if (r.message) {
+ // populate child table with allocations
+ unreconcile_dialog_fields[0].data = r.message;
+ unreconcile_dialog_fields[0].get_data = function(){ return r.message};
+
+ let d = new frappe.ui.Dialog({
+ title: 'Un-Reconcile Allocations',
+ fields: unreconcile_dialog_fields,
+ size: 'large',
+ cannot_add_rows: true,
+ primary_action_label: 'Un-Reconcile',
+ primary_action(values) {
+
+ let selected_allocations = values.allocations.filter(x=>x.__checked);
+ if (selected_allocations.length > 0) {
+ let selection_map = erpnext.accounts.unreconcile_payments.build_selection_map(frm, selected_allocations);
+ erpnext.accounts.unreconcile_payments.create_unreconcile_docs(selection_map);
+ d.hide();
+
+ } else {
+ frappe.msgprint("No Selection");
+ }
+ }
+ });
+
+ d.show();
+ }
+ }
+ });
+ }
+ },
+
+ create_unreconcile_docs(selection_map) {
+ frappe.call({
+ "method": "erpnext.accounts.doctype.unreconcile_payments.unreconcile_payments.create_unreconcile_doc_for_selection",
+ "args": {
+ "selections": selection_map
+ },
+ });
+ }
+
+
+
+}