Qty difference between Stock Balance and Stock Ledger Reports

[moizsami]

Information about bug

We are seeing differences in Qty for multiple items between the stock balance and ledger reports

I have made sure that all the repost item valuation jobs have been completed, still facing the issue.

Using the latest v15 version.

I am feeling mostly this is happening with Batch items. This is a production environment and affecting us really badly.

Example for one of the items.

One more issue we see is, if we click on the segragate checkmark on the Stock ledger for batches, the numbers are again displaying something else entirely.

Module

stock, buying

Version

ERPNext: v15.47.5 (version-15)
Frappe Framework: v15.51.1 (version-15)

Installation method

None

Relevant log output / Stack trace / Full Error Message.

No response

[mihir-kandoi]

Can you try setting the to and from date filters to the same value for both the reports and try again?

[moizsami]

@mihir-kandoi I have shared the stock ledger screenshot same as stock balance now

and the segragated enabled option also

[mihir-kandoi]

I'll have a word with more experienced folks regarding this issue but I have to say, this is a very rare bug.

I am pretty sure without access to your site through Frappe Cloud, we probably won't be able to assist you as it is next to impossible to replicate this issue on our machines.

If you are a Frappe Cloud customer, I'd highly recommend opening a support ticket.

[moizsami]

Unfortunately I am not on the frappe cloud, this is a self hosted environment. But I am willing to share any details or credentials if required.

[mihir-kandoi]

Since ERPs contain sensitive business data, I am not permitted to access user sites outside of Frappe Cloud.

Maybe with the relevant permission and some free time I might be able to help you but I can't make any guarantee or give you a time frame whatsoever.

On a different note: it seems like we're missing something here. Stock Balance is a crucial report. We have lots of users/customers using ERPNext for a long time. If there was something wrong with it we would have come to know very quickly.

[moizsami]

I understand, thanks for your input. if we reach to no solution I will see if we could shift temporarily to frappe cloud just for this issue resolution.

We are using ERPNext since v12, I am also seeing this issue for the first time. Some things which I think might be causing this:

• New way of Serial and batch handling: our users still have not completely understood how it works as many of times different types of error comes espceially while using it in Subcontracting module
• Subcontracting: this also has been updated and has been made a bit more complicated than before
• Creating past dated entries: its a common practice in this site to do past dated entries, Although there are no pending reposting valuation entries but this might be affecting the report.

Note; we have done some minor tweaking in Subcontracting module to make it easier for the users..thats it.

[mihir-kandoi]

Thank you for understanding.

As someone who primarily works on the Subcontracting module, I implore you to open GitHub issues regarding them. We will try to solve it.

[saivineeth100]

I just calculated what is shown in stock ledger and balance is calculated wrongly at 13 and 16 dec

In Qty	Out Qty	Balance (manual)	Balance …	Diff
0	0	0	0	0
7,184.00	0	7,184.00	7,184.00	0
0	-3,916.00	3,268.00	3,268.00	0
0	-3,268.00	0.00	304	-304
4,977.00	0	4,977.00	5,281.00	-304
4,016.00	0	8,993.00	9,297.00	-304
0	-4,977.00	4,016.00	4,320.00	-304
0	-4,017.00	-1.00	652.913	-653.913

[moizsami]

Thanks for the input @saivineeth100

@mihir-kandoi I digged down a bit more and I think i can see what is happening.

For some reason, while creating Subcontracting Receipt, when I update the consumed qty in Supplied raw material table, it does not update the Serial and Batch qty. I am sharing screenshots below

Consumed Qty is FG+Waste: 2964+304 = 3268

The detailed Serial and batch record

[mihir-kandoi]

@moizsami are you manually updating the Consumed Qty for the Raw Materials while creating the Subcontracting Receipt?

[moizsami]

@moizsami are you manually updating the Consumed Qty for the Raw Materials while creating the Subcontracting Receipt?

Yes, because system doesnt add up the Waste qty. As you can see we receive waste + FG, so we want the consumed to be total of waste + FG, thats why we manually write it and here we had done some scripting that it auto calculates and update the qty.

I need your input also if possible, isnt this how it is suppose to be ? my raw material was fully consumed but some of it was returned as waste. so shouldnt the consumed be total of FG+Waste ?

[mihir-kandoi]

Have you done some customisation that would interfere with this process?

Please check the Stock Ledger Variance report if it shows something. If it does, try reposting those entries.

Other than that I think we are out of options here as we are not able to replicate this.

As for your question, it is not necessary that raw materials should be FG + Waste as in some manufacturing processes the ratio between raw material and FG is not always fixed. You can set the consumed qty to anything you like given that it is lesser or equal to available qty.

However it is worth mentioning that when we change the consumed qty of raw material and if that RM has batch/serial bundle, user should manually change the batch qty. If they don't, the system will modify the quantity of those batches on its own to match the total consumed qty which is not always correct.

In your case the serial/batch bundle is not being updated on its own.

[moizsami]

Have you done some customisation that would interfere with this process?

From my side I have only added the option before save to auto calculate the number and insert in consumed qty

Please check the Stock Ledger Variance report if it shows something. If it does, try reposting those entries.

However it is worth mentioning that when we change the consumed qty of raw material and if that RM has batch/serial bundle, user should manually change the batch qty. If they don't, the system will modify the quantity of those batches on its own to match the total consumed qty which is not always correct.

In your case the serial/batch bundle is not being updated on its own.
Yes exactly that's what I found that the serial and batch is not getting updated always as required.

it is very difficult to ask the users to update these things (batch qty) manually as they were not doing it in older versions..

Is there any way to disable the batch and convert the item to non batch item. I know ERP doesnt allow it, but I have no other option left here. I cannot create new item as 100s of BOM are dependant on these items.

[mihir-kandoi]

From my side I have only added the option before save to auto calculate the number and insert in consumed qty

Then this can be the root cause. As said before, when you change Consumed Qty it is also mandatory to change Batch Qty of each batch item. There is no way the system can know by itself how to distribute the quantity between the batches.

You cannot convert Items with batches to Non-Batch Item. The amount of reposting required would be insane and time consuming.

What I can suggest however is to create new versions of both BOMs and Items (non-batch this time if you don't require batches for that item) and work with that.

Without knowing the exact in and outs of your masters, it's nearly impossible to help you with this as we cannot possibly replicate this.

[moizsami]

Then this can be the root cause. As said before, when you change Consumed Qty it is also mandatory to change Batch Qty of each batch item. There is no way the system can know by itself how to distribute the quantity between the batches.

You cannot convert Items with batches to Non-Batch Item. The amount of reposting required would be insane and time consuming.

What I can suggest however is to create new versions of both BOMs and Items (non-batch this time if you don't require batches for that item) and work with that.

Without knowing the exact in and outs of your masters, it's nearly impossible to help you with this as we cannot possibly replicate this.

Thank you for all the feedback, one last question, if I have an item which has only one batch (one master batch) will it then auto update the values while doing the consumption entry ?

I am thinking to disable all batches of each item and keep one only active, if it will help with the process

[moizsami]

@mihir-kandoi

Can you help me understanding what does the below highlighted option does to an entry, I tried understanding it from the documentation but still not getting it.

[mihir-kandoi]

@moizsami

1. If item has only one batch, it will still get auto updated. Same as more than one. Logically speaking, this should not cause the issue you're facing as distribution of quantity in this case becomes irrelevant.
2. The option in your screenshot will update the Serial Nos and Batch value in the line item if disabled. Take a Subcontracting Receipt for example, if this option is disabled the Serial Nos and Batch No fields get updated along with the Bundle value. If not, only the Bundle value gets updated. Here is the code for it for your reference.

if not frappe.db.get_single_value("Stock Settings", "do_not_update_serial_batch_on_creation_of_auto_bundle"):
	if sn_doc.has_serial_no:
		values_to_update["serial_no"] = ",".join(cstr(d.serial_no) for d in sn_doc.entries)
	elif sn_doc.has_batch_no and len(sn_doc.entries) == 1:
		values_to_update["batch_no"] = sn_doc.entries[0].batch_no

	frappe.db.set_value(self.child_doctype, self.sle.voucher_detail_no, values_to_update)