diff --git a/erpnext/patches.txt b/erpnext/patches.txt index d71970a2a88e1f852fab481e086392eef01fb542..b012237e239cbe8d4128591eaac7606c0da8ff2e 100644 --- a/erpnext/patches.txt +++ b/erpnext/patches.txt @@ -362,6 +362,7 @@ execute:frappe.db.set_default("date_format", frappe.db.get_single_value("System erpnext.patches.v14_0.update_total_asset_cost_field erpnext.patches.v15_0.allow_on_submit_dimensions_for_repostable_doctypes erpnext.patches.v15_0.create_accounting_dimensions_in_payment_request +erpnext.patches.v14_0.update_pos_return_ledger_entries #2024-08-16 # @dokos erpnext.patches.dokos.v3_0.add_expiration_date_to_booking_legder diff --git a/erpnext/patches/v14_0/update_pos_return_ledger_entries.py b/erpnext/patches/v14_0/update_pos_return_ledger_entries.py new file mode 100644 index 0000000000000000000000000000000000000000..6a6d15e0b34a63d7da0920b20405973950e98ed5 --- /dev/null +++ b/erpnext/patches/v14_0/update_pos_return_ledger_entries.py @@ -0,0 +1,127 @@ +import frappe +from frappe import qb + +from erpnext.accounts.utils import update_voucher_outstanding + + +def get_valid_against_voucher_ref(pos_returns): + sinv = qb.DocType("Sales Invoice") + res = ( + qb.from_(sinv) + .select(sinv.name, sinv.return_against) + .where(sinv.name.isin(pos_returns) & sinv.return_against.notnull()) + .orderby(sinv.name) + .run(as_dict=True) + ) + return res + + +def build_dict_of_valid_against_reference(pos_returns): + _against_ref_dict = frappe._dict() + res = get_valid_against_voucher_ref(pos_returns) + for x in res: + _against_ref_dict[x.name] = x.return_against + return _against_ref_dict + + +def fix_incorrect_against_voucher_ref(affected_pos_returns): + if affected_pos_returns: + valid_against_voucher_dict = build_dict_of_valid_against_reference(affected_pos_returns) + + gle = qb.DocType("GL Entry") + gles_with_invalid_against = ( + qb.from_(gle) + .select(gle.name, gle.voucher_no) + .where( + gle.voucher_no.isin(affected_pos_returns) + & gle.against_voucher.notnull() + & gle.against_voucher.eq(gle.voucher_no) + & gle.is_cancelled.eq(0) + ) + .run(as_dict=True) + ) + # Update GL + if gles_with_invalid_against: + for gl in gles_with_invalid_against: + frappe.db.set_value( + "GL Entry", + gl.name, + "against_voucher", + valid_against_voucher_dict[gl.voucher_no], + ) + + # Update Payment Ledger + ple = qb.DocType("Payment Ledger Entry") + for x in affected_pos_returns: + qb.update(ple).set(ple.against_voucher_no, valid_against_voucher_dict[x]).where( + ple.voucher_no.eq(x) & ple.delinked.eq(0) + ).run() + + +def get_pos_returns_with_invalid_against_ref(): + sinv = qb.DocType("Sales Invoice") + pos_returns_without_self = ( + qb.from_(sinv) + .select(sinv.name) + .where( + sinv.docstatus.eq(1) + & sinv.is_pos.eq(1) + & sinv.is_return.eq(1) + & sinv.return_against.notnull() + & sinv.update_outstanding_for_self.eq(0) + ) + .run() + ) + if pos_returns_without_self: + pos_returns_without_self = [x[0] for x in pos_returns_without_self] + + gle = qb.DocType("GL Entry") + gl_against_references = ( + qb.from_(gle) + .select(gle.voucher_no, gle.against_voucher) + .where( + gle.voucher_no.isin(pos_returns_without_self) + & gle.against_voucher.notnull() + & gle.against_voucher.eq(gle.voucher_no) + & gle.is_cancelled.eq(0) + ) + .run() + ) + + if gl_against_references: + _vouchers = list(set([x[0] for x in gl_against_references])) + invoice_return_against = ( + qb.from_(sinv) + .select(sinv.name, sinv.return_against) + .where(sinv.name.isin(_vouchers) & sinv.return_against.notnull()) + .orderby(sinv.name) + .run() + ) + + valid_references = set(invoice_return_against) + actual_references = set(gl_against_references) + + invalid_references = actual_references.difference(valid_references) + if invalid_references: + return [x[0] for x in invalid_references] + return None + + +def update_outstanding_for_affected(affected_pos_returns): + if affected_pos_returns: + sinv = qb.DocType("Sales Invoice") + pos_with_accounts = ( + qb.from_(sinv) + .select(sinv.return_against, sinv.debit_to, sinv.customer) + .where(sinv.name.isin(affected_pos_returns)) + .run(as_dict=True) + ) + + for x in pos_with_accounts: + update_voucher_outstanding("Sales Invoice", x.return_against, x.debit_to, "Customer", x.customer) + + +def execute(): + affected_pos_returns = get_pos_returns_with_invalid_against_ref() + fix_incorrect_against_voucher_ref(affected_pos_returns) + update_outstanding_for_affected(affected_pos_returns)