Merge pull request #37211 from deepeshgarg007/trial_balance_net_values
feat: Toggle net values in Trial Balance report
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 794a4ef..0203c45 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -154,6 +154,12 @@
frm.events.set_dynamic_labels(frm);
frm.events.show_general_ledger(frm);
erpnext.accounts.ledger_preview.show_accounting_ledger_preview(frm);
+ if(frm.doc.references.find((elem) => {return elem.exchange_gain_loss != 0})) {
+ frm.add_custom_button(__("View Exchange Gain/Loss Journals"), function() {
+ frappe.set_route("List", "Journal Entry", {"voucher_type": "Exchange Gain Or Loss", "reference_name": frm.doc.name});
+ }, __('Actions'));
+
+ }
erpnext.accounts.unreconcile_payments.add_unreconcile_btn(frm);
},
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index b16b03e..38a5209 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -1152,8 +1152,25 @@
)
make_reverse_gl_entries(gl_entries=gl_entries, partial_cancel=True)
- else:
- make_gl_entries(gl_entries)
+ return
+
+ # same reference added to payment entry
+ for gl_entry in gl_entries.copy():
+ if frappe.db.exists(
+ "GL Entry",
+ {
+ "account": gl_entry.account,
+ "voucher_type": gl_entry.voucher_type,
+ "voucher_no": gl_entry.voucher_no,
+ "voucher_detail_no": gl_entry.voucher_detail_no,
+ "debit": gl_entry.debit,
+ "credit": gl_entry.credit,
+ "is_cancelled": 0,
+ },
+ ):
+ gl_entries.remove(gl_entry)
+
+ make_gl_entries(gl_entries)
def make_invoice_liability_entry(self, gl_entries, invoice):
args_dict = {
diff --git a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
index af1c066..d984d86 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -33,7 +33,7 @@
def on_cancel(self):
self.validate_future_closing_vouchers()
self.db_set("gle_processing_status", "In Progress")
- self.ignore_linked_doctypes = ("GL Entry", "Stock Ledger Entry")
+ self.ignore_linked_doctypes = ("GL Entry", "Stock Ledger Entry", "Payment Ledger Entry")
gle_count = frappe.db.count(
"GL Entry",
{"voucher_type": "Period Closing Voucher", "voucher_no": self.name, "is_cancelled": 0},
diff --git a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
index 7863103..9a5ad35 100644
--- a/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
+++ b/erpnext/accounts/doctype/process_statement_of_accounts/process_statement_of_accounts.py
@@ -65,6 +65,7 @@
filters = get_common_filters(doc)
if doc.report == "General Ledger":
+ filters.update(get_gl_filters(doc, entry, tax_id, presentation_currency))
col, res = get_soa(filters)
for x in [0, -2, -1]:
res[x]["account"] = res[x]["account"].replace("'", "")
diff --git a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
index 9ffdaf6..84b0149 100644
--- a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
@@ -1801,6 +1801,10 @@
)
def test_outstanding_amount_after_advance_payment_entry_cancellation(self):
+ """Test impact of advance PE submission/cancellation on SI and SO."""
+ from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+
+ sales_order = make_sales_order(item_code="138-CMS Shoe", qty=1, price_list_rate=500)
pe = frappe.get_doc(
{
"doctype": "Payment Entry",
@@ -1820,10 +1824,25 @@
"paid_to": "_Test Cash - _TC",
}
)
+ pe.append(
+ "references",
+ {
+ "reference_doctype": "Sales Order",
+ "reference_name": sales_order.name,
+ "total_amount": sales_order.grand_total,
+ "outstanding_amount": sales_order.grand_total,
+ "allocated_amount": 300,
+ },
+ )
pe.insert()
pe.submit()
+ sales_order.reload()
+ self.assertEqual(sales_order.advance_paid, 300)
+
si = frappe.copy_doc(test_records[0])
+ si.items[0].sales_order = sales_order.name
+ si.items[0].so_detail = sales_order.get("items")[0].name
si.is_pos = 0
si.append(
"advances",
@@ -1831,6 +1850,7 @@
"doctype": "Sales Invoice Advance",
"reference_type": "Payment Entry",
"reference_name": pe.name,
+ "reference_row": pe.references[0].name,
"advance_amount": 300,
"allocated_amount": 300,
"remarks": pe.remarks,
@@ -1839,7 +1859,13 @@
si.insert()
si.submit()
- si.load_from_db()
+ si.reload()
+ pe.reload()
+ sales_order.reload()
+
+ # Check if SO is unlinked/replaced by SI in PE & if SO advance paid is 0
+ self.assertEqual(pe.references[0].reference_name, si.name)
+ self.assertEqual(sales_order.advance_paid, 0.0)
# check outstanding after advance allocation
self.assertEqual(
@@ -1847,11 +1873,9 @@
flt(si.rounded_total - si.total_advance, si.precision("outstanding_amount")),
)
- # added to avoid Document has been modified exception
- pe = frappe.get_doc("Payment Entry", pe.name)
pe.cancel()
+ si.reload()
- si.load_from_db()
# check outstanding after advance cancellation
self.assertEqual(
flt(si.outstanding_amount),
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 1360f73..555ed4f 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -581,6 +581,10 @@
"""
jv_detail = journal_entry.get("accounts", {"name": d["voucher_detail_no"]})[0]
+ # Update Advance Paid in SO/PO since they might be getting unlinked
+ if jv_detail.get("reference_type") in ("Sales Order", "Purchase Order"):
+ frappe.get_doc(jv_detail.reference_type, jv_detail.reference_name).set_total_advance_paid()
+
if flt(d["unadjusted_amount"]) - flt(d["allocated_amount"]) != 0:
# adjust the unreconciled balance
amount_in_account_currency = flt(d["unadjusted_amount"]) - flt(d["allocated_amount"])
@@ -647,6 +651,13 @@
if d.voucher_detail_no:
existing_row = payment_entry.get("references", {"name": d["voucher_detail_no"]})[0]
+
+ # Update Advance Paid in SO/PO since they are getting unlinked
+ if existing_row.get("reference_doctype") in ("Sales Order", "Purchase Order"):
+ frappe.get_doc(
+ existing_row.reference_doctype, existing_row.reference_name
+ ).set_total_advance_paid()
+
original_row = existing_row.as_dict().copy()
existing_row.update(reference_details)
diff --git a/erpnext/controllers/buying_controller.py b/erpnext/controllers/buying_controller.py
index c302ece..a76abe2 100644
--- a/erpnext/controllers/buying_controller.py
+++ b/erpnext/controllers/buying_controller.py
@@ -418,6 +418,10 @@
item.bom = None
def set_qty_as_per_stock_uom(self):
+ allow_to_edit_stock_qty = frappe.db.get_single_value(
+ "Stock Settings", "allow_to_edit_stock_uom_qty_for_purchase"
+ )
+
for d in self.get("items"):
if d.meta.get_field("stock_qty"):
# Check if item code is present
@@ -432,6 +436,11 @@
d.conversion_factor, d.precision("conversion_factor")
)
+ if allow_to_edit_stock_qty:
+ d.stock_qty = flt(d.stock_qty, d.precision("stock_qty"))
+ if d.get("received_stock_qty"):
+ d.received_stock_qty = flt(d.received_stock_qty, d.precision("received_stock_qty"))
+
def validate_purchase_return(self):
for d in self.get("items"):
if self.is_return and flt(d.rejected_qty) != 0:
diff --git a/erpnext/controllers/selling_controller.py b/erpnext/controllers/selling_controller.py
index 9771f60..9014662 100644
--- a/erpnext/controllers/selling_controller.py
+++ b/erpnext/controllers/selling_controller.py
@@ -194,11 +194,17 @@
frappe.throw(_("Maximum discount for Item {0} is {1}%").format(d.item_code, discount))
def set_qty_as_per_stock_uom(self):
+ allow_to_edit_stock_qty = frappe.db.get_single_value(
+ "Stock Settings", "allow_to_edit_stock_uom_qty_for_sales"
+ )
+
for d in self.get("items"):
if d.meta.get_field("stock_qty"):
if not d.conversion_factor:
frappe.throw(_("Row {0}: Conversion Factor is mandatory").format(d.idx))
d.stock_qty = flt(d.qty) * flt(d.conversion_factor)
+ if allow_to_edit_stock_qty:
+ d.stock_qty = flt(d.stock_qty, d.precision("stock_qty"))
def validate_selling_price(self):
def throw_message(idx, item_name, rate, ref_rate_field):
diff --git a/erpnext/e_commerce/doctype/website_item/test_website_item.py b/erpnext/e_commerce/doctype/website_item/test_website_item.py
index 43b2f67..2ba84c0 100644
--- a/erpnext/e_commerce/doctype/website_item/test_website_item.py
+++ b/erpnext/e_commerce/doctype/website_item/test_website_item.py
@@ -312,7 +312,7 @@
# check if stock details are fetched and item not in stock with warehouse set
data = get_product_info_for_website(item_code, skip_quotation_creation=True)
self.assertFalse(bool(data.product_info["in_stock"]))
- self.assertEqual(data.product_info["stock_qty"][0][0], 0)
+ self.assertEqual(data.product_info["stock_qty"], 0)
# disable show stock availability
setup_e_commerce_settings({"show_stock_availability": 0})
@@ -355,7 +355,7 @@
# check if stock details are fetched and item is in stock with warehouse set
data = get_product_info_for_website(item_code, skip_quotation_creation=True)
self.assertTrue(bool(data.product_info["in_stock"]))
- self.assertEqual(data.product_info["stock_qty"][0][0], 2)
+ self.assertEqual(data.product_info["stock_qty"], 2)
# unset warehouse
frappe.db.set_value("Website Item", {"item_code": item_code}, "website_warehouse", "")
@@ -364,7 +364,7 @@
# (even though it has stock in some warehouse)
data = get_product_info_for_website(item_code, skip_quotation_creation=True)
self.assertFalse(bool(data.product_info["in_stock"]))
- self.assertFalse(bool(data.product_info["stock_qty"]))
+ self.assertFalse(data.product_info["stock_qty"])
# disable show stock availability
setup_e_commerce_settings({"show_stock_availability": 0})
diff --git a/erpnext/e_commerce/doctype/website_item/website_item.js b/erpnext/e_commerce/doctype/website_item/website_item.js
index 7b7193e..b6595cc 100644
--- a/erpnext/e_commerce/doctype/website_item/website_item.js
+++ b/erpnext/e_commerce/doctype/website_item/website_item.js
@@ -5,12 +5,6 @@
onload: (frm) => {
// should never check Private
frm.fields_dict["website_image"].df.is_private = 0;
-
- frm.set_query("website_warehouse", () => {
- return {
- filters: {"is_group": 0}
- };
- });
},
refresh: (frm) => {
diff --git a/erpnext/e_commerce/doctype/website_item/website_item.json b/erpnext/e_commerce/doctype/website_item/website_item.json
index 6556eab..6f551a0 100644
--- a/erpnext/e_commerce/doctype/website_item/website_item.json
+++ b/erpnext/e_commerce/doctype/website_item/website_item.json
@@ -135,7 +135,7 @@
"fieldtype": "Column Break"
},
{
- "description": "Show Stock availability based on this warehouse.",
+ "description": "Show Stock availability based on this warehouse. If the parent warehouse is selected, then the system will display the consolidated available quantity of all child warehouses.",
"fieldname": "website_warehouse",
"fieldtype": "Link",
"ignore_user_permissions": 1,
@@ -348,7 +348,7 @@
"index_web_pages_for_search": 1,
"links": [],
"make_attachments_public": 1,
- "modified": "2022-09-30 04:01:52.090732",
+ "modified": "2023-09-12 14:19:22.822689",
"modified_by": "Administrator",
"module": "E-commerce",
"name": "Website Item",
diff --git a/erpnext/e_commerce/product_data_engine/query.py b/erpnext/e_commerce/product_data_engine/query.py
index e6a595a..975f876 100644
--- a/erpnext/e_commerce/product_data_engine/query.py
+++ b/erpnext/e_commerce/product_data_engine/query.py
@@ -259,6 +259,10 @@
)
def get_stock_availability(self, item):
+ from erpnext.templates.pages.wishlist import (
+ get_stock_availability as get_stock_availability_from_template,
+ )
+
"""Modify item object and add stock details."""
item.in_stock = False
warehouse = item.get("website_warehouse")
@@ -274,11 +278,7 @@
else:
item.in_stock = True
elif warehouse:
- # stock item and has warehouse
- actual_qty = frappe.db.get_value(
- "Bin", {"item_code": item.item_code, "warehouse": item.get("website_warehouse")}, "actual_qty"
- )
- item.in_stock = bool(flt(actual_qty))
+ item.in_stock = get_stock_availability_from_template(item.item_code, warehouse)
def get_cart_items(self):
customer = get_customer(silent=True)
diff --git a/erpnext/e_commerce/shopping_cart/cart.py b/erpnext/e_commerce/shopping_cart/cart.py
index c66ae1d..7c7e169 100644
--- a/erpnext/e_commerce/shopping_cart/cart.py
+++ b/erpnext/e_commerce/shopping_cart/cart.py
@@ -111,8 +111,8 @@
item_stock = get_web_item_qty_in_stock(item.item_code, "website_warehouse")
if not cint(item_stock.in_stock):
throw(_("{0} Not in Stock").format(item.item_code))
- if item.qty > item_stock.stock_qty[0][0]:
- throw(_("Only {0} in Stock for item {1}").format(item_stock.stock_qty[0][0], item.item_code))
+ if item.qty > item_stock.stock_qty:
+ throw(_("Only {0} in Stock for item {1}").format(item_stock.stock_qty, item.item_code))
sales_order.flags.ignore_permissions = True
sales_order.insert()
@@ -150,6 +150,10 @@
empty_card = True
else:
+ warehouse = frappe.get_cached_value(
+ "Website Item", {"item_code": item_code}, "website_warehouse"
+ )
+
quotation_items = quotation.get("items", {"item_code": item_code})
if not quotation_items:
quotation.append(
@@ -159,11 +163,13 @@
"item_code": item_code,
"qty": qty,
"additional_notes": additional_notes,
+ "warehouse": warehouse,
},
)
else:
quotation_items[0].qty = qty
quotation_items[0].additional_notes = additional_notes
+ quotation_items[0].warehouse = warehouse
apply_cart_settings(quotation=quotation)
@@ -317,6 +323,10 @@
fields = fields[2:]
d.update(frappe.db.get_value("Website Item", {"item_code": item_code}, fields, as_dict=True))
+ website_warehouse = frappe.get_cached_value(
+ "Website Item", {"item_code": item_code}, "website_warehouse"
+ )
+ d.warehouse = website_warehouse
return doc
diff --git a/erpnext/e_commerce/variant_selector/utils.py b/erpnext/e_commerce/variant_selector/utils.py
index 4466c45..88356f5 100644
--- a/erpnext/e_commerce/variant_selector/utils.py
+++ b/erpnext/e_commerce/variant_selector/utils.py
@@ -104,6 +104,8 @@
@frappe.whitelist(allow_guest=True)
def get_next_attribute_and_values(item_code, selected_attributes):
+ from erpnext.stock.doctype.warehouse.warehouse import get_child_warehouses
+
"""Find the count of Items that match the selected attributes.
Also, find the attribute values that are not applicable for further searching.
If less than equal to 10 items are found, return item_codes of those items.
@@ -168,7 +170,7 @@
product_info = None
product_id = ""
- website_warehouse = ""
+ warehouse = ""
if exact_match or filtered_items:
if exact_match and len(exact_match) == 1:
product_id = exact_match[0]
@@ -176,16 +178,19 @@
product_id = list(filtered_items)[0]
if product_id:
- website_warehouse = frappe.get_cached_value(
+ warehouse = frappe.get_cached_value(
"Website Item", {"item_code": product_id}, "website_warehouse"
)
available_qty = 0.0
- if website_warehouse:
- available_qty = flt(
- frappe.db.get_value(
- "Bin", {"item_code": product_id, "warehouse": website_warehouse}, "actual_qty"
- )
+ if warehouse and frappe.get_cached_value("Warehouse", warehouse, "is_group") == 1:
+ warehouses = get_child_warehouses(warehouse)
+ else:
+ warehouses = [warehouse] if warehouse else []
+
+ for warehouse in warehouses:
+ available_qty += flt(
+ frappe.db.get_value("Bin", {"item_code": product_id, "warehouse": warehouse}, "actual_qty")
)
return {
diff --git a/erpnext/public/js/controllers/buying.js b/erpnext/public/js/controllers/buying.js
index 54f0aad..0860d9c 100644
--- a/erpnext/public/js/controllers/buying.js
+++ b/erpnext/public/js/controllers/buying.js
@@ -9,6 +9,7 @@
erpnext.buying.BuyingController = class BuyingController extends erpnext.TransactionController {
setup() {
super.setup();
+ this.toggle_enable_for_stock_uom("allow_to_edit_stock_uom_qty_for_purchase");
this.frm.email_field = "contact_email";
}
diff --git a/erpnext/public/js/controllers/transaction.js b/erpnext/public/js/controllers/transaction.js
index 975adc2..b0a9e40 100644
--- a/erpnext/public/js/controllers/transaction.js
+++ b/erpnext/public/js/controllers/transaction.js
@@ -224,6 +224,14 @@
}
}
+
+ toggle_enable_for_stock_uom(field) {
+ frappe.db.get_single_value('Stock Settings', field)
+ .then(value => {
+ this.frm.fields_dict["items"].grid.toggle_enable("stock_qty", value);
+ });
+ }
+
onload() {
var me = this;
@@ -1191,6 +1199,16 @@
]);
}
+ stock_qty(doc, cdt, cdn) {
+ let item = frappe.get_doc(cdt, cdn);
+ item.conversion_factor = 1.0;
+ if (item.stock_qty) {
+ item.conversion_factor = flt(item.stock_qty) / flt(item.qty);
+ }
+
+ refresh_field("conversion_factor", item.name, item.parentfield);
+ }
+
calculate_stock_uom_rate(doc, cdt, cdn) {
let item = frappe.get_doc(cdt, cdn);
item.stock_uom_rate = flt(item.rate)/flt(item.conversion_factor);
diff --git a/erpnext/public/js/utils/sales_common.js b/erpnext/public/js/utils/sales_common.js
index 89dcaa6..1d6daa5 100644
--- a/erpnext/public/js/utils/sales_common.js
+++ b/erpnext/public/js/utils/sales_common.js
@@ -8,6 +8,7 @@
erpnext.selling.SellingController = class SellingController extends erpnext.TransactionController {
setup() {
super.setup();
+ this.toggle_enable_for_stock_uom("allow_to_edit_stock_uom_qty_for_sales");
this.frm.email_field = "contact_email";
}
diff --git a/erpnext/public/js/utils/unreconcile.js b/erpnext/public/js/utils/unreconcile.js
index bbdd51d..fa00ed2 100644
--- a/erpnext/public/js/utils/unreconcile.js
+++ b/erpnext/public/js/utils/unreconcile.js
@@ -17,7 +17,7 @@
},
callback: function(r) {
if (r.message) {
- frm.add_custom_button(__("Un-Reconcile"), function() {
+ frm.add_custom_button(__("UnReconcile"), function() {
erpnext.accounts.unreconcile_payments.build_unreconcile_dialog(frm);
}, __('Actions'));
}
@@ -87,11 +87,11 @@
unreconcile_dialog_fields[0].get_data = function(){ return r.message};
let d = new frappe.ui.Dialog({
- title: 'Un-Reconcile Allocations',
+ title: 'UnReconcile Allocations',
fields: unreconcile_dialog_fields,
size: 'large',
cannot_add_rows: true,
- primary_action_label: 'Un-Reconcile',
+ primary_action_label: 'UnReconcile',
primary_action(values) {
let selected_allocations = values.allocations.filter(x=>x.__checked);
diff --git a/erpnext/stock/dashboard/item_dashboard.py b/erpnext/stock/dashboard/item_dashboard.py
index 5a8d84a..e638268 100644
--- a/erpnext/stock/dashboard/item_dashboard.py
+++ b/erpnext/stock/dashboard/item_dashboard.py
@@ -3,7 +3,7 @@
from frappe.utils import cint, flt
from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
- get_sre_reserved_qty_for_item_and_warehouse as get_reserved_stock,
+ get_sre_reserved_qty_for_items_and_warehouses as get_reserved_stock_details,
)
@@ -61,7 +61,10 @@
limit_page_length=21,
)
- sre_reserved_stock_details = get_reserved_stock(item_code, warehouse)
+ item_code_list = [item_code] if item_code else [i.item_code for i in items]
+ warehouse_list = [warehouse] if warehouse else [i.warehouse for i in items]
+
+ sre_reserved_stock_details = get_reserved_stock_details(item_code_list, warehouse_list)
precision = cint(frappe.db.get_single_value("System Settings", "float_precision"))
for item in items:
@@ -75,7 +78,8 @@
"reserved_qty_for_production": flt(item.reserved_qty_for_production, precision),
"reserved_qty_for_sub_contract": flt(item.reserved_qty_for_sub_contract, precision),
"actual_qty": flt(item.actual_qty, precision),
- "reserved_stock": sre_reserved_stock_details,
+ "reserved_stock": flt(sre_reserved_stock_details.get((item.item_code, item.warehouse))),
}
)
+
return items
diff --git a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
index d31fec5..5eb3656 100644
--- a/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
+++ b/erpnext/stock/doctype/purchase_receipt_item/purchase_receipt_item.json
@@ -34,8 +34,8 @@
"sample_quantity",
"tracking_section",
"received_stock_qty",
- "stock_qty",
"col_break_tracking_section",
+ "stock_qty",
"returned_qty",
"rate_and_amount",
"price_list_rate",
@@ -858,7 +858,8 @@
},
{
"fieldname": "tracking_section",
- "fieldtype": "Section Break"
+ "fieldtype": "Section Break",
+ "label": "Qty as Per Stock UOM"
},
{
"fieldname": "col_break_tracking_section",
@@ -1060,7 +1061,7 @@
"idx": 1,
"istable": 1,
"links": [],
- "modified": "2023-07-26 12:55:15.234477",
+ "modified": "2023-08-11 16:16:16.504549",
"modified_by": "Administrator",
"module": "Stock",
"name": "Purchase Receipt Item",
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 26ca012..e36d576 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -346,7 +346,7 @@
"""Raises an exception if there is any reserved stock for the items in the Stock Reconciliation."""
from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
- get_sre_reserved_qty_for_item_and_warehouse as get_sre_reserved_qty_details,
+ get_sre_reserved_qty_for_items_and_warehouses as get_sre_reserved_qty_details,
)
item_code_list, warehouse_list = [], []
diff --git a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.js b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.js
index 4d96636..c5df319 100644
--- a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.js
+++ b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.js
@@ -1,42 +1,42 @@
// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
// For license information, please see license.txt
-frappe.ui.form.on("Stock Reservation Entry", {
+frappe.ui.form.on('Stock Reservation Entry', {
refresh(frm) {
- frm.trigger("set_queries");
- frm.trigger("toggle_read_only_fields");
- frm.trigger("hide_rate_related_fields");
- frm.trigger("hide_primary_action_button");
- frm.trigger("make_sb_entries_warehouse_read_only");
+ frm.trigger('set_queries');
+ frm.trigger('toggle_read_only_fields');
+ frm.trigger('hide_rate_related_fields');
+ frm.trigger('hide_primary_action_button');
+ frm.trigger('make_sb_entries_warehouse_read_only');
},
has_serial_no(frm) {
- frm.trigger("toggle_read_only_fields");
+ frm.trigger('toggle_read_only_fields');
},
has_batch_no(frm) {
- frm.trigger("toggle_read_only_fields");
+ frm.trigger('toggle_read_only_fields');
},
warehouse(frm) {
if (frm.doc.warehouse) {
frm.doc.sb_entries.forEach((row) => {
- frappe.model.set_value(row.doctype, row.name, "warehouse", frm.doc.warehouse);
+ frappe.model.set_value(row.doctype, row.name, 'warehouse', frm.doc.warehouse);
});
}
},
set_queries(frm) {
- frm.set_query("warehouse", () => {
+ frm.set_query('warehouse', () => {
return {
filters: {
- "is_group": 0,
- "company": frm.doc.company,
+ 'is_group': 0,
+ 'company': frm.doc.company,
}
};
});
- frm.set_query("serial_no", "sb_entries", function(doc, cdt, cdn) {
+ frm.set_query('serial_no', 'sb_entries', function(doc, cdt, cdn) {
var selected_serial_nos = doc.sb_entries.map(row => {
return row.serial_no;
});
@@ -45,16 +45,16 @@
filters: {
item_code: doc.item_code,
warehouse: row.warehouse,
- status: "Active",
- name: ["not in", selected_serial_nos],
+ status: 'Active',
+ name: ['not in', selected_serial_nos],
}
}
});
- frm.set_query("batch_no", "sb_entries", function(doc, cdt, cdn) {
+ frm.set_query('batch_no', 'sb_entries', function(doc, cdt, cdn) {
let filters = {
item: doc.item_code,
- batch_qty: [">", 0],
+ batch_qty: ['>', 0],
disabled: 0,
}
@@ -63,7 +63,7 @@
return row.batch_no;
});
- filters.name = ["not in", selected_batch_nos];
+ filters.name = ['not in', selected_batch_nos];
}
return { filters: filters }
@@ -74,37 +74,37 @@
if (frm.doc.has_serial_no) {
frm.doc.sb_entries.forEach(row => {
if (row.qty !== 1) {
- frappe.model.set_value(row.doctype, row.name, "qty", 1);
+ frappe.model.set_value(row.doctype, row.name, 'qty', 1);
}
})
}
frm.fields_dict.sb_entries.grid.update_docfield_property(
- "serial_no", "read_only", !frm.doc.has_serial_no
+ 'serial_no', 'read_only', !frm.doc.has_serial_no
);
frm.fields_dict.sb_entries.grid.update_docfield_property(
- "batch_no", "read_only", !frm.doc.has_batch_no
+ 'batch_no', 'read_only', !frm.doc.has_batch_no
);
// Qty will always be 1 for Serial No.
frm.fields_dict.sb_entries.grid.update_docfield_property(
- "qty", "read_only", frm.doc.has_serial_no
+ 'qty', 'read_only', frm.doc.has_serial_no
);
- frm.set_df_property("sb_entries", "allow_on_submit", frm.doc.against_pick_list ? 0 : 1);
+ frm.set_df_property('sb_entries', 'allow_on_submit', frm.doc.against_pick_list ? 0 : 1);
},
hide_rate_related_fields(frm) {
- ["incoming_rate", "outgoing_rate", "stock_value_difference", "is_outward", "stock_queue"].forEach(field => {
+ ['incoming_rate', 'outgoing_rate', 'stock_value_difference', 'is_outward', 'stock_queue'].forEach(field => {
frm.fields_dict.sb_entries.grid.update_docfield_property(
- field, "hidden", 1
+ field, 'hidden', 1
);
});
},
hide_primary_action_button(frm) {
- // Hide "Amend" button on cancelled document
+ // Hide 'Amend' button on cancelled document
if (frm.doc.docstatus == 2) {
frm.page.btn_primary.hide()
}
@@ -112,15 +112,15 @@
make_sb_entries_warehouse_read_only(frm) {
frm.fields_dict.sb_entries.grid.update_docfield_property(
- "warehouse", "read_only", 1
+ 'warehouse', 'read_only', 1
);
},
});
-frappe.ui.form.on("Serial and Batch Entry", {
+frappe.ui.form.on('Serial and Batch Entry', {
sb_entries_add(frm, cdt, cdn) {
if (frm.doc.warehouse) {
- frappe.model.set_value(cdt, cdn, "warehouse", frm.doc.warehouse);
+ frappe.model.set_value(cdt, cdn, 'warehouse', frm.doc.warehouse);
}
},
});
\ No newline at end of file
diff --git a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
index bd7bb66..936be3f 100644
--- a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
+++ b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry.py
@@ -14,7 +14,7 @@
self.validate_amended_doc()
self.validate_mandatory()
- self.validate_for_group_warehouse()
+ self.validate_group_warehouse()
validate_disabled_warehouse(self.warehouse)
validate_warehouse_company(self.warehouse, self.company)
self.validate_uom_is_integer()
@@ -74,7 +74,7 @@
msg = _("{0} is required").format(self.meta.get_label(d))
frappe.throw(msg)
- def validate_for_group_warehouse(self) -> None:
+ def validate_group_warehouse(self) -> None:
"""Raises an exception if `Warehouse` is a Group Warehouse."""
if frappe.get_cached_value("Warehouse", self.warehouse, "is_group"):
@@ -544,10 +544,36 @@
return available_serial_nos_list
-def get_sre_reserved_qty_for_item_and_warehouse(
- item_code: str | list, warehouse: str | list = None
-) -> float | dict:
- """Returns `Reserved Qty` for Item and Warehouse combination OR a dict like {("item_code", "warehouse"): "reserved_qty", ... }."""
+def get_sre_reserved_qty_for_item_and_warehouse(item_code: str, warehouse: str = None) -> float:
+ """Returns current `Reserved Qty` for Item and Warehouse combination."""
+
+ sre = frappe.qb.DocType("Stock Reservation Entry")
+ query = (
+ frappe.qb.from_(sre)
+ .select(Sum(sre.reserved_qty - sre.delivered_qty).as_("reserved_qty"))
+ .where(
+ (sre.docstatus == 1)
+ & (sre.item_code == item_code)
+ & (sre.status.notin(["Delivered", "Cancelled"]))
+ )
+ .groupby(sre.item_code, sre.warehouse)
+ )
+
+ if warehouse:
+ query = query.where(sre.warehouse == warehouse)
+
+ reserved_qty = query.run(as_list=True)
+
+ return flt(reserved_qty[0][0]) if reserved_qty else 0.0
+
+
+def get_sre_reserved_qty_for_items_and_warehouses(
+ item_code_list: list, warehouse_list: list = None
+) -> dict:
+ """Returns a dict like {("item_code", "warehouse"): "reserved_qty", ... }."""
+
+ if not item_code_list:
+ return {}
sre = frappe.qb.DocType("Stock Reservation Entry")
query = (
@@ -557,29 +583,20 @@
sre.warehouse,
Sum(sre.reserved_qty - sre.delivered_qty).as_("reserved_qty"),
)
- .where((sre.docstatus == 1) & (sre.status.notin(["Delivered", "Cancelled"])))
+ .where(
+ (sre.docstatus == 1)
+ & sre.item_code.isin(item_code_list)
+ & (sre.status.notin(["Delivered", "Cancelled"]))
+ )
.groupby(sre.item_code, sre.warehouse)
)
- query = (
- query.where(sre.item_code.isin(item_code))
- if isinstance(item_code, list)
- else query.where(sre.item_code == item_code)
- )
-
- if warehouse:
- query = (
- query.where(sre.warehouse.isin(warehouse))
- if isinstance(warehouse, list)
- else query.where(sre.warehouse == warehouse)
- )
+ if warehouse_list:
+ query = query.where(sre.warehouse.isin(warehouse_list))
data = query.run(as_dict=True)
- if isinstance(item_code, str) and isinstance(warehouse, str):
- return data[0]["reserved_qty"] if data else 0.0
- else:
- return {(d["item_code"], d["warehouse"]): d["reserved_qty"] for d in data} if data else {}
+ return {(d["item_code"], d["warehouse"]): d["reserved_qty"] for d in data} if data else {}
def get_sre_reserved_qty_details_for_voucher(voucher_type: str, voucher_no: str) -> dict:
@@ -711,7 +728,7 @@
).run(as_dict=True)
-def get_ssb_bundle_for_voucher(sre: dict) -> object | None:
+def get_ssb_bundle_for_voucher(sre: dict) -> object:
"""Returns a new `Serial and Batch Bundle` against the provided SRE."""
sb_entries = get_serial_batch_entries_for_voucher(sre["name"])
diff --git a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry_list.js b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry_list.js
index 442ac39..5b390f7 100644
--- a/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry_list.js
+++ b/erpnext/stock/doctype/stock_reservation_entry/stock_reservation_entry_list.js
@@ -4,13 +4,14 @@
frappe.listview_settings['Stock Reservation Entry'] = {
get_indicator: function (doc) {
const status_colors = {
- 'Draft': 'red',
- 'Partially Reserved': 'orange',
- 'Reserved': 'blue',
- 'Partially Delivered': 'purple',
- 'Delivered': 'green',
- 'Cancelled': 'red',
+ 'Draft': 'red',
+ 'Partially Reserved': 'orange',
+ 'Reserved': 'blue',
+ 'Partially Delivered': 'purple',
+ 'Delivered': 'green',
+ 'Cancelled': 'red',
};
- return [__(doc.status), status_colors[doc.status], 'status,=,' + doc.status];
+
+ return [__(doc.status), status_colors[doc.status], 'status,=,' + doc.status];
},
};
\ No newline at end of file
diff --git a/erpnext/stock/doctype/stock_settings/stock_settings.json b/erpnext/stock/doctype/stock_settings/stock_settings.json
index 88b5575..4fbc0eb 100644
--- a/erpnext/stock/doctype/stock_settings/stock_settings.json
+++ b/erpnext/stock/doctype/stock_settings/stock_settings.json
@@ -18,6 +18,10 @@
"auto_insert_price_list_rate_if_missing",
"column_break_12",
"update_existing_price_list_rate",
+ "conversion_factor_section",
+ "allow_to_edit_stock_uom_qty_for_sales",
+ "column_break_lznj",
+ "allow_to_edit_stock_uom_qty_for_purchase",
"stock_validations_tab",
"section_break_9",
"over_delivery_receipt_allowance",
@@ -358,10 +362,6 @@
"label": "Allow Partial Reservation"
},
{
- "fieldname": "section_break_plhx",
- "fieldtype": "Section Break"
- },
- {
"fieldname": "column_break_mhzc",
"fieldtype": "Column Break"
},
@@ -400,6 +400,27 @@
"fieldname": "auto_reserve_stock_for_sales_order",
"fieldtype": "Check",
"label": "Auto Reserve Stock for Sales Order"
+ },
+ {
+ "fieldname": "conversion_factor_section",
+ "fieldtype": "Section Break",
+ "label": "Stock UOM Quantity"
+ },
+ {
+ "fieldname": "column_break_lznj",
+ "fieldtype": "Column Break"
+ },
+ {
+ "default": "0",
+ "fieldname": "allow_to_edit_stock_uom_qty_for_sales",
+ "fieldtype": "Check",
+ "label": "Allow to Edit Stock UOM Qty for Sales Documents"
+ },
+ {
+ "default": "0",
+ "fieldname": "allow_to_edit_stock_uom_qty_for_purchase",
+ "fieldtype": "Check",
+ "label": "Allow to Edit Stock UOM Qty for Purchase Documents"
}
],
"icon": "icon-cog",
diff --git a/erpnext/stock/doctype/stock_settings/stock_settings.py b/erpnext/stock/doctype/stock_settings/stock_settings.py
index 9ad3c9d..c7afb10 100644
--- a/erpnext/stock/doctype/stock_settings/stock_settings.py
+++ b/erpnext/stock/doctype/stock_settings/stock_settings.py
@@ -56,6 +56,8 @@
self.validate_clean_description_html()
self.validate_pending_reposts()
self.validate_stock_reservation()
+ self.change_precision_for_for_sales()
+ self.change_precision_for_purchase()
def validate_warehouses(self):
warehouse_fields = ["default_warehouse", "sample_retention_warehouse"]
@@ -167,6 +169,56 @@
def on_update(self):
self.toggle_warehouse_field_for_inter_warehouse_transfer()
+ def change_precision_for_for_sales(self):
+ doc_before_save = self.get_doc_before_save()
+ if doc_before_save and (
+ doc_before_save.allow_to_edit_stock_uom_qty_for_sales
+ == self.allow_to_edit_stock_uom_qty_for_sales
+ ):
+ return
+
+ if self.allow_to_edit_stock_uom_qty_for_sales:
+ doctypes = ["Sales Order Item", "Sales Invoice Item", "Delivery Note Item", "Quotation Item"]
+ self.make_property_setter_for_precision(doctypes)
+
+ def change_precision_for_purchase(self):
+ doc_before_save = self.get_doc_before_save()
+ if doc_before_save and (
+ doc_before_save.allow_to_edit_stock_uom_qty_for_purchase
+ == self.allow_to_edit_stock_uom_qty_for_purchase
+ ):
+ return
+
+ if self.allow_to_edit_stock_uom_qty_for_purchase:
+ doctypes = [
+ "Purchase Order Item",
+ "Purchase Receipt Item",
+ "Purchase Invoice Item",
+ "Request for Quotation Item",
+ "Supplier Quotation Item",
+ "Material Request Item",
+ ]
+ self.make_property_setter_for_precision(doctypes)
+
+ @staticmethod
+ def make_property_setter_for_precision(doctypes):
+ for doctype in doctypes:
+ if property_name := frappe.db.exists(
+ "Property Setter",
+ {"doc_type": doctype, "field_name": "conversion_factor", "property": "precision"},
+ ):
+ frappe.db.set_value("Property Setter", property_name, "value", 9)
+ continue
+
+ make_property_setter(
+ doctype,
+ "conversion_factor",
+ "precision",
+ 9,
+ "Float",
+ validate_fields_for_doctype=False,
+ )
+
def toggle_warehouse_field_for_inter_warehouse_transfer(self):
make_property_setter(
"Sales Invoice Item",
diff --git a/erpnext/stock/get_item_details.py b/erpnext/stock/get_item_details.py
index 79e6488..a6ab63b 100644
--- a/erpnext/stock/get_item_details.py
+++ b/erpnext/stock/get_item_details.py
@@ -1292,6 +1292,9 @@
@frappe.whitelist()
def get_valuation_rate(item_code, company, warehouse=None):
+ if frappe.get_cached_value("Warehouse", warehouse, "is_group"):
+ return {"valuation_rate": 0.0}
+
item = get_item_defaults(item_code, company)
item_group = get_item_group_defaults(item_code, company)
brand = get_brand_defaults(item_code, company)
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 337b0ea..a59f9de 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -165,7 +165,7 @@
def get_sre_reserved_qty_details(self) -> dict:
from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
- get_sre_reserved_qty_for_item_and_warehouse as get_reserved_qty_details,
+ get_sre_reserved_qty_for_items_and_warehouses as get_reserved_qty_details,
)
item_code_list, warehouse_list = [], []
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
index 36e91e3..3f67bff 100644
--- 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
@@ -2,7 +2,7 @@
// For license information, please see license.txt
-const DIFFERNCE_FIELD_NAMES = [
+const DIFFERENCE_FIELD_NAMES = [
'difference_in_qty',
'fifo_qty_diff',
'fifo_value_diff',
@@ -37,7 +37,7 @@
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) {
+ if (DIFFERENCE_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.py b/erpnext/stock/report/stock_ledger_invariant_check/stock_ledger_invariant_check.py
index e11c9bb..ca15afe 100644
--- 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
@@ -186,7 +186,7 @@
{
"fieldname": "fifo_queue_qty",
"fieldtype": "Float",
- "label": _("(C) Total qty in queue"),
+ "label": _("(C) Total Qty in Queue"),
},
{
"fieldname": "fifo_qty_diff",
@@ -211,52 +211,52 @@
{
"fieldname": "stock_value_difference",
"fieldtype": "Float",
- "label": _("(F) Stock Value Difference"),
+ "label": _("(F) Change in Stock Value"),
},
{
"fieldname": "stock_value_from_diff",
"fieldtype": "Float",
- "label": _("Balance Stock Value using (F)"),
+ "label": _("(G) Sum of Change in Stock Value"),
},
{
"fieldname": "diff_value_diff",
"fieldtype": "Float",
- "label": _("K - D"),
+ "label": _("G - D"),
},
{
"fieldname": "fifo_stock_diff",
"fieldtype": "Float",
- "label": _("(G) Stock Value difference (FIFO queue)"),
+ "label": _("(H) Change in Stock Value (FIFO Queue)"),
},
{
"fieldname": "fifo_difference_diff",
"fieldtype": "Float",
- "label": _("F - G"),
+ "label": _("H - F"),
},
{
"fieldname": "valuation_rate",
"fieldtype": "Float",
- "label": _("(H) Valuation Rate"),
+ "label": _("(I) Valuation Rate"),
},
{
"fieldname": "fifo_valuation_rate",
"fieldtype": "Float",
- "label": _("(I) Valuation Rate as per FIFO"),
+ "label": _("(J) Valuation Rate as per FIFO"),
},
{
"fieldname": "fifo_valuation_diff",
"fieldtype": "Float",
- "label": _("H - I"),
+ "label": _("I - J"),
},
{
"fieldname": "balance_value_by_qty",
"fieldtype": "Float",
- "label": _("(J) Valuation = Value (D) ÷ Qty (A)"),
+ "label": _("(K) Valuation = Value (D) ÷ Qty (A)"),
},
{
"fieldname": "valuation_diff",
"fieldtype": "Float",
- "label": _("H - J"),
+ "label": _("I - K"),
},
]
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
diff --git a/erpnext/templates/generators/item/item_add_to_cart.html b/erpnext/templates/generators/item/item_add_to_cart.html
index 1381dfe..9bd3f75 100644
--- a/erpnext/templates/generators/item/item_add_to_cart.html
+++ b/erpnext/templates/generators/item/item_add_to_cart.html
@@ -49,7 +49,7 @@
<span class="in-green has-stock">
{{ _('In stock') }}
{% if product_info.show_stock_qty and product_info.stock_qty %}
- ({{ product_info.stock_qty[0][0] }})
+ ({{ product_info.stock_qty }})
{% endif %}
</span>
{% endif %}
diff --git a/erpnext/templates/pages/wishlist.py b/erpnext/templates/pages/wishlist.py
index d70f27c..17607e4 100644
--- a/erpnext/templates/pages/wishlist.py
+++ b/erpnext/templates/pages/wishlist.py
@@ -25,9 +25,19 @@
def get_stock_availability(item_code, warehouse):
- stock_qty = frappe.utils.flt(
- frappe.db.get_value("Bin", {"item_code": item_code, "warehouse": warehouse}, "actual_qty")
- )
+ from erpnext.stock.doctype.warehouse.warehouse import get_child_warehouses
+
+ if warehouse and frappe.get_cached_value("Warehouse", warehouse, "is_group") == 1:
+ warehouses = get_child_warehouses(warehouse)
+ else:
+ warehouses = [warehouse] if warehouse else []
+
+ stock_qty = 0.0
+ for warehouse in warehouses:
+ stock_qty += frappe.utils.flt(
+ frappe.db.get_value("Bin", {"item_code": item_code, "warehouse": warehouse}, "actual_qty")
+ )
+
return bool(stock_qty)
diff --git a/erpnext/utilities/product.py b/erpnext/utilities/product.py
index afe9654..e967f70 100644
--- a/erpnext/utilities/product.py
+++ b/erpnext/utilities/product.py
@@ -6,6 +6,7 @@
from erpnext.accounts.doctype.pricing_rule.pricing_rule import get_pricing_rule_for_item
from erpnext.stock.doctype.batch.batch import get_batch_qty
+from erpnext.stock.doctype.warehouse.warehouse import get_child_warehouses
def get_web_item_qty_in_stock(item_code, item_warehouse_field, warehouse=None):
@@ -22,23 +23,31 @@
"Website Item", {"item_code": template_item_code}, item_warehouse_field
)
- if warehouse:
- stock_qty = frappe.db.sql(
- """
- select GREATEST(S.actual_qty - S.reserved_qty - S.reserved_qty_for_production - S.reserved_qty_for_sub_contract, 0) / IFNULL(C.conversion_factor, 1)
- from tabBin S
- inner join `tabItem` I on S.item_code = I.Item_code
- left join `tabUOM Conversion Detail` C on I.sales_uom = C.uom and C.parent = I.Item_code
- where S.item_code=%s and S.warehouse=%s""",
- (item_code, warehouse),
- )
+ if warehouse and frappe.get_cached_value("Warehouse", warehouse, "is_group") == 1:
+ warehouses = get_child_warehouses(warehouse)
+ else:
+ warehouses = [warehouse] if warehouse else []
- if stock_qty:
- stock_qty = adjust_qty_for_expired_items(item_code, stock_qty, warehouse)
- in_stock = stock_qty[0][0] > 0 and 1 or 0
+ total_stock = 0.0
+ if warehouses:
+ for warehouse in warehouses:
+ stock_qty = frappe.db.sql(
+ """
+ select GREATEST(S.actual_qty - S.reserved_qty - S.reserved_qty_for_production - S.reserved_qty_for_sub_contract, 0) / IFNULL(C.conversion_factor, 1)
+ from tabBin S
+ inner join `tabItem` I on S.item_code = I.Item_code
+ left join `tabUOM Conversion Detail` C on I.sales_uom = C.uom and C.parent = I.Item_code
+ where S.item_code=%s and S.warehouse=%s""",
+ (item_code, warehouse),
+ )
+
+ if stock_qty:
+ total_stock += adjust_qty_for_expired_items(item_code, stock_qty, warehouse)
+
+ in_stock = total_stock > 0 and 1 or 0
return frappe._dict(
- {"in_stock": in_stock, "stock_qty": stock_qty, "is_stock_item": is_stock_item}
+ {"in_stock": in_stock, "stock_qty": total_stock, "is_stock_item": is_stock_item}
)
@@ -56,7 +65,7 @@
if not stock_qty[0][0]:
break
- return stock_qty
+ return stock_qty[0][0] if stock_qty else 0
def get_expired_batches(batches):