Try ERPNext Buy Support Partners Foundation

Cost of Goods Sold valuation is messed up suddenly

Hi,

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.

Thanks in advance.

I have found the issue [at least I think it is].

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]

Amended

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.

Thanks again.

What version are you currently on, and have you reported this on https://github.com/frappe/erpnext/issues ?

Note these two PRs to fix an Add Serial No button issue on a Delivery Note:

back in August https://github.com/frappe/erpnext/pull/18570 released in 12.1.8 (I think)

Then this related one 10 days ago https://github.com/frappe/erpnext/pull/19550 that has not yet been released

Perhaps the August one broke the Sales Invoice?

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.

Thank you again.

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.

Hi, I’m trying to give you a detailed step by step from purchase to sales invoice.

  1. Purchase Invoice, serials are pasted with commas
    PINV%20SN%20with%20comma

  2. Purchase Invoice serials gets auto aligned as the serials were pasted in the field within the item; no pop up window for serial for purchase invoice.
    PINV%20SN%20auto%20aligned

  3. Serials get created

  4. 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!


    Pasted%20in%20SN%20POP%20up

  5. Serials don’t get automatically aligned in lines.

  6. Created invoice shows serials with commas.

  7. Serials gets automatically aligned in lines when pasted/typed in serial box within the item.


    SINV%20auto%20align

  8. Invoice doesn’t have serials with commas and valuation rate doesn’t get messed up either.

Now, I don’t know what can I do and how else can I present the bug.

Thanks

My guess is this commit in August and merged in the 12.1.0 release is the source of the bug you have found fix: incorrect valuation rate calculated because of string replacement issue

Please submit a github issue referring to these and also this discussion thanks!

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.

https://github.com/frappe/erpnext/issues/19685

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%';

Hi,

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 © 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)

MariaDB [_1bd3e0294da19198]>

No trailing comma there, so that disproves my theory. I don’t understand why just a single item and not 13, the same as the gui list.

Well, what caused it to show only single serial in the list, also caused all the problems I’m facing and this thread is about. Right?

How do you figure this, what is your reasoning here?

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.

You have lost me with your explanation. You best focus on one representative problem case - for example SINV-00690 or perhaps SINV-00736

Well, I just meant, the same item’s valuation rate is now again calculating correctly once the stock became zero. Nevertheless,

We still don’t know what to do regarding our issue.

Have you run this test?