Anand Doshi | 885e074 | 2015-03-03 14:55:30 +0530 | [diff] [blame] | 1 | # Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors |
Rushabh Mehta | e67d1fb | 2013-08-05 14:59:54 +0530 | [diff] [blame] | 2 | # License: GNU General Public License v3. See license.txt |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 3 | |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 4 | import copy |
Nabin Hait | 26d4655 | 2013-01-09 15:23:05 +0530 | [diff] [blame] | 5 | import json |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 6 | from typing import Optional, Set, Tuple |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 7 | |
| 8 | import frappe |
| 9 | from frappe import _ |
| 10 | from frappe.model.meta import get_field_precision |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 11 | from frappe.query_builder.functions import Sum |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 12 | from frappe.utils import cint, cstr, flt, get_link_to_form, getdate, now, nowdate |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 13 | from pypika import CustomFunction |
Achilles Rasquinha | 361366e | 2018-02-14 17:08:59 +0530 | [diff] [blame] | 14 | |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 15 | import erpnext |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 16 | from erpnext.stock.doctype.bin.bin import update_qty as update_bin_qty |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 17 | from erpnext.stock.utils import ( |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 18 | get_incoming_outgoing_rate_for_cancel, |
Deepesh Garg | 6f107da | 2021-10-12 20:15:55 +0530 | [diff] [blame] | 19 | get_or_make_bin, |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 20 | get_valuation_method, |
| 21 | ) |
Ankush Menat | b534fee | 2022-02-19 20:58:36 +0530 | [diff] [blame] | 22 | from erpnext.stock.valuation import FIFOValuation, LIFOValuation, round_off_if_near_zero |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 23 | |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 24 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 25 | class NegativeStockError(frappe.ValidationError): |
| 26 | pass |
| 27 | |
| 28 | |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 29 | class SerialNoExistsInFutureTransaction(frappe.ValidationError): |
| 30 | pass |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 31 | |
Anand Doshi | 5b004ff | 2013-09-25 19:55:41 +0530 | [diff] [blame] | 32 | |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 33 | def make_sl_entries(sl_entries, allow_negative_stock=False, via_landed_cost_voucher=False): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 34 | """Create SL entries from SL entry dicts |
Ankush Menat | eb8495a | 2022-03-02 12:01:51 +0530 | [diff] [blame] | 35 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 36 | args: |
| 37 | - allow_negative_stock: disable negative stock valiations if true |
| 38 | - via_landed_cost_voucher: landed cost voucher cancels and reposts |
| 39 | entries of purchase document. This flag is used to identify if |
| 40 | cancellation and repost is happening via landed cost voucher, in |
| 41 | such cases certain validations need to be ignored (like negative |
| 42 | stock) |
Ankush Menat | eb8495a | 2022-03-02 12:01:51 +0530 | [diff] [blame] | 43 | """ |
Rohit Waghchaure | 4d81d45 | 2021-06-15 10:21:44 +0530 | [diff] [blame] | 44 | from erpnext.controllers.stock_controller import future_sle_exists |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 45 | |
Nabin Hait | ca77574 | 2013-09-26 16:16:44 +0530 | [diff] [blame] | 46 | if sl_entries: |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 47 | cancel = sl_entries[0].get("is_cancelled") |
Nabin Hait | ca77574 | 2013-09-26 16:16:44 +0530 | [diff] [blame] | 48 | if cancel: |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 49 | validate_cancellation(sl_entries) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 50 | set_as_cancel(sl_entries[0].get("voucher_type"), sl_entries[0].get("voucher_no")) |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 51 | |
Rohit Waghchaure | 4d81d45 | 2021-06-15 10:21:44 +0530 | [diff] [blame] | 52 | args = get_args_for_future_sle(sl_entries[0]) |
| 53 | future_sle_exists(args, sl_entries) |
| 54 | |
Nabin Hait | ca77574 | 2013-09-26 16:16:44 +0530 | [diff] [blame] | 55 | for sle in sl_entries: |
Ankush Menat | efc4b94 | 2022-03-02 11:19:12 +0530 | [diff] [blame] | 56 | if sle.serial_no and not via_landed_cost_voucher: |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 57 | validate_serial_no(sle) |
| 58 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 59 | if cancel: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 60 | sle["actual_qty"] = -flt(sle.get("actual_qty")) |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 61 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 62 | if sle["actual_qty"] < 0 and not sle.get("outgoing_rate"): |
| 63 | sle["outgoing_rate"] = get_incoming_outgoing_rate_for_cancel( |
| 64 | sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no |
| 65 | ) |
| 66 | sle["incoming_rate"] = 0.0 |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 67 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 68 | if sle["actual_qty"] > 0 and not sle.get("incoming_rate"): |
| 69 | sle["incoming_rate"] = get_incoming_outgoing_rate_for_cancel( |
| 70 | sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no |
| 71 | ) |
| 72 | sle["outgoing_rate"] = 0.0 |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 73 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 74 | if sle.get("actual_qty") or sle.get("voucher_type") == "Stock Reconciliation": |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 75 | sle_doc = make_entry(sle, allow_negative_stock, via_landed_cost_voucher) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 76 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 77 | args = sle_doc.as_dict() |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 78 | |
| 79 | if sle.get("voucher_type") == "Stock Reconciliation": |
| 80 | # preserve previous_qty_after_transaction for qty reposting |
| 81 | args.previous_qty_after_transaction = sle.get("previous_qty_after_transaction") |
| 82 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 83 | is_stock_item = frappe.get_cached_value("Item", args.get("item_code"), "is_stock_item") |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 84 | if is_stock_item: |
| 85 | bin_name = get_or_make_bin(args.get("item_code"), args.get("warehouse")) |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 86 | repost_current_voucher(args, allow_negative_stock, via_landed_cost_voucher) |
Ankush Menat | ff9a6e8 | 2021-12-20 15:07:41 +0530 | [diff] [blame] | 87 | update_bin_qty(bin_name, args) |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 88 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 89 | frappe.msgprint( |
| 90 | _("Item {0} ignored since it is not a stock item").format(args.get("item_code")) |
| 91 | ) |
| 92 | |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 93 | |
| 94 | def repost_current_voucher(args, allow_negative_stock=False, via_landed_cost_voucher=False): |
| 95 | if args.get("actual_qty") or args.get("voucher_type") == "Stock Reconciliation": |
| 96 | if not args.get("posting_date"): |
| 97 | args["posting_date"] = nowdate() |
| 98 | |
| 99 | if args.get("is_cancelled") and via_landed_cost_voucher: |
| 100 | return |
| 101 | |
| 102 | # Reposts only current voucher SL Entries |
| 103 | # Updates valuation rate, stock value, stock queue for current transaction |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 104 | update_entries_after( |
| 105 | { |
| 106 | "item_code": args.get("item_code"), |
| 107 | "warehouse": args.get("warehouse"), |
| 108 | "posting_date": args.get("posting_date"), |
| 109 | "posting_time": args.get("posting_time"), |
| 110 | "voucher_type": args.get("voucher_type"), |
| 111 | "voucher_no": args.get("voucher_no"), |
| 112 | "sle_id": args.get("name"), |
| 113 | "creation": args.get("creation"), |
| 114 | }, |
| 115 | allow_negative_stock=allow_negative_stock, |
| 116 | via_landed_cost_voucher=via_landed_cost_voucher, |
| 117 | ) |
Ankush Menat | cef84c2 | 2021-12-03 12:18:59 +0530 | [diff] [blame] | 118 | |
| 119 | # update qty in future sle and Validate negative qty |
| 120 | update_qty_in_future_sle(args, allow_negative_stock) |
| 121 | |
Nabin Hait | adeb976 | 2014-10-06 11:53:52 +0530 | [diff] [blame] | 122 | |
Rohit Waghchaure | 4d81d45 | 2021-06-15 10:21:44 +0530 | [diff] [blame] | 123 | def get_args_for_future_sle(row): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 124 | return frappe._dict( |
| 125 | { |
| 126 | "voucher_type": row.get("voucher_type"), |
| 127 | "voucher_no": row.get("voucher_no"), |
| 128 | "posting_date": row.get("posting_date"), |
| 129 | "posting_time": row.get("posting_time"), |
| 130 | } |
| 131 | ) |
| 132 | |
Rohit Waghchaure | 4d81d45 | 2021-06-15 10:21:44 +0530 | [diff] [blame] | 133 | |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 134 | def validate_serial_no(sle): |
| 135 | from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos |
Ankush Menat | 66bf21f | 2022-01-16 20:45:59 +0530 | [diff] [blame] | 136 | |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 137 | for sn in get_serial_nos(sle.serial_no): |
| 138 | args = copy.deepcopy(sle) |
| 139 | args.serial_no = sn |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 140 | args.warehouse = "" |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 141 | |
| 142 | vouchers = [] |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 143 | for row in get_stock_ledger_entries(args, ">"): |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 144 | voucher_type = frappe.bold(row.voucher_type) |
| 145 | voucher_no = frappe.bold(get_link_to_form(row.voucher_type, row.voucher_no)) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 146 | vouchers.append(f"{voucher_type} {voucher_no}") |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 147 | |
| 148 | if vouchers: |
| 149 | serial_no = frappe.bold(sn) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 150 | msg = ( |
| 151 | f"""The serial no {serial_no} has been used in the future transactions so you need to cancel them first. |
| 152 | The list of the transactions are as below.""" |
| 153 | + "<br><br><ul><li>" |
| 154 | ) |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 155 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 156 | msg += "</li><li>".join(vouchers) |
| 157 | msg += "</li></ul>" |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 158 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 159 | title = "Cannot Submit" if not sle.get("is_cancelled") else "Cannot Cancel" |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 160 | frappe.throw(_(msg), title=_(title), exc=SerialNoExistsInFutureTransaction) |
| 161 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 162 | |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 163 | def validate_cancellation(args): |
| 164 | if args[0].get("is_cancelled"): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 165 | repost_entry = frappe.db.get_value( |
| 166 | "Repost Item Valuation", |
| 167 | {"voucher_type": args[0].voucher_type, "voucher_no": args[0].voucher_no, "docstatus": 1}, |
| 168 | ["name", "status"], |
| 169 | as_dict=1, |
| 170 | ) |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 171 | |
| 172 | if repost_entry: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 173 | if repost_entry.status == "In Progress": |
| 174 | frappe.throw( |
| 175 | _( |
| 176 | "Cannot cancel the transaction. Reposting of item valuation on submission is not completed yet." |
| 177 | ) |
| 178 | ) |
| 179 | if repost_entry.status == "Queued": |
Nabin Hait | d46b236 | 2021-02-23 16:38:52 +0530 | [diff] [blame] | 180 | doc = frappe.get_doc("Repost Item Valuation", repost_entry.name) |
Ankush Menat | a281998 | 2022-04-08 13:20:25 +0530 | [diff] [blame] | 181 | doc.status = "Skipped" |
Ankush Menat | aa024fc | 2021-11-18 12:51:26 +0530 | [diff] [blame] | 182 | doc.flags.ignore_permissions = True |
Nabin Hait | d46b236 | 2021-02-23 16:38:52 +0530 | [diff] [blame] | 183 | doc.cancel() |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 184 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 185 | |
Nabin Hait | 9653f60 | 2013-08-20 15:37:33 +0530 | [diff] [blame] | 186 | def set_as_cancel(voucher_type, voucher_no): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 187 | frappe.db.sql( |
| 188 | """update `tabStock Ledger Entry` set is_cancelled=1, |
Nabin Hait | 9653f60 | 2013-08-20 15:37:33 +0530 | [diff] [blame] | 189 | modified=%s, modified_by=%s |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 190 | where voucher_type=%s and voucher_no=%s and is_cancelled = 0""", |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 191 | (now(), frappe.session.user, voucher_type, voucher_no), |
| 192 | ) |
| 193 | |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 194 | |
Nabin Hait | 54c865e | 2015-03-27 15:38:31 +0530 | [diff] [blame] | 195 | def make_entry(args, allow_negative_stock=False, via_landed_cost_voucher=False): |
Saqib Ansari | c7fc609 | 2021-10-12 13:30:40 +0530 | [diff] [blame] | 196 | args["doctype"] = "Stock Ledger Entry" |
Rushabh Mehta | a504f06 | 2014-04-04 12:16:26 +0530 | [diff] [blame] | 197 | sle = frappe.get_doc(args) |
Anand Doshi | 6dfd430 | 2015-02-10 14:41:27 +0530 | [diff] [blame] | 198 | sle.flags.ignore_permissions = 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 199 | sle.allow_negative_stock = allow_negative_stock |
Nabin Hait | 54c865e | 2015-03-27 15:38:31 +0530 | [diff] [blame] | 200 | sle.via_landed_cost_voucher = via_landed_cost_voucher |
Nabin Hait | aeba24e | 2013-08-23 15:17:36 +0530 | [diff] [blame] | 201 | sle.submit() |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 202 | return sle |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 203 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 204 | |
| 205 | def repost_future_sle( |
| 206 | args=None, |
| 207 | voucher_type=None, |
| 208 | voucher_no=None, |
| 209 | allow_negative_stock=None, |
| 210 | via_landed_cost_voucher=False, |
| 211 | doc=None, |
| 212 | ): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 213 | if not args and voucher_type and voucher_no: |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 214 | args = get_items_to_be_repost(voucher_type, voucher_no, doc) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 215 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 216 | distinct_item_warehouses = get_distinct_item_warehouse(args, doc) |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 217 | affected_transactions = get_affected_transactions(doc) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 218 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 219 | i = get_current_index(doc) or 0 |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 220 | while i < len(args): |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 221 | validate_item_warehouse(args[i]) |
| 222 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 223 | obj = update_entries_after( |
| 224 | { |
| 225 | "item_code": args[i].get("item_code"), |
| 226 | "warehouse": args[i].get("warehouse"), |
| 227 | "posting_date": args[i].get("posting_date"), |
| 228 | "posting_time": args[i].get("posting_time"), |
| 229 | "creation": args[i].get("creation"), |
| 230 | "distinct_item_warehouses": distinct_item_warehouses, |
| 231 | }, |
| 232 | allow_negative_stock=allow_negative_stock, |
| 233 | via_landed_cost_voucher=via_landed_cost_voucher, |
| 234 | ) |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 235 | affected_transactions.update(obj.affected_transactions) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 236 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 237 | distinct_item_warehouses[ |
| 238 | (args[i].get("item_code"), args[i].get("warehouse")) |
| 239 | ].reposting_status = True |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 240 | |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 241 | if obj.new_items_found: |
Ankush Menat | 8fe5feb | 2021-11-04 19:48:32 +0530 | [diff] [blame] | 242 | for item_wh, data in distinct_item_warehouses.items(): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 243 | if ("args_idx" not in data and not data.reposting_status) or ( |
| 244 | data.sle_changed and data.reposting_status |
| 245 | ): |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 246 | data.args_idx = len(args) |
| 247 | args.append(data.sle) |
| 248 | elif data.sle_changed and not data.reposting_status: |
| 249 | args[data.args_idx] = data.sle |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 250 | |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 251 | data.sle_changed = False |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 252 | i += 1 |
| 253 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 254 | if doc and i % 2 == 0: |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 255 | update_args_in_repost_item_valuation( |
| 256 | doc, i, args, distinct_item_warehouses, affected_transactions |
| 257 | ) |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 258 | |
| 259 | if doc and args: |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 260 | update_args_in_repost_item_valuation( |
| 261 | doc, i, args, distinct_item_warehouses, affected_transactions |
| 262 | ) |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 263 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 264 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 265 | def validate_item_warehouse(args): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 266 | for field in ["item_code", "warehouse", "posting_date", "posting_time"]: |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 267 | if not args.get(field): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 268 | validation_msg = f"The field {frappe.unscrub(args.get(field))} is required for the reposting" |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 269 | frappe.throw(_(validation_msg)) |
| 270 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 271 | |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 272 | def update_args_in_repost_item_valuation( |
| 273 | doc, index, args, distinct_item_warehouses, affected_transactions |
| 274 | ): |
| 275 | affected_transactions_list = [list(transaction) for transaction in affected_transactions] |
| 276 | |
| 277 | doc.db_set( |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 278 | { |
| 279 | "items_to_be_repost": json.dumps(args, default=str), |
| 280 | "distinct_item_and_warehouse": json.dumps( |
| 281 | {str(k): v for k, v in distinct_item_warehouses.items()}, default=str |
| 282 | ), |
| 283 | "current_index": index, |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 284 | "affected_transactions": json.dumps(affected_transactions_list), |
| 285 | } |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 286 | ) |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 287 | |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 288 | if not frappe.flags.in_test: |
| 289 | frappe.db.commit() |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 290 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 291 | frappe.publish_realtime( |
| 292 | "item_reposting_progress", |
| 293 | {"name": doc.name, "items_to_be_repost": json.dumps(args, default=str), "current_index": index}, |
| 294 | ) |
| 295 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 296 | |
| 297 | def get_items_to_be_repost(voucher_type, voucher_no, doc=None): |
| 298 | if doc and doc.items_to_be_repost: |
| 299 | return json.loads(doc.items_to_be_repost) or [] |
| 300 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 301 | return frappe.db.get_all( |
| 302 | "Stock Ledger Entry", |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 303 | filters={"voucher_type": voucher_type, "voucher_no": voucher_no}, |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 304 | fields=["item_code", "warehouse", "posting_date", "posting_time", "creation"], |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 305 | order_by="creation asc", |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 306 | group_by="item_code, warehouse", |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 307 | ) |
Nabin Hait | 74c281c | 2013-08-19 16:17:18 +0530 | [diff] [blame] | 308 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 309 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 310 | def get_distinct_item_warehouse(args=None, doc=None): |
| 311 | distinct_item_warehouses = {} |
| 312 | if doc and doc.distinct_item_and_warehouse: |
| 313 | distinct_item_warehouses = json.loads(doc.distinct_item_and_warehouse) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 314 | distinct_item_warehouses = { |
| 315 | frappe.safe_eval(k): frappe._dict(v) for k, v in distinct_item_warehouses.items() |
| 316 | } |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 317 | else: |
| 318 | for i, d in enumerate(args): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 319 | distinct_item_warehouses.setdefault( |
| 320 | (d.item_code, d.warehouse), frappe._dict({"reposting_status": False, "sle": d, "args_idx": i}) |
| 321 | ) |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 322 | |
| 323 | return distinct_item_warehouses |
| 324 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 325 | |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 326 | def get_affected_transactions(doc) -> Set[Tuple[str, str]]: |
| 327 | if not doc.affected_transactions: |
| 328 | return set() |
| 329 | |
| 330 | transactions = frappe.parse_json(doc.affected_transactions) |
| 331 | return {tuple(transaction) for transaction in transactions} |
| 332 | |
| 333 | |
rohitwaghchaure | 31fe5f5 | 2021-08-02 11:01:30 +0530 | [diff] [blame] | 334 | def get_current_index(doc=None): |
| 335 | if doc and doc.current_index: |
| 336 | return doc.current_index |
| 337 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 338 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 339 | class update_entries_after(object): |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 340 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 341 | update valution rate and qty after transaction |
| 342 | from the current time-bucket onwards |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 343 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 344 | :param args: args as dict |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 345 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 346 | args = { |
| 347 | "item_code": "ABC", |
| 348 | "warehouse": "XYZ", |
| 349 | "posting_date": "2012-12-12", |
| 350 | "posting_time": "12:00" |
| 351 | } |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 352 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 353 | |
| 354 | def __init__( |
| 355 | self, |
| 356 | args, |
| 357 | allow_zero_rate=False, |
| 358 | allow_negative_stock=None, |
| 359 | via_landed_cost_voucher=False, |
| 360 | verbose=1, |
| 361 | ): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 362 | self.exceptions = {} |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 363 | self.verbose = verbose |
| 364 | self.allow_zero_rate = allow_zero_rate |
Anand Doshi | 0dc79f4 | 2015-04-06 12:59:34 +0530 | [diff] [blame] | 365 | self.via_landed_cost_voucher = via_landed_cost_voucher |
Ankush Menat | eb8b424 | 2022-02-12 13:08:28 +0530 | [diff] [blame] | 366 | self.item_code = args.get("item_code") |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 367 | self.allow_negative_stock = allow_negative_stock or is_negative_stock_allowed( |
| 368 | item_code=self.item_code |
| 369 | ) |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 370 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 371 | self.args = frappe._dict(args) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 372 | if self.args.sle_id: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 373 | self.args["name"] = self.args.sle_id |
Nabin Hait | d46b236 | 2021-02-23 16:38:52 +0530 | [diff] [blame] | 374 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 375 | self.company = frappe.get_cached_value("Warehouse", self.args.warehouse, "company") |
| 376 | self.get_precision() |
| 377 | self.valuation_method = get_valuation_method(self.item_code) |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 378 | |
| 379 | self.new_items_found = False |
| 380 | self.distinct_item_warehouses = args.get("distinct_item_warehouses", frappe._dict()) |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 381 | self.affected_transactions: Set[Tuple[str, str]] = set() |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 382 | |
| 383 | self.data = frappe._dict() |
| 384 | self.initialize_previous_data(self.args) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 385 | self.build() |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 386 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 387 | def get_precision(self): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 388 | company_base_currency = frappe.get_cached_value("Company", self.company, "default_currency") |
| 389 | self.precision = get_field_precision( |
| 390 | frappe.get_meta("Stock Ledger Entry").get_field("stock_value"), currency=company_base_currency |
| 391 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 392 | |
| 393 | def initialize_previous_data(self, args): |
| 394 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 395 | Get previous sl entries for current item for each related warehouse |
| 396 | and assigns into self.data dict |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 397 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 398 | :Data Structure: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 399 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 400 | self.data = { |
| 401 | warehouse1: { |
| 402 | 'previus_sle': {}, |
| 403 | 'qty_after_transaction': 10, |
| 404 | 'valuation_rate': 100, |
| 405 | 'stock_value': 1000, |
| 406 | 'prev_stock_value': 1000, |
| 407 | 'stock_queue': '[[10, 100]]', |
| 408 | 'stock_value_difference': 1000 |
| 409 | } |
| 410 | } |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 411 | |
| 412 | """ |
Ankush Menat | c1d986a | 2021-08-31 19:43:42 +0530 | [diff] [blame] | 413 | self.data.setdefault(args.warehouse, frappe._dict()) |
| 414 | warehouse_dict = self.data[args.warehouse] |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 415 | previous_sle = get_previous_sle_of_current_voucher(args) |
Ankush Menat | c1d986a | 2021-08-31 19:43:42 +0530 | [diff] [blame] | 416 | warehouse_dict.previous_sle = previous_sle |
Nabin Hait | bb77756 | 2013-08-29 18:19:37 +0530 | [diff] [blame] | 417 | |
Ankush Menat | c1d986a | 2021-08-31 19:43:42 +0530 | [diff] [blame] | 418 | for key in ("qty_after_transaction", "valuation_rate", "stock_value"): |
| 419 | setattr(warehouse_dict, key, flt(previous_sle.get(key))) |
| 420 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 421 | warehouse_dict.update( |
| 422 | { |
| 423 | "prev_stock_value": previous_sle.stock_value or 0.0, |
| 424 | "stock_queue": json.loads(previous_sle.stock_queue or "[]"), |
| 425 | "stock_value_difference": 0.0, |
| 426 | } |
| 427 | ) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 428 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 429 | def build(self): |
Sagar Vora | e50324a | 2021-03-31 12:44:03 +0530 | [diff] [blame] | 430 | from erpnext.controllers.stock_controller import future_sle_exists |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 431 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 432 | if self.args.get("sle_id"): |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 433 | self.process_sle_against_current_timestamp() |
Sagar Vora | e50324a | 2021-03-31 12:44:03 +0530 | [diff] [blame] | 434 | if not future_sle_exists(self.args): |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 435 | self.update_bin() |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 436 | else: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 437 | entries_to_fix = self.get_future_entries_to_fix() |
| 438 | |
| 439 | i = 0 |
| 440 | while i < len(entries_to_fix): |
| 441 | sle = entries_to_fix[i] |
| 442 | i += 1 |
| 443 | |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 444 | self.process_sle(sle) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 445 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 446 | if sle.dependant_sle_voucher_detail_no: |
Nabin Hait | 243d59b | 2021-02-02 16:55:13 +0530 | [diff] [blame] | 447 | entries_to_fix = self.get_dependent_entries_to_fix(entries_to_fix, sle) |
Nabin Hait | d46b236 | 2021-02-23 16:38:52 +0530 | [diff] [blame] | 448 | |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 449 | self.update_bin() |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 450 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 451 | if self.exceptions: |
| 452 | self.raise_exceptions() |
| 453 | |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 454 | def process_sle_against_current_timestamp(self): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 455 | sl_entries = self.get_sle_against_current_voucher() |
| 456 | for sle in sl_entries: |
| 457 | self.process_sle(sle) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 458 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 459 | def get_sle_against_current_voucher(self): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 460 | self.args["time_format"] = "%H:%i:%s" |
Nabin Hait | f2be080 | 2021-02-15 19:27:49 +0530 | [diff] [blame] | 461 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 462 | return frappe.db.sql( |
| 463 | """ |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 464 | select |
| 465 | *, timestamp(posting_date, posting_time) as "timestamp" |
| 466 | from |
| 467 | `tabStock Ledger Entry` |
| 468 | where |
| 469 | item_code = %(item_code)s |
| 470 | and warehouse = %(warehouse)s |
rohitwaghchaure | fe4540d | 2021-08-26 12:52:36 +0530 | [diff] [blame] | 471 | and is_cancelled = 0 |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 472 | and timestamp(posting_date, time_format(posting_time, %(time_format)s)) = timestamp(%(posting_date)s, time_format(%(posting_time)s, %(time_format)s)) |
| 473 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 474 | order by |
| 475 | creation ASC |
| 476 | for update |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 477 | """, |
| 478 | self.args, |
| 479 | as_dict=1, |
| 480 | ) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 481 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 482 | def get_future_entries_to_fix(self): |
| 483 | # includes current entry! |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 484 | args = self.data[self.args.warehouse].previous_sle or frappe._dict( |
| 485 | {"item_code": self.item_code, "warehouse": self.args.warehouse} |
| 486 | ) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 487 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 488 | return list(self.get_sle_after_datetime(args)) |
Rushabh Mehta | 538607e | 2016-06-12 11:03:00 +0530 | [diff] [blame] | 489 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 490 | def get_dependent_entries_to_fix(self, entries_to_fix, sle): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 491 | dependant_sle = get_sle_by_voucher_detail_no( |
| 492 | sle.dependant_sle_voucher_detail_no, excluded_sle=sle.name |
| 493 | ) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 494 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 495 | if not dependant_sle: |
Nabin Hait | 243d59b | 2021-02-02 16:55:13 +0530 | [diff] [blame] | 496 | return entries_to_fix |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 497 | elif ( |
| 498 | dependant_sle.item_code == self.item_code and dependant_sle.warehouse == self.args.warehouse |
| 499 | ): |
Nabin Hait | 243d59b | 2021-02-02 16:55:13 +0530 | [diff] [blame] | 500 | return entries_to_fix |
| 501 | elif dependant_sle.item_code != self.item_code: |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 502 | self.update_distinct_item_warehouses(dependant_sle) |
Nabin Hait | 243d59b | 2021-02-02 16:55:13 +0530 | [diff] [blame] | 503 | return entries_to_fix |
| 504 | elif dependant_sle.item_code == self.item_code and dependant_sle.warehouse in self.data: |
| 505 | return entries_to_fix |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 506 | else: |
| 507 | return self.append_future_sle_for_dependant(dependant_sle, entries_to_fix) |
| 508 | |
| 509 | def update_distinct_item_warehouses(self, dependant_sle): |
| 510 | key = (dependant_sle.item_code, dependant_sle.warehouse) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 511 | val = frappe._dict({"sle": dependant_sle}) |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 512 | if key not in self.distinct_item_warehouses: |
| 513 | self.distinct_item_warehouses[key] = val |
| 514 | self.new_items_found = True |
| 515 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 516 | existing_sle_posting_date = ( |
| 517 | self.distinct_item_warehouses[key].get("sle", {}).get("posting_date") |
| 518 | ) |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 519 | if getdate(dependant_sle.posting_date) < getdate(existing_sle_posting_date): |
| 520 | val.sle_changed = True |
| 521 | self.distinct_item_warehouses[key] = val |
| 522 | self.new_items_found = True |
| 523 | |
| 524 | def append_future_sle_for_dependant(self, dependant_sle, entries_to_fix): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 525 | self.initialize_previous_data(dependant_sle) |
| 526 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 527 | args = self.data[dependant_sle.warehouse].previous_sle or frappe._dict( |
| 528 | {"item_code": self.item_code, "warehouse": dependant_sle.warehouse} |
| 529 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 530 | future_sle_for_dependant = list(self.get_sle_after_datetime(args)) |
| 531 | |
| 532 | entries_to_fix.extend(future_sle_for_dependant) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 533 | return sorted(entries_to_fix, key=lambda k: k["timestamp"]) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 534 | |
| 535 | def process_sle(self, sle): |
Ankush Menat | 66bf21f | 2022-01-16 20:45:59 +0530 | [diff] [blame] | 536 | from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos |
| 537 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 538 | # previous sle data for this warehouse |
| 539 | self.wh_data = self.data[sle.warehouse] |
Ankush Menat | ecdb493 | 2022-04-17 19:06:13 +0530 | [diff] [blame^] | 540 | self.affected_transactions.add((sle.voucher_type, sle.voucher_no)) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 541 | |
Anand Doshi | 0dc79f4 | 2015-04-06 12:59:34 +0530 | [diff] [blame] | 542 | if (sle.serial_no and not self.via_landed_cost_voucher) or not cint(self.allow_negative_stock): |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 543 | # validate negative stock for serialized items, fifo valuation |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 544 | # or when negative stock is not allowed for moving average |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 545 | if not self.validate_negative_stock(sle): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 546 | self.wh_data.qty_after_transaction += flt(sle.actual_qty) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 547 | return |
Nabin Hait | b96c014 | 2014-10-07 11:25:04 +0530 | [diff] [blame] | 548 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 549 | # Get dynamic incoming/outgoing rate |
Ankush Menat | 701878f | 2022-03-01 18:08:29 +0530 | [diff] [blame] | 550 | if not self.args.get("sle_id"): |
| 551 | self.get_dynamic_incoming_outgoing_rate(sle) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 552 | |
Ankush Menat | 66bf21f | 2022-01-16 20:45:59 +0530 | [diff] [blame] | 553 | if get_serial_nos(sle.serial_no): |
Rushabh Mehta | 2a21bc9 | 2015-02-25 15:08:42 +0530 | [diff] [blame] | 554 | self.get_serialized_values(sle) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 555 | self.wh_data.qty_after_transaction += flt(sle.actual_qty) |
Rohit Waghchaure | 05d3bcb | 2019-04-28 18:39:18 +0530 | [diff] [blame] | 556 | if sle.voucher_type == "Stock Reconciliation": |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 557 | self.wh_data.qty_after_transaction = sle.qty_after_transaction |
Rohit Waghchaure | 05d3bcb | 2019-04-28 18:39:18 +0530 | [diff] [blame] | 558 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 559 | self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt( |
| 560 | self.wh_data.valuation_rate |
| 561 | ) |
| 562 | elif sle.batch_no and frappe.db.get_value( |
| 563 | "Batch", sle.batch_no, "use_batchwise_valuation", cache=True |
| 564 | ): |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 565 | self.update_batched_values(sle) |
Nabin Hait | b96c014 | 2014-10-07 11:25:04 +0530 | [diff] [blame] | 566 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 567 | if sle.voucher_type == "Stock Reconciliation" and not sle.batch_no: |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 568 | # assert |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 569 | self.wh_data.valuation_rate = sle.valuation_rate |
| 570 | self.wh_data.qty_after_transaction = sle.qty_after_transaction |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 571 | self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt( |
| 572 | self.wh_data.valuation_rate |
| 573 | ) |
Ankush Menat | b0cf619 | 2022-01-16 13:02:23 +0530 | [diff] [blame] | 574 | if self.valuation_method != "Moving Average": |
| 575 | self.wh_data.stock_queue = [[self.wh_data.qty_after_transaction, self.wh_data.valuation_rate]] |
Nabin Hait | b96c014 | 2014-10-07 11:25:04 +0530 | [diff] [blame] | 576 | else: |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 577 | if self.valuation_method == "Moving Average": |
| 578 | self.get_moving_average_values(sle) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 579 | self.wh_data.qty_after_transaction += flt(sle.actual_qty) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 580 | self.wh_data.stock_value = flt(self.wh_data.qty_after_transaction) * flt( |
| 581 | self.wh_data.valuation_rate |
| 582 | ) |
Nabin Hait | b96c014 | 2014-10-07 11:25:04 +0530 | [diff] [blame] | 583 | else: |
Ankush Menat | f089d39 | 2022-02-02 12:51:21 +0530 | [diff] [blame] | 584 | self.update_queue_values(sle) |
Nabin Hait | b96c014 | 2014-10-07 11:25:04 +0530 | [diff] [blame] | 585 | |
Rushabh Mehta | 5404778 | 2013-12-26 11:07:46 +0530 | [diff] [blame] | 586 | # rounding as per precision |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 587 | self.wh_data.stock_value = flt(self.wh_data.stock_value, self.precision) |
Ankush Menat | 609d2fc | 2022-02-20 11:35:53 +0530 | [diff] [blame] | 588 | if not self.wh_data.qty_after_transaction: |
| 589 | self.wh_data.stock_value = 0.0 |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 590 | stock_value_difference = self.wh_data.stock_value - self.wh_data.prev_stock_value |
| 591 | self.wh_data.prev_stock_value = self.wh_data.stock_value |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 592 | |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 593 | # update current sle |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 594 | sle.qty_after_transaction = self.wh_data.qty_after_transaction |
| 595 | sle.valuation_rate = self.wh_data.valuation_rate |
| 596 | sle.stock_value = self.wh_data.stock_value |
| 597 | sle.stock_queue = json.dumps(self.wh_data.stock_queue) |
Rushabh Mehta | 2e0e711 | 2015-02-18 11:38:05 +0530 | [diff] [blame] | 598 | sle.stock_value_difference = stock_value_difference |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 599 | sle.doctype = "Stock Ledger Entry" |
Rushabh Mehta | 8bb6e53 | 2015-02-18 20:22:59 +0530 | [diff] [blame] | 600 | frappe.get_doc(sle).db_update() |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 601 | |
Ankush Menat | 701878f | 2022-03-01 18:08:29 +0530 | [diff] [blame] | 602 | if not self.args.get("sle_id"): |
| 603 | self.update_outgoing_rate_on_transaction(sle) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 604 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 605 | def validate_negative_stock(self, sle): |
| 606 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 607 | validate negative stock for entries current datetime onwards |
| 608 | will not consider cancelled entries |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 609 | """ |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 610 | diff = self.wh_data.qty_after_transaction + flt(sle.actual_qty) |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 611 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 612 | if diff < 0 and abs(diff) > 0.0001: |
| 613 | # negative stock! |
| 614 | exc = sle.copy().update({"diff": diff}) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 615 | self.exceptions.setdefault(sle.warehouse, []).append(exc) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 616 | return False |
Nabin Hait | 902e860 | 2013-01-08 18:29:24 +0530 | [diff] [blame] | 617 | else: |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 618 | return True |
| 619 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 620 | def get_dynamic_incoming_outgoing_rate(self, sle): |
| 621 | # Get updated incoming/outgoing rate from transaction |
| 622 | if sle.recalculate_rate: |
| 623 | rate = self.get_incoming_outgoing_rate_from_transaction(sle) |
| 624 | |
| 625 | if flt(sle.actual_qty) >= 0: |
| 626 | sle.incoming_rate = rate |
| 627 | else: |
| 628 | sle.outgoing_rate = rate |
| 629 | |
| 630 | def get_incoming_outgoing_rate_from_transaction(self, sle): |
| 631 | rate = 0 |
| 632 | # Material Transfer, Repack, Manufacturing |
| 633 | if sle.voucher_type == "Stock Entry": |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 634 | self.recalculate_amounts_in_stock_entry(sle.voucher_no) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 635 | rate = frappe.db.get_value("Stock Entry Detail", sle.voucher_detail_no, "valuation_rate") |
| 636 | # Sales and Purchase Return |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 637 | elif sle.voucher_type in ( |
| 638 | "Purchase Receipt", |
| 639 | "Purchase Invoice", |
| 640 | "Delivery Note", |
| 641 | "Sales Invoice", |
| 642 | ): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 643 | if frappe.get_cached_value(sle.voucher_type, sle.voucher_no, "is_return"): |
Chillar Anand | 915b343 | 2021-09-02 16:44:59 +0530 | [diff] [blame] | 644 | from erpnext.controllers.sales_and_purchase_return import ( |
| 645 | get_rate_for_return, # don't move this import to top |
| 646 | ) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 647 | |
| 648 | rate = get_rate_for_return( |
| 649 | sle.voucher_type, |
| 650 | sle.voucher_no, |
| 651 | sle.item_code, |
| 652 | voucher_detail_no=sle.voucher_detail_no, |
| 653 | sle=sle, |
| 654 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 655 | else: |
| 656 | if sle.voucher_type in ("Purchase Receipt", "Purchase Invoice"): |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 657 | rate_field = "valuation_rate" |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 658 | else: |
| 659 | rate_field = "incoming_rate" |
| 660 | |
| 661 | # check in item table |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 662 | item_code, incoming_rate = frappe.db.get_value( |
| 663 | sle.voucher_type + " Item", sle.voucher_detail_no, ["item_code", rate_field] |
| 664 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 665 | |
| 666 | if item_code == sle.item_code: |
| 667 | rate = incoming_rate |
| 668 | else: |
| 669 | if sle.voucher_type in ("Delivery Note", "Sales Invoice"): |
| 670 | ref_doctype = "Packed Item" |
| 671 | else: |
| 672 | ref_doctype = "Purchase Receipt Item Supplied" |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 673 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 674 | rate = frappe.db.get_value( |
| 675 | ref_doctype, |
| 676 | {"parent_detail_docname": sle.voucher_detail_no, "item_code": sle.item_code}, |
| 677 | rate_field, |
| 678 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 679 | |
| 680 | return rate |
| 681 | |
| 682 | def update_outgoing_rate_on_transaction(self, sle): |
| 683 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 684 | Update outgoing rate in Stock Entry, Delivery Note, Sales Invoice and Sales Return |
| 685 | In case of Stock Entry, also calculate FG Item rate and total incoming/outgoing amount |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 686 | """ |
| 687 | if sle.actual_qty and sle.voucher_detail_no: |
| 688 | outgoing_rate = abs(flt(sle.stock_value_difference)) / abs(sle.actual_qty) |
| 689 | |
| 690 | if flt(sle.actual_qty) < 0 and sle.voucher_type == "Stock Entry": |
| 691 | self.update_rate_on_stock_entry(sle, outgoing_rate) |
| 692 | elif sle.voucher_type in ("Delivery Note", "Sales Invoice"): |
| 693 | self.update_rate_on_delivery_and_sales_return(sle, outgoing_rate) |
| 694 | elif flt(sle.actual_qty) < 0 and sle.voucher_type in ("Purchase Receipt", "Purchase Invoice"): |
| 695 | self.update_rate_on_purchase_receipt(sle, outgoing_rate) |
| 696 | |
| 697 | def update_rate_on_stock_entry(self, sle, outgoing_rate): |
| 698 | frappe.db.set_value("Stock Entry Detail", sle.voucher_detail_no, "basic_rate", outgoing_rate) |
| 699 | |
Ankush Menat | 701878f | 2022-03-01 18:08:29 +0530 | [diff] [blame] | 700 | # Update outgoing item's rate, recalculate FG Item's rate and total incoming/outgoing amount |
| 701 | if not sle.dependant_sle_voucher_detail_no: |
| 702 | self.recalculate_amounts_in_stock_entry(sle.voucher_no) |
Nabin Hait | 97bce3a | 2021-07-12 13:24:43 +0530 | [diff] [blame] | 703 | |
| 704 | def recalculate_amounts_in_stock_entry(self, voucher_no): |
| 705 | stock_entry = frappe.get_doc("Stock Entry", voucher_no, for_update=True) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 706 | stock_entry.calculate_rate_and_amount(reset_outgoing_rate=False, raise_error_if_no_rate=False) |
| 707 | stock_entry.db_update() |
| 708 | for d in stock_entry.items: |
| 709 | d.db_update() |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 710 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 711 | def update_rate_on_delivery_and_sales_return(self, sle, outgoing_rate): |
| 712 | # Update item's incoming rate on transaction |
| 713 | item_code = frappe.db.get_value(sle.voucher_type + " Item", sle.voucher_detail_no, "item_code") |
| 714 | if item_code == sle.item_code: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 715 | frappe.db.set_value( |
| 716 | sle.voucher_type + " Item", sle.voucher_detail_no, "incoming_rate", outgoing_rate |
| 717 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 718 | else: |
| 719 | # packed item |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 720 | frappe.db.set_value( |
| 721 | "Packed Item", |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 722 | {"parent_detail_docname": sle.voucher_detail_no, "item_code": sle.item_code}, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 723 | "incoming_rate", |
| 724 | outgoing_rate, |
| 725 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 726 | |
| 727 | def update_rate_on_purchase_receipt(self, sle, outgoing_rate): |
| 728 | if frappe.db.exists(sle.voucher_type + " Item", sle.voucher_detail_no): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 729 | frappe.db.set_value( |
| 730 | sle.voucher_type + " Item", sle.voucher_detail_no, "base_net_rate", outgoing_rate |
| 731 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 732 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 733 | frappe.db.set_value( |
| 734 | "Purchase Receipt Item Supplied", sle.voucher_detail_no, "rate", outgoing_rate |
| 735 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 736 | |
| 737 | # Recalculate subcontracted item's rate in case of subcontracted purchase receipt/invoice |
Sagar Sharma | d074c93 | 2022-03-31 19:57:42 +0530 | [diff] [blame] | 738 | if frappe.get_cached_value(sle.voucher_type, sle.voucher_no, "is_subcontracted"): |
Rohit Waghchaure | e5fb239 | 2021-06-18 20:37:42 +0530 | [diff] [blame] | 739 | doc = frappe.get_doc(sle.voucher_type, sle.voucher_no) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 740 | doc.update_valuation_rate(reset_outgoing_rate=False) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 741 | for d in doc.items + doc.supplied_items: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 742 | d.db_update() |
| 743 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 744 | def get_serialized_values(self, sle): |
| 745 | incoming_rate = flt(sle.incoming_rate) |
| 746 | actual_qty = flt(sle.actual_qty) |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 747 | serial_nos = cstr(sle.serial_no).split("\n") |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 748 | |
| 749 | if incoming_rate < 0: |
| 750 | # wrong incoming rate |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 751 | incoming_rate = self.wh_data.valuation_rate |
Rushabh Mehta | 538607e | 2016-06-12 11:03:00 +0530 | [diff] [blame] | 752 | |
Nabin Hait | 2620bf4 | 2016-02-29 11:30:27 +0530 | [diff] [blame] | 753 | stock_value_change = 0 |
Ankush Menat | b9642a1 | 2021-12-21 16:49:41 +0530 | [diff] [blame] | 754 | if actual_qty > 0: |
Nabin Hait | 2620bf4 | 2016-02-29 11:30:27 +0530 | [diff] [blame] | 755 | stock_value_change = actual_qty * incoming_rate |
Ankush Menat | b9642a1 | 2021-12-21 16:49:41 +0530 | [diff] [blame] | 756 | else: |
Nabin Hait | 2620bf4 | 2016-02-29 11:30:27 +0530 | [diff] [blame] | 757 | # In case of delivery/stock issue, get average purchase rate |
| 758 | # of serial nos of current entry |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 759 | if not sle.is_cancelled: |
| 760 | outgoing_value = self.get_incoming_value_for_serial_nos(sle, serial_nos) |
| 761 | stock_value_change = -1 * outgoing_value |
| 762 | else: |
| 763 | stock_value_change = actual_qty * sle.outgoing_rate |
Rushabh Mehta | 2a21bc9 | 2015-02-25 15:08:42 +0530 | [diff] [blame] | 764 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 765 | new_stock_qty = self.wh_data.qty_after_transaction + actual_qty |
rohitwaghchaure | 0fe6ced | 2018-07-27 10:33:30 +0530 | [diff] [blame] | 766 | |
Nabin Hait | 2620bf4 | 2016-02-29 11:30:27 +0530 | [diff] [blame] | 767 | if new_stock_qty > 0: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 768 | new_stock_value = ( |
| 769 | self.wh_data.qty_after_transaction * self.wh_data.valuation_rate |
| 770 | ) + stock_value_change |
rohitwaghchaure | 0fe6ced | 2018-07-27 10:33:30 +0530 | [diff] [blame] | 771 | if new_stock_value >= 0: |
Nabin Hait | 2620bf4 | 2016-02-29 11:30:27 +0530 | [diff] [blame] | 772 | # calculate new valuation rate only if stock value is positive |
| 773 | # else it remains the same as that of previous entry |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 774 | self.wh_data.valuation_rate = new_stock_value / new_stock_qty |
Rushabh Mehta | cca33b2 | 2016-07-08 18:24:46 +0530 | [diff] [blame] | 775 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 776 | if not self.wh_data.valuation_rate and sle.voucher_detail_no: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 777 | allow_zero_rate = self.check_if_allow_zero_valuation_rate( |
| 778 | sle.voucher_type, sle.voucher_detail_no |
| 779 | ) |
rohitwaghchaure | b1ac979 | 2017-12-01 16:09:02 +0530 | [diff] [blame] | 780 | if not allow_zero_rate: |
Ankush Menat | d7ca83e | 2022-02-19 19:35:33 +0530 | [diff] [blame] | 781 | self.wh_data.valuation_rate = self.get_fallback_rate(sle) |
rohitwaghchaure | b1ac979 | 2017-12-01 16:09:02 +0530 | [diff] [blame] | 782 | |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 783 | def get_incoming_value_for_serial_nos(self, sle, serial_nos): |
| 784 | # get rate from serial nos within same company |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 785 | all_serial_nos = frappe.get_all( |
| 786 | "Serial No", fields=["purchase_rate", "name", "company"], filters={"name": ("in", serial_nos)} |
| 787 | ) |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 788 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 789 | incoming_values = sum(flt(d.purchase_rate) for d in all_serial_nos if d.company == sle.company) |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 790 | |
| 791 | # Get rate for serial nos which has been transferred to other company |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 792 | invalid_serial_nos = [d.name for d in all_serial_nos if d.company != sle.company] |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 793 | for serial_no in invalid_serial_nos: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 794 | incoming_rate = frappe.db.sql( |
| 795 | """ |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 796 | select incoming_rate |
| 797 | from `tabStock Ledger Entry` |
| 798 | where |
| 799 | company = %s |
| 800 | and actual_qty > 0 |
Ankush Menat | 82ea958 | 2022-01-16 20:19:04 +0530 | [diff] [blame] | 801 | and is_cancelled = 0 |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 802 | and (serial_no = %s |
| 803 | or serial_no like %s |
| 804 | or serial_no like %s |
| 805 | or serial_no like %s |
| 806 | ) |
| 807 | order by posting_date desc |
| 808 | limit 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 809 | """, |
| 810 | (sle.company, serial_no, serial_no + "\n%", "%\n" + serial_no, "%\n" + serial_no + "\n%"), |
| 811 | ) |
Nabin Hait | 328c4f9 | 2020-01-02 19:00:32 +0530 | [diff] [blame] | 812 | |
| 813 | incoming_values += flt(incoming_rate[0][0]) if incoming_rate else 0 |
| 814 | |
| 815 | return incoming_values |
| 816 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 817 | def get_moving_average_values(self, sle): |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 818 | actual_qty = flt(sle.actual_qty) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 819 | new_stock_qty = flt(self.wh_data.qty_after_transaction) + actual_qty |
Nabin Hait | 6dfc78b | 2016-06-24 12:28:55 +0530 | [diff] [blame] | 820 | if new_stock_qty >= 0: |
| 821 | if actual_qty > 0: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 822 | if flt(self.wh_data.qty_after_transaction) <= 0: |
| 823 | self.wh_data.valuation_rate = sle.incoming_rate |
Nabin Hait | 6dfc78b | 2016-06-24 12:28:55 +0530 | [diff] [blame] | 824 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 825 | new_stock_value = (self.wh_data.qty_after_transaction * self.wh_data.valuation_rate) + ( |
| 826 | actual_qty * sle.incoming_rate |
| 827 | ) |
Rushabh Mehta | 14a908b | 2015-10-15 12:28:20 +0530 | [diff] [blame] | 828 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 829 | self.wh_data.valuation_rate = new_stock_value / new_stock_qty |
Rushabh Mehta | 14a908b | 2015-10-15 12:28:20 +0530 | [diff] [blame] | 830 | |
Nabin Hait | 6dfc78b | 2016-06-24 12:28:55 +0530 | [diff] [blame] | 831 | elif sle.outgoing_rate: |
| 832 | if new_stock_qty: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 833 | new_stock_value = (self.wh_data.qty_after_transaction * self.wh_data.valuation_rate) + ( |
| 834 | actual_qty * sle.outgoing_rate |
| 835 | ) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 836 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 837 | self.wh_data.valuation_rate = new_stock_value / new_stock_qty |
Nabin Hait | 6dfc78b | 2016-06-24 12:28:55 +0530 | [diff] [blame] | 838 | else: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 839 | self.wh_data.valuation_rate = sle.outgoing_rate |
Nabin Hait | 6dfc78b | 2016-06-24 12:28:55 +0530 | [diff] [blame] | 840 | else: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 841 | if flt(self.wh_data.qty_after_transaction) >= 0 and sle.outgoing_rate: |
| 842 | self.wh_data.valuation_rate = sle.outgoing_rate |
Rushabh Mehta | 14a908b | 2015-10-15 12:28:20 +0530 | [diff] [blame] | 843 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 844 | if not self.wh_data.valuation_rate and actual_qty > 0: |
| 845 | self.wh_data.valuation_rate = sle.incoming_rate |
Rushabh Mehta | cc8b2b2 | 2017-03-31 12:44:29 +0530 | [diff] [blame] | 846 | |
Rushabh Mehta | aedaac6 | 2017-05-04 09:35:19 +0530 | [diff] [blame] | 847 | # Get valuation rate from previous SLE or Item master, if item does not have the |
Javier Wong | 9b11d9b | 2017-04-14 18:24:04 +0800 | [diff] [blame] | 848 | # allow zero valuration rate flag set |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 849 | if not self.wh_data.valuation_rate and sle.voucher_detail_no: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 850 | allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate( |
| 851 | sle.voucher_type, sle.voucher_detail_no |
| 852 | ) |
Javier Wong | 9b11d9b | 2017-04-14 18:24:04 +0800 | [diff] [blame] | 853 | if not allow_zero_valuation_rate: |
Ankush Menat | d7ca83e | 2022-02-19 19:35:33 +0530 | [diff] [blame] | 854 | self.wh_data.valuation_rate = self.get_fallback_rate(sle) |
Rushabh Mehta | cc8b2b2 | 2017-03-31 12:44:29 +0530 | [diff] [blame] | 855 | |
Ankush Menat | f089d39 | 2022-02-02 12:51:21 +0530 | [diff] [blame] | 856 | def update_queue_values(self, sle): |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 857 | incoming_rate = flt(sle.incoming_rate) |
| 858 | actual_qty = flt(sle.actual_qty) |
Nabin Hait | ada485f | 2015-07-17 15:09:56 +0530 | [diff] [blame] | 859 | outgoing_rate = flt(sle.outgoing_rate) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 860 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 861 | self.wh_data.qty_after_transaction = round_off_if_near_zero( |
| 862 | self.wh_data.qty_after_transaction + actual_qty |
| 863 | ) |
Ankush Menat | b534fee | 2022-02-19 20:58:36 +0530 | [diff] [blame] | 864 | |
Ankush Menat | 97e18a1 | 2022-01-15 17:42:25 +0530 | [diff] [blame] | 865 | if self.valuation_method == "LIFO": |
| 866 | stock_queue = LIFOValuation(self.wh_data.stock_queue) |
| 867 | else: |
| 868 | stock_queue = FIFOValuation(self.wh_data.stock_queue) |
| 869 | |
Ankush Menat | b534fee | 2022-02-19 20:58:36 +0530 | [diff] [blame] | 870 | _prev_qty, prev_stock_value = stock_queue.get_total_stock_and_value() |
| 871 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 872 | if actual_qty > 0: |
Ankush Menat | 97e18a1 | 2022-01-15 17:42:25 +0530 | [diff] [blame] | 873 | stock_queue.add_stock(qty=actual_qty, rate=incoming_rate) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 874 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 875 | |
Ankush Menat | 4b29fb6 | 2021-12-18 18:40:22 +0530 | [diff] [blame] | 876 | def rate_generator() -> float: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 877 | allow_zero_valuation_rate = self.check_if_allow_zero_valuation_rate( |
| 878 | sle.voucher_type, sle.voucher_detail_no |
| 879 | ) |
Ankush Menat | 4b29fb6 | 2021-12-18 18:40:22 +0530 | [diff] [blame] | 880 | if not allow_zero_valuation_rate: |
Ankush Menat | d7ca83e | 2022-02-19 19:35:33 +0530 | [diff] [blame] | 881 | return self.get_fallback_rate(sle) |
Nabin Hait | ada485f | 2015-07-17 15:09:56 +0530 | [diff] [blame] | 882 | else: |
Ankush Menat | 4b29fb6 | 2021-12-18 18:40:22 +0530 | [diff] [blame] | 883 | return 0.0 |
Rushabh Mehta | cca33b2 | 2016-07-08 18:24:46 +0530 | [diff] [blame] | 884 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 885 | stock_queue.remove_stock( |
| 886 | qty=abs(actual_qty), outgoing_rate=outgoing_rate, rate_generator=rate_generator |
| 887 | ) |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 888 | |
Ankush Menat | b534fee | 2022-02-19 20:58:36 +0530 | [diff] [blame] | 889 | _qty, stock_value = stock_queue.get_total_stock_and_value() |
| 890 | |
| 891 | stock_value_difference = stock_value - prev_stock_value |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 892 | |
Ankush Menat | 97e18a1 | 2022-01-15 17:42:25 +0530 | [diff] [blame] | 893 | self.wh_data.stock_queue = stock_queue.state |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 894 | self.wh_data.stock_value = round_off_if_near_zero( |
| 895 | self.wh_data.stock_value + stock_value_difference |
| 896 | ) |
Rushabh Mehta | cca33b2 | 2016-07-08 18:24:46 +0530 | [diff] [blame] | 897 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 898 | if not self.wh_data.stock_queue: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 899 | self.wh_data.stock_queue.append( |
| 900 | [0, sle.incoming_rate or sle.outgoing_rate or self.wh_data.valuation_rate] |
| 901 | ) |
Rushabh Mehta | cc8b2b2 | 2017-03-31 12:44:29 +0530 | [diff] [blame] | 902 | |
Ankush Menat | b534fee | 2022-02-19 20:58:36 +0530 | [diff] [blame] | 903 | if self.wh_data.qty_after_transaction: |
| 904 | self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction |
| 905 | |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 906 | def update_batched_values(self, sle): |
| 907 | incoming_rate = flt(sle.incoming_rate) |
| 908 | actual_qty = flt(sle.actual_qty) |
Ankush Menat | 4b29fb6 | 2021-12-18 18:40:22 +0530 | [diff] [blame] | 909 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 910 | self.wh_data.qty_after_transaction = round_off_if_near_zero( |
| 911 | self.wh_data.qty_after_transaction + actual_qty |
| 912 | ) |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 913 | |
| 914 | if actual_qty > 0: |
| 915 | stock_value_difference = incoming_rate * actual_qty |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 916 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 917 | outgoing_rate = get_batch_incoming_rate( |
| 918 | item_code=sle.item_code, |
| 919 | warehouse=sle.warehouse, |
| 920 | batch_no=sle.batch_no, |
| 921 | posting_date=sle.posting_date, |
| 922 | posting_time=sle.posting_time, |
| 923 | creation=sle.creation, |
| 924 | ) |
Ankush Menat | aba7a7c | 2022-02-19 19:36:28 +0530 | [diff] [blame] | 925 | if outgoing_rate is None: |
| 926 | # This can *only* happen if qty available for the batch is zero. |
| 927 | # in such case fall back various other rates. |
| 928 | # future entries will correct the overall accounting as each |
| 929 | # batch individually uses moving average rates. |
| 930 | outgoing_rate = self.get_fallback_rate(sle) |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 931 | stock_value_difference = outgoing_rate * actual_qty |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 932 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 933 | self.wh_data.stock_value = round_off_if_near_zero( |
| 934 | self.wh_data.stock_value + stock_value_difference |
| 935 | ) |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 936 | if self.wh_data.qty_after_transaction: |
| 937 | self.wh_data.valuation_rate = self.wh_data.stock_value / self.wh_data.qty_after_transaction |
Ankush Menat | 4b29fb6 | 2021-12-18 18:40:22 +0530 | [diff] [blame] | 938 | |
Javier Wong | 9b11d9b | 2017-04-14 18:24:04 +0800 | [diff] [blame] | 939 | def check_if_allow_zero_valuation_rate(self, voucher_type, voucher_detail_no): |
deepeshgarg007 | f9c0ef3 | 2019-07-30 18:49:19 +0530 | [diff] [blame] | 940 | ref_item_dt = "" |
| 941 | |
| 942 | if voucher_type == "Stock Entry": |
| 943 | ref_item_dt = voucher_type + " Detail" |
| 944 | elif voucher_type in ["Purchase Invoice", "Sales Invoice", "Delivery Note", "Purchase Receipt"]: |
| 945 | ref_item_dt = voucher_type + " Item" |
| 946 | |
| 947 | if ref_item_dt: |
| 948 | return frappe.db.get_value(ref_item_dt, voucher_detail_no, "allow_zero_valuation_rate") |
| 949 | else: |
| 950 | return 0 |
Rushabh Mehta | cc8b2b2 | 2017-03-31 12:44:29 +0530 | [diff] [blame] | 951 | |
Ankush Menat | d7ca83e | 2022-02-19 19:35:33 +0530 | [diff] [blame] | 952 | def get_fallback_rate(self, sle) -> float: |
| 953 | """When exact incoming rate isn't available use any of other "average" rates as fallback. |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 954 | This should only get used for negative stock.""" |
| 955 | return get_valuation_rate( |
| 956 | sle.item_code, |
| 957 | sle.warehouse, |
| 958 | sle.voucher_type, |
| 959 | sle.voucher_no, |
| 960 | self.allow_zero_rate, |
| 961 | currency=erpnext.get_company_currency(sle.company), |
| 962 | company=sle.company, |
| 963 | batch_no=sle.batch_no, |
| 964 | ) |
Ankush Menat | d7ca83e | 2022-02-19 19:35:33 +0530 | [diff] [blame] | 965 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 966 | def get_sle_before_datetime(self, args): |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 967 | """get previous stock ledger entry before current time-bucket""" |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 968 | sle = get_stock_ledger_entries(args, "<", "desc", "limit 1", for_update=False) |
| 969 | sle = sle[0] if sle else frappe._dict() |
| 970 | return sle |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 971 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 972 | def get_sle_after_datetime(self, args): |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 973 | """get Stock Ledger Entries after a particular datetime, for reposting""" |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 974 | return get_stock_ledger_entries(args, ">", "asc", for_update=True, check_serial_no=False) |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 975 | |
| 976 | def raise_exceptions(self): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 977 | msg_list = [] |
Ankush Menat | 8fe5feb | 2021-11-04 19:48:32 +0530 | [diff] [blame] | 978 | for warehouse, exceptions in self.exceptions.items(): |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 979 | deficiency = min(e["diff"] for e in exceptions) |
Rushabh Mehta | 538607e | 2016-06-12 11:03:00 +0530 | [diff] [blame] | 980 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 981 | if ( |
| 982 | exceptions[0]["voucher_type"], |
| 983 | exceptions[0]["voucher_no"], |
| 984 | ) in frappe.local.flags.currently_saving: |
Nabin Hait | 3edefb1 | 2016-07-20 16:13:18 +0530 | [diff] [blame] | 985 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 986 | msg = _("{0} units of {1} needed in {2} to complete this transaction.").format( |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 987 | abs(deficiency), |
| 988 | frappe.get_desk_link("Item", exceptions[0]["item_code"]), |
| 989 | frappe.get_desk_link("Warehouse", warehouse), |
| 990 | ) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 991 | else: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 992 | msg = _( |
| 993 | "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction." |
| 994 | ).format( |
| 995 | abs(deficiency), |
| 996 | frappe.get_desk_link("Item", exceptions[0]["item_code"]), |
| 997 | frappe.get_desk_link("Warehouse", warehouse), |
| 998 | exceptions[0]["posting_date"], |
| 999 | exceptions[0]["posting_time"], |
| 1000 | frappe.get_desk_link(exceptions[0]["voucher_type"], exceptions[0]["voucher_no"]), |
| 1001 | ) |
Rushabh Mehta | 538607e | 2016-06-12 11:03:00 +0530 | [diff] [blame] | 1002 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1003 | if msg: |
| 1004 | msg_list.append(msg) |
| 1005 | |
| 1006 | if msg_list: |
| 1007 | message = "\n\n".join(msg_list) |
| 1008 | if self.verbose: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1009 | frappe.throw(message, NegativeStockError, title=_("Insufficient Stock")) |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1010 | else: |
| 1011 | raise NegativeStockError(message) |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 1012 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1013 | def update_bin(self): |
| 1014 | # update bin for each warehouse |
Ankush Menat | 8fe5feb | 2021-11-04 19:48:32 +0530 | [diff] [blame] | 1015 | for warehouse, data in self.data.items(): |
Ankush Menat | 97060c4 | 2021-12-03 11:50:38 +0530 | [diff] [blame] | 1016 | bin_name = get_or_make_bin(self.item_code, warehouse) |
Deepesh Garg | 6f107da | 2021-10-12 20:15:55 +0530 | [diff] [blame] | 1017 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1018 | updated_values = {"actual_qty": data.qty_after_transaction, "stock_value": data.stock_value} |
Ankush Menat | 7dd1036 | 2022-03-10 17:07:57 +0530 | [diff] [blame] | 1019 | if data.valuation_rate is not None: |
| 1020 | updated_values["valuation_rate"] = data.valuation_rate |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1021 | frappe.db.set_value("Bin", bin_name, updated_values) |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 1022 | |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1023 | |
| 1024 | def get_previous_sle_of_current_voucher(args, exclude_current_voucher=False): |
| 1025 | """get stock ledger entries filtered by specific posting datetime conditions""" |
| 1026 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1027 | args["time_format"] = "%H:%i:%s" |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1028 | if not args.get("posting_date"): |
| 1029 | args["posting_date"] = "1900-01-01" |
| 1030 | if not args.get("posting_time"): |
| 1031 | args["posting_time"] = "00:00" |
| 1032 | |
| 1033 | voucher_condition = "" |
| 1034 | if exclude_current_voucher: |
| 1035 | voucher_no = args.get("voucher_no") |
| 1036 | voucher_condition = f"and voucher_no != '{voucher_no}'" |
| 1037 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1038 | sle = frappe.db.sql( |
| 1039 | """ |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1040 | select *, timestamp(posting_date, posting_time) as "timestamp" |
| 1041 | from `tabStock Ledger Entry` |
| 1042 | where item_code = %(item_code)s |
| 1043 | and warehouse = %(warehouse)s |
| 1044 | and is_cancelled = 0 |
| 1045 | {voucher_condition} |
| 1046 | and timestamp(posting_date, time_format(posting_time, %(time_format)s)) < timestamp(%(posting_date)s, time_format(%(posting_time)s, %(time_format)s)) |
| 1047 | order by timestamp(posting_date, posting_time) desc, creation desc |
| 1048 | limit 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1049 | for update""".format( |
| 1050 | voucher_condition=voucher_condition |
| 1051 | ), |
| 1052 | args, |
| 1053 | as_dict=1, |
| 1054 | ) |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1055 | |
| 1056 | return sle[0] if sle else frappe._dict() |
| 1057 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1058 | |
Anand Doshi | 4dc7caa | 2013-01-11 11:44:49 +0530 | [diff] [blame] | 1059 | def get_previous_sle(args, for_update=False): |
Anand Doshi | 1b53186 | 2013-01-10 19:29:51 +0530 | [diff] [blame] | 1060 | """ |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1061 | get the last sle on or before the current time-bucket, |
| 1062 | to get actual qty before transaction, this function |
| 1063 | is called from various transaction like stock entry, reco etc |
Nabin Hait | dc82d4f | 2014-04-07 12:02:57 +0530 | [diff] [blame] | 1064 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1065 | args = { |
| 1066 | "item_code": "ABC", |
| 1067 | "warehouse": "XYZ", |
| 1068 | "posting_date": "2012-12-12", |
| 1069 | "posting_time": "12:00", |
| 1070 | "sle": "name of reference Stock Ledger Entry" |
| 1071 | } |
Anand Doshi | 1b53186 | 2013-01-10 19:29:51 +0530 | [diff] [blame] | 1072 | """ |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1073 | args["name"] = args.get("sle", None) or "" |
| 1074 | sle = get_stock_ledger_entries(args, "<=", "desc", "limit 1", for_update=for_update) |
Pratik Vyas | 16371b7 | 2013-09-18 18:31:03 +0530 | [diff] [blame] | 1075 | return sle and sle[0] or {} |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1076 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1077 | |
| 1078 | def get_stock_ledger_entries( |
| 1079 | previous_sle, |
| 1080 | operator=None, |
| 1081 | order="desc", |
| 1082 | limit=None, |
| 1083 | for_update=False, |
| 1084 | debug=False, |
| 1085 | check_serial_no=True, |
| 1086 | ): |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1087 | """get stock ledger entries filtered by specific posting datetime conditions""" |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1088 | conditions = " and timestamp(posting_date, posting_time) {0} timestamp(%(posting_date)s, %(posting_time)s)".format( |
| 1089 | operator |
| 1090 | ) |
Nabin Hait | b9ce104 | 2018-02-01 14:58:50 +0530 | [diff] [blame] | 1091 | if previous_sle.get("warehouse"): |
| 1092 | conditions += " and warehouse = %(warehouse)s" |
| 1093 | elif previous_sle.get("warehouse_condition"): |
| 1094 | conditions += " and " + previous_sle.get("warehouse_condition") |
| 1095 | |
Rohit Waghchaure | 66aa37f | 2019-05-24 16:53:51 +0530 | [diff] [blame] | 1096 | if check_serial_no and previous_sle.get("serial_no"): |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 1097 | # conditions += " and serial_no like {}".format(frappe.db.escape('%{0}%'.format(previous_sle.get("serial_no")))) |
| 1098 | serial_no = previous_sle.get("serial_no") |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1099 | conditions += ( |
| 1100 | """ and |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 1101 | ( |
| 1102 | serial_no = {0} |
| 1103 | or serial_no like {1} |
| 1104 | or serial_no like {2} |
| 1105 | or serial_no like {3} |
| 1106 | ) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1107 | """ |
| 1108 | ).format( |
| 1109 | frappe.db.escape(serial_no), |
| 1110 | frappe.db.escape("{}\n%".format(serial_no)), |
| 1111 | frappe.db.escape("%\n{}".format(serial_no)), |
| 1112 | frappe.db.escape("%\n{}\n%".format(serial_no)), |
| 1113 | ) |
Rohit Waghchaure | 05d3bcb | 2019-04-28 18:39:18 +0530 | [diff] [blame] | 1114 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1115 | if not previous_sle.get("posting_date"): |
| 1116 | previous_sle["posting_date"] = "1900-01-01" |
| 1117 | if not previous_sle.get("posting_time"): |
| 1118 | previous_sle["posting_time"] = "00:00" |
| 1119 | |
| 1120 | if operator in (">", "<=") and previous_sle.get("name"): |
| 1121 | conditions += " and name!=%(name)s" |
| 1122 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1123 | return frappe.db.sql( |
| 1124 | """ |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1125 | select *, timestamp(posting_date, posting_time) as "timestamp" |
| 1126 | from `tabStock Ledger Entry` |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1127 | where item_code = %%(item_code)s |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1128 | and is_cancelled = 0 |
Nabin Hait | b9ce104 | 2018-02-01 14:58:50 +0530 | [diff] [blame] | 1129 | %(conditions)s |
Aditya Hase | 0c16424 | 2019-01-07 22:07:13 +0530 | [diff] [blame] | 1130 | order by timestamp(posting_date, posting_time) %(order)s, creation %(order)s |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1131 | %(limit)s %(for_update)s""" |
| 1132 | % { |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1133 | "conditions": conditions, |
| 1134 | "limit": limit or "", |
| 1135 | "for_update": for_update and "for update" or "", |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1136 | "order": order, |
| 1137 | }, |
| 1138 | previous_sle, |
| 1139 | as_dict=1, |
| 1140 | debug=debug, |
| 1141 | ) |
| 1142 | |
Rushabh Mehta | df9e80c | 2015-02-17 19:55:17 +0530 | [diff] [blame] | 1143 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1144 | def get_sle_by_voucher_detail_no(voucher_detail_no, excluded_sle=None): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1145 | return frappe.db.get_value( |
| 1146 | "Stock Ledger Entry", |
| 1147 | {"voucher_detail_no": voucher_detail_no, "name": ["!=", excluded_sle]}, |
| 1148 | [ |
| 1149 | "item_code", |
| 1150 | "warehouse", |
| 1151 | "posting_date", |
| 1152 | "posting_time", |
| 1153 | "timestamp(posting_date, posting_time) as timestamp", |
| 1154 | ], |
| 1155 | as_dict=1, |
| 1156 | ) |
Deepesh Garg | 2a9c5ba | 2020-04-30 10:38:58 +0530 | [diff] [blame] | 1157 | |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 1158 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1159 | def get_batch_incoming_rate( |
| 1160 | item_code, warehouse, batch_no, posting_date, posting_time, creation=None |
| 1161 | ): |
| 1162 | |
| 1163 | Timestamp = CustomFunction("timestamp", ["date", "time"]) |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 1164 | |
| 1165 | sle = frappe.qb.DocType("Stock Ledger Entry") |
| 1166 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1167 | timestamp_condition = Timestamp(sle.posting_date, sle.posting_time) < Timestamp( |
| 1168 | posting_date, posting_time |
| 1169 | ) |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 1170 | if creation: |
| 1171 | timestamp_condition |= ( |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1172 | Timestamp(sle.posting_date, sle.posting_time) == Timestamp(posting_date, posting_time) |
| 1173 | ) & (sle.creation < creation) |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 1174 | |
| 1175 | batch_details = ( |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1176 | frappe.qb.from_(sle) |
| 1177 | .select(Sum(sle.stock_value_difference).as_("batch_value"), Sum(sle.actual_qty).as_("batch_qty")) |
| 1178 | .where( |
| 1179 | (sle.item_code == item_code) |
| 1180 | & (sle.warehouse == warehouse) |
| 1181 | & (sle.batch_no == batch_no) |
| 1182 | & (sle.is_cancelled == 0) |
| 1183 | ) |
| 1184 | .where(timestamp_condition) |
Ankush Menat | 102fff2 | 2022-02-19 15:51:04 +0530 | [diff] [blame] | 1185 | ).run(as_dict=True) |
Ankush Menat | ce0514c | 2022-02-15 11:41:41 +0530 | [diff] [blame] | 1186 | |
| 1187 | if batch_details and batch_details[0].batch_qty: |
| 1188 | return batch_details[0].batch_value / batch_details[0].batch_qty |
| 1189 | |
| 1190 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1191 | def get_valuation_rate( |
| 1192 | item_code, |
| 1193 | warehouse, |
| 1194 | voucher_type, |
| 1195 | voucher_no, |
| 1196 | allow_zero_rate=False, |
| 1197 | currency=None, |
| 1198 | company=None, |
| 1199 | raise_error_if_no_rate=True, |
| 1200 | batch_no=None, |
| 1201 | ): |
Rohit Waghchaure | a5f4094 | 2017-06-16 15:21:36 +0530 | [diff] [blame] | 1202 | |
Ankush Menat | f7ffe04 | 2021-11-01 13:21:14 +0530 | [diff] [blame] | 1203 | if not company: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1204 | company = frappe.get_cached_value("Warehouse", warehouse, "company") |
Ankush Menat | f7ffe04 | 2021-11-01 13:21:14 +0530 | [diff] [blame] | 1205 | |
Ankush Menat | 342d09a | 2022-02-19 14:28:51 +0530 | [diff] [blame] | 1206 | last_valuation_rate = None |
| 1207 | |
| 1208 | # Get moving average rate of a specific batch number |
| 1209 | if warehouse and batch_no and frappe.db.get_value("Batch", batch_no, "use_batchwise_valuation"): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1210 | last_valuation_rate = frappe.db.sql( |
| 1211 | """ |
Ankush Menat | 342d09a | 2022-02-19 14:28:51 +0530 | [diff] [blame] | 1212 | select sum(stock_value_difference) / sum(actual_qty) |
| 1213 | from `tabStock Ledger Entry` |
| 1214 | where |
| 1215 | item_code = %s |
| 1216 | AND warehouse = %s |
| 1217 | AND batch_no = %s |
| 1218 | AND is_cancelled = 0 |
| 1219 | AND NOT (voucher_no = %s AND voucher_type = %s) |
| 1220 | """, |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1221 | (item_code, warehouse, batch_no, voucher_no, voucher_type), |
| 1222 | ) |
Ankush Menat | 342d09a | 2022-02-19 14:28:51 +0530 | [diff] [blame] | 1223 | |
Ankush Menat | f7ffe04 | 2021-11-01 13:21:14 +0530 | [diff] [blame] | 1224 | # Get valuation rate from last sle for the same item and warehouse |
Ankush Menat | 342d09a | 2022-02-19 14:28:51 +0530 | [diff] [blame] | 1225 | if not last_valuation_rate or last_valuation_rate[0][0] is None: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1226 | last_valuation_rate = frappe.db.sql( |
| 1227 | """select valuation_rate |
Ankush Menat | 342d09a | 2022-02-19 14:28:51 +0530 | [diff] [blame] | 1228 | from `tabStock Ledger Entry` force index (item_warehouse) |
| 1229 | where |
| 1230 | item_code = %s |
| 1231 | AND warehouse = %s |
| 1232 | AND valuation_rate >= 0 |
| 1233 | AND is_cancelled = 0 |
| 1234 | AND NOT (voucher_no = %s AND voucher_type = %s) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1235 | order by posting_date desc, posting_time desc, name desc limit 1""", |
| 1236 | (item_code, warehouse, voucher_no, voucher_type), |
| 1237 | ) |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1238 | |
| 1239 | if not last_valuation_rate: |
Nabin Hait | a0b967f | 2017-01-18 18:35:58 +0530 | [diff] [blame] | 1240 | # Get valuation rate from last sle for the item against any warehouse |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1241 | last_valuation_rate = frappe.db.sql( |
| 1242 | """select valuation_rate |
Deepesh Garg | 6f107da | 2021-10-12 20:15:55 +0530 | [diff] [blame] | 1243 | from `tabStock Ledger Entry` force index (item_code) |
Mangesh-Khairnar | 0df5134 | 2019-08-19 10:04:52 +0530 | [diff] [blame] | 1244 | where |
| 1245 | item_code = %s |
| 1246 | AND valuation_rate > 0 |
Ankush Menat | 82ea958 | 2022-01-16 20:19:04 +0530 | [diff] [blame] | 1247 | AND is_cancelled = 0 |
Mangesh-Khairnar | 0df5134 | 2019-08-19 10:04:52 +0530 | [diff] [blame] | 1248 | AND NOT(voucher_no = %s AND voucher_type = %s) |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1249 | order by posting_date desc, posting_time desc, name desc limit 1""", |
| 1250 | (item_code, voucher_no, voucher_type), |
| 1251 | ) |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1252 | |
Nabin Hait | a645f36 | 2018-03-01 10:31:24 +0530 | [diff] [blame] | 1253 | if last_valuation_rate: |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1254 | return flt(last_valuation_rate[0][0]) |
Nabin Hait | a645f36 | 2018-03-01 10:31:24 +0530 | [diff] [blame] | 1255 | |
| 1256 | # If negative stock allowed, and item delivered without any incoming entry, |
| 1257 | # system does not found any SLE, then take valuation rate from Item |
| 1258 | valuation_rate = frappe.db.get_value("Item", item_code, "valuation_rate") |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1259 | |
| 1260 | if not valuation_rate: |
Nabin Hait | a645f36 | 2018-03-01 10:31:24 +0530 | [diff] [blame] | 1261 | # try Item Standard rate |
| 1262 | valuation_rate = frappe.db.get_value("Item", item_code, "standard_rate") |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1263 | |
Rushabh Mehta | aedaac6 | 2017-05-04 09:35:19 +0530 | [diff] [blame] | 1264 | if not valuation_rate: |
Nabin Hait | a645f36 | 2018-03-01 10:31:24 +0530 | [diff] [blame] | 1265 | # try in price list |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1266 | valuation_rate = frappe.db.get_value( |
| 1267 | "Item Price", dict(item_code=item_code, buying=1, currency=currency), "price_list_rate" |
| 1268 | ) |
Rushabh Mehta | cc8b2b2 | 2017-03-31 12:44:29 +0530 | [diff] [blame] | 1269 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1270 | if ( |
| 1271 | not allow_zero_rate |
| 1272 | and not valuation_rate |
| 1273 | and raise_error_if_no_rate |
| 1274 | and cint(erpnext.is_perpetual_inventory_enabled(company)) |
| 1275 | ): |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 1276 | form_link = get_link_to_form("Item", item_code) |
Marica | 97715f2 | 2020-05-11 20:45:37 +0530 | [diff] [blame] | 1277 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1278 | message = _( |
| 1279 | "Valuation Rate for the Item {0}, is required to do accounting entries for {1} {2}." |
| 1280 | ).format(form_link, voucher_type, voucher_no) |
Rohit Waghchaure | bb3e5d0 | 2021-04-24 17:28:33 +0530 | [diff] [blame] | 1281 | message += "<br><br>" + _("Here are the options to proceed:") |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1282 | solutions = ( |
| 1283 | "<li>" |
| 1284 | + _( |
| 1285 | "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." |
| 1286 | ).format(voucher_type) |
| 1287 | + "</li>" |
| 1288 | ) |
| 1289 | solutions += ( |
| 1290 | "<li>" |
| 1291 | + _("If not, you can Cancel / Submit this entry") |
| 1292 | + " {0} ".format(frappe.bold("after")) |
| 1293 | + _("performing either one below:") |
| 1294 | + "</li>" |
| 1295 | ) |
Marica | 97715f2 | 2020-05-11 20:45:37 +0530 | [diff] [blame] | 1296 | sub_solutions = "<ul><li>" + _("Create an incoming stock transaction for the Item.") + "</li>" |
| 1297 | sub_solutions += "<li>" + _("Mention Valuation Rate in the Item master.") + "</li></ul>" |
| 1298 | msg = message + solutions + sub_solutions + "</li>" |
| 1299 | |
| 1300 | frappe.throw(msg=msg, title=_("Valuation Rate Missing")) |
Nabin Hait | fb6e434 | 2014-10-15 11:34:40 +0530 | [diff] [blame] | 1301 | |
| 1302 | return valuation_rate |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1303 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1304 | |
Ankush Menat | e7109c1 | 2021-08-26 16:40:45 +0530 | [diff] [blame] | 1305 | def update_qty_in_future_sle(args, allow_negative_stock=False): |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1306 | """Recalculate Qty after Transaction in future SLEs based on current SLE.""" |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1307 | datetime_limit_condition = "" |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1308 | qty_shift = args.actual_qty |
| 1309 | |
| 1310 | # find difference/shift in qty caused by stock reconciliation |
| 1311 | if args.voucher_type == "Stock Reconciliation": |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1312 | qty_shift = get_stock_reco_qty_shift(args) |
| 1313 | |
| 1314 | # find the next nearest stock reco so that we only recalculate SLEs till that point |
| 1315 | next_stock_reco_detail = get_next_stock_reco(args) |
| 1316 | if next_stock_reco_detail: |
| 1317 | detail = next_stock_reco_detail[0] |
| 1318 | # add condition to update SLEs before this date & time |
| 1319 | datetime_limit_condition = get_datetime_limit_condition(detail) |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1320 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1321 | frappe.db.sql( |
| 1322 | """ |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 1323 | update `tabStock Ledger Entry` |
marination | 8418c4b | 2021-06-22 21:35:25 +0530 | [diff] [blame] | 1324 | set qty_after_transaction = qty_after_transaction + {qty_shift} |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 1325 | where |
| 1326 | item_code = %(item_code)s |
| 1327 | and warehouse = %(warehouse)s |
| 1328 | and voucher_no != %(voucher_no)s |
| 1329 | and is_cancelled = 0 |
| 1330 | and (timestamp(posting_date, posting_time) > timestamp(%(posting_date)s, %(posting_time)s) |
| 1331 | or ( |
| 1332 | timestamp(posting_date, posting_time) = timestamp(%(posting_date)s, %(posting_time)s) |
| 1333 | and creation > %(creation)s |
| 1334 | ) |
| 1335 | ) |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1336 | {datetime_limit_condition} |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1337 | """.format( |
| 1338 | qty_shift=qty_shift, datetime_limit_condition=datetime_limit_condition |
| 1339 | ), |
| 1340 | args, |
| 1341 | ) |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 1342 | |
| 1343 | validate_negative_qty_in_future_sle(args, allow_negative_stock) |
| 1344 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1345 | |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1346 | def get_stock_reco_qty_shift(args): |
| 1347 | stock_reco_qty_shift = 0 |
| 1348 | if args.get("is_cancelled"): |
| 1349 | if args.get("previous_qty_after_transaction"): |
| 1350 | # get qty (balance) that was set at submission |
| 1351 | last_balance = args.get("previous_qty_after_transaction") |
| 1352 | stock_reco_qty_shift = flt(args.qty_after_transaction) - flt(last_balance) |
| 1353 | else: |
| 1354 | stock_reco_qty_shift = flt(args.actual_qty) |
| 1355 | else: |
| 1356 | # reco is being submitted |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1357 | last_balance = get_previous_sle_of_current_voucher(args, exclude_current_voucher=True).get( |
| 1358 | "qty_after_transaction" |
| 1359 | ) |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1360 | |
| 1361 | if last_balance is not None: |
| 1362 | stock_reco_qty_shift = flt(args.qty_after_transaction) - flt(last_balance) |
| 1363 | else: |
| 1364 | stock_reco_qty_shift = args.qty_after_transaction |
| 1365 | |
| 1366 | return stock_reco_qty_shift |
| 1367 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1368 | |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1369 | def get_next_stock_reco(args): |
| 1370 | """Returns next nearest stock reconciliaton's details.""" |
| 1371 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1372 | return frappe.db.sql( |
| 1373 | """ |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1374 | select |
| 1375 | name, posting_date, posting_time, creation, voucher_no |
| 1376 | from |
marination | 8c44126 | 2021-07-02 17:46:05 +0530 | [diff] [blame] | 1377 | `tabStock Ledger Entry` |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1378 | where |
| 1379 | item_code = %(item_code)s |
| 1380 | and warehouse = %(warehouse)s |
| 1381 | and voucher_type = 'Stock Reconciliation' |
| 1382 | and voucher_no != %(voucher_no)s |
| 1383 | and is_cancelled = 0 |
| 1384 | and (timestamp(posting_date, posting_time) > timestamp(%(posting_date)s, %(posting_time)s) |
| 1385 | or ( |
| 1386 | timestamp(posting_date, posting_time) = timestamp(%(posting_date)s, %(posting_time)s) |
| 1387 | and creation > %(creation)s |
| 1388 | ) |
| 1389 | ) |
| 1390 | limit 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1391 | """, |
| 1392 | args, |
| 1393 | as_dict=1, |
| 1394 | ) |
| 1395 | |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1396 | |
| 1397 | def get_datetime_limit_condition(detail): |
marination | 4038977 | 2021-07-02 17:13:45 +0530 | [diff] [blame] | 1398 | return f""" |
| 1399 | and |
| 1400 | (timestamp(posting_date, posting_time) < timestamp('{detail.posting_date}', '{detail.posting_time}') |
| 1401 | or ( |
| 1402 | timestamp(posting_date, posting_time) = timestamp('{detail.posting_date}', '{detail.posting_time}') |
| 1403 | and creation < '{detail.creation}' |
| 1404 | ) |
| 1405 | )""" |
| 1406 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1407 | |
Ankush Menat | e7109c1 | 2021-08-26 16:40:45 +0530 | [diff] [blame] | 1408 | def validate_negative_qty_in_future_sle(args, allow_negative_stock=False): |
Ankush Menat | eb8b424 | 2022-02-12 13:08:28 +0530 | [diff] [blame] | 1409 | if allow_negative_stock or is_negative_stock_allowed(item_code=args.item_code): |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1410 | return |
| 1411 | if not (args.actual_qty < 0 or args.voucher_type == "Stock Reconciliation"): |
| 1412 | return |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 1413 | |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1414 | neg_sle = get_future_sle_with_negative_qty(args) |
| 1415 | if neg_sle: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1416 | message = _( |
| 1417 | "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction." |
| 1418 | ).format( |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1419 | abs(neg_sle[0]["qty_after_transaction"]), |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1420 | frappe.get_desk_link("Item", args.item_code), |
| 1421 | frappe.get_desk_link("Warehouse", args.warehouse), |
| 1422 | neg_sle[0]["posting_date"], |
| 1423 | neg_sle[0]["posting_time"], |
| 1424 | frappe.get_desk_link(neg_sle[0]["voucher_type"], neg_sle[0]["voucher_no"]), |
| 1425 | ) |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1426 | |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1427 | frappe.throw(message, NegativeStockError, title=_("Insufficient Stock")) |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1428 | |
| 1429 | if not args.batch_no: |
| 1430 | return |
| 1431 | |
| 1432 | neg_batch_sle = get_future_sle_with_negative_batch_qty(args) |
| 1433 | if neg_batch_sle: |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1434 | message = _( |
| 1435 | "{0} units of {1} needed in {2} on {3} {4} for {5} to complete this transaction." |
| 1436 | ).format( |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1437 | abs(neg_batch_sle[0]["cumulative_total"]), |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1438 | frappe.get_desk_link("Batch", args.batch_no), |
| 1439 | frappe.get_desk_link("Warehouse", args.warehouse), |
| 1440 | neg_batch_sle[0]["posting_date"], |
| 1441 | neg_batch_sle[0]["posting_time"], |
| 1442 | frappe.get_desk_link(neg_batch_sle[0]["voucher_type"], neg_batch_sle[0]["voucher_no"]), |
| 1443 | ) |
mergify[bot] | 7a3d301 | 2022-03-08 10:42:30 +0530 | [diff] [blame] | 1444 | frappe.throw(message, NegativeStockError, title=_("Insufficient Stock for Batch")) |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1445 | |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1446 | |
| 1447 | def get_future_sle_with_negative_qty(args): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1448 | return frappe.db.sql( |
| 1449 | """ |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1450 | select |
| 1451 | qty_after_transaction, posting_date, posting_time, |
| 1452 | voucher_type, voucher_no |
| 1453 | from `tabStock Ledger Entry` |
Deepesh Garg | b4be292 | 2021-01-28 13:09:56 +0530 | [diff] [blame] | 1454 | where |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1455 | item_code = %(item_code)s |
| 1456 | and warehouse = %(warehouse)s |
| 1457 | and voucher_no != %(voucher_no)s |
| 1458 | and timestamp(posting_date, posting_time) >= timestamp(%(posting_date)s, %(posting_time)s) |
| 1459 | and is_cancelled = 0 |
Nabin Hait | 186a045 | 2021-02-18 14:14:21 +0530 | [diff] [blame] | 1460 | and qty_after_transaction < 0 |
Nabin Hait | 243d59b | 2021-02-02 16:55:13 +0530 | [diff] [blame] | 1461 | order by timestamp(posting_date, posting_time) asc |
Nabin Hait | a77b8c9 | 2020-12-21 14:45:50 +0530 | [diff] [blame] | 1462 | limit 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1463 | """, |
| 1464 | args, |
| 1465 | as_dict=1, |
| 1466 | ) |
Ankush Menat | 6a014d1 | 2021-04-12 20:21:27 +0530 | [diff] [blame] | 1467 | |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1468 | |
| 1469 | def get_future_sle_with_negative_batch_qty(args): |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1470 | return frappe.db.sql( |
| 1471 | """ |
Ankush Menat | 5eba575 | 2021-12-07 23:03:52 +0530 | [diff] [blame] | 1472 | with batch_ledger as ( |
| 1473 | select |
| 1474 | posting_date, posting_time, voucher_type, voucher_no, |
| 1475 | sum(actual_qty) over (order by posting_date, posting_time, creation) as cumulative_total |
| 1476 | from `tabStock Ledger Entry` |
| 1477 | where |
| 1478 | item_code = %(item_code)s |
| 1479 | and warehouse = %(warehouse)s |
| 1480 | and batch_no=%(batch_no)s |
| 1481 | and is_cancelled = 0 |
| 1482 | order by posting_date, posting_time, creation |
| 1483 | ) |
| 1484 | select * from batch_ledger |
| 1485 | where |
| 1486 | cumulative_total < 0.0 |
| 1487 | and timestamp(posting_date, posting_time) >= timestamp(%(posting_date)s, %(posting_time)s) |
| 1488 | limit 1 |
Ankush Menat | 494bd9e | 2022-03-28 18:52:46 +0530 | [diff] [blame] | 1489 | """, |
| 1490 | args, |
| 1491 | as_dict=1, |
| 1492 | ) |
Ankush Menat | eb8b424 | 2022-02-12 13:08:28 +0530 | [diff] [blame] | 1493 | |
| 1494 | |
| 1495 | def is_negative_stock_allowed(*, item_code: Optional[str] = None) -> bool: |
| 1496 | if cint(frappe.db.get_single_value("Stock Settings", "allow_negative_stock", cache=True)): |
| 1497 | return True |
| 1498 | if item_code and cint(frappe.db.get_value("Item", item_code, "allow_negative_stock", cache=True)): |
| 1499 | return True |
| 1500 | return False |