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()
filters.show_warehouse_wise_stock = True
item_wh_wise_slots = FIFOSlots(filters, sle).generate()
filters.show_warehouse_wise_stock = False
return item_wise_slots, item_wh_wise_slots
def test_insufficient_balance(self):
"Reference: Case 3 in stock_ageing_fifo_logic.md (same wh)"
sle = [
frappe._dict(
name="Flask Item",
actual_qty=(-30),
qty_after_transaction=(-30),
warehouse="WH 1",
posting_date="2021-12-01",
voucher_type="Stock Entry",
voucher_no="001",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=20,
qty_after_transaction=(-10),
warehouse="WH 1",
posting_date="2021-12-02",
voucher_type="Stock Entry",
voucher_no="002",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=20,
qty_after_transaction=10,
warehouse="WH 1",
posting_date="2021-12-03",
voucher_type="Stock Entry",
voucher_no="003",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=10,
qty_after_transaction=20,
warehouse="WH 1",
posting_date="2021-12-03",
voucher_type="Stock Entry",
voucher_no="004",
has_serial_no=False,
serial_no=None,
),
]
slots = FIFOSlots(self.filters, sle).generate()
result = slots["Flask Item"]
queue = result["fifo_queue"]
self.assertEqual(result["qty_after_transaction"], result["total_qty"])
self.assertEqual(queue[0][0], 10.0)
self.assertEqual(queue[1][0], 10.0)
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_basic_stock_reconciliation(self):
"""
Ledger (same wh): [+30, reco reset >> 50, -10]
Bal: 40
"""
sle = [
frappe._dict(
name="Flask Item",
actual_qty=30,
qty_after_transaction=30,
warehouse="WH 1",
posting_date="2021-12-01",
voucher_type="Stock Entry",
voucher_no="001",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=0,
qty_after_transaction=50,
warehouse="WH 1",
posting_date="2021-12-02",
voucher_type="Stock Reconciliation",
voucher_no="002",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=(-10),
qty_after_transaction=40,
warehouse="WH 1",
posting_date="2021-12-03",
voucher_type="Stock Entry",
voucher_no="003",
has_serial_no=False,
serial_no=None,
),
]
slots = FIFOSlots(self.filters, sle).generate()
result = slots["Flask Item"]
queue = result["fifo_queue"]
self.assertEqual(result["qty_after_transaction"], result["total_qty"])
self.assertEqual(result["total_qty"], 40.0)
self.assertEqual(queue[0][0], 20.0)
self.assertEqual(queue[1][0], 20.0)
def test_sequential_stock_reco_same_warehouse(self):
"""
Test back to back stock recos (same warehouse).
Ledger: [reco opening >> +1000, reco reset >> 400, -10]
Bal: 390
"""
sle = [
frappe._dict(
name="Flask Item",
actual_qty=0,
qty_after_transaction=1000,
warehouse="WH 1",
posting_date="2021-12-01",
voucher_type="Stock Reconciliation",
voucher_no="002",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=0,
qty_after_transaction=400,
warehouse="WH 1",
posting_date="2021-12-02",
voucher_type="Stock Reconciliation",
voucher_no="003",
has_serial_no=False,
serial_no=None,
),
frappe._dict(
name="Flask Item",
actual_qty=(-10),
qty_after_transaction=390,
warehouse="WH 1",
posting_date="2021-12-03",
voucher_type="Stock Entry",
voucher_no="003",
has_serial_no=False,
serial_no=None,
),
]
slots = FIFOSlots(self.filters, sle).generate()
result = slots["Flask Item"]
queue = result["fifo_queue"]
self.assertEqual(result["qty_after_transaction"], result["total_qty"])
self.assertEqual(result["total_qty"], 390.0)
self.assertEqual(queue[0][0], 390.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 execute(filters=None):
is_reposting_item_valuation_in_progress()
if not filters:
filters = {}
validate_filters(filters)
columns = get_columns(filters)
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
item_map = get_item_details(items, sle, filters)
iwb_map = get_item_warehouse_map(filters, sle)
warehouse_list = get_warehouse_list(filters)
item_ageing = FIFOSlots(filters).generate()
data = []
item_balance = {}
item_value = {}
for (company, item, warehouse) in sorted(iwb_map):
if not item_map.get(item):
continue
row = []
qty_dict = iwb_map[(company, item, warehouse)]
item_balance.setdefault((item, item_map[item]["item_group"]), [])
total_stock_value = 0.00
for wh in warehouse_list:
row += [qty_dict.bal_qty] if wh.name == warehouse else [0.00]
total_stock_value += qty_dict.bal_val if wh.name == warehouse else 0.00
item_balance[(item, item_map[item]["item_group"])].append(row)
item_value.setdefault((item, item_map[item]["item_group"]), [])
item_value[(item,
item_map[item]["item_group"])].append(total_stock_value)
# sum bal_qty by item
for (item, item_group), wh_balance in item_balance.items():
if not item_ageing.get(item):
continue
total_stock_value = sum(item_value[(item, item_group)])
row = [item, item_group, total_stock_value]
fifo_queue = item_ageing[item]["fifo_queue"]
average_age = 0.00
if fifo_queue:
average_age = get_average_age(fifo_queue, filters["to_date"])
row += [average_age]
bal_qty = [sum(bal_qty) for bal_qty in zip(*wh_balance)]
total_qty = sum(bal_qty)
if len(warehouse_list) > 1:
row += [total_qty]
row += bal_qty
if total_qty > 0:
data.append(row)
elif not filters.get("filter_total_zero_qty"):
data.append(row)
add_warehouse_column(columns, warehouse_list)
return columns, data
def execute(filters: Optional[StockBalanceFilter] = None):
is_reposting_item_valuation_in_progress()
if not filters:
filters = {}
if filters.get("company"):
company_currency = erpnext.get_company_currency(filters.get("company"))
else:
company_currency = frappe.db.get_single_value("Global Defaults",
"default_currency")
include_uom = filters.get("include_uom")
columns = get_columns(filters)
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
if filters.get("show_stock_ageing_data"):
filters["show_warehouse_wise_stock"] = True
item_wise_fifo_queue = FIFOSlots(filters, sle).generate()
# if no stock ledger entry found return
if not sle:
return columns, []
iwb_map = get_item_warehouse_map(filters, sle)
item_map = get_item_details(items, sle, filters)
item_reorder_detail_map = get_item_reorder_details(item_map.keys())
data = []
conversion_factors = {}
_func = itemgetter(1)
to_date = filters.get("to_date")
for (company, item, warehouse) in sorted(iwb_map):
if item_map.get(item):
qty_dict = iwb_map[(company, item, warehouse)]
item_reorder_level = 0
item_reorder_qty = 0
if item + warehouse in item_reorder_detail_map:
item_reorder_level = item_reorder_detail_map[
item + warehouse]["warehouse_reorder_level"]
item_reorder_qty = item_reorder_detail_map[
item + warehouse]["warehouse_reorder_qty"]
report_data = {
"currency": company_currency,
"item_code": item,
"warehouse": warehouse,
"company": company,
"reorder_level": item_reorder_level,
"reorder_qty": item_reorder_qty,
}
report_data.update(item_map[item])
report_data.update(qty_dict)
if include_uom:
conversion_factors.setdefault(item,
item_map[item].conversion_factor)
if filters.get("show_stock_ageing_data"):
fifo_queue = item_wise_fifo_queue[(
item, warehouse)].get("fifo_queue")
stock_ageing_data = {
"average_age": 0,
"earliest_age": 0,
"latest_age": 0
}
if fifo_queue:
fifo_queue = sorted(filter(_func, fifo_queue), key=_func)
if not fifo_queue:
continue
stock_ageing_data["average_age"] = get_average_age(
fifo_queue, to_date)
stock_ageing_data["earliest_age"] = date_diff(
to_date, fifo_queue[0][1])
stock_ageing_data["latest_age"] = date_diff(
to_date, fifo_queue[-1][1])
report_data.update(stock_ageing_data)
data.append(report_data)
add_additional_uom_columns(columns, data, include_uom, conversion_factors)
return columns, data
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_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)
