chore: ledger invariant check report (#28921)
diff --git a/erpnext/stock/report/stock_ledger_invariant_check/__init__.py b/erpnext/stock/report/stock_ledger_invariant_check/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_invariant_check/__init__.py
diff --git a/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.js b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.js
new file mode 100644
index 0000000..c484516
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.js
@@ -0,0 +1,43 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+const DIFFERNCE_FIELD_NAMES = [
+ "difference_in_qty",
+ "fifo_qty_diff",
+ "fifo_value_diff",
+ "fifo_valuation_diff",
+ "valuation_diff",
+ "fifo_difference_diff"
+];
+
+frappe.query_reports["Stock Ledger Invariant Check"] = {
+ "filters": [
+ {
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "label": "Item",
+ "mandatory": 1,
+ "options": "Item",
+ get_query: function() {
+ return {
+ filters: {is_stock_item: 1, has_serial_no: 0}
+ }
+ }
+ },
+ {
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "label": "Warehouse",
+ "mandatory": 1,
+ "options": "Warehouse",
+ }
+ ],
+ formatter (value, row, column, data, default_formatter) {
+ value = default_formatter(value, row, column, data);
+ if (DIFFERNCE_FIELD_NAMES.includes(column.fieldname) && Math.abs(data[column.fieldname]) > 0.001) {
+ value = "<span style='color:red'>" + value + "</span>";
+ }
+ return value;
+ },
+};
diff --git a/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.json b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.json
new file mode 100644
index 0000000..d28fe0f
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.json
@@ -0,0 +1,26 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2021-12-16 06:31:23.290916",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2021-12-16 09:55:58.341764",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Stock Ledger Invariant Check",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Stock Ledger Invariant Check",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "System Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
new file mode 100644
index 0000000..ca47a1e
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
@@ -0,0 +1,236 @@
+# Copyright (c) 2021, Frappe Technologies Pvt. Ltd. and contributors
+# License: GNU GPL v3. See LICENSE
+
+import json
+
+import frappe
+
+SLE_FIELDS = (
+ "name",
+ "posting_date",
+ "posting_time",
+ "creation",
+ "voucher_type",
+ "voucher_no",
+ "actual_qty",
+ "qty_after_transaction",
+ "incoming_rate",
+ "outgoing_rate",
+ "stock_queue",
+ "batch_no",
+ "stock_value",
+ "stock_value_difference",
+ "valuation_rate",
+)
+
+
+def execute(filters=None):
+ columns = get_columns()
+ data = get_data(filters)
+ return columns, data
+
+
+def get_data(filters):
+ sles = get_stock_ledger_entries(filters)
+ return add_invariant_check_fields(sles)
+
+
+def get_stock_ledger_entries(filters):
+ return frappe.get_all(
+ "Stock Ledger Entry",
+ fields=SLE_FIELDS,
+ filters={
+ "item_code": filters.item_code,
+ "warehouse": filters.warehouse,
+ "is_cancelled": 0
+ },
+ order_by="timestamp(posting_date, posting_time), creation",
+ )
+
+
+def add_invariant_check_fields(sles):
+ balance_qty = 0.0
+ for idx, sle in enumerate(sles):
+ queue = json.loads(sle.stock_queue)
+
+ fifo_qty = 0.0
+ fifo_value = 0.0
+ for qty, rate in queue:
+ fifo_qty += qty
+ fifo_value += qty * rate
+
+ balance_qty += sle.actual_qty
+ if sle.voucher_type == "Stock Reconciliation" and not sle.batch_no:
+ balance_qty = sle.qty_after_transaction
+
+ sle.fifo_queue_qty = fifo_qty
+ sle.fifo_stock_value = fifo_value
+ sle.fifo_valuation_rate = fifo_value / fifo_qty if fifo_qty else None
+ sle.balance_value_by_qty = (
+ sle.stock_value / sle.qty_after_transaction if sle.qty_after_transaction else None
+ )
+ sle.expected_qty_after_transaction = balance_qty
+
+ # set difference fields
+ sle.difference_in_qty = sle.qty_after_transaction - sle.expected_qty_after_transaction
+ sle.fifo_qty_diff = sle.qty_after_transaction - fifo_qty
+ sle.fifo_value_diff = sle.stock_value - fifo_value
+ sle.fifo_valuation_diff = (
+ sle.valuation_rate - sle.fifo_valuation_rate if sle.fifo_valuation_rate else None
+ )
+ sle.valuation_diff = (
+ sle.valuation_rate - sle.balance_value_by_qty if sle.balance_value_by_qty else None
+ )
+
+ if idx > 0:
+ sle.fifo_stock_diff = sle.fifo_stock_value - sles[idx - 1].fifo_stock_value
+ sle.fifo_difference_diff = sle.fifo_stock_diff - sle.stock_value_difference
+
+ return sles
+
+
+def get_columns():
+ return [
+ {
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "label": "Stock Ledger Entry",
+ "options": "Stock Ledger Entry",
+ },
+ {
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "label": "Posting Date",
+ },
+ {
+ "fieldname": "posting_time",
+ "fieldtype": "Time",
+ "label": "Posting Time",
+ },
+ {
+ "fieldname": "creation",
+ "fieldtype": "Datetime",
+ "label": "Creation",
+ },
+ {
+ "fieldname": "voucher_type",
+ "fieldtype": "Link",
+ "label": "Voucher Type",
+ "options": "DocType",
+ },
+ {
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "label": "Voucher No",
+ "options": "voucher_type",
+ },
+ {
+ "fieldname": "batch_no",
+ "fieldtype": "Link",
+ "label": "Batch",
+ "options": "Batch",
+ },
+ {
+ "fieldname": "actual_qty",
+ "fieldtype": "Float",
+ "label": "Qty Change",
+ },
+ {
+ "fieldname": "incoming_rate",
+ "fieldtype": "Float",
+ "label": "Incoming Rate",
+ },
+ {
+ "fieldname": "outgoing_rate",
+ "fieldtype": "Float",
+ "label": "Outgoing Rate",
+ },
+ {
+ "fieldname": "qty_after_transaction",
+ "fieldtype": "Float",
+ "label": "(A) Qty After Transaction",
+ },
+ {
+ "fieldname": "expected_qty_after_transaction",
+ "fieldtype": "Float",
+ "label": "(B) Expected Qty After Transaction",
+ },
+ {
+ "fieldname": "difference_in_qty",
+ "fieldtype": "Float",
+ "label": "A - B",
+ },
+ {
+ "fieldname": "stock_queue",
+ "fieldtype": "Data",
+ "label": "FIFO Queue",
+ },
+
+ {
+ "fieldname": "fifo_queue_qty",
+ "fieldtype": "Float",
+ "label": "(C) Total qty in queue",
+ },
+ {
+ "fieldname": "fifo_qty_diff",
+ "fieldtype": "Float",
+ "label": "A - C",
+ },
+ {
+ "fieldname": "stock_value",
+ "fieldtype": "Float",
+ "label": "(D) Balance Stock Value",
+ },
+ {
+ "fieldname": "fifo_stock_value",
+ "fieldtype": "Float",
+ "label": "(E) Balance Stock Value in Queue",
+ },
+ {
+ "fieldname": "fifo_value_diff",
+ "fieldtype": "Float",
+ "label": "D - E",
+ },
+
+ {
+ "fieldname": "stock_value_difference",
+ "fieldtype": "Float",
+ "label": "(F) Stock Value Difference",
+ },
+ {
+ "fieldname": "fifo_stock_diff",
+ "fieldtype": "Float",
+ "label": "(G) Stock Value difference (FIFO queue)",
+ },
+ {
+ "fieldname": "fifo_difference_diff",
+ "fieldtype": "Float",
+ "label": "F - G",
+ },
+ {
+ "fieldname": "valuation_rate",
+ "fieldtype": "Float",
+ "label": "(H) Valuation Rate",
+ },
+ {
+ "fieldname": "fifo_valuation_rate",
+ "fieldtype": "Float",
+ "label": "(I) Valuation Rate as per FIFO",
+ },
+
+ {
+ "fieldname": "fifo_valuation_diff",
+ "fieldtype": "Float",
+ "label": "H - I",
+ },
+ {
+ "fieldname": "balance_value_by_qty",
+ "fieldtype": "Float",
+ "label": "(J) Valuation = Value (D) ÷ Qty (A)",
+ },
+ {
+ "fieldname": "valuation_diff",
+ "fieldtype": "Float",
+ "label": "H - J",
+ },
+ ]
diff --git a/erpnext/stock/report/test_reports.py b/erpnext/stock/report/test_reports.py
index d7fb5b2..1dcf863 100644
--- a/erpnext/stock/report/test_reports.py
+++ b/erpnext/stock/report/test_reports.py
@@ -41,6 +41,12 @@
("Total Stock Summary", {"group_by": "warehouse",}),
("Batch Item Expiry Status", {}),
("Stock Ageing", {"range1": 30, "range2": 60, "range3": 90, "_optional": True}),
+ ("Stock Ledger Invariant Check",
+ {
+ "warehouse": "_Test Warehouse - _TC",
+ "item": "_Test Item"
+ }
+ ),
]
OPTIONAL_FILTERS = {