[Idea] Solution to Stock Transfer Reposting Problem

Frappe Framework
1

This is a technical post meant to be understood by developers who understand the Stock Ledger system in ERPNext.

Problem: (Nabin’s Post @ [discussion] Immutable Ledger - #29 by nabinhait)

When Stock Ledger is reposted (either because of back dated stock transaction or landed cost voucher) future entries are recalculated and almost everything goes fine EXCEPT for when reposting these kind of entries:

  • Stock Transfer
  • Manufacture Entry / Repack Entry
  • Sales Return / Purchase Return

To recreate the problem:

  1. Make Purchase Receipt @ incoming rate of $100 in Warehouse A
  2. Make Stock Transfer from Warehouse A to Warehouse B
    2.5. $100 reduced from Warehouse A and $100 increased in Warehouse B
  3. Make Landed Cost Voucher against Purchase Receipt for additional cost of $50
  4. Notice that Purchase Receipt and Stock Transfer is reposted / recalculated
    4.1 Purchase Receipt’s incoming rate for Warehouse A now becomes $150 (alright)
    4.2 Stock Transfer’s outgoing rate for Warehouse A now becomes $150 (alright)
    4.3 Stock Transfer’s incoming rate for Warehouse B is STILL $100 (NOT ALRIGHT)
    4.4 Stock Transfer’s General Leger entry will show Dr $50 in Stock Adjustment account to adjust for this change in outgoing and incoming value

Idea For a Solution in a Nutshell

  • Treat the Stock Ledger as a graph data structure
  • Allow incoming rate to be dependent on a previous stock ledger entries
  • Allow explicit outgoing rate (used in purchase returns) to be dependent on previous stock ledger entries

What does a Stock Ledger Graph look like (click to expand cases and see graph diagrams)

Case 1: Stock Transfer

Incoming Rate is dependent on Outgoing Rate of item that is transferred so when Incoming Rate of Purchase Receipt is updated, the Outgoing Rate of Stock Transfer is updated, but the Incoming Rate of Stock Transfer is not! Incoming Rate of Stock Transfer has to be updated too.

1
11240×1754 256 KB

Case 2: Sales Return

Incoming Rate in Sales Return is dependent on Outgoing Rate at which the item was originally sold so when Outgoing Rate of original sale is changed then Incoming Rate of returned items have to be updated too. Currently the Incoming Rate is fixed.

For Purchase Return it’s even worse. It can cause nasty values in the stock ledger like Balance Qty = 0 while Balance Value = negative $100

2
21240×1754 249 KB

Case 3: Repack / Manufacture Entry

Incoming Rate of Finished Good is dependent on Outgoing Rate of raw materials

3
31754×1240 299 KB

Graph Traversal for Repost

Graph structures are great for representing dependencies like this and great for traversing all connected entries.

When a Stock Voucher is supposed to be reposted (by Landed Cost Voucher) or when future entries need to be updated (by back dated stock transaction):

  • Query the subgraph: meaning get all the future entries and all dependent entries in those future entries
  • Build an in-memory graph data structure
  • Walk through the whole subgraph
    • If there is a dependent incoming_rate or dependent outgoing_rate then calculate it from past entry
    • Recalculate and update SLE

Child DocType in Stock Ledger Entry to hold information about dependent incoming rates and outgoing rates

A child DocType for Stock Ledger Entry will have to be created that will serve the following purposes:

  1. Determine link (or edges) between SLEs (or vertices) joined by voucher_detail_no
  2. Determine how to calculate dependent incoming_rate and dependent outgoing_rate

Challenges

  • Querying a graph to get a complete subgraph in an RDBMS
    • While locking SLEs to be modified to prevent concurrency issues and maintain ACID compliance
  • Graph traversal sequence
  • Updating the General Ledger reposting list

Querying a graph to get a complete subgraph in an RDBMS

I have 3 methods in mind to query the Stock Ledger Subgraph to be modified

  1. Using a Common Table Expression (CTE) SQL Query allows querying a graph like structure (https://www.essentialsql.com/introduction-common-table-expressions-ctes/)
  2. Using a Graph Database Engine to store and query the SLE graph like OQGraph (https://mariadb.com/kb/en/oqgraph-storage-engine/)
  3. Lock tabStock Ledger Entry Table → Recursively query the whole subgraph for update → Unlock table tabStock Ledger Entry

Graph traversal sequence

The diagram in Case 2: Sales Return shows that Sales Return is dependent on it’s previous entry (Purchase Receipt 2) and the incoming_rate determining Delivery Note.

So it may be necessary to use a dependency resolution algorithm in order to traverse in a way that walks through all the dependent entries first. Graphs are great at resolving dependency though.

Updating the General Ledger reposting list

Stock Ledger Repost method will have to create a list of all the Stock Vouchers it has touched/updated and pass that on as an argument to the method that is responsible for General Ledger reposting. Right now General Ledger repost method runs completely independently of the Stock Ledger repost method

Note on performance (lock wait timeout issue)

Stock Ledger Entry reposting is not responsible for the lock wait timeout issue, Stock Ledger Entry repost method can repost through hundreds of thousands of SLEs within a span of a few seconds and can be made even more efficient with bulk updates rather than running sle_doc.db_update() serially

It is the General Ledger reposting that takes a VERY LONG time to complete. It can be improved by loading all documents to repost in bulk rather than serially running frappe.get_doc() on each and every document.

12 Likes
2

The existing ERPNext python code is elegant but doing too much A single OLAP process of going over transactions that affect inventory valuation can be done as a background process.

3

I get what you’re trying to say Joseph. But this is more about the accuracy of the Stock Ledger than about the accounting principles or perpetual inventory accounting or periodic inventory accounting.

Right now if you open Stock Balance report or Stock Ledger report, the values will be incorrect if there are stock transfers or repack entries and landed cost vouchers in the system.

Take for example a flagship phone costs $1000 according to the supplier. But your government wants to tax you for importing that phone so they add lots of duties and taxes that increase the cost to $1700. Would you want your ERP to show your $1700 phone is worth $1000? And that $700 is just an inventory adjustment?
This is not an exaggeration by the way, duties and taxes really are that high in some parts of the world. And this is a real world ERPNext problem.

With an accurate Stock Ledger, you can calculate Gross Profit for each sale with 100% confidence that you’re making a profit no matter what. Some companies set their prices based on the Gross Profit and need the correct values at the right time.

Right now the Stock Ledger cannot do that and if you cannot trust an ERP to be accurate then you would spend extra time doing your own analysis every time rather than focusing on something that people are better at. Or you can write your own reports that handles a subset of cases to do your analysis.

This idea is a catch-all solution to achieve 100% accurate values in the Stock Ledger.

Is there any reason not to go for an accurate system?

2 Likes
4

Thanks for this. It’s interesting and well thought through.

I agree that separating valuation from the stock ledger would be a huge help. It certainly doesn’t solve the problems you’re describing, but it would make the work of solving them a lot simpler (and likely computationally more efficient). A valuation child/linked doctype like you mention would be one way to do that, especially elegant because it would provide both flexibility and ledger immutability simultaneously.

I also agree with you that computational efficiency is no justification for decreased accuracy. I wonder, though, if a full graph implementation is necessary to clear out the bottlenecks. Graphs are great when it’s computationally hard to locate dependencies, but for forward stock valuation I’m not sure that it is. It’s very possible I’m missing something, but given an item_code and a transaction date it certainly seems straightforward to locate down-stream dependencies even with a flat database (such as in your three cases).

In other words, wouldn’t disambiguating stock quantity/value and improving on serial get_doc()s likely fix the performance issues, without requiring a new data abstraction layer?

1 Like
5

Thank you, you understand.

I’m not saying that use a graph database system. Using multiple database systems is a very difficult task. All I’m saying that you can consider the Stock Ledger as a graph structure (because it is).

An example is:

  1. Item 1 was purchased and received in Warehouse A @ $100
  2. Item 1 was transferred from Warehouse A to Warehouse B
  3. Item 1 was then transferred from Warehouse B to Warehouse C
  4. Item 1 was delivered to customer from Warehouse C

Notice that from (1) to (4), it is the same item worth $100.
So you can see that transaction (4) is dependent on transaction (3) which is dependent on transaction (2) which is dependent on transaction (1).

So any change in valuation to transaction (1) must propagate all the way from (1) till (4). And currently the system does not do that because it does not see the dependency of all these transactions. You can still achieve this with MariaDB and PostgreSQL.

This is not what I’m saying at all. The idea is to recalculate incoming and outgoing rates for all future entries (already done) and all entries connected to the future entries (not being done at the moment).

I don’t see how you can separate valuation from stock ledger. All stock related reports and stock accounting is based on the stock ledger in ERPNext. I don’t know where else would be better to store the valuation of stock.

1 Like
6

Nice post @saifi0102!

I am starting the development of this reposting thing from tomorrow. I will ping you on telegram if any kind of help needed.

2 Likes
8

If you want mutable valuation against an immutable ledger, I think the easiest thing to do would be to treat the valuation queue/moving average as what it actually is: a cache. Storing item valuation explicitly denormalizes your database, since all of these values are by necessity derivable from otherwise normalized transactions.

It probably makes sense to do it this way for performance reasons, but it’d be a lot less messy to do it in a separate document chain that references the stock ledger, rather than doing it in the ledger itself.

10

I have made custom gross profit reports and I can tell you, deriving values from transaction is 10x more complex than having denormalized Stock Ledgers and General Ledgers with data preprocessed and readily available.

But I’m stuck with deriving values from transactions just because the Stock Ledger is not accurate yet.

11

I think this can be achieved by filling in Rate field for outgoing WH. And then referred in the incoming WH or SO.

12

You can give it a try and you’ll see for your self in version 12 that if you change $100 to $120 either by cancelling and amending the transaction or by creating a landed cost voucher. Then Warehouse A will have $120 worth item and Warehouse B and C will have $100 worth item.

13

Sorry, I meant in the table presented by @nabinhait the column Rate is NA when the Qty is outgoing (negative qty) from a WH. In other post about this Stock and immutable (I guess there are 3 or 4 different threads now) I mention this.

14

Right…this complexity is exactly why you don’t want to be writing it to the ledger itself. As your step 4.3 shows, the relationship between stock ledger entries and valuation calculations is not one-to-one. To represent them that way invites precisely the kind of bugs you’re observing.

15

You misunderstood me. To solve this complexity, values are prepossessed and stored in the stock ledger.

16

For the immutable ledger cancellation reverse gl entries which uses the arbitrary date “today”, can this date be changed to the “latest date possible” instead of “today”?

For example, if the last entry date is January 1 - because only the opening inventory was entered and cancelled, the reverse gl entries can have the date January 1 instead of today.

Since today is outside the context of the data. It simply assumes that everything is ok. What if the server date for today is January 1, 2004?

It is also possible for users to submit future dated transactions (whether in error, or by intent). So, if there is a cancel, the reverse gl entries with date today are actually backdated transactions.

1 Like
17

hello saifi0102,
are you have any update about this problem?

18

Last I asked Nabin, he was working on something for this, it wasn’t the same as what I suggested, however that was in late September, no idea about it now

19

@nabinhait

Has it been implemented in the latest versions , In companies where there are a lot of stock transfers and back dated entries are possible , it causes a huge difference in the profit and loss statement because of the stock adjustment account values .

It is actually a big deterrent to use the platform as the accounting becomes inaccurate and confusing to solve.

4 Likes
20

The implementation lags careful thinking of the real life situation in executing transactions I guess. Our ledger is messed up and every stock involving inboud or outbound transfer, sale, issue creates a reposting and transactions cannot be deleted. I am not sure about how the accounting entries are affected and how reversals effect accuracy. This is another feature being implemented before thorough testing and business case. Damages ErpNext quality IMHO.

21

May I ask for opinions regarding Stock Valuation, (it does not matter whether FIFO, LIFO, or Moving Average).

  1. If you transfer a Stock Item from one warehouse to another warehouse owned by the same Company, do you:

(A) change the Stock Valuation of the Item taking into account the newer Stock Transfer Date?

  • Meaning, if it is FIFO, the Stock Transfer Date becomes the new reckoning date for FIFO valuation, which means the valuation for this group of items can be delayed or reset to a later date.
  • if it is moving average, a new moving average cost is computed for this group of stock items - meaning, it is possible to change the cost of items within the company.

or

(B) if the stocks are moved to different locations or warehouses owned by the Company, the Stock Valuation is not affected: meaning:

  • for Fifo, the reckoning date for Fifo remains the original aquisition cost.
  • for Moving average, new average costs are not computed.

What I am trying to do here is to slowly get a consensus of how Stock Costing is done - based on Accounting Standards, as well as common or standard Company practices.

My next question is, would your company would prefer Standard Costing with end-of-period (say monthly) adjusting entries?

Meaning - You assign a standard cost for a stock item which is used to compute cost of goods for ledger accounting on the transaction (posting) date.

At the end of the period, you go through every transaction during the period, and compute the variance of the Standard Cost from the Actual Cost (based on the costing method - Fifo / Moving Average / LIFO), and make an adjusting entry based on the variance.

1 Like
22

You can change the code if you like. I don’t mean to tease.

What I mean is, maybe, if the community can share some data on this and discuss where the errors are – and what the correct values should be, it would be easier to go into the code, and trace what changes are needed to achieve your desired values.

To say values are messed up, without showing data does not move things forward toward the desired objective - which is satisfactory stock valuation for “your” (and my) company

1 Like