blob: db71fe280ac2041991dd6f23b5cf8cf97efefeec [file] [log] [blame]
Maricad6078aa2022-06-17 15:13:13 +05301# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
Rushabh Mehtae67d1fb2013-08-05 14:59:54 +05302# License: GNU General Public License v3. See license.txt
Nabin Hait902e8602013-01-08 18:29:24 +05303
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +05304import copy
Nabin Hait26d46552013-01-09 15:23:05 +05305import json
Ankush Menatecdb4932022-04-17 19:06:13 +05306from typing import Optional, Set, Tuple
Chillar Anand915b3432021-09-02 16:44:59 +05307
8import frappe
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +05309from frappe import _, scrub
Chillar Anand915b3432021-09-02 16:44:59 +053010from frappe.model.meta import get_field_precision
Rohit Waghchaurebb954512023-06-02 00:11:43 +053011from frappe.query_builder import Case
Ankush Menate1c16872022-04-21 20:01:48 +053012from frappe.query_builder.functions import CombineDatetime, Sum
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +053013from frappe.utils import (
14 cint,
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +053015 flt,
16 get_link_to_form,
17 getdate,
18 gzip_compress,
19 gzip_decompress,
20 now,
21 nowdate,
22 parse_json,
23)
Achilles Rasquinha361366e2018-02-14 17:08:59 +053024
Chillar Anand915b3432021-09-02 16:44:59 +053025import erpnext
Ankush Menatcef84c22021-12-03 12:18:59 +053026from erpnext.stock.doctype.bin.bin import update_qty as update_bin_qty
s-aga-rf0acb202023-04-12 14:13:54 +053027from erpnext.stock.doctype.stock_reservation_entry.stock_reservation_entry import (
28 get_sre_reserved_qty_for_item_and_warehouse as get_reserved_stock,
29)
Chillar Anand915b3432021-09-02 16:44:59 +053030from erpnext.stock.utils import (
Chillar Anand915b3432021-09-02 16:44:59 +053031 get_incoming_outgoing_rate_for_cancel,
Deepesh Garg6f107da2021-10-12 20:15:55 +053032 get_or_make_bin,
Chillar Anand915b3432021-09-02 16:44:59 +053033 get_valuation_method,
34)
Ankush Menatb534fee2022-02-19 20:58:36 +053035from erpnext.stock.valuation import FIFOValuation, LIFOValuation, round_off_if_near_zero
Chillar Anand915b3432021-09-02 16:44:59 +053036
Nabin Hait97bce3a2021-07-12 13:24:43 +053037
Ankush Menat494bd9e2022-03-28 18:52:46 +053038class NegativeStockError(frappe.ValidationError):
39 pass
40
41
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +053042class SerialNoExistsInFutureTransaction(frappe.ValidationError):
43 pass
Nabin Hait902e8602013-01-08 18:29:24 +053044
Anand Doshi5b004ff2013-09-25 19:55:41 +053045
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +053046def make_sl_entries(sl_entries, allow_negative_stock=False, via_landed_cost_voucher=False):
Ankush Menat494bd9e2022-03-28 18:52:46 +053047 """Create SL entries from SL entry dicts
Ankush Menateb8495a2022-03-02 12:01:51 +053048
Ankush Menat494bd9e2022-03-28 18:52:46 +053049 args:
50 - allow_negative_stock: disable negative stock valiations if true
51 - via_landed_cost_voucher: landed cost voucher cancels and reposts
52 entries of purchase document. This flag is used to identify if
53 cancellation and repost is happening via landed cost voucher, in
54 such cases certain validations need to be ignored (like negative
55 stock)
Ankush Menateb8495a2022-03-02 12:01:51 +053056 """
Rohit Waghchaure4d81d452021-06-15 10:21:44 +053057 from erpnext.controllers.stock_controller import future_sle_exists
Ankush Menat494bd9e2022-03-28 18:52:46 +053058
Nabin Haitca775742013-09-26 16:16:44 +053059 if sl_entries:
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +053060 cancel = sl_entries[0].get("is_cancelled")
Nabin Haitca775742013-09-26 16:16:44 +053061 if cancel:
Nabin Hait186a0452021-02-18 14:14:21 +053062 validate_cancellation(sl_entries)
Ankush Menat494bd9e2022-03-28 18:52:46 +053063 set_as_cancel(sl_entries[0].get("voucher_type"), sl_entries[0].get("voucher_no"))
Nabin Haitdc82d4f2014-04-07 12:02:57 +053064
Rohit Waghchaure4d81d452021-06-15 10:21:44 +053065 args = get_args_for_future_sle(sl_entries[0])
66 future_sle_exists(args, sl_entries)
67
Nabin Haitca775742013-09-26 16:16:44 +053068 for sle in sl_entries:
Ankush Menatefc4b942022-03-02 11:19:12 +053069 if sle.serial_no and not via_landed_cost_voucher:
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +053070 validate_serial_no(sle)
71
Nabin Haita77b8c92020-12-21 14:45:50 +053072 if cancel:
Ankush Menat494bd9e2022-03-28 18:52:46 +053073 sle["actual_qty"] = -flt(sle.get("actual_qty"))
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +053074
Ankush Menat494bd9e2022-03-28 18:52:46 +053075 if sle["actual_qty"] < 0 and not sle.get("outgoing_rate"):
76 sle["outgoing_rate"] = get_incoming_outgoing_rate_for_cancel(
77 sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no
78 )
79 sle["incoming_rate"] = 0.0
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +053080
Ankush Menat494bd9e2022-03-28 18:52:46 +053081 if sle["actual_qty"] > 0 and not sle.get("incoming_rate"):
82 sle["incoming_rate"] = get_incoming_outgoing_rate_for_cancel(
83 sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no
84 )
85 sle["outgoing_rate"] = 0.0
Nabin Haitdc82d4f2014-04-07 12:02:57 +053086
Ankush Menat494bd9e2022-03-28 18:52:46 +053087 if sle.get("actual_qty") or sle.get("voucher_type") == "Stock Reconciliation":
Nabin Haita77b8c92020-12-21 14:45:50 +053088 sle_doc = make_entry(sle, allow_negative_stock, via_landed_cost_voucher)
Deepesh Gargb4be2922021-01-28 13:09:56 +053089
Nabin Haita77b8c92020-12-21 14:45:50 +053090 args = sle_doc.as_dict()
marination40389772021-07-02 17:13:45 +053091
92 if sle.get("voucher_type") == "Stock Reconciliation":
93 # preserve previous_qty_after_transaction for qty reposting
94 args.previous_qty_after_transaction = sle.get("previous_qty_after_transaction")
95
Ankush Menat494bd9e2022-03-28 18:52:46 +053096 is_stock_item = frappe.get_cached_value("Item", args.get("item_code"), "is_stock_item")
Ankush Menatcef84c22021-12-03 12:18:59 +053097 if is_stock_item:
98 bin_name = get_or_make_bin(args.get("item_code"), args.get("warehouse"))
Ankush Menatcef84c22021-12-03 12:18:59 +053099 repost_current_voucher(args, allow_negative_stock, via_landed_cost_voucher)
Ankush Menatff9a6e82021-12-20 15:07:41 +0530100 update_bin_qty(bin_name, args)
Ankush Menatcef84c22021-12-03 12:18:59 +0530101 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530102 frappe.msgprint(
103 _("Item {0} ignored since it is not a stock item").format(args.get("item_code"))
104 )
105
Ankush Menatcef84c22021-12-03 12:18:59 +0530106
107def repost_current_voucher(args, allow_negative_stock=False, via_landed_cost_voucher=False):
108 if args.get("actual_qty") or args.get("voucher_type") == "Stock Reconciliation":
109 if not args.get("posting_date"):
110 args["posting_date"] = nowdate()
111
marination7a5fd712022-07-04 17:46:54 +0530112 if not (args.get("is_cancelled") and via_landed_cost_voucher):
113 # Reposts only current voucher SL Entries
114 # Updates valuation rate, stock value, stock queue for current transaction
115 update_entries_after(
116 {
117 "item_code": args.get("item_code"),
118 "warehouse": args.get("warehouse"),
119 "posting_date": args.get("posting_date"),
120 "posting_time": args.get("posting_time"),
121 "voucher_type": args.get("voucher_type"),
122 "voucher_no": args.get("voucher_no"),
123 "sle_id": args.get("name"),
124 "creation": args.get("creation"),
125 },
126 allow_negative_stock=allow_negative_stock,
127 via_landed_cost_voucher=via_landed_cost_voucher,
128 )
Ankush Menatcef84c22021-12-03 12:18:59 +0530129
130 # update qty in future sle and Validate negative qty
marination7a5fd712022-07-04 17:46:54 +0530131 # For LCV: update future balances with -ve LCV SLE, which will be balanced by +ve LCV SLE
Ankush Menatcef84c22021-12-03 12:18:59 +0530132 update_qty_in_future_sle(args, allow_negative_stock)
133
Nabin Haitadeb9762014-10-06 11:53:52 +0530134
Rohit Waghchaure4d81d452021-06-15 10:21:44 +0530135def get_args_for_future_sle(row):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530136 return frappe._dict(
137 {
138 "voucher_type": row.get("voucher_type"),
139 "voucher_no": row.get("voucher_no"),
140 "posting_date": row.get("posting_date"),
141 "posting_time": row.get("posting_time"),
142 }
143 )
144
Rohit Waghchaure4d81d452021-06-15 10:21:44 +0530145
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530146def validate_serial_no(sle):
147 from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
Ankush Menat66bf21f2022-01-16 20:45:59 +0530148
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530149 for sn in get_serial_nos(sle.serial_no):
150 args = copy.deepcopy(sle)
151 args.serial_no = sn
Ankush Menat494bd9e2022-03-28 18:52:46 +0530152 args.warehouse = ""
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530153
154 vouchers = []
Ankush Menat494bd9e2022-03-28 18:52:46 +0530155 for row in get_stock_ledger_entries(args, ">"):
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530156 voucher_type = frappe.bold(row.voucher_type)
157 voucher_no = frappe.bold(get_link_to_form(row.voucher_type, row.voucher_no))
Ankush Menat494bd9e2022-03-28 18:52:46 +0530158 vouchers.append(f"{voucher_type} {voucher_no}")
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530159
160 if vouchers:
161 serial_no = frappe.bold(sn)
Ankush Menat494bd9e2022-03-28 18:52:46 +0530162 msg = (
163 f"""The serial no {serial_no} has been used in the future transactions so you need to cancel them first.
164 The list of the transactions are as below."""
165 + "<br><br><ul><li>"
166 )
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530167
Ankush Menat494bd9e2022-03-28 18:52:46 +0530168 msg += "</li><li>".join(vouchers)
169 msg += "</li></ul>"
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530170
Ankush Menat494bd9e2022-03-28 18:52:46 +0530171 title = "Cannot Submit" if not sle.get("is_cancelled") else "Cannot Cancel"
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +0530172 frappe.throw(_(msg), title=_(title), exc=SerialNoExistsInFutureTransaction)
173
Ankush Menat494bd9e2022-03-28 18:52:46 +0530174
Nabin Hait186a0452021-02-18 14:14:21 +0530175def validate_cancellation(args):
176 if args[0].get("is_cancelled"):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530177 repost_entry = frappe.db.get_value(
178 "Repost Item Valuation",
179 {"voucher_type": args[0].voucher_type, "voucher_no": args[0].voucher_no, "docstatus": 1},
180 ["name", "status"],
181 as_dict=1,
182 )
Nabin Hait186a0452021-02-18 14:14:21 +0530183
184 if repost_entry:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530185 if repost_entry.status == "In Progress":
186 frappe.throw(
187 _(
188 "Cannot cancel the transaction. Reposting of item valuation on submission is not completed yet."
189 )
190 )
191 if repost_entry.status == "Queued":
Nabin Haitd46b2362021-02-23 16:38:52 +0530192 doc = frappe.get_doc("Repost Item Valuation", repost_entry.name)
Ankush Menata2819982022-04-08 13:20:25 +0530193 doc.status = "Skipped"
Ankush Menataa024fc2021-11-18 12:51:26 +0530194 doc.flags.ignore_permissions = True
Nabin Haitd46b2362021-02-23 16:38:52 +0530195 doc.cancel()
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530196
Ankush Menat494bd9e2022-03-28 18:52:46 +0530197
Nabin Hait9653f602013-08-20 15:37:33 +0530198def set_as_cancel(voucher_type, voucher_no):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530199 frappe.db.sql(
200 """update `tabStock Ledger Entry` set is_cancelled=1,
Nabin Hait9653f602013-08-20 15:37:33 +0530201 modified=%s, modified_by=%s
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +0530202 where voucher_type=%s and voucher_no=%s and is_cancelled = 0""",
Ankush Menat494bd9e2022-03-28 18:52:46 +0530203 (now(), frappe.session.user, voucher_type, voucher_no),
204 )
205
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530206
Nabin Hait54c865e2015-03-27 15:38:31 +0530207def make_entry(args, allow_negative_stock=False, via_landed_cost_voucher=False):
Saqib Ansaric7fc6092021-10-12 13:30:40 +0530208 args["doctype"] = "Stock Ledger Entry"
Rushabh Mehtaa504f062014-04-04 12:16:26 +0530209 sle = frappe.get_doc(args)
Anand Doshi6dfd4302015-02-10 14:41:27 +0530210 sle.flags.ignore_permissions = 1
Ankush Menat494bd9e2022-03-28 18:52:46 +0530211 sle.allow_negative_stock = allow_negative_stock
Nabin Hait54c865e2015-03-27 15:38:31 +0530212 sle.via_landed_cost_voucher = via_landed_cost_voucher
Nabin Haitaeba24e2013-08-23 15:17:36 +0530213 sle.submit()
Nabin Haita77b8c92020-12-21 14:45:50 +0530214 return sle
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530215
Ankush Menat494bd9e2022-03-28 18:52:46 +0530216
217def repost_future_sle(
218 args=None,
219 voucher_type=None,
220 voucher_no=None,
221 allow_negative_stock=None,
222 via_landed_cost_voucher=False,
223 doc=None,
224):
Nabin Haite1fa7232022-07-20 15:19:09 +0530225 if not args:
226 args = [] # set args to empty list if None to avoid enumerate error
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530227
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530228 reposting_data = {}
229 if doc and doc.reposting_data_file:
230 reposting_data = get_reposting_data(doc.reposting_data_file)
231
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530232 items_to_be_repost = get_items_to_be_repost(
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530233 voucher_type=voucher_type, voucher_no=voucher_no, doc=doc, reposting_data=reposting_data
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530234 )
235 if items_to_be_repost:
236 args = items_to_be_repost
Deepesh Gargb4be2922021-01-28 13:09:56 +0530237
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530238 distinct_item_warehouses = get_distinct_item_warehouse(args, doc, reposting_data=reposting_data)
239 affected_transactions = get_affected_transactions(doc, reposting_data=reposting_data)
Nabin Haita77b8c92020-12-21 14:45:50 +0530240
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530241 i = get_current_index(doc) or 0
Nabin Haita77b8c92020-12-21 14:45:50 +0530242 while i < len(args):
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530243 validate_item_warehouse(args[i])
244
Ankush Menat494bd9e2022-03-28 18:52:46 +0530245 obj = update_entries_after(
246 {
247 "item_code": args[i].get("item_code"),
248 "warehouse": args[i].get("warehouse"),
249 "posting_date": args[i].get("posting_date"),
250 "posting_time": args[i].get("posting_time"),
251 "creation": args[i].get("creation"),
252 "distinct_item_warehouses": distinct_item_warehouses,
253 },
254 allow_negative_stock=allow_negative_stock,
255 via_landed_cost_voucher=via_landed_cost_voucher,
256 )
Ankush Menatecdb4932022-04-17 19:06:13 +0530257 affected_transactions.update(obj.affected_transactions)
Nabin Haita77b8c92020-12-21 14:45:50 +0530258
Ankush Menat494bd9e2022-03-28 18:52:46 +0530259 distinct_item_warehouses[
260 (args[i].get("item_code"), args[i].get("warehouse"))
261 ].reposting_status = True
Deepesh Gargb4be2922021-01-28 13:09:56 +0530262
Nabin Hait97bce3a2021-07-12 13:24:43 +0530263 if obj.new_items_found:
Ankush Menat8fe5feb2021-11-04 19:48:32 +0530264 for item_wh, data in distinct_item_warehouses.items():
Ankush Menat494bd9e2022-03-28 18:52:46 +0530265 if ("args_idx" not in data and not data.reposting_status) or (
266 data.sle_changed and data.reposting_status
267 ):
Nabin Hait97bce3a2021-07-12 13:24:43 +0530268 data.args_idx = len(args)
269 args.append(data.sle)
270 elif data.sle_changed and not data.reposting_status:
271 args[data.args_idx] = data.sle
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530272
Nabin Hait97bce3a2021-07-12 13:24:43 +0530273 data.sle_changed = False
Nabin Haita77b8c92020-12-21 14:45:50 +0530274 i += 1
275
Rohit Waghchaure78c8bb22022-07-04 20:24:18 +0530276 if doc:
Ankush Menatecdb4932022-04-17 19:06:13 +0530277 update_args_in_repost_item_valuation(
278 doc, i, args, distinct_item_warehouses, affected_transactions
279 )
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530280
Ankush Menat494bd9e2022-03-28 18:52:46 +0530281
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530282def get_reposting_data(file_path) -> dict:
283 file_name = frappe.db.get_value(
284 "File",
285 {
286 "file_url": file_path,
287 "attached_to_field": "reposting_data_file",
288 },
289 "name",
290 )
291
292 if not file_name:
293 return frappe._dict()
294
295 attached_file = frappe.get_doc("File", file_name)
296
297 data = gzip_decompress(attached_file.get_content())
298 if data := json.loads(data.decode("utf-8")):
299 data = data
300
301 return parse_json(data)
302
303
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530304def validate_item_warehouse(args):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530305 for field in ["item_code", "warehouse", "posting_date", "posting_time"]:
s-aga-reeda2642022-01-12 20:55:30 +0530306 if args.get(field) in [None, ""]:
s-aga-rba77da02022-11-28 18:01:30 +0530307 validation_msg = f"The field {frappe.unscrub(field)} is required for the reposting"
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530308 frappe.throw(_(validation_msg))
309
Ankush Menat494bd9e2022-03-28 18:52:46 +0530310
Ankush Menatecdb4932022-04-17 19:06:13 +0530311def update_args_in_repost_item_valuation(
312 doc, index, args, distinct_item_warehouses, affected_transactions
313):
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530314 if not doc.items_to_be_repost:
315 file_name = ""
316 if doc.reposting_data_file:
317 file_name = get_reposting_file_name(doc.doctype, doc.name)
318 # frappe.delete_doc("File", file_name, ignore_permissions=True, delete_permanently=True)
319
320 doc.reposting_data_file = create_json_gz_file(
321 {
322 "items_to_be_repost": args,
323 "distinct_item_and_warehouse": {str(k): v for k, v in distinct_item_warehouses.items()},
324 "affected_transactions": affected_transactions,
325 },
326 doc,
327 file_name,
328 )
329
330 doc.db_set(
331 {
332 "current_index": index,
333 "total_reposting_count": len(args),
334 "reposting_data_file": doc.reposting_data_file,
335 }
336 )
337
338 else:
339 doc.db_set(
340 {
341 "items_to_be_repost": json.dumps(args, default=str),
342 "distinct_item_and_warehouse": json.dumps(
343 {str(k): v for k, v in distinct_item_warehouses.items()}, default=str
344 ),
345 "current_index": index,
346 "affected_transactions": frappe.as_json(affected_transactions),
347 }
348 )
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530349
Ankush Menatecdb4932022-04-17 19:06:13 +0530350 if not frappe.flags.in_test:
351 frappe.db.commit()
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530352
Ankush Menat494bd9e2022-03-28 18:52:46 +0530353 frappe.publish_realtime(
354 "item_reposting_progress",
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530355 {
356 "name": doc.name,
357 "items_to_be_repost": json.dumps(args, default=str),
358 "current_index": index,
359 "total_reposting_count": len(args),
360 },
Ankush Menatc0642cf2023-07-29 15:02:11 +0530361 doctype=doc.doctype,
362 docname=doc.name,
Ankush Menat494bd9e2022-03-28 18:52:46 +0530363 )
364
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530365
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530366def get_reposting_file_name(dt, dn):
367 return frappe.db.get_value(
368 "File",
369 {
370 "attached_to_doctype": dt,
371 "attached_to_name": dn,
372 "attached_to_field": "reposting_data_file",
373 },
374 "name",
375 )
376
377
378def create_json_gz_file(data, doc, file_name=None) -> str:
379 encoded_content = frappe.safe_encode(frappe.as_json(data))
380 compressed_content = gzip_compress(encoded_content)
381
382 if not file_name:
383 json_filename = f"{scrub(doc.doctype)}-{scrub(doc.name)}.json.gz"
384 _file = frappe.get_doc(
385 {
386 "doctype": "File",
387 "file_name": json_filename,
388 "attached_to_doctype": doc.doctype,
389 "attached_to_name": doc.name,
390 "attached_to_field": "reposting_data_file",
391 "content": compressed_content,
392 "is_private": 1,
393 }
394 )
395 _file.save(ignore_permissions=True)
396
397 return _file.file_url
398 else:
399 file_doc = frappe.get_doc("File", file_name)
400 path = file_doc.get_full_path()
401
402 with open(path, "wb") as f:
403 f.write(compressed_content)
404
405 return doc.reposting_data_file
406
407
408def get_items_to_be_repost(voucher_type=None, voucher_no=None, doc=None, reposting_data=None):
409 if not reposting_data and doc and doc.reposting_data_file:
410 reposting_data = get_reposting_data(doc.reposting_data_file)
411
412 if reposting_data and reposting_data.items_to_be_repost:
413 return reposting_data.items_to_be_repost
414
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530415 items_to_be_repost = []
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530416
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530417 if doc and doc.items_to_be_repost:
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530418 items_to_be_repost = json.loads(doc.items_to_be_repost) or []
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530419
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530420 if not items_to_be_repost and voucher_type and voucher_no:
421 items_to_be_repost = frappe.db.get_all(
422 "Stock Ledger Entry",
423 filters={"voucher_type": voucher_type, "voucher_no": voucher_no},
424 fields=["item_code", "warehouse", "posting_date", "posting_time", "creation"],
425 order_by="creation asc",
426 group_by="item_code, warehouse",
427 )
428
Nabin Haite1fa7232022-07-20 15:19:09 +0530429 return items_to_be_repost or []
Nabin Hait74c281c2013-08-19 16:17:18 +0530430
Ankush Menat494bd9e2022-03-28 18:52:46 +0530431
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530432def get_distinct_item_warehouse(args=None, doc=None, reposting_data=None):
433 if not reposting_data and doc and doc.reposting_data_file:
434 reposting_data = get_reposting_data(doc.reposting_data_file)
435
436 if reposting_data and reposting_data.distinct_item_and_warehouse:
437 return reposting_data.distinct_item_and_warehouse
438
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530439 distinct_item_warehouses = {}
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530440
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530441 if doc and doc.distinct_item_and_warehouse:
442 distinct_item_warehouses = json.loads(doc.distinct_item_and_warehouse)
Ankush Menat494bd9e2022-03-28 18:52:46 +0530443 distinct_item_warehouses = {
444 frappe.safe_eval(k): frappe._dict(v) for k, v in distinct_item_warehouses.items()
445 }
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530446 else:
447 for i, d in enumerate(args):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530448 distinct_item_warehouses.setdefault(
449 (d.item_code, d.warehouse), frappe._dict({"reposting_status": False, "sle": d, "args_idx": i})
450 )
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530451
452 return distinct_item_warehouses
453
Ankush Menat494bd9e2022-03-28 18:52:46 +0530454
Rohit Waghchaurefb1a40c2023-05-31 14:11:15 +0530455def get_affected_transactions(doc, reposting_data=None) -> Set[Tuple[str, str]]:
456 if not reposting_data and doc and doc.reposting_data_file:
457 reposting_data = get_reposting_data(doc.reposting_data_file)
458
459 if reposting_data and reposting_data.affected_transactions:
460 return {tuple(transaction) for transaction in reposting_data.affected_transactions}
461
Ankush Menatecdb4932022-04-17 19:06:13 +0530462 if not doc.affected_transactions:
463 return set()
464
465 transactions = frappe.parse_json(doc.affected_transactions)
466 return {tuple(transaction) for transaction in transactions}
467
468
rohitwaghchaure31fe5f52021-08-02 11:01:30 +0530469def get_current_index(doc=None):
470 if doc and doc.current_index:
471 return doc.current_index
472
Ankush Menat494bd9e2022-03-28 18:52:46 +0530473
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530474class update_entries_after(object):
Nabin Hait902e8602013-01-08 18:29:24 +0530475 """
Ankush Menat494bd9e2022-03-28 18:52:46 +0530476 update valution rate and qty after transaction
477 from the current time-bucket onwards
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530478
Ankush Menat494bd9e2022-03-28 18:52:46 +0530479 :param args: args as dict
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530480
Ankush Menat494bd9e2022-03-28 18:52:46 +0530481 args = {
482 "item_code": "ABC",
483 "warehouse": "XYZ",
484 "posting_date": "2012-12-12",
485 "posting_time": "12:00"
486 }
Nabin Hait902e8602013-01-08 18:29:24 +0530487 """
Ankush Menat494bd9e2022-03-28 18:52:46 +0530488
489 def __init__(
490 self,
491 args,
492 allow_zero_rate=False,
493 allow_negative_stock=None,
494 via_landed_cost_voucher=False,
495 verbose=1,
496 ):
Nabin Haita77b8c92020-12-21 14:45:50 +0530497 self.exceptions = {}
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530498 self.verbose = verbose
499 self.allow_zero_rate = allow_zero_rate
Anand Doshi0dc79f42015-04-06 12:59:34 +0530500 self.via_landed_cost_voucher = via_landed_cost_voucher
Ankush Menateb8b4242022-02-12 13:08:28 +0530501 self.item_code = args.get("item_code")
Ankush Menat494bd9e2022-03-28 18:52:46 +0530502 self.allow_negative_stock = allow_negative_stock or is_negative_stock_allowed(
503 item_code=self.item_code
504 )
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530505
Nabin Haita77b8c92020-12-21 14:45:50 +0530506 self.args = frappe._dict(args)
Nabin Haita77b8c92020-12-21 14:45:50 +0530507 if self.args.sle_id:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530508 self.args["name"] = self.args.sle_id
Nabin Haitd46b2362021-02-23 16:38:52 +0530509
Nabin Haita77b8c92020-12-21 14:45:50 +0530510 self.company = frappe.get_cached_value("Warehouse", self.args.warehouse, "company")
Maricad6078aa2022-06-17 15:13:13 +0530511 self.set_precision()
Nabin Haita77b8c92020-12-21 14:45:50 +0530512 self.valuation_method = get_valuation_method(self.item_code)
Nabin Hait97bce3a2021-07-12 13:24:43 +0530513
514 self.new_items_found = False
515 self.distinct_item_warehouses = args.get("distinct_item_warehouses", frappe._dict())
Ankush Menatecdb4932022-04-17 19:06:13 +0530516 self.affected_transactions: Set[Tuple[str, str]] = set()
s-aga-rf0acb202023-04-12 14:13:54 +0530517 self.reserved_stock = get_reserved_stock(self.args.item_code, self.args.warehouse)
Nabin Haita77b8c92020-12-21 14:45:50 +0530518
519 self.data = frappe._dict()
520 self.initialize_previous_data(self.args)
Nabin Haita77b8c92020-12-21 14:45:50 +0530521 self.build()
Deepesh Gargb4be2922021-01-28 13:09:56 +0530522
Maricad6078aa2022-06-17 15:13:13 +0530523 def set_precision(self):
524 self.flt_precision = cint(frappe.db.get_default("float_precision")) or 2
525 self.currency_precision = get_field_precision(
526 frappe.get_meta("Stock Ledger Entry").get_field("stock_value")
Ankush Menat494bd9e2022-03-28 18:52:46 +0530527 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530528
529 def initialize_previous_data(self, args):
530 """
Ankush Menat494bd9e2022-03-28 18:52:46 +0530531 Get previous sl entries for current item for each related warehouse
532 and assigns into self.data dict
Nabin Haita77b8c92020-12-21 14:45:50 +0530533
Ankush Menat494bd9e2022-03-28 18:52:46 +0530534 :Data Structure:
Nabin Haita77b8c92020-12-21 14:45:50 +0530535
Ankush Menat494bd9e2022-03-28 18:52:46 +0530536 self.data = {
537 warehouse1: {
538 'previus_sle': {},
539 'qty_after_transaction': 10,
540 'valuation_rate': 100,
541 'stock_value': 1000,
542 'prev_stock_value': 1000,
543 'stock_queue': '[[10, 100]]',
544 'stock_value_difference': 1000
545 }
546 }
Nabin Haita77b8c92020-12-21 14:45:50 +0530547
548 """
Ankush Menatc1d986a2021-08-31 19:43:42 +0530549 self.data.setdefault(args.warehouse, frappe._dict())
550 warehouse_dict = self.data[args.warehouse]
marination8418c4b2021-06-22 21:35:25 +0530551 previous_sle = get_previous_sle_of_current_voucher(args)
Ankush Menatc1d986a2021-08-31 19:43:42 +0530552 warehouse_dict.previous_sle = previous_sle
Nabin Haitbb777562013-08-29 18:19:37 +0530553
Ankush Menatc1d986a2021-08-31 19:43:42 +0530554 for key in ("qty_after_transaction", "valuation_rate", "stock_value"):
555 setattr(warehouse_dict, key, flt(previous_sle.get(key)))
556
Ankush Menat494bd9e2022-03-28 18:52:46 +0530557 warehouse_dict.update(
558 {
559 "prev_stock_value": previous_sle.stock_value or 0.0,
560 "stock_queue": json.loads(previous_sle.stock_queue or "[]"),
561 "stock_value_difference": 0.0,
562 }
563 )
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530564
Nabin Haita77b8c92020-12-21 14:45:50 +0530565 def build(self):
Sagar Vorae50324a2021-03-31 12:44:03 +0530566 from erpnext.controllers.stock_controller import future_sle_exists
Nabin Hait186a0452021-02-18 14:14:21 +0530567
Nabin Haita77b8c92020-12-21 14:45:50 +0530568 if self.args.get("sle_id"):
Nabin Hait186a0452021-02-18 14:14:21 +0530569 self.process_sle_against_current_timestamp()
Sagar Vorae50324a2021-03-31 12:44:03 +0530570 if not future_sle_exists(self.args):
Nabin Hait186a0452021-02-18 14:14:21 +0530571 self.update_bin()
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +0530572 else:
Nabin Haita77b8c92020-12-21 14:45:50 +0530573 entries_to_fix = self.get_future_entries_to_fix()
574
575 i = 0
576 while i < len(entries_to_fix):
577 sle = entries_to_fix[i]
578 i += 1
579
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +0530580 self.process_sle(sle)
Rohit Waghchaure9e5e2de2023-05-25 23:41:56 +0530581 self.update_bin_data(sle)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530582
Nabin Haita77b8c92020-12-21 14:45:50 +0530583 if sle.dependant_sle_voucher_detail_no:
Nabin Hait243d59b2021-02-02 16:55:13 +0530584 entries_to_fix = self.get_dependent_entries_to_fix(entries_to_fix, sle)
Nabin Haitd46b2362021-02-23 16:38:52 +0530585
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530586 if self.exceptions:
587 self.raise_exceptions()
588
Nabin Hait186a0452021-02-18 14:14:21 +0530589 def process_sle_against_current_timestamp(self):
Nabin Haita77b8c92020-12-21 14:45:50 +0530590 sl_entries = self.get_sle_against_current_voucher()
591 for sle in sl_entries:
592 self.process_sle(sle)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530593
Nabin Haita77b8c92020-12-21 14:45:50 +0530594 def get_sle_against_current_voucher(self):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530595 self.args["time_format"] = "%H:%i:%s"
Nabin Haitf2be0802021-02-15 19:27:49 +0530596
Ankush Menat494bd9e2022-03-28 18:52:46 +0530597 return frappe.db.sql(
598 """
Nabin Haita77b8c92020-12-21 14:45:50 +0530599 select
600 *, timestamp(posting_date, posting_time) as "timestamp"
601 from
602 `tabStock Ledger Entry`
603 where
604 item_code = %(item_code)s
605 and warehouse = %(warehouse)s
rohitwaghchaurefe4540d2021-08-26 12:52:36 +0530606 and is_cancelled = 0
Rohit Waghchaurea05c47e2022-12-22 10:24:04 +0530607 and (
608 posting_date = %(posting_date)s and
609 time_format(posting_time, %(time_format)s) = time_format(%(posting_time)s, %(time_format)s)
610 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530611 order by
612 creation ASC
613 for update
Ankush Menat494bd9e2022-03-28 18:52:46 +0530614 """,
615 self.args,
616 as_dict=1,
617 )
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530618
Nabin Haita77b8c92020-12-21 14:45:50 +0530619 def get_future_entries_to_fix(self):
620 # includes current entry!
Ankush Menat494bd9e2022-03-28 18:52:46 +0530621 args = self.data[self.args.warehouse].previous_sle or frappe._dict(
622 {"item_code": self.item_code, "warehouse": self.args.warehouse}
623 )
Deepesh Gargb4be2922021-01-28 13:09:56 +0530624
Nabin Haita77b8c92020-12-21 14:45:50 +0530625 return list(self.get_sle_after_datetime(args))
Rushabh Mehta538607e2016-06-12 11:03:00 +0530626
Nabin Haita77b8c92020-12-21 14:45:50 +0530627 def get_dependent_entries_to_fix(self, entries_to_fix, sle):
Ankush Menat494bd9e2022-03-28 18:52:46 +0530628 dependant_sle = get_sle_by_voucher_detail_no(
629 sle.dependant_sle_voucher_detail_no, excluded_sle=sle.name
630 )
Deepesh Gargb4be2922021-01-28 13:09:56 +0530631
Nabin Haita77b8c92020-12-21 14:45:50 +0530632 if not dependant_sle:
Nabin Hait243d59b2021-02-02 16:55:13 +0530633 return entries_to_fix
Ankush Menat494bd9e2022-03-28 18:52:46 +0530634 elif (
635 dependant_sle.item_code == self.item_code and dependant_sle.warehouse == self.args.warehouse
636 ):
Nabin Hait243d59b2021-02-02 16:55:13 +0530637 return entries_to_fix
638 elif dependant_sle.item_code != self.item_code:
Nabin Hait97bce3a2021-07-12 13:24:43 +0530639 self.update_distinct_item_warehouses(dependant_sle)
Nabin Hait243d59b2021-02-02 16:55:13 +0530640 return entries_to_fix
641 elif dependant_sle.item_code == self.item_code and dependant_sle.warehouse in self.data:
642 return entries_to_fix
Nabin Hait97bce3a2021-07-12 13:24:43 +0530643 else:
Rohit Waghchaure1d80d372022-07-19 16:36:22 +0530644 self.initialize_previous_data(dependant_sle)
645 self.update_distinct_item_warehouses(dependant_sle)
Rohit Waghchaure78c8bb22022-07-04 20:24:18 +0530646 return entries_to_fix
Nabin Hait97bce3a2021-07-12 13:24:43 +0530647
648 def update_distinct_item_warehouses(self, dependant_sle):
649 key = (dependant_sle.item_code, dependant_sle.warehouse)
mergify[bot]87d02512023-08-19 15:37:33 +0530650 val = frappe._dict({"sle": dependant_sle})
Rohit Waghchaureb77a8082023-07-01 11:30:46 +0530651
Nabin Hait97bce3a2021-07-12 13:24:43 +0530652 if key not in self.distinct_item_warehouses:
653 self.distinct_item_warehouses[key] = val
654 self.new_items_found = True
655 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530656 existing_sle_posting_date = (
657 self.distinct_item_warehouses[key].get("sle", {}).get("posting_date")
658 )
Rohit Waghchaurec16a5812023-07-11 17:51:27 +0530659
660 dependent_voucher_detail_nos = self.get_dependent_voucher_detail_nos(key)
661
Nabin Hait97bce3a2021-07-12 13:24:43 +0530662 if getdate(dependant_sle.posting_date) < getdate(existing_sle_posting_date):
663 val.sle_changed = True
mergify[bot]87d02512023-08-19 15:37:33 +0530664 dependent_voucher_detail_nos.append(dependant_sle.voucher_detail_no)
665 val.dependent_voucher_detail_nos = dependent_voucher_detail_nos
Nabin Hait97bce3a2021-07-12 13:24:43 +0530666 self.distinct_item_warehouses[key] = val
667 self.new_items_found = True
Rohit Waghchaurec16a5812023-07-11 17:51:27 +0530668 elif dependant_sle.voucher_detail_no not in set(dependent_voucher_detail_nos):
669 # Future dependent voucher needs to be repost to get the correct stock value
670 # If dependent voucher has not reposted, then add it to the list
671 dependent_voucher_detail_nos.append(dependant_sle.voucher_detail_no)
Rohit Waghchaureb77a8082023-07-01 11:30:46 +0530672 self.new_items_found = True
Rohit Waghchaurec16a5812023-07-11 17:51:27 +0530673 val.dependent_voucher_detail_nos = dependent_voucher_detail_nos
674 self.distinct_item_warehouses[key] = val
675
676 def get_dependent_voucher_detail_nos(self, key):
677 if "dependent_voucher_detail_nos" not in self.distinct_item_warehouses[key]:
678 self.distinct_item_warehouses[key].dependent_voucher_detail_nos = []
679
680 return self.distinct_item_warehouses[key].dependent_voucher_detail_nos
Nabin Hait97bce3a2021-07-12 13:24:43 +0530681
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530682 def process_sle(self, sle):
Nabin Haita77b8c92020-12-21 14:45:50 +0530683 # previous sle data for this warehouse
684 self.wh_data = self.data[sle.warehouse]
Ankush Menatecdb4932022-04-17 19:06:13 +0530685 self.affected_transactions.add((sle.voucher_type, sle.voucher_no))
Nabin Haita77b8c92020-12-21 14:45:50 +0530686
Anand Doshi0dc79f42015-04-06 12:59:34 +0530687 if (sle.serial_no and not self.via_landed_cost_voucher) or not cint(self.allow_negative_stock):
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530688 # validate negative stock for serialized items, fifo valuation
Nabin Hait902e8602013-01-08 18:29:24 +0530689 # or when negative stock is not allowed for moving average
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530690 if not self.validate_negative_stock(sle):
Nabin Haita77b8c92020-12-21 14:45:50 +0530691 self.wh_data.qty_after_transaction += flt(sle.actual_qty)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530692 return
Nabin Haitb96c0142014-10-07 11:25:04 +0530693
Nabin Haita77b8c92020-12-21 14:45:50 +0530694 # Get dynamic incoming/outgoing rate
Ankush Menat701878f2022-03-01 18:08:29 +0530695 if not self.args.get("sle_id"):
696 self.get_dynamic_incoming_outgoing_rate(sle)
Deepesh Gargb4be2922021-01-28 13:09:56 +0530697
Rohit Waghchaure3266e542022-10-12 15:09:50 +0530698 if (
Rohit Waghchaure7bfc8f12023-04-14 12:22:19 +0530699 sle.voucher_type == "Stock Reconciliation"
Rohit Waghchaure42b22942023-05-27 19:18:03 +0530700 and (sle.batch_no or (sle.has_batch_no and sle.serial_and_batch_bundle))
Rohit Waghchaure7bfc8f12023-04-14 12:22:19 +0530701 and sle.voucher_detail_no
702 and sle.actual_qty < 0
703 ):
704 self.reset_actual_qty_for_stock_reco(sle)
705
706 if (
Rohit Waghchaure3266e542022-10-12 15:09:50 +0530707 sle.voucher_type in ["Purchase Receipt", "Purchase Invoice"]
708 and sle.voucher_detail_no
709 and sle.actual_qty < 0
Rohit Waghchaureb5a2ccf2023-05-04 15:38:35 +0530710 and is_internal_transfer(sle)
Rohit Waghchaure3266e542022-10-12 15:09:50 +0530711 ):
712 sle.outgoing_rate = get_incoming_rate_for_inter_company_transfer(sle)
713
Rohit Waghchauree6143ab2023-03-13 14:51:43 +0530714 if sle.serial_and_batch_bundle:
Rohit Waghchaured3ceb072023-03-31 09:03:54 +0530715 self.calculate_valuation_for_serial_batch_bundle(sle)
Nabin Haitb96c0142014-10-07 11:25:04 +0530716 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530717 if sle.voucher_type == "Stock Reconciliation" and not sle.batch_no:
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530718 # assert
Nabin Haita77b8c92020-12-21 14:45:50 +0530719 self.wh_data.valuation_rate = sle.valuation_rate
720 self.wh_data.qty_after_transaction = sle.qty_after_transaction
Ankush Menat494bd9e2022-03-28 18:52:46 +0530721 self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt(
722 self.wh_data.valuation_rate
723 )
Ankush Menatb0cf6192022-01-16 13:02:23 +0530724 if self.valuation_method != "Moving Average":
725 self.wh_data.stock_queue = [[self.wh_data.qty_after_transaction, self.wh_data.valuation_rate]]
Nabin Haitb96c0142014-10-07 11:25:04 +0530726 else:
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530727 if self.valuation_method == "Moving Average":
728 self.get_moving_average_values(sle)
Nabin Haita77b8c92020-12-21 14:45:50 +0530729 self.wh_data.qty_after_transaction += flt(sle.actual_qty)
Ankush Menat494bd9e2022-03-28 18:52:46 +0530730 self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt(
731 self.wh_data.valuation_rate
732 )
Nabin Haitb96c0142014-10-07 11:25:04 +0530733 else:
Ankush Menatf089d392022-02-02 12:51:21 +0530734 self.update_queue_values(sle)
Nabin Haitb96c0142014-10-07 11:25:04 +0530735
Rushabh Mehta54047782013-12-26 11:07:46 +0530736 # rounding as per precision
Maricad6078aa2022-06-17 15:13:13 +0530737 self.wh_data.stock_value = flt(self.wh_data.stock_value, self.currency_precision)
Ankush Menat609d2fc2022-02-20 11:35:53 +0530738 if not self.wh_data.qty_after_transaction:
739 self.wh_data.stock_value = 0.0
Rohit Waghchaurec2d74612023-03-29 11:40:36 +0530740
Nabin Haita77b8c92020-12-21 14:45:50 +0530741 stock_value_difference = self.wh_data.stock_value - self.wh_data.prev_stock_value
742 self.wh_data.prev_stock_value = self.wh_data.stock_value
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530743
Nabin Hait902e8602013-01-08 18:29:24 +0530744 # update current sle
Nabin Haita77b8c92020-12-21 14:45:50 +0530745 sle.qty_after_transaction = self.wh_data.qty_after_transaction
746 sle.valuation_rate = self.wh_data.valuation_rate
747 sle.stock_value = self.wh_data.stock_value
748 sle.stock_queue = json.dumps(self.wh_data.stock_queue)
Rushabh Mehta2e0e7112015-02-18 11:38:05 +0530749 sle.stock_value_difference = stock_value_difference
Ankush Menat494bd9e2022-03-28 18:52:46 +0530750 sle.doctype = "Stock Ledger Entry"
Rohit Waghchaure683a47f2022-10-11 15:11:39 +0530751
Rushabh Mehta8bb6e532015-02-18 20:22:59 +0530752 frappe.get_doc(sle).db_update()
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530753
Ankush Menat701878f2022-03-01 18:08:29 +0530754 if not self.args.get("sle_id"):
755 self.update_outgoing_rate_on_transaction(sle)
Nabin Haita77b8c92020-12-21 14:45:50 +0530756
Rohit Waghchaure7bfc8f12023-04-14 12:22:19 +0530757 def reset_actual_qty_for_stock_reco(self, sle):
Rohit Waghchaure42b22942023-05-27 19:18:03 +0530758 if sle.serial_and_batch_bundle:
759 current_qty = frappe.get_cached_value(
760 "Serial and Batch Bundle", sle.serial_and_batch_bundle, "total_qty"
761 )
762
763 if current_qty is not None:
764 current_qty = abs(current_qty)
765 else:
766 current_qty = frappe.get_cached_value(
767 "Stock Reconciliation Item", sle.voucher_detail_no, "current_qty"
768 )
Rohit Waghchaure7bfc8f12023-04-14 12:22:19 +0530769
770 if current_qty:
771 sle.actual_qty = current_qty * -1
772 elif current_qty == 0:
773 sle.is_cancelled = 1
774
Rohit Waghchaured3ceb072023-03-31 09:03:54 +0530775 def calculate_valuation_for_serial_batch_bundle(self, sle):
776 doc = frappe.get_cached_doc("Serial and Batch Bundle", sle.serial_and_batch_bundle)
777
778 doc.set_incoming_rate(save=True)
779 doc.calculate_qty_and_amount(save=True)
780
781 self.wh_data.stock_value = round_off_if_near_zero(self.wh_data.stock_value + doc.total_amount)
782
783 self.wh_data.qty_after_transaction += doc.total_qty
784 if self.wh_data.qty_after_transaction:
785 self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction
786
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530787 def validate_negative_stock(self, sle):
788 """
Ankush Menat494bd9e2022-03-28 18:52:46 +0530789 validate negative stock for entries current datetime onwards
790 will not consider cancelled entries
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530791 """
s-aga-rf0acb202023-04-12 14:13:54 +0530792 diff = self.wh_data.qty_after_transaction + flt(sle.actual_qty) - flt(self.reserved_stock)
Maricad6078aa2022-06-17 15:13:13 +0530793 diff = flt(diff, self.flt_precision) # respect system precision
Nabin Haitdc82d4f2014-04-07 12:02:57 +0530794
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530795 if diff < 0 and abs(diff) > 0.0001:
796 # negative stock!
797 exc = sle.copy().update({"diff": diff})
Nabin Haita77b8c92020-12-21 14:45:50 +0530798 self.exceptions.setdefault(sle.warehouse, []).append(exc)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530799 return False
Nabin Hait902e8602013-01-08 18:29:24 +0530800 else:
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530801 return True
802
Nabin Haita77b8c92020-12-21 14:45:50 +0530803 def get_dynamic_incoming_outgoing_rate(self, sle):
804 # Get updated incoming/outgoing rate from transaction
805 if sle.recalculate_rate:
806 rate = self.get_incoming_outgoing_rate_from_transaction(sle)
807
808 if flt(sle.actual_qty) >= 0:
809 sle.incoming_rate = rate
810 else:
811 sle.outgoing_rate = rate
812
813 def get_incoming_outgoing_rate_from_transaction(self, sle):
814 rate = 0
815 # Material Transfer, Repack, Manufacturing
816 if sle.voucher_type == "Stock Entry":
Nabin Hait97bce3a2021-07-12 13:24:43 +0530817 self.recalculate_amounts_in_stock_entry(sle.voucher_no)
Nabin Haita77b8c92020-12-21 14:45:50 +0530818 rate = frappe.db.get_value("Stock Entry Detail", sle.voucher_detail_no, "valuation_rate")
819 # Sales and Purchase Return
Ankush Menat494bd9e2022-03-28 18:52:46 +0530820 elif sle.voucher_type in (
821 "Purchase Receipt",
822 "Purchase Invoice",
823 "Delivery Note",
824 "Sales Invoice",
Sagar Sharma323bdf82022-05-17 15:14:07 +0530825 "Subcontracting Receipt",
Ankush Menat494bd9e2022-03-28 18:52:46 +0530826 ):
Nabin Haita77b8c92020-12-21 14:45:50 +0530827 if frappe.get_cached_value(sle.voucher_type, sle.voucher_no, "is_return"):
Chillar Anand915b3432021-09-02 16:44:59 +0530828 from erpnext.controllers.sales_and_purchase_return import (
829 get_rate_for_return, # don't move this import to top
830 )
Ankush Menat494bd9e2022-03-28 18:52:46 +0530831
832 rate = get_rate_for_return(
833 sle.voucher_type,
834 sle.voucher_no,
835 sle.item_code,
836 voucher_detail_no=sle.voucher_detail_no,
837 sle=sle,
838 )
Rohit Waghchaureddd24ea2022-08-09 14:50:20 +0530839
840 elif (
841 sle.voucher_type in ["Purchase Receipt", "Purchase Invoice"]
Rohit Waghchaurea03b4ce2022-09-08 19:16:00 +0530842 and sle.voucher_detail_no
Rohit Waghchaureb5a2ccf2023-05-04 15:38:35 +0530843 and is_internal_transfer(sle)
Rohit Waghchaureddd24ea2022-08-09 14:50:20 +0530844 ):
Rohit Waghchaure683a47f2022-10-11 15:11:39 +0530845 rate = get_incoming_rate_for_inter_company_transfer(sle)
Nabin Haita77b8c92020-12-21 14:45:50 +0530846 else:
847 if sle.voucher_type in ("Purchase Receipt", "Purchase Invoice"):
Deepesh Gargb4be2922021-01-28 13:09:56 +0530848 rate_field = "valuation_rate"
Sagar Sharma323bdf82022-05-17 15:14:07 +0530849 elif sle.voucher_type == "Subcontracting Receipt":
850 rate_field = "rate"
Nabin Haita77b8c92020-12-21 14:45:50 +0530851 else:
852 rate_field = "incoming_rate"
853
854 # check in item table
Ankush Menat494bd9e2022-03-28 18:52:46 +0530855 item_code, incoming_rate = frappe.db.get_value(
856 sle.voucher_type + " Item", sle.voucher_detail_no, ["item_code", rate_field]
857 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530858
859 if item_code == sle.item_code:
860 rate = incoming_rate
861 else:
862 if sle.voucher_type in ("Delivery Note", "Sales Invoice"):
863 ref_doctype = "Packed Item"
Sagar Sharma323bdf82022-05-17 15:14:07 +0530864 elif sle == "Subcontracting Receipt":
865 ref_doctype = "Subcontracting Receipt Supplied Item"
Nabin Haita77b8c92020-12-21 14:45:50 +0530866 else:
867 ref_doctype = "Purchase Receipt Item Supplied"
Deepesh Gargb4be2922021-01-28 13:09:56 +0530868
Ankush Menat494bd9e2022-03-28 18:52:46 +0530869 rate = frappe.db.get_value(
870 ref_doctype,
871 {"parent_detail_docname": sle.voucher_detail_no, "item_code": sle.item_code},
872 rate_field,
873 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530874
875 return rate
876
877 def update_outgoing_rate_on_transaction(self, sle):
878 """
Ankush Menat494bd9e2022-03-28 18:52:46 +0530879 Update outgoing rate in Stock Entry, Delivery Note, Sales Invoice and Sales Return
880 In case of Stock Entry, also calculate FG Item rate and total incoming/outgoing amount
Nabin Haita77b8c92020-12-21 14:45:50 +0530881 """
882 if sle.actual_qty and sle.voucher_detail_no:
883 outgoing_rate = abs(flt(sle.stock_value_difference)) / abs(sle.actual_qty)
884
885 if flt(sle.actual_qty) < 0 and sle.voucher_type == "Stock Entry":
886 self.update_rate_on_stock_entry(sle, outgoing_rate)
887 elif sle.voucher_type in ("Delivery Note", "Sales Invoice"):
888 self.update_rate_on_delivery_and_sales_return(sle, outgoing_rate)
889 elif flt(sle.actual_qty) < 0 and sle.voucher_type in ("Purchase Receipt", "Purchase Invoice"):
890 self.update_rate_on_purchase_receipt(sle, outgoing_rate)
Sagar Sharma323bdf82022-05-17 15:14:07 +0530891 elif flt(sle.actual_qty) < 0 and sle.voucher_type == "Subcontracting Receipt":
892 self.update_rate_on_subcontracting_receipt(sle, outgoing_rate)
s-aga-r88a3f652023-05-30 16:54:28 +0530893 elif sle.voucher_type == "Stock Reconciliation":
894 self.update_rate_on_stock_reconciliation(sle)
Nabin Haita77b8c92020-12-21 14:45:50 +0530895
896 def update_rate_on_stock_entry(self, sle, outgoing_rate):
897 frappe.db.set_value("Stock Entry Detail", sle.voucher_detail_no, "basic_rate", outgoing_rate)
898
Ankush Menat701878f2022-03-01 18:08:29 +0530899 # Update outgoing item's rate, recalculate FG Item's rate and total incoming/outgoing amount
900 if not sle.dependant_sle_voucher_detail_no:
901 self.recalculate_amounts_in_stock_entry(sle.voucher_no)
Nabin Hait97bce3a2021-07-12 13:24:43 +0530902
903 def recalculate_amounts_in_stock_entry(self, voucher_no):
904 stock_entry = frappe.get_doc("Stock Entry", voucher_no, for_update=True)
Nabin Haita77b8c92020-12-21 14:45:50 +0530905 stock_entry.calculate_rate_and_amount(reset_outgoing_rate=False, raise_error_if_no_rate=False)
906 stock_entry.db_update()
907 for d in stock_entry.items:
908 d.db_update()
Deepesh Gargb4be2922021-01-28 13:09:56 +0530909
Nabin Haita77b8c92020-12-21 14:45:50 +0530910 def update_rate_on_delivery_and_sales_return(self, sle, outgoing_rate):
911 # Update item's incoming rate on transaction
912 item_code = frappe.db.get_value(sle.voucher_type + " Item", sle.voucher_detail_no, "item_code")
913 if item_code == sle.item_code:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530914 frappe.db.set_value(
915 sle.voucher_type + " Item", sle.voucher_detail_no, "incoming_rate", outgoing_rate
916 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530917 else:
918 # packed item
Ankush Menat494bd9e2022-03-28 18:52:46 +0530919 frappe.db.set_value(
920 "Packed Item",
Nabin Haita77b8c92020-12-21 14:45:50 +0530921 {"parent_detail_docname": sle.voucher_detail_no, "item_code": sle.item_code},
Ankush Menat494bd9e2022-03-28 18:52:46 +0530922 "incoming_rate",
923 outgoing_rate,
924 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530925
926 def update_rate_on_purchase_receipt(self, sle, outgoing_rate):
927 if frappe.db.exists(sle.voucher_type + " Item", sle.voucher_detail_no):
Rohit Waghchaure683a47f2022-10-11 15:11:39 +0530928 if sle.voucher_type in ["Purchase Receipt", "Purchase Invoice"] and frappe.get_cached_value(
929 sle.voucher_type, sle.voucher_no, "is_internal_supplier"
930 ):
931 frappe.db.set_value(
932 f"{sle.voucher_type} Item", sle.voucher_detail_no, "valuation_rate", sle.outgoing_rate
933 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530934 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +0530935 frappe.db.set_value(
936 "Purchase Receipt Item Supplied", sle.voucher_detail_no, "rate", outgoing_rate
937 )
Nabin Haita77b8c92020-12-21 14:45:50 +0530938
939 # Recalculate subcontracted item's rate in case of subcontracted purchase receipt/invoice
Sagar Sharmad074c932022-03-31 19:57:42 +0530940 if frappe.get_cached_value(sle.voucher_type, sle.voucher_no, "is_subcontracted"):
Rohit Waghchauree5fb2392021-06-18 20:37:42 +0530941 doc = frappe.get_doc(sle.voucher_type, sle.voucher_no)
Nabin Haita77b8c92020-12-21 14:45:50 +0530942 doc.update_valuation_rate(reset_outgoing_rate=False)
Ankush Menat494bd9e2022-03-28 18:52:46 +0530943 for d in doc.items + doc.supplied_items:
Nabin Haita77b8c92020-12-21 14:45:50 +0530944 d.db_update()
945
Sagar Sharma323bdf82022-05-17 15:14:07 +0530946 def update_rate_on_subcontracting_receipt(self, sle, outgoing_rate):
s-aga-ra6cb6c62023-05-03 09:51:58 +0530947 if frappe.db.exists("Subcontracting Receipt Item", sle.voucher_detail_no):
948 frappe.db.set_value("Subcontracting Receipt Item", sle.voucher_detail_no, "rate", outgoing_rate)
Sagar Sharma323bdf82022-05-17 15:14:07 +0530949 else:
950 frappe.db.set_value(
Sagar Sharma9c72c2a2023-05-12 11:46:32 +0530951 "Subcontracting Receipt Supplied Item",
952 sle.voucher_detail_no,
953 {"rate": outgoing_rate, "amount": abs(sle.actual_qty) * outgoing_rate},
Sagar Sharma323bdf82022-05-17 15:14:07 +0530954 )
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +0530955
s-aga-ra6cb6c62023-05-03 09:51:58 +0530956 scr = frappe.get_doc("Subcontracting Receipt", sle.voucher_no, for_update=True)
Sagar Sharma9c72c2a2023-05-12 11:46:32 +0530957 scr.calculate_items_qty_and_amount()
s-aga-ra6cb6c62023-05-03 09:51:58 +0530958 scr.db_update()
Sagar Sharma9c72c2a2023-05-12 11:46:32 +0530959 for d in scr.items:
s-aga-ra6cb6c62023-05-03 09:51:58 +0530960 d.db_update()
961
s-aga-r88a3f652023-05-30 16:54:28 +0530962 def update_rate_on_stock_reconciliation(self, sle):
963 if not sle.serial_no and not sle.batch_no:
964 sr = frappe.get_doc("Stock Reconciliation", sle.voucher_no, for_update=True)
965
966 for item in sr.items:
967 # Skip for Serial and Batch Items
s-aga-rdb159dd2023-06-12 18:28:16 +0530968 if item.name != sle.voucher_detail_no or item.serial_no or item.batch_no:
s-aga-r88a3f652023-05-30 16:54:28 +0530969 continue
970
971 previous_sle = get_previous_sle(
972 {
973 "item_code": item.item_code,
974 "warehouse": item.warehouse,
975 "posting_date": sr.posting_date,
976 "posting_time": sr.posting_time,
977 "sle": sle.name,
978 }
979 )
980
981 item.current_qty = previous_sle.get("qty_after_transaction") or 0.0
982 item.current_valuation_rate = previous_sle.get("valuation_rate") or 0.0
983 item.current_amount = flt(item.current_qty) * flt(item.current_valuation_rate)
984
985 item.amount = flt(item.qty) * flt(item.valuation_rate)
s-aga-r6a1b0a22023-06-15 11:39:22 +0530986 item.quantity_difference = item.qty - item.current_qty
s-aga-r88a3f652023-05-30 16:54:28 +0530987 item.amount_difference = item.amount - item.current_amount
988 else:
989 sr.difference_amount = sum([item.amount_difference for item in sr.items])
990 sr.db_update()
991
992 for item in sr.items:
993 item.db_update()
994
Nabin Hait328c4f92020-01-02 19:00:32 +0530995 def get_incoming_value_for_serial_nos(self, sle, serial_nos):
996 # get rate from serial nos within same company
Ankush Menat494bd9e2022-03-28 18:52:46 +0530997 all_serial_nos = frappe.get_all(
998 "Serial No", fields=["purchase_rate", "name", "company"], filters={"name": ("in", serial_nos)}
999 )
Nabin Hait328c4f92020-01-02 19:00:32 +05301000
Ankush Menat494bd9e2022-03-28 18:52:46 +05301001 incoming_values = sum(flt(d.purchase_rate) for d in all_serial_nos if d.company == sle.company)
Nabin Hait328c4f92020-01-02 19:00:32 +05301002
1003 # Get rate for serial nos which has been transferred to other company
Ankush Menat494bd9e2022-03-28 18:52:46 +05301004 invalid_serial_nos = [d.name for d in all_serial_nos if d.company != sle.company]
Nabin Hait328c4f92020-01-02 19:00:32 +05301005 for serial_no in invalid_serial_nos:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301006 incoming_rate = frappe.db.sql(
1007 """
Nabin Hait328c4f92020-01-02 19:00:32 +05301008 select incoming_rate
1009 from `tabStock Ledger Entry`
1010 where
1011 company = %s
1012 and actual_qty > 0
Ankush Menat82ea9582022-01-16 20:19:04 +05301013 and is_cancelled = 0
Nabin Hait328c4f92020-01-02 19:00:32 +05301014 and (serial_no = %s
1015 or serial_no like %s
1016 or serial_no like %s
1017 or serial_no like %s
1018 )
1019 order by posting_date desc
1020 limit 1
Ankush Menat494bd9e2022-03-28 18:52:46 +05301021 """,
1022 (sle.company, serial_no, serial_no + "\n%", "%\n" + serial_no, "%\n" + serial_no + "\n%"),
1023 )
Nabin Hait328c4f92020-01-02 19:00:32 +05301024
1025 incoming_values += flt(incoming_rate[0][0]) if incoming_rate else 0
1026
1027 return incoming_values
1028
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301029 def get_moving_average_values(self, sle):
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301030 actual_qty = flt(sle.actual_qty)
Nabin Haita77b8c92020-12-21 14:45:50 +05301031 new_stock_qty = flt(self.wh_data.qty_after_transaction) + actual_qty
Nabin Hait6dfc78b2016-06-24 12:28:55 +05301032 if new_stock_qty >= 0:
1033 if actual_qty > 0:
Nabin Haita77b8c92020-12-21 14:45:50 +05301034 if flt(self.wh_data.qty_after_transaction) <= 0:
1035 self.wh_data.valuation_rate = sle.incoming_rate
Nabin Hait6dfc78b2016-06-24 12:28:55 +05301036 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301037 new_stock_value = (self.wh_data.qty_after_transaction * self.wh_data.valuation_rate) + (
1038 actual_qty * sle.incoming_rate
1039 )
Rushabh Mehta14a908b2015-10-15 12:28:20 +05301040
Nabin Haita77b8c92020-12-21 14:45:50 +05301041 self.wh_data.valuation_rate = new_stock_value / new_stock_qty
Rushabh Mehta14a908b2015-10-15 12:28:20 +05301042
Nabin Hait6dfc78b2016-06-24 12:28:55 +05301043 elif sle.outgoing_rate:
1044 if new_stock_qty:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301045 new_stock_value = (self.wh_data.qty_after_transaction * self.wh_data.valuation_rate) + (
1046 actual_qty * sle.outgoing_rate
1047 )
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301048
Nabin Haita77b8c92020-12-21 14:45:50 +05301049 self.wh_data.valuation_rate = new_stock_value / new_stock_qty
Nabin Hait6dfc78b2016-06-24 12:28:55 +05301050 else:
Nabin Haita77b8c92020-12-21 14:45:50 +05301051 self.wh_data.valuation_rate = sle.outgoing_rate
Nabin Hait6dfc78b2016-06-24 12:28:55 +05301052 else:
Nabin Haita77b8c92020-12-21 14:45:50 +05301053 if flt(self.wh_data.qty_after_transaction) >= 0 and sle.outgoing_rate:
1054 self.wh_data.valuation_rate = sle.outgoing_rate
Rushabh Mehta14a908b2015-10-15 12:28:20 +05301055
Nabin Haita77b8c92020-12-21 14:45:50 +05301056 if not self.wh_data.valuation_rate and actual_qty > 0:
1057 self.wh_data.valuation_rate = sle.incoming_rate
Rushabh Mehtacc8b2b22017-03-31 12:44:29 +05301058
Rushabh Mehtaaedaac62017-05-04 09:35:19 +05301059 # Get valuation rate from previous SLE or Item master, if item does not have the
Javier Wong9b11d9b2017-04-14 18:24:04 +08001060 # allow zero valuration rate flag set
Nabin Haita77b8c92020-12-21 14:45:50 +05301061 if not self.wh_data.valuation_rate and sle.voucher_detail_no:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301062 allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate(
1063 sle.voucher_type, sle.voucher_detail_no
1064 )
Javier Wong9b11d9b2017-04-14 18:24:04 +08001065 if not allow_zero_valuation_rate:
Ankush Menatd7ca83e2022-02-19 19:35:33 +05301066 self.wh_data.valuation_rate = self.get_fallback_rate(sle)
Rushabh Mehtacc8b2b22017-03-31 12:44:29 +05301067
Ankush Menatf089d392022-02-02 12:51:21 +05301068 def update_queue_values(self, sle):
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301069 incoming_rate = flt(sle.incoming_rate)
1070 actual_qty = flt(sle.actual_qty)
Nabin Haitada485f2015-07-17 15:09:56 +05301071 outgoing_rate = flt(sle.outgoing_rate)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301072
Ankush Menat494bd9e2022-03-28 18:52:46 +05301073 self.wh_data.qty_after_transaction = round_off_if_near_zero(
1074 self.wh_data.qty_after_transaction + actual_qty
1075 )
Ankush Menatb534fee2022-02-19 20:58:36 +05301076
Ankush Menat97e18a12022-01-15 17:42:25 +05301077 if self.valuation_method == "LIFO":
1078 stock_queue = LIFOValuation(self.wh_data.stock_queue)
1079 else:
1080 stock_queue = FIFOValuation(self.wh_data.stock_queue)
1081
Ankush Menatb534fee2022-02-19 20:58:36 +05301082 _prev_qty, prev_stock_value = stock_queue.get_total_stock_and_value()
1083
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301084 if actual_qty > 0:
Ankush Menat97e18a12022-01-15 17:42:25 +05301085 stock_queue.add_stock(qty=actual_qty, rate=incoming_rate)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301086 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301087
Ankush Menat4b29fb62021-12-18 18:40:22 +05301088 def rate_generator() -> float:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301089 allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate(
1090 sle.voucher_type, sle.voucher_detail_no
1091 )
Ankush Menat4b29fb62021-12-18 18:40:22 +05301092 if not allow_zero_valuation_rate:
Ankush Menatd7ca83e2022-02-19 19:35:33 +05301093 return self.get_fallback_rate(sle)
Nabin Haitada485f2015-07-17 15:09:56 +05301094 else:
Ankush Menat4b29fb62021-12-18 18:40:22 +05301095 return 0.0
Rushabh Mehtacca33b22016-07-08 18:24:46 +05301096
Ankush Menat494bd9e2022-03-28 18:52:46 +05301097 stock_queue.remove_stock(
1098 qty=abs(actual_qty), outgoing_rate=outgoing_rate, rate_generator=rate_generator
1099 )
Nabin Haitdc82d4f2014-04-07 12:02:57 +05301100
Ankush Menatb534fee2022-02-19 20:58:36 +05301101 _qty, stock_value = stock_queue.get_total_stock_and_value()
1102
1103 stock_value_difference = stock_value - prev_stock_value
Nabin Haitdc82d4f2014-04-07 12:02:57 +05301104
Ankush Menat97e18a12022-01-15 17:42:25 +05301105 self.wh_data.stock_queue = stock_queue.state
Ankush Menat494bd9e2022-03-28 18:52:46 +05301106 self.wh_data.stock_value = round_off_if_near_zero(
1107 self.wh_data.stock_value + stock_value_difference
1108 )
Rushabh Mehtacca33b22016-07-08 18:24:46 +05301109
Nabin Haita77b8c92020-12-21 14:45:50 +05301110 if not self.wh_data.stock_queue:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301111 self.wh_data.stock_queue.append(
1112 [0, sle.incoming_rate or sle.outgoing_rate or self.wh_data.valuation_rate]
1113 )
Rushabh Mehtacc8b2b22017-03-31 12:44:29 +05301114
Ankush Menatb534fee2022-02-19 20:58:36 +05301115 if self.wh_data.qty_after_transaction:
1116 self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction
1117
Ankush Menatce0514c2022-02-15 11:41:41 +05301118 def update_batched_values(self, sle):
1119 incoming_rate = flt(sle.incoming_rate)
1120 actual_qty = flt(sle.actual_qty)
Ankush Menat4b29fb62021-12-18 18:40:22 +05301121
Ankush Menat494bd9e2022-03-28 18:52:46 +05301122 self.wh_data.qty_after_transaction = round_off_if_near_zero(
1123 self.wh_data.qty_after_transaction + actual_qty
1124 )
Ankush Menatce0514c2022-02-15 11:41:41 +05301125
1126 if actual_qty > 0:
1127 stock_value_difference = incoming_rate * actual_qty
Ankush Menatce0514c2022-02-15 11:41:41 +05301128 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301129 outgoing_rate = get_batch_incoming_rate(
1130 item_code=sle.item_code,
1131 warehouse=sle.warehouse,
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301132 serial_and_batch_bundle=sle.serial_and_batch_bundle,
Ankush Menat494bd9e2022-03-28 18:52:46 +05301133 posting_date=sle.posting_date,
1134 posting_time=sle.posting_time,
1135 creation=sle.creation,
1136 )
Ankush Menataba7a7c2022-02-19 19:36:28 +05301137 if outgoing_rate is None:
1138 # This can *only* happen if qty available for the batch is zero.
1139 # in such case fall back various other rates.
1140 # future entries will correct the overall accounting as each
1141 # batch individually uses moving average rates.
1142 outgoing_rate = self.get_fallback_rate(sle)
Ankush Menatce0514c2022-02-15 11:41:41 +05301143 stock_value_difference = outgoing_rate * actual_qty
Ankush Menatce0514c2022-02-15 11:41:41 +05301144
Ankush Menat494bd9e2022-03-28 18:52:46 +05301145 self.wh_data.stock_value = round_off_if_near_zero(
1146 self.wh_data.stock_value + stock_value_difference
1147 )
Ankush Menatce0514c2022-02-15 11:41:41 +05301148 if self.wh_data.qty_after_transaction:
1149 self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction
Ankush Menat4b29fb62021-12-18 18:40:22 +05301150
Javier Wong9b11d9b2017-04-14 18:24:04 +08001151 def check_if_allow_zero_valuation_rate(self, voucher_type, voucher_detail_no):
deepeshgarg007f9c0ef32019-07-30 18:49:19 +05301152 ref_item_dt = ""
1153
1154 if voucher_type == "Stock Entry":
1155 ref_item_dt = voucher_type + " Detail"
1156 elif voucher_type in ["Purchase Invoice", "Sales Invoice", "Delivery Note", "Purchase Receipt"]:
1157 ref_item_dt = voucher_type + " Item"
1158
1159 if ref_item_dt:
1160 return frappe.db.get_value(ref_item_dt, voucher_detail_no, "allow_zero_valuation_rate")
1161 else:
1162 return 0
Rushabh Mehtacc8b2b22017-03-31 12:44:29 +05301163
Ankush Menatd7ca83e2022-02-19 19:35:33 +05301164 def get_fallback_rate(self, sle) -> float:
1165 """When exact incoming rate isn't available use any of other "average" rates as fallback.
Ankush Menat494bd9e2022-03-28 18:52:46 +05301166 This should only get used for negative stock."""
1167 return get_valuation_rate(
1168 sle.item_code,
1169 sle.warehouse,
1170 sle.voucher_type,
1171 sle.voucher_no,
1172 self.allow_zero_rate,
1173 currency=erpnext.get_company_currency(sle.company),
1174 company=sle.company,
Ankush Menat494bd9e2022-03-28 18:52:46 +05301175 )
Ankush Menatd7ca83e2022-02-19 19:35:33 +05301176
Nabin Haita77b8c92020-12-21 14:45:50 +05301177 def get_sle_before_datetime(self, args):
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301178 """get previous stock ledger entry before current time-bucket"""
Nabin Haita77b8c92020-12-21 14:45:50 +05301179 sle = get_stock_ledger_entries(args, "<", "desc", "limit 1", for_update=False)
1180 sle = sle[0] if sle else frappe._dict()
1181 return sle
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +05301182
Nabin Haita77b8c92020-12-21 14:45:50 +05301183 def get_sle_after_datetime(self, args):
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301184 """get Stock Ledger Entries after a particular datetime, for reposting"""
Nabin Haita77b8c92020-12-21 14:45:50 +05301185 return get_stock_ledger_entries(args, ">", "asc", for_update=True, check_serial_no=False)
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301186
1187 def raise_exceptions(self):
Nabin Haita77b8c92020-12-21 14:45:50 +05301188 msg_list = []
Ankush Menat8fe5feb2021-11-04 19:48:32 +05301189 for warehouse, exceptions in self.exceptions.items():
Nabin Haita77b8c92020-12-21 14:45:50 +05301190 deficiency = min(e["diff"] for e in exceptions)
Rushabh Mehta538607e2016-06-12 11:03:00 +05301191
Ankush Menat494bd9e2022-03-28 18:52:46 +05301192 if (
1193 exceptions[0]["voucher_type"],
1194 exceptions[0]["voucher_no"],
1195 ) in frappe.local.flags.currently_saving:
Nabin Hait3edefb12016-07-20 16:13:18 +05301196
Nabin Haita77b8c92020-12-21 14:45:50 +05301197 msg = _("{0} units of {1} needed in {2} to complete this transaction.").format(
s-aga-rf0acb202023-04-12 14:13:54 +05301198 frappe.bold(abs(deficiency)),
Ankush Menat494bd9e2022-03-28 18:52:46 +05301199 frappe.get_desk_link("Item", exceptions[0]["item_code"]),
1200 frappe.get_desk_link("Warehouse", warehouse),
1201 )
Nabin Haita77b8c92020-12-21 14:45:50 +05301202 else:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301203 msg = _(
1204 "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction."
1205 ).format(
s-aga-rf0acb202023-04-12 14:13:54 +05301206 frappe.bold(abs(deficiency)),
Ankush Menat494bd9e2022-03-28 18:52:46 +05301207 frappe.get_desk_link("Item", exceptions[0]["item_code"]),
1208 frappe.get_desk_link("Warehouse", warehouse),
1209 exceptions[0]["posting_date"],
1210 exceptions[0]["posting_time"],
1211 frappe.get_desk_link(exceptions[0]["voucher_type"], exceptions[0]["voucher_no"]),
1212 )
Rushabh Mehta538607e2016-06-12 11:03:00 +05301213
Nabin Haita77b8c92020-12-21 14:45:50 +05301214 if msg:
s-aga-rf0acb202023-04-12 14:13:54 +05301215 if self.reserved_stock:
s-aga-r7e8fd8f2023-04-21 17:44:44 +05301216 allowed_qty = abs(exceptions[0]["actual_qty"]) - abs(exceptions[0]["diff"])
s-aga-r2d8363a2023-09-02 11:02:24 +05301217
1218 if allowed_qty > 0:
1219 msg = "{0} As {1} units are reserved for other sales orders, you are allowed to consume only {2} units.".format(
1220 msg, frappe.bold(self.reserved_stock), frappe.bold(allowed_qty)
1221 )
1222 else:
1223 msg = "{0} As the full stock is reserved for other sales orders, you're not allowed to consume the stock.".format(
1224 msg,
1225 )
s-aga-rf0acb202023-04-12 14:13:54 +05301226
Nabin Haita77b8c92020-12-21 14:45:50 +05301227 msg_list.append(msg)
1228
1229 if msg_list:
1230 message = "\n\n".join(msg_list)
1231 if self.verbose:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301232 frappe.throw(message, NegativeStockError, title=_("Insufficient Stock"))
Nabin Haita77b8c92020-12-21 14:45:50 +05301233 else:
1234 raise NegativeStockError(message)
Deepesh Gargb4be2922021-01-28 13:09:56 +05301235
Rohit Waghchaure9e5e2de2023-05-25 23:41:56 +05301236 def update_bin_data(self, sle):
1237 bin_name = get_or_make_bin(sle.item_code, sle.warehouse)
Rohit Waghchaure718ad3f2023-05-26 11:29:22 +05301238 values_to_update = {
1239 "actual_qty": sle.qty_after_transaction,
1240 "stock_value": sle.stock_value,
1241 }
1242
1243 if sle.valuation_rate is not None:
1244 values_to_update["valuation_rate"] = sle.valuation_rate
1245
1246 frappe.db.set_value("Bin", bin_name, values_to_update)
Rohit Waghchaure9e5e2de2023-05-25 23:41:56 +05301247
Nabin Haita77b8c92020-12-21 14:45:50 +05301248 def update_bin(self):
1249 # update bin for each warehouse
Ankush Menat8fe5feb2021-11-04 19:48:32 +05301250 for warehouse, data in self.data.items():
Ankush Menat97060c42021-12-03 11:50:38 +05301251 bin_name = get_or_make_bin(self.item_code, warehouse)
Deepesh Garg6f107da2021-10-12 20:15:55 +05301252
Ankush Menat494bd9e2022-03-28 18:52:46 +05301253 updated_values = {"actual_qty": data.qty_after_transaction, "stock_value": data.stock_value}
Ankush Menat7dd10362022-03-10 17:07:57 +05301254 if data.valuation_rate is not None:
1255 updated_values["valuation_rate"] = data.valuation_rate
Ankush Menat8376fbc2022-10-06 20:35:33 +05301256 frappe.db.set_value("Bin", bin_name, updated_values, update_modified=True)
Nabin Haitdc82d4f2014-04-07 12:02:57 +05301257
marination8418c4b2021-06-22 21:35:25 +05301258
Rohit Waghchaure6d513e22023-02-02 18:40:15 +05301259def get_previous_sle_of_current_voucher(args, operator="<", exclude_current_voucher=False):
marination8418c4b2021-06-22 21:35:25 +05301260 """get stock ledger entries filtered by specific posting datetime conditions"""
1261
Ankush Menat494bd9e2022-03-28 18:52:46 +05301262 args["time_format"] = "%H:%i:%s"
marination8418c4b2021-06-22 21:35:25 +05301263 if not args.get("posting_date"):
1264 args["posting_date"] = "1900-01-01"
1265 if not args.get("posting_time"):
1266 args["posting_time"] = "00:00"
1267
1268 voucher_condition = ""
1269 if exclude_current_voucher:
1270 voucher_no = args.get("voucher_no")
1271 voucher_condition = f"and voucher_no != '{voucher_no}'"
1272
Ankush Menat494bd9e2022-03-28 18:52:46 +05301273 sle = frappe.db.sql(
1274 """
marination8418c4b2021-06-22 21:35:25 +05301275 select *, timestamp(posting_date, posting_time) as "timestamp"
1276 from `tabStock Ledger Entry`
1277 where item_code = %(item_code)s
1278 and warehouse = %(warehouse)s
1279 and is_cancelled = 0
1280 {voucher_condition}
Rohit Waghchaurea05c47e2022-12-22 10:24:04 +05301281 and (
1282 posting_date < %(posting_date)s or
1283 (
1284 posting_date = %(posting_date)s and
Rohit Waghchaure6d513e22023-02-02 18:40:15 +05301285 time_format(posting_time, %(time_format)s) {operator} time_format(%(posting_time)s, %(time_format)s)
Rohit Waghchaurea05c47e2022-12-22 10:24:04 +05301286 )
1287 )
marination8418c4b2021-06-22 21:35:25 +05301288 order by timestamp(posting_date, posting_time) desc, creation desc
1289 limit 1
Ankush Menat494bd9e2022-03-28 18:52:46 +05301290 for update""".format(
Rohit Waghchaure6d513e22023-02-02 18:40:15 +05301291 operator=operator, voucher_condition=voucher_condition
Ankush Menat494bd9e2022-03-28 18:52:46 +05301292 ),
1293 args,
1294 as_dict=1,
1295 )
marination8418c4b2021-06-22 21:35:25 +05301296
1297 return sle[0] if sle else frappe._dict()
1298
Ankush Menat494bd9e2022-03-28 18:52:46 +05301299
Anand Doshi4dc7caa2013-01-11 11:44:49 +05301300def get_previous_sle(args, for_update=False):
Anand Doshi1b531862013-01-10 19:29:51 +05301301 """
Ankush Menat494bd9e2022-03-28 18:52:46 +05301302 get the last sle on or before the current time-bucket,
1303 to get actual qty before transaction, this function
1304 is called from various transaction like stock entry, reco etc
Nabin Haitdc82d4f2014-04-07 12:02:57 +05301305
Ankush Menat494bd9e2022-03-28 18:52:46 +05301306 args = {
1307 "item_code": "ABC",
1308 "warehouse": "XYZ",
1309 "posting_date": "2012-12-12",
1310 "posting_time": "12:00",
1311 "sle": "name of reference Stock Ledger Entry"
1312 }
Anand Doshi1b531862013-01-10 19:29:51 +05301313 """
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301314 args["name"] = args.get("sle", None) or ""
1315 sle = get_stock_ledger_entries(args, "<=", "desc", "limit 1", for_update=for_update)
Pratik Vyas16371b72013-09-18 18:31:03 +05301316 return sle and sle[0] or {}
Nabin Haitfb6e4342014-10-15 11:34:40 +05301317
Ankush Menat494bd9e2022-03-28 18:52:46 +05301318
1319def get_stock_ledger_entries(
1320 previous_sle,
1321 operator=None,
1322 order="desc",
1323 limit=None,
1324 for_update=False,
1325 debug=False,
1326 check_serial_no=True,
1327):
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301328 """get stock ledger entries filtered by specific posting datetime conditions"""
Ankush Menat494bd9e2022-03-28 18:52:46 +05301329 conditions = " and timestamp(posting_date, posting_time) {0} timestamp(%(posting_date)s, %(posting_time)s)".format(
1330 operator
1331 )
Nabin Haitb9ce1042018-02-01 14:58:50 +05301332 if previous_sle.get("warehouse"):
1333 conditions += " and warehouse = %(warehouse)s"
1334 elif previous_sle.get("warehouse_condition"):
1335 conditions += " and " + previous_sle.get("warehouse_condition")
1336
Rohit Waghchaure66aa37f2019-05-24 16:53:51 +05301337 if check_serial_no and previous_sle.get("serial_no"):
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +05301338 # conditions += " and serial_no like {}".format(frappe.db.escape('%{0}%'.format(previous_sle.get("serial_no"))))
1339 serial_no = previous_sle.get("serial_no")
Ankush Menat494bd9e2022-03-28 18:52:46 +05301340 conditions += (
1341 """ and
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +05301342 (
1343 serial_no = {0}
1344 or serial_no like {1}
1345 or serial_no like {2}
1346 or serial_no like {3}
1347 )
Ankush Menat494bd9e2022-03-28 18:52:46 +05301348 """
1349 ).format(
1350 frappe.db.escape(serial_no),
1351 frappe.db.escape("{}\n%".format(serial_no)),
1352 frappe.db.escape("%\n{}".format(serial_no)),
1353 frappe.db.escape("%\n{}\n%".format(serial_no)),
1354 )
Rohit Waghchaure05d3bcb2019-04-28 18:39:18 +05301355
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301356 if not previous_sle.get("posting_date"):
1357 previous_sle["posting_date"] = "1900-01-01"
1358 if not previous_sle.get("posting_time"):
1359 previous_sle["posting_time"] = "00:00"
1360
1361 if operator in (">", "<=") and previous_sle.get("name"):
1362 conditions += " and name!=%(name)s"
1363
Ankush Menat494bd9e2022-03-28 18:52:46 +05301364 return frappe.db.sql(
1365 """
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +05301366 select *, timestamp(posting_date, posting_time) as "timestamp"
1367 from `tabStock Ledger Entry`
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301368 where item_code = %%(item_code)s
Nabin Haita77b8c92020-12-21 14:45:50 +05301369 and is_cancelled = 0
Nabin Haitb9ce1042018-02-01 14:58:50 +05301370 %(conditions)s
Aditya Hase0c164242019-01-07 22:07:13 +05301371 order by timestamp(posting_date, posting_time) %(order)s, creation %(order)s
Ankush Menat494bd9e2022-03-28 18:52:46 +05301372 %(limit)s %(for_update)s"""
1373 % {
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301374 "conditions": conditions,
1375 "limit": limit or "",
1376 "for_update": for_update and "for update" or "",
Ankush Menat494bd9e2022-03-28 18:52:46 +05301377 "order": order,
1378 },
1379 previous_sle,
1380 as_dict=1,
1381 debug=debug,
1382 )
1383
Rushabh Mehtadf9e80c2015-02-17 19:55:17 +05301384
Nabin Haita77b8c92020-12-21 14:45:50 +05301385def get_sle_by_voucher_detail_no(voucher_detail_no, excluded_sle=None):
Ankush Menat494bd9e2022-03-28 18:52:46 +05301386 return frappe.db.get_value(
1387 "Stock Ledger Entry",
Rohit Waghchauref8c852c2023-02-01 15:38:12 +05301388 {"voucher_detail_no": voucher_detail_no, "name": ["!=", excluded_sle], "is_cancelled": 0},
Ankush Menat494bd9e2022-03-28 18:52:46 +05301389 [
1390 "item_code",
1391 "warehouse",
Rohit Waghchaureb77a8082023-07-01 11:30:46 +05301392 "actual_qty",
1393 "qty_after_transaction",
Ankush Menat494bd9e2022-03-28 18:52:46 +05301394 "posting_date",
1395 "posting_time",
Rohit Waghchaurec16a5812023-07-11 17:51:27 +05301396 "voucher_detail_no",
Ankush Menat494bd9e2022-03-28 18:52:46 +05301397 "timestamp(posting_date, posting_time) as timestamp",
1398 ],
1399 as_dict=1,
1400 )
Deepesh Garg2a9c5ba2020-04-30 10:38:58 +05301401
Ankush Menatce0514c2022-02-15 11:41:41 +05301402
Ankush Menat494bd9e2022-03-28 18:52:46 +05301403def get_batch_incoming_rate(
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301404 item_code, warehouse, serial_and_batch_bundle, posting_date, posting_time, creation=None
Ankush Menat494bd9e2022-03-28 18:52:46 +05301405):
1406
Ankush Menat102fff22022-02-19 15:51:04 +05301407 sle = frappe.qb.DocType("Stock Ledger Entry")
Rohit Waghchaure5bb31732023-03-21 10:54:41 +05301408 batch_ledger = frappe.qb.DocType("Serial and Batch Entry")
Ankush Menat102fff22022-02-19 15:51:04 +05301409
Ankush Menate1c16872022-04-21 20:01:48 +05301410 timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime(
Ankush Menat494bd9e2022-03-28 18:52:46 +05301411 posting_date, posting_time
1412 )
Ankush Menat102fff22022-02-19 15:51:04 +05301413 if creation:
1414 timestamp_condition |= (
Ankush Menate1c16872022-04-21 20:01:48 +05301415 CombineDatetime(sle.posting_date, sle.posting_time)
1416 == CombineDatetime(posting_date, posting_time)
Ankush Menat494bd9e2022-03-28 18:52:46 +05301417 ) & (sle.creation < creation)
Ankush Menat102fff22022-02-19 15:51:04 +05301418
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301419 batches = frappe.get_all(
Rohit Waghchaure5bb31732023-03-21 10:54:41 +05301420 "Serial and Batch Entry", fields=["batch_no"], filters={"parent": serial_and_batch_bundle}
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301421 )
1422
Ankush Menat102fff22022-02-19 15:51:04 +05301423 batch_details = (
Ankush Menat494bd9e2022-03-28 18:52:46 +05301424 frappe.qb.from_(sle)
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301425 .inner_join(batch_ledger)
1426 .on(sle.serial_and_batch_bundle == batch_ledger.parent)
1427 .select(
1428 Sum(
1429 Case()
1430 .when(sle.actual_qty > 0, batch_ledger.qty * batch_ledger.incoming_rate)
1431 .else_(batch_ledger.qty * batch_ledger.outgoing_rate * -1)
1432 ).as_("batch_value"),
1433 Sum(Case().when(sle.actual_qty > 0, batch_ledger.qty).else_(batch_ledger.qty * -1)).as_(
1434 "batch_qty"
1435 ),
1436 )
Ankush Menat494bd9e2022-03-28 18:52:46 +05301437 .where(
1438 (sle.item_code == item_code)
1439 & (sle.warehouse == warehouse)
Rohit Waghchaure6c9b2122022-12-05 14:48:18 +05301440 & (batch_ledger.batch_no.isin([row.batch_no for row in batches]))
Ankush Menat494bd9e2022-03-28 18:52:46 +05301441 & (sle.is_cancelled == 0)
1442 )
1443 .where(timestamp_condition)
Ankush Menat102fff22022-02-19 15:51:04 +05301444 ).run(as_dict=True)
Ankush Menatce0514c2022-02-15 11:41:41 +05301445
1446 if batch_details and batch_details[0].batch_qty:
1447 return batch_details[0].batch_value / batch_details[0].batch_qty
1448
1449
Ankush Menat494bd9e2022-03-28 18:52:46 +05301450def get_valuation_rate(
1451 item_code,
1452 warehouse,
1453 voucher_type,
1454 voucher_no,
1455 allow_zero_rate=False,
1456 currency=None,
1457 company=None,
1458 raise_error_if_no_rate=True,
Rohit Waghchaurec2d74612023-03-29 11:40:36 +05301459 serial_and_batch_bundle=None,
Ankush Menat494bd9e2022-03-28 18:52:46 +05301460):
Rohit Waghchaurea5f40942017-06-16 15:21:36 +05301461
Rohit Waghchaured3ceb072023-03-31 09:03:54 +05301462 from erpnext.stock.serial_batch_bundle import BatchNoValuation
1463
Ankush Menatf7ffe042021-11-01 13:21:14 +05301464 if not company:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301465 company = frappe.get_cached_value("Warehouse", warehouse, "company")
Ankush Menatf7ffe042021-11-01 13:21:14 +05301466
Ankush Menat342d09a2022-02-19 14:28:51 +05301467 last_valuation_rate = None
1468
1469 # Get moving average rate of a specific batch number
Rohit Waghchaurec2d74612023-03-29 11:40:36 +05301470 if warehouse and serial_and_batch_bundle:
1471 batch_obj = BatchNoValuation(
1472 sle=frappe._dict(
1473 {
1474 "item_code": item_code,
1475 "warehouse": warehouse,
1476 "actual_qty": -1,
1477 "serial_and_batch_bundle": serial_and_batch_bundle,
1478 }
1479 )
Ankush Menat494bd9e2022-03-28 18:52:46 +05301480 )
Ankush Menat342d09a2022-02-19 14:28:51 +05301481
Rohit Waghchaurec2d74612023-03-29 11:40:36 +05301482 return batch_obj.get_incoming_rate()
1483
Ankush Menatf7ffe042021-11-01 13:21:14 +05301484 # Get valuation rate from last sle for the same item and warehouse
Ankush Menat342d09a2022-02-19 14:28:51 +05301485 if not last_valuation_rate or last_valuation_rate[0][0] is None:
Ankush Menat494bd9e2022-03-28 18:52:46 +05301486 last_valuation_rate = frappe.db.sql(
1487 """select valuation_rate
Ankush Menat342d09a2022-02-19 14:28:51 +05301488 from `tabStock Ledger Entry` force index (item_warehouse)
1489 where
1490 item_code = %s
1491 AND warehouse = %s
1492 AND valuation_rate >= 0
1493 AND is_cancelled = 0
1494 AND NOT (voucher_no = %s AND voucher_type = %s)
Ankush Menat494bd9e2022-03-28 18:52:46 +05301495 order by posting_date desc, posting_time desc, name desc limit 1""",
1496 (item_code, warehouse, voucher_no, voucher_type),
1497 )
Nabin Haitfb6e4342014-10-15 11:34:40 +05301498
Nabin Haita645f362018-03-01 10:31:24 +05301499 if last_valuation_rate:
Nabin Haita77b8c92020-12-21 14:45:50 +05301500 return flt(last_valuation_rate[0][0])
Nabin Haita645f362018-03-01 10:31:24 +05301501
1502 # If negative stock allowed, and item delivered without any incoming entry,
1503 # system does not found any SLE, then take valuation rate from Item
1504 valuation_rate = frappe.db.get_value("Item", item_code, "valuation_rate")
Nabin Haitfb6e4342014-10-15 11:34:40 +05301505
1506 if not valuation_rate:
Nabin Haita645f362018-03-01 10:31:24 +05301507 # try Item Standard rate
1508 valuation_rate = frappe.db.get_value("Item", item_code, "standard_rate")
Nabin Haitfb6e4342014-10-15 11:34:40 +05301509
Rushabh Mehtaaedaac62017-05-04 09:35:19 +05301510 if not valuation_rate:
Nabin Haita645f362018-03-01 10:31:24 +05301511 # try in price list
Ankush Menat494bd9e2022-03-28 18:52:46 +05301512 valuation_rate = frappe.db.get_value(
1513 "Item Price", dict(item_code=item_code, buying=1, currency=currency), "price_list_rate"
1514 )
Rushabh Mehtacc8b2b22017-03-31 12:44:29 +05301515
Ankush Menat494bd9e2022-03-28 18:52:46 +05301516 if (
1517 not allow_zero_rate
1518 and not valuation_rate
1519 and raise_error_if_no_rate
1520 and cint(erpnext.is_perpetual_inventory_enabled(company))
1521 ):
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +05301522 form_link = get_link_to_form("Item", item_code)
Marica97715f22020-05-11 20:45:37 +05301523
Ankush Menat494bd9e2022-03-28 18:52:46 +05301524 message = _(
1525 "Valuation Rate for the Item {0}, is required to do accounting entries for {1} {2}."
1526 ).format(form_link, voucher_type, voucher_no)
Rohit Waghchaurebb3e5d02021-04-24 17:28:33 +05301527 message += "<br><br>" + _("Here are the options to proceed:")
Ankush Menat494bd9e2022-03-28 18:52:46 +05301528 solutions = (
1529 "<li>"
1530 + _(
1531 "If the item is transacting as a Zero Valuation Rate item in this entry, please enable 'Allow Zero Valuation Rate' in the {0} Item table."
1532 ).format(voucher_type)
1533 + "</li>"
1534 )
1535 solutions += (
1536 "<li>"
1537 + _("If not, you can Cancel / Submit this entry")
1538 + " {0} ".format(frappe.bold("after"))
1539 + _("performing either one below:")
1540 + "</li>"
1541 )
Marica97715f22020-05-11 20:45:37 +05301542 sub_solutions = "<ul><li>" + _("Create an incoming stock transaction for the Item.") + "</li>"
1543 sub_solutions += "<li>" + _("Mention Valuation Rate in the Item master.") + "</li></ul>"
1544 msg = message + solutions + sub_solutions + "</li>"
1545
1546 frappe.throw(msg=msg, title=_("Valuation Rate Missing"))
Nabin Haitfb6e4342014-10-15 11:34:40 +05301547
1548 return valuation_rate
Nabin Haita77b8c92020-12-21 14:45:50 +05301549
Ankush Menat494bd9e2022-03-28 18:52:46 +05301550
Ankush Menate7109c12021-08-26 16:40:45 +05301551def update_qty_in_future_sle(args, allow_negative_stock=False):
marination8418c4b2021-06-22 21:35:25 +05301552 """Recalculate Qty after Transaction in future SLEs based on current SLE."""
marination40389772021-07-02 17:13:45 +05301553 datetime_limit_condition = ""
marination8418c4b2021-06-22 21:35:25 +05301554 qty_shift = args.actual_qty
1555
Ankush Menat7c839c42022-05-06 12:09:08 +05301556 args["time_format"] = "%H:%i:%s"
1557
marination8418c4b2021-06-22 21:35:25 +05301558 # find difference/shift in qty caused by stock reconciliation
1559 if args.voucher_type == "Stock Reconciliation":
marination40389772021-07-02 17:13:45 +05301560 qty_shift = get_stock_reco_qty_shift(args)
1561
1562 # find the next nearest stock reco so that we only recalculate SLEs till that point
1563 next_stock_reco_detail = get_next_stock_reco(args)
1564 if next_stock_reco_detail:
1565 detail = next_stock_reco_detail[0]
Rohit Waghchaure42b22942023-05-27 19:18:03 +05301566 if detail.batch_no or (detail.serial_and_batch_bundle and detail.has_batch_no):
Rohit Waghchaureef4bd772023-04-04 23:56:57 +05301567 regenerate_sle_for_batch_stock_reco(detail)
1568
marination40389772021-07-02 17:13:45 +05301569 # add condition to update SLEs before this date & time
1570 datetime_limit_condition = get_datetime_limit_condition(detail)
marination8418c4b2021-06-22 21:35:25 +05301571
Ankush Menat494bd9e2022-03-28 18:52:46 +05301572 frappe.db.sql(
Ankush Menat7e2fbc02022-05-09 11:13:31 +05301573 f"""
Nabin Hait186a0452021-02-18 14:14:21 +05301574 update `tabStock Ledger Entry`
marination8418c4b2021-06-22 21:35:25 +05301575 set qty_after_transaction = qty_after_transaction + {qty_shift}
Nabin Hait186a0452021-02-18 14:14:21 +05301576 where
1577 item_code = %(item_code)s
1578 and warehouse = %(warehouse)s
1579 and voucher_no != %(voucher_no)s
1580 and is_cancelled = 0
Rohit Waghchaurea05c47e2022-12-22 10:24:04 +05301581 and (
1582 posting_date > %(posting_date)s or
1583 (
1584 posting_date = %(posting_date)s and
1585 time_format(posting_time, %(time_format)s) > time_format(%(posting_time)s, %(time_format)s)
1586 )
1587 )
marination40389772021-07-02 17:13:45 +05301588 {datetime_limit_condition}
Ankush Menat7e2fbc02022-05-09 11:13:31 +05301589 """,
Ankush Menat494bd9e2022-03-28 18:52:46 +05301590 args,
1591 )
Nabin Hait186a0452021-02-18 14:14:21 +05301592
1593 validate_negative_qty_in_future_sle(args, allow_negative_stock)
1594
Ankush Menat494bd9e2022-03-28 18:52:46 +05301595
Rohit Waghchaureef4bd772023-04-04 23:56:57 +05301596def regenerate_sle_for_batch_stock_reco(detail):
1597 doc = frappe.get_cached_doc("Stock Reconciliation", detail.voucher_no)
Rohit Waghchaureef4bd772023-04-04 23:56:57 +05301598 doc.recalculate_current_qty(detail.item_code, detail.batch_no)
Rohit Waghchauref2253dd2023-04-22 11:16:12 +05301599
1600 if not frappe.db.exists(
1601 "Repost Item Valuation", {"voucher_no": doc.name, "status": "Queued", "docstatus": "1"}
1602 ):
Rohit Waghchaure2d5ccc02023-05-01 21:17:18 +05301603 doc.repost_future_sle_and_gle(force=True)
Rohit Waghchaureef4bd772023-04-04 23:56:57 +05301604
1605
marination40389772021-07-02 17:13:45 +05301606def get_stock_reco_qty_shift(args):
1607 stock_reco_qty_shift = 0
1608 if args.get("is_cancelled"):
1609 if args.get("previous_qty_after_transaction"):
1610 # get qty (balance) that was set at submission
1611 last_balance = args.get("previous_qty_after_transaction")
1612 stock_reco_qty_shift = flt(args.qty_after_transaction) - flt(last_balance)
1613 else:
1614 stock_reco_qty_shift = flt(args.actual_qty)
1615 else:
1616 # reco is being submitted
Rohit Waghchaure6d513e22023-02-02 18:40:15 +05301617 last_balance = get_previous_sle_of_current_voucher(args, "<=", exclude_current_voucher=True).get(
Ankush Menat494bd9e2022-03-28 18:52:46 +05301618 "qty_after_transaction"
1619 )
marination40389772021-07-02 17:13:45 +05301620
1621 if last_balance is not None:
1622 stock_reco_qty_shift = flt(args.qty_after_transaction) - flt(last_balance)
1623 else:
1624 stock_reco_qty_shift = args.qty_after_transaction
1625
1626 return stock_reco_qty_shift
1627
Ankush Menat494bd9e2022-03-28 18:52:46 +05301628
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301629def get_next_stock_reco(kwargs):
marination40389772021-07-02 17:13:45 +05301630 """Returns next nearest stock reconciliaton's details."""
1631
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301632 sle = frappe.qb.DocType("Stock Ledger Entry")
1633
1634 query = (
1635 frappe.qb.from_(sle)
1636 .select(
1637 sle.name,
1638 sle.posting_date,
1639 sle.posting_time,
1640 sle.creation,
1641 sle.voucher_no,
1642 sle.item_code,
1643 sle.batch_no,
Rohit Waghchaure42b22942023-05-27 19:18:03 +05301644 sle.serial_and_batch_bundle,
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301645 sle.actual_qty,
Rohit Waghchaure42b22942023-05-27 19:18:03 +05301646 sle.has_batch_no,
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301647 )
1648 .where(
1649 (sle.item_code == kwargs.get("item_code"))
1650 & (sle.warehouse == kwargs.get("warehouse"))
1651 & (sle.voucher_type == "Stock Reconciliation")
1652 & (sle.voucher_no != kwargs.get("voucher_no"))
1653 & (sle.is_cancelled == 0)
1654 & (
1655 (
1656 CombineDatetime(sle.posting_date, sle.posting_time)
1657 > CombineDatetime(kwargs.get("posting_date"), kwargs.get("posting_time"))
Rohit Waghchaure379b2152023-04-24 17:32:32 +05301658 )
1659 | (
1660 (
1661 CombineDatetime(sle.posting_date, sle.posting_time)
1662 == CombineDatetime(kwargs.get("posting_date"), kwargs.get("posting_time"))
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301663 )
Rohit Waghchaure379b2152023-04-24 17:32:32 +05301664 & (sle.creation > kwargs.get("creation"))
marination40389772021-07-02 17:13:45 +05301665 )
1666 )
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301667 )
Rohit Waghchaure6bccd862023-04-17 14:22:27 +05301668 .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
1669 .orderby(sle.creation)
Rohit Waghchaurefcfa8842023-04-20 09:48:15 +05301670 .limit(1)
Ankush Menat494bd9e2022-03-28 18:52:46 +05301671 )
1672
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301673 if kwargs.get("batch_no"):
Deepesh Gargea6eeac2023-04-20 12:48:44 +05301674 query = query.where(sle.batch_no == kwargs.get("batch_no"))
Rohit Waghchaured9dd64b2023-04-14 13:00:12 +05301675
1676 return query.run(as_dict=True)
1677
marination40389772021-07-02 17:13:45 +05301678
1679def get_datetime_limit_condition(detail):
marination40389772021-07-02 17:13:45 +05301680 return f"""
1681 and
1682 (timestamp(posting_date, posting_time) < timestamp('{detail.posting_date}', '{detail.posting_time}')
1683 or (
1684 timestamp(posting_date, posting_time) = timestamp('{detail.posting_date}', '{detail.posting_time}')
1685 and creation < '{detail.creation}'
1686 )
1687 )"""
1688
Ankush Menat494bd9e2022-03-28 18:52:46 +05301689
Ankush Menate7109c12021-08-26 16:40:45 +05301690def validate_negative_qty_in_future_sle(args, allow_negative_stock=False):
Ankush Menateb8b4242022-02-12 13:08:28 +05301691 if allow_negative_stock or is_negative_stock_allowed(item_code=args.item_code):
Ankush Menat5eba5752021-12-07 23:03:52 +05301692 return
1693 if not (args.actual_qty < 0 or args.voucher_type == "Stock Reconciliation"):
1694 return
Deepesh Gargb4be2922021-01-28 13:09:56 +05301695
Ankush Menat5eba5752021-12-07 23:03:52 +05301696 neg_sle = get_future_sle_with_negative_qty(args)
Maricad6078aa2022-06-17 15:13:13 +05301697
1698 if is_negative_with_precision(neg_sle):
Ankush Menat494bd9e2022-03-28 18:52:46 +05301699 message = _(
1700 "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction."
1701 ).format(
Ankush Menat5eba5752021-12-07 23:03:52 +05301702 abs(neg_sle[0]["qty_after_transaction"]),
Ankush Menat494bd9e2022-03-28 18:52:46 +05301703 frappe.get_desk_link("Item", args.item_code),
1704 frappe.get_desk_link("Warehouse", args.warehouse),
1705 neg_sle[0]["posting_date"],
1706 neg_sle[0]["posting_time"],
1707 frappe.get_desk_link(neg_sle[0]["voucher_type"], neg_sle[0]["voucher_no"]),
1708 )
Ankush Menat5eba5752021-12-07 23:03:52 +05301709
Ankush Menat494bd9e2022-03-28 18:52:46 +05301710 frappe.throw(message, NegativeStockError, title=_("Insufficient Stock"))
Ankush Menat5eba5752021-12-07 23:03:52 +05301711
1712 if not args.batch_no:
1713 return
1714
1715 neg_batch_sle = get_future_sle_with_negative_batch_qty(args)
Maricad6078aa2022-06-17 15:13:13 +05301716 if is_negative_with_precision(neg_batch_sle, is_batch=True):
Ankush Menat494bd9e2022-03-28 18:52:46 +05301717 message = _(
1718 "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction."
1719 ).format(
Ankush Menat5eba5752021-12-07 23:03:52 +05301720 abs(neg_batch_sle[0]["cumulative_total"]),
Ankush Menat494bd9e2022-03-28 18:52:46 +05301721 frappe.get_desk_link("Batch", args.batch_no),
1722 frappe.get_desk_link("Warehouse", args.warehouse),
1723 neg_batch_sle[0]["posting_date"],
1724 neg_batch_sle[0]["posting_time"],
1725 frappe.get_desk_link(neg_batch_sle[0]["voucher_type"], neg_batch_sle[0]["voucher_no"]),
1726 )
mergify[bot]7a3d3012022-03-08 10:42:30 +05301727 frappe.throw(message, NegativeStockError, title=_("Insufficient Stock for Batch"))
Ankush Menat5eba5752021-12-07 23:03:52 +05301728
Nabin Haita77b8c92020-12-21 14:45:50 +05301729
Maricad6078aa2022-06-17 15:13:13 +05301730def is_negative_with_precision(neg_sle, is_batch=False):
1731 """
1732 Returns whether system precision rounded qty is insufficient.
1733 E.g: -0.0003 in precision 3 (0.000) is sufficient for the user.
1734 """
1735
1736 if not neg_sle:
1737 return False
1738
1739 field = "cumulative_total" if is_batch else "qty_after_transaction"
1740 precision = cint(frappe.db.get_default("float_precision")) or 2
1741 qty_deficit = flt(neg_sle[0][field], precision)
1742
1743 return qty_deficit < 0 and abs(qty_deficit) > 0.0001
1744
1745
Nabin Haita77b8c92020-12-21 14:45:50 +05301746def get_future_sle_with_negative_qty(args):
Ankush Menat494bd9e2022-03-28 18:52:46 +05301747 return frappe.db.sql(
1748 """
Nabin Haita77b8c92020-12-21 14:45:50 +05301749 select
1750 qty_after_transaction, posting_date, posting_time,
1751 voucher_type, voucher_no
1752 from `tabStock Ledger Entry`
Deepesh Gargb4be2922021-01-28 13:09:56 +05301753 where
Nabin Haita77b8c92020-12-21 14:45:50 +05301754 item_code = %(item_code)s
1755 and warehouse = %(warehouse)s
1756 and voucher_no != %(voucher_no)s
1757 and timestamp(posting_date, posting_time) >= timestamp(%(posting_date)s, %(posting_time)s)
1758 and is_cancelled = 0
Nabin Hait186a0452021-02-18 14:14:21 +05301759 and qty_after_transaction < 0
Nabin Hait243d59b2021-02-02 16:55:13 +05301760 order by timestamp(posting_date, posting_time) asc
Nabin Haita77b8c92020-12-21 14:45:50 +05301761 limit 1
Ankush Menat494bd9e2022-03-28 18:52:46 +05301762 """,
1763 args,
1764 as_dict=1,
1765 )
Ankush Menat6a014d12021-04-12 20:21:27 +05301766
Ankush Menat5eba5752021-12-07 23:03:52 +05301767
1768def get_future_sle_with_negative_batch_qty(args):
Ankush Menat494bd9e2022-03-28 18:52:46 +05301769 return frappe.db.sql(
1770 """
Ankush Menat5eba5752021-12-07 23:03:52 +05301771 with batch_ledger as (
1772 select
1773 posting_date, posting_time, voucher_type, voucher_no,
1774 sum(actual_qty) over (order by posting_date, posting_time, creation) as cumulative_total
1775 from `tabStock Ledger Entry`
1776 where
1777 item_code = %(item_code)s
1778 and warehouse = %(warehouse)s
1779 and batch_no=%(batch_no)s
1780 and is_cancelled = 0
1781 order by posting_date, posting_time, creation
1782 )
1783 select * from batch_ledger
1784 where
1785 cumulative_total < 0.0
1786 and timestamp(posting_date, posting_time) >= timestamp(%(posting_date)s, %(posting_time)s)
1787 limit 1
Ankush Menat494bd9e2022-03-28 18:52:46 +05301788 """,
1789 args,
1790 as_dict=1,
1791 )
Ankush Menateb8b4242022-02-12 13:08:28 +05301792
1793
1794def is_negative_stock_allowed(*, item_code: Optional[str] = None) -> bool:
1795 if cint(frappe.db.get_single_value("Stock Settings", "allow_negative_stock", cache=True)):
1796 return True
1797 if item_code and cint(frappe.db.get_value("Item", item_code, "allow_negative_stock", cache=True)):
1798 return True
1799 return False
Rohit Waghchaure683a47f2022-10-11 15:11:39 +05301800
1801
1802def get_incoming_rate_for_inter_company_transfer(sle) -> float:
1803 """
1804 For inter company transfer, incoming rate is the average of the outgoing rate
1805 """
1806 rate = 0.0
1807
1808 field = "delivery_note_item" if sle.voucher_type == "Purchase Receipt" else "sales_invoice_item"
1809
1810 doctype = "Delivery Note Item" if sle.voucher_type == "Purchase Receipt" else "Sales Invoice Item"
1811
1812 reference_name = frappe.get_cached_value(sle.voucher_type + " Item", sle.voucher_detail_no, field)
1813
1814 if reference_name:
1815 rate = frappe.get_cached_value(
1816 doctype,
1817 reference_name,
1818 "incoming_rate",
1819 )
1820
1821 return rate
Rohit Waghchaureb5a2ccf2023-05-04 15:38:35 +05301822
1823
1824def is_internal_transfer(sle):
1825 data = frappe.get_cached_value(
1826 sle.voucher_type,
1827 sle.voucher_no,
1828 ["is_internal_supplier", "represents_company", "company"],
1829 as_dict=True,
1830 )
1831
1832 if data.is_internal_supplier and data.represents_company == data.company:
1833 return True