Merge branch 'develop' into repack-entry-stock-ageing
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index a89a403..97a740e 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -12,6 +12,7 @@
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
Filters = frappe._dict
+precision = cint(frappe.db.get_single_value("System Settings", "float_precision"))
def execute(filters: Filters = None) -> Tuple:
to_date = filters["to_date"]
@@ -48,10 +49,13 @@
if filters.get("show_warehouse_wise_stock"):
row.append(details.warehouse)
- row.extend([item_dict.get("total_qty"), average_age,
+ row.extend([
+ flt(item_dict.get("total_qty"), precision),
+ average_age,
range1, range2, range3, above_range3,
earliest_age, latest_age,
- details.stock_uom])
+ details.stock_uom
+ ])
data.append(row)
@@ -79,13 +83,13 @@
qty = flt(item[0]) if not item_dict["has_serial_no"] else 1.0
if age <= filters.range1:
- range1 += qty
+ range1 = flt(range1 + qty, precision)
elif age <= filters.range2:
- range2 += qty
+ range2 = flt(range2 + qty, precision)
elif age <= filters.range3:
- range3 += qty
+ range3 = flt(range3 + qty, precision)
else:
- above_range3 += qty
+ above_range3 = flt(above_range3 + qty, precision)
return range1, range2, range3, above_range3
@@ -286,14 +290,16 @@
def __compute_incoming_stock(self, row: Dict, fifo_queue: List, transfer_key: Tuple, serial_nos: List):
"Update FIFO Queue on inward stock."
- if self.transferred_item_details.get(transfer_key):
+ transfer_data = self.transferred_item_details.get(transfer_key)
+ if transfer_data:
# inward/outward from same voucher, item & warehouse
- slot = self.transferred_item_details[transfer_key].pop(0)
- fifo_queue.append(slot)
+ # eg: Repack with same item, Stock reco for batch item
+ # consume transfer data and add stock to fifo queue
+ self.__adjust_incoming_transfer_qty(transfer_data, fifo_queue, row)
else:
if not serial_nos:
- if fifo_queue and flt(fifo_queue[0][0]) < 0:
- # neutralize negative stock by adding positive stock
+ if fifo_queue and flt(fifo_queue[0][0]) <= 0:
+ # neutralize 0/negative stock by adding positive stock
fifo_queue[0][0] += flt(row.actual_qty)
fifo_queue[0][1] = row.posting_date
else:
@@ -324,7 +330,7 @@
elif not fifo_queue:
# negative stock, no balance but qty yet to consume
fifo_queue.append([-(qty_to_pop), row.posting_date])
- self.transferred_item_details[transfer_key].append([row.actual_qty, row.posting_date])
+ self.transferred_item_details[transfer_key].append([qty_to_pop, row.posting_date])
qty_to_pop = 0
else:
# qty to pop < slot qty, ample balance
@@ -333,6 +339,33 @@
self.transferred_item_details[transfer_key].append([qty_to_pop, slot[1]])
qty_to_pop = 0
+ def __adjust_incoming_transfer_qty(self, transfer_data: Dict, fifo_queue: List, row: Dict):
+ "Add previously removed stock back to FIFO Queue."
+ transfer_qty_to_pop = flt(row.actual_qty)
+
+ def add_to_fifo_queue(slot):
+ if fifo_queue and flt(fifo_queue[0][0]) <= 0:
+ # neutralize 0/negative stock by adding positive stock
+ fifo_queue[0][0] += flt(slot[0])
+ fifo_queue[0][1] = slot[1]
+ else:
+ fifo_queue.append(slot)
+
+ while transfer_qty_to_pop:
+ if transfer_data and 0 < transfer_data[0][0] <= transfer_qty_to_pop:
+ # bucket qty is not enough, consume whole
+ transfer_qty_to_pop -= transfer_data[0][0]
+ add_to_fifo_queue(transfer_data.pop(0))
+ elif not transfer_data:
+ # transfer bucket is empty, extra incoming qty
+ add_to_fifo_queue([transfer_qty_to_pop, row.posting_date])
+ transfer_qty_to_pop = 0
+ else:
+ # ample bucket qty to consume
+ transfer_data[0][0] -= transfer_qty_to_pop
+ add_to_fifo_queue([transfer_qty_to_pop, transfer_data[0][1]])
+ transfer_qty_to_pop = 0
+
def __update_balances(self, row: Dict, key: Union[Tuple, str]):
self.item_details[key]["qty_after_transaction"] = row.qty_after_transaction
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing_fifo_logic.md b/erpnext/stock/report/stock_ageing/stock_ageing_fifo_logic.md
index 9e9bed4..3d759dd 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing_fifo_logic.md
+++ b/erpnext/stock/report/stock_ageing/stock_ageing_fifo_logic.md
@@ -71,4 +71,39 @@
2nd | -60 | [[-10, 1-12-2021]]
3rd | +5 | [[-5, 3-12-2021]]
4th | +10 | [[5, 4-12-2021]]
-4th | +20 | [[5, 4-12-2021], [20, 4-12-2021]]
\ No newline at end of file
+4th | +20 | [[5, 4-12-2021], [20, 4-12-2021]]
+
+### Concept of Transfer Qty Bucket
+In the case of **Repack**, Quantity that comes in, isn't really incoming. It is just new stock repurposed from old stock, due to incoming-outgoing of the same warehouse.
+
+Here, stock is consumed from the FIFO Queue. It is then re-added back to the queue.
+While adding stock back to the queue we need to know how much to add.
+For this we need to keep track of how much was previously consumed.
+Hence we use **Transfer Qty Bucket**.
+
+While re-adding stock, we try to add buckets that were consumed earlier (date intact), to maintain correctness.
+
+#### Case 1: Same Item-Warehouse in Repack
+Eg:
+-------------------------------------------------------------------------------------
+Date | Qty | Voucher | FIFO Queue | Transfer Qty Buckets
+-------------------------------------------------------------------------------------
+1st | +500 | PR | [[500, 1-12-2021]] |
+2nd | -50 | Repack | [[450, 1-12-2021]] | [[50, 1-12-2021]]
+2nd | +50 | Repack | [[450, 1-12-2021], [50, 1-12-2021]] | []
+
+- The balance at the end is restored back to 500
+- However, the initial 500 qty bucket is now split into 450 and 50, with the same date
+- The net effect is the same as that before the Repack
+
+#### Case 2: Same Item-Warehouse in Repack with Split Consumption rows
+Eg:
+-------------------------------------------------------------------------------------
+Date | Qty | Voucher | FIFO Queue | Transfer Qty Buckets
+-------------------------------------------------------------------------------------
+1st | +500 | PR | [[500, 1-12-2021]] |
+2nd | -50 | Repack | [[450, 1-12-2021]] | [[50, 1-12-2021]]
+2nd | -50 | Repack | [[400, 1-12-2021]] | [[50, 1-12-2021],
+- | | | |[50, 1-12-2021]]
+2nd | +100 | Repack | [[400, 1-12-2021], [50, 1-12-2021], | []
+- | | | [50, 1-12-2021]] |
diff --git a/erpnext/stock/report/stock_ageing/test_stock_ageing.py b/erpnext/stock/report/stock_ageing/test_stock_ageing.py
index 66d2f6b..3fc357e 100644
--- a/erpnext/stock/report/stock_ageing/test_stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/test_stock_ageing.py
@@ -3,7 +3,7 @@
import frappe
-from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots
+from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, format_report_data
from erpnext.tests.utils import ERPNextTestCase
@@ -11,7 +11,8 @@
def setUp(self) -> None:
self.filters = frappe._dict(
company="_Test Company",
- to_date="2021-12-10"
+ to_date="2021-12-10",
+ range1=30, range2=60, range3=90
)
def test_normal_inward_outward_queue(self):
@@ -236,6 +237,371 @@
item_wh_balances = [item_wh_wise_slots.get(i).get("qty_after_transaction") for i in item_wh_wise_slots]
self.assertEqual(sum(item_wh_balances), item_result["qty_after_transaction"])
+ def test_repack_entry_same_item_split_rows(self):
+ """
+ Split consumption rows and have single repacked item row (same warehouse).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | 500 | 001
+ Item 1 | -50 | 002 (repack)
+ Item 1 | -50 | 002 (repack)
+ Item 1 | 100 | 002 (repack)
+
+ Case most likely for batch items. Test time bucket computation.
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=500, qty_after_transaction=500,
+ warehouse="WH 1",
+ posting_date="2021-12-03", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=450,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=400,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=100, qty_after_transaction=500,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ slots = FIFOSlots(self.filters, sle).generate()
+ item_result = slots["Flask Item"]
+ queue = item_result["fifo_queue"]
+
+ self.assertEqual(item_result["total_qty"], 500.0)
+ self.assertEqual(queue[0][0], 400.0)
+ self.assertEqual(queue[1][0], 50.0)
+ self.assertEqual(queue[2][0], 50.0)
+ # check if time buckets add up to balance qty
+ self.assertEqual(sum([i[0] for i in queue]), 500.0)
+
+ def test_repack_entry_same_item_overconsume(self):
+ """
+ Over consume item and have less repacked item qty (same warehouse).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | 500 | 001
+ Item 1 | -100 | 002 (repack)
+ Item 1 | 50 | 002 (repack)
+
+ Case most likely for batch items. Test time bucket computation.
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=500, qty_after_transaction=500,
+ warehouse="WH 1",
+ posting_date="2021-12-03", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-100), qty_after_transaction=400,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=50, qty_after_transaction=450,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ slots = FIFOSlots(self.filters, sle).generate()
+ item_result = slots["Flask Item"]
+ queue = item_result["fifo_queue"]
+
+ self.assertEqual(item_result["total_qty"], 450.0)
+ self.assertEqual(queue[0][0], 400.0)
+ self.assertEqual(queue[1][0], 50.0)
+ # check if time buckets add up to balance qty
+ self.assertEqual(sum([i[0] for i in queue]), 450.0)
+
+ def test_repack_entry_same_item_overconsume_with_split_rows(self):
+ """
+ Over consume item and have less repacked item qty (same warehouse).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | 20 | 001
+ Item 1 | -50 | 002 (repack)
+ Item 1 | -50 | 002 (repack)
+ Item 1 | 50 | 002 (repack)
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=20, qty_after_transaction=20,
+ warehouse="WH 1",
+ posting_date="2021-12-03", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=(-30),
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=(-80),
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=50, qty_after_transaction=(-30),
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ fifo_slots = FIFOSlots(self.filters, sle)
+ slots = fifo_slots.generate()
+ item_result = slots["Flask Item"]
+ queue = item_result["fifo_queue"]
+
+ self.assertEqual(item_result["total_qty"], -30.0)
+ self.assertEqual(queue[0][0], -30.0)
+
+ # check transfer bucket
+ transfer_bucket = fifo_slots.transferred_item_details[('002', 'Flask Item', 'WH 1')]
+ self.assertEqual(transfer_bucket[0][0], 50)
+
+ def test_repack_entry_same_item_overproduce(self):
+ """
+ Under consume item and have more repacked item qty (same warehouse).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | 500 | 001
+ Item 1 | -50 | 002 (repack)
+ Item 1 | 100 | 002 (repack)
+
+ Case most likely for batch items. Test time bucket computation.
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=500, qty_after_transaction=500,
+ warehouse="WH 1",
+ posting_date="2021-12-03", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=450,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=100, qty_after_transaction=550,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ slots = FIFOSlots(self.filters, sle).generate()
+ item_result = slots["Flask Item"]
+ queue = item_result["fifo_queue"]
+
+ self.assertEqual(item_result["total_qty"], 550.0)
+ self.assertEqual(queue[0][0], 450.0)
+ self.assertEqual(queue[1][0], 50.0)
+ self.assertEqual(queue[2][0], 50.0)
+ # check if time buckets add up to balance qty
+ self.assertEqual(sum([i[0] for i in queue]), 550.0)
+
+ def test_repack_entry_same_item_overproduce_with_split_rows(self):
+ """
+ Over consume item and have less repacked item qty (same warehouse).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | 20 | 001
+ Item 1 | -50 | 002 (repack)
+ Item 1 | 50 | 002 (repack)
+ Item 1 | 50 | 002 (repack)
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=20, qty_after_transaction=20,
+ warehouse="WH 1",
+ posting_date="2021-12-03", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=(-30),
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=50, qty_after_transaction=20,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict(
+ name="Flask Item",
+ actual_qty=50, qty_after_transaction=70,
+ warehouse="WH 1",
+ posting_date="2021-12-04", voucher_type="Stock Entry",
+ voucher_no="002",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ fifo_slots = FIFOSlots(self.filters, sle)
+ slots = fifo_slots.generate()
+ item_result = slots["Flask Item"]
+ queue = item_result["fifo_queue"]
+
+ self.assertEqual(item_result["total_qty"], 70.0)
+ self.assertEqual(queue[0][0], 20.0)
+ self.assertEqual(queue[1][0], 50.0)
+
+ # check transfer bucket
+ transfer_bucket = fifo_slots.transferred_item_details[('002', 'Flask Item', 'WH 1')]
+ self.assertFalse(transfer_bucket)
+
+ def test_negative_stock_same_voucher(self):
+ """
+ Test negative stock scenario in transfer bucket via repack entry (same wh).
+ Ledger:
+ Item | Qty | Voucher
+ ------------------------
+ Item 1 | -50 | 001
+ Item 1 | -50 | 001
+ Item 1 | 30 | 001
+ Item 1 | 80 | 001
+ """
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=(-50),
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=(-50), qty_after_transaction=(-100),
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=30, qty_after_transaction=(-70),
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+ fifo_slots = FIFOSlots(self.filters, sle)
+ slots = fifo_slots.generate()
+ item_result = slots["Flask Item"]
+
+ # check transfer bucket
+ transfer_bucket = fifo_slots.transferred_item_details[('001', 'Flask Item', 'WH 1')]
+ self.assertEqual(transfer_bucket[0][0], 20)
+ self.assertEqual(transfer_bucket[1][0], 50)
+ self.assertEqual(item_result["fifo_queue"][0][0], -70.0)
+
+ sle.append(frappe._dict(
+ name="Flask Item",
+ actual_qty=80, qty_after_transaction=10,
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ))
+
+ fifo_slots = FIFOSlots(self.filters, sle)
+ slots = fifo_slots.generate()
+ item_result = slots["Flask Item"]
+
+ transfer_bucket = fifo_slots.transferred_item_details[('001', 'Flask Item', 'WH 1')]
+ self.assertFalse(transfer_bucket)
+ self.assertEqual(item_result["fifo_queue"][0][0], 10.0)
+
+ def test_precision(self):
+ "Test if final balance qty is rounded off correctly."
+ sle = [
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=0.3, qty_after_transaction=0.3,
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ frappe._dict( # stock up item
+ name="Flask Item",
+ actual_qty=0.6, qty_after_transaction=0.9,
+ warehouse="WH 1",
+ posting_date="2021-12-01", voucher_type="Stock Entry",
+ voucher_no="001",
+ has_serial_no=False, serial_no=None
+ ),
+ ]
+
+ slots = FIFOSlots(self.filters, sle).generate()
+ report_data = format_report_data(self.filters, slots, self.filters["to_date"])
+ row = report_data[0] # first row in report
+ bal_qty = row[5]
+ range_qty_sum = sum([i for i in row[7:11]]) # get sum of range balance
+
+ # check if value of Available Qty column matches with range bucket post format
+ self.assertEqual(bal_qty, 0.9)
+ self.assertEqual(bal_qty, range_qty_sum)
+
def generate_item_and_item_wh_wise_slots(filters, sle):
"Return results with and without 'show_warehouse_wise_stock'"
item_wise_slots = FIFOSlots(filters, sle).generate()