Everything were going alright and every report were alright until suddenly stock valuation system got messed up. I purchase an item at 3100 per unit and the valuation rate was 3100 in stock ledger then suddenly the rate went high even though nothing was changed. The valuation method is still FIFO, unit price was same. Now these messed up my Profit and Loss statement report.
I can not find the error neither can I calculate it properly. I’m attaching the stock ledger for that item; if anyone can point out if there’s any explanation, I’d be really really grateful.
Serialized items which are appearing in my invoice list with “Commas” are messing up the valuation rate in stock ledger. ERPNext says serials should be input one by one in lines. Using commas for serials while adding serial item don’t automatically aligns the serials in line in the pop up serial add window. But adding serials within the item [closing the pop up window], commas disappear and serials get aligned line by line.
I researched an invoice and then found an item with serials which Valuation rate somehow got messed up. I then edited the invoice and made the serials one by one in lines and the valuation rate got corrected.
Screenshots of the invoice and stock ledger. [1. serials with commas invoice. 2. without commas]
Now, finding all the invoices and amending them is not a solution at all and these messed up valuation rate messed up my P&L report and everything else. I’d kindly request ERPNext team to look into these matter, specially @nabinhait and @umair brother, You’ve helped me a lot over the years and I’d really appreciate if you can give a solution.
Thank you for your reply. Unfortunately I haven’t created an issue yet. I thought I’d discuss in the forum first.
My production environment is ERPNext: v10.1.64 (master), Frappe Framework: v10.1.56 (master) and I tried it after restoring the database on a v12 server.
As for the reference links, it shows adding serials from the pop-up window and clicking add serial button. I was talking about copy & pasting serials [ which are already purchased from a word doc or something] in the serials box.
Good info - so with say v10, to copy and paste a comma-separated list into a serial box, the valuation rate code works with commas but now in v12 that is no longer the case.
Please post two screenshots: one the dialog where you enter and create the list of serial numbers, and the other, the dialog box to edit the list to remove the commas. The goal is to find where the list with the commas is stored in the database, and also the valuation code - there is code that refers to a packing_list.
Sales Invoice item pops up for serials and pasted the serials from a word document. Typing serials with commas in the pop up serial box results the same!
I’ve created a Github issue, but, I’m not sure what that will achieve. Also, my production ERP’s accounting is not accurate. This is humanly not possible to check every single invoices which caused this!
Well, with open source fortunately you can always ‘scratch your own itch’ - users are always free to secure a resource themselves to contribute a fix for this.
So then you think the problem here lies with your data rather than the code - specifically your serial number data includes rather than omits the comma!?
Maybe test for that - use a space-delimited (not a comma-delimited) list of serial numbers - does that practice work and also fix your valuation and reporting problem?
If so all that remains is to clean up your current ‘bad’ serial number data.
@clarkej As I’ve demonstrated earlier, screenshots of the invoice which has serials numbers with commas; caused COGS ledger to calculate incorrect. Amending the invoice, removing the serials commas, made things alright.
I don’t know how I can remove/clean up all these data. I don’t know which invoices have these issues and manually checking all and amending the invoices is not a solution I’m looking for. There’s gotta be a way, easier one!
Also, perhaps I’ve failed to mention, valuation rate always didn’t get messed up for comma serials invoices. They were fine, until one invoice suddenly made ambiguous valuation rate in stock ledger and then never the same old good calculation again. Please check these.
Even though this invoice has serials with commas, the valuation rate were alright.
Now you can tell me that, then the serials with commas aren’t the culprit. Believe me, I tried to find any other issue and didn’t find anything unusual. And amending the invoice, removing the serials resolved the issue where in stock ledger it happened. Also, if I try to create another invoice in that date as the invoice in the screenshot, with serials and commas, then the valuation rate for both will get messed up.
So, they were fine, until one day they were not! And I don’t think I’ve made any kind of changes in the core that could cause it.
I also want to take a moment to express my gratitude towards you as you’ve been so much helpful and supportive. Thank you again.
Right but the ‘how’ all depends on and only follows from the ‘what’ and ‘where’ - first you must resolve and specify the latter before you can postulate how to proceed?
To resolve whether the data (and not the code) is the problem source, one must inspect the database.
The idea and goal here is to find a ‘smoking gun’ case where a serial number has a trailing comma?
So what do you get when you run something like this:
frappe@ubuntu:~/frappe-bench$ bench mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 83
Server version: 10.2.13-MariaDB-10.2.13+maria~xenial-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [1bd3e0294da19198]> select name, serial_no, sales_invoice from `tabSerial No` where sales_invoice like '%SINV-00690%';
This is the output. Apology for the delayed response.
$bench mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 385
Server version: 10.2.29-MariaDB-1:10.2.29+maria~bionic-log mariadb.org binary di stribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [_1bd3e0294da19198]> select name, serial_no, sales_invoice from tabSerial No where sales_invoice like ‘%SINV-006 90%’;
±-----------------±-----------------±--------------+
| name | serial_no | sales_invoice |
±-----------------±-----------------±--------------+
| 20EZ1QHJ70E1EAFB | 20EZ1QHJ70E1EAFB | SINV-00690 |
±-----------------±-----------------±--------------+
1 row in set (0.00 sec)
Well, I’m not sure how it happened. Serialized items are bound to have serials matching to quantities in vouchers; except we can see that it only counted single serial here!
If I were to make any item zero, which have wrong valuation rate, then balance value would be zero and then the new entries will start valuating freshly according to the next purchase invoice.
