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:
- Make Purchase Receipt @ incoming rate of $100 in Warehouse A
- Make Stock Transfer from Warehouse A to Warehouse B
2.5. $100 reduced from Warehouse A and $100 increased in Warehouse B - Make Landed Cost Voucher against Purchase Receipt for additional cost of $50
- 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.
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
Case 3: Repack / Manufacture Entry
Incoming Rate of Finished Good is dependent on Outgoing Rate of raw materials
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 dependentoutgoing_rate
then calculate it from past entry
- If there is a dependent
-
- 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:
- Determine link (or edges) between SLEs (or vertices) joined by
voucher_detail_no
- Determine how to calculate dependent
incoming_rate
and dependentoutgoing_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
- Using a Common Table Expression (CTE) SQL Query allows querying a graph like structure (https://www.essentialsql.com/introduction-common-table-expressions-ctes/)
- Using a Graph Database Engine to store and query the SLE graph like OQGraph (https://mariadb.com/kb/en/oqgraph-storage-engine/)
- Lock
tabStock Ledger Entry
Table → Recursively query the whole subgraphfor update
→ Unlock tabletabStock 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.