feat: `Stock Ledger Variance` report (#37165)
* feat: `Stock Ledger Variance` report
* refactor: `get_data()`
diff --git a/erpnext/stock/report/stock_ledger_variance/__init__.py b/erpnext/stock/report/stock_ledger_variance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_variance/__init__.py
diff --git a/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.js b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.js
new file mode 100644
index 0000000..b1e4a74
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.js
@@ -0,0 +1,101 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+const DIFFERENCE_FIELD_NAMES = [
+ "difference_in_qty",
+ "fifo_qty_diff",
+ "fifo_value_diff",
+ "fifo_valuation_diff",
+ "valuation_diff",
+ "fifo_difference_diff",
+ "diff_value_diff"
+];
+
+frappe.query_reports["Stock Ledger Variance"] = {
+ "filters": [
+ {
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "label": "Item",
+ "options": "Item",
+ get_query: function() {
+ return {
+ filters: {is_stock_item: 1, has_serial_no: 0}
+ }
+ }
+ },
+ {
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "label": "Warehouse",
+ "options": "Warehouse",
+ get_query: function() {
+ return {
+ filters: {is_group: 0, disabled: 0}
+ }
+ }
+ },
+ {
+ "fieldname": "difference_in",
+ "fieldtype": "Select",
+ "label": "Difference In",
+ "options": [
+ "",
+ "Qty",
+ "Value",
+ "Valuation",
+ ],
+ },
+ {
+ "fieldname": "include_disabled",
+ "fieldtype": "Check",
+ "label": "Include Disabled",
+ }
+ ],
+
+ formatter (value, row, column, data, default_formatter) {
+ value = default_formatter(value, row, column, data);
+
+ if (DIFFERENCE_FIELD_NAMES.includes(column.fieldname) && Math.abs(data[column.fieldname]) > 0.001) {
+ value = "<span style='color:red'>" + value + "</span>";
+ }
+
+ return value;
+ },
+
+ get_datatable_options(options) {
+ return Object.assign(options, {
+ checkboxColumn: true,
+ });
+ },
+
+ onload(report) {
+ report.page.add_inner_button(__('Create Reposting Entries'), () => {
+ let message = `
+ <div>
+ <p>
+ Reposting Entries will change the value of
+ accounts Stock In Hand, and Stock Expenses
+ in the Trial Balance report and will also change
+ the Balance Value in the Stock Balance report.
+ </p>
+ <p>Are you sure you want to create Reposting Entries?</p>
+ </div>`;
+ let indexes = frappe.query_report.datatable.rowmanager.getCheckedRows();
+ let selected_rows = indexes.map(i => frappe.query_report.data[i]);
+
+ if (!selected_rows.length) {
+ frappe.throw(__("Please select rows to create Reposting Entries"));
+ }
+
+ frappe.confirm(__(message), () => {
+ frappe.call({
+ method: 'erpnext.stock.report.stock_ledger_invariant_check.stock_ledger_invariant_check.create_reposting_entries',
+ args: {
+ rows: selected_rows,
+ }
+ });
+ });
+ });
+ },
+};
diff --git a/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.json b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.json
new file mode 100644
index 0000000..f36ed1b
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.json
@@ -0,0 +1,22 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2023-09-20 10:44:19.414449",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "letterhead": null,
+ "modified": "2023-09-20 10:44:19.414449",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Stock Ledger Variance",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Stock Ledger Variance",
+ "report_type": "Script Report",
+ "roles": []
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.py b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.py
new file mode 100644
index 0000000..732f108
--- /dev/null
+++ b/erpnext/stock/report/stock_ledger_variance/stock_ledger_variance.py
@@ -0,0 +1,279 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe import _
+from frappe.utils import cint, flt
+
+from erpnext.stock.report.stock_ledger_invariant_check.stock_ledger_invariant_check import (
+ get_data as stock_ledger_invariant_check,
+)
+
+
+def execute(filters=None):
+ columns, data = [], []
+
+ filters = frappe._dict(filters or {})
+ columns = get_columns()
+ data = get_data(filters)
+
+ return columns, data
+
+
+def get_columns():
+ return [
+ {
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "label": _("Stock Ledger Entry"),
+ "options": "Stock Ledger Entry",
+ },
+ {
+ "fieldname": "posting_date",
+ "fieldtype": "Data",
+ "label": _("Posting Date"),
+ },
+ {
+ "fieldname": "posting_time",
+ "fieldtype": "Data",
+ "label": _("Posting Time"),
+ },
+ {
+ "fieldname": "creation",
+ "fieldtype": "Data",
+ "label": _("Creation"),
+ },
+ {
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "label": _("Item"),
+ "options": "Item",
+ },
+ {
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "label": _("Warehouse"),
+ "options": "Warehouse",
+ },
+ {
+ "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": "use_batchwise_valuation",
+ "fieldtype": "Check",
+ "label": _("Batchwise Valuation"),
+ },
+ {
+ "fieldname": "actual_qty",
+ "fieldtype": "Float",
+ "label": _("Qty Change"),
+ },
+ {
+ "fieldname": "incoming_rate",
+ "fieldtype": "Float",
+ "label": _("Incoming Rate"),
+ },
+ {
+ "fieldname": "consumption_rate",
+ "fieldtype": "Float",
+ "label": _("Consumption 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/LIFO 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) Change in Stock Value"),
+ },
+ {
+ "fieldname": "stock_value_from_diff",
+ "fieldtype": "Float",
+ "label": _("(G) Sum of Change in Stock Value"),
+ },
+ {
+ "fieldname": "diff_value_diff",
+ "fieldtype": "Float",
+ "label": _("G - D"),
+ },
+ {
+ "fieldname": "fifo_stock_diff",
+ "fieldtype": "Float",
+ "label": _("(H) Change in Stock Value (FIFO Queue)"),
+ },
+ {
+ "fieldname": "fifo_difference_diff",
+ "fieldtype": "Float",
+ "label": _("H - F"),
+ },
+ {
+ "fieldname": "valuation_rate",
+ "fieldtype": "Float",
+ "label": _("(I) Valuation Rate"),
+ },
+ {
+ "fieldname": "fifo_valuation_rate",
+ "fieldtype": "Float",
+ "label": _("(J) Valuation Rate as per FIFO"),
+ },
+ {
+ "fieldname": "fifo_valuation_diff",
+ "fieldtype": "Float",
+ "label": _("I - J"),
+ },
+ {
+ "fieldname": "balance_value_by_qty",
+ "fieldtype": "Float",
+ "label": _("(K) Valuation = Value (D) ÷ Qty (A)"),
+ },
+ {
+ "fieldname": "valuation_diff",
+ "fieldtype": "Float",
+ "label": _("I - K"),
+ },
+ ]
+
+
+def get_data(filters=None):
+ filters = frappe._dict(filters or {})
+ item_warehouse_map = get_item_warehouse_combinations(filters)
+
+ data = []
+ if item_warehouse_map:
+ precision = cint(frappe.db.get_single_value("System Settings", "float_precision"))
+
+ for item_warehouse in item_warehouse_map:
+ report_data = stock_ledger_invariant_check(item_warehouse)
+
+ if not report_data:
+ continue
+
+ for row in report_data:
+ if has_difference(row, precision, filters.difference_in):
+ data.append(add_item_warehouse_details(row, item_warehouse))
+ break
+
+ return data
+
+
+def get_item_warehouse_combinations(filters: dict = None) -> dict:
+ filters = frappe._dict(filters or {})
+
+ bin = frappe.qb.DocType("Bin")
+ item = frappe.qb.DocType("Item")
+ warehouse = frappe.qb.DocType("Warehouse")
+
+ query = (
+ frappe.qb.from_(bin)
+ .inner_join(item)
+ .on(bin.item_code == item.name)
+ .inner_join(warehouse)
+ .on(bin.warehouse == warehouse.name)
+ .select(
+ bin.item_code,
+ bin.warehouse,
+ )
+ .where((item.is_stock_item == 1) & (item.has_serial_no == 0) & (warehouse.is_group == 0))
+ )
+
+ if filters.item_code:
+ query = query.where(item.name == filters.item_code)
+ if filters.warehouse:
+ query = query.where(warehouse.name == filters.warehouse)
+ if not filters.include_disabled:
+ query = query.where((item.disabled == 0) & (warehouse.disabled == 0))
+
+ return query.run(as_dict=1)
+
+
+def has_difference(row, precision, difference_in):
+ has_qty_difference = flt(row.difference_in_qty, precision) or flt(row.fifo_qty_diff, precision)
+ has_value_difference = (
+ flt(row.diff_value_diff, precision)
+ or flt(row.fifo_value_diff, precision)
+ or flt(row.fifo_difference_diff, precision)
+ )
+ has_valuation_difference = flt(row.valuation_diff, precision) or flt(
+ row.fifo_valuation_diff, precision
+ )
+
+ if difference_in == "Qty" and has_qty_difference:
+ return True
+ elif difference_in == "Value" and has_value_difference:
+ return True
+ elif difference_in == "Valuation" and has_valuation_difference:
+ return True
+ elif difference_in not in ["Qty", "Value", "Valuation"] and (
+ has_qty_difference or has_value_difference or has_valuation_difference
+ ):
+ return True
+
+ return False
+
+
+def add_item_warehouse_details(row, item_warehouse):
+ row.update(
+ {
+ "item_code": item_warehouse.item_code,
+ "warehouse": item_warehouse.warehouse,
+ }
+ )
+
+ return row