ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Changed posting_date of JE in the database - General Ledger still uses old Posting Date

mariadb

#1

ERPNext: v11.1.18 (master)
Frappe Framework: v11.1.18 (master)


I have a journal entry (JV-2019-00028) which was posted on the wrong day (April 8, instead of April 9). Now I try to correct this by changing the posting_date of that faulty entry

update `tabJournal Entry`
set posting_date = replace(posting_date, '2019-04-08', '2019-04-09') WHERE name="JV-2019-00028";

then I check the database and the posting_date value was altered as planned.
Also when I look at the Journal Entry JV-2019-00028 in the GUI it has the altered Posting Date.

So, thus far everything is looking good. When I look at the General Ledger filtered to one of the involved accounts of this JE (Cash In Hand) the posting date is still shown on April 8 (which is the date that was used when posted the JE in the GUI).

I have executed bench clear-cache, bench clear-website-cache, bench restart with no result in this regards. Can someone advise what is the mistake I am making?


P.S. I am aware I can Cancel ‘n’ Amend the Journal Entries, but out of the desire to learn am trying “the SQL way”


#2

modifying accounting entry through database, you should never do this in any erp system.


#3

And for your question, General Ledger Report is based on GL Entry doctype, which is a read-only doctype but if you insist on modifying entry through SQL, you can try but I can’t sure will anything go wrong. The best way to do this is reverse back your change and do it the right way with Reverse Journal Entry
https://erpnext.com/docs/user/manual/en/accounts/reverse-journal-entry


#4

thanks for pointing out these thoughts.

Probably I didn’t make it clear enough that my attempt as per this Topic was pointing towards a technical exercise and not a business practice.

Agree, to the points raised in general. Only that in my financial jurisdiction it is not problem to Amend a JE in the way ERPNext offers this (with the reference to the Amended Document, as well as the amended document itself) being available.


Actually this conversation has increased my understanding of the background mechanics of ERPNext, so thanks for that