Merge pull request #35417 from ruthra-kumar/fix_ambiguous_company_in_gross_profit_report
fix(Gross Profit): 'company' column is ambiguous in filter
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index e16b1b1..7454332 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -1180,7 +1180,12 @@
if self.is_return:
fixed_asset_gl_entries = get_gl_entries_on_asset_regain(
- asset, item.base_net_amount, item.finance_book, self.get("doctype"), self.get("name")
+ asset,
+ item.base_net_amount,
+ item.finance_book,
+ self.get("doctype"),
+ self.get("name"),
+ self.get("posting_date"),
)
asset.db_set("disposal_date", None)
@@ -1208,7 +1213,12 @@
asset.reload()
fixed_asset_gl_entries = get_gl_entries_on_asset_disposal(
- asset, item.base_net_amount, item.finance_book, self.get("doctype"), self.get("name")
+ asset,
+ item.base_net_amount,
+ item.finance_book,
+ self.get("doctype"),
+ self.get("name"),
+ self.get("posting_date"),
)
asset.db_set("disposal_date", self.posting_date)
diff --git a/erpnext/accounts/form_tour/sales_invoice/sales_invoice.json b/erpnext/accounts/form_tour/sales_invoice/sales_invoice.json
new file mode 100644
index 0000000..414b897
--- /dev/null
+++ b/erpnext/accounts/form_tour/sales_invoice/sales_invoice.json
@@ -0,0 +1,41 @@
+{
+ "creation": "2023-05-23 09:58:17.235916",
+ "docstatus": 0,
+ "doctype": "Form Tour",
+ "first_document": 0,
+ "idx": 0,
+ "include_name_field": 0,
+ "is_standard": 1,
+ "modified": "2023-05-23 13:10:56.227127",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Sales Invoice",
+ "owner": "Administrator",
+ "reference_doctype": "Sales Invoice",
+ "save_on_complete": 1,
+ "steps": [
+ {
+ "description": "Select a customer for whom this invoice is being prepared.",
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "has_next_condition": 1,
+ "is_table_field": 0,
+ "label": "Customer",
+ "next_step_condition": "eval: doc.customer",
+ "position": "Right",
+ "title": "Select Customer"
+ },
+ {
+ "child_doctype": "Sales Invoice Item",
+ "description": "Select item that you have sold along with quantity and rate.",
+ "fieldname": "items",
+ "fieldtype": "Table",
+ "has_next_condition": 0,
+ "is_table_field": 0,
+ "parent_fieldname": "items",
+ "position": "Top",
+ "title": "Select Item"
+ }
+ ],
+ "title": "Sales Invoice"
+}
\ No newline at end of file
diff --git a/erpnext/assets/doctype/asset/depreciation.py b/erpnext/assets/doctype/asset/depreciation.py
index f23ae2f..c64b917 100644
--- a/erpnext/assets/doctype/asset/depreciation.py
+++ b/erpnext/assets/doctype/asset/depreciation.py
@@ -307,7 +307,7 @@
je.company = asset.company
je.remark = "Scrap Entry for asset {0}".format(asset_name)
- for entry in get_gl_entries_on_asset_disposal(asset):
+ for entry in get_gl_entries_on_asset_disposal(asset, date):
entry.update({"reference_type": "Asset", "reference_name": asset_name})
je.append("accounts", entry)
@@ -434,8 +434,11 @@
def get_gl_entries_on_asset_regain(
- asset, selling_amount=0, finance_book=None, voucher_type=None, voucher_no=None
+ asset, selling_amount=0, finance_book=None, voucher_type=None, voucher_no=None, date=None
):
+ if not date:
+ date = getdate()
+
(
fixed_asset_account,
asset,
@@ -453,7 +456,7 @@
"debit_in_account_currency": asset.gross_purchase_amount,
"debit": asset.gross_purchase_amount,
"cost_center": depreciation_cost_center,
- "posting_date": getdate(),
+ "posting_date": date,
},
item=asset,
),
@@ -463,7 +466,7 @@
"credit_in_account_currency": accumulated_depr_amount,
"credit": accumulated_depr_amount,
"cost_center": depreciation_cost_center,
- "posting_date": getdate(),
+ "posting_date": date,
},
item=asset,
),
@@ -472,7 +475,7 @@
profit_amount = abs(flt(value_after_depreciation)) - abs(flt(selling_amount))
if profit_amount:
get_profit_gl_entries(
- asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center
+ asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center, date
)
if voucher_type and voucher_no:
@@ -484,8 +487,11 @@
def get_gl_entries_on_asset_disposal(
- asset, selling_amount=0, finance_book=None, voucher_type=None, voucher_no=None
+ asset, selling_amount=0, finance_book=None, voucher_type=None, voucher_no=None, date=None
):
+ if not date:
+ date = getdate()
+
(
fixed_asset_account,
asset,
@@ -503,7 +509,7 @@
"credit_in_account_currency": asset.gross_purchase_amount,
"credit": asset.gross_purchase_amount,
"cost_center": depreciation_cost_center,
- "posting_date": getdate(),
+ "posting_date": date,
},
item=asset,
),
@@ -513,7 +519,7 @@
"debit_in_account_currency": accumulated_depr_amount,
"debit": accumulated_depr_amount,
"cost_center": depreciation_cost_center,
- "posting_date": getdate(),
+ "posting_date": date,
},
item=asset,
),
@@ -522,7 +528,7 @@
profit_amount = flt(selling_amount) - flt(value_after_depreciation)
if profit_amount:
get_profit_gl_entries(
- asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center
+ asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center, date
)
if voucher_type and voucher_no:
@@ -556,8 +562,11 @@
def get_profit_gl_entries(
- asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center
+ asset, profit_amount, gl_entries, disposal_account, depreciation_cost_center, date=None
):
+ if not date:
+ date = getdate()
+
debit_or_credit = "debit" if profit_amount < 0 else "credit"
gl_entries.append(
asset.get_gl_dict(
@@ -566,7 +575,7 @@
"cost_center": depreciation_cost_center,
debit_or_credit: abs(profit_amount),
debit_or_credit + "_in_account_currency": abs(profit_amount),
- "posting_date": getdate(),
+ "posting_date": date,
},
item=asset,
)
diff --git a/erpnext/assets/doctype/asset/test_asset.py b/erpnext/assets/doctype/asset/test_asset.py
index f3a9ba0..c64f296 100644
--- a/erpnext/assets/doctype/asset/test_asset.py
+++ b/erpnext/assets/doctype/asset/test_asset.py
@@ -356,6 +356,83 @@
si.cancel()
self.assertEqual(frappe.db.get_value("Asset", asset.name, "status"), "Partially Depreciated")
+ def test_gle_made_by_asset_sale_for_existing_asset(self):
+ from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+
+ asset = create_asset(
+ calculate_depreciation=1,
+ available_for_use_date="2020-04-01",
+ purchase_date="2020-04-01",
+ expected_value_after_useful_life=0,
+ total_number_of_depreciations=5,
+ number_of_depreciations_booked=2,
+ frequency_of_depreciation=12,
+ depreciation_start_date="2023-03-31",
+ opening_accumulated_depreciation=24000,
+ gross_purchase_amount=60000,
+ submit=1,
+ )
+
+ expected_depr_values = [
+ ["2023-03-31", 12000, 36000],
+ ["2024-03-31", 12000, 48000],
+ ["2025-03-31", 12000, 60000],
+ ]
+
+ first_asset_depr_schedule = get_depr_schedule(asset.name, "Active")
+
+ for i, schedule in enumerate(first_asset_depr_schedule):
+ self.assertEqual(getdate(expected_depr_values[i][0]), schedule.schedule_date)
+ self.assertEqual(expected_depr_values[i][1], schedule.depreciation_amount)
+ self.assertEqual(expected_depr_values[i][2], schedule.accumulated_depreciation_amount)
+
+ post_depreciation_entries(date="2023-03-31")
+
+ si = create_sales_invoice(
+ item_code="Macbook Pro", asset=asset.name, qty=1, rate=40000, posting_date=getdate("2023-05-23")
+ )
+ asset.load_from_db()
+
+ self.assertEqual(frappe.db.get_value("Asset", asset.name, "status"), "Sold")
+
+ expected_values = [["2023-03-31", 12000, 36000], ["2023-05-23", 1742.47, 37742.47]]
+
+ second_asset_depr_schedule = get_depr_schedule(asset.name, "Active")
+
+ for i, schedule in enumerate(second_asset_depr_schedule):
+ self.assertEqual(getdate(expected_values[i][0]), schedule.schedule_date)
+ self.assertEqual(expected_values[i][1], schedule.depreciation_amount)
+ self.assertEqual(expected_values[i][2], schedule.accumulated_depreciation_amount)
+ self.assertTrue(schedule.journal_entry)
+
+ expected_gle = (
+ (
+ "_Test Accumulated Depreciations - _TC",
+ 37742.47,
+ 0.0,
+ ),
+ (
+ "_Test Fixed Asset - _TC",
+ 0.0,
+ 60000.0,
+ ),
+ (
+ "_Test Gain/Loss on Asset Disposal - _TC",
+ 0.0,
+ 17742.47,
+ ),
+ ("Debtors - _TC", 40000.0, 0.0),
+ )
+
+ gle = frappe.db.sql(
+ """select account, debit, credit from `tabGL Entry`
+ where voucher_type='Sales Invoice' and voucher_no = %s
+ order by account""",
+ si.name,
+ )
+
+ self.assertSequenceEqual(gle, expected_gle)
+
def test_asset_with_maintenance_required_status_after_sale(self):
asset = create_asset(
calculate_depreciation=1,
diff --git a/erpnext/assets/doctype/asset_capitalization/asset_capitalization.py b/erpnext/assets/doctype/asset_capitalization/asset_capitalization.py
index 5b910db..789ca6c 100644
--- a/erpnext/assets/doctype/asset_capitalization/asset_capitalization.py
+++ b/erpnext/assets/doctype/asset_capitalization/asset_capitalization.py
@@ -443,6 +443,7 @@
item.get("finance_book") or self.get("finance_book"),
self.get("doctype"),
self.get("name"),
+ self.get("posting_date"),
)
asset.db_set("disposal_date", self.posting_date)
diff --git a/erpnext/assets/doctype/asset_depreciation_schedule/asset_depreciation_schedule.py b/erpnext/assets/doctype/asset_depreciation_schedule/asset_depreciation_schedule.py
index ad5ec3d..8b359cd 100644
--- a/erpnext/assets/doctype/asset_depreciation_schedule/asset_depreciation_schedule.py
+++ b/erpnext/assets/doctype/asset_depreciation_schedule/asset_depreciation_schedule.py
@@ -252,7 +252,10 @@
# if asset is being sold or scrapped
if date_of_disposal:
- from_date = asset_doc.available_for_use_date
+ from_date = add_months(
+ getdate(asset_doc.available_for_use_date),
+ (asset_doc.number_of_depreciations_booked * row.frequency_of_depreciation),
+ )
if self.depreciation_schedule:
from_date = self.depreciation_schedule[-1].schedule_date
diff --git a/erpnext/buying/doctype/purchase_order/purchase_order.json b/erpnext/buying/doctype/purchase_order/purchase_order.json
index c51c6ed..645abf2 100644
--- a/erpnext/buying/doctype/purchase_order/purchase_order.json
+++ b/erpnext/buying/doctype/purchase_order/purchase_order.json
@@ -157,7 +157,7 @@
"party_account_currency",
"inter_company_order_reference",
"is_old_subcontracting_flow",
- "dashboard"
+ "connections_tab"
],
"fields": [
{
@@ -1171,7 +1171,6 @@
"depends_on": "is_internal_supplier",
"fieldname": "set_from_warehouse",
"fieldtype": "Link",
- "ignore_user_permissions": 1,
"label": "Set From Warehouse",
"options": "Warehouse"
},
@@ -1186,12 +1185,6 @@
"label": "More Info"
},
{
- "fieldname": "dashboard",
- "fieldtype": "Tab Break",
- "label": "Dashboard",
- "show_dashboard": 1
- },
- {
"fieldname": "column_break_7",
"fieldtype": "Column Break"
},
@@ -1266,13 +1259,19 @@
"fieldname": "shipping_address_section",
"fieldtype": "Section Break",
"label": "Shipping Address"
+ },
+ {
+ "fieldname": "connections_tab",
+ "fieldtype": "Tab Break",
+ "label": "Connections",
+ "show_dashboard": 1
}
],
"icon": "fa fa-file-text",
"idx": 105,
"is_submittable": 1,
"links": [],
- "modified": "2023-05-07 20:18:09.196799",
+ "modified": "2023-05-24 11:16:41.195340",
"modified_by": "Administrator",
"module": "Buying",
"name": "Purchase Order",
diff --git a/erpnext/controllers/subcontracting_controller.py b/erpnext/controllers/subcontracting_controller.py
index c3fa894..1e9c4dc 100644
--- a/erpnext/controllers/subcontracting_controller.py
+++ b/erpnext/controllers/subcontracting_controller.py
@@ -689,7 +689,6 @@
"actual_qty": flt(item.rejected_qty) * flt(item.conversion_factor),
"serial_no": cstr(item.rejected_serial_no).strip(),
"incoming_rate": 0.0,
- "recalculate_rate": 1,
},
)
)
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.json b/erpnext/manufacturing/doctype/job_card/job_card.json
index 8506111..316e586 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.json
+++ b/erpnext/manufacturing/doctype/job_card/job_card.json
@@ -439,7 +439,7 @@
],
"is_submittable": 1,
"links": [],
- "modified": "2022-11-09 15:02:44.490731",
+ "modified": "2023-05-23 09:56:43.826602",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Job Card",
diff --git a/erpnext/manufacturing/doctype/job_card/job_card.py b/erpnext/manufacturing/doctype/job_card/job_card.py
index 4a4046e..a7d0b29 100644
--- a/erpnext/manufacturing/doctype/job_card/job_card.py
+++ b/erpnext/manufacturing/doctype/job_card/job_card.py
@@ -730,7 +730,7 @@
self.status = {0: "Open", 1: "Submitted", 2: "Cancelled"}[self.docstatus or 0]
if self.docstatus < 2:
- if self.for_quantity <= self.transferred_qty:
+ if flt(self.for_quantity) <= flt(self.transferred_qty):
self.status = "Material Transferred"
if self.time_logs:
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.js b/erpnext/manufacturing/doctype/production_plan/production_plan.js
index ab7aa52..45a59cf 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.js
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.js
@@ -451,10 +451,14 @@
for_warehouse: row.warehouse
},
callback: function(r) {
- let {projected_qty, actual_qty} = r.message;
+ if (r.message) {
+ let {projected_qty, actual_qty} = r.message[0];
- frappe.model.set_value(cdt, cdn, 'projected_qty', projected_qty);
- frappe.model.set_value(cdt, cdn, 'actual_qty', actual_qty);
+ frappe.model.set_value(cdt, cdn, {
+ 'projected_qty': projected_qty,
+ 'actual_qty': actual_qty
+ });
+ }
}
})
}
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.json b/erpnext/manufacturing/doctype/production_plan/production_plan.json
index fdaa4a2..232f1cb 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.json
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.json
@@ -35,8 +35,12 @@
"section_break_25",
"prod_plan_references",
"section_break_24",
- "get_sub_assembly_items",
"combine_sub_items",
+ "section_break_ucc4",
+ "skip_available_sub_assembly_item",
+ "column_break_igxl",
+ "get_sub_assembly_items",
+ "section_break_g4ip",
"sub_assembly_items",
"download_materials_request_plan_section_section",
"download_materials_required",
@@ -351,12 +355,12 @@
{
"fieldname": "section_break_24",
"fieldtype": "Section Break",
- "hide_border": 1
+ "hide_border": 1,
+ "label": "Sub Assembly Items"
},
{
"fieldname": "sub_assembly_items",
"fieldtype": "Table",
- "label": "Sub Assembly Items",
"no_copy": 1,
"options": "Production Plan Sub Assembly Item"
},
@@ -392,13 +396,33 @@
"fieldname": "download_materials_request_plan_section_section",
"fieldtype": "Section Break",
"label": "Download Materials Request Plan Section"
+ },
+ {
+ "default": "0",
+ "description": "System consider the projected quantity to check available or will be available sub-assembly items ",
+ "fieldname": "skip_available_sub_assembly_item",
+ "fieldtype": "Check",
+ "label": "Skip Available Sub Assembly Items"
+ },
+ {
+ "fieldname": "section_break_ucc4",
+ "fieldtype": "Column Break",
+ "hide_border": 1
+ },
+ {
+ "fieldname": "section_break_g4ip",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "column_break_igxl",
+ "fieldtype": "Column Break"
}
],
"icon": "fa fa-calendar",
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2023-03-31 10:30:48.118932",
+ "modified": "2023-05-22 23:36:31.770517",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Production Plan",
diff --git a/erpnext/manufacturing/doctype/production_plan/production_plan.py b/erpnext/manufacturing/doctype/production_plan/production_plan.py
index f9e68b9..0800bdd 100644
--- a/erpnext/manufacturing/doctype/production_plan/production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/production_plan.py
@@ -718,7 +718,9 @@
frappe.throw(_("Row #{0}: Please select Item Code in Assembly Items").format(row.idx))
bom_data = []
- get_sub_assembly_items(row.bom_no, bom_data, row.planned_qty)
+
+ warehouse = row.warehouse if self.skip_available_sub_assembly_item else None
+ get_sub_assembly_items(row.bom_no, bom_data, row.planned_qty, self.company, warehouse=warehouse)
self.set_sub_assembly_items_based_on_level(row, bom_data, manufacturing_type)
sub_assembly_items_store.extend(bom_data)
@@ -894,7 +896,9 @@
build_csv_response(item_list, doc.name)
-def get_exploded_items(item_details, company, bom_no, include_non_stock_items, planned_qty=1):
+def get_exploded_items(
+ item_details, company, bom_no, include_non_stock_items, planned_qty=1, doc=None
+):
bei = frappe.qb.DocType("BOM Explosion Item")
bom = frappe.qb.DocType("BOM")
item = frappe.qb.DocType("Item")
@@ -1271,6 +1275,12 @@
include_safety_stock = doc.get("include_safety_stock")
so_item_details = frappe._dict()
+
+ sub_assembly_items = {}
+ if doc.get("skip_available_sub_assembly_item"):
+ for d in doc.get("sub_assembly_items"):
+ sub_assembly_items.setdefault((d.get("production_item"), d.get("bom_no")), d.get("qty"))
+
for data in po_items:
if not data.get("include_exploded_items") and doc.get("sub_assembly_items"):
data["include_exploded_items"] = 1
@@ -1296,10 +1306,24 @@
frappe.throw(_("For row {0}: Enter Planned Qty").format(data.get("idx")))
if bom_no:
- if data.get("include_exploded_items") and include_subcontracted_items:
+ if (
+ data.get("include_exploded_items")
+ and doc.get("sub_assembly_items")
+ and doc.get("skip_available_sub_assembly_item")
+ ):
+ item_details = get_raw_materials_of_sub_assembly_items(
+ item_details,
+ company,
+ bom_no,
+ include_non_stock_items,
+ sub_assembly_items,
+ planned_qty=planned_qty,
+ )
+
+ elif data.get("include_exploded_items") and include_subcontracted_items:
# fetch exploded items from BOM
item_details = get_exploded_items(
- item_details, company, bom_no, include_non_stock_items, planned_qty=planned_qty
+ item_details, company, bom_no, include_non_stock_items, planned_qty=planned_qty, doc=doc
)
else:
item_details = get_subitems(
@@ -1456,12 +1480,22 @@
}
-def get_sub_assembly_items(bom_no, bom_data, to_produce_qty, indent=0):
+def get_sub_assembly_items(bom_no, bom_data, to_produce_qty, company, warehouse=None, indent=0):
data = get_bom_children(parent=bom_no)
for d in data:
if d.expandable:
parent_item_code = frappe.get_cached_value("BOM", bom_no, "item")
stock_qty = (d.stock_qty / d.parent_bom_qty) * flt(to_produce_qty)
+
+ if warehouse:
+ bin_dict = get_bin_details(d, company, for_warehouse=warehouse)
+
+ if bin_dict and bin_dict[0].projected_qty > 0:
+ if bin_dict[0].projected_qty > stock_qty:
+ continue
+ else:
+ stock_qty = stock_qty - bin_dict[0].projected_qty
+
bom_data.append(
frappe._dict(
{
@@ -1481,7 +1515,7 @@
)
if d.value:
- get_sub_assembly_items(d.value, bom_data, stock_qty, indent=indent + 1)
+ get_sub_assembly_items(d.value, bom_data, stock_qty, company, warehouse, indent=indent + 1)
def set_default_warehouses(row, default_warehouses):
@@ -1500,7 +1534,7 @@
frappe.qb.from_(table)
.inner_join(child)
.on(table.name == child.parent)
- .select(Sum(child.quantity * IfNull(child.conversion_factor, 1.0)))
+ .select(Sum(child.required_bom_qty * IfNull(child.conversion_factor, 1.0)))
.where(
(table.docstatus == 1)
& (child.item_code == item_code)
@@ -1518,4 +1552,72 @@
get_reserved_qty_for_production(item_code, warehouse, check_production_plan=True)
)
+ if reserved_qty_for_production > reserved_qty_for_production_plan:
+ return 0.0
+
return reserved_qty_for_production_plan - reserved_qty_for_production
+
+
+def get_raw_materials_of_sub_assembly_items(
+ item_details, company, bom_no, include_non_stock_items, sub_assembly_items, planned_qty=1
+):
+
+ bei = frappe.qb.DocType("BOM Item")
+ bom = frappe.qb.DocType("BOM")
+ item = frappe.qb.DocType("Item")
+ item_default = frappe.qb.DocType("Item Default")
+ item_uom = frappe.qb.DocType("UOM Conversion Detail")
+
+ items = (
+ frappe.qb.from_(bei)
+ .join(bom)
+ .on(bom.name == bei.parent)
+ .join(item)
+ .on(item.name == bei.item_code)
+ .left_join(item_default)
+ .on((item_default.parent == item.name) & (item_default.company == company))
+ .left_join(item_uom)
+ .on((item.name == item_uom.parent) & (item_uom.uom == item.purchase_uom))
+ .select(
+ (IfNull(Sum(bei.stock_qty / IfNull(bom.quantity, 1)), 0) * planned_qty).as_("qty"),
+ item.item_name,
+ item.name.as_("item_code"),
+ bei.description,
+ bei.stock_uom,
+ bei.bom_no,
+ item.min_order_qty,
+ bei.source_warehouse,
+ item.default_material_request_type,
+ item.min_order_qty,
+ item_default.default_warehouse,
+ item.purchase_uom,
+ item_uom.conversion_factor,
+ item.safety_stock,
+ )
+ .where(
+ (bei.docstatus == 1)
+ & (bom.name == bom_no)
+ & (item.is_stock_item.isin([0, 1]) if include_non_stock_items else item.is_stock_item == 1)
+ )
+ .groupby(bei.item_code, bei.stock_uom)
+ ).run(as_dict=True)
+
+ for item in items:
+ key = (item.item_code, item.bom_no)
+ if item.bom_no and key in sub_assembly_items:
+ planned_qty = flt(sub_assembly_items[key])
+ get_raw_materials_of_sub_assembly_items(
+ item_details,
+ company,
+ item.bom_no,
+ include_non_stock_items,
+ sub_assembly_items,
+ planned_qty=planned_qty,
+ )
+ else:
+ if not item.conversion_factor and item.purchase_uom:
+ item.conversion_factor = get_uom_conversion_factor(item.item_code, item.purchase_uom)
+
+ item_details.setdefault(item.get("item_code"), item)
+
+ return item_details
diff --git a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
index 4648d89..75b43ec 100644
--- a/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
+++ b/erpnext/manufacturing/doctype/production_plan/test_production_plan.py
@@ -926,6 +926,50 @@
self.assertEqual(after_qty, before_qty)
+ def test_skip_available_qty_for_sub_assembly_items(self):
+ from erpnext.manufacturing.doctype.bom.test_bom import create_nested_bom
+
+ bom_tree = {
+ "Fininshed Goods1 For SUB Test": {
+ "SubAssembly1 For SUB Test": {"ChildPart1 For SUB Test": {}},
+ "SubAssembly2 For SUB Test": {},
+ }
+ }
+
+ parent_bom = create_nested_bom(bom_tree, prefix="")
+ plan = create_production_plan(
+ item_code=parent_bom.item,
+ planned_qty=10,
+ ignore_existing_ordered_qty=1,
+ do_not_submit=1,
+ skip_available_sub_assembly_item=1,
+ warehouse="_Test Warehouse - _TC",
+ )
+
+ make_stock_entry(
+ item_code="SubAssembly1 For SUB Test",
+ qty=5,
+ rate=100,
+ target="_Test Warehouse - _TC",
+ )
+
+ self.assertTrue(plan.skip_available_sub_assembly_item)
+
+ plan.get_sub_assembly_items()
+
+ for row in plan.sub_assembly_items:
+ if row.production_item == "SubAssembly1 For SUB Test":
+ self.assertEqual(row.qty, 5)
+
+ mr_items = get_items_for_material_requests(plan.as_dict())
+ for row in mr_items:
+ row = frappe._dict(row)
+ if row.item_code == "ChildPart1 For SUB Test":
+ self.assertEqual(row.quantity, 5)
+
+ if row.item_code == "SubAssembly2 For SUB Test":
+ self.assertEqual(row.quantity, 10)
+
def create_production_plan(**args):
"""
@@ -945,6 +989,7 @@
"include_subcontracted_items": args.include_subcontracted_items or 0,
"ignore_existing_ordered_qty": args.ignore_existing_ordered_qty or 0,
"get_items_from": "Sales Order",
+ "skip_available_sub_assembly_item": args.skip_available_sub_assembly_item or 0,
}
)
@@ -958,6 +1003,7 @@
"planned_qty": args.planned_qty or 1,
"planned_start_date": args.planned_start_date or now_datetime(),
"stock_uom": args.stock_uom or "Nos",
+ "warehouse": args.warehouse,
},
)
diff --git a/erpnext/manufacturing/doctype/production_plan_sub_assembly_item/production_plan_sub_assembly_item.json b/erpnext/manufacturing/doctype/production_plan_sub_assembly_item/production_plan_sub_assembly_item.json
index 4eb6bf6..fde0404 100644
--- a/erpnext/manufacturing/doctype/production_plan_sub_assembly_item/production_plan_sub_assembly_item.json
+++ b/erpnext/manufacturing/doctype/production_plan_sub_assembly_item/production_plan_sub_assembly_item.json
@@ -28,7 +28,11 @@
"uom",
"stock_uom",
"column_break_22",
- "description"
+ "description",
+ "section_break_4rxf",
+ "actual_qty",
+ "column_break_xfhm",
+ "projected_qty"
],
"fields": [
{
@@ -183,12 +187,34 @@
"fieldtype": "Datetime",
"in_list_view": 1,
"label": "Schedule Date"
+ },
+ {
+ "fieldname": "section_break_4rxf",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "actual_qty",
+ "fieldtype": "Float",
+ "label": "Actual Qty",
+ "no_copy": 1,
+ "read_only": 1
+ },
+ {
+ "fieldname": "column_break_xfhm",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "projected_qty",
+ "fieldtype": "Float",
+ "label": "Projected Qty",
+ "no_copy": 1,
+ "read_only": 1
}
],
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2022-11-28 13:50:15.116082",
+ "modified": "2023-05-22 17:52:34.708879",
"modified_by": "Administrator",
"module": "Manufacturing",
"name": "Production Plan Sub Assembly Item",
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index e158df6..7e68ec1 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -326,7 +326,7 @@
erpnext.patches.v15_0.update_asset_value_for_manual_depr_entries
erpnext.patches.v15_0.update_gpa_and_ndb_for_assdeprsch
erpnext.patches.v14_0.create_accounting_dimensions_for_closing_balance
-erpnext.patches.v14_0.update_closing_balances #10-05-2023
+erpnext.patches.v14_0.update_closing_balances #17-05-2023
execute:frappe.db.set_single_value("Accounts Settings", "merge_similar_account_heads", 0)
# below migration patches should always run last
erpnext.patches.v14_0.migrate_gl_to_payment_ledger
diff --git a/erpnext/patches/v14_0/update_closing_balances.py b/erpnext/patches/v14_0/update_closing_balances.py
index bb108ab..d664677 100644
--- a/erpnext/patches/v14_0/update_closing_balances.py
+++ b/erpnext/patches/v14_0/update_closing_balances.py
@@ -13,8 +13,8 @@
def execute():
frappe.db.truncate("Account Closing Balance")
+ i = 0
company_wise_order = {}
- get_opening_entries = True
for pcv in frappe.db.get_all(
"Period Closing Voucher",
fields=["company", "posting_date", "name"],
@@ -29,6 +29,7 @@
pcv.posting_date, pcv.fiscal_year, company=pcv.company
)[1]
+ # get gl entries against pcv
gl_entries = frappe.db.get_all(
"GL Entry", filters={"voucher_no": pcv.name, "is_cancelled": 0}, fields=["*"]
)
@@ -37,20 +38,31 @@
entry["closing_date"] = pcv_doc.posting_date
entry["period_closing_voucher"] = pcv_doc.name
+ # get all gl entries for the year
closing_entries = frappe.db.get_all(
"GL Entry",
filters={
"is_cancelled": 0,
"voucher_no": ["!=", pcv.name],
- "posting_date": ["<=", pcv.posting_date],
+ "posting_date": ["between", [pcv_doc.year_start_date, pcv.posting_date]],
+ "is_opening": "No",
},
fields=["*"],
)
+ if i == 0:
+ # add opening entries only for the first pcv
+ closing_entries += frappe.db.get_all(
+ "GL Entry",
+ filters={"is_cancelled": 0, "is_opening": "Yes"},
+ fields=["*"],
+ )
+
for entry in closing_entries:
entry["closing_date"] = pcv_doc.posting_date
entry["period_closing_voucher"] = pcv_doc.name
make_closing_entries(gl_entries + closing_entries, voucher_name=pcv.name)
company_wise_order[pcv.company].append(pcv.posting_date)
- get_opening_entries = False
+
+ i += 1
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index 0cab21f..61969fe 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -288,7 +288,7 @@
)
# sales team
- for d in customer.get("sales_team", []):
+ for d in customer.get("sales_team") or []:
target.append(
"sales_team",
{
diff --git a/erpnext/selling/form_tour/quotation/quotation.json b/erpnext/selling/form_tour/quotation/quotation.json
index 2a2aa5e..8c97700 100644
--- a/erpnext/selling/form_tour/quotation/quotation.json
+++ b/erpnext/selling/form_tour/quotation/quotation.json
@@ -2,9 +2,11 @@
"creation": "2021-11-23 12:00:36.138824",
"docstatus": 0,
"doctype": "Form Tour",
+ "first_document": 0,
"idx": 0,
+ "include_name_field": 0,
"is_standard": 1,
- "modified": "2021-11-23 12:02:48.010298",
+ "modified": "2023-05-23 12:51:48.684517",
"modified_by": "Administrator",
"module": "Selling",
"name": "Quotation",
@@ -14,51 +16,43 @@
"steps": [
{
"description": "Select a customer or lead for whom this quotation is being prepared. Let's select a Customer.",
- "field": "",
"fieldname": "quotation_to",
"fieldtype": "Link",
"has_next_condition": 0,
"is_table_field": 0,
"label": "Quotation To",
- "parent_field": "",
"position": "Right",
"title": "Quotation To"
},
{
"description": "Select a specific Customer to whom this quotation will be sent.",
- "field": "",
"fieldname": "party_name",
"fieldtype": "Dynamic Link",
"has_next_condition": 0,
"is_table_field": 0,
"label": "Party",
- "parent_field": "",
"position": "Right",
"title": "Party"
},
{
"child_doctype": "Quotation Item",
"description": "Select an item for which you will be quoting a price.",
- "field": "",
"fieldname": "items",
"fieldtype": "Table",
"has_next_condition": 0,
"is_table_field": 0,
"label": "Items",
- "parent_field": "",
"parent_fieldname": "items",
"position": "Bottom",
"title": "Items"
},
{
"description": "You can select pre-populated Sales Taxes and Charges from here.",
- "field": "",
"fieldname": "taxes",
"fieldtype": "Table",
"has_next_condition": 0,
"is_table_field": 0,
"label": "Sales Taxes and Charges",
- "parent_field": "",
"position": "Bottom",
"title": "Sales Taxes and Charges"
}
diff --git a/erpnext/setup/module_onboarding/home/home.json b/erpnext/setup/module_onboarding/home/home.json
index 516f122..7b0d77f 100644
--- a/erpnext/setup/module_onboarding/home/home.json
+++ b/erpnext/setup/module_onboarding/home/home.json
@@ -25,29 +25,23 @@
"documentation_url": "https://docs.erpnext.com/docs/v14/user/manual/en/setting-up/company-setup",
"idx": 0,
"is_complete": 0,
- "modified": "2023-05-16 13:13:24.043792",
+ "modified": "2023-05-23 13:20:19.703506",
"modified_by": "Administrator",
"module": "Setup",
"name": "Home",
"owner": "Administrator",
"steps": [
{
- "step": "Navigation Help"
- },
- {
"step": "Create an Item"
},
{
"step": "Create a Customer"
},
{
- "step": "Create a Supplier"
- },
- {
- "step": "Create a Quotation"
+ "step": "Create Your First Sales Invoice"
}
],
- "subtitle": "Item, Customer, Supplier, Navigation Help and Quotation",
+ "subtitle": "Item, Customer, Supplier and Quotation",
"success_message": "You're ready to start your journey with ERPNext",
"title": "Let's begin your journey with ERPNext"
}
\ No newline at end of file
diff --git a/erpnext/setup/onboarding_step/create_a_customer/create_a_customer.json b/erpnext/setup/onboarding_step/create_a_customer/create_a_customer.json
index e1a8f90..dc07578 100644
--- a/erpnext/setup/onboarding_step/create_a_customer/create_a_customer.json
+++ b/erpnext/setup/onboarding_step/create_a_customer/create_a_customer.json
@@ -9,7 +9,7 @@
"is_complete": 0,
"is_single": 0,
"is_skipped": 0,
- "modified": "2023-05-16 12:54:54.112364",
+ "modified": "2023-05-23 12:45:55.138580",
"modified_by": "Administrator",
"name": "Create a Customer",
"owner": "Administrator",
diff --git a/erpnext/setup/onboarding_step/create_a_supplier/create_a_supplier.json b/erpnext/setup/onboarding_step/create_a_supplier/create_a_supplier.json
index ef493fe..4ac26e2 100644
--- a/erpnext/setup/onboarding_step/create_a_supplier/create_a_supplier.json
+++ b/erpnext/setup/onboarding_step/create_a_supplier/create_a_supplier.json
@@ -9,7 +9,7 @@
"is_complete": 0,
"is_single": 0,
"is_skipped": 0,
- "modified": "2023-05-16 12:55:08.610113",
+ "modified": "2023-05-19 15:32:55.069257",
"modified_by": "Administrator",
"name": "Create a Supplier",
"owner": "Administrator",
diff --git a/erpnext/setup/onboarding_step/create_an_item/create_an_item.json b/erpnext/setup/onboarding_step/create_an_item/create_an_item.json
index 15f36be..4115196 100644
--- a/erpnext/setup/onboarding_step/create_an_item/create_an_item.json
+++ b/erpnext/setup/onboarding_step/create_an_item/create_an_item.json
@@ -11,7 +11,7 @@
"is_complete": 0,
"is_single": 0,
"is_skipped": 0,
- "modified": "2023-05-16 12:56:40.355878",
+ "modified": "2023-05-23 12:43:08.484206",
"modified_by": "Administrator",
"name": "Create an Item",
"owner": "Administrator",
diff --git a/erpnext/setup/onboarding_step/create_your_first_sales_invoice/create_your_first_sales_invoice.json b/erpnext/setup/onboarding_step/create_your_first_sales_invoice/create_your_first_sales_invoice.json
new file mode 100644
index 0000000..96fa68e
--- /dev/null
+++ b/erpnext/setup/onboarding_step/create_your_first_sales_invoice/create_your_first_sales_invoice.json
@@ -0,0 +1,20 @@
+{
+ "action": "Create Entry",
+ "creation": "2020-05-14 17:48:21.019019",
+ "description": "# All about sales invoice\n\nA Sales Invoice is a bill that you send to your Customers against which the Customer makes the payment. Sales Invoice is an accounting transaction. On submission of Sales Invoice, the system updates the receivable and books income against a Customer Account.",
+ "docstatus": 0,
+ "doctype": "Onboarding Step",
+ "idx": 0,
+ "is_complete": 0,
+ "is_single": 0,
+ "is_skipped": 0,
+ "modified": "2023-05-22 21:20:15.589644",
+ "modified_by": "Administrator",
+ "name": "Create Your First Sales Invoice",
+ "owner": "Administrator",
+ "reference_document": "Sales Invoice",
+ "show_form_tour": 1,
+ "show_full_form": 1,
+ "title": "Create Your First Sales Invoice ",
+ "validate_action": 1
+}
\ No newline at end of file
diff --git a/erpnext/stock/doctype/closing_stock_balance/__init__.py b/erpnext/stock/doctype/closing_stock_balance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/doctype/closing_stock_balance/__init__.py
diff --git a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js
new file mode 100644
index 0000000..5c807a8
--- /dev/null
+++ b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js
@@ -0,0 +1,39 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on("Closing Stock Balance", {
+ refresh(frm) {
+ frm.trigger("generate_closing_balance");
+ frm.trigger("regenerate_closing_balance");
+ },
+
+ generate_closing_balance(frm) {
+ if (in_list(["Queued", "Failed"], frm.doc.status)) {
+ frm.add_custom_button(__("Generate Closing Stock Balance"), () => {
+ frm.call({
+ method: "enqueue_job",
+ doc: frm.doc,
+ freeze: true,
+ callback: () => {
+ frm.reload_doc();
+ }
+ })
+ })
+ }
+ },
+
+ regenerate_closing_balance(frm) {
+ if (frm.doc.status == "Completed") {
+ frm.add_custom_button(__("Regenerate Closing Stock Balance"), () => {
+ frm.call({
+ method: "regenerate_closing_balance",
+ doc: frm.doc,
+ freeze: true,
+ callback: () => {
+ frm.reload_doc();
+ }
+ })
+ })
+ }
+ }
+});
diff --git a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.json b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.json
new file mode 100644
index 0000000..225da6d
--- /dev/null
+++ b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.json
@@ -0,0 +1,148 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "autoname": "naming_series:",
+ "creation": "2023-05-17 09:58:42.086911",
+ "default_view": "List",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "naming_series",
+ "company",
+ "status",
+ "column_break_p0s0",
+ "from_date",
+ "to_date",
+ "filters_section",
+ "item_code",
+ "item_group",
+ "include_uom",
+ "column_break_rm5w",
+ "warehouse",
+ "warehouse_type",
+ "amended_from"
+ ],
+ "fields": [
+ {
+ "fieldname": "naming_series",
+ "fieldtype": "Select",
+ "label": "Naming Series",
+ "options": "CBAL-.#####"
+ },
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "label": "Company",
+ "options": "Company"
+ },
+ {
+ "default": "Draft",
+ "fieldname": "status",
+ "fieldtype": "Select",
+ "in_list_view": 1,
+ "in_preview": 1,
+ "label": "Status",
+ "options": "Draft\nQueued\nIn Progress\nCompleted\nFailed\nCanceled",
+ "read_only": 1
+ },
+ {
+ "fieldname": "column_break_p0s0",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "from_date",
+ "fieldtype": "Date",
+ "label": "From Date"
+ },
+ {
+ "fieldname": "to_date",
+ "fieldtype": "Date",
+ "label": "To Date"
+ },
+ {
+ "collapsible": 1,
+ "fieldname": "filters_section",
+ "fieldtype": "Section Break",
+ "label": "Filters"
+ },
+ {
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "label": "Item Code",
+ "options": "Item"
+ },
+ {
+ "fieldname": "item_group",
+ "fieldtype": "Link",
+ "label": "Item Group",
+ "options": "Item Group"
+ },
+ {
+ "fieldname": "column_break_rm5w",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "label": "Warehouse",
+ "options": "Warehouse"
+ },
+ {
+ "fieldname": "warehouse_type",
+ "fieldtype": "Link",
+ "label": "Warehouse Type",
+ "options": "Warehouse Type"
+ },
+ {
+ "fieldname": "amended_from",
+ "fieldtype": "Link",
+ "label": "Amended From",
+ "no_copy": 1,
+ "options": "Closing Stock Balance",
+ "print_hide": 1,
+ "read_only": 1
+ },
+ {
+ "fieldname": "amended_from",
+ "fieldtype": "Link",
+ "label": "Amended From",
+ "no_copy": 1,
+ "options": "Closing Stock Balance",
+ "print_hide": 1,
+ "read_only": 1
+ },
+ {
+ "fieldname": "include_uom",
+ "fieldtype": "Link",
+ "label": "Include UOM",
+ "options": "UOM"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "is_submittable": 1,
+ "links": [],
+ "modified": "2023-05-17 11:46:04.448220",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Closing Stock Balance",
+ "naming_rule": "By \"Naming Series\" field",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py
new file mode 100644
index 0000000..a796372
--- /dev/null
+++ b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py
@@ -0,0 +1,133 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import json
+
+import frappe
+from frappe import _
+from frappe.core.doctype.prepared_report.prepared_report import create_json_gz_file
+from frappe.desk.form.load import get_attachments
+from frappe.model.document import Document
+from frappe.utils import get_link_to_form, gzip_decompress, parse_json
+from frappe.utils.background_jobs import enqueue
+
+from erpnext.stock.report.stock_balance.stock_balance import execute
+
+
+class ClosingStockBalance(Document):
+ def before_save(self):
+ self.set_status()
+
+ def set_status(self, save=False):
+ self.status = "Queued"
+ if self.docstatus == 2:
+ self.status = "Canceled"
+
+ if self.docstatus == 0:
+ self.status = "Draft"
+
+ if save:
+ self.db_set("status", self.status)
+
+ def validate(self):
+ self.validate_duplicate()
+
+ def validate_duplicate(self):
+ table = frappe.qb.DocType("Closing Stock Balance")
+
+ query = (
+ frappe.qb.from_(table)
+ .select(table.name)
+ .where(
+ (table.docstatus == 1)
+ & (table.company == self.company)
+ & (
+ (table.from_date.between(self.from_date, self.to_date))
+ | (table.to_date.between(self.from_date, self.to_date))
+ | (table.from_date >= self.from_date and table.to_date <= self.to_date)
+ )
+ )
+ )
+
+ for fieldname in ["warehouse", "item_code", "item_group", "warehouse_type"]:
+ if self.get(fieldname):
+ query = query.where(table.get(fieldname) == self.get(fieldname))
+
+ query = query.run(as_dict=True)
+
+ if query and query[0].name:
+ name = get_link_to_form("Closing Stock Balance", query[0].name)
+ msg = f"Closing Stock Balance {name} already exists for the selected date range"
+ frappe.throw(_(msg), title=_("Duplicate Closing Stock Balance"))
+
+ def on_submit(self):
+ self.set_status(save=True)
+ self.enqueue_job()
+
+ def on_cancel(self):
+ self.set_status(save=True)
+ self.clear_attachment()
+
+ @frappe.whitelist()
+ def enqueue_job(self):
+ self.db_set("status", "In Progress")
+ self.clear_attachment()
+ enqueue(prepare_closing_stock_balance, name=self.name, queue="long", timeout=1500)
+
+ @frappe.whitelist()
+ def regenerate_closing_balance(self):
+ self.enqueue_job()
+
+ def clear_attachment(self):
+ if attachments := get_attachments(self.doctype, self.name):
+ attachment = attachments[0]
+ frappe.delete_doc("File", attachment.name)
+
+ def create_closing_stock_balance_entries(self):
+ columns, data = execute(
+ filters=frappe._dict(
+ {
+ "company": self.company,
+ "from_date": self.from_date,
+ "to_date": self.to_date,
+ "warehouse": self.warehouse,
+ "item_code": self.item_code,
+ "item_group": self.item_group,
+ "warehouse_type": self.warehouse_type,
+ "include_uom": self.include_uom,
+ "ignore_closing_balance": 1,
+ "show_variant_attributes": 1,
+ "show_stock_ageing_data": 1,
+ }
+ )
+ )
+
+ create_json_gz_file({"columns": columns, "data": data}, self.doctype, self.name)
+
+ def get_prepared_data(self):
+ if attachments := get_attachments(self.doctype, self.name):
+ attachment = attachments[0]
+ attached_file = frappe.get_doc("File", attachment.name)
+
+ data = gzip_decompress(attached_file.get_content())
+ if data := json.loads(data.decode("utf-8")):
+ data = data
+
+ return parse_json(data)
+
+ return frappe._dict({})
+
+
+def prepare_closing_stock_balance(name):
+ doc = frappe.get_doc("Closing Stock Balance", name)
+
+ doc.db_set("status", "In Progress")
+
+ try:
+ doc.create_closing_stock_balance_entries()
+ doc.db_set("status", "Completed")
+ except Exception as e:
+ doc.db_set("status", "Failed")
+ traceback = frappe.get_traceback()
+
+ frappe.log_error("Closing Stock Balance Failed", traceback, doc.doctype, doc.name)
diff --git a/erpnext/stock/doctype/closing_stock_balance/test_closing_stock_balance.py b/erpnext/stock/doctype/closing_stock_balance/test_closing_stock_balance.py
new file mode 100644
index 0000000..7d61f5c
--- /dev/null
+++ b/erpnext/stock/doctype/closing_stock_balance/test_closing_stock_balance.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+# import frappe
+from frappe.tests.utils import FrappeTestCase
+
+
+class TestClosingStockBalance(FrappeTestCase):
+ pass
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index 74927c7..a9a9a1d 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -460,7 +460,7 @@
item_doc.qty if (docstatus == 1 and item_doc.stock_qty == 0) else item_doc.stock_qty
)
- while remaining_stock_qty > 0 and available_locations:
+ while flt(remaining_stock_qty) > 0 and available_locations:
item_location = available_locations.pop(0)
item_location = frappe._dict(item_location)
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index 2fa97ae..d3f1f31 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -281,7 +281,7 @@
# consume transfer data and add stock to fifo queue
self.__adjust_incoming_transfer_qty(transfer_data, fifo_queue, row)
else:
- if not serial_nos:
+ if not serial_nos and not row.get("has_serial_no"):
if fifo_queue and flt(fifo_queue[0][0]) <= 0:
# neutralize 0/negative stock by adding positive stock
fifo_queue[0][0] += flt(row.actual_qty)
diff --git a/erpnext/stock/report/stock_analytics/stock_analytics.py b/erpnext/stock/report/stock_analytics/stock_analytics.py
index 27b94ab..6c5b58c 100644
--- a/erpnext/stock/report/stock_analytics/stock_analytics.py
+++ b/erpnext/stock/report/stock_analytics/stock_analytics.py
@@ -5,15 +5,13 @@
import frappe
from frappe import _, scrub
+from frappe.query_builder.functions import CombineDatetime
from frappe.utils import get_first_day as get_first_day_of_month
from frappe.utils import get_first_day_of_week, get_quarter_start, getdate
+from frappe.utils.nestedset import get_descendants_of
from erpnext.accounts.utils import get_fiscal_year
-from erpnext.stock.report.stock_balance.stock_balance import (
- get_item_details,
- get_items,
- get_stock_ledger_entries,
-)
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.utils import is_reposting_item_valuation_in_progress
@@ -231,7 +229,7 @@
data = []
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
- item_details = get_item_details(items, sle, filters)
+ item_details = get_item_details(items, sle)
periodic_data = get_periodic_data(sle, filters)
ranges = get_period_date_ranges(filters)
@@ -265,3 +263,109 @@
chart["type"] = "line"
return chart
+
+
+def get_items(filters):
+ "Get items based on item code, item group or brand."
+ if item_code := filters.get("item_code"):
+ return [item_code]
+ else:
+ item_filters = {}
+ if item_group := filters.get("item_group"):
+ children = get_descendants_of("Item Group", item_group, ignore_permissions=True)
+ item_filters["item_group"] = ("in", children + [item_group])
+ if brand := filters.get("brand"):
+ item_filters["brand"] = brand
+
+ return frappe.get_all("Item", filters=item_filters, pluck="name", order_by=None)
+
+
+def get_stock_ledger_entries(filters, items):
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ sle.warehouse,
+ sle.posting_date,
+ sle.actual_qty,
+ sle.valuation_rate,
+ sle.company,
+ sle.voucher_type,
+ sle.qty_after_transaction,
+ sle.stock_value_difference,
+ sle.item_code.as_("name"),
+ sle.voucher_no,
+ sle.stock_value,
+ sle.batch_no,
+ )
+ .where((sle.docstatus < 2) & (sle.is_cancelled == 0))
+ .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
+ .orderby(sle.creation)
+ .orderby(sle.actual_qty)
+ )
+
+ if items:
+ query = query.where(sle.item_code.isin(items))
+
+ query = apply_conditions(query, filters)
+ return query.run(as_dict=True)
+
+
+def apply_conditions(query, filters):
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ warehouse_table = frappe.qb.DocType("Warehouse")
+
+ if not filters.get("from_date"):
+ frappe.throw(_("'From Date' is required"))
+
+ if to_date := filters.get("to_date"):
+ query = query.where(sle.posting_date <= to_date)
+ else:
+ frappe.throw(_("'To Date' is required"))
+
+ if company := filters.get("company"):
+ query = query.where(sle.company == company)
+
+ if filters.get("warehouse"):
+ query = apply_warehouse_filter(query, sle, filters)
+ elif warehouse_type := filters.get("warehouse_type"):
+ query = (
+ query.join(warehouse_table)
+ .on(warehouse_table.name == sle.warehouse)
+ .where(warehouse_table.warehouse_type == warehouse_type)
+ )
+
+ return query
+
+
+def get_item_details(items, sle):
+ item_details = {}
+ if not items:
+ items = list(set(d.item_code for d in sle))
+
+ if not items:
+ return item_details
+
+ item_table = frappe.qb.DocType("Item")
+
+ query = (
+ frappe.qb.from_(item_table)
+ .select(
+ item_table.name,
+ item_table.item_name,
+ item_table.description,
+ item_table.item_group,
+ item_table.brand,
+ item_table.stock_uom,
+ )
+ .where(item_table.name.isin(items))
+ )
+
+ result = query.run(as_dict=1)
+
+ for item_table in result:
+ item_details.setdefault(item_table.name, item_table)
+
+ return item_details
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js
index 7a170be..50a78a8 100644
--- a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js
+++ b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js
@@ -33,5 +33,40 @@
"fieldtype": "Date",
"default": frappe.datetime.get_today(),
},
- ]
+ ],
+
+ get_datatable_options(options) {
+ return Object.assign(options, {
+ checkboxColumn: true,
+ });
+ },
+
+ onload(report) {
+ report.page.add_inner_button(__("Create Reposting Entries"), function() {
+ 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>
+ `;
+
+ frappe.confirm(__(message), () => {
+ let indexes = frappe.query_report.datatable.rowmanager.getCheckedRows();
+ let selected_rows = indexes.map(i => frappe.query_report.data[i]);
+
+ frappe.call({
+ method: "erpnext.stock.report.stock_and_account_value_comparison.stock_and_account_value_comparison.create_reposting_entries",
+ args: {
+ rows: selected_rows,
+ company: frappe.query_report.get_filter_values().company
+ }
+ });
+
+ });
+ });
+ }
};
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py
index 106e877..b1da3ec 100644
--- a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py
+++ b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.utils import get_link_to_form, parse_json
import erpnext
from erpnext.accounts.utils import get_currency_precision, get_stock_accounts
@@ -134,3 +135,35 @@
"width": "120",
},
]
+
+
+@frappe.whitelist()
+def create_reposting_entries(rows, company):
+ if isinstance(rows, str):
+ rows = parse_json(rows)
+
+ entries = []
+ for row in rows:
+ row = frappe._dict(row)
+
+ try:
+ doc = frappe.get_doc(
+ {
+ "doctype": "Repost Item Valuation",
+ "based_on": "Transaction",
+ "status": "Queued",
+ "voucher_type": row.voucher_type,
+ "voucher_no": row.voucher_no,
+ "posting_date": row.posting_date,
+ "company": company,
+ "allow_nagative_stock": 1,
+ }
+ ).submit()
+
+ entries.append(get_link_to_form("Repost Item Valuation", doc.name))
+ except frappe.DuplicateEntryError:
+ pass
+
+ if entries:
+ entries = ", ".join(entries)
+ frappe.msgprint(_(f"Reposting entries created: {entries}"))
diff --git a/erpnext/stock/report/stock_balance/stock_balance.js b/erpnext/stock/report/stock_balance/stock_balance.js
index 9b3965d..33ed955 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.js
+++ b/erpnext/stock/report/stock_balance/stock_balance.js
@@ -87,6 +87,12 @@
"label": __('Show Stock Ageing Data'),
"fieldtype": 'Check'
},
+ {
+ "fieldname": 'ignore_closing_balance',
+ "label": __('Ignore Closing Balance'),
+ "fieldtype": 'Check',
+ "default": 1
+ },
],
"formatter": function (value, row, column, data, default_formatter) {
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 66991a9..68df918 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -7,15 +7,16 @@
import frappe
from frappe import _
+from frappe.query_builder import Order
from frappe.query_builder.functions import Coalesce, CombineDatetime
-from frappe.utils import cint, date_diff, flt, getdate
+from frappe.utils import add_days, cint, date_diff, flt, getdate
from frappe.utils.nestedset import get_descendants_of
import erpnext
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
-from erpnext.stock.utils import add_additional_uom_columns, is_reposting_item_valuation_in_progress
+from erpnext.stock.utils import add_additional_uom_columns
class StockBalanceFilter(TypedDict):
@@ -35,400 +36,548 @@
def execute(filters: Optional[StockBalanceFilter] = None):
- is_reposting_item_valuation_in_progress()
- if not filters:
- filters = {}
+ return StockBalanceReport(filters).run()
- if filters.get("company"):
- company_currency = erpnext.get_company_currency(filters.get("company"))
- else:
- company_currency = frappe.db.get_single_value("Global Defaults", "default_currency")
- include_uom = filters.get("include_uom")
- columns = get_columns(filters)
- items = get_items(filters)
- sle = get_stock_ledger_entries(filters, items)
+class StockBalanceReport(object):
+ def __init__(self, filters: Optional[StockBalanceFilter]) -> None:
+ self.filters = filters
+ self.from_date = getdate(filters.get("from_date"))
+ self.to_date = getdate(filters.get("to_date"))
- if filters.get("show_stock_ageing_data"):
- filters["show_warehouse_wise_stock"] = True
- item_wise_fifo_queue = FIFOSlots(filters, sle).generate()
+ self.start_from = None
+ self.data = []
+ self.columns = []
+ self.sle_entries: List[SLEntry] = []
+ self.set_company_currency()
- # if no stock ledger entry found return
- if not sle:
- return columns, []
+ def set_company_currency(self) -> None:
+ if self.filters.get("company"):
+ self.company_currency = erpnext.get_company_currency(self.filters.get("company"))
+ else:
+ self.company_currency = frappe.db.get_single_value("Global Defaults", "default_currency")
- iwb_map = get_item_warehouse_map(filters, sle)
- item_map = get_item_details(items, sle, filters)
- item_reorder_detail_map = get_item_reorder_details(item_map.keys())
+ def run(self):
+ self.float_precision = cint(frappe.db.get_default("float_precision")) or 3
- data = []
- conversion_factors = {}
+ self.inventory_dimensions = self.get_inventory_dimension_fields()
+ self.prepare_opening_data_from_closing_balance()
+ self.prepare_stock_ledger_entries()
+ self.prepare_new_data()
- _func = itemgetter(1)
+ if not self.columns:
+ self.columns = self.get_columns()
- to_date = filters.get("to_date")
+ self.add_additional_uom_columns()
- for group_by_key in iwb_map:
- item = group_by_key[1]
- warehouse = group_by_key[2]
- company = group_by_key[0]
+ return self.columns, self.data
- if item_map.get(item):
- qty_dict = iwb_map[group_by_key]
- item_reorder_level = 0
- item_reorder_qty = 0
- if item + warehouse in item_reorder_detail_map:
- item_reorder_level = item_reorder_detail_map[item + warehouse]["warehouse_reorder_level"]
- item_reorder_qty = item_reorder_detail_map[item + warehouse]["warehouse_reorder_qty"]
+ def prepare_opening_data_from_closing_balance(self) -> None:
+ self.opening_data = frappe._dict({})
- report_data = {
- "currency": company_currency,
- "item_code": item,
- "warehouse": warehouse,
- "company": company,
- "reorder_level": item_reorder_level,
- "reorder_qty": item_reorder_qty,
- }
- report_data.update(item_map[item])
- report_data.update(qty_dict)
+ closing_balance = self.get_closing_balance()
+ if not closing_balance:
+ return
- if include_uom:
- conversion_factors.setdefault(item, item_map[item].conversion_factor)
+ self.start_from = add_days(closing_balance[0].to_date, 1)
+ res = frappe.get_doc("Closing Stock Balance", closing_balance[0].name).get_prepared_data()
- if filters.get("show_stock_ageing_data"):
- fifo_queue = item_wise_fifo_queue[(item, warehouse)].get("fifo_queue")
+ for entry in res.data:
+ entry = frappe._dict(entry)
+
+ group_by_key = self.get_group_by_key(entry)
+ if group_by_key not in self.opening_data:
+ self.opening_data.setdefault(group_by_key, entry)
+
+ def prepare_new_data(self):
+ if not self.sle_entries:
+ return
+
+ if self.filters.get("show_stock_ageing_data"):
+ self.filters["show_warehouse_wise_stock"] = True
+ item_wise_fifo_queue = FIFOSlots(self.filters, self.sle_entries).generate()
+
+ _func = itemgetter(1)
+
+ self.item_warehouse_map = self.get_item_warehouse_map()
+
+ variant_values = {}
+ if self.filters.get("show_variant_attributes"):
+ variant_values = self.get_variant_values_for()
+
+ for key, report_data in self.item_warehouse_map.items():
+ if variant_data := variant_values.get(report_data.item_code):
+ report_data.update(variant_data)
+
+ if self.filters.get("show_stock_ageing_data"):
+ opening_fifo_queue = self.get_opening_fifo_queue(report_data) or []
+
+ fifo_queue = []
+ if fifo_queue := item_wise_fifo_queue.get((report_data.item_code, report_data.warehouse)):
+ fifo_queue = fifo_queue.get("fifo_queue")
+
+ if fifo_queue:
+ opening_fifo_queue.extend(fifo_queue)
stock_ageing_data = {"average_age": 0, "earliest_age": 0, "latest_age": 0}
- if fifo_queue:
- fifo_queue = sorted(filter(_func, fifo_queue), key=_func)
+ if opening_fifo_queue:
+ fifo_queue = sorted(filter(_func, opening_fifo_queue), key=_func)
if not fifo_queue:
continue
+ to_date = self.to_date
stock_ageing_data["average_age"] = get_average_age(fifo_queue, to_date)
stock_ageing_data["earliest_age"] = date_diff(to_date, fifo_queue[0][1])
stock_ageing_data["latest_age"] = date_diff(to_date, fifo_queue[-1][1])
+ stock_ageing_data["fifo_queue"] = fifo_queue
report_data.update(stock_ageing_data)
- data.append(report_data)
+ self.data.append(report_data)
- add_additional_uom_columns(columns, data, include_uom, conversion_factors)
- return columns, data
+ def get_item_warehouse_map(self):
+ item_warehouse_map = {}
+ self.opening_vouchers = self.get_opening_vouchers()
+ for entry in self.sle_entries:
+ group_by_key = self.get_group_by_key(entry)
+ if group_by_key not in item_warehouse_map:
+ self.initialize_data(item_warehouse_map, group_by_key, entry)
-def get_columns(filters: StockBalanceFilter):
- """return columns"""
- columns = [
- {
- "label": _("Item"),
- "fieldname": "item_code",
- "fieldtype": "Link",
- "options": "Item",
- "width": 100,
- },
- {"label": _("Item Name"), "fieldname": "item_name", "width": 150},
- {
- "label": _("Item Group"),
- "fieldname": "item_group",
- "fieldtype": "Link",
- "options": "Item Group",
- "width": 100,
- },
- {
- "label": _("Warehouse"),
- "fieldname": "warehouse",
- "fieldtype": "Link",
- "options": "Warehouse",
- "width": 100,
- },
- ]
+ self.prepare_item_warehouse_map(item_warehouse_map, entry, group_by_key)
- for dimension in get_inventory_dimensions():
- columns.append(
- {
- "label": _(dimension.doctype),
- "fieldname": dimension.fieldname,
- "fieldtype": "Link",
- "options": dimension.doctype,
- "width": 110,
- }
+ if self.opening_data.get(group_by_key):
+ del self.opening_data[group_by_key]
+
+ for group_by_key, entry in self.opening_data.items():
+ if group_by_key not in item_warehouse_map:
+ self.initialize_data(item_warehouse_map, group_by_key, entry)
+
+ item_warehouse_map = filter_items_with_no_transactions(
+ item_warehouse_map, self.float_precision, self.inventory_dimensions
)
- columns.extend(
- [
- {
- "label": _("Stock UOM"),
- "fieldname": "stock_uom",
- "fieldtype": "Link",
- "options": "UOM",
- "width": 90,
- },
- {
- "label": _("Balance Qty"),
- "fieldname": "bal_qty",
- "fieldtype": "Float",
- "width": 100,
- "convertible": "qty",
- },
- {
- "label": _("Balance Value"),
- "fieldname": "bal_val",
- "fieldtype": "Currency",
- "width": 100,
- "options": "currency",
- },
- {
- "label": _("Opening Qty"),
- "fieldname": "opening_qty",
- "fieldtype": "Float",
- "width": 100,
- "convertible": "qty",
- },
- {
- "label": _("Opening Value"),
- "fieldname": "opening_val",
- "fieldtype": "Currency",
- "width": 110,
- "options": "currency",
- },
- {
- "label": _("In Qty"),
- "fieldname": "in_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {"label": _("In Value"), "fieldname": "in_val", "fieldtype": "Float", "width": 80},
- {
- "label": _("Out Qty"),
- "fieldname": "out_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {"label": _("Out Value"), "fieldname": "out_val", "fieldtype": "Float", "width": 80},
- {
- "label": _("Valuation Rate"),
- "fieldname": "val_rate",
- "fieldtype": "Currency",
- "width": 90,
- "convertible": "rate",
- "options": "currency",
- },
- {
- "label": _("Reorder Level"),
- "fieldname": "reorder_level",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Reorder Qty"),
- "fieldname": "reorder_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Company"),
- "fieldname": "company",
- "fieldtype": "Link",
- "options": "Company",
- "width": 100,
- },
- ]
- )
+ return item_warehouse_map
- if filters.get("show_stock_ageing_data"):
- columns += [
- {"label": _("Average Age"), "fieldname": "average_age", "width": 100},
- {"label": _("Earliest Age"), "fieldname": "earliest_age", "width": 100},
- {"label": _("Latest Age"), "fieldname": "latest_age", "width": 100},
- ]
+ def prepare_item_warehouse_map(self, item_warehouse_map, entry, group_by_key):
+ qty_dict = item_warehouse_map[group_by_key]
+ for field in self.inventory_dimensions:
+ qty_dict[field] = entry.get(field)
- if filters.get("show_variant_attributes"):
- columns += [
- {"label": att_name, "fieldname": att_name, "width": 100}
- for att_name in get_variants_attributes()
- ]
-
- return columns
-
-
-def apply_conditions(query, filters):
- sle = frappe.qb.DocType("Stock Ledger Entry")
- warehouse_table = frappe.qb.DocType("Warehouse")
-
- if not filters.get("from_date"):
- frappe.throw(_("'From Date' is required"))
-
- if to_date := filters.get("to_date"):
- query = query.where(sle.posting_date <= to_date)
- else:
- frappe.throw(_("'To Date' is required"))
-
- if company := filters.get("company"):
- query = query.where(sle.company == company)
-
- if filters.get("warehouse"):
- query = apply_warehouse_filter(query, sle, filters)
- elif warehouse_type := filters.get("warehouse_type"):
- query = (
- query.join(warehouse_table)
- .on(warehouse_table.name == sle.warehouse)
- .where(warehouse_table.warehouse_type == warehouse_type)
- )
-
- return query
-
-
-def get_stock_ledger_entries(filters: StockBalanceFilter, items: List[str]) -> List[SLEntry]:
- sle = frappe.qb.DocType("Stock Ledger Entry")
-
- query = (
- frappe.qb.from_(sle)
- .select(
- sle.item_code,
- sle.warehouse,
- sle.posting_date,
- sle.actual_qty,
- sle.valuation_rate,
- sle.company,
- sle.voucher_type,
- sle.qty_after_transaction,
- sle.stock_value_difference,
- sle.item_code.as_("name"),
- sle.voucher_no,
- sle.stock_value,
- sle.batch_no,
- )
- .where((sle.docstatus < 2) & (sle.is_cancelled == 0))
- .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
- .orderby(sle.creation)
- .orderby(sle.actual_qty)
- )
-
- inventory_dimension_fields = get_inventory_dimension_fields()
- if inventory_dimension_fields:
- for fieldname in inventory_dimension_fields:
- query = query.select(fieldname)
- if fieldname in filters and filters.get(fieldname):
- query = query.where(sle[fieldname].isin(filters.get(fieldname)))
-
- if items:
- query = query.where(sle.item_code.isin(items))
-
- query = apply_conditions(query, filters)
- return query.run(as_dict=True)
-
-
-def get_opening_vouchers(to_date):
- opening_vouchers = {"Stock Entry": [], "Stock Reconciliation": []}
-
- se = frappe.qb.DocType("Stock Entry")
- sr = frappe.qb.DocType("Stock Reconciliation")
-
- vouchers_data = (
- frappe.qb.from_(
- (
- frappe.qb.from_(se)
- .select(se.name, Coalesce("Stock Entry").as_("voucher_type"))
- .where((se.docstatus == 1) & (se.posting_date <= to_date) & (se.is_opening == "Yes"))
- )
- + (
- frappe.qb.from_(sr)
- .select(sr.name, Coalesce("Stock Reconciliation").as_("voucher_type"))
- .where((sr.docstatus == 1) & (sr.posting_date <= to_date) & (sr.purpose == "Opening Stock"))
- )
- ).select("voucher_type", "name")
- ).run(as_dict=True)
-
- if vouchers_data:
- for d in vouchers_data:
- opening_vouchers[d.voucher_type].append(d.name)
-
- return opening_vouchers
-
-
-def get_inventory_dimension_fields():
- return [dimension.fieldname for dimension in get_inventory_dimensions()]
-
-
-def get_item_warehouse_map(filters: StockBalanceFilter, sle: List[SLEntry]):
- iwb_map = {}
- from_date = getdate(filters.get("from_date"))
- to_date = getdate(filters.get("to_date"))
- opening_vouchers = get_opening_vouchers(to_date)
- float_precision = cint(frappe.db.get_default("float_precision")) or 3
- inventory_dimensions = get_inventory_dimension_fields()
-
- for d in sle:
- group_by_key = get_group_by_key(d, filters, inventory_dimensions)
- if group_by_key not in iwb_map:
- iwb_map[group_by_key] = frappe._dict(
- {
- "opening_qty": 0.0,
- "opening_val": 0.0,
- "in_qty": 0.0,
- "in_val": 0.0,
- "out_qty": 0.0,
- "out_val": 0.0,
- "bal_qty": 0.0,
- "bal_val": 0.0,
- "val_rate": 0.0,
- }
- )
-
- qty_dict = iwb_map[group_by_key]
- for field in inventory_dimensions:
- qty_dict[field] = d.get(field)
-
- if d.voucher_type == "Stock Reconciliation" and not d.batch_no:
- qty_diff = flt(d.qty_after_transaction) - flt(qty_dict.bal_qty)
+ if entry.voucher_type == "Stock Reconciliation" and (not entry.batch_no or entry.serial_no):
+ qty_diff = flt(entry.qty_after_transaction) - flt(qty_dict.bal_qty)
else:
- qty_diff = flt(d.actual_qty)
+ qty_diff = flt(entry.actual_qty)
- value_diff = flt(d.stock_value_difference)
+ value_diff = flt(entry.stock_value_difference)
- if d.posting_date < from_date or d.voucher_no in opening_vouchers.get(d.voucher_type, []):
+ if entry.posting_date < self.from_date or entry.voucher_no in self.opening_vouchers.get(
+ entry.voucher_type, []
+ ):
qty_dict.opening_qty += qty_diff
qty_dict.opening_val += value_diff
- elif d.posting_date >= from_date and d.posting_date <= to_date:
- if flt(qty_diff, float_precision) >= 0:
+ elif entry.posting_date >= self.from_date and entry.posting_date <= self.to_date:
+
+ if flt(qty_diff, self.float_precision) >= 0:
qty_dict.in_qty += qty_diff
qty_dict.in_val += value_diff
else:
qty_dict.out_qty += abs(qty_diff)
qty_dict.out_val += abs(value_diff)
- qty_dict.val_rate = d.valuation_rate
+ qty_dict.val_rate = entry.valuation_rate
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
- iwb_map = filter_items_with_no_transactions(iwb_map, float_precision, inventory_dimensions)
+ def initialize_data(self, item_warehouse_map, group_by_key, entry):
+ opening_data = self.opening_data.get(group_by_key, {})
- return iwb_map
+ item_warehouse_map[group_by_key] = frappe._dict(
+ {
+ "item_code": entry.item_code,
+ "warehouse": entry.warehouse,
+ "item_group": entry.item_group,
+ "company": entry.company,
+ "currency": self.company_currency,
+ "stock_uom": entry.stock_uom,
+ "item_name": entry.item_name,
+ "opening_qty": opening_data.get("bal_qty") or 0.0,
+ "opening_val": opening_data.get("bal_val") or 0.0,
+ "opening_fifo_queue": opening_data.get("fifo_queue") or [],
+ "in_qty": 0.0,
+ "in_val": 0.0,
+ "out_qty": 0.0,
+ "out_val": 0.0,
+ "bal_qty": opening_data.get("bal_qty") or 0.0,
+ "bal_val": opening_data.get("bal_val") or 0.0,
+ "val_rate": 0.0,
+ }
+ )
+
+ def get_group_by_key(self, row) -> tuple:
+ group_by_key = [row.company, row.item_code, row.warehouse]
+
+ for fieldname in self.inventory_dimensions:
+ if self.filters.get(fieldname):
+ group_by_key.append(row.get(fieldname))
+
+ return tuple(group_by_key)
+
+ def get_closing_balance(self) -> List[Dict[str, Any]]:
+ if self.filters.get("ignore_closing_balance"):
+ return []
+
+ table = frappe.qb.DocType("Closing Stock Balance")
+
+ query = (
+ frappe.qb.from_(table)
+ .select(table.name, table.to_date)
+ .where(
+ (table.docstatus == 1)
+ & (table.company == self.filters.company)
+ & ((table.to_date <= self.from_date))
+ )
+ .orderby(table.to_date, order=Order.desc)
+ .limit(1)
+ )
+
+ for fieldname in ["warehouse", "item_code", "item_group", "warehouse_type"]:
+ if self.filters.get(fieldname):
+ query = query.where(table[fieldname] == self.filters.get(fieldname))
+
+ return query.run(as_dict=True)
+
+ def prepare_stock_ledger_entries(self):
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ item_table = frappe.qb.DocType("Item")
+
+ query = (
+ frappe.qb.from_(sle)
+ .inner_join(item_table)
+ .on(sle.item_code == item_table.name)
+ .select(
+ sle.item_code,
+ sle.warehouse,
+ sle.posting_date,
+ sle.actual_qty,
+ sle.valuation_rate,
+ sle.company,
+ sle.voucher_type,
+ sle.qty_after_transaction,
+ sle.stock_value_difference,
+ sle.item_code.as_("name"),
+ sle.voucher_no,
+ sle.stock_value,
+ sle.batch_no,
+ sle.serial_no,
+ item_table.item_group,
+ item_table.stock_uom,
+ item_table.item_name,
+ )
+ .where((sle.docstatus < 2) & (sle.is_cancelled == 0))
+ .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
+ .orderby(sle.creation)
+ .orderby(sle.actual_qty)
+ )
+
+ query = self.apply_inventory_dimensions_filters(query, sle)
+ query = self.apply_warehouse_filters(query, sle)
+ query = self.apply_items_filters(query, item_table)
+ query = self.apply_date_filters(query, sle)
+
+ if self.filters.get("company"):
+ query = query.where(sle.company == self.filters.get("company"))
+
+ self.sle_entries = query.run(as_dict=True)
+
+ def apply_inventory_dimensions_filters(self, query, sle) -> str:
+ inventory_dimension_fields = self.get_inventory_dimension_fields()
+ if inventory_dimension_fields:
+ for fieldname in inventory_dimension_fields:
+ query = query.select(fieldname)
+ if self.filters.get(fieldname):
+ query = query.where(sle[fieldname].isin(self.filters.get(fieldname)))
+
+ return query
+
+ def apply_warehouse_filters(self, query, sle) -> str:
+ warehouse_table = frappe.qb.DocType("Warehouse")
+
+ if self.filters.get("warehouse"):
+ query = apply_warehouse_filter(query, sle, self.filters)
+ elif warehouse_type := self.filters.get("warehouse_type"):
+ query = (
+ query.join(warehouse_table)
+ .on(warehouse_table.name == sle.warehouse)
+ .where(warehouse_table.warehouse_type == warehouse_type)
+ )
+
+ return query
+
+ def apply_items_filters(self, query, item_table) -> str:
+ if item_group := self.filters.get("item_group"):
+ children = get_descendants_of("Item Group", item_group, ignore_permissions=True)
+ query = query.where(item_table.item_group.isin(children + [item_group]))
+
+ for field in ["item_code", "brand"]:
+ if not self.filters.get(field):
+ continue
+
+ query = query.where(item_table[field] == self.filters.get(field))
+
+ return query
+
+ def apply_date_filters(self, query, sle) -> str:
+ if not self.filters.ignore_closing_balance and self.start_from:
+ query = query.where(sle.posting_date >= self.start_from)
+
+ if self.to_date:
+ query = query.where(sle.posting_date <= self.to_date)
+
+ return query
+
+ def get_columns(self):
+ columns = [
+ {
+ "label": _("Item"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 100,
+ },
+ {"label": _("Item Name"), "fieldname": "item_name", "width": 150},
+ {
+ "label": _("Item Group"),
+ "fieldname": "item_group",
+ "fieldtype": "Link",
+ "options": "Item Group",
+ "width": 100,
+ },
+ {
+ "label": _("Warehouse"),
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": 100,
+ },
+ ]
+
+ for dimension in get_inventory_dimensions():
+ columns.append(
+ {
+ "label": _(dimension.doctype),
+ "fieldname": dimension.fieldname,
+ "fieldtype": "Link",
+ "options": dimension.doctype,
+ "width": 110,
+ }
+ )
+
+ columns.extend(
+ [
+ {
+ "label": _("Stock UOM"),
+ "fieldname": "stock_uom",
+ "fieldtype": "Link",
+ "options": "UOM",
+ "width": 90,
+ },
+ {
+ "label": _("Balance Qty"),
+ "fieldname": "bal_qty",
+ "fieldtype": "Float",
+ "width": 100,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Balance Value"),
+ "fieldname": "bal_val",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "currency",
+ },
+ {
+ "label": _("Opening Qty"),
+ "fieldname": "opening_qty",
+ "fieldtype": "Float",
+ "width": 100,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Opening Value"),
+ "fieldname": "opening_val",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "currency",
+ },
+ {
+ "label": _("In Qty"),
+ "fieldname": "in_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {"label": _("In Value"), "fieldname": "in_val", "fieldtype": "Float", "width": 80},
+ {
+ "label": _("Out Qty"),
+ "fieldname": "out_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {"label": _("Out Value"), "fieldname": "out_val", "fieldtype": "Float", "width": 80},
+ {
+ "label": _("Valuation Rate"),
+ "fieldname": "val_rate",
+ "fieldtype": "Currency",
+ "width": 90,
+ "convertible": "rate",
+ "options": "currency",
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 100,
+ },
+ ]
+ )
+
+ if self.filters.get("show_stock_ageing_data"):
+ columns += [
+ {"label": _("Average Age"), "fieldname": "average_age", "width": 100},
+ {"label": _("Earliest Age"), "fieldname": "earliest_age", "width": 100},
+ {"label": _("Latest Age"), "fieldname": "latest_age", "width": 100},
+ ]
+
+ if self.filters.get("show_variant_attributes"):
+ columns += [
+ {"label": att_name, "fieldname": att_name, "width": 100}
+ for att_name in get_variants_attributes()
+ ]
+
+ return columns
+
+ def add_additional_uom_columns(self):
+ if not self.filters.get("include_uom"):
+ return
+
+ conversion_factors = self.get_itemwise_conversion_factor()
+ add_additional_uom_columns(self.columns, self.data, self.filters.include_uom, conversion_factors)
+
+ def get_itemwise_conversion_factor(self):
+ items = []
+ if self.filters.item_code or self.filters.item_group:
+ items = [d.item_code for d in self.data]
+
+ table = frappe.qb.DocType("UOM Conversion Detail")
+ query = (
+ frappe.qb.from_(table)
+ .select(
+ table.conversion_factor,
+ table.parent,
+ )
+ .where((table.parenttype == "Item") & (table.uom == self.filters.include_uom))
+ )
+
+ if items:
+ query = query.where(table.parent.isin(items))
+
+ result = query.run(as_dict=1)
+ if not result:
+ return {}
+
+ return {d.parent: d.conversion_factor for d in result}
+
+ def get_variant_values_for(self):
+ """Returns variant values for items."""
+ attribute_map = {}
+ items = []
+ if self.filters.item_code or self.filters.item_group:
+ items = [d.item_code for d in self.data]
+
+ filters = {}
+ if items:
+ filters = {"parent": ("in", items)}
+
+ attribute_info = frappe.get_all(
+ "Item Variant Attribute",
+ fields=["parent", "attribute", "attribute_value"],
+ filters=filters,
+ )
+
+ for attr in attribute_info:
+ attribute_map.setdefault(attr["parent"], {})
+ attribute_map[attr["parent"]].update({attr["attribute"]: attr["attribute_value"]})
+
+ return attribute_map
+
+ def get_opening_vouchers(self):
+ opening_vouchers = {"Stock Entry": [], "Stock Reconciliation": []}
+
+ se = frappe.qb.DocType("Stock Entry")
+ sr = frappe.qb.DocType("Stock Reconciliation")
+
+ vouchers_data = (
+ frappe.qb.from_(
+ (
+ frappe.qb.from_(se)
+ .select(se.name, Coalesce("Stock Entry").as_("voucher_type"))
+ .where((se.docstatus == 1) & (se.posting_date <= self.to_date) & (se.is_opening == "Yes"))
+ )
+ + (
+ frappe.qb.from_(sr)
+ .select(sr.name, Coalesce("Stock Reconciliation").as_("voucher_type"))
+ .where(
+ (sr.docstatus == 1) & (sr.posting_date <= self.to_date) & (sr.purpose == "Opening Stock")
+ )
+ )
+ ).select("voucher_type", "name")
+ ).run(as_dict=True)
+
+ if vouchers_data:
+ for d in vouchers_data:
+ opening_vouchers[d.voucher_type].append(d.name)
+
+ return opening_vouchers
+
+ @staticmethod
+ def get_inventory_dimension_fields():
+ return [dimension.fieldname for dimension in get_inventory_dimensions()]
+
+ @staticmethod
+ def get_opening_fifo_queue(report_data):
+ opening_fifo_queue = report_data.get("opening_fifo_queue") or []
+ for row in opening_fifo_queue:
+ row[1] = getdate(row[1])
+
+ return opening_fifo_queue
-def get_group_by_key(row, filters, inventory_dimension_fields) -> tuple:
- group_by_key = [row.company, row.item_code, row.warehouse]
-
- for fieldname in inventory_dimension_fields:
- if filters.get(fieldname):
- group_by_key.append(row.get(fieldname))
-
- return tuple(group_by_key)
-
-
-def filter_items_with_no_transactions(iwb_map, float_precision: float, inventory_dimensions: list):
+def filter_items_with_no_transactions(
+ iwb_map, float_precision: float, inventory_dimensions: list = None
+):
pop_keys = []
for group_by_key in iwb_map:
qty_dict = iwb_map[group_by_key]
no_transactions = True
for key, val in qty_dict.items():
- if key in inventory_dimensions:
+ if inventory_dimensions and key in inventory_dimensions:
+ continue
+
+ if key in [
+ "item_code",
+ "warehouse",
+ "item_name",
+ "item_group",
+ "projecy",
+ "stock_uom",
+ "company",
+ "opening_fifo_queue",
+ ]:
continue
val = flt(val, float_precision)
@@ -445,96 +594,6 @@
return iwb_map
-def get_items(filters: StockBalanceFilter) -> List[str]:
- "Get items based on item code, item group or brand."
- if item_code := filters.get("item_code"):
- return [item_code]
- else:
- item_filters = {}
- if item_group := filters.get("item_group"):
- children = get_descendants_of("Item Group", item_group, ignore_permissions=True)
- item_filters["item_group"] = ("in", children + [item_group])
- if brand := filters.get("brand"):
- item_filters["brand"] = brand
-
- return frappe.get_all("Item", filters=item_filters, pluck="name", order_by=None)
-
-
-def get_item_details(items: List[str], sle: List[SLEntry], filters: StockBalanceFilter):
- item_details = {}
- if not items:
- items = list(set(d.item_code for d in sle))
-
- if not items:
- return item_details
-
- item_table = frappe.qb.DocType("Item")
-
- query = (
- frappe.qb.from_(item_table)
- .select(
- item_table.name,
- item_table.item_name,
- item_table.description,
- item_table.item_group,
- item_table.brand,
- item_table.stock_uom,
- )
- .where(item_table.name.isin(items))
- )
-
- if uom := filters.get("include_uom"):
- uom_conv_detail = frappe.qb.DocType("UOM Conversion Detail")
- query = (
- query.left_join(uom_conv_detail)
- .on((uom_conv_detail.parent == item_table.name) & (uom_conv_detail.uom == uom))
- .select(uom_conv_detail.conversion_factor)
- )
-
- result = query.run(as_dict=1)
-
- for item_table in result:
- item_details.setdefault(item_table.name, item_table)
-
- if filters.get("show_variant_attributes"):
- variant_values = get_variant_values_for(list(item_details))
- item_details = {k: v.update(variant_values.get(k, {})) for k, v in item_details.items()}
-
- return item_details
-
-
-def get_item_reorder_details(items):
- item_reorder_details = frappe._dict()
-
- if items:
- item_reorder_details = frappe.get_all(
- "Item Reorder",
- ["parent", "warehouse", "warehouse_reorder_qty", "warehouse_reorder_level"],
- filters={"parent": ("in", items)},
- )
-
- return dict((d.parent + d.warehouse, d) for d in item_reorder_details)
-
-
def get_variants_attributes() -> List[str]:
"""Return all item variant attributes."""
return frappe.get_all("Item Attribute", pluck="name")
-
-
-def get_variant_values_for(items):
- """Returns variant values for items."""
- attribute_map = {}
-
- attribute_info = frappe.get_all(
- "Item Variant Attribute",
- ["parent", "attribute", "attribute_value"],
- {
- "parent": ("in", items),
- },
- )
-
- for attr in attribute_info:
- attribute_map.setdefault(attr["parent"], {})
- attribute_map[attr["parent"]].update({attr["attribute"]: attr["attribute_value"]})
-
- return attribute_map
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
index abbb33b..5dbdcef 100644
--- a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
@@ -8,15 +8,15 @@
import frappe
from frappe import _
from frappe.query_builder.functions import Count
-from frappe.utils import flt
+from frappe.utils import cint, flt, getdate
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
-from erpnext.stock.report.stock_balance.stock_balance import (
+from erpnext.stock.report.stock_analytics.stock_analytics import (
get_item_details,
- get_item_warehouse_map,
get_items,
get_stock_ledger_entries,
)
+from erpnext.stock.report.stock_balance.stock_balance import filter_items_with_no_transactions
from erpnext.stock.utils import is_reposting_item_valuation_in_progress
@@ -32,7 +32,7 @@
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
- item_map = get_item_details(items, sle, filters)
+ item_map = get_item_details(items, sle)
iwb_map = get_item_warehouse_map(filters, sle)
warehouse_list = get_warehouse_list(filters)
item_ageing = FIFOSlots(filters).generate()
@@ -128,3 +128,59 @@
for wh in warehouse_list:
columns += [_(wh.name) + ":Int:100"]
+
+
+def get_item_warehouse_map(filters, sle):
+ iwb_map = {}
+ from_date = getdate(filters.get("from_date"))
+ to_date = getdate(filters.get("to_date"))
+ float_precision = cint(frappe.db.get_default("float_precision")) or 3
+
+ for d in sle:
+ group_by_key = get_group_by_key(d)
+ if group_by_key not in iwb_map:
+ iwb_map[group_by_key] = frappe._dict(
+ {
+ "opening_qty": 0.0,
+ "opening_val": 0.0,
+ "in_qty": 0.0,
+ "in_val": 0.0,
+ "out_qty": 0.0,
+ "out_val": 0.0,
+ "bal_qty": 0.0,
+ "bal_val": 0.0,
+ "val_rate": 0.0,
+ }
+ )
+
+ qty_dict = iwb_map[group_by_key]
+ if d.voucher_type == "Stock Reconciliation" and not d.batch_no:
+ qty_diff = flt(d.qty_after_transaction) - flt(qty_dict.bal_qty)
+ else:
+ qty_diff = flt(d.actual_qty)
+
+ value_diff = flt(d.stock_value_difference)
+
+ if d.posting_date < from_date:
+ qty_dict.opening_qty += qty_diff
+ qty_dict.opening_val += value_diff
+
+ elif d.posting_date >= from_date and d.posting_date <= to_date:
+ if flt(qty_diff, float_precision) >= 0:
+ qty_dict.in_qty += qty_diff
+ qty_dict.in_val += value_diff
+ else:
+ qty_dict.out_qty += abs(qty_diff)
+ qty_dict.out_val += abs(value_diff)
+
+ qty_dict.val_rate = d.valuation_rate
+ qty_dict.bal_qty += qty_diff
+ qty_dict.bal_val += value_diff
+
+ iwb_map = filter_items_with_no_transactions(iwb_map, float_precision)
+
+ return iwb_map
+
+
+def get_group_by_key(row) -> tuple:
+ return (row.company, row.item_code, row.warehouse)
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index fb52697..10654dd 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -256,8 +256,6 @@
if isinstance(args, str):
args = json.loads(args)
- voucher_no = args.get("voucher_no") or args.get("name")
-
in_rate = None
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
@@ -280,12 +278,13 @@
in_rate = (
_get_fifo_lifo_rate(previous_stock_queue, args.get("qty") or 0, valuation_method)
if previous_stock_queue
- else 0
+ else None
)
elif valuation_method == "Moving Average":
- in_rate = previous_sle.get("valuation_rate") or 0
+ in_rate = previous_sle.get("valuation_rate")
if in_rate is None:
+ voucher_no = args.get("voucher_no") or args.get("name")
in_rate = get_valuation_rate(
args.get("item_code"),
args.get("warehouse"),